Search Results drop_tmp_col_type_table

Overview

BSC_OLAP_MAIN is an Oracle EBS Balanced Scorecard (BSC) PL/SQL package owned by APPS and classified under the "OTHER" API category. It serves as the primary runtime controller for creating, refreshing, dropping, and managing materialized views (MVs) that support the Oracle Balanced Scorecard OLAP engine and its summary aggregation layer. The package encapsulates the DDL and data-loading logic required to translate BSC KPI definitions and dimension metadata into physical summary objects within the data warehouse, as well as the logic to maintain the reporting calendar used by scorecard computations.

The package declares a substantial set of package-level globals that drive its behavior across calls. Notably, G_CLOB is a CLOB variable used to accumulate and hold large character payloads generated during dynamic SQL construction and log output, which is why users searching for g_clob land on this object. Other globals include g_debug and g_status (boolean flags), g_aw_workspace (the OLAP workspace), g_init_all, g_status_message, option arrays (g_options, g_number_options), and tablespace clause/name variables used to control where summary tables and indexes are created. A schema-scoped temporary table name for column type staging is derived from the session ID. These globals make the package stateful for the duration of a session, so concurrent invocations must be managed carefully.

Key Procedures and Functions

• IMPLEMENT_BSC_MV — Creates the materialized view(s) required for a given KPI, based on an option string; returns success/failure with an error message.
• DROP_BSC_MV — Removes the BSC materialized view associated with a KPI.
• DROP_SUMMARY_MV — Drops a specified summary materialized view.
• REFRESH_BSC_MV — Refreshes the BSC materialized view for a KPI.
• REFRESH_SUMMARY_MV — Refreshes a summary materialized view in relation to a KPI.
• LOAD_REPORTING_CALENDAR — Overloaded function that loads the reporting calendar, either generally or for a specific calendar ID (per Bug#4027813).
• GET_LIST_OF_REC_DIM — Retrieves the list of recursive dimensions, supporting PMV handling of recursive hierarchies.
• SET_AND_GET_DIM_SQL — Constructs and returns dimension SQL used during MV generation.
• RESET — Initializes/resets package global state between operations.
• OPEN_FILE — Opens a file handle, typically for logging output.
• GET_TIME — Returns a timestamp used for logging and diagnostics.
• INIT_ALL — Performs full package initialization.
• WRITE_TO_LOG_FILE / WRITE_TO_LOG_FILE_N — Write log messages, with and without newline handling.
• WRITE_TO_DEBUG / WRITE_TO_DEBUG_N — Emit debug output under debug mode.
• DROP_TMP_COL_TYPE_TABLE / CREATE_TMP_COL_TYPE_TABLE — Manage the session temporary column-type staging table introduced by Bug 3878968.

Tables Accessed

The package references the APPS synonyms AD_DDL and PLITBLM. AD_DDL (Applications DDL) is used to execute dynamic DDL statements consistently within the EBS environment, which aligns with the package's responsibility for creating and dropping materialized views and temporary tables. PLITBLM (PL/SQL Integer Table by Line/Message) is a standard EBS utility table used for message and text handling, consistent with the logging and status-message globals described above.

Usage Notes

BSC_OLAP_MAIN is invoked from Oracle Balanced Scorecard administrative flows — including concurrent programs and internal BSC components that implement, refresh, or drop KPI and summary materialized views, and that load the reporting calendar. Because its public functions take option strings and return boolean status with an OUT error message, it is well suited for programmatic invocation from custom code as well as from standard BSC application logic. The package is referenced by seven other packages, confirming its role as a shared utility layer within the BSC schema. Callers should be aware that the package relies on session-level globals, including G_CLOB, and therefore should not assume it is safe for unrestricted concurrent use within a single session without appropriate reset between operations.