Search Results source_tables

Overview

The APPS.BSC_DBGEN_BSC_READER package body is a core metadata reader within the Oracle Balanced Scorecard (BSC) database generation subsystem. Its principal business function is to interrogate the BSC repository — the set of tables that describe KPIs, dimensions, measures, periodicities, calendars, and the physical database tables that store fact data — and return structured information that the database generation engine uses to build and populate the BSC analytical schema. In effect, the package answers questions such as which facts, measures, dimensions, levels, and periodicities are associated with a given object, thereby supplying the generation layer with the metadata it needs to construct SQL, materialized structures, and data-loading logic.

The package is classified as a standard Oracle EBS internal API (classification OTHER) owned by APPS. It is referenced by at least one other package, confirming its role as a shared metadata access layer rather than an end-user-facing component.

Key Procedures and Functions

The package exposes 38 documented procedures and functions. Among the most significant are the following, grouped by purpose:

• Hierarchy and parentage: IS_PARENT_1N and IS_PARENT_MN determine whether a dimension exhibits single-level or multi-level parent-child relationships. GET_CHILDREN_FOR_LEVEL and GET_PARENTS_FOR_LEVEL return the structural members of a level, while GET_LEVEL_INFO and GET_ZERO_CODE_LEVELS supply level metadata and identify levels requiring zero-code handling.
• Fact and measure discovery: GET_FACTS_TO_PROCESS, GET_MEASURES_FOR_FACT, and GET_B_TABLE_MEASURES_FOR_FACT identify facts eligible for generation and the measures associated with each. GET_FACTS_FOR_LEVELS maps facts to dimensional levels.
• Periodicity and calendar resolution: GET_PERIODICITIES_FOR_FACT, GET_PERIODICITY_FOR_TABLE, GET_DB_CALENDAR_COLUMN, and GET_CURRENT_PERIOD_FOR_FACT resolve the time dimension, including the physical calendar column used by each fact table.
• Dimensional structure: GET_DIMENSIONS_FOR_FACT, GET_DIM_SETS_FOR_FACT, GET_BASE_TABLES_FOR_DIM_SET, and GET_FILTER_FOR_DIM_LEVEL describe dimension sets, base tables, and level filters used when generating queries.
• Views and tables: GET_S_VIEWS and GET_LEVELS_FOR_TABLE return the set of source views and the levels associated with a given physical table.

Tables Accessed

All repository access is performed through APPS synonyms. The package reads from BSC_DB_TABLES, BSC_DB_TABLES_COLS, and BSC_DB_TABLES_RELS to resolve physical table structures and relationships; from BSC_DB_CALCULATIONS and BSC_DB_MEASURE_COLS_TL for calculated measures and translated measure columns; from BSC_KPI_DATA_TABLES, BSC_KPI_DIM_LEVELS_B, BSC_KPI_PERIODICITIES, and BSC_KPI_PROPERTIES for KPI-to-table, KPI-to-level, periodicity, and property mappings; from BSC_SYS_CALENDARS_B, BSC_SYS_DIM_LEVELS_B, BSC_SYS_DIM_LEVEL_RELS, and BSC_SYS_MEASURES for system-level calendar, dimension level, relationship, and measure definitions; and from BSC_INTERNAL_COLUMN_S for internal column metadata. Dynamic SQL against these structures is executed via DBMS_SQL.

Usage Notes

BSC_DBGEN_BSC_READER is invoked internally by the BSC database generation engine and by other BSC packages that require consistent metadata resolution. It is not intended for direct invocation from Oracle Forms or from end-user concurrent programs. Customizations should avoid calling these routines directly, as signatures and behavior are governed by Oracle's internal generation logic and subjects to change across releases. The presence of the g_sys_measures cache and g_initialized flag indicates that the package may persist session-level state, so callers should be aware of initialization semantics when reusing a session.