Results for “cancel_date”

50+ results




Overview
Explore in AI Chat

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

Overview

OE_BIS_CANCELLED_BOOKINGS_V is a Business Intelligence System (BIS) reporting view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the Oracle Order Entry (OE) product family and exposes cancelled booking information for sales orders. The view consolidates line-level order data with cancellation records, allowing BI Publisher reports, Discoverer workbooks, and custom integrations to analyze cancelled order quantities, prices, and dates without directly joining the underlying transactional tables. Because it is a view, it inherits the security and row-level visibility rules applied to the base synonyms at query time. The view is documented as VALID in the ETRM metadata, and the object name carries the BIS prefix, indicating its intended use in embedded BI reporting rather than in core order-processing logic.

Underlying Base Objects

The view is defined over three referenced base objects, all exposed as synonyms in the APPS schema:

  • SO_HEADERS_ALL — the order header table, supplying header-level attributes such as currency and operating unit.
  • SO_LINES_ALL — the order line table, supplying line quantities, selling prices, and shipment/invoice quantities.
  • SO_ORDER_CANCELLATIONS — the cancellation table, recording cancelled quantities and cancellation dates.

The join condition links a cancellation record either to a specific line (L.LINE_ID = C.LINE_ID) or, where the cancellation is at header level (C.LINE_ID IS NULL), to the header (L.HEADER_ID = C.HEADER_ID). Additional filters restrict results to line records where S1 = 1 and S1_DATE is on or before the cancellation date, and require the header match between SO_LINES_ALL and SO_HEADERS_ALL. This construction ensures only cancellations that occurred after the line's S1 (shipped or fulfilled) milestone are reported.

Key Columns

  • LINE_ID — the order line identifier from SO_LINES_ALL.
  • CANCELLED_QUANTITY — derived using DECODE and NVL logic: if LINE_ID is null, the ordered quantity is returned; otherwise the cancelled quantity from SO_ORDER_CANCELLATIONS, or ordered quantity minus the greater of shipped or invoiced quantity.
  • SELLING_PRICE — the unit selling price of the line.
  • CURRENCY_CODE — the transaction currency from the order header.
  • CANCEL_DATE — the date the cancellation was recorded.
  • C_LINE_ID and C_HEADER_ID — the line and header identifiers from the cancellation record, useful for tracing the cancellation source.
  • HEADER_ID — the order header identifier from SO_LINES_ALL.
  • OU_ID — aliased from H.ORG_ID, this is the operating unit identifier of the order. This column is frequently referenced by users searching for "ou_id," since it enables filtering of cancelled bookings by operating unit for multi-org reporting.

Common Use Cases and Queries

The view is typically used to build cancellation analytics for order management, including cancelled booking value, cancellation trends by operating unit, and currency-level summaries. A representative query filtering on the operating unit is:

  • SELECT HEADER_ID, LINE_ID, CANCELLED_QUANTITY, SELLING_PRICE, CURRENCY_CODE, CANCEL_DATE FROM APPS.OE_BIS_CANCELLED_BOOKINGS_V WHERE OU_ID = :p_org_id AND CANCEL_DATE BETWEEN :p_start AND :p_end;
  • SELECT OU_ID, CURRENCY_CODE, SUM(CANCELLED_QUANTITY * SELLING_PRICE) CANCELLED_VALUE FROM APPS.OE_BIS_CANCELLED_BOOKINGS_V GROUP BY OU_ID, CURRENCY_CODE;
  • SELECT CANCEL_DATE, COUNT(*) CANCELLATION_COUNT FROM APPS.OE_BIS_CANCELLED_BOOKINGS_V WHERE OU_ID = :p_org_id GROUP BY CANCEL_DATE ORDER BY CANCEL_DATE;

Because OU_ID is exposed directly from the header, it is the standard join key for integrating this view with operating unit and organization dimension tables in BI reports.