Search Results mtl_system_items_interface_n2

Overview

MTL_SYSTEM_ITEMS_INTERFACE is the Inventory item open interface table in the INV schema. It temporarily stores the definitions for inventory items, engineering items, and purchasing items before they are validated and loaded into the production item master. The table is owned by INV, is registered under FND Design Data as INV.MTL_SYSTEM_ITEMS_INTERFACE, and resides in the APPS_TS_INTERFACE tablespace with a status of VALID. Because it is an interface table rather than a transactional or master entity, the columns are deliberately user-friendly: they permit entry of descriptive business identifiers such as ORGANIZATION_CODE, TEMPLATE_NAME, and COPY_ORGANIZATION_CODE, which Oracle Inventory then resolves into their corresponding internal IDs after validation. Unspecified columns are defaulted during processing.

From a Data Vault modeling perspective, the heuristic classification for this object is standalone. It does not participate in a strict hub-and-link topology within the documented foreign-key structure; instead it functions as a staging or landing construct. It is best modeled as a transient staging table feeding downstream hubs (item), satellites (item attributes), and links (item-to-organization, item-to-catalog-group) in the target model rather than as a durable raw vault entity in its own right.

Key Information Stored

The table contains 437 documented columns in the 12.2.2 physical schema, reflecting the very wide attribute surface of the item master. The most functionally significant columns include:

• INTERFACE_TABLE_UNIQUE_ID — the single-column business-key candidate, enforced by the unique index MTL_SYSTEM_ITEMS_INTERFACE_U2. This is the surrogate-style unique identifier for each interface row.
• INVENTORY_ITEM_ID and ORGANIZATION_ID — the primary composite lookup columns, indexed by MTL_SYSTEM_ITEMS_INTERFACE_N1; they tie the interface record to an item and an inventory organization.
• SEGMENT1 through SEGMENT20 — the item key flexfield segments. Entering segment information is strongly recommended in preference to the item number. SEGMENT1 combined with ORGANIZATION_ID is indexed by MTL_SYSTEM_ITEMS_INTERFACE_N4.
• ITEM_NUMBER and ORGANIZATION_CODE — user-friendly identifiers indexed by MTL_SYSTEM_ITEMS_INTERFACE_N2.
• TEMPLATE_ID / TEMPLATE_NAME — specifies an item attribute template from which attributes are copied.
• COPY_ITEM_ID / COPY_ITEM_NUMBER — identifies a source item to copy attributes from.
• REVISION — when populated, causes Oracle Inventory to insert a corresponding record into MTL_ITEM_REVISIONS.
• PROCESS_FLAG, SET_PROCESS_ID, and CONFIRM_STATUS — control the interface processing lifecycle and are indexed by N3 and N5.
• TRANSACTION_ID and BUNDLE_ID — indexed by N6 and N7 respectively, supporting transaction and bundle grouping.
• SOURCE_SYSTEM_ID / SOURCE_SYSTEM_REFERENCE — record the originating system for external or third-party loads.
• ITEM_CATALOG_GROUP_ID, HAZARD_CLASS_ID, and UN_NUMBER_ID — foreign keys resolving against MTL_ITEM_CATALOG_GROUPS_B, PO_HAZARD_CLASSES_B, and PO_UN_NUMBERS_B.

Common Use Cases and Queries

Typical uses center on bulk item creation, data migration, and integration from external systems. A common operational query identifies rows awaiting processing for a given organization and process set:

• Filter unprocessed rows: SELECT * FROM mtl_system_items_interface WHERE process_flag = 1 AND set_process_id = :p_set_id AND organization_id = :p_org;
• Locate rows by organization code prior to ID resolution: SELECT interface_table_unique_id, organization_code, segment1, item_number FROM mtl_system_items_interface WHERE organization_code = :p_org_code;
• Diagnose errors using CONFIRM_STATUS and PROCESS_FLAG together with SET_PROCESS_ID and ORGANIZATION_ID, leveraging index N5.
• Reconcile external loads by SOURCE_SYSTEM_ID and SOURCE_SYSTEM_REFERENCE.

Reporting use cases include interface throughput monitoring, error-rate analysis by source system, and audit of template- or copy-driven item loads.

Related Objects

• MTL_SYSTEM_ITEMS_B / MTL_SYSTEM_ITEMS_TL — the production item master that interface rows are validated into.
• MTL_ITEM_REVISIONS — populated when REVISION is supplied on the interface row.
• MTL_ITEM_CATALOG_GROUPS_B — referenced by ITEM_CATALOG_GROUP_ID.
• PO_HAZARD_CLASSES_B — referenced by HAZARD_CLASS_ID.
• PO_UN_NUMBERS_B — referenced by UN_NUMBER_ID.
• MTL_ITEM_TEMPLATES — supplies the template referenced by TEMPLATE_ID/TEMPLATE_NAME.
• MTL_PARAMETERS and MTL_ITEM_LOCATIONS — organizational context used during validation.
• The Inventory Item Open Interface concurrent program and associated PL/SQL packages, which read, validate, and transfer rows into the item master.