Results for “mtl_item_revisions_b”

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

Overview

MTL_ITEM_REVISIONS_B is a core Inventory (INV) base table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the item revision definitions for inventory items. A revision represents a controlled engineering or manufacturing version of an item, and this table records the revision identifier, the engineering change context, effectivity and implementation dates, lifecycle state, and descriptive attributes for each revision of an item within a given organization. The _B suffix identifies it as the base (non-translated) table; user-facing revision descriptions are held in the companion translation table, MTL_ITEM_REVISIONS_TL.

The table carries a dual primary-key definition: MTL_ITEM_REVISIONS_B_PK on (INVENTORY_ITEM_ID, ORGANIZATION_ID, REVISION_ID) and a second unique constraint on (INVENTORY_ITEM_ID, ORGANIZATION_ID, REVISION). From a heuristic Data Vault perspective, the mined foreign-key structure classifies MTL_ITEM_REVISIONS_B as a hub, since it defines a natural business key for revisions that many transactional and planning satellites reference. This classification is a modeling suggestion rather than a documented Oracle construct.

Key Information Stored

The table contains 41 documented columns. The most significant are:

• INVENTORY_ITEM_ID and ORGANIZATION_ID — the composite reference to the item/master-organization pair defined in MTL_SYSTEM_ITEMS_B. Together with REVISION these form the business key (unique index MTL_ITEM_REVISIONS_B_U1).
• REVISION_ID — the internal surrogate identifier, which is itself unique (unique index MTL_ITEM_REVISIONS_B_U2) and forms part of the primary key.
• REVISION and REVISION_LABEL — the short revision value and its display label.
• CHANGE_NOTICE — the engineering change order reference, a foreign key to ENG_ENGINEERING_CHANGES.
• ECN_INITIATION_DATE, IMPLEMENTATION_DATE, and EFFECTIVITY_DATE — the engineering change and effectivity timing for the revision.
• IMPLEMENTED_SERIAL_NUMBER — the serial number at which the revision took effect.
• REVISED_ITEM_SEQUENCE_ID — a foreign key to ENG_REVISED_ITEMS, linking the revision to its engineering revised-item record.
• REVISION_REASON, LIFECYCLE_ID, and CURRENT_PHASE_ID — the reason for the revision and its product lifecycle/phase state.
• DESCRIPTION, ATTRIBUTE1–ATTRIBUTE15, and ATTRIBUTE_CATEGORY — descriptive and flexfield storage.
• Standard WHO columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN), concurrency column OBJECT_VERSION_NUMBER, and concurrent request columns (REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE).

Common Use Cases and Queries

MTL_ITEM_REVISIONS_B is consulted wherever a transaction, reservation, demand, or on-hand record must be validated against a valid item revision. Typical scenarios include revision validation during material transactions, revision-specific on-hand and reservation reporting, engineering change effectivity analysis, and WIP/serial number revision tracking.

A straightforward listing of revisions for an item in an organization:

• SELECT revision, revision_label, change_notice, effectivity_date
• FROM mtl_item_revisions_b
• WHERE inventory_item_id = :item_id AND organization_id = :org_id;

Joining to on-hand quantities by revision (key columns INVENTORY_ITEM_ID, ORGANIZATION_ID, REVISION):

• SELECT r.revision, o.primary_transaction_quantity
• FROM mtl_item_revisions_b r, mtl_onhand_quantities o
• WHERE r.inventory_item_id = o.inventory_item_id
• AND r.organization_id = o.organization_id
• AND r.revision = o.revision;

Because REVISION_ID is a stable surrogate key, it is commonly used to join to consumer tables such as CS_CUSTOMER_PRODUCTS_ALL (MIB_REVISION_ID). Reporting on revision effectivity is frequently combined with the engineering change notice data to reconcile revisions against their originating ECOs.

Related Objects

The table is heavily referenced. The most significant relationships include:

• MTL_SYSTEM_ITEMS_B — parent item master; joined on INVENTORY_ITEM_ID and ORGANIZATION_ID.
• ENG_ENGINEERING_CHANGES — engineering change orders; joined on CHANGE_NOTICE and ORGANIZATION_ID.
• MTL_PARAMETERS — organization definition; joined on ORGANIZATION_ID.
• ENG_REVISED_ITEMS — engineering revised items; joined via REVISED_ITEM_SEQUENCE_ID.
• MTL_ITEM_REVISIONS_TL — translation table for revision descriptions, joined on INVENTORY_ITEM_ID, ORGANIZATION_ID, and REVISION.
• MTL_MATERIAL_TRANSACTIONS and MTL_ONHAND_QUANTITIES — transaction and stock records that resolve to a revision.
• MTL_RESERVATIONS and MTL_DEMAND — reservation and demand records carrying an item revision.
• WIP_DISCRETE_JOBS and WIP_FLOW_SCHEDULES — work orders referencing the primary item revision.
• CSI_ITEM_INSTANCES and CS_CUSTOMER_PRODUCTS_ALL — installed-base and customer product records that reference a specific revision.