Search Results return_context

Overview

IBE_RETURN_DETAIL_V is an Oracle E-Business Suite view owned by the APPS schema and associated with the IBE – iStore product. It stores and presents information about return order detail, exposing line-level attributes for orders categorized as returns so that iStore pages, reports, and integrations can display and process return authorizations without querying the base Order Management tables directly. The view is documented as VALID in ETRM for both 12.1.1 and 12.2.2.

The view is essentially a denormalized read layer over OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL. It joins header and line data, resolves the returned item to its concatenated item number and description, translates lookup codes into meanings, and computes monetary totals through the OE_TOTALS_GRP package. Because it filters on LINE_CATEGORY_CODE = 'RETURN', every row represents a return line rather than a standard sales order line.

Underlying Base Objects

The documented base objects referenced by the view are:

• MTL_SYSTEM_ITEMS_VL (VIEW) – item master; supplies CONCATENATED_SEGMENTS (item number), DESCRIPTION, ATTRIBUTE7 (product description) and RETURNABLE_FLAG.
• MTL_UNITS_OF_MEASURE_TL (SYNONYM) – unit of measure translations; supplies UOM_NAME.
• OE_LOOKUPS (VIEW) – lookup values; translates FLOW_STATUS_CODE into FLOW_STATUS_DESC.
• OE_ORDER_HEADERS_ALL (SYNONYM) – order headers; source of ORDER_NUMBER, ORG_ID, ORDER_CATEGORY_CODE, currency and FLOW_STATUS_CODE.
• OE_ORDER_LINES_ALL (SYNONYM) – order lines, joined twice (aliases OL and OL1) to relate the return line to its original source line.
• OE_PROFILE (PACKAGE) – used in the item join to resolve the operating unit/organization context for MTL_SYSTEM_ITEMS_VL.
• OE_TOTALS_GRP (PACKAGE) – called via GET_ORDER_TOTAL to derive EXTENDED_PRICE, LINES_TOTAL, TAXES_TOTAL and CHARGES_TOTAL.

Relationships are driven by OL.HEADER_ID = OH.HEADER_ID for header context, OL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID with MSI.ORGANIZATION_ID tied to OE_PROFILE for item detail, and OL1 for the originating order/line. Note that APPS.IBE_RETURN_DETAIL_V is the implementation reference shown in the view text.

Key Columns

• Identifiers: LINE_ID, HEADER_ID, ORDER_NUMBER, LINE_NUMBER, TOP_MODEL_LINE_ID, LINK_TO_LINE_ID, ATO_LINE_ID.
• Organization and category: ORG_ID, ORDER_CATEGORY_CODE, LINE_CATEGORY_CODE (fixed to 'RETURN').
• Item detail: INVENTORY_ITEM_ID, ITEM_TYPE_CODE, ITEM_NUMBER, ITEM_DESCRIPTION, PRODUCT_DESCRIPTION, RETURNABLE_FLAG.
• Quantities: ORDERED_QUANTITY, OL1.ORDERED_QUANTITY (source line), SHIPPED_QUANTITY, CANCELLED_QUANTITY, ORDER_QUANTITY_UOM, UOM_NAME.
• Pricing and totals: UNIT_LIST_PRICE, UNIT_SELLING_PRICE, TRANSACTIONAL_CURR_CODE, EXTENDED_PRICE, LINES_TOTAL, TAXES_TOTAL, CHARGES_TOTAL.
• Dates: SCHEDULE_SHIP_DATE, REQUEST_DATE, PROMISE_DATE.
• Status and referencing: FLOW_STATUS_CODE, FLOW_STATUS_DESC, SHIPMENT_NUMBER, OPTION_NUMBER, COMPONENT_NUMBER, SERVICE_NUMBER.
• Parties and return context: SOLD_TO_ORG_ID, INVOICE_TO_ORG_ID/CONTACT_ID, SHIP_TO_ORG_ID/CONTACT_ID, RETURN_CONTEXT, RETURN_REASON_CODE, and SOURCE_HEADER_ID / SOURCE_LINE_ID decoded from RETURN_ATTRIBUTE1/2 when RETURN_CONTEXT = 'ORDER'.

Because LINES_TOTAL is computed by OE_TOTALS_GRP.GET_ORDER_TOTAL(..., 'LINES'), it reflects the line-level total for the return line, which is precisely the value users seek when searching for "lines_total".

Common Use Cases and Queries

The view supports iStore return flows, return authorization listings, customer-facing order status, and reconciliation reports that require line totals, tax, charges and extended price in one query. Typical filters use ORDER_NUMBER, LINE_ID or SOURCE_HEADER_ID.

• Retrieve line totals for a return:
  SELECT order_number, line_number, item_number, ordered_quantity, lines_total, taxes_total, extended_price FROM apps.ibe_return_detail_v WHERE order_number = :p_order_number;
• List returnable items for a customer:
  SELECT order_number, line_number, item_number, return_reason_code, flow_status_desc FROM apps.ibe_return_detail_v WHERE sold_to_org_id = :p_org_id AND returnable_flag = 'Y';
• Trace a return back to its originating order line:
  SELECT order_number, line_number, source_header_id, source_line_id, return_context FROM apps.ibe_return_detail_v WHERE return_context = 'ORDER';

Since the view performs package calls and multiple joins, queries should be constrained by header or line identifiers where possible to avoid full scans of the Order Management tables.