# Late Purchase Order Lines SQL Query Generator

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

## Short answer

Generates a SQL query that lists open PO lines past their promise date with days late, plus a roll-up by supplier. Table names are yours to fill in.

## Category

developer

## Audience

- Buyer
- ERP analyst
- Report developer

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### PO header table

**Key:** po_header

**Type:** text

**Default:** purchase_order_header

**Help:** Your ERP table holding one row per PO.

#### PO line table

**Key:** po_line

**Type:** text

**Default:** purchase_order_line

**Help:** Your ERP table holding one row per PO line.

#### Vendor table

**Key:** vendor_table

**Type:** text

**Default:** vendor

#### As of date

**Key:** as_of

**Type:** date

**Default:** 2026-10-20

#### Minimum days late

**Key:** min_days

**Type:** number

**Default:** 1

**Blocks:**

#### Late PO lines with open quantity

**Body:** SELECT h.po_number,
       h.vendor_id,
       v.vendor_name,
       l.line_number,
       l.item_number,
       l.order_qty,
       l.received_qty,
       l.order_qty - l.received_qty AS open_qty,
       l.promise_date,
       DATEDIFF(day, l.promise_date, '{{as_of}}') AS days_late
FROM {{po_header}} h
JOIN {{po_line}} l ON l.po_number = h.po_number
JOIN {{vendor_table}} v ON v.vendor_id = h.vendor_id
WHERE l.order_qty > l.received_qty
  AND l.promise_date < '{{as_of}}'
  AND DATEDIFF(day, l.promise_date, '{{as_of}}') >= {{min_days}}
ORDER BY days_late DESC, h.po_number, l.line_number;

#### Roll-up by supplier

**Body:** SELECT h.vendor_id,
       v.vendor_name,
       COUNT(*) AS late_lines,
       MAX(DATEDIFF(day, l.promise_date, '{{as_of}}')) AS worst_days_late
FROM {{po_header}} h
JOIN {{po_line}} l ON l.po_number = h.po_number
JOIN {{vendor_table}} v ON v.vendor_id = h.vendor_id
WHERE l.order_qty > l.received_qty
  AND l.promise_date < '{{as_of}}'
GROUP BY h.vendor_id, v.vendor_name
ORDER BY late_lines DESC;

**Notes:** Column names such as po_number, vendor_id, order_qty, received_qty and promise_date are generic. Rename them to your ERP's real columns (for example in SAP EKET and EKPO, Infor SyteLine poitem, or Oracle PO_LINE_LOCATIONS_ALL) and add company or site filters. DATEDIFF is SQL Server syntax: use (date1 - date2) on Oracle and PostgreSQL. Filter out closed, cancelled and blanket lines according to your status codes. Test on a copy before scheduling.

## Content

**Intro:** Every buyer needs a list of what is late today. This generator writes a SQL query that returns open PO lines past their promise date with open quantity and days late, plus a supplier roll-up you can drop into a daily expedite report.

**Sections:**

#### How to use it

**Body:** Enter the names of your PO header, PO line and vendor tables, the as-of date and the minimum days late. Copy the generated query into your SQL client, then replace the generic column names with those from your ERP schema. Run it read-only against a reporting copy if you have one. Sort by days late to start the phone calls with the worst offenders, and use the supplier roll-up in the weekly review.

**Bullets:**

- Use a reporting replica rather than the live transaction database
- Filter by buyer or plant to build one list per person
- Schedule the query to email before the buyers' morning meeting

#### The logic behind the query

**Body:** A line is late when the quantity received is lower than the quantity ordered and the promise date is before the as-of date. Open quantity is order quantity minus received quantity. Days late is the difference between today and the promise date. Joining the vendor table gives readable names. Partial deliveries stay on the list until the remaining quantity arrives, which is why lines are the grain rather than whole POs.

#### Common mistakes

**Body:** The most frequent error is measuring lateness against the original request date in one report and the confirmed date in another, so buyers argue about the numbers. Decide which date counts and use it everywhere. Others include forgetting cancelled or closed lines, counting over-receipts as negative open quantity, ignoring delivery tolerance, and joining on PO number without the company or site key in multi-entity ERPs, which duplicates rows.

**Cta:** If your team writes this report by hand for every ERP, Netray can build a read-only question interface over your purchasing data that shows the SQL it ran.

**Faq:**

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

**Answer:** The structure works anywhere, but the table and column names are generic. You must map them to your ERP: for example SAP uses EKKO, EKPO and EKET, SyteLine uses po and poitem, and Oracle EBS uses PO_HEADERS_ALL and PO_LINE_LOCATIONS_ALL. Check your own schema before running it.

#### Why is days late negative or zero for some lines?

**Answer:** The query filters to promise dates before the as-of date, so days late should be at least one. If you see zero or negative values, your date columns may carry time parts or you changed the minimum days. Cast to date and confirm time zones.

#### How do I exclude blanket or scheduled releases?

**Answer:** Add a filter on your line type or item category column so only standard order lines remain, and run a separate query on release schedule lines for blanket POs. Blanket headers often have no single promise date, so mixing them gives false lateness.

#### Can I use this for supplier scorecards?

**Answer:** It shows current open lateness, not historical on-time performance. For a scorecard, compare receipt date to promise date on received lines over a period. Use the open-line list for expediting and the received-line history for scoring.

## Search terms

overdue purchase orders sql, late PO report query, open purchase order lines past due sql, purchase order expedite report, supplier late deliveries sql, past due PO report erp, open PO report query

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