Search Results c_line_id

Overview

APPS.OE_BIS_CANCELLED_BOOKINGS_V is a Business Intelligence System (BIS) reporting view in the Oracle Order Management module of Oracle E-Business Suite. It exposes cancelled order booking lines in a denormalized form suitable for order-management reporting, analytics, and downstream data extraction. The view answers the fundamental business question of what quantity of an ordered line was cancelled, at what selling price, in which currency, on which date, and for which operating unit.

The view is owned by the APPS schema and is available in both EBS 12.1.1 and 12.2.2, where it retains an identical definition. It is consumed by Order Management intelligence reports, custom dashboards, and integration extracts that reconcile booked versus cancelled order demand. Because the view resolves the cancellation quantity dynamically rather than storing it, it reflects cancellation data as of query execution time.

Underlying Base Objects

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

• SO_LINES_ALL (alias L) — the order line detail table, providing line_id, ordered_quantity, shipped_quantity, invoiced_quantity, selling_price, header_id, s1, and s1_date.
• SO_HEADERS_ALL (alias H) — the order header table, providing header_id, currency_code, and org_id (the operating unit identifier).
• SO_ORDER_CANCELLATIONS (alias C) — the cancellation transaction table, providing cancel_date, cancelled_quantity, header_id, and line_id.

The join logic is significant. A row is returned when l.line_id = c.line_id, or when l.header_id = c.header_id AND c.line_id IS NULL, meaning a header-level cancellation with no specific line reference. Additional predicates restrict results to lines where l.s1 = 1 (a shipped or otherwise fulfilled line indicator) and l.s1_date <= c.cancel_date, ensuring the line was fulfilled no later than the cancellation date. The header join on l.header_id = h.header_id is unconditional.

Key Columns

• LINE_ID — The order line identifier from SO_LINES_ALL.
• CANCELLED_QUANTITY — Derived via DECODE and the GREATEST function. When line_id is null, it returns ordered_quantity; otherwise it returns NVL(c.cancelled_quantity, ordered_quantity - GREATEST(shipped_quantity, invoiced_quantity)).
• SELLING_PRICE — The unit selling price of the line.
• CURRENCY_CODE — The transaction currency of the parent order header.
• CANCEL_DATE — The date the cancellation was recorded.
• C_LINE_ID — The cancellation record's line reference (the column targeted by the user's search for "c_line_id"). It is null for header-level cancellations and populated when the cancellation is tied to a specific line.
• HEADER_ID / C_HEADER_ID — The order header and cancellation header identifiers respectively.
• OU_ID — The operating unit (org_id) of the order header, used for multi-org reporting segmentation.

Common Use Cases and Queries

Typical uses include cancellation trending analysis, booked-versus-cancelled demand reconciliation, and multi-org extract pipelines. Because C_LINE_ID is null for header-level cancellations, reporting that must distinguish the two cancellation types should test that column explicitly.

Sample query for line-level cancellations in a given operating unit:

SELECT line_id, c_line_id, cancelled_quantity, selling_price,
       currency_code, cancel_date, header_id
FROM   apps.oe_bis_cancelled_bookings_v
WHERE  ou_id = :p_org_id
AND    cancel_date BETWEEN :p_from AND :p_to
AND    c_line_id IS NOT NULL;

Sample query contrasting header-level versus line-level cancellations:

SELECT DECODE(c_line_id, NULL, 'HEADER', 'LINE') cancel_scope,
       COUNT(*) rows_count, SUM(cancelled_quantity) qty
FROM   apps.oe_bis_cancelled_bookings_v
GROUP  BY DECODE(c_line_id, NULL, 'HEADER', 'LINE');

Because cancelled_quantity is computed rather than persisted, queries against this view should be evaluated for performance on large order volumes; the derived expression depends on correlated values from SO_LINES_ALL and SO_ORDER_CANCELLATIONS at runtime.