[Home] [Help]
15: | FUNCTION Can_Load_Dim_Table
16: +============================================================================*/
17: FUNCTION Can_Load_Dim_Table(
18: x_dim_table IN VARCHAR2,
19: x_loaded_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
20: x_num_loaded_tables IN NUMBER,
21: x_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
22: x_num_dim_tables IN NUMBER
23: ) RETURN BOOLEAN IS
17: FUNCTION Can_Load_Dim_Table(
18: x_dim_table IN VARCHAR2,
19: x_loaded_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
20: x_num_loaded_tables IN NUMBER,
21: x_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
22: x_num_dim_tables IN NUMBER
23: ) RETURN BOOLEAN IS
24:
25: TYPE t_cursor IS REF CURSOR;
39:
40: OPEN c_parent_tables(x_dim_table);
41: FETCH c_parent_tables INTO h_parent_table;
42: WHILE c_parent_tables%FOUND LOOP
43: IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_parent_table,
44: x_dim_tables,
45: x_num_dim_tables) THEN
46:
47: -- The parent table was or is going to be loaded in this process
48: -- So, we need to check if this parent table is already loaded or not
49: -- If it is not already loaded the dimension table cannot be
50: -- loaded right now.
51:
52: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_parent_table,
53: x_loaded_tables,
54: x_num_loaded_tables) THEN
55: h_ret := FALSE;
56: EXIT;
77: | FUNCTION Can_Calculate_Sys_Table
78: +============================================================================*/
79: FUNCTION Can_Calculate_Sys_Table(
80: x_system_table IN VARCHAR2,
81: x_calculated_sys_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
82: x_num_calculated_sys_tables IN NUMBER,
83: x_system_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
84: x_num_system_tables IN NUMBER
85: ) RETURN BOOLEAN IS
79: FUNCTION Can_Calculate_Sys_Table(
80: x_system_table IN VARCHAR2,
81: x_calculated_sys_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
82: x_num_calculated_sys_tables IN NUMBER,
83: x_system_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
84: x_num_system_tables IN NUMBER
85: ) RETURN BOOLEAN IS
86:
87: TYPE t_cursor IS REF CURSOR;
106: --OPEN c_origin_tables FOR c_origin_tables_sql USING x_system_table;
107: OPEN c_origin_tables(x_system_table);
108: FETCH c_origin_tables INTO h_origin_table;
109: WHILE c_origin_tables%FOUND LOOP
110: IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_origin_table,
111: x_system_tables,
112: x_num_system_tables) THEN
113:
114: -- The origin table was or is going to be calculated in this process
115: -- So, we need to check if this origin table is already calculated or not
116: -- If it is not already calculated the the system table cannot be
117: -- calculated right now.
118:
119: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_origin_table,
120: x_calculated_sys_tables,
121: x_num_calculated_sys_tables) THEN
122: h_ret := FALSE;
123: EXIT;
151: e_unexpected_error EXCEPTION;
152:
153: c_calculation_type NUMBER;
154:
155: h_base_table BSC_UPDATE_UTIL.t_array_of_varchar2;
156: h_base_table_periodicity NUMBER;
157:
158: h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
159: h_num_system_tables NUMBER;
154:
155: h_base_table BSC_UPDATE_UTIL.t_array_of_varchar2;
156: h_base_table_periodicity NUMBER;
157:
158: h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
159: h_num_system_tables NUMBER;
160:
161: h_lst_where VARCHAR2(32700);
162: h_i NUMBER;
175: DELETE FROM bsc_db_calculations
176: WHERE table_name = p_base_table AND calculation_type = c_calculation_type;
177:
178: -- Get base table periodicity
179: h_base_table_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(p_base_table);
180: IF h_base_table_periodicity IS NULL THEN
181: RAISE e_unexpected_error;
182: END IF;
183:
219: RETURN TRUE;
220:
221: EXCEPTION
222: WHEN e_unexpected_error THEN
223: x_error_message := BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR');
224: WHEN OTHERS THEN
225: x_error_message := SQLERRM;
226: RETURN FALSE;
227:
382:
383: h_b BOOLEAN;
384: h_i NUMBER;
385:
386: h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
387: h_num_input_tables NUMBER;
388:
389: h_indicators BSC_UPDATE_UTIL.t_array_of_number;
390: h_num_indicators NUMBER;
385:
386: h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
387: h_num_input_tables NUMBER;
388:
389: h_indicators BSC_UPDATE_UTIL.t_array_of_number;
390: h_num_indicators NUMBER;
391:
392: h_calendars BSC_UPDATE_UTIL.t_array_of_number;
393: h_num_calendars NUMBER;
388:
389: h_indicators BSC_UPDATE_UTIL.t_array_of_number;
390: h_num_indicators NUMBER;
391:
392: h_calendars BSC_UPDATE_UTIL.t_array_of_number;
393: h_num_calendars NUMBER;
394:
395: h_edw_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
396: h_num_edw_dim_tables NUMBER;
391:
392: h_calendars BSC_UPDATE_UTIL.t_array_of_number;
393: h_num_calendars NUMBER;
394:
395: h_edw_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
396: h_num_edw_dim_tables NUMBER;
397:
398: TYPE t_cursor IS REF CURSOR;
399: h_cursor t_cursor;
457: -- ----------------------------------------------------------------------------------
458: */
459:
460: h_sql := 'alter session set hash_area_size=50000000';
461: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
462:
463: h_sql := 'alter session set sort_area_size=50000000';
464: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
465:
460: h_sql := 'alter session set hash_area_size=50000000';
461: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
462:
463: h_sql := 'alter session set sort_area_size=50000000';
464: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
465:
466: IF BSC_UPDATE_UTIL.is_parallel THEN
467: COMMIT;
468: h_sql := 'alter session enable parallel dml';
462:
463: h_sql := 'alter session set sort_area_size=50000000';
464: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
465:
466: IF BSC_UPDATE_UTIL.is_parallel THEN
467: COMMIT;
468: h_sql := 'alter session enable parallel dml';
469: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
470: COMMIT;
465:
466: IF BSC_UPDATE_UTIL.is_parallel THEN
467: COMMIT;
468: h_sql := 'alter session enable parallel dml';
469: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
470: COMMIT;
471: END IF;
472:
473: -- Get the pending process id
491: END IF;
492: END IF;
493:
494: -- Write process_id to log file
495: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'PROCESS_ID')||
496: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
497: ' '||TO_CHAR(g_process_id), BSC_UPDATE_LOG.LOG);
498:
499: BSC_UPDATE_LOG.Write_Line_Log(USERENV('SESSIONID'), BSC_UPDATE_LOG.LOG);
492: END IF;
493:
494: -- Write process_id to log file
495: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'PROCESS_ID')||
496: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
497: ' '||TO_CHAR(g_process_id), BSC_UPDATE_LOG.LOG);
498:
499: BSC_UPDATE_LOG.Write_Line_Log(USERENV('SESSIONID'), BSC_UPDATE_LOG.LOG);
500:
541: g_kpi_mode := FALSE;
542: IF x_process_name = PC_LOADER_PROCESS OR x_process_name = PC_LOAD_DIMENSIONS THEN
543: -- Decompose the list in x_parameter_1 into the array g_indicators
544: IF x_parameter_1 IS NOT NULL THEN
545: g_num_indicators := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_parameter_1,
546: g_indicators,
547: ',');
548: g_kpi_mode := TRUE;
549: END IF;
584: -- if none of the objectives are implemented in AW
585: h_load_type_into_aw := FALSE;
586: IF g_kpi_mode THEN
587: FOR h_i IN 1..g_num_indicators LOOP
588: IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(g_indicators(h_i)) = 2 THEN
589: h_load_type_into_aw := TRUE;
590: EXIT;
591: END IF;
592: END LOOP;
591: END IF;
592: END LOOP;
593: ELSE
594: -- running by input tables
595: IF BSC_UPDATE_UTIL.Exists_AW_Kpi THEN
596: h_load_type_into_aw := TRUE;
597: END IF;
598: END IF;
599: IF h_load_type_into_aw THEN
718: ELSIF x_process_name = PC_YEAR_CHANGE_PROCESS THEN
719: -- Run the year change process
720:
721: -- Decompose the list in x_parameter_1 into the array h_calendars
722: h_num_calendars := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_parameter_1,
723: h_calendars,
724: ',');
725:
726: -- LOCKING: Lock the indicators in cascade mode and in read mode that are using
800:
801: COMMIT;
802:
803: -- Write Program completed to log file
804: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
805: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
806: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
807: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
808:
801: COMMIT;
802:
803: -- Write Program completed to log file
804: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
805: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
806: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
807: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
808:
809: -- Delete records in the temporal table used for big 'in' conditions
803: -- Write Program completed to log file
804: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
805: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
806: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
807: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
808:
809: -- Delete records in the temporal table used for big 'in' conditions
810: BSC_APPS.Init_Big_In_Cond_Table;
811:
819: end if;
820:
821: --Enable parallel: need to disable parallel here since I am getting ORA-12838 error when concurrent manager
822: -- is closing the concurrent program.
823: IF BSC_UPDATE_UTIL.is_parallel THEN
824: h_sql := 'alter session disable parallel dml';
825: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
826: commit;
827: END IF;
821: --Enable parallel: need to disable parallel here since I am getting ORA-12838 error when concurrent manager
822: -- is closing the concurrent program.
823: IF BSC_UPDATE_UTIL.is_parallel THEN
824: h_sql := 'alter session disable parallel dml';
825: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
826: commit;
827: END IF;
828:
829: COMMIT;
832: WHEN e_no_pending_process THEN
833: -- Delete records in the temporal table used for big 'in' conditions
834: BSC_APPS.Init_Big_In_Cond_Table;
835:
836: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_NO_PENDING_PROCESS'),
837: x_source => 'BSC_UPDATE.Execute_Update_Process',
838: x_mode => 'I');
839: COMMIT;
840:
839: COMMIT;
840:
841: BSC_UPDATE_LOG.Write_Errors_To_Log;
842:
843: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
844: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
845: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
846: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
847:
840:
841: BSC_UPDATE_LOG.Write_Errors_To_Log;
842:
843: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
844: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
845: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
846: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
847:
848: WHEN e_exists_prototype_indicators THEN
842:
843: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
844: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
845: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
846: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
847:
848: WHEN e_exists_prototype_indicators THEN
849: -- Delete records in the temporal table used for big 'in' conditions
850: BSC_APPS.Init_Big_In_Cond_Table;
849: -- Delete records in the temporal table used for big 'in' conditions
850: BSC_APPS.Init_Big_In_Cond_Table;
851:
852: BSC_MESSAGE.flush;
853: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_METADATA_PEND_CHANGES'),
854: x_source => 'BSC_UPDATE.Execute_Update_Process',
855: x_mode => 'I');
856: COMMIT;
857:
861: h_b := Set_PStatus_Finished(PC_ERROR_STATUS);
862: COMMIT;
863: END IF;
864:
865: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
866: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
867: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
868: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
869:
862: COMMIT;
863: END IF;
864:
865: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
866: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
867: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
868: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
869:
870: WHEN e_update_error THEN
864:
865: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
866: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
867: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
868: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
869:
870: WHEN e_update_error THEN
871: ROLLBACK;
872:
873: -- Delete records in the temporal table used for big 'in' conditions
874: BSC_APPS.Init_Big_In_Cond_Table;
875:
876: BSC_MESSAGE.flush;
877: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED'),
878: x_source => 'BSC_UPDATE.Execute_Update_Process',
879: x_mode => 'I');
880: COMMIT;
881:
885: h_b := Set_PStatus_Finished(PC_ERROR_STATUS);
886: COMMIT;
887: END IF;
888:
889: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
890: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
891: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
892: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
893:
886: COMMIT;
887: END IF;
888:
889: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
890: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
891: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
892: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
893:
894: --catching the error in creating table for bug 3899523
888:
889: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
890: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
891: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
892: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
893:
894: --catching the error in creating table for bug 3899523
895: --added 3899523
896: WHEN e_tmp_tbl_create_error THEN
910: h_b := Set_PStatus_Finished(PC_ERROR_STATUS);
911: COMMIT;
912: END IF;
913:
914: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
915: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
916: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
917: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
918:
911: COMMIT;
912: END IF;
913:
914: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
915: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
916: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
917: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
918:
919: --Fix bug#3923207: import dbi plans into bsc benchmarks
913:
914: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
915: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
916: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
917: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
918:
919: --Fix bug#3923207: import dbi plans into bsc benchmarks
920: WHEN e_import_dbi_plans THEN
921: ROLLBACK;
935: h_b := Set_PStatus_Finished(PC_ERROR_STATUS);
936: COMMIT;
937: END IF;
938:
939: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
940: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
941: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
942: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
943:
936: COMMIT;
937: END IF;
938:
939: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
940: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
941: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
942: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
943:
944: --LOCKING
938:
939: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
940: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
941: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
942: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
943:
944: --LOCKING
945: WHEN e_could_not_get_lock THEN
946: ROLLBACK;
962: h_b := Set_PStatus_Finished(PC_ERROR_STATUS);
963: COMMIT;
964: END IF;
965:
966: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
967: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
968: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
969: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
970:
963: COMMIT;
964: END IF;
965:
966: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
967: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
968: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
969: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
970:
971: WHEN OTHERS THEN
965:
966: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
967: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
968: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
969: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
970:
971: WHEN OTHERS THEN
972: ROLLBACK;
973:
986: h_b := Set_PStatus_Finished(PC_ERROR_STATUS);
987: COMMIT;
988: END IF;
989:
990: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
991: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
992: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
993: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
994:
987: COMMIT;
988: END IF;
989:
990: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
991: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
992: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
993: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
994:
995: END Execute_Update_Process;
989:
990: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
991: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
992: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
993: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
994:
995: END Execute_Update_Process;
996:
997:
1087: h_end_year NUMBER;
1088:
1089: h_sql VARCHAR2(2000);
1090: h_b BOOLEAN;
1091: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
1092: l_num_bind_vars NUMBER;
1093:
1094: h_periodicity_id NUMBER;
1095: h_yearly_flag NUMBER;
1094: h_periodicity_id NUMBER;
1095: h_yearly_flag NUMBER;
1096: h_current_period NUMBER;
1097:
1098: h_base_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
1099: h_num_base_tables NUMBER;
1100:
1101: h_calendar_source VARCHAR2(20);
1102: h_count NUMBER;
1105: h_message VARCHAR2(4000);
1106: h_calendar_name VARCHAR2(2000);
1107:
1108: --AW_INTEGRATION: New variables
1109: h_aw_indicators BSC_UPDATE_UTIL.t_array_of_number;
1110: h_num_aw_indicators NUMBER;
1111: h_aw_table_name VARCHAR2(30);
1112: h_aw_flag BOOLEAN;
1113: h_i NUMBER;
1111: h_aw_table_name VARCHAR2(30);
1112: h_aw_flag BOOLEAN;
1113: h_i NUMBER;
1114: h_kpi_list dbms_sql.varchar2_table;
1115: h_aw_base_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
1116: h_num_aw_base_tables NUMBER;
1117:
1118: --LOCKING: new variables
1119: h_error_message VARCHAR2(2000);
1140: END IF;
1141:
1142: --LOCKING: review no commit between this point and the commit to release the locks
1143:
1144: h_calendar_name := BSC_UPDATE_UTIL.Get_Calendar_Name(x_calendar_id);
1145:
1146: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_YEARCHANGE_PROCESS')||' ('||
1147: h_calendar_name||')', BSC_UPDATE_LOG.OUTPUT);
1148:
1142: --LOCKING: review no commit between this point and the commit to release the locks
1143:
1144: h_calendar_name := BSC_UPDATE_UTIL.Get_Calendar_Name(x_calendar_id);
1145:
1146: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_YEARCHANGE_PROCESS')||' ('||
1147: h_calendar_name||')', BSC_UPDATE_LOG.OUTPUT);
1148:
1149: -- Get the current fiscal year
1150: h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(x_calendar_id);
1146: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_YEARCHANGE_PROCESS')||' ('||
1147: h_calendar_name||')', BSC_UPDATE_LOG.OUTPUT);
1148:
1149: -- Get the current fiscal year
1150: h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(x_calendar_id);
1151: h_new_fiscal_year := h_current_fy + 1;
1152:
1153: -- Get calendar source
1154: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
1150: h_current_fy := BSC_UPDATE_UTIL.Get_Calendar_Fiscal_Year(x_calendar_id);
1151: h_new_fiscal_year := h_current_fy + 1;
1152:
1153: -- Get calendar source
1154: h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(x_calendar_id);
1155:
1156: -- BSC-BIS-DIMENSIONS: If it is a BIS calendar we need to validate that the new fiscal
1157: -- year is already available in BSC
1158: IF h_calendar_source = 'PMF' THEN
1162: WHERE calendar_id = x_calendar_id AND year = h_new_fiscal_year;
1163:
1164: IF h_count = 0 THEN
1165: -- The new fiscal year is not available
1166: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_DBI_YEAR_NOT_AVAILABLE');
1167: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'YEAR', TO_CHAR(h_new_fiscal_year));
1168: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'CALENDAR_NAME', h_calendar_name);
1169: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
1170: --LOCKING: commit to release locks
1163:
1164: IF h_count = 0 THEN
1165: -- The new fiscal year is not available
1166: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_DBI_YEAR_NOT_AVAILABLE');
1167: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'YEAR', TO_CHAR(h_new_fiscal_year));
1168: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'CALENDAR_NAME', h_calendar_name);
1169: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
1170: --LOCKING: commit to release locks
1171: COMMIT;
1164: IF h_count = 0 THEN
1165: -- The new fiscal year is not available
1166: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_DBI_YEAR_NOT_AVAILABLE');
1167: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'YEAR', TO_CHAR(h_new_fiscal_year));
1168: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'CALENDAR_NAME', h_calendar_name);
1169: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
1170: --LOCKING: commit to release locks
1171: COMMIT;
1172: RETURN FALSE;
1176: -- Fix bug#3636273 We are going to drop indexes to avoid commit in the middle
1177: --LOCKING: we are not going to drop indexes in calendar tables. If we do so, we cannot
1178: --run year change process on different calendars at the same time
1179: --IF h_calendar_source = 'BSC' THEN
1180: -- IF NOT BSC_UPDATE_UTIL.Init_Calendar_Tables(x_calendar_id, 1) THEN
1181: -- RAISE e_unexpected_error;
1182: -- END IF;
1183: --END IF;
1184:
1183: --END IF;
1184:
1185: -- Write new current fiscal year
1186: -- LOCKING: There is not commit inside this function
1187: IF NOT BSC_UPDATE_UTIL.Set_Calendar_Fiscal_Year(x_calendar_id, (h_current_fy + 1)) THEN
1188: RAISE e_unexpected_error;
1189: END IF;
1190:
1191: -- Init calendar tables (BSC Calendars)
1193: -- Removing code for EDW. This was never supported.
1194: IF h_calendar_source = 'BSC' THEN
1195: -- Fix bug#3636273 add second parameter.
1196: -- LOCKING: no commit inside this function
1197: IF NOT BSC_UPDATE_UTIL.Init_Calendar_Tables(x_calendar_id) THEN
1198: RAISE e_unexpected_error;
1199: END IF;
1200: END IF;
1201:
1204: FETCH c_indicators INTO h_indicator;
1205: WHILE c_indicators%FOUND LOOP
1206:
1207: -- AW_INTEGRATION: Add AW indicators to the array x_aw_indicators
1208: IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(h_indicator) = 2 THEN
1209: h_num_aw_indicators := h_num_aw_indicators + 1;
1210: h_aw_indicators(h_num_aw_indicators) := h_indicator;
1211: END IF;
1212:
1255: WHERE indicator = h_indicator;
1256:
1257:
1258: -- Update the name of period of indicators in BSC_KPI_DEFAULTS_TL table
1259: IF NOT BSC_UPDATE_UTIL.Update_Kpi_Period_Name(h_indicator) THEN
1260: RAISE e_unexpected_error;
1261: END IF;
1262:
1263: -- Update date of indicator
1291: -- the AW table created for the base table. I will add them to the array
1292: -- h_aw_base_tables to truncate them later. We cannot do DDL here.
1293: -- Also the base table is not added to
1294: -- to array h_base_tables because that array is for base table for MV indicators
1295: h_aw_flag := BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_table_info.table_name);
1296: IF h_aw_flag THEN
1297: -- Base table for AW indicators
1298: h_num_aw_base_tables := h_num_aw_base_tables + 1;
1299: h_aw_base_tables(h_num_aw_base_tables) := h_table_info.table_name;
1311: -- In this case we need to truncate the projection table and we do not need to touch
1312: -- the base table since it contains only actuals.
1313: h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_table_info.table_name);
1314: IF h_proj_tbl_name IS NOT NULL THEN
1315: BSC_UPDATE_UTIL.Truncate_Table_AT(h_proj_tbl_name);
1316: ELSE
1317: IF h_table_info.yearly_flag = 1 THEN
1318: -- Annual periodicity
1319: h_sql := 'DELETE FROM '||h_table_info.table_name||
1321: IF NOT h_aw_flag THEN
1322: h_sql := h_sql||' AND PERIODICITY_ID = :2';
1323: l_bind_vars_values(1):=h_table_info.current_period;
1324: l_bind_vars_values(2):=h_table_info.periodicity_id;
1325: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
1326: ELSE
1327: l_bind_vars_values(1):=h_table_info.current_period;
1328: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
1329: END IF;
1324: l_bind_vars_values(2):=h_table_info.periodicity_id;
1325: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
1326: ELSE
1327: l_bind_vars_values(1):=h_table_info.current_period;
1328: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
1329: END IF;
1330: ELSE
1331: -- Other periodicity
1332: h_sql := 'DELETE FROM '||h_table_info.table_name||
1335: h_sql := h_sql||' AND PERIODICITY_ID = :3';
1336: l_bind_vars_values(1):=h_current_fy;
1337: l_bind_vars_values(2):=h_table_info.current_period;
1338: l_bind_vars_values(3):=h_table_info.periodicity_id;
1339: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
1340: ELSE
1341: l_bind_vars_values(1):=h_current_fy;
1342: l_bind_vars_values(2):=h_table_info.current_period;
1343: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
1339: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
1340: ELSE
1341: l_bind_vars_values(1):=h_current_fy;
1342: l_bind_vars_values(2):=h_table_info.current_period;
1343: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
1344: END IF;
1345: END IF;
1346:
1347: -- Delete projection for other periodicities
1357: h_sql := 'DELETE FROM '||h_table_info.table_name||
1358: ' WHERE YEAR > :1 AND TYPE = 0 AND PERIODICITY_ID = :2';
1359: l_bind_vars_values(1):=h_current_fy;
1360: l_bind_vars_values(2):=h_periodicity_id;
1361: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
1362: ELSE
1363: -- Get current period of this periodicity based on the current period
1364: -- of the base periodicity of the base table
1365: h_current_period := BSC_UPDATE_UTIL.Get_Period_Other_Periodicity(
1361: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
1362: ELSE
1363: -- Get current period of this periodicity based on the current period
1364: -- of the base periodicity of the base table
1365: h_current_period := BSC_UPDATE_UTIL.Get_Period_Other_Periodicity(
1366: h_periodicity_id,
1367: x_calendar_id,
1368: h_yearly_flag,
1369: h_current_fy,
1376: ' WHERE YEAR = :1 AND PERIOD > :2 AND TYPE = 0 AND PERIODICITY_ID = :3';
1377: l_bind_vars_values(1):=h_current_fy;
1378: l_bind_vars_values(2):=h_current_period;
1379: l_bind_vars_values(3):=h_periodicity_id;
1380: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
1381: END IF;
1382: END LOOP;
1383: CLOSE c_other_periodicities;
1384: END IF;
1389: l_bind_vars_values.delete;
1390: OPEN c_tables(1, x_calendar_id);
1391: FETCH c_tables INTO h_table_info;
1392: WHILE c_tables%FOUND LOOP
1393: IF BSC_UPDATE_UTIL.Table_Exists(h_table_info.table_name) THEN
1394: --ENH_B_TABLES_PERF: In the new strategy the base table may have a projection table
1395: -- In this case we need to truncate the projection table and we do not need to touch
1396: -- the base table since it contains only actuals.
1397: h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_table_info.table_name);
1395: -- In this case we need to truncate the projection table and we do not need to touch
1396: -- the base table since it contains only actuals.
1397: h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_table_info.table_name);
1398: IF h_proj_tbl_name IS NOT NULL THEN
1399: BSC_UPDATE_UTIL.Truncate_Table_AT(h_proj_tbl_name);
1400: ELSE
1401: IF h_table_info.yearly_flag = 1 THEN
1402: -- Annual periodicity
1403: h_sql := 'DELETE FROM '||h_table_info.table_name||
1402: -- Annual periodicity
1403: h_sql := 'DELETE FROM '||h_table_info.table_name||
1404: ' WHERE YEAR > :1 AND TYPE = 0';
1405: l_bind_vars_values(1):=h_table_info.current_period;
1406: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
1407: ELSE
1408: -- Other periodicity
1409: h_sql := 'DELETE FROM '||h_table_info.table_name||
1410: ' WHERE YEAR = :1 AND PERIOD > :2 AND TYPE = 0';
1409: h_sql := 'DELETE FROM '||h_table_info.table_name||
1410: ' WHERE YEAR = :1 AND PERIOD > :2 AND TYPE = 0';
1411: l_bind_vars_values(1):=h_current_fy;
1412: l_bind_vars_values(2):=h_table_info.current_period;
1413: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
1414: END IF;
1415: END IF;
1416: END IF;
1417: FETCH c_tables INTO h_table_info;
1462: -- Fix bug#3636273 We are going to create indexes here to avoid commit in the middle
1463: --LOCKING: we are not going to drop indexes in calendar tables. If we do so, we cannot
1464: --run year change process on different calendars at the same time
1465: --IF h_calendar_source = 'BSC' THEN
1466: -- IF NOT BSC_UPDATE_UTIL.Init_Calendar_Tables(x_calendar_id, 3) THEN
1467: -- RAISE e_unexpected_error;
1468: -- END IF;
1469: --END IF;
1470:
1491: RAISE e_could_not_get_lock;
1492: END IF;
1493:
1494: --LOCKING: Call the autonomous transaction function
1495: BSC_UPDATE_UTIL.Truncate_Table_AT(h_table_name);
1496:
1497: --LOCKING: commit to release locks
1498: COMMIT;
1499: END LOOP;
1517:
1518: h_aw_table_name := BSC_UPDATE_BASE.Get_Base_AW_Table_Name(h_aw_base_tables(h_i));
1519:
1520: --LOCKING: call the autonomous transaction function
1521: BSC_UPDATE_UTIL.Truncate_Table_AT(h_aw_table_name);
1522:
1523: --LOCKING: commit to release locks
1524: COMMIT;
1525: END LOOP;*/
1525: END LOOP;*/
1526:
1527: -- AW_INTEGRATION: Refresh indicators cubes
1528: FOR h_i IN 1..h_num_aw_indicators LOOP
1529: IF BSC_UPDATE_UTIL.Is_Kpi_In_Production(h_aw_indicators(h_i)) THEN
1530: --LOCKING: Lock the objects required to refresh the AW indicator cubes
1531: IF NOT BSC_UPDATE_LOCK.Lock_Refresh_AW_Indicator(h_aw_indicators(h_i)) THEN
1532: RAISE e_could_not_get_lock;
1533: END IF;
1546: IF NOT BSC_UPDATE_LOCK.Lock_Update_Date THEN
1547: RAISE e_could_not_get_lock;
1548: END IF;
1549:
1550: IF NOT BSC_UPDATE_UTIL.Write_Init_Variable_Value('UPDATE_DATE', TO_CHAR(SYSDATE, 'DD/MM/YYYY')) THEN
1551: RAISE e_unexpected_error;
1552: END IF;
1553:
1554: -- Fix bug#3636273 Move this call here.
1552: END IF;
1553:
1554: -- Fix bug#3636273 Move this call here.
1555: -- Update the system time stamp
1556: BSC_UPDATE_UTIL.Update_System_Time_Stamp;
1557:
1558: --LOCKING: commit to release locks
1559: COMMIT;
1560:
1573: END IF;
1574:
1575: --AW_INTEGRATION: call aw api to import calendars into aw world
1576: --LOCKING: call the autonomous transaction
1577: BSC_UPDATE_UTIL.Load_Calendar_Into_AW_AT(x_calendar_id);
1578:
1579: --LOCKING: commit to release locks
1580: COMMIT;
1581: END IF;
1579: --LOCKING: commit to release locks
1580: COMMIT;
1581: END IF;
1582:
1583: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_YEARCHANGE_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
1584:
1585: RETURN TRUE;
1586:
1587: EXCEPTION
1586:
1587: EXCEPTION
1588: WHEN e_unexpected_error THEN
1589: ROLLBACK;
1590: BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_YEARCHANGE_FAILED'),
1591: x_source => 'BSC_UPDATE.Execute_Year_Change_Process');
1592:
1593: RETURN FALSE;
1594:
1765: /*===========================================================================+
1766: | FUNCTION Get_EDW_Dims_In_Input_Tables
1767: +============================================================================*/
1768: FUNCTION Get_EDW_Dims_In_Input_Tables (
1769: x_edw_dim_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
1770: x_num_edw_dim_tables IN OUT NOCOPY NUMBER
1771: ) RETURN BOOLEAN IS
1772:
1773: TYPE t_cursor IS REF CURSOR;
1824: /*===========================================================================+
1825: | FUNCTION Get_Indicators_To_Color
1826: +============================================================================*/
1827: FUNCTION Get_Indicators_To_Color(
1828: x_base_tables_to_color IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1829: x_num_base_tables_to_color IN NUMBER,
1830: x_color_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
1831: x_num_color_indicators IN OUT NOCOPY NUMBER
1832: ) RETURN BOOLEAN IS
1826: +============================================================================*/
1827: FUNCTION Get_Indicators_To_Color(
1828: x_base_tables_to_color IN BSC_UPDATE_UTIL.t_array_of_varchar2,
1829: x_num_base_tables_to_color IN NUMBER,
1830: x_color_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
1831: x_num_color_indicators IN OUT NOCOPY NUMBER
1832: ) RETURN BOOLEAN IS
1833:
1834: e_unexpected_error EXCEPTION;
1832: ) RETURN BOOLEAN IS
1833:
1834: e_unexpected_error EXCEPTION;
1835:
1836: h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
1837: h_num_system_tables NUMBER;
1838: h_i NUMBER;
1839: h_lst_where VARCHAR2(32700);
1840:
1886:
1887: RETURN TRUE;
1888: EXCEPTION
1889: WHEN e_unexpected_error THEN
1890: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_COLORKPILIST_FAILED'),
1891: x_source => 'BSC_UPDATE.Get_Indicators_To_Color');
1892: RETURN FALSE;
1893:
1894: WHEN OTHERS THEN
2101: /*===========================================================================+
2102: | FUNCTION Get_Process_Input_Tables
2103: +============================================================================*/
2104: FUNCTION Get_Process_Input_Tables (
2105: x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2106: x_num_input_tables IN OUT NOCOPY NUMBER,
2107: x_status IN VARCHAR2
2108: ) RETURN BOOLEAN IS
2109:
2173: /*===========================================================================+
2174: | FUNCTION Import_ITables_From_DBSrc
2175: +============================================================================*/
2176: FUNCTION Import_ITables_From_DBSrc(
2177: x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2178: x_num_input_tables IN NUMBER
2179: ) RETURN BOOLEAN IS
2180:
2181: e_unexpected_error EXCEPTION;
2195:
2196: FOR h_i IN 1 .. x_num_input_tables LOOP
2197: h_input_table := x_input_tables(h_i);
2198:
2199: IF NOT BSC_UPDATE_UTIL.Get_Input_Table_Source(h_input_table, h_source_type, h_source_name) THEN
2200: RAISE e_unexpected_error;
2201: END IF;
2202:
2203: IF h_source_type = 4 THEN
2202:
2203: IF h_source_type = 4 THEN
2204: -- Stored procedure
2205:
2206: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_IMPORTING_TO_ITABLE');
2207: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'INPUT_TABLE', h_input_table);
2208: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2209:
2210: h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SOURCE')||
2203: IF h_source_type = 4 THEN
2204: -- Stored procedure
2205:
2206: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_IMPORTING_TO_ITABLE');
2207: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'INPUT_TABLE', h_input_table);
2208: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2209:
2210: h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SOURCE')||
2211: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||h_source_name;
2206: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_IMPORTING_TO_ITABLE');
2207: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'INPUT_TABLE', h_input_table);
2208: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2209:
2210: h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SOURCE')||
2211: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||h_source_name;
2212: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2213:
2214: -- LOCKING: Lock required objects
2207: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'INPUT_TABLE', h_input_table);
2208: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2209:
2210: h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SOURCE')||
2211: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||h_source_name;
2212: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2213:
2214: -- LOCKING: Lock required objects
2215: IF NOT BSC_UPDATE_LOCK.Lock_Import_ITable(h_input_table) THEN
2236: x_source => 'BSC_UPDATE.Import_ITables_From_DBSrc');
2237: RETURN FALSE;
2238:
2239: WHEN e_unexpected_error THEN
2240: BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_IMPORT_ITABLES_FAILED'),
2241: x_source => 'BSC_UPDATE.Import_ITables_From_DBSrc');
2242: RETURN FALSE;
2243:
2244: WHEN OTHERS THEN
2252: /*===========================================================================+
2253: | FUNCTION Import_ITables_From_DBSrc_AT
2254: +============================================================================*/
2255: FUNCTION Import_ITables_From_DBSrc_AT(
2256: x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2257: x_num_input_tables IN NUMBER
2258: ) RETURN BOOLEAN IS
2259: PRAGMA AUTONOMOUS_TRANSACTION;
2260: h_b BOOLEAN;
2301: /*h_procedure := 'PROCEDURE';*/
2302:
2303: -- Delete data from the input table
2304: h_sql := 'DELETE FROM '||x_input_table;
2305: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2306: COMMIT;
2307:
2308: /*h_stored_proc := UPPER(x_stored_proc);
2309:
2315: IF h_status = 'VALID' THEN*/
2316: -- Execute the stored procedure to populate the input table
2317: BEGIN
2318: h_sql := 'BEGIN '||x_stored_proc||'; END;';
2319: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
2320:
2321: -- Update the stage of the input table to UPLOADED
2322: IF NOT Update_Stage_Input_Table(x_input_table, LC_UPLOADED_STAGE) THEN
2323: RAISE e_unexpected_error;
2329: IF NOT Update_Status_Input_Table(x_input_table, LC_ERROR_STATUS, LC_UPLOAD_SP_EXECUTION_ERR) THEN
2330: RAISE e_unexpected_error;
2331: END IF;
2332:
2333: h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
2334: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
2335: BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_FAILED');
2336: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2337: BSC_UPDATE_LOG.Write_Line_Log(SQLERRM, BSC_UPDATE_LOG.OUTPUT);
2330: RAISE e_unexpected_error;
2331: END IF;
2332:
2333: h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
2334: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
2335: BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_FAILED');
2336: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2337: BSC_UPDATE_LOG.Write_Line_Log(SQLERRM, BSC_UPDATE_LOG.OUTPUT);
2338: END;
2331: END IF;
2332:
2333: h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
2334: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
2335: BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_FAILED');
2336: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2337: BSC_UPDATE_LOG.Write_Line_Log(SQLERRM, BSC_UPDATE_LOG.OUTPUT);
2338: END;
2339: /*
2342: IF NOT Update_Status_Input_Table(x_input_table, LC_ERROR_STATUS, LC_UPLOAD_SP_INVALID_ERR) THEN
2343: RAISE e_unexpected_error;
2344: END IF;
2345:
2346: h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
2347: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
2348: BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_IS_INVALID');
2349: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2350: END IF;
2343: RAISE e_unexpected_error;
2344: END IF;
2345:
2346: h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
2347: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
2348: BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_IS_INVALID');
2349: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2350: END IF;
2351: ELSE
2344: END IF;
2345:
2346: h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
2347: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
2348: BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_IS_INVALID');
2349: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2350: END IF;
2351: ELSE
2352: -- Stored procedure does not exists
2353: IF NOT Update_Status_Input_Table(x_input_table, LC_ERROR_STATUS, LC_UPLOAD_SP_NOT_FOUND_ERR) THEN
2354: RAISE e_unexpected_error;
2355: END IF;
2356:
2357: h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
2358: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
2359: BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_NOT_FOUND');
2360: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2361: END IF;
2354: RAISE e_unexpected_error;
2355: END IF;
2356:
2357: h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
2358: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
2359: BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_NOT_FOUND');
2360: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2361: END IF;
2362: CLOSE c_status;
2355: END IF;
2356:
2357: h_message := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'ERROR')||
2358: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||' '||
2359: BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_NOT_FOUND');
2360: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2361: END IF;
2362: CLOSE c_status;
2363: */
2366: RETURN TRUE;
2367:
2368: EXCEPTION
2369: WHEN e_unexpected_error THEN
2370: BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_IMPORT_ITABLES_FAILED'),
2371: x_source => 'BSC_UPDATE.Import_ITable_StoredProc');
2372: RETURN FALSE;
2373:
2374: WHEN OTHERS THEN
2398: /*===========================================================================+
2399: | FUNCTION Insert_Affected_Tables
2400: +============================================================================*/
2401: FUNCTION Insert_Affected_Tables (
2402: x_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2403: x_num_tables IN NUMBER,
2404: x_affected_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2405: x_num_affected_tables IN OUT NOCOPY NUMBER
2406: ) RETURN BOOLEAN IS
2400: +============================================================================*/
2401: FUNCTION Insert_Affected_Tables (
2402: x_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2403: x_num_tables IN NUMBER,
2404: x_affected_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2405: x_num_affected_tables IN OUT NOCOPY NUMBER
2406: ) RETURN BOOLEAN IS
2407:
2408: e_unexpected_error EXCEPTION;
2414: SELECT table_name
2415: FROM bsc_db_tables_rels
2416: WHERE source_table_name = p_source_table_name;
2417:
2418: h_new_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2419: h_num_new_tables NUMBER;
2420:
2421: h_i NUMBER;
2422: h_sql VARCHAR2(32700);
2436: */
2437: OPEN c_tables(x_tables(h_i));
2438: FETCH c_tables INTO h_table_name;
2439: WHILE c_tables%FOUND LOOP
2440: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_table_name, x_affected_tables, x_num_affected_tables) THEN
2441: x_num_affected_tables := x_num_affected_tables + 1;
2442: x_affected_tables(x_num_affected_tables) := h_table_name;
2443:
2444: h_num_new_tables := h_num_new_tables + 1;
2460: RETURN TRUE;
2461:
2462: EXCEPTION
2463: WHEN e_unexpected_error THEN
2464: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_TABLES_INSERT_FAILED'),
2465: x_source => 'BSC_UPDATE.Insert_Affected_Tables');
2466: RETURN FALSE;
2467:
2468: WHEN OTHERS THEN
2476: /*===========================================================================+
2477: | FUNCTION Load_Dim_Input_Tables
2478: +============================================================================*/
2479: FUNCTION Load_Dim_Input_Tables(
2480: x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2481: x_num_input_tables IN NUMBER
2482: ) RETURN BOOLEAN IS
2483:
2484: e_unexpected_error EXCEPTION;
2484: e_unexpected_error EXCEPTION;
2485: --LOCKING
2486: e_could_not_get_lock EXCEPTION;
2487:
2488: h_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2489: h_num_dim_tables NUMBER;
2490:
2491: h_loaded_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2492: h_num_loaded_tables NUMBER;
2487:
2488: h_dim_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2489: h_num_dim_tables NUMBER;
2490:
2491: h_loaded_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2492: h_num_loaded_tables NUMBER;
2493:
2494: h_i NUMBER;
2495: h_b BOOLEAN;
2505: h_dim_table_type NUMBER;
2506: h_dim_level_list dbms_sql.varchar2_table;
2507: h_dim_was_loaded BOOLEAN;
2508:
2509: h_input_tables_err_status BSC_UPDATE_UTIL.t_array_of_varchar2;
2510: h_num_input_tables_err_status NUMBER;
2511:
2512: CURSOR c_input_tables_err_status IS
2513: SELECT input_table_name
2518: h_num_dim_tables := 0;
2519: h_num_loaded_tables := 0;
2520: h_num_input_tables_err_status := 0;
2521:
2522: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPD_PROCESS'), BSC_UPDATE_LOG.OUTPUT);
2523:
2524: -- Get input table with status error
2525: OPEN c_input_tables_err_status;
2526: LOOP
2553: WHILE h_num_loaded_tables <> h_num_dim_tables LOOP
2554: FOR h_i IN 1 .. h_num_dim_tables LOOP
2555: h_dim_table := h_dim_tables(h_i);
2556:
2557: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_dim_table,
2558: h_loaded_tables,
2559: h_num_loaded_tables) THEN
2560: -- The table has not been calculated yet
2561:
2584:
2585: -- AW_INTEGRATION: init this variable
2586: h_dim_was_loaded := FALSE;
2587:
2588: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_input_table,
2589: h_input_tables_err_status,
2590: h_num_input_tables_err_status) THEN
2591: -- Input table is ok, there was no error before loading it from stored procedure
2592:
2590: h_num_input_tables_err_status) THEN
2591: -- Input table is ok, there was no error before loading it from stored procedure
2592:
2593: -- Know if the input table is empty or not.
2594: h_table_has_any_row := BSC_UPDATE_UTIL.Table_Has_Any_Row(h_input_table);
2595:
2596: IF h_table_has_any_row IS NULL THEN
2597: RAISE e_unexpected_error;
2598: END IF;
2600: IF h_table_has_any_row THEN
2601: -- Input table has records. The table can be processed
2602:
2603: -- Validate input table
2604: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDATION');
2605: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
2606: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2607: --LOCKING: call the automous transaction function
2608: h_table_is_valid := BSC_UPDATE_DIM.Validate_Input_Table_AT(h_input_table, h_dim_table);
2601: -- Input table has records. The table can be processed
2602:
2603: -- Validate input table
2604: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDATION');
2605: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
2606: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2607: --LOCKING: call the automous transaction function
2608: h_table_is_valid := BSC_UPDATE_DIM.Validate_Input_Table_AT(h_input_table, h_dim_table);
2609:
2612: END IF;
2613:
2614: IF h_table_is_valid THEN
2615: -- Input table doesn't have invalid codes
2616: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDCONF');
2617: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
2618: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2619:
2620: -- Update the stage of the input table to VALIDATED
2613:
2614: IF h_table_is_valid THEN
2615: -- Input table doesn't have invalid codes
2616: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDCONF');
2617: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
2618: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2619:
2620: -- Update the stage of the input table to VALIDATED
2621: IF NOT Update_Stage_Input_Table(h_input_table, LC_VALIDATED_STAGE) THEN
2622: RAISE e_unexpected_error;
2623: END IF;
2624:
2625: -- Load the dimension table
2626: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_UPDATING_DIM_TABLE')||
2627: ' '||h_dim_table, BSC_UPDATE_LOG.OUTPUT);
2628:
2629: --LOCKING: Call the autonomous trnsaction
2630: IF NOT BSC_UPDATE_DIM.Load_Dim_Table_AT(h_dim_table, h_input_table) THEN
2638: IF NOT Update_Stage_Input_Table(h_input_table, LC_COMPLETED_STAGE) THEN
2639: RAISE e_unexpected_error;
2640: END IF;
2641:
2642: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_DIM_TABLE');
2643: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_dim_table);
2644: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2645: ELSE
2646: -- Input table has invalid codes
2639: RAISE e_unexpected_error;
2640: END IF;
2641:
2642: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_DIM_TABLE');
2643: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_dim_table);
2644: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2645: ELSE
2646: -- Input table has invalid codes
2647: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_ITABLE');
2643: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_dim_table);
2644: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2645: ELSE
2646: -- Input table has invalid codes
2647: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_ITABLE');
2648: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
2649: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2650:
2651: -- Update the status of input table to ERROR
2644: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2645: ELSE
2646: -- Input table has invalid codes
2647: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_ITABLE');
2648: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
2649: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2650:
2651: -- Update the status of input table to ERROR
2652: IF NOT Update_Status_Input_Table(h_input_table, LC_ERROR_STATUS, LC_INVALID_CODES_ERR) THEN
2656: ELSE
2657: -- Input table doesn't have any record.
2658:
2659: -- The input table cannot be processed because is an empty table
2660: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_DATA_ITABLE');
2661: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
2662: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2663:
2664: -- Update the status of the input table to NO_DATA
2657: -- Input table doesn't have any record.
2658:
2659: -- The input table cannot be processed because is an empty table
2660: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_DATA_ITABLE');
2661: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
2662: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
2663:
2664: -- Update the status of the input table to NO_DATA
2665: IF NOT Update_Status_Input_Table(h_input_table, LC_NO_DATA_STATUS, NULL) THEN
2691: END LOOP;
2692: END LOOP;
2693:
2694: -- Update the date of update process
2695: IF NOT BSC_UPDATE_UTIL.Write_Init_Variable_Value('UPDATE_DATE_DIM', TO_CHAR(SYSDATE, 'DD/MM/YYYY')) THEN
2696: RAISE e_unexpected_error;
2697: END IF;
2698: COMMIT;
2699:
2707: RAISE e_unexpected_error;
2708: END IF;
2709: COMMIT;
2710:
2711: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPDATED'), BSC_UPDATE_LOG.OUTPUT);
2712:
2713: -- Write indicator with unexisting default dimenision values
2714: IF NOT BSC_UPDATE_DIM.WriteRemovedKeyItems THEN
2715: NULL;
2728: RETURN FALSE;
2729:
2730: WHEN e_unexpected_error THEN
2731: ROLLBACK;
2732: BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPDATE_FAILED'),
2733: x_source => 'BSC_UPDATE.Load_Dim_Input_Tables');
2734: h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
2735: COMMIT;
2736:
2752: /*===========================================================================+
2753: | FUNCTION Load_Dims_Into_AW
2754: +============================================================================*/
2755: FUNCTION Load_Dims_Into_AW(
2756: x_dim_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2757: x_num_dim_tables IN NUMBER
2758: ) RETURN BOOLEAN IS
2759:
2760: e_unexpected_error EXCEPTION;
2758: ) RETURN BOOLEAN IS
2759:
2760: e_unexpected_error EXCEPTION;
2761:
2762: h_loaded_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2763: h_num_loaded_tables NUMBER;
2764:
2765: h_i NUMBER;
2766: h_b BOOLEAN;
2778: WHILE h_num_loaded_tables <> x_num_dim_tables LOOP
2779: FOR h_i IN 1 .. x_num_dim_tables LOOP
2780: h_dim_table := x_dim_tables(h_i);
2781:
2782: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_dim_table,
2783: h_loaded_tables,
2784: h_num_loaded_tables) THEN
2785: -- The table has not been calculated yet
2786:
2819: RETURN TRUE;
2820: EXCEPTION
2821: WHEN e_unexpected_error THEN
2822: ROLLBACK;
2823: BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPDATE_FAILED'),
2824: x_source => 'BSC_UPDATE.Load_Dims_Into_AW');
2825: RETURN FALSE;
2826:
2827: WHEN OTHERS THEN
2856: /*===========================================================================+
2857: | FUNCTION Process_Input_Tables
2858: +============================================================================*/
2859: FUNCTION Process_Input_Tables(
2860: x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
2861: x_num_input_tables IN NUMBER,
2862: x_start_from IN NUMBER
2863: ) RETURN BOOLEAN IS
2864:
2876: h_last_stage VARCHAR2(1);
2877:
2878: h_base_table VARCHAR2(30);
2879:
2880: h_base_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2881: h_num_base_tables NUMBER;
2882:
2883: h_base_tables_to_color BSC_UPDATE_UTIL.t_array_of_varchar2;
2884:
2879:
2880: h_base_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2881: h_num_base_tables NUMBER;
2882:
2883: h_base_tables_to_color BSC_UPDATE_UTIL.t_array_of_varchar2;
2884:
2885: h_num_base_tables_to_color NUMBER;
2886:
2887: h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2883: h_base_tables_to_color BSC_UPDATE_UTIL.t_array_of_varchar2;
2884:
2885: h_num_base_tables_to_color NUMBER;
2886:
2887: h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2888: h_num_system_tables NUMBER;
2889:
2890: h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2891: h_num_calculated_sys_tables NUMBER;
2886:
2887: h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2888: h_num_system_tables NUMBER;
2889:
2890: h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2891: h_num_calculated_sys_tables NUMBER;
2892:
2893: h_color_indicators BSC_UPDATE_UTIL.t_array_of_number;
2894: h_num_color_indicators NUMBER;
2889:
2890: h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
2891: h_num_calculated_sys_tables NUMBER;
2892:
2893: h_color_indicators BSC_UPDATE_UTIL.t_array_of_number;
2894: h_num_color_indicators NUMBER;
2895:
2896: h_message VARCHAR2(4000);
2897:
2898: h_table_edw_flag NUMBER;
2899:
2900: h_sql VARCHAR2(32000);
2901:
2902: h_kpis BSC_UPDATE_UTIL.t_array_kpis;
2903: h_num_kpis NUMBER;
2904: h_calc_summary_table BOOLEAN;
2905: h_calc_color BOOLEAN;
2906:
2904: h_calc_summary_table BOOLEAN;
2905: h_calc_color BOOLEAN;
2906:
2907: -- AW_INTEGRATION: need these new variables
2908: h_base_tables_aw BSC_UPDATE_UTIL.t_array_of_varchar2;
2909: h_num_base_tables_aw NUMBER;
2910: h_system_tables_aw BSC_UPDATE_UTIL.t_array_of_varchar2;
2911: h_num_system_tables_aw NUMBER;
2912: h_indicators_aw BSC_UPDATE_UTIL.t_array_of_number;
2906:
2907: -- AW_INTEGRATION: need these new variables
2908: h_base_tables_aw BSC_UPDATE_UTIL.t_array_of_varchar2;
2909: h_num_base_tables_aw NUMBER;
2910: h_system_tables_aw BSC_UPDATE_UTIL.t_array_of_varchar2;
2911: h_num_system_tables_aw NUMBER;
2912: h_indicators_aw BSC_UPDATE_UTIL.t_array_of_number;
2913: h_num_indicators_aw NUMBER;
2914: h_aw_flag BOOLEAN;
2908: h_base_tables_aw BSC_UPDATE_UTIL.t_array_of_varchar2;
2909: h_num_base_tables_aw NUMBER;
2910: h_system_tables_aw BSC_UPDATE_UTIL.t_array_of_varchar2;
2911: h_num_system_tables_aw NUMBER;
2912: h_indicators_aw BSC_UPDATE_UTIL.t_array_of_number;
2913: h_num_indicators_aw NUMBER;
2914: h_aw_flag BOOLEAN;
2915: h_calc_aw_kpi BOOLEAN;
2916: h_kpi_list dbms_sql.varchar2_table;
2928: h_num_base_tables_aw := 0;
2929: h_num_system_tables_aw := 0;
2930: h_num_indicators_aw := 0;
2931:
2932: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPD_PROCESS'), BSC_UPDATE_LOG.OUTPUT);
2933:
2934: -- Create generic temporal tables for the process
2935: --LOCKING: lock the temporary tables
2936: IF NOT BSC_UPDATE_LOCK.Lock_Temp_Tables('DATA') THEN
2979: END IF;
2980:
2981: -- AW_INTEGRATION: Know if the base table belongs to an AW Kpi
2982: IF BSC_APPS.bsc_mv THEN
2983: h_aw_flag := BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_base_table);
2984: ELSE
2985: h_aw_flag := FALSE;
2986: END IF;
2987:
2992:
2993: --LOCKING: review no commit between this point and the commit to release the locks
2994:
2995: -- Know if the input table is empty or not.
2996: h_table_has_any_row := BSC_UPDATE_UTIL.Table_Has_Any_Row(h_input_table);
2997:
2998: IF h_table_has_any_row IS NULL THEN
2999: RAISE e_unexpected_error;
3000: END IF;
3010: RAISE e_unexpected_error;
3011: END IF;
3012:
3013: -- Validate input table
3014: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDATION');
3015: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
3016: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3017: --LOCKING: Call the autonomous transaction function
3018: h_table_is_valid := BSC_UPDATE_VAL.Validate_Codes_AT(h_input_table);
3011: END IF;
3012:
3013: -- Validate input table
3014: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDATION');
3015: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
3016: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3017: --LOCKING: Call the autonomous transaction function
3018: h_table_is_valid := BSC_UPDATE_VAL.Validate_Codes_AT(h_input_table);
3019:
3022: END IF;
3023:
3024: IF h_table_is_valid THEN
3025: -- Input table doesn't have invalid codes
3026: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDCONF');
3027: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
3028: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3029:
3030: -- Update the stage of the input table to VALIDATED
3023:
3024: IF h_table_is_valid THEN
3025: -- Input table doesn't have invalid codes
3026: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_NAME_VALIDCONF');
3027: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
3028: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3029:
3030: -- Update the stage of the input table to VALIDATED
3031: --LOCKING: there is no commit inside this function so we do not need AT
3033: RAISE e_unexpected_error;
3034: END IF;
3035:
3036: -- Update the base table
3037: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_UPDATING_BASE_TABLE')||
3038: ' '||h_base_table, BSC_UPDATE_LOG.OUTPUT);
3039:
3040: -- ENH_B_TABLES_PERF: If the base table has a projection table then this is a new strategy.
3041: h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_base_table);
3082: -- colored.
3083: h_num_base_tables_to_color := h_num_base_tables_to_color + 1;
3084: h_base_tables_to_color(h_num_base_tables_to_color) := h_base_table;
3085:
3086: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_BASE_TABLE');
3087: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_base_table);
3088: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3089:
3090: --LOCKING: we do not need to call this here. base tables are not used by any kpi
3083: h_num_base_tables_to_color := h_num_base_tables_to_color + 1;
3084: h_base_tables_to_color(h_num_base_tables_to_color) := h_base_table;
3085:
3086: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_BASE_TABLE');
3087: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_base_table);
3088: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3089:
3090: --LOCKING: we do not need to call this here. base tables are not used by any kpi
3091: -- Update Time Stamp of the indicators using this table directly
3088: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3089:
3090: --LOCKING: we do not need to call this here. base tables are not used by any kpi
3091: -- Update Time Stamp of the indicators using this table directly
3092: --BSC_UPDATE_UTIL.Update_Kpi_Table_Time_Stamp(h_base_table);
3093:
3094: ELSE
3095: -- Input table has invalid codes
3096: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_ITABLE');
3092: --BSC_UPDATE_UTIL.Update_Kpi_Table_Time_Stamp(h_base_table);
3093:
3094: ELSE
3095: -- Input table has invalid codes
3096: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_ITABLE');
3097: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
3098: BSC_UPDATE_LOG.Write_Line_Log(h_message,
3099: BSC_UPDATE_LOG.OUTPUT);
3100:
3093:
3094: ELSE
3095: -- Input table has invalid codes
3096: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_ITABLE');
3097: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
3098: BSC_UPDATE_LOG.Write_Line_Log(h_message,
3099: BSC_UPDATE_LOG.OUTPUT);
3100:
3101: -- Update the status of input table to ERROR
3144: IF NOT Update_Status_Input_Table(h_input_table, LC_NO_DATA_STATUS, NULL) THEN
3145: RAISE e_unexpected_error;
3146: END IF;
3147:
3148: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_BASE_TABLE');
3149: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_base_table);
3150: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3151: ELSE
3152: -- The input table cannot be processed because is an empty table
3145: RAISE e_unexpected_error;
3146: END IF;
3147:
3148: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_BASE_TABLE');
3149: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_base_table);
3150: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3151: ELSE
3152: -- The input table cannot be processed because is an empty table
3153: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_DATA_ITABLE');
3149: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_base_table);
3150: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3151: ELSE
3152: -- The input table cannot be processed because is an empty table
3153: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_DATA_ITABLE');
3154: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
3155: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3156:
3157: -- Update the status of the input table to NO_DATA
3150: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3151: ELSE
3152: -- The input table cannot be processed because is an empty table
3153: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_MISSING_DATA_ITABLE');
3154: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_input_table);
3155: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3156:
3157: -- Update the status of the input table to NO_DATA
3158: --LOCKING: there is no commit inside this function so we do not need AT
3196: -- are calculated in the base tables and the user could have activated projection
3197: -- so it needs to be calculatated.
3198:
3199: -- Update the base table
3200: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_UPDATING_BASE_TABLE')||
3201: ' '||h_base_table, BSC_UPDATE_LOG.OUTPUT);
3202:
3203: -- AW_INTEGRATION: Know if the base table belongs to an AW Kpi
3204: IF BSC_APPS.bsc_mv THEN
3201: ' '||h_base_table, BSC_UPDATE_LOG.OUTPUT);
3202:
3203: -- AW_INTEGRATION: Know if the base table belongs to an AW Kpi
3204: IF BSC_APPS.bsc_mv THEN
3205: h_aw_flag := BSC_UPDATE_UTIL.Is_Table_For_AW_Kpi(h_base_table);
3206: ELSE
3207: h_aw_flag := FALSE;
3208: END IF;
3209:
3221: RAISE e_unexpected_error;
3222: END IF;
3223: END IF;
3224:
3225: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_BASE_TABLE');
3226: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_base_table);
3227: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3228:
3229: h_num_base_tables := h_num_base_tables + 1;
3222: END IF;
3223: END IF;
3224:
3225: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_BASE_TABLE');
3226: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_base_table);
3227: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
3228:
3229: h_num_base_tables := h_num_base_tables + 1;
3230: h_base_tables(h_num_base_tables) := h_base_table;
3277:
3278: -- Until all system tables have been calculated
3279: WHILE h_num_calculated_sys_tables <> h_num_system_tables LOOP
3280: FOR h_i IN 1 .. h_num_system_tables LOOP
3281: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
3282: h_calculated_sys_tables,
3283: h_num_calculated_sys_tables) THEN
3284: -- The table has not been calculated yet
3285:
3303: -- If the table is used direclty by a Kpi and the Kpi is in production then we calculate
3304: -- the table.
3305:
3306: -- If the table is not used directly by a Kpi then this fuctions returns 0;
3307: h_num_kpis := BSC_UPDATE_UTIL.Get_Kpis_Using_Table(h_system_tables(h_i), h_kpis);
3308:
3309: IF h_num_kpis = 0 THEN
3310: -- This table is not used directly by any Kpi (T table) so we need to
3311: -- to calculate the table.
3317: h_calc_summary_table := FALSE;
3318: FOR h_i IN 1..h_num_kpis LOOP
3319: IF h_kpis(h_i).prototype_flag IN (0,6,7) THEN
3320: IF g_kpi_mode THEN
3321: IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_kpis(h_i).indicator,
3322: g_indicators,
3323: g_num_indicators) THEN
3324: h_calc_summary_table := TRUE;
3325: EXIT;
3333: END IF;
3334:
3335: IF h_calc_summary_table THEN
3336: -- Calculate the summary (system) table
3337: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALC_PROCESS')||
3338: ' '||h_system_tables(h_i),
3339: BSC_UPDATE_LOG.OUTPUT);
3340:
3341:
3341:
3342: -- BSC-MV Note: In new architecture, call Calculate_Sum_Table_MV()
3343: IF BSC_APPS.bsc_mv THEN
3344: -- AW_INTEGRATION: Know if the table is used in a AW indicator
3345: IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
3346: h_system_tables_aw,
3347: h_num_system_tables_aw) THEN
3348: -- This table is for an AW indicator
3349: --LOCKING: lock the tables needed to refresh this table
3392: COMMIT;
3393: END IF;
3394: COMMIT;
3395:
3396: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALCULATED');
3397: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_system_tables(h_i));
3398: BSC_UPDATE_LOG.Write_Line_Log(h_message,
3399: BSC_UPDATE_LOG.OUTPUT);
3400:
3393: END IF;
3394: COMMIT;
3395:
3396: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_SUMTABLE_CALCULATED');
3397: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'TABLE_NAME', h_system_tables(h_i));
3398: BSC_UPDATE_LOG.Write_Line_Log(h_message,
3399: BSC_UPDATE_LOG.OUTPUT);
3400:
3401: --LOCKING: remove this call
3404: IF NOT BSC_UPDATE_LOCK.Lock_Period_Indicators(h_system_tables(h_i)) THEN
3405: RAISE e_could_not_get_lock;
3406: END IF;
3407:
3408: BSC_UPDATE_UTIL.Update_Kpi_Table_Time_Stamp(h_system_tables(h_i));
3409:
3410: --LOCKING: commit to release lock
3411: COMMIT;
3412: END IF;
3436: -- are indicators in prototype. We need to check and only color indicators in production mode.
3437: -- Additionally if we are running in KPI_MODE we need to color only indicators
3438: -- that belong to the array g_indicators.
3439: h_calc_aw_kpi := FALSE;
3440: IF BSC_UPDATE_UTIL.Is_Kpi_In_Production(h_indicators_aw(h_i)) THEN
3441: IF g_kpi_mode THEN
3442: IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_indicators_aw(h_i),
3443: g_indicators,
3444: g_num_indicators) THEN
3438: -- that belong to the array g_indicators.
3439: h_calc_aw_kpi := FALSE;
3440: IF BSC_UPDATE_UTIL.Is_Kpi_In_Production(h_indicators_aw(h_i)) THEN
3441: IF g_kpi_mode THEN
3442: IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_indicators_aw(h_i),
3443: g_indicators,
3444: g_num_indicators) THEN
3445: h_calc_aw_kpi := TRUE;
3446: END IF;
3449: END IF;
3450: END IF;
3451:
3452: IF h_calc_aw_kpi THEN
3453: BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_IVIEWER', 'REFRESH')||
3454: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
3455: ' '||h_indicators_aw(h_i),
3456: BSC_UPDATE_LOG.OUTPUT);
3457:
3450: END IF;
3451:
3452: IF h_calc_aw_kpi THEN
3453: BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_IVIEWER', 'REFRESH')||
3454: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
3455: ' '||h_indicators_aw(h_i),
3456: BSC_UPDATE_LOG.OUTPUT);
3457:
3458: --LOCKING: Lock the objects required to refresh the AW indicator cubes
3515: -- are indicators in prototype. We need to check and only color indicators in production mode.
3516: -- Additionally if we are runnign in KPI_MODE we need to color only indicators
3517: -- that belong to the array g_indicators.
3518: h_calc_color := FALSE;
3519: IF BSC_UPDATE_UTIL.Is_Kpi_In_Production(h_color_indicators(h_i)) THEN
3520: IF g_kpi_mode THEN
3521: IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_color_indicators(h_i),
3522: g_indicators,
3523: g_num_indicators) THEN
3517: -- that belong to the array g_indicators.
3518: h_calc_color := FALSE;
3519: IF BSC_UPDATE_UTIL.Is_Kpi_In_Production(h_color_indicators(h_i)) THEN
3520: IF g_kpi_mode THEN
3521: IF BSC_UPDATE_UTIL.Item_Belong_To_Array_Number(h_color_indicators(h_i),
3522: g_indicators,
3523: g_num_indicators) THEN
3524: h_calc_color := TRUE;
3525: END IF;
3528: END IF;
3529: END IF;
3530:
3531: IF h_calc_color THEN
3532: BSC_UPDATE_LOG.Write_Line_log(BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_CALC')||
3533: ' '||h_color_indicators(h_i),
3534: BSC_UPDATE_LOG.OUTPUT);
3535:
3536: --LOCKING: Calculate indicator period here
3563:
3564: -- LOCKING: Commit to release the locks
3565: COMMIT;
3566:
3567: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_CALC_COMPLETED');
3568: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'INDICATOR', TO_CHAR(h_color_indicators(h_i)));
3569: BSC_UPDATE_LOG.Write_Line_log(h_message,
3570: BSC_UPDATE_LOG.OUTPUT);
3571:
3564: -- LOCKING: Commit to release the locks
3565: COMMIT;
3566:
3567: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_COLOR_CALC_COMPLETED');
3568: h_message := BSC_UPDATE_UTIL.Replace_Token(h_message, 'INDICATOR', TO_CHAR(h_color_indicators(h_i)));
3569: BSC_UPDATE_LOG.Write_Line_log(h_message,
3570: BSC_UPDATE_LOG.OUTPUT);
3571:
3572: --LOCKING: Lock the update period of the indicator
3576:
3577: -- LOCKING: review not commit between this point and the commit to release the locks
3578:
3579: -- Update the name of period of indicator in BSC_KPI_DEFAULTS_TL table
3580: IF NOT BSC_UPDATE_UTIL.Update_Kpi_Period_Name(h_color_indicators(h_i)) THEN
3581: RAISE e_unexpected_error;
3582: END IF;
3583:
3584: -- Update date of indicator
3585: UPDATE bsc_kpi_defaults_b SET last_update_date = SYSDATE
3586: WHERE indicator = h_color_indicators(h_i);
3587:
3588: -- Update kpi time stamp
3589: BSC_UPDATE_UTIL.Update_Kpi_Time_Stamp(h_color_indicators(h_i));
3590:
3591: -- Update Tabs time stamp
3592: BSC_UPDATE_UTIL.Update_Kpi_Tab_Time_Stamp(h_color_indicators(h_i));
3593:
3588: -- Update kpi time stamp
3589: BSC_UPDATE_UTIL.Update_Kpi_Time_Stamp(h_color_indicators(h_i));
3590:
3591: -- Update Tabs time stamp
3592: BSC_UPDATE_UTIL.Update_Kpi_Tab_Time_Stamp(h_color_indicators(h_i));
3593:
3594: --LOCKING: commit to release locks
3595: COMMIT;
3596:
3623: IF NOT BSC_UPDATE_LOCK.Lock_Update_Date THEN
3624: RAISE e_could_not_get_lock;
3625: END IF;
3626:
3627: IF NOT BSC_UPDATE_UTIL.Write_Init_Variable_Value('UPDATE_DATE', TO_CHAR(SYSDATE, 'DD/MM/YYYY')) THEN
3628: RAISE e_unexpected_error;
3629: END IF;
3630:
3631: --LOCKING: commit to release lock
3642: END IF;
3643: COMMIT;
3644: END IF;
3645:
3646: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPDATED'), BSC_UPDATE_LOG.OUTPUT);
3647:
3648: RETURN TRUE;
3649: EXCEPTION
3650: WHEN e_unexpected_error THEN
3648: RETURN TRUE;
3649: EXCEPTION
3650: WHEN e_unexpected_error THEN
3651: ROLLBACK;
3652: BSC_MESSAGE.Add (x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPDATE_FAILED'),
3653: x_source => 'BSC_UPDATE.Process_Input_Tables');
3654: IF x_start_from = 0 THEN
3655: h_b := Update_Status_All_Input_Tables(LC_RUNNING_STATUS, LC_ERROR_STATUS, LC_PROGRAM_ERR);
3656: COMMIT;
3688: /*===========================================================================+
3689: | FUNCTION Process_Input_Tables_AT
3690: +============================================================================*/
3691: FUNCTION Process_Input_Tables_AT(
3692: x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3693: x_num_input_tables IN NUMBER,
3694: x_start_from IN NUMBER
3695: ) RETURN BOOLEAN IS
3696: PRAGMA AUTONOMOUS_TRANSACTION;
3705: /*===========================================================================+
3706: | FUNCTION Refresh_System_MVs
3707: +============================================================================*/
3708: FUNCTION Refresh_System_MVs(
3709: p_base_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3710: p_num_base_tables IN NUMBER
3711: ) RETURN BOOLEAN IS
3712:
3713: e_unexpected_error EXCEPTION;
3713: e_unexpected_error EXCEPTION;
3714: --LOCKING
3715: e_could_not_get_lock EXCEPTION;
3716:
3717: h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3718: h_num_system_tables NUMBER;
3719:
3720: h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3721: h_num_calculated_sys_tables NUMBER;
3716:
3717: h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3718: h_num_system_tables NUMBER;
3719:
3720: h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3721: h_num_calculated_sys_tables NUMBER;
3722:
3723: h_refreshed_mvs BSC_UPDATE_UTIL.t_array_of_varchar2;
3724: h_num_refreshed_mvs NUMBER;
3719:
3720: h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3721: h_num_calculated_sys_tables NUMBER;
3722:
3723: h_refreshed_mvs BSC_UPDATE_UTIL.t_array_of_varchar2;
3724: h_num_refreshed_mvs NUMBER;
3725:
3726: h_i NUMBER;
3727: h_b BOOLEAN;
3731: e_error_refresh EXCEPTION;
3732: e_error_refresh_zero EXCEPTION;
3733: h_error_refresh VARCHAR2(2000);
3734:
3735: h_kpis BSC_UPDATE_UTIL.t_array_kpis;
3736: h_num_kpis NUMBER;
3737: h_calc_summary_table BOOLEAN;
3738:
3739: BEGIN
3751:
3752: -- Until all MVs have been refreshed
3753: WHILE h_num_calculated_sys_tables <> h_num_system_tables LOOP
3754: FOR h_i IN 1 .. h_num_system_tables LOOP
3755: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
3756: h_calculated_sys_tables,
3757: h_num_calculated_sys_tables) THEN
3758: -- The table has not been calculated yet
3759:
3773: -- have already been calculated
3774:
3775: -- Fix bug#5023588 Only calculate the MV if it is used by an indicator in production mode
3776: -- If the table is not used directly by a Kpi then this fuctions returns 0;
3777: h_num_kpis := BSC_UPDATE_UTIL.Get_Kpis_Using_Table(h_system_tables(h_i), h_kpis);
3778: IF h_num_kpis > 0 THEN
3779: -- If at least one indicator using the table is in production then we calculate the table.
3780: h_calc_summary_table := FALSE;
3781: FOR i IN 1..h_num_kpis LOOP
3790: -- has data for different periodicities.
3791: -- Note: The api to refresh the MV does not fail if the MV does not exists
3792: -- or if the MV is actually a normal view
3793: mv_name := BSC_BIA_WRAPPER.Get_Sum_Table_MV_Name(h_system_tables(h_i));
3794: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(mv_name,
3795: h_refreshed_mvs,
3796: h_num_refreshed_mvs) THEN
3797:
3798: --LOCKING: Lock the objects required for the mv refresh
3842: x_source => 'BSC_UPDATE.Refresh_System_MVs');
3843: RETURN FALSE;
3844:
3845: WHEN e_unexpected_error THEN
3846: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPDATE_FAILED'),
3847: x_source => 'BSC_UPDATE.Refresh_System_MVs');
3848: RETURN FALSE;
3849:
3850: WHEN e_error_refresh THEN
3868: /*===========================================================================+
3869: | FUNCTION Refresh_System_MVs_Mig
3870: +============================================================================*/
3871: FUNCTION Refresh_System_MVs_Mig(
3872: p_base_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
3873: p_num_base_tables IN NUMBER
3874: ) RETURN BOOLEAN IS
3875:
3876: e_unexpected_error EXCEPTION;
3874: ) RETURN BOOLEAN IS
3875:
3876: e_unexpected_error EXCEPTION;
3877:
3878: h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3879: h_num_system_tables NUMBER;
3880:
3881: h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3882: h_num_calculated_sys_tables NUMBER;
3877:
3878: h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3879: h_num_system_tables NUMBER;
3880:
3881: h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3882: h_num_calculated_sys_tables NUMBER;
3883:
3884: h_refreshed_mvs BSC_UPDATE_UTIL.t_array_of_varchar2;
3885: h_num_refreshed_mvs NUMBER;
3880:
3881: h_calculated_sys_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3882: h_num_calculated_sys_tables NUMBER;
3883:
3884: h_refreshed_mvs BSC_UPDATE_UTIL.t_array_of_varchar2;
3885: h_num_refreshed_mvs NUMBER;
3886:
3887: h_i NUMBER;
3888: h_b BOOLEAN;
3908:
3909: -- Until all MVs have been refreshed
3910: WHILE h_num_calculated_sys_tables <> h_num_system_tables LOOP
3911: FOR h_i IN 1 .. h_num_system_tables LOOP
3912: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(h_system_tables(h_i),
3913: h_calculated_sys_tables,
3914: h_num_calculated_sys_tables) THEN
3915: -- The table has not been calculated yet
3916:
3934: -- has data for different periodicities.
3935: -- Note: The api to refresh the MV does not fail if the MV does not exists
3936: -- or if the MV is actually a normal view
3937: mv_name := BSC_BIA_WRAPPER.Get_Sum_Table_MV_Name(h_system_tables(h_i));
3938: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(mv_name,
3939: h_refreshed_mvs,
3940: h_num_refreshed_mvs) THEN
3941:
3942: IF NOT BSC_BIA_WRAPPER.Refresh_Summary_MV(mv_name, h_error_refresh) THEN
3968: RETURN TRUE;
3969:
3970: EXCEPTION
3971: WHEN e_unexpected_error THEN
3972: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DATATABLE_UPDATE_FAILED'),
3973: x_source => 'BSC_UPDATE.Refresh_System_MVs_Mig');
3974: RETURN FALSE;
3975:
3976: WHEN e_error_refresh THEN
4097: );
4098:
4099: BSC_UPDATE_LOG.Write_Errors_To_Log;
4100:
4101: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4102: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4103: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4104: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4105:
4098:
4099: BSC_UPDATE_LOG.Write_Errors_To_Log;
4100:
4101: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4102: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4103: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4104: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4105:
4106: -- LOCKING
4100:
4101: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4102: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4103: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4104: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4105:
4106: -- LOCKING
4107: BSC_LOCKS_PUB.Remove_System_Lock;
4108:
4105:
4106: -- LOCKING
4107: BSC_LOCKS_PUB.Remove_System_Lock;
4108:
4109: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4110: RETCODE := 2; -- Request completed with errors
4111:
4112: WHEN e_update_error THEN
4113: -- LOCKING
4112: WHEN e_update_error THEN
4113: -- LOCKING
4114: BSC_LOCKS_PUB.Remove_System_Lock;
4115:
4116: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4117: RETCODE := 2; -- Request completed with errors
4118:
4119: WHEN e_warning THEN
4120: -- LOCKING
4130: COMMIT;
4131:
4132: BSC_UPDATE_LOG.Write_Errors_To_Log;
4133:
4134: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4135: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4136: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4137: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4138:
4131:
4132: BSC_UPDATE_LOG.Write_Errors_To_Log;
4133:
4134: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4135: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4136: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4137: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4138:
4139: -- LOCKING
4133:
4134: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4135: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4136: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4137: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4138:
4139: -- LOCKING
4140: BSC_LOCKS_PUB.Remove_System_Lock;
4141:
4162: COMMIT;
4163:
4164: BSC_UPDATE_LOG.Write_Errors_To_Log;
4165:
4166: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4167: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4168: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4169: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4170:
4163:
4164: BSC_UPDATE_LOG.Write_Errors_To_Log;
4165:
4166: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4167: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4168: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4169: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4170:
4171: ERRBUF := SQLERRM;
4165:
4166: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4167: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4168: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4169: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4170:
4171: ERRBUF := SQLERRM;
4172: RETCODE := 2; -- Request completed with errors
4173: End;
4190:
4191: h_source VARCHAR2(200);
4192: h_sessionid NUMBER;
4193: --
4194: h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
4195: h_num_input_tables NUMBER;
4196: h_indicators BSC_UPDATE_UTIL.t_array_of_number;
4197: h_num_indicators NUMBER;
4198: h_indicator_string varchar2(32000);
4192: h_sessionid NUMBER;
4193: --
4194: h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
4195: h_num_input_tables NUMBER;
4196: h_indicators BSC_UPDATE_UTIL.t_array_of_number;
4197: h_num_indicators NUMBER;
4198: h_indicator_string varchar2(32000);
4199: --
4200:
4343: );
4344:
4345: BSC_UPDATE_LOG.Write_Errors_To_Log;
4346:
4347: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4348: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4349: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4350: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4351:
4344:
4345: BSC_UPDATE_LOG.Write_Errors_To_Log;
4346:
4347: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4348: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4349: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4350: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4351:
4352: -- LOCKING
4346:
4347: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4348: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4349: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4350: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4351:
4352: -- LOCKING
4353: BSC_LOCKS_PUB.Remove_System_Lock;
4354:
4351:
4352: -- LOCKING
4353: BSC_LOCKS_PUB.Remove_System_Lock;
4354:
4355: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4356: RETCODE := 2; -- Request completed with errors
4357:
4358: WHEN e_update_error THEN
4359: -- LOCKING
4358: WHEN e_update_error THEN
4359: -- LOCKING
4360: BSC_LOCKS_PUB.Remove_System_Lock;
4361:
4362: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4363: RETCODE := 2; -- Request completed with errors
4364:
4365: WHEN e_warning THEN
4366: -- LOCKING
4376: COMMIT;
4377:
4378: BSC_UPDATE_LOG.Write_Errors_To_Log;
4379:
4380: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4381: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4382: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4383: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4384:
4377:
4378: BSC_UPDATE_LOG.Write_Errors_To_Log;
4379:
4380: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4381: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4382: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4383: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4384:
4385: -- LOCKING
4379:
4380: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4381: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4382: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4383: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4384:
4385: -- LOCKING
4386: BSC_LOCKS_PUB.Remove_System_Lock;
4387:
4515: );
4516:
4517: BSC_UPDATE_LOG.Write_Errors_To_Log;
4518:
4519: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4520: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4521: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4522: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4523:
4516:
4517: BSC_UPDATE_LOG.Write_Errors_To_Log;
4518:
4519: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4520: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4521: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4522: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4523:
4524: -- LOCKING
4518:
4519: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4520: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4521: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4522: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4523:
4524: -- LOCKING
4525: BSC_LOCKS_PUB.Remove_System_Lock;
4526:
4523:
4524: -- LOCKING
4525: BSC_LOCKS_PUB.Remove_System_Lock;
4526:
4527: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4528: RETCODE := 2; -- Request completed with errors
4529:
4530: WHEN e_update_error THEN
4531: -- LOCKING
4530: WHEN e_update_error THEN
4531: -- LOCKING
4532: BSC_LOCKS_PUB.Remove_System_Lock;
4533:
4534: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4535: RETCODE := 2; -- Request completed with errors
4536:
4537: WHEN e_warning THEN
4538: -- LOCKING
4548: COMMIT;
4549:
4550: BSC_UPDATE_LOG.Write_Errors_To_Log;
4551:
4552: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4553: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4554: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4555: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4556:
4549:
4550: BSC_UPDATE_LOG.Write_Errors_To_Log;
4551:
4552: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4553: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4554: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4555: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4556:
4557: -- LOCKING
4551:
4552: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4553: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4554: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4555: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4556:
4557: -- LOCKING
4558: BSC_LOCKS_PUB.Remove_System_Lock;
4559:
4556:
4557: -- LOCKING
4558: BSC_LOCKS_PUB.Remove_System_Lock;
4559:
4560: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4561: RETCODE := 2; -- Request completed with errors
4562:
4563: END Run_Concurrent_Loader_Dim_Apps;
4564:
4662: );
4663:
4664: BSC_UPDATE_LOG.Write_Errors_To_Log;
4665:
4666: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4667: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4668: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4669: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4670:
4663:
4664: BSC_UPDATE_LOG.Write_Errors_To_Log;
4665:
4666: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4667: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4668: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4669: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4670:
4671: -- LOCKING
4665:
4666: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4667: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4668: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4669: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4670:
4671: -- LOCKING
4672: BSC_LOCKS_PUB.Remove_System_Lock;
4673:
4670:
4671: -- LOCKING
4672: BSC_LOCKS_PUB.Remove_System_Lock;
4673:
4674: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4675: RETCODE := 2; -- Request completed with errors
4676:
4677: WHEN e_update_error THEN
4678: -- LOCKING
4677: WHEN e_update_error THEN
4678: -- LOCKING
4679: BSC_LOCKS_PUB.Remove_System_Lock;
4680:
4681: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4682: RETCODE := 2; -- Request completed with errors
4683:
4684: WHEN OTHERS THEN
4685: BSC_MESSAGE.Add(x_message => SQLERRM,
4688: COMMIT;
4689:
4690: BSC_UPDATE_LOG.Write_Errors_To_Log;
4691:
4692: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4693: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4694: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4695: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4696:
4689:
4690: BSC_UPDATE_LOG.Write_Errors_To_Log;
4691:
4692: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4693: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4694: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4695: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4696:
4697: -- LOCKING
4691:
4692: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4693: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4694: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4695: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4696:
4697: -- LOCKING
4698: BSC_LOCKS_PUB.Remove_System_Lock;
4699:
4726: h_sql VARCHAR2(32000);
4727:
4728: h_source VARCHAR2(200);
4729:
4730: h_indicators BSC_UPDATE_UTIL.t_array_of_number;
4731: h_num_indicators NUMBER;
4732:
4733: h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
4734: h_num_input_tables NUMBER;
4729:
4730: h_indicators BSC_UPDATE_UTIL.t_array_of_number;
4731: h_num_indicators NUMBER;
4732:
4733: h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
4734: h_num_input_tables NUMBER;
4735:
4736: h_process_id NUMBER;
4737: h_i NUMBER;
4745: h_msg_count NUMBER;
4746: h_msg_data VARCHAR2(4000);
4747:
4748: -- Bug#4681065
4749: h_prod_indicators BSC_UPDATE_UTIL.t_array_of_number;
4750: h_num_prod_indicators NUMBER;
4751: h_lst_prod_indicators VARCHAR2(32000);
4752: h_first_prot_indic BOOLEAN;
4753: h_indic_name VARCHAR2(2000);
4816: RAISE e_system_lock;
4817: END IF;
4818:
4819: -- Decompose the list in x_parameter_1 into the array h_indicators
4820: h_num_indicators := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_indicators,
4821: h_indicators,
4822: ',');
4823:
4824: IF h_num_indicators = 0 THEN
4848: END IF;
4849: h_lst_prod_indicators := h_lst_prod_indicators||h_indicators(h_i);
4850: ELSE
4851: IF h_first_prot_indic THEN
4852: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_CANNOT_LOAD_OBJS_IN_PROT'),
4853: BSC_UPDATE_LOG.OUTPUT);
4854: h_first_prot_indic := FALSE;
4855: g_warning := TRUE;
4856: END IF;
4942: );
4943:
4944: BSC_UPDATE_LOG.Write_Errors_To_Log;
4945:
4946: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4947: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4948: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4949: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4950:
4943:
4944: BSC_UPDATE_LOG.Write_Errors_To_Log;
4945:
4946: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4947: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4948: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4949: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4950:
4951: -- LOCKING
4945:
4946: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4947: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4948: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4949: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4950:
4951: -- LOCKING
4952: BSC_LOCKS_PUB.Remove_System_Lock;
4953:
4950:
4951: -- LOCKING
4952: BSC_LOCKS_PUB.Remove_System_Lock;
4953:
4954: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4955: RETCODE := 2; -- Request completed with errors
4956:
4957: WHEN e_update_error THEN
4958: -- LOCKING
4957: WHEN e_update_error THEN
4958: -- LOCKING
4959: BSC_LOCKS_PUB.Remove_System_Lock;
4960:
4961: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
4962: RETCODE := 2; -- Request completed with errors
4963:
4964: WHEN e_warning THEN
4965: -- LOCKING
4970:
4971: WHEN e_unexpected_error THEN
4972: BSC_UPDATE_LOG.Write_Errors_To_Log;
4973:
4974: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4975: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4976: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4977: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4978:
4971: WHEN e_unexpected_error THEN
4972: BSC_UPDATE_LOG.Write_Errors_To_Log;
4973:
4974: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4975: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4976: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4977: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4978:
4979: -- LOCKING
4973:
4974: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4975: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4976: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4977: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
4978:
4979: -- LOCKING
4980: BSC_LOCKS_PUB.Remove_System_Lock;
4981:
4992: COMMIT;
4993:
4994: BSC_UPDATE_LOG.Write_Errors_To_Log;
4995:
4996: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4997: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4998: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4999: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5000:
4993:
4994: BSC_UPDATE_LOG.Write_Errors_To_Log;
4995:
4996: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4997: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4998: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4999: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5000:
5001: -- LOCKING
4995:
4996: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
4997: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
4998: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
4999: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5000:
5001: -- LOCKING
5002: BSC_LOCKS_PUB.Remove_System_Lock;
5003:
5028: h_sql VARCHAR2(32000);
5029:
5030: h_source VARCHAR2(200);
5031:
5032: h_indicators BSC_UPDATE_UTIL.t_array_of_number;
5033: h_num_indicators NUMBER;
5034:
5035: h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
5036: h_num_input_tables NUMBER;
5031:
5032: h_indicators BSC_UPDATE_UTIL.t_array_of_number;
5033: h_num_indicators NUMBER;
5034:
5035: h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
5036: h_num_input_tables NUMBER;
5037:
5038: l_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
5039: l_num_input_tables NUMBER;
5034:
5035: h_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
5036: h_num_input_tables NUMBER;
5037:
5038: l_input_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
5039: l_num_input_tables NUMBER;
5040:
5041: h_process_id NUMBER;
5042: h_i NUMBER;
5040:
5041: h_process_id NUMBER;
5042: h_i NUMBER;
5043:
5044: h_dbi_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
5045: h_num_dbi_dimensions NUMBER;
5046: h_dbi_dim_requests BSC_UPDATE_UTIL.t_array_of_number;
5047:
5048: --LOCKING: new variables
5042: h_i NUMBER;
5043:
5044: h_dbi_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
5045: h_num_dbi_dimensions NUMBER;
5046: h_dbi_dim_requests BSC_UPDATE_UTIL.t_array_of_number;
5047:
5048: --LOCKING: new variables
5049: h_return_status VARCHAR2(10);
5050: h_msg_count NUMBER;
5104: RAISE e_system_lock;
5105: END IF;
5106:
5107: -- Decompose the list in x_parameter_1 into the array h_indicators
5108: h_num_indicators := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_indicators,
5109: h_indicators,
5110: ',');
5111:
5112: IF h_num_indicators = 0 THEN
5154: END IF;
5155:
5156: h_dbi_dim_requests.delete;
5157: FOR h_i IN 1..h_num_dbi_dimensions LOOP
5158: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_IVIEWER', 'REFRESH')||
5159: ' '||h_dbi_dimensions(h_i), BSC_UPDATE_LOG.OUTPUT);
5160:
5161: h_dbi_dim_requests(h_i) := FND_REQUEST.Submit_Request(application => BSC_APPS.bsc_apps_short_name,
5162: program => 'BSC_REFRESH_DBI_DIM',
5164:
5165:
5166: IF h_dbi_dim_requests(h_i) = 0 THEN
5167: -- Request error;
5168: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_SUBMMITREQ_FAILED'), BSC_UPDATE_LOG.OUTPUT);
5169: ELSE
5170: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_LOADER_REQ_ID')||
5171: ' '||h_dbi_dim_requests(h_i), BSC_UPDATE_LOG.OUTPUT);
5172: END IF;
5166: IF h_dbi_dim_requests(h_i) = 0 THEN
5167: -- Request error;
5168: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_SUBMMITREQ_FAILED'), BSC_UPDATE_LOG.OUTPUT);
5169: ELSE
5170: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_LOADER_REQ_ID')||
5171: ' '||h_dbi_dim_requests(h_i), BSC_UPDATE_LOG.OUTPUT);
5172: END IF;
5173: COMMIT;
5174: END LOOP;
5259: );
5260:
5261: BSC_UPDATE_LOG.Write_Errors_To_Log;
5262:
5263: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5264: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5265: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5266: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5267:
5260:
5261: BSC_UPDATE_LOG.Write_Errors_To_Log;
5262:
5263: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5264: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5265: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5266: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5267:
5268: -- LOCKING
5262:
5263: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5264: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5265: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5266: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5267:
5268: -- LOCKING
5269: BSC_LOCKS_PUB.Remove_System_Lock;
5270:
5267:
5268: -- LOCKING
5269: BSC_LOCKS_PUB.Remove_System_Lock;
5270:
5271: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
5272: RETCODE := 2; -- Request completed with errors
5273:
5274: WHEN e_update_error THEN
5275: -- LOCKING
5273:
5274: WHEN e_update_error THEN
5275: -- LOCKING
5276: BSC_LOCKS_PUB.Remove_System_Lock;
5277: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
5278: RETCODE := 2; -- Request completed with errors
5279:
5280: WHEN e_warning THEN
5281: -- LOCKING
5285:
5286: WHEN e_unexpected_error THEN
5287: BSC_UPDATE_LOG.Write_Errors_To_Log;
5288:
5289: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5290: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5291: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5292: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5293:
5286: WHEN e_unexpected_error THEN
5287: BSC_UPDATE_LOG.Write_Errors_To_Log;
5288:
5289: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5290: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5291: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5292: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5293:
5294: -- LOCKING
5288:
5289: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5290: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5291: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5292: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5293:
5294: -- LOCKING
5295: BSC_LOCKS_PUB.Remove_System_Lock;
5296: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
5292: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5293:
5294: -- LOCKING
5295: BSC_LOCKS_PUB.Remove_System_Lock;
5296: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
5297: RETCODE := 2; -- Request completed with errors
5298:
5299: WHEN OTHERS THEN
5300: BSC_MESSAGE.Add(x_message => SQLERRM,
5303: COMMIT;
5304:
5305: BSC_UPDATE_LOG.Write_Errors_To_Log;
5306:
5307: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5308: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5309: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5310: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5311:
5304:
5305: BSC_UPDATE_LOG.Write_Errors_To_Log;
5306:
5307: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5308: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5309: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5310: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5311:
5312: -- LOCKING
5306:
5307: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5308: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5309: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5310: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5311:
5312: -- LOCKING
5313: BSC_LOCKS_PUB.Remove_System_Lock;
5314: ERRBUF := SQLERRM;
5336: h_sql VARCHAR2(32000);
5337:
5338: h_source VARCHAR2(200);
5339:
5340: h_indicators BSC_UPDATE_UTIL.t_array_of_number;
5341: h_num_indicators NUMBER;
5342:
5343: h_process_id NUMBER;
5344: h_i NUMBER;
5404: h_process_id := TO_NUMBER(SUBSTR(x_indicators, 12));
5405: ELSE
5406: -- called from RSG, it is passing a list of indicators
5407: --Decompose the list in x_indicators into the array h_indicators
5408: h_num_indicators := BSC_UPDATE_UTIL.Decompose_Numeric_List(x_indicators,
5409: h_indicators,
5410: ',');
5411:
5412: IF h_num_indicators = 0 THEN
5464: );
5465:
5466: BSC_UPDATE_LOG.Write_Errors_To_Log;
5467:
5468: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5469: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5470: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5471: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5472:
5465:
5466: BSC_UPDATE_LOG.Write_Errors_To_Log;
5467:
5468: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5469: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5470: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5471: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5472:
5473: -- LOCKING
5467:
5468: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5469: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5470: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5471: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5472:
5473: -- LOCKING
5474: BSC_LOCKS_PUB.Remove_System_Lock;
5475:
5472:
5473: -- LOCKING
5474: BSC_LOCKS_PUB.Remove_System_Lock;
5475:
5476: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
5477: RETCODE := 2; -- Request completed with errors
5478:
5479: WHEN e_update_error THEN
5480: -- LOCKING
5478:
5479: WHEN e_update_error THEN
5480: -- LOCKING
5481: BSC_LOCKS_PUB.Remove_System_Lock;
5482: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
5483: RETCODE := 2; -- Request completed with errors
5484:
5485: WHEN OTHERS THEN
5486: BSC_MESSAGE.Add(x_message => SQLERRM,
5489: COMMIT;
5490:
5491: BSC_UPDATE_LOG.Write_Errors_To_Log;
5492:
5493: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5494: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5495: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5496: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5497:
5490:
5491: BSC_UPDATE_LOG.Write_Errors_To_Log;
5492:
5493: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5494: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5495: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5496: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5497:
5498: -- LOCKING
5492:
5493: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
5494: BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
5495: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_UPDATE_LOG.OUTPUT);
5496: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_UPDATE_LOG.OUTPUT);
5497:
5498: -- LOCKING
5499: BSC_LOCKS_PUB.Remove_System_Lock;
5500:
5507: /*===========================================================================+
5508: | FUNCTION Get_Input_Tables_Kpis
5509: +============================================================================*/
5510: FUNCTION Get_Input_Tables_Kpis(
5511: x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
5512: x_num_indicators IN NUMBER,
5513: x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
5514: x_num_input_tables IN OUT NOCOPY NUMBER
5515: ) RETURN BOOLEAN IS
5509: +============================================================================*/
5510: FUNCTION Get_Input_Tables_Kpis(
5511: x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
5512: x_num_indicators IN NUMBER,
5513: x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
5514: x_num_input_tables IN OUT NOCOPY NUMBER
5515: ) RETURN BOOLEAN IS
5516:
5517: e_unexpected_error EXCEPTION;
5524: h_sql VARCHAR2(32700);
5525:
5526: h_table_name VARCHAR2(50);
5527:
5528: h_system_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
5529: h_num_system_tables NUMBER;
5530:
5531: BEGIN
5532: h_where_indics := NULL;
5560: RETURN TRUE;
5561:
5562: EXCEPTION
5563: WHEN e_unexpected_error THEN
5564: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_ITABLES_FAILED'),
5565: x_source => 'BSC_UPDATE.Get_Input_Tables_Kpis');
5566: RETURN FALSE;
5567:
5568: WHEN OTHERS THEN
5572:
5573: END Get_Input_Tables_Kpis;
5574:
5575: function get_kpi_for_input_tables(
5576: x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
5577: x_num_input_tables IN NUMBER,
5578: x_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
5579: x_num_indicators IN OUT NOCOPY NUMBER
5580: )return boolean is
5574:
5575: function get_kpi_for_input_tables(
5576: x_input_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
5577: x_num_input_tables IN NUMBER,
5578: x_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
5579: x_num_indicators IN OUT NOCOPY NUMBER
5580: )return boolean is
5581: --
5582: e_unexpected_error exception;
5592: end loop;
5593: return true;
5594: EXCEPTION
5595: WHEN e_unexpected_error THEN
5596: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_ITABLES_FAILED'),
5597: x_source => 'BSC_UPDATE.get_kpi_for_input_tables');
5598: RETURN FALSE;
5599:
5600: WHEN OTHERS THEN
5606:
5607: --called recursively
5608: function get_kpi_for_input_tables(
5609: x_input_table varchar2,
5610: x_indicators IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number,
5611: x_num_indicators IN OUT NOCOPY NUMBER
5612: )return boolean is
5613: --
5614: cursor c1(p_table varchar2) is select table_name from bsc_db_tables_rels where source_table_name=p_table;
5614: cursor c1(p_table varchar2) is select table_name from bsc_db_tables_rels where source_table_name=p_table;
5615: --
5616: cursor c2(p_table varchar2) is select distinct indicator from bsc_kpi_data_tables where table_name=p_table;
5617: --
5618: l_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
5619: l_num_tables number;
5620: l_indicator number;
5621: --
5622: Begin
5652: END get_kpi_for_input_tables;
5653:
5654: function value_in_array(
5655: x_value number,
5656: x_array BSC_UPDATE_UTIL.t_array_of_number,
5657: x_num_array NUMBER
5658: )return boolean is
5659: Begin
5660: for i in 1..x_num_array loop
5670: /*===========================================================================+
5671: | FUNCTION Get_Dim_Input_Tables_Kpis
5672: +============================================================================*/
5673: FUNCTION Get_Dim_Input_Tables_Kpis(
5674: x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
5675: x_num_indicators IN NUMBER,
5676: x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
5677: x_num_input_tables IN OUT NOCOPY NUMBER
5678: ) RETURN BOOLEAN IS
5672: +============================================================================*/
5673: FUNCTION Get_Dim_Input_Tables_Kpis(
5674: x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
5675: x_num_indicators IN NUMBER,
5676: x_input_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
5677: x_num_input_tables IN OUT NOCOPY NUMBER
5678: ) RETURN BOOLEAN IS
5679:
5680: e_unexpected_error EXCEPTION;
5734: RETURN TRUE;
5735:
5736: EXCEPTION
5737: WHEN e_unexpected_error THEN
5738: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_RETR_ITABLES_FAILED'),
5739: x_source => 'BSC_UPDATE.Get_Dim_Input_Tables_Kpis');
5740: RETURN FALSE;
5741:
5742: WHEN OTHERS THEN
6035: /*===========================================================================+
6036: | FUNCTION Wait_For_Requests
6037: +============================================================================*/
6038: FUNCTION Wait_For_Requests(
6039: x_requests IN BSC_UPDATE_UTIL.t_array_of_number,
6040: x_num_requests IN NUMBER
6041: ) RETURN BOOLEAN IS
6042:
6043: h_i NUMBER;
6159: -- Result
6160: BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
6161: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6162: BSC_UPDATE_LOG.OUTPUT);
6163: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROCESS_RESULT'), BSC_UPDATE_LOG.OUTPUT);
6164: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6165: BSC_UPDATE_LOG.OUTPUT);
6166: -- Get update date
6167: IF NOT BSC_UPDATE_UTIL.Get_Init_Variable_Value('UPDATE_DATE', h_update_date) THEN
6163: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_PROCESS_RESULT'), BSC_UPDATE_LOG.OUTPUT);
6164: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6165: BSC_UPDATE_LOG.OUTPUT);
6166: -- Get update date
6167: IF NOT BSC_UPDATE_UTIL.Get_Init_Variable_Value('UPDATE_DATE', h_update_date) THEN
6168: RAISE e_unexpected_error;
6169: END IF;
6170: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_LAST_UPDATE')||' '||h_update_date, BSC_UPDATE_LOG.OUTPUT);
6171: BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
6166: -- Get update date
6167: IF NOT BSC_UPDATE_UTIL.Get_Init_Variable_Value('UPDATE_DATE', h_update_date) THEN
6168: RAISE e_unexpected_error;
6169: END IF;
6170: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_LAST_UPDATE')||' '||h_update_date, BSC_UPDATE_LOG.OUTPUT);
6171: BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
6172:
6173: --OPEN c_status FOR c_status_sql USING g_process_id;
6174: OPEN c_status(g_process_id);
6174: OPEN c_status(g_process_id);
6175: FETCH c_status INTO h_input_table_name, h_status, h_error_code;
6176:
6177: IF c_status%FOUND THEN
6178: BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_W)||
6179: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'STATUS'), C_STATUS_W),
6180: BSC_UPDATE_LOG.OUTPUT);
6181: END IF;
6182:
6175: FETCH c_status INTO h_input_table_name, h_status, h_error_code;
6176:
6177: IF c_status%FOUND THEN
6178: BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_W)||
6179: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'STATUS'), C_STATUS_W),
6180: BSC_UPDATE_LOG.OUTPUT);
6181: END IF;
6182:
6183: WHILE c_status%FOUND LOOP
6182:
6183: WHILE c_status%FOUND LOOP
6184: h_line := RPAD(h_input_table_name, C_TABLE_W);
6185: IF h_status = LC_ERROR_STATUS THEN
6186: h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_UPDATE_FAILED');
6187:
6188: IF h_error_code = LC_INVALID_CODES_ERR THEN
6189: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_FOUND');
6190: ELSIF h_error_code = LC_PROGRAM_ERR THEN
6185: IF h_status = LC_ERROR_STATUS THEN
6186: h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_UPDATE_FAILED');
6187:
6188: IF h_error_code = LC_INVALID_CODES_ERR THEN
6189: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_FOUND');
6190: ELSIF h_error_code = LC_PROGRAM_ERR THEN
6191: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_LOADER_PROC_FAILED');
6192: ELSIF h_error_code = LC_UPLOAD_OPEN_ERR THEN
6193: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRCFILE_FAILED');
6187:
6188: IF h_error_code = LC_INVALID_CODES_ERR THEN
6189: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_FOUND');
6190: ELSIF h_error_code = LC_PROGRAM_ERR THEN
6191: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_LOADER_PROC_FAILED');
6192: ELSIF h_error_code = LC_UPLOAD_OPEN_ERR THEN
6193: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRCFILE_FAILED');
6194: ELSIF h_error_code = LC_UPLOAD_NUM_COLS_ERR THEN
6195: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRC_FIELDNUMBER_FAILED');
6189: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_CODES_FOUND');
6190: ELSIF h_error_code = LC_PROGRAM_ERR THEN
6191: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_LOADER_PROC_FAILED');
6192: ELSIF h_error_code = LC_UPLOAD_OPEN_ERR THEN
6193: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRCFILE_FAILED');
6194: ELSIF h_error_code = LC_UPLOAD_NUM_COLS_ERR THEN
6195: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRC_FIELDNUMBER_FAILED');
6196: ELSIF h_error_code = LC_UPLOAD_INV_KEY_ERR THEN
6197: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRC_NULL_KEYVALUES');
6191: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_LOADER_PROC_FAILED');
6192: ELSIF h_error_code = LC_UPLOAD_OPEN_ERR THEN
6193: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRCFILE_FAILED');
6194: ELSIF h_error_code = LC_UPLOAD_NUM_COLS_ERR THEN
6195: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRC_FIELDNUMBER_FAILED');
6196: ELSIF h_error_code = LC_UPLOAD_INV_KEY_ERR THEN
6197: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRC_NULL_KEYVALUES');
6198: ELSIF h_error_code = LC_UPLOAD_INSERT_ERR THEN
6199: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_DATATYPE');
6193: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRCFILE_FAILED');
6194: ELSIF h_error_code = LC_UPLOAD_NUM_COLS_ERR THEN
6195: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRC_FIELDNUMBER_FAILED');
6196: ELSIF h_error_code = LC_UPLOAD_INV_KEY_ERR THEN
6197: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRC_NULL_KEYVALUES');
6198: ELSIF h_error_code = LC_UPLOAD_INSERT_ERR THEN
6199: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_DATATYPE');
6200: ELSIF h_error_code = LC_UPLOAD_NOT_FOUND_ERR THEN
6201: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRCFILE_NOT_FOUND');
6195: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRC_FIELDNUMBER_FAILED');
6196: ELSIF h_error_code = LC_UPLOAD_INV_KEY_ERR THEN
6197: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRC_NULL_KEYVALUES');
6198: ELSIF h_error_code = LC_UPLOAD_INSERT_ERR THEN
6199: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_DATATYPE');
6200: ELSIF h_error_code = LC_UPLOAD_NOT_FOUND_ERR THEN
6201: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRCFILE_NOT_FOUND');
6202: ELSIF h_error_code = LC_UPLOAD_EXCEL_ERR THEN
6203: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_EXCEL_CONNECTION_FAILED');
6197: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRC_NULL_KEYVALUES');
6198: ELSIF h_error_code = LC_UPLOAD_INSERT_ERR THEN
6199: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_DATATYPE');
6200: ELSIF h_error_code = LC_UPLOAD_NOT_FOUND_ERR THEN
6201: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRCFILE_NOT_FOUND');
6202: ELSIF h_error_code = LC_UPLOAD_EXCEL_ERR THEN
6203: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_EXCEL_CONNECTION_FAILED');
6204: ELSIF h_error_code = LC_UPLOAD_SP_NOT_FOUND_ERR THEN
6205: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_NOT_FOUND');
6199: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_INVALID_DATATYPE');
6200: ELSIF h_error_code = LC_UPLOAD_NOT_FOUND_ERR THEN
6201: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRCFILE_NOT_FOUND');
6202: ELSIF h_error_code = LC_UPLOAD_EXCEL_ERR THEN
6203: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_EXCEL_CONNECTION_FAILED');
6204: ELSIF h_error_code = LC_UPLOAD_SP_NOT_FOUND_ERR THEN
6205: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_NOT_FOUND');
6206: ELSIF h_error_code = LC_UPLOAD_SP_INVALID_ERR THEN
6207: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_IS_INVALID');
6201: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_SRCFILE_NOT_FOUND');
6202: ELSIF h_error_code = LC_UPLOAD_EXCEL_ERR THEN
6203: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_EXCEL_CONNECTION_FAILED');
6204: ELSIF h_error_code = LC_UPLOAD_SP_NOT_FOUND_ERR THEN
6205: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_NOT_FOUND');
6206: ELSIF h_error_code = LC_UPLOAD_SP_INVALID_ERR THEN
6207: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_IS_INVALID');
6208: ELSIF h_error_code = LC_UPLOAD_SP_EXECUTION_ERR THEN
6209: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_FAILED');
6203: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_EXCEL_CONNECTION_FAILED');
6204: ELSIF h_error_code = LC_UPLOAD_SP_NOT_FOUND_ERR THEN
6205: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_NOT_FOUND');
6206: ELSIF h_error_code = LC_UPLOAD_SP_INVALID_ERR THEN
6207: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_IS_INVALID');
6208: ELSIF h_error_code = LC_UPLOAD_SP_EXECUTION_ERR THEN
6209: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_FAILED');
6210: END IF;
6211:
6205: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_NOT_FOUND');
6206: ELSIF h_error_code = LC_UPLOAD_SP_INVALID_ERR THEN
6207: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_IS_INVALID');
6208: ELSIF h_error_code = LC_UPLOAD_SP_EXECUTION_ERR THEN
6209: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_FAILED');
6210: END IF;
6211:
6212: ELSIF h_status = LC_NO_DATA_STATUS THEN
6213: h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_UPDATE_FAILED')||' '||
6209: h_line := h_line||' '||BSC_UPDATE_UTIL.Get_Message('BSC_STOREDPROC_FAILED');
6210: END IF;
6211:
6212: ELSIF h_status = LC_NO_DATA_STATUS THEN
6213: h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_UPDATE_FAILED')||' '||
6214: BSC_UPDATE_UTIL.Get_Message('BSC_ITABLE_EMPTY');
6215: ELSIF h_status = LC_COMPLETED_STATUS THEN
6216: h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_STATUS');
6217: END IF;
6210: END IF;
6211:
6212: ELSIF h_status = LC_NO_DATA_STATUS THEN
6213: h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_UPDATE_FAILED')||' '||
6214: BSC_UPDATE_UTIL.Get_Message('BSC_ITABLE_EMPTY');
6215: ELSIF h_status = LC_COMPLETED_STATUS THEN
6216: h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_STATUS');
6217: END IF;
6218:
6212: ELSIF h_status = LC_NO_DATA_STATUS THEN
6213: h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_TABLE_UPDATE_FAILED')||' '||
6214: BSC_UPDATE_UTIL.Get_Message('BSC_ITABLE_EMPTY');
6215: ELSIF h_status = LC_COMPLETED_STATUS THEN
6216: h_line := h_line||BSC_UPDATE_UTIL.Get_Message('BSC_UPDATED_STATUS');
6217: END IF;
6218:
6219: BSC_UPDATE_LOG.Write_Line_Log(h_line, BSC_UPDATE_LOG.OUTPUT);
6220:
6228: IF c_invalid_codes%FOUND THEN
6229: BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
6230: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6231: BSC_UPDATE_LOG.OUTPUT);
6232: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INVALID_RECORDS'),
6233: BSC_UPDATE_LOG.OUTPUT);
6234: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6235: BSC_UPDATE_LOG.OUTPUT);
6236: BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_W)||
6232: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INVALID_RECORDS'),
6233: BSC_UPDATE_LOG.OUTPUT);
6234: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6235: BSC_UPDATE_LOG.OUTPUT);
6236: BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_W)||
6237: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'COLUMN'), C_COLUMN_W)||
6238: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INVALID_CODE'), C_INVALID_CODE_W),
6239: BSC_UPDATE_LOG.OUTPUT);
6240: END IF;
6233: BSC_UPDATE_LOG.OUTPUT);
6234: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6235: BSC_UPDATE_LOG.OUTPUT);
6236: BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_W)||
6237: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'COLUMN'), C_COLUMN_W)||
6238: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INVALID_CODE'), C_INVALID_CODE_W),
6239: BSC_UPDATE_LOG.OUTPUT);
6240: END IF;
6241:
6234: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6235: BSC_UPDATE_LOG.OUTPUT);
6236: BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_W)||
6237: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'COLUMN'), C_COLUMN_W)||
6238: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INVALID_CODE'), C_INVALID_CODE_W),
6239: BSC_UPDATE_LOG.OUTPUT);
6240: END IF;
6241:
6242: WHILE c_invalid_codes%FOUND LOOP
6252: IF g_process_name = PC_LOADER_PROCESS THEN
6253: BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
6254: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6255: BSC_UPDATE_LOG.OUTPUT);
6256: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'TABLE_UPDATE_PERIOD'),
6257: BSC_UPDATE_LOG.OUTPUT);
6258: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6259: BSC_UPDATE_LOG.OUTPUT);
6260: BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_NAME_W)||
6256: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'TABLE_UPDATE_PERIOD'),
6257: BSC_UPDATE_LOG.OUTPUT);
6258: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6259: BSC_UPDATE_LOG.OUTPUT);
6260: BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_NAME_W)||
6261: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'PERIODICITY'), C_PERIODICITY_W)||
6262: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'PERIOD'), C_PERIOD_W),
6263: BSC_UPDATE_LOG.OUTPUT);
6264:
6257: BSC_UPDATE_LOG.OUTPUT);
6258: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6259: BSC_UPDATE_LOG.OUTPUT);
6260: BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_NAME_W)||
6261: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'PERIODICITY'), C_PERIODICITY_W)||
6262: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'PERIOD'), C_PERIOD_W),
6263: BSC_UPDATE_LOG.OUTPUT);
6264:
6265: --OPEN c_tables FOR c_tables_sql USING g_process_id;
6258: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
6259: BSC_UPDATE_LOG.OUTPUT);
6260: BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'INPUT_TABLE_NAME'), C_TABLE_NAME_W)||
6261: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'PERIODICITY'), C_PERIODICITY_W)||
6262: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'PERIOD'), C_PERIOD_W),
6263: BSC_UPDATE_LOG.OUTPUT);
6264:
6265: --OPEN c_tables FOR c_tables_sql USING g_process_id;
6266: OPEN c_tables(g_process_id);
6268:
6269: WHILE c_tables%FOUND LOOP
6270: h_line := RPAD(h_input_table_name, C_TABLE_NAME_W)||RPAD(h_periodicity_name, C_PERIODICITY_W)||TO_CHAR(h_current_period);
6271:
6272: h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(h_periodicity_id);
6273:
6274: IF h_periodicity_type = 11 OR h_periodicity_type = 12 THEN
6275: h_line := h_line||', '||TO_CHAR(h_current_subperiod);
6276: END IF;
6287: RETURN TRUE;
6288:
6289: EXCEPTION
6290: WHEN e_unexpected_error THEN
6291: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_WR_LOGFILE_RES_FAILED'),
6292: x_source => 'BSC_UPDATE.Write_Result_Log');
6293: RETURN FALSE;
6294:
6295: WHEN OTHERS THEN
6301:
6302: /*===========================================================================+
6303: | FUNCTION Get_Indicator_List
6304: +============================================================================*/
6305: FUNCTION Get_Indicator_List(x_number_array IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number)
6306: RETURN NUMBER IS
6307: CURSOR cIndics is
6308: SELECT value_n
6309: FROM BSC_TMP_BIG_IN_COND
6329: /*===========================================================================+
6330: | PROCEDURE Write_Warning_Kpis_In_Prot
6331: +============================================================================*/
6332: PROCEDURE Write_Warning_Kpis_In_Prot (
6333: x_system_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
6334: x_num_system_tables IN NUMBER
6335: ) IS
6336:
6337: h_where_cond VARCHAR2(32700);
6359: FETCH h_cursor INTO h_kpi, h_name;
6360: EXIT WHEN h_cursor%NOTFOUND;
6361:
6362: IF h_i = 0 THEN
6363: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_CANNOT_LOAD_OBJS_IN_PROT');
6364: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
6365: h_i := 1;
6366: END IF;
6367: BSC_UPDATE_LOG.Write_Line_Log(h_kpi||' '||h_name, BSC_UPDATE_LOG.OUTPUT);
6387: FETCH h_cursor INTO h_kpi, h_name;
6388: EXIT WHEN h_cursor%NOTFOUND;
6389:
6390: IF h_i = 0 THEN
6391: h_message := BSC_UPDATE_UTIL.Get_Message('BSC_CANNOT_LOAD_OBJS_IN_PROT');
6392: BSC_UPDATE_LOG.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
6393: h_i := 1;
6394: END IF;
6395: BSC_UPDATE_LOG.Write_Line_Log(h_kpi||' '||h_name, BSC_UPDATE_LOG.OUTPUT);