SAP B1 AR Invoice and Incoming Payments Query

[Former Member]

Hi Experts

Posting Date     Doc Num     Card Code       Card Name     Ref Num     Inv Doc Total     DueDate     Incoming DocNum     Incoming Applied Amount     Balance Due

11/6/2015                2452      112               Customer A        145           1000           11/10/2015               2642                             500                          500                                                        

Can anyone help me with this kind of query, I would like to generate reports all AR invoice with incoming payments.

Thank you.

[former_member227598 · Accepted solution]

Hi ,

Try below query

SELECT  T0.[DocDate] AS 'Invoice Posting Date' ,

T0.[DocNum] AS 'Inv Document Number',

T2.[CardCode] AS 'Customer/Vendor Code', T2.[CardName] AS 'Customer/Vendor Name',

T0.[NumAtCard], T0.[DocTotal],T1.[SumApplied] AS 'Paid to Invoice', T2.[DocNum],

T2.[DocDate] AS 'Posting Date', T0.[DocTotal]- T1.[SumApplied]

FROM [dbo].[OINV]  T0  INNER  JOIN [dbo].[RCT2] T1  ON  T1.[DocEntry] = T0.[DocEntry] 

INNER  JOIN [dbo].[ORCT] T2  ON  T2.[DocNum] = T1.[DocNum]

WHERE T2.[DocDate] >= [%0] AND T2.[DocDate] <= [%1]

Rgds,

Kamlesh Naware

[isaac_kalii]

Hi Erwin,

Please try the below query, it also includes status of the invoices.

SELECT	dbo.OINV.DocNum as [DocNum ], CONVERT(VARCHAR, dbo.OINV.DocDate, 103) AS DocDate, dbo.OINV.CardName as Customer, dbo.OINV.DocTotal [DocTotal ], dbo.OINV.PaidToDate AS [Amount Rec'd],
	CASE WHEN oinv.DocTotal = oinv.PaidToDate THEN 'Fully received' WHEN oinv.PaidToDate > 0 THEN 'Partially Received' ELSE 'Not Received' END AS Status
FROM	dbo.OCTG INNER JOIN
	dbo.OINV ON dbo.OCTG.GroupNum = dbo.OINV.GroupNum
WHERE	(dbo.OCTG.PymntGroup = N'Cash/Cheque') AND (dbo.OINV.CANCELED = 'N')

GROUP BY dbo.OINV.DocNum, CONVERT(VARCHAR, dbo.OINV.DocDate, 103), dbo.OINV.CardName, dbo.OINV.DocTotal, dbo.OINV.PaidToDate

Regards,

Isaac Kalii.