# PCBA BOM Staging Table and Validation SQL Generator

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

## Short answer

Create a staging table and validation queries to clean an electronic BOM before loading it into an ERP.

## Category

developer

## Industry

Electronics Manufacturing

## Audience

- ERP analyst
- NPI engineer
- Data migration lead

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Schema name

**Key:** schema

**Type:** text

**Default:** stg

#### Assembly part number

**Key:** assy

**Type:** text

**Default:** PCBA-10452

#### Revision

**Key:** rev

**Type:** text

**Default:** B

#### Table prefix

**Key:** line_prefix

**Type:** text

**Default:** pcba_bom

**Blocks:**

#### 1. Create staging tables

**Body:** CREATE TABLE {{schema}}.{{line_prefix}}_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 {{schema}}.{{line_prefix}}_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)
);

#### 2. Insert example rows

**Body:** INSERT INTO {{schema}}.{{line_prefix}}_line (assy_pn, assy_rev, line_no, comp_pn, mpn, manufacturer, qty_per, uom, dnp_flag)
VALUES ('{{assy}}', '{{rev}}', 10, 'CAP-0402-100NF', 'GRM155R71C104KA88D', 'Murata', 3, 'EA', 'N');

INSERT INTO {{schema}}.{{line_prefix}}_refdes (assy_pn, assy_rev, line_no, ref_des)
VALUES ('{{assy}}', '{{rev}}', 10, 'C1'), ('{{assy}}', '{{rev}}', 10, 'C2'), ('{{assy}}', '{{rev}}', 10, 'C3');

#### 3. Check: quantity does not match reference designator count

**Body:** SELECT l.line_no, l.comp_pn, l.qty_per, COUNT(r.ref_des) AS refdes_count
FROM {{schema}}.{{line_prefix}}_line l
LEFT JOIN {{schema}}.{{line_prefix}}_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 = '{{assy}}' AND l.assy_rev = '{{rev}}' AND l.dnp_flag = 'N'
GROUP BY l.line_no, l.comp_pn, l.qty_per
HAVING COUNT(r.ref_des) <> l.qty_per;

#### 4. Check: component used on lines with no manufacturer part number

**Body:** SELECT line_no, comp_pn
FROM {{schema}}.{{line_prefix}}_line
WHERE assy_pn = '{{assy}}' AND assy_rev = '{{rev}}'
  AND (mpn IS NULL OR mpn = '' OR manufacturer IS NULL OR manufacturer = '');

#### 5. Check: same component number listed on more than one line

**Body:** SELECT comp_pn, COUNT(*) AS line_count, SUM(qty_per) AS total_qty
FROM {{schema}}.{{line_prefix}}_line
WHERE assy_pn = '{{assy}}' AND assy_rev = '{{rev}}'
GROUP BY comp_pn
HAVING COUNT(*) > 1;

**Notes:** Table and column names here are generic staging names, not those of a specific ERP. Adjust data types and syntax for your database (SQL Server, Oracle, PostgreSQL) and map the clean data to your ERP's BOM import tool or API. Test in a non production system first.

## Content

**Intro:** Electronic BOMs from CAD or customers often arrive with quantity errors, missing manufacturer part numbers and duplicate reference designators. This generator gives you staging tables and validation queries to catch them before the data is loaded into your ERP.

**Sections:**

#### How to use the generator

**Body:** Enter the schema, assembly number and revision, then copy the blocks in order. Create the staging tables, load the BOM lines and the reference designators from your spreadsheet using your database import tool, then run the check queries. Fix the rows they return in the staging data, not in the ERP, so you keep a clean audit trail of what changed before the load.

#### Why these checks matter

**Body:** A BOM line quantity that does not match the count of reference designators is one of the most common causes of line shortages and wrong builds. Missing manufacturer part numbers break approved vendor list checks. Duplicated component lines inflate or fragment purchasing demand. The primary key on the reference table blocks a designator from appearing twice on the same revision.

**Bullets:**

- Run checks per assembly revision
- Exclude do not populate lines from count checks
- Keep the staging tables after the load as evidence

#### Worked example

**Body:** A capacitor line shows quantity 4 but only three reference designators are listed. The check query returns the line with refdes_count 3. The engineer finds that C4 was dropped from the CAD export, corrects the staging row and re-runs the check until the query returns nothing.

#### Common mistakes

**Body:** People often load straight into the ERP and rely on its import errors, which seldom explain engineering data problems. Others forget that alternates and substitutes belong on a separate structure, not on additional BOM lines. Do not treat fitted and unfitted lines the same; use the do not populate flag and exclude those lines from the count.

**Cta:** Netray can help build and run BOM validation against your ERP's real tables and import tools as part of a migration or NPI process.

**Faq:**

#### Does this work with my ERP table names?

**Answer:** The tables are generic staging names so you can use them with any ERP. After validation, map the clean data to your ERP's own BOM import tool, service or API.

#### Which databases does the SQL work on?

**Answer:** The statements are standard SQL and work on SQL Server, PostgreSQL and Oracle with minor type changes. For example, Oracle uses VARCHAR2 and NUMBER.

#### How do I handle alternates and approved manufacturers?

**Answer:** Keep them in a separate table linked by component number, with one row per approved manufacturer part number. Do not duplicate the BOM line.

#### Can I use it for engineering change orders?

**Answer:** Yes. Load the new revision into the staging tables under its own revision key and compare it with the previous revision to list added, removed and changed lines.

## Search terms

bom staging table sql, bom validation sql, duplicate reference designator query, bom load erp sql, pcba bom import checks, ebom to mbom data cleanup, bom quantity vs refdes count, bom data migration template

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