1 PACKAGE BSC_BIS_KPI_MEAS_PUB AS
2 /* $Header: BSCKPMDS.pls 120.4 2007/04/13 12:24:41 ankgoel ship $ */
3 /*
4 REM +=======================================================================+
5 REM | Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA |
6 REM | All rights reserved. |
7 REM +=======================================================================+
8 REM | FILENAME |
9 REM | BSCKPMDS.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Module: Wrapper for Dimension, part of PMD APIs |
13 REM | |
14 REM | NOTES |
15 REM | 14-FEB-2003 PAJOHRI Created. |
16 REM | 20-Sep-2003 ADEULGAO fixed bug#3126401 |
17 REM | 04-NOV-2003 PAJOHRI Bug #3152258 |
18 REM | 08-DEC-2003 KYADAMAK Bug #3225685 |
19 REM | 14-JUN-2004 ADRAO added Short_Name to Analysis Option for Enh#3540302 |
20 REM | (ADMINISTRATOR TO ADD KPI TO KPI REGION) |
21 REM | 17-AUG-2004 WLEUNG added function Remove_Empty_Dims_For_DimSet |
22 REM | Bug #3784852 |
23 REM | 29-SEP-2004 ashankar added modules is_Period_Circular, |
24 REM | Parse_Base_Periods and Find_Period_CircularRef |
25 REM | for bug#3908204 |
26 REM | 10-OCT-2004 ashankar Moved Parse_Base_Periods to BSC_UTILITY package |
27 REM | and renamed it to Parse_String to make it Generic|
28 REM | enough.This was done as per the review comment |
29 REM | 22-AUG-2005 ashankar Bug#4220400 Made public store_kpi_anal_group |
30 REM | 15-FEB-2006 akoduri Bug#4305536 Support new attribute type in |
31 REM | Objective designer |
32 REM | 31-Jan-2007 akoduri Enh #5679096 Migration of multibar functionality |
33 REM | from VB to Html |
34 REM | 13-APR-2007 ankgoel Bug#5943068 Impact on common dimension by dim |
35 REM | reorder in a dim set |
36 REM +=======================================================================+
37 */
38
39 /*******************************************************************
40 Adeulgao changed bug#3126401
41 description: Moved this from body to here to
42 to make it public.
43 *******************************************************************/
44 CONFIG_LIMIT_DIM CONSTANT NUMBER := 8;
45 CONFIG_LIMIT_DIMSET CONSTANT NUMBER := 2;
46 CONFIG_LIMIT_RELS CONSTANT NUMBER := 5;
47 COMMA_SEPARATOR CONSTANT VARCHAR2(3) :=',';
48 CIR_REF_EXISTS CONSTANT VARCHAR2(3) := 'Y';
49 CIR_REF_NOTEXISTS CONSTANT VARCHAR2(3) := 'N';
50
51 c_VIEWBY CONSTANT VARCHAR2(6) := 'VIEWBY';
52 c_ALL CONSTANT VARCHAR2(3) := 'ALL';
53 C_HIDE_DIM_OBJ CONSTANT VARCHAR2(4) := 'HIDE';
54 -- TYPE varchar_tabletype IS TABLE OF varchar2(32000) INDEX BY binary_integer;
55
56
57 TYPE DimObj_Viewby_Rec_Type IS RECORD
58 ( p_Measure_Short_Name BSC_SYS_MEASURES.Short_Name%TYPE
59 , p_Region_Code VARCHAR2(30)
60 , p_Function_Code VARCHAR2(30)
61 , p_Is_Time_There BOOLEAN
62 , p_Dimension_Name VARCHAR2(2000)
63 , p_Dim_Object_Names VARCHAR2(8000)
64 , p_View_By_There VARCHAR2(8000)
65 , p_All_There VARCHAR2(8000)
66 );
67 /*******************************************************************/
68 TYPE DimObj_Viewby_Tbl_Type IS TABLE OF DimObj_Viewby_Rec_Type INDEX BY BINARY_INTEGER;
69 /*******************************************************************
70 Adeulgao changed bug#3126401
71 description: Moved this from body to here to
72 to make it public.
73 *******************************************************************/
74 PROCEDURE Get_Dimobj_Viewby_Tbl
75 ( p_Measure_Short_Name IN VARCHAR2
76 , p_Region_Code IN VARCHAR2
77 , x_DimObj_ViewBy_Tbl OUT NOCOPY BSC_BIS_KPI_MEAS_PUB.DimObj_Viewby_Tbl_Type
78 , x_return_status OUT NOCOPY VARCHAR2
79 , x_msg_count OUT NOCOPY NUMBER
80 , x_msg_data OUT NOCOPY VARCHAR2
81 );
82
83 FUNCTION Get_Dimobj_Properties
84 ( p_Measure_Short_Name IN VARCHAR2
85 , p_Dim_Obj_Short_Name IN VARCHAR2
86 , p_Property_Type IN VARCHAR2
87 ) RETURN VARCHAR2;
88
89 /*********************************************************************************
90 CREATE DIMENSION-SETS
91 *********************************************************************************/
92 PROCEDURE Create_Dim_Set
93 (
94 p_commit IN VARCHAR2 := FND_API.G_TRUE
95 , p_kpi_id IN NUMBER
96 , p_display_name IN VARCHAR2
97 , p_dim_short_names IN VARCHAR2
98 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
99 , p_dim_set_short_name IN VARCHAR2 := NULL
100 , x_return_status OUT NOCOPY VARCHAR2 -- Send the KPI Time Stamp
101 , x_msg_count OUT NOCOPY NUMBER
102 , x_msg_data OUT NOCOPY VARCHAR2
103 );
104 /*********************************************************************************
105 UPDATE DIMENSION-SETS
106 *********************************************************************************/
107 PROCEDURE Update_Dim_Set
108 (
109 p_commit IN VARCHAR2 := FND_API.G_TRUE
110 , p_kpi_id IN NUMBER
111 , p_dim_set_id IN NUMBER
112 , p_display_name IN VARCHAR2
113 , p_assign_dim_names IN VARCHAR2
114 , p_unassign_dim_names IN VARCHAR2
115 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
116 , x_return_status OUT NOCOPY VARCHAR2
117 , x_msg_count OUT NOCOPY NUMBER
118 , x_msg_data OUT NOCOPY VARCHAR2
119 );
120 /*********************************************************************************
121 UPDATE DIMENSION-SETS
122 *********************************************************************************/
123 PROCEDURE Update_Dim_Set
124 (
125 p_commit IN VARCHAR2 := FND_API.G_TRUE
126 , p_kpi_id IN NUMBER
127 , p_dim_set_id IN NUMBER
128 , p_display_name IN VARCHAR2
129 , p_dim_short_names IN VARCHAR2
130 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
131 , x_return_status OUT NOCOPY VARCHAR2
132 , x_msg_count OUT NOCOPY NUMBER
133 , x_msg_data OUT NOCOPY VARCHAR2
134 );
135
136 /*********************************************************************************
137 UPDATE DIMENSION-SET
138 *********************************************************************************/
139 PROCEDURE Update_Dim_Set
140 (
141 p_commit IN VARCHAR2 := FND_API.G_TRUE
142 , p_kpi_id IN NUMBER
143 , p_dim_set_id IN NUMBER
144 , p_display_name IN VARCHAR2
145 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
146 , x_return_status OUT NOCOPY VARCHAR2
147 , x_msg_count OUT NOCOPY NUMBER
148 , x_msg_data OUT NOCOPY VARCHAR2
149 );
150 /*********************************************************************************
151 DELETE DIMENSION-SETS
152 *********************************************************************************/
153 PROCEDURE Delete_Dim_Set
154 (
155 p_commit IN VARCHAR2 := FND_API.G_TRUE
156 , p_kpi_id IN NUMBER
157 , p_dim_set_id IN NUMBER
158 , x_return_status OUT NOCOPY VARCHAR2
159 , x_msg_count OUT NOCOPY NUMBER
160 , x_msg_data OUT NOCOPY VARCHAR2
161 );
162 /*********************************************************************************
163 ASSIGN DIMENSION TO DIMENSION-SETS
164 *********************************************************************************/
165 PROCEDURE Assign_Dims_To_Dim_Set
166 (
167 p_commit IN VARCHAR2 := FND_API.G_TRUE
168 , p_kpi_id IN NUMBER
169 , p_dim_set_id IN NUMBER
170 , p_dim_short_names IN VARCHAR2
171 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
172 , x_return_status OUT NOCOPY VARCHAR2
173 , x_msg_count OUT NOCOPY NUMBER
174 , x_msg_data OUT NOCOPY VARCHAR2
175 );
176 /*********************************************************************************
177 REMOVE DIMENSION FROM DIMENSION-SETS
178 *********************************************************************************/
179 PROCEDURE Unassign_Dims_From_Dim_Set
180 (
181 p_commit IN VARCHAR2 := FND_API.G_TRUE
182 , p_kpi_id IN NUMBER
183 , p_dim_set_id IN NUMBER
184 , p_dim_short_names IN VARCHAR2
185 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
186 , x_return_status OUT NOCOPY VARCHAR2
187 , x_msg_count OUT NOCOPY NUMBER
188 , x_msg_data OUT NOCOPY VARCHAR2
189 );
190
191 /*********************************************************************************
192 ASSIGN DIMENSION TO DIMENSION-SETS
193 *********************************************************************************/
194 PROCEDURE Assign_Unassign_Dimensions
195 (
196 p_commit IN VARCHAR2 := FND_API.G_TRUE
197 , p_kpi_id IN NUMBER
198 , p_dim_set_id IN NUMBER
199 , p_assign_dim_names IN VARCHAR2
200 , p_unassign_dim_names IN VARCHAR2
201 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
202 , x_return_status OUT NOCOPY VARCHAR2
203 , x_msg_count OUT NOCOPY NUMBER
204 , x_msg_data OUT NOCOPY VARCHAR2
205 );
206
207 /*********************************************************************************
208 ASSIGN DIMENSION-SETS
209 *********************************************************************************/
210 PROCEDURE Assign_DSet_Analysis_Options
211 (
212 p_commit IN VARCHAR2 := FND_API.G_TRUE
213 , p_kpi_id IN NUMBER
214 , p_dim_set_id IN NUMBER
215 , p_analysis_grp_id IN NUMBER
216 , p_option_id IN NUMBER
217 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
218 , x_return_status OUT NOCOPY VARCHAR2
219 , x_msg_count OUT NOCOPY NUMBER
220 , x_msg_data OUT NOCOPY VARCHAR2
221 );
222 /*********************************************************************************
223 API TO UPDATE PMF/BSC MEASURES/ANALYSIS OPTIONS WITHIN AN INDICATOR
224 *********************************************************************************/
225 PROCEDURE Update_KPI_Analysis_Options
226 (
227 p_commit IN VARCHAR2 := FND_API.G_TRUE
228 , p_kpi_id IN NUMBER
229 , p_data_source IN VARCHAR2
230 , p_analysis_group_id IN NUMBER
231 , p_analysis_option_id0 IN NUMBER
232 , p_analysis_option_id1 IN NUMBER
233 , p_analysis_option_id2 IN NUMBER
234 , p_series_id IN NUMBER
235 , p_data_set_id IN NUMBER
236 , p_dim_set_id IN NUMBER
237 , p_option0_Name IN VARCHAR2
238 , p_option1_Name IN VARCHAR2
239 , p_option2_Name IN VARCHAR2
240 , p_measure_short_name IN VARCHAR2
241 , p_dim_obj_short_names IN VARCHAR2 --comma seperated dimension objects needed for PMF Measures
242 , p_default_short_names IN VARCHAR2 := NULL
243 , p_view_by_name IN VARCHAR2 := NULL
244 , p_measure_name IN VARCHAR2 --BSC_KPI_ANALYSIS_MEASURES_VL.name
245 , p_measure_help IN VARCHAR2 --BSC_KPI_ANALYSIS_MEASURES_VL.help
246 , p_default_value IN NUMBER
247 , p_time_stamp IN VARCHAR2 := NULL
248 , p_update_ana_opt IN BOOLEAN := FALSE
249 , x_return_status OUT NOCOPY VARCHAR2
250 , x_msg_count OUT NOCOPY NUMBER
251 , x_msg_data OUT NOCOPY VARCHAR2
252 );
253
254 FUNCTION get_DimensionSetSource
255 (
256 p_Indicator IN NUMBER
257 , p_DimSetId IN NUMBER
258 ) RETURN VARCHAR2;
259
260 --ASHANKAR added on 09-Jun-2003
261 FUNCTION GET_AO_NAME
262 (
263 p_indicator in NUMBER
264 , p_a0 in NUMBER
265 , p_a1 in NUMBER
266 , p_a2 in NUMBER
267 , p_group_id in NUMBER
268 ) RETURN VARCHAR2;
269
270 FUNCTION GET_SERIES_COUNT
271 (
272 p_indicator IN NUMBER
273 , p_a0 IN NUMBER
274 , p_a1 IN NUMBER
275 , p_a2 IN NUMBER
276 ) RETURN NUMBER;
277
278 /************************************************************************************
279 UPDATE KPIS
280 ************************************************************************************/
281 PROCEDURE Update_Kpi
282 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
283 , p_kpi_id IN NUMBER
284 , p_kpi_name IN VARCHAR2
285 , p_kpi_help IN VARCHAR2 := NULL
286 , p_responsibility_id IN NUMBER := NULL
287 , p_default_value IN NUMBER
288 , p_BM_Property_Value IN NUMBER := BSC_KPI_PUB.Benchmark_Kpi_Line_Graph -- 0 For Lines and 1 for Bars
289 , p_time_stamp IN VARCHAR2 := NULL
290 , p_Anal_opt0 IN BSC_KPI_ANALYSIS_MEASURES_B.analysis_option0%TYPE
291 , p_Anal_opt1 IN BSC_KPI_ANALYSIS_MEASURES_B.analysis_option1%TYPE
292 , p_Anal_opt2 IN BSC_KPI_ANALYSIS_MEASURES_B.analysis_option2%TYPE
293 , p_Anal_Series IN BSC_KPI_ANALYSIS_MEASURES_B.series_id%TYPE
294 , x_return_status OUT NOCOPY VARCHAR2
298 /************************************************************************************
295 , x_msg_count OUT NOCOPY NUMBER
296 , x_msg_data OUT NOCOPY VARCHAR2
297 );
299 ************************************************************************************/
300
301 PROCEDURE Create_Kpi
302 (
303 p_commit IN VARCHAR2 := FND_API.G_TRUE
304 , p_group_id IN NUMBER
305 , p_kpi_name IN VARCHAR2
306 , p_kpi_help IN VARCHAR2
307 , p_responsibility_id IN NUMBER
308 , x_return_status OUT NOCOPY VARCHAR2
309 , x_msg_count OUT NOCOPY NUMBER
310 , x_msg_data OUT NOCOPY VARCHAR2
311 );
312 /*********************************************************************************
313 API TO DELETE PMF/BSC MEASURES/ANALYSIS OPTIONS WITHIN AN INDICATOR
314 *********************************************************************************/
315 /*PROCEDURE Delete_KPI_Analysis_Options
316 (
317 p_commit IN VARCHAR2 := FND_API.G_TRUE
318 , p_kpi_id IN NUMBER
319 , p_data_source IN VARCHAR2
320 , p_option_id IN NUMBER
321 , p_time_stamp IN VARCHAR2 := NULL
322 , x_return_status OUT NOCOPY VARCHAR2
323 , x_msg_count OUT NOCOPY NUMBER
324 , x_msg_data OUT NOCOPY VARCHAR2
325 );
326 /*********************************************************************************
327 API TO CREATE PMF/BSC MEASURES/ANALYSIS OPTIONS WITHIN AN INDICATOR
328 *********************************************************************************/
329 -- ADRAO added Short_Name to Analysis Option for Enh#3540302 (ADMINISTRATOR TO ADD KPI TO KPI REGION)
330 PROCEDURE Create_KPI_Analysis_Options
331 (
332 p_commit IN VARCHAR2 := FND_API.G_TRUE
333 , p_kpi_id IN NUMBER
334 , p_analysis_group_id IN NUMBER
335 , p_data_set_id IN NUMBER
336 , p_measure_short_name IN VARCHAR2
337 , p_measure_name IN VARCHAR2
338 , p_measure_help IN VARCHAR2
339 , p_time_stamp IN VARCHAR2 := NULL
340 , p_Short_Name IN VARCHAR2 := NULL
341 , x_return_status OUT NOCOPY VARCHAR2
342 , x_msg_count OUT NOCOPY NUMBER
343 , x_msg_data OUT NOCOPY VARCHAR2
344 );
345
346 /*********************************************************************************
347 API to CREATE DIMENSION-OBJECTS IN DIMENSION SETS USED IN CASCADING
348 *********************************************************************************/
349 PROCEDURE Create_Dim_Objs_In_DSet
350 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
351 , p_kpi_id IN NUMBER
352 , p_dim_set_id IN NUMBER
356 , x_msg_count OUT NOCOPY NUMBER
353 , p_kpi_flag_change IN NUMBER := NULL
354 , p_delete IN BOOLEAN := FALSE
355 , x_return_status OUT NOCOPY VARCHAR2
357 , x_msg_data OUT NOCOPY VARCHAR2
358 );
359 /*********************************************************************************
360 API to DELETE DIMENSION-OBJECTS IN DIMENSION SETS USED IN CASCADING
361 *********************************************************************************/
362 PROCEDURE Delete_Dim_Objs_In_DSet
363 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
364 , p_kpi_id IN NUMBER
365 , p_dim_set_id IN NUMBER
366 , x_return_status OUT NOCOPY VARCHAR2
367 , x_msg_count OUT NOCOPY NUMBER
368 , x_msg_data OUT NOCOPY VARCHAR2
369 );
370 --=============================================================================
371 FUNCTION get_KPIs
372 ( p_Kpi_ID IN NUMBER
373 , p_Dim_Set_ID IN NUMBER
374 ) RETURN VARCHAR2;
375 --=============================================================================
376 FUNCTION is_KPI_Flag_For_Dim_In_DimSets
377 ( p_Kpi_ID IN NUMBER
378 , p_Dim_Set_ID IN NUMBER
379 , p_Unassign_dim_names IN VARCHAR2
380 , p_Dim_Short_Names IN VARCHAR2
381 ) RETURN VARCHAR2;
382 --=============================================================================
383 FUNCTION check_config_impact_dimset
384 ( p_Kpi_ID IN NUMBER
385 , p_Dim_Set_ID IN NUMBER :=NULL
386 , p_Unassign_dim_names IN VARCHAR2
387 , p_Dim_Short_Names IN VARCHAR2
388 ) RETURN VARCHAR2;
389 --============================================================================
390 FUNCTION get_no_rels
391 ( p_dim_obj_sht_names IN VARCHAR2
392 )RETURN NUMBER;
393 --====================================================================
394 /*********************************************************************************
395 API TO DELETE PMF/BSC MEASURES/ANALYSIS OPTIONS WITHIN AN INDICATOR
396 *********************************************************************************/
397 PROCEDURE Delete_KPI_Multi_Groups_Opts
398 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
399 , p_kpi_id IN NUMBER
400 , p_data_source IN VARCHAR2
401 , p_Option_0 IN NUMBER
402 , p_Option_1 IN NUMBER
403 , p_Option_2 IN NUMBER
404 , p_Sid IN NUMBER
405 , p_time_stamp IN VARCHAR2 := NULL
406 , x_return_status OUT NOCOPY VARCHAR2
407 , x_msg_count OUT NOCOPY NUMBER
408 , x_msg_data OUT NOCOPY VARCHAR2
409 );
410
411 /*********************************************************************************
412 API TO GET THE ANALYSIS OPTION COMBINATION MESSAGE
413 *********************************************************************************/
414
415 FUNCTION get_anal_opt_comb_message
416 (
417 p_Kpi_Id IN BSC_KPIS_B.indicator%TYPE
418 , p_Option_0 IN NUMBER
419 , p_Option_1 IN NUMBER
420 , p_Option_2 IN NUMBER
421 , p_Sid IN NUMBER
422 )RETURN VARCHAR2;
423
424 /*********************************************************************************
425 API TO CHECK FOR CIRCULAR REFERENCE AMONG PERIODS
426 *********************************************************************************/
427
428 FUNCTION Find_Period_CircularRef
429 (
430 p_basePeriod IN BSC_SYS_PERIODICITIES.periodicity_id%TYPE
431 , p_current_period IN BSC_SYS_PERIODICITIES.periodicity_id%TYPE
432
433 ) RETURN BOOLEAN;
434
435 FUNCTION is_Period_Circular
436 (
437 p_basePeriod IN BSC_SYS_PERIODICITIES.periodicity_id%TYPE
438 , p_current_period IN BSC_SYS_PERIODICITIES.periodicity_id%TYPE
439 ) RETURN VARCHAR2;
440
441 PROCEDURE check_pmf_validveiw_for_mes
442 (
443 p_dataset_id IN NUMBER
444 , x_dimobj_name OUT NOCOPY VARCHAR2
445 , x_return_status OUT NOCOPY VARCHAR2
446 , x_msg_count OUT NOCOPY NUMBER
447 , x_msg_data OUT NOCOPY VARCHAR2
448
449 );
450
451 PROCEDURE store_kpi_anal_group
452 ( p_kpi_id IN NUMBER
453 , x_Anal_Opt_Tbl IN OUT NOCOPY BSC_ANALYSIS_OPTION_PUB.Bsc_Anal_Opt_Tbl_Type
454 );
455
456 PROCEDURE Remove_Unused_PMF_Dimenison
457 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
458 , p_Kpi_Id IN NUMBER
459 , p_dim_set_id IN NUMBER
460 , x_return_status OUT NOCOPY VARCHAR2
461 , x_msg_count OUT NOCOPY NUMBER
462 , x_msg_data OUT NOCOPY VARCHAR2
463 );
464
465 FUNCTION Is_More
466 ( p_dim_short_names IN OUT NOCOPY VARCHAR2
467 , p_dim_short_name OUT NOCOPY VARCHAR2
468 ) RETURN BOOLEAN;
469
470 PROCEDURE get_common_dimensions_tabs (
471 p_dim_short_name IN VARCHAR2
472 , p_objective_id IN NUMBER
473 , x_tab_ids OUT NOCOPY VARCHAR2
474 );
475
476 END BSC_BIS_KPI_MEAS_PUB;
[Home] [Help]