Search Results retrieve_bsc_dim_levels_md

Overview

APPS.BSC_DIMENSION_LEVELS_PVT is the private implementation package behind the Balanced Scorecard (BSC) Dimension Level public API in Oracle E-Business Suite. It provides the core PL/SQL logic used to create, maintain, and validate dimension levels and the relationships between them within the BSC/OBIA performance management model. Dimension levels define the hierarchical structures (such as Organization, Time, or Account hierarchies) that organize data in the Balanced Scorecard repository.

The package header carries the RCS identifier BSCVDMLS.pls 120.0, dated 2005/06/01, and is Copyright Oracle Corporation. Its design follows the standard EBS private/public API pattern: BSC_DIMENSION_LEVELS_PUB holds the public specification, while this PVT package holds the actual implementation and is normally invoked indirectly through the public wrapper. The stated purpose in the header — "This package creates a BSC Dimension (Level)" — confirms its role as the metadata engine for dimension-level definition, including the Create_Dim_Level entry point that populates all metadata for a given dimension level.

Key Procedures and Functions

The package exposes approximately thirty documented procedures organized around CRUD operations, validation, and identifier generation:

• CREATE_DIM_LEVEL / RETRIEVE_DIM_LEVEL / UPDATE_DIM_LEVEL / DELETE_DIM_LEVEL — the primary lifecycle procedures for a dimension level record. CREATE_DIM_LEVEL is described in the source as the entry point that populates all metadata for dimension levels; it accepts a commit flag and a dimension-level record, and returns the standard FND_API return status, message count, and message data.
• CREATE_DIM_LEVEL_RELATION — creates the association (relationship) between dimension levels, writing to BSC_SYS_DIM_LEVEL_RELS. This is the procedure most frequently associated with the search term "create_dim_level_relation."
• CREATE/RETRIEVE/UPDATE/DELETE_BSC_DIM_LEVELS_MD — manage the multi-language descriptive metadata for dimension levels.
• CREATE/RETRIEVE/UPDATE/DELETE_BSC_SYS_DIM_LVL_COLS — maintain the column-level definitions attached to system dimension levels.
• GET_NEXT_VALUE, GET_ID — sequence and identifier utilities used when inserting new level or relation rows.
• VALIDATE_DIM_LEVEL, VALIDATE_DIM_GROUP, VALIDATE_VALUE — validation routines ensuring referenced levels, groups, and values exist and are consistent before persistence.
• GET_DIM_LEVELS, GET_OBJECT_NAME — retrieval helpers returning level sets and resolving object names.
• TRANSLATE_DIMENSION_LEVEL — added June 2003 (enhancement 2842894) to support translated level names.
• RETRIEVE_RELATIONSHIP — added May 2003 (enhancement 2901823) to return relation metadata.

Tables Accessed

All access is performed through APPS synonyms. The core dimension tables are BSC_SYS_DIM_LEVELS_B (base level definitions), BSC_SYS_DIM_LEVELS_TL (translated names), BSC_SYS_DIM_LEVELS_BY_GROUP (group-to-level mapping), and BSC_SYS_DIM_LEVEL_RELS (level-to-level relationships, written by CREATE_DIM_LEVEL_RELATION). Column metadata resides in BSC_SYS_DIM_LEVEL_COLS. Several _S sequences — BSC_SYS_DIM_LEVEL_ID_S, BSC_SYS_DIM_GROUP_ID_S, and others — provide primary keys via GET_NEXT_VALUE/GET_ID. BSC_DB_TABLES and BSC_DB_TABLES_RELS map levels to their underlying database tables, while FND_LANGUAGES supports translation. AD_DDL is used for dynamic DDL operations, and DUAL for simple expression evaluation.

Usage Notes

Because this is a PVT package, it should not be called directly by customer code; the supported entry point is the BSC_DIMENSION_LEVELS_PUB public API, which delegates to these procedures. The package is referenced by twenty other packages, indicating it is a foundational dependency for higher-level BSC setup and administration routines. It is typically invoked from the Balanced Scorecard administrator setup forms and from concurrent programs that build or refresh dimension metadata. Custom integrations that must create dimension levels or level relations should call the PUB wrapper rather than this private package, and should honor the p_commit convention (using FND_API.G_FALSE when participating in a larger transaction). The standard NOCOPY OUT parameters x_return_status, x_msg_count, and x_msg_data should always be inspected for error handling.