eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BSC_MV_ADAPTER dependencies on BSC_IM_UTILS

Line 28: p_child_mv BSC_IM_UTILS.varchar_tabletype,

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,

Line 30: p_options BSC_IM_UTILS.varchar_tabletype,

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

Line 41: l_map_name BSC_IM_UTILS.varchar_tabletype;

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;

Line 42: l_map_type BSC_IM_UTILS.varchar_tabletype;

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;

Line 43: l_object_name BSC_IM_UTILS.varchar_tabletype;

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;

Line 44: l_map_property BSC_IM_UTILS.varchar_tabletype;

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: --------------------------------------

Line 45: l_detail_property BSC_IM_UTILS.varchar_tabletype;

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

Line 46: l_chosen_map BSC_IM_UTILS.boolean_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;

Line 50: l_line 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

Line 51: l_line_type BSC_IM_UTILS.varchar_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;

Line 55: l_snplog_created BSC_IM_UTILS.boolean_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;

Line 57: l_b_tables BSC_IM_UTILS.varchar_tabletype;

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;

Line 58: l_base_snplog_created BSC_IM_UTILS.boolean_tabletype;

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;

Line 61: l_level_tables BSC_IM_UTILS.varchar_tabletype;

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);

Line 62: l_level_snplog_created BSC_IM_UTILS.boolean_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);

Line 82: l_db_version:=BSC_IM_UTILS.get_db_version;

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

Line 84: if BSC_IM_UTILS.get_option_value(p_options,p_number_options,'FULL REFRESH')='Y' then

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;

Line 103: l_tablespace:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'TABLESPACE');

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

Line 104: l_storage:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'STORAGE');

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

Line 105: l_index_tablespace:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'INDEX TABLESPACE');

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;

Line 106: l_index_storage:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'INDEX STORAGE');

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;

Line 173: ll_b_tables BSC_IM_UTILS.varchar_tabletype;

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

Line 180: ll_string:=BSC_IM_UTILS.get_option_value(l_map_property(i),',','BASE TABLES');

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;

Line 182: if BSC_IM_UTILS.parse_values(ll_string,'+',ll_b_tables,ll_number_b_tables)=false then

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

Line 186: if BSC_IM_UTILS.in_array(l_b_tables,l_number_b_tables,ll_b_tables(j))=false 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;

Line 219: ll_level_tables BSC_IM_UTILS.varchar_tabletype;

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

Line 226: ll_string:=BSC_IM_UTILS.get_option_value(l_map_property(i),',','DIM LEVELS');

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;

Line 228: if BSC_IM_UTILS.parse_values(ll_string,'+',ll_level_tables,ll_number_level_tables)=false then

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

Line 232: if BSC_IM_UTILS.in_array(l_level_tables,l_number_level_tables,ll_level_tables(j))=false 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;

Line 281: if BSC_IM_UTILS.parse_and_find(l_map_property(i),',','FULL REFRESH') then

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;

Line 287: if BSC_IM_UTILS.parse_and_find(l_map_property(i),',','FAST REFRESH') then

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;

Line 411: BSC_IM_UTILS.g_status_message:=sqlerrm;

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

Line 424: if BSC_IM_UTILS.drop_mv_log(p_child_mv(i),null)=false then

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;

Line 427: --if BSC_IM_UTILS.drop_constraint(p_child_mv(i),null,p_child_mv(i)||'_PK')=false then

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;

Line 439: if BSC_IM_UTILS.drop_mv_log(l_b_tables(i),null)=false then

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;

Line 442: --if BSC_IM_UTILS.drop_constraint(l_b_tables(i),null,l_b_tables(i)||'_PK')=false then

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;

Line 454: if BSC_IM_UTILS.drop_mv_log(l_level_tables(i),null)=false then

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;

Line 457: --if BSC_IM_UTILS.drop_constraint(l_level_tables(i),null,l_level_tables(i)||'_PK')=false then

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;

Line 483: BSC_IM_UTILS.write_to_log_file_n(p_type||' '||p_mv_name||' Created');

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;

Line 486: BSC_IM_UTILS.write_to_log_file_n(' -> FAST REFRESH');

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');

Line 490: BSC_IM_UTILS.write_to_log_file_n(' -> FULL REFRESH');

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: --------------------------------------------

Line 493: BSC_IM_UTILS.write_to_log_file_n(' ');

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;

Line 497: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 504: p_b_tables BSC_IM_UTILS.varchar_tabletype,

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

Line 516: g_bsc_owner:=BSC_IM_UTILS.get_bsc_owner;

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

