Results for “mtl_item_revisions_interface”

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

Overview

MTL_ITEM_REVISIONS_INTERFACE is an Inventory (INV) module interface table in Oracle EBS 12.1.1 and 12.2.2 that serves as the staging area for importing and updating item revision data into Oracle Inventory. The table allows external systems, legacy applications, or bulk data loads to submit revision records — including revision labels, effectivity dates, change notices, and lifecycle information — which are then validated and processed by the Item Revisions interface program into the base item revision tables.

From a Data Vault modeling perspective, this table is heuristically classified as standalone, meaning it does not exhibit the hub, link, or satellite characteristics typical of normalized master data. This classification is appropriate given that the table functions as a transient staging buffer rather than a persistent analytical structure. Records are typically short-lived, remaining only until the concurrent interface program processes them successfully or flags them for correction. The table's physical schema in ETRM 12.2.2 documents 53 columns under the INV schema.

Key Information Stored

The table captures the full context required to resolve an inbound revision record against the target item and organization. The most significant columns include:

• INTERFACE_TABLE_UNIQUE_ID — The surrogate primary key and the sole business-key candidate, enforced by unique index MTL_ITEM_REVS_INTERFACE_U1. Every interface row must carry a system-unique value.
• INVENTORY_ITEM_ID / ITEM_NUMBER — Identify the item being revised; the numeric ID is the internal key, while ITEM_NUMBER supports text-based loads.
• ORGANIZATION_ID / ORGANIZATION_CODE — Specify the inventory organization context for the revision.
• REVISION / REVISION_LABEL / REVISION_ID — Carry the revision identifier being introduced or updated.
• REVISION_REASON, REVISION_REASON and CHANGE_NOTICE — Document the engineering change justification.
• ECN_INITIATION_DATE, IMPLEMENTATION_DATE, EFFECTIVITY_DATE — Provide the lifecycle timing attributes that govern when the revision becomes effective.
• IMPLEMENTED_SERIAL_NUMBER — Identifies serial-numbered units affected by the revision.
• LIFECYCLE_ID, CURRENT_PHASE_ID — Link the revision to PLM lifecycle and phase definitions.
• PROCESS_FLAG, TRANSACTION_TYPE, TRANSACTION_ID, SET_PROCESS_ID — Control and track the interface processing state.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Standard concurrent program audit columns.
• REVISED_ITEM_SEQUENCE_ID — A foreign key to ENG_REVISED_ITEMS, tying the staging row to the engineering revised-item structure.
• DESCRIPTION, TEMPLATE_ID, TEMPLATE_NAME, BUNDLE_ID, SOURCE_SYSTEM_ID, SOURCE_SYSTEM_REFERENCE — Support descriptive and source-tracking metadata.
• The fifteen ATTRIBUTE flex columns (ATTRIBUTE_CATEGORY plus ATTRIBUTE1–15) provide extensibility for customer-specific data.

Common Use Cases and Queries

Typical scenarios include bulk loading new revisions for acquired product lines, migrating engineering change data from a PLM system, and automating revision creation via scheduled interfaces. A frequent validation query checks for rows that failed processing:

SELECT INTERFACE_TABLE_UNIQUE_ID, ITEM_NUMBER, REVISION, PROCESS_FLAG, TRANSACTION_TYPE FROM MTL_ITEM_REVISIONS_INTERFACE WHERE PROCESS_FLAG IS NULL OR PROCESS_FLAG != 'S';

Reporting queries often join staging rows to master item data to confirm resolution before processing:

SELECT i.ITEM_NUMBER, r.REVISION, r.EFFECTIVITY_DATE, r.CHANGE_NOTICE FROM MTL_ITEM_REVISIONS_INTERFACE r, MTL_SYSTEM_ITEMS_B i WHERE r.INVENTORY_ITEM_ID = i.INVENTORY_ITEM_ID AND r.ORGANIZATION_ID = i.ORGANIZATION_ID;

Pre-load audits also test for duplicate REVISION values per item and organization, since the interface program rejects conflicts.

Related Objects

• ENG_REVISED_ITEMS — Referenced via REVISED_ITEM_SEQUENCE_ID; the parent engineering revised-item record.
• MTL_ITEM_REVISIONS_B / MTL_ITEM_REVISIONS_TL — The base revision tables that receive validated interface rows.
• MTL_SYSTEM_ITEMS_B — Joins on INVENTORY_ITEM_ID and ORGANIZATION_ID to resolve item context.
• MTL_PARAMETERS — Provides organization defaults used during processing.
• MTL_ITEM_REVISIONS_INTERFACE processing is driven by the Item Revisions Interface concurrent program, and staged rows are typically loaded using SQL*Loader or the INV_ITEM_REVISIONS_PUB API.