Search Results mtl_related_items_view




Overview
Explore in AI Chat

Overview

MTL_RELATED_ITEMS_VIEW is a reporting view owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2, delivered as part of the INV (Inventory) product family. Its documented purpose is to present the contents of the MTL_RELATED_ITEMS base table while ensuring that reciprocal item relationships appear twice — once in each direction.

In Oracle Inventory, an item relationship record links two inventory items through a relationship type (for example, substitute, cross-sell, or supersession relationships defined in MTL_RELATIONSHIP_TYPES). When the RECIPROCAL_FLAG on a row equals 'Y', the relationship is bidirectional: item A relates to item B and item B relates to item A. The base table stores only one physical row for such a pair. The view compensates for this asymmetry by emitting the stored row and a mirrored row with INVENTORY_ITEM_ID and RELATED_ITEM_ID swapped, so that consumers of the view see the relationship from both perspectives without writing additional UNION logic themselves. Non-reciprocal relationships (RECIPROCAL_FLAG = 'N' or NULL) appear only once. The view is read-only and serves reporting, integration, and downstream inquiry purposes rather than transactional data entry.

Underlying Base Objects

The view is defined over a single documented base object: MTL_RELATED_ITEMS, exposed to APPS through the MTL_RELATED_ITEMS synonym. The definition is a UNION ALL of two SELECT statements. The first SELECT projects all columns from MTL_RELATED_ITEMS, capturing every relationship row exactly as stored. The second SELECT projects the same columns but exchanges INVENTORY_ITEM_ID and RELATED_ITEM_ID, and is filtered by RECIPROCAL_FLAG = 'Y'. The UNION ALL preserves duplicates by design — a reciprocal relationship intentionally yields two result rows. Because the second branch re-reads the same base table with a filter, the effective row count of the view equals the count of all base rows plus the count of reciprocal base rows. The view exposes the Oracle pseudo-column ROWID as ROW_ID, but this identifier is not unique across the two branches for reciprocal relationships, since both mirrored rows originate from the same physical base row.

Key Columns

  • ROW_ID — The ROWID of the underlying MTL_RELATED_ITEMS row. Because of the UNION ALL, a reciprocal relationship returns the same ROW_ID twice; it must not be treated as a unique key of the view.
  • INVENTORY_ITEM_ID — The primary item in the relationship, as seen from the current perspective. For mirrored rows this is the RELATED_ITEM_ID of the stored record.
  • RELATED_ITEM_ID — The counterpart item in the relationship, expressed relative to INVENTORY_ITEM_ID.
  • RELATIONSHIP_TYPE_ID — Foreign key to the relationship type definition that classifies the link between the two items.
  • RECIPROCAL_FLAG — Indicates whether the relationship is bidirectional; only rows flagged 'Y' are mirrored by the view.
  • ORGANIZATION_ID — The inventory organization in which the relationship is defined, supporting multi-organization filtering.
  • START_DATE / END_DATE — Effective date range of the relationship.
  • PLANNING_ENABLED_FLAG — Indicates whether the relationship participates in planning activities.

Common Use Cases and Queries

The view is typically used when a report or interface must enumerate relationships from a given item's perspective without manually handling reciprocity. A representative query retrieving all relationships for a specific item within an organization is:

SELECT related_item_id, relationship_type_id, reciprocal_flag, start_date, end_date FROM apps.mtl_related_items_view WHERE inventory_item_id = :item_id AND organization_id = :org_id AND (start_date IS NULL OR start_date <= SYSDATE) AND (end_date IS NULL OR end_date >= SYSDATE);

Because reciprocal relationships are duplicated, users should apply DISTINCT or aggregate functions when counting unique relationships, and should join to MTL_SYSTEM_ITEMS_B via INVENTORY_ITEM_ID and RELATED_ITEM_ID to resolve item names. The view must not be used for updates, as it is a non-updatable UNION ALL construct.