Month End Inventory-to-G/L Reconciliation - F4111 Vs F0911 Compare

drop table #temp11
drop table #temp12
drop table #temp13

--select * from proddta.f4111 where ILDGL>117000
--select * from proddta.f0911 where GLDGJ>117000


select  ildoc,ildct,ILJELN,ILLITM, ilmcu, sum(iltrqt)iltrqt,sum(ilpaid)/100ilpaid into #temp11
from proddta.f4111 where ILDGL>117000
group by   ildoc,ildct,ILLITM,ilmcu,ILLITM,ILJELN


select  gldoc,gldct,GLJELN,glmcu,gLexr, glsub,glani,sum(glu)/100 glu, sum(glaa)/100 glaa into #temp12
from proddta.f0911 where gLDGj>117000 and gldct in(
select  distinct ildct from proddta.f4111 where ILDGL>117000)
group by   GLDOC,gldct,glexr,glsub,GLJELN,glani,glmcu


select *,( glaa-ilpaid) amt into #temp13
 from #temp11
left outer join #temp12 on ildoc=gldoc and ildct=gldct and GLJELN=iljeln
--where len(glsub) >3 and glaa-ilpaid<>0
select
ildoc,ildct,illitm,ilmcu,iltrqt,ilpaid,glani,glu,glaa, (ilpaid-glaa) AmtDiff,
case when (ilpaid-glaa)=0 then 'Recoincile' when glaa is null then  'Not in GL' else 'Not Recoinciled' end  RecoincileYN
from #temp13
--where  glaa is null



--select illitm,ilmcu,sum(ilpaid)ilpaid,glsub, sum(glaa) glaa  from #temp13
--where ilpaid-glaa<>0 group by illitm,ilmcu,glsub

                 

----select * from proddta.f4111 where  illitm='1645618' and ltrim(ilmcu)='GRAYTIF-RT' and ildgl>117000 and
----select * from proddta.f0911 where gldgj>117000 and GLEXR='1649608' and ltrim(glsub) in('NIAGRA','010')
----select * from proddta.f0911 where gldoc=127572
--select * from proddta.f4111 where  ildoc=127375 and illitm='1654571' and ilmcu=' SCHAILI-SC2'
--select * from proddta.f0911  where  GLDOC=127375 and gldct='IK' --illitm='1654571' and ilmcu=' SCHAILI-SC2'

-- IK