Results for “bom_small_expl_temp”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

BOM.BOM_SMALL_EXPL_TEMP is a temporary working table in the Oracle Bills of Material (BOM) module of Oracle E-Business Suite, documented for releases 12.1.1 and 12.2.2. Its stated purpose is to hold temporary data for indented bills of material generated by on-line processes. When a user performs an online bill explosion, indented BOM inquiry, or cost rollup within the Oracle Forms interface, the application writes the exploded structure into this table row by row, enabling the form to scroll, sort, and navigate the flattened multilevel hierarchy without repeatedly traversing BOM_STRUCTURES_B and BOM_COMPONENTS_B.

The table is not a master data repository. It is a transient staging area scoped to a session, request, or user group, and its contents are routinely purged or overwritten between executions. It carries the standard concurrent program context columns (REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE, GROUP_ID, SESSION_ID) that identify the process responsible for each row.

The ETRM metadata classifies this object heuristically as a link in Data Vault terms. This is a reasonable modeling suggestion: the table records associations between a top-level assembly, its parent bill sequence, and its component sequence rather than describing an enduring entity, and it joins the bill structure and component structures via foreign keys.

Key Information Stored

The documented 12.2.2 schema contains 119 columns. The most significant include:

• TOP_BILL_SEQUENCE_ID, BILL_SEQUENCE_ID, TOP_ITEM_ID – the top assembly and the current parent bill node in the explosion; all are foreign keys to BOM_STRUCTURES_B.
• COMPONENT_SEQUENCE_ID – the component node for the current row, foreign key to BOM_COMPONENTS_B.
• ORGANIZATION_ID – the inventory organization in which the explosion executes.
• COMPONENT_ITEM_ID, ASSEMBLY_ITEM_ID, ITEM_NUM, DESCRIPTION – denormalized item identifiers and descriptive text for display.
• PLAN_LEVEL, SORT_ORDER, LOOP_FLAG – control column for indentation depth, display sequencing, and loop detection in circular structures.
• COMPONENT_QUANTITY, EXTENDED_QUANTITY, COMPONENT_YIELD_FACTOR, SHRINKAGE_RATE – quantity roll-up values through the hierarchy.
• EXTEND_COST_FLAG, ITEM_COST, INCLUDE_IN_ROLLUP_FLAG, BASED_ON_ROLLUP_FLAG, ACTUAL_COST_TYPE_ID – cost rollup controls and results.
• SELECT_FLAG, SELECT_QUANTITY – user selection state when the temp table backs an interactive picker.
• GROUP_ID, SESSION_ID, REQUEST_ID – session and process scoping used to isolate concurrent explosions.

No surrogate single-column primary key is documented; the table is used as a transient set. Business-key candidates correspond to the composite of (GROUP_ID, TOP_BILL_SEQUENCE_ID, BILL_SEQUENCE_ID, COMPONENT_SEQUENCE_ID) when rows must be uniquely addressed.

Common Use Cases and Queries

Typical scenarios involve diagnosing an indented explosion, verifying what an online report captured, or reproducing a multilevel rollup manually.

• Inspect the current user's explosion set:

  SELECT h.PLAN_LEVEL, h.ITEM_NUM, h.COMPONENT_QUANTITY, h.EXTENDED_QUANTITY
  FROM   BOM.BOM_SMALL_EXPL_TEMP h
  WHERE  h.SESSION_ID = :session_id
  ORDER BY h.SORT_ORDER;

• Trace a top assembly's exploded structure by joining back to master data:

  SELECT t.TOP_ITEM_ID, t.BILL_SEQUENCE_ID, t.COMPONENT_SEQUENCE_ID,
         t.PLAN_LEVEL, t.COMPONENT_QUANTITY
  FROM   BOM.BOM_SMALL_EXPL_TEMP t
  WHERE  t.TOP_BILL_SEQUENCE_ID = :bill_seq;

• Identify rows of an active cost rollup process by REQUEST_ID or PROGRAM_ID.
• Detect explosion loops via LOOP_FLAG = 'Y' when validating a bill structure.
• Reconcile summary versus detail quantities using EXTENDED_QUANTITY against COMPONENT_QUANTITY at each PLAN_LEVEL.

Because contents are transient, queries should always filter by session, group, or request identifiers. Reporting against this table alone is unreliable; join to persistent structures for durable output.

Related Objects

The foreign keys defined in the ETRM metadata anchor this table to the core BOM structures:

• BOM_STRUCTURES_B – referenced three times via TOP_BILL_SEQUENCE_ID, BILL_SEQUENCE_ID, and TOP_ITEM_ID; the parent bill and item master records.
• BOM_COMPONENTS_B – referenced via COMPONENT_SEQUENCE_ID; the component line definition.
• BOM_BILL_OF_MATERIALS – the bill header that owns the sequences exploded.
• MTL_SYSTEM_ITEMS_B – item attributes for COMPONENT_ITEM_ID, ASSEMBLY_ITEM_ID, and TOP_ITEM_ID.
• BOM_EXPLOSION_TEMP and related BOM temporary structures – parallel staging tables used by different explosion processes.
• BOM indented explosion and cost rollup concurrent programs that populate and read from this table by REQUEST_ID and GROUP_ID.