Developer ToolsFree generatorPDF, Excel, Word exportOracle E-Business Suite

Oracle EBS BOM Explosion SQL Query Generator

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.

Fill in

Organization ID

Top assembly (segment1)

Component for where-used (segment1)

Maximum levels

Generated code

Download:

1. Multilevel explosion (current effectivity, primary BOM)

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 = 207
       AND bom.assembly_item_id = (SELECT inventory_item_id
                                   FROM   mtl_system_items_b
                                   WHERE  segment1 = 'AS-1000'
                                   AND    organization_id = 207)
CONNECT BY NOCYCLE PRIOR bic.component_item_id = bom.assembly_item_id
       AND bom.organization_id = PRIOR bom.organization_id
       AND LEVEL <= 10
ORDER SIBLINGS BY bic.operation_seq_num;

2. Single-level where-used

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 = 207
AND    bom.alternate_bom_designator IS NULL
AND    msi_c.segment1 = 'RM-2040'
AND    (bic.disable_date IS NULL OR bic.disable_date > SYSDATE)
ORDER BY msi_p.segment1;

3. Bills with no components

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 = 207
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;

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.

Ask BOM questions in plain English

Optional: a walkthrough of read-only natural-language BOM and where-used queries.

Optional. The tool and every export work without this. No spam.

How to use the generator

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

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.

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

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

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.

Frequently Asked Questions

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

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools

Related Tools

Oracle

More Developer Tools tools

See all

Back to All Tools