Skip to Content
author's profile photo
Former Member

Customer Ageing

Hi Experts,

I have the following query that I would like to add some further fields to, those fields being:

NumAtCard

CnctCode

TelNum1

SlpCode

GroupNum (Words not Code Number)

No of Days Diff from Doc Due Date (based on Date printed)

I have tried various ways to insert them into this query but get errors everytime. I know there is a standard report in SAP (using version 8.81) but the customer has a different format and layout they must keep to. Advice and guideance greatly appreciated with this.

select 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 where

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

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

ORDER BY T2.CARDCODE, T0.taxdate

Many thanks

Sean Martin

Edited by: Sean Martin on May 3, 2011 12:24 PM

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

1 Answer

  • author's profile photo
    Former Member
    Posted on May 03, 2011 at 11:05 AM

    hi Sean Martin

    Customer aging report include All Transactions (AR invoice , AR credit memo , Incoming payment, outgoing payment , JE )

    SAP haven't query standard.

    ex: aging date : 30/04/2011

    - AR Invoice before 30/04/2011 that are still open

    - AR Invoice reconciled after >30/04/2011 for AP Invoice created <= 30/04/2011

    -AP Credit Memo before 30/04/2011 that are still open (It should be credit memo without base doc)

    -Reconciliation for Credit memo without base doc created before 30/04/2011 but reconciled after 30/04/2011

    -outgoing payment created before 30/04/20110 but have Open Balance <> 0

    - Outgoing payment created before <= 30/04/2011 but reconciled >30/04/2011

    - Incoming payment of vendor created before 30/04/2011 and still open

    - incoming payment created before <= 30/04/2011 but reconciled >30/04/2011

    - journal entry befor 30/04/2011 that are still open

    - journal entry befor 30/04/2011 But reconciled >30/04/2011

    thanks

    H2

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Hoe,

      Thanks for the information, I understand the details, I have the query above that will give me the information I need, I just need to add the fields I mentioned into the query above to give me all the information I need to complete my crystal report.

      Hope that makes sense.

      Sean

      Edited by: Sean Martin on May 3, 2011 2:00 PM