Skip to Content
0
Former Member
Dec 03, 2009 at 03:30 AM

aging query

45 Views

I would appreciate some assistance in integrating the following into the below report

T0 WHERE T0.U_BU ={%0] and T0.U_Dept =[%1] (T0 = OCRD)

Integrated into

select OCRD.cardcode 'Supplier Code',OCRD.cardname 'Name',sysdeb 'Debit Amount',syscred 'Credit Amount',

case l.transtype

when '13' then 'INV'

when '14' then 'AR CN'

when '24' then 'INCOMING'

else 'Other'

end 'Type',

j.BaseRef'Trans #',

case l.transtype

when '13' then

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

else '-'

end 'Inv.Rem.',

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

l.Ref1,

fccurrency 'BP Currency',

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

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

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

CASE

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

then

case

when syscred <> 0 then syscred * - 1

else sysdeb

end

end "0-30 days",

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

and (datediff(dd,l.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,l.refdate,current_timestamp))+1 > 60

and (datediff(dd,l.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,l.refdate,current_timestamp))+1 > 90

then

case

when syscred= 0 then sysdeb

when sysdeb= 0 then syscred * - 1

end

end "90 + days"

from JDT1 l

Inner Join OJDT j On j.TransId=l.TransId

,OCRD where l.shortname = OCRD.cardcode and cardtype = 'c' and intrnmatch = '0'

ORDER BY OCRD.CARDCODE, l.taxdate

thanks in advanced