Results for “retro_lin”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

APPS.OE_RETROBILLED_INFO_V is a reporting view in the Oracle E-Business Suite Order Management module that correlates original sales order lines with the retrobill (retro-billed) order lines subsequently generated against them. Retrobilling is the Order Management mechanism by which a price adjustment, surcharge, or late-arriving charge is applied to an order that has already been shipped and invoiced; the system creates a new order line referencing the original line and documents the difference in pricing. This view exposes both sides of that relationship in a single row, allowing users to see the original ordered item alongside the retrobilled quantity, unit selling price, and extended amount.

Because retrobilled lines are ordinary rows in OE_ORDER_LINES_ALL distinguished by their order source and by the orig_sys_document_ref / orig_sys_line_ref pointers back to the originating order and line, the view acts as a convenience join layer that abstracts those linkage mechanics. It is typically consumed in reports, reconciliations, and integration extracts where the business needs to trace revenue adjustments back to the source order, and it is relevant to the custom search term "retro_lin" because the underlying retrobill line rows are commonly aliased RETRO_LIN in this and related SQL statements.

Underlying Base Objects

The view is defined over four documented base objects, all referenced through APPS synonyms:

• OE_ORDER_LINES_ALL — referenced twice, once as ORIG_LIN for the original order line and once as RETRO_LIN for the retrobilled line. The two instances are joined by RETRO_LIN.ORIG_SYS_DOCUMENT_REF = ORIG_LIN.HEADER_ID and RETRO_LIN.ORIG_SYS_LINE_REF = ORIG_LIN.LINE_ID.
• OE_ORDER_HEADERS — joined on HEADER_ID = RETRO_LIN.HEADER_ID to supply the retrobill order number and order type.
• OE_TRANSACTION_TYPES_TL — joined on ORDER_TYPE_ID = TRANSACTION_TYPE_ID to supply the translated order type name, filtered to the installed language via FND_LANGUAGES.
• FND_LANGUAGES — used in the scalar subquery that restricts OE_TRANSACTION_TYPES_TL to the language whose INSTALLED_FLAG is 'B' (the base/installed language), preventing duplicate rows from multilingual order type descriptions.

The view additionally restricts the retrobill side to ORDER_SOURCE_ID = 27, the seeded order source that identifies retrobill-generated orders.

Key Columns

• ORDER_NUMBER (RETROBILL_ORDER_NUMBER) and RETROBILL_LINE_NUMBER — identify the retrobilled order and line.
• ORIG_SHIPMENT_NUMBER, ORIG_OPTION_NUMBER, ORIG_COMPONENT_NUMBER, ORIG_SERVICE_NUMBER — line-level context from the original line.
• ORDERED_ITEM — the item on the original line.
• PRICING_DATE — the pricing date carried on the retrobilled line.
• QTY — the retrobilled ordered quantity, negated when LINE_CATEGORY_CODE equals 'RETURN'.
• UNIT_SELLING_PRICE — the unit selling price applied to the retrobill line.
• AMOUNT — the extended value (UNIT_SELLING_PRICE × ORDERED_QUANTITY), negated for returns.
• INVOICED_QUANTITY, ITEM_IDENTIFIER_TYPE, ORDER_QUANTITY_UOM — invoicing and UOM attributes from the retrobill line.
• NAME — the translated order type name.
• HEADER_ID, LINE_ID (original) and RETROBILL_HEADER_ID, RETROBILL_LINE_ID — surrogate keys for further joins.

Common Use Cases and Queries

Typical scenarios include reconciling retro-billed adjustments to their source orders, extracting retrobill activity for revenue or AR analysis, and auditing pricing changes on previously shipped lines.

To list all retrobilled adjustments for a given original order number:

• SELECT v.order_number, v.retrobill_line_number, v.ordered_item, v.qty, v.unit_selling_price, v.amount, v.pricing_date FROM apps.oe_retrobilled_info_v v WHERE v.order_number = :p_order_number;

To summarize retrobill amounts by item and order type:

• SELECT v.ordered_item, v.name order_type, SUM(v.amount) total_amount FROM apps.oe_retrobilled_info_v v GROUP BY v.ordered_item, v.name ORDER BY total_amount DESC;

To trace a specific retrobill line back to its origin using the surrogate keys:

• SELECT v.header_id, v.line_id, v.retrobill_header_id, v.retrobill_line_id FROM apps.oe_retrobilled_info_v v WHERE v.retrobill_header_id = :p_header_id AND v.retrobill_line_id = :p_line_id;