Search Results revision_qty_control_code




Overview
Explore in AI Chat

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

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.

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.