Search Results oe_related_items_mv




Overview
Explore in AI Chat

Overview

OE_RELATED_ITEMS_MV is a read-only view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is part of the ONT (Order Management) product family and exposes item relationship data — substitutes, related items, and cross-sell or up-sell associations — in a form suitable for order entry, pricing, and integration reporting. The view consolidates relationship definitions, item descriptive attributes, unit of measure, and list price into a single flat structure keyed by organization and inventory item. Because it joins to master organization parameters and joins MTL_SYSTEM_ITEMS_B to the current organization, it returns item relationships only for organizations that are enabled and mapped to the master organization, which keeps its output consistent with the operating units where Order Management transactions are processed. In practice the view serves as a reporting and validation surface for downstream logic that must resolve an ordered item to its valid substitutes or related items without re-implementing the relationship and lookup join logic.

Underlying Base Objects

The view is defined over six documented objects:

  • MTL_RELATED_ITEMS_VIEW — supplies the core relationship rows (inventory item, related item, relationship type, reciprocal flag).
  • OE_ITEMS_ORD_MV — a materialized view supplying item identifier, description, and organization context; the join enforces ITEM_IDENTIFIER_TYPE = 'INT'.
  • MTL_SYSTEM_ITEMS_B — referenced as a synonym; provides primary UOM, list price, customer-order-enabled flag, and BOM item type.
  • MTL_PARAMETERS — a synonym providing the master organization relationship used to align REL.ORGANIZATION_ID with MP.MASTER_ORGANIZATION_ID.
  • MFG_LOOKUPS — resolves RELATIONSHIP_TYPE_ID against the MTL_RELATIONSHIP_TYPES lookup type, filtering on enabled flag, effective dates, and exclusion of lookup code 14.
  • OE_ITORD_UTIL — the Order Management utility package documented as a referenced object, typically used for item validation and attribute derivation.

Joins are driven from MTL_RELATED_ITEMS_VIEW through the lookup table, OE_ITEMS_ORD_MV, MTL_SYSTEM_ITEMS_B, and finally MTL_PARAMETERS, so an item that lacks a current organization or master organization mapping will not appear.

Key Columns

Common Use Cases and Queries

Typical scenarios include substitute item validation during order capture, related-item cross-sell reporting, and data-quality audits reconciling relationship definitions with item setup. Because the user search referenced mtl_system_items_b, the view is frequently used as a pre-joined alternative to querying item master plus relationship tables separately.

List substitutes for a given item in an organization:

  • SELECT related_item, related_item_description, substitution_type, unit_price FROM oe_related_items_mv WHERE organization_id = :org_id AND inventory_item_id = :item_id AND substitution_type = 'R';

Audit relationships for orderable items only:

  • SELECT inventory_item_id, related_item, relationship_type, uom FROM oe_related_items_mv WHERE organization_id = :org_id AND customer_order_enabled_flag = 'Y' ORDER BY inventory_item_id, related_item;

Join back to MTL_SYSTEM_ITEMS_B for additional attributes such as item status or planning flags when the view's exposed columns are insufficient.