Skip to content

SQL Scripts – Inventory Valuation

April 14, 2010

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

About these ads

From → Dynamics GP

Leave a Comment

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

Follow

Get every new post delivered to your Inbox.

Join 27 other followers

%d bloggers like this: