How To Modify SAP Aging Report ?

[Former Member]

Dear Experts,

Greetings !!!

At Present, As Per SAP Business One's Standard Report For Aging, It Deducts Amount Received From Any Customer From The Dues Of That Particular Period...In Spite Of Its Previous Pending Dues...

For Example,

One Customer's Outstandings For More That 220 Days Are Rs. 4,85,382 And We Received An Amount Of Rs. 3,00,770 On October 1, 2012 Which Sap Considers In The Bucket Of 60-90 Days And Deducts The Same From Dues Of 60-90 Days Column...i.e. Rs.3,00,770 Is Shown In The Column Of 60-90 Days...

So, Here Instead Of Deducting Rs. 3,00,770 From The Oldest Dues Of More Than 220 Days, i.e Rs.4,85,382, It Deducts From Dues Of 60-90 Days' Period...

So, Can We Have Some Modification In Query To Get The Desired Output....That Is Can We Put Some Condition In The Query Which Checks The Most Oldest Dues Of That Particular Customer And Automatically Deducts The Amount Of Receipt From The Most Oldest Dues Of That Customer?

I Am Using Below Mentioned Query Received From You For The Aging Report :

Please Do Suggest Necessary Modifications In The Below Mentioned Query...

Dear Experts, We Expect Your Kind Cooperation..!!!!!

Thanks In Advance....

Regards,

Raaj kumar.

select 1, T0.shortname,T2.cardcode 'Customer Code',T2.cardname 'Name',  sysdeb 'Debit Amount',syscred 'Credit Amount',

case T0.transtype

when '13' then 'INV'

when '14' then 'AR CN'

when '24' then 'INCOMING'

else 'Other'

end 'Type',

T1.BaseRef'Trans #',

case T0.transtype

when '13' then

(Select Comments from OINV where OINV.Transid=T1.Transid)

else '-'

end 'Inv.Rem.',

(Select SeriesName From NNM1 Where Series=T1.DocSeries and ObjectCode=T0.TransType)'Series',

T0.Ref1,

fccurrency 'BP Currency',

CONVERT(VARCHAR(10), T0.refdate, 103)'Posting Date' ,

CONVERT(VARCHAR(10), T0.duedate, 103) 'Due Date',

CONVERT(VARCHAR(10), T0.taxdate, 103) 'Doc Date' ,

CASE

when (DATEDIFF(dd,T0.refdate,current_timestamp))+1 < 31

then

case

when syscred <> 0 then syscred * - 1

else sysdeb

end

end "0-30 days",

case when ((datediff(dd,T0.refdate,current_timestamp))+1 > 30

and (datediff(dd,T0.refdate,current_timestamp))+1< 61)

then

case

when syscred <> 0 then syscred * - 1

else sysdeb

end

end "31 to 60 days",

case when ((datediff(dd,T0.refdate,current_timestamp))+1 > 60

and (datediff(dd,T0.refdate,current_timestamp))+1< 91)

then

case

when syscred <> 0 then syscred * - 1

else sysdeb

end

end "60 to 90 days",

case when ((datediff(dd,T0.refdate,current_timestamp))+1 > 90

and (datediff(dd,T0.refdate,current_timestamp))+1< 121)

then

case

when syscred <> 0 then syscred * - 1

else sysdeb

end

end "91 to 120 days",

case when ((datediff(dd,T0.refdate,current_timestamp))+1 > 120

and (datediff(dd,T0.refdate,current_timestamp))+1< 151)

then

case

when syscred <> 0 then syscred * - 1

else sysdeb

end

end "121 to 150 days",

case when ((datediff(dd,T0.refdate,current_timestamp))+1 > 150

and (datediff(dd,T0.refdate,current_timestamp))+1< 181)

then

case

when syscred <> 0 then syscred * - 1

else sysdeb

end

end "151 to 180 days",

case when ((datediff(dd,T0.refdate,current_timestamp))+1 > 180

and (datediff(dd,T0.refdate,current_timestamp))+1< 221)

then

case

when syscred <> 0 then syscred * - 1

else sysdeb

end

end "181 to 220 days",

CASE

when (DATEDIFF(dd,T0.refdate,current_timestamp))+1 > 220

then

case

when syscred= 0 then sysdeb

when sysdeb= 0 then syscred * - 1

end

end "220 + days"

from JDT1 T0

Inner Join OJDT T1 On T1.TransId=T0.TransId

left outer join

OCRD T2 ON T2.cardcode =T0.shortname left outer join oinv t3 on T3.TransId = T1.TransId

where T2.cardtype = 'c' and T0.intrnmatch = '0'

and (T0.BalDueCred  + T0.BalDueDeb) > 0

ORDER BY T2.CARDCODE, T0.taxdate

[Former Member]

Use this Query This may help you for ON Account Payment.

