eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BSC_UPDATE_DIM dependencies on BSC_UPDATE_UTIL

Line 10: x_relation_cols IN BSC_UPDATE_UTIL.t_array_of_varchar2,

6: +============================================================================*/
7: FUNCTION Any_Item_Changed_Any_Relation(
8: x_dimension_table IN VARCHAR2,
9: x_temp_table IN VARCHAR2,
10: x_relation_cols IN BSC_UPDATE_UTIL.t_array_of_varchar2,
11: x_num_relation_cols IN NUMBER
12: ) RETURN BOOLEAN IS
13:
14: h_i NUMBER;

Line 71: l_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

67: l_short_name VARCHAR2(50);
68: l_table_owner VARCHAR2(50);
69: l_date_tracked_dim VARCHAR2(5);
70:
71: l_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
72: l_src_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
73: l_num_parent_columns NUMBER;
74:
75: l_lst_parent_cols VARCHAR2(8000);

Line 72: l_src_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

68: l_table_owner VARCHAR2(50);
69: l_date_tracked_dim VARCHAR2(5);
70:
71: l_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
72: l_src_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
73: l_num_parent_columns NUMBER;
74:
75: l_lst_parent_cols VARCHAR2(8000);
76: l_lst_cols_desc VARCHAR2(8000);

Line 107: BSC_UPDATE_UTIL.Make_Lst_Description(l_parent_columns, l_num_parent_columns, 'VARCHAR2(400)');

103:
104: l_lst_cols_desc := 'USER_CODE VARCHAR2(400),CODE VARCHAR2(400)';
105: IF l_num_parent_columns > 0 THEN
106: l_lst_cols_desc := l_lst_cols_desc||', '||
107: BSC_UPDATE_UTIL.Make_Lst_Description(l_parent_columns, l_num_parent_columns, 'VARCHAR2(400)');
108: END IF;
109: IF l_date_tracked_dim = 'YES' THEN
110: l_lst_cols_desc := l_lst_cols_desc||', EFFECTIVE_START_DATE DATE, EFFECTIVE_END_DATE DATE';
111: END IF;

Line 114: --if not BSC_UPDATE_UTIL.Drop_Table(l_table_name) then

110: l_lst_cols_desc := l_lst_cols_desc||', EFFECTIVE_START_DATE DATE, EFFECTIVE_END_DATE DATE';
111: END IF;
112:
113: --remove
114: --if not BSC_UPDATE_UTIL.Drop_Table(l_table_name) then
115: --null;
116: --end if;
117: -- Create the table, if it does not exists
118: IF NOT BSC_APPS.Table_Exists(l_table_name) THEN

Line 159: l_sql := l_sql||','||BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(l_parent_columns, l_num_parent_columns);

155: l_sql := l_sql||' SEQUENCE,';
156: END IF;
157: l_sql := l_sql||' PRIMARY KEY, ROWID(CODE';
158: IF l_num_parent_columns > 0 THEN
159: l_sql := l_sql||','||BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(l_parent_columns, l_num_parent_columns);
160: END IF;
161: l_sql := l_sql||')';
162: l_sql := l_sql||' INCLUDING NEW VALUES';
163: if bsc_im_utils.g_debug then

Line 243: h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;

239:
240: e_unexpected_error EXCEPTION;
241:
242: h_table_name VARCHAR2(30);
243: h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;
244: h_num_columns NUMBER;
245: h_i NUMBER;
246: h_tablespace VARCHAR2(80);
247: h_idx_tablespace VARCHAR2(80);

Line 268: IF NOT BSC_UPDATE_UTIL.Create_Permanent_Table(h_table_name, h_table_columns, h_num_columns,

264: h_table_columns(h_num_columns).data_type := 'VARCHAR2';
265: h_table_columns(h_num_columns).data_size := 400;
266: h_table_columns(h_num_columns).add_to_index := 'N';
267: -- Fix bug#5121276 this table needs to be created as a permanent table
268: IF NOT BSC_UPDATE_UTIL.Create_Permanent_Table(h_table_name, h_table_columns, h_num_columns,
269: h_tablespace, h_idx_tablespace) THEN
270: RAISE e_unexpected_error;
271: END IF;
272:

Line 288: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

284: h_table_columns(h_num_columns).column_name := 'CODE';
285: h_table_columns(h_num_columns).data_type := 'VARCHAR2';
286: h_table_columns(h_num_columns).data_size := 400;
287: h_table_columns(h_num_columns).add_to_index := 'N';
288: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
289: RAISE e_unexpected_error;
290: END IF;
291:
292: -- BSC_AW_TMP_DENORM

Line 306: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

302: h_table_columns(h_num_columns).column_name := 'PARENT_VALUE';
303: h_table_columns(h_num_columns).data_type := 'VARCHAR2';
304: h_table_columns(h_num_columns).data_size := 400;
305: h_table_columns(h_num_columns).add_to_index := 'N';
306: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
307: RAISE e_unexpected_error;
308: END IF;
309:
310: -- BSC_AW_REC_DIM_HIER_CHANGE

Line 334: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

330: h_table_columns(h_num_columns).column_name := 'HIER_CHANGE_DATE';
331: h_table_columns(h_num_columns).data_type := 'DATE';
332: h_table_columns(h_num_columns).data_size := NULL;
333: h_table_columns(h_num_columns).add_to_index := 'N';
334: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
335: RAISE e_unexpected_error;
336: END IF;
337:
338: RETURN TRUE;

Line 342: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),

338: RETURN TRUE;
339:
340: EXCEPTION
341: WHEN e_unexpected_error THEN
342: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
343: x_source => 'BSC_UPDATE_DIM.Create_AW_Dim_Temp_Tables');
344: RETURN FALSE;
345:
346: WHEN OTHERS THEN

Line 375: h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;

371:
372: e_unexpected_error EXCEPTION;
373:
374: h_table_name VARCHAR2(30);
375: h_table_columns BSC_UPDATE_UTIL.t_array_temp_table_cols;
376: h_num_columns NUMBER;
377: h_i NUMBER;
378: h_tablespace VARCHAR2(80);
379: h_idx_tablespace VARCHAR2(80);

Line 416: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

412: h_table_columns(h_num_columns).column_name := 'EFFECTIVE_END_DATE';
413: h_table_columns(h_num_columns).data_type := 'DATE';
414: h_table_columns(h_num_columns).data_size := NULL;
415: h_table_columns(h_num_columns).add_to_index := 'N';
416: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
417: RAISE e_unexpected_error;
418: END IF;
419:
420: -- BSC_TMP_DBI_DIM_ADD

Line 429: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

425: h_table_columns(h_num_columns).column_name := 'USER_CODE';
426: h_table_columns(h_num_columns).data_type := 'VARCHAR2';
427: h_table_columns(h_num_columns).data_size := 400;
428: h_table_columns(h_num_columns).add_to_index := 'Y';
429: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
430: RAISE e_unexpected_error;
431: END IF;
432:
433: -- BSC_TMP_DBI_DIM_DEL (Note this table has the same strucutre of the previouos table)

Line 435: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

431: END IF;
432:
433: -- BSC_TMP_DBI_DIM_DEL (Note this table has the same strucutre of the previouos table)
434: h_table_name := 'BSC_TMP_DBI_DIM_DEL';
435: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
436: RAISE e_unexpected_error;
437: END IF;
438:
439: -- BSC_OBJECT_REFRESH_LOG

Line 464: IF NOT BSC_UPDATE_UTIL.Create_Permanent_Table(h_table_name, h_table_columns, h_num_columns,

460: h_table_columns(h_num_columns).column_name := 'REFRESH_END_TIME';
461: h_table_columns(h_num_columns).data_type := 'DATE';
462: h_table_columns(h_num_columns).data_size := NULL;
463: h_table_columns(h_num_columns).add_to_index := 'N';
464: IF NOT BSC_UPDATE_UTIL.Create_Permanent_Table(h_table_name, h_table_columns, h_num_columns,
465: h_tablespace, h_idx_tablespace) THEN
466: RAISE e_unexpected_error;
467: END IF;
468:

Line 494: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN

490: h_table_columns(h_num_columns).column_name := 'PARENT_LEVEL';
491: h_table_columns(h_num_columns).data_type := 'NUMBER';
492: h_table_columns(h_num_columns).data_size := NULL;
493: h_table_columns(h_num_columns).add_to_index := 'N';
494: IF NOT BSC_UPDATE_UTIL.Create_Global_Temp_Table(h_table_name, h_table_columns, h_num_columns) THEN
495: RAISE e_unexpected_error;
496: END IF;
497:
498: RETURN TRUE;

Line 502: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),

