Search Results mtl_item_revisions_interface




Overview
Explore in AI Chat

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:

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.