1 PACKAGE BSC_UTILITY AS
2 /*$Header: BSCUTILS.pls 120.24 2007/10/04 14:29:31 sirukull ship $ */
3 /*===========================================================================+
4 | Copyright (c) 1995 Oracle Corporation |
5 | Redwood Shores, California, USA |
6 | All rights reserved. |
7 | 12-NOV-03 Bug #3232366 |
8 | 09-AUG-2004 sawu Added API Get_Default_Internal_Name for bug#3819855 |
9 | 18-AUG-2004 ADRAO Fixed Bug#3831815 |
10 | 01-OCT-2004 ashankar Fixed Bug#3908204 |
11 | 05-OCT-2004 ankgoel Bug#3933075 Moved C_BSC_UNDERSCORE here from |
12 | BSCCRUDS.pls |
13 | 08-APR-2005 kyadamak Added function get_valid_bsc_master_tbl_name() for |
14 | bug# 4290359 |
15 | 18-JYL-2005 ppandey added Dimension entity validation APIs |
16 | 11-AUG-2005 ppandey Bug #4324947 Validation for Dim,Dim Obj in Rpt |
17 | 01-SEP-2005 adrao Added API Get_Responsibility_Key for Bug#4563456 |
18 | 06-SEP-2005 KYADMAK added constant for bug#4593321 |
19 | 13-Sep-2005 sawu Bug#4602231: Broken is_internal_dim into component apis|
20 | 05-Oct-2005 ashankar Bug# Added the method Get_User_Time |
21 | 02-Jan-2006 akoduri Bug#4611303 - Support For Enable/Disable All |
22 | In Report Designer |
23 | 05-JAN-06 ppandey Enh#4860106 Defined Is_More as a public function |
24 | 13-JAN-06 adrao |
25 | The following APIs have been added as a part of the Enhancement#3909868 |
26 | |
27 | Validate_Plsql_For_Report |
28 | -- actual api to validation the pl/sql for the report |
29 | Get_Plsql_Parameters |
30 | -- gets the pl/sql report for the passed pl/sql procedure |
31 | Remove_Repeating_Comma |
32 | -- Removes and parses repeating comma's |
33 | Validate_PLSQL |
34 | -- Validates existentially the pl/sql package anderformes some |
35 | validation apis |
36 | Obtain_Report_Query |
37 | -- Does the job of actually getting the Report query |
38 | Insert_Into_Query_Table |
39 | -- An API to insert into the PL/SQL table. |
40 | Do_DDL_AT |
41 | -- Autonously call DDL statements, used in our case for creating |
42 | and dropping views |
43 | Validate_Sql_String |
44 | -- Validate's if a SQL string is ok by creating a view |
45 | Sort_String |
46 | -- sorts a comma separated string values |
47 | |
48 | 19-JUN-2006 adrao Added util API Create_Unique_Comma_List & |
49 | Get_Unique_List for Bug#5300060 |
50 | 09-feb-2007 ashankar Simulation Tree Enhacement 5386112 |
51 | 21-MAR-2007 akoduri Copy Indicator Enh#5943238 |
52 +===========================================================================*/
53 /*----------------------------------------------------------------------------
54 FILE NAME
55
56 BSCUTILS.pls
57
58 PACKAGE NAME
59
60 bsc_utility
61
62 DESCRIPTION
63
64 Contains debuging and utility functions and procedures.
65
66 PUBLIC PROCEDURES/FUNCTIONS
67
68 HISTORY
69 15-JAN-1999 Srinivasan Jandyala Created
70 22-JAN-1999 Alex Yang Added Do_SQL() procedure
71 29-MAR-2001 Srini Added PROCEDURE update_edw_flag.
72 21-DEC-2001 Mario-Jair Campos Added procedures: get_dataset_id
73 get_kpi_dim_levels
74 27-DEC-2001 Srini Added function:get_kpi_dim_level_short_names
75 12-MAR-2002 M. Jair Campos Added system time stamp function.
76 23-APR-2003 mdamle Added the toStringArray and ToNumberArray
77 23-APR-2003 mdamle Added the Add_To_Fnd_Msg_Stack
78 27-FEB-2004 adeulgao fixed bug#3431750
79 16-JUN-2004 ADRAO added API Is_BSC_Licensed() for Bug#3764205
80 21-DEC-2004 adrao added type DIMOBJ_SHORT_NAME_CLASS for Bug#4079898
81 31-MAR-2005 adrao added API is_Mix_Dim_Objects_Allowed
82 24-JAN-2006 ankgoel Bug#4954663 Show Info text for AG to PL/SQL or VB conversion
83 08-FEB-2006 akoduri Bug#4956836 Updating dim object cache should
84 invalidate AK Cache also
85 04-OCT-2007 sirukull Bug#6406844. Comparing Leapyear daily periodicity |
86 data with non-leapyear data. |
87
88 ----------------------------------------------------------------------------*/
89 -----------------------------------------------------------------------------
90 -- Public Variables
91 -----------------------------------------------------------------------------
92 -- Linefeed character
93 --
94 CRLF CONSTANT VARCHAR2(1) := '
95 ';
96
97 YES CONSTANT VARCHAR2(1) := 'Y';
98 NO CONSTANT VARCHAR2(1) := 'N';
99
100 MSG_LEVEL_BASIC CONSTANT NUMBER := 0;
101 MSG_LEVEL_TIMING CONSTANT NUMBER := 1;
102 MSG_LEVEL_DEBUG CONSTANT NUMBER := 2;
103 MSG_LEVEL_DEBUG2 CONSTANT NUMBER := 3;
104 MSG_LEVEL_DEVELOP CONSTANT NUMBER := 10;
105 NO_IND_DIM_OBJ_LIMIT CONSTANT NUMBER := 10;
106
107 PRODUCTION_MODE CONSTANT VARCHAR2(10) := 'PRODUCTION';
108 PROTOTYPE_MODE CONSTANT VARCHAR2(9) := 'PROTOTYPE';
109 INVALID_MODE CONSTANT VARCHAR2(9) := 'INVALID';
110 BSC_APP_ID CONSTANT NUMBER := 271;
111 INVALID_CUST_VIEW_NAME CONSTANT VARCHAR2(25) := 'INVALID_INTERNAL_NAME';
112 BSC_CUSTOM_VIEW CONSTANT VARCHAR2(25) := 'CUSTOMVIEW';
113
114 MAX_DIM_IN_DIM_SET CONSTANT NUMBER := 20; -- Added for the Maximum number of Dimension Object that can exist
115 -- within a Dimension Set for Bug# 3141813
116 msg_level NUMBER := MSG_LEVEL_DEVELOP;
117
118
119 c_BSC CONSTANT VARCHAR2(3) := 'BSC';
120
121 c_ADV_SUMMARIZATION_LEVEL CONSTANT VARCHAR2(32) := 'BSC_ADVANCED_SUMMARIZATION_LEVEL';
122
123 c_BSC_MEASURE CONSTANT VARCHAR(11) := 'BSC_MEASURE';
124 c_BSC_DIMENSION CONSTANT VARCHAR(13) := 'BSC_DIMENSION';
125 c_BSC_DIM_OBJ CONSTANT VARCHAR(11) := 'BSC_DIM_OBJ';
126 C_BSC_MEASURE_SHORT_NAME CONSTANT VARCHAR2(7) := 'BSC_MES';
127 C_BSC_UNDERSCORE CONSTANT VARCHAR2(5) := 'BSC_';
128
129 -- Added for Enhancement #3947903
130 c_PMF CONSTANT VARCHAR2(3) := 'PMF';
131 c_CALENDAR CONSTANT VARCHAR2(30) := 'CALENDAR';
132 c_DIMENSION CONSTANT VARCHAR2(9) := 'DIMENSION';
133 c_DIM_OBJ CONSTANT VARCHAR2(7) := 'DIM_OBJ';
134 c_DIM_OBJ_REL CONSTANT VARCHAR2(16) := 'DIM_OBJ_RELATION';
135 c_MEASURE CONSTANT VARCHAR2(7) := 'MEASURE';
136
137 c_CREATE CONSTANT VARCHAR2(6) := 'CREATE';
138 c_UPDATE CONSTANT VARCHAR2(6) := 'UPDATE';
139 c_DELETE CONSTANT VARCHAR2(6) := 'DELETE';
140
141 c_MIXED_DIM_OBJS CONSTANT VARCHAR2(14) := 'MIXED_DIM_OBJS';
142
143 -- added for Dimension entity validations
144 c_DIMENSION_OBJECT CONSTANT VARCHAR2(30) := 'DIMENSION_OBJECT';
145
146 -- BSC AND BIS RESPONSIBILITIES
147 c_BSC_Manager CONSTANT VARCHAR2(11) := 'BSC_Manager';
148 c_BSC_DESIGNER CONSTANT VARCHAR2(12) := 'BSC_DESIGNER';
149 c_BSC_PMD_USER CONSTANT VARCHAR2(12) := 'BSC_PMD_USER';
150 c_BIS_BID_RESP CONSTANT VARCHAR2(12) := 'BIS_BID_RESP';
151 c_BIS_DBI_ADMIN CONSTANT VARCHAR2(13) := 'BIS_DBI_ADMIN';
152
153 -- BIS Report Constants
154 C_ATTRIBUTE_CATEGORY CONSTANT VARCHAR2(22) := 'BIS PM Viewer';
155 C_REPORT_TYPE_MDS CONSTANT VARCHAR2(22) := 'MULTIPLE_DATA_SOURCE';
156 C_REPORT_TYPE_TABLE CONSTANT VARCHAR2(20) := 'TABLE_DATA_SOURCE';
157
158 ---
159 C_MEASURE_SOURCE_CDS CONSTANT VARCHAR2(22) := 'CDS';
160 C_MEASURE_TYPE_CDS_SCORE CONSTANT VARCHAR2(22) := 'CDS_SCORE';
161 C_MEASURE_TYPE_CDS_PERF CONSTANT VARCHAR2(22) := 'CDS_PERF';
162 C_MEASURE_SOURCE_CDS_CALC CONSTANT VARCHAR2(22) := 'CDS_CALC';
163
164 C_ATTRTYPE_MEASURE CONSTANT VARCHAR2(30) := 'MEASURE';
165 C_ATTRTYPE_MEASURE_NO_TARGET CONSTANT VARCHAR2(30) := 'MEASURE_NOTARGET';
166 C_BUCKET_MEASURE CONSTANT VARCHAR2(30) := 'BUCKET_MEASURE';
167 C_SUB_MEASURE CONSTANT VARCHAR2(30) := 'SUB MEASURE';
168 C_MULTIPLE_DATA_SOURCE CONSTANT VARCHAR2(30) := 'MULTIPLE_DATA_SOURCE';
169
170
171 TYPE varchar_tabletype IS TABLE OF varchar2(32000) INDEX BY binary_integer;
172
173 -- added for Bug#4079898
174 TYPE DIMOBJ_SHORT_NAME_CLASS IS VARRAY(20) OF BIS_LEVELS.SHORT_NAME%TYPE;
175
176
177 -- added for Enh to Validate PL/SQL procedure.
178 TYPE PARAMETER_CLASS IS VARRAY(30) OF VARCHAR2(1024);
179
180 C_REPORT_PARAMETER_CLASS CONSTANT PARAMETER_CLASS := PARAMETER_CLASS (
181 'BIS_CURRENT_ASOF_DATE',
182 'BIS_CURRENT_EFFECTIVE_END_DATE',
183 'BIS_CURRENT_EFFECTIVE_START_DATE',
184 'BIS_CURRENT_REPORT_START_DATE',
185 'BIS_CUR_REPORT_START_DATE',
186 'BIS_FXN_NAME',
187 'BIS_ICX_SESSION_ID',
188 'BIS_PERIOD_TYPE',
189 'BIS_PREVIOUS_ASOF_DATE',
190 'BIS_PREVIOUS_EFFECTIVE_END_DATE',
191 'BIS_PREVIOUS_EFFECTIVE_START_DATE',
192 'BIS_PREVIOUS_REPORT_START_DATE',
193 'BIS_PREV_REPORT_START_DATE',
194 'BIS_P_ASOF_DATE',
195 'BIS_REGION_CODE',
196 'BIS_SELECTED_TOP_MANAGER',
197 'BIS_TIME_COMPARISON_TYPE',
198 'BIS_TOP_MANAGERS',
199 'ORDERBY',
200 'PERIOD_TYPE',
201 'TIME_COMPARISON_TYPE',
202 'VIEW_BY',
203 '_LOCAL_TIME_PARAM');
204
205
206 /*
207 Constants for package parser
208 */
209
210 C_PACKAGE_BODY CONSTANT VARCHAR2(20) := 'PACKAGE BODY';
211 C_PACKAGE_SPECIFICATION CONSTANT VARCHAR2(20) := 'PACKAGE';
212 C_PACKAGE_OWNER CONSTANT VARCHAR2(20) := 'APPS';
213 C_PLSQL_TOKEN_FUNCTION CONSTANT VARCHAR2(30) := 'FUNCTION';
214 C_PACKAGE_STATUS_VALID VARCHAR2(20) := 'VALID';
215 C_PACKAGE_STATUS_INVALID VARCHAR2(20) := 'INVALID';
216 C_PLSQL_TOKEN_PROCEDURE CONSTANT VARCHAR2(30) := 'PROCEDURE';
217
218
219
220 -----------------------------------------------------------------------------
221 -- Debugging functions
222 -----------------------------------------------------------------------------
223 PROCEDURE enable_debug;
224 PROCEDURE enable_debug( buffer_size NUMBER );
225 PROCEDURE disable_debug;
226 PROCEDURE print_debug( line IN VARCHAR2 ) ;
227 PROCEDURE print_debug( str VARCHAR2, print_level NUMBER );
228 PROCEDURE print_fcn_label( p_label VARCHAR2 );
229 PROCEDURE print_fcn_label2( p_label VARCHAR2 );
230
231 -----------------------------------------------------------------------------
232 TYPE t_array_of_number IS TABLE OF NUMBER
233 INDEX BY BINARY_INTEGER;
234
235 TYPE t_array_of_varchar2 IS TABLE OF VARCHAR2(32000)
236 INDEX BY BINARY_INTEGER;
237 -----------------------------------------------------------------------------
238 --
239 -- x_mode:
240 -- 'N': no insert.
241 -- 'I': insert into BSC_MESSAGE_LOGS immediately.
242 --
243 Procedure Debug(
244 x_calling_fn IN Varchar2,
245 x_debug_msg IN Varchar2 := NULL,
246 x_mode IN Varchar2 := 'N'
247 );
248
249
250 -----------------------------------------------------------------------------
251 -- database Functions/Procedures
252 -----------------------------------------------------------------------------
253 PROCEDURE close_cursor(p_cursor_handle IN OUT NOCOPY NUMBER);
254
255 Procedure Do_SQL(
256 x_sql_stmt IN Varchar2,
257 x_calling_fn IN Varchar2
258 );
259
260 Procedure Do_Rollback;
261
262 -----------------------------------------------------------------------------
263 -- Procedure: update_edw_flag (for BSC v5.0)
264 -----------------------------------------------------------------------------
265
266 -- Purpose: To ENABLE/DISABLE bsc_sys_init.property_code = 'EDW_INSTALLED'.
267 -- This procedure is called by BUILDER.
268 --
269 -- Arguments
270 --
271 -- h_call_proc_name: Calling Function/Procedure name.
272 -- h_mode: ENABLE/DISABLE mode.
273 --
274 -----------------------------------------------------------------------------
275
276 PROCEDURE update_edw_flag(
277 h_call_proc_name IN VARCHAR2,
278 h_mode IN VARCHAR2);
279
280 -----------------------------------------------------------------------------
281 -- Function: is_edw_installed (for BSC v5.0)
282 -----------------------------------------------------------------------------
283
284 -- Purpose: To ENABLE/DISABLE menu item 'EDW' in Builder. This function will
285 -- check if EDW and BSC patch are installed. If they are, then
286 -- Builder will show menu item 'EDW' enabled, otherwise, disabled.
287 --
288 -- This function is called by BUILDER.
289 --
290 -- Arguments
291 --
292 -- h_call_proc_name: Calling Function/Procedure name.
293 --
294 -- Return code
295 --
296 -- 1 = EDW installed
297 -- 0 = EDW not installed
298 --
299 -----------------------------------------------------------------------------
300
301 FUNCTION is_edw_installed(h_call_proc_name IN VARCHAR2) RETURN NUMBER;
302
303 -----------------------------------------------------------------------------
304
305 /* The following function is used to get the dataset id for an analysis
306 option. A function is needed to do this because of the way
307 BSC_KPI_ANALYSIS_MEASURES_B handles analysis option ids, it has different
308 columns for the different analysis groups. This Function in a way
309 normalizes these columns.
310 Parameters for the function are: BSC KPI Id, Analysis Option group Id,
311 Analysis Option Id.
312 */
313
314 function get_dataset_id(
315 p_kpi_id number
316 ,p_option_group_id number
317 ,p_option_id number
318 ) return number;
319
320 -----------------------------------------------------------------------------
321
322 /* The following function is used to obtain the dimension levels for a given
323 Analysis Option. This function returns all dimension levels in a single
324 string.
325 */
326
327 function get_kpi_dim_levels(
328 p_kpi_id number
329 ,p_dim_set_id number
330 ) return varchar2;
331
332 function get_kpi_dim_level_short_names(
333 p_kpi_id number
334 ,p_dim_set_id number
335 ) return varchar2;
336
337 function get_system_timestamp (
338 x_return_status OUT NOCOPY varchar2
339 ,x_msg_count OUT NOCOPY number
340 ,x_msg_data OUT NOCOPY varchar2
341 ) return varchar2;
342
343 function get_session_error(
344 x_return_status OUT NOCOPY varchar2
345 ,x_msg_count OUT NOCOPY number
346 ,x_msg_data OUT NOCOPY varchar2
347 ) return varchar2;
348
349
350 FUNCTION ListToNumericArray(
351 x_string IN VARCHAR2,
352 x_number_array IN OUT NOCOPY t_array_of_number,
353 x_separator IN VARCHAR2
354 ) RETURN NUMBER;
355
356 FUNCTION ListToStringArray(
357 x_string IN VARCHAR2,
358 x_array IN OUT NOCOPY t_array_of_varchar2,
359 x_separator IN VARCHAR2
360 ) RETURN NUMBER;
361
362 PROCEDURE Add_To_Fnd_Msg_Stack
363 (p_error_tbl IN BIS_UTILITIES_PUB.ERROR_TBL_TYPE
364 ,x_msg_count OUT NOCOPY NUMBER
365 ,x_msg_data OUT NOCOPY VARCHAR2
366 ,x_return_status OUT NOCOPY VARCHAR2
367 );
368
369 FUNCTION is_Internal_User RETURN BOOLEAN;
370
371 /**************************************************************************************
372 FUNCTION get_Next_DispName
373
374 Function to generated names as required by Bug 3137260 , for example if Country
375 is passed we will get 'Country 1' if 'Country 5' is passed, we will get
376 'Country 6', if 'Country A' is passed, we will get 'Country A 1', etc.
377 **************************************************************************************/
378
379 FUNCTION get_Next_DispName
380 (
381 p_Alias IN VARCHAR2
382 ) RETURN VARCHAR2;
383
384 FUNCTION get_Next_Name (
385 p_Name IN VARCHAR2
386 ,p_Max_Count IN NUMBER
387 ,p_Table_Name IN VARCHAR2
388 ,p_Column_Name IN VARCHAR2
389 ,p_Character IN CHAR
390 ) RETURN VARCHAR2;
391
392 /*********************************************************************************
393 This function is used to get no of independent dimension objects in a dimension set
394 of a given objective
395 /*********************************************************************************/
396 FUNCTION get_nof_independent_dimobj
397 ( p_kpi_id IN NUMBER
398 , p_dim_set_id IN NUMBER
399 )RETURN NUMBER;
400 /*********************************************************************************/
401
402
403 /*********************************************************************************
404 FUNCTION isBscInProductionMode
405 *********************************************************************************/
406
407 FUNCTION isBscInProductionMode
408 RETURN BOOLEAN;
409 /*********************************************************************************/
410 FUNCTION is_MV_Exists(
411 p_MV_Name IN VARCHAR2
412 ) RETURN BOOLEAN;
413 /*********************************************************************************/
414 FUNCTION is_View_Exists(
415 p_View_Name IN VARCHAR2
416 ) RETURN BOOLEAN;
417 /*********************************************************************************/
418 FUNCTION is_Table_Exists(
419 p_Table_Name IN VARCHAR2
420 ) RETURN BOOLEAN;
421 /*********************************************************************************/
422 FUNCTION is_Table_View_Exists(
423 p_Table_View_Name IN VARCHAR2
424 ) RETURN BOOLEAN;
425 /*********************************************************************************/
426 FUNCTION get_owner_for_object(
427 p_object_name IN VARCHAR2
428 ) RETURN VARCHAR2;
429 /*********************************************************************************/
430 FUNCTION is_Indicator_In_Production(
431 p_kpi_id IN NUMBER
432 ) RETURN BOOLEAN;
433 /*********************************************************************************/
434 FUNCTION Is_BSC_Licensed
435 RETURN VARCHAR2;
436 /*********************************************************************************/
437 FUNCTION Is_Adv_Summarization_Enabled
438 RETURN VARCHAR2;
439 /*********************************************************************************/
440 FUNCTION Get_Default_Internal_Name(
441 p_type IN VARCHAR2
442 )RETURN VARCHAR2;
443 /*********************************************************************************/
444
445 /*********************************************************************************
446 API TO PARSE THE COMMA SEPARATED BASE PERIODS
447 *********************************************************************************/
448 PROCEDURE Parse_String
449 (
450 p_List VARCHAR2
451 , p_Separator VARCHAR2
452 , p_List_Data OUT NOCOPY BSC_UTILITY.varchar_tabletype
453 , p_List_number OUT NOCOPY NUMBER
454 );
455
456 FUNCTION get_Next_Alias
457 (
458 p_Alias IN VARCHAR2
459 ) RETURN VARCHAR2;
460
461 /*********************************************************************************
462 API TO CHECK IF MIXED DIMENSION OBJECTS SHOULD BE ALLOWED AT THE
463 DIMENSION AND DIMENSION SET LEVEL
464 *********************************************************************************/
465
466 FUNCTION is_Mix_Dim_Objects_Allowed
467 RETURN VARCHAR2;
468
469 FUNCTION get_valid_bsc_master_tbl_name
470 (
471 p_short_name IN VARCHAR2
472 )
473 RETURN VARCHAR2;
474
475
476 FUNCTION Is_Time_Period_Type (
477 p_Dimension_Short_Name IN VARCHAR2
478 , p_Dimension_Object_Short_Name IN VARCHAR2
479 ) RETURN VARCHAR2;
480
481 FUNCTION is_dim_time_period_type (
482 p_dimension_short_name IN VARCHAR2
483 ) RETURN VARCHAR2;
484
485 FUNCTION Is_Dim_Object_Periodicity_Type (
486 p_Dimension_Object_Short_Name IN VARCHAR2
487 ) RETURN VARCHAR2;
488
489
490 /*
491 Dimension Designer validation APIs for multiple entity
492 */
493 PROCEDURE Enable_Dimensions_Entity (
494 p_Entity_Type IN VARCHAR2
495 , p_Entity_Short_Names IN VARCHAR2
496 , p_Entity_Action_Type IN VARCHAR2
497 , x_Return_Status OUT NOCOPY VARCHAR2
498 , x_Msg_Count OUT NOCOPY NUMBER
499 , x_Msg_Data OUT NOCOPY VARCHAR2
500 );
501
502 /*
503 Dimension Designer validation APIs
504 This API returns "S" under x_Return_Status if the Dimension
505 entity can be enabled, else it returns "E" or "U" with
506 a valid error message text
507 */
508 PROCEDURE Enable_Dimension_Entity (
509 p_Entity_Type IN VARCHAR2
510 , p_Entity_Short_Name IN VARCHAR2
511 , p_Entity_Action_Type IN VARCHAR2
512 , p_Entity_Name IN VARCHAR2
513 , x_Return_Status OUT NOCOPY VARCHAR2
514 , x_Msg_Count OUT NOCOPY NUMBER
515 , x_Msg_Data OUT NOCOPY VARCHAR2
516 );
517
518 PROCEDURE Check_Weighted_Dimension (
519 p_Dim_Short_Names IN VARCHAR2
520 , x_Return_Status OUT NOCOPY VARCHAR2
521 , x_Msg_Count OUT NOCOPY NUMBER
522 , x_Msg_Data OUT NOCOPY VARCHAR2
523 );
524
525 FUNCTION Is_More
526 ( p_comma_sep_values IN OUT NOCOPY VARCHAR2
527 ,x_value OUT NOCOPY VARCHAR2
528 ) RETURN BOOLEAN;
529
530 /****************************************************************************************************
531 This functions returns an unique time based short name .
532 It Prefixes the word based on type of the object sent in parameter p_Object_Type
533 ****************************************************************************************************/
534
535 FUNCTION Get_Unique_Sht_Name_By_Obj_Typ(p_Object_Type IN VARCHAR2)
536 RETURN VARCHAR2;
537
538 FUNCTION Is_Internal_Dimension(p_Short_Name IN VARCHAR2)
539 RETURN VARCHAR2;
540
541 FUNCTION Is_Dim_In_AKReport(
542 p_Short_Name IN VARCHAR2
543 , p_Entity_Type IN VARCHAR2 := c_MIXED_DIM_OBJS
544 )
545 RETURN VARCHAR2;
546
547 PROCEDURE Is_Dim_Obj_In_AKReport(
548 p_Short_Names IN VARCHAR2
549 , x_region_codes OUT NOCOPY FND_TABLE_OF_VARCHAR2_30
550 , x_Return_Status OUT NOCOPY VARCHAR2
551 , x_Msg_Count OUT NOCOPY NUMBER
552 , x_Msg_Data OUT NOCOPY VARCHAR2
553 );
554
555 FUNCTION Get_Report_Name(
556 p_Region_Code IN VARCHAR2
557 )
558 RETURN VARCHAR2;
559
560 FUNCTION Get_Responsibility_Key RETURN VARCHAR2;
561
562 /****************************************************************************************************
563 These three apis are taken from Is_Internal_Dim. They are required for fixes of bug#4602231
564 where the Dimension LOV window will only call Is_Internal_AG_Dim and Is_Internal_BIS_Import_Dim
565 to boost LOV performance. Is_Internal_WKPI_Dim is causing performance issue since it queries
566 ak_regions without using indexed columns.
567 ****************************************************************************************************/
568 FUNCTION Is_Internal_AG_Dim(p_Short_Name IN VARCHAR2) RETURN VARCHAR2;
569 FUNCTION Is_Internal_BIS_Import_Dim(p_Short_Name IN VARCHAR2) RETURN VARCHAR2;
570 FUNCTION Is_Internal_WKPI_Dim(p_Short_Name IN VARCHAR2) RETURN VARCHAR2;
571 FUNCTION Is_Internal_VB_Dim(p_Short_Name IN VARCHAR2) RETURN VARCHAR2;
572
573
574 -- added for Bug#4599432
575 FUNCTION Is_Measure_Seeded (p_Short_Name IN VARCHAR2) RETURN VARCHAR2;
576
577
578 FUNCTION Get_User_Time
579 (
580 p_current_user_time IN DATE
581 , p_date_format IN VARCHAR2
582 ) RETURN VARCHAR2;
583
584
585 /****************************************************************************************************
586
587 Implementation of SQL Parser Starts from here
588
589
590 Current Implementation Algorithm
591 --------------------------------
592
593 STEP#1: Validate the PL/SQL Procedure passed down from the API
594 STEP#1A: Existential check
595 STEP#1B: Check if the package has both spec/body
596 STEP#1C: Check if the package body/speck has any errors
597
598 STEP#2: Obtain the parameter to pass to the PL/SQL package.
599 ****************************************************************************************************/
600
601 PROCEDURE Validate_Plsql_For_Report (
602 p_Region_Code IN VARCHAR2
603 , p_Region_Application_Id IN VARCHAR2
604 , p_Plsql_Function IN VARCHAR2
605 , p_Attribute_Code IN VARCHAR2
606 , p_Attribute1 IN VARCHAR2
607 , p_Attribute2 IN VARCHAR2
608 , p_Attribute3 IN VARCHAR2
609 , p_Default_Values IN VARCHAR2
610 , x_Return_Status OUT NOCOPY VARCHAR2
611 , x_Msg_Count OUT NOCOPY NUMBER
612 , x_Msg_Data OUT NOCOPY VARCHAR2
613 );
614
615 -- This API returns the parameter being used for a PL/SQL API, which should have the one parameter
616 -- and should take the type BIS_PMV_PAGE_PARAMETER_TBL
617 PROCEDURE Get_Plsql_Parameters (
618 p_Report_Function IN VARCHAR2
619 , x_Parameter_1 OUT NOCOPY VARCHAR2
620 , x_Parameter_2 OUT NOCOPY VARCHAR2
621 , x_Parameter_3 OUT NOCOPY VARCHAR2
622 , x_Parameter_1_type OUT NOCOPY VARCHAR2
623 , x_Parameter_2_type OUT NOCOPY VARCHAR2
624 , x_Parameter_3_type OUT NOCOPY VARCHAR2
625 , x_Parameter_1_var OUT NOCOPY VARCHAR2
626 , x_Parameter_2_var OUT NOCOPY VARCHAR2
627 , x_Parameter_3_var OUT NOCOPY VARCHAR2
628 );
629
630
631 -- this API trims all moving comma's to a single comma.
632 FUNCTION Remove_Repeating_Comma (
633 p_String IN VARCHAR2
634 ) RETURN VARCHAR2;
635
636 -- this API does an existential validation on the PL/SQL Package/function.
637 PROCEDURE Validate_PLSQL (
638 p_Plsql_Function IN VARCHAR2
639 , x_Return_Status OUT NOCOPY VARCHAR2
640 , x_Msg_Count OUT NOCOPY NUMBER
641 , x_Msg_Data OUT NOCOPY VARCHAR2
642 );
643
644 PROCEDURE Obtain_Report_Query (
645 p_Region_Code IN VARCHAR2
646 , p_Region_Application_Id IN VARCHAR2
647 , p_Plsql_Function IN VARCHAR2
648 , p_Attribute_Code IN VARCHAR2
649 , p_Attribute1 IN VARCHAR2
650 , p_Attribute2 IN VARCHAR2
651 , p_Attribute3 IN VARCHAR2
652 , p_Default_Values IN VARCHAR2
653 , x_Custom_Sql OUT NOCOPY VARCHAR2
654 , x_Custom_Output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
655 , x_Custom_Columns OUT NOCOPY VARCHAR2
656 , x_Return_Status OUT NOCOPY VARCHAR2
657 , x_Msg_Count OUT NOCOPY NUMBER
658 , x_Msg_Data OUT NOCOPY VARCHAR2
659 );
660
661 PROCEDURE Insert_Into_Query_Table (
662 x_Param_Table IN OUT NOCOPY BIS_PMV_PAGE_PARAMETER_TBL
663 , p_Parameter_Name IN VARCHAR2
664 , p_Parameter_Id IN VARCHAR2
665 , p_Parameter_Value IN VARCHAR2
666 , p_Dimension IN VARCHAR2
667 , p_Period_Date IN DATE
668 , p_Operator IN VARCHAR2
669 );
670
671
672 -- Procedure to perform transactions autonomously
673 PROCEDURE Do_DDL_AT(
674 p_Statement IN VARCHAR2,
675 p_Statement_Type IN INTEGER,
676 p_Object_Name IN VARCHAR2,
677 p_Fnd_Apps_Schema IN VARCHAR2,
678 p_Apps_Short_Name IN VARCHAR2
679 );
680
681 PROCEDURE Validate_Sql_String (
682 p_Sql_String IN VARCHAR2
683 , x_Columns OUT NOCOPY VARCHAR2
684 , x_Return_Status OUT NOCOPY VARCHAR2
685 , x_Msg_Count OUT NOCOPY NUMBER
686 , x_Msg_Data OUT NOCOPY VARCHAR2
687 );
688
689 FUNCTION Sort_String (
690 p_String IN VARCHAR2
691 ) RETURN VARCHAR2;
692
693 FUNCTION is_bsc_measure_convertible (
694 p_dataset_id IN NUMBER
695 , p_region_code IN VARCHAR2
696 ) RETURN VARCHAR2;
697
698 FUNCTION is_src_col_in_formulas (
699 p_Source_Col IN VARCHAR2
700 ) RETURN VARCHAR2;
701
702 /*****************************************************
703 UTILITY FUNCTION TO RETURN A UNIQUE MERGED LIST
704 *****************************************************/
705
706 FUNCTION Create_Unique_Comma_List (
707 p_List1 IN VARCHAR2,
708 p_List2 IN VARCHAR2
709 ) RETURN VARCHAR2;
710
711 /*****************************************************************************
712 UTILITY FUNCTION TO RETURN A UNIQUE LIST of TYPE BSC_UTILITY.VARCHAR_TABLETYPE
713 ******************************************************************************/
714 FUNCTION Get_Unique_List (p_List IN BSC_UTILITY.VARCHAR_TABLETYPE)
715 RETURN BSC_UTILITY.varchar_tabletype;
716
717 FUNCTION is_Calculated_kpi
718 (
719 p_Measure_Short_Name IN VARCHAR2
720 )RETURN VARCHAR2;
721
722
723 FUNCTION Is_Meas_Used_In_Targets
724 (
725 p_Dataset_Id IN BSC_SYS_DATASETS_VL.dataset_id%TYPE
726 ) RETURN VARCHAR2;
727
728
729 FUNCTION Is_Wam_Kpi
730 (
731 p_dataset_id IN BSC_SYS_DATASETS_VL.dataset_id%TYPE
732 )RETURN VARCHAR2;
733
734
735 FUNCTION is_Calculated_kpi
736 (
737 p_dataset_id IN BSC_SYS_DATASETS_B.dataset_id%TYPE
738 )RETURN VARCHAR2;
739
740
741 FUNCTION Is_Report_Primary_Data_Source
742 (
743 p_Indicator IN BSC_KPIS_B.indicator%TYPE
744 ,p_Dataset_Id IN BSC_SYS_DATASETS_B.dataset_id%TYPE
745 ) RETURN VARCHAR2;
746
747 FUNCTION Is_Meas_Used_In_Wam_Report
748 (
749 p_dataset_id IN BSC_SYS_DATASETS_B.dataset_id%TYPE
750 )RETURN VARCHAR2;
751
752
753 PROCEDURE comp_leapyear_prioryear(
754 p_calid IN NUMBER,
755 p_cyear IN NUMBER,
756 p_pyear IN NUMBER,
757 x_result OUT nocopy NUMBER
758 );
759 END BSC_UTILITY;
[Home] [Help]