Search Results set_dim_recursive

Overview

APPS.BSC_AW_BSC_METADATA is an internal PL/SQL package belonging to the Oracle Balanced Scorecard (BSC) Analytics Warehouse (AW) metadata layer. Its role is to resolve the structural metadata that defines how a Balanced Scorecard KPI is materialized in the analytics warehouse — specifically the dimensions, dimension sets, dimension levels, measures, calendars, and data sources associated with each KPI. The package acts as a bridge between the configuration stored in the BSC repository and the generated analytics objects, converting configured KPI definitions into the dimension hierarchies, level lists, and recursive parent-child relationships required to build and query aggregated data.

The package header carries the signature /*$Header: BSCAWMDS.pls 120.7 2006/01/14 20:54 vsurendr noship $*/, indicating a shipped, Oracle-owned object that is not intended for direct customer modification. The noship marker reflects that BSC module code is distributed with the applications rather than as standalone shipping code. In release 12.1.1 the package is owned by APPS and classified as OTHER in the ETRM repository, with no public API designation.

Key Procedures and Functions

The documented interface exposes approximately 39 procedures and functions. The principal groupings are:

• Hierarchy resolution: GET_ALL_PARENT_CHILD and GET_PARENT_CHILDREN derive parent-child relationships for a dimension across the configured levels; GET_ALL_DISTINCT_LEVELS returns the distinct level values present in a level list.
• KPI-to-dimension mapping: GET_KPI_FOR_DIM, GET_DIMS_FOR_KPIS, GET_KPI_PROPERTIES, and GET_KPI_DIM_SETS retrieve the KPIs associated with a dimension and the properties and dimension sets attached to a KPI.
• Dimension-set introspection: GET_DIM_SET_DIMS, GET_DIM_SET_MEASURES, GET_DIM_SET_DATA_SOURCE, and GET_S_VIEWS return the dimensions, measures, and source views that make up a given dimension set for a KPI.
• Target and level semantics: IS_TARGET_AT_HIGHER_LEVEL, GET_TARGET_DIM_LEVELS, CHECK_DIM_ZERO_CODE, and GET_DIM_LEVEL_PROPERTIES evaluate level properties and validate zero-code handling for target comparisons.
• Recursive and source setup: SET_DIM_RECURSIVE, CREATE_DATA_SOURCE, CREATE_REC_DATA_SOURCE, and GET_BASE_TABLE_DATA_SOURCE construct the data source definitions, including recursive dimension handling, that feed the analytics build.
• Calendar association: GET_KPI_FOR_CALENDAR resolves the calendar context for KPI processing. This is the routine most closely related to the get_dim_set_calendar search term, which describes the same functional area — mapping a dimension set to its calendar context.

The package maintains runtime state through globals such as g_debug, g_stmt, g_count, and g_exception, and several routines use NOCOPY out parameters to return large PL/SQL tables efficiently.

Tables Accessed

The ETRM record lists two referenced objects through APPS synonyms: DBMS_SQL and PLITBLM. DBMS_SQL is used for dynamic SQL construction, consistent with the g_stmt varchar2(32000) global and the generation of data source and view definitions at runtime. PLITBLM is the standard Oracle PL/SQL-supplied package used for index-by table manipulation, supporting the varchar2_table level and dimension lists passed between procedures. No application base tables are disclosed in the documented metadata; the package operates primarily against BSC AW adapter data structures (dimension, KPI, and calendar record types) rather than direct DML on business tables.

Usage Notes

BSC_AW_BSC_METADATA is an internal utility invoked programmatically, not directly by end users. It is referenced by seven other packages within the BSC Analytics Warehouse stack, which depend on its metadata resolution routines when building or refreshing analytics structures. Typical invocation occurs during Balanced Scorecard configuration and refresh processing, when KPI definitions, dimension sets, and calendars must be translated into concrete data source and hierarchy definitions. Because the package is marked noship and appears in ETRM as a non-public API, customizations should avoid calling it directly; instead, customers requiring equivalent information should use supported BSC public APIs. Any diagnostic use of the g_debug flag or inspection of g_stmt should be treated as unsupported tracing.