DBA Data[Home] [Help]

APPS.BSC_UPDATE SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 19

	x_loaded_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
	x_num_loaded_tables IN NUMBER,
        x_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
        x_num_dim_tables IN NUMBER
	) RETURN BOOLEAN IS

    TYPE t_cursor IS REF CURSOR;
Line: 28

        SELECT dp.level_table_name
        FROM bsc_sys_dim_levels_b d, bsc_sys_dim_levels_b dp, bsc_sys_dim_level_rels r
        WHERE d.dim_level_id = r.dim_level_id AND
              r.parent_dim_level_id = dp.dim_level_id AND
              DECODE(r.relation_type, 2, r.relation_col, d.level_table_name) = p_dim_table;
Line: 43

        IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_parent_table,
                                                         x_dim_tables,
                                                         x_num_dim_tables) THEN

            -- The parent table was or is going to be loaded in this process
            -- So, we need to check if this parent table is already loaded or not
            -- If it is not already loaded the dimension table cannot be
            -- loaded right now.

            IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_parent_table,
	    						         x_loaded_tables,
							         x_num_loaded_tables) THEN
                h_ret := FALSE;
Line: 70

                        x_source => 'BSC_UPDATE.Can_Load_Dim_Table');
Line: 81

	x_calculated_sys_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
	x_num_calculated_sys_tables IN NUMBER,
        x_system_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
        x_num_system_tables IN NUMBER
	) RETURN BOOLEAN IS

    TYPE t_cursor IS REF CURSOR;
Line: 91

    c_origin_tables_sql VARCHAR2(2000) := 'SELECT source_table_name'||
                                          ' FROM bsc_db_tables_rels'||
                                          ' WHERE table_name = :1';
Line: 96

        SELECT source_table_name
        FROM bsc_db_tables_rels
        WHERE table_name = p_table_name;
Line: 110

        IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_origin_table,
                                                         x_system_tables,
                                                         x_num_system_tables) THEN

            -- The origin table was or is going to be calculated in this process
            -- So, we need to check if this origin table is already calculated or not
            -- If it is not already calculated the the system table cannot be
            -- calculated right now.

            IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_origin_table,
	    						         x_calculated_sys_tables,
							         x_num_calculated_sys_tables) THEN
                h_ret := FALSE;
Line: 137

                        x_source => 'BSC_UPDATE.Can_Calculate_Sys_Table');
Line: 155

    h_base_table BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 158

    h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 175

    DELETE FROM bsc_db_calculations
    WHERE table_name = p_base_table AND calculation_type = c_calculation_type;
Line: 179

    h_base_table_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(p_base_table);
Line: 187

    IF NOT Insert_Affected_Tables(h_base_table, 1, h_system_tables, h_num_system_tables) THEN
        RAISE e_unexpected_error;
Line: 199

        h_sql := 'SELECT DISTINCT periodicity_id '||
                 'FROM bsc_db_tables '||
                 'WHERE '||h_lst_where;
Line: 207

                INSERT INTO bsc_db_calculations (table_name, calculation_type, parameter1)
                VALUES (p_base_table, c_calculation_type, h_periodicity_id);
Line: 223

        x_error_message := BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR');
Line: 251

                        x_source => 'BSC_UPDATE.Configure_Periodicity_Calc_VB',
                        x_mode => 'I');
Line: 257

                        x_source => 'BSC_UPDATE.Configure_Periodicity_Calc_VB',
                        x_mode => 'I');
Line: 278

        SELECT table_name, column_name
        FROM bsc_db_tables_cols
        WHERE table_name IN (
                SELECT table_name
                FROM bsc_db_tables_rels
                WHERE source_table_name IN (
                        SELECT table_name
                        FROM bsc_db_tables
                        WHERE table_type = p_table_type
                      )
              ) AND
              column_type = p_column_type AND
              column_name IN (
                SELECT level_pk_col
                FROM bsc_kpi_dim_levels_b
                WHERE indicator IN (
                        SELECT indicator
                        FROM bsc_kpis_b
                        WHERE indicator_type = p_indic_type AND config_type = p_config_type
                       ) AND
                      dim_level_index = p_dim_level_index
              );
Line: 307

    DELETE FROM bsc_db_calculations WHERE calculation_type = h_calculation_type;
Line: 317

        INSERT INTO bsc_db_calculations (table_name, calculation_type, parameter1)
        VALUES(h_base_table, h_calculation_type, h_pk_level_subaccount);
Line: 352

                        x_source => 'BSC_UPDATE.Configure_Profit_Calc_VB',
                        x_mode => 'I');
Line: 358

                        x_source => 'BSC_UPDATE.Configure_Profit_Calc_VB',
                        x_mode => 'I');
Line: 366

| PROCEDURE Execute_Update_Process
+============================================================================*/
PROCEDURE Execute_Update_Process (
    x_process_id IN NUMBER,
    x_process_name IN VARCHAR2,
    x_parameter_1 IN VARCHAR2
    ) IS

    e_update_error EXCEPTION;
Line: 386

    h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 389

    h_indicators BSC_UPDATE_UTIL.t_array_of_number;
Line: 392

    h_calendars BSC_UPDATE_UTIL.t_array_of_number;
Line: 395

    h_edw_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 405

        SELECT input_table_name
        FROM bsc_db_loader_control
        WHERE process_id = p_process_id;
Line: 447

        RAISE e_update_error;
Line: 461

    BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
Line: 464

    BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
Line: 466

    IF BSC_UPDATE_UTIL.is_parallel THEN
        COMMIT;
Line: 469

        BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
Line: 478

        RAISE e_update_error;
Line: 489

        IF NOT BSC_UPDATE_LOG.Init_Log_File(h_log_file_name) THEN
            RAISE e_update_error;
Line: 495

    BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'PROCESS_ID')||
                                  BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                  ' '||TO_CHAR(g_process_id), BSC_UPDATE_LOG.LOG);
Line: 499

    BSC_UPDATE_LOG.Write_Line_Log(USERENV('SESSIONID'), BSC_UPDATE_LOG.LOG);
Line: 503

       RAISE e_update_error;
Line: 545

            g_num_indicators := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_parameter_1,
                                                                       g_indicators,
                                                                       ',');
Line: 572

       IF NOT BSC_UPDATE_LOCK.Lock_Import_Dbi_Plans THEN
           RAISE e_could_not_get_lock;
Line: 577

       IF NOT BSC_UPDATE_DIM.Import_Dbi_Plans_AT(h_error_message) THEN
           RAISE e_import_dbi_plans;
Line: 588

               IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(g_indicators(h_i)) = 2 THEN
                   h_load_type_into_aw := TRUE;
Line: 595

           IF BSC_UPDATE_UTIL.Exists_AW_Kpi THEN
               h_load_type_into_aw := TRUE;
Line: 600

           BSC_UPDATE_DIM.Load_Type_Into_AW_AT;
Line: 617

        IF NOT BSC_UPDATE_LOCK.Lock_Temp_Tables('DIMENSION') THEN
            RAISE e_could_not_get_lock;
Line: 622

        IF NOT BSC_UPDATE_DIM.Create_AW_Dim_Temp_Tables_AT THEN
            RAISE e_update_error;
Line: 630

            RAISE e_update_error;
Line: 636

                RAISE e_update_error;
Line: 642

                RAISE e_update_error;
Line: 658

    IF NOT BSC_UPDATE_LOCK.Lock_Incremental_Indicators THEN
        RAISE e_could_not_get_lock;
Line: 663

    IF NOT BSC_UPDATE_INC.Do_Incremental_AT() THEN
        RAISE e_update_error;
Line: 676

            RAISE e_update_error;
Line: 684

        IF NOT BSC_UPDATE_LOCK.Lock_Indicators(h_input_tables, h_num_input_tables) THEN
            RAISE e_could_not_get_lock;
Line: 693

                RAISE e_update_error;
Line: 698

                RAISE e_update_error;
Line: 704

            IF NOT BSC_UPDATE.Process_Input_Tables_AT(h_input_tables, h_num_input_tables, 0) THEN
                RAISE e_update_error;
Line: 722

        h_num_calendars := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_parameter_1,
                                                                  h_calendars,
                                                                  ',');
Line: 729

        IF NOT BSC_UPDATE_LOCK.Lock_Indicators_by_Calendar(h_calendars, h_num_calendars) THEN
            RAISE e_could_not_get_lock;
Line: 739

                RAISE e_update_error;
Line: 746

    ELSIF x_process_name = PC_DELETE_KPI_DATA_PROCESS THEN
        -- Run delete kpis data process

        h_num_indicators := 0;
Line: 752

        h_sql := 'SELECT input_table_name'||
                 ' FROM bsc_db_loader_control'||
                 ' WHERE process_id = :1';
Line: 769

        IF NOT BSC_UPDATE_LOCK.Lock_Indicators_To_Delete(h_indicators, h_num_indicators) THEN
            RAISE e_could_not_get_lock;
Line: 778

        IF NOT BSC_UPDATE_INC.Purge_Indicators_Data_AT(h_indicators, h_num_indicators, g_keep_input_table_data) THEN
            RAISE e_update_error;
Line: 798

        RAISE e_update_error;
Line: 804

    BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                  BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                  ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 807

    BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 823

    IF BSC_UPDATE_UTIL.is_parallel THEN
        h_sql := 'alter session disable parallel dml';
Line: 825

        BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
Line: 833

	-- Delete records in the temporal table used for big 'in' conditions
        BSC_APPS.Init_Big_In_Cond_Table;
Line: 836

        BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_NO_PENDING_PROCESS'),
                        x_source => 'BSC_UPDATE.Execute_Update_Process',
                        x_mode => 'I');
Line: 841

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 843

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 846

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 849

	-- Delete records in the temporal table used for big 'in' conditions
        BSC_APPS.Init_Big_In_Cond_Table;
Line: 853

        BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_METADATA_PEND_CHANGES'),
                        x_source => 'BSC_UPDATE.Execute_Update_Process',
                        x_mode => 'I');
Line: 858

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 865

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 868

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 870

   WHEN e_update_error THEN
        ROLLBACK;
Line: 873

	-- Delete records in the temporal table used for big 'in' conditions
        BSC_APPS.Init_Big_In_Cond_Table;
Line: 877

        BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED'),
                        x_source => 'BSC_UPDATE.Execute_Update_Process',
                        x_mode => 'I');
Line: 882

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 889

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 892

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 898

 	-- Delete records in the temporal table used for big 'in' conditions
        BSC_APPS.Init_Big_In_Cond_Table;
Line: 903

                        x_source => 'BSC_UPDATE.Execute_Update_Process',
                        x_mode => 'I');
Line: 907

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 914

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 917

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 923

	-- Delete records in the temporal table used for big 'in' conditions
        BSC_APPS.Init_Big_In_Cond_Table;
Line: 927

        BSC_MESSAGE.Add(x_message => 'BSC_UPDATE_DIM.Import_Dbi_Plans: '||h_error_message,
                        x_source => 'BSC_UPDATE.Execute_Update_Process',
                        x_mode => 'I');
Line: 932

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 939

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 942

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 948

	-- Delete records in the temporal table used for big 'in' conditions
        BSC_APPS.Init_Big_In_Cond_Table;
Line: 955

                        x_source => 'BSC_UPDATE.Execute_Update_Process',
                        x_mode => 'I');
Line: 959

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 966

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 969

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 974

	-- Delete records in the temporal table used for big 'in' conditions
        BSC_APPS.Init_Big_In_Cond_Table;
Line: 979

                        x_source => 'BSC_UPDATE.Execute_Update_Process',
                        x_mode => 'I');
