Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
KennedyT21
Active Contributor

Inventory Aging Report


Declare @a char(60)

Set @a  = (select distinct t20.docdate from oinm t20 where t20.docdate = '[%2]')

Select  distinct c.code, c.name

,SUM(c.bal) [Bal],sum(c.Val) [Val],

(case when avg(c.[FullStock]) > 0 then avg(c.[Pric]) / avg(c.[FullStock]) end ) [AvgPrice],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then SUM(c.bal)  else SUM(c.[0-30 Days]) end) [0-30 Days],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then (SUM(c.bal) - sum(c.[0-30 Days]))

else sum(c.[30-60 Days]) end)end)   [30-60 Days],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then

(SUM(c.bal) - (sum(c.[0-30 Days]) + sum(c.[30-60 Days]))) else

sum(c.[60-90 Days])end)end) end)   [60-90 Days],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])) >= SUM(c.bal) then

(SUM(c.bal) - (sum(c.[0-30 Days]) + sum(c.[30-60 Days]) + sum(c.[60-90 Days]))) else sum(c.[90-180 Days])

end)end) end) end)  [90-180 Days],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])) >= SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days]) +

sum(c.[90-180 Days]) +  sum(c.[180-360 Days]) ) >= SUM(c.bal) then

(SUM(c.bal) - (sum(c.[0-30 Days]) + sum(c.[30-60 Days]) + sum(c.[60-90 Days]) + sum(c.[90-180 Days])))

else sum(c.[180-360 Days])end)end)end)end)end) [180-360 Days],

(case when SUM(c.[0-30 Days]) >= SUM(c.bal)  then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days])) >=  SUM(c.bal) then 0 else

(case when (sum(c.[30-60 Days]) + SUM(c.[0-30 Days]) + sum(c.[60-90 Days])) >= SUM(c.bal) then 0 else sum(c.[Above 360 Days])

end)end)end) [Above 360 Days]

from (

select b.code, b.name,

b.Bal, b.Val,b.[Pric],b.[FullStock],

isnull(case when b.days <=30 then b.Bal end,0)[0-30 Days] ,

isnull(case when b.days <= 30 then b.val end,0)[0-30 Days-Value] ,

isnull(case when b.days between 31 and 60 then b.Bal end,0) [30-60 Days],

isnull(case when b.days between 61 and 90 then b.Bal end,0) [60-90 Days],

isnull(case when b.days between 91 and 180 then b.Bal end,0) [90-180 Days],

isnull(case when b.days between 181 and 360 then b.Bal end,0) [180-360 Days],

isnull(case when b.days >= 361 then b.Bal end,0) [Above 360 Days]

from

(

select a.code,a.name,

a.bal,a.val,datediff(dd,dt,@a)'days',a.[Receipt],a.[Pric],a.[FullStock]

from

(

select max(t0.itemcode)'Code',max(t0.Dscription)'Name',

(select sum(isnull(t11.transvalue,0))  from oinm t11 where (t11.itemcode = (t0.itemcode))

and (t11.docdate <= '[%2]' )) [Pric],

(select (sum(isnull(t11.inqty,0)) - sum(isnull(t11.outqty,0)) ) from oinm t11 where (t11.itemcode = (t0.itemcode))

and (t11.docdate <= '[%2]' )) [FullStock],

--(t0.Warehouse)'Wh',

(sum(isnull(t0.inqty,0))) [Receipt],

(sum(isnull(t0.inqty,0)) - sum(isnull(t0.outqty,0)) )'Bal',sum(isnull(t0.transvalue,0))'Val',(t0.docdate)'dt'

from oinm t0 inner join oitm t1 on t0.itemcode=t1.itemcode inner join oitb ob on ob.itmsgrpcod = t1.itmsgrpcod

where  (ob.itmsgrpnam = '[%1]')

and (t0.docdate <= '[%2]')

group by t0.itemcode,t0.docdate

)a

)b

)c where c.bal>0

group by c.code, c.name

35 Comments
Labels in this area