# Prophet 21 Inventory by Location SQL Query Generator

> Source: https://netray.co/tools/prophet-21-inventory-by-location-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Builds a read-only SELECT for on-hand and allocated quantity by item and location in a Prophet 21 database.

## Category

epicor

## Erp

Epicor Prophet 21

## Industry

Wholesale Distribution

## Audience

- P21 administrator
- Report writer
- Inventory analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Location ID

**Key:** location_id

**Type:** text

**Default:** 10

**Help:** Numeric location code from your system

#### Item ID starts with

**Key:** item_filter

**Type:** text

**Default:** A

**Help:** Leave a single letter or a prefix

#### Minimum on hand to show

**Key:** min_on_hand

**Type:** number

**Default:** 1

#### Rows to return

**Key:** top_rows

**Type:** number

**Default:** 500

#### Sort by

**Key:** sort_by

**Type:** select

**Options:**

- m.item_id
- l.qty_on_hand DESC

**Default:** m.item_id

**Blocks:**

#### Inventory by location (read-only)

**Body:** -- Read-only: run against a reporting copy or with a read-only login
SELECT TOP {{top_rows}}
    m.item_id,
    m.item_desc,
    l.location_id,
    l.qty_on_hand,
    l.qty_allocated,
    (l.qty_on_hand - l.qty_allocated) AS qty_available
FROM inv_mast m
INNER JOIN inv_loc l
    ON l.inv_mast_uid = m.inv_mast_uid
WHERE l.location_id = {{location_id}}
  AND m.item_id LIKE '{{item_filter}}%'
  AND l.qty_on_hand >= {{min_on_hand}}
ORDER BY {{sort_by}};

#### Items with allocation above on hand

**Body:** SELECT m.item_id, l.location_id, l.qty_on_hand, l.qty_allocated
FROM inv_mast m
INNER JOIN inv_loc l
    ON l.inv_mast_uid = m.inv_mast_uid
WHERE l.location_id = {{location_id}}
  AND l.qty_allocated > l.qty_on_hand
ORDER BY m.item_id;

**Notes:** Table and column names inv_mast, inv_loc, inv_mast_uid, location_id, qty_on_hand and qty_allocated are commonly seen in Prophet 21 databases, but versions and customizations differ. Verify them in your own schema, add any delete or status flag filters your site uses, and run against a reporting copy first. Never write to ERP tables with SQL.

## Content

**Intro:** Prophet 21 administrators and report writers often need a quick list of what is on hand by location outside the standard screens. This generator fills a read-only SQL pattern from a few inputs so you can paste it into your SQL client.

**Sections:**

#### How to use it

**Body:** Fill in the location, an item prefix, the minimum on-hand and the row count. Copy the query into SQL Server Management Studio or your reporting tool and run it with a read-only account. The second block finds items where allocated quantity exceeds on hand, which usually points to stock moved without updating an order or an allocation that needs review.

**Bullets:**

- Use a read-only login or a reporting copy
- Start with a small row count
- Verify column names in your schema first
- Add site-specific status filters if needed

#### How the query is built

**Body:** The item master holds one row per item, and the location table holds one row per item per location. Joining them on the item key returns balances with descriptions. Available quantity here is on hand minus allocated, a simple version of what the system shows. Your ERP may also consider reserved, in-transit and hold quantities, so treat this as a quick check, not a replacement for the standard availability screen.

#### Reading the result

**Body:** A large gap between on hand and allocated is normal for stock awaiting pick. A negative available number is a flag: either a count error, a receipt not yet posted, or an allocation made against stock that has since moved. Compare a sample of flagged items to the physical bin and to open orders before you adjust anything.

#### Common mistakes

**Body:** People run broad queries during business hours on the live database and slow the system, compare numbers against a screen that uses a different availability rule, or forget that location codes are numeric in some installs and quote them wrongly. Test with one location and a few items first.

**Cta:** If you want plain-language questions answered from your Prophet 21 data with the SQL shown and read-only access, we can discuss how that would work in your environment.

**Faq:**

#### Is it safe to run this on my production database?

**Answer:** A SELECT does not change data, but heavy queries can slow users. Use a read-only login, limit rows, and prefer a reporting copy or off-hours run. Never run INSERT, UPDATE or DELETE statements against ERP tables.

#### Why do my numbers differ from the P21 screen?

**Answer:** The screen may subtract holds, reservations or other quantity types and may be filtered by security. This query shows two raw columns. Differences usually point to those extra quantity rules or to customizations in your install.

#### Can I add vendor or product group?

**Answer:** Yes, join the extra tables that hold those attributes in your schema and add the columns. Check the table and column names in your own database, because they differ by version and configuration.

#### Does this work for Eclipse?

**Answer:** No. Epicor Eclipse uses a different data structure and tooling. This pattern is meant for Prophet 21 on SQL Server. Use Eclipse reporting tools or a vetted extract for that system.

## Search terms

prophet 21 sql query inventory, p21 inv_loc query, p21 inv_mast sql, prophet 21 on hand by location, epicor p21 report writer sql, p21 inventory report sql, prophet 21 database tables, p21 low stock query

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