[Home] [Help]
PACKAGE BODY: APPS.BSC_UTILITY
Source
1 PACKAGE BODY BSC_UTILITY AS
2 /*$Header: BSCUTILB.pls 120.31 2007/10/04 14:39:13 sirukull ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1995 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 | History: |
8 | Modified Date Modified By Description |
9 | 31-JUL-2003 mahrao Increased the size of l_dim_level var. |
10 | in get_kpi_dim_levels procedure for |
11 | bug# 3030788 |
12 | 20-Aug-03 Adeulgao fixed bug#3008243 eliminated hard coding |
13 | of schema name |
14 | 12-NOV-03 Bug #3232366 |
15 | 27-FEB-2004 adeulgao fixed bug#3431750 |
16 | 20-APR-2004 ADRAO Added API is_Indicator_In_Production for KPI end-to-end|
17 | 16-JUN-2004 ADRAO added API Is_BSC_Licensed() for Bug#3764205 |
18 | 09-AUG-2004 sawu Added API Get_Default_Internal_Name for bug#3819855 |
19 | 18-AUG-2004 ADRAO Fixed Bug#3831815 |
20 | 01-OCT-2004 ashankar Fixed Bug#3908204 |
21 | 31-MAR-2005 ADRAO added API is_Mix_Dim_Objects_Allowed |
22 | 08-APR-2005 kyadamak Added function get_valid_bsc_master_tbl_name() for |
23 | bug# 4290359 |
24 | 30-JUN-2005 ppandey added Dimension entity validation APIs |
25 | 01-SEP-2005 adrao Added API Get_Responsibility_Key for Bug#4563456 |
26 | 25-AUG-2005 ppandey added validation for Weighted Report generated |
27 | Bug #4570320 Dim and Dim Group used in Report |
28 | 13-Sep-2005 sawu Bug#4602231: Broken is_internal_dim into component apis|
29 | 05-Oct-2005 ashankar Bug# Added the method Get_User_Time |
30 | 10-Oct-2005 akoduri Bug#4646118 Recognize 120 as seed user for R12 |
31 | 25-OCT-2005 kyadamak Removed literals for Enhancement#4618419 |
32 | 02-Jan-2006 akoduri Bug#4611303 - Support For Enable/Disable All |
33 | In Report Designer |
34 | 05-JAN-06 ppandey Enh#4860106 - Defined Is_More as a public function |
35 | 06-Jan-2006 akoduri Enh#4739401 - Hide Dimensions/Dim Objects |
36 | 13-JAN-06 adrao |
37 | The following APIs have been added as a part of the Enhancement#3909868 |
38 | |
39 | Validate_Plsql_For_Report |
40 | -- actual api to validation the pl/sql for the report |
41 | Get_Plsql_Parameters |
42 | -- gets the pl/sql report for the passed pl/sql procedure |
43 | Remove_Repeating_Comma |
44 | -- Removes and parses repeating comma's |
45 | Validate_PLSQL |
46 | -- Validates existentially the pl/sql package and performes some |
47 | validation apis |
48 | Obtain_Report_Query |
49 | -- Does the job of actually getting the Report query |
50 | Insert_Into_Query_Table |
51 | -- An API to insert into the PL/SQL table. |
52 | Do_DDL_AT |
53 | -- Autonously call DDL statements, used in our case for creating |
54 | and dropping views |
55 | Validate_Sql_String |
56 | -- Validate's if a SQL string is ok by creating a view |
57 | Sort_String |
58 | -- sorts a comma separated string values |
59 | |
60 | 17-JAN-2005 adrao modified Validate_Plsql for Bug#4957841 |
61 | 24-JAN-2006 ankgoel Bug#4954663 Show Info text for AG to PL/SQL or VB conversion|
62 | 15-FEB-2006 adrao Bug#5034549; Added ABS() to DBMS_UTILITY.GET_TIME, |
63 | since it can return a negative value |
64 | 29-MAR-2006 visuri Enh#5125893 Direct Creation of Pl/Sql reports |
65 | 19-JUN-2006 adrao Added util API Create_Unique_Comma_List & |
66 | Get_Unique_List for Bug#5300060 |
67 | 09-feb-2007 ashankar Simulation Tree Enhacement 5386112 |
68 | 21-MAR-2007 akoduri Copy Indicator Enh#5943238 |
69 | 02-JUl-2007 lbodired Bug#6152009;To make use get_nof_independent_dimobj |
70 | function for 'PMF' dimension objects also |
71 | 04-OCT-2007 sirukull Bug#6406844. Comparing Leapyear daily periodicity |
72 | data with non-leapyear data. |
73 +===========================================================================*/
74 /*----------------------------------------------------------------------------
75 FILE NAME
76
77 BSCUTILB.pls
78
79 PACKAGE NAME
80
81 bsc_utility
82
83 DESCRIPTION
84 This package includes all OBSC public utility functions/procedures.
85
86 PUBLIC PROCEDURES
87 enable_debug
88 enable_debug()
89 disable_debug
90 debug()
91 print_fcn_label()
92 print_fcn_label2()
93 close_cursor()
94 update_edw_flag()
95
96 PRIVATE PROCEDURES/FUNCTIONS
97 exec_dynamic_sql()
98 create_synonym_for_edw_time_m()
99
100 PRIVATE PROCEDURES/FUNCTIONS
101
102 EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
103
104 HISTORY
105 15-JAN-1999 Srinivasan Jandyala Created
106 22-JAN-1999 Alex Yang Added Do_SQL() procedure.
107 29-MAR-2001 Srini Added PUBLIC PROCEDURE update_edw_flag(),
108 PRIVATE PROCEDURE
109 create_synonym_for_edw_time_m().
110 27-Apr-2001 Srini Added PUBLIC FUNCTION is_edw_installed().
111 21-DEC-2001 Mario-Jair Campos Added procedures: get_dataset_id
112 get_kpi_dim_levels
113 27-DEC-2001 Srini Added function:get_kpi_dim_level_short_names
114 23-APR-2003 mdamle Added the Add_To_Fnd_Msg_Stack
115 06-AUG-2003 mdamle Added token-value to add_to_fnd_msg_stack
116 08-FEB-2006 akoduri Bug#4956836 Updating dim object cache should
117 invalidate AK Cache also
118 ----------------------------------------------------------------------------*/
119
120 -----------------------------------------------------------------------------
121 -- Private Variables
122 -----------------------------------------------------------------------------
123 debug_flag boolean := false;
124
125 --This is for caching User schema names
126 TYPE user_schema_table IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(30);
127 user_schema_tbl user_schema_table;
128
129 g_apps_user_schema CONSTANT VARCHAR2(100) := 'APPS';
130
131 FUNCTION is_attached_to_objective (
132 p_dataset_id IN NUMBER
133 , p_region_code IN VARCHAR2
134 ) RETURN VARCHAR2;
135
136 FUNCTION is_formula_measure (
137 p_dataset_id IN NUMBER
138 ) RETURN VARCHAR2;
139
140
141 -----------------------------------------------------------------------------
142 -- Debugging functions
143 -----------------------------------------------------------------------------
144 PROCEDURE enable_debug IS
145 BEGIN
146
147 debug_flag := true;
148 --dbms_output.enable;
149
150 END;
151
152 PROCEDURE enable_debug( buffer_size NUMBER ) IS
153 BEGIN
154
155 debug_flag := true;
156 --dbms_output.enable( buffer_size );
157
158 END;
159
160 PROCEDURE disable_debug IS
161 BEGIN
162
163 debug_flag := false;
164
165 END;
166
167 PROCEDURE print_debug( line IN VARCHAR2 ) IS
168
169 rest varchar2(32767);
170 buffer_overflow exception;
171 pragma exception_init(buffer_overflow, -20000);
172
173 BEGIN
174
175 IF debug_flag THEN
176
177 rest := line;
178
179 LOOP
180
181 IF (rest IS NULL) THEN
182 exit;
183 ELSE
184 --dbms_output.put_line(substrb(rest, 1, 255));
185 rest := substrb(rest, 256);
186 END IF;
187
188 END LOOP;
189
190 END IF;
191
192 EXCEPTION
193 WHEN buffer_overflow THEN
194 NULL; -- buffer overflow, ignore
195 WHEN OTHERS THEN
196 RAISE;
197
198 END print_debug;
199
200 PROCEDURE print_debug( str VARCHAR2, print_level NUMBER ) IS
201 BEGIN
202
203 IF( bsc_utility.msg_level >= print_level ) THEN
204 print_debug( str );
205 END IF;
206
207 END print_debug;
208
209
210 Procedure Debug(
211 x_calling_fn IN Varchar2,
212 x_debug_msg IN Varchar2 := NULL,
213 x_mode IN Varchar2 := 'N'
214 ) Is
215 l_debug_msg Varchar2(2000);
216 Begin
217 IF debug_flag THEN
218 l_debug_msg := x_calling_fn || ': ' || x_debug_msg;
219
220 BSC_MESSAGE.add(x_message => l_debug_msg,
221 x_source => x_calling_fn,
222 x_type => 4,
223 x_mode => x_mode
224 );
225 END IF;
226 End Debug;
227
228
229 PROCEDURE print_fcn_label( p_label VARCHAR2 ) IS
230 BEGIN
231
232 print_debug( p_label || ' ' || to_char(sysdate, 'DD-MON-YY HH:MI:SS'),
233 bsc_utility.MSG_LEVEL_TIMING );
234
235 END print_fcn_label;
236
237 PROCEDURE print_fcn_label2( p_label VARCHAR2 ) IS
238 BEGIN
239
240 print_debug( p_label || ' ' || to_char(sysdate, 'DD-MON-YY HH:MI:SS'),
241 bsc_utility.MSG_LEVEL_DEBUG );
242
243 END print_fcn_label2;
244
245 -----------------------------------------------------------------------------
246 -- Database utilities
247 -----------------------------------------------------------------------------
248 FUNCTION exec_dynamic_sql(x_sql_stmt IN VARCHAR2)
249
250 RETURN NUMBER IS
251
252 h_handle NUMBER;
253 h_ignore NUMBER;
254
255 BEGIN
256
257 bsc_utility.print_debug('SQL stmt: '||x_sql_stmt);
258
259 h_handle := dbms_sql.open_cursor;
260 dbms_sql.parse(h_handle, x_sql_stmt, dbms_sql.native);
261 h_ignore := dbms_sql.execute(h_handle);
262
263 RETURN(h_handle);
264
265 END exec_dynamic_sql;
266
267
268 PROCEDURE close_cursor( p_cursor_handle IN OUT NOCOPY NUMBER ) IS
269 BEGIN
270
271 IF( dbms_sql.is_open( p_cursor_handle ) ) THEN
272
273 dbms_sql.close_cursor( p_cursor_handle );
274
275 END IF;
276
277 EXCEPTION
278 WHEN OTHERS THEN
279 BSC_MESSAGE.add(
280 x_message => sqlerrm,
281 x_source => 'BSC_UTILITY.Close_Cursor'
282 );
283
284 print_debug('ERROR: bsc_utility.close_cursor()',
285 bsc_utility.MSG_LEVEL_BASIC);
286 RAISE;
287 END close_cursor;
288
289
290
291
292 Procedure Do_SQL(
293 x_sql_stmt IN Varchar2,
294 x_calling_fn IN Varchar2
295 ) Is
296 h_handle NUMBER;
297 h_ignore NUMBER;
298 Begin
299
300 h_handle := dbms_sql.open_cursor;
301
302 dbms_sql.parse(h_handle, x_sql_stmt, dbms_sql.native);
303
304 h_ignore := dbms_sql.execute(h_handle);
305
306 dbms_sql.close_cursor(h_handle);
307
308 Exception
309 When Others Then
310
311 BSC_MESSAGE.add(x_message => sqlerrm,
312 x_source => x_calling_fn,
313 x_type => 0,
314 x_mode => 'I'
315 );
316
317 BSC_UTILITY.debug(x_calling_fn => 'BSC_UTILITY.DO_SQL',
318 x_debug_msg => x_sql_stmt,
319 x_mode => 'I'
320 );
321
322 if (dbms_sql.is_open(h_handle)) then
323 dbms_sql.close_cursor(h_handle);
324 end if;
325
326 End Do_SQL;
327
328
329 Procedure Do_Rollback Is
330 Begin
331
332 -- Rollback uncommitted transactions.
333 rollback;
334
335 -- Insert error messages into bsc_message_logs table.
336 bsc_message.flush;
337
338 -- commit rows in bsc_message_logs table.
339 -- commit work;
340
341 End Do_Rollback;
342
343 -----------------------------------------------------------------------------
344 -- Private Procedure: create_synonym_for_edw_time_m (for BSC v5.0)
345 -----------------------------------------------------------------------------
346
347 -- Purpose: To create synonym for APPS.edw_time_m object. This is required to
348 -- be created seperately since EDW time is not mapped as a regular
349 -- dimension. This means DIM DDL generator will not create it.
350 -- We create it only when EDW is enabled by user.
351 --
352 -- This procedure is called by update_edw_flag() precedure.
353 --
354 -- Arguments
355 --
356 -- h_call_proc_name: Calling Function/Procedure name.
357 -- h_mode: ENABLE/DISABLE mode.
358 --
359 -----------------------------------------------------------------------------
360
361 PROCEDURE create_synonym_for_edw_time_m(h_call_proc_name IN VARCHAR2) IS
362
363 l_call_proc VARCHAR2(1024) := NULL;
364 l_object_name VARCHAR2(30) := NULL;
365 l_sql_stmt VARCHAR2(100);
366 l_Bsc_Temp VARCHAR2(3);
367
368 BEGIN
369
370 l_call_proc := RTRIM(LTRIM(h_call_proc_name));
371
372 -- to avoid GSCC Fail - File.Sql.6 Hard-coded Schema name 'BSC'.
373 l_Bsc_Temp := 'BSC';
374 l_sql_stmt := 'CREATE SYNONYM '|| l_Bsc_Temp||'.edw_time_m FOR edw_time_m';
375
376 --dbms_output.put_line(l_sql_stmt);
377
378 BEGIN -- Check for edw_time_m
379
380 SELECT
381 synonym_name
382 INTO
383 l_object_name
384 FROM
385 ALL_SYNONYMS
386 WHERE
387 TABLE_NAME = 'EDW_TIME_M'
388 AND owner = BSC_APPS.get_user_schema;
389
390 EXCEPTION
391 WHEN NO_DATA_FOUND THEN
392 l_object_name := NULL;
393 --dbms_output.put_line('EDW_TIME_M does not exist');
394
395 END; -- Check for edw_time_m
396
397 -- We will create synonym only if doesn't exist. Otherwise, you will get
398 -- ORA error: Object already exists.
399
400 IF (l_object_name IS NULL) THEN
401
402 BEGIN
403
404 -- Create synonym.
405
406 EXECUTE IMMEDIATE l_sql_stmt;
407 --dbms_output.put_line('EDW_TIME_M synonym created');
408
409 EXCEPTION
410 WHEN OTHERS THEN
411 bsc_message.add(
412 x_message => 'create_synonym_for_edw_time_m: '||SQLERRM,
413 x_source => l_call_proc,
414 x_mode => 'I' );
415
416 RAISE;
417 END;
418
419 END IF;
420
421 END create_synonym_for_edw_time_m;
422
423 -----------------------------------------------------------------------------
424 -- Function: is_edw_installed (for BSC v5.0)
425 -----------------------------------------------------------------------------
426
427 -- Purpose: To ENABLE/DISABLE menu item 'EDW' in Builder. This function will
428 -- check if EDW and BSC patch are installed. If they are, then
429 -- Builder will show menu item 'EDW' enabled, otherwise, disabled.
430 --
431 -- This function is called by BUILDER.
432 --
433 -- Arguments
434 --
435 -- h_call_proc_name: Calling Function/Procedure name.
436 --
437 -- Return code
438 --
439 -- 1 = EDW installed
440 -- 0 = EDW not installed
441 --
442 -----------------------------------------------------------------------------
443
444 FUNCTION is_edw_installed(h_call_proc_name IN VARCHAR2)
445 RETURN NUMBER IS
446
447 -- Objects to check for.
448
449 l_edw_obj_name VARCHAR2(30) := 'EDW_DIMENSIONS_MD_V';
450 l_bsc_obj_name VARCHAR2(30) := 'BSC_INTEGRATION_MV_GEN';
451
452 -- Local variables
453
454 l_count NUMBER := 0;
455 l_message VARCHAR2(1024) := NULL;
456 l_call_proc VARCHAR2(1024) := NULL;
457 l_object_name VARCHAR2(30) := NULL;
458
459 l_sql_stmt VARCHAR2(512);
460
461 l_edw_dimensions_md_v_exist BOOLEAN := FALSE;
462 l_dimensions_exist BOOLEAN := FALSE;
463 l_bsc_integration_mv_gen_exist BOOLEAN := FALSE;
464
465 BEGIN
466
467 l_call_proc := RTRIM(LTRIM(h_call_proc_name));
468
469 -- Check for the existance of EDW metadata view 'EDW_DIMENSIONS_MD_V'.
470 -- (To determine if EDW is properly installed for use by BSC).
471
472 BEGIN -- Check EDW object_name
473
474 SELECT
475 object_name
476 INTO
477 l_object_name
478 FROM
479 user_objects
480 WHERE
481 object_name = l_edw_obj_name
482 AND object_type IN ('VIEW', 'SYNONYM');
483
484 -- Returned row, i.e., EDW_DIMENSIONS_MD_V view exists.
485
486 l_edw_dimensions_md_v_exist := TRUE;
487
488 EXCEPTION
489 WHEN NO_DATA_FOUND THEN
490
491 l_edw_dimensions_md_v_exist := FALSE;
492 RETURN (0);
493
494 WHEN OTHERS THEN
495 l_message := 'is_edw_installed.SQL1: '||SQLERRM;
496 RAISE;
497
498 END; -- Check EDW object_name
499
500 -- Check if EDW metadata import was done successfully.
501 -- This can be verified if any dimensions exist in edw_dimensions_md_v
502 -- view.
503
504 l_sql_stmt := 'SELECT dim_id FROM edw_dimensions_md_v WHERE ROWNUM < 2';
505
506 BEGIN -- Do dimensions exist ?
507
508 EXECUTE IMMEDIATE l_sql_stmt INTO l_count;
509 --dbms_output.put_line('l_count: '||l_count);
510
511 l_dimensions_exist := TRUE;
512
513 EXCEPTION
514 WHEN NO_DATA_FOUND THEN
515
516 l_dimensions_exist := FALSE;
517 RETURN (0);
518
519 WHEN OTHERS THEN
520 l_message := 'is_edw_installed.SQL2: '||SQLERRM;
521 RAISE;
522
523 END; -- Do dimensions exist ?
524
525 -- If we are here, it means that EDW is installed and metadata import
526 -- was done successfully.
527 -- Check if BSC-EDW Integration package(s) is installed.
528
529 BEGIN -- Check BSC object_name
530
531 SELECT
532 object_name
533 INTO
534 l_object_name
535 FROM
536 user_objects
537 WHERE
538 object_name = l_bsc_obj_name
539 AND object_type = 'PACKAGE BODY';
540
541 l_bsc_integration_mv_gen_exist := TRUE;
542
543 EXCEPTION
544 WHEN NO_DATA_FOUND THEN
545
546 l_bsc_integration_mv_gen_exist := FALSE;
547 RETURN (0);
548
549 WHEN OTHERS THEN
550 l_message := 'is_edw_installed.SQL3: '||SQLERRM;
551 RAISE;
552
553 END; -- Check BSC object_name
554
555 IF ( l_edw_dimensions_md_v_exist AND
556 l_dimensions_exist AND
557 l_bsc_integration_mv_gen_exist ) THEN
558
559 RETURN(1);
560 ELSE
561 RETURN(0);
562 END IF;
563
564 EXCEPTION
565 WHEN OTHERS THEN
566 bsc_message.add(
567 x_message => l_message,
568 x_source => l_call_proc,
569 x_type => 1,
570 x_mode => 'I' );
571
572 RETURN(0);
573
574 END is_edw_installed;
575
576 -----------------------------------------------------------------------------
577 -- Procedure: update_edw_flag (for BSC v5.0)
578 -----------------------------------------------------------------------------
579
580 -- Purpose: To ENABLE/DISABLE bsc_sys_init.property_code = 'EDW_INSTALLED'.
581 -- This procedure is called by BUILDER.
582 --
583 -- We do the same checks as in is_edw_installed() function since
584 -- from the time EDW is installed/implemented, the objects
585 -- in questions may have been deleted.
586 --
587 -- If the checks fail, we don't update EDW_INSTALLED property code
588 -- wrongly. I log any errors.
589 --
590 -- Arguments
591 --
592 -- h_call_proc_name: Calling Function/Procedure name.
593 -- h_mode: ENABLE/DISABLE.
594 --
595 -----------------------------------------------------------------------------
596
597 PROCEDURE update_edw_flag(
598 h_call_proc_name IN VARCHAR2,
599 h_mode IN VARCHAR2) IS
600
601 -- Local variables
602
603 -- Objects to check for.
604
605 l_edw_obj_name VARCHAR2(30) := 'EDW_DIMENSIONS_MD_V';
606 l_bsc_obj_name VARCHAR2(30) := 'BSC_INTEGRATION_MV_GEN';
607
608 l_count NUMBER := 0;
609 l_message VARCHAR2(1024) := NULL;
610 l_call_proc VARCHAR2(1024) := NULL;
611 l_object_name VARCHAR2(30) := NULL;
612 l_property_code bsc_sys_init.property_code%TYPE := 'EDW_INSTALLED';
613 l_property_value bsc_sys_init.property_value%TYPE;
614
615 l_sql_stmt VARCHAR2(512);
616
617 BEGIN
618
619 l_call_proc := RTRIM(LTRIM(h_call_proc_name));
620
621 IF (h_mode = 'ENABLE') THEN
622
623 -- Check for the existance of EDW metadata view 'EDW_DIMENSIONS_MD_V'.
624 -- (To determine if EDW is properly installed for use by BSC).
625
626 BEGIN -- Check EDW object_name
627
628 SELECT
629 object_name
630 INTO
631 l_object_name
632 FROM
633 user_objects
634 WHERE
635 object_name = l_edw_obj_name
636 AND object_type IN ('VIEW', 'SYNONYM');
637
638 EXCEPTION
639 WHEN NO_DATA_FOUND THEN
640
641 l_message := 'Error: '||l_edw_obj_name||' view not found.';
642
643 --dbms_output.put_line(l_message);
644
645 bsc_message.add(
646 x_message => 'update_edw_flag(ENABLE): '||l_message,
647 x_source => l_call_proc,
648 x_type => 1,
649 x_mode => 'I' );
650
651 goto done;
652
653 END; -- Check EDW object_name
654
655 -- Check if EDW metadata import was done successfully.
656 -- This can be verified if any dimensions exist in edw_dimensions_md_v
657 -- view.
658
659 l_sql_stmt := 'SELECT dim_id FROM edw_dimensions_md_v WHERE ROWNUM < 2';
660
661 BEGIN -- Do dimensions exist ?
662
663 EXECUTE IMMEDIATE l_sql_stmt INTO l_count;
664 --dbms_output.put_line('l_count: '||l_count);
665
666 EXCEPTION
667 WHEN NO_DATA_FOUND THEN
668
669 l_message := 'Warning: No dimensions exist.';
670
671 bsc_message.add(
672 x_message => 'update_edw_flag(ENABLE): '||l_message,
673 x_source => l_call_proc,
674 x_type => 1,
675 x_mode => 'I' );
676
677 goto done;
678
679 END; -- Do dimensions exist ?
680
681
682 -- If we are here, it means that EDW is installed and metadata import
683 -- was done successfully.
684 -- Check if BSC-EDW Integration package(s) is installed.
685
686 BEGIN -- Check BSC object_name
687
688 SELECT
689 object_name
690 INTO
691 l_object_name
692 FROM
693 user_objects
694 WHERE
695 object_name = l_bsc_obj_name
696 AND object_type = 'PACKAGE BODY';
697
698 EXCEPTION
699 WHEN NO_DATA_FOUND THEN
700
701 l_message := 'Error: '||l_bsc_obj_name||' view not found.';
702
703 --dbms_output.put_line(l_message);
704
705 bsc_message.add(
706 x_message => 'update_edw_flag(ENABLE): '||l_message,
707 x_source => l_call_proc,
708 x_type => 1,
709 x_mode => 'I' );
710
711 goto done;
712
713 END; -- Check BSC object_name
714
715 -- Now, we can update property_value for EDW_INSTALLED property_code.
716
717 BEGIN -- Update EDW_INSTALLED flag
718
719 UPDATE bsc_sys_init
720 SET property_value = 'TRUE'
721 WHERE property_code = l_property_code
722 AND property_value = 'FALSE';
723
724 EXCEPTION
725 WHEN OTHERS THEN
726
727 --dbms_output.put_line(SQLERRM);
728
729 bsc_message.add(
730 x_message => 'update_edw_flag(ENABLE): '||SQLERRM,
731 x_source => l_call_proc,
732 x_type => 1,
733 x_mode => 'I' );
734
735 goto done;
736
737 END; -- Update EDW_INSTALLED flag
738
739 -- Since we updated EDW_INSTALLED property code, we can create the
740 -- EDW_TIME_M synonym from BSC schema. This is done only once.
741 --
742 create_synonym_for_edw_time_m(l_call_proc);
743
744 --dbms_output.put_line('Updated bsc_sys_init.EDW_INSTALLED = TRUE.');
745
746
747 <<done>>
748 NULL;
749
750 ELSIF (h_mode = 'DISABLE') THEN
751
752 -- Check if any KPIs are currently mapped to EDW. If so, ask the user
753 -- to delete them before we disable EDW Integration.
754
755 l_count := 0;
756
757 BEGIN -- Check for EDW mapped KPIs
758
759 SELECT
760 indicator
761 INTO
762 l_count
763 FROM
764 bsc_kpis_vl
765 WHERE
766 edw_flag = 1
767 AND ROWNUM < 2;
768
769 EXCEPTION
770 WHEN OTHERS THEN
771
772 --dbms_output.put_line(SQLERRM);
773
774 bsc_message.add(
775 x_message => 'update_edw_flag(DISABLE): '||SQLERRM,
776 x_source => l_call_proc,
777 x_type => 1,
778 x_mode => 'I' );
779
780 END; -- Check for EDW mapped KPIs
781
782
783 -- If there are EDW mapped KPIs, don't update flag. Instead, ask user to
784 -- first delete these KPIs before he can disable it.
785
786 IF (l_count = 0) THEN
787
788 BEGIN -- Update EDW_INSTALLED flag
789
790 UPDATE bsc_sys_init
791 SET property_value = 'FALSE'
792 WHERE property_code = l_property_code
793 AND property_value = 'TRUE';
794
795 --dbms_output.put_line('Updated bsc_sys_init.EDW_INSTALLED = FALSE.');
796
797 EXCEPTION
798 WHEN OTHERS THEN
799
800 --dbms_output.put_line(SQLERRM);
801
802 bsc_message.add(
803 x_message => 'update_edw_flag(ENABLE): '||SQLERRM,
804 x_source => l_call_proc,
805 x_type => 1,
806 x_mode => 'I' );
807
808 END; -- Update EDW_INSTALLED flag
809
810 ELSE
811
812 l_message := BSC_UPDATE_UTIL.Get_Message('BSC_EDW_DISABLE');
813
814 --dbms_output.put_line(l_message);
815
816 bsc_message.add(
817 x_message => l_message,
818 x_source => l_call_proc,
819 x_type => 0,
820 x_mode => 'I' );
821
822 END IF; -- IF (l_count = 0)
823
824 ELSE
825
826 -- Invalid mode passed.
827
828 l_message := 'ERROR: Invalid mode ('||h_mode||') passed.';
829
830 --dbms_output.put_line(l_message);
831
832 bsc_message.add(
833 x_message => 'update_edw_flag(): '||l_message,
834 x_source => l_call_proc,
835 x_mode => 'I' );
836
837 END IF; -- IF (p_mode = 'ENABLE')
838
839 COMMIT WORK;
840
841 EXCEPTION
842 WHEN OTHERS THEN
843 --dbms_output.put_line(SQLERRM);
844 bsc_message.add(
845 x_message => 'update_edw_flag: '||SQLERRM,
846 x_source => l_call_proc,
847 x_mode => 'I' );
848
849 COMMIT WORK;
850
851 END update_edw_flag;
852
853 -----------------------------------------------------------------------------
854
855 /* The following function is used to get the dataset id for an analysis
856 option. A function is needed to do this because of the way
857 BSC_KPI_ANALYSIS_MEASURES_B handles analysis option ids, it has different
858 columns for the different analysis groups. This Function in a way
859 normalizes these columns.
860 Parameters for the function are: BSC KPI Id, Analysis Option group Id,
861 Analysis Option Id.
862 */
863
864 function get_dataset_id(
865 p_kpi_id number
866 ,p_option_group_id number
867 ,p_option_id number
868 ) return number is
869
870 TYPE Recdc_value IS REF CURSOR;
871 dc_value Recdc_value;
872
873 l_cnt number;
874 l_dataset_id number;
875
876 l_column varchar2(30);
877 l_sql varchar2(5000);
878
879 begin
880
881 if p_option_group_id = 0 then
882 l_column := 'analysis_option0';
883 elsif p_option_group_id = 1 then
884 l_column := 'analysis_option1';
885 else
886 l_column := 'analysis_option2';
887 end if;
888
889 l_sql := ' select distinct dataset_id ' ||
890 ' from BSC_KPI_ANALYSIS_MEASURES_B ' ||
891 ' where indicator = :1 '||
892 ' and ' || l_column || ' = : 2' ;
893
894 open dc_value for l_sql using p_kpi_id,p_option_id;
895 fetch dc_value into l_dataset_id;
896 close dc_value;
897 return l_dataset_id;
898
899 EXCEPTION
900 when others then
901 return NULL;
902 --dbms_output.put_line('Error:' || SQLERRM);
903
904 end get_dataset_id;
905
906 -----------------------------------------------------------------------------
907
908 /* The following function is used to obtain the dimension levels for a given
909 Analysis Option. This function returns all dimension levels in a single
910 string.
911 */
912
913 function get_kpi_dim_levels(
914 p_kpi_id number
915 ,p_dim_set_id number
916 ) return varchar2 is
917 l_dim_levels varchar2(32000);
918
919 CURSOR c_KPI_Names IS
920 SELECT DISTINCT NAME
921 FROM BSC_KPI_DIM_LEVELS_TL
922 WHERE INDICATOR = p_kpi_id
923 AND DIM_SET_ID = p_dim_set_id;
924 begin
925 FOR cd IN c_KPI_Names LOOP
926 IF (l_dim_levels IS NULL) THEN
927 l_dim_levels := cd.Name;
928 ELSE
929 l_dim_levels := l_dim_levels || ', ' || cd.Name;
930 END IF;
931 END LOOP;
932 return l_dim_levels;
933 EXCEPTION
934 when others then
935 NULL;
936 --dbms_output.put_line('Error:' || SQLERRM);
937 end get_kpi_dim_levels;
938
939 function get_kpi_dim_level_short_names(
940 p_kpi_id number
941 ,p_dim_set_id number
942 ) return varchar2 is
943 l_dim_levels varchar2(2000);
944
945 CURSOR c_KPI_Names IS
946 SELECT DISTINCT Level_ShortName
947 FROM BSC_KPI_DIM_LEVELS_VL
948 WHERE INDICATOR = p_kpi_id
949 AND DIM_SET_ID = p_dim_set_id;
950 begin
951 FOR cd IN c_KPI_Names LOOP
952 IF (l_dim_levels IS NULL) THEN
953 l_dim_levels := cd.Level_ShortName;
954 ELSE
955 l_dim_levels := l_dim_levels || ', ' || cd.Level_ShortName;
956 END IF;
957 END LOOP;
958 return l_dim_levels;
959 EXCEPTION
960 when others then
961 NULL;
962 --dbms_output.put_line('Error:' || SQLERRM);
963
964 end get_kpi_dim_level_short_names;
965
966 function get_system_timestamp(
967 x_return_status OUT NOCOPY varchar2
968 ,x_msg_count OUT NOCOPY number
969 ,x_msg_data OUT NOCOPY varchar2
970 ) return varchar2 is
971
972 l_timestamp varchar2(20);
973
974 begin
975
976 select to_char(last_update_date, 'DD-MON-YYYY-HH24-MI-SS')
977 into l_timestamp
978 from BSC_SYS_INIT
979 where property_code = 'LOCK_SYSTEM';
980
981 return l_timestamp;
982
983 EXCEPTION
984 WHEN FND_API.G_EXC_ERROR THEN
985 rollback;
986 x_return_status := FND_API.G_RET_STS_ERROR;
987 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
988 ,p_data => x_msg_data);
989 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
990 rollback;
991 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
992 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
993 ,p_data => x_msg_data);
994 WHEN NO_DATA_FOUND THEN
995 rollback;
996 x_return_status := FND_API.G_RET_STS_ERROR;
997 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
998 ,p_data => x_msg_data);
999 WHEN OTHERS THEN
1000 rollback;
1001 FND_MSG_PUB.Initialize;
1002 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1003 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1004 ,p_data => x_msg_data);
1005
1006 end get_system_timestamp;
1007
1008 function get_session_error(
1009 x_return_status OUT NOCOPY varchar2
1010 ,x_msg_count OUT NOCOPY number
1011 ,x_msg_data OUT NOCOPY varchar2
1012 ) return varchar2 is
1013
1014 l_message varchar2(2000);
1015 l_session_id number;
1016 l_count number;
1017
1018 begin
1019
1020 FND_MSG_PUB.Initialize;
1021
1022 select userenv('SESSIONID')
1023 into l_session_id
1024 from dual;
1025
1026 select count(message)
1027 into l_count
1028 from BSC_MESSAGE_LOGS
1029 where type = 0
1030 and upper(source) = 'BSC_SECURITY.CHECK_SYSTEM_LOCK'
1031 and last_update_login = l_session_id;
1032
1033 if l_count < 1 then
1034 return 'N';
1035 else
1036 select message
1037 into l_message
1038 from BSC_MESSAGE_LOGS
1039 where type = 0
1040 and upper(source) = 'BSC_SECURITY.CHECK_SYSTEM_LOCK'
1041 and last_update_login = l_session_id;
1042
1043 return l_message;
1044
1045 end if;
1046
1047 EXCEPTION
1048 WHEN FND_API.G_EXC_ERROR THEN
1049 rollback;
1050 x_return_status := FND_API.G_RET_STS_ERROR;
1051 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1052 ,p_data => x_msg_data);
1053 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1054 rollback;
1055 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1056 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1057 ,p_data => x_msg_data);
1058 WHEN NO_DATA_FOUND THEN
1059 rollback;
1060 x_return_status := FND_API.G_RET_STS_ERROR;
1061 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1062 ,p_data => x_msg_data);
1063 WHEN OTHERS THEN
1064 rollback;
1065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1066 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count
1067 ,p_data => x_msg_data);
1068
1069 end get_session_error;
1070
1071
1072 FUNCTION ListToNumericArray(
1073 x_string IN VARCHAR2,
1074 x_number_array IN OUT NOCOPY t_array_of_number,
1075 x_separator IN VARCHAR2
1076 ) RETURN NUMBER IS
1077
1078 h_num_items NUMBER := 0;
1079
1080 h_sub_string VARCHAR2(32700);
1081 h_position NUMBER;
1082
1083 BEGIN
1084
1085 IF x_string IS NOT NULL THEN
1086 h_sub_string := x_string;
1087 h_position := INSTR(h_sub_string, x_separator);
1088
1089 WHILE h_position <> 0 LOOP
1090 h_num_items := h_num_items + 1;
1091 x_number_array(h_num_items) := TO_NUMBER(RTRIM(LTRIM(SUBSTR(h_sub_string, 1, h_position - 1))));
1092
1093 h_sub_string := SUBSTR(h_sub_string, h_position + 1);
1094 h_position := INSTR(h_sub_string, x_separator);
1095 END LOOP;
1096
1097 h_num_items := h_num_items + 1;
1098 x_number_array(h_num_items) := TO_NUMBER(RTRIM(LTRIM(h_sub_string)));
1099
1100 END IF;
1101
1102 RETURN h_num_items;
1103
1104 END ListToNumericArray;
1105
1106
1107 FUNCTION ListToStringArray(
1108 x_string IN VARCHAR2,
1109 x_array IN OUT NOCOPY t_array_of_varchar2,
1110 x_separator IN VARCHAR2
1111 ) RETURN NUMBER IS
1112
1113 h_num_items NUMBER := 0;
1114
1115 h_sub_string VARCHAR2(32700);
1116 h_position NUMBER;
1117
1118 BEGIN
1119
1120 IF x_string IS NOT NULL THEN
1121 h_sub_string := x_string;
1122 h_position := INSTR(h_sub_string, x_separator);
1123
1124 WHILE h_position <> 0 LOOP
1125 h_num_items := h_num_items + 1;
1126 x_array(h_num_items) := RTRIM(LTRIM(SUBSTR(h_sub_string, 1, h_position - 1)));
1127
1128 h_sub_string := SUBSTR(h_sub_string, h_position + 1);
1129 h_position := INSTR(h_sub_string, x_separator);
1130 END LOOP;
1131
1132 h_num_items := h_num_items + 1;
1133 x_array(h_num_items) := RTRIM(LTRIM(h_sub_string));
1134
1135 END IF;
1136
1137 RETURN h_num_items;
1138
1139 END ListToStringArray;
1140
1141
1142 PROCEDURE Add_To_Fnd_Msg_Stack
1143 (p_error_tbl IN BIS_UTILITIES_PUB.ERROR_TBL_TYPE
1144 ,x_msg_count OUT NOCOPY NUMBER
1145 ,x_msg_data OUT NOCOPY VARCHAR2
1146 ,x_return_status OUT NOCOPY VARCHAR2
1147 )
1148 IS
1149 BEGIN
1150 IF (p_error_tbl.COUNT > 0) THEN
1151 FOR l_Count in 1..p_error_tbl.COUNT LOOP
1152 FND_MESSAGE.SET_NAME('BIS',p_error_tbl(l_count).error_msg_name);
1153
1154 -- mdamle 08/06/2003 - Add tokens and values
1155 if (p_error_tbl(l_count).error_token1 is not null) then
1156 FND_MESSAGE.SET_TOKEN(p_error_tbl(l_count).error_token1, p_error_tbl(l_count).error_value1);
1157 end if;
1158
1159 if (p_error_tbl(l_count).error_token2 is not null) then
1160 FND_MESSAGE.SET_TOKEN(p_error_tbl(l_count).error_token2, p_error_tbl(l_count).error_value2);
1161 end if;
1162 if (p_error_tbl(l_count).error_token3 is not null) then
1163 FND_MESSAGE.SET_TOKEN(p_error_tbl(l_count).error_token3, p_error_tbl(l_count).error_value3);
1164 end if;
1165
1166 FND_MSG_PUB.Add;
1167 END LOOP;
1168 -- Fix for 2332823
1169 FND_MSG_PUB.Count_And_Get
1170 ( p_count => x_msg_count,
1171 p_data => x_msg_data
1172 );
1173 /*
1174 -- Fix for 2254597 starts here
1175 x_msg_count := p_error_tbl.count;
1176 x_msg_data := p_error_tbl(p_error_tbl.count).error_description;
1177 -- Fix for 2254597 ends here
1178 */
1179 END IF;
1180 END ADD_TO_FND_MSG_STACK;
1181
1182 /*********************************************************************************/
1183 FUNCTION is_Internal_User
1184 RETURN BOOLEAN IS
1185 l_internal VARCHAR2(30);
1186 BEGIN
1187 SELECT FND_PROFILE.VALUE('BSC_INTERNAL_USER') INTO l_internal FROM DUAL;
1188 IF((l_internal IS NOT NULL) AND (UPPER(l_internal) = 'YES')) THEN
1189 RETURN TRUE;
1190 ELSE
1191 RETURN FALSE;
1192 END IF;
1193 END is_Internal_User;
1194 /*********************************************************************************/
1195
1196
1197
1198 /**************************************************************************************
1199 FUNCTION get_Next_DispName
1200
1201 Function to generated names as required by Bug 3137260 , for example if Country
1202 is passed we will get 'Country 1' if 'Country 5' is passed, we will get
1203 'Country 6', if 'Country A' is passed, we will get 'Country A 1', etc.
1204 **************************************************************************************/
1205
1206 FUNCTION get_Next_DispName
1207 (
1208 p_Alias IN VARCHAR2
1209 ) RETURN VARCHAR2
1210 IS
1211 l_alias VARCHAR2(255);
1212 l_number NUMBER;
1213 l_return VARCHAR2(255);
1214 l_count NUMBER;
1215 l_count1 NUMBER;
1216 l_tempcnt NUMBER;
1217 l_tempalias VARCHAR2(255);
1218 l_isNumber VARCHAR2(255);
1219 BEGIN
1220 IF (p_Alias IS NULL) THEN
1221 l_return := 'A';
1222 ELSE
1223 l_count := LENGTH(p_Alias);
1224
1225 if(l_count > 255) THEN
1226 l_alias := SUBSTR(p_Alias, 1, 250); -- Reduce the size to 250 chars
1227 else
1228 l_alias := p_Alias;
1229 end if;
1230
1231 l_count1 := INSTR(p_Alias, ' ');
1232 l_tempcnt := 0;
1233 l_tempalias := p_Alias;
1234 l_number := 0;
1235
1236 while(l_count1 > 0) loop
1237 l_tempcnt := l_tempcnt + l_count1;
1238 l_count := LENGTH(l_tempalias);
1239 l_tempalias := SUBSTR(l_tempalias, l_count1+1, l_count);
1240 l_count1 := INSTR(l_tempalias, ' ');
1241 l_number := l_number + 1;
1242 end loop;
1243 l_count := LENGTH(p_Alias);
1244 l_tempalias := RTRIM(LTRIM(l_tempalias));
1245
1246 select replace(translate(l_tempalias,'0123456789.','???????????'), '?', '')
1247 into l_isNumber
1248 from dual;
1249
1250 if(l_number = 0 or (l_isNumber is not null)) then
1251 l_return := l_alias ||' '||TO_CHAR(1);
1252 else
1253 l_alias := SUBSTR(l_alias, 1, l_tempcnt-1);
1254 l_return := l_alias ||' '||TO_CHAR(TO_NUMBER(l_tempalias)+1);
1255 end if;
1256 END IF;
1257 RETURN l_return;
1258 END get_Next_DispName;
1259
1260 /**************************************************************************************
1261 **************************************************************************************/
1262
1263 FUNCTION get_Next_Name (
1264 p_Name IN VARCHAR2
1265 ,p_Max_Count IN NUMBER
1266 ,p_Table_Name IN VARCHAR2
1267 ,p_Column_Name IN VARCHAR2
1268 ,p_Character IN CHAR
1269 ) RETURN VARCHAR2
1270 IS
1271 l_sql VARCHAR2(32000);
1272 l_Name VARCHAR2(2000);
1273 l_Count NUMBER := 0;
1274 l_Loop_Count NUMBER := 1;
1275 TYPE c_cur_type IS REF CURSOR;
1276 cd c_cur_type;
1277 BEGIN
1278 l_sql := 'SELECT COUNT(1) FROM '|| p_Table_Name || ' WHERE ' || p_Column_Name ||' = :1';
1279 OPEN cd FOR l_sql USING p_Name ;
1280 FETCH cd INTO l_Count;
1281 CLOSE cd;
1282
1283 l_Name := p_Name;
1284 IF l_Count = 0 THEN
1285 RETURN p_Name;
1286 END IF;
1287
1288 WHILE l_Count > 0 LOOP
1289 l_Name := p_Name || p_Character || l_Loop_Count;
1290 l_Loop_Count := l_Loop_Count + 1;
1291 IF LENGTH(l_Name) > p_Max_Count THEN
1292 l_Name := SUBSTR(p_Name , 0, (LENGTH(p_Name) - (LENGTH(l_Name) - p_Max_Count))) || p_Character || l_Count;
1293 END IF;
1294 l_sql := 'SELECT COUNT(1) FROM '|| p_Table_Name || ' WHERE ' || p_Column_Name ||' = :1';
1295 OPEN cd FOR l_sql USING l_Name ;
1296 FETCH cd INTO l_Count;
1297 CLOSE cd;
1298
1299 END LOOP;
1300 RETURN l_Name;
1301 EXCEPTION
1302 WHEN OTHERS THEN
1303 RETURN p_Name;
1304 END get_Next_Name;
1305
1306 /**************************************************************************************/
1307
1308 /*********************************************************************************
1309 FUNCTION isBscInProductionMode
1310 *********************************************************************************/
1311 FUNCTION isBscInProductionMode
1312 RETURN BOOLEAN
1313 IS
1314 l_property_value BSC_SYS_INIT.Property_Value%TYPE;
1315
1316 CURSOR c_isBscInProductionMode IS
1317 SELECT Property_Value
1318 FROM BSC_SYS_INIT
1319 WHERE PROPERTY_CODE ='SYSTEM_STAGE';
1320 BEGIN
1321 --DBMS_OUTPUT.PUT_LINE('Entered inside BSC_BIS_DIM_OBJ_PUB.isBscInProductionMode Function');
1322 IF (c_isBscInProductionMode%ISOPEN) THEN
1323 CLOSE c_isBscInProductionMode;
1324 END IF;
1325 OPEN c_isBscInProductionMode;
1326 FETCH c_isBscInProductionMode
1327 INTO l_property_value;
1328
1329 CLOSE c_isBscInProductionMode;
1330 --DBMS_OUTPUT.PUT_LINE('Exiting from BSC_BIS_DIM_OBJ_PUB.isBscInProductionMode Function');
1331 IF (l_property_value = '2') THEN
1332 RETURN TRUE;
1333 ELSE
1334 RETURN FALSE;
1335 END IF;
1336 EXCEPTION
1337 WHEN OTHERS THEN
1338 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS <'||SQLERRM||'>');
1339 IF (c_isBscInProductionMode%ISOPEN) THEN
1340 CLOSE c_isBscInProductionMode;
1341 END IF;
1342 RETURN FALSE;
1343 END;
1344 /*********************************************************************************/
1345 FUNCTION is_MV_Exists(
1346 p_MV_Name IN VARCHAR2
1347 ) RETURN BOOLEAN
1348 IS
1349 l_Count NUMBER := 0;
1350 l_Tab_Name VARCHAR2(100);
1351 BEGIN
1352 IF (p_MV_Name IS NULL) THEN
1353 RETURN FALSE;
1354 END IF;
1355 l_Tab_Name := UPPER(TRIM(p_MV_Name));
1356 --Bug#3431750 appended schema name
1357 SELECT COUNT(*) INTO l_Count
1358 FROM ALL_MVIEWS
1359 WHERE MVIEW_NAME = l_Tab_Name
1360 AND OWNER = BSC_APPS.get_user_schema;
1361
1362 IF (l_Count <> 0) THEN
1363 RETURN TRUE;
1364 ELSE
1365 RETURN FALSE;
1366 END IF;
1367 EXCEPTION
1368 WHEN OTHERS THEN
1369 RETURN FALSE;
1370 END is_MV_Exists;
1371 /*********************************************************************************/
1372 FUNCTION is_View_Exists(
1373 p_View_Name IN VARCHAR2
1374 ) RETURN BOOLEAN
1375 IS
1376 l_Count NUMBER := 0;
1377 l_Tab_Name VARCHAR2(100);
1378 BEGIN
1379 IF (p_View_Name IS NULL) THEN
1380 RETURN FALSE;
1381 END IF;
1382 l_Tab_Name := UPPER(TRIM(p_View_Name));
1383 SELECT COUNT(0) INTO l_Count
1384 FROM ALL_VIEWS
1385 WHERE VIEW_NAME = l_Tab_Name
1386 AND OWNER = BSC_APPS.get_user_schema('APPS');
1387
1388 IF (l_Count <> 0) THEN
1389 RETURN TRUE;
1390 ELSE
1391 RETURN FALSE;
1392 END IF;
1393 EXCEPTION
1394 WHEN OTHERS THEN
1395 RETURN FALSE;
1396 END is_View_Exists;
1397 /*********************************************************************************/
1398 FUNCTION is_Table_Exists(
1399 p_Table_Name IN VARCHAR2
1400 ) RETURN BOOLEAN
1401 IS
1402 l_Count NUMBER := 0;
1403 l_Tab_Name VARCHAR2(100);
1404 BEGIN
1405 IF (p_Table_Name IS NULL) THEN
1406 RETURN FALSE;
1407 END IF;
1408 l_Tab_Name := UPPER(TRIM(p_Table_Name));
1409 SELECT COUNT(0) INTO l_Count
1410 FROM ALL_TABLES
1411 WHERE TABLE_NAME = l_Tab_Name
1412 AND OWNER = get_owner_for_object(p_Table_Name);
1413 IF (l_Count <> 0) THEN
1414 RETURN TRUE;
1415 ELSE
1416 RETURN FALSE;
1417 END IF;
1418 EXCEPTION
1419 WHEN OTHERS THEN
1420 RETURN FALSE;
1421 END is_Table_Exists;
1422 /*********************************************************************************/
1423 FUNCTION is_Table_View_Exists(
1424 p_Table_View_Name IN VARCHAR2
1425 ) RETURN BOOLEAN
1426 IS
1427 BEGIN
1428 IF (BSC_UTILITY.is_View_Exists(p_Table_View_Name)) THEN
1429 RETURN TRUE;
1430 ELSIF (BSC_UTILITY.is_Table_Exists(p_Table_View_Name)) THEN
1431 RETURN TRUE;
1432 ELSE
1433 RETURN FALSE;
1434 END IF;
1435 EXCEPTION
1436 WHEN OTHERS THEN
1437 RETURN FALSE;
1438 END is_Table_View_Exists;
1439
1440 /*********************************************************************************/
1441
1442 FUNCTION get_owner_for_object(
1443 p_object_name IN VARCHAR2
1444
1445 ) RETURN VARCHAR2 IS
1446
1447 l_owner VARCHAR2(100);
1448 l_object_type VARCHAR2(100);
1449 l_object_name VARCHAR2(256);
1450
1451 CURSOR c_object_type(c_object_name VARCHAR2)
1452 IS
1453 SELECT object_type
1454 FROM user_objects
1455 WHERE object_name = c_object_name;
1456
1457 CURSOR c_owner(c_syn_name VARCHAR2)
1458 IS
1459 SELECT table_owner
1460 FROM user_synonyms
1461 WHERE synonym_name = c_syn_name;
1462
1463 BEGIN
1464
1465 l_object_name := UPPER(p_object_name);
1466
1467 IF (c_object_type%ISOPEN) THEN
1468 CLOSE c_object_type;
1469 END IF;
1470
1471 OPEN c_object_type(l_object_name);
1472 FETCH c_object_type INTO l_object_type;
1473 CLOSE c_object_type;
1474
1475 IF (l_object_type = 'VIEW') THEN
1476 -- view always in apps schema
1477 l_owner := BSC_APPS.get_user_schema('APPS');
1478 ELSE
1479 -- table then it should be synonym
1480 IF (c_owner%ISOPEN) THEN
1481 CLOSE c_owner;
1482 END IF;
1483
1484 OPEN c_owner(l_object_name);
1485 FETCH c_owner INTO l_owner;
1486 CLOSE c_owner;
1487
1488 IF(l_owner IS NULL) THEN
1489 l_owner := BSC_APPS.get_user_schema('APPS');
1490 END IF;
1491 END IF;
1492
1493 RETURN l_owner;
1494 EXCEPTION
1495 WHEN OTHERS THEN
1496
1497 IF (c_object_type%ISOPEN) THEN
1498 CLOSE c_object_type;
1499 END IF;
1500 IF (c_owner%ISOPEN) THEN
1501 CLOSE c_owner;
1502 END IF;
1503 RAISE;
1504 END get_owner_for_object;
1505
1506
1507 -- Added by ADRAO for End-To-End KPI Project
1508 /*********************************************************************************/
1509 FUNCTION is_Indicator_In_Production(
1510 p_kpi_id IN NUMBER
1511 ) RETURN BOOLEAN IS
1512
1513 CURSOR c_Production IS
1514 SELECT PROTOTYPE_FLAG
1515 FROM BSC_KPIS_B
1516 WHERE INDICATOR = p_Kpi_Id;
1517
1518 l_Prototype_Flag NUMBER;
1519 BEGIN
1520
1521 l_Prototype_Flag := 2;
1522
1523 FOR ckpi IN c_Production LOOP
1524 l_Prototype_Flag := ckpi.Prototype_Flag;
1525 END LOOP;
1526
1527 IF ((l_Prototype_Flag = 0) OR (l_Prototype_Flag = 2)) THEN
1528 RETURN TRUE;
1529 ELSE
1530 RETURN FALSE;
1531 END IF;
1532 EXCEPTION
1533 WHEN OTHERS THEN
1534 RETURN TRUE;
1535 END is_Indicator_In_Production;
1536 /*********************************************************************************/
1537
1538 /*********************************************************************************
1539
1540 This API Is_BSC_Licensed() returns "T" if BSC is licensed otherwise returns "F"
1541
1542 *********************************************************************************/
1543
1544 FUNCTION Is_BSC_Licensed
1545 RETURN VARCHAR2 IS
1546 l_Application_Short_Name FND_APPLICATION.APPLICATION_SHORT_NAME%TYPE;
1547 l_Status FND_PRODUCT_INSTALLATIONS.STATUS%TYPE;
1548 l_Industry FND_PRODUCT_INSTALLATIONS.INDUSTRY%TYPE;
1549 l_Oracle_Schema FND_ORACLE_USERID.ORACLE_USERNAME%TYPE;
1550
1551 l_Return VARCHAR2(1);
1552 l_Function_Return BOOLEAN;
1553
1554 BEGIN
1555
1556 l_Application_Short_Name := BSC_UTILITY.c_BSC;
1557
1558 l_Function_Return := FND_INSTALLATION.Get_App_Info
1559 (
1560 Application_Short_Name => l_Application_Short_Name
1561 , Status => l_Status
1562 , Industry => l_Industry
1563 , Oracle_Schema => l_Oracle_Schema
1564 );
1565
1566 IF (l_Function_Return = TRUE) THEN
1567 IF( (l_Status = 'L') OR (l_Status = 'S') OR (l_Status = 'I')) THEN
1568 RETURN FND_API.G_TRUE;
1569 ELSE
1570 RETURN FND_API.G_FALSE;
1571 END IF;
1572 ELSE
1573 RETURN FND_API.G_FALSE;
1574 END IF;
1575
1576 EXCEPTION
1577 WHEN OTHERS THEN
1578 RETURN FND_API.G_FALSE;
1579
1580 END Is_BSC_Licensed;
1581
1582
1583
1584 /*********************************************************************************
1585
1586 This API returns 'T' if the Advance Summarization is set >= 0, else returns 'F'
1587
1588 Added for Start-to-End KPI Project.
1589 *********************************************************************************/
1590
1591 FUNCTION Is_Adv_Sum_Enabled
1592 RETURN VARCHAR2 IS
1593 l_Profile_Value VARCHAR2(10);
1594 BEGIN
1595
1596 SELECT FND_PROFILE.VALUE(c_ADV_SUMMARIZATION_LEVEL)
1597 INTO l_Profile_Value
1598 FROM DUAL;
1599
1600 IF (TO_NUMBER(NVL(l_Profile_Value, '-1')) >= 0) THEN
1601 RETURN FND_API.G_TRUE;
1602 ELSE
1603 RETURN FND_API.G_FALSE;
1604 END IF;
1605
1606 EXCEPTION
1607 WHEN NO_DATA_FOUND THEN
1608 RETURN FND_API.G_FALSE;
1609
1610 WHEN OTHERS THEN
1611 RETURN FND_API.G_FALSE;
1612
1613 END Is_Adv_Sum_Enabled;
1614
1615
1616 /*********************************************************************************
1617
1618 This API returns 'T' if table BSC_SYS_INIT.adv_sum_level is set >= 0, else returns 'F'
1619
1620 Added for Start-to-End KPI Project.
1621 *********************************************************************************/
1622
1623 FUNCTION Is_Init_Adv_Sum_Enabled
1624 RETURN VARCHAR2 IS
1625 l_Profile_Value VARCHAR2(10);
1626 BEGIN
1627
1628 SELECT property_value
1629 INTO l_Profile_Value
1630 FROM BSC_SYS_INIT
1631 WHERE property_code = 'ADV_SUM_LEVEL';
1632
1633 IF (TO_NUMBER(NVL(l_Profile_Value, '-1')) >= 0) THEN
1634 RETURN FND_API.G_TRUE;
1635 ELSE
1636 RETURN FND_API.G_FALSE;
1637 END IF;
1638
1639 EXCEPTION
1640 WHEN NO_DATA_FOUND THEN
1641 RETURN FND_API.G_FALSE;
1642
1643 WHEN OTHERS THEN
1644 RETURN FND_API.G_FALSE;
1645
1646 END Is_Init_Adv_Sum_Enabled;
1647
1648
1649 /*********************************************************************************
1650
1651 This API returns 'T' if the Advance Summarization is set >= 0 or
1652 if table BSC_SYS_INIT.adv_sum_level is set >= 0, else returns 'F'
1653
1654 Added for Start-to-End KPI Project.
1655 *********************************************************************************/
1656
1657 FUNCTION Is_Adv_Summarization_Enabled
1658 RETURN VARCHAR2 IS
1659 l_Profile_Value VARCHAR2(10);
1660 BEGIN
1661
1662 IF (Is_Adv_Sum_Enabled = FND_API.G_TRUE) THEN
1663 RETURN FND_API.G_TRUE;
1664 ELSIF (Is_Init_Adv_Sum_Enabled = FND_API.G_TRUE) THEN
1665 RETURN FND_API.G_TRUE;
1666 ELSE
1667 RETURN FND_API.G_FALSE;
1668 END IF;
1669
1670 EXCEPTION
1671 WHEN NO_DATA_FOUND THEN
1672 RETURN FND_API.G_FALSE;
1673
1674 WHEN OTHERS THEN
1675 RETURN FND_API.G_FALSE;
1676
1677 END Is_Adv_Summarization_Enabled;
1678
1679
1680 /*********************************************************************************
1681 This funciton is added to get the no of independent dimension objects in a
1682 dimension set of an Objective
1683 Input Parameters:-
1684 p_kpi_id :- Objective Id
1685 p_dim_set_id :- Dimension set Id
1686 *********************************************************************************/
1687 FUNCTION get_nof_independent_dimobj
1688 ( p_Kpi_Id IN NUMBER
1689 , p_Dim_Set_Id IN NUMBER
1690 )RETURN NUMBER IS
1691 l_count NUMBER;
1692 l_souce VARCHAR2(100);
1693 l_Flag BOOLEAN := FALSE;
1694 l_Short_Name BSC_KPIS_B.SHORT_NAME%TYPE;
1695 l_Is_EndToEnd_Kpi VARCHAR2(2);
1696
1697 CURSOR c_source IS
1698 SELECT DISTINCT(sys.source) source
1699 FROM bsc_sys_dim_levels_b sys,
1700 bsc_kpi_dim_level_properties kpi
1701 WHERE sys.dim_level_id = kpi.dim_level_id
1702 AND kpi.INDICATOR = p_Kpi_Id
1703 AND kpi.dim_set_id = p_Dim_Set_Id;
1704
1705 BEGIN
1706 l_count := 0;
1707 FOR c_rec IN c_source
1708 LOOP
1709 IF(c_rec.source = 'BSC') THEN
1710 l_Flag := TRUE;
1711 EXIT;
1712 ELSIF(c_rec.source = 'PMF') THEN
1713 SELECT short_name
1714 INTO l_Short_Name
1715 FROM BSC_KPIS_B
1716 WHERE indicator= p_Kpi_Id;
1717 l_Is_EndToEnd_Kpi := BSC_BIS_KPI_CRUD_PUB.IS_KPI_ENDTOEND_KPI(l_Short_Name);
1718 IF(l_Is_EndToEnd_Kpi = 'T') THEN
1719 l_Flag := TRUE;
1720 EXIT;
1721 END IF;
1722 END IF;
1723 END LOOP;
1724
1725 IF(l_Flag = TRUE)THEN
1726 SELECT COUNT(0) into l_count
1727 FROM (
1728 (SELECT dim_level_index
1729 FROM bsc_kpi_dim_levels_b
1730 WHERE INDICATOR = p_kpi_id
1731 AND dim_set_id = p_dim_set_id )
1732 MINUS
1733 ((SELECT DISTINCT (parent_level_index)
1734 FROM bsc_kpi_dim_levels_b
1735 WHERE INDICATOR = p_kpi_id
1736 AND dim_set_id = p_dim_set_id
1737 AND parent_level_index IS NOT NULL)
1738 UNION
1739 (SELECT dim_level_index
1740 FROM bsc_kpi_dim_levels_b
1741 WHERE INDICATOR = p_kpi_id
1742 AND dim_set_id = p_dim_set_id
1743 AND parent_level_index IS NOT NULL)));
1744 END IF;
1745
1746 return l_count;
1747
1748 END get_nof_independent_dimobj;
1749
1750 /*********************************************************************************/
1751
1752
1753
1754
1755 /*********************************************************************************
1756 Return default internal name based on p_type. Currently supported type:
1757 bsc_utility.c_BSC_MEASURE,
1758 bsc_utility.c_BSC_DIMENSION,
1759 bsc_utility.c_BSC_DIM_OBJECT
1760
1761 Input Parameters:-
1762 p_type :- one of the supported types
1763 *********************************************************************************/
1764 FUNCTION Get_Default_Internal_Name(
1765 p_type IN VARCHAR2
1766 )RETURN VARCHAR2 IS
1767 l_next NUMBER := 0;
1768 l_type VARCHAR2(15);
1769 l_ret_val VARCHAR2(30);
1770 l_msg_data VARCHAR2(10);
1771 l_msg_count NUMBER;
1772 BEGIN
1773 l_type := UPPER(p_type);
1774 IF (l_type = bsc_utility.c_BSC_MEASURE) THEN
1775 SELECT NVL(MAX(dataset_id) + 1, 0)
1776 INTO l_next
1777 FROM BSC_SYS_DATASETS_TL;
1778 l_ret_val := bsc_bis_measure_pub.c_PMD || l_next;
1779
1780 ELSIF (l_type = bsc_utility.c_BSC_DIMENSION) THEN
1781 SELECT NVL(MAX(dim_group_id) + 1, 0)
1782 INTO l_next
1783 FROM BSC_SYS_DIM_GROUPS_TL;
1784 l_ret_val := bsc_bis_dimension_pub.c_BSC_DIM || l_next;
1785
1786 ELSIF (l_type = bsc_utility.c_BSC_DIM_OBJ) THEN
1787 SELECT NVL(MAX(dim_level_id) + 1, 0)
1788 INTO l_next
1789 FROM BSC_SYS_DIM_LEVELS_B;
1790 l_ret_val := bsc_bis_dim_obj_pub.c_BSC_DIM_OBJ || l_next;
1791 END IF;
1792
1793 RETURN l_ret_val;
1794
1795 EXCEPTION
1796 WHEN FND_API.G_EXC_ERROR THEN
1797 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1798 ,p_data => l_msg_data);
1799 RAISE;
1800 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1801 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1802 ,p_data => l_msg_data);
1803 RAISE;
1804 WHEN NO_DATA_FOUND THEN
1805 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1806 ,p_data => l_msg_data);
1807 RAISE;
1808 WHEN OTHERS THEN
1809 FND_MSG_PUB.Count_And_Get( p_count => l_msg_count
1810 ,p_data => l_msg_data);
1811 RAISE;
1812 END Get_Default_Internal_Name;
1813
1814
1815
1816 /************************************************************
1817 Name :- Parse_String
1818 Description :- This function will parse the comma separated String
1819 and return the table containing the data
1820 Before using this API check if p_List is NULL or not
1821 and lenght(p_List) must be >0.and
1822 Creaor :- Ashankar to fix the bug 3908204
1823 /*****************************************************************/
1824
1825 PROCEDURE Parse_String
1826 (
1827 p_List VARCHAR2
1828 , p_Separator VARCHAR2
1829 , p_List_Data OUT NOCOPY BSC_UTILITY.varchar_tabletype
1830 , p_List_number OUT NOCOPY NUMBER
1831 ) IS
1832
1833 l_start NUMBER;
1834 l_end NUMBER;
1835 l_len NUMBER;
1836 BEGIN
1837
1838 p_List_number:=0;
1839 l_len:=LENGTH(p_List);
1840
1841 IF (INSTR(p_List,p_Separator)=0) THEN
1842 p_List_number:=1;
1843 p_List_Data(p_List_number):=TRIM(p_List);
1844 ELSE
1845 l_start:=1;
1846 LOOP
1847 l_end:=INSTR(p_List,p_Separator,l_start);
1848 IF(l_end = 0 ) THEN
1849 l_end:=l_len+1;
1850 END IF;
1851 p_List_number:=p_List_number+1;
1852 p_List_Data(p_List_number):=TRIM(SUBSTR(p_List,l_start,(l_end-l_start)));
1853 l_start:=l_end+1;
1854 IF (l_end>=l_len) THEN
1855 EXIT;
1856 END IF;
1857 END LOOP;
1858 END IF;
1859 END Parse_String;
1860
1861 /************************************************************
1862 Name :- get_Next_Alias
1863 Description :- This function will retrun the next alias which
1864 can be sufuxed to short_names and Names.
1865 It can be used to check the uniqueness of short_names
1866 or names
1867 Creaor :- Ashankar to fix the bug 4054812
1868 /*****************************************************************/
1869
1870 FUNCTION get_Next_Alias
1871 (
1872 p_Alias IN VARCHAR2
1873 ) RETURN VARCHAR2
1874 IS
1875 l_alias VARCHAR2(3);
1876 l_return VARCHAR2(3);
1877 l_count NUMBER;
1878 BEGIN
1879 IF (p_Alias IS NULL) THEN
1880 l_return := 'A';
1881 ELSE
1882 l_count := LENGTH(p_Alias);
1883 IF (l_count = 1) THEN
1884 l_return := 'A0';
1885 ELSIF (l_count > 1) THEN
1886 l_alias := SUBSTR(p_Alias, 2);
1887 l_count := TO_NUMBER(l_alias)+1;
1888 l_return := 'A'||TO_CHAR(l_count);
1889 END IF;
1890 END IF;
1891 RETURN l_return;
1892
1893 END get_Next_Alias;
1894
1895
1896 /*********************************************************************************
1897 API TO CHECK IF MIXED DIMENSION OBJECTS SHOULD BE ALLOWED AT THE
1898 DIMENSION AND DIMENSION SET LEVEL
1899 *********************************************************************************/
1900
1901 FUNCTION is_Mix_Dim_Objects_Allowed
1902 RETURN VARCHAR2 IS
1903 l_Return VARCHAR2(1);
1904 l_Count NUMBER;
1905 BEGIN
1906 l_Count := 0;
1907 l_Return := FND_API.G_FALSE;
1908
1909 SELECT COUNT(1) INTO l_Count
1910 FROM BSC_SYS_INIT B
1911 WHERE B.PROPERTY_CODE = c_MIXED_DIM_OBJS
1912 AND B.PROPERTY_VALUE > 0;
1913
1914 IF (l_Count > 0) THEN
1915 l_Return := FND_API.G_TRUE;
1916 END IF;
1917
1918 RETURN l_Return;
1919
1920 EXCEPTION
1921 WHEN OTHERS THEN
1922 RETURN FND_API.G_FALSE;
1923 END is_Mix_Dim_Objects_Allowed;
1924
1925 FUNCTION get_valid_bsc_master_tbl_name
1926 (
1927 p_short_name IN VARCHAR2
1928 )
1929 RETURN VARCHAR2 IS
1930 l_found BOOLEAN;
1931 l_alias VARCHAR2(30);
1932 l_count NUMBER;
1933 l_table_name BSC_SYS_DIM_LEVELS_B.LEVEL_TABLE_NAME%TYPE;
1934 BEGIN
1935 l_found := TRUE;
1936 l_alias := NULL;
1937 l_table_name := 'BSC_D_' || SUBSTR(UPPER(REPLACE(p_short_name, ' ', '_')) , 1, 22) || '_V';
1938 WHILE (l_found) LOOP
1939 SELECT COUNT(1)
1940 INTO l_count
1941 FROM BSC_SYS_DIM_LEVELS_B
1942 WHERE Level_Table_Name = l_table_name
1943 AND short_name <> p_short_name ;
1944 IF (l_count = 0) THEN
1945 l_found := FALSE;
1946 END IF;
1947 IF(l_found) THEN
1948 l_alias := bsc_utility.get_Next_Alias(l_alias);
1949 l_table_name := 'BSC_D_' ||SUBSTR(UPPER(REPLACE(p_short_name, ' ', '_')) , 1, 18)||l_alias|| '_V';
1950 END IF;
1951 END LOOP;
1952
1953 RETURN l_table_name;
1954
1955 END get_valid_bsc_master_tbl_name;
1956
1957 /*
1958 API to return if the Dimension Object and Dimension passed form a Periodicity type
1959 */
1960
1961 FUNCTION Is_Time_Period_Type (
1962 p_Dimension_Short_Name IN VARCHAR2
1963 , p_Dimension_Object_Short_Name IN VARCHAR2
1964 ) RETURN VARCHAR2 IS
1965 l_Count NUMBER;
1966 l_Sql VARCHAR2(300);
1967 BEGIN
1968
1969 -- Using dyanamic query, since BSC52 maynot have the data model changes for the SHORT_NAME
1970 l_Sql := ' SELECT COUNT(1) ' ||
1971 ' FROM BSC_SYS_CALENDARS_B BC, BSC_SYS_PERIODICITIES BP ' ||
1972 ' WHERE BC.SHORT_NAME = :1 AND BP.SHORT_NAME = :2 ' ||
1973 ' AND BP.CALENDAR_ID = BC.CALENDAR_ID ';
1974
1975 EXECUTE IMMEDIATE l_Sql INTO l_Count USING p_Dimension_Short_Name, p_Dimension_Object_Short_Name;
1976
1977 IF (l_Count <> 0) THEN
1978 RETURN FND_API.G_TRUE;
1979 END IF;
1980
1981 RETURN FND_API.G_FALSE;
1982
1983 EXCEPTION
1984 WHEN OTHERS THEN
1985 RETURN FND_API.G_FALSE;
1986 END Is_Time_Period_Type;
1987
1988
1989 /*
1990 API to return if the Dimension passed form a Periodicity type
1991 */
1992
1993 FUNCTION is_dim_time_period_type (
1994 p_dimension_short_name IN VARCHAR2
1995 )
1996 RETURN VARCHAR2
1997 IS
1998 l_count NUMBER;
1999 l_sql VARCHAR2(300);
2000 BEGIN
2001
2002 -- Using dyanamic query, since BSC52 maynot have the data model changes for the SHORT_NAME
2003 l_sql := ' SELECT COUNT(1) ' ||
2004 ' FROM BSC_SYS_CALENDARS_B BC ' ||
2005 ' WHERE BC.SHORT_NAME = :1 ';
2006
2007 EXECUTE IMMEDIATE l_sql INTO l_Count USING p_dimension_short_name;
2008
2009 IF (l_count <> 0) THEN
2010 RETURN FND_API.G_TRUE;
2011 END IF;
2012
2013 RETURN FND_API.G_FALSE;
2014
2015 EXCEPTION
2016 WHEN OTHERS THEN
2017 RETURN FND_API.G_FALSE;
2018 END is_dim_time_period_type;
2019
2020
2021 /*
2022 API to return if the Dimension Object of a Periodicity type
2023 */
2024
2025 FUNCTION Is_Dim_Object_Periodicity_Type (
2026 p_Dimension_Object_Short_Name IN VARCHAR2
2027 ) RETURN VARCHAR2 IS
2028 l_Count NUMBER;
2029 l_Sql VARCHAR2(300);
2030 BEGIN
2031
2032 l_Sql := ' SELECT COUNT(1) ' ||
2033 ' FROM BSC_SYS_PERIODICITIES BP ' ||
2034 ' WHERE BP.SHORT_NAME = :1 ' ;
2035
2036 EXECUTE IMMEDIATE l_Sql INTO l_Count USING p_Dimension_Object_Short_Name;
2037
2038 IF (l_Count <> 0) THEN
2039 RETURN FND_API.G_TRUE;
2040 END IF;
2041
2042 RETURN FND_API.G_FALSE;
2043
2044 EXCEPTION
2045 WHEN OTHERS THEN
2046 RETURN FND_API.G_FALSE;
2047 END Is_Dim_Object_Periodicity_Type;
2048
2049 /*********************************************************************************
2050 API TO CHECK IF DIMENSION/DIMENSION OBJECTS SHOULD BE ALLOWED AT THE
2051 DIMENSION AND DIMENSION SET LEVEL
2052 Type- DIMENSION, DIMENSION_OBJECT, MIX(DIMENSION+DIMENSION_OBJECT).
2053 entity_short_name- comman seperated short_names.
2054 *********************************************************************************/
2055
2056 PROCEDURE Enable_Dimensions_Entity (
2057 p_Entity_Type IN VARCHAR2
2058 , p_Entity_Short_Names IN VARCHAR2
2059 , p_Entity_Action_Type IN VARCHAR2
2060 , x_Return_Status OUT NOCOPY VARCHAR2
2061 , x_Msg_Count OUT NOCOPY NUMBER
2062 , x_Msg_Data OUT NOCOPY VARCHAR2
2063 ) IS
2064 l_Count NUMBER;
2065 l_Sql VARCHAR2(2000);
2066 l_Entity_Name VARCHAR2(300);
2067 l_Short_Name VARCHAR2(100);
2068 l_pos_value NUMBER;
2069 l_temp_snames VARCHAR2(2000);
2070 l_dim_sname VARCHAR2(30);
2071 l_dim_obj_sname VARCHAR2(30);
2072 BEGIN
2073 FND_MSG_PUB.Initialize;
2074 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
2075
2076 IF ((p_Entity_Short_Names IS NULL) OR (BIS_UTILITIES_PUB.Enable_Generated_Source_Report = FND_API.G_FALSE)) THEN
2077 RETURN;
2078 END IF;
2079
2080 l_temp_snames := p_Entity_Short_Names;
2081 WHILE (Is_More(l_temp_snames, l_Short_Name)) LOOP
2082
2083 IF(p_Entity_Type = c_MIXED_DIM_OBJS) THEN
2084 -- each Dimension/Dimension Object combination split that and call API individually.
2085 l_pos_value := INSTR(l_Short_Name, '+');
2086 IF (l_pos_value > 0) THEN
2087 l_dim_sname := TRIM(SUBSTR(l_Short_Name, 1, l_pos_value - 1));
2088 l_dim_obj_sname := TRIM(SUBSTR(l_Short_Name, l_pos_value + 1));
2089 l_Entity_Name := null;
2090 Enable_Dimension_Entity (
2091 p_Entity_Type => c_DIMENSION
2092 , p_Entity_Short_Name => l_dim_sname
2093 , p_Entity_Action_Type => p_Entity_Action_Type
2094 , p_Entity_Name => null
2095 , x_Return_Status => x_Return_Status
2096 , x_Msg_Count => x_Msg_Count
2097 , x_Msg_Data => x_Msg_Data
2098 );
2099 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2101 END IF;
2102
2103 l_Entity_Name := null;
2104 Enable_Dimension_Entity (
2105 p_Entity_Type => c_DIMENSION_OBJECT
2106 , p_Entity_Short_Name => l_dim_obj_sname
2107 , p_Entity_Action_Type => p_Entity_Action_Type
2108 , p_Entity_Name => null
2109 , x_Return_Status => x_Return_Status
2110 , x_Msg_Count => x_Msg_Count
2111 , x_Msg_Data => x_Msg_Data
2112 );
2113 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2115 END IF;
2116 END IF;
2117
2118 ELSE
2119 -- fetch the entity name.
2120 l_Entity_Name := null;
2121 Enable_Dimension_Entity (
2122 p_Entity_Type => p_Entity_Type
2123 , p_Entity_Short_Name => l_Short_Name
2124 , p_Entity_Action_Type => p_Entity_Action_Type
2125 , p_Entity_Name => null
2126 , x_Return_Status => x_Return_Status
2127 , x_Msg_Count => x_Msg_Count
2128 , x_Msg_Data => x_Msg_Data
2129 );
2130 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2132 END IF;
2133 END IF;
2134 END LOOP;
2135
2136
2137 EXCEPTION
2138 WHEN OTHERS THEN
2139 IF (x_msg_data IS NULL) THEN
2140 FND_MSG_PUB.Count_And_Get
2141 ( p_encoded => FND_API.G_FALSE
2142 , p_count => x_msg_count
2143 , p_data => x_msg_data
2144 );
2145 END IF;
2146 x_Return_Status := FND_API.G_RET_STS_ERROR;
2147 END Enable_Dimensions_Entity;
2148
2149 FUNCTION Get_Name(
2150 p_Entity_Type IN VARCHAR2
2151 , p_Entity_Short_Name IN VARCHAR2
2152 ) RETURN VARCHAR IS
2153 l_name VARCHAR2(400);
2154 BEGIN
2155 IF(p_Entity_Type = c_DIMENSION_OBJECT) THEN
2156 SELECT NAME
2157 INTO l_name
2158 FROM BSC_SYS_DIM_LEVELS_VL
2159 WHERE SHORT_NAME = p_Entity_Short_Name;
2160 ELSE
2161 SELECT NAME
2162 INTO l_name
2163 FROM BSC_SYS_DIM_GROUPS_VL
2164 WHERE SHORT_NAME = p_Entity_Short_Name;
2165 END IF;
2166 RETURN l_name;
2167 END;
2168
2169
2170 /****************************************************************************************************
2171 This functions retuns error message, for a given dimension short_name if:
2172 1. Dimension is Autogenerated Dimension (Report Designer).
2173 2. If Dimension is import Dimension (created while importing a BIS report in BSC)
2174 3. Dimension is Weighted Report Dimension (Report Designer).
2175 else returns null.
2176 ****************************************************************************************************/
2177 FUNCTION Is_Internal_Dim(p_Short_Name IN VARCHAR2)
2178 RETURN VARCHAR2 IS
2179 l_Count NUMBER;
2180 l_Msg VARCHAR2(80);
2181 BEGIN
2182 l_Msg := Is_Internal_AG_Dim(p_Short_Name);
2183 IF (l_Msg IS NOT NULL) THEN
2184 RETURN l_Msg;
2185 END IF;
2186
2187 l_Msg := Is_Internal_BIS_Import_Dim(p_Short_Name);
2188 IF (l_Msg IS NOT NULL) THEN
2189 RETURN l_Msg;
2190 END IF;
2191
2192 -- Condition for Dimension Group created/used in Weighted Report or VB Report.
2193 l_Msg := Is_Internal_WKPI_Dim(p_Short_Name);
2194 IF (l_Msg IS NOT NULL) THEN
2195 RETURN l_Msg;
2196 END IF;
2197
2198 l_Msg := Is_Internal_VB_Dim(p_Short_Name);
2199 IF (l_Msg IS NOT NULL) THEN
2200 RETURN l_Msg;
2201 END IF;
2202
2203 RETURN NULL;
2204 END Is_Internal_Dim;
2205
2206
2207 /****************************************************************************************************
2208 The following three apis are taken from Is_Internal_Dim. They are required for fixes of bug#4602231
2209 where the Dimension LOV window will only call Is_Internal_AG_Dim and Is_Internal_BIS_Import_Dim
2210 to boost LOV performance. Is_Internal_WKPI_Dim is causing performance issue since it queries
2211 ak_regions without using indexed columns.
2212 ****************************************************************************************************/
2213 FUNCTION Is_Internal_AG_Dim(p_Short_Name IN VARCHAR2)
2214 RETURN VARCHAR2 IS
2215 l_Count NUMBER;
2216 BEGIN
2217 SELECT COUNT(1) INTO l_Count
2218 FROM BSC_SYS_DIM_GROUPS_VL B
2219 , BSC_KPIS_B K
2220 , BSC_KPI_DIM_GROUPS G
2221 WHERE B.SHORT_NAME = p_Short_Name
2222 AND K.SHORT_NAME = B.SHORT_NAME
2223 AND G.INDICATOR = K.INDICATOR;
2224
2225 IF (l_Count <> 0) THEN
2226 RETURN 'BIS_DIM_ASSIGN_AGREPORT';
2227 END IF;
2228
2229 RETURN null;
2230 END Is_Internal_AG_Dim;
2231
2232
2233 FUNCTION Is_Internal_BIS_Import_Dim(p_Short_Name IN VARCHAR2)
2234 RETURN VARCHAR2 IS
2235 l_Count NUMBER;
2236 BEGIN
2237 SELECT COUNT(1)
2238 INTO l_Count
2239 FROM BSC_SYS_DIM_GROUPS_VL d,
2240 BSC_KPI_DIM_GROUPS k
2241 WHERE d.DIM_GROUP_ID = k.DIM_GROUP_ID
2242 AND BSC_BIS_DIMENSION_PUB.Get_Dimension_Source(d.SHORT_NAME)=BSC_UTILITY.c_PMF
2243 AND d.SHORT_NAME=p_Short_Name;
2244
2245 IF (l_Count <> 0) THEN
2246 RETURN 'BIS_DIM_IMPORT_DIM';
2247 END IF;
2248
2249 RETURN null;
2250 END Is_Internal_BIS_Import_Dim;
2251
2252
2253 FUNCTION Is_Internal_WKPI_Dim(p_Short_Name IN VARCHAR2)
2254 RETURN VARCHAR2 IS
2255 l_Count NUMBER;
2256 BEGIN
2257 -- Condition for Dimension Group created/used in Weighted Report.
2258 SELECT COUNT(1)
2259 INTO l_Count
2260 FROM ak_regions
2261 WHERE attribute_category = BSC_UTILITY.C_ATTRIBUTE_CATEGORY
2262 AND attribute10 = BSC_UTILITY.C_REPORT_TYPE_MDS
2263 AND attribute12 = p_Short_Name;
2264
2265 IF (l_Count <> 0) THEN
2266 RETURN 'BIS_DIM_ASSIGN_AGREPORT';
2267 END IF;
2268
2269 RETURN NULL;
2270 END Is_Internal_WKPI_Dim;
2271
2272 FUNCTION Is_Internal_VB_Dim(p_Short_Name IN VARCHAR2)
2273 RETURN VARCHAR2 IS
2274 l_Count NUMBER;
2275 BEGIN
2276 -- Condition for Dimension Group created/used in Table Based Report.
2277 SELECT COUNT(1)
2278 INTO l_Count
2279 FROM ak_regions
2280 WHERE attribute_category = BSC_UTILITY.C_ATTRIBUTE_CATEGORY
2281 AND attribute10 = BSC_UTILITY.C_REPORT_TYPE_TABLE
2282 AND attribute12 = p_Short_Name;
2283
2284 IF (l_Count <> 0) THEN
2285 RETURN 'BIS_DIM_ASSIGN_TABLEREPORT';
2286 END IF;
2287
2288 SELECT COUNT(1)
2289 INTO l_Count
2290 FROM ak_regions
2291 WHERE attribute_category = BSC_UTILITY.C_ATTRIBUTE_CATEGORY
2292 AND attribute10 IS NULL
2293 AND attribute12 = p_Short_Name;
2294
2295 IF (l_Count <> 0) THEN
2296 RETURN 'BIS_DIM_ASSIGN_VIEWREPORT';
2297 END IF;
2298
2299 RETURN NULL;
2300 END Is_Internal_VB_Dim;
2301
2302
2303 PROCEDURE Enable_Dimension_Entity (
2304 p_Entity_Type IN VARCHAR2
2305 , p_Entity_Short_Name IN VARCHAR2
2306 , p_Entity_Action_Type IN VARCHAR2
2307 , p_Entity_Name IN VARCHAR2
2308 , x_Return_Status OUT NOCOPY VARCHAR2
2309 , x_Msg_Count OUT NOCOPY NUMBER
2310 , x_Msg_Data OUT NOCOPY VARCHAR2
2311 ) IS
2312 l_Count NUMBER;
2313 l_Sql VARCHAR2(2000);
2314 l_tmp_EntityName VARCHAR2(4000);
2315 l_Return_Msg VARCHAR2(30);
2316 BEGIN
2317 -- pick the name if it is null.
2318 FND_MSG_PUB.Initialize;
2319 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
2320
2321 IF ((p_Entity_Short_Name IS NULL) OR (BIS_UTILITIES_PUB.Enable_Generated_Source_Report = FND_API.G_FALSE)) THEN
2322 RETURN;
2323 END IF;
2324
2325 IF(p_Entity_Type = c_DIMENSION_OBJECT) THEN
2326
2327 -- Check if it is associated to a Periodicity
2328 l_Sql := ' SELECT COUNT(1) FROM BIS_LEVELS L, BSC_SYS_PERIODICITIES P '
2329 || ' WHERE L.SHORT_NAME = :1 '
2330 || ' AND P.SHORT_NAME = L.SHORT_NAME ';
2331
2332 EXECUTE IMMEDIATE l_Sql INTO l_Count USING p_Entity_Short_Name;
2333
2334 IF (l_Count <> 0) THEN
2335 IF (p_Entity_Name IS NULL) THEN
2336 l_tmp_EntityName := Get_Name(p_Entity_Type, p_Entity_Short_Name);
2337 ELSE
2338 l_tmp_EntityName := p_Entity_Name;
2339 END IF;
2340 FND_MESSAGE.SET_NAME('BIS','BIS_DIMOBJ_ASSIGN_PERIODS');
2341 FND_MESSAGE.SET_TOKEN('DIMOBJ', l_tmp_EntityName);
2342 FND_MSG_PUB.ADD;
2343 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2344 END IF;
2345
2346 ELSIF (p_Entity_Type = c_DIMENSION) THEN
2347 l_Sql := ' SELECT COUNT(1) FROM BIS_DIMENSIONS D, BSC_SYS_CALENDARS_B C '
2348 ||' WHERE D.SHORT_NAME = :1 AND C.SHORT_NAME = D.SHORT_NAME ';
2349
2350 EXECUTE IMMEDIATE l_Sql INTO l_Count USING p_Entity_Short_Name;
2351
2352 IF (l_Count <> 0) THEN
2353 IF (p_Entity_Name IS NULL) THEN
2354 l_tmp_EntityName := Get_Name(p_Entity_Type, p_Entity_Short_Name);
2355 ELSE
2356 l_tmp_EntityName := p_Entity_Name;
2357 END IF;
2358 FND_MESSAGE.SET_NAME('BIS','BIS_DIM_ASSIGN_PERIODS');
2359 FND_MESSAGE.SET_TOKEN('DIM', l_tmp_EntityName);
2360 FND_MSG_PUB.ADD;
2361 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2362 END IF;
2363
2364 l_Return_Msg := Is_Internal_Dim(p_Entity_Short_Name);
2365
2366 IF (l_Return_Msg IS NOT NULL) THEN
2367 IF (p_Entity_Name IS NULL) THEN
2368 l_tmp_EntityName := Get_Name(p_Entity_Type, p_Entity_Short_Name);
2369 ELSE
2370 l_tmp_EntityName := p_Entity_Name;
2371 END IF;
2372 FND_MESSAGE.SET_NAME('BIS',l_Return_Msg);
2373 FND_MESSAGE.SET_TOKEN('DIM', l_tmp_EntityName);
2374 FND_MSG_PUB.ADD;
2375 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2376 END IF;
2377
2378 END IF;
2379
2380 EXCEPTION
2381 WHEN OTHERS THEN
2382 IF (x_msg_data IS NULL) THEN
2383 FND_MSG_PUB.Count_And_Get
2384 ( p_encoded => FND_API.G_FALSE
2385 , p_count => x_msg_count
2386 , p_data => x_msg_data
2387 );
2388 END IF;
2389 x_Return_Status := FND_API.G_RET_STS_ERROR;
2390 END Enable_Dimension_Entity;
2391
2392 /*
2393 Verify whether the comma(,) separated dimensions are not internal
2394 dimensions created for WA report.
2395 */
2396 PROCEDURE Check_Weighted_Dimension (
2397 p_Dim_Short_Names IN VARCHAR2
2398 , x_Return_Status OUT NOCOPY VARCHAR2
2399 , x_Msg_Count OUT NOCOPY NUMBER
2400 , x_Msg_Data OUT NOCOPY VARCHAR2
2401 ) IS
2402 l_Entity_Name VARCHAR2(300);
2403 l_Short_Name VARCHAR2(100);
2404 l_temp_snames VARCHAR2(2000);
2405 l_Return_Msg VARCHAR2(100);
2406 BEGIN
2407 FND_MSG_PUB.Initialize;
2408
2409 l_temp_snames := p_Dim_Short_Names;
2410 WHILE (Is_More(l_temp_snames, l_Short_Name)) LOOP
2411 l_Return_Msg := Is_Internal_WKPI_Dim(l_Short_Name);
2412 IF (l_Return_Msg IS NOT NULL) THEN
2413 l_Entity_Name := Get_Name(c_DIMENSION, l_Short_Name);
2414 FND_MESSAGE.SET_NAME('BIS',l_Return_Msg);
2415 FND_MESSAGE.SET_TOKEN('DIM', l_Entity_Name);
2416 FND_MSG_PUB.ADD;
2417 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2418 END IF;
2419 END LOOP;
2420
2421 EXCEPTION
2422 WHEN OTHERS THEN
2423 IF (x_msg_data IS NULL) THEN
2424 FND_MSG_PUB.Count_And_Get
2425 ( p_encoded => FND_API.G_FALSE
2426 , p_count => x_msg_count
2427 , p_data => x_msg_data
2428 );
2429 END IF;
2430 x_Return_Status := FND_API.G_RET_STS_ERROR;
2431 END Check_Weighted_Dimension;
2432
2433
2434 FUNCTION Is_More
2435 ( p_comma_sep_values IN OUT NOCOPY VARCHAR2
2436 , x_value OUT NOCOPY VARCHAR2
2437 ) RETURN BOOLEAN
2438 IS
2439 l_pos_values NUMBER;
2440 BEGIN
2441 IF (p_comma_sep_values IS NOT NULL) THEN
2442 l_pos_values := INSTR(p_comma_sep_values, ',');
2443 IF (l_pos_values > 0) THEN
2444 x_value := TRIM(SUBSTR(p_comma_sep_values, 1, l_pos_values - 1));
2445 p_comma_sep_values := TRIM(SUBSTR(p_comma_sep_values, l_pos_values + 1));
2446 ELSE
2447 x_value := TRIM(p_comma_sep_values);
2448 p_comma_sep_values := NULL;
2449 END IF;
2450 RETURN TRUE;
2451 ELSE
2452 RETURN FALSE;
2453 END IF;
2454 END Is_More;
2455 /****************************************************************************************************
2456 This functions returns an unique time based short name .
2457 It Prefixes the word based on type of the object sent in parameter p_Object_Type
2458 ****************************************************************************************************/
2459 -- Bug#5034549; Added ABS() to DBMS_UTILITY.GET_TIME, since it can return negative value
2460 FUNCTION Get_Unique_Sht_Name_By_Obj_Typ(p_Object_Type IN VARCHAR2)
2461 RETURN VARCHAR2
2462 IS
2463 l_Return_Short_Name VARCHAR2(30);
2464 BEGIN
2465 IF(p_Object_Type = bsc_utility.c_BSC_MEASURE) THEN
2466 l_Return_Short_Name := bsc_utility.c_BSC_MEASURE_SHORT_NAME||TO_CHAR(SYSDATE,'J')||ABS(DBMS_UTILITY.GET_TIME);
2467 END IF;
2468 RETURN l_Return_Short_Name;
2469 EXCEPTION
2470 WHEN OTHERS THEN
2471 RETURN NULL;
2472 END Get_Unique_Sht_Name_By_Obj_Typ;
2473 /****************************************************************************************************/
2474
2475 FUNCTION Is_Internal_Dimension(p_Short_Name IN VARCHAR2)
2476 RETURN VARCHAR2 IS
2477 l_Return_Msg VARCHAR2(30);
2478 BEGIN
2479 l_Return_Msg := Is_Internal_Dim(p_Short_Name);
2480 IF (l_Return_Msg IS NULL) THEN
2481 RETURN FND_API.G_FALSE;
2482 ELSE
2483 RETURN FND_API.G_TRUE;
2484 END IF;
2485
2486 EXCEPTION
2487 WHEN OTHERS THEN
2488 RETURN FND_API.G_TRUE;
2489 END Is_Internal_Dimension;
2490
2491
2492 /****************************************************************************************************
2493 Append_Report_List appends report_name to regions list,
2494 and return true if added count reaches maximum limit of 10
2495 ****************************************************************************************************/
2496
2497 FUNCTION Append_Report_List(
2498 p_Report_Name IN VARCHAR2
2499 , p_count IN NUMBER
2500 , p_Regions IN OUT NOCOPY VARCHAR2
2501 ) RETURN BOOLEAN IS
2502 BEGIN
2503 IF (p_Regions IS NULL) THEN
2504 p_Regions := p_Report_Name;
2505 ELSE
2506 p_Regions := p_Regions ||', '||p_Report_Name;
2507 END IF;
2508 IF (p_count >= 10) THEN
2509 p_Regions := p_Regions || ' ...';
2510 RETURN TRUE;
2511 END IF;
2512
2513 RETURN FALSE;
2514 END Append_Report_List;
2515
2516 /****************************************************************************************************
2517 This API takes a comman separated list of dimension objects and returns the region Codes of all the
2518 reports containing these dimension objects
2519 p_Short_Names : Comma separated list of the dimension objects
2520 x_region_codes: Table of region Codes containing the above dimension objects
2521 ****************************************************************************************************/
2522 PROCEDURE Is_Dim_Obj_In_AKReport(
2523 p_Short_Names IN VARCHAR2
2524 , x_region_codes OUT NOCOPY FND_TABLE_OF_VARCHAR2_30
2525 , x_Return_Status OUT NOCOPY VARCHAR2
2526 , x_Msg_Count OUT NOCOPY NUMBER
2527 , x_Msg_Data OUT NOCOPY VARCHAR2
2528 ) IS
2529 l_short_names VARCHAR2(32000);
2530 l_sql VARCHAR2(32000);
2531 l_attr2_sql VARCHAR2(32000);
2532 l_region_sht_name AK_REGIONS.REGION_CODE%TYPE;
2533 l_dim_obj_sht_name BIS_LEVELS.SHORT_NAME%TYPE;
2534 l_count NUMBER := 0;
2535 TYPE ref_cur IS REF CURSOR;
2536 c_regions_dim_obj ref_cur;
2537 BEGIN
2538 FND_MSG_PUB.Initialize;
2539 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
2540 IF (p_Short_Names IS NOT NULL) THEN
2541 x_region_codes := FND_TABLE_OF_VARCHAR2_30();
2542 l_sql := 'SELECT DISTINCT REGION_CODE FROM ak_region_items WHERE node_query_flag = ''Y'' AND node_display_flag = ''N'' AND SUBSTR(attribute2,INSTR(attribute2,''+'') +1 ,LENGTH(attribute2)) IN (' ;
2543 l_short_names := p_Short_Names;
2544 WHILE (BSC_UTILITY.is_more( l_short_names,l_dim_obj_sht_name)) LOOP
2545 l_attr2_sql := l_attr2_sql || '''' || l_dim_obj_sht_name || ''',' ;
2546 END LOOP;
2547 IF(l_attr2_sql IS NOT NULL) THEN
2548 --Remove the additional comma at the end
2549 l_attr2_sql := SUBSTR(l_attr2_sql,0,LENGTH(l_attr2_sql) - 1);
2550 l_sql := l_sql || l_attr2_sql || ')';
2551 OPEN c_regions_dim_obj FOR l_sql;
2552 LOOP
2553 FETCH c_regions_dim_obj INTO l_region_sht_name;
2554 EXIT WHEN c_regions_dim_obj%NOTFOUND;
2555 x_region_codes.extend(l_count+1);
2556 x_region_codes(l_count+1) := l_region_sht_name;
2557 l_count := l_count + 1;
2558 END LOOP;
2559 CLOSE c_regions_dim_obj;
2560 END IF;
2561 END IF;
2562 EXCEPTION
2563 WHEN OTHERS THEN
2564 IF (x_msg_data IS NULL) THEN
2565 FND_MSG_PUB.Count_And_Get
2566 ( p_encoded => FND_API.G_FALSE
2567 , p_count => x_msg_count
2568 , p_data => x_msg_data
2569 );
2570 END IF;
2571 x_Return_Status := FND_API.G_RET_STS_ERROR;
2572 END Is_Dim_Obj_In_AKReport;
2573
2574 /****************************************************************************************************
2575 Checkes if Report a Dimension or Dimension+Dimension Object is used in a Report.
2576 p_Short_Name can have values 'DIMENSION' or 'MIXED_DIM_OBJS'
2577 p_Short_name: For DIMENSION- Dimension short name should be passed.
2578 For MIXED_DIM_OBJS- <Dim shortname>+<Dim Object ShortName> should be passed.
2579 ****************************************************************************************************/
2580 FUNCTION Is_Dim_In_AKReport(
2581 p_Short_Name IN VARCHAR2
2582 , p_Entity_Type IN VARCHAR2 := c_MIXED_DIM_OBJS
2583 )
2584 RETURN VARCHAR2 IS
2585 l_regions VARCHAR2(32000);
2586 l_region VARCHAR2(200);
2587 l_count NUMBER;
2588 l_report_name VARCHAR2(100);
2589
2590 CURSOR c_regions_dim IS
2591 SELECT DISTINCT REGION_CODE
2592 FROM ak_region_items
2593 WHERE attribute2 LIKE p_Short_name ||'+%'
2594 AND attribute1 IN ('VIEWBY PARAMETER', 'DIMENSION LEVEL', 'DIM LEVEL SINGLE VALUE', 'HIDE VIEW BY DIMENSION');
2595
2596 CURSOR c_regions_dim_grp IS
2597 SELECT region_code
2598 FROM ak_regions
2599 WHERE attribute12 = p_Short_Name;
2600
2601 CURSOR c_regions_dim_obj IS
2602 SELECT DISTINCT REGION_CODE
2603 FROM ak_region_items
2604 WHERE attribute2 = p_Short_name
2605 AND attribute1 IN ('VIEWBY PARAMETER', 'DIMENSION LEVEL', 'DIM LEVEL SINGLE VALUE', 'HIDE VIEW BY DIMENSION');
2606 BEGIN
2607
2608 l_count := 1;
2609 IF(p_Entity_Type = c_DIMENSION) THEN
2610 FOR l_regions_val IN c_regions_dim LOOP
2611 l_report_name := Get_Report_Name(l_regions_val.REGION_CODE);
2612 IF(Append_Report_List(l_report_name, l_count, l_regions)) THEN
2613 EXIT;
2614 END IF;
2615 l_count := l_count + 1;
2616 END LOOP;
2617
2618 IF (l_count < 10) THEN
2619 FOR l_regions_dim_grp_val IN c_regions_dim_grp LOOP
2620 l_report_name := Get_Report_Name(l_regions_dim_grp_val.REGION_CODE);
2621
2622 IF(Append_Report_List(l_report_name, l_count, l_regions)) THEN
2623 EXIT;
2624 END IF;
2625 l_count := l_count + 1;
2626 END LOOP;
2627 END IF;
2628 ELSIF (p_Entity_Type = c_MIXED_DIM_OBJS) THEN
2629 FOR l_regions_val IN c_regions_dim_obj LOOP
2630 l_report_name := Get_Report_Name(l_regions_val.REGION_CODE);
2631 IF(Append_Report_List(l_report_name, l_count, l_regions)) THEN
2632 EXIT;
2633 END IF;
2634 l_count := l_count + 1;
2635 END LOOP;
2636 END IF;
2637
2638 RETURN l_regions;
2639 END Is_Dim_In_AKReport;
2640
2641
2642 /****************************************************************************************************
2643 Returns Report Name for given region_code.
2644 First Checks if there is a form functions pointing to this Report
2645 (Will pick first in case of multiple form functions)
2646 Otherwise returns the ak_region name.
2647 ****************************************************************************************************/
2648 FUNCTION Get_Report_Name(
2649 p_Region_Code IN VARCHAR2
2650 )
2651 RETURN VARCHAR2 IS
2652 report_name fnd_form_functions_vl.User_Function_Name%TYPE;
2653
2654 CURSOR c_function_name IS
2655 SELECT user_function_name FROM fnd_form_functions_vl
2656 WHERE parameters like '%pRegionCode='|| p_Region_Code ||'%'
2657 AND rownum < 2;
2658
2659 CURSOR c_region_name IS
2660 SELECT name FROM ak_regions_vl
2661 WHERE region_code = p_Region_Code;
2662
2663 BEGIN
2664 FOR c_function_name_val IN c_function_name LOOP
2665 report_name := c_function_name_val.user_function_name;
2666 END LOOP;
2667
2668 IF (report_name IS NULL) THEN
2669 FOR c_region_name_val IN c_region_name LOOP
2670 report_name := c_region_name_val.name;
2671 END LOOP;
2672 END IF;
2673
2674 return report_name;
2675 END Get_Report_Name;
2676
2677 -- added for Bug#4563456
2678 FUNCTION Get_Responsibility_Key
2679 RETURN VARCHAR2 IS
2680 l_Resp_Key FND_RESPONSIBILITY.RESPONSIBILITY_KEY%TYPE;
2681 BEGIN
2682 SELECT F.RESPONSIBILITY_KEY INTO l_Resp_Key
2683 FROM FND_RESPONSIBILITY F
2684 WHERE F.RESPONSIBILITY_ID = FND_GLOBAL.RESP_ID
2685 AND F.APPLICATION_ID = FND_GLOBAL.RESP_APPL_ID;
2686
2687 RETURN l_Resp_Key;
2688
2689 EXCEPTION
2690 WHEN OTHERS THEN
2691 RETURN NULL;
2692 END Get_Responsibility_Key;
2693
2694 -- added for Bug#4599432
2695 FUNCTION Is_Measure_Seeded (p_Short_Name IN VARCHAR2)
2696 RETURN VARCHAR2 IS
2697 l_Count NUMBER;
2698 BEGIN
2699 SELECT COUNT(1) INTO l_Count
2700 FROM BIS_INDICATORS B
2701 WHERE B.SHORT_NAME = p_Short_Name
2702 AND BIS_UTIL.is_Seeded(B.CREATED_BY,'Y','N') = 'Y';
2703
2704 IF (l_Count <> 0 AND NOT BIS_UTIL.Is_Internal_Customer) THEN
2705 RETURN FND_API.G_TRUE;
2706 END IF;
2707
2708 RETURN FND_API.G_FALSE;
2709
2710 EXCEPTION
2711 WHEN OTHERS THEN
2712 RETURN FND_API.G_TRUE;
2713 END Is_Measure_Seeded;
2714
2715
2716 /****************************************************************
2717 Name : Get_User_Time
2718 Input : p_current_user_time (Current User Time)
2719 p_date_format (Format in which the date is to be displayed)
2720 Output : out time in the desired format as specified by the user.
2721 Creation date : 05-OCT-2005
2722 Created By : ashankar
2723 /****************************************************************/
2724 FUNCTION Get_User_Time
2725 (
2726 p_current_user_time IN DATE
2727 , p_date_format IN VARCHAR2
2728 ) RETURN VARCHAR2 IS
2729 BEGIN
2730
2731 RETURN TO_CHAR(fnd_timezones_pvt.adjust_datetime
2732 (
2733 date_time => p_current_user_time
2734 , from_tz => fnd_timezones.get_server_timezone_code
2735 , to_tz => fnd_timezones.get_client_timezone_code
2736 ),p_date_format
2737 );
2738 EXCEPTION
2739 WHEN OTHERS THEN
2740 RETURN NULL;
2741 END Get_User_Time;
2742
2743
2744 /****************************************************************************************************
2745
2746 Implementation of SQL Parser Starts from here
2747
2748
2749 Current Implementation Algorithm
2750 --------------------------------
2751
2752 STEP#1: Validate the PL/SQL Procedure passed down from the API
2753 STEP#1A: Existential check
2754 STEP#1B: Check if the package has both spec/body
2755 STEP#1C: Check if the package body/speck has any errors
2756
2757 STEP#2: Obtain the parameter to pass to the PL/SQL package.
2758
2759 STEP#3: Validate and parse the function input parameters
2760
2761 STEP#4: Call the Report PL/SQL Function
2762
2763 STEP#5: Validate default report query
2764 ****************************************************************************************************/
2765
2766 PROCEDURE Validate_Plsql_For_Report (
2767 p_Region_Code IN VARCHAR2
2768 , p_Region_Application_Id IN VARCHAR2
2769 , p_Plsql_Function IN VARCHAR2
2770 , p_Attribute_Code IN VARCHAR2
2771 , p_Attribute1 IN VARCHAR2
2772 , p_Attribute2 IN VARCHAR2
2773 , p_Attribute3 IN VARCHAR2
2774 , p_Default_Values IN VARCHAR2
2775 , x_Return_Status OUT NOCOPY VARCHAR2
2776 , x_Msg_Count OUT NOCOPY NUMBER
2777 , x_Msg_Data OUT NOCOPY VARCHAR2
2778 ) IS
2779
2780 l_Function_Parameter VARCHAR2(30);
2781 l_Parameter VARCHAR2(30);
2782 l_Parameter_1 VARCHAR2(30);
2783 l_Parameter_2 VARCHAR2(30);
2784 l_Parameter_3 VARCHAR2(30);
2785 l_Parameter_1_type VARCHAR2(30);
2786 l_Parameter_2_type VARCHAR2(30);
2787 l_Parameter_3_type VARCHAR2(30);
2788 l_Parameter_1_var VARCHAR2(30);
2789 l_Parameter_2_var VARCHAR2(30);
2790 l_Parameter_3_var VARCHAR2(30);
2791
2792 l_Custom_Sql VARCHAR2(3000);
2793 l_Custom_Output BIS_QUERY_ATTRIBUTES_TBL;
2794
2795 l_View_Cols VARCHAR2(2000);
2796 l_Custom_Cols VARCHAR2(2000);
2797 BEGIN
2798 -- STEP#1 of algorithm
2799 BSC_UTILITY.Validate_PLSQL (
2800 p_Plsql_Function => p_Plsql_Function
2801 , x_Return_Status => x_Return_Status
2802 , x_Msg_Count => x_Msg_Count
2803 , x_Msg_Data => x_Msg_Data
2804 );
2805 IF (x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2807 END IF;
2808
2809 /*
2810 -- STEP#2 of algorithm
2811 l_Function_Parameter := Get_Plsql_Parameter(p_Plsql_Function);
2812
2813 IF (l_Function_Parameter IS NULL) THEN
2814 -- BIS_PLSQL_INVALID_PARAMETERS
2815 -- The PL/SQL Function provided has been defined with invalid parameters
2816 FND_MESSAGE.SET_NAME('BIS','BIS_PLSQL_INVALID_PARAMETERS');
2817 FND_MSG_PUB.ADD;
2818 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2819 END IF;
2820 */
2821
2822 -- STEP#2 of algorithm
2823 BSC_UTILITY.Get_Plsql_Parameters (
2824 p_Report_Function => p_Plsql_Function
2825 , x_Parameter_1 => l_Parameter_1
2826 , x_Parameter_2 => l_Parameter_2
2827 , x_Parameter_3 => l_Parameter_3
2828 , x_Parameter_1_type => l_Parameter_1_type
2829 , x_Parameter_2_type => l_Parameter_2_type
2830 , x_Parameter_3_type => l_Parameter_3_type
2831 , x_Parameter_1_var => l_Parameter_1_var
2832 , x_Parameter_2_var => l_Parameter_2_var
2833 , x_Parameter_3_var => l_Parameter_3_var
2834 );
2835 IF ((l_Parameter_1 IS NULL) AND
2836 (l_Parameter_2 IS NULL) AND
2837 (l_Parameter_3 IS NULL)) THEN
2838 FND_MESSAGE.SET_NAME('BIS','BIS_PLSQL_INVALID_FUNC_NAME');
2839 FND_MSG_PUB.ADD;
2840 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2841 END IF;
2842
2843 /*
2844 Perform validations here from STEP#2
2845 */
2846
2847 -- STEP#3 of Algorithm
2848 -- l_Custom_Cols has the comma seperated SELECT CLAUSE required
2849 -- and sufficient for the Report region.
2850 BSC_UTILITY.Obtain_Report_Query (
2851 p_Region_Code => p_Region_Code
2852 , p_Region_Application_Id => p_Region_Application_Id
2853 , p_Plsql_Function => p_Plsql_Function
2854 , p_Attribute_Code => p_Attribute_Code
2855 , p_Attribute1 => p_Attribute1
2856 , p_Attribute2 => p_Attribute2
2857 , p_Attribute3 => p_Attribute3
2858 , p_Default_Values => p_Default_Values
2859 , x_Custom_Sql => l_Custom_Sql
2860 , x_Custom_Output => l_Custom_Output
2861 , x_Custom_Columns => l_Custom_Cols
2862 , x_Return_Status => x_Return_Status
2863 , x_Msg_Count => x_Msg_Count
2864 , x_Msg_Data => x_Msg_Data
2865 );
2866 -- BIS_ERR_OBTAIN_RPT_QUERY - There was an error when extracting Report Query using the Default
2867 -- parameter values. Please verify if your PL/SQL API returns a SQL for the default parameters.
2868 IF ((x_Return_Status <> FND_API.G_RET_STS_SUCCESS) OR (l_Custom_Sql IS NULL)) THEN
2869 FND_MESSAGE.SET_NAME('BIS','BIS_ERR_OBTAIN_RPT_QUERY');
2870 FND_MSG_PUB.ADD;
2871 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2872 END IF;
2873
2874 BSC_UTILITY.Validate_Sql_String (
2875 p_Sql_String => l_Custom_Sql
2876 , x_Columns => l_View_Cols
2877 , x_Return_Status => x_Return_Status
2878 , x_Msg_Count => x_Msg_Count
2879 , x_Msg_Data => x_Msg_Data
2880 );
2881 -- BIS_ERR_WITH_REPORT_SQL
2882 -- The Report Query returned from the PL/SQL procedure has the following
2883 -- error(s) : ERROR
2884 IF (x_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2885 FND_MESSAGE.SET_NAME('BIS','BIS_ERR_WITH_REPORT_SQL');
2886 FND_MESSAGE.SET_TOKEN('ERROR', x_Msg_Data);
2887 FND_MSG_PUB.ADD;
2888 x_Msg_Data := NULL;
2889 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2890 END IF;
2891
2892 l_Custom_Cols := Sort_String(l_Custom_Cols);
2893 l_View_Cols := Sort_String(l_View_Cols);
2894
2895 -- BIS_ERR_COLS_RPT_MISMATCH
2896 -- The columns in the report query returned from the PL/SQL Package does not match
2897 -- the number of columns required to run the report for default parameters. Please review the
2898 -- PL/SQL procedure.
2899 IF ((l_Custom_Cols IS NULL) OR (l_Custom_Cols <> l_View_Cols)) THEN
2900 FND_MESSAGE.SET_NAME('BIS','BIS_ERR_COLS_RPT_MISMATCH');
2901 FND_MSG_PUB.ADD;
2902 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2903 END IF;
2904
2905 -- Finally the PL/SQL is OK - return that the PL/SQL has passed the validation.
2906 IF ((x_Return_Status = FND_API.G_RET_STS_SUCCESS) OR (x_Msg_Data IS NULL)) THEN
2907 FND_MESSAGE.SET_NAME('BIS','BIS_PLSQL_PACKAGE_IS_VALID');
2908 FND_MSG_PUB.ADD;
2909 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2910 END IF;
2911
2912 EXCEPTION
2913 WHEN FND_API.G_EXC_ERROR THEN
2914 IF (x_msg_data IS NULL) THEN
2915 FND_MSG_PUB.Count_And_Get
2916 ( p_encoded => FND_API.G_FALSE
2917 , p_count => x_msg_count
2918 , p_data => x_msg_data
2919 );
2920 END IF;
2921 x_Return_Status := FND_API.G_RET_STS_ERROR;
2922 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2923 IF (x_msg_data IS NULL) THEN
2924 FND_MSG_PUB.Count_And_Get
2925 ( p_encoded => FND_API.G_FALSE
2926 , p_count => x_msg_count
2927 , p_data => x_msg_data
2928 );
2929 END IF;
2930 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2931 WHEN NO_DATA_FOUND THEN
2932 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2933 IF (x_msg_data IS NOT NULL) THEN
2934 x_msg_data := x_msg_data||' -> BSC_UTILITY.Validate_Plsql_For_Report ';
2935 ELSE
2936 x_msg_data := SQLERRM||' at BSC_UTILITY.Validate_Plsql_For_Report ';
2937 END IF;
2938 WHEN OTHERS THEN
2939 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2940 IF (x_msg_data IS NOT NULL) THEN
2941 x_msg_data := x_msg_data||' -> BSC_UTILITY.Validate_Plsql_For_Report ';
2942 ELSE
2943 x_msg_data := SQLERRM||' at BSC_UTILITY.Validate_Plsql_For_Report ';
2944 END IF;
2945 END Validate_Plsql_For_Report;
2946
2947
2948 -- This API returns the parameter being used for a PL/SQL API, which should have the one parameter
2949 -- and should take the type BIS_PMV_PAGE_PARAMETER_TBL
2950
2951 PROCEDURE Get_Plsql_Parameters (
2952 p_Report_Function IN VARCHAR2
2953 , x_Parameter_1 OUT NOCOPY VARCHAR2
2954 , x_Parameter_2 OUT NOCOPY VARCHAR2
2955 , x_Parameter_3 OUT NOCOPY VARCHAR2
2956 , x_Parameter_1_type OUT NOCOPY VARCHAR2
2957 , x_Parameter_2_type OUT NOCOPY VARCHAR2
2958 , x_Parameter_3_type OUT NOCOPY VARCHAR2
2959 , x_Parameter_1_var OUT NOCOPY VARCHAR2
2960 , x_Parameter_2_var OUT NOCOPY VARCHAR2
2961 , x_Parameter_3_var OUT NOCOPY VARCHAR2
2962 ) IS
2963 l_Function_Name VARCHAR2(30);
2964 l_Package_Name VARCHAR2(30);
2965 l_Temp_String1 ALL_SOURCE.TEXT%TYPE;
2966 l_Temp_String2 ALL_SOURCE.TEXT%TYPE;
2967
2968 l_Package_Specification VARCHAR2(32000);
2969
2970 l_Package_Token_Table BSC_UTILITY.Varchar_Tabletype;
2971 l_Package_Token_Count NUMBER;
2972
2973 l_Token_Count1 NUMBER;
2974 l_Token_Count2 NUMBER;
2975
2976 l_Parameter VARCHAR2(30);
2977 l_Parameter_1 VARCHAR2(30);
2978 l_Parameter_2 VARCHAR2(30);
2979 l_Parameter_3 VARCHAR2(30);
2980 l_Parameter_1_type VARCHAR2(30);
2981 l_Parameter_2_type VARCHAR2(30);
2982 l_Parameter_3_type VARCHAR2(30);
2983 l_Parameter_1_var VARCHAR2(30);
2984 l_Parameter_2_var VARCHAR2(30);
2985 l_Parameter_3_var VARCHAR2(30);
2986
2987 l_Index_Cnt NUMBER;
2988
2989 CURSOR c_Parse_Package_Spec IS
2990 SELECT A.LINE, A.TEXT
2991 FROM ALL_SOURCE A
2992 WHERE A.OWNER = BSC_APPS.get_user_schema(C_PACKAGE_OWNER)
2993 AND A.TYPE = C_PACKAGE_SPECIFICATION
2994 AND A.NAME = l_Package_Name;
2995 BEGIN
2996
2997 l_Package_Name := UPPER(SUBSTR(p_Report_Function, 1, INSTR(p_Report_Function, '.')-1));
2998 l_Function_Name := UPPER(SUBSTR(p_Report_Function, INSTR(p_Report_Function, '.')+1));
2999
3000 FOR cPPS IN c_Parse_Package_Spec LOOP
3001 l_Temp_String1 := UPPER(Remove_Repeating_Comma(TRANSLATE(cPPS.TEXT, ',.() +-*/;'||fnd_global.local_chr(10)||fnd_global.local_chr(9) ,',,,,,,,,,,,,')));
3002 --DBMS_OUTPUT.PUT_LINE (' l_Temp_String1 - ' || l_Temp_String1);
3003 l_Package_Specification := Remove_Repeating_Comma(l_Package_Specification ||','||l_Temp_String1);
3004 END LOOP;
3005
3006 --DBMS_OUTPUT.PUT_LINE (' l_Package_Name - ' || l_Package_Name);
3007 --DBMS_OUTPUT.PUT_LINE (' l_Function_Name - ' || l_Function_Name);
3008
3009 BSC_UTILITY.Parse_String
3010 (
3011 p_List => l_Package_Specification
3012 , p_Separator => ','
3013 , p_List_Data => l_Package_Token_Table
3014 , p_List_number => l_Package_Token_Count
3015 );
3016
3017 FOR i IN 1..l_Package_Token_Count LOOP
3018 --DBMS_OUTPUT.PUT_LINE (' l_Package_Token_Table - ' || l_Package_Token_Table(i));
3019
3020
3021 IF (l_Package_Token_Table(i) = C_PLSQL_TOKEN_PROCEDURE) THEN
3022 IF(l_Package_Token_Table(i+1) = UPPER(l_Function_Name)) THEN
3023 --l_Parameter := l_Package_Token_Table(i+2);
3024 -- Get the first parameter details
3025
3026 --DBMS_OUTPUT.PUT_LINE (' l_Package_Token_Table - ' || l_Package_Token_Table(i));
3027 --DBMS_OUTPUT.PUT_LINE (' l_Index_Cnt - ' || l_Index_Cnt);
3028
3029 l_Index_Cnt := 2;
3030 l_Parameter_1 := l_Package_Token_Table(i+l_Index_Cnt);
3031 l_Index_Cnt := l_Index_Cnt + 1; -- 3
3032 l_Parameter_1_type := l_Package_Token_Table(i+l_Index_Cnt);
3033 l_Index_Cnt := l_Index_Cnt + 1; -- 4
3034 l_Parameter_1_var := l_Package_Token_Table(i+l_Index_Cnt);
3035
3036 -- Get the second parameter details
3037 l_Index_Cnt := l_Index_Cnt + 1; -- 5
3038 l_Parameter_2 := l_Package_Token_Table(i+l_Index_Cnt);
3039 l_Index_Cnt := l_Index_Cnt + 1; -- 6
3040 l_Parameter_2_type := l_Package_Token_Table(i+l_Index_Cnt);
3041 IF (l_Package_Token_Table(i+l_Index_Cnt+1) = 'NOCOPY') THEN
3042 l_Parameter_2_type := l_Package_Token_Table(i+l_Index_Cnt);
3043 l_Index_Cnt := l_Index_Cnt + 1; -- ~7
3044 l_Parameter_2_type := l_Parameter_2_type || ' ' || l_Package_Token_Table(i+l_Index_Cnt);
3045 END IF;
3046 l_Index_Cnt := l_Index_Cnt + 1; -- ~ 7 || 8
3047 l_Parameter_2_var := l_Package_Token_Table(i+l_Index_Cnt);
3048
3049
3050 -- Get the third parameter details
3051 l_Index_Cnt := l_Index_Cnt + 1; -- 9
3052 l_Parameter_3 := l_Package_Token_Table(i+l_Index_Cnt);
3053 l_Index_Cnt := l_Index_Cnt + 1; -- 10
3054 l_Parameter_3_type := l_Package_Token_Table(i+l_Index_Cnt);
3055 IF (l_Package_Token_Table(i+l_Index_Cnt+1) = 'NOCOPY') THEN
3056 l_Parameter_3_type := l_Package_Token_Table(i+l_Index_Cnt) ;
3057 l_Index_Cnt := l_Index_Cnt + 1; -- ~11
3058 l_Parameter_3_type := l_Parameter_3_type || ' ' || l_Package_Token_Table(i+l_Index_Cnt);
3059 END IF;
3060 l_Index_Cnt := l_Index_Cnt + 1; -- ~ 11 || 12
3061 l_Parameter_3_var := l_Package_Token_Table(i+l_Index_Cnt);
3062
3063 x_Parameter_1 := l_Parameter_1;
3064 x_Parameter_2 := l_Parameter_2;
3065 x_Parameter_3 := l_Parameter_3;
3066 x_Parameter_1_type := l_Parameter_1_type;
3067 x_Parameter_2_type := l_Parameter_2_type;
3068 x_Parameter_3_type := l_Parameter_3_type;
3069 x_Parameter_1_var := l_Parameter_1_var;
3070 x_Parameter_2_var := l_Parameter_2_var;
3071 x_Parameter_3_var := l_Parameter_3_var;
3072 EXIT;
3073 END IF;
3074 END IF;
3075 END LOOP;
3076
3077 EXCEPTION
3078 WHEN OTHERS THEN
3079 NULL;
3080 END Get_Plsql_Parameters;
3081
3082
3083 -- this API trims all moving comma's to a single comma.
3084
3085 FUNCTION Remove_Repeating_Comma (
3086 p_String IN VARCHAR2
3087 ) RETURN VARCHAR2 IS
3088 l_Remove_Comma BOOLEAN;
3089 l_Return VARCHAR2(32000);
3090 l_Char VARCHAR2(1);
3091 BEGIN
3092
3093 l_Remove_Comma := FALSE;
3094
3095 FOR i IN 1..LENGTH(p_String) LOOP
3096 l_Char := SUBSTR(p_String, i, 1);
3097
3098 IF (NOT l_Remove_Comma) THEN
3099 l_Return := l_Return || l_Char;
3100 END IF;
3101
3102 IF (l_Char = ',') THEN
3103 l_Remove_Comma := TRUE;
3104 ELSE
3105 IF (l_Remove_Comma) THEN
3106 l_Return := l_Return || l_Char;
3107 END IF;
3108 l_Remove_Comma := FALSE;
3109 END IF;
3110 END LOOP;
3111
3112 RETURN l_Return;
3113
3114 EXCEPTION
3115 WHEN OTHERS THEN
3116 RETURN NULL;
3117 END Remove_Repeating_Comma;
3118
3119
3120 -- this API does an existential validation on the PL/SQL Package/function.
3121 PROCEDURE Validate_PLSQL (
3122 p_Plsql_Function IN VARCHAR2
3123 , x_Return_Status OUT NOCOPY VARCHAR2
3124 , x_Msg_Count OUT NOCOPY NUMBER
3125 , x_Msg_Data OUT NOCOPY VARCHAR2
3126 ) IS
3127 l_Function_Name VARCHAR2(30);
3128 l_Package_Name VARCHAR2(30);
3129 l_Count NUMBER;
3130 BEGIN
3131
3132 -- BIS_PLSQL_INVALID_FUNC_NAME
3133 -- The PL/SQL "Package_Name.Function_Name" specification is incorrect or does not exist.
3134 IF (INSTR(p_Plsql_Function, '.') = 0) THEN
3135 FND_MESSAGE.SET_NAME('BIS','BIS_PLSQL_INVALID_FUNC_NAME');
3136 FND_MSG_PUB.ADD;
3137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3138 END IF;
3139
3140 l_Package_Name := UPPER(SUBSTR(p_Plsql_Function, 1, INSTR(p_Plsql_Function, '.')-1));
3141 l_Function_Name := UPPER(SUBSTR(p_Plsql_Function, INSTR(p_Plsql_Function, '.')+1));
3142
3143 SELECT COUNT(1) INTO l_Count
3144 FROM ALL_SOURCE A
3145 WHERE A.TYPE = C_PACKAGE_SPECIFICATION
3146 AND A.OWNER = BSC_APPS.get_user_schema(C_PACKAGE_OWNER)
3147 AND A.NAME = l_Package_Name;
3148
3149 -- BIS_PLSQL_PKG_NOT_EXIST
3150 -- The PL/SQL procedure or function does not exist in the database
3151 IF (l_Count = 0) THEN
3152 FND_MESSAGE.SET_NAME('BIS','BIS_PLSQL_PKG_NOT_EXIST');
3153 FND_MSG_PUB.ADD;
3154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3155 END IF;
3156
3157 SELECT COUNT(1) INTO l_Count
3158 FROM USER_OBJECTS A
3159 WHERE A.OBJECT_NAME = l_Package_Name
3160 AND A.STATUS = C_PACKAGE_STATUS_INVALID;
3161
3162 -- BIS_PLSQL_PKG_NOT_EXIST
3163 -- The PL/SQL package has errors.
3164 IF (l_Count <> 0) THEN
3165 FND_MESSAGE.SET_NAME('BIS','BIS_PLSQL_PKG_HAS_ERRORS');
3166 FND_MSG_PUB.ADD;
3167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3168 END IF;
3169
3170 EXCEPTION
3171 WHEN FND_API.G_EXC_ERROR THEN
3172 IF (x_msg_data IS NULL) THEN
3173 FND_MSG_PUB.Count_And_Get
3174 ( p_encoded => FND_API.G_FALSE
3175 , p_count => x_msg_count
3176 , p_data => x_msg_data
3177 );
3178 END IF;
3179 x_Return_Status := FND_API.G_RET_STS_ERROR;
3180 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3181 IF (x_msg_data IS NULL) THEN
3182 FND_MSG_PUB.Count_And_Get
3183 ( p_encoded => FND_API.G_FALSE
3184 , p_count => x_msg_count
3185 , p_data => x_msg_data
3186 );
3187 END IF;
3188 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3189 WHEN NO_DATA_FOUND THEN
3190 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3191 IF (x_msg_data IS NOT NULL) THEN
3192 x_msg_data := x_msg_data||' -> BSC_UTILITY.Validate_PLSQL ';
3193 ELSE
3194 x_msg_data := SQLERRM||' at BSC_UTILITY.Validate_PLSQL ';
3195 END IF;
3196 WHEN OTHERS THEN
3197 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3198 IF (x_msg_data IS NOT NULL) THEN
3199 x_msg_data := x_msg_data||' -> BSC_UTILITY.Validate_PLSQL ';
3200 ELSE
3201 x_msg_data := SQLERRM||' at BSC_UTILITY.Validate_PLSQL ';
3202 END IF;
3203 END Validate_PLSQL;
3204
3205
3206 -- Get's the report query
3207 PROCEDURE Obtain_Report_Query (
3208 p_Region_Code IN VARCHAR2
3209 , p_Region_Application_Id IN VARCHAR2
3210 , p_Plsql_Function IN VARCHAR2
3211 , p_Attribute_Code IN VARCHAR2
3212 , p_Attribute1 IN VARCHAR2
3213 , p_Attribute2 IN VARCHAR2
3214 , p_Attribute3 IN VARCHAR2
3215 , p_Default_Values IN VARCHAR2
3216 , x_Custom_Sql OUT NOCOPY VARCHAR2
3217 , x_Custom_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
3218 , x_Custom_Columns OUT NOCOPY VARCHAR2
3219 , x_Return_Status OUT NOCOPY VARCHAR2
3220 , x_Msg_Count OUT NOCOPY NUMBER
3221 , x_Msg_Data OUT NOCOPY VARCHAR2
3222 ) IS
3223 l_Attribute_Code BSC_UTILITY.Varchar_Tabletype;
3224 l_Attribute1 BSC_UTILITY.Varchar_Tabletype;
3225 l_Attribute2 BSC_UTILITY.Varchar_Tabletype;
3226 l_Attribute3 BSC_UTILITY.Varchar_Tabletype;
3227 l_Default_Values BSC_UTILITY.Varchar_Tabletype;
3228
3229 l_Non_Time_Dimension_Type BSC_UTILITY.Varchar_Tabletype;
3230 l_Non_Time_Dimension_Value BSC_UTILITY.Varchar_Tabletype;
3231 l_Time_Dimension_Type BSC_UTILITY.Varchar_Tabletype;
3232 l_Time_Dimension_Value BSC_UTILITY.Varchar_Tabletype;
3233
3234 l_Attribute_Code_Count NUMBER;
3235 l_Attribute1_Count NUMBER;
3236 l_Attribute2_Count NUMBER;
3237 l_Attribute3_Count NUMBER;
3238 l_Default_Values_Count NUMBER;
3239
3240 l_PMV_Query_Table BIS_PMV_PAGE_PARAMETER_TBL := BIS_PMV_PAGE_PARAMETER_TBL();
3241 l_Table_Count NUMBER;
3242
3243 l_Page_Parameter_Tbl BIS_PMV_PAGE_PARAMETER_TBL := BIS_PMV_PAGE_PARAMETER_TBL();
3244 l_Page_Parameter_Rec BIS_PMV_PAGE_PARAMETER_REC := BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null);
3245
3246 l_Sql VARCHAR2(8192);
3247 l_Comparison_Type VARCHAR2(30);
3248 l_Measure_Columns VARCHAR2(4096);
3249 BEGIN
3250 FND_MSG_PUB.Initialize;
3251 l_Table_Count := 0;
3252 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
3253
3254 BSC_UTILITY.Parse_String
3255 (
3256 p_List => p_Attribute_Code
3257 , p_Separator => ','
3258 , p_List_Data => l_Attribute_Code
3259 , p_List_number => l_Attribute_Code_Count
3260 );
3261
3262 BSC_UTILITY.Parse_String
3263 (
3264 p_List => p_Attribute1
3265 , p_Separator => ','
3266 , p_List_Data => l_Attribute1
3267 , p_List_number => l_Attribute1_Count
3268 );
3269
3270 BSC_UTILITY.Parse_String
3271 (
3272 p_List => p_Attribute2
3273 , p_Separator => ','
3274 , p_List_Data => l_Attribute2
3275 , p_List_number => l_Attribute2_Count
3276 );
3277
3278 BSC_UTILITY.Parse_String
3279 (
3280 p_List => p_Attribute3
3281 , p_Separator => ','
3282 , p_List_Data => l_Attribute3
3283 , p_List_number => l_Attribute3_Count
3284 );
3285
3286 BSC_UTILITY.Parse_String
3287 (
3288 p_List => p_Default_Values
3289 , p_Separator => ','
3290 , p_List_Data => l_Default_Values
3291 , p_List_number => l_Default_Values_Count
3292 );
3293
3294 --DBMS_OUTPUT.PUT_LINE ('Stage 1');
3295
3296 FOR i IN 1..l_Attribute_Code_Count LOOP -- BRANCH #1
3297 IF (l_Attribute1(i) IN ( -- BRANCH #2
3298 'DIMENSION LEVEL',
3299 'DIM LEVEL SINGLE VALUE',
3300 'DIMENSION VALUE',
3301 'HIDE_VIEW_BY',
3302 'HIDE_VIEW_BY_SINGLE',
3303 'HIDE PARAMETER',
3304 'VIEWBY PARAMETER',
3305 'HIDE_DIM_LVL',
3306 'HIDE DIMENSION LEVEL',
3307 'HIDE VIEW BY DIMENSION',
3308 'HIDE_VIEW_BY_DIM_SINGLE')) THEN
3309
3310 --DBMS_OUTPUT.PUT_LINE ('Stage 2');
3311
3312 IF (l_Attribute2(i) = 'AS_OF_DATE') THEN -- BRANCH #3
3313 Insert_Into_Query_Table(l_PMV_Query_Table
3314 ,'BIS_CURRENT_ASOF_DATE'
3315 ,i
3316 ,l_Default_Values(i)
3317 ,NULL
3318 ,NULL
3319 ,NULL
3320 );
3321 Insert_Into_Query_Table(l_PMV_Query_Table
3322 ,'AS_OF_DATE'
3323 ,i
3324 ,l_Default_Values(i)
3325 ,NULL
3326 ,NULL
3327 ,NULL
3328 );
3329 ELSIF (SUBSTR(l_Attribute2(i), 1, INSTR(l_Attribute2(i),'+') - 1) IN ('TIME', 'EDW_TIME')) THEN -- BRANCH#3
3330 IF (l_Default_Values(i) IS NOT NULL) THEN -- BRANCH #4A
3331
3332 Insert_Into_Query_Table(l_PMV_Query_Table
3333 ,'BIS_CURRENT_EFFECTIVE_END_DATE'
3334 ,i
3335 ,l_Default_Values(i)
3336 ,NULL
3337 ,NULL
3338 ,NULL
3339 );
3340
3341 Insert_Into_Query_Table(l_PMV_Query_Table
3342 ,'BIS_CURRENT_EFFECTIVE_END_DATE'
3343 ,i
3344 ,l_Default_Values(i)
3345 ,NULL
3346 ,NULL
3347 ,NULL
3348 );
3349
3350 Insert_Into_Query_Table(l_PMV_Query_Table
3351 ,'BIS_PERIOD_TYPE'
3352 ,i
3353 ,SUBSTR(l_Attribute2(i), INSTR(l_Attribute2(i),'+') + 1)
3354 ,NULL
3355 ,NULL
3356 ,NULL
3357 );
3358
3359 Insert_Into_Query_Table(l_PMV_Query_Table
3360 ,'_LOCAL_TIME_PARAM'
3361 ,i
3362 ,l_Default_Values(i)
3363 ,NULL
3364 ,NULL
3365 ,NULL
3366 );
3367
3368 Insert_Into_Query_Table(l_PMV_Query_Table
3369 ,l_Attribute2(i) || '_FROM'
3370 ,i
3371 ,l_Default_Values(i)
3372 ,NULL
3373 ,NULL
3374 ,NULL
3375 );
3376
3377 Insert_Into_Query_Table(l_PMV_Query_Table
3378 ,l_Attribute2(i) || '_TO'
3379 ,i
3380 ,l_Default_Values(i)
3381 ,NULL
3382 ,NULL
3383 ,NULL
3384 );
3385
3386 Insert_Into_Query_Table(l_PMV_Query_Table
3387 ,l_Attribute2(i) || '_PFROM'
3388 ,i
3389 ,l_Default_Values(i)
3390 ,NULL
3391 ,NULL
3392 ,NULL
3393 );
3394
3395 Insert_Into_Query_Table(l_PMV_Query_Table
3396 ,l_Attribute2(i) || '_PTO'
3397 ,i
3398 ,l_Default_Values(i)
3399 ,NULL
3400 ,NULL
3401 ,NULL
3402 );
3403 END IF; -- BRANCH #4A
3404 -- WHEN PASSING THE TIME_COMPARISON_TYPE,THE VALUE SHOULD BE EITHER SEQUENTIAL OR YEARLY
3405 ELSIF (SUBSTR(l_Attribute2(i), 1, INSTR(l_Attribute2(i),'+') - 1) = 'TIME_COMPARISON_TYPE') THEN -- BRANCH#3
3406 l_Comparison_Type := SUBSTR(l_Attribute2(i), INSTR(l_Attribute2(i),'+') + 1);
3407
3408 Insert_Into_Query_Table(l_PMV_Query_Table
3409 ,'BIS_TIME_COMPARISON_TYPE'
3410 ,i
3411 ,l_Comparison_Type
3412 ,NULL
3413 ,NULL
3414 ,NULL
3415 );
3416
3417 Insert_Into_Query_Table(l_PMV_Query_Table
3418 ,'TIME_COMPARISON_TYPE'
3419 ,i
3420 ,l_Comparison_Type
3421 ,NULL
3422 ,NULL
3423 ,NULL
3424 );
3425
3426 ELSE -- BRANCH#3
3427 Insert_Into_Query_Table(l_PMV_Query_Table
3428 ,l_Attribute2(i)
3429 ,i
3430 ,l_Default_Values(i)
3431 ,NULL
3432 ,NULL
3433 ,NULL
3434 );
3435
3436 -- needs review
3437 Insert_Into_Query_Table(l_PMV_Query_Table
3438 ,'BIS_SELECTED_TOP_MANAGER'
3439 ,i
3440 ,l_Default_Values(i)
3441 ,NULL
3442 ,NULL
3443 ,NULL
3444 );
3445
3446 END IF; -- BRANCH#3
3447 ELSIF (l_Attribute1(i) = 'VIEW_BY') THEN -- BRANCH#2
3448 Insert_Into_Query_Table(l_PMV_Query_Table
3449 ,l_Attribute1(i)
3450 ,i
3451 ,l_Default_Values(i)
3452 ,NULL
3453 ,NULL
3454 ,NULL
3455 );
3456 ELSIF (l_Attribute1(i) IN ('BUCKET_MEASURE',
3457 'MEASURE',
3458 'MEASURE_NOTARGET',
3459 'SUB MEASURE',
3460 'COMPARE_TO_MEASURE_NO_TARGET',
3461 'COMPARE_TO_MEASURE')) THEN
3462 IF (l_Measure_Columns IS NULL) THEN -- BRANCH#3
3463 l_Measure_Columns := l_Attribute_Code(i);
3464 ELSE
3465 l_Measure_Columns := l_Measure_Columns || ',' || l_Attribute_Code(i);
3466 END IF; -- BRANCH#3
3467
3468 ELSIF (l_Attribute1(i) IN ('CHANGE_MEASURE_NO_TARGET',
3469 'CHANGE_MEASURE')) THEN
3470
3471 -- We must return the MEASURE column only if the base column (ATTRIBUTE3) has
3472 -- not been defined for a Change/ Percentage of total [NOTSURE].
3473 IF (l_Attribute3(i) IS NULL) THEN -- BRANCH#3
3474 IF (l_Measure_Columns IS NULL) THEN -- BRANCH#4
3475 l_Measure_Columns := l_Attribute_Code(i);
3476 ELSE
3477 l_Measure_Columns := l_Measure_Columns || ',' || l_Attribute_Code(i);
3478 END IF; -- BRANCH#4
3479 END IF; -- BRANCH#3
3480
3481 END IF; -- BRANCH#2
3482 END LOOP; -- BRANCH#1
3483
3484 -- No insert into the table misc Key,Value pairs.
3485 Insert_Into_Query_Table(l_PMV_Query_Table
3486 ,'BIS_FXN_NAME'
3487 ,NULL
3488 ,p_Region_Code
3489 ,NULL
3490 ,NULL
3491 ,NULL
3492 );
3493
3494 Insert_Into_Query_Table(l_PMV_Query_Table
3495 ,'BIS_REGION_CODE'
3496 ,NULL
3497 ,p_Region_Code
3498 ,NULL
3499 ,NULL
3500 ,NULL
3501 );
3502
3503 Insert_Into_Query_Table(l_PMV_Query_Table
3504 ,'BIS_ICX_SESSION_ID'
3505 ,NULL
3506 ,'-1'
3507 ,NULL
3508 ,NULL
3509 ,NULL
3510 );
3511
3512 --FOR i IN 1..l_PMV_Query_Table.COUNT LOOP
3513 --DBMS_OUTPUT.PUT_LINE (' parameter_name ('||i||') || - ' || l_PMV_Query_Table(i).parameter_name);
3514 --DBMS_OUTPUT.PUT_LINE (' parameter_value('||i||') || - ' || l_PMV_Query_Table(i).parameter_value);
3515 --END LOOP;
3516
3517
3518 x_Custom_Columns := l_Measure_Columns;
3519
3520 l_Sql := 'BEGIN ' || p_Plsql_Function || ' (:1, :2, :3); END;';
3521 EXECUTE IMMEDIATE l_Sql USING l_PMV_Query_Table, OUT x_Custom_Sql, OUT x_Custom_Output;
3522
3523 EXCEPTION
3524 WHEN FND_API.G_EXC_ERROR THEN
3525 IF (x_msg_data IS NULL) THEN
3526 FND_MSG_PUB.Count_And_Get
3527 ( p_encoded => FND_API.G_FALSE
3528 , p_count => x_msg_count
3529 , p_data => x_msg_data
3530 );
3531 END IF;
3532 x_Return_Status := FND_API.G_RET_STS_ERROR;
3533 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3534 IF (x_msg_data IS NULL) THEN
3535 FND_MSG_PUB.Count_And_Get
3536 ( p_encoded => FND_API.G_FALSE
3537 , p_count => x_msg_count
3538 , p_data => x_msg_data
3539 );
3540 END IF;
3541 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3542 WHEN NO_DATA_FOUND THEN
3543 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3544 IF (x_msg_data IS NOT NULL) THEN
3545 x_msg_data := x_msg_data||' -> BSC_UTILITY.Obtain_Report_Query ';
3546 ELSE
3547 x_msg_data := SQLERRM||' at BSC_UTILITY.Obtain_Report_Query ';
3548 END IF;
3549 WHEN OTHERS THEN
3550 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3551 IF (x_msg_data IS NOT NULL) THEN
3552 x_msg_data := x_msg_data||' -> BSC_UTILITY.Obtain_Report_Query ';
3553 ELSE
3554 x_msg_data := SQLERRM||' at BSC_UTILITY.Obtain_Report_Query ';
3555 END IF;
3556 END Obtain_Report_Query;
3557
3558
3559 -- Inserts into Parameter Query table.
3560 PROCEDURE Insert_Into_Query_Table (
3561 x_Param_Table IN OUT NOCOPY BIS_PMV_PAGE_PARAMETER_TBL
3562 , p_Parameter_Name IN VARCHAR2
3563 , p_Parameter_Id IN VARCHAR2
3564 , p_Parameter_Value IN VARCHAR2
3565 , p_Dimension IN VARCHAR2
3566 , p_Period_Date IN DATE
3567 , p_Operator IN VARCHAR2
3568 ) IS
3569 l_Index NUMBER;
3570 l_Page_Parameter_Rec BIS_PMV_PAGE_PARAMETER_REC := BIS_PMV_PAGE_PARAMETER_REC(null,null,null,null,null,null);
3571
3572 BEGIN
3573 l_Page_Parameter_Rec.Parameter_Name := p_Parameter_Name;
3574 l_Page_Parameter_Rec.Parameter_Id := l_Index;
3575 l_Page_Parameter_Rec.Parameter_Value := p_Parameter_Value;
3576 l_Page_Parameter_Rec.Dimension := p_Dimension;
3577 l_Page_Parameter_Rec.Period_Date := p_Period_Date;
3578 l_Page_Parameter_Rec.Operator := p_Operator;
3579
3580 x_Param_Table.EXTEND;
3581 x_Param_Table(x_Param_Table.LAST) := l_Page_Parameter_Rec;
3582 EXCEPTION
3583 WHEN OTHERS THEN
3584 NULL;
3585 END Insert_Into_Query_Table;
3586
3587 -- Procedure to perform transactions autonomously
3588 PROCEDURE Do_DDL_AT(
3589 p_Statement IN VARCHAR2,
3590 p_Statement_Type IN INTEGER,
3591 p_Object_Name IN VARCHAR2,
3592 p_Fnd_Apps_Schema IN VARCHAR2,
3593 p_Apps_Short_Name IN VARCHAR2
3594 ) IS
3595 PRAGMA AUTONOMOUS_TRANSACTION;
3596 BEGIN
3597 AD_DDL.Do_DDL(p_Fnd_Apps_Schema,
3598 p_Apps_Short_Name,
3599 p_Statement_Type,
3600 p_Statement,
3601 p_Object_Name);
3602 END Do_DDL_AT;
3603
3604
3605 /*
3606 Current Algorithm
3607
3608 -- Taken in the SQL
3609 -- Validate by Creating a View autonomously
3610 -- Report Failure to create
3611 -- Return the comma separated list (ordered)
3612 -- Drop the view autonomously
3613
3614 */
3615
3616 PROCEDURE Validate_Sql_String (
3617 p_Sql_String IN VARCHAR2
3618 , x_Columns OUT NOCOPY VARCHAR2
3619 , x_Return_Status OUT NOCOPY VARCHAR2
3620 , x_Msg_Count OUT NOCOPY NUMBER
3621 , x_Msg_Data OUT NOCOPY VARCHAR2
3622 ) IS
3623 l_Temp_View_Name VARCHAR2(30);
3624 l_Sql_View VARCHAR2(2048);
3625 l_View_Cols VARCHAR2(2048);
3626
3627 l_Stage VARCHAR2(30);
3628
3629 CURSOR c_Get_Column_Names IS
3630 SELECT A.COLUMN_NAME
3631 FROM ALL_TAB_COLS A
3632 WHERE A.TABLE_NAME = l_Temp_View_Name
3633 AND A.OWNER = BSC_APPS.get_user_schema(C_PACKAGE_OWNER)
3634 ORDER BY A.COLUMN_NAME;
3635
3636 BEGIN
3637 FND_MSG_PUB.Initialize;
3638 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
3639
3640 -- Bug#5034549; Added ABS() to DBMS_UTILITY.GET_TIME, since it can return negative value
3641 l_Temp_View_Name := 'BIS_TMP_' || TO_CHAR(SYSDATE,'J')||ABS(DBMS_UTILITY.GET_TIME) || '_V';
3642
3643 l_Sql_View := 'CREATE OR REPLACE VIEW ' || l_Temp_View_Name;
3644 l_Sql_View := l_Sql_View || ' AS ';
3645 l_Sql_View := l_Sql_View || p_Sql_String;
3646
3647 l_Stage := 'CREATE_VIEW';
3648 BSC_UTILITY.Do_Ddl_AT(l_Sql_View, AD_DDL.CREATE_VIEW, l_Temp_View_Name, 'APPS', 'BIS');
3649
3650 -- View has been created successfully, if it reaches here.
3651
3652 FOR cGCN IN c_Get_Column_Names LOOP
3653 IF (x_Columns IS NULL) THEN
3654 x_Columns := cGCN.COLUMN_NAME;
3655 ELSE
3656 x_Columns := x_Columns || ',' || cGCN.COLUMN_NAME;
3657 END IF;
3658 END LOOP;
3659
3660 l_Sql_View := 'DROP VIEW ' || l_Temp_View_Name;
3661 l_Stage := 'DROP_VIEW';
3662 BSC_UTILITY.Do_Ddl_AT(l_Sql_View, AD_DDL.DROP_VIEW, l_Temp_View_Name, 'APPS', 'BIS');
3663
3664 EXCEPTION
3665 WHEN OTHERS THEN
3666 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
3667 x_Msg_Data := SQLERRM;
3668 END Validate_Sql_String;
3669
3670 -- Sorts a comma separated string using a Enhanced Bubble Sort (Very bad - but ok for now)
3671 FUNCTION Sort_String (
3672 p_String IN VARCHAR2
3673 ) RETURN VARCHAR2 IS
3674 l_String_Table BSC_UTILITY.Varchar_Tabletype;
3675 l_Temp VARCHAR2(1024);
3676 l_Count NUMBER;
3677 i NUMBER;
3678 j NUMBER;
3679 l_Return_String VARCHAR2(8192);
3680 BEGIN
3681 IF (p_String IS NULL) THEN
3682 RETURN NULL;
3683 END IF;
3684 BSC_UTILITY.Parse_String
3685 (
3686 p_List => p_String
3687 , p_Separator => ','
3688 , p_List_Data => l_String_Table
3689 , p_List_number => l_Count
3690 );
3691
3692 FOR i IN REVERSE 1..l_Count LOOP
3693 FOR j IN 1..(i-1) LOOP
3694 IF (l_String_Table(j) > l_String_Table(j+1)) THEN
3695 l_Temp := l_String_Table(j);
3696 l_String_Table(j) := l_String_Table(j+1);
3697 l_String_Table(j+1) := l_Temp;
3698 END IF;
3699 END LOOP;
3700 END LOOP;
3701
3702 FOR i IN 1..l_String_Table.COUNT LOOP
3703 IF l_Return_String IS NULL THEN
3704 l_Return_String := l_String_Table(i);
3705 ELSE
3706 l_Return_String := l_Return_String || ',' || l_String_Table(i);
3707 END IF;
3708 END LOOP;
3709
3710 RETURN l_Return_String;
3711
3712 EXCEPTION
3713 WHEN OTHERS THEN
3714 RETURN SQLERRM;
3715 END Sort_String;
3716
3717 FUNCTION is_bsc_measure_convertible (
3718 p_dataset_id IN NUMBER
3719 , p_region_code IN VARCHAR2
3720 ) RETURN VARCHAR2
3721 IS
3722 l_convertible VARCHAR2(10);
3723 l_obj_attached VARCHAR2(10);
3724 l_formula_meas VARCHAR2(10);
3725 BEGIN
3726 l_convertible := FND_API.G_FALSE;
3727
3728 l_obj_attached := is_attached_to_objective
3729 ( p_dataset_id => p_dataset_id
3730 , p_region_code => p_region_code
3731 );
3732
3733 IF (l_obj_attached = FND_API.G_FALSE) THEN
3734 l_formula_meas := is_formula_measure
3735 ( p_dataset_id => p_dataset_id
3736 );
3737 END IF;
3738
3739 IF (l_obj_attached = FND_API.G_FALSE AND l_formula_meas = FND_API.G_FALSE) THEN
3740 l_convertible := FND_API.G_TRUE;
3741 END IF;
3742
3743 RETURN l_convertible;
3744 EXCEPTION
3745 WHEN OTHERS THEN
3746 RETURN FND_API.G_FALSE;
3747 END is_bsc_measure_convertible;
3748
3749 -- Returns 'T' if the p_dataset_id is attached to any objective except the objective for p_region_code
3750 FUNCTION is_attached_to_objective (
3751 p_dataset_id IN NUMBER
3752 , p_region_code IN VARCHAR2
3753 ) RETURN VARCHAR2
3754 IS
3755 l_attached VARCHAR2(10);
3756 l_obj_count NUMBER;
3757
3758 CURSOR c_attached_obj IS
3759 SELECT COUNT(1)
3760 FROM bsc_kpis_b k, bsc_kpi_analysis_measures_b m
3761 WHERE k.indicator = m.indicator
3762 AND m.dataset_id = p_dataset_id
3763 AND k.short_name <> p_region_code;
3764
3765 BEGIN
3766 l_attached := FND_API.G_FALSE;
3767
3768 IF (c_attached_obj%ISOPEN) THEN
3769 CLOSE c_attached_obj;
3770 END IF;
3771 OPEN c_attached_obj;
3772 FETCH c_attached_obj INTO l_obj_count;
3773 IF (l_obj_count > 0) THEN
3774 l_attached := FND_API.G_TRUE;
3775 END IF;
3776 CLOSE c_attached_obj;
3777
3778 RETURN l_attached;
3779 EXCEPTION
3780 WHEN OTHERS THEN
3781 IF (c_attached_obj%ISOPEN) THEN
3782 CLOSE c_attached_obj;
3783 END IF;
3784 RETURN FND_API.G_FALSE;
3785 END is_attached_to_objective;
3786
3787 -- Wrapper over BSC_BIS_MEASURE_PUB.Is_Src_Col_In_Formulas to return a VARCHAR so that it can be used in a SQL query.
3788 FUNCTION Is_Src_Col_In_Formulas (
3789 p_Source_Col IN VARCHAR2
3790 ) RETURN VARCHAR2
3791 IS
3792 BEGIN
3793 IF (BSC_BIS_MEASURE_PUB.Is_Src_Col_In_Formulas(p_Source_Col)) THEN
3794 RETURN FND_API.G_TRUE;
3795 END IF;
3796 RETURN FND_API.G_FALSE;
3797 EXCEPTION
3798 WHEN OTHERS THEN
3799 RETURN FND_API.G_FALSE;
3800 END Is_Src_Col_In_Formulas;
3801
3802 -- Returns 'T' if the p_dataset_id is itself a Formula measure or a part of the formula for some other measure.
3803 FUNCTION is_formula_measure (
3804 p_dataset_id IN NUMBER
3805 ) RETURN VARCHAR2
3806 IS
3807 l_formula VARCHAR2(10);
3808 l_count NUMBER;
3809
3810 CURSOR c_formula_meas IS
3811 SELECT COUNT(1)
3812 FROM bsc_sys_datasets_b d, bsc_sys_measures m
3813 WHERE d.dataset_id = p_dataset_id
3814 AND m.measure_id = d.measure_id1
3815 AND ((BSC_BIS_MEASURE_PUB.Is_Formula_Type(m.measure_Col) = 'T') OR
3816 (d.measure_id2 IS NOT NULL) OR
3817 (BSC_UTILITY.Is_Src_Col_In_Formulas(m.measure_Col) = 'T') OR
3818 ((SELECT COUNT(1) FROM bsc_sys_datasets_b
3819 WHERE dataset_id <> d.dataset_id
3820 AND (measure_id1 = d.measure_id1 OR measure_id2 = d.measure_id1)) > 0)
3821 );
3822
3823 BEGIN
3824 l_formula := FND_API.G_FALSE;
3825
3826 IF (c_formula_meas%ISOPEN) THEN
3827 CLOSE c_formula_meas;
3828 END IF;
3829 OPEN c_formula_meas;
3830 FETCH c_formula_meas INTO l_count;
3831 IF (l_count > 0) THEN
3832 l_formula := FND_API.G_TRUE;
3833 END IF;
3834 CLOSE c_formula_meas;
3835
3836 RETURN l_formula;
3837 EXCEPTION
3838 WHEN OTHERS THEN
3839 IF (c_formula_meas%ISOPEN) THEN
3840 CLOSE c_formula_meas;
3841 END IF;
3842 RETURN FND_API.G_FALSE;
3843 END is_formula_measure;
3844
3845
3846 /***********************************************
3847 UTILITY FUNCTION TO RETURN A UNIQUE MERGED LIST
3848 ************************************************/
3849
3850 FUNCTION Create_Unique_Comma_List (
3851 p_List1 IN VARCHAR2,
3852 p_List2 IN VARCHAR2
3853 ) RETURN VARCHAR2 IS
3854
3855 l_Merged_String VARCHAR2(32000);
3856
3857 l_List_Table BSC_UTILITY.Varchar_Tabletype;
3858 l_List_Count NUMBER;
3859 l_UList_Table BSC_UTILITY.Varchar_Tabletype;
3860 l_UList_Count NUMBER;
3861
3862 l_Final_List VARCHAR2(32000);
3863 BEGIN
3864 IF (p_List1 IS NOT NULL) THEN
3865 l_Merged_String := p_List1;
3866
3867 IF (p_List2 IS NOT NULL) THEN
3868 l_Merged_String := l_Merged_String || ',' || p_List2;
3869 END IF;
3870 ELSE
3871 IF (p_List2 IS NOT NULL) THEN
3872 l_Merged_String := p_List2;
3873 END IF;
3874 END IF;
3875
3876 IF (l_Merged_String IS NOT NULL) THEN
3877 BSC_UTILITY.Parse_String
3878 (
3879 p_List => l_Merged_String
3880 , p_Separator => ','
3881 , p_List_Data => l_List_Table
3882 , p_List_number => l_List_Count
3883 );
3884 END IF;
3885
3886 l_UList_Table := BSC_UTILITY.Get_Unique_List(l_List_Table);
3887
3888 FOR i IN 1..l_UList_Table.COUNT LOOP
3889 IF (l_Final_List IS NULL) THEN
3890 l_Final_List := l_UList_Table(i);
3891 ELSE
3892 l_Final_List := l_Final_List || ',' || l_UList_Table(i);
3893 END IF;
3894 END LOOP;
3895
3896 RETURN l_Final_List;
3897
3898 EXCEPTION
3899 WHEN OTHERS THEN
3900 RETURN NULL;
3901 END Create_Unique_Comma_List;
3902
3903
3904 /*****************************************************************************
3905 UTILITY FUNCTION TO RETURN A UNIQUE LIST of TYPE BSC_UTILITY.varchar_tabletype
3906 ******************************************************************************/
3907
3908 FUNCTION Get_Unique_List (p_List IN BSC_UTILITY.varchar_tabletype)
3909 RETURN BSC_UTILITY.varchar_tabletype IS
3910 l_Unique_List BSC_UTILITY.varchar_tabletype;
3911 l_Element VARCHAR2(32000);
3912 l_Count NUMBER;
3913 l_Duplicate BOOLEAN;
3914 BEGIN
3915 l_Count := 1;
3916 l_Duplicate := FALSE;
3917
3918 IF ((p_List IS NOT NULL) AND p_List.COUNT > 0) THEN
3919 FOR i IN 1..p_List.COUNT LOOP
3920 l_Element := p_List(i);
3921
3922 FOR j IN (i+1)..p_List.COUNT LOOP
3923 IF (l_Element = p_List(j)) THEN
3924 l_Duplicate := TRUE;
3925 END IF;
3926 END LOOP;
3927
3928 IF NOT l_Duplicate THEN
3929 l_Unique_List(l_Count) := l_Element;
3930 l_Count := l_Count + 1;
3931 END IF;
3932
3933 l_Duplicate := FALSE;
3934 END LOOP;
3935 END IF;
3936
3937 RETURN l_Unique_List;
3938 EXCEPTION
3939 WHEN OTHERS THEN
3940 RETURN l_Unique_List;
3941 END Get_Unique_List;
3942
3943 /*****************************************************************************
3944 UTILITY FUNCTIONS FOR MEASURES
3945 ******************************************************************************/
3946
3947 FUNCTION is_Calculated_kpi
3948 (
3949 p_Measure_Short_Name IN VARCHAR2
3950 )RETURN VARCHAR2
3951 IS
3952 l_flag VARCHAR2(10);
3953 l_measure_type VARCHAR2(10);
3954 BEGIN
3955 l_flag :=FND_API.G_FALSE;
3956
3957 SELECT measure_type
3958 INTO l_measure_type
3959 FROM bis_indicators
3960 WHERE short_name = p_Measure_Short_Name;
3961
3962 IF(l_measure_type=BSC_UTILITY.C_MEASURE_SOURCE_CDS_CALC)THEN
3963 l_flag:=FND_API.G_TRUE;
3964 END IF;
3965 RETURN l_flag;
3966
3967 EXCEPTION
3968 WHEN OTHERS THEN
3969 RETURN l_flag;
3970 END is_Calculated_kpi;
3971
3972
3973
3974 FUNCTION Is_Meas_Used_In_Targets
3975 (
3976 p_Dataset_Id IN BSC_SYS_DATASETS_VL.dataset_id%TYPE
3977 ) RETURN VARCHAR2
3978 IS
3979 l_count NUMBER;
3980 BEGIN
3981
3982 l_count := 0;
3983
3984 SELECT COUNT(0)
3985 INTO l_count
3986 FROM bis_target_levels tl,
3987 bis_indicators i
3988 WHERE i.dataset_id = p_Dataset_Id
3989 AND tl.indicator_id = i.indicator_id;
3990
3991 IF(l_count>0)THEN
3992 RETURN FND_API.G_TRUE;
3993 ELSE
3994 RETURN FND_API.G_FALSE;
3995 END IF;
3996 END Is_Meas_Used_In_Targets;
3997
3998
3999 FUNCTION Is_Wam_Kpi
4000 (
4001 p_dataset_id IN BSC_SYS_DATASETS_VL.dataset_id%TYPE
4002 )RETURN VARCHAR2
4003 IS
4004 l_count NUMBER;
4005 l_flag VARCHAR2(10);
4006 l_measure_type VARCHAR2(10);
4007 BEGIN
4008 l_flag :=FND_API.G_FALSE;
4009
4010 SELECT measure_type
4011 INTO l_measure_type
4012 FROM bis_indicators
4013 WHERE dataset_id = p_dataset_id;
4014
4015 IF((l_measure_type=BSC_UTILITY.C_MEASURE_TYPE_CDS_SCORE) OR (l_measure_type=BSC_UTILITY.C_MEASURE_TYPE_CDS_PERF))THEN
4016 l_flag:=FND_API.G_TRUE;
4017 END IF;
4018 RETURN l_flag;
4019 EXCEPTION
4020 WHEN OTHERS THEN
4021 RETURN l_flag;
4022 END Is_Wam_Kpi;
4023
4024
4025
4026 FUNCTION Is_Report_Primary_Data_Source
4027 (
4028 p_Indicator IN BSC_KPIS_B.indicator%TYPE
4029 ,p_Dataset_Id IN BSC_SYS_DATASETS_B.dataset_id%TYPE
4030 ) RETURN VARCHAR2
4031 IS
4032 l_region_code BSC_KPIS_B.short_name%TYPE;
4033 l_meas_short_name BIS_INDICATORS.short_name%TYPE;
4034 l_flag VARCHAR2(10);
4035 BEGIN
4036
4037 l_flag := FND_API.G_FALSE;
4038
4039 SELECT short_name
4040 INTO l_region_code
4041 FROM bsc_kpis_b
4042 WHERE indicator =p_Indicator;
4043
4044 SELECT short_name
4045 INTO l_meas_short_name
4046 FROM bis_indicators
4047 WHERE dataset_id =p_Dataset_Id;
4048
4049 l_flag := BSC_BIS_KPI_CRUD_PUB.Is_Primary_Source_Of_Measure
4050 (
4051 p_Measure_Short_Name => l_meas_short_name
4052 ,p_Region_Code => l_region_code
4053 );
4054 RETURN l_flag;
4055
4056 EXCEPTION
4057 WHEN OTHERS THEN
4058 RETURN l_flag;
4059 END Is_Report_Primary_Data_Source;
4060
4061
4062 FUNCTION is_Calculated_kpi
4063 (
4064 p_dataset_id IN BSC_SYS_DATASETS_B.dataset_id%TYPE
4065 )RETURN VARCHAR2
4066 IS
4067 l_flag VARCHAR2(10);
4068 l_measure_type VARCHAR2(10);
4069 BEGIN
4070 l_flag :=FND_API.G_FALSE;
4071
4072 SELECT measure_type
4073 INTO l_measure_type
4074 FROM bis_indicators
4075 WHERE dataset_id = p_dataset_id;
4076
4077 IF(l_measure_type=BSC_UTILITY.C_MEASURE_SOURCE_CDS_CALC)THEN
4078 l_flag:=FND_API.G_TRUE;
4079 END IF;
4080 RETURN l_flag;
4081
4082 EXCEPTION
4083 WHEN OTHERS THEN
4084 RETURN l_flag;
4085 END is_Calculated_kpi;
4086
4087
4088 FUNCTION Is_Meas_Used_In_Wam_Report
4089 (
4090 p_dataset_id IN BSC_SYS_DATASETS_B.dataset_id%TYPE
4091 )RETURN VARCHAR2
4092 IS
4093 l_count NUMBER;
4094 l_flag VARCHAR2(10);
4095 l_attribute2 ak_region_items.attribute2%TYPE;
4096 BEGIN
4097
4098 l_flag := FND_API.G_FALSE;
4099 l_count := 0;
4100
4101 SELECT short_name
4102 INTO l_attribute2
4103 FROM bis_indicators
4104 WHERE dataset_id =p_dataset_id;
4105
4106 SELECT COUNT(1)
4107 INTO l_count
4108 FROM ak_region_items a,
4109 ak_regions b
4110 WHERE a.region_code = b.region_code
4111 AND a.attribute1 IN (BSC_UTILITY.C_ATTRTYPE_MEASURE,BSC_UTILITY.C_ATTRTYPE_MEASURE_NO_TARGET,BSC_UTILITY.C_BUCKET_MEASURE,BSC_UTILITY.C_SUB_MEASURE)
4112 AND a.attribute2 = l_attribute2
4113 AND b.attribute10 = BSC_UTILITY.C_MULTIPLE_DATA_SOURCE;
4114
4115 IF(l_count>0)THEN
4116 l_flag := FND_API.G_TRUE;
4117 END IF;
4118
4119 RETURN l_flag;
4120
4121 EXCEPTION
4122 WHEN OTHERS THEN
4123 RETURN l_flag;
4124 END Is_Meas_Used_In_Wam_Report;
4125
4126
4127 PROCEDURE comp_leapyear_prioryear(
4128 p_calid IN NUMBER,
4129 p_cyear IN NUMBER,
4130 p_pyear IN NUMBER,
4131 x_result OUT nocopy NUMBER
4132 )IS
4133 lday number :=0;
4134 lmonth number:=0;
4135
4136 CURSOR diff IS
4137 SELECT day30, MONTH
4138 FROM bsc_db_calendar
4139 WHERE calendar_id = p_calid
4140 AND calendar_year = p_cyear
4141 MINUS
4142 SELECT day30, MONTH
4143 FROM bsc_db_calendar
4144 WHERE calendar_id = p_calid
4145 AND calendar_year = p_pyear;
4146 BEGIN
4147 OPEN diff;
4148 IF diff%NOTFOUND THEN
4149 x_result := -1;
4150 ELSE
4151 FETCH diff into lday, lmonth;
4152 END IF;
4153 CLOSE diff;
4154 IF lday <>0 THEN
4155 SELECT day365
4156 INTO x_result
4157 FROM bsc_db_calendar
4158 WHERE calendar_id = p_calid
4159 AND calendar_year = p_cyear
4160 AND day30 = lday
4161 AND MONTH = lmonth;
4162 END IF;
4163 EXCEPTION
4164 WHEN OTHERS THEN
4165 x_result := -1;
4166 END comp_leapyear_prioryear;
4167
4168 END BSC_UTILITY;