Skip to Content

check paid invoices

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    Feb 08, 2017 at 09:05 AM

    Hi,

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

    Regards,

    Nagarajan

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 08, 2017 at 12:05 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 08, 2017 at 12:54 PM

    Please share screen of query result here.

    Add comment
    10|10000 characters needed characters exceeded

  • Feb 08, 2017 at 02:04 PM

    Please close this thread by assigning best answer. Thanks

    Add comment
    10|10000 characters needed characters exceeded