1 package body EDW_DERIVED_FACT_FACT_COLLECT AS
2 /*$Header: EDWFFCLB.pls 120.1 2006/05/12 02:51:06 vsurendr ship $*/
3
4 /*
5 entry point for conc processes
6 */
7 procedure COLLECT_FACT_MULTI_THREAD(
8 errbuf out nocopy varchar2,
9 retcode out nocopy varchar2,
10 p_fact_name varchar2,
11 p_fact_id number,
12 p_log_file varchar2,
13 p_input_table varchar2,
14 p_ilog varchar2,
15 p_dlog varchar2,
16 p_pre_hook varchar2,
17 p_post_hook varchar2,
18 p_thread_type varchar2
19 ) is
20 Begin
21 retcode:='0';
22 COLLECT_FACT_MULTI_THREAD(
23 p_fact_name
24 ,p_fact_id
25 ,p_log_file
26 ,p_input_table
27 ,p_ilog
28 ,p_dlog
29 ,p_pre_hook
30 ,p_post_hook
31 ,p_thread_type);
32 if g_status=false then
33 retcode:='2';
34 errbuf:=g_status_message;
35 end if;
36 Exception when others then
37 errbuf:=sqlerrm;
38 retcode:='2';
39 write_to_log_file_n('Exception in COLLECT_FACT_MULTI_THREAD '||sqlerrm||get_time);
40 End;
41
42 /*
43 This is en entry point for job. called from EDWFCOLB
44 This can be called via a job or it can also be called serially
45 */
46 procedure COLLECT_FACT_MULTI_THREAD(
47 p_fact_name varchar2,
48 p_fact_id number,
49 p_log_file varchar2,
50 p_input_table varchar2,
51 p_ilog varchar2,
52 p_dlog varchar2,
53 p_pre_hook varchar2,
54 p_post_hook varchar2,
55 p_thread_type varchar2
56 ) is
57 Begin
58 g_fact_name:=p_fact_name;
59 g_fact_id:=p_fact_id;
60 g_dbms_job_id:=-1;
61 g_ilog:=p_ilog;
62 g_dlog:=p_dlog;
63 g_log_file:=p_log_file;
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(
73 g_job_status_table,
74 g_fact_name,
75 g_job_id,
76 'ERROR',
77 g_status_message)=false then
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',
87 g_status_message)=false then
88 null;
89 end if;
90 end if;
91 Exception when others then
92 g_status_message:=sqlerrm;
93 write_to_log_file_n('Exception in COLLECT_FACT_MULTI_THREAD '||sqlerrm||get_time);
94 return;
95 End;
96
97 function COLLECT_FACT_MULTI_THREAD(
98 p_input_table varchar2
99 ) return boolean is
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;
109 -----------------------------------------
110 l_temp_conc_name varchar2(200);
111 l_temp_conc_short_name varchar2(200);
112 l_temp_exe_name varchar2(200);
113 l_bis_short_name varchar2(200);
114 l_try_serial boolean;
115 -----------------------------------------
116 l_errbuf varchar2(2000);
117 l_retcode varchar2(200);
118 -----------------------------------------
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
128 return false;
129 end if; --alter session etc
130 if g_pre_hook='Y' then
131 if pre_fact_load_hook(g_fact_name,g_src_object)=false then
132 return false;
133 end if;
134 end if;
135 g_exec_flag:=true;
136 g_jobid_stmt:=null;--?
137 g_job_id:=null;--?
138 g_ilog_name:=g_ilog;
139 g_dlog_name:=g_dlog;
140 /*
141 g_load_mode is BU-DELETE when inc dim changes are propogated to derived/summary facts
142 In initial_set_up g_ilog and g_dlog will change names
143 */
144 if initial_set_up(
145 p_input_table,
146 g_max_threads,
147 l_ilog_table,
148 l_dlog_table)=false then
149 return false;
150 end if;
151 if g_over then
152 return true;
153 end if;
154 /*
155 if this is full refresh, there will be no multi threading
156 */
157 if g_full_refresh then
158 if COLLECT_FACT('ALL')=false then
159 return false;
160 end if;
161 if g_debug 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,
171 g_skip_dlog_update,
172 g_skip_ilog,
173 g_load_mode,
174 g_full_refresh,
175 g_src_object_ilog,
176 g_src_object_dlog,
177 g_src_snplog_has_pk,
178 g_err_rec_flag,
179 g_err_rec_flag_d
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,
189 l_log_low_end,
190 l_log_high_end,
191 l_log_end_count)=false then
192 return false;
193 end if;
194 l_number_jobs:=0;
195 l_temp_conc_name:='Sub-Proc '||g_src_object_id||'-'||g_fact_id;
196 l_temp_conc_short_name:='C_FCLB_'||g_fact_id||'_'||g_src_object_id||'C';
197 l_temp_exe_name:='E_FFCLB_'||g_fact_id||'_'||g_src_object_id||'_E';
198 l_bis_short_name:='BIS';
199 if g_thread_type='CONC' then
200 --create the executable, conc program etc
201 if create_conc_program(l_temp_conc_name,l_temp_conc_short_name,l_temp_exe_name,l_bis_short_name)=false then
202 if g_debug then
203 write_to_log_file_n('Could not create seed data for conc programs. Trying jobs');
204 end if;
205 g_thread_type:='JOB';
206 end if;
207 end if;
208 for j in 1..l_log_end_count loop
209 l_number_jobs:=l_number_jobs+1;
210 l_job_id(l_number_jobs):=null;
211 l_ilog:=g_ilog_name||'_'||l_number_jobs||'_IL';
212 l_dlog:=g_dlog_name||'_'||l_number_jobs||'_DL';
213 if g_debug then
214 write_to_log_file_n('EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT(''ILOG'','''||g_fact_name||''','||
215 ''''||p_input_table||''','||l_number_jobs||','||l_log_low_end(j)||','||l_log_high_end(j)||','''||
216 l_ilog||''','''||l_dlog||''','''||g_log_file||''','''||g_thread_type||''');');
217 end if;
218 begin
219 l_try_serial:=false;
220 if g_thread_type='CONC' then
221 l_job_id(l_number_jobs):=FND_REQUEST.SUBMIT_REQUEST(
222 application=>l_bis_short_name,
223 program=>l_temp_conc_short_name,
224 argument1=>'ILOG',
225 argument2=>g_fact_name,
226 argument3=>p_input_table,
227 argument4=>l_number_jobs,
228 argument5=>l_log_low_end(j),
229 argument6=>l_log_high_end(j),
230 argument7=>l_ilog,
231 argument8=>l_dlog,
232 argument9=>g_log_file,
233 argument10=>g_thread_type
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;
243 end if;
244 commit;--this commit is very imp
245 else
246 DBMS_JOB.SUBMIT(l_job_id(l_number_jobs),'EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT(''ILOG'','''||
247 g_fact_name||''','||''''||p_input_table||''','||l_number_jobs||','||l_log_low_end(j)||','||
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;
257 end if;
258 commit;--this commit is very imp
259 end if;
260 exception when others then
261 if g_debug then
262 write_to_log_file_n('Error launching parallel slaves '||sqlerrm||'. Attempt serial load');
263 end if;
264 l_try_serial:=true;
265 end;
266 if l_try_serial then
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(
276 l_errbuf,
277 l_retcode,
278 'ILOG',
279 g_fact_name,
280 p_input_table,
281 l_number_jobs,
282 l_log_low_end(j),
283 l_log_high_end(j),
284 l_ilog,
285 l_dlog,
286 g_log_file,
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
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
303 return false;
304 end if;
305 /*
306 Now launch threads to process the dlog rows
307 */
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,
317 l_log_low_end,
318 l_log_high_end,
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;
328 l_job_id(l_number_jobs):=null;
329 l_ilog:=g_ilog_name||'_'||l_number_jobs||'_IL';
330 l_dlog:=g_dlog_name||'_'||l_number_jobs||'_DL';
331 if g_debug then
332 write_to_log_file_n('EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT(''DLOG'','''||g_fact_name||''','||
333 ''''||p_input_table||''','||l_number_jobs||','||l_log_low_end(j)||','||l_log_high_end(j)||','''||
334 l_ilog||''','''||l_dlog||''','''||g_log_file||''','''||g_thread_type||''');');
335 end if;
336 begin
337 l_try_serial:=false;
338 if g_thread_type='CONC' then
339 l_job_id(l_number_jobs):=FND_REQUEST.SUBMIT_REQUEST(
340 application=>l_bis_short_name,
341 program=>l_temp_conc_short_name,
342 argument1=>'DLOG',
343 argument2=>g_fact_name,
344 argument3=>p_input_table,
345 argument4=>l_number_jobs,
346 argument5=>l_log_low_end(j),
347 argument6=>l_log_high_end(j),
348 argument7=>l_ilog,
349 argument8=>l_dlog,
350 argument9=>g_log_file,
351 argument10=>g_thread_type
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;
361 end if;
362 commit;--this commit is very imp
363 else
364 DBMS_JOB.SUBMIT(l_job_id(l_number_jobs),'EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT(''DLOG'','''||
365 g_fact_name||''','||''''||p_input_table||''','||l_number_jobs||','||l_log_low_end(j)||','||
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;
375 end if;
376 commit;--this commit is very imp
377 end if;
378 exception when others then
379 if g_debug then
380 write_to_log_file_n('Error launching parallel slaves '||sqlerrm||'. Attempt serial load');
381 end if;
382 l_try_serial:=true;
383 end;
384 if l_try_serial then
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(
394 l_errbuf,
395 l_retcode,
396 'DLOG',
397 g_fact_name,
398 p_input_table,
399 l_number_jobs,
400 l_log_low_end(j),
404 g_log_file,
401 l_log_high_end(j),
402 l_ilog,
403 l_dlog,
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
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
421 return false;
422 end if;
423 end if;
424 --clean up etc
425 clean_up;
426 if g_post_hook='Y' then
427 if post_fact_load_hook(g_fact_name,g_src_object)=false then
428 return false;
429 end if;
430 end if;
431 return true;
432 Exception when others then
433 g_status_message:=sqlerrm;
434 write_to_log_file_n('Exception in COLLECT_FACT_MULTI_THREAD '||sqlerrm||get_time);
435 return false;
436 End;
437
438 /*
439 entry point for child concurrent requests
440 */
441 procedure COLLECT_FACT(
442 errbuf out nocopy varchar2,
443 retcode out nocopy varchar2,
444 p_mode varchar2,
445 p_fact_name varchar2,
446 p_input_table varchar2,
447 p_job_id number,
448 p_ilog_low_end number,
449 p_ilog_high_end number,
450 p_ilog varchar2,
451 p_dlog varchar2,
452 p_log_file varchar2,
453 p_thread_type varchar2
454 ) is
455 Begin
456 retcode:='0';
457 COLLECT_FACT(
458 p_mode,
459 p_fact_name,
460 p_input_table,
461 p_job_id,
462 p_ilog_low_end,
463 p_ilog_high_end,
464 p_ilog,
465 p_dlog,
466 p_log_file,
467 p_thread_type);
468 if g_status=false then
469 retcode:='2';
470 errbuf:=g_status_message;
471 end if;
472 Exception when others then
473 errbuf:=sqlerrm;
474 retcode:='2';
475 write_to_log_file_n('Exception in COLLECT_FACT '||sqlerrm||get_time);
476 End;
477
478 /*
479 entry point for child threads
480 */
481 procedure COLLECT_FACT(
482 p_mode varchar2,
483 p_fact_name varchar2,
484 p_input_table varchar2,
485 p_job_id number,
486 p_ilog_low_end number,
487 p_ilog_high_end number,
488 p_ilog varchar2,
489 p_dlog varchar2,
490 p_log_file varchar2,
491 p_thread_type varchar2
492 ) is
493 Begin
494 g_job_id:=p_job_id;
495 g_jobid_stmt:=' Job '||g_job_id||' ';
496 g_fact_name:=p_fact_name;
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
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',
520 g_status_message)=false then
521 null;
522 end if;
523 end if;
524 Exception when others then
525 g_status_message:=sqlerrm;
526 write_to_log_file_n('Exception in COLLECT_FACT '||sqlerrm||get_time);
527 End;
528
529 function COLLECT_FACT(
530 p_mode varchar2,
531 p_input_table varchar2,
532 p_ilog_low_end number,
533 p_ilog_high_end number
534 ) return boolean is
535 l_log_name varchar2(80);
536 l_log varchar2(80);
537 Begin
538 if read_options_table(p_input_table)=false then
539 return false;
540 end if;
541 init_all(g_job_id);
542 --have to read again as init_all resets many of the variables
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
552 return false;
553 end if;--alter session etc
554 if p_mode='ILOG' then
555 l_log_name:=g_ilog_name;
556 l_log:=g_ilog;
557 elsif p_mode='DLOG' then
558 l_log_name:=g_dlog_name;
559 l_log:=g_dlog;
560 end if;
561 if make_ok_from_main_ok(l_log_name,l_log,p_ilog_low_end,p_ilog_high_end,p_mode)=false then
562 return false;
563 end if;
564 if read_metadata=false then
565 return false;
569 end if;
566 end if;
567 if COLLECT_FACT(p_mode)=false then
568 return false;
570 clean_up;
571 return true;
572 Exception when others then
573 g_status_message:=sqlerrm;
574 write_to_log_file_n('Exception in COLLECT_FACT '||sqlerrm||get_time);
575 return false;
576 End;
577
578 function initial_set_up(
579 p_input_table varchar2,
580 p_max_threads number,
581 p_ilog_table out nocopy varchar2,
582 p_dlog_table out nocopy varchar2
583 ) return boolean is
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;
593 end if;
594 /*please note that the following are reset to the values shown in init_all in initialize
595 g_err_rec_flag:=false;
596 g_err_rec_flag_d:=false;
597 g_skip_ilog:=false;
598 g_skip_ilog_update:=false;
599 g_skip_dlog_update:=false;
600 */
601 if g_over then
602 return true;
603 end if;
604 if g_full_refresh=false then
605 insert_into_load_progress_d(g_load_fk,g_fact_name,'ILOG DLOG Processing'||g_jobid_stmt,sysdate,null,'DF',
606 'INSERT','ILOGPROC'||g_jobid_stmt,'I');
607 if put_rownum_in_log_table=false then
608 return false;
609 end if;
610 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'ILOGPROC'||g_jobid_stmt,'U');
611 end if;
612 p_ilog_table:=g_ilog;
613 p_dlog_table:=g_dlog;
614 return true;
615 Exception when others then
616 g_status_message:=sqlerrm;
617 write_to_log_file_n('Exception in initial_set_up '||sqlerrm||get_time);
618 return false;
619 End;
620
621 function initialize(p_multi_thread boolean) return boolean is
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;
631 end if;
632 init_all(null);
633 if read_metadata=false then
634 return false;
635 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,
645 g_op_table_space,
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,
655 g_op_table_space,
656 g_bis_owner,
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
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
670 if recover_from_prot=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;
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;
686 end if;
687 end if;
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;
697 end if;
698 if drop_ilog_dlog_tables(g_ilog_name,g_dlog_name)=false then
699 return false;
700 end if;
701 end if;
702 if g_full_refresh=false then
703 insert_into_load_progress_d(g_load_fk,g_fact_name,'Recover from any Previous Error'||g_jobid_stmt,sysdate,null,'DF',
704 'RECOVER','DFRPE'||g_jobid_stmt,'I');
705 if recover_from_previous_error= false then
706 write_to_log_file_n('recover_from_previous_error returned with false');
707 return false;
708 end if;
709 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFRPE'||g_jobid_stmt,'U');
710 end if;
711 if check_src_fact_snplog=-1 then
712 return false;
713 end if;
714 g_skip_ilog:=false;
718 end if;
715 if g_full_refresh then
716 if g_debug then
717 write_to_log_file_n('Full refresh. Making g_collection_size=0');
719 g_collection_size:=0;--this is for performance reasons when loading a derived fact
720 end if;
721 g_src_object_count:=get_base_fact_count;
722 if set_g_src_join_nl(g_collection_size,g_src_object_count)=false then
723 g_src_join_nl:=true;
724 end if;
725 if g_full_refresh then
726 --cannot do this for incremental load because snapshot log can have non distinct values
727 if g_collection_size=0 or (g_src_object_count<=g_collection_size and g_src_object_count<>-1) then
728 g_skip_ilog:=true;
729 end if;
730 elsif g_load_mode='BU-DELETE' then
731 if g_collection_size=0 then
732 g_skip_ilog:=true;
733 end if;
734 elsif g_load_mode='BU-UPDATE' then
735 if g_collection_size=0 then
736 g_skip_ilog:=true;
737 end if;
738 end if;
739 if g_debug then
740 if g_skip_ilog then
741 write_to_log_file_n('Skip ILOG TRUE');
742 end if;
743 end if;
744 --move the snapshot data into some ilog table
745 --and update the status of the delete log
746 if g_err_rec_flag and g_full_refresh = false and g_load_mode <>'BU-DELETE' and g_load_mode <>'BU-UPDATE' then
747 insert_into_load_progress_d(g_load_fk,g_fact_name,'Error Recovery into ILOG,DLOG'||g_jobid_stmt,sysdate,null,'DF',
748 'INSERT','ERRECDI'||g_jobid_stmt,'I');
749 if load_new_update_data= false then
750 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'ERRECDI'||g_jobid_stmt,'U');
751 return false;
752 end if;
753 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'ERRECDI'||g_jobid_stmt,'U');
754 end if;
755 if g_skip_ilog=false then
756 insert_into_load_progress_d(g_load_fk,g_fact_name,'Move Data into ILOG'||g_jobid_stmt,sysdate,null,'DF',
757 'INSERT','DFILOG'||g_jobid_stmt,'I');
758 if move_data_into_local_ilog(p_multi_thread)=false then
759 write_to_log_file_n('move_data_into_local_ilog returned with error');
760 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFILOG'||g_jobid_stmt,'U');
761 return false;
762 end if;
763 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFILOG'||g_jobid_stmt,'U');
764 else
765 if create_temp_gilog=false then
766 return false;
767 end if;
768 g_type_ilog_generation:='UPDATE';
769 g_type_dlog_generation:='UPDATE';
770 end if;
771 if g_load_mode <>'BU-DELETE' and g_load_mode <>'BU-UPDATE' then
772 if g_src_object_dlog is not null and g_full_refresh = false then
773 insert_into_load_progress_d(g_load_fk,g_fact_name,'Move Data into DLOG'||g_jobid_stmt,sysdate,null,'DF',
774 'INSERT','DFDLOG'||g_jobid_stmt,'I');
775 if move_data_into_local_dlog(p_multi_thread)= false then
776 write_to_log_file_n('move_data_into_local_dlog returned with error');
777 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDLOG'||g_jobid_stmt,'U');
778 return false;
779 end if;
780 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDLOG'||g_jobid_stmt,'U');
781 else
782 if create_temp_gdlog=false then
783 return false;
784 end if;
785 g_type_dlog_generation:='UPDATE';
786 end if;
787 else
788 if create_temp_gdlog=false then
789 return false;
790 end if;
791 end if;
792 return true;
793 Exception when others then
794 g_status_message:=sqlerrm;
795 write_to_log_file_n('Exception in initialize '||sqlerrm||get_time);
796 return false;
797 End;
798
799 function read_metadata return boolean is
800 Begin
801 insert_into_load_progress_d(g_load_fk,g_fact_name,'Read Metadata'||g_jobid_stmt,sysdate,null,'DF',
802 'METADAT','DFRM'||g_jobid_stmt,'I');
803 if get_ilog_dlog = false then
804 return false;
805 end if;
806 if g_full_refresh=false and g_src_object_ilog is null then
807 write_to_log_file_n('Source snapshot log not found. Returning...');
808 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFRM'||g_jobid_stmt,'U');
809 g_over:=true;
810 return true; --there is nothing to do.
811 end if;
812 if get_src_fks = false then
813 return false;
814 end if;
815 if get_mapping_details = false then
816 return false;
817 end if;
818 if get_df_extra_fks=false then
819 return false;
820 end if;
821 if make_is_groupby_col= false then
822 write_to_log_file_n('make_is_groupby_col returned with false');
823 return false;
824 end if;
825 if make_is_fk_flag = false then
826 write_to_log_file_n('make_is_fk_flag returned with false');
827 return false;
828 end if;
829 if is_tgt_fk_mapped= false then
830 write_to_log_file_n('is_tgt_fk_mapped returned with false');
831 return false;
832 end if;
833 if make_g_higher_level_flag= false then
834 write_to_log_file_n('make_g_higher_level_flag returned with false');
835 return false;
836 end if;
837 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFRM'||g_jobid_stmt,'U');
838 return true;
839 Exception when others then
843 End;
840 g_status_message:=sqlerrm;
841 write_to_log_file_n('Exception in read_metadata '||sqlerrm||get_time);
842 return false;
844
845 --single thread
846 function COLLECT_FACT(p_fact_name varchar2,
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,
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,
863 p_collection_size number,
864 p_parallel number,
865 p_bis_owner varchar2,
866 p_table_owner varchar2,
867 p_ins_rows_processed out NOCOPY number,
868 p_full_refresh boolean,
869 p_ilog varchar2,
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,
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,
885 p_src_join_nl_percentage number,
886 p_pre_hook varchar2,
887 p_post_hook varchar2
888 ) return boolean is
889 Begin
890 g_fact_name:=p_fact_name;
891 g_fact_id:=p_fact_id;
892 g_mapping_id:=p_mapping_id;
893 g_src_object:=p_src_object;
894 g_src_object_id:=p_src_object_id;
895 g_fact_fks:=p_fact_fks;
896 g_higher_level:=p_higher_level;
897 g_parent_dim:=p_parent_dim;
898 g_parent_level:=p_parent_level;
899 g_level_prefix:=p_level_prefix;
900 g_level_pk:=p_level_pk;
901 g_level_pk_key:=p_level_pk_key;
902 g_dim_pk_key:=p_dim_pk_key;
903 g_number_fact_fks:=p_number_fact_fks;
904 g_conc_id:=p_conc_id;
905 g_conc_program_name:=p_conc_program_name;
906 g_debug:=p_debug;
907 g_exec_flag:=true;
908 g_ins_rows_processed:=0;
909 g_full_refresh:=p_full_refresh;
910 g_collection_size:=p_collection_size;
911 g_parallel:=p_parallel;
912 g_bis_owner:=p_bis_owner;
913 g_table_owner :=p_table_owner;
914 g_ilog:=p_ilog;
915 g_dlog:=p_dlog;
916 g_ilog_name:=p_ilog;
917 g_dlog_name:=p_dlog;
918 g_forall_size:=p_forall_size;
919 g_update_type :=p_update_type;
920 g_fact_dlog:=p_fact_dlog;
921 g_load_fk:=p_load_fk;
922 g_skip_cols:=p_skip_cols;
923 g_number_skip_cols:=p_number_skip_cols;
924 g_fresh_restart:=p_fresh_restart;
925 g_op_table_space:=p_op_table_space;
926 g_bu_tables:=p_bu_tables;
927 g_bu_dimensions:=p_bu_dimensions;
928 g_number_bu_tables:=p_number_bu_tables;
929 g_load_mode:=p_load_mode;
930 g_rollback:=p_rollback;
931 if g_load_mode is null then
932 g_load_mode:='NORMAL';
933 end if;
934 g_bu_src_fact:=p_bu_src_fact;
935 if g_number_bu_tables is null then
936 g_number_bu_tables:=0;
937 end if;
938 g_src_join_nl_percentage:=p_src_join_nl_percentage;
939 g_pre_hook:=p_pre_hook;
940 g_post_hook:=p_post_hook;
941 g_dbms_job_id:=-1;
942 /*
943 g_load_mode is BU-DELETE when inc dim changes are propogated to derived/summary facts
944 */
945 write_to_log_file_n('In EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT'||get_time);
946 if g_debug then
947 write_to_log_file('g_collection_size='||g_collection_size);
948 write_to_log_file('g_parallel='||g_parallel);
949 write_to_log_file('g_bis_owner='||g_bis_owner);
950 write_to_log_file('g_table_owner='||g_table_owner);
951 write_to_log_file('g_ilog='||g_ilog);
952 write_to_log_file('g_dlog='||g_dlog);
953 write_to_log_file('p_fact_dlog='||p_fact_dlog);
954 write_to_log_file('g_load_fk='||g_load_fk);
955 write_to_log_file('g_forall_size='||g_forall_size);
956 write_to_log_file('g_op_table_space='||g_op_table_space);
957 write_to_log_file('g_rollback='||g_rollback);
958 if g_full_refresh then
959 write_to_log_file('Full refresh ON');
960 else
961 write_to_log_file('Full refresh OFF');
962 end if;
963 write_to_log_file_n('g_fact_id '||g_fact_id||', g_mapping_id '||g_mapping_id||', g_src_object_id '||
964 g_src_object_id);
965 write_to_log_file_n('Skipped Columns');
966 for i in 1..g_number_skip_cols loop
967 write_to_log_file(g_skip_cols(i));
968 end loop;
969 write_to_log_file_n('Keys pointing to higher levels');
970 for i in 1..g_number_fact_fks loop
971 if g_higher_level(i) then
972 write_to_log_file(g_fact_fks(i));
973 end if;
974 end loop;
978 write_to_log_file('g_fresh_restart is FALSE');
975 if g_fresh_restart then
976 write_to_log_file('g_fresh_restart is TRUE');
977 else
979 end if;
980 write_to_log_file_n('g_load_mode='||g_load_mode);
981 write_to_log_file_n('g_bu_src_fact='||g_bu_src_fact);
982 write_to_log_file_n('g_number_bu_tables='||g_number_bu_tables);
983 write_to_log_file('BU Tables(Dimension)');
984 for i in 1..g_number_bu_tables loop
985 write_to_log_file(g_bu_tables(i)||'('||g_bu_dimensions(i)||')');
986 end loop;
987 write_to_log_file_n('g_src_join_nl_percentage='||g_src_join_nl_percentage);
988 write_to_log_file_n('g_pre_hook='||g_pre_hook);
989 write_to_log_file_n('g_post_hook='||g_post_hook);
990 end if;
991 if g_pre_hook='Y' then
992 if pre_fact_load_hook(g_fact_name,g_src_object)=false then
993 return false;
994 end if;
995 end if;
996 if initialize(false)=false then
997 return false;
998 end if;
999 if g_over then
1000 return true; --there is nothing to do.
1001 end if;
1002 if COLLECT_FACT('ALL')=false then
1003 return false;
1004 end if;
1005 --drop the df temp table
1006 clean_up;
1007 if g_debug then
1008 write_to_log_file_n('Delete Tables Done');
1009 end if;
1010 if g_post_hook='Y' then
1011 if post_fact_load_hook(g_fact_name,g_src_object)=false then
1012 return false;
1013 end if;
1014 end if;
1015 write_to_log_file_n('EDW_DERIVED_FACT_FACT_COLLECT.collect_fact done for '||
1016 g_src_object||' to '||g_fact_name||'. Time '||get_time);
1017 p_ins_rows_processed :=g_ins_rows_processed;
1018 return true;
1019 Exception when others then
1020 g_status_message:=sqlerrm;
1021 write_to_log_file_n('Exception in COLLECT_FACT '||sqlerrm||get_time);
1022 return false;
1023 End;
1024
1025 function COLLECT_FACT(p_mode varchar2) return boolean is
1026 l_count number:=0;
1027 l_status number;
1028 Begin
1029 if g_debug then
1030 write_to_log_file_n('In Internal COLLECT_FACT p_mode='||p_mode);
1031 end if;
1032 if update_rowid_table_stmt = false then
1033 write_to_log_file_n('update_rowid_table_stmt returned with error');
1034 return false;
1035 end if;
1036 if delete_rowid_table_stmt = false then
1037 write_to_log_file_n('delete_rowid_table_stmt returned with error');
1038 return false;
1039 end if;
1040 if insert_rowid_table_stmt = false then
1041 write_to_log_file_n('insert_rowid_table_stmt returned with error');
1042 return false;
1043 end if;
1044 if make_insert_into_fact = false then --make the stmt
1045 write_to_log_file_n('make_insert_into_fact_iv returned with error');
1046 return false;
1047 end if;
1048 if make_update_into_fact = false then --make the stmt
1049 write_to_log_file_n('make_update_into_fact returned with error');
1050 return false;
1051 end if;
1052 if make_delete_into_fact = false then --make the stmt
1053 write_to_log_file_n('make_delete_into_fact returned with error');
1054 return false;
1055 end if;
1056 --p_mode in single thread mode
1057 if p_mode='ILOG' or p_mode='ALL' then
1058 if g_load_mode<>'BU-DELETE' then
1059 if g_debug then
1060 write_to_log_file_n('In update mode. derv fact getting added');
1061 end if;
1062 loop
1063 --move the data into the temp table
1064 /*
1065 if g_err_rec_flag is true then there is data in the ilog with status 1
1066 */
1067 --reset_profiles;
1068 --g_ins_rows_processed:=0; bug 5197441
1069 g_total_insert:=0;
1070 g_total_update:=0;
1071 g_total_delete:=0;
1072 l_count:=l_count+1;
1073 if g_err_rec_flag=false then
1074 if g_skip_ilog_update=false then
1075 l_status:=set_gilog_status;
1076 else
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;
1086 g_err_rec_flag:=false;
1087 g_skip_ilog_update:=false;
1088 end if;
1089 if l_status=0 then --error
1090 write_to_log_file_n('set_gilog_status returned with error');
1091 return false;
1092 elsif l_status=1 then
1093 if g_debug then
1094 write_to_log_file_n('No More ILOG data in '||g_ilog||' to go into derived fact');
1095 end if;
1096 exit;
1097 else
1098 --data still to go
1099 insert_into_load_progress_d(g_load_fk,g_fact_name,'Move Data into Temp'||g_jobid_stmt,sysdate,null,'DF',
1100 'INSERT','DFTEMP'||l_count||g_jobid_stmt,'I');
1101 l_status:=execute_data_into_temp;
1102 if l_status=0 then
1103 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFTEMP'||l_count||g_jobid_stmt,'U');
1104 return false;
1105 end if;
1106 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFTEMP'||l_count||g_jobid_stmt,'U');
1107 if l_status=2 then
1108 /*
1112 if g_fact_type='SUMMARY' then
1109 for summary facts, summarization to higher levels must take place after data has moved into
1110 temp table
1111 */
1113 insert_into_load_progress_d(g_load_fk,g_fact_name,'Summarize Base Fact Data'||g_jobid_stmt,sysdate,null,'DF',
1114 'CREATE-TABLE','DFSUM'||l_count||g_jobid_stmt,'I');
1115 if summarize_fact_data=false then
1116 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFSUM'||l_count||g_jobid_stmt,'U');
1117 return false;
1118 end if;
1119 end if;
1120 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFSUM'||l_count||g_jobid_stmt,'U');
1121 insert_into_load_progress_d(g_load_fk,g_fact_name,'Create Update/Insert rowid Tables'||g_jobid_stmt,sysdate,null,'DF',
1122 'CREATE-TABLE','DFROWID'||l_count||g_jobid_stmt,'I');
1123 if execute_data_into_rowid_table = false then --creates update and insert rowid tables as select
1124 write_to_log_file_n('execute_data_into_rowid_table returned with error');
1125 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFROWID'||l_count||g_jobid_stmt,'U');
1126 return false;
1127 end if;
1128 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFROWID'||l_count||g_jobid_stmt,'U');
1129 if move_data_into_derived_fact(l_count) = false then
1130 write_to_log_file_n('move_data_into_derived_fact returned with error');
1131 return false;
1132 end if;
1133 insert_into_temp_log('+');
1134 end if;--if l_status=2 then
1135 if g_type_ilog_generation='UPDATE' then
1136 if update_ilog_status_2 = false then
1137 return false;
1138 end if;
1139 end if;
1140 if drop_prot_tables=false then
1141 return false;
1142 end if;
1143 commit;
1144 if g_debug then
1145 write_to_log_file_n('commit');
1146 end if;
1147 end if;
1148 end loop;
1149 end if;--if g_load_mode<>'BU-DELETE' then
1150 end if;
1151 if p_mode='DLOG' or p_mode='ALL' then
1152 --if this is full refresh, no need to delete data
1153 --we take the rowids from the src fact itself and populate the ilog table
1154 --the derived fact is truncated before the full refresh begins
1155 if g_load_mode='BU-DELETE' or (g_full_refresh=false and g_src_object_dlog is not null and
1156 g_load_mode<>'BU-UPDATE') then
1157 if g_debug then
1158 write_to_log_file_n('In delete mode. derv fact getting subtracted');
1159 end if;
1160 l_count:=0;
1161 loop
1162 --reset_profiles;
1163 l_count:=l_count+1;
1164 --move the delete data
1165 --g_ins_rows_processed:=0; bug 5197441
1166 g_total_insert:=0;
1167 g_total_update:=0;
1168 g_total_delete:=0;
1169 if g_err_rec_flag_d=false then
1170 if g_skip_dlog_update=false then
1171 l_status:=set_gdlog_status;
1172 else
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;
1182 g_err_rec_flag_d:=false;
1183 g_skip_dlog_update:=false;
1184 end if;
1185 if l_status=0 then --error
1186 return false;
1187 elsif l_status=1 then
1188 if g_debug then
1189 write_to_log_file_n('No More DLOG data in '||g_ilog||' to go into derived fact');
1190 end if;
1191 exit;
1192 else
1193 insert_into_load_progress_d(g_load_fk,g_fact_name,'Move Update/Delete Data into Temp'||g_jobid_stmt,sysdate,null,'DF',
1194 'INSERT','DFDTEMP'||l_count||g_jobid_stmt,'I');
1195 l_status:=execute_delete_data_into_temp;
1196 if l_status= 0 then
1197 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDTEMP'||l_count||g_jobid_stmt,'U');
1198 return false;
1199 end if;
1200 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDTEMP'||l_count||g_jobid_stmt,'U');
1201 if l_status=2 then
1202 /*
1203 for summary facts, summarization to higher levels must take place after data has moved into
1204 temp table
1205 */
1206 if g_fact_type='SUMMARY' then
1207 insert_into_load_progress_d(g_load_fk,g_fact_name,'Summarize Base Fact Data'||g_jobid_stmt,sysdate,null,'DF',
1208 'CREATE-TABLE','DFDSUM'||l_count||g_jobid_stmt,'I');
1209 if summarize_fact_data=false then
1210 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDSUM'||l_count||g_jobid_stmt,'U');
1211 return false;
1212 end if;
1213 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDSUM'||l_count||g_jobid_stmt,'U');
1214 end if;
1215 insert_into_load_progress_d(g_load_fk,g_fact_name,'Create Delete rowid Tables'||g_jobid_stmt,sysdate,null,'DF',
1219 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDROWID'||l_count||g_jobid_stmt,'U');
1216 'CREATE-TABLE','DFDROWID'||l_count||g_jobid_stmt,'I');
1217 if execute_ddata_into_rowid_table = false then --moves data into delete
1218 write_to_log_file_n('execute_ddata_into_rowid_table returned with error');
1220 return false;
1221 end if;
1222 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDROWID'||l_count||g_jobid_stmt,'U');
1223 if move_ddata_into_derived_fact(l_count) = false then
1224 write_to_log_file_n('move_ddata_into_derived_fact returned with error');
1225 return false;
1226 end if;
1227 insert_into_temp_log('-');
1228 end if;--if l_status=2 then
1229 if g_type_dlog_generation='UPDATE' then
1230 if update_dlog_status_2 = false then
1231 return false;
1232 end if;
1233 end if;
1234 if drop_d_prot_tables=false then
1235 return false;
1236 end if;
1237 commit;
1238 if g_debug then
1239 write_to_log_file_n('commit');
1240 end if;
1241 end if;
1242 end loop;
1243 end if;
1244 end if;
1245 return true;
1246 Exception when others then
1247 g_status_message:=sqlerrm;
1248 write_to_log_file_n('Exception in Internal COLLECT_FACT '||sqlerrm||get_time);
1249 return false;
1250 End;
1251
1252 function move_ddata_into_derived_fact(p_count number) return boolean is
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');
1262 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDDEL'||p_count||g_jobid_stmt,'U');
1263 return false;
1264 end if;
1265 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDDEL'||p_count||g_jobid_stmt,'U');
1266 end if;
1267 return true;
1268 Exception when others then
1269 g_status_message:=sqlerrm;
1270 write_to_log_file_n(g_status_message);
1271 return false;
1272 End;
1273
1274
1275 function move_data_into_derived_fact(p_count number) return boolean is
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');
1285 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDINS'||p_count||g_jobid_stmt,'U');
1286 return false;
1287 end if;
1288 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDINS'||p_count||g_jobid_stmt,'U');
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');
1298 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDUPD'||p_count||g_jobid_stmt,'U');
1299 return false;
1300 end if;
1301 insert_into_load_progress_d(g_load_fk,null,null,null,sysdate,null,null,'DFDUPD'||p_count||g_jobid_stmt,'U');
1302 end if;
1303 return true;
1304 Exception when others then
1305 g_status_message:=sqlerrm;
1306 write_to_log_file_n(g_status_message);
1307 return false;
1308 End;
1309
1310 /*
1311 a temp function here only because m_row$$ is varchar2 while row_id is rowid, see MINUS
1312 */
1313 function create_gilog_T(p_table varchar2,p_ilog_temp varchar2) return boolean is
1314 l_stmt varchar2(4000);
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;
1324 l_stmt:='create table '||p_ilog_temp||'(row_id rowid,'||g_src_pk||' number) tablespace '||g_op_table_space;
1325 else
1326 --l_stmt:='create table '||p_ilog_temp||'(row_id varchar2(255)) '||' tablespace '||g_op_table_space;
1327 l_stmt:='create table '||p_ilog_temp||'(row_id rowid) '||' tablespace '||g_op_table_space;
1328 end if;
1329 if g_parallel is not null then
1330 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
1331 end if;
1332 if g_debug then
1336 execute immediate l_stmt;
1333 write_to_log_file_n('Going to execute '||l_stmt||get_time);
1334 end if;
1335 begin
1337 exception when others then
1338 g_status_message:=sqlerrm;
1339 write_to_log_file_n(g_status_message);
1340 return false;
1341 end;
1342 if g_src_snplog_has_pk then
1343 l_stmt:='insert into '||p_ilog_temp||' (row_id,'||g_src_pk||') select ';
1344 else
1345 l_stmt:='insert into '||p_ilog_temp||' (row_id) select ';
1346 end if;
1347 if g_parallel is not null then
1348 l_stmt:=l_stmt||' /*+PARALLEL ('||p_table||','||g_parallel||')*/ ';
1349 end if;
1350 if g_src_snplog_has_pk then
1351 l_stmt:=l_stmt||' rowid,'||g_src_pk||' from '||p_table;
1352 else
1353 l_stmt:=l_stmt||' rowid from '||p_table;
1354 end if;
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);
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;
1371 write_to_log_file_n(g_status_message);
1372 return false;
1373 end;
1374 return true;
1375 Exception when others then
1376 g_status_message:=sqlerrm;
1377 write_to_log_file_n(g_status_message);
1378 return false;
1379 End;
1380
1381 function drop_ilog_index return boolean is
1382 l_stmt varchar2(4000);
1383 Begin
1384 l_stmt:='drop index '||g_ilog||'u';
1385 if g_debug then
1386 write_to_log_file_n('Going to execute '||l_stmt||get_time);
1387 end if;
1388 execute immediate l_stmt;
1389 l_stmt:='drop index '||g_ilog||'n';
1390 if g_debug then
1391 write_to_log_file_n('Going to execute '||l_stmt||get_time);
1392 end if;
1393 execute immediate l_stmt;
1394 return true;
1395 Exception when others then
1396 g_status_message:=sqlerrm;
1397 write_to_log_file_n(g_status_message);
1398 return false;
1399 End;
1400
1401 function move_data_into_local_ilog(p_multi_thread boolean) return boolean is
1402 l_stmt varchar2(20000);
1403 l_stmt1 varchar2(20000);
1404 l_ilog varchar2(400);
1405 l_ilog_found boolean;
1406 l_ilog_has_data boolean;
1407 l_ilog_temp varchar2(400);
1408 l_ilog_el varchar2(400);
1409 l_round_found boolean;
1410 l_src_object_ilog varchar2(400);
1411 l_use_nl boolean;
1412 l_ilog_count number;
1413 Begin
1414 if g_debug then
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;
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
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;
1437 end if;
1438 end if;
1439 end if;
1440 if l_ilog_found=false then
1441 g_skip_ilog_update:=true;
1442 l_stmt:='create table '||g_ilog||' tablespace '||g_op_table_space||
1443 ' storage(initial 4M next 4M pctincrease 0) ';
1444 if g_parallel is not null then
1445 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
1446 end if;
1447 l_stmt:=l_stmt||' as select ';
1448 if g_full_refresh then
1449 l_ilog_temp:=g_ilog||'T';
1450 if create_gilog_T(g_src_object,l_ilog_temp) = false then
1451 write_to_log_file_n('create_gilog_T returned with error');
1452 return false;
1453 end if;
1454 if g_collection_size>0 and p_multi_thread=false then
1455 if g_src_snplog_has_pk then
1456 l_stmt:=l_stmt||' row_id row_id,'||g_src_pk||',decode(sign(rownum-'||
1457 g_collection_size||'),1,0,1) status,0 round from '||l_ilog_temp;
1458 else
1459 l_stmt:=l_stmt||' row_id row_id,decode(sign(rownum-'||g_collection_size||'),1,0,1) status,0 round from '||
1460 l_ilog_temp;
1461 end if;
1462 else
1463 if g_src_snplog_has_pk then
1464 if p_multi_thread then
1465 l_stmt:=l_stmt||' row_id row_id,'||g_src_pk||', 0 status,0 round from '||l_ilog_temp;
1466 else
1467 l_stmt:=l_stmt||' row_id row_id,'||g_src_pk||', 1 status,0 round from '||l_ilog_temp;
1468 end if;
1469 else
1470 if p_multi_thread then
1474 end if;
1471 l_stmt:=l_stmt||' row_id row_id, 0 status,0 round from '||l_ilog_temp;
1472 else
1473 l_stmt:=l_stmt||' row_id row_id, 1 status,0 round from '||l_ilog_temp;
1475 end if;
1476 end if;
1477 else
1478 l_stmt1:='create table '||l_src_object_ilog||' tablespace '||g_op_table_space;
1479 if g_parallel is not null then
1480 l_stmt1:=l_stmt1||' parallel (degree '||g_parallel||') ';
1481 end if;
1482 l_stmt1:=l_stmt1||' as select ';
1483 if g_parallel is not null then
1484 l_stmt1:=l_stmt1||'/*+PARALLEL ('||g_src_object_ilog||','||g_parallel||')*/ ';
1485 end if;
1486 if g_src_snplog_has_pk then
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);
1496 end if;
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-'||
1506 g_collection_size||'),1,0,1) status,0 round from '||l_src_object_ilog;
1507 else
1508 l_stmt:=l_stmt||' chartorowid(row_id) row_id,decode(sign(rownum-'||g_collection_size||'),1,0,1) status,0 round '||
1509 'from '||l_src_object_ilog;
1510 end if;
1511 else
1512 if g_src_snplog_has_pk then
1513 if p_multi_thread then
1514 l_stmt:=l_stmt||' chartorowid(row_id) row_id,'||g_src_pk||',0 status,0 round from '||l_src_object_ilog;
1515 else
1516 l_stmt:=l_stmt||' chartorowid(row_id) row_id,'||g_src_pk||',1 status,0 round from '||l_src_object_ilog;
1517 end if;
1518 else
1519 if p_multi_thread then
1520 l_stmt:=l_stmt||' chartorowid(row_id) row_id,0 status,0 round from '||l_src_object_ilog;
1521 else
1522 l_stmt:=l_stmt||' chartorowid(row_id) row_id,1 status,0 round from '||l_src_object_ilog;
1523 end if;
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;
1533 if substr(g_ilog,length(g_ilog),1)='A' then
1534 l_ilog_el:=substr(g_ilog,1,length(g_ilog)-1);
1535 else
1536 l_ilog_el:=g_ilog||'A';
1537 end if;
1538 l_stmt:='create table '||l_ilog_el||' tablespace '||g_op_table_space;
1539 if g_parallel is not null then
1540 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
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)';
1550 end if;
1551 l_stmt:=l_stmt||'*/ ';
1552 if g_parallel is not null then
1553 l_stmt:=l_stmt||' /*+PARALLEL(B,'||g_parallel||')*/ ';
1554 end if;
1555 if g_src_snplog_has_pk then
1556 if l_round_found then
1557 l_stmt:=l_stmt||' B.rowid row_id,B.'||g_src_pk||',A.status,A.round from '||g_ilog||' A,'||
1558 g_src_object||' B where A.'||g_src_pk||'=B.'||g_src_pk;
1559 else
1560 l_stmt:=l_stmt||' B.rowid row_id,B.'||g_src_pk||',A.status,0 round from '||g_ilog||' A,'||
1561 g_src_object||' B where A.'||g_src_pk||'=B.'||g_src_pk;
1562 end if;
1563 else
1564 if l_round_found then
1565 l_stmt:=l_stmt||' A.row_id,A.status,A.round from '||g_ilog||' A,'||g_src_object||' B where A.row_id=B.rowid';
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);
1575 end if;
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;
1588 if g_parallel is not null then
1585 g_ilog:=l_ilog_el;
1586 l_ilog_temp:=g_ilog||'T';
1587 l_stmt:='create table '||l_ilog_temp||' tablespace '||g_op_table_space;
1589 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
1590 end if;
1591 l_stmt:=l_stmt||' ';
1592 if g_parallel is not null then
1593 if g_src_snplog_has_pk then
1594 l_stmt:=l_stmt||' as select /*+PARALLEL ('||g_src_object_ilog||','||g_parallel||')*/ '||
1595 ' distinct chartorowid(m_row$$) row_id,'||g_src_pk||',0 status from '||g_src_object_ilog||
1596 ' MINUS select row_id row_id,'||g_src_pk||',0 status from '||g_ilog;
1597 else
1598 l_stmt:=l_stmt||' as select /*+PARALLEL ('||g_src_object_ilog||','||g_parallel||')*/ '||
1599 ' distinct chartorowid(m_row$$) row_id ,0 status from '||g_src_object_ilog||
1600 ' MINUS select row_id row_id ,0 status from '||g_ilog;
1601 end if;
1602 else
1603 if g_src_snplog_has_pk then
1604 l_stmt:=l_stmt||' as select distinct chartorowid(m_row$$) row_id,'||g_src_pk||',0 status from '||
1605 g_src_object_ilog||' MINUS select row_id row_id,'||g_src_pk||',0 status from '||g_ilog;
1606 else
1607 l_stmt:=l_stmt||' as select distinct chartorowid(m_row$$) row_id ,0 status from '||g_src_object_ilog||
1608 ' MINUS select row_id row_id ,0 status from '||g_ilog;
1609 end if;
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
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;
1626 end if;*/
1627 if g_src_snplog_has_pk then
1628 l_stmt:='insert into '||g_ilog||'(row_id,'||g_src_pk||',status,round) select row_id,'||g_src_pk||
1629 ',status,0 from '||l_ilog_temp;
1630 else
1631 l_stmt:='insert into '||g_ilog||'(row_id, status,round) select row_id,status,0 from '||l_ilog_temp;
1632 end if;
1633 end if;
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;
1643 commit;
1644 if g_debug then
1645 write_to_log_file_n('commit');
1646 end if;
1647 exception when others then
1648 g_status_message:=sqlerrm;
1649 write_to_log_file_n(g_status_message);
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)),
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
1666 g_status_message:=sqlerrm;
1667 write_to_log_file_n(g_status_message);
1668 return false;
1669 End;
1670
1671 function drop_dlog_index return boolean is
1672 l_stmt varchar2(4000);
1673 Begin
1674 l_stmt:='drop index '||g_dlog||'u';
1675 if g_debug then
1676 write_to_log_file_n('Going to execute '||l_stmt||get_time);
1677 end if;
1678 execute immediate l_stmt;
1679 l_stmt:='drop index '||g_dlog||'n';
1680 if g_debug then
1681 write_to_log_file_n('Going to execute '||l_stmt||get_time);
1682 end if;
1683 execute immediate l_stmt;
1684 return true;
1685 Exception when others then
1686 g_status_message:=sqlerrm;
1687 write_to_log_file_n(g_status_message);
1688 return false;
1689 End;
1690
1691 function move_data_into_local_dlog(p_multi_thread boolean) return boolean is
1692 l_stmt varchar2(20000);
1693 l_dlog varchar2(400);
1694 l_dlog_temp varchar2(400);
1695 l_dlog_found boolean;
1696 l_pk_key_found boolean;
1697 l_rowid1_found boolean;
1698 l_dlog_el varchar2(400);
1699 l_use_nl boolean;
1700 l_dlog_count number;
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;
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;
1719 else
1723 write_to_log_file_n('Table '||g_dlog||' not found for dropping');
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
1724 end if;
1725 end if;
1726 end if;
1727 end if;
1728 if l_dlog_found=false then
1729 g_skip_dlog_update:=true;
1730 l_stmt:='create table '||g_dlog||' tablespace '||g_op_table_space||
1731 ' storage (initial 4M next 4M pctincrease 0) ';
1732 if g_parallel is not null then
1733 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
1734 end if;
1735 l_stmt:=l_stmt||' as select ';
1736 if g_parallel is not null then
1737 l_stmt:=l_stmt||'/*+PARALLEL ('||g_src_object_dlog||','||g_parallel||')*/ ';
1738 end if;
1739 if g_collection_size>0 and p_multi_thread=false then
1740 if l_pk_key_found then
1741 l_stmt:=l_stmt||' rowid row_id,row_id row_id1,decode(sign(rownum-'||g_collection_size||'),1,0,1) status,'||
1742 'pk_key,0 round from '||g_src_object_dlog||' where round=0';
1743 else
1744 l_stmt:=l_stmt||' rowid row_id,row_id row_id1,decode(sign(rownum-'||g_collection_size||'),1,0,1) status,'||
1745 '0 pk_key,0 round from '||g_src_object_dlog;
1746 end if;
1747 else
1748 if l_pk_key_found then
1749 if p_multi_thread then
1750 l_stmt:=l_stmt||' rowid row_id,row_id row_id1,0 status,pk_key,0 round from '||g_src_object_dlog||
1751 ' where round=0';
1752 else
1753 l_stmt:=l_stmt||' rowid row_id,row_id row_id1,1 status,pk_key,0 round from '||g_src_object_dlog||
1754 ' where round=0';
1755 end if;
1756 else
1757 if p_multi_thread then
1758 l_stmt:=l_stmt||' rowid row_id,row_id row_id1,0 status,0 pk_key,0 round from '||g_src_object_dlog;
1759 else
1760 l_stmt:=l_stmt||' rowid row_id,row_id row_id1,1 status,0 pk_key,0 round from '||g_src_object_dlog;
1761 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';
1771 end if;
1772 l_stmt:='create table '||l_dlog_el||' tablespace '||g_op_table_space||
1773 ' storage (initial 4M next 4M pctincrease 0) ';
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 ';
1783 if l_use_nl then
1784 l_stmt:=l_stmt||'use_nl(B)';
1785 end if;
1786 l_stmt:=l_stmt||'*/ ';
1787 if g_parallel is not null then
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);
1797 end if;
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;
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;
1814 --create dlog temp
1815 l_dlog:=g_dlog||'T';
1816 l_dlog_temp:=g_dlog||'TM';
1817 l_stmt:='create table '||l_dlog||' tablespace '||g_op_table_space;
1818 if g_parallel is not null then
1819 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
1820 end if;
1821 l_stmt:=l_stmt||' ';
1822 l_stmt:=l_stmt||' as select ';
1823 if g_parallel is not null then
1824 l_stmt:=l_stmt||'/*+PARALLEL ('||g_src_object_dlog||','||g_parallel||')*/ ';
1825 end if;
1826 if l_pk_key_found then
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);
1836 end if;
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;
1844 if g_parallel is not null then
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;
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);
1854 end if;
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
1864 l_stmt:=l_stmt||' /*+parallel(B,'||g_parallel||')*/ ';
1865 end if;
1866 l_stmt:=l_stmt||'A.row_id,B.row_id,A.status,';
1867 if l_pk_key_found then
1868 l_stmt:=l_stmt||'B.pk_key,B.round from '||l_dlog_temp||' A,'||g_src_object_dlog||' B where A.row_id=B.rowid';
1869 else
1870 l_stmt:=l_stmt||'0,0 from '||l_dlog_temp||' A,'||g_src_object_dlog||' B where A.row_id=B.rowid';
1871 end if;
1872 else
1873 l_stmt:='insert into '||g_dlog||'(row_id,status) select row_id,status from '||l_dlog_temp;
1874 end if;
1875 end if;
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;
1885 commit;
1886 if g_debug then
1887 write_to_log_file_n('commit');
1888 end if;
1889 exception when others then
1890 g_status_message:=sqlerrm;
1891 write_to_log_file_n(g_status_message);
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;
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;
1908 write_to_log_file_n(g_status_message);
1909 return false;
1910 End;
1911
1912 /*
1913 this function sets the status of the ilog from 0 to 1 and also deletes those that are 1 first
1914 returns:
1915 0: error
1916 1: no more records to change from 0 to 1
1917 2: success
1918 */
1919 function set_gilog_status return number is
1920 l_stmt varchar2(10000);
1921 l_count number;
1922 L_ILOG_PREV varchar2(400);
1923 Begin
1924 --update
1925 if g_debug then
1926 write_to_log_file_n('In set_gilog_status type='||g_type_ilog_generation);
1927 end if;
1928 --no need to explictly say parallel for g_ilog because its created with parallel option
1929 --(if the g_parallel option is not null of course
1930 if g_type_ilog_generation='UPDATE' then
1931 if g_collection_size =0 then
1932 l_stmt:='update '||g_ilog||' set status=1 where status=0';
1933 else
1934 l_stmt:='update '||g_ilog||' set status=1 where status=0 and rownum <='||g_collection_size;
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);
1944 end if;
1945 commit;
1946 if g_debug then
1947 write_to_log_file_n('commit');
1948 end if;
1949 elsif g_type_ilog_generation='CTAS' then
1950 if g_ilog_prev is null then
1951 g_ilog_prev:=g_ilog;
1952 if substr(g_ilog,length(g_ilog),1)='A' then
1953 g_ilog:=substr(g_ilog,1,length(g_ilog)-1);
1954 else
1955 g_ilog:=g_ilog||'A';
1956 end if;
1957 else
1958 l_ilog_prev:=g_ilog_prev;
1959 g_ilog_prev:=g_ilog;
1960 g_ilog:=l_ilog_prev;
1961 end if;
1962 l_stmt:='create table '||g_ilog||' tablespace '||g_op_table_space;
1963 l_stmt:=l_stmt||' storage (initial 4M next 4M pctincrease 0) ';
1964 if g_parallel is not null then
1965 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
1966 end if;
1967 if g_collection_size>0 then
1968 if g_src_snplog_has_pk then
1969 l_stmt:=l_stmt||' as select row_id,'||g_src_pk||',decode(status,1,2,2,2,decode(sign(rownum-'||
1973 l_stmt:=l_stmt||' as select row_id,decode(status,1,2,2,2,decode(sign(rownum-'||
1970 g_collection_size||'),1,0,1)) status,round from (select row_id,'||g_src_pk||',status,round from '||
1971 g_ilog_prev||' order by status) abc ';
1972 else
1974 g_collection_size||'),1,0,1)) status,round from (select row_id,status,round from '||
1975 g_ilog_prev||' order by status) abc ';
1976 end if;
1977 else
1978 if g_src_snplog_has_pk then
1979 l_stmt:=l_stmt||' as select row_id,'||g_src_pk||',decode(status,1,2,0,1,2) status,round from '||
1980 g_ilog_prev;
1981 else
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);
1991 end if;
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;
2001 else
2002 l_count:=1;
2003 end if;
2004 if g_debug then
2005 write_to_log_file_n('Time'||get_time);
2006 end if;
2007 end if;
2008 if l_count=0 then
2009 return 1;
2010 else
2011 return 2;
2012 end if;
2013 Exception when others then
2014 g_status_message:=sqlerrm;
2015 write_to_log_file_n(g_status_message);
2016 return 0;
2017 End;
2018
2019 /*
2020 this function sets the status of the dlog from 0 to 1 and also deletes those that are 1 first
2021 returns:
2022 0: error
2023 1: no more records to change from 0 to 1
2024 2: success
2025 */
2026 function set_gdlog_status return number is
2027 l_stmt varchar2(10000);
2028 l_count number;
2029 l_dlog_prev varchar2(200);
2030 l_pk_key_found boolean;
2031 Begin
2032 --update
2033 if g_debug then
2034 write_to_log_file_n('In set_gdlog_status type='||g_type_dlog_generation);
2035 end if;
2036 --no need to explictly say parallel for g_dlog because its created with parallel option
2037 --(if the g_parallel option is not null of course
2038 if g_type_dlog_generation='UPDATE' then
2039 if g_collection_size =0 then
2040 l_stmt:='update '||g_dlog||' set status=1 where status=0';
2041 else
2042 l_stmt:='update '||g_dlog||' set status=1 where status=0 and rownum <='||g_collection_size;
2043 end if;
2044 if g_debug then
2045 write_to_log_file_n('Going to execute '||l_stmt||get_time);
2046 end if;
2047 execute immediate l_stmt;
2048 l_count:=sql%rowcount;
2049 if g_debug then
2050 write_to_log_file_n('Updated '||l_count||' rows in '||g_dlog||get_time);
2051 end if;
2052 commit;
2053 elsif g_type_dlog_generation='CTAS' then
2054 if g_dlog_prev is null then
2055 g_dlog_prev:=g_dlog;
2056 if substr(g_dlog,length(g_dlog),1)='A' then
2057 g_dlog:=substr(g_dlog,1,length(g_dlog)-1);
2058 else
2059 g_dlog:=g_dlog||'A';
2060 end if;
2061 else
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||') ';
2071 end if;
2072 if g_collection_size > 0 then
2073 if l_pk_key_found then
2074 l_stmt:=l_stmt||' as select row_id,decode(status,1,2,2,2,decode(sign(rownum-'||
2075 g_collection_size||'),1,0,1)) status,row_id1,pk_key,round from (select row_id,status,row_id1,pk_key,'||
2076 'round from '||g_dlog_prev||' order by status) abc ';
2077 else
2078 l_stmt:=l_stmt||' as select row_id,decode(status,1,2,2,2,decode(sign(rownum-'||
2079 g_collection_size||'),1,0,1)) status from (select row_id,status from '||g_dlog_prev||' order by status) abc ';
2080 end if;
2081 else
2082 if l_pk_key_found then
2083 l_stmt:=l_stmt||' as select row_id,decode(status,1,2,0,1,2) status,row_id1,pk_key,round from '||
2084 g_dlog_prev;
2085 else
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);
2095 end if;
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;
2106 l_count:=1;
2103 if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_dlog,' status=1 ')<2 then
2104 l_count:=0;
2105 else
2107 end if;
2108 if g_debug then
2109 write_to_log_file_n('Time'||get_time);
2110 end if;
2111 end if;
2112 if l_count=0 then
2113 return 1;
2114 else
2115 return 2;
2116 end if;
2117 Exception when others then
2118 g_status_message:=sqlerrm;
2119 write_to_log_file_n(g_status_message);
2120 return 0;
2121 End;
2122
2123 function get_ilog_dlog return boolean is
2124 l_ilog_desc varchar2(400);
2125 l_dlog_desc varchar2(400);
2126 begin
2127 if g_debug then
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
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
2142 null;
2143 end if;
2144 if g_debug then
2145 write_to_log_file_n('ILog Table is '||g_src_object_ilog);
2146 write_to_log_file_n('DLog Table is '||g_src_object_dlog);
2147 end if;
2148 return true;
2149 Exception when others then
2150 g_status_message:=sqlerrm;
2151 write_to_log_file_n('Exception in get_ilog_dlog '||sqlerrm||get_time);
2152 return false;
2153 End;
2154
2155 function get_src_fks return boolean is
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, '||
2165 'edw_pvt_columns_md_v fk_item '||
2166 'where '||
2167 'fk.entity_id=:s '||
2168 'and isu.key_id=fk.foreign_key_id '||
2169 'and fk_item.column_id=isu.column_id';
2170 g_number_src_fks:=1;
2171 if g_debug then
2172 write_to_log_file_n('Going to execute '||l_stmt||' using '||g_src_object_id);
2173 end if;
2174 open cv for l_stmt using g_src_object_id;
2175 loop
2176 fetch cv into g_src_fks(g_number_src_fks);
2177 exit when cv%notfound;
2178 g_number_src_fks:=g_number_src_fks+1;
2179 end loop;
2180 g_number_src_fks:=g_number_src_fks-1;
2181 if g_debug then
2182 write_to_log_file_n('The source fact fks, number '||g_number_src_fks);
2183 for i in 1..g_number_src_fks loop
2184 write_to_log_file(g_src_fks(i));
2185 end loop;
2186 end if;
2187 return true;
2188 Exception when others then
2189 g_status_message:=sqlerrm;
2190 write_to_log_file_n('Error in get_src_fks '||sqlerrm||' '||get_time);
2191 return false;
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,
2201 g_fact_fks,
2202 g_number_fact_fks,
2203 g_src_fks,
2204 g_number_src_fks,
2205 g_fact_id,
2206 g_src_object,
2207 g_temp_fact_name_temp,
2208 g_number_sec_sources,
2209 g_sec_sources,
2210 g_sec_sources_alias,
2211 g_number_sec_key,
2212 g_sec_sources_pk,
2213 g_sec_sources_fk,
2214 g_groupby_stmt,
2215 g_hold_number,
2216 g_number_group_by_cols,
2217 g_hold_relation,
2218 g_hold_item,
2219 g_group_by_cols,
2220 g_output_group_by_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
2230 g_status_message:=sqlerrm;
2231 write_to_log_file_n('Error in get_mapping_details '||sqlerrm||' '||get_time);
2232 return false;
2233 End;
2234
2235 function get_df_extra_fks return boolean is
2236 l_found boolean;
2237 Begin
2238 if g_debug then
2239 write_to_log_file_n('In get_df_extra_fks');
2240 end if;
2241 g_number_df_extra_fks:=0;
2242 for i in 1..g_number_fact_fks loop
2243 l_found:=false;
2244 for j in 1..g_number_input_params loop
2245 if g_output_params(j)=g_fact_fks(i) then
2246 l_found:=true;
2247 exit;
2248 end if;
2249 end loop;
2250 if l_found=false then
2251 g_number_df_extra_fks:=g_number_df_extra_fks+1;
2252 g_df_extra_fks(g_number_df_extra_fks):=g_fact_fks(i);
2253 end if;
2254 end loop;
2255 if g_debug then
2256 if g_number_df_extra_fks>0 then
2257 write_to_log_file_n('The extra keys obtained');
2258 for i in 1..g_number_df_extra_fks loop
2259 write_to_log_file(g_df_extra_fks(i));
2260 end loop;
2261 end if;
2262 end if;
2263 return true;
2267 return false;
2264 Exception when others then
2265 g_status_message:=sqlerrm;
2266 write_to_log_file_n(g_status_message);
2268 End;
2269
2270 function make_data_into_temp(p_use_ordered_hint boolean) return boolean is
2271 l_stmt varchar2(30000);
2272 l_index number;
2273 l_use_nl boolean;
2274 l_ilog_count number;
2275 begin
2276 l_stmt:='create table '||g_temp_fact_name||' tablespace '||g_op_table_space;
2277 if g_parallel is not null then
2278 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2279 end if;
2280 l_stmt:=l_stmt||' ';
2281 l_stmt:=l_stmt||' as select ';
2282 l_use_nl:=false;
2283 if g_temp_fact_name_temp=g_src_object 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;
2293 if p_use_ordered_hint then
2294 if l_use_nl then
2295 l_stmt:=l_stmt||' /*+ORDERED USE_NL('||g_src_object||')*/ ';
2296 else
2297 l_stmt:=l_stmt||' /*+ORDERED */ ';
2298 end if;
2299 end if;
2300 end if;
2301 if g_skip_ilog or g_temp_fact_name_temp=g_src_object then
2302 if g_parallel is not null then
2303 l_stmt:=l_stmt||' /*+PARALLEL('||g_src_object||','||g_parallel||')*/ ';
2304 end if;
2305 end if;
2306 for i in 1..g_number_input_params loop
2307 l_stmt:=l_stmt||' '||g_input_params(i)||' '||g_output_params(i)||',';
2308 end loop;
2309 if g_number_df_extra_fks > 0 then
2310 for i in 1..g_number_df_extra_fks loop
2311 l_stmt:=l_stmt||' '||g_naedw_pk||' '||g_df_extra_fks(i)||',';
2312 end loop;
2313 end if;
2314 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
2315 if g_skip_ilog=false then
2316 if g_bu_src_fact is not null then
2317 if g_temp_fact_name_temp=g_src_object then
2318 l_stmt:=l_stmt||' from '||g_ilog_small||','||g_bu_src_fact||' '||g_src_object;
2319 else
2320 l_stmt:=l_stmt||' from '||g_bu_src_fact||','||g_temp_fact_name_temp||' '||g_src_object;
2321 end if;
2322 else
2323 if g_temp_fact_name_temp=g_src_object then
2324 l_stmt:=l_stmt||' from '||g_ilog_small||','||g_src_object;
2325 else
2326 l_stmt:=l_stmt||' from '||g_temp_fact_name_temp||' '||g_src_object;
2327 end if;
2328 end if;
2329 else
2330 if g_bu_src_fact is not null then
2331 l_stmt:=l_stmt||' from '||g_bu_src_fact||' '||g_src_object;
2332 else
2333 l_stmt:=l_stmt||' from '||g_src_object;
2334 end if;
2335 end if;
2336 --add the secondary sources here
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);
2346 end if;
2347 else
2348 l_stmt:=l_stmt||','||g_sec_sources(i)||' '||g_sec_sources_alias(i);
2349 end if;
2350 end loop;
2351 end if;
2352 if g_skip_ilog=false then
2353 if g_temp_fact_name_temp=g_src_object then
2354 l_stmt:=l_stmt||' where '||g_ilog_small||'.row_id='||g_src_object||'.rowid ';
2355 else
2356 l_stmt:=l_stmt||' where 1=1';
2357 end if;
2358 else
2359 l_stmt:=l_stmt||' where 1=1';
2360 end if;
2361 if g_number_sec_sources > 0 then
2362 for i in 1..g_number_sec_key loop
2363 l_stmt:=l_stmt||' and '||g_sec_sources_pk(i)||'='||g_sec_sources_fk(i);
2364 end loop;
2365 end if;
2366 if g_filter_stmt is not null then
2367 l_stmt:=l_stmt||' and '||g_filter_stmt;
2368 end if;
2369 --there is group by only when there are common keys
2370 if g_number_group_by_cols > 0 then
2371 l_stmt:=l_stmt||' group by ';
2372 for i in 1..g_number_group_by_cols loop
2373 l_stmt:=l_stmt||' '||g_group_by_cols(i)||',';
2374 end loop;
2375 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
2376 end if;
2377 g_data_temp_stmt:=l_stmt;
2378 return true;
2379 Exception when others then
2380 g_status_message:=sqlerrm;
2381 write_to_log_file_n('Error in make_data_into_temp '||sqlerrm||' '||get_time);
2382 return false;
2383 End;
2384
2385 function execute_data_into_temp return number is
2386 l_status number:=0;
2387 l_divide number:=2;
2388 l_stmt varchar2(32000);
2389 l_use_nl boolean;
2390 l_ilog_count number;
2391 l_use_ordered_hint boolean;
2392 Begin
2393 if g_debug then
2394 write_to_log_file_n('In execute_data_into_temp');
2395 end if;
2396 l_use_ordered_hint:=true;
2397 <<start_data_into_temp>>
2398 if g_skip_ilog=false then
2399 l_stmt:='create table '||g_ilog_small||' tablespace '||g_op_table_space;
2400 if g_parallel is not null then
2404 l_stmt:=l_stmt||' as select row_id from '||g_ilog||' where status=1';
2401 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2402 end if;
2403 l_stmt:=l_stmt||' ';
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);
2410 end if;
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
2420 if g_parallel is null then
2421 l_divide:=2;
2422 else
2423 l_divide:=g_parallel;
2424 end if;
2425 l_stmt:=l_stmt||' storage(initial '||g_fact_next_extent/2||' next '||
2426 (g_fact_next_extent/l_divide)||' pctincrease 0 MAXEXTENTS 2147483645) ';
2427 end if;
2428 if g_parallel is not null then
2429 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2430 end if;
2431 l_stmt:=l_stmt||' ';
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||')*/ ';
2441 else
2442 l_stmt:=l_stmt||' /*+ORDERED */ ';
2443 end if;
2444 end if;
2445 if g_parallel is not null then
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);
2455 end if;
2456 begin
2457 execute immediate l_stmt;
2458 exception when others then
2459 write_to_log_file_n('Error '||sqlerrm||get_time);
2460 if sqlcode=-01410 then --invalid rowid error
2461 if l_use_ordered_hint then
2462 l_use_ordered_hint:=false;
2463 goto start_data_into_temp;
2464 else
2465 write_to_log_file_n('Unrecoverable invalid rowid error');
2466 raise;
2467 end if;
2468 end if;
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;
2478 end if;
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);
2488 end if;
2489 begin
2490 execute immediate g_data_temp_stmt;
2491 l_status:=sql%rowcount;
2492 exception when others then
2493 write_to_log_file_n('Error '||sqlerrm||get_time);
2494 if sqlcode=-01410 then --invalid rowid error
2495 if l_use_ordered_hint then
2496 l_use_ordered_hint:=false;
2497 goto start_data_into_temp;
2498 else
2499 write_to_log_file_n('Unrecoverable invalid rowid error');
2500 raise;
2501 end if;
2502 end if;
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;
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;
2521 if l_status>0 then
2522 return 2;
2523 else
2524 return 1;
2525 end if;
2526 Exception when others then
2527 g_status_message:=sqlerrm;
2531
2528 write_to_log_file_n('Error in execute_data_into_temp '||sqlerrm||' '||get_time);
2529 return 0;
2530 End;
2532 function make_delete_data_into_temp return boolean is
2533 l_stmt varchar2(30000);
2534 l_index number;
2535 begin
2536 if g_debug then
2537 write_to_log_file_n('In move_delete_data_into_temp');
2538 end if;
2539 l_stmt:='create table '||g_temp_fact_name||' tablespace '||g_op_table_space;
2540 if g_parallel is not null then
2541 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2542 end if;
2543 l_stmt:=l_stmt||' ';
2544 l_stmt:=l_stmt||' as select ';
2545 l_stmt:=l_stmt||' /*+ORDERED */ ';
2546 if g_parallel is not null then
2547 l_stmt:=l_stmt||' /*+ PARALLEL('||g_src_object||','||g_parallel||')*/ ';
2548 end if;
2549 for i in 1..g_number_input_params loop
2550 l_stmt:=l_stmt||' '||g_input_params(i)||' '||g_output_params(i)||',';
2551 end loop;
2552 if g_number_df_extra_fks > 0 then
2553 for i in 1..g_number_df_extra_fks loop
2554 l_stmt:=l_stmt||' '||g_naedw_pk||' '||g_df_extra_fks(i)||',';
2555 end loop;
2556 end if;
2557 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
2558 if g_skip_ilog=false then
2559 if g_bu_src_fact is not null then
2560 l_stmt:=l_stmt||' from '||g_dlog_small||','||g_bu_src_fact||' '||g_src_object;
2561 else
2562 l_stmt:=l_stmt||' from '||g_dlog_small||','||g_src_object_dlog||' '||g_src_object;
2563 end if;
2564 else
2565 if g_bu_src_fact is not null then
2566 l_stmt:=l_stmt||' from '||g_bu_src_fact||' '||g_src_object;
2567 else
2568 l_stmt:=l_stmt||' from '||g_src_object_dlog||' '||g_src_object;
2569 end if;
2570 end if;
2571 --we need to alias g_src_object_dlog to g_src_object because g_input_params is all g_src_object.col
2572 --add the secondary sources here
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);
2582 end if;
2583 else
2584 l_stmt:=l_stmt||','||g_sec_sources(i)||' '||g_sec_sources_alias(i);
2585 end if;
2586 end loop;
2587 end if;
2588 if g_skip_ilog=false then
2589 l_stmt:=l_stmt||' where '||g_dlog_small||'.row_id='||g_src_object||'.rowid ';
2590 else
2591 l_stmt:=l_stmt||' where 1=1';
2592 end if;
2593 if g_number_sec_sources > 0 then
2594 for i in 1..g_number_sec_key loop
2595 l_stmt:=l_stmt||' and '||g_sec_sources_pk(i)||'='||g_sec_sources_fk(i);
2596 end loop;
2597 end if;
2598 if g_filter_stmt is not null then
2599 l_stmt:=l_stmt||' and '||g_filter_stmt;
2600 end if;
2601 if g_number_group_by_cols > 0 then
2602 l_stmt:=l_stmt||' group by ';
2603 for i in 1..g_number_group_by_cols loop
2604 l_stmt:=l_stmt||' '||g_group_by_cols(i)||',';
2605 end loop;
2606 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
2607 end if;
2608 g_delete_data_temp_stmt:=l_stmt;
2609 return true;
2610 Exception when others then
2611 g_status_message:=sqlerrm;
2612 write_to_log_file_n('Error in make_delete_data_into_temp '||sqlerrm||' '||get_time);
2613 return false;
2614 End;
2615
2616 function execute_delete_data_into_temp return number is
2617 l_status number :=0;
2618 l_stmt varchar2(8000);
2619 Begin
2620 if g_debug then
2621 write_to_log_file_n('In execute_delete_data_into_temp');
2622 end if;
2623 if g_skip_ilog=false then
2624 l_stmt:='create table '||g_dlog_small||' tablespace '||g_op_table_space;
2625 if g_parallel is not null then
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);
2635 end if;
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;
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);
2651 end if;
2652 execute immediate g_delete_data_temp_stmt;
2653 l_status:=sql%rowcount;
2654 if g_debug then
2655 write_to_log_file_n('Moved '||l_status||' rows into the temp table from the DLOG'||get_time);
2656 end if;
2657 commit;
2658 if g_debug then
2659 write_to_log_file_n('commit');
2660 end if;
2664 if EDW_OWB_COLLECTION_UTIL.drop_table(g_dlog_small)=false then
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;
2665 null;
2666 end if;
2667 if l_status >0 then
2668 return 2;
2669 else
2670 return 1;
2671 end if;
2672 Exception when others then
2673 g_status_message:=sqlerrm;
2674 write_to_log_file_n('Error in execute_delete_data_into_temp '||sqlerrm||' '||get_time);
2675 return 0;
2676 End;
2677
2678
2679 function is_src_fk(p_fk varchar2) return boolean is
2680 l_fk varchar2(400);
2681 begin
2682 if g_debug then
2683 write_to_log_file_n('in is_src_fk,p_fk='||p_fk);
2684 end if;
2685 --if the fk is abc.xyz then parse the xyz out
2686 if instr(p_fk,'.') <> 0 then
2687 l_fk:=substr(p_fk,instr(p_fk,'.')+1,length(p_fk));
2688 else
2689 l_fk:=p_fk;
2690 end if;
2691 if g_debug then
2692 write_to_log_file('l_fk='||l_fk);
2693 end if;
2694 for i in 1..g_number_src_fks loop
2695 if l_fk=g_src_fks(i) then
2696 return true;
2697 end if;
2698 end loop;
2699 return false;
2700 Exception when others then
2701 g_status_message:=sqlerrm;
2702 write_to_log_file_n('Error in is_src_fk for '||p_fk||' '||sqlerrm||get_time);
2703 return false;
2704 End;
2705
2706 function is_tgt_fk(p_fk varchar2) return boolean is
2707 begin
2708 for i in 1..g_number_fact_fks loop
2709 if p_fk=g_fact_fks(i) then
2710 return true;
2711 end if;
2712 end loop;
2713 return false;
2714 Exception when others then
2715 g_status_message:=sqlerrm;
2716 write_to_log_file_n('Error in is_tgt_fk for '||p_fk||' '||sqlerrm||get_time);
2717 return false;
2718 End;
2719
2720 function is_groupby_col (p_col varchar2) return boolean is
2721 begin
2722 for i in 1..g_number_group_by_cols loop
2723 if p_col=g_output_group_by_cols(i) then
2724 return true;
2725 end if;
2726 end loop;
2727 return false;
2728 Exception when others then
2729 g_status_message:=sqlerrm;
2730 write_to_log_file_n('Error in is_groupby_col for '||p_col||' '||sqlerrm);
2731 return false;
2732 End;
2733
2734 function is_input_groupby_col(p_col varchar2) return boolean is
2735 begin
2736 for i in 1..g_number_group_by_cols loop
2737 if p_col=g_group_by_cols(i) then
2738 return true;
2739 end if;
2740 end loop;
2741 return false;
2742 Exception when others then
2743 g_status_message:=sqlerrm;
2744 write_to_log_file_n('Error in is_input_groupby_col for '||p_col||' '||sqlerrm);
2745 return false;
2746 End;
2747
2748 function make_delete_into_fact return boolean is
2749 l_last_update_date_flag boolean;
2750 l_creation_date_flag boolean;
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;
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;
2766 end if;
2767 if g_update_type='DELETE-INSERT' then
2768 g_delete_stmt:='insert into '||g_fact_name||' ( ';
2769 for i in 1..g_number_input_params loop
2770 g_delete_stmt:=g_delete_stmt||g_output_params(i)||',';
2771 end loop;
2772 if l_creation_date_flag then
2773 g_delete_stmt:=g_delete_stmt||'CREATION_DATE,';
2774 end if;
2775 if l_last_update_date_flag then
2776 g_delete_stmt:=g_delete_stmt||'LAST_UPDATE_DATE,';
2777 end if;
2778 g_delete_stmt:=substr(g_delete_stmt,1,length(g_delete_stmt)-1);
2779 g_delete_stmt:=g_delete_stmt||') select ';
2780 for i in 1..g_number_input_params loop
2781 g_delete_stmt:=g_delete_stmt||g_delete_rowid_table||'.'||g_output_params(i)||',';
2782 end loop;
2783 if l_creation_date_flag then
2784 g_delete_stmt:=g_delete_stmt||'SYSDATE,';
2785 end if;
2786 if l_last_update_date_flag then
2787 g_delete_stmt:=g_delete_stmt||'SYSDATE,';
2788 end if;
2789 g_delete_stmt:=substr(g_delete_stmt,1,length(g_delete_stmt)-1);
2790 g_delete_stmt:=g_delete_stmt||' from '||g_delete_rowid_table;
2791 else
2792 g_delete_stmt_row:='update '||g_fact_name||' set ( ';
2793 if g_update_type='ROW-BY-ROW' then
2794 g_delete_stmt:='update '||g_fact_name||' set ( ';
2795 elsif g_update_type='MASS' then
2796 if g_parallel is null then
2797 g_delete_stmt:='update /*+ ORDERED USE_NL('||g_fact_name||')*/ '||g_fact_name||' set ( ';
2798 else
2799 g_delete_stmt:='update /*+ ORDERED USE_NL('||g_fact_name||')*/ /*+ PARALLEL ('||g_fact_name||','||
2800 g_parallel||')*/ '||g_fact_name||' set ( ';
2801 end if;
2802 end if;
2803 for i in 1..g_number_input_params loop
2804 if g_fk_flag(i)=false and g_groupby_col_flag(i)=false then
2808 end loop;
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;
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,';
2814 else
2815 l_last_update_date_flag:=false;
2816 end if;
2817 g_delete_stmt:=substr(g_delete_stmt,1,length(g_delete_stmt)-1);
2818 g_delete_stmt_row:=substr(g_delete_stmt_row,1,length(g_delete_stmt_row)-1);
2819 g_delete_stmt:=g_delete_stmt||') = (select ';
2820 g_delete_stmt_row:=g_delete_stmt_row||') = (select ';
2821 for i in 1..g_number_input_params loop
2822 if g_fk_flag(i)=false and g_groupby_col_flag(i)=false then
2823 --g_delete_stmt:=g_delete_stmt||' nvl('||g_fact_name||'.'||g_output_params(i)||',0)-nvl('||
2824 -- g_delete_rowid_table||'.'||g_output_params(i)||',0),';
2825 g_delete_stmt:=g_delete_stmt||' nvl('||g_fact_name||'.'||g_output_params(i)||',0)-'||
2826 g_delete_rowid_table||'.'||g_output_params(i)||',';
2827 g_delete_stmt_row:=g_delete_stmt_row||' nvl('||g_fact_name||'.'||g_output_params(i)||',0)-'||
2828 g_delete_rowid_table||'.'||g_output_params(i)||',';
2829 end if;
2830 end loop;
2831 if l_last_update_date_flag then
2832 g_delete_stmt:=g_delete_stmt||'SYSDATE,';
2833 g_delete_stmt_row:=g_delete_stmt_row||'SYSDATE,';
2834 end if;
2835 g_delete_stmt:=substr(g_delete_stmt,1,length(g_delete_stmt)-1);
2836 g_delete_stmt_row:=substr(g_delete_stmt_row,1,length(g_delete_stmt_row)-1);
2837 g_delete_stmt:=g_delete_stmt||' from '||g_delete_rowid_table||' where ';
2838 g_delete_stmt_row:=g_delete_stmt_row||' from '||g_delete_rowid_table||' where ';
2839 g_delete_stmt_row:=g_delete_stmt_row||g_delete_rowid_table||'.row_id1=:a) where '||g_fact_name||'.rowid=:b';
2840 if g_update_type='ROW-BY-ROW' then
2841 g_delete_stmt:=g_delete_stmt||g_delete_rowid_table||'.row_id1=:a) where '||g_fact_name||'.rowid=:b';
2842 elsif g_update_type='MASS' then
2843 g_delete_stmt:=g_delete_stmt||g_delete_rowid_table||'.row_id1='||g_fact_name||'.rowid ) where '||
2844 g_fact_name||'.rowid in (select row_id1 from '||g_delete_rowid_table||')';
2845 end if;
2846 end if;
2847 return true;
2848 Exception when others then
2849 g_status_message:=sqlerrm;
2850 write_to_log_file_n('Error in make_delete_into_fact '||sqlerrm||' '||get_time);
2851 return false;
2852 End;
2853
2854 function make_update_into_fact return boolean is
2855 l_last_update_date_flag boolean;
2856 l_creation_date_flag boolean;
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;
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;
2872 end if;
2873 if g_update_type='DELETE-INSERT' then
2874 g_update_stmt:='insert into '||g_fact_name||' ( ';
2875 for i in 1..g_number_input_params loop
2876 g_update_stmt:=g_update_stmt||g_output_params(i)||',';
2877 end loop;
2878 if l_creation_date_flag then
2879 g_update_stmt:=g_update_stmt||'CREATION_DATE,';
2880 end if;
2881 if l_last_update_date_flag then
2882 g_update_stmt:=g_update_stmt||'LAST_UPDATE_DATE,';
2883 end if;
2884 g_update_stmt:=substr(g_update_stmt,1,length(g_update_stmt)-1);
2885 g_update_stmt:=g_update_stmt||') select ';
2886 for i in 1..g_number_input_params loop
2887 g_update_stmt:=g_update_stmt||g_update_rowid_table||'.'||g_output_params(i)||',';
2888 end loop;
2889 if l_creation_date_flag then
2890 g_update_stmt:=g_update_stmt||'SYSDATE,';
2891 end if;
2892 if l_last_update_date_flag then
2893 g_update_stmt:=g_update_stmt||'SYSDATE,';
2894 end if;
2895 g_update_stmt:=substr(g_update_stmt,1,length(g_update_stmt)-1);
2896 g_update_stmt:=g_update_stmt||' from '||g_update_rowid_table;
2897 else
2898 g_update_stmt_row:='update '||g_fact_name||' set ( ';
2899 if g_update_type='ROW-BY-ROW' then
2900 g_update_stmt:='update '||g_fact_name||' set ( ';
2901 elsif g_update_type='MASS' then
2902 if g_parallel is null then
2903 g_update_stmt:='update /*+ ORDERED USE_NL('||g_fact_name||')*/ '||g_fact_name||' set ( ';
2904 else
2905 g_update_stmt:='update /*+ ORDERED USE_NL('||g_fact_name||')*/ /*+ PARALLEL ('||g_fact_name||','||
2906 g_parallel||')*/ '||g_fact_name||' set ( ';
2907 end if;
2908 end if;
2909 for i in 1..g_number_input_params loop
2910 if g_fk_flag(i)=false and g_groupby_col_flag(i)=false then
2911 g_update_stmt:=g_update_stmt||g_output_params(i)||',';
2912 g_update_stmt_row:=g_update_stmt_row||g_output_params(i)||',';
2913 end if;
2914 end loop;
2915 if l_last_update_date_flag then
2919 g_update_stmt:=substr(g_update_stmt,1,length(g_update_stmt)-1);
2916 g_update_stmt:=g_update_stmt||'LAST_UPDATE_DATE,';
2917 g_update_stmt_row:=g_update_stmt_row||'LAST_UPDATE_DATE,';
2918 end if;
2920 g_update_stmt_row:=substr(g_update_stmt_row,1,length(g_update_stmt_row)-1);
2921 g_update_stmt:=g_update_stmt||') = (select ';
2922 g_update_stmt_row:=g_update_stmt_row||') = (select ';
2923 for i in 1..g_number_input_params loop
2924 if g_fk_flag(i)=false and g_groupby_col_flag(i)=false then
2925 g_update_stmt:=g_update_stmt||' nvl('||g_fact_name||'.'||g_output_params(i)||',0)+'||
2926 g_update_rowid_table||'.'||g_output_params(i)||',';
2927 g_update_stmt_row:=g_update_stmt_row||' nvl('||g_fact_name||'.'||g_output_params(i)||',0)+'||
2928 g_update_rowid_table||'.'||g_output_params(i)||',';
2929 end if;
2930 end loop;
2931 if l_last_update_date_flag then
2932 g_update_stmt:=g_update_stmt||'SYSDATE,';
2933 g_update_stmt_row:=g_update_stmt_row||'SYSDATE,';
2934 end if;
2935 g_update_stmt:=substr(g_update_stmt,1,length(g_update_stmt)-1);
2936 g_update_stmt_row:=substr(g_update_stmt_row,1,length(g_update_stmt_row)-1);
2937 g_update_stmt:=g_update_stmt||' from '||g_update_rowid_table||' where ';
2938 g_update_stmt_row:=g_update_stmt_row||' from '||g_update_rowid_table||' where ';
2939 g_update_stmt_row:=g_update_stmt_row||g_update_rowid_table||'.row_id1=:a) where '||g_fact_name||'.rowid=:b';
2940 if g_update_type='ROW-BY-ROW' then
2941 g_update_stmt:=g_update_stmt||g_update_rowid_table||'.row_id1=:a) where '||g_fact_name||'.rowid=:b';
2942 elsif g_update_type='MASS' then
2943 g_update_stmt:=g_update_stmt||g_update_rowid_table||'.row_id1='||g_fact_name||'.rowid ) where '||
2944 g_fact_name||'.rowid in (select row_id1 from '||g_update_rowid_table||')';
2945 end if;
2946 end if;
2947 /*
2948 if g_debug then
2949 write_to_log_file_n('g_update_stmt is '||g_update_stmt);
2950 end if;*/
2951 return true;
2952 Exception when others then
2953 g_status_message:=sqlerrm;
2954 write_to_log_file_n('Error in make_update_into_fact '||sqlerrm||' '||get_time);
2955 return false;
2956 End;
2957
2958
2959 function make_insert_into_fact return boolean is
2960 l_creation_date_flag boolean;
2961 l_last_update_date_flag boolean;
2962 Begin
2963 if g_debug then
2964 write_to_log_file_n('In make_insert_into_fact');
2965 end if;
2966 if g_parallel is null then
2967 g_insert_stmt:='insert into '||g_fact_name||'(';
2968 else
2969 g_insert_stmt:='insert /*+ PARALLEL ('||g_fact_name||','||g_parallel||')*/ into '||g_fact_name||'(';
2970 end if;
2971 for i in 1..g_number_input_params loop
2972 g_insert_stmt:=g_insert_stmt||' '||g_output_params(i)||',';
2973 end loop;
2974 if g_number_df_extra_fks > 0 then
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
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
2991 l_last_update_date_flag:=false;
2992 end if;
2993 g_insert_stmt:=substr(g_insert_stmt,1,length(g_insert_stmt)-1);
2994 g_insert_stmt:=g_insert_stmt||' ) select /*+ORDERED */ ';
2995 for i in 1..g_number_input_params loop
2996 g_insert_stmt:=g_insert_stmt||' '||g_output_params(i)||',';
2997 end loop;
2998 if g_number_df_extra_fks > 0 then
2999 for i in 1..g_number_df_extra_fks loop
3000 g_insert_stmt:=g_insert_stmt||' '||g_df_extra_fks(i)||',';
3001 end loop;
3002 end if;
3003 if l_creation_date_flag then
3004 g_insert_stmt:=g_insert_stmt||'SYSDATE,';
3005 end if;
3006 if l_last_update_date_flag then
3007 g_insert_stmt:=g_insert_stmt||'SYSDATE,';
3008 end if;
3009 g_insert_stmt:=substr(g_insert_stmt,1,length(g_insert_stmt)-1);
3010 g_insert_stmt:=g_insert_stmt||' from '||g_insert_rowid_table||','||g_temp_fact_name||
3011 ' where '||g_temp_fact_name||'.rowid='||g_insert_rowid_table||'.row_id';
3012 /*
3013 if g_debug then
3014 write_to_log_file('The statement to insert into the IV');
3015 write_to_log_file_n(g_insert_stmt);
3016 end if;*/
3017 return true;
3018 Exception when others then
3019 g_status_message:=sqlerrm;
3020 write_to_log_file_n('Error in make_insert_into_fact '||sqlerrm||' '||get_time);
3021 return false;
3022 End;
3023
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
3033 if g_debug then
3034 write_to_log_file_n('In delete_into_fact');
3035 end if;
3036 l_update_type:=g_update_type;
3037 <<start_delete>>
3038 if l_update_type='ROW-BY-ROW' then
3039 l_stmt:='select row_id1 from '||g_delete_rowid_table;
3043 if g_debug then
3040 if g_debug then
3041 write_to_log_file_n('Goint to execute '||l_stmt);
3042 end if;
3044 write_to_log_file('Going to execute '||g_delete_stmt_row||get_time);
3045 end if;
3046 l_count:=1;
3047 open cv for l_stmt;
3048 loop
3049 fetch cv into l_rowid(l_count);
3050 exit when cv%notfound;
3051 if l_count>=g_forall_size then
3052 for i in 1..l_count loop
3053 execute immediate g_delete_stmt_row using l_rowid(i),l_rowid(i);
3054 end loop;
3055 l_total_count:=l_total_count+l_count;
3056 l_count:=1;
3057 commit;
3058 else
3059 l_count:=l_count+1;
3060 end if;
3061 end loop;
3062 close cv;
3063 l_count:=l_count-1;
3064 if l_count>0 then
3065 for i in 1..l_count loop
3066 execute immediate g_delete_stmt_row using l_rowid(i),l_rowid(i);
3067 end loop;
3068 l_total_count:=l_total_count+l_count;
3069 end if;
3070 elsif l_update_type='MASS' then
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
3080 commit;--release any rollback
3081 --if there is a out NOCOPY of memory issue, retry an update using row by row
3082 if g_debug then
3083 write_to_log_file_n('Memory issue with Mass Update. Retrying using ROW_BY_ROW');
3084 end if;
3085 l_update_type:='ROW-BY-ROW';
3086 goto start_delete;
3087 elsif sqlcode=-00060 then
3088 if g_debug then
3089 write_to_log_file_n('Deadlock detected. Try again after sleep');
3090 end if;
3091 DBMS_LOCK.SLEEP(g_sleep_time);
3092 goto start_delete;
3093 else
3094 g_status_message:=sqlerrm;
3095 write_to_log_file_n(g_status_message);
3096 write_to_log_file('Problem stmt '||g_delete_stmt);
3097 return false;
3098 end if;
3099 end ;
3100 elsif l_update_type='DELETE-INSERT' then
3101 l_stmt:='delete '||g_fact_name||' where exists (select 1 from '||g_delete_rowid_table||' where '||
3102 g_delete_rowid_table||'.row_id1='||g_fact_name||'.rowid)';
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;
3112 exception when others then
3113 g_status_message:=sqlerrm;
3114 write_to_log_file_n(g_status_message);
3115 write_to_log_file('Problem stmt '||l_stmt);
3116 return false;
3117 end ;
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;
3127 write_to_log_file_n(g_status_message);
3128 write_to_log_file('Problem stmt '||g_delete_stmt);
3129 return false;
3130 end ;
3131 end if;
3132 if g_debug then
3133 write_to_log_file(get_time);
3134 end if;
3135 if make_delete_prot_log=false then --this is the commit
3136 write_to_log_file_n('make_delete_prot_log returned with error');
3137 rollback;
3138 return false;
3139 end if;
3140 g_ins_rows_processed:=nvl(g_ins_rows_processed,0)+l_total_count;
3141 g_total_delete:=nvl(g_total_delete,0)+l_total_count;
3142 if g_debug then
3143 write_to_log_file_n('Number of rows updated for delete in the fact '||l_total_count);
3144 write_to_log_file('Number of rows Processed So Far into the fact '||g_ins_rows_processed);
3145 end if;
3146 return true;
3147 Exception when others then
3148 g_status_message:=sqlerrm;
3149 write_to_log_file_n('Error in delete_into_fact '||sqlerrm||' '||get_time);
3150 return false;
3151 End;
3152
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
3162 if g_debug then
3163 write_to_log_file_n('In update_into_fact');
3164 end if;
3165 l_update_type:=g_update_type;
3166 <<start_update>>
3167 if l_update_type='ROW-BY-ROW' then
3168 l_stmt:='select row_id1 from '||g_update_rowid_table;
3169 if g_debug then
3170 write_to_log_file_n('Goint to execute '||l_stmt);
3171 end if;
3172 if g_debug then
3173 write_to_log_file_n('Goint to execute '||g_update_stmt_row||get_time);
3174 end if;
3175 l_count:=1;
3176 open cv for l_stmt;
3177 loop
3181 for i in 1..l_count loop
3178 fetch cv into l_rowid(l_count);
3179 exit when cv%notfound;
3180 if l_count>=g_forall_size then
3182 execute immediate g_update_stmt_row using l_rowid(i),l_rowid(i);
3183 end loop;
3184 l_total_count:=l_total_count+l_count;
3185 l_count:=1;
3186 commit;
3187 else
3188 l_count:=l_count+1;
3189 end if;
3190 end loop;
3191 close cv;
3192 l_count:=l_count-1;
3193 if l_count>0 then
3194 for i in 1..l_count loop
3195 execute immediate g_update_stmt_row using l_rowid(i),l_rowid(i);
3196 end loop;
3197 l_total_count:=l_total_count+l_count;
3198 end if;
3199 elsif g_update_type='MASS' then
3200 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
3210 commit;--release any rollback
3211 --if there is a out NOCOPY of memory issue, retry an update using row by row
3212 write_to_log_file_n('Memory issue with Mass Update. Retrying using ROW_BY_ROW');
3213 l_update_type:='ROW-BY-ROW';
3214 goto start_update;
3215 elsif sqlcode=-00060 then
3216 if g_debug then
3217 write_to_log_file_n('Deadlock detected. Try again after sleep');
3218 end if;
3219 DBMS_LOCK.SLEEP(g_sleep_time);
3220 goto start_update;
3221 else
3222 g_status_message:=sqlerrm;
3223 write_to_log_file_n(g_status_message);
3224 write_to_log_file('Problem stmt '||g_update_stmt);
3225 return false;
3226 end if;
3227 end ;
3228 elsif g_update_type='DELETE-INSERT' then
3229 l_stmt:='delete '||g_fact_name||' where exists (select 1 from '||g_update_rowid_table||' where '||
3230 g_update_rowid_table||'.row_id1='||g_fact_name||'.rowid)';
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;
3240 exception when others then
3241 g_status_message:=sqlerrm;
3242 write_to_log_file_n(g_status_message);
3243 write_to_log_file('Problem stmt '||l_stmt);
3244 return false;
3245 end ;
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;
3255 write_to_log_file_n(g_status_message);
3256 write_to_log_file('Problem stmt '||g_update_stmt);
3257 return false;
3258 end ;
3259 end if;
3260 if g_debug then
3261 write_to_log_file(get_time);
3262 end if;
3263 if make_update_prot_log=false then --this is the commit
3264 write_to_log_file_n('make_update_prot_log returned with error');
3265 rollback;
3266 return false;
3267 end if;
3268 g_ins_rows_processed:=nvl(g_ins_rows_processed,0)+l_total_count;
3269 g_total_update:=nvl(g_total_update,0)+l_total_count;
3270 if g_debug then
3271 write_to_log_file_n('Number of rows updated in the fact '||l_total_count);
3272 write_to_log_file('Number of rows Processed So Far into the fact '||g_ins_rows_processed);
3273 end if;
3274 return true;
3275 Exception when others then
3276 g_status_message:=sqlerrm;
3277 write_to_log_file_n('Error in update_into_fact '||sqlerrm||' '||get_time);
3278 return false;
3279 End;
3280
3281 function insert_into_fact return boolean is
3282 l_count number;
3283 begin
3284 if g_debug then
3285 write_to_log_file_n('In insert_into_fact');
3286 end if;
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);
3296 end if;
3297 if make_insert_prot_log=false then --this is the commit!!
3298 write_to_log_file_n('make_insert_prot_log returned with error');
3299 rollback;
3300 return false;
3301 end if;
3302 g_ins_rows_processed:=nvl(g_ins_rows_processed,0)+l_count;
3303 g_total_insert:=nvl(g_total_insert,0)+l_count;
3304 if g_debug then
3305 write_to_log_file_n('Number of rows moved into the fact '||l_count);
3306 write_to_log_file('Number of rows Processed So Far into the fact '||g_ins_rows_processed);
3307 end if;
3308 return true;
3309 Exception when others then
3310 g_status_message:=sqlerrm;
3311 write_to_log_file_n('Error in insert_into_fact '||sqlerrm||' '||get_time);
3312 return false;
3313 End;
3314
3318 l_analyze boolean:=false;
3315 procedure clean_up is
3316 l_table_owner varchar2(400);
3317 l_date date;
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;
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;
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;
3343 write_to_log_file_n(g_status_message);
3344 End;
3345
3346 function update_log_status_0(p_log varchar2) return boolean is
3347 l_stmt varchar2(2000);
3348 Begin
3349 if g_debug then
3350 write_to_log_file_n('In update_ilog_status_2');
3351 end if;
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');
3361 end if;
3362 if g_debug then
3363 write_to_log_file_n('Updated '||sql%rowcount||' rows in '||p_log||' from status 1 to status 0'||get_time);
3364 end if;
3365 return true;
3366 Exception when others then
3367 g_status_message:=sqlerrm;
3368 write_to_log_file_n(g_status_message);
3369 return false;
3370 End;
3371
3372 function update_ilog_status_2 return boolean is
3373 l_stmt varchar2(2000);
3374 Begin
3375 if g_debug then
3376 write_to_log_file_n('In update_ilog_status_2');
3377 end if;
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);
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;
3393 write_to_log_file_n(g_status_message);
3394 return false;
3395 End;
3396
3397 function update_dlog_status_2 return boolean is
3398 l_stmt varchar2(2000);
3399 Begin
3400 if g_debug then
3401 write_to_log_file_n('In update_dlog_status_2');
3402 end if;
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);
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;
3418 write_to_log_file_n(g_status_message);
3419 return false;
3420 End;
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
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;
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
3440 if g_parallel is null then
3441 l_divide:=2;
3442 else
3443 l_divide:=g_parallel;
3444 end if;
3445 g_delete_rowid_stmt:=g_delete_rowid_stmt||' storage(initial '||g_fact_next_extent/2||' next '||
3446 (g_fact_next_extent/l_divide)||' pctincrease 0 MAXEXTENTS 2147483645) ';
3447 end if;
3448 if g_parallel is not null then
3449 g_delete_rowid_stmt:=g_delete_rowid_stmt||' parallel (degree '||g_parallel||') ';
3450 end if;
3451 g_delete_rowid_stmt:=g_delete_rowid_stmt||' ';
3452 g_delete_rowid_stmt:=g_delete_rowid_stmt||' as select /*+ ORDERED */ ';
3453 if g_update_type='DELETE-INSERT' then
3454 for i in 1..g_number_input_params loop
3455 if g_fk_flag(i)=false and g_groupby_col_flag(i)=false then
3459 g_delete_rowid_stmt:=g_delete_rowid_stmt||g_temp_fact_name||'.'||g_output_params(i)||' '||
3456 g_delete_rowid_stmt:=g_delete_rowid_stmt||'nvl('||g_fact_name||'.'||g_output_params(i)||',0)-nvl('||
3457 g_temp_fact_name||'.'||g_output_params(i)||',0) '||g_output_params(i)||',';
3458 else
3460 g_output_params(i)||',';
3461 end if;
3462 end loop;
3463 else
3464 for i in 1..g_number_input_params loop
3465 if g_fk_flag(i)=false and g_groupby_col_flag(i)=false then
3466 g_delete_rowid_stmt:=g_delete_rowid_stmt||' nvl('||g_temp_fact_name||'.'||g_output_params(i)||',0) '||
3467 g_output_params(i)||',';
3468 end if;
3469 end loop;
3470 end if;
3471 if g_update_type='DELETE-INSERT' then
3472 g_delete_rowid_stmt:=g_delete_rowid_stmt||g_fact_name||'.rowid row_id1,'||
3473 g_fact_name||'.CREATION_DATE CREATION_DATE from '||g_temp_fact_name||','||g_fact_name||' where ';
3474 else
3475 g_delete_rowid_stmt:=g_delete_rowid_stmt||g_fact_name||'.rowid row_id1 from '||g_temp_fact_name||','||
3476 g_fact_name||' where ';
3477 end if;
3478 /*
3479 in the where clause, if we use nvl(...), we lose index use
3480 so for fk, we dont use nvl . this is because. fk cannot be null and the index
3481 is on the fks.
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
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('||
3496 g_temp_fact_name||'.'||g_output_group_by_cols(i)||',0) and ';
3497 elsif l_data_type(l_index) ='DATE' then
3498 g_delete_rowid_stmt:=g_delete_rowid_stmt||' nvl('||g_fact_name||'.'||g_output_group_by_cols(i)||
3499 ',sysdate)=nvl('||g_temp_fact_name||'.'||g_output_group_by_cols(i)||',sysdate) and ';
3500 else
3501 g_delete_rowid_stmt:=g_delete_rowid_stmt||' '||g_fact_name||'.'||g_output_group_by_cols(i)||'='||
3502 g_temp_fact_name||'.'||g_output_group_by_cols(i)||' and ';
3503 end if;
3504 else
3505 g_delete_rowid_stmt:=g_delete_rowid_stmt||' '||g_fact_name||'.'||g_output_group_by_cols(i)||'='||
3506 g_temp_fact_name||'.'||g_output_group_by_cols(i)||' and ';
3507 end if;
3508 end if;
3509 end loop;
3510 g_delete_rowid_stmt:=substr(g_delete_rowid_stmt,1,length(g_delete_rowid_stmt)-4);
3511 end if;
3512 return true;
3513 Exception when others then
3514 g_status_message:=sqlerrm;
3515 write_to_log_file_n(g_status_message);
3516 return false;
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
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;
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
3536 if g_parallel is null then
3537 l_divide:=2;
3538 else
3539 l_divide:=g_parallel;
3540 end if;
3541 g_update_rowid_stmt:=g_update_rowid_stmt||' storage(initial '||g_fact_next_extent/2||' next '||
3542 (g_fact_next_extent/l_divide)||' pctincrease 0 MAXEXTENTS 2147483645) ';
3543 end if;
3544 if g_parallel is not null then
3545 g_update_rowid_stmt:=g_update_rowid_stmt||' parallel (degree '||g_parallel||') ';
3546 end if;
3547 g_update_rowid_stmt:=g_update_rowid_stmt||' ';
3548 g_update_rowid_stmt:=g_update_rowid_stmt||' as select /*+ ORDERED */ ';
3549 if g_update_type='DELETE-INSERT' then
3550 for i in 1..g_number_input_params loop
3551 if g_fk_flag(i)=false and g_groupby_col_flag(i)=false then
3552 g_update_rowid_stmt:=g_update_rowid_stmt||' nvl('||g_fact_name||'.'||g_output_params(i)||',0)+nvl('||
3553 g_temp_fact_name||'.'||g_output_params(i)||',0) '||g_output_params(i)||',';
3554 else --we need the keys also in this update mode for inserts
3555 g_update_rowid_stmt:=g_update_rowid_stmt||g_temp_fact_name||'.'||g_output_params(i)||' '||
3556 g_output_params(i)||',';
3557 end if;
3558 end loop;
3559 else
3560 for i in 1..g_number_input_params loop
3561 if g_fk_flag(i)=false and g_groupby_col_flag(i)=false then
3562 g_update_rowid_stmt:=g_update_rowid_stmt||' nvl('||g_temp_fact_name||'.'||g_output_params(i)||',0) '||
3566 end if;
3563 g_output_params(i)||',';
3564 end if;
3565 end loop;
3567 if g_update_type='DELETE-INSERT' then
3568 g_update_rowid_stmt:=g_update_rowid_stmt||g_fact_name||'.rowid row_id1,'||
3569 g_fact_name||'.CREATION_DATE CREATION_DATE from '||g_temp_fact_name||','||g_fact_name||' where ';
3570 else
3571 g_update_rowid_stmt:=g_update_rowid_stmt||g_fact_name||'.rowid row_id1,'||g_temp_fact_name||'.rowid row_id '||
3572 ' from '||g_temp_fact_name||','||g_fact_name||' where ';
3573 end if;
3574 /*
3575 in the where clause, if we use nvl(...), we lose index use
3576 so for fk, we dont use nvl . this is because. fk cannot be null and the index
3577 is on the fks.
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
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('||
3592 g_temp_fact_name||'.'||g_output_group_by_cols(i)||',0) and ';
3593 elsif l_data_type(l_index) ='DATE' then
3594 g_update_rowid_stmt:=g_update_rowid_stmt||' nvl('||g_fact_name||'.'||g_output_group_by_cols(i)||
3595 ',sysdate)=nvl('||g_temp_fact_name||'.'||g_output_group_by_cols(i)||',sysdate) and ';
3596 else
3597 g_update_rowid_stmt:=g_update_rowid_stmt||' '||g_fact_name||'.'||g_output_group_by_cols(i)||'='||
3598 g_temp_fact_name||'.'||g_output_group_by_cols(i)||' and ';
3599 end if;
3600 else
3601 g_update_rowid_stmt:=g_update_rowid_stmt||' '||g_fact_name||'.'||g_output_group_by_cols(i)||'='||
3602 g_temp_fact_name||'.'||g_output_group_by_cols(i)||' and ';
3603 end if;
3604 end if;
3605 end loop;
3606 g_update_rowid_stmt:=substr(g_update_rowid_stmt,1,length(g_update_rowid_stmt)-4);
3607 end if;
3608 return true;
3609 Exception when others then
3610 g_status_message:=sqlerrm;
3611 write_to_log_file_n(g_status_message);
3612 return false;
3613 End;
3614
3615 function insert_rowid_table_stmt return boolean is
3616 Begin
3617 if g_debug then
3618 write_to_log_file_n('In insert_rowid_table_stmt');
3619 end if;
3620 g_insert_rowid_stmt:='create table '||g_insert_rowid_table||' tablespace '||g_op_table_space;
3621 if g_parallel is not null then
3622 g_insert_rowid_stmt:=g_insert_rowid_stmt||' parallel (degree '||g_parallel||') ';
3623 end if;
3624 g_insert_rowid_stmt:=g_insert_rowid_stmt||' ';
3625 g_insert_rowid_stmt:=g_insert_rowid_stmt||' as select rowid row_id from '||
3626 g_temp_fact_name||' MINUS select row_id row_id from '||g_update_rowid_table||' ';
3627 /*
3628 if g_debug then
3629 write_to_log_file_n('g_insert_rowid_stmt is '||g_insert_rowid_stmt);
3630 end if;*/
3631 return true;
3632 Exception when others then
3633 g_status_message:=sqlerrm;
3634 write_to_log_file_n(g_status_message);
3635 return false;
3636 End;
3637
3638 function execute_ddata_into_rowid_table return boolean is
3639 Begin
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);
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;
3655 exception when others then
3656 g_status_message:=sqlerrm;
3657 write_to_log_file_n(g_status_message);
3658 return false;
3659 end;
3660 if create_index_drowid_table = false then
3661 write_to_log_file_n('create_index_drowid_table returned with error');
3662 return false;
3663 end if;
3664 return true;
3665 Exception when others then
3666 g_status_message:=sqlerrm;
3667 write_to_log_file_n(g_status_message);
3668 return false;
3669 End;
3670
3671
3672 function execute_data_into_rowid_table return boolean is
3673 l_stmt varchar2(4000);
3674 l_count number;
3675 Begin
3676 if g_debug then
3677 write_to_log_file_n('In execute_data_into_rowid_table');
3678 end if;
3679 --create the insert lock table
3680 if create_insert_lock_table=false then
3681 return false;
3682 end if;
3683 begin
3684 --drop the update rowid table first
3688 if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_fact_name)=1 then
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;
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);
3693 end if;
3694 execute immediate l_stmt;
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;
3704 end if;
3705 exception when others then
3706 g_status_message:=sqlerrm;
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);
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');
3722 end if;
3723 exception when others then
3724 g_status_message:=sqlerrm;
3725 write_to_log_file_n(g_status_message);
3726 return false;
3727 end;
3728 if l_count=0 then --then there is no need to keep this table as a lock as there are going to be no inserts
3729 if drop_insert_lock_table=false then
3730 return false;
3731 end if;
3732 end if;
3733 if create_index_rowid_table = false then
3734 write_to_log_file_n('create_index_rowid_table returned with error');
3735 return false;
3736 end if;
3737 return true;
3738 Exception when others then
3739 g_status_message:=sqlerrm;
3740 write_to_log_file_n(g_status_message);
3741 return false;
3742 End;
3743
3744 function create_index_drowid_table return boolean is
3745 l_stmt varchar2(2000);
3746 Begin
3747 if g_debug then
3748 write_to_log_file_n('In create_index_drowid_table');
3749 end if;
3750 l_stmt:='create unique index '||g_delete_rowid_table||'u1 on '||g_delete_rowid_table||'(row_id1) '||
3751 ' tablespace '||g_op_table_space;
3752 if g_parallel is not null then
3753 l_stmt:=l_stmt||' parallel '||g_parallel;
3754 end if;
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;
3764 write_to_log_file_n(g_status_message);
3765 write_to_log_file('problem statement '||l_stmt);
3766 return false;
3767 End;
3768
3769 function create_index_rowid_table return boolean is
3770 l_stmt varchar2(2000);
3771 Begin
3772 if g_debug then
3773 write_to_log_file_n('In create_index_rowid_table');
3774 end if;
3775 l_stmt:='create unique index '||g_update_rowid_table||'u1 on '||g_update_rowid_table||'(row_id1) '||
3776 ' tablespace '||g_op_table_space;
3777 if g_parallel is not null then
3778 l_stmt:=l_stmt||' parallel '||g_parallel;
3779 end if;
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;
3789 Exception when others then
3790 g_status_message:=sqlerrm;
3791 write_to_log_file_n(g_status_message);
3792 write_to_log_file('problem statement '||l_stmt);
3793 return false;
3794 End;
3795
3796 function make_is_groupby_col return boolean is
3797 Begin
3798 if g_debug then
3799 write_to_log_file_n('In make_is_groupby_col');
3800 end if;
3801 for i in 1..g_number_input_params loop
3802 if is_groupby_col(g_output_params(i))=false then
3803 g_groupby_col_flag(i):=false;
3804 else
3805 g_groupby_col_flag(i):=true;
3806 end if;
3807 end loop;
3808 return true;
3809 Exception when others then
3810 g_status_message:=sqlerrm;
3811 write_to_log_file_n(g_status_message);
3812 return false;
3813 End;
3814
3815 function make_is_fk_flag return boolean is
3816 Begin
3817 if g_debug then
3818 write_to_log_file_n('In make_is_fk_flag');
3819 end if;
3820 for i in 1..g_number_input_params loop
3824 if g_groupby_col_flag(i) then
3821 if is_tgt_fk(g_output_params(i))=false then
3822 g_fk_flag(i):=false;
3823 else
3825 g_fk_flag(i):=true;
3826 else
3827 g_fk_flag(i):=false;
3828 end if;
3829 end if;
3830 end loop;
3831 return true;
3832 Exception when others then
3833 g_status_message:=sqlerrm;
3834 write_to_log_file_n(g_status_message);
3835 return false;
3836 End;
3837
3838 function is_tgt_fk_mapped return boolean is
3839 l_found boolean;
3840 Begin
3841 for i in 1..g_number_fact_fks loop
3842 l_found:=false;
3843 for j in 1..g_number_input_params loop
3844 if g_fk_flag(j) then
3845 if g_output_params(j)=g_fact_fks(i) then
3846 l_found:=true;
3847 exit;
3848 end if;
3849 end if;
3850 end loop;
3851 g_fact_fks_mapped(i):=l_found;
3852 end loop;
3853 --g_fact_fks_mapped
3854 return true;
3855 Exception when others then
3856 g_status_message:=sqlerrm;
3857 write_to_log_file_n(g_status_message);
3858 return false;
3859 End;
3860
3861 procedure init_all(p_job_id number) is
3862 l_fact_name varchar2(400);
3863 l_fact_name_org varchar2(400);
3864 Begin
3865 if p_job_id is null then
3866 l_fact_name:='TAB_'||g_fact_id||'_'||g_src_object_id||'_';
3867 else
3868 l_fact_name:='TAB_'||g_fact_id||'_'||g_src_object_id||'_'||p_job_id||'_';
3869 end if;
3870 l_fact_name_org:='TAB_'||g_fact_id||'_'||g_src_object_id||'_';
3871 g_insert_lock_table:=g_bis_owner||'.INSERT_LOCK_'||g_fact_id;--should this be a passed down parameter?
3872 g_naedw_pk:=0;
3873 g_temp_fact_name:=g_bis_owner||'.'||l_fact_name||'T';
3874 g_summarize_temp2:=g_bis_owner||'.'||l_fact_name||'T2';
3875 g_summarize_temp3:=g_bis_owner||'.'||l_fact_name||'T3';
3876 g_insert_rowid_table:=g_bis_owner||'.'||l_fact_name||'IR';
3877 g_update_rowid_table:=g_bis_owner||'.'||l_fact_name||'UR';
3878 g_delete_rowid_table:=g_bis_owner||'.'||l_fact_name||'DR';
3879 g_insert_prot_log :=g_bis_owner||'.'||l_fact_name||'PI';
3880 g_update_prot_log :=g_bis_owner||'.'||l_fact_name||'PU';
3881 g_delete_prot_log :=g_bis_owner||'.'||l_fact_name||'PD';
3882 g_ins_rows_processed:=0;
3883 g_fact_type:='DERIVED';
3884 g_filter_stmt:=null;
3885 g_err_rec_flag:=false;
3886 g_err_rec_flag_d:=false;
3887 g_total_insert:=0;
3888 g_total_update:=0;
3889 g_total_delete:=0;
3890 g_skip_ilog:=false;
3891 g_skip_ilog_update:=false;
3892 g_skip_dlog_update:=false;
3893 g_type_ilog_generation:='CTAS';
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
3903 g_fact_next_extent:=16777216;
3904 end if;
3905 g_src_join_nl:=true;
3906 g_ilog_small:=g_ilog||'S';
3907 g_dlog_small:=g_dlog||'S';
3908 g_over:=false;
3909 Exception when others then
3910 g_status_message:=sqlerrm;
3911 write_to_log_file_n(g_status_message);
3912 End;
3913
3914 function get_status_message return varchar2 is
3915 begin
3916 return g_status_message;
3917 Exception when others then
3918 write_to_log_file_n('Error in get_status_message '||sqlerrm);
3919 return null;
3920 End;
3921
3922 function get_time return varchar2 is
3923 begin
3924 return ' Time:'||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');
3925 Exception when others then
3926 write_to_log_file_n('Error in get_time '||sqlerrm);
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
3936 procedure write_to_log_file_n(p_message varchar2) is
3937 begin
3938 write_to_log_file(' ');
3939 write_to_log_file(p_message);
3940 Exception when others then
3941 null;
3942 End;
3943
3944 function summarize_fact_data return boolean is
3945 Begin
3946 if g_debug then
3947 write_to_log_file_n('In summarize_fact_data');
3948 end if;
3949 if create_summarize_temp2=false then
3950 return false;
3951 end if;
3952 if create_summarize_temp3=false then
3953 return false;
3954 end if;
3955 if create_summarize_temp=false then
3956 return false;
3957 end if;
3958 return true;
3959 Exception when others then
3960 g_status_message:=sqlerrm;
3961 write_to_log_file_n(g_status_message);
3962 return false;
3963 End;
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
3973 if g_debug then
3977 if EDW_OWB_COLLECTION_UTIL.drop_table(g_summarize_temp2)=false then
3974 write_to_log_file_n('In create_summarize_temp2');
3975 end if;
3976 l_summarize_temp2:=g_summarize_temp2||'A';
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
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;
3991 end loop;
3992 if g_debug then
3993 write_to_log_file_n('The dimensions using the PK');
3994 for i in 1..g_number_fact_fks loop
3995 if g_higher_level(i)=true and g_fact_fks_mapped(i) and l_use_dim_pk(i) then
3996 write_to_log_file(g_parent_dim(i)||' '||g_level_pk(i));
3997 end if;
3998 end loop;
3999 write_to_log_file_n('The dimensions using the PK_KEY');
4000 for i in 1..g_number_fact_fks loop
4001 if g_higher_level(i)=true and g_fact_fks_mapped(i) and l_use_dim_pk(i)=false then
4002 write_to_log_file(g_parent_dim(i)||' '||g_level_pk_key(i));
4003 end if;
4004 end loop;
4005 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
4015 l_stmt:='create table '||l_summarize_temp2||' tablespace '||g_op_table_space;
4016 if g_parallel is not null then
4017 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
4018 end if;
4019 l_stmt:=l_stmt||' ';
4020 l_stmt:=l_stmt||' as select ';
4021 --measures
4022 for i in 1..g_number_input_params loop
4023 if g_groupby_col_flag(i)=false then
4024 l_stmt:=l_stmt||' SUM(temp.'||g_output_params(i)||') '||g_output_params(i)||',';
4025 else
4026 --this will include the fks that are not rolling upto higher levels and cols that are grouped by
4027 if g_higher_level_flag(i) =false then
4028 l_stmt:=l_stmt||' temp.'||g_output_params(i)||' '||g_output_params(i)||',';
4029 end if;
4030 end if;
4031 end loop;
4032 for i in 1..g_number_fact_fks loop
4033 if g_higher_level(i)=true and g_fact_fks_mapped(i) then
4034 if l_use_dim_pk(i) then
4035 l_stmt:=l_stmt||g_parent_dim(i)||i||'.'||g_level_pk(i)||' '||g_fact_fks(i)||',';
4036 else
4037 l_stmt:=l_stmt||g_parent_dim(i)||i||'.'||g_level_pk_key(i)||' '||g_fact_fks(i)||',';
4038 end if;
4039 end if;
4040 end loop;
4041 if g_number_df_extra_fks > 0 then
4042 for i in 1..g_number_df_extra_fks loop
4043 --l_stmt:=l_stmt||' temp.'||g_df_extra_fks(i)||' '||g_df_extra_fks(i)||',';
4044 l_stmt:=l_stmt||g_naedw_pk||' '||g_df_extra_fks(i)||',';
4045 end loop;
4046 end if;
4047 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
4048 l_stmt:=l_stmt||' from '||g_temp_fact_name||' temp,';
4049 for i in 1..g_number_fact_fks loop
4050 if g_higher_level(i)=true and g_fact_fks_mapped(i) then
4051 l_stmt:=l_stmt||g_parent_dim(i)||' '||g_parent_dim(i)||i||',';
4052 end if;
4053 end loop;
4054 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
4055 l_stmt:=l_stmt||' where ';
4056 for i in 1..g_number_fact_fks loop
4057 if g_higher_level(i)=true and g_fact_fks_mapped(i) then
4058 l_stmt:=l_stmt||g_parent_dim(i)||i||'.'||g_dim_pk_key(i)||'=temp.'||g_fact_fks(i)||' and ';
4059 end if;
4060 end loop;
4061 l_stmt:=substr(l_stmt,1,length(l_stmt)-4);
4062 l_stmt:=l_stmt||' group by ';
4063 for i in 1..g_number_fact_fks loop
4064 if g_higher_level(i)=true and g_fact_fks_mapped(i) then
4065 if l_use_dim_pk(i) then
4066 l_stmt:=l_stmt||g_parent_dim(i)||i||'.'||g_level_pk(i)||',';
4067 else
4068 l_stmt:=l_stmt||g_parent_dim(i)||i||'.'||g_level_pk_key(i)||',';
4069 end if;
4070 end if;
4071 end loop;
4072 if g_number_group_by_cols > 0 then
4073 for i in 1..g_number_group_by_cols loop
4074 for j in 1..g_number_fact_fks loop
4075 if g_higher_level(j)=false and g_fact_fks(j)=g_output_group_by_cols(i) then
4076 l_stmt:=l_stmt||'temp.'||g_output_group_by_cols(i)||',';
4077 exit;
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;
4087 /*
4088 if g_number_df_extra_fks > 0 then
4089 for i in 1..g_number_df_extra_fks loop
4090 l_stmt:=l_stmt||' temp.'||g_df_extra_fks(i)||',';
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;
4103
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
4104 l_stmt:='create table '||g_summarize_temp2||' tablespace '||g_op_table_space;
4105 if g_parallel is not null then
4106 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
4107 end if;
4108 l_stmt:=l_stmt||' ';
4109 l_stmt:=l_stmt||' as select ';
4110 for i in 1..g_number_input_params loop
4111 if g_groupby_col_flag(i)=false then
4112 l_stmt:=l_stmt||l_summarize_temp2||'.'||g_output_params(i)||',';
4113 else
4114 --this will include the fks that are not rolling upto higher levels and cols that are grouped by
4115 if g_higher_level_flag(i) =false then
4116 l_stmt:=l_stmt||l_summarize_temp2||'.'||g_output_params(i)||',';
4117 end if;
4118 end if;
4119 end loop;
4120 for i in 1..g_number_fact_fks loop
4121 if g_higher_level(i)=true and g_fact_fks_mapped(i) then
4122 if l_use_dim_pk(i) then
4123 l_stmt:=l_stmt||l_summarize_temp2||'.'||g_fact_fks(i)||' '||g_fact_fks(i)||',';
4124 else
4125 l_stmt:=l_stmt||l_parent_ltc(i)||i||'.'||l_parent_pk(i)||' '||g_fact_fks(i)||',';
4126 end if;
4127 end if;
4128 end loop;
4129 if g_number_df_extra_fks > 0 then
4130 for i in 1..g_number_df_extra_fks loop
4131 l_stmt:=l_stmt||l_summarize_temp2||'.'||g_df_extra_fks(i)||',';
4132 end loop;
4133 end if;
4134 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
4135 l_stmt:=l_stmt||' from '||l_summarize_temp2||',';
4136 for i in 1..g_number_fact_fks loop
4137 if g_higher_level(i)=true and g_fact_fks_mapped(i) and l_use_dim_pk(i)=false then
4138 l_stmt:=l_stmt||l_parent_ltc(i)||' '||l_parent_ltc(i)||i||',';
4139 end if;
4140 end loop;
4141 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
4142 l_stmt:=l_stmt||' where ';
4143 for i in 1..g_number_fact_fks loop
4144 if g_higher_level(i)=true and g_fact_fks_mapped(i) and l_use_dim_pk(i)=false then
4145 l_stmt:=l_stmt||l_parent_ltc(i)||i||'.'||l_parent_pk_key(i)||'='||
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;
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
4162 g_status_message:=sqlerrm;
4163 write_to_log_file_n(g_status_message);
4164 return false;
4165 End;
4166
4167 function create_summarize_temp3 return boolean is
4168 l_stmt varchar2(10000);
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
4178 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
4179 end if;
4180 l_stmt:=l_stmt||' ';
4181 l_stmt:=l_stmt||' as select ';
4182 l_stmt:=l_stmt||' temp2.rowid row_id,';
4183 for i in 1..g_number_fact_fks loop
4184 if g_higher_level(i) and g_fact_fks_mapped(i) then
4185 l_stmt:=l_stmt||g_parent_dim(i)||i||'.'||g_dim_pk_key(i)||' '||g_fact_fks(i)||',';
4186 end if;
4187 end loop;
4188 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
4189 l_stmt:=l_stmt||' from '||g_summarize_temp2||' temp2,';
4190 for i in 1..g_number_fact_fks loop
4191 if g_higher_level(i)=true and g_fact_fks_mapped(i) then
4192 l_stmt:=l_stmt||g_parent_dim(i)||' '||g_parent_dim(i)||i||',';
4193 end if;
4194 end loop;
4195 l_stmt:=substr(l_stmt,1,length(l_stmt)-1);
4196 l_stmt:=l_stmt||' where ';
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;
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,
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;
4219 write_to_log_file_n(g_status_message);
4220 return false;
4221 End;
4222
4223 function create_summarize_temp return boolean is
4224 l_stmt varchar2(10000);
4228 end if;
4225 Begin
4226 if g_debug then
4227 write_to_log_file_n('In create_summarize_temp');
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
4234 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
4235 end if;
4236 l_stmt:=l_stmt||' ';
4237 l_stmt:=l_stmt||' as select ';
4238 for i in 1..g_number_input_params loop
4239 if g_groupby_col_flag(i)=false then
4240 l_stmt:=l_stmt||'temp2.'||g_output_params(i)||',';
4241 else
4242 if g_higher_level_flag(i) =false then
4243 l_stmt:=l_stmt||'temp2.'||g_output_params(i)||',';
4244 end if;
4245 end if;
4246 end loop;
4247 for i in 1..g_number_fact_fks loop
4248 if g_higher_level(i)=true and g_fact_fks_mapped(i) then
4249 l_stmt:=l_stmt||'temp3.'||g_fact_fks(i)||',';
4250 end if;
4251 end loop;
4252 if g_number_df_extra_fks > 0 then
4253 for i in 1..g_number_df_extra_fks loop
4254 l_stmt:=l_stmt||' temp2.'||g_df_extra_fks(i)||',';
4255 end loop;
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;
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;
4270 write_to_log_file_n(g_status_message);
4271 return false;
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;
4281 end if;
4282 return true;
4283 Exception when others then
4284 g_status_message:=sqlerrm;
4285 write_to_log_file_n(g_status_message);
4286 return false;
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
4296 g_status_message:=sqlerrm;
4297 write_to_log_file_n(g_status_message);
4298 return false;
4299 End;
4300
4301 function make_insert_prot_log return boolean is
4302 l_stmt varchar2(2000);
4303 Begin
4304 --l_stmt:='create table '||g_insert_prot_log||' (row_id rowid) '||' tablespace '||g_op_table_space;
4305 l_stmt:='create table '||g_insert_prot_log||' tablespace '||g_op_table_space||
4306 ' storage(initial 4M next 4M pctincrease 0) ';
4307 if g_parallel is not null then
4308 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
4309 end if;
4310 l_stmt:=l_stmt||' as select row_id from '||g_ilog||' where status=1';
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;
4320 write_to_log_file_n('Error in make_insert_prot_log '||g_status_message);
4321 return false;
4322 End;
4323
4324 function make_update_prot_log return boolean is
4325 l_stmt varchar2(2000);
4326 Begin
4327 --l_stmt:='create table '||g_update_prot_log||' (row_id rowid) '||' tablespace '||g_op_table_space;
4328 l_stmt:='create table '||g_update_prot_log||' tablespace '||g_op_table_space||
4329 ' storage(initial 4M next 4M pctincrease 0) ';
4330 if g_parallel is not null then
4331 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
4332 end if;
4333 l_stmt:=l_stmt||' as select row_id from '||g_ilog||' where status=1';
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;
4343 write_to_log_file_n(g_status_message);
4344 return false;
4345 End;
4346
4347 function make_delete_prot_log return boolean is
4348 l_stmt varchar2(2000);
4349 Begin
4350 --l_stmt:='create table '||g_delete_prot_log||' (row_id rowid) '||' tablespace '||g_op_table_space;
4351 l_stmt:='create table '||g_delete_prot_log||' tablespace '||g_op_table_space||
4352 ' storage(initial 4M next 4M pctincrease 0) ';
4353 if g_parallel is not null then
4354 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
4355 end if;
4356 l_stmt:=l_stmt||' as select row_id from '||g_dlog||' where status=1';
4357 if g_debug then
4361 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_delete_prot_log,instr(g_delete_prot_log,'.')+1,
4358 write_to_log_file_n('Going to execute '||l_stmt);
4359 end if;
4360 execute immediate l_stmt;
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;
4366 write_to_log_file_n(g_status_message);
4367 return false;
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;
4377 end if;
4378 return true;
4379 Exception when others then
4380 g_status_message:=sqlerrm;
4381 write_to_log_file_n(g_status_message);
4382 return false;
4383 End;
4384
4385 procedure insert_into_temp_log(p_flag varchar2) is
4386 Begin
4387 g_number_ins_req_coll:=1;
4388 g_ins_instance_name(1):=null;
4389 g_ins_request_id_table(1):=g_load_fk;
4390 g_ins_rows_dangling(1):=0;
4391 g_ins_rows_duplicate(1):=0;
4392 g_ins_rows_error(1):=0;
4393 if p_flag='+' then
4394 g_ins_rows_ready(1):=g_ins_rows_processed;
4395 g_ins_rows_processed_tab(1):=g_ins_rows_processed;
4396 g_ins_rows_collected(1):=g_ins_rows_processed;
4397 else --delete data
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,
4407 g_ins_request_id_table,
4408 g_ins_rows_ready,
4409 g_ins_rows_processed_tab,
4410 g_ins_rows_collected,
4411 g_ins_rows_dangling,
4412 g_ins_rows_duplicate,
4413 g_ins_rows_error,
4414 null,
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;
4424 write_to_log_file_n(g_status_message);
4425 End;
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;
4435 write_to_log_file_n(g_status_message);
4436 End;
4437
4438 --if g_debug is on...
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
4448 g_status_message:=sqlerrm;
4449 write_to_log_file_n(g_status_message);
4450 End;
4451
4452 function make_g_higher_level_flag return boolean is
4453 l_found boolean;
4454 Begin
4455 for i in 1..g_number_input_params loop
4456 l_found:=false;
4457 for j in 1..g_number_fact_fks loop
4458 if g_output_params(i)=g_fact_fks(j) and g_higher_level(j) and g_fact_fks_mapped(j) then
4459 l_found:=true;
4460 exit;
4461 end if;
4462 end loop;
4463 if l_found then
4464 g_higher_level_flag(i):=true;
4465 else
4466 g_higher_level_flag(i):=false;
4467 end if;
4468 end loop;
4469 for i in 1..g_number_fact_fks loop
4470 if g_higher_level(i) and g_fact_fks_mapped(i) then
4471 g_fact_type:='SUMMARY';
4472 exit;
4473 end if;
4474 end loop;
4475 return true;
4476 Exception when others then
4477 g_status_message:=sqlerrm;
4478 write_to_log_file_n(g_status_message);
4479 return false;
4480 End;
4481
4482 procedure reset_profiles is
4483 Begin
4484 if g_debug then
4485 write_to_log_file_n('In reset_profiles'||get_time);
4486 end if;
4487 --EDW_ALL_COLLECT.reset_profiles;
4488 --g_collection_size:=EDW_ALL_COLLECT.g_collection_size;
4489 --g_mode:=EDW_ALL_COLLECT.g_mode;
4490 Exception when others then
4491 write_to_log_file_n('Error in reset_profiles '||sqlerrm||get_time);
4492 End;
4493
4494 function get_base_fact_count return number is
4495 TYPE CurTyp IS REF CURSOR;
4496 cv CurTyp;
4497 l_stmt varchar2(5000);
4498 l_num number;
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);
4507 l_stmt:='select /*+parallel('||g_src_object||','||g_parallel||')*/ count(*) from '||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
4508 else
4509 l_stmt:='select count(*) from '||g_src_object;
4510 end if;
4511 if g_debug then
4512 write_to_log_file_n('Going to execute '||l_stmt||get_time);
4513 end if;
4514 open cv for l_stmt;
4515 fetch cv into l_num;
4516 close cv;
4517 end if;
4518 if g_debug then
4519 write_to_log_file_n('count='||l_num||get_time);
4520 end if;
4521 return l_num;
4522 Exception when others then
4523 g_status_message:=sqlerrm;
4524 write_to_log_file_n(g_status_message);
4525 return -1;
4526 End;
4527
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
4537 l_stmt:='create table '||g_ilog||' tablespace '||g_op_table_space||
4538 ' as select rowid row_id,-10 '||g_src_pk||', 1 status from dual ';
4539 else
4540 l_stmt:='create table '||g_ilog||' tablespace '||g_op_table_space||
4541 ' as select rowid row_id, 1 status from dual ';
4542 end if;
4543 if g_debug then
4544 write_to_log_file_n('Going to execute '||l_stmt||get_time);
4545 end if;
4546 execute immediate l_stmt;
4547 return true;
4548 Exception when others then
4549 g_status_message:=sqlerrm;
4550 write_to_log_file_n(g_status_message);
4551 return false;
4552 End;
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||
4562 ' as select rowid row_id, 1 status from dual ';
4563 if g_debug then
4564 write_to_log_file_n('Going to execute '||l_stmt||get_time);
4565 end if;
4566 execute immediate l_stmt;
4567 return true;
4568 Exception when others then
4569 g_status_message:=sqlerrm;
4570 write_to_log_file_n(g_status_message);
4571 return false;
4572 End;
4573
4574 function check_src_fact_snplog return number is
4575 Begin
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;
4585 return 1;
4586 end if;
4587 return 0;
4588 Exception when others then
4589 g_status_message:=sqlerrm;
4590 write_to_log_file_n(g_status_message);
4591 return -1;
4592 End;
4593
4594 function load_new_update_data return boolean is
4595 l_stmt varchar2(10000);
4596 l_table_i_and_d varchar2(400);
4597 l_table_dlog varchar2(400);
4598 l_col varchar2(400);
4599 l_dlog_col varchar2(400);
4600 l_round varchar2(400);
4601 l_count number;
4602 l_table_1 varchar2(400);
4603 l_table_2 varchar2(400);
4604 Begin
4605 if g_debug then
4606 write_to_log_file_n('In load_new_update_data'||get_time);
4607 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;
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;
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;
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;
4635 end if;
4636 l_table_i_and_d:=g_ilog||'ID';
4637 l_table_dlog:=g_ilog||'DL';
4638 l_table_1:=g_ilog||'T1';
4639 l_table_2:=g_ilog||'T2';
4640 l_stmt:='create table '||l_table_i_and_d||' tablespace '||g_op_table_space;
4641 if g_parallel is not null then
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);
4651 end if;
4652 execute immediate l_stmt;
4653 l_count:=sql%rowcount;
4654 if g_debug then
4658 if g_debug then
4655 write_to_log_file_n('Created with '||l_count||' rows '||get_time);
4656 end if;
4657 if l_count=0 then
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||') ';
4668 end if;
4669 l_stmt:=l_stmt||' as select /*+ordered*/ ';
4670 if g_parallel is not null then
4671 l_stmt:=l_stmt||' /*+parallel(A,'||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);
4681 end if;
4682 execute immediate l_stmt;
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
4692 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
4693 end if;
4694 l_stmt:=l_stmt||' as select A.rowid row_id from '||l_table_dlog||' A,'||g_ilog||' B where ';
4695 if g_src_snplog_has_pk 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);
4705 end if;
4706 execute immediate l_stmt;
4707 l_count:=sql%rowcount;
4708 if g_debug then
4709 write_to_log_file_n('Created with '||l_count||' rows '||get_time);
4710 end if;
4711 l_stmt:='create table '||l_table_2||' tablespace '||g_op_table_space;
4712 if g_parallel is not null then
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);
4722 end if;
4723 execute immediate l_stmt;
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='||
4733 l_table_dlog||'.rowid';
4734 else
4735 l_stmt:='insert into '||g_ilog||'(row_id,status,'||l_round||') select row_id1,0,'||l_round||
4736 ' from '||l_table_2||','||l_table_dlog||' where '||l_table_2||'.row_id='||l_table_dlog||'.rowid';
4737 end if;
4738 if g_debug then
4739 write_to_log_file_n('Going to exec '||l_stmt||get_time);
4740 end if;
4741 execute immediate l_stmt;
4742 l_count:=sql%rowcount;
4743 commit;
4744 if g_debug then
4745 write_to_log_file_n('Inserted '||l_count||' rows '||get_time);
4746 end if;
4747 --insert into D
4748 l_stmt:='insert into '||g_dlog||'(row_id,row_id1,status,'||l_round||','||l_col||') select B.row_id,B.row_id1,'||
4749 '0,B.'||l_round||',B.'||l_dlog_col||' from '||l_table_2||' A,'||l_table_dlog||' B where '||
4750 'A.row_id=B.rowid';
4751 if g_debug then
4752 write_to_log_file_n('Going to exec '||l_stmt||get_time);
4753 end if;
4754 execute immediate l_stmt;
4755 l_count:=sql%rowcount;
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;
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;
4772 end if;
4773 return true;
4774 Exception when others then
4775 g_status_message:=sqlerrm;
4779
4776 write_to_log_file_n(g_status_message);
4777 return false;
4778 End;
4780 function set_g_src_join_nl(p_load_size number,p_total_records number) return boolean is
4781 l_percentage number;
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);
4791 return false;
4792 End;
4793
4794 /*
4795 This function sets the status of g_ilog and g_dlog if there are prot tables left around
4796 */
4797 function recover_from_prot return boolean is
4798 l_found boolean;
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,
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;
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||')';
4824 l_found:=true;
4825 end if;
4826 if l_found then
4827 if g_debug then
4828 write_to_log_file_n(g_stmt||get_time);
4829 end if;
4830 execute immediate g_stmt;
4831 if g_debug then
4832 write_to_log_file_n('Updated '||sql%rowcount||' rows '||get_time);
4833 end if;
4834 commit;
4835 end if;
4836 if drop_prot_tables=false then
4837 null;
4838 end if;
4839 if drop_d_prot_tables=false then
4840 null;
4841 end if;
4842 return true;
4843 Exception when others then
4844 g_status_message:=sqlerrm;
4845 write_to_log_file_n('Error in recover_from_prot '||g_status_message);
4846 return false;
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;
4856 Exception when others then
4857 g_status_message:=sqlerrm;
4858 write_to_log_file_n('Error in set_session_parameters '||g_status_message);
4859 return false;
4860 End;
4861
4862 function read_options_table(p_table varchar2) return boolean is
4863 TYPE CurTyp IS REF CURSOR;
4864 cv CurTyp;
4865 l_fk_table varchar2(80);
4866 l_skip_table varchar2(80);
4867 l_bu_table varchar2(80);
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);
4877 l_src_snplog_has_pk varchar2(2);
4878 l_err_rec_flag varchar2(2);
4879 l_err_rec_flag_d varchar2(2);
4880 Begin
4881 write_to_log_file_n('In read_options_table '||p_table);
4882 l_fk_table:=p_table||'_FK';
4883 l_skip_table:=p_table||'_SK';
4884 l_bu_table:=p_table||'_BU';
4885 g_stmt:='select '||
4886 'fact_id,'||
4887 'mapping_id,'||
4888 'src_object,'||
4889 'src_object_id,'||
4890 'conc_id,'||
4891 'conc_program_name,'||
4892 'debug,'||
4893 'collection_size,'||
4894 'parallel,'||
4895 'bis_owner,'||
4896 'table_owner ,'||
4897 'full_refresh,'||
4898 'forall_size,'||
4899 'update_type,'||
4900 'fact_dlog,'||
4901 'load_fk,'||
4902 'fresh_restart,'||
4903 'op_table_space,'||
4904 'bu_src_fact,'||
4905 'load_mode,'||
4906 'rollback,'||
4907 'src_join_nl_percentage,'||
4908 'max_threads,'||
4909 'min_job_load_size,'||
4910 'sleep_time,'||
4911 'job_status_table,'||
4912 'hash_area_size,'||
4913 'sort_area_size,'||
4914 'trace,'||
4915 'read_cfig_options,'||
4916 'ilog_table,'||
4917 'dlog_table,'||
4918 'skip_ilog_update,'||
4919 'skip_dlog_update,'||
4920 'skip_ilog,'||
4921 'src_object_ilog,'||
4922 'src_object_dlog,'||
4923 'src_snplog_has_pk,'||
4924 'err_rec_flag,'||
4925 'err_rec_flag_d,'||
4926 'dbms_job_id '||
4930 end if;
4927 ' from '||p_table;
4928 if g_debug then
4929 write_to_log_file_n(g_stmt);
4931 open cv for g_stmt;
4932 fetch cv into
4933 g_fact_id
4934 ,g_mapping_id
4935 ,g_src_object
4936 ,g_src_object_id
4937 ,g_conc_id
4938 ,g_conc_program_name
4939 ,l_debug
4940 ,g_collection_size
4941 ,g_parallel
4942 ,g_bis_owner
4943 ,g_table_owner
4944 ,l_full_refresh
4945 ,g_forall_size
4946 ,g_update_type
4947 ,g_fact_dlog
4948 ,g_load_fk
4949 ,l_fresh_restart
4950 ,g_op_table_space
4951 ,g_bu_src_fact
4952 ,g_load_mode
4953 ,g_rollback
4954 ,g_src_join_nl_percentage
4955 ,g_max_threads
4956 ,g_min_job_load_size
4957 ,g_sleep_time
4958 ,g_job_status_table
4959 ,g_hash_area_size
4960 ,g_sort_area_size
4961 ,l_trace
4962 ,l_read_cfig_options
4963 ,g_ilog_name
4964 ,g_dlog_name
4965 ,l_skip_ilog_update
4966 ,l_skip_dlog_update
4967 ,l_skip_ilog
4968 ,g_src_object_ilog
4969 ,g_src_object_dlog
4970 ,l_src_snplog_has_pk
4971 ,l_err_rec_flag
4972 ,l_err_rec_flag_d
4973 ,g_dbms_job_id;
4974 close cv;
4975 if g_load_mode is null then
4976 g_load_mode:='NORMAL';
4977 end if;
4978 if l_debug='Y' then
4979 write_to_log_file_n('The values read');
4980 write_to_log_file('g_fact_id='||g_fact_id);
4981 write_to_log_file('g_mapping_id='||g_mapping_id);
4982 write_to_log_file('g_src_object='||g_src_object);
4983 write_to_log_file('g_src_object_id='||g_src_object_id);
4984 write_to_log_file('g_conc_id='||g_conc_id);
4985 write_to_log_file('g_conc_program_name='||g_conc_program_name);
4986 write_to_log_file('l_debug='||l_debug);
4987 write_to_log_file('g_collection_size='||g_collection_size);
4988 write_to_log_file('g_parallel='||g_parallel);
4989 write_to_log_file('g_bis_owner='||g_bis_owner);
4990 write_to_log_file('g_table_owner='||g_table_owner);
4991 write_to_log_file('l_full_refresh='||l_full_refresh);
4992 write_to_log_file('g_forall_size='||g_forall_size);
4993 write_to_log_file('g_update_type='||g_update_type);
4994 write_to_log_file('g_fact_dlog='||g_fact_dlog);
4995 write_to_log_file('g_load_fk='||g_load_fk);
4996 write_to_log_file('l_fresh_restart='||l_fresh_restart);
4997 write_to_log_file('g_op_table_space='||g_op_table_space);
4998 write_to_log_file('g_bu_src_fact='||g_bu_src_fact);
4999 write_to_log_file('g_load_mode='||g_load_mode);
5000 write_to_log_file('g_rollback='||g_rollback);
5001 write_to_log_file('g_src_join_nl_percentage='||g_src_join_nl_percentage);
5002 write_to_log_file('g_max_threads='||g_max_threads);
5003 write_to_log_file('g_min_job_load_size='||g_min_job_load_size);
5004 write_to_log_file('g_sleep_time='||g_sleep_time);
5005 write_to_log_file('g_job_status_table='||g_job_status_table);
5006 write_to_log_file('g_hash_area_size='||g_hash_area_size);
5007 write_to_log_file('g_sort_area_size='||g_sort_area_size);
5008 write_to_log_file('l_trace='||l_trace);
5009 write_to_log_file('l_read_cfig_options='||l_read_cfig_options);
5010 write_to_log_file('g_ilog_name='||g_ilog_name);
5011 write_to_log_file('g_dlog_name='||g_dlog_name);
5012 write_to_log_file('l_skip_ilog_update='||l_skip_ilog_update);
5013 write_to_log_file('l_skip_dlog_update='||l_skip_dlog_update);
5014 write_to_log_file('l_skip_ilog='||l_skip_ilog);
5015 write_to_log_file('g_src_object_ilog='||g_src_object_ilog);
5016 write_to_log_file('g_src_object_dlog='||g_src_object_dlog);
5017 write_to_log_file('l_src_snplog_has_pk='||l_src_snplog_has_pk);
5018 write_to_log_file('l_err_rec_flag='||l_err_rec_flag);
5019 write_to_log_file('l_err_rec_flag_d='||l_err_rec_flag_d);
5020 write_to_log_file('g_dbms_job_id='||g_dbms_job_id);
5021 --g_dbms_job_id can the id of a dbms job or conc process. depends on g_thread_type
5022 end if;
5023 g_debug:=false;
5024 g_full_refresh:=false;
5025 g_fresh_restart:=false;
5026 g_trace:=false;
5027 g_read_cfig_options:=false;
5028 g_skip_ilog_update:=false;
5029 g_skip_dlog_update:=false;
5030 g_skip_ilog:=false;
5031 g_src_snplog_has_pk:=false;
5032 g_err_rec_flag:=false;
5033 g_err_rec_flag_d:=false;
5034 if l_debug='Y' then
5035 g_debug:=true;
5036 end if;
5037 if l_full_refresh='Y' then
5038 g_full_refresh:=true;
5039 end if;
5040 if l_fresh_restart='Y' then
5041 g_fresh_restart:=true;
5042 end if;
5043 if l_trace='Y' then
5044 g_trace:=true;
5045 end if;
5046 if l_read_cfig_options='Y' then
5047 g_read_cfig_options:=true;
5048 end if;
5049 if l_skip_ilog_update='Y' then
5050 g_skip_ilog_update:=true;
5051 end if;
5052 if l_skip_dlog_update='Y' then
5053 g_skip_dlog_update:=true;
5054 end if;
5055 if l_skip_ilog='Y' then
5056 g_skip_ilog:=true;
5057 end if;
5058 if l_src_snplog_has_pk='Y' then
5059 g_src_snplog_has_pk:=true;
5060 end if;
5061 if l_err_rec_flag='Y' then
5062 g_err_rec_flag:=true;
5063 end if;
5064 if l_err_rec_flag_d='Y' then
5065 g_err_rec_flag_d:=true;
5066 end if;
5067 g_stmt:='select '||
5068 'fact_fks,'||
5069 'higher_level,'||
5073 'level_pk,'||
5070 'parent_dim,'||
5071 'parent_level,'||
5072 'level_prefix,'||
5074 'level_pk_key,'||
5075 'dim_pk_key '||
5076 ' from '||l_fk_table;
5077 g_number_fact_fks:=1;
5078 if g_debug then
5079 write_to_log_file_n(g_stmt);
5080 end if;
5081 open cv for g_stmt;
5082 loop
5083 fetch cv into
5084 g_fact_fks(g_number_fact_fks),
5085 l_higher_level(g_number_fact_fks),
5086 g_parent_dim(g_number_fact_fks),
5087 g_parent_level(g_number_fact_fks),
5088 g_level_prefix(g_number_fact_fks),
5089 g_level_pk(g_number_fact_fks),
5090 g_level_pk_key(g_number_fact_fks),
5091 g_dim_pk_key(g_number_fact_fks);
5092 exit when cv%notfound;
5093 g_number_fact_fks:=g_number_fact_fks+1;
5094 end loop;
5095 close cv;
5096 g_number_fact_fks:=g_number_fact_fks-1;
5097 for i in 1..g_number_fact_fks loop
5098 if l_higher_level(i)='Y' then
5099 g_higher_level(i):=true;
5100 else
5101 g_higher_level(i):=false;
5102 end if;
5103 end loop;
5104 if g_debug then
5105 for i in 1..g_number_fact_fks loop
5106 write_to_log_file(g_fact_fks(i)||' '|| l_higher_level(i)||' '||g_parent_dim(i)||' '||
5107 g_parent_level(i)||' '||g_level_prefix(i)||' '||g_level_pk(i)||' '||g_level_pk_key(i)||' '||
5108 g_dim_pk_key(i));
5109 end loop;
5110 end if;
5111 g_stmt:='select skip_cols from '||l_skip_table;
5112 g_number_skip_cols:=1;
5113 if g_debug then
5114 write_to_log_file_n(g_stmt);
5115 end if;
5116 open cv for g_stmt;
5117 loop
5118 fetch cv into g_skip_cols(g_number_skip_cols);
5119 exit when cv%notfound;
5120 g_number_skip_cols:=g_number_skip_cols+1;
5121 end loop;
5122 close cv;
5123 g_number_skip_cols:=g_number_skip_cols-1;
5124 if g_debug then
5125 for i in 1..g_number_skip_cols loop
5126 write_to_log_file(g_skip_cols(i));
5127 end loop;
5128 end if;
5129 g_stmt:='select bu_tables,bu_dimensions from '||l_bu_table;
5130 g_number_bu_tables:=1;
5131 if g_debug then
5132 write_to_log_file_n(g_stmt);
5133 end if;
5134 open cv for g_stmt;
5135 loop
5136 fetch cv into g_bu_tables(g_number_bu_tables),g_bu_dimensions(g_number_bu_tables);
5137 exit when cv%notfound;
5138 g_number_bu_tables:=g_number_bu_tables+1;
5139 end loop;
5140 close cv;
5141 g_number_bu_tables:=g_number_bu_tables-1;
5142 if g_debug then
5143 for i in 1..g_number_bu_tables loop
5144 write_to_log_file(g_bu_tables(i)||' '||g_bu_dimensions(i));
5145 end loop;
5146 end if;
5147 return true;
5148 Exception when others then
5149 g_status_message:=sqlerrm;
5150 write_to_log_file_n('Error in read_options_table '||g_status_message);
5151 return false;
5152 End;
5153
5154 --in case of multi threading. each job here
5155 function make_ok_from_main_ok(
5156 p_main_ok_table_name varchar2,
5157 p_ilog_table varchar2,
5158 p_low_end number,
5159 p_high_end number,
5160 p_mode varchar2
5161 ) return boolean is
5162 l_ilog_number number;
5163 Begin
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,
5173 g_op_table_space,
5174 g_bis_owner,
5175 g_parallel,
5176 l_ilog_number)=false then
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;
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;
5191 Exception when others then
5192 g_status_message:=sqlerrm;
5193 write_to_log_file_n('Error in make_ok_from_main_ok '||g_status_message);
5194 return false;
5195 End;
5196
5197 function put_rownum_in_log_table return boolean is
5198 l_ilog_table varchar2(80);
5199 Begin
5200 if g_debug then
5201 write_to_log_file_n('In put_rownum_in_log_table');
5202 end if;
5203 l_ilog_table:=g_ilog;
5204 if substr(g_ilog,length(g_ilog),1)='A' then
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
5214 return false;
5215 end if;
5216 --for dlog
5217 l_ilog_table:=g_dlog;
5218 if substr(g_dlog,length(g_dlog),1)='A' 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
5231 Exception when others then
5228 return false;
5229 end if;
5230 return true;
5232 g_status_message:=sqlerrm;
5233 write_to_log_file_n('Error in put_rownum_in_log_table '||g_status_message);
5234 return false;
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;
5244 end if;
5245 return true;
5246 Exception when others then
5247 g_status_message:=sqlerrm;
5248 write_to_log_file_n('Error in drop_ilog_dlog_tables '||g_status_message);
5249 return false;
5250 End;
5251
5252 /*
5253 this function will prevent data corruption in derv fact. if there are 2 conc processes or jobs
5254 both trying to insert into the fact, there will be duplicate rows.
5255 so keep insert locked. so inserts will be single threaded.
5256 */
5257 function create_insert_lock_table return boolean is
5258 l_stmt varchar2(2000);
5259 TYPE CurTyp IS REF CURSOR;
5260 cv CurTyp;
5261 l_conc_id number;
5262 l_dbms_job_id number;
5263 l_status varchar2(20);
5264 ----------------------------
5265 l_my_conc_id number;
5266 l_my_job_id number;
5267 ----------------------------
5268 Begin
5269 if g_debug then
5270 write_to_log_file_n('In create_insert_lock_table '||get_time);
5271 end if;
5272 if g_conc_id is null then
5273 g_conc_id:=-1;
5274 end if;
5275 if g_dbms_job_id is null then
5276 g_dbms_job_id:=-1;
5277 end if;
5278 -------------------------------------------------------
5279 l_my_conc_id:=FND_GLOBAL.Conc_request_id;--my conc id
5280 if l_my_conc_id is null or l_my_conc_id<=0 then
5281 l_my_conc_id:=-1;
5282 l_my_job_id:=g_dbms_job_id;
5283 else
5284 l_my_job_id:=-1;
5285 end if;
5286 -------------------------------------------------------
5287 g_stmt:='create table '||g_insert_lock_table||' tablespace '||g_op_table_space||
5288 ' as select nvl('||l_my_conc_id||',-1) conc_id, nvl('||l_my_job_id||',-1) dbms_job_id from dual';
5289 l_stmt:='select conc_id,dbms_job_id from '||g_insert_lock_table;
5290 if g_debug then
5291 write_to_log_file_n(g_stmt);
5292 end if;
5293 loop
5294 begin
5295 execute immediate g_stmt;
5296 if g_debug then
5297 write_to_log_file_n('Created '||g_insert_lock_table||get_time);
5298 end if;
5299 exit;
5300 exception when others then
5301 if sqlcode=-00955 then --already exists
5302 null;
5303 else
5304 return false;
5305 end if;
5306 end;
5307 --see if the processes mentioned are still running
5308 begin
5309 if g_debug then
5310 write_to_log_file_n(l_stmt);
5311 end if;
5312 open cv for l_stmt;
5313 fetch cv into l_conc_id,l_dbms_job_id;
5314 close cv;
5315 if g_debug 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
5325 null;
5326 end if;
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
5336 null;
5337 end if;
5338 else
5339 DBMS_LOCK.SLEEP(g_sleep_time);
5340 end if;
5341 end if;
5342 exception when others then
5343 if sqlcode=-00942 then --object does not exist
5344 null;
5345 end if;
5346 end;
5347 end loop;
5348 return true;
5349 Exception when others then
5350 write_to_log_file_n('Error in create_insert_lock_table '||g_status_message);
5351 return false;
5352 End;
5353
5354 function drop_insert_lock_table return boolean is
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
5364 g_status_message:=sqlerrm;
5365 write_to_log_file_n('Error in drop_insert_lock_table '||g_status_message);
5366 return false;
5367 End;
5368
5369 function pre_fact_load_hook(p_derv_fact varchar2,p_src_fact varchar2) return boolean is
5370 Begin
5371 if g_debug then
5372 write_to_log_file_n('Calling pre_fact_load_hook '||p_derv_fact||' '||p_src_fact||get_time);
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,
5382 'U');
5383 if g_debug then
5384 write_to_log_file_n('Finished pre_fact_load_hook '||get_time);
5385 end if;
5386 return true;
5387 Exception when others then
5388 g_status_message:=sqlerrm;
5389 write_to_log_file_n('Error in pre_fact_load_hook '||g_status_message);
5390 return false;
5391 End;
5392
5393 function post_fact_load_hook(p_derv_fact varchar2,p_src_fact varchar2) return boolean is
5394 Begin
5395 if g_debug then
5396 write_to_log_file_n('Calling post_fact_load_hook '||p_derv_fact||' '||p_src_fact||get_time);
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,
5406 'U');
5407 if g_debug then
5408 write_to_log_file_n('Finished post_fact_load_hook '||get_time);
5409 end if;
5410 return true;
5411 Exception when others then
5412 g_status_message:=sqlerrm;
5413 write_to_log_file_n('Error in post_fact_load_hook '||g_status_message);
5414 return false;
5415 End;
5416
5417 function create_conc_program(
5418 p_temp_conc_name varchar2,
5419 p_temp_conc_short_name varchar2,
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';
5429 l_parameter(1):='p_mode';
5433 l_parameter(3):='p_input_table';
5430 l_parameter_value_set(1):='FND_CHAR240';
5431 l_parameter(2):='p_fact_name';
5432 l_parameter_value_set(2):='FND_CHAR240';
5434 l_parameter_value_set(3):='FND_CHAR240';
5435 l_parameter(4):='p_job_id';
5436 l_parameter_value_set(4):='FND_NUMBER';
5437 l_parameter(5):='p_ilog_low_end';
5438 l_parameter_value_set(5):='FND_NUMBER';
5439 l_parameter(6):='p_ilog_high_end';
5440 l_parameter_value_set(6):='FND_NUMBER';
5441 l_parameter(7):='p_ilog';
5442 l_parameter_value_set(7):='FND_CHAR240';
5443 l_parameter(8):='p_dlog';
5444 l_parameter_value_set(8):='FND_CHAR240';
5445 l_parameter(9):='p_log_file';
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,
5455 p_bis_short_name,
5456 l_parameter,
5457 l_parameter_value_set,
5458 l_number_parameters
5459 )=false then
5460 return false;
5461 end if;
5462 return true;
5463 Exception when others then
5464 g_status_message:=sqlerrm;
5465 write_to_log_file_n('Error in create_conc_program '||g_status_message);
5469
5466 return false;
5467 End;
5468
5470 END;
[Home] [Help]