Search Results mtl_item_revs_interface_n2

Overview

The INV.MTL_ITEM_REVISIONS_INTERFACE table is an Oracle Inventory open interface table that temporarily stores item revision level definitions before they are validated and loaded into the production Inventory revision tables. It sits within the interface layer of Oracle E-Business Suite and functions as a staging area whose contents are read by the item interface concurrent program, which processes both this table and MTL_SYSTEM_ITEMS_INTERFACE in a coordinated fashion. The interface exposes user-friendly columns — notably ORGANIZATION_CODE and ITEM_NUMBER — alongside the corresponding internal foreign keys such as ORGANIZATION_ID and INVENTORY_ITEM_ID, allowing data to be loaded from external systems, conversion scripts, or legacy migrations without requiring the loader to resolve internal identifiers directly. Oracle Inventory derives and validates most values at import time.

The table resides in the APPS_TS_INTERFACE tablespace with a PCT Free of 10 and is owned by the INV schema. In the documented 12.2.2 physical schema it contains 53 columns. From a Data Vault modeling perspective, the schema is classified as standalone, meaning it does not function as a durable hub, link, or satellite construct; it is a transient staging structure designed to be purged after successful import.

Key Information Stored

• INTERFACE_TABLE_UNIQUE_ID — surrogate key that underpins the unique index MTL_ITEM_REVS_INTERFACE_U1, the primary business-key candidate for row uniqueness.
• INVENTORY_ITEM_ID and ORGANIZATION_ID — the internal identifiers for the target item and organization; joined by non-unique index MTL_ITEM_REVS_INTERFACE_N2.
• ITEM_NUMBER and ORGANIZATION_CODE — user-facing concatenated item segments and organization code, converted to the internal IDs above during load.
• REVISION and REVISION_ID — the revision code (for example, "A") and its resolved internal identifier.
• TRANSACTION_ID, TRANSACTION_TYPE — control columns identifying the interface row and its intended operation; indexed by MTL_ITEM_REVS_INTERFACE_N1.
• PROCESS_FLAG and SET_PROCESS_ID — processing status and grouping, used by concurrent programs to select unprocessed rows.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — concurrent request tracking columns.
• REVISED_ITEM_SEQUENCE_ID — foreign key linking to ENG_REVISED_ITEMS in the engineering schema.
• SOURCE_SYSTEM_ID and SOURCE_SYSTEM_REFERENCE — external system traceability, indexed by MTL_ITEM_REVS_INTERFACE_N5.
• DESCRIPTION, REVISION_REASON, LIFECYCLE_ID, CURRENT_PHASE_ID — supplementary revision attributes.
• Standard Who columns (LAST_UPDATE_DATE, CREATED_BY, and companions) plus ATTRIBUTE1–ATTRIBUTE15 for descriptive flexibility.

Common Use Cases and Queries

Typical usage includes bulk loading item revisions during legacy data conversion, integrating an external PLM or manufacturing system, and auditing interface processing errors. Rows are staged here, then the item interface program validates them and inserts the accepted records into the base revision tables.

Identifying unprocessed rows for a given run:

SELECT interface_table_unique_id, item_number, revision, process_flag
FROM   mtl_item_revisions_interface
WHERE  set_process_id = :p_set_process_id
AND    process_flag = 1;

Reconciling staged revisions against loaded items:

SELECT i.item_number, i.organization_code, i.revision, msi.description
FROM   mtl_item_revisions_interface i,
       mtl_system_items_b msi
WHERE  i.inventory_item_id = msi.inventory_item_id
AND    i.organization_id  = msi.organization_id;

Error reconciliation, source-system traceability lookups, and pre-load duplicate detection using INTERFACE_TABLE_UNIQUE_ID are also common reporting patterns.

Related Objects

• INV.MTL_SYSTEM_ITEMS_INTERFACE — companion interface table processed in the same interface run.
• INV.MTL_ITEM_REVISIONS_B / _TL — destination base revision tables populated by the import.
• ENG.ENG_REVISED_ITEMS — referenced by the REVISED_ITEM_SEQUENCE_ID foreign key.
• INV.MTL_SYSTEM_ITEMS_B — joins via INVENTORY_ITEM_ID and ORGANIZATION_ID.
• INV.MTL_PARAMETERS / ORG_ORGANIZATION_DEFINITIONS — resolve ORGANIZATION_CODE to ORGANIZATION_ID.
• Item interface concurrent programs and their request records (FND_CONCURRENT_REQUESTS) — drive staging consumption.