# SQL Open Purchase Orders Query Template

> Source: https://netray.co/tools/sql-open-purchase-orders-query-template
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

A configurable open PO line query with open quantity, open value and days past due, ready to adapt to any ERP schema.

## Category

supply-chain

## Audience

- Buyer
- ERP analyst
- Planner

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### PO header table

**Key:** hdr

**Type:** text

**Default:** po_header

#### PO line table

**Key:** line

**Type:** text

**Default:** po_line

#### PO number column

**Key:** pono

**Type:** text

**Default:** po_number

#### Vendor column (on header)

**Key:** vendor

**Type:** text

**Default:** vendor_id

#### Item column (on line)

**Key:** item

**Type:** text

**Default:** item

#### Ordered quantity column

**Key:** ordqty

**Type:** text

**Default:** qty_ordered

#### Received quantity column

**Key:** rcvqty

**Type:** text

**Default:** qty_received

#### Unit price column

**Key:** price

**Type:** text

**Default:** unit_price

#### Due date column (on line)

**Key:** due

**Type:** text

**Default:** due_date

#### Closed or cancelled status values

**Key:** closed

**Type:** text

**Default:** 'C','X'

#### As-of date

**Key:** asof

**Type:** date

**Default:** 2026-10-20

**Blocks:**

#### Open PO lines with open value and days late

**Body:** SELECT h.{{pono}},
       h.{{vendor}},
       l.{{item}},
       l.{{ordqty}} AS qty_ordered,
       l.{{rcvqty}} AS qty_received,
       l.{{ordqty}} - l.{{rcvqty}} AS qty_open,
       (l.{{ordqty}} - l.{{rcvqty}}) * l.{{price}} AS open_value,
       l.{{due}} AS due_date,
       CASE WHEN l.{{due}} < '{{asof}}' THEN 'LATE' ELSE 'ON TIME' END AS status_flag
FROM {{hdr}} h
JOIN {{line}} l ON l.{{pono}} = h.{{pono}}
WHERE l.{{ordqty}} > l.{{rcvqty}}
  AND h.status NOT IN ({{closed}})
ORDER BY l.{{due}}, h.{{pono}};

#### Open value by vendor

**Body:** SELECT h.{{vendor}},
       COUNT(*) AS open_lines,
       SUM((l.{{ordqty}} - l.{{rcvqty}}) * l.{{price}}) AS open_value,
       SUM(CASE WHEN l.{{due}} < '{{asof}}' THEN 1 ELSE 0 END) AS late_lines
FROM {{hdr}} h
JOIN {{line}} l ON l.{{pono}} = h.{{pono}}
WHERE l.{{ordqty}} > l.{{rcvqty}}
  AND h.status NOT IN ({{closed}})
GROUP BY h.{{vendor}}
ORDER BY open_value DESC;

#### Lines to check before trusting the numbers

**Body:** -- Over-received lines (received more than ordered)
SELECT h.{{pono}}, l.{{item}}, l.{{ordqty}}, l.{{rcvqty}}
FROM {{hdr}} h
JOIN {{line}} l ON l.{{pono}} = h.{{pono}}
WHERE l.{{rcvqty}} > l.{{ordqty}};

**Notes:** Table and column names are placeholders, not those of any specific ERP. Map them to your schema: for example the header and line tables in SAP are EKKO and EKPO, but SAP also has schedule lines (EKET) and goods receipt history (EKBE), so quantities should come from those, not a single column. Run read-only and verify against the ERP's own open PO report.

## Content

**Intro:** Every ERP has an open purchase order report, and every buyer eventually needs one the standard screen cannot give them. This template gives you the core query: open quantity, open value and late flag per PO line, which you can adapt to any schema.

**Sections:**

#### How to use the template

**Body:** Fill in your header and line table names and the column names for PO number, vendor, item, quantities, price and due date. Set the status values that mean closed or cancelled in your ERP. The first block lists every line where ordered exceeds received. The second rolls that up by vendor so you can see where late exposure sits. The third finds over-received lines, which are the usual reason an open PO total does not match the ledger.

#### The logic behind open quantity

**Body:** Open quantity is ordered minus received, floored at zero, but many ERPs add complications. Partial receipts post to a receipts table rather than to the line. Returns to vendor reverse received quantity. Delivery schedules split one line into several due dates. Some systems also allow a line to be force-closed while quantity remains open. If your ERP has any of these, sum the receipt history instead of trusting one column, and join to the schedule table to get true due dates.

**Bullets:**

- Open qty = ordered - received, never below zero
- Open value = open qty x unit price in PO currency
- Exclude closed, cancelled and deleted lines explicitly
- Convert foreign currency before totaling across vendors

#### Reading the output

**Body:** Sort by due date to see what is late first. A vendor with a high late line count and a small open value is a nuisance. A vendor with a few late lines and a large open value is a risk to production and cash planning. Compare the grand total of open value to the ERP's accrual or uninvoiced receipts figure; large unexplained differences usually point to over-receipts, unit of measure conversions or lines that should have been closed.

#### Common mistakes

**Body:** Forgetting unit of measure conversion is the classic error: ordering in boxes and receiving in each makes open quantities meaningless. Mixing currencies in a SUM gives a number that means nothing. Using the header status alone hides lines that are individually closed. Joining a schedule table without grouping multiplies rows. Always reconcile row count and total value against one known PO before sharing the report.

**Cta:** If buyers keep asking IT for variations of this report, we can connect a read-only natural language layer to your ERP so they can ask for open lines by vendor, buyer or commodity themselves.

**Faq:**

#### Why does my open PO total not match the ERP report?

**Answer:** Common causes are partial receipts stored in a separate table, unit of measure differences, foreign currency, closed lines still showing a quantity gap, and delivery schedule rows duplicating lines. Reconcile one PO line by line to find which applies.

#### Does this work for SAP, Oracle or SyteLine?

**Answer:** The pattern is the same but table names differ. SAP uses EKKO, EKPO, EKET and EKBE; SyteLine and Epicor have their own PO and release tables. Replace the placeholders and use the receipt history your ERP provides.

#### How do I show only lines that are past due?

**Answer:** Add AND l.due_date < your as-of date to the WHERE clause, using your real column name. Keep the open quantity condition so fully received lines do not appear even when their due date has passed.

#### Should I run this on the production database?

**Answer:** Prefer a read replica or reporting copy. If you must run on production, use a read-only login, keep the query selective and run it outside the busiest transaction windows. Never alter data from a query you pasted.

## Search terms

sql open purchase orders, open po report sql, open purchase order query, purchase order lines not received sql, late purchase orders sql query, po open quantity sql, erp open po report, sql purchase order due date query

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