# Dynamics GP Inventory On-Hand by Site SQL Query Generator

> Source: https://netray.co/tools/gp-sql-inventory-on-hand-by-site-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generate a read-only SQL query that lists item quantities and value by site from IV00101 and IV00102.

## Category

microsoft-dynamics

## Erp

Microsoft Dynamics GP

## Audience

- GP report writer
- Inventory controller
- Migration consultant

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Company database

**Key:** company_db

**Type:** text

**Default:** TWO

#### Site (LOCNCODE)

**Key:** site

**Type:** text

**Default:** WAREHOUSE

**Help:** The GP site ID to report.

#### Minimum on-hand quantity

**Key:** min_qty

**Type:** number

**Default:** 0

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

#### Item number prefix

**Key:** item_prefix

**Type:** text

**Help:** Optional. Leave blank for all items.

**Blocks:**

#### On-hand by site

**Body:** USE [{{company_db}}];
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 = '{{site}}'
  AND q.QTYONHND > {{min_qty}}
  AND i.ITEMNMBR LIKE '{{item_prefix}}%'
  AND i.INACTIVE = 0
ORDER BY extended_value DESC;

**Notes:** 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.

## Content

**Intro:** 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.

**Sections:**

#### How to use it

**Body:** 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.

**Bullets:**

- 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

**Body:** 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

**Body:** 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

**Body:** 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.

**Cta:** 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.

**Faq:**

#### Why does RCRDTYPE matter?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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?

**Answer:** 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.

## Search terms

dynamics gp inventory on hand sql, IV00102 query, gp item quantity by site, gp inventory valuation sql, dynamics gp item master query, gp inventory migration extract

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json