Search Results planning_enabled_flag




Overview
Explore in AI Chat

Overview

APPS.MTL_RELATED_ITEMS_VIEW is a database view in the Oracle E-Business Suite Applications schema that presents item-to-item relationships maintained in Oracle Inventory. It consolidates both directional and reciprocal relationships into a single, uniformly-oriented result set, allowing a query to retrieve related items from the perspective of any given inventory item without separately interrogating the reciprocal flag. The view is defined over the MTL_RELATED_ITEMS synonym and is available in EBS 12.1.1 and 12.2.2.

In reporting and integration scenarios, the view functions as a convenience layer. Because the underlying relationship table stores each relationship once with a RECIPROCAL_FLAG indicator, consumers retrieving "what is related to item X" would otherwise need to union two differently-joined queries. MTL_RELATED_ITEMS_VIEW performs that union internally, exposing a normalized pair of INVENTORY_ITEM_ID and RELATED_ITEM_ID columns that can be joined directly to item master tables. The column name most often sought in this context, RELATED_ITEM_ID, identifies the counterpart item in each relationship row.

Underlying Base Objects

Per the documented ETRM metadata, the view references a single base object: the MTL_RELATED_ITEMS synonym (which resolves to the MTL_RELATED_ITEMS base table in the Oracle Inventory schema). The view definition is a UNION ALL of two SELECT statements over that same source:

  • The first branch selects INVENTORY_ITEM_ID and RELATED_ITEM_ID directly from MTL_RELATED_ITEMS, preserving the stored orientation.
  • The second branch swaps the two columns (RELATED_ITEM_ID AS INVENTORY_ITEM_ID and INVENTORY_ITEM_ID AS RELATED_ITEM_ID) and filters on RECIPROCAL_FLAG = 'Y', emitting the reverse orientation only for reciprocal relationships.

All other projected columns — ROWID as ROW_ID, RELATIONSHIP_TYPE_ID, RECIPROCAL_FLAG, ORGANIZATION_ID, START_DATE, END_DATE, and PLANNING_ENABLED_FLAG — are carried through unchanged in both branches. Because it relies on UNION ALL, the view returns two rows for every reciprocal relationship (one in each direction) and one row for each non-reciprocal relationship.

Key Columns

  • ROW_ID — the source ROWID of the underlying MTL_RELATED_ITEMS row; identifies the physical record.
  • INVENTORY_ITEM_ID — the item from whose perspective the relationship is presented. In rows sourced from the second UNION ALL branch, this is the stored RELATED_ITEM_ID.
  • RELATED_ITEM_ID — the counterpart item in the relationship. This is the primary column of interest for relationship lookups and joins to item master.
  • RELATIONSHIP_TYPE_ID — identifier of the relationship type (for example, substitute, related, or configured item associations) defined in the relevant setup table.
  • RECIPROCAL_FLAG — indicates whether the relationship is bidirectional ('Y') or one-directional ('N').
  • ORGANIZATION_ID — the inventory organization in which the relationship is defined.
  • START_DATE / END_DATE — the effective date range for the relationship.
  • PLANNING_ENABLED_FLAG — indicates whether the relationship is recognized by planning processes.

Common Use Cases and Queries

Typical uses include displaying related items on item inquiry forms, driving substitute-item logic in order entry or planning, and extracting item relationship data for downstream systems. A simple lookup of all related items for a given item and organization:

SELECT related_item_id, relationship_type_id, reciprocal_flag
FROM   apps.mtl_related_items_view
WHERE  inventory_item_id = :item_id
AND    organization_id  = :org_id;

Joining to the item master to obtain descriptive attributes for both sides of the relationship:

SELECT msi.segment1  inventory_item,
       msi2.segment1 related_item
FROM   apps.mtl_related_items_view v,
       apps.mtl_system_items_b  msi,
       apps.mtl_system_items_b  msi2
WHERE  v.inventory_item_id = msi.inventory_item_id
AND    v.related_item_id   = msi2.inventory_item_id
AND    v.organization_id   = msi.organization_id
AND    v.organization_id   = msi2.organization_id;

Because the view already normalizes reciprocal relationships, filter predicates on INVENTORY_ITEM_ID return all relationships in which the item participates, regardless of the stored direction. Consumers requiring date-effective filtering should additionally constrain START_DATE and END_DATE, and those interested only in planning-relevant relationships should filter PLANNING_ENABLED_FLAG = 'Y'.