Skip to Content

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

Customer aging report

Hi All,

I need to get a customer aging report through a query. showing open invoices and credits.report should contain

customercode, customername, type, doc no, posting date, due date, bp ref no, balance due.  Can someone show me the query how to get this information please.  In they query i should be able to pass the customer code.

Tags:
replied

Hi,

Try this

SELECT tt.[Cust Num],

       tt.[Cust Name],

       tt.[Cust Group],

       tt.[Posting Date],

    tt.[Due Date],

       SUM(tt.[0-30 Days]) AS [0-30 Days],

       SUM(tt.[31 to 60 Days]) AS [31 to 60 Days],

       SUM(tt.[61 to 90 days]) AS [61 to 90 Days],

       SUM(tt.[90 to 120 Days]) AS [91 to 120 Days],

       SUM(tt.[120 Plus Days]) AS [120 Plus Days]

FROM   (

           SELECT T1.CardCode AS 'Cust Num',

                  T1.CardName AS 'Cust Name',

                  T2.GroupName AS 'Cust Group',

                  T0.BalDueDeb AS 'Debit Amt',

                  T0.BalDueCred * -1 AS 'Credit Amt',

                  CASE

                       WHEN T0.TransType = 13 THEN 'AR Invoice'

                       WHEN T0.TransType = 14 THEN 'AR Cred Memo'

                       WHEN T0.TransType = 24 THEN 'Payment'

                       ELSE 'Other'

                  END AS 'Trans Type',

                  T0.Ref1 AS 'Reference',

                  FcCurrency AS 'Currency',

                  CONVERT(VARCHAR(10), RefDate, 103) 'Posting Date',

                  CONVERT(VARCHAR(10), DueDate, 103) 'Due Date',

                  CONVERT(VARCHAR(10), TaxDate, 103) 'Doc Date',

                  CASE

                       WHEN (DATEDIFF(DD, RefDate, CURRENT_TIMESTAMP)) + 1 < 31 THEN CASE

                                                                                          WHEN

                                                                                               balduecred

                                                                                               <

                                                                                               >

                                                                                               0 THEN

                                                                                               balduecred

                                                                                               *

                                                                                               -

                                                                                               1

                                                                                          ELSE

                                                                                               balduedeb

                                                                                     END

                  END AS '0-30 Days',

                  CASE

                       WHEN (DATEDIFF(DD, RefDate, CURRENT_TIMESTAMP)) + 1 > 30

           AND (DATEDIFF(DD, RefDate, CURRENT_TIMESTAMP)) + 1 < 61 THEN

               CASE

                    WHEN balduecred < > 0 THEN balduecred * -1

                    ELSE balduedeb

               END

               END AS '31 to 60 Days',

           CASE

                WHEN (DATEDIFF(DD, RefDate, CURRENT_TIMESTAMP)) + 1 > 60

           AND (DATEDIFF(DD, RefDate, CURRENT_TIMESTAMP)) + 1 < 91 THEN

               CASE

                    WHEN balduecred < > 0 THEN balduecred * -1

                    ELSE balduedeb

               END

               END AS '61 to 90 days',

           CASE

                WHEN (DATEDIFF(DD, RefDate, CURRENT_TIMESTAMP)) + 1 > 90

           AND (DATEDIFF(DD, RefDate, CURRENT_TIMESTAMP)) + 1 < 121 THEN

               CASE

                    WHEN balduecred < > 0 THEN balduecred * -1

                    ELSE balduedeb

               END

               END AS '90 to 120 Days',

           CASE

                WHEN (DATEDIFF(DD, RefDate, CURRENT_TIMESTAMP)) + 1 > 120 THEN CASE

                                                                                    WHEN

                                                                                         balduecred

                                                                                         <

                                                                                         >

                                                                                         0 THEN

                                                                                         balduecred

                                                                                         *

                                                                                         -

                                                                                         1

                                                                                    ELSE

                                                                                         balduedeb

                                                                               END

           END AS '120 Plus Days'

          

           FROM JDT1 T0

          

           INNER JOIN OCRD T1

          

           ON T0.ShortName = T1.CardCode

      AND T1.CardType = 'C'

  INNER JOIN OCRG T2 ON T1.GroupCode = T2.GroupCode

          

           WHERE

          

           T0.IntrnMatch = '0'

           AND T0.BalDueDeb != T0.BalDueCred

       ) AS tt

GROUP BY

       tt.[Cust Num],

       tt.[Cust Name],

      

       tt.[Cust Group],

      

       tt.[Posting Date],

      

    tt.[Due Date]

Regards,

SP Samy

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question