Line 522: if BSC_IM_UTILS.drop_mv(l_mv_name,null)=false then

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;

Line 525: if BSC_IM_UTILS.drop_synonym(l_mv_name)=false then

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 '||

Line 539: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 548: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 561: l_object_type BSC_IM_UTILS.varchar_tabletype;

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);

Line 562: l_description BSC_IM_UTILS.varchar_tabletype;

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

Line 563: l_zero_code_mv BSC_IM_UTILS.varchar_tabletype;

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

Line 590: l_owner:=bsc_im_utils.get_table_owner(p_mv_name);

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

Line 656: l_fk_name BSC_IM_UTILS.varchar_tabletype;

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;

Line 657: l_fk_type BSC_IM_UTILS.varchar_tabletype;

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;

Line 658: l_uk_name BSC_IM_UTILS.varchar_tabletype;

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;

Line 659: l_uk_parent_name BSC_IM_UTILS.varchar_tabletype;

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: ------------------------------------------------------------

Line 660: l_fk_property BSC_IM_UTILS.varchar_tabletype;

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);

Line 661: l_description 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);

Line 682: l_s_tables BSC_IM_UTILS.varchar_tabletype;

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

Line 690: l_owner:=bsc_im_utils.get_table_owner(p_mv_name);

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

Line 753: --l_db_version:=BSC_IM_UTILS.get_db_version;

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

Line 765: if BSC_IM_UTILS.create_index(l_stmt,null)=false then

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

Line 774: if p_create_non_unique_index OR BSC_IM_UTILS.is_parent_of_type_present(l_mv_name_for_zmv,'VIEW') then

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;

Line 790: if(BSC_IM_UTILS.needs_zero_code_mv(p_mv_name, p_kpi, l_fk_name(i))) 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');

Line 828: if BSC_IM_UTILS.create_index(l_stmt,null)=false then

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

Line 859: if BSC_IM_UTILS.create_index(l_stmt,null)=false then

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;

Line 874: if BSC_IM_UTILS.create_index(l_stmt,null)=false then

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;

Line 905: if BSC_IM_UTILS.create_index(l_stmt,null)=false then

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

Line 916: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 926: p_already_included IN BSC_IM_UTILS.varchar_tabletype,

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

Line 928: p_columns IN OUT nocopy BSC_IM_UTILS.varchar_tabletype,

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

Line 939: IF bsc_im_utils.in_array(p_already_included, p_included_count, i.relation_col)=FALSE AND

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);

Line 940: bsc_im_utils.in_array(p_columns, p_num_columns, i.relation_col)=FALSE THEN

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;

Line 948: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 958: p_options BSC_IM_UTILS.varchar_tabletype,

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;

Line 962: l_column_name BSC_IM_UTILS.varchar_tabletype;

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;

Line 963: l_column_type 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;

Line 964: l_column_data_type BSC_IM_UTILS.varchar_tabletype;

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;

Line 965: l_column_origin BSC_IM_UTILS.varchar_tabletype;

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;

Line 966: l_aggregation_type BSC_IM_UTILS.varchar_tabletype;

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: ------------------------------------------------------------

Line 967: l_description 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;

Line 968: l_property 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;

Line 971: l_fk_name 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;

Line 972: l_fk_type 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;

Line 973: l_uk_name 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: ------------------------------------------------------------

Line 974: l_uk_parent_name BSC_IM_UTILS.varchar_tabletype;

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;

Line 975: l_fk_property BSC_IM_UTILS.varchar_tabletype;

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

Line 1025: if BSC_IM_UTILS.create_mv_log_on_table(

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,

Line 1040: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 1054: if BSC_IM_UTILS.drop_synonym(p_level)=false then

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');

Line 1061: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 1075: l_owner:=bsc_im_utils.get_table_owner(p_mv_name);

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;

Line 1083: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 1092: p_options BSC_IM_UTILS.varchar_tabletype,

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;

Line 1096: l_summary_mv BSC_IM_UTILS.varchar_tabletype;

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;

Line 1097: l_object_type 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: -------------------------------------------------------------------------

Line 1098: l_description BSC_IM_UTILS.varchar_tabletype;

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;

Line 1099: l_property BSC_IM_UTILS.varchar_tabletype;

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;

Line 1102: l_parent_summary_mv 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;

Line 1103: l_child_summary_mv 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;

Line 1106: l_ordered_summary_mv BSC_IM_UTILS.varchar_tabletype;

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;

Line 1107: l_ordered_summary_mv_rank BSC_IM_UTILS.number_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;

Line 1114: l_child_mv BSC_IM_UTILS.varchar_tabletype;

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;

Line 1131: BSC_IM_UTILS.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;

Line 1132: BSC_IM_UTILS.write_to_log_file_n('Create MV/Views for KPI '||p_kpi);

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;

Line 1133: BSC_IM_UTILS.write_to_log_file_n('++++++++++++++++++++++++++++++++++++++++++++++++++++++');

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;

Line 1135: g_bsc_owner:=BSC_IM_UTILS.get_bsc_owner;

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

Line 1155: l_max_mv_levels:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'MV LEVELS');

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);

