cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

What I notice is the aging shows reconciled amounts that net to zero I had though becasue of the logic below it would not due that. And that seems to be the issue between the aging buckets in my query and what shows in the canned report in SAP business one. Can anyone help?? 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, LastPmtDate, LastPmtAmt  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(900)) 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' , t.DocDate AS LastPmtDate , t.[DocTotalSy] AS LastPmtAmt FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode Left outer join  OCTG T2 On T1.GroupNum = T2.GroupNum LEFT JOIN (SELECT MAX(DocEntry) AS LastPmt, CardCode FROM ORCT GROUP BY CardCode) p ON T1.CardCode = p.CardCode LEFT JOIN ORCT t ON p.CardCode = t.CardCode AND p.LastPmt = t.DocEntry WHERE  T1.CardType = 'C'  and BalSccred + balscdeb <>0) T100 GROUP BY CARDCODE, CARDNAME, PymntGroup, Rmk, LastPmtDate, LastPmtAmt ORDER BY CARDCODE

JoergAldinger
Active Contributor
0 Kudos

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

Joerg.

Former Member
0 Kudos

Thank you. This does work!! Amazing as now I went account by account and it matches for all but multi currency which is fine. This is really helpful as I spent all day trying to figure this out!!

Former Member
0 Kudos

The one question I have is there a way to ask for as of date , so it shows you as of this date what the balance was for each bucket?

Answers (4)

Answers (4)

Former Member
0 Kudos

The issue is the balance seems correct but I want the aging buckets to match what is in SAP on the aging report. IT seem like noone can match the aging report that comes from the system. Let me know if that is possible. This query also has other info beside the aging but that should not be an issue. Thanks Danielle

Former Member
0 Kudos

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, LastPmtDate, LastPmtAmt  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' , t.DocDate AS LastPmtDate , t.[DocTotalSy] AS LastPmtAmt FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode Left outer join  OCTG T2 On T1.GroupNum = T2.GroupNum LEFT JOIN (SELECT MAX(DocEntry) AS LastPmt, CardCode FROM ORCT GROUP BY CardCode) p ON T1.CardCode = p.CardCode LEFT JOIN ORCT t ON p.CardCode = t.CardCode AND p.LastPmt = t.DocEntry WHERE  T1.CardType = 'C'  and BalSccred + balscdeb <>0) T100 GROUP BY CARDCODE, CARDNAME, PymntGroup, Rmk, LastPmtDate, LastPmtAmt ORDER BY CARDCODE

KennedyT21
Active Contributor
0 Kudos

Hi Danielle ,

The query what you have posted is not excuting,

Regards

JoergAldinger
Active Contributor
0 Kudos

Hi Danielle:

This should solve your problem:

SELECT

  T1.CardCode AS "CardCode",

  T1.CardName AS "CardName",

  SUM(T0.BalScDeb - T0.BalScCred) AS "Balance",

  SUM(CASE WHEN DATEDIFF(day, T0.DueDate, GETDATE()) < 0 THEN T0.BalScDeb - T0.BalScCred ELSE 0 END) AS "Future",

  SUM(CASE WHEN DATEDIFF(day, T0.DueDate, GETDATE()) BETWEEN 0 AND 30 THEN T0.BalScDeb - T0.BalScCred ELSE 0 END) AS "0-30",

  SUM(CASE WHEN DATEDIFF(day, T0.DueDate, GETDATE()) BETWEEN 31 AND 60 THEN T0.BalScDeb - T0.BalScCred ELSE 0 END) AS "31-60",

  SUM(CASE WHEN DATEDIFF(day, T0.DueDate, GETDATE()) BETWEEN 61 AND 90 THEN T0.BalScDeb - T0.BalScCred ELSE 0 END) AS "61-90",

  SUM(CASE WHEN DATEDIFF(day, T0.DueDate, GETDATE()) BETWEEN 91 AND 120 THEN T0.BalScDeb - T0.BalScCred ELSE 0 END) AS "91-120",

  SUM(CASE WHEN DATEDIFF(day, T0.DueDate, GETDATE()) > 120 THEN T0.BalScDeb - T0.BalScCred ELSE 0 END) AS "121+"

FROM

  JDT1 T0

  INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode

WHERE

  BalScDeb <> BalScCred

GROUP BY

  T1.CardCode, T1.CardName

Let me know...

Joerg.