Search Results mtl_customer_items

Overview

The MTL_CUSTOMER_ITEMS table is a core data object within the Oracle E-Business Suite Inventory (INV) module, specifically in releases 12.1.1 and 12.2.2. It serves as the central repository for managing customer-specific item information. This table enables a fundamental business process: mapping an organization's internal inventory item to the unique identifier, description, or specification used by a specific customer. This mapping is critical for accurate order processing, shipping, and invoicing, ensuring that the customer's part number is reflected on all transactional documents, even when it differs from the organization's internal item code.

Key Information Stored

The table stores the relationship between an internal item and a customer's item data. Its primary key is CUSTOMER_ITEM_ID. Essential columns include INVENTORY_ITEM_ID and ORGANIZATION_ID, which link to the internal item definition in MTL_SYSTEM_ITEMS_B. The CUSTOMER_ID and ADDRESS_ID columns reference the trading partner and specific ship-to location in RA_CUSTOMERS and RA_ADDRESSES_ALL, respectively. The core mapping data is held in CUSTOMER_ITEM_NUMBER and CUSTOMER_ITEM_DESCRIPTION. The table also supports complex relationships through the MODEL_CUSTOMER_ITEM_ID, which can point to another record within the same table, allowing for hierarchical or model-based customer item structures. Additional attributes like COMMODITY_CODE_ID facilitate trade compliance.

Common Use Cases and Queries

A primary use case is resolving the customer item on sales orders and shipping documents. For example, to find the customer's part number for an ordered item, a query joins OE_ORDER_LINES_ALL to MTL_CUSTOMER_ITEMS via the ORDERED_ITEM_ID (which often maps to CUSTOMER_ITEM_ID). Common reporting needs include generating a cross-reference list for a customer or validating setup before order entry.

• Sample Query: Customer Item Cross-Reference Report
  SELECT mc.customer_item_number "Customer Part",
  msib.segment1 "Internal Item",
  mc.customer_item_description,
  hca.account_number
  FROM mtl_customer_items mc,
  mtl_system_items_b msib,
  hz_cust_accounts hca
  WHERE mc.inventory_item_id = msib.inventory_item_id
  AND mc.organization_id = msib.organization_id
  AND mc.customer_id = hca.cust_account_id
  AND hca.account_number = 'CUST123';

Related Objects

MTL_CUSTOMER_ITEMS is integral to the Order Management and Inventory data model, with documented foreign key relationships to several key tables.

• Primary Source/Destination Tables: OE_ORDER_LINES_ALL references MTL_CUSTOMER_ITEMS via its ORDERED_ITEM_ID column to fetch the customer-specific item for an order line.
• Master Data References: It references RA_CUSTOMERS (CUSTOMER_ID), RA_ADDRESSES_ALL (ADDRESS_ID), MTL_COMMODITY_CODES (COMMODITY_CODE_ID), and MTL_PARAMETERS (CONTAINER_ITEM_ORG_ID) for validation and additional attributes.
• Hierarchical & Cross-Reference Data: It has a self-referential foreign key to its own MODEL_CUSTOMER_ITEM_ID. It is also the parent table for MTL_CUSTOMER_ITEM_XREFS (CUSTOMER_ITEM_ID).
• Interface Tables: Serves as a target for data import via MTL_CI_INTERFACE (MODEL_CUSTOMER_ITEM_ID) and MTL_CI_XREFS_INTERFACE (CUSTOMER_ITEM_ID).