[Home] [Help]
2: /*$Header: EDWPSDNB.pls 115.15 2003/11/06 00:56:50 vsurendr ship $*/
3:
4: function push_down_all_levels(
5: p_dim_name varchar2,
6: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
7: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
8: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
9: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
10: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
3:
4: function push_down_all_levels(
5: p_dim_name varchar2,
6: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
7: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
8: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
9: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
10: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
11: p_number_levels number,
4: function push_down_all_levels(
5: p_dim_name varchar2,
6: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
7: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
8: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
9: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
10: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
11: p_number_levels number,
12: p_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
5: p_dim_name varchar2,
6: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
7: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
8: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
9: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
10: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
11: p_number_levels number,
12: p_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
13: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
6: p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
7: p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
8: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
9: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
10: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
11: p_number_levels number,
12: p_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
13: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
14: p_debug boolean,
8: p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
9: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
10: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
11: p_number_levels number,
12: p_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
13: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
14: p_debug boolean,
15: p_parallel number,
16: p_collection_size number,
9: p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
10: p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
11: p_number_levels number,
12: p_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
13: p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
14: p_debug boolean,
15: p_parallel number,
16: p_collection_size number,
17: p_bis_owner varchar2,
129: if g_number_levels <=1 then
130: write_to_log_file_n('Only one level dimension. No need to push down.');
131: return true;
132: end if;
133: g_dim_id:=EDW_OWB_COLLECTION_UTIL.get_dim_id(g_dim_name);
134: init_all(null);
135: if find_lowest_level =false then
136: write_to_log_file_n('find_lowest_level returned with error');
137: return false;
217: function push_down_all_levels_multi return boolean is
218: l_input_table varchar2(80);
219: l_max_ilog_count number;
220: l_index number;
221: l_ok_low_end EDW_OWB_COLLECTION_UTIL.numberTableType;
222: l_ok_high_end EDW_OWB_COLLECTION_UTIL.numberTableType;
223: l_ok_end_count integer;
224: l_job_id EDW_OWB_COLLECTION_UTIL.numberTableType;
225: l_number_jobs number;
218: l_input_table varchar2(80);
219: l_max_ilog_count number;
220: l_index number;
221: l_ok_low_end EDW_OWB_COLLECTION_UTIL.numberTableType;
222: l_ok_high_end EDW_OWB_COLLECTION_UTIL.numberTableType;
223: l_ok_end_count integer;
224: l_job_id EDW_OWB_COLLECTION_UTIL.numberTableType;
225: l_number_jobs number;
226: l_job_status_table varchar2(80);
220: l_index number;
221: l_ok_low_end EDW_OWB_COLLECTION_UTIL.numberTableType;
222: l_ok_high_end EDW_OWB_COLLECTION_UTIL.numberTableType;
223: l_ok_end_count integer;
224: l_job_id EDW_OWB_COLLECTION_UTIL.numberTableType;
225: l_number_jobs number;
226: l_job_status_table varchar2(80);
227: l_log_file varchar2(1000);
228: -----------------------------------------
247: end if;
248: end loop;
249: l_input_table:=g_bis_owner||'.INP_TAB_'||g_dim_id;
250: l_job_status_table:=g_bis_owner||'.JOB_STATUS_'||g_dim_id;
251: if EDW_OWB_COLLECTION_UTIL.create_input_table_push_down(
252: l_input_table,
253: g_dim_name,
254: g_dim_id,
255: g_levels,
284: g_join_nl_percentage
285: )=false then
286: return false;
287: end if;
288: if EDW_OWB_COLLECTION_UTIL.create_job_status_table(l_job_status_table,g_op_table_space)=false then
289: return false;
290: end if;
291: for i in 1..g_number_levels loop
292: g_level_ilog_count(i):=0;
290: end if;
291: for i in 1..g_number_levels loop
292: g_level_ilog_count(i):=0;
293: if g_level_consider(i) and g_levels(i)<>g_lowest_level then
294: g_level_ilog_count(i):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_level_ilog(i),
295: g_bis_owner);
296: end if;
297: end loop;
298: l_max_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_max_in_array(g_level_ilog_count,g_number_levels,l_index);
294: g_level_ilog_count(i):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_level_ilog(i),
295: g_bis_owner);
296: end if;
297: end loop;
298: l_max_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_max_in_array(g_level_ilog_count,g_number_levels,l_index);
299: if l_index<=0 then
300: return false;
301: end if;
302: /*
301: end if;
302: /*
303: for the ilog with the largest number of records, find the ok dist
304: */
305: if EDW_OWB_COLLECTION_UTIL.find_ok_distribution(
306: g_level_ilog(l_index),
307: g_bis_owner,
308: g_max_threads,
309: g_min_job_load_size,
309: g_min_job_load_size,
310: l_ok_low_end,
311: l_ok_high_end,
312: l_ok_end_count)=false then
313: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_status_message;
314: return false;
315: end if;
316: /*
317: launch the threads
398: l_job_status_table
399: );
400: end if;
401: end loop;
402: if EDW_OWB_COLLECTION_UTIL.wait_on_jobs(
403: l_job_id,
404: l_number_jobs,
405: g_sleep_time,
406: g_thread_type)=false then
403: l_job_id,
404: l_number_jobs,
405: g_sleep_time,
406: g_thread_type)=false then
407: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_status_message;
408: return false;
409: end if;
410: if g_status then
411: if EDW_OWB_COLLECTION_UTIL.check_all_child_jobs(
407: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_status_message;
408: return false;
409: end if;
410: if g_status then
411: if EDW_OWB_COLLECTION_UTIL.check_all_child_jobs(
412: l_job_status_table,
413: l_job_id,
414: l_number_jobs,
415: null)=false then
412: l_job_status_table,
413: l_job_id,
414: l_number_jobs,
415: null)=false then
416: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_status_message;
417: return false;
418: end if;
419: end if;
420: /*if g_thread_type='CONC' then
418: end if;
419: end if;
420: /*if g_thread_type='CONC' then
421: --drop the conc programs
422: if EDW_OWB_COLLECTION_UTIL.delete_conc_program(
423: l_temp_conc_short_name,
424: l_temp_exe_name,
425: l_bis_short_name,
426: 'SHORT')=false then
441: end if;
442: if drop_input_tables(l_input_table)=false then
443: null;
444: end if;
445: if EDW_OWB_COLLECTION_UTIL.drop_table(l_job_status_table)=false then
446: null;
447: end if;
448: return true;
449: Exception when others then
503: g_log_file:=p_log_file;
504: g_input_table:=p_input_table;
505: g_job_id:=p_job_id;
506: g_jobid_stmt:=' Job '||g_job_id||' ';
507: EDW_OWB_COLLECTION_UTIL.init_all(g_log_file||'_'||g_job_id,null,'bis.edw.loader');
508: if PUSH_DOWN_ALL_LEVELS(p_ok_low_end,p_ok_high_end)=false then
509: if EDW_OWB_COLLECTION_UTIL.log_into_job_status_table(
510: p_job_status_table,
511: g_dim_name,
505: g_job_id:=p_job_id;
506: g_jobid_stmt:=' Job '||g_job_id||' ';
507: EDW_OWB_COLLECTION_UTIL.init_all(g_log_file||'_'||g_job_id,null,'bis.edw.loader');
508: if PUSH_DOWN_ALL_LEVELS(p_ok_low_end,p_ok_high_end)=false then
509: if EDW_OWB_COLLECTION_UTIL.log_into_job_status_table(
510: p_job_status_table,
511: g_dim_name,
512: g_job_id,
513: 'ERROR',
514: g_status_message)=false then
515: null;
516: end if;
517: else
518: if EDW_OWB_COLLECTION_UTIL.log_into_job_status_table(
519: p_job_status_table,
520: g_dim_name,
521: g_job_id,
522: 'SUCCESS',
536: Begin
537: if read_options_table(g_input_table)=false then
538: return false;
539: end if;
540: EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
541: EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
542: EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
543: if g_debug then
544: write_to_log_file_n('In PUSH_DOWN_ALL_LEVELS, p_ok_low_end='||p_ok_low_end||',p_ok_high_end='||p_ok_high_end);
537: if read_options_table(g_input_table)=false then
538: return false;
539: end if;
540: EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
541: EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
542: EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
543: if g_debug then
544: write_to_log_file_n('In PUSH_DOWN_ALL_LEVELS, p_ok_low_end='||p_ok_low_end||',p_ok_high_end='||p_ok_high_end);
545: end if;
538: return false;
539: end if;
540: EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
541: EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
542: EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
543: if g_debug then
544: write_to_log_file_n('In PUSH_DOWN_ALL_LEVELS, p_ok_low_end='||p_ok_low_end||',p_ok_high_end='||p_ok_high_end);
545: end if;
546: if set_session_parameters=false then
601: l_stmt varchar2(20000);
602: l_in_stmt varchar2(20000);
603: TYPE CurTyp IS REF CURSOR;
604: cv CurTyp;
605: l_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
606: l_prefix EDW_OWB_COLLECTION_UTIL.varcharTableType;
607: l_number_ltc number;
608: Begin
609: if g_debug then
602: l_in_stmt varchar2(20000);
603: TYPE CurTyp IS REF CURSOR;
604: cv CurTyp;
605: l_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
606: l_prefix EDW_OWB_COLLECTION_UTIL.varcharTableType;
607: l_number_ltc number;
608: Begin
609: if g_debug then
610: write_to_log_file_n('In get_level_prefix');
663: l_stmt varchar2(20000);
664: l_in_stmt varchar2(20000);
665: TYPE CurTyp IS REF CURSOR;
666: cv CurTyp;
667: l_meaning EDW_OWB_COLLECTION_UTIL.varcharTableType;
668: l_code EDW_OWB_COLLECTION_UTIL.varcharTableType;
669: l_number_ltc number;
670: Begin
671: if g_debug then
664: l_in_stmt varchar2(20000);
665: TYPE CurTyp IS REF CURSOR;
666: cv CurTyp;
667: l_meaning EDW_OWB_COLLECTION_UTIL.varcharTableType;
668: l_code EDW_OWB_COLLECTION_UTIL.varcharTableType;
669: l_number_ltc number;
670: Begin
671: if g_debug then
672: write_to_log_file_n('In get_level_display_prefix');
892: write_to_log_file_n('In get_all_level_pk');
893: end if;
894: for i in 1..g_number_levels loop
895: if g_levels(i) = g_lowest_level then
896: if EDW_OWB_COLLECTION_UTIL.get_table_surr_pk(g_lowest_level,g_level_pk(i))=false then
897: write_to_log_file_n('EDW_OWB_COLLECTION_UTIL.get_table_surr_pk returned with error');
898: return false;
899: end if;
900: else
893: end if;
894: for i in 1..g_number_levels loop
895: if g_levels(i) = g_lowest_level then
896: if EDW_OWB_COLLECTION_UTIL.get_table_surr_pk(g_lowest_level,g_level_pk(i))=false then
897: write_to_log_file_n('EDW_OWB_COLLECTION_UTIL.get_table_surr_pk returned with error');
898: return false;
899: end if;
900: else
901: g_level_pk(i):=get_ltc_pk(g_levels(i));
951: write_to_log_file_n('In push_down_all_levels_single');
952: end if;
953: for i in 1..(g_number_levels-1) loop --no need to push down the lowest level
954: if g_level_consider(get_index_for_level(g_level_order(i))) then
955: g_level_id:=EDW_OWB_COLLECTION_UTIL.get_object_id(g_level_order(i));
956: if g_level_id=-1 then
957: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
958: return false;
959: end if;
953: for i in 1..(g_number_levels-1) loop --no need to push down the lowest level
954: if g_level_consider(get_index_for_level(g_level_order(i))) then
955: g_level_id:=EDW_OWB_COLLECTION_UTIL.get_object_id(g_level_order(i));
956: if g_level_id=-1 then
957: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
958: return false;
959: end if;
960: insert_into_load_progress_d(g_load_pk,g_level_order(i),'Push Down Level '||g_jobid_stmt,sysdate,null,'LEVEL',
961: 'LEVEL-PUSH-DOWN','LPD'||i||g_jobid_stmt,'I');
987: write_to_log_file_n('In push_down_level');
988: write_to_log_file('The level to push down '||p_level);
989: end if;
990: l_index:=get_index_for_level(p_level);
991: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_level_ilog(l_index))=2 then
992: if make_sql_stmts(p_level)=false then --also executes them
993: write_to_log_file_n('make_sql_stmts returned with error');
994: return false;
995: end if;
1010: return boolean is
1011: l_stmt varchar2(5000);
1012: TYPE CurTyp IS REF CURSOR;
1013: cv CurTyp;
1014: l_rowid EDW_OWB_COLLECTION_UTIL.rowidTableType;
1015: l_count number;
1016: l_total_count number:=0;
1017: l_update_type varchar2(400);
1018: Begin
1049: end loop;
1050: l_total_count:=l_total_count+l_count;
1051: end if;
1052: elsif l_update_type='MASS' then
1053: EDW_OWB_COLLECTION_UTIL.alter_session('NO-PARALLEL');
1054: begin
1055: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1056: execute immediate p_update_stmt;
1057: l_total_count:=sql%rowcount;
1051: end if;
1052: elsif l_update_type='MASS' then
1053: EDW_OWB_COLLECTION_UTIL.alter_session('NO-PARALLEL');
1054: begin
1055: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1056: execute immediate p_update_stmt;
1057: l_total_count:=sql%rowcount;
1058: if g_parallel is not null then
1059: EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
1055: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1056: execute immediate p_update_stmt;
1057: l_total_count:=sql%rowcount;
1058: if g_parallel is not null then
1059: EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
1060: end if;
1061: exception when others then
1062: if sqlcode=-4030 then
1063: commit;
1068: g_status_message:=sqlerrm;
1069: write_to_log_file_n(g_status_message);
1070: write_to_log_file('Problem stmt '||p_update_stmt);
1071: if g_parallel is not null then
1072: EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
1073: end if;
1074: return false;
1075: end ;
1076: end if;
1086: return false;
1087: End;
1088:
1089: function make_sql_stmts(p_level varchar2) return boolean is
1090: l_child_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
1091: l_number_child_level number;
1092: l_parent_index number;
1093: l_level_ilog varchar2(200);
1094: l_status number;
1120: for i in 1..g_number_levels loop
1121: if g_level_order(i)<>p_level then
1122: for j in 1..g_number_final loop
1123: if g_final_levels(j)=p_level and g_final_child_levels(j)=g_level_order(i) then
1124: if EDW_OWB_COLLECTION_UTIL.value_in_table(l_child_level,l_number_child_level,g_level_order(i))=false then
1125: l_number_child_level:=l_number_child_level+1;
1126: l_child_level(l_number_child_level):=g_level_order(i);
1127: if make_and_exec_sql_stmts(p_level,g_level_order(i),l_level_ilog)=false then
1128: return false;
1141: write_to_log_file_n('Error in update_gilog');
1142: return false;
1143: end if;
1144: end if;
1145: if EDW_OWB_COLLECTION_UTIL.drop_table(l_level_ilog)=false then
1146: null;
1147: end if;
1148: end if;
1149: end loop;
1158: function make_and_exec_sql_stmts(p_parent_level varchar2,p_child_level varchar2,p_ilog varchar2) return boolean is
1159: l_parent_index number;
1160: l_child_index number;
1161: l_number_final number;
1162: l_final EDW_OWB_COLLECTION_UTIL.numberTableType;
1163: l_level_copy EDW_OWB_COLLECTION_UTIL.varcharTableType;
1164: l_number_level_copy number;
1165: l_level_copy_index EDW_OWB_COLLECTION_UTIL.numberTableType;
1166: l_final_index EDW_OWB_COLLECTION_UTIL.numberTableType;
1159: l_parent_index number;
1160: l_child_index number;
1161: l_number_final number;
1162: l_final EDW_OWB_COLLECTION_UTIL.numberTableType;
1163: l_level_copy EDW_OWB_COLLECTION_UTIL.varcharTableType;
1164: l_number_level_copy number;
1165: l_level_copy_index EDW_OWB_COLLECTION_UTIL.numberTableType;
1166: l_final_index EDW_OWB_COLLECTION_UTIL.numberTableType;
1167: l_index number;
1161: l_number_final number;
1162: l_final EDW_OWB_COLLECTION_UTIL.numberTableType;
1163: l_level_copy EDW_OWB_COLLECTION_UTIL.varcharTableType;
1164: l_number_level_copy number;
1165: l_level_copy_index EDW_OWB_COLLECTION_UTIL.numberTableType;
1166: l_final_index EDW_OWB_COLLECTION_UTIL.numberTableType;
1167: l_index number;
1168: l_stmt varchar2(30000);
1169: l_table varchar2(200);
1162: l_final EDW_OWB_COLLECTION_UTIL.numberTableType;
1163: l_level_copy EDW_OWB_COLLECTION_UTIL.varcharTableType;
1164: l_number_level_copy number;
1165: l_level_copy_index EDW_OWB_COLLECTION_UTIL.numberTableType;
1166: l_final_index EDW_OWB_COLLECTION_UTIL.numberTableType;
1167: l_index number;
1168: l_stmt varchar2(30000);
1169: l_table varchar2(200);
1170: l_pk varchar2(200);
1174: l_opcode_table varchar2(200);
1175: l_update_stmt varchar2(32000);
1176: l_update_stmt_row varchar2(32000);
1177: l_insert_stmt varchar2(32000);
1178: l_other_fks EDW_OWB_COLLECTION_UTIL.varcharTableType;
1179: l_number_other_fks number;
1180: l_other_fks_hold EDW_OWB_COLLECTION_UTIL.varcharTableType;
1181: l_number_other_fks_hold number;
1182: l_found boolean;
1176: l_update_stmt_row varchar2(32000);
1177: l_insert_stmt varchar2(32000);
1178: l_other_fks EDW_OWB_COLLECTION_UTIL.varcharTableType;
1179: l_number_other_fks number;
1180: l_other_fks_hold EDW_OWB_COLLECTION_UTIL.varcharTableType;
1181: l_number_other_fks_hold number;
1182: l_found boolean;
1183: l_diamond_fk_table varchar2(400):=null;
1184: l_below_diamond_flag EDW_OWB_COLLECTION_UTIL.booleanTableType;
1180: l_other_fks_hold EDW_OWB_COLLECTION_UTIL.varcharTableType;
1181: l_number_other_fks_hold number;
1182: l_found boolean;
1183: l_diamond_fk_table varchar2(400):=null;
1184: l_below_diamond_flag EDW_OWB_COLLECTION_UTIL.booleanTableType;
1185: l_parent_level_count number;
1186: l_ilog_count number;
1187: l_use_nl boolean;
1188: Begin
1208: l_number_level_copy:=0;--for all the levels that are not parent or child create copies
1209: for i in 1..l_number_final loop
1210: l_index:=l_final(i);
1211: if g_final_next_parent(l_index)<>p_parent_level then
1212: if EDW_OWB_COLLECTION_UTIL.value_in_table(l_level_copy,l_number_level_copy,g_final_next_parent(l_index))=false then
1213: l_number_level_copy:=l_number_level_copy+1;
1214: l_level_copy(l_number_level_copy):=g_final_next_parent(l_index);
1215: l_level_copy_index(l_number_level_copy):=get_index_for_level(g_final_next_parent(l_index));
1216: l_final_index(l_number_level_copy):=l_final(i);
1231: if g_parallel is not null then
1232: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
1233: end if;
1234: if l_parent_level_count is null then
1235: l_parent_level_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(p_parent_level,g_table_owner);
1236: end if;
1237: if l_ilog_count is null then
1238: l_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(p_ilog,g_bis_owner);
1239: end if;
1234: if l_parent_level_count is null then
1235: l_parent_level_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(p_parent_level,g_table_owner);
1236: end if;
1237: if l_ilog_count is null then
1238: l_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(p_ilog,g_bis_owner);
1239: end if;
1240: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_ilog_count,l_parent_level_count,g_join_nl_percentage);
1241: l_stmt:=l_stmt||' as select /*+ordered ';
1242: if l_use_nl then
1236: end if;
1237: if l_ilog_count is null then
1238: l_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(p_ilog,g_bis_owner);
1239: end if;
1240: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_ilog_count,l_parent_level_count,g_join_nl_percentage);
1241: l_stmt:=l_stmt||' as select /*+ordered ';
1242: if l_use_nl then
1243: l_stmt:=l_stmt||'use_nl(A)';
1244: end if;
1249: l_stmt:=l_stmt||'B.'||g_final_next_pk(l_index)||',B.'||get_user_key(g_final_next_pk(l_index))||
1250: ',A.rowid row_id from '||p_ilog||','||p_parent_level||' A,'||l_level_copy(i)||' B where '||
1251: 'A.'||get_user_key(g_final_pk_value(l_index))||'||''-'||g_final_pk_prefix(l_index)||'''=B.'||
1252: get_user_key(g_final_next_pk(l_index))||' and '||p_ilog||'.row_id=A.rowid';
1253: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ltc_copy(l_level_copy_index(i)))=false then
1254: null;
1255: end if;
1256: if g_debug then
1257: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1260: if g_debug then
1261: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
1262: end if;
1263: l_table:=g_ltc_copy(l_level_copy_index(i));
1264: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_table,instr(l_table,'.')+1,
1265: length(l_table)),substr(l_table,1,instr(l_table,'.')-1));
1266: end loop;
1267: else
1268: write_to_log_file_n('There are no other levels to make copies of');
1272: --if get_fks_without_fk(p_child_level,null,l_other_fks_hold,l_number_other_fks_hold)=false then
1273: --return false;
1274: --end if;
1275: l_number_other_fks_hold:=0;
1276: if EDW_OWB_COLLECTION_UTIL.get_fks_for_table(p_child_level,l_other_fks_hold,l_number_other_fks_hold)=false then
1277: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
1278: g_status:=false;
1279: return false;
1280: end if;
1273: --return false;
1274: --end if;
1275: l_number_other_fks_hold:=0;
1276: if EDW_OWB_COLLECTION_UTIL.get_fks_for_table(p_child_level,l_other_fks_hold,l_number_other_fks_hold)=false then
1277: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
1278: g_status:=false;
1279: return false;
1280: end if;
1281: if g_debug then
1310: find out diamond keys and create tables to take care of the issue
1311: */
1312: if g_number_diamond_level>0 then
1313: declare
1314: l_diamond_tops EDW_OWB_COLLECTION_UTIL.varcharTableType;
1315: l_number_diamond_tops number:=0;
1316: l_other_fk_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
1317: l_parent_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1318: l_child_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1312: if g_number_diamond_level>0 then
1313: declare
1314: l_diamond_tops EDW_OWB_COLLECTION_UTIL.varcharTableType;
1315: l_number_diamond_tops number:=0;
1316: l_other_fk_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
1317: l_parent_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1318: l_child_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1319: l_parent_pk_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1320: l_child_fk_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1313: declare
1314: l_diamond_tops EDW_OWB_COLLECTION_UTIL.varcharTableType;
1315: l_number_diamond_tops number:=0;
1316: l_other_fk_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
1317: l_parent_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1318: l_child_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1319: l_parent_pk_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1320: l_child_fk_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1321: l_number_level_order number;
1314: l_diamond_tops EDW_OWB_COLLECTION_UTIL.varcharTableType;
1315: l_number_diamond_tops number:=0;
1316: l_other_fk_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
1317: l_parent_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1318: l_child_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1319: l_parent_pk_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1320: l_child_fk_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1321: l_number_level_order number;
1322: begin
1315: l_number_diamond_tops number:=0;
1316: l_other_fk_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
1317: l_parent_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1318: l_child_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1319: l_parent_pk_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1320: l_child_fk_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1321: l_number_level_order number;
1322: begin
1323: --see if the parent is in a hier that belongs to diamond
1316: l_other_fk_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
1317: l_parent_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1318: l_child_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1319: l_parent_pk_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1320: l_child_fk_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1321: l_number_level_order number;
1322: begin
1323: --see if the parent is in a hier that belongs to diamond
1324: if get_diamond_tops(p_parent_level,l_diamond_tops,l_number_diamond_tops)=false then
1416: for i in 1..l_number_final loop
1417: if g_final_next_parent(l_final(i))=p_parent_level then
1418: l_stmt:=l_stmt||',A.'||g_final_pk_value(l_final(i));
1419: else
1420: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(l_level_copy,l_number_level_copy,
1421: g_final_next_parent(l_final(i)));
1422: l_stmt:=l_stmt||','||g_ltc_copy(l_level_copy_index(l_index))||'.'||g_final_next_pk(l_final(i));
1423: end if;
1424: end loop;
1447: l_stmt:=substr(l_stmt,1,length(l_stmt)-4);
1448: if g_debug then
1449: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1450: end if;
1451: EDW_OWB_COLLECTION_UTIL.alter_session('NO-PARALLEL');
1452: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1453: execute immediate l_stmt;
1454: if g_debug then
1455: write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
1448: if g_debug then
1449: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1450: end if;
1451: EDW_OWB_COLLECTION_UTIL.alter_session('NO-PARALLEL');
1452: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1453: execute immediate l_stmt;
1454: if g_debug then
1455: write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
1456: end if;
1455: write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
1456: end if;
1457: commit;
1458: if g_parallel is not null then
1459: EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
1460: end if;
1461: else
1462: --insert and update most of the time here
1463: l_opcode_table:=g_update_rowid(l_child_index)||'O';
1471: end if;
1472: l_stmt:=l_stmt||'A.rowid row_id,B.rowid row_id1,decode(B.rowid,null,0,1) status from '||
1473: p_ilog||','||p_parent_level||' A,'||p_child_level||' B where '||p_ilog||'.row_id=A.rowid '||
1474: 'and A.'||l_parent_user_pk||'||''-'||g_level_prefix(l_parent_index)||'''=B.'||l_user_pk||'(+)';
1475: if EDW_OWB_COLLECTION_UTIL.drop_table(l_opcode_table)=false then
1476: null;
1477: end if;
1478: if g_debug then
1479: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1481: execute immediate l_stmt;
1482: if g_debug then
1483: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
1484: end if;
1485: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_opcode_table,instr(l_opcode_table,'.')+1,
1486: length(l_opcode_table)),substr(l_opcode_table,1,instr(l_opcode_table,'.')-1));
1487: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(l_opcode_table,'status=1')=2 then
1488: if g_debug then
1489: write_to_log_file_n('Update needed for child level');
1483: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
1484: end if;
1485: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_opcode_table,instr(l_opcode_table,'.')+1,
1486: length(l_opcode_table)),substr(l_opcode_table,1,instr(l_opcode_table,'.')-1));
1487: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(l_opcode_table,'status=1')=2 then
1488: if g_debug then
1489: write_to_log_file_n('Update needed for child level');
1490: end if;
1491: l_stmt:='create table '||g_update_rowid(l_child_index)||' tablespace '||g_op_table_space;
1500: for i in 1..l_number_final loop
1501: if g_final_next_parent(l_final(i))=p_parent_level then
1502: l_stmt:=l_stmt||',A.'||g_final_pk_value(l_final(i))||' '||g_final_fk(l_final(i));
1503: else
1504: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(l_level_copy,l_number_level_copy,
1505: g_final_next_parent(l_final(i)));
1506: l_stmt:=l_stmt||','||g_ltc_copy(l_level_copy_index(l_index))||'.'||g_final_next_pk(l_final(i))||' '||
1507: g_final_fk(l_final(i));
1508: end if;
1530: if l_diamond_fk_table is not null then
1531: l_stmt:=l_stmt||'A.rowid='||l_diamond_fk_table||'.row_id and ';
1532: end if;
1533: l_stmt:=substr(l_stmt,1,length(l_stmt)-4);
1534: if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid(l_child_index))=false then
1535: null;
1536: end if;
1537: if g_debug then
1538: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1550: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1551: end if;
1552: execute immediate l_stmt;
1553: l_table:=g_update_rowid(l_child_index);
1554: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_table,instr(l_table,'.')+1,
1555: length(l_table)),substr(l_table,1,instr(l_table,'.')-1));
1556: --call update
1557: l_update_stmt:='update /*+ORDERED USE_NL('||p_child_level||')*/ '||p_child_level||' set '||
1558: '(NAME,LAST_UPDATE_DATE,PUSHED_DOWN_FLAG';
1587: end if;
1588: if execute_update_stmt(l_update_stmt,l_update_stmt_row,g_update_rowid(l_child_index))=false then
1589: return false;
1590: end if;
1591: if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid(l_child_index))=false then
1592: null;
1593: end if;
1594: end if;
1595: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(l_opcode_table,'status=0')=2 then
1591: if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid(l_child_index))=false then
1592: null;
1593: end if;
1594: end if;
1595: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(l_opcode_table,'status=0')=2 then
1596: if g_debug then
1597: write_to_log_file_n('Insert needed for child level');
1598: end if;
1599: l_insert_stmt:='insert into '||p_child_level||'('||l_user_pk||','||l_pk;
1612: for i in 1..l_number_final loop
1613: if g_final_next_parent(l_final(i))=p_parent_level then
1614: l_insert_stmt:=l_insert_stmt||',A.'||g_final_pk_value(l_final(i));
1615: else
1616: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(l_level_copy,l_number_level_copy,
1617: g_final_next_parent(l_final(i)));
1618: l_insert_stmt:=l_insert_stmt||','||g_ltc_copy(l_level_copy_index(l_index))||'.'||g_final_next_pk(l_final(i));
1619: end if;
1620: end loop;
1644: l_insert_stmt:=substr(l_insert_stmt,1,length(l_insert_stmt)-4);
1645: if g_debug then
1646: write_to_log_file_n('Going to execute Insert stmt '||l_insert_stmt||get_time);
1647: end if;
1648: EDW_OWB_COLLECTION_UTIL.alter_session('NO-PARALLEL');
1649: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1650: execute immediate l_insert_stmt;
1651: if g_debug then
1652: write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
1645: if g_debug then
1646: write_to_log_file_n('Going to execute Insert stmt '||l_insert_stmt||get_time);
1647: end if;
1648: EDW_OWB_COLLECTION_UTIL.alter_session('NO-PARALLEL');
1649: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1650: execute immediate l_insert_stmt;
1651: if g_debug then
1652: write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
1653: end if;
1651: if g_debug then
1652: write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
1653: end if;
1654: if g_parallel is not null then
1655: EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
1656: end if;
1657: commit;
1658: end if;
1659: if EDW_OWB_COLLECTION_UTIL.drop_table(l_opcode_table)=false then
1655: EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
1656: end if;
1657: commit;
1658: end if;
1659: if EDW_OWB_COLLECTION_UTIL.drop_table(l_opcode_table)=false then
1660: null;
1661: end if;
1662: end if;
1663: if l_number_level_copy>0 then
1661: end if;
1662: end if;
1663: if l_number_level_copy>0 then
1664: for i in 1..l_number_level_copy loop
1665: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ltc_copy(l_level_copy_index(i)))=false then
1666: null;
1667: end if;
1668: end loop;
1669: end if;
1667: end if;
1668: end loop;
1669: end if;
1670: if l_diamond_fk_table is not null then
1671: if EDW_OWB_COLLECTION_UTIL.drop_table(l_diamond_fk_table)=false then
1672: null;
1673: end if;
1674: end if;
1675: return true;
1686: if g_debug then
1687: write_to_log_file_n('In update_gilog for '||p_ilog||get_time);
1688: end if;
1689: l_stmt:='update '||p_ilog||' set status=2 where status=1';
1690: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1691: execute immediate l_stmt;
1692: commit;
1693: if g_debug then
1694: write_to_log_file_n('Updated '||sql%rowcount||' rows from 1 to 2 for '||p_ilog||get_time);
1717: if g_debug then
1718: write_to_log_file_n('In set_gilog_status');
1719: end if;
1720: if g_type_ilog_generation='UPDATE' then
1721: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(p_ilog,'status=1')=2 then
1722: return 2;
1723: end if;
1724: if g_collection_size =0 then
1725: l_stmt:='update '||p_ilog||' set status=1 where status=0';
1728: end if;
1729: if g_debug then
1730: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1731: end if;
1732: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1733: execute immediate l_stmt;
1734: l_count:=sql%rowcount;
1735: commit;
1736: if g_debug then
1768: l_stmt:=l_stmt||' as select row_id,decode(status,1,2,0,1,2) status from '||
1769: p_ilog;
1770: end if;
1771: end if;
1772: if EDW_OWB_COLLECTION_UTIL.drop_table(l_ilog)=false then
1773: null;
1774: end if;
1775: if g_debug then
1776: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1778: execute immediate l_stmt;
1779: if g_debug then
1780: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
1781: end if;
1782: if EDW_OWB_COLLECTION_UTIL.drop_table(p_ilog)=false then
1783: null;
1784: end if;
1785: p_ilog:=l_ilog;
1786: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(p_ilog,' status=1 ')<2 then
1782: if EDW_OWB_COLLECTION_UTIL.drop_table(p_ilog)=false then
1783: null;
1784: end if;
1785: p_ilog:=l_ilog;
1786: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(p_ilog,' status=1 ')<2 then
1787: l_count:=0;
1788: else
1789: l_count:=1;
1790: end if;
1827: if g_debug then
1828: write_to_log_file_n('In init_all p_job_id='||p_job_id);
1829: end if;
1830: for i in 1..g_number_levels loop
1831: g_level_ids(i):=EDW_OWB_COLLECTION_UTIL.get_object_id(g_levels(i));
1832: g_skip_ilog_update(i):=false;
1833: if p_job_id is null then
1834: l_name:='TAB_'||g_level_ids(i)||'_';
1835: else
1838: l_name_org:='TAB_'||g_level_ids(i)||'_';
1839: g_level_ilog(i):=g_bis_owner||'.'||l_name||'IL';
1840: g_level_ilog_name(i):=g_level_ilog(i);
1841: if p_job_id is null then
1842: if EDW_OWB_COLLECTION_UTIL.check_table(g_level_ilog(i)||'A') then
1843: g_level_ilog(i):=g_level_ilog(i)||'A';
1844: end if;
1845: end if;
1846: g_level_ilog_found(i):=false;
1866: End;
1867:
1868: procedure write_to_log_file(p_message varchar2) is
1869: begin
1870: EDW_OWB_COLLECTION_UTIL.write_to_log_file(p_message);
1871: Exception when others then
1872: null;
1873: End;
1874:
1886: write_to_log_file_n('In does_snp_have_data, p_level is '||p_level);
1887: end if;
1888: for i in 1..g_number_levels loop
1889: if g_levels(i)=p_level then
1890: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_level_snapshot_logs(i))=2 then
1891: return true;
1892: else
1893: return false;
1894: end if;
1928: end if;
1929: if g_full_refresh then
1930: for i in 1..g_number_levels loop
1931: if g_level_consider(i) then --this may be false if the user does not want this level pushed down
1932: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_levels(i))=2 then
1933: g_level_consider(i):=true;
1934: else
1935: g_level_consider(i):=false;
1936: end if;
1938: end loop;
1939: else
1940: for i in 1..g_number_levels loop
1941: if g_level_consider(i) then
1942: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_level_snapshot_logs(i))=2 then
1943: g_level_consider(i):=true;
1944: else
1945: g_level_consider(i):=false;
1946: end if;
1950: /*is this reqd ? will a ltc table have only 2 rows?
1951: */
1952: for i in 1..g_number_levels loop
1953: g_level_full_insert(i):=false;--default
1954: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_levels(i))=1 then
1955: g_level_full_insert(i):=true;
1956: if g_debug then
1957: write_to_log_file_n('Level '||g_levels(i)||' Empty');
1958: end if;
1955: g_level_full_insert(i):=true;
1956: if g_debug then
1957: write_to_log_file_n('Level '||g_levels(i)||' Empty');
1958: end if;
1959: elsif EDW_OWB_COLLECTION_UTIL.does_table_have_only_n_row(g_levels(i),2)=2
1960: and (EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_levels(i),g_level_pk(i)||'=0')=2
1961: and EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_levels(i),g_level_pk(i)||'=-1')=2) then
1962: g_level_full_insert(i):=true;
1963: if g_debug then
1956: if g_debug then
1957: write_to_log_file_n('Level '||g_levels(i)||' Empty');
1958: end if;
1959: elsif EDW_OWB_COLLECTION_UTIL.does_table_have_only_n_row(g_levels(i),2)=2
1960: and (EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_levels(i),g_level_pk(i)||'=0')=2
1961: and EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_levels(i),g_level_pk(i)||'=-1')=2) then
1962: g_level_full_insert(i):=true;
1963: if g_debug then
1964: write_to_log_file_n('Level '||g_levels(i)||' only has NA_EDW and NA_ERR rows');
1957: write_to_log_file_n('Level '||g_levels(i)||' Empty');
1958: end if;
1959: elsif EDW_OWB_COLLECTION_UTIL.does_table_have_only_n_row(g_levels(i),2)=2
1960: and (EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_levels(i),g_level_pk(i)||'=0')=2
1961: and EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_levels(i),g_level_pk(i)||'=-1')=2) then
1962: g_level_full_insert(i):=true;
1963: if g_debug then
1964: write_to_log_file_n('Level '||g_levels(i)||' only has NA_EDW and NA_ERR rows');
1965: end if;
2000: return null;
2001: End;
2002:
2003: function get_fks_without_fk(p_level varchar2, p_fk varchar2,
2004: p_fks_out out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,p_number_fks_out out NOCOPY number) return boolean is
2005: l_count number;
2006: Begin
2007: if g_debug then
2008: write_to_log_file_n('In get_fks_without_fk');
2032: l_stmt varchar2(10000);
2033: l_in_stmt varchar2(10000);
2034: TYPE CurTyp IS REF CURSOR;
2035: cv CurTyp;
2036: l_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
2037: l_seq EDW_OWB_COLLECTION_UTIL.varcharTableType;
2038: l_number_level number;
2039: Begin
2040: if g_debug then
2033: l_in_stmt varchar2(10000);
2034: TYPE CurTyp IS REF CURSOR;
2035: cv CurTyp;
2036: l_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
2037: l_seq EDW_OWB_COLLECTION_UTIL.varcharTableType;
2038: l_number_level number;
2039: Begin
2040: if g_debug then
2041: write_to_log_file_n('In get_level_seq');
2138: if g_debug then
2139: write_to_log_file_n('In clean_up');
2140: end if;
2141: for i in 1..g_number_levels loop
2142: if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid(i))=false then
2143: null;
2144: end if;
2145: if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_rowid(i))=false then
2146: null;
2141: for i in 1..g_number_levels loop
2142: if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid(i))=false then
2143: null;
2144: end if;
2145: if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_rowid(i))=false then
2146: null;
2147: end if;
2148: end loop;
2149: Exception when others then
2177: if g_debug then
2178: write_to_log_file_n('In create_ilog_tables('||p_index||')');
2179: end if;
2180: l_index:=get_index_for_level(g_level_order(p_index));
2181: if EDW_OWB_COLLECTION_UTIL.is_column_in_table(g_level_snapshot_logs(l_index),g_level_pk(l_index),
2182: g_table_owner) then
2183: g_snplog_has_pk(l_index):=true;
2184: end if;
2185: if EDW_OWB_COLLECTION_UTIL.check_table(g_level_ilog(l_index))=false then
2181: if EDW_OWB_COLLECTION_UTIL.is_column_in_table(g_level_snapshot_logs(l_index),g_level_pk(l_index),
2182: g_table_owner) then
2183: g_snplog_has_pk(l_index):=true;
2184: end if;
2185: if EDW_OWB_COLLECTION_UTIL.check_table(g_level_ilog(l_index))=false then
2186: l_stmt:='create table '||g_level_ilog(l_index)||' tablespace '||g_op_table_space;
2187: if g_parallel is not null then
2188: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2189: end if;
2191: if g_snplog_has_pk(l_index) then
2192: l_stmt:=l_stmt||','||g_level_pk(l_index);
2193: end if;
2194: l_stmt:=l_stmt||',0 status from '||g_level_snapshot_logs(l_index)||' where 1=2';
2195: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2196: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2197: g_status:=false;
2198: write_to_log_file_n(g_status_message);
2199: return false;
2192: l_stmt:=l_stmt||','||g_level_pk(l_index);
2193: end if;
2194: l_stmt:=l_stmt||',0 status from '||g_level_snapshot_logs(l_index)||' where 1=2';
2195: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2196: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2197: g_status:=false;
2198: write_to_log_file_n(g_status_message);
2199: return false;
2200: end if;
2287: end if;
2288: l_stmt:=l_stmt||','||g_levels(l_index)||' where '||g_levels(l_index)||'.rowid='||
2289: g_level_snapshot_logs(l_index)||'.M_ROW$$ and '||g_levels(l_index)||'.PUSHED_DOWN_FLAG is null';
2290: end if;
2291: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
2292: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2293: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2294: g_status:=false;
2295: write_to_log_file_n(g_status_message);
2288: l_stmt:=l_stmt||','||g_levels(l_index)||' where '||g_levels(l_index)||'.rowid='||
2289: g_level_snapshot_logs(l_index)||'.M_ROW$$ and '||g_levels(l_index)||'.PUSHED_DOWN_FLAG is null';
2290: end if;
2291: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
2292: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2293: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2294: g_status:=false;
2295: write_to_log_file_n(g_status_message);
2296: return false;
2289: g_level_snapshot_logs(l_index)||'.M_ROW$$ and '||g_levels(l_index)||'.PUSHED_DOWN_FLAG is null';
2290: end if;
2291: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
2292: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2293: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2294: g_status:=false;
2295: write_to_log_file_n(g_status_message);
2296: return false;
2297: end if;
2313: l_stmt:=l_stmt||',A.'||g_level_pk(l_index)||',B.status';
2314: l_stmt:=l_stmt||' from '||g_level_ilog(l_index)||' B,'||g_levels(l_index)||
2315: ' A where A.'||g_level_pk(l_index)||'=B.'||g_level_pk(l_index);
2316: else
2317: l_level_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(l_index),g_table_owner);
2318: l_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_level_ilog(l_index),g_bis_owner);
2319: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_ilog_count,l_level_count,g_join_nl_percentage);
2320: l_stmt:=l_stmt||' as select /*+ordered ';
2321: if l_use_nl then
2314: l_stmt:=l_stmt||' from '||g_level_ilog(l_index)||' B,'||g_levels(l_index)||
2315: ' A where A.'||g_level_pk(l_index)||'=B.'||g_level_pk(l_index);
2316: else
2317: l_level_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(l_index),g_table_owner);
2318: l_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_level_ilog(l_index),g_bis_owner);
2319: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_ilog_count,l_level_count,g_join_nl_percentage);
2320: l_stmt:=l_stmt||' as select /*+ordered ';
2321: if l_use_nl then
2322: l_stmt:=l_stmt||'use_nl(A)';
2315: ' A where A.'||g_level_pk(l_index)||'=B.'||g_level_pk(l_index);
2316: else
2317: l_level_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(l_index),g_table_owner);
2318: l_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_level_ilog(l_index),g_bis_owner);
2319: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_ilog_count,l_level_count,g_join_nl_percentage);
2320: l_stmt:=l_stmt||' as select /*+ordered ';
2321: if l_use_nl then
2322: l_stmt:=l_stmt||'use_nl(A)';
2323: end if;
2327: end if;
2328: l_stmt:=l_stmt||'B.row_id,B.status';
2329: l_stmt:=l_stmt||' from '||g_level_ilog(l_index)||' B,'||g_levels(l_index)||' A where A.rowid=B.row_id';
2330: end if;
2331: if EDW_OWB_COLLECTION_UTIL.drop_table(l_level_ilog_el)=false then
2332: null;
2333: end if;
2334: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2335: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2330: end if;
2331: if EDW_OWB_COLLECTION_UTIL.drop_table(l_level_ilog_el)=false then
2332: null;
2333: end if;
2334: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2335: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2336: g_status:=false;
2337: write_to_log_file_n(g_status_message);
2338: return false;
2331: if EDW_OWB_COLLECTION_UTIL.drop_table(l_level_ilog_el)=false then
2332: null;
2333: end if;
2334: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2335: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2336: g_status:=false;
2337: write_to_log_file_n(g_status_message);
2338: return false;
2339: end if;
2336: g_status:=false;
2337: write_to_log_file_n(g_status_message);
2338: return false;
2339: end if;
2340: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_level_ilog_el,instr(l_level_ilog_el,'.')+1,
2341: length(l_level_ilog_el)),substr(l_level_ilog_el,1,instr(l_level_ilog_el,'.')-1));
2342: if EDW_OWB_COLLECTION_UTIL.drop_table(g_level_ilog(l_index))=false then
2343: null;
2344: end if;
2338: return false;
2339: end if;
2340: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_level_ilog_el,instr(l_level_ilog_el,'.')+1,
2341: length(l_level_ilog_el)),substr(l_level_ilog_el,1,instr(l_level_ilog_el,'.')-1));
2342: if EDW_OWB_COLLECTION_UTIL.drop_table(g_level_ilog(l_index))=false then
2343: null;
2344: end if;
2345: g_level_ilog(l_index):=l_level_ilog_el;
2346: l_level_ilog_el:=g_level_ilog(l_index)||'T';
2365: l_stmt:=l_stmt||'row_id,'||g_level_pk(l_index)||',0 status from '||g_level_ilog(l_index);
2366: else
2367: l_stmt:=l_stmt||'row_id,'||'0 status from '||g_level_ilog(l_index);
2368: end if;
2369: if EDW_OWB_COLLECTION_UTIL.drop_table(l_level_ilog_el)=false then
2370: null;
2371: end if;
2372: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2373: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2368: end if;
2369: if EDW_OWB_COLLECTION_UTIL.drop_table(l_level_ilog_el)=false then
2370: null;
2371: end if;
2372: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2373: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2374: g_status:=false;
2375: write_to_log_file_n(g_status_message);
2376: return false;
2369: if EDW_OWB_COLLECTION_UTIL.drop_table(l_level_ilog_el)=false then
2370: null;
2371: end if;
2372: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2373: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2374: g_status:=false;
2375: write_to_log_file_n(g_status_message);
2376: return false;
2377: end if;
2381: else
2382: l_stmt:='insert into '||g_level_ilog(l_index)||'(row_id, status) select '||
2383: 'row_id,status from '||l_level_ilog_el;
2384: end if;
2385: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2386: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2387: g_status:=false;
2388: write_to_log_file_n(g_status_message);
2389: return false;
2382: l_stmt:='insert into '||g_level_ilog(l_index)||'(row_id, status) select '||
2383: 'row_id,status from '||l_level_ilog_el;
2384: end if;
2385: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2386: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2387: g_status:=false;
2388: write_to_log_file_n(g_status_message);
2389: return false;
2390: end if;
2388: write_to_log_file_n(g_status_message);
2389: return false;
2390: end if;
2391: commit;
2392: if EDW_OWB_COLLECTION_UTIL.drop_table(l_level_ilog_el)=false then
2393: null;
2394: end if;
2395: end if;
2396: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_level_ilog(l_index),instr(g_level_ilog(l_index),'.')+1,
2392: if EDW_OWB_COLLECTION_UTIL.drop_table(l_level_ilog_el)=false then
2393: null;
2394: end if;
2395: end if;
2396: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_level_ilog(l_index),instr(g_level_ilog(l_index),'.')+1,
2397: length(g_level_ilog(l_index))),substr(g_level_ilog(l_index),1,instr(g_level_ilog(l_index),'.')-1));
2398: return true;
2399: Exception when others then
2400: g_status_message:=sqlerrm;
2409: write_to_log_file_n('In analyze_ltc_tables');
2410: end if;
2411: for i in 1..g_number_levels loop
2412: if g_analyze_needed(i)=true then
2413: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(g_levels(i), g_table_owner);
2414: if g_debug then
2415: write_to_log_file_n('Analyzed '||g_levels(i));
2416: end if;
2417: end if;
2431: if g_debug then
2432: write_to_log_file_n('In drop_ilog');
2433: end if;
2434: for i in 1..g_number_levels loop
2435: if EDW_OWB_COLLECTION_UTIL.drop_table(g_level_ilog(i))=false then
2436: null;
2437: end if;
2438: if EDW_OWB_COLLECTION_UTIL.drop_table(g_level_ilog(i)||'A')=false then
2439: null;
2434: for i in 1..g_number_levels loop
2435: if EDW_OWB_COLLECTION_UTIL.drop_table(g_level_ilog(i))=false then
2436: null;
2437: end if;
2438: if EDW_OWB_COLLECTION_UTIL.drop_table(g_level_ilog(i)||'A')=false then
2439: null;
2440: end if;
2441: if EDW_OWB_COLLECTION_UTIL.drop_ilog_tables(g_level_ilog(i)||'_IL',null,g_bis_owner)=false then
2442: null;
2437: end if;
2438: if EDW_OWB_COLLECTION_UTIL.drop_table(g_level_ilog(i)||'A')=false then
2439: null;
2440: end if;
2441: if EDW_OWB_COLLECTION_UTIL.drop_ilog_tables(g_level_ilog(i)||'_IL',null,g_bis_owner)=false then
2442: null;
2443: end if;
2444: end loop;
2445: Exception when others then
2451: procedure insert_into_load_progress_d(p_load_fk number,p_object_name varchar2,p_load_progress varchar2,
2452: p_start_date date,p_end_date date,p_category varchar2, p_operation varchar2,p_seq_id varchar2,p_flag varchar2) is
2453: Begin
2454: if g_debug then
2455: EDW_OWB_COLLECTION_UTIL.insert_into_load_progress(p_load_fk,p_object_name,g_level_id,p_load_progress,
2456: p_start_date,p_end_date,p_category,p_operation,p_seq_id,p_flag,g_level_id);
2457: commit;
2458: end if;
2459: Exception when others then
2468: */
2469: function find_ltc_to_push_down return number is
2470: l_found boolean;
2471: l_option_value varchar2(20);
2472: l_itemset_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
2473: l_number_itemset_cols number;
2474: Begin
2475: l_found:=false;
2476: if g_read_cfig_options then
2497: return 0;
2498: end if;
2499: if l_number_itemset_cols>0 then
2500: for i in 1..g_number_levels loop
2501: if EDW_OWB_COLLECTION_UTIL.value_in_table(l_itemset_cols,l_number_itemset_cols,
2502: substr(g_levels(i),1,instr(g_levels(i),'_LTC',-1)-1))=false then
2503: g_level_consider(i):=false;
2504: end if;
2505: end loop;
2517: else
2518: return 1;
2519: end if;
2520: else
2521: if EDW_OWB_COLLECTION_UTIL.is_itemset_implemented(g_dim_name,'EDW_LEVEL_PUSH_DOWN')='Y' then
2522: --all levels
2523: for i in 1..g_number_levels loop
2524: g_level_consider(i):=true;
2525: end loop;
2529: return 2;
2530: end if;
2531: for i in 1..g_number_levels loop
2532: --g_levels
2533: if EDW_OWB_COLLECTION_UTIL.is_itemset_implemented(g_levels(i),'EDW_LEVEL_PUSH_DOWN')='Y' then
2534: g_level_consider(i):=true;
2535: l_found:=true;
2536: else
2537: g_level_consider(i):=false;
2566: if g_parallel is not null then
2567: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2568: end if;
2569: l_stmt:=l_stmt||' as select row_id from '||p_ilog||' where status=1';
2570: if EDW_OWB_COLLECTION_UTIL.drop_table(p_ilog_copy)=false then
2571: null;
2572: end if;
2573: if g_debug then
2574: write_to_log_file_n('Going to execute '||l_stmt||get_time);
2576: execute immediate l_stmt;
2577: if g_debug then
2578: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
2579: end if;
2580: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(p_ilog_copy,instr(p_ilog_copy,'.')+1,
2581: length(p_ilog_copy)),substr(p_ilog_copy,1,instr(p_ilog_copy,'.')-1));
2582: return true;
2583: Exception when others then
2584: g_status_message:=sqlerrm;
2593: cv CurTyp;
2594: l_top_level varchar2(200);
2595: l_hier_hold varchar2(200);
2596: l_child_hold varchar2(200);
2597: l_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
2598: l_child_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
2599: l_hier EDW_OWB_COLLECTION_UTIL.varcharTableType;
2600: l_number_ltc number;
2601: Begin
2594: l_top_level varchar2(200);
2595: l_hier_hold varchar2(200);
2596: l_child_hold varchar2(200);
2597: l_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
2598: l_child_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
2599: l_hier EDW_OWB_COLLECTION_UTIL.varcharTableType;
2600: l_number_ltc number;
2601: Begin
2602: l_stmt:='select parent_level.level_name||''_LTC'',child_level.level_name||''_LTC'', hier.hier_name '||
2595: l_hier_hold varchar2(200);
2596: l_child_hold varchar2(200);
2597: l_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
2598: l_child_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
2599: l_hier EDW_OWB_COLLECTION_UTIL.varcharTableType;
2600: l_number_ltc number;
2601: Begin
2602: l_stmt:='select parent_level.level_name||''_LTC'',child_level.level_name||''_LTC'', hier.hier_name '||
2603: 'from '||
2632: --see what all levels are allowed and allocate them
2633: --g_number_levels
2634: g_number_ltc:=0;
2635: for i in 1..l_number_ltc loop
2636: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_levels,g_number_levels,l_parent_ltc(i)) and
2637: EDW_OWB_COLLECTION_UTIL.value_in_table(g_levels,g_number_levels,l_child_ltc(i)) then
2638: g_number_ltc:=g_number_ltc+1;
2639: g_parent_ltc(g_number_ltc):=l_parent_ltc(i);
2640: g_child_ltc(g_number_ltc):=l_child_ltc(i);
2633: --g_number_levels
2634: g_number_ltc:=0;
2635: for i in 1..l_number_ltc loop
2636: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_levels,g_number_levels,l_parent_ltc(i)) and
2637: EDW_OWB_COLLECTION_UTIL.value_in_table(g_levels,g_number_levels,l_child_ltc(i)) then
2638: g_number_ltc:=g_number_ltc+1;
2639: g_parent_ltc(g_number_ltc):=l_parent_ltc(i);
2640: g_child_ltc(g_number_ltc):=l_child_ltc(i);
2641: g_hier(g_number_ltc):=l_hier(i);
2648: end loop;
2649: end if;
2650: g_number_distinct_hier:=0;
2651: for i in 1..g_number_ltc loop
2652: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_distinct_hier,g_number_distinct_hier,g_hier(i))=false then
2653: g_number_distinct_hier:=g_number_distinct_hier+1;
2654: g_distinct_hier(g_number_distinct_hier):=g_hier(i);
2655: end if;
2656: end loop;
2698: write_to_log_file_n(g_status_message);
2699: return false;
2700: End;
2701:
2702: function get_diamond_tops(p_parent_level varchar2,p_diamond_tops out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2703: p_number_diamond_tops out NOCOPY number) return boolean is
2704: Begin
2705: p_number_diamond_tops:=0;
2706: for i in 1..g_number_diamond_level loop
2759: return false;
2760: End;
2761:
2762: function get_way_to_child(p_parent_level varchar2,p_child_level varchar2,
2763: p_parent_level_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2764: p_child_level_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2765: p_parent_pk_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2766: p_child_fk_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2767: p_number_level_order out NOCOPY number) return boolean is
2760: End;
2761:
2762: function get_way_to_child(p_parent_level varchar2,p_child_level varchar2,
2763: p_parent_level_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2764: p_child_level_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2765: p_parent_pk_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2766: p_child_fk_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2767: p_number_level_order out NOCOPY number) return boolean is
2768: l_cost EDW_OWB_COLLECTION_UTIL.numberTableType;
2761:
2762: function get_way_to_child(p_parent_level varchar2,p_child_level varchar2,
2763: p_parent_level_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2764: p_child_level_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2765: p_parent_pk_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2766: p_child_fk_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2767: p_number_level_order out NOCOPY number) return boolean is
2768: l_cost EDW_OWB_COLLECTION_UTIL.numberTableType;
2769: l_parent varchar2(200);
2762: function get_way_to_child(p_parent_level varchar2,p_child_level varchar2,
2763: p_parent_level_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2764: p_child_level_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2765: p_parent_pk_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2766: p_child_fk_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2767: p_number_level_order out NOCOPY number) return boolean is
2768: l_cost EDW_OWB_COLLECTION_UTIL.numberTableType;
2769: l_parent varchar2(200);
2770: l_child varchar2(200);
2764: p_child_level_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2765: p_parent_pk_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2766: p_child_fk_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2767: p_number_level_order out NOCOPY number) return boolean is
2768: l_cost EDW_OWB_COLLECTION_UTIL.numberTableType;
2769: l_parent varchar2(200);
2770: l_child varchar2(200);
2771: l_found boolean;
2772: l_level_in_hier EDW_OWB_COLLECTION_UTIL.booleanTableType;--both parent and child in the hier
2768: l_cost EDW_OWB_COLLECTION_UTIL.numberTableType;
2769: l_parent varchar2(200);
2770: l_child varchar2(200);
2771: l_found boolean;
2772: l_level_in_hier EDW_OWB_COLLECTION_UTIL.booleanTableType;--both parent and child in the hier
2773: l_parent_found boolean;
2774: l_child_found boolean;
2775: l_min number;
2776: l_min_hier number;
2842: end if;
2843: end if;
2844: end loop;
2845: if l_min_hier=0 then
2846: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_NO_MIN_COST_HIER');
2847: g_status:=false;
2848: write_to_log_file_n(g_status_message);
2849: return false;
2850: end if;
2896:
2897: function create_child_dia_fk_table(
2898: p_parent_level varchar2,
2899: p_child_level varchar2,
2900: p_parent_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2901: p_child_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2902: p_parent_pk_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2903: p_child_fk_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2904: p_number_level_order number,
2897: function create_child_dia_fk_table(
2898: p_parent_level varchar2,
2899: p_child_level varchar2,
2900: p_parent_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2901: p_child_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2902: p_parent_pk_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2903: p_child_fk_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2904: p_number_level_order number,
2905: p_ilog varchar2,
2898: p_parent_level varchar2,
2899: p_child_level varchar2,
2900: p_parent_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2901: p_child_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2902: p_parent_pk_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2903: p_child_fk_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2904: p_number_level_order number,
2905: p_ilog varchar2,
2906: p_other_fks EDW_OWB_COLLECTION_UTIL.varcharTableType,
2899: p_child_level varchar2,
2900: p_parent_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2901: p_child_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2902: p_parent_pk_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2903: p_child_fk_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2904: p_number_level_order number,
2905: p_ilog varchar2,
2906: p_other_fks EDW_OWB_COLLECTION_UTIL.varcharTableType,
2907: p_below_diamond_flag EDW_OWB_COLLECTION_UTIL.booleanTableType,
2902: p_parent_pk_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2903: p_child_fk_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2904: p_number_level_order number,
2905: p_ilog varchar2,
2906: p_other_fks EDW_OWB_COLLECTION_UTIL.varcharTableType,
2907: p_below_diamond_flag EDW_OWB_COLLECTION_UTIL.booleanTableType,
2908: p_number_other_fks number,
2909: p_diamond_fk_table out NOCOPY varchar2) return boolean is
2910: l_diamond_table varchar2(200);
2903: p_child_fk_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2904: p_number_level_order number,
2905: p_ilog varchar2,
2906: p_other_fks EDW_OWB_COLLECTION_UTIL.varcharTableType,
2907: p_below_diamond_flag EDW_OWB_COLLECTION_UTIL.booleanTableType,
2908: p_number_other_fks number,
2909: p_diamond_fk_table out NOCOPY varchar2) return boolean is
2910: l_diamond_table varchar2(200);
2911: l_max_rownum_table varchar2(200);
2941: l_stmt:=l_stmt||p_parent_level_order(i)||'.'||p_parent_pk_order(i)||'='||p_child_level_order(i)||'.'||
2942: p_child_fk_order(i)||' and ';
2943: end loop;
2944: l_stmt:=l_stmt||p_ilog||'.row_id='||p_parent_level||'.rowid';
2945: if EDW_OWB_COLLECTION_UTIL.drop_table(l_diamond_table)=false then
2946: null;
2947: end if;
2948: if g_debug then
2949: write_to_log_file_n('Going to execute '||l_stmt||get_time);
2951: execute immediate l_stmt;
2952: if g_debug then
2953: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
2954: end if;
2955: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_diamond_table,instr(l_diamond_table,'.')+1,
2956: length(l_diamond_table)),substr(l_diamond_table,1,instr(l_diamond_table,'.')-1));
2957: --get the max of rownum
2958: l_stmt:='create table '||l_max_rownum_table||' tablespace '||g_op_table_space;
2959: if g_parallel is not null then
2960: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2961: end if;
2962: l_stmt:=l_stmt||' as select max(row_num) row_num,'||p_child_fk_order(p_number_level_order)||' from '||
2963: l_diamond_table||' group by '||p_child_fk_order(p_number_level_order);
2964: if EDW_OWB_COLLECTION_UTIL.drop_table(l_max_rownum_table)=false then
2965: null;
2966: end if;
2967: if g_debug then
2968: write_to_log_file_n('Going to execute '||l_stmt||get_time);
2970: execute immediate l_stmt;
2971: if g_debug then
2972: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
2973: end if;
2974: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_max_rownum_table,instr(l_max_rownum_table,'.')+1,
2975: length(l_max_rownum_table)),substr(l_max_rownum_table,1,instr(l_max_rownum_table,'.')-1));
2976: l_stmt:='create table '||p_diamond_fk_table||' tablespace '||g_op_table_space;
2977: if g_parallel is not null then
2978: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2984: end if;
2985: end loop;
2986: l_stmt:=l_stmt||' from '||l_max_rownum_table||' B,'||l_diamond_table||' A where A.row_num=B.row_num and '||
2987: 'A.'||p_child_fk_order(p_number_level_order)||'=B.'||p_child_fk_order(p_number_level_order);
2988: if EDW_OWB_COLLECTION_UTIL.drop_table(p_diamond_fk_table)=false then
2989: null;
2990: end if;
2991: if g_debug then
2992: write_to_log_file_n('Going to execute '||l_stmt||get_time);
2994: execute immediate l_stmt;
2995: if g_debug then
2996: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
2997: end if;
2998: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(p_diamond_fk_table,instr(p_diamond_fk_table,'.')+1,
2999: length(p_diamond_fk_table)),substr(p_diamond_fk_table,1,instr(p_diamond_fk_table,'.')-1));
3000: if EDW_OWB_COLLECTION_UTIL.drop_table(l_diamond_table)=false then
3001: null;
3002: end if;
2996: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
2997: end if;
2998: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(p_diamond_fk_table,instr(p_diamond_fk_table,'.')+1,
2999: length(p_diamond_fk_table)),substr(p_diamond_fk_table,1,instr(p_diamond_fk_table,'.')-1));
3000: if EDW_OWB_COLLECTION_UTIL.drop_table(l_diamond_table)=false then
3001: null;
3002: end if;
3003: if EDW_OWB_COLLECTION_UTIL.drop_table(l_max_rownum_table)=false then
3004: null;
2999: length(p_diamond_fk_table)),substr(p_diamond_fk_table,1,instr(p_diamond_fk_table,'.')-1));
3000: if EDW_OWB_COLLECTION_UTIL.drop_table(l_diamond_table)=false then
3001: null;
3002: end if;
3003: if EDW_OWB_COLLECTION_UTIL.drop_table(l_max_rownum_table)=false then
3004: null;
3005: end if;
3006: return true;
3007: Exception when others then
3013:
3014: function merge_all_ilog_tables return boolean is
3015: Begin
3016: for i in 1..g_number_levels loop
3017: if EDW_OWB_COLLECTION_UTIL.merge_all_ilog_tables(
3018: g_level_ilog_name(i),
3019: g_level_ilog_name(i),
3020: g_level_ilog_name(i)||'A',
3021: 'IL',
3021: 'IL',
3022: g_op_table_space,
3023: g_bis_owner,
3024: g_parallel)=false then
3025: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
3026: return false;
3027: end if;
3028: if EDW_OWB_COLLECTION_UTIL.check_table(g_level_ilog_name(i)||'A') then
3029: g_level_ilog(i):=g_level_ilog_name(i)||'A';
3024: g_parallel)=false then
3025: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
3026: return false;
3027: end if;
3028: if EDW_OWB_COLLECTION_UTIL.check_table(g_level_ilog_name(i)||'A') then
3029: g_level_ilog(i):=g_level_ilog_name(i)||'A';
3030: else
3031: g_level_ilog(i):=g_level_ilog_name(i);
3032: end if;
3044: l_owner varchar2(80);
3045: Begin
3046: l_col:='PUSHED_DOWN_FLAG';
3047: for i in 1..g_number_levels loop
3048: l_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(g_levels(i));
3049: if EDW_OWB_COLLECTION_UTIL.check_table_column(g_levels(i),l_owner,l_col)=false then
3050: g_stmt:='alter table '||l_owner||'.'||g_levels(i)||' add ('||l_col||' varchar2(10))';
3051: if g_debug then
3052: write_to_log_file_n(g_stmt);
3045: Begin
3046: l_col:='PUSHED_DOWN_FLAG';
3047: for i in 1..g_number_levels loop
3048: l_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(g_levels(i));
3049: if EDW_OWB_COLLECTION_UTIL.check_table_column(g_levels(i),l_owner,l_col)=false then
3050: g_stmt:='alter table '||l_owner||'.'||g_levels(i)||' add ('||l_col||' varchar2(10))';
3051: if g_debug then
3052: write_to_log_file_n(g_stmt);
3053: end if;
3073: g_level_ilog(p_index):=substr(g_level_ilog(p_index),1,length(g_level_ilog(p_index))-1);
3074: else
3075: g_level_ilog(p_index):=g_level_ilog(p_index)||'A';
3076: end if;
3077: if EDW_OWB_COLLECTION_UTIL.put_rownum_in_ilog_table(
3078: g_level_ilog(p_index),
3079: l_ilog_table,
3080: g_op_table_space,
3081: g_parallel)=false then
3095: write_to_log_file_n('In create_ilog_from_main');
3096: end if;
3097: for i in 1..g_number_levels loop
3098: if g_level_consider(i) and g_levels(i)<>g_lowest_level then
3099: if EDW_OWB_COLLECTION_UTIL.make_ilog_from_main_ilog(
3100: g_level_ilog(i),
3101: g_level_ilog_main(i),
3102: p_low_end,
3103: p_high_end,
3106: g_parallel,
3107: l_ilog_number)=false then
3108: return false;
3109: end if;
3110: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_level_ilog(i),' status=1 ')=2 then
3111: g_skip_ilog_update(i):=true;
3112: end if;
3113: end if;
3114: end loop;
3120: End;
3121:
3122: function set_session_parameters return boolean is
3123: Begin
3124: if EDW_OWB_COLLECTION_UTIL.set_session_parameters(g_hash_area_size,g_sort_area_size,
3125: g_trace,g_parallel)=false then
3126: return false;
3127: end if;
3128: return true;
3313: End;
3314:
3315: function drop_input_tables(p_table_name varchar2) return boolean is
3316: Begin
3317: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name)=false then
3318: null;
3319: end if;
3320: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name||'_LT')=false then
3321: null;
3316: Begin
3317: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name)=false then
3318: null;
3319: end if;
3320: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name||'_LT')=false then
3321: null;
3322: end if;
3323: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name||'_LC')=false then
3324: null;
3319: end if;
3320: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name||'_LT')=false then
3321: null;
3322: end if;
3323: if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name||'_LC')=false then
3324: null;
3325: end if;
3326: return true;
3327: Exception when others then
3336: p_temp_exe_name varchar2,
3337: p_bis_short_name varchar2
3338: ) return boolean is
3339: l_exe_file_name varchar2(200);
3340: l_parameter EDW_OWB_COLLECTION_UTIL.varcharTableType;
3341: l_parameter_value_set EDW_OWB_COLLECTION_UTIL.varcharTableType;
3342: l_number_parameters number;
3343: Begin
3344: l_exe_file_name:='EDW_PUSH_DOWN_DIMS.PUSH_DOWN_ALL_LEVELS';
3337: p_bis_short_name varchar2
3338: ) return boolean is
3339: l_exe_file_name varchar2(200);
3340: l_parameter EDW_OWB_COLLECTION_UTIL.varcharTableType;
3341: l_parameter_value_set EDW_OWB_COLLECTION_UTIL.varcharTableType;
3342: l_number_parameters number;
3343: Begin
3344: l_exe_file_name:='EDW_PUSH_DOWN_DIMS.PUSH_DOWN_ALL_LEVELS';
3345: l_parameter(1):='p_dim_name';
3356: l_parameter_value_set(6):='FND_NUMBER';
3357: l_parameter(7):='p_job_status_table';
3358: l_parameter_value_set(7):='FND_CHAR240';
3359: l_number_parameters:=7;
3360: if EDW_OWB_COLLECTION_UTIL.create_conc_program(
3361: p_temp_conc_name,
3362: p_temp_conc_short_name,
3363: p_temp_exe_name,
3364: l_exe_file_name,