SAP B1 crystal report: Customer Receivable Aging report

[Former Member]

Hi All, I want to write a Customer Receivable Aging report in crystal report. I was using query to union open AR crdit memo and AR invoice. but, I foggot to add incomming payment ORCT. I really have trouble to get Customer Receivable Aging report . Can anyone help me? thanks Is that I did in wrong direction?

SELECT OINV.DocEntry,OINV.DocNum,OINV.ObjType,OINV.CardCode,OCRD.CntctPrsn,OCRD.fax,OINV.NumAtCard,OINV.Address,OCTG.PymntGroup

,OINV.CardName,OINV.DocDate,OINV.DocDueDate,OINV.DocCur,OINV.DocTotalFC,

OINV.DocTotal, (OINV.DocTotal-OINV.PaidToDate) as AmountAUD

FROM SAPLIVE.dbo.OINV OINV join SAPLIVE.dbo.OCTG OCTG

On OINV.GroupNum=OCTG.GroupNum

join SAPLIVE.dbo.OCRD OCRD

On OINV.CardCode=OCRD.CardCode

WHERE OINV.CANCELED='N' AND OINV.DocStatus='O' AND (OINV.DocTotal-OINV.PaidToDate)>0

union

SELECT ORIN.DocEntry,ORIN.DocNum,ORIN.ObjType,ORIN.CardCode,OCRD.CntctPrsn,OCRD.fax,ORIN.NumAtCard,ORIN.Address,OCTG.PymntGroup

,ORIN.CardName,ORIN.DocDate,ORIN.DocDueDate,ORIN.DocCur,ORIN.DocTotalFC,

ORIN.DocTotal,(ORIN.DocTotal-ORIN.PaidToDate)as AmountAUD

FROM SAPLIVE.dbo.ORIN ORIN join SAPLIVE.dbo.OCTG OCTG

On ORIN.GroupNum=OCTG.GroupNum

join SAPLIVE.dbo.OCRD OCRD

On ORIN.CardCode=OCRD.CardCode

WHERE ORIN.CANCELED='N' AND ORIN.DocStatus='O' AND (ORIN.DocTotal-ORIN.PaidToDate)

[Former Member · Accepted solution]

Hi,

It is not an easy job to create this report because you have to get all transaction upto the date you need aging. Current document is not the source for it unless you always run aging as of the current day.

Thanks,

Gordon

[Former Member · Accepted solution]

Hi Gordon,thanks for your help. I just want current ageing report for each customer with A/R invoices and credit memo and incoming payment. I really don't know where to start. Help!!

[zal_parchem2 · Accepted solution]

Hello Julian - maybe you can start with the JDT1 table which contains all of the General Ledger Journal Entries...

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

[Private_Member_135681 · Accepted solution]

Hi Zal,

I've been looking at trying to create a aging report too, so this looks very interesting.

How do you make use of the SQL you've posted ?

[zal_parchem2 · Accepted solution]

Hello Car...

The most important thing about this SQL is that it picks up directly from the journal entries created on the date it is run, so the UNION statements are not needed.

The SQL is NOT a substiture for the AR Aging functionality found in version 8.8, but a stop gap measure/supplement for those using version 2007. The AR clerk runs the normal AR Aging in 2007 and exports it to Excel - then she immediately runs the SQL and exports that to Excel also. Instead of double-clicking each and every line, she is able to use the SQL results in her Excel worksheet to verify/research the Aging for her weekly collections call.

There are only three transaction types in the SQL this Customer uses, but depending upon what your company might use for AR transacting, there might be more "TransType" in the CASE statement. That is why I mentioned to Julian it could be a start. If someone wanted to have the report age according to an input date (like the AR Aging function), the SQL would be somewhat different and I have not done that, but as said before, it is a supplement research tool, not a substitute for the functionalilty.

Hope that helps in explaining it a bit better...

Regards - Zal

[zal_parchem2 · Accepted solution]

Julian and Car - a small update to some information I gathered due to this request...

here is a new list of some transaction types you might want to look at when using the JDT1 table - the list might not be complete but I tried every combination/transaction I know to generate it:

http://wiki.sdn.sap.com/wiki/display/B1/SAPB1SQLB-FNJournalEntryDetailbyDate+Range

Hope this helps - Regards - Zal

[Former Member]

Have you checked the standard SAP crystal report? You may be able to amend this to suit?