SQL Scripts – Inventory Valuation
The following script will take a look at your inventory receipts table (Inquiry->Inventory->Receipts) and give you a summary, per item, of your inventory dollar valuation. You can use this script in an SSRS or Crystal report to sum total your entire valuation of inventory by GL account.
We use this at the end of each month to make sure our inventory account dollars in GL match what GP inventory receipts are.
The script uses the following views and GP tables:
Views
- Inventory Receipts (tspvInventoryReceipts)
- Item Master (tspvItemMaster)
- Inventory Summaries – By Location (tspvInventoryByLoc)
Tables
- IV00101
- GL00100
- GL00105
**SQL Script***
SELECT a.item_number,
b.item_description,
b.item_class_code,
a.location,
SUM(( qty_received – qty_sold ) * unit_cost) AS valuation,
a.unit_cost,
b.standard_cost,
c.onhand,
c.allocated,
c.available,
LTRIM(RTRIM(e.ACTNUMBR_1)) + ‘-‘ + LTRIM(RTRIM(e.ACTNUMBR_2)) + ‘-‘
+ LTRIM(RTRIM(e.ACTNUMBR_3)) AS account_num,
f.ACTDESCR
FROM tspvinventoryreceipts a
LEFT JOIN spvitemmaster b ON a.item_number = b.item_number
LEFT JOIN spvinventorybyloc c ON a.item_number = c.item_number
LEFT JOIN iv00101 d ON a.item_number = d.itemnmbr
LEFT JOIN gl00105 e ON d.ivivindx = e.actindx
LEFT JOIN gl00100 f ON e.actindx = f.actindx
WHERE a.location = c.location
AND ( ( qty_received – qty_sold ) <> 0 )
GROUP BY a.location,
a.item_number,
b.item_description,
b.item_class_code,
a.unit_cost,
b.standard_cost,
c.onhand,
c.allocated,
c.available,
LTRIM(RTRIM(e.ACTNUMBR_1)) + ‘-‘ + LTRIM(RTRIM(e.ACTNUMBR_2)) + ‘-‘
+ LTRIM(RTRIM(e.ACTNUMBR_3)),
f.actdescr
ORDER BY a.location,
a.item_number
