# SQL Generator for Receipts Missing a Mill Test Certificate

> Source: https://netray.co/tools/open-po-missing-mill-certificate-sql-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generate SQL that finds material receipts requiring an EN 10204 certificate where no certificate record exists, so quality can chase suppliers.

## Category

developer

## Industry

Oil and Gas Equipment

## Audience

- ERP developer
- Quality engineer
- Receiving inspector
- Supplier quality

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Receipt line table

**Key:** receipt_table

**Type:** text

**Default:** dbo.po_receipt_line

**Help:** Table with one row per received line.

#### Certificate table

**Key:** cert_table

**Type:** text

**Default:** dbo.material_certificate

**Help:** Table with one row per attached certificate.

#### Item master table

**Key:** item_table

**Type:** text

**Default:** dbo.item_master

#### Item column that requires a certificate

**Key:** cert_flag

**Type:** text

**Default:** cert_required

#### Required certificate type

**Key:** cert_type

**Type:** select

**Options:**

- 3.1
- 3.2
- 2.2

**Default:** 3.1

#### Receipts from

**Key:** from_date

**Type:** date

**Default:** 2026-01-01

#### Grace period for certificate in days

**Key:** grace_days

**Type:** number

**Default:** 5

**Blocks:**

#### Query 1: receipts with no matching certificate

**Body:** SELECT r.receipt_no,
       r.po_number,
       r.item_code,
       r.heat_number,
       r.vendor_code,
       r.receipt_date,
       DATEDIFF(day, r.receipt_date, GETDATE()) AS days_open
FROM {{receipt_table}} r
JOIN {{item_table}} i ON i.item_code = r.item_code
LEFT JOIN {{cert_table}} c
       ON c.item_code = r.item_code
      AND c.heat_number = r.heat_number
      AND c.cert_type = '{{cert_type}}'
WHERE i.{{cert_flag}} = 1
  AND r.receipt_date >= '{{from_date}}'
  AND c.heat_number IS NULL
  AND DATEDIFF(day, r.receipt_date, GETDATE()) > {{grace_days}}
ORDER BY days_open DESC;

#### Query 2: count of open gaps by supplier

**Body:** SELECT r.vendor_code,
       COUNT(*) AS lines_without_cert,
       MIN(r.receipt_date) AS oldest_receipt
FROM {{receipt_table}} r
JOIN {{item_table}} i ON i.item_code = r.item_code
LEFT JOIN {{cert_table}} c
       ON c.item_code = r.item_code
      AND c.heat_number = r.heat_number
      AND c.cert_type = '{{cert_type}}'
WHERE i.{{cert_flag}} = 1
  AND r.receipt_date >= '{{from_date}}'
  AND c.heat_number IS NULL
  AND DATEDIFF(day, r.receipt_date, GETDATE()) > {{grace_days}}
GROUP BY r.vendor_code
ORDER BY lines_without_cert DESC;

**Notes:** Table and column names are generic placeholders, not the schema of any specific ERP. Map them to your own receipt, certificate and item tables (for example SAP QM certificate tables, Infor SyteLine receiving and lot tables, or a custom table). The T-SQL syntax uses DATEDIFF and GETDATE, adjust for Oracle or PostgreSQL. Test against a copy, and run read only.

## Content

**Intro:** Pressure equipment buyers expect a mill test certificate for each heat of material, often an EN 10204 type 3.1 certificate. Finding the receipts that arrived without one is a daily quality task, and it should not need a manual spreadsheet. This generator builds the SQL once you tell it your table names.

**Sections:**

#### How to use the generator

**Body:** Fill in your receipt table, certificate table and item master table, and the column that marks an item as certificate controlled. Choose the required certificate type and the receipt start date. The tool returns two queries: the detail list of receipt lines with no matching certificate, and a summary count by supplier. Copy the SQL into your reporting tool, a database client or a scheduled job that emails the list to supplier quality.

#### What the query does

**Body:** The detail query joins each receipt line to the item master to keep only certificate controlled items. It then left joins the certificate table on item and heat number, and keeps rows where no certificate was found. The days open column shows how long the gap has existed, and the grace period removes recent receipts that are still in the normal paperwork window. The summary query groups the same gaps by vendor.

**Bullets:**

- Join on heat number, not only on item code.
- Match the certificate type the order requires.
- Use a grace period to avoid noise on new receipts.

#### Adapting to your system

**Body:** Most ERPs keep lot or heat data in a lot table separate from the receipt line, and certificates as attachments rather than a typed table. In that case join through the lot record and test for the existence of an attachment or a certificate flag. Document the mapping once so the query survives upgrades. For Oracle or PostgreSQL replace the date functions with the platform equivalents.

#### Common mistakes

**Body:** Typical errors are matching on item code only, which hides missing certificates when another heat of the same item has one, ignoring partial receipts, and counting a certificate of the wrong type as acceptable. Also avoid running heavy queries against the live transaction database in peak hours, use a reporting replica where possible, and keep the query read only.

**Cta:** If certificate tracking is manual today, we can help build a read only report or an assistant over your receiving and lot data, with the SQL shown for review.

**Faq:**

#### What is an EN 10204 3.1 certificate?

**Answer:** It is an inspection certificate in which the manufacturer declares compliance with the order and reports test results, validated by the manufacturer's authorized inspection representative who is independent of the production department. Type 3.2 adds validation by a third party or the purchaser's representative.

#### Can I run this directly on my ERP database?

**Answer:** Only after mapping the table and column names, and preferably against a copy or a reporting replica. The query is read only, but heavy joins on a production database can still affect users. Review it with your DBA first.

#### Why join on heat number?

**Answer:** The certificate belongs to a heat or lot of material, not to the item code. Joining only on item code could treat a receipt as covered because a certificate exists for a different heat, which is exactly the error the report is meant to catch.

#### Does the query work for Oracle?

**Answer:** Not as written. It uses T-SQL date functions. In Oracle replace DATEDIFF and GETDATE with date subtraction and SYSDATE, and in PostgreSQL use date subtraction and CURRENT_DATE. The join logic stays the same.

## Search terms

missing mill test certificate query, MTR missing report SQL, receipt without material certificate, ERP certificate compliance query, EN 10204 3.1 certificate tracking SQL, purchase receipt certificate gap report, material certificate audit query, heat number receipt SQL

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