Skip to Content
avatar image
Former Member

SAP B1 crystal report: Customer Receivable Aging report

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)

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Apr 15, 2011 at 03:46 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 16, 2011 at 07:55 AM

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

    Add comment
    10|10000 characters needed characters exceeded