Search Results create_tables_spawned




Overview
Explore in AI Chat

Overview

APPS.BSC_MO_DB_PKG is a database-tier PL/SQL package in the Oracle E-Business Suite environment, owned by the APPS schema and classified as OTHER in the ETRM documentation for release 12.1.1. Its name and dependency footprint place it within the Balanced Scorecard (BSC) "Metadata Optimizer" subsystem of Oracle EBS. The Metadata Optimizer is the component that collects, stores, and tunes the statistical metadata used by the Balanced Scorecard performance-management reporting engines. BSC_MO_DB_PKG serves as the database-manipulation layer of that subsystem: it creates and manages the physical staging and summary structures that hold optimizer metadata, and it coordinates the parallel or spawned database work needed to populate and maintain those structures.

The package is documented as VALID and sits in the APPS schema alongside two subordinate bodies: the package specification and the package body, both registered as dependency subobjects. It references only the SYS STANDARD package at the inter-schema level, indicating that its substantive logic is self-contained within PL/SQL and built directly on Oracle-supplied built-ins rather than on other public EBS APIs.

Key Procedures and Functions

The ETRM metadata documents six procedures or functions for this package. Descriptions are based on the documented names; no parameter lists are asserted.

  • CREATEALLTABLES — The primary table-creation driver. It materializes the collection of optimizer metadata tables required by the Balanced Scorecard subsystem, issuing DDL against the APPS schema.
  • CREATE_TABLES_SPAWNED — A variant of the creation routine that performs table creation work within spawned sessions, allowing DDL to be distributed rather than executed serially.
  • SPAWN_CHILD_PROCESSES — The process-coordination routine. It launches child sessions or jobs that execute the distributed work driven by the table-creation and period-column routines.
  • GETPERIODCOLUMNNAME — A lookup function that returns the column name associated with a given period, allowing callers to build dynamic SQL against periodically partitioned or period-suffixed metadata tables.
  • GETSUBPERIODCOLUMNNAME — The sub-period analogue of the preceding function, resolving column names for sub-period granularity.
  • The sixth documented routine, combined with the above, completes the public surface used by the Metadata Optimizer's higher-level packages.

Tables Accessed

The documented table and view references divide into control, catalog, and diagnostic groups.

  • BSC_DB_TABLES, BSC_SYS_PERIODICITIES, BSC_TMP_BIG_IN_COND — Native BSC metadata and control tables that define which optimizer tables exist, which periodicities are configured, and which large IN-list conditions are staged for dynamic SQL construction.
  • AD_DDL, FND_CONCURRENT_QUEUES — EBS-level DDL tracking and concurrent-manager queue information, consistent with DDL creation and spawned concurrent work.
  • ALL_INDEXES, ALL_TAB_COLUMNS, USER_TAB_COLUMNS — Data dictionary views consulted to validate existing structures and to resolve column names for the period and sub-period functions.
  • DBMS_JOB, DBMS_LOCK, DBMS_SQL, PLITBLM — Oracle built-in packages used for job submission, serialization, dynamic SQL execution, and PL/SQL table manipulation.
  • V$PARAMETER — Instance parameter inspection, typically to size or gate parallel work.

Usage Notes

BSC_MO_DB_PKG is an internal, non-API package. It is not intended for direct invocation by customer code. It is referenced by three other packages — BSC_METADATA_OPTIMIZER_PKG, BSC_MO_DOC_PKG, and BSC_MO_UI_PKG — which form its normal call paths. Administrative users typically trigger the underlying work indirectly, through the Balanced Scorecard Metadata Optimizer concurrent programs or through the Scorecard setup forms that call BSC_MO_UI_PKG. Because the package issues DDL and submits jobs, invocation should be restricted to periods of low concurrent load, and the APPS schema must hold the requisite object-creation privileges. Investigators troubleshooting optimizer table creation or period-column resolution errors should trace execution from BSC_METADATA_OPTIMIZER_PKG into this package.