Line 1156: l_summary_views:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'SUMMARY VIEWS');

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;

Line 1309: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 1319: p_options BSC_IM_UTILS.varchar_tabletype,

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,

Line 1324: p_ordered_summary_mv 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: -------------------------------------------------------------------------

Line 1325: p_ordered_summary_mv_rank BSC_IM_UTILS.number_tabletype,

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;

Line 1329: l_object_type 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;

Line 1330: l_description BSC_IM_UTILS.varchar_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: -------------------------------------------------------------------------

Line 1332: l_child_mv BSC_IM_UTILS.varchar_tabletype;

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;

Line 1339: l_zero_code_mv BSC_IM_UTILS.varchar_tabletype;

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;

Line 1343: l_dep_summary_mv BSC_IM_UTILS.varchar_tabletype;

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;

Line 1344: l_dep_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);

Line 1375: ll_dep_object_name BSC_IM_UTILS.varchar_tabletype;

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

Line 1376: ll_dep_object_type BSC_IM_UTILS.varchar_tabletype;

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

Line 1377: ll_dep_object_desc BSC_IM_UTILS.varchar_tabletype;

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(

Line 1392: if BSC_IM_UTILS.in_array(l_dep_zero_code_mv,l_dep_summary_mv,l_number_dep_mv,

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);

Line 1435: l_index:=BSC_IM_UTILS.get_index(l_dep_summary_mv,l_number_dep_mv,p_ordered_summary_mv(j));

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

Line 1493: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 1502: p_parent_summary_mv out nocopy BSC_IM_UTILS.varchar_tabletype,

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,

Line 1503: p_child_summary_mv out nocopy BSC_IM_UTILS.varchar_tabletype,

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,

Line 1505: p_ordered_summary_mv out nocopy BSC_IM_UTILS.varchar_tabletype,

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

Line 1506: p_ordered_summary_mv_rank out nocopy BSC_IM_UTILS.number_tabletype,

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: -------------------------------------------------------------------------

Line 1511: l_summary_mv BSC_IM_UTILS.varchar_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;

Line 1512: l_object_type BSC_IM_UTILS.varchar_tabletype;

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: -------------------------------------------------------------------------

Line 1513: l_description BSC_IM_UTILS.varchar_tabletype;

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

Line 1514: l_property BSC_IM_UTILS.varchar_tabletype;

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

Line 1541: ll_object_name BSC_IM_UTILS.varchar_tabletype;

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;

Line 1542: ll_object_type BSC_IM_UTILS.varchar_tabletype;

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

Line 1543: ll_description BSC_IM_UTILS.varchar_tabletype;

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;

Line 1544: ll_property BSC_IM_UTILS.varchar_tabletype;

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

Line 1572: if BSC_IM_UTILS.get_rank(

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,

Line 1583: if BSC_IM_UTILS.in_array(p_ordered_summary_mv,p_number_ordered_summary_mv,l_summary_mv(i))=false then

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;

Line 1598: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 1609: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 1617: BSC_IM_UTILS.write_to_log_file(p_message);

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;

Line 1619: BSC_IM_UTILS.g_status_message:=sqlerrm;

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

Line 1628: BSC_IM_UTILS.g_status_message:=sqlerrm;

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

Line 1638: BSC_IM_UTILS.g_status_message:=sqlerrm;

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

Line 1648: BSC_IM_UTILS.g_status_message:=sqlerrm;

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

Line 1654: return BSC_IM_UTILS.get_time;

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;

Line 1656: BSC_IM_UTILS.g_status_message:=sqlerrm;

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(

Line 1664: BSC_IM_UTILS.set_globals(g_debug);

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;

Line 1667: BSC_IM_UTILS.g_status_message:=sqlerrm;

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: --------------------------------------------------------------------------

Line 1675: p_options BSC_IM_UTILS.varchar_tabletype,

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;

Line 1679: l_parent_summary_mv BSC_IM_UTILS.varchar_tabletype;

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;

Line 1680: l_child_summary_mv 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;

Line 1683: l_ordered_summary_mv BSC_IM_UTILS.varchar_tabletype;

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;

Line 1684: l_ordered_summary_mv_rank BSC_IM_UTILS.number_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);

Line 1690: l_dummy_mv BSC_IM_UTILS.varchar_tabletype;

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;

Line 1693: l_zero_code_mv BSC_IM_UTILS.varchar_tabletype;

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: -------------------------------------------------------------------------

Line 1694: l_object_type BSC_IM_UTILS.varchar_tabletype;

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);

Line 1695: l_description 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);

