Skip to Content
author's profile photo Former Member
Former Member

How To Modify SAP Aging Report ?

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Posted on Dec 09, 2012 at 11:18 AM

    Hi Raaj Kumar..

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

    Hope helpful

    Regards

    Kennedy

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Jan 18, 2013 at 10:29 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.