BOM Compliance Declaration Gap SQL Query Generator

-- Components of ASSY-1000 with no current ROHS declaration
-- Table and column names are generic: map them to your ERP schema
SELECT b.parent_item AS parent_item,
       b.component_item AS component_item,
       i.description,
       i.supplier_code,
       d.status,
       d.declaration_date,
       CASE
         WHEN d.component_item IS NULL THEN 'MISSING'
         WHEN d.status <> 'COMPLIANT' THEN 'NOT COMPLIANT'
         ELSE 'EXPIRED'
       END AS gap_reason
FROM bom_structure b
JOIN item_master i
  ON i.item = b.component_item
LEFT JOIN item_compliance_declaration d
  ON d.component_item = b.component_item
 AND d.regulation = 'ROHS'
WHERE b.parent_item = 'ASSY-1000'
  AND (d.component_item IS NULL
       OR d.status <> 'COMPLIANT'
       OR d.declaration_date < CURRENT_DATE - 730)
ORDER BY gap_reason, b.component_item;

SELECT gap_reason, COUNT(*) AS component_count
FROM (
  SELECT CASE
           WHEN d.component_item IS NULL THEN 'MISSING'
           WHEN d.status <> 'COMPLIANT' THEN 'NOT COMPLIANT'
           ELSE 'EXPIRED'
         END AS gap_reason
  FROM bom_structure b
  LEFT JOIN item_compliance_declaration d
    ON d.component_item = b.component_item
   AND d.regulation = 'ROHS'
  WHERE b.parent_item = 'ASSY-1000'
    AND (d.component_item IS NULL
         OR d.status <> 'COMPLIANT'
         OR d.declaration_date < CURRENT_DATE - 730)
) gaps
GROUP BY gap_reason;