Details of Paid Invoices for each Incoming Payment

[leon_laikan]

Hi!

When we receive payment from debtors, we raise an Incoming Payment (in SAP B1), and settle the payment against the relevant Sales Invoices in AR

I wish to write a query that will list all Incoming Payments together with full details of all Sales Invoices (AR) that were settled (fully or partially) by that incoming payment.

Could you give me a full listing of tables that I must use to develop my query, and if possible what Joins I should make?

Thanks a lot

Leon Lai

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query:

SELECT T0.[DocNum], T0.[DocDate], T0.[CardName],T0.[CashSum], T0.[CreditSum], T0.[TrsfrSum],T0.[DocTotal],  T2.[DocNum], T2.[DocDate], T2.[CardName], T3.[ItemCode], T3.[Quantity], T3.[LineStatus] FROM ORCT T0  INNER JOIN RCT2 T1 ON T0.DocNum = T1.DocNum left join OINV T2 on t1.docentry = t2.docentry INNER JOIN INV1 T3 ON T2.DocEntry = T3.DocEntry WHERE T0.[DocDate] between [%0] and [%1] GROUP BY T0.[DocNum], T0.[DocDate], T0.[CardName],T0.[CashSum], T0.[CreditSum], T0.[TrsfrSum],T0.[DocTotal],  T2.[DocNum], T2.[DocDate], T2.[CardName], T3.[ItemCode], T3.[Quantity], T3.[LineStatus]

Thanks & Regards,

Nagarajan

[leon_laikan · Accepted solution]

Hi Nagarajan,

Thanks a lot for your response.

It helped solve my problem.

Best Regards,

Leon Lai

[Former Member · Accepted solution]

Hi Lenon ,

Add this condition also in your query .

oinv.ObjType = rct2.InvType

-Rajesh N

[mk3 · Accepted solution]

Hello,

The query from Nagarajan K. is good. But as Lenon has pointed out, the condition oinv.ObjType = rct2.InvType needs to be added to the query. Users should also be aware that the query does not show those incoming payments where it was posted as payment on account.

With regards,

M.K. Shah

[mk3]

Hii,

Try the below query. It is based on Nagarajan K query above. It will also list the payment on account too.

SELECT DISTINCT T2.[DocNum] AS 'Document Number', T2.[DocDate] AS 'Posting Date', T2.[CardName] AS 'Customer/Vendor Name',T0.[DocNum] as 'Invoice Number',T0.[DOCDATE] as 'invoice date',T0.[Doctotal] as 'Invoice total',T1.[SumApplied],T0.[Doctotal] -T1.[SumApplied] as 'bal. to pay',T2.[DOCTYPE],T2.[DOCTOTAL] AS 'AMOUNT'

FROM [dbo].[OINV] T0 INNER JOIN [dbo].[RCT2] T1 ON T1.[DocEntry] = T0.[DocEntry] AND T0.ObjType = T1.InvType RIGHT OUTER JOIN [dbo].[ORCT] T2 ON T2.[DocNum] = T1.[DocNum]

WHERE T2.[DocDate] >= [%0]

Thanks

M.K. Shah