498: RETURN TRUE;
499:
500: EXCEPTION
501: WHEN e_unexpected_error THEN
502: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
503: x_source => 'BSC_UPDATE_DIM.Create_Dbi_Dim_Temp_Tables');
504: RETURN FALSE;
505:
506: WHEN OTHERS THEN

Line 518: x_deleted_codes IN BSC_UPDATE_UTIL.t_array_of_number,

514: | FUNCTION Delete_Codes_Cascade
515: +============================================================================*/
516: FUNCTION Delete_Codes_Cascade(
517: x_dim_table IN VARCHAR2,
518: x_deleted_codes IN BSC_UPDATE_UTIL.t_array_of_number,
519: x_num_deleted_codes IN NUMBER
520: ) RETURN BOOLEAN IS
521:
522: e_unexpected_error EXCEPTION;

Line 556: h_deleted_codes BSC_UPDATE_UTIL.t_array_of_number;

552:
553: h_condition VARCHAR2(32700);
554: h_i NUMBER;
555:
556: h_deleted_codes BSC_UPDATE_UTIL.t_array_of_number;
557:
558: -- BSC-BIS-DIMENSIONS
559: -- MN dimension can be created in BSC to store MN relations between BIS dimensions.
560: -- To spport NUMBER or VARCHAR2 I will change the type of this arrays to varchar2.

Line 561: h_deleted_codes1 BSC_UPDATE_UTIL.t_array_of_varchar2;

557:
558: -- BSC-BIS-DIMENSIONS
559: -- MN dimension can be created in BSC to store MN relations between BIS dimensions.
560: -- To spport NUMBER or VARCHAR2 I will change the type of this arrays to varchar2.
561: h_deleted_codes1 BSC_UPDATE_UTIL.t_array_of_varchar2;
562: h_deleted_codes2 BSC_UPDATE_UTIL.t_array_of_varchar2;
563: h_num_deleted_codes NUMBER;
564:
565: h_code NUMBER;

Line 562: h_deleted_codes2 BSC_UPDATE_UTIL.t_array_of_varchar2;

558: -- BSC-BIS-DIMENSIONS
559: -- MN dimension can be created in BSC to store MN relations between BIS dimensions.
560: -- To spport NUMBER or VARCHAR2 I will change the type of this arrays to varchar2.
561: h_deleted_codes1 BSC_UPDATE_UTIL.t_array_of_varchar2;
562: h_deleted_codes2 BSC_UPDATE_UTIL.t_array_of_varchar2;
563: h_num_deleted_codes NUMBER;
564:
565: h_code NUMBER;
566: h_code1 NUMBER;

Line 703: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

699: END LOOP;
700:
701: h_sql := 'DELETE FROM '||x_dim_table||
702: ' WHERE '||h_condition;
703: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
704:
705: RETURN TRUE;
706:
707: EXCEPTION

Line 709: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMREC_DELETE_FAILED'),

705: RETURN TRUE;
706:
707: EXCEPTION
708: WHEN e_unexpected_error THEN
709: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMREC_DELETE_FAILED'),
710: x_source => 'BSC_UPDATE_BASE.Delete_Codes_Cascade');
711: RETURN FALSE;
712:
713: WHEN OTHERS THEN

Line 728: x_deleted_codes1 IN BSC_UPDATE_UTIL.t_array_of_varchar2,

724: FUNCTION Delete_Codes_CascadeMN(
725: x_dim_table IN VARCHAR2,
726: x_key_column1 IN VARCHAR2,
727: x_key_column2 IN VARCHAR2,
728: x_deleted_codes1 IN BSC_UPDATE_UTIL.t_array_of_varchar2,
729: x_deleted_codes2 IN BSC_UPDATE_UTIL.t_array_of_varchar2,
730: x_num_deleted_codes IN NUMBER
731: ) RETURN BOOLEAN IS
732:

Line 729: x_deleted_codes2 IN BSC_UPDATE_UTIL.t_array_of_varchar2,

725: x_dim_table IN VARCHAR2,
726: x_key_column1 IN VARCHAR2,
727: x_key_column2 IN VARCHAR2,
728: x_deleted_codes1 IN BSC_UPDATE_UTIL.t_array_of_varchar2,
729: x_deleted_codes2 IN BSC_UPDATE_UTIL.t_array_of_varchar2,
730: x_num_deleted_codes IN NUMBER
731: ) RETURN BOOLEAN IS
732:
733: h_condition VARCHAR2(32700);

Line 787: BEGIN BSC_UPDATE_UTIL.Execute_Immediate(h_sql); EXCEPTION WHEN OTHERS THEN NULL; END;

783: h_sql := 'DELETE FROM '||h_system_table||
784: ' WHERE '||h_condition;
785: --Fix bug#5060236 B table may not exists, GDB may have fail in the middle
786: -- We can ignore error if the table does not exists
787: BEGIN BSC_UPDATE_UTIL.Execute_Immediate(h_sql); EXCEPTION WHEN OTHERS THEN NULL; END;
788:
789: -- ENH_B_TABLES_PERF: with the new strategy the B table may have a projection table.
790: -- We need to delete rows from the projection table too.
791: h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_system_table);

Line 797: BEGIN BSC_UPDATE_UTIL.Execute_Immediate(h_sql); EXCEPTION WHEN OTHERS THEN NULL; END;

793: h_sql := 'DELETE FROM '||h_proj_tbl_name||
794: ' WHERE '||h_condition;
795: --Fix bug#5060236 B table may not exists, GDB may have fail in the middle
796: -- We can ignore error if the table does not exists
797: BEGIN BSC_UPDATE_UTIL.Execute_Immediate(h_sql); EXCEPTION WHEN OTHERS THEN NULL; END;
798: END IF;
799:
800: FETCH c_system_tables INTO h_system_table;
801: END LOOP;

Line 807: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

803:
804: -- Delete from dimension table
805: h_sql := 'DELETE FROM '||x_dim_table||
806: ' WHERE '||h_condition;
807: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
808:
809: RETURN TRUE;
810:
811: EXCEPTION

Line 864: BEGIN BSC_UPDATE_UTIL.Execute_Immediate(h_sql); EXCEPTION WHEN OTHERS THEN NULL; END;

860: h_sql := 'DELETE FROM '||h_table_name||
861: ' WHERE '||x_condition;
862: --Fix bug#5060236 B table may not exists, GDB may have fail in the middle
863: -- We can ignore error if the table does not exists
864: BEGIN BSC_UPDATE_UTIL.Execute_Immediate(h_sql); EXCEPTION WHEN OTHERS THEN NULL; END;
865:
866: -- ENH_B_TABLES_PERF: with the new strategy the B table may have a projection table.
867: -- We need to delete rows from the projection table too
868: h_proj_tbl_name := BSC_UPDATE_BASE_V2.Get_Base_Proj_Tbl_Name(h_table_name);

Line 874: BEGIN BSC_UPDATE_UTIL.Execute_Immediate(h_sql); EXCEPTION WHEN OTHERS THEN NULL; END;

870: h_sql := 'DELETE FROM '||h_proj_tbl_name||
871: ' WHERE '||x_condition;
872: --Fix bug#5060236 B table may not exists, GDB may have fail in the middle
873: -- We can ignore error if the table does not exists
874: BEGIN BSC_UPDATE_UTIL.Execute_Immediate(h_sql); EXCEPTION WHEN OTHERS THEN NULL; END;
875: END IF;
876:
877: FETCH c_affected_tables INTO h_table_name;
878: END LOOP;

Line 968: BSC_UPDATE_UTIL.Execute_Immediate('DELETE BSC_AW_TMP_DENORM');

964: COMMIT;
965:
966: -- AW_INTEGRATION: We need to save the current denorm table data int temp table BSC_AW_TMP_DENORM
967: IF l_dim_for_aw_kpi THEN
968: BSC_UPDATE_UTIL.Execute_Immediate('DELETE BSC_AW_TMP_DENORM');
969: l_sql := 'INSERT INTO BSC_AW_TMP_DENORM (CHILD_VALUE, PARENT_VALUE)'||
970: ' SELECT '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
971: ' FROM '||l_dbi_dim_data.denorm_table;
972: EXECUTE IMMEDIATE l_sql;

