1 package BSC_PMF_UI_WRAPPER as
2 /* $Header: BSCPMFWS.pls 120.6 2007/06/01 06:52:59 ashankar ship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCPMFWS.pls |
10 | |
11 | Creation Date: |
12 | October 18, 2001 |
13 | |
14 | Creator: |
15 | Mario-Jair Campos |
16 | Adrao fixed bug#3118110 added function is_In_Dimension |
17 | |
18 | Description: |
19 | |
20 | 11-MAR-04 jxyu Modified for enhancement #3493589 |
21 | 18-MAY-04 adrao Modified PL/SQL records and CRUD to accept SHORT_NAME |
22 | 02-JUL-04 rpenneru Modified for enh# 3532517 |
23 | 30-SEP-04 visuri modified for bug 3852611 |
24 | 27-DEC-04 adrao added DIMOBJ_SHORT_NAME_CLASS for Bug#4089297 |
25 | 25-JUL_05 hengliu added Check_Tabview_Dependency for bug#4237294 |
26 | 16-NOV-2006 ankgoel Color By KPI enh#5244136 |
27 | 30-MAR-2007 akoduri Enh #5928640 Migration of Periodicity properties from |
28 | VB to Html |
29 +======================================================================================+
30 */
31
32 TYPE t_of_Bsc_Dim_Level_Rec IS TABLE OF BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type
33 INDEX BY BINARY_INTEGER;
34
35 TYPE t_of_varchar2 IS TABLE OF VARCHAR2(500)
36 INDEX BY BINARY_INTEGER;
37
38 -- added for Bug#4089297
39 TYPE DIMOBJ_SHORT_NAME_CLASS IS VARRAY(20) OF BIS_LEVELS.SHORT_NAME%TYPE;
40
41
42 -- added orginally for Bug#3459282 and for resolving Bug#3888428
43 TYPE Bsc_Tabs_Type is RECORD(
44 Bsc_tab_id BSC_TABS_VL.tab_id%TYPE
45 );
46
47 TYPE Bsc_Tabs_Tbl_Rec IS TABLE OF Bsc_Tabs_Type INDEX BY BINARY_INTEGER;
48
49 g_time_stamp_format VARCHAR2(200):= 'YY/MM/DD-HH:MI:SS';
50 C_SCORECARD_LOGO_TYPE NUMBER :=3;
51
52 procedure Fire_Api(
53 p_api_call varchar2
54 );
55
56 procedure Table_Generator(
57 p_commit IN VARCHAR2 := FND_API.G_TRUE
58 ,p_kpi_id number
59 ,p_meas_short_name varchar2
60 ,p_dim_level_short_name varchar2
61 );
62
63 procedure Add_Analysis_Option(
64 p_commit IN VARCHAR2 := FND_API.G_TRUE
65 ,p_option_name IN VARCHAR2
66 ,p_option_description IN VARCHAR2
67 ,p_meas_short_name IN VARCHAR2
68 ,p_dim_level_short_names IN VARCHAR2
69 ,p_kpi_id IN NUMBER
70 ,x_bad_level OUT NOCOPY varchar2
71 ,x_return_status OUT NOCOPY varchar2
72 ,x_msg_count OUT NOCOPY number
73 ,x_msg_data OUT NOCOPY varchar2
74 );
75
76 procedure Create_Kpi_Group(
77 p_commit IN VARCHAR2 := FND_API.G_TRUE
78 ,p_tab_id IN number
79 ,p_kpi_group_id IN number
80 ,x_return_status OUT NOCOPY varchar2
81 ,x_msg_count OUT NOCOPY number
82 ,x_msg_data OUT NOCOPY varchar2
83 ,p_kpi_group_name IN varchar2 DEFAULT null
84 ,p_kpi_group_help IN varchar2 DEFAULT null
85 );
86
87 procedure Create_Kpi_Group(
88 p_commit IN VARCHAR2 := FND_API.G_FALSE
89 ,p_tab_id IN NUMBER -- It needs to pass NULL or -1
90 ,p_kpi_group_id IN NUMBER
91 ,p_kpi_group_name IN VARCHAR2
92 ,p_kpi_group_help IN VARCHAR2
93 ,p_Kpi_Group_short_Name IN VARCHAR2 := NULL
94 ,x_kpi_group_id OUT NOCOPY NUMBER -- OUT parameter for kpi group id
95 ,x_return_status OUT NOCOPY VARCHAR2
96 ,x_msg_count OUT NOCOPY NUMBER
97 ,x_msg_data OUT NOCOPY VARCHAR2
98 );
99
100 procedure Update_Kpi_Group(
101 p_commit IN VARCHAR2 := FND_API.G_TRUE
102 ,p_kpi_group_id IN number
103 ,p_kpi_group_name IN varchar2
104 ,x_return_status OUT NOCOPY varchar2
105 ,x_msg_count OUT NOCOPY number
106 ,x_msg_data OUT NOCOPY varchar2
107 ,p_kpi_group_help IN varchar2 DEFAULT null
108 );
109
110 procedure Create_Kpi(
111 p_commit IN VARCHAR2 := FND_API.G_TRUE
112 ,p_group_id IN number
113 ,p_responsibility_id IN number
114 ,x_return_status OUT NOCOPY varchar2
115 ,x_msg_count OUT NOCOPY number
116 ,x_msg_data OUT NOCOPY varchar2
117 ,p_kpi_name IN varchar2 DEFAULT null
118 ,p_kpi_help IN varchar2 DEFAULT null
119 );
120
121 procedure Create_Kpi(
122 p_commit IN VARCHAR2 := FND_API.G_FALSE
123 ,p_group_id IN NUMBER
124 ,p_responsibility_id IN NUMBER
125 ,p_kpi_name IN VARCHAR2
126 ,p_kpi_help IN VARCHAR2
127 ,p_Kpi_Short_Name IN VARCHAR2 := NULL
128 ,p_Kpi_Indicator_Type IN NUMBER := NULL
129 ,x_kpi_id OUT NOCOPY NUMBER
130 ,x_return_status OUT NOCOPY VARCHAR2
131 ,x_msg_count OUT NOCOPY NUMBER
132 ,x_msg_data OUT NOCOPY VARCHAR2
133 );
134
135 procedure Update_Kpi(
136 p_commit IN VARCHAR2 := FND_API.G_TRUE
137 ,p_kpi_id IN number
138 ,x_return_status OUT NOCOPY varchar2
139 ,x_msg_count OUT NOCOPY number
140 ,x_msg_data OUT NOCOPY varchar2
141 ,p_kpi_name IN varchar2 DEFAULT null
142 ,p_kpi_help IN varchar2 DEFAULT null
143 );
144
145 procedure Create_Tab(
146 p_commit IN VARCHAR2 := FND_API.G_TRUE
147 ,p_responsibility_id IN number
148 ,x_return_status OUT NOCOPY varchar2
149 ,x_msg_count OUT NOCOPY number
150 ,x_msg_data OUT NOCOPY varchar2
151 ,p_tab_name IN varchar2 := NULL
152 ,p_tab_help IN varchar2 := NULL
153 );
154
155 PROCEDURE Create_Tab
156 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
157 , p_responsibility_id IN NUMBER
158 , p_parent_tab_id IN NUMBER
159 , p_owner_id IN NUMBER
160 , p_Short_Name IN VARCHAR2 := NULL
161 , x_tab_id OUT NOCOPY NUMBER
162 , x_return_status OUT NOCOPY VARCHAR2
163 , x_msg_count OUT NOCOPY NUMBER
164 , x_msg_data OUT NOCOPY VARCHAR2
165 , p_tab_name IN VARCHAR2 := NULL
166 , p_tab_help IN VARCHAR2 := NULL
167 , p_tab_info IN VARCHAR2 := NULL
168 );
169
170 procedure Update_Tab(
171 p_commit IN VARCHAR2 := FND_API.G_TRUE
172 ,p_tab_id IN number
173 ,p_tab_name IN varchar2
174 ,x_return_status OUT NOCOPY varchar2
175 ,x_msg_count OUT NOCOPY number
176 ,x_msg_data OUT NOCOPY varchar2
177 ,p_tab_help IN varchar2 DEFAULT null
178 );
179
180 procedure Update_Tab(
181 p_commit IN VARCHAR2 := FND_API.G_TRUE
182 ,p_tab_id IN number
183 ,p_owner_id IN number
184 ,p_tab_name IN varchar2
185 ,x_return_status OUT NOCOPY varchar2
186 ,x_msg_count OUT NOCOPY number
187 ,x_msg_data OUT NOCOPY varchar2
188 ,p_tab_help IN varchar2 DEFAULT null
189 ,p_tab_info IN varchar2 DEFAULT null
190 ,p_time_stamp IN varchar2 := NULL
191
192 );
193
194 procedure Update_Analysis_Option(
195 p_commit IN VARCHAR2 := FND_API.G_TRUE
196 ,p_kpi_id IN number
197 ,p_option_group_id IN number
198 ,p_option_id IN number
199 ,p_option_name IN varchar2
200 ,x_return_status OUT NOCOPY varchar2
201 ,x_msg_count OUT NOCOPY number
202 ,x_msg_data OUT NOCOPY varchar2
203 ,p_option_help IN varchar2 DEFAULT null
204 );
205
206 procedure Delete_Analysis_Option(
207 p_commit IN VARCHAR2 := FND_API.G_TRUE
208 ,p_kpi_id IN number
209 ,p_option_group_id IN number
210 ,p_option_id IN number
211 ,x_return_status OUT NOCOPY varchar2
212 ,x_msg_count OUT NOCOPY number
213 ,x_msg_data OUT NOCOPY varchar2
214 );
215
216 procedure Delete_Kpi(
217 p_commit IN VARCHAR2 := FND_API.G_TRUE
218 ,p_kpi_id IN number
219 ,x_return_status OUT NOCOPY varchar2
220 ,x_msg_count OUT NOCOPY number
221 ,x_msg_data OUT NOCOPY varchar2
222 );
223
224 procedure Delete_Kpi_Group(
225 p_commit IN VARCHAR2 := FND_API.G_TRUE
226 ,p_kpi_group_id IN number
227 ,p_tab_id IN number
228 ,x_return_status OUT NOCOPY varchar2
229 ,x_msg_count OUT NOCOPY number
230 ,x_msg_data OUT NOCOPY varchar2
231 );
232
233 procedure Delete_Tab(
234 p_commit IN VARCHAR2 := FND_API.G_TRUE
235 ,p_tab_id IN number
236 ,x_return_status OUT NOCOPY varchar2
237 ,x_msg_count OUT NOCOPY number
238 ,x_msg_data OUT NOCOPY varchar2
239 );
240
241 procedure Assign_KPI(
242 p_commit IN VARCHAR2 := FND_API.G_FALSE
243 ,p_kpi_id IN number
244 ,p_tab_id IN number
245 ,x_return_status IN OUT NOCOPY varchar2
246 ,x_msg_count IN OUT NOCOPY number
247 ,x_msg_data IN OUT NOCOPY varchar2
248 ,p_time_stamp IN varchar2 := NULL
249 );
250
251 procedure Unassign_KPI(
252 p_commit IN VARCHAR2 := FND_API.G_TRUE
253 ,p_kpi_id IN number
254 ,p_tab_id IN number
255 ,x_return_status OUT NOCOPY varchar2
256 ,x_msg_count OUT NOCOPY number
257 ,x_msg_data OUT NOCOPY varchar2
258 ,p_time_stamp IN varchar2 := NULL
259 );
260
261 function Is_KPI_Assigned(
262 p_kpi_id IN number
263 ,p_tab_id IN number
264 ) return varchar2;
265
266 procedure Assign_KPI_Group(
267 p_commit IN VARCHAR2 := FND_API.G_TRUE
268 ,p_kpi_Group_id IN number
269 ,p_tab_id IN number
270 ,x_return_status OUT NOCOPY varchar2
271 ,x_msg_count OUT NOCOPY number
272 ,x_msg_data OUT NOCOPY varchar2
273 );
274
275 procedure Assign_Analysis_Option(
276 p_kpi_id IN number
277 ,p_analysis_group_id IN number
278 ,p_option_id IN number
279 ,p_parent_option_id IN number
280 ,p_grandparent_option_id IN number
281 ,x_return_status OUT NOCOPY varchar2
282 ,x_msg_count OUT NOCOPY number
283 ,x_msg_data OUT NOCOPY varchar2
284 ,p_commit IN varchar2 := FND_API.G_TRUE
285 ,p_time_stamp_to_check IN varchar2 := null
286 );
287
288 procedure Unassign_Analysis_Option(
289 p_kpi_id IN number
290 ,p_analysis_group_id IN number
291 ,p_option_id IN number
292 ,p_parent_option_id IN number
293 ,p_grandparent_Option_id IN number
294 ,x_return_status OUT NOCOPY varchar2
295 ,x_msg_count OUT NOCOPY number
296 ,x_msg_data OUT NOCOPY varchar2
297 ,p_commit IN varchar2 := FND_API.G_TRUE
298 ,p_time_stamp_to_check IN varchar2 := null
299 );
300
301 function Is_Analysis_Option_Selected(
302 p_kpi_id IN number
303 ,p_analysis_group_id IN number
304 ,p_option_id IN number
305 ,p_parent_option_id IN number
306 ,p_grandparent_Option_id IN number
307 ) return varchar2;
308
309 function Is_Leaf_Analysis_Option(
310 p_kpi_id IN number
311 ,p_analysis_group_id IN number
312 ,p_option_id IN number
313 ,p_parent_option_id IN number
314 ,p_grandparent_Option_id IN number
315 ) return varchar2;
316
317 procedure Populate_Option_Dependency_Rec(
318 p_commit IN VARCHAR2 := FND_API.G_TRUE
319 ,p_kpi_id IN number
320 ,p_analysis_group_id IN number
321 ,p_option_id IN number
322 ,p_parent_option_id IN number
323 ,p_grandparent_option_id IN number
324 ,p_Bsc_kpi_Entity_Rec OUT NOCOPY BSC_KPI_PUB.Bsc_kpi_Entity_Rec
325 ,x_return_status OUT NOCOPY varchar2
326 ,x_msg_count OUT NOCOPY number
327 ,x_msg_data OUT NOCOPY varchar2
328 );
329
330 /*********************************************************************************
331 -- Procedures to Handle Relationships between Dimension Levels
332 **********************************************************************************/
333 procedure Change_Error_Msg(
334 p_commit IN VARCHAR2 := FND_API.G_TRUE
335 ,p_msg_name IN varchar2 DEFAULT null
336 ,p_new_msg_name IN varchar2 DEFAULT null
337 ,p_token1 IN varchar2 DEFAULT null
338 ,p_token1_value IN varchar2 DEFAULT null
339 ,p_token2 IN varchar2 DEFAULT null
340 ,p_token2_value IN varchar2 DEFAULT null
341 ,p_initialize_flag IN varchar2 DEFAULT 'Y'
342 ,p_sys_admin_flag IN varchar2 DEFAULT 'F'
343 ,x_return_status IN OUT NOCOPY varchar2
344 ,x_msg_count OUT NOCOPY number
345 ,x_msg_data OUT NOCOPY varchar2
346
347 );
348
349 PROCEDURE Import_Dim_Level(
350 p_commit IN VARCHAR2 := FND_API.G_TRUE
351 ,p_Short_Name IN varchar2
352 ,x_return_status OUT NOCOPY varchar2
353 ,x_msg_count OUT NOCOPY number
354 ,x_msg_data OUT NOCOPY varchar2
355 );
356
357 PROCEDURE Update_RelationShips(
358 p_commit IN VARCHAR2 := FND_API.G_TRUE
359 ,p_Dim_Level_Id IN number
360 ,p_Short_Name IN varchar2
361 ,p_Parents IN varchar2
362 ,x_return_status OUT NOCOPY varchar2
363 ,x_msg_count OUT NOCOPY number
364 ,x_msg_data OUT NOCOPY varchar2
365 );
366
367 FUNCTION Decompose_String_List(
368 x_string IN VARCHAR2,
369 x_varchar2_array IN OUT NOCOPY t_of_varchar2,
370 x_separator IN VARCHAR2
371 ) RETURN VARCHAR2;
372
373 procedure Create_PMF_Relationship (
374 p_commit IN varchar := FND_API.G_FALSE
375 ,p_SHORT_NAME IN VARCHAR2
376 ,p_PARENT_SHORT_NAME IN VARCHAR2
377 ,p_RELATION_COL IN VARCHAR2
378 );
379
380 procedure Order_Tab_Index(
381 p_commit IN VARCHAR2 := FND_API.G_TRUE
382 ,p_tab_ids IN varchar2
383 ,x_return_status OUT NOCOPY varchar2
384 ,x_msg_count OUT NOCOPY number
385 ,x_msg_data OUT NOCOPY varchar2
386 );
387
388 procedure Update_Tab_Parent(
389 p_commit IN VARCHAR2 := FND_API.G_TRUE
390 ,p_tab_id IN number
391 ,p_parent_tab_id IN number
392 ,x_return_status OUT NOCOPY varchar2
393 ,x_msg_count OUT NOCOPY number
394 ,x_msg_data OUT NOCOPY varchar2
395 );
396
397 procedure Create_Measure(
398 p_commit IN VARCHAR2 := FND_API.G_TRUE
399 ,p_short_name IN varchar2
400 ,x_return_status OUT NOCOPY varchar2
401 ,x_msg_count OUT NOCOPY number
402 ,x_msg_data OUT NOCOPY varchar2
403 );
404
405 procedure Create_Measure_VB(
406 p_short_name IN varchar2
407 );
408
409 procedure Get_PMV_Report_Levels(
410 p_region_code IN varchar2
411 ,p_measure_short_name IN varchar2
412 ,x_dim1_name OUT NOCOPY varchar2
413 ,x_dim1_levels OUT NOCOPY varchar2
414 ,x_dim2_name OUT NOCOPY varchar2
415 ,x_dim2_levels OUT NOCOPY varchar2
416 ,x_dim3_name OUT NOCOPY varchar2
417 ,x_dim3_levels OUT NOCOPY varchar2
418 ,x_dim4_name OUT NOCOPY varchar2
419 ,x_dim4_levels OUT NOCOPY varchar2
420 ,x_dim5_name OUT NOCOPY varchar2
421 ,x_dim5_levels OUT NOCOPY varchar2
422 ,x_dim6_name OUT NOCOPY varchar2
423 ,x_dim6_levels OUT NOCOPY varchar2
424 ,x_dim7_name OUT NOCOPY varchar2
425 ,x_dim7_levels OUT NOCOPY varchar2
426 ,x_is_there_time OUT NOCOPY varchar2
427 ,x_return_status OUT NOCOPY varchar2
428 ,x_msg_count OUT NOCOPY number
429 ,x_msg_data OUT NOCOPY varchar2
430 );
431
432 PROCEDURE Assign_Kpi_Tab
433 (
434 p_commit IN VARCHAR2 := FND_API.G_FALSE
435 , p_tab_id IN NUMBER
436 , p_kpi_ids IN VARCHAR2
437 , x_return_status IN OUT NOCOPY VARCHAR2
438 , x_msg_count IN OUT NOCOPY NUMBER
439 , x_msg_data IN OUT NOCOPY VARCHAR2
440 , p_time_stamp IN VARCHAR2 := NULL
441 );
442
443 function get_KPI_Time_Stamp(
444 p_kpi_id IN number
445 ) return varchar2;
446
447 FUNCTION get_Tab_Id
448 (
449 p_Tab_Name IN VARCHAR2
450 ) RETURN NUMBER;
451
452
453 FUNCTION is_In_Dimension
454 ( p_measure_short_name IN VARCHAR2
455 , p_dims_short_name IN VARCHAR2
456 , p_dim_obj IN VARCHAR2
457 ) RETURN VARCHAR2;
458
459 FUNCTION is_group_selected
460 (
461 p_tab_id IN NUMBER
462 , p_group_id IN NUMBER
463
464 ) RETURN VARCHAR2;
465
466 procedure Update_Kpi_Periodicities(
467 p_commit IN VARCHAR2 := FND_API.G_FALSE
468 ,p_kpi_id IN NUMBER
469 ,p_calendar_id IN NUMBER
470 ,p_periodicity_ids IN VARCHAR2
471 ,p_Dft_periodicity_id IN NUMBER
472 ,p_Periods_In_Graph IN FND_TABLE_OF_NUMBER := NULL
473 ,p_Periodicity_Id_Tbl IN FND_TABLE_OF_NUMBER := NULL
474 ,p_Number_Of_Years IN NUMBER := 10
475 ,p_Previous_Years IN NUMBER := 5
476 ,x_return_status OUT NOCOPY VARCHAR2
477 ,x_msg_count OUT NOCOPY NUMBER
478 ,x_msg_data OUT NOCOPY VARCHAR2
479 );
480
481 procedure Check_Tab(
482 p_tab_id IN number
483 ,x_return_status OUT NOCOPY varchar2
484 ,x_msg_count OUT NOCOPY number
485 ,x_msg_data OUT NOCOPY varchar2
486 );
487
488 PROCEDURE Check_Tabviews (
489 p_tab_id IN NUMBER
490 ,p_list_dependency IN VARCHAR2
491 ,x_exist_dependency OUT NOCOPY VARCHAR2
492 ,x_dep_obj_list OUT NOCOPY VARCHAR2
493 ,x_return_status OUT NOCOPY VARCHAR2
494 ,x_msg_count OUT NOCOPY NUMBER
495 ,x_msg_data OUT NOCOPY VARCHAR2
496 );
497
498 PROCEDURE Check_Tabview_Dependency (
499 p_tab_id IN NUMBER
500 ,p_tab_view_id IN NUMBER
501 ,p_list_dependency IN VARCHAR2
502 ,x_exist_dependency OUT NOCOPY VARCHAR2
503 ,x_dep_obj_list OUT NOCOPY VARCHAR2
504 ,x_return_status OUT NOCOPY VARCHAR2
505 ,x_msg_count OUT NOCOPY NUMBER
506 ,x_msg_data OUT NOCOPY VARCHAR2
507 );
508
509
510 PROCEDURE Create_Scorecard_logo (
511 p_obj_id IN NUMBER
512 ,p_file_name IN VARCHAR2
513 ,p_description IN VARCHAR2
514 ,p_width IN NUMBER
515 ,p_height IN NUMBER
516 ,p_mime_type IN VARCHAR2
517 ,x_image_id OUT NOCOPY NUMBER
518 ,x_return_status OUT NOCOPY VARCHAR2
519 ,x_msg_count OUT NOCOPY NUMBER
520 ,x_msg_data OUT NOCOPY VARCHAR2
521 );
522
523
524 PROCEDURE Add_Or_Update_Tab_Logo (
525 p_tab_id IN NUMBER
526 ,p_image_id IN NUMBER
527 ,p_file_name IN VARCHAR2
528 ,p_description IN VARCHAR2
529 ,p_width IN NUMBER
530 ,p_height IN NUMBER
531 ,p_mime_type IN VARCHAR2
532 ,x_image_id OUT NOCOPY NUMBER
533 ,x_return_status OUT NOCOPY VARCHAR2
534 ,x_msg_count OUT NOCOPY NUMBER
535 ,x_msg_data OUT NOCOPY VARCHAR2
536 );
537
538 PROCEDURE Delete_Tab_Logo
539 (
540 p_tab_id IN BSC_TABS_B.tab_id%TYPE
541 ,x_return_status OUT NOCOPY VARCHAR2
542 ,x_msg_count OUT NOCOPY NUMBER
543 ,x_msg_data OUT NOCOPY VARCHAR2
544 );
545
546
547 END BSC_PMF_UI_WRAPPER;
[Home] [Help]