Line: 983

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 990

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 993

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 995

END Execute_Update_Process;
Line: 1017

        SELECT t.table_name, t.periodicity_id, t.current_period, p.yearly_flag
        FROM bsc_db_tables t, bsc_sys_periodicities p
        WHERE t.periodicity_id = p.periodicity_id AND
              t.table_type = p_table_type AND p.calendar_id = p_calendar_id;
Line: 1024

        SELECT t.table_name, t.periodicity_id, t.current_period, p.yearly_flag
        FROM bsc_db_tables t, bsc_sys_periodicities p
        WHERE t.periodicity_id = p.periodicity_id AND
              t.table_type = p_table_type1 AND t.generation_type <> p_gen_type AND
              p.calendar_id = p_calendar_id AND
              t.table_name IN (
                  SELECT r.table_name
                  FROM bsc_db_tables_rels r, bsc_db_tables b
                  WHERE r.source_table_name = b.table_name and b.table_type = p_table_type0
              );
Line: 1040

        SELECT t.table_name, t.periodicity_id, t.current_period, p.yearly_flag
        FROM bsc_db_tables t, bsc_sys_periodicities_vl p
        WHERE t.periodicity_id = p.periodicity_id AND
              t.table_type = p_table_type1 AND t.generation_type <> p_gen_type AND
              p.calendar_id = p_calendar_id AND
              NOT (t.table_name IN (
                  SELECT r.table_name
                  FROM bsc_db_tables_rels r, bsc_db_tables b
                  WHERE r.source_table_name = b.table_name and b.table_type = p_table_type0
              ));
Line: 1053

        SELECT DISTINCT k.projection_data
        FROM bsc_kpi_data_tables k, bsc_sys_periodicities p
        WHERE k.periodicity_id = p.periodicity_id AND
              p.calendar_id = p_calendar_id AND
              projection_data IS NOT NULL;
Line: 1061

        SELECT c.parameter1, p.yearly_flag
        FROM bsc_db_calculations c, bsc_sys_periodicities p
        WHERE c.table_name = p_table_name AND c.calculation_type = p_calc_type AND
              c.parameter1 = p.periodicity_id;
Line: 1078

        SELECT indicator
        FROM bsc_kpis_vl
        WHERE calendar_id = p_calendar_id;
Line: 1091

    l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
Line: 1098

    h_base_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 1109

    h_aw_indicators BSC_UPDATE_UTIL.t_array_of_number;
Line: 1115

    h_aw_base_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 1138

    IF NOT BSC_UPDATE_LOCK.Lock_Calendar_Change(x_calendar_id) THEN
        RAISE e_could_not_get_lock;
Line: 1144

    h_calendar_name := BSC_UPDATE_UTIL.Get_Calendar_Name(x_calendar_id);
Line: 1146

    BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_YEARCHANGE_PROCESS')||' ('||
                                  h_calendar_name||')', BSC_UPDATE_LOG.OUTPUT);
Line: 1150

    h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(x_calendar_id);
Line: 1154

    h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
Line: 1159

        SELECT count(year)
        INTO h_count
        FROM bsc_db_calendar
        WHERE calendar_id = x_calendar_id AND year = h_new_fiscal_year;
Line: 1166

            h_message := BSC_UPDATE_UTIL.Get_Message('BSC_DBI_YEAR_NOT_AVAILABLE');
Line: 1167

            h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'YEAR', TO_CHAR(h_new_fiscal_year));
Line: 1168

            h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'CALENDAR_NAME', h_calendar_name);
Line: 1169

            BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 1187

    IF NOT BSC_UPDATE_UTIL.Set_Calendar_Fiscal_Year(x_calendar_id, (h_current_fy + 1)) THEN
        RAISE e_unexpected_error;
Line: 1197

        IF NOT BSC_UPDATE_UTIL.Init_Calendar_Tables(x_calendar_id) THEN
            RAISE e_unexpected_error;
Line: 1208

        IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(h_indicator) = 2 THEN
            h_num_aw_indicators := h_num_aw_indicators + 1;
Line: 1215

        UPDATE
            bsc_kpi_periodicities
        SET
            current_period = h_current_fy + 1
        WHERE
            indicator = h_indicator AND
            periodicity_id  IN (
                SELECT
                    periodicity_id
                FROM
                    bsc_sys_periodicities
                WHERE
                    calendar_id = x_calendar_id AND
                    yearly_flag = 1);
Line: 1230

        UPDATE
            bsc_kpi_periodicities
        SET
            current_period = 1
        WHERE
            indicator = h_indicator AND
            periodicity_id IN (
                SELECT
                    periodicity_id
                FROM
                    bsc_sys_periodicities
                WHERE
                    calendar_id = x_calendar_id AND
                    yearly_flag = 0);
Line: 1247

        UPDATE bsc_sys_kpi_colors
        SET kpi_color = BSC_UPDATE_COLOR.GRAY,
            actual_data = NULL,
            budget_data = NULL
        WHERE indicator = h_indicator;
Line: 1253

        UPDATE bsc_sys_objective_colors
        SET obj_color = BSC_UPDATE_COLOR.GRAY
        WHERE indicator = h_indicator;
Line: 1259

        IF NOT BSC_UPDATE_UTIL.Update_Kpi_Period_Name(h_indicator) THEN
            RAISE e_unexpected_error;
Line: 1264

        UPDATE
            bsc_kpi_defaults_b
        SET
            last_update_date = SYSDATE
        WHERE
            indicator = h_indicator;
Line: 1295

            h_aw_flag := BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_table_info.table_name);
Line: 1313

            h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_table_info.table_name);
Line: 1315

                BSC_UPDATE_UTIL.Truncate_Table_AT(h_proj_tbl_name);
Line: 1319

                    h_sql := 'DELETE FROM '||h_table_info.table_name||
                             ' WHERE YEAR > :1 AND TYPE = 0';
Line: 1325

                        BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
Line: 1328

                        BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
Line: 1332

                    h_sql := 'DELETE FROM '||h_table_info.table_name||
                             ' WHERE YEAR = :1 AND PERIOD > :2 AND TYPE = 0';
Line: 1339

                        BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
Line: 1343

                        BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
Line: 1357

                            h_sql := 'DELETE FROM '||h_table_info.table_name||
                                     ' WHERE YEAR > :1 AND TYPE = 0 AND PERIODICITY_ID = :2';
Line: 1361

                            BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
Line: 1365

                            h_current_period := BSC_UPDATE_UTIL.Get_Period_Other_Periodicity(
                                                    h_periodicity_id,
                                                    x_calendar_id,
                                                    h_yearly_flag,
                                                    h_current_fy,
                                                    h_table_info.periodicity_id,
                                                    h_table_info.current_period
                                                );
Line: 1375

                            h_sql := 'DELETE FROM '||h_table_info.table_name||
                                     ' WHERE YEAR = :1 AND PERIOD > :2 AND TYPE = 0 AND PERIODICITY_ID = :3';
Line: 1380

                            BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
Line: 1389

        l_bind_vars_values.delete;
Line: 1393

            IF BSC_UPDATE_UTIL.Table_Exists(h_table_info.table_name) THEN
                --ENH_B_TABLES_PERF: In the new strategy the base table may have a projection table
                -- In this case we need to truncate the projection table and we do not need to touch
                -- the base table since it contains only actuals.
                h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_table_info.table_name);
Line: 1399

                    BSC_UPDATE_UTIL.Truncate_Table_AT(h_proj_tbl_name);
Line: 1403

                        h_sql := 'DELETE FROM '||h_table_info.table_name||
                                 ' WHERE YEAR > :1 AND TYPE = 0';
Line: 1406

                        BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
Line: 1409

                        h_sql := 'DELETE FROM '||h_table_info.table_name||
                                 ' WHERE YEAR = :1 AND PERIOD > :2 AND TYPE = 0';
Line: 1413

                        BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
Line: 1424

    UPDATE
        bsc_db_tables
    SET
        current_period = h_current_fy + 1,
        current_subperiod = 0
    WHERE
        table_type <> 2 AND
        periodicity_id IN (
            SELECT
                periodicity_id
            FROM
                bsc_sys_periodicities
            WHERE
                calendar_id = x_calendar_id AND
                yearly_flag = 1);
Line: 1441

    UPDATE
        bsc_db_tables
    SET
        current_period = 1,
        current_subperiod = 0
    WHERE
        table_type <> 2 AND
        periodicity_id IN (
            SELECT
                periodicity_id
            FROM
                bsc_sys_periodicities
            WHERE
                calendar_id = x_calendar_id AND
                yearly_flag = 0);
Line: 1483

        l_bind_vars_values.delete;
Line: 1490

            IF NOT BSC_UPDATE_LOCK.Lock_Table(h_table_name) THEN
                RAISE e_could_not_get_lock;
Line: 1495

            BSC_UPDATE_UTIL.Truncate_Table_AT(h_table_name);
Line: 1503

        IF NOT BSC_UPDATE.Refresh_System_MVs(h_base_tables, h_num_base_tables) THEN
            RAISE e_unexpected_error;
Line: 1514

        IF NOT BSC_UPDATE_LOCK.Lock_Table(h_aw_base_tables(h_i)) THEN
            RAISE e_could_not_get_lock;
Line: 1518

        h_aw_table_name := BSC_UPDATE_BASE.Get_Base_AW_Table_Name(h_aw_base_tables(h_i));
Line: 1521

        BSC_UPDATE_UTIL.Truncate_Table_AT(h_aw_table_name);
Line: 1529

        IF BSC_UPDATE_UTIL.Is_Kpi_In_Production(h_aw_indicators(h_i)) THEN
            --LOCKING: Lock the objects required to refresh the AW indicator cubes
            IF NOT BSC_UPDATE_LOCK.Lock_Refresh_AW_Indicator(h_aw_indicators(h_i)) THEN
                RAISE e_could_not_get_lock;
Line: 1536

            BSC_UPDATE_SUM.Refresh_AW_Kpi_AT(h_aw_indicators(h_i));
Line: 1546

    IF NOT BSC_UPDATE_LOCK.Lock_Update_Date THEN
        RAISE e_could_not_get_lock;
Line: 1550

    IF NOT BSC_UPDATE_UTIL.Write_Init_Variable_Value('UPDATE_DATE', TO_CHAR(SYSDATE, 'DD/MM/YYYY')) THEN
        RAISE e_unexpected_error;
Line: 1556

    BSC_UPDATE_UTIL.Update_System_Time_Stamp;
Line: 1566

        IF NOT BSC_UPDATE_LOCK.Lock_Calendar(x_calendar_id) THEN
            RAISE e_could_not_get_lock;
Line: 1577

        BSC_UPDATE_UTIL.Load_Calendar_Into_AW_AT(x_calendar_id);
Line: 1583

    BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_YEARCHANGE_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 1590

        BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_YEARCHANGE_FAILED'),
                         x_source => 'BSC_UPDATE.Execute_Year_Change_Process');
Line: 1599

                         x_source => 'BSC_UPDATE.Execute_Year_Change_Process');
Line: 1607

                         x_source => 'BSC_UPDATE.Execute_Year_Change_Process');
Line: 1614

                         x_source => 'BSC_UPDATE.Execute_Year_Change_Process');
Line: 1646

    c_prototype_sql VARCHAR2(2000) := 'SELECT COUNT(prototype_flag)'||
                                      ' FROM bsc_kpis_vl'||
                                      ' WHERE prototype_flag IN (1, 2, 3, 4, 5)';
