Search Results mtl_customer_item_xrefs_v




Overview
Explore in AI Chat

Overview

MTL_CUSTOMER_ITEM_XREFS_V is an APPS-owned database view in the Oracle E-Business Suite Inventory (INV) module, documented with the description "10SC ONLY." It presents a denormalized, reporting-oriented representation of the customer item cross-reference relationship held in MTL_CUSTOMER_ITEM_XREFS, joined to the customer item definition in MTL_CUSTOMER_ITEMS and enriched with customer, address, item, and lookup attributes. The view answers the core business question of which internal inventory item corresponds to a given customer item number for a specific customer, master organization, and rank or preference sequence.

Its role is primarily reporting and integration rather than transactional processing. Because it resolves internal item identifiers into concatenated segment values and descriptions, and resolves customer identifiers into party and account numbers, it is well suited to interfaces, extracts, and operational reports where consumers require human-readable cross-reference data without performing the multi-table joins themselves. The ETRM metadata records the object as VALID in the APPS schema under ETRM 12.2.2, and the same view is present in 12.1.1 environments.

Underlying Base Objects

The view is defined over the MTL_CUSTOMER_ITEMS and MTL_CUSTOMER_ITEM_XREFS base tables, with supporting joins to item and party master data. The documented referenced base objects are: ARP_ADDR_PKG (PACKAGE), AR_LOOKUPS (VIEW), FND_TERRITORIES_VL (VIEW), HZ_CUST_ACCOUNTS (SYNONYM), HZ_CUST_ACCT_SITES_ALL (SYNONYM), HZ_LOCATIONS (SYNONYM), HZ_PARTIES (SYNONYM), HZ_PARTY_SITES (SYNONYM), MFG_LOOKUPS (VIEW), MTL_CUSTOMER_ITEMS (SYNONYM), MTL_CUSTOMER_ITEM_XREFS (SYNONYM), MTL_SYSTEM_ITEMS_B_KFV (VIEW), and MTL_SYSTEM_ITEMS_TL (SYNONYM).

  • MTL_CUSTOMER_ITEM_XREFS is the driving table; its ROWID is exposed as ROW_ID and it supplies the customer item, inventory item, master organization, preference rank, inactive flag, and the WHO columns.
  • MTL_CUSTOMER_ITEMS supplies the customer item number, description, customer category code, customer identifier, item definition level, and address identifier.
  • MTL_SYSTEM_ITEMS_B_KFV and MTL_SYSTEM_ITEMS_TL resolve the internal item to concatenated segments and a description.
  • HZ_PARTIES, HZ_CUST_ACCOUNTS, HZ_CUST_ACCT_SITES_ALL, HZ_PARTY_SITES, and HZ_LOCATIONS supply customer name, account number, status, and address components; ARP_ADDR_PKG.FORMAT_ADDRESS produces the concatenated address.
  • AR_LOOKUPS resolves the customer category code to a meaning, while MFG_LOOKUPS resolves the item definition level.

Key Columns

Common Use Cases and Queries

Typical scenarios include customer item cross-reference reports, order-to-inventory mapping extracts, and data validation before loading or maintaining cross-references.

  • List active cross-references for a customer, resolving the internal item.
  • Identify the preferred (lowest rank) customer item for an internal item.
  • Extract cross-references with full customer name and address for integration feeds.

Sample query — active cross-references for a customer:

SELECT customer_item_number, customer_item_desc, concatenated_segments,
  item_description, rank, customer_name, customer_number, concatenated_address
FROM apps.mtl_customer_item_xrefs_v
WHERE customer_number = :p_customer_number
  AND NVL(inactive_flag,'N') = 'N'
ORDER BY customer_item_number, rank;

Sample query — preferred cross-reference per internal item:

SELECT inventory_item_id, concatenated_segments, customer_number,
  customer_item_number, rank
FROM apps.mtl_customer_item_xrefs_v x
WHERE rank = (SELECT MIN(rank) FROM apps.mtl_customer_item_xrefs_v y
  WHERE y.inventory_item_id = x.inventory_item_id
    AND y.customer_id = x.customer_id
    AND NVL(y.inactive_flag,'N') = 'N');

As the view is documented as "10SC ONLY," consumption should be confirmed against the target release before being embedded in custom code, and the view should be treated as a read-only reporting object rather than a maintenance interface.