Search Results bsc_mo_input_table_pkg




Overview
Explore in AI Chat

Overview

BSC_MO_INPUT_TABLE_PKG is a PL/SQL package owned by the APPS schema in Oracle E-Business Suite, classified under the ETRM metadata as an "OTHER" API type. It belongs to the Oracle Balanced Scorecard (BSC) product family, specifically the Metadata Optimizer (MO) subsystem. The package provides infrastructure for managing and loading the input tables used by the Metadata Optimizer, a set of EBS components that analyze, transform, and optimize the database structures supporting Balanced Scorecard performance measurement data.

The package operates in the context of the BSC_DB_* dictionary tables, which describe the physical database tables, columns, and relationships that underlie the Balanced Scorecard data model. Its primary role is to present a consolidated view of candidate input tables, determine the highest table index currently in use, and load business-layer ("B_T") table definitions from the database into PL/SQL collections for downstream processing. In ETRM 12.1.1 the package is documented as VALID with three procedures, and it is referenced by a single other package, BSC_METADATA_OPTIMIZER_PKG, confirming its role as a supporting utility within the optimizer framework rather than a standalone entry point.

Key Procedures and Functions

  • INPUTTABLES — The principal routine for assembling the set of input tables available to the Metadata Optimizer. It identifies which database tables participate as inputs, drawing on the BSC_DB_TABLES and BSC_DB_TABLES_COLS metadata that describe table and column definitions.
  • GETMAXTABLEINDEX — Returns the maximum table index value currently assigned within the optimizer's working set. This value is used to sequence and uniquely identify table entries, preventing collisions when new tables are registered or processed.
  • LOAD_B_T_TABLES_FROM_DB — Loads the business-layer table definitions from the database into PL/SQL structures. This routine bridges persistent dictionary rows and in-memory processing, enabling the Metadata Optimizer to operate on the current physical database layout.

The package also exposes package-level structures and a body, but the ETRM metadata does not document individual parameter lists; consumers should inspect the package specification in the instance for exact signatures.

Tables Accessed

  • BSC_DB_CALCULATIONS — Calculation definitions associated with the Balanced Scorecard data model; included when deriving the input table set.
  • BSC_DB_TABLES — The core registry of database tables recognized by the Metadata Optimizer; serves as the primary source for INPUTTABLES and LOAD_B_T_TABLES_FROM_DB.
  • BSC_DB_TABLES_COLS — Column-level metadata for each registered table, used to validate and construct table structures.
  • BSC_DB_TABLES_RELS — Table relationships (foreign key and join paths) needed to understand dependencies among input tables.
  • BSC_SYS_MEASURES — System measure definitions that map to the underlying tables, linking business metrics to physical storage.
  • ALL_TAB_COLUMNS — Oracle data dictionary view consulted to reconcile actual database columns against the BSC metadata repository.

Additional dependencies include DBMS_SQL for dynamic SQL execution and the PLITBLM package (PL/SQL Index Table) plus the SYS.STANDARD package, indicating use of associative array or index-by table types during table loading.

Usage Notes

This package is not typically invoked directly by end users. It is called programmatically by BSC_METADATA_OPTIMIZER_PKG, which orchestrates the optimization process. Administrators may encounter it during Balanced Scorecard setup, metadata refresh, or when concurrent programs that invoke the Metadata Optimizer are executed. Custom code should call the documented procedures only after confirming the expected collection structures and table-naming conventions, since DBMS_SQL-based dynamic execution and index-by table handling require careful context management. Because the package relies on SQL privileges granted through APPS synonyms, execution must run under a user with the appropriate BSC responsibilities and EXECUTE grants on the package.