Skip to Content
0
Former Member
Dec 06, 2009 at 10:53 PM

Open aging report

28 Views

This report has been extended from an existing query that has been posted on this forum.

This report is an aging report for ALL trasnactions, both open and closed.

Is it possible to modify this query to report on OPEN / unreconciled trasnactions only?

select T0.shortname,T2.cardcode 'Customer Code',T2.cardname 'Name',T2.U_BU, T2.U_DEPT, 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 "61 to 90 days",

CASE

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

then

case

when syscred= 0 then sysdeb

when sysdeb= 0 then syscred * - 1

end

end "90 + 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

T2.U_Bu = %0and T2.U_Dept = %1

ORDER BY T2.CARDCODE, T0.taxdate

Thanks in advanced Lisa