Search Results implement_bsc_mv_vb

Overview

BSC_BIA_WRAPPER is a PL/SQL package owned by the APPS schema in Oracle E-Business Suite Release 12.1.1 and 12.2.2. It belongs to the Oracle Balanced Scorecard (BSC) product family and functions as a thin abstraction layer over the materialized view (MV) and metadata optimization logic used by the BSC reporting and Business Intelligence Applications (BIA) stack. The package name reflects its role as a wrapper: rather than exposing the underlying metadata optimizer routines directly, it provides a stable, version-tolerant API surface that other BSC components — and in particular the "VB" (VisualBasic-era, backported) callers referenced in the header comments — can invoke without depending on packages that may not exist in older releases such as 5.1.1 and 5.1.1.1.

The header comment for bug 3835059 explicitly states that the wrapper was created so that older clients could call this API where bsc_metadata_optimizer_pkg does not exist. A later fix (bug 5069433) added package-level state for projection KPIs. The package therefore serves both as a compatibility shim and as the operational driver for summary materialized view lifecycle management in Balanced Scorecard deployments.

Key Procedures and Functions

The documented API comprises sixteen procedures and functions. Principal among them — and directly relevant to the search term implement_bsc_mv_vb — is IMPLEMENT_BSC_MV_VB, the VB-callable variant of IMPLEMENT_BSC_MV. These routines create (implement) the summary materialized views that support BSC KPI aggregation and reporting. The _VB suffix denotes the entry point intended for invocation from the metadata optimizer path, with error handling that writes to BSC_MESSAGE_LOGS rather than raising.

• ANALYZE_TABLE — Gathers optimizer statistics on a named table, typically invoked after an MV refresh.
• DO_ANALYZE — Returns a BOOLEAN indicating whether the MVs should be analyzed following a refresh.
• DROP_RPT_KEY_TABLE — Drops the bsc_rpt_keys_% reporting key tables for a given user, returning an error message via an OUT parameter.
• DROP_RPT_KEY_TABLE_VB — The metadata-optimizer-facing variant of the above; on error it logs to BSC_MESSAGE_LOGS.
• DROP_SUMMARY_MV — Removes an existing summary materialized view.
• DROP_SUMMARY_MV_VB — VB/metadata-optimizer variant of the MV drop, with logging-based error handling.
• GET_SUM_TABLE_MV_NAME — Derives the physical MV name for a summary table.
• IMPLEMENT_BSC_MV — Creates the summary MV.
• IMPLEMENT_BSC_MV_VB — VB-callable MV implementation entry point.
• INDICATOR_HAS_PROJECTION — Determines whether a KPI/indicator carries a projection, using the cached g_projection_kpis collection.
• LOAD_REPORTING_CALENDAR — Loads reporting calendar data.
• LOAD_REPORTING_CALENDAR_AT — Alternate/at-parameter variant of the calendar load.
• LOAD_REPORTING_CALENDAR_VB — VB-callable calendar load.
• REFRESH_SUMMARY_MV — Refreshes an existing summary MV.
• REFRESH_SUMMARY_MV_AT — Variant of the MV refresh.

The constant MAX_ALLOWED_LEVELS is set to 8, bounding the dimensionality permitted in summary MV definitions.

Tables Accessed

The package reads metadata through APPS synonyms:

• BSC_DB_TABLES_COLS — column-level metadata for BSC database tables, used to build MV column lists and key definitions.
• BSC_DB_TABLES_RELS — relationship metadata between BSC tables, used to resolve joins for summary MV construction.
• BSC_KPI_DATA_TABLES — identifies the underlying KPI data tables whose content the summary MVs aggregate.
• PLITBLM — the standard Oracle PL/SQL table-of-VARCHAR2 helper table, used for internal array handling.

These tables supply the optimizer with the structural information needed to generate, name, refresh, and drop summary MVs and reporting key tables.

Usage Notes

BSC_BIA_WRAPPER is not intended for direct end-user invocation. It is called programmatically by the BSC metadata optimizer and by twelve other packages within the BSC/BIA codebase. The _VB procedures are the compatibility entry points; on failure they insert diagnostic rows into BSC_MESSAGE_LOGS so that upstream batch processes can continue. The non-_VB variants return status through OUT parameters and BOOLEAN results.

Typical invocation occurs during Balanced Scorecard summary MV maintenance — after KPI metadata changes, during scheduled aggregation refreshes, and when reporting calendar data must be reloaded. When troubleshooting implement_bsc_mv_vb behavior, DBAs should inspect BSC_MESSAGE_LOGS for logged errors and verify that the referenced metadata tables reflect the expected KPI and table-relationship configuration.