Line: 1663

    SELECT COUNT(prototype_flag)
    INTO h_count
    FROM bsc_kpis_vl
    WHERE prototype_flag IN (1, 2, 3, 4, 5);
Line: 1677

                        x_source => 'BSC_UPDATE.Exists_Prototype_Indicators');
Line: 1691

    UPDATE
        bsc_db_loader_control
    SET
        last_stage_flag = 0
    WHERE
        input_table_name = x_input_table;
Line: 1698

    UPDATE
        bsc_db_loader_control
    SET
        last_stage_flag = 1
    WHERE
        input_table_name = x_input_table AND
        process_id = g_process_id;
Line: 1711

                        x_source => 'BSC_UPDATE.Flag_Last_Stage_Input_Table');
Line: 1729

    c_base_table_sql VARCHAR2(2000) := 'SELECT table_name'||
                                       ' FROM bsc_db_tables_rels'||
                                       ' WHERE UPPER(source_table_name) = UPPER(:1) AND relation_type = :2';
Line: 1745

        SELECT table_name
        INTO x_base_table
        FROM bsc_db_tables_rels
        WHERE UPPER(source_table_name) = UPPER(x_input_table) AND relation_type = 0;
Line: 1759

                        x_source => 'BSC_UPDATE.Get_Base_Table_Of_Input_Table');
Line: 1769

	x_edw_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
        x_num_edw_dim_tables IN OUT NOCOPY NUMBER
	) RETURN BOOLEAN IS

    TYPE t_cursor IS REF CURSOR;
Line: 1777

    c_edw_dimensions_sql VARCHAR2(2000) := 'SELECT level_table_name'||
                                           ' FROM bsc_sys_dim_levels_b'||
                                           ' WHERE NVL(edw_flag, 0) = :1 AND'||
                                           ' level_pk_col IN (SELECT column_name'||
                                           ' FROM bsc_db_tables_cols c, bsc_db_loader_control p'||
                                           ' WHERE c.table_name = p.input_table_name AND'||
                                           ' c.column_type = :2 AND p.process_id = :3 AND p.status = :4)';
Line: 1786

        SELECT level_table_name
        FROM bsc_sys_dim_levels_b
        WHERE NVL(edw_flag, 0) = p_edw_flag AND
              level_pk_col IN (
                  SELECT column_name
                  FROM bsc_db_tables_cols c, bsc_db_loader_control p
                  WHERE c.table_name = p.input_table_name AND
                        c.column_type = p_column_type AND p.process_id = p_process_id AND p.status = p_status);
Line: 1819

                        x_source => 'BSC_UPDATE.Get_EDW_Dims_In_Input_Tables');
Line: 1828

	x_base_tables_to_color IN BSC_UPDATE_UTIL.t_array_of_varchar2,
        x_num_base_tables_to_color IN NUMBER,
        x_color_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
        x_num_color_indicators IN OUT NOCOPY NUMBER
        ) RETURN BOOLEAN IS

    e_unexpected_error EXCEPTION;
Line: 1836

    h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 1857

    IF NOT Insert_Affected_Tables(x_base_tables_to_color,
                                  x_num_base_tables_to_color,
                                  h_system_tables,
                                  h_num_system_tables) THEN
        RAISE e_unexpected_error;
Line: 1873

    h_sql := 'SELECT DISTINCT indicator '||
             'FROM bsc_kpi_data_tables '||
             'WHERE '||h_lst_where;
Line: 1890

        BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_COLORKPILIST_FAILED'),
                        x_source => 'BSC_UPDATE.Get_Indicators_To_Color');
Line: 1896

                        x_source => 'BSC_UPDATE.Get_Indicators_To_Color');
Line: 1914

    c_last_stage_sql VARCHAR2(2000) := 'SELECT stage'||
                                       ' FROM bsc_db_loader_control'||
                                       ' WHERE input_table_name = :1 AND last_stage_flag = :2';
Line: 1929

        SELECT stage
        INTO x_last_stage
        FROM bsc_db_loader_control
        WHERE input_table_name = x_input_table AND last_stage_flag = 1;
Line: 1942

                        x_source => 'BSC_UPDATE.Get_Last_Stage_Input_Table');
Line: 1960

    c_process_status_sql VARCHAR2(2000) := 'SELECT status'||
                                           ' FROM bsc_db_process_control'||
                                           ' WHERE process_id = :1';
Line: 1981

       SELECT status
       INTO h_status
       FROM bsc_db_process_control
       WHERE process_id = x_process_id;
Line: 2006

    SELECT BSC_DB_PROCESS_ID_S.NEXTVAL
    INTO h_process_id
    FROM DUAL;
Line: 2010

    INSERT INTO BSC_DB_PROCESS_CONTROL (
        PROCESS_ID,
        PROCESS_NAME,
        CREATED_BY,
        CREATION_DATE,
        LAST_UPDATED_BY,
        LAST_UPDATE_DATE,
        LAST_UPDATE_LOGIN,
        STATUS,
        DESCRIPTION
    )
    SELECT
        h_process_id,
        PROCESS_NAME,
        g_user_id,
        SYSDATE,
        g_user_id,
        SYSDATE,
        g_session_id,
        PC_PENDING_STATUS,
        DESCRIPTION
    FROM
        BSC_DB_PROCESS_CONTROL
    WHERE
        PROCESS_ID = x_process_id;
Line: 2041

        INSERT INTO BSC_DB_LOADER_CONTROL (
            PROCESS_ID,
            INPUT_TABLE_NAME,
            STATUS,
            ERROR_CODE,
            STAGE,
            LAST_STAGE_FLAG
        )
        SELECT
            h_process_id,
            INPUT_TABLE_NAME,
            LC_PENDING_STATUS,
            NULL,
            LC_PENDING_STAGE,
            0
        FROM
            BSC_DB_LOADER_CONTROL
        WHERE
            PROCESS_ID = x_process_id AND
            INPUT_TABLE_NAME IN (SELECT TABLE_NAME FROM BSC_DB_TABLES WHERE TABLE_TYPE = 0);
Line: 2066

        INSERT INTO BSC_DB_LOADER_CONTROL (
            PROCESS_ID,
            INPUT_TABLE_NAME,
            STATUS,
            ERROR_CODE,
            STAGE,
            LAST_STAGE_FLAG
        )
        SELECT
            h_process_id,
            INPUT_TABLE_NAME,
            LC_PENDING_STATUS,
            NULL,
            LC_PENDING_STAGE,
            0
        FROM
            BSC_DB_LOADER_CONTROL
        WHERE
            PROCESS_ID = x_process_id AND
            INPUT_TABLE_NAME IN (SELECT TABLE_NAME FROM BSC_DB_TABLES WHERE TABLE_TYPE = 2);
Line: 2095

                        x_source => 'BSC_UPDATE.Get_Process_Id');
Line: 2105

	x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
        x_num_input_tables IN OUT NOCOPY NUMBER,
        x_status IN VARCHAR2
	) RETURN BOOLEAN IS

    TYPE t_cursor IS REF CURSOR;
Line: 2114

    c_input_tables_sql VARCHAR2(2000) := 'SELECT input_table_name'||
                                         ' FROM bsc_db_loader_control'||
                                         ' WHERE process_id = :1 AND status = :2';
Line: 2119

        SELECT input_table_name
        FROM bsc_db_loader_control
        WHERE process_id = p_process_id AND status = p_status;
Line: 2143

                        x_source => 'BSC_UPDATE.Get_Process_Input_Tables');
Line: 2167

                        x_source => 'BSC_UPDATE.Init_Env_Values');
Line: 2177

	x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
	x_num_input_tables IN NUMBER
	) RETURN BOOLEAN IS

    e_unexpected_error EXCEPTION;
Line: 2199

        IF NOT BSC_UPDATE_UTIL.Get_Input_Table_Source(h_input_table, h_source_type, h_source_name) THEN
            RAISE e_unexpected_error;
Line: 2206

            h_message := BSC_UPDATE_UTIL.Get_Message('BSC_IMPORTING_TO_ITABLE');
Line: 2207

            h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'INPUT_TABLE', h_input_table);
Line: 2208

            BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 2210

            h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SOURCE')||
                         BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||h_source_name;
Line: 2212

            BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 2215

            IF NOT BSC_UPDATE_LOCK.Lock_Import_ITable(h_input_table) THEN
                RAISE e_could_not_get_lock;
Line: 2236

                         x_source => 'BSC_UPDATE.Import_ITables_From_DBSrc');
Line: 2240

        BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_IMPORT_ITABLES_FAILED'),
                         x_source => 'BSC_UPDATE.Import_ITables_From_DBSrc');
Line: 2246

                         x_source => 'BSC_UPDATE.Import_ITables_From_DBSrc');
Line: 2256

	x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
	x_num_input_tables IN NUMBER
	) RETURN BOOLEAN IS
PRAGMA AUTONOMOUS_TRANSACTION;
Line: 2284

    c_status_sql VARCHAR2(2000) := 'SELECT status'||
                                   ' FROM user_objects'||
                                   ' WHERE object_name = :1 AND object_type = :2';
Line: 2290

        SELECT status
        FROM user_objects
        WHERE object_name = p_object_name AND object_type = p_object_type;
Line: 2304

    h_sql := 'DELETE FROM '||x_input_table;
Line: 2305

    BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
Line: 2319

                BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
Line: 2322

                IF NOT Update_Stage_Input_Table(x_input_table, LC_UPLOADED_STAGE) THEN
                    RAISE e_unexpected_error;
Line: 2329

                    IF NOT Update_Status_Input_Table(x_input_table, LC_ERROR_STATUS, LC_UPLOAD_SP_EXECUTION_ERR) THEN
                        RAISE e_unexpected_error;
Line: 2333

                    h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
                                 BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
                                 BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_FAILED');
Line: 2336

                    BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 2337

                    BSC_UPDATE_LOG.Write_Line_Log(SQLERRM, BSC_UPDATE_LOG.OUTPUT);
Line: 2342

            IF NOT Update_Status_Input_Table(x_input_table, LC_ERROR_STATUS, LC_UPLOAD_SP_INVALID_ERR) THEN
                RAISE e_unexpected_error;
Line: 2346

            h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
                         BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
                         BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_IS_INVALID');
Line: 2349

            BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 2353

        IF NOT Update_Status_Input_Table(x_input_table, LC_ERROR_STATUS, LC_UPLOAD_SP_NOT_FOUND_ERR) THEN
            RAISE e_unexpected_error;
Line: 2357

        h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
                     BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
                     BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_NOT_FOUND');
Line: 2360

        BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 2370

        BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_IMPORT_ITABLES_FAILED'),
                         x_source => 'BSC_UPDATE.Import_ITable_StoredProc');
Line: 2376

                         x_source => 'BSC_UPDATE.Import_ITable_StoredProc');
Line: 2399

| FUNCTION Insert_Affected_Tables
+============================================================================*/
FUNCTION Insert_Affected_Tables (
	x_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
        x_num_tables IN NUMBER,
        x_affected_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
        x_num_affected_tables IN OUT NOCOPY NUMBER
	) RETURN BOOLEAN IS

    e_unexpected_error EXCEPTION;
Line: 2414

        SELECT table_name
        FROM bsc_db_tables_rels
        WHERE source_table_name = p_source_table_name;
Line: 2418

    h_new_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 2432

        h_sql := 'SELECT table_name'||
                 ' FROM bsc_db_tables_rels'||
                 ' WHERE source_table_name = :1';
Line: 2440

            IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_table_name, x_affected_tables, x_num_affected_tables) THEN
                x_num_affected_tables := x_num_affected_tables + 1;
