# Prophet 21 Open Order and Backorder SQL Query Generator

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

## Short answer

Builds a read-only SQL pattern that lists open sales order lines, ordered versus shipped quantity and age.

## Category

epicor

## Erp

Epicor Prophet 21

## Industry

Wholesale Distribution

## Audience

- P21 administrator
- Customer service lead
- Report writer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Orders entered from

**Key:** from_date

**Type:** date

**Default:** 2026-01-01

#### Customer ID

**Key:** customer_id

**Type:** text

**Default:** 100245

#### Minimum age in days

**Key:** min_age

**Type:** number

**Default:** 7

#### Rows to return

**Key:** top_rows

**Type:** number

**Default:** 300

**Blocks:**

#### Open order lines pattern

**Body:** -- Pattern only. Confirm table and column names in your schema before use.
-- Read-only: use a reporting login.
SELECT TOP {{top_rows}}
    h.order_no,
    h.customer_id,
    h.order_date,
    DATEDIFF(day, h.order_date, GETDATE()) AS age_days,
    l.line_no,
    l.qty_ordered,
    l.qty_canceled,
    (l.qty_ordered - l.qty_canceled) AS qty_open_gross
FROM oe_hdr h
INNER JOIN oe_line l
    ON l.order_no = h.order_no
WHERE h.order_date >= '{{from_date}}'
  AND h.customer_id = {{customer_id}}
  AND DATEDIFF(day, h.order_date, GETDATE()) >= {{min_age}}
ORDER BY h.order_date, h.order_no, l.line_no;

#### What to add before you rely on it

**Body:** -- 1. Join your shipped or invoiced quantity source to subtract what has already shipped.
-- 2. Add the completed or closed flag columns that your system uses on header and line.
-- 3. Exclude quotes, returns and cancelled orders using your order type fields.
-- 4. Add expected ship date if you want to separate past due from future.

**Notes:** This is a starting pattern. The names oe_hdr and oe_line are familiar from Prophet 21, but join keys, status flags and shipped-quantity columns vary by version and customization, so verify each one in your schema. Open quantity is not calculated fully here on purpose, because the shipped quantity source differs between installs. Run read-only.

## Content

**Intro:** Customer service teams want one list of open order lines and how long they have been waiting. This generator gives a clean starting SQL pattern for Prophet 21 that you finish with your own shipped quantity and status fields.

**Sections:**

#### How to use it

**Body:** Enter the start date, a customer and a minimum age, then copy the pattern. The second block lists the pieces you must add: shipped quantity, closed flags, order type exclusions and expected ship date. Add them one at a time and compare to a known customer order on screen until the counts agree.

**Bullets:**

- Start with one customer and a short date range
- Compare to the standard order screen
- Add shipped quantity from your schema
- Exclude quotes, returns and cancelled orders

#### Why the pattern stops where it does

**Body:** Open quantity is ordered minus canceled minus shipped. The shipped part comes from different places depending on how invoicing and pick tickets are configured, and a wrong guess produces plausible but false numbers. We prefer an honest partial query to an incorrect complete one.

#### Reading the result

**Body:** Age in days uses the order date, not the promise date. A line that is old and still open is the first candidate to review with purchasing: is stock on a purchase order, is it a special order, or did the customer change the need? Sort by age and work from the oldest, then group by customer to find accounts with several stuck lines.

#### Common mistakes

**Body:** The usual errors are counting canceled lines as open, including quotes, joining header and line on the wrong key so lines duplicate, and running the query without a date limit on the live server. Check row counts against the screen before you share a report.

**Cta:** If backorder follow-up is manual in your branches, we can talk about a read-only agent that flags aging open lines from your own data.

**Faq:**

#### Why does the query not show shipped quantity?

**Answer:** Shipped quantity sits in different structures depending on configuration, such as pick ticket and invoice detail. Rather than guess, the pattern marks where to join your own source so the open quantity is correct.

#### Can I make this a backorder report?

**Answer:** Yes. After subtracting shipped quantity, filter to lines with open quantity greater than zero where available stock is below the open quantity. Use your system's availability rule for the stock side.

#### Will this slow the live system?

**Answer:** It can if run broadly. Keep a date range, filter by customer or location, limit rows, and prefer a reporting copy. Index advice should come from your DBA.

#### Is this valid for Eclipse?

**Answer:** No. It is a pattern for Prophet 21 on SQL Server. Eclipse is a different product with its own data access and reporting tools.

## Search terms

prophet 21 open orders sql, p21 backorder report, p21 oe_hdr oe_line query, prophet 21 sales order lines sql, epicor p21 backorder query, p21 aging open orders, prophet 21 report writer sql, p21 customer service report

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