PCBA BOM Staging Table and Validation SQL Generator

CREATE TABLE stg.pcba_bom_line (
  assy_pn       VARCHAR(40)  NOT NULL,
  assy_rev      VARCHAR(10)  NOT NULL,
  line_no       INT          NOT NULL,
  comp_pn       VARCHAR(40)  NOT NULL,
  mpn           VARCHAR(80)  NULL,
  manufacturer  VARCHAR(80)  NULL,
  qty_per       DECIMAL(12,4) NOT NULL,
  uom           VARCHAR(10)  NOT NULL,
  dnp_flag      CHAR(1)      NOT NULL DEFAULT 'N',
  PRIMARY KEY (assy_pn, assy_rev, line_no)
);

CREATE TABLE stg.pcba_bom_refdes (
  assy_pn   VARCHAR(40) NOT NULL,
  assy_rev  VARCHAR(10) NOT NULL,
  line_no   INT         NOT NULL,
  ref_des   VARCHAR(20) NOT NULL,
  PRIMARY KEY (assy_pn, assy_rev, ref_des)
);

INSERT INTO stg.pcba_bom_line (assy_pn, assy_rev, line_no, comp_pn, mpn, manufacturer, qty_per, uom, dnp_flag)
VALUES ('PCBA-10452', 'B', 10, 'CAP-0402-100NF', 'GRM155R71C104KA88D', 'Murata', 3, 'EA', 'N');

INSERT INTO stg.pcba_bom_refdes (assy_pn, assy_rev, line_no, ref_des)
VALUES ('PCBA-10452', 'B', 10, 'C1'), ('PCBA-10452', 'B', 10, 'C2'), ('PCBA-10452', 'B', 10, 'C3');

SELECT l.line_no, l.comp_pn, l.qty_per, COUNT(r.ref_des) AS refdes_count
FROM stg.pcba_bom_line l
LEFT JOIN stg.pcba_bom_refdes r
  ON r.assy_pn = l.assy_pn AND r.assy_rev = l.assy_rev AND r.line_no = l.line_no
WHERE l.assy_pn = 'PCBA-10452' AND l.assy_rev = 'B' AND l.dnp_flag = 'N'
GROUP BY l.line_no, l.comp_pn, l.qty_per
HAVING COUNT(r.ref_des) <> l.qty_per;

SELECT line_no, comp_pn
FROM stg.pcba_bom_line
WHERE assy_pn = 'PCBA-10452' AND assy_rev = 'B'
  AND (mpn IS NULL OR mpn = '' OR manufacturer IS NULL OR manufacturer = '');

SELECT comp_pn, COUNT(*) AS line_count, SUM(qty_per) AS total_qty
FROM stg.pcba_bom_line
WHERE assy_pn = 'PCBA-10452' AND assy_rev = 'B'
GROUP BY comp_pn
HAVING COUNT(*) > 1;