Search Results secondary_uom_code

Overview

OE_LINES_VIEW_RETURNS_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite, associated with the Order Management (ONT) product family. In the documented ETRM metadata for release 12.2.2, the view is flagged with the description "No longer used." This designation indicates that the object is retained for backward compatibility and is not part of the supported, forward-looking integration surface for Order Management. Its status remains VALID in the data dictionary, meaning the underlying SQL still resolves against its base objects, but Oracle does not recommend building new dependencies on it.

Functionally, the view presents a flattened, denormalized projection of return-related order lines. It joins order line and header data to units of measure, lookup meanings, transaction type translations, and credit memo transaction data. The intended role was to support reporting and integration scenarios requiring return authorizations alongside their associated credit-to-invoice references, expected return dates, and accepted, cancelled, and open quantities. Because it is marked obsolete, its practical role in 12.1.1 and 12.2.2 is limited to legacy reports, custom extensions, or interfaces that were written against it prior to its deprecation.

Underlying Base Objects

The view text joins several documented base objects. OE_ORDER_LINES supplies the driving line-level rows, including quantities, pricing, item, shipment, and option number attributes. OE_ORDER_HEADERS supplies the order number and links the header to its order type. Two aliases of OE_TRANSACTION_TYPES_TL provide translated names for the order type (OOT) and the line type (OLT), each restricted to the session language through USERENV('LANG'). MTL_UNITS_OF_MEASURE resolves the unit of measure code, defaulting to 'EA' when the order quantity UOM is null.

Return reasons are resolved from AR_LOOKUPS through an outer join on the CREDIT_MEMO_REASON lookup type. Credit memo linkage is established by outer-joining RA_CUSTOMER_TRX_LINES_ALL to RA_CUSTOMER_TRX_ALL on customer transaction ID, and then to the order line on CREDIT_INVOICE_LINE_ID. This outer-join chain preserves order lines that have no associated credit memo. In the documented dependency list, AR_LOOKUPS appears as a view, while the remaining objects are referenced through synonyms.

Key Columns

• LINE_ID, HEADER_ID, LINE_NUMBER, SHIPMENT_NUMBER, OPTION_NUMBER: Core identifiers for the order line. OPTION_NUMBER is the column most directly associated with the user's search term and identifies the option line within a configured or model item context.
• UOM_CODE, SECONDARY_UOM_CODE: Primary and secondary units of measure, with the secondary code derived from ORDERED_QUANTITY_UOM2.
• OPEN_QUANTITY, ACCEPTED_QUANTITY, CANCELLED_QUANTITY: Return quantities at the line level, with open quantity drawn from ORDERED_QUANTITY and accepted quantity mapped from FULFILLED_QUANTITY. Parallel *_QUANTITY2 columns expose the corresponding secondary-UOM values.
• UNIT_SELLING_PRICE: Selling price per unit for the return line.
• RETURN_REASON: The translated meaning of the return reason code from AR_LOOKUPS.
• CREDIT_TO_INVOICE: The credit memo transaction number linked to the line, where one exists.
• ORDER_NUMBER, ORDER_TYPE, LINE_TYPE: Descriptive context for the header and line.
• EXPECTED_RETURN_DATE: Mapped from PROMISE_DATE on the order line.
• SHIP_FROM_ORG_ID, ORG_ID, ROW_ID: Organization identifiers and the row identifier.

Common Use Cases and Queries

Given the obsolete designation, usage is typically limited to validating or maintaining legacy custom code. Typical queries select return lines with their credit memo references, or aggregate open and accepted quantities by order type:

• SELECT order_number, line_number, option_number, return_reason, open_quantity, accepted_quantity, credit_to_invoice FROM oe_lines_view_returns_v WHERE org_id = :org_id;
• SELECT order_type, SUM(open_quantity) FROM oe_lines_view_returns_v GROUP BY order_type;

Because the view is documented as no longer used, new development should target supported Order Management and Receivables entities rather than this object.