Search Results mtl_relationship_types




Overview
Explore in AI Chat

Overview

APPS.ASO_I_RELATED_ITEMS_V is a reporting and integration view in the Oracle E-Business Suite Order Management and Advanced Supply Chain (ASO) schema. It presents the relationships defined between inventory items — for example, substitute, replacement, or complementary items — enriched with descriptive attributes for both the base item and its related item. The view joins the related-items definition to the manufacturing lookup that decodes the relationship type and to the item master to retrieve descriptive and pricing attributes. It is commonly consumed by order capture, iStore, and pricing flows where the application needs to identify valid related items for a given inventory item within a specific organization.

The view exposes a denormalized, query-ready structure that resolves coded lookup values into meaningful text, so callers do not need to join MFG_LOOKUPS or MTL_SYSTEM_ITEMS_VL themselves. This makes it a convenient source for concurrent programs, BI Publisher reports, and interfaces that must display or validate item relationships.

Underlying Base Objects

The view is defined over three documented objects:

  • MTL_RELATED_ITEMS_VIEW — supplies the core relationship data: the organization, the inventory item, the related item, the relationship type identifier, and the reciprocal flag.
  • MFG_LOOKUPS — joined on lookup_type = 'MTL_RELATIONSHIP_TYPES' and lookup_code = relationship_type_id, providing the human-readable MEANING for each relationship type.
  • MTL_SYSTEM_ITEMS_VL — joined on inventory item and organization, supplying the related item's description, concatenated segments, primary unit of measure, and list price.

All three referenced objects are themselves views. The joins are strictly inner joins, so a row is returned only when a matching relationship type lookup and a matching related item exist for the organization.

Key Columns

Common Use Cases and Queries

The view is typically queried when an application or report must display valid substitutes or related items for a given item, or when validating that a chosen related item belongs to an allowed relationship type. A simple query retrieving all relationships for a specific item and organization is shown below.

SELECT relationship_type, related_item, related_item_description, uom, unit_price, reciprocal_flag
FROM apps.aso_i_related_items_v
WHERE organization_id = :org_id
AND inventory_item_id = :item_id;

To restrict results to a single relationship type, filter on the decoded meaning rather than the code, for example WHERE relationship_type = 'Substitute'. Because the view resolves the lookup and joins the item master, it is well suited to listing substitute items in an order entry screen or interface load. Note that the inner joins to MTL_SYSTEM_ITEMS_VL and MFG_LOOKUPS mean any relationship lacking a valid lookup code or a valid related item in the organization will be excluded from the result set.