Search Results list_price_currency_code




Overview
Explore in AI Chat

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:

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.