1 PACKAGE BODY BSC_BIS_CUSTOM_KPI_UTIL_PUB AS
2 /* $Header: BSCCSUBB.pls 120.9 2007/10/10 06:42:39 bijain ship $ */
3
4 /*
5 REM +=======================================================================+
6 REM | Copyright (c) 2004 Oracle Corporation, Redwood Shores, CA, USA |
7 REM | All rights reserved. |
8 REM +=======================================================================+
9 REM | FILENAME |
10 REM | BSCCSUBB.pls |
11 REM | |
12 REM | DESCRIPTION |
13 REM | Module: Wrapper API for Configure KPI List Page |
14 REM | |
15 REM | NOTES |
16 REM | |
17 REM | HISTORY |
18 REM | 13-Aug-04 rpenneru Created. |
19 REM | 17-AUG-04 adrao Modified API SetGlobalFlag and added API |
20 REM | Is_BSC52_Applied for Bug@3836170 |
21 REM | 05-OCT-04 ankgoel Bug#3933075 Moved Get_Pmf_Metadata_By_Objective
22 REM | and get_Region_Code here from BSCCRUDB.pls|
23 REM | 11-Feb-05 sawu Bug#4057761: added get_Next_Alias and |
24 REM | Get_Unqiue_Tab_Name, Get_Unqiue_Tab_Group_Name|
25 REM | 30-Mar-05 wleung hardcode Get_Max_Tab_Name_Length() and |
26 REM | Get_Max_Tab_Grp_Name_Length |
27 REM | 27-MAR-05 adrao Modified API is_Report_S2E for Bug#4331964|
28 REM | 29-APR-05 ankagarw Modified API Is_Short_Name_Available for Bug#4336571|
29 REM | Made it public. |
30 REM | 09-MAY-05 adrao Added API is_Objective_Report_Type |
31 REM | and is_Objective_Page_Type |
32 REM | 26-SEP-2005 arhegde bug# 4624100 Moved get_format_mask code to |
33 REM | BSC_BIS_CUSTOM_KPI_UTIL_PUB from BSC_BIS_KPI_CRUD_PUB since |
34 REM | pure BIS can use it too. |
35 REM | 10-MAY-2006 visuri bug#5130750 Data Corruption issue |
36 REM | 09-FEB-1007 ashankar Simulation ER 5386112 |
37 REM | 03-APR-2007 amitgupt modified for bug 5959433 |
38 REM | 02-Oct-2007 bijain Bug#6327035 Changing the display name of a |
39 REM | DashBord for End to End Kpi should update BSC MetaData|
40 REM +=======================================================================+
41 */
42
43 -- changed to use BSC_KPI_ANALYSIS_OPTIONS_B
44 -- BSC_KPIS_B.SHORT_NAME and BSC_TABS_B.SHORT_NAME have existed before Start-to-End KPI.
45
46
47 PROCEDURE SetGlobalFlag
48 IS
49
50 TYPE Ref_Cur IS REF CURSOR;
51 Flag_Cur Ref_Cur;
52 sql_query VARCHAR2(2000);
53 rec_count NUMBER;
54 BEGIN
55
56 IF(Flag_Cur%ISOPEN) THEN
57 CLOSE Flag_Cur;
58 END IF;
59
60 sql_query := 'select count(1) from sys.all_tab_columns where table_name = ''BSC_DB_MEASURE_GROUPS_TL'' and COLUMN_NAME = ''SHORT_NAME'' ';
61 OPEN Flag_Cur FOR sql_query;
62 FETCH Flag_Cur INTO rec_count;
63 CLOSE Flag_Cur;
64
65 IF (rec_count > 0) THEN
66 BSC_BIS_CUSTOM_KPI_UTIL_PUB.G_MDDD_52 := BSC_BIS_CUSTOM_KPI_UTIL_PUB.C_BSC_PATCH_LEVEL_52;
67 ELSE
68 BSC_BIS_CUSTOM_KPI_UTIL_PUB.G_MDDD_52 := BSC_BIS_CUSTOM_KPI_UTIL_PUB.C_BSC_PATCH_LEVEL_511;
69 END IF;
70 EXCEPTION
71 WHEN OTHERS THEN
72 BSC_BIS_CUSTOM_KPI_UTIL_PUB.G_MDDD_52 := BSC_BIS_CUSTOM_KPI_UTIL_PUB.C_BSC_PATCH_LEVEL_511;
73 END SetGlobalFlag;
74
75
76 FUNCTION IS_NOT_NULL(p_name VARCHAR2)
77 RETURN VARCHAR2 IS
78 BEGIN
79 IF (p_name IS NULL) THEN
80 RETURN FND_API.G_FALSE;
81 END IF;
82 RETURN FND_API.G_TRUE;
83 END IS_NOT_NULL;
84
85
86 /*this function will return 'T' (FND_API.G_TRUE) if the passed short_name of Measure
87 is created through KPI End to End module otherwise 'F' (FND_API.G_FALSE)
88
89 This function assumes the Short_Name passed is valid and exists in
90 BSC Metadata. It will not be checked from BSC tables due to performance reasons.
91
92 p_Short_Name Corresponds to BSC_SYS_MEASURES.Short_Name
93 */
94 FUNCTION is_KPI_EndToEnd_Measure(p_Short_Name VARCHAR2)
95 RETURN VARCHAR2 IS
96 l_Count NUMBER;
97 BEGIN
98 IF (p_Short_Name IS NULL) THEN
99 RETURN FND_API.G_FALSE;
100 END IF;
101
102 l_Count := 0;
103
104 SELECT COUNT(1) INTO l_Count
105 FROM bis_indicators
106 WHERE short_name = p_Short_Name
107 AND actual_data_source IS NOT NULL
108 AND actual_data_source_type = 'AK';
109
110 IF (l_Count = 0) THEN
111 RETURN FND_API.G_FALSE;
112 ELSE
113 RETURN FND_API.G_TRUE;
114 END IF;
115 EXCEPTION
116 WHEN OTHERS THEN
117 RETURN FND_API.G_FALSE;
118 END is_KPI_EndToEnd_Measure;
119
120 procedure update_kpi_End_To_End_Name(
121 p_Commit IN VARCHAR2 := FND_API.G_FALSE
122 ,p_Name IN VARCHAR2
123 ,p_Short_Name IN VARCHAR2
124 ,x_Return_Status OUT NOCOPY VARCHAR2
125 ,x_Msg_Count OUT NOCOPY NUMBER
126 ,x_Msg_Data OUT NOCOPY VARCHAR2
127 ) IS
128
129 l_tab_id BSC_TABS_B.TAB_ID%TYPE;
130 l_kpi_group_id BSC_TAB_IND_GROUPS_B.IND_GROUP_ID%TYPE;
131 l_kpi_id BSC_KPIS_B.INDICATOR%TYPE;
132 l_tab_name BSC_TABS_VL.NAME%TYPE;
133 l_tab_ind_group_name BSC_TAB_IND_GROUPS_VL.NAME%TYPE;
134 not_S_To_E_Kpi EXCEPTION;
135
136 BEGIN
137 SAVEPOINT UpdateEndToEndKPIName;
138 FND_MSG_PUB.Initialize;
139 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
140
141 l_tab_id := BSC_BIS_KPI_CRUD_PUB.Get_Tab_Id(p_Short_Name);
142 l_kpi_group_id := BSC_BIS_KPI_CRUD_PUB.Get_Group_Id(p_Short_Name);
143 l_kpi_id := BSC_BIS_KPI_CRUD_PUB.Get_Kpi_Id(p_Short_Name);
144
145 IF(BSC_BIS_KPI_CRUD_PUB.C_INVALID_ENTITY <> l_tab_id) THEN
146 l_tab_name := BSC_BIS_CUSTOM_KPI_UTIL_PUB.Get_Unqiue_Tab_Name(p_Name, l_tab_id);
147 BSC_PMF_UI_WRAPPER.Update_Tab(
148 p_commit => p_Commit
149 ,p_tab_id => l_tab_id
150 ,p_tab_name => l_tab_name
151 ,p_tab_help => l_tab_name
152 ,x_return_status => x_Return_Status
153 ,x_msg_count => x_Msg_Count
154 ,x_msg_data => x_Msg_Data
155 );
156 ELSE
157 RAISE not_S_To_E_Kpi;
158 END IF;
159
160 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
162 END IF;
163
164 IF(BSC_BIS_KPI_CRUD_PUB.C_INVALID_ENTITY <> l_kpi_group_id) THEN
165 l_tab_ind_group_name := BSC_BIS_CUSTOM_KPI_UTIL_PUB.Get_Unqiue_Tab_Group_Name(p_Name,l_kpi_group_id);
166 BSC_PMF_UI_WRAPPER.Update_Kpi_Group(
167 p_commit => p_Commit
168 ,p_kpi_group_id => l_kpi_group_id
169 ,p_kpi_group_name => l_tab_ind_group_name
170 ,p_kpi_group_help => l_tab_ind_group_name
171 ,x_return_status => x_Return_Status
172 ,x_msg_count => x_Msg_Count
173 ,x_msg_data => x_Msg_Data
174 );
175 ELSE
176 RAISE not_S_To_E_Kpi;
177 END IF;
178
179 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181 END IF;
182 IF(-1000 <> l_kpi_id) THEN
183 BSC_PMF_UI_WRAPPER.Update_Kpi(
184 p_commit => p_Commit
185 ,p_kpi_id => l_kpi_id
186 ,p_kpi_name => p_Name
187 ,p_kpi_help => p_Name
188 ,x_return_status => x_Return_Status
189 ,x_msg_count => x_Msg_Count
190 ,x_msg_data => x_Msg_Data
191 );
192 ELSE
193 RAISE not_S_To_E_Kpi;
194 END IF;
195
196 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
198 END IF;
199
200 IF (p_Commit = FND_API.G_TRUE) THEN
201 COMMIT;
202 END IF;
203
204 EXCEPTION
205 WHEN not_S_To_E_Kpi THEN
206 ROLLBACK TO UpdateEndToEndKPIName;
207 WHEN OTHERS THEN
208 x_Return_Status := FND_API.G_RET_STS_ERROR;
209 ROLLBACK TO UpdateEndToEndKPIName;
210 IF (x_msg_data IS NOT NULL) THEN
211 x_msg_data := x_msg_data||' -> BSC_BIS_CUSTOM_KPI_UTIL_PUB.update_kpi_End_To_End_Name ';
212 ELSE
213 x_msg_data := SQLERRM||' at BSC_BIS_CUSTOM_KPI_UTIL_PUB.update_kpi_End_To_End_Name ';
214 END IF;
215 END update_kpi_End_To_End_Name;
216 FUNCTION is_KPI_EndToEnd_Measure(p_Measure_Id NUMBER)
217 RETURN VARCHAR2 IS
218 TYPE Ref_Cur IS REF CURSOR;
219 Flag_Cur Ref_Cur;
220 sql_query VARCHAR2(2000);
221 l_count NUMBER :=0;
222
223 BEGIN
224
225 IF (BSC_BIS_CUSTOM_KPI_UTIL_PUB.Is_Short_Name_Available = FND_API.G_FALSE) THEN
226 RETURN FND_API.G_FALSE;
227 END IF;
228
229 IF(Flag_Cur%ISOPEN) THEN
230 CLOSE Flag_Cur;
231 END IF;
232
233 sql_query := 'SELECT count(1) FROM BSC_SYS_MEASURES a,BSC_DB_MEASURE_COLS_VL b '
234 ||' , BSC_DB_MEASURE_GROUPS_VL c '
235 ||' WHERE a.measure_col = b.measure_col '
236 ||' AND a.measure_id = :p_measure_id'
237 ||' AND b.measure_group_id = c.measure_group_id '
238 ||' AND c.short_name IS NOT NULL ';
239 OPEN Flag_Cur FOR sql_query USING p_Measure_Id;
240 FETCH Flag_Cur INTO l_count;
241 CLOSE Flag_Cur;
242 IF (l_count > 0) THEN
243 RETURN FND_API.G_TRUE;
244 END IF;
245 RETURN FND_API.G_FALSE;
246 EXCEPTION
247 WHEN OTHERS THEN
248 RETURN FND_API.G_FALSE;
249 END is_KPI_EndToEnd_Measure;
250
251
252 FUNCTION is_KPI_EndToEnd_MeasureGroup(p_Short_Name VARCHAR2)
253 RETURN VARCHAR2 IS
254 BEGIN
255 RETURN IS_NOT_NULL(p_Short_Name);
256 EXCEPTION
257 WHEN OTHERS THEN
258 RETURN FND_API.G_FALSE;
259 END is_KPI_EndToEnd_MeasureGroup;
260
261
262 FUNCTION is_KPI_EndToEnd_MeasureGroup(p_Measure_Group_Id NUMBER)
263 RETURN VARCHAR2 IS
264 TYPE Ref_Cur IS REF CURSOR;
265 Flag_Cur Ref_Cur;
266 sql_query VARCHAR2(2000);
267 rec_count NUMBER;
268
269 BEGIN
270
271 IF (BSC_BIS_CUSTOM_KPI_UTIL_PUB.Is_Short_Name_Available = FND_API.G_FALSE) THEN
272 RETURN FND_API.G_FALSE;
273 END IF;
274
275 IF(Flag_Cur%ISOPEN) THEN
276 CLOSE Flag_Cur;
277 END IF;
278
279 sql_query := 'select count(1) from bsc_db_measure_groups_vl where short_name is not null and measure_group_id = :p_measure_group_id';
280 OPEN Flag_Cur FOR sql_query USING p_Measure_Group_Id;
281 FETCH Flag_Cur INTO rec_count;
282 CLOSE Flag_Cur;
283
284 IF (rec_count > 0) THEN
285 RETURN FND_API.G_TRUE;
286 END IF;
287 RETURN FND_API.G_FALSE;
288
289 EXCEPTION
290 WHEN OTHERS THEN
291 RETURN FND_API.G_FALSE;
292 END is_KPI_EndToEnd_MeasureGroup;
293
294
295 FUNCTION is_KPI_EndToEnd_MeasureCol(p_Measure_Col VARCHAR2,p_MesGrp_Short_Name VARCHAR2)
296 RETURN VARCHAR2 IS
297 BEGIN
298 RETURN BSC_BIS_CUSTOM_KPI_UTIL_PUB.is_KPI_EndToEnd_MeasureGroup(p_MesGrp_Short_Name);
299 EXCEPTION
300 WHEN OTHERS THEN
301 RETURN FND_API.G_FALSE;
302 END is_KPI_EndToEnd_MeasureCol;
303
304
305 FUNCTION is_KPI_EndToEnd_MeasureCol(p_Measure_Col VARCHAR2)
306 RETURN VARCHAR2
307 IS
308 CURSOR c_MeasureGrp IS
309 SELECT measure_group_id FROM BSC_DB_MEASURE_COLS_VL
310 WHERE measure_col = p_Measure_Col;
311
312 l_Measure_Group_Id BSC_DB_MEASURE_COLS_VL.MEASURE_GROUP_ID%TYPE;
313
314 BEGIN
315 IF(c_MeasureGrp%ISOPEN) THEN
316 CLOSE c_MeasureGrp;
317 END IF;
318
319 OPEN c_MeasureGrp;
320 FETCH c_MeasureGrp INTO l_Measure_Group_Id;
321 CLOSE c_MeasureGrp;
322 RETURN BSC_BIS_CUSTOM_KPI_UTIL_PUB.is_KPI_EndToEnd_MeasureGroup(l_Measure_Group_Id);
323
324 EXCEPTION
325 WHEN OTHERS THEN
326 RETURN FND_API.G_FALSE;
327 END is_KPI_EndToEnd_MeasureCol;
328
329
330 /*this function will return 'T' (FND_API.G_TRUE) if the passed short_name of scorecard
331 is created through KPI End to End module otherwise 'F' (FND_API.G_FALSE).
332
333 This function assumes the Short_Name passed is valid and exists in
334 BSC Metadata. It will not be checked from BSC tables due to performance reasons.
335
336 p_Short_Name Corresponds to BSC_TABS_B.Short_Name
337 */
338 FUNCTION is_KPI_EndToEnd_Scorecard(p_Short_Name VARCHAR2)
339 RETURN VARCHAR2 IS
340 BEGIN
341 RETURN IS_NOT_NULL(p_Short_Name);
342 EXCEPTION
343 WHEN OTHERS THEN
344 RETURN FND_API.G_FALSE;
345 END is_KPI_EndToEnd_Scorecard;
346
347
348 FUNCTION Get_User_Function_Name(p_function_name VARCHAR2) RETURN VARCHAR2
349 IS
350 l_user_function_name VARCHAR2(100);
351 BEGIN
352 l_user_function_name := p_function_name;
353 SELECT a.user_function_name
354 INTO l_user_function_name
355 FROM fnd_form_functions_vl a
356 WHERE a.function_name = p_function_name;
357
358 IF (l_user_function_name IS NULL) THEN
359 l_user_function_name := p_function_name;
360 END IF;
361
362 RETURN l_user_function_name;
363 EXCEPTION
364 WHEN NO_DATA_FOUND THEN RETURN p_function_name;
365 WHEN OTHERS THEN RETURN p_function_name;
366 END Get_User_Function_Name;
367
368
369 -- Check if ODF with short_names has been applied or not
370 FUNCTION enableVarchar2Implementation
371 RETURN VARCHAR2 IS
372 BEGIN
373 IF (BSC_BIS_CUSTOM_KPI_UTIL_PUB.Is_Short_Name_Available = FND_API.G_FALSE) THEN
374 RETURN FND_API.G_FALSE;
375 END IF;
376
377 RETURN FND_API.G_TRUE;
378 EXCEPTION
379 WHEN OTHERS THEN
380 RETURN FND_API.G_FALSE;
381 END enableVarchar2Implementation;
382
383
384 -- Returns/Inits if SHORT_NAME is available via ODF or not.
385 FUNCTION Is_Short_Name_Available
386 RETURN VARCHAR2 IS
387 BEGIN
388
389 IF (BSC_BIS_CUSTOM_KPI_UTIL_PUB.G_MDDD_52 = BSC_BIS_CUSTOM_KPI_UTIL_PUB.C_BSC_PATCH_LEVEL_UNINIT) THEN
390 BSC_BIS_CUSTOM_KPI_UTIL_PUB.setGlobalFlag;
391 END IF;
392
393 IF (BSC_BIS_CUSTOM_KPI_UTIL_PUB.G_MDDD_52 = BSC_BIS_CUSTOM_KPI_UTIL_PUB.C_BSC_PATCH_LEVEL_511) THEN
394 RETURN FND_API.G_FALSE;
395 ELSIF (BSC_BIS_CUSTOM_KPI_UTIL_PUB.G_MDDD_52 = BSC_BIS_CUSTOM_KPI_UTIL_PUB.C_BSC_PATCH_LEVEL_52) THEN
396 RETURN FND_API.G_TRUE;
397 END IF;
398
399 RETURN FND_API.G_FALSE;
400 EXCEPTION
401 WHEN OTHERS THEN
402 RETURN FND_API.G_FALSE;
403 END Is_Short_Name_Available;
404
405 /*
406 PL/SQL API to return region_code
407 */
408
409 FUNCTION get_Region_Code (
410 p_Kpi_Id NUMBER
411 , p_AO_Id NUMBER := NULL
412 ) RETURN VARCHAR2 IS
413 l_Region_Code VARCHAR2(80);
414 BEGIN
415
416 IF (p_Kpi_Id IS NULL) THEN
417 RETURN NULL;
418 END IF;
419
420 IF ((p_AO_Id IS NOT NULL) AND (p_AO_Id <> -1)) THEN
421 l_Region_Code := BSC_UTILITY.C_BSC_UNDERSCORE || p_Kpi_Id || '_' || p_AO_Id;
422 ELSE
423 l_Region_Code := BSC_UTILITY.C_BSC_UNDERSCORE || p_Kpi_Id ;
424 END IF;
425
426 RETURN l_Region_Code;
427
428 END get_Region_Code;
429
430
431 -- ankgoel: bug#3759819
432 -- This API assumes that the Input parameters are not NULL
433 PROCEDURE Get_Pmf_Metadata_By_Objective(
434 p_Dataset_Id IN NUMBER
435 , p_Measure_Short_Name IN VARCHAR2
436 , x_Actual_Source_Type OUT NOCOPY VARCHAR2
437 , x_Actual_Source OUT NOCOPY VARCHAR2
438 , x_Function_Name OUT NOCOPY VARCHAR2
439 )
440 IS
441 CURSOR c_KpiAo IS
442 SELECT indicator kpiId, analysis_option0 aoId
443 FROM bsc_kpi_analysis_measures_b
444 WHERE dataset_id = p_Dataset_Id;
445
446 l_Analysis_Option NUMBER;
447 l_Region_Code VARCHAR2(30);
448 l_Kpi_Id NUMBER;
449 BEGIN
450
451 FOR Kpi_Ao IN c_KpiAo LOOP
452 l_Kpi_Id := Kpi_Ao.kpiId;
453 l_Analysis_Option := Kpi_Ao.aoId;
454 END LOOP;
455
456 l_Region_Code := Get_Region_Code (l_Kpi_Id, l_Analysis_Option);
457 x_Actual_Source_Type := BSC_BIS_CUSTOM_KPI_UTIL_PUB.C_AK_DATASOURCE;
458
459 IF (l_Region_Code IS NOT NULL) THEN
460 x_Actual_Source := l_Region_Code || '.' || p_Measure_Short_Name;
461 ELSE
462 x_Actual_Source := NULL;
463 END IF;
464
465 x_Function_Name := l_Region_Code;
466
467 EXCEPTION
468 WHEN OTHERS THEN
469 RAISE;
470 END Get_Pmf_Metadata_By_Objective;
471
472 --added for bug#4057761
473 FUNCTION get_Next_Alias(
474 p_Alias IN VARCHAR2
475 ) RETURN VARCHAR2
476 IS
477 l_alias VARCHAR2(5);
478 l_return VARCHAR2(5);
479 l_count NUMBER;
480 BEGIN
481 IF (p_Alias IS NULL) THEN
482 l_return := 'A';
483 ELSE
484 l_count := LENGTH(p_Alias);
485 IF (l_count = 1) THEN
486 l_return := 'A0';
487 ELSIF (l_count > 1) THEN
488 l_alias := SUBSTR(p_Alias, 2);
489 l_count := TO_NUMBER(l_alias)+1;
490 l_return := 'A'||TO_CHAR(l_count);
491 END IF;
492 END IF;
493 RETURN l_return;
494 END get_Next_Alias;
495
496 FUNCTION Get_Max_Tab_Name_Length
497 RETURN NUMBER IS
498 l_fnd_length NUMBER;
499 l_tab_length NUMBER;
500 BEGIN
501 /*
502 SELECT data_length INTO l_fnd_length
503 FROM SYS.ALL_TAB_COLUMNS
504 WHERE table_name = 'FND_FORM_FUNCTIONS_TL'
505 AND column_name = 'USER_FUNCTION_NAME';
506
507 SELECT data_length INTO l_tab_length
508 FROM SYS.ALL_TAB_COLUMNS
509 WHERE table_name = 'BSC_TABS_TL'
510 AND column_name = 'NAME';
511
512 --return the min of these two
513 IF (l_fnd_length > l_tab_length) THEN
514 RETURN l_tab_length;
515 ELSE
516 RETURN l_fnd_length;
517 END IF;
518 */
519 RETURN 80;
520 END Get_Max_Tab_Name_Length;
521
522 FUNCTION Get_Max_Tab_Grp_Name_Length
523 RETURN NUMBER IS
524 l_fnd_length NUMBER;
525 l_tab_group_length NUMBER;
526 BEGIN
527 /*
528 SELECT data_length INTO l_fnd_length
529 FROM SYS.ALL_TAB_COLUMNS
530 WHERE table_name = 'FND_FORM_FUNCTIONS_TL'
531 AND column_name = 'USER_FUNCTION_NAME';
532
533 SELECT data_length INTO l_tab_group_length
534 FROM SYS.ALL_TAB_COLUMNS
535 WHERE table_name = 'BSC_TAB_IND_GROUPS_TL'
536 AND column_name = 'NAME';
537
538 --return the min of these two
539 IF (l_fnd_length > l_tab_group_length) THEN
540 RETURN l_tab_group_length;
541 ELSE
542 RETURN l_fnd_length;
543 END IF;
544 */
545 RETURN 80;
546 END Get_Max_Tab_Grp_Name_Length;
547
548 FUNCTION Get_Unqiue_Tab_Name(
549 p_tab_name BSC_TABS_TL.NAME%TYPE
550 ) RETURN BSC_TABS_TL.NAME%TYPE IS
551 l_tab_name BSC_TABS_TL.NAME%TYPE;
552 l_flag BOOLEAN := TRUE;
553 l_count NUMBER;
554 l_loop_count NUMBER := 0;
555 l_alias VARCHAR2(5) := NULL;
556 l_max_tab_length NUMBER;
557 l_overflow NUMBER;
558 BEGIN
559 IF (p_tab_name IS NULL) THEN
560 l_tab_name := 'A'; --extended alias
561 ELSE
562 l_tab_name := trim(p_tab_name);
563 END IF;
564
565 l_max_tab_length := Get_Max_Tab_Name_Length;
566
567 WHILE ((l_flag) AND (l_loop_count < BSC_BIS_CUSTOM_KPI_UTIL_PUB.C_MAX_LOOP_COUNT)) LOOP
568 SELECT count(1) INTO l_count
569 FROM BSC_TABS_VL
570 WHERE UPPER(trim(name)) = UPPER(l_tab_name);
571
572 IF (l_count = 0) THEN
573 l_flag := FALSE;
574 ELSE
575 l_alias := get_Next_Alias(l_alias);
576 l_loop_count := l_loop_count + 1; --prevent infinite loop should alias exhausted
577 --prevent overflow
578 l_overflow := LENGTH(p_tab_name) + LENGTH(l_alias) - l_max_tab_length;
579 IF (l_overflow > 0) THEN
580 l_tab_name := SUBSTR(p_tab_name, 1, LENGTH(p_tab_name)-l_overflow) || l_alias;
581 ELSE
582 l_tab_name := p_tab_name || l_alias;
583 END IF;
584 END IF;
585 END LOOP;
586
587 RETURN l_tab_name;
588
589 EXCEPTION
590 WHEN OTHERS THEN
591 RETURN p_tab_name;
592 END Get_Unqiue_Tab_Name;
593
594 FUNCTION Get_Unqiue_Tab_Name(
595 p_tab_name BSC_TABS_TL.NAME%TYPE,
596 p_tab_id BSC_TABS_TL.TAB_ID%TYPE
597 ) RETURN BSC_TABS_TL.NAME%TYPE IS
598 l_tab_name BSC_TABS_TL.NAME%TYPE;
599 l_flag BOOLEAN := TRUE;
600 l_count NUMBER;
601 l_loop_count NUMBER := 0;
602 l_alias VARCHAR2(5) := NULL;
603 l_max_tab_length NUMBER;
604 l_overflow NUMBER;
605 BEGIN
606 IF (p_tab_name IS NULL) THEN
607 l_tab_name := 'A'; --extended alias
608 ELSE
609 l_tab_name := trim(p_tab_name);
610 END IF;
611
612 l_max_tab_length := Get_Max_Tab_Name_Length;
613
614 WHILE ((l_flag) AND (l_loop_count < BSC_BIS_CUSTOM_KPI_UTIL_PUB.C_MAX_LOOP_COUNT)) LOOP
615 SELECT count(1) INTO l_count
616 FROM BSC_TABS_VL
617 WHERE UPPER(trim(name)) = UPPER(l_tab_name)
618 AND tab_id <> p_tab_id;
619
620 IF (l_count = 0) THEN
621 l_flag := FALSE;
622 ELSE
623 l_alias := get_Next_Alias(l_alias);
624 l_loop_count := l_loop_count + 1; --prevent infinite loop should alias exhausted
625 --prevent overflow
626 l_overflow := LENGTH(p_tab_name) + LENGTH(l_alias) - l_max_tab_length;
627 IF (l_overflow > 0) THEN
628 l_tab_name := SUBSTR(p_tab_name, 1, LENGTH(p_tab_name)-l_overflow) || l_alias;
629 ELSE
630 l_tab_name := p_tab_name || l_alias;
631 END IF;
632 END IF;
633 END LOOP;
634
635 RETURN l_tab_name;
636
637 EXCEPTION
638 WHEN OTHERS THEN
639 RETURN p_tab_name;
640 END Get_Unqiue_Tab_Name;
641
642 FUNCTION Get_Unqiue_Tab_Group_Name(
643 p_tab_grp_name BSC_TAB_IND_GROUPS_TL.NAME%TYPE
644 ) RETURN BSC_TAB_IND_GROUPS_TL.NAME%TYPE IS
645 l_tab_grp_name BSC_TAB_IND_GROUPS_TL.NAME%TYPE;
646 l_flag BOOLEAN := TRUE;
647 l_count NUMBER;
648 l_loop_count NUMBER := 0;
649 l_alias VARCHAR2(5) := NULL;
650 l_max_tab_grp_length NUMBER;
651 l_overflow NUMBER;
652 BEGIN
653 IF (p_tab_grp_name IS NULL) THEN
654 l_tab_grp_name := 'A'; --extended alias
655 ELSE
656 l_tab_grp_name := trim(p_tab_grp_name);
657 END IF;
658
659 l_max_tab_grp_length := Get_Max_Tab_Grp_Name_Length;
660
661 WHILE ((l_flag) AND (l_loop_count < BSC_BIS_CUSTOM_KPI_UTIL_PUB.C_MAX_LOOP_COUNT)) LOOP
662 SELECT count(1) INTO l_count
663 FROM BSC_TAB_IND_GROUPS_VL
664 WHERE UPPER(trim(name)) = UPPER(l_tab_grp_name);
665
666 IF (l_count = 0) THEN
667 l_flag := FALSE;
668 ELSE
669 l_alias := get_Next_Alias(l_alias);
670 l_loop_count := l_loop_count + 1; --prevent infinite loop should alias exhausted
671 --prevent overflow
672 l_overflow := LENGTH(p_tab_grp_name) + LENGTH(l_alias) - l_max_tab_grp_length;
673 IF (l_overflow > 0) THEN
674 l_tab_grp_name := SUBSTR(p_tab_grp_name, 1, LENGTH(p_tab_grp_name)-l_overflow) || l_alias;
675 ELSE
676 l_tab_grp_name := p_tab_grp_name || l_alias;
677 END IF;
678 END IF;
679 END LOOP;
680
681 RETURN l_tab_grp_name;
682
683 EXCEPTION
684 WHEN OTHERS THEN
685 RETURN p_tab_grp_name;
686 END Get_Unqiue_Tab_Group_Name;
687
688 FUNCTION Get_Unqiue_Tab_Group_Name(
689 p_tab_grp_name BSC_TAB_IND_GROUPS_TL.NAME%TYPE,
690 p_tab_grp_id BSC_TAB_IND_GROUPS_TL.IND_GROUP_ID%TYPE
691 ) RETURN BSC_TAB_IND_GROUPS_TL.NAME%TYPE IS
692 l_tab_grp_name BSC_TAB_IND_GROUPS_TL.NAME%TYPE;
693 l_flag BOOLEAN := TRUE;
694 l_count NUMBER;
695 l_loop_count NUMBER := 0;
696 l_alias VARCHAR2(5) := NULL;
697 l_max_tab_grp_length NUMBER;
698 l_overflow NUMBER;
699 BEGIN
700 IF (p_tab_grp_name IS NULL) THEN
701 l_tab_grp_name := 'A'; --extended alias
702 ELSE
703 l_tab_grp_name := trim(p_tab_grp_name);
704 END IF;
705
706 l_max_tab_grp_length := Get_Max_Tab_Grp_Name_Length;
707
708 WHILE ((l_flag) AND (l_loop_count < BSC_BIS_CUSTOM_KPI_UTIL_PUB.C_MAX_LOOP_COUNT)) LOOP
709 SELECT count(1) INTO l_count
710 FROM BSC_TAB_IND_GROUPS_VL
711 WHERE UPPER(trim(name)) = UPPER(l_tab_grp_name)
712 AND IND_GROUP_ID <> p_tab_grp_id;
713
714 IF (l_count = 0) THEN
715 l_flag := FALSE;
716 ELSE
717 l_alias := get_Next_Alias(l_alias);
718 l_loop_count := l_loop_count + 1; --prevent infinite loop should alias exhausted
719 --prevent overflow
720 l_overflow := LENGTH(p_tab_grp_name) + LENGTH(l_alias) - l_max_tab_grp_length;
721 IF (l_overflow > 0) THEN
722 l_tab_grp_name := SUBSTR(p_tab_grp_name, 1, LENGTH(p_tab_grp_name)-l_overflow) || l_alias;
723 ELSE
724 l_tab_grp_name := p_tab_grp_name || l_alias;
725 END IF;
726 END IF;
727 END LOOP;
728
729 RETURN l_tab_grp_name;
730
731 EXCEPTION
732 WHEN OTHERS THEN
733 RETURN p_tab_grp_name;
734 END Get_Unqiue_Tab_Group_Name;
735
736 -- API to check if the report is S2E or AG Report.
737
738 FUNCTION is_Report_S2E(
739 p_Region_Function_Name IN VARCHAR2
740 ) RETURN VARCHAR2
741 IS
742 l_Count NUMBER;
743 BEGIN
744
745 l_Count := 0;
746
747 SELECT COUNT(1) INTO l_Count
748 FROM BSC_KPIS_B K
749 WHERE K.SHORT_NAME = p_Region_Function_Name;
750
751 IF l_Count <> 0 THEN
752 RETURN 'N';
753 END IF;
754
755 RETURN 'Y';
756
757 EXCEPTION
758 WHEN OTHERS THEN
759 RETURN 'N';
760 END is_Report_S2E;
761
762
763 -- given the objective short_name, tells us if the
764 -- the Objective was created form Report Designer.
765 FUNCTION is_Objective_Report_Type (
766 p_Short_Name IN VARCHAR2
767 ) RETURN VARCHAR2
768 IS
769 l_Count NUMBER;
770 BEGIN
771
772 IF(p_Short_Name IS NULL) THEN
773 RETURN FND_API.G_FALSE;
774 END IF;
775
776 SELECT COUNT(1) INTO l_Count
777 FROM BIS_DISPLAY_FORM_FUNC_V B
778 WHERE B.FUNCTION_NAME = p_Short_Name
779 AND B.OBJECT_TYPE = BSC_BIS_CUSTOM_KPI_UTIL_PUB.C_OBJECT_TYPE_REPORT;
780 --//For Simulation Objectives we are creating AG Reports but we don't want it to be projected as
781 --//as the report.thats why we are checking the condition that even if form function exists
782 --//and the objective config_type is 7then it should be treated as an objective not as a report.
783
784 IF (l_Count <> 0) THEN
785 SELECT COUNT(0)
786 INTO l_Count
787 FROM bsc_kpis_b
788 WHERE short_name = p_Short_Name
789 AND config_Type =7;
790 IF(l_Count <> 0) THEN
791 RETURN FND_API.G_FALSE;
792 ELSE
793 RETURN FND_API.G_TRUE;
794 END IF;
795 ELSE -- Bug#4476730
796 SELECT COUNT(1) INTO l_Count
797 FROM BSC_KPIS_B
798 WHERE SHORT_NAME = p_Short_Name
799 AND PROTOTYPE_FLAG = BSC_BIS_CUSTOM_KPI_UTIL_PUB.C_DELETED_OBJECTIVE_FLAG; -- deleted flag
800 -- bug 5959433 we don't need following condition here
801 -- in simulation flow code won't come here
802 --AND config_type =7;
803
804 IF (l_Count <> 0) THEN
805 RETURN FND_API.G_TRUE;
806 ELSE
807 RETURN FND_API.G_FALSE;
808 END IF;
809 END IF;
810
811 RETURN FND_API.G_FALSE;
812
813 EXCEPTION
814 WHEN OTHERS THEN
815 RETURN FND_API.G_FALSE;
816 END is_Objective_Report_Type;
817
818
819 -- given the objective short_name, tells us if the
820 -- the Objective was created from Page Designer/Configure.
821 FUNCTION is_Objective_Page_Type (
822 p_Short_Name IN VARCHAR2
823 ) RETURN VARCHAR2
824 IS
825 l_Count NUMBER;
826 BEGIN
827 SELECT COUNT(1) INTO l_Count
828 FROM BIS_DISPLAY_FORM_FUNC_V B
829 WHERE B.FUNCTION_NAME = p_Short_Name
830 AND B.OBJECT_TYPE = BSC_BIS_CUSTOM_KPI_UTIL_PUB.C_OBJECT_TYPE_PAGE;
831
832 IF (l_Count <> 0) THEN
833 RETURN FND_API.G_TRUE;
834 END IF;
835
836 RETURN FND_API.G_FALSE;
837
838 EXCEPTION
839 WHEN OTHERS THEN
840 RETURN FND_API.G_FALSE;
841 END is_Objective_Page_Type;
842
843
844 -- Added for Bug#4369210
845 FUNCTION Is_Objective_AutoGen_Type (
846 p_Short_Name IN VARCHAR2
847 ) RETURN VARCHAR2
848 IS
849 l_Count NUMBER;
850 l_Region_Code AK_REGIONS.REGION_CODE%TYPE;
851 l_Source_Type AK_REGIONS.ATTRIBUTE10%TYPE;
852 BEGIN
853
854 IF (p_Short_Name IS NULL) THEN
855 RETURN FND_API.G_FALSE;
856 END IF;
857
858 -- we populate the Objective with FUNCTION_NAME of the report.
859 l_Region_Code := BIS_PMV_UTIL.GetReportRegion(p_Short_Name);
860
861 SELECT R.ATTRIBUTE10 INTO l_Source_Type
862 FROM AK_REGIONS R
863 WHERE R.REGION_CODE = l_Region_Code;
864
865 IF (l_Source_Type = C_BSC_DATA_SOURCE) THEN
866 RETURN FND_API.G_TRUE;
867 END IF;
868
869 RETURN FND_API.G_FALSE;
870
871 EXCEPTION
872 WHEN OTHERS THEN
873 RETURN FND_API.G_FALSE;
874 END Is_Objective_AutoGen_Type;
875
876 /*
877 * Moved from BSC_BIS_KPI_CRUD_PUB since it is used by non-BSC too
878 * WAM KPI shows the formats in the drop-down from this table.
879 */
880 FUNCTION Get_Format_Mask (
881 p_Format_Id NUMBER
882 ) RETURN VARCHAR2 IS
883
884 l_Format_Mask VARCHAR2(15);
885 l_Format_Id NUMBER;
886
887 BEGIN
888
889 l_Format_Id := p_Format_Id;
890
891 -- Check for standard format ids
892 IF (p_Format_Id NOT IN (0, 1, 2, 5, 6, 7)) THEN
893 l_Format_Id := 0;
894 END IF;
895
896 -- Changed the Format Mask to use 0
897 SELECT REPLACE(FORMAT,'#','9') FORMAT
898 INTO l_Format_Mask
899 FROM BSC_SYS_FORMATS
900 WHERE FORMAT_ID = l_Format_Id;
901
902 RETURN l_Format_Mask;
903 END Get_Format_Mask;
904
905
906 END BSC_BIS_CUSTOM_KPI_UTIL_PUB;
[Home] [Help]