Search Results get_session_error




Overview
Explore in AI Chat

Overview

BSC_UTILITY is the shared utility library for the Oracle Balanced Scorecard (BSC) application, one of the products in the Oracle E-Business Suite 12.1.1 and 12.2.2 technology stack whose physical schema objects continue to reside in the APPS schema. The package is classified as a general-purpose API (ETRM classification OTHER); it is not a business transaction interface but a toolkit of low-level helpers used across the BSC subsystem. Its responsibilities include debug tracing, dynamic SQL execution, session and timestamp retrieval, error capture onto the FND message stack, PL/SQL validation for user-authored report logic, conversion of delimited strings into PL/SQL collections, and lookup of Balanced Scorecard metadata such as datasets, KPI dimension levels, and internal objects.

The package header file is BSCUTILS.pls, currently at version 120.24, dated 4-OCT-2007. The header comment log records a long series of bug and enhancement fixes between November 2003 and January 2006, covering additions such as Get_Default_Internal_Name, Get_Valid_Bsc_Master_Tbl_Name, dimension entity validation APIs, Get_Responsibility_Key, Get_User_Time, and the Enable/Disable All support in Report Designer. A significant enhancement (Enhancement#3909868, January 2006) added the report PL/SQL validation and query-building APIs: Validate_Plsql_For_Report, Get_Plsql_Parameters, Remove_Repeating_Comma, Validate_PLSQL, Obtain_Report_Query, Insert_Into_Query_Table, and Do_DDL_AT. The presence of C_BSC_UNDERSCORE, a constant moved here from BSCCRUDS.pls, illustrates the package's role as the central repository for shared constants and naming rules.

Key Procedures and Functions

The documented package exposes 78 procedures and functions. The core groups are:

  • Debug and trace utilities: ENABLE_DEBUG, DISABLE_DEBUG, PRINT_DEBUG, DEBUG, PRINT_FCN_LABEL, and PRINT_FCN_LABEL2. These control a session-level tracing facility and emit procedure labels and diagnostic text during development or troubleshooting runs.
  • SQL execution and transaction control: DO_SQL performs dynamic SQL execution; CLOSE_CURSOR releases open cursors; DO_ROLLBACK issues a rollback; DO_DDL_AT executes a DDL statement in autonomous transaction mode (commonly used with Insert_Into_Query_Table).
  • Environment and session information: GET_SYSTEM_TIMESTAMP, GET_SESSION_ERROR, GET_USER_TIME (per the header log), and GET_RESPONSIBILITY_KEY (per the header log).
  • Warehouse and dataset helpers: IS_EDW_INSTALLED, UPDATE_EDW_FLAG, and GET_DATASET_ID, which support the interface between the Balanced Scorecard and the Enterprise Data Warehouse installation.
  • Dimension and KPI metadata: GET_KPI_DIM_LEVELS and GET_KPI_DIM_LEVEL_SHORT_NAMES return dimension level identifiers and abbreviated labels for KPI presentation.
  • Array conversion: LISTTONUMERICARRAY and LISTTOSTRINGARRAY convert comma-separated lists into PL/SQL arrays; Remove_Repeating_Comma (per the header log) normalizes malformed delimited strings before conversion.
  • Message stack and security: ADD_TO_FND_MSG_STACK places errors and warnings onto the Oracle Forms message stack so they surface through the standard EBS error-handling framework; IS_INTERNAL_USER distinguishes internal Oracle users for validation purposes.
  • Report PL/SQL validation: Validate_Plsql_For_Report, Validate_PLSQL, Get_Plsql_Parameters, and Obtain_Report_Query support user-entered PL/SQL and query definitions in Report Designer.

Tables Accessed

BSC_UTILITY references its tables through APPS synonyms. The metadata layer is read for validation and enumeration: BSC_KPIS_B, BSC_SYS_DATASETS_B and BSC_SYS_DATASETS_TL (dataset ID resolution), BSC_KPI_DIM_GROUPS, BSC_KPI_DIM_LEVELS_B and BSC_KPI_DIM_LEVELS_TL, BSC_KPI_DIM_LEVEL_PROPERTIES, and BSC_KPI_ANALYSIS_MEASURES_B. The BIS_LEVELS, BIS_INDICATORS, and BIS_TARGET_LEVELS tables supply level and indicator definitions inherited from the BIS schema lineage. BSC_DB_CALENDAR provides the Balanced Scorecard calendar used by timestamp and period helpers. BSC_MESSAGE_LOGS supports logging performed by the message and debug utilities. AK_REGIONS and AK_REGION_ITEMS are queried in the context of Oracle Application Framework/Forms region lookup, and the package also touches FND message stack structures indirectly through ADD_TO_FND_MSG_STACK.

Usage Notes

BSC_UTILITY is an internal dependency rather than a directly invoked interface. It is referenced by 40 other packages in the APPS schema, making it a foundational library for Balanced Scorecard modules such as Report Designer, KPI administration, and analytics presentation. It is typically called from PL/SQL within other BSC packages, from Oracle Forms-driven flows where ADD_TO_FND_MSG_STACK surfaces validation errors, and from customization or extension code that needs to validate user-supplied PL/SQL, resolve dataset or KPI dimension identifiers, or obtain a formatted system timestamp. Because BSC_UTILITY is not a public business API, direct invocation in customizations should be limited to the documented helper routines and is subject to the usual warning that Oracle may change internal packages between releases; consumers should validate behavior in the target 12.1.1 or 12.2.2 instance before relying on it.