on 04-15-2011 7:22 PM
Hello all,
I have SAP Business One v 2007 A in my company. The auditors are coming in next week and I was trying to get the Aging report for both vendor and customer. The default summary looks good. To get the details, I have to go into each customer/vendor and collect them. Is there a way to get all the details in one shot (like an executing an SQL)?
Thank you all so much.
Manoj.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Thanks Bala for your quick responses. I looked into the links.
(1) We do not have 8.8 yet.
(2) The query samples does not help me get the details - similar to what I see in the default Ageing report.
Is there any SQL that is available. I checked the forum and could not get a breakthrough.
Thanks again,
Manoj.
Hello Manoj - maybe try this since it uses the actual General Journal entries found in the JDT1 table. PS - I have no idea why it does not show the NOT EQUAL characters of < and > in the last CASE. The CASE calculation should be the same in all instances - make sure you add that before running. I used the appropriate coding for the SQL - something about SAP forums!!!
For Vendors, just change the CardType to S in the WHERE clause, the Transtype, and change the column headings to what you want them to say.
Hope this helps...
Regards - Zal
--F-BP AR Aging Report Detail Ver 1 ZP 2011 04 16
--DESCRIPTION: SQL uses General Ledger Detail to create an Aging Report which shows
actual invoices, credit memos, and payments by reference date in the JDT1 table.
--AUTHOR(s):
--Version 1 Zal Parchem 16 April 2011
SELECT
T1.CardCode AS 'Cust Num',
T1.CardName AS 'Cust Name',
T0.SysDeb AS 'Debit Amt',
T0.SysCred * -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 SysCred < > 0 THEN SysCred * -1
ELSE SysDeb
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 SysCred < > 0 THEN SysCred * -1
ELSE SysDeb
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 SysCred < > 0 THEN SysCred * -1
ELSE SysDeb
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 SysCred < > 0 THEN SysCred * -1
ELSE SysDeb
END
END AS '90 to 120 Days',
CASE
WHEN (DATEDIFF(DD,RefDate,Current_Timestamp)) +1 > 120
THEN
CASE
WHEN SysCred != 0 THEN SysCred * -1
ELSE SysDeb
END
END AS '120 Plus Days'
FROM JDT1 T0
INNER JOIN OCRD T1
ON T0.ShortName = T1.CardCode
AND T1.CardType = 'C'
WHERE
T0.IntrnMatch = '0'
AND T0.BalDueDeb != T0.BalDueCred
ORDER BY
T1.CardCode,
T0.TaxDate
FOR BROWSE
I have been looking for this functionality for years. I tried your script and made a few corrections and now it matches the SAP aging report to the penny.
The changes I made were:
1. REM 3rd line
2. Replaced SysDeb with BalDueDeb
3. Replaced SysCred with BalDueCred
Thank you thank you thank you!
--F-BP AR Aging Report Detail Ver 1 ZP 2011 04 16
--DESCRIPTION: SQL uses General Ledger Detail to create an Aging Report which shows
--actual invoices, credit memos, and payments by reference date in the JDT1 table.
--AUTHOR(s):
--Version 1 Zal Parchem 16 April 2011
SELECT
T1.CardCode AS 'Cust Num',
T1.CardName AS 'Cust Name',
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'
WHERE
T0.IntrnMatch = '0'
AND T0.BalDueDeb != T0.BalDueCred
ORDER BY
T1.CardCode,
T0.TaxDate
FOR BROWSE
Glad to hear that Rich - you are more than welcome, that is what the forum is all about. You might want to kick around here a bit also - some folks are starting to get some good SQL together at this Wiki (another area for good SQL):
http://wiki.sdn.sap.com/wiki/display/B1/SAPB1SQLTipsand+Tricks
Regards - Zal
I see some of your code with not equal is not showing either - I wonder when SAP will get that fixed???
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
91 | |
10 | |
10 | |
6 | |
5 | |
5 | |
5 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.