Search Results return_reason

Overview

SO_LINES_RETURN_LINES_V is an Oracle E-Business Suite (EBS) view owned by the APPS schema and validated in both release 12.1.1 and 12.2.2. It belongs to the Order Entry (OE) product family and exposes return line information drawn from sales order lines, enriched with lookup descriptions, unit-of-measure codes, and the credit memo transaction that references each returned line. The view is defined with a SELECT that joins SO_LINES to SO_HEADERS, MTL_UNITS_OF_MEASURE, AR_LOOKUPS, and the RA_CUSTOMER_TRX / RA_CUSTOMER_TRX_LINES tables, and also invokes the OE_QUERY package for two derived quantities.

For reporting and integration purposes, the view presents a flattened, read-only projection of return-related order lines. It is commonly used in custom reports, concurrent programs, and interfaces where the user needs the selling price, ordered versus cancelled quantities, and the credit memo generated against a return line without navigating the full Order Management data model directly.

Underlying Base Objects

The documented referenced base objects are:

• SO_LINES (VIEW) — the primary source of line-level attributes, including selling price, quantities, reason codes, and the credit invoice line linkage.
• SO_HEADERS (VIEW) — supplies the order number, order type, and currency code; joined on HEADER_ID.
• MTL_UNITS_OF_MEASURE (SYNONYM) — provides the UOM_CODE; joined with NVL(SL.UNIT_CODE, 'EA') so a default of 'EA' is applied when the line carries no unit code.
• AR_LOOKUPS (VIEW) — a non-mandatory (+) join on LOOKUP_TYPE='CREDIT_MEMO_REASON' and LOOKUP_CODE = NVL( TRANSACTION_REASON_CODE, 'RETURN'), producing the RETURN_REASON meaning.
• RA_CUSTOMER_TRX (SYNONYM) and RA_CUSTOMER_TRX_LINES (SYNONYM) — outer-joined to the credit invoice line, yielding the CREDIT_TO_INVOICE transaction number.
• OE_QUERY (PACKAGE) — supplies the ACCEPTED_QUANTITY and ORDER_TYPE functions used as derived columns.

The header query is driven by the indexed hint /*+ INDEX(SH SO_HEADERS_U1) INDEX(SL SO_LINES_N24) */, indicating the expected access path across header and line tables.

Key Columns

• SELLING_PRICE — the unit selling price captured on the return line; the column the user specifically searched for.
• LINE_ID / HEADER_ID / LINE_NUMBER — identifiers for the line and its parent order header.
• OPEN_QUANTITY — computed as NVL(ORDERED_QUANTITY,0) − NVL(CANCELLED_QUANTITY,0).
• CANCELLED_QUANTITY — quantity cancelled on the line.
• ACCEPTED_QUANTITY — derived via OE_QUERY.ACCEPTED_QTY(LINE_ID).
• RETURN_REASON — decoded meaning from AR_LOOKUPS for the credit memo reason.
• CREDIT_TO_INVOICE — transaction number of the RA_CUSTOMER_TRX generated for the return.
• ORDER_NUMBER / ORDER_TYPE / CURRENCY_CODE — header-level context.
• RETURN_REFERENCE_TYPE_CODE / RETURN_REFERENCE_ID — linkage back to what the return references.
• UOM_CODE, DATE_REQUESTED_CURRENT, ROW_ID — unit of measure, requested date, and row identifier; DUMMY_ID is a constant 0 placeholder.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

Typical scenarios include return-line price analysis, credit memo reconciliation, and open return quantity reporting. Because the view already resolves the UOM, return reason, and credit transaction, it reduces the number of manual joins needed in custom extracts.

-- Return lines with selling price for a given order
SELECT ORDER_NUMBER, LINE_NUMBER, SELLING_PRICE,
       OPEN_QUANTITY, CANCELLED_QUANTITY, RETURN_REASON
FROM   APPS.SO_LINES_RETURN_LINES_V
WHERE  ORDER_NUMBER = :order_number
ORDER  BY LINE_NUMBER;

-- Reconcile returns against their credit memos
SELECT LINE_ID, ORDER_NUMBER, CREDIT_TO_INVOICE,
       SELLING_PRICE, ACCEPTED_QUANTITY
FROM   APPS.SO_LINES_RETURN_LINES_V
WHERE  CREDIT_TO_INVOICE IS NOT NULL;

Query performance benefits from predicates on HEADER_ID, LINE_ID, or ORDER_NUMBER, which align with the view's index hints. As with all APPS views, access should be granted through the appropriate responsibility, and the underlying OE_QUERY function calls should be accounted for when querying large volumes.