Search Results bsc_olap_main




Overview
Explore in AI Chat

Overview

BSC_OLAP_MAIN is the core PL/SQL package body within the Oracle EBS Balanced Scorecard (BSC) module, responsible for managing the materialized views, summary tables, logging utilities, and supporting infrastructure that underpin the OLAP-style reporting engine used by the Balanced Scorecard and related performance-management products. The package is owned by APPS and is classified under the OTHER API category, indicating it is an internal implementation package rather than a published, user-facing API. It operates primarily by generating and executing dynamic DDL and DML against summary and materialized-view objects, and it exposes a set of administrative and diagnostic routines that are invoked by seven other packages in the EBS schema. Its dependency on AD_DDL and PLITBLM confirms that it participates in Oracle Application Object Library's DDL-handling framework and its client-side logging conventions.

Key Procedures and Functions

The package exposes nineteen documented procedures and functions, organized broadly around materialized-view lifecycle management, reporting calendar maintenance, logging, and temp-table handling.

  • IMPLEMENT_BSC_MV, DROP_BSC_MV, REFRESH_BSC_MV — create, remove, and refresh the Balanced Scorecard materialized views that store aggregated KPI data.
  • DROP_SUMMARY_MV, REFRESH_SUMMARY_MV — corresponding lifecycle operations for the summary materialized views.
  • LOAD_REPORTING_CALENDAR — populates the reporting calendar used to align KPI aggregation periods.
  • GET_LIST_OF_REC_DIM — returns the list of recommended dimensions for a given summary context.
  • SET_AND_GET_DIM_SQL — stores and retrieves the dynamically generated dimension SQL used during summary construction.
  • RESET — resets package-level global variables (such as tablespace clauses and flag states) to their initial values.
  • OPEN_FILE, GET_TIME — utility routines for opening the debug/log file and returning a timestamp string.
  • INIT_ALL — initializes all package globals prior to a run.
  • WRITE_TO_LOG_FILE, WRITE_TO_LOG_FILE_N, WRITE_TO_DEBUG, WRITE_TO_DEBUG_N — logging helpers; the "_N" variants append a newline.
  • DROP_TMP_COL_TYPE_TABLE — drops the temporary table that stores CODE datatype mappings, added via Bug 3878968.
  • CREATE_TMP_COL_TYPE_TABLE — the routine the user searched for; it creates the temporary column-type table (level_table_name, data_type) in the summary tablespace, dropping any prior instance first. It also handles the Bug 5458512 distinction between "all objective" mode (all indicators) and incremental/selective mode (only KPIs in prototype or current run). A table-name global (g_col_type_table_name), a tablespace clause (g_summary_table_tbs_clause), and an error-message OUT parameter drive its behaviour.

Tables Accessed

Two objects are documented as referenced via APPS synonyms:

  • AD_DDL — Oracle Application Object Library's DDL invocation package, used to execute dynamic DDL (such as DROP TABLE) through the supported framework rather than raw EXECUTE IMMEDIATE.
  • PLITBLM — the PL/SQL table-to-file logging utility used by WRITE_TO_LOG_FILE and related procedures.

In addition, the package dynamically creates and drops its own temporary objects (the column-type table referenced above) and creates or refreshes materialized views and summary tables whose names and tablespaces are resolved at runtime.

Usage Notes

BSC_OLAP_MAIN is not intended for direct end-user invocation. It is called from the Balanced Scorecard administration flows, from concurrent programs that build or refresh OLAP summaries, and from seven other EBS packages that depend on it. Custom code should avoid calling it directly, since its materialized-view creation logic and the create_tmp_col_type_table routine manipulate schema objects and package globals that assume an initialized BSC session. The drop and create operations observed in the header (Bugs 3878968 and 5458512) reflect historically significant corrections; environments at 12.1.1 and 12.2.2 should therefore treat this package as a maintenance and diagnostics utility, invoked by the BSC scheduler rather than by ad hoc SQL.