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