Roll up account balance JD Edwards

drop

table #temp

select

gbobj,gbsub,gmdl01,gmlda,sum(gbapyc/100)gbapyc,sum(gban01/100)gban01,sum(gban02/100)gban02,sum(gban03/100)gban03,

sum

(gban04/100)gban04,sum(gban05/100)gban05,sum(gban06/100)gban06,sum(gban07/100)gban07,

sum

(gban08/100)gban08,sum(gban09/100)gban09,sum(gban10/100)gban10,sum(gban11/100)gban11,sum(gban12/100)gban12

into

#temp

from

jde_crp.crpdta.f0902

left

outer join jde_crp.crpdta.f0901 on gbaid=gmaid

where

gmco=1 and gmobj>=40000

and

(gban01+gban02+gban03+gban04+gban05+gban06+gban07+gban08+gban09+gban10+gban11+gban12)<>0

group

by gbobj,gbsub,gmlda,gmdl01

order

by gbobj,gbsub,gmlda

drop

table [#temp2]

select

distinct gmobj,gmsub,gmdl01,gmlda into #temp2 from jde_crp.crpdta.f0901 where gmco=1 and gmobj>=40000

drop

table [#temp3]

select

a.gmobj pgmobj,a.gmsub pgmsub,a.gmdl01 pgmdl01,a.gmlda pgmlda, b.*

into

#temp3 from #temp2 a

left

outer join #temp b on gbobj=gmobj and gbsub=gmsub

order

by a.gmobj

delete

from #temp3 where gbobj is null and pgmlda in(7,8)

drop

table #temp4

select

ltrim

(substring(a.gmobj,1,1)+'0000')t01,d.gmdl01 td01,

ltrim

(substring(a.gmobj,1,2)+'000')t02,c.gmdl01 td02,

ltrim

(substring(a.gmobj,1,3)+'00') t03,b.gmdl01 td03,

a

.* into #temp4

from

#temp2 b

left

outer join #temp2 a on ltrim(substring(a.gmobj,1,3)+'00')=b.gmobj

left

outer join #temp2 c on ltrim(substring(a.gmobj,1,2)+'000')=c.gmobj

left

outer join #temp2 d on ltrim(substring(a.gmobj,1,1)+'0000')=d.gmobj

where

a.gmlda in(4,5,6,7)order by a.gmobj

--select * from #temp3

--select * from #temp4

select

t01

,td01,t02,td02,t03,td03,gmobj,gmsub,a.gmdl01,a.gmlda,gbobj,gbsub,gbapyc,gban01,

gban02

,gban03,gban04,gban05,gban06,gban07,gban08,gban09,gban10,gban11,gban12

from

#temp4 b

left

outer join #temp3 a on a.pgmobj=b.gmobj and a.pgmsub=b.gmsub

where

a.pgmobj is not null