Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Aging Report gives correct balance, but does not show payments on account correctly

Can someone help me fix my query so the aging buckets are reflected correctly. Currently when there is a balance on account, the amount is not correct for the balance on account. Then the totals per bucket do not match the balance and what shows on the BP system report in SAP. Is this a simple fix? Below is what I have which is aging summary with remarks and payment terms. SELECT CardCode, CardName, PymntGroup, SUM(Balance) BALANCE, SUM(A) FUTURE, SUM(B) '0-30', SUM(C) '31-60', SUM(D) '61-90', SUM(E) '91-120', SUM(F) '121+',Rmk FROM ( SELECT T1.CardCode, T1.CardName, T2.[PymntGroup], T0.RefDate, T0.Ref1 'Document_Number',    CASE  WHEN T0.TransType=13 THEN 'Invoice'          WHEN T0.TransType=14 THEN 'Credit Note'          WHEN T0.TransType=30 THEN 'Journal'          WHEN T0.TransType=24 THEN 'Receipt'          END AS 'Document_Type',    T0.DueDate, (T0. [BalScDeb]- T0.[BalScCred]) 'Balance' , CAST(T1. [Free_Text]  as varchar(250)) as Rmk    ,ISNULL((SELECT T0.[SYSDeb] - T0.[SYSCred] WHERE DateDiff(day, T0.DueDate, getdate())=0 and DateDiff(day, T0.DueDate,getdate())30 and DateDiff(day, T0.DueDate,getdate())60 and DateDiff(day, T0.DueDate,getdate())90 and DateDiff(day, T0.DueDate,getdate())=121),0) 'F' FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode Left outer join  OCTG T2 On T1.GroupNum = T2.GroupNum WHERE  T1.CardType = 'C'  and BalSccred + balscdeb <>0 ) T100 GROUP BY CARDCODE, CARDNAME, PymntGroup, Rmk ORDER BY CARDCODE


Have you ever tried the query I posted earlier? It should give you the correct results.


0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question