Line 976: BSC_UPDATE_UTIL.Truncate_Table(l_dbi_dim_data.denorm_table);

972: EXECUTE IMMEDIATE l_sql;
973: COMMIT;
974: END IF;
975:
976: BSC_UPDATE_UTIL.Truncate_Table(l_dbi_dim_data.denorm_table);
977:
978: FOR l_current_level IN 1..l_dbi_dim_data.top_n_levels LOOP
979: l_num_ids_this_level := 0;
980:

Line 1061: l_ids_this_level BSC_UPDATE_UTIL.t_array_of_number;

1057:
1058: TYPE t_cursor IS REF CURSOR;
1059: l_cursor t_cursor;
1060:
1061: l_ids_this_level BSC_UPDATE_UTIL.t_array_of_number;
1062: l_num_ids_this_level NUMBER;
1063: l_ids BSC_UPDATE_UTIL.t_array_of_number;
1064: l_num_ids NUMBER;
1065: l_current_level NUMBER;

Line 1063: l_ids BSC_UPDATE_UTIL.t_array_of_number;

1059: l_cursor t_cursor;
1060:
1061: l_ids_this_level BSC_UPDATE_UTIL.t_array_of_number;
1062: l_num_ids_this_level NUMBER;
1063: l_ids BSC_UPDATE_UTIL.t_array_of_number;
1064: l_num_ids NUMBER;
1065: l_current_level NUMBER;
1066: l_id NUMBER;
1067: l_where_level VARCHAR2(32000);

Line 1128: BSC_UPDATE_UTIL.Execute_Immediate('DELETE BSC_AW_TMP_DENORM');

1124: COMMIT;
1125:
1126: -- AW_INTEGRATION: We need to save the current denorm table data int temp table BSC_AW_TMP_DENORM
1127: IF l_dim_for_aw_kpi THEN
1128: BSC_UPDATE_UTIL.Execute_Immediate('DELETE BSC_AW_TMP_DENORM');
1129: l_sql := 'INSERT INTO BSC_AW_TMP_DENORM (CHILD_VALUE, PARENT_VALUE)'||
1130: ' SELECT '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
1131: ' FROM '||l_dbi_dim_data.denorm_table;
1132: EXECUTE IMMEDIATE l_sql;

Line 1136: BSC_UPDATE_UTIL.Truncate_Table(l_dbi_dim_data.denorm_table);

1132: EXECUTE IMMEDIATE l_sql;
1133: COMMIT;
1134: END IF;
1135:
1136: BSC_UPDATE_UTIL.Truncate_Table(l_dbi_dim_data.denorm_table);
1137:
1138: l_src_condition := 'NVL(node, '||l_dbi_dim_data.child_col_src||') = '||l_dbi_dim_data.child_col_src;
1139:
1140: FOR l_current_level IN 1..l_dbi_dim_data.top_n_levels LOOP

Line 1223: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),

1219: RETURN TRUE;
1220:
1221: EXCEPTION
1222: WHEN e_unexpected_error THEN
1223: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
1224: x_source => 'BSC_UPDATE_DIM.Denorm_Eni_Item_Itm_Cat');
1225: RETURN FALSE;
1226:
1227: WHEN OTHERS THEN

Line 1300: BSC_UPDATE_UTIL.Execute_Immediate('DELETE BSC_AW_TMP_DENORM');

1296: COMMIT;
1297:
1298: -- AW_INTEGRATION: We need to save the current denorm table data int temp table BSC_AW_TMP_DENORM
1299: IF l_dim_for_aw_kpi THEN
1300: BSC_UPDATE_UTIL.Execute_Immediate('DELETE BSC_AW_TMP_DENORM');
1301: l_sql := 'INSERT INTO BSC_AW_TMP_DENORM (CHILD_VALUE, PARENT_VALUE)'||
1302: ' SELECT '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
1303: ' FROM '||l_dbi_dim_data.denorm_table;
1304: EXECUTE IMMEDIATE l_sql;

Line 1308: BSC_UPDATE_UTIL.Truncate_Table(l_dbi_dim_data.denorm_table);

1304: EXECUTE IMMEDIATE l_sql;
1305: COMMIT;
1306: END IF;
1307:
1308: BSC_UPDATE_UTIL.Truncate_Table(l_dbi_dim_data.denorm_table);
1309:
1310: l_lst_cols := l_dbi_dim_data.parent_col||', '||l_dbi_dim_data.child_col||
1311: ', '||l_dbi_dim_data.parent_level_col;
1312:

Line 1373: l_ids_this_level BSC_UPDATE_UTIL.t_array_of_number;

1369:
1370: TYPE t_cursor IS REF CURSOR;
1371: l_cursor t_cursor;
1372:
1373: l_ids_this_level BSC_UPDATE_UTIL.t_array_of_number;
1374: l_num_ids_this_level NUMBER;
1375: l_ids BSC_UPDATE_UTIL.t_array_of_number;
1376: l_num_ids NUMBER;
1377: l_current_level NUMBER;

Line 1375: l_ids BSC_UPDATE_UTIL.t_array_of_number;

1371: l_cursor t_cursor;
1372:
1373: l_ids_this_level BSC_UPDATE_UTIL.t_array_of_number;
1374: l_num_ids_this_level NUMBER;
1375: l_ids BSC_UPDATE_UTIL.t_array_of_number;
1376: l_num_ids NUMBER;
1377: l_current_level NUMBER;
1378: l_id NUMBER;
1379: l_where_level VARCHAR2(32000);

Line 1438: BSC_UPDATE_UTIL.Execute_Immediate('DELETE BSC_AW_TMP_DENORM');

1434: COMMIT;
1435:
1436: -- AW_INTEGRATION: We need to save the current denorm table data int temp table BSC_AW_TMP_DENORM
1437: IF l_dim_for_aw_kpi THEN
1438: BSC_UPDATE_UTIL.Execute_Immediate('DELETE BSC_AW_TMP_DENORM');
1439: l_sql := 'INSERT INTO BSC_AW_TMP_DENORM (CHILD_VALUE, PARENT_VALUE)'||
1440: ' SELECT '||l_dbi_dim_data.child_col||', '||l_dbi_dim_data.parent_col||
1441: ' FROM '||l_dbi_dim_data.denorm_table;
1442: EXECUTE IMMEDIATE l_sql;

Line 1446: BSC_UPDATE_UTIL.Truncate_Table(l_dbi_dim_data.denorm_table);

1442: EXECUTE IMMEDIATE l_sql;
1443: COMMIT;
1444: END IF;
1445:
1446: BSC_UPDATE_UTIL.Truncate_Table(l_dbi_dim_data.denorm_table);
1447:
1448: FOR l_current_level IN 1..l_dbi_dim_data.top_n_levels LOOP
1449: l_num_ids_this_level := 0;
1450: l_ids_this_level.delete;

Line 1530: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),

1526: RETURN TRUE;
1527:
1528: EXCEPTION
1529: WHEN e_unexpected_error THEN
1530: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
1531: x_source => 'BSC_UPDATE_DIM.Denorm_Pji_Organizations');
1532: RETURN FALSE;
1533:
1534: WHEN OTHERS THEN

Line 1638: x_aux_fields IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2

1634: | FUNCTION Get_Aux_Fields_Dim_Table
1635: +============================================================================*/
1636: FUNCTION Get_Aux_Fields_Dim_Table(
1637: x_dim_table IN VARCHAR2,
1638: x_aux_fields IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
1639: ) RETURN NUMBER IS
1640:
1641: TYPE t_cursor IS REF CURSOR;
1642:

Line 1688: x_child_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2

1684: | FUNCTION Get_Child_Dimensions
1685: +============================================================================*/
1686: FUNCTION Get_Child_Dimensions(
1687: x_dimension_table IN VARCHAR2,
1688: x_child_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
1689: ) RETURN NUMBER IS
1690:
1691: h_num_child_dimensions NUMBER;
1692:

Line 1754: x_parent_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,

1750: | FUNCTION Get_Dbi_Dim_Parent_Columns
1751: +============================================================================*/
1752: FUNCTION Get_Dbi_Dim_Parent_Columns(
1753: x_dim_short_name IN VARCHAR2,
1754: x_parent_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
1755: x_src_parent_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
1756: ) RETURN NUMBER IS
1757:
1758: CURSOR c_parent_cols IS

