Results for “mtl_customer_item_xrefs”

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

Overview

MTL_CUSTOMER_ITEM_XREFS is an Inventory (INV) module table in Oracle E-Business Suite 12.1.1 and 12.2.2 that stores the relationships between customer-defined item numbers and the internal inventory items to which they map. In many order-to-cash and manufacturing flows, the same physical item is referenced by a customer using a number, description, or identifier that differs from the organization's internal item definition. This table provides the cross-reference that allows Oracle Order Management, shipping, and receiving processes to resolve an incoming customer item reference to the correct internal inventory item, and conversely to translate an internal item into the identifier expected by a specific customer.

From a Data Vault modeling perspective, the metadata classifies this object as a link. This is a modeling suggestion rather than a physical constraint of the EBS schema: the table records an association between two business entities — a customer item and an inventory item — within a particular master organization, with the relationship itself carrying descriptive attributes such as preference ordering and status. It is not a standalone hub (it does not define either entity) and it is not purely a satellite of one parent, since it depends on the intersection of multiple keys.

Key Information Stored

The table is owned by the INV schema and contains 30 documented columns. The most significant columns are:

• CUSTOMER_ITEM_ID — Identifies the customer item definition in MTL_CUSTOMER_ITEMS. Part of the composite primary key.
• INVENTORY_ITEM_ID — Identifies the internal inventory item in MTL_SYSTEM_ITEMS_B. Part of the composite primary key.
• MASTER_ORGANIZATION_ID — The master organization context, referencing MTL_PARAMETERS. Part of the composite primary key.
• PREFERENCE_NUMBER — Establishes ordering among multiple cross-references for the same customer item, so that a preferred mapping can be selected when more than one internal item corresponds to a customer reference.
• INACTIVE_FLAG — Indicates whether the cross-reference is inactive and should be excluded from resolution logic.
• Attribute columns (ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15) — The standard Oracle flexfield columns available for customer-specific extensions.
• Who columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) — Audit information for the record.
• Concurrent program columns (REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE) — Identify the concurrent request and program that last updated the row.

The surrogate primary key is MTL_CUSTOMER_ITEM_XREFS_PK, defined on (CUSTOMER_ITEM_ID, INVENTORY_ITEM_ID, MASTER_ORGANIZATION_ID). Two unique indexes act as business-key candidates: MTL_CUSTOMER_ITEM_XREFS_U1 on the same three columns, and MTL_CUSTOMER_ITEM_XREFS_U2 on (CUSTOMER_ITEM_ID, MASTER_ORGANIZATION_ID, PREFERENCE_NUMBER), which enforces uniqueness of preference ordering per customer item within an organization.

Common Use Cases and Queries

The most common requirement is resolving a customer-supplied item number to the internal item used for order entry, pricing, and fulfillment. A typical pattern joins through MTL_CUSTOMER_ITEMS to obtain the customer item number and then to MTL_SYSTEM_ITEMS_B for the internal item's attributes:

SELECT ci.customer_item_number, si.segment1 AS internal_item,
       x.preference_number, x.inactive_flag
FROM mtl_customer_item_xrefs x,
       mtl_customer_items ci,
       mtl_system_items_b si
WHERE x.customer_item_id = ci.customer_item_id
  AND x.inventory_item_id = si.inventory_item_id
  AND x.master_organization_id = si.organization_id
  AND x.master_organization_id = :org_id
  AND NVL(x.preference_number,1) = 1;

Reporting use cases include identifying customer items with no cross-reference (which cause order import failures), auditing inactive cross-references that were left in place after an item was superseded, and analyzing customers who maintain multiple cross-references to the same internal item for substitution scenarios. The REQUEST_ID and program columns are useful when tracing which concurrent interface loaded or modified the mappings.

Related Objects

• MTL_CUSTOMER_ITEMS — Parent of the customer item definition, joined on CUSTOMER_ITEM_ID.
• MTL_SYSTEM_ITEMS_B — The internal item master, joined on INVENTORY_ITEM_ID and MASTER_ORGANIZATION_ID.
• MTL_PARAMETERS — Defines the master organization, joined on MASTER_ORGANIZATION_ID.
• MTL_CUSTOMER_ITEM_RELATIONS and related customer item views — additional cross-reference structures that extend the relationship model.
• OE_ORDER_LINES_ALL — Order lines reference customer items during order import, and resolution typically flows through this table.
• MTL_SYSTEM_ITEMS_VL and item-related views — commonly used to retrieve descriptive item attributes in reporting queries that start here.

Because MTL_CUSTOMER_ITEM_XREFS is a pure relationship table, it should generally be queried in conjunction with its parent tables rather than in isolation; any modification should be performed through the supported Oracle Forms or APIs rather than by direct DML.