Search Results isc_opi_top_ord_qtd_base_v

Overview

The APPS.ISC_OPI_TOP_ORD_QTD_BASE_V view is a Supply Chain Intelligence (ISC) reporting object shipped with Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the ISC product family, which delivers the Operational Procurement Intelligence and related analytics content built on the Oracle EBS data warehouse and the EDW (Enterprise Data Warehouse) materialized views. This particular view exposes order quantity and value metrics at the trading-partner (customer) level, ranked by net booked value. It is a "base" view, meaning it is intended to feed higher-level OPI (Operational Intelligence) dashboards, BI Publisher reports, and Discoverer/OBIEE analyses rather than to be consumed directly by end users in transactional forms.

The view aggregates order, booking, shipping, fulfillment, and invoicing amounts into a single denormalized row per order header, joined to conformed dimension keys for calendar, organization, book, and trade partner. Its naming convention (TOP_ORD = Top Orders, QTD = Quarter-to-Date semantics via the EC period/quarter keys, BASE_V = base view) reflects its role as a ranked, time-bounded source for "top orders" style analytics.

Underlying Base Objects

The ETRM metadata documents the following referenced base objects for this view:

• ISC_EDW_BOOK_SUM1_F — the booked-order summary fact, supplying order number, booked/shipped/fulfilled/invoiced amounts, and the foreign keys to the conformed dimensions.
• EDW_TRD_PARTNER_M — the trade partner (customer) conformed dimension, providing the customer name and trade partner key.
• EDW_ORGANIZATION_M — the organization dimension supplying the operating unit key.
• EDW_GL_BOOK_M — the set-of-books dimension supplying the SOB key.
• EDW_TIME_M — the time dimension providing the calendar day, EC period, and EC quarter keys.

The joins are inner joins on the respective surrogate keys (bill-to customer, date booked, operating unit, and set of books). A key characteristic is the time filter: DATE_BOOKED BETWEEN FII_TIME_WH_API.GET_CURR_EQTR_START AND FII_TIME_WH_API.TODAY, which restricts the result set to the current equalized quarter through today. This makes the view inherently "current period" scoped at execution time.

Key Columns

• SOBL_BOOK_FK_KEY, OPER_UNIT_FK_KEY, CAL_DAY_FK_KEY, EC_PERIOD_FK_KEY, EC_QTR_FK_KEY, TPRT_FK_KEY — conformed dimension surrogate keys enabling drill-down across book, operating unit, day, period, quarter, and trade partner.
• ORDER_NUMBER / HEADER_ID — the order identifier and the underlying order header surrogate.
• CUSTOMER_NAME, ORDERED_DATE, BOOKED_DATE — descriptive and date attributes for reporting context.
• NET_BOOKED_VALUE, SHIPPED_VALUE, FULFILLED_VALUE, INVOICED_VALUE — progressive revenue/quantity metrics aligned to the order lifecycle (booked through invoiced).
• LATEST_SHIP_DATE, FULFILLED_DATE — milestone dates for the shipped and fulfilled stages.
• NUM_OF_ORDERS — constant 1, allowing sum aggregation for order counts.
• RANK — a windowed RANK() OVER (ORDER BY BOOKED_AMT_G DESC), ranking orders by net booked value descending. This is the defining analytical column enabling "top N orders" reporting.

Common Use Cases and Queries

Analysts typically use this view to identify the highest-value orders booked in the current quarter and track their progression through the fulfillment lifecycle. Because the RANK column is computed on booked value, filtering RANK <= 10 yields the top ten booked orders.

Example query:

SELECT ORDER_NUMBER,
       CUSTOMER_NAME,
       BOOKED_DATE,
       NET_BOOKED_VALUE,
       SHIPPED_VALUE,
       INVOICED_VALUE,
       RANK
FROM   APPS.ISC_OPI_TOP_ORD_QTD_BASE_V
WHERE  RANK <= 20
ORDER  BY RANK;

Aggregate usage for customer-level summaries:

SELECT CUSTOMER_NAME,
       SUM(NUM_OF_ORDERS)     AS ORD_CNT,
       SUM(NET_BOOKED_VALUE)  AS TOTAL_BOOKED,
       SUM(INVOICED_VALUE)    AS TOTAL_INVOICED
FROM   APPS.ISC_OPI_TOP_ORD_QTD_BASE_V
GROUP  BY CUSTOMER_NAME
ORDER  BY TOTAL_BOOKED DESC;

Because the view enforces the current-quarter timeframe through FII_TIME_WH_API, consumers can rely on it as a ready-made QTD dataset without applying their own date predicates. Typical embedders include Order Management and Supply Chain dashboards within the ISC OPI framework.