Historical Inventory Demand Analysis by State and Date Range for Multi-Site Companies
Historical Inventory Demand Analysis by State and Date Range for Multi-Site Companies
A few things to note about this query:
– It is analyzing fill rates for a second site location to see what inventory needs to be brought in to maintain the same fill rates as the corporate warehouse
– It is looks at two values: current quantity on hand and max quantity to have in the warehouse
Variables in the query:
– x.locncode YYY needs to be replaced with the site id of your second location
– x.locncode XXX needs to be replaced with the site id of your first location
– a.GLPOSTDT XXXX-XX-XX needs to be replaced with the date range you want to analyze
– b.locncode XXX needs to be replaced with the site id of your first location
– a.state XXX needs to be replaced with the states you wish to analyze
select a.sopnumbe, a.custnmbr, a.custname, b.itemnmbr, b.itemdesc, b.QTYFULFI, b.UNITPRCE, b.XTNDPRCE, a.city, a.state,
(select x.qtyonhnd from iv00102 x where x.LOCNCODE = ‘YYY’ and x.ITEMNMBR = b.itemnmbr) [SITE 2 QTY ON HAND],
case
When ((select x.qtyonhnd from iv00102 x where x.LOCNCODE = ‘YYY’ and x.ITEMNMBR = b.itemnmbr) – b.QTYFULFI) < ‘0’ THEN ‘NO’
else ‘YES’
end [SITE 2 Able to Fill],
(select x.ORDRUPTOLVL from iv00102 x where x.LOCNCODE = ‘YYY’ and x.ITEMNMBR = b.itemnmbr) [SITE 2 Max Stock QTY],
case
When ((select x.ORDRUPTOLVL from iv00102 x where x.LOCNCODE = ‘YYY’ and x.ITEMNMBR = b.itemnmbr) – b.QTYFULFI) < ‘0’ THEN ‘NO’
else ‘YES’
end [SITE 2 Able to Fill Max],
(select x.qtyonhnd from iv00102 x where x.LOCNCODE = ‘XXX’ and x.ITEMNMBR = b.itemnmbr) [SITE 1 QTY ON HAND],
case
When ((select x.qtyonhnd from iv00102 x where x.LOCNCODE = ‘XXX’ and x.ITEMNMBR = b.itemnmbr) – b.QTYFULFI) < ‘0’ THEN ‘NO’
else ‘YES’
end [SITE 1 Able to Fill],
(select x.ORDRUPTOLVL from iv00102 x where x.LOCNCODE = ‘XXX’ and x.ITEMNMBR = b.itemnmbr) [ SITE 1 Max Stock QTY],
case
When ((select x.ORDRUPTOLVL from iv00102 x where x.LOCNCODE = ‘XXX’ and x.ITEMNMBR = b.itemnmbr) – b.QTYFULFI) < ‘0’ THEN ‘NO’
else ‘YES’
end [SITE 1 Able to Fill Max]
from sop30200 a
left outer join sop30300 b
on a.sopnumbe = b.SOPNUMBE
where a.GLPOSTDT between ‘XXXX-XX-XX’ and ‘XXXX-XX-XX’
and b.soptype = ‘3’
and a.voidstts = ‘0’
and b.LOCNCODE = ‘XXX’
and a.state IN (‘XX’,’XX’)