Search Results returnable_flag

Overview

The view APPS.ASO_PVT_RELATED_ITEMS_BALI_V is a read-only database object owned by the APPS schema within the Oracle E-Business Suite Order Capture (ASO) product family. Its status is documented as VALID in the ETRM repository for both Oracle EBS 12.1.1 and 12.2.2. The view exposes the set of items that are configured as related to a given inventory item within a specific inventory organization, together with the descriptive and commercial attributes needed by order capture and order management processing.

Functionally, the view serves as a presentation layer over the item relationship model. It joins the related items definition (MTL_RELATED_ITEMS_VIEW) to the system items master (MTL_SYSTEM_ITEMS_VL) and to the relationship type lookup values (MFG_LOOKUPS). Because it resolves the relationship type ID to its user-facing meaning and derives an item type code, it is well suited to reporting, integration extracts, and any consumer that needs related-item context without reapplying the decoding logic itself. The RETURNABLE_FLAG column, which was the term of interest in the searching user's query, is one of the item attributes propagated from the item master.

Underlying Base Objects

The view definition references three base objects, all of which are themselves views rather than tables:

• MTL_RELATED_ITEMS_VIEW — alias A. Supplies the relationship facts: ORGANIZATION_ID, INVENTORY_ITEM_ID, RELATED_ITEM_ID, RELATIONSHIP_TYPE_ID, and RECIPROCAL_FLAG.
• MTL_SYSTEM_ITEMS_VL — alias B. Supplies descriptive and commercial attributes of the related item, including concatenated segments, description, primary UOM, list price, BOM item type, service and warranty flags, customer order enabled flag, and the RETURNABLE_FLAG.
• MFG_LOOKUPS — alias C. Supplies the translated MEANING for the relationship type, restricted to LOOKUP_TYPE = 'MTL_RELATIONSHIP_TYPES'.

The join predicates correlate related item to item master on both RELATED_ITEM_ID = INVENTORY_ITEM_ID and ORGANIZATION_ID = ORGANIZATION_ID, and correlate the relationship type to the lookup code. Filtering restricts output to model items (BOM_ITEM_TYPE = 1) or to standard items (BOM_ITEM_TYPE = 4) that are not simultaneously service-flagged and vendor-warranty-flagged, and it excludes items whose primary UOM is ENR.

Key Columns

• ORGANIZATION_ID, INVENTORY_ITEM_ID, RELATED_ITEM_ID — the relationship key identifying which item is related to which within an organization.
• RELATIONSHIP_TYPE_ID / RELATIONSHIP_TYPE — the lookup code and its decoded meaning (for example, substitute or related item types).
• RELATED_ITEM, RELATED_ITEM_DESCRIPTION, RELATED_UOM_CODE, RELATED_UNIT_PRICE — derived from concatenated segments, description, PRIMARY_UOM_CODE, and LIST_PRICE_PER_UNIT.
• RETURNABLE_FLAG — indicates whether the related item may be returned; sourced from MTL_SYSTEM_ITEMS_VL.
• SERVICE_ITEM_FLAG, VENDOR_WARRANTY_FLAG, CUSTOMER_ORDER_ENABLED_FLAG — item classification and orderability indicators.
• BOM_ITEM_TYPE, ITEM_TYPE_CODE — the raw BOM item type and a derived code (MDL, OPT, PLN, SRV, SVA, or STD) computed via nested DECODE on item and service flags.
• RECIPROCAL_FLAG — indicates whether the relationship applies in both directions.
• REQUEST_DATE, SHIP_DATE, AVAILABLE_QUANTITY, REQUEST_DATE_QUANTITY — placeholders in the view text (SYSDATE and NULL), retained for interface compatibility rather than populated with transactional data.

Common Use Cases and Queries

Typical uses include filtering related items that are orderable or returnable, validating substitute or accessory relationships in order capture, and feeding integration extracts. A representative query that finds returnable related items for an organization is:

• SELECT INVENTORY_ITEM_ID, RELATED_ITEM, RELATIONSHIP_TYPE, RETURNABLE_FLAG, ITEM_TYPE_CODE FROM ASO_PVT_RELATED_ITEMS_BALI_V WHERE ORGANIZATION_ID = :org_id AND RETURNABLE_FLAG = 'Y';
• SELECT * FROM ASO_PVT_RELATED_ITEMS_BALI_V WHERE INVENTORY_ITEM_ID = :item_id AND CUSTOMER_ORDER_ENABLED_FLAG = 'Y';

Because the object is a view over other views, it carries no independent storage and should not be treated as an update target.