Line: 2455

        IF NOT Insert_Affected_Tables(h_new_tables, h_num_new_tables, x_affected_tables, x_num_affected_tables) THEN
            RAISE e_unexpected_error;
Line: 2464

        BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_TABLES_INSERT_FAILED'),
                        x_source => 'BSC_UPDATE.Insert_Affected_Tables');
Line: 2470

                        x_source => 'BSC_UPDATE.Insert_Affected_Tables');
Line: 2473

END Insert_Affected_Tables;
Line: 2480

	x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
	x_num_input_tables IN NUMBER
	) RETURN BOOLEAN IS

    e_unexpected_error EXCEPTION;
Line: 2488

    h_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 2491

    h_loaded_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 2509

    h_input_tables_err_status BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 2513

        SELECT input_table_name
        FROM bsc_db_loader_control
        WHERE process_id = g_process_id AND status = LC_ERROR_STATUS;
Line: 2522

    BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPD_PROCESS'), BSC_UPDATE_LOG.OUTPUT);
Line: 2535

    IF NOT Update_Status_All_Input_Tables(LC_PENDING_STATUS, LC_RUNNING_STATUS, NULL) THEN
        RAISE e_unexpected_error;
Line: 2543

        h_dim_tables(h_i) := BSC_UPDATE_DIM.Get_Dim_Table_of_Input_Table(x_input_tables(h_i));
Line: 2557

            IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_dim_table,
                                                                 h_loaded_tables,
                                                                 h_num_loaded_tables) THEN
                -- The table has not been calculated yet

                -- Check if the table can be calculated now
                h_b := Can_Load_Dim_Table(h_dim_table,
                                          h_loaded_tables,
                                          h_num_loaded_tables,
                                          h_dim_tables,
                                          h_num_dim_tables);
Line: 2581

                    IF NOT BSC_UPDATE_LOCK.Lock_Load_Dimension_Table(h_dim_table, h_input_table) THEN
                        RAISE e_could_not_get_lock;
Line: 2588

                    IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_input_table,
                                                                         h_input_tables_err_status,
                                                                         h_num_input_tables_err_status) THEN
                        -- Input table is ok, there was no error before loading it from stored procedure

                        -- Know if the input table is empty or not.
                        h_table_has_any_row := BSC_UPDATE_UTIL.Table_Has_Any_Row(h_input_table);
Line: 2604

                            h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDATION');
Line: 2605

                            h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
Line: 2606

                            BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 2608

                            h_table_is_valid := BSC_UPDATE_DIM.Validate_Input_Table_AT(h_input_table, h_dim_table);
Line: 2616

                                h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDCONF');
Line: 2617

                                h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
Line: 2618

                                BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 2621

                                IF NOT Update_Stage_Input_Table(h_input_table, LC_VALIDATED_STAGE) THEN
                                    RAISE e_unexpected_error;
Line: 2626

                                BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_UPDATING_DIM_TABLE')||
                                                              ' '||h_dim_table, BSC_UPDATE_LOG.OUTPUT);
Line: 2630

                                IF NOT BSC_UPDATE_DIM.Load_Dim_Table_AT(h_dim_table, h_input_table) THEN
                                    RAISE e_unexpected_error;
Line: 2638

                                IF NOT Update_Stage_Input_Table(h_input_table, LC_COMPLETED_STAGE) THEN
                                    RAISE e_unexpected_error;
Line: 2642

                                h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_DIM_TABLE');
Line: 2643

                                h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_dim_table);
Line: 2644

                                BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 2647

                                h_message := BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_ITABLE');
Line: 2648

                                h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
Line: 2649

                                BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 2652

                                IF NOT Update_Status_Input_Table(h_input_table, LC_ERROR_STATUS, LC_INVALID_CODES_ERR) THEN
                                    RAISE e_unexpected_error;
Line: 2660

                            h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_DATA_ITABLE');
Line: 2661

                            h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
Line: 2662

                            BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 2665

                            IF NOT Update_Status_Input_Table(h_input_table, LC_NO_DATA_STATUS, NULL) THEN
                                RAISE e_unexpected_error;
Line: 2673

                        h_dim_table_type := BSC_UPDATE_DIM.Get_Dim_Table_Type(h_dim_table);
Line: 2674

                        IF h_dim_table_type = BSC_UPDATE_DIM.DIM_TABLE_TYPE_1N THEN
                            IF BSC_UPDATE_DIM.Dimension_Used_In_AW_Kpi(h_dim_table) THEN
                                BSC_UPDATE_LOG.Write_Line_Log('Loading '||h_dim_table||' into AW', BSC_UPDATE_LOG.OUTPUT);
Line: 2678

                                BSC_UPDATE_DIM.Load_Dim_Into_AW_AT(h_dim_table);
Line: 2695

    IF NOT BSC_UPDATE_UTIL.Write_Init_Variable_Value('UPDATE_DATE_DIM', TO_CHAR(SYSDATE, 'DD/MM/YYYY')) THEN
        RAISE e_unexpected_error;
Line: 2701

    IF NOT Update_Stage_Input_Tables(LC_RUNNING_STATUS, LC_COMPLETED_STAGE, FALSE) THEN
        RAISE e_unexpected_error;
Line: 2706

    IF NOT Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_COMPLETED_STATUS, NULL) THEN
        RAISE e_unexpected_error;
Line: 2711

    BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPDATED'), BSC_UPDATE_LOG.OUTPUT);
Line: 2714

    IF NOT BSC_UPDATE_DIM.WriteRemovedKeyItems THEN
        NULL;
Line: 2724

                         x_source => 'BSC_UPDATE.Load_Dim_Input_Tables');
Line: 2725

        h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
Line: 2732

        BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPDATE_FAILED'),
                         x_source => 'BSC_UPDATE.Load_Dim_Input_Tables');
Line: 2734

        h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
Line: 2742

                         x_source => 'BSC_UPDATE.Load_Dim_Input_Tables');
Line: 2744

        h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
Line: 2756

    x_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
    x_num_dim_tables IN NUMBER
) RETURN BOOLEAN IS

    e_unexpected_error EXCEPTION;
Line: 2762

    h_loaded_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 2782

            IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_dim_table,
                                                                 h_loaded_tables,
                                                                 h_num_loaded_tables) THEN
                -- The table has not been calculated yet

                -- Check if the table can be calculated now
                h_b := Can_Load_Dim_Table(h_dim_table,
                                          h_loaded_tables,
                                          h_num_loaded_tables,
                                          x_dim_tables,
                                          x_num_dim_tables);
Line: 2802

                    h_dim_level_list.delete;
Line: 2823

        BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPDATE_FAILED'),
                         x_source => 'BSC_UPDATE.Load_Dims_Into_AW');
Line: 2830

                         x_source => 'BSC_UPDATE.Load_Dims_Into_AW');
Line: 2837

FUNCTION Update_Kpis_Prototype_Flag (
  x_indicator IN NUMBER
) RETURN BOOLEAN IS
BEGIN
  -- Color By KPI: Mark KPIs for color re-calculation
  UPDATE bsc_kpi_analysis_measures_b
    SET prototype_flag = 7
    WHERE indicator = x_indicator;
Line: 2851

                   , x_source => 'BSC_UPDATE.Update_Kpis_Prototype_Flag');
Line: 2853

END Update_Kpis_Prototype_Flag;
Line: 2860

	x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
	x_num_input_tables IN NUMBER,
	x_start_from IN NUMBER
	) RETURN BOOLEAN IS

    e_unexpected_error EXCEPTION;
Line: 2880

    h_base_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 2883

    h_base_tables_to_color BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 2887

    h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 2890

    h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 2893

    h_color_indicators BSC_UPDATE_UTIL.t_array_of_number;
Line: 2902

    h_kpis BSC_UPDATE_UTIL.t_array_kpis;
Line: 2908

    h_base_tables_aw BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 2910

    h_system_tables_aw BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 2912

    h_indicators_aw BSC_UPDATE_UTIL.t_array_of_number;
Line: 2932

    BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPD_PROCESS'), BSC_UPDATE_LOG.OUTPUT);
Line: 2936

    IF NOT BSC_UPDATE_LOCK.Lock_Temp_Tables('DATA') THEN
        RAISE e_could_not_get_lock;
Line: 2941

    IF NOT BSC_UPDATE_BASE.Create_Generic_Temp_Tables_AT THEN
        RAISE e_unexpected_error;
Line: 2946

    IF NOT BSC_UPDATE_BASE_V2.Create_Generic_Temp_Tables_AT THEN
        RAISE e_unexpected_error;
Line: 2958

        IF NOT Update_Status_All_Input_Tables(LC_PENDING_STATUS, LC_RUNNING_STATUS, NULL) THEN
            RAISE e_unexpected_error;
Line: 2972

            DELETE FROM bsc_db_validation
            WHERE input_table_name = h_input_table;
Line: 2983

                h_aw_flag := BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_base_table);
Line: 2989

            IF NOT BSC_UPDATE_LOCK.Lock_Update_Base_Table(h_input_table, h_base_table) THEN
                RAISE e_could_not_get_lock;
Line: 2996

            h_table_has_any_row := BSC_UPDATE_UTIL.Table_Has_Any_Row(h_input_table);
Line: 3014

                h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDATION');
Line: 3015

                h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
Line: 3016

                BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 3018

                h_table_is_valid := BSC_UPDATE_VAL.Validate_Codes_AT(h_input_table);
Line: 3026

                    h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDCONF');
Line: 3027

                    h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
Line: 3028

                    BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 3032

                    IF NOT Update_Stage_Input_Table(h_input_table, LC_VALIDATED_STAGE) THEN
                        RAISE e_unexpected_error;
Line: 3037

                    BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_UPDATING_BASE_TABLE')||
                                                  ' '||h_base_table, BSC_UPDATE_LOG.OUTPUT);
Line: 3041

                    h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_base_table);
Line: 3045

                        IF NOT BSC_UPDATE_BASE.Calculate_Base_Table_AT(h_base_table, h_input_table, FALSE, h_aw_flag) THEN
                            RAISE e_unexpected_error;
Line: 3050

                        IF NOT BSC_UPDATE_BASE_V2.Calculate_Base_Table_AT(h_base_table, h_input_table, FALSE, h_aw_flag) THEN
                            RAISE e_unexpected_error;
Line: 3057

                    IF NOT Update_Stage_Input_Table(h_input_table, LC_BASE_UPDATED_STAGE) THEN
                        RAISE e_unexpected_error;
Line: 3086

                    h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_BASE_TABLE');
Line: 3087

                    h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_base_table);
Line: 3088

                    BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 3096

                    h_message := BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_ITABLE');
Line: 3097

                    h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
Line: 3098

                    BSC_UPDATE_LOG.Write_Line_Log(h_message,
						  BSC_UPDATE_LOG.OUTPUT);
Line: 3103

                    IF NOT Update_Status_Input_Table(h_input_table, LC_ERROR_STATUS, LC_INVALID_CODES_ERR) THEN
                        RAISE e_unexpected_error;
Line: 3117

                    IF NOT Update_Stage_Input_Table(h_input_table, LC_BASE_UPDATED_STAGE) THEN
                        RAISE e_unexpected_error;
Line: 3144

                    IF NOT Update_Status_Input_Table(h_input_table, LC_NO_DATA_STATUS, NULL) THEN
                        RAISE e_unexpected_error;
Line: 3148

                    h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_BASE_TABLE');
Line: 3149

                    h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_base_table);
Line: 3150

                    BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 3153

                    h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_DATA_ITABLE');
