Inventory Listing Grouped by SKU for Multi-Site Companies

Inventory Listing (QTY available by site & QTY on order by site) Grouped by SKU for Multi-Site Companies

A few things to note about this query:
– the selling price sub-query assumes that your company is using multiple price level and a pricing method of ‘percentage of list’
– replace the XXX and YYY in the location code piece with the SIte ID’s you are reporting on
– replace the XXX in the prclevel piece with the price level you are focusing on
– replace the XXX in the itemnmbr with the item number you want results on
– you can comment out these two lines of code below to list all inventory:

and a.itemnmbr  = ‘XXX’

and a.itemnmbr  LIKE ‘%’ + @itemnmbr + ‘%’

– or you can comment out the top line of code and use the bottom line to declare a variable and create a store procedure

select a.itemnmbr [Item Number], c.itemdesc [Item Description], a.LOCNCODE [Location Code], (a.qtyonhnd – a.atyalloc) [QTY Available],

((select max(x.uomprice) from iv00108 x where  x.itemnmbr = a.itemnmbr and x.prclevel = b.prclevel and x.fromqty =’1′)*(select y.listprce from iv00105 y where y.itemnmbr = a.itemnmbr)/100) [Selling Price],

a.QTYONORD [QTY on Order]

from iv00102 a

inner join iv00108 b

on a.ITEMNMBR = b.ITEMNMBR

left outer join iv00101 c

on a.ITEMNMBR = c.ITEMNMBR

where a.LOCNCODE in (‘XXX’, ‘YYY’)

and b.PRCLEVEL = ‘XXX’

and a.itemnmbr  = ‘XXX’

— and a.itemnmbr  LIKE ‘%’ + @itemnmbr + ‘%’ — you can declare a variable here to replace the code above to build a store procedure

group by a.ITEMNMBR, a.locncode, a.QTYONHND, a.ATYALLOC, b.PRCLEVEL, c.ITEMDESC, a.QTYONORD