Results for “mtl_ci_xrefs_interface”

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

Overview

MTL_CI_XREFS_INTERFACE is an Inventory (INV) module interface table that serves as the staging area for Customer Items Cross References data in Oracle E-Business Suite 12.1.1 and 12.2.2. Customer item cross references allow an organization to associate an internal inventory item with the part number, description, and ship-to context used by an external customer. This interface table accepts flat-file or programmatic input, validates it against master data, and feeds the base table MTL_CUSTOMER_ITEM_XREFS (via the Customer Items Cross References interface program) so that receiving, shipping, and order management processes can resolve the customer's item number to the correct internal item.

The table is owned by the INV schema and carries 77 documented columns. Under the heuristic Data Vault classification mined from its foreign-key structure, the object is best modeled as a link. It does not describe the intrinsic attributes of a single business entity; instead it records associations among a customer item, a master organization, a customer, and a customer address. Each row therefore represents a relationship instance being staged for validation and load.

Key Information Stored

The interface uses a composite of surrogate and business identifiers together with control columns:

• Control columns: PROCESS_FLAG, PROCESS_MODE, and LOCK_FLAG drive concurrent processing and status handling; REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE tie the row to the concurrent request that loaded it.
• Surrogate and foreign keys: CUSTOMER_ITEM_ID (FK to MTL_CUSTOMER_ITEMS), CUSTOMER_ID (FK to RA_CUSTOMERS), ADDRESS_ID (FK to RA_ADDRESSES_ALL), and MASTER_ORGANIZATION_ID (FK to MTL_PARAMETERS). These are the join keys that resolve the link to its parent entities.
• Business-key candidates: CUSTOMER_NAME / CUSTOMER_NUMBER, CUSTOMER_ITEM_NUMBER, INVENTORY_ITEM, INVENTORY_ITEM_ID, MASTER_ORGANIZATION_CODE / MASTER_ORGANIZATION_NAME, and PREFERENCE_NUMBER together identify the customer-to-item mapping the user intends to create.
• Item flexfield segments: INVENTORY_ITEM_SEGMENT1 through INVENTORY_ITEM_SEGMENT20 hold the concatenated key flexfield of the inventory item, allowing validation before the internal ITEM_ID is resolved.
• Address components: ADDRESS1–ADDRESS4, CITY, STATE, COUNTY, COUNTRY, and POSTAL_CODE capture the ship-to address for validation against RA_ADDRESSES_ALL.
• Error handling: ERROR_CODE and ERROR_EXPLANATION report row-level validation failures back to the user.
• Descriptive and audit columns: INACTIVE_FLAG, ATTRIBUTE_CATEGORY, ATTRIBUTE1–ATTRIBUTE15, plus LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, and CREATED_BY.

Common Use Cases and Queries

Typical usage includes bulk loading customer item cross references through SQL*Loader or an external system, then running the interface program to populate the base table.

• Identifying rows that failed validation: SELECT * FROM mtl_ci_xrefs_interface WHERE process_flag = 3 OR error_code IS NOT NULL;
• Auditing a specific concurrent request: SELECT request_id, customer_item_number, inventory_item, error_explanation FROM mtl_ci_xrefs_interface WHERE request_id = :req_id;
• Reconciling against the base table to find unloaded mappings by joining CUSTOMER_ITEM_ID, CUSTOMER_ID, and ADDRESS_ID to their parent tables.
• Reporting on mappings that target inactive items or addresses via INACTIVE_FLAG.

Related Objects

• MTL_CUSTOMER_ITEMS — joined on CUSTOMER_ITEM_ID; parent definition of the customer item.
• MTL_PARAMETERS — joined on MASTER_ORGANIZATION_ID; validates the operating organization.
• RA_CUSTOMERS — joined on CUSTOMER_ID; validates the customer.
• RA_ADDRESSES_ALL — joined on ADDRESS_ID; validates the ship-to address.
• MTL_CUSTOMER_ITEM_XREFS — the base table ultimately populated by the interface program.
• MTL_SYSTEM_ITEMS_B — resolves INVENTORY_ITEM_ID and the concatenated segment key.