1 package body EDW_PUSH_DOWN_DIMS AS
2 /*$Header: EDWPSDNB.pls 115.15 2003/11/06 00:56:50 vsurendr ship $*/
3
4 function push_down_all_levels(
5 p_dim_name varchar2,
6 p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
7 p_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType,
8 p_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
9 p_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType,
10 p_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
11 p_number_levels number,
12 p_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
13 p_level_snapshot_logs EDW_OWB_COLLECTION_UTIL.varcharTableType,
14 p_debug boolean,
15 p_parallel number,
16 p_collection_size number,
17 p_bis_owner varchar2,
18 p_table_owner varchar2,
19 p_full_refresh boolean,
20 p_forall_size number,
21 p_update_type varchar2,
22 p_load_pk number,
23 p_op_table_space varchar2,
24 p_dim_push_down out NOCOPY boolean,
25 p_rollback varchar2,
26 p_thread_type varchar2,
27 p_max_threads number,
28 p_min_job_load_size number,
29 p_sleep_time number,
30 p_hash_area_size number,
31 p_sort_area_size number,
32 p_trace boolean,
33 p_read_cfig_options boolean,
34 p_join_nl_percentage number
35 ) return boolean is
36 l_status number;
37 Begin
38 write_to_log_file_n('In push_down_all_levels , dim name is '||p_dim_name);
39 g_dim_name:=p_dim_name;
40 g_levels :=p_levels;
41 g_child_level_number:=p_child_level_number;
42 g_child_levels:=p_child_levels;
43 g_child_fk:=p_child_fk;
44 g_parent_pk:=p_parent_pk;
45 g_number_levels:=p_number_levels;
46 g_level_order:=p_level_order;
47 g_level_snapshot_logs:=p_level_snapshot_logs;
48 g_debug:=p_debug;
49 g_full_refresh:=p_full_refresh;
50 g_parallel :=p_parallel;
51 g_collection_size :=p_collection_size;
52 g_bis_owner:=p_bis_owner;
53 g_table_owner:=p_table_owner;
54 g_forall_size:=g_forall_size;
55 g_update_type:=p_update_type;
56 g_load_pk:=p_load_pk;
57 g_op_table_space:=p_op_table_space;
58 g_rollback:=p_rollback;
59 g_max_threads:=p_max_threads;
60 g_min_job_load_size:=p_min_job_load_size;
61 g_sleep_time:=p_sleep_time;
62 g_hash_area_size:=p_hash_area_size;
63 g_sort_area_size:=p_sort_area_size;
64 g_trace:=p_trace;
65 g_read_cfig_options:=p_read_cfig_options;
66 g_join_nl_percentage:=p_join_nl_percentage;
67 g_job_id:=null;
68 g_jobid_stmt:=null;
69 g_thread_type:=p_thread_type;
70 if g_update_type='DELETE-INSERT' then
71 g_update_type:='MASS';
72 end if;
73 if g_debug then
74 if g_full_refresh then
75 write_to_log_file('FULL Refresh ON');
76 else
77 write_to_log_file('FULL Refresh OFF');
78 end if;
79 end if;
80 if g_debug then
81 write_to_log_file_n('The inputs');
82 write_to_log_file('Levels');
83 for i in 1..g_number_levels loop
84 write_to_log_file(g_levels(i)||'('||g_child_level_number(i)||') '||g_level_snapshot_logs(i));
85 end loop;
86 declare
87 l_run number:=0;
88 begin
89 write_to_log_file('Child Levels');
90 for i in 1..g_number_levels loop
91 for j in 1..g_child_level_number(i) loop
92 l_run:=l_run+1;
93 write_to_log_file(l_run||' '||g_child_levels(l_run)||' '||g_child_fk(l_run)||' '||
94 g_parent_pk(l_run));
95 end loop;
96 end loop;
97 exception when others then
98 write_to_log_file(sqlerrm);
99 end;
100 write_to_log_file('g_max_threads='||g_max_threads);
101 write_to_log_file('g_min_job_load_size='||g_min_job_load_size);
102 write_to_log_file('g_sleep_time='||g_sleep_time);
103 write_to_log_file('g_hash_area_size='||g_hash_area_size);
104 write_to_log_file('g_sort_area_size='||g_sort_area_size);
105 if g_trace then
106 write_to_log_file('TRACE TRUE');
107 else
108 write_to_log_file('TRACE FALSE');
109 end if;
110 if g_read_cfig_options then
111 write_to_log_file('g_read_cfig_options TRUE');
112 else
113 write_to_log_file('g_read_cfig_options FALSE');
114 end if;
115 write_to_log_file('g_join_nl_percentage='||g_join_nl_percentage);
116 write_to_log_file('g_thread_type='||p_thread_type);
117 end if;
118 l_status:=find_ltc_to_push_down;
119 if l_status=0 then
120 write_to_log_file_n('find_ltc_to_push_down returned with error');
121 p_dim_push_down:=false;
122 return false;
123 elsif l_status=1 then
124 write_to_log_file_n('Level Push Down not implemented');
125 p_dim_push_down:=false;
126 return true;
127 end if;
128 p_dim_push_down:=true;
129 if g_number_levels <=1 then
130 write_to_log_file_n('Only one level dimension. No need to push down.');
131 return true;
132 end if;
133 g_dim_id:=EDW_OWB_COLLECTION_UTIL.get_dim_id(g_dim_name);
134 init_all(null);
135 if find_lowest_level =false then
136 write_to_log_file_n('find_lowest_level returned with error');
137 return false;
138 end if;
139 if get_all_level_pk=false then
140 write_to_log_file_n('get_all_level_pk returned with error');
141 return false;
142 end if;
143 if get_level_prefix=false then
144 write_to_log_file_n('get_level_prefix returned with error');
145 return false;
146 end if;
147 if get_level_display_prefix=false then
148 write_to_log_file_n('get_level_display_prefix returned with error');
149 return false;
150 end if;
151 if get_level_seq=false then
152 write_to_log_file_n('get_level_seq returned with error');
153 return false;
154 end if;
155 if get_all_children_main=false then
156 write_to_log_file_n('get_all_children_main returned with error');
157 return false;
158 end if;
159 if find_diamond_levels=false then
160 return false;
161 end if;
162 if check_level_for_column=false then
163 return false;
164 end if;
165 if check_levels_for_data=false then
166 write_to_log_file_n('check_levels_for_data returned with error');
167 return false;
168 end if;
169 if merge_all_ilog_tables=false then
170 write_to_log_file_n('merge_all_ilog_tables returned with error');
171 return false;
172 end if;
173 if create_ilog_tables=false then
174 write_to_log_file_n('create_ilog_tables returned with error');
175 return false;
176 end if;
177 if move_data_into_ilog=false then
178 write_to_log_file_n('move_data_into_ilog returned with error');
179 return false;
180 end if;
181 if push_down_all_levels=false then
182 write_to_log_file_n('push_down_all_levels returned with error');
183 return false;
184 end if;
185 /*if analyze_ltc_tables=false then
186 write_to_log_file_n('analyze_ltc_tables returned with error');
187 return false;
188 end if;*/
189 clean_up;
190 return true;
191 Exception when others then
192 g_status_message:=sqlerrm;
193 g_status:=false;
194 write_to_log_file_n(g_status_message);
195 return false;
196 End;
197
198 function push_down_all_levels return boolean is
199 Begin
200 if g_max_threads>1 then
201 if push_down_all_levels_multi=false then
202 return false;
203 end if;
204 else
205 if push_down_all_levels_single=false then
206 return false;
207 end if;
208 end if;
209 return true;
210 Exception when others then
211 g_status_message:=sqlerrm;
212 g_status:=false;
213 write_to_log_file_n('Error in push_down_all_levels '||g_status_message);
214 return false;
215 End;
216
217 function push_down_all_levels_multi return boolean is
218 l_input_table varchar2(80);
219 l_max_ilog_count number;
220 l_index number;
221 l_ok_low_end EDW_OWB_COLLECTION_UTIL.numberTableType;
222 l_ok_high_end EDW_OWB_COLLECTION_UTIL.numberTableType;
223 l_ok_end_count integer;
224 l_job_id EDW_OWB_COLLECTION_UTIL.numberTableType;
225 l_number_jobs number;
226 l_job_status_table varchar2(80);
227 l_log_file varchar2(1000);
228 -----------------------------------------
229 l_temp_conc_name varchar2(200);
230 l_temp_conc_short_name varchar2(200);
231 l_temp_exe_name varchar2(200);
232 l_bis_short_name varchar2(200);
233 l_try_serial boolean;
234 -----------------------------------------
235 l_errbuf varchar2(2000);
236 l_retcode varchar2(200);
237 -----------------------------------------
238 Begin
239 if g_debug then
240 write_to_log_file_n('In push_down_all_levels_multi '||get_time);
241 end if;
242 for i in 1..g_number_levels loop
243 if g_level_consider(i) and g_levels(i)<>g_lowest_level then
244 if put_rownum_in_ilog_table(i)=false then
245 return false;
246 end if;
247 end if;
248 end loop;
249 l_input_table:=g_bis_owner||'.INP_TAB_'||g_dim_id;
250 l_job_status_table:=g_bis_owner||'.JOB_STATUS_'||g_dim_id;
251 if EDW_OWB_COLLECTION_UTIL.create_input_table_push_down(
252 l_input_table,
253 g_dim_name,
254 g_dim_id,
255 g_levels,
256 g_child_level_number,
257 g_child_levels,
258 g_child_fk,
259 g_parent_pk,
260 g_number_levels,
261 g_level_order,
262 g_level_snapshot_logs,
263 g_level_ilog,
264 g_level_consider,
265 g_level_full_insert,
266 g_debug,
267 g_parallel,
268 g_collection_size,
269 g_bis_owner,
270 g_table_owner,
271 g_full_refresh,
272 g_forall_size,
273 g_update_type,
274 g_load_pk,
275 g_op_table_space,
276 g_rollback,
277 g_max_threads,
278 g_min_job_load_size,
279 g_sleep_time,
280 g_hash_area_size,
281 g_sort_area_size,
282 g_trace,
283 g_read_cfig_options,
284 g_join_nl_percentage
285 )=false then
286 return false;
287 end if;
288 if EDW_OWB_COLLECTION_UTIL.create_job_status_table(l_job_status_table,g_op_table_space)=false then
289 return false;
290 end if;
291 for i in 1..g_number_levels loop
292 g_level_ilog_count(i):=0;
293 if g_level_consider(i) and g_levels(i)<>g_lowest_level then
294 g_level_ilog_count(i):=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_level_ilog(i),
295 g_bis_owner);
296 end if;
297 end loop;
298 l_max_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_max_in_array(g_level_ilog_count,g_number_levels,l_index);
299 if l_index<=0 then
300 return false;
301 end if;
302 /*
303 for the ilog with the largest number of records, find the ok dist
304 */
305 if EDW_OWB_COLLECTION_UTIL.find_ok_distribution(
306 g_level_ilog(l_index),
307 g_bis_owner,
308 g_max_threads,
309 g_min_job_load_size,
310 l_ok_low_end,
311 l_ok_high_end,
312 l_ok_end_count)=false then
313 g_status_message:=EDW_OWB_COLLECTION_UTIL.get_status_message;
314 return false;
315 end if;
316 /*
317 launch the threads
318 */
319 if l_ok_end_count>0 then
320 if g_debug then
321 write_to_log_file_n('Launch multiple threads.');
322 end if;
323 l_number_jobs:=0;
324 l_temp_conc_name:='Sub-Proc Pdn-'||g_dim_id;
325 l_temp_conc_short_name:='CONC_PDN_'||g_dim_id||'_CONC';
326 l_temp_exe_name:=l_temp_conc_name||'_EXE';
327 l_bis_short_name:='BIS';
328 if g_thread_type='CONC' then
329 --create the executable, conc program etc
330 if create_conc_program(l_temp_conc_name,l_temp_conc_short_name,l_temp_exe_name,l_bis_short_name)=false then
331 if g_debug then
332 write_to_log_file_n('Could not create seed data for conc programs. Trying jobs');
333 end if;
334 g_thread_type:='JOB';
335 end if;
336 end if;
337 for i in 1..l_ok_end_count loop
338 l_number_jobs:=l_number_jobs+1;
339 l_job_id(l_number_jobs):=null;
340 l_log_file:='LOG_'||g_dim_id||'_PD';
341 if g_debug then
342 write_to_log_file_n('EDW_PUSH_DOWN_DIMS.PUSH_DOWN_ALL_LEVELS('''||g_dim_name||''','||
343 ''''||l_log_file||''','''||l_input_table||''','||l_number_jobs||','||
344 l_ok_low_end(i)||','||l_ok_high_end(i)||','''||l_job_status_table||''');');
345 end if;
346 begin
347 l_try_serial:=false;
348 if g_thread_type='CONC' then
349 l_job_id(l_number_jobs):=FND_REQUEST.SUBMIT_REQUEST(
350 application=>l_bis_short_name,
351 program=>l_temp_conc_short_name,
352 argument1=>g_dim_name,
353 argument2=>l_log_file,
354 argument3=>l_input_table,
355 argument4=>l_number_jobs,
356 argument5=>l_ok_low_end(i),
357 argument6=>l_ok_high_end(i),
358 argument7=>l_job_status_table);
359 commit;
360 if g_debug then
361 write_to_log_file_n('Concurrent Request '||l_job_id(l_number_jobs)||' launched '||get_time);
362 end if;
363 if l_job_id(l_number_jobs)<=0 then
364 l_try_serial:=true;
365 end if;
366 else
367 DBMS_JOB.SUBMIT(l_job_id(l_number_jobs),'EDW_PUSH_DOWN_DIMS.PUSH_DOWN_ALL_LEVELS('''||g_dim_name||''','||
368 ''''||l_log_file||''','''||l_input_table||''','||l_number_jobs||','||
369 l_ok_low_end(i)||','||l_ok_high_end(i)||','''||l_job_status_table||''');');
370 commit;--this commit is very imp
371 if g_debug then
375 l_try_serial:=true;
372 write_to_log_file_n('Job '||l_job_id(l_number_jobs)||' launched '||get_time);
373 end if;
374 if l_job_id(l_number_jobs)<=0 then
376 end if;
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 EDW_PUSH_DOWN_DIMS.PUSH_DOWN_ALL_LEVELS(
390 l_errbuf,
391 l_retcode,
392 g_dim_name,
393 l_log_file,
394 l_input_table,
395 l_number_jobs,
396 l_ok_low_end(i),
397 l_ok_high_end(i),
398 l_job_status_table
399 );
400 end if;
401 end loop;
402 if EDW_OWB_COLLECTION_UTIL.wait_on_jobs(
403 l_job_id,
404 l_number_jobs,
405 g_sleep_time,
406 g_thread_type)=false then
407 g_status_message:=EDW_OWB_COLLECTION_UTIL.get_status_message;
408 return false;
409 end if;
410 if g_status then
411 if EDW_OWB_COLLECTION_UTIL.check_all_child_jobs(
412 l_job_status_table,
413 l_job_id,
414 l_number_jobs,
415 null)=false then
416 g_status_message:=EDW_OWB_COLLECTION_UTIL.get_status_message;
417 return false;
418 end if;
419 end if;
420 /*if g_thread_type='CONC' then
421 --drop the conc programs
422 if EDW_OWB_COLLECTION_UTIL.delete_conc_program(
423 l_temp_conc_short_name,
424 l_temp_exe_name,
425 l_bis_short_name,
426 'SHORT')=false then
427 null;
428 end if;
429 end if;*/
430 if g_status=false then
431 return false;
432 end if;
433 else
434 --single thread
435 if g_debug then
436 write_to_log_file_n('Launch single thread');
437 end if;
438 if push_down_all_levels_single=false then
439 return false;
440 end if;
441 end if;
442 if drop_input_tables(l_input_table)=false then
443 null;
444 end if;
445 if EDW_OWB_COLLECTION_UTIL.drop_table(l_job_status_table)=false then
446 null;
447 end if;
448 return true;
449 Exception when others then
450 g_status_message:=sqlerrm;
451 g_status:=false;
452 write_to_log_file_n('Error in push_down_all_levels_multi '||g_status_message);
453 return false;
454 End;
455
456 /*
457 entry point for concurrent requests
458 */
459 procedure PUSH_DOWN_ALL_LEVELS(
460 errbuf out nocopy varchar2,
461 retcode out nocopy varchar2,
462 p_dim_name varchar2,
463 p_log_file varchar2,
464 p_input_table varchar2,
465 p_job_id number,
466 p_ok_low_end number,
467 p_ok_high_end number,
468 p_job_status_table varchar2
469 ) is
470 Begin
471 retcode:='0';
472 PUSH_DOWN_ALL_LEVELS(
473 p_dim_name,
474 p_log_file,
475 p_input_table,
476 p_job_id,
477 p_ok_low_end,
478 p_ok_high_end,
479 p_job_status_table);
480 if g_status=false then
481 retcode:='2';
482 errbuf:=g_status_message;
483 end if;
484 Exception when others then
485 errbuf:=sqlerrm;
486 retcode:='2';
487 write_to_log_file_n('Exception in PUSH_DOWN_ALL_LEVELS '||sqlerrm||get_time);
488 End;
489 /*
490 entry point for threads
491 */
492 procedure PUSH_DOWN_ALL_LEVELS(
493 p_dim_name varchar2,
494 p_log_file varchar2,
495 p_input_table varchar2,
496 p_job_id number,
497 p_ok_low_end number,
498 p_ok_high_end number,
499 p_job_status_table varchar2
500 ) is
501 Begin
502 g_dim_name:=p_dim_name;
503 g_log_file:=p_log_file;
504 g_input_table:=p_input_table;
505 g_job_id:=p_job_id;
506 g_jobid_stmt:=' Job '||g_job_id||' ';
507 EDW_OWB_COLLECTION_UTIL.init_all(g_log_file||'_'||g_job_id,null,'bis.edw.loader');
508 if PUSH_DOWN_ALL_LEVELS(p_ok_low_end,p_ok_high_end)=false then
509 if EDW_OWB_COLLECTION_UTIL.log_into_job_status_table(
510 p_job_status_table,
511 g_dim_name,
512 g_job_id,
513 'ERROR',
514 g_status_message)=false then
515 null;
516 end if;
517 else
518 if EDW_OWB_COLLECTION_UTIL.log_into_job_status_table(
519 p_job_status_table,
520 g_dim_name,
521 g_job_id,
522 'SUCCESS',
523 g_status_message)=false then
524 null;
525 end if;
526 end if;
527 Exception when others then
528 g_status_message:=sqlerrm;
529 write_to_log_file_n('Error in PUSH_DOWN_ALL_LEVELS '||g_status_message);
530 End;
531
532 function PUSH_DOWN_ALL_LEVELS(
533 p_ok_low_end number,
534 p_ok_high_end number
535 ) return boolean is
536 Begin
537 if read_options_table(g_input_table)=false then
538 return false;
542 EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
539 end if;
540 EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
541 EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
543 if g_debug then
544 write_to_log_file_n('In PUSH_DOWN_ALL_LEVELS, p_ok_low_end='||p_ok_low_end||',p_ok_high_end='||p_ok_high_end);
545 end if;
546 if set_session_parameters=false then
547 return false;
548 end if; --alter session etc
549 init_all(g_job_id);
550 if find_lowest_level =false then
551 write_to_log_file_n('find_lowest_level returned with error');
552 return false;
553 end if;
554 if get_all_level_pk=false then
555 write_to_log_file_n('get_all_level_pk returned with error');
556 return false;
557 end if;
558 if get_level_prefix=false then
559 write_to_log_file_n('get_level_prefix returned with error');
560 return false;
561 end if;
562 if get_level_display_prefix=false then
563 write_to_log_file_n('get_level_display_prefix returned with error');
564 return false;
565 end if;
566 if get_level_seq=false then
567 write_to_log_file_n('get_level_seq returned with error');
568 return false;
569 end if;
570 if get_all_children_main=false then
571 write_to_log_file_n('get_all_children_main returned with error');
572 return false;
573 end if;
574 if find_diamond_levels=false then
575 return false;
576 end if;
577 if create_ilog_from_main(p_ok_low_end,p_ok_high_end)=false then
578 write_to_log_file_n('create_ilog_from_main returned with error');
579 return false;
580 end if;
581 /*
582 even we are multi thread here, we need to call push_down_all_levels_single
583 */
584 if push_down_all_levels_single=false then
585 write_to_log_file_n('push_down_all_levels_single returned with error');
586 return false;
587 end if;
588 /*if analyze_ltc_tables=false then
589 write_to_log_file_n('analyze_ltc_tables returned with error');
590 return false;
591 end if;*/
592 clean_up;
593 return true;
594 Exception when others then
595 g_status_message:=sqlerrm;
596 write_to_log_file_n('Error in PUSH_DOWN_ALL_LEVELS '||g_status_message);
597 return false;
598 End;
599
600 function get_level_prefix return boolean is
601 l_stmt varchar2(20000);
602 l_in_stmt varchar2(20000);
603 TYPE CurTyp IS REF CURSOR;
604 cv CurTyp;
605 l_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
606 l_prefix EDW_OWB_COLLECTION_UTIL.varcharTableType;
607 l_number_ltc number;
608 Begin
609 if g_debug then
610 write_to_log_file_n('In get_level_prefix');
611 end if;
612 l_in_stmt:=null;
613 for i in 1..g_number_levels loop
614 if i=1 then
615 l_in_stmt:=''''||substr(g_levels(i),1,instr(g_levels(i),'_LTC')-1)||'''';
616 else
617 l_in_stmt:=l_in_stmt||','''||substr(g_levels(i),1,instr(g_levels(i),'_LTC')-1)||'''';
618 end if;
619 end loop;
620 if g_debug then
621 write_to_log_file_n('The in stmt is '||l_in_stmt);
622 end if;
623 l_stmt:='select lvl.level_prefix, lvl.level_name||''_LTC'' from edw_levels_md_v lvl where lvl.level_name '||
624 'in ('||l_in_stmt||')';
625 if g_debug then
626 write_to_log_file_n('Going to execute '||l_stmt);
627 end if;
628 l_number_ltc:=1;
629 open cv for l_stmt;
630 loop
631 fetch cv into l_prefix(l_number_ltc),l_ltc(l_number_ltc);
632 exit when cv%notfound;
633 l_number_ltc:=l_number_ltc+1;
634 end loop;
635 close cv;
636 l_number_ltc:=l_number_ltc-1;
637 for i in 1..g_number_levels loop
638 g_level_prefix(i):=null;
639 end loop;
640 for i in 1..g_number_levels loop
641 for j in 1..l_number_ltc loop
642 if g_levels(i)=l_ltc(j) then
643 g_level_prefix(i):=l_prefix(j);
644 exit;
645 end if;
646 end loop;
647 end loop;
648 if g_debug then
649 write_to_log_file_n('The final list of PREFIX and LTC');
650 for i in 1..g_number_levels loop
651 write_to_log_file(g_level_prefix(i)||' '||g_levels(i));
652 end loop;
653 end if;
654 return true;
655 Exception when others then
656 g_status_message:=sqlerrm;
657 g_status:=false;
658 write_to_log_file_n(g_status_message);
659 return false;
660 End;
661
662 function get_level_display_prefix return boolean is
663 l_stmt varchar2(20000);
664 l_in_stmt varchar2(20000);
665 TYPE CurTyp IS REF CURSOR;
666 cv CurTyp;
667 l_meaning EDW_OWB_COLLECTION_UTIL.varcharTableType;
668 l_code EDW_OWB_COLLECTION_UTIL.varcharTableType;
669 l_number_ltc number;
670 Begin
671 if g_debug then
672 write_to_log_file_n('In get_level_display_prefix');
673 end if;
674 for i in 1..g_number_levels loop
675 g_level_display_prefix(i):=null;
676 end loop;
677 l_in_stmt:=null;
678 for i in 1..g_number_levels loop
679 if i=1 then
680 l_in_stmt:=l_in_stmt||''''||g_dim_name||'_'||g_level_prefix(i)||'''';
681 else
682 l_in_stmt:=l_in_stmt||','''||g_dim_name||'_'||g_level_prefix(i)||'''';
683 end if;
684 end loop;
685 if g_debug then
689 ' and lookup_code in ('||l_in_stmt||')';
686 write_to_log_file_n('The in stmt is '||l_in_stmt);
687 end if;
688 l_stmt:='select lookup_code,meaning from FND_LOOKUP_VALUES_VL where lookup_type=''EDW_LEVEL_PUSH_DOWN'' '||
690 if g_debug then
691 write_to_log_file_n('Going to execute '||l_stmt);
692 end if;
693 l_number_ltc:=1;
694 open cv for l_stmt;
695 loop
696 fetch cv into l_code(l_number_ltc),l_meaning(l_number_ltc);
697 exit when cv%notfound;
698 l_number_ltc:=l_number_ltc+1;
699 end loop;
700 close cv;
701 l_number_ltc:=l_number_ltc-1;
702
703 for i in 1..g_number_levels loop
704 for j in 1..l_number_ltc loop
705 if g_dim_name||'_'||g_level_prefix(i)=l_code(j) then
706 g_level_display_prefix(i):=l_meaning(j);
707 exit;
708 end if;
709 end loop;
710 end loop;
711 if g_debug then
712 write_to_log_file_n('The final list of levels and meaning');
713 for i in 1..g_number_levels loop
714 write_to_log_file(g_levels(i)||' '||g_level_display_prefix(i));
715 end loop;
716 end if;
717 return true;
718 Exception when others then
719 g_status_message:=sqlerrm;
720 g_status:=false;
721 write_to_log_file_n(g_status_message);
722 return false;
723 End;
724
725 function get_all_children_main return boolean is
726 Begin
727 if g_debug then
728 write_to_log_file_n('In get_all_children_main');
729 end if;
730 for i in 1..g_number_levels loop
731 if get_all_children(i)=false then
732 write_to_log_file_n('get_all_children returned with error for '||g_levels(i));
733 return false;
734 end if;
735 end loop;
736 if g_debug then
737 write_to_log_file_n('The Final result');
738 for i in 1..g_number_final loop
739 write_to_log_file(g_final_levels(i)||' '||g_final_child_levels(i)||' '||g_final_next_parent(i)||' '||
740 g_final_fk(i)||' '||g_final_next_pk(i)||' '||g_final_pk_value(i)||' '||g_final_pk_prefix(i));
741 end loop;
742 end if;
743 return true;
744 Exception when others then
745 g_status_message:=sqlerrm;
746 g_status:=false;
747 write_to_log_file_n(g_status_message);
748 return false;
749 End;
750
751 function get_all_children(p_index number) return boolean is
752 l_prefix varchar2(400);
753 l_pk varchar2(400);
754 Begin
755 if g_debug then
756 write_to_log_file_n('In get_all_children');
757 write_to_log_file('p_index='||p_index);
758 write_to_log_file('The level='||g_levels(p_index));
759 end if;
760 if g_child_level_number(p_index) > 0 then
761 l_prefix:=g_level_prefix(p_index);
762 l_pk:=g_level_pk(p_index);
763 if get_all_children_rec(p_index,g_levels(p_index),l_prefix,l_pk)=false then
764 write_to_log_file_n('get_all_children_rec returned with error for '||g_levels(p_index));
765 return false;
766 end if;
767 end if;
768 return true;
769 Exception when others then
770 g_status_message:=sqlerrm;
771 g_status:=false;
772 write_to_log_file_n(g_status_message);
773 return false;
774 End;
775
776 function get_all_children_rec(p_index number, p_level varchar2,
777 p_prefix varchar2,p_pk varchar2) return boolean is
778 l_child_count number:=0;
779 l_index number;
780 l_found boolean;
781 Begin
782 /*
783 if g_debug then
784 write_to_log_file_n('In get_all_children_rec');
785 write_to_log_file('p_index='||p_index);
786 write_to_log_file('p_level='||p_level);
787 write_to_log_file('p_prefix='||p_prefix);
788 write_to_log_file('p_pk='||p_pk);
789 end if;*/
790 /*--REMOVE---------------------------------
791 g_count:=g_count+1;
792 if g_count > 200 then
793 return false;
794 end if;
795 -----------------------------------------*/
796 if p_index > 1 then
797 for i in 1..(p_index-1) loop
798 l_child_count:=l_child_count+g_child_level_number(i);
799 end loop;
800 end if;
801 if g_debug then
802 write_to_log_file_n('l_child_count='||l_child_count);
803 end if;
804 for i in 1..g_child_level_number(p_index) loop
805 --if level_child_found(p_level,g_child_levels(l_child_count+i)) = true then
806 --return true;
807 --end if;
808 l_found:=false;
809 for j in 1..g_number_final loop
810 if g_final_levels(j)=p_level and g_final_child_levels(j)=g_child_levels(l_child_count+i)
811 and g_final_fk(j)=g_child_fk(l_child_count+i) and g_final_next_pk(j)=g_parent_pk(l_child_count+i) then
812 l_found:=true;
813 exit;
814 end if;
815 end loop;
816 if l_found=false then
817 g_number_final:=g_number_final+1;
818 g_final_levels(g_number_final):=p_level;--main parent
819 g_final_child_levels(g_number_final):=g_child_levels(l_child_count+i);
820 g_final_next_parent(g_number_final):=g_levels(p_index);--immediate parent
821 g_final_fk(g_number_final):=g_child_fk(l_child_count+i);
822 g_final_next_pk(g_number_final):=g_parent_pk(l_child_count+i);
823 g_final_pk_value(g_number_final):=p_pk;
824 g_final_pk_prefix(g_number_final):=p_prefix; --only if FNP=FL this is needed
828 g_final_fk_prefix(g_number_final):=p_prefix;
825 if g_levels(p_index) = p_level then
826 g_final_fk_prefix(g_number_final):=null;
827 else
829 end if;
830 end if;
831 end loop;
832 for i in 1..g_child_level_number(p_index) loop
833 l_index:=get_level_index(g_child_levels(l_child_count+i));
834 if g_child_level_number(l_index) > 0 then
835 if get_all_children_rec(l_index,p_level,p_prefix,p_pk)=false then
836 write_to_log_file_n('get_all_children_rec returned with false for '||l_index||' '||
837 p_level||' '||p_prefix);
838 return false;
839 end if;
840 end if;
841 end loop;
842 return true;
843 Exception when others then
844 g_status_message:=sqlerrm;
845 g_status:=false;
846 write_to_log_file_n(g_status_message);
847 return false;
848 End;
849
850 function level_child_found(p_level varchar2, p_child_level varchar2) return boolean is
851 Begin
852 /*
853 if g_debug then
854 write_to_log_file_n('In level_child_found');
855 write_to_log_file('p_level='||p_level);
856 write_to_log_file('p_child_level='||p_child_level);
857 end if;*/
858 for i in 1..g_number_final loop
859 if g_final_levels(i)=p_level and g_final_child_levels(i)=p_child_level then
860 return true;
861 end if;
862 end loop;
863 return false;
864 Exception when others then
865 g_status_message:=sqlerrm;
866 g_status:=false;
867 write_to_log_file_n(g_status_message);
868 return false;
869 End;
870
871 function get_level_index(p_level varchar2) return number is
872 Begin
873 if g_debug then
874 write_to_log_file_n('In get_level_index, p_level='||p_level);
875 end if;
876 for i in 1..g_number_levels loop
877 if g_levels(i)=p_level then
878 return i;
879 end if;
880 end loop;
881 return null;
882 Exception when others then
883 g_status_message:=sqlerrm;
884 g_status:=false;
885 write_to_log_file_n(g_status_message);
886 return null;
887 End;
888
889 function get_all_level_pk return boolean is
890 Begin
891 if g_debug then
892 write_to_log_file_n('In get_all_level_pk');
893 end if;
894 for i in 1..g_number_levels loop
895 if g_levels(i) = g_lowest_level then
896 if EDW_OWB_COLLECTION_UTIL.get_table_surr_pk(g_lowest_level,g_level_pk(i))=false then
897 write_to_log_file_n('EDW_OWB_COLLECTION_UTIL.get_table_surr_pk returned with error');
898 return false;
899 end if;
900 else
901 g_level_pk(i):=get_ltc_pk(g_levels(i));
902 end if;
903 end loop;
904 return true;
905 Exception when others then
906 g_status_message:=sqlerrm;
907 g_status:=false;
908 write_to_log_file_n(g_status_message);
909 return false;
910 End;
911
912 function get_ltc_pk(p_level varchar2) return varchar2 is
913 Begin
914 if g_debug then
915 write_to_log_file_n('In get_ltc_pk, p_level='||p_level);
916 end if;
917 return get_ltc_pk(get_level_index(p_level));
918 Exception when others then
919 g_status_message:=sqlerrm;
920 g_status:=false;
921 write_to_log_file_n(g_status_message);
922 return null;
923 End;
924
925
926 function get_ltc_pk(p_index number) return varchar2 is
927 l_child_count number:=0;
928 Begin
929 if g_debug then
930 write_to_log_file_n('In get_ltc_pk, p_index='||p_index);
931 end if;
932 if p_index > 1 then
933 for i in 1..(p_index-1) loop
934 l_child_count:=l_child_count+g_child_level_number(i);
935 end loop;
936 end if;
937 if g_debug then
938 write_to_log_file_n('l_child_count+1='||(l_child_count+1));
939 end if;
940 return g_parent_pk(l_child_count+1);
941 Exception when others then
942 g_status_message:=sqlerrm;
943 g_status:=false;
944 write_to_log_file_n(g_status_message);
945 return null;
946 End;
947
948 function push_down_all_levels_single return boolean is
949 Begin
950 if g_debug then
951 write_to_log_file_n('In push_down_all_levels_single');
952 end if;
953 for i in 1..(g_number_levels-1) loop --no need to push down the lowest level
954 if g_level_consider(get_index_for_level(g_level_order(i))) then
955 g_level_id:=EDW_OWB_COLLECTION_UTIL.get_object_id(g_level_order(i));
956 if g_level_id=-1 then
957 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
958 return false;
959 end if;
960 insert_into_load_progress_d(g_load_pk,g_level_order(i),'Push Down Level '||g_jobid_stmt,sysdate,null,'LEVEL',
961 'LEVEL-PUSH-DOWN','LPD'||i||g_jobid_stmt,'I');
962 if push_down_level(g_level_order(i))=false then
963 write_to_log_file_n('push_down_level returned with error for '||g_level_order(i));
964 insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,'LPD'||i||g_jobid_stmt,'U');
965 return false;
966 end if;
967 insert_into_load_progress_d(g_load_pk,null,null,null,sysdate,null,null,'LPD'||i||g_jobid_stmt,'U');
968 else
969 if g_debug then
970 write_to_log_file_n('Level '||g_level_order(i)||' has no incremental data');
974 return true;
971 end if;
972 end if;
973 end loop;
975 Exception when others then
976 g_status_message:=sqlerrm;
977 g_status:=false;
978 write_to_log_file_n('Error in push_down_all_levels_single '||g_status_message);
979 return false;
980 End;
981
982 function push_down_level(p_level varchar2) return boolean is
983 l_status number;
984 l_index number;
985 Begin
986 if g_debug then
987 write_to_log_file_n('In push_down_level');
988 write_to_log_file('The level to push down '||p_level);
989 end if;
990 l_index:=get_index_for_level(p_level);
991 if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_level_ilog(l_index))=2 then
992 if make_sql_stmts(p_level)=false then --also executes them
993 write_to_log_file_n('make_sql_stmts returned with error');
994 return false;
995 end if;
996 else
997 if g_debug then
998 write_to_log_file_n('ILOG '||g_level_ilog(l_index)||' has no data '||get_time);
999 end if;
1000 end if;
1001 return true;
1002 Exception when others then
1003 g_status_message:=sqlerrm;
1004 g_status:=false;
1005 write_to_log_file_n(g_status_message);
1006 return false;
1007 End;
1008
1009 function execute_update_stmt(p_update_stmt varchar2,p_update_stmt_row varchar2,p_update_rowid_table varchar2)
1010 return boolean is
1011 l_stmt varchar2(5000);
1012 TYPE CurTyp IS REF CURSOR;
1013 cv CurTyp;
1014 l_rowid EDW_OWB_COLLECTION_UTIL.rowidTableType;
1015 l_count number;
1016 l_total_count number:=0;
1017 l_update_type varchar2(400);
1018 Begin
1019 if g_debug then
1020 write_to_log_file_n('In execute_update_stmt '||get_time);
1021 end if;
1022 l_update_type:=g_update_type;
1023 <<start_update>>
1024 if l_update_type='ROW-BY-ROW' then
1025 l_stmt:='select row_id from '||p_update_rowid_table;
1026 if g_debug then
1027 write_to_log_file_n('Goint to execute '||l_stmt);
1028 end if;
1029 l_count:=1;
1030 open cv for l_stmt;
1031 loop
1032 fetch cv into l_rowid(l_count);
1033 exit when cv%notfound;
1034 if l_count>=g_forall_size then
1035 for i in 1..l_count loop
1036 execute immediate p_update_stmt_row using l_rowid(i),l_rowid(i);
1037 end loop;
1038 l_total_count:=l_total_count+l_count;
1039 l_count:=1;
1040 else
1041 l_count:=l_count+1;
1042 end if;
1043 end loop;
1044 close cv;
1045 l_count:=l_count-1;
1046 if l_count>0 then
1047 for i in 1..l_count loop
1048 execute immediate p_update_stmt_row using l_rowid(i),l_rowid(i);
1049 end loop;
1050 l_total_count:=l_total_count+l_count;
1051 end if;
1052 elsif l_update_type='MASS' then
1053 EDW_OWB_COLLECTION_UTIL.alter_session('NO-PARALLEL');
1054 begin
1055 EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1056 execute immediate p_update_stmt;
1057 l_total_count:=sql%rowcount;
1058 if g_parallel is not null then
1059 EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
1060 end if;
1061 exception when others then
1062 if sqlcode=-4030 then
1063 commit;
1064 write_to_log_file_n('Memory issue with Mass Update. Retrying using ROW_BY_ROW');
1065 l_update_type:='ROW-BY-ROW';
1066 goto start_update;
1067 end if;
1068 g_status_message:=sqlerrm;
1069 write_to_log_file_n(g_status_message);
1070 write_to_log_file('Problem stmt '||p_update_stmt);
1071 if g_parallel is not null then
1072 EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
1073 end if;
1074 return false;
1075 end ;
1076 end if;
1077 commit;
1078 if g_debug then
1079 write_to_log_file_n('Number of rows updated '||l_total_count||get_time);
1080 end if;
1081 return true;
1082 Exception when others then
1083 g_status_message:=sqlerrm;
1084 g_status:=false;
1085 write_to_log_file_n(g_status_message);
1086 return false;
1087 End;
1088
1089 function make_sql_stmts(p_level varchar2) return boolean is
1090 l_child_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
1091 l_number_child_level number;
1092 l_parent_index number;
1093 l_level_ilog varchar2(200);
1094 l_status number;
1095 Begin
1096 if g_debug then
1097 write_to_log_file_n('In make_sql_stmts(p_level)');
1098 write_to_log_file('P_level is '||p_level);
1099 end if;
1100 l_parent_index:=get_index_for_level(p_level);
1101 loop
1102 if g_skip_ilog_update(l_parent_index)=false then
1103 l_status:=set_gilog_status(g_level_ilog(l_parent_index),l_parent_index);
1104 else
1105 l_status:=2;
1106 g_skip_ilog_update(l_parent_index):=false;
1107 end if;
1108 if l_status=0 then --error
1109 write_to_log_file_n('ERROR set_gilog_status returned with status 0');
1110 g_status:=false;
1111 return false;
1112 elsif l_status=1 then
1113 exit;
1114 else
1115 l_level_ilog:=g_level_ilog(l_parent_index)||'T';
1116 if create_ilog_copy(g_level_ilog(l_parent_index),l_level_ilog)=false then
1117 return false;
1118 end if;
1119 l_number_child_level:=0;
1120 for i in 1..g_number_levels loop
1121 if g_level_order(i)<>p_level then
1125 l_number_child_level:=l_number_child_level+1;
1122 for j in 1..g_number_final loop
1123 if g_final_levels(j)=p_level and g_final_child_levels(j)=g_level_order(i) then
1124 if EDW_OWB_COLLECTION_UTIL.value_in_table(l_child_level,l_number_child_level,g_level_order(i))=false then
1126 l_child_level(l_number_child_level):=g_level_order(i);
1127 if make_and_exec_sql_stmts(p_level,g_level_order(i),l_level_ilog)=false then
1128 return false;
1129 end if;
1130 else
1131 if g_debug then
1132 write_to_log_file_n('Level '||p_level||' already pushed down to '||g_level_order(i));
1133 end if;
1134 end if;
1135 end if;
1136 end loop;
1137 end if;
1138 end loop;
1139 if g_type_ilog_generation='UPDATE' then
1140 if update_gilog(g_level_ilog(l_parent_index))=false then
1141 write_to_log_file_n('Error in update_gilog');
1142 return false;
1143 end if;
1144 end if;
1145 if EDW_OWB_COLLECTION_UTIL.drop_table(l_level_ilog)=false then
1146 null;
1147 end if;
1148 end if;
1149 end loop;
1150 return true;
1151 Exception when others then
1152 g_status_message:=sqlerrm;
1153 g_status:=false;
1154 write_to_log_file_n(g_status_message);
1155 return false;
1156 End;
1157
1158 function make_and_exec_sql_stmts(p_parent_level varchar2,p_child_level varchar2,p_ilog varchar2) return boolean is
1159 l_parent_index number;
1160 l_child_index number;
1161 l_number_final number;
1162 l_final EDW_OWB_COLLECTION_UTIL.numberTableType;
1163 l_level_copy EDW_OWB_COLLECTION_UTIL.varcharTableType;
1164 l_number_level_copy number;
1165 l_level_copy_index EDW_OWB_COLLECTION_UTIL.numberTableType;
1166 l_final_index EDW_OWB_COLLECTION_UTIL.numberTableType;
1167 l_index number;
1168 l_stmt varchar2(30000);
1169 l_table varchar2(200);
1170 l_pk varchar2(200);
1171 l_user_pk varchar2(200);
1172 l_parent_pk varchar2(200);
1173 l_parent_user_pk varchar2(200);
1174 l_opcode_table varchar2(200);
1175 l_update_stmt varchar2(32000);
1176 l_update_stmt_row varchar2(32000);
1177 l_insert_stmt varchar2(32000);
1178 l_other_fks EDW_OWB_COLLECTION_UTIL.varcharTableType;
1179 l_number_other_fks number;
1180 l_other_fks_hold EDW_OWB_COLLECTION_UTIL.varcharTableType;
1181 l_number_other_fks_hold number;
1182 l_found boolean;
1183 l_diamond_fk_table varchar2(400):=null;
1184 l_below_diamond_flag EDW_OWB_COLLECTION_UTIL.booleanTableType;
1185 l_parent_level_count number;
1186 l_ilog_count number;
1187 l_use_nl boolean;
1188 Begin
1189 if g_debug then
1190 write_to_log_file_n('In make_and_exec_sql_stmts parent='||p_parent_level||',child='||p_child_level||
1191 ',p_ilog='||p_ilog);
1192 end if;
1193 l_parent_index:=get_index_for_level(p_parent_level);
1194 l_child_index:=get_index_for_level(p_child_level);
1195 l_number_final:=0;
1196 for i in 1..g_number_final loop
1197 if g_final_levels(i)=p_parent_level and g_final_child_levels(i)=p_child_level then
1198 l_number_final:=l_number_final+1;
1199 l_final(l_number_final):=i;
1200 end if;
1201 end loop;
1202 if g_debug then
1203 write_to_log_file_n('The indexes ');
1204 for i in 1..l_number_final loop
1205 write_to_log_file(l_final(i));
1206 end loop;
1207 end if;
1208 l_number_level_copy:=0;--for all the levels that are not parent or child create copies
1209 for i in 1..l_number_final loop
1210 l_index:=l_final(i);
1211 if g_final_next_parent(l_index)<>p_parent_level then
1212 if EDW_OWB_COLLECTION_UTIL.value_in_table(l_level_copy,l_number_level_copy,g_final_next_parent(l_index))=false then
1213 l_number_level_copy:=l_number_level_copy+1;
1214 l_level_copy(l_number_level_copy):=g_final_next_parent(l_index);
1215 l_level_copy_index(l_number_level_copy):=get_index_for_level(g_final_next_parent(l_index));
1216 l_final_index(l_number_level_copy):=l_final(i);
1217 end if;
1218 end if;
1219 end loop;
1220 if g_debug then
1221 write_to_log_file_n('The other levels for whom copies are going to be made ');
1222 for i in 1..l_number_level_copy loop
1223 write_to_log_file(l_level_copy(i)||'('||l_level_copy_index(i)||')');
1224 end loop;
1225 end if;
1226 --make the level copies
1227 if l_number_level_copy>0 then
1228 for i in 1..l_number_level_copy loop
1229 l_index:=l_final_index(i);
1230 l_stmt:='create table '||g_ltc_copy(l_level_copy_index(i))||' tablespace '||g_op_table_space;
1231 if g_parallel is not null then
1232 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
1233 end if;
1234 if l_parent_level_count is null then
1235 l_parent_level_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(p_parent_level,g_table_owner);
1236 end if;
1237 if l_ilog_count is null then
1238 l_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(p_ilog,g_bis_owner);
1239 end if;
1240 l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_ilog_count,l_parent_level_count,g_join_nl_percentage);
1241 l_stmt:=l_stmt||' as select /*+ordered ';
1242 if l_use_nl then
1243 l_stmt:=l_stmt||'use_nl(A)';
1244 end if;
1248 end if;
1245 l_stmt:=l_stmt||'*/ ';
1246 if g_parallel is not null then
1247 l_stmt:=l_stmt||' /*+parallel(A,'||g_parallel||') (B,'||g_parallel||')*/ ';
1249 l_stmt:=l_stmt||'B.'||g_final_next_pk(l_index)||',B.'||get_user_key(g_final_next_pk(l_index))||
1250 ',A.rowid row_id from '||p_ilog||','||p_parent_level||' A,'||l_level_copy(i)||' B where '||
1251 'A.'||get_user_key(g_final_pk_value(l_index))||'||''-'||g_final_pk_prefix(l_index)||'''=B.'||
1252 get_user_key(g_final_next_pk(l_index))||' and '||p_ilog||'.row_id=A.rowid';
1253 if EDW_OWB_COLLECTION_UTIL.drop_table(g_ltc_copy(l_level_copy_index(i)))=false then
1254 null;
1255 end if;
1256 if g_debug then
1257 write_to_log_file_n('Going to execute '||l_stmt||get_time);
1258 end if;
1259 execute immediate l_stmt;
1260 if g_debug then
1261 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
1262 end if;
1263 l_table:=g_ltc_copy(l_level_copy_index(i));
1264 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_table,instr(l_table,'.')+1,
1265 length(l_table)),substr(l_table,1,instr(l_table,'.')-1));
1266 end loop;
1267 else
1268 write_to_log_file_n('There are no other levels to make copies of');
1269 end if;
1270 l_number_other_fks:=0;
1271 l_number_other_fks_hold:=0;
1272 --if get_fks_without_fk(p_child_level,null,l_other_fks_hold,l_number_other_fks_hold)=false then
1273 --return false;
1274 --end if;
1275 l_number_other_fks_hold:=0;
1276 if EDW_OWB_COLLECTION_UTIL.get_fks_for_table(p_child_level,l_other_fks_hold,l_number_other_fks_hold)=false then
1277 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
1278 g_status:=false;
1279 return false;
1280 end if;
1281 if g_debug then
1282 write_to_log_file_n('ALL the fks');
1283 for i in 1..l_number_other_fks_hold loop
1284 write_to_log_file(l_other_fks_hold(i));
1285 end loop;
1286 end if;
1287 for i in 1..l_number_other_fks_hold loop
1288 l_found:=false;
1289 for j in 1..l_number_final loop
1290 if l_other_fks_hold(i)=g_final_fk(l_final(j)) then
1291 l_found:=true;
1292 exit;
1293 end if;
1294 end loop;
1295 if l_found=false then
1296 l_number_other_fks:=l_number_other_fks+1;
1297 l_other_fks(l_number_other_fks):=l_other_fks_hold(i);
1298 end if;
1299 end loop;
1300 for i in 1..l_number_other_fks loop
1301 l_below_diamond_flag(i):=false;
1302 end loop;
1303 if g_debug then
1304 write_to_log_file_n('ALL the OTHER fks');
1305 for i in 1..l_number_other_fks loop
1306 write_to_log_file(l_other_fks(i));
1307 end loop;
1308 end if;
1309 /*
1310 find out diamond keys and create tables to take care of the issue
1311 */
1312 if g_number_diamond_level>0 then
1313 declare
1314 l_diamond_tops EDW_OWB_COLLECTION_UTIL.varcharTableType;
1315 l_number_diamond_tops number:=0;
1316 l_other_fk_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
1317 l_parent_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1318 l_child_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1319 l_parent_pk_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1320 l_child_fk_order EDW_OWB_COLLECTION_UTIL.varcharTableType;
1321 l_number_level_order number;
1322 begin
1323 --see if the parent is in a hier that belongs to diamond
1324 if get_diamond_tops(p_parent_level,l_diamond_tops,l_number_diamond_tops)=false then
1325 return false;
1326 end if;
1327 if l_number_diamond_tops>0 then
1328 --check all the other fks to get the other levels
1329 for i in 1..l_number_other_fks loop
1330 l_other_fk_level(i):=get_level_for_fk(p_child_level,l_other_fks(i));
1331 if l_other_fk_level(i) is null then
1332 return false;
1333 end if;
1334 end loop;
1335 if g_debug then
1336 write_to_log_file_n('The fks and levels');
1337 for i in 1..l_number_other_fks loop
1338 write_to_log_file(l_other_fks(i)||'('||l_other_fk_level(i)||')');
1339 end loop;
1340 end if;
1341 --see if these levels are below the diamond tops
1342 for i in 1..l_number_diamond_tops loop
1343 for j in 1..l_number_other_fks loop
1344 if is_below_diamond_top(l_other_fk_level(j),l_diamond_tops(i)) then
1345 l_below_diamond_flag(j):=true;
1346 end if;
1347 end loop;
1348 end loop;
1349 if g_debug then
1350 write_to_log_file_n('The levels below and not below diamonds');
1351 for i in 1..l_number_other_fks loop
1352 if l_below_diamond_flag(i) then
1353 write_to_log_file(l_other_fk_level(i)||' below diamond');
1354 else
1355 write_to_log_file(l_other_fk_level(i)||' NOT below diamond');
1356 end if;
1357 end loop;
1358 end if;
1359 l_found:=false;
1360 for i in 1..l_number_other_fks loop
1361 if l_below_diamond_flag(i) then
1362 l_found:=true;
1363 exit;
1364 end if;
1365 end loop;
1369 l_parent_pk_order,l_child_fk_order,l_number_level_order)=false then
1366 if l_found then
1367 --from the parent,find the way to the child
1368 if get_way_to_child(p_parent_level,p_child_level,l_parent_level_order,l_child_level_order,
1370 return false;
1371 end if;
1372 if create_child_dia_fk_table(p_parent_level,p_child_level,l_parent_level_order,l_child_level_order,
1373 l_parent_pk_order,l_child_fk_order,l_number_level_order,p_ilog,l_other_fks,l_below_diamond_flag,
1374 l_number_other_fks,l_diamond_fk_table)=false then
1375 return false;
1376 end if;
1377 else
1378 if g_debug then
1379 write_to_log_file_n('There are no other levels that belong to a diamond top shared by '||p_parent_level);
1380 end if;
1381 end if;
1382 else
1383 if g_debug then
1384 write_to_log_file_n('This level '||p_parent_level||' is not a child of any diamond top');
1385 end if;
1386 end if;
1387 exception when others then
1388 g_status_message:=sqlerrm;
1389 g_status:=false;
1390 write_to_log_file_n(g_status_message);
1391 return false;
1392 end;
1393 end if;
1394 l_pk:=g_level_pk(l_child_index);
1395 l_user_pk:=get_user_key(l_pk);
1396 l_parent_pk:=g_level_pk(l_parent_index);
1397 l_parent_user_pk:=get_user_key(l_parent_pk);
1398 if g_level_full_insert(l_child_index) then
1399 --only insert
1400 if g_debug then
1401 write_to_log_file_n('Full Insert');
1402 end if;
1403 l_stmt:='insert into '||p_child_level||'('||l_user_pk||','||l_pk;
1404 for i in 1..l_number_final loop
1405 l_stmt:=l_stmt||','||g_final_fk(l_final(i));
1406 end loop;
1407 for i in 1..l_number_other_fks loop
1408 l_stmt:=l_stmt||','||l_other_fks(i);
1409 end loop;
1410 l_stmt:=l_stmt||',NAME,CREATION_DATE,LAST_UPDATE_DATE,PUSHED_DOWN_FLAG) select ';
1411 if g_parallel is not null then
1412 l_stmt:=l_stmt||' /*+parallel(A,'||g_parallel||')*/ ';
1413 end if;
1414 l_stmt:=l_stmt||'A.'||l_parent_user_pk||'||''-'||g_level_prefix(l_parent_index)||''''||
1415 ','||g_level_seq(l_child_index)||'.NEXTVAL';
1416 for i in 1..l_number_final loop
1417 if g_final_next_parent(l_final(i))=p_parent_level then
1418 l_stmt:=l_stmt||',A.'||g_final_pk_value(l_final(i));
1419 else
1420 l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(l_level_copy,l_number_level_copy,
1421 g_final_next_parent(l_final(i)));
1422 l_stmt:=l_stmt||','||g_ltc_copy(l_level_copy_index(l_index))||'.'||g_final_next_pk(l_final(i));
1423 end if;
1424 end loop;
1425 for i in 1..l_number_other_fks loop
1426 if l_below_diamond_flag(i) then
1427 l_stmt:=l_stmt||','||l_diamond_fk_table||'.'||l_other_fks(i);
1428 else
1429 l_stmt:=l_stmt||',0';--NA_EDW
1430 end if;
1431 end loop;
1432 l_stmt:=l_stmt||','''||g_level_display_prefix(l_child_index)||'(''||A.NAME||'')'',SYSDATE,SYSDATE,''Y'' from '||
1433 p_parent_level||' A ';
1434 for i in 1..l_number_level_copy loop
1435 l_stmt:=l_stmt||','||g_ltc_copy(l_level_copy_index(i));
1436 end loop;
1437 if l_diamond_fk_table is not null then
1438 l_stmt:=l_stmt||','||l_diamond_fk_table;
1439 end if;
1440 l_stmt:=l_stmt||','||p_ilog||' where '||p_ilog||'.row_id=A.rowid and ';
1441 for i in 1..l_number_level_copy loop
1442 l_stmt:=l_stmt||'A.rowid='||g_ltc_copy(l_level_copy_index(i))||'.row_id and ';
1443 end loop;
1444 if l_diamond_fk_table is not null then
1445 l_stmt:=l_stmt||'A.rowid='||l_diamond_fk_table||'.row_id and ';
1446 end if;
1447 l_stmt:=substr(l_stmt,1,length(l_stmt)-4);
1448 if g_debug then
1449 write_to_log_file_n('Going to execute '||l_stmt||get_time);
1450 end if;
1451 EDW_OWB_COLLECTION_UTIL.alter_session('NO-PARALLEL');
1452 EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1453 execute immediate l_stmt;
1454 if g_debug then
1455 write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
1456 end if;
1457 commit;
1458 if g_parallel is not null then
1459 EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
1460 end if;
1461 else
1462 --insert and update most of the time here
1463 l_opcode_table:=g_update_rowid(l_child_index)||'O';
1464 l_stmt:='create table '||l_opcode_table||' tablespace '||g_op_table_space;
1465 if g_parallel is not null then
1466 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
1467 end if;
1468 l_stmt:=l_stmt||' as select ';
1469 if g_parallel is not null then
1470 l_stmt:=l_stmt||'/*+parallel(A,'||g_parallel||') (B,'||g_parallel||')*/ ';
1471 end if;
1472 l_stmt:=l_stmt||'A.rowid row_id,B.rowid row_id1,decode(B.rowid,null,0,1) status from '||
1473 p_ilog||','||p_parent_level||' A,'||p_child_level||' B where '||p_ilog||'.row_id=A.rowid '||
1474 'and A.'||l_parent_user_pk||'||''-'||g_level_prefix(l_parent_index)||'''=B.'||l_user_pk||'(+)';
1475 if EDW_OWB_COLLECTION_UTIL.drop_table(l_opcode_table)=false then
1476 null;
1477 end if;
1478 if g_debug then
1479 write_to_log_file_n('Going to execute '||l_stmt||get_time);
1480 end if;
1484 end if;
1481 execute immediate l_stmt;
1482 if g_debug then
1483 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
1485 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_opcode_table,instr(l_opcode_table,'.')+1,
1486 length(l_opcode_table)),substr(l_opcode_table,1,instr(l_opcode_table,'.')-1));
1487 if EDW_OWB_COLLECTION_UTIL.does_table_have_data(l_opcode_table,'status=1')=2 then
1488 if g_debug then
1489 write_to_log_file_n('Update needed for child level');
1490 end if;
1491 l_stmt:='create table '||g_update_rowid(l_child_index)||' tablespace '||g_op_table_space;
1492 if g_parallel is not null then
1493 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
1494 end if;
1495 l_stmt:=l_stmt||' as select ';
1496 if g_parallel is not null then
1497 l_stmt:=l_stmt||' /*+parallel(A,'||g_parallel||')*/ ';
1498 end if;
1499 l_stmt:=l_stmt||l_opcode_table||'.row_id1 row_id ';
1500 for i in 1..l_number_final loop
1501 if g_final_next_parent(l_final(i))=p_parent_level then
1502 l_stmt:=l_stmt||',A.'||g_final_pk_value(l_final(i))||' '||g_final_fk(l_final(i));
1503 else
1504 l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(l_level_copy,l_number_level_copy,
1505 g_final_next_parent(l_final(i)));
1506 l_stmt:=l_stmt||','||g_ltc_copy(l_level_copy_index(l_index))||'.'||g_final_next_pk(l_final(i))||' '||
1507 g_final_fk(l_final(i));
1508 end if;
1509 end loop;
1510 for i in 1..l_number_other_fks loop
1511 if l_below_diamond_flag(i) then
1512 l_stmt:=l_stmt||','||l_diamond_fk_table||'.'||l_other_fks(i);
1513 else
1514 l_stmt:=l_stmt||',0 '||l_other_fks(i);
1515 end if;
1516 end loop;
1517 l_stmt:=l_stmt||','''||g_level_display_prefix(l_child_index)||'(''||A.NAME||'')'' NAME '||
1518 ' from '||p_parent_level||' A ';
1519 for i in 1..l_number_level_copy loop
1520 l_stmt:=l_stmt||','||g_ltc_copy(l_level_copy_index(i));
1521 end loop;
1522 if l_diamond_fk_table is not null then
1523 l_stmt:=l_stmt||','||l_diamond_fk_table;
1524 end if;
1525 l_stmt:=l_stmt||','||l_opcode_table||' where '||l_opcode_table||'.row_id=A.rowid and '||
1526 l_opcode_table||'.status=1 and ';
1527 for i in 1..l_number_level_copy loop
1528 l_stmt:=l_stmt||'A.rowid='||g_ltc_copy(l_level_copy_index(i))||'.row_id and ';
1529 end loop;
1530 if l_diamond_fk_table is not null then
1531 l_stmt:=l_stmt||'A.rowid='||l_diamond_fk_table||'.row_id and ';
1532 end if;
1533 l_stmt:=substr(l_stmt,1,length(l_stmt)-4);
1534 if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid(l_child_index))=false then
1535 null;
1536 end if;
1537 if g_debug then
1538 write_to_log_file_n('Going to execute '||l_stmt||get_time);
1539 end if;
1540 execute immediate l_stmt;
1541 if g_debug then
1542 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
1543 end if;
1544 l_stmt:='create unique index '||g_update_rowid(l_child_index)||'u on '||
1545 g_update_rowid(l_child_index)||'(row_id) tablespace '||g_op_table_space;
1546 if g_parallel is not null then
1547 l_stmt:=l_stmt||' parallel '||g_parallel;
1548 end if;
1549 if g_debug then
1550 write_to_log_file_n('Going to execute '||l_stmt||get_time);
1551 end if;
1552 execute immediate l_stmt;
1553 l_table:=g_update_rowid(l_child_index);
1554 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_table,instr(l_table,'.')+1,
1555 length(l_table)),substr(l_table,1,instr(l_table,'.')-1));
1556 --call update
1557 l_update_stmt:='update /*+ORDERED USE_NL('||p_child_level||')*/ '||p_child_level||' set '||
1558 '(NAME,LAST_UPDATE_DATE,PUSHED_DOWN_FLAG';
1559 l_update_stmt_row:='update '||p_child_level||' set (NAME,LAST_UPDATE_DATE,PUSHED_DOWN_FLAG';
1560 for i in 1..l_number_final loop
1561 l_update_stmt:=l_update_stmt||','||g_final_fk(l_final(i));
1562 l_update_stmt_row:=l_update_stmt_row||','||g_final_fk(l_final(i));
1563 end loop;
1564 for i in 1..l_number_other_fks loop
1565 l_update_stmt:=l_update_stmt||','||l_other_fks(i);
1566 l_update_stmt_row:=l_update_stmt_row||','||l_other_fks(i);
1567 end loop;
1568 l_update_stmt:=l_update_stmt||')=(select NAME,SYSDATE,''Y''';
1569 l_update_stmt_row:=l_update_stmt_row||')=(select NAME,SYSDATE,''Y''';
1570 for i in 1..l_number_final loop
1571 l_update_stmt:=l_update_stmt||','||g_final_fk(l_final(i));
1572 l_update_stmt_row:=l_update_stmt_row||','||g_final_fk(l_final(i));
1573 end loop;
1574 for i in 1..l_number_other_fks loop
1575 l_update_stmt:=l_update_stmt||','||l_other_fks(i);
1576 l_update_stmt_row:=l_update_stmt_row||','||l_other_fks(i);
1577 end loop;
1578 l_update_stmt:=l_update_stmt||' from '||g_update_rowid(l_child_index)||' where '||
1579 g_update_rowid(l_child_index)||'.row_id='||p_child_level||'.rowid) where '||p_child_level||'.rowid in '||
1580 '(select row_id from '||g_update_rowid(l_child_index)||')';
1581 l_update_stmt_row:=l_update_stmt_row||' from '||g_update_rowid(l_child_index)||' where '||
1582 g_update_rowid(l_child_index)||'.row_id=:a) where '||p_child_level||'.rowid=:b';
1586 write_to_log_file_n('ROW-BY-ROW Update stmt '||l_update_stmt_row);
1583 --execute update
1584 if g_debug then
1585 write_to_log_file_n('MASS Update stmt '||l_update_stmt);
1587 end if;
1588 if execute_update_stmt(l_update_stmt,l_update_stmt_row,g_update_rowid(l_child_index))=false then
1589 return false;
1590 end if;
1591 if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid(l_child_index))=false then
1592 null;
1593 end if;
1594 end if;
1595 if EDW_OWB_COLLECTION_UTIL.does_table_have_data(l_opcode_table,'status=0')=2 then
1596 if g_debug then
1597 write_to_log_file_n('Insert needed for child level');
1598 end if;
1599 l_insert_stmt:='insert into '||p_child_level||'('||l_user_pk||','||l_pk;
1600 for i in 1..l_number_final loop
1601 l_insert_stmt:=l_insert_stmt||','||g_final_fk(l_final(i));
1602 end loop;
1603 for i in 1..l_number_other_fks loop
1604 l_insert_stmt:=l_insert_stmt||','||l_other_fks(i);
1605 end loop;
1606 l_insert_stmt:=l_insert_stmt||',NAME,CREATION_DATE,LAST_UPDATE_DATE,PUSHED_DOWN_FLAG) select ';
1607 if g_parallel is not null then
1608 l_insert_stmt:=l_insert_stmt||' /*+parallel(A,'||g_parallel||')*/ ';
1609 end if;
1610 l_insert_stmt:=l_insert_stmt||'A.'||l_parent_user_pk||'||''-'||g_level_prefix(l_parent_index)||''''||
1611 ','||g_level_seq(l_child_index)||'.NEXTVAL';
1612 for i in 1..l_number_final loop
1613 if g_final_next_parent(l_final(i))=p_parent_level then
1614 l_insert_stmt:=l_insert_stmt||',A.'||g_final_pk_value(l_final(i));
1615 else
1616 l_index:=EDW_OWB_COLLECTION_UTIL.index_in_table(l_level_copy,l_number_level_copy,
1617 g_final_next_parent(l_final(i)));
1618 l_insert_stmt:=l_insert_stmt||','||g_ltc_copy(l_level_copy_index(l_index))||'.'||g_final_next_pk(l_final(i));
1619 end if;
1620 end loop;
1621 for i in 1..l_number_other_fks loop
1622 if l_below_diamond_flag(i) then
1623 l_insert_stmt:=l_insert_stmt||','||l_diamond_fk_table||'.'||l_other_fks(i);
1624 else
1625 l_insert_stmt:=l_insert_stmt||',0 ';
1626 end if;
1627 end loop;
1628 l_insert_stmt:=l_insert_stmt||','''||g_level_display_prefix(l_child_index)||
1629 '(''||A.NAME||'')'',SYSDATE,SYSDATE,''Y'' from '||l_opcode_table||','||p_parent_level||' A ';
1630 for i in 1..l_number_level_copy loop
1631 l_insert_stmt:=l_insert_stmt||','||g_ltc_copy(l_level_copy_index(i));
1632 end loop;
1633 if l_diamond_fk_table is not null then
1634 l_insert_stmt:=l_insert_stmt||','||l_diamond_fk_table;
1635 end if;
1636 l_insert_stmt:=l_insert_stmt||' where '||l_opcode_table||'.row_id=A.rowid and '||
1637 l_opcode_table||'.status=0 and ';
1638 for i in 1..l_number_level_copy loop
1639 l_insert_stmt:=l_insert_stmt||'A.rowid='||g_ltc_copy(l_level_copy_index(i))||'.row_id and ';
1640 end loop;
1641 if l_diamond_fk_table is not null then
1642 l_insert_stmt:=l_insert_stmt||'A.rowid='||l_diamond_fk_table||'.row_id and ';
1643 end if;
1644 l_insert_stmt:=substr(l_insert_stmt,1,length(l_insert_stmt)-4);
1645 if g_debug then
1646 write_to_log_file_n('Going to execute Insert stmt '||l_insert_stmt||get_time);
1647 end if;
1648 EDW_OWB_COLLECTION_UTIL.alter_session('NO-PARALLEL');
1649 EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1650 execute immediate l_insert_stmt;
1651 if g_debug then
1652 write_to_log_file_n('Inserted '||sql%rowcount||' rows '||get_time);
1653 end if;
1654 if g_parallel is not null then
1655 EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
1656 end if;
1657 commit;
1658 end if;
1659 if EDW_OWB_COLLECTION_UTIL.drop_table(l_opcode_table)=false then
1660 null;
1661 end if;
1662 end if;
1663 if l_number_level_copy>0 then
1664 for i in 1..l_number_level_copy loop
1665 if EDW_OWB_COLLECTION_UTIL.drop_table(g_ltc_copy(l_level_copy_index(i)))=false then
1666 null;
1667 end if;
1668 end loop;
1669 end if;
1670 if l_diamond_fk_table is not null then
1671 if EDW_OWB_COLLECTION_UTIL.drop_table(l_diamond_fk_table)=false then
1672 null;
1673 end if;
1674 end if;
1675 return true;
1676 Exception when others then
1677 g_status_message:=sqlerrm;
1678 g_status:=false;
1679 write_to_log_file_n(g_status_message);
1680 return false;
1681 End;
1682
1683 function update_gilog(p_ilog varchar2) return boolean is
1684 l_stmt varchar2(2000);
1685 Begin
1686 if g_debug then
1687 write_to_log_file_n('In update_gilog for '||p_ilog||get_time);
1688 end if;
1689 l_stmt:='update '||p_ilog||' set status=2 where status=1';
1690 EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1691 execute immediate l_stmt;
1692 commit;
1693 if g_debug then
1694 write_to_log_file_n('Updated '||sql%rowcount||' rows from 1 to 2 for '||p_ilog||get_time);
1695 end if;
1696 return true;
1697 Exception when others then
1698 g_status_message:=sqlerrm;
1699 g_status:=false;
1700 write_to_log_file_n(g_status_message);
1701 return false;
1702 End;
1703 /*
1704 this function sets the status of the ilog from 0 to 1 and also deletes those that are 1 first
1705 returns:
1706 0: error
1707 1: no more records to change from 0 to 1
1711 l_stmt varchar2(10000);
1708 2: success
1709 */
1710 function set_gilog_status(p_ilog in out NOCOPY varchar2,p_index number) return number is
1712 l_count number;
1713 l_ilog varchar2(400);
1714 l_ltc_pk varchar2(400);
1715 Begin
1716 --update
1717 if g_debug then
1718 write_to_log_file_n('In set_gilog_status');
1719 end if;
1720 if g_type_ilog_generation='UPDATE' then
1721 if EDW_OWB_COLLECTION_UTIL.does_table_have_data(p_ilog,'status=1')=2 then
1722 return 2;
1723 end if;
1724 if g_collection_size =0 then
1725 l_stmt:='update '||p_ilog||' set status=1 where status=0';
1726 else
1727 l_stmt:='update '||p_ilog||' set status=1 where status=0 and rownum <='||g_collection_size;
1728 end if;
1729 if g_debug then
1730 write_to_log_file_n('Going to execute '||l_stmt||get_time);
1731 end if;
1732 EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
1733 execute immediate l_stmt;
1734 l_count:=sql%rowcount;
1735 commit;
1736 if g_debug then
1737 write_to_log_file_n('commit'||get_time);
1738 end if;
1739 if g_debug then
1740 write_to_log_file_n('Updated '||l_count||' rows in '||p_ilog||get_time);
1741 end if;
1742 elsif g_type_ilog_generation='CTAS' then
1743 l_ltc_pk:=g_level_pk(p_index);
1744 if substr(p_ilog,length(p_ilog),1)='A' then
1745 l_ilog:=substr(p_ilog,1,length(p_ilog)-1);
1746 else
1747 l_ilog:=p_ilog||'A';
1748 end if;
1749 l_stmt:='create table '||l_ilog||' tablespace '||g_op_table_space;
1750 l_stmt:=l_stmt||' storage (initial 4M next 4M pctincrease 0) ';
1751 if g_parallel is not null then
1752 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
1753 end if;
1754 if g_collection_size > 0 then
1755 if g_snplog_has_pk(p_index) then
1756 l_stmt:=l_stmt||' as select row_id,'||l_ltc_pk||',decode(status,1,2,2,2,decode(sign(rownum-'||
1757 g_collection_size||'),1,0,1)) status from (select row_id,'||l_ltc_pk||',status from '||p_ilog||
1758 ' order by status) abc ';
1759 else
1760 l_stmt:=l_stmt||' as select row_id,decode(status,1,2,2,2,decode(sign(rownum-'||
1761 g_collection_size||'),1,0,1)) status from (select row_id,status from '||p_ilog||' order by status) abc ';
1762 end if;
1763 else
1764 if g_snplog_has_pk(p_index) then
1765 l_stmt:=l_stmt||' as select row_id,'||l_ltc_pk||',decode(status,1,2,0,1,2) status from '||
1766 p_ilog;
1767 else
1768 l_stmt:=l_stmt||' as select row_id,decode(status,1,2,0,1,2) status from '||
1769 p_ilog;
1770 end if;
1771 end if;
1772 if EDW_OWB_COLLECTION_UTIL.drop_table(l_ilog)=false then
1773 null;
1774 end if;
1775 if g_debug then
1776 write_to_log_file_n('Going to execute '||l_stmt||get_time);
1777 end if;
1778 execute immediate l_stmt;
1779 if g_debug then
1780 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
1781 end if;
1782 if EDW_OWB_COLLECTION_UTIL.drop_table(p_ilog)=false then
1783 null;
1784 end if;
1785 p_ilog:=l_ilog;
1786 if EDW_OWB_COLLECTION_UTIL.does_table_have_data(p_ilog,' status=1 ')<2 then
1787 l_count:=0;
1788 else
1789 l_count:=1;
1790 end if;
1791 if g_debug then
1792 write_to_log_file_n('Time '||get_time);
1793 end if;
1794 end if;
1795 if l_count=0 then
1796 return 1;
1797 else
1798 return 2;
1799 end if;
1800 Exception when others then
1801 g_status_message:=sqlerrm;
1802 write_to_log_file_n(g_status_message);
1803 g_status:=false;
1804 return 0;
1805 End;
1806
1807
1808 function get_user_key(p_key varchar2) return varchar2 is
1809 Begin
1810 return substr(p_key,1,instr(p_key,'_KEY')-1);
1811 Exception when others then
1812 g_status_message:=sqlerrm;
1813 g_status:=false;
1814 write_to_log_file_n(g_status_message);
1815 return null;
1816 End;
1817
1818 procedure init_all(p_job_id number) is
1819 l_name varchar2(200);
1820 l_name_org varchar2(200);
1821 Begin
1822 g_number_final:=0;
1823 g_count:=0;
1824 /*
1825 the ilog and milog are dropped ONLY AFTER THE LTC SNAPSHOT LOGS have been TRUNCATED!!
1826 */
1827 if g_debug then
1828 write_to_log_file_n('In init_all p_job_id='||p_job_id);
1829 end if;
1830 for i in 1..g_number_levels loop
1831 g_level_ids(i):=EDW_OWB_COLLECTION_UTIL.get_object_id(g_levels(i));
1832 g_skip_ilog_update(i):=false;
1833 if p_job_id is null then
1834 l_name:='TAB_'||g_level_ids(i)||'_';
1835 else
1836 l_name:='TAB_'||g_level_ids(i)||'_'||p_job_id||'_';
1837 end if;
1838 l_name_org:='TAB_'||g_level_ids(i)||'_';
1839 g_level_ilog(i):=g_bis_owner||'.'||l_name||'IL';
1840 g_level_ilog_name(i):=g_level_ilog(i);
1841 if p_job_id is null then
1842 if EDW_OWB_COLLECTION_UTIL.check_table(g_level_ilog(i)||'A') then
1843 g_level_ilog(i):=g_level_ilog(i)||'A';
1844 end if;
1845 end if;
1846 g_level_ilog_found(i):=false;
1847 g_insert_rowid(i):=g_bis_owner||'.'||l_name||'IR';
1848 g_update_rowid(i):=g_bis_owner||'.'||l_name||'UR';
1849 g_snplog_has_pk(i):=false;
1850 g_analyze_needed(i):=false;
1854 g_number_diamond_level:=0;
1851 g_ltc_copy(i):=g_bis_owner||'.'||l_name||'LC';
1852 end loop;
1853 g_type_ilog_generation:='CTAS';
1855 Exception when others then
1856 g_status_message:=sqlerrm;
1857 g_status:=false;
1858 write_to_log_file_n(g_status_message);
1859 End;
1860
1861 function get_time return varchar2 is
1862 begin
1863 return ' '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');
1864 Exception when others then
1865 write_to_log_file_n('Error in get_time '||sqlerrm);
1866 End;
1867
1868 procedure write_to_log_file(p_message varchar2) is
1869 begin
1870 EDW_OWB_COLLECTION_UTIL.write_to_log_file(p_message);
1871 Exception when others then
1872 null;
1873 End;
1874
1875 procedure write_to_log_file_n(p_message varchar2) is
1876 begin
1877 write_to_log_file(' ');
1878 write_to_log_file(p_message);
1879 Exception when others then
1880 null;
1881 End;
1882
1883 function does_snp_have_data(p_level varchar2) return boolean is
1884 Begin
1885 if g_debug then
1886 write_to_log_file_n('In does_snp_have_data, p_level is '||p_level);
1887 end if;
1888 for i in 1..g_number_levels loop
1889 if g_levels(i)=p_level then
1890 if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_level_snapshot_logs(i))=2 then
1891 return true;
1892 else
1893 return false;
1894 end if;
1895 exit;
1896 end if;
1897 end loop;
1898 return false;
1899 Exception when others then
1900 g_status_message:=sqlerrm;
1901 g_status:=false;
1902 write_to_log_file_n(g_status_message);
1903 return false;
1904 End;
1905
1906 function get_level_snplog(p_level varchar2) return varchar2 is
1907 Begin
1908 if g_debug then
1909 write_to_log_file_n('In get_level_snplog, p_level='||p_level);
1910 end if;
1911 for i in 1..g_number_levels loop
1912 if g_levels(i)=p_level then
1913 return g_level_snapshot_logs(i);
1914 end if;
1915 end loop;
1916 return null;
1917 Exception when others then
1918 g_status_message:=sqlerrm;
1919 g_status:=false;
1920 write_to_log_file_n(g_status_message);
1921 return null;
1922 End;
1923
1924 function check_levels_for_data return boolean is
1925 Begin
1926 if g_debug then
1927 write_to_log_file_n('In check_levels_for_data');
1928 end if;
1929 if g_full_refresh then
1930 for i in 1..g_number_levels loop
1931 if g_level_consider(i) then --this may be false if the user does not want this level pushed down
1932 if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_levels(i))=2 then
1933 g_level_consider(i):=true;
1934 else
1935 g_level_consider(i):=false;
1936 end if;
1937 end if;
1938 end loop;
1939 else
1940 for i in 1..g_number_levels loop
1941 if g_level_consider(i) then
1942 if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_level_snapshot_logs(i))=2 then
1943 g_level_consider(i):=true;
1944 else
1945 g_level_consider(i):=false;
1946 end if;
1947 end if;
1948 end loop;
1949 end if;
1950 /*is this reqd ? will a ltc table have only 2 rows?
1951 */
1952 for i in 1..g_number_levels loop
1953 g_level_full_insert(i):=false;--default
1954 if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_levels(i))=1 then
1955 g_level_full_insert(i):=true;
1956 if g_debug then
1957 write_to_log_file_n('Level '||g_levels(i)||' Empty');
1958 end if;
1959 elsif EDW_OWB_COLLECTION_UTIL.does_table_have_only_n_row(g_levels(i),2)=2
1960 and (EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_levels(i),g_level_pk(i)||'=0')=2
1961 and EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_levels(i),g_level_pk(i)||'=-1')=2) then
1962 g_level_full_insert(i):=true;
1963 if g_debug then
1964 write_to_log_file_n('Level '||g_levels(i)||' only has NA_EDW and NA_ERR rows');
1965 end if;
1966 end if;
1967 end loop;
1968 if g_debug then
1969 for i in 1..g_number_levels loop
1970 if g_level_consider(i) then
1971 write_to_log_file_n('Push down implemented for '||g_levels(i));
1972 else
1973 write_to_log_file_n('Push down NOT implemented for '||g_levels(i));
1974 end if;
1975 end loop;
1976 end if;
1977 return true;
1978 Exception when others then
1979 g_status_message:=sqlerrm;
1980 g_status:=false;
1981 write_to_log_file_n(g_status_message);
1982 return false;
1983 End;
1984
1985 function get_index_for_level(p_level varchar2) return number is
1986 Begin
1987 if g_debug then
1988 write_to_log_file_n('In get_index_for_level, plevel is '||p_level);
1989 end if;
1990 for i in 1..g_number_levels loop
1991 if g_levels(i)=p_level then
1992 return i;
1993 end if;
1994 end loop;
1995 return null;
1996 Exception when others then
1997 g_status_message:=sqlerrm;
1998 g_status:=false;
1999 write_to_log_file_n(g_status_message);
2000 return null;
2001 End;
2002
2003 function get_fks_without_fk(p_level varchar2, p_fk varchar2,
2004 p_fks_out out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,p_number_fks_out out NOCOPY number) return boolean is
2005 l_count number;
2009 write_to_log_file('p_level='||p_level);
2006 Begin
2007 if g_debug then
2008 write_to_log_file_n('In get_fks_without_fk');
2010 write_to_log_file('p_fk='||p_fk);
2011 end if;
2012 p_number_fks_out:=0;
2013 l_count:=0;
2014 for i in 1..g_number_levels loop
2015 l_count:=l_count+g_child_level_number(i);
2016 end loop;
2017 for i in 1..l_count loop
2018 if g_child_levels(i)=p_level then
2019 p_number_fks_out:=p_number_fks_out+1;
2020 p_fks_out(p_number_fks_out):=g_child_fk(i);
2021 end if;
2022 end loop;
2023 return true;
2024 Exception when others then
2025 g_status_message:=sqlerrm;
2026 g_status:=false;
2027 write_to_log_file_n(g_status_message);
2028 return false;
2029 End;
2030
2031 function get_level_seq return boolean is
2032 l_stmt varchar2(10000);
2033 l_in_stmt varchar2(10000);
2034 TYPE CurTyp IS REF CURSOR;
2035 cv CurTyp;
2036 l_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
2037 l_seq EDW_OWB_COLLECTION_UTIL.varcharTableType;
2038 l_number_level number;
2039 Begin
2040 if g_debug then
2041 write_to_log_file_n('In get_level_seq');
2042 end if;
2043 l_in_stmt:=null;
2044 for i in 1..g_number_levels loop
2045 if i=1 then
2046 l_in_stmt:=l_in_stmt||''''||g_levels(i)||'''';
2047 else
2048 l_in_stmt:=l_in_stmt||','''||g_levels(i)||'''';
2049 end if;
2050 end loop;
2051 if g_debug then
2052 write_to_log_file_n('In stmt='||l_in_stmt);
2053 end if;
2054 --not checked
2055 l_stmt:='select seq.sequence_name, rel.name from edw_tables_md_v rel, edw_pvt_sequences_md_v seq, '||
2056 'edw_pvt_map_properties_md_v map, edw_pvt_map_sources_md_v ru where rel.name in ('||l_in_stmt||') '||
2057 'and map.primary_target=rel.elementid and ru.mapping_id=map.mapping_id '||
2058 'and ru.source_id=seq.sequence_id ';
2059 if g_debug then
2060 write_to_log_file_n('Going to execute '||l_stmt);
2061 end if;
2062 l_number_level:=1;
2063 begin
2064 open cv for l_stmt;
2065 loop
2066 fetch cv into l_seq(l_number_level),l_level(l_number_level);
2067 exit when cv%notfound;
2068 l_number_level:=l_number_level+1;
2069 end loop;
2070 l_number_level:=l_number_level-1;
2071 close cv;
2072 exception when others then
2073 g_status_message:=sqlerrm;
2074 write_to_log_file_n(g_status_message);
2075 write_to_log_file('Problem stmt '||l_stmt);
2076 end;
2077 /*
2078 if g_debug then
2079 write_to_log_file_n('Results');
2080 for i in 1..l_number_level loop
2081 write_to_log_file(l_level(i)||'('||l_seq(i)||')');
2082 end loop;
2083 end if;*/
2084 for i in 1..g_number_levels loop
2085 g_level_seq(i):=null;
2086 end loop;
2087 for i in 1..g_number_levels loop
2088 for j in 1..l_number_level loop
2089 if g_levels(i)=l_level(j) then
2090 g_level_seq(i):=l_seq(j);
2091 exit;
2092 end if;
2093 end loop;
2094 end loop;
2095 if g_debug then
2096 write_to_log_file_n('Results');
2097 for i in 1..g_number_levels loop
2098 write_to_log_file(g_levels(i)||'('||g_level_seq(i)||')');
2099 end loop;
2100 end if;
2101 return true;
2102 Exception when others then
2103 g_status_message:=sqlerrm;
2104 g_status:=false;
2105 write_to_log_file_n(g_status_message);
2106 return false;
2107 End;
2108
2109 function find_lowest_level return boolean is
2110 l_found boolean;
2111 l_stmt varchar2(4000);
2112 Begin
2113 if g_debug then
2114 write_to_log_file_n('In find_lowest_level');
2115 end if;
2116 g_lowest_level:=g_level_order(g_number_levels);
2117 /*
2118 for i in 1..g_number_levels loop
2119 if g_child_level_number(i)=0 then
2120 g_lowest_level:=g_levels(i);
2121 exit;
2122 end if;
2123 end loop;
2124 */
2125 if g_debug then
2126 write_to_log_file_n('The lowest level is '||g_lowest_level);
2127 end if;
2128 return true;
2129 Exception when others then
2130 g_status_message:=sqlerrm;
2131 g_status:=false;
2132 write_to_log_file_n(g_status_message);
2133 return false;
2134 End;
2135
2136 procedure clean_up is
2137 Begin
2138 if g_debug then
2139 write_to_log_file_n('In clean_up');
2140 end if;
2141 for i in 1..g_number_levels loop
2142 if EDW_OWB_COLLECTION_UTIL.drop_table(g_update_rowid(i))=false then
2143 null;
2144 end if;
2145 if EDW_OWB_COLLECTION_UTIL.drop_table(g_insert_rowid(i))=false then
2146 null;
2147 end if;
2148 end loop;
2149 Exception when others then
2150 g_status_message:=sqlerrm;
2151 g_status:=false;
2152 write_to_log_file_n(g_status_message);
2153 End;
2154
2155 function create_ilog_tables return boolean is
2156 Begin
2157 if g_debug then
2158 write_to_log_file_n('In create_ilog_tables');
2159 end if;
2160 for i in 1..(g_number_levels-1) loop
2161 if create_ilog_tables(i)=false then
2162 write_to_log_file_n('create_ilog_tables returned with error for '||g_level_order(i));
2163 end if;
2164 end loop;
2165 return true;
2166 Exception when others then
2167 g_status_message:=sqlerrm;
2171 End;
2168 g_status:=false;
2169 write_to_log_file_n(g_status_message);
2170 return false;
2172
2173 function create_ilog_tables(p_index number) return boolean is
2174 l_stmt varchar2(10000);
2175 l_index number;
2176 Begin
2177 if g_debug then
2178 write_to_log_file_n('In create_ilog_tables('||p_index||')');
2179 end if;
2180 l_index:=get_index_for_level(g_level_order(p_index));
2181 if EDW_OWB_COLLECTION_UTIL.is_column_in_table(g_level_snapshot_logs(l_index),g_level_pk(l_index),
2182 g_table_owner) then
2183 g_snplog_has_pk(l_index):=true;
2184 end if;
2185 if EDW_OWB_COLLECTION_UTIL.check_table(g_level_ilog(l_index))=false then
2186 l_stmt:='create table '||g_level_ilog(l_index)||' tablespace '||g_op_table_space;
2187 if g_parallel is not null then
2188 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2189 end if;
2190 l_stmt:=l_stmt||' as select chartorowid(M_ROW$$) row_id ';
2191 if g_snplog_has_pk(l_index) then
2192 l_stmt:=l_stmt||','||g_level_pk(l_index);
2193 end if;
2194 l_stmt:=l_stmt||',0 status from '||g_level_snapshot_logs(l_index)||' where 1=2';
2195 if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2196 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2197 g_status:=false;
2198 write_to_log_file_n(g_status_message);
2199 return false;
2200 end if;
2201 else
2202 g_level_ilog_found(l_index):=true;
2203 end if;
2204 return true;
2205 Exception when others then
2206 g_status_message:=sqlerrm;
2207 g_status:=false;
2208 write_to_log_file_n(g_status_message);
2209 return false;
2210 End;
2211
2212
2213 function move_data_into_ilog return boolean is
2214 l_index number;
2215 Begin
2216 if g_debug then
2217 write_to_log_file_n('In move_data_into_ilog');
2218 end if;
2219 for i in 1..g_number_levels loop
2220 l_index:=get_index_for_level(g_level_order(i));
2221 if g_debug then
2222 if g_level_consider(l_index) and g_levels(l_index)<>g_lowest_level then
2223 write_to_log_file_n('for level '||g_levels(l_index)||' move_data_into_ilog');
2224 else
2225 write_to_log_file_n('for level '||g_levels(l_index)||' NO move_data_into_ilog');
2226 end if;
2227 end if;
2228 if g_level_consider(l_index) and g_levels(l_index)<>g_lowest_level then
2229 if move_data_into_ilog(i)=false then
2230 write_to_log_file_n('move_data_into_ilog returned with error for '||g_level_order(i));
2231 return false;
2232 end if;
2233 end if;
2234 end loop;
2235 return true;
2236 Exception when others then
2237 g_status_message:=sqlerrm;
2238 g_status:=false;
2239 write_to_log_file_n(g_status_message);
2240 return false;
2241 End;
2242
2243 function move_data_into_ilog(p_index number) return boolean is
2244 l_stmt varchar2(10000);
2245 l_index number;
2246 l_ilog varchar2(400);
2247 l_level_ilogm_el varchar2(400);
2248 l_level_ilog_el varchar2(400);
2249 l_level_count number;
2250 l_ilog_count number;
2251 l_use_nl boolean;
2252 Begin
2253 if g_debug then
2254 write_to_log_file_n('In move_data_into_ilog(int), pindex='||p_index);
2255 end if;
2256 l_index:=get_index_for_level(g_level_order(p_index));
2257 if g_level_ilog_found(l_index)=false then
2258 if g_full_refresh then
2259 if g_snplog_has_pk(l_index) then
2260 l_stmt:='insert into '||g_level_ilog(l_index)||'(row_id,'||g_level_pk(l_index)||',status) select ';
2261 if g_parallel is not null then
2262 l_stmt:=l_stmt||' /*+PARALLEL ('||g_levels(l_index)||','||g_parallel||')*/ ';
2263 end if;
2264 l_stmt:=l_stmt||' rowid,'||g_level_pk(l_index)||',0 from '||g_levels(l_index);
2265 else
2266 l_stmt:='insert into '||g_level_ilog(l_index)||'(row_id, status) select ';
2267 if g_parallel is not null then
2268 l_stmt:=l_stmt||' /*+PARALLEL ('||g_levels(l_index)||','||g_parallel||')*/ ';
2269 end if;
2270 l_stmt:=l_stmt||' rowid,0 from '||g_levels(l_index);
2271 end if;
2272 l_stmt:=l_stmt||' where PUSHED_DOWN_FLAG is null';
2273 else
2274 if g_snplog_has_pk(l_index) then
2275 l_stmt:='insert into '||g_level_ilog(l_index)||'(row_id,'||g_level_pk(l_index)||',status) select ';
2276 if g_parallel is not null then
2277 l_stmt:=l_stmt||' /*+PARALLEL ('||g_level_snapshot_logs(l_index)||','||g_parallel||')*/ ';
2278 end if;
2279 l_stmt:=l_stmt||' distinct chartorowid(M_ROW$$),'||g_level_snapshot_logs(l_index)||'.'||g_level_pk(l_index)||
2280 ',0 from '||g_level_snapshot_logs(l_index);
2281 else
2282 l_stmt:='insert into '||g_level_ilog(l_index)||'(row_id, status) select ';
2283 if g_parallel is not null then
2284 l_stmt:=l_stmt||' /*+PARALLEL ('||g_level_snapshot_logs(l_index)||','||g_parallel||')*/ ';
2285 end if;
2286 l_stmt:=l_stmt||' distinct chartorowid(M_ROW$$),0 from '||g_level_snapshot_logs(l_index);
2287 end if;
2288 l_stmt:=l_stmt||','||g_levels(l_index)||' where '||g_levels(l_index)||'.rowid='||
2289 g_level_snapshot_logs(l_index)||'.M_ROW$$ and '||g_levels(l_index)||'.PUSHED_DOWN_FLAG is null';
2290 end if;
2291 EDW_OWB_COLLECTION_UTIL.set_rollback(g_rollback);
2292 if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2296 return false;
2293 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2294 g_status:=false;
2295 write_to_log_file_n(g_status_message);
2297 end if;
2298 commit;
2299 else --table already there...part of error recovery
2300 --recreate g_level_ilog
2301 --recover g_level_ilog
2302 if substr(g_level_ilog(l_index),length(g_level_ilog(l_index)),1)='A' then
2303 l_level_ilog_el:=substr(g_level_ilog(l_index),1,length(g_level_ilog(l_index))-1);
2304 else
2305 l_level_ilog_el:=g_level_ilog(l_index)||'A';
2306 end if;
2307 l_stmt:='create table '||l_level_ilog_el||' tablespace '||g_op_table_space;
2308 if g_parallel is not null then
2309 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2310 end if;
2311 if g_snplog_has_pk(l_index) then
2312 l_stmt:=l_stmt||' as select /*+ordered*/ A.rowid row_id';
2313 l_stmt:=l_stmt||',A.'||g_level_pk(l_index)||',B.status';
2314 l_stmt:=l_stmt||' from '||g_level_ilog(l_index)||' B,'||g_levels(l_index)||
2315 ' A where A.'||g_level_pk(l_index)||'=B.'||g_level_pk(l_index);
2316 else
2317 l_level_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_levels(l_index),g_table_owner);
2318 l_ilog_count:=EDW_OWB_COLLECTION_UTIL.get_table_count_stats(g_level_ilog(l_index),g_bis_owner);
2319 l_use_nl:=EDW_OWB_COLLECTION_UTIL.get_join_nl(l_ilog_count,l_level_count,g_join_nl_percentage);
2320 l_stmt:=l_stmt||' as select /*+ordered ';
2321 if l_use_nl then
2322 l_stmt:=l_stmt||'use_nl(A)';
2323 end if;
2324 l_stmt:=l_stmt||'*/ ';
2325 if g_parallel is not null then
2326 l_stmt:=l_stmt||'/*+parallel(A,'||g_parallel||')*/ ';
2327 end if;
2328 l_stmt:=l_stmt||'B.row_id,B.status';
2329 l_stmt:=l_stmt||' from '||g_level_ilog(l_index)||' B,'||g_levels(l_index)||' A where A.rowid=B.row_id';
2330 end if;
2331 if EDW_OWB_COLLECTION_UTIL.drop_table(l_level_ilog_el)=false then
2332 null;
2333 end if;
2334 if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2335 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2336 g_status:=false;
2337 write_to_log_file_n(g_status_message);
2338 return false;
2339 end if;
2340 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_level_ilog_el,instr(l_level_ilog_el,'.')+1,
2341 length(l_level_ilog_el)),substr(l_level_ilog_el,1,instr(l_level_ilog_el,'.')-1));
2342 if EDW_OWB_COLLECTION_UTIL.drop_table(g_level_ilog(l_index))=false then
2343 null;
2344 end if;
2345 g_level_ilog(l_index):=l_level_ilog_el;
2346 l_level_ilog_el:=g_level_ilog(l_index)||'T';
2347 l_stmt:='create table '||l_level_ilog_el||' tablespace '||g_op_table_space;
2348 if g_parallel is not null then
2349 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2350 end if;
2351 l_stmt:=l_stmt||' as select ';
2352 if g_parallel is not null then
2353 l_stmt:=l_stmt||' /*+PARALLEL ('||g_level_snapshot_logs(l_index)||','||g_parallel||')*/ ';
2354 end if;
2355 if g_snplog_has_pk(l_index) then
2356 l_stmt:=l_stmt||' distinct chartorowid(M_ROW$$) row_id,'||g_level_snapshot_logs(l_index)||'.'||g_level_pk(l_index)||
2357 ',0 status from '||g_level_snapshot_logs(l_index);
2358 else
2359 l_stmt:=l_stmt||' distinct chartorowid(M_ROW$$) row_id,0 status from '||g_level_snapshot_logs(l_index);
2360 end if;
2361 l_stmt:=l_stmt||','||g_levels(l_index)||' where '||g_levels(l_index)||'.rowid='||
2362 g_level_snapshot_logs(l_index)||'.M_ROW$$ and '||g_levels(l_index)||'.PUSHED_DOWN_FLAG is null '||
2363 'MINUS select ';
2364 if g_snplog_has_pk(l_index) then
2365 l_stmt:=l_stmt||'row_id,'||g_level_pk(l_index)||',0 status from '||g_level_ilog(l_index);
2366 else
2367 l_stmt:=l_stmt||'row_id,'||'0 status from '||g_level_ilog(l_index);
2368 end if;
2369 if EDW_OWB_COLLECTION_UTIL.drop_table(l_level_ilog_el)=false then
2370 null;
2371 end if;
2372 if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2373 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2374 g_status:=false;
2375 write_to_log_file_n(g_status_message);
2376 return false;
2377 end if;
2378 if g_snplog_has_pk(l_index) then
2379 l_stmt:='insert into '||g_level_ilog(l_index)||'(row_id,'||g_level_pk(l_index)||',status) select '||
2380 'row_id,'||g_level_pk(l_index)||',status from '||l_level_ilog_el;
2381 else
2382 l_stmt:='insert into '||g_level_ilog(l_index)||'(row_id, status) select '||
2383 'row_id,status from '||l_level_ilog_el;
2384 end if;
2385 if EDW_OWB_COLLECTION_UTIL.execute_stmt(l_stmt)=false then
2386 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2387 g_status:=false;
2388 write_to_log_file_n(g_status_message);
2389 return false;
2390 end if;
2391 commit;
2392 if EDW_OWB_COLLECTION_UTIL.drop_table(l_level_ilog_el)=false then
2393 null;
2394 end if;
2395 end if;
2396 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(g_level_ilog(l_index),instr(g_level_ilog(l_index),'.')+1,
2397 length(g_level_ilog(l_index))),substr(g_level_ilog(l_index),1,instr(g_level_ilog(l_index),'.')-1));
2398 return true;
2399 Exception when others then
2400 g_status_message:=sqlerrm;
2401 g_status:=false;
2402 write_to_log_file_n(g_status_message);
2403 return false;
2404 End;
2405
2409 write_to_log_file_n('In analyze_ltc_tables');
2406 function analyze_ltc_tables return boolean is
2407 Begin
2408 if g_debug then
2410 end if;
2411 for i in 1..g_number_levels loop
2412 if g_analyze_needed(i)=true then
2413 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(g_levels(i), g_table_owner);
2414 if g_debug then
2415 write_to_log_file_n('Analyzed '||g_levels(i));
2416 end if;
2417 end if;
2418 end loop;
2419 return true;
2420 Exception when others then
2421 g_status_message:=sqlerrm;
2422 g_status:=false;
2423 write_to_log_file_n(g_status_message);
2424 return false;
2425 End;
2426 /*
2427 drop_ilog is called from all collect in clean_up
2428 */
2429 procedure drop_ilog is
2430 Begin
2431 if g_debug then
2432 write_to_log_file_n('In drop_ilog');
2433 end if;
2434 for i in 1..g_number_levels loop
2435 if EDW_OWB_COLLECTION_UTIL.drop_table(g_level_ilog(i))=false then
2436 null;
2437 end if;
2438 if EDW_OWB_COLLECTION_UTIL.drop_table(g_level_ilog(i)||'A')=false then
2439 null;
2440 end if;
2441 if EDW_OWB_COLLECTION_UTIL.drop_ilog_tables(g_level_ilog(i)||'_IL',null,g_bis_owner)=false then
2442 null;
2443 end if;
2444 end loop;
2445 Exception when others then
2446 g_status_message:=sqlerrm;
2447 g_status:=false;
2448 write_to_log_file_n(g_status_message);
2449 End;
2450
2451 procedure insert_into_load_progress_d(p_load_fk number,p_object_name varchar2,p_load_progress varchar2,
2452 p_start_date date,p_end_date date,p_category varchar2, p_operation varchar2,p_seq_id varchar2,p_flag varchar2) is
2453 Begin
2454 if g_debug then
2455 EDW_OWB_COLLECTION_UTIL.insert_into_load_progress(p_load_fk,p_object_name,g_level_id,p_load_progress,
2456 p_start_date,p_end_date,p_category,p_operation,p_seq_id,p_flag,g_level_id);
2457 commit;
2458 end if;
2459 Exception when others then
2460 g_status_message:=sqlerrm;
2461 write_to_log_file_n(g_status_message);
2462 End;
2463
2464 /*
2465 0 error
2466 1 no need to push down
2467 2 need to push down
2468 */
2469 function find_ltc_to_push_down return number is
2470 l_found boolean;
2471 l_option_value varchar2(20);
2472 l_itemset_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
2473 l_number_itemset_cols number;
2474 Begin
2475 l_found:=false;
2476 if g_read_cfig_options then
2477 if g_debug then
2478 write_to_log_file_n('Reading the cfig data for levels to push down');
2479 end if;
2480 if edw_option.get_warehouse_option(g_dim_name,null,'LEVELPUSHDOWN',l_option_value)=false then
2481 g_status_message:=edw_option.g_status_message;
2482 g_status:=false;
2483 write_to_log_file_n(g_status_message);
2484 return 0;
2485 end if;
2486 if l_option_value='Y' then
2487 if g_debug then
2488 write_to_log_file_n('Push down implemented for the dimension');
2489 end if;
2490 for i in 1..g_number_levels loop
2491 g_level_consider(i):=true;
2492 end loop;
2493 if edw_option.get_option_columns(g_dim_name,null,'LEVELPUSHDOWN',l_itemset_cols,
2494 l_number_itemset_cols)=false then
2495 g_status_message:=edw_option.g_status_message;
2496 g_status:=false;
2497 return 0;
2498 end if;
2499 if l_number_itemset_cols>0 then
2500 for i in 1..g_number_levels loop
2501 if EDW_OWB_COLLECTION_UTIL.value_in_table(l_itemset_cols,l_number_itemset_cols,
2502 substr(g_levels(i),1,instr(g_levels(i),'_LTC',-1)-1))=false then
2503 g_level_consider(i):=false;
2504 end if;
2505 end loop;
2506 end if;
2507 if g_debug then
2508 for i in 1..g_number_levels loop
2509 if g_level_consider(i) then
2510 write_to_log_file_n('Push down implemented for '||g_levels(i));
2511 else
2512 write_to_log_file_n('Push down NOT implemented for '||g_levels(i));
2513 end if;
2514 end loop;
2515 end if;
2516 return 2;
2517 else
2518 return 1;
2519 end if;
2520 else
2521 if EDW_OWB_COLLECTION_UTIL.is_itemset_implemented(g_dim_name,'EDW_LEVEL_PUSH_DOWN')='Y' then
2522 --all levels
2523 for i in 1..g_number_levels loop
2524 g_level_consider(i):=true;
2525 end loop;
2526 if g_debug then
2527 write_to_log_file_n('Push down implemented for all levels of the dimension');
2528 end if;
2529 return 2;
2530 end if;
2531 for i in 1..g_number_levels loop
2532 --g_levels
2533 if EDW_OWB_COLLECTION_UTIL.is_itemset_implemented(g_levels(i),'EDW_LEVEL_PUSH_DOWN')='Y' then
2534 g_level_consider(i):=true;
2535 l_found:=true;
2536 else
2537 g_level_consider(i):=false;
2538 end if;
2539 end loop;
2540 end if;
2541 if g_debug then
2542 for i in 1..g_number_levels loop
2543 if g_level_consider(i) then
2544 write_to_log_file_n('Push down implemented for '||g_levels(i));
2545 else
2546 write_to_log_file_n('Push down NOT implemented for '||g_levels(i));
2547 end if;
2548 end loop;
2549 end if;
2550 if l_found then
2554 end if;
2551 return 2;
2552 else
2553 return 1;
2555 Exception when others then
2556 g_status_message:=sqlerrm;
2557 g_status:=false;
2558 write_to_log_file_n(g_status_message);
2559 return 0;
2560 End;
2561
2562 function create_ilog_copy(p_ilog varchar2,p_ilog_copy varchar2) return boolean is
2563 l_stmt varchar2(10000);
2564 Begin
2565 l_stmt:='create table '||p_ilog_copy||' tablespace '||g_op_table_space;
2566 if g_parallel is not null then
2567 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2568 end if;
2569 l_stmt:=l_stmt||' as select row_id from '||p_ilog||' where status=1';
2570 if EDW_OWB_COLLECTION_UTIL.drop_table(p_ilog_copy)=false then
2571 null;
2572 end if;
2573 if g_debug then
2574 write_to_log_file_n('Going to execute '||l_stmt||get_time);
2575 end if;
2576 execute immediate l_stmt;
2577 if g_debug then
2578 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
2579 end if;
2580 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(p_ilog_copy,instr(p_ilog_copy,'.')+1,
2581 length(p_ilog_copy)),substr(p_ilog_copy,1,instr(p_ilog_copy,'.')-1));
2582 return true;
2583 Exception when others then
2584 g_status_message:=sqlerrm;
2585 g_status:=false;
2586 write_to_log_file_n(g_status_message);
2587 return false;
2588 End;
2589
2590 function find_diamond_levels return boolean is
2591 l_stmt varchar2(10000);
2592 TYPE CurTyp IS REF CURSOR;
2593 cv CurTyp;
2594 l_top_level varchar2(200);
2595 l_hier_hold varchar2(200);
2596 l_child_hold varchar2(200);
2597 l_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
2598 l_child_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
2599 l_hier EDW_OWB_COLLECTION_UTIL.varcharTableType;
2600 l_number_ltc number;
2601 Begin
2602 l_stmt:='select parent_level.level_name||''_LTC'',child_level.level_name||''_LTC'', hier.hier_name '||
2603 'from '||
2604 'edw_pvt_level_relation_md_v lvl_rel, '||
2605 'edw_hierarchies_md_v hier, '||
2606 'edw_dimensions_md_v dim, '||
2607 'edw_levels_md_v child_level, '||
2608 'edw_levels_md_v parent_level '||
2609 'where '||
2610 'dim.dim_name=:a '||
2611 'and hier.dim_id=dim.dim_id '||
2612 'and lvl_rel.hierarchy_id=hier.hier_id '||
2613 'and child_level.level_id=lvl_rel.child_level_id '||
2614 'and parent_level.level_id=lvl_rel.parent_level_id';
2615 if g_debug then
2616 write_to_log_file_n('Going to execute '||l_stmt||' using '||g_dim_name);
2617 end if;
2618 l_number_ltc:=1;
2619 open cv for l_stmt using g_dim_name;
2620 loop
2621 fetch cv into l_parent_ltc(l_number_ltc),l_child_ltc(l_number_ltc),l_hier(l_number_ltc);
2622 exit when cv%notfound;
2623 l_number_ltc:=l_number_ltc+1;
2624 end loop;
2625 l_number_ltc:=l_number_ltc-1;
2626 if g_debug then
2627 write_to_log_file_n('Results');
2628 for i in 1..l_number_ltc loop
2629 write_to_log_file(l_parent_ltc(i)||' '||l_child_ltc(i)||' '||l_hier(i));
2630 end loop;
2631 end if;
2632 --see what all levels are allowed and allocate them
2633 --g_number_levels
2634 g_number_ltc:=0;
2635 for i in 1..l_number_ltc loop
2636 if EDW_OWB_COLLECTION_UTIL.value_in_table(g_levels,g_number_levels,l_parent_ltc(i)) and
2637 EDW_OWB_COLLECTION_UTIL.value_in_table(g_levels,g_number_levels,l_child_ltc(i)) then
2638 g_number_ltc:=g_number_ltc+1;
2639 g_parent_ltc(g_number_ltc):=l_parent_ltc(i);
2640 g_child_ltc(g_number_ltc):=l_child_ltc(i);
2641 g_hier(g_number_ltc):=l_hier(i);
2642 end if;
2643 end loop;
2644 if g_debug then
2645 write_to_log_file_n('After considering the included levels turned ON');
2646 for i in 1..g_number_ltc loop
2647 write_to_log_file(g_parent_ltc(i)||' '||g_child_ltc(i)||' '||g_hier(i));
2648 end loop;
2649 end if;
2650 g_number_distinct_hier:=0;
2651 for i in 1..g_number_ltc loop
2652 if EDW_OWB_COLLECTION_UTIL.value_in_table(g_distinct_hier,g_number_distinct_hier,g_hier(i))=false then
2653 g_number_distinct_hier:=g_number_distinct_hier+1;
2654 g_distinct_hier(g_number_distinct_hier):=g_hier(i);
2655 end if;
2656 end loop;
2657 if g_debug then
2658 write_to_log_file_n('Distinct list of hierarchies');
2659 for i in 1..g_number_distinct_hier loop
2660 write_to_log_file(g_distinct_hier(i));
2661 end loop;
2662 end if;
2663 --g_levels
2664 l_top_level:=g_level_order(1);
2665 if g_debug then
2666 write_to_log_file_n('Top level='||l_top_level);
2667 end if;
2668 for i in 1..g_number_levels loop
2669 if g_levels(i)<>l_top_level then
2670 l_hier_hold:=null;
2671 l_child_hold:=null;
2672 for j in 1..g_number_ltc loop
2673 if g_parent_ltc(j)=g_levels(i) then
2674 if l_hier_hold is null then
2675 l_hier_hold:=g_hier(j);
2676 l_child_hold:=g_child_ltc(j);
2677 else
2678 if g_hier(j)<>l_hier_hold and g_child_ltc(j)<>l_child_hold then
2679 g_number_diamond_level:=g_number_diamond_level+1;
2680 g_diamond_level(g_number_diamond_level):=g_levels(i);
2681 exit;
2682 end if;
2683 end if;
2684 end if;
2685 end loop;
2686 end if;
2690 for i in 1..g_number_diamond_level loop
2687 end loop;
2688 if g_debug then
2689 write_to_log_file_n('The diamond top levels');
2691 write_to_log_file(g_diamond_level(i));
2692 end loop;
2693 end if;
2694 return true;
2695 Exception when others then
2696 g_status_message:=sqlerrm;
2697 g_status:=false;
2698 write_to_log_file_n(g_status_message);
2699 return false;
2700 End;
2701
2702 function get_diamond_tops(p_parent_level varchar2,p_diamond_tops out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2703 p_number_diamond_tops out NOCOPY number) return boolean is
2704 Begin
2705 p_number_diamond_tops:=0;
2706 for i in 1..g_number_diamond_level loop
2707 for j in 1..g_number_final loop
2708 if g_diamond_level(i)=g_final_levels(j) and p_parent_level=g_final_child_levels(j) then
2709 p_number_diamond_tops:=p_number_diamond_tops+1;
2710 p_diamond_tops(p_number_diamond_tops):=g_diamond_level(i);
2711 exit;
2712 end if;
2713 end loop;
2714 end loop;
2715 if g_debug then
2716 write_to_log_file_n('The diamond tops for '||p_parent_level);
2717 for i in 1..p_number_diamond_tops loop
2718 write_to_log_file(p_diamond_tops(i));
2719 end loop;
2720 end if;
2721 return true;
2722 Exception when others then
2723 g_status_message:=sqlerrm;
2724 g_status:=false;
2725 write_to_log_file_n(g_status_message);
2726 return false;
2727 End;
2728
2729 function get_level_for_fk(p_child_level varchar2,p_fk varchar2) return varchar2 is
2730 Begin
2731 for i in 1..g_number_final loop
2732 if p_child_level=g_final_child_levels(i) and p_fk=g_final_fk(i) then
2733 return g_final_next_parent(i);
2734 end if;
2735 end loop;
2736 return null;
2737 Exception when others then
2738 g_status_message:=sqlerrm;
2739 g_status:=false;
2740 write_to_log_file_n(g_status_message);
2741 return null;
2742 End;
2743
2744 function is_below_diamond_top(p_other_fk_level varchar2,p_diamond_tops varchar2) return boolean is
2745 Begin
2746 if p_other_fk_level=p_diamond_tops then
2747 return true;
2748 end if;
2749 for i in 1..g_number_final loop
2750 if g_final_levels(i)=p_diamond_tops and g_final_child_levels(i)=p_other_fk_level then
2751 return true;
2752 end if;
2753 end loop;
2754 return false;
2755 Exception when others then
2756 g_status_message:=sqlerrm;
2757 g_status:=false;
2758 write_to_log_file_n(g_status_message);
2759 return false;
2760 End;
2761
2762 function get_way_to_child(p_parent_level varchar2,p_child_level varchar2,
2763 p_parent_level_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2764 p_child_level_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2765 p_parent_pk_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2766 p_child_fk_order out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType,
2767 p_number_level_order out NOCOPY number) return boolean is
2768 l_cost EDW_OWB_COLLECTION_UTIL.numberTableType;
2769 l_parent varchar2(200);
2770 l_child varchar2(200);
2771 l_found boolean;
2772 l_level_in_hier EDW_OWB_COLLECTION_UTIL.booleanTableType;--both parent and child in the hier
2773 l_parent_found boolean;
2774 l_child_found boolean;
2775 l_min number;
2776 l_min_hier number;
2777 Begin
2778 if g_debug then
2779 write_to_log_file_n('In get_way_to_child');
2780 end if;
2781 for i in 1..g_number_distinct_hier loop
2782 l_level_in_hier(i):=false;
2783 l_parent_found:=false;
2784 l_child_found:=false;
2785 for j in 1..g_number_ltc loop
2786 if g_distinct_hier(i)=g_hier(j) and g_parent_ltc(j)=p_parent_level then
2787 l_parent_found:=true;
2788 end if;
2789 if g_distinct_hier(i)=g_hier(j) and g_child_ltc(j)=p_child_level then
2790 l_child_found:=true;
2791 end if;
2792 end loop;
2793 if l_parent_found and l_child_found then
2794 l_level_in_hier(i):=true;
2795 end if;
2796 end loop;
2797 if g_debug then
2798 write_to_log_file_n('Hierarchies that have both the parent '||p_parent_level||' and child '||p_child_level);
2799 for i in 1..g_number_distinct_hier loop
2800 if l_level_in_hier(i) then
2801 write_to_log_file(g_distinct_hier(i));
2802 end if;
2803 end loop;
2804 end if;
2805 for i in 1..g_number_distinct_hier loop
2806 l_cost(i):=-1;
2807 if l_level_in_hier(i) then
2808 l_parent:=p_parent_level;
2809 l_cost(i):=0;
2810 l_found:=false;
2811 loop
2812 for j in 1..g_number_ltc loop
2813 if g_parent_ltc(j)=l_parent and g_hier(j)=g_distinct_hier(i) then
2814 l_parent:=g_child_ltc(j);
2815 l_cost(i):=l_cost(i)+1;
2816 if l_parent=p_child_level then
2817 l_found:=true;
2818 end if;
2819 exit;
2820 end if;
2821 end loop;
2822 if l_found then
2823 exit;
2824 end if;
2825 end loop;
2826 end if;
2827 end loop;
2828 if g_debug then
2829 write_to_log_file_n('The cost for each hier');
2830 for i in 1..g_number_distinct_hier loop
2834 --find the min cost
2831 write_to_log_file(g_distinct_hier(i)||'='||l_cost(i));
2832 end loop;
2833 end if;
2835 l_min:=1000000;
2836 l_min_hier:=0;
2837 for i in 1..g_number_distinct_hier loop
2838 if l_level_in_hier(i) then
2839 if l_min>l_cost(i) then
2840 l_min:=l_cost(i);
2841 l_min_hier:=i;
2842 end if;
2843 end if;
2844 end loop;
2845 if l_min_hier=0 then
2846 g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_NO_MIN_COST_HIER');
2847 g_status:=false;
2848 write_to_log_file_n(g_status_message);
2849 return false;
2850 end if;
2851 if g_debug then
2852 write_to_log_file_n('The min cost and hier '||l_min||' and '||g_distinct_hier(l_min_hier));
2853 end if;
2854 p_number_level_order:=0;
2855 l_parent:=p_parent_level;
2856 l_found:=false;
2857 loop
2858 for i in 1..g_number_ltc loop
2859 if g_parent_ltc(i)=l_parent and g_hier(i)=g_distinct_hier(l_min_hier) then
2860 l_child:=g_child_ltc(i);
2861 for j in 1..g_number_final loop
2862 if g_final_next_parent(j)=l_parent and g_final_child_levels(j)=l_child then
2863 p_number_level_order:=p_number_level_order+1;
2864 p_parent_level_order(p_number_level_order):=l_parent;
2865 p_child_level_order(p_number_level_order):=l_child;
2866 p_parent_pk_order(p_number_level_order):=g_final_next_pk(j);
2867 p_child_fk_order(p_number_level_order):=g_final_fk(j);
2868 exit;
2869 end if;
2870 end loop;
2871 l_parent:=g_child_ltc(i);
2872 if l_parent=p_child_level then
2873 l_found:=true;
2874 end if;
2875 exit;
2876 end if;
2877 end loop;
2878 if l_found then
2879 exit;
2880 end if;
2881 end loop;
2882 if g_debug then
2883 write_to_log_file_n('The way to the child level');
2884 for i in 1..p_number_level_order loop
2885 write_to_log_file(p_parent_level_order(i)||'('||p_parent_pk_order(i)||') '||p_child_level_order(i)||
2886 '('||p_child_fk_order(i)||')');
2887 end loop;
2888 end if;
2889 return true;
2890 Exception when others then
2891 g_status_message:=sqlerrm;
2892 g_status:=false;
2893 write_to_log_file_n(g_status_message);
2894 return false;
2895 End;
2896
2897 function create_child_dia_fk_table(
2898 p_parent_level varchar2,
2899 p_child_level varchar2,
2900 p_parent_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2901 p_child_level_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2902 p_parent_pk_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2903 p_child_fk_order EDW_OWB_COLLECTION_UTIL.varcharTableType,
2904 p_number_level_order number,
2905 p_ilog varchar2,
2906 p_other_fks EDW_OWB_COLLECTION_UTIL.varcharTableType,
2907 p_below_diamond_flag EDW_OWB_COLLECTION_UTIL.booleanTableType,
2908 p_number_other_fks number,
2909 p_diamond_fk_table out NOCOPY varchar2) return boolean is
2910 l_diamond_table varchar2(200);
2911 l_max_rownum_table varchar2(200);
2912 l_stmt varchar2(32000);
2913 Begin
2914 if g_debug then
2915 write_to_log_file_n('In create_child_dia_fk_table ');
2916 end if;
2917 l_diamond_table:=g_bis_owner||'.'||p_parent_level||'DK';
2918 l_max_rownum_table:=g_bis_owner||'.'||p_parent_level||'DM';
2919 p_diamond_fk_table:=g_bis_owner||'.'||p_parent_level||'DF';
2920 l_stmt:='create table '||l_diamond_table||' tablespace '||g_op_table_space;
2921 if g_parallel is not null then
2922 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2923 end if;
2924 l_stmt:=l_stmt||' as select /*+ordered*/ ';
2925 if g_parallel is not null then
2926 l_stmt:=l_stmt||'/*+parallel('||p_child_level||','||g_parallel||')*/ ';
2927 end if;
2928 l_stmt:=l_stmt||'rownum row_num,'||p_parent_level||'.rowid row_id';
2929 for i in 1..p_number_other_fks loop
2930 if p_below_diamond_flag(i) then
2931 l_stmt:=l_stmt||','||p_child_level||'.'||p_other_fks(i);
2932 end if;
2933 end loop;
2934 l_stmt:=l_stmt||','||p_child_level||'.'||p_child_fk_order(p_number_level_order);
2935 l_stmt:=l_stmt||' from '||p_ilog;
2936 for i in 1..p_number_level_order loop
2937 l_stmt:=l_stmt||','||p_parent_level_order(i);
2938 end loop;
2939 l_stmt:=l_stmt||','||p_child_level||' where ';
2940 for i in 1..p_number_level_order loop
2941 l_stmt:=l_stmt||p_parent_level_order(i)||'.'||p_parent_pk_order(i)||'='||p_child_level_order(i)||'.'||
2942 p_child_fk_order(i)||' and ';
2943 end loop;
2944 l_stmt:=l_stmt||p_ilog||'.row_id='||p_parent_level||'.rowid';
2945 if EDW_OWB_COLLECTION_UTIL.drop_table(l_diamond_table)=false then
2946 null;
2947 end if;
2948 if g_debug then
2949 write_to_log_file_n('Going to execute '||l_stmt||get_time);
2950 end if;
2951 execute immediate l_stmt;
2952 if g_debug then
2953 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
2954 end if;
2955 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_diamond_table,instr(l_diamond_table,'.')+1,
2956 length(l_diamond_table)),substr(l_diamond_table,1,instr(l_diamond_table,'.')-1));
2957 --get the max of rownum
2958 l_stmt:='create table '||l_max_rownum_table||' tablespace '||g_op_table_space;
2962 l_stmt:=l_stmt||' as select max(row_num) row_num,'||p_child_fk_order(p_number_level_order)||' from '||
2959 if g_parallel is not null then
2960 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2961 end if;
2963 l_diamond_table||' group by '||p_child_fk_order(p_number_level_order);
2964 if EDW_OWB_COLLECTION_UTIL.drop_table(l_max_rownum_table)=false then
2965 null;
2966 end if;
2967 if g_debug then
2968 write_to_log_file_n('Going to execute '||l_stmt||get_time);
2969 end if;
2970 execute immediate l_stmt;
2971 if g_debug then
2972 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
2973 end if;
2974 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_max_rownum_table,instr(l_max_rownum_table,'.')+1,
2975 length(l_max_rownum_table)),substr(l_max_rownum_table,1,instr(l_max_rownum_table,'.')-1));
2976 l_stmt:='create table '||p_diamond_fk_table||' tablespace '||g_op_table_space;
2977 if g_parallel is not null then
2978 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2979 end if;
2980 l_stmt:=l_stmt||' as select /*+ordered*/ A.row_id';
2981 for i in 1..p_number_other_fks loop
2982 if p_below_diamond_flag(i) then
2983 l_stmt:=l_stmt||',A.'||p_other_fks(i);
2984 end if;
2985 end loop;
2986 l_stmt:=l_stmt||' from '||l_max_rownum_table||' B,'||l_diamond_table||' A where A.row_num=B.row_num and '||
2987 'A.'||p_child_fk_order(p_number_level_order)||'=B.'||p_child_fk_order(p_number_level_order);
2988 if EDW_OWB_COLLECTION_UTIL.drop_table(p_diamond_fk_table)=false then
2989 null;
2990 end if;
2991 if g_debug then
2992 write_to_log_file_n('Going to execute '||l_stmt||get_time);
2993 end if;
2994 execute immediate l_stmt;
2995 if g_debug then
2996 write_to_log_file_n('Created with '||sql%rowcount||' rows '||get_time);
2997 end if;
2998 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(p_diamond_fk_table,instr(p_diamond_fk_table,'.')+1,
2999 length(p_diamond_fk_table)),substr(p_diamond_fk_table,1,instr(p_diamond_fk_table,'.')-1));
3000 if EDW_OWB_COLLECTION_UTIL.drop_table(l_diamond_table)=false then
3001 null;
3002 end if;
3003 if EDW_OWB_COLLECTION_UTIL.drop_table(l_max_rownum_table)=false then
3004 null;
3005 end if;
3006 return true;
3007 Exception when others then
3008 g_status_message:=sqlerrm;
3009 g_status:=false;
3010 write_to_log_file_n(g_status_message);
3011 return false;
3012 End;
3013
3014 function merge_all_ilog_tables return boolean is
3015 Begin
3016 for i in 1..g_number_levels loop
3017 if EDW_OWB_COLLECTION_UTIL.merge_all_ilog_tables(
3018 g_level_ilog_name(i),
3019 g_level_ilog_name(i),
3020 g_level_ilog_name(i)||'A',
3021 'IL',
3022 g_op_table_space,
3023 g_bis_owner,
3024 g_parallel)=false then
3025 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
3026 return false;
3027 end if;
3028 if EDW_OWB_COLLECTION_UTIL.check_table(g_level_ilog_name(i)||'A') then
3029 g_level_ilog(i):=g_level_ilog_name(i)||'A';
3030 else
3031 g_level_ilog(i):=g_level_ilog_name(i);
3032 end if;
3033 end loop;
3034 return true;
3035 Exception when others then
3036 g_status_message:=sqlerrm;
3037 g_status:=false;
3038 write_to_log_file_n('Error in merge_all_ilog_tables '||g_status_message);
3039 return false;
3040 End;
3041
3042 function check_level_for_column return boolean is
3043 l_col varchar2(200);
3044 l_owner varchar2(80);
3045 Begin
3046 l_col:='PUSHED_DOWN_FLAG';
3047 for i in 1..g_number_levels loop
3048 l_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(g_levels(i));
3049 if EDW_OWB_COLLECTION_UTIL.check_table_column(g_levels(i),l_owner,l_col)=false then
3050 g_stmt:='alter table '||l_owner||'.'||g_levels(i)||' add ('||l_col||' varchar2(10))';
3051 if g_debug then
3052 write_to_log_file_n(g_stmt);
3053 end if;
3054 execute immediate g_stmt;
3055 end if;
3056 end loop;
3057 return true;
3058 Exception when others then
3059 g_status_message:=sqlerrm;
3060 g_status:=false;
3061 write_to_log_file_n('Error in check_level_for_column '||g_status_message);
3062 return false;
3063 End;
3064
3065 function put_rownum_in_ilog_table(p_index number) return boolean is
3066 l_ilog_table varchar2(80);
3067 Begin
3068 if g_debug then
3069 write_to_log_file_n('In put_rownum_in_ilog_table for '||g_levels(p_index));
3070 end if;
3071 l_ilog_table:=g_level_ilog(p_index);
3072 if substr(g_level_ilog(p_index),length(g_level_ilog(p_index)),1)='A' then
3073 g_level_ilog(p_index):=substr(g_level_ilog(p_index),1,length(g_level_ilog(p_index))-1);
3074 else
3075 g_level_ilog(p_index):=g_level_ilog(p_index)||'A';
3076 end if;
3077 if EDW_OWB_COLLECTION_UTIL.put_rownum_in_ilog_table(
3078 g_level_ilog(p_index),
3079 l_ilog_table,
3080 g_op_table_space,
3081 g_parallel)=false then
3082 return false;
3083 end if;
3084 return true;
3085 Exception when others then
3086 g_status_message:=sqlerrm;
3087 write_to_log_file_n('Error in put_rownum_in_ilog_table '||g_status_message);
3088 return false;
3089 End;
3090
3091 function create_ilog_from_main(p_low_end number,p_high_end number) return boolean is
3095 write_to_log_file_n('In create_ilog_from_main');
3092 l_ilog_number number;
3093 Begin
3094 if g_debug then
3096 end if;
3097 for i in 1..g_number_levels loop
3098 if g_level_consider(i) and g_levels(i)<>g_lowest_level then
3099 if EDW_OWB_COLLECTION_UTIL.make_ilog_from_main_ilog(
3100 g_level_ilog(i),
3101 g_level_ilog_main(i),
3102 p_low_end,
3103 p_high_end,
3104 g_op_table_space,
3105 g_bis_owner,
3106 g_parallel,
3107 l_ilog_number)=false then
3108 return false;
3109 end if;
3110 if EDW_OWB_COLLECTION_UTIL.does_table_have_data(g_level_ilog(i),' status=1 ')=2 then
3111 g_skip_ilog_update(i):=true;
3112 end if;
3113 end if;
3114 end loop;
3115 return true;
3116 Exception when others then
3117 g_status_message:=sqlerrm;
3118 write_to_log_file_n('Error in create_ilog_from_main '||g_status_message);
3119 return false;
3120 End;
3121
3122 function set_session_parameters return boolean is
3123 Begin
3124 if EDW_OWB_COLLECTION_UTIL.set_session_parameters(g_hash_area_size,g_sort_area_size,
3125 g_trace,g_parallel)=false then
3126 return false;
3127 end if;
3128 return true;
3129 Exception when others then
3130 g_status_message:=sqlerrm;
3131 write_to_log_file_n('Error in set_session_parameters '||g_status_message);
3132 return false;
3133 End;
3134
3135 function read_options_table(p_input_table varchar2) return boolean is
3136 TYPE CurTyp IS REF CURSOR;
3137 cv CurTyp;
3138 l_level_table varchar2(80);
3139 l_level_child_table varchar2(80);
3140 l_debug varchar2(10);
3141 l_full_refresh varchar2(10);
3142 l_trace varchar2(10);
3143 l_read_cfig_options varchar2(10);
3144 l_level_consider varchar2(10);
3145 l_level_full_insert varchar2(10);
3146 l_run number;
3147 Begin
3148 write_to_log_file_n('In read_options_table '||p_input_table);
3149 l_level_table:=p_input_table||'_LT';
3150 l_level_child_table:=p_input_table||'_LC';
3151 g_stmt:='select '||
3152 'dim_id'||
3153 ',debug'||
3154 ',parallel'||
3155 ',collection_size'||
3156 ',bis_owner'||
3157 ',table_owner'||
3158 ',full_refresh'||
3159 ',forall_size'||
3160 ',update_type'||
3161 ',load_pk'||
3162 ',op_table_space'||
3163 ',rollback'||
3164 ',max_threads'||
3165 ',min_job_load_size'||
3166 ',sleep_time'||
3167 ',hash_area_size'||
3168 ',sort_area_size'||
3169 ',trace'||
3170 ',read_cfig_options'||
3171 ',join_nl_percentage'||
3172 ' from '||p_input_table;
3173 if g_debug then
3174 write_to_log_file_n(g_stmt);
3175 end if;
3176 open cv for g_stmt;
3177 fetch cv into
3178 g_dim_id
3179 ,l_debug
3180 ,g_parallel
3181 ,g_collection_size
3182 ,g_bis_owner
3183 ,g_table_owner
3184 ,l_full_refresh
3185 ,g_forall_size
3186 ,g_update_type
3187 ,g_load_pk
3188 ,g_op_table_space
3189 ,g_rollback
3190 ,g_max_threads
3191 ,g_min_job_load_size
3192 ,g_sleep_time
3193 ,g_hash_area_size
3194 ,g_sort_area_size
3195 ,l_trace
3196 ,l_read_cfig_options
3197 ,g_join_nl_percentage;
3198 if l_debug='Y' then
3199 write_to_log_file('g_dim_id='||g_dim_id);
3200 write_to_log_file('l_debug='||l_debug);
3201 write_to_log_file('g_parallel='||g_parallel);
3202 write_to_log_file('g_collection_size='||g_collection_size);
3203 write_to_log_file('g_bis_owner='||g_bis_owner);
3204 write_to_log_file('g_table_owner='||g_table_owner);
3205 write_to_log_file('l_full_refresh='||l_full_refresh);
3206 write_to_log_file('g_forall_size='||g_forall_size);
3207 write_to_log_file('g_update_type='||g_update_type);
3208 write_to_log_file('g_load_pk='||g_load_pk);
3209 write_to_log_file('g_op_table_space='||g_op_table_space);
3210 write_to_log_file('g_rollback='||g_rollback);
3211 write_to_log_file('g_max_threads='||g_max_threads);
3212 write_to_log_file('g_min_job_load_size='||g_min_job_load_size);
3213 write_to_log_file('g_sleep_time='||g_sleep_time);
3214 write_to_log_file('g_hash_area_size='||g_hash_area_size);
3215 write_to_log_file('g_sort_area_size='||g_sort_area_size);
3216 write_to_log_file('l_trace='||l_trace);
3217 write_to_log_file('l_read_cfig_options='||l_read_cfig_options);
3218 write_to_log_file('g_join_nl_percentage='||g_join_nl_percentage);
3219 end if;
3220 g_debug:=false;
3221 g_full_refresh:=false;
3222 g_trace:=false;
3223 g_read_cfig_options:=false;
3224 if l_debug='Y' then
3225 g_debug:=true;
3226 end if;
3227 if l_full_refresh='Y' then
3228 g_full_refresh:=true;
3229 end if;
3230 if l_trace='Y' then
3231 g_trace:=true;
3232 end if;
3233 if l_read_cfig_options='Y' then
3234 g_read_cfig_options:=true;
3235 end if;
3236 g_stmt:='select '||
3237 'levels'||
3238 ',child_level_number'||
3239 ',level_order'||
3240 ',level_snapshot_logs'||
3241 ',level_ilog'||
3242 ',level_consider'||
3243 ',level_full_insert '||
3244 ' from '||l_level_table||' order by level_number';
3245 if g_debug then
3246 write_to_log_file_n(g_stmt);
3247 end if;
3248 g_number_levels:=1;
3249 open cv for g_stmt;
3250 loop
3251 fetch cv into
3252 g_levels(g_number_levels)
3256 ,g_level_ilog_main(g_number_levels)
3253 ,g_child_level_number(g_number_levels)
3254 ,g_level_order(g_number_levels)
3255 ,g_level_snapshot_logs(g_number_levels)
3257 ,l_level_consider
3258 ,l_level_full_insert;
3259 exit when cv%notfound;
3260 g_level_consider(g_number_levels):=false;
3261 g_level_full_insert(g_number_levels):=false;
3262 if l_level_consider='Y' then
3263 g_level_consider(g_number_levels):=true;
3264 end if;
3265 if l_level_full_insert='Y' then
3266 g_level_full_insert(g_number_levels):=true;
3267 end if;
3268 g_number_levels:=g_number_levels+1;
3269 end loop;
3270 g_number_levels:=g_number_levels-1;
3271 close cv;
3272 if g_debug then
3273 write_to_log_file_n('The levels, snp logs etc');
3274 for i in 1..g_number_levels loop
3275 write_to_log_file(g_levels(i)||' '||g_child_level_number(i)||' '||g_level_order(i)||' '||
3276 g_level_snapshot_logs(i)||' '||g_level_ilog_main(i));
3277 if g_level_consider(i) then
3278 write_to_log_file('g_level_consider('||i||') TRUE');
3279 else
3280 write_to_log_file('g_level_consider('||i||') FALSE');
3281 end if;
3282 if g_level_full_insert(i) then
3283 write_to_log_file('g_level_full_insert('||i||') TRUE');
3284 else
3285 write_to_log_file('g_level_full_insert('||i||') FALSE');
3286 end if;
3287 end loop;
3288 end if;
3289 l_run:=1;
3290 g_stmt:='select '||
3291 'child_levels'||
3292 ',child_fk'||
3293 ',parent_pk from '||l_level_child_table||' order by run_number';
3294 open cv for g_stmt;
3295 loop
3296 fetch cv into g_child_levels(l_run),g_child_fk(l_run),g_parent_pk(l_run);
3297 exit when cv%notfound;
3298 l_run:=l_run+1;
3299 end loop;
3300 close cv;
3301 l_run:=l_run-1;
3302 if g_debug then
3303 write_to_log_file_n('The child levels and fk and pk');
3304 for i in 1..l_run loop
3305 write_to_log_file(g_child_levels(i)||' '||g_child_fk(i)||' '||g_parent_pk(i));
3306 end loop;
3307 end if;
3308 return true;
3309 Exception when others then
3310 g_status_message:=sqlerrm;
3311 write_to_log_file_n('Error in read_options_table '||g_status_message);
3312 return false;
3313 End;
3314
3315 function drop_input_tables(p_table_name varchar2) return boolean is
3316 Begin
3317 if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name)=false then
3318 null;
3319 end if;
3320 if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name||'_LT')=false then
3321 null;
3322 end if;
3323 if EDW_OWB_COLLECTION_UTIL.drop_table(p_table_name||'_LC')=false then
3324 null;
3325 end if;
3326 return true;
3327 Exception when others then
3328 g_status_message:=sqlerrm;
3329 write_to_log_file_n('Error in drop_input_tables '||g_status_message);
3330 return false;
3331 End;
3332
3333 function create_conc_program(
3334 p_temp_conc_name varchar2,
3335 p_temp_conc_short_name varchar2,
3336 p_temp_exe_name varchar2,
3337 p_bis_short_name varchar2
3338 ) return boolean is
3339 l_exe_file_name varchar2(200);
3340 l_parameter EDW_OWB_COLLECTION_UTIL.varcharTableType;
3341 l_parameter_value_set EDW_OWB_COLLECTION_UTIL.varcharTableType;
3342 l_number_parameters number;
3343 Begin
3344 l_exe_file_name:='EDW_PUSH_DOWN_DIMS.PUSH_DOWN_ALL_LEVELS';
3345 l_parameter(1):='p_dim_name';
3346 l_parameter_value_set(1):='FND_CHAR240';
3347 l_parameter(2):='p_log_file';
3348 l_parameter_value_set(2):='FND_CHAR240';
3349 l_parameter(3):='p_input_table';
3350 l_parameter_value_set(3):='FND_CHAR240';
3351 l_parameter(4):='p_job_id';
3352 l_parameter_value_set(4):='FND_NUMBER';
3353 l_parameter(5):='p_ok_low_end';
3354 l_parameter_value_set(5):='FND_NUMBER';
3355 l_parameter(6):='p_ok_high_end';
3356 l_parameter_value_set(6):='FND_NUMBER';
3357 l_parameter(7):='p_job_status_table';
3358 l_parameter_value_set(7):='FND_CHAR240';
3359 l_number_parameters:=7;
3360 if EDW_OWB_COLLECTION_UTIL.create_conc_program(
3361 p_temp_conc_name,
3362 p_temp_conc_short_name,
3363 p_temp_exe_name,
3364 l_exe_file_name,
3365 p_bis_short_name,
3366 l_parameter,
3367 l_parameter_value_set,
3368 l_number_parameters
3369 )=false then
3370 return false;
3371 end if;
3372 return true;
3373 Exception when others then
3374 g_status_message:=sqlerrm;
3375 write_to_log_file_n('Error in create_conc_program '||g_status_message);
3376 return false;
3377 End;
3378
3379 END EDW_PUSH_DOWN_DIMS;
[Home] [Help]