# BOM Compliance Declaration Gap SQL Query Generator

> Source: https://netray.co/tools/bom-compliance-declaration-gap-sql-query-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Builds a SQL query and a summary query that list BOM components without a current compliance declaration for a chosen regulation.

## Category

developer

## Audience

- Compliance engineer
- ERP analyst
- Database administrator

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### BOM structure table

**Key:** bom_table

**Type:** text

**Default:** bom_structure

**Help:** Table that links parent to component

#### Item master table

**Key:** item_table

**Type:** text

**Default:** item_master

#### Declaration table

**Key:** decl_table

**Type:** text

**Default:** item_compliance_declaration

#### Parent item column

**Key:** parent_col

**Type:** text

**Default:** parent_item

#### Component item column

**Key:** comp_col

**Type:** text

**Default:** component_item

#### Regulation code

**Key:** regulation

**Type:** select

**Options:**

- ROHS
- REACH_SVHC
- CONFLICT_MINERALS
- PFAS
- PROP65

**Default:** ROHS

#### Maximum declaration age (days)

**Key:** max_age_days

**Type:** number

**Default:** 730

#### Top level assembly

**Key:** top_parent

**Type:** text

**Default:** ASSY-1000

**Blocks:**

#### Component gap list

**Body:** -- Components of {{top_parent}} with no current {{regulation}} declaration
-- Table and column names are generic: map them to your ERP schema
SELECT b.{{parent_col}} AS parent_item,
       b.{{comp_col}} AS component_item,
       i.description,
       i.supplier_code,
       d.status,
       d.declaration_date,
       CASE
         WHEN d.{{comp_col}} IS NULL THEN 'MISSING'
         WHEN d.status <> 'COMPLIANT' THEN 'NOT COMPLIANT'
         ELSE 'EXPIRED'
       END AS gap_reason
FROM {{bom_table}} b
JOIN {{item_table}} i
  ON i.item = b.{{comp_col}}
LEFT JOIN {{decl_table}} d
  ON d.{{comp_col}} = b.{{comp_col}}
 AND d.regulation = '{{regulation}}'
WHERE b.{{parent_col}} = '{{top_parent}}'
  AND (d.{{comp_col}} IS NULL
       OR d.status <> 'COMPLIANT'
       OR d.declaration_date < CURRENT_DATE - {{max_age_days}})
ORDER BY gap_reason, b.{{comp_col}};

#### Summary by gap reason

**Body:** SELECT gap_reason, COUNT(*) AS component_count
FROM (
  SELECT CASE
           WHEN d.{{comp_col}} IS NULL THEN 'MISSING'
           WHEN d.status <> 'COMPLIANT' THEN 'NOT COMPLIANT'
           ELSE 'EXPIRED'
         END AS gap_reason
  FROM {{bom_table}} b
  LEFT JOIN {{decl_table}} d
    ON d.{{comp_col}} = b.{{comp_col}}
   AND d.regulation = '{{regulation}}'
  WHERE b.{{parent_col}} = '{{top_parent}}'
    AND (d.{{comp_col}} IS NULL
         OR d.status <> 'COMPLIANT'
         OR d.declaration_date < CURRENT_DATE - {{max_age_days}})
) gaps
GROUP BY gap_reason;

**Notes:** Table and column names are generic placeholders, not a specific ERP schema. Date arithmetic differs by database: PostgreSQL accepts CURRENT_DATE minus an integer, SQL Server needs DATEADD(day, -730, GETDATE()), Oracle accepts SYSDATE minus a number. This query reads only the first BOM level; use a recursive CTE for multi level BOMs. Test on a copy first.

## Content

**Intro:** Compliance teams are often asked which parts in a product still lack a valid RoHS or REACH declaration. This generator writes the query so you can fill in your own table names and get a list ready for supplier follow up.

**Sections:**

#### How to use the generator

**Body:** Enter the name of your BOM, item and declaration tables, the column names that hold parent and component, the regulation code and how old a declaration may be before it counts as expired. The output updates immediately. Copy it into your SQL client or hand it to your ERP analyst. Most ERPs store compliance flags in user defined fields, so you may need to point the declaration table at a custom table or a view.

#### What the query returns

**Body:** Each row is a component of the chosen assembly that has no declaration for the regulation, has a declaration marked other than compliant, or has a compliant declaration older than the age limit. The gap reason column splits the work: missing means chase the supplier, not compliant means engineering must find an alternative, expired means ask for a refreshed certificate. The second query summarises counts for a status meeting.

#### Going beyond one level

**Body:** Compliance applies to every part in the product, not only first level components. For a full explosion, wrap the BOM join in a recursive common table expression using WITH RECURSIVE or the equivalent in your database, and add an effectivity date filter so you only look at the revision that is currently released. Remember phantom assemblies and consumables that may not appear in the BOM at all.

#### Common mistakes

**Body:** Do not treat a missing row as compliant, and do not compare dates stored as text. Also decide what the age limit means for your policy, because some customers require a refresh every year and others accept a declaration until the part changes. Keep the output as evidence by saving each run with a date for audit.

**Cta:** If you want this running against live ERP data with approvals and supplier emails, Netray can help build it as a read-only report or agent.

**Faq:**

#### Will this work with my ERP?

**Answer:** The structure works with most relational databases, but table and column names are placeholders. You must map them to your schema and adjust date arithmetic. Always run on a read replica or test copy first.

#### How do I handle multi level BOMs?

**Answer:** Use a recursive common table expression to explode the BOM down to the leaf components, then join the declaration table on the exploded list. Filter by revision and effectivity so retired parts are not reported.

#### What age limit should I use?

**Answer:** There is no legal number. Many companies accept two to five years for stable parts and demand a new declaration after any design, material or supplier change. Set the value from your own compliance procedure.

#### Can I use it for PFAS or conflict minerals?

**Answer:** Yes, if your declaration table stores a regulation code for them. Pick the code from the list or edit it. The logic of missing, not compliant and expired fits any declaration based requirement.

## Search terms

bom rohs compliance sql query, find parts without reach declaration sql, component compliance gap report sql, expired supplier declaration query, erp rohs status query, bill of materials compliance query, declaration age sql, missing material declaration report

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