Results for “mtl_ci_interface”

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

Overview

MTL_CI_INTERFACE is a standard Oracle Inventory (INV) interface table that serves as the staging and processing area for customer item master data in Oracle E-Business Suite 12.1.1 and 12.2.2. In the Oracle ETRM metadata, this table is owned by the INV schema and is documented as "Customer Items Interface." Customer items are the buyer- or seller-specific item identifiers, container relationships, commodity codes, and packing attributes exchanged between trading partners in supply chain and order-management flows. Loading this interface table allows bulk creation or update of customer item definitions in MTL_CUSTOMER_ITEMS and their associated container detail records.

The interface design follows the conventional Oracle EBS pattern of a concurrent-program-driven staging table: rows are inserted with a PROCESS_FLAG indicating their processing status, then read and validated by the Customer Items interface program, which resolves foreign key references and writes valid rows to the destination tables. Error rows are retained with populated ERROR_CODE and ERROR_EXPLANATION values for correction and resubmission. From a data-vault modeling perspective, the metadata's relationship structure suggests a link classification, as the table primarily records associations between customer, address, commodity, organization, and customer-item entities rather than acting as an independent hub or a purely descriptive satellite. The table has 107 documented columns in the 12.2.2 physical schema.

Key Information Stored

The interface row is identified by no separate surrogate numeric key; the functional identity is derived from the combination of transaction type, customer, and customer item number. The most significant columns are:

• PROCESS_FLAG / PROCESS_MODE – Control columns indicating whether the row is pending, rejected, or processed.
• TRANSACTION_TYPE – Distinguishes the intended action (for example, create, update, or delete) for the customer item.
• CUSTOMER_ID / CUSTOMER_NAME / CUSTOMER_NUMBER – Reference to the customer in RA_CUSTOMERS.
• ADDRESS_ID – Ship-to or bill-to address reference in RA_ADDRESSES_ALL.
• CUSTOMER_ITEM_NUMBER / CUSTOMER_ITEM_DESC / ITEM_DEFINITION_LEVEL – The customer-facing item identifier and definition level.
• MODEL_CUSTOMER_ITEM_ID / MODEL_CUSTOMER_ITEM_NUMBER – Parent model customer item reference.
• COMMODITY_CODE_ID / COMMODITY_CODE – Commodity classification from MTL_COMMODITY_CODES.
• MASTER_CONTAINER / MASTER_CONTAINER_ITEM_ID and the MASTER_CONTAINER_SEGMENT1..20 columns – Key flexfield segments describing the master container item.
• DETAIL_CONTAINER / DETAIL_CONTAINER_ITEM_ID and DETAIL_CONTAINER_SEGMENT1..20 – Equivalent segments for the detail container.
• CONTAINER_ITEM_ORG_ID / CONTAINER_ITEM_ORG_NAME – Owning organization from MTL_PARAMETERS.
• MIN_FILL_PERCENTAGE, DEMAND_TOLERANCE_POSITIVE, DEMAND_TOLERANCE_NEGATIVE, DEP_PLAN_REQUIRED_FLAG, DEP_PLAN_PRIOR_BLD_FLAG, INACTIVE_FLAG – Planning and packing attributes.
• ERROR_CODE / ERROR_EXPLANATION – Validation failures returned to the submitting process.

Concurrency and audit columns (REQUEST_ID, PROGRAM_ID, LAST_UPDATED_BY, CREATION_DATE) and the ATTRIBUTE1..15 descriptive flexfield columns complete the structure.

Common Use Cases and Queries

Typical use cases include bulk customer item loads, container-relationship maintenance, and reconciliation of rejected interface rows. A common query retrieves unprocessed rows for a specific request:

SELECT customer_item_number, customer_id, transaction_type, error_code, error_explanation
FROM   mtl_ci_interface
WHERE  process_flag = 'PENDING'
AND    request_id = :p_request_id;

Error analysis aggregates validation failures by code to help correct source data before resubmission. A join to MTL_CUSTOMER_ITEMS and MTL_PARAMETERS confirms that expected destination rows exist after the interface program completes, and a query keyed on CUSTOMER_ITEM_NUMBER supports exception reporting for trading-partner onboarding.

Related Objects

The documented foreign keys anchor the interface to the following principal objects:

• RA_CUSTOMERS – joined on MTL_CI_INTERFACE.CUSTOMER_ID = RA_CUSTOMERS.CUSTOMER_ID.
• RA_ADDRESSES_ALL – joined on ADDRESS_ID.
• MTL_PARAMETERS – joined on CONTAINER_ITEM_ORG_ID.
• MTL_COMMODITY_CODES – joined on COMMODITY_CODE_ID.
• MTL_CUSTOMER_ITEMS – joined on MODEL_CUSTOMER_ITEM_ID; this is also the primary destination table written by the interface program.

In practice these are complemented by the Customer Items interface concurrent program and the related container detail tables, which together form the complete customer item import framework.