Search Results mtl_related_items_pk

Overview

MTL_RELATED_ITEMS is an Inventory (INV) module table that stores user-defined relationships between inventory items. It answers the business question of which items are connected to one another — for example, substitute components, complementary products, or items commonly ordered together — and captures that association along with the directionality and validity of the link. In Oracle E-Business Suite 12.1.1 and 12.2.2 the table serves as the authoritative source of item relationship definitions consumed by pricing, order management, and planning functionality.

Under the Data Vault classification heuristic derived from its foreign-key structure, MTL_RELATED_ITEMS exhibits satellite-leaning characteristics: it carries descriptive and effective-dating attributes ({START_DATE}, {END_DATE}, attributes, {PLANNING_ENABLED_FLAG}) surrounding a four-part composite key rather than acting as an independent hub. It can also be read as a link table because it connects two item instances within the same organization. Both interpretations are modeling suggestions, not mandates; the physical design is a conventional EBS intersection table with audit and descriptive columns.

Key Information Stored

The table's documented physical schema contains 49 columns. The most operationally significant are:

• INVENTORY_ITEM_ID — the primary item in the relationship, referencing MTL_SYSTEM_ITEMS_B.
• RELATED_ITEM_ID — the counterpart item, also referencing MTL_SYSTEM_ITEMS_B.
• RELATIONSHIP_TYPE_ID — the relationship code (substitute, complementary, etc.), which determines the semantics of the link.
• ORGANIZATION_ID — the inventory organization in which the relationship is defined, referencing MTL_PARAMETERS. Relationship data is organization-scoped in EBS.
• RECIPROCAL_FLAG — indicates whether the relationship is implicitly symmetric (A→B also implies B→A).
• PLANNING_ENABLED_FLAG — controls whether the relationship is honored by planning processes such as MRP.
• START_DATE / END_DATE — effective dating that governs when the relationship is active.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the 12.2.2 OAF-based UI.
• Audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID.
• ATTR_CONTEXT and ATTR_CHAR1–10 / ATTR_NUM1–10 / ATTR_DATE1–10 — the standard EBS DFF/attribute set available for customer extensions.

The surrogate primary key is MTL_RELATED_ITEMS_PK, composed of INVENTORY_ITEM_ID, RELATED_ITEM_ID, RELATIONSHIP_TYPE_ID, and ORGANIZATION_ID. A unique index, MTL_RELATED_ITEMS_U1, is documented over the same four columns, confirming that this composite is the business-key candidate and that no duplicate relationship can exist within an organization. All four key columns are mandatory within the key, so queries should always filter by ORGANIZATION_ID to avoid cross-organization contamination.

Common Use Cases and Queries

Typical scenarios include substitute-item sourcing during order entry, complementary-item suggestions in Configurator and iStore, planning associations for MRP, and pricing setups that leverage related-item structures. A basic lookup retrieves all relationships for a given item within an organization:

• SELECT related_item_id, relationship_type_id, reciprocal_flag, start_date, end_date FROM mtl_related_items WHERE inventory_item_id = :item AND organization_id = :org AND TRUNC(SYSDATE) BETWEEN NVL(start_date, SYSDATE) AND NVL(end_date, SYSDATE);
• To resolve item names, join twice to MTL_SYSTEM_ITEMS_B, once on INVENTORY_ITEM_ID and once on RELATED_ITEM_ID, with ORGANIZATION_ID in both join conditions.
• Reporting by relationship type uses a GROUP BY on RELATIONSHIP_TYPE_ID, filtering on PLANNING_ENABLED_FLAG for planning-oriented extracts.

Related Objects

• MTL_SYSTEM_ITEMS_B — parent of both INVENTORY_ITEM_ID and RELATED_ITEM_ID; join on both item columns plus ORGANIZATION_ID.
• MTL_PARAMETERS — parent of ORGANIZATION_ID; defines the inventory organization context.
• QP_LIST_LINES — the Advance Pricing list line table carries the same four-column combination (INVENTORY_ITEM_ID, RELATED_ITEM_ID, RELATIONSHIP_TYPE_ID, ORGANIZATION_ID), so it functionally depends on MTL_RELATED_ITEMS when pricing relationships are qualified.
• Relationship type definitions — the descriptive meaning of RELATIONSHIP_TYPE_ID is stored in the inventory relationship-type setup, and must be joined to translate the code into a user-visible label.