Developer ToolsFree generatorPDF, Excel, Word export

Supplier PPM and On-Time Delivery SQL Query Generator

Reject PPM and on-time delivery are the two numbers every supplier scorecard starts with. This generator writes the SQL for them from your receipt table, so an analyst does not have to rebuild the logic each quarter.

Fill in

Receipt table or view

Vendor column

Quantity received column

Quantity rejected column

Receipt date column

Promise date column

Start date

End date (exclusive)

Minimum receipt lines per vendor

Generated code

Download:

Supplier PPM and on-time delivery

SELECT
  r.vendor_id AS vendor,
  COUNT(*) AS receipt_lines,
  SUM(r.qty_received) AS qty_received,
  SUM(r.qty_rejected) AS qty_rejected,
  ROUND(1000000.0 * SUM(r.qty_rejected) / NULLIF(SUM(r.qty_received), 0), 0) AS reject_ppm,
  ROUND(100.0 * SUM(CASE WHEN r.receipt_date <= r.promise_date THEN 1 ELSE 0 END) / COUNT(*), 1) AS otd_pct
FROM po_receipt_lines r
WHERE r.receipt_date >= DATE '2026-01-01'
  AND r.receipt_date < DATE '2026-10-01'
GROUP BY r.vendor_id
HAVING COUNT(*) >= 10
ORDER BY reject_ppm DESC, otd_pct ASC;

Optional: lines received late

SELECT
  r.vendor_id AS vendor,
  COUNT(*) AS late_lines
FROM po_receipt_lines r
WHERE r.receipt_date > r.promise_date
  AND r.receipt_date >= DATE '2026-01-01'
  AND r.receipt_date < DATE '2026-10-01'
GROUP BY r.vendor_id
ORDER BY late_lines DESC;

Table and column names are generic placeholders. Map them to your ERP, for example a view that joins PO lines to receipts and inspection results. DATE literals work in PostgreSQL, Oracle and MySQL; on SQL Server use CAST('2026-01-01' AS date) instead. Test on a copy of the data first and check how your ERP stores rejects, because some systems record them in a separate inspection table.

Supplier performance view for your ERP

Optional: we can outline a read-only supplier performance view for your ERP tables.

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

How to use the generator

Enter the name of the table or view that holds one row per receipt line, then the column names for vendor, received quantity, rejected quantity, receipt date and promise date. Set the period and the minimum number of lines per vendor so that low-volume suppliers do not distort the ranking. Copy the query into your SQL tool and run it against a reporting copy of the database. If your ERP has no single receipt view, create one first that joins PO lines, receipts and inspection results.

Use a view with one row per receipt line
Run it on a reporting copy, not production
Set a minimum line count for fair ranking
Change the date cut-off to match your fiscal period

The formulas behind the query

Reject PPM is rejected quantity divided by received quantity, multiplied by one million. On-time delivery percentage is the number of lines received on or before the promise date, divided by all lines in the period. Both are calculated per vendor. The query divides with NULLIF so a vendor with no received quantity returns null rather than an error. Rank by PPM first, then by lateness, to see suppliers that are weak on both.

Reading the result

A supplier with 25 lines, 12,000 pieces received and 6 rejected has 500 PPM. A supplier with 3 lines and 1 reject out of 100 pieces has 10,000 PPM but too little data to judge, which is why the minimum line filter exists. Use PPM next to the 8D history for the same supplier before you act. Remember that OTD depends on which promise date you measure, the original or the revised one, so agree that definition first.

Common mistakes

Teams often count rejected lots instead of pieces, include internal transfers in the receipts, or measure OTD against the order date rather than the promise date. Another issue is mixing units of measure across parts, which makes quantities meaningless. Convert to a common unit or measure PPM by lot when units differ. Document the definitions beside the report so scorecards stay consistent between years.

Count pieces, not lots, for PPM
Exclude internal transfers and returns
Agree on original or revised promise date
Check units of measure

Frequently Asked Questions

What is a good supplier PPM?

It depends on the industry and the part. Many automotive customers aim for well under 100 PPM from key suppliers, while other sectors accept higher figures. Set targets by commodity and criticality in your supplier agreement rather than using one number for everything.

Does this work in SAP, Oracle or Epicor?

The SQL is generic, but you must map the table and column names. SAP, Oracle, Epicor, SyteLine and others hold receipts and inspection results in different tables. Build a reporting view first, then point this query at the view.

Why use NULLIF in the PPM formula?

NULLIF returns null when the denominator is zero, which prevents a division by zero error for vendors with no received quantity in the period. Those vendors then show an empty PPM, which is more honest than a zero.

Should I measure on-time delivery on the original date?

Many buyers measure against the original confirmed promise date, because revised dates hide chronic lateness. Some also report against the revised date to see recovery performance. Choose one primary definition and write it in the scorecard rules.

If your receipt and inspection data sits in several ERP tables, we can map them into a clean supplier performance view and a natural language question layer that stays read only.

Talk to an Expert Explore More Free Tools

Related Tools

Quality & Inspection

More Developer Tools tools

See all

Back to All Tools