cancel
Showing results for 
Search instead for 
Did you mean: 

Details of Paid Invoices for each Incoming Payment

leon_laikan
Participant
0 Kudos
2,244

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

Accepted Solutions (1)

Accepted Solutions (1)

kothandaraman_nagarajan
Active Contributor

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
Participant
0 Kudos

Hi Nagarajan,

Thanks a lot for your response.

It helped solve my problem.

Best Regards,

Leon Lai

Former Member

Hi Lenon ,

Add this condition also in your query .

oinv.ObjType = rct2.InvType

-Rajesh N

mk3
Participant
0 Kudos

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

Answers (1)

Answers (1)

mk3
Participant
0 Kudos

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