Search Results so_headers_u1




Overview
Explore in AI Chat

Overview

APPS.SO_LINES_RETURN_LINES_V is a reporting view in Oracle E-Business Suite that consolidates Order Management return line information into a single queryable structure. It is designed to expose return-related attributes of sales order lines alongside associated credit memo, unit of measure, and lookup information, making it suitable for reporting, integration, and downstream processing of RMAs and returns. The view is defined over the SO_LINES and SO_HEADERS views, joined to unit of measure, AR lookup, and receivables transaction objects to present a denormalized record per return line.

A signature characteristic of this view is the explicit use of the optimizer hint /*+ INDEX(SH SO_HEADERS_U1) INDEX(SL SO_LINES_N24) */. This confirms the view is tuned to drive execution plans through the SO_HEADERS_U1 unique index on SO_HEADERS and the SO_LINES_N24 index on SO_LINES, reflecting the view's intended access path when filtering return lines by header or line criteria.

Underlying Base Objects

The documented referenced objects are: AR_LOOKUPS (view), MTL_UNITS_OF_MEASURE (synonym), OE_QUERY (package), RA_CUSTOMER_TRX (synonym), RA_CUSTOMER_TRX_LINES (synonym), SO_HEADERS (view), and SO_LINES (view). The view text joins these as follows:

  • SO_LINES SL is the driving table, supplying line-level attributes such as LINE_ID, HEADER_ID, quantities, pricing, and return reference data.
  • SO_HEADERS SH is joined on SH.HEADER_ID = SL.HEADER_ID to supply order number, order type, and currency.
  • MTL_UNITS_OF_MEASURE UOM is joined via UOM.UOM_CODE = NVL(SL.UNIT_CODE,'EA'), defaulting to 'EA' when the unit code is null.
  • AR_LOOKUPS AL is an outer join on LOOKUP_TYPE = 'CREDIT_MEMO_REASON' and LOOKUP_CODE = NVL(SL.TRANSACTION_REASON_CODE,'RETURN'), yielding the return reason meaning.
  • RA_CUSTOMER_TRX RCT and RA_CUSTOMER_TRX_LINES RCTL are outer-joined on the credit invoice line to expose the credit-to-invoice transaction number.
  • OE_QUERY is invoked through its ACCEPTED_QTY and ORDER_TYPE functions to derive accepted quantity and order type description.

Key Columns

Common Use Cases and Queries

Typical uses include return line reporting, RMA reconciliation, and integration extracts that require credit memo linkage.

  • Listing open return quantities by order:
SELECT order_number, line_number, uom_code, open_quantity
FROM   apps.so_lines_return_lines_v
WHERE  open_quantity > 0
ORDER  BY order_number, line_number;
  • Return reasons with credit invoice linkage (filtered via the SO_HEADERS_U1 access path):
SELECT order_number, return_reason, credit_to_invoice, accepted_quantity
FROM   apps.so_lines_return_lines_v
WHERE  header_id = :p_header_id
AND    return_reference_type_code IS NOT NULL;
  • Aggregating cancelled and accepted quantities per order type:
SELECT order_type, SUM(cancelled_quantity), SUM(accepted_quantity)
FROM   apps.so_lines_return_lines_v
GROUP  BY order_type;

Because ORDER_TYPE and ACCEPTED_QUANTITY are computed through OE_QUERY function calls, queries returning many rows should be filtered aggressively to avoid row-by-row PL/SQL function invocation overhead.