Search Results line_flow_status_code

Overview

IBE_RETURNABLE_LINES_V is an Oracle EBS iStore (IBE) view owned by the APPS schema that exposes order lines eligible (or ineligible) for return processing. It presents a single-row-per-order-line projection of Oracle Order Management data, augmented with inventory item attributes and unit-of-measure translations. The view is primarily consumed by iStore return flows, order inquiry pages, and customer self-service return screens, where the application must determine whether a given shipped line may be returned. The central computed column, LINE_RETURNABLE_FLAG, encapsulates this business rule by evaluating multiple conditions in a nested DECODE expression. The view joins order lines and headers to the item master and UOM tables, so it resolves item numbers, descriptions, and currencies at query time rather than requiring the caller to perform these joins. Because it is a view rather than a table, it carries no independent data and reflects the current transactional state of the underlying Order Management tables.

Underlying Base Objects

The view is defined over four base objects in the documented metadata:

• OE_ORDER_LINES_ALL (SYNONYM) — the primary source of line-level attributes, aliased as OL.
• OE_ORDER_HEADERS_ALL (SYNONYM) — supplies header attributes such as order number, org, currency, flow status, and sold-to org, aliased as OH and joined on HEADER_ID.
• MTL_SYSTEM_ITEMS_VL (VIEW) — the item master, joined to resolve the concatenated item number, description, returnable flag, serial control, and product description (ATTRIBUTE7). The join is restricted by MSI.ORGANIZATION_ID = OE_PROFILE.VALUE('OE_ORGANIZATION_ID', OL.ORG_ID).
• MTL_UNITS_OF_MEASURE_TL (SYNONYM) — provides the translated UOM name, joined on UOM_CODE and filtered by USERENV('LANG').
• OE_PROFILE (PACKAGE) — invoked as a function to resolve the inventory organization profile value per order org.

All joins are inner joins, so lines without valid item or UOM references are excluded.

Key Columns

• LINE_ID / HEADER_ID — primary and foreign keys to the order structures.
• ORDER_NUMBER / LINE_NUMBER — human-readable order and line identifiers.
• ORG_ID / ORDER_CATEGORY_CODE / FLOW_STATUS_CODE — operational context derived from the header; line-level flow status is also exposed as LINE_FLOW_STATUS_CODE.
• INVENTORY_ITEM_ID / ITEM_NUMBER / ITEM_DESCRIPTION / PRODUCT_DESCRIPTION — item identity resolved from MTL_SYSTEM_ITEMS_VL.
• ORDERED_QUANTITY / SHIPPED_QUANTITY / CANCELLED_QUANTITY — quantities used to assess return quantity limits.
• UNIT_LIST_PRICE / UNIT_SELLING_PRICE / CURRENCY_CODE — pricing information for credit calculation.
• SCHEDULE_SHIP_DATE / REQUEST_DATE / PROMISE_DATE — fulfillment dates commonly used for return-window logic.
• LINE_RETURNABLE_FLAG — the computed flag returning 'Y' or 'N'. It yields 'N' when the line is a top model, non-shippable, has a non-returnable item (RETURNABLE_FLAG = 'N'), is a SERVICE item, is a RETURN category line, or when the header flow status is ENTERED or CANCELLED; otherwise 'Y'.
• SERIAL_CONTROL_FLAG — 'Y' when the item serial number control code is 2, 5, or 6, indicating serialized return handling.

Common Use Cases and Queries

The view is queried when building return eligibility lists, validating a customer-selected line for return, or reporting on returnable inventory.

SELECT order_number, line_number, item_number,
       ordered_quantity, shipped_quantity,
       line_returnable_flag
FROM   apps.ibe_returnable_lines_v
WHERE  order_number = :p_order_number
AND    line_returnable_flag = 'Y';

To identify serialized items requiring serial capture on return:

SELECT line_id, item_number, serial_control_flag
FROM   apps.ibe_returnable_lines_v
WHERE  order_number = :p_order_number
AND    serial_control_flag = 'Y';

Because the flag is computed inline, it cannot be indexed directly; performance is best served by filtering on ORG_ID, ORDER_NUMBER, or HEADER_ID inherited from the base order tables.