Line: 3154

                    h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
Line: 3155

                    BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 3159

                    IF NOT Update_Status_Input_Table(h_input_table, LC_NO_DATA_STATUS, NULL) THEN
                        RAISE e_unexpected_error;
Line: 3186

            IF NOT BSC_UPDATE_LOCK.Lock_Update_Base_Table(x_input_tables(h_i), h_base_table) THEN
                RAISE e_could_not_get_lock;
Line: 3200

            BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_UPDATING_BASE_TABLE')||
                                          ' '||h_base_table, BSC_UPDATE_LOG.OUTPUT);
Line: 3205

                h_aw_flag := BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_base_table);
Line: 3211

            h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_base_table);
Line: 3215

                IF NOT BSC_UPDATE_BASE.Calculate_Base_Table_AT(h_base_table, x_input_tables(h_i), TRUE, h_aw_flag) THEN
                    RAISE e_unexpected_error;
Line: 3220

                IF NOT BSC_UPDATE_BASE_V2.Calculate_Base_Table_AT(h_base_table, x_input_tables(h_i), TRUE, h_aw_flag) THEN
                    RAISE e_unexpected_error;
Line: 3225

            h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_BASE_TABLE');
Line: 3226

            h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_base_table);
Line: 3227

            BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 3255

    IF NOT Insert_Affected_Tables(h_base_tables, h_num_base_tables, h_system_tables, h_num_system_tables) THEN
        RAISE e_unexpected_error;
Line: 3269

        IF NOT Insert_Affected_Tables(h_base_tables_aw, h_num_base_tables_aw, h_system_tables_aw, h_num_system_tables_aw) THEN
           RAISE e_unexpected_error;
Line: 3275

    BSC_UPDATE_SUM.g_refreshed_mvs.delete;
Line: 3276

    BSC_UPDATE_SUM.g_num_refreshed_mvs := 0;
Line: 3281

            IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
                                                                 h_calculated_sys_tables,
                                                                 h_num_calculated_sys_tables) THEN
                -- The table has not been calculated yet

                -- Check if the table can be calculated now
                h_b := Can_Calculate_Sys_Table(h_system_tables(h_i),
                                               h_calculated_sys_tables,
                                               h_num_calculated_sys_tables,
                                               h_system_tables,
                                               h_num_system_tables);
Line: 3307

                    h_num_kpis := BSC_UPDATE_UTIL.Get_Kpis_Using_Table(h_system_tables(h_i), h_kpis);
Line: 3321

                                    IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_kpis(h_i).indicator,
                                                                                   g_indicators,
                                                                                   g_num_indicators) THEN
                                        h_calc_summary_table := TRUE;
Line: 3337

                        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALC_PROCESS')||
                                                      ' '||h_system_tables(h_i),
			  			      BSC_UPDATE_LOG.OUTPUT);
Line: 3345

                            IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
                                                                             h_system_tables_aw,
                                                                             h_num_system_tables_aw) THEN
                                -- This table is for an AW indicator
                                --LOCKING: lock the tables needed to refresh this table
                                IF NOT BSC_UPDATE_LOCK.Lock_Refresh_AW_Table(h_system_tables(h_i)) THEN
                                    RAISE e_could_not_get_lock;
Line: 3355

                                IF NOT BSC_UPDATE_SUM.Calculate_Sum_Table_AW_AT(h_system_tables(h_i)) THEN
                                    RAISE e_unexpected_error;
Line: 3364

                                IF NOT BSC_UPDATE_LOCK.Lock_Refresh_MV(h_system_tables(h_i)) THEN
                                    RAISE e_could_not_get_lock;
Line: 3369

                                IF NOT BSC_UPDATE_SUM.Calculate_Sum_Table_MV_AT(h_system_tables(h_i),
                                                                             h_calculated_sys_tables,
                                                                             h_num_calculated_sys_tables,
                                                                             h_system_tables,
                                                                             h_num_system_tables) THEN
                                    RAISE e_unexpected_error;
Line: 3382

                            IF NOT BSC_UPDATE_LOCK.Lock_Refresh_Sum_Table(h_system_tables(h_i)) THEN
                                RAISE e_could_not_get_lock;
Line: 3387

                            IF NOT BSC_UPDATE_SUM.Calculate_Sum_Table_AT(h_system_tables(h_i)) THEN
                                RAISE e_unexpected_error;
Line: 3396

                        h_message := BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALCULATED');
Line: 3397

                        h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_system_tables(h_i));
Line: 3398

                        BSC_UPDATE_LOG.Write_Line_Log(h_message,
	   					      BSC_UPDATE_LOG.OUTPUT);
Line: 3404

                        IF NOT BSC_UPDATE_LOCK.Lock_Period_Indicators(h_system_tables(h_i)) THEN
                            RAISE e_could_not_get_lock;
Line: 3408

                        BSC_UPDATE_UTIL.Update_Kpi_Table_Time_Stamp(h_system_tables(h_i));
Line: 3440

            IF BSC_UPDATE_UTIL.Is_Kpi_In_Production(h_indicators_aw(h_i)) THEN
                IF g_kpi_mode THEN
                    IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_indicators_aw(h_i),
                                                                   g_indicators,
                                                                   g_num_indicators) THEN
                        h_calc_aw_kpi := TRUE;
Line: 3453

                BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_IVIEWER', 'REFRESH')||
                                              BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                              ' '||h_indicators_aw(h_i),
			    		      BSC_UPDATE_LOG.OUTPUT);
Line: 3459

                IF NOT BSC_UPDATE_LOCK.Lock_Refresh_AW_Indicator(h_indicators_aw(h_i)) THEN
                    RAISE e_could_not_get_lock;
Line: 3465

                BSC_UPDATE_SUM.Refresh_AW_Kpi_AT(h_indicators_aw(h_i));
Line: 3482

        IF NOT Update_Stage_Input_Tables(LC_RUNNING_STATUS, LC_SYSTEM_UPDATED_STAGE, FALSE) THEN
            RAISE e_unexpected_error;
Line: 3501

        IF NOT BSC_UPDATE_LOCK.Lock_Temp_Tables('COLOR') THEN
            RAISE e_could_not_get_lock;
Line: 3506

        IF NOT BSC_UPDATE_COLOR.Create_Temp_Tab_Tables_AT() THEN
            RAISE e_unexpected_error;
Line: 3519

            IF BSC_UPDATE_UTIL.Is_Kpi_In_Production(h_color_indicators(h_i)) THEN
                IF g_kpi_mode THEN
                    IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_color_indicators(h_i),
                                                                   g_indicators,
                                                                   g_num_indicators) THEN
                        h_calc_color := TRUE;
Line: 3532

                BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_CALC')||
                                              ' '||h_color_indicators(h_i),
			    		      BSC_UPDATE_LOG.OUTPUT);
Line: 3537

                IF NOT BSC_UPDATE_LOCK.Lock_Period_Indicator(h_color_indicators(h_i)) THEN
                    RAISE e_could_not_get_lock;
Line: 3541

                IF NOT Update_Indicator_Period(h_color_indicators(h_i)) THEN
                    RAISE e_unexpected_error;
Line: 3547

                IF NOT Update_Kpis_Prototype_Flag(h_color_indicators(h_i)) THEN
		  RAISE e_unexpected_error;
Line: 3555

                IF NOT BSC_UPDATE_LOCK.Lock_Color_Indicator(h_color_indicators(h_i)) THEN
                    RAISE e_could_not_get_lock;
Line: 3560

                IF NOT BSC_UPDATE_COLOR.Color_Indicator_AT(h_color_indicators(h_i)) THEN
                    RAISE e_unexpected_error;
Line: 3567

                h_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_CALC_COMPLETED');
Line: 3568

                h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'INDICATOR', TO_CHAR(h_color_indicators(h_i)));
Line: 3569

                BSC_UPDATE_LOG.Write_Line_log(h_message,
					      BSC_UPDATE_LOG.OUTPUT);
Line: 3573

                IF NOT BSC_UPDATE_LOCK.Lock_Period_Indicator(h_color_indicators(h_i)) THEN
                    RAISE e_could_not_get_lock;
Line: 3580

                IF NOT BSC_UPDATE_UTIL.Update_Kpi_Period_Name(h_color_indicators(h_i)) THEN
                    RAISE e_unexpected_error;
Line: 3585

                UPDATE bsc_kpi_defaults_b SET last_update_date = SYSDATE
                WHERE indicator = h_color_indicators(h_i);
Line: 3589

                BSC_UPDATE_UTIL.Update_Kpi_Time_Stamp(h_color_indicators(h_i));
Line: 3592

                BSC_UPDATE_UTIL.Update_Kpi_Tab_Time_Stamp(h_color_indicators(h_i));
Line: 3600

                IF NOT BSC_UPDATE_LOCK.Lock_Prototype_Indicator(h_color_indicators(h_i)) THEN
                    RAISE e_could_not_get_lock;
Line: 3604

                UPDATE bsc_kpis_b
                SET prototype_flag = 0, last_updated_by = BSC_APPS.fnd_global_user_id, last_update_date = SYSDATE
                WHERE indicator = h_color_indicators(h_i) AND prototype_flag IN (6, 7);
Line: 3609

                UPDATE bsc_kpi_analysis_measures_b
		  SET prototype_flag = 0
                  WHERE indicator = h_color_indicators(h_i) AND prototype_flag = 7;
Line: 3623

    IF NOT BSC_UPDATE_LOCK.Lock_Update_Date THEN
        RAISE e_could_not_get_lock;
Line: 3627

    IF NOT BSC_UPDATE_UTIL.Write_Init_Variable_Value('UPDATE_DATE', TO_CHAR(SYSDATE, 'DD/MM/YYYY')) THEN
        RAISE e_unexpected_error;
Line: 3635

        IF NOT Update_Stage_Input_Tables(LC_RUNNING_STATUS, LC_COMPLETED_STAGE, FALSE) THEN
            RAISE e_unexpected_error;
Line: 3640

        IF NOT Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_COMPLETED_STATUS, NULL) THEN
            RAISE e_unexpected_error;
Line: 3646

    BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPDATED'), BSC_UPDATE_LOG.OUTPUT);
Line: 3652

        BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPDATE_FAILED'),
                         x_source => 'BSC_UPDATE.Process_Input_Tables');
Line: 3655

            h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
Line: 3665

                         x_source => 'BSC_UPDATE.Process_Input_Tables');
Line: 3667

            h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
Line: 3676

                         x_source => 'BSC_UPDATE.Process_Input_Tables');
Line: 3679

            h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
Line: 3692

	x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
	x_num_input_tables IN NUMBER,
	x_start_from IN NUMBER
	) RETURN BOOLEAN IS
PRAGMA AUTONOMOUS_TRANSACTION;
Line: 3709

    p_base_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
    p_num_base_tables IN NUMBER
) RETURN BOOLEAN IS

    e_unexpected_error EXCEPTION;
Line: 3717

    h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 3720

    h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 3723

    h_refreshed_mvs BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 3735

    h_kpis BSC_UPDATE_UTIL.t_array_kpis;
Line: 3748

    IF NOT Insert_Affected_Tables(p_base_tables, p_num_base_tables, h_system_tables, h_num_system_tables) THEN
        RAISE e_unexpected_error;
Line: 3755

            IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
                                                                 h_calculated_sys_tables,
                                                                 h_num_calculated_sys_tables) THEN
                -- The table has not been calculated yet

                -- Check if the table can be calculated
                h_b := Can_Calculate_Sys_Table(h_system_tables(h_i),
                                               h_calculated_sys_tables,
                                               h_num_calculated_sys_tables,
                                               h_system_tables,
                                               h_num_system_tables);
