1 package body BSC_IM_UTILS AS
2 /*$Header: BSCOLUTB.pls 120.9 2006/07/10 07:54:19 rkumar ship $*/
3
4 -- Start of apis added by arun for bug 3876730
5 -- from s or sb table name, parse the indicator number
6 -- assumed to be of the form BSC_S_3005_0_0_1 or BSC_S_4885_0_2_PT
7 -- or BSC_SB_4885_0_2_1
8 FUNCTION getParsedIndicNumber(p_Stable IN VARCHAR2) RETURN VARCHAR2
9 IS
10 cursor cIndicator is
11 select substr(p_Stable, instr(p_Stable, '_',1, 2)+1,
12 instr(p_Stable, '_', 1, 3)- (instr(p_Stable, '_',1, 2)+1))
13 from dual;
14 l_indicator NUMBER;
15 BEGIN
16 OPEN cIndicator;
17 FETCH cIndicator INTO l_indicator;
18 CLOSE cIndicator;
19 return l_indicator;
20 exception when others then
21 write_to_log_file('Exception in getParsedIndicNumber for table name = '||p_Stable);
22 raise;
23 END;
24
25 -- given a MV's fk, tell
26 -- whether zero code is needed or not
27
28 function needs_zero_code_mv(
29 p_mv_name varchar2,
30 p_kpi varchar2,
31 p_fk varchar2)
32 return boolean is
33 --------------------------------------------------------------------
34 cursor cZeroCode is
35 select count(1)from bsc_db_calculations calc
36 where calc.calculation_type = 4
37 and calc.table_name =
38 (select table_name
39 from bsc_kpi_data_tables data
40 where
41 data.indicator = p_kpi
42 and data.mv_name = p_mv_name
43 and rownum=1)
44 and calc.parameter1 = p_fk;
45 --------------------------------------------------------------------
46 l_count number;
47 begin
48 l_count := 0;
49 open cZeroCode;
50 fetch cZeroCode into l_count;
51 close cZeroCode;
52 if (l_count=0) then
53 return false;
54 else
55 return true;
56 end if;
57 exception when others then
58 write_to_log_file('Exception in needs_zero_code_mv for mv name = '||p_mv_name ||', p_kpi = '||p_kpi||', fk ='||p_fk);
59 raise;
60
61 end;
62
63 -- given a Base or Projection table's fk, tell
64 -- whether zero code is needed or not
65 function needs_zero_code_b_pt(
66 p_b_pt_table_name varchar2,
67 p_fk varchar2)
68 return boolean is
69 --------------------------------------------
70 cursor cSTableNameForB is
71 select table_name from bsc_db_tables_rels
72 where table_name like 'BSC_S%'
73 connect by source_table_name = prior table_name
74 start with source_table_name = p_b_pt_table_name;
75 --------------------------------------------
76 cursor cSTableNameForPT(p_kpi number) is
77 select projection_data from bsc_kpi_data_tables
78 where indicator = p_kpi
79 and projection_data = p_b_pt_table_name;
80 --------------------------------------------
81 cursor cZeroCode(p_sTable IN VARCHAR2) is
82 select count(1) from bsc_db_calculations calc
83 where calc.calculation_type = 4
84 and calc.table_name = p_sTable
85 and calc.parameter1 = p_fk;
86 --------------------------------------------
87 l_count number;
88 l_sTable VARCHAR2(100);
89 --------------------------------------------
90 l_indicator number;
91 begin
92 l_count := 0;
93
94 if (p_b_pt_table_name like 'BSC_S%PT') then -- pt table
95 l_indicator := getParsedIndicNumber(p_b_pt_table_name);
96 open cSTableNameForPT(l_indicator);
97 fetch cSTableNameForPT into l_sTable;
98 close cSTableNameForPT;
99 else
100 open cSTableNameForB;
101 fetch cSTableNameForB into l_sTable;
102 close cSTableNameForB;
103 end if;
104
105 IF (l_sTable is null) then
106 return false;
107 END IF;
108
109 open cZeroCode(l_sTable);
110 fetch cZeroCode into l_count;
111 close cZeroCode;
112
113 if (l_count=0) then
114 return false;
115 else
116 return true;
117 end if;
118 exception when others then
119 write_to_log_file('Exception in reorder_index'||sqlerrm);
120 write_to_log_file('table name = '||p_b_pt_table_name||', fk = '||p_fk);
121 raise;
122 end;
123
124 -- given a list of columns for the index
125 -- reorder them for bug 3876730
126 function reorder_index(p_b_pt_table_name IN varchar2, colColumns IN varchar_tabletype) return varchar2 is
127 l_periodicity_id_exists boolean;
128 l_year_exists boolean;
129 l_period_exists boolean;
130 l_type_exists boolean;
131 l_stmt varchar2(1000);
132 l_zero_code_cols varchar2(1000) ;
133 i number :=0;
134 begin
135
136 i := colColumns.first;
137 loop
138 EXIT WHEN colColumns.count = 0;
139 if (colColumns(i) = 'PERIODICITY_ID') then
140 l_periodicity_id_exists := true;
141 elsif (colColumns(i) = 'YEAR') then
142 l_year_exists := true;
143 elsif (colColumns(i) = 'PERIOD') then
144 l_period_exists := true;
145 elsif (colColumns(i) = 'TYPE') then
146 l_type_exists := true;
147 end if;
148 exit when i=colColumns.last;
149 i := colColumns.next(i);
150 end loop;
151
152 -- bug 3876730, add in the following order
153 -- 'PERIODICITY_ID', 'YEAR', 'PERIOD', 'TYPE'
154 l_stmt := null;
155 if (l_periodicity_id_exists) then
156 l_stmt := l_stmt||'PERIODICITY_ID,';
157 end if;
158 if (l_year_exists) then
159 l_stmt := l_stmt||'YEAR,';
160 end if;
161 if (l_period_exists) then
162 l_stmt := l_stmt||'PERIOD,';
163 end if;
164 if (l_type_exists) then
165 l_stmt := l_stmt||'TYPE,';
166 end if;
167
168 l_zero_code_cols := null;
169 i := colColumns.first;
170 LOOP
171 EXIT WHEN colColumns.count = 0;
172 if (colColumns(i) not in ('PERIODICITY_ID', 'YEAR', 'PERIOD', 'TYPE')) then
173 if(BSC_IM_UTILS.needs_zero_code_b_pt(p_b_pt_table_name, colColumns(i))) then
174 l_zero_code_cols := l_zero_code_cols||colColumns(i)||',';
175 else
176 l_stmt:=l_stmt||colColumns(i)||',';
177 end if;
178 end if;
179 exit when i = colColumns.last;
180 i := colColumns.next(i);
181 end loop;
182
183 if (l_zero_code_cols is not null) then
184 l_stmt := l_stmt ||l_zero_code_cols;
185 end if;
186 if (l_stmt is not null) then
187 l_stmt := substr(l_stmt, 1, length(l_stmt)-1);
188 end if;
189
190 return l_stmt;
191 exception when others then
192 write_to_log_file('Exception in reorder_index'||sqlerrm);
193 write_to_log_file('Table name = '||p_b_pt_table_name );
194 raise;
195 end;
196
197 -- end of apis added by arun for bug 3876730
198
199 procedure open_file(p_object_name varchar2) is
200 l_dir varchar2(200);
201 Begin
202 l_dir:=null;
203 l_dir:=fnd_profile.value('UTL_FILE_LOG');
204 if l_dir is null then
205 l_dir:=fnd_profile.value('EDW_LOGFILE_DIR');
206 end if;
207 if l_dir is null then
208 l_dir:='/sqlcom/log';
209 end if;
210 FND_FILE.PUT_NAMES(p_object_name||'.log',p_object_name||'.out',l_dir);
211 Exception when others then
212 null;
213 End;
214
215 procedure write_to_file(p_type varchar2,p_message varchar2,p_new_line boolean) is
216 l_len number;
217 l_start number:=1;
218 l_end number:=1;
219 last_reached boolean:=false;
220 Begin
221 if p_message is null or p_message='' then
222 return;
223 end if;
224 l_len:=nvl(length(p_message),0);
225 if l_len <=0 then
226 return;
227 end if;
228 while true loop
229 l_end:=l_start+250;
230 if l_end >= l_len then
231 l_end:=l_len;
232 last_reached:=true;
233 end if;
234 if p_new_line then
235 if p_type='LOG' then
236 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(p_message, l_start, 250));
237 else
238 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,substr(p_message, l_start, 250));
239 end if;
240 else
241 if p_type='LOG' then
242 FND_FILE.PUT(FND_FILE.LOG,substr(p_message, l_start, 250)||' ');
243 else
244 FND_FILE.PUT(FND_FILE.OUTPUT,substr(p_message, l_start, 250)||' ');
245 end if;
246 end if;
247 l_start:=l_start+250;
248 if last_reached then
249 exit;
250 end if;
251 end loop;
252 Exception when others then
253 null;
254 End;
255
256 procedure write_to_log(p_message varchar2,p_new_line boolean) is
257 Begin
258 write_to_file('LOG',p_message,p_new_line);
259 Exception when others then
260 BSC_IM_UTILS.g_status_message:=sqlerrm;
261 null;
262 End;
263
264 procedure write_to_log_file(p_message varchar2) is
265 begin
266 write_to_log(p_message,true);
267 Exception when others then
268 null;
269 end;
270
271 procedure write_to_log_file_s(p_message varchar2) is
272 begin
273 write_to_log(p_message,false);
274 Exception when others then
275 null;
276 end;
277
278 procedure write_to_log_file_n(p_message varchar2) is
279 begin
280 write_to_log_file(' ');
281 write_to_log_file(p_message);
282 Exception when others then
283 null;
284 end;
285
286 procedure write_to_debug_n(p_message varchar2) is
287 begin
288 if g_debug then
289 write_to_log_file_n(p_message);
290 end if;
291 Exception when others then
292 null;
293 end;
294
295 procedure write_to_debug(p_message varchar2) is
296 begin
297 if g_debug then
298 write_to_log_file(p_message);
299 end if;
300 Exception when others then
301 null;
302 end;
303
304 procedure write_to_out_file(p_message varchar2) is
305 begin
306 write_to_file('OUT',p_message,true);
307 Exception when others then
308 null;
309 end;
310
311 procedure write_to_out_file_s(p_message varchar2) is
312 begin
313 write_to_file('OUT',p_message,false);
314 Exception when others then
315 null;
316 end;
317
318 procedure write_to_out_file_n(p_message varchar2) is
319 begin
320 write_to_out_file(' ');
321 write_to_out_file(p_message);
322 Exception when others then
323 null;
324 end;
325
326 function get_time return varchar2 is
327 begin
328 return ' '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');
329 Exception when others then
330 null;
331 End;
332
333 function is_column_in_object(p_object varchar2,p_column varchar2) return boolean is
334 Begin
335 g_stmt:='select '||p_column||' from '||p_object||' where rownum=1';
336 write_to_debug_n(g_stmt);
337 execute immediate g_stmt;
338 return true;
339 Exception when others then
340 if sqlcode=-00904 then
341 write_to_debug_n('Column '||p_column||' does not exist in '||p_object);
342 return false;
343 end if;
344 g_status_message:=sqlerrm;
345 write_to_log_file_n('Error in is_column_in_object '||sqlerrm);
346 return false;
347 End;
348
349 function in_array(p_table varchar_tabletype,p_number_table number,p_value varchar2) return boolean is
350 Begin
351 for i in 1..p_number_table loop
352 if lower(p_table(i))=lower(p_value) then
353 return true;
354 end if;
355 end loop;
356 return false;
357 Exception when others then
358 g_status_message:=sqlerrm;
359 write_to_log_file_n('Error in in_array '||sqlerrm);
360 return false;
361 End;
362
363 function in_array(p_table number_tabletype,p_number_table number,p_value number) return boolean is
364 Begin
365 for i in 1..p_number_table loop
366 if p_table(i)=p_value then
367 return true;
368 end if;
369 end loop;
370 return false;
371 Exception when others then
372 g_status_message:=sqlerrm;
373 write_to_log_file_n('Error in in_array '||sqlerrm);
374 return false;
375 End;
376
377 function in_array(p_table number_tabletype, p_table2 varchar_tabletype,
378 p_number_table number,p_value number,p_value2 varchar2) return boolean is
379 Begin
380 for i in 1..p_number_table loop
381 if p_table(i)=p_value and lower(p_table2(i))=lower(p_value2) then
382 return true;
383 end if;
384 end loop;
385 return false;
386 Exception when others then
387 g_status_message:=sqlerrm;
388 write_to_log_file_n('Error in in_array '||sqlerrm);
389 return false;
390 End;
391
392 function in_array(p_table number_tabletype, p_table2 number_tabletype,
393 p_number_table number,p_value number,p_value2 number) return boolean is
394 Begin
395 for i in 1..p_number_table loop
396 if p_table(i)=p_value and p_table2(i)=p_value2 then
397 return true;
398 end if;
399 end loop;
400 return false;
401 Exception when others then
402 g_status_message:=sqlerrm;
403 write_to_log_file_n('Error in in_array '||sqlerrm);
404 return false;
405 End;
406
407 function in_array(p_table varchar_tabletype, p_table2 varchar_tabletype,
408 p_number_table number,p_value varchar2,p_value2 varchar2) return boolean is
409 Begin
410 for i in 1..p_number_table loop
411 if lower(p_table(i))=lower(p_value) and lower(p_table2(i))=lower(p_value2) then
412 return true;
413 end if;
414 end loop;
415 return false;
416 Exception when others then
417 g_status_message:=sqlerrm;
418 write_to_log_file_n('Error in in_array '||sqlerrm);
419 return false;
420 End;
421
422 function in_array(p_table1 number_tabletype, p_table2 number_tabletype,
423 p_table3 number_tabletype,p_number_table number,p_value1 number,
424 p_value2 number,p_value3 number) return boolean is
425 Begin
426 for i in 1..p_number_table loop
427 if p_table1(i)=p_value1 and p_table2(i)=p_value2 and p_table3(i)=p_value3 then
428 return true;
429 end if;
430 end loop;
431 return false;
432 Exception when others then
433 g_status_message:=sqlerrm;
434 write_to_log_file_n('Error in in_array '||sqlerrm);
435 return false;
436 End;
437
438 function add_distinct_values_to_table(
439 p_table in out nocopy varchar_tabletype,
440 p_number_table in out nocopy number,
441 p_values_table varchar_tabletype,
442 p_number_values_table number,
443 p_options varchar2) return boolean is
444 l_found boolean;
445 l_table varchar_tabletype;
446 l_number_table number:=0;
447 Begin
448 for i in 1..p_number_values_table loop
449 l_found:=false;
450 for j in 1..p_number_table loop
451 if p_options = 'case' then
452 if p_table(j)=p_values_table(i) then
453 l_found:=true;
454 exit;
455 end if;
456 else
457 if lower(p_table(j))=lower(p_values_table(i)) then
458 l_found:=true;
459 exit;
460 end if;
461 end if;
462 end loop;
463 if l_found=false then
464 l_number_table:=l_number_table+1;
465 l_table(l_number_table):=p_values_table(i);
466 end if;
467 end loop;
468 if g_debug then
469 write_to_debug_n('The additional values');
470 for i in 1..l_number_table loop
471 write_to_debug(l_table(i));
472 end loop;
473 end if;
474 for i in 1..l_number_table loop
475 p_number_table:=p_number_table+1;
476 p_table(p_number_table):=l_table(i);
477 end loop;
478 return true;
479 Exception when others then
480 g_status_message:=sqlerrm;
481 write_to_log_file_n('Error in add_distinct_values_to_table '||sqlerrm);
482 return false;
483 End;
484
485 function add_distinct_values_to_table(
486 p_table in out nocopy varchar_tabletype,
487 p_number_table in out nocopy number,
488 p_value varchar2,
489 p_options varchar2) return boolean is
490 l_found boolean:=false;
491 Begin
492 for j in 1..p_number_table loop
493 if p_options = 'case' then
494 if p_table(j)=p_value then
495 l_found:=true;
496 exit;
497 end if;
498 else
499 if lower(p_table(j))=lower(p_value) then
500 l_found:=true;
501 exit;
502 end if;
503 end if;
504 end loop;
505 if l_found=false then
506 write_to_debug_n('Adding the value '||p_value);
507 p_number_table:=p_number_table+1;
508 p_table(p_number_table):=p_value;
509 end if;
510 return true;
511 Exception when others then
512 g_status_message:=sqlerrm;
513 write_to_log_file_n('Error in add_distinct_values_to_table '||sqlerrm);
514 return false;
515 End;
516
517 procedure set_globals(p_debug boolean) is
518 Begin
519 g_debug:=p_debug;
520 Exception when others then
521 BSC_IM_UTILS.g_status_message:=sqlerrm;
522 null;
523 End;
524
525 function get_db_user(
526 p_product varchar2,
527 p_db_user out nocopy varchar2
528 )return boolean is
529 l_dummy1 varchar2(2000);
530 l_dummy2 varchar2(2000);
531 l_db_user varchar2(200);
532 Begin
533
534 p_db_user:=bsc_apps.get_user_schema(p_product);
535 return true;
536 Exception when others then
537 g_status_message:=sqlerrm;
538 write_to_log_file_n('Error in get_db_user '||sqlerrm);
539 return false;
540 End;
541
542 function read_global return number is
543 Begin
544 if g_id is null then
545 g_id:=1;
546 end if;
547 g_id:=g_id+1;
548 return g_id;
549 Exception when others then
550 BSC_IM_UTILS.g_status_message:=sqlerrm;
551 write_to_log_file_n('Error in read_global '||sqlerrm);
552 return -1;
553 End;
554
555 function read_sequence(p_seq varchar) return number is
556 TYPE CurTyp IS REF CURSOR;
557 cv CurTyp;
558 l_id number;
559 Begin
560 g_stmt:='select '||p_seq||'.nextval from dual';
561 write_to_debug_n(g_stmt);
562 execute immediate g_stmt into l_id;
563 return l_id;
564 Exception when others then
565 BSC_IM_UTILS.g_status_message:=sqlerrm;
566 write_to_log_file_n('Error in read_sequence '||sqlerrm);
567 return -1;
568 End;
569
570 function sort_number_array(
571 p_list number_tabletype,
572 p_number_list number,
573 p_direction varchar2,
574 p_sorted_list out nocopy number_tabletype) return boolean is
575 l_temp number;
576 Begin
577 if g_debug then
578 write_to_log_file_n('In sort_number_array '||p_direction);
579 end if;
580 p_sorted_list:=p_list;
581 if g_debug then
582 write_to_debug_n('Before sort');
583 for i in 1..p_number_list loop
584 write_to_debug(p_sorted_list(i));
585 end loop;
586 end if;
587 if p_direction='ASC' then
588 for i in 1..p_number_list-1 loop
589 for j in 1..(p_number_list-i) loop
590 if p_sorted_list(j) > p_sorted_list(j+1) then
591 l_temp:= p_sorted_list(j+1);
592 p_sorted_list(j+1):=p_sorted_list(j);
593 p_sorted_list(j):=l_temp;
594 end if;
595 end loop;
596 end loop;
597 else
598 for i in 1..p_number_list-1 loop
599 for j in 1..(p_number_list-i) loop
600 if p_sorted_list(j) < p_sorted_list(j+1) then
601 l_temp:= p_sorted_list(j+1);
602 p_sorted_list(j+1):=p_sorted_list(j);
603 p_sorted_list(j):=l_temp;
604 end if;
605 end loop;
606 end loop;
607 end if;
608 if g_debug then
609 write_to_debug_n('After sort');
610 for i in 1..p_number_list loop
611 write_to_debug(p_sorted_list(i));
612 end loop;
613 end if;
614 return true;
615 Exception when others then
616 g_status_message:=sqlerrm;
617 write_to_log_file_n('Error in sort_number_array '||sqlerrm);
618 return false;
619 End;
620
621 function get_index(p_table varchar_tabletype,p_number_table number,p_value varchar2) return number is
622 Begin
623 for i in 1..p_number_table loop
624 if lower(p_table(i))=lower(p_value) then
625 return i;
626 end if;
627 end loop;
628 return 0;
629 Exception when others then
630 write_to_log_file_n('Error in get_index '||sqlerrm);
631 BSC_IM_UTILS.g_status_message:=sqlerrm;
632 return -1;
633 End;
634
635 function get_index(p_table number_tabletype,p_number_table number,p_value number) return number is
636 Begin
637 for i in 1..p_number_table loop
638 if p_table(i)=p_value then
639 return i;
640 end if;
641 end loop;
642 return 0;
643 Exception when others then
644 BSC_IM_UTILS.g_status_message:=sqlerrm;
645 write_to_log_file_n('Error in get_index '||sqlerrm);
646 return -1;
647 End;
648
649 function get_index(
650 p_table_1 varchar_tabletype,
651 p_table_2 number_tabletype,
652 p_number_table number,
653 p_value_1 varchar2,
654 p_value_2 number
655 ) return number is
656 Begin
657 for i in 1..p_number_table loop
658 if lower(p_table_1(i))=lower(p_value_1) and p_table_2(i)=p_value_2 then
659 return i;
660 end if;
661 end loop;
662 return 0;
663 Exception when others then
664 BSC_IM_UTILS.g_status_message:=sqlerrm;
665 write_to_log_file_n('Error in get_index '||sqlerrm);
666 return -1;
667 End;
668
669 function get_index(
670 p_table_1 varchar_tabletype,
671 p_table_2 varchar_tabletype,
672 p_number_table number,
673 p_value_1 varchar2,
674 p_value_2 varchar2
675 ) return number is
676 Begin
677 for i in 1..p_number_table loop
678 if lower(p_table_1(i))=lower(p_value_1) and lower(p_table_2(i))=lower(p_value_2) then
679 return i;
680 end if;
681 end loop;
682 return 0;
683 Exception when others then
684 BSC_IM_UTILS.g_status_message:=sqlerrm;
685 write_to_log_file_n('Error in get_index '||sqlerrm);
686 return -1;
687 End;
688
689 function get_rank(
690 p_parent_array varchar_tabletype,
691 p_child_array varchar_tabletype,
692 p_number_array number,
693 p_rep_array out nocopy varchar_tabletype,
694 p_rep_rank out nocopy number_tabletype,
695 p_number_rep_array out nocopy number,
696 p_max_rank out nocopy number
697 ) return boolean is
698 l_seed_child varchar_tabletype;
699 l_number_seed_child number;
700 l_rep_array varchar_tabletype;
701 l_rep_rank number_tabletype;
702 l_number_rep_array number;
703 l_index number;
704 p_rank number; --rkumar: bug 5335536
705 Begin
706 if g_debug then
707 write_to_log_file_n('In get_rank');
708 write_to_log_file('---------------------------------------------');
709 end if;
710 p_number_rep_array:=0;
711 l_number_seed_child:=0;
712 p_max_rank:=0;
713 if p_number_array is null or p_number_array<=0 then
714 return true;
715 end if;
716 --seed child contains all the children that are not parent
717 for i in 1..p_number_array loop
718 if in_array(p_parent_array,p_number_array,p_child_array(i))=false then
719 l_number_seed_child:=l_number_seed_child+1;
720 l_seed_child(l_number_seed_child):=p_child_array(i);
721 end if;
722 end loop;
723 if l_number_seed_child=0 then
724 if g_debug then
725 write_to_log_file_n('seed child not found. Error ');
726 end if;
727 return false;
728 else
729 if g_debug then
730 write_to_log_file_n('The seed tables ');
731 for i in 1..l_number_seed_child loop
732 write_to_log_file(l_seed_child(i));
733 end loop;
734 end if;
735 end if;
736 l_number_rep_array:=0;
737 -- filled rep_array with all the unique elements from parent_array and set the rank to 0
738 for i in 1..p_number_array loop
739 if in_array(l_rep_array,l_number_rep_array,p_parent_array(i))=false then
740 l_number_rep_array:=l_number_rep_array+1;
741 l_rep_array(l_number_rep_array):=p_parent_array(i);
742 l_rep_rank(l_number_rep_array):=0;
743 end if;
744 end loop;
745 --filled rep_array with all the unique elements from chld_array and set the rank to 0
746 for i in 1..p_number_array loop
747 if in_array(l_rep_array,l_number_rep_array,p_child_array(i))=false then
748 l_number_rep_array:=l_number_rep_array+1;
749 l_rep_array(l_number_rep_array):=p_child_array(i);
750 if substr(p_child_array(i),1,7)='BSC_SB_' then --rkumar:bug5335536
751 l_rep_rank(l_number_rep_array):= -1;
752 else
753 l_rep_rank(l_number_rep_array):=0;
754 end if;
755 end if;
756 end loop;
757 for i in 1..l_number_seed_child loop
758 --rkumar: bug5335536 Here check for l_seed_child(i) if it matches _SB_ call the set rank with p_rank as -1
759 if substr(l_seed_child(i),1,7)='BSC_SB_' then
760 p_rank:= -1;
761 else
762 p_rank:=0;
763 end if;
764
765 if set_rank(
766 p_parent_array,
767 p_child_array,
768 p_number_array,
769 l_seed_child(i),
770 p_rank,
771 l_rep_array,
772 l_rep_rank,
773 l_number_rep_array)=false then
774 return false;
775 end if;
776 end loop;
777 for i in 1..l_number_rep_array loop
778 if p_max_rank<l_rep_rank(i) then
779 p_max_rank:=l_rep_rank(i);
780 end if;
781 end loop;
782 if g_debug then
783 write_to_log_file_n('The rep tables and their rank p_max_rank='||p_max_rank);
784 for i in 1..l_number_rep_array loop
785 write_to_log_file(l_rep_array(i)||' '||l_rep_rank(i));
786 end loop;
787 write_to_log_file('---------------------------------------------');
788 end if;
789 p_rep_array:=l_rep_array;
790 p_rep_rank:=l_rep_rank;
791 p_number_rep_array:=l_number_rep_array;
792 return true;
793 Exception when others then
794 g_status_message:=sqlerrm;
795 write_to_log_file_n('Error in get_rank '||sqlerrm);
796 return false;
797 End;
798
799 function set_rank(
800 p_parent_array varchar_tabletype,
801 p_child_array varchar_tabletype,
802 p_number_array number,
803 p_child_level varchar2,
804 p_rank number,
805 p_rep_array in out nocopy varchar_tabletype,
806 p_rep_rank in out nocopy number_tabletype,
807 p_number_rep_array in out nocopy number
808 ) return boolean is
809 l_index number;
810 l_rank number;
811 Begin
812 --if g_debug then
813 --write_to_log_file_n('In set_rank for child='||p_child_level||' rank='||p_rank||' seed='||p_child_level);
814 --end if;
815 l_index:=get_index(p_rep_array,p_number_rep_array,p_child_level);
816 if l_index>0 then
817 if p_rep_rank(l_index)<p_rank then
818 p_rep_rank(l_index):=p_rank;
819 end if;
820 end if;
821 for i in 1..p_number_array loop
822 if p_child_array(i)=p_child_level then
823 l_rank:=p_rank+1;
824 if set_rank(p_parent_array,p_child_array,p_number_array,p_parent_array(i),l_rank,p_rep_array,p_rep_rank,
825 p_number_rep_array)=false then
826 return false;
827 end if;
828 end if;
829 end loop;
830 return true;
831 Exception when others then
832 g_status_message:=sqlerrm;
833 write_to_log_file_n('Error in set_rank '||sqlerrm);
834 return false;
835 End;
836
837 function get_distinct_list(
838 p_input varchar_tabletype,
839 p_number_input number,
840 p_dist_list out nocopy varchar_tabletype,
841 p_number_dist_list out nocopy number
842 ) return boolean is
843 Begin
844 p_number_dist_list:=0;
845 for i in 1..p_number_input loop
846 if in_array(p_dist_list,p_number_dist_list,p_input(i))=false then
847 p_number_dist_list:=p_number_dist_list+1;
848 p_dist_list(p_number_dist_list):=p_input(i);
849 end if;
850 end loop;
851 return true;
852 Exception when others then
853 g_status_message:=sqlerrm;
854 write_to_log_file_n('Error in get_distinct_list '||sqlerrm);
855 return false;
856 End;
857
858 function get_distinct_list(
859 p_input number_tabletype,
860 p_number_input number,
861 p_dist_list out nocopy number_tabletype,
862 p_number_dist_list out nocopy number
863 ) return boolean is
864 Begin
865 p_number_dist_list:=0;
866 for i in 1..p_number_input loop
867 if in_array(p_dist_list,p_number_dist_list,p_input(i))=false then
868 p_number_dist_list:=p_number_dist_list+1;
869 p_dist_list(p_number_dist_list):=p_input(i);
870 end if;
871 end loop;
872 return true;
873 Exception when others then
874 g_status_message:=sqlerrm;
875 write_to_log_file_n('Error in get_distinct_list '||sqlerrm);
876 return false;
877 End;
878
879 function parse_values(
880 p_list varchar2,
881 p_separator varchar2,
882 p_names out nocopy number_tabletype,
883 p_number_names out nocopy number) return boolean is
884 l_names varchar_tabletype;
885 Begin
886 if parse_values(p_list,p_separator,l_names,p_number_names)=false then
887 return false;
888 end if;
889 for i in 1..p_number_names loop
890 p_names(i):=l_names(i);
891 end loop;
892 return true;
893 Exception when others then
894 g_status_message:=sqlerrm;
895 write_to_log_file_n('Error in parse_values '||sqlerrm);
896 return false;
897 End;
898 --------
899 function parse_values(
900 p_list varchar2,
901 p_separator varchar2,
902 p_names out nocopy varchar_tabletype,
903 p_number_names out nocopy number) return boolean is
904 l_start number;
905 l_end number;
906 l_len number;
907 Begin
908 p_number_names:=0;
909 if p_list is null then
910 return true;
911 end if;
912 l_len:=length(p_list);
913 if l_len<=0 then
914 return true;
915 end if;
916 if instr(p_list,p_separator)=0 then
917 p_number_names:=1;
918 p_names(p_number_names):=ltrim(rtrim(p_list));
919 return true;
920 end if;
921 l_start:=1;
922 loop
923 l_end:=instr(p_list,p_separator,l_start);
924 if l_end=0 then
925 l_end:=l_len+1;
926 end if;
927 p_number_names:=p_number_names+1;
928 p_names(p_number_names):=ltrim(rtrim(substr(p_list,l_start,(l_end-l_start))));
929 l_start:=l_end+1;
930 if l_end>=l_len then
931 exit;
932 end if;
933 end loop;
934 /*
935 if g_debug then
936 write_to_log_file_n('The input string '||p_list);
937 write_to_log_file('Parsed values');
938 for i in 1..p_number_names loop
939 write_to_log_file(p_names(i));
940 end loop;
941 end if;*/
942 return true;
943 Exception when others then
944 g_status_message:=sqlerrm;
945 write_to_log_file_n('Error in parse_values '||sqlerrm);
946 return false;
947 End;
948
949 function parse_and_find(
950 p_list varchar2,
951 p_separator varchar2,
952 p_string varchar2
953 )return boolean is
954 l_array varchar_tabletype;
955 l_number_array number;
956 Begin
957 if parse_values(p_list,p_separator,l_array,l_number_array)=false then
958 return false;
959 end if;
960 for i in 1..l_number_array loop
961 if l_array(i)=p_string then
962 return true;
963 end if;
964 end loop;
965 return false;
966 Exception when others then
967 g_status_message:=sqlerrm;
968 write_to_log_file_n('Error in parse_and_find '||sqlerrm);
969 return false;
970 End;
971
972 function get_value(
973 p_list varchar_tabletype,
974 p_list_values varchar_tabletype,
975 p_number_list number,
976 p_list_name varchar2
977 )return varchar2 is
978 l_index number;
979 l_list_value varchar2(4000);
980 Begin
981 l_list_value:=null;
982 l_index:=get_index(p_list,p_number_list,p_list_name);
983 if l_index >0 then
984 l_list_value:=p_list_values(l_index);
985 end if;
986 return l_list_value;
987 Exception when others then
988 g_status_message:=sqlerrm;
989 write_to_log_file_n('Error in get_value '||sqlerrm);
990 return null;
991 End;
992
993 /*
994 given a set of levels, return an ordered set of them
995 */
996 function get_ordered_levels(
997 p_dim_name varchar2,
998 p_apps_origin varchar2,
999 p_levels varchar_tabletype,
1000 p_number_levels number,
1001 p_ordered_levels out nocopy varchar_tabletype,
1002 p_number_children out nocopy number_tabletype
1003 )return boolean is
1004 l_count number;
1005 l_index number;
1006 l_levels varchar_tabletype;
1007 l_level_number_children number_tabletype;
1008 l_number_levels number;
1009 l_ordered_levels varchar_tabletype;
1010 l_number_children number_tabletype;
1011 l_min number:=1000000;
1012 l_looked_at number_tabletype;
1013 l_number_looked_at number:=0;
1014 l_min_index number;
1015 l_description varchar_tabletype;
1016 l_property varchar_tabletype;
1017 Begin
1018 if BSC_IM_INT_MD.get_level(
1019 p_dim_name,
1020 p_apps_origin,
1021 l_levels,
1022 l_level_number_children,
1023 l_description,
1024 l_property,
1025 l_number_levels)=false then
1026 return false;
1027 end if;
1028 l_count:=0;
1029 for i in 1..p_number_levels loop
1030 l_index:=0;
1031 l_index:=get_index(l_levels,l_number_levels,p_levels(i));
1032 if l_index>0 then
1033 l_count:=l_count+1;
1034 l_ordered_levels(l_count):=l_levels(l_index);
1035 l_number_children(l_count):=l_level_number_children(l_index);
1036 end if;
1037 end loop;
1038 for i in 1..l_count loop
1039 l_min:=1000000;
1040 for j in 1..l_count loop
1041 if l_number_children(j)<l_min then
1042 if in_array(l_looked_at,l_number_looked_at,j)=false then
1043 l_min_index:=j;
1044 l_min:=l_number_children(j);
1045 end if;
1046 end if;
1047 end loop;
1048 l_number_looked_at:=l_number_looked_at+1;
1049 l_looked_at(l_number_looked_at):=l_min_index;
1050 p_ordered_levels(i):=l_ordered_levels(l_min_index);
1051 p_number_children(i):=l_number_children(l_min_index);
1052 end loop;
1053 if g_debug then
1054 write_to_debug_n('Results');
1055 for i in 1..l_count loop
1056 write_to_debug(p_ordered_levels(i)||' '||p_number_children(i));
1057 end loop;
1058 end if;
1059 return true;
1060 Exception when others then
1061 g_status_message:=sqlerrm;
1062 write_to_log_file_n('Error in get_ordered_levels '||sqlerrm);
1063 return false;
1064 End;
1065
1066 function get_ordered_levels(
1067 p_levels varchar_tabletype,
1068 p_level_number_children number_tabletype,
1069 p_number_levels number,
1070 p_ordered_levels out nocopy varchar_tabletype) return boolean is
1071 l_sorted_list number_tabletype;
1072 l_last number;
1073 l_count number:=0;
1074 Begin
1075 if sort_number_array(p_level_number_children,p_number_levels,
1076 'DSC',l_sorted_list)=false then
1077 return false;
1078 end if;
1079 l_last:=-1;
1080 for i in 1..p_number_levels loop
1081 if l_sorted_list(i)<>l_last then
1082 l_last:=l_sorted_list(i);
1083 for j in 1..p_number_levels loop
1084 if p_level_number_children(j)=l_last then
1085 l_count:=l_count+1;
1086 p_ordered_levels(l_count):=p_levels(j);
1087 end if;
1088 end loop;
1089 end if;
1090 end loop;
1091 return true;
1092 Exception when others then
1093 g_status_message:=sqlerrm;
1094 write_to_log_file_n('Error in get_ordered_levels '||sqlerrm);
1095 return false;
1096 End;
1097
1098 function get_seq_nextval(p_seq varchar2) return number is
1099 Begin
1100 if p_seq is null then
1101 return read_global;
1102 else
1103 return read_sequence(p_seq);
1104 end if;
1105 Exception when others then
1106 BSC_IM_UTILS.g_status_message:=sqlerrm;
1107 return -1;
1108 End;
1109
1110 function drop_db_object(p_object varchar2,p_type varchar2,p_owner varchar2) return boolean is
1111 l_stmt varchar2(1000);
1112 Begin
1113 if p_owner is null then
1114 l_stmt:='drop '||p_type||' '||p_object;
1115 else
1116 l_stmt:='drop '||p_type||p_owner||'.'||p_object;
1117 end if;
1118 write_to_debug_n(l_stmt);
1119 execute immediate l_stmt;
1120 return true;
1121 Exception when others then
1122 g_status_message:=sqlerrm;
1123 write_to_log_file_n('Error in drop_db_object '||sqlerrm);
1124 return false;
1125 End;
1126
1127 function set_global_dimensions return boolean is
1128 Begin
1129 g_global_dimension(1):='PERIODICITY_ID';
1130 g_global_dimension(2):='YEAR';
1131 g_global_dimension(3):='PERIOD';
1132 g_global_dimension(4):='TYPE';
1133 g_number_global_dimension:=4;
1134 return true;
1135 Exception when others then
1136 g_status_message:=sqlerrm;
1137 write_to_log_file_n('Error in set_global_dimensions '||sqlerrm);
1138 return false;
1139 End;
1140
1141 function get_global_dimensions(
1142 p_global_dimensions out nocopy varchar_tabletype,
1143 p_number_global_dimensions out nocopy number
1144 ) return boolean is
1145 Begin
1146 p_global_dimensions:=g_global_dimension;
1147 p_number_global_dimensions:=g_number_global_dimension;
1148 return true;
1149 Exception when others then
1150 g_status_message:=sqlerrm;
1151 write_to_log_file_n('Error in get_global_dimensions '||sqlerrm);
1152 return false;
1153 End;
1154
1155 function is_global_dimension(
1156 p_column varchar2
1157 )return boolean is
1158 Begin
1159 for i in 1..g_number_global_dimension loop
1160 if g_global_dimension(i)=p_column then
1161 return true;
1162 end if;
1163 end loop;
1164 return false;
1165 Exception when others then
1166 g_status_message:=sqlerrm;
1167 write_to_log_file_n('Error in is_global_dimension '||sqlerrm);
1168 return false;
1169 End;
1170
1171 function check_package(p_package varchar2) return boolean is
1172 l_stmt varchar2(4000);
1173 TYPE CurTyp IS REF CURSOR;
1174 cv CurTyp;
1175 l_res number;
1176 Begin
1177 --changed from all_source to user_source
1178 l_stmt:= 'SELECT 1 FROM USER_SOURCE WHERE NAME=:1 AND TYPE=:2 ';
1179 write_to_debug_n(l_stmt||' '||p_package);
1180 open cv for l_stmt using p_package, 'PACKAGE';
1181 fetch cv into l_res;
1182 close cv;
1183 if l_res=1 then
1184 write_to_debug('Found!');
1185 return true;
1186 else
1187 write_to_debug('NOT Found!');
1188 return false;
1189 end if;
1190 Exception when others then
1191 g_status_message:=sqlerrm;
1192 write_to_log_file_n('Error in check_package '||sqlerrm);
1193 return false;
1194 End;
1195
1196 function get_table_owner(p_table varchar2) return varchar2 is
1197 l_owner varchar2(400);
1198 l_stmt varchar2(4000);
1199 cursor c1(p_table varchar2) is select table_owner from user_synonyms where synonym_name=p_table;
1200 -----------------------------------
1201 Begin
1202 if g_debug then
1203 write_to_log_file_n('In get_table_owner'||get_time);
1204 end if;
1205 if instr(p_table,'.')<>0 then
1206 l_owner:=substr(p_table,1,instr(p_table,'.')-1);
1207 return l_owner;
1208 end if;
1209 open c1(p_table);
1210 fetch c1 into l_owner;
1211 close c1;
1212 if l_owner is null then
1213 -- owner is apps return apps schema name
1214 if g_debug then
1215 write_to_log_file_n('going to get apps owner '||get_time);
1216 end if;
1217 l_owner := BSC_APPS.get_user_schema('APPS');
1218 if g_debug then
1219 write_to_log_file_n('After get apps owner '||get_time);
1220 end if;
1221 end if;
1222 if g_debug then
1223 write_to_log_file_n('Owner for '||p_table||' is '||l_owner);
1224 end if;
1225 return l_owner;
1226 Exception when others then
1227 BSC_IM_UTILS.g_status_message:=sqlerrm;
1228 write_to_log_file_n('Error in get_table_owner '||sqlerrm);
1229 return null;
1230 End;
1231
1232 function get_object_owner(p_object varchar2) return varchar2 is
1233 l_owner varchar2(400);
1234 l_stmt varchar2(4000);
1235 cursor c1(p_table varchar2) is select table_owner from user_synonyms where synonym_name=p_table;
1236 Begin
1237 if g_debug then
1238 write_to_log_file_n('In get_object_owner '||p_object);
1239 end if;
1240 if instr(p_object,'.')<>0 then
1241 l_owner:=substr(p_object,1,instr(p_object,'.')-1);
1242 return l_owner;
1243 end if;
1244 open c1(p_object);
1245 fetch c1 into l_owner;
1246 close c1;
1247 if l_owner is null then
1248 -- owner is apps return apps schema name
1249 l_owner := BSC_APPS.get_user_schema('APPS');
1250 end if;
1251 if g_debug then
1252 write_to_log_file_n('Owner for '||p_object||' is '||l_owner);
1253 end if;
1254 return l_owner;
1255 Exception when others then
1256 BSC_IM_UTILS.g_status_message:=sqlerrm;
1257 write_to_log_file_n('Error in get_object_owner '||sqlerrm);
1258 return null;
1259 End;
1260
1261 function get_object_type(
1262 p_object varchar2,
1263 p_owner varchar2
1264 ) return varchar2 is
1265 l_type varchar2(400);
1266 l_stmt varchar2(4000);
1267 TYPE CurTyp IS REF CURSOR;
1268 cv CurTyp;
1269 l_owner varchar2(400);
1270 l_object varchar2(400);
1271 Begin
1272 if g_debug then
1273 write_to_log_file_n('In get_object_type '||p_object);
1274 end if;
1275 l_owner:=p_owner;
1276 l_object:=p_object;
1277 if instr(p_object,'.')<>0 then
1278 l_owner:=substr(p_object,1,instr(p_object,'.')-1);
1279 l_object:=substr(p_object,instr(p_object,'.')+1);
1280 else
1281 if l_owner is null then
1282 l_owner:=get_object_owner(l_object);
1283 end if;
1284 end if;
1285 --first see if this is a mview
1286 if is_mview(l_object,l_owner) then
1287 l_type:='MATERIALIZED VIEW';
1288 else
1289 l_stmt:='select object_type from all_objects where object_name=:1 and owner=:2';
1290 if g_debug then
1291 write_to_log_file_n(l_stmt||' '||l_object||','||l_owner);
1292 end if;
1293 open cv for l_stmt using l_object,l_owner;
1294 loop
1295 fetch cv into l_type;
1296 --MV always gives 2 rows, 1 saying table and the other saying MV
1297 if l_type='MATERIALIZED VIEW' then
1298 exit;
1299 end if;
1300 exit when cv%notfound;
1301 end loop;
1302 close cv;
1303 end if;
1304 if g_debug then
1305 write_to_log_file_n('object type '||l_type);
1306 end if;
1307 return l_type;
1308 Exception when others then
1309 BSC_IM_UTILS.g_status_message:=sqlerrm;
1310 write_to_log_file_n('Error in get_object_type '||sqlerrm);
1311 return null;
1312 End;
1313
1314 function get_table_constraints(
1315 p_table_name varchar2,
1316 p_table_owner varchar2,
1317 p_constraint_name out nocopy varchar_tabletype,
1318 p_constraint_type out nocopy varchar_tabletype,
1319 p_status out nocopy varchar_tabletype,
1320 p_validated out nocopy varchar_tabletype,
1321 p_index_name out nocopy varchar_tabletype,
1322 p_number_constraints out nocopy number
1323 )return boolean is
1324 TYPE CurTyp IS REF CURSOR;
1325 cv CurTyp;
1326 l_table_owner varchar2(200);
1327 Begin
1328 p_number_constraints:=0;
1329 l_table_owner:=p_table_owner;
1330 if l_table_owner is null then
1331 l_table_owner:=get_table_owner(p_table_name);
1332 end if;
1333 g_stmt:='select constraint_name,constraint_type,status,validated,index_name from all_constraints '||
1334 'where table_name=:a and owner=:b';
1335 write_to_debug_n(g_stmt||' using '||p_table_name||' '||p_table_owner);
1336 open cv for g_stmt using p_table_name,p_table_owner;
1337 loop
1338 fetch cv into p_constraint_name(p_number_constraints),p_constraint_type(p_number_constraints),
1339 p_status(p_number_constraints),p_validated(p_number_constraints),p_index_name(p_number_constraints);
1340 exit when cv%notfound;
1341 p_number_constraints:=p_number_constraints+1;
1342 end loop;
1343 --Fix bug#3899842: Close cursor
1344 close cv;
1345 p_number_constraints:=p_number_constraints-1;
1346 if g_debug then
1347 write_to_log_file_n('Results');
1348 for i in 1..p_number_constraints loop
1349 write_to_log_file(p_constraint_name(i)||' '||p_constraint_type(i)||' '||p_status(i)||' '||
1350 p_validated(i)||' '||p_index_name(i));
1351 end loop;
1352 end if;
1353 return true;
1354 Exception when others then
1355 g_status_message:=sqlerrm;
1356 write_to_log_file_n('Error in get_table_constraints '||sqlerrm);
1357 return false;
1358 End;
1359
1360 function get_partition_clause return varchar2 is
1361 l_num_partitions number;
1362 begin
1363 l_num_partitions := bsc_dbgen_metadata_reader.get_max_partitions;
1364 if (l_num_partitions > 1) then
1365 return ' partition by hash(periodicity_id, year, period) partitions '||l_num_partitions;
1366 else
1367 return null;
1368 end if;
1369 end;
1370
1371 function create_mv_log_on_table(
1372 p_table_name varchar2,
1373 p_table_owner varchar2,
1374 p_options varchar_tabletype,
1375 p_number_options number,
1376 p_uk_columns varchar_tabletype,
1377 p_numbet_uk_columns number,
1378 p_columns varchar_tabletype,
1379 p_number_columns number,
1380 p_snplog_creates out nocopy boolean
1381 )return boolean is
1382 l_table_owner varchar2(200);
1383 l_object_type varchar2(200);
1384 ------------------------------------
1385 l_tablespace varchar2(200);
1386 l_storage varchar2(200);
1387 ------------------------------------
1388 Begin
1389 --p_table_name may be a table, MV, view etc.
1390 if g_debug then
1391 write_to_log_file_n('In util.create_mv_log_on_table '||p_table_name||' '||p_table_owner);
1392 end if;
1393 l_table_owner:=p_table_owner;
1394 p_snplog_creates:=false;
1395 if l_table_owner is null then
1396 l_table_owner:=get_table_owner(p_table_name);
1397 if l_table_owner is null then
1398 l_table_owner:=get_object_owner(p_table_name);
1399 end if;
1400 end if;
1401 if l_table_owner is null then
1402 return false;
1403 end if;
1404 l_object_type:=get_object_type(p_table_name,l_table_owner);
1405 if l_object_type<>'TABLE' and l_object_type<>'MATERIALIZED VIEW' then
1406 return true;
1407 end if;
1408 if get_option_value(p_options,p_number_options,'RECREATE')='Y' then
1409 --if drop_constraint(p_table_name,l_table_owner,p_table_name||'_pk')=false then
1410 --null;
1411 --end if;
1412 --if drop_mv_log(p_table_name,l_table_owner)=false then
1413 --null;
1414 --end if;
1415 null;
1416 else
1417 if check_snapshot_log(p_table_name,l_table_owner) then
1418 write_to_debug_n('The snapshot log already exists');
1419 return true;
1420 end if;
1421 end if;
1422 l_tablespace:=get_option_value(p_options,p_number_options,'TABLESPACE');
1423 l_storage:=get_option_value(p_options,p_number_options,'STORAGE');
1424 if l_tablespace is not null then
1425 if instr(lower(l_tablespace),'tablespace')<=0 then
1426 l_tablespace:=' tablespace '||l_tablespace;
1427 end if;
1428 end if;
1429 if l_storage is not null then
1430 if instr(lower(l_storage),'storage')<=0 then
1431 l_storage:=' storage '||l_storage;
1432 end if;
1433 end if;
1434 --first create the constraint
1435 -- removed 11/18/2005 by arun, instead of creating constraint use this in the rowid clause
1436 /*if p_numbet_uk_columns>0 then
1437 g_stmt := reorder_index(p_table_name, p_uk_columns);
1438 g_stmt:='alter table '||l_table_owner||'.'||p_table_name||' add constraint '||p_table_name||'_pk '||
1439 'primary key ('||g_stmt;
1440 g_stmt:=g_stmt||') rely enable novalidate';
1441 if g_debug then
1442 write_to_log_file_n(g_stmt||get_time);
1443 end if;
1444 begin
1445 execute immediate g_stmt;
1446 exception when others then
1447 BSC_IM_UTILS.g_status_message:=sqlerrm;
1448 write_to_log_file_n('Error in creating primary key constraint '||sqlerrm);
1449 end;
1450 end if; */
1451
1452 g_stmt:='create MATERIALIZED VIEW log on '||l_table_owner||'.'||p_table_name||' '||l_tablespace||
1453 ' INITRANS 4 MAXTRANS 255 '||l_storage;
1454
1455 if (p_table_name like 'BSC_B_%') then
1456 g_stmt := g_stmt|| get_partition_clause;
1457 end if;
1458 g_stmt:= g_stmt|| ' with ';
1459 if get_db_version='9i' then
1460 g_stmt:=g_stmt||'sequence,';
1461 end if;
1462 --if p_numbet_uk_columns>0 then
1463 -- g_stmt:=g_stmt||'primary key,';
1464 --end if;
1465 g_stmt:=g_stmt||'rowid';
1466 if p_number_columns>0 or p_numbet_uk_columns>0 then
1467 g_stmt:=g_stmt||'(';
1468 for i in 1..p_numbet_uk_columns loop
1469 g_stmt:=g_stmt||p_uk_columns(i)||',';
1470 end loop;
1471 for i in 1..p_number_columns loop
1472 g_stmt:=g_stmt||p_columns(i)||',';
1473 end loop;
1474 g_stmt:=substr(g_stmt,1,length(g_stmt)-1);
1475 g_stmt:=g_stmt||')';
1476 end if;
1477 g_stmt:=g_stmt||' including new values';
1478 write_to_debug_n(g_stmt);
1479 begin
1480 execute immediate g_stmt;
1481 p_snplog_creates:=true;
1482 exception when others then
1483 BSC_IM_UTILS.g_status_message:=sqlerrm;
1484 write_to_log_file_n('Error in creating mv log '||sqlerrm);
1485 end;
1486 return true;
1487 Exception when others then
1488 if sqlcode=-00942 then
1489 if g_debug then
1490 write_to_log_file_n('Error in util create_mv_log_on_table '||sqlerrm);
1491 write_to_log_file('You cannot create materialized view log on this object '||l_table_owner||'.'||
1492 p_table_name);
1493 end if;
1494 return true;
1495 else
1496 g_status_message:=sqlerrm;
1497 write_to_log_file_n('Error in util create_mv_log_on_table '||sqlerrm);
1498 return false;
1499 end if;
1500 End;
1501
1502 function drop_constraint(
1503 p_table_name varchar2,
1504 p_table_owner varchar2,
1505 p_constraint varchar2
1506 )return boolean is
1507 l_table_owner varchar2(200);
1508 Begin
1509 l_table_owner:=p_table_owner;
1510 if l_table_owner is null then
1511 l_table_owner:=get_table_owner(p_table_name);
1512 end if;
1513 g_stmt:='alter table '||l_table_owner||'.'||p_table_name||' drop constraint '||p_constraint;
1514 if g_debug then
1515 write_to_log_file_n(g_stmt);
1516 end if;
1517 execute immediate g_stmt;
1518 return true;
1519 Exception when others then
1520 g_status_message:=sqlerrm;
1521 write_to_log_file_n('Error in drop_constraint '||sqlerrm);
1522 return false;
1523 End;
1524
1525 function drop_mv_log(
1526 p_table_name varchar2,
1527 p_table_owner varchar2
1528 )return boolean is
1529 l_table_owner varchar2(200);
1530 Begin
1531 l_table_owner:=p_table_owner;
1532 if l_table_owner is null then
1533 l_table_owner:=get_table_owner(p_table_name);
1534 end if;
1535 g_stmt:='drop materialized view log on '||l_table_owner||'.'||p_table_name;
1536 if g_debug then
1537 write_to_log_file_n(g_stmt);
1538 end if;
1539 execute immediate g_stmt;
1540 return true;
1541 Exception when others then
1542 g_status_message:=sqlerrm;
1543 write_to_log_file_n('Error in drop_mv_log '||sqlerrm);
1544 return false;
1545 End;
1546
1547 function drop_mv(
1548 p_mv varchar2,
1549 p_mv_owner varchar2
1550 )return boolean is
1551 l_table_owner varchar2(200);
1552 Begin
1553 l_table_owner:=p_mv_owner;
1554 if l_table_owner is null and instr(p_mv,'.')<>0 then
1555 l_table_owner:=substr(p_mv,1,instr(p_mv,'.')-1);
1556 end if;
1557 if l_table_owner is null then
1558 l_table_owner:=get_table_owner(p_mv);
1559 end if;
1560 /*
1561 have to use ad_mv api pre-req patch 3050839
1562 ad_mv.drop_mv(<MV NAME>, 'DROP MATERIALIZED VIEW <MV NAME>');
1563 */
1564 g_stmt:='drop materialized view '||l_table_owner||'.'||p_mv;
1565 if g_debug then
1566 write_to_log_file_n(g_stmt);
1567 end if;
1568 execute immediate g_stmt;
1569 return true;
1570 Exception when others then
1571 g_status_message:=sqlerrm;
1572 write_to_log_file_n('Error in drop_mv '||sqlerrm);
1573 return false;
1574 End;
1575
1576 function drop_view(
1577 p_view varchar2,
1578 p_view_owner varchar2
1579 )return boolean is
1580 Begin
1581 if p_view_owner is not null then
1582 g_stmt:='drop view '||p_view_owner||'.'||p_view;
1583 else
1584 g_stmt:='drop view '||p_view;
1585 end if;
1586 if g_debug then
1587 write_to_log_file_n(g_stmt);
1588 end if;
1589 execute immediate g_stmt;
1590 return true;
1591 Exception when others then
1592 g_status_message:=sqlerrm;
1593 write_to_log_file_n('Error in drop_view '||sqlerrm);
1594 return false;
1595 End;
1596
1597 function check_snapshot_log(
1598 p_table_name varchar2,
1599 p_table_owner varchar2
1600 )return boolean is
1601 l_table_owner varchar2(200);
1602 TYPE CurTyp IS REF CURSOR;
1603 cv CurTyp;
1604 l_res number;
1605 Begin
1606 l_table_owner:=p_table_owner;
1607 if l_table_owner is null then
1608 l_table_owner:=get_table_owner(p_table_name);
1609 end if;
1610 g_stmt:='select 1 from all_snapshot_logs where MASTER=:a and log_owner=:b';
1611 write_to_debug_n(g_stmt||' using '||p_table_name||' '||l_table_owner);
1612 open cv for g_stmt using p_table_name,l_table_owner;
1613 fetch cv into l_res;
1614 close cv;
1615 if l_res=1 then
1616 write_to_debug('Found!');
1617 return true;
1618 else
1619 write_to_debug('NOT Found!');
1620 return false;
1621 end if;
1622 Exception when others then
1623 g_status_message:=sqlerrm;
1624 write_to_log_file_n('Error in check_snapshot_log '||sqlerrm);
1625 return false;
1626 End;
1627
1628 function get_snapshot_log(
1629 p_table_name varchar2,
1630 p_table_owner varchar2,
1631 p_snplog out nocopy varchar2
1632 )return boolean is
1633 l_table_owner varchar2(200);
1634 TYPE CurTyp IS REF CURSOR;
1635 cv CurTyp;
1636 l_res number;
1637 Begin
1638 l_table_owner:=p_table_owner;
1639 if l_table_owner is null then
1640 l_table_owner:=get_table_owner(p_table_name);
1641 end if;
1642 g_stmt:='select log_table from all_snapshot_logs where MASTER=:a and log_owner=:b';
1643 write_to_debug_n(g_stmt||' using '||p_table_name||' '||l_table_owner);
1644 open cv for g_stmt using p_table_name,l_table_owner;
1645 fetch cv into p_snplog;
1646 close cv;
1647 return true;
1648 Exception when others then
1649 g_status_message:=sqlerrm;
1650 write_to_log_file_n('Error in get_snapshot_log '||sqlerrm);
1651 return false;
1652 End;
1653
1654 function get_mv_owner(p_mv_name varchar2) return varchar2 is
1655 Begin
1656 return get_table_owner(p_mv_name);
1657 -- RETURN BSC SCHEMA NAME
1658 --return BSC_APPS.get_user_schema;
1659 Exception when others then
1660 BSC_IM_UTILS.g_status_message:=sqlerrm;
1661 write_to_log_file_n('Error in get_mv_owner '||sqlerrm);
1662 return null;
1663 End;
1664
1665 function get_mv_properties(
1666 p_mv_name varchar2,
1667 p_mv_owner in out nocopy varchar2,
1668 p_refresh_mode out nocopy varchar2,
1669 p_refresh_method out nocopy varchar2,
1670 p_build_mode out nocopy varchar2,
1671 p_last_refresh_type out nocopy varchar2,
1672 p_last_refresh_date out nocopy date,
1673 p_staleness out nocopy varchar2
1674 )return boolean is
1675 l_mv_owner varchar2(200);
1676 TYPE CurTyp IS REF CURSOR;
1677 cv CurTyp;
1678 Begin
1679 l_mv_owner:=p_mv_owner;
1680 if l_mv_owner is null then
1681 l_mv_owner:=get_mv_owner(p_mv_name);
1682 p_mv_owner:=l_mv_owner;
1683 end if;
1684 g_stmt:='select refresh_mode,refresh_method,build_mode,last_refresh_type,last_refresh_date, '||
1685 'staleness from all_mviews where mview_name=:a and owner=:b';
1686 write_to_debug_n(g_stmt||' using '||p_mv_name||' '||l_mv_owner);
1687 open cv for g_stmt using p_mv_name,l_mv_owner;
1688 fetch cv into p_refresh_mode,p_refresh_method,p_build_mode,p_last_refresh_type,
1689 p_last_refresh_date,p_staleness;
1690 --Fix bug#3899842: Close cursor
1691 close cv;
1692 write_to_debug_n(p_refresh_mode||', '||p_refresh_method||', '||p_build_mode||', '||
1693 p_last_refresh_type||', '||p_last_refresh_date||', '||p_staleness);
1694 return true;
1695 Exception when others then
1696 g_status_message:=sqlerrm;
1697 write_to_log_file_n('Error in get_mv_properties '||sqlerrm);
1698 return false;
1699 End;
1700
1701 function drop_materialized_view(p_mview varchar2,p_owner varchar2) return boolean is
1702 l_owner varchar2(200);
1703 Begin
1704 l_owner:=p_owner;
1705 if l_owner is null then
1706 l_owner:=get_mv_owner(p_mview);
1707 end if;
1708 g_stmt:='drop MATERIALIZED VIEW '||l_owner||'.'||p_mview;
1709 write_to_debug_n(g_stmt);
1710 execute immediate g_stmt;
1711 write_to_debug_n('Dropped MV');
1712 return true;
1713 Exception when others then
1714 g_status_message:=sqlerrm;
1715 write_to_log_file_n('Error in drop_materialized_view '||sqlerrm);
1716 return false;
1717 End;
1718
1719 function check_mv(
1720 p_mv_name varchar2,
1721 p_mv_owner varchar2
1722 )return boolean is
1723 l_mv_owner varchar2(200);
1724 l_res number;
1725 TYPE CurTyp IS REF CURSOR;
1726 cv CurTyp;
1727 Begin
1728 l_mv_owner:=p_mv_owner;
1729 if l_mv_owner is null then
1730 l_mv_owner:=get_mv_owner(p_mv_name);
1731 end if;
1732 g_stmt:='select 1 from all_mviews where mview_name=:a and owner=:b';
1733 write_to_debug_n(g_stmt||' using '||p_mv_name||' '||l_mv_owner);
1734 open cv for g_stmt using p_mv_name,l_mv_owner;
1735 fetch cv into l_res;
1736 --Fix bug#3899842: close cursor
1737 close cv;
1738 if l_res=1 then
1739 write_to_debug('Found!');
1740 return true;
1741 else
1742 write_to_debug('NOT Found!');
1743 return false;
1744 end if;
1745 Exception when others then
1746 g_status_message:=sqlerrm;
1747 write_to_log_file_n('Error in check_mv '||sqlerrm);
1748 return false;
1749 End;
1750
1751 function check_view(
1752 p_view_name varchar2,
1753 p_view_owner varchar2
1754 )return boolean is
1755 l_mv_owner varchar2(200);
1756 l_res number;
1757 TYPE CurTyp IS REF CURSOR;
1758 cv CurTyp;
1759 Begin
1760 if p_view_owner is null then
1761 g_stmt:='select 1 from user_views where view_name=:a';
1762 write_to_debug_n(g_stmt||' using '||p_view_name);
1763 open cv for g_stmt using p_view_name;
1764 else
1765 g_stmt:='select 1 from all_views where view_name=:a and owner=:b';
1766 write_to_debug_n(g_stmt||' using '||p_view_name||' '||p_view_owner);
1767 open cv for g_stmt using p_view_name,p_view_owner;
1768 end if;
1769 fetch cv into l_res;
1770 --Fix bug#3899842: close cursor
1771 close cv;
1772 if l_res=1 then
1773 write_to_debug('Found!');
1774 return true;
1775 else
1776 write_to_debug('NOT Found!');
1777 return false;
1778 end if;
1779 Exception when others then
1780 g_status_message:=sqlerrm;
1781 write_to_log_file_n('Error in check_view '||sqlerrm);
1782 return false;
1783 End;
1784
1785 function refresh_mv(
1786 p_mv_name varchar2,
1787 p_mv_owner varchar2,
1788 p_kpi varchar2,
1789 p_options varchar_tabletype,
1790 p_number_options number
1791 )return boolean is
1792 --------------------
1793 l_method varchar2(40);
1794 l_parallel number;
1795 l_drop_index varchar2(40);
1796 --------------------
1797 l_start_time varchar2(100);
1798 l_end_time varchar2(100);
1799 --------------------
1800 l_index varchar_tabletype;
1801 l_uniqueness varchar_tabletype;
1802 l_tablespace varchar_tabletype;
1803 l_initial_extent number_tabletype;
1804 l_next_extent number_tabletype;
1805 l_max_extents number_tabletype;
1806 l_pct_increase number_tabletype;
1807 l_number_index number;
1808 ------
1809 l_ind_name varchar_tabletype;
1810 l_ind_col varchar_tabletype;
1811 l_number_ind_col number;
1812 --------------------
1813 l_stmt varchar2(32000);
1814 --------------------
1815 l_index_tablespace varchar2(320);
1816 l_index_storage varchar2(3000);
1817 --------------------
1818 l_snp_log varchar2(100);
1819 --------------------
1820 l_mv_owner varchar2(200);
1821 l_refresh_mode varchar2(200);
1822 l_refresh_method varchar2(200);
1823 l_build_mode varchar2(200);
1824 l_last_refresh_type varchar2(200);
1825 l_last_refresh_date date;
1826 l_staleness varchar2(200);
1827 --------------------
1828 Begin
1829 if g_debug then
1830 write_to_log_file_n('In BSC_IM_UTILS.refresh_mv '||p_mv_owner||'.'||p_mv_name||' kpi='||p_kpi||get_time);
1831 write_to_log_file('Options:-');
1832 for i in 1..p_number_options loop
1833 write_to_log_file(p_options(i));
1834 end loop;
1835 end if;
1836 if p_mv_owner is null then
1837 l_mv_owner:=get_table_owner(p_mv_name);
1838 else
1839 l_mv_owner:=p_mv_owner;
1840 end if;
1841 if is_mview(p_mv_name,l_mv_owner)=false then
1842 if g_debug then
1843 write_to_log_file_n('Not an MV. Cannot do MV refresh');
1844 end if;
1845 return true;
1846 end if;
1847 l_method:=get_option_value(p_options,p_number_options,'FULL REFRESH');
1848 if l_method='Y' then
1849 l_method:='c';
1850 else
1851 if get_mv_properties(
1852 p_mv_name,
1853 l_mv_owner,
1854 l_refresh_mode,
1855 l_refresh_method,
1856 l_build_mode,
1857 l_last_refresh_type,
1858 l_last_refresh_date,
1859 l_staleness)=false then
1860 l_method:='c';
1861 end if;
1862 if l_refresh_method='FAST' then
1863 l_method:='f';
1864 else
1865 l_method:='c';
1866 end if;
1867 end if;
1868 l_parallel:=get_option_value(p_options,p_number_options,'PARALLEL');
1869 l_drop_index:=get_option_value(p_options,p_number_options,'DROP INDEX');
1870 -----------------
1871 l_index_tablespace:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'INDEX TABLESPACE');
1872 l_index_storage:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'INDEX STORAGE');
1873 if l_index_tablespace is not null then
1874 if instr(lower(l_index_tablespace),'tablespace')<=0 then
1875 l_index_tablespace:=' tablespace '||l_index_tablespace;
1876 end if;
1877 end if;
1878 if l_index_storage is not null then
1879 if instr(lower(l_index_storage),'storage')<=0 then
1880 l_index_storage:=' storage '||l_index_storage;
1881 end if;
1882 end if;
1883 -----------------
1884 <<start_mv_refresh>>
1885 if g_debug then
1886 write_to_log_file_n('Method='||l_method||', parallel='||l_parallel||', drop index='||l_drop_index);
1887 end if;
1888 if l_drop_index='Y' then
1889 if g_debug then
1890 write_to_log_file_n('Drop Index specified. Will drop the indexes ONLY if the refresh is complete refresh');
1891 end if;
1892 if l_method='c' then
1893 if get_table_indexes(
1894 p_mv_name,
1895 l_mv_owner,
1896 l_index,
1897 l_uniqueness,
1898 l_tablespace,
1899 l_initial_extent,
1900 l_next_extent,
1901 l_max_extents,
1902 l_pct_increase,
1903 l_number_index
1904 )=false then
1905 return false;
1906 end if;
1907 if l_number_index>0 then
1908 if l_index_tablespace is null then
1909 l_index_tablespace:=' tablespace '||l_tablespace(1);
1910 end if;
1911 if l_index_storage is null then
1912 l_index_storage:=' storage (initial '||l_initial_extent(1)||' next '||l_next_extent(1)||
1913 ' minextents 1 maxextents '||l_max_extents(1)||' pctincrease '||l_pct_increase(1)||') ';
1914 end if;
1915 end if;
1916 for i in 1..l_number_index loop
1917 if g_debug then
1918 write_to_log_file('Dropping index '||l_mv_owner||'.'||l_index(i));
1919 end if;
1920 if execute_immediate('drop index '||l_mv_owner||'.'||l_index(i),null)=false then
1921 null;
1922 end if;
1923 if g_debug then
1924 write_to_log_file('Dropped index');
1925 end if;
1926 end loop;
1927 end if;
1928 end if;
1929 l_start_time:=get_time;
1930 if g_debug then
1931 write_to_log_file_n('Going to refresh MV '||l_mv_owner||'.'||p_mv_name||get_time);
1932 end if;
1933 --Enh#4239064: parallelism
1934 if l_method='c' then
1935 execute immediate 'alter session force parallel query';
1936 execute immediate 'alter session enable parallel dml';
1937 execute immediate 'alter table '||l_mv_owner||'.'||p_mv_name||' parallel';
1938 end if;
1939 begin
1940 DBMS_MVIEW.REFRESH(
1941 list=>l_mv_owner||'.'||p_mv_name,
1942 method=>l_method);
1943 --Enh#4239064: in Venu's doc it does not use parallelism paramenter
1944 --PARALLELISM=>l_parallel);
1945 if g_debug then
1946 write_to_log_file('Refresh complete '||get_time);
1947 end if;
1948 exception when others then
1949 --Enh#4239064: add commit here
1950 commit;
1951 BSC_IM_UTILS.g_status_message:=sqlerrm;
1952 if g_debug then
1953 write_to_log_file_n('Could not refresh MV '||sqlerrm);
1954 end if;
1955 if l_method='f' then
1956 if g_debug then
1957 write_to_log_file_n('One more try with full refresh');
1958 end if;
1959 l_method:='c';
1960 goto start_mv_refresh;
1961 end if;
1962 --error
1963 write_to_out_file('Error refreshing '||p_mv_name||' '||BSC_IM_UTILS.g_status_message);
1964 return false;
1965 end;
1966 --Enh#4239064: add commit here
1967 commit;
1968 l_end_time:=get_time;
1969 write_to_out_file('-----------------------------------------------');
1970 if l_method='f' then
1971 write_to_out_file('Refreshed '||p_mv_name||' FAST REFRESH '||
1972 ' Start time ->'||l_start_time||' End time->'||l_end_time);
1973 else
1974 write_to_out_file('Refreshed '||p_mv_name||' FULL REFRESH '||
1975 ' Start time ->'||l_start_time||' End time->'||l_end_time);
1976 end if;
1977 l_start_time:=get_time;
1978 if l_drop_index='Y' then
1979 if l_method='c' then
1980 if g_debug then
1981 write_to_log_file_n('Going to recreate the indexes that were dropped');
1982 end if;
1983 if BSC_MV_ADAPTER.create_mv_index(
1984 p_mv_name,
1985 l_mv_owner,
1986 p_kpi,
1987 'BSC',
1988 l_index_tablespace,
1989 l_index_storage,
1990 null,
1991 true)=false then
1992 return false;
1993 end if;
1994 l_end_time:=get_time;
1995 write_to_out_file('Recreated Indexes for '||p_mv_name||
1996 ' Start time ->'||l_start_time||' End time->'||l_end_time);
1997 end if;
1998 end if;
1999 --Enh#4239064: add commit here
2000 commit;
2001 if BSC_IM_UTILS.get_option_value(p_options,p_number_options,'ANALYZE')='Y' then
2002 l_start_time:=get_time;
2003 analyze_object(p_mv_name,l_mv_owner,null,l_parallel,null);
2004 --Enh#4239064: add commit here
2005 commit;
2006 if g_debug then
2007 write_to_log_file_n('Analyzed the MV'||get_time);
2008 end if;
2009 --analyze the mv log also if present
2010 if get_snapshot_log(p_mv_name,l_mv_owner,l_snp_log)=false then
2011 null;
2012 end if;
2013 if l_snp_log is not null then
2014 analyze_object(l_snp_log,l_mv_owner,null,l_parallel,null);
2015 end if;
2016 l_end_time:=get_time;
2017 write_to_out_file('Analyzed '||p_mv_name||' Start time ->'||l_start_time||' End time->'||l_end_time);
2018 end if;
2019 --Enh#4239064: add commit here and disable parallelism
2020 commit;
2021 if l_method='c' then
2022 execute immediate 'alter session disable parallel query';
2023 execute immediate 'alter session disable parallel dml';
2024 execute immediate 'alter table '||l_mv_owner||'.'||p_mv_name||' noparallel';
2025 end if;
2026 return true;
2027 Exception when others then
2028 g_status_message:=sqlerrm;
2029 write_to_log_file_n('Error in refresh_mv '||sqlerrm);
2030 return false;
2031 End;
2032
2033 function create_index(
2034 p_table_name varchar2,
2035 p_table_owner varchar2,
2036 p_index varchar_tabletype,
2037 p_uniqueness varchar_tabletype,
2038 p_tablespace varchar_tabletype,
2039 p_initial_extent number_tabletype,
2040 p_next_extent number_tabletype,
2041 p_max_extents number_tabletype,
2042 p_pct_increase number_tabletype,
2043 p_number_index number,
2044 ------
2045 p_ind_name varchar_tabletype,
2046 p_ind_col varchar_tabletype,
2047 p_number_ind_col number
2048 )return boolean is
2049 l_stmt varchar2(32000);
2050 Begin
2051 if g_debug then
2052 write_to_log_file_n('In create_index for '||p_table_owner||'.'||p_table_name||get_time);
2053 end if;
2054 for i in 1..p_number_index loop
2055 if g_debug then
2056 write_to_log_file_n('Going to create index '||p_table_owner||'.'||p_index(i));
2057 end if;
2058 l_stmt:='create ';
2059 if p_uniqueness(i)='UNIQUE' then
2060 l_stmt:=l_stmt||' unique ';
2061 end if;
2062 l_stmt:=l_stmt||'index '||p_table_owner||'.'||p_index(i)||' on '||p_table_owner||'.'||p_table_name||'(';
2063 for j in 1..p_number_ind_col loop
2064 if p_ind_name(j)=p_index(i) then
2065 l_stmt:=l_stmt||p_ind_col(j)||',';
2066 end if;
2067 end loop;
2068 l_stmt:=substr(l_stmt,1,length(l_stmt)-1)||')';
2069 l_stmt:=l_stmt||' tablespace '||p_tablespace(i)||' storage(INITIAL '||p_initial_extent(i)||' NEXT '||
2070 p_next_extent(i)||' MINEXTENTS 1 MAXEXTENTS '||p_max_extents(i)||' PCTINCREASE '||p_pct_increase(i)||')';
2071 if create_index(l_stmt,null)=false then
2072 return false;
2073 end if;
2074 end loop;
2075 return true;
2076 Exception when others then
2077 g_status_message:=sqlerrm;
2078 write_to_log_file_n('Error in create_index '||sqlerrm);
2079 return false;
2080 End;
2081
2082 function create_index(
2083 p_stmt varchar2,
2084 p_options varchar2
2085 )return boolean is
2086 Begin
2087 if g_debug then
2088 write_to_log_file_n(p_stmt||get_time);
2089 end if;
2090 begin
2091 execute immediate p_stmt;
2092 exception when others then
2093 if g_debug then
2094 write_to_log_file_n(sqlerrm);
2095 end if;
2096 if sqlcode=-01408 or sqlcode=-00955 then
2097 if g_debug then
2098 write_to_log_file_n('Ignore this error');
2099 end if;
2100 else
2101 g_status_message:=sqlerrm;
2102 return false;
2103 end if;
2104 end;
2105 if g_debug then
2106 write_to_log_file_n('Index created '||get_time);
2107 end if;
2108 return true;
2109 Exception when others then
2110 g_status_message:=sqlerrm;
2111 write_to_log_file_n('Error in create_index '||sqlerrm);
2112 return false;
2113 End;
2114
2115
2116 function drop_table(p_table varchar2,p_owner varchar2) return boolean is
2117 l_owner varchar2(200);
2118 Begin
2119 l_owner:=p_owner;
2120 if l_owner is null then
2121 l_owner:=get_table_owner(p_table);
2122 end if;
2123 if instr(p_table,'.')<>0 then
2124 g_stmt:='drop table '||p_table;
2125 else
2126 g_stmt:='drop table '||l_owner||'.'||p_table;
2127 end if;
2128 write_to_debug_n(g_stmt);
2129 execute immediate g_stmt;
2130 write_to_debug_n('Dropped table');
2131 return true;
2132 Exception when others then
2133 g_status_message:=sqlerrm;
2134 write_to_log_file_n('Error in drop_table '||sqlerrm);
2135 return false;
2136 End;
2137
2138 function get_table_properties(
2139 p_table varchar2,
2140 p_owner varchar2,
2141 p_columns out nocopy varchar_tabletype,
2142 p_columns_data_type out nocopy varchar_tabletype,
2143 p_number_columns out nocopy number
2144 )return boolean is
2145 l_owner varchar2(200);
2146 TYPE CurTyp IS REF CURSOR;
2147 cv CurTyp;
2148 Begin
2149 l_owner:=p_owner;
2150 p_number_columns:=1;
2151 if l_owner is null then
2152 l_owner:=get_table_owner(p_table);
2153 end if;
2154 g_stmt:='select '||
2155 'column_name, '||
2156 'decode(data_type,:1, data_type||''(''||data_length||'')'',data_type) date_type '||
2157 'from all_tab_columns '||
2158 'where table_name=:a '||
2159 'and owner=:b ';
2160 write_to_debug_n(g_stmt||' '||p_table||' '||l_owner);
2161 open cv for g_stmt using 'VARCHAR2', p_table,l_owner;
2162 loop
2163 fetch cv into p_columns(p_number_columns),p_columns_data_type(p_number_columns);
2164 exit when cv%notfound;
2165 p_number_columns:=p_number_columns+1;
2166 end loop;
2167 --Fix bug#3899842: close cursor
2168 close cv;
2169 p_number_columns:=p_number_columns-1;
2170 if g_debug then
2171 write_to_debug_n('The columns');
2172 for i in 1..p_number_columns loop
2173 write_to_debug(p_columns(i)||' '||p_columns_data_type(i));
2174 end loop;
2175 end if;
2176 return true;
2177 Exception when others then
2178 g_status_message:=sqlerrm;
2179 write_to_log_file_n('Error in get_table_properties '||sqlerrm);
2180 return false;
2181 End;
2182
2183 function drop_synonym(p_syn_name varchar2) return boolean is
2184 Begin
2185 g_stmt:='drop synonym '||p_syn_name;
2186 write_to_debug_n(g_stmt);
2187 execute immediate g_stmt;
2188 write_to_debug('Dropped');
2189 return true;
2190 Exception when others then
2191 g_status_message:=sqlerrm;
2192 -- Dont display synonym does not exist error
2193 if (sqlcode<>-1434) then
2194 write_to_log_file_n('Error in drop_synonym '||sqlerrm);
2195 end if;
2196 return false;
2197 End;
2198
2199 --get all the levels between 2 given levels in a dimension
2200 function get_all_levels_between(
2201 p_dim_name varchar2,
2202 p_apps_origin varchar2,
2203 p_level_1 varchar2,
2204 p_level_2 varchar2,
2205 p_child_level out nocopy varchar_tabletype,
2206 p_child_level_fk out nocopy varchar_tabletype,
2207 p_parent_level out nocopy varchar_tabletype,
2208 p_parent_level_pk out nocopy varchar_tabletype,
2209 p_hier out nocopy varchar_tabletype,
2210 p_number_hier out nocopy number
2211 )return boolean is
2212 -------------------------------------------------------------
2213 l_child_level varchar_tabletype;
2214 l_parent_level varchar_tabletype;
2215 l_child_fk varchar_tabletype;
2216 l_parent_pk varchar_tabletype;
2217 l_hier_rel varchar_tabletype;
2218 l_number_rels number;
2219 -------------------------------------------------------------
2220 l_hier_1 varchar_tabletype;
2221 l_number_hier_1 number;
2222 l_hier_2 varchar_tabletype;
2223 l_number_hier_2 number;
2224 l_hier_common varchar_tabletype;
2225 l_number_hier_common number;
2226 -------------------------------------------------------------
2227 ll_child_level varchar_tabletype;
2228 ll_child_level_fk varchar_tabletype;
2229 ll_parent_level varchar_tabletype;
2230 ll_parent_level_pk varchar_tabletype;
2231 ll_hier varchar_tabletype;
2232 ll_number_hier number;
2233 -------------------------------------------------------------
2234 l_description varchar_tabletype;
2235 l_property varchar_tabletype;
2236 Begin
2237 write_to_debug_n('In get_all_levels_between for '||p_dim_name||' level 1='||p_level_1||', level 2='||p_level_2);
2238 p_number_hier:=0;
2239 if p_level_1=p_level_2 then
2240 return true;
2241 end if;
2242 if BSC_IM_INT_MD.get_level_relation(
2243 p_dim_name,
2244 p_apps_origin,
2245 l_child_level,
2246 l_parent_level,
2247 l_child_fk,
2248 l_parent_pk,
2249 l_hier_rel,
2250 l_property,
2251 l_number_rels)=false then
2252 return false;
2253 end if;
2254 --find all hier that have these levels
2255 l_number_hier_1:=0;
2256 l_number_hier_2:=0;
2257 l_number_hier_common:=0;
2258 for i in 1..l_number_rels loop
2259 if l_child_level(i)=p_level_1 or l_parent_level(i)=p_level_1 then
2260 if in_array(l_hier_1,l_number_hier_1,l_hier_rel(i))=false then
2261 l_number_hier_1:=l_number_hier_1+1;
2262 l_hier_1(l_number_hier_1):=l_hier_rel(i);
2263 end if;
2264 end if;
2265 end loop;
2266 for i in 1..l_number_rels loop
2267 if l_child_level(i)=p_level_2 or l_parent_level(i)=p_level_2 then
2268 if in_array(l_hier_2,l_number_hier_2,l_hier_rel(i))=false then
2269 l_number_hier_2:=l_number_hier_2+1;
2270 l_hier_2(l_number_hier_2):=l_hier_rel(i);
2271 end if;
2272 end if;
2273 end loop;
2274 --find the common
2275 for i in 1..l_number_hier_1 loop
2276 if in_array(l_hier_2,l_number_hier_2,l_hier_1(i)) then
2277 l_number_hier_common:=l_number_hier_common+1;
2278 l_hier_common(l_number_hier_common):=l_hier_1(i);
2279 end if;
2280 end loop;
2281 if g_debug then
2282 write_to_log_file_n('The common hier');
2283 for i in 1..l_number_hier_common loop
2284 write_to_log_file(l_hier_common(i));
2285 end loop;
2286 end if;
2287 --for each of the hier, loop and see in which all both the level appear
2288 for i in 1..l_number_hier_common loop
2289 if get_all_levels_between(
2290 p_level_1,
2291 p_level_2,
2292 l_hier_common(i),
2293 l_child_level,
2294 l_parent_level,
2295 l_child_fk,
2296 l_parent_pk,
2297 l_hier_rel,
2298 l_number_rels,
2299 ll_child_level,
2300 ll_child_level_fk,
2301 ll_parent_level,
2302 ll_parent_level_pk,
2303 ll_hier,
2304 ll_number_hier)=false then
2305 return false;
2306 end if;
2307 for i in 1..ll_number_hier loop
2308 p_number_hier:=p_number_hier+1;
2309 p_child_level(p_number_hier):=ll_child_level(i);
2310 p_child_level_fk(p_number_hier):=ll_child_level_fk(i);
2311 p_parent_level(p_number_hier):=ll_parent_level(i);
2312 p_parent_level_pk(p_number_hier):=ll_parent_level_pk(i);
2313 p_hier(p_number_hier):=ll_hier(i);
2314 end loop;
2315 end loop;
2316 if g_debug then
2317 write_to_log_file_n('The levels in between '||p_level_1||' and '||p_level_2);
2318 for i in 1..p_number_hier loop
2319 write_to_log_file(p_child_level(i)||' '||p_child_level_fk(i)||' '||p_parent_level(i)||' '||
2320 p_parent_level_pk(i)||' '||p_hier(i));
2321 end loop;
2322 end if;
2323 return true;
2324 Exception when others then
2325 g_status_message:=sqlerrm;
2326 write_to_log_file_n('Error in get_all_levels_between '||sqlerrm);
2327 return false;
2328 End;
2329
2330 --given 2 levels and the hier, get all the levels in between and the relation
2331 function get_all_levels_between(
2332 p_level_1 varchar2,
2333 p_level_2 varchar2,
2334 p_hier varchar2,
2335 p_child_level varchar_tabletype,
2336 p_parent_level varchar_tabletype,
2337 p_child_fk varchar_tabletype,
2338 p_parent_pk varchar_tabletype,
2339 p_hier_rel varchar_tabletype,
2340 p_number_rels number,
2341 po_child_level out nocopy varchar_tabletype,
2342 po_child_level_fk out nocopy varchar_tabletype,
2343 po_parent_level out nocopy varchar_tabletype,
2344 po_parent_level_pk out nocopy varchar_tabletype,
2345 po_hier out nocopy varchar_tabletype,
2346 po_number_hier out nocopy number
2347 )return boolean is
2348 l_found boolean;
2349 l_exit boolean;
2350 l_child_level varchar2(200);
2351 l_parent_level varchar2(200);
2352 l_mark boolean_tabletype;
2353 Begin
2354 --we dont know if level 1 is child or level 2 is child
2355 po_number_hier:=0;
2356 l_child_level:=p_level_1;
2357 l_parent_level:=p_level_2;
2358 for m in 1..2 loop
2359 for i in 1..p_number_rels loop
2360 l_mark(i):=false;
2361 end loop;
2362 l_found:=false;
2363 l_exit:=false;
2364 loop
2365 l_exit:=true;
2366 for i in 1..p_number_rels loop
2367 if p_hier_rel(i)=p_hier and p_child_level(i)=l_child_level then
2368 l_exit:=false;
2369 l_mark(i):=true;
2370 l_child_level:=p_parent_level(i);
2371 if l_child_level=l_parent_level then
2372 l_exit:=true;
2373 l_found:=true;
2374 end if;
2375 exit;
2376 end if;
2377 end loop;
2378 if l_exit then
2379 exit;
2380 end if;
2381 end loop;
2382 if l_found then
2383 exit;
2384 else
2385 l_child_level:=p_level_2; --try with these
2386 l_parent_level:=p_level_1;
2387 end if;
2388 end loop;
2389 if l_found=false then
2390 write_to_debug_n('Could never find the levels in between '||p_level_1||' '||p_level_2||
2391 ' in hier '||p_hier);
2392 return false;
2393 end if;
2394 for i in 1..p_number_rels loop
2395 if l_mark(i) then
2396 po_number_hier:=po_number_hier+1;
2397 po_child_level(po_number_hier):=p_child_level(i);
2398 po_child_level_fk(po_number_hier):=p_child_fk(i);
2399 po_parent_level(po_number_hier):=p_parent_level(i);
2400 po_parent_level_pk(po_number_hier):=p_parent_pk(i);
2401 po_hier(po_number_hier):=p_hier_rel(i);
2402 end if;
2403 end loop;
2404 return true;
2405 Exception when others then
2406 g_status_message:=sqlerrm;
2407 write_to_log_file_n('Error in get_all_levels_between '||sqlerrm);
2408 return false;
2409 End;
2410
2411 function get_option_value(
2412 p_options varchar_tabletype,
2413 p_number_options number,
2414 p_check_option varchar2
2415 )return varchar2 is
2416 l_length number;
2417 l_value varchar2(20000);
2418 Begin
2419 if p_number_options is null or p_number_options=0 then
2420 return null;
2421 end if;
2422 l_value:=null;
2423 l_length:=length(p_check_option);
2424 for i in 1..p_number_options loop
2425 if substr(p_options(i),1,l_length)=p_check_option then
2426 if substr(p_options(i),1,l_length+1)=p_check_option||'=' then
2427 l_value:=substr(p_options(i),l_length+2);
2428 else
2429 l_value:='Y';
2430 exit;
2431 end if;
2432 end if;
2433 end loop;
2434 if g_debug then
2435 write_to_log_file(p_check_option||'='||l_value);
2436 end if;
2437 return l_value;
2438 Exception when others then
2439 BSC_IM_UTILS.g_status_message:=sqlerrm;
2440 write_to_log_file_n('Error in get_option_value '||sqlerrm);
2441 return null;
2442 End;
2443
2444 function get_option_value(
2445 p_options varchar2,
2446 p_separator varchar2,
2447 p_check_option varchar2
2448 )return varchar2 is
2449 l_array varchar_tabletype;
2450 l_number_array number;
2451 l_value varchar2(20000);
2452 Begin
2453 if parse_values(p_options,p_separator,l_array,l_number_array)=false then
2454 return null;
2455 end if;
2456 l_value:=get_option_value(l_array,l_number_array,p_check_option);
2457 return l_value;
2458 Exception when others then
2459 BSC_IM_UTILS.g_status_message:=sqlerrm;
2460 write_to_log_file_n('Error in get_option_value '||sqlerrm);
2461 return null;
2462 End;
2463
2464 function get_table_storage(
2465 p_table varchar2,
2466 p_owner varchar2,
2467 p_table_space out nocopy varchar2,
2468 p_initial_extent out nocopy number,
2469 p_next_extent out nocopy number,
2470 p_pct_free out nocopy number,
2471 p_pct_used out nocopy number,
2472 p_pct_increase out nocopy number,
2473 p_max_extents out nocopy number,
2474 p_avg_row_len out nocopy number
2475 ) return boolean is
2476 l_stmt varchar2(2000);
2477 l_owner varchar2(100);
2478 TYPE CurTyp IS REF CURSOR;
2479 cv CurTyp;
2480 Begin
2481 l_owner:=p_owner;
2482 if l_owner is null then
2483 l_owner:=get_table_owner(p_table);
2484 end if;
2485 l_stmt:='select tablespace_name,initial_extent,next_extent,pct_free,pct_used,pct_increase,max_extents,'||
2486 'avg_row_len from '||
2487 'all_tables where table_name=:a and owner=:b';
2488 if g_debug then
2489 write_to_log_file_n('Going to execute '||l_stmt||' using '||p_table||','||l_owner);
2490 end if;
2491 open cv for l_stmt using p_table,l_owner;
2492 fetch cv into p_table_space,p_initial_extent,p_next_extent,p_pct_free,p_pct_used,p_pct_increase,p_max_extents,
2493 p_avg_row_len;
2494 close cv;
2495 return true;
2496 Exception when others then
2497 g_status_message:=sqlerrm;
2498 write_to_log_file_n('Error in get_table_storage '||sqlerrm);
2499 return false;
2500 End;
2501
2502 function get_table_indexes(
2503 p_table_name varchar2,
2504 p_table_owner varchar2,
2505 p_index out nocopy varchar_tabletype,
2506 p_uniqueness out nocopy varchar_tabletype,
2507 p_tablespace out nocopy varchar_tabletype,
2508 p_initial_extent out nocopy number_tabletype,
2509 p_next_extent out nocopy number_tabletype,
2510 p_max_extents out nocopy number_tabletype,
2511 p_pct_increase out nocopy number_tabletype,
2512 p_number_index out nocopy number
2513 ) return boolean is
2514 l_stmt varchar2(2000);
2515 l_owner varchar2(100);
2516 TYPE CurTyp IS REF CURSOR;
2517 cv CurTyp;
2518 Begin
2519 l_owner:=p_table_owner;
2520 p_number_index:=0;
2521 if l_owner is null then
2522 l_owner:=get_table_owner(p_table_name);
2523 end if;
2524 l_stmt:='select index_name,uniqueness,tablespace_name,initial_extent,next_extent,max_extents,pct_increase '||
2525 'from all_indexes where table_name=:1 and table_owner=:2';
2526 if g_debug then
2527 write_to_log_file_n(l_stmt||' '||p_table_name||' '||l_owner);
2528 end if;
2529 p_number_index:=1;
2530 open cv for l_stmt using p_table_name,l_owner;
2531 loop
2532 fetch cv into
2533 p_index(p_number_index),
2534 p_uniqueness(p_number_index),
2535 p_tablespace(p_number_index),
2536 p_initial_extent(p_number_index),
2537 p_next_extent(p_number_index),
2538 p_max_extents(p_number_index),
2539 p_pct_increase(p_number_index);
2540 exit when cv%notfound;
2541 p_number_index:=p_number_index+1;
2542 end loop;
2543 p_number_index:=p_number_index-1;
2544 close cv;
2545 if g_debug then
2546 write_to_log_file_n('Result');
2547 for i in 1..p_number_index loop
2548 write_to_log_file(p_index(i)||' '||p_uniqueness(i)||' '||p_tablespace(i)||' '||p_initial_extent(i)||' '||
2549 p_next_extent(i)||' '||p_max_extents(i)||' '||p_pct_increase(i));
2550 end loop;
2551 end if;
2552 return true;
2553 Exception when others then
2554 g_status_message:=sqlerrm;
2555 write_to_log_file_n('Error in get_table_indexes '||sqlerrm);
2556 return false;
2557 End;
2558
2559 function get_table_indexes(
2560 p_table_name varchar2,
2561 p_table_owner varchar2,
2562 p_index out nocopy varchar_tabletype,
2563 p_uniqueness out nocopy varchar_tabletype,
2564 p_tablespace out nocopy varchar_tabletype,
2565 p_initial_extent out nocopy number_tabletype,
2566 p_next_extent out nocopy number_tabletype,
2567 p_max_extents out nocopy number_tabletype,
2568 p_pct_increase out nocopy number_tabletype,
2569 p_number_index out nocopy number,
2570 p_ind_name out nocopy varchar_tabletype,
2571 p_ind_col out nocopy varchar_tabletype,
2572 p_number_ind_col out nocopy number
2573 ) return boolean is
2574 l_stmt varchar2(2000);
2575 l_owner varchar2(100);
2576 TYPE CurTyp IS REF CURSOR;
2577 cv CurTyp;
2578 Begin
2579 l_owner:=p_table_owner;
2580 p_number_index:=0;
2581 if l_owner is null then
2582 l_owner:=get_table_owner(p_table_name);
2583 end if;
2584 if get_table_indexes(
2585 p_table_name,
2586 l_owner,
2587 p_index,
2588 p_uniqueness,
2589 p_tablespace,
2590 p_initial_extent,
2591 p_next_extent,
2592 p_max_extents,
2593 p_pct_increase,
2594 p_number_index)=false then
2595 return false;
2596 end if;
2597 p_number_ind_col:=1;
2598 l_stmt:='select index_name,column_name from all_ind_columns where index_name=:1 and index_owner=:2 '||
2599 'order by column_position';
2600 for i in 1..p_number_index loop
2601 if g_debug then
2602 write_to_log_file_n(l_stmt||' '||p_index(i));
2603 end if;
2604 open cv for l_stmt using p_index(i),l_owner;
2605 loop
2606 fetch cv into p_ind_name(p_number_ind_col),p_ind_col(p_number_ind_col);
2607 exit when cv%notfound;
2608 p_number_ind_col:=p_number_ind_col+1;
2609 end loop;
2610 --Fix bug#3899842: close cursor
2611 close cv;
2612 end loop;
2613 p_number_ind_col:=p_number_ind_col-1;
2614 if g_debug then
2615 write_to_log_file_n('Result');
2616 for i in 1..p_number_ind_col loop
2617 write_to_log_file(p_ind_name(i)||' '||p_ind_col(i));
2618 end loop;
2619 end if;
2620 return true;
2621 Exception when others then
2622 g_status_message:=sqlerrm;
2623 write_to_log_file_n('Error in get_table_indexes '||sqlerrm);
2624 return false;
2625 End;
2626
2627 function get_synonym_property(
2628 p_synonym varchar2,
2629 p_syn_owner out nocopy varchar2,
2630 p_syn_object out nocopy varchar2
2631 )return boolean is
2632 TYPE CurTyp IS REF CURSOR;
2633 cv CurTyp;
2634 Begin
2635 g_stmt:='select TABLE_NAME,TABLE_OWNER from user_synonyms where synonym_name=:1';
2636 if g_debug then
2637 write_to_log_file_n(g_stmt||' '||p_synonym);
2638 end if;
2639 open cv for g_stmt using p_synonym;
2640 fetch cv into p_syn_object,p_syn_owner;
2641 if g_debug then
2642 write_to_log_file(p_syn_owner||' '||p_syn_object);
2643 end if;
2644 close cv;
2645 return true;
2646 Exception when others then
2647 g_status_message:=sqlerrm;
2648 write_to_log_file_n('Error in get_synonym_property '||sqlerrm);
2649 return false;
2650 End;
2651
2652 function create_synonym(
2653 p_synonym varchar2,
2654 p_syn_owner varchar2,
2655 p_syn_object varchar2
2656 )return boolean is
2657 Begin
2658 --if the synonym exists, drop it first
2659 if drop_synonym(p_synonym)=false then
2660 null;
2661 end if;
2662 g_stmt:='create synonym '||p_synonym||' for '||p_syn_owner||'.'||p_syn_object;
2663 if g_debug then
2664 write_to_log_file_n(g_stmt);
2665 end if;
2666 execute immediate g_stmt;
2667 return true;
2668 Exception when others then
2669 g_status_message:=sqlerrm;
2670 write_to_log_file_n('Error in create_synonym '||sqlerrm);
2671 return false;
2672 End;
2673
2674 procedure set_trace is
2675 Begin
2676 execute immediate 'alter session set sql_trace=true';
2677 Exception when others then
2678 BSC_IM_UTILS.g_status_message:=sqlerrm;
2679 write_to_log_file_n('Error in set_trace '||sqlerrm);
2680 End;
2681
2682 procedure analyze_object(
2683 p_object varchar2,
2684 p_owner varchar2,
2685 p_sample number,
2686 p_parallel number,
2687 p_partname varchar2
2688 ) is
2689 l_owner varchar2(200);
2690 l_errbuf varchar2(2000);
2691 l_retcode varchar2(2000);
2692 Begin
2693 l_owner:=p_owner;
2694 if l_owner is null then
2695 l_owner:=get_table_owner(p_object);
2696 end if;
2697 if g_debug then
2698 write_to_log_file_n('Analyze '||l_owner||'.'||p_object);
2699 end if;
2700 FND_STATS.GATHER_TABLE_STATS(l_errbuf,l_retcode,l_owner,p_object,null,null,p_partname);
2701 if g_debug then
2702 write_to_log_file_n('Completed Analyzing '||l_owner||'.'||p_object);
2703 end if;
2704 Exception when others then
2705 g_status_message:=sqlerrm;
2706 write_to_log_file_n('Error in analyze_object '||sqlerrm);
2707 End;
2708
2709 function get_object_name(p_object_name varchar2) return varchar2 is
2710 Begin
2711 return substr(p_object_name,instr(p_object_name,'.')+1);
2712 Exception when others then
2713 g_status_message:=sqlerrm;
2714 write_to_log_file_n('Error in get_object_name '||sqlerrm);
2715 return null;
2716 End;
2717
2718 function get_corrected_map_table(
2719 p_map_table varchar2,--could be sql stmt
2720 p_map_table_list varchar2,
2721 p_options varchar_tabletype,
2722 p_number_options number,
2723 p_apps_src varchar2,
2724 p_olap_target varchar2,
2725 p_corr_table_name out nocopy varchar2,
2726 p_corr_table_list out nocopy varchar_tabletype,
2727 p_original_table_list out nocopy varchar_tabletype,
2728 p_number_corr_table out nocopy number
2729 )return boolean is
2730 -----------------------------------------------------------
2731 l_table_list varchar_tabletype;
2732 -----------------------------------------------------------
2733 Begin
2734 if g_debug then
2735 write_to_log_file_n('In get_corrected_map_table '||p_apps_src||' '||p_olap_target);
2736 end if;
2737 if p_apps_src='BSC' and p_olap_target='MV' then
2738 return get_corrected_map_table_bsc_mv(p_map_table,p_map_table_list,p_options,p_number_options,
2739 p_corr_table_name,p_corr_table_list,p_original_table_list,p_number_corr_table);
2740 else
2741 p_corr_table_name:=p_map_table;
2742 if parse_values(p_map_table_list,',',p_corr_table_list,p_number_corr_table)=false then
2743 return false;
2744 end if;
2745 for i in 1..p_number_corr_table loop
2746 p_original_table_list(i):=p_corr_table_list(i);
2747 end loop;
2748 end if;
2749 return true;
2750 Exception when others then
2751 g_status_message:=sqlerrm;
2752 write_to_log_file_n('Error in get_corrected_map_table '||sqlerrm);
2753 return false;
2754 End;
2755
2756 function get_corrected_map_table_bsc_mv(
2757 p_map_table varchar2,--could be sql stmt
2758 p_map_table_list varchar2,
2759 p_options varchar_tabletype,
2760 p_number_options number,
2761 p_corr_table_name out nocopy varchar2,
2762 p_corr_table_list out nocopy varchar_tabletype,
2763 p_original_table_list out nocopy varchar_tabletype,
2764 p_number_corr_table out nocopy number
2765 )return boolean is
2766 l_table_list varchar_tabletype;
2767 l_append_string varchar2(200);
2768 l_syn_owner varchar2(200);
2769 l_syn_object varchar2(200);
2770 l_object_type varchar2(200);
2771 Begin
2772 if g_debug then
2773 write_to_log_file_n('In get_corrected_map_table_bsc_mv');
2774 end if;
2775 p_number_corr_table:=0;
2776 if parse_values(p_map_table_list,',',l_table_list,p_number_corr_table)=false then
2777 return false;
2778 end if;
2779 p_corr_table_name:=p_map_table;
2780 l_append_string:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'MV NAME APPEND');
2781 for i in 1..p_number_corr_table loop
2782 p_corr_table_list(i):=l_table_list(i);
2783 p_original_table_list(i):=l_table_list(i);
2784 if get_synonym_property(l_table_list(i),l_syn_owner,l_syn_object) then
2785 l_object_type:=get_object_type(l_syn_object,l_syn_owner);
2786 if l_object_type='MATERIALIZED VIEW' then
2787 p_corr_table_list(i):=l_syn_owner||'.'||l_syn_object;
2788 else
2789 l_object_type:=get_object_type(l_syn_object||l_append_string,l_syn_owner);
2790 if l_object_type='MATERIALIZED VIEW' then
2791 p_corr_table_list(i):=l_syn_owner||'.'||l_syn_object||l_append_string;
2792 end if;
2793 end if;
2794 end if;
2795 if p_corr_table_list(i)<>l_table_list(i) then
2796 p_corr_table_name:=replace(p_corr_table_name,l_table_list(i),p_corr_table_list(i));
2797 end if;
2798 end loop;
2799 if g_debug then
2800 write_to_log_file_n('p_corr_table_name='||p_corr_table_name);
2801 for i in 1..p_number_corr_table loop
2802 write_to_log_file(p_corr_table_list(i));
2803 end loop;
2804 end if;
2805 return true;
2806 Exception when others then
2807 g_status_message:=sqlerrm;
2808 write_to_log_file_n('Error in get_corrected_map_table_bsc_mv '||sqlerrm);
2809 return false;
2810 End;
2811
2812 function get_db_version return varchar2 is
2813 l_compatibility varchar2(200);
2814 l_ver number;
2815 Begin
2816 if g_db_version is null then
2817 DBMS_UTILITY.DB_VERSION(g_db_version,l_compatibility);
2818 l_ver := to_number(substr(g_db_version,1,instr(g_db_version,'.'))||replace(substr(g_db_version,instr(g_db_version,'.')+1),'.'));
2819 if g_debug then
2820 write_to_log_file(g_db_version);
2821 end if;
2822 if l_ver>=9 then
2823 g_db_version:='9i';
2824 else
2825 g_db_version:='8i';
2826 end if;
2827 end if;
2828 if g_debug then
2829 write_to_log_file('DB version='||g_db_version);
2830 end if;
2831 return g_db_version;
2832 Exception when others then
2833 g_status_message:=sqlerrm;
2834 write_to_log_file_n('Error in get_db_version '||sqlerrm);
2835 return null;
2836 End;
2837
2838 function drop_object(
2839 p_object varchar2,
2840 p_owner varchar2
2841 ) return boolean is
2842 l_object_type varchar2(200);
2843 l_owner varchar2(200);
2844 l_object varchar2(200);
2845 Begin
2846 l_owner:=p_owner;
2847 l_object:=p_object;
2848 if instr(p_object,'.')<>0 then
2849 l_owner:=substr(p_object,1,instr(p_object,'.')-1);
2850 l_object:=substr(p_object,instr(p_object,'.')+1);
2851 else
2852 if l_owner is null then
2853 l_owner:=get_object_owner(l_object);
2854 end if;
2855 end if;
2856 l_object_type:=get_object_type(l_object,l_owner);
2857 g_stmt:='drop '||l_object_type||' '||l_owner||'.'||l_object;
2858 if g_debug then
2859 write_to_log_file_n(g_stmt);
2860 end if;
2861 execute immediate g_stmt;
2862 return true;
2863 Exception when others then
2864 g_status_message:=sqlerrm;
2865 write_to_log_file_n('Error in drop_object '||sqlerrm);
2866 return false;
2867 End;
2868
2869 FUNCTION is_mview(
2870 p_mview VARCHAR2,
2871 p_owner VARCHAR2
2872 )RETURN BOOLEAN is
2873 TYPE CurTyp IS REF CURSOR;
2874 cv CurTyp;
2875 l_val NUMBER;
2876 l_owner VARCHAR2(256);
2877
2878 BEGIN
2879 l_owner := p_owner;
2880 IF(l_owner IS NULL) THEN
2881 l_owner := get_mv_owner(p_mview);
2882 END IF;
2883 g_stmt:='select 1 from all_mviews where mview_name=:1 and owner=:2';
2884 IF g_debug THEN
2885 write_to_log_file_n(g_stmt||' '||p_mview||' '||l_owner);
2886 END IF;
2887 OPEN cv FOR g_stmt USING p_mview,l_owner;
2888 FETCH cv INTO l_val;
2889 CLOSE cv;
2890 IF l_val=1 THEN
2891 RETURN TRUE;
2892 ELSE
2893 RETURN FALSE;
2894 END IF;
2895 EXCEPTION WHEN others THEN
2896 g_status_message:=SQLERRM;
2897 write_to_log_file_n('Error in is_mview '||SQLERRM);
2898 RETURN FALSE;
2899 END;
2900
2901 /*
2902 -1 : error
2903 1 : no data
2904 2 : data
2905 */
2906 function does_table_have_data(p_table varchar2, p_where varchar2) return number is
2907 l_stmt varchar2(2000);
2908 TYPE CurTyp IS REF CURSOR;
2909 cv CurTyp;
2910 l_res number:=null;
2911 Begin
2912 if g_debug then
2913 write_to_log_file_n('In does_table_have_data , table is '||p_table||' and where clause is '||p_where);
2914 end if;
2915 if p_where is null then
2916 l_stmt:='select 1 from '||p_table||' where rownum=1';
2917 else
2918 l_stmt:='select 1 from '||p_table||' where '||p_where||' and rownum=1';
2919 end if;
2920 open cv for l_stmt;
2921 fetch cv into l_res;
2922 close cv;
2923 if l_res is null then
2924 if g_debug then
2925 write_to_log_file('No');
2926 end if;
2927 return 1;
2928 end if;
2929 if g_debug then
2930 write_to_log_file('Yes');
2931 end if;
2932 return 2;
2933 Exception when others then
2934 g_status_message:=sqlerrm;
2935 write_to_log_file_n('Error in does_table_have_data '||sqlerrm);
2936 return -1;
2937 End;
2938
2939 --with bind variables
2940 function does_table_have_data(p_table varchar2, p_where varchar2,p_bind varchar2) return boolean is
2941 l_stmt varchar2(2000);
2942 TYPE CurTyp IS REF CURSOR;
2943 cv CurTyp;
2944 l_res number:=null;
2945 Begin
2946 l_stmt:='select 1 from '||p_table||' where '||p_where||' and rownum=1';
2947 open cv for l_stmt using p_bind;
2948 fetch cv into l_res;
2949 close cv;
2950 if l_res is null then
2951 if g_debug then
2952 write_to_log_file('No');
2953 end if;
2954 return false;
2955 end if;
2956 if g_debug then
2957 write_to_log_file('Yes');
2958 end if;
2959 return true;
2960 Exception when others then
2961 write_to_log_file_n('Error in does_table_have_data '||sqlerrm);
2962 raise;
2963 End;
2964
2965 function truncate_table(p_table varchar2, p_owner varchar2) return boolean is
2966 l_stmt varchar2(1000);
2967 l_owner varchar2(200);
2968 Begin
2969 if g_debug then
2970 write_to_log_file_n('In truncate_table, p_table='||p_table||', p_owner='||p_owner);
2971 end if;
2972 if p_owner is null or instr(p_table,'.')<>0 then
2973 --if p_owner is null then
2974 if instr(p_table,'.')<>0 then
2975 l_stmt:='truncate table '||p_table;
2976 else
2977 l_owner:=get_table_owner(p_table);
2978 l_stmt:='truncate table '||l_owner||'.'||p_table;
2979 end if;
2980 else
2981 l_stmt:='truncate table '||p_owner||'.'||p_table;
2982 end if;
2983 execute immediate l_stmt;
2984 return true;
2985 Exception when others then
2986 g_status_message:=sqlerrm;
2987 write_to_log_file_n('Exception in truncate_table '||sqlerrm);
2988 return false;
2989 End;
2990
2991
2992 --===========================================================================+
2993 --
2994 -- Name: IsNumber
2995 -- Description: Returns true if the string is a number
2996 -- Parameters:
2997 --============================================================================*/
2998 FUNCTION IsNumber (str IN VARCHAR2) RETURN BOOLEAN IS
2999 l_temp NUMBER := -1;
3000 BEGIN
3001 l_temp:= to_number(str);
3002 return true;
3003 exception when others then
3004 return false;
3005 END;
3006
3007 -- added by ARSANTHA, for bug 3906968
3008
3009 Function get_measures_in_formula(p_measures IN OUT NOCOPY varchar_tabletype,
3010 Expresion IN VARCHAR2) return NUMBER IS
3011 i NUMBER;
3012 p_num_measures number;
3013 l_fields dbms_sql.varchar2_table;
3014 l_num_fields NUMBER;
3015 l_formula VARCHAR2(1000);
3016 cursor cReservedFunctions IS
3017 SELECT WORD FROM BSC_DB_RESERVED_WORDS WHERE WORD IS NOT NULL AND TYPE = 1;
3018 cursor cReservedOperators IS
3019 SELECT WORD FROM BSC_DB_RESERVED_WORDS WHERE WORD IS NOT NULL AND TYPE = 2;
3020 l_reserved VARCHAR2(100);
3021 l_reserved_functions varchar_tabletype;
3022 BEGIN
3023 l_formula := Expresion;
3024 --Replace the operators by ' '
3025 open cReservedOperators ;
3026 LOOP
3027 fetch cReservedOperators into l_reserved;
3028 exit when cReservedOperators%notfound;
3029 l_formula := Replace(l_formula, l_reserved, ' ');
3030 END LOOP;
3031 close cReservedOperators ;
3032 open cReservedFunctions ;
3033 LOOP
3034 fetch cReservedFunctions into l_reserved;
3035 exit when cReservedFunctions%notfound;
3036 l_reserved_functions(l_reserved_functions.count+1) := l_reserved;
3037 END LOOP;
3038 close cReservedFunctions ;
3039 --Break down the expression which is separated by ' '
3040 l_num_fields := BSC_MO_HELPER_PKG.DecomposeString(l_formula, ' ', l_fields);
3041 l_num_fields := l_fields.count;
3042 p_num_measures := 0;
3043 i:= l_fields.first;
3044 LOOP
3045 EXIT WHEN l_fields.count = 0;
3046 If l_fields(i) IS NOT NULL Then
3047 If in_array(l_Reserved_Functions, l_Reserved_Functions.count, l_fields(i)) = false Then
3048 --The word l_fields(i) is not a reserved function
3049 If UPPER(l_fields(i)) <> 'NULL' Then
3050 --the word is not 'NULL'
3051 If Not BSC_MO_HELPER_PKG.IsNumber(l_fields(i)) Then
3052 --the word is not a constant
3053 p_num_measures := p_num_measures + 1;
3054 p_measures(p_num_measures) := l_fields(i);
3055 END IF;
3056 END IF;
3057 END IF;
3058 END IF;
3059 EXIT WHEN i = l_fields.last;
3060 i := l_fields.next(i);
3061 END LOOP;
3062
3063 for i in 1..p_measures.count loop
3064 write_to_log_file_n(i||' '||p_measures(i));
3065 end loop;
3066 return p_num_measures;
3067
3068 EXCEPTION WHEN OTHERS THEN
3069 write_to_log_file_n('Exception in get_measures_in_formula:'||sqlerrm);
3070 raise;
3071 End;
3072
3073 -- changed by ARSANTHA, for bug 3906968
3074 function find_aggregation_columns(
3075 p_formula varchar2,
3076 p_columns out nocopy varchar_tabletype,
3077 p_number_columns out nocopy number
3078 )return boolean is
3079 --------------------------------------
3080 Begin
3081 p_number_columns:=0;
3082 p_number_columns:=get_measures_in_formula(p_columns, p_formula);
3083 return true;
3084 Exception when others then
3085 g_status_message:=sqlerrm;
3086 write_to_log_file_n('Exception in find_aggregation_columns '||sqlerrm);
3087 return false;
3088 End;
3089
3090 function get_bsc_owner return varchar2 is
3091 l_bsc_owner varchar2(200);
3092 Begin
3093 if BSC_IM_UTILS.get_db_user('BSC',l_bsc_owner)=false then
3094 l_bsc_owner:='BSC';
3095 end if;
3096 if l_bsc_owner is null then
3097 l_bsc_owner:='BSC';
3098 end if;
3099 return l_bsc_owner;
3100 Exception when others then
3101 BSC_IM_UTILS.g_status_message:=sqlerrm;
3102 raise;
3103 End;
3104
3105 function get_lang return varchar2 is
3106 Begin
3107 return userenv('LANG');
3108 Exception when others then
3109 BSC_IM_UTILS.g_status_message:=sqlerrm;
3110 raise;
3111 End;
3112
3113 function is_like(p_string varchar2,p_comp_string varchar2) return boolean is
3114 l_length number;
3115 Begin
3116 if p_comp_string is null or p_string is null then
3117 return false;
3118 end if;
3119 l_length:=length(p_comp_string);
3120 if l_length=0 then
3121 return false;
3122 end if;
3123 if l_length>length(p_string) then
3124 return false;
3125 end if;
3126 if substr(p_string,1,l_length)=p_comp_string then
3127 return true;
3128 else
3129 return false;
3130 end if;
3131 Exception when others then
3132 BSC_IM_UTILS.g_status_message:=sqlerrm;
3133 raise;
3134 End;
3135
3136 function execute_immediate(
3137 p_stmt varchar2,
3138 p_options varchar2
3139 )return boolean is
3140 Begin
3141 execute immediate p_stmt;
3142 return true;
3143 Exception when others then
3144 g_status_message:=sqlerrm;
3145 write_to_log_file_n('Exception in execute_immediate '||sqlerrm);
3146 return false;
3147 End;
3148
3149 /*
3150 if p_cube is null, then see if any cube is present
3151 else check for the particular cube
3152 */
3153 function is_cube_present(
3154 p_cube varchar2,
3155 p_apps_origin varchar2
3156 )return boolean is
3157 l_cube_id number;
3158 l_cube_periodicity varchar2(2000);
3159 l_description varchar2(2000);
3160 l_property varchar2(20000);
3161 l_present boolean;
3162 Begin
3163 if g_debug then
3164 write_to_log_file_n('In is_cube_present '||p_cube||' '||p_apps_origin);
3165 end if;
3166 l_present:=false;
3167 if p_cube is null then
3168 if BSC_IM_INT_MD.get_cube_count>0 then
3169 l_present:=true;
3170 else
3171 l_present:=false;
3172 end if;
3173 else
3174 if BSC_IM_INT_MD.get_cube(p_cube,p_apps_origin,l_cube_id,l_cube_periodicity,l_description,l_property)=false then
3175 l_present:=false;
3176 end if;
3177 if l_cube_id is not null then
3178 l_present:=true;
3179 else
3180 l_present:=false;
3181 end if;
3182 end if;
3183 if g_debug then
3184 if l_present then
3185 write_to_log_file('Yes');
3186 else
3187 write_to_log_file('No');
3188 end if;
3189 end if;
3190 return l_present;
3191 Exception when others then
3192 g_status_message:=sqlerrm;
3193 write_to_log_file_n('Exception in is_cube_present '||sqlerrm);
3194 return false;
3195 End;
3196
3197 function is_view_present(p_view_like varchar2) return boolean is
3198 l_present boolean;
3199 l_res number;
3200 ---------------------------
3201 cursor c1(p_view varchar2) is select 1 from user_views where view_name like p_view;
3202 ---------------------------
3203 Begin
3204 if g_debug then
3205 write_to_log_file_n('select 1 from user_views where view_name like '||p_view_like);
3206 end if;
3207 open c1(p_view_like);
3208 fetch c1 into l_res;
3209 close c1;
3210 if l_res=1 then
3211 if g_debug then
3212 write_to_log_file('View Present');
3213 end if;
3214 l_present:=true;
3215 else
3216 if g_debug then
3217 write_to_log_file('View NOT Present');
3218 end if;
3219 l_present:=false;
3220 end if;
3221 return l_present;
3222 Exception when others then
3223 g_status_message:=sqlerrm;
3224 write_to_log_file_n('Exception in is_cube_present '||sqlerrm);
3225 return false;
3226 End;
3227
3228 /*
3229 given a table or mv, returns the parent mv for the same
3230 */
3231 function get_parent_mv(
3232 p_mv varchar2,
3233 p_parent_mv out nocopy varchar_tabletype,
3234 p_number_parent_mv out nocopy number
3235 )return boolean is
3236 ----------------------------
3237 cursor c1 (p_mv varchar2,p_owner varchar2)
3238 is select mview_name from all_mview_detail_relations where detailobj_name=p_mv and owner=p_owner;
3239 ----------------------------
3240 l_owner varchar2(200);
3241 Begin
3242 l_owner:=get_table_owner(p_mv);
3243 p_number_parent_mv:=1;
3244 open c1(p_mv,l_owner);
3245 loop
3246 fetch c1 into p_parent_mv(p_number_parent_mv);
3247 exit when c1%notfound;
3248 p_number_parent_mv:=p_number_parent_mv+1;
3249 end loop;
3250 close c1;
3251 p_number_parent_mv:=p_number_parent_mv-1;
3252 if g_debug then
3253 write_to_log_file('Results');
3254 for i in 1..p_number_parent_mv loop
3255 write_to_log_file(p_parent_mv(i));
3256 end loop;
3257 end if;
3258 return true;
3259 Exception when others then
3260 g_status_message:=sqlerrm;
3261 write_to_log_file_n('Exception in get_parent_mv '||sqlerrm);
3262 return false;
3263 End;
3264
3265 /*
3266 given a mv, returns the child objects for the same
3267 */
3268 function get_child_mv(
3269 p_mv varchar2,
3270 p_child_mv out nocopy varchar_tabletype,
3271 p_number_child_mv out nocopy number
3272 )return boolean is
3273 ----------------------------
3274 cursor c1 (p_mv varchar2,p_owner varchar2)
3275 is select detailobj_name from all_mview_detail_relations where mview_name=p_mv and owner=p_owner;
3276 ----------------------------
3277 l_owner varchar2(200);
3278 Begin
3279 l_owner:=get_table_owner(p_mv);
3280 p_number_child_mv:=1;
3281 open c1(p_mv,l_owner);
3282 loop
3283 fetch c1 into p_child_mv(p_number_child_mv);
3284 exit when c1%notfound;
3285 p_number_child_mv:=p_number_child_mv+1;
3286 end loop;
3287 close c1;
3288 p_number_child_mv:=p_number_child_mv-1;
3289 if g_debug then
3290 write_to_log_file('Results');
3291 for i in 1..p_number_child_mv loop
3292 write_to_log_file(p_child_mv(i));
3293 end loop;
3294 end if;
3295 return true;
3296 Exception when others then
3297 g_status_message:=sqlerrm;
3298 write_to_log_file_n('Exception in get_child_mv '||sqlerrm);
3299 return false;
3300 End;
3301
3302 function is_parent_of_type_present(
3303 p_object varchar2,
3304 p_parent_type varchar2
3305 )return boolean is
3306 ----------------------------
3307 --cursor c1 (p_object varchar2,p_parent_type varchar2)
3308 --is select 1 from all_dependencies where referenced_name=p_object and type=p_parent_type and
3309 --owner=g_apps_owner;
3310
3311 cursor cHigherLevel(p_lower_level varchar2) is
3312 select distinct substr(rels.table_name, 1, instr(rels.table_name, '_', -1))||'MV' from
3313 bsc_db_Tables_rels rels
3314 where rels.source_table_name like p_lower_level
3315 and rels.table_name not like p_lower_level
3316 and rels.table_name like 'BSC_S%';
3317
3318 cursor cView(p_higher_level varchar2) is
3319 select 1 from user_objects where object_name = p_higher_level and object_type='VIEW';
3320 ----------------------------
3321 l_res number;
3322 l_higher_level varchar2(100);
3323 Begin
3324 if g_apps_owner is null then
3325 g_apps_owner:=get_apps_owner;
3326 end if;
3327
3328 open cHigherLevel(substr(p_object, 1, instr(p_object, '_', -1))||'%');
3329 fetch cHigherLevel into l_higher_level;
3330 close cHigherLevel;
3331
3332 open cView(l_higher_level);
3333 fetch cView into l_res;
3334 close cView;
3335
3336 if l_res=1 then
3337 if g_debug then
3338 write_to_log_file('View is parent');
3339 end if;
3340 return true;
3341 else
3342 if g_debug then
3343 write_to_log_file('View is NOT parent');
3344 end if;
3345 return false;
3346 end if;
3347 return true;
3348 Exception when others then
3349 g_status_message:=sqlerrm;
3350 write_to_log_file_n('Exception in is_parent_of_type_present '||sqlerrm);
3351 return false;
3352 End;
3353
3354 function get_apps_owner return varchar2 is
3355 Begin
3356 return bsc_apps.get_user_schema('APPS');
3357 Exception when others then
3358 g_status_message:=sqlerrm;
3359 write_to_log_file_n('Exception in get_apps_owner '||sqlerrm);
3360 return null;
3361 End;
3362
3363 END BSC_IM_UTILS;
[Home] [Help]