cancel
Showing results for 
Search instead for 
Did you mean: 

Incoming Payments Query

Former Member
0 Kudos

Hi All,

I am trying to get a SQL query to track incoming payments. Basically I would need to display the same information contained in the incoming payments windows.

Headers: Code, DocNum,

Lines: Document No. Instalment, Date, Due Date, Overdue Date, Total Payment

I know that most of the information can be extracted from OINV, ORCT and RCT2 but I am having problems trying to figure out the fields, Date, Due Date, Overdue Date.

Can anybody help me to create a query that displays this information?

Thanks

Julian

Accepted Solutions (1)

Accepted Solutions (1)

KennedyT21
Active Contributor
0 Kudos

Hi Julian Matias...

Try With This Query

SELECT T0.DOCDATE AS INVOICEDATE,T0.DOCNUM AS INVOICENO,T0.CARDNAME AS CUSTOMERNAME,T0.DOCTOTAL AS INVOICETOTAL,T0.DOCTOTAL-Isnull(T1.SumApplied,0) AS PENDINGAMT,T2.DOCDATE AS INCPAYDATE,T2.DOCNUM INCPAYNO

FROM OINV T0 LEFT JOIN RCT2 T1 ON T0.DOCENTRY=T1.DOCENTRY

AND T0.OBJTYPE=T1.INVTYPE LEFT JOIN ORCT T2 ON T2.DOCNUM=T1.DOCNUM AND T2.DocDate<=[%0]

WHERE T0.DocDate<=[%0] AND T0.DOCTOTAL>Isnull(T1.SumApplied,0)

Hope Helpful

Regards

Kennedy


Former Member
0 Kudos

Hi Kennedy,

Thanks, your query is very helpful but not exactly what I am trying to accomplish. Basically I would like to get a view like the incomming payments window in SAP:

For example, the Invoice number has been split in two incoming payments due to the payment terms (30:60). This is what I am trying to achieve.

Kind Regards

Julian

Answers (3)

Answers (3)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

If you got the answer, please close the thread by marking correct/helpful answer.

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hi Julian,

Try this first:

SELECT T0.CardCode, T2.DocNum 'Payment#',T0.[DocNum] 'Invoice#', T1.InstID,T0.DocDate 'Invoice Date',  T0.DocTotal 'Invoice Amount', T2.DocDate 'Pay Date', T2.DocDueDate, T1.[SumApplied] 'Total Pay'ment  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.Canceled='N'  AND T0.Canceled='N' AND T0.CardName LIKE '[%0]%'

Order By T0.CardCode,T0.[DocDate] Desc

For overdue, you need to give your screenshot.

.

Thanks,

Gordon

Former Member
0 Kudos

Thanks Gordon,

Your query is very useful too, but I think it works better with left outer joins in my case, since I want to see what amount is due, even when there is no incoming payment. Also, as I replied to Kennedy, what I am trying to achieve is a similar view to the incoming payments window in SAP. Please see image below:

For example, the Invoice number has been split in two incoming payments due to the payment terms (30:60). This is what I am trying to achieve.

Kind Regards

Julian

P.S.: I would like to thank you for answering so many questions in the forum. Your answers has been useful for me in many situations.


Former Member
0 Kudos

I don't have the data to test. Try this update:

SELECT T0.CardCode 'Customer Code', T0.CardName 'Customer Name', T2.DocNum 'Payment#',T2.DocDate 'Pay Date',T0.[DocNum] 'Invoice#', T1.InstID,T0.DocDate 'Invoice Date',   T0.DocDueDate, DateDiff(d,T0.DocDueDate,T2.DocDate) 'Overdue Days',T0.DocTotal 'Total', [SumApplied] 'Total Payment'

FROM  [dbo].[OINV] T0  LEFT JOIN [dbo].[RCT2] T1  ON  T1.[DocEntry] = T0.[DocEntry]    LEFT JOIN [dbo].[ORCT] T2  ON  T2.[DocNum] = T1.[DocNum]   WHERE T2.Canceled='N'  AND T0.Canceled='N' AND T0.CardName LIKE '[%0]%'

Order By T0.CardCode,T0.[DocDate] Desc

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this:

SELECT T0.[DocNum], T0.[CardCode], T2.[DocNum], T1.[InstId], T2.[DocDueDate], T2.[DocDate], datediff(dd,T0.[DocDate] , T2.[DocDate]) as overdue, T1.[PaidSum] FROM ORCT T0  INNER JOIN RCT2 T1 ON T0.DocEntry = T1.DocNum left join OINV T2 on  T2.[DocNum]  = T1.[baseAbs]