Line 1755: x_src_parent_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2

1751: +============================================================================*/
1752: FUNCTION Get_Dbi_Dim_Parent_Columns(
1753: x_dim_short_name IN VARCHAR2,
1754: x_parent_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
1755: x_src_parent_columns IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
1756: ) RETURN NUMBER IS
1757:
1758: CURSOR c_parent_cols IS
1759: SELECT p.level_pk_col

Line 1852: x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,

1848: /*===========================================================================+
1849: | FUNCTION Get_Dbi_Dims_Kpis
1850: +============================================================================*/
1851: FUNCTION Get_Dbi_Dims_Kpis(
1852: x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
1853: x_num_indicators IN NUMBER,
1854: x_dbi_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
1855: x_num_dbi_dimensions IN OUT NOCOPY NUMBER
1856: ) RETURN BOOLEAN IS

Line 1854: x_dbi_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,

1850: +============================================================================*/
1851: FUNCTION Get_Dbi_Dims_Kpis(
1852: x_indicators IN BSC_UPDATE_UTIL.t_array_of_number,
1853: x_num_indicators IN NUMBER,
1854: x_dbi_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
1855: x_num_dbi_dimensions IN OUT NOCOPY NUMBER
1856: ) RETURN BOOLEAN IS
1857:
1858: h_where_indics VARCHAR2(32000);

Line 1914: x_deleted_records IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number

1910: +============================================================================*/
1911: FUNCTION Get_Deleted_Records(
1912: x_dimension_table IN VARCHAR2,
1913: x_temp_table IN VARCHAR2,
1914: x_deleted_records IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_number
1915: ) RETURN NUMBER IS
1916:
1917: h_num_deleted_records NUMBER;
1918: h_sql VARCHAR2(32700);

Line 2042: x_parent_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,

2038: | FUNCTION Get_Info_Parents_Dimensions
2039: +============================================================================*/
2040: FUNCTION Get_Info_Parents_Dimensions(
2041: x_dim_table IN VARCHAR2,
2042: x_parent_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2043: x_parent_keys IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
2044: ) RETURN NUMBER IS
2045:
2046: TYPE t_cursor IS REF CURSOR;

Line 2043: x_parent_keys IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2

2039: +============================================================================*/
2040: FUNCTION Get_Info_Parents_Dimensions(
2041: x_dim_table IN VARCHAR2,
2042: x_parent_tables IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
2043: x_parent_keys IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
2044: ) RETURN NUMBER IS
2045:
2046: TYPE t_cursor IS REF CURSOR;
2047:

Line 2162: x_parent_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2

2158: | FUNCTION Get_Parent_Dimensions
2159: +============================================================================*/
2160: FUNCTION Get_Parent_Dimensions(
2161: x_dimension_table IN VARCHAR2,
2162: x_parent_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
2163: ) RETURN NUMBER IS
2164:
2165: h_num_parent_dimensions NUMBER;
2166:

Line 2227: x_relation_cols IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2

2223: | FUNCTION Get_Relation_Cols
2224: +============================================================================*/
2225: FUNCTION Get_Relation_Cols(
2226: x_dimension_table IN VARCHAR2,
2227: x_relation_cols IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2
2228: ) RETURN NUMBER IS
2229:
2230: TYPE t_cursor IS REF CURSOR;
2231:

Line 2282: l_ids BSC_UPDATE_UTIL.t_array_of_number;

2278:
2279: l_id NUMBER;
2280: l_value VARCHAR2(400);
2281:
2282: l_ids BSC_UPDATE_UTIL.t_array_of_number;
2283: l_values BSC_UPDATE_UTIL.t_array_of_varchar2;
2284: l_num_ids NUMBER;
2285: l_i NUMBER;
2286: l_bm_id NUMBER;

Line 2283: l_values BSC_UPDATE_UTIL.t_array_of_varchar2;

2279: l_id NUMBER;
2280: l_value VARCHAR2(400);
2281:
2282: l_ids BSC_UPDATE_UTIL.t_array_of_number;
2283: l_values BSC_UPDATE_UTIL.t_array_of_varchar2;
2284: l_num_ids NUMBER;
2285: l_i NUMBER;
2286: l_bm_id NUMBER;
2287:

Line 3236: x_ids IN BSC_UPDATE_UTIL.t_array_of_number,

3232: | FUNCTION Insert_Children_Denorm_Table
3233: +============================================================================*/
3234: FUNCTION Insert_Children_Denorm_Table(
3235: x_parent_id IN number,
3236: x_ids IN BSC_UPDATE_UTIL.t_array_of_number,
3237: x_num_ids IN NUMBER,
3238: x_level IN NUMBER,
3239: x_denorm_table IN VARCHAR2,
3240: x_child_col IN VARCHAR2,

Line 3256: l_child_ids BSC_UPDATE_UTIL.t_array_of_number;

3252: TYPE t_cursor IS REF CURSOR;
3253: l_cursor t_cursor;
3254:
3255: l_num_child_ids NUMBER;
3256: l_child_ids BSC_UPDATE_UTIL.t_array_of_number;
3257: l_child_id NUMBER;
3258:
3259: BEGIN
3260:

Line 3357: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;

3353: x_delete_value IN VARCHAR2
3354: ) IS
3355:
3356: h_sql VARCHAR2(32000);
3357: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
3358: l_num_bind_vars NUMBER;
3359:
3360: BEGIN
3361:

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

3363: ' VALUES (:1,:2)';
3364: l_bind_vars_values.delete;
3365: l_bind_vars_values(1) := x_dim_table;
3366: l_bind_vars_values(2) := x_delete_value;
3367: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
3368:
3369: END Insert_AW_Delete_Value;
3370:
3371:

Line 3417: h_deleted_codes BSC_UPDATE_UTIL.t_array_of_number;

3413: h_code1 NUMBER;
3414: h_code2 NUMBER;
3415: h_user_code VARCHAR2(1000);
3416:
3417: h_deleted_codes BSC_UPDATE_UTIL.t_array_of_number;
3418:
3419: -- BSC-BIS-DIMENSIONS
3420: -- MN dimension can be created in BSC to store MN relations between BIS dimensions.
3421: -- To spport NUMBER or VARCHAR2 I will change the type of this arrays to varchar2.

Line 3422: h_deleted_codes1 BSC_UPDATE_UTIL.t_array_of_varchar2;

3418:
3419: -- BSC-BIS-DIMENSIONS
3420: -- MN dimension can be created in BSC to store MN relations between BIS dimensions.
3421: -- To spport NUMBER or VARCHAR2 I will change the type of this arrays to varchar2.
3422: h_deleted_codes1 BSC_UPDATE_UTIL.t_array_of_varchar2;
3423: h_deleted_codes2 BSC_UPDATE_UTIL.t_array_of_varchar2;
3424: h_num_deleted_codes NUMBER;
3425:
3426: h_table_was_modified BOOLEAN;

Line 3423: h_deleted_codes2 BSC_UPDATE_UTIL.t_array_of_varchar2;

3419: -- BSC-BIS-DIMENSIONS
3420: -- MN dimension can be created in BSC to store MN relations between BIS dimensions.
3421: -- To spport NUMBER or VARCHAR2 I will change the type of this arrays to varchar2.
3422: h_deleted_codes1 BSC_UPDATE_UTIL.t_array_of_varchar2;
3423: h_deleted_codes2 BSC_UPDATE_UTIL.t_array_of_varchar2;
3424: h_num_deleted_codes NUMBER;
3425:
3426: h_table_was_modified BOOLEAN;
3427:

Line 3428: h_parent_tables BSC_UPDATE_UTIL.t_array_of_varchar2;

3424: h_num_deleted_codes NUMBER;
3425:
3426: h_table_was_modified BOOLEAN;
3427:
3428: h_parent_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3429: h_parent_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
3430: h_num_parents NUMBER;
3431:
3432: h_aux_fields BSC_UPDATE_UTIL.t_array_of_varchar2;

Line 3429: h_parent_keys BSC_UPDATE_UTIL.t_array_of_varchar2;

3425:
3426: h_table_was_modified BOOLEAN;
3427:
3428: h_parent_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3429: h_parent_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
3430: h_num_parents NUMBER;
3431:
3432: h_aux_fields BSC_UPDATE_UTIL.t_array_of_varchar2;
3433: h_num_aux_fields NUMBER;

