Search Results iscbv_edw_book_sum1_base_fcv




Overview
Explore in AI Chat

Overview

ISCBV_EDW_BOOK_SUM1_BASE_FCV is a database view owned by the APPS schema within the Oracle E-Business Suite Supply Chain Intelligence (ISC) product family. It belongs to the ETRM (Enterprise Territory and Reporting Model) layer, which supplies the extract-transform-load foundation for Oracle Supply Chain and Order Management analytics. In EBS 12.1.1 and 12.2.2, this view functions as a materialization stage — the "_BASE_FCV" suffix denotes a "fact collection view" — that flattens transactional booking data from Order Management into a denormalized, analytics-ready row set.

The principal role of the view is to reconcile Order Management header and line activity (bookings) against General Ledger currency and calendar dimensions, so that downstream EDW (Enterprise Data Warehouse) fact tables can aggregate booked revenue by customer, operating unit, set of books, currency, and accounting period. It is a read-only reporting object and is not used by Online Transaction Processing (OLTP) flows; it is queried by concurrent programs and BI Publisher or OBIEE-style reporting against the ISC star schemas.

Underlying Base Objects

Although the ETRM metadata does not enumerate documented base objects for this view, the embedded view text shows it is defined over Order Management booking tables and EBS lookup dimensions. The column prefixes reveal the following traces:

Two packaged functions are invoked for derived values: EDW_ORGANIZATION_PKG.OPERATING_UNIT_FK for the operating unit foreign key, and EDW_CURRENCY.GET_RATE along with GL_CURRENCY_API.GET_RATE_SQL for currency conversion.

Key Columns

The view exposes a mix of surrogate primary keys, smart foreign keys, and financial measures:

  • SEQ_ID — Surrogate sequence value used as the fact row identifier.
  • BOOKINGS_PK — Composite key of HEADER_ID concatenated with INSTANCE_CODE.
  • BILL_TO_CUST_FK — Foreign key to the bill-to customer site, derived from INVOICE_TO_ORG_ID.
  • CURRENCY_BASE_FK — Currency of record for the transaction.
  • DATE_BOOKED_FK — Composite date key using BOOKED_DATE, PERIOD_SET_NAME, and ACCOUNTED_PERIOD_TYPE.
  • INSTANCE_FK, OPERATING_UNIT_FK, SET_OF_BOOKS_FK — Multi-org and ledger dimensions.
  • BOOKED_AMT_B — Booked amount in the transactional currency, with quantity multiplied by unit selling price and adjusted for returns, configuration, and "included" item types.
  • BOOKED_AMT_G — The same amount converted to the ledger currency using EDW_CURRENCY.GET_RATE.
  • Additional list-price and conversion-adjusted measures follow the same DECODE-weighted pattern.

Missing values are consistently substituted with the sentinel 'NA_EDW', enabling reliable star-schema joins without NULL handling.

Common Use Cases and Queries

Typical use cases include booking-revenue trending, order-line profitability analysis, currency exposure reporting, and feeding the ISC EDW booking fact tables. A representative query selecting booked revenue by operating unit and period is:

  • SELECT OPERATING_UNIT_FK, DATE_BOOKED_FK, CURRENCY_BASE_FK, SUM(BOOKED_AMT_G) BOOKED_REVENUE FROM APPS.ISCBV_EDW_BOOK_SUM1_BASE_FCV GROUP BY OPERATING_UNIT_FK, DATE_BOOKED_FK, CURRENCY_BASE_FK ORDER BY DATE_BOOKED_FK DESC;
  • A drill-down identifying order headers and currencies: SELECT BOOKINGS_PK, BILL_TO_CUST_FK, CURRENCY_BASE_FK, BOOKED_AMT_B FROM APPS.ISCBV_EDW_BOOK_SUM1_BASE_FCV WHERE DATE_BOOKED_FK LIKE '%-%CD';
  • A reconciliation between transactional and ledger currency: SELECT BOOKINGS_PK, BOOKED_AMT_B, BOOKED_AMT_G, (BOOKED_AMT_G - BOOKED_AMT_B) FX_DIFF FROM APPS.ISCBV_EDW_BOOK_SUM1_BASE_FCV WHERE CURRENCY_BASE_FK <> 'NA_EDW';

Because the view performs function-based currency conversion at query time, performance tuning typically targets the underlying OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL indexes on HEADER_ID and BOOKED_DATE. For high-volume extracts, DBAs should consider materializing the view within the EDW staging schema rather than querying it ad hoc.