Search Results mtl_customer_items




The MTL_CUSTOMER_ITEMS table in Oracle E-Business Suite (EBS) 12.1.1 or 12.2.2 is a critical repository for managing customer-specific item information within the Inventory and Order Management modules. This table facilitates the mapping of an organization's internal item identifiers to customer-specific item codes, enabling seamless transactions, order processing, and reporting while accommodating customer-specific nomenclature. Below is a detailed breakdown of its structure, purpose, and integration within Oracle EBS.

Purpose and Functional Overview

The MTL_CUSTOMER_ITEMS table serves as a cross-reference between an enterprise's internal item codes and the item identifiers used by customers. This is particularly useful in scenarios where customers reference the same product with different part numbers, descriptions, or units of measure (UOM). By maintaining this mapping, Oracle EBS ensures accurate order fulfillment, invoicing, and inventory tracking without requiring internal item master modifications. Key functionalities include:
  • Customer-Specific Item Identification: Stores alternate item codes, descriptions, and UOMs as defined by customers.
  • Order Management Integration: Enables sales orders and invoices to reflect customer-preferred item details.
  • Reporting Consistency: Aligns internal and external item references for compliance and analytics.

Key Columns and Relationships

The table's structure includes columns that link items (INVENTORY_ITEM_ID), customers (CUSTOMER_ID), and organizations (ORGANIZATION_ID). Notable columns:
  • CUSTOMER_ITEM_NUMBER: The customer's unique identifier for the item.
  • CUSTOMER_ITEM_DESCRIPTION: Optional customer-provided description.
  • PRIMARY_UOM_CODE: Customer-specified unit of measure.
  • START_DATE_ACTIVE/END_DATE_ACTIVE: Validity period for the mapping.
  • ATTRIBUTE_CATEGORY/ATTRIBUTE_n: Flexfields for custom attributes.
The table references MTL_SYSTEM_ITEMS_B (via INVENTORY_ITEM_ID), HZ_CUST_ACCOUNTS (via CUSTOMER_ID), and HR_ORGANIZATION_UNITS (via ORGANIZATION_ID).

Integration with Oracle EBS Modules

MTL_CUSTOMER_ITEMS integrates with multiple EBS modules:
  1. Inventory (INV): Ensures accurate item identification during stock movements and cycle counts.
  2. Order Management (OM): Auto-populates customer item details in sales orders and returns.
  3. Purchasing (PO): Supports customer-driven procurement processes.
  4. Advanced Pricing: Enables customer-specific pricing based on mapped items.

Technical Considerations

  • Indexes: Key indexes include MTL_CUSTOMER_ITEMS_U1 (unique on INVENTORY_ITEM_ID, CUSTOMER_ID, ORGANIZATION_ID) and MTL_CUSTOMER_ITEMS_N1 (on CUSTOMER_ITEM_NUMBER).
  • APIs: The INV_CUSTOMER_ITEM_PUB package provides procedures for CRUD operations.
  • Data Security: Access is controlled via Oracle's VPD (Virtual Private Database) policies in multi-org environments.

Common Use Cases

  1. Drop-Shipments: Aligns supplier and customer item codes for direct shipments.
  2. Consigned Inventory: Tracks customer-owned stock using their item identifiers.
  3. Global Trade: Mananges regional/customer-specific compliance codes.

Conclusion

The MTL_CUSTOMER_ITEMS table is a pivotal component in Oracle EBS, bridging the gap between internal inventory systems and customer-specific requirements. Its robust design supports multi-org configurations, flexfields, and seamless integration with core modules, ensuring operational efficiency and data accuracy in complex supply chain environments.