Search Results set_of_books_fk




Overview
Explore in AI Chat

Overview

APPS.ISCBV_EDW_BOOK_SUM1_FCV is a reporting view within the Oracle E-Business Suite Order Management and Advanced Pricing schema. It belongs to the ISCBV family of database objects, which are used primarily by the Oracle Enterprise Data Warehouse (EDW) integration layer for extracting summarized order booking information from the EBS transactional tables. This view acts as a pooled aggregation layer over the "Bookings Summary" business area, materializing per-header booking metrics together with associated dimension foreign keys.

The view aggregates numeric measures from an underlying base view and joins them with descriptive dimension attributes such as order number, booking date, ordered date, and user-defined descriptive flexfield (DFF) column values. It is designed for ETL consumers, BI reports, and downstream analytical schemas that require pre-aggregated booking amounts across multiple currencies and amounts measured in both base and global currency.

Underlying Base Objects

Per ETRM metadata, this view is defined exclusively over the base view ISCBV_EDW_BOOK_SUM1_BASE_FCV. No base tables are directly documented. All measures and attributes present in ISCBV_EDW_BOOK_SUM1_FCV originate from this single base view, and the outer view applies a GROUP BY clause on it to consolidate rows.

The grouping keys include the dimension foreign keys (INSTANCE_FK, OPERATING_UNIT_FK, SET_OF_BOOKS_FK, BILL_TO_CUST_FK, CURRENCY_BASE_FK, DATE_BOOKED_FK), the transaction identifiers (SEQ_ID, BOOKINGS_PK, HEADER_ID), several denormalized descriptive columns (DATE_BOOKED, DATE_ORDERED, INSTANCE, ORDER_NUMBER), and the 25 USER_ATTRIBUTE columns plus USER_FK1 through USER_FK5. Measures are aggregated via SUM and MAX over the base view.

Key Columns

  • INSTANCE_FK — Foreign key identifying the EBS application instance (organization/operating environment) to which the booking row belongs. This is the standard "instance" dimension used throughout EDW extracts to distinguish data sourced from different EBS instances.
  • BOOKINGS_PK — Surrogate primary key of the booking fact record.
  • HEADER_ID — Order header identifier from OE_ORDER_HEADERS_ALL.
  • OPERATING_UNIT_FK / SET_OF_BOOKS_FK / BILL_TO_CUST_FK / CURRENCY_BASE_FK / DATE_BOOKED_FK — Foreign keys to the operating unit, ledger, customer, currency, and booking-date dimensions respectively.
  • BOOKED_AMT_B / BOOKED_AMT_G — Summed booked amounts in base and global currency.
  • BOOKED_LIST_AMT_B / _G — Summed list-price amounts in base and global currency.
  • FULFILLED_AMT_B / _G, INVOICED_AMT_B / _G, SHIPPED_AMT_B / _G — Summed amounts for the fulfillment, invoicing, and shipment lifecycle stages.
  • DATE_BOOKED, DATE_ORDERED, DATE_LATEST_FULFILLED, DATE_LATEST_SHIP — Descriptive date attributes; the latter two are aggregated using MAX.
  • USER_ATTRIBUTE1..25 and USER_MEASURE1..5 — Extensible DFF attributes and custom measures configured for the bookings extract.
  • USER_FK1..5 — Configurable foreign keys allowing extension of the dimension model.

Common Use Cases and Queries

The view is typically queried by EDW extract jobs, OBIEE/BI Publisher reports, and custom analytical SQL that needs booking totals split by instance, operating unit, customer, or currency.

SELECT INSTANCE_FK,
       OPERATING_UNIT_FK,
       SET_OF_BOOKS_FK,
       SUM(BOOKED_AMT_G)     AS total_booked_global,
       SUM(SHIPPED_AMT_G)    AS total_shipped_global,
       COUNT(DISTINCT HEADER_ID) AS order_count
FROM   APPS.ISCBV_EDW_BOOK_SUM1_FCV
WHERE  DATE_BOOKED BETWEEN :p_start AND :p_end
GROUP  BY INSTANCE_FK, OPERATING_UNIT_FK, SET_OF_BOOKS_FK;

Because INSTANCE_FK is a first-class grouping column, multi-instance consolidation is straightforward: filtering or grouping by INSTANCE_FK isolates data from a specific EBS deployment, which is a common requirement for shared EDW repositories sourcing multiple EBS production instances. Analysts also frequently filter on ORDER_NUMBER, HEADER_ID, or BILL_TO_CUST_FK, and pivot on USER_ATTRIBUTE columns to expose customer-specific DFF values in reports.