1 package body BSC_OLAP_MAIN AS
2 /*$Header: BSCMAINB.pls 120.5 2006/10/30 23:24:55 amitgupt noship $*/
3
4 --Bug 3878968
5 --Procedure added to drop table storing CODE datatype
6 procedure drop_tmp_col_type_table is
7 l_stmt varchar2(200);
8 begin
9 --drop table
10 bsc_apps.init_bsc_apps;
11 b_table_col_type_created:=false;
12 l_stmt:= 'drop table '||g_col_type_table_name;
13 BSC_APPS.Do_DDL(l_stmt, ad_ddl.drop_table, g_col_type_table_name);
14
15 Exception when others then
16 null;
17 end;
18
19 procedure init_tbs_clause is
20 begin
21 g_summary_table_tbs_name := BSC_APPS.get_tablespace_name(BSC_APPS.summary_table_tbs_type);
22 g_summary_index_tbs_name := BSC_APPS.get_tablespace_name(BSC_APPS.summary_index_tbs_type);
23 if (g_summary_table_tbs_name is not null) then
24 g_summary_table_tbs_clause := ' TABLESPACE '|| g_summary_table_tbs_name;
25 end if;
26 if (g_summary_index_tbs_name is not null) then
27 g_summary_index_tbs_clause := ' TABLESPACE '|| g_summary_index_tbs_name;
28 end if;
29 end;
30
31 --Function added to create tmp table to store CODE datatype and index
32 --Bug 3878968
33 function create_tmp_col_type_table(
34 p_error_message out nocopy varchar2)
35 return boolean is
36 l_stmt varchar2(5000);
37 l_cur_run_kpis varchar2(2000);
38 l_counter number;
39 begin
40 if g_debug then
41 write_to_log_file_n('Start of create_tmp_col_type_table '||get_time);
42 end if;
43 drop_tmp_col_type_table ;
44 if (g_summary_table_tbs_name is null) then
45 init_tbs_clause;
46 end if;
47 -- bug 5458512
48 -- If we are running GDB in all objective mode then we will consider all the
49 -- indicators in the system while populating that temp table.
50 -- If we are running GDB in incremental/selective mode then we will consider
51 -- kpis only in prototype mode or being processed in current run.
52 --create table
53 l_stmt:= 'create table '||g_col_type_table_name||' (level_table_name varchar2(100), data_type varchar2(100))'|| g_summary_table_tbs_clause;
54 BSC_APPS.Do_DDL(l_stmt, ad_ddl.create_table, g_col_type_table_name);
55
56 if bsc_metadata_optimizer_pkg.gGAA_RUN_MODE=0 then
57 --if running in all objective mode then consider all indicators
58 l_stmt := ' insert into '||g_col_type_table_name||'(level_table_name, data_type)
59 select distinct dim.level_table_name,col.data_type
60 from
61 bsc_kpi_dim_levels_b dim,
62 all_tab_columns col
63 where
64 dim.level_table_name=col.table_name
65 and col.column_name=:1
66 and col.owner=:2
67 union all
68 select distinct dim.level_table_name,col.data_type
69 from
70 all_tab_columns col,
71 bsc_kpi_dim_levels_b dim
72 where
73 dim.level_table_name=col.table_name
74 and col.column_name=:3
75 and col.owner =:4';
76 else --if incremental or seletive then consider production mode indicators and
77 -- indictors in current mode
78 l_cur_run_kpis:=BSC_MO_HELPER_PKG.Get_New_Big_In_Cond_Number( 20, 'KPI.INDICATOR');
79 l_counter := BSC_METADATA_OPTIMIZER_PKG.gIndicators.first;
80 if BSC_METADATA_OPTIMIZER_PKG.gIndicators.count <> 0 then
81 loop
82 BSC_MO_HELPER_PKG.Add_Value_Big_In_Cond_Number(20, BSC_METADATA_OPTIMIZER_PKG.gindicators(l_counter).code);
83 EXIT WHEN l_counter=BSC_METADATA_OPTIMIZER_PKG.gIndicators.last;
84 l_counter := BSC_METADATA_OPTIMIZER_PKG.gIndicators.next(l_counter);
85 end loop;
86 end if;
87
88 l_stmt := 'insert into '||g_col_type_table_name||'(level_table_name, data_type)
89 select distinct dim.level_table_name,col.data_type
90 from
91 all_tab_columns col,
92 bsc_kpi_dim_levels_b dim,
93 bsc_kpis_vl kpi
94 where
95 (kpi.prototype_flag not in (1,2,3,4) OR '||
96 l_cur_run_kpis||
97 ') and kpi.indicator=dim.indicator
98 and dim.level_table_name=col.table_name
99 and col.column_name=:1
100 and col.owner =:2
101 union all
102 select distinct dim.level_table_name,col.data_type
103 from
104 all_tab_columns col,
105 bsc_kpi_dim_levels_b dim,
106 bsc_kpis_vl kpi
107 where
108 (kpi.prototype_flag not in (1,2,3,4) OR '||
109 l_cur_run_kpis||
110 ')and kpi.indicator=dim.indicator
111 and dim.level_table_name=col.table_name
112 and col.column_name=:3
113 and col.owner =:4';
114
115 END IF;
116 execute immediate l_stmt using 'CODE', bsc_apps.get_user_schema('BSC'), 'CODE', bsc_apps.get_user_schema('APPS') ;
117 --create index on table
118 l_stmt := 'create unique index '||g_col_type_table_name||'_u1 on '||g_col_type_table_name||'(level_table_name)'||g_summary_index_tbs_clause;
119 BSC_APPS.Do_DDL(l_stmt, ad_ddl.create_index, g_col_type_table_name||'_u1');
120 if g_debug then
121 write_to_log_file_n('End of create_tmp_col_type_table '||get_time);
122 end if;
123 return true;
124 Exception when others then
125 p_error_message:=sqlerrm;
126 write_to_log_file_n('Error in create_tmp_col_type_table '||sqlerrm);
127 return false;
128 end;
129
130 function implement_bsc_mv(
131 p_kpi varchar2,
132 p_option_string varchar2,
133 p_error_message out nocopy varchar2)return boolean is
134 Begin
135 open_file;
136 reset;
137 --Bug 3878968
138 --check if the temp table for col datatype has already been created
139 --if false drop the table and create it again
140 if(b_table_col_type_created=false)then
141 drop_tmp_col_type_table;
142 if(create_tmp_col_type_table(p_error_message)) then
143 b_table_col_type_created := true;
144 else
145 return false;
146 end if;
147 end if;
148
149 if BSC_IM_UTILS.parse_values(p_option_string,',',g_options,g_number_options)=false then
150 p_error_message:=BSC_IM_UTILS.g_status_message;
151 return false;
152 end if;
153 if init_all(g_options,g_number_options)=false then
154 p_error_message:=BSC_IM_UTILS.g_status_message;
155 return false;
156 end if;
157 if g_debug then
158 write_to_log_file_n('================================================');
159 write_to_log_file('Implement BSC MV '||p_kpi);
160 write_to_log_file_n('================================================');
161 end if;
162 if BSC_BSC_ADAPTER.load_metadata_for_indicators(p_kpi,g_options,g_number_options)=false then
163 p_error_message:=BSC_IM_UTILS.g_status_message;
164 return false;
165 end if;
166 if BSC_MV_ADAPTER.create_mv_kpi(p_kpi,'BSC',g_options,g_number_options)=false then
167 p_error_message:=BSC_IM_UTILS.g_status_message;
168 return false;
169 end if;
170 return true;
171 Exception when others then
172 BSC_IM_UTILS.g_status_message:=sqlerrm;
173 write_to_log_file_n('Error in implement_bsc_mv '||sqlerrm);
174 p_error_message:=BSC_IM_UTILS.g_status_message;
175 return false;
176 End;
177
178 ----------------------------------------------------------
179 function drop_bsc_mv(
180 p_kpi varchar2,
181 p_option_string varchar2,
182 p_error_message out nocopy varchar2)return boolean is
183 Begin
184 open_file;
185 reset;
186 if BSC_IM_UTILS.parse_values(p_option_string,',',g_options,g_number_options)=false then
187 p_error_message:=BSC_IM_UTILS.g_status_message;
188 return false;
189 end if;
190 if init_all(g_options,g_number_options)=false then
191 p_error_message:=BSC_IM_UTILS.g_status_message;
192 return false;
193 end if;
194 if BSC_BSC_ADAPTER.load_metadata_for_indicators(p_kpi,g_options,g_number_options)=false then
195 p_error_message:=BSC_IM_UTILS.g_status_message;
196 return false;
197 end if;
198 if BSC_MV_ADAPTER.drop_mv_kpi(p_kpi,'BSC',g_options,g_number_options)=false then
199 p_error_message:=BSC_MV_ADAPTER.g_status_message;
200 end if;
201 return true;
202 Exception when others then
203 BSC_IM_UTILS.g_status_message:=sqlerrm;
204 write_to_log_file_n('Error in drop_bsc_mv '||sqlerrm);
205 p_error_message:=BSC_IM_UTILS.g_status_message;
206 return false;
207 End;
208
209 function drop_summary_mv(
210 p_mv varchar2,
211 p_option_string varchar2,
212 p_error_message out nocopy varchar2)return boolean is
213 Begin
214 if BSC_IM_UTILS.parse_values(p_option_string,',',g_options,g_number_options)=false then
215 p_error_message:=BSC_IM_UTILS.g_status_message;
216 return false;
217 end if;
218 if BSC_MV_ADAPTER.drop_mv(p_mv,g_options,g_number_options)=false then
219 p_error_message:=BSC_MV_ADAPTER.g_status_message;
220 end if;
221 return true;
222 Exception when others then
223 BSC_IM_UTILS.g_status_message:=sqlerrm;
224 write_to_log_file_n('Error in drop_bsc_mv '||sqlerrm);
225 p_error_message:=BSC_IM_UTILS.g_status_message;
226 return false;
227 End;
228 ----------------------------------------------------------
229
230 ----------------------------------------------------------
231 function refresh_bsc_mv(
232 p_kpi varchar2,
233 p_option_string varchar2,
234 p_error_message out nocopy varchar2)return boolean is
235 Begin
236 open_file;
237 reset;
238 if BSC_IM_UTILS.parse_values(p_option_string,',',g_options,g_number_options)=false then
239 p_error_message:=BSC_IM_UTILS.g_status_message;
240 return false;
241 end if;
242 if init_all(g_options,g_number_options)=false then
243 p_error_message:=BSC_IM_UTILS.g_status_message;
244 return false;
245 end if;
246 if g_debug then
247 write_to_log_file_n('================================================');
248 write_to_log_file('Refresh BSC MV '||p_kpi);
249 write_to_log_file_n('================================================');
250 end if;
251 if BSC_BSC_ADAPTER.load_metadata_for_indicators(p_kpi,g_options,g_number_options)=false then
252 p_error_message:=BSC_IM_UTILS.g_status_message;
253 return false;
254 end if;
255 if BSC_MV_ADAPTER.refresh_mv_kpi(p_kpi,'BSC',g_options,g_number_options)=false then
256 p_error_message:=BSC_MV_ADAPTER.g_status_message;
257 return false;
258 end if;
259 return true;
260 Exception when others then
261 BSC_IM_UTILS.g_status_message:=sqlerrm;
262 write_to_log_file_n('Error in refresh_bsc_mv '||sqlerrm);
263 p_error_message:=BSC_IM_UTILS.g_status_message;
264 return false;
265 End;
266
267 function refresh_summary_mv(
268 p_mv varchar2,
269 p_kpi varchar2,
270 p_option_string varchar2,
271 p_error_message out nocopy varchar2)return boolean is
272 Begin
273 open_file;
274 if BSC_IM_UTILS.parse_values(p_option_string,',',g_options,g_number_options)=false then
275 p_error_message:=BSC_IM_UTILS.g_status_message;
276 return false;
277 end if;
278 if init_all(g_options,g_number_options)=false then
279 p_error_message:=BSC_IM_UTILS.g_status_message;
280 return false;
281 end if;
282 if g_debug then
283 write_to_log_file_n('================================================');
284 write_to_log_file('Refresh Summary MV '||p_mv||' '||p_kpi);
285 write_to_log_file_n('================================================');
286 end if;
287 -- Bug#3899842: Performance fix: Comment out these lines.
288 --Instead call BSC_BSC_ADAPTER.create_int_md_fk(p_mv)
289 /*
290 if BSC_IM_UTILS.is_cube_present(p_kpi,'BSC')=false then
291 --see if any cube is present.
292 if BSC_IM_UTILS.is_cube_present(null,'BSC') then
293 reset;
294 end if;
295 --populate the int metadata
296 if BSC_BSC_ADAPTER.load_metadata_for_indicators(p_kpi,g_options,g_number_options)=false then
297 p_error_message:=BSC_IM_UTILS.g_status_message;
298 return false;
299 end if;
300 end if;
301 */
302 BSC_BSC_ADAPTER.create_int_md_fk(p_mv);
303 if BSC_MV_ADAPTER.refresh_mv(p_mv,p_kpi,g_options,g_number_options)=false then
304 p_error_message:=BSC_MV_ADAPTER.g_status_message;
305 return false;
306 end if;
307 return true;
308 Exception when others then
309 BSC_IM_UTILS.g_status_message:=sqlerrm;
310 write_to_log_file_n('Error in refresh_bsc_mv '||sqlerrm);
311 p_error_message:=BSC_IM_UTILS.g_status_message;
312 return false;
313 End;
314 ------------------------------------------
315 --------=====================================
316 --------- Load Reporting Calendar
317 function load_reporting_calendar(
318 p_apps varchar2,
319 p_option_string varchar2,
320 p_error_message out nocopy varchar2)return boolean is
321 Begin
322 open_file;
323 reset;
324 if BSC_IM_UTILS.parse_values(p_option_string,',',g_options,g_number_options)=false then
325 p_error_message:=BSC_IM_UTILS.g_status_message;
326 return false;
327 end if;
328 if init_all(g_options,g_number_options)=false then
329 p_error_message:=BSC_IM_UTILS.g_status_message;
330 return false;
331 end if;
332 if g_debug then
333 write_to_log_file_n('================================================');
334 write_to_log_file('Load Reporting Calendar, Apps='||p_apps);
335 write_to_log_file_n('================================================');
336 end if;
337 if p_apps='BSC' then
338 if BSC_BSC_ADAPTER.load_reporting_calendar(g_options,g_number_options)=false then
339 p_error_message:=BSC_IM_UTILS.g_status_message;
340 return false;
341 end if;
342 end if;
343 return true;
344 Exception when others then
345 BSC_IM_UTILS.g_status_message:=sqlerrm;
346 write_to_log_file_n('Error in load_reporting_calendar '||sqlerrm);
347 p_error_message:=BSC_IM_UTILS.g_status_message;
348 return false;
349 End;
350
351 --Fix bug#4027813: Added this function to load reporting calendar for only
352 --the specified calendar id
353 function load_reporting_calendar(
354 p_calendar_id number,
355 p_apps varchar2,
356 p_option_string varchar2,
357 p_error_message out nocopy varchar2)return boolean is
358 Begin
359 open_file;
360 reset;
361 if BSC_IM_UTILS.parse_values(p_option_string,',',g_options,g_number_options)=false then
362 p_error_message:=BSC_IM_UTILS.g_status_message;
363 return false;
364 end if;
365 if init_all(g_options,g_number_options)=false then
366 p_error_message:=BSC_IM_UTILS.g_status_message;
367 return false;
368 end if;
369 if g_debug then
370 write_to_log_file_n('================================================');
371 write_to_log_file('Load Reporting Calendar, calendar id='||p_calendar_id||' Apps='||p_apps);
372 write_to_log_file_n('================================================');
373 end if;
374 if p_apps='BSC' then
375 if BSC_BSC_ADAPTER.load_reporting_calendar(p_calendar_id, g_options,g_number_options)=false then
376 p_error_message:=BSC_IM_UTILS.g_status_message;
377 return false;
378 end if;
379 end if;
380 return true;
381 Exception when others then
382 BSC_IM_UTILS.g_status_message:=sqlerrm;
383 write_to_log_file_n('Error in load_reporting_calendar '||sqlerrm);
384 p_error_message:=BSC_IM_UTILS.g_status_message;
385 return false;
386 End;
387 --------=====================================
388
389 ------------------------------------------
390 --------=====================================
391 --------- Support for PMV for Recursive Dimensions
392 procedure get_list_of_rec_dim(
393 p_dim_list out nocopy bsc_varchar2_table_type,
394 p_num_dim_list out nocopy number,
395 p_error_message out nocopy varchar2) is
396 Begin
397 bsc_bsc_adapter.get_list_of_rec_dim(p_dim_list,p_num_dim_list,p_error_message);
398 Exception when others then
399 BSC_IM_UTILS.g_status_message:=sqlerrm;
400 p_error_message:=BSC_IM_UTILS.g_status_message;
401 raise;
402 End;
403
404 procedure set_and_get_dim_sql(
405 p_dim_level_short_name bsc_varchar2_table_type,
406 p_dim_level_value bsc_varchar2_table_type,
407 p_num_dim_level number,
408 p_dim_level_sql out nocopy bsc_varchar2_table_type,
409 p_error_message out nocopy varchar2
410 ) is
411 Begin
412 bsc_bsc_adapter.set_and_get_dim_sql(p_dim_level_short_name,p_dim_level_value,p_num_dim_level,
413 p_dim_level_sql,p_error_message);
414 Exception when others then
415 BSC_IM_UTILS.g_status_message:=sqlerrm;
416 p_error_message:=BSC_IM_UTILS.g_status_message;
417 raise;
418 End;
419
420 --------=====================================
421 --reset
422 procedure reset is
423 Begin
424 write_to_log_file_n('Intermediate metadata reset');
425 BSC_IM_INT_MD.reset_int_metadata;
426 Exception when others then
427 BSC_IM_UTILS.g_status_message:=sqlerrm;
428 write_to_log_file_n('Error in reset '||sqlerrm);
429 raise;
430 End;
431
432 procedure open_file is
433 Begin
434 BSC_IM_UTILS.open_file('TEST');
435 Exception when others then
436 BSC_IM_UTILS.g_status_message:=sqlerrm;
437 write_to_log_file_n('Error in open_file '||sqlerrm);
438 raise;
439 End;
440
441 ------------------------------------------
442 function init_all(
443 p_options BSC_IM_UTILS.varchar_tabletype,
444 p_number_options number) return boolean is
445 Begin
446 g_init_all:='set';
447 g_status:=true;
448 g_debug:=false;
449 if BSC_IM_UTILS.get_option_value(p_options,p_number_options,'DEBUG LOG')='Y' then
450 g_debug:=true;
451 end if;
452 if BSC_IM_UTILS.get_option_value(p_options,p_number_options,'TRACE')='Y' then
453 BSC_IM_UTILS.set_trace;
454 end if;
455 BSC_IM_UTILS.set_globals(g_debug);
456 if BSC_IM_UTILS.set_global_dimensions=false then
457 return false;
458 end if;
459 BSC_BSC_ADAPTER.set_globals(g_debug);
460 BSC_IM_INT_MD.set_globals(g_debug);
461 BSC_MV_ADAPTER.set_globals(g_debug);
462 return true;
463 Exception when others then
464 BSC_IM_UTILS.g_status_message:=sqlerrm;
465 write_to_log_file_n('Error in init_all '||sqlerrm);
466 return false;
467 End;
468
469 procedure write_to_log_file(p_message varchar2) is
470 Begin
471 BSC_IM_UTILS.write_to_log_file(p_message);
472 Exception when others then
473 BSC_IM_UTILS.g_status_message:=sqlerrm;
474 null;
475 End;
476
477 procedure write_to_log_file_n(p_message varchar2) is
478 begin
479 write_to_log_file(' ');
480 write_to_log_file(p_message);
481 Exception when others then
482 BSC_IM_UTILS.g_status_message:=sqlerrm;
483 null;
484 end;
485
486 procedure write_to_debug_n(p_message varchar2) is
487 begin
488 if g_debug then
489 write_to_log_file_n(p_message);
490 end if;
491 Exception when others then
492 BSC_IM_UTILS.g_status_message:=sqlerrm;
493 null;
494 end;
495
496 procedure write_to_debug(p_message varchar2) is
497 begin
498 if g_debug then
499 write_to_log_file(p_message);
500 end if;
501 Exception when others then
502 BSC_IM_UTILS.g_status_message:=sqlerrm;
503 null;
504 end;
505
506 function get_time return varchar2 is
507 begin
508 return BSC_IM_UTILS.get_time;
509 Exception when others then
510 BSC_IM_UTILS.g_status_message:=sqlerrm;
511 null;
512 End;
513
514 END BSC_OLAP_MAIN;
[Home] [Help]