Search Results related_code

Overview

AST_ITEM_RELATIONSHIPS_V is a TeleSales (AST) view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes the set of related items defined against a base inventory item within a specific inventory organization, enriching the raw relationship record with descriptive and pricing attributes drawn from the item master, on-hand quantities, and lookup tables. The view is a denormalized convenience layer: rather than requiring callers to join MTL_RELATED_ITEMS to MTL_SYSTEM_ITEMS_VL, MTL_ITEM_QUANTITIES_VIEW, and MFG_LOOKUPS manually, it returns a single row per related item with the columns most commonly needed by TeleSales and Order Capture screens, upsell/cross-sell logic, and integration payloads.

Its role is primarily read-only reporting and integration. TeleSales agents see related items with their description, relationship code, availability, and list price; downstream interfaces use the same columns to populate sales proposals or quotes. Because the view performs outer joins and profile lookups, it is not intended as an updateable entity — it is a query surface over the underlying item relationship model.

Underlying Base Objects

The ETRM metadata documents five referenced base objects:

• MTL_RELATED_ITEMS (SYNONYM) — the driving table (aliased REL). Supplies INVENTORY_ITEM_ID, ORGANIZATION_ID, RELATED_ITEM_ID, RELATIONSHIP_TYPE_ID, and RECIPROCAL_FLAG.
• MTL_SYSTEM_ITEMS_VL (VIEW) — the item master (aliased ITM). Joined on INVENTORY_ITEM_ID and ORGANIZATION_ID to provide RELATED_INVENTORY_NUMBER (CONCATENATED_SEGMENTS), RELATED_DESCRIPTION (DESCRIPTION), PRIMARY_UOM_CODE, PRIMARY_UNIT_OF_MEASURE, and LIST_PRICE_PER_UNIT.
• MTL_ITEM_QUANTITIES_VIEW (VIEW) — the quantity source (aliased QTY). Outer-joined on item and organization to yield QUANTITY and to derive AVAILABLE_FLAG.
• MFG_LOOKUPS (VIEW) — the lookup table (aliased LKU). Filters LOOKUP_TYPE = 'MTL_RELATIONSHIP_TYPES' and matches LOOKUP_CODE to REL.RELATIONSHIP_TYPE_ID, returning RELATED_CODE and RELATED_CODE_MEANING.
• FND_PROFILE (PACKAGE) — invoked via FND_PROFILE.VALUE('JTF_PROFILE_DEFAULT_CURRENCY') to populate LIST_PRICE_CURRENCY_CODE.

Notably, PRICE_LIST, PRICE_LIST_ID, and PRICE_LIST_LINE_ID are emitted as literal NULL placeholders, indicating the view preserves a legacy column signature but supplies no pricing-list data.

Key Columns

• INVENTORY_ITEM_ID / ORGANIZATION_ID — the base item and organization that own the relationship.
• RELATED_INVENTORY_ITEM_ID — the related item (REL.RELATED_ITEM_ID).
• RELATED_INVENTORY_NUMBER — the concatenated segment value of the related item, used as the human-readable item number.
• RELATED_DESCRIPTION — the item description, and the column most often targeted by the search term "related_description".
• RELATED_CODE / RELATED_CODE_MEANING — the relationship type lookup code and its translated meaning.
• RECIPROCAL_FLAG — indicates whether the relationship is reciprocated in the opposite direction.
• AVAILABLE_FLAG — 'Y' when the related item has a non-null, non-zero available quantity; computed as a DECODE over QUANTITY.
• QUANTITY — NVL(QTY.QUANTITY, 0), the available quantity of the related item.
• PRIMARY_UOM_CODE / PRIMARY_UNIT_OF_MEASURE, LIST_PRICE_PER_UNIT, LIST_PRICE_CURRENCY_CODE — unit and pricing attributes surfaced from the item master and profile default currency.

Common Use Cases and Queries

The view supports cross-sell/upsell proposals, catalog screens, and integrations that need related item details in one pass. A typical query retrieves all related items with descriptions and availability for a given base item and organization:

SELECT related_inventory_item_id,
       related_inventory_number,
       related_description,
       related_code_meaning,
       available_flag,
       quantity
  FROM apps.ast_item_relationships_v
 WHERE inventory_item_id = :p_item_id
   AND organization_id   = :p_org_id
 ORDER BY related_code_meaning, related_description;

To locate items by their description text, callers filter on RELATED_DESCRIPTION, for example WHERE UPPER(related_description) LIKE '%CABLE%'. Because the quantity source is outer-joined, AVAILABLE_FLAG is reliably 'N' when quantity is missing or zero, making it suitable for filtering sellable related items. Remember that the view returns NULL for the price-list columns and that the currency code is resolved at query time via the JTF_PROFILE_DEFAULT_CURRENCY profile option, so results reflect the session's profile context.