Search Results mtl_customer_item_xrefs

Overview

The MTL_CUSTOMER_ITEM_XREFS table is a core data object within the Oracle E-Business Suite (EBS) Inventory (INV) module. It serves as a central repository for establishing and maintaining cross-reference relationships between a customer's internal part numbers and the corresponding internal inventory items within an organization's master inventory. This table is fundamental for enabling business processes where a customer uses a different item identifier than the supplier, such as in customer-specific labeling, drop shipments, consigned inventory, and advanced shipping notice (ASN) transactions. Its role is to provide the necessary mapping that allows the EBS system to correctly process transactions and reports using either the customer's item reference or the organization's internal item reference.

Key Information Stored

The table's structure is designed to enforce a unique relationship between a customer item and an internal item within a specific master organization. The primary key constraint, MTL_CUSTOMER_ITEM_XREFS_PK, is defined on three columns, highlighting their combined uniqueness and importance. The CUSTOMER_ITEM_ID column stores the unique identifier for the customer's item, linking to the MTL_CUSTOMER_ITEMS table. The INVENTORY_ITEM_ID column holds the identifier for the internal inventory item, linking to the MTL_SYSTEM_ITEMS_B table. The MASTER_ORGANIZATION_ID column specifies the master inventory organization context for the relationship, linking to the MTL_PARAMETERS table. This three-part key ensures that a single customer item can be correctly mapped to the appropriate internal item within the correct organizational structure.

Common Use Cases and Queries

A primary use case is translating item identifiers during order management and shipping processes. For instance, when processing a sales order line containing a customer part number, a query against this table retrieves the internal item ID for inventory reservation and picking. Common reporting needs include generating a cross-reference listing for a specific customer or item. Sample SQL to find the internal item for a given customer item number in a master organization is:

• SELECT msib.segment1 internal_item, mcix.*
• FROM mtl_customer_item_xrefs mcix,
• mtl_system_items_b msib,
• mtl_customer_items mci
• WHERE mcix.inventory_item_id = msib.inventory_item_id
• AND mcix.master_organization_id = msib.organization_id
• AND mcix.customer_item_id = mci.customer_item_id
• AND mci.customer_item_number = '&CUST_ITEM'
• AND mcix.master_organization_id = &MASTER_ORG_ID;

Conversely, to find all customer items referencing a specific internal item, the query would filter on the INVENTORY_ITEM_ID and MASTER_ORGANIZATION_ID.

Related Objects

As indicated by its foreign key constraints, MTL_CUSTOMER_ITEM_XREFS has direct dependencies on several key Inventory tables. It references MTL_CUSTOMER_ITEMS via CUSTOMER_ITEM_ID to obtain the customer's item number and descriptive details. It references MTL_SYSTEM_ITEMS_B via both INVENTORY_ITEM_ID and MASTER_ORGANIZATION_ID to link to the internal item master. Finally, it references MTL_PARAMETERS via MASTER_ORGANIZATION_ID to validate the master organization. This table is also critical for interfaces and APIs related to shipping (WSH) and order management (OE), where customer item translation is required for transaction processing.