Search Results bom_inv_comps_interface_u1

Overview

BOM.BOM_INVENTORY_COMPS_INTERFACE is an open-interface staging table in the Oracle BOM schema that temporarily holds bill of material component information before it is validated and loaded into the production BOM tables. As documented in the ETRM metadata, the table is shared by two Oracle open interfaces: the Bills of Material open interface and the Engineering Change Order open interface. Records inserted here are consumed by the respective concurrent programs, at which point the open interfaces derive column values that were not supplied, after performing the applicable validation. The table resides in the APPS_TS_INTERFACE tablespace with PCT Free 10 and holds 146 documented columns in release 12.2.2.

The table is deliberately denormalized in a user-friendly fashion: callers may supply descriptive values rather than their derived foreign keys. For example, entering ORGANIZATION_CODE causes the interface to populate the corresponding ORGANIZATION_ID; entering ASSEMBLY_ITEM_ID, ORGANIZATION_ID, and ALTERNATE_BOM_DESIGNATOR allows the interface to derive BILL_SEQUENCE_ID; and entering BILL_SEQUENCE_ID, COMPONENT_ITEM_ID, OPERATION_SEQ_NUM, and EFFECTIVITY_DATE allows it to derive COMPONENT_SEQUENCE_ID. Following the heuristic Data Vault classification supplied in the metadata, this object is best modeled as a standalone hub: it shows no parent-style foreign key dependencies and functions as an independently keyed record of interface transactions rather than as a link or satellite.

Key Information Stored

The table's surrogate primary key is TRANSACTION_ID, which is also the single documented unique index (BOM_INV_COMPS_INTERFACE_U1, NORMAL/UNIQUE on TRANSACTION_ID). Because the metadata exposes no unique functional key, this is the only documented business-key and surrogate-key candidate. Among the 146 columns, the most operationally significant are:

• TRANSACTION_ID — the unique row identifier used by the interface processes.
• PROCESS_FLAG — drives interface processing state; rows are typically selected by this flag.
• TRANSACTION_TYPE and INTERFACE_ENTITY_TYPE — distinguish the type of component transaction being applied.
• ORGANIZATION_ID and ORGANIZATION_CODE — the inventory organization, supplied in friendly or keyed form.
• ASSEMBLY_ITEM_ID and ASSEMBLY_ITEM_NUMBER — the parent assembly for which components are defined.
• COMPONENT_ITEM_ID and COMPONENT_ITEM_NUMBER — the component being added to the bill.
• BILL_SEQUENCE_ID — the derived bill header reference.
• COMPONENT_SEQUENCE_ID — the derived component line reference.
• OPERATION_SEQ_NUM — the routing operation to which the component is assigned.
• EFFECTIVITY_DATE and DISABLE_DATE — the effectivity window for the component.
• COMPONENT_QUANTITY and COMPONENT_YIELD_FACTOR — the usage quantity per assembly and its yield.
• ALTERNATE_BOM_DESIGNATOR — the alternate bill of material being addressed.
• REFERENCE_DESIGNATOR and SUBSTITUTE_COMP_ID — used to feed BOM_REFERENCE_DESIGNATORS and BOM_SUBSTITUTE_COMPONENTS.
• REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID — concurrent program context for the load.
• RETURN_STATUS — the interface outcome returned for the row.

Common Use Cases and Queries

The primary use case is bulk maintenance of bills of material: users or external systems stage component additions, changes, and deletions in this interface and then submit the Bills of Material open interface concurrent program. A parallel use case is ECO-driven component change, where the same table feeds the Engineering Change Order open interface. Typical verification queries monitor interface progress:

• SELECT TRANSACTION_ID, ORGANIZATION_CODE, ASSEMBLY_ITEM_NUMBER, COMPONENT_ITEM_NUMBER, COMPONENT_QUANTITY, RETURN_STATUS FROM BOM.BOM_INVENTORY_COMPS_INTERFACE WHERE PROCESS_FLAG = 1 AND RETURN_STATUS IS NULL;
• SELECT COUNT(*), RETURN_STATUS FROM BOM.BOM_INVENTORY_COMPS_INTERFACE WHERE REQUEST_ID = :request_id GROUP BY RETURN_STATUS;
• SELECT ASSEMBLY_ITEM_ID, ORGANIZATION_ID, ALTERNATE_BOM_DESIGNATOR, COUNT(*) FROM BOM.BOM_INVENTORY_COMPS_INTERFACE GROUP BY ASSEMBLY_ITEM_ID, ORGANIZATION_ID, ALTERNATE_BOM_DESIGNATOR;

Reporting use cases include auditing unprocessed interface rows, reconciling requested versus derived keys before load, and validating that reference designators and substitute components will be staged correctly.

Related Objects

The documented foreign key metadata identifies two explicit relationships, both from this table:

• BOM.BOM_INVENTORY_COMPS_INTERFACE.REVISED_ITEM_SEQUENCE_ID → ENG_REVISED_ITEMS — ties a staged component row to the engineering revised item record.
• BOM.BOM_INVENTORY_COMPS_INTERFACE.ITEM_CATALOG_GROUP_ID → MTL_ITEM_CATALOG_GROUPS_B — links the component to a catalog group.

The wider interface ecosystem also includes the production destination tables BOM_BILL_OF_MATERIALS and BOM_INVENTORY_COMPONENTS, the auxiliary tables BOM_REFERENCE_DESIGNATORS and BOM_SUBSTITUTE_COMPONENTS noted in the ETRM content, and the standard EBS concurrent process machinery (FND_CONCURRENT_REQUESTS) referenced through REQUEST_ID and PROGRAM_ID. The companion interface BOM_INVENTORY_COMPS_IFCE_KEY supports cross-reference of staged component rows.