Supply Chain & InventoryFree generatorPDF, Excel, Word export

SQL Open Purchase Orders Query Template

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.

Fill in

PO header table

PO line table

PO number column

Vendor column (on header)

Item column (on line)

Ordered quantity column

Received quantity column

Unit price column

Due date column (on line)

Closed or cancelled status values

As-of date

Generated code

Download:

Open PO lines with open value and days late

SELECT h.po_number,
       h.vendor_id,
       l.item,
       l.qty_ordered AS qty_ordered,
       l.qty_received AS qty_received,
       l.qty_ordered - l.qty_received AS qty_open,
       (l.qty_ordered - l.qty_received) * l.unit_price AS open_value,
       l.due_date AS due_date,
       CASE WHEN l.due_date < '2026-10-20' THEN 'LATE' ELSE 'ON TIME' END AS status_flag
FROM po_header h
JOIN po_line l ON l.po_number = h.po_number
WHERE l.qty_ordered > l.qty_received
  AND h.status NOT IN ('C','X')
ORDER BY l.due_date, h.po_number;

Open value by vendor

SELECT h.vendor_id,
       COUNT(*) AS open_lines,
       SUM((l.qty_ordered - l.qty_received) * l.unit_price) AS open_value,
       SUM(CASE WHEN l.due_date < '2026-10-20' THEN 1 ELSE 0 END) AS late_lines
FROM po_header h
JOIN po_line l ON l.po_number = h.po_number
WHERE l.qty_ordered > l.qty_received
  AND h.status NOT IN ('C','X')
GROUP BY h.vendor_id
ORDER BY open_value DESC;

Lines to check before trusting the numbers

-- Over-received lines (received more than ordered)
SELECT h.po_number, l.item, l.qty_ordered, l.qty_received
FROM po_header h
JOIN po_line l ON l.po_number = h.po_number
WHERE l.qty_received > l.qty_ordered;

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.

Open PO reporting on your own ERP

We can adapt this query to your exact schema and receipt logic. Optional expert review.

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

How to use the template

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

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.

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

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

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.

Frequently Asked Questions

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

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools

Related Tools

Developer Tools

More Supply Chain & Inventory tools

See all

Back to All Tools