[Home] [Help]
154: h_property_code VARCHAR2(50);
155:
156:
157: BEGIN
158: h_message := BSC_APPS.Get_Message('BSC_MIG_ASG_KPIS_TO_RESPS');
159: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
160:
161: -- Delete current assigments
162: h_sql := 'DELETE FROM bsc_user_tab_access';
155:
156:
157: BEGIN
158: h_message := BSC_APPS.Get_Message('BSC_MIG_ASG_KPIS_TO_RESPS');
159: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
160:
161: -- Delete current assigments
162: h_sql := 'DELETE FROM bsc_user_tab_access';
163: BSC_APPS.Execute_Immediate(h_sql);
159: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
160:
161: -- Delete current assigments
162: h_sql := 'DELETE FROM bsc_user_tab_access';
163: BSC_APPS.Execute_Immediate(h_sql);
164:
165: h_sql := 'DELETE FROM bsc_user_list_access';
166: BSC_APPS.Execute_Immediate(h_sql);
167:
162: h_sql := 'DELETE FROM bsc_user_tab_access';
163: BSC_APPS.Execute_Immediate(h_sql);
164:
165: h_sql := 'DELETE FROM bsc_user_list_access';
166: BSC_APPS.Execute_Immediate(h_sql);
167:
168: h_sql := 'DELETE FROM bsc_user_kpi_access';
169: BSC_APPS.Execute_Immediate(h_sql);
170:
165: h_sql := 'DELETE FROM bsc_user_list_access';
166: BSC_APPS.Execute_Immediate(h_sql);
167:
168: h_sql := 'DELETE FROM bsc_user_kpi_access';
169: BSC_APPS.Execute_Immediate(h_sql);
170:
171: -- Transfer source assigments to target
172: -- Initialize the array of distinct target responsibilities
173: h_num_dist_trg_resps := 0;
178: END IF;
179: END LOOP;
180:
181: -- Assign each different target responsibility base on his source responsibilities
182: h_tab_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'tab_id');
183: FOR h_i IN 1 .. g_num_mig_tabs LOOP
184: BSC_APPS.Add_Value_Big_In_Cond(1, g_mig_tabs(h_i));
185: END LOOP;
186:
180:
181: -- Assign each different target responsibility base on his source responsibilities
182: h_tab_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'tab_id');
183: FOR h_i IN 1 .. g_num_mig_tabs LOOP
184: BSC_APPS.Add_Value_Big_In_Cond(1, g_mig_tabs(h_i));
185: END LOOP;
186:
187: h_kpi_condition := BSC_APPS.Get_New_Big_In_Cond_Number(2, 'indicator');
188: FOR h_i IN 1 .. g_num_mig_kpis LOOP
183: FOR h_i IN 1 .. g_num_mig_tabs LOOP
184: BSC_APPS.Add_Value_Big_In_Cond(1, g_mig_tabs(h_i));
185: END LOOP;
186:
187: h_kpi_condition := BSC_APPS.Get_New_Big_In_Cond_Number(2, 'indicator');
188: FOR h_i IN 1 .. g_num_mig_kpis LOOP
189: BSC_APPS.Add_Value_Big_In_Cond(2, g_mig_kpis(h_i));
190: END LOOP;
191:
185: END LOOP;
186:
187: h_kpi_condition := BSC_APPS.Get_New_Big_In_Cond_Number(2, 'indicator');
188: FOR h_i IN 1 .. g_num_mig_kpis LOOP
189: BSC_APPS.Add_Value_Big_In_Cond(2, g_mig_kpis(h_i));
190: END LOOP;
191:
192: FOR h_i IN 1..h_num_dist_trg_resps LOOP
193: -- Get the componets for the queries
192: FOR h_i IN 1..h_num_dist_trg_resps LOOP
193: -- Get the componets for the queries
194: h_decode_lst := NULL;
195:
196: h_src_resps_condition := BSC_APPS.Get_New_Big_In_Cond_Number(3, 'responsibility_id');
197: FOR h_j IN 1..g_num_trg_resps LOOP
198: IF g_trg_resps(h_j) = h_dist_trg_resps(h_i) THEN
199: IF h_decode_lst IS NULL THEN
200: h_decode_lst := g_src_resps(h_j)||', '||h_dist_trg_resps(h_i);
201: ELSE
202: h_decode_lst := h_decode_lst||', '||g_src_resps(h_j)||', '||h_dist_trg_resps(h_i);
203: END IF;
204:
205: BSC_APPS.Add_Value_Big_In_Cond(3, g_src_resps(h_j));
206: END IF;
207: END LOOP;
208:
209: -- Transfer responsibilities for this target responsibility. All this logica is because
223: IF h_tab_condition IS NOT NULL THEN
224: h_sql := h_sql||' AND ('||h_tab_condition||')';
225: END IF;
226: h_sql := h_sql||' GROUP BY DECODE(responsibility_id, '||h_decode_lst||'), tab_id';
227: BSC_APPS.Execute_Immediate(h_sql);
228:
229: -- BSC_USER_LIST_ACCESS
230: h_sql := 'INSERT INTO bsc_user_list_access (responsibility_id, tab_id, dim_level_index,'||
231: ' dim_level_value, creation_date, created_by, last_update_date, last_updated_by,'||
238: IF h_tab_condition IS NOT NULL THEN
239: h_sql := h_sql||' AND ('||h_tab_condition||')';
240: END IF;
241: h_sql := h_sql||' GROUP BY DECODE(responsibility_id, '||h_decode_lst||'), tab_id, dim_level_index';
242: BSC_APPS.Execute_Immediate(h_sql);
243:
244: -- BSC_USER_KPI_ACCESS
245: h_sql := 'INSERT INTO bsc_user_kpi_access (responsibility_id, indicator, creation_date,'||
246: ' created_by, last_update_date, last_updated_by, last_update_login, start_date,'||
253: IF h_kpi_condition IS NOT NULL THEN
254: h_sql := h_sql||' AND ('||h_kpi_condition||')';
255: END IF;
256: h_sql := h_sql||' GROUP BY DECODE(responsibility_id, '||h_decode_lst||'), indicator';
257: BSC_APPS.Execute_Immediate(h_sql);
258:
259: END LOOP;
260:
261: -- Assign custom links to the target responsibilities based on the accessibility of the source
259: END LOOP;
260:
261: -- Assign custom links to the target responsibilities based on the accessibility of the source
262: -- responsibilities
263: IF BSC_APPS.APPS_ENV AND g_src_apps_flag THEN
264: -- Source and target systems are in APPS
265: FOR h_i IN 1..g_num_trg_resps LOOP
266: IF NOT BSC_LAUNCH_PAD_PVT.Migrate_Custom_Links_Security(g_trg_resps(h_i), g_src_resps(h_i), g_db_link) THEN
267: RAISE e_unexpected_error;
284: RETURN TRUE;
285:
286: EXCEPTION
287: WHEN e_unexpected_error THEN
288: BSC_MESSAGE.Add (x_message => BSC_APPS.Get_Message('BSC_MIG_FAIL_EXEC'),
289: x_source => 'BSC_MIGRATION.Assign_Target_Responsibilities');
290: RETURN FALSE;
291:
292: WHEN e_add_roles_scorecard THEN
337: CURSOR c_columns_apps IS
338: SELECT column_name
339: FROM all_tab_columns
340: WHERE table_name = UPPER(h_table_name) AND
341: owner = UPPER(BSC_APPS.BSC_APPS_SCHEMA);
342:
343: BEGIN
344: -- Get supported languages in source system
345: h_sql := 'SELECT DISTINCT language_code, installed_flag'||
390: h_src_languages,
391: h_num_src_languages) THEN
392: h_lst_insert := NULL;
393: h_lst_select := NULL;
394: IF NOT BSC_APPS.APPS_ENV THEN
395: -- Personal mode
396: OPEN c_columns;
397: FETCH c_columns INTO h_column_name;
398: WHILE c_columns%FOUND LOOP
442: /* ' WHERE LANGUAGE = '''||h_src_base_language||'''';*/
443:
444: EXECUTE IMMEDIATE h_sql USING h_src_base_language;
445:
446: /* BSC_APPS.Execute_Immediate(h_sql); */
447:
448: END IF;
449: END LOOP;
450:
458: ' WHERE LANGUAGE = :1';
459: /* ' WHERE LANGUAGE = '''||h_src_languages(h_j)||'''';*/
460: EXECUTE IMMEDIATE h_sql USING h_src_languages(h_j);
461:
462: /*BSC_APPS.Execute_Immediate(h_sql); */
463: END IF;
464: END LOOP;
465:
466: COMMIT;
485: h_src_languages,
486: h_num_src_languages) THEN
487: h_lst_insert := NULL;
488: h_lst_select := NULL;
489: IF NOT BSC_APPS.APPS_ENV THEN
490: -- Personal mode
491: OPEN c_columns;
492: FETCH c_columns INTO h_column_name;
493: WHILE c_columns%FOUND LOOP
577: BEGIN
578: -- Clean metadata from invalid measures
579:
580: IF g_num_invalid_pmf_measures > 0 THEN
581: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'short_name');
582: FOR h_i IN 1 .. g_num_invalid_pmf_measures LOOP
583: BSC_APPS.Add_Value_Big_In_Cond(1, g_invalid_pmf_measures(h_i));
584: END LOOP;
585:
579:
580: IF g_num_invalid_pmf_measures > 0 THEN
581: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'short_name');
582: FOR h_i IN 1 .. g_num_invalid_pmf_measures LOOP
583: BSC_APPS.Add_Value_Big_In_Cond(1, g_invalid_pmf_measures(h_i));
584: END LOOP;
585:
586: -- BSC_SYS_DATASET_CALC
587: h_sql := 'DELETE FROM bsc_sys_dataset_calc'||
593: ' FROM bsc_sys_measures'||
594: ' WHERE '||h_condition||
595: ' )'||
596: ' )';
597: BSC_APPS.Execute_Immediate(h_sql);
598:
599: -- BSC_SYS_DATASETS_TL
600: h_sql := 'DELETE FROM bsc_sys_datasets_tl'||
601: ' WHERE dataset_id IN ('||
606: ' FROM bsc_sys_measures'||
607: ' WHERE '||h_condition||
608: ' )'||
609: ' )';
610: BSC_APPS.Execute_Immediate(h_sql);
611:
612: -- BSC_SYS_DATASETS_B
613: h_sql := 'DELETE FROM bsc_sys_datasets_b'||
614: ' WHERE measure_id1 IN ('||
615: ' SELECT measure_id'||
616: ' FROM bsc_sys_measures'||
617: ' WHERE '||h_condition||
618: ' )';
619: BSC_APPS.Execute_Immediate(h_sql);
620:
621: -- BSC_DB_MEASURE_COLS_TL
622: h_sql := 'DELETE FROM bsc_db_measure_cols_tl'||
623: ' WHERE measure_col IN ('||
624: ' SELECT measure_col'||
625: ' FROM bsc_sys_measures'||
626: ' WHERE '||h_condition||
627: ' )';
628: BSC_APPS.Execute_Immediate(h_sql);
629:
630: -- BSC_SYS_MEASURES
631: h_sql := 'DELETE FROM bsc_sys_measures'||
632: ' WHERE '||h_condition;
629:
630: -- BSC_SYS_MEASURES
631: h_sql := 'DELETE FROM bsc_sys_measures'||
632: ' WHERE '||h_condition;
633: BSC_APPS.Execute_Immediate(h_sql);
634:
635: END IF;
636:
637: -- Clean metadata from invalid dimensions (dimension groups)
636:
637: -- Clean metadata from invalid dimensions (dimension groups)
638:
639: IF g_num_invalid_pmf_dimensions > 0 THEN
640: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'short_name');
641: FOR h_i IN 1 .. g_num_invalid_pmf_dimensions LOOP
642: BSC_APPS.Add_Value_Big_In_Cond(1, g_invalid_pmf_dimensions(h_i));
643: END LOOP;
644:
638:
639: IF g_num_invalid_pmf_dimensions > 0 THEN
640: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'short_name');
641: FOR h_i IN 1 .. g_num_invalid_pmf_dimensions LOOP
642: BSC_APPS.Add_Value_Big_In_Cond(1, g_invalid_pmf_dimensions(h_i));
643: END LOOP;
644:
645: -- BSC_SYS_DIM_LEVELS_BY_GROUP
646: h_sql := 'DELETE FROM bsc_sys_dim_levels_by_group'||
648: ' SELECT dim_group_id'||
649: ' FROM bsc_sys_dim_groups_tl'||
650: ' WHERE '||h_condition||
651: ' )';
652: BSC_APPS.Execute_Immediate(h_sql);
653:
654: -- BSC_SYS_DIM_GROUPS_TL
655: h_sql := 'DELETE FROM bsc_sys_dim_groups_tl'||
656: ' WHERE '||h_condition;
653:
654: -- BSC_SYS_DIM_GROUPS_TL
655: h_sql := 'DELETE FROM bsc_sys_dim_groups_tl'||
656: ' WHERE '||h_condition;
657: BSC_APPS.Execute_Immediate(h_sql);
658:
659: END IF;
660:
661: -- Clean metadata from invalid dimension levels
660:
661: -- Clean metadata from invalid dimension levels
662:
663: IF g_num_invalid_pmf_dim_levels > 0 THEN
664: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'short_name');
665: FOR h_i IN 1 .. g_num_invalid_pmf_dim_levels LOOP
666: BSC_APPS.Add_Value_Big_In_Cond(1, g_invalid_pmf_dim_levels(h_i));
667: END LOOP;
668:
662:
663: IF g_num_invalid_pmf_dim_levels > 0 THEN
664: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'short_name');
665: FOR h_i IN 1 .. g_num_invalid_pmf_dim_levels LOOP
666: BSC_APPS.Add_Value_Big_In_Cond(1, g_invalid_pmf_dim_levels(h_i));
667: END LOOP;
668:
669: -- BSC_SYS_DIM_LEVELS_BY_GROUP
670: h_sql := 'DELETE FROM bsc_sys_dim_levels_by_group'||
672: ' SELECT dim_level_id'||
673: ' FROM bsc_sys_dim_levels_b'||
674: ' WHERE '||h_condition||
675: ' )';
676: BSC_APPS.Execute_Immediate(h_sql);
677:
678: -- BSC_SYS_DIM_LEVEL_COLS
679: h_sql := 'DELETE FROM bsc_sys_dim_level_cols'||
680: ' WHERE dim_level_id IN ('||
681: ' SELECT dim_level_id'||
682: ' FROM bsc_sys_dim_levels_b'||
683: ' WHERE '||h_condition||
684: ' )';
685: BSC_APPS.Execute_Immediate(h_sql);
686:
687: -- BSC_SYS_DIM_LEVEL_RELS (dim_level_id)
688: h_sql := 'DELETE FROM bsc_sys_dim_level_rels'||
689: ' WHERE dim_level_id IN ('||
690: ' SELECT dim_level_id'||
691: ' FROM bsc_sys_dim_levels_b'||
692: ' WHERE '||h_condition||
693: ' )';
694: BSC_APPS.Execute_Immediate(h_sql);
695:
696: -- BSC_SYS_DIM_LEVEL_RELS (parent_dim_level_id)
697: h_sql := 'DELETE FROM bsc_sys_dim_level_rels'||
698: ' WHERE parent_dim_level_id IN ('||
699: ' SELECT dim_level_id'||
700: ' FROM bsc_sys_dim_levels_b'||
701: ' WHERE '||h_condition||
702: ' )';
703: BSC_APPS.Execute_Immediate(h_sql);
704:
705: -- BSC_SYS_DIM_LEVELS_TL
706: h_sql := 'DELETE FROM bsc_sys_dim_levels_tl'||
707: ' WHERE dim_level_id IN ('||
708: ' SELECT dim_level_id'||
709: ' FROM bsc_sys_dim_levels_b'||
710: ' WHERE '||h_condition||
711: ' )';
712: BSC_APPS.Execute_Immediate(h_sql);
713:
714: -- BSC_SYS_DIM_LEVELS_B
715: h_sql := 'DELETE FROM bsc_sys_dim_levels_b'||
716: ' WHERE '||h_condition;
713:
714: -- BSC_SYS_DIM_LEVELS_B
715: h_sql := 'DELETE FROM bsc_sys_dim_levels_b'||
716: ' WHERE '||h_condition;
717: BSC_APPS.Execute_Immediate(h_sql);
718:
719: END IF;
720:
721: COMMIT;
776:
777: h_table := UPPER(x_table);
778:
779: --Bug3329329 Drop the table if exists before trying to create it
780: IF BSC_APPS.Table_Exists(h_table) THEN
781: -- Fix perf bug#4583017: do not truncate, just drop.
782: -- Truncate table to release space --> Bug: DROP TABLE don't release the space immediately
783: --h_sql := 'TRUNCATE TABLE '||h_table;
784: --BSC_APPS.Do_DDL(h_sql, AD_DDL.TRUNCATE_TABLE, h_table);
780: IF BSC_APPS.Table_Exists(h_table) THEN
781: -- Fix perf bug#4583017: do not truncate, just drop.
782: -- Truncate table to release space --> Bug: DROP TABLE don't release the space immediately
783: --h_sql := 'TRUNCATE TABLE '||h_table;
784: --BSC_APPS.Do_DDL(h_sql, AD_DDL.TRUNCATE_TABLE, h_table);
785:
786: h_sql := 'DROP TABLE '||h_table;
787: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
788: END IF;
783: --h_sql := 'TRUNCATE TABLE '||h_table;
784: --BSC_APPS.Do_DDL(h_sql, AD_DDL.TRUNCATE_TABLE, h_table);
785:
786: h_sql := 'DROP TABLE '||h_table;
787: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
788: END IF;
789: ---------
790:
791: IF NOT g_src_apps_flag THEN
917: ')';
918: IF x_with_partitions THEN
919: h_sql := h_sql||' '||bsc_dbgen_metadata_reader.get_partition_clause;
920: END IF;
921: h_sql := h_sql||' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(x_tbs_type)||' '||h_storage;
922:
923: BSC_APPS.Do_DDL(h_sql, AD_DDL.CREATE_TABLE, x_table);
924: END IF;
925:
919: h_sql := h_sql||' '||bsc_dbgen_metadata_reader.get_partition_clause;
920: END IF;
921: h_sql := h_sql||' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(x_tbs_type)||' '||h_storage;
922:
923: BSC_APPS.Do_DDL(h_sql, AD_DDL.CREATE_TABLE, x_table);
924: END IF;
925:
926: RETURN TRUE;
927:
995: IF x_replace THEN
996: h_sql := h_sql||' OR REPLACE';
997: END IF;
998: h_sql := h_sql||' FORCE VIEW '||x_view||' ('||h_lst_columns||') AS '||h_text;
999: BSC_APPS.Execute_DDL(h_sql);
1000: EXCEPTION
1001: WHEN OTHERS THEN
1002: IF (SQLCODE = -24344) THEN
1003: NULL; -- Ignore, success with compilation error
1062: h_num_key_columns NUMBER;
1063: h_key_columns_create BOOLEAN;
1064: BEGIN
1065:
1066: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_CREATE_OBJS'), BSC_APPS.OUTPUT_FILE);
1067:
1068: h_base_message := BSC_APPS.Get_Message('BSC_CREATING_TABLE');
1069: h_base_message_v := BSC_APPS.Get_Message('BSC_CREATING_VIEW');
1070:
1064: BEGIN
1065:
1066: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_CREATE_OBJS'), BSC_APPS.OUTPUT_FILE);
1067:
1068: h_base_message := BSC_APPS.Get_Message('BSC_CREATING_TABLE');
1069: h_base_message_v := BSC_APPS.Get_Message('BSC_CREATING_VIEW');
1070:
1071: -- Dimension tables
1072: h_sql := 'SELECT level_table_name, level_view_name, nvl(source, ''BSC'')'||
1065:
1066: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_CREATE_OBJS'), BSC_APPS.OUTPUT_FILE);
1067:
1068: h_base_message := BSC_APPS.Get_Message('BSC_CREATING_TABLE');
1069: h_base_message_v := BSC_APPS.Get_Message('BSC_CREATING_VIEW');
1070:
1071: -- Dimension tables
1072: h_sql := 'SELECT level_table_name, level_view_name, nvl(source, ''BSC'')'||
1073: ' FROM bsc_sys_dim_levels_b';
1080: -- is the same and corresponds to a view
1081:
1082: IF h_source = 'BSC' THEN
1083: -- create dimension table
1084: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1085: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1086:
1087: IF NOT Create_Copy_Of_Table_Def(h_table, BSC_APPS.dimension_table_tbs_type) THEN
1088: RAISE e_unexpected_error;
1081:
1082: IF h_source = 'BSC' THEN
1083: -- create dimension table
1084: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1085: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1086:
1087: IF NOT Create_Copy_Of_Table_Def(h_table, BSC_APPS.dimension_table_tbs_type) THEN
1088: RAISE e_unexpected_error;
1089: END IF;
1083: -- create dimension table
1084: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1085: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1086:
1087: IF NOT Create_Copy_Of_Table_Def(h_table, BSC_APPS.dimension_table_tbs_type) THEN
1088: RAISE e_unexpected_error;
1089: END IF;
1090: END IF;
1091:
1089: END IF;
1090: END IF;
1091:
1092: -- Create vl view
1093: h_message := BSC_APPS.Replace_Token(h_base_message_v, 'VIEW', h_view);
1094: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1095:
1096: IF NOT Create_Copy_Of_View_Def(h_view) THEN
1097: RAISE e_unexpected_error;
1090: END IF;
1091:
1092: -- Create vl view
1093: h_message := BSC_APPS.Replace_Token(h_base_message_v, 'VIEW', h_view);
1094: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1095:
1096: IF NOT Create_Copy_Of_View_Def(h_view) THEN
1097: RAISE e_unexpected_error;
1098: END IF;
1109: FETCH h_cursor INTO h_table;
1110: EXIT WHEN h_cursor%NOTFOUND;
1111:
1112: -- create dimension input table
1113: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1114: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1115:
1116: IF NOT Create_Copy_Of_Table_Def(h_table, BSC_APPS.input_table_tbs_type) THEN
1117: RAISE e_unexpected_error;
1110: EXIT WHEN h_cursor%NOTFOUND;
1111:
1112: -- create dimension input table
1113: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1114: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1115:
1116: IF NOT Create_Copy_Of_Table_Def(h_table, BSC_APPS.input_table_tbs_type) THEN
1117: RAISE e_unexpected_error;
1118: END IF;
1112: -- create dimension input table
1113: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1114: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1115:
1116: IF NOT Create_Copy_Of_Table_Def(h_table, BSC_APPS.input_table_tbs_type) THEN
1117: RAISE e_unexpected_error;
1118: END IF;
1119:
1120: END LOOP;
1129: LOOP
1130: FETCH h_cursor INTO h_table;
1131: EXIT WHEN h_cursor%NOTFOUND;
1132:
1133: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1134: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1135:
1136: IF NOT Create_Copy_Of_Table_Def(h_table, BSC_APPS.dimension_table_tbs_type) THEN
1137: RAISE e_unexpected_error;
1130: FETCH h_cursor INTO h_table;
1131: EXIT WHEN h_cursor%NOTFOUND;
1132:
1133: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1134: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1135:
1136: IF NOT Create_Copy_Of_Table_Def(h_table, BSC_APPS.dimension_table_tbs_type) THEN
1137: RAISE e_unexpected_error;
1138: END IF;
1132:
1133: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1134: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1135:
1136: IF NOT Create_Copy_Of_Table_Def(h_table, BSC_APPS.dimension_table_tbs_type) THEN
1137: RAISE e_unexpected_error;
1138: END IF;
1139: END LOOP;
1140: CLOSE h_cursor;
1140: CLOSE h_cursor;
1141:
1142:
1143: -- Filter views
1144: h_base_message := BSC_APPS.Get_Message('BSC_CREATING_VIEW');
1145:
1146: h_sql := 'SELECT level_view_name'||
1147: ' FROM bsc_sys_filters_views';
1148: OPEN h_cursor FOR h_sql;
1149: LOOP
1150: FETCH h_cursor INTO h_view;
1151: EXIT WHEN h_cursor%NOTFOUND;
1152:
1153: h_message := BSC_APPS.Replace_Token(h_base_message, 'VIEW', h_view);
1154: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1155:
1156: IF NOT Create_Copy_Of_View_Def(h_view) THEN
1157: RAISE e_unexpected_error;
1150: FETCH h_cursor INTO h_view;
1151: EXIT WHEN h_cursor%NOTFOUND;
1152:
1153: h_message := BSC_APPS.Replace_Token(h_base_message, 'VIEW', h_view);
1154: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1155:
1156: IF NOT Create_Copy_Of_View_Def(h_view) THEN
1157: RAISE e_unexpected_error;
1158: END IF;
1161: --rkumar:bug#5943112
1162: h_max_partitions := bsc_dbgen_metadata_reader.get_max_partitions;
1163:
1164: -- Data tables
1165: h_base_message := BSC_APPS.Get_Message('BSC_CREATING_TABLE');
1166:
1167: FOR h_i IN 1..g_num_mig_tables LOOP
1168: h_table := g_mig_tables(h_i);
1169: h_num_key_columns := 0;
1182: --BSC-MV Note: Create only existing tables
1183: IF g_adv_sum_level IS NULL THEN
1184: -- BSC summary tables architecture. All summary tables exists
1185: IF Is_Input_Table(h_table) THEN
1186: h_tbs_type := BSC_APPS.input_table_tbs_type;
1187:
1188: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1189: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1190: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type) THEN
1184: -- BSC summary tables architecture. All summary tables exists
1185: IF Is_Input_Table(h_table) THEN
1186: h_tbs_type := BSC_APPS.input_table_tbs_type;
1187:
1188: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1189: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1190: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type) THEN
1191: RAISE e_unexpected_error;
1192: END IF;
1185: IF Is_Input_Table(h_table) THEN
1186: h_tbs_type := BSC_APPS.input_table_tbs_type;
1187:
1188: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1189: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1190: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type) THEN
1191: RAISE e_unexpected_error;
1192: END IF;
1193:
1193:
1194: -- ENH_B_TABLES_PERF: check if the input table has a rowid table
1195: h_rowid_table_name := Get_RowId_Table_Name(h_table);
1196: IF h_rowid_table_name IS NOT NULL THEN
1197: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_rowid_table_name);
1198: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1199: IF NOT Create_Copy_Of_Table_Def(h_rowid_table_name, h_tbs_type) THEN
1200: RAISE e_unexpected_error;
1201: END IF;
1194: -- ENH_B_TABLES_PERF: check if the input table has a rowid table
1195: h_rowid_table_name := Get_RowId_Table_Name(h_table);
1196: IF h_rowid_table_name IS NOT NULL THEN
1197: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_rowid_table_name);
1198: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1199: IF NOT Create_Copy_Of_Table_Def(h_rowid_table_name, h_tbs_type) THEN
1200: RAISE e_unexpected_error;
1201: END IF;
1202: END IF;
1201: END IF;
1202: END IF;
1203:
1204: ELSIF Is_Base_Table(h_table) THEN
1205: h_tbs_type := BSC_APPS.base_table_tbs_type;
1206:
1207: -- ENH_B_TABLES_PERF: check if the base table has a projection table
1208: h_proj_table_name := Get_Proj_Table_Name(h_table);
1209: IF h_proj_table_name IS NULL THEN
1207: -- ENH_B_TABLES_PERF: check if the base table has a projection table
1208: h_proj_table_name := Get_Proj_Table_Name(h_table);
1209: IF h_proj_table_name IS NULL THEN
1210: -- create the base table in the standard way
1211: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1212: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1213: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type) THEN
1214: RAISE e_unexpected_error;
1215: END IF;
1208: h_proj_table_name := Get_Proj_Table_Name(h_table);
1209: IF h_proj_table_name IS NULL THEN
1210: -- create the base table in the standard way
1211: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1212: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1213: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type) THEN
1214: RAISE e_unexpected_error;
1215: END IF;
1216: ELSE
1214: RAISE e_unexpected_error;
1215: END IF;
1216: ELSE
1217: -- new strategy: b table has a projection table and may be partitioned
1218: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1219: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1220: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type, h_key_columns_create) THEN
1221: RAISE e_unexpected_error;
1222: END IF;
1215: END IF;
1216: ELSE
1217: -- new strategy: b table has a projection table and may be partitioned
1218: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1219: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1220: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type, h_key_columns_create) THEN
1221: RAISE e_unexpected_error;
1222: END IF;
1223: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_proj_table_name);
1219: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1220: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type, h_key_columns_create) THEN
1221: RAISE e_unexpected_error;
1222: END IF;
1223: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_proj_table_name);
1224: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1225: IF NOT Create_Copy_Of_Table_Def(h_proj_table_name, h_tbs_type, h_key_columns_create) THEN
1226: RAISE e_unexpected_error;
1227: END IF;
1220: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type, h_key_columns_create) THEN
1221: RAISE e_unexpected_error;
1222: END IF;
1223: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_proj_table_name);
1224: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1225: IF NOT Create_Copy_Of_Table_Def(h_proj_table_name, h_tbs_type, h_key_columns_create) THEN
1226: RAISE e_unexpected_error;
1227: END IF;
1228:
1227: END IF;
1228:
1229: END IF;
1230: ELSE
1231: h_tbs_type := BSC_APPS.summary_table_tbs_type;
1232:
1233: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1234: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1235: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type) THEN
1229: END IF;
1230: ELSE
1231: h_tbs_type := BSC_APPS.summary_table_tbs_type;
1232:
1233: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1234: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1235: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type) THEN
1236: RAISE e_unexpected_error;
1237: END IF;
1230: ELSE
1231: h_tbs_type := BSC_APPS.summary_table_tbs_type;
1232:
1233: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1234: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1235: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type) THEN
1236: RAISE e_unexpected_error;
1237: END IF;
1238: END IF;
1239: ELSE
1240: -- BSC-MV architecture. Generation type = -1 means the table does not exists
1241: IF BSC_UPDATE_UTIL.Get_Table_Generation_Type(h_table) <> -1 THEN
1242: IF Is_Input_Table(h_table) THEN
1243: h_tbs_type := BSC_APPS.input_table_tbs_type;
1244:
1245: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1246: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1247: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type) THEN
1241: IF BSC_UPDATE_UTIL.Get_Table_Generation_Type(h_table) <> -1 THEN
1242: IF Is_Input_Table(h_table) THEN
1243: h_tbs_type := BSC_APPS.input_table_tbs_type;
1244:
1245: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1246: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1247: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type) THEN
1248: RAISE e_unexpected_error;
1249: END IF;
1242: IF Is_Input_Table(h_table) THEN
1243: h_tbs_type := BSC_APPS.input_table_tbs_type;
1244:
1245: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1246: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1247: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type) THEN
1248: RAISE e_unexpected_error;
1249: END IF;
1250:
1250:
1251: -- ENH_B_TABLES_PERF: check if the input table has a rowid table
1252: h_rowid_table_name := Get_RowId_Table_Name(h_table);
1253: IF h_rowid_table_name IS NOT NULL THEN
1254: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_rowid_table_name);
1255: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1256: IF NOT Create_Copy_Of_Table_Def(h_rowid_table_name, h_tbs_type) THEN
1257: RAISE e_unexpected_error;
1258: END IF;
1251: -- ENH_B_TABLES_PERF: check if the input table has a rowid table
1252: h_rowid_table_name := Get_RowId_Table_Name(h_table);
1253: IF h_rowid_table_name IS NOT NULL THEN
1254: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_rowid_table_name);
1255: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1256: IF NOT Create_Copy_Of_Table_Def(h_rowid_table_name, h_tbs_type) THEN
1257: RAISE e_unexpected_error;
1258: END IF;
1259: END IF;
1258: END IF;
1259: END IF;
1260:
1261: ELSIF Is_Base_Table(h_table) THEN
1262: h_tbs_type := BSC_APPS.base_table_tbs_type;
1263:
1264: -- ENH_B_TABLES_PERF: check if the base table has a projection table in the source
1265: h_proj_table_name := Get_Proj_Table_Name(h_table);
1266: IF h_proj_table_name IS NULL THEN
1264: -- ENH_B_TABLES_PERF: check if the base table has a projection table in the source
1265: h_proj_table_name := Get_Proj_Table_Name(h_table);
1266: IF h_proj_table_name IS NULL THEN
1267: -- create the base table in the standard way
1268: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1269: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1270: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type) THEN
1271: RAISE e_unexpected_error;
1272: END IF;
1265: h_proj_table_name := Get_Proj_Table_Name(h_table);
1266: IF h_proj_table_name IS NULL THEN
1267: -- create the base table in the standard way
1268: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1269: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1270: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type) THEN
1271: RAISE e_unexpected_error;
1272: END IF;
1273: ELSE
1271: RAISE e_unexpected_error;
1272: END IF;
1273: ELSE
1274: -- new strategy: b table has a projection table and may be partitioned
1275: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1276: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1277: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type, h_key_columns_create) THEN
1278: RAISE e_unexpected_error;
1279: END IF;
1272: END IF;
1273: ELSE
1274: -- new strategy: b table has a projection table and may be partitioned
1275: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1276: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1277: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type, h_key_columns_create) THEN
1278: RAISE e_unexpected_error;
1279: END IF;
1280: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_proj_table_name);
1276: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1277: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type, h_key_columns_create) THEN
1278: RAISE e_unexpected_error;
1279: END IF;
1280: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_proj_table_name);
1281: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1282: IF NOT Create_Copy_Of_Table_Def(h_proj_table_name, h_tbs_type, h_key_columns_create) THEN
1283: RAISE e_unexpected_error;
1284: END IF;
1277: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type, h_key_columns_create) THEN
1278: RAISE e_unexpected_error;
1279: END IF;
1280: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_proj_table_name);
1281: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1282: IF NOT Create_Copy_Of_Table_Def(h_proj_table_name, h_tbs_type, h_key_columns_create) THEN
1283: RAISE e_unexpected_error;
1284: END IF;
1285: END IF;
1284: END IF;
1285: END IF;
1286:
1287: ELSE
1288: h_tbs_type := BSC_APPS.summary_table_tbs_type;
1289:
1290: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1291: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1292: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type) THEN
1286:
1287: ELSE
1288: h_tbs_type := BSC_APPS.summary_table_tbs_type;
1289:
1290: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1291: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1292: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type) THEN
1293: RAISE e_unexpected_error;
1294: END IF;
1287: ELSE
1288: h_tbs_type := BSC_APPS.summary_table_tbs_type;
1289:
1290: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1291: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1292: IF NOT Create_Copy_Of_Table_Def(h_table, h_tbs_type) THEN
1293: RAISE e_unexpected_error;
1294: END IF;
1295: END IF;
1306: LOOP
1307: FETCH h_cursor INTO h_table;
1308: EXIT WHEN h_cursor%NOTFOUND;
1309:
1310: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1311: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1312:
1313: IF NOT Create_Copy_Of_Table_Def(h_table, BSC_APPS.summary_table_tbs_type) THEN
1314: RAISE e_unexpected_error;
1307: FETCH h_cursor INTO h_table;
1308: EXIT WHEN h_cursor%NOTFOUND;
1309:
1310: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1311: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1312:
1313: IF NOT Create_Copy_Of_Table_Def(h_table, BSC_APPS.summary_table_tbs_type) THEN
1314: RAISE e_unexpected_error;
1315: END IF;
1309:
1310: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1311: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1312:
1313: IF NOT Create_Copy_Of_Table_Def(h_table, BSC_APPS.summary_table_tbs_type) THEN
1314: RAISE e_unexpected_error;
1315: END IF;
1316: END LOOP;
1317: CLOSE h_cursor;
1320: RETURN TRUE;
1321:
1322: EXCEPTION
1323: WHEN e_unexpected_error THEN
1324: BSC_MESSAGE.Add (x_message => BSC_APPS.Get_Message('BSC_MIG_FAIL_CREATE_OBJS'),
1325: x_source => 'BSC_MIGRATION.Create_Dynamic_Objects');
1326: RETURN FALSE;
1327:
1328: WHEN OTHERS THEN
1353: h_proj_table_name VARCHAR2(30);
1354:
1355: BEGIN
1356:
1357: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_CREATE_INDEXES'), BSC_APPS.OUTPUT_FILE);
1358:
1359: -- Dimension tables
1360: h_sql := 'SELECT level_table_name'||
1361: ' FROM bsc_sys_dim_levels_b';
1362: OPEN h_cursor FOR h_sql;
1363: LOOP
1364: FETCH h_cursor INTO h_table;
1365: EXIT WHEN h_cursor%NOTFOUND;
1366: IF NOT Create_Table_Indexes(h_table, BSC_APPS.dimension_index_tbs_type) THEN
1367: RAISE e_unexpected_error;
1368: END IF;
1369: END LOOP;
1370: CLOSE h_cursor;
1377: LOOP
1378: FETCH h_cursor INTO h_table;
1379: EXIT WHEN h_cursor%NOTFOUND;
1380:
1381: IF NOT Create_Table_Indexes(h_table, BSC_APPS.input_index_tbs_type) THEN
1382: RAISE e_unexpected_error;
1383: END IF;
1384: END LOOP;
1385: CLOSE h_cursor;
1392: LOOP
1393: FETCH h_cursor INTO h_table;
1394: EXIT WHEN h_cursor%NOTFOUND;
1395:
1396: IF NOT Create_Table_Indexes(h_table, BSC_APPS.dimension_index_tbs_type) THEN
1397: RAISE e_unexpected_error;
1398: END IF;
1399: END LOOP;
1400: CLOSE h_cursor;
1407: IF g_adv_sum_level IS NULL THEN
1408: -- BSC summary tables architecture. All summary tables exists
1409:
1410: IF Is_Input_Table(h_table) THEN
1411: h_tbs_type := BSC_APPS.input_index_tbs_type;
1412:
1413: IF NOT Create_Table_Indexes(h_table, h_tbs_type) THEN
1414: RAISE e_unexpected_error;
1415: END IF;
1422: END IF;
1423: END IF;
1424:
1425: ELSIF Is_Base_Table(h_table) THEN
1426: h_tbs_type := BSC_APPS.base_index_tbs_type;
1427:
1428: IF NOT Create_Table_Indexes(h_table, h_tbs_type) THEN
1429: RAISE e_unexpected_error;
1430: END IF;
1437: END IF;
1438: END IF;
1439:
1440: ELSE
1441: h_tbs_type := BSC_APPS.summary_index_tbs_type;
1442:
1443: IF NOT Create_Table_Indexes(h_table, h_tbs_type) THEN
1444: RAISE e_unexpected_error;
1445: END IF;
1448: ELSE
1449: -- BSC-MV architecture. Generation type = -1 means the table does not exists
1450: IF BSC_UPDATE_UTIL.Get_Table_Generation_Type(h_table) <> -1 THEN
1451: IF Is_Input_Table(h_table) THEN
1452: h_tbs_type := BSC_APPS.input_index_tbs_type;
1453:
1454: IF NOT Create_Table_Indexes(h_table, h_tbs_type) THEN
1455: RAISE e_unexpected_error;
1456: END IF;
1463: END IF;
1464: END IF;
1465:
1466: ELSIF Is_Base_Table(h_table) THEN
1467: h_tbs_type := BSC_APPS.base_index_tbs_type;
1468:
1469: IF NOT Create_Table_Indexes(h_table, h_tbs_type) THEN
1470: RAISE e_unexpected_error;
1471: END IF;
1478: END IF;
1479: END IF;
1480:
1481: ELSE
1482: h_tbs_type := BSC_APPS.summary_index_tbs_type;
1483:
1484: IF NOT Create_Table_Indexes(h_table, h_tbs_type) THEN
1485: RAISE e_unexpected_error;
1486: END IF;
1499: LOOP
1500: FETCH h_cursor INTO h_table;
1501: EXIT WHEN h_cursor%NOTFOUND;
1502:
1503: IF NOT Create_Table_Indexes(h_table, BSC_APPS.summary_index_tbs_type) THEN
1504: RAISE e_unexpected_error;
1505: END IF;
1506: END LOOP;
1507: CLOSE h_cursor;
1510: RETURN TRUE;
1511:
1512: EXCEPTION
1513: WHEN e_unexpected_error THEN
1514: BSC_MESSAGE.Add (x_message => BSC_APPS.Get_Message('BSC_MIG_FAIL_CREATE_OBJS'),
1515: x_source => 'BSC_MIGRATION.Create_Indexes_Dynamic_Tables');
1516: RETURN FALSE;
1517:
1518: WHEN OTHERS THEN
1608: -- ENH_B_TABLES_PERF: need to enhance this function to take into account bitmap indexes too
1609:
1610: h_table := UPPER(x_table);
1611:
1612: h_base_message := BSC_APPS.Get_Message('BSC_CREATING_INDEX');
1613:
1614: IF NOT g_src_apps_flag THEN
1615: -- The source is Non-Enterprise version
1616: h_sql := 'SELECT index_name, index_type, uniqueness, initial_extent, next_extent, max_extents, pct_increase, partitioned'||
1650: IF h_storage IS NOT NULL THEN
1651: h_storage := 'STORAGE ('||h_storage||')';
1652: END IF;
1653:
1654: h_message := BSC_APPS.Replace_Token(h_base_message, 'INDEX', h_index_name);
1655: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1656:
1657: IF NOT g_src_apps_flag THEN
1658: -- The source is Non-Enterprise version
1651: h_storage := 'STORAGE ('||h_storage||')';
1652: END IF;
1653:
1654: h_message := BSC_APPS.Replace_Token(h_base_message, 'INDEX', h_index_name);
1655: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1656:
1657: IF NOT g_src_apps_flag THEN
1658: -- The source is Non-Enterprise version
1659: h_sql := 'SELECT column_position, column_name'||
1714: END IF;
1715: END IF;
1716:
1717: h_sql := h_sql||' PARALLEL NOLOGGING COMPUTE STATISTICS'||
1718: ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(x_tbs_type)||' '||h_storage;
1719: BSC_APPS.Do_DDL(h_sql, AD_DDL.CREATE_INDEX, h_index_name);
1720: commit;
1721:
1722: -- Fix performance bug#3860149: do not leave the index in parallel
1715: END IF;
1716:
1717: h_sql := h_sql||' PARALLEL NOLOGGING COMPUTE STATISTICS'||
1718: ' TABLESPACE '||BSC_APPS.Get_Tablespace_Name(x_tbs_type)||' '||h_storage;
1719: BSC_APPS.Do_DDL(h_sql, AD_DDL.CREATE_INDEX, h_index_name);
1720: commit;
1721:
1722: -- Fix performance bug#3860149: do not leave the index in parallel
1723: -- Fix perf bug#4583017: Use NOLOGGING option
1720: commit;
1721:
1722: -- Fix performance bug#3860149: do not leave the index in parallel
1723: -- Fix perf bug#4583017: Use NOLOGGING option
1724: h_sql := 'ALTER INDEX '||BSC_APPS.bsc_apps_schema||'.'||h_index_name||' NOPARALLEL LOGGING';
1725: BSC_APPS.Execute_Immediate(h_sql);
1726: commit;
1727: END IF;
1728:
1721:
1722: -- Fix performance bug#3860149: do not leave the index in parallel
1723: -- Fix perf bug#4583017: Use NOLOGGING option
1724: h_sql := 'ALTER INDEX '||BSC_APPS.bsc_apps_schema||'.'||h_index_name||' NOPARALLEL LOGGING';
1725: BSC_APPS.Execute_Immediate(h_sql);
1726: commit;
1727: END IF;
1728:
1729: END LOOP;
1806: h_message VARCHAR2(4000);
1807: h_base_message VARCHAR2(4000);
1808:
1809: BEGIN
1810: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_DELETE_METADATA'), BSC_APPS.OUTPUT_FILE);
1811:
1812: h_base_message := BSC_APPS.Get_Message('BSC_DELETING_DATA');
1813:
1814: FOR h_i IN 1..g_num_metadata_tables LOOP
1808:
1809: BEGIN
1810: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_DELETE_METADATA'), BSC_APPS.OUTPUT_FILE);
1811:
1812: h_base_message := BSC_APPS.Get_Message('BSC_DELETING_DATA');
1813:
1814: FOR h_i IN 1..g_num_metadata_tables LOOP
1815: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', g_metadata_tables(h_i).table_name);
1816: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1811:
1812: h_base_message := BSC_APPS.Get_Message('BSC_DELETING_DATA');
1813:
1814: FOR h_i IN 1..g_num_metadata_tables LOOP
1815: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', g_metadata_tables(h_i).table_name);
1816: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1817:
1818: -- Fix bug#4430901: Use truncate instead of delete
1819: h_sql := 'TRUNCATE TABLE '||g_metadata_tables(h_i).table_name;
1812: h_base_message := BSC_APPS.Get_Message('BSC_DELETING_DATA');
1813:
1814: FOR h_i IN 1..g_num_metadata_tables LOOP
1815: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', g_metadata_tables(h_i).table_name);
1816: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1817:
1818: -- Fix bug#4430901: Use truncate instead of delete
1819: h_sql := 'TRUNCATE TABLE '||g_metadata_tables(h_i).table_name;
1820: BSC_APPS.Do_DDL(h_sql, AD_DDL.TRUNCATE_TABLE, g_metadata_tables(h_i).table_name);
1816: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1817:
1818: -- Fix bug#4430901: Use truncate instead of delete
1819: h_sql := 'TRUNCATE TABLE '||g_metadata_tables(h_i).table_name;
1820: BSC_APPS.Do_DDL(h_sql, AD_DDL.TRUNCATE_TABLE, g_metadata_tables(h_i).table_name);
1821: COMMIT;
1822: END LOOP;
1823:
1824: RETURN TRUE;
1885: e_error_drop_mv EXCEPTION;
1886:
1887: BEGIN
1888:
1889: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_DROP_OBJECTS'), BSC_APPS.OUTPUT_FILE);
1890:
1891: h_base_message := BSC_APPS.Get_Message('BSC_DROPPING_TABLE');
1892: h_base_message_v := BSC_APPS.Get_Message('BSC_DROPPING_VIEW');
1893:
1887: BEGIN
1888:
1889: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_DROP_OBJECTS'), BSC_APPS.OUTPUT_FILE);
1890:
1891: h_base_message := BSC_APPS.Get_Message('BSC_DROPPING_TABLE');
1892: h_base_message_v := BSC_APPS.Get_Message('BSC_DROPPING_VIEW');
1893:
1894: -- Drop dimension tables
1895: -- EDW Note: EDW Dimension tables are materialized views
1888:
1889: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_DROP_OBJECTS'), BSC_APPS.OUTPUT_FILE);
1890:
1891: h_base_message := BSC_APPS.Get_Message('BSC_DROPPING_TABLE');
1892: h_base_message_v := BSC_APPS.Get_Message('BSC_DROPPING_VIEW');
1893:
1894: -- Drop dimension tables
1895: -- EDW Note: EDW Dimension tables are materialized views
1896: OPEN c_dim_tables;
1897: FETCH c_dim_tables INTO h_table, h_view, h_edw_flag;
1898: WHILE c_dim_tables%FOUND LOOP
1899: IF h_edw_flag = 0 THEN
1900: -- BSC dimension
1901: IF BSC_APPS.Table_Exists(h_table) THEN
1902: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1903: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1904:
1905: -- Fix perf bug#4583017: do not truncate, just drop.
1898: WHILE c_dim_tables%FOUND LOOP
1899: IF h_edw_flag = 0 THEN
1900: -- BSC dimension
1901: IF BSC_APPS.Table_Exists(h_table) THEN
1902: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1903: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1904:
1905: -- Fix perf bug#4583017: do not truncate, just drop.
1906: -- Truncate table to release space --> Bug: DROP TABLE don't release the space immediately
1899: IF h_edw_flag = 0 THEN
1900: -- BSC dimension
1901: IF BSC_APPS.Table_Exists(h_table) THEN
1902: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1903: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1904:
1905: -- Fix perf bug#4583017: do not truncate, just drop.
1906: -- Truncate table to release space --> Bug: DROP TABLE don't release the space immediately
1907: --h_sql := 'TRUNCATE TABLE '||h_table;
1904:
1905: -- Fix perf bug#4583017: do not truncate, just drop.
1906: -- Truncate table to release space --> Bug: DROP TABLE don't release the space immediately
1907: --h_sql := 'TRUNCATE TABLE '||h_table;
1908: --BSC_APPS.Do_DDL(h_sql, AD_DDL.TRUNCATE_TABLE, h_table);
1909:
1910: h_sql := 'DROP TABLE '||h_table;
1911: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
1912: END IF;
1907: --h_sql := 'TRUNCATE TABLE '||h_table;
1908: --BSC_APPS.Do_DDL(h_sql, AD_DDL.TRUNCATE_TABLE, h_table);
1909:
1910: h_sql := 'DROP TABLE '||h_table;
1911: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
1912: END IF;
1913:
1914: -- Drop the Vl view
1915: IF BSC_APPS.View_Exists(h_view) THEN
1911: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
1912: END IF;
1913:
1914: -- Drop the Vl view
1915: IF BSC_APPS.View_Exists(h_view) THEN
1916: h_message := BSC_APPS.Replace_Token(h_base_message_v, 'VIEW', h_view);
1917: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1918:
1919: h_sql := 'DROP VIEW '||h_view;
1912: END IF;
1913:
1914: -- Drop the Vl view
1915: IF BSC_APPS.View_Exists(h_view) THEN
1916: h_message := BSC_APPS.Replace_Token(h_base_message_v, 'VIEW', h_view);
1917: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1918:
1919: h_sql := 'DROP VIEW '||h_view;
1920: BSC_APPS.Execute_DDL(h_sql);
1913:
1914: -- Drop the Vl view
1915: IF BSC_APPS.View_Exists(h_view) THEN
1916: h_message := BSC_APPS.Replace_Token(h_base_message_v, 'VIEW', h_view);
1917: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1918:
1919: h_sql := 'DROP VIEW '||h_view;
1920: BSC_APPS.Execute_DDL(h_sql);
1921: END IF;
1916: h_message := BSC_APPS.Replace_Token(h_base_message_v, 'VIEW', h_view);
1917: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1918:
1919: h_sql := 'DROP VIEW '||h_view;
1920: BSC_APPS.Execute_DDL(h_sql);
1921: END IF;
1922:
1923: ELSE
1924: -- EDW dimension
1921: END IF;
1922:
1923: ELSE
1924: -- EDW dimension
1925: IF BSC_APPS.Object_Exists(h_table) THEN
1926: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1927: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1928:
1929: BSC_INTEGRATION_MV_GEN.Drop_Materialized_View(h_table, 'Y');
1922:
1923: ELSE
1924: -- EDW dimension
1925: IF BSC_APPS.Object_Exists(h_table) THEN
1926: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1927: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1928:
1929: BSC_INTEGRATION_MV_GEN.Drop_Materialized_View(h_table, 'Y');
1930: IF BSC_APPS.CheckError('BSC_INTEGRATION_MV_GEN.Drop_Materialized_View') THEN
1923: ELSE
1924: -- EDW dimension
1925: IF BSC_APPS.Object_Exists(h_table) THEN
1926: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1927: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1928:
1929: BSC_INTEGRATION_MV_GEN.Drop_Materialized_View(h_table, 'Y');
1930: IF BSC_APPS.CheckError('BSC_INTEGRATION_MV_GEN.Drop_Materialized_View') THEN
1931: RAISE e_unexpected_error;
1926: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1927: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1928:
1929: BSC_INTEGRATION_MV_GEN.Drop_Materialized_View(h_table, 'Y');
1930: IF BSC_APPS.CheckError('BSC_INTEGRATION_MV_GEN.Drop_Materialized_View') THEN
1931: RAISE e_unexpected_error;
1932: END IF;
1933:
1934: END IF;
1943: -- EDW Note: No mn dimensions are from EDW
1944: OPEN c_mn_dim_tables;
1945: FETCH c_mn_dim_tables INTO h_table;
1946: WHILE c_mn_dim_tables%FOUND LOOP
1947: IF BSC_APPS.Table_Exists(h_table) THEN
1948: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1949: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1950:
1951: -- Fix perf bug#4583017: do not truncate, just drop.
1944: OPEN c_mn_dim_tables;
1945: FETCH c_mn_dim_tables INTO h_table;
1946: WHILE c_mn_dim_tables%FOUND LOOP
1947: IF BSC_APPS.Table_Exists(h_table) THEN
1948: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1949: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1950:
1951: -- Fix perf bug#4583017: do not truncate, just drop.
1952: -- Truncate table to release space --> Bug: DROP TABLE don't release the space immediately
1945: FETCH c_mn_dim_tables INTO h_table;
1946: WHILE c_mn_dim_tables%FOUND LOOP
1947: IF BSC_APPS.Table_Exists(h_table) THEN
1948: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1949: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1950:
1951: -- Fix perf bug#4583017: do not truncate, just drop.
1952: -- Truncate table to release space --> Bug: DROP TABLE don't release the space immediately
1953: --h_sql := 'TRUNCATE TABLE '||h_table;
1950:
1951: -- Fix perf bug#4583017: do not truncate, just drop.
1952: -- Truncate table to release space --> Bug: DROP TABLE don't release the space immediately
1953: --h_sql := 'TRUNCATE TABLE '||h_table;
1954: --BSC_APPS.Do_DDL(h_sql, AD_DDL.TRUNCATE_TABLE, h_table);
1955:
1956: h_sql := 'DROP TABLE '||h_table;
1957: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
1958: END IF;
1953: --h_sql := 'TRUNCATE TABLE '||h_table;
1954: --BSC_APPS.Do_DDL(h_sql, AD_DDL.TRUNCATE_TABLE, h_table);
1955:
1956: h_sql := 'DROP TABLE '||h_table;
1957: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
1958: END IF;
1959:
1960: FETCH c_mn_dim_tables INTO h_table;
1961: END LOOP;
1961: END LOOP;
1962: CLOSE c_mn_dim_tables;
1963:
1964: -- Drop filter views
1965: h_base_message := BSC_APPS.Get_Message('BSC_DROPPING_VIEW');
1966:
1967: OPEN c_filter_views;
1968: FETCH c_filter_views INTO h_view;
1969: WHILE c_filter_views%FOUND LOOP
1966:
1967: OPEN c_filter_views;
1968: FETCH c_filter_views INTO h_view;
1969: WHILE c_filter_views%FOUND LOOP
1970: IF BSC_APPS.View_Exists(h_view) THEN
1971: h_message := BSC_APPS.Replace_Token(h_base_message, 'VIEW', h_view);
1972: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1973:
1974: h_sql := 'DROP VIEW '||h_view;
1967: OPEN c_filter_views;
1968: FETCH c_filter_views INTO h_view;
1969: WHILE c_filter_views%FOUND LOOP
1970: IF BSC_APPS.View_Exists(h_view) THEN
1971: h_message := BSC_APPS.Replace_Token(h_base_message, 'VIEW', h_view);
1972: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1973:
1974: h_sql := 'DROP VIEW '||h_view;
1975: BSC_APPS.Execute_DDL(h_sql);
1968: FETCH c_filter_views INTO h_view;
1969: WHILE c_filter_views%FOUND LOOP
1970: IF BSC_APPS.View_Exists(h_view) THEN
1971: h_message := BSC_APPS.Replace_Token(h_base_message, 'VIEW', h_view);
1972: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1973:
1974: h_sql := 'DROP VIEW '||h_view;
1975: BSC_APPS.Execute_DDL(h_sql);
1976: END IF;
1971: h_message := BSC_APPS.Replace_Token(h_base_message, 'VIEW', h_view);
1972: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1973:
1974: h_sql := 'DROP VIEW '||h_view;
1975: BSC_APPS.Execute_DDL(h_sql);
1976: END IF;
1977:
1978: FETCH c_filter_views INTO h_view;
1979: END LOOP;
1979: END LOOP;
1980: CLOSE c_filter_views;
1981:
1982: -- Drop data tables (input, base and system tables)
1983: h_base_message := BSC_APPS.Get_Message('BSC_DROPPING_TABLE');
1984:
1985: OPEN c_data_tables;
1986: FETCH c_data_tables INTO h_table;
1987: WHILE c_data_tables%FOUND LOOP
1984:
1985: OPEN c_data_tables;
1986: FETCH c_data_tables INTO h_table;
1987: WHILE c_data_tables%FOUND LOOP
1988: IF BSC_APPS.Table_Exists(h_table) THEN
1989: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1990: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1991:
1992: -- Fix perf bug#4583017: do not truncate, just drop.
1985: OPEN c_data_tables;
1986: FETCH c_data_tables INTO h_table;
1987: WHILE c_data_tables%FOUND LOOP
1988: IF BSC_APPS.Table_Exists(h_table) THEN
1989: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1990: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1991:
1992: -- Fix perf bug#4583017: do not truncate, just drop.
1993: -- Truncate table to release space --> Bug: DROP TABLE don't release the space immediately
1986: FETCH c_data_tables INTO h_table;
1987: WHILE c_data_tables%FOUND LOOP
1988: IF BSC_APPS.Table_Exists(h_table) THEN
1989: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
1990: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
1991:
1992: -- Fix perf bug#4583017: do not truncate, just drop.
1993: -- Truncate table to release space --> Bug: DROP TABLE don't release the space immediately
1994: --h_sql := 'TRUNCATE TABLE '||h_table;
1991:
1992: -- Fix perf bug#4583017: do not truncate, just drop.
1993: -- Truncate table to release space --> Bug: DROP TABLE don't release the space immediately
1994: --h_sql := 'TRUNCATE TABLE '||h_table;
1995: --BSC_APPS.Do_DDL(h_sql, AD_DDL.TRUNCATE_TABLE, h_table);
1996:
1997: h_sql := 'DROP TABLE '||h_table;
1998: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
1999: END IF;
1994: --h_sql := 'TRUNCATE TABLE '||h_table;
1995: --BSC_APPS.Do_DDL(h_sql, AD_DDL.TRUNCATE_TABLE, h_table);
1996:
1997: h_sql := 'DROP TABLE '||h_table;
1998: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
1999: END IF;
2000:
2001: -- BSC-MV Note: I am commenting this code. EDW is not supported and
2002: -- this code affect the performance.
2003: -- -- EDW note: I dont know at this point, whether the table is a S, B or I table
2004: -- -- neither if the table is from EDW. So I check if there is a materialized
2005: -- -- view corresponding to this table. In that case I drop the materialize view.
2006: -- h_mv := h_table||'_MV_V';
2007: -- IF BSC_APPS.Object_Exists(h_mv) THEN
2008: -- BSC_INTEGRATION_MV_GEN.Drop_Materialized_View(h_table, 'N');
2009: -- IF BSC_APPS.CheckError('BSC_INTEGRATION_MV_GEN.Drop_Materialized_View') THEN
2010: -- RAISE e_unexpected_error;
2011: -- END IF;
2005: -- -- view corresponding to this table. In that case I drop the materialize view.
2006: -- h_mv := h_table||'_MV_V';
2007: -- IF BSC_APPS.Object_Exists(h_mv) THEN
2008: -- BSC_INTEGRATION_MV_GEN.Drop_Materialized_View(h_table, 'N');
2009: -- IF BSC_APPS.CheckError('BSC_INTEGRATION_MV_GEN.Drop_Materialized_View') THEN
2010: -- RAISE e_unexpected_error;
2011: -- END IF;
2012: -- END IF;
2013: --
2012: -- END IF;
2013: --
2014: -- -- Same for the union view.
2015: -- h_uv := h_table||'_V';
2016: -- IF BSC_APPS.View_Exists(h_uv) THEN
2017: -- h_sql := 'DROP VIEW '||h_uv;
2018: -- BSC_APPS.Execute_DDL(h_sql);
2019: -- END IF;
2020:
2014: -- -- Same for the union view.
2015: -- h_uv := h_table||'_V';
2016: -- IF BSC_APPS.View_Exists(h_uv) THEN
2017: -- h_sql := 'DROP VIEW '||h_uv;
2018: -- BSC_APPS.Execute_DDL(h_sql);
2019: -- END IF;
2020:
2021: FETCH c_data_tables INTO h_table;
2022: END LOOP;
2026: OPEN c_rowid_tables;
2027: FETCH c_rowid_tables INTO h_table;
2028: WHILE c_rowid_tables%FOUND LOOP
2029: IF h_table IS NOT NULL THEN
2030: IF BSC_APPS.Table_Exists(h_table) THEN
2031: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
2032: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
2033:
2034: h_sql := 'DROP TABLE '||h_table;
2027: FETCH c_rowid_tables INTO h_table;
2028: WHILE c_rowid_tables%FOUND LOOP
2029: IF h_table IS NOT NULL THEN
2030: IF BSC_APPS.Table_Exists(h_table) THEN
2031: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
2032: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
2033:
2034: h_sql := 'DROP TABLE '||h_table;
2035: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
2028: WHILE c_rowid_tables%FOUND LOOP
2029: IF h_table IS NOT NULL THEN
2030: IF BSC_APPS.Table_Exists(h_table) THEN
2031: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
2032: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
2033:
2034: h_sql := 'DROP TABLE '||h_table;
2035: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
2036: END IF;
2031: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
2032: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
2033:
2034: h_sql := 'DROP TABLE '||h_table;
2035: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
2036: END IF;
2037: END IF;
2038: FETCH c_rowid_tables INTO h_table;
2039: END LOOP;
2043: OPEN c_proj_tables;
2044: FETCH c_proj_tables INTO h_table;
2045: WHILE c_proj_tables%FOUND LOOP
2046: IF h_table IS NOT NULL THEN
2047: IF BSC_APPS.Table_Exists(h_table) THEN
2048: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
2049: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
2050:
2051: h_sql := 'DROP TABLE '||h_table;
2044: FETCH c_proj_tables INTO h_table;
2045: WHILE c_proj_tables%FOUND LOOP
2046: IF h_table IS NOT NULL THEN
2047: IF BSC_APPS.Table_Exists(h_table) THEN
2048: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
2049: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
2050:
2051: h_sql := 'DROP TABLE '||h_table;
2052: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
2045: WHILE c_proj_tables%FOUND LOOP
2046: IF h_table IS NOT NULL THEN
2047: IF BSC_APPS.Table_Exists(h_table) THEN
2048: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
2049: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
2050:
2051: h_sql := 'DROP TABLE '||h_table;
2052: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
2053: END IF;
2048: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
2049: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
2050:
2051: h_sql := 'DROP TABLE '||h_table;
2052: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
2053: END IF;
2054: END IF;
2055: FETCH c_proj_tables INTO h_table;
2056: END LOOP;
2056: END LOOP;
2057: CLOSE c_proj_tables;
2058:
2059: -- BSC-MV Note: Drop all MVs used by the Kpis
2060: h_base_message := BSC_APPS.Get_Message('BSC_DROPPING_VIEW');
2061:
2062: h_sql := 'SELECT DISTINCT MV_NAME'||
2063: ' FROM BSC_KPI_DATA_TABLES'||
2064: ' WHERE MV_NAME IS NOT NULL';
2066: LOOP
2067: FETCH h_cursor INTO h_mv_name;
2068: EXIT WHEN h_cursor%NOTFOUND;
2069:
2070: h_message := BSC_APPS.Replace_Token(h_base_message, 'VIEW', h_mv_name);
2071: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
2072:
2073: IF NOT BSC_BIA_WRAPPER.Drop_Summary_MV(h_mv_name, h_error_message_drop_mv) THEN
2074: RAISE e_error_drop_mv;
2067: FETCH h_cursor INTO h_mv_name;
2068: EXIT WHEN h_cursor%NOTFOUND;
2069:
2070: h_message := BSC_APPS.Replace_Token(h_base_message, 'VIEW', h_mv_name);
2071: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
2072:
2073: IF NOT BSC_BIA_WRAPPER.Drop_Summary_MV(h_mv_name, h_error_message_drop_mv) THEN
2074: RAISE e_error_drop_mv;
2075: END IF;
2089: LOOP
2090: FETCH h_cursor INTO h_mv_name;
2091: EXIT WHEN h_cursor%NOTFOUND;
2092:
2093: h_message := BSC_APPS.Replace_Token(h_base_message, 'VIEW', h_mv_name);
2094: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
2095:
2096: IF NOT BSC_BIA_WRAPPER.Drop_Summary_MV(h_mv_name, h_error_message_drop_mv) THEN
2097: RAISE e_error_drop_mv;
2090: FETCH h_cursor INTO h_mv_name;
2091: EXIT WHEN h_cursor%NOTFOUND;
2092:
2093: h_message := BSC_APPS.Replace_Token(h_base_message, 'VIEW', h_mv_name);
2094: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
2095:
2096: IF NOT BSC_BIA_WRAPPER.Drop_Summary_MV(h_mv_name, h_error_message_drop_mv) THEN
2097: RAISE e_error_drop_mv;
2098: END IF;
2100: END LOOP;
2101: CLOSE h_cursor;
2102:
2103: -- BSC-MV Note: Also drop all the projection tables
2104: h_base_message := BSC_APPS.Get_Message('BSC_DROPPING_TABLE');
2105:
2106: h_sql := 'SELECT DISTINCT PROJECTION_DATA'||
2107: ' FROM BSC_KPI_DATA_TABLES'||
2108: ' WHERE PROJECTION_DATA IS NOT NULL';
2110: LOOP
2111: FETCH h_cursor INTO h_table;
2112: EXIT WHEN h_cursor%NOTFOUND;
2113:
2114: IF BSC_APPS.Table_Exists(h_table) THEN
2115: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
2116: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
2117:
2118: -- Fix perf bug#4583017: do not truncate, just drop.
2111: FETCH h_cursor INTO h_table;
2112: EXIT WHEN h_cursor%NOTFOUND;
2113:
2114: IF BSC_APPS.Table_Exists(h_table) THEN
2115: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
2116: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
2117:
2118: -- Fix perf bug#4583017: do not truncate, just drop.
2119: -- Truncate table to release space --> Bug: DROP TABLE don't release the space immediately
2112: EXIT WHEN h_cursor%NOTFOUND;
2113:
2114: IF BSC_APPS.Table_Exists(h_table) THEN
2115: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
2116: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
2117:
2118: -- Fix perf bug#4583017: do not truncate, just drop.
2119: -- Truncate table to release space --> Bug: DROP TABLE don't release the space immediately
2120: --h_sql := 'TRUNCATE TABLE '||h_table;
2117:
2118: -- Fix perf bug#4583017: do not truncate, just drop.
2119: -- Truncate table to release space --> Bug: DROP TABLE don't release the space immediately
2120: --h_sql := 'TRUNCATE TABLE '||h_table;
2121: --BSC_APPS.Do_DDL(h_sql, AD_DDL.TRUNCATE_TABLE, h_table);
2122:
2123: h_sql := 'DROP TABLE '||h_table;
2124: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
2125: END IF;
2120: --h_sql := 'TRUNCATE TABLE '||h_table;
2121: --BSC_APPS.Do_DDL(h_sql, AD_DDL.TRUNCATE_TABLE, h_table);
2122:
2123: h_sql := 'DROP TABLE '||h_table;
2124: BSC_APPS.Do_DDL(h_sql, AD_DDL.DROP_TABLE, h_table);
2125: END IF;
2126: END LOOP;
2127: CLOSE h_cursor;
2128:
2129: RETURN TRUE;
2130:
2131: EXCEPTION
2132: WHEN e_unexpected_error THEN
2133: BSC_MESSAGE.Add (x_message => BSC_APPS.Get_Message('BSC_ERROR_DROPPING_OLD_DB_OBJ'),
2134: x_source => 'BSC_MIGRATION.Drop_Dynamic_Objects');
2135: RETURN FALSE;
2136:
2137: WHEN e_error_drop_mv THEN
2186:
2187: BEGIN
2188:
2189: --Lock the entire target system
2190: BSC_APPS.Write_Line_Log('Locking Target System:', BSC_APPS.OUTPUT_FILE);
2191: BSC_APPS.Write_Line_Log('Locking the whole system', BSC_APPS.OUTPUT_FILE);
2192: BSC_LOCKS_PUB.Get_System_Lock (
2193: p_program_id => -800,
2194: p_user_id => BSC_APPS.apps_user_id,
2187: BEGIN
2188:
2189: --Lock the entire target system
2190: BSC_APPS.Write_Line_Log('Locking Target System:', BSC_APPS.OUTPUT_FILE);
2191: BSC_APPS.Write_Line_Log('Locking the whole system', BSC_APPS.OUTPUT_FILE);
2192: BSC_LOCKS_PUB.Get_System_Lock (
2193: p_program_id => -800,
2194: p_user_id => BSC_APPS.apps_user_id,
2195: x_return_status => h_return_status,
2190: BSC_APPS.Write_Line_Log('Locking Target System:', BSC_APPS.OUTPUT_FILE);
2191: BSC_APPS.Write_Line_Log('Locking the whole system', BSC_APPS.OUTPUT_FILE);
2192: BSC_LOCKS_PUB.Get_System_Lock (
2193: p_program_id => -800,
2194: p_user_id => BSC_APPS.apps_user_id,
2195: x_return_status => h_return_status,
2196: x_msg_count => h_msg_count,
2197: x_msg_data => h_msg_data
2198: );
2201: END IF;
2202:
2203: --In the source, lock the indicators or scorecards or the whole system according to the filters
2204: -- Get parameters from BSC_DB_LOADER_CONTROL
2205: BSC_APPS.Write_Line_Log('Locking Source System:', BSC_APPS.OUTPUT_FILE);
2206: Get_Migration_Parameters(
2207: p_process_id => x_process_id,
2208: x_src_responsibilities => h_src_responsibilities,
2209: x_trg_responsibilities => h_trg_responsibilities,
2219:
2220: h_sql := 'BEGIN FND_MSG_PUB.Initialize@'||g_db_link||'; END;';
2221: execute immediate h_sql;
2222:
2223: h_sql := 'BEGIN BSC_APPS.Init_Bsc_Apps@'||g_db_link||'; END;';
2224: execute immediate h_sql;
2225:
2226: h_lock_type := 'W';
2227: h_query_time := SYSDATE;
2234: h_object_type := 'SCORECARD';
2235: FOR h_i IN 1..h_num_tabs_filter LOOP
2236: h_object_key := h_tabs_filter(h_i);
2237:
2238: BSC_APPS.Write_Line_Log('Locking SCORECARD '||h_object_key, BSC_APPS.OUTPUT_FILE);
2239:
2240: h_sql := 'BEGIN BSC_LOCKS_PUB.GET_SYSTEM_LOCK@'||g_db_link||' ('||
2241: ' p_object_key => :1'||
2242: ',p_object_type => :2'||
2261: h_object_type := 'OBJECTIVE';
2262: FOR h_i IN 1..h_num_kpis_filter LOOP
2263: h_object_key := h_kpis_filter(h_i);
2264:
2265: BSC_APPS.Write_Line_Log('Locking OBJECTIVE '||h_object_key, BSC_APPS.OUTPUT_FILE);
2266:
2267: h_sql := 'BEGIN BSC_LOCKS_PUB.GET_SYSTEM_LOCK@'||g_db_link||' ('||
2268: ' p_object_key => :1'||
2269: ',p_object_type => :2'||
2286:
2287: --Lock the entire source system if there is no filters
2288: IF h_num_kpis_filter = 0 AND h_num_tabs_filter = 0 THEN
2289:
2290: BSC_APPS.Write_Line_Log('Locking the whole system', BSC_APPS.OUTPUT_FILE);
2291:
2292: h_sql := 'BEGIN BSC_LOCKS_PUB.GET_SYSTEM_LOCK@'||g_db_link||' ('||
2293: 'p_program_id => :1'||
2294: ',p_user_id => :2'||
2349: h_source_user_id NUMBER;
2350: h_error_msg VARCHAR2(4000);
2351:
2352: BEGIN
2353: -- Initialize BSC/APPS global variables
2354: BSC_APPS.Init_Bsc_Apps;
2355:
2356: -- LOCKING: call this api
2357: FND_MSG_PUB.Initialize;
2350: h_error_msg VARCHAR2(4000);
2351:
2352: BEGIN
2353: -- Initialize BSC/APPS global variables
2354: BSC_APPS.Init_Bsc_Apps;
2355:
2356: -- LOCKING: call this api
2357: FND_MSG_PUB.Initialize;
2358:
2359: -- Initializes the error message stack
2360: BSC_MESSAGE.Init(g_debug_flag);
2361:
2362: -- Initialize the temporal table used for big 'in' conditions
2363: BSC_APPS.Init_Big_In_Cond_Table;
2364:
2365: -- Alter session set global_names = false to disable the enforcement
2366: -- of database link name must be equals to remote database name.
2367: h_sql := 'ALTER SESSION SET GLOBAL_NAMES = FALSE';
2364:
2365: -- Alter session set global_names = false to disable the enforcement
2366: -- of database link name must be equals to remote database name.
2367: h_sql := 'ALTER SESSION SET GLOBAL_NAMES = FALSE';
2368: BSC_APPS.Execute_Immediate(h_sql);
2369:
2370: Get_Migration_Parameters(
2371: p_process_id => x_src_responsibilities,
2372: x_src_responsibilities => h_src_responsibilities,
2380:
2381: --LOCKING: Call this api for backward compatibility
2382: BSC_LOCKS_PUB.Get_System_Lock (
2383: p_program_id => -800,
2384: p_user_id => BSC_APPS.apps_user_id,
2385: p_icx_session_id => null,
2386: x_return_status => h_return_status,
2387: x_msg_count => h_msg_count,
2388: x_msg_data => h_msg_data
2390: IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2391: RAISE e_system_lock;
2392: END IF;
2393:
2394: --Fix bug#4873385 if the user BSC_APPS.apps_user_id does not exists in the source system
2395: -- then we lock the source system with the user ANONYMOUS
2396: h_source_user_id := Get_Source_User_Id;
2397:
2398: -- Fix bug#4480209 LOCKING: Need to call this api for backward compatibility
2397:
2398: -- Fix bug#4480209 LOCKING: Need to call this api for backward compatibility
2399: h_sql := 'BEGIN FND_MSG_PUB.Initialize@'||g_db_link||'; END;';
2400: execute immediate h_sql;
2401: h_sql := 'BEGIN BSC_APPS.Init_Bsc_Apps@'||g_db_link||'; END;';
2402: execute immediate h_sql;
2403: h_null := NULL;
2404: h_sql := 'BEGIN BSC_LOCKS_PUB.Get_System_Lock@'||g_db_link||' ('||
2405: 'p_program_id => :1,'||
2461: execute immediate h_sql;
2462:
2463: EXCEPTION
2464: WHEN e_error THEN
2465: ERRBUF := BSC_APPS.Get_Message('BSC_MIG_FAIL_EXEC');
2466: RETCODE := 2; -- Request completed with errors
2467: -- LOCKING
2468: BSC_LOCKS_PUB.Remove_System_Lock;
2469: -- Fix bug#4624462: need to call this in the source system too
2496: x_source => 'BSC_MIGRATION.Migrate_System',
2497: x_mode => 'I');
2498: COMMIT;
2499:
2500: BSC_APPS.Write_Errors_To_Log;
2501:
2502: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
2503: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
2504: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
2498: COMMIT;
2499:
2500: BSC_APPS.Write_Errors_To_Log;
2501:
2502: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
2503: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
2504: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
2505: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
2506:
2499:
2500: BSC_APPS.Write_Errors_To_Log;
2501:
2502: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
2503: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
2504: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
2505: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
2506:
2507: ERRBUF := BSC_APPS.Get_Message('BSC_MIG_FAIL_EXEC');
2500: BSC_APPS.Write_Errors_To_Log;
2501:
2502: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
2503: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
2504: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
2505: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
2506:
2507: ERRBUF := BSC_APPS.Get_Message('BSC_MIG_FAIL_EXEC');
2508: RETCODE := 2; -- Request completed with errors
2501:
2502: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
2503: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
2504: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
2505: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
2506:
2507: ERRBUF := BSC_APPS.Get_Message('BSC_MIG_FAIL_EXEC');
2508: RETCODE := 2; -- Request completed with errors
2509: -- LOCKING
2503: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
2504: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
2505: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
2506:
2507: ERRBUF := BSC_APPS.Get_Message('BSC_MIG_FAIL_EXEC');
2508: RETCODE := 2; -- Request completed with errors
2509: -- LOCKING
2510: BSC_LOCKS_PUB.Remove_System_Lock;
2511: h_sql := 'BEGIN BSC_LOCKS_PUB.Remove_System_Lock@'||g_db_link||'; END;';
2516: x_source => 'BSC_MIGRATION.Execute_Migration',
2517: x_mode => 'I');
2518: COMMIT;
2519:
2520: BSC_APPS.Write_Errors_To_Log;
2521:
2522: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
2523: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
2524: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
2518: COMMIT;
2519:
2520: BSC_APPS.Write_Errors_To_Log;
2521:
2522: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
2523: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
2524: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
2525: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
2526:
2519:
2520: BSC_APPS.Write_Errors_To_Log;
2521:
2522: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
2523: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
2524: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
2525: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
2526:
2527:
2520: BSC_APPS.Write_Errors_To_Log;
2521:
2522: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
2523: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
2524: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
2525: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
2526:
2527:
2528: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
2521:
2522: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
2523: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
2524: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
2525: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
2526:
2527:
2528: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UPDATE_PROC_FAILED');
2529: RETCODE := 2; -- Request completed with errors
2555: CURSOR c_columns_apps IS
2556: SELECT column_name
2557: FROM all_tab_columns
2558: WHERE table_name = UPPER(x_table) AND
2559: owner = UPPER(BSC_APPS.BSC_APPS_SCHEMA);
2560:
2561: BEGIN
2562: h_lst := NULL;
2563: IF NOT BSC_APPS.APPS_ENV THEN
2559: owner = UPPER(BSC_APPS.BSC_APPS_SCHEMA);
2560:
2561: BEGIN
2562: h_lst := NULL;
2563: IF NOT BSC_APPS.APPS_ENV THEN
2564: -- Personal mode
2565: OPEN c_columns;
2566: FETCH c_columns INTO h_column_name;
2567: WHILE c_columns%FOUND LOOP
4010: -- BSC_USER_PARAMETERS_B
4011: -- BSC_USER_PARAMETERS_TL
4012:
4013: -- Security Level tables
4014: -- BSC_APPS_USERS
4015: -- BSC_RESPONSIBILITY
4016: -- BSC_RESPONSIBILITY_TL
4017: -- BSC_USERS
4018: -- BSC_USER_INFO
4065: -- We cannot migrate those indicators.
4066: Init_Invalid_PMF_Objects;
4067: -- ------------------------------------------------------------------------------------
4068:
4069: h_resp_cond := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'r.responsibility_id');
4070: FOR h_i IN 1 .. g_num_src_resps LOOP
4071: BSC_APPS.Add_Value_Big_In_Cond(1, g_src_resps(h_i));
4072: END LOOP;
4073:
4067: -- ------------------------------------------------------------------------------------
4068:
4069: h_resp_cond := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'r.responsibility_id');
4070: FOR h_i IN 1 .. g_num_src_resps LOOP
4071: BSC_APPS.Add_Value_Big_In_Cond(1, g_src_resps(h_i));
4072: END LOOP;
4073:
4074: h_tab_cond := BSC_APPS.Get_New_Big_In_Cond_Number(2, 'rt.tab_id');
4075: FOR h_i IN 1 .. g_num_tabs_filter LOOP
4070: FOR h_i IN 1 .. g_num_src_resps LOOP
4071: BSC_APPS.Add_Value_Big_In_Cond(1, g_src_resps(h_i));
4072: END LOOP;
4073:
4074: h_tab_cond := BSC_APPS.Get_New_Big_In_Cond_Number(2, 'rt.tab_id');
4075: FOR h_i IN 1 .. g_num_tabs_filter LOOP
4076: BSC_APPS.Add_Value_Big_In_Cond(2, g_tabs_filter(h_i));
4077: END LOOP;
4078:
4072: END LOOP;
4073:
4074: h_tab_cond := BSC_APPS.Get_New_Big_In_Cond_Number(2, 'rt.tab_id');
4075: FOR h_i IN 1 .. g_num_tabs_filter LOOP
4076: BSC_APPS.Add_Value_Big_In_Cond(2, g_tabs_filter(h_i));
4077: END LOOP;
4078:
4079: h_kpi_cond := BSC_APPS.Get_New_Big_In_Cond_Number(3, 'rk.indicator');
4080: FOR h_i IN 1 .. g_num_kpis_filter LOOP
4075: FOR h_i IN 1 .. g_num_tabs_filter LOOP
4076: BSC_APPS.Add_Value_Big_In_Cond(2, g_tabs_filter(h_i));
4077: END LOOP;
4078:
4079: h_kpi_cond := BSC_APPS.Get_New_Big_In_Cond_Number(3, 'rk.indicator');
4080: FOR h_i IN 1 .. g_num_kpis_filter LOOP
4081: BSC_APPS.Add_Value_Big_In_Cond(3, g_kpis_filter(h_i));
4082: END LOOP;
4083:
4077: END LOOP;
4078:
4079: h_kpi_cond := BSC_APPS.Get_New_Big_In_Cond_Number(3, 'rk.indicator');
4080: FOR h_i IN 1 .. g_num_kpis_filter LOOP
4081: BSC_APPS.Add_Value_Big_In_Cond(3, g_kpis_filter(h_i));
4082: END LOOP;
4083:
4084: h_filter_condition := '('||h_resp_cond||')';
4085: IF g_num_tabs_filter > 0 THEN
4162: END IF;
4163:
4164: -- Initialize the array of tables that are used by those KPIs
4165: IF g_num_mig_kpis > 0 THEN
4166: h_kpi_cond := BSC_APPS.Get_New_Big_In_Cond_Number(4, 'indicator');
4167: FOR h_i IN 1 .. g_num_mig_kpis LOOP
4168: BSC_APPS.Add_Value_Big_In_Cond(4, g_mig_kpis(h_i));
4169: END LOOP;
4170:
4164: -- Initialize the array of tables that are used by those KPIs
4165: IF g_num_mig_kpis > 0 THEN
4166: h_kpi_cond := BSC_APPS.Get_New_Big_In_Cond_Number(4, 'indicator');
4167: FOR h_i IN 1 .. g_num_mig_kpis LOOP
4168: BSC_APPS.Add_Value_Big_In_Cond(4, g_mig_kpis(h_i));
4169: END LOOP;
4170:
4171: h_sql := 'SELECT DISTINCT table_name'||
4172: ' FROM bsc_kpi_data_tables_v@'||g_db_link||
4287:
4288: BEGIN
4289: IF x_num_tables > 0 THEN
4290:
4291: h_table_cond := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'table_name');
4292: FOR h_i IN 1 .. x_num_tables LOOP
4293: BSC_APPS.Add_Value_Big_In_Cond(1, x_tables(h_i));
4294: END LOOP;
4295:
4289: IF x_num_tables > 0 THEN
4290:
4291: h_table_cond := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'table_name');
4292: FOR h_i IN 1 .. x_num_tables LOOP
4293: BSC_APPS.Add_Value_Big_In_Cond(1, x_tables(h_i));
4294: END LOOP;
4295:
4296: h_sql := 'SELECT DISTINCT source_table_name'||
4297: ' FROM bsc_db_tables_rels@'||g_db_link||
4442: h_proj_table_name VARCHAR2(30);
4443:
4444: BEGIN
4445:
4446: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_TABLES_DATA'), BSC_APPS.OUTPUT_FILE);
4447:
4448: h_base_message := BSC_APPS.Get_Message('BSC_MIG_DATA');
4449:
4450: -- Dimension tables
4444: BEGIN
4445:
4446: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_TABLES_DATA'), BSC_APPS.OUTPUT_FILE);
4447:
4448: h_base_message := BSC_APPS.Get_Message('BSC_MIG_DATA');
4449:
4450: -- Dimension tables
4451: h_sql := 'SELECT level_table_name'||
4452: ' FROM bsc_sys_dim_levels_b'||
4455: LOOP
4456: FETCH h_cursor INTO h_table;
4457: EXIT WHEN h_cursor%NOTFOUND;
4458:
4459: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
4460: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
4461:
4462: -- Fix bug#4430901 add append hint
4463: -- Fix performance bug#3860149: use parallel hint
4456: FETCH h_cursor INTO h_table;
4457: EXIT WHEN h_cursor%NOTFOUND;
4458:
4459: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
4460: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
4461:
4462: -- Fix bug#4430901 add append hint
4463: -- Fix performance bug#3860149: use parallel hint
4464: h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
4462: -- Fix bug#4430901 add append hint
4463: -- Fix performance bug#3860149: use parallel hint
4464: h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
4465: ' SELECT /*+ parallel('||h_table||') */ * FROM '||h_table||'@'||g_db_link;
4466: BSC_APPS.Execute_Immediate(h_sql);
4467:
4468: COMMIT;
4469: END LOOP;
4470: CLOSE h_cursor;
4479: LOOP
4480: FETCH h_cursor INTO h_table;
4481: EXIT WHEN h_cursor%NOTFOUND;
4482:
4483: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
4484: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
4485:
4486: -- Fix bug#4430901 add append hint
4487: -- Fix performance bug#3860149: use parallel hint
4480: FETCH h_cursor INTO h_table;
4481: EXIT WHEN h_cursor%NOTFOUND;
4482:
4483: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
4484: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
4485:
4486: -- Fix bug#4430901 add append hint
4487: -- Fix performance bug#3860149: use parallel hint
4488: h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
4486: -- Fix bug#4430901 add append hint
4487: -- Fix performance bug#3860149: use parallel hint
4488: h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
4489: ' SELECT /* parallel('||h_table||') */ * FROM '||h_table||'@'||g_db_link;
4490: BSC_APPS.Execute_Immediate(h_sql);
4491:
4492: COMMIT;
4493: END LOOP;
4494: CLOSE h_cursor;
4512: RAISE e_unexpected_error;
4513: END IF;
4514: ELSE
4515: -- This is a normal B table
4516: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
4517: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
4518:
4519: -- Fix bug#4430901 add append hint
4520: -- Fix performance bug#3860149: use parallel hint
4513: END IF;
4514: ELSE
4515: -- This is a normal B table
4516: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
4517: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
4518:
4519: -- Fix bug#4430901 add append hint
4520: -- Fix performance bug#3860149: use parallel hint
4521: h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
4519: -- Fix bug#4430901 add append hint
4520: -- Fix performance bug#3860149: use parallel hint
4521: h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
4522: ' SELECT /*+ parallel('||h_table||') */ * FROM '||h_table||'@'||g_db_link;
4523: BSC_APPS.Execute_Immediate(h_sql);
4524: commit;
4525: END IF;
4526: ELSE
4527: -- This is not a base table
4524: commit;
4525: END IF;
4526: ELSE
4527: -- This is not a base table
4528: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
4529: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
4530:
4531: -- Fix bug#4430901 add append hint
4532: -- Fix performance bug#3860149: use parallel hint
4525: END IF;
4526: ELSE
4527: -- This is not a base table
4528: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
4529: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
4530:
4531: -- Fix bug#4430901 add append hint
4532: -- Fix performance bug#3860149: use parallel hint
4533: h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
4531: -- Fix bug#4430901 add append hint
4532: -- Fix performance bug#3860149: use parallel hint
4533: h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
4534: ' SELECT /*+ parallel('||h_table||') */ * FROM '||h_table||'@'||g_db_link;
4535: BSC_APPS.Execute_Immediate(h_sql);
4536: commit;
4537: END IF;
4538: ELSE
4539: -- BSC-MV architecture. Generation type = -1 means the table does not exists
4549: RAISE e_unexpected_error;
4550: END IF;
4551: ELSE
4552: -- This is a normal B table
4553: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
4554: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
4555:
4556: -- Fix bug#4430901 add append hint
4557: -- Fix performance bug#3860149: use parallel hint
4550: END IF;
4551: ELSE
4552: -- This is a normal B table
4553: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
4554: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
4555:
4556: -- Fix bug#4430901 add append hint
4557: -- Fix performance bug#3860149: use parallel hint
4558: h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
4556: -- Fix bug#4430901 add append hint
4557: -- Fix performance bug#3860149: use parallel hint
4558: h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
4559: ' SELECT /*+ parallel('||h_table||') */ * FROM '||h_table||'@'||g_db_link;
4560: BSC_APPS.Execute_Immediate(h_sql);
4561: commit;
4562: END IF;
4563: ELSE
4564: -- This is not a base table
4561: commit;
4562: END IF;
4563: ELSE
4564: -- This is not a base table
4565: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
4566: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
4567:
4568: -- Fix bug#4430901 add append hint
4569: -- Fix performance bug#3860149: use parallel hint
4562: END IF;
4563: ELSE
4564: -- This is not a base table
4565: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
4566: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
4567:
4568: -- Fix bug#4430901 add append hint
4569: -- Fix performance bug#3860149: use parallel hint
4570: h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
4568: -- Fix bug#4430901 add append hint
4569: -- Fix performance bug#3860149: use parallel hint
4570: h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
4571: ' SELECT /*+ parallel('||h_table||') */ * FROM '||h_table||'@'||g_db_link;
4572: BSC_APPS.Execute_Immediate(h_sql);
4573: commit;
4574: END IF;
4575: END IF;
4576: END IF;
4585: LOOP
4586: FETCH h_cursor INTO h_table;
4587: EXIT WHEN h_cursor%NOTFOUND;
4588:
4589: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
4590: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
4591:
4592: IF Exist_Table_In_Src(h_table) THEN
4593: -- Fix bug#4430901 add append hint
4586: FETCH h_cursor INTO h_table;
4587: EXIT WHEN h_cursor%NOTFOUND;
4588:
4589: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
4590: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
4591:
4592: IF Exist_Table_In_Src(h_table) THEN
4593: -- Fix bug#4430901 add append hint
4594: -- Fix performance bug#3860149: use parallel hint
4593: -- Fix bug#4430901 add append hint
4594: -- Fix performance bug#3860149: use parallel hint
4595: h_sql := 'INSERT /*+ append parallel('||h_table||') */ INTO '||h_table||
4596: ' SELECT /*+ parallel('||h_table||') */ * FROM '||h_table||'@'||g_db_link;
4597: BSC_APPS.Execute_Immediate(h_sql);
4598: commit;
4599: END IF;
4600: END LOOP;
4601: CLOSE h_cursor;
4604: RETURN TRUE;
4605:
4606: EXCEPTION
4607: WHEN e_unexpected_error THEN
4608: BSC_MESSAGE.Add (x_message => BSC_APPS.Get_Message('BSC_UNEXPECTED_ERROR'),
4609: x_source => 'BSC_MIGRATION.Migrate_Dynamic_Tables_Data');
4610: RETURN FALSE;
4611:
4612: WHEN OTHERS THEN
4650: h_interval NUMBER;
4651:
4652: BEGIN
4653:
4654: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_METADATA'), BSC_APPS.OUTPUT_FILE);
4655:
4656: h_base_message := BSC_APPS.Get_Message('BSC_MIG_DATA');
4657:
4658: FOR h_i IN 1..g_num_metadata_tables LOOP
4652: BEGIN
4653:
4654: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_METADATA'), BSC_APPS.OUTPUT_FILE);
4655:
4656: h_base_message := BSC_APPS.Get_Message('BSC_MIG_DATA');
4657:
4658: FOR h_i IN 1..g_num_metadata_tables LOOP
4659: IF g_metadata_tables(h_i).copy_flag THEN
4660: h_table_name := g_metadata_tables(h_i).table_name;
4661: h_level := g_metadata_tables(h_i).level;
4662: h_level_column := g_metadata_tables(h_i).level_column;
4663: h_level_condition := g_metadata_tables(h_i).level_condition;
4664:
4665: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table_name);
4666: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
4667:
4668: h_mig_condition := NULL;
4669:
4662: h_level_column := g_metadata_tables(h_i).level_column;
4663: h_level_condition := g_metadata_tables(h_i).level_condition;
4664:
4665: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table_name);
4666: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
4667:
4668: h_mig_condition := NULL;
4669:
4670: IF h_level = 0 THEN
4672: h_mig_condition := NULL;
4673:
4674: ELSIF h_level = 1 THEN
4675: -- Tab level
4676: h_mig_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, h_level_column);
4677: FOR h_j IN 1 .. g_num_mig_tabs LOOP
4678: BSC_APPS.Add_Value_Big_In_Cond(1, g_mig_tabs(h_j));
4679: END LOOP;
4680:
4674: ELSIF h_level = 1 THEN
4675: -- Tab level
4676: h_mig_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, h_level_column);
4677: FOR h_j IN 1 .. g_num_mig_tabs LOOP
4678: BSC_APPS.Add_Value_Big_In_Cond(1, g_mig_tabs(h_j));
4679: END LOOP;
4680:
4681: ELSIF h_level = 2 THEN
4682: -- KPI Level
4679: END LOOP;
4680:
4681: ELSIF h_level = 2 THEN
4682: -- KPI Level
4683: h_mig_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, h_level_column);
4684: FOR h_j IN 1 .. g_num_mig_kpis LOOP
4685: BSC_APPS.Add_Value_Big_In_Cond(1, g_mig_kpis(h_j));
4686: END LOOP;
4687:
4681: ELSIF h_level = 2 THEN
4682: -- KPI Level
4683: h_mig_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, h_level_column);
4684: FOR h_j IN 1 .. g_num_mig_kpis LOOP
4685: BSC_APPS.Add_Value_Big_In_Cond(1, g_mig_kpis(h_j));
4686: END LOOP;
4687:
4688: ELSIF h_level = 3 THEN
4689: -- Table level
4686: END LOOP;
4687:
4688: ELSIF h_level = 3 THEN
4689: -- Table level
4690: h_mig_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, h_level_column);
4691: FOR h_j IN 1 .. g_num_mig_tables LOOP
4692: BSC_APPS.Add_Value_Big_In_Cond(1, g_mig_tables(h_j));
4693: END LOOP;
4694:
4688: ELSIF h_level = 3 THEN
4689: -- Table level
4690: h_mig_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, h_level_column);
4691: FOR h_j IN 1 .. g_num_mig_tables LOOP
4692: BSC_APPS.Add_Value_Big_In_Cond(1, g_mig_tables(h_j));
4693: END LOOP;
4694:
4695: END IF;
4696:
4712:
4713: IF h_mig_condition IS NOT NULL THEN
4714: h_sql := h_sql||' WHERE '||h_mig_condition;
4715: END IF;
4716: BSC_APPS.Execute_Immediate(h_sql);
4717:
4718: COMMIT;
4719: END IF;
4720: END LOOP;
4721:
4722: -- Fix some problems
4723: -- 1. The table BSC_KPI_CAUSE_EFFECT_RELS only should have Kpis that are in g_mig_kpis
4724: -- in columns cause_indicator and effect_indicator
4725: h_cause_cond := BSC_APPS.Get_New_Big_In_Cond_Number(2, 'cause_indicator');
4726:
4727: FOR h_j in 1 .. g_num_mig_kpis LOOP
4728: BSC_APPS.Add_Value_Big_In_Cond(2, g_mig_kpis(h_j));
4729: END LOOP;
4724: -- in columns cause_indicator and effect_indicator
4725: h_cause_cond := BSC_APPS.Get_New_Big_In_Cond_Number(2, 'cause_indicator');
4726:
4727: FOR h_j in 1 .. g_num_mig_kpis LOOP
4728: BSC_APPS.Add_Value_Big_In_Cond(2, g_mig_kpis(h_j));
4729: END LOOP;
4730:
4731: h_effect_cond := BSC_APPS.Get_New_Big_In_Cond_Number(3, 'effect_indicator');
4732: FOR h_j in 1 .. g_num_mig_kpis LOOP
4727: FOR h_j in 1 .. g_num_mig_kpis LOOP
4728: BSC_APPS.Add_Value_Big_In_Cond(2, g_mig_kpis(h_j));
4729: END LOOP;
4730:
4731: h_effect_cond := BSC_APPS.Get_New_Big_In_Cond_Number(3, 'effect_indicator');
4732: FOR h_j in 1 .. g_num_mig_kpis LOOP
4733: BSC_APPS.Add_Value_Big_In_Cond(3, g_mig_kpis(h_j));
4734: END LOOP;
4735:
4729: END LOOP;
4730:
4731: h_effect_cond := BSC_APPS.Get_New_Big_In_Cond_Number(3, 'effect_indicator');
4732: FOR h_j in 1 .. g_num_mig_kpis LOOP
4733: BSC_APPS.Add_Value_Big_In_Cond(3, g_mig_kpis(h_j));
4734: END LOOP;
4735:
4736: h_sql := 'DELETE FROM bsc_kpi_cause_effect_rels'||
4737: ' WHERE NOT ('||h_cause_cond||') OR'||
4735:
4736: h_sql := 'DELETE FROM bsc_kpi_cause_effect_rels'||
4737: ' WHERE NOT ('||h_cause_cond||') OR'||
4738: ' NOT ('||h_effect_cond||')';
4739: BSC_APPS.Execute_Immediate(h_sql);
4740: COMMIT;
4741:
4742: -- 2. Reset sequence BSC_SYS_IMAGE_ID_S to start with at least the same current value of the sequence
4743: -- in the source system.
4754: IF h_src_currval > h_currval THEN
4755: h_interval := h_src_currval - h_currval;
4756:
4757: h_sql := 'ALTER SEQUENCE BSC_SYS_IMAGE_ID_S INCREMENT BY '||h_interval;
4758: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_SYS_IMAGE_ID_S');
4759:
4760: SELECT BSC_SYS_IMAGE_ID_S.NEXTVAL INTO h_currval FROM DUAL;
4761:
4762: h_sql := 'ALTER SEQUENCE BSC_SYS_IMAGE_ID_S INCREMENT BY 1';
4759:
4760: SELECT BSC_SYS_IMAGE_ID_S.NEXTVAL INTO h_currval FROM DUAL;
4761:
4762: h_sql := 'ALTER SEQUENCE BSC_SYS_IMAGE_ID_S INCREMENT BY 1';
4763: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_SYS_IMAGE_ID_S');
4764: END IF;
4765:
4766: -- 3. Reset sequence BSC_SYS_CALENDAR_ID_S to start with at least the same current value of the sequence
4767: -- in the source system.
4774: IF h_src_currval > h_currval THEN
4775: h_interval := h_src_currval - h_currval;
4776:
4777: h_sql := 'ALTER SEQUENCE BSC_SYS_CALENDAR_ID_S INCREMENT BY '||h_interval;
4778: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_SYS_CALENDAR_ID_S');
4779:
4780: SELECT BSC_SYS_CALENDAR_ID_S.NEXTVAL INTO h_currval FROM DUAL;
4781:
4782: h_sql := 'ALTER SEQUENCE BSC_SYS_CALENDAR_ID_S INCREMENT BY 1';
4779:
4780: SELECT BSC_SYS_CALENDAR_ID_S.NEXTVAL INTO h_currval FROM DUAL;
4781:
4782: h_sql := 'ALTER SEQUENCE BSC_SYS_CALENDAR_ID_S INCREMENT BY 1';
4783: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_SYS_CALENDAR_ID_S');
4784: END IF;
4785:
4786: -- 4. Reset sequence BSC_SYS_PERIODICITY_ID_S to start with at least the same current value of the sequence
4787: -- in the source system.
4794: IF h_src_currval > h_currval THEN
4795: h_interval := h_src_currval - h_currval;
4796:
4797: h_sql := 'ALTER SEQUENCE BSC_SYS_PERIODICITY_ID_S INCREMENT BY '||h_interval;
4798: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_SYS_PERIODICITY_ID_S');
4799:
4800: SELECT BSC_SYS_PERIODICITY_ID_S.NEXTVAL INTO h_currval FROM DUAL;
4801:
4802: h_sql := 'ALTER SEQUENCE BSC_SYS_PERIODICITY_ID_S INCREMENT BY 1';
4799:
4800: SELECT BSC_SYS_PERIODICITY_ID_S.NEXTVAL INTO h_currval FROM DUAL;
4801:
4802: h_sql := 'ALTER SEQUENCE BSC_SYS_PERIODICITY_ID_S INCREMENT BY 1';
4803: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_SYS_PERIODICITY_ID_S');
4804: END IF;
4805:
4806: -- 5. Migrate custom links
4807: IF BSC_APPS.APPS_ENV AND g_src_apps_flag THEN
4803: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_SYS_PERIODICITY_ID_S');
4804: END IF;
4805:
4806: -- 5. Migrate custom links
4807: IF BSC_APPS.APPS_ENV AND g_src_apps_flag THEN
4808: -- Source and target systems are in APPS
4809: IF NOT BSC_LAUNCH_PAD_PVT.Migrate_Custom_Links(g_db_link) THEN
4810: RAISE e_unexpected_error;
4811: END IF;
4814: -- Update link_id to -1
4815: h_sql := 'UPDATE bsc_tab_view_labels_b
4816: SET link_id = -1
4817: WHERE label_type = 2';
4818: BSC_APPS.Execute_Immediate(h_sql);
4819: END IF;
4820:
4821: -- 6. Fix CREATED_BY and LAST_UPDATED_BY columns of BSC_KPI_COMMENTS
4822: -- according to users in the target system.
4823: -- Update those columns in the target system with the USER_ID from FND_USER
4824: -- whose USER_NAME is the same in the source system.
4825: -- If a USER_ID do not have a corresponding USER_ID in the target system,
4826: -- it will be set to the USER_ID of the SYSADMIN user.
4827: IF BSC_APPS.APPS_ENV THEN
4828: -- Target system is APPS
4829: IF g_src_apps_flag THEN
4830: -- Source system is APPS
4831: h_sql := 'UPDATE bsc_kpi_comments c
4858: IF h_src_currval > h_currval THEN
4859: h_interval := h_src_currval - h_currval;
4860:
4861: h_sql := 'ALTER SEQUENCE BSC_KPI_COMMENTS_ID_S INCREMENT BY '||h_interval;
4862: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_KPI_COMMENTS_ID_S');
4863:
4864: SELECT BSC_KPI_COMMENTS_ID_S.NEXTVAL INTO h_currval FROM DUAL;
4865:
4866: h_sql := 'ALTER SEQUENCE BSC_KPI_COMMENTS_ID_S INCREMENT BY 1';
4863:
4864: SELECT BSC_KPI_COMMENTS_ID_S.NEXTVAL INTO h_currval FROM DUAL;
4865:
4866: h_sql := 'ALTER SEQUENCE BSC_KPI_COMMENTS_ID_S INCREMENT BY 1';
4867: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_KPI_COMMENTS_ID_S');
4868: END IF;
4869:
4870: -- 8. Add TAB_ID -1 in BSC_TAB_IND_GROUPS_B and BSC_TAB_IND_GROUPS_TL
4871: INSERT INTO BSC_TAB_IND_GROUPS_TL (TAB_ID,CSF_ID,IND_GROUP_ID,LANGUAGE,SOURCE_LANG,NAME,HELP)
4884: -- Update this column in the target system with the USER_ID from FND_USER
4885: -- whose USER_NAME is the same in the source system.
4886: -- If a USER_ID do not have a corresponding USER_ID in the target system,
4887: -- it will be set to the USER_ID of the SYSADMIN user.
4888: IF BSC_APPS.APPS_ENV THEN
4889: -- Target system is APPS
4890: IF g_src_apps_flag THEN
4891: -- Source system is APPS
4892: h_sql := 'UPDATE bsc_tabs_b c
4923: IF h_src_currval > h_currval THEN
4924: h_interval := h_src_currval - h_currval;
4925:
4926: h_sql := 'ALTER SEQUENCE BSC_INTERNAL_COLUMN_S INCREMENT BY '||h_interval;
4927: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_INTERNAL_COLUMN_S');
4928:
4929: SELECT BSC_INTERNAL_COLUMN_S.NEXTVAL INTO h_currval FROM DUAL;
4930:
4931: h_sql := 'ALTER SEQUENCE BSC_INTERNAL_COLUMN_S INCREMENT BY 1';
4928:
4929: SELECT BSC_INTERNAL_COLUMN_S.NEXTVAL INTO h_currval FROM DUAL;
4930:
4931: h_sql := 'ALTER SEQUENCE BSC_INTERNAL_COLUMN_S INCREMENT BY 1';
4932: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_INTERNAL_COLUMN_S');
4933: END IF;
4934:
4935: -- 12. Reset sequence BSC_SYS_DIM_LEVEL_ID_S to start with at least the same current value of the sequence
4936: -- in the source system.
4943: IF h_src_currval > h_currval THEN
4944: h_interval := h_src_currval - h_currval;
4945:
4946: h_sql := 'ALTER SEQUENCE BSC_SYS_DIM_LEVEL_ID_S INCREMENT BY '||h_interval;
4947: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_SYS_DIM_LEVEL_ID_S');
4948:
4949: SELECT BSC_SYS_DIM_LEVEL_ID_S.NEXTVAL INTO h_currval FROM DUAL;
4950:
4951: h_sql := 'ALTER SEQUENCE BSC_SYS_DIM_LEVEL_ID_S INCREMENT BY 1';
4948:
4949: SELECT BSC_SYS_DIM_LEVEL_ID_S.NEXTVAL INTO h_currval FROM DUAL;
4950:
4951: h_sql := 'ALTER SEQUENCE BSC_SYS_DIM_LEVEL_ID_S INCREMENT BY 1';
4952: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_SYS_DIM_LEVEL_ID_S');
4953: END IF;
4954:
4955: -- 13. Reset sequence BSC_SYS_DIM_GROUP_ID_S to start with at least the same current value of the sequence
4956: -- in the source system.
4963: IF h_src_currval > h_currval THEN
4964: h_interval := h_src_currval - h_currval;
4965:
4966: h_sql := 'ALTER SEQUENCE BSC_SYS_DIM_GROUP_ID_S INCREMENT BY '||h_interval;
4967: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_SYS_DIM_GROUP_ID_S');
4968:
4969: SELECT BSC_SYS_DIM_GROUP_ID_S.NEXTVAL INTO h_currval FROM DUAL;
4970:
4971: h_sql := 'ALTER SEQUENCE BSC_SYS_DIM_GROUP_ID_S INCREMENT BY 1';
4968:
4969: SELECT BSC_SYS_DIM_GROUP_ID_S.NEXTVAL INTO h_currval FROM DUAL;
4970:
4971: h_sql := 'ALTER SEQUENCE BSC_SYS_DIM_GROUP_ID_S INCREMENT BY 1';
4972: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_SYS_DIM_GROUP_ID_S');
4973: END IF;
4974:
4975: -- 14. Reset sequence BSC_SYS_DATASET_ID_S to start with at least the same current value of the sequence
4976: -- in the source system.
4983: IF h_src_currval > h_currval THEN
4984: h_interval := h_src_currval - h_currval;
4985:
4986: h_sql := 'ALTER SEQUENCE BSC_SYS_DATASET_ID_S INCREMENT BY '||h_interval;
4987: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_SYS_DATASET_ID_S');
4988:
4989: SELECT BSC_SYS_DATASET_ID_S.NEXTVAL INTO h_currval FROM DUAL;
4990:
4991: h_sql := 'ALTER SEQUENCE BSC_SYS_DATASET_ID_S INCREMENT BY 1';
4988:
4989: SELECT BSC_SYS_DATASET_ID_S.NEXTVAL INTO h_currval FROM DUAL;
4990:
4991: h_sql := 'ALTER SEQUENCE BSC_SYS_DATASET_ID_S INCREMENT BY 1';
4992: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_SYS_DATASET_ID_S');
4993: END IF;
4994:
4995: -- 15. Reset sequence BSC_SYS_MEASURE_ID_S to start with at least the same current value of the sequence
4996: -- in the source system.
5003: IF h_src_currval > h_currval THEN
5004: h_interval := h_src_currval - h_currval;
5005:
5006: h_sql := 'ALTER SEQUENCE BSC_SYS_MEASURE_ID_S INCREMENT BY '||h_interval;
5007: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_SYS_MEASURE_ID_S');
5008:
5009: SELECT BSC_SYS_MEASURE_ID_S.NEXTVAL INTO h_currval FROM DUAL;
5010:
5011: h_sql := 'ALTER SEQUENCE BSC_SYS_MEASURE_ID_S INCREMENT BY 1';
5008:
5009: SELECT BSC_SYS_MEASURE_ID_S.NEXTVAL INTO h_currval FROM DUAL;
5010:
5011: h_sql := 'ALTER SEQUENCE BSC_SYS_MEASURE_ID_S INCREMENT BY 1';
5012: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_SYS_MEASURE_ID_S');
5013: END IF;
5014:
5015: -- 16. Reset sequence BSC_DB_MEASURE_GROUPS_S to start with at least the same current value of the sequence
5016: -- in the source system.
5023: IF h_src_currval > h_currval THEN
5024: h_interval := h_src_currval - h_currval;
5025:
5026: h_sql := 'ALTER SEQUENCE BSC_DB_MEASURE_GROUPS_S INCREMENT BY '||h_interval;
5027: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_DB_MEASURE_GROUPS_S');
5028:
5029: SELECT BSC_DB_MEASURE_GROUPS_S.NEXTVAL INTO h_currval FROM DUAL;
5030:
5031: h_sql := 'ALTER SEQUENCE BSC_DB_MEASURE_GROUPS_S INCREMENT BY 1';
5028:
5029: SELECT BSC_DB_MEASURE_GROUPS_S.NEXTVAL INTO h_currval FROM DUAL;
5030:
5031: h_sql := 'ALTER SEQUENCE BSC_DB_MEASURE_GROUPS_S INCREMENT BY 1';
5032: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_DB_MEASURE_GROUPS_S');
5033: END IF;
5034:
5035: -- 17. Reset sequence BSC_INDICATOR_ID_S to start with at least the same current value of the sequence
5036: -- in the source system.
5043: IF h_src_currval > h_currval THEN
5044: h_interval := h_src_currval - h_currval;
5045:
5046: h_sql := 'ALTER SEQUENCE BSC_INDICATOR_ID_S INCREMENT BY '||h_interval;
5047: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_INDICATOR_ID_S');
5048:
5049: SELECT BSC_INDICATOR_ID_S.NEXTVAL INTO h_currval FROM DUAL;
5050:
5051: h_sql := 'ALTER SEQUENCE BSC_INDICATOR_ID_S INCREMENT BY 1';
5048:
5049: SELECT BSC_INDICATOR_ID_S.NEXTVAL INTO h_currval FROM DUAL;
5050:
5051: h_sql := 'ALTER SEQUENCE BSC_INDICATOR_ID_S INCREMENT BY 1';
5052: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_INDICATOR_ID_S');
5053: END IF;
5054:
5055: -- 18. Reset sequence BSC_KPI_MEASURE_S to start with at least the same current value of the sequence
5056: -- in the source system.
5063: IF h_src_currval > h_currval THEN
5064: h_interval := h_src_currval - h_currval;
5065:
5066: h_sql := 'ALTER SEQUENCE BSC_KPI_MEASURE_S INCREMENT BY '||h_interval;
5067: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_KPI_MEASURE_S');
5068:
5069: SELECT BSC_KPI_MEASURE_S.NEXTVAL INTO h_currval FROM DUAL;
5070:
5071: h_sql := 'ALTER SEQUENCE BSC_KPI_MEASURE_S INCREMENT BY 1';
5068:
5069: SELECT BSC_KPI_MEASURE_S.NEXTVAL INTO h_currval FROM DUAL;
5070:
5071: h_sql := 'ALTER SEQUENCE BSC_KPI_MEASURE_S INCREMENT BY 1';
5072: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_KPI_MEASURE_S');
5073: END IF;
5074:
5075: -- 18. Reset sequence BSC_COLOR_RANGE_ID_S to start with at least the same current value of the sequence
5076: -- in the source system.
5083: IF h_src_currval > h_currval THEN
5084: h_interval := h_src_currval - h_currval;
5085:
5086: h_sql := 'ALTER SEQUENCE BSC_COLOR_RANGE_ID_S INCREMENT BY '||h_interval;
5087: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_COLOR_RANGE_ID_S');
5088:
5089: SELECT BSC_COLOR_RANGE_ID_S.NEXTVAL INTO h_currval FROM DUAL;
5090:
5091: h_sql := 'ALTER SEQUENCE BSC_COLOR_RANGE_ID_S INCREMENT BY 1';
5088:
5089: SELECT BSC_COLOR_RANGE_ID_S.NEXTVAL INTO h_currval FROM DUAL;
5090:
5091: h_sql := 'ALTER SEQUENCE BSC_COLOR_RANGE_ID_S INCREMENT BY 1';
5092: BSC_APPS.Do_DDL(h_sql, AD_DDL.ALTER_SEQUENCE, 'BSC_COLOR_RANGE_ID_S');
5093: END IF;
5094:
5095: RETURN TRUE;
5096:
5095: RETURN TRUE;
5096:
5097: EXCEPTION
5098: WHEN e_unexpected_error THEN
5099: BSC_MESSAGE.Add (x_message => BSC_APPS.Get_Message('BSC_MIG_FAIL_EXEC'),
5100: x_source => 'BSC_MIGRATION.Migrate_Metadata');
5101: RETURN FALSE;
5102:
5103: WHEN OTHERS THEN
5176: CMT.INDICATOR = TGT.INDICATOR';
5177: e_unexpected_error EXCEPTION;
5178: BEGIN
5179:
5180: BSC_APPS.Do_DDL(l_stmt, AD_DDL.CREATE_TABLE, c_comments_bak);
5181: EXCEPTION
5182: WHEN OTHERS THEN
5183: BSC_MESSAGE.Add (x_message => SQLERRM,
5184: x_source => 'BSC_MIGRATION.create_comment_backup_table');
5224: e_unexpected_error EXCEPTION;
5225: BEGIN
5226:
5227: -- for testing comments APIs
5228: IF (BSC_APPS.bsc_apps_schema IS NULL) THEN
5229: BSC_APPS.Init_BSC_APPS;
5230: END IF;
5231:
5232: IF (get_previous_migration_status) THEN
5225: BEGIN
5226:
5227: -- for testing comments APIs
5228: IF (BSC_APPS.bsc_apps_schema IS NULL) THEN
5229: BSC_APPS.Init_BSC_APPS;
5230: END IF;
5231:
5232: IF (get_previous_migration_status) THEN
5233: IF table_exists(c_comments_bak, BSC_APPS.bsc_apps_schema) THEN
5229: BSC_APPS.Init_BSC_APPS;
5230: END IF;
5231:
5232: IF (get_previous_migration_status) THEN
5233: IF table_exists(c_comments_bak, BSC_APPS.bsc_apps_schema) THEN
5234: BSC_APPS.Do_DDL('DROP TABLE '||c_comments_bak, AD_DDL.DROP_TABLE, c_comments_bak);
5235: END IF;
5236: create_comment_backup_table;
5237: ELSE
5230: END IF;
5231:
5232: IF (get_previous_migration_status) THEN
5233: IF table_exists(c_comments_bak, BSC_APPS.bsc_apps_schema) THEN
5234: BSC_APPS.Do_DDL('DROP TABLE '||c_comments_bak, AD_DDL.DROP_TABLE, c_comments_bak);
5235: END IF;
5236: create_comment_backup_table;
5237: ELSE
5238: IF NOT table_exists(c_comments_bak, BSC_APPS.bsc_apps_schema) THEN
5234: BSC_APPS.Do_DDL('DROP TABLE '||c_comments_bak, AD_DDL.DROP_TABLE, c_comments_bak);
5235: END IF;
5236: create_comment_backup_table;
5237: ELSE
5238: IF NOT table_exists(c_comments_bak, BSC_APPS.bsc_apps_schema) THEN
5239: create_comment_backup_table;
5240: END IF;
5241: END IF;
5242:
5415:
5416: -- Init g_warnings
5417: g_warnings := FALSE;
5418:
5419: -- Initialize BSC/APPS global variables
5420: BSC_APPS.Init_Bsc_Apps;
5421:
5422: -- Initializes the error message stack
5423: BSC_MESSAGE.Init(g_debug_flag);
5416: -- Init g_warnings
5417: g_warnings := FALSE;
5418:
5419: -- Initialize BSC/APPS global variables
5420: BSC_APPS.Init_Bsc_Apps;
5421:
5422: -- Initializes the error message stack
5423: BSC_MESSAGE.Init(g_debug_flag);
5424:
5422: -- Initializes the error message stack
5423: BSC_MESSAGE.Init(g_debug_flag);
5424:
5425: -- Initialize the temporal table used for big 'in' conditions
5426: BSC_APPS.Init_Big_In_Cond_Table;
5427:
5428: -- Initiliaze log file
5429: h_log_file_name := 'bscmig40.log';
5430: IF NOT BSC_APPS.Init_Log_File(h_log_file_name, h_error_msg) THEN
5426: BSC_APPS.Init_Big_In_Cond_Table;
5427:
5428: -- Initiliaze log file
5429: h_log_file_name := 'bscmig40.log';
5430: IF NOT BSC_APPS.Init_Log_File(h_log_file_name, h_error_msg) THEN
5431: BSC_MESSAGE.Add(x_message => h_error_msg,
5432: x_source => 'BSC_MIGRATION.Migrate_System');
5433: RAISE e_unexpected_error;
5434: END IF;
5444: */
5445:
5446: -- Fix performance bug#3860149
5447: h_sql := 'alter session set hash_area_size=50000000';
5448: BSC_APPS.Execute_Immediate(h_sql);
5449: h_sql := 'alter session set sort_area_size=50000000';
5450: BSC_APPS.Execute_Immediate(h_sql);
5451: commit;
5452: h_sql := 'alter session enable parallel dml';
5446: -- Fix performance bug#3860149
5447: h_sql := 'alter session set hash_area_size=50000000';
5448: BSC_APPS.Execute_Immediate(h_sql);
5449: h_sql := 'alter session set sort_area_size=50000000';
5450: BSC_APPS.Execute_Immediate(h_sql);
5451: commit;
5452: h_sql := 'alter session enable parallel dml';
5453: BSC_APPS.Execute_Immediate(h_sql);
5454: commit;
5449: h_sql := 'alter session set sort_area_size=50000000';
5450: BSC_APPS.Execute_Immediate(h_sql);
5451: commit;
5452: h_sql := 'alter session enable parallel dml';
5453: BSC_APPS.Execute_Immediate(h_sql);
5454: commit;
5455:
5456:
5457: -- Alter session set global_names = false to disable the enforcement
5456:
5457: -- Alter session set global_names = false to disable the enforcement
5458: -- of database link name must be equals to remote database name.
5459: h_sql := 'ALTER SESSION SET GLOBAL_NAMES = FALSE';
5460: BSC_APPS.Execute_Immediate(h_sql);
5461:
5462:
5463: -- Get parameters from BSC_DB_LOADER_CONTROL
5464: Get_Migration_Parameters(
5497: -- Validations
5498:
5499: -- Validate x_overwrite is 'Y'
5500: IF h_overwrite <> 'Y' THEN
5501: h_validation_error := BSC_APPS.Get_Message('BSC_MIG_CONFIRM_OVERW');
5502: BSC_APPS.Write_Line_Log(h_validation_error, BSC_APPS.OUTPUT_FILE);
5503: BSC_MESSAGE.Add(x_message => h_validation_error,
5504: x_source => 'BSC_MIGRATION.Migrate_System');
5505: RAISE e_unexpected_error;
5498:
5499: -- Validate x_overwrite is 'Y'
5500: IF h_overwrite <> 'Y' THEN
5501: h_validation_error := BSC_APPS.Get_Message('BSC_MIG_CONFIRM_OVERW');
5502: BSC_APPS.Write_Line_Log(h_validation_error, BSC_APPS.OUTPUT_FILE);
5503: BSC_MESSAGE.Add(x_message => h_validation_error,
5504: x_source => 'BSC_MIGRATION.Migrate_System');
5505: RAISE e_unexpected_error;
5506: END IF;
5506: END IF;
5507:
5508: -- Validate that the source and target system are same version
5509: IF NOT Validate_System_Versions(h_validation_error) THEN
5510: BSC_APPS.Write_Line_Log(h_validation_error, BSC_APPS.OUTPUT_FILE);
5511: BSC_MESSAGE.Add(x_message => h_validation_error,
5512: x_source => 'BSC_MIGRATION.Migrate_System');
5513: RAISE e_unexpected_error;
5514: END IF;
5517: -- By the way initialize the array g_src_resps and g_trg_resps
5518: IF NOT Validate_Responsibilities(h_src_responsibilities,
5519: h_trg_responsibilities,
5520: h_validation_error) THEN
5521: BSC_APPS.Write_Line_Log(h_validation_error, BSC_APPS.OUTPUT_FILE);
5522: BSC_MESSAGE.Add(x_message => h_validation_error,
5523: x_source => 'BSC_MIGRATION.Migrate_System');
5524: RAISE e_unexpected_error;
5525: END IF;
5528: -- By the way initialize the array g_tabs_filter and g_kpis_filter
5529: IF NOT Validate_Filters(h_tab_filter,
5530: h_kpi_filter,
5531: h_validation_error) THEN
5532: BSC_APPS.Write_Line_Log(h_validation_error, BSC_APPS.OUTPUT_FILE);
5533: BSC_MESSAGE.Add(x_message => h_validation_error,
5534: x_source => 'BSC_MIGRATION.Migrate_System');
5535: RAISE e_unexpected_error;
5536: END IF;
5612: -- non custom PMF data
5613: -- then we should not be throwing error that there are no objectives according to
5614: -- filters, in this case migration should finish with warning.
5615: IF g_num_no_mig_kpis = 0 THEN
5616: h_validation_error := BSC_APPS.Get_Message('BSC_MIG_NO_INDICATORS');
5617: BSC_APPS.Write_Line_Log(h_validation_error, BSC_APPS.OUTPUT_FILE);
5618: BSC_MESSAGE.Add(x_message => h_validation_error,
5619: x_source => 'BSC_MIGRATION.Migrate_System');
5620: RAISE e_unexpected_error;
5613: -- then we should not be throwing error that there are no objectives according to
5614: -- filters, in this case migration should finish with warning.
5615: IF g_num_no_mig_kpis = 0 THEN
5616: h_validation_error := BSC_APPS.Get_Message('BSC_MIG_NO_INDICATORS');
5617: BSC_APPS.Write_Line_Log(h_validation_error, BSC_APPS.OUTPUT_FILE);
5618: BSC_MESSAGE.Add(x_message => h_validation_error,
5619: x_source => 'BSC_MIGRATION.Migrate_System');
5620: RAISE e_unexpected_error;
5621: ELSE
5637: -- At this point, the system is inconsistent until everything have been migrated
5638: -- So, we flag the system like if it is not in the latest version.
5639: UPDATE bsc_sys_init
5640: SET property_value = 'UNAPPLIED',
5641: last_updated_by = BSC_APPS.fnd_global_user_id,
5642: last_update_date = SYSDATE
5643: WHERE property_code = 'TARGET_STATUS';
5644: COMMIT;
5645:
5653: RAISE e_unexpected_error;
5654: END IF;
5655:
5656: -- Get the user_id of the SYSADMIN user in the target system
5657: IF BSC_APPS.APPS_ENV THEN
5658: -- The target system is APPS
5659: h_sql := 'SELECT user_id FROM fnd_user WHERE user_name = ''SYSADMIN''';
5660: OPEN h_cursor FOR h_sql;
5661: FETCH h_cursor INTO h_sysadmin_user_id;
5699: ' INTO BSC_SYS_INIT ('||h_lst_columns||')'||
5700: ' SELECT /*+ parallel(BSC_SYS_INIT)*/ '||h_lst_columns||
5701: ' FROM BSC_SYS_INIT'||'@'||g_db_link ||
5702: ' WHERE PROPERTY_CODE <> ''PATCH_NUMBER'' AND PROPERTY_CODE <>''EDW_INSTALLED'' ';
5703: BSC_APPS.Execute_Immediate(h_sql);
5704: COMMIT;
5705:
5706: -- At this point, the system is inconsistent until everything have been migrated
5707: -- So, we flag the system like if it is not in the latest version.
5707: -- So, we flag the system like if it is not in the latest version.
5708: -- I do it again because the records in BSC_SYS_INIT have been migrated from the source.
5709: UPDATE bsc_sys_init
5710: SET property_value = 'UNAPPLIED',
5711: last_updated_by = BSC_APPS.fnd_global_user_id,
5712: last_update_date = SYSDATE
5713: WHERE property_code = 'TARGET_STATUS';
5714: COMMIT;
5715:
5722: FETCH h_cursor INTO h_short_name;
5723: CLOSE h_cursor;
5724: IF h_short_name IS NOT NULL THEN
5725: -- By design there is a form function and ak region called as the short name
5726: BSC_APPS.Write_Line_Log('Migrating report '||h_short_name, BSC_APPS.OUTPUT_FILE);
5727: IF NOT Migrate_AK_Region(h_short_name, h_error_msg) THEN
5728: BSC_APPS.Write_Line_Log('Error migrating AK Region '||h_short_name||': '||h_error_msg,
5729: BSC_APPS.OUTPUT_FILE);
5730: END IF;
5724: IF h_short_name IS NOT NULL THEN
5725: -- By design there is a form function and ak region called as the short name
5726: BSC_APPS.Write_Line_Log('Migrating report '||h_short_name, BSC_APPS.OUTPUT_FILE);
5727: IF NOT Migrate_AK_Region(h_short_name, h_error_msg) THEN
5728: BSC_APPS.Write_Line_Log('Error migrating AK Region '||h_short_name||': '||h_error_msg,
5729: BSC_APPS.OUTPUT_FILE);
5730: END IF;
5731: IF NOT Migrate_Form_Function(h_short_name, h_error_msg) THEN
5732: BSC_APPS.Write_Line_Log('Error migrating Form Function '||h_short_name||': '||h_error_msg,
5725: -- By design there is a form function and ak region called as the short name
5726: BSC_APPS.Write_Line_Log('Migrating report '||h_short_name, BSC_APPS.OUTPUT_FILE);
5727: IF NOT Migrate_AK_Region(h_short_name, h_error_msg) THEN
5728: BSC_APPS.Write_Line_Log('Error migrating AK Region '||h_short_name||': '||h_error_msg,
5729: BSC_APPS.OUTPUT_FILE);
5730: END IF;
5731: IF NOT Migrate_Form_Function(h_short_name, h_error_msg) THEN
5732: BSC_APPS.Write_Line_Log('Error migrating Form Function '||h_short_name||': '||h_error_msg,
5733: BSC_APPS.OUTPUT_FILE);
5728: BSC_APPS.Write_Line_Log('Error migrating AK Region '||h_short_name||': '||h_error_msg,
5729: BSC_APPS.OUTPUT_FILE);
5730: END IF;
5731: IF NOT Migrate_Form_Function(h_short_name, h_error_msg) THEN
5732: BSC_APPS.Write_Line_Log('Error migrating Form Function '||h_short_name||': '||h_error_msg,
5733: BSC_APPS.OUTPUT_FILE);
5734: END IF;
5735: END IF;
5736: END LOOP;
5729: BSC_APPS.OUTPUT_FILE);
5730: END IF;
5731: IF NOT Migrate_Form_Function(h_short_name, h_error_msg) THEN
5732: BSC_APPS.Write_Line_Log('Error migrating Form Function '||h_short_name||': '||h_error_msg,
5733: BSC_APPS.OUTPUT_FILE);
5734: END IF;
5735: END IF;
5736: END LOOP;
5737:
5750: -- Fix performance bug#3860149: indexes are going to be created in parallel
5751: -- for that reason we need the following commands
5752: commit;
5753: h_sql := 'alter session force parallel query';
5754: BSC_APPS.Execute_Immediate(h_sql);
5755: commit;
5756: h_sql := 'alter session enable parallel dml';
5757: BSC_APPS.Execute_Immediate(h_sql);
5758: commit;
5753: h_sql := 'alter session force parallel query';
5754: BSC_APPS.Execute_Immediate(h_sql);
5755: commit;
5756: h_sql := 'alter session enable parallel dml';
5757: BSC_APPS.Execute_Immediate(h_sql);
5758: commit;
5759:
5760: IF NOT Create_Indexes_Dynamic_Tables THEN
5761: RAISE e_unexpected_error;
5762: END IF;
5763:
5764: commit;
5765: h_sql := 'alter session disable parallel dml';
5766: BSC_APPS.Execute_Immediate(h_sql);
5767: commit;
5768: h_sql := 'alter session disable parallel query';
5769: BSC_APPS.Execute_Immediate(h_sql);
5770: commit;
5765: h_sql := 'alter session disable parallel dml';
5766: BSC_APPS.Execute_Immediate(h_sql);
5767: commit;
5768: h_sql := 'alter session disable parallel query';
5769: BSC_APPS.Execute_Immediate(h_sql);
5770: commit;
5771: h_sql := 'alter session enable parallel dml';
5772: BSC_APPS.Execute_Immediate(h_sql);
5773: commit;
5768: h_sql := 'alter session disable parallel query';
5769: BSC_APPS.Execute_Immediate(h_sql);
5770: commit;
5771: h_sql := 'alter session enable parallel dml';
5772: BSC_APPS.Execute_Immediate(h_sql);
5773: commit;
5774:
5775: -- Fix performance bug#3860149: analyze base and dimension tables
5776: IF NOT Analyze_Base_And_Dim_Tables THEN
5808: END IF;
5809:
5810: -- BSC-MV Note: Need to create MVs for migrated Kpis and refresh them.
5811: -- AW_INTEGRATION: Need to create cubes for migrated Kpis and refresh them.
5812: h_base_message_v := BSC_APPS.Get_Message('BSC_CREATING_VIEW');
5813: IF g_adv_sum_level IS NOT NULL THEN
5814: -- Create MVs for migrated Kpis that has prototype flag 0, 6 or 7
5815: FOR h_i IN 1..g_num_mig_kpis LOOP
5816: -- Get prototype flag and report type
5839: --AW_INTEGRATION: see if the indicator is implemented in AW
5840: IF BSC_UPDATE_UTIL.Get_Kpi_Impl_Type(g_mig_kpis(h_i)) = 2 THEN
5841: --AW implementation
5842: h_message := 'Creating AW cubes: '||g_mig_kpis(h_i);
5843: BSC_APPS.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
5844: h_kpi_list.delete;
5845: h_kpi_list(1) := g_mig_kpis(h_i);
5846: bsc_aw_adapter.implement_kpi_aw(h_kpi_list,
5847: 'DEBUG LOG,RECREATE KPI,SUMMARIZATION LEVEL='||h_kpi_sum_level);
5846: bsc_aw_adapter.implement_kpi_aw(h_kpi_list,
5847: 'DEBUG LOG,RECREATE KPI,SUMMARIZATION LEVEL='||h_kpi_sum_level);
5848: ELSE
5849: --MV implementation
5850: h_message := BSC_APPS.Replace_Token(h_base_message_v, 'VIEW', TO_CHAR(g_mig_kpis(h_i)));
5851: BSC_APPS.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
5852:
5853: IF NOT BSC_BIA_WRAPPER.Implement_Bsc_MV(g_mig_kpis(h_i),
5854: h_kpi_sum_level,
5847: 'DEBUG LOG,RECREATE KPI,SUMMARIZATION LEVEL='||h_kpi_sum_level);
5848: ELSE
5849: --MV implementation
5850: h_message := BSC_APPS.Replace_Token(h_base_message_v, 'VIEW', TO_CHAR(g_mig_kpis(h_i)));
5851: BSC_APPS.Write_Line_Log(h_message, BSC_UPDATE_LOG.OUTPUT);
5852:
5853: IF NOT BSC_BIA_WRAPPER.Implement_Bsc_MV(g_mig_kpis(h_i),
5854: h_kpi_sum_level,
5855: FALSE,
5885: IF NOT BSC_UPDATE_DIM.Refresh_Dbi_Dimension_Table(h_dbi_short_name) THEN
5886: -- fix bug#4682494: if for any reason the dbi dimension cannot be refreshed
5887: -- then we write this to the log file an continue
5888: g_warnings := TRUE;
5889: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_WARNING')||' '||
5890: BSC_APPS.Get_Message('BSC_RETR_DIMTABLE_FAILED')||' '||h_dbi_short_name,
5891: BSC_UPDATE_LOG.OUTPUT);
5892: END IF;
5893: END LOOP;
5886: -- fix bug#4682494: if for any reason the dbi dimension cannot be refreshed
5887: -- then we write this to the log file an continue
5888: g_warnings := TRUE;
5889: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_WARNING')||' '||
5890: BSC_APPS.Get_Message('BSC_RETR_DIMTABLE_FAILED')||' '||h_dbi_short_name,
5891: BSC_UPDATE_LOG.OUTPUT);
5892: END IF;
5893: END LOOP;
5894: CLOSE h_cursor;
6011:
6012: COMMIT;
6013:
6014: --BSC-MV Note: Overwrite profile BSC_ADVANCED_SUMMARIZATION_LEVEL with the value from source
6015: IF BSC_APPS.APPS_ENV THEN
6016: IF NOT FND_PROFILE.SAVE('BSC_ADVANCED_SUMMARIZATION_LEVEL', TO_CHAR(g_adv_sum_level), 'SITE') THEN
6017: RAISE e_unexpected_error;
6018: END IF;
6019:
6027:
6028: -- At this point, the system is consistent
6029: UPDATE bsc_sys_init
6030: SET property_value = 'APPLIED',
6031: last_updated_by = BSC_APPS.fnd_global_user_id,
6032: last_update_date = SYSDATE
6033: WHERE property_code = 'TARGET_STATUS';
6034: COMMIT;
6035:
6033: WHERE property_code = 'TARGET_STATUS';
6034: COMMIT;
6035:
6036: -- Write Program completed to log file
6037: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6038: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6039: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6040: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6041:
6034: COMMIT;
6035:
6036: -- Write Program completed to log file
6037: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6038: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6039: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6040: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6041:
6042: -- Write in the log file the PMF objects that are not valid
6035:
6036: -- Write Program completed to log file
6037: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6038: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6039: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6040: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6041:
6042: -- Write in the log file the PMF objects that are not valid
6043: --Fix bug#4220506
6036: -- Write Program completed to log file
6037: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6038: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6039: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6040: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6041:
6042: -- Write in the log file the PMF objects that are not valid
6043: --Fix bug#4220506
6044: Write_Log_Invalid_PMF_Objects;
6043: --Fix bug#4220506
6044: Write_Log_Invalid_PMF_Objects;
6045:
6046: -- Delete records in the temporal table used for big 'in' conditions
6047: BSC_APPS.Init_Big_In_Cond_Table;
6048:
6049: --Bug 3919106
6050: if(BSC_OLAP_MAIN.b_table_col_type_created) then
6051: BSC_OLAP_MAIN.drop_tmp_col_type_table;
6056: -- error message is in h_error_msg
6057: ROLLBACK;
6058:
6059: -- Delete records in the temporal table used for big 'in' conditions
6060: BSC_APPS.Init_Big_In_Cond_Table;
6061:
6062: BSC_MESSAGE.flush;
6063: BSC_MESSAGE.Add(x_message => 'Error in BSC_BIA_WRAPPER.Load_Reporting_Calendar: '||h_calendar_id||' '||h_error_msg,
6064: x_source => 'BSC_MIGRATION.Migrate_System',
6064: x_source => 'BSC_MIGRATION.Migrate_System',
6065: x_mode => 'I');
6066: COMMIT;
6067:
6068: BSC_APPS.Write_Errors_To_Log;
6069:
6070: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6071: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6072: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6066: COMMIT;
6067:
6068: BSC_APPS.Write_Errors_To_Log;
6069:
6070: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6071: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6072: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6073: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6074:
6067:
6068: BSC_APPS.Write_Errors_To_Log;
6069:
6070: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6071: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6072: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6073: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6074:
6075: WHEN e_creating_dbi_dim_tables THEN
6068: BSC_APPS.Write_Errors_To_Log;
6069:
6070: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6071: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6072: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6073: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6074:
6075: WHEN e_creating_dbi_dim_tables THEN
6076: -- error message is in h_error_msg
6069:
6070: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6071: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6072: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6073: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6074:
6075: WHEN e_creating_dbi_dim_tables THEN
6076: -- error message is in h_error_msg
6077: ROLLBACK;
6076: -- error message is in h_error_msg
6077: ROLLBACK;
6078:
6079: -- Delete records in the temporal table used for big 'in' conditions
6080: BSC_APPS.Init_Big_In_Cond_Table;
6081:
6082: BSC_MESSAGE.flush;
6083: BSC_MESSAGE.Add(x_message => 'Error in BSC_UPDATE_DIM.Create_Dbi_Dim_Tables: '||h_error_msg,
6084: x_source => 'BSC_MIGRATION.Migrate_System',
6084: x_source => 'BSC_MIGRATION.Migrate_System',
6085: x_mode => 'I');
6086: COMMIT;
6087:
6088: BSC_APPS.Write_Errors_To_Log;
6089:
6090: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6091: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6092: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6086: COMMIT;
6087:
6088: BSC_APPS.Write_Errors_To_Log;
6089:
6090: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6091: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6092: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6093: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6094:
6087:
6088: BSC_APPS.Write_Errors_To_Log;
6089:
6090: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6091: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6092: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6093: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6094:
6095: WHEN g_error_synch_bsc_pmf THEN
6088: BSC_APPS.Write_Errors_To_Log;
6089:
6090: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6091: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6092: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6093: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6094:
6095: WHEN g_error_synch_bsc_pmf THEN
6096: -- error message is in h_error_msg
6089:
6090: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6091: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6092: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6093: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6094:
6095: WHEN g_error_synch_bsc_pmf THEN
6096: -- error message is in h_error_msg
6097: ROLLBACK;
6096: -- error message is in h_error_msg
6097: ROLLBACK;
6098:
6099: -- Delete records in the temporal table used for big 'in' conditions
6100: BSC_APPS.Init_Big_In_Cond_Table;
6101:
6102: BSC_MESSAGE.flush;
6103: BSC_MESSAGE.Add(x_message => h_error_msg,
6104: x_source => 'BSC_MIGRATION.Migrate_System',
6104: x_source => 'BSC_MIGRATION.Migrate_System',
6105: x_mode => 'I');
6106: COMMIT;
6107:
6108: BSC_APPS.Write_Errors_To_Log;
6109:
6110: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6111: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6112: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6106: COMMIT;
6107:
6108: BSC_APPS.Write_Errors_To_Log;
6109:
6110: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6111: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6112: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6113: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6114:
6107:
6108: BSC_APPS.Write_Errors_To_Log;
6109:
6110: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6111: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6112: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6113: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6114:
6115: WHEN e_error_implement_mv THEN
6108: BSC_APPS.Write_Errors_To_Log;
6109:
6110: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6111: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6112: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6113: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6114:
6115: WHEN e_error_implement_mv THEN
6116: ROLLBACK;
6109:
6110: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6111: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6112: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6113: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6114:
6115: WHEN e_error_implement_mv THEN
6116: ROLLBACK;
6117:
6115: WHEN e_error_implement_mv THEN
6116: ROLLBACK;
6117:
6118: -- Delete records in the temporal table used for big 'in' conditions
6119: BSC_APPS.Init_Big_In_Cond_Table;
6120:
6121: BSC_MESSAGE.flush;
6122: BSC_MESSAGE.Add(x_message => 'BSC_BIA_WRAPPER.Implement_Bsc_MV '||g_mig_kpis(h_i)||' '||h_error_implement_mv,
6123: x_source => 'BSC_MIGRATION.Migrate_System',
6123: x_source => 'BSC_MIGRATION.Migrate_System',
6124: x_mode => 'I');
6125: COMMIT;
6126:
6127: BSC_APPS.Write_Errors_To_Log;
6128:
6129: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6130: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6131: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6125: COMMIT;
6126:
6127: BSC_APPS.Write_Errors_To_Log;
6128:
6129: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6130: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6131: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6132: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6133:
6126:
6127: BSC_APPS.Write_Errors_To_Log;
6128:
6129: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6130: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6131: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6132: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6133:
6134: WHEN e_unexpected_error THEN
6127: BSC_APPS.Write_Errors_To_Log;
6128:
6129: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6130: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6131: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6132: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6133:
6134: WHEN e_unexpected_error THEN
6135: ROLLBACK;
6128:
6129: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6130: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6131: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6132: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6133:
6134: WHEN e_unexpected_error THEN
6135: ROLLBACK;
6136:
6134: WHEN e_unexpected_error THEN
6135: ROLLBACK;
6136:
6137: -- Delete records in the temporal table used for big 'in' conditions
6138: BSC_APPS.Init_Big_In_Cond_Table;
6139:
6140: BSC_MESSAGE.flush;
6141: BSC_MESSAGE.Add(x_message => BSC_APPS.Get_Message('BSC_MIG_FAIL_EXEC'),
6142: x_source => 'BSC_MIGRATION.Migrate_System',
6137: -- Delete records in the temporal table used for big 'in' conditions
6138: BSC_APPS.Init_Big_In_Cond_Table;
6139:
6140: BSC_MESSAGE.flush;
6141: BSC_MESSAGE.Add(x_message => BSC_APPS.Get_Message('BSC_MIG_FAIL_EXEC'),
6142: x_source => 'BSC_MIGRATION.Migrate_System',
6143: x_mode => 'I');
6144: COMMIT;
6145:
6142: x_source => 'BSC_MIGRATION.Migrate_System',
6143: x_mode => 'I');
6144: COMMIT;
6145:
6146: BSC_APPS.Write_Errors_To_Log;
6147:
6148: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6149: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6150: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6144: COMMIT;
6145:
6146: BSC_APPS.Write_Errors_To_Log;
6147:
6148: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6149: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6150: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6151: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6152:
6145:
6146: BSC_APPS.Write_Errors_To_Log;
6147:
6148: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6149: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6150: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6151: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6152:
6153: WHEN e_warning_raised THEN
6146: BSC_APPS.Write_Errors_To_Log;
6147:
6148: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6149: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6150: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6151: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6152:
6153: WHEN e_warning_raised THEN
6154: ROLLBACK;
6147:
6148: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6149: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6150: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6151: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6152:
6153: WHEN e_warning_raised THEN
6154: ROLLBACK;
6155: -- Delete records in the temporal table used for big 'in' conditions
6152:
6153: WHEN e_warning_raised THEN
6154: ROLLBACK;
6155: -- Delete records in the temporal table used for big 'in' conditions
6156: BSC_APPS.Init_Big_In_Cond_Table;
6157:
6158: BSC_MESSAGE.flush;
6159:
6160: COMMIT ;
6162: WHEN OTHERS THEN
6163: ROLLBACK;
6164:
6165: -- Delete records in the temporal table used for big 'in' conditions
6166: BSC_APPS.Init_Big_In_Cond_Table;
6167:
6168: BSC_MESSAGE.flush;
6169: BSC_MESSAGE.Add(x_message => SQLERRM,
6170: x_source => 'BSC_MIGRATION.Migrate_System',
6170: x_source => 'BSC_MIGRATION.Migrate_System',
6171: x_mode => 'I');
6172: COMMIT;
6173:
6174: BSC_APPS.Write_Errors_To_Log;
6175:
6176: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6177: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6178: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6172: COMMIT;
6173:
6174: BSC_APPS.Write_Errors_To_Log;
6175:
6176: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6177: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6178: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6179: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6180:
6173:
6174: BSC_APPS.Write_Errors_To_Log;
6175:
6176: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6177: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6178: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6179: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6180:
6181: END Migrate_System;
6174: BSC_APPS.Write_Errors_To_Log;
6175:
6176: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6177: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6178: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6179: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6180:
6181: END Migrate_System;
6182:
6175:
6176: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TIME')||
6177: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'SYMBOL_COLON')||
6178: ' '||TO_CHAR(SYSDATE, c_fto_long_date_time), BSC_APPS.OUTPUT_FILE);
6179: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_PROGRAM_COMPLETED'), BSC_APPS.OUTPUT_FILE);
6180:
6181: END Migrate_System;
6182:
6183:
6565: RAISE e_invalid_kpi_filter;
6566: END IF;
6567:
6568: -- All the provided tabs must be accessible by any of the source responsibilities.
6569: h_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'responsibility_id');
6570: FOR h_i IN 1..g_num_src_resps LOOP
6571: BSC_APPS.Add_Value_Big_In_Cond(1, g_src_resps(h_i));
6572: END LOOP;
6573:
6567:
6568: -- All the provided tabs must be accessible by any of the source responsibilities.
6569: h_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'responsibility_id');
6570: FOR h_i IN 1..g_num_src_resps LOOP
6571: BSC_APPS.Add_Value_Big_In_Cond(1, g_src_resps(h_i));
6572: END LOOP;
6573:
6574: FOR h_i IN 1..g_num_tabs_filter LOOP
6575: h_sql := 'SELECT tab_id'||
6603: RETURN TRUE;
6604:
6605: EXCEPTION
6606: WHEN e_invalid_tab_filter THEN
6607: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_INV_TAB_FILTER');
6608: RETURN FALSE;
6609:
6610: WHEN e_invalid_kpi_filter THEN
6611: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_INV_KPI_FILTER');
6607: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_INV_TAB_FILTER');
6608: RETURN FALSE;
6609:
6610: WHEN e_invalid_kpi_filter THEN
6611: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_INV_KPI_FILTER');
6612: RETURN FALSE;
6613:
6614: WHEN e_no_tab_access THEN
6615: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_NO_TAB_ACCESS');
6611: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_INV_KPI_FILTER');
6612: RETURN FALSE;
6613:
6614: WHEN e_no_tab_access THEN
6615: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_NO_TAB_ACCESS');
6616: x_validation_error := BSC_APPS.Replace_Token(x_validation_error, 'TAB_ID', TO_CHAR(h_access_item));
6617: RETURN FALSE;
6618:
6619: WHEN e_no_kpi_access THEN
6612: RETURN FALSE;
6613:
6614: WHEN e_no_tab_access THEN
6615: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_NO_TAB_ACCESS');
6616: x_validation_error := BSC_APPS.Replace_Token(x_validation_error, 'TAB_ID', TO_CHAR(h_access_item));
6617: RETURN FALSE;
6618:
6619: WHEN e_no_kpi_access THEN
6620: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_NO_KPI_ACCESS');
6616: x_validation_error := BSC_APPS.Replace_Token(x_validation_error, 'TAB_ID', TO_CHAR(h_access_item));
6617: RETURN FALSE;
6618:
6619: WHEN e_no_kpi_access THEN
6620: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_NO_KPI_ACCESS');
6621: x_validation_error := BSC_APPS.Replace_Token(x_validation_error, 'INDICATOR', TO_CHAR(h_access_item));
6622: RETURN FALSE;
6623:
6624: WHEN OTHERS THEN
6617: RETURN FALSE;
6618:
6619: WHEN e_no_kpi_access THEN
6620: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_NO_KPI_ACCESS');
6621: x_validation_error := BSC_APPS.Replace_Token(x_validation_error, 'INDICATOR', TO_CHAR(h_access_item));
6622: RETURN FALSE;
6623:
6624: WHEN OTHERS THEN
6625: x_validation_error := SQLERRM;
6716: RETURN TRUE;
6717:
6718: EXCEPTION
6719: WHEN e_invalid_src_resps THEN
6720: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_INV_SRC_RESPS');
6721: RETURN FALSE;
6722:
6723: WHEN e_invalid_trg_resps THEN
6724: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_INV_TRG_RESPS');
6720: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_INV_SRC_RESPS');
6721: RETURN FALSE;
6722:
6723: WHEN e_invalid_trg_resps THEN
6724: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_INV_TRG_RESPS');
6725: RETURN FALSE;
6726:
6727: WHEN e_null_src_resps THEN
6728: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_SRC_RESPS_EMPTY');
6724: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_INV_TRG_RESPS');
6725: RETURN FALSE;
6726:
6727: WHEN e_null_src_resps THEN
6728: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_SRC_RESPS_EMPTY');
6729: RETURN FALSE;
6730:
6731: WHEN e_null_trg_resps THEN
6732: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_TRG_RESPS_EMPTY');
6728: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_SRC_RESPS_EMPTY');
6729: RETURN FALSE;
6730:
6731: WHEN e_null_trg_resps THEN
6732: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_TRG_RESPS_EMPTY');
6733: RETURN FALSE;
6734:
6735: WHEN e_no_match_resps THEN
6736: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_NOMATCH_RESPS');
6732: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_TRG_RESPS_EMPTY');
6733: RETURN FALSE;
6734:
6735: WHEN e_no_match_resps THEN
6736: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_NOMATCH_RESPS');
6737: RETURN FALSE;
6738:
6739: WHEN e_no_src_resp THEN
6740: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_SRC_RESP_NOEXIST');
6736: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_NOMATCH_RESPS');
6737: RETURN FALSE;
6738:
6739: WHEN e_no_src_resp THEN
6740: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_SRC_RESP_NOEXIST');
6741: x_validation_error := BSC_APPS.Replace_Token(x_validation_error, 'RESP_ID', TO_CHAR(h_resp));
6742: RETURN FALSE;
6743:
6744: WHEN e_no_trg_resp THEN
6737: RETURN FALSE;
6738:
6739: WHEN e_no_src_resp THEN
6740: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_SRC_RESP_NOEXIST');
6741: x_validation_error := BSC_APPS.Replace_Token(x_validation_error, 'RESP_ID', TO_CHAR(h_resp));
6742: RETURN FALSE;
6743:
6744: WHEN e_no_trg_resp THEN
6745: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_TRG_RESP_NOEXIST');
6741: x_validation_error := BSC_APPS.Replace_Token(x_validation_error, 'RESP_ID', TO_CHAR(h_resp));
6742: RETURN FALSE;
6743:
6744: WHEN e_no_trg_resp THEN
6745: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_TRG_RESP_NOEXIST');
6746: x_validation_error := BSC_APPS.Replace_Token(x_validation_error, 'RESP_ID', TO_CHAR(h_resp));
6747: RETURN FALSE;
6748:
6749: WHEN OTHERS THEN
6742: RETURN FALSE;
6743:
6744: WHEN e_no_trg_resp THEN
6745: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_TRG_RESP_NOEXIST');
6746: x_validation_error := BSC_APPS.Replace_Token(x_validation_error, 'RESP_ID', TO_CHAR(h_resp));
6747: RETURN FALSE;
6748:
6749: WHEN OTHERS THEN
6750: x_validation_error := SQLERRM;
6839:
6840: RETURN TRUE;
6841: EXCEPTION
6842: WHEN e_src_invalid_version THEN
6843: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_INV_SRC_VERSION');
6844: x_validation_error := BSC_APPS.Replace_Token(x_validation_error, 'VERSION_NUMBER', h_trg_version);
6845: RETURN FALSE;
6846:
6847: WHEN e_trg_invalid_version THEN
6840: RETURN TRUE;
6841: EXCEPTION
6842: WHEN e_src_invalid_version THEN
6843: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_INV_SRC_VERSION');
6844: x_validation_error := BSC_APPS.Replace_Token(x_validation_error, 'VERSION_NUMBER', h_trg_version);
6845: RETURN FALSE;
6846:
6847: WHEN e_trg_invalid_version THEN
6848: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_INV_TRG_VERSION');
6844: x_validation_error := BSC_APPS.Replace_Token(x_validation_error, 'VERSION_NUMBER', h_trg_version);
6845: RETURN FALSE;
6846:
6847: WHEN e_trg_invalid_version THEN
6848: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_INV_TRG_VERSION');
6849: x_validation_error := BSC_APPS.Replace_Token(x_validation_error, 'VERSION_NUMBER', h_trg_version);
6850: RETURN FALSE;
6851:
6852: WHEN OTHERS THEN
6845: RETURN FALSE;
6846:
6847: WHEN e_trg_invalid_version THEN
6848: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_INV_TRG_VERSION');
6849: x_validation_error := BSC_APPS.Replace_Token(x_validation_error, 'VERSION_NUMBER', h_trg_version);
6850: RETURN FALSE;
6851:
6852: WHEN OTHERS THEN
6853: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_NO_SOURCE_SYSTEM');
6849: x_validation_error := BSC_APPS.Replace_Token(x_validation_error, 'VERSION_NUMBER', h_trg_version);
6850: RETURN FALSE;
6851:
6852: WHEN OTHERS THEN
6853: x_validation_error := BSC_APPS.Get_Message('BSC_MIG_NO_SOURCE_SYSTEM');
6854: RETURN FALSE;
6855:
6856: END Validate_System_Versions;
6857:
6979: RETURN TRUE;
6980: END IF;
6981:
6982: -- Write to the log file the invalid tables and kpis
6983: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_FOUND_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
6984: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
6985: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TABLE'),32)||
6986: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TYPE'),
6987: BSC_APPS.OUTPUT_FILE);
6980: END IF;
6981:
6982: -- Write to the log file the invalid tables and kpis
6983: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_FOUND_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
6984: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
6985: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TABLE'),32)||
6986: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TYPE'),
6987: BSC_APPS.OUTPUT_FILE);
6988: BSC_APPS.Write_Line_Log(RPAD('-', 30, '-')||' '||RPAD('-', 30, '-'), BSC_APPS.OUTPUT_FILE);
6981:
6982: -- Write to the log file the invalid tables and kpis
6983: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_FOUND_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
6984: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
6985: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TABLE'),32)||
6986: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TYPE'),
6987: BSC_APPS.OUTPUT_FILE);
6988: BSC_APPS.Write_Line_Log(RPAD('-', 30, '-')||' '||RPAD('-', 30, '-'), BSC_APPS.OUTPUT_FILE);
6989:
6982: -- Write to the log file the invalid tables and kpis
6983: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_FOUND_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
6984: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
6985: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TABLE'),32)||
6986: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TYPE'),
6987: BSC_APPS.OUTPUT_FILE);
6988: BSC_APPS.Write_Line_Log(RPAD('-', 30, '-')||' '||RPAD('-', 30, '-'), BSC_APPS.OUTPUT_FILE);
6989:
6990: h_num_invalid_kpis := 0;
6983: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_FOUND_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
6984: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
6985: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TABLE'),32)||
6986: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TYPE'),
6987: BSC_APPS.OUTPUT_FILE);
6988: BSC_APPS.Write_Line_Log(RPAD('-', 30, '-')||' '||RPAD('-', 30, '-'), BSC_APPS.OUTPUT_FILE);
6989:
6990: h_num_invalid_kpis := 0;
6991: FOR h_i IN 1..h_num_invalid_tables LOOP
6984: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
6985: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TABLE'),32)||
6986: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'TYPE'),
6987: BSC_APPS.OUTPUT_FILE);
6988: BSC_APPS.Write_Line_Log(RPAD('-', 30, '-')||' '||RPAD('-', 30, '-'), BSC_APPS.OUTPUT_FILE);
6989:
6990: h_num_invalid_kpis := 0;
6991: FOR h_i IN 1..h_num_invalid_tables LOOP
6992: BSC_APPS.Write_Line_Log(RPAD(h_invalid_tables(h_i),32)||
6988: BSC_APPS.Write_Line_Log(RPAD('-', 30, '-')||' '||RPAD('-', 30, '-'), BSC_APPS.OUTPUT_FILE);
6989:
6990: h_num_invalid_kpis := 0;
6991: FOR h_i IN 1..h_num_invalid_tables LOOP
6992: BSC_APPS.Write_Line_Log(RPAD(h_invalid_tables(h_i),32)||
6993: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_FACT_TABLE'),
6994: BSC_APPS.OUTPUT_FILE);
6995:
6996: -- Add to the array of invalid kpis the affected indicators
6989:
6990: h_num_invalid_kpis := 0;
6991: FOR h_i IN 1..h_num_invalid_tables LOOP
6992: BSC_APPS.Write_Line_Log(RPAD(h_invalid_tables(h_i),32)||
6993: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_FACT_TABLE'),
6994: BSC_APPS.OUTPUT_FILE);
6995:
6996: -- Add to the array of invalid kpis the affected indicators
6997: h_sql := 'select distinct k.indicator, k.name'||
6990: h_num_invalid_kpis := 0;
6991: FOR h_i IN 1..h_num_invalid_tables LOOP
6992: BSC_APPS.Write_Line_Log(RPAD(h_invalid_tables(h_i),32)||
6993: BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_FACT_TABLE'),
6994: BSC_APPS.OUTPUT_FILE);
6995:
6996: -- Add to the array of invalid kpis the affected indicators
6997: h_sql := 'select distinct k.indicator, k.name'||
6998: ' from bsc_kpi_data_tables@'||g_db_link||' t, bsc_kpis_vl@'||g_db_link||' k'||
7016: END LOOP;
7017: CLOSE h_cursor;
7018: END LOOP;
7019: FOR h_i IN 1..h_num_invalid_dim_tables LOOP
7020: BSC_APPS.Write_Line_Log(RPAD(h_invalid_dim_tables(h_i),32)||
7021: BSC_APPS.Get_Lookup_Value('BSC_UI_BUILDER', 'DIMENTION_TABLE'),
7022: BSC_APPS.OUTPUT_FILE);
7023: END LOOP;
7024: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7017: CLOSE h_cursor;
7018: END LOOP;
7019: FOR h_i IN 1..h_num_invalid_dim_tables LOOP
7020: BSC_APPS.Write_Line_Log(RPAD(h_invalid_dim_tables(h_i),32)||
7021: BSC_APPS.Get_Lookup_Value('BSC_UI_BUILDER', 'DIMENTION_TABLE'),
7022: BSC_APPS.OUTPUT_FILE);
7023: END LOOP;
7024: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7025:
7018: END LOOP;
7019: FOR h_i IN 1..h_num_invalid_dim_tables LOOP
7020: BSC_APPS.Write_Line_Log(RPAD(h_invalid_dim_tables(h_i),32)||
7021: BSC_APPS.Get_Lookup_Value('BSC_UI_BUILDER', 'DIMENTION_TABLE'),
7022: BSC_APPS.OUTPUT_FILE);
7023: END LOOP;
7024: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7025:
7026: -- Now write the affected indicators
7020: BSC_APPS.Write_Line_Log(RPAD(h_invalid_dim_tables(h_i),32)||
7021: BSC_APPS.Get_Lookup_Value('BSC_UI_BUILDER', 'DIMENTION_TABLE'),
7022: BSC_APPS.OUTPUT_FILE);
7023: END LOOP;
7024: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7025:
7026: -- Now write the affected indicators
7027: IF h_num_invalid_kpis > 0 THEN
7028: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'RELATED_OBJECTIVES'), BSC_APPS.OUTPUT_FILE);
7024: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7025:
7026: -- Now write the affected indicators
7027: IF h_num_invalid_kpis > 0 THEN
7028: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'RELATED_OBJECTIVES'), BSC_APPS.OUTPUT_FILE);
7029: BSC_APPS.Write_Line_Log(RPAD('-', 50, '-'), BSC_APPS.OUTPUT_FILE);
7030: FOR h_i IN 1..h_num_invalid_kpis LOOP
7031: BSC_APPS.Write_Line_Log(RPAD(h_invalid_kpis_code(h_i), 12)||h_invalid_kpis_name(h_i), BSC_APPS.OUTPUT_FILE);
7032: END LOOP;
7025:
7026: -- Now write the affected indicators
7027: IF h_num_invalid_kpis > 0 THEN
7028: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'RELATED_OBJECTIVES'), BSC_APPS.OUTPUT_FILE);
7029: BSC_APPS.Write_Line_Log(RPAD('-', 50, '-'), BSC_APPS.OUTPUT_FILE);
7030: FOR h_i IN 1..h_num_invalid_kpis LOOP
7031: BSC_APPS.Write_Line_Log(RPAD(h_invalid_kpis_code(h_i), 12)||h_invalid_kpis_name(h_i), BSC_APPS.OUTPUT_FILE);
7032: END LOOP;
7033: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7027: IF h_num_invalid_kpis > 0 THEN
7028: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'RELATED_OBJECTIVES'), BSC_APPS.OUTPUT_FILE);
7029: BSC_APPS.Write_Line_Log(RPAD('-', 50, '-'), BSC_APPS.OUTPUT_FILE);
7030: FOR h_i IN 1..h_num_invalid_kpis LOOP
7031: BSC_APPS.Write_Line_Log(RPAD(h_invalid_kpis_code(h_i), 12)||h_invalid_kpis_name(h_i), BSC_APPS.OUTPUT_FILE);
7032: END LOOP;
7033: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7034: END IF;
7035:
7029: BSC_APPS.Write_Line_Log(RPAD('-', 50, '-'), BSC_APPS.OUTPUT_FILE);
7030: FOR h_i IN 1..h_num_invalid_kpis LOOP
7031: BSC_APPS.Write_Line_Log(RPAD(h_invalid_kpis_code(h_i), 12)||h_invalid_kpis_name(h_i), BSC_APPS.OUTPUT_FILE);
7032: END LOOP;
7033: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7034: END IF;
7035:
7036: -- Now write invalid dimension objects
7037: IF h_num_invalid_dim_objs > 0 THEN
7034: END IF;
7035:
7036: -- Now write invalid dimension objects
7037: IF h_num_invalid_dim_objs > 0 THEN
7038: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECTS'), BSC_APPS.OUTPUT_FILE);
7039: BSC_APPS.Write_Line_Log(RPAD('-', 50, '-'), BSC_APPS.OUTPUT_FILE);
7040: FOR h_i IN 1..h_num_invalid_dim_objs LOOP
7041: BSC_APPS.Write_Line_Log(h_invalid_dim_objs(h_i), BSC_APPS.OUTPUT_FILE);
7042: END LOOP;
7035:
7036: -- Now write invalid dimension objects
7037: IF h_num_invalid_dim_objs > 0 THEN
7038: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECTS'), BSC_APPS.OUTPUT_FILE);
7039: BSC_APPS.Write_Line_Log(RPAD('-', 50, '-'), BSC_APPS.OUTPUT_FILE);
7040: FOR h_i IN 1..h_num_invalid_dim_objs LOOP
7041: BSC_APPS.Write_Line_Log(h_invalid_dim_objs(h_i), BSC_APPS.OUTPUT_FILE);
7042: END LOOP;
7043: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7037: IF h_num_invalid_dim_objs > 0 THEN
7038: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECTS'), BSC_APPS.OUTPUT_FILE);
7039: BSC_APPS.Write_Line_Log(RPAD('-', 50, '-'), BSC_APPS.OUTPUT_FILE);
7040: FOR h_i IN 1..h_num_invalid_dim_objs LOOP
7041: BSC_APPS.Write_Line_Log(h_invalid_dim_objs(h_i), BSC_APPS.OUTPUT_FILE);
7042: END LOOP;
7043: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7044: END IF;
7045:
7039: BSC_APPS.Write_Line_Log(RPAD('-', 50, '-'), BSC_APPS.OUTPUT_FILE);
7040: FOR h_i IN 1..h_num_invalid_dim_objs LOOP
7041: BSC_APPS.Write_Line_Log(h_invalid_dim_objs(h_i), BSC_APPS.OUTPUT_FILE);
7042: END LOOP;
7043: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7044: END IF;
7045:
7046: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_ACTION_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
7047: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7042: END LOOP;
7043: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7044: END IF;
7045:
7046: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_ACTION_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
7047: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7048: IF h_num_invalid_tables > 0 THEN
7049: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_ACTION1_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
7050: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7043: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7044: END IF;
7045:
7046: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_ACTION_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
7047: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7048: IF h_num_invalid_tables > 0 THEN
7049: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_ACTION1_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
7050: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7051: END IF;
7045:
7046: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_ACTION_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
7047: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7048: IF h_num_invalid_tables > 0 THEN
7049: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_ACTION1_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
7050: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7051: END IF;
7052: IF h_num_invalid_dim_tables > 0 THEN
7053: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_ACTION2_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
7046: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_ACTION_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
7047: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7048: IF h_num_invalid_tables > 0 THEN
7049: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_ACTION1_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
7050: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7051: END IF;
7052: IF h_num_invalid_dim_tables > 0 THEN
7053: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_ACTION2_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
7054: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7049: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_ACTION1_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
7050: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7051: END IF;
7052: IF h_num_invalid_dim_tables > 0 THEN
7053: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_ACTION2_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
7054: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7055: END IF;
7056:
7057: RETURN FALSE;
7050: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7051: END IF;
7052: IF h_num_invalid_dim_tables > 0 THEN
7053: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_ACTION2_INVALID_TABLES'), BSC_APPS.OUTPUT_FILE);
7054: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7055: END IF;
7056:
7057: RETURN FALSE;
7058:
7145: (g_num_invalid_pmf_dim_levels > 0) OR (g_num_no_mig_kpis > 0) THEN
7146:
7147: g_warnings := TRUE;
7148:
7149: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_UPG_PMF_ERROR'), BSC_APPS.OUTPUT_FILE);
7150: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_INSTALL_PROD_PATCH'), BSC_APPS.OUTPUT_FILE);
7151:
7152: IF g_num_no_mig_kpis > 0 THEN
7153: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7146:
7147: g_warnings := TRUE;
7148:
7149: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_UPG_PMF_ERROR'), BSC_APPS.OUTPUT_FILE);
7150: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_INSTALL_PROD_PATCH'), BSC_APPS.OUTPUT_FILE);
7151:
7152: IF g_num_no_mig_kpis > 0 THEN
7153: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7154: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_BACKEND', 'KPI_CODE'), 15)||
7149: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_UPG_PMF_ERROR'), BSC_APPS.OUTPUT_FILE);
7150: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_INSTALL_PROD_PATCH'), BSC_APPS.OUTPUT_FILE);
7151:
7152: IF g_num_no_mig_kpis > 0 THEN
7153: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7154: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_BACKEND', 'KPI_CODE'), 15)||
7155: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'KPI_NAME'), 50),
7156: BSC_UPDATE_LOG.OUTPUT);
7157:
7150: BSC_APPS.Write_Line_Log(BSC_APPS.Get_Message('BSC_MIG_INSTALL_PROD_PATCH'), BSC_APPS.OUTPUT_FILE);
7151:
7152: IF g_num_no_mig_kpis > 0 THEN
7153: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7154: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_BACKEND', 'KPI_CODE'), 15)||
7155: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'KPI_NAME'), 50),
7156: BSC_UPDATE_LOG.OUTPUT);
7157:
7158: h_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
7151:
7152: IF g_num_no_mig_kpis > 0 THEN
7153: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7154: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_BACKEND', 'KPI_CODE'), 15)||
7155: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'KPI_NAME'), 50),
7156: BSC_UPDATE_LOG.OUTPUT);
7157:
7158: h_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
7159: FOR h_i IN 1 .. g_num_no_mig_kpis LOOP
7154: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_BACKEND', 'KPI_CODE'), 15)||
7155: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'KPI_NAME'), 50),
7156: BSC_UPDATE_LOG.OUTPUT);
7157:
7158: h_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
7159: FOR h_i IN 1 .. g_num_no_mig_kpis LOOP
7160: BSC_APPS.Add_Value_Big_In_Cond(1, g_no_mig_kpis(h_i));
7161: END LOOP;
7162:
7156: BSC_UPDATE_LOG.OUTPUT);
7157:
7158: h_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, 'indicator');
7159: FOR h_i IN 1 .. g_num_no_mig_kpis LOOP
7160: BSC_APPS.Add_Value_Big_In_Cond(1, g_no_mig_kpis(h_i));
7161: END LOOP;
7162:
7163: h_sql := 'SELECT indicator, name'||
7164: ' FROM bsc_kpis_vl@'||g_db_link||
7167: LOOP
7168: FETCH h_cursor INTO h_indicator, h_name;
7169: EXIT WHEN h_cursor%NOTFOUND;
7170:
7171: BSC_APPS.Write_Line_Log(RPAD(h_indicator, 15)||h_name,
7172: BSC_UPDATE_LOG.OUTPUT);
7173:
7174: END LOOP;
7175: CLOSE h_cursor;
7175: CLOSE h_cursor;
7176: END IF;
7177:
7178: IF g_num_invalid_pmf_measures > 0 THEN
7179: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7180: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_MEASURE'), 40)||
7181: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'SHORT_NAME'), 35)||
7182: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'APPLICATION_NAME'), 40),
7183: BSC_UPDATE_LOG.OUTPUT);
7176: END IF;
7177:
7178: IF g_num_invalid_pmf_measures > 0 THEN
7179: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7180: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_MEASURE'), 40)||
7181: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'SHORT_NAME'), 35)||
7182: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'APPLICATION_NAME'), 40),
7183: BSC_UPDATE_LOG.OUTPUT);
7184:
7177:
7178: IF g_num_invalid_pmf_measures > 0 THEN
7179: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7180: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_MEASURE'), 40)||
7181: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'SHORT_NAME'), 35)||
7182: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'APPLICATION_NAME'), 40),
7183: BSC_UPDATE_LOG.OUTPUT);
7184:
7185: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'i.short_name');
7178: IF g_num_invalid_pmf_measures > 0 THEN
7179: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7180: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_MEASURE'), 40)||
7181: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'SHORT_NAME'), 35)||
7182: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'APPLICATION_NAME'), 40),
7183: BSC_UPDATE_LOG.OUTPUT);
7184:
7185: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'i.short_name');
7186: FOR h_i IN 1 .. g_num_invalid_pmf_measures LOOP
7181: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'SHORT_NAME'), 35)||
7182: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'APPLICATION_NAME'), 40),
7183: BSC_UPDATE_LOG.OUTPUT);
7184:
7185: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'i.short_name');
7186: FOR h_i IN 1 .. g_num_invalid_pmf_measures LOOP
7187: BSC_APPS.Add_Value_Big_In_Cond(1, g_invalid_pmf_measures(h_i));
7188: END LOOP;
7189:
7183: BSC_UPDATE_LOG.OUTPUT);
7184:
7185: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'i.short_name');
7186: FOR h_i IN 1 .. g_num_invalid_pmf_measures LOOP
7187: BSC_APPS.Add_Value_Big_In_Cond(1, g_invalid_pmf_measures(h_i));
7188: END LOOP;
7189:
7190: -- perf bug#4583017: remove distinct
7191: h_sql := 'SELECT itl.name, i.short_name, f.application_name'||
7201: LOOP
7202: FETCH h_cursor INTO h_name, h_short_name, h_application_name;
7203: EXIT WHEN h_cursor%NOTFOUND;
7204:
7205: BSC_APPS.Write_Line_Log(RPAD(h_name, 40)||
7206: RPAD(h_short_name, 35)||
7207: h_application_name,
7208: BSC_UPDATE_LOG.OUTPUT);
7209: END LOOP;
7210: CLOSE h_cursor;
7211: END IF;
7212:
7213: IF g_num_invalid_pmf_dimensions > 0 THEN
7214: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7215: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_DIMENSION'), 40)||
7216: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'SHORT_NAME'), 35)||
7217: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'APPLICATION_NAME'), 40),
7218: BSC_UPDATE_LOG.OUTPUT);
7211: END IF;
7212:
7213: IF g_num_invalid_pmf_dimensions > 0 THEN
7214: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7215: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_DIMENSION'), 40)||
7216: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'SHORT_NAME'), 35)||
7217: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'APPLICATION_NAME'), 40),
7218: BSC_UPDATE_LOG.OUTPUT);
7219:
7212:
7213: IF g_num_invalid_pmf_dimensions > 0 THEN
7214: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7215: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_DIMENSION'), 40)||
7216: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'SHORT_NAME'), 35)||
7217: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'APPLICATION_NAME'), 40),
7218: BSC_UPDATE_LOG.OUTPUT);
7219:
7220: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'd.short_name');
7213: IF g_num_invalid_pmf_dimensions > 0 THEN
7214: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7215: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_DIMENSION'), 40)||
7216: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'SHORT_NAME'), 35)||
7217: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'APPLICATION_NAME'), 40),
7218: BSC_UPDATE_LOG.OUTPUT);
7219:
7220: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'd.short_name');
7221: FOR h_i IN 1 .. g_num_invalid_pmf_dimensions LOOP
7216: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'SHORT_NAME'), 35)||
7217: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'APPLICATION_NAME'), 40),
7218: BSC_UPDATE_LOG.OUTPUT);
7219:
7220: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'd.short_name');
7221: FOR h_i IN 1 .. g_num_invalid_pmf_dimensions LOOP
7222: BSC_APPS.Add_Value_Big_In_Cond(1, g_invalid_pmf_dimensions(h_i));
7223: END LOOP;
7224:
7218: BSC_UPDATE_LOG.OUTPUT);
7219:
7220: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'd.short_name');
7221: FOR h_i IN 1 .. g_num_invalid_pmf_dimensions LOOP
7222: BSC_APPS.Add_Value_Big_In_Cond(1, g_invalid_pmf_dimensions(h_i));
7223: END LOOP;
7224:
7225: h_sql := 'SELECT DISTINCT dtl.name, d.short_name, f.application_name'||
7226: ' FROM bis_dimensions@'||g_db_link||' d,'||
7233: LOOP
7234: FETCH h_cursor INTO h_name, h_short_name, h_application_name;
7235: EXIT WHEN h_cursor%NOTFOUND;
7236:
7237: BSC_APPS.Write_Line_Log(RPAD(h_name, 40)||
7238: RPAD(h_short_name, 35)||
7239: h_application_name,
7240: BSC_UPDATE_LOG.OUTPUT);
7241: END LOOP;
7242: CLOSE h_cursor;
7243: END IF;
7244:
7245: IF g_num_invalid_pmf_dim_levels > 0 THEN
7246: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7247: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECT'), 40)||
7248: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'SHORT_NAME'), 35)||
7249: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'APPLICATION_NAME'), 40),
7250: BSC_UPDATE_LOG.OUTPUT);
7243: END IF;
7244:
7245: IF g_num_invalid_pmf_dim_levels > 0 THEN
7246: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7247: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECT'), 40)||
7248: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'SHORT_NAME'), 35)||
7249: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'APPLICATION_NAME'), 40),
7250: BSC_UPDATE_LOG.OUTPUT);
7251:
7244:
7245: IF g_num_invalid_pmf_dim_levels > 0 THEN
7246: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7247: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECT'), 40)||
7248: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'SHORT_NAME'), 35)||
7249: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'APPLICATION_NAME'), 40),
7250: BSC_UPDATE_LOG.OUTPUT);
7251:
7252: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'l.short_name');
7245: IF g_num_invalid_pmf_dim_levels > 0 THEN
7246: BSC_APPS.Write_Line_Log(' ', BSC_APPS.OUTPUT_FILE);
7247: BSC_APPS.Write_Line_Log(RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECT'), 40)||
7248: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'SHORT_NAME'), 35)||
7249: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'APPLICATION_NAME'), 40),
7250: BSC_UPDATE_LOG.OUTPUT);
7251:
7252: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'l.short_name');
7253: FOR h_i IN 1 .. g_num_invalid_pmf_dim_levels LOOP
7248: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'SHORT_NAME'), 35)||
7249: RPAD(BSC_APPS.Get_Lookup_Value('BSC_UI_SETUP', 'APPLICATION_NAME'), 40),
7250: BSC_UPDATE_LOG.OUTPUT);
7251:
7252: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'l.short_name');
7253: FOR h_i IN 1 .. g_num_invalid_pmf_dim_levels LOOP
7254: BSC_APPS.Add_Value_Big_In_Cond(1, g_invalid_pmf_dim_levels(h_i));
7255: END LOOP;
7256:
7250: BSC_UPDATE_LOG.OUTPUT);
7251:
7252: h_condition := BSC_APPS.Get_New_Big_In_Cond_Varchar2(1, 'l.short_name');
7253: FOR h_i IN 1 .. g_num_invalid_pmf_dim_levels LOOP
7254: BSC_APPS.Add_Value_Big_In_Cond(1, g_invalid_pmf_dim_levels(h_i));
7255: END LOOP;
7256:
7257: h_sql := 'SELECT DISTINCT ltl.name, l.short_name, f.application_name'||
7258: ' FROM bis_levels@'||g_db_link||' l,'||
7265: LOOP
7266: FETCH h_cursor INTO h_name, h_short_name, h_application_name;
7267: EXIT WHEN h_cursor%NOTFOUND;
7268:
7269: BSC_APPS.Write_Line_Log(RPAD(h_name, 40)||
7270: RPAD(h_short_name, 35)||
7271: h_application_name,
7272: BSC_UPDATE_LOG.OUTPUT);
7273: END LOOP;
7315: FETCH h_cursor INTO h_dimension_short_name;
7316: EXIT WHEN h_cursor%NOTFOUND;
7317:
7318: -- Retrieve the dimension from the source system
7319: BSC_APPS.Write_Line_Log('Migrating existing source dimension '||h_dimension_short_name, BSC_APPS.OUTPUT_FILE);
7320:
7321: h_sql := 'BEGIN BSC_MIGRATION.Retrieve_Dimension@'||g_db_link||'('||
7322: ' p_dimension_short_name => :1'||
7323: ', x_dimension_name => :2'||
7332: OUT h_description, OUT h_application_id,
7333: OUT h_return_status, OUT h_error_msg, OUT h_hide;
7334:
7335: IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7336: BSC_APPS.Write_Line_Log('Error retrieving dimension: '||h_error_msg, BSC_APPS.OUTPUT_FILE);
7337: ELSE
7338: h_dimension_rec.Dimension_Short_Name := h_dimension_short_name;
7339: h_dimension_rec.Dimension_Name := h_dimension_name;
7340: h_dimension_rec.Description := h_description;
7360: );
7361: commit;
7362:
7363: IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7364: BSC_APPS.Write_Line_Log('Error creating dimension: '||h_error_tbl(1).Error_Description, BSC_APPS.OUTPUT_FILE);
7365: END IF;
7366: ELSE
7367: -- Dimension exists, update it
7368: BIS_DIMENSION_PUB.Update_Dimension(
7375: );
7376: commit;
7377:
7378: IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7379: BSC_APPS.Write_Line_Log('Error updating dimension: '||h_error_tbl(1).Error_Description, BSC_APPS.OUTPUT_FILE);
7380: END IF;
7381: END IF;
7382: END IF;
7383: END LOOP;
7444: FETCH h_cursor INTO h_dimension_level_short_name, h_level_source;
7445: EXIT WHEN h_cursor%NOTFOUND;
7446:
7447: -- Retrieve the dimension level from the source system
7448: BSC_APPS.Write_Line_Log('Migrating existing source dimension object '||h_dimension_level_short_name, BSC_APPS.OUTPUT_FILE);
7449:
7450: h_sql := 'BEGIN BSC_MIGRATION.Retrieve_Dimension_Level@'||g_db_link||'('||
7451: ' p_dimension_level_short_name => :1'||
7452: ', x_dimension_short_name => :2'||
7481: OUT h_drill_to_form_function, OUT h_primary_dim,
7482: OUT h_return_status, OUT h_error_msg, OUT h_hide;
7483:
7484: IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7485: BSC_APPS.Write_Line_Log('Error retrieving dimension object: '||h_error_msg, BSC_APPS.OUTPUT_FILE);
7486: ELSE
7487: h_dimension_level_rec.dimension_level_short_name := h_dimension_level_short_name;
7488: h_dimension_level_rec.dimension_short_name := h_dimension_short_name;
7489: h_dimension_level_rec.dimension_name := h_dimension_name;
7510: -- then we do not migrate that dimension level
7511: h_create_level := TRUE;
7512: IF h_level_source = 'PMF' AND (h_level_values_view_name IS NOT NULL) THEN
7513: IF NOT Create_Copy_Of_View_Def(h_level_values_view_name,FALSE) THEN --bug 6004972 No Overwrite version
7514: BSC_APPS.Write_Line_Log('Could not migrate the level values view: '||h_level_values_view_name,
7515: BSC_APPS.OUTPUT_FILE);
7516: h_create_level := FALSE;
7517: ELSE
7518: -- Check if the view has not errors
7511: h_create_level := TRUE;
7512: IF h_level_source = 'PMF' AND (h_level_values_view_name IS NOT NULL) THEN
7513: IF NOT Create_Copy_Of_View_Def(h_level_values_view_name,FALSE) THEN --bug 6004972 No Overwrite version
7514: BSC_APPS.Write_Line_Log('Could not migrate the level values view: '||h_level_values_view_name,
7515: BSC_APPS.OUTPUT_FILE);
7516: h_create_level := FALSE;
7517: ELSE
7518: -- Check if the view has not errors
7519: h_sql := 'select count(*) from '||h_level_values_view_name||' where rownum < :1';
7523: CLOSE h_cursor2;
7524: EXCEPTION
7525: WHEN OTHERS THEN
7526: CLOSE h_cursor2;
7527: BSC_APPS.Write_Line_Log('Level values view has errors: '||h_level_values_view_name,
7528: BSC_APPS.OUTPUT_FILE);
7529: h_create_level := FALSE;
7530: END;
7531: END IF;
7524: EXCEPTION
7525: WHEN OTHERS THEN
7526: CLOSE h_cursor2;
7527: BSC_APPS.Write_Line_Log('Level values view has errors: '||h_level_values_view_name,
7528: BSC_APPS.OUTPUT_FILE);
7529: h_create_level := FALSE;
7530: END;
7531: END IF;
7532: END IF;
7551: );
7552: commit;
7553:
7554: IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7555: BSC_APPS.Write_Line_Log('Error creating dimension object: '||h_error_tbl(1).Error_Description,
7556: BSC_APPS.OUTPUT_FILE);
7557: END IF;
7558: ELSE
7559: -- Dimension level exists, update it
7552: commit;
7553:
7554: IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7555: BSC_APPS.Write_Line_Log('Error creating dimension object: '||h_error_tbl(1).Error_Description,
7556: BSC_APPS.OUTPUT_FILE);
7557: END IF;
7558: ELSE
7559: -- Dimension level exists, update it
7560: BIS_DIMENSION_LEVEL_PUB.Update_Dimension_Level(
7567: );
7568: commit;
7569:
7570: IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7571: BSC_APPS.Write_Line_Log('Error updating dimension level: '||h_error_tbl(1).Error_Description,
7572: BSC_APPS.OUTPUT_FILE);
7573: END IF;
7574: END IF;
7575: END IF;
7568: commit;
7569:
7570: IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7571: BSC_APPS.Write_Line_Log('Error updating dimension level: '||h_error_tbl(1).Error_Description,
7572: BSC_APPS.OUTPUT_FILE);
7573: END IF;
7574: END IF;
7575: END IF;
7576: END IF;
7654: FETCH h_cursor INTO h_measure_short_name;
7655: EXIT WHEN h_cursor%NOTFOUND;
7656:
7657: -- Retrieve the measure from the source system
7658: BSC_APPS.Write_Line_Log('Migrating existing source measure '||h_measure_short_name, BSC_APPS.OUTPUT_FILE);
7659:
7660: h_sql := 'BEGIN BSC_MIGRATION.Retrieve_Measure@'||g_db_link||'('||
7661: ' p_measure_short_name => :1'||
7662: ', x_measure_name => :2'||
7707: OUT h_unit_of_measure_class, OUT h_application_id, OUT h_is_validate,
7708: OUT h_func_area_short_name, OUT h_return_status, OUT h_error_msg;
7709:
7710: IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7711: BSC_APPS.Write_Line_Log('Error retrieving measure: '||h_error_msg, BSC_APPS.OUTPUT_FILE);
7712: ELSE
7713: h_measure_rec.measure_short_name := h_measure_short_name;
7714: h_measure_rec.measure_name := h_measure_name;
7715: h_measure_rec.description := h_description;
7748: IF h_actual_data_source_type = 'AK' THEN
7749: h_region_code := SUBSTR(h_actual_data_source, 1, INSTR(h_actual_data_source, '.') - 1);
7750: IF h_region_code IS NOT NULL THEN
7751: IF NOT Migrate_AK_Region(h_region_code, h_error_msg) THEN
7752: BSC_APPS.Write_Line_Log('Error migrating AK Region '||h_region_code||': '||h_error_msg,
7753: BSC_APPS.OUTPUT_FILE);
7754: END IF;
7755: END IF;
7756: h_region_code := SUBSTR(h_comparison_source, 1, INSTR(h_comparison_source, '.') - 1);
7749: h_region_code := SUBSTR(h_actual_data_source, 1, INSTR(h_actual_data_source, '.') - 1);
7750: IF h_region_code IS NOT NULL THEN
7751: IF NOT Migrate_AK_Region(h_region_code, h_error_msg) THEN
7752: BSC_APPS.Write_Line_Log('Error migrating AK Region '||h_region_code||': '||h_error_msg,
7753: BSC_APPS.OUTPUT_FILE);
7754: END IF;
7755: END IF;
7756: h_region_code := SUBSTR(h_comparison_source, 1, INSTR(h_comparison_source, '.') - 1);
7757: IF h_region_code IS NOT NULL THEN
7755: END IF;
7756: h_region_code := SUBSTR(h_comparison_source, 1, INSTR(h_comparison_source, '.') - 1);
7757: IF h_region_code IS NOT NULL THEN
7758: IF NOT Migrate_AK_Region(h_region_code, h_error_msg) THEN
7759: BSC_APPS.Write_Line_Log('Error migrating AK Region '||h_region_code||': '||h_error_msg,
7760: BSC_APPS.OUTPUT_FILE);
7761: END IF;
7762: END IF;
7763: END IF;
7756: h_region_code := SUBSTR(h_comparison_source, 1, INSTR(h_comparison_source, '.') - 1);
7757: IF h_region_code IS NOT NULL THEN
7758: IF NOT Migrate_AK_Region(h_region_code, h_error_msg) THEN
7759: BSC_APPS.Write_Line_Log('Error migrating AK Region '||h_region_code||': '||h_error_msg,
7760: BSC_APPS.OUTPUT_FILE);
7761: END IF;
7762: END IF;
7763: END IF;
7764:
7764:
7765: -- Enh#4697749 Migrate Form Function of the measure
7766: IF h_function_name IS NOT NULL THEN
7767: IF NOT Migrate_Form_Function(h_function_name, h_error_msg) THEN
7768: BSC_APPS.Write_Line_Log('Error migrating Form Function '||h_function_name||': '||h_error_msg,
7769: BSC_APPS.OUTPUT_FILE);
7770: END IF;
7771: END IF;
7772:
7765: -- Enh#4697749 Migrate Form Function of the measure
7766: IF h_function_name IS NOT NULL THEN
7767: IF NOT Migrate_Form_Function(h_function_name, h_error_msg) THEN
7768: BSC_APPS.Write_Line_Log('Error migrating Form Function '||h_function_name||': '||h_error_msg,
7769: BSC_APPS.OUTPUT_FILE);
7770: END IF;
7771: END IF;
7772:
7773: --check if the dimension level exists in the target
7789: );
7790: commit;
7791:
7792: IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7793: BSC_APPS.Write_Line_Log('Error creating measure: '||h_error_tbl(1).Error_Description,
7794: BSC_APPS.OUTPUT_FILE);
7795: END IF;
7796: ELSE
7797: -- Measure exists, update it
7790: commit;
7791:
7792: IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7793: BSC_APPS.Write_Line_Log('Error creating measure: '||h_error_tbl(1).Error_Description,
7794: BSC_APPS.OUTPUT_FILE);
7795: END IF;
7796: ELSE
7797: -- Measure exists, update it
7798: BIS_MEASURE_PUB.Update_Measure(
7805: );
7806: commit;
7807:
7808: IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7809: BSC_APPS.Write_Line_Log('Error updating measure: '||h_error_tbl(1).Error_Description,
7810: BSC_APPS.OUTPUT_FILE);
7811: END IF;
7812: END IF;
7813: END IF;
7806: commit;
7807:
7808: IF h_return_status <> FND_API.G_RET_STS_SUCCESS THEN
7809: BSC_APPS.Write_Line_Log('Error updating measure: '||h_error_tbl(1).Error_Description,
7810: BSC_APPS.OUTPUT_FILE);
7811: END IF;
7812: END IF;
7813: END IF;
7814: END LOOP;
8136: CURSOR c_columns(p_table varchar2) IS
8137: SELECT column_name
8138: FROM all_tab_columns
8139: WHERE table_name = UPPER(p_table) AND
8140: owner = UPPER(BSC_APPS.BSC_APPS_SCHEMA);
8141:
8142: h_column VARCHAR2(50);
8143: h_batch_column VARCHAR2(50);
8144: h_lst_select VARCHAR2(32000);
8146: h_where_cond VARCHAR2(32000);
8147:
8148: BEGIN
8149:
8150: h_base_message := BSC_APPS.Get_Message('BSC_MIG_DATA');
8151:
8152: h_max_partitions := bsc_dbgen_metadata_reader.get_max_partitions;
8153: h_num_partitions := Get_Num_Partitions(x_base_table);
8154: h_batch_column := UPPER(BSC_DBGEN_STD_METADATA.BSC_BATCH_COLUMN_NAME);
8185: h_table := x_proj_table;
8186: END IF;
8187:
8188: -- Bug4769877: review from here
8189: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
8190: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
8191:
8192: h_lst_insert := null;
8193: h_lst_select := null;
8186: END IF;
8187:
8188: -- Bug4769877: review from here
8189: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', h_table);
8190: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
8191:
8192: h_lst_insert := null;
8193: h_lst_select := null;
8194:
8265: commit;
8266: END IF;
8267: ELSE
8268: -- No difference in the number of partitions. So we can just insert from select, no need to fix batch column
8269: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', x_base_table);
8270: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
8271:
8272: h_sql := 'INSERT /*+ append parallel('||x_base_table||') */ INTO '||x_base_table||
8273: ' SELECT /*+ parallel('||x_base_table||') */ * FROM '||x_base_table||'@'||g_db_link;
8266: END IF;
8267: ELSE
8268: -- No difference in the number of partitions. So we can just insert from select, no need to fix batch column
8269: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', x_base_table);
8270: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
8271:
8272: h_sql := 'INSERT /*+ append parallel('||x_base_table||') */ INTO '||x_base_table||
8273: ' SELECT /*+ parallel('||x_base_table||') */ * FROM '||x_base_table||'@'||g_db_link;
8274: BSC_APPS.Execute_Immediate(h_sql);
8270: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
8271:
8272: h_sql := 'INSERT /*+ append parallel('||x_base_table||') */ INTO '||x_base_table||
8273: ' SELECT /*+ parallel('||x_base_table||') */ * FROM '||x_base_table||'@'||g_db_link;
8274: BSC_APPS.Execute_Immediate(h_sql);
8275: commit;
8276:
8277: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', x_proj_table);
8278: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
8273: ' SELECT /*+ parallel('||x_base_table||') */ * FROM '||x_base_table||'@'||g_db_link;
8274: BSC_APPS.Execute_Immediate(h_sql);
8275: commit;
8276:
8277: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', x_proj_table);
8278: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
8279:
8280: h_sql := 'INSERT /*+ append parallel('||x_proj_table||') */ INTO '||x_proj_table||
8281: ' SELECT /*+ parallel('||x_proj_table||') */ * FROM '||x_proj_table||'@'||g_db_link;
8274: BSC_APPS.Execute_Immediate(h_sql);
8275: commit;
8276:
8277: h_message := BSC_APPS.Replace_Token(h_base_message, 'TABLE', x_proj_table);
8278: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
8279:
8280: h_sql := 'INSERT /*+ append parallel('||x_proj_table||') */ INTO '||x_proj_table||
8281: ' SELECT /*+ parallel('||x_proj_table||') */ * FROM '||x_proj_table||'@'||g_db_link;
8282: BSC_APPS.Execute_Immediate(h_sql);
8278: BSC_APPS.Write_Line_Log(h_message, BSC_APPS.OUTPUT_FILE);
8279:
8280: h_sql := 'INSERT /*+ append parallel('||x_proj_table||') */ INTO '||x_proj_table||
8281: ' SELECT /*+ parallel('||x_proj_table||') */ * FROM '||x_proj_table||'@'||g_db_link;
8282: BSC_APPS.Execute_Immediate(h_sql);
8283: commit;
8284: END IF;
8285:
8286: RETURN TRUE;
8286: RETURN TRUE;
8287:
8288: EXCEPTION
8289: WHEN e_unexpected_error THEN
8290: BSC_MESSAGE.Add (x_message => BSC_APPS.Get_Message('BSC_UNEXPECTED_ERROR'),
8291: x_source => 'BSC_MIGRATION.Migrate_BTable_With_Partitions');
8292: RETURN FALSE;
8293:
8294: WHEN OTHERS THEN
8796: h_sql VARCHAR2(32000);
8797: h_source_user_id NUMBER;
8798: h_def_user VARCHAR2(200);
8799: BEGIN
8800: --Fix bug#4873385 if the user BSC_APPS.apps_user_id does not exists in the source system
8801: -- then we lock the source system with the user ANONYMOUS
8802: h_def_user := 'ANONYMOUS';
8803:
8804: h_sql := 'select s.user_id'||
8803:
8804: h_sql := 'select s.user_id'||
8805: ' from fnd_user@'||g_db_link||' s, fnd_user t'||
8806: ' where t.user_name = s.user_name and t.user_id = :1';
8807: OPEN h_cursor FOR h_sql USING BSC_APPS.apps_user_id;
8808: FETCH h_cursor INTO h_source_user_id;
8809: IF h_cursor%NOTFOUND THEN
8810: h_source_user_id := NULL;
8811: END IF;
9581: CURSOR c_column IS
9582: SELECT column_name
9583: FROM all_tab_columns
9584: WHERE table_name = UPPER(l_table_name)
9585: AND OWNER = UPPER(BSC_APPS.BSC_APPS_SCHEMA);
9586:
9587:
9588:
9589: BEGIN
9621: , x_msg_count => x_msg_count
9622: , x_msg_data => x_msg_data
9623: );
9624: IF (x_return_status IS NOT NULL AND x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
9625: BSC_APPS.Write_Line_Log('Error Deleting SimTree Data ', BSC_APPS.OUTPUT_FILE);
9626: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
9627: END IF;
9628: END IF;
9629:
9633: ,x_Table_Number => l_Table_Number
9634: ,x_kpi_metadata_tables => l_kpi_metadata_tables
9635: );
9636:
9637: BSC_APPS.Write_Line_Log('Migrating Sim Data [p_Trg_indicator =' ||p_Trg_indicator||']' , BSC_APPS.OUTPUT_FILE);
9638:
9639: FOR h_i IN 1..l_Table_Number LOOP
9640: IF l_kpi_metadata_tables(h_i).table_type <> BSC_SIMULATION_VIEW_PVT.C_AK_TABLE AND
9641: l_kpi_metadata_tables(h_i).table_type <> BSC_SIMULATION_VIEW_PVT.C_FORM_TABLE THEN
9844:
9845: --Now we will migrate the AK stuff here
9846: IF p_Region_Code IS NOT NULL THEN
9847: -- By design there is a form function and ak region called as the short name
9848: BSC_APPS.Write_Line_Log('Migrating report '||p_Old_Region_Code, BSC_APPS.OUTPUT_FILE);
9849: Synchronize_AK_BSC_Metadata (
9850: p_commit => FND_API.G_FALSE
9851: ,p_Src_indicator => p_Src_indicator
9852: ,p_Trg_indicator => p_Trg_indicator