[Home] [Help]
PACKAGE BODY: APPS.BSC_BIS_DIMENSION_PUB
Source
1 PACKAGE BODY BSC_BIS_DIMENSION_PUB AS
2 /* $Header: BSCGPMDB.pls 120.9 2007/08/02 13:39:22 psomesul 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 | BSCCPMDB.pls |
10 REM | |
11 REM | DESCRIPTION |
12 REM | Module: Wrapper for Dimension in PMF & Dimension Group in BSC |
13 REM | part of PMD APIs |
14 REM | |
15 REM | NOTES |
16 REM | 02-MAY-2003 PAJOHRI Created. |
17 REM | 17-JUL-2003 ADRAO Bug#3054935 Changed following procedures |
18 REM | BSC_BIS_KPI_MEAS_PUB.Create_Dim_Objs_In_DSet() |
19 REM | BSC_BIS_KPI_MEAS_PUB.UnCreate_Dim_Objs_In_DSet() |
20 REM | to use 'NULL' timestamp |
21 REM | 18-JUL-2003 PAJOHRI Bug #3053521 |
22 REM | 22-JUL-2003 ADRAO Bug #3034094 - Fixed in Update_Dimension |
23 REM | 30-JUL-2003 PAJOHRI Bug #3075316, 3073486 |
24 REM | 09-SEP-2003 ASHANKAR Bug #3129610 |
25 REM | 13-SEP-2003 MAHRAO Fix for bug# 3099977, added p_create_view flag |
26 REM | 20-OCT-2003 PAJOHRI Bug #3179995 |
27 REM | 20-OCT-2003 PAJOHRI Bug #3179995 |
28 REM | 04-NOV-2003 PAJOHRI Bug #3152258 |
29 REM | 04-NOV-2003 PAJOHRI Bug #3220613 |
30 REM | 04-NOV-2003 PAJOHRI Bug #3232366 |
31 REM | 04-NOV-2003 PAJOHRI Bug #3269384 |
32 REM | 08-DEC-2003 KYADAMAK Bug #3225685 |
33 REM | 02-MAR-2004 ankgoel Bug #3464470 |
34 REM | 30-MAR-2004 KYADAMAK BUG #3516466 Passing default appid as 271 |
35 REM | 12-APR-2004 PAJOHRI Bug #3426566, modified the logic to use dimension|
36 REM | 'UNASSIGNED' always if there if no Dimension/ |
37 REM | Dimension Object association for PMF and retain |
38 REM | 'All Enable' flag |
39 REM | 19-APR-2004 PAJOHRI Bug #3541933, fix for filter buttons |
40 REM | 23-APR-2004 ASHANKAR Bug #3518610,Added the fucntion Validate |
41 REM | listbutton |
42 REM | 05-MAY-2004 PAJOHRI Bug #3590436, fixed Sync_Dimensions_In_Bis |
43 REM | 16-JUN-2004 PAJOHRI Bug #3659486, to support 'All Enable' flag in |
44 REM | Dimension/Dimension Object Association Page |
45 REM | 09-AUG-2004 sawu Used c_BSC_DIM constant in create_dimension |
46 REM | 11-AUG-2004 sawu Added create_dimension() for bug#3819855 with |
47 REM | p_is_default_short_name |
48 REM | 17-AUG-2004 wleung modified Bug#3784852 on Assign_Unassign_Dim_Objs |
49 REM | 08-SEP-2004 visuri Added Dim_With_Single_Dim_Obj() and |
50 REM | Is_Dim_Empty() for bug #3784852 |
51 REM | 09-SEP-2004 visuri Shifted Remove_Empty_Dims_For_DimSet() from |
52 REM | BSC_BIS_KPI_MEAS_PUB for bug #3784852 |
53 REM | 08-OCT-2004 rpenneru added Modified for bug#3939995 |
54 REM | 27-OCT-2004 sawu Bug#3947903: added Is_Objective_Assigned() |
55 REM | 08-Feb-05 ankgoel Enh#4172034 DD Seeding by Product Teams |
56 REM | 02-Mar-05 ppandey Bug#4211876 Prmary Dim provided for |
57 REM | Update_Dimension_Level should not be accepte. |
58 REM | 29-Mar-05 ankagarw bug# 4218260 Unable to save comparison source |
59 REM | label lov value |
60 REM | 31-MAR-05 adrao Modified API check_sametype_dims to remove |
61 REM | disctinction betweem BSC and BIS Dimesion Objs |
62 REM | 11-APR-2005 kyadamak bug#4290070 Not recreating views for rolling dims|
63 REM | 06-JUN-2005 mdamle Enh#4403547 Set default p_commit to false for |
64 REM | dim. group apis called from EOs |
65 REM | 18-Jul-2005 ppandey Enh #4417483, Restrict Internal/Calendar Dims |
66 REM | 20-Jul-2005 ppandey Bug #4495539, MIXED Dim Obj not allowed from DD |
67 REM | 11-AUG-2005 ppandey Bug #4324947 Validation for Dim,Dim Obj in Rpt |
68 REM | 06-Jan-2006 akoduri Enh#4739401 - Hide Dimensions/Dim Objects |
69 REM | 10-FEB-2006 akoduri Bug#4997042 Cascade 'All' property from dim |
70 REM | designer to dim groups of Reports |
71 REM | 15-JUN-2006 ashankar Bug#5254737 Made changes to Create_Dimension |
72 REM | Method.Removed the parameter value 'TRUE' in |
73 REM | FND_MESSAGE.SET_TOKEN API |
74 REM | 27-Jun-07 ashankar Bug#6134149 synching up the dim obj props to |
75 REM | BSC_KPI_MEASURE_PROPS table |
76 REM | 02-AUG-07 psomesul B#6168487-Handling dim. object comparison settings |
77 REM +=======================================================================+
78 */
79 CONFIG_LIMIT_DIM CONSTANT NUMBER := 8;
80 /*********************************************************************************/
81
82 TYPE KPI_Dim_Set_Type IS Record
83 ( p_kpi_id BSC_KPI_DIM_SETS_TL.indicator%TYPE
84 , p_dim_set_id BSC_KPI_DIM_SETS_TL.dim_set_id%TYPE
85 , p_short_name BSC_SYS_DIM_GROUPS_TL.short_name%TYPE
86 );
87 TYPE KPI_Dim_Set_Table_Type IS TABLE OF KPI_Dim_Set_Type INDEX BY BINARY_INTEGER;
88 /*********************************************************************************/
89 TYPE Dim_Obj_Relations_Type IS Record
90 (
91 p_dim_obj_id BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE
92 );
93 TYPE Dim_Obj_Table_Type IS TABLE OF Dim_Obj_Relations_Type INDEX BY BINARY_INTEGER;
94 /*********************************************************************************/
95
96 TYPE dimobj_objective_kpis_type IS RECORD
97 ( p_indicator BSC_KPIS_B.indicator%TYPE ,
98 p_kpi_measure_id BSC_DB_DATASET_DIM_SETS_V.kpi_measure_id%TYPE,
99 p_short_name BSC_SYS_DIM_LEVELS_B.short_name%TYPE
100 );
101 TYPE dimobj_obj_kpis_tbl_type IS TABLE OF dimobj_objective_kpis_type INDEX BY BINARY_INTEGER;
102
103 /*********************************************************************************/
104 FUNCTION Attmpt_Recr_View
105 ( p_dim_lvl_shrt_name VARCHAR2
106 , x_dim_lvl_name OUT NOCOPY VARCHAR2
107 ) RETURN BOOLEAN;
108 /*********************************************************************************/
109 FUNCTION check_sametype_dimobjs
110 ( p_dim_name IN VARCHAR2
111 , p_dim_short_name IN VARCHAR2
112 , p_dim_short_names IN VARCHAR2
113 , p_Restrict_Dim_Validate IN VARCHAR2 := NULL
114 , x_dim_type OUT NOCOPY VARCHAR2
115 , x_return_status OUT NOCOPY VARCHAR2
116 , x_msg_count OUT NOCOPY NUMBER
117 , x_msg_data OUT NOCOPY VARCHAR2
118 )RETURN BOOLEAN;
119
120 FUNCTION is_color_change_required (
121 p_old_default IN VARCHAR2,
122 p_new_default IN VARCHAR2,
123 p_obj_id IN bsc_kpis_b.indicator%TYPE,
124 p_kpi_measure_id IN bsc_kpi_measure_props.kpi_measure_id%TYPE
125 )
126 RETURN NUMBER;
127
128 FUNCTION get_kpi_flag_change (
129 p_old_default IN VARCHAR2,
130 p_new_default IN VARCHAR2,
131 p_indicator IN bsc_kpis_b.indicator%TYPE,
132 p_dim_obj_objs_tbl IN OUT NOCOPY BSC_BIS_DIMENSION_PUB.dimobj_obj_kpis_tbl_type
133 )
134 RETURN NUMBER;
135
136 /*********************************************************************************/
137 PROCEDURE Restrict_Internal_Dim_Objs
138 ( p_dim_short_name IN VARCHAR2
139 , p_assign_dim_obj_names IN VARCHAR2
140 , p_unassign_dim_obj_names IN VARCHAR2
141 , x_return_status OUT NOCOPY VARCHAR2
142 , x_msg_count OUT NOCOPY NUMBER
143 , x_msg_data OUT NOCOPY VARCHAR2
144 );
145
146 /*********************************************************************************/
147 FUNCTION get_Next_Alias
148 (
149 p_Alias IN VARCHAR2
150 ) RETURN VARCHAR2
151 IS
152 l_alias VARCHAR2(4);
153 l_return VARCHAR2(4);
154 l_count NUMBER;
155 BEGIN
156 IF (p_Alias IS NULL) THEN
157 l_return := 'A';
158 ELSE
159 l_count := LENGTH(p_Alias);
160 IF (l_count = 1) THEN
161 l_return := 'A0';
162 ELSIF (l_count > 1) THEN
163 l_alias := SUBSTR(p_Alias, 2);
164 l_count := TO_NUMBER(l_alias)+1;
165 l_return := SUBSTR(p_Alias, 1, 1)||TO_CHAR(l_count);
166 END IF;
167 END IF;
168 RETURN l_return;
169 END get_Next_Alias;
170 /*********************************************************************************************
171 Returns the Dim_Group_ID of BIS Dimension
172 *********************************************************************************************/
173 FUNCTION Get_Bis_Dimension_ID
174 ( p_Short_Name IN BIS_DIMENSIONS.Short_Name%TYPE
175 ) RETURN NUMBER IS
176
177 l_dim_id BIS_DIMENSIONS.Dimension_ID%TYPE;
178
179 CURSOR c_Dim_Group_Id IS
180 SELECT Dimension_ID
181 FROM BIS_DIMENSIONS
182 WHERE Short_Name = p_Short_Name;
183 BEGIN
184 IF (c_Dim_Group_Id%ISOPEN) THEN
185 CLOSE c_Dim_Group_Id;
186 END IF;
187 OPEN c_Dim_Group_Id;
188 FETCH c_Dim_Group_Id INTO l_dim_id;
189 CLOSE c_Dim_Group_Id;
190 RETURN l_dim_id;
191 EXCEPTION
192 WHEN OTHERS THEN
193 IF (c_Dim_Group_Id%ISOPEN) THEN
194 CLOSE c_Dim_Group_Id;
195 END IF;
196 RETURN l_dim_id;
197 END Get_Bis_Dimension_ID;
198
199 /*********************************************************************************************
200 Returns the Dim_Group_ID of BSC Dimension
201 *********************************************************************************************/
202 FUNCTION Get_Bsc_Dimension_ID
203 ( p_Short_Name IN BSC_SYS_DIM_GROUPS_TL.Short_Name%TYPE
204 ) RETURN NUMBER IS
205
206 l_dim_id BSC_SYS_DIM_GROUPS_TL.Dim_Group_ID%TYPE;
207
208 CURSOR c_Dim_Group_Id IS
209 SELECT Dim_Group_ID
210 FROM BSC_SYS_DIM_GROUPS_VL
211 WHERE Short_Name = p_Short_Name;
212 BEGIN
213 IF (c_Dim_Group_Id%ISOPEN) THEN
214 CLOSE c_Dim_Group_Id;
215 END IF;
216 OPEN c_Dim_Group_Id;
217 FETCH c_Dim_Group_Id INTO l_dim_id;
218 CLOSE c_Dim_Group_Id;
219
220 RETURN l_dim_id;
221 EXCEPTION
222 WHEN OTHERS THEN
223 IF (c_Dim_Group_Id%ISOPEN) THEN
224 CLOSE c_Dim_Group_Id;
225 END IF;
226 RETURN l_dim_id;
227 END Get_Bsc_Dimension_ID;
228 /*********************************************************************************************
229 Checks if a Dimension is Attached to a Objective
230 *********************************************************************************************/
231 FUNCTION Is_Dimension_in_Ind
232 ( p_dim_group_id IN BSC_SYS_DIM_GROUPS_TL.Dim_Group_ID%TYPE
233 ) RETURN BOOLEAN IS
234
235 l_return_val BOOLEAN:= FALSE;
236 l_Count NUMBER := 0;
237 BEGIN
238
239 SELECT COUNT(1) INTO l_Count
240 FROM BSC_KPI_DIM_GROUPS
241 WHERE DIM_GROUP_ID= p_dim_group_id;
242 IF (l_Count = 0) THEN
243 RETURN FALSE;
244 ELSE
245 RETURN TRUE;
246 END IF;
247 EXCEPTION
248 WHEN OTHERS THEN
249 RETURN l_return_val;
250 END Is_Dimension_in_Ind;
251 /*********************************************************************************************
252 Returns the Name of BSC Dimension
253 *********************************************************************************************/
254 FUNCTION Get_Bsc_Dimension_Name
255 ( p_Short_Name IN BSC_SYS_DIM_GROUPS_TL.Short_Name%TYPE
256 ) RETURN VARCHAR2 IS
257
258 l_dim_name BSC_SYS_DIM_GROUPS_TL.Name%TYPE;
259
260 CURSOR c_Dim_Group_Name IS
261 SELECT Name
262 FROM BSC_SYS_DIM_GROUPS_VL
263 WHERE Short_Name = p_Short_Name;
264 BEGIN
265 IF (c_Dim_Group_Name%ISOPEN) THEN
266 CLOSE c_Dim_Group_Name;
267 END IF;
268 OPEN c_Dim_Group_Name;
269 FETCH c_Dim_Group_Name INTO l_dim_name;
270 CLOSE c_Dim_Group_Name;
271
272 RETURN l_dim_name;
273 EXCEPTION
274 WHEN OTHERS THEN
275 IF (c_Dim_Group_Name%ISOPEN) THEN
276 CLOSE c_Dim_Group_Name;
277 END IF;
278 RETURN l_dim_name;
279 END Get_Bsc_Dimension_Name;
280 /*********************************************************************************************
281 Function to check Dimension/Dimension Object if association exists
282 *********************************************************************************************/
283 FUNCTION is_Relation_Exists
284 ( p_Dim_Grp_Id IN BSC_SYS_DIM_GROUPS_TL.Dim_Group_ID%TYPE
285 , p_Dim_Level_Id IN BSC_SYS_DIM_LEVELS_B.Dim_Level_ID%TYPE
286 ) RETURN BOOLEAN IS
287 l_flag BOOLEAN := FALSE;
288 l_Count NUMBER := 0;
289 BEGIN
290 SELECT COUNT(1) INTO l_Count
291 FROM BSC_SYS_DIM_LEVELS_BY_GROUP
292 WHERE Dim_Level_Id = p_Dim_Level_Id
293 AND Dim_Group_Id = p_Dim_Grp_Id;
294 IF (l_Count = 0) THEN
295 RETURN FALSE;
296 ELSE
297 RETURN TRUE;
298 END IF;
299 EXCEPTION
300 WHEN OTHERS THEN
301 RETURN l_flag;
302 END is_Relation_Exists;
303 /*********************************************************************************
304 Set the ALL Enable Flag Primary Flag
305 *********************************************************************************/
306 FUNCTION Get_Primary_All_Flag(p_Dim_Obj_Short_Name IN VARCHAR2)
307 RETURN NUMBER IS
308 l_Bsc_Dim_Obj_ID BSC_SYS_DIM_LEVELS_B.Dim_Level_ID%TYPE;
309 l_Bsc_Group_ID BSC_SYS_DIM_GROUPS_TL.Dim_Group_ID%TYPE;
310 l_Dim_Short_Name BIS_DIMENSIONS.Short_Name%TYPE;
311 l_All_Flag BSC_SYS_DIM_LEVELS_BY_GROUP.Total_Flag%TYPE;
312
313 CURSOR c_Bis_Levels IS
314 SELECT B.Short_Name
315 FROM BIS_LEVELS A
316 , BIS_DIMENSIONS B
317 WHERE A.Short_Name = p_Dim_Obj_Short_Name
318 AND A.Dimension_Id = B.Dimension_Id;
319
320 CURSOR c_All_Pri_Flag IS
321 SELECT Total_Flag
322 FROM BSC_SYS_DIM_LEVELS_BY_GROUP
323 WHERE Dim_Level_Id = l_Bsc_Dim_Obj_ID
324 AND Dim_Group_Id = l_Bsc_Group_ID;
325 BEGIN
326 --DBMS_OUTPUT.PUT_LINE('Entered inside BSC_BIS_DIMENSION_PUB.Get_Primary_All_Flag procedure');
327 IF (c_Bis_Levels%ISOPEN) THEN
328 CLOSE c_Bis_Levels;
329 END IF;
330 OPEN c_Bis_Levels;
331 FETCH c_Bis_Levels
332 INTO l_Dim_Short_Name;
333 CLOSE c_Bis_Levels;
334
335 l_Bsc_Group_ID := BSC_BIS_DIMENSION_PUB.Get_Bsc_Dimension_ID(l_Dim_Short_Name);
336 l_Bsc_Dim_Obj_ID := BSC_BIS_DIM_OBJ_PUB.Get_Bsc_Dim_Obj_ID(p_Dim_Obj_Short_Name);
337
338 IF (c_All_Pri_Flag%ISOPEN) THEN
339 CLOSE c_All_Pri_Flag;
340 END IF;
341 OPEN c_All_Pri_Flag;
342 FETCH c_All_Pri_Flag INTO l_All_Flag;
343 CLOSE c_All_Pri_Flag;
344 RETURN NVL(l_All_Flag, -1);
345 --DBMS_OUTPUT.PUT_LINE('Exiting from BSC_BIS_DIMENSION_PUB.Get_Primary_All_Flag procedure');
346 EXCEPTION
347 WHEN OTHERS THEN
348 RETURN -1;
349 END Get_Primary_All_Flag;
350 /*********************************************************************************************
351 Returns the number of dimension associated with the dimension object
352 *********************************************************************************************/
353 FUNCTION Get_Number_Of_Dimensions
354 ( p_Dim_Level_Id IN BSC_SYS_DIM_LEVELS_B.Dim_Level_ID%TYPE
355 ) RETURN NUMBER IS
356 l_Count NUMBER := 0;
357 BEGIN
358 SELECT COUNT(Dim_Group_ID) INTO l_Count
359 FROM BSC_SYS_DIM_LEVELS_BY_GROUP
360 WHERE Dim_Level_Id = p_Dim_Level_Id;
361 RETURN l_Count;
362 EXCEPTION
363 WHEN OTHERS THEN
364 RETURN l_Count;
365 END Get_Number_Of_Dimensions;
366 --=========================================================================================
367 PROCEDURE Sync_All_Enable_Flag
368 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
369 , p_Dim_Obj_Short_Name IN VARCHAR2
370 , x_return_status OUT NOCOPY VARCHAR2
371 , x_msg_count OUT NOCOPY NUMBER
372 , x_msg_data OUT NOCOPY VARCHAR2
373 ) IS
374 l_Bsc_Level_ID BSC_SYS_DIM_LEVELS_B.Dim_Level_ID%TYPE;
375 l_Bsc_Group_ID BSC_SYS_DIM_GROUPS_TL.Dim_Group_ID%TYPE;
376 l_Dim_Short_Name BIS_DIMENSIONS.Short_Name%TYPE;
377 l_Total_Flag BSC_SYS_DIM_LEVELS_BY_GROUP.Total_Flag%TYPE := NULL;
378
379 CURSOR c_Bis_Levels IS
380 SELECT B.Short_Name
381 FROM BIS_LEVELS A
382 , BIS_DIMENSIONS B
383 WHERE A.Short_Name = p_Dim_Obj_Short_Name
384 AND A.Dimension_Id = B.Dimension_Id;
385
386 CURSOR c_Total_Flag IS
387 SELECT Total_Flag
388 FROM BSC_SYS_DIM_LEVELS_BY_GROUP
389 WHERE Dim_Group_Id = l_Bsc_Group_ID
390 AND Dim_Level_Id = l_Bsc_Level_ID;
391 BEGIN
392 --DBMS_OUTPUT.PUT_LINE('Entered inside BSC_BIS_DIMENSION_PUB.Sync_All_Enable_Flag procedure');
393 SAVEPOINT SyncPMFAllInPMD;
394 IF (BSC_BIS_DIM_OBJ_PUB.Get_Dim_Obj_Source(NULL, p_Dim_Obj_Short_Name) = 'PMF') THEN
395 l_Bsc_Level_ID := BSC_BIS_DIM_OBJ_PUB.Get_Bsc_Dim_Obj_ID(p_Dim_Obj_Short_Name);
396
397 IF (c_Bis_Levels%ISOPEN) THEN
398 CLOSE c_Bis_Levels;
399 END IF;
400 OPEN c_Bis_Levels;
401 FETCH c_Bis_Levels
402 INTO l_Dim_Short_Name;
403 CLOSE c_Bis_Levels;
404
405 --sync up all values
406 l_Bsc_Group_ID := BSC_BIS_DIMENSION_PUB.Get_Bsc_Dimension_ID(l_Dim_Short_Name);
407 IF (c_Total_Flag%ISOPEN) THEN
408 CLOSE c_Total_Flag;
409 END IF;
410 OPEN c_Total_Flag;
411 FETCH c_Total_Flag INTO l_Total_Flag;
412 CLOSE c_Total_Flag;
413 IF (l_Total_Flag IS NOT NULL) THEN
414 UPDATE BSC_SYS_DIM_LEVELS_BY_GROUP
415 SET Total_Flag = l_Total_Flag
416 WHERE Dim_Level_Id = l_Bsc_Level_ID;
417 END IF;
418 END IF;
419 IF (p_commit = FND_API.G_TRUE) THEN
420 COMMIT;
421 --DBMS_OUTPUT.PUT_LINE('COMMIT SUCCESSFUL');
422 END IF;
423 x_return_status := FND_API.G_RET_STS_SUCCESS;
424 --DBMS_OUTPUT.PUT_LINE('Exiting from BSC_BIS_DIMENSION_PUB.Sync_All_Enable_Flag procedure');
425 EXCEPTION
426 WHEN OTHERS THEN
427 ROLLBACK TO SyncPMFAllInPMD;
428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
429 IF (x_msg_data IS NULL) THEN
430 x_msg_data := SQLERRM||' -> BSC_BIS_DIMENSION_PUB.Sync_All_Enable_Flag ';
431 END IF;
432 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
433 END Sync_All_Enable_Flag;
434 --=========================================================================================
435 PROCEDURE Sync_Dimensions_In_Bis
436 ( p_commit IN VARCHAR2 := FND_API.G_TRUE
437 , p_Dim_Obj_Short_Name IN VARCHAR2
438 , p_Sync_Flag IN BOOLEAN
439 , x_return_status OUT NOCOPY VARCHAR2
440 , x_msg_count OUT NOCOPY NUMBER
441 , x_msg_data OUT NOCOPY VARCHAR2
442 ) IS
443 l_Bsc_Level_ID BSC_SYS_DIM_LEVELS_B.Dim_Level_ID%TYPE;
444 l_Bsc_Group_ID BSC_SYS_DIM_GROUPS_TL.Dim_Group_ID%TYPE := NULL;
445 l_Bis_Group_ID BIS_DIMENSIONS.Dimension_ID%TYPE;
446
447 l_Old_Bsc_Group_ID BSC_SYS_DIM_GROUPS_TL.Dim_Group_ID%TYPE;
448 l_Old_Dim_Short_Name BIS_DIMENSIONS.Short_Name%TYPE;
449
450 l_Dim_Short_Name BIS_DIMENSIONS.Short_Name%TYPE;
451
452 l_Sync_Flag BOOLEAN;
453
454 l_bis_dimension_level_rec BIS_DIMENSION_LEVEL_PUB.Dimension_Level_Rec_Type;
455 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
456
457 l_bis_dim_level_rec BIS_LEVELS%ROWTYPE;
458
459 CURSOR c_Bis_Levels IS
460 SELECT B.Short_Name
461 FROM BIS_LEVELS A
462 , BIS_DIMENSIONS B
463 WHERE A.Short_Name = p_Dim_Obj_Short_Name
464 AND A.Dimension_Id = B.Dimension_Id;
465
466 CURSOR c_Dim_Groups IS
467 SELECT Dim_Group_Id
468 FROM BSC_SYS_DIM_LEVELS_BY_GROUP
469 WHERE Dim_Level_Id = l_Bsc_Level_ID
470 ORDER BY Dim_Level_Index DESC;
471
472 CURSOR c_Dim_Short_Name IS
473 SELECT Short_Name
474 FROM BSC_SYS_DIM_GROUPS_VL
475 WHERE Dim_Group_Id = l_Bsc_Group_ID;
476
477 CURSOR c_Dim_Level_Info IS
478 SELECT LEVEL_ID
479 , SHORT_NAME
480 , DIMENSION_ID
481 , LEVEL_VALUES_VIEW_NAME
482 , WHERE_CLAUSE
483 , CREATION_DATE
484 , CREATED_BY
485 , LAST_UPDATE_DATE
486 , LAST_UPDATED_BY
487 , LAST_UPDATE_LOGIN
488 , SOURCE
489 , COMPARISON_LABEL_CODE
490 , ATTRIBUTE_CODE
491 , APPLICATION_ID
492 , VIEW_OBJECT_NAME
493 , DEFAULT_VALUES_API
494 , DEFAULT_SEARCH
495 , LONG_LOV
496 , MASTER_LEVEL
497 , ENABLED
498 , DRILL_TO_FORM_FUNCTION
499 , HIDE_IN_DESIGN
500 FROM BIS_LEVELS A
501 WHERE A.Short_Name = p_Dim_Obj_Short_Name;
502 BEGIN
503
504 SAVEPOINT SyncPMFBSCDimsInPMD;
505 IF (c_Bis_Levels%ISOPEN) THEN
506 CLOSE c_Bis_Levels;
507 END IF;
508
509 OPEN c_Bis_Levels;
510 FETCH c_Bis_Levels
511 INTO l_Old_Dim_Short_Name;
512 CLOSE c_Bis_Levels;
513
514 l_Old_Bsc_Group_ID := BSC_BIS_DIMENSION_PUB.Get_Bsc_Dimension_ID(l_Old_Dim_Short_Name);
515
516 l_Bsc_Level_ID := BSC_BIS_DIM_OBJ_PUB.Get_Bsc_Dim_Obj_ID(p_Dim_Obj_Short_Name);
517
518
519 l_Sync_Flag := TRUE;
520 IF (p_Sync_Flag) THEN
521 FOR cd IN c_Dim_Groups LOOP
522 l_Bsc_Group_ID := cd.Dim_Group_Id;
523
524 IF (l_Old_Bsc_Group_ID = l_Bsc_Group_ID) THEN
525 l_Sync_Flag := FALSE;
526 EXIT;
527 END IF;
528 END LOOP;
529 END IF;
530
531 IF (l_Sync_Flag) THEN
532 IF (l_Bsc_Group_ID IS NOT NULL) THEN
533 IF (c_Dim_Short_Name%ISOPEN) THEN
534 CLOSE c_Dim_Short_Name;
535 END IF;
536 OPEN c_Dim_Short_Name;
537 FETCH c_Dim_Short_Name INTO l_Dim_Short_Name;
538 CLOSE c_Dim_Short_Name;
539 ELSE
540 l_Dim_Short_Name := BSC_BIS_DIMENSION_PUB.Unassigned_Dim;
541 END IF;
542 l_Bis_Group_ID := BSC_BIS_DIMENSION_PUB.Get_Bis_Dimension_ID(l_Dim_Short_Name);
543
544 IF (c_Dim_Level_Info%ISOPEN) THEN
545 CLOSE c_Dim_Level_Info;
546 END IF;
547
548 OPEN c_Dim_Level_Info;
549 FETCH c_Dim_Level_Info
550 INTO l_bis_dim_level_rec;
551 CLOSE c_Dim_Level_Info;
552
553 --sync bis dimension objects also
554 l_bis_dimension_level_rec.Dimension_Level_ID := l_bis_dim_level_rec.level_id;
555 l_bis_dimension_level_rec.Level_Values_View_Name := l_bis_dim_level_rec.Level_Values_View_Name;
556 l_bis_dimension_level_rec.where_Clause := l_bis_dim_level_rec.WHERE_CLAUSE;
557 l_bis_dimension_level_rec.CREATION_DATE := l_bis_dim_level_rec.CREATION_DATE;
558 l_bis_dimension_level_rec.CREATED_BY := l_bis_dim_level_rec.CREATED_BY;
559 l_bis_dimension_level_rec.SOURCE := l_bis_dim_level_rec.SOURCE;
560 l_bis_dimension_level_rec.COMPARISON_LABEL_CODE := l_bis_dim_level_rec.COMPARISON_LABEL_CODE;
561 l_bis_dimension_level_rec.ATTRIBUTE_CODE := l_bis_dim_level_rec.ATTRIBUTE_CODE;
562 l_bis_dimension_level_rec.APPLICATION_ID := l_bis_dim_level_rec.APPLICATION_ID;
563 l_bis_dimension_level_rec.VIEW_OBJECT_NAME := l_bis_dim_level_rec.VIEW_OBJECT_NAME;
564 l_bis_dimension_level_rec.DEFAULT_VALUES_API := l_bis_dim_level_rec.DEFAULT_VALUES_API;
565 l_bis_dimension_level_rec.DEFAULT_SEARCH := l_bis_dim_level_rec.DEFAULT_SEARCH;
566 l_bis_dimension_level_rec.LONG_LOV := l_bis_dim_level_rec.LONG_LOV;
567 l_bis_dimension_level_rec.MASTER_LEVEL := l_bis_dim_level_rec.MASTER_LEVEL;
568 l_bis_dimension_level_rec.ENABLED := l_bis_dim_level_rec.ENABLED;
569 l_bis_dimension_level_rec.DRILL_TO_FORM_FUNCTION := l_bis_dim_level_rec.DRILL_TO_FORM_FUNCTION;
570 l_bis_dimension_level_rec.Hide := l_bis_dim_level_rec.Hide_In_Design;
571
572
573 l_bis_dimension_level_rec.Dimension_Level_Short_Name := p_Dim_Obj_Short_Name;
574 l_bis_dimension_level_rec.Dimension_ID := l_Bis_Group_ID;
575 l_bis_dimension_level_rec.Dimension_Short_Name := l_Dim_Short_Name;
576
577 l_bis_dimension_level_rec.Primary_Dim := FND_API.G_TRUE;
578
579 BIS_DIMENSION_LEVEL_PUB.Update_Dimension_Level
580 ( p_api_version => 1.0
581 , p_commit => FND_API.G_FALSE
582 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
583 , p_Dimension_Level_Rec => l_bis_dimension_level_rec
584 , x_return_status => x_return_status
585 , x_error_Tbl => l_error_tbl
586 );
587 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
588 IF (l_error_tbl.COUNT > 0) THEN
589 x_msg_data := l_error_tbl(l_error_tbl.COUNT).Error_Description;
590 IF(INSTR(x_msg_data, ' ') = 0 ) THEN
591 FND_MESSAGE.SET_NAME('BIS',x_msg_data);
592 FND_MSG_PUB.ADD;
593 x_msg_data := NULL;
594 END IF;
595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
596 END IF;
597 END IF;
598 END IF;
599 IF (p_commit = FND_API.G_TRUE) THEN
600 COMMIT;
601
602 END IF;
603 x_return_status := FND_API.G_RET_STS_SUCCESS;
604
605 EXCEPTION
606 WHEN OTHERS THEN
607 ROLLBACK TO SyncPMFBSCDimsInPMD;
608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
609 IF (x_msg_data IS NULL) THEN
610 x_msg_data := SQLERRM||' -> BSC_BIS_DIMENSION_PUB.Sync_Dimensions_In_Bis ';
611 END IF;
612
613 END Sync_Dimensions_In_Bis;
614 /*********************************************************************************/
615 PROCEDURE Delete_Dim_Objs_In_DSet
616 ( p_MTab_Tbl IN BSC_BIS_DIMENSION_PUB.KPI_Dim_Set_Table_Type
617 , x_return_status OUT NOCOPY VARCHAR2
618 , x_msg_count OUT NOCOPY NUMBER
619 , x_msg_data OUT NOCOPY VARCHAR2
620 ) IS
621 BEGIN
622 FOR i IN 0..(p_MTab_Tbl.COUNT-1) LOOP
623 BSC_BIS_KPI_MEAS_PUB.Delete_Dim_Objs_In_DSet
624 ( p_commit => FND_API.G_FALSE
625 , p_kpi_id => p_MTab_Tbl(i).p_kpi_id
626 , p_dim_set_id => p_MTab_Tbl(i).p_dim_set_id
627 , x_return_status => x_return_status
628 , x_msg_count => x_msg_count
629 , x_msg_data => x_msg_data
630 );
631 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
632 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
633 END IF;
634 END LOOP;
635 END Delete_Dim_Objs_In_DSet;
636 /*********************************************************************************/
637 PROCEDURE Create_Dim_Objs_In_DSet
638 ( p_MTab_Tbl IN BSC_BIS_DIMENSION_PUB.KPI_Dim_Set_Table_Type
639 , p_kpi_flag_change IN VARCHAR2 := NULL
640 , p_delete IN BOOLEAN := FALSE
641 , x_return_status OUT NOCOPY VARCHAR2
642 , x_msg_count OUT NOCOPY NUMBER
643 , x_msg_data OUT NOCOPY VARCHAR2
644 ) IS
645 BEGIN
646 FOR i IN 0..(p_MTab_Tbl.COUNT-1) LOOP
647 BSC_BIS_KPI_MEAS_PUB.Create_Dim_Objs_In_DSet
648 ( p_commit => FND_API.G_FALSE
649 , p_kpi_id => p_MTab_Tbl(i).p_kpi_id
650 , p_dim_set_id => p_MTab_Tbl(i).p_dim_set_id
651 , p_kpi_flag_change => p_kpi_flag_change
652 , p_delete => p_delete
653 , x_return_status => x_return_status
654 , x_msg_count => x_msg_count
655 , x_msg_data => x_msg_data
656 );
657 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
659 END IF;
660 END LOOP;
661 END Create_Dim_Objs_In_DSet;
662
663 /**********************************************************************************
664
665 The following overloaded API take dimension object old default value and new default
666 value and sets the prototype flag of the corresponding objectives accordingly
667
668 ***********************************************************************************/
669
670 PROCEDURE Create_Dim_Objs_In_DSet
671 ( p_MTab_Tbl IN BSC_BIS_DIMENSION_PUB.KPI_Dim_Set_Table_Type
672 , p_delete IN BOOLEAN := FALSE
673 , p_old_default IN VARCHAR2
674 , p_new_default IN VARCHAR2
675 , p_dim_obj_short_name IN VARCHAR2
676 , p_dim_obj_objs_tbl IN OUT NOCOPY BSC_BIS_DIMENSION_PUB.dimobj_obj_kpis_tbl_type
677 , x_return_status OUT NOCOPY VARCHAR2
678 , x_msg_count OUT NOCOPY NUMBER
679 , x_msg_data OUT NOCOPY VARCHAR2
680 ) IS
681 l_kpi_flag_change VARCHAR2(1);
682
683 BEGIN
684
685 FOR i IN 0..(p_MTab_Tbl.COUNT-1) LOOP
686 l_kpi_flag_change := NULL;
687
688 l_kpi_flag_change := get_kpi_flag_change(
689 p_old_default => p_old_default,
690 p_new_default => p_new_default,
691 p_indicator => p_MTab_Tbl(i).p_kpi_id,
692 p_dim_obj_objs_tbl => p_dim_obj_objs_tbl
693 );
694
695 BSC_BIS_KPI_MEAS_PUB.Create_Dim_Objs_In_DSet
696 ( p_commit => FND_API.G_FALSE
697 , p_kpi_id => p_MTab_Tbl(i).p_kpi_id
698 , p_dim_set_id => p_MTab_Tbl(i).p_dim_set_id
699 , p_kpi_flag_change => l_kpi_flag_change
700 , p_delete => p_delete
701 , x_return_status => x_return_status
702 , x_msg_count => x_msg_count
703 , x_msg_data => x_msg_data
704 );
705
706 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
707 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
708 END IF;
709
710 END LOOP;
711 END Create_Dim_Objs_In_DSet;
712
713 /*********************************************************************************/
714 PROCEDURE Store_Dim_Set_Records
715 ( p_dim_group_id IN NUMBER
716 , p_dim_short_name IN VARCHAR2
717 , x_MTab_Tbl IN OUT NOCOPY BSC_BIS_DIMENSION_PUB.KPI_Dim_Set_Table_Type
718 ) IS
719 CURSOR c_dim_set_kpi IS
720 SELECT DISTINCT A.indicator INDICATOR
721 , A.dim_set_id DIM_SET_ID
722 , A.Dim_Group_Index
723 FROM BSC_KPI_DIM_GROUPS A
724 , BSC_KPIS_B B
725 WHERE A.INDICATOR = B.INDICATOR
726 AND B.share_flag <> 2
727 AND A.dim_group_id = p_dim_group_id
728 ORDER BY A.Dim_Group_Index;
729
730 l_count NUMBER;
731 BEGIN
732 l_count := 0;
733 FOR cd IN c_dim_set_kpi LOOP
734 x_MTab_Tbl(l_count).p_kpi_id := cd.Indicator;
735 x_MTab_Tbl(l_count).p_dim_set_id := cd.Dim_Set_Id;
736 x_MTab_Tbl(l_count).p_short_name := p_dim_short_name;
737 l_count := l_count + 1;
738 END LOOP;
739 END Store_Dim_Set_Records;
740 /*********************************************************************************/
741
742 PROCEDURE store_dim_obj_objectives (
743 p_dim_obj_short_name IN VARCHAR2,
744 x_dim_obj_objs_tbl OUT NOCOPY BSC_BIS_DIMENSION_PUB.dimobj_obj_kpis_tbl_type
745 ) IS
746 l_count NUMBER;
747 CURSOR c_dim_obj_objectives IS
748 SELECT a.indicator indicator, a.kpi_measure_id
749 FROM BSC_DB_DATASET_DIM_SETS_V a, bsc_kpi_dim_levels_vl b
750 WHERE a.indicator = b.indicator
751 AND a.dim_set_id = b.dim_set_id
752 AND b.level_shortname = p_dim_obj_short_name;
753
754 BEGIN
755 l_count := 0;
756 FOR cd IN c_dim_obj_objectives LOOP
757 x_dim_obj_objs_tbl(l_count).p_indicator := cd.indicator;
758 x_dim_obj_objs_tbl(l_count).p_kpi_measure_id := cd.kpi_measure_id;
759 x_dim_obj_objs_tbl(l_count).p_short_name := p_dim_obj_short_name;
760 l_count := l_count + 1;
761 END LOOP;
762 END store_dim_obj_objectives;
763
764 /********************************************************************************/
765
766 FUNCTION Is_More
767 ( p_dim_obj_short_names IN OUT NOCOPY VARCHAR2
768 , p_dim_obj_name OUT NOCOPY VARCHAR2
769 ) RETURN BOOLEAN;
770
771
772
773 /*******************************************************************************
774 FUNCTION TO CHECK IF VALID ALPHA NUMERIC CHARACTER
775 ********************************************************************************/
776 FUNCTION is_Valid_AlphaNum
777 (
778 p_SQL_Ident IN VARCHAR2
779 ) RETURN BOOLEAN
780 IS
781 l_SQL_Ident VARCHAR2(30);
782 BEGIN
783 IF (p_SQL_Ident IS NULL) THEN
784 RETURN FALSE;
785 END IF;
786 l_SQL_Ident := UPPER(p_SQL_Ident);
787 IF (REPLACE(TRANSLATE(l_SQL_Ident, '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ_',
788 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'), 'X', '') IS NOT NULL) THEN
789 RETURN FALSE;
790 END IF;
791 RETURN TRUE;
792 END is_Valid_AlphaNum;
793
794 /*********************************************************************************
795 CREATE DIMENSION
796 *********************************************************************************/
797 PROCEDURE Create_Dimension
798 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
799 , p_dim_short_name IN VARCHAR2
800 , p_display_name IN VARCHAR2
801 , p_description IN VARCHAR2
802 , p_dim_obj_short_names IN VARCHAR2
803 , p_application_id IN NUMBER
804 , p_create_view IN NUMBER := 0
805 , p_hide IN VARCHAR2 := FND_API.G_FALSE
806 , x_return_status OUT NOCOPY VARCHAR2
807 , x_msg_count OUT NOCOPY NUMBER
808 , x_msg_data OUT NOCOPY VARCHAR2
809 ) IS
810 BEGIN
811 SAVEPOINT CreateBSCDimensionPMD;
812
813 FND_MSG_PUB.INITIALIZE;
814 x_return_status := FND_API.G_RET_STS_SUCCESS;
815
816 Create_Dimension(
817 p_commit => p_commit
818 , p_dim_short_name => p_dim_short_name
819 , p_display_name => p_display_name
820 , p_description => p_description
821 , p_dim_obj_short_names => p_dim_obj_short_names
822 , p_application_id => p_application_id
823 , p_create_view => p_create_view
824 , p_hide => p_hide
825 , p_is_default_short_name => 'F'
826 , x_return_status => x_return_status
827 , x_msg_count => x_msg_count
828 , x_msg_data => x_msg_data
829 );
830 EXCEPTION
831 WHEN FND_API.G_EXC_ERROR THEN
832 ROLLBACK TO CreateBSCDimensionPMD;
833 IF (x_msg_data IS NULL) THEN
834 FND_MSG_PUB.Count_And_Get
835 ( p_encoded => FND_API.G_FALSE
836 , p_count => x_msg_count
837 , p_data => x_msg_data
838 );
839 END IF;
840
841 x_return_status := FND_API.G_RET_STS_ERROR;
842 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
843 ROLLBACK TO CreateBSCDimensionPMD;
844 IF (x_msg_data IS NULL) THEN
845 FND_MSG_PUB.Count_And_Get
846 ( p_encoded => FND_API.G_FALSE
847 , p_count => x_msg_count
848 , p_data => x_msg_data
849 );
850 END IF;
851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
852
853 WHEN NO_DATA_FOUND THEN
854 ROLLBACK TO CreateBSCDimensionPMD;
855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
856 IF (x_msg_data IS NOT NULL) THEN
857 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Create_Dimension ';
858 ELSE
859 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Create_Dimension ';
860 END IF;
861
862 WHEN OTHERS THEN
863 ROLLBACK TO CreateBSCDimensionPMD;
864 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
865 IF (x_msg_data IS NOT NULL) THEN
866 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Create_Dimension ';
867 ELSE
868 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Create_Dimension ';
869 END IF;
870
871 END Create_Dimension;
872
873 PROCEDURE Create_Dimension
874 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
875 , p_dim_short_name IN VARCHAR2
876 , p_display_name IN VARCHAR2
877 , p_description IN VARCHAR2
878 , p_dim_obj_short_names IN VARCHAR2
879 , p_application_id IN NUMBER
880 , p_create_view IN NUMBER := 0
881 , p_hide IN VARCHAR2 := FND_API.G_FALSE
882 , p_is_default_short_name IN VARCHAR2
883 , p_Restrict_Dim_Validate IN VARCHAR2 := NULL
884 , x_return_status OUT NOCOPY VARCHAR2
885 , x_msg_count OUT NOCOPY NUMBER
886 , x_msg_data OUT NOCOPY VARCHAR2
887 ) IS
888 l_bis_dimension_rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
889 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
890 l_bsc_dimension_rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
891
892 l_dim_short_name BSC_SYS_DIM_GROUPS_TL.Short_Name%TYPE;
893 l_application_id BIS_DIMENSIONS.Application_Id%TYPE;
894 l_count NUMBER;
895
896 l_alias VARCHAR2(4);
897 l_flag BOOLEAN;
898 l_temp_var BSC_SYS_DIM_GROUPS_TL.Short_Name%TYPE;
899 -- Start Granular Locking added by Aditya
900 l_Dim_Obj_Tab BSC_BIS_LOCKS_PUB.t_numberTable;
901 l_dim_obj_names VARCHAR2(32000);
902
903 l_dim_obj_name BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE;
904 l_index NUMBER := 0;
905 -- End Granular Locking added by Aditya
906
907 l_pmf_disp_name VARCHAR2(255); -- DispName
908 l_mix_type_dim BOOLEAN;
909 l_dim_type VARCHAR2(10);
910
911 BEGIN
912 SAVEPOINT CreateBSCDimensionPMD;
913
914
915 FND_MSG_PUB.INITIALIZE;
916
917 IF((p_dim_short_name IS NOT NULL) AND
918 (p_is_default_short_name <> 'T')) THEN
919 l_dim_short_name := p_dim_short_name;
920 l_application_id := p_application_id;
921 ELSE
922 SELECT NVL(MAX(dim_group_id) + 1, 0)
923 INTO l_count
924 FROM BSC_SYS_DIM_GROUPS_TL;
925 IF (p_dim_short_name IS NULL) THEN
926 l_dim_short_name := c_BSC_DIM ||l_count;
927 ELSE
928 l_dim_short_name := p_dim_short_name;
929 END IF;
930 l_flag := TRUE;
931 l_alias := NULL;
932 l_temp_var := l_dim_short_name;
933 WHILE (l_flag) LOOP
934 SELECT COUNT(1) INTO l_count
935 FROM (SELECT COUNT(1) rec_count
936 FROM BSC_SYS_DIM_GROUPS_VL
937 WHERE UPPER(Short_Name) = UPPER(l_temp_var)
938 UNION
939 SELECT COUNT(1) rec_count
940 FROM BIS_DIMENSIONS_VL
941 WHERE UPPER(Short_Name) = UPPER(l_temp_var))
942 WHERE rec_count > 0;
943 IF (l_count = 0) THEN
944 l_flag := FALSE;
945 l_dim_short_name := l_temp_var;
946 END IF;
947 l_alias := BSC_BIS_DIMENSION_PUB.get_Next_Alias(l_alias);
948 l_temp_var := l_dim_short_name||l_alias;
949 END LOOP;
950 IF(p_application_id = -1 OR p_application_id IS NULL) THEN
951 l_application_id := 271;
952 ELSE
953 l_application_id := p_application_id;
954 END IF;
955
956 END IF;
957
958 IF (l_dim_short_name IS NULL) THEN
959 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
960 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_SHORT_NAME'));
961 FND_MSG_PUB.ADD;
962 RAISE FND_API.G_EXC_ERROR;
963 END IF;
964 IF (NOT is_Valid_AlphaNum(l_dim_short_name)) THEN
965 FND_MESSAGE.SET_NAME('BSC','BSC_ALPHA_NUM_REQUIRED');
966 FND_MESSAGE.SET_TOKEN('VALUE', l_dim_short_name);
967 FND_MESSAGE.SET_TOKEN('NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_SHORT_NAME'));
968 FND_MSG_PUB.ADD;
969 RAISE FND_API.G_EXC_ERROR;
970 END IF;
971 IF (p_display_name IS NULL) THEN
972 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
973 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DISPLAY_NAME'));
974 FND_MSG_PUB.ADD;
975 RAISE FND_API.G_EXC_ERROR;
976 END IF;
977 IF (l_application_id IS NULL) THEN
978 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
979 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'APPLICATION_ID'));
980 FND_MSG_PUB.ADD;
981 RAISE FND_API.G_EXC_ERROR;
982 END IF;
983
984 SELECT COUNT(1) INTO l_count
985 FROM BSC_SYS_DIM_GROUPS_TL
986 WHERE UPPER(short_name) = UPPER(l_dim_short_name);
987 IF (l_count <> 0) THEN
988 FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
989 FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_SHORT_NAME'));
990 FND_MESSAGE.SET_TOKEN('NAME_VALUE', l_dim_short_name);
991 FND_MSG_PUB.ADD;
992 RAISE FND_API.G_EXC_ERROR;
993 END IF;
994
995 SELECT COUNT(1) INTO l_count
996 FROM BIS_DIMENSIONS_VL
997 WHERE UPPER(short_name) = UPPER(l_dim_short_name);
998 IF (l_count <> 0) THEN
999 FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
1000 FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_SHORT_NAME'));
1001 FND_MESSAGE.SET_TOKEN('NAME_VALUE', l_dim_short_name);
1002 FND_MSG_PUB.ADD;
1003 RAISE FND_API.G_EXC_ERROR;
1004 END IF;
1005
1006
1007 l_mix_type_dim := FALSE;
1008 IF(p_dim_obj_short_names IS NOT NULL) THEN
1009 l_mix_type_dim := check_sametype_dimobjs
1010 ( p_dim_name => p_display_name
1011 , p_dim_short_name => p_dim_short_name
1012 , p_dim_short_names => p_dim_obj_short_names
1013 , p_Restrict_Dim_Validate => p_Restrict_Dim_Validate
1014 , x_dim_type => l_dim_type
1015 , x_return_status => x_return_status
1016 , x_msg_count => x_msg_count
1017 , x_msg_data => x_msg_data
1018 );
1019 IF (l_mix_type_dim) THEN
1020 RAISE FND_API.G_EXC_ERROR;
1021 END IF;
1022 END IF;
1023
1024 l_pmf_disp_name := p_display_name;
1025
1026 -- Enh#4172034: Validations
1027 IF ((l_dim_type IS NULL) OR (l_dim_type = 'BSC')) THEN
1028
1029 SELECT COUNT(1) INTO l_count
1030 FROM BIS_DIMENSIONS_VL
1031 WHERE UPPER(name) = UPPER(p_display_name);
1032
1033 WHILE(l_count > 0) LOOP
1034 l_pmf_disp_name := bsc_utility.get_Next_DispName(l_pmf_disp_name);
1035
1036 SELECT COUNT(1) INTO l_count
1037 FROM BIS_DIMENSIONS_VL
1038 WHERE UPPER(name) = UPPER(l_pmf_disp_name);
1039
1040 END LOOP;
1041
1042 ELSE
1043
1044 SELECT COUNT(1) INTO l_count
1045 FROM BSC_SYS_DIM_GROUPS_VL
1046 WHERE UPPER(name) = UPPER(p_display_name);
1047 IF (l_count <> 0) THEN
1048 FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
1049 FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DISPLAY_NAME'));
1050 FND_MESSAGE.SET_TOKEN('NAME_VALUE', p_display_name);
1051 FND_MSG_PUB.ADD;
1052 RAISE FND_API.G_EXC_ERROR;
1053 END IF;
1054
1055 SELECT COUNT(1) INTO l_count
1056 FROM BIS_DIMENSIONS_VL
1057 WHERE UPPER(name) = UPPER(p_display_name);
1058 IF (l_count <> 0) THEN
1059 FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
1060 FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DISPLAY_NAME'));
1061 FND_MESSAGE.SET_TOKEN('NAME_VALUE', p_display_name);
1062 FND_MSG_PUB.ADD;
1063 RAISE FND_API.G_EXC_ERROR;
1064 END IF;
1065
1066 END IF;
1067
1068 --assign values to bsc records
1069 --l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value( 'BSC_SYS_DIM_GROUPS_TL', 'DIM_GROUP_ID');
1070 l_bsc_dimension_rec.Bsc_Dim_Level_Group_Name := p_display_name;
1071 l_bsc_dimension_rec.Bsc_Dim_Level_Group_short_name := l_dim_short_name;
1072 l_bsc_dimension_rec.Bsc_Dim_Level_Index := 1;
1073 l_bsc_dimension_rec.Bsc_Group_Level_Comp_Flag := -1;
1074 l_bsc_dimension_rec.Bsc_Group_Level_Default_Value := 'T';
1075 l_bsc_dimension_rec.Bsc_Group_Level_Default_Type := 0;
1076 l_bsc_dimension_rec.Bsc_Group_Level_Filter_Col := NULL;
1077 l_bsc_dimension_rec.Bsc_Group_Level_Filter_Value := 0;
1078 l_bsc_dimension_rec.Bsc_Group_Level_No_Items := 0;
1079 l_bsc_dimension_rec.Bsc_Group_Level_Parent_In_Tot := 2;
1080 l_bsc_dimension_rec.Bsc_Group_Level_Total_Flag := -1;
1081 l_bsc_dimension_rec.Bsc_Language := NULL;
1082 l_bsc_dimension_rec.Bsc_Level_Id := NULL;
1083 l_bsc_dimension_rec.Bsc_Source_Language := NULL;
1084 -- Start Granular Locking
1085 l_dim_obj_names := p_dim_obj_short_names;
1086 IF (p_dim_obj_short_names IS NOT NULL) THEN
1087 l_dim_obj_names := p_dim_obj_short_names ;
1088 WHILE (is_more( p_dim_obj_short_names => l_dim_obj_names
1089 , p_dim_obj_name => l_dim_obj_name)
1090 ) LOOP
1091 l_Dim_Obj_Tab(l_index) := NVL(BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Id(l_dim_obj_name), -1);
1092 l_index := l_index + 1;
1093 END LOOP;
1094 -- Lock all the Dimension Objects to be assigned to the Dimension
1095 BSC_BIS_LOCKS_PUB.Lock_Create_Dimension
1096 ( p_selected_dim_objets => l_Dim_Obj_Tab
1097 , x_return_status => x_return_status
1098 , x_msg_count => x_msg_count
1099 , x_msg_data => x_msg_data
1100 );
1101 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1102 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1103 END IF;
1104 END IF;
1105 -- End Granular Locking
1106 BSC_DIMENSION_GROUPS_PUB.Create_Dimension_Group
1107 ( p_commit => FND_API.G_FALSE
1108 , p_Dim_Grp_Rec => l_bsc_dimension_rec
1109 , p_create_Dim_Levels => FALSE
1110 , x_return_status => x_return_status
1111 , x_msg_count => x_msg_count
1112 , x_msg_data => x_msg_data
1113 );
1114 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1116 END IF;
1117 --assign values to bis records -This is not required
1118 SELECT dim_group_id
1119 INTO l_bis_dimension_rec.Dim_Grp_Id
1120 FROM BSC_SYS_DIM_GROUPS_VL
1121 WHERE Short_Name = l_bsc_dimension_rec.Bsc_Dim_Level_Group_short_name;
1122
1123 l_bis_dimension_rec.Dimension_Short_Name := l_dim_short_name;
1124 l_bis_dimension_rec.Dimension_Name := l_pmf_disp_name;
1125 l_bis_dimension_rec.Description := p_description;
1126 l_bis_dimension_rec.Application_ID := l_application_id;
1127 l_bis_dimension_rec.Hide := p_hide;
1128 BIS_DIMENSION_PUB.Create_Dimension
1129 ( p_api_version => 1.0
1130 , p_commit => FND_API.G_FALSE
1131 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1132 , p_Dimension_Rec => l_bis_dimension_rec
1133 , x_return_status => x_return_status
1134 , x_error_Tbl => l_error_tbl
1135 );
1136 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1137 IF (l_error_tbl.COUNT > 0) THEN
1138 x_msg_data := l_error_tbl(l_error_tbl.COUNT).Error_Description;
1139 IF(INSTR(x_msg_data, ' ') = 0 ) THEN
1140 FND_MESSAGE.SET_NAME('BIS',x_msg_data);
1141 FND_MSG_PUB.ADD;
1142 x_msg_data := NULL;
1143 END IF;
1144 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1145 END IF;
1146
1147 END IF;
1148 IF (p_dim_obj_short_names IS NOT NULL) THEN
1149 BSC_BIS_DIMENSION_PUB.Assign_Dimension_Objects
1150 ( p_commit => FND_API.G_FALSE
1151 , p_dim_short_name => l_dim_short_name
1152 , p_dim_obj_short_names => p_dim_obj_short_names
1153 , p_create_view => p_create_view
1154 , p_Restrict_Dim_Validate => p_Restrict_Dim_Validate
1155 , x_return_status => x_return_status
1156 , x_msg_count => x_msg_count
1157 , x_msg_data => x_msg_data
1158 );
1159 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1160 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1161 END IF;
1162 END IF;
1163
1164 IF (p_commit = FND_API.G_TRUE) THEN
1165 COMMIT;
1166
1167 END IF;
1168 x_return_status := FND_API.G_RET_STS_SUCCESS;
1169
1170 EXCEPTION
1171 WHEN FND_API.G_EXC_ERROR THEN
1172 ROLLBACK TO CreateBSCDimensionPMD;
1173 IF (x_msg_data IS NULL) THEN
1174 FND_MSG_PUB.Count_And_Get
1175 ( p_encoded => FND_API.G_FALSE
1176 , p_count => x_msg_count
1177 , p_data => x_msg_data
1178 );
1179 END IF;
1180
1181 x_return_status := FND_API.G_RET_STS_ERROR;
1182 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1183 ROLLBACK TO CreateBSCDimensionPMD;
1184 IF (x_msg_data IS NULL) THEN
1185 FND_MSG_PUB.Count_And_Get
1186 ( p_encoded => FND_API.G_FALSE
1187 , p_count => x_msg_count
1188 , p_data => x_msg_data
1189 );
1190 END IF;
1191 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1192
1193 WHEN NO_DATA_FOUND THEN
1194 ROLLBACK TO CreateBSCDimensionPMD;
1195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1196 IF (x_msg_data IS NOT NULL) THEN
1197 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Create_Dimension ';
1198 ELSE
1199 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Create_Dimension ';
1200 END IF;
1201
1202 WHEN OTHERS THEN
1203 ROLLBACK TO CreateBSCDimensionPMD;
1204 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1205 IF (x_msg_data IS NOT NULL) THEN
1206 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Create_Dimension ';
1207 ELSE
1208 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Create_Dimension ';
1209 END IF;
1210
1211 END Create_Dimension;
1212 /*********************************************************************************
1213 ASSIGN DIMENSION OBJECTS TO DIMENSION
1214 *********************************************************************************/
1215 PROCEDURE Assign_Dimension_Objects
1216 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
1217 , p_dim_short_name IN VARCHAR2
1218 , p_dim_obj_short_names IN VARCHAR2
1219 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
1220 , p_create_view IN NUMBER := 0
1221 , p_Restrict_Dim_Validate IN VARCHAR2 := NULL
1222 , x_return_status OUT NOCOPY VARCHAR2
1223 , x_msg_count OUT NOCOPY NUMBER
1224 , x_msg_data OUT NOCOPY VARCHAR2
1225 ) IS
1226 l_dim_obj_names VARCHAR2(32000);
1227 l_dim_obj_name BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE;
1228 l_Source_Type BSC_SYS_DIM_LEVELS_B.Source%TYPE;
1229 l_All_Flag BSC_SYS_DIM_LEVELS_BY_GROUP.Total_Flag%TYPE;
1230
1231 l_assigns VARCHAR2(32000);
1232 l_assign BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE;
1233 l_MTab_Tbl BSC_BIS_DIMENSION_PUB.KPI_Dim_Set_Table_Type;
1234 l_MTab_DimRels1 BSC_BIS_DIMENSION_PUB.Dim_Obj_Table_Type;
1235 l_MTab_DimRels2 BSC_BIS_DIMENSION_PUB.Dim_Obj_Table_Type;
1236
1237 l_dim_group_id BSC_SYS_DIM_GROUPS_VL.Dim_Group_Id%TYPE;
1238 l_count NUMBER;
1239 l_count1 NUMBER;
1240 l_flag BOOLEAN;
1241 l_bsc_dimension_rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
1242 l_dim_level_id BSC_SYS_DIM_LEVELS_B.Dim_Level_ID%TYPE;
1243
1244 l_tab_index NUMBER;
1245 l_original_tab_index NUMBER;
1246 l_struct_change BOOLEAN := FALSE;
1247
1248 CURSOR c_dim_obj_index IS
1249 SELECT B.Short_Name
1250 , B.Dim_Level_ID
1251 FROM BSC_SYS_DIM_LEVELS_B B
1252 , BSC_SYS_DIM_LEVELS_BY_GROUP A
1253 WHERE A.dim_group_id =
1254 ( SELECT dim_group_id
1255 FROM BSC_SYS_DIM_GROUPS_VL
1256 WHERE Short_Name = p_dim_short_name
1257 )
1258 AND A.Dim_Level_Id = B.Dim_Level_Id
1259 ORDER BY A.Dim_Level_Index;
1260
1261 CURSOR cr_bsc_dim_id IS
1262 SELECT dim_group_id
1263 FROM BSC_SYS_DIM_GROUPS_VL
1264 WHERE short_name = p_dim_short_name;
1265
1266 CURSOR cr_bsc_dim_obj_id IS
1267 SELECT Dim_Level_Id
1268 , Source
1269 FROM BSC_SYS_DIM_LEVELS_B
1270 WHERE Short_Name = l_dim_obj_name;
1271 BEGIN
1272
1273 FND_MSG_PUB.Initialize;
1274 x_return_status := FND_API.G_RET_STS_SUCCESS;
1275 IF (p_dim_short_name IS NULL) THEN
1276 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
1277 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_SHORT_NAME'), TRUE);
1278 FND_MSG_PUB.ADD;
1279 RAISE FND_API.G_EXC_ERROR;
1280 END IF;
1281 IF (p_dim_obj_short_names IS NOT NULL) THEN
1282 IF (cr_bsc_dim_id%ISOPEN) THEN
1283 CLOSE cr_bsc_dim_id;
1284 END IF;
1285 OPEN cr_bsc_dim_id;
1286 FETCH cr_bsc_dim_id
1287 INTO l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id;
1288 CLOSE cr_bsc_dim_id;
1289 IF (l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id IS NULL) THEN
1290 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
1291 FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_DIMENSION'), TRUE);
1292 FND_MSG_PUB.ADD;
1293 RAISE FND_API.G_EXC_ERROR;
1294 END IF;
1295 IF (p_Restrict_Dim_Validate IS NOT NULL) THEN
1296 Restrict_Internal_Dim_Objs
1297 ( p_dim_short_name => p_dim_short_name
1298 , p_assign_dim_obj_names => p_dim_obj_short_names
1299 , p_unassign_dim_obj_names => NULL
1300 , x_return_status => x_return_status
1301 , x_msg_count => x_msg_count
1302 , x_msg_data => x_msg_data
1303 );
1304 END IF;
1305 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1306 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1307 END IF;
1308 BSC_BIS_DIMENSION_PUB.Store_Dim_Set_Records
1309 ( p_dim_group_id => l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id
1310 , p_dim_short_name => p_dim_short_name
1311 , x_MTab_Tbl => l_MTab_Tbl
1312 );
1313 BSC_BIS_DIMENSION_PUB.Delete_Dim_Objs_In_DSet
1314 ( p_MTab_Tbl => l_MTab_Tbl
1315 , x_return_status => x_return_status
1316 , x_msg_count => x_msg_count
1317 , x_msg_data => x_msg_data
1318 );
1319 l_dim_obj_names := p_dim_obj_short_names;
1320 WHILE (is_more(p_dim_obj_short_names => l_dim_obj_names
1321 , p_dim_obj_name => l_dim_obj_name)
1322 ) LOOP
1323 IF (cr_bsc_dim_obj_id%ISOPEN) THEN
1324 CLOSE cr_bsc_dim_obj_id;
1325 END IF;
1326 OPEN cr_bsc_dim_obj_id;
1327 FETCH cr_bsc_dim_obj_id
1328 INTO l_dim_level_id
1329 , l_Source_Type;
1330 CLOSE cr_bsc_dim_obj_id;
1331 IF (l_dim_level_id IS NULL) THEN
1332 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
1333 FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECT'), TRUE);
1334 FND_MSG_PUB.ADD;
1335 RAISE FND_API.G_EXC_ERROR;
1336 END IF;
1337
1338 IF (NOT BSC_BIS_DIMENSION_PUB.is_Relation_Exists(l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id, l_dim_level_id)) THEN
1339 l_struct_change := TRUE;
1340
1341 IF (l_Source_Type = 'BSC') THEN
1342 l_All_Flag := -1;
1343
1344 ELSE
1345 l_All_Flag := BSC_BIS_DIMENSION_PUB.Get_Primary_All_Flag(l_dim_obj_name);
1346
1347 END IF;
1348 BSC_BIS_DIMENSION_PUB.Assign_Dimension_Object
1349 ( p_commit => FND_API.G_FALSE
1350 , p_dim_short_name => p_dim_short_name
1351 , p_dim_obj_short_name => l_dim_obj_name
1352 , p_comp_flag => -111 -- this value is acting like a flag
1353 , p_no_items => 0
1354 , p_parent_in_tot => 2
1355 , p_total_flag => l_All_Flag
1356 , p_default_value => 'T'
1357 , p_time_stamp => p_time_stamp -- Granular Locking
1358 , p_create_view => p_create_view
1359 , x_return_status => x_return_status
1360 , x_msg_count => x_msg_count
1361 , x_msg_data => x_msg_data
1362 );
1363 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1365 END IF;
1366 END IF;
1367 END LOOP;
1368 --ordering logic of dimension level indexes starts here
1369 l_dim_group_id := l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id;
1370 l_count := 0;
1371 FOR cd IN c_dim_obj_index LOOP
1372 l_flag := TRUE;
1373 l_assigns := p_dim_obj_short_names;
1374 l_count1 := 0;
1375 WHILE (is_more( p_dim_obj_short_names => l_assigns
1376 , p_dim_obj_name => l_assign)
1377 ) LOOP
1378 IF(cd.Short_Name = l_assign) THEN
1379 l_flag := FALSE;
1380 l_MTab_DimRels2(l_count1).p_dim_obj_id := cd.Dim_Level_ID;
1381 EXIT;
1382 END IF;
1383 l_count1 := l_count1 + 1;
1384 END LOOP;
1385 IF(l_flag) THEN
1386 l_MTab_DimRels1(l_count).p_dim_obj_id := cd.Dim_Level_ID;
1387 l_count := l_count + 1;
1388 END IF;
1389 END LOOP;
1390 l_count := 0;
1391 FOR i IN 0..(l_MTab_DimRels1.COUNT-1) LOOP
1392 SELECT Dim_Level_Index INTO l_original_tab_index
1393 FROM BSC_SYS_DIM_LEVELS_BY_GROUP
1394 WHERE Dim_Group_Id = l_dim_group_id
1395 AND Dim_Level_ID = l_MTab_DimRels1(i).p_dim_obj_id;
1396
1397 IF (l_original_tab_index <> l_count) THEN
1398 l_bsc_dimension_rec.Bsc_Dim_Level_Index := l_count;
1399 l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id := l_dim_group_id;
1400 l_bsc_dimension_rec.Bsc_Level_Id := l_MTab_DimRels1(i).p_dim_obj_id;
1401
1402 BSC_DIMENSION_GROUPS_PUB.Update_Dim_Levels_In_Group
1403 ( p_commit => FND_API.G_FALSE
1404 , p_Dim_Grp_Rec => l_bsc_dimension_rec
1405 , x_return_status => x_return_status
1406 , x_msg_count => x_msg_count
1407 , x_msg_data => x_msg_data
1408 );
1409 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1410 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1411 END IF;
1412 END IF;
1413 l_count := l_count + 1;
1414 END LOOP;
1415 l_count1 := l_MTab_DimRels2.COUNT -1;
1416 l_tab_index := 0;
1417 WHILE (l_tab_index <= l_count1) LOOP
1418 IF (l_MTab_DimRels2.EXISTS(l_tab_index)) THEN
1419 SELECT Dim_Level_Index
1420 INTO l_original_tab_index
1421 FROM BSC_SYS_DIM_LEVELS_BY_GROUP
1422 WHERE Dim_Group_Id = l_dim_group_id
1423 AND Dim_Level_ID = l_MTab_DimRels2(l_tab_index).p_dim_obj_id;
1424 IF (l_original_tab_index <> l_count) THEN
1425 l_bsc_dimension_rec.Bsc_Dim_Level_Index := l_count;
1426 l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id := l_dim_group_id;
1427 l_bsc_dimension_rec.Bsc_Level_Id := l_MTab_DimRels2(l_tab_index).p_dim_obj_id;
1428 l_struct_change := TRUE;
1429
1430 BSC_DIMENSION_GROUPS_PUB.Update_Dim_Levels_In_Group
1431 ( p_commit => FND_API.G_FALSE
1432 , p_Dim_Grp_Rec => l_bsc_dimension_rec
1433 , x_return_status => x_return_status
1434 , x_msg_count => x_msg_count
1435 , x_msg_data => x_msg_data
1436 );
1437 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1438 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1439 END IF;
1440 END IF;
1441 l_count := l_count + 1;
1442 END IF;
1443 l_tab_index := l_tab_index + 1;
1444 END LOOP;
1445 IF (l_struct_change) THEN
1446 BSC_BIS_DIMENSION_PUB.Create_Dim_Objs_In_DSet
1447 ( p_MTab_Tbl => l_MTab_Tbl
1448 , x_return_status => x_return_status
1449 , x_msg_count => x_msg_count
1450 , x_msg_data => x_msg_data
1451 );
1452 ELSE
1453 BSC_BIS_DIMENSION_PUB.Create_Dim_Objs_In_DSet
1454 ( p_MTab_Tbl => l_MTab_Tbl
1455 , p_kpi_flag_change => BSC_DESIGNER_PVT.G_ActionFlag.Normal
1456 , x_return_status => x_return_status
1457 , x_msg_count => x_msg_count
1458 , x_msg_data => x_msg_data
1459 );
1460 END IF;
1461 END IF;
1462 IF (p_commit = FND_API.G_TRUE) THEN
1463 COMMIT;
1464
1465 END IF;
1466 x_return_status := FND_API.G_RET_STS_SUCCESS;
1467
1468 EXCEPTION
1469 WHEN FND_API.G_EXC_ERROR THEN
1470 IF (x_msg_data IS NULL) THEN
1471 FND_MSG_PUB.Count_And_Get
1472 ( p_encoded => FND_API.G_FALSE
1473 , p_count => x_msg_count
1474 , p_data => x_msg_data
1475 );
1476 END IF;
1477
1478 x_return_status := FND_API.G_RET_STS_ERROR;
1479 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1480 IF (x_msg_data IS NULL) THEN
1481 FND_MSG_PUB.Count_And_Get
1482 ( p_encoded => FND_API.G_FALSE
1483 , p_count => x_msg_count
1484 , p_data => x_msg_data
1485 );
1486 END IF;
1487 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1488
1489 WHEN NO_DATA_FOUND THEN
1490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1491 IF (x_msg_data IS NOT NULL) THEN
1492 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Assign_Dimension_Objects ';
1493 ELSE
1494 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Assign_Dimension_Objects ';
1495 END IF;
1496
1497 WHEN OTHERS THEN
1498 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1499 IF (x_msg_data IS NOT NULL) THEN
1500 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Assign_Dimension_Objects ';
1501 ELSE
1502 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Assign_Dimension_Objects ';
1503 END IF;
1504
1505 END Assign_Dimension_Objects;
1506 /*********************************************************************************
1507 ASSIGN OR UPDATE A DIMENSION OBJECT TO DIMENSION
1508 *********************************************************************************/
1509 PROCEDURE Assign_Dimension_Object
1510 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
1511 , p_dim_short_name IN VARCHAR2
1512 , p_dim_obj_short_name IN VARCHAR2
1513 , p_comp_flag IN NUMBER
1514 , p_no_items IN NUMBER
1515 , p_parent_in_tot IN NUMBER
1516 , p_total_flag IN NUMBER
1517 , p_default_value IN VARCHAR2
1518 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
1519 , p_create_view IN NUMBER := 0
1520 , p_where_clause IN VARCHAR2 := NULL
1521 , x_return_status OUT NOCOPY VARCHAR2
1522 , x_msg_count OUT NOCOPY NUMBER
1523 , x_msg_data OUT NOCOPY VARCHAR2
1524 ) IS
1525 l_Dim_Grp_Id BSC_SYS_DIM_GROUPS_TL.Dim_Group_Id%TYPE;
1526 l_dim_lvl_name VARCHAR2(400);
1527 l_default VARCHAR2(3);
1528 l_MTab_Tbl BSC_BIS_DIMENSION_PUB.KPI_Dim_Set_Table_Type;
1529 l_dim_obj_objs_tbl BSC_BIS_DIMENSION_PUB.dimobj_obj_kpis_tbl_type;
1530 l_bsc_dimension_rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
1531 l_count NUMBER;
1532 l_comp_count NUMBER;
1533
1534 l_valid_Assign BOOLEAN := FALSE;
1535 l_do_not_cascade BOOLEAN := TRUE;
1536
1537 l_dim_obj_sht_name BSC_SYS_DIM_LEVELS_VL.Short_Name%TYPE;
1538 l_Dim_Obj_Source BSC_SYS_DIM_LEVELS_B.Source%TYPE;
1539 l_Dim_Obj_Name BSC_SYS_DIM_LEVELS_TL.Name%TYPE;
1540 l_Rolling_Period NUMBER;
1541
1542 CURSOR cr_bsc_dim_obj_id IS
1543 SELECT dim_level_id FROM BSC_SYS_DIM_LEVELS_B
1544 WHERE short_name = p_dim_obj_short_name;
1545
1546 CURSOR cr_bsc_dim_id IS
1547 SELECT dim_group_id FROM BSC_SYS_DIM_GROUPS_VL
1548 WHERE short_name = p_dim_short_name;
1549
1550 /* Fix for the bug 3129610 */
1551 CURSOR cr_bsc_dim_obj_count IS
1552 SELECT B.Default_Value
1553 , C.Short_Name
1554 , C.Source
1555 , C.Name
1556 FROM BSC_SYS_DIM_LEVELS_BY_GROUP B,
1557 BSC_SYS_DIM_GROUPS_VL V,
1558 BSC_SYS_DIM_LEVELS_VL C
1559 WHERE V.Dim_Group_Id = B.Dim_Group_Id
1560 AND B.Dim_Level_Id = C.Dim_Level_Id
1561 AND V.Short_Name = p_Dim_Short_Name;
1562
1563 CURSOR c_Defaut_Value IS
1564 SELECT Default_Value
1565 FROM BSC_SYS_DIM_LEVELS_BY_GROUP
1566 WHERE Dim_Group_Id = l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id;
1567
1568 l_kpi_measure_props_rec BSC_KPI_MEASURE_PROPS_PUB.kpi_measure_props_rec;
1569
1570 CURSOR c_kpi_meas_props IS
1571 SELECT a.indicator,
1572 a.kpi_measure_id
1573 FROM bsc_db_dataset_dim_sets_v a,
1574 bsc_kpi_dim_levels_vl b
1575 WHERE a.indicator =b.indicator
1576 AND a.dim_set_id =b.dim_set_id
1577 AND b.level_shortname =p_dim_obj_short_name;
1578
1579 BEGIN
1580 SAVEPOINT AssBSCDimObjectPMD;
1581
1582 FND_MSG_PUB.Initialize;
1583 x_return_status := FND_API.G_RET_STS_SUCCESS;
1584 IF (p_dim_short_name IS NULL) THEN
1585 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
1586 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_SHORT_NAME'), TRUE);
1587 FND_MSG_PUB.ADD;
1588 RAISE FND_API.G_EXC_ERROR;
1589 END IF;
1590 IF (cr_bsc_dim_id%ISOPEN) THEN
1591 CLOSE cr_bsc_dim_id;
1592 END IF;
1593 OPEN cr_bsc_dim_id;
1594 FETCH cr_bsc_dim_id
1595 INTO l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id;
1596 CLOSE cr_bsc_dim_id;
1597 IF (l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id IS NULL) THEN
1598 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
1599 FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_DIMENSION'), TRUE);
1600 FND_MSG_PUB.ADD;
1601 RAISE FND_API.G_EXC_ERROR;
1602 END IF;
1603
1604 -- Check if the number of dimension objects within the dimension are greater than 1.
1605 -- if it is one it means that it is the current dimension object which we are trying to update.
1606 -- if the count is greater than 1 then do validate that if any of the dimension objects with in the
1607 -- dimension have default_value set to 'C'. ie. compariosn mode. if yes than throw the exception
1608 -- that in a dimension there cannot be more than one dimension object with comparison as the
1609 -- default value
1610 SELECT COUNT(B.DEFAULT_VALUE)
1611 INTO l_count
1612 FROM BSC_SYS_DIM_LEVELS_BY_GROUP B
1613 WHERE B.DIM_GROUP_ID = l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id;
1614
1615 IF (l_count > 1) THEN
1616 l_comp_count := 0;
1617 FOR cd IN cr_bsc_dim_obj_count LOOP
1618 l_default := cd.Default_Value;
1619 l_dim_obj_sht_name := cd.Short_Name;
1620 l_Dim_Obj_Source := cd.Source;
1621 IF ((l_default = 'C') AND (l_dim_obj_sht_name <> p_dim_obj_short_name)) THEN
1622 l_Dim_Obj_Name := cd.Name;
1623 l_comp_count := l_comp_count + 1;
1624 EXIT;
1625 END IF;
1626 END LOOP;
1627 IF ((l_comp_count > 0) AND (p_default_value = 'C')) THEN
1628 IF (l_Dim_Obj_Source = 'PMF') THEN
1629 FND_MESSAGE.SET_NAME('BIS', 'BIS_ONE_RNKLVL_IN_DIMGRP');
1630 FND_MESSAGE.SET_TOKEN('BIS_DIM_OBJ', l_Dim_Obj_Name, TRUE);
1631 FND_MSG_PUB.ADD;
1632 RAISE FND_API.G_EXC_ERROR;
1633 ELSIF (l_Dim_Obj_Source = 'BSC') THEN
1634 FND_MESSAGE.SET_NAME('BSC', 'BSC_D_ONE_DIM_IN_COMPARISON');
1635 FND_MSG_PUB.ADD;
1636 RAISE FND_API.G_EXC_ERROR;
1637 END IF;
1638 END IF;
1639 END IF;
1640
1641 ----DEBUG('1..'||get_default_value());
1642
1643
1644 l_bsc_dimension_rec.Bsc_Dim_Level_Index := 1;
1645 IF ((p_comp_flag IS NULL) OR ((p_comp_flag <> 0) AND (p_comp_flag <> -1))) THEN
1646 l_bsc_dimension_rec.Bsc_Group_Level_Comp_Flag := -1;
1647 ELSE
1648 l_bsc_dimension_rec.Bsc_Group_Level_Comp_Flag := p_comp_flag;
1649 END IF;
1650
1651 IF ((p_default_value IS NULL) OR ((p_default_value <> 'C') AND (p_default_value <> 'T'))) THEN
1652 l_bsc_dimension_rec.Bsc_Group_Level_Default_Value := 'T';
1653 ELSE
1654 l_bsc_dimension_rec.Bsc_Group_Level_Default_Value := p_default_value;
1655 END IF;
1656
1657 l_bsc_dimension_rec.Bsc_Group_Level_Default_Type := 0;
1658 l_bsc_dimension_rec.Bsc_Group_Level_Filter_Col := NULL;
1659 l_bsc_dimension_rec.Bsc_Group_Level_Filter_Value := 0;
1660 l_bsc_dimension_rec.Bsc_Group_Level_Where_Clause := p_where_clause;
1661 IF ((p_no_items IS NULL) OR ((p_no_items <> 0) AND (p_no_items <> 1))) THEN
1662 l_bsc_dimension_rec.Bsc_Group_Level_No_Items := 0;
1663 ELSE
1664 l_bsc_dimension_rec.Bsc_Group_Level_No_Items := p_no_items;
1665 END IF;
1666
1667 IF ((p_parent_in_tot IS NULL) OR (p_parent_in_tot < 0) OR (p_parent_in_tot > 2)) THEN
1668 l_bsc_dimension_rec.Bsc_Group_Level_Parent_In_Tot := 2;
1669 ELSE
1670 l_bsc_dimension_rec.Bsc_Group_Level_Parent_In_Tot := p_parent_in_tot;
1671 END IF;
1672
1673 IF ((p_total_flag IS NULL) OR ((p_total_flag <> 0) AND (p_total_flag <> -1))) THEN
1674 l_bsc_dimension_rec.Bsc_Group_Level_Total_Flag := -1;
1675 ELSE
1676 l_bsc_dimension_rec.Bsc_Group_Level_Total_Flag := p_total_flag; -- BSC_SYS_DIM_LEVELS_BY_GROUP.TOTAL_FLAG, true
1677 END IF;
1678 IF (p_dim_obj_short_name IS NOT NULL) THEN
1679 IF (cr_bsc_dim_obj_id%ISOPEN) THEN
1680 CLOSE cr_bsc_dim_obj_id;
1681 END IF;
1682 OPEN cr_bsc_dim_obj_id;
1683 FETCH cr_bsc_dim_obj_id
1684 INTO l_bsc_dimension_rec.Bsc_Level_Id;
1685 CLOSE cr_bsc_dim_obj_id;
1686
1687 IF (l_bsc_dimension_rec.Bsc_Level_Id IS NULL) THEN
1688 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
1689 FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECT'), TRUE);
1690 FND_MSG_PUB.ADD;
1691 RAISE FND_API.G_EXC_ERROR;
1692 END IF;
1693 -- Can test with 'LEGAL ENTITY', 'EDW_HR_PERM_SPSR1_L9'
1694 l_Rolling_Period := BIS_UTILITIES_PVT.Is_Rolling_Period_Level(p_dim_obj_short_name);
1695 IF (p_create_view = 1 AND l_Rolling_Period = 0) THEN
1696 IF NOT (BSC_BIS_DIMENSION_PUB.Attmpt_Recr_View(
1697 p_dim_lvl_shrt_name => p_dim_obj_short_name
1698 , x_dim_lvl_name => l_dim_lvl_name)) THEN
1699 FND_MESSAGE.SET_NAME('BSC','BSC_UNAVAILABLE_LEVEL');
1700 FND_MESSAGE.SET_TOKEN('BSC_LEVEL', l_dim_lvl_name, TRUE);
1701 FND_MSG_PUB.ADD;
1702 RAISE FND_API.G_EXC_ERROR;
1703 END IF;
1704 END IF;
1705
1706 BSC_BIS_LOCKS_PUB.Lock_Update_Dim_Obj_In_Dim
1707 ( p_dim_object_id => l_bsc_dimension_rec.Bsc_Level_Id
1708 , p_dimension_id => l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id
1709 , p_time_stamp => p_time_stamp -- Granular Locking
1710 , x_return_status => x_return_status
1711 , x_msg_count => x_msg_count
1712 , x_msg_data => x_msg_data
1713 );
1714 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1715
1716 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1717 END IF;
1718
1719 ----DEBUG('2..'||get_default_value());
1720 -- END Granular Locking added by Aditya
1721 IF (p_comp_flag IS NOT NULL) AND (p_comp_flag = -111) THEN
1722 l_do_not_cascade := FALSE;
1723 END IF;
1724 IF (l_do_not_cascade) THEN
1725 BSC_BIS_DIMENSION_PUB.Store_Dim_Set_Records
1726 ( p_dim_group_id => l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id
1727 , p_dim_short_name => p_dim_short_name
1728 , x_MTab_Tbl => l_MTab_Tbl
1729 );
1730
1731 store_dim_obj_objectives
1732 ( p_dim_obj_short_name => p_dim_obj_short_name
1733 , x_dim_obj_objs_tbl => l_dim_obj_objs_tbl
1734 );
1735 BSC_BIS_DIMENSION_PUB.Delete_Dim_Objs_In_DSet
1736 ( p_MTab_Tbl => l_MTab_Tbl
1737 , x_return_status => x_return_status
1738 , x_msg_count => x_msg_count
1739 , x_msg_data => x_msg_data
1740 );
1741 END IF;
1742 IF (BSC_BIS_DIMENSION_PUB.is_Relation_Exists
1743 (l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id, l_bsc_dimension_rec.Bsc_Level_Id)) THEN
1744 l_bsc_dimension_rec.Bsc_Dim_Level_Index := NULL;
1745
1746 -- START Added by Aditya for Incremental Changes
1747 -- Create a Dynamic SQL to extract the current state of the Default values
1748 IF (c_Defaut_Value%ISOPEN) THEN
1749 CLOSE c_Defaut_Value;
1750 END IF;
1751 OPEN c_Defaut_Value;
1752 FETCH c_Defaut_Value
1753 INTO l_default;
1754 CLOSE c_Defaut_Value;
1755 -- END Added by Aditya for Incremental Changes
1756 BSC_DIMENSION_GROUPS_PUB.Update_Dim_Levels_In_Group
1757 ( p_commit => FND_API.G_FALSE
1758 , p_Dim_Grp_Rec => l_bsc_dimension_rec
1759 , x_return_status => x_return_status
1760 , x_msg_count => x_msg_count
1761 , x_msg_data => x_msg_data
1762 );
1763 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1764 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1765 END IF;
1766
1767 ------DEBUG('3..'||get_default_value());
1768 IF (l_do_not_cascade) THEN
1769 Create_Dim_Objs_In_DSet
1770 ( p_MTab_Tbl => l_MTab_Tbl
1771 , p_old_default => l_default
1772 , p_new_default => p_default_value
1773 , p_dim_obj_short_name => p_dim_obj_short_name
1774 , p_dim_obj_objs_tbl => l_dim_obj_objs_tbl
1775 , x_return_status => x_return_status
1776 , x_msg_count => x_msg_count
1777 , x_msg_data => x_msg_data
1778 );
1779 /*IF (p_default_value <> l_default) THEN
1780 BSC_BIS_DIMENSION_PUB.Create_Dim_Objs_In_DSet
1781 ( p_MTab_Tbl => l_MTab_Tbl
1782 , p_kpi_flag_change => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color
1783 , x_return_status => x_return_status
1784 , x_msg_count => x_msg_count
1785 , x_msg_data => x_msg_data
1786 );
1787
1788 ----DEBUG('4..'||get_default_value());
1789 ELSE
1790 BSC_BIS_DIMENSION_PUB.Create_Dim_Objs_In_DSet
1791 ( p_MTab_Tbl => l_MTab_Tbl
1792 , p_kpi_flag_change => BSC_DESIGNER_PVT.G_ActionFlag.Normal
1793 , x_return_status => x_return_status
1794 , x_msg_count => x_msg_count
1795 , x_msg_data => x_msg_data
1796 );
1797 ----DEBUG('5..'||get_default_value());
1798 END IF;*/
1799 END IF;
1800 ELSE
1801 --if Dimension is already assigned to "UNASSIGNED" Dimension
1802 l_Dim_Grp_Id := BSC_BIS_DIMENSION_PUB.Get_Bsc_Dimension_ID(BSC_BIS_DIMENSION_PUB.Unassigned_Dim);
1803 IF (BSC_BIS_DIMENSION_PUB.is_Relation_Exists(l_Dim_Grp_Id, l_bsc_dimension_rec.Bsc_Level_Id)) THEN
1804
1805 UPDATE BSC_SYS_DIM_LEVELS_BY_GROUP
1806 SET Dim_Group_ID = l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id
1807 WHERE Dim_Level_ID = l_bsc_dimension_rec.Bsc_Level_Id
1808 AND Dim_Group_ID = l_Dim_Grp_Id;
1809 ELSE
1810 -- END - Added by Aditya for Incremental Changes
1811 BSC_DIMENSION_GROUPS_PUB.Create_Dim_Levels_In_Group
1812 ( p_commit => FND_API.G_FALSE
1813 , p_Dim_Grp_Rec => l_bsc_dimension_rec
1814 , x_return_status => x_return_status
1815 , x_msg_count => x_msg_count
1816 , x_msg_data => x_msg_data
1817 );
1818
1819 ----DEBUG('7..'||get_default_value());
1820 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1821 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1822 END IF;
1823 IF (l_do_not_cascade) THEN
1824 BSC_BIS_DIMENSION_PUB.Create_Dim_Objs_In_DSet
1825 ( p_MTab_Tbl => l_MTab_Tbl
1826 , x_return_status => x_return_status
1827 , x_msg_count => x_msg_count
1828 , x_msg_data => x_msg_data
1829 );
1830 ----DEBUG('8..'||get_default_value());
1831 END IF;
1832 END IF;
1833 --sync up with BIS Dimensions
1834 BSC_BIS_DIMENSION_PUB.Sync_Dimensions_In_Bis
1835 ( p_commit => FND_API.G_FALSE
1836 , p_Dim_Obj_Short_Name => p_dim_obj_short_name
1837 , p_Sync_Flag => TRUE
1838 , x_return_status => x_return_status
1839 , x_msg_count => x_msg_count
1840 , x_msg_data => x_msg_data
1841 );
1842 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1843 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1844 END IF;
1845
1846 ----DEBUG('9..'||get_default_value());
1847 END IF;
1848 /*************************************************************
1849 While updating the dimension object within the dimension we
1850 need to call the Validate_List_Button due to the following reason.
1851 For a List button to be enabled none of the dimension objects
1852 which are being used in the List button should have comparison
1853 as default.So after updating the dimension object properties
1854 we need to call the Sanity check API.
1855 /************************************************************/
1856 BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels_by_Dim
1857 (
1858 p_Dimension_Id => l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id
1859 , x_return_status => x_return_status
1860 , x_msg_count => x_msg_count
1861 , x_msg_data => x_msg_data
1862 );
1863
1864 ----DEBUG('10..'||get_default_value());
1865 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1866 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1867 END IF;
1868
1869 -- START Granular Locking added by Aditya
1870 BSC_BIS_LOCKS_PUB.Set_Time_Stamp_Dim_Group
1871 ( p_dim_group_id => l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id
1872 , x_return_status => x_return_status
1873 , x_msg_count => x_msg_count
1874 , x_msg_data => x_msg_data
1875 );
1876 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1877 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1878 END IF;
1879 END IF;
1880
1881 IF (p_dim_short_name IS NOT NULL AND p_dim_short_name <> BSC_BIS_DIMENSION_PUB.Unassigned_Dim) THEN
1882 -- Bug 4997042
1883 BSC_BIS_DIM_OBJ_PUB.Cascade_Dim_Props_Into_Dim_Grp (
1884 p_Dim_Obj_Short_Name => p_dim_obj_short_name
1885 , p_Dim_Short_Name => p_dim_short_name
1886 , p_All_Flag => p_total_flag
1887 , x_Return_Status => x_return_status
1888 , x_Msg_Count => x_msg_count
1889 , x_Msg_Data => x_msg_data
1890 );
1891 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1892 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1893 END IF;
1894
1895 ----DEBUG('11..'||get_default_value());
1896 END IF;
1897
1898 IF(p_dim_obj_short_name IS NOT NULL AND p_default_value IS NOT NULL) THEN
1899 --Here we need to cascade the changes to bsc_measure_props table.
1900 --when any of the dimension object is set to comparison mode then all
1901 --the kpis which are using this dimension object should be set to comparison mode
1902
1903 FOR cd IN c_kpi_meas_props LOOP
1904 l_kpi_measure_props_rec.objective_id := cd.indicator;
1905 l_kpi_measure_props_rec.kpi_measure_id := cd.kpi_measure_id;
1906 l_kpi_measure_props_rec.color_by_total := 1;
1907
1908 IF(p_default_value='C') THEN
1909 l_kpi_measure_props_rec.color_by_total := 0;
1910 END IF;
1911
1912 BSC_KPI_MEASURE_PROPS_PUB.Update_Kpi_Measure_Props
1913 (
1914 p_commit => FND_API.G_FALSE
1915 , p_kpi_measure_rec => l_kpi_measure_props_rec
1916 , p_cascade_shared => TRUE
1917 , x_return_status => x_return_status
1918 , x_msg_count => x_msg_count
1919 , x_msg_data => x_msg_data
1920 );
1921 IF (x_return_status <> NULL AND x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1922 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1923 END IF;
1924 END LOOP;
1925 END IF;
1926
1927 ----DEBUG('11..'||get_default_value());
1928
1929 IF (p_commit = FND_API.G_TRUE) THEN
1930 COMMIT;
1931
1932 END IF;
1933 x_return_status := FND_API.G_RET_STS_SUCCESS;
1934
1935 EXCEPTION
1936 WHEN FND_API.G_EXC_ERROR THEN
1937 IF (cr_bsc_dim_id%ISOPEN) THEN
1938 CLOSE cr_bsc_dim_id;
1939 END IF;
1940 IF (cr_bsc_dim_obj_id%ISOPEN) THEN
1941 CLOSE cr_bsc_dim_obj_id;
1942 END IF;
1943 IF (c_Defaut_Value%ISOPEN) THEN
1944 CLOSE c_Defaut_Value;
1945 END IF;
1946 ROLLBACK TO AssBSCDimObjectPMD;
1947 IF (x_msg_data IS NULL) THEN
1948 FND_MSG_PUB.Count_And_Get
1949 ( p_encoded => FND_API.G_FALSE
1950 , p_count => x_msg_count
1951 , p_data => x_msg_data
1952 );
1953 END IF;
1954
1955 x_return_status := FND_API.G_RET_STS_ERROR;
1956 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1957 IF (cr_bsc_dim_id%ISOPEN) THEN
1958 CLOSE cr_bsc_dim_id;
1959 END IF;
1960 IF (cr_bsc_dim_obj_id%ISOPEN) THEN
1961 CLOSE cr_bsc_dim_obj_id;
1962 END IF;
1963 IF (c_Defaut_Value%ISOPEN) THEN
1964 CLOSE c_Defaut_Value;
1965 END IF;
1966 ROLLBACK TO AssBSCDimObjectPMD;
1967 IF (x_msg_data IS NULL) THEN
1968 FND_MSG_PUB.Count_And_Get
1969 ( p_encoded => FND_API.G_FALSE
1970 , p_count => x_msg_count
1971 , p_data => x_msg_data
1972 );
1973 END IF;
1974 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1975
1976 WHEN NO_DATA_FOUND THEN
1977 IF (cr_bsc_dim_id%ISOPEN) THEN
1978 CLOSE cr_bsc_dim_id;
1979 END IF;
1980 IF (cr_bsc_dim_obj_id%ISOPEN) THEN
1981 CLOSE cr_bsc_dim_obj_id;
1982 END IF;
1983 IF (c_Defaut_Value%ISOPEN) THEN
1984 CLOSE c_Defaut_Value;
1985 END IF;
1986 ROLLBACK TO AssBSCDimObjectPMD;
1987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1988 IF (x_msg_data IS NOT NULL) THEN
1989 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Assign_Dimension_Object ';
1990 ELSE
1991 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Assign_Dimension_Object ';
1992 END IF;
1993
1994 WHEN OTHERS THEN
1995 IF (cr_bsc_dim_id%ISOPEN) THEN
1996 CLOSE cr_bsc_dim_id;
1997 END IF;
1998 IF (cr_bsc_dim_obj_id%ISOPEN) THEN
1999 CLOSE cr_bsc_dim_obj_id;
2000 END IF;
2001 IF (c_Defaut_Value%ISOPEN) THEN
2002 CLOSE c_Defaut_Value;
2003 END IF;
2004 ROLLBACK TO AssBSCDimObjectPMD;
2005 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2006 IF (x_msg_data IS NOT NULL) THEN
2007 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Assign_Dimension_Object ';
2008 ELSE
2009 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Assign_Dimension_Object ';
2010 END IF;
2011
2012 END Assign_Dimension_Object;
2013
2014 /*********************************************************************************
2015 UNASSIGN DIMENSION OBJECTS FROM DIMENSION
2016 *********************************************************************************/
2017 PROCEDURE UnAssign_Dimension_Objects
2018 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
2019 , p_dim_short_name IN VARCHAR2
2020 , p_dim_obj_short_names IN VARCHAR2
2021 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
2022 , x_return_status OUT NOCOPY VARCHAR2
2023 , x_msg_count OUT NOCOPY NUMBER
2024 , x_msg_data OUT NOCOPY VARCHAR2
2025 ) IS
2026 l_bsc_dimension_rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
2027 l_Dim_Obj_Source BSC_SYS_DIM_LEVELS_B.Source%TYPE := NULL;
2028 l_Dim_Grp_ID BSC_SYS_DIM_GROUPS_TL.Dim_Group_Id%TYPE;
2029 l_Source_Type BSC_SYS_DIM_LEVELS_B.Source%TYPE;
2030 l_dim_obj_names VARCHAR2(32000);
2031 l_dim_obj_name BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE;
2032 l_MTab_Tbl BSC_BIS_DIMENSION_PUB.KPI_Dim_Set_Table_Type;
2033 l_Bis_Group_ID BIS_DIMENSIONS.Dimension_ID%TYPE;
2034 l_kpi_cascade BOOLEAN := FALSE;
2035 l_count NUMBER;
2036 -- START Granular Locking Declaration added by Aditya
2037 l_Dim_Obj_Tab BSC_BIS_LOCKS_PUB.t_numberTable;
2038 l_index NUMBER := 0;
2039 -- END Granular Locking Declaration added by Aditya
2040 CURSOR cr_bsc_dimension_id IS
2041 SELECT Dim_Group_Id
2042 FROM BSC_SYS_DIM_GROUPS_VL
2043 WHERE Short_Name = p_dim_short_name;
2044
2045 CURSOR cr_bsc_dim_obj_id IS
2046 SELECT Dim_Level_Id
2047 , Source
2048 FROM BSC_SYS_DIM_LEVELS_B
2049 WHERE Short_Name = l_dim_obj_name;
2050
2051 CURSOR cr_bis_dim_ids IS
2052 SELECT Short_Name
2053 FROM BIS_LEVELS
2054 WHERE Dimension_Id = l_Bis_Group_ID;
2055 BEGIN
2056 SAVEPOINT UnAssBSCDimObjectPMD;
2057
2058 IF (p_dim_short_name <> BSC_BIS_DIMENSION_PUB.Unassigned_Dim) THEN
2059 FND_MSG_PUB.Initialize;
2060 x_return_status := FND_API.G_RET_STS_SUCCESS;
2061 IF (p_dim_short_name IS NULL) THEN
2062 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
2063 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_SHORT_NAME'), TRUE);
2064 FND_MSG_PUB.ADD;
2065 RAISE FND_API.G_EXC_ERROR;
2066 END IF;
2067 IF (cr_bsc_dimension_id%ISOPEN) THEN
2068 CLOSE cr_bsc_dimension_id;
2069 END IF;
2070 OPEN cr_bsc_dimension_id;
2071 FETCH cr_bsc_dimension_id
2072 INTO l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id;
2073 CLOSE cr_bsc_dimension_id;
2074 IF (l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id IS NULL) THEN
2075 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
2076 FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_DIMENSION'), TRUE);
2077 FND_MSG_PUB.ADD;
2078 RAISE FND_API.G_EXC_ERROR;
2079 END IF;
2080 -- START Granular Locking Declaration added by Aditya
2081 -- Lock all the object that would be un-assigned to the Dimension
2082 l_dim_obj_names := p_dim_obj_short_names;
2083 IF (p_dim_obj_short_names IS NOT NULL) THEN
2084 l_dim_obj_names := p_dim_obj_short_names;
2085 WHILE (is_more( p_dim_obj_short_names => l_dim_obj_names
2086 , p_dim_obj_name => l_dim_obj_name)
2087 ) LOOP
2088 l_Dim_Obj_Tab(l_index) := nvl(BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Id(l_dim_obj_name), -1);
2089 l_index := l_index + 1;
2090 END LOOP;
2091 BSC_BIS_LOCKS_PUB.Lock_Update_Dimension
2092 ( p_dimension_id => l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id
2093 , p_selected_dim_objets => l_Dim_Obj_Tab
2094 , p_time_stamp => p_time_stamp -- Granular Locking
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 END IF;
2103 -- End Granular Locking
2104 IF (p_dim_obj_short_names IS NOT NULL) THEN
2105 l_Source_Type := NVL(BSC_BIS_DIMENSION_PUB.Get_Dimension_Source(p_dim_short_name), 'BSC');
2106 /* -- Changed the position because must to go at the final when the metadata had
2107 -- been updated
2108 BSC_DIM_FILTERS_PUB.Check_Filters_Not_Apply
2109 ( p_Tab_Id => NULL
2110 , x_return_status => x_return_status
2111 , x_msg_count => x_msg_count
2112 , x_msg_data => x_msg_data
2113 );
2114 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2116 END IF;*/
2117 BSC_BIS_DIMENSION_PUB.Store_Dim_Set_Records
2118 ( p_dim_group_id => l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id
2119 , p_dim_short_name => p_dim_short_name
2120 , x_MTab_Tbl => l_MTab_Tbl
2121 );
2122 BSC_BIS_DIMENSION_PUB.Delete_Dim_Objs_In_DSet
2123 ( p_MTab_Tbl => l_MTab_Tbl
2124 , x_return_status => x_return_status
2125 , x_msg_count => x_msg_count
2126 , x_msg_data => x_msg_data
2127 );
2128
2129 l_dim_obj_names := p_dim_obj_short_names;
2130 WHILE (is_more(p_dim_obj_short_names => l_dim_obj_names
2131 , p_dim_obj_name => l_dim_obj_name)
2132 ) LOOP
2133 IF (cr_bsc_dim_obj_id%ISOPEN) THEN
2134 CLOSE cr_bsc_dim_obj_id;
2135 END IF;
2136 OPEN cr_bsc_dim_obj_id;
2137 FETCH cr_bsc_dim_obj_id INTO l_bsc_dimension_rec.Bsc_Level_Id, l_Dim_Obj_Source;
2138 CLOSE cr_bsc_dim_obj_id;
2139 IF (l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id IS NULL) THEN
2140 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
2141 FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIMENSION_OBJECT'), TRUE);
2142 FND_MSG_PUB.ADD;
2143 RAISE FND_API.G_EXC_ERROR;
2144 END IF;
2145 IF (BSC_BIS_DIMENSION_PUB.is_Relation_Exists
2146 (l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id, l_bsc_dimension_rec.Bsc_Level_Id)) THEN
2147 l_kpi_cascade := TRUE;
2148 IF (l_Dim_Obj_Source = 'PMF') THEN
2149 IF (BSC_BIS_DIMENSION_PUB.Get_Number_Of_Dimensions(l_bsc_dimension_rec.Bsc_Level_Id) = 1) THEN
2150 l_Dim_Grp_ID := BSC_BIS_DIMENSION_PUB.Get_Bsc_Dimension_ID(BSC_BIS_DIMENSION_PUB.Unassigned_Dim);
2151 UPDATE BSC_SYS_DIM_LEVELS_BY_GROUP
2152 SET Dim_Group_Id = l_Dim_Grp_ID
2153 WHERE Dim_Group_Id = l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id
2154 AND Dim_Level_Id = l_bsc_dimension_rec.Bsc_Level_Id;
2155 END IF;
2156 END IF;
2157 IF ((l_Dim_Obj_Source = 'BSC') OR
2158 (BSC_BIS_DIMENSION_PUB.Get_Number_Of_Dimensions(l_bsc_dimension_rec.Bsc_Level_Id) <> 1)) THEN
2159 BSC_DIMENSION_GROUPS_PUB.Delete_Dim_Levels_In_Group
2160 ( p_commit => FND_API.G_FALSE
2161 , p_Dim_Grp_Rec => l_bsc_dimension_rec
2162 , x_return_status => x_return_status
2163 , x_msg_count => x_msg_count
2164 , x_msg_data => x_msg_data
2165 );
2166 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2167 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2168 END IF;
2169 END IF;
2170 END IF;
2171 END LOOP;
2172 --Update BSC_KPI_DIM_LEVELS_B with the current status
2173 IF (l_kpi_cascade) THEN
2174 BSC_BIS_DIMENSION_PUB.Create_Dim_Objs_In_DSet
2175 ( p_MTab_Tbl => l_MTab_Tbl
2176 , x_return_status => x_return_status
2177 , x_msg_count => x_msg_count
2178 , x_msg_data => x_msg_data
2179 );
2180 ELSE
2181 BSC_BIS_DIMENSION_PUB.Create_Dim_Objs_In_DSet
2182 ( p_MTab_Tbl => l_MTab_Tbl
2183 , p_kpi_flag_change => BSC_DESIGNER_PVT.G_ActionFlag.Normal
2184 , x_return_status => x_return_status
2185 , x_msg_count => x_msg_count
2186 , x_msg_data => x_msg_data
2187 );
2188 END IF;
2189 --sync up Dimensions in BIS
2190 l_Bis_Group_ID := BSC_BIS_DIMENSION_PUB.Get_Bis_Dimension_ID(p_dim_short_name);
2191 FOR cd IN cr_bis_dim_ids LOOP
2192 BSC_BIS_DIMENSION_PUB.Sync_Dimensions_In_Bis
2193 ( p_commit => FND_API.G_FALSE
2194 , p_Dim_Obj_Short_Name => cd.Short_Name
2195 , p_Sync_Flag => TRUE
2196 , x_return_status => x_return_status
2197 , x_msg_count => x_msg_count
2198 , x_msg_data => x_msg_data
2199 );
2200 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2202 END IF;
2203 END LOOP;
2204 END IF;
2205
2206 -- This syncrinization mut to go at the final when all the metadata had
2207 -- been updated.
2208 IF (l_Source_Type = 'BSC') THEN
2209 BSC_DIM_FILTERS_PUB.Check_Filters_Not_Apply
2210 ( p_Tab_Id => NULL
2211 , x_return_status => x_return_status
2212 , x_msg_count => x_msg_count
2213 , x_msg_data => x_msg_data
2214 );
2215 END IF;
2216 -- Granular Locking : Change the Time Stamp of the Group, once it is changed
2217 BSC_BIS_LOCKS_PUB.Set_Time_Stamp_Dim_Group
2218 ( p_dim_group_id => l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id
2219 , x_return_status => x_return_status
2220 , x_msg_count => x_msg_count
2221 , x_msg_data => x_msg_data
2222 );
2223 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2224 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2225 END IF;
2226 -- Granular Locking : Change the Time Stamp of the Group, Once it is changed
2227 IF (p_commit = FND_API.G_TRUE) THEN
2228 COMMIT;
2229 END IF;
2230 END IF;
2231 x_return_status := FND_API.G_RET_STS_SUCCESS;
2232 EXCEPTION
2233 WHEN FND_API.G_EXC_ERROR THEN
2234 IF (cr_bsc_dimension_id%ISOPEN) THEN
2235 CLOSE cr_bsc_dimension_id;
2236 END IF;
2237 IF (cr_bsc_dim_obj_id%ISOPEN) THEN
2238 CLOSE cr_bsc_dim_obj_id;
2239 END IF;
2240 ROLLBACK TO UnAssBSCDimObjectPMD;
2241 IF (x_msg_data IS NULL) THEN
2242 FND_MSG_PUB.Count_And_Get
2243 ( p_encoded => FND_API.G_FALSE
2244 , p_count => x_msg_count
2245 , p_data => x_msg_data
2246 );
2247 END IF;
2248
2249 x_return_status := FND_API.G_RET_STS_ERROR;
2250 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2251 IF (cr_bsc_dimension_id%ISOPEN) THEN
2252 CLOSE cr_bsc_dimension_id;
2253 END IF;
2254 IF (cr_bsc_dim_obj_id%ISOPEN) THEN
2255 CLOSE cr_bsc_dim_obj_id;
2256 END IF;
2257 ROLLBACK TO UnAssBSCDimObjectPMD;
2258 IF (x_msg_data IS NULL) THEN
2259 FND_MSG_PUB.Count_And_Get
2260 ( p_encoded => FND_API.G_FALSE
2261 , p_count => x_msg_count
2262 , p_data => x_msg_data
2263 );
2264 END IF;
2265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2266
2267 WHEN NO_DATA_FOUND THEN
2268 IF (cr_bsc_dimension_id%ISOPEN) THEN
2269 CLOSE cr_bsc_dimension_id;
2270 END IF;
2271 IF (cr_bsc_dim_obj_id%ISOPEN) THEN
2272 CLOSE cr_bsc_dim_obj_id;
2273 END IF;
2274 ROLLBACK TO UnAssBSCDimObjectPMD;
2275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2276 IF (x_msg_data IS NOT NULL) THEN
2277 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.UnAssign_Dimension_Objects ';
2278 ELSE
2279 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.UnAssign_Dimension_Objects ';
2280 END IF;
2281
2282 WHEN OTHERS THEN
2283 IF (cr_bsc_dimension_id%ISOPEN) THEN
2284 CLOSE cr_bsc_dimension_id;
2285 END IF;
2286 IF (cr_bsc_dim_obj_id%ISOPEN) THEN
2287 CLOSE cr_bsc_dim_obj_id;
2288 END IF;
2289 ROLLBACK TO UnAssBSCDimObjectPMD;
2290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2291 IF (x_msg_data IS NOT NULL) THEN
2292 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.UnAssign_Dimension_Objects ';
2293 ELSE
2294 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.UnAssign_Dimension_Objects ';
2295 END IF;
2296
2297 END UnAssign_Dimension_Objects;
2298
2299 /*********************************************************************************
2300 RESTRICT INTERNAL DIMENSION OBJECT FOR USE IN DD
2301 *********************************************************************************/
2302 PROCEDURE Restrict_Internal_Dim_Objs
2303 ( p_dim_short_name IN VARCHAR2
2304 , p_assign_dim_obj_names IN VARCHAR2
2305 , p_unassign_dim_obj_names IN VARCHAR2
2306 , x_return_status OUT NOCOPY VARCHAR2
2307 , x_msg_count OUT NOCOPY NUMBER
2308 , x_msg_data OUT NOCOPY VARCHAR2
2309 ) IS
2310 l_dim_obj_sname VARCHAR2(32000);
2311 l_unassigns VARCHAR2(32000);
2312 l_unassign VARCHAR2(100);
2313 l_regions VARCHAR2(32000);
2314 l_dim_name VARCHAR2(300);
2315 l_dim_obj_name VARCHAR2(300);
2316
2317 BEGIN
2318 IF (p_unassign_dim_obj_names IS NOT NULL) THEN
2319 l_unassigns := p_unassign_dim_obj_names;
2320 WHILE (is_more( p_dim_obj_short_names => l_unassigns
2321 , p_dim_obj_name => l_unassign)
2322 ) LOOP
2323 l_regions := BSC_UTILITY.Is_Dim_In_AKReport(p_dim_short_name||'+'||l_unassign);
2324 IF(l_regions IS NOT NULL) THEN
2325 SELECT DIM_NAME
2326 INTO l_dim_name
2327 FROM BSC_BIS_DIM_VL
2328 WHERE SHORT_NAME = p_dim_short_name;
2329
2330 SELECT NAME
2331 INTO l_dim_obj_name
2332 FROM BSC_BIS_DIM_OBJS_VL
2333 WHERE SHORT_NAME = l_unassign;
2334
2335 FND_MESSAGE.SET_NAME('BIS','BIS_DIM_OBJ_RPTASSOC_ERROR');
2336 FND_MESSAGE.SET_TOKEN('DIM_NAME', l_dim_name);
2337 FND_MESSAGE.SET_TOKEN('DIM_OBJ_NAME', l_dim_obj_name);
2338 FND_MESSAGE.SET_TOKEN('REPORTS_ASSOC', l_regions);
2339 FND_MSG_PUB.ADD;
2340 RAISE FND_API.G_EXC_ERROR;
2341 END IF;
2342 END LOOP;
2343 END IF;
2344 l_dim_obj_sname := p_assign_dim_obj_names;
2345 IF (p_unassign_dim_obj_names IS NOT NULL) THEN
2346 IF (l_dim_obj_sname IS NULL) THEN
2347 l_dim_obj_sname := p_unassign_dim_obj_names;
2348 ELSE
2349 l_dim_obj_sname := l_dim_obj_sname|| ',' || p_unassign_dim_obj_names;
2350 END IF;
2351 END IF;
2352 BSC_UTILITY.Enable_Dimensions_Entity(
2353 p_Entity_Type => BSC_UTILITY.c_DIMENSION_OBJECT
2354 , p_Entity_Short_Names => l_dim_obj_sname
2355 , p_Entity_Action_Type => BSC_UTILITY.c_UPDATE
2356 , x_Return_Status => x_return_status
2357 , x_Msg_Count => x_msg_count
2358 , x_Msg_Data => x_msg_data
2359 );
2360 END Restrict_Internal_Dim_Objs;
2361
2362 /*********************************************************************************
2363 ASSIGN & UNASSIGN DIMENSION OBJECTS TO DIMENSION
2364 *********************************************************************************/
2365 PROCEDURE Assign_Unassign_Dim_Objs
2366 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
2367 , p_dim_short_name IN VARCHAR2
2368 , p_assign_dim_obj_names IN VARCHAR2
2369 , p_unassign_dim_obj_names IN VARCHAR2
2370 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
2371 , p_Restrict_Dim_Validate IN VARCHAR2 := NULL
2372 , x_return_status OUT NOCOPY VARCHAR2
2373 , x_msg_count OUT NOCOPY NUMBER
2374 , x_msg_data OUT NOCOPY VARCHAR2
2375 ) IS
2376 l_unassigns VARCHAR2(32000);
2377 l_assigns VARCHAR2(32000);
2378 l_unassign BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE;
2379 l_assign BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE;
2380 l_unassign_dim_objs VARCHAR2(32000);
2381 l_flag BOOLEAN;
2382
2383 l_dim_obj_sht_names VARCHAR2(32000);
2384 l_dim_obj_sht_name BIS_LEVELS.Short_Name%TYPE;
2385
2386 l_dim_short_name BIS_DIMENSIONS.short_name%TYPE;
2387 l_first_dim_short_name BIS_DIMENSIONS.short_name%TYPE;
2388 CURSOR c_dim_sht_names IS
2389 SELECT B.SHORT_NAME
2390 FROM BIS_LEVELS A
2391 , BIS_DIMENSIONS B
2392 WHERE B.DIMENSION_ID = A.DIMENSION_ID
2393 AND A.SHORT_NAME = l_dim_obj_sht_name;
2394
2395 CURSOR c_bsc_dim_sht_names IS
2396 SELECT dim_short_name
2397 FROM BSC_BIS_DIM_OBJ_BY_DIM_VL
2398 WHERE obj_short_name = l_dim_obj_sht_name
2399 AND dim_short_name <>p_dim_short_name;
2400 BEGIN
2401
2402 FND_MSG_PUB.Initialize;
2403 x_return_status := FND_API.G_RET_STS_SUCCESS;
2404 IF (p_dim_short_name IS NULL) THEN
2405 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
2406 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_SHORT_NAME'), TRUE);
2407 FND_MSG_PUB.ADD;
2408 RAISE FND_API.G_EXC_ERROR;
2409 END IF;
2410 IF (p_unassign_dim_obj_names IS NOT NULL) THEN
2411 l_unassigns := p_unassign_dim_obj_names;
2412 WHILE (is_more( p_dim_obj_short_names => l_unassigns
2413 , p_dim_obj_name => l_unassign)
2414 ) LOOP
2415 l_assigns := p_assign_dim_obj_names;
2416 l_flag := TRUE;
2417 WHILE (is_more( p_dim_obj_short_names => l_assigns
2418 , p_dim_obj_name => l_assign)
2419 ) LOOP
2420 IF(l_unassign = l_assign) THEN
2421 l_flag := FALSE;
2422 END IF;
2423 END LOOP;
2424 IF(l_flag) THEN
2425 IF (l_unassign_dim_objs IS NULL) THEN
2426 l_unassign_dim_objs := l_unassign;
2427 ELSE
2428 l_unassign_dim_objs := l_unassign_dim_objs||', '||l_unassign;
2429 END IF;
2430 END IF;
2431 END LOOP;
2432 IF (l_unassign_dim_objs IS NOT NULL) THEN
2433
2434 BSC_BIS_DIMENSION_PUB.UnAssign_Dimension_Objects
2435 ( p_commit => FND_API.G_FALSE
2436 , p_dim_short_name => p_dim_short_name
2437 , p_dim_obj_short_names => l_unassign_dim_objs
2438 , p_time_stamp => p_time_stamp -- Granular Locking
2439 , x_return_status => x_return_status
2440 , x_msg_count => x_msg_count
2441 , x_msg_data => x_msg_data
2442 );
2443 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2444 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2445 END IF;
2446 END IF;
2447 END IF;
2448 IF (p_Restrict_Dim_Validate IS NOT NULL) THEN
2449 Restrict_Internal_Dim_Objs
2450 ( p_dim_short_name => p_dim_short_name
2451 , p_assign_dim_obj_names => p_assign_dim_obj_names
2452 , p_unassign_dim_obj_names => l_unassign_dim_objs
2453 , x_return_status => x_return_status
2454 , x_msg_count => x_msg_count
2455 , x_msg_data => x_msg_data
2456 );
2457 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2458 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2459 END IF;
2460 END IF;
2461 IF (p_assign_dim_obj_names IS NOT NULL) THEN
2462 BSC_BIS_DIMENSION_PUB.Assign_Dimension_Objects
2463 ( p_commit => FND_API.G_FALSE
2464 , p_dim_short_name => p_dim_short_name
2465 , p_dim_obj_short_names => p_assign_dim_obj_names
2466 , p_time_stamp => p_time_stamp -- Granular Locking
2467 , x_return_status => x_return_status
2468 , x_msg_count => x_msg_count
2469 , x_msg_data => x_msg_data
2470 );
2471 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2472
2473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2474 END IF;
2475 END IF;
2476 -- Bug 3784852 validate empty dimension after unassign dim objs and remove those from dim sets for all objectives
2477 BSC_BIS_DIMENSION_PUB.Remove_Empty_Dims_For_DimSet
2478 ( p_commit => FND_API.G_FALSE
2479 , p_dim_short_names => p_dim_short_name
2480 , p_time_stamp => p_time_stamp -- Granular Locking
2481 , x_return_status => x_return_status
2482 , x_msg_count => x_msg_count
2483 , x_msg_data => x_msg_data
2484 );
2485 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2486 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2487 END IF;
2488 IF (p_commit = FND_API.G_TRUE) THEN
2489 COMMIT;
2490
2491 END IF;
2492 x_return_status := FND_API.G_RET_STS_SUCCESS;
2493
2494 EXCEPTION
2495 WHEN FND_API.G_EXC_ERROR THEN
2496 IF (x_msg_data IS NULL) THEN
2497 FND_MSG_PUB.Count_And_Get
2498 ( p_encoded => FND_API.G_FALSE
2499 , p_count => x_msg_count
2500 , p_data => x_msg_data
2501 );
2502 END IF;
2503
2504 x_return_status := FND_API.G_RET_STS_ERROR;
2505 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2506 IF (x_msg_data IS NULL) THEN
2507 FND_MSG_PUB.Count_And_Get
2508 ( p_encoded => FND_API.G_FALSE
2509 , p_count => x_msg_count
2510 , p_data => x_msg_data
2511 );
2512 END IF;
2513 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2514
2515 WHEN NO_DATA_FOUND THEN
2516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2517 IF (x_msg_data IS NOT NULL) THEN
2518 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Assign_Unassign_Dim_Objs ';
2519 ELSE
2520 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Assign_Unassign_Dim_Objs ';
2521 END IF;
2522
2523 WHEN OTHERS THEN
2524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2525 IF (x_msg_data IS NOT NULL) THEN
2526 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Assign_Unassign_Dim_Objs ';
2527 ELSE
2528 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Assign_Unassign_Dim_Objs ';
2529 END IF;
2530
2531 END Assign_Unassign_Dim_Objs;
2532 /*********************************************************************************
2533 UPDATE DIMENSION
2534 *********************************************************************************/
2535 PROCEDURE Update_Dimension
2536 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
2537 , p_dim_short_name IN VARCHAR2
2538 , p_display_name IN VARCHAR2
2539 , p_description IN VARCHAR2
2540 , p_application_id IN NUMBER
2541 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
2542 , p_hide IN VARCHAR2 := FND_API.G_FALSE
2543 , x_return_status OUT NOCOPY VARCHAR2
2544 , x_msg_count OUT NOCOPY NUMBER
2545 , x_msg_data OUT NOCOPY VARCHAR2
2546 )IS
2547 l_bis_dimension_rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
2548 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
2549 l_bsc_dimension_rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
2550 l_dim_short_name BSC_SYS_DIM_GROUPS_TL.Short_Name%TYPE;
2551 l_application_id BIS_DIMENSIONS.Application_Id%TYPE;
2552 l_bis_create BOOLEAN := FALSE;
2553 l_bsc_create BOOLEAN := FALSE;
2554
2555 l_count NUMBER;
2556
2557 l_pmf_disp_name VARCHAR2(255); -- DispName
2558
2559
2560
2561 CURSOR cr_bsc_dim_id IS
2562 SELECT name, dim_group_id
2563 FROM BSC_SYS_DIM_GROUPS_VL
2564 WHERE short_name = l_dim_short_name;
2565
2566 CURSOR cr_bis_dim_id IS
2567 SELECT name, description, dimension_id
2568 FROM BIS_DIMENSIONS_VL
2569 WHERE short_name = l_dim_short_name;
2570 BEGIN
2571 SAVEPOINT UpdateBSCDimensionPMD;
2572
2573 FND_MSG_PUB.Initialize;
2574 x_return_status := FND_API.G_RET_STS_SUCCESS;
2575 IF(p_dim_short_name IS NOT NULL) THEN
2576 l_application_id := p_application_id;
2577 END IF;
2578 l_dim_short_name := p_dim_short_name;
2579 IF (l_dim_short_name IS NULL) THEN
2580 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
2581 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_SHORT_NAME'), TRUE);
2582 FND_MSG_PUB.ADD;
2583 RAISE FND_API.G_EXC_ERROR;
2584 END IF;
2585
2586
2587 IF (cr_bis_dim_id%ISOPEN) THEN
2588 CLOSE cr_bis_dim_id;
2589 END IF;
2590 OPEN cr_bis_dim_id;
2591 FETCH cr_bis_dim_id
2592 INTO l_bis_dimension_rec.Dimension_Name
2593 , l_bis_dimension_rec.Description
2594 , l_bis_dimension_rec.dimension_id;
2595 IF (cr_bis_dim_id%ROWCOUNT = 0) THEN
2596 l_bis_create := TRUE; -- this flag indicates that the entries are not in the PMF metadata, so create it
2597 END IF;
2598 CLOSE cr_bis_dim_id;
2599
2600 IF (cr_bsc_dim_id%ISOPEN) THEN
2601 CLOSE cr_bsc_dim_id;
2602 END IF;
2603 OPEN cr_bsc_dim_id;
2604 FETCH cr_bsc_dim_id
2605 INTO l_bsc_dimension_rec.Bsc_Dim_Level_Group_Name
2606 , l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id;
2607 IF (cr_bsc_dim_id%ROWCOUNT = 0) THEN
2608 IF (l_bis_create) THEN
2609 l_bis_create := FALSE;
2610 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
2611 FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_DIMENSION'), TRUE);
2612 FND_MSG_PUB.ADD;
2613 RAISE FND_API.G_EXC_ERROR;
2614 ELSE
2615 l_bsc_create := TRUE; -- this flag indicates that the entries are not in the PMF metadata, so create it
2616 END IF;
2617 END IF;
2618 CLOSE cr_bsc_dim_id;
2619 IF (l_dim_short_name IS NULL) THEN
2620 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
2621 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_SHORT_NAME'), TRUE);
2622 FND_MSG_PUB.ADD;
2623 RAISE FND_API.G_EXC_ERROR;
2624 END IF;
2625 --check uniqueness of display name
2626 IF (p_display_name IS NOT NULL) THEN
2627 IF (p_display_name <> l_bis_dimension_rec.Dimension_Name) THEN
2628 l_pmf_disp_name := p_display_name;
2629 SELECT COUNT(1) INTO l_count
2630 FROM BIS_DIMENSIONS_VL
2631 WHERE UPPER(short_name) <> UPPER(l_dim_short_name)
2632 AND UPPER(name) = UPPER(p_display_name);
2633 WHILE(l_count > 0) LOOP
2634 l_pmf_disp_name := bsc_utility.get_Next_DispName(l_pmf_disp_name);
2635 SELECT COUNT(1) INTO l_count
2636 FROM BIS_DIMENSIONS_VL
2637 WHERE UPPER(name) = UPPER(l_pmf_disp_name);
2638 END LOOP;
2639 END IF;
2640 END IF;
2641 IF (l_bis_create) THEN
2642 IF (p_display_name IS NULL) THEN
2643 l_bis_dimension_rec.Dimension_Name := l_bsc_dimension_rec.Bsc_Dim_Level_Group_Name;
2644 ELSE
2645 l_bis_dimension_rec.Dimension_Name := l_pmf_disp_name;
2646 END IF;
2647
2648 l_bis_dimension_rec.Dimension_Short_Name := l_dim_short_name;
2649
2650 l_bis_dimension_rec.Application_ID := l_application_id;
2651
2652 l_bis_dimension_rec.Description := p_description;
2653
2654 l_bis_dimension_rec.Hide := p_Hide;
2655
2656 BIS_DIMENSION_PUB.Create_Dimension
2657 ( p_api_version => 1.0
2658 , p_commit => FND_API.G_FALSE
2659 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
2660 , p_Dimension_Rec => l_bis_dimension_rec
2661 , x_return_status => x_return_status
2662 , x_error_Tbl => l_error_tbl
2663 );
2664 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2665 IF (l_error_tbl.COUNT > 0) THEN
2666 x_msg_data := l_error_tbl(l_error_tbl.COUNT).Error_Description;
2667 IF(INSTR(x_msg_data, ' ') = 0 ) THEN
2668 FND_MESSAGE.SET_NAME('BIS', x_msg_data);
2669 FND_MSG_PUB.ADD;
2670 x_msg_data := NULL;
2671 END IF;
2672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2673 END IF;
2674
2675 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2676 END IF;
2677 ELSE
2678 l_bis_dimension_rec.Dimension_Name := l_pmf_disp_name;
2679
2680 l_bis_dimension_rec.Dimension_Short_Name := l_dim_short_name;
2681
2682 l_bis_dimension_rec.Application_ID := l_application_id;
2683
2684 l_bis_dimension_rec.Description := p_description;
2685
2686 l_bis_dimension_rec.Hide := p_Hide;
2687
2688 BIS_DIMENSION_PUB.Update_Dimension
2689 ( p_api_version => 1.0
2690 , p_commit => FND_API.G_FALSE
2691 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
2692 , p_Dimension_Rec => l_bis_dimension_rec
2693 , x_return_status => x_return_status
2694 , x_error_Tbl => l_error_tbl
2695 );
2696 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2697 IF (l_error_tbl.COUNT > 0) THEN
2698 x_msg_data := l_error_tbl(l_error_tbl.COUNT).Error_Description;
2699
2700 IF(INSTR(x_msg_data, ' ') = 0 ) THEN
2701 FND_MESSAGE.SET_NAME('BIS',x_msg_data);
2702 FND_MSG_PUB.ADD;
2703 x_msg_data := NULL;
2704 END IF;
2705 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2706 END IF;
2707
2708 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2709 END IF;
2710 END IF;
2711 l_bsc_dimension_rec.Bsc_Dim_Level_Index := 1;
2712 l_bsc_dimension_rec.Bsc_Dim_Level_Group_short_name := l_dim_short_name;
2713 l_bsc_dimension_rec.Bsc_Group_Level_Comp_Flag := -1;
2714 l_bsc_dimension_rec.Bsc_Group_Level_Default_Value := 'T';
2715 l_bsc_dimension_rec.Bsc_Group_Level_Default_Type := 0;
2716 l_bsc_dimension_rec.Bsc_Group_Level_Filter_Col := NULL;
2717 l_bsc_dimension_rec.Bsc_Group_Level_Filter_Value := 0;
2718 l_bsc_dimension_rec.Bsc_Group_Level_No_Items := 0;
2719 l_bsc_dimension_rec.Bsc_Group_Level_Parent_In_Tot := 2;
2720 l_bsc_dimension_rec.Bsc_Group_Level_Total_Flag := -1;
2721 IF (l_bsc_create) THEN
2722 IF (p_display_name IS NULL) THEN
2723 l_bsc_dimension_rec.Bsc_Dim_Level_Group_Name := l_bis_dimension_rec.Dimension_Name;
2724 ELSE
2725 l_bsc_dimension_rec.Bsc_Dim_Level_Group_Name := p_display_name;
2726 END IF;
2727
2728 BSC_DIMENSION_GROUPS_PUB.Create_Dimension_Group
2729 ( p_commit => FND_API.G_FALSE
2730 , p_Dim_Grp_Rec => l_bsc_dimension_rec
2731 , p_create_Dim_Levels => FALSE
2732 , x_return_status => x_return_status
2733 , x_msg_count => x_msg_count
2734 , x_msg_data => x_msg_data
2735 );
2736 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2737 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2738 END IF;
2739 --update grp id in BIS Diemension Tables
2740 --this call will be moved to CRUD APIs later
2741 UPDATE BIS_DIMENSIONS
2742 SET Dim_Grp_ID =
2743 ( SELECT dim_group_id
2744 FROM BSC_SYS_DIM_GROUPS_VL
2745 WHERE Short_Name = l_bsc_dimension_rec.Bsc_Dim_Level_Group_short_name
2746 );
2747 ELSE
2748 l_bsc_dimension_rec.Bsc_Dim_Level_Group_Name := p_display_name;
2749
2750 l_bsc_dimension_rec.Bsc_Dim_Level_Group_short_name := l_dim_short_name;
2751 BSC_DIMENSION_GROUPS_PUB.Update_Dimension_Group
2752 ( p_commit => FND_API.G_FALSE
2753 , p_Dim_Grp_Rec => l_bsc_dimension_rec
2754 , p_create_Dim_Levels => FALSE
2755 , x_return_status => x_return_status
2756 , x_msg_count => x_msg_count
2757 , x_msg_data => x_msg_data
2758 );
2759 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2760 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2761 END IF;
2762 END IF;
2763 l_bis_create := FALSE;--Initialize with the default value
2764 l_bsc_create := FALSE;
2765 IF (p_commit = FND_API.G_TRUE) THEN
2766 COMMIT;
2767
2768 END IF;
2769 x_return_status := FND_API.G_RET_STS_SUCCESS;
2770
2771 EXCEPTION
2772 WHEN FND_API.G_EXC_ERROR THEN
2773 IF (cr_bis_dim_id%ISOPEN) THEN
2774 CLOSE cr_bis_dim_id;
2775 END IF;
2776 IF (cr_bsc_dim_id%ISOPEN) THEN
2777 CLOSE cr_bsc_dim_id;
2778 END IF;
2779 ROLLBACK TO UpdateBSCDimensionPMD;
2780 IF (x_msg_data IS NULL) THEN
2781 FND_MSG_PUB.Count_And_Get
2782 ( p_encoded => FND_API.G_FALSE
2783 , p_count => x_msg_count
2784 , p_data => x_msg_data
2785 );
2786 END IF;
2787
2788 x_return_status := FND_API.G_RET_STS_ERROR;
2789 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2790 IF (cr_bis_dim_id%ISOPEN) THEN
2791 CLOSE cr_bis_dim_id;
2792 END IF;
2793 IF (cr_bsc_dim_id%ISOPEN) THEN
2794 CLOSE cr_bsc_dim_id;
2795 END IF;
2796 ROLLBACK TO UpdateBSCDimensionPMD;
2797 IF (x_msg_data IS NULL) THEN
2798 FND_MSG_PUB.Count_And_Get
2799 ( p_encoded => FND_API.G_FALSE
2800 , p_count => x_msg_count
2801 , p_data => x_msg_data
2802 );
2803 END IF;
2804 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2805
2806 WHEN NO_DATA_FOUND THEN
2807 IF (cr_bis_dim_id%ISOPEN) THEN
2808 CLOSE cr_bis_dim_id;
2809 END IF;
2810 IF (cr_bsc_dim_id%ISOPEN) THEN
2811 CLOSE cr_bsc_dim_id;
2812 END IF;
2813 ROLLBACK TO UpdateBSCDimensionPMD;
2814 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2815 IF (x_msg_data IS NOT NULL) THEN
2816 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Update_Dimension ';
2817 ELSE
2818 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Update_Dimension ';
2819 END IF;
2820
2821 WHEN OTHERS THEN
2822 IF (cr_bis_dim_id%ISOPEN) THEN
2823 CLOSE cr_bis_dim_id;
2824 END IF;
2825 IF (cr_bsc_dim_id%ISOPEN) THEN
2826 CLOSE cr_bsc_dim_id;
2827 END IF;
2828 ROLLBACK TO UpdateBSCDimensionPMD;
2829 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2830 IF (x_msg_data IS NOT NULL) THEN
2831 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Update_Dimension ';
2832 ELSE
2833 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Update_Dimension ';
2834 END IF;
2835
2836 END Update_Dimension;
2837
2838 /*********************************************************************************
2839 UPDATE DIMENSION
2840 *********************************************************************************/
2841 PROCEDURE Update_Dimension
2842 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
2843 , p_dim_short_name IN VARCHAR2
2844 , p_display_name IN VARCHAR2
2845 , p_description IN VARCHAR2
2846 , p_application_id IN NUMBER
2847 , p_assign_dim_obj_names IN VARCHAR2
2848 , p_unassign_dim_obj_names IN VARCHAR2
2849 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
2850 , p_hide IN VARCHAR2 := FND_API.G_FALSE
2851 , p_Restrict_Dim_Validate IN VARCHAR2 := NULL
2852 , x_return_status OUT NOCOPY VARCHAR2
2853 , x_msg_count OUT NOCOPY NUMBER
2854 , x_msg_data OUT NOCOPY VARCHAR2
2855 )IS
2856 -- START Granular Locking Declaration added by Aditya
2857 l_bsc_dimension_rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
2858
2859 CURSOR cr_bsc_dim_id IS
2860 SELECT dim_group_id
2861 FROM BSC_SYS_DIM_GROUPS_VL
2862 WHERE short_name = p_dim_short_name;
2863
2864 l_Dim_Obj_Tab BSC_BIS_LOCKS_PUB.t_numberTable;
2865 l_dim_obj_names VARCHAR2(32000);
2866
2867 l_dim_obj_name BSC_SYS_DIM_LEVELS_B.Short_Name%TYPE;
2868 l_index NUMBER := 0;
2869 l_mix_type_dim BOOLEAN;
2870 l_dim_type VARCHAR2(10);
2871 l_count NUMBER;
2872
2873 CURSOR c_indicators IS
2874 SELECT DISTINCT indicator
2875 FROM BSC_KPI_DIM_GROUPS
2876 WHERE dim_group_id = l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id;
2877 -- END Granular Locking Declaration added by Aditya
2878 BEGIN
2879 -- START Granular Locking added by Aditya
2880
2881 FND_MSG_PUB.Initialize;
2882 x_return_status := FND_API.G_RET_STS_SUCCESS;
2883 IF (p_dim_short_name IS NULL) THEN
2884 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
2885 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_SHORT_NAME'), TRUE);
2886 FND_MSG_PUB.ADD;
2887 RAISE FND_API.G_EXC_ERROR;
2888 END IF;
2889 IF (cr_bsc_dim_id%ISOPEN) THEN
2890 CLOSE cr_bsc_dim_id;
2891 END IF;
2892 OPEN cr_bsc_dim_id;
2893 FETCH cr_bsc_dim_id
2894 INTO l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id;
2895 CLOSE cr_bsc_dim_id;
2896 IF (l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id IS NULL) THEN
2897 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
2898 FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_DIMENSION'), TRUE);
2899 FND_MSG_PUB.ADD;
2900 RAISE FND_API.G_EXC_ERROR;
2901 END IF;
2902
2903 l_mix_type_dim := FALSE;
2904 IF(p_assign_dim_obj_names IS NOT NULL) THEN
2905 l_mix_type_dim := check_sametype_dimobjs
2906 ( p_dim_name => p_display_name
2907 , p_dim_short_name => p_dim_short_name
2908 , p_dim_short_names => p_assign_dim_obj_names
2909 , p_Restrict_Dim_Validate => p_Restrict_Dim_Validate
2910 , x_dim_type => l_dim_type
2911 , x_return_status => x_return_status
2912 , x_msg_count => x_msg_count
2913 , x_msg_data => x_msg_data
2914 );
2915 IF (l_mix_type_dim) THEN
2916 RAISE FND_API.G_EXC_ERROR;
2917 END IF;
2918
2919 END IF;
2920
2921 -- Enh#4172034: Validations
2922 IF (l_dim_type = 'PMF') THEN
2923
2924 SELECT COUNT(1) INTO l_count
2925 FROM BSC_SYS_DIM_GROUPS_VL
2926 WHERE UPPER(short_name) <> UPPER(p_dim_short_name)
2927 AND UPPER(name) = UPPER(p_display_name); -- already trimmed from JAVA
2928 IF (l_count <> 0) THEN
2929 FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
2930 FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DISPLAY_NAME'), TRUE);
2931 FND_MESSAGE.SET_TOKEN('NAME_VALUE', p_display_name);
2932 FND_MSG_PUB.ADD;
2933 RAISE FND_API.G_EXC_ERROR;
2934 END IF;
2935
2936 SELECT COUNT(1) INTO l_count
2937 FROM BIS_DIMENSIONS_VL
2938 WHERE UPPER(short_name) <> UPPER(p_dim_short_name)
2939 AND UPPER(name) = UPPER(p_display_name);
2940 IF (l_count <> 0) THEN
2941 FND_MESSAGE.SET_NAME('BSC','BSC_UNIQUE_NAME_REQUIRED');
2942 FND_MESSAGE.SET_TOKEN('SHORT_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DISPLAY_NAME'), TRUE);
2943 FND_MESSAGE.SET_TOKEN('NAME_VALUE', p_display_name);
2944 FND_MSG_PUB.ADD;
2945 RAISE FND_API.G_EXC_ERROR;
2946 END IF;
2947
2948 END IF;
2949
2950
2951 -- Lock all the object that would be assigned to the Dimension
2952 l_dim_obj_names := p_assign_dim_obj_names;
2953 IF (p_assign_dim_obj_names IS NOT NULL) THEN
2954 l_dim_obj_names := p_assign_dim_obj_names ;
2955 WHILE (is_more( p_dim_obj_short_names => l_dim_obj_names
2956 , p_dim_obj_name => l_dim_obj_name)
2957 ) LOOP
2958
2959 l_Dim_Obj_Tab(l_index) := NVL(BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Id(l_dim_obj_name), -1);
2960 l_index := l_index + 1;
2961 END LOOP;
2962 END IF;
2963 -- Lock all the object that would be un-assigned to the Dimension
2964 l_dim_obj_names := p_unassign_dim_obj_names;
2965 IF (p_unassign_dim_obj_names IS NOT NULL) THEN
2966 l_dim_obj_names := p_unassign_dim_obj_names ;
2967 WHILE (is_more( p_dim_obj_short_names => l_dim_obj_names
2968 , p_dim_obj_name => l_dim_obj_name)
2969 ) LOOP
2970 l_Dim_Obj_Tab(l_index) := NVL(BSC_DIMENSION_LEVELS_PVT.get_Dim_Level_Id(l_dim_obj_name), -1);
2971 l_index := l_index + 1;
2972 END LOOP;
2973 END IF;
2974 -- Lock all the Dimension Objects to be assigned/unassigned to the Dimension
2975 -- Pass the time_stamp_value
2976 BSC_BIS_LOCKS_PUB.Lock_Update_Dimension
2977 ( p_dimension_id => l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id
2978 , p_selected_dim_objets => l_Dim_Obj_Tab
2979 , p_time_stamp => p_time_stamp -- Granular Locking
2980 , x_return_status => x_return_status
2981 , x_msg_count => x_msg_count
2982 , x_msg_data => x_msg_data
2983 );
2984 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2985 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2986 END IF;
2987 -- End Granular Locking
2988 BSC_BIS_DIMENSION_PUB.Update_Dimension
2989 ( p_commit => FND_API.G_FALSE
2990 , p_dim_short_name => p_dim_short_name
2991 , p_display_name => p_display_name
2992 , p_description => p_description
2993 , p_application_id => p_application_id
2994 , p_time_stamp => NULL -- Granular Locking
2995 , p_hide => p_hide
2996 , x_return_status => x_return_status
2997 , x_msg_count => x_msg_count
2998 , x_msg_data => x_msg_data
2999 );
3000 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3001 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3002 END IF;
3003 BSC_BIS_DIMENSION_PUB.Assign_Unassign_Dim_Objs
3004 ( p_commit => FND_API.G_FALSE
3005 , p_dim_short_name => p_dim_short_name
3006 , p_assign_dim_obj_names => p_assign_dim_obj_names
3007 , p_unassign_dim_obj_names => p_unassign_dim_obj_names
3008 , p_time_stamp => NULL
3009 , p_Restrict_Dim_Validate => p_Restrict_Dim_Validate
3010 , x_return_status => x_return_status
3011 , x_msg_count => x_msg_count
3012 , x_msg_data => x_msg_data
3013 );
3014 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3016 END IF;
3017 -- Granular Locking : Change the Time Stamp of the Group, once it is changed
3018 BSC_BIS_LOCKS_PUB.Set_Time_Stamp_Dim_Group
3019 ( p_dim_group_id => l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id
3020 , x_return_status => x_return_status
3021 , x_msg_count => x_msg_count
3022 , x_msg_data => x_msg_data
3023 );
3024 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3026 END IF;
3027 -- Granular Locking : Change the Time Stamp of the Group, Once it is changed
3028 BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels_by_Dim
3029 (
3030 p_Dimension_Id => l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id
3031 , x_return_status => x_return_status
3032 , x_msg_count => x_msg_count
3033 , x_msg_data => x_msg_data
3034 );
3035 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3036 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3037 END IF;
3038 IF (p_commit = FND_API.G_TRUE) THEN
3039 COMMIT;
3040
3041 END IF;
3042 x_return_status := FND_API.G_RET_STS_SUCCESS;
3043
3044 EXCEPTION
3045 WHEN FND_API.G_EXC_ERROR THEN
3046 IF (cr_bsc_dim_id%ISOPEN) THEN
3047 CLOSE cr_bsc_dim_id;
3048 END IF;
3049 IF (x_msg_data IS NULL) THEN
3050 FND_MSG_PUB.Count_And_Get
3051 ( p_encoded => FND_API.G_FALSE
3052 , p_count => x_msg_count
3053 , p_data => x_msg_data
3054 );
3055 END IF;
3056
3057 x_return_status := FND_API.G_RET_STS_ERROR;
3058 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3059 IF (cr_bsc_dim_id%ISOPEN) THEN
3060 CLOSE cr_bsc_dim_id;
3061 END IF;
3062 IF (x_msg_data IS NULL) THEN
3063 FND_MSG_PUB.Count_And_Get
3064 ( p_encoded => FND_API.G_FALSE
3065 , p_count => x_msg_count
3066 , p_data => x_msg_data
3067 );
3068 END IF;
3069 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3070
3071 WHEN NO_DATA_FOUND THEN
3072 IF (cr_bsc_dim_id%ISOPEN) THEN
3073 CLOSE cr_bsc_dim_id;
3074 END IF;
3075 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3076 IF (x_msg_data IS NOT NULL) THEN
3077 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Update_Dimension ';
3078 ELSE
3079 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Update_Dimension ';
3080 END IF;
3081
3082 WHEN OTHERS THEN
3083 IF (cr_bsc_dim_id%ISOPEN) THEN
3084 CLOSE cr_bsc_dim_id;
3085 END IF;
3086 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3087 IF (x_msg_data IS NOT NULL) THEN
3088 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Update_Dimension ';
3089 ELSE
3090 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Update_Dimension ';
3091 END IF;
3092
3093 END Update_Dimension;
3094
3095 /*********************************************************************************
3096 UPDATE DIMENSION
3097 *********************************************************************************/
3098 PROCEDURE Update_Dimension
3099 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
3100 , p_dim_short_name IN VARCHAR2
3101 , p_display_name IN VARCHAR2
3102 , p_description IN VARCHAR2
3103 , p_application_id IN NUMBER
3104 , p_dim_obj_short_names IN VARCHAR2
3105 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
3106 , p_hide IN VARCHAR2 := FND_API.G_FALSE
3107 , p_Restrict_Dim_Validate IN VARCHAR2 := NULL
3108 , x_return_status OUT NOCOPY VARCHAR2
3109 , x_msg_count OUT NOCOPY NUMBER
3110 , x_msg_data OUT NOCOPY VARCHAR2
3111 )IS
3112 l_bsc_dimension_rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
3113 l_kpi_id NUMBER;
3114 l_dim_set_id NUMBER;
3115 l_count NUMBER;
3116 l_indp_dimobj NUMBER;
3117 l_affected_kpis VARCHAR2(32000);
3118 l_kpi_name VARCHAR2(20000);
3119 l_is_kpi_affected BOOLEAN;
3120
3121 CURSOR cr_bsc_dim_id IS
3122 SELECT dim_group_id
3123 FROM BSC_SYS_DIM_GROUPS_VL WHERE short_name = p_dim_short_name;
3124
3125 CURSOR cr_bsc_dim IS
3126 SELECT short_name
3127 FROM BSC_SYS_DIM_LEVELS_B
3128 WHERE dim_level_id IN (SELECT dim_level_id
3129 FROM BSC_SYS_DIM_LEVELS_BY_GROUP
3130 WHERE dim_group_id = l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id);
3131
3132 CURSOR cr_indicdimset_for_dimgrp IS
3133 SELECT grp.indicator , grp.dim_set_id,count(kpi.dim_level_index)
3134 FROM BSC_KPI_DIM_GROUPS grp,
3135 BSC_KPI_DIM_LEVELS_B kpi
3136 WHERE kpi.indicator = grp.indicator
3137 AND kpi.dim_set_id = grp.dim_set_id
3138 AND grp.dim_group_id = l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id
3139 GROUP BY grp.indicator,grp.dim_set_id;
3140
3141 -- START Granular Locking Declaration added by Aditya
3142 l_Dim_Obj_Tab BSC_BIS_LOCKS_PUB.t_numberTable;
3143 l_dim_obj_names VARCHAR2(32000);
3144
3145 l_unassign_dim_obj_names VARCHAR2(32000);
3146
3147 l_dim_obj_name VARCHAR2(30);
3148 l_index NUMBER := 0;
3149 l_mix_type_dim BOOLEAN;
3150 -- END Granular Locking Declaration added by Aditya
3151
3152 BEGIN
3153
3154 FND_MSG_PUB.Initialize;
3155 x_return_status := FND_API.G_RET_STS_SUCCESS;
3156 IF (p_dim_short_name IS NULL) THEN
3157 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
3158 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_SHORT_NAME'), TRUE);
3159 FND_MSG_PUB.ADD;
3160 RAISE FND_API.G_EXC_ERROR;
3161 END IF;
3162
3163
3164 IF (cr_bsc_dim_id%ISOPEN) THEN
3165 CLOSE cr_bsc_dim_id;
3166 END IF;
3167 OPEN cr_bsc_dim_id;
3168 FETCH cr_bsc_dim_id
3169 INTO l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id;
3170 CLOSE cr_bsc_dim_id;
3171
3172 IF (l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id IS NULL) THEN
3173 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
3174 FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_DIMENSION'), TRUE);
3175 FND_MSG_PUB.ADD;
3176 RAISE FND_API.G_EXC_ERROR;
3177 END IF;
3178 FOR cd IN cr_bsc_dim LOOP
3179 IF (l_unassign_dim_obj_names IS NULL) THEN
3180 l_unassign_dim_obj_names := cd.short_name;
3181 ELSE
3182 l_unassign_dim_obj_names := l_unassign_dim_obj_names||', '||cd.short_name;
3183 END IF;
3184 END LOOP;
3185 BSC_BIS_DIMENSION_PUB.Update_Dimension
3186 ( p_commit => FND_API.G_FALSE
3187 , p_dim_short_name => p_dim_short_name
3188 , p_display_name => p_display_name
3189 , p_description => p_description
3190 , p_application_id => p_application_id
3191 , p_assign_dim_obj_names => p_dim_obj_short_names
3192 , p_unassign_dim_obj_names => l_unassign_dim_obj_names
3193 , p_time_stamp => p_time_stamp -- Need to add timestamp
3194 , p_hide => p_hide
3195 , p_Restrict_Dim_Validate => p_Restrict_Dim_Validate
3196 , x_return_status => x_return_status
3197 , x_msg_count => x_msg_count
3198 , x_msg_data => x_msg_data
3199 );
3200 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3202 END IF;
3203 /********************************************************
3204 Check no of independent dimension objects in dimension set
3205 ********************************************************/
3206 l_is_kpi_affected := FALSE;
3207 OPEN cr_indicdimset_for_dimgrp;
3208 LOOP
3209 FETCH cr_indicdimset_for_dimgrp INTO l_kpi_id,l_dim_set_id,l_count;
3210 EXIT WHEN cr_indicdimset_for_dimgrp%NOTFOUND;
3211 IF( l_count > bsc_utility.NO_IND_DIM_OBJ_LIMIT) THEN
3212
3213 l_indp_dimobj := 0;
3214 l_indp_dimobj := bsc_utility.get_nof_independent_dimobj
3215 ( p_Kpi_Id => l_kpi_id
3216 , p_Dim_Set_Id => l_dim_set_id
3217 );
3218 IF(l_indp_dimobj >bsc_utility.NO_IND_DIM_OBJ_LIMIT) THEN
3219 SELECT NAME INTO l_kpi_name
3220 FROM BSC_KPIS_VL
3221 WHERE INDICATOR = l_kpi_id;
3222
3223 IF(l_affected_kpis IS NULL) THEN
3224 l_affected_kpis := '['||l_kpi_name||']';
3225 ELSE
3226 IF(INSTR(l_affected_kpis,l_kpi_name) = 0) THEN
3227 l_affected_kpis := l_affected_kpis ||','|| '['||l_kpi_name||']';
3228 END IF;
3229 END IF;
3230 l_is_kpi_affected := TRUE;
3231 END IF;
3232
3233 END IF;
3234 END LOOP;
3235
3236 CLOSE cr_indicdimset_for_dimgrp;
3237 IF(l_is_kpi_affected) THEN
3238 FND_MESSAGE.SET_NAME('BSC','BSC_IND_DIMOBJ_LIMIT');
3239 FND_MESSAGE.SET_TOKEN('NAME_LIST',l_affected_kpis);
3240 FND_MSG_PUB.ADD;
3241 RAISE FND_API.G_EXC_ERROR;
3242 END IF;
3243
3244 /********************************************************/
3245
3246
3247
3248 IF (p_commit = FND_API.G_TRUE) THEN
3249 COMMIT;
3250
3251 END IF;
3252 x_return_status := FND_API.G_RET_STS_SUCCESS;
3253
3254 EXCEPTION
3255 WHEN FND_API.G_EXC_ERROR THEN
3256 IF (cr_bsc_dim_id%ISOPEN) THEN
3257 CLOSE cr_bsc_dim_id;
3258 END IF;
3259 IF (x_msg_data IS NULL) THEN
3260 FND_MSG_PUB.Count_And_Get
3261 ( p_encoded => FND_API.G_FALSE
3262 , p_count => x_msg_count
3263 , p_data => x_msg_data
3264 );
3265 END IF;
3266
3267 x_return_status := FND_API.G_RET_STS_ERROR;
3268 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3269 IF (cr_bsc_dim_id%ISOPEN) THEN
3270 CLOSE cr_bsc_dim_id;
3271 END IF;
3272 IF (x_msg_data IS NULL) THEN
3273 FND_MSG_PUB.Count_And_Get
3274 ( p_encoded => FND_API.G_FALSE
3275 , p_count => x_msg_count
3276 , p_data => x_msg_data
3277 );
3278 END IF;
3279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3280
3281 WHEN NO_DATA_FOUND THEN
3282 IF (cr_bsc_dim_id%ISOPEN) THEN
3283 CLOSE cr_bsc_dim_id;
3284 END IF;
3285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3286 IF (x_msg_data IS NOT NULL) THEN
3287 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Update_Dimension ';
3288 ELSE
3289 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Update_Dimension ';
3290 END IF;
3291
3292 WHEN OTHERS THEN
3293 IF (cr_bsc_dim_id%ISOPEN) THEN
3294 CLOSE cr_bsc_dim_id;
3295 END IF;
3296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3297 IF (x_msg_data IS NOT NULL) THEN
3298 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Update_Dimension ';
3299 ELSE
3300 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Update_Dimension ';
3301 END IF;
3302
3303 END Update_Dimension;
3304 /*********************************************************************************
3305 DELETE DIMENSION
3306 *********************************************************************************/
3307 PROCEDURE Delete_Dimension
3308 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
3309 , p_dim_short_name IN VARCHAR2
3310 , x_return_status OUT NOCOPY VARCHAR2
3311 , x_msg_count OUT NOCOPY NUMBER
3312 , x_msg_data OUT NOCOPY VARCHAR2
3313 ) IS
3314
3315 l_delete BOOLEAN := TRUE;
3316 l_bis_dimension_rec BIS_DIMENSION_PUB.Dimension_Rec_Type;
3317 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
3318 l_bsc_dimension_rec BSC_DIMENSION_GROUPS_PUB.Bsc_Dim_Group_Rec_Type;
3319 l_Dim_Obj_Short_Name BIS_LEVELS.Short_Name%TYPE;
3320 l_count NUMBER;
3321 l_delete_count NUMBER := 0;
3322 l_Bis_Group_ID BIS_DIMENSIONS.Dimension_ID%TYPE;
3323 l_regions VARCHAR2(32000);
3324
3325 CURSOR cr_bis_dim_short_name IS
3326 SELECT dimension_id
3327 ,name
3328 FROM BIS_DIMENSIONS_VL
3329 WHERE short_name = p_dim_short_name;
3330
3331 CURSOR cr_bsc_dimension_id IS
3332 SELECT dim_group_id
3333 , name
3334 FROM BSC_SYS_DIM_GROUPS_VL
3335 WHERE short_name = p_dim_short_name;
3336
3337 CURSOR cr_bis_dim_ids IS
3338 SELECT Short_Name
3339 FROM BIS_LEVELS
3340 WHERE Dimension_Id = l_bis_dimension_rec.dimension_id;
3341 BEGIN
3342 SAVEPOINT DeleteBSCDimensionsPMD;
3343
3344 FND_MSG_PUB.Initialize;
3345 x_return_status := FND_API.G_RET_STS_SUCCESS;
3346 IF (p_dim_short_name IS NULL) THEN
3347 FND_MESSAGE.SET_NAME('BSC','BSC_REQ_FIELD_MISSING');
3348 FND_MESSAGE.SET_TOKEN('FIELD_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'DIM_SHORT_NAME'), TRUE);
3349 FND_MSG_PUB.ADD;
3350 RAISE FND_API.G_EXC_ERROR;
3351 END IF;
3352 --UNASSIGNED group can't be deleted
3353 IF (p_dim_short_name = BSC_BIS_DIMENSION_PUB.Unassigned_Dim) THEN
3354 FND_MESSAGE.SET_NAME('BSC','BSC_B_CAN_NOT_DELETE_GROUP');
3355 FND_MSG_PUB.ADD;
3356 RAISE FND_API.G_EXC_ERROR;
3357 END IF;
3358 --The Dimension [dimension display name] cannot be deleted. If it is being used in a Measure, Report or Key Performance Indicator.
3359 IF (cr_bis_dim_short_name%ISOPEN) THEN
3360 CLOSE cr_bis_dim_short_name;
3361 END IF;
3362 OPEN cr_bis_dim_short_name;
3363 FETCH cr_bis_dim_short_name
3364 INTO l_bis_dimension_rec.dimension_id
3365 , l_bis_dimension_rec.Dimension_Name;
3366 IF (cr_bis_dim_short_name%ROWCOUNT = 0) THEN
3367 l_delete := FALSE;
3368 END IF;
3369 CLOSE cr_bis_dim_short_name;
3370
3371 --sync up Dimensions in BIS
3372 l_Bis_Group_ID := BSC_BIS_DIMENSION_PUB.Get_Bis_Dimension_ID(p_dim_short_name);
3373 FOR cd IN cr_bis_dim_ids LOOP
3374 BSC_BIS_DIMENSION_PUB.Sync_Dimensions_In_Bis
3375 ( p_commit => FND_API.G_FALSE
3376 , p_Dim_Obj_Short_Name => cd.Short_Name
3377 , p_Sync_Flag => FALSE
3378 , x_return_status => x_return_status
3379 , x_msg_count => x_msg_count
3380 , x_msg_data => x_msg_data
3381 );
3382 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3384 END IF;
3385 END LOOP;
3386 --assign values to bis records
3387
3388 l_bis_dimension_rec.Dimension_Short_Name := p_dim_short_name;
3389
3390 IF (l_delete) THEN
3391 SELECT COUNT(1) INTO l_count
3392 FROM BIS_INDICATOR_DIMENSIONS
3393 WHERE DIMENSION_ID = l_bis_dimension_rec.dimension_id;
3394 IF (l_count <> 0) THEN
3395 FND_MESSAGE.SET_NAME('BSC','BSC_NOT_DELETE_DIM_IN_MEASURE');
3396 FND_MESSAGE.SET_TOKEN('SHORT_NAME', l_bis_dimension_rec.Dimension_Name);
3397 FND_MSG_PUB.ADD;
3398 RAISE FND_API.G_EXC_ERROR;
3399 END IF;
3400 BIS_DIMENSION_PUB.Delete_Dimension
3401 ( p_commit => FND_API.G_FALSE
3402 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
3403 , p_Dimension_Rec => l_bis_dimension_rec
3404 , x_return_status => x_return_status
3405 , x_error_Tbl => l_error_tbl
3406 );
3407 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3408 IF (l_error_tbl.COUNT > 0) THEN
3409 x_msg_data := l_error_tbl(l_error_tbl.COUNT).Error_Description;
3410 IF(INSTR(x_msg_data, ' ') = 0 ) THEN
3411 FND_MESSAGE.SET_NAME('BIS',x_msg_data);
3412 FND_MSG_PUB.ADD;
3413 x_msg_data := NULL;
3414 END IF;
3415 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3416 END IF;
3417
3418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3419 END IF;
3420 l_delete_count := l_delete_count + 1;
3421 ELSE
3422 l_delete := TRUE;
3423 END IF;
3424
3425 IF (cr_bsc_dimension_id%ISOPEN) THEN
3426 CLOSE cr_bsc_dimension_id;
3427 END IF;
3428 OPEN cr_bsc_dimension_id;
3429 FETCH cr_bsc_dimension_id
3430 INTO l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id
3431 , l_bsc_dimension_rec.Bsc_Dim_Level_Group_Name;
3432 IF (cr_bsc_dimension_id%ROWCOUNT = 0) THEN
3433 l_delete := FALSE;
3434 END IF;
3435 CLOSE cr_bsc_dimension_id;
3436
3437 --assign values to bsc records
3438
3439 l_bsc_dimension_rec.Bsc_Dim_Level_Group_short_name := p_dim_short_name;
3440
3441 l_regions := BSC_UTILITY.Is_Dim_In_AKReport(p_dim_short_name, BSC_UTILITY.c_DIMENSION);
3442 IF(l_regions IS NOT NULL) THEN
3443 FND_MESSAGE.SET_NAME('BIS','BIS_DIM_RPTASSOC_ERROR');
3444 FND_MESSAGE.SET_TOKEN('DIM_NAME', l_bsc_dimension_rec.Bsc_Dim_Level_Group_Name);
3445 FND_MESSAGE.SET_TOKEN('REPORTS_ASSOC', l_regions);
3446 FND_MSG_PUB.ADD;
3447 RAISE FND_API.G_EXC_ERROR;
3448 END IF;
3449
3450 IF (l_delete) THEN
3451 -- START: Granular Locking to Lock Dimension Group when it is being deleted.
3452 SELECT COUNT(1) INTO l_count
3453 FROM BSC_KPI_DIM_GROUPS
3454 WHERE dim_group_id = l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id;
3455 IF (l_count <> 0) THEN
3456 FND_MESSAGE.SET_NAME('BSC','BSC_NOT_DELETE_DIMENSIONS');
3457 FND_MESSAGE.SET_TOKEN('SHORT_NAME', l_bsc_dimension_rec.Bsc_Dim_Level_Group_Name);
3458 FND_MSG_PUB.ADD;
3459 RAISE FND_API.G_EXC_ERROR;
3460 END IF;
3461 BSC_BIS_LOCKS_PUB.Lock_Dim_Group
3462 ( p_dim_group_id => l_bsc_dimension_rec.Bsc_Dim_Level_Group_Id
3463 ,p_time_stamp => NULL -- Granular Locking
3464 ,x_return_status => x_return_status
3465 ,x_msg_count => x_msg_count
3466 ,x_msg_data => x_msg_data
3467 );
3468 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3469
3470 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3471 END IF;
3472 -- END: Granular Locking to Lock Dimension Group when it is being deleted.
3473 -- Aditya added incremental changes
3474 -- This will create a structural changes to all the KPIs that are currently using the
3475 -- Groups, which is going to be deleted.
3476 BSC_DESIGNER_PVT.Dimension_Change(p_dim_short_name, BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure);
3477 -- End incremental changes.
3478 BSC_DIMENSION_GROUPS_PUB.Delete_Dimension_Group
3479 ( p_commit => FND_API.G_FALSE
3480 , p_Dim_Grp_Rec => l_bsc_dimension_rec
3481 , x_return_status => x_return_status
3482 , x_msg_count => x_msg_count
3483 , x_msg_data => x_msg_data
3484 );
3485 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3486
3487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3488 END IF;
3489 l_delete_count := l_delete_count + 1;
3490 ELSE
3491 l_delete := TRUE;
3492 END IF;
3493 IF (l_delete_count = 0) THEN
3494 FND_MESSAGE.SET_NAME('BSC','BSC_MUSER_DELETE_MESSAGE');
3495 FND_MESSAGE.SET_TOKEN('TYPE', BSC_APPS.Get_Lookup_Value('BSC_UI_COMMON', 'EDW_DIMENSION'), TRUE);
3496 FND_MSG_PUB.ADD;
3497 RAISE FND_API.G_EXC_ERROR;
3498 END IF;
3499 IF (p_commit = FND_API.G_TRUE) THEN
3500 COMMIT;
3501
3502 END IF;
3503 x_return_status := FND_API.G_RET_STS_SUCCESS;
3504
3505 EXCEPTION
3506 WHEN FND_API.G_EXC_ERROR THEN
3507 IF (cr_bis_dim_short_name%ISOPEN) THEN
3508 CLOSE cr_bis_dim_short_name;
3509 END IF;
3510 IF (cr_bsc_dimension_id%ISOPEN) THEN
3511 CLOSE cr_bsc_dimension_id;
3512 END IF;
3513 ROLLBACK TO DeleteBSCDimensionsPMD;
3514 IF (x_msg_data IS NULL) THEN
3515 FND_MSG_PUB.Count_And_Get
3516 ( p_encoded => FND_API.G_FALSE
3517 , p_count => x_msg_count
3518 , p_data => x_msg_data
3519 );
3520 END IF;
3521
3522 x_return_status := FND_API.G_RET_STS_ERROR;
3523 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3524 IF (cr_bis_dim_short_name%ISOPEN) THEN
3525 CLOSE cr_bis_dim_short_name;
3526 END IF;
3527 IF (cr_bsc_dimension_id%ISOPEN) THEN
3528 CLOSE cr_bsc_dimension_id;
3529 END IF;
3530 ROLLBACK TO DeleteBSCDimensionsPMD;
3531 IF (x_msg_data IS NULL) THEN
3532 FND_MSG_PUB.Count_And_Get
3533 ( p_encoded => FND_API.G_FALSE
3534 , p_count => x_msg_count
3535 , p_data => x_msg_data
3536 );
3537 END IF;
3538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3539
3540 WHEN NO_DATA_FOUND THEN
3541 IF (cr_bis_dim_short_name%ISOPEN) THEN
3542 CLOSE cr_bis_dim_short_name;
3543 END IF;
3544 IF (cr_bsc_dimension_id%ISOPEN) THEN
3545 CLOSE cr_bsc_dimension_id;
3546 END IF;
3547 ROLLBACK TO DeleteBSCDimensionsPMD;
3548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3549 IF (x_msg_data IS NOT NULL) THEN
3550 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Delete_Dimension ';
3551 ELSE
3552 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Delete_Dimension ';
3553 END IF;
3554
3555 WHEN OTHERS THEN
3556 IF (cr_bis_dim_short_name%ISOPEN) THEN
3557 CLOSE cr_bis_dim_short_name;
3558 END IF;
3559 IF (cr_bsc_dimension_id%ISOPEN) THEN
3560 CLOSE cr_bsc_dimension_id;
3561 END IF;
3562 ROLLBACK TO DeleteBSCDimensionsPMD;
3563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3564 IF (x_msg_data IS NOT NULL) THEN
3565 x_msg_data := x_msg_data||' -> BSC_BIS_DIMENSION_PUB.Delete_Dimension ';
3566 ELSE
3567 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Delete_Dimension ';
3568 END IF;
3569
3570 END Delete_Dimension;
3571 /*******************************************************************************
3572 ********************************************************************************/
3573
3574 FUNCTION Is_More
3575 ( p_dim_obj_short_names IN OUT NOCOPY VARCHAR2
3576 , p_dim_obj_name OUT NOCOPY VARCHAR2
3577 ) RETURN BOOLEAN
3578 IS
3579 l_pos_ids NUMBER;
3580 l_pos_rel_types NUMBER;
3581 l_pos_rel_columns NUMBER;
3582 BEGIN
3583 IF (p_dim_obj_short_names IS NOT NULL) THEN
3584 l_pos_ids := INSTR(p_dim_obj_short_names, ',');
3585 IF (l_pos_ids > 0) THEN
3586 p_dim_obj_name := TRIM(SUBSTR(p_dim_obj_short_names, 1, l_pos_ids - 1));
3587
3588 p_dim_obj_short_names := TRIM(SUBSTR(p_dim_obj_short_names, l_pos_ids + 1));
3589 ELSE
3590 p_dim_obj_name := TRIM(p_dim_obj_short_names);
3591
3592 p_dim_obj_short_names := NULL;
3593 END IF;
3594 RETURN TRUE;
3595 ELSE
3596 RETURN FALSE;
3597 END IF;
3598 END Is_More;
3599 /*******************************************************************************
3600 ********************************************************************************/
3601 FUNCTION Get_Dimension_Source
3602 (
3603 p_short_Name IN VARCHAR2
3604 ) RETURN VARCHAR2 IS
3605 l_Data_Source BSC_SYS_DIM_LEVELS_B.Source%TYPE := NULL;
3606
3607 CURSOR c_Dim_Obj_Data_Source IS
3608 SELECT VL.Source
3609 FROM BSC_SYS_DIM_GROUPS_TL TL
3610 , BSC_SYS_DIM_LEVELS_BY_GROUP GP
3611 , BSC_SYS_DIM_LEVELS_B VL
3612 WHERE TL.Dim_Group_Id = GP.Dim_Group_Id
3613 AND GP.Dim_Level_Id = VL.Dim_Level_Id
3614 AND TL.Short_Name = p_short_Name;
3615 BEGIN
3616 IF (p_short_Name IS NULL) THEN
3617 RETURN NULL;
3618 ELSIF (BSC_BIS_DIMENSION_PUB.Unassigned_Dim = p_short_Name) THEN
3619 RETURN 'PMF';
3620 ELSE
3621 IF (c_Dim_Obj_Data_Source%ISOPEN) THEN
3622 CLOSE c_Dim_Obj_Data_Source;
3623 END IF;
3624 OPEN c_Dim_Obj_Data_Source;
3625 FETCH c_Dim_Obj_Data_Source INTO l_Data_Source;
3626 CLOSE c_Dim_Obj_Data_Source;
3627 END IF;
3628 RETURN l_Data_Source;
3629 EXCEPTION
3630 WHEN OTHERS THEN
3631 IF (c_Dim_Obj_Data_Source%ISOPEN) THEN
3632 CLOSE c_Dim_Obj_Data_Source;
3633 END IF;
3634 RETURN NULL;
3635 END Get_Dimension_Source;
3636 /*******************************************************************************
3637 ********************************************************************************/
3638 -- Modified for Bug#3739872
3639 FUNCTION Attmpt_Recr_View
3640 ( p_dim_lvl_shrt_name VARCHAR2
3641 , x_dim_lvl_name OUT NOCOPY VARCHAR2
3642 ) RETURN BOOLEAN IS
3643 l_return_status VARCHAR2(2);
3644 l_msg_count NUMBER;
3645 l_msg_data VARCHAR2(2000);
3646 BEGIN
3647 -- Underlying View does not exist.
3648 IF (BSC_BIS_DIM_OBJ_PUB.Get_Table_Type_Value(p_dim_lvl_shrt_name) = -1) THEN
3649 BSC_BIS_DIM_OBJ_PUB.Refresh_BSC_PMF_Dim_View
3650 ( p_Short_Name => p_dim_lvl_shrt_name
3651 , x_return_status => l_return_status
3652 , x_msg_count => l_msg_count
3653 , x_msg_data => l_msg_data
3654 );
3655 -- If the view has still not been created, then we need to return FALSE
3656 IF(BSC_BIS_DIM_OBJ_PUB.Get_Table_Type_Value(p_dim_lvl_shrt_name) = -1) THEN
3657 RETURN FALSE;
3658 END IF;
3659 END IF;
3660
3661 RETURN TRUE;
3662 EXCEPTION
3663 WHEN OTHERS THEN
3664 RETURN FALSE;
3665 END Attmpt_Recr_View;
3666 /*******************************************************************************
3667 ********************************************************************************/
3668 PROCEDURE Get_Lvl_Dtls
3669 ( p_dim_lvl_shrt_name VARCHAR2
3670 , x_source OUT NOCOPY VARCHAR2
3671 , x_dim_lvl_name OUT NOCOPY VARCHAR2
3672 , x_dim_lvl_view_name OUT NOCOPY VARCHAR2
3673 , x_dim_lvl_pk_key OUT NOCOPY VARCHAR2
3674 , x_dim_lvl_name_col OUT NOCOPY VARCHAR2
3675 , x_return_status OUT NOCOPY VARCHAR2
3676 , x_msg_count OUT NOCOPY NUMBER
3677 , x_msg_data OUT NOCOPY VARCHAR2
3678 ) IS
3679 BEGIN
3680 IF (p_dim_lvl_shrt_name IS NOT NULL) THEN
3681 SELECT source, name, level_values_view_name, 'ID', 'value'
3682 INTO x_source
3683 , x_dim_lvl_name
3684 , x_dim_lvl_view_name
3685 , x_dim_lvl_pk_key
3686 , x_dim_lvl_name_col
3687 FROM bis_levels_vl
3688 WHERE UPPER(short_name) = UPPER(p_dim_lvl_shrt_name);
3689 END IF;
3690 EXCEPTION
3691 WHEN OTHERS THEN
3692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3693 IF (x_msg_data IS NULL) THEN
3694 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Get_Lvl_Dtls ';
3695 END IF;
3696 END Get_Lvl_Dtls;
3697 /*******************************************************************************
3698 ********************************************************************************/
3699 PROCEDURE Get_Spec_Edw_Dtls
3700 ( p_dim_lvl_shrt_name VARCHAR2
3701 , x_dim_lvl_view_name OUT NOCOPY VARCHAR2
3702 , x_dim_lvl_pk_key OUT NOCOPY VARCHAR2
3703 , x_dim_lvl_name_col OUT NOCOPY VARCHAR2
3704 , x_return_status OUT NOCOPY VARCHAR2
3705 , x_msg_count OUT NOCOPY NUMBER
3706 , x_msg_data OUT NOCOPY VARCHAR2
3707 ) IS
3708 l_dim_lvl_sql VARCHAR2(2000);
3709 l_dim_lvl_shrt_name VARCHAR2(30);
3710 l_dim_lvl_prefix VARCHAR2(30);
3711 BEGIN
3712 l_dim_lvl_sql := 'SELECT dim.DIM_NAME dimshortname, lvl.LEVEL_PREFIX prefix '||
3713 ' FROM '||
3714 ' edw_dimensions_md_v dim, edw_levels_md_v lvl '||
3715 ' WHERE '||
3716 ' lvl.DIM_ID = dim.DIM_ID AND '||
3717 ' lvl.LEVEL_NAME = :1 ';
3718 BEGIN
3719 EXECUTE IMMEDIATE l_dim_lvl_sql
3720 INTO l_dim_lvl_shrt_name, l_dim_lvl_prefix
3721 USING p_dim_lvl_shrt_name;
3722 IF (INSTR(l_dim_lvl_shrt_name,'EDW_GL_ACCT') <> 0) THEN -- return TRUE case
3723 x_dim_lvl_view_name := p_dim_lvl_shrt_name;
3724 x_dim_lvl_pk_key := l_dim_lvl_prefix||'_NAME';
3725 x_dim_lvl_name_col := l_dim_lvl_prefix||'_NAME';
3726 END IF;
3727 EXCEPTION
3728 WHEN NO_DATA_FOUND THEN
3729 x_dim_lvl_view_name := NULL;
3730 x_dim_lvl_pk_key := NULL;
3731 x_dim_lvl_name_col := NULL;
3732 END;
3733 EXCEPTION
3734 WHEN OTHERS THEN
3735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3736 IF (x_msg_data IS NULL) THEN
3737 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Get_Spec_Edw_Dtls ';
3738 END IF;
3739 END Get_Spec_Edw_Dtls;
3740 /*******************************************************************************
3741 ********************************************************************************/
3742 PROCEDURE Get_Gene_Edw_Dtls
3743 ( p_dim_lvl_shrt_name VARCHAR2
3744 , x_dim_lvl_view_name IN OUT NOCOPY VARCHAR2
3745 , x_dim_lvl_pk_key OUT NOCOPY VARCHAR2
3746 , x_dim_lvl_name_col OUT NOCOPY VARCHAR2
3747 , x_return_status OUT NOCOPY VARCHAR2
3748 , x_msg_count OUT NOCOPY NUMBER
3749 , x_msg_data OUT NOCOPY VARCHAR2
3750 ) IS
3751 l_edw_sql VARCHAR2(2000);
3752 TYPE Recdc_value IS REF CURSOR;
3753 dl_value Recdc_value;
3754 BEGIN
3755 IF (x_dim_lvl_view_name IS NOT NULL) THEN
3756 x_dim_lvl_view_name := p_dim_lvl_shrt_name||'_LTC';
3757 END IF;
3758 l_edw_sql := ' select distinct level_table_col_name ' ||
3759 ' from edw_level_Table_atts_md_v ' ||
3760 ' where key_type=''UK'' and ' ||
3761 ' upper(level_Table_name) = upper(:1) and ' ||
3762 ' upper(level_table_col_name) like ''%PK_KEY%''';
3763
3764 OPEN dl_value FOR l_edw_sql USING x_dim_lvl_view_name;
3765 FETCH dl_value INTO x_dim_lvl_pk_key;
3766 CLOSE dl_value;
3767
3768 l_edw_sql := 'select level_table_col_name ' ||
3769 ' from edw_level_Table_atts_md_v ' ||
3770 ' where upper(level_Table_name) = upper(:1) and ' ||
3771 ' (upper(level_table_col_name) like ''%DESCRIPTION%'' or ' ||
3772 ' upper(level_table_col_name) like ''NAME%'') ';
3773
3774 OPEN dl_value FOR l_edw_sql USING x_dim_lvl_view_name;
3775 FETCH dl_value INTO x_dim_lvl_name_col;
3776 CLOSE dl_value;
3777 EXCEPTION
3778 WHEN OTHERS THEN
3779 IF (dl_value%ISOPEN) THEN
3780 CLOSE dl_value;
3781 END IF;
3782 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3783 IF (x_msg_data IS NULL) THEN
3784 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.Get_Gene_Edw_Dtls ';
3785 END IF;
3786 END Get_Gene_Edw_Dtls;
3787
3788 /***************************************************************************
3789 WARNING: -
3790 This function will return false if any changes Dimension-Objects and
3791 Dimension Property will result in color changes. This is designed to
3792 fulfil the UI screen need and not a generic function so it should not
3793 be called internally from any other APIs without proper impact analysis.
3794 ****************************************************************************/
3795 FUNCTION is_KPI_Flag_For_DimProp_Change
3796 ( p_dim_short_name IN VARCHAR2
3797 , p_dim_Obj_Short_Name IN VARCHAR2
3798 , p_Default_Value IN VARCHAR2
3799 ) RETURN VARCHAR2 IS
3800 l_Msg_Data VARCHAR2(32000);
3801 l_msg_count NUMBER;
3802
3803 l_default_Value BSC_SYS_DIM_LEVELS_BY_GROUP.Default_Value%TYPE;
3804 l_new_default_Value BSC_SYS_DIM_LEVELS_BY_GROUP.Default_Value%TYPE;
3805 l_Prod_Mode BSC_SYS_INIT.Property_Value%TYPE;
3806 l_Struct_Flag BOOLEAN := FALSE;
3807 l_kpi_names VARCHAR2(32000);
3808 l_Dim_Grp_Id BSC_SYS_DIM_GROUPS_VL.Dim_Group_Id%TYPE;
3809 l_Source BSC_SYS_DIM_LEVELS_B.Source%TYPE;
3810 l_indicator_list VARCHAR2(32000);
3811 l_obj_name bsc_kpis_vl.name%TYPE;
3812 l_ind bsc_kpis_vl.indicator%TYPE;
3813 l_sql VARCHAR2(2000);
3814 l_is_color_change NUMBER;
3815
3816 CURSOR c_default_Value IS
3817 SELECT A.Default_Value
3818 , A.Dim_Group_ID
3819 , C.Source
3820 FROM BSC_SYS_DIM_LEVELS_BY_GROUP A
3821 , BSC_SYS_DIM_GROUPS_VL B
3822 , BSC_SYS_DIM_LEVELS_B C
3823 WHERE A.Dim_Group_Id = B.Dim_Group_Id
3824 AND A.Dim_Level_Id = C.Dim_Level_Id
3825 AND B.Short_Name = p_Dim_Short_Name
3826 AND C.Short_Name = p_dim_Obj_Short_Name;
3827
3828 CURSOR c_dim_set_kpi IS
3829 SELECT a.indicator indicator,
3830 a.kpi_measure_id,
3831 c.color_by_total
3832 FROM bsc_db_dataset_dim_sets_v a,
3833 bsc_kpi_dim_levels_vl b,
3834 bsc_kpi_measure_props c
3835 WHERE a.indicator =b.indicator
3836 AND a.dim_set_id =b.dim_set_id
3837 AND c.indicator = a.indicator
3838 AND c.kpi_measure_id = a.kpi_measure_id
3839 AND b.level_shortname = p_dim_Obj_Short_Name;
3840
3841 TYPE ref_cursor IS REF CURSOR;
3842 ref_cur ref_cursor;
3843 BEGIN
3844
3845 FND_MSG_PUB.Initialize;
3846 IF (p_dim_short_name IS NULL) THEN
3847 RETURN NULL;
3848 END IF;
3849 SELECT Property_Value INTO l_Prod_Mode
3850 FROM BSC_SYS_INIT
3851 WHERE PROPERTY_CODE ='SYSTEM_STAGE';
3852 IF (l_Prod_Mode <> '2') THEN
3853 RETURN NULL;
3854 END IF;
3855 IF ((p_Default_Value IS NULL) OR ((p_Default_Value <> 'C') AND (p_Default_Value <> 'T'))) THEN
3856 l_new_default_Value := 'T';
3857 ELSE
3858 l_new_default_Value := p_Default_Value;
3859 END IF;
3860 OPEN c_default_Value;
3861 FETCH c_default_Value INTO l_default_Value, l_Dim_Grp_Id, l_Source;
3862 CLOSE c_default_Value;
3863
3864 IF ((l_Source IS NULL) OR (l_Source <> 'BSC')) THEN
3865 RETURN NULL;
3866 END IF;
3867
3868 IF (l_default_Value IS NULL) THEN
3869 l_default_Value := 'T';
3870 END IF;
3871
3872 FOR cd IN c_dim_set_kpi LOOP
3873 --If KPI comparison setting is ALL
3874 l_is_color_change := is_color_change_required (l_default_Value, l_new_default_Value, cd.indicator, cd.kpi_measure_id);
3875
3876 IF (l_is_color_change =1) THEN
3877 IF (l_indicator_list IS NULL) THEN
3878 l_indicator_list := cd.indicator;
3879 ELSE
3880 l_indicator_list := l_indicator_list || ',' || cd.indicator;
3881 END IF;
3882 END IF;
3883
3884 END LOOP;
3885
3886
3887 IF (l_indicator_list IS NOT NULL) THEN
3888 IF(ref_cur%ISOPEN) THEN
3889 CLOSE ref_cur;
3890 END IF;
3891
3892 l_sql := 'SELECT name, indicator FROM bsc_kpis_vl WHERE indicator IN (' || l_indicator_list || ') AND prototype_flag = 0 AND source_indicator IS NULL';
3893
3894 OPEN ref_cur FOR l_sql;
3895 LOOP
3896 FETCH ref_cur INTO l_obj_name, l_ind;
3897 EXIT WHEN ref_cur%NOTFOUND;
3898 IF (l_kpi_names IS NULL) THEN
3899 l_kpi_names := l_obj_name || '[' || l_ind || ']';
3900 ELSE
3901 l_kpi_names := l_kpi_names ||', '||l_obj_name || '[' || l_ind || ']';
3902 END IF;
3903 END LOOP;
3904 IF(ref_cur%ISOPEN) THEN
3905 CLOSE ref_cur;
3906 END IF;
3907 END IF;
3908
3909 IF (l_kpi_names IS NOT NULL) THEN
3910 FND_MESSAGE.SET_NAME('BSC','BSC_PMD_KPI_COLOR_INVALID');
3911 FND_MESSAGE.SET_TOKEN('INDICATORS', l_kpi_names);
3912 FND_MSG_PUB.ADD;
3913 RAISE FND_API.G_EXC_ERROR;
3914 END IF;
3915
3916 RETURN NULL;
3917 EXCEPTION
3918 WHEN FND_API.G_EXC_ERROR THEN
3919 IF (c_default_Value%ISOPEN) THEN
3920 CLOSE c_default_Value;
3921 END IF;
3922 IF(ref_cur%ISOPEN) THEN
3923 CLOSE ref_cur;
3924 END IF;
3925 IF (l_Msg_Data IS NULL) THEN
3926 FND_MSG_PUB.Count_And_Get
3927 ( p_encoded => FND_API.G_FALSE
3928 , p_count => l_msg_count
3929 , p_data => l_Msg_Data
3930 );
3931 END IF;
3932
3933 RETURN l_Msg_Data;
3934 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3935 IF (c_default_Value%ISOPEN) THEN
3936 CLOSE c_default_Value;
3937 END IF;
3938 IF(ref_cur%ISOPEN) THEN
3939 CLOSE ref_cur;
3940 END IF;
3941
3942 IF (l_Msg_Data IS NULL) THEN
3943 FND_MSG_PUB.Count_And_Get
3944 ( p_encoded => FND_API.G_FALSE
3945 , p_count => l_msg_count
3946 , p_data => l_Msg_Data
3947 );
3948 END IF;
3949
3950 RETURN l_Msg_Data;
3951 WHEN OTHERS THEN
3952 IF (c_default_Value%ISOPEN) THEN
3953 CLOSE c_default_Value;
3954 END IF;
3955 IF(ref_cur%ISOPEN) THEN
3956 CLOSE ref_cur;
3957 END IF;
3958 RETURN NULL;
3959 END is_KPI_Flag_For_DimProp_Change;
3960
3961 /********************************************************************************
3962 WARNING : -
3963 This function will return false if any changes Dimension-Objects within a Dimension
3964 will result in structural changes. This is designed to fulfil the UI screen
3965 need and not a generic function so it should not be called internally from any
3966 other APIs without proper impact analysis.
3967 ********************************************************************************/
3968 FUNCTION is_KPI_Flag_For_Dimension
3969 ( p_Dim_Short_Name IN VARCHAR2
3970 , p_Dim_Obj_Short_Names IN VARCHAR2
3971 ) RETURN VARCHAR2 IS
3972 l_Msg_Data VARCHAR2(32000);
3973 l_msg_count NUMBER;
3974
3975 l_Dim_Obj_Name BSC_SYS_DIM_LEVELS_B.short_name%TYPE;
3976 l_Dim_Obj_Old_Name BSC_SYS_DIM_LEVELS_B.short_name%TYPE;
3977 l_Dim_Grp_Id BSC_SYS_DIM_GROUPS_VL.Dim_Group_Id%TYPE;
3978 l_Source BSC_SYS_DIM_LEVELS_B.Source%TYPE;
3979
3980 l_old_dim_objects VARCHAR2(8000) := NULL;
3981 l_temp_dim_objcts VARCHAR2(8000);
3982 l_temp_var VARCHAR2(8000);
3983 l_kpi_names VARCHAR2(32000);
3984
3985 l_passed_index NUMBER := 0;
3986 l_Struct_Flag BOOLEAN := FALSE;
3987 l_flag BOOLEAN;
3988
3989 CURSOR c_Dim_Old_Objects IS
3990 SELECT C.Short_Name
3991 , B.Dim_Level_Index
3992 , A.Dim_Group_Id
3993 , C.Source
3994 FROM BSC_SYS_DIM_GROUPS_VL A
3995 , BSC_SYS_DIM_LEVELS_BY_GROUP B
3996 , BSC_SYS_DIM_LEVELS_B C
3997 WHERE A.Dim_Group_Id = B.Dim_Group_Id
3998 AND C.Dim_Level_Id = B.Dim_Level_Id
3999 AND A.Short_Name = p_Dim_Short_Name
4000 ORDER BY B.Dim_Level_Index;
4001
4002 CURSOR c_dim_set_kpi IS
4003 SELECT DISTINCT B.Name||'['||B.Indicator||']' INDICATOR
4004 FROM BSC_KPI_DIM_GROUPS A
4005 , BSC_KPIS_VL B
4006 , BSC_SYS_DIM_GROUPS_VL C
4007 WHERE A.INDICATOR = B.INDICATOR
4008 AND B.share_flag <> 2
4009 AND A.Dim_Group_Id = C.Dim_Group_Id
4010 AND C.Short_Name = p_Dim_Short_Name;
4011 BEGIN
4012
4013 FND_MSG_PUB.Initialize;
4014 IF (p_Dim_Short_Name IS NULL) THEN
4015 RETURN NULL;
4016 END IF;
4017 IF (NOT BSC_UTILITY.isBscInProductionMode()) THEN
4018 RETURN NULL;
4019 END IF;
4020
4021 FOR cd IN c_Dim_Old_Objects LOOP
4022 l_flag := TRUE;
4023 l_temp_var := p_Dim_Obj_Short_Names;
4024 l_passed_index := 0;
4025 l_Source := cd.Source;
4026 IF (l_Source <> 'BSC') THEN
4027 EXIT;
4028 END IF;
4029 IF (l_old_dim_objects IS NULL) THEN
4030 l_Dim_Grp_Id := cd.Dim_Group_ID;
4031 l_old_dim_objects := cd.Short_Name;
4032 ELSE
4033 l_old_dim_objects := l_old_dim_objects||','||cd.Short_Name;
4034 END IF;
4035 WHILE (is_more(p_dim_obj_short_names => l_temp_var
4036 , p_dim_obj_name => l_dim_obj_name
4037 )) LOOP
4038 IF ((l_dim_obj_name = cd.Short_Name) AND (cd.Dim_Level_Index = l_passed_index)) THEN
4039 l_flag := FALSE;
4040 EXIT;
4041 END IF;
4042 l_passed_index := l_passed_index + 1;
4043 END LOOP;
4044 IF (l_flag) THEN
4045 l_Struct_Flag := TRUE;
4046 EXIT;
4047 END IF;
4048 END LOOP;
4049 IF ((l_Source IS NOT NULL) AND (l_Source <> 'BSC')) THEN
4050 RETURN NULL;
4051 END IF;
4052
4053 IF (NOT l_Struct_Flag) THEN
4054 l_temp_var := p_Dim_Obj_Short_Names;
4055 WHILE (is_more(p_dim_obj_short_names => l_temp_var
4056 , p_dim_obj_name => l_dim_obj_name
4057 )) LOOP
4058 l_flag := TRUE;
4059 l_temp_dim_objcts := l_old_dim_objects;
4060 WHILE (is_more(p_dim_obj_short_names => l_temp_dim_objcts
4061 , p_dim_obj_name => l_Dim_Obj_Old_Name
4062 )) LOOP
4063 IF (l_Dim_Obj_Old_Name = l_dim_obj_name) THEN
4064 l_flag := FALSE;
4065 EXIT;
4066 END IF;
4067 END LOOP;
4068 IF (l_flag) THEN
4069
4070 l_Struct_Flag := TRUE;
4071 EXIT;
4072 END IF;
4073 END LOOP;
4074 END IF;
4075 IF (l_Struct_Flag) THEN
4076 FOR cd IN c_dim_set_kpi LOOP
4077 IF (l_kpi_names IS NULL) THEN
4078 l_kpi_names := cd.Indicator;
4079 ELSE
4080 l_kpi_names := l_kpi_names||', '||cd.Indicator;
4081 END IF;
4082 END LOOP;
4083 END IF;
4084
4085 IF ((l_Struct_Flag) AND (l_kpi_names IS NOT NULL)) THEN
4086 FND_MESSAGE.SET_NAME('BSC','BSC_PMD_KPI_STRUCT_INVALID');
4087 FND_MESSAGE.SET_TOKEN('INDICATORS', l_kpi_names);
4088 FND_MSG_PUB.ADD;
4089 RAISE FND_API.G_EXC_ERROR;
4090 END IF;
4091
4092 RETURN NULL;
4093 EXCEPTION
4094 WHEN FND_API.G_EXC_ERROR THEN
4095 IF (l_Msg_Data IS NULL) THEN
4096 FND_MSG_PUB.Count_And_Get
4097 ( p_encoded => FND_API.G_FALSE
4098 , p_count => l_msg_count
4099 , p_data => l_Msg_Data
4100 );
4101 END IF;
4102
4103 RETURN l_Msg_Data;
4104 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4105 IF (l_Msg_Data IS NULL) THEN
4106 FND_MSG_PUB.Count_And_Get
4107 ( p_encoded => FND_API.G_FALSE
4108 , p_count => l_msg_count
4109 , p_data => l_Msg_Data
4110 );
4111 END IF;
4112
4113 RETURN l_Msg_Data;
4114 WHEN OTHERS THEN
4115
4116 RETURN NULL;
4117 END is_KPI_Flag_For_Dimension;
4118 --=============================================================================
4119 FUNCTION is_config_impact_dim
4120 ( p_Dim_Short_Name IN VARCHAR2
4121 , p_Dim_Obj_Short_Names IN VARCHAR2
4122 ) RETURN VARCHAR2 IS
4123 l_dim_obj_short_names VARCHAR2(32000);
4124 l_count_dim_objs NUMBER := 0;
4125 l_count_temp NUMBER;
4126 l_total_count NUMBER := 0;
4127 l_dim_short_name VARCHAR2(32000);
4128 l_kpi_id NUMBER;
4129 l_dim_set_id NUMBER;
4130 l_minus_count NUMBER;
4131 l_plus_count NUMBER;
4132 l_Msg_Data VARCHAR2(32000);
4133 l_msg_count NUMBER;
4134 l_found_dimobj BOOLEAN;
4135 l_dimobj_temp VARCHAR2(32000);
4136 TYPE index_by_table IS Record
4137 (
4138 p_no_dim_object VARCHAR2(32000)
4139 );
4140 TYPE index_by_table_type IS TABLE OF index_by_table INDEX BY BINARY_INTEGER;
4141 TYPE index_by_table_kpi IS Record
4142 (
4143 kpi_id NUMBER
4144 , dim_set_id NUMBER
4145 );
4146 TYPE index_by_table_type_kpi IS TABLE OF index_by_table_kpi INDEX BY BINARY_INTEGER;
4147 dim_objs_array index_by_table_type;
4148 kpi_dim_set_array index_by_table_type_kpi;
4149 dim_objs_in_dim index_by_table_type;
4150
4151 CURSOR cr_kpidimset_dim IS
4152 SELECT A. INDICATOR
4153 , A.DIM_SET_ID
4154 FROM BSC_KPI_DIM_GROUPS A
4155 , BSC_SYS_DIM_GROUPS_VL B
4156 WHERE A.DIM_GROUP_ID = B.DIM_GROUP_ID
4157 AND B.SHORT_NAME = p_Dim_Short_Name;
4158
4159 CURSOR cr_dimobj_in_dimset IS
4160 SELECT B.SHORT_NAME
4161 FROM BSC_SYS_DIM_LEVELS_B B
4162 ,BSC_KPI_DIM_LEVEL_PROPERTIES KDL
4163 WHERE B.DIM_LEVEL_ID = KDL.DIM_LEVEL_ID
4164 AND KDL.indicator = l_kpi_id
4165 AND KDL.dim_set_id = l_dim_set_id;
4166
4167 CURSOR cr_dimobjs_in_dim IS
4168 SELECT BL.SHORT_NAME
4169 FROM BSC_SYS_DIM_LEVELS_BY_GROUP B
4170 ,BSC_SYS_DIM_GROUPS_VL VL
4171 ,BSC_SYS_DIM_LEVELS_B BL
4172 WHERE VL.DIM_GROUP_ID = B.DIM_GROUP_ID
4173 AND BL.DIM_LEVEL_ID = B.DIM_LEVEL_ID
4174 AND VL.SHORT_NAME = p_Dim_Short_Name;
4175
4176 i NUMBER;
4177 l_no_dim_object VARCHAR2(32000);
4178
4179 BEGIN
4180 FND_MSG_PUB.Initialize;
4181 l_dim_obj_short_names := p_Dim_Obj_Short_Names;
4182 WHILE(Is_More(p_dim_obj_short_names=>l_dim_obj_short_names,p_dim_obj_name=> l_dim_short_name)) LOOP
4183 l_count_dim_objs:= l_count_dim_objs +1;
4184 END LOOP;
4185
4186 IF(l_count_dim_objs > BSC_BIS_KPI_MEAS_PUB.Config_Limit_Dim) THEN
4187 FND_MESSAGE.SET_NAME('BSC','BSC_PMD_IMPACT_KPI_SPACE');
4188 FND_MESSAGE.SET_TOKEN('CONTINUE', BSC_APPS.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'YES'), TRUE);
4189 FND_MESSAGE.SET_TOKEN('CANCEL', BSC_APPS.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'NO'), TRUE);
4190 FND_MSG_PUB.ADD;
4191 RAISE FND_API.G_EXC_ERROR;
4192 END IF;
4193
4194 OPEN cr_dimobjs_in_dim;
4195 -- bug#3405498 meastmon 28-jan-2004: The following is not supported in 8i
4196 --FETCH cr_dimobjs_in_dim BULK COLLECT INTO dim_objs_in_dim;
4197 dim_objs_in_dim.delete;
4198 i := 0;
4199 LOOP
4200 FETCH cr_dimobjs_in_dim INTO l_no_dim_object;
4201 EXIT WHEN cr_dimobjs_in_dim%NOTFOUND;
4202 i:= i+1;
4203 dim_objs_in_dim(i).p_no_dim_object := l_no_dim_object;
4204 END LOOP;
4205 CLOSE cr_dimobjs_in_dim;
4206
4207
4208 IF(p_Dim_Obj_Short_Names IS NOT NULL) THEN
4209 OPEN cr_kpidimset_dim ;
4210 -- bug#3405498 meastmon 28-jan-2004: The following is not supported in 8i
4211 --FETCH cr_kpidimset_dim BULK COLLECT INTO kpi_dim_set_array;
4212 kpi_dim_set_array.delete;
4213 i:= 0;
4214 LOOP
4215 FETCH cr_kpidimset_dim INTO l_kpi_id, l_dim_set_id;
4216 EXIT WHEN cr_kpidimset_dim%NOTFOUND;
4217 i:= i+1;
4218 kpi_dim_set_array(i).kpi_id := l_kpi_id;
4219 kpi_dim_set_array(i).dim_set_id := l_dim_set_id;
4220 END LOOP;
4221 CLOSE cr_kpidimset_dim;
4222
4223 FOR index_loop IN 1..(kpi_dim_set_array.COUNT) LOOP
4224 l_kpi_id := kpi_dim_set_array(index_loop).kpi_id;
4225 l_dim_set_id := kpi_dim_set_array(index_loop).dim_set_id;
4226 l_count_temp := l_count_dim_objs;
4227 l_plus_count := 0;
4228 l_minus_count := 0;
4229 IF(cr_dimobj_in_dimset%ISOPEN) THEN
4230 CLOSE cr_dimobj_in_dimset;
4231 END IF;
4232 OPEN cr_dimobj_in_dimset;
4233 -- bug#3405498 meastmon 28-jan-2004: The following is not supported in 8i
4234 --FETCH cr_dimobj_in_dimset BULK COLLECT INTO dim_objs_array;
4235 dim_objs_array.delete;
4236 i:= 0;
4237 LOOP
4238 FETCH cr_dimobj_in_dimset INTO l_no_dim_object;
4239 EXIT WHEN cr_dimobj_in_dimset%NOTFOUND;
4240 i:= i+1;
4241 dim_objs_array(i).p_no_dim_object := l_no_dim_object;
4242 END LOOP;
4243 CLOSE cr_dimobj_in_dimset;
4244
4245 l_dim_obj_short_names := p_Dim_Obj_Short_Names;
4246
4247 WHILE(Is_More(p_dim_obj_short_names=>l_dim_obj_short_names,p_dim_obj_name=> l_dim_short_name)) LOOP
4248
4249 l_found_dimobj := FALSE;
4250 FOR index_loop IN 1..(dim_objs_in_dim.COUNT) LOOP
4251
4252 IF(l_dim_short_name = dim_objs_in_dim(index_loop).p_no_dim_object) THEN
4253 l_found_dimobj := TRUE;
4254 END IF;
4255 END LOOP;
4256 IF(NOT l_found_dimobj ) THEN
4257 l_plus_count := l_plus_count + 1;
4258 END IF;
4259
4260 END LOOP;
4261 l_dim_obj_short_names := ','||p_Dim_Obj_Short_Names||',';
4262 FOR index_loop IN 1..(dim_objs_in_dim.COUNT) LOOP
4263 l_dimobj_temp := ','||dim_objs_in_dim(index_loop).p_no_dim_object||',';
4264 IF(Instr(l_dim_obj_short_names, l_dimobj_temp) = 0) THEN
4265 l_minus_count := l_minus_count + 1;
4266
4267 END IF;
4268 END LOOP;
4269
4270 IF(( (dim_objs_array.COUNT)+l_plus_count-l_minus_count ) > BSC_BIS_KPI_MEAS_PUB.Config_Limit_Dim ) THEN
4271
4272 FND_MESSAGE.SET_NAME('BSC','BSC_PMD_IMPACT_KPI_SPACE');
4273 FND_MESSAGE.SET_TOKEN('CONTINUE', BSC_APPS.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'YES'), TRUE);
4274 FND_MESSAGE.SET_TOKEN('CANCEL', BSC_APPS.Get_Lookup_Value('BSC_UI_KPIDESIGNER', 'NO'), TRUE);
4275 FND_MSG_PUB.ADD;
4276 RAISE FND_API.G_EXC_ERROR;
4277 END IF;
4278 END LOOP;
4279 END IF;
4280 IF(cr_dimobjs_in_dim%ISOPEN) THEN
4281 CLOSE cr_dimobjs_in_dim;
4282 END IF;
4283 IF(cr_kpidimset_dim%ISOPEN) THEN
4284 CLOSE cr_kpidimset_dim;
4285 END IF;
4286 IF(cr_dimobj_in_dimset%ISOPEN) THEN
4287 CLOSE cr_dimobj_in_dimset;
4288 END IF;
4289 RETURN NULL;
4290 EXCEPTION
4291 WHEN FND_API.G_EXC_ERROR THEN
4292 IF (l_Msg_Data IS NULL) THEN
4293 FND_MSG_PUB.Count_And_Get
4294 ( p_encoded => FND_API.G_FALSE
4295 , p_count => l_msg_count
4296 , p_data => l_Msg_Data
4297 );
4298 END IF;
4299 IF(cr_kpidimset_dim%ISOPEN) THEN
4300 CLOSE cr_kpidimset_dim;
4301 END IF;
4302 IF(cr_dimobj_in_dimset%ISOPEN) THEN
4303 CLOSE cr_dimobj_in_dimset;
4304 END IF;
4305 IF(cr_dimobjs_in_dim%ISOPEN) THEN
4306 CLOSE cr_dimobjs_in_dim;
4307 END IF;
4308 RETURN l_Msg_Data;
4309 WHEN OTHERS THEN
4310 IF(cr_kpidimset_dim%ISOPEN) THEN
4311 CLOSE cr_kpidimset_dim;
4312 END IF;
4313 IF(cr_dimobj_in_dimset%ISOPEN) THEN
4314 CLOSE cr_dimobj_in_dimset;
4315 END IF;
4316 IF(cr_dimobjs_in_dim%ISOPEN) THEN
4317 CLOSE cr_dimobjs_in_dim;
4318 END IF;
4319
4320 RETURN NULL;
4321 END is_config_impact_dim;
4322 --============================================================================================
4323
4324 /**************************************************************************************************************
4325 Check if the Dimension/Dimensions is Empty
4326 ****************************************************************************************************************/
4327 FUNCTION Is_Dim_Empty
4328 (
4329 p_dim_short_names IN VARCHAR
4330
4331 ) RETURN VARCHAR2 IS
4332 l_count NUMBER;
4333 l_dim_group_id BSC_SYS_DIM_GROUPS_TL.Dim_Group_ID%TYPE;
4334 l_dim_short_names VARCHAR2(32000);
4335 l_dim_short_name BSC_SYS_DIM_GROUPS_TL.Short_Name%TYPE;
4336 l_return_value VARCHAR2(1);
4337
4338
4339
4340 BEGIN
4341 l_dim_short_names := p_dim_short_names;
4342 l_return_value :=FND_API.G_FALSE;
4343
4344 WHILE (is_more(p_dim_obj_short_names => l_dim_short_names
4345 , p_dim_obj_name => l_dim_short_name )
4346 ) LOOP
4347
4348 l_dim_group_id := BSC_BIS_DIMENSION_PUB.Get_Bsc_Dimension_ID(l_dim_short_name);
4349
4350 SELECT count(dim_level_id) into l_count
4351 FROM BSC_SYS_DIM_LEVELS_BY_GROUP
4352 WHERE dim_group_id = l_dim_group_id;
4353
4354 IF (l_count =0) THEN
4355
4356 l_return_value:= FND_API.G_TRUE;
4357 EXIT;
4358 END IF;
4359 END LOOP;
4360
4361 RETURN l_return_value; --Will return FND_API.G_TRUE even if 1 Dimension is Empty
4362
4363 END Is_Dim_Empty;
4364
4365
4366 /**************************************************************************************************************
4367 Check if the Dimension/Dimensions has Single Dimension Object
4368 ****************************************************************************************************************/
4369 FUNCTION Dim_With_Single_Dim_Obj
4370 (
4371 p_dim_short_names IN VARCHAR
4372
4373 ) RETURN VARCHAR2 IS
4374 l_count NUMBER;
4375 l_dim_group_id BSC_SYS_DIM_GROUPS_TL.Dim_Group_ID%TYPE;
4376 l_dim_short_names VARCHAR2(32000);
4377 l_dim_name BSC_SYS_DIM_GROUPS_TL.Name%TYPE;
4378 l_dim_short_name BSC_SYS_DIM_GROUPS_TL.Short_Name%TYPE;
4379 l_return_value VARCHAR2(32000);
4380
4381
4382
4383 BEGIN
4384
4385
4386
4387 l_dim_short_names := p_dim_short_names;
4388 l_return_value :='';
4389
4390
4391
4392
4393
4394 WHILE (is_more( p_dim_obj_short_names => l_dim_short_names
4395 , p_dim_obj_name => l_dim_short_name )
4396 ) LOOP
4397
4398 l_dim_group_id := BSC_BIS_DIMENSION_PUB.Get_Bsc_Dimension_ID(l_dim_short_name);
4399
4400
4401
4402
4403 l_dim_name:= BSC_BIS_DIMENSION_PUB.Get_Bsc_Dimension_Name(l_dim_short_name);
4404
4405
4406
4407 SELECT count(dim_level_id) into l_count
4408 FROM BSC_SYS_DIM_LEVELS_BY_GROUP
4409 WHERE dim_group_id = l_dim_group_id;
4410
4411 IF ((l_count =1) AND (BSC_BIS_DIMENSION_PUB.Is_Dimension_in_Ind( l_dim_group_id))) THEN
4412
4413
4414 l_return_value:= l_return_value||l_dim_name||',';
4415 END IF;
4416
4417
4418 END LOOP;
4419
4420
4421
4422
4423 RETURN l_return_value; --Will return Dimension Names of all Dimensions with Single Dimension Object
4424
4425 END Dim_With_Single_Dim_Obj;
4426 /**************************************************************************************************************
4427 Summry: Check the passing list of dimensions, cascading remove empty BSC dimension from dim set
4428 Called in: 1.) BSC_BIS_DIMENSION_PUB.Assign_Unassign_Dim_Objs 2.) BSC_BIS_DIM_OBJ_PUB.Assign_Unassign_Dimensions
4429 ****************************************************************************************************************/
4430 PROCEDURE Remove_Empty_Dims_For_DimSet
4431 ( p_commit IN VARCHAR2 := FND_API.G_FALSE -- mdamle 06/06/2005 - Set default p_commit to false for dim. group apis called from EO
4432 , p_dim_short_names IN VARCHAR2
4433 , p_time_stamp IN VARCHAR2 := NULL -- Granular Locking
4434 , x_return_status OUT NOCOPY VARCHAR2
4435 , x_msg_count OUT NOCOPY NUMBER
4436 , x_msg_data OUT NOCOPY VARCHAR2
4437 ) IS
4438
4439 l_count NUMBER;
4440 l_dim_group_id BSC_SYS_DIM_GROUPS_TL.Dim_Group_ID%TYPE;
4441 l_dim_short_names VARCHAR2(32000);
4442 l_dim_short_name BSC_SYS_DIM_GROUPS_TL.Short_Name%TYPE;
4443 l_source BSC_SYS_DIM_LEVELS_B.Source%TYPE;
4444
4445 CURSOR c_dim_set_kpi IS
4446 SELECT DISTINCT A.indicator INDICATOR
4447 , A.dim_set_id DIM_SET_ID
4448 , A.Dim_Group_Index
4449 FROM BSC_KPI_DIM_GROUPS A
4450 , BSC_KPIS_B B
4451 WHERE A.INDICATOR = B.INDICATOR
4452 AND B.share_flag <> 2
4453 AND A.dim_group_id = l_dim_group_id
4454 ORDER BY A.Dim_Group_Index;
4455
4456 CURSOR c_dim_level_count IS
4457 SELECT count(dim_level_id)
4458 FROM BSC_SYS_DIM_LEVELS_BY_GROUP
4459 WHERE dim_group_id = l_dim_group_id;
4460
4461 BEGIN
4462
4463 l_dim_short_names := p_dim_short_names;
4464
4465
4466 -- For each dimension
4467 WHILE (is_more(p_dim_obj_short_names => l_dim_short_names
4468 , p_dim_obj_name => l_dim_short_name )
4469 ) LOOP
4470 l_source :=BSC_BIS_DIMENSION_PUB.Get_Dimension_Source(l_dim_short_name);
4471
4472
4473 -- check if it is bsc dimension
4474 IF ( l_source= 'BSC' or l_source is NULL) THEN --only need to unassign dimension from dim set for BSC
4475
4476 l_dim_group_id := BSC_BIS_DIMENSION_PUB.Get_Bsc_Dimension_ID(l_dim_short_name);
4477
4478
4479 -- check if empty dimension
4480 SELECT count(dim_level_id) into l_count
4481 FROM BSC_SYS_DIM_LEVELS_BY_GROUP
4482 WHERE dim_group_id = l_dim_group_id;
4483
4484 IF (l_count =0) THEN
4485 -- Cascading unassign empty dimension from Objectives-dim sets
4486 FOR cd IN c_dim_set_kpi LOOP
4487
4488 BSC_BIS_KPI_MEAS_PUB.Unassign_Dims_From_Dim_Set
4489 ( p_commit => FND_API.G_FALSE
4490 , p_kpi_id => cd.Indicator
4491 , p_dim_set_id => cd.Dim_Set_Id
4492 , p_dim_short_names => l_dim_short_name
4493 , p_time_stamp => p_time_stamp
4494 , x_return_status => x_return_status
4495 , x_msg_count => x_msg_count
4496 , x_msg_data => x_msg_data
4497 );
4498 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4499 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4500 END IF;
4501 END LOOP;
4502 END IF;
4503 END IF;
4504
4505 END LOOP;
4506
4507 IF (p_commit = FND_API.G_TRUE) THEN
4508 COMMIT;
4509
4510 END IF;
4511
4512 END Remove_Empty_Dims_For_DimSet;
4513 /****************************************************************************************
4514 this function checks the invalid dimenison objects in dimension
4515 dimension should not have both autogenerated and existing source dimension object at a time
4516 ****************************************************************************************/
4517 FUNCTION check_sametype_dimobjs
4518 ( p_dim_name IN VARCHAR2
4519 , p_dim_short_name IN VARCHAR2
4520 , p_dim_short_names IN VARCHAR2
4521 , p_Restrict_Dim_Validate IN VARCHAR2 := NULL
4522 , x_dim_type OUT NOCOPY VARCHAR2
4523 , x_return_status OUT NOCOPY VARCHAR2
4524 , x_msg_count OUT NOCOPY NUMBER
4525 , x_msg_data OUT NOCOPY VARCHAR2
4526 ) RETURN BOOLEAN
4527 IS
4528
4529 CURSOR C_SOURCE_DIM IS
4530 SELECT source,name
4531 FROM bsc_sys_dim_levels_vl
4532 WHERE INSTR(','||p_dim_short_names ||',' , ','||short_name||',') > 0;
4533
4534 l_source VARCHAR2(20);
4535 l_true BOOLEAN;
4536 l_dim_obj_name VARCHAR2(32000);
4537 l_dim_name VARCHAR2(32000);
4538 l_diff_source_cnt NUMBER;
4539 BEGIN
4540
4541 x_return_status := FND_API.G_RET_STS_SUCCESS;
4542 l_true := FALSE;
4543
4544 -- added to relax checking for mixed type of Dimension Objects within a Dimension
4545 -- for Autogenerated reports and removing the disctiction, BSC 5.3
4546 IF (p_Restrict_Dim_Validate IS NOT NULL OR BIS_UTILITIES_PUB.Enable_Generated_Source_Report = FND_API.G_FALSE) THEN
4547 SELECT count(distinct(source))
4548 INTO l_diff_source_cnt
4549 FROM bsc_sys_dim_levels_vl
4550 WHERE INSTR(','||p_dim_short_names ||',',','||short_name||',') > 0;
4551
4552 IF(l_diff_source_cnt > 1) THEN
4553 l_true := TRUE;
4554 FOR CD IN C_SOURCE_DIM LOOP
4555 l_dim_obj_name := CD.NAME;
4556 IF(l_source IS NULL) THEN
4557 l_source := CD.source;
4558 END IF;
4559 IF ((p_dim_short_name = BSC_BIS_DIMENSION_PUB.Unassigned_Dim) AND
4560 (l_source = 'BSC')) THEN
4561
4562 EXIT;
4563 END IF;
4564
4565 IF(l_source <> CD.source) then
4566 EXIT;
4567
4568 END IF;
4569 END LOOP;
4570
4571 END IF;
4572
4573 IF (l_true) THEN
4574 FND_MESSAGE.SET_NAME('BSC','BSC_DIM_DIMOBJ_MIXED_TYPE');
4575 FND_MESSAGE.SET_TOKEN('DIMENSION', p_dim_name);
4576 FND_MESSAGE.SET_TOKEN('DIM_OBJECT', l_dim_obj_name);
4577 FND_MSG_PUB.ADD;
4578 ELSE
4579 SELECT distinct(NVL(source, 'BSC'))
4580 INTO x_dim_type
4581 FROM bsc_sys_dim_levels_vl
4582 WHERE INSTR(','||p_dim_short_names ||',',','||short_name||',') > 0;
4583 END IF;
4584 END IF;
4585
4586 x_return_status := FND_API.G_RET_STS_SUCCESS;
4587 RETURN l_true;
4588 EXCEPTION
4589 WHEN OTHERS THEN
4590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4591 IF (x_msg_data IS NULL) THEN
4592 x_msg_data := SQLERRM||' at BSC_BIS_DIMENSION_PUB.is_Valid_Dim_Grp_Rels ';
4593 END IF;
4594
4595 RETURN l_true;
4596 END check_sametype_dimobjs ;
4597
4598 /*********************************************************************************
4599 API TO Check if Dimension is Assigned to any KPI.
4600 Return 'T' if there's at least one Objective assigned, 'F' otherwise.
4601 *********************************************************************************/
4602 FUNCTION Is_Objective_Assigned
4603 (
4604 p_dim_short_name IN VARCHAR2
4605 ) RETURN VARCHAR2
4606 IS
4607 l_retval VARCHAR2(1) := 'T'; --be protective, set to true by default
4608 l_count NUMBER;
4609 BEGIN
4610 SELECT COUNT(1) INTO l_count
4611 FROM BSC_KPI_DIM_GROUPS KG,
4612 BSC_SYS_DIM_GROUPS_VL G
4613 WHERE KG.DIM_GROUP_ID = G.DIM_GROUP_ID
4614 AND G.SHORT_NAME = p_dim_short_name;
4615
4616 IF (l_count = 0) THEN
4617 l_retval := 'F';
4618 END IF;
4619
4620 RETURN l_retval;
4621 EXCEPTION
4622 WHEN OTHERS THEN
4623 RETURN 'T';
4624 END Is_Objective_Assigned;
4625
4626
4627 /***************************************************************
4628
4629 The following API determines whether a KPI needs color change
4630 prototype flag to set
4631
4632 ****************************************************************/
4633
4634 FUNCTION is_color_change_required (
4635 p_old_default IN VARCHAR2,
4636 p_new_default IN VARCHAR2,
4637 p_obj_id IN bsc_kpis_b.indicator%TYPE,
4638 p_kpi_measure_id IN bsc_kpi_measure_props.kpi_measure_id%TYPE
4639 )
4640 RETURN NUMBER IS
4641 l_color_change NUMBER;
4642 CURSOR c_kpi_color IS
4643 SELECT c.color_by_total
4644 FROM bsc_kpi_measure_props c
4645 WHERE c.indicator = p_obj_id
4646 AND c.kpi_measure_id = p_kpi_measure_id;
4647
4648 BEGIN
4649 l_color_change := 0;
4650 FOR cd IN c_kpi_color LOOP
4651
4652 --If KPI comparison setting is ALL
4653 IF (cd.color_by_total = 1 ) THEN
4654 IF (p_new_default = 'C') THEN
4655 l_color_change := 1;
4656 END IF;
4657
4658 --If KPI comparison setting is WORST MEMBER COLOR
4659 ELSIF (cd.color_by_total = 0 ) THEN
4660 IF (p_old_default = 'C' AND p_new_default = 'T') THEN
4661 l_color_change := 1;
4662 END IF;
4663 END IF;
4664 EXIT;
4665 END LOOP;
4666 RETURN l_color_change;
4667 END is_color_change_required;
4668
4669 /********************************************************************
4670 The following API returns 1 if the objective needs color change
4671 and returns 0 if the objective does not need color change
4672 ***************************************************************/
4673
4674 FUNCTION get_kpi_flag_change (
4675 p_old_default IN VARCHAR2,
4676 p_new_default IN VARCHAR2,
4677 p_indicator IN bsc_kpis_b.indicator%TYPE,
4678 p_dim_obj_objs_tbl IN OUT NOCOPY BSC_BIS_DIMENSION_PUB.dimobj_obj_kpis_tbl_type
4679 )
4680 RETURN NUMBER IS
4681
4682 l_color_change NUMBER;
4683 l_count NUMBER;
4684 l_result NUMBER;
4685 l_is_col_change NUMBER;
4686 l_ind NUMBER;
4687 l_mes_id NUMBER;
4688 l_color_rollup_type VARCHAR2(100);
4689 l_obj_proto_flag NUMBER;
4690 l_default_kpi NUMBER;
4691 l_kpi_weight NUMBER;
4692
4693 BEGIN
4694
4695 SELECT count(0) INTO l_count
4696 FROM bsc_kpis_b WHERE
4697 indicator = p_indicator;
4698 l_result := BSC_DESIGNER_PVT.G_ActionFlag.Normal;
4699
4700 IF (l_count = 1) THEN
4701 FOR i IN 0..(p_dim_obj_objs_tbl.COUNT-1) LOOP
4702 IF (p_indicator = p_dim_obj_objs_tbl(i).p_indicator) THEN
4703
4704 l_is_col_change := is_color_change_required(
4705 p_old_default => p_old_default,
4706 p_new_default => p_new_default,
4707 p_obj_id => p_dim_obj_objs_tbl(i).p_indicator,
4708 p_kpi_measure_id=> p_dim_obj_objs_tbl(i).p_kpi_measure_id
4709 );
4710
4711 IF (l_is_col_change = 1) THEN
4712 l_result := BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color;
4713 END IF;
4714 END IF;
4715 END LOOP;
4716
4717 END IF;
4718
4719 RETURN l_result;
4720
4721 END get_kpi_flag_change;
4722 END BSC_BIS_DIMENSION_PUB;