Search Results get_parent_dimension_levels

Overview

APPS.BSC_DIMENSION_LEVELS_PUB is the public PL/SQL package body that maintains dimension levels within the Oracle Balanced Scorecard (BSC) module of Oracle E-Business Suite. Its declared purpose, per the package header comment, is to create a dimension level in BSC. Dimension levels define the hierarchical granularity and structural relationships used when BSC aggregates and reports performance measures across an organization. The package encapsulates create, retrieve, update, and delete operations for dimension level definitions and for the associated system dimension level columns, and it exposes validation routines that enforce relational integrity between parent and child levels.

The package body carries a revision history from the original creation in October 2001 through later enhancements covering granular locking, multi-language support (removal of hardcoded "US" language values), "All" and "Comparison" lookup handling, LUD (last update date) validations, and LDT (loader) processing corrections. These changes indicate the package is a long-lived, actively maintained integration point rather than a standalone utility.

Key Procedures and Functions

The documented API surface comprises 26 procedures and functions, organized into three functional groups.

• Dimension level CRUD: CREATE_DIM_LEVEL, RETRIEVE_DIM_LEVEL, UPDATE_DIM_LEVEL, and DELETE_DIM_LEVEL manage the definition and lifecycle of individual dimension levels. CREATE_DIM_LEVEL is overloaded to support distinct invocation scenarios and derives the "All" and "Comparison" level values from lookups rather than hardcoding them.
• System dimension level columns and metadata: CREATE_BSC_DIM_LEVELS_MD, RETRIEVE_BSC_DIM_LEVELS_MD, UPDATE_BSC_DIM_LEVELS_MD, DELETE_BSC_DIM_LEVELS_MD, and the parallel set CREATE_BSC_SYS_DIM_LVL_COLS, RETRIEVE_BSC_SYS_DIM_LVL_COLS, UPDATE_BSC_SYS_DIM_LVL_COLS, DELETE_BSC_SYS_DIM_LVL_COLS handle metadata records and the column-level mappings that bind a dimension level to its underlying data structure.
• Hierarchy and relationship logic: CREATE_DIM_LEVEL_RELATION, DELETE_DIM_LEVEL_RELATION, RETRIEVE_RELATIONSHIP, IS_VALID_RELATIONSHIP, IS_DEPENDENT, IS_CHILD_PARENT, GET_PARENT_DIMENSION_LEVELS, and GET_CHILD_DIMENSION_LEVELS define, validate, and navigate the parent-child structure among levels. IS_CHILD_PARENT was introduced for enhancement 2901823, and granular locking changes were made alongside it.

A TRANSLATE_DIMENSION_LEVEL procedure supports translated level descriptions, and LOAD_DIMENSION_LEVEL loads level data, with revised exception handling introduced during the ALL enhancement forward port. The routine raises errors via the standard FND_MSG_PUB mechanism, so callers should inspect x_msg_data for the returned message text after a failure.

Tables Accessed

The package operates against BSC_SYS_DIM_LEVELS_B (the base table holding dimension level definitions) and BSC_SYS_DIM_LEVELS_TL (the translation table storing language-specific level names and descriptions), both accessed through APPS synonyms. The _B table is the primary target of the create, update, and delete routines; the _TL table is written by the translation procedure and read during retrieval so that level text is returned in the caller's session language. LUD validations for dimension levels by group were added against these tables.

Usage Notes

The package is referenced by ten other packages, confirming it as a shared dependency for BSC setup and administration flows. It is typically invoked from BSC administrative forms, from concurrent programs that load or migrate dimension level definitions (including LDT file uploads), and from custom code requiring programmatic maintenance of scorecard hierarchies. Because callers receive error information through the standard message stack, any wrapper code should read x_msg_data or the FND_MSG_PUB message stack immediately after an unsuccessful call. The _PUB suffix indicates the supported public interface; direct DML against the underlying BSC_SYS_DIM_LEVELS tables should be avoided in favor of these APIs.