Line 1707: g_bsc_owner:=BSC_IM_UTILS.get_bsc_owner;

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

Line 1731: if BSC_IM_UTILS.is_mview(l_ordered_summary_mv(j),null) then

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;

Line 1732: l_start_time:=BSC_IM_UTILS.get_time;

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;

Line 1733: if BSC_IM_UTILS.refresh_mv(l_ordered_summary_mv(j),null,p_kpi,p_options,p_number_options)=false then

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

Line 1734: g_status_message:=BSC_IM_UTILS.g_status_message;

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);

Line 1768: if BSC_IM_UTILS.is_mview(l_zero_code_mv(i),null) 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;

Line 1769: l_start_time:=BSC_IM_UTILS.get_time;

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;

Line 1770: if BSC_IM_UTILS.refresh_mv(l_zero_code_mv(i),null,p_kpi,p_options,p_number_options)=false then

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

Line 1771: g_status_message:=BSC_IM_UTILS.g_status_message;

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);

Line 1791: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 1801: p_options BSC_IM_UTILS.varchar_tabletype,

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: -------------------------------------------------------------------------

Line 1808: if BSC_IM_UTILS.get_option_value(p_options,p_number_options,'DEBUG LOG')='Y' then

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;

Line 1811: if BSC_IM_UTILS.get_option_value(p_options,p_number_options,'TRACE')='Y' then

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);

Line 1812: BSC_IM_UTILS.set_trace;

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;

Line 1818: g_bsc_owner:=BSC_IM_UTILS.get_bsc_owner;

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

Line 1822: if BSC_IM_UTILS.is_mview(p_mv,null)=false 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;

Line 1831: 1. grab the index info into memory (BSC_im_utils.refresh_mv)

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)

Line 1832: 2. drop the indexes (BSC_im_utils.refresh_mv)

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

Line 1833: 3. mv full refresh (BSC_im_utils.refresh_mv)

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)

Line 1834: 4. re-create the indexes back (BSC_im_utils.refresh_mv)

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: */

Line 1835: 5. validate the indexes.(BSC_IM_UTILS.object_index_validation)

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

Line 1837: and tablespace(BSC_IM_UTILS.object_index_validation)

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;

Line 1839: if BSC_IM_UTILS.refresh_mv(p_mv,null,p_kpi,p_options,p_number_options)=false then

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

Line 1840: g_status_message:=BSC_IM_UTILS.g_status_message;

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);

Line 1852: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 1862: p_dummy_mv out nocopy BSC_IM_UTILS.varchar_tabletype,

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;

Line 1875: if BSC_IM_UTILS.check_mv(l_mv_name,null)=false then

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;

Line 1891: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 1901: p_options BSC_IM_UTILS.varchar_tabletype,

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;

Line 1905: l_parent_summary_mv BSC_IM_UTILS.varchar_tabletype;

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;

Line 1906: l_child_summary_mv 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;

Line 1909: l_ordered_summary_mv BSC_IM_UTILS.varchar_tabletype;

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;

Line 1910: l_ordered_summary_mv_rank BSC_IM_UTILS.number_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);

Line 1916: l_zero_code_mv BSC_IM_UTILS.varchar_tabletype;

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: -------------------------------------------------------------------------

Line 1917: l_object_type BSC_IM_UTILS.varchar_tabletype;

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

Line 1918: l_description BSC_IM_UTILS.varchar_tabletype;

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

Line 1927: g_bsc_owner:=BSC_IM_UTILS.get_bsc_owner;

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(

Line 1958: if BSC_IM_UTILS.drop_object(l_zero_code_mv(i),null)=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;

Line 1961: if BSC_IM_UTILS.drop_synonym(l_zero_code_mv(i))=false 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;

Line 1973: if BSC_IM_UTILS.drop_object(l_ordered_summary_mv(j),null)=false then

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;

Line 1976: if BSC_IM_UTILS.drop_synonym(l_ordered_summary_mv(j))=false then

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;

Line 1985: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 1993: p_options BSC_IM_UTILS.varchar_tabletype,

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

Line 1999: g_bsc_owner:=BSC_IM_UTILS.get_bsc_owner;

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

Line 2002: if BSC_IM_UTILS.is_mview(p_mv,null) then

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

Line 2003: if BSC_IM_UTILS.drop_mv(p_mv,null)=false 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;

Line 2006: if BSC_IM_UTILS.drop_synonym(p_mv)=false then

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

Line 2010: if BSC_IM_UTILS.drop_view(p_mv,null)=false then

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;

Line 2016: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 2030: l_mv BSC_IM_UTILS.varchar_tabletype;

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);

