Skip to Content
0
Former Member
Dec 08, 2012 at 09:12 AM

How To Modify SAP Aging Report ?

258 Views

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