Line: 3777

                    h_num_kpis := BSC_UPDATE_UTIL.Get_Kpis_Using_Table(h_system_tables(h_i), h_kpis);
Line: 3794

                            IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(mv_name,
                                                                                 h_refreshed_mvs,
                                                                                 h_num_refreshed_mvs) THEN

                                --LOCKING: Lock the objects required for the mv refresh
                                --LOCKING: review that no commits between this point and the commit to
                                -- to release the locks
                                IF NOT BSC_UPDATE_LOCK.Lock_Refresh_MV(h_system_tables(h_i)) THEN
                                    RAISE e_could_not_get_lock;
Line: 3812

                                IF NOT BSC_UPDATE_SUM.Refresh_Zero_MVs_AT(h_system_tables(h_i),
                                                                       mv_name, h_error_refresh) THEN
                                    RAISE e_error_refresh_zero;
Line: 3842

                        x_source => 'BSC_UPDATE.Refresh_System_MVs');
Line: 3846

        BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPDATE_FAILED'),
                        x_source => 'BSC_UPDATE.Refresh_System_MVs');
Line: 3852

                        x_source => 'BSC_UPDATE.Refresh_System_MVs');
Line: 3856

        BSC_MESSAGE.Add(x_message => 'BSC_UPDATE_SUM.Refresh_Zero_MVs '||mv_name||' '||h_error_refresh,
                        x_source => 'BSC_UPDATE.Refresh_System_MVs');
Line: 3862

                        x_source => 'BSC_UPDATE.Refresh_System_MVs');
Line: 3872

    p_base_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
    p_num_base_tables IN NUMBER
) RETURN BOOLEAN IS

    e_unexpected_error EXCEPTION;
Line: 3878

    h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 3881

    h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 3884

    h_refreshed_mvs BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 3905

    IF NOT Insert_Affected_Tables(p_base_tables, p_num_base_tables, h_system_tables, h_num_system_tables) THEN
        RAISE e_unexpected_error;
Line: 3912

            IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
                                                                 h_calculated_sys_tables,
                                                                 h_num_calculated_sys_tables) THEN
                -- The table has not been calculated yet

                -- Check if the table can be calculated
                h_b := Can_Calculate_Sys_Table(h_system_tables(h_i),
                                               h_calculated_sys_tables,
                                               h_num_calculated_sys_tables,
                                               h_system_tables,
                                               h_num_system_tables);
Line: 3938

                    IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(mv_name,
                                                                         h_refreshed_mvs,
                                                                         h_num_refreshed_mvs) THEN

                        IF NOT BSC_BIA_WRAPPER.Refresh_Summary_MV(mv_name, h_error_refresh) THEN
                            RAISE e_error_refresh;
Line: 3947

                        IF NOT BSC_UPDATE_SUM.Refresh_Zero_MVs(h_system_tables(h_i),
                                                               mv_name, h_error_refresh) THEN
                            RAISE e_error_refresh_zero;
Line: 3972

        BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPDATE_FAILED'),
                        x_source => 'BSC_UPDATE.Refresh_System_MVs_Mig');
Line: 3978

                        x_source => 'BSC_UPDATE.Refresh_System_MVs_Mig');
Line: 3982

        BSC_MESSAGE.Add(x_message => 'BSC_UPDATE_SUM.Refresh_Zero_MVs '||mv_name||' '||h_error_refresh,
                        x_source => 'BSC_UPDATE.Refresh_System_MVs_Mig');
Line: 3988

                        x_source => 'BSC_UPDATE.Refresh_System_MVs_Mig');
Line: 4005

    e_update_error EXCEPTION;
Line: 4060

    Execute_Update_Process(TO_NUMBER(x_process_id), x_process_name, x_parameter_1);
Line: 4062

    h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
Line: 4064

    SELECT count(*)
    INTO h_count
    FROM bsc_message_logs
    WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = h_sessionid;
Line: 4070

        RAISE e_update_error;
Line: 4073

    SELECT count(*)
    INTO h_count
    FROM bsc_db_loader_control
    WHERE process_id = g_process_id AND status IN (LC_ERROR_STATUS, LC_NO_DATA_STATUS);
Line: 4095

                X_Source  => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
                X_Mode    => 'I'
        );
Line: 4099

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 4101

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 4104

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 4109

        ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
Line: 4112

    WHEN e_update_error THEN
        -- LOCKING
        BSC_LOCKS_PUB.Remove_System_Lock;
Line: 4116

        ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
Line: 4128

                        x_source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
                        x_mode => 'I');
Line: 4132

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 4134

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 4137

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 4160

                        x_source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
                        x_mode => 'I');
Line: 4164

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 4166

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 4169

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 4183

    e_update_error EXCEPTION;
Line: 4194

    h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 4196

    h_indicators BSC_UPDATE_UTIL.t_array_of_number;
Line: 4250

        RAISE e_update_error;
Line: 4256

          raise e_update_error;
Line: 4267

            raise e_update_error;
Line: 4289

    Execute_Update_Process(TO_NUMBER(x_process_id), PC_LOADER_PROCESS, NULL);
Line: 4291

    h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
Line: 4294

    h_sql := 'SELECT count(*)'||
             ' FROM bsc_message_logs'||
             ' WHERE type = :1'||
             ' AND UPPER(source) = :2 AND last_update_login = :3';
Line: 4302

    SELECT count(*)
    INTO h_count
    FROM bsc_message_logs
    WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = h_sessionid;
Line: 4308

        RAISE e_update_error;
Line: 4312

    h_sql := 'SELECT count(*)'||
             ' FROM bsc_db_loader_control'||
             ' WHERE process_id = :1 AND status IN (:2, :3)';
Line: 4319

    SELECT count(*)
    INTO h_count
    FROM bsc_db_loader_control
    WHERE process_id = g_process_id AND status IN (LC_ERROR_STATUS, LC_NO_DATA_STATUS);
Line: 4341

                X_Source  => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
                X_Mode    => 'I'
        );
Line: 4345

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 4347

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 4350

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 4355

        ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
Line: 4358

    WHEN e_update_error THEN
        -- LOCKING
        BSC_LOCKS_PUB.Remove_System_Lock;
Line: 4362

        ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
Line: 4374

                        x_source => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
                        x_mode => 'I');
Line: 4378

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 4380

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 4383

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 4404

    e_update_error EXCEPTION;
Line: 4461

    Execute_Update_Process(TO_NUMBER(x_process_id), PC_LOAD_DIMENSIONS, NULL);
Line: 4463

    h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
Line: 4466

    h_sql := 'SELECT count(*)'||
             ' FROM bsc_message_logs'||
             ' WHERE type = :1'||
             ' AND UPPER(source) = :2 AND last_update_login = :3';
Line: 4474

    SELECT count(*)
    INTO h_count
    FROM bsc_message_logs
    WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = h_sessionid;
Line: 4480

        RAISE e_update_error;
Line: 4484

    h_sql := 'SELECT count(*)'||
             ' FROM bsc_db_loader_control'||
             ' WHERE process_id = :1 AND status IN (:2, :3)';
Line: 4491

    SELECT count(*)
    INTO h_count
    FROM bsc_db_loader_control
    WHERE process_id = g_process_id AND status IN (LC_ERROR_STATUS, LC_NO_DATA_STATUS);
Line: 4513

                X_Source  => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
                X_Mode    => 'I'
        );
Line: 4517

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 4519

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 4522

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 4527

        ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
Line: 4530

    WHEN e_update_error THEN
        -- LOCKING
        BSC_LOCKS_PUB.Remove_System_Lock;
Line: 4534

        ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
Line: 4546

                        x_source => 'BSC_UPDATE.Run_Concurrent_Loader_Dim_Apps',
                        x_mode => 'I');
Line: 4550

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 4552

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 4555

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 4560

        ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
Line: 4579

    e_update_error EXCEPTION;
Line: 4606

        RAISE e_update_error;
Line: 4639

    Execute_Update_Process(TO_NUMBER(x_process_id), PC_YEAR_CHANGE_PROCESS, x_calendars);
Line: 4641

    h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
Line: 4643

    SELECT count(*)
    INTO h_count
    FROM bsc_message_logs
    WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = g_session_id;
Line: 4649

        RAISE e_update_error;
Line: 4660

                X_Source  => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
                X_Mode    => 'I'
        );
Line: 4664

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 4666

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 4669

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 4674

        ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
Line: 4677

    WHEN e_update_error THEN
        -- LOCKING
        BSC_LOCKS_PUB.Remove_System_Lock;
Line: 4681

        ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
Line: 4686

                        x_source => 'BSC_UPDATE.Run_change_current_year',
                        x_mode => 'I');
Line: 4690

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 4692

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 4695

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 4717

    e_update_error EXCEPTION;
Line: 4730

    h_indicators BSC_UPDATE_UTIL.t_array_of_number;
Line: 4733

    h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 4749

    h_prod_indicators BSC_UPDATE_UTIL.t_array_of_number;
Line: 4757

        SELECT prototype_flag, name
        FROM bsc_kpis_vl
        WHERE indicator = p_kpi;
Line: 4787

        RAISE e_update_error;
Line: 4820

    h_num_indicators := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_indicators,
                                                               h_indicators,
                                                               ',');
Line: 4852

                BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_CANNOT_LOAD_OBJS_IN_PROT'),
                                              BSC_UPDATE_LOG.OUTPUT);
Line: 4857

            BSC_UPDATE_LOG.Write_Line_Log(h_indicators(h_i)||' '||h_indic_name, BSC_UPDATE_LOG.OUTPUT);
Line: 4879

    SELECT bsc_db_process_id_s.nextval
    INTO h_process_id
    FROM DUAL;
Line: 4883

    INSERT INTO bsc_db_process_control (process_id, process_name,
        creation_date, created_by, last_update_date,last_updated_by,
        last_update_login, status)
    VALUES (h_process_id, PC_LOADER_PROCESS,
        SYSDATE, g_user_id, SYSDATE, g_user_id, g_session_id, PC_PENDING_STATUS);
Line: 4891

        INSERT INTO bsc_db_loader_control (process_id, input_table_name, status,
            error_code, stage, last_stage_flag)
        VALUES (h_process_id, h_input_tables(h_i), LC_PENDING_STATUS, NULL, LC_PENDING_STAGE, 0);
Line: 4900

        Execute_Update_Process(h_process_id, PC_LOADER_PROCESS, NULL);
Line: 4903

        Execute_Update_Process(h_process_id, PC_LOADER_PROCESS, h_lst_prod_indicators);
Line: 4907

    h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
Line: 4909

    SELECT count(*)
    INTO h_count
    FROM bsc_message_logs
    WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = g_session_id;
Line: 4915

        RAISE e_update_error;
Line: 4918

    SELECT count(*)
    INTO h_count
    FROM bsc_db_loader_control
    WHERE process_id = g_process_id AND status IN (LC_ERROR_STATUS, LC_NO_DATA_STATUS);
Line: 4940

                X_Source  => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
                X_Mode    => 'I'
        );
Line: 4944

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 4946

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 4949

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 4954

        ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
Line: 4957

    WHEN e_update_error THEN
        -- LOCKING
        BSC_LOCKS_PUB.Remove_System_Lock;
Line: 4961

        ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
Line: 4972

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 4974

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 4977

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 4990

                        x_source => 'BSC_UPDATE.Load_Indicators_Data',
                        x_mode => 'I');
Line: 4994

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 4996

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 4999

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 5022

    e_update_error EXCEPTION;
