# Oracle EBS BOM Explosion SQL Query Generator

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

## Short answer

Produces multilevel bill of material explosion and where-used SQL for Oracle EBS using hierarchical queries, with organization, item and effectivity inputs.

## Category

developer

## Erp

Oracle E-Business Suite

## Audience

- Oracle EBS consultant
- Oracle developer
- Bills of material analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Organization ID

**Key:** org_id

**Type:** number

**Default:** 207

#### Top assembly (segment1)

**Key:** assembly

**Type:** text

**Default:** AS-1000

#### Component for where-used (segment1)

**Key:** component

**Type:** text

**Default:** RM-2040

#### Maximum levels

**Key:** max_level

**Type:** number

**Default:** 10

**Blocks:**

#### 1. Multilevel explosion (current effectivity, primary BOM)

**Body:** SELECT LEVEL AS bom_level,
       LPAD(' ', 2 * (LEVEL - 1)) || msi_p.segment1 AS assembly,
       msi_c.segment1 AS component,
       msi_c.description,
       bic.component_quantity,
       bic.operation_seq_num,
       bic.effectivity_date,
       bic.disable_date
FROM   bom_structures_b bom,
       bom_components_b bic,
       mtl_system_items_b msi_p,
       mtl_system_items_b msi_c
WHERE  bic.bill_sequence_id = bom.bill_sequence_id
AND    msi_p.inventory_item_id = bom.assembly_item_id
AND    msi_p.organization_id = bom.organization_id
AND    msi_c.inventory_item_id = bic.component_item_id
AND    msi_c.organization_id = bom.organization_id
AND    bom.alternate_bom_designator IS NULL
AND    bic.effectivity_date <= SYSDATE
AND    (bic.disable_date IS NULL OR bic.disable_date > SYSDATE)
START WITH bom.organization_id = {{org_id}}
       AND bom.assembly_item_id = (SELECT inventory_item_id
                                   FROM   mtl_system_items_b
                                   WHERE  segment1 = '{{assembly}}'
                                   AND    organization_id = {{org_id}})
CONNECT BY NOCYCLE PRIOR bic.component_item_id = bom.assembly_item_id
       AND bom.organization_id = PRIOR bom.organization_id
       AND LEVEL <= {{max_level}}
ORDER SIBLINGS BY bic.operation_seq_num;

#### 2. Single-level where-used

**Body:** SELECT msi_p.segment1 AS parent_assembly,
       msi_p.description,
       bic.component_quantity,
       bic.effectivity_date,
       bic.disable_date
FROM   bom_components_b bic,
       bom_structures_b bom,
       mtl_system_items_b msi_p,
       mtl_system_items_b msi_c
WHERE  bic.bill_sequence_id = bom.bill_sequence_id
AND    msi_p.inventory_item_id = bom.assembly_item_id
AND    msi_p.organization_id = bom.organization_id
AND    msi_c.inventory_item_id = bic.component_item_id
AND    msi_c.organization_id = bom.organization_id
AND    bom.organization_id = {{org_id}}
AND    bom.alternate_bom_designator IS NULL
AND    msi_c.segment1 = '{{component}}'
AND    (bic.disable_date IS NULL OR bic.disable_date > SYSDATE)
ORDER BY msi_p.segment1;

#### 3. Bills with no components

**Body:** SELECT msi.segment1 AS assembly, msi.description
FROM   bom_structures_b bom,
       mtl_system_items_b msi
WHERE  msi.inventory_item_id = bom.assembly_item_id
AND    msi.organization_id = bom.organization_id
AND    bom.organization_id = {{org_id}}
AND    bom.alternate_bom_designator IS NULL
AND    NOT EXISTS (SELECT 1
                   FROM   bom_components_b bic
                   WHERE  bic.bill_sequence_id = bom.bill_sequence_id)
ORDER BY msi.segment1;

**Notes:** Written for Release 12 table names. In 11i the tables are BOM_BILL_OF_MATERIALS and BOM_INVENTORY_COMPONENTS. This simple hierarchy ignores phantom handling, planning percent, option classes and quantity roll-up, so for costing or MRP use the supported BOM explosion programs or the BOM explosion APIs. Verify against your release.

## Content

**Intro:** A multilevel bill of material is a recursive structure, and Oracle EBS stores it as bill headers and component lines. This generator builds a hierarchical SQL query that walks the structure, plus a where-used and a check for empty bills.

**Sections:**

#### How to use the generator

**Body:** Enter the organization id, the top assembly item number and, for where-used, a component item number. Copy the SQL into your tool. The explosion block indents each level, orders siblings by operation sequence and limits depth so a loop in the data cannot run away. The bills with no components block is useful before a go-live data audit.

#### How the BOM tables join

**Body:** BOM_STRUCTURES_B holds one header per assembly, organization and alternate designator, with a BILL_SEQUENCE_ID. BOM_COMPONENTS_B holds the component lines for that sequence. To recurse, the component item of one line must be the assembly item of another header, which is exactly what the CONNECT BY clause expresses. A NULL alternate designator means the primary bill.

**Bullets:**

- Header key: BILL_SEQUENCE_ID
- Recursion: PRIOR component_item_id = assembly_item_id
- Effectivity: EFFECTIVITY_DATE and DISABLE_DATE
- Primary bill: ALTERNATE_BOM_DESIGNATOR IS NULL

#### What this query does not do

**Body:** The quantities shown are per parent and are not rolled up to the top assembly. Phantom assemblies, component yield, planning percent, option class items and revision-based effectivity are not applied. For extended quantity or cost, either multiply quantities down the tree in a wrapper query or use the Oracle explosion routines that populate the explosion tables.

#### Common mistakes

**Body:** The usual errors are leaving out the organization filter, missing the alternate designator filter and so mixing alternates with the primary, and ignoring disable dates, which returns obsolete components. Also remember that a circular reference in bad data causes errors without NOCYCLE.

**Cta:** If you need BOM explosions, where-used and cost roll-ups answered in plain language from your live EBS data, we can build that read-only on your own infrastructure.

**Faq:**

#### What tables hold the bill of material in Oracle EBS R12?

**Answer:** BOM_STRUCTURES_B for bill headers and BOM_COMPONENTS_B for component lines, linked by BILL_SEQUENCE_ID. In 11i the equivalent tables are BOM_BILL_OF_MATERIALS and BOM_INVENTORY_COMPONENTS.

#### How do I get an extended quantity down the tree?

**Answer:** Use SYS_CONNECT_BY_PATH for the chain, or a recursive subquery factoring clause that multiplies component quantity by the parent quantity at each level. The simple hierarchical form here shows per-parent quantity only.

#### How do I include alternate BOMs?

**Answer:** Remove the alternate designator IS NULL filter, or set it equal to the designator you want. Be careful, because alternates are separate headers and will otherwise double count components.

#### Should I use this for MRP or costing?

**Answer:** No. MRP and Cost Management have their own explosion logic including phantoms and planning percentages. Use this query for analysis, data audits and ad hoc questions, and the supported programs for transactional results.

## Search terms

oracle ebs bom explosion sql, bom_components_b query, bom_structures_b, oracle apps multilevel bom query, oracle ebs where used sql, connect by bom oracle, bom_inventory_components query, oracle r12 bom tables

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