1 package body BSC_MV_ADAPTER AS
2 /*$Header: BSCMVLDB.pls 120.10 2006/07/10 07:58:52 rkumar ship $*/
3
4 function get_partition_clause(p_keys in varchar2) return varchar2 is
5 l_num_partitions number;
6 l_partition_stmt varchar2(1000);
7 begin
8
9 l_num_partitions := bsc_dbgen_metadata_reader.get_max_partitions;
10 if (l_num_partitions > 2 and p_keys is not null) then
11 l_partition_stmt := 'partition by hash('||p_keys||') partitions '||l_num_partitions;
12 else
13 l_partition_stmt := null;
14 end if;
15 if g_debug then
16 write_to_log_file_n('In get_partition_clause, returning '||l_partition_stmt);
17 end if;
18 return l_partition_stmt;
19 end;
20
21 /*
22 This API can create MV or View
23 */
24 function create_mv_normal(
25 p_kpi varchar2,
26 p_mv_name varchar2,
27 p_mv_owner varchar2,
28 p_child_mv BSC_IM_UTILS.varchar_tabletype,
29 p_number_child_mv number,
30 p_options BSC_IM_UTILS.varchar_tabletype,
31 p_number_options number,
32 p_apps_origin varchar2,
33 p_type varchar2,
34 p_create_non_unique_index boolean
35 )return boolean is
36 --------------------------------------
37 l_mv_stmt varchar2(32000);
38 l_fast_refresh_mv boolean;
39 --------------------------------------
40 --map info
41 l_map_name BSC_IM_UTILS.varchar_tabletype;
42 l_map_type BSC_IM_UTILS.varchar_tabletype;
43 l_object_name BSC_IM_UTILS.varchar_tabletype;
44 l_map_property BSC_IM_UTILS.varchar_tabletype;
45 l_detail_property BSC_IM_UTILS.varchar_tabletype;
46 l_chosen_map BSC_IM_UTILS.boolean_tabletype;
47 l_number_mapping number;
48 --------------------------------------
49 --map details
50 l_line BSC_IM_UTILS.varchar_tabletype;
51 l_line_type BSC_IM_UTILS.varchar_tabletype;
52 l_number_map_detail number;
53 --------------------------------------
54 --manage snapshot log creation on the mv
55 l_snplog_created BSC_IM_UTILS.boolean_tabletype;
56 --------------------------------------
57 l_b_tables BSC_IM_UTILS.varchar_tabletype;
58 l_base_snplog_created BSC_IM_UTILS.boolean_tabletype;
59 l_number_b_tables number;
60 --------------------------------------
61 l_level_tables BSC_IM_UTILS.varchar_tabletype;
62 l_level_snplog_created BSC_IM_UTILS.boolean_tabletype;
63 l_number_level_tables number;
64 --------------------------------------
65 l_tablespace varchar2(400);
66 l_storage varchar2(800);
67 l_index_tablespace varchar2(400);
68 l_index_storage varchar2(800);
69 --------------------------------------
70 l_db_version varchar2(80);
71 l_status varchar2(200);
72 --------------------------------------
73 l_keys varchar2(10000);
74 Begin
75 if g_debug then
76 write_to_log_file_n('In create_mv_normal '||p_mv_name||' '||p_mv_owner||', p_type='||p_type||' p_kpi='||p_kpi) ;
77 if ( p_create_non_unique_index) then
78 write_to_log_file_n('p_create_non_unique_index=true');
79 end if;
80 end if;
81 g_kpi:=p_kpi;
82 l_db_version:=BSC_IM_UTILS.get_db_version;
83 if p_type='MV' then
84 if BSC_IM_UTILS.get_option_value(p_options,p_number_options,'FULL REFRESH')='Y' then
85 l_fast_refresh_mv:=false;
86 else
87 l_fast_refresh_mv:=true;
88 end if;
89 else
90 l_fast_refresh_mv:=false; --view
91 end if;
92 ----------------------------------------------------
93 --l_status:=check_old_mv_view(p_mv_name,p_mv_owner,p_type,p_options,p_number_options);
94 l_status:=check_old_mv_view(p_mv_name,null,p_type,p_options,p_number_options);
95 if l_status='ALREADY PRESENT' then
96 return true;
97 elsif l_status='ERROR' then
98 --error
99 return false;
100 end if;
101 --if none of the above, continue processing
102 ----------------------------------------------------
103 l_tablespace:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'TABLESPACE');
104 l_storage:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'STORAGE');
105 l_index_tablespace:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'INDEX TABLESPACE');
106 l_index_storage:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'INDEX STORAGE');
107 if l_tablespace is not null then
108 if instr(lower(l_tablespace),'tablespace')<=0 then
109 l_tablespace:=' tablespace '||l_tablespace;
110 end if;
111 end if;
112 if l_storage is not null then
113 if instr(lower(l_storage),'storage')<=0 then
114 l_storage:=' storage '||l_storage;
115 end if;
116 end if;
117 --------
118 if l_index_tablespace is null then
119 l_index_tablespace:=l_tablespace;
120 else
121 if instr(lower(l_index_tablespace),'tablespace')<=0 then
122 l_index_tablespace:=' tablespace '||l_index_tablespace;
123 end if;
124 end if;
125 if l_index_storage is null then
126 l_index_storage:=l_storage;
127 else
128 if instr(lower(l_index_storage),'storage')<=0 then
129 l_index_storage:=' storage '||l_index_storage;
130 end if;
131 end if;
132 ----------------------------------------------------
133 --see if we need to create any snapshot logs
134 for i in 1..p_number_child_mv loop
135 l_snplog_created(i):=false;
136 end loop;
137 --p_number_child_mv is only the list of mv.
138 if p_type='MV' and l_fast_refresh_mv then
139 for i in 1..p_number_child_mv loop
140 if create_mv_log_on_table(
141 p_child_mv(i),
142 p_apps_origin,
143 p_options,
144 p_number_options,
145 l_snplog_created(i))=false then
146 return false;
147 end if;
148 end loop;
149 end if;
150 --------------------------------------------
151 --get the mapping info
152 if BSC_IM_INT_MD.get_mapping(
153 p_mv_name,
154 p_apps_origin,
155 l_map_name,
156 l_map_type,
157 l_object_name,
158 l_map_property,
159 l_number_mapping)=false then
160 return false;
161 end if;
162 if g_debug then
163 write_to_log_file_n('The maps read');
164 for i in 1..l_number_mapping loop
165 write_to_log_file(l_map_name(i)||' '||l_map_type(i)||' '||l_object_name(i)||' '||l_map_property(i));
166 end loop;
167 end if;
168 --------------------------------------------
169 --from the property, get the list of B tables. we have to create dummy MV on these B tables if this
170 --MV gets created as full refresh
171 declare
172 ll_string varchar2(10000);
173 ll_b_tables BSC_IM_UTILS.varchar_tabletype;
174 ll_number_b_tables number;
175 begin
176 l_number_b_tables:=0;
177 for i in 1..l_number_mapping loop
178 ll_string:=null;
179 ll_number_b_tables:=0;
180 ll_string:=BSC_IM_UTILS.get_option_value(l_map_property(i),',','BASE TABLES');
181 if ll_string is not null then
182 if BSC_IM_UTILS.parse_values(ll_string,'+',ll_b_tables,ll_number_b_tables)=false then
183 return false;
184 end if;
185 for j in 1..ll_number_b_tables loop
186 if BSC_IM_UTILS.in_array(l_b_tables,l_number_b_tables,ll_b_tables(j))=false then
187 l_number_b_tables:=l_number_b_tables+1;
188 l_b_tables(l_number_b_tables):=ll_b_tables(j);
189 l_base_snplog_created(l_number_b_tables):=false;
190 end if;
191 end loop;
192 end if;
193 end loop;
194 if g_debug then
195 write_to_log_file_n('The Base Tables for which we may need to create snapshot logs');
196 for i in 1..l_number_b_tables loop
197 write_to_log_file(l_b_tables(i));
198 end loop;
199 end if;
200 if p_type='MV' and l_fast_refresh_mv then
201 --create mv log on these base tables for inc mv
202 for i in 1..l_number_b_tables loop
203 if create_mv_log_on_table(
204 l_b_tables(i),
205 p_apps_origin,
206 p_options,
207 p_number_options,
208 l_base_snplog_created(i))=false then
209 return false;
210 end if;
211 end loop;
212 end if;
213 end;
214 --------------------------------------------
215 --from the property, get the list of level tables. we have to create snp log on these level tables if this
216 --MV gets created as full refresh
217 declare
218 ll_string varchar2(10000);
219 ll_level_tables BSC_IM_UTILS.varchar_tabletype;
220 ll_number_level_tables number;
221 begin
222 l_number_level_tables:=0;
223 for i in 1..l_number_mapping loop
224 ll_string:=null;
225 ll_number_level_tables:=0;
226 ll_string:=BSC_IM_UTILS.get_option_value(l_map_property(i),',','DIM LEVELS');
227 if ll_string is not null then
228 if BSC_IM_UTILS.parse_values(ll_string,'+',ll_level_tables,ll_number_level_tables)=false then
229 return false;
230 end if;
231 for j in 1..ll_number_level_tables loop
232 if BSC_IM_UTILS.in_array(l_level_tables,l_number_level_tables,ll_level_tables(j))=false then
233 l_number_level_tables:=l_number_level_tables+1;
234 l_level_tables(l_number_level_tables):=ll_level_tables(j);
235 l_level_snplog_created(l_number_level_tables):=false;
236 end if;
237 end loop;
238 end if;
239 end loop;
240 if g_debug then
241 write_to_log_file_n('The Dim Level Tables for which we may need to create snapshot logs');
242 for i in 1..l_number_level_tables loop
243 write_to_log_file(l_level_tables(i));
244 end loop;
245 end if;
246 if p_type='MV' and l_fast_refresh_mv then
247 --create mv log on these base tables for inc mv
248 for i in 1..l_number_level_tables loop
249 if create_mv_log_on_table(
250 l_level_tables(i),
251 p_apps_origin,
252 p_options,
253 p_number_options,
254 l_level_snplog_created(i))=false then
255 return false;
256 end if;
257 end loop;
258 end if;
259 end;
260 ------------------------------------------------
261 --create the MV in the BSC user.
262 <<start_mv_create>>
263 if g_debug then
264 if l_fast_refresh_mv then
265 write_to_log_file_n('Try with FAST Refresh');
266 else
267 write_to_log_file_n('Try with FULL Refresh');
268 end if;
269 end if;
270 --------------------------------------------
271 --find out what maps to look at
272 declare
273 begin
274 --ran into an error with 8i instance : ORA-30489 Cannot have more than one rollup/cube expression list
275 --cannot have rollup(fk1),rollup(fk2)
276 --so for 8i, we never go for full refresh mapping
277 for i in 1..l_number_mapping loop
278 l_chosen_map(i):=true;
279 if l_fast_refresh_mv then
280 --if this is a fast refresh mv and there is full refresh specified, ignore the part
281 if BSC_IM_UTILS.parse_and_find(l_map_property(i),',','FULL REFRESH') then
282 l_chosen_map(i):=false;
283 else
284 l_chosen_map(i):=true;
285 end if;
286 else --this is full refresh
287 if BSC_IM_UTILS.parse_and_find(l_map_property(i),',','FAST REFRESH') then
288 l_chosen_map(i):=false;
289 else
290 l_chosen_map(i):=true;
291 end if;
292 end if;
293 end loop;
294 if g_debug then
295 write_to_log_file_n('List of maps being looked at and ignored');
296 for i in 1..l_number_mapping loop
297 if l_chosen_map(i) then
298 write_to_log_file(l_map_name(i)||' YES');
299 else
300 write_to_log_file(l_map_name(i)||' NO');
301 end if;
302 end loop;
303 end if;
304 end;
305 --------------------------------------------
306 /*
307 have to use ad_mv api to create mv. pre-req patch 3050839
308 ad_mv.create_mv(<MV_NAME>,
309 ' create materialized view <MV NAME>'||
310 ' tablespace '||ad_mv.g_mv_data_tablespace||
311 ' INITRANS 4 MAXTRANS 255'||
312 ' storage(INITIAL 4K NEXT .. '||
313 ' MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0)'||
314 ' build <DEFERRED|IMMEDIATE>'||
315 ' using index tablespace '||ad_mv.g_mv_index_tablespace||
316 ' storage (INITIAL 4K NEXT .. '||
317 ' MAXEXTENTS UNLIMITED PCTINCREASE 0) '||
318 ' refresh <FAST|COMPLETE> ON DEMAND'||
319 ' with <rowid|primary key> '||
320 ' <ENABLE|DISABLE QUERY REWRITE>'||
321 ' as <sub-query qualifying table references with schema name...>');
322
323 */
324 if p_type='MV' then
325 --l_mv_stmt:='create materialized view '||p_mv_owner||'.'||p_mv_name;
326 --create the MV in the apps schema
327 l_mv_stmt:='create materialized view '||p_mv_name;
328 l_mv_stmt:=l_mv_stmt||' '||l_tablespace||' INITRANS 4 MAXTRANS 255 '||l_storage||' ';
329 l_mv_stmt := l_mv_stmt || '<PARTITION_CLAUSE>';
330 l_mv_stmt:=l_mv_stmt||' BUILD DEFERRED ';
331 if l_tablespace is not null then
332 l_mv_stmt:=l_mv_stmt||' using index '||l_tablespace||' '||l_storage;
333 end if;
334 if l_fast_refresh_mv then
335 l_mv_stmt:=l_mv_stmt||' REFRESH FAST ON DEMAND ';
336 end if;
337 l_mv_stmt:=l_mv_stmt||' DISABLE QUERY REWRITE ';
338 elsif p_type='VIEW' then
339 l_mv_stmt:='create view '||p_mv_name;
340 else
341 if g_debug then
342 write_to_log_file_n('Unknown type '||p_type||'. Error.');
343 return false;
344 end if;
345 end if;
346 l_mv_stmt:=l_mv_stmt||' AS ';
347 ---------------------------------
348 --get the mapping details
349 begin
350 for i in 1..l_number_mapping loop
351 if l_chosen_map(i) then
352 if g_debug then
353 write_to_log_file_n('Process map '||l_map_name(i));
354 end if;
355 if BSC_IM_INT_MD.get_mapping_detail(
356 l_map_name(i),
357 p_apps_origin,
358 l_line,
359 l_line_type,
360 l_detail_property,
361 l_number_map_detail)=false then
362 return false;
363 end if;
364 if g_debug then
365 write_to_log_file_n('The map details read');
366 for i in 1..l_number_map_detail loop
367 write_to_log_file(l_line(i)||' '||l_line_type(i)||' '||l_detail_property(i));
368 end loop;
369 end if;
370 for j in 1..l_number_map_detail loop
371 if l_line_type(j)='SELECT' then
372 if j=1 then
373 l_mv_stmt:=l_mv_stmt||l_line(j);
374 else
375 l_mv_stmt:=l_mv_stmt||' UNION ALL '||l_line(j);
376 end if;
377 elsif l_line_type(j)='SELECT INC' then
378 if l_fast_refresh_mv then
379 l_mv_stmt:=l_mv_stmt||l_line(j);
380 end if;
381 elsif l_line_type(j)='FROM' then
382 l_mv_stmt:=l_mv_stmt||l_line(j);
383 elsif l_line_type(j)='WHERE' then
384 l_mv_stmt:=l_mv_stmt||l_line(j);
385 elsif l_line_type(j)='GROUP BY' then
386 l_mv_stmt:=l_mv_stmt||l_line(j);
387 elsif l_line_type(j)='KEYS' then
388 l_keys := l_line(j);
389 end if;
390 end loop;
391 l_mv_stmt:=l_mv_stmt||' UNION ALL ';
392 else
393 if g_debug then
394 write_to_log_file_n('Not looking at this map '||l_map_name(i));
395 end if;
396 end if;
397 end loop;
398 --we were running into an error where the string is so long, it was beyond 32000 bytes.
399 --in that case, go for full refresh mv with small stmt
400 l_mv_stmt:=substr(l_mv_stmt,1,length(l_mv_stmt)-10);
401
402 -- replace the partition clause
403 l_mv_stmt := replace(l_mv_stmt, '<PARTITION_CLAUSE>', get_partition_clause(l_keys));
404 if g_debug then
405 write_to_log_file_n('l_mv_stmt='||l_mv_stmt);
406 end if;
407 --------------------------
408 --create the mv
409 execute immediate l_mv_stmt;
410 exception when others then
411 BSC_IM_UTILS.g_status_message:=sqlerrm;
412 if g_debug then
413 write_to_log_file_n('Error creating MV '||sqlerrm);
414 end if;
415 if l_fast_refresh_mv then
416 l_fast_refresh_mv:=false;
417 ------------------------------
418 for i in 1..p_number_child_mv loop
419 --if the mv log got created for this mv, drop the mv logs
420 if l_snplog_created(i) then
421 if g_debug then
422 write_to_log_file_n('Going to drop the snapshot log and constraint on '||p_child_mv(i));
423 end if;
424 if BSC_IM_UTILS.drop_mv_log(p_child_mv(i),null)=false then
425 null;
426 end if;
427 --if BSC_IM_UTILS.drop_constraint(p_child_mv(i),null,p_child_mv(i)||'_PK')=false then
428 --null;
429 --end if;
430 end if;
431 end loop;
432 -----------------------------
433 --drop the mv logs on the base tables
434 for i in 1..l_number_b_tables loop
435 if l_base_snplog_created(i) then
436 if g_debug then
437 write_to_log_file_n('Going to drop the snapshot log and constraint on '||l_b_tables(i));
438 end if;
439 if BSC_IM_UTILS.drop_mv_log(l_b_tables(i),null)=false then
440 null;
441 end if;
442 --if BSC_IM_UTILS.drop_constraint(l_b_tables(i),null,l_b_tables(i)||'_PK')=false then
443 --null;
444 --end if;
445 end if;
446 end loop;
447 -----------------------------
448 --drop the mv logs on the dim levels
449 for i in 1..l_number_level_tables loop
450 if l_level_snplog_created(i) then
451 if g_debug then
452 write_to_log_file_n('Going to drop the snapshot log and constraint on '||l_level_tables(i));
453 end if;
454 if BSC_IM_UTILS.drop_mv_log(l_level_tables(i),null)=false then
455 null;
456 end if;
457 --if BSC_IM_UTILS.drop_constraint(l_level_tables(i),null,l_level_tables(i)||'_PK')=false then
458 --null;
459 --end if;
460 end if;
461 end loop;
462 -----------------------------
463 --dont create dummy mv for now
464 /*if create_dummy_mv(l_b_tables,l_number_b_tables,p_mv_name,p_mv_owner)=false then
465 --we have to consider cases where the base tables may not have snapshot logs or pk constraints
466 null;
467 end if;*/
468 goto start_mv_create;
469 else
470 raise;
471 end if;
472 end;
473 if p_type='MV' then
474 --MV are created in the apps schema
475 --if create_mv_synonym(p_mv_name,p_mv_name,p_mv_owner)=false then
476 --null;
477 --end if;
478 if create_mv_index(p_mv_name,null,p_kpi,p_apps_origin,l_index_tablespace,l_index_storage,
479 p_create_non_unique_index)=false then
480 return false;
481 end if;
482 end if;
483 BSC_IM_UTILS.write_to_log_file_n(p_type||' '||p_mv_name||' Created');
484 if l_fast_refresh_mv then
485 if p_type='MV' then
486 BSC_IM_UTILS.write_to_log_file_n(' -> FAST REFRESH');
487 end if;
488 else
489 if p_type='MV' then
490 BSC_IM_UTILS.write_to_log_file_n(' -> FULL REFRESH');
491 end if;
492 end if;
493 BSC_IM_UTILS.write_to_log_file_n(' ');
494 --------------------------------------------
495 return true;
496 Exception when others then
497 BSC_IM_UTILS.g_status_message:=sqlerrm;
498 g_status_message:=sqlerrm;
499 write_to_log_file_n('Exception in create_mv_normal '||sqlerrm);
500 return false;
501 End;
502
503 function create_dummy_mv(
504 p_b_tables BSC_IM_UTILS.varchar_tabletype,
505 p_number_b_tables number,
506 p_mv_name varchar2,
507 p_mv_owner varchar2
508 )return boolean is
509 l_mv_name varchar2(200);
510 l_stmt varchar2(8000);
511 Begin
512 if g_debug then
513 write_to_log_file_n('In create_dummy_mv '||p_mv_name||' '||p_mv_owner);
514 end if;
515 if g_bsc_owner is null then
516 g_bsc_owner:=BSC_IM_UTILS.get_bsc_owner;
517 end if;
518 if p_number_b_tables>0 then
519 for i in 1..p_number_b_tables loop
520 --create dummy MV on as many base tables as possible
521 l_mv_name:=substr(substr(p_mv_name,1,length(p_mv_name)-3),1,24)||'_D'||i||'MV';
522 if BSC_IM_UTILS.drop_mv(l_mv_name,null)=false then
523 null;
524 end if;
525 if BSC_IM_UTILS.drop_synonym(l_mv_name)=false then
526 null;
527 end if;
528 --create the mv on the apps schema
529 l_stmt:='CREATE MATERIALIZED VIEW '||l_mv_name||' BUILD DEFERRED REFRESH FAST ON '||
530 'DEMAND AS SELECT '||p_b_tables(i)||'.*,'||p_b_tables(i)||'.rowid row_id from '||
531 g_bsc_owner||'.'||p_b_tables(i)||' '||p_b_tables(i)||
532 ' where 1=2';
533 if g_debug then
534 write_to_log_file_n(l_stmt);
535 end if;
536 begin
537 execute immediate l_stmt;
538 exception when others then
539 BSC_IM_UTILS.g_status_message:=sqlerrm;
540 if g_debug then
541 write_to_log_file_n('Could not create dummy mv on '||p_b_tables(i)||' '||sqlerrm);
542 end if;
543 end;
544 end loop;
545 end if;
546 return true;
547 Exception when others then
548 BSC_IM_UTILS.g_status_message:=sqlerrm;
549 g_status_message:=sqlerrm;
550 write_to_log_file_n('Exception in create_dummy_mv '||sqlerrm);
551 return false;
552 End;
553 function zmv_exists_for_mv(
554 p_mv_name varchar2,
555 p_mv_owner varchar2,
556 p_kpi varchar2,
557 p_apps_origin varchar2) return boolean
558 is
559 l_zmv varchar2(100);
560 -------------------------------------------------------------------------
561 l_object_type BSC_IM_UTILS.varchar_tabletype;
562 l_description BSC_IM_UTILS.varchar_tabletype;
563 l_zero_code_mv BSC_IM_UTILS.varchar_tabletype;
564 l_number_zero_code_mv number;
565 l_owner varchar2(100);
566 cursor cZMV(p_owner varchar2, p_zmv_name varchar2) is
567 select count(1) from all_objects where
568 owner=p_owner and object_name=p_zmv_name
569 and object_type = 'MATERIALIZED VIEW';
570 begin
571 l_zmv := upper(substr(p_mv_name, 1, instr(p_mv_name, '_MV', -1)))||'ZMV';
572 --write_to_log_file('zmv_exists_for_mv, we are searching for l_zmv ='||l_zmv);
573 if BSC_IM_INT_MD.get_object(
574 p_kpi,
575 p_apps_origin,
576 'ZERO CODE MV',
577 l_zero_code_mv,
578 l_object_type,
579 l_description,
580 l_number_zero_code_mv)=false then
581 return false;
582 end if;
583 for i in 1..l_number_zero_code_mv loop
584 write_to_log_file('Comparing '||l_zmv||' to '||l_zero_code_mv(i));
585 if (upper(l_zero_code_mv(i)) = l_zmv) then
586 return true;
587 end if;
588 end loop;
589 -- could be called from mv refresh
590 l_owner:=bsc_im_utils.get_table_owner(p_mv_name);
591 open cZMV(l_owner, l_zmv);
592 fetch cZMV into l_number_zero_code_mv;
593 close cZMV;
594 if (l_number_zero_code_mv>0) then
595 return true;
596 end if;
597 return false;
598
599 end;
600
601 function is_higher_mv_a_view(p_mv_name varchar2) return boolean
602 is
603 begin
604
605 return false;
606 end;
607
608 function index_already_exists(p_index_name varchar2, p_mv_name varchar2, p_mv_owner varchar2) return boolean is
609 cursor cIndexExists is
610 select count(1) from user_indexes where index_name=p_index_name
611 and table_name = p_mv_name and table_owner=p_mv_owner;
612 l_index_exists number;
613 begin
614 open cIndexExists;
615 fetch cIndexExists into l_index_exists;
616 close cIndexExists;
617 if l_index_exists > 0 then
618 return true;
619 end if;
620 return false;
621 end;
622
623 /*---------------------------------------------------------
624 New Index strategy
625 Time index for every mv (periodicity_id, year, period)
626
627 Other indexes
628 Case 1: No views above
629 MV and ZMV: periodicity_id, zkeys, nonzkeys, type
630
631 Case 2: Views above, zmv exists
632 MV: periodicity_id, zkeys, nonzkeys, type
633 ZMV: periodicity_id, zkeys, type
634 periodicity_id, nonzkey1
635 .
636 .
637 periodicity_id, nonzkeyn
638
639 Case 3: Views above, no zmv
640 MV: periodicity_id, nonzkey1
641 .
642 .
643 periodicity_id, nonzkeyn
644 ---------------------------------------------------------*/
645 function create_mv_index(
646 p_mv_name varchar2,
647 p_mv_owner varchar2,
648 p_kpi varchar2,
649 p_apps_origin varchar2,
650 p_tablespace varchar2,
651 p_storage varchar2,
652 p_create_non_unique_index boolean,
653 p_called_from_refresh boolean default false
654 )return boolean is
655 ------------------------------------------------------------
656 l_fk_name BSC_IM_UTILS.varchar_tabletype;
657 l_fk_type BSC_IM_UTILS.varchar_tabletype;
658 l_uk_name BSC_IM_UTILS.varchar_tabletype;
659 l_uk_parent_name BSC_IM_UTILS.varchar_tabletype;
660 l_fk_property BSC_IM_UTILS.varchar_tabletype;
661 l_description BSC_IM_UTILS.varchar_tabletype;
662 l_number_fk number;
663 ------------------------------------------------------------
664 l_stmt varchar2(8000);
665 --l_db_version varchar2(80);
666 l_trans varchar2(200);
667 ------------------------------------------------------------
668 l_create_non_unique_index boolean;
669 l_owner varchar2(200);
670 ------------------------------------------------------------
671 l_zero_code_cols dbms_sql.varchar2_table;
672 -----
673 l_higher_level_view_exists boolean ;
674 l_time_columns VARCHAR2(100):= '(PERIODICITY_ID, YEAR, PERIOD)';
675 l_index_counter number := 1;
676 l_nonzero_code_cols dbms_sql.varchar2_table;
677 l_is_zmv boolean;
678 l_zmv_exists_for_mv boolean;
679 l_mv_name_for_zmv varchar2(100);
680 ---------------------------------
681
682 l_s_tables BSC_IM_UTILS.varchar_tabletype;
683 l_number_s_tables number;
684
685 Begin
686 --get the fks
687 g_kpi:=p_kpi;
688 l_owner:=p_mv_owner;
689 if l_owner is null then
690 l_owner:=bsc_im_utils.get_table_owner(p_mv_name);
691 end if;
692 l_create_non_unique_index:=p_create_non_unique_index;
693 -- Get the FKS differently for refresh and GDB run
694 if (p_called_from_refresh) then
695 if BSC_BSC_ADAPTER.get_s_tables_for_mv(p_mv_name,l_s_tables,l_number_s_tables)=false then
696 return false;
697 end if;
698 if l_number_s_tables=0 then
699 if g_debug then
700 write_to_log_file_n('No s tables found for '||p_mv_name||'. Do Validations to do...');
701 end if;
702 return true;
703 end if;
704 if BSC_BSC_ADAPTER.get_table_fks(l_s_tables,l_number_s_tables,l_fk_name,l_number_fk)=false then
705 return false;
706 end if;
707 else
708 if BSC_IM_INT_MD.get_fk(
709 p_mv_name,
710 p_apps_origin,
711 l_fk_name,
712 l_fk_type,
713 l_uk_name,
714 l_uk_parent_name,
715 l_description,
716 l_fk_property,
717 l_number_fk)=false then
718 return false;
719 end if;
720 end if;
721
722
723 if (g_debug) then
724 write_to_log_file('In create_mv_index for '||p_mv_name);
725 end if;
726 l_zmv_exists_for_mv := false;
727
728 if (p_mv_name like '%_ZMV') then
729 l_is_zmv := true;
730 if (g_debug) then
731 write_to_log_file(', l_is_zmv=true');
732 end if;
733 l_mv_name_for_zmv := substr(p_mv_name, 1, instr(p_mv_name, '_ZMV', -1))||'MV';
734 else
735 l_is_zmv := false;
736 l_mv_name_for_zmv := p_mv_name;
737 if (g_debug) then
738 write_to_log_file(', l_is_zmv=false');
739 end if;
740 if zmv_exists_for_mv(p_mv_name, p_mv_owner, p_kpi, p_apps_origin) then
741 l_zmv_exists_for_mv := true;
742 if (g_debug) then
743 write_to_log_file(', l_zmv_exists_for_mv=true');
744 end if;
745 else
746 l_zmv_exists_for_mv := false;
747 if (g_debug) then
748 write_to_log_file(', l_zmv_exists_for_mv=false');
749 end if;
750 end if;
751 end if;
752
753 --l_db_version:=BSC_IM_UTILS.get_db_version;
754 l_trans:=' PCTFREE 5 INITRANS 11 MAXTRANS 255 ';
755
756 -- New MV Strategy, enh 4195212
757 -- Every MV has a time index
758 -- Enh#4239064: create index in parallel
759 l_stmt:='create index '||l_owner||'.'||p_mv_name||'N'||l_index_counter||' on '||l_owner||'.'||p_mv_name||l_time_columns;
760 l_stmt:=l_stmt||' '||p_tablespace||' '||p_storage||l_trans||' parallel';
761
762
763
764 if index_already_exists(p_mv_name||'N'||l_index_counter, p_mv_name, p_mv_owner)=false then
765 if BSC_IM_UTILS.create_index(l_stmt,null)=false then
766 return false;
767 end if;
768 end if;
769 -- Enh#4239064: set to noparallel
770 execute immediate 'alter index '||l_owner||'.'||p_mv_name||'N'||l_index_counter||' noparallel';
771
772 l_index_counter := l_index_counter + 1;
773
774 if p_create_non_unique_index OR BSC_IM_UTILS.is_parent_of_type_present(l_mv_name_for_zmv,'VIEW') then
775 l_higher_level_view_exists := true;
776 if (g_debug) then
777 write_to_log_file(' l_higher_level_view_exists := true');
778 end if;
779 else
780 l_higher_level_view_exists := false;
781 if (g_debug) then
782 write_to_log_file(' l_higher_level_view_exists := false');
783 end if;
784 end if;
785 if l_number_fk>0 then
786 l_zero_code_cols.delete;
787 l_nonzero_code_cols.delete;
788 for i in 1..l_number_fk loop
789 if (l_fk_name(i) not in ('PERIODICITY_ID', 'YEAR', 'PERIOD', 'TYPE')) then
790 if(BSC_IM_UTILS.needs_zero_code_mv(p_mv_name, p_kpi, l_fk_name(i))) then
791 write_to_log_file(l_fk_name(i)||' is a zero code col');
792 l_zero_code_cols(l_zero_code_cols.count+1) := l_fk_name(i);
793 else
794 write_to_log_file(l_fk_name(i)||' is a non zero code col');
795 l_nonzero_code_cols(l_nonzero_code_cols.count+1) :=l_fk_name(i);
796 end if;
797 end if;
798 end loop;
799 end if;
800
801 -- Case 1 both MV/ZMV and MV only for Case 2
802 -- Case 1: No views above
803 -- MV and ZMV: periodicity_id, zkeys, nonzkeys, type
804 -- Case 2: Views above, zmv exists
805 -- MV: periodicity_id, zkeys, nonzkeys, type
806
807 l_stmt := 'create index '||l_owner||'.'||p_mv_name||'N'||l_index_counter||' on '||l_owner||'.'||p_mv_name||'(PERIODICITY_ID,';
808 if (l_higher_level_view_exists=false OR
809 (l_higher_level_view_exists=true AND l_is_zmv=false AND l_zmv_exists_for_mv=true)-- CASE2 for MV
810 ) then
811 if (g_debug) then
812 write_to_log_file('Case1 and Case 2a');
813 end if;
814 if l_number_fk>0 then
815 if (l_zero_code_cols.count>0) then
816 for i in l_zero_code_cols.first..l_zero_code_cols.last loop
817 l_stmt := l_stmt ||l_zero_code_cols(i)||',';
818 end loop;
819 end if;
820 if (l_nonzero_code_cols.count>0) then
821 for i in l_nonzero_code_cols.first..l_nonzero_code_cols.last loop
822 l_stmt := l_stmt ||l_nonzero_code_cols(i)||',';
823 end loop;
824 end if;
825 -- Enh#4239064: create index in parallel
826 l_stmt:=l_stmt||' TYPE) '||p_tablespace||' '||p_storage||l_trans||' parallel';
827 if index_already_exists(p_mv_name||'N'||l_index_counter, p_mv_name, p_mv_owner)=false then
828 if BSC_IM_UTILS.create_index(l_stmt,null)=false then
829 return false;
830 end if;
831 end if;
832 -- Enh#4239064: set to noparallel
833 execute immediate 'alter index '||l_owner||'.'||p_mv_name||'N'||l_index_counter||' noparallel';
834 end if;
835 return true;
836 end if;
837 -- There are views above, so its either Case 2 for ZMV or Case 3 for MV
838 --First handle Case 2 ZMV
839 --ZMV:
840 -- periodicity_id, zkeys, type
841 -- periodicity_id, nonzkey1
842 -- .
843 -- .
844 -- periodicity_id, nonzkeyn
845 if (l_is_zmv) then
846 if (g_debug) then
847 write_to_log_file('Case 2b');
848 end if;
849
850 if l_number_fk>0 then
851 l_stmt := 'create index '||l_owner||'.'||p_mv_name||'N'||l_index_counter||' on '||l_owner||'.'||p_mv_name||'(PERIODICITY_ID,';
852 if (l_zero_code_cols.count>0) then
853 for i in l_zero_code_cols.first..l_zero_code_cols.last loop
854 l_stmt := l_stmt ||l_zero_code_cols(i)||',';
855 end loop;
856 -- Enh#4239064: create index in parallel
857 l_stmt:=l_stmt||' TYPE) '||p_tablespace||' '||p_storage||l_trans||' parallel';
858 if index_already_exists(p_mv_name||'N'||l_index_counter, p_mv_name, p_mv_owner)=false then
859 if BSC_IM_UTILS.create_index(l_stmt,null)=false then
860 write_to_log_file_n('Exception in create_mv_index, stmt='||l_stmt);
861 return false;
862 end if;
863 end if;
864 -- Enh#4239064: set to noparallel
865 execute immediate 'alter index '||l_owner||'.'||p_mv_name||'N'||l_index_counter||' noparallel';
866 l_index_counter := l_index_counter+1;
867 end if;
868 if (l_nonzero_code_cols.count>0) then
869 for i in l_nonzero_code_cols.first..l_nonzero_code_cols.last loop
870 l_stmt := 'create index '||l_owner||'.'||p_mv_name||'N'||l_index_counter||' on '||l_owner||'.'||p_mv_name||'(PERIODICITY_ID,';
871 -- Enh#4239064: create index in parallel
872 l_stmt := l_stmt ||l_nonzero_code_cols(i)||') '||p_tablespace||' '||p_storage||l_trans||' parallel';
873 if index_already_exists(p_mv_name||'N'||l_index_counter, p_mv_name, p_mv_owner)=false then
874 if BSC_IM_UTILS.create_index(l_stmt,null)=false then
875 write_to_log_file_n('Exception in create_mv_index, stmt='||l_stmt);
876 return false;
877 end if;
878 end if;
879 -- Enh#4239064: set to noparallel
880 execute immediate 'alter index '||l_owner||'.'||p_mv_name||'N'||l_index_counter||' noparallel';
881 l_index_counter := l_index_counter+1;
882 end loop;
883 end if;
884 end if;
885 return true;
886 end if;
887
888 --Case 3: Views above, no zmv
889 ---------------------------
890 -- MV:
891 -- periodicity_id, nonzkey1
892 -- .
893 -- .
894 -- periodicity_id, nonzkeyn
895 if (g_debug) then
896 write_to_log_file('Case3');
897 end if;
898
899 if (l_nonzero_code_cols.count>0) then
900 for i in l_nonzero_code_cols.first..l_nonzero_code_cols.last loop
901 l_stmt := 'create index '||l_owner||'.'||p_mv_name||'N'||l_index_counter||' on '||l_owner||'.'||p_mv_name||'(PERIODICITY_ID,';
902 -- Enh#4239064: create index in parallel
903 l_stmt := l_stmt ||l_nonzero_code_cols(i)||') '||p_tablespace||' '||p_storage||l_trans||' parallel';
904 if index_already_exists(p_mv_name||'N'||l_index_counter, p_mv_name, p_mv_owner)=false then
905 if BSC_IM_UTILS.create_index(l_stmt,null)=false then
906 return false;
907 end if;
908 end if;
909 -- Enh#4239064: set to noparallel
910 execute immediate 'alter index '||l_owner||'.'||p_mv_name||'N'||l_index_counter||' noparallel';
911 l_index_counter := l_index_counter+1;
912 end loop;
913 end if;
914 return true;
915 Exception when others then
916 BSC_IM_UTILS.g_status_message:=sqlerrm;
917 g_status_message:=sqlerrm;
918 write_to_log_file_n('Exception in create_mv_index, stmt='||l_stmt);
919 write_to_log_file_n(sqlerrm);
920 return false;
921 End;
922 -- bug 4180632, PMD does not populate bsc_sys_dim_level_cols with the parent FKs
923 -- add the column only if it is not there in the list of FKs
924 FUNCTION get_parent_fk_columns(
925 p_object IN varchar2,
926 p_already_included IN BSC_IM_UTILS.varchar_tabletype,
927 p_included_count IN NUMBER,
928 p_columns IN OUT nocopy BSC_IM_UTILS.varchar_tabletype,
929 p_num_columns IN OUT nocopy number
930 ) RETURN boolean
931 IS
932 CURSOR cColumns IS
933 select relation_col
934 from bsc_sys_dim_level_rels rels ,bsc_sys_dim_levels_b levels
935 where levels.level_table_name=p_object
936 and rels.dim_level_id=levels.dim_level_id;
937 begin
938 FOR i IN cColumns LOOP
939 IF bsc_im_utils.in_array(p_already_included, p_included_count, i.relation_col)=FALSE AND
940 bsc_im_utils.in_array(p_columns, p_num_columns, i.relation_col)=FALSE THEN
941 p_columns(p_columns.count+1) := i.relation_col;
942 p_num_columns := p_num_columns +1;
943 --write_to_log_file('Adding fk column '||i.relation_col);
944 END IF;
945 END LOOP;
946 RETURN TRUE;
947 Exception when others then
948 BSC_IM_UTILS.g_status_message:=sqlerrm;
949 g_status_message:=sqlerrm;
950 write_to_log_file_n('Exception in get_parent_fk_columns, '||sqlerrm);
951 write_to_log_file_n(sqlerrm);
952 RETURN FALSE;
953 End;
954
955 function create_mv_log_on_table(
956 p_object varchar2,
957 p_apps_origin varchar2,
958 p_options BSC_IM_UTILS.varchar_tabletype,
959 p_number_options number,
960 p_snplog_created out nocopy boolean
961 )return boolean is
962 l_column_name BSC_IM_UTILS.varchar_tabletype;
963 l_column_type BSC_IM_UTILS.varchar_tabletype;
964 l_column_data_type BSC_IM_UTILS.varchar_tabletype;
965 l_column_origin BSC_IM_UTILS.varchar_tabletype;
966 l_aggregation_type BSC_IM_UTILS.varchar_tabletype;
967 l_description BSC_IM_UTILS.varchar_tabletype;
968 l_property BSC_IM_UTILS.varchar_tabletype;
969 l_number_columns number;
970 ------------------------------------------------------------
971 l_fk_name BSC_IM_UTILS.varchar_tabletype;
972 l_fk_type BSC_IM_UTILS.varchar_tabletype;
973 l_uk_name BSC_IM_UTILS.varchar_tabletype;
974 l_uk_parent_name BSC_IM_UTILS.varchar_tabletype;
975 l_fk_property BSC_IM_UTILS.varchar_tabletype;
976 l_number_fk number;
977 ------------------------------------------------------------
978 l_dim number;
979 Begin
980 if g_debug then
981 write_to_log_file_n('In create_mv_log_on_table '||p_object);
982 end if;
983 p_snplog_created:=false;
984 l_number_columns := 0;
985 select count(1) into l_dim from bsc_sys_dim_levels_b where level_table_name=p_object;
986 if(l_dim=0) then -- not dimension
987 if BSC_IM_INT_MD.get_column(
988 p_object,
989 p_apps_origin,
990 l_column_name,
991 l_column_type,
992 l_column_data_type,
993 l_column_origin,
994 l_aggregation_type,
995 l_description,
996 l_property,
997 l_number_columns)=false then
998 return false;
999 end if;
1000 end if;
1001 --get the fks
1002 if BSC_IM_INT_MD.get_fk(
1003 p_object,
1004 p_apps_origin,
1005 l_fk_name,
1006 l_fk_type,
1007 l_uk_name,
1008 l_uk_parent_name,
1009 l_description,
1010 l_fk_property,
1011 l_number_fk)=false then
1012 return false;
1013 end if;
1014 if (l_dim<>0) then -- dimension, get parent fk cols
1015 -- bug 4180632, PMD does not populate bsc_sys_dim_level_cols with the parent FKs
1016 if get_parent_fk_columns(
1017 p_object,
1018 l_fk_name,--we shouldnt add fk cols again
1019 l_number_fk, --# of fk cols
1020 l_column_name,
1021 l_number_columns)=false then
1022 return false;
1023 end if;
1024 end if;
1025 if BSC_IM_UTILS.create_mv_log_on_table(
1026 p_object,
1027 null,
1028 p_options,
1029 p_number_options,
1030 l_fk_name,
1031 l_number_fk,
1032 l_column_name,
1033 l_number_columns,
1034 p_snplog_created
1035 )=false then
1036 return false;
1037 end if;
1038 return true;
1039 Exception when others then
1040 BSC_IM_UTILS.g_status_message:=sqlerrm;
1041 g_status_message:=sqlerrm;
1042 write_to_log_file_n('Exception in create_mv_log_on_table '||sqlerrm);
1043 return false;
1044 End;
1045
1046 function create_mv_synonym(
1047 p_level varchar2,
1048 p_mv_name varchar2,
1049 p_mv_owner varchar2
1050 )return boolean is
1051 Begin
1052 g_stmt:='create synonym '||p_level||' for '||p_mv_owner||'.'||p_mv_name;
1053 write_to_debug_n(g_stmt);
1054 if BSC_IM_UTILS.drop_synonym(p_level)=false then
1055 null;
1056 end if;
1057 execute immediate g_stmt;
1058 write_to_debug('Created synonym');
1059 return true;
1060 Exception when others then
1061 BSC_IM_UTILS.g_status_message:=sqlerrm;
1062 g_status_message:=sqlerrm;
1063 write_to_log_file_n('Exception in create_mv_synonym '||sqlerrm);
1064 return false;
1065 End;
1066
1067 function alter_mv_to_refresh_demand(
1068 p_mv_name varchar2,
1069 p_mv_owner varchar2
1070 )return boolean is
1071 l_owner varchar2(200);
1072 Begin
1073 l_owner:=p_mv_owner;
1074 if l_owner is null then
1075 l_owner:=bsc_im_utils.get_table_owner(p_mv_name);
1076 end if;
1077 g_stmt:='ALTER MATERIALIZED VIEW '||l_owner||'.'||p_mv_name||' REFRESH ON DEMAND';
1078 write_to_debug_n(g_stmt);
1079 execute immediate g_stmt;
1080 write_to_debug('MV altered');
1081 return true;
1082 Exception when others then
1083 BSC_IM_UTILS.g_status_message:=sqlerrm;
1084 g_status_message:=sqlerrm;
1085 write_to_log_file_n('Exception in alter_mv_to_refresh_demand '||sqlerrm);
1086 return false;
1087 End;
1088
1089 function create_mv_kpi(
1090 p_kpi varchar2,
1091 p_apps_origin varchar2,
1092 p_options BSC_IM_UTILS.varchar_tabletype,
1093 p_number_options number
1094 ) return boolean is
1095 -------------------------------------------------------------------------
1096 l_summary_mv BSC_IM_UTILS.varchar_tabletype;
1097 l_object_type BSC_IM_UTILS.varchar_tabletype;
1098 l_description BSC_IM_UTILS.varchar_tabletype;
1099 l_property BSC_IM_UTILS.varchar_tabletype;
1100 l_number_summary_mv number;
1101 -------------------------------------------------------------------------
1102 l_parent_summary_mv BSC_IM_UTILS.varchar_tabletype;
1103 l_child_summary_mv BSC_IM_UTILS.varchar_tabletype;
1104 l_number_pc_mv number;
1105 -------------------------------------------------------------------------
1106 l_ordered_summary_mv BSC_IM_UTILS.varchar_tabletype;
1107 l_ordered_summary_mv_rank BSC_IM_UTILS.number_tabletype;
1108 l_number_ordered_summary_mv number;
1109 -------------------------------------------------------------------------
1110 l_max_rank number;
1111 l_min_rank number;
1112 l_bsc_owner varchar2(200);
1113 -------------------------------------------------------------------------
1114 l_child_mv BSC_IM_UTILS.varchar_tabletype;
1115 l_number_child_mv number;
1116 -------------------------------------------------------------------------
1117 --users may say they only want three levels of mv.
1118 l_max_mv_levels number;
1119 l_summary_views varchar2(20);
1120 l_type varchar2(20);
1121 -------------------------------------------------------------------------
1122 l_create_non_unique_index boolean;
1123 -------------------------------------------------------------------------
1124 Begin
1125 if g_debug then
1126 write_to_log_file_n('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1127 write_to_log_file('In create_mv_fact '||p_kpi||' '||get_time);
1128 write_to_log_file('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1129 end if;
1130 g_kpi:=p_kpi;
1131 BSC_IM_UTILS.write_to_log_file_n('++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1132 BSC_IM_UTILS.write_to_log_file_n('Create MV/Views for KPI '||p_kpi);
1133 BSC_IM_UTILS.write_to_log_file_n('++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1134 if g_bsc_owner is null then
1135 g_bsc_owner:=BSC_IM_UTILS.get_bsc_owner;
1136 end if;
1137 l_bsc_owner:=g_bsc_owner;
1138 ------------------------------------------------
1139 --get the ordered list of mv
1140 if get_ordered_mv_list(
1141 p_kpi,
1142 p_apps_origin,
1143 l_parent_summary_mv,
1144 l_child_summary_mv,
1145 l_number_pc_mv,
1146 l_ordered_summary_mv,
1147 l_ordered_summary_mv_rank,
1148 l_number_ordered_summary_mv,
1149 l_max_rank
1150 )=false then
1151 return false;
1152 end if;
1153 ------------------------------------------------
1154 --call the create MV. should be called according to the rank
1155 l_max_mv_levels:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'MV LEVELS');
1156 l_summary_views:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'SUMMARY VIEWS');
1157 if g_debug then
1158 write_to_log_file_n('Max levels of MV='||l_max_mv_levels);
1159 write_to_log_file('Summary Views='||l_summary_views);
1160 end if;
1161 l_min_rank:=1000;
1162 --rkumar: bug5335536 calculate min_rank
1163 for i in 1..l_number_ordered_summary_mv loop
1164 if l_ordered_summary_mv_rank(i) < l_min_rank then
1165 l_min_rank:=l_ordered_summary_mv_rank(i);
1166 end if;
1167 end loop;
1168 write_to_log_file('MinRank is: '||l_min_rank);
1169 --3534805
1170 /*
1171 if the first level is not a BSC_S_ mv but instead say BSC_SB_ mv, then
1172 we set the rank for the SB mv as -1 this is because, if we have something like
1173 B1 -> SB -> S_0_0_MV -> S_0_1_MV
1174 B2 -------> S_0_0_MV -> S_0_1_MV
1175 and levels=2, we want S_0_0_MV and S_0_1_MV to be mv. earlier, S_0_1_MV became a
1176 view as it was viewed as third level
1177 */
1178 --rank starts from 0. so we need l_max_mv_levels-1
1179 /*
1180 go through the list of MV, if there are SB with rank 0, look at the corresponding S MV.
1181 if the S MV does not have rank 0, reduce the rank of the SB MV
1182 */
1183 --rkumar:5335536 Commeting out the logic for rank modification. No longer required after the bugfix.
1184 /* declare
1185 l_name varchar2(100);
1186 l_pattern varchar2(100);
1187 l_pattern_len number;
1188 l_sb_pattern varchar2(100);
1189 l_sb_pattern_len number;
1190 l_rank number;
1191 begin
1192 for i in 1..l_number_ordered_summary_mv loop
1193 if substr(l_ordered_summary_mv(i),1,7)='BSC_SB_' and l_ordered_summary_mv_rank(i)=0 then
1194 l_pattern:=substr(l_ordered_summary_mv(i),1,instr(l_ordered_summary_mv(i),'_',1,4));
1195 l_sb_pattern:=l_pattern;
1196 l_sb_pattern_len:=length(l_sb_pattern);
1197 l_pattern:=replace(l_pattern,'BSC_SB_','BSC_S_');
1198 l_pattern_len:=length(l_pattern);
1199 l_name:=replace(l_ordered_summary_mv(i),'BSC_SB_','BSC_S_');
1200 for j in 1..l_number_ordered_summary_mv loop
1201 if l_ordered_summary_mv(j)=l_name then
1202 if l_ordered_summary_mv_rank(j)<>0 then
1203 l_rank:=l_ordered_summary_mv_rank(j);
1204 --first reduce the rank of the S
1205 for k in 1..l_number_ordered_summary_mv loop
1206 --write_to_log_file(substr(l_ordered_summary_mv(k),1,l_pattern_len)||' '||l_pattern);
1207 if substr(l_ordered_summary_mv(k),1,l_pattern_len)=l_pattern then
1208 l_ordered_summary_mv_rank(k):=l_ordered_summary_mv_rank(k)-l_rank;
1209 if l_min_rank>l_ordered_summary_mv_rank(k) then
1210 l_min_rank:=l_ordered_summary_mv_rank(k);
1211 end if;
1212 end if;
1213 --for SB also
1214 if substr(l_ordered_summary_mv(k),1,l_sb_pattern_len)=l_sb_pattern then
1215 l_ordered_summary_mv_rank(k):=l_ordered_summary_mv_rank(k)-l_rank;
1216 if l_min_rank>l_ordered_summary_mv_rank(k) then
1217 l_min_rank:=l_ordered_summary_mv_rank(k);
1218 end if;
1219 end if;
1220 end loop;
1221 end if;
1222 exit;
1223 end if;
1224 end loop;
1225 end if;
1226 end loop;
1227 if g_debug then
1228 write_to_log_file_n('After rank modifications');
1229 for i in 1..l_number_ordered_summary_mv loop
1230 write_to_log_file(l_ordered_summary_mv(i)||' '||l_ordered_summary_mv_rank(i));
1231 end loop;
1232 write_to_log_file_n('Min Rank='||l_min_rank);
1233 end if;
1234 end;
1235 */
1236 --------------------------
1237 if l_max_rank>l_max_mv_levels-1 then
1238 g_all_levels_mv:=false;
1239 if g_debug then
1240 write_to_log_file_n('NOT ALL Levels MV');
1241 end if;
1242 else
1243 g_all_levels_mv:=true;
1244 if g_debug then
1245 write_to_log_file_n('ALL Levels MV');
1246 end if;
1247 end if;
1248 for i in l_min_rank..l_max_rank loop
1249 --l_mv_level_count:=l_mv_level_count+1;
1250 l_type:='MV';
1251 if i>l_max_mv_levels-1 then
1252 if l_summary_views='Y' then
1253 l_type:='VIEW';
1254 else
1255 exit;
1256 end if;
1257 end if;
1258 for j in 1..l_number_ordered_summary_mv loop
1259 if l_ordered_summary_mv_rank(j)=i then
1260 l_number_child_mv:=0;
1261 for k in 1..l_number_pc_mv loop
1262 if l_parent_summary_mv(k)=l_ordered_summary_mv(j) then
1263 l_number_child_mv:=l_number_child_mv+1;
1264 l_child_mv(l_number_child_mv):=l_child_summary_mv(k);
1265 end if;
1266 end loop;
1267 if g_all_levels_mv=false and l_ordered_summary_mv_rank(j)=l_max_mv_levels-1 then
1268 --this is the highest level of the mv. beyond this, they are all views.
1269 l_create_non_unique_index:=true;
1270 else
1271 l_create_non_unique_index:=false;
1272 end if;
1273 if create_mv_normal(
1274 p_kpi,
1275 l_ordered_summary_mv(j),
1276 l_bsc_owner,
1277 l_child_mv,
1278 l_number_child_mv,
1279 p_options,
1280 p_number_options,
1281 p_apps_origin,
1282 l_type,
1283 l_create_non_unique_index
1284 )=false then
1285 return false;
1286 end if;
1287 end if;
1288 end loop;
1289 end loop;
1290
1291 ---------------------------------------------
1292 if create_zero_code_mv_kpi(
1293 p_kpi,
1294 p_apps_origin,
1295 p_options,
1296 p_number_options,
1297 l_max_rank,
1298 l_bsc_owner,
1299 l_max_mv_levels,
1300 l_ordered_summary_mv,
1301 l_ordered_summary_mv_rank,
1302 l_number_ordered_summary_mv
1303 )=false then
1304 return false;
1305 end if;
1306 ---------------------------------------------
1307 return true;
1308 Exception when others then
1309 BSC_IM_UTILS.g_status_message:=sqlerrm;
1310 g_status_message:=sqlerrm;
1311 write_to_log_file_n('Exception in create_mv_fact '||sqlerrm);
1312 return false;
1313 End;
1314
1315
1316 function create_zero_code_mv_kpi(
1317 p_kpi varchar2,
1318 p_apps_origin varchar2,
1319 p_options BSC_IM_UTILS.varchar_tabletype,
1320 p_number_options number,
1321 p_max_rank number,
1322 p_bsc_owner varchar2,
1323 p_max_mv_levels number,
1324 p_ordered_summary_mv BSC_IM_UTILS.varchar_tabletype,
1325 p_ordered_summary_mv_rank BSC_IM_UTILS.number_tabletype,
1326 p_number_ordered_summary_mv number
1327 ) return boolean is
1328 -------------------------------------------------------------------------
1329 l_object_type BSC_IM_UTILS.varchar_tabletype;
1330 l_description BSC_IM_UTILS.varchar_tabletype;
1331 -------------------------------------------------------------------------
1332 l_child_mv BSC_IM_UTILS.varchar_tabletype;
1333 l_number_child_mv number;
1334 -------------------------------------------------------------------------
1335 --users may say they only want three levels of mv.
1336 l_mv_level_count number;
1337 l_type varchar2(20);
1338 -------------------------------------------------------------------------
1339 l_zero_code_mv BSC_IM_UTILS.varchar_tabletype;
1340 l_number_zero_code_mv number;
1341 -------------------------------------------------------------------------
1342 --to grab the dependency info
1343 l_dep_summary_mv BSC_IM_UTILS.varchar_tabletype;
1344 l_dep_zero_code_mv BSC_IM_UTILS.varchar_tabletype;
1345 l_number_dep_mv number;
1346 -------------------------------------------------------------------------
1347 l_index number;
1348 l_status varchar2(200);
1349 -------------------------------------------------------------------------
1350 l_max_rank number;
1351 l_min_rank number;
1352 l_create_non_unique_index boolean;
1353 Begin
1354 --zero code MV
1355 if g_debug then
1356 write_to_log_file_n('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1357 write_to_log_file('In create_zero_code_mv_kpi '||p_kpi||' '||get_time);
1358 write_to_log_file('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
1359 end if;
1360 g_kpi:=p_kpi;
1361 if BSC_IM_INT_MD.get_object(
1362 p_kpi,
1363 p_apps_origin,
1364 'ZERO CODE MV',
1365 l_zero_code_mv,
1366 l_object_type,
1367 l_description,
1368 l_number_zero_code_mv)=false then
1369 return false;
1370 end if;
1371 if l_number_zero_code_mv>0 then
1372 l_number_dep_mv:=0;
1373 declare
1374 --to grab the dependency info
1375 ll_dep_object_name BSC_IM_UTILS.varchar_tabletype;
1376 ll_dep_object_type BSC_IM_UTILS.varchar_tabletype;
1377 ll_dep_object_desc BSC_IM_UTILS.varchar_tabletype;
1378 ll_number_dep_objects number;
1379 begin
1380 for i in 1..l_number_zero_code_mv loop
1381 if BSC_IM_INT_MD.get_object(
1382 l_zero_code_mv(i),
1383 p_apps_origin,
1384 'MV DEPENDENCY',
1385 ll_dep_object_name,
1386 ll_dep_object_type,
1387 ll_dep_object_desc,
1388 ll_number_dep_objects)=false then
1389 return false;
1390 end if;
1391 for j in 1..ll_number_dep_objects loop
1392 if BSC_IM_UTILS.in_array(l_dep_zero_code_mv,l_dep_summary_mv,l_number_dep_mv,
1393 l_zero_code_mv(i),ll_dep_object_name(j))=false then
1394 l_number_dep_mv:=l_number_dep_mv+1;
1395 l_dep_zero_code_mv(l_number_dep_mv):=l_zero_code_mv(i);
1396 l_dep_summary_mv(l_number_dep_mv):=ll_dep_object_name(j);
1397 end if;
1398 end loop;
1399 end loop;
1400 end;
1401 if g_debug then
1402 write_to_log_file_n('The zero code MV and the corresponding summary MV');
1403 for i in 1..l_number_dep_mv loop
1404 write_to_log_file(l_dep_zero_code_mv(i)||' '||l_dep_summary_mv(i));
1405 end loop;
1406 end if;
1407 -- bug 3867313
1408 l_min_rank:=1000000;
1409 for i in 1..p_number_ordered_summary_mv loop
1410 if substr(p_ordered_summary_mv(i),1,6)='BSC_S_' and
1411 p_ordered_summary_mv_rank(i)<l_min_rank then
1412 l_min_rank:=p_ordered_summary_mv_rank(i);
1413 end if;
1414 end loop;
1415 l_mv_level_count:=0;
1416 l_max_rank:=l_min_rank+p_max_rank;
1417 for i in 1..p_number_ordered_summary_mv loop
1418 write_to_log_file(p_ordered_summary_mv(i)||' '||p_ordered_summary_mv_rank(i));
1419 end loop;
1420 -- bug 3867313
1421 for i in l_min_rank..l_max_rank loop
1422 l_mv_level_count:=l_mv_level_count+1;
1423 /*
1424 3534805
1425 for zero code, using l_mv_level_count is fine.
1426 */
1427 l_type:='MV';
1428 for j in 1..p_number_ordered_summary_mv loop
1429 if p_ordered_summary_mv_rank(j)=i then
1430 --p_ordered_summary_mv_rank(j) is the child mv. for this mv, get the zero code mv by looking at the
1431 --mv dependency
1432 l_number_child_mv:=1;
1433 l_child_mv(l_number_child_mv):=p_ordered_summary_mv(j);
1434 --get the zero code mv
1435 l_index:=BSC_IM_UTILS.get_index(l_dep_summary_mv,l_number_dep_mv,p_ordered_summary_mv(j));
1436 if l_index>0 then
1437 --for zero code mv, we never need to create non unique indexes since there are no views
1438 --on the zero code mv
1439 if p_max_mv_levels is not null and l_mv_level_count>p_max_mv_levels then
1440 l_type:='VIEW';
1441 if g_debug then
1442 write_to_log_file_n('Max levels of MV reached.p_max_mv_levels='||p_max_mv_levels||
1443 ', l_mv_level_count='||l_mv_level_count);
1444 end if;
1445 --l_status:=check_old_mv_view(l_dep_zero_code_mv(l_index),p_bsc_owner,l_type,p_options,p_number_options);
1446 l_status:=check_old_mv_view(l_dep_zero_code_mv(l_index),null,l_type,p_options,p_number_options);
1447 --check_old_mv_view will drop the MV/View if it already exists and the option in RESET MV LEVELS
1448 else
1449 if g_all_levels_mv=false and p_max_mv_levels-1=p_ordered_summary_mv_rank(j) then
1450 --this is the highest level of the mv. beyond this, they are all views.
1451 l_create_non_unique_index:=true;
1452 write_to_log_file('p_max_mv_levels='||p_max_mv_levels||' p_ordered_summary_mv_rank='||p_ordered_summary_mv_rank(j));
1453 if l_create_non_unique_index and g_debug then
1454 write_to_log_file('p_create_non_unique_index=true');
1455 end if;
1456 else
1457 l_create_non_unique_index:=false;
1458 end if;
1459 l_type:='MV';
1460 if create_mv_normal(
1461 p_kpi,
1462 l_dep_zero_code_mv(l_index),
1463 p_bsc_owner,
1464 l_child_mv,
1465 l_number_child_mv,
1466 p_options,
1467 p_number_options,
1468 p_apps_origin,
1469 l_type,
1470 l_create_non_unique_index
1471 )=false then
1472 return false;
1473 end if;
1474 end if;
1475 else
1476 if g_debug then
1477 write_to_log_file_n('Could not find p_ordered_summary_mv(j) in list of dep between zero code mv'||
1478 ' and summary mv. Could not create zero code mv');
1479 end if;
1480 end if;
1481 --------------------------
1482 end if;
1483 end loop;
1484 end loop;
1485 else
1486 if g_debug then
1487 write_to_log_file_n('No zero code MV to create');
1488 end if;
1489 end if;--if l_number_zero_code_mv>0 then
1490 ------------------------------------------
1491 return true;
1492 Exception when others then
1493 BSC_IM_UTILS.g_status_message:=sqlerrm;
1494 g_status_message:=sqlerrm;
1495 write_to_log_file_n('Exception in create_zero_code_mv_kpi '||sqlerrm);
1496 return false;
1497 End;
1498
1499 function get_ordered_mv_list(
1500 p_kpi varchar2,
1501 p_apps_origin varchar2,
1502 p_parent_summary_mv out nocopy BSC_IM_UTILS.varchar_tabletype,
1503 p_child_summary_mv out nocopy BSC_IM_UTILS.varchar_tabletype,
1504 p_number_pc_mv out nocopy number,
1505 p_ordered_summary_mv out nocopy BSC_IM_UTILS.varchar_tabletype,
1506 p_ordered_summary_mv_rank out nocopy BSC_IM_UTILS.number_tabletype,
1507 p_number_ordered_summary_mv out nocopy number,
1508 p_max_rank out nocopy number
1509 )return boolean is
1510 -------------------------------------------------------------------------
1511 l_summary_mv BSC_IM_UTILS.varchar_tabletype;
1512 l_object_type BSC_IM_UTILS.varchar_tabletype;
1513 l_description BSC_IM_UTILS.varchar_tabletype;
1514 l_property BSC_IM_UTILS.varchar_tabletype;
1515 l_number_summary_mv number;
1516 -------------------------------------------------------------------------
1517 Begin
1518 if g_debug then
1519 write_to_log_file_n('In get_ordered_mv_list '||p_kpi||' '||p_apps_origin);
1520 end if;
1521 g_kpi:=p_kpi;
1522 if BSC_IM_INT_MD.get_object(
1523 p_kpi,
1524 p_apps_origin,
1525 'SUMMARY MV',
1526 l_summary_mv,
1527 l_object_type,
1528 l_description,
1529 l_number_summary_mv)=false then
1530 return false;
1531 end if;
1532 if g_debug then
1533 write_to_log_file_n('The summary MV for this KPI');
1534 for i in 1..l_number_summary_mv loop
1535 write_to_log_file(l_summary_mv(i)||' '||l_object_type(i));
1536 end loop;
1537 end if;
1538 ------------------------------------------------
1539 --get the relationship between these objects
1540 declare
1541 ll_object_name BSC_IM_UTILS.varchar_tabletype;
1542 ll_object_type BSC_IM_UTILS.varchar_tabletype;
1543 ll_description BSC_IM_UTILS.varchar_tabletype;
1544 ll_property BSC_IM_UTILS.varchar_tabletype;
1545 ll_number_object number;
1546 begin
1547 p_number_pc_mv:=0;
1548 for i in 1..l_number_summary_mv loop
1549 ll_number_object:=0;
1550 if BSC_IM_INT_MD.get_object(
1551 l_summary_mv(i),
1552 p_apps_origin,
1553 'MV DEPENDENCY',
1554 ll_object_name,
1555 ll_object_type,
1556 ll_description,
1557 ll_number_object)=false then
1558 return false;
1559 end if;
1560 for j in 1..ll_number_object loop
1561 p_number_pc_mv:=p_number_pc_mv+1;
1562 p_parent_summary_mv(p_number_pc_mv):=l_summary_mv(i);
1563 p_child_summary_mv(p_number_pc_mv):=ll_object_name(j);
1564 end loop;
1565 end loop;
1566 if g_debug then
1567 write_to_log_file_n('The parent child relations: parent child');
1568 for i in 1..p_number_pc_mv loop
1569 write_to_log_file(p_parent_summary_mv(i)||' '||p_child_summary_mv(i));
1570 end loop;
1571 end if;
1572 if BSC_IM_UTILS.get_rank(
1573 p_parent_summary_mv,
1574 p_child_summary_mv,
1575 p_number_pc_mv,
1576 p_ordered_summary_mv,
1577 p_ordered_summary_mv_rank,
1578 p_number_ordered_summary_mv,
1579 p_max_rank)=false then
1580 return false;
1581 end if;
1582 for i in 1..l_number_summary_mv loop
1583 if BSC_IM_UTILS.in_array(p_ordered_summary_mv,p_number_ordered_summary_mv,l_summary_mv(i))=false then
1584 p_number_ordered_summary_mv:=p_number_ordered_summary_mv+1;
1585 p_ordered_summary_mv(p_number_ordered_summary_mv):=l_summary_mv(i);
1586 p_ordered_summary_mv_rank(p_number_ordered_summary_mv):=0;
1587 end if;
1588 end loop;
1589 if g_debug then
1590 write_to_log_file_n('The ordered summary MV list');
1591 for i in 1..p_number_ordered_summary_mv loop
1592 write_to_log_file(p_ordered_summary_mv(i)||' '||p_ordered_summary_mv_rank(i));
1593 end loop;
1594 end if;
1595 end;
1596 return true;
1597 Exception when others then
1598 BSC_IM_UTILS.g_status_message:=sqlerrm;
1599 g_status_message:=sqlerrm;
1600 write_to_log_file_n('Exception in get_ordered_mv_list '||sqlerrm);
1601 return false;
1602 End;
1603
1604 function init_all return boolean is
1605 Begin
1606 g_status:=true;
1607 return true;
1608 Exception when others then
1609 BSC_IM_UTILS.g_status_message:=sqlerrm;
1610 g_status_message:=sqlerrm;
1611 write_to_log_file_n('Exception in init_all '||sqlerrm);
1612 return false;
1613 End;
1614
1615 procedure write_to_log_file(p_message varchar2) is
1616 Begin
1617 BSC_IM_UTILS.write_to_log_file(p_message);
1618 Exception when others then
1619 BSC_IM_UTILS.g_status_message:=sqlerrm;
1620 null;
1621 End;
1622
1623 procedure write_to_log_file_n(p_message varchar2) is
1624 begin
1625 write_to_log_file(' ');
1626 write_to_log_file(p_message);
1627 Exception when others then
1628 BSC_IM_UTILS.g_status_message:=sqlerrm;
1629 null;
1630 end;
1631
1632 procedure write_to_debug_n(p_message varchar2) is
1633 begin
1634 if g_debug then
1635 write_to_log_file_n(p_message);
1636 end if;
1637 Exception when others then
1638 BSC_IM_UTILS.g_status_message:=sqlerrm;
1639 null;
1640 end;
1641
1642 procedure write_to_debug(p_message varchar2) is
1643 begin
1644 if g_debug then
1645 write_to_log_file(p_message);
1646 end if;
1647 Exception when others then
1648 BSC_IM_UTILS.g_status_message:=sqlerrm;
1649 null;
1650 end;
1651
1652 function get_time return varchar2 is
1653 begin
1654 return BSC_IM_UTILS.get_time;
1655 Exception when others then
1656 BSC_IM_UTILS.g_status_message:=sqlerrm;
1657 null;
1658 End;
1659
1660 procedure set_globals(
1661 p_debug boolean) is
1662 Begin
1663 g_debug:=p_debug;
1664 BSC_IM_UTILS.set_globals(g_debug);
1665 BSC_IM_INT_MD.set_globals(g_debug);
1666 Exception when others then
1667 BSC_IM_UTILS.g_status_message:=sqlerrm;
1668 null;
1669 End;
1670
1671 --------------------------------------------------------------------------
1672 function refresh_mv_kpi(
1673 p_kpi varchar2,
1674 p_apps_origin varchar2,
1675 p_options BSC_IM_UTILS.varchar_tabletype,
1676 p_number_options number
1677 ) return boolean is
1678 -------------------------------------------------------------------------
1679 l_parent_summary_mv BSC_IM_UTILS.varchar_tabletype;
1680 l_child_summary_mv BSC_IM_UTILS.varchar_tabletype;
1681 l_number_pc_mv number;
1682 -------------------------------------------------------------------------
1683 l_ordered_summary_mv BSC_IM_UTILS.varchar_tabletype;
1684 l_ordered_summary_mv_rank BSC_IM_UTILS.number_tabletype;
1685 l_number_ordered_summary_mv number;
1686 -------------------------------------------------------------------------
1687 l_max_rank number;
1688 l_bsc_owner varchar2(200);
1689 -------------------------------------------------------------------------
1690 l_dummy_mv BSC_IM_UTILS.varchar_tabletype;
1691 l_number_dummy_mv number;
1692 -------------------------------------------------------------------------
1693 l_zero_code_mv BSC_IM_UTILS.varchar_tabletype;
1694 l_object_type BSC_IM_UTILS.varchar_tabletype;
1695 l_description BSC_IM_UTILS.varchar_tabletype;
1696 l_number_zero_code_mv number;
1697 -------------------------------------------------------------------------
1698 l_start_time varchar2(200);
1699 l_end_time varchar2(200);
1700 -------------------------------------------------------------------------
1701 Begin
1702 if g_debug then
1703 write_to_log_file_n('In refresh_mv_kpi for kpi '||p_kpi||get_time);
1704 end if;
1705 g_kpi:=p_kpi;
1706 if g_bsc_owner is null then
1707 g_bsc_owner:=BSC_IM_UTILS.get_bsc_owner;
1708 end if;
1709 l_bsc_owner:=g_bsc_owner;
1710 ------------------------------------------------------------
1711 --find all the ordered list of MV to refresh
1712 ------------------------------------------------
1713 --get the ordered list of mv
1714 if get_ordered_mv_list(
1715 p_kpi,
1716 p_apps_origin,
1717 l_parent_summary_mv,
1718 l_child_summary_mv,
1719 l_number_pc_mv,
1720 l_ordered_summary_mv,
1721 l_ordered_summary_mv_rank,
1722 l_number_ordered_summary_mv,
1723 l_max_rank
1724 )=false then
1725 return false;
1726 end if;
1727 ------------------------------------------------
1728 for i in 0..l_max_rank loop
1729 for j in 1..l_number_ordered_summary_mv loop
1730 if l_ordered_summary_mv_rank(j)=i then
1731 if BSC_IM_UTILS.is_mview(l_ordered_summary_mv(j),null) then
1732 l_start_time:=BSC_IM_UTILS.get_time;
1733 if BSC_IM_UTILS.refresh_mv(l_ordered_summary_mv(j),null,p_kpi,p_options,p_number_options)=false then
1734 g_status_message:=BSC_IM_UTILS.g_status_message;
1735 return false;
1736 end if;
1737 if g_debug then
1738 write_to_log_file_n('MV Refresh Complete '||get_time);
1739 end if;
1740 if object_index_validation(l_ordered_summary_mv(j),null,p_kpi,p_apps_origin,
1741 p_options,p_number_options,null)=false then
1742 return false;
1743 end if;
1744 ----------------
1745 else
1746 if g_debug then
1747 write_to_log_file_n('Object '||l_ordered_summary_mv(j)||' not a MV');
1748 end if;
1749 end if;
1750 --------------------------------------------
1751 end if;
1752 end loop;
1753 end loop;
1754 --------------------------------------------
1755 --the zero code mv
1756 if BSC_IM_INT_MD.get_object(
1757 p_kpi,
1758 p_apps_origin,
1759 'ZERO CODE MV',
1760 l_zero_code_mv,
1761 l_object_type,
1762 l_description,
1763 l_number_zero_code_mv)=false then
1764 return false;
1765 end if;
1766 if l_number_zero_code_mv>0 then
1767 for i in 1..l_number_zero_code_mv loop
1768 if BSC_IM_UTILS.is_mview(l_zero_code_mv(i),null) then
1769 l_start_time:=BSC_IM_UTILS.get_time;
1770 if BSC_IM_UTILS.refresh_mv(l_zero_code_mv(i),null,p_kpi,p_options,p_number_options)=false then
1771 g_status_message:=BSC_IM_UTILS.g_status_message;
1772 return false;
1773 end if;
1774 if g_debug then
1775 write_to_log_file_n('MV Refresh Complete '||get_time);
1776 end if;
1777 if object_index_validation(l_zero_code_mv(i),null,p_kpi,p_apps_origin,
1778 p_options,p_number_options,null)=false then
1779 return false;
1780 end if;
1781 else
1782 if g_debug then
1783 write_to_log_file_n('Object '||l_zero_code_mv(i)||' not a MV');
1784 end if;
1785 end if;
1786 end loop;
1787 end if;
1788 --------------------------------------------
1789 return true;
1790 Exception when others then
1791 BSC_IM_UTILS.g_status_message:=sqlerrm;
1792 g_status_message:=sqlerrm;
1793 write_to_log_file_n('Exception in refresh_mv_kpi '||sqlerrm||get_time);
1794 return false;
1795 End;
1796
1797 --if user needs to refresh just an MV
1798 function refresh_mv(
1799 p_mv varchar2,
1800 p_kpi varchar2,
1801 p_options BSC_IM_UTILS.varchar_tabletype,
1802 p_number_options number
1803 ) return boolean is
1804 l_bsc_owner varchar2(200);
1805 -------------------------------------------------------------------------
1806 Begin
1807 g_kpi:=p_kpi;
1808 if BSC_IM_UTILS.get_option_value(p_options,p_number_options,'DEBUG LOG')='Y' then
1809 g_debug:=true;
1810 end if;
1811 if BSC_IM_UTILS.get_option_value(p_options,p_number_options,'TRACE')='Y' then
1812 BSC_IM_UTILS.set_trace;
1813 end if;
1814 if g_debug then
1815 write_to_log_file_n('In refresh_mv '||p_mv||get_time);
1816 end if;
1817 if g_bsc_owner is null then
1818 g_bsc_owner:=BSC_IM_UTILS.get_bsc_owner;
1819 end if;
1820 l_bsc_owner:=g_bsc_owner;
1821 --first see if this is an MV. if not, no need to process
1822 if BSC_IM_UTILS.is_mview(p_mv,null)=false then
1823 if g_debug then
1824 write_to_log_file_n('Not an MV. Cannot do MV refresh');
1825 end if;
1826 return true;
1827 end if;
1828 /*
1829 we need to get the index info of the mv from the database. if there is a full refresh
1830 then we need to
1831 1. grab the index info into memory (BSC_im_utils.refresh_mv)
1832 2. drop the indexes (BSC_im_utils.refresh_mv)
1833 3. mv full refresh (BSC_im_utils.refresh_mv)
1834 4. re-create the indexes back (BSC_im_utils.refresh_mv)
1835 5. validate the indexes.(BSC_IM_UTILS.object_index_validation)
1836 6. if there are missing indexes, create them with default storage
1837 and tablespace(BSC_IM_UTILS.object_index_validation)
1838 */
1839 if BSC_IM_UTILS.refresh_mv(p_mv,null,p_kpi,p_options,p_number_options)=false then
1840 g_status_message:=BSC_IM_UTILS.g_status_message;
1841 return false;
1842 end if;
1843 if g_debug then
1844 write_to_log_file_n('MV Refresh Complete '||get_time);
1845 end if;
1846 --now do the index validation. for the mv make sure that all indexes are in place
1847 if object_index_validation(p_mv,null,p_kpi,'BSC',p_options,p_number_options,null)=false then
1848 return false;
1849 end if;
1850 return true;
1851 Exception when others then
1852 BSC_IM_UTILS.g_status_message:=sqlerrm;
1853 g_status_message:=sqlerrm;
1854 write_to_log_file_n('Exception in refresh_mv '||sqlerrm||get_time);
1855 return false;
1856 End;
1857 ------------------------------------------------------------------------
1858
1859 function get_dummy_mv(
1860 p_mv_name varchar2,
1861 p_mv_owner varchar2,
1862 p_dummy_mv out nocopy BSC_IM_UTILS.varchar_tabletype,
1863 p_number_dummy_mv out nocopy number
1864 )return boolean is
1865 l_mv_name varchar2(200);
1866 i integer;
1867 Begin
1868 if g_debug then
1869 write_to_log_file_n('In get_dummy_mv');
1870 end if;
1871 p_number_dummy_mv:=0;
1872 i:=1;
1873 loop
1874 l_mv_name:=substr(substr(p_mv_name,1,length(p_mv_name)-3),1,24)||'_D'||i||'MV';
1875 if BSC_IM_UTILS.check_mv(l_mv_name,null)=false then
1876 exit;
1877 else
1878 p_number_dummy_mv:=p_number_dummy_mv+1;
1879 p_dummy_mv(p_number_dummy_mv):=l_mv_name;
1880 i:=i+1;
1881 end if;
1882 end loop;
1883 if g_debug then
1884 write_to_log_file_n('Dummy MVs');
1885 for i in 1..p_number_dummy_mv loop
1886 write_to_log_file(p_dummy_mv(i));
1887 end loop;
1888 end if;
1889 return true;
1890 Exception when others then
1891 BSC_IM_UTILS.g_status_message:=sqlerrm;
1892 g_status_message:=sqlerrm;
1893 write_to_log_file_n('Exception in get_dummy_mv '||sqlerrm||get_time);
1894 return false;
1895 End;
1896
1897 ---------------------------------------------------------------
1898 function drop_mv_kpi(
1899 p_kpi varchar2,
1900 p_apps_origin varchar2,
1901 p_options BSC_IM_UTILS.varchar_tabletype,
1902 p_number_options number
1903 ) return boolean is
1904 -------------------------------------------------------------------------
1905 l_parent_summary_mv BSC_IM_UTILS.varchar_tabletype;
1906 l_child_summary_mv BSC_IM_UTILS.varchar_tabletype;
1907 l_number_pc_mv number;
1908 -------------------------------------------------------------------------
1909 l_ordered_summary_mv BSC_IM_UTILS.varchar_tabletype;
1910 l_ordered_summary_mv_rank BSC_IM_UTILS.number_tabletype;
1911 l_number_ordered_summary_mv number;
1912 -------------------------------------------------------------------------
1913 l_max_rank number;
1914 l_bsc_owner varchar2(200);
1915 -------------------------------------------------------------------------
1916 l_zero_code_mv BSC_IM_UTILS.varchar_tabletype;
1917 l_object_type BSC_IM_UTILS.varchar_tabletype;
1918 l_description BSC_IM_UTILS.varchar_tabletype;
1919 l_number_zero_code_mv number;
1920 -------------------------------------------------------------------------
1921 Begin
1922 if g_debug then
1923 write_to_log_file_n('In drop_mv_kpi for kpi '||p_kpi||get_time);
1924 end if;
1925 g_kpi:=p_kpi;
1926 if g_bsc_owner is null then
1927 g_bsc_owner:=BSC_IM_UTILS.get_bsc_owner;
1928 end if;
1929 l_bsc_owner:=g_bsc_owner;
1930 --get the ordered list of mv
1931 if get_ordered_mv_list(
1932 p_kpi,
1933 p_apps_origin,
1934 l_parent_summary_mv,
1935 l_child_summary_mv,
1936 l_number_pc_mv,
1937 l_ordered_summary_mv,
1938 l_ordered_summary_mv_rank,
1939 l_number_ordered_summary_mv,
1940 l_max_rank
1941 )=false then
1942 return false;
1943 end if;
1944 ------------------------------------------------------
1945 --the zero code mv
1946 if BSC_IM_INT_MD.get_object(
1947 p_kpi,
1948 p_apps_origin,
1949 'ZERO CODE MV',
1950 l_zero_code_mv,
1951 l_object_type,
1952 l_description,
1953 l_number_zero_code_mv)=false then
1954 return false;
1955 end if;
1956 if l_number_zero_code_mv>0 then
1957 for i in 1..l_number_zero_code_mv loop
1958 if BSC_IM_UTILS.drop_object(l_zero_code_mv(i),null)=false then
1959 null;
1960 end if;
1961 if BSC_IM_UTILS.drop_synonym(l_zero_code_mv(i))=false then
1962 null;
1963 end if;
1964 end loop;
1965 end if;
1966 ------------------------------------------------------
1967 for i in 0..l_max_rank loop
1968 for j in 1..l_number_ordered_summary_mv loop
1969 if l_ordered_summary_mv_rank(j)=l_max_rank-i then
1970 if g_debug then
1971 write_to_log_file_n('Drop '||l_ordered_summary_mv(j));
1972 end if;
1973 if BSC_IM_UTILS.drop_object(l_ordered_summary_mv(j),null)=false then
1974 null;
1975 end if;
1976 if BSC_IM_UTILS.drop_synonym(l_ordered_summary_mv(j))=false then
1977 null;
1978 end if;
1979 end if;
1980 end loop;
1981 end loop;
1982 --------------------------------------------------
1983 return true;
1984 Exception when others then
1985 BSC_IM_UTILS.g_status_message:=sqlerrm;
1986 g_status_message:=sqlerrm;
1987 write_to_log_file_n('Exception in drop_mv_kpi '||sqlerrm||get_time);
1988 return false;
1989 End;
1990
1991 function drop_mv(
1992 p_mv varchar2,
1993 p_options BSC_IM_UTILS.varchar_tabletype,
1994 p_number_options number
1995 )return boolean is
1996 l_bsc_owner varchar2(200);
1997 Begin
1998 if g_bsc_owner is null then
1999 g_bsc_owner:=BSC_IM_UTILS.get_bsc_owner;
2000 end if;
2001 l_bsc_owner:=g_bsc_owner;
2002 if BSC_IM_UTILS.is_mview(p_mv,null) then
2003 if BSC_IM_UTILS.drop_mv(p_mv,null)=false then
2004 null;
2005 end if;
2006 if BSC_IM_UTILS.drop_synonym(p_mv)=false then
2007 null;
2008 end if;
2009 else
2010 if BSC_IM_UTILS.drop_view(p_mv,null)=false then
2011 null;
2012 end if;
2013 end if;
2014 return true;
2015 Exception when others then
2016 BSC_IM_UTILS.g_status_message:=sqlerrm;
2017 g_status_message:=sqlerrm;
2018 write_to_log_file_n('Exception in drop_mv '||sqlerrm||get_time);
2019 return false;
2020 End;
2021 ---------------------------------------------------------------
2022
2023 function drop_summary_objects(
2024 p_mv_list varchar2,
2025 p_synonym_list varchar2,
2026 p_options varchar2,
2027 p_error_message out nocopy varchar2
2028 ) return boolean is
2029 l_method varchar2(200);
2030 l_mv BSC_IM_UTILS.varchar_tabletype;
2031 l_mv_owner BSC_IM_UTILS.varchar_tabletype;
2032 l_s_table BSC_IM_UTILS.varchar_tabletype;
2033 l_number_mv number;
2034 l_list varchar2(32000);
2035 l_options BSC_IM_UTILS.varchar_tabletype;
2036 l_number_options number;
2037 Begin
2038 p_error_message:=null;
2039 l_number_options:=0;
2040 if BSC_IM_UTILS.parse_values(p_options,',',l_options,l_number_options)=false then
2041 return false;
2042 end if;
2043 if BSC_IM_UTILS.get_option_value(l_options,l_number_options,'DEBUG LOG')='Y' then
2044 g_debug:=true;
2045 set_globals(g_debug);
2046 end if;
2047 if BSC_IM_UTILS.get_option_value(l_options,l_number_options,'TRACE')='Y' then
2048 BSC_IM_UTILS.set_trace;
2049 end if;
2050 if g_debug then
2051 write_to_log_file_n('In drop_summary_objects '||get_time);
2052 write_to_log_file('p_mv_list='||p_mv_list);
2053 write_to_log_file('p_synonym_list='||p_synonym_list);
2054 write_to_log_file('p_options='||p_options);
2055 end if;
2056 l_number_mv:=0;
2057 if BSC_IM_UTILS.parse_values(p_mv_list,',',l_mv,l_number_mv)=false then
2058 return false;
2059 end if;
2060 if BSC_IM_UTILS.parse_values(p_synonym_list,',',l_s_table,l_number_mv)=false then
2061 return false;
2062 end if;
2063 for i in 1..l_number_mv loop
2064 l_mv_owner(i):=BSC_IM_UTILS.get_table_owner(l_mv(i));
2065 end loop;
2066 if g_debug then
2067 write_to_log_file_n('The MV, S table and the owner');
2068 for i in 1..l_number_mv loop
2069 write_to_log_file(l_mv(i)||' '||l_s_table(i)||' '||l_mv_owner(i));
2070 end loop;
2071 end if;
2072 --drop the table, mv and the synonym
2073 for i in 1..l_number_mv loop
2074 if BSC_IM_UTILS.drop_mv(l_mv(i),l_mv_owner(i))=false then
2075 null;
2076 end if;
2077 if BSC_IM_UTILS.drop_synonym(l_mv(i))=false then
2078 null;
2079 end if;
2080 if BSC_IM_UTILS.drop_table(l_s_table(i),l_mv_owner(i))=false then
2081 null;
2082 end if;
2083 if BSC_IM_UTILS.drop_synonym(l_s_table(i))=false then
2084 null;
2085 end if;
2086 end loop;
2087 return true;
2088 Exception when others then
2089 BSC_IM_UTILS.g_status_message:=sqlerrm;
2090 p_error_message:=sqlerrm;
2091 g_status_message:=sqlerrm;
2092 write_to_log_file_n('Exception in drop_summary_objects '||sqlerrm||get_time);
2093 return false;
2094 End;
2095
2096 /*
2097 check to make sure that all indexes are present in the database.
2098 if any are missing, create them
2099 This API is actually used for error handling. In the initial refresh,
2100 we drop the MV indexes, the MV refresh could have succeeded but the
2101 index creation could have failed. Next time around, we should correct
2102 this problem and create the missing indexes.
2103 */
2104 function object_index_validation(
2105 p_object varchar2,
2106 p_owner varchar2,
2107 p_kpi varchar2,
2108 p_apps_origin varchar2,
2109 p_options BSC_IM_UTILS.varchar_tabletype,
2110 p_number_options number,
2111 p_create_non_unique_index boolean
2112 )return boolean is
2113 l_apps_origin varchar2(200);
2114 l_owner varchar2(200);
2115 ---BSC----------------------
2116 l_s_tables BSC_IM_UTILS.varchar_tabletype;
2117 l_number_s_tables number;
2118 l_fk BSC_IM_UTILS.varchar_tabletype;
2119 l_number_fk number;
2120 -------------------------------------------------------------------------
2121 l_index BSC_IM_UTILS.varchar_tabletype;
2122 l_uniqueness BSC_IM_UTILS.varchar_tabletype;
2123 l_tablespace BSC_IM_UTILS.varchar_tabletype;
2124 l_initial_extent BSC_IM_UTILS.number_tabletype;
2125 l_next_extent BSC_IM_UTILS.number_tabletype;
2126 l_max_extents BSC_IM_UTILS.number_tabletype;
2127 l_pct_increase BSC_IM_UTILS.number_tabletype;
2128 l_number_index number;
2129 ------
2130 l_ind_name BSC_IM_UTILS.varchar_tabletype;
2131 l_ind_col BSC_IM_UTILS.varchar_tabletype;
2132 l_number_ind_col number;
2133 -------------------------------------------------------------------------
2134 l_stmt varchar2(20000);
2135 l_index_tablespace varchar2(200);
2136 l_index_storage varchar2(2000);
2137 -------------------------------------------------------------------------
2138 l_create_non_unique_index boolean;
2139 -------------------------------------------------------------------------
2140
2141 l_zero_code_cols dbms_sql.varchar2_table;
2142 -----
2143 l_higher_level_view_exists boolean ;
2144 l_time_columns VARCHAR2(100):= '(PERIODICITY_ID, YEAR, PERIOD)';
2145 l_index_counter number := 1;
2146 l_nonzero_code_cols dbms_sql.varchar2_table;
2147 l_is_zmv boolean;
2148 l_zmv_exists_for_mv boolean;
2149 l_mv_name_for_zmv varchar2(100);
2150
2151 Begin
2152 return true;
2153 -- this is not required as we are calling create_mv_index already from refresh
2154 if g_debug then
2155 write_to_log_file_n('In object_index_validation for '||p_object||' '||p_owner||' p_apps_origin '||p_apps_origin||
2156 ' p_kpi '||p_kpi);
2157 end if;
2158 g_kpi:=p_kpi;
2159 l_create_non_unique_index:=p_create_non_unique_index ;
2160 l_apps_origin:=p_apps_origin;
2161 l_owner:=p_owner;
2162 if l_owner is null then
2163 l_owner:=bsc_im_utils.get_table_owner(p_object);
2164 if g_debug then
2165 write_to_log_file('l_owner='||l_owner);
2166 end if;
2167 end if;
2168 -----------------
2169 l_index_tablespace:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'INDEX TABLESPACE');
2170 l_index_storage:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'INDEX STORAGE');
2171 if l_index_tablespace is not null then
2172 if instr(lower(l_index_tablespace),'tablespace')<=0 then
2173 l_index_tablespace:=' tablespace '||l_index_tablespace;
2174 end if;
2175 end if;
2176 if l_index_storage is not null then
2177 if instr(lower(l_index_storage),'storage')<=0 then
2178 l_index_storage:=' storage '||l_index_storage;
2179 end if;
2180 end if;
2181 -----------------
2182 if l_apps_origin='BSC' then
2183 if BSC_BSC_ADAPTER.get_s_tables_for_mv(p_object,l_s_tables,l_number_s_tables)=false then
2184 return false;
2185 end if;
2186 if l_number_s_tables=0 then
2187 if g_debug then
2188 write_to_log_file_n('No s tables found for '||p_object||'. Do Validations to do...');
2189 end if;
2190 return true;
2191 end if;
2192 if BSC_BSC_ADAPTER.get_table_fks(l_s_tables,l_number_s_tables,l_fk,l_number_fk)=false then
2193 return false;
2194 end if;
2195 --first get the index info from the database
2196 if BSC_IM_UTILS.get_table_indexes(
2197 p_object,
2198 l_owner,
2199 l_index,
2200 l_uniqueness,
2201 l_tablespace,
2202 l_initial_extent,
2203 l_next_extent,
2204 l_max_extents,
2205 l_pct_increase,
2206 l_number_index,
2207 l_ind_name,
2208 l_ind_col,
2209 l_number_ind_col)=false then
2210 return false;
2211 end if;
2212 if create_mv_index(p_object,
2213 p_owner,
2214 p_kpi,
2215 p_apps_origin,
2216 l_index_tablespace,
2217 l_index_storage,
2218 p_create_non_unique_index,
2219 true-- called from refresh
2220 )= false then
2221 return false;
2222 else
2223 return true;
2224 end if;
2225
2226
2227 --quick check
2228 /*if l_number_index>=l_number_fk-3 then
2229 if g_debug then
2230 write_to_log_file_n('All indexes present.');
2231 end if;
2232 else
2233 --need to create missing indexes
2234 if g_debug then
2235 write_to_log_file_n('Going to create missing indexes');
2236 end if;
2237 if l_create_non_unique_index is null then
2238 --if the parent of this mv is a view, then create the non-unique indexes
2239 if BSC_IM_UTILS.is_parent_of_type_present(p_object,'VIEW') then
2240 l_create_non_unique_index:=true;
2241 else
2242 l_create_non_unique_index:=false;
2243 end if;
2244 end if;
2245 if l_create_non_unique_index then
2246 --try the non unique ones
2247 for i in 1..l_number_fk loop
2248 if l_fk(i)<>'PERIOD' and l_fk(i)<>'TYPE' and l_fk(i)<>'PERIODICITY_ID' and
2249 l_fk(i)<>'YEAR' then
2250 l_stmt:='create index '||l_owner||'.'||p_object||'N'||i||' on '||l_owner||'.'||p_object||'(';
2251 l_stmt:=l_stmt||l_fk(i)||',PERIODICITY_ID,YEAR,PERIOD)';
2252 --Enh#4239064: create index in parallel
2253 l_stmt:=l_stmt||l_index_tablespace||' '||l_index_storage||' parallel';
2254 if BSC_IM_UTILS.create_index(l_stmt,null)=false then
2255 return false;
2256 end if;
2257 --Enh#4239064: set to noparallel
2258 execute immediate 'alter index '||l_owner||'.'||p_object||'N'||i||' noparallel';
2259 end if;
2260 end loop;
2261 end if;
2262 end if;
2263 */
2264 end if;--if l_apps_origin='BSC' then
2265
2266 return true;
2267 Exception when others then
2268 g_status_message:=sqlerrm;
2269 write_to_log_file_n('Exception in object_index_validation '||sqlerrm);
2270 return false;
2271 End;
2272
2273 function check_old_mv_view(
2274 p_mv_name varchar2,
2275 p_mv_owner varchar2,
2276 p_type varchar2,
2277 p_options BSC_IM_UTILS.varchar_tabletype,
2278 p_number_options number
2279 )return varchar2 is
2280 l_status varchar2(200);
2281 Begin
2282 l_status:='CONTINUE';
2283 --check to see if MV RECREATE flag is off and MV already exists. then no need to do anything
2284 if BSC_IM_UTILS.get_option_value(p_options,p_number_options,'RECREATE')='Y' then
2285 if g_debug then
2286 write_to_log_file_n('MV RECREATE flag TRUE. Dropping the MV/View first first');
2287 end if;
2288 --p_mv_owner
2289 if drop_mv(p_mv_name,p_options,0)=false then
2290 null;
2291 end if;
2292 elsif BSC_IM_UTILS.get_option_value(p_options,p_number_options,'RESET MV LEVELS')='Y' then
2293 if p_type='MV' then
2294 if BSC_IM_UTILS.check_mv(p_mv_name,null) then
2295 if g_debug then
2296 write_to_log_file_n('MV '||p_mv_name||' already present');
2297 end if;
2298 return 'ALREADY PRESENT';
2299 else
2300 if g_debug then
2301 write_to_log_file_n(p_mv_name||' is a view...dropping');
2302 end if;
2303 if BSC_IM_UTILS.drop_view(p_mv_name,null)=false then
2304 null;
2305 end if;
2306 end if;
2307 elsif p_type='VIEW' then
2308 if BSC_IM_UTILS.check_view(p_mv_name,null) then
2309 if g_debug then
2310 write_to_log_file_n('View '||p_mv_name||' already present');
2311 end if;
2312 return 'ALREADY PRESENT';
2313 else
2314 if g_debug then
2315 write_to_log_file_n(p_mv_name||' is a MV...dropping');
2316 end if;
2317 if drop_mv(p_mv_name,p_options,0)=false then
2318 null;
2319 end if;
2320 end if;
2321 end if;
2322 else
2323 if p_type='MV' then
2324 if BSC_IM_UTILS.check_mv(p_mv_name,null) then
2325 if g_debug then
2326 write_to_log_file_n('MV RECREATE flag FALSE. MV already exists. No need to create the MV');
2327 end if;
2328 return 'ALREADY PRESENT';
2329 end if;
2330 elsif p_type='VIEW' then
2331 if BSC_IM_UTILS.check_view(p_mv_name,null) then
2332 if g_debug then
2333 write_to_log_file_n('MV RECREATE flag FALSE. View already exists. No need to create the View');
2334 end if;
2335 return 'ALREADY PRESENT';
2336 end if;
2337 end if;
2338 end if;
2339 return l_status;
2340 Exception when others then
2341 g_status_message:=sqlerrm;
2342 write_to_log_file_n('Exception in check_old_mv_view '||sqlerrm);
2343 return 'ERROR';
2344 End;
2345
2346 END BSC_MV_ADAPTER;
[Home] [Help]