Skip to end of metadata
Go to start of metadata

(Back to B-FN Financials Module SQL Posted to Wiki Listing -  B-FN Financials Module SQL Posted to Wiki   )

TYPE: Frequently Requested SQL.

AUTHOR NAME: Neetu Dhami

PURPOSE AND BACKGROUND:  This SQL was created in 8.8 PL16.  This query will  show Debtors Ageing Report on specified date entered. It is the basic outline and can be used as base to modify as per customer requirements.

Copy and Paste this SQL:

--B-FN Debtors Aging Report by date Ver 1 ND 2011 05 20

--DESCRIPTION:  This SQL script is for debtors aging reports on specified date and can be modified as required.

--AUTHOR(s):
--Version 1 Neetu Dhami 20 May 2011

SELECT T1.CardCode, T1.CardName, T1.CreditLine, 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.Debit- T0.Credit) 'Balance'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')<=-1),0) 'Future'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>=0 and DateDiff(day, T0.DueDate,'[%1]')<=30),0) 'Current'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>30 and DateDiff(day, T0.DueDate,'[%1]')<=60),0) '31-60 Days'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>60 and DateDiff(day, T0.DueDate,'[%1]')<=90),0) '61-90 Days'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>90 and DateDiff(day, T0.DueDate,'[%1]')<=120),0) '91-120 Days'
     ,ISNULL((SELECT T0.Debit-T0.Credit WHERE DateDiff(day, T0.DueDate,'[%1]')>=121),0) '121+ Days'
FROM JDT1 T0 INNER JOIN OCRD T1 ON T0.ShortName = T1.CardCode
WHERE (T0.MthDate IS NULL OR T0.MthDate > [%1]) AND T0.RefDate <= [%1] AND T1.CardType = 'C'
ORDER BY T1.CardCode, T0.DueDate, T0.Ref1

  • No labels
  1. Guest

    Hi, 

    Thanks for the post, the following error massage is posted when the SQL is run in SQL server.

    Msg 207, Level 16, State 1, Line 15
    Invalid column name '%1'.
    Msg 207, Level 16, State 1, Line 15
    Invalid column name '%1'.
    Msg 207, Level 16, State 1, Line 15

    Invalid column name '%1'.

    Msg 207, Level 16, State 1, Line 15

    Invalid column name '%1'.

    please do correct it.. i am in great need of the correct SQL 

  2. Aslead - the SQL IS correct - Neetu's SQL works perfectly in the OEC test environment.  First, please see number 5 here:  SAP B1 SQL Tips and Tricks .  Second, a hint as to why you are getting error messages - check to see that the brackets around the "invalid column name" of %1 are in the SQL you copied......

    Regards - Zal

  3. Aslead - [%0] Choose from list is applicable inside the SAP only. Try to run in SAP's Query generator to get the correct results!

    Regards,

    Bala

  4. aselad :  [%0] and [%1] are variables used inside SAP query generator. These will display error message when you wun query in SQL.
     
    Specify contant date values in query instead of [%0] and [%1] if you want to execute query in SQL.

  5.  

    This is a brilliant script. But is there a way of making this script as a summary. As im looking to make this detailed aged debtor as a summary aged debtor.