Line: 5032

    h_indicators BSC_UPDATE_UTIL.t_array_of_number;
Line: 5035

    h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 5038

    l_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 5044

    h_dbi_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 5046

    h_dbi_dim_requests BSC_UPDATE_UTIL.t_array_of_number;
Line: 5075

        RAISE e_update_error;
Line: 5108

    h_num_indicators := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_indicators,
                                                               h_indicators,
                                                               ',');
Line: 5138

    IF NOT BSC_UPDATE_DIM.Get_Dbi_Dims_Kpis(h_indicators, h_num_indicators, h_dbi_dimensions, h_num_dbi_dimensions) THEN
        RAISE e_unexpected_error;
Line: 5147

        IF NOT BSC_UPDATE_DIM.Create_Dbi_Dim_Temp_Tables THEN
            RAISE e_unexpected_error;
Line: 5151

        IF NOT BSC_UPDATE_DIM.Create_AW_Dim_Temp_Tables THEN
            RAISE e_unexpected_error;
Line: 5156

    h_dbi_dim_requests.delete;
Line: 5158

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_IVIEWER', 'REFRESH')||
                                      ' '||h_dbi_dimensions(h_i), BSC_UPDATE_LOG.OUTPUT);
Line: 5168

            BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_SUBMMITREQ_FAILED'), BSC_UPDATE_LOG.OUTPUT);
Line: 5170

            BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_LOADER_REQ_ID')||
                                          ' '||h_dbi_dim_requests(h_i), BSC_UPDATE_LOG.OUTPUT);
Line: 5193

    SELECT bsc_db_process_id_s.nextval
    INTO h_process_id
    FROM DUAL;
Line: 5198

    INSERT INTO bsc_db_process_control (process_id, process_name,
        creation_date, created_by, last_update_date,last_updated_by,
        last_update_login, status)
    VALUES (h_process_id, PC_LOAD_DIMENSIONS,
        SYSDATE, g_user_id, SYSDATE, g_user_id, g_session_id, PC_PENDING_STATUS);
Line: 5206

        INSERT INTO bsc_db_loader_control (process_id, input_table_name, status,
            error_code, stage, last_stage_flag)
        VALUES (h_process_id, h_input_tables(h_i), LC_PENDING_STATUS, NULL, LC_PENDING_STAGE, 0);
Line: 5213

    Execute_Update_Process(h_process_id, PC_LOAD_DIMENSIONS, x_indicators);
Line: 5224

    h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
Line: 5226

    SELECT count(*)
    INTO h_count
    FROM bsc_message_logs
    WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = g_session_id;
Line: 5232

        RAISE e_update_error;
Line: 5235

    SELECT count(*)
    INTO h_count
    FROM bsc_db_loader_control
    WHERE process_id = g_process_id AND status IN (LC_ERROR_STATUS, LC_NO_DATA_STATUS);
Line: 5257

                X_Source  => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
                X_Mode    => 'I'
        );
Line: 5261

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 5263

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 5266

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 5271

        ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
Line: 5274

    WHEN e_update_error THEN
        -- LOCKING
        BSC_LOCKS_PUB.Remove_System_Lock;
Line: 5277

        ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
Line: 5287

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 5289

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 5292

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 5296

        ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
Line: 5301

                        x_source => 'BSC_UPDATE.Load_Indicators_Dims',
                        x_mode => 'I');
Line: 5305

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 5307

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 5310

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 5321

| PROCEDURE Delete_Indicators_Data
+============================================================================*/
PROCEDURE Delete_Indicators_Data (
        ERRBUF OUT NOCOPY VARCHAR2,
	RETCODE OUT NOCOPY VARCHAR2,
        x_indicators IN VARCHAR2,
        x_keep_input_table_data IN VARCHAR2
	) IS

    e_system_lock EXCEPTION;
Line: 5331

    e_update_error EXCEPTION;
Line: 5340

    h_indicators BSC_UPDATE_UTIL.t_array_of_number;
Line: 5370

        RAISE e_update_error;
Line: 5408

        h_num_indicators := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_indicators,
                                                                   h_indicators,
                                                                   ',');
Line: 5420

        SELECT bsc_db_process_id_s.nextval
        INTO h_process_id
        FROM DUAL;
Line: 5425

        INSERT INTO bsc_db_process_control (process_id, process_name,
           creation_date, created_by, last_update_date,last_updated_by,
           last_update_login, status)
        VALUES (h_process_id, PC_DELETE_KPI_DATA_PROCESS,
           SYSDATE, g_user_id, SYSDATE, g_user_id, g_session_id, PC_PENDING_STATUS);
Line: 5433

            INSERT INTO bsc_db_loader_control (process_id, input_table_name, status,
                error_code, stage, last_stage_flag)
            VALUES (h_process_id, h_indicators(h_i), NULL, NULL, NULL, 0);
Line: 5441

    Execute_Update_Process(h_process_id, PC_DELETE_KPI_DATA_PROCESS, NULL);
Line: 5443

    h_source := 'BSC_UPDATE.EXECUTE_UPDATE_PROCESS';
Line: 5445

    SELECT count(*)
    INTO h_count
    FROM bsc_message_logs
    WHERE type = 0 AND UPPER(source) = h_source AND last_update_login = g_session_id;
Line: 5451

        RAISE e_update_error;
Line: 5462

                X_Source  => 'BSC_UPDATE.Run_Concurrent_Loader_Apps',
                X_Mode    => 'I'
        );
Line: 5466

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 5468

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 5471

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 5476

        ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
Line: 5479

    WHEN e_update_error THEN
        -- LOCKING
        BSC_LOCKS_PUB.Remove_System_Lock;
Line: 5482

        ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
Line: 5487

                        x_source => 'BSC_UPDATE.Delete_Indicators_Data',
                        x_mode => 'I');
Line: 5491

        BSC_UPDATE_LOG.Write_Errors_To_Log;
Line: 5493

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
                                      BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
                                      ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
Line: 5496

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
Line: 5504

END Delete_Indicators_Data;
Line: 5511

	x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
        x_num_indicators IN NUMBER,
        x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
        x_num_input_tables IN OUT NOCOPY NUMBER
	) RETURN BOOLEAN IS

    e_unexpected_error EXCEPTION;
Line: 5528

    h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 5541

    h_sql := 'SELECT table_name'||
             ' FROM bsc_kpi_data_tables'||
             ' WHERE ('||h_where_indics||') AND table_name IS NOT NULL';
Line: 5556

    IF NOT BSC_UPDATE_INC.Get_Input_Tables(x_input_tables, x_num_input_tables, h_system_tables, h_num_system_tables) THEN
        RAISE e_unexpected_error;
Line: 5564

        BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_ITABLES_FAILED'),
                        x_source => 'BSC_UPDATE.Get_Input_Tables_Kpis');
Line: 5570

                        x_source => 'BSC_UPDATE.Get_Input_Tables_Kpis');
Line: 5576

x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
x_num_input_tables IN NUMBER,
x_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
x_num_indicators IN OUT NOCOPY NUMBER
)return boolean is
--
e_unexpected_error exception;
Line: 5596

        BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_ITABLES_FAILED'),
                        x_source => 'BSC_UPDATE.get_kpi_for_input_tables');
Line: 5602

                        x_source => 'BSC_UPDATE.get_kpi_for_input_tables');
Line: 5610

x_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
x_num_indicators IN OUT NOCOPY NUMBER
)return boolean is
--
cursor c1(p_table varchar2) is select table_name from bsc_db_tables_rels where source_table_name=p_table;
Line: 5616

cursor c2(p_table varchar2) is select distinct indicator from bsc_kpi_data_tables where table_name=p_table;
Line: 5618

l_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
Line: 5656

x_array BSC_UPDATE_UTIL.t_array_of_number,
x_num_array NUMBER
)return boolean is
Begin
  for i in 1..x_num_array loop
    if x_array(i)=x_value then
      return true;
Line: 5674

	x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
        x_num_indicators IN NUMBER,
        x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
        x_num_input_tables IN OUT NOCOPY NUMBER
	) RETURN BOOLEAN IS

    e_unexpected_error EXCEPTION;
Line: 5703

    h_sql := 'SELECT DISTINCT source_table_name'||
             ' FROM bsc_kpi_dim_levels_vl k, bsc_db_tables_rels r'||
             ' WHERE ('||h_where_indics||') AND k.level_source = :1 '||
             ' AND  k.level_table_name = r.table_name';
Line: 5719

    h_sql := 'SELECT DISTINCT source_table_name'||
             ' FROM bsc_kpi_dim_levels_vl k, bsc_db_tables_rels r'||
             ' WHERE ('||h_where_indics||') AND k.level_source = :1 '||
             ' AND  k.table_relation = r.table_name';
Line: 5738

        BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_ITABLES_FAILED'),
                        x_source => 'BSC_UPDATE.Get_Dim_Input_Tables_Kpis');
Line: 5744

                        x_source => 'BSC_UPDATE.Get_Dim_Input_Tables_Kpis');
Line: 5757

    UPDATE
        bsc_db_process_control
    SET
        last_update_date = SYSDATE,
        last_updated_by = g_user_id,
        last_update_login = g_session_id,
        status = x_status,
        end_time = SYSDATE
    WHERE
        process_id = g_process_id;
Line: 5773

                        x_source => 'BSC_UPDATE.Set_PStatus_Finished');
Line: 5787

    IF BSC_UPDATE_LOG.Log_File_Name IS NULL THEN
        h_complete_log_file_name := NULL;
Line: 5790

        h_complete_log_file_name := BSC_UPDATE_LOG.Log_File_Dir||'/'||BSC_UPDATE_LOG.Log_File_Name;
Line: 5793

    UPDATE
        bsc_db_process_control
    SET
        last_update_date = SYSDATE,
        last_updated_by = g_user_id,
        last_update_login = g_session_id,
        status = PC_RUNNING_STATUS,
        log_file_location = h_complete_log_file_name,
        start_time = SYSDATE
    WHERE
        process_id = g_process_id;
Line: 5810

                      x_source => 'BSC_UPDATE.Set_PStatus_Running');
Line: 5818

| FUNCTION Update_Indicator_Period
+============================================================================*/
FUNCTION Update_Indicator_Period (
    x_indicator IN NUMBER
) RETURN BOOLEAN IS
BEGIN
    -- The update period of an indicator is the minimun period of the tables
    -- used by the indicator.

    -- BSC-PMF Integration:Fix this query by adding NVL, to avoid assigning NULL
    -- to the current period when all of the analysis options of a kpi are for PMF measures.

    UPDATE bsc_kpi_periodicities p
    SET current_period = (
        SELECT DISTINCT
            NVL(MIN(t.current_period), p.current_period)
        FROM
            bsc_kpi_data_tables k,
            bsc_db_tables t
        WHERE
            k.table_name = t.table_name AND
            k.indicator = p.indicator AND
	    t.periodicity_id = p.periodicity_id)
    WHERE p.indicator = x_indicator;
Line: 5848

                        x_source => 'BSC_UPDATE.Update_Indicator_Period');
Line: 5851

END Update_Indicator_Period;
Line: 5855

