SQL Script to get Inventory Value by Site

The following will return the value of inventory on hand in each site. Good for verifying modified reports are accurate.

SELECT a.ITEMNMBR, b.ITEMDESC, a.LOCNCODE, a.QTYONHND, a.ATYALLOC, b.CURRCOST, CASE WHEN b.VCTNMTHD IN (1, 2) THEN isnull(c.Value, 0) WHEN b.VCTNMTHD = 3 THEN (a.QTYONHND * b.CURRCOST) WHEN b.VCTNMTHD IN (4, 5) THEN (a.QTYONHND * b.STNDCOST) END AS Value FROM IV00102 AS a INNER JOIN IV00101 AS b ON a.ITEMNMBR = b.ITEMNMBR LEFT OUTER JOIN (SELECT ITEMNMBR, TRXLOCTN, SUM((QTYRECVD – QTYSOLD) * UNITCOST) AS Value FROM IV10200 WHERE (QTYTYPE = 1) GROUP BY ITEMNMBR, TRXLOCTN) AS c ON a.ITEMNMBR = c.ITEMNMBR AND a.LOCNCODE = c.TRXLOCTN WHERE (a.RCRDTYPE = 2) AND (b.ITEMTYPE IN (1, 2)) AND (c.Value <> 0) ORDER BY Value, a.ITEMNMBR, Value DESC