[Home] [Help]
64: g_pre_hook:=p_pre_hook;
65: g_post_hook:=p_post_hook;
66: g_status:=true;
67: g_thread_type:=p_thread_type;
68: EDW_OWB_COLLECTION_UTIL.init_all(g_log_file,null,'bis.edw.loader');
69: write_to_log_file_n('In COLLECT_FACT_MULTI_THREAD'||get_time);
70: if COLLECT_FACT_MULTI_THREAD(p_input_table)=false then
71: g_status:=false;
72: if EDW_OWB_COLLECTION_UTIL.log_into_job_status_table(
68: EDW_OWB_COLLECTION_UTIL.init_all(g_log_file,null,'bis.edw.loader');
69: write_to_log_file_n('In COLLECT_FACT_MULTI_THREAD'||get_time);
70: if COLLECT_FACT_MULTI_THREAD(p_input_table)=false then
71: g_status:=false;
72: if EDW_OWB_COLLECTION_UTIL.log_into_job_status_table(
73: g_job_status_table,
74: g_fact_name,
75: g_job_id,
76: 'ERROR',
78: null;
79: end if;
80: return;
81: else
82: if EDW_OWB_COLLECTION_UTIL.log_into_job_status_table(
83: g_job_status_table,
84: g_fact_name,
85: g_job_id,
86: 'SUCCESS',
100: l_ilog_table varchar2(100);
101: l_dlog_table varchar2(100);
102: l_ilog varchar2(100);
103: l_dlog varchar2(100);
104: l_log_low_end EDW_OWB_COLLECTION_UTIL.numberTableType;
105: l_log_high_end EDW_OWB_COLLECTION_UTIL.numberTableType;
106: l_log_end_count integer;
107: l_job_id EDW_OWB_COLLECTION_UTIL.numberTableType;
108: l_number_jobs number;
101: l_dlog_table varchar2(100);
102: l_ilog varchar2(100);
103: l_dlog varchar2(100);
104: l_log_low_end EDW_OWB_COLLECTION_UTIL.numberTableType;
105: l_log_high_end EDW_OWB_COLLECTION_UTIL.numberTableType;
106: l_log_end_count integer;
107: l_job_id EDW_OWB_COLLECTION_UTIL.numberTableType;
108: l_number_jobs number;
109: -----------------------------------------
103: l_dlog varchar2(100);
104: l_log_low_end EDW_OWB_COLLECTION_UTIL.numberTableType;
105: l_log_high_end EDW_OWB_COLLECTION_UTIL.numberTableType;
106: l_log_end_count integer;
107: l_job_id EDW_OWB_COLLECTION_UTIL.numberTableType;
108: l_number_jobs number;
109: -----------------------------------------
110: l_temp_conc_name varchar2(200);
111: l_temp_conc_short_name varchar2(200);
119: Begin
120: if read_options_table(p_input_table)=false then
121: return false;
122: end if;
123: EDW_OWB_COLLECTION_UTIL.set_conc_program_id(g_conc_id);
124: EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
125: EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
126: EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
127: if set_session_parameters=false then
120: if read_options_table(p_input_table)=false then
121: return false;
122: end if;
123: EDW_OWB_COLLECTION_UTIL.set_conc_program_id(g_conc_id);
124: EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
125: EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
126: EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
127: if set_session_parameters=false then
128: return false;
121: return false;
122: end if;
123: EDW_OWB_COLLECTION_UTIL.set_conc_program_id(g_conc_id);
124: EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
125: EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
126: EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
127: if set_session_parameters=false then
128: return false;
129: end if; --alter session etc
122: end if;
123: EDW_OWB_COLLECTION_UTIL.set_conc_program_id(g_conc_id);
124: EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
125: EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
126: EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
127: if set_session_parameters=false then
128: return false;
129: end if; --alter session etc
130: if g_pre_hook='Y' then
162: write_to_log_file_n('EDW_DERIVED_FACT_FACT_COLLECT.collect_fact done for '||
163: g_src_object||' to '||g_fact_name||'. Time '||get_time);
164: end if;
165: else
166: if EDW_OWB_COLLECTION_UTIL.update_derv_fact_input_table(
167: p_input_table,
168: l_ilog_table,--the g_ilog_name
169: l_dlog_table, --the g_dlog_name
170: g_skip_ilog_update,
180: )=false then
181: return false;
182: end if;
183: --once for ilog and once for dlog
184: if EDW_OWB_COLLECTION_UTIL.find_ok_distribution(
185: l_ilog_table,
186: g_bis_owner,
187: g_max_threads,
188: g_min_job_load_size,
234: );
235: if g_debug then
236: write_to_log_file_n('Concurrent process '||l_job_id(l_number_jobs)||' launched '||get_time);
237: end if;
238: if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(p_input_table,l_job_id(l_number_jobs))=false then
239: return false;
240: end if;
241: if l_job_id(l_number_jobs)<=0 then
242: l_try_serial:=true;
248: l_log_high_end(j)||','''||l_ilog||''','''||l_dlog||''','''||g_log_file||''','''||g_thread_type||''');');
249: if g_debug then
250: write_to_log_file_n('Job '||l_job_id(l_number_jobs)||' launched '||get_time);
251: end if;
252: if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(p_input_table,l_job_id(l_number_jobs))=false then
253: return false;
254: end if;
255: if l_job_id(l_number_jobs)<=0 then
256: l_try_serial:=true;
267: if g_debug then
268: write_to_log_file_n('Attempt serial load');
269: end if;
270: l_job_id(l_number_jobs):=0-l_number_jobs;
271: if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(p_input_table,l_job_id(l_number_jobs))=false then
272: return false;
273: end if;
274: commit;--this commit is very imp
275: EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT(
287: g_thread_type
288: );
289: end if;
290: end loop;
291: if EDW_OWB_COLLECTION_UTIL.wait_on_jobs(
292: l_job_id,
293: l_number_jobs,
294: g_sleep_time,
295: g_thread_type)=false then
294: g_sleep_time,
295: g_thread_type)=false then
296: return false;
297: end if;
298: if EDW_OWB_COLLECTION_UTIL.check_all_child_jobs(
299: g_job_status_table,
300: l_job_id,
301: l_number_jobs,
302: g_fact_name)=false then
308: if g_debug then
309: write_to_log_file_n('Processing DLOG Threads '||get_time);
310: end if;
311: l_log_end_count:=0;
312: if EDW_OWB_COLLECTION_UTIL.find_ok_distribution(
313: l_dlog_table,
314: g_bis_owner,
315: g_max_threads,
316: g_min_job_load_size,
319: l_log_end_count)=false then
320: return false;
321: end if;
322: l_number_jobs:=0;
323: if EDW_OWB_COLLECTION_UTIL.truncate_table(g_job_status_table)=false then
324: null;
325: end if;
326: for j in 1..l_log_end_count loop
327: l_number_jobs:=l_number_jobs+1;
352: );
353: if g_debug then
354: write_to_log_file_n('Concurrent Request '||l_job_id(l_number_jobs)||' launched '||get_time);
355: end if;
356: if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(p_input_table,l_job_id(l_number_jobs))=false then
357: return false;
358: end if;
359: if l_job_id(l_number_jobs)<=0 then
360: l_try_serial:=true;
366: l_log_high_end(j)||','''||l_ilog||''','''||l_dlog||''','''||g_log_file||''','''||g_thread_type||''');');
367: if g_debug then
368: write_to_log_file_n('Job '||l_job_id(l_number_jobs)||' launched '||get_time);
369: end if;
370: if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(p_input_table,l_job_id(l_number_jobs))=false then
371: return false;
372: end if;
373: if l_job_id(l_number_jobs)<=0 then
374: l_try_serial:=true;
385: if g_debug then
386: write_to_log_file_n('Attempt serial load');
387: end if;
388: l_job_id(l_number_jobs):=0-l_number_jobs;
389: if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(p_input_table,l_job_id(l_number_jobs))=false then
390: return false;
391: end if;
392: commit;--this commit is very imp
393: EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT(
405: g_thread_type
406: );
407: end if;
408: end loop;
409: if EDW_OWB_COLLECTION_UTIL.wait_on_jobs(
410: l_job_id,
411: l_number_jobs,
412: g_sleep_time,
413: g_thread_type)=false then
412: g_sleep_time,
413: g_thread_type)=false then
414: return false;
415: end if;
416: if EDW_OWB_COLLECTION_UTIL.check_all_child_jobs(
417: g_job_status_table,
418: l_job_id,
419: l_number_jobs,
420: g_fact_name)=false then
497: g_ilog:=p_ilog;
498: g_dlog:=p_dlog;
499: g_log_file:=p_log_file;
500: g_thread_type:=p_thread_type;
501: EDW_OWB_COLLECTION_UTIL.init_all(g_log_file||'_'||g_job_id||'_'||p_mode,null,'bis.edw.loader');
502: write_to_log_file_n('In COLLECT_FACT p_fact_name='||p_fact_name||',p_input_table='||p_input_table||
503: ',p_job_id='||p_job_id||',p_ilog_low_end='||p_ilog_low_end||',p_ilog_high_end='||p_ilog_high_end||
504: ',p_mode='||p_mode||' p_thread_type='||p_thread_type);
505: if COLLECT_FACT(p_mode,p_input_table,p_ilog_low_end,p_ilog_high_end)=false then
502: write_to_log_file_n('In COLLECT_FACT p_fact_name='||p_fact_name||',p_input_table='||p_input_table||
503: ',p_job_id='||p_job_id||',p_ilog_low_end='||p_ilog_low_end||',p_ilog_high_end='||p_ilog_high_end||
504: ',p_mode='||p_mode||' p_thread_type='||p_thread_type);
505: if COLLECT_FACT(p_mode,p_input_table,p_ilog_low_end,p_ilog_high_end)=false then
506: if EDW_OWB_COLLECTION_UTIL.log_into_job_status_table(
507: g_job_status_table,
508: g_fact_name,
509: g_job_id,
510: 'ERROR',
511: g_status_message)=false then
512: null;
513: end if;
514: else
515: if EDW_OWB_COLLECTION_UTIL.log_into_job_status_table(
516: g_job_status_table,
517: g_fact_name,
518: g_job_id,
519: 'SUCCESS',
543: if read_options_table(p_input_table)=false then
544: return false;
545: end if;
546: --g_conc_program_id is read from p_table_name
547: EDW_OWB_COLLECTION_UTIL.set_conc_program_id(g_conc_id);
548: EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
549: EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
550: EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
551: if set_session_parameters=false then
544: return false;
545: end if;
546: --g_conc_program_id is read from p_table_name
547: EDW_OWB_COLLECTION_UTIL.set_conc_program_id(g_conc_id);
548: EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
549: EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
550: EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
551: if set_session_parameters=false then
552: return false;
545: end if;
546: --g_conc_program_id is read from p_table_name
547: EDW_OWB_COLLECTION_UTIL.set_conc_program_id(g_conc_id);
548: EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
549: EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
550: EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
551: if set_session_parameters=false then
552: return false;
553: end if;--alter session etc
546: --g_conc_program_id is read from p_table_name
547: EDW_OWB_COLLECTION_UTIL.set_conc_program_id(g_conc_id);
548: EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
549: EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
550: EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
551: if set_session_parameters=false then
552: return false;
553: end if;--alter session etc
554: if p_mode='ILOG' then
584: Begin
585: if g_debug then
586: write_to_log_file_n('In initial_set_up');
587: end if;
588: if EDW_OWB_COLLECTION_UTIL.create_job_status_table(g_job_status_table,g_op_table_space)=false then
589: return false;
590: end if;
591: if initialize(true)=false then
592: return false;
622: l_ilog_old varchar2(200);
623: l_dlog_old varchar2(200);
624: Begin
625: --if the fact has no data then, its full refresh
626: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_fact_name)=1 then
627: g_full_refresh:=true;
628: if g_debug then
629: write_to_log_file_n(g_fact_name||' has no data. full refresh');
630: end if;
636: if g_over then
637: return true;
638: end if;
639: if g_full_refresh=false and g_fresh_restart=false then
640: if EDW_OWB_COLLECTION_UTIL.merge_all_ilog_tables(
641: g_ilog||'_IL',
642: g_ilog,
643: g_ilog||'A',
644: null,
646: g_bis_owner,
647: g_parallel)=false then
648: return false;
649: end if;
650: if EDW_OWB_COLLECTION_UTIL.merge_all_ilog_tables(
651: g_dlog||'_DL',
652: g_dlog,
653: g_dlog||'A',
654: null,
657: g_parallel)=false then
658: return false;
659: end if;
660: end if;
661: if EDW_OWB_COLLECTION_UTIL.check_table(g_ilog||'A') and EDW_OWB_COLLECTION_UTIL.check_table(g_ilog)=false then
662: l_ilog_old:=g_ilog;
663: g_ilog:=g_ilog||'A';
664: end if;
665: if EDW_OWB_COLLECTION_UTIL.check_table(g_dlog||'A') and EDW_OWB_COLLECTION_UTIL.check_table(g_dlog)=false then
661: if EDW_OWB_COLLECTION_UTIL.check_table(g_ilog||'A') and EDW_OWB_COLLECTION_UTIL.check_table(g_ilog)=false then
662: l_ilog_old:=g_ilog;
663: g_ilog:=g_ilog||'A';
664: end if;
665: if EDW_OWB_COLLECTION_UTIL.check_table(g_dlog||'A') and EDW_OWB_COLLECTION_UTIL.check_table(g_dlog)=false then
666: l_dlog_old:=g_dlog;
667: g_dlog:=g_dlog||'A';
668: end if;
669: if g_full_refresh=false and g_fresh_restart=false then
671: return false;
672: end if;
673: end if;
674: if g_full_refresh or g_fresh_restart then
675: if EDW_OWB_COLLECTION_UTIL.drop_prot_tables(g_insert_prot_log,'PI',g_bis_owner)=false then
676: return false;
677: end if;
678: if EDW_OWB_COLLECTION_UTIL.drop_prot_tables(g_update_prot_log,'PU',g_bis_owner)=false then
679: return false;
674: if g_full_refresh or g_fresh_restart then
675: if EDW_OWB_COLLECTION_UTIL.drop_prot_tables(g_insert_prot_log,'PI',g_bis_owner)=false then
676: return false;
677: end if;
678: if EDW_OWB_COLLECTION_UTIL.drop_prot_tables(g_update_prot_log,'PU',g_bis_owner)=false then
679: return false;
680: end if;
681: if EDW_OWB_COLLECTION_UTIL.drop_prot_tables(g_delete_prot_log,'PD',g_bis_owner)=false then
682: return false;
677: end if;
678: if EDW_OWB_COLLECTION_UTIL.drop_prot_tables(g_update_prot_log,'PU',g_bis_owner)=false then
679: return false;
680: end if;
681: if EDW_OWB_COLLECTION_UTIL.drop_prot_tables(g_delete_prot_log,'PD',g_bis_owner)=false then
682: return false;
683: end if;
684: if drop_prot_tables=false then
685: null;
688: /*
689: the position of the next two stmt is very imp. dont put it before merge_all_ilog_tables!!
690: */
691: if g_fresh_restart then
692: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
693: null;
694: end if;
695: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog)=false then
696: null;
691: if g_fresh_restart then
692: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
693: null;
694: end if;
695: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog)=false then
696: null;
697: end if;
698: if drop_ilog_dlog_tables(g_ilog_name,g_dlog_name)=false then
699: return false;
847: p_fact_id number,
848: p_mapping_id number,
849: p_src_object varchar2,
850: p_src_object_id number,
851: p_fact_fks EDW_OWB_COLLECTION_UTIL.varcharTableType,
852: p_higher_level EDW_OWB_COLLECTION_UTIL.booleanTableType,
853: p_parent_dim EDW_OWB_COLLECTION_UTIL.varcharTableType,
854: p_parent_level EDW_OWB_COLLECTION_UTIL.varcharTableType,
855: p_level_prefix EDW_OWB_COLLECTION_UTIL.varcharTableType,
848: p_mapping_id number,
849: p_src_object varchar2,
850: p_src_object_id number,
851: p_fact_fks EDW_OWB_COLLECTION_UTIL.varcharTableType,
852: p_higher_level EDW_OWB_COLLECTION_UTIL.booleanTableType,
853: p_parent_dim EDW_OWB_COLLECTION_UTIL.varcharTableType,
854: p_parent_level EDW_OWB_COLLECTION_UTIL.varcharTableType,
855: p_level_prefix EDW_OWB_COLLECTION_UTIL.varcharTableType,
856: p_level_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
849: p_src_object varchar2,
850: p_src_object_id number,
851: p_fact_fks EDW_OWB_COLLECTION_UTIL.varcharTableType,
852: p_higher_level EDW_OWB_COLLECTION_UTIL.booleanTableType,
853: p_parent_dim EDW_OWB_COLLECTION_UTIL.varcharTableType,
854: p_parent_level EDW_OWB_COLLECTION_UTIL.varcharTableType,
855: p_level_prefix EDW_OWB_COLLECTION_UTIL.varcharTableType,
856: p_level_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
857: p_level_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType,
850: p_src_object_id number,
851: p_fact_fks EDW_OWB_COLLECTION_UTIL.varcharTableType,
852: p_higher_level EDW_OWB_COLLECTION_UTIL.booleanTableType,
853: p_parent_dim EDW_OWB_COLLECTION_UTIL.varcharTableType,
854: p_parent_level EDW_OWB_COLLECTION_UTIL.varcharTableType,
855: p_level_prefix EDW_OWB_COLLECTION_UTIL.varcharTableType,
856: p_level_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
857: p_level_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType,
858: p_dim_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType,
851: p_fact_fks EDW_OWB_COLLECTION_UTIL.varcharTableType,
852: p_higher_level EDW_OWB_COLLECTION_UTIL.booleanTableType,
853: p_parent_dim EDW_OWB_COLLECTION_UTIL.varcharTableType,
854: p_parent_level EDW_OWB_COLLECTION_UTIL.varcharTableType,
855: p_level_prefix EDW_OWB_COLLECTION_UTIL.varcharTableType,
856: p_level_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
857: p_level_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType,
858: p_dim_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType,
859: p_number_fact_fks number,
852: p_higher_level EDW_OWB_COLLECTION_UTIL.booleanTableType,
853: p_parent_dim EDW_OWB_COLLECTION_UTIL.varcharTableType,
854: p_parent_level EDW_OWB_COLLECTION_UTIL.varcharTableType,
855: p_level_prefix EDW_OWB_COLLECTION_UTIL.varcharTableType,
856: p_level_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
857: p_level_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType,
858: p_dim_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType,
859: p_number_fact_fks number,
860: p_conc_id number,
853: p_parent_dim EDW_OWB_COLLECTION_UTIL.varcharTableType,
854: p_parent_level EDW_OWB_COLLECTION_UTIL.varcharTableType,
855: p_level_prefix EDW_OWB_COLLECTION_UTIL.varcharTableType,
856: p_level_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
857: p_level_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType,
858: p_dim_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType,
859: p_number_fact_fks number,
860: p_conc_id number,
861: p_conc_program_name varchar2,
854: p_parent_level EDW_OWB_COLLECTION_UTIL.varcharTableType,
855: p_level_prefix EDW_OWB_COLLECTION_UTIL.varcharTableType,
856: p_level_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
857: p_level_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType,
858: p_dim_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType,
859: p_number_fact_fks number,
860: p_conc_id number,
861: p_conc_program_name varchar2,
862: p_debug boolean,
870: p_dlog varchar2,
871: p_forall_size number,
872: p_update_type varchar2,
873: p_fact_dlog varchar2,
874: p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
875: p_number_skip_cols number,
876: p_load_fk number,
877: p_fresh_restart boolean,
878: p_op_table_space varchar2,
875: p_number_skip_cols number,
876: p_load_fk number,
877: p_fresh_restart boolean,
878: p_op_table_space varchar2,
879: p_bu_tables EDW_OWB_COLLECTION_UTIL.varcharTableType,--before update tables.prop dim change to derv
880: p_bu_dimensions EDW_OWB_COLLECTION_UTIL.varcharTableType,
881: p_number_bu_tables number,
882: p_bu_src_fact varchar2,--what table to look at as the src fact. if null, scan full the src fact
883: p_load_mode varchar2,
876: p_load_fk number,
877: p_fresh_restart boolean,
878: p_op_table_space varchar2,
879: p_bu_tables EDW_OWB_COLLECTION_UTIL.varcharTableType,--before update tables.prop dim change to derv
880: p_bu_dimensions EDW_OWB_COLLECTION_UTIL.varcharTableType,
881: p_number_bu_tables number,
882: p_bu_src_fact varchar2,--what table to look at as the src fact. if null, scan full the src fact
883: p_load_mode varchar2,
884: p_rollback varchar2,
1077: l_status:=2;
1078: g_skip_ilog_update:=false;
1079: end if;
1080: else
1081: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_ilog,'status=1')=2 then
1082: l_status:=2;
1083: else
1084: l_status:=set_gilog_status;
1085: end if;
1173: l_status:=2;
1174: g_skip_dlog_update:=false;
1175: end if;
1176: else
1177: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_dlog,'status=1')=2 then
1178: l_status:=2;
1179: else
1180: l_status:=set_gdlog_status;
1181: end if;
1253: Begin
1254: if g_debug then
1255: write_to_log_file_n('In move_ddata_into_derived_fact');
1256: end if;
1257: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_delete_rowid_table) = 2 then
1258: insert_into_load_progress_d(g_load_fk,g_fact_name,'Update Derived/Summary Fact with Delete Data'||g_jobid_stmt,sysdate,
1259: null,'DF','UPDATE','DFDDEL'||p_count||g_jobid_stmt,'I');
1260: if delete_into_fact = false then
1261: write_to_log_file_n('delete_into_fact returned with false');
1276: Begin
1277: if g_debug then
1278: write_to_log_file_n('In move_data_into_derived_fact');
1279: end if;
1280: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_insert_rowid_table)=2 then
1281: insert_into_load_progress_d(g_load_fk,g_fact_name,'Insert Into Derived/Summary Fact'||g_jobid_stmt,sysdate,null,'DF',
1282: 'INSERT','DFDINS'||p_count||g_jobid_stmt,'I');
1283: if insert_into_fact = false then
1284: write_to_log_file_n('insert_into_fact returned with false');
1289: end if;
1290: if drop_insert_lock_table=false then
1291: return false;
1292: end if;
1293: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_update_rowid_table) =2 then
1294: insert_into_load_progress_d(g_load_fk,g_fact_name,'Update Derived/Summary Fact'||g_jobid_stmt,sysdate,null,'DF',
1295: 'UPDATE','DFDUPD'||p_count||g_jobid_stmt,'I');
1296: if update_into_fact = false then
1297: write_to_log_file_n('update_into_fact returned with false');
1315: Begin
1316: if g_debug then
1317: write_to_log_file_n('In create_gilog_T');
1318: end if;
1319: if EDW_OWB_COLLECTION_UTIL.drop_table(p_ilog_temp)=false then
1320: null;
1321: end if;
1322: if g_src_snplog_has_pk then
1323: --l_stmt:='create table '||p_ilog_temp||'(row_id varchar2(255),'||g_src_pk||' number) tablespace '||g_op_table_space;
1355: if g_debug then
1356: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1357: end if;
1358: begin
1359: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1360: execute immediate l_stmt;
1361: commit;
1362: if g_debug then
1363: write_to_log_file_n('Moved '||sql%rowcount||' rows into '||p_ilog_temp||get_time);
1362: if g_debug then
1363: write_to_log_file_n('Moved '||sql%rowcount||' rows into '||p_ilog_temp||get_time);
1364: end if;
1365: if sql%rowcount > 0 then
1366: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(p_ilog_temp,instr(p_ilog_temp,'.')+1,
1367: length(p_ilog_temp)),substr(p_ilog_temp,1,instr(p_ilog_temp,'.')-1));
1368: end if;
1369: exception when others then
1370: g_status_message:=sqlerrm;
1415: write_to_log_file_n('In move_data_into_local_ilog');
1416: end if;
1417: l_src_object_ilog:=g_ilog||'SRC';
1418: if g_full_refresh then
1419: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
1420: write_to_log_file_n('Table '||g_ilog||' not found for dropping');
1421: end if;
1422: l_ilog_found:=false;
1423: l_ilog_has_data:=false;
1421: end if;
1422: l_ilog_found:=false;
1423: l_ilog_has_data:=false;
1424: else
1425: if EDW_OWB_COLLECTION_UTIL.check_table(g_ilog) = false then
1426: l_ilog_found:=false;
1427: else
1428: l_ilog_found:=true;
1429: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_ilog) = 1 then
1425: if EDW_OWB_COLLECTION_UTIL.check_table(g_ilog) = false then
1426: l_ilog_found:=false;
1427: else
1428: l_ilog_found:=true;
1429: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_ilog) = 1 then
1430: l_ilog_has_data:=false;
1431: l_ilog_found:=false;
1432: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
1433: write_to_log_file_n('Table '||g_ilog||' not found for dropping');
1428: l_ilog_found:=true;
1429: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_ilog) = 1 then
1430: l_ilog_has_data:=false;
1431: l_ilog_found:=false;
1432: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
1433: write_to_log_file_n('Table '||g_ilog||' not found for dropping');
1434: end if;
1435: else
1436: l_ilog_has_data:=true;
1487: l_stmt1:=l_stmt1||' distinct m_row$$ row_id,'||g_src_pk||' from '||g_src_object_ilog;
1488: else
1489: l_stmt1:=l_stmt1||' distinct m_row$$ row_id from '||g_src_object_ilog;
1490: end if;
1491: if EDW_OWB_COLLECTION_UTIL.drop_table(l_src_object_ilog)=false then
1492: null;
1493: end if;
1494: if g_debug then
1495: write_to_log_file_n('Going to execute '||l_stmt1||get_time);
1497: execute immediate l_stmt1;
1498: if g_debug then
1499: write_to_log_file_n('Created '||l_src_object_ilog||' with '||sql%rowcount||' records'||get_time);
1500: end if;
1501: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_src_object_ilog,instr(l_src_object_ilog,'.')+1,
1502: length(l_src_object_ilog)), substr(l_src_object_ilog,1,instr(l_src_object_ilog,'.')-1));
1503: if g_collection_size>0 and p_multi_thread=false then
1504: if g_src_snplog_has_pk then
1505: l_stmt:=l_stmt||' chartorowid(row_id) row_id,'||g_src_pk||',decode(sign(rownum-'||
1524: end if;
1525: end if;
1526: end if;
1527: else
1528: if EDW_OWB_COLLECTION_UTIL.check_table_column(g_ilog,'round') then
1529: l_round_found:=true;
1530: else
1531: l_round_found:=false;
1532: end if;
1541: end if;
1542: if g_src_object_count is null then
1543: g_src_object_count:=get_base_fact_count;
1544: end if;
1545: l_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_ilog,g_bis_owner);
1546: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_ilog_count,g_src_object_count,g_src_join_nl_percentage);
1547: l_stmt:=l_stmt||' as select /*+ORDERED ';
1548: if l_use_nl then
1549: l_stmt:=l_stmt||'use_nl(B)';
1542: if g_src_object_count is null then
1543: g_src_object_count:=get_base_fact_count;
1544: end if;
1545: l_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_ilog,g_bis_owner);
1546: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_ilog_count,g_src_object_count,g_src_join_nl_percentage);
1547: l_stmt:=l_stmt||' as select /*+ORDERED ';
1548: if l_use_nl then
1549: l_stmt:=l_stmt||'use_nl(B)';
1550: end if;
1566: else
1567: l_stmt:=l_stmt||' A.row_id,A.status,0 round from '||g_ilog||' A,'||g_src_object||' B where A.row_id=B.rowid';
1568: end if;
1569: end if;
1570: if EDW_OWB_COLLECTION_UTIL.drop_table(l_ilog_el)=false then
1571: null;
1572: end if;
1573: if g_debug then
1574: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1576: execute immediate l_stmt;
1577: if g_debug then
1578: write_to_log_file_n('Created '||l_ilog_el||' with '||sql%rowcount||' records'||get_time);
1579: end if;
1580: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_ilog_el,instr(l_ilog_el,'.')+1,length(l_ilog_el)),
1581: substr(l_ilog_el,1,instr(l_ilog_el,'.')-1));
1582: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
1583: null;
1584: end if;
1578: write_to_log_file_n('Created '||l_ilog_el||' with '||sql%rowcount||' records'||get_time);
1579: end if;
1580: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_ilog_el,instr(l_ilog_el,'.')+1,length(l_ilog_el)),
1581: substr(l_ilog_el,1,instr(l_ilog_el,'.')-1));
1582: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
1583: null;
1584: end if;
1585: g_ilog:=l_ilog_el;
1586: l_ilog_temp:=g_ilog||'T';
1610: end if;
1611: if g_debug then
1612: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1613: end if;
1614: if EDW_OWB_COLLECTION_UTIL.drop_table(l_ilog_temp)=false then
1615: null;
1616: end if;
1617: execute immediate l_stmt;
1618: if g_debug then
1617: execute immediate l_stmt;
1618: if g_debug then
1619: write_to_log_file_n('Created '||l_ilog_temp||' with '||sql%rowcount||' records'||get_time);
1620: end if;
1621: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_ilog_temp,instr(l_ilog_temp,'.')+1,length(l_ilog_temp)),
1622: substr(l_ilog_temp,1,instr(l_ilog_temp,'.')-1));
1623: /*
1624: if drop_ilog_index=false then
1625: null;
1634: if g_debug then
1635: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1636: end if;
1637: begin
1638: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1639: execute immediate l_stmt;
1640: if g_debug then
1641: write_to_log_file_n('Inserted '||g_ilog||' with '||sql%rowcount||' records'||get_time);
1642: end if;
1650: write_to_log_file('Error executing '||l_stmt);
1651: return false;
1652: end;
1653: if l_ilog_temp is not null then
1654: if EDW_OWB_COLLECTION_UTIL.drop_table(l_ilog_temp)=false then
1655: null;
1656: end if;
1657: end if;
1658: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_ilog,instr(g_ilog,'.')+1,length(g_ilog)),
1654: if EDW_OWB_COLLECTION_UTIL.drop_table(l_ilog_temp)=false then
1655: null;
1656: end if;
1657: end if;
1658: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_ilog,instr(g_ilog,'.')+1,length(g_ilog)),
1659: substr(g_ilog,1,instr(g_ilog,'.')-1));
1660: commit;
1661: if EDW_OWB_COLLECTION_UTIL.drop_table(l_src_object_ilog)=false then
1662: null;
1657: end if;
1658: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_ilog,instr(g_ilog,'.')+1,length(g_ilog)),
1659: substr(g_ilog,1,instr(g_ilog,'.')-1));
1660: commit;
1661: if EDW_OWB_COLLECTION_UTIL.drop_table(l_src_object_ilog)=false then
1662: null;
1663: end if;
1664: return true;
1665: Exception when others then
1701: Begin
1702: if g_debug then
1703: write_to_log_file_n('In move_data_into_local_dlog');
1704: end if;
1705: if EDW_OWB_COLLECTION_UTIL.check_table_column(g_src_object_dlog,'PK_KEY') then
1706: l_pk_key_found:=true;
1707: else
1708: l_pk_key_found:=false;
1709: end if;
1706: l_pk_key_found:=true;
1707: else
1708: l_pk_key_found:=false;
1709: end if;
1710: if EDW_OWB_COLLECTION_UTIL.check_table(g_dlog) = false then
1711: l_dlog_found:=false;
1712: else
1713: l_dlog_found:=true;
1714: if EDW_OWB_COLLECTION_UTIL.check_table_column(g_dlog,'pk_key')=false then
1710: if EDW_OWB_COLLECTION_UTIL.check_table(g_dlog) = false then
1711: l_dlog_found:=false;
1712: else
1713: l_dlog_found:=true;
1714: if EDW_OWB_COLLECTION_UTIL.check_table_column(g_dlog,'pk_key')=false then
1715: l_dlog_found:=false;
1716: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog)=false then
1717: null;
1718: end if;
1712: else
1713: l_dlog_found:=true;
1714: if EDW_OWB_COLLECTION_UTIL.check_table_column(g_dlog,'pk_key')=false then
1715: l_dlog_found:=false;
1716: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog)=false then
1717: null;
1718: end if;
1719: else
1720: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_dlog) = 1 then
1716: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog)=false then
1717: null;
1718: end if;
1719: else
1720: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_dlog) = 1 then
1721: l_dlog_found:=false;
1722: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog)=false then
1723: write_to_log_file_n('Table '||g_dlog||' not found for dropping');
1724: end if;
1718: end if;
1719: else
1720: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_dlog) = 1 then
1721: l_dlog_found:=false;
1722: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog)=false then
1723: write_to_log_file_n('Table '||g_dlog||' not found for dropping');
1724: end if;
1725: end if;
1726: end if;
1762: end if;
1763: end if;
1764: else
1765: --recreate the D table
1766: if EDW_OWB_COLLECTION_UTIL.check_table_column(g_src_object_dlog,'PK_KEY') then
1767: if substr(g_dlog,length(g_dlog),1)='A' then
1768: l_dlog_el:=substr(g_dlog,1,length(g_dlog)-1);
1769: else
1770: l_dlog_el:=g_dlog||'A';
1774: if g_parallel is not null then
1775: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
1776: end if;
1777: if g_src_object_dlog_count is null then
1778: g_src_object_dlog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_src_object_dlog,null);
1779: end if;
1780: l_dlog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_dlog,g_bis_owner);
1781: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_dlog_count,g_src_object_dlog_count,g_src_join_nl_percentage);
1782: l_stmt:=l_stmt||' as select /*+ORDERED ';
1776: end if;
1777: if g_src_object_dlog_count is null then
1778: g_src_object_dlog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_src_object_dlog,null);
1779: end if;
1780: l_dlog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_dlog,g_bis_owner);
1781: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_dlog_count,g_src_object_dlog_count,g_src_join_nl_percentage);
1782: l_stmt:=l_stmt||' as select /*+ORDERED ';
1783: if l_use_nl then
1784: l_stmt:=l_stmt||'use_nl(B)';
1777: if g_src_object_dlog_count is null then
1778: g_src_object_dlog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_src_object_dlog,null);
1779: end if;
1780: l_dlog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_dlog,g_bis_owner);
1781: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_dlog_count,g_src_object_dlog_count,g_src_join_nl_percentage);
1782: l_stmt:=l_stmt||' as select /*+ORDERED ';
1783: if l_use_nl then
1784: l_stmt:=l_stmt||'use_nl(B)';
1785: end if;
1788: l_stmt:=l_stmt||' /*+PARALLEL(B,'||g_parallel||')*/ ';
1789: end if;
1790: l_stmt:=l_stmt||'B.rowid row_id,B.row_id row_id1,A.status,A.pk_key,A.round from '||
1791: g_dlog||' A,'||g_src_object_dlog||' B where A.pk_key=B.pk_key and A.round=B.round';
1792: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dlog_el)=false then
1793: null;
1794: end if;
1795: if g_debug then
1796: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1798: execute immediate l_stmt;
1799: if g_debug then
1800: write_to_log_file_n('Created '||l_dlog_el||' with '||sql%rowcount||' records'||get_time);
1801: end if;
1802: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dlog_el,instr(l_dlog_el,'.')+1,length(l_dlog_el)),
1803: substr(l_dlog_el,1,instr(l_dlog_el,'.')-1));
1804: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog)=false then
1805: null;
1806: end if;
1800: write_to_log_file_n('Created '||l_dlog_el||' with '||sql%rowcount||' records'||get_time);
1801: end if;
1802: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dlog_el,instr(l_dlog_el,'.')+1,length(l_dlog_el)),
1803: substr(l_dlog_el,1,instr(l_dlog_el,'.')-1));
1804: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog)=false then
1805: null;
1806: end if;
1807: g_dlog:=l_dlog_el;
1808: end if;
1805: null;
1806: end if;
1807: g_dlog:=l_dlog_el;
1808: end if;
1809: if EDW_OWB_COLLECTION_UTIL.check_table_column(g_dlog,'row_id1') then
1810: l_rowid1_found:=true;
1811: else
1812: l_rowid1_found:=false;
1813: end if;
1827: l_stmt:=l_stmt||' rowid row_id from '||g_src_object_dlog||' where round=0';
1828: else
1829: l_stmt:=l_stmt||' rowid row_id from '||g_src_object_dlog;
1830: end if;
1831: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dlog)=false then
1832: null;
1833: end if;
1834: if g_debug then
1835: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1837: execute immediate l_stmt;
1838: if g_debug then
1839: write_to_log_file_n('Created '||l_dlog||' with '||sql%rowcount||' records'||get_time);
1840: end if;
1841: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dlog,instr(l_dlog,'.')+1,length(l_dlog)),
1842: substr(l_dlog,1,instr(l_dlog,'.')-1));
1843: l_stmt:='create table '||l_dlog_temp||' tablespace '||g_op_table_space;
1844: if g_parallel is not null then
1845: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
1845: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
1846: end if;
1847: l_stmt:=l_stmt||' ';
1848: l_stmt:=l_stmt||' as select row_id,0 status from '||l_dlog||' MINUS select row_id,0 status from '||g_dlog;
1849: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dlog_temp)=false then
1850: null;
1851: end if;
1852: if g_debug then
1853: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1855: execute immediate l_stmt;
1856: if g_debug then
1857: write_to_log_file_n('Created '||l_dlog_temp||' with '||sql%rowcount||' records'||get_time);
1858: end if;
1859: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_dlog_temp,instr(l_dlog_temp,'.')+1,length(l_dlog_temp)),
1860: substr(l_dlog_temp,1,instr(l_dlog_temp,'.')-1));
1861: if l_rowid1_found then
1862: l_stmt:='insert into '||g_dlog||'(row_id,row_id1,status,pk_key,round) select ';
1863: if g_parallel is not null then
1876: if g_debug then
1877: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1878: end if;
1879: begin
1880: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1881: execute immediate l_stmt;
1882: if g_debug then
1883: write_to_log_file_n('Inserted '||g_dlog||' with '||sql%rowcount||' records'||get_time);
1884: end if;
1892: write_to_log_file('Error executing '||l_stmt);
1893: return false;
1894: end;
1895: if l_dlog_found then
1896: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dlog)=false then
1897: null;
1898: end if;
1899: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dlog_temp)=false then
1900: null;
1895: if l_dlog_found then
1896: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dlog)=false then
1897: null;
1898: end if;
1899: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dlog_temp)=false then
1900: null;
1901: end if;
1902: end if;
1903: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_dlog,instr(g_dlog,'.')+1,length(g_dlog)),
1899: if EDW_OWB_COLLECTION_UTIL.drop_table(l_dlog_temp)=false then
1900: null;
1901: end if;
1902: end if;
1903: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_dlog,instr(g_dlog,'.')+1,length(g_dlog)),
1904: substr(g_dlog,1,instr(g_dlog,'.')-1));
1905: return true;
1906: Exception when others then
1907: g_status_message:=sqlerrm;
1935: end if;
1936: if g_debug then
1937: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1938: end if;
1939: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1940: execute immediate l_stmt;
1941: l_count:=sql%rowcount;
1942: if g_debug then
1943: write_to_log_file_n('Updated '||l_count||' rows in '||g_ilog||get_time);
1982: l_stmt:=l_stmt||' as select row_id,decode(status,1,2,0,1,2) status,round from '||
1983: g_ilog_prev;
1984: end if;
1985: end if;
1986: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
1987: null;
1988: end if;
1989: if g_debug then
1990: write_to_log_file_n('Going to execute '||l_stmt||get_time);
1992: execute immediate l_stmt;
1993: if g_debug then
1994: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
1995: end if;
1996: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_prev)=false then
1997: null;
1998: end if;
1999: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_ilog,' status=1 ')<2 then
2000: l_count:=0;
1995: end if;
1996: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_prev)=false then
1997: null;
1998: end if;
1999: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_ilog,' status=1 ')<2 then
2000: l_count:=0;
2001: else
2002: l_count:=1;
2003: end if;
2062: l_dlog_prev:=g_dlog_prev;
2063: g_dlog_prev:=g_dlog;
2064: g_dlog:=l_dlog_prev;
2065: end if;
2066: l_pk_key_found:=EDW_OWB_COLLECTION_UTIL.check_table_column(g_dlog_prev,'pk_key');
2067: l_stmt:='create table '||g_dlog||' tablespace '||g_op_table_space;
2068: l_stmt:=l_stmt||' storage (initial 4M next 4M pctincrease 0) ';
2069: if g_parallel is not null then
2070: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2086: l_stmt:=l_stmt||' as select row_id,decode(status,1,2,0,1,2) status from '||
2087: g_dlog_prev;
2088: end if;
2089: end if;
2090: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog)=false then
2091: null;
2092: end if;
2093: if g_debug then
2094: write_to_log_file_n('Going to execute '||l_stmt||get_time);
2096: execute immediate l_stmt;
2097: if g_debug then
2098: write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
2099: end if;
2100: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog_prev)=false then
2101: null;
2102: end if;
2103: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_dlog,' status=1 ')<2 then
2104: l_count:=0;
2099: end if;
2100: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog_prev)=false then
2101: null;
2102: end if;
2103: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_dlog,' status=1 ')<2 then
2104: l_count:=0;
2105: else
2106: l_count:=1;
2107: end if;
2128: write_to_log_file_n('In get_ilog_dlog');
2129: end if;
2130: l_ilog_desc :='SNAPSHOT-LOG';
2131: l_dlog_desc :='Delete Log';
2132: g_src_object_ilog:=EDW_OWB_COLLECTION_UTIL.get_log_for_table(g_src_object,l_ilog_desc);
2133: if g_src_object_ilog is null then
2134: null;
2135: end if;
2136: if g_fact_dlog is null then
2133: if g_src_object_ilog is null then
2134: null;
2135: end if;
2136: if g_fact_dlog is null then
2137: g_src_object_dlog:=EDW_OWB_COLLECTION_UTIL.get_log_for_table(g_src_object,l_dlog_desc);
2138: else
2139: g_src_object_dlog:=g_fact_dlog;
2140: end if;
2141: if g_src_object_dlog is null then
2156: TYPE CurTyp IS REF CURSOR;
2157: cv CurTyp;
2158: l_stmt varchar2(5000);
2159: begin
2160: if EDW_OWB_COLLECTION_UTIL.get_ltc_fact_unique_key(g_src_object_id,null,g_src_uk,g_src_pk)=false then
2161: return false;
2162: end if;
2163: l_stmt:='select fk_item.column_name from edw_foreign_keys_md_v fk, '||
2164: 'edw_pvt_key_columns_md_v isu, '||
2192: End;
2193:
2194: function get_mapping_details return boolean is
2195: begin
2196: if EDW_OWB_COLLECTION_UTIL.get_derv_mapping_details(
2197: g_mapping_id,
2198: g_src_object_id,
2199: g_number_skip_cols,
2200: g_skip_cols,
2221: g_number_input_params,
2222: g_output_params,
2223: g_input_params,
2224: g_filter_stmt)=false then
2225: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2226: return false;
2227: end if;
2228: return true;
2229: Exception when others then
2284: if g_src_object_count is null then
2285: g_src_object_count:=get_base_fact_count;
2286: end if;
2287: if g_skip_ilog=false and g_bu_src_fact is null then
2288: l_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_ilog_small,g_bis_owner);
2289: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_ilog_count,g_src_object_count,g_src_join_nl_percentage);
2290: else
2291: l_use_nl:=false;
2292: end if;
2285: g_src_object_count:=get_base_fact_count;
2286: end if;
2287: if g_skip_ilog=false and g_bu_src_fact is null then
2288: l_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_ilog_small,g_bis_owner);
2289: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_ilog_count,g_src_object_count,g_src_join_nl_percentage);
2290: else
2291: l_use_nl:=false;
2292: end if;
2293: if p_use_ordered_hint then
2337: if g_number_sec_sources > 0 then
2338: for i in 1..g_number_sec_sources loop
2339: if g_number_bu_tables>0 then
2340: l_index:=0;
2341: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_bu_dimensions,g_number_bu_tables,g_sec_sources(i));
2342: if l_index > 0 then
2343: l_stmt:=l_stmt||','||g_bu_tables(l_index)||' '||g_sec_sources_alias(i);
2344: else
2345: l_stmt:=l_stmt||','||g_sec_sources(i)||' '||g_sec_sources_alias(i);
2401: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2402: end if;
2403: l_stmt:=l_stmt||' ';
2404: l_stmt:=l_stmt||' as select row_id from '||g_ilog||' where status=1';
2405: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_small)=false then
2406: null;
2407: end if;
2408: if g_debug then
2409: write_to_log_file_n('Going to execute '||l_stmt||get_time);
2411: execute immediate l_stmt;
2412: if g_debug then
2413: write_to_log_file_n('Created '||g_ilog_small||' with '||sql%rowcount||' rows '||get_time);
2414: end if;
2415: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_ilog_small,instr(g_ilog_small,'.')+1,
2416: length(g_ilog_small)),substr(g_ilog_small,1,instr(g_ilog_small,'.')-1));
2417: if g_number_sec_sources>0 then
2418: l_stmt:='create table '||g_temp_fact_name_temp||' tablespace '||g_op_table_space;
2419: if g_fact_next_extent is not null then
2432: l_stmt:=l_stmt||' as select ';
2433: if g_src_object_count is null then
2434: g_src_object_count:=get_base_fact_count;
2435: end if;
2436: l_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_ilog_small,g_bis_owner);
2437: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_ilog_count,g_src_object_count,g_src_join_nl_percentage);
2438: if l_use_ordered_hint then
2439: if l_use_nl then
2440: l_stmt:=l_stmt||' /*+ORDERED USE_NL('||g_src_object||')*/ ';
2433: if g_src_object_count is null then
2434: g_src_object_count:=get_base_fact_count;
2435: end if;
2436: l_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_ilog_small,g_bis_owner);
2437: l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_ilog_count,g_src_object_count,g_src_join_nl_percentage);
2438: if l_use_ordered_hint then
2439: if l_use_nl then
2440: l_stmt:=l_stmt||' /*+ORDERED USE_NL('||g_src_object||')*/ ';
2441: else
2446: l_stmt:=l_stmt||' /*+ PARALLEL('||g_src_object||','||g_parallel||')*/ ';
2447: end if;
2448: l_stmt:=l_stmt||g_src_object||'.* from '||g_ilog_small||','||g_src_object||' where '||
2449: g_ilog_small||'.row_id='||g_src_object||'.rowid';
2450: if EDW_OWB_COLLECTION_UTIL.drop_table(g_temp_fact_name_temp)=false then
2451: null;
2452: end if;
2453: if g_debug then
2454: write_to_log_file_n('Going to execute '||l_stmt||get_time);
2469: end;
2470: if g_debug then
2471: write_to_log_file_n('Created '||g_temp_fact_name_temp||' with '||sql%rowcount||' rows '||get_time);
2472: end if;
2473: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_temp_fact_name_temp,instr(g_temp_fact_name_temp,'.')+1,
2474: length(g_temp_fact_name_temp)),substr(g_temp_fact_name_temp,1,instr(g_temp_fact_name_temp,'.')-1));
2475: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_small)=false then
2476: null;
2477: end if;
2471: write_to_log_file_n('Created '||g_temp_fact_name_temp||' with '||sql%rowcount||' rows '||get_time);
2472: end if;
2473: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_temp_fact_name_temp,instr(g_temp_fact_name_temp,'.')+1,
2474: length(g_temp_fact_name_temp)),substr(g_temp_fact_name_temp,1,instr(g_temp_fact_name_temp,'.')-1));
2475: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_small)=false then
2476: null;
2477: end if;
2478: end if;
2479: end if;--if g_skip_ilog=false then
2479: end if;--if g_skip_ilog=false then
2480: if make_data_into_temp(l_use_ordered_hint)=false then
2481: return 0;
2482: end if;
2483: if EDW_OWB_COLLECTION_UTIL.drop_table(g_temp_fact_name)=false then
2484: null;
2485: end if;
2486: if g_debug then
2487: write_to_log_file_n('Going to execute '||g_data_temp_stmt||get_time);
2503: end;
2504: if g_debug then
2505: write_to_log_file_n('Moved '||l_status||' rows into the temp table from the ILOG'||get_time);
2506: end if;
2507: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_temp_fact_name,instr(g_temp_fact_name,'.')+1,
2508: length(g_temp_fact_name)),substr(g_temp_fact_name,1,instr(g_temp_fact_name,'.')-1));
2509: if g_skip_ilog=false then
2510: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_small)=false then
2511: null;
2506: end if;
2507: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_temp_fact_name,instr(g_temp_fact_name,'.')+1,
2508: length(g_temp_fact_name)),substr(g_temp_fact_name,1,instr(g_temp_fact_name,'.')-1));
2509: if g_skip_ilog=false then
2510: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog_small)=false then
2511: null;
2512: end if;
2513: end if;
2514: if g_src_object<>g_temp_fact_name_temp then
2512: end if;
2513: end if;
2514: if g_src_object<>g_temp_fact_name_temp then
2515: if instr(g_temp_fact_name_temp,'.')<>0 then
2516: if EDW_OWB_COLLECTION_UTIL.drop_table(g_temp_fact_name_temp)=false then
2517: null;
2518: end if;
2519: end if;
2520: end if;
2573: if g_number_sec_sources > 0 then
2574: for i in 1..g_number_sec_sources loop
2575: if g_number_bu_tables>0 then
2576: l_index:=0;
2577: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_bu_dimensions,g_number_bu_tables,g_sec_sources(i));
2578: if l_index > 0 then
2579: l_stmt:=l_stmt||','||g_bu_tables(l_index)||' '||g_sec_sources_alias(i);
2580: else
2581: l_stmt:=l_stmt||','||g_sec_sources(i)||' '||g_sec_sources_alias(i);
2626: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2627: end if;
2628: l_stmt:=l_stmt||' ';
2629: l_stmt:=l_stmt||' as select row_id from '||g_dlog||' where status=1';
2630: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog_small)=false then
2631: null;
2632: end if;
2633: if g_debug then
2634: write_to_log_file_n('Going to execute '||l_stmt||get_time);
2636: execute immediate l_stmt;
2637: if g_debug then
2638: write_to_log_file_n('Created '||g_dlog_small||' with '||sql%rowcount||' rows '||get_time);
2639: end if;
2640: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_dlog_small,instr(g_dlog_small,'.')+1,
2641: length(g_dlog_small)),substr(g_dlog_small,1,instr(g_dlog_small,'.')-1));
2642: end if;
2643: if make_delete_data_into_temp=false then
2644: return 0;
2642: end if;
2643: if make_delete_data_into_temp=false then
2644: return 0;
2645: end if;
2646: if EDW_OWB_COLLECTION_UTIL.drop_table(g_temp_fact_name)=false then
2647: null;
2648: end if;
2649: if g_debug then
2650: write_to_log_file_n('Goint to execute '||g_delete_data_temp_stmt);
2657: commit;
2658: if g_debug then
2659: write_to_log_file_n('commit');
2660: end if;
2661: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_temp_fact_name,instr(g_temp_fact_name,'.')+1,
2662: length(g_temp_fact_name)),substr(g_temp_fact_name,1,instr(g_temp_fact_name,'.')-1));
2663: commit;
2664: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog_small)=false then
2665: null;
2660: end if;
2661: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_temp_fact_name,instr(g_temp_fact_name,'.')+1,
2662: length(g_temp_fact_name)),substr(g_temp_fact_name,1,instr(g_temp_fact_name,'.')-1));
2663: commit;
2664: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog_small)=false then
2665: null;
2666: end if;
2667: if l_status >0 then
2668: return 2;
2751: Begin
2752: if g_debug then
2753: write_to_log_file_n('In make_delete_into_fact');
2754: end if;
2755: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_output_params,
2756: g_number_input_params,'LAST_UPDATE_DATE')= false then
2757: l_last_update_date_flag:=true;
2758: else
2759: l_last_update_date_flag:=false;
2757: l_last_update_date_flag:=true;
2758: else
2759: l_last_update_date_flag:=false;
2760: end if;
2761: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_output_params,
2762: g_number_input_params,'CREATION_DATE')= false then
2763: l_creation_date_flag:=true;
2764: else
2765: l_creation_date_flag:=false;
2805: g_delete_stmt:=g_delete_stmt||g_output_params(i)||',';
2806: g_delete_stmt_row:=g_delete_stmt_row||g_output_params(i)||',';
2807: end if;
2808: end loop;
2809: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_output_params,
2810: g_number_input_params,'LAST_UPDATE_DATE')= false then
2811: l_last_update_date_flag:=true;
2812: g_delete_stmt:=g_delete_stmt||'LAST_UPDATE_DATE,';
2813: g_delete_stmt_row:=g_delete_stmt_row||'LAST_UPDATE_DATE,';
2857: Begin
2858: if g_debug then
2859: write_to_log_file_n('In make_update_into_fact');
2860: end if;
2861: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_output_params,
2862: g_number_input_params,'LAST_UPDATE_DATE')= false then
2863: l_last_update_date_flag:=true;
2864: else
2865: l_last_update_date_flag:=false;
2863: l_last_update_date_flag:=true;
2864: else
2865: l_last_update_date_flag:=false;
2866: end if;
2867: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_output_params,
2868: g_number_input_params,'CREATION_DATE')= false then
2869: l_creation_date_flag:=true;
2870: else
2871: l_creation_date_flag:=false;
2975: for i in 1..g_number_df_extra_fks loop
2976: g_insert_stmt:=g_insert_stmt||' '||g_df_extra_fks(i)||',';
2977: end loop;
2978: end if;
2979: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_output_params,
2980: g_number_input_params,'CREATION_DATE')= false then
2981: l_creation_date_flag:=true;
2982: g_insert_stmt:=g_insert_stmt||'CREATION_DATE,';
2983: else
2982: g_insert_stmt:=g_insert_stmt||'CREATION_DATE,';
2983: else
2984: l_creation_date_flag:=false;
2985: end if;
2986: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_output_params,
2987: g_number_input_params,'LAST_UPDATE_DATE')= false then
2988: l_last_update_date_flag:=true;
2989: g_insert_stmt:=g_insert_stmt||'LAST_UPDATE_DATE,';
2990: else
3024: function delete_into_fact return boolean is
3025: l_stmt varchar2(5000);
3026: TYPE CurTyp IS REF CURSOR;
3027: cv CurTyp;
3028: l_rowid EDW_OWB_COLLECTION_UTIL.rowidTableType;
3029: l_count number;
3030: l_total_count number:=0;
3031: l_update_type varchar2(400);
3032: begin
3071: begin
3072: if g_debug then
3073: write_to_log_file_n('Going to execute '||g_delete_stmt||get_time);
3074: end if;
3075: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
3076: execute immediate g_delete_stmt;
3077: l_total_count:=sql%rowcount;
3078: exception when others then
3079: if sqlcode=-4030 then
3103: if g_debug then
3104: write_to_log_file_n('Going to execute '||l_stmt||get_time);
3105: end if;
3106: begin
3107: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
3108: execute immediate l_stmt;
3109: if g_debug then
3110: write_to_log_file('Deleted '||sql%rowcount||' rows'||get_time);
3111: end if;
3118: begin
3119: if g_debug then
3120: write_to_log_file_n('Going to execute '||g_delete_stmt||get_time);
3121: end if;
3122: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
3123: execute immediate g_delete_stmt;--this is actually an insert
3124: l_total_count:=sql%rowcount;
3125: exception when others then
3126: g_status_message:=sqlerrm;
3153: function update_into_fact return boolean is
3154: l_stmt varchar2(5000);
3155: TYPE CurTyp IS REF CURSOR;
3156: cv CurTyp;
3157: l_rowid EDW_OWB_COLLECTION_UTIL.rowidTableType;
3158: l_count number;
3159: l_total_count number:=0;
3160: l_update_type varchar2(400);
3161: begin
3201: if g_debug then
3202: write_to_log_file('Going to execute '||g_update_stmt);
3203: write_to_log_file(get_time);
3204: end if;
3205: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
3206: execute immediate g_update_stmt;
3207: l_total_count:=sql%rowcount;
3208: exception when others then
3209: if sqlcode=-4030 then
3231: if g_debug then
3232: write_to_log_file_n('Going to execute '||l_stmt);
3233: end if;
3234: begin
3235: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
3236: execute immediate l_stmt;
3237: if g_debug then
3238: write_to_log_file('Deleted '||sql%rowcount||' rows');
3239: end if;
3246: begin
3247: if g_debug then
3248: write_to_log_file('Going to execute '||g_update_stmt);
3249: end if;
3250: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
3251: execute immediate g_update_stmt;--this is actually an insert
3252: l_total_count:=sql%rowcount;
3253: exception when others then
3254: g_status_message:=sqlerrm;
3287: if g_debug then
3288: write_to_log_file('Going to execute '||g_insert_stmt);
3289: write_to_log_file(get_time);
3290: end if;
3291: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
3292: execute immediate g_insert_stmt;
3293: l_count:=sql%rowcount;
3294: if g_debug then
3295: write_to_log_file(get_time);
3318: l_analyze boolean:=false;
3319: l_diff number;
3320: l_target_rec_count number;
3321: Begin
3322: l_table_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(g_fact_name);
3323: if EDW_OWB_COLLECTION_UTIL.drop_table(g_temp_fact_name)=false then
3324: null;
3325: end if;
3326: if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_rowid_table)=false then
3319: l_diff number;
3320: l_target_rec_count number;
3321: Begin
3322: l_table_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(g_fact_name);
3323: if EDW_OWB_COLLECTION_UTIL.drop_table(g_temp_fact_name)=false then
3324: null;
3325: end if;
3326: if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_rowid_table)=false then
3327: null;
3322: l_table_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(g_fact_name);
3323: if EDW_OWB_COLLECTION_UTIL.drop_table(g_temp_fact_name)=false then
3324: null;
3325: end if;
3326: if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_rowid_table)=false then
3327: null;
3328: end if;
3329: if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid_table)=false then
3330: null;
3325: end if;
3326: if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_rowid_table)=false then
3327: null;
3328: end if;
3329: if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid_table)=false then
3330: null;
3331: end if;
3332: if EDW_OWB_COLLECTION_UTIL.drop_table(g_delete_rowid_table)=false then
3333: null;
3328: end if;
3329: if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid_table)=false then
3330: null;
3331: end if;
3332: if EDW_OWB_COLLECTION_UTIL.drop_table(g_delete_rowid_table)=false then
3333: null;
3334: end if;
3335: if EDW_OWB_COLLECTION_UTIL.drop_table(g_summarize_temp2)=false then
3336: null;
3331: end if;
3332: if EDW_OWB_COLLECTION_UTIL.drop_table(g_delete_rowid_table)=false then
3333: null;
3334: end if;
3335: if EDW_OWB_COLLECTION_UTIL.drop_table(g_summarize_temp2)=false then
3336: null;
3337: end if;
3338: if EDW_OWB_COLLECTION_UTIL.drop_table(g_summarize_temp3)=false then
3339: null;
3334: end if;
3335: if EDW_OWB_COLLECTION_UTIL.drop_table(g_summarize_temp2)=false then
3336: null;
3337: end if;
3338: if EDW_OWB_COLLECTION_UTIL.drop_table(g_summarize_temp3)=false then
3339: null;
3340: end if;
3341: Exception when others then
3342: g_status_message:=sqlerrm;
3352: l_stmt:='update '||p_log||' set status=0 where status=1';
3353: if g_debug then
3354: write_to_log_file_n('Going to execute '||l_stmt||get_time);
3355: end if;
3356: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
3357: execute immediate l_stmt;
3358: commit;
3359: if g_debug then
3360: write_to_log_file_n('commit');
3378: l_stmt:='update '||g_ilog||' set status=2 where status=1';
3379: if g_debug then
3380: write_to_log_file_n('Going to execute '||l_stmt||get_time);
3381: end if;
3382: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
3383: execute immediate l_stmt;
3384: commit;
3385: if g_debug then
3386: write_to_log_file_n('Updated '||sql%rowcount||' rows in '||g_ilog||' from status 1 to status 2'||get_time);
3384: commit;
3385: if g_debug then
3386: write_to_log_file_n('Updated '||sql%rowcount||' rows in '||g_ilog||' from status 1 to status 2'||get_time);
3387: end if;
3388: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_ilog,instr(g_ilog,'.')+1,
3389: length(g_ilog)),substr(g_ilog,1,instr(g_ilog,'.')-1));
3390: return true;
3391: Exception when others then
3392: g_status_message:=sqlerrm;
3403: l_stmt:='update '||g_dlog||' set status=2 where status=1';
3404: if g_debug then
3405: write_to_log_file_n('Going to execute '||l_stmt||get_time);
3406: end if;
3407: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
3408: execute immediate l_stmt;
3409: commit;
3410: if g_debug then
3411: write_to_log_file_n('Updated '||sql%rowcount||' rows in '||g_dlog||' from status 1 to status 2'||get_time);
3409: commit;
3410: if g_debug then
3411: write_to_log_file_n('Updated '||sql%rowcount||' rows in '||g_dlog||' from status 1 to status 2'||get_time);
3412: end if;
3413: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_dlog,instr(g_dlog,'.')+1,
3414: length(g_dlog)),substr(g_dlog,1,instr(g_dlog,'.')-1));
3415: return true;
3416: Exception when others then
3417: g_status_message:=sqlerrm;
3421:
3422: function delete_rowid_table_stmt return boolean is
3423: l_divide number:=2;
3424: l_table_owner varchar2(200);
3425: l_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
3426: l_data_type EDW_OWB_COLLECTION_UTIL.varcharTableType;
3427: l_number_cols number;
3428: l_index number;
3429: Begin
3422: function delete_rowid_table_stmt return boolean is
3423: l_divide number:=2;
3424: l_table_owner varchar2(200);
3425: l_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
3426: l_data_type EDW_OWB_COLLECTION_UTIL.varcharTableType;
3427: l_number_cols number;
3428: l_index number;
3429: Begin
3430: if g_debug then
3429: Begin
3430: if g_debug then
3431: write_to_log_file_n('In delete_rowid_table_stmt');
3432: end if;
3433: if EDW_OWB_COLLECTION_UTIL.get_db_columns_for_table(g_fact_name,l_cols,l_data_type,
3434: l_number_cols,g_df_table_owner)=false then
3435: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
3436: return false;
3437: end if;
3431: write_to_log_file_n('In delete_rowid_table_stmt');
3432: end if;
3433: if EDW_OWB_COLLECTION_UTIL.get_db_columns_for_table(g_fact_name,l_cols,l_data_type,
3434: l_number_cols,g_df_table_owner)=false then
3435: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
3436: return false;
3437: end if;
3438: g_delete_rowid_stmt:='create table '||g_delete_rowid_table||' tablespace '||g_op_table_space;
3439: if g_fact_next_extent is not null then
3482: */
3483: if g_number_group_by_cols > 0 then
3484: for i in 1..g_number_group_by_cols loop
3485: l_index:=0;
3486: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_fact_fks,g_number_fact_fks,g_output_group_by_cols(i));
3487: if l_index>0 then --if this is a fk
3488: g_delete_rowid_stmt:=g_delete_rowid_stmt||' '||g_fact_name||'.'||g_output_group_by_cols(i)||'='||
3489: g_temp_fact_name||'.'||g_output_group_by_cols(i)||' and ';
3490: else
3487: if l_index>0 then --if this is a fk
3488: g_delete_rowid_stmt:=g_delete_rowid_stmt||' '||g_fact_name||'.'||g_output_group_by_cols(i)||'='||
3489: g_temp_fact_name||'.'||g_output_group_by_cols(i)||' and ';
3490: else
3491: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(l_cols,l_number_cols,upper(g_output_group_by_cols(i)));
3492: if l_index >0 then
3493: if l_data_type(l_index) like '%CHAR%' or l_data_type(l_index) ='NUMBER' or
3494: l_data_type(l_index) ='LONG' or l_data_type(l_index) ='RAW' or l_data_type(l_index) ='LONG RAW' then
3495: g_delete_rowid_stmt:=g_delete_rowid_stmt||' nvl('||g_fact_name||'.'||g_output_group_by_cols(i)||',0)=nvl('||
3517: End;
3518:
3519: function update_rowid_table_stmt return boolean is
3520: l_divide number:=2;
3521: l_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
3522: l_data_type EDW_OWB_COLLECTION_UTIL.varcharTableType;
3523: l_number_cols number;
3524: l_index number;
3525: Begin
3518:
3519: function update_rowid_table_stmt return boolean is
3520: l_divide number:=2;
3521: l_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
3522: l_data_type EDW_OWB_COLLECTION_UTIL.varcharTableType;
3523: l_number_cols number;
3524: l_index number;
3525: Begin
3526: if g_debug then
3525: Begin
3526: if g_debug then
3527: write_to_log_file_n('In update_rowid_table_stmt');
3528: end if;
3529: if EDW_OWB_COLLECTION_UTIL.get_db_columns_for_table(g_fact_name,l_cols,l_data_type,
3530: l_number_cols,g_df_table_owner)=false then
3531: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
3532: return false;
3533: end if;
3527: write_to_log_file_n('In update_rowid_table_stmt');
3528: end if;
3529: if EDW_OWB_COLLECTION_UTIL.get_db_columns_for_table(g_fact_name,l_cols,l_data_type,
3530: l_number_cols,g_df_table_owner)=false then
3531: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
3532: return false;
3533: end if;
3534: g_update_rowid_stmt:='create table '||g_update_rowid_table||' tablespace '||g_op_table_space;
3535: if g_fact_next_extent is not null then
3578: */
3579: if g_number_group_by_cols > 0 then
3580: for i in 1..g_number_group_by_cols loop
3581: l_index:=0;
3582: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(g_fact_fks,g_number_fact_fks,g_output_group_by_cols(i));
3583: if l_index>0 then --if this is a fk
3584: g_update_rowid_stmt:=g_update_rowid_stmt||' '||g_fact_name||'.'||g_output_group_by_cols(i)||'='||
3585: g_temp_fact_name||'.'||g_output_group_by_cols(i)||' and ';
3586: else
3583: if l_index>0 then --if this is a fk
3584: g_update_rowid_stmt:=g_update_rowid_stmt||' '||g_fact_name||'.'||g_output_group_by_cols(i)||'='||
3585: g_temp_fact_name||'.'||g_output_group_by_cols(i)||' and ';
3586: else
3587: l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(l_cols,l_number_cols,upper(g_output_group_by_cols(i)));
3588: if l_index >0 then
3589: if l_data_type(l_index) like '%CHAR%' or l_data_type(l_index) ='NUMBER' or
3590: l_data_type(l_index) ='LONG' or l_data_type(l_index) ='RAW' or l_data_type(l_index) ='LONG RAW' then
3591: g_update_rowid_stmt:=g_update_rowid_stmt||' nvl('||g_fact_name||'.'||g_output_group_by_cols(i)||',0)=nvl('||
3640: if g_debug then
3641: write_to_log_file_n('In execute_ddata_into_rowid_table');
3642: end if;
3643: begin
3644: if EDW_OWB_COLLECTION_UTIL.drop_table(g_delete_rowid_table) = false then
3645: write_to_log_file_n('Table '||g_delete_rowid_table||' not found for dropping');
3646: end if;
3647: if g_debug then
3648: write_to_log_file_n('Going to execute '||g_delete_rowid_stmt||get_time);
3646: end if;
3647: if g_debug then
3648: write_to_log_file_n('Going to execute '||g_delete_rowid_stmt||get_time);
3649: end if;
3650: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
3651: execute immediate g_delete_rowid_stmt;
3652: if g_debug then
3653: write_to_log_file_n('Moved '||sql%rowcount||' rows into the delete rowid table');
3654: end if;
3681: return false;
3682: end if;
3683: begin
3684: --drop the update rowid table first
3685: if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid_table) = false then
3686: write_to_log_file_n('Table '||g_update_rowid_table||' not found for dropping');
3687: end if;
3688: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_fact_name)=1 then
3689: l_stmt:='create table '||g_update_rowid_table||'(row_id rowid,row_id1 rowid)'||
3684: --drop the update rowid table first
3685: if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid_table) = false then
3686: write_to_log_file_n('Table '||g_update_rowid_table||' not found for dropping');
3687: end if;
3688: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_fact_name)=1 then
3689: l_stmt:='create table '||g_update_rowid_table||'(row_id rowid,row_id1 rowid)'||
3690: ' tablespace '||g_op_table_space;
3691: if g_debug then
3692: write_to_log_file_n('Going to execute '||l_stmt||get_time);
3695: else
3696: if g_debug then
3697: write_to_log_file_n('Going to execute '||g_update_rowid_stmt||get_time);
3698: end if;
3699: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
3700: execute immediate g_update_rowid_stmt;
3701: if g_debug then
3702: write_to_log_file_n('Moved '||sql%rowcount||' rows into the update rowid table');
3703: end if;
3707: write_to_log_file_n(g_status_message);
3708: return false;
3709: end;
3710: begin
3711: if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_rowid_table) = false then
3712: write_to_log_file_n('Table '||g_insert_rowid_table||' not found for dropping');
3713: end if;
3714: if g_debug then
3715: write_to_log_file_n('Going to execute '||g_insert_rowid_stmt);
3713: end if;
3714: if g_debug then
3715: write_to_log_file_n('Going to execute '||g_insert_rowid_stmt);
3716: end if;
3717: EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
3718: execute immediate g_insert_rowid_stmt;
3719: l_count:=sql%rowcount;
3720: if g_debug then
3721: write_to_log_file_n('Moved '||l_count||' rows into the insert rowid table');
3755: if g_debug then
3756: write_to_log_file('Going to execute '||l_stmt||get_time);
3757: end if;
3758: execute immediate l_stmt;
3759: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_delete_rowid_table,instr(g_delete_rowid_table,'.')+1,
3760: length(g_delete_rowid_table)),substr(g_delete_rowid_table,1,instr(g_delete_rowid_table,'.')-1));
3761: return true;
3762: Exception when others then
3763: g_status_message:=sqlerrm;
3780: if g_debug then
3781: write_to_log_file('Going to execute '||l_stmt||get_time);
3782: end if;
3783: execute immediate l_stmt;
3784: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_insert_rowid_table,instr(g_insert_rowid_table,'.')+1,
3785: length(g_insert_rowid_table)),substr(g_insert_rowid_table,1,instr(g_insert_rowid_table,'.')-1));
3786: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_update_rowid_table,instr(g_update_rowid_table,'.')+1,
3787: length(g_update_rowid_table)),substr(g_update_rowid_table,1,instr(g_update_rowid_table,'.')-1));
3788: return true;
3782: end if;
3783: execute immediate l_stmt;
3784: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_insert_rowid_table,instr(g_insert_rowid_table,'.')+1,
3785: length(g_insert_rowid_table)),substr(g_insert_rowid_table,1,instr(g_insert_rowid_table,'.')-1));
3786: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_update_rowid_table,instr(g_update_rowid_table,'.')+1,
3787: length(g_update_rowid_table)),substr(g_update_rowid_table,1,instr(g_update_rowid_table,'.')-1));
3788: return true;
3789: Exception when others then
3790: g_status_message:=sqlerrm;
3894: g_type_dlog_generation:='CTAS';
3895: g_ilog_prev:=null;
3896: g_dlog_prev:=null;
3897: g_temp_fact_name_temp:=g_temp_fact_name||'A';
3898: g_df_table_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(g_fact_name);
3899: if EDW_OWB_COLLECTION_UTIL.get_table_next_extent(g_fact_name,g_df_table_owner,g_fact_next_extent)=false then
3900: g_fact_next_extent:=null;
3901: end if;
3902: if g_fact_next_extent is null or g_fact_next_extent=0 then
3895: g_ilog_prev:=null;
3896: g_dlog_prev:=null;
3897: g_temp_fact_name_temp:=g_temp_fact_name||'A';
3898: g_df_table_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(g_fact_name);
3899: if EDW_OWB_COLLECTION_UTIL.get_table_next_extent(g_fact_name,g_df_table_owner,g_fact_next_extent)=false then
3900: g_fact_next_extent:=null;
3901: end if;
3902: if g_fact_next_extent is null or g_fact_next_extent=0 then
3903: g_fact_next_extent:=16777216;
3927: End;
3928:
3929: procedure write_to_log_file(p_message varchar2) is
3930: begin
3931: EDW_OWB_COLLECTION_UTIL.write_to_log_file(p_message);
3932: Exception when others then
3933: null;
3934: End;
3935:
3964:
3965: function create_summarize_temp2 return boolean is
3966: l_stmt varchar2(10000);
3967: l_summarize_temp2 varchar2(400);
3968: l_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
3969: l_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
3970: l_parent_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType;
3971: l_use_dim_pk EDW_OWB_COLLECTION_UTIL.booleanTableType;--if higher level pk_key is not present, use the pk
3972: Begin
3965: function create_summarize_temp2 return boolean is
3966: l_stmt varchar2(10000);
3967: l_summarize_temp2 varchar2(400);
3968: l_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
3969: l_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
3970: l_parent_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType;
3971: l_use_dim_pk EDW_OWB_COLLECTION_UTIL.booleanTableType;--if higher level pk_key is not present, use the pk
3972: Begin
3973: if g_debug then
3966: l_stmt varchar2(10000);
3967: l_summarize_temp2 varchar2(400);
3968: l_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
3969: l_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
3970: l_parent_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType;
3971: l_use_dim_pk EDW_OWB_COLLECTION_UTIL.booleanTableType;--if higher level pk_key is not present, use the pk
3972: Begin
3973: if g_debug then
3974: write_to_log_file_n('In create_summarize_temp2');
3967: l_summarize_temp2 varchar2(400);
3968: l_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
3969: l_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
3970: l_parent_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType;
3971: l_use_dim_pk EDW_OWB_COLLECTION_UTIL.booleanTableType;--if higher level pk_key is not present, use the pk
3972: Begin
3973: if g_debug then
3974: write_to_log_file_n('In create_summarize_temp2');
3975: end if;
3973: if g_debug then
3974: write_to_log_file_n('In create_summarize_temp2');
3975: end if;
3976: l_summarize_temp2:=g_summarize_temp2||'A';
3977: if EDW_OWB_COLLECTION_UTIL.drop_table(g_summarize_temp2)=false then
3978: null;
3979: end if;
3980: if EDW_OWB_COLLECTION_UTIL.drop_table(l_summarize_temp2)=false then
3981: null;
3976: l_summarize_temp2:=g_summarize_temp2||'A';
3977: if EDW_OWB_COLLECTION_UTIL.drop_table(g_summarize_temp2)=false then
3978: null;
3979: end if;
3980: if EDW_OWB_COLLECTION_UTIL.drop_table(l_summarize_temp2)=false then
3981: null;
3982: end if;
3983: for i in 1..g_number_fact_fks loop
3984: if g_higher_level(i)=true and g_fact_fks_mapped(i) then
3982: end if;
3983: for i in 1..g_number_fact_fks loop
3984: if g_higher_level(i)=true and g_fact_fks_mapped(i) then
3985: l_use_dim_pk(i):=false;--default
3986: if EDW_OWB_COLLECTION_UTIL.is_column_in_table(g_parent_dim(i),g_level_pk_key(i))=false or
3987: g_parent_dim(i) like 'EDW_GL_ACCT%' then
3988: l_use_dim_pk(i):=true;
3989: end if;
3990: end if;
4006: --get the higher level pks
4007: for i in 1..g_number_fact_fks loop
4008: if g_higher_level(i)=true and g_fact_fks_mapped(i) and l_use_dim_pk(i)=false then
4009: l_parent_ltc(i):=g_parent_level(i)||'_LTC';
4010: l_parent_pk(i):=EDW_OWB_COLLECTION_UTIL.get_user_pk(l_parent_ltc(i));
4011: l_parent_pk_key(i):=l_parent_pk(i)||'_KEY';
4012: end if;
4013: end loop;
4014:
4078: end if;
4079: end loop;
4080: end loop;
4081: for i in 1..g_number_group_by_cols loop
4082: if EDW_OWB_COLLECTION_UTIL.value_in_table(g_fact_fks,g_number_fact_fks,g_output_group_by_cols(i))=false then
4083: l_stmt:=l_stmt||'temp.'||g_output_group_by_cols(i)||',';
4084: end if;
4085: end loop;
4086: end if;
4091: end loop;
4092: end if;*/
4093:
4094: l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
4095: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
4096: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
4097: write_to_log_file_n(g_status_message);
4098: return false;
4099: end if;
4092: end if;*/
4093:
4094: l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
4095: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
4096: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
4097: write_to_log_file_n(g_status_message);
4098: return false;
4099: end if;
4100: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_summarize_temp2,instr(l_summarize_temp2,'.')+1,
4096: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
4097: write_to_log_file_n(g_status_message);
4098: return false;
4099: end if;
4100: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_summarize_temp2,instr(l_summarize_temp2,'.')+1,
4101: length(l_summarize_temp2)),substr(l_summarize_temp2,1,instr(l_summarize_temp2,'.')-1));
4102:
4103:
4104: l_stmt:='create table '||g_summarize_temp2||' tablespace '||g_op_table_space;
4146: l_summarize_temp2||'.'||g_fact_fks(i)||' and ';
4147: end if;
4148: end loop;
4149: l_stmt:=substr(l_stmt,1,length(l_stmt)-4);
4150: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
4151: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
4152: write_to_log_file_n(g_status_message);
4153: return false;
4154: end if;
4147: end if;
4148: end loop;
4149: l_stmt:=substr(l_stmt,1,length(l_stmt)-4);
4150: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
4151: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
4152: write_to_log_file_n(g_status_message);
4153: return false;
4154: end if;
4155: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_summarize_temp2,instr(g_summarize_temp2,'.')+1,
4151: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
4152: write_to_log_file_n(g_status_message);
4153: return false;
4154: end if;
4155: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_summarize_temp2,instr(g_summarize_temp2,'.')+1,
4156: length(g_summarize_temp2)),substr(g_summarize_temp2,1,instr(g_summarize_temp2,'.')-1));
4157: if EDW_OWB_COLLECTION_UTIL.drop_table(l_summarize_temp2)=false then
4158: null;
4159: end if;
4153: return false;
4154: end if;
4155: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_summarize_temp2,instr(g_summarize_temp2,'.')+1,
4156: length(g_summarize_temp2)),substr(g_summarize_temp2,1,instr(g_summarize_temp2,'.')-1));
4157: if EDW_OWB_COLLECTION_UTIL.drop_table(l_summarize_temp2)=false then
4158: null;
4159: end if;
4160: return true;
4161: Exception when others then
4169: Begin
4170: if g_debug then
4171: write_to_log_file_n('In create_summarize_temp3');
4172: end if;
4173: if EDW_OWB_COLLECTION_UTIL.drop_table(g_summarize_temp3)=false then
4174: null;
4175: end if;
4176: l_stmt:='create table '||g_summarize_temp3||' tablespace '||g_op_table_space;
4177: if g_parallel is not null then
4197: for i in 1..g_number_fact_fks loop
4198: if g_higher_level(i)=true and g_fact_fks_mapped(i) then
4199: if g_parent_dim(i) like 'EDW_GL_ACCT%' then
4200: l_stmt:=l_stmt||'temp2.'||g_fact_fks(i)||'='||
4201: g_parent_dim(i)||i||'.'||EDW_OWB_COLLECTION_UTIL.get_user_key(g_dim_pk_key(i))||' and ';
4202: else
4203: l_stmt:=l_stmt||'temp2.'||g_fact_fks(i)||'||''-'||g_level_prefix(i)||'''='||
4204: g_parent_dim(i)||i||'.'||EDW_OWB_COLLECTION_UTIL.get_user_key(g_dim_pk_key(i))||' and ';
4205: end if;
4200: l_stmt:=l_stmt||'temp2.'||g_fact_fks(i)||'='||
4201: g_parent_dim(i)||i||'.'||EDW_OWB_COLLECTION_UTIL.get_user_key(g_dim_pk_key(i))||' and ';
4202: else
4203: l_stmt:=l_stmt||'temp2.'||g_fact_fks(i)||'||''-'||g_level_prefix(i)||'''='||
4204: g_parent_dim(i)||i||'.'||EDW_OWB_COLLECTION_UTIL.get_user_key(g_dim_pk_key(i))||' and ';
4205: end if;
4206: end if;
4207: end loop;
4208: l_stmt:=substr(l_stmt,1,length(l_stmt)-4);
4205: end if;
4206: end if;
4207: end loop;
4208: l_stmt:=substr(l_stmt,1,length(l_stmt)-4);
4209: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
4210: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
4211: write_to_log_file_n(g_status_message);
4212: return false;
4213: end if;
4206: end if;
4207: end loop;
4208: l_stmt:=substr(l_stmt,1,length(l_stmt)-4);
4209: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
4210: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
4211: write_to_log_file_n(g_status_message);
4212: return false;
4213: end if;
4214: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_summarize_temp3,instr(g_summarize_temp3,'.')+1,
4210: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
4211: write_to_log_file_n(g_status_message);
4212: return false;
4213: end if;
4214: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_summarize_temp3,instr(g_summarize_temp3,'.')+1,
4215: length(g_summarize_temp3)),substr(g_summarize_temp3,1,instr(g_summarize_temp3,'.')-1));
4216: return true;
4217: Exception when others then
4218: g_status_message:=sqlerrm;
4225: Begin
4226: if g_debug then
4227: write_to_log_file_n('In create_summarize_temp');
4228: end if;
4229: if EDW_OWB_COLLECTION_UTIL.drop_table(g_temp_fact_name)=false then
4230: null;
4231: end if;
4232: l_stmt:='create table '||g_temp_fact_name||' tablespace '||g_op_table_space;
4233: if g_parallel is not null then
4256: end if;
4257: l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
4258: l_stmt:=l_stmt||' from '||g_summarize_temp2||' temp2,'||g_summarize_temp3||' temp3 where '||
4259: 'temp2.rowid=temp3.row_id';
4260: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
4261: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
4262: write_to_log_file_n(g_status_message);
4263: return false;
4264: end if;
4257: l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
4258: l_stmt:=l_stmt||' from '||g_summarize_temp2||' temp2,'||g_summarize_temp3||' temp3 where '||
4259: 'temp2.rowid=temp3.row_id';
4260: if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
4261: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
4262: write_to_log_file_n(g_status_message);
4263: return false;
4264: end if;
4265: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_temp_fact_name,instr(g_temp_fact_name,'.')+1,
4261: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
4262: write_to_log_file_n(g_status_message);
4263: return false;
4264: end if;
4265: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_temp_fact_name,instr(g_temp_fact_name,'.')+1,
4266: length(g_temp_fact_name)),substr(g_temp_fact_name,1,instr(g_temp_fact_name,'.')-1));
4267: return true;
4268: Exception when others then
4269: g_status_message:=sqlerrm;
4272: End;
4273:
4274: function drop_prot_tables return boolean is
4275: Begin
4276: if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_prot_log)=false then
4277: null;
4278: end if;
4279: if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_prot_log)=false then
4280: null;
4275: Begin
4276: if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_prot_log)=false then
4277: null;
4278: end if;
4279: if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_prot_log)=false then
4280: null;
4281: end if;
4282: return true;
4283: Exception when others then
4287: End;
4288:
4289: function drop_d_prot_tables return boolean is
4290: Begin
4291: if EDW_OWB_COLLECTION_UTIL.drop_table(g_delete_prot_log)=false then
4292: null;
4293: end if;
4294: return true;
4295: Exception when others then
4311: if g_debug then
4312: write_to_log_file_n('Going to execute '||l_stmt);
4313: end if;
4314: execute immediate l_stmt;
4315: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_insert_prot_log,instr(g_insert_prot_log,'.')+1,
4316: length(g_insert_prot_log)),substr(g_insert_prot_log,1,instr(g_insert_prot_log,'.')-1));
4317: return true;
4318: Exception when others then
4319: g_status_message:=sqlerrm;
4334: if g_debug then
4335: write_to_log_file_n('Going to execute '||l_stmt);
4336: end if;
4337: execute immediate l_stmt;
4338: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_update_prot_log,instr(g_update_prot_log,'.')+1,
4339: length(g_update_prot_log)),substr(g_update_prot_log,1,instr(g_update_prot_log,'.')-1));
4340: return true;
4341: Exception when others then
4342: g_status_message:=sqlerrm;
4357: if g_debug then
4358: write_to_log_file_n('Going to execute '||l_stmt);
4359: end if;
4360: execute immediate l_stmt;
4361: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_delete_prot_log,instr(g_delete_prot_log,'.')+1,
4362: length(g_delete_prot_log)),substr(g_delete_prot_log,1,instr(g_delete_prot_log,'.')-1));
4363: return true;
4364: Exception when others then
4365: g_status_message:=sqlerrm;
4368: End;
4369:
4370: function recover_from_previous_error return boolean is
4371: Begin
4372: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_ilog) = 2 then
4373: g_err_rec_flag:=true;
4374: end if;
4375: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_dlog) = 2 then
4376: g_err_rec_flag_d:=true;
4371: Begin
4372: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_ilog) = 2 then
4373: g_err_rec_flag:=true;
4374: end if;
4375: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_dlog) = 2 then
4376: g_err_rec_flag_d:=true;
4377: end if;
4378: return true;
4379: Exception when others then
4398: g_ins_rows_ready(1):=g_ins_rows_processed;
4399: g_ins_rows_processed_tab(1):=g_ins_rows_processed;
4400: g_ins_rows_collected(1):=g_ins_rows_processed;
4401: end if;
4402: if EDW_OWB_COLLECTION_UTIL.insert_temp_log_table(
4403: g_fact_name,
4404: 'FACT',
4405: g_conc_id,
4406: g_ins_instance_name,
4415: g_total_insert,
4416: g_total_update,
4417: g_total_delete,
4418: g_number_ins_req_coll) = false then
4419: g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
4420: write_to_log_file_n(g_status_message);
4421: end if;
4422: Exception when others then
4423: g_status_message:=sqlerrm;
4426:
4427: procedure insert_into_load_progress(p_load_fk number,p_object_name varchar2,p_load_progress varchar2,
4428: p_start_date date,p_end_date date,p_category varchar2, p_operation varchar2,p_seq_id varchar2,p_flag varchar2) is
4429: Begin
4430: EDW_OWB_COLLECTION_UTIL.insert_into_load_progress(p_load_fk,p_object_name,g_fact_id,p_load_progress,p_start_date,
4431: p_end_date,p_category,p_operation,p_seq_id,p_flag,g_fact_id);
4432: commit;
4433: Exception when others then
4434: g_status_message:=sqlerrm;
4439: procedure insert_into_load_progress_d(p_load_fk number,p_object_name varchar2,p_load_progress varchar2,
4440: p_start_date date,p_end_date date,p_category varchar2, p_operation varchar2,p_seq_id varchar2,p_flag varchar2) is
4441: Begin
4442: if g_debug then
4443: EDW_OWB_COLLECTION_UTIL.insert_into_load_progress(p_load_fk,p_object_name,g_fact_id,p_load_progress,p_start_date,
4444: p_end_date,p_category,p_operation,p_seq_id,p_flag,g_fact_id);
4445: commit;
4446: end if;
4447: Exception when others then
4499: l_num_blks number;
4500: l_avg_rowlen number;
4501: l_table_owner varchar2(200);
4502: Begin
4503: l_table_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(g_src_object);
4504: l_num:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_src_object,l_table_owner);
4505: if l_num is null or l_num=0 then
4506: if g_parallel is not null then
4507: l_stmt:='select /*+parallel('||g_src_object||','||g_parallel||')*/ count(*) from '||g_src_object;
4500: l_avg_rowlen number;
4501: l_table_owner varchar2(200);
4502: Begin
4503: l_table_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(g_src_object);
4504: l_num:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_src_object,l_table_owner);
4505: if l_num is null or l_num=0 then
4506: if g_parallel is not null then
4507: l_stmt:='select /*+parallel('||g_src_object||','||g_parallel||')*/ count(*) from '||g_src_object;
4508: else
4528:
4529: function create_temp_gilog return boolean is
4530: l_stmt varchar2(5000);
4531: Begin
4532: if EDW_OWB_COLLECTION_UTIL.drop_table(g_ilog)=false then
4533: null;
4534: end if;
4535: g_skip_ilog_update:=true;
4536: if g_src_snplog_has_pk then
4553:
4554: function create_temp_gdlog return boolean is
4555: l_stmt varchar2(5000);
4556: Begin
4557: if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog)=false then
4558: null;
4559: end if;
4560: g_skip_dlog_update:=true;
4561: l_stmt:='create table '||g_dlog||' tablespace '||g_op_table_space||
4576: --g_src_snplog_has_pk
4577: --g_src_uk
4578: --g_src_pk
4579: --g_src_object_ilog
4580: if EDW_OWB_COLLECTION_UTIL.is_column_in_table(g_src_object_ilog,g_src_pk,g_table_owner) then
4581: g_src_snplog_has_pk:=true;
4582: if g_debug then
4583: write_to_log_file_n('g_src_snplog_has_pk set to TRUE');
4584: end if;
4608: l_col:='pk_key';
4609: l_dlog_col:='pk_key';
4610: l_round:='round';
4611: --use is_column_in_table
4612: if EDW_OWB_COLLECTION_UTIL.check_table(g_dlog)=false then
4613: if g_debug then
4614: write_to_log_file_n(g_dlog||' not found'||get_time);
4615: end if;
4616: return true;
4614: write_to_log_file_n(g_dlog||' not found'||get_time);
4615: end if;
4616: return true;
4617: end if;
4618: if EDW_OWB_COLLECTION_UTIL.check_table_column(g_ilog,l_round)=false then
4619: if g_debug then
4620: write_to_log_file_n(l_round||' column not in '||g_ilog||get_time);
4621: end if;
4622: return true;
4620: write_to_log_file_n(l_round||' column not in '||g_ilog||get_time);
4621: end if;
4622: return true;
4623: end if;
4624: if EDW_OWB_COLLECTION_UTIL.check_table_column(g_dlog,l_round)=false then
4625: if g_debug then
4626: write_to_log_file_n(l_round||' column not in '||g_dlog||get_time);
4627: end if;
4628: return true;
4626: write_to_log_file_n(l_round||' column not in '||g_dlog||get_time);
4627: end if;
4628: return true;
4629: end if;
4630: if EDW_OWB_COLLECTION_UTIL.check_table_column(g_src_object_dlog,'PK_KEY')=false then
4631: if g_debug then
4632: write_to_log_file_n('PK_KEY column not in '||g_src_object_dlog||get_time);
4633: end if;
4634: return true;
4642: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
4643: end if;
4644: l_stmt:=l_stmt||' as select B.'||l_col||',B.'||l_round||' from '||g_ilog||' A,'||g_dlog||' B where '||
4645: ' A.row_id=B.row_id1 and A.status=2';
4646: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table_i_and_d)=false then
4647: null;
4648: end if;
4649: if g_debug then
4650: write_to_log_file_n('Going to exec '||l_stmt||get_time);
4659: write_to_log_file_n('There is no need to get any new rows for DLOG and ILOG');
4660: end if;
4661: return true;
4662: end if;
4663: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_table_i_and_d,instr(l_table_i_and_d,'.')+1,
4664: length(l_table_i_and_d)),substr(l_table_i_and_d,1,instr(l_table_i_and_d,'.')-1));
4665: l_stmt:='create table '||l_table_dlog||' tablespace '||g_op_table_space;
4666: if g_parallel is not null then
4667: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
4672: end if;
4673: l_stmt:=l_stmt||'A.rowid row_id,A.row_id row_id1,A.'||l_round||',A.'||l_dlog_col;
4674: l_stmt:=l_stmt||' from '||l_table_i_and_d||' B,'||g_src_object_dlog||' A where A.'||l_dlog_col||'=B.'||l_col||
4675: ' and A.'||l_round||' > B.'||l_round;
4676: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table_dlog)=false then
4677: null;
4678: end if;
4679: if g_debug then
4680: write_to_log_file_n('Going to exec '||l_stmt||get_time);
4683: l_count:=sql%rowcount;
4684: if g_debug then
4685: write_to_log_file_n('Created with '||l_count||' rows '||get_time);
4686: end if;
4687: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_table_dlog,instr(l_table_dlog,'.')+1,
4688: length(l_table_dlog)),substr(l_table_dlog,1,instr(l_table_dlog,'.')-1));
4689: if l_count>0 then
4690: l_stmt:='create table '||l_table_1||' tablespace '||g_op_table_space;
4691: if g_parallel is not null then
4696: l_stmt:=l_stmt||'A.pk_key=B.'||g_src_pk||' and A.round=B.round';
4697: else
4698: l_stmt:=l_stmt||'A.row_id1=B.row_id and A.round=B.round';
4699: end if;
4700: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table_1)=false then
4701: null;
4702: end if;
4703: if g_debug then
4704: write_to_log_file_n('Going to exec '||l_stmt||get_time);
4713: l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
4714: end if;
4715: l_stmt:=l_stmt||' as select rowid row_id from '||l_table_dlog||' MINUS select row_id from '||
4716: l_table_1;
4717: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table_2)=false then
4718: null;
4719: end if;
4720: if g_debug then
4721: write_to_log_file_n('Going to exec '||l_stmt||get_time);
4724: l_count:=sql%rowcount;
4725: if g_debug then
4726: write_to_log_file_n('Created with '||l_count||' rows '||get_time);
4727: end if;
4728: EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_table_2,instr(l_table_2,'.')+1,
4729: length(l_table_2)),substr(l_table_2,1,instr(l_table_2,'.')-1));
4730: if g_src_snplog_has_pk then
4731: l_stmt:='insert into '||g_ilog||'(row_id,status,'||l_round||','||g_src_pk||') select row_id1,0,'||
4732: l_round||','||l_dlog_col||' from '||l_table_2||','||l_table_dlog||' where '||l_table_2||'.row_id='||
4756: commit;
4757: if g_debug then
4758: write_to_log_file_n('Inserted '||l_count||' rows '||get_time);
4759: end if;
4760: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table_1)=false then
4761: null;
4762: end if;
4763: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table_2)=false then
4764: null;
4759: end if;
4760: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table_1)=false then
4761: null;
4762: end if;
4763: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table_2)=false then
4764: null;
4765: end if;
4766: end if;
4767: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table_i_and_d)=false then
4763: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table_2)=false then
4764: null;
4765: end if;
4766: end if;
4767: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table_i_and_d)=false then
4768: null;
4769: end if;
4770: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table_dlog)=false then
4771: null;
4766: end if;
4767: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table_i_and_d)=false then
4768: null;
4769: end if;
4770: if EDW_OWB_COLLECTION_UTIL.drop_table(l_table_dlog)=false then
4771: null;
4772: end if;
4773: return true;
4774: Exception when others then
4782: Begin
4783: if g_debug then
4784: write_to_log_file_n('In set_g_src_join_nl '||p_load_size||' '||p_total_records);
4785: end if;
4786: g_src_join_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(p_load_size,p_total_records,g_src_join_nl_percentage);
4787: return true;
4788: Exception when others then
4789: g_status_message:=sqlerrm;
4790: write_to_log_file_n('Error in set_g_src_join_nl '||g_status_message);
4799: Begin
4800: if g_debug then
4801: write_to_log_file_n('In recover_from_prot');
4802: end if;
4803: if EDW_OWB_COLLECTION_UTIL.merge_all_prot_tables(g_insert_prot_log,'PI',g_op_table_space,g_bis_owner,
4804: g_parallel)=false then
4805: return false;
4806: end if;
4807: if EDW_OWB_COLLECTION_UTIL.merge_all_prot_tables(g_update_prot_log,'PU',g_op_table_space,g_bis_owner,
4803: if EDW_OWB_COLLECTION_UTIL.merge_all_prot_tables(g_insert_prot_log,'PI',g_op_table_space,g_bis_owner,
4804: g_parallel)=false then
4805: return false;
4806: end if;
4807: if EDW_OWB_COLLECTION_UTIL.merge_all_prot_tables(g_update_prot_log,'PU',g_op_table_space,g_bis_owner,
4808: g_parallel)=false then
4809: return false;
4810: end if;
4811: if EDW_OWB_COLLECTION_UTIL.merge_all_prot_tables(g_delete_prot_log,'PD',g_op_table_space,g_bis_owner,
4807: if EDW_OWB_COLLECTION_UTIL.merge_all_prot_tables(g_update_prot_log,'PU',g_op_table_space,g_bis_owner,
4808: g_parallel)=false then
4809: return false;
4810: end if;
4811: if EDW_OWB_COLLECTION_UTIL.merge_all_prot_tables(g_delete_prot_log,'PD',g_op_table_space,g_bis_owner,
4812: g_parallel)=false then
4813: return false;
4814: end if;
4815: l_found:=false;
4812: g_parallel)=false then
4813: return false;
4814: end if;
4815: l_found:=false;
4816: if EDW_OWB_COLLECTION_UTIL.check_table(g_insert_prot_log) then
4817: g_stmt:='update '||g_ilog||' set status=2 where row_id in (select row_id from '||g_insert_prot_log||')';
4818: l_found:=true;
4819: elsif EDW_OWB_COLLECTION_UTIL.check_table(g_update_prot_log) then
4820: g_stmt:='update '||g_ilog||' set status=2 where row_id in (select row_id from '||g_update_prot_log||')';
4815: l_found:=false;
4816: if EDW_OWB_COLLECTION_UTIL.check_table(g_insert_prot_log) then
4817: g_stmt:='update '||g_ilog||' set status=2 where row_id in (select row_id from '||g_insert_prot_log||')';
4818: l_found:=true;
4819: elsif EDW_OWB_COLLECTION_UTIL.check_table(g_update_prot_log) then
4820: g_stmt:='update '||g_ilog||' set status=2 where row_id in (select row_id from '||g_update_prot_log||')';
4821: l_found:=true;
4822: elsif EDW_OWB_COLLECTION_UTIL.check_table(g_delete_prot_log) then
4823: g_stmt:='update '||g_dlog||' set status=2 where row_id in (select row_id from '||g_delete_prot_log||')';
4818: l_found:=true;
4819: elsif EDW_OWB_COLLECTION_UTIL.check_table(g_update_prot_log) then
4820: g_stmt:='update '||g_ilog||' set status=2 where row_id in (select row_id from '||g_update_prot_log||')';
4821: l_found:=true;
4822: elsif EDW_OWB_COLLECTION_UTIL.check_table(g_delete_prot_log) then
4823: g_stmt:='update '||g_dlog||' set status=2 where row_id in (select row_id from '||g_delete_prot_log||')';
4824: l_found:=true;
4825: end if;
4826: if l_found then
4847: End;
4848:
4849: function set_session_parameters return boolean is
4850: Begin
4851: if EDW_OWB_COLLECTION_UTIL.set_session_parameters(g_hash_area_size,g_sort_area_size,
4852: g_trace,g_parallel)=false then
4853: return false;
4854: end if;
4855: return true;
4868: l_debug varchar2(2);
4869: l_fresh_restart varchar2(2);
4870: l_trace varchar2(2);
4871: l_read_cfig_options varchar2(2);
4872: l_higher_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
4873: l_skip_ilog_update varchar2(2);
4874: l_skip_dlog_update varchar2(2);
4875: l_skip_ilog varchar2(2);
4876: l_full_refresh varchar2(2);
5164: if g_debug then
5165: write_to_log_file_n('In make_ok_from_main_ok '||p_main_ok_table_name||' '||p_ilog_table||' '||p_low_end||' '||
5166: p_high_end);
5167: end if;
5168: if EDW_OWB_COLLECTION_UTIL.make_ilog_from_main_ilog(
5169: p_ilog_table,
5170: p_main_ok_table_name,
5171: p_low_end,
5172: p_high_end,
5177: return false;
5178: end if;
5179: if p_mode='ILOG' then
5180: g_skip_ilog_update:=false;
5181: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(p_ilog_table,' status=1 ')=2 then
5182: g_skip_ilog_update:=true;
5183: end if;
5184: else
5185: g_skip_dlog_update:=false;
5182: g_skip_ilog_update:=true;
5183: end if;
5184: else
5185: g_skip_dlog_update:=false;
5186: if EDW_OWB_COLLECTION_UTIL.does_table_have_data(p_ilog_table,' status=1 ')=2 then
5187: g_skip_dlog_update:=true;
5188: end if;
5189: end if;
5190: return true;
5205: g_ilog:=substr(g_ilog,1,length(g_ilog)-1);
5206: else
5207: g_ilog:=g_ilog||'A';
5208: end if;
5209: if EDW_OWB_COLLECTION_UTIL.put_rownum_in_ilog_table(
5210: g_ilog,
5211: l_ilog_table,
5212: g_op_table_space,
5213: g_parallel)=false then
5219: g_dlog:=substr(g_dlog,1,length(g_dlog)-1);
5220: else
5221: g_dlog:=g_dlog||'A';
5222: end if;
5223: if EDW_OWB_COLLECTION_UTIL.put_rownum_in_ilog_table(
5224: g_dlog,
5225: l_ilog_table,
5226: g_op_table_space,
5227: g_parallel)=false then
5235: End;
5236:
5237: function drop_ilog_dlog_tables(p_ilog varchar2,p_dlog varchar2) return boolean is
5238: Begin
5239: if EDW_OWB_COLLECTION_UTIL.drop_ilog_tables(p_ilog,null,g_bis_owner)=false then
5240: return false;
5241: end if;
5242: if EDW_OWB_COLLECTION_UTIL.drop_ilog_tables(p_dlog,null,g_bis_owner)=false then
5243: return false;
5238: Begin
5239: if EDW_OWB_COLLECTION_UTIL.drop_ilog_tables(p_ilog,null,g_bis_owner)=false then
5240: return false;
5241: end if;
5242: if EDW_OWB_COLLECTION_UTIL.drop_ilog_tables(p_dlog,null,g_bis_owner)=false then
5243: return false;
5244: end if;
5245: return true;
5246: Exception when others then
5316: write_to_log_file(l_conc_id||' '||l_dbms_job_id);
5317: end if;
5318: --for single threaded case, g_thread_type is null
5319: if l_dbms_job_id <> -1 then
5320: l_status:=EDW_OWB_COLLECTION_UTIL.check_job_status(l_dbms_job_id);
5321: if l_status is null then
5322: return false;
5323: elsif l_status='N' then
5324: if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_lock_table)=false then
5320: l_status:=EDW_OWB_COLLECTION_UTIL.check_job_status(l_dbms_job_id);
5321: if l_status is null then
5322: return false;
5323: elsif l_status='N' then
5324: if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_lock_table)=false then
5325: null;
5326: end if;
5327: else
5328: DBMS_LOCK.SLEEP(g_sleep_time);
5327: else
5328: DBMS_LOCK.SLEEP(g_sleep_time);
5329: end if;
5330: else
5331: l_status:=EDW_OWB_COLLECTION_UTIL.check_conc_process_status(l_conc_id);
5332: if l_status is null then
5333: return false;
5334: elsif l_status='N' then
5335: if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_lock_table)=false then
5331: l_status:=EDW_OWB_COLLECTION_UTIL.check_conc_process_status(l_conc_id);
5332: if l_status is null then
5333: return false;
5334: elsif l_status='N' then
5335: if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_lock_table)=false then
5336: null;
5337: end if;
5338: else
5339: DBMS_LOCK.SLEEP(g_sleep_time);
5355: Begin
5356: if g_debug then
5357: write_to_log_file_n('In drop_insert_lock_table');
5358: end if;
5359: if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_lock_table)=false then
5360: null;
5361: end if;
5362: return true;
5363: Exception when others then
5373: end if;
5374: insert_into_load_progress(g_load_fk,g_fact_name,'Pre Fact Load Hook',sysdate,null,'DF',
5375: 'PRE-FACT-HOOK','PREDFHOOK'||g_fact_id||'-'||g_src_object_id,'I');
5376: if EDW_COLLECTION_HOOK.pre_derived_fact_coll(p_derv_fact)=false then
5377: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_PREDERVFACT_COLL_ERROR');
5378: write_to_log_file_n(g_status_message||' for '||p_derv_fact);
5379: return false;
5380: end if;
5381: insert_into_load_progress(g_load_fk,null,null,null,sysdate,null,null,'PREDFHOOK'||g_fact_id||'-'||g_src_object_id,
5397: end if;
5398: insert_into_load_progress(g_load_fk,g_fact_name,'Post Fact Load Hook',sysdate,null,'DF',
5399: 'POST-FACT-HOOK','PDFHOOK'||g_fact_id||'-'||g_src_object_id,'I');
5400: if EDW_COLLECTION_HOOK.post_derived_fact_coll(p_derv_fact)=false then
5401: g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_POSTDERVFACT_COLL_ERROR');
5402: write_to_log_file_n(g_status_message||' for '||p_derv_fact||get_time);
5403: return false;
5404: end if;
5405: insert_into_load_progress(g_load_fk,null,null,null,sysdate,null,null,'PDFHOOK'||g_fact_id||'-'||g_src_object_id,
5420: p_temp_exe_name varchar2,
5421: p_bis_short_name varchar2
5422: ) return boolean is
5423: l_exe_file_name varchar2(200);
5424: l_parameter EDW_OWB_COLLECTION_UTIL.varcharTableType;
5425: l_parameter_value_set EDW_OWB_COLLECTION_UTIL.varcharTableType;
5426: l_number_parameters number;
5427: Begin
5428: l_exe_file_name:='EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT';
5421: p_bis_short_name varchar2
5422: ) return boolean is
5423: l_exe_file_name varchar2(200);
5424: l_parameter EDW_OWB_COLLECTION_UTIL.varcharTableType;
5425: l_parameter_value_set EDW_OWB_COLLECTION_UTIL.varcharTableType;
5426: l_number_parameters number;
5427: Begin
5428: l_exe_file_name:='EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT';
5429: l_parameter(1):='p_mode';
5446: l_parameter_value_set(9):='FND_CHAR240';
5447: l_parameter(10):='p_thread_type';
5448: l_parameter_value_set(10):='FND_CHAR240';
5449: l_number_parameters:=10;
5450: if EDW_OWB_COLLECTION_UTIL.create_conc_program(
5451: p_temp_conc_name,
5452: p_temp_conc_short_name,
5453: p_temp_exe_name,
5454: l_exe_file_name,