Inventory Excess Report with SKU Ranking by Procurement Software
Inventory Excess Report with SKU Ranking by Procurement Software
A few things about this query:
– Q1, Q2, Q3 and Q4 sub-queries have XXXX for the year.
– Location Code has XXX and will need to be replaced with the Site ID of company you are running the report on.
– z.LOCNCODE and x.TRXLOCTN must equal the same Site ID for accurate inventory reporting.
– The historical demand column provides a running number for historical demand directly related to the date the query is run
select a.itemnmbr [Item Number], b.itemdesc [Item Description] , b.currcost [Current Cost], a.QTYONHND [QTY on Hand], (b.currcost * a.qtyonhnd) [Inventory Value], a.locncode [Location],
ISNULL(CASE c.tcsFLDL_itemrank
WHEN 1 then ‘A’
WHEN 2 then ‘B’
WHEN 3 then ‘C’
WHEN 4 then ‘D’
WHEN 5 then ‘X’
END, ‘?’) [Rank], isnull(d.hm_string_1, ‘NO MFG’) [Primary Vendor],
isnull(((select sum(x.qtyfulfi) from sop30300 x
left outer join sop30200 z
on x.sopnumbe = z.sopnumbe
where x.ITEMNMBR = a.ITEMNMBR
and z.GLPOSTDT between getdate()-365 and getdate()-1
and z.voidstts = ‘0’ and z.soptype = ‘3’ and z.LOCNCODE = ‘XXX’)/12),0) [Historical Demand],
isnull((select sum(x.qtyfulfi) from sop30300 x
left outer join sop30200 z
on x.sopnumbe = z.sopnumbe
where x.ITEMNMBR = a.ITEMNMBR
and z.GLPOSTDT between ‘XXXX-01-01’ and ‘XXXX-03-31’
and z.voidstts = ‘0’ and z.soptype = ‘3’ and z.LOCNCODE = ‘main’),0) [Q1 Demand],
isnull((select sum(x.qtyfulfi) from sop30300 x
left outer join sop30200 z
on x.sopnumbe = z.sopnumbe
where x.ITEMNMBR = a.ITEMNMBR
and z.GLPOSTDT between ‘XXXX-04-01’ and ‘XXXX-06-30’
and z.voidstts = ‘0’ and z.soptype = ‘3’ and z.LOCNCODE = ‘main’),0) [Q2 Demand],
isnull((select sum(x.qtyfulfi) from sop30300 x
left outer join sop30200 z
on x.sopnumbe = z.sopnumbe
where x.ITEMNMBR = a.ITEMNMBR
and z.GLPOSTDT between ‘XXXX-07-01’ and ‘XXXX-09-30
and z.voidstts = ‘0’ and z.soptype = ‘3’ and z.LOCNCODE = ‘XXX’),0) [Q3 Demand],
isnull((select sum(x.qtyfulfi) from sop30300 x
left outer join sop30200 z
on x.sopnumbe = z.sopnumbe
where x.ITEMNMBR = a.ITEMNMBR
and z.GLPOSTDT between ‘XXXX-10-01’ and ‘XXXX-12-31’
and z.voidstts = ‘0’ and z.soptype = ‘3’ and z.LOCNCODE = ‘XXX’),0) [Q4 Demand],
(select top 1 x.daterecd from iv10200 x where x.ITEMNMBR = a.ITEMNMBR and x.TRXLOCTN = ‘XXX’ order by x.daterecd desc) [Last Recv’d Date],
(select top 1 x.QTYRECVD from iv10200 x where ITEMNMBR = a.ITEMNMBR and x.TRXLOCTN = ‘XXX’ order by x.daterecd desc) [Last Recv’d QTY]
from iv00102 a
left outer join iv00101 b
on a.ITEMNMBR = b.ITEMNMBR
left outer join tcsINVTB00091_IR_ISD c
on a.ITEMNMBR = c.ITEMNMBR
left outer join hm00500 d
on a.ITEMNMBR = d.ITEMNMBR
where a.QTYONHND > ‘0’