Search Results mtl_related_items_u1

Overview

INV.MTL_RELATED_ITEMS is the item relationship master table in Oracle E-Business Suite, storing the associations that link one inventory item to another within the item master organization. It is the definitive source for both "Related" and "Substitute" item definitions used throughout the supply chain: order entry, receiving, planning, and pricing all consult these relationships. Item relationships may be used to search for related and substitute items, and items defined with a substitute relationship may be received in place of the ordered item when substitutes are permitted. All relationships are defined within the item master organization, and the RELATIONSHIP_TYPE_ID column distinguishes whether a given row represents a 'Substitute' or a 'Related' relationship. The RECIPROCAL_FLAG controls directionality; defining a relationship as reciprocal has the same effect as defining two separate relationships. Later releases extended the table with START_DATE and END_DATE for effectivity, and with SUBSTITUTION_SET, PARTIAL_FULFILLMENT_FLAG, and ALL_CUSTOMERS_FLAG for the substitute relationship type, plus the ATTR_* descriptive flexfield columns. In Data Vault terms, the heuristic classification for this table is satellite-leaning: it functions as a satellite capturing descriptive relationship attributes keyed to combinations of item and organization hubs, rather than as a pure hub or link. This classification should be treated as a modeling suggestion.

Key Information Stored

The table's four-column unique index, MTL_RELATED_ITEMS_U1, spans INVENTORY_ITEM_ID, RELATED_ITEM_ID, RELATIONSHIP_TYPE_ID, and ORGANIZATION_ID, and is the primary business-key candidate; the physical primary key MTL_RELATED_ITEMS_PK uses the same column set. There is no separate surrogate key column, so referential integrity is expressed through the natural composite key.

• INVENTORY_ITEM_ID — Identifier of the driving inventory item.
• RELATED_ITEM_ID — Identifier of the related or substitute item.
• RELATIONSHIP_TYPE_ID — Discriminates Substitute versus Related relationships and drives all filtering logic.
• ORGANIZATION_ID — Organization context; relationships are scoped to the item master organization.
• RECIPROCAL_FLAG — Marks the relationship as symmetric, equivalent to two directional definitions.
• START_DATE, END_DATE — Effectivity window controlling when the relationship is valid.
• SUBSTITUTION_SET — Groups substitutes into sets for substitution processing.
• PARTIAL_FULFILLMENT_FLAG — Governs whether partial quantity fulfillment is permitted for substitutes.
• ALL_CUSTOMERS_FLAG — Indicates whether a substitute applies across all customers.
• PLANNING_ENABLED_FLAG — Controls whether the relationship participates in planning.
• ATTR_CONTEXT and the ATTR_CHAR*, ATTR_NUM*, ATTR_DATE* columns — Descriptive flexfield segments.
• OBJECT_VERSION_NUMBER — Optimistic locking token used by the OAF framework.
• Standard Who columns (CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN) and concurrent request columns.

Common Use Cases and Queries

Typical scenarios include substitution validation during order entry and receiving, related-item cross-sell or upsell displays, and configuration reporting. The view MTL_RELATED_ITEMS_VIEW explodes reciprocal relationships, so it should be preferred whenever a complete picture of relationships is required regardless of how each was defined.

• Direct relationship lookup: SELECT * FROM mtl_related_items WHERE inventory_item_id = :item AND organization_id = :org AND TRUNC(SYSDATE) BETWEEN NVL(start_date, TRUNC(SYSDATE)) AND NVL(end_date, TRUNC(SYSDATE));
• All substitutes via the view: SELECT * FROM mtl_related_items_view WHERE relationship_type_id = :subst AND inventory_item_id = :item;
• Join to item master for descriptions: SELECT a.related_item_id, b.segment1, b.description FROM mtl_related_items a, mtl_system_items_b b WHERE a.related_item_id = b.inventory_item_id AND a.organization_id = b.organization_id;
• Reporting on substitution sets and customer scope using SUBSTITUTION_SET and ALL_CUSTOMERS_FLAG.

Related Objects

• INV.MTL_SYSTEM_ITEMS_B — both INVENTORY_ITEM_ID and RELATED_ITEM_ID reference this table.
• INV.MTL_PARAMETERS — ORGANIZATION_ID references organization parameters.
• QP_LIST_LINES — references item relationships for pricing and qualifier setup.
• INV.MTL_RELATED_ITEMS_VIEW — explodes reciprocal relationships for complete viewing.