Search Results mtl_system_items_interface

Overview

MTL_SYSTEM_ITEMS_INTERFACE is the Inventory Items Open Interface table in Oracle E-Business Suite, owned by the INV (Inventory) schema. It serves as the staging and validation surface through which item master records are loaded into Oracle Inventory, either from external systems, legacy conversions, or third-party applications. Records inserted into this table are processed by the Item Open Interface concurrent program, which validates each row, may route it through the Item Interface Errors and correction workflow, and ultimately inserts the accepted definitions into MTL_SYSTEM_ITEMS_B.

The table is wide, with 437 documented columns in the 12.2.2 physical schema, reflecting the full breadth of item attribute groups: inventory, purchasing, receiving, planning (MRP), bills of material, costing, service, quality, lot and serial control, process manufacturing, and global trade. It is not a transactional table; it is a transient staging structure whose rows are consumed by the import process and generally purged after processing.

Under the heuristic Data Vault classification derived from the FK structure, this object resolves as standalone, meaning it shows no strong parent-child dependency pattern and behaves as an independent staging entity. This is a modeling suggestion rather than a physical constraint — in practice, the table holds descriptive item attribute payloads that would map to a satellite-style construct around an item business key.

Key Information Stored

Because the column list is extensive, the following are the columns of greatest structural and functional importance.

• INTERFACE_TABLE_UNIQUE_ID — documented as the unique index candidate (MTL_SYSTEM_ITEMS_INTERFACE_U2), making it the business-key candidate used to identify each staging row uniquely. This is the primary handle for correlating interface rows with error records and with the resulting MTL_SYSTEM_ITEMS_B records.
• INVENTORY_ITEM_ID / ORGANIZATION_ID — the target item identity and destination organization. Together these link the staged record to the item definition being created or updated.
• SEGMENT1 through SEGMENT20 — the concatenated key flexfield segments that form the item number as configured for the item key flexfield.
• DESCRIPTION / LONG_DESCRIPTION — the short and extended item descriptions.
• PRIMARY_UOM_CODE / PRIMARY_UNIT_OF_MEASURE — the primary stocking unit of measure, mandatory for item creation.
• ITEM_TYPE / BOM_ITEM_TYPE — classification of the item (standard, model, option class, kit, planning bill, phantom), which drives downstream BOM and planning behavior.
• INVENTORY_ITEM_FLAG, PURCHASING_ITEM_FLAG, CUSTOMER_ORDER_FLAG, SERVICE_ITEM_FLAG, ENG_ITEM_FLAG — the "item usage" flags that determine which modules treat the item as in scope.
• INVENTORY_ITEM_STATUS_CODE / ENABLED_FLAG — lifecycle and activation state controlling whether the item is transactable.
• ITEM_CATALOG_GROUP_ID — the item catalog group, with a documented FK to MTL_ITEM_CATALOG_GROUPS_B.
• HAZARD_CLASS_ID — the hazardous material class, with a documented FK to PO_HAZARD_CLASSES_B.
• UN_NUMBER_ID — the United Nations hazardous goods identifier, with a documented FK to PO_UN_NUMBERS_B.
• PROCESS_FLAG, SET_PROCESS_ID, TRANSACTION_TYPE — control columns indicating the intended processing action (insert or update) and the batch grouping for the concurrent program.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — the standard concurrent program audit trail identifying the import run that touched the row.

Common Use Cases and Queries

The table is most often used to stage bulk item loads, to diagnose failures from a prior import run, and to audit what the interface received versus what was finally created.

• Monitoring an import batch: query rows by PROCESS_FLAG and SET_PROCESS_ID to see how many are pending, errored, or complete for a given run.
• Correlating interface rows to errors: join INTERFACE_TABLE_UNIQUE_ID to MTL_INTERFACE_ERRORS.INTERFACE_TABLE_UNIQUE_ID to retrieve validation failure messages for each staging row.
• Confirming successful creation: join the interface row to MTL_SYSTEM_ITEMS_B on ORGANIZATION_ID and the reconstructed segment key to verify the final item record.
• Auditing failed purchasable attributes: filter on HAZARD_CLASS_ID or UN_NUMBER_ID to identify rows rejected because the referenced hazardous class or UN number does not exist in the PO tables.

A representative query to review errors for a batch is:

• SELECT i.INTERFACE_TABLE_UNIQUE_ID, i.SEGMENT1, i.ORGANIZATION_CODE, i.PROCESS_FLAG, e.MESSAGE_TEXT FROM MTL_SYSTEM_ITEMS_INTERFACE i, MTL_INTERFACE_ERRORS e WHERE i.INTERFACE_TABLE_UNIQUE_ID = e.INTERFACE_TABLE_UNIQUE_ID AND i.SET_PROCESS_ID = :batch_id;

Related Objects

• MTL_SYSTEM_ITEMS_B — the destination base table holding the validated item master definitions.
• MTL_INTERFACE_ERRORS — stores validation messages keyed by INTERFACE_TABLE_UNIQUE_ID.
• MTL_ITEM_CATALOG_GROUPS_B — referenced through ITEM_CATALOG_GROUP_ID.
• PO_HAZARD_CLASSES_B — referenced through HAZARD_CLASS_ID.
• PO_UN_NUMBERS_B — referenced through UN_NUMBER_ID.
• MTL_ITEM_CATEGORIES / MTL_ITEM_CATEGORIES_INTERFACE — category assignments frequently loaded alongside item interface rows.
• MTL_ITEM_REVISIONS_INTERFACE — companion interface for revision data.
• EGO_ITEM_PUB / EGO_ITEM_GRP — the public API layer that provides the supported programmatic path for item creation in place of direct interface inserts.