EpicorFree generatorPDF, Excel, Word exportEpicor Prophet 21Wholesale Distribution

Prophet 21 Inventory by Location SQL Query Generator

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.

Fill in

Location ID

Numeric location code from your system

Item ID starts with

Leave a single letter or a prefix

Minimum on hand to show

Rows to return

Sort by

Generated code

Download:

Inventory by location (read-only)

-- Read-only: run against a reporting copy or with a read-only login
SELECT TOP 500
    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 = 10
  AND m.item_id LIKE 'A%'
  AND l.qty_on_hand >= 1
ORDER BY m.item_id;

Items with allocation above on hand

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 = 10
  AND l.qty_allocated > l.qty_on_hand
ORDER BY m.item_id;

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.

Ask your P21 data in plain English

Optional: see how read-only natural-language queries over Prophet 21 could work, with the SQL always visible.

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

How to use it

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.

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

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

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

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.

Frequently Asked Questions

Is it safe to run this on my production database?

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools

Related Tools

Epicor

More Epicor tools

See all

Back to All Tools