check paid invoices

[former_member267979]

Hello,

my goal is check if customer paid invoice. I built the query based on customer CardCode, Journal and Invoce:

SELECT
T0.CardCode AS 'CustomerCode',T1.TransId AS 'Transaction ID'
, T2.BaseRef AS 'Invoice No.'
, T2.Number AS 'Journal No.'
, T2.DueDate AS 'Due Date'
, T1.Debit
--,SUM(T1.Credit)
, T3.U_NAME AS 'Billed'
FROM OCRD T0
INNER JOIN JDT1 T1 ON T1.ShortName = T0.CardCode
INNER JOIN OJDT T2 ON T2.TransId = T1.TransId
LEFT JOIN OUSR T3 ON T3.USERID = T1.UserSign
LEFT JOIN VPM2 T4 ON T4.DocNum = T2.BaseRef    
LEFT JOIN OINV T5 ON T5.DocEntry = T4.DocEntry

As is common the Debit goes from one cell

but sometime the Credit is formed from more cells, so I have to count it via SUM

example:

BaseRef Debit          Credit
160750	195.300000	0.000000
160750	0.000000	32.550000
160750	0.000000	90.000000
160750	0.000000	63.400000
160750	0.000000	9.350000
160751	184.500000	0.000000
160751	0.000000	30.750000
160751	0.000000	144.400000
160751	0.000000	9.350000

and this is exactly what makes me a problem, get the value of Credit

Thanks for any Hint

Regards ZAJDAN

[kothandaraman_nagarajan · Accepted solution]

Hi,

Please refer thread https://archive.sap.com/discussions/thread/3439126

Regards,

Nagarajan

[kothandaraman_nagarajan]

Please close this thread by assigning best answer. Thanks

[kothandaraman_nagarajan]

Please share screen of query result here.

[former_member267979]

the problem was that I was connected in test database and there is not incoming payment

In real database it works

thanks

[former_member267979]

Thanks for the link....

right now I am trying the query directly in SAP, but I am facing a problem when appear window to set Posting Date, so I am not able to pass the Date. Data is not available.