Search Results is_totally_shared_obj




Overview
Explore in AI Chat

Overview

APPS.BSC_METADATA_OPTIMIZER_PKG is a PL/SQL package body belonging to the Oracle Balanced Scorecard (BSC) product family within Oracle E-Business Suite. Its central responsibility is to optimize the metadata layer that supports BSC KPI (key performance indicator) processing, particularly the dimension-level and KPI definitions stored in the BSC repository. The package accomplishes this by materializing filtered subsets of KPI, dimension, and dimension-level metadata into temporary summary tables that carry supporting indexes, thereby reducing the cost of repeated joins and lookups performed during scorecard expansion and shared-indicator resolution.

The package header draws on the $Header convention and was last revised around 2006 (BSCMOPTB.pls, version 120.25), which places it among the long-standing BSC infrastructure components carried forward into EBS 12.1.1 and 12.2.2. It depends heavily on the helper package BSC_MO_HELPER_PKG for DDL execution and logging, and on FND_LOG for statement-level diagnostics. The documented classification of the package is OTHER, meaning it is not exposed as a functional business API but rather exists as an internal utility for metadata maintenance.

Key Procedures and Functions

The ETRM metadata documents seven procedures and functions in this package body:

  • RUN_METADATA_OPTIMIZER — The primary entry point. It orchestrates the full optimization run, invoking the supporting routines that create temporary summary tables, populate them, and refresh the supporting structures used during KPI metadata resolution. This is the procedure most commonly referenced in operational or diagnostic searches.
  • INITMVFLAGS — Initializes package-level flags (materialized view or optimization-related state) used by later stages of the optimizer run.
  • INITRESERVEDFUNCTIONS — Initializes or populates the list of reserved functions referenced during metadata optimization, ensuring reserved internal function names are accounted for before processing begins.
  • DOCUMENTATION — A diagnostic helper that emits descriptive text about the package and its processing steps, used for logging or self-documentation purposes.
  • GETUTLFILEDIR — Retrieves the operating-system directory path used for UTL_FILE output, allowing the package to write progress or diagnostic files to a known location.
  • LOGPROGRESS — Writes incremental progress information during long-running optimizer executions, supporting the internal progress counter maintained by the package.
  • IS_TOTALLY_SHARED_OBJ — A predicate that determines whether a given object is completely shared, which influences whether metadata must be re-optimized or can be reused.

The visible source excerpt also shows internal routines such as createTmpLogTables and writeTableCounts, which drop and recreate the filtered-indicator temporary table and record row counts respectively, using AD_DDL for safe DDL execution.

Tables Accessed

The package references several tables through APPS synonyms. BSC_DB_TABLES and BSC_KPI_PROPERTIES hold KPI and database metadata that drive optimization decisions. BSC_TMP_BIG_IN_COND and BSC_TMP_OPT_UI_KPIS are temporary working tables that store intermediate KPI sets during processing. FND_LANGUAGES supplies the language context for multilingual metadata. AD_DDL is used to perform controlled DDL operations, and DBMS_SQL and UTL_FILE support dynamic SQL and file output. V$PARAMETER is queried to inspect database initialization parameters, and PLITBLM and DUAL are used for utility lookups. The temporary tables created by the package (for example, the filtered indicator table) are derived from BSC_KPI_DIM_LEVELS_B and BSC_SYS_DIM_LEVELS_B, filtering on shared KPI flags and mismatched level view names.

Usage Notes

RUN_METADATA_OPTIMIZER is not exposed as a concurrent program via an applications menu; it is typically invoked programmatically or from a maintenance script when BSC metadata performance degrades, when shared KPI definitions change, or during diagnostic investigation of KPI dimension-level resolution. Because it performs DDL (dropping and recreating temporary tables), it should be run when BSC processing is quiescent, and callers should ensure the database user has the privileges required by AD_DDL and UTL_FILE. Twelve other packages reference this package, indicating that it participates in a broader BSC metadata maintenance chain. For EBS 12.1.1 and 12.2.2, the package remains an internal APPS utility; custom code that calls it should treat it as subject to change without notice and should avoid direct dependencies on its temporary table structures.