Line 2031: l_mv_owner BSC_IM_UTILS.varchar_tabletype;

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;

Line 2032: l_s_table BSC_IM_UTILS.varchar_tabletype;

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;

Line 2035: l_options 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;

Line 2040: if BSC_IM_UTILS.parse_values(p_options,',',l_options,l_number_options)=false then

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;

Line 2043: if BSC_IM_UTILS.get_option_value(l_options,l_number_options,'DEBUG LOG')='Y' then

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

Line 2047: if BSC_IM_UTILS.get_option_value(l_options,l_number_options,'TRACE')='Y' then

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);

Line 2048: BSC_IM_UTILS.set_trace;

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);

Line 2057: if BSC_IM_UTILS.parse_values(p_mv_list,',',l_mv,l_number_mv)=false then

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;

Line 2060: if BSC_IM_UTILS.parse_values(p_synonym_list,',',l_s_table,l_number_mv)=false then

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));

Line 2064: l_mv_owner(i):=BSC_IM_UTILS.get_table_owner(l_mv(i));

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

Line 2074: if BSC_IM_UTILS.drop_mv(l_mv(i),l_mv_owner(i))=false then

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;

Line 2077: if BSC_IM_UTILS.drop_synonym(l_mv(i))=false then

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;

Line 2080: if BSC_IM_UTILS.drop_table(l_s_table(i),l_mv_owner(i))=false then

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;

Line 2083: if BSC_IM_UTILS.drop_synonym(l_s_table(i))=false then

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;

Line 2089: BSC_IM_UTILS.g_status_message:=sqlerrm;

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;

Line 2109: p_options BSC_IM_UTILS.varchar_tabletype,

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);

Line 2116: l_s_tables BSC_IM_UTILS.varchar_tabletype;

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: -------------------------------------------------------------------------

Line 2118: l_fk BSC_IM_UTILS.varchar_tabletype;

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;

Line 2121: l_index 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;

Line 2122: l_uniqueness BSC_IM_UTILS.varchar_tabletype;

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;

Line 2123: l_tablespace 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;

Line 2124: l_initial_extent BSC_IM_UTILS.number_tabletype;

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;

Line 2125: l_next_extent BSC_IM_UTILS.number_tabletype;

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: ------

Line 2126: l_max_extents BSC_IM_UTILS.number_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;

Line 2127: l_pct_increase BSC_IM_UTILS.number_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;

Line 2130: l_ind_name BSC_IM_UTILS.varchar_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);

Line 2131: l_ind_col BSC_IM_UTILS.varchar_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);

Line 2163: l_owner:=bsc_im_utils.get_table_owner(p_object);

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;

Line 2169: l_index_tablespace:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'INDEX TABLESPACE');

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;

Line 2170: l_index_storage:=BSC_IM_UTILS.get_option_value(p_options,p_number_options,'INDEX STORAGE');

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;

Line 2196: if BSC_IM_UTILS.get_table_indexes(

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,

Line 2239: if BSC_IM_UTILS.is_parent_of_type_present(p_object,'VIEW') 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;

Line 2254: if BSC_IM_UTILS.create_index(l_stmt,null)=false 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';

Line 2277: p_options BSC_IM_UTILS.varchar_tabletype,

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

Line 2284: if BSC_IM_UTILS.get_option_value(p_options,p_number_options,'RECREATE')='Y' then

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

Line 2292: elsif BSC_IM_UTILS.get_option_value(p_options,p_number_options,'RESET MV LEVELS')='Y' then

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');

Line 2294: if BSC_IM_UTILS.check_mv(p_mv_name,null) 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';

Line 2303: if BSC_IM_UTILS.drop_view(p_mv_name,null)=false then

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

Line 2308: if BSC_IM_UTILS.check_view(p_mv_name,null) 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';

Line 2324: if BSC_IM_UTILS.check_mv(p_mv_name,null) then

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';

Line 2331: if BSC_IM_UTILS.check_view(p_mv_name,null) then

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';