Microsoft DynamicsFree generatorPDF, Excel, Word exportMicrosoft Dynamics GP

Dynamics GP Inventory On-Hand by Site SQL Query Generator

Inventory extracts are among the first things a migration team asks for. This generator builds a read-only on-hand query from the GP item master and item quantity master, filtered by site, with allocated, available, on-order and extended value columns.

Fill in

The GP site ID to report.

Rows with on-hand at or below this are excluded.

Optional. Leave blank for all items.

Generated code
Download:

On-hand by site

USE [TWO];
GO

SELECT
    RTRIM(i.ITEMNMBR) AS item_number,
    RTRIM(i.ITEMDESC) AS description,
    RTRIM(i.ITMCLSCD) AS item_class,
    RTRIM(q.LOCNCODE) AS site,
    q.QTYONHND AS qty_on_hand,
    q.ATYALLOC AS qty_allocated,
    q.QTYONHND - q.ATYALLOC AS qty_available,
    q.QTYONORD AS qty_on_order,
    i.CURRCOST AS current_cost,
    q.QTYONHND * i.CURRCOST AS extended_value
FROM dbo.IV00101 i
JOIN dbo.IV00102 q
    ON q.ITEMNMBR = i.ITEMNMBR
WHERE q.RCRDTYPE = 2
  AND q.LOCNCODE = 'WAREHOUSE'
  AND q.QTYONHND > 0
  AND i.ITEMNMBR LIKE '[Item number prefix]%'
  AND i.INACTIVE = 0
ORDER BY extended_value DESC;

IV00102 holds an overall record (RCRDTYPE 1) and one record per site (RCRDTYPE 2); this query uses site records. CURRCOST is the current cost on the item master, which may not equal FIFO or average layers in IV10200; use it for estimates, not for audited valuation. Verify column names on your GP version.

Planning an inventory cutover?

An optional review of your count, valuation and opening balance approach.

Optional. The tool and every export work without this. No spam.

How to use it

Fill in the company database and the site ID, set a minimum quantity if you want to skip zero rows, and optionally restrict by item prefix. Copy the SQL into SSMS. Run once per site, or remove the site filter to produce a multi-site list sorted by value.

  • Use for cycle count lists, obsolete stock review and migration baselines
  • Export the result to Excel and add a counted-quantity column
  • Keep a dated copy as your cutover reference

What the tables mean

IV00101 is the item master with description, class, cost and the inactive flag. IV00102 is the item quantity master, one row per item per site plus an overall row. Quantity on hand, allocated and on order live there. Available is derived here as on hand less allocated, which matches how most GP reports present availability, though some sites also net out back orders.

Reading the value column

Extended value multiplies on-hand quantity by the item master current cost. For standard cost items that is a good estimate. For FIFO or average cost items, the true valuation comes from receipt layers and posted history, so expect differences from the GL inventory account. Reconcile to the GL, and explain the gap, before using the figure for financial decisions.

Common mistakes

Forgetting the RCRDTYPE filter doubles quantities because the overall and site rows both appear. Ignoring serial and lot tracked items can hide detail, since lots are in separate tables. Finally, remember inactive items can still hold stock, so remove the INACTIVE filter if your audit needs it.

Frequently Asked Questions

Why does RCRDTYPE matter?

IV00102 stores an overall record per item (type 1) and a record per site (type 2). Without a filter, each item appears at least twice and quantities double count. Use type 2 for site level reporting.

Does this include lot or serial detail?

No. Lot and serial quantities are in separate tables. This query gives item level quantity by site. For lot tracked items, extend with the lot master tables in your GP version.

Will this match the GL inventory balance?

Only for simple standard cost setups. Otherwise, valuation comes from receipt layers and posted cost adjustments. Reconcile quantities first, then value, and explain any difference before cutover.

How do I report all sites at once?

Remove the LOCNCODE condition. The query then returns one row per item per site, which is useful for a company-wide stock baseline before a migration.

If you are consolidating inventory from GP into a new ERP, we can help build count reconciliation and variance reports that run on your own infrastructure.

Talk to an ExpertExplore More Free Tools

Related Tools

Microsoft Dynamics

Dynamics GP Work, Open and History Table Map

A searchable list of Dynamics GP tables grouped by lifecycle: master, work, open and history, so you know where a transaction lives at each stage.

Microsoft Dynamics

Dynamics GP SQL AR Aging Query Generator

Generate a read-only SQL aging report from GP open receivables, with your own as-of date and bucket sizes.

Microsoft Dynamics

Dynamics GP SQL Trial Balance Query Generator

Build a read-only trial balance by account for a chosen fiscal year and period from the GP summary tables.

More Microsoft Dynamics tools

See all
Back to All Tools