[Home] [Help]
PACKAGE BODY: APPS.BSC_BIA_WRAPPER
Source
1 PACKAGE BODY BSC_BIA_WRAPPER AS
2 /* $Header: BSCBIAWB.pls 120.3 2006/04/19 11:46:08 meastmon noship $ */
3
4
5 /*===========================================================================+
6 | PROCEDURE Analyze_Table
7 +============================================================================*/
8 PROCEDURE Analyze_Table(
9 p_table_name IN VARCHAR2
10 ) IS
11 BEGIN
12 IF Do_Analyze THEN
13 -- Fix perf bug#4583017: pass cascade = false
14 FND_STATS.gather_table_stats(
15 OWNNAME => BSC_APPS.BSC_APPS_SCHEMA,
16 TABNAME => p_table_name,
17 CASCADE => FALSE);
18 END IF;
19 END Analyze_Table;
20
21
22 /*===========================================================================+
23 | FUNCTION Do_Analyze
24 +============================================================================*/
25 FUNCTION Do_Analyze RETURN BOOLEAN IS
26 BEGIN
27 RETURN TRUE;
28 END Do_Analyze;
29
30
31 /*===========================================================================+
32 | FUNCTION Drop_Rpt_Key_Table
33 +============================================================================*/
34 FUNCTION Drop_Rpt_Key_Table(
35 p_user_id NUMBER,
36 x_error_message OUT NOCOPY VARCHAR2
37 ) RETURN BOOLEAN IS
38
39 e_error EXCEPTION;
40 h_error_message VARCHAR2(4000) := NULL;
41
42 BEGIN
43
44 BSC_BSC_XTD_PKG.drop_rpt_key_table(
45 p_user_id => p_user_id,
46 p_error_message => h_error_message
47 );
48
49 IF h_error_message IS NOT NULL THEN
50 x_error_message := h_error_message;
51 RAISE e_error;
52 END IF;
53
54 RETURN TRUE;
55
56 EXCEPTION
57 WHEN e_error THEN
58 -- x_error_message should have the error
59 RETURN FALSE;
60
61 WHEN OTHERS THEN
62 x_error_message := SQLERRM;
63 RETURN FALSE;
64
65 END Drop_Rpt_Key_Table;
66
67
68 /*===========================================================================+
69 | PROCEDURE Drop_Rpt_Key_Table_VB
70 +============================================================================*/
71 PROCEDURE Drop_Rpt_Key_Table_VB(
72 p_user_id NUMBER
73 ) IS
74
75 e_error EXCEPTION;
76
77 l_error_message VARCHAR2(2000);
78
79 BEGIN
80
81 IF NOT Drop_Rpt_Key_Table(p_user_id, l_error_message) THEN
82 RAISE e_error;
83 END IF;
84
85 EXCEPTION
86 WHEN e_error THEN
87 BSC_MESSAGE.flush;
88 BSC_MESSAGE.Add(x_message => l_error_message,
89 x_source => 'BSC_BIA_WRAPPER.Drop_Rpt_Key_Table_VB',
90 x_mode => 'I');
91 COMMIT;
92
93 WHEN OTHERS THEN
94 BSC_MESSAGE.Add(x_message => SQLERRM,
95 x_source => 'BSC_BIA_WRAPPER.Drop_Rpt_Key_Table_VB',
96 x_mode => 'I');
97 COMMIT;
98
99 END Drop_Rpt_Key_Table_VB;
100
101
102 /*===========================================================================+
103 | FUNCTION Drop_Summary_MV
104 +============================================================================*/
105 FUNCTION Drop_Summary_MV(
106 p_mv IN VARCHAR2,
107 x_error_message OUT NOCOPY VARCHAR2
108 ) RETURN BOOLEAN IS
109
110 e_error EXCEPTION;
111
112 l_options VARCHAR2(32000) := null;
113 l_ret BOOLEAN;
114
115 BEGIN
116
117 IF (FND_PROFILE.VALUE('BIS_PMF_DEBUG') = 'Y') THEN
118 l_options := 'DEBUG LOG';
119 END IF;
120
121 l_ret := BSC_OLAP_MAIN.drop_summary_mv(
122 p_mv => p_mv,
123 p_option_string => l_options,
124 p_error_message => x_error_message
125 );
126
127 IF NOT l_ret THEN
128 RAISE e_error;
129 END IF;
130
131 RETURN TRUE;
132
133 EXCEPTION
134 WHEN e_error THEN
135 -- x_error_message should have the error
136 RETURN FALSE;
137
138 WHEN OTHERS THEN
139 x_error_message := SQLERRM;
140 RETURN FALSE;
141
142 END Drop_Summary_MV;
143
144
145 /*===========================================================================+
146 | PROCEDURE Drop_Summary_MV_VB
147 +============================================================================*/
148 PROCEDURE Drop_Summary_MV_VB(
149 p_mv IN VARCHAR2
150 ) IS
151
152 e_error EXCEPTION;
153
154 l_error_message VARCHAR2(2000);
155
156 BEGIN
157
158 IF NOT Drop_Summary_MV(p_mv, l_error_message) THEN
159 RAISE e_error;
160 END IF;
161
162 EXCEPTION
163 WHEN e_error THEN
164 BSC_MESSAGE.flush;
165 BSC_MESSAGE.Add(x_message => l_error_message,
166 x_source => 'BSC_BIA_WRAPPER.Drop_Summary_MV_VB',
167 x_mode => 'I');
168 COMMIT;
169
170 WHEN OTHERS THEN
171 BSC_MESSAGE.Add(x_message => SQLERRM,
172 x_source => 'BSC_BIA_WRAPPER.Drop_Summary_MV_VB',
173 x_mode => 'I');
174 COMMIT;
175
176 END Drop_Summary_MV_VB;
177
178
179 /*===========================================================================+
180 | FUNCTION Get_Sum_Table_MV_Name
181 +============================================================================*/
182 FUNCTION Get_Sum_Table_MV_Name(
183 p_table_name IN VARCHAR2
184 ) RETURN VARCHAR2 IS
185
186 h_mv_name VARCHAR2(100) := NULL;
187 h_pos NUMBER;
188
189 BEGIN
190
191 h_pos := INSTR(p_table_name, '_', -1);
192 IF h_pos > 0 THEN
193 h_mv_name := SUBSTR(p_table_name, 1, h_pos)||'MV';
194 ELSE
195 h_mv_name := p_table_name||'_MV';
196 END IF;
197
198 RETURN h_mv_name;
199
200 EXCEPTION
201 WHEN OTHERS THEN
202 RETURN NULL;
203 END Get_Sum_Table_MV_Name;
204
205
206 /*===========================================================================+
207 | FUNCTION Implement_Bsc_MV
208 +============================================================================*/
209 FUNCTION Implement_Bsc_MV(
210 p_kpi IN NUMBER,
211 p_adv_sum_level IN NUMBER,
212 p_reset_mv_levels IN BOOLEAN,
213 x_error_message OUT NOCOPY VARCHAR2
214 ) RETURN BOOLEAN IS
215
216 e_error EXCEPTION;
217
218 l_kpi VARCHAR2(30);
219 l_options VARCHAR2(32000);
220 l_ret BOOLEAN;
221
222 l_mv_levels VARCHAR2(10);
223
224 l_tablespace_param_tbl VARCHAR2(32000);
225 l_tablespace_param_idx VARCHAR2(32000);
226 l_storage_param VARCHAR2(32000);
227 l_storage_param_tbl VARCHAR2(32000);
228 l_storage_param_idx VARCHAR2(32000);
229
230 h_pos NUMBER;
231 l_db_version VARCHAR2(30);
232
233 BEGIN
234
235 l_kpi := TO_CHAR(p_kpi);
236
237 l_tablespace_param_tbl := 'TABLESPACE='||BSC_APPS.Get_Tablespace_Name(BSC_APPS.summary_table_tbs_type);
238 l_tablespace_param_idx := 'INDEX TABLESPACE='||BSC_APPS.Get_Tablespace_Name(BSC_APPS.summary_index_tbs_type);
239
240 l_storage_param := BSC_APPS.bsc_storage_clause;
241 -- Remove any other hint after ) like INITRANS
242 h_pos := INSTR(l_storage_param, ')');
243 IF h_pos > 0 THEN
244 l_storage_param := SUBSTR(l_storage_param, 1, h_pos);
245 END IF;
246
247 l_storage_param_tbl := 'STORAGE='||l_storage_param;
248 l_storage_param_idx := 'INDEX STORAGE='||l_storage_param;
249
250 l_db_version := BSC_IM_UTILS.get_db_version;
251
252
253 IF p_reset_mv_levels THEN
254 l_options := 'RESET MV LEVELS';
255 ELSE
256 l_options := 'RECREATE';
257 END IF;
258
259 IF (FND_PROFILE.VALUE('BIS_PMF_DEBUG') = 'Y') THEN
260 l_options := l_options||',DEBUG LOG';
261 END IF;
262
263 -- bug 3835059, to support any number of keys and not hang while creating the mv
264 l_options:= l_options||',NO ROLLUP='||MAX_ALLOWED_LEVELS;
265 l_options:= l_options||',MV LEVELS='||p_adv_sum_level||',SUMMARY VIEWS';
266 IF l_db_version = '8i' OR Indicator_Has_Projection(p_kpi) THEN
267 l_options := l_options||',FULL REFRESH';
268 END IF;
269 l_options := l_options||',OUTPUT=NO';
270 l_options := l_options||','||
271 l_tablespace_param_tbl||','||l_storage_param_tbl||','||
272 l_tablespace_param_idx||','||l_storage_param_idx;
273
274 l_ret := BSC_OLAP_MAIN.implement_bsc_mv(
275 p_kpi => l_kpi,
276 p_option_string => l_options,
277 p_error_message => x_error_message
278 );
279
280 IF NOT l_ret THEN
281 RAISE e_error;
282 END IF;
283
284 RETURN TRUE;
285
286 EXCEPTION
287 WHEN e_error THEN
288 -- x_error_message should have the error
289 RETURN FALSE;
290
291 WHEN OTHERS THEN
292 x_error_message := SQLERRM;
293 RETURN FALSE;
294
295 END Implement_Bsc_MV;
296
297
298 /*===========================================================================+
299 | FUNCTION Indicator_Has_Projection
300 +============================================================================*/
301 FUNCTION Indicator_Has_Projection(
302 p_kpi IN NUMBER
303 ) RETURN BOOLEAN IS
304
305 CURSOR c1 (p1 varchar2, p2 varchar2, p3 varchar2, p4 number, p5 number) IS
306 SELECT DISTINCT kt.indicator
307 FROM bsc_kpi_data_tables kt, bsc_db_tables_cols tc, bsc_db_measure_cols_vl m
308 WHERE kt.table_name = tc.table_name AND
309 tc.column_type = p1 AND
310 NVL(tc.source, p2) = p3 AND
311 tc.column_name = m.measure_col AND
312 NVL(m.projection_id, p4) <> p5;
313
314 BEGIN
315
316 -- Fix bug#5069433 Use bulk collect
317
318 -- SUPPORT_BSC_BIS_MEASURES: Only BSC measures exists in bsc_db_measure_cols_vl and
319 -- by design we assumed that BIS measures do not have projection.
320 -- I have added the condition on source in bsc_db_tables_cols
321
322 IF g_projection_kpis_set IS NULL OR g_projection_kpis_set = FALSE THEN
323 g_projection_kpis.delete;
324 OPEN c1('A','BSC','BSC',0,0);
325 LOOP
326 FETCH c1 BULK COLLECT INTO g_projection_kpis;
327 EXIT WHEN c1%NOTFOUND;
328 END LOOP;
329 CLOSE c1;
330 g_projection_kpis_set := TRUE;
331 END IF;
332
333 FOR i IN 1..g_projection_kpis.count LOOP
334 IF g_projection_kpis(i) = p_kpi THEN
335 RETURN TRUE;
336 END IF;
337 END LOOP;
338
339 RETURN FALSE;
340
341 END Indicator_Has_Projection;
342
343
344 /*===========================================================================+
345 | PROCEDURE Implement_Bsc_MV_VB
346 +============================================================================*/
347 PROCEDURE Implement_Bsc_MV_VB(
348 p_kpi IN NUMBER,
349 p_adv_sum_level IN NUMBER,
350 p_reset_mv_levels IN BOOLEAN
351 ) IS
352
353 e_error EXCEPTION;
354
355 l_error_message VARCHAR2(2000);
356
357 BEGIN
358
359 IF NOT Implement_Bsc_MV(p_kpi, p_adv_sum_level, p_reset_mv_levels, l_error_message) THEN
360 RAISE e_error;
361 END IF;
362
363 EXCEPTION
364 WHEN e_error THEN
365 BSC_MESSAGE.flush;
366 BSC_MESSAGE.Add(x_message => l_error_message||'. p_kpi='||p_kpi,
367 x_source => 'BSC_BIA_WRAPPER.Implement_Bsc_MV_VB',
368 x_mode => 'I');
369 COMMIT;
370
371 WHEN OTHERS THEN
372 BSC_MESSAGE.Add(x_message => SQLERRM||'. p_kpi='||p_kpi,
373 x_source => 'BSC_BIA_WRAPPER.Implement_Bsc_MV_VB',
374 x_mode => 'I');
375 COMMIT;
376
377 END Implement_Bsc_MV_VB;
378
379
380 /*===========================================================================+
381 | FUNCTION Load_Reporting_Calendar
382 +============================================================================*/
383 FUNCTION Load_Reporting_Calendar(
384 x_error_message OUT NOCOPY VARCHAR2
385 ) RETURN BOOLEAN IS
386
387 e_error EXCEPTION;
388 l_options VARCHAR2(32000);
389 l_ret BOOLEAN;
390
391 BEGIN
392
393 l_options := 'ANALYZE';
394
395 IF (FND_PROFILE.VALUE('BIS_PMF_DEBUG') = 'Y') THEN
396 l_options := l_options||',DEBUG LOG';
397 END IF;
398
399 l_ret := BSC_OLAP_MAIN.load_reporting_calendar(
400 p_apps => 'BSC',
401 p_option_string => l_options,
402 p_error_message => x_error_message
403 );
404
405 IF NOT l_ret THEN
406 RAISE e_error;
407 END IF;
408
409 RETURN TRUE;
410
411 EXCEPTION
412 WHEN e_error THEN
413 -- x_error_message should have the error
414 RETURN FALSE;
415
416 WHEN OTHERS THEN
417 x_error_message := SQLERRM;
418 RETURN FALSE;
419
420 END Load_Reporting_Calendar;
421
422
423 --Fix bug#4027813: Add this function to load reporting calendar for only
424 -- the specified calendar id
425 /*===========================================================================+
426 | FUNCTION Load_Reporting_Calendar
427 +============================================================================*/
428 FUNCTION Load_Reporting_Calendar(
429 x_calendar_id IN NUMBER,
430 x_error_message OUT NOCOPY VARCHAR2
431 ) RETURN BOOLEAN IS
432
433 e_error EXCEPTION;
434 l_options VARCHAR2(32000);
435 l_ret BOOLEAN;
436
437 BEGIN
438
439 l_options := 'ANALYZE';
440
441 IF (FND_PROFILE.VALUE('BIS_PMF_DEBUG') = 'Y') THEN
442 l_options := l_options||',DEBUG LOG';
443 END IF;
444
445 l_ret := BSC_OLAP_MAIN.load_reporting_calendar(
446 p_calendar_id => x_calendar_id,
447 p_apps => 'BSC',
448 p_option_string => l_options,
449 p_error_message => x_error_message
450 );
451
452 IF NOT l_ret THEN
453 RAISE e_error;
454 END IF;
455
456 RETURN TRUE;
457
458 EXCEPTION
459 WHEN e_error THEN
460 -- x_error_message should have the error
461 RETURN FALSE;
462
463 WHEN OTHERS THEN
464 x_error_message := SQLERRM;
465 RETURN FALSE;
466
467 END Load_Reporting_Calendar;
468
469 --LOCKING: new function
470 /*===========================================================================+
471 | FUNCTION Load_Reporting_Calendar_AT
472 +============================================================================*/
473 FUNCTION Load_Reporting_Calendar_AT(
474 x_calendar_id IN NUMBER,
475 x_error_message OUT NOCOPY VARCHAR2
476 ) RETURN BOOLEAN IS
477 PRAGMA AUTONOMOUS_TRANSACTION;
478 h_b BOOLEAN;
479 BEGIN
480 h_b := Load_Reporting_Calendar(x_calendar_id, x_error_message);
481 commit; -- all autonomous transaction needs to commit
482 RETURN h_b;
483 END Load_Reporting_Calendar_AT;
484
485
486 /*===========================================================================+
487 | PROCEDURE Load_Reporting_Calendar_VB
488 +============================================================================*/
489 PROCEDURE Load_Reporting_Calendar_VB IS
490
491 e_error EXCEPTION;
492
493 l_error_message VARCHAR2(2000);
494
495 BEGIN
496
497 IF NOT Load_Reporting_Calendar(l_error_message) THEN
498 RAISE e_error;
499 END IF;
500
501 EXCEPTION
502 WHEN e_error THEN
503 BSC_MESSAGE.flush;
504 BSC_MESSAGE.Add(x_message => l_error_message,
505 x_source => 'BSC_BIA_WRAPPER.Load_Reporting_Calendar_VB',
506 x_mode => 'I');
507 COMMIT;
508
509 WHEN OTHERS THEN
510 BSC_MESSAGE.Add(x_message => SQLERRM,
511 x_source => 'BSC_BIA_WRAPPER.Load_Reporting_Calendar_VB',
512 x_mode => 'I');
513 COMMIT;
514
515 END Load_Reporting_Calendar_VB;
516
517
518 /*===========================================================================+
519 | FUNCTION Refresh_Summary_MV
520 |
521 | Convered Dynamic SQL to Static Cursors -- Bug #3236356
522 +============================================================================*/
523 FUNCTION Refresh_Summary_MV(
524 p_mv IN VARCHAR2,
525 x_error_message OUT NOCOPY VARCHAR2
526 ) RETURN BOOLEAN IS
527
528 e_error EXCEPTION;
529
530 l_options VARCHAR2(32000);
531 l_ret BOOLEAN;
532 l_kpi NUMBER;
533
534
535 l_tablespace_param_idx VARCHAR2(32000);
536 l_storage_param VARCHAR2(32000);
537 l_storage_param_idx VARCHAR2(32000);
538 h_pos NUMBER;
539
540 -- Bug #3236356
541 CURSOR c_Kpi_For_MV IS
542 SELECT DISTINCT INDICATOR
543 FROM BSC_KPI_DATA_TABLES
544 WHERE MV_NAME = p_mv;
545
546 -- Bug #3236356
547 CURSOR c_Kpi_For_SB_MV IS
548 SELECT DISTINCT T.INDICATOR
549 FROM BSC_KPI_DATA_TABLES T, BSC_DB_TABLES_RELS R
550 WHERE T.TABLE_NAME = R.TABLE_NAME
551 AND R.RELATION_TYPE = 1
552 AND BSC_BIA_WRAPPER.Get_Sum_Table_MV_Name(SOURCE_TABLE_NAME) = p_mv;
553
554 BEGIN
555
556 -- Get the KPI using the given MV
557
558 -- Bug #3236356
559 OPEN c_Kpi_For_MV;
560 FETCH c_Kpi_For_MV INTO l_kpi;
561 IF c_Kpi_For_MV%NOTFOUND THEN
562 l_kpi := NULL;
563 END IF;
564 CLOSE c_Kpi_For_MV;
565
566 IF l_kpi IS NULL THEN
567 -- The MV is not used direclty by any Kpi, so it can be a SB MV
568 -- Bug #3236356
569 OPEN c_Kpi_For_SB_MV;
570 FETCH c_Kpi_For_SB_MV INTO l_kpi;
571 IF c_Kpi_For_SB_MV%NOTFOUND THEN
572 l_kpi := NULL;
573 END IF;
574 CLOSE c_Kpi_For_SB_MV;
575 END IF;
576
577 IF l_kpi IS NULL THEN
578 -- The MV does not have corresponding KPI. So we do not need to refresh that MV.
579 RETURN TRUE;
580 END IF;
581
582 l_tablespace_param_idx := 'INDEX TABLESPACE='||BSC_APPS.Get_Tablespace_Name(BSC_APPS.summary_index_tbs_type);
583
584 l_storage_param := BSC_APPS.bsc_storage_clause;
585 -- Remove any other hint after ) like INITRANS
586 h_pos := INSTR(l_storage_param, ')');
587 IF h_pos > 0 THEN
588 l_storage_param := SUBSTR(l_storage_param, 1, h_pos);
589 END IF;
590
591 l_storage_param_idx := 'INDEX STORAGE='||l_storage_param;
592
593 -- l_options := 'DEBUG LOG';
594 IF Do_Analyze THEN
595 l_options := 'ANALYZE,';
596 END IF;
597 l_options := l_options||'DROP INDEX,'||
598 l_tablespace_param_idx||','||l_storage_param_idx;
599
600 IF (FND_PROFILE.VALUE('BIS_PMF_DEBUG') = 'Y') THEN
601 l_options := l_options||',DEBUG LOG';
602 END IF;
603
604 l_ret := BSC_OLAP_MAIN.refresh_summary_mv(
605 p_mv => p_mv,
606 p_kpi => TO_CHAR(l_kpi),
607 p_option_string => l_options,
608 p_error_message => x_error_message
609 );
610
611 IF NOT l_ret THEN
612 RAISE e_error;
613 END IF;
614
615 RETURN TRUE;
616
617 EXCEPTION
618 WHEN e_error THEN
619 -- x_error_message should have the error
620 RETURN FALSE;
621
622 WHEN OTHERS THEN
623
624 x_error_message := SQLERRM;
625 RETURN FALSE;
626
627 END Refresh_Summary_MV;
628
629 --LOCKING: new function
630 /*===========================================================================+
631 | FUNCTION Refresh_Summary_MV_AT
632 +============================================================================*/
633 FUNCTION Refresh_Summary_MV_AT(
634 p_mv IN VARCHAR2,
635 x_error_message OUT NOCOPY VARCHAR2
636 ) RETURN BOOLEAN IS
637 PRAGMA AUTONOMOUS_TRANSACTION;
638 h_b BOOLEAN;
639 BEGIN
640 h_b := Refresh_Summary_MV(p_mv, x_error_message);
641 commit; --all autonomous transaction needs to commit
642 RETURN h_b;
643 END Refresh_Summary_MV_AT;
644
645
646 END BSC_BIA_WRAPPER;