Developer ToolsFree generatorPDF, Excel, Word export

Late Purchase Order Lines SQL Query Generator

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.

Fill in

PO header table

Your ERP table holding one row per PO.

PO line table

Your ERP table holding one row per PO line.

Vendor table

As of date

Minimum days late

Generated code

Download:

Late PO lines with open quantity

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, '2026-10-20') AS days_late
FROM purchase_order_header h
JOIN purchase_order_line l ON l.po_number = h.po_number
JOIN vendor v ON v.vendor_id = h.vendor_id
WHERE l.order_qty > l.received_qty
  AND l.promise_date < '2026-10-20'
  AND DATEDIFF(day, l.promise_date, '2026-10-20') >= 1
ORDER BY days_late DESC, h.po_number, l.line_number;

Roll-up by supplier

SELECT h.vendor_id,
       v.vendor_name,
       COUNT(*) AS late_lines,
       MAX(DATEDIFF(day, l.promise_date, '2026-10-20')) AS worst_days_late
FROM purchase_order_header h
JOIN purchase_order_line l ON l.po_number = h.po_number
JOIN vendor v ON v.vendor_id = h.vendor_id
WHERE l.order_qty > l.received_qty
  AND l.promise_date < '2026-10-20'
GROUP BY h.vendor_id, v.vendor_name
ORDER BY late_lines DESC;

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.

Ask your ERP what is late

Optional: see how a read-only natural language layer can answer late PO questions from your own data.

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

How to use it

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.

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

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

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.

Frequently Asked Questions

Does this work with SAP, Oracle or SyteLine?

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools

Related Tools

Supply Chain & Inventory

More Developer Tools tools

See all

Back to All Tools