Line 3432: h_aux_fields BSC_UPDATE_UTIL.t_array_of_varchar2;

3428: h_parent_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
3429: h_parent_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
3430: h_num_parents NUMBER;
3431:
3432: h_aux_fields BSC_UPDATE_UTIL.t_array_of_varchar2;
3433: h_num_aux_fields NUMBER;
3434:
3435: h_installed_languages BSC_UPDATE_UTIL.t_array_of_varchar2;
3436: h_num_installed_languages NUMBER;

Line 3435: h_installed_languages BSC_UPDATE_UTIL.t_array_of_varchar2;

3431:
3432: h_aux_fields BSC_UPDATE_UTIL.t_array_of_varchar2;
3433: h_num_aux_fields NUMBER;
3434:
3435: h_installed_languages BSC_UPDATE_UTIL.t_array_of_varchar2;
3436: h_num_installed_languages NUMBER;
3437:
3438: h_p_insert VARCHAR2(32700);
3439: h_p_select VARCHAR2(32700);

Line 3446: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;

3442:
3443: h_aux_insert VARCHAR2(32700);
3444: h_aux_select VARCHAR2(32700);
3445:
3446: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
3447: l_num_bind_vars NUMBER;
3448:
3449: h_safe_user_code VARCHAR2(1000);
3450:

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

3584: l_bind_vars_values.delete;
3585: l_bind_vars_values(1) := userenv('LANG');
3586: l_bind_vars_values(2) := userenv('LANG');
3587: l_bind_vars_values(3) := userenv('LANG');
3588: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
3589:
3590: -- Udpate parent key columns
3591: FOR h_i IN 1 .. h_num_parents LOOP
3592: -- Check if there is at least one change of parent to mark this dimension as modified (to recalc kpi totals)

Line 3613: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

3609: ' FROM '||x_input_table||' i, '||h_parent_tables(h_i)||' p'||
3610: ' WHERE d.user_code = i.user_code'||
3611: ' AND i.'||h_parent_keys(h_i)||'_usr = p.user_code)'||
3612: ' WHERE d.user_code IN (SELECT user_code FROM '||x_input_table||')';
3613: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3614: END LOOP;
3615:
3616: -- Udpate auxiliary fileds
3617: FOR h_i IN 1 .. h_num_aux_fields LOOP

Line 3624: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

3620: ' SELECT i.'||h_aux_fields(h_i)||
3621: ' FROM '||x_input_table||' i'||
3622: ' WHERE d.user_code = i.user_code)'||
3623: ' WHERE d.user_code IN (SELECT user_code FROM '||x_input_table||')';
3624: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3625: END LOOP;
3626:
3627: -- Delete existing records from the input table
3628: h_sql := 'DELETE FROM '||x_input_table||

Line 3630: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

3626:
3627: -- Delete existing records from the input table
3628: h_sql := 'DELETE FROM '||x_input_table||
3629: ' WHERE user_code IN (SELECT user_code FROM '||x_dim_table||')';
3630: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3631:
3632: END IF;
3633:
3634: -- Insert new records from the input table to the dimension table

Line 3636: h_num_installed_languages := BSC_UPDATE_UTIL.Get_Installed_Languages(h_installed_languages);

3632: END IF;
3633:
3634: -- Insert new records from the input table to the dimension table
3635: -- Get installed languages
3636: h_num_installed_languages := BSC_UPDATE_UTIL.Get_Installed_Languages(h_installed_languages);
3637: IF h_num_installed_languages = -1 THEN
3638: RAISE e_unexpected_error;
3639: END IF;
3640:

Line 3705: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

3701: ' SELECT d1.user_code, d2.user_code'||
3702: ' FROM '||x_dim_table||' d, '||h_parent_tables(1)||' d1, '||h_parent_tables(2)||' d2'||
3703: ' WHERE d.'||h_parent_keys(1)||' = d1.code'||
3704: ' AND d.'||h_parent_keys(2)||' = d2.code)';
3705: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
3706:
3707: -- Insert new records from the input table to the dimension table
3708: h_sql := 'INSERT INTO '||x_dim_table||' ('||h_parent_keys(1)||', '||h_parent_keys(2)||')'||
3709: ' SELECT p1.code, p2.code'||

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

3712: ' i.'||h_parent_keys(2)||'_USR = p2.user_code AND p2.language = :2';
3713: l_bind_vars_values.delete;
3714: l_bind_vars_values(1) := userenv('LANG');
3715: l_bind_vars_values(2) := userenv('LANG');
3716: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
3717:
3718: END IF;
3719:
3720: -- Delete data from input table

Line 3721: BSC_UPDATE_UTIL.Truncate_Table(x_input_table);

3717:
3718: END IF;
3719:
3720: -- Delete data from input table
3721: BSC_UPDATE_UTIL.Truncate_Table(x_input_table);
3722:
3723: --AW_INTEGRATION: We need to load the dimension into AW even that the dimension
3724: -- input table is empty
3725: h_dim_table_type := Get_Dim_Table_Type(x_dim_table);

Line 3777: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPDATE_FAILED'),

3773:
3774: EXCEPTION
3775: WHEN e_unexpected_error THEN
3776: ROLLBACK;
3777: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_DIMTABLE_UPDATE_FAILED'),
3778: x_source => 'BSC_UPDATE_BASE.Load_Dim_Table');
3779: RETURN FALSE;
3780:
3781: WHEN OTHERS THEN

Line 3838: l_objs_to_check BSC_UPDATE_UTIL.t_array_of_varchar2;

3834:
3835: TYPE t_cursor IS REF CURSOR;
3836: l_cursor t_cursor;
3837: l_sql VARCHAR2(32000);
3838: l_objs_to_check BSC_UPDATE_UTIL.t_array_of_varchar2;
3839: l_num_objs_to_check NUMBER;
3840:
3841: l_max_lud_source DATE;
3842: l_max_lud DATE;

Line 3872: l_num_objs_to_check := BSC_UPDATE_UTIL.Decompose_Varchar2_List(x_source_to_check, l_objs_to_check, ',');

3868: END IF;
3869:
3870:
3871: -- get the max last update date between the source objects
3872: l_num_objs_to_check := BSC_UPDATE_UTIL.Decompose_Varchar2_List(x_source_to_check, l_objs_to_check, ',');
3873: l_max_lud_source := NULL;
3874: FOR l_i IN 1..l_num_objs_to_check LOOP
3875: l_sql := 'SELECT MAX(last_update_date)'||
3876: ' FROM '||l_objs_to_check(l_i);

Line 3929: l_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

3925: l_lst_set_tmp VARCHAR2(8000);
3926: l_cond_parents VARCHAR2(8000);
3927: l_cond_eff_date VARCHAR2(8000);
3928:
3929: l_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3930: l_src_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3931: l_num_parent_columns NUMBER;
3932:
3933: l_source_object VARCHAR2(20000);

Line 3930: l_src_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;

3926: l_cond_parents VARCHAR2(8000);
3927: l_cond_eff_date VARCHAR2(8000);
3928:
3929: l_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3930: l_src_parent_columns BSC_UPDATE_UTIL.t_array_of_varchar2;
3931: l_num_parent_columns NUMBER;
3932:
3933: l_source_object VARCHAR2(20000);
3934: l_code varchar2(100);

Line 4050: BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM');

4046: END IF;
4047: END IF;
4048:
4049: --delete the temp tables
4050: BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM');
4051: BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM_ADD');
4052: BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM_DEL');
4053: commit;
4054:

Line 4051: BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM_ADD');

4047: END IF;
4048:
4049: --delete the temp tables
4050: BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM');
4051: BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM_ADD');
4052: BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM_DEL');
4053: commit;
4054:
4055: if l_dbi_dim_data.source_object_alias is not null then

Line 4052: BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM_DEL');

4048:
4049: --delete the temp tables
4050: BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM');
4051: BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM_ADD');
4052: BSC_UPDATE_UTIL.Execute_Immediate('delete BSC_TMP_DBI_DIM_DEL');
4053: commit;
4054:
4055: if l_dbi_dim_data.source_object_alias is not null then
4056: l_source_object_alias:=l_dbi_dim_data.source_object_alias;

Line 4069: BSC_UPDATE_UTIL.Execute_Immediate(l_sql);

