Skip to Content
0

check paid invoices

Feb 07, 2017 at 10:12 PM

68

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Nagarajan K Feb 08, 2017 at 09:05 AM
1

Hi,

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

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded
Zdenek Chladek Feb 08, 2017 at 12:05 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Nagarajan K Feb 08, 2017 at 12:54 PM
0

Please share screen of query result here.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

In real database it works

thanks

0
Nagarajan K Feb 08, 2017 at 02:04 PM
0

Please close this thread by assigning best answer. Thanks

Share
10 |10000 characters needed characters left characters exceeded