Search Results aso_i_order_lines_v

Overview

BIM_FCTV_ORDER_OFFERS is a read-only view owned by the APPS schema within the BIM – Marketing Intelligence product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to support the concurrent programs that load the Marketing Intelligence summary tables, meaning it functions as an intermediate fact-source that feeds aggregate and analytical reporting structures rather than serving as a direct end-user reporting object.

From a functional standpoint, the view joins orders, order price adjustments, and marketing offers to answer a specific analytical question: which marketing offers were applied to which order lines and order headers. Because the Marketing Intelligence summary loaders require denormalized, uniform rows — no nulls and no ambiguity about whether an adjustment was header-level or line-level — the view resolves these complexities at the SQL layer so the concurrent programs can consume the output directly.

The view is defined WITH READ ONLY, so it cannot be used in DML. It is a query-only construct intended for SELECT access by concurrent managers and, secondarily, for diagnostic and ad hoc reporting by developers and analysts.

Underlying Base Objects

The view is built over three primary sources in its SELECT statement:

• ASO_I_ORDER_LINES_V — the order lines interface view, supplying HEADER_ID and LINE_ID. It is aliased as ODL in the view text.
• ASO_I_ORDER_PRICE_ADJS_V — the order price adjustment interface view, supplying HEADER_ID, LIST_HEADER_ID, LINE_ID, and the APPLIED_FLAG used to filter only applied adjustments. Aliased as ADJ.
• AMS_ACT_OFFERS — a synonym resolving to the Oracle Marketing activities offers entity, supplying ACTIVITY_OFFER_ID and QP_LIST_HEADER_ID. Aliased as OFFR.

The join path is: order lines match price adjustments on HEADER_ID; offers match price adjustments on QP_LIST_HEADER_ID = LIST_HEADER_ID; and the adjustment is retained only where APPLIED_FLAG = 'Y', with line alignment enforced via ODL.LINE_ID = NVL(ADJ.LINE_ID, ODL.LINE_ID). Documented referenced objects also include the OE_PROFILE and QP_PRICE_LIST_PVT packages, which are invoked indirectly through the underlying ASO interface views.

Key Columns

• ORDER_HEADER_ID — exposed as NVL(ODL.HEADER_ID, -999). This is the column most commonly searched for (the user query term "order_header_id" resolves here). The NVL substitution to the sentinel value -999 ensures the summary loaders never encounter a null key.
• ORDER_LINE_ID — similarly exposed as NVL(ODL.LINE_ID, -999), identifying the specific order line associated with the offer.
• OFFER_ID — exposed as NVL(OFFR.ACTIVITY_OFFER_ID, -999), the identifier of the marketing offer (activity offer) linked through the price list header.
• HEADER_OFFER_FLAG — derived via DECODE(ADJ.LINE_ID, '', 'Y', 'N'). Returns 'Y' when the price adjustment has no line identifier, marking the offer as applied at the order header level; returns 'N' when a line identifier is present, marking it as line-level.

Common Use Cases and Queries

The principal use case is concurrent program data loading into Marketing Intelligence summary tables, where each row represents a distinct order/offer association with a header-versus-line indicator. Typical analytical applications include offer attribution by order, measuring header-level versus line-level promotion uptake, and reconciliation of applied adjustments against offer definitions.

A representative query counts offers by order header:

• SELECT order_header_id, COUNT(DISTINCT offer_id) offer_count FROM apps.bim_fctv_order_offers GROUP BY order_header_id;

To isolate header-level offers only:

• SELECT order_header_id, order_line_id, offer_id FROM apps.bim_fctv_order_offers WHERE header_offer_flag = 'Y';

To trace a specific order's applied offers:

• SELECT * FROM apps.bim_fctv_order_offers WHERE order_header_id = :p_header_id;

Because the view is read-only and carries the -999 sentinel, consumers should filter or interpret that value as "not applicable" rather than as a valid identifier. No DML should ever be attempted against this object.