Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
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

10 Comments

  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.

  6. Please note that the above query is semantically incorrect (at least for version 9.0 and later), as it does not consider partial payments or reconciliations.
    All receivables with a partial reconciliation go missing in the above query!

    Example:
    You create an A/R invoice with booking date 01.12.2016 with an amount of 500$.
    The customer pays 100$ on 11.12.2016, so 400$ are still open.
    You perform this query and use 31.12.2016 as parameter date.
    In the query you would expect to see 400$ as open amount for the customer. Instead, you don't see the position at all. The Business One receivables report however shows the open amout of 400$.

    Why it's not working:
    With booking the incoming payment of 100$, the A/R invoice is partially reconciled. Therefore the entry in JDT1 has the MthDate 11.12.2016.
    In the above query, you will only select journal lines where MthDate IS NULL (which is not the case anymore) or with MthDate > 31.12.2016 (which is not the case either).

    How to select correctly:
    For a correct approach you need to consider all reconcilations (tables OITR and ITR1). You can't use the DueAmount from JDT1 either, because it only gives the current due amount, not the one it had back then. So you also need to calculate all due amounts using the manual reconciliations. This can't be done inside a simple SQL in a sane way (incredible high amount of subselects required for each time-case), you need to write a stored procedure (or use the default report from Business One, which is - of course- correct).

     

    So here is my proposal (it's HANA syntax!). It tested it against the standard report and it worked so far. I hope there are no mistakes while reducing the additional fields back to the original examples. I give no guarantees. Use it on your own risk.

     

    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."Debit"- T0."Credit"-COALESCE(T5."ReconSum",0)) "Balance"
       ,(CASE WHEN days_between(T0."DueDate",'[%1]')<=0 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "Future"
       ,(CASE WHEN days_between(T0."DueDate",'[%1]')>0 AND days_between(T0."DueDate",'[%1]')<=30 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "Current"
       ,(CASE WHEN days_between(T0."DueDate",'[%1]')>30 AND days_between(T0."DueDate",'[%1]')<=60 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "31-60 Days"
       ,(CASE WHEN days_between(T0."DueDate",'[%1]')>60 AND days_between(T0."DueDate",'[%1]')<=90 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "61-90 Days"
       ,(CASE WHEN days_between(T0."DueDate",'[%1]')>90 AND days_between(T0."DueDate",'[%1]')<=120 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "91-120 Days"
       ,(CASE WHEN days_between(T0."DueDate",'[%1]')>120 THEN T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0) ELSE 0 END) "121+ Days"
    FROM JDT1 T0
    INNER JOIN OCRD T1 ON T0."ShortName" = T1."CardCode"
    INNER JOIN OJDT T2 ON T0."TransId" = T2."TransId"
    LEFT JOIN (
     SELECT T4."ShortName", T4."TransId", SUM( T4."ReconSum" * CASE WHEN T4."IsCredit" = 'D' THEN 1 ELSE -1 END ) AS "ReconSum"
     FROM OITR T3
     INNER JOIN ITR1 T4 ON T3."ReconNum" = T4."ReconNum"
     WHERE T3."ReconDate" <= [%1]
     GROUP BY T4."ShortName", T4."TransId"
    ) T5 ON T0."TransId" = T5."TransId" AND T0."ShortName" = T5."ShortName"
    WHERE T0."RefDate" <= [%1] AND T1."CardType" = 'C'
    AND (T0."Debit"-T0."Credit"-COALESCE(T5."ReconSum",0)) <> 0
    ORDER BY T1."CardCode", T0."DueDate", T0."Ref1"

     
  7. Hi

    I just wanted to say thank you for posting this. I have spent the last two months trying to analyse and join tables using the sales transactions tables. This has now clarified where I should go and how I can analyse historic customer payment performance for which the system seems to be lacking.

  8. Test below query and works perfectly for me on SAP B1 9.1

    Thanks 

    Raj Rawat

    ---------------------------------------------

    Select cardcode, cardname , ref1, 

    balduedeb-balduecred as "Total Balance",
    Case When DATEDIFF(DD,DUEDATE,current_timestamp) < 0 then balduedeb-balduecred End as 'Future',
    Case When DATEDIFF(DD,DUEDATE,current_timestamp) >=0 and DATEDIFF(DD,DUEDATE,current_timestamp) <=30 then balduedeb-balduecred End as '1-30 Days',
    Case When DATEDIFF(DD,DUEDATE,current_timestamp) >=31 and DATEDIFF(DD,DUEDATE,current_timestamp) <=60 then balduedeb-balduecred End as '31-60 Days',
    Case When DATEDIFF(DD,DUEDATE,current_timestamp) >=61 and DATEDIFF(DD,DUEDATE,current_timestamp) <=90 then balduedeb-balduecred End as '61-90 Days',
    Case When DATEDIFF(DD,DUEDATE,current_timestamp) >=91 and DATEDIFF(DD,DUEDATE,current_timestamp) <=120 then balduedeb-balduecred End as '91-120 Days',
    Case When DATEDIFF(DD,DUEDATE,current_timestamp) >=121 then balduedeb-balduecred End as '121+ Days',
    duedate, case WHEN TransType=13 THEN 'Invoice'
    WHEN TransType=14 THEN 'Credit Note'
    WHEN TransType=30 THEN 'Journal'
    WHEN TransType=24 THEN 'Receipt' end as "Document Type", refdate
    from (SELECT t1.cardcode,t1.cardname, t0.* FROM JDT1 T0 inner join OCRD T1 on t0.shortname = t1.cardcode WHERE t1.cardtype = 'C') as d where balduedeb <> 0 or balduecred <> 0

    1. This works perfectly if I run this query with the current day, as you do. If I change the current_timestamp to for example '20170430', the result data is not the same as the SAP

  9. Thanks for sharing!