BOM Component Tables

Understanding how Bill of Materials (BOM) components are copied to Work Orders, and why BOM changes don't update existing Work Orders.

---

The BOM Snapshot Pattern

BOM TO WORK ORDER COMPONENT FLOW
═══════════════════════════════════════════════════════════════════════════════

BOM (Master)                               Work Order (Transaction)
┌───────────────────────────┐             ┌───────────────────────────┐
│ Assembly: Widget-FG       │             │ Work Order: WO-001        │
│                           │             │ Assembly: Widget-FG       │
│ Components:               │  COPIED     │                           │
│ ├─ Part-A: 2 units        │─────────────▶│ TransactionLine:          │
│ ├─ Part-B: 5 units        │  AT WO      │ ├─ Part-A: 2 units        │
│ └─ Part-C: 1 unit         │  CREATION   │ ├─ Part-B: 5 units        │
└───────────────────────────┘             │ └─ Part-C: 1 unit         │
                                          └───────────────────────────┘
         │                                         │
         ▼                                         ▼
┌───────────────────────────┐             ┌───────────────────────────┐
│ BOM UPDATED:              │             │ WO UNCHANGED:             │
│ ├─ Part-A: 3 units  ◄──   │   NO        │ ├─ Part-A: 2 units        │
│ ├─ Part-B: 5 units        │   SYNC      │ ├─ Part-B: 5 units        │
│ └─ Part-D: 2 units  ◄──   │             │ └─ Part-C: 1 unit         │
└───────────────────────────┘             └───────────────────────────┘

Existing Work Orders keep their ORIGINAL components.

---

Master BOM Tables

Legacy BOM (AssemblyItemMember)

For accounts NOT using Advanced Bill of Materials:

LEGACY BOM STRUCTURE
═══════════════════════════════════════════════════════════════════════════════

Assembly Item                              AssemblyItemMember (Junction)
┌───────────────────┐                     ┌─────────────────────────────┐
│ Item: Widget-FG   │                     │ parentitem: 500             │
│ ID: 500           │─────────────────────▶│ item: 101 (Part-A)          │
│ Type: Assembly    │                     │ quantity: 2                 │
│                   │                     │ memberunit: 1               │
└───────────────────┘                     │ linenumber: 1               │
                                          │ itemsource: STOCK           │
                                          ├─────────────────────────────┤
                                          │ parentitem: 500             │
                                          │ item: 102 (Part-B)          │
                                          │ quantity: 5                 │
                                          └─────────────────────────────┘

Table	Purpose	Key Columns
AssemblyItemMember	Legacy BOM components	parentitem, item, quantity, memberunit, linenumber, itemsource

ItemSource Values:

• STOCK - Standard stocked component
• Phantom - Sub-assembly assembled when needed

Advanced BOM (BomRevisionComponentMember)

For accounts using the Advanced Bill of Materials feature:

ADVANCED BOM STRUCTURE
═══════════════════════════════════════════════════════════════════════════════

Assembly Item          BillOfMaterials         BomRevision              Components
┌─────────────┐       ┌─────────────┐        ┌─────────────┐        ┌──────────────────┐
│ Widget-FG   │──────▶│ BOM-001     │───────▶│ Rev A       │───────▶│ BomRevision      │
│ ID: 500     │       │             │        │ ID: 1001    │        │ ComponentMember  │
└─────────────┘       └─────────────┘        │ isdefault:T │        │                  │
                                             │ effectivedt │        │ bomrevision:1001 │
                                             └─────────────┘        │ item: 101        │
                                                                    │ bomquantity: 2   │
                                                                    └──────────────────┘

Table	Purpose	Key Columns
BillOfMaterials	BOM header	id, name, assembly
BomRevision	Revision versions	id, billofmaterials, name, isdefault, effectivedate, obsoletedate
BomRevisionComponentMember	Components per revision	bomrevision, item, bomquantity, unit, linenumber

