Manufacturing, Distribution, and Internet Applications

[sschrader]

I am looking for an Aged inventory report. Somethign along the lines of Onhand inventory sorted by last sale date to include fifo cost. Has anyone written something like this allready?

[jnoll · Accepted solution]

How is this for the data selection? Then you can use for a report or a BI.

--vimQOH
Create View vimQOH_RKL as (
select b.whsekey, w.itemkey, SUM(w.qtyonhand) as QtyOnHand
from timwhsebininvt w WITH (NOLOCK) inner join
timwhsebin b WITH (NOLOCK) on w.whsebinkey = b.whsebinkey
group by b.whsekey, w.itemkey)
GO
Grant Select on vimQOH_RKL to ApplicationDBRole

--vsoLastSale
Create View vsoLastSale_RKL as (
select l.itemkey, MAX(s.trandate) as LastSaleDate
from tsosalesorder s WITH (NOLOCK) inner join
tsosoline l WITH (NOLOCK) on s.sokey = l.sokey
group by l.itemkey)
GO
Grant Select on vsoLastSale_RKL to ApplicationDBRole

--vimFIFOCost_RKL
Create View vimFIFOCost_RKL as (
select totmaterialcost,costingdate,itemkey,whsekey from timcosttier WITH (NOLOCK)
where status = 2 and 
Cast(CostingDate as VARCHAR(30)) + '-' + CAST(itemkey as varchar(20)) + '-' + CAST(whsekey as varchar(20)) 
IN (select Cast(MIN(CostingDate) as VARCHAR(30)) + '-' + CAST(itemkey as varchar(20)) + '-' + CAST(whsekey as varchar(20))
from timcosttier WITH (NOLOCK)
where status = 2
group by whsekey, itemkey)
)
GO
Grant Select on vimFIFOCost_RKL to ApplicationDBRole

--vimFIFOCost_RKL
Create View vimInventoryCosting_RKL as (
Select w.whseid, w.description as WhseName, i.itemid, d.shortdesc, u.unitmeasid as StockUofM,
q.QtyOnHand, s.LastSaleDate, f.totmaterialcost as FIFOunitcost, v.avgunitcost, v.stdunitcost
from timitem i WITH (NOLOCK) inner join
timinventory v WITH (NOLOCK) on i.itemkey = v.itemkey inner join
timwarehouse w WITH (NOLOCK) on v.whsekey = w.whsekey inner join
tciunitmeasure u WITH (NOLOCK) on i.stockunitmeaskey = u.unitmeaskey inner join
timitemdescription d WITH (NOLOCK) on i.itemkey = d.itemkey left outer join
vimQOH_RKL q WITH (NOLOCK) on v.whsekey = q.whsekey and v.itemkey = q.itemkey left outer join
vsoLastSale_RKL s WITH (NOLOCK) on v.itemkey = s.itemkey left outer join
vimFIFOCost_RKL f WITH (NOLOCK) on v.whsekey = f.whsekey and v.itemkey = f.itemkey 
)
GO
Grant Select on vimInventoryCosting_RKL to ApplicationDBRole

select * from vimInventoryCosting_RKL

[sschrader]

Thanks Joe, that did the trick

[jnoll]

Slight revision to add CompanyID and Product Line.

--vimQOH
Create View vimQOH_RKL as (
select b.whsekey, w.itemkey, SUM(w.qtyonhand) as QtyOnHand
from timwhsebininvt w WITH (NOLOCK) inner join
timwhsebin b WITH (NOLOCK) on w.whsebinkey = b.whsebinkey
group by b.whsekey, w.itemkey)
GO
Grant Select on vimQOH_RKL to ApplicationDBRole

--vsoLastSale
Create View vsoLastSale_RKL as (
select l.itemkey, MAX(s.trandate) as LastSaleDate
from tsosalesorder s WITH (NOLOCK) inner join
tsosoline l WITH (NOLOCK) on s.sokey = l.sokey
group by l.itemkey)
GO
Grant Select on vsoLastSale_RKL to ApplicationDBRole

--vimFIFOCost_RKL
Create View vimFIFOCost_RKL as (
select totmaterialcost,costingdate,itemkey,whsekey from timcosttier WITH (NOLOCK)
where status = 2 and 
Cast(CostingDate as VARCHAR(30)) + '-' + CAST(itemkey as varchar(20)) + '-' + CAST(whsekey as varchar(20)) 
IN (select Cast(MIN(CostingDate) as VARCHAR(30)) + '-' + CAST(itemkey as varchar(20)) + '-' + CAST(whsekey as varchar(20))
from timcosttier WITH (NOLOCK)
where status = 2
group by whsekey, itemkey)
)
GO
Grant Select on vimFIFOCost_RKL to ApplicationDBRole

--vimFIFOCost_RKL
Create View vimInventoryCosting_RKL as (
Select w.whseid, w.description as WhseName, i.itemid, d.shortdesc, u.unitmeasid as StockUofM,
q.QtyOnHand, s.LastSaleDate, f.totmaterialcost as FIFOunitcost, v.avgunitcost, v.stdunitcost
from timitem i WITH (NOLOCK) inner join
timinventory v WITH (NOLOCK) on i.itemkey = v.itemkey inner join
timwarehouse w WITH (NOLOCK) on v.whsekey = w.whsekey inner join
tciunitmeasure u WITH (NOLOCK) on i.stockunitmeaskey = u.unitmeaskey inner join
timitemdescription d WITH (NOLOCK) on i.itemkey = d.itemkey left outer join
vimQOH_RKL q WITH (NOLOCK) on v.whsekey = q.whsekey and v.itemkey = q.itemkey left outer join
vsoLastSale_RKL s WITH (NOLOCK) on v.itemkey = s.itemkey left outer join
vimFIFOCost_RKL f WITH (NOLOCK) on v.whsekey = f.whsekey and v.itemkey = f.itemkey 
)
GO
Grant Select on vimInventoryCosting_RKL to ApplicationDBRole

select * from vimInventoryCosting_RKL

[WKK]

I have been looking for a report for inventory aging and have seen a script called Aged Inventory by jnoll.  I have no clue how to run the script.

[jnoll]

This is SQL Script that would be run from a SQL Server Query Window. Your IT Person or a Sage Partner should be able to use this to create.