Search Results bom_bill_of_mtls_interface




Overview
Explore in AI Chat

Overview

BOM_BILL_OF_MTLS_INTERFACE is the Bills of Material interface (staging) table in the Oracle BOM module, owned by the BOM schema. It acts as the inbound landing area for bill of material and component records before they are validated and imported into the production BOM tables (BOM_BILL_OF_MATERIALS and BOM_INVENTORY_COMPONENTS) through the Bills of Material Interface concurrent program. Records are typically populated by external systems, legacy conversions, engineering change processes, or open interface loaders, and are then processed in batch using a request ID and process flag.

The table is a transient, high-churn structure: rows are inserted, processed, and purged or retained only for audit and error review. It carries extensive descriptive and reference columns (76 documented in ETRM 12.2.2) that mirror the structure of the production bill tables plus interface control fields (REQUEST_ID, PROCESS_FLAG, RETURN_STATUS, TRANSACTION_TYPE, TRANSACTION_ID).

ETRM FK mining classifies this object heuristically as standalone in Data Vault terms rather than a true hub, link, or satellite. That is a modeling suggestion only: the table is essentially an adjacency/transaction staging structure rather than a normalized reference entity. Its only documented foreign keys point to BOM_STRUCTURE_TYPES_B and MTL_ITEM_CATALOG_GROUPS_B, which are lookup relationships rather than business identity relationships.

Key Information Stored

Common Use Cases and Queries

Typical uses include monitoring inbound bill interfaces, diagnosing rows that failed validation, and reporting on pending or errored bills before they enter production.

  • List unprocessed interface rows for a given request:
    SELECT TRANSACTION_ID, ASSEMBLY_ITEM_ID, ORGANIZATION_ID, PROCESS_FLAG, RETURN_STATUS, TRANSACTION_TYPE FROM BOM.BOM_BILL_OF_MTLS_INTERFACE WHERE REQUEST_ID = :req AND PROCESS_FLAG <> 'PROCESSED';
  • Find errored rows by status: ... WHERE RETURN_STATUS = 'E';
  • Resolve organization and item keys to internal IDs by joining ORGANIZATION_CODE to MTL_PARAMETERS (ORGANIZATION_CODE) and ITEM_NUMBER to MTL_SYSTEM_ITEMS_B.
  • Report staged bills grouped by SOURCE_SYSTEM_REFERENCE and BATCH_ID to trace which external system or convert submitted each set.
  • Audit loaded bills against production by joining BILL_SEQUENCE_ID to BOM_BILL_OF_MATERIALS after processing.

Related Objects

  • BOM_STRUCTURE_TYPES_B — referenced via STRUCTURE_TYPE_ID; defines the type of bill/structure being loaded.
  • MTL_ITEM_CATALOG_GROUPS_B — referenced via ITEM_CATALOG_GROUP_ID; catalog grouping of the assembly item.
  • BOM_BILL_OF_MATERIALS — the target production bill header table populated after processing this interface.
  • BOM_INVENTORY_COMPONENTS — the production component table populated for bill components.
  • MTL_SYSTEM_ITEMS_B — source of ASSEMBLY_ITEM_ID / ITEM_NUMBER validation.
  • MTL_PARAMETERS — source of ORGANIZATION_ID / ORGANIZATION_CODE validation.
  • FND_CONCURRENT_REQUESTS — joined on REQUEST_ID for batch tracking.
  • Bills of Material Open Interface APIs (BOM_BILL_OF_MTLS_INTERFACE public import program) — the program consuming this table.