Migration Note

As of NetSuite 2023.1, legacy embedded BOMs are deprecated. NetSuite recommends migrating to Advanced Bill of Materials.

---

Work Order Components (Snapshot)

Work Order components are stored in TransactionLine:

WORK ORDER COMPONENT STORAGE
═══════════════════════════════════════════════════════════════════════════════

Transaction (Work Order)                   TransactionLine (Components)
┌─────────────────────────────┐           ┌─────────────────────────────┐
│ ID: 12345                   │           │ transaction: 12345          │
│ Type: WorkOrd               │           │ item: 101 (Part-A)          │
│ TranID: WO-001              │──────────▶│ quantity: -2  (negative!)   │
│ AssemblyItem: 500           │           │ linesequencenumber: 1       │
│                             │           ├─────────────────────────────┤
└─────────────────────────────┘           │ transaction: 12345          │
                                          │ item: 102 (Part-B)          │
                                          │ quantity: -5  (negative!)   │
                                          │ linesequencenumber: 2       │
                                          └─────────────────────────────┘

Note: Component quantities are NEGATIVE (consumption from inventory)

---

Query Examples

Get Legacy BOM Components

SELECT
    parent.itemid AS assembly,
    parent.displayname AS assembly_name,
    aim.linenumber AS line,
    component.itemid AS component_code,
    component.displayname AS component_name,
    aim.quantity,
    BUILTIN.DF(aim.memberunit) AS uom,
    aim.itemsource
FROM Item parent
INNER JOIN AssemblyItemMember aim ON aim.parentitem = parent.id
INNER JOIN Item component ON component.id = aim.item
WHERE parent.id = 500  -- Assembly item ID
ORDER BY aim.linenumber

Get Advanced BOM Components

SELECT
    BUILTIN.DF(br.billofmaterials) AS bom_name,
    br.name AS revision_name,
    br.effectivedate,
    BUILTIN.DF(brc.item) AS component,
    brc.bomquantity AS quantity,
    BUILTIN.DF(brc.unit) AS uom,
    brc.linenumber
FROM BomRevision br
INNER JOIN BomRevisionComponentMember brc ON brc.bomrevision = br.id
WHERE br.billofmaterials = 100  -- BOM ID
  AND br.isdefault = 'T'
ORDER BY brc.linenumber

Get Work Order Components (Snapshot)

SELECT
    t.tranid AS work_order,
    t.trandate,
    BUILTIN.DF(t.assemblyitem) AS assembly,
    tl.linesequencenumber AS line,
    BUILTIN.DF(tl.item) AS component,
    ABS(tl.quantity) AS quantity,  -- Convert negative to positive
    BUILTIN.DF(tl.units) AS uom
FROM Transaction t
INNER JOIN TransactionLine tl ON tl.transaction = t.id
WHERE t.type = 'WorkOrd'
  AND tl.mainline = 'F'
  AND tl.item IS NOT NULL
  AND t.id = 12345
ORDER BY tl.linesequencenumber

Compare BOM vs Work Order Components

-- Find Work Orders where components differ from current BOM
WITH CurrentBOM AS (
    SELECT
        aim.parentitem AS assembly_id,
        aim.item,
        aim.quantity
    FROM AssemblyItemMember aim
),
WOComponents AS (
    SELECT
        t.id AS wo_id,
        t.tranid,
        t.assemblyitem,
        tl.item,
        ABS(tl.quantity) AS quantity
    FROM Transaction t
    INNER JOIN TransactionLine tl ON tl.transaction = t.id
    WHERE t.type = 'WorkOrd'
      AND tl.mainline = 'F'
      AND tl.item IS NOT NULL
)
SELECT
    woc.tranid AS work_order,
    BUILTIN.DF(woc.item) AS component,
    woc.quantity AS wo_quantity,
    bom.quantity AS current_bom_quantity,
    CASE
        WHEN bom.quantity IS NULL THEN 'Removed from BOM'
        WHEN woc.quantity != bom.quantity THEN 'Quantity changed'
        ELSE 'Match'
    END AS status
