Results for “mtl_customer_items”

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

Overview

MTL_CUSTOMER_ITEMS is an Inventory (INV) module table that stores customer item information — the cross-reference between a customer's own part or product numbering and Oracle EBS inventory and order management entities. The table is owned by the INV schema, holds 43 columns, and is documented as valid in ETRM 12.2.2. It functions as the master repository for customer item definitions used in order entry, shipping, receiving, and demand planning processes where the customer identifies goods by their own item number rather than the internal Oracle item number.

From a Data Vault modeling perspective, the heuristic classification for this table is hub, derived from its foreign key structure. This suggests MTL_CUSTOMER_ITEMS should be modeled as a central business entity keyed by a surrogate identifier, with descriptive attributes and relationships distributed to satellite and link structures. The table acts as the anchor point for customer item identity across multiple downstream transactional and interface tables.

Key Information Stored

MTL_CUSTOMER_ITEMS is structured around a surrogate primary key, CUSTOMER_ITEM_ID, which uniquely identifies each customer item record and is enforced by the MTL_CUSTOMER_ITEMS_PK constraint. Business-key candidates are defined by two unique indexes: MTL_CUSTOMER_ITEMS_U1 on (CUSTOMER_ID, CUSTOMER_ITEM_NUMBER, ADDRESS_ID, CUSTOMER_CATEGORY_CODE, ITEM_DEFINITION_LEVEL) and MTL_CUSTOMER_ITEMS_U2 on CUSTOMER_ITEM_ID alone.

The most significant columns include:

• CUSTOMER_ITEM_ID — surrogate primary key and the join target for most dependent tables.
• CUSTOMER_ID — foreign key to RA_CUSTOMERS, identifying the owning customer.
• CUSTOMER_ITEM_NUMBER — the customer's own part or catalog number.
• CUSTOMER_ITEM_DESC — descriptive text for the customer item.
• ADDRESS_ID — foreign key to RA_ADDRESSES_ALL, allowing ship-to specific item definitions.
• CUSTOMER_CATEGORY_CODE — classification of the customer item.
• ITEM_DEFINITION_LEVEL — defines the level at which the item is identified.
• MODEL_CUSTOMER_ITEM_ID — self-referencing foreign key to MTL_CUSTOMER_ITEMS for model/component hierarchies.
• COMMODITY_CODE_ID — foreign key to MTL_COMMODITY_CODES for trade and shipping classification.
• CONTAINER_ITEM_ORG_ID — foreign key to MTL_PARAMETERS for container item organization context.
• MASTER_CONTAINER_ITEM_ID / DETAIL_CONTAINER_ITEM_ID / MIN_FILL_PERCENTAGE — container and fill-related attributes.
• DEMAND_TOLERANCE_POSITIVE / DEMAND_TOLERANCE_NEGATIVE — tolerances applied in demand planning.
• DEP_PLAN_REQUIRED_FLAG / DEP_PLAN_PRIOR_BLD_FLAG — flags governing deployment planning behavior.
• INACTIVE_FLAG — controls whether the customer item is active.
• ATTRIBUTE_CATEGORY and ATTRIBUTE1 through ATTRIBUTE15 — the standard Oracle descriptive flexfield columns.

Common Use Cases and Queries

Typical use cases include resolving a customer's part number to the internal customer item record during order import, validating ship-to specific item definitions, and reporting on customer item coverage for shipping and receiving interfaces. A common query pattern joins to RA_CUSTOMERS to return the customer name alongside the customer item number:

• Lookup by customer and item number: SELECT CUSTOMER_ITEM_ID, CUSTOMER_ITEM_DESC FROM MTL_CUSTOMER_ITEMS WHERE CUSTOMER_ID = :p_customer_id AND CUSTOMER_ITEM_NUMBER = :p_item_number;
• Order line resolution: SELECT ci.CUSTOMER_ITEM_NUMBER FROM MTL_CUSTOMER_ITEMS ci, OE_ORDER_LINES_ALL ol WHERE ol.ORDERED_ITEM_ID = ci.CUSTOMER_ITEM_ID AND ol.ORDERED_ITEM_ID IS NOT NULL;
• Active item reporting: filter on INACTIVE_FLAG = 'N' to exclude deactivated records.
• Interface validation: join to MTL_CI_INTERFACE or MTL_CI_XREFS_INTERFACE to reconcile inbound cross-reference records.

Related Objects

MTL_CUSTOMER_ITEMS is heavily referenced across order management, shipping, receiving, and planning modules. The most significant related objects include:

• RA_CUSTOMERS — joined on MTL_CUSTOMER_ITEMS.CUSTOMER_ID for customer identity.
• RA_ADDRESSES_ALL — joined on MTL_CUSTOMER_ITEMS.ADDRESS_ID for ship-to context.
• MTL_CUSTOMER_ITEM_XREFS — joined on CUSTOMER_ITEM_ID; stores internal-to-customer item cross-references.
• OE_ORDER_LINES_ALL — joined via ORDERED_ITEM_ID; order entry captures customer item identifiers.
• WSH_DELIVERY_DETAILS — joined on CUSTOMER_ITEM_ID for shipping and delivery detail.
• SO_LINES_ALL and SO_LINE_DETAILS — joined on CUSTOMER_ITEM_ID for order line detail.
• QP_LIST_LINES — joined on CUSTOMER_ITEM_ID for pricing and modifier list entries.
• MTL_CI_INTERFACE / MTL_CI_XREFS_INTERFACE — inbound interface tables referencing CUSTOMER_ITEM_ID and MODEL_CUSTOMER_ITEM_ID.
• MTL_COMMODITY_CODES and MTL_PARAMETERS — parent lookups for commodity and container organization.

The self-referencing MODEL_CUSTOMER_ITEM_ID relationship supports hierarchical configuration and model/component modeling of customer items.