Results for “bom_bill_of_materials_archive”

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

Overview

BOM_BILL_OF_MATERIALS_ARCHIVE is a table owned by the BOM schema in Oracle E-Business Suite, documented as VALID in both 12.1.1 and 12.2.2. Its stated purpose in the ETRM metadata is "Bills of material archive" — that is, it holds historical or archived representations of bill of material headers, distinct from the live definition maintained in BOM_BILL_OF_MATERIALS. Records are keyed by the surrogate primary key BOM_BILL_OF_MATLS_ARCHIVE_PK on BILL_SEQUENCE_ID.

The heuristic Data Vault classification derived from the FK structure is standalone. This is a modelling suggestion only: the table is not part of a dependency chain and it holds a single FK reference (STRUCTURE_TYPE_ID → BOM_STRUCTURE_TYPES_B). In Data Vault terms it behaves closest to a satellite that preserves point-in-time attributes of the bill header, with BILL_SEQUENCE_ID acting as the retained business key rather than a hub hash key. With 50 documented columns in the 12.2.2 view of the schema, it is a wide, audit-bearing object rather than a narrow transactional table.

Key Information Stored

The most operationally significant columns are those that identify the assembly and re-establish the original bill context:

• BILL_SEQUENCE_ID — surrogate primary key (BOM_BILL_OF_MATLS_ARCHIVE_PK); the row identifier for the archived header.
• ASSEMBLY_ITEM_ID and COMMON_ASSEMBLY_ITEM_ID — the assembly the archived bill describes, plus its master-level equivalent.
• ORGANIZATION_ID and COMMON_ORGANIZATION_ID — the inventory organization owning the bill.
• ALTERNATE_BOM_DESIGNATOR — the alternate bill designation (for example a specific alternate routing/version).
• ASSEMBLY_TYPE — classifies the assembly, distinguishing standard and model-related header types.
• STRUCTURE_TYPE_ID — the only documented foreign key, to BOM_STRUCTURE_TYPES_B.
• EFFECTIVITY_CONTROL — indicates whether the bill is date-effective or not.
• IS_PREFERRED — marks the preferred alternate for the assembly/org combination.
• SOLURCE_BILL_SEQUENCE_ID — retained as SOURCE_BILL_SEQUENCE_ID, linking the archived row to the originating live bill.
• COMMON_BILL_SEQUENCE_ID — the master-level bill sequence identifier.
• PENDING_FROM_ECN — the engineering change notice context at archive time.
• PROJECT_ID, TASK_ID, IMPLEMENTATION_DATE, NEXT_EXPLODE_DATE — project/planning attributes carried over.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — the concurrent request and program that produced the archive row.
• PK1_VALUE … PK5_VALUE and OBJ_NAME — generic archive key fragments used to reconstruct the original object identity.
• Standard WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN — plus ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15.

Common Use Cases and Queries

Typical uses are historical BOM reporting, audit of engineering changes, and point-in-time reconstruction of a bill header after the live row has been superseded or deleted. A basic lookup chains through the source key:

• Retrieve archived headers for an assembly and org:
  SELECT bill_sequence_id, assembly_item_id, organization_id, alternate_bom_designator, source_bill_sequence_id FROM bom.bom_bill_of_materials_archive WHERE assembly_item_id = :item AND organization_id = :org;
• Identify rows produced by a specific concurrent program run:
  SELECT bill_sequence_id, request_id, program_id, program_update_date FROM bom.bom_bill_of_materials_archive WHERE request_id = :req;
• Trace an archived header back to its live counterpart using SOURCE_BILL_SEQUENCE_ID, then join to BOM_BILL_OF_MATERIALS on BILL_SEQUENCE_ID.
• Audit engineering change activity via PENDING_FROM_ECN together with IMPLEMENTATION_DATE and effectivity columns.

Related Objects

The strongest documented relationship is the single foreign key STRUCTURE_TYPE_ID → BOM_STRUCTURE_TYPES_B. Adjacent BOM objects that logically complete the picture include:

• BOM_BILL_OF_MATERIALS — the live header; joined on BILL_SEQUENCE_ID / SOURCE_BILL_SEQUENCE_ID.
• BOM_BILL_OF_MATERIALS_ARCHIVE child/components equivalents in the same archive family.
• BOM_STRUCTURE_TYPES_B — structure type lookup referenced by STRUCTURE_TYPE_ID.
• BOM_INVENTORY_COMPONENTS — component lines of the original bill.
• MTL_SYSTEM_ITEMS_B — resolves ASSEMBLY_ITEM_ID.
• MTL_PARAMETERS / ORG_ORGANIZATION_DEFINITIONS — resolves ORGANIZATION_ID.