FROM WOComponents woc
LEFT JOIN CurrentBOM bom
    ON bom.assembly_id = woc.assemblyitem
    AND bom.item = woc.item
WHERE woc.quantity != NVL(bom.quantity, 0)
ORDER BY woc.tranid

---

How Default BOM Selection Works

DEFAULT BOM SELECTION WORKFLOW
═══════════════════════════════════════════════════════════════════════════════

When Work Order is created:

1. Check Location-Specific Default
   ┌─────────────────────────────────────┐
   │ Does assembly have a BOM marked as  │
   │ default for THIS location?          │──▶ Yes: Use location-specific BOM
   └─────────────────────────────────────┘
                    │ No
                    ▼
2. Check Master Default
   ┌─────────────────────────────────────┐
   │ Does assembly have a Master         │
   │ Default BOM?                        │──▶ Yes: Use master default BOM
   └─────────────────────────────────────┘
                    │ No
                    ▼
3. No Components
   ┌─────────────────────────────────────┐
   │ Work Order created with empty       │
   │ component list. Must add manually.  │
   └─────────────────────────────────────┘

---

Effective Dating and Revisions

BOM components can have effective and obsolete dates:

-- Get components effective on a specific date
SELECT
    BUILTIN.DF(brc.item) AS component,
    brc.bomquantity,
    br.effectivedate,
    br.obsoletedate
FROM BomRevision br
INNER JOIN BomRevisionComponentMember brc ON brc.bomrevision = br.id
WHERE br.billofmaterials = 100
  AND (br.effectivedate IS NULL OR br.effectivedate <= SYSDATE)
  AND (br.obsoletedate IS NULL OR br.obsoletedate > SYSDATE)

note

NetSuite automatically selects the correct revision based on the Work Order's transaction date or production start date.

---

Common Pitfalls

Pitfall 1: Expecting Automatic Component Updates

❌ WRONG EXPECTATION:
"I added a new component to the BOM, so all open Work Orders should include it."

✅ REALITY:
Only Work Orders created AFTER the BOM change will have the new component.
Existing WOs must be manually updated if needed.

Pitfall 2: Forgetting Negative Quantities

-- ❌ WRONG: Using raw quantity (negative for WO components)
SELECT tl.quantity FROM TransactionLine tl
WHERE tl.transaction = 12345
-- Returns: -2, -5, -1

-- ✅ CORRECT: Use ABS() to get positive values
SELECT ABS(tl.quantity) AS quantity FROM TransactionLine tl
WHERE tl.transaction = 12345
-- Returns: 2, 5, 1

Pitfall 3: Mixing Legacy and Advanced BOM Tables

-- ❌ WRONG: Using AssemblyItemMember when Advanced BOM is enabled
-- May return empty results

-- ✅ CORRECT: Check which feature is enabled and use appropriate table
-- Legacy: AssemblyItemMember
-- Advanced: BomRevisionComponentMember

---

Quick Reference

Legacy BOM Query Pattern

FROM Item parent
INNER JOIN AssemblyItemMember aim ON aim.parentitem = parent.id
INNER JOIN Item component ON component.id = aim.item

Advanced BOM Query Pattern

FROM BomRevision br
INNER JOIN BomRevisionComponentMember brc ON brc.bomrevision = br.id
INNER JOIN Item component ON component.id = brc.item

Work Order Components Pattern

FROM Transaction t
INNER JOIN TransactionLine tl ON tl.transaction = t.id
WHERE t.type = 'WorkOrd'
  AND tl.mainline = 'F'
  AND tl.item IS NOT NULL

---

See Also

• Snapshot Tables Overview - Understanding the snapshot pattern
• Address Tables - Similar pattern for addresses
• Other Snapshots - Prices, rates, and other snapshotted fields