4065: ' FROM '||l_source_object;
4066: if bsc_im_utils.g_debug then
4067: write_to_log_file_n(l_sql);
4068: end if;
4069: BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
4070: COMMIT;
4071:
4072: -- Insert BSC_TMP_DBI_DIM.CODE minus DIM_TABLE.CODE (new records) into BSC_TMP_DBI_DIM_ADD
4073: l_sql := 'INSERT /*+ parallel(BSC_TMP_DBI_DIM_ADD) */'||

Line 4080: BSC_UPDATE_UTIL.Execute_Immediate(l_sql);

4076: ' FROM BSC_TMP_DBI_DIM MINUS select USER_CODE from '||l_dbi_dim_data.table_name;
4077: if bsc_im_utils.g_debug then
4078: write_to_log_file_n(l_sql);
4079: end if;
4080: BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
4081: COMMIT;
4082:
4083: -- Insert DIM_TABLE.CODE minus BSC_TMP_DBI_DIM.CODE (records to delete) into BSC_TMP_DBI_DIM_DEL
4084: l_sql := 'INSERT /*+ parallel(BSC_TMP_DBI_DIM_DEL) */'||

Line 4091: BSC_UPDATE_UTIL.Execute_Immediate(l_sql);

4087: ' FROM '||l_dbi_dim_data.table_name||' MINUS select USER_CODE from BSC_TMP_DBI_DIM';
4088: if bsc_im_utils.g_debug then
4089: write_to_log_file_n(l_sql);
4090: end if;
4091: BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
4092: COMMIT;
4093:
4094: -- AW_INTEGRATION: We need to insert the deleted rows into BSC_AW_DIM_DELETE table
4095: -- Here we need to inser CODEs not USER_CODEs

Line 4127: BSC_UPDATE_UTIL.Execute_Immediate(l_sql);

4123: l_sql := l_sql||' )';
4124: if bsc_im_utils.g_debug then
4125: write_to_log_file_n(l_sql);
4126: end if;
4127: BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
4128: END IF;
4129:
4130: -- Insert new rows into DIM_TABLE
4131: l_sql := 'INSERT /*+ parallel('||l_dbi_dim_data.table_name||') */'||

Line 4139: BSC_UPDATE_UTIL.Execute_Immediate(l_sql);

4135: ' WHERE T.USER_CODE = N.USER_CODE';
4136: if bsc_im_utils.g_debug then
4137: write_to_log_file_n(l_sql);
4138: end if;
4139: BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
4140:
4141: -- Delete from DIM_TABLE
4142: l_sql := 'DELETE FROM '||l_dbi_dim_data.table_name||
4143: ' WHERE USER_CODE IN (SELECT USER_CODE FROM BSC_TMP_DBI_DIM_DEL)';

Line 4147: BSC_UPDATE_UTIL.Execute_Immediate(l_sql);

4143: ' WHERE USER_CODE IN (SELECT USER_CODE FROM BSC_TMP_DBI_DIM_DEL)';
4144: if bsc_im_utils.g_debug then
4145: write_to_log_file_n(l_sql);
4146: end if;
4147: BSC_UPDATE_UTIL.Execute_Immediate(l_sql);
4148:
4149: COMMIT;
4150:
4151: -- Udpate REFRESH_END_TIME in BSC_OBJECT_REFRESH_LOG for this table

Line 4246: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),

4242: RETURN TRUE;
4243:
4244: EXCEPTION
4245: WHEN e_unexpected_error THEN
4246: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR'),
4247: x_source => 'BSC_UPDATE_DIM.Refresh_Dbi_Dimension_Table');
4248: RETURN FALSE;
4249:
4250: WHEN OTHERS THEN

Line 4314: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR');

4310: COMMIT;
4311:
4312: BSC_UPDATE_LOG.Write_Errors_To_Log;
4313:
4314: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR');
4315: RETCODE := 2; -- Request completed with errors
4316:
4317: --LOCKING
4318: WHEN e_could_not_get_lock THEN

Line 4338: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR');

4334: COMMIT;
4335:
4336: BSC_UPDATE_LOG.Write_Errors_To_Log;
4337:
4338: ERRBUF := BSC_UPDATE_UTIL.Get_Message('BSC_UNEXPECTED_ERROR');
4339: RETCODE := 2; -- Request completed with errors
4340:
4341: END Refresh_Dbi_Dimension;
4342:

Line 4480: IF BSC_UPDATE_UTIL.Table_Has_Any_Row(x_denorm_table_name) THEN

4476: end loop;
4477: end loop;
4478:
4479: -- update the denorm table
4480: IF BSC_UPDATE_UTIL.Table_Has_Any_Row(x_denorm_table_name) THEN
4481: -- Incremental load
4482: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_DNT');
4483:
4484: forall i in 1..denorm_parent.count

Line 4482: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_DNT');

4478:
4479: -- update the denorm table
4480: IF BSC_UPDATE_UTIL.Table_Has_Any_Row(x_denorm_table_name) THEN
4481: -- Incremental load
4482: BSC_UPDATE_UTIL.Truncate_Table('BSC_TMP_DNT');
4483:
4484: forall i in 1..denorm_parent.count
4485: execute immediate 'insert into bsc_tmp_dnt (parent_code, code, child_level, parent_level)'||
4486: ' values (:1, :2, :3, :4)'

Line 4534: x_mod_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,

4530: | FUNCTION Refresh_EDW_Dimension
4531: +============================================================================*/
4532: FUNCTION Refresh_EDW_Dimension(
4533: x_dimension_table IN VARCHAR2,
4534: x_mod_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
4535: x_num_mod_dimensions IN OUT NOCOPY NUMBER,
4536: x_checked_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
4537: x_num_checked_dimensions IN OUT NOCOPY NUMBER
4538: ) RETURN BOOLEAN IS

Line 4536: x_checked_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,

4532: FUNCTION Refresh_EDW_Dimension(
4533: x_dimension_table IN VARCHAR2,
4534: x_mod_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
4535: x_num_mod_dimensions IN OUT NOCOPY NUMBER,
4536: x_checked_dimensions IN OUT NOCOPY BSC_UPDATE_UTIL.t_array_of_varchar2,
4537: x_num_checked_dimensions IN OUT NOCOPY NUMBER
4538: ) RETURN BOOLEAN IS
4539:
4540: e_unexpected_error EXCEPTION;

Line 4544: h_relation_cols BSC_UPDATE_UTIL.t_array_of_varchar2;

4540: e_unexpected_error EXCEPTION;
4541:
4542: h_sql VARCHAR2(32700);
4543:
4544: h_relation_cols BSC_UPDATE_UTIL.t_array_of_varchar2;
4545: h_num_relation_cols NUMBER;
4546: h_lst_relation_cols_desc VARCHAR2(32700);
4547: h_lst_relation_cols VARCHAR2(32700);
4548:

Line 4551: h_deleted_records BSC_UPDATE_UTIL.t_array_of_number;

4547: h_lst_relation_cols VARCHAR2(32700);
4548:
4549: h_level_pk_col VARCHAR2(30);
4550:
4551: h_deleted_records BSC_UPDATE_UTIL.t_array_of_number;
4552: h_num_deleted_records NUMBER;
4553:
4554: h_condition VARCHAR2(32700);
4555:

Line 4558: h_child_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;

4554: h_condition VARCHAR2(32700);
4555:
4556: h_i NUMBER;
4557:
4558: h_child_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
4559: h_num_child_dimensions NUMBER;
4560: h_parent_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
4561: h_num_parent_dimensions NUMBER;
4562:

Line 4560: h_parent_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;

4556: h_i NUMBER;
4557:
4558: h_child_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
4559: h_num_child_dimensions NUMBER;
4560: h_parent_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
4561: h_num_parent_dimensions NUMBER;
4562:
4563:
4564: BEGIN

Line 4573: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_dimension_table,

4569:
4570: -- It is possible to try to refresh a dimension which was previously refreshed
4571: -- because of the cascade logical. To prevent this, we insert the dimension into the array
4572: -- x_checked dimensions.
4573: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_dimension_table,
4574: x_checked_dimensions,
4575: x_num_checked_dimensions) THEN
4576:
4577: BSC_UPDATE_LOG.Write_Line_Log(x_dimension_table, BSC_UPDATE_LOG.OUTPUT);

Line 4593: IF NOT BSC_UPDATE_UTIL.Drop_Table('BSC_TMP_DIMENSION') THEN

