Results for “mtl_customer_items_u1”

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

Overview

INV.MTL_CUSTOMER_ITEMS is the master reference table for customer item definitions in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the cross-reference between a customer's own part number or description and the internal inventory item, allowing order entry, shipping, and pricing processes to identify goods using the customer's terminology rather than the supplier's internal numbering. Critically, customer item definitions are organization independent: a single definition applies across all inventory organizations, which distinguishes this table from organization-scoped item attributes held in MTL_SYSTEM_ITEMS and related tables.

Each record can be defined at one of three levels — Customer, Address Category, or Address — providing progressively finer granularity of the customer-to-item mapping. The heuristic Data Vault classification for this object is hub, mined from its foreign-key profile. In modeling terms, MTL_CUSTOMER_ITEMS behaves as a central business entity (a customer item identity) that many transactional tables reference by surrogate key, which is consistent with its role as a domain master rather than a transactional or attribute-history record.

Key Information Stored

The table contains 43 documented columns, of which the following are the most significant:

• CUSTOMER_ITEM_ID — Surrogate primary key (MTL_CUSTOMER_ITEMS_PK), the unique identifier for the customer item record and the column referenced by nearly every dependent transactional table.
• CUSTOMER_ID — Foreign key to RA_CUSTOMERS; identifies the customer owning the item definition.
• CUSTOMER_ITEM_NUMBER — The customer's name or number for the item (VARCHAR2 50).
• ADDRESS_ID — Foreign key to RA_ADDRESSES_ALL; supports address-level definitions.
• CUSTOMER_CATEGORY_CODE — User-definable category (lookup type CUSTOMER_CATEGORY) used to store the Address Category code.
• ITEM_DEFINITION_LEVEL — Determines whether the record applies at Customer, Address Category, or Address level.
• CUSTOMER_ITEM_DESC — Descriptive text for the customer item.
• MODEL_CUSTOMER_ITEM_ID — Self-referencing foreign key linking a customer item to its model/container parent.
• INACTIVE_FLAG — Controls whether the definition is available for use.
• COMMODITY_CODE_ID — Foreign key to MTL_COMMODITY_CODES.
• MASTER_CONTAINER_ITEM_ID, CONTAINER_ITEM_ORG_ID, DETAIL_CONTAINER_ITEM_ID — Container and organization linkage columns for container-item relationships.
• MIN_FILL_PERCENTAGE, DEMAND_TOLERANCE_POSITIVE, DEMAND_TOLERANCE_NEGATIVE — Planning and tolerance attributes supporting replenishment logic.

Two unique indexes define business-key candidates: MTL_CUSTOMER_ITEMS_U1 (CUSTOMER_ID, CUSTOMER_ITEM_NUMBER, ADDRESS_ID, CUSTOMER_CATEGORY_CODE, ITEM_DEFINITION_LEVEL) enforces that a given customer item number is unique within its definition scope, while MTL_CUSTOMER_ITEMS_U2 (CUSTOMER_ITEM_ID) mirrors the primary key. Non-unique indexes _N2 through _N6 support lookups by category, address, item number, container columns, and description.

Common Use Cases and Queries

Customer item data is consumed during order capture, pick confirmation, and delivery confirmation, where the customer's item number must be resolved to an internal item. A typical resolution query joins the customer item to its cross-reference and inventory item:

• Locate a definition by customer and item number: SELECT customer_item_id, item_definition_level FROM mtl_customer_items WHERE customer_id = :p_customer AND customer_item_number = :p_number AND inactive_flag = 'N';
• Resolve order lines to customer items: join OE_ORDER_LINES_ALL to MTL_CUSTOMER_ITEMS on CUSTOMER_ITEM_ID to display the customer's part number on reports.
• Inactive definition audit: list records with INACTIVE_FLAG = 'Y' by customer to review stale cross-references.
• Container/planning analysis: query the container columns and tolerance fields for replenishment or vendor-managed inventory reporting.

Related Objects

Because the object is a hub, it is referenced widely. The most significant related objects include:

• MTL_CUSTOMER_ITEM_XREFS — references CUSTOMER_ITEM_ID; stores the cross-reference between customer items and internal items.
• OE_ORDER_LINES_ALL and OE_ORDER_LINES_HISTORY — reference ORDERED_ITEM_ID/CUSTOMER_ITEM_ID for sales order entry.
• SO_LINES_ALL, SO_LINE_DETAILS, SO_PICKING_LINES_ALL — shipping and picking references on CUSTOMER_ITEM_ID.
• WSH_DELIVERY_DETAILS — delivery confirmation reference.
• QP_LIST_LINES and QP_ARCH_LIST_LINES — pricing modifier references.
• RLM_CUST_ITEM_TERMS_ALL and RLM_CUST_ITEM_CUM_KEYS_ALL — return/release management references.
• RA_CUSTOMERS, RA_ADDRESSES_ALL, MTL_COMMODITY_CODES, and MTL_PARAMETERS — the parent entities referenced by this table's foreign keys.