| FUNCTION Update_Indicators_Periods
+============================================================================*/
FUNCTION Update_Indicators_Periods RETURN BOOLEAN IS
BEGIN
    -- The update period of an indicator is the minimun period of the tables
    -- used by the indicator.

    -- BSC-PMF Integration:Fix this query by adding NVL, to avoid assigning NULL
    -- to the current period when all of the analysis options of a kpi are for PMF measures.

    UPDATE bsc_kpi_periodicities p
    SET current_period = (
        SELECT DISTINCT
            NVL(MIN(t.current_period), p.current_period)
        FROM
            bsc_kpi_data_tables k,
            bsc_db_tables t
        WHERE
            k.table_name = t.table_name AND
            k.indicator = p.indicator AND
	    t.periodicity_id = p.periodicity_id)
    WHERE p.indicator IN (
        SELECT
            indicator
        FROM
            bsc_kpis_vl);
Line: 5888

                        x_source => 'BSC_UPDATE.Update_Indicators_Periods');
Line: 5891

END Update_Indicators_Periods;
Line: 5895

| FUNCTION Update_Stage_Input_Table
+============================================================================*/
FUNCTION Update_Stage_Input_Table(
	x_input_table IN VARCHAR2,
        x_target_stage IN VARCHAR2
	) RETURN BOOLEAN IS
BEGIN
    UPDATE
        bsc_db_loader_control
    SET
        stage = x_target_stage
    WHERE
        input_table_name = x_input_table AND
        process_id = g_process_id;
Line: 5915

                      x_source => 'BSC_UPDATE.Update_Stage_Input_Table');
Line: 5918

END Update_Stage_Input_Table;
Line: 5922

| FUNCTION Update_Stage_Input_Tables
+============================================================================*/
FUNCTION Update_Stage_Input_Tables(
	x_current_status IN VARCHAR2,
	x_target_stage IN VARCHAR2,
	x_last_stage_flag IN BOOLEAN
	) RETURN BOOLEAN IS
BEGIN
    IF x_last_stage_flag THEN
        UPDATE
            bsc_db_loader_control
        SET
            last_stage_flag = 0
        WHERE
            input_table_name IN (
                SELECT
                    input_table_name
                FROM
                    bsc_db_loader_control
                WHERE
                    process_id = g_process_id AND
                    status = x_current_status
            );
Line: 5946

        UPDATE
            bsc_db_loader_control
        SET
            stage = x_target_stage,
            last_stage_flag = 1
        WHERE
            process_id = g_process_id AND
            status = x_current_status;
Line: 5956

        UPDATE
            bsc_db_loader_control
        SET
            stage = x_target_stage
        WHERE
            process_id = g_process_id AND
            status = x_current_status;
Line: 5971

                        x_source => 'BSC_UPDATE.Update_Stage_Input_Tables');
Line: 5974

END Update_Stage_Input_Tables;
Line: 5978

| FUNCTION Update_Status_All_Input_Tables
+============================================================================*/
FUNCTION Update_Status_All_Input_Tables(
	x_current_status IN VARCHAR2,
	x_target_status IN VARCHAR2,
	x_error_code IN VARCHAR2
	) RETURN BOOLEAN IS
BEGIN
    UPDATE
        bsc_db_loader_control
    SET
        status = x_target_status,
        error_code = x_error_code
    WHERE
        status = x_current_status AND
        process_id = g_process_id;
Line: 6000

                      x_source => 'BSC_UPDATE.Update_Status_All_Input_Tables');
Line: 6003

END Update_Status_All_Input_Tables;
Line: 6007

| FUNCTION Update_Status_Input_Table
+============================================================================*/
FUNCTION Update_Status_Input_Table(
	x_input_table IN VARCHAR2,
        x_target_status IN VARCHAR2,
	x_error_code IN VARCHAR2
	) RETURN BOOLEAN IS
BEGIN
    UPDATE
        bsc_db_loader_control
    SET
        status = x_target_status,
        error_code = x_error_code
    WHERE
        input_table_name = x_input_table AND
        process_id = g_process_id;
Line: 6029

                      x_source => 'BSC_UPDATE.Update_Status_Input_Table');
Line: 6032

END Update_Status_Input_Table;
Line: 6039

    x_requests IN BSC_UPDATE_UTIL.t_array_of_number,
    x_num_requests IN NUMBER
) RETURN BOOLEAN IS

    h_i NUMBER;
Line: 6069

                        x_source => 'BSC_UPDATE.Wait_For_Requests');
Line: 6089

    h_update_date VARCHAR2(200);
Line: 6095

    c_status_sql VARCHAR2(2000) := 'SELECT input_table_name, status, error_code'||
                                   ' FROM bsc_db_loader_control'||
                                   ' WHERE process_id = :1'||
                                   ' ORDER BY input_table_name';
Line: 6101

        SELECT input_table_name, status, error_code
        FROM bsc_db_loader_control
        WHERE process_id = p_process_id
        ORDER BY input_table_name;
Line: 6114

    c_invalid_codes_sql VARCHAR2(2000) := 'SELECT input_table_name, column_name, invalid_code'||
                                          ' FROM bsc_db_validation'||
                                          ' WHERE input_table_name IN ('||
                                          ' SELECT table_name'||
                                          ' FROM bsc_db_tables'||
                                          ' WHERE table_type = DECODE(:1, :2, :3, :4))'||
                                          ' ORDER BY input_table_name';
Line: 6124

        SELECT input_table_name, column_name, invalid_code
        FROM bsc_db_validation
        WHERE input_table_name IN (
            SELECT input_table_name
            FROM bsc_db_loader_control
            WHERE process_id = p_process_id
        )
        ORDER BY input_table_name;
Line: 6138

    c_tables_sql VARCHAR2(2000) := 'SELECT lc.input_table_name, t.periodicity_id,'||
                                   ' p.name, t.current_period, t.current_subperiod'||
                                   ' FROM bsc_db_tables t, bsc_db_loader_control lc, bsc_sys_periodicities_vl p'||
                                   ' WHERE lc.input_table_name = t.table_name AND'||
                                   ' lc.process_id = :1 AND t.periodicity_id = p.periodicity_id';
Line: 6145

        SELECT lc.input_table_name, t.periodicity_id,
               p.name, t.current_period, t.current_subperiod
        FROM bsc_db_tables t, bsc_db_loader_control lc, bsc_sys_periodicities_vl p
        WHERE lc.input_table_name = t.table_name AND
              lc.process_id = p_process_id AND t.periodicity_id = p.periodicity_id;
Line: 6160

    BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
Line: 6161

    BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
                                  BSC_UPDATE_LOG.OUTPUT);
Line: 6163

    BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROCESS_RESULT'), BSC_UPDATE_LOG.OUTPUT);
Line: 6164

    BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
                                  BSC_UPDATE_LOG.OUTPUT);
Line: 6167

    IF NOT BSC_UPDATE_UTIL.Get_Init_Variable_Value('UPDATE_DATE', h_update_date) THEN
        RAISE e_unexpected_error;
Line: 6170

    BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_LAST_UPDATE')||' '||h_update_date, BSC_UPDATE_LOG.OUTPUT);
Line: 6171

    BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
Line: 6178

        BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_W)||
                                      RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'STATUS'), C_STATUS_W),
                                      BSC_UPDATE_LOG.OUTPUT);
Line: 6186

            h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_UPDATE_FAILED');
Line: 6189

                h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_FOUND');
Line: 6191

                h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_LOADER_PROC_FAILED');
Line: 6193

                h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRCFILE_FAILED');
Line: 6195

                h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRC_FIELDNUMBER_FAILED');
Line: 6197

                h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRC_NULL_KEYVALUES');
Line: 6198

            ELSIF h_error_code = LC_UPLOAD_INSERT_ERR THEN
                h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_DATATYPE');
Line: 6201

                h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRCFILE_NOT_FOUND');
Line: 6203

                h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_EXCEL_CONNECTION_FAILED');
Line: 6205

                h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_NOT_FOUND');
Line: 6207

                h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_IS_INVALID');
Line: 6209

                h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_FAILED');
Line: 6213

            h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_UPDATE_FAILED')||' '||
                      BSC_UPDATE_UTIL.Get_Message('BSC_ITABLE_EMPTY');
Line: 6216

            h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_STATUS');
Line: 6219

        BSC_UPDATE_LOG.Write_Line_Log(h_line, BSC_UPDATE_LOG.OUTPUT);
Line: 6229

        BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
Line: 6230

        BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
                                      BSC_UPDATE_LOG.OUTPUT);
Line: 6232

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INVALID_RECORDS'),
                                      BSC_UPDATE_LOG.OUTPUT);
Line: 6234

        BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
                                      BSC_UPDATE_LOG.OUTPUT);
Line: 6236

        BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_W)||
                                      RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'COLUMN'), C_COLUMN_W)||
                                      RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INVALID_CODE'), C_INVALID_CODE_W),
                                      BSC_UPDATE_LOG.OUTPUT);
Line: 6243

        BSC_UPDATE_LOG.Write_Line_Log(RPAD(h_input_table_name, C_TABLE_W)||
                                      RPAD(h_column_name, C_COLUMN_W)||
                                      RPAD(h_invalid_code, C_INVALID_CODE_W), BSC_UPDATE_LOG.OUTPUT);
Line: 6253

        BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
Line: 6254

        BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
                                      BSC_UPDATE_LOG.OUTPUT);
Line: 6256

        BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'TABLE_UPDATE_PERIOD'),
                                      BSC_UPDATE_LOG.OUTPUT);
Line: 6258

        BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
                                      BSC_UPDATE_LOG.OUTPUT);
Line: 6260

        BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_NAME_W)||
                                      RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'PERIODICITY'), C_PERIODICITY_W)||
                                      RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'PERIOD'), C_PERIOD_W),
                                      BSC_UPDATE_LOG.OUTPUT);
Line: 6272

            h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(h_periodicity_id);
Line: 6278

            BSC_UPDATE_LOG.Write_Line_Log(h_line, BSC_UPDATE_LOG.OUTPUT);
Line: 6285

    BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
Line: 6291

      BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WR_LOGFILE_RES_FAILED'),
                      x_source => 'BSC_UPDATE.Write_Result_Log');
Line: 6297

                      x_source => 'BSC_UPDATE.Write_Result_Log');
Line: 6305

FUNCTION Get_Indicator_List(x_number_array IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number)
RETURN NUMBER IS
  CURSOR cIndics is
    SELECT value_n
    FROM BSC_TMP_BIG_IN_COND
    WHERE session_id = -500 and variable_id = -500;
Line: 6323

  DELETE BSC_TMP_BIG_IN_COND where session_id = -500 and variable_id = -500;
Line: 6333

    x_system_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
    x_num_system_tables IN NUMBER
) IS

    h_where_cond VARCHAR2(32700);
Line: 6353

        h_sql := 'select indicator, name'||
                 ' from bsc_kpis_vl'||
                 ' where prototype_flag NOT IN (:1, :2, :3) and '||h_where_cond;
Line: 6363

                h_message := BSC_UPDATE_UTIL.Get_Message('BSC_CANNOT_LOAD_OBJS_IN_PROT');
Line: 6364

                BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 6367

            BSC_UPDATE_LOG.Write_Line_Log(h_kpi||' '||h_name, BSC_UPDATE_LOG.OUTPUT);
Line: 6377

        h_sql := 'select indicator, name'||
                 ' from bsc_kpis_vl'||
                 ' where prototype_flag NOT IN (:1, :2, :3) and'||
                 ' indicator in (select indicator'||
                 ' from bsc_kpi_data_tables'||
                 ' where '||h_where_cond||
                 ' )';
Line: 6391

               h_message := BSC_UPDATE_UTIL.Get_Message('BSC_CANNOT_LOAD_OBJS_IN_PROT');
Line: 6392

               BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
Line: 6395

           BSC_UPDATE_LOG.Write_Line_Log(h_kpi||' '||h_name, BSC_UPDATE_LOG.OUTPUT);