4589: -- Create a temporal table with the current data
4590: -- Only we are interested in CODE and relation columns.
4591: -- Remember that in EDW all relations are 1-n
4592: -- Drop table if exits
4593: IF NOT BSC_UPDATE_UTIL.Drop_Table('BSC_TMP_DIMENSION') THEN
4594: RAISE e_unexpected_error;
4595: END IF;
4596:
4597: -- Create the table

Line 4598: h_lst_relation_cols := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_relation_cols, h_num_relation_cols);

4594: RAISE e_unexpected_error;
4595: END IF;
4596:
4597: -- Create the table
4598: h_lst_relation_cols := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_relation_cols, h_num_relation_cols);
4599: h_lst_relation_cols_desc := BSC_UPDATE_UTIL.Make_Lst_Description(h_relation_cols, h_num_relation_cols, 'NUMBER');
4600: IF h_lst_relation_cols IS NOT NULL THEN
4601: h_lst_relation_cols := ', '||h_lst_relation_cols;
4602: h_lst_relation_cols_desc := ', '||h_lst_relation_cols_desc;

Line 4599: h_lst_relation_cols_desc := BSC_UPDATE_UTIL.Make_Lst_Description(h_relation_cols, h_num_relation_cols, 'NUMBER');

4595: END IF;
4596:
4597: -- Create the table
4598: h_lst_relation_cols := BSC_UPDATE_UTIL.Make_Lst_From_Array_Varchar2(h_relation_cols, h_num_relation_cols);
4599: h_lst_relation_cols_desc := BSC_UPDATE_UTIL.Make_Lst_Description(h_relation_cols, h_num_relation_cols, 'NUMBER');
4600: IF h_lst_relation_cols IS NOT NULL THEN
4601: h_lst_relation_cols := ', '||h_lst_relation_cols;
4602: h_lst_relation_cols_desc := ', '||h_lst_relation_cols_desc;
4603: END IF;

Line 4615: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);

4611: -- Insert records
4612: h_sql := 'INSERT INTO BSC_TMP_DIMENSION (CODE'||h_lst_relation_cols||')'||
4613: ' SELECT CODE'||h_lst_relation_cols||
4614: ' FROM '||x_dimension_table;
4615: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
4616:
4617: -- Create unique index
4618: IF NOT BSC_UPDATE_UTIL.Create_Unique_Index('BSC_TMP_DIMENSION',
4619: 'BSC_TMP_DIMENSION_U1',

Line 4618: IF NOT BSC_UPDATE_UTIL.Create_Unique_Index('BSC_TMP_DIMENSION',

4614: ' FROM '||x_dimension_table;
4615: BSC_UPDATE_UTIL.Execute_Immediate(h_sql);
4616:
4617: -- Create unique index
4618: IF NOT BSC_UPDATE_UTIL.Create_Unique_Index('BSC_TMP_DIMENSION',
4619: 'BSC_TMP_DIMENSION_U1',
4620: 'CODE',
4621: BSC_APPS.other_index_tbs_type) THEN
4622: RAISE e_unexpected_error;

Line 4634: -- h_condition := BSC_UPDATE_UTIL.Make_Lst_Cond_Number(h_level_pk_col, h_deleted_records, h_num_deleted_records, 'OR');

4630:
4631: IF h_num_deleted_records > 0 THEN
4632: -- Delete from all system tables rows for deleted values
4633:
4634: -- h_condition := BSC_UPDATE_UTIL.Make_Lst_Cond_Number(h_level_pk_col, h_deleted_records, h_num_deleted_records, 'OR');
4635: h_condition := BSC_APPS.Get_New_Big_In_Cond_Number(1, h_level_pk_col);
4636: FOR h_i IN 1..h_num_deleted_records LOOP
4637: BSC_APPS.Add_Value_Big_In_Cond(1, h_deleted_records(h_i));
4638: END LOOP;

Line 4645: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_dimension_table,

4641: RAISE e_unexpected_error;
4642: END IF;
4643:
4644: -- Add the dimension table to the array of modified dimensions
4645: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_dimension_table,
4646: x_mod_dimensions,
4647: x_num_mod_dimensions) THEN
4648: -- Add the dimension to the array x_checked dimensions
4649: x_num_mod_dimensions := x_num_mod_dimensions + 1;

Line 4658: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_dimension_table,

4654: IF h_num_relation_cols > 0 THEN
4655: -- Check if any dimension item changed any relation
4656: IF Any_Item_Changed_Any_Relation(x_dimension_table, 'BSC_TMP_DIMENSION', h_relation_cols, h_num_relation_cols) THEN
4657: -- Add the dimension table to the array of modified dimensions
4658: IF NOT BSC_UPDATE_UTIL.Item_Belong_To_Array_Varchar2(x_dimension_table,
4659: x_mod_dimensions,
4660: x_num_mod_dimensions) THEN
4661: -- Add the dimension to the array x_checked dimensions
4662: x_num_mod_dimensions := x_num_mod_dimensions + 1;

Line 4669: IF NOT BSC_UPDATE_UTIL.Drop_Table('BSC_TMP_DIMENSION') THEN

4665: END IF;
4666: END IF;
4667:
4668: -- Drop the temporal table
4669: IF NOT BSC_UPDATE_UTIL.Drop_Table('BSC_TMP_DIMENSION') THEN
4670: RAISE e_unexpected_error;
4671: END IF;
4672:
4673: -- Refresh child dimensions

Line 4704: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_REFRESH_EDW_DIM_FAILED'),

4700: RETURN TRUE;
4701:
4702: EXCEPTION
4703: WHEN e_unexpected_error THEN
4704: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_REFRESH_EDW_DIM_FAILED'),
4705: x_source => 'BSC_UPDATE_DIM.Refresh_EDW_Dimension');
4706: RETURN FALSE;
4707:
4708: WHEN OTHERS THEN

Line 4720: x_dimension_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,

4716: /*===========================================================================+
4717: | FUNCTION Refresh_EDW_Dimensions
4718: +============================================================================*/
4719: FUNCTION Refresh_EDW_Dimensions(
4720: x_dimension_tables IN BSC_UPDATE_UTIL.t_array_of_varchar2,
4721: x_num_dimension_tables IN NUMBER
4722: ) RETURN BOOLEAN IS
4723:
4724: e_unexpected_error EXCEPTION;

Line 4726: h_mod_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;

4722: ) RETURN BOOLEAN IS
4723:
4724: e_unexpected_error EXCEPTION;
4725:
4726: h_mod_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
4727: h_num_mod_dimensions NUMBER;
4728: h_checked_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
4729: h_num_checked_dimensions NUMBER;
4730: h_i NUMBER;

Line 4728: h_checked_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;

4724: e_unexpected_error EXCEPTION;
4725:
4726: h_mod_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
4727: h_num_mod_dimensions NUMBER;
4728: h_checked_dimensions BSC_UPDATE_UTIL.t_array_of_varchar2;
4729: h_num_checked_dimensions NUMBER;
4730: h_i NUMBER;
4731:
4732: BEGIN

Line 4738: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_REFRESHING_EDW_DIM'), BSC_UPDATE_LOG.OUTPUT);

4734: h_num_checked_dimensions := 0;
4735:
4736: -- Refresh each dimension. It will be adding modified dimension in the array
4737: -- Also, it will delete from B, S tables the deleted dimension values
4738: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_REFRESHING_EDW_DIM'), BSC_UPDATE_LOG.OUTPUT);
4739: FOR h_i IN 1 .. x_num_dimension_tables LOOP
4740: IF NOT Refresh_EDW_Dimension(x_dimension_tables(h_i),
4741: h_mod_dimensions,
4742: h_num_mod_dimensions,

Line 4754: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_RECALC_KPI_DIMTABLES'), BSC_UPDATE_LOG.OUTPUT);

4750: -- that dimension.
4751: -- This procedure mark the affected KPIs with prototype 6 (Recalc data).
4752: -- Once the kpis are marked, Incremental changes will re calculate the data.
4753: IF h_num_mod_dimensions > 0 THEN
4754: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_RECALC_KPI_DIMTABLES'), BSC_UPDATE_LOG.OUTPUT);
4755: FOR h_i IN 1..h_num_mod_dimensions LOOP
4756:
4757: UPDATE BSC_KPIS_B K
4758: SET PROTOTYPE_FLAG = 6,

Line 4790: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_REFRESH_EDW_DIM_FAILED'),

4786: RETURN TRUE;
4787:
4788: EXCEPTION
4789: WHEN e_unexpected_error THEN
4790: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_REFRESH_EDW_DIM_FAILED'),
4791: x_source => 'BSC_UPDATE_DIM.Refresh_EDW_Dimensions');
4792: RETURN FALSE;
4793:
4794: WHEN OTHERS THEN

