Posted by: istewart | July 2, 2009

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

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

Follow

Get every new post delivered to your Inbox.

Join 28 other followers

%d bloggers like this: