Search Results mtl_ci_interface




Overview
Explore in AI Chat

Overview

The MTL_CI_INTERFACE table is a core interface table within the Oracle E-Business Suite Inventory (INV) module, specifically for version 12.1.1 and 12.2.2. It serves as the staging area for importing and processing customer item data. Customer items represent products or parts that are defined and identified by a customer's part number, which are then cross-referenced to the organization's internal items. The primary role of this table is to facilitate the batch loading of customer item information from external systems or legacy data sources. Data is first inserted into this interface table, validated, and then processed by a standard Oracle interface program to populate the base transactional table, MTL_CUSTOMER_ITEMS.

Key Information Stored

The table stores a comprehensive set of attributes required to define a relationship between a customer and an item. While the full column list is extensive, key columns include identifiers for the transaction batch (TRANSACTION_ID), the customer (CUSTOMER_ID), the customer's site (ADDRESS_ID), and the internal inventory item (INVENTORY_ITEM_ID, ORGANIZATION_ID). Critical descriptive columns include the customer's part number (CUSTOMER_ITEM_NUMBER) and its description (CUSTOMER_ITEM_DESC). The table also contains control columns essential for the interface process, such as PROCESS_FLAG (indicating pending, error, or processed status), ERROR_CODE, and ERROR_EXPLANATION for troubleshooting failed records. The provided metadata confirms foreign key relationships to RA_CUSTOMERS and RA_ADDRESSES_ALL, establishing the link to Trading Community Architecture (TCA) customer and site data.

Common Use Cases and Queries

The most prevalent use case is the bulk creation or update of customer item assignments via the "Customer Items Interface" concurrent program. A typical operational query checks for records that failed processing to diagnose errors. A common reporting need is to verify staged data before submission.

  • Checking Interface Errors: SELECT transaction_id, customer_item_number, error_code, error_explanation FROM inv.mtl_ci_interface WHERE process_flag = 'E';
  • Previewing Staged Data: SELECT customer_id, address_id, inventory_item_id, customer_item_number FROM inv.mtl_ci_interface WHERE process_flag = 'P' AND transaction_id = <batch_id>;
  • Validating Customer/Site Reference: Joins to RA_CUSTOMERS and RA_ADDRESSES_ALL are often used in validation scripts to ensure foreign key integrity before running the interface.

Related Objects

MTL_CI_INTERFACE is centrally connected to several key Inventory and Order Management objects. As per the provided metadata, it has direct foreign key dependencies on RA_CUSTOMERS and RA_ADDRESSES_ALL for customer and address validation. It also references MTL_PARAMETERS, MTL_COMMODITY_CODES, and MTL_CUSTOMER_ITEMS. The primary destination for successfully processed interface records is the base table MTL_CUSTOMER_ITEMS. The standard API for programmatic creation of customer items, INV_CUSTOMER_ITEM_PUB, internally manages data flow to and from this interface. The concurrent program "Customer Items Interface" (INVCIICP) is the engine that processes rows from this table.