select 1, T0.shortname,T2.cardcode 'Customer Code',T2.cardname 'Name', T0.BalDueDeb  as Bal_Due_Deb, sysdeb 'Debit Amount',T0.BalDueCred  AS Bal_Due_Cre, syscred 'Credit Amount',

case T0.transtype when '13' then 'INV'

when '14' then 'AR CN'

when '24' then 'INCOMING'

else 'Other'

end 'Type', T1.BaseRef'Trans #',

case T0.transtype

when '13' then

(Select Comments from OINV where OINV.Transid=T1.Transid)

else '-'

end 'Inv.Rem.',

(Select SeriesName From NNM1 Where Series=T1.DocSeries and ObjectCode=T0.TransType)'Series',

T0.Ref1,

fccurrency 'BP Currency',

CONVERT(VARCHAR(10), T0.refdate, 103)'Posting Date' ,

CONVERT(VARCHAR(10), T0.duedate, 103) 'Due Date',

CONVERT(VARCHAR(10), T0.taxdate, 103) 'Doc Date' ,

CASE

when (DATEDIFF(dd,T0.refdate,current_timestamp))+1 < 31 then

case

when T0.BalDueCred <> 0 then T0.BalDueCred * - 1 else T0.BalDueDeb

end

end "0-30 days",

case when ((datediff(dd,T0.refdate,current_timestamp))+1 > 30

and (datediff(dd,T0.refdate,current_timestamp))+1< 61) then

case

when T0.BalDueCred <> 0 then T0.BalDueCred * - 1 else T0.BalDueDeb

end

end "31 to 60 days",

case when ((datediff(dd,T0.refdate,current_timestamp))+1 > 60

and (datediff(dd,T0.refdate,current_timestamp))+1< 91)

then

case

when T0.BalDueCred <> 0 then T0.BalDueCred * - 1 else T0.BalDueDeb

end

end "60 to 90 days",

case when ((datediff(dd,T0.refdate,current_timestamp))+1 > 90

and (datediff(dd,T0.refdate,current_timestamp))+1< 121)

then

case

when T0.BalDueCred <> 0 then T0.BalDueCred * - 1 else T0.BalDueDeb

end

end "91 to 120 days",

case when ((datediff(dd,T0.refdate,current_timestamp))+1 > 120

and (datediff(dd,T0.refdate,current_timestamp))+1< 151) then

case

when T0.BalDueCred <> 0 then T0.BalDueCred * - 1 else T0.BalDueDeb

end

end "121 to 150 days",

case when ((datediff(dd,T0.refdate,current_timestamp))+1 > 150

and (datediff(dd,T0.refdate,current_timestamp))+1< 181)

then

case

when T0.BalDueCred <> 0 then T0.BalDueCred * - 1

else T0.BalDueDeb

end

end "151 to 180 days",

case when ((datediff(dd,T0.refdate,current_timestamp))+1 > 180

and (datediff(dd,T0.refdate,current_timestamp))+1< 221)

then

case

when T0.BalDueCred <> 0 then T0.BalDueCred * - 1

else T0.BalDueDeb

end

end "181 to 220 days",

CASE

when (DATEDIFF(dd,T0.refdate,current_timestamp))+1 > 220

then

case

when T0.BalDueCred= 0 then T0.BalDueDeb

when T0.BalDueDeb= 0 then T0.BalDueCred * - 1

end

end "220 + days"

from JDT1 T0

Inner Join OJDT T1 On T1.TransId=T0.TransId

left outer join OCRD T2 ON T2.cardcode =T0.shortname

left outer join oinv t3 on T3.TransId = T1.TransId

where T2.cardtype = 'c' and T0.intrnmatch = '0'

and (T0.BalDueCred  + T0.BalDueDeb) > 0

ORDER BY T2.CARDCODE, T0.taxdate

Regards,

Natrayan

[KennedyT21]

Hi Raaj Kumar..

If you do reconcile with the old bills it will be solved i think pls check

Hope helpful

Regards

Kennedy

[Former Member]

Hi.....

Extremely Sorry For Late Reply.....

The Solution You Suggested Is Absolutely True But The Problem Arises When We Receive Payment On Account, And At That Time We May Not Be Knowing Against Which Bill The Customer Has Made Payment.....

And At That Time If We Need A Perfect Aging Report, We Don't Get......So, That Is The Main Problem.....

As SAP Deducts Automatically The Amount Received From So And So Months Sales And Not From The Older Dues......

Anyways, Thanks For The Reply......Please Do Suggest Us If You Get Better Solution Than The One You Suggested.......

Regards,

Raaj kumar Lodha.

[KennedyT21]

Hi Rajj Kumar...

I do Agree with you ....

If you not able to Match the invoice at the Time of payment better make incoming without selection of invoice and when you have the bill copy from the customer make reconciliation with the invoice against the payment.

Regards

Kennedy