Line 4814: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;

4810: h_cursor t_cursor;
4811:
4812: h_dim_level_id NUMBER;
4813: h_level_table_name VARCHAR2(30);
4814: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
4815: l_num_bind_vars NUMBER;
4816:
4817: CURSOR c_list IS
4818: SELECT DISTINCT L.DIM_LEVEL_ID, D.LEVEL_TABLE_NAME

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

4854: ' AND LA.DIM_LEVEL_VALUE NOT IN ('||
4855: ' SELECT CODE FROM '||h_level_table_name||'))';
4856: l_bind_vars_values.delete;
4857: l_bind_vars_values(1) := TO_CHAR(h_dim_level_id);
4858: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
4859:
4860: FETCH c_list INTO h_dim_level_id, h_level_table_name;
4861: END LOOP;
4862: CLOSE c_list;

Line 4891: h_parent_tables BSC_UPDATE_UTIL.t_array_of_varchar2;

4887: h_cursor t_cursor;
4888:
4889: h_sql VARCHAR2(32700);
4890:
4891: h_parent_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
4892: h_parent_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
4893: h_num_parents NUMBER;
4894:
4895: h_aux_fields BSC_UPDATE_UTIL.t_array_of_varchar2;

Line 4892: h_parent_keys BSC_UPDATE_UTIL.t_array_of_varchar2;

4888:
4889: h_sql VARCHAR2(32700);
4890:
4891: h_parent_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
4892: h_parent_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
4893: h_num_parents NUMBER;
4894:
4895: h_aux_fields BSC_UPDATE_UTIL.t_array_of_varchar2;
4896: h_num_aux_fields NUMBER;

Line 4895: h_aux_fields BSC_UPDATE_UTIL.t_array_of_varchar2;

4891: h_parent_tables BSC_UPDATE_UTIL.t_array_of_varchar2;
4892: h_parent_keys BSC_UPDATE_UTIL.t_array_of_varchar2;
4893: h_num_parents NUMBER;
4894:
4895: h_aux_fields BSC_UPDATE_UTIL.t_array_of_varchar2;
4896: h_num_aux_fields NUMBER;
4897:
4898: h_i NUMBER;
4899:

Line 4905: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;

4901:
4902: h_null VARCHAR2(250);
4903:
4904: h_loading_mode NUMBER;
4905: l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
4906: l_num_bind_vars NUMBER;
4907:
4908: h_num_rows NUMBER;
4909:

Line 4943: h_null := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'NULL');

4939: INTO h_loading_mode
4940: FROM bsc_db_tables
4941: WHERE table_name = x_input_table;
4942:
4943: h_null := BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'NULL');
4944: --Fix bug#2562867 The lookup can contain single quotes
4945: h_null := REPLACE(h_null,'''', '''''');
4946:
4947: IF h_dim_table_type = DIM_TABLE_TYPE_1N THEN

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

4959: WHERE NVL(USER_CODE, ''0'') = ''0''';
4960: l_bind_vars_values.delete;
4961: l_bind_vars_values(1) := x_input_table;
4962: l_bind_vars_values(2) := h_null;
4963: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
4964:
4965: -- Validate NAME
4966: -- Must be not null
4967: h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)

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

4970: WHERE NAME IS NULL';
4971: l_bind_vars_values.delete;
4972: l_bind_vars_values(1) := x_input_table;
4973: l_bind_vars_values(2) := h_null;
4974: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
4975:
4976: -- NAME should not be duplicated
4977: IF h_loading_mode = 1 THEN
4978: -- Overwrite

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

4983: GROUP BY name
4984: HAVING count(*) > 1';
4985: l_bind_vars_values.delete;
4986: l_bind_vars_values(1) := x_input_table;
4987: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
4988: ELSE
4989: -- Add/Update
4990: h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
4991: SELECT DISTINCT :1, :2, name

Line 5007: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,4);

5003: l_bind_vars_values(1) := x_input_table;
5004: l_bind_vars_values(2) := 'NAME';
5005: l_bind_vars_values(3) := userenv('LANG');
5006: l_bind_vars_values(4) := userenv('LANG');
5007: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,4);
5008: END IF;
5009: END IF;
5010:
5011: -- Validate parents

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

5026: l_bind_vars_values.delete;
5027: l_bind_vars_values(1) := x_input_table;
5028: l_bind_vars_values(2) := h_parent_keys(h_i)||'_USR';
5029: l_bind_vars_values(3) := h_null;
5030: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
5031: END LOOP;
5032:
5033: -- Validate auxiliar fields
5034: IF h_dim_table_type = DIM_TABLE_TYPE_1N THEN

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

5042: l_bind_vars_values.delete;
5043: l_bind_vars_values(1) := x_input_table;
5044: l_bind_vars_values(2) := h_aux_fields(h_i);
5045: l_bind_vars_values(3) := h_null;
5046: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,3);
5047: END LOOP;
5048: END IF;
5049:
5050: -- Check if there were invalid codes

Line 5073: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_ITABLE_VALID_FAILED'),

5069: RETURN NOT h_invalid;
5070:
5071: EXCEPTION
5072: WHEN e_unexpected_error THEN
5073: BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_ITABLE_VALID_FAILED'),
5074: x_source => 'BSC_UPDATE_DIM.Validate_Input_Table');
5075: RETURN NULL;
5076:
5077: WHEN OTHERS THEN

Line 5156: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DFT_DIMVALUE_MISSING'), BSC_UPDATE_LOG.OUTPUT);

5152: IF NOT h_header THEN
5153: BSC_UPDATE_LOG.Write_Line_Log('', BSC_UPDATE_LOG.OUTPUT);
5154: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
5155: BSC_UPDATE_LOG.OUTPUT);
5156: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DFT_DIMVALUE_MISSING'), BSC_UPDATE_LOG.OUTPUT);
5157: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
5158: BSC_UPDATE_LOG.OUTPUT);
5159: BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'KPI_CODE'), C_INDICATOR_W)||
5160: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_TABLE_NAME'), C_DIMENSION_TABLE_W)||

Line 5159: BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'KPI_CODE'), C_INDICATOR_W)||

5155: BSC_UPDATE_LOG.OUTPUT);
5156: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DFT_DIMVALUE_MISSING'), BSC_UPDATE_LOG.OUTPUT);
5157: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
5158: BSC_UPDATE_LOG.OUTPUT);
5159: BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'KPI_CODE'), C_INDICATOR_W)||
5160: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_TABLE_NAME'), C_DIMENSION_TABLE_W)||
5161: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'DEFAULT_VALUE'), C_DEFAULT_VALUE_W),
5162: BSC_UPDATE_LOG.OUTPUT);
5163: h_header := TRUE;

Line 5160: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_TABLE_NAME'), C_DIMENSION_TABLE_W)||

5156: BSC_UPDATE_LOG.Write_Line_Log(BSC_UPDATE_UTIL.Get_Message('BSC_DFT_DIMVALUE_MISSING'), BSC_UPDATE_LOG.OUTPUT);
5157: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
5158: BSC_UPDATE_LOG.OUTPUT);
5159: BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'KPI_CODE'), C_INDICATOR_W)||
5160: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_TABLE_NAME'), C_DIMENSION_TABLE_W)||
5161: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'DEFAULT_VALUE'), C_DEFAULT_VALUE_W),
5162: BSC_UPDATE_LOG.OUTPUT);
5163: h_header := TRUE;
5164: END IF;

Line 5161: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'DEFAULT_VALUE'), C_DEFAULT_VALUE_W),

5157: BSC_UPDATE_LOG.Write_Line_Log('+---------------------------------------------------------------------------+',
5158: BSC_UPDATE_LOG.OUTPUT);
5159: BSC_UPDATE_LOG.Write_Line_Log(RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_BACKEND', 'KPI_CODE'), C_INDICATOR_W)||
5160: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_TABLE_NAME'), C_DIMENSION_TABLE_W)||
5161: RPAD(BSC_UPDATE_UTIL.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'DEFAULT_VALUE'), C_DEFAULT_VALUE_W),
5162: BSC_UPDATE_LOG.OUTPUT);
5163: h_header := TRUE;
5164: END IF;
5165: