Search Results ddl_sql




Overview
Explore in AI Chat

Overview

The APPS.BSC_MO_LOADER_CONFIG_PKG package body is a component of the Oracle E-Business Suite Balanced Scorecard (BSC) module, specifically within the Metadata Optimizer (MO) subsystem. Its principal business function is to manage the configuration metadata that governs how data from source tables is loaded, transformed, and materialized for use by the Balanced Scorecard analytics engine. The package operates as a configuration layer that inspects the repository of BSC database tables and their relationships, determines which tables qualify as "basic" (originating directly from input tables), and applies configuration changes such as actualization rules, upload field indicators, materialized view regeneration, and origin table registration. In the context of the ddl_sql search term, the package relies on the AD_DDL table and dynamic SQL facilities to generate and execute DDL statements that recreate or restructure the underlying BSC staging and materialized view objects. This makes it a foundational utility for keeping the scorecard's physical data model synchronized with its logical metadata model across Oracle EBS 12.1.1 and 12.2.2 environments.

Key Procedures and Functions

The documented package exposes six procedures and functions, classified under the API classification OTHER:

  • CONFIGUREACTUALIZATION — Configures the actualization settings for BSC data tables, controlling how source data is transformed into actualized KPI values.
  • RECONFIGUREUPLOADFIELDSINDIC — Reconfigures upload field indicators, adjusting which columns are flagged for upload into the BSC data model.
  • RECREATEMATERIALIZEDVIEWSINDIC — Recreates or refreshes materialized views, generating the necessary DDL to rebuild aggregation structures.
  • CORRECTIONS — Applies corrective metadata adjustments to reconcile inconsistencies in table definitions or relationships.
  • INSERTORIGINTABLES — Inserts origin table definitions into the BSC metadata repository, establishing the lineage between source and derived tables.
  • ISBASICTABLE — Returns a Boolean indicating whether a given table is a base (input-origin) table, resolving the table type by inspecting the origin table chain and the bsc_db_tables / bsc_db_tables_rels relationship model.

Tables Accessed

The package reads and writes configuration metadata through APPS synonyms. The primary metadata tables are BSC_DB_TABLES (table definitions), BSC_DB_TABLES_RELS (source-to-target table relationships), BSC_DB_TABLES_COLS (column definitions), BSC_DB_CALCULATIONS (calculation rules), BSC_KPI_DATA_TABLES, and BSC_KPI_DIM_LEVELS_B. The AD_DDL table is referenced to record and manage DDL operations, while BSC_TMP_BIG_IN_COND supports temporary condition handling. DBMS_SQL and PLITBLM are Oracle-supplied dependencies used for dynamic SQL execution and PL/SQL table manipulation.

Usage Notes

This package is typically invoked during Balanced Scorecard metadata setup and maintenance, either from administrative forms, concurrent programs, or as a dependency of higher-level loader packages (it is referenced by four other packages). Because it performs DDL operations such as materialized view recreation, it should be invoked in controlled maintenance windows. Custom code should avoid direct invocation unless the caller fully understands the BSC metadata model and the sequencing requirements imposed by the origin table relationship repository.