Search Results load_input_table_initial
Overview
BSC_UPDATE_BASE_V2 is an Oracle Applications (APPS) PL/SQL package that belongs to the Oracle Balanced Scorecard (BSC) product family within Oracle E-Business Suite. Its role is to manage the calculation and refresh of the "base" fact tables that underpin Balanced Scorecard performance aggregation. The package computes base-level metrics from source data and prepares the intermediate structures that higher-level periodicities and dimensions depend upon. It is classified as an OTHER API in the ETRM repository, indicating it is an internal engine package rather than a published extension interface, and it is deployed against release 12.1.1 and 12.2.2 schemas with a VALID status.
The package sits at the foundation of the Balanced Scorecard data-refresh chain. It depends upon BSC_UPDATE_UTIL and the SYS STANDARD package, and it is referenced by BSC_UPDATE, BSC_UPDATE_DIM, BSC_UPDATE_INC, and BSC_UPDATE_SUM. This dependency graph confirms that base-table calculation must complete before dimensional, incremental, and summary level aggregations execute.
Key Procedures and Functions
The ETRM metadata documents eighteen procedures and functions. Their purposes are as follows:
- CALCULATE_BASE_TABLE and CALCULATE_BASE_TABLE_AT — compute base fact tables for the current context and for a specified point in time.
- CALC_NEW_PERIOD_INPUT_TABLE and CALC_NEW_PERIOD_BASE_TABLE — process input and base tables for newly opened periods.
- CALC_PROJECTION — generate projected values used in forward-looking scorecard calculations.
- CALC_HIGHER_PERIODICITIES — roll base-level results up into coarser periodicities.
- CREATE_GENERIC_TEMP_TABLES and CREATE_GENERIC_TEMP_TABLES_AT — build reusable temporary staging structures.
- CREATE_PROC_LOAD_TBL_MV and CREATE_PROC_LOAD_TBL_SUM_AW — create processing/load tables for materialized-view and summary-based loading.
- CREATE_TYPES_FOR_MV_LOAD — register SQL types required by the MV load process.
- GET_BASE_PROJ_TBL_NAME — return the name of the base projection table in use.
- INIT_BSC_DB_CALENDAR_TEMP and INIT_BSC_DB_CALENDAR_TEMP_PROJ — initialise calendar temporary structures, including projection calendars.
- LOAD_INPUT_TABLE_INC and LOAD_INPUT_TABLE_INITIAL — load input tables incrementally and for the initial full load.
- UPDATE_BASE_TABLE and UPDATE_BASE_TABLE_JOB — drive the base-table refresh, the latter as a scheduled job entry point.
Tables Accessed
The package reads and writes a defined set of configuration and control tables through APPS synonyms. BSC_DB_CALCULATIONS stores calculation definitions that govern how base metrics are derived. BSC_DB_TABLES holds metadata about the base tables managed by the package. BSC_SYS_DIM_LEVELS_B defines dimension level hierarchy information used when resolving aggregation paths. BSC_SYS_PERIODICITIES supplies the list of supported periodicities used by CALC_HIGHER_PERIODICITIES. In addition, the package invokes DBMS_SQL for dynamic SQL construction, DBMS_STATS for optimizer statistics gathering, and PLITBLM for index-organised table operations — all essential to the dynamic, data-driven nature of base-table generation.
Usage Notes
BSC_UPDATE_BASE_V2 is not intended for direct end-user invocation. It is called programmatically from the higher-level BSC_UPDATE package, which is typical of the Balanced Scorecard administration and refresh flows. Administrators may trigger it indirectly through documented concurrent programs that rebuild scorecard data, and UPDATE_BASE_TABLE_JOB suggests support for DBMS_SCHEDULER- or concurrent-manager-based job submission. Because it manipulates large staging and fact tables, the package should be run during controlled maintenance windows, and its DBMS_STATS calls may generate significant system statistics activity. Custom code should avoid calling individual procedures out of sequence; the intended order is initial load, incremental load, base calculation, then higher-periodicity aggregation.