Search Results get_base_table_levels

Overview

BSC_METADATA is an Oracle E-Business Suite PL/SQL package body owned by the APPS schema and validated as VALID within the Balanced Scorecard (BSC) product family. Its business function is to provide runtime discovery of the structural metadata that underpins the Balanced Scorecard data model: dimension hierarchies, level definitions, KPI-to-dimension associations, calendar mappings, dimension sets, and the underlying data source objects. Rather than hard-coding this structural knowledge into analytic or reporting code, the BSC intellectual capital modules interrogate BSC_METADATA to determine parent and child levels, resolve primary keys for hierarchy levels, and identify which tables, views, and data sources feed a given dimension or KPI. It is therefore an internal infrastructure package that allows the Scorecard engine, the Analytic Workspace (AW) generation utilities, and the denormalised data builder to remain generic and configuration-driven. The package was documented under ETRM 12.1.1 with an API classification of OTHER, indicating it is not a public, supported extension API but an internal component called by other BSC packages.

Key Procedures and Functions

The ETRM metadata documents 42 procedures and functions, of which the following twenty are enumerated. They fall into four functional clusters.

• Hierarchy navigation: GET_PARENT_LEVEL and GET_CHILD_LEVEL return the parent and child hierarchy levels respectively; GET_LEVEL_PK resolves the primary key that uniquely identifies a given level; IS_DIM_RECURSIVE reports whether a dimension hierarchy is recursive.
• KPI and dimension relationship: GET_KPI_FOR_DIM and GET_DIMS_FOR_KPIS resolve KPI-to-dimension associations in both directions; GET_KPI_FOR_CALENDAR and GET_KPI_CALENDAR map KPIs to their assigned calendars; GET_TARGET_LEVELS and IS_TARGET_AT_HIGHER_LEVEL support target-setting logic at the correct hierarchy level.
• Dimension sets and data sources: GET_KPI_DIM_SETS returns the dimension sets attached to a KPI; GET_DIM_SET_DIMS and GET_DIM_SET_MEASURES expose the dimensions and measures contained within a dimension set; GET_DIM_DATA_SOURCE and GET_DENORM_DATA_SOURCE identify the source objects for dimension and denormalised data respectively.
• Level properties and views: GET_DIM_LEVEL_PROPERTIES and GET_DIM_LEVEL_FILTER return the attributes and filter conditions applicable at a level; GET_S_VIEWS, GET_S_VIEW_LEVELS, and GET_BASE_TABLE_LEVELS expose the star views, their associated levels, and the base tables that supply them.

Tables Accessed

The documented table dependencies are entirely on SYS/PLSQL built-in objects rather than on BSC application tables: DBMS_SQL (dynamic SQL execution) and PLITBLM (the PL/SQL internal table function library), both referenced through APPS synonyms and STANDARD. This indicates that BSC_METADATA performs its metadata interrogation dynamically, constructing and executing SQL at runtime against the BSC metadata repository rather than binding direct table references in static SQL. Consequently, static dependency analysis does not reveal the BSC tables it ultimately queries. The metadata repository provides the level, dimension, KPI, calendar, and dimension-set definitions that drive Scorecard configuration.

Usage Notes

BSC_METADATA is not referenced by any database object external to the BSC package suite, but it is referenced by five other packages, and it in turn depends on BSC_AW_ADAPTER_DIM, BSC_AW_UTILITY, BSC_DBGEN_METADATA_READER, BSC_DBGEN_STD_METADATA, BSC_DBGEN_UTILS, and BSC_UPDATE_DIM. This dependency pattern places it in the internal call chain invoked during Analytic Workspace generation, denormalised data construction, and dimension maintenance. It is typically executed indirectly through the Balanced Scorecard concurrent programs and administrative forms that trigger metadata refreshes and AW builds, rather than being called from custom user code. Because it is classified as an internal (OTHER) API and relies on dynamic SQL, direct invocation by customers is unsupported and subject to change across EBS 12.1.1 and 12.2.2.