Search Results edw_local_set_of_books

Overview

EDW_LOCAL_SET_OF_BOOKS is a table owned by the BIS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It is a component of the Applications BIS product, which supplies the extraction, transformation, and staging infrastructure that feeds Oracle's Enterprise Data Warehouse and related financial reporting layers. In practical terms, the table acts as a local reference or staging copy of set-of-books definitions drawn from the General Ledger, allowing downstream BI and analytical processes to resolve the accounting book context without repeatedly querying the operational GL tables directly.

Under the heuristic Data Vault classification mined from its foreign-key structure, this table is modeled as a standalone entity. It does not participate in an extensive hub-and-link network on the source side; the single documented foreign-key relationship points outward to GL_MGT_SEG_UPGRADE_H via CHART_OF_ACCOUNTS_ID, which suggests the table functions more like a descriptive reference or satellite-style store than a central hub or transaction link. This classification should be treated as a modeling suggestion rather than an authoritative designation.

Key Information Stored

The table contains eleven documented columns. The most significant are listed below, with the surrogate key and business-key candidates distinguished.

• EDW_SET_OF_BOOKS_ID — The warehouse-side surrogate identifier for each row. This is the primary operational key for joins within the EDW/BIS layer.
• SET_OF_BOOKS_ID — The source General Ledger set-of-books identifier. This is the principal business-key candidate and the natural join to GL_SET_OF_BOOKS and other GL fact sources.
• SET_OF_BOOKS_NAME — The descriptive name of the accounting book, used for display and report labeling.
• CHART_OF_ACCOUNTS_ID — The chart of accounts associated with the set of books; this is the column carrying the documented foreign-key relationship.
• INSTANCE — Identifies the EBS instance from which the row was sourced, supporting multi-instance consolidation.
• DESCRIPTION — Free-text description of the book or its configuration.
• Creation and audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN provide standard EBS WHO-column lineage metadata for change tracking.

Common Use Cases and Queries

Typical usage centers on resolving set-of-books context in EDW reports, validating that warehouse book definitions reconcile with the General Ledger, and supporting multi-instance or multi-book financial consolidations.

A common pattern joins the local warehouse copy back to GL books to confirm synchronization:

• SELECT e.SET_OF_BOOKS_ID, e.SET_OF_BOOKS_NAME, e.CHART_OF_ACCOUNTS_ID FROM BIS.EDW_LOCAL_SET_OF_BOOKS e WHERE e.INSTANCE = :instance;
• Joining to GL_SET_OF_BOOKS on SET_OF_BOOKS_ID to compare names and chart-of-accounts assignments between source and warehouse.
• Filtering by LAST_UPDATE_DATE to drive incremental extracts or to detect stale book definitions.

BI extract jobs commonly use the table as a lookup to translate a numeric SET_OF_BOOKS_ID into a human-readable name for financial dashboards and statutory reporting.

Related Objects

• GL_MGT_SEG_UPGRADE_H — referenced through CHART_OF_ACCOUNTS_ID, the sole documented foreign-key target.
• GL_SET_OF_BOOKS — the operational source of set-of-books definitions, joined on SET_OF_BOOKS_ID.
• GL_CODE_COMBINATIONS — keyed by CHART_OF_ACCOUNTS_ID for account-segment resolution.
• GL_LEDGERS — successor ledger definitions in 12.x, related through the accounting configuration.
• Other BIS.EDW_* staging tables — companion EDW extracts that share SET_OF_BOOKS_ID as a common dimension key.