1 PACKAGE BODY BSC_COMMON_DIM_LEVELS_PUB AS
2 /* $Header: BSCPCDLB.pls 120.3 2007/02/20 17:04:07 psomesul ship $ */
3
4 -------------------------------------------------------------------------------------------------------------------
5 -- Check_Common_Dim_Levels
6 -- Return x_return_status = 'DISABLE' if it disables one or more common
7 -- Dimension in the Checking.
8 -------------------------------------------------------------------------------------------------------------------
9 PROCEDURE Check_Common_Dim_Levels(
10 p_commit IN varchar2 -- := FND_API.G_FALSE
11 ,p_Tab_Id IN number
12 ,x_return_status OUT NOCOPY varchar2
13 ,x_msg_count OUT NOCOPY number
14 ,x_msg_data OUT NOCOPY varchar2
15 ) IS
16
17 v_Common_Level_ReTrieved_Tbl BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Tbl_Type;
18 v_Common_Level_Found_Tbl BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Tbl_Type;
19
20 v_Dim_Level_Rec_R BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
21 v_Dim_Level_Rec_F BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
22 v_Index NUMBER;
23 v_Parent_Dim_Level_Id NUMBER;
24 l_deleted_rows NUMBER;
25 l_Bsc_Tab_Entity_Rec BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec;
26
27
28 BEGIN
29 FND_MSG_PUB.Initialize;
30 x_return_status := FND_API.G_RET_STS_SUCCESS;
31 SAVEPOINT BSCCheDimLevsPUB;
32 --DBMS_OUTPUT.PUT_LINE('Begin Check_Common_Dim_Levels' );
33 --DBMS_OUTPUT.PUT_LINE('Check_Common_Dim_Levels p_Tab_Id = ' || p_Tab_Id );
34 -- Get the Common Dimension Level already define in the DB.
35 Retrieve_Common_Dim_Levels(p_commit, p_Tab_Id, v_Common_Level_ReTrieved_Tbl
36 ,x_return_status ,x_msg_count ,x_msg_data );
37
38 l_deleted_rows := 0;
39 IF v_Common_Level_ReTrieved_Tbl.COUNT > 0 THEN /* If There are common Dimension defined in DB */
40 -- Find the potention Common Dimension Levels
41 Find_Common_Dim_Levels(p_commit, p_Tab_Id, v_Common_Level_Found_Tbl
42 ,x_return_status ,x_msg_count ,x_msg_data );
43
44 -- Check For the Common Dimension Level that not apply any more
45 -- Compare data from v_Common_Level_ReTrieved_Tbl and v_Common_Level_Found_Tbl
46 -- (The Common Level are stored in secuencial order)
47 v_Index := 0;
48 IF v_Common_Level_Found_Tbl.COUNT > 0 THEN
49 LOOP
50 v_Index := v_Index + 1;
51 v_Dim_Level_Rec_R := v_Common_Level_ReTrieved_Tbl(v_Index);
52 IF v_Index <= v_Common_Level_Found_Tbl.COUNT THEN
53 v_Dim_Level_Rec_F := v_Common_Level_Found_Tbl(v_Index);
54 IF v_Dim_Level_Rec_R.Bsc_Level_View_Name <> v_Dim_Level_Rec_F.Bsc_Level_View_Name THEN
55 EXIT;
56 END IF;
57 ELSE
58 EXIT;
59 END IF;
60 IF v_Index = v_Common_Level_ReTrieved_Tbl.COUNT THEN
61 --It does not need to delete any of the defined Common levels
62 v_Index := - 9999;
63 EXIT;
64 END IF;
65 END LOOP;
66 v_Index := v_Index - 1;
67 END IF;
68 IF v_Index >= 0 then
69 --DBMS_OUTPUT.PUT_LINE('Check_Common_Dim_Levels It need delete some common Dimension Levels');
70
71 -- Delete the Common Levels that not applay any more
72 DELETE FROM BSC_SYS_COM_DIM_LEVELS
73 WHERE TAB_ID = p_Tab_Id
74 AND DIM_LEVEL_INDEX >= v_Index;
75 l_deleted_rows := sql%rowcount;
76 --DBMS_OUTPUT.PUT_LINE('Check_Common_Dim_Levels l_deleted_rows = ' ||l_deleted_rows);
77 -- Delete Records from BSC_USER_LIST_ACCESS that not apply any more
78 DELETE FROM BSC_USER_LIST_ACCESS
79 WHERE TAB_ID = p_Tab_Id
80 AND DIM_LEVEL_INDEX >= v_Index;
81
82 x_return_status := 'DISABLE';
83
84 Check_Dim_Level_Default_Value(p_commit, p_Tab_Id
85 ,x_return_status ,x_msg_count ,x_msg_data );
86 ELSE
87 --DBMS_OUTPUT.PUT_LINE('Check_Common_Dim_Levels Common Dimension Levels Not need Changes');
88 v_Index := 0; /* Just to support the output */
89 END IF;
90 ELSE
91 --DBMS_OUTPUT.PUT_LINE('Check_Common_Dim_Levels No Common Dimension Levels Defined in DB');
92 v_Index := 0; /* Just to support the output */
93 END IF;
94
95 -- change the Scorecard time stamp when the common dimension were updated.
96
97 --DBMS_OUTPUT.PUT_LINE('Check_Common_Dim_Levels - l_deleted_rows = '||l_deleted_rows);
98
99
100 IF l_deleted_rows <> 0 THEN
101 --DBMS_OUTPUT.PUT_LINE('Check_Common_Dim_Levels - l_deleted_rows = '||l_deleted_rows);
102
103 l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id:=p_Tab_Id;
104 BSC_SCORECARD_PUB.Update_Tab_Time_Stamp( FND_API.G_FALSE
105 ,l_Bsc_Tab_Entity_Rec
106 ,x_return_status
107 ,x_msg_count
108 ,x_msg_data
109 );
110 END IF;
111
112 --DBMS_OUTPUT.PUT_LINE('End Check_Common_Dim_Levels');
113 /*
114 BSC_MESSAGE.Add(x_message => 'completed run Check_Common_Dim_Levels',
115 x_source => 'BSC_COMMON_DIM_LEVELS_PUB',
116 x_mode => 'I');
117 commit;
118 */
119
120 EXCEPTION
121 WHEN FND_API.G_EXC_ERROR THEN
122 ROLLBACK TO BSCCheDimLevsPUB;
123 FND_MSG_PUB.Count_And_Get
124 ( p_encoded => FND_API.G_FALSE
125 , p_count => x_msg_count
126 , p_data => x_msg_data
127 );
128 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
129 x_return_status := FND_API.G_RET_STS_ERROR;
130 RAISE;
131 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
132 ROLLBACK TO BSCCheDimLevsPUB;
133 FND_MSG_PUB.Count_And_Get
134 ( p_encoded => FND_API.G_FALSE
135 , p_count => x_msg_count
136 , p_data => x_msg_data
137 );
138 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
139 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
140 RAISE;
141 WHEN NO_DATA_FOUND THEN
142 ROLLBACK TO BSCCheDimLevsPUB;
143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
144 IF (x_msg_data IS NOT NULL) THEN
145 x_msg_data := x_msg_data||' -> BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels ';
146 ELSE
147 x_msg_data := SQLERRM||' at BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels ';
148 END IF;
149 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
150 RAISE;
151 WHEN OTHERS THEN
152 ROLLBACK TO BSCCheDimLevsPUB;
153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
154 IF (x_msg_data IS NOT NULL) THEN
155 x_msg_data := x_msg_data||' -> BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels ';
156 ELSE
157 x_msg_data := SQLERRM||' at BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels ';
158 END IF;
159 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
160 RAISE;
161 END Check_Common_Dim_Levels;
162
163 /*-------------------------------------------------------------------------------------------------------------------
164 Check_Common_Dim_Levels_DL
165 To Check Common dimension levels when dimension level is updated
166 ot deleted, etc
167 -------------------------------------------------------------------------------------------------------------------*/
168 PROCEDURE Check_Common_Dim_Levels_DL(
169 p_Dim_Level_Id IN number
170 ,x_return_status OUT NOCOPY varchar2
171 ,x_msg_count OUT NOCOPY number
172 ,x_msg_data OUT NOCOPY varchar2
173 ) IS
174 -- Query to get the tabs where a dimension object is used
175 -- as common dimension level
176 CURSOR c_tabs_to_check is
177 select TAB_ID
178 from BSC_SYS_COM_DIM_LEVELS
179 Where DIM_LEVEL_id = p_Dim_Level_Id;
180
181 l_tab_id number;
182
183 BEGIN
184 FND_MSG_PUB.Initialize;
185 x_return_status := FND_API.G_RET_STS_SUCCESS;
186 SAVEPOINT BSCCheDimLevsDL_PUB;
187 --DBMS_OUTPUT.PUT_LINE('Begin Check_Common_Dim_Levels_DL' );
188 --DBMS_OUTPUT.PUT_LINE('Check_Common_Dim_Levels_DL p_Dim_Level_Id = ' || p_Dim_Level_Id );
189 open c_tabs_to_check;
190 loop
191 fetch c_tabs_to_check into l_tab_id;
192 exit when c_tabs_to_check%notfound;
193 Check_Common_Dim_Levels(
194 p_Tab_Id => l_tab_id
195 ,x_return_status => x_return_status
196 ,x_msg_count => x_msg_count
197 ,x_msg_data => x_msg_data
198 );
199 end loop;
200 close c_tabs_to_check;
201 --DBMS_OUTPUT.PUT_LINE('Begin Check_Common_Dim_Levels_DL' );
202
203 EXCEPTION
204 WHEN FND_API.G_EXC_ERROR THEN
205 if c_tabs_to_check%isopen then
206 close c_tabs_to_check;
207 end if;
208 ROLLBACK TO BSCCheDimLevsDL_PUB;
209 FND_MSG_PUB.Count_And_Get
210 ( p_encoded => FND_API.G_FALSE
211 , p_count => x_msg_count
212 , p_data => x_msg_data
213 );
214 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
215 x_return_status := FND_API.G_RET_STS_ERROR;
216 RAISE;
217 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
218 if c_tabs_to_check%isopen then
219 close c_tabs_to_check;
220 end if;
221 ROLLBACK TO BSCCheDimLevsDL_PUB;
222 FND_MSG_PUB.Count_And_Get
223 ( p_encoded => FND_API.G_FALSE
224 , p_count => x_msg_count
225 , p_data => x_msg_data
226 );
227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
228 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
229 RAISE;
230 WHEN OTHERS THEN
231 if c_tabs_to_check%isopen then
232 close c_tabs_to_check;
233 end if;
234 ROLLBACK TO BSCCheDimLevsDL_PUB;
235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
236 IF (x_msg_data IS NOT NULL) THEN
237 x_msg_data := x_msg_data||' -> BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels_DL';
238 ELSE
239 x_msg_data := SQLERRM||' at BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels_DL';
240 END IF;
241 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
242 RAISE;
243 END Check_Common_Dim_Levels_DL;
244
245 /*------------------------------------------------------------------------------
246 Check_Common_Dim_Levels_by_Dim
247 Top be use when a Dimension (Dimension Group is updated)
248 ---------------------------------------------------------------------------------*/
249 PROCEDURE Check_Common_Dim_Levels_by_Dim(
250 p_Dimension_Id IN number
251 ,x_return_status OUT NOCOPY varchar2
252 ,x_msg_count OUT NOCOPY number
253 ,x_msg_data OUT NOCOPY varchar2
254 ) IS
255 -- Query to get the tabs where a dimension object is used
256 -- as common dimension level
257
258 CURSOR c_tabs_to_check is
259 SELECT DISTINCT B.TAB_ID
260 FROM BSC_KPI_DIM_GROUPS A
261 ,BSC_TAB_INDICATORS B
262 WHERE A.INDICATOR = B.INDICATOR
263 AND A.DIM_GROUP_ID = p_Dimension_Id;
264
265 l_tab_id number;
266
267 BEGIN
268 FND_MSG_PUB.Initialize;
269 x_return_status := FND_API.G_RET_STS_SUCCESS;
270 SAVEPOINT BSCCheckCDimByDim;
271 --DBMS_OUTPUT.PUT_LINE('Begin Check_Common_Dim_Levels_by_Dim' );
272 --DBMS_OUTPUT.PUT_LINE('Check_Common_Dim_Levels_by_Dim p_Dimension_Id = ' || p_Dimension_Id );
273 open c_tabs_to_check;
274 loop
275 fetch c_tabs_to_check into l_tab_id;
276 exit when c_tabs_to_check%notfound;
277 Check_Common_Dim_Levels(
278 p_Tab_Id => l_tab_id
279 ,x_return_status => x_return_status
280 ,x_msg_count => x_msg_count
281 ,x_msg_data => x_msg_data
282 );
283 end loop;
284 close c_tabs_to_check;
285 --DBMS_OUTPUT.PUT_LINE('End Check_Common_Dim_Levels_by_Dim' );
286
287 EXCEPTION
288 WHEN FND_API.G_EXC_ERROR THEN
289 if c_tabs_to_check%isopen then
290 close c_tabs_to_check;
291 end if;
292 ROLLBACK TO BSCCheckCDimByDim;
293 FND_MSG_PUB.Count_And_Get
294 ( p_encoded => FND_API.G_FALSE
295 , p_count => x_msg_count
296 , p_data => x_msg_data
297 );
298 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
299 x_return_status := FND_API.G_RET_STS_ERROR;
300 RAISE;
301 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
302 if c_tabs_to_check%isopen then
303 close c_tabs_to_check;
304 end if;
305 ROLLBACK TO BSCCheckCDimByDim;
306 FND_MSG_PUB.Count_And_Get
307 ( p_encoded => FND_API.G_FALSE
308 , p_count => x_msg_count
309 , p_data => x_msg_data
310 );
311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
312 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
313 RAISE;
314 WHEN OTHERS THEN
315 if c_tabs_to_check%isopen then
316 close c_tabs_to_check;
317 end if;
318 ROLLBACK TO BSCCheckCDimByDim;
319 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
320 IF (x_msg_data IS NOT NULL) THEN
321 x_msg_data := x_msg_data||' -> BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels_by_Dim';
322 ELSE
323 x_msg_data := SQLERRM||' at BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels_by_Dim';
324 END IF;
325 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
326 RAISE;
327 END Check_Common_Dim_Levels_by_Dim;
328
329
330 -------------------------------------------------------------------------------------------------------------------
331 -- Find_Common_Dim_Levels
332 -------------------------------------------------------------------------------------------------------------------
333 PROCEDURE Find_Common_Dim_Levels(
334 p_commit IN varchar2 := FND_API.G_FALSE
335 ,p_Tab_Id IN number
336 ,x_Dim_Level_Tbl OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Tbl_Type
337 ,x_return_status OUT NOCOPY varchar2
338 ,x_msg_count OUT NOCOPY number
339 ,x_msg_data OUT NOCOPY varchar2
340 ) IS
341
342 v_Num_KPI_Default_PMF number; /* Number of KPIs With Default PMF Measures */
343 v_Num_Dim_Sets_In_Tab number;
344 v_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
345
346 v_Index number;
347 v_Parent_Dim_Level_Id number;
348
349
350 --Cursor to get The common Dimensions for the tab.
351 CURSOR c_Common_Levels IS
352 SELECT DISTINCT KL.LEVEL_TABLE_NAME, KL.DIM_LEVEL_INDEX, NVL(KL.PARENT_LEVEL_INDEX, -1), SL.DIM_LEVEL_ID
353 FROM BSC_TAB_INDICATORS TI,
354 BSC_KPIS_B KB,
355 BSC_KPI_DIM_LEVELS_VL KL,
356 BSC_SYS_DIM_LEVELS_VL SL
357 WHERE TI.TAB_ID = p_Tab_Id
358 AND KB.INDICATOR = TI.INDICATOR
359 AND KB.PROTOTYPE_FLAG <> 2
360 AND KL.INDICATOR = KB.INDICATOR
361 AND KL.TABLE_RELATION IS NULL
362 AND KL.STATUS <> 0
363 AND KL.DEFAULT_KEY_VALUE IS NULL
364 AND ( KL.DEFAULT_VALUE = 'T' OR KL.DEFAULT_VALUE LIKE 'D%')
365 AND KL.LEVEL_SOURCE ='BSC'
366 AND SL.LEVEL_TABLE_NAME = KL.LEVEL_TABLE_NAME
367 GROUP BY KL.LEVEL_TABLE_NAME,
368 KL.PARENT_LEVEL_INDEX,
369 KL.DIM_LEVEL_INDEX,
370 STATUS,
374 ORDER BY KL.DIM_LEVEL_INDEX;
371 KL.TABLE_RELATION,
372 SL.DIM_LEVEL_ID
373 HAVING Count(KL.DIM_SET_ID) = v_Num_Dim_Sets_In_Tab
375
376 CURSOR c_child_validation IS
377 SELECT KL.INDICATOR
378 , KL.DIM_SET_ID
379 , KL.DIM_LEVEL_INDEX
380 , SLG.DEFAULT_VALUE
381 , KL.PARENT_LEVEL_INDEX
382 , KL.DEFAULT_KEY_VALUE
383 FROM BSC_TAB_INDICATORS TI
384 , BSC_KPIS_B KB
385 , BSC_KPI_DIM_LEVELS_VL KL
386 , BSC_KPI_DIM_GROUPS KG
387 , BSC_SYS_DIM_LEVELS_BY_GROUP SLG
388 , BSC_SYS_DIM_LEVELS_VL SL
389 WHERE TI.TAB_ID = p_Tab_Id
390 AND KB.INDICATOR = TI.INDICATOR
391 AND KB.PROTOTYPE_FLAG <> 2
392 AND KL.INDICATOR = KB.INDICATOR
393 AND KG.INDICATOR = KL.INDICATOR
394 AND KG.DIM_SET_ID = KL.DIM_SET_ID
395 AND SLG.DIM_GROUP_ID = KG.DIM_GROUP_ID
396 AND SL.DIM_LEVEL_ID = SLG.DIM_LEVEL_ID
397 AND SL.LEVEL_TABLE_NAME = KL.LEVEL_TABLE_NAME
398 ORDER BY KL.INDICATOR, KL.DIM_SET_ID, KL.DIM_LEVEL_INDEX;
399
400 l_Dim_Set_Changed_Flag BOOLEAN;
401 l_Child_Dim_Obj_Flag BOOLEAN;
402 l_last_KPI_Code NUMBER;
403 l_Last_Dim_Set_Id NUMBER;
404 l_Firt_Dim_Family_Flag BOOLEAN;
405
406 BEGIN
407 FND_MSG_PUB.Initialize;
408 x_return_status := FND_API.G_RET_STS_SUCCESS;
409 --DBMS_OUTPUT.PUT_LINE('Begin Find_Common_Dim_Levels');
410
411 --Evaluate that not KPI in the tab has PMF Measures as Default One
412 SELECT COUNT(SOURCE)
413 INTO v_Num_KPI_Default_PMF
414 FROM (SELECT DISTINCT KM.INDICATOR, DS.DATASET_ID, DS.SOURCE --, KM.PROTOTYPE_FLAG
415 FROM BSC_TAB_INDICATORS TI,
416 BSC_KPI_ANALYSIS_MEASURES_B KM ,
417 (SELECT INDICATOR, DEFAULT_VALUE
418 FROM BSC_KPI_ANALYSIS_GROUPS
419 WHERE ANALYSIS_GROUP_ID = 0 ) A0,
420 (SELECT INDICATOR, DEFAULT_VALUE
421 FROM BSC_KPI_ANALYSIS_GROUPS
422 WHERE ANALYSIS_GROUP_ID = 1 ) A1,
423 (SELECT INDICATOR, DEFAULT_VALUE
424 FROM BSC_KPI_ANALYSIS_GROUPS
425 WHERE ANALYSIS_GROUP_ID = 2 ) A2,
426 BSC_SYS_DATASETS_B DS
427 WHERE TI.TAB_ID = p_Tab_Id
428 AND KM.INDICATOR = TI.INDICATOR
429 AND KM.DEFAULT_VALUE = 1
430 AND KM.INDICATOR = A0.INDICATOR (+)
431 AND KM.ANALYSIS_OPTION0 = NVL(A0.DEFAULT_VALUE, 0)
432 AND KM.INDICATOR = A1.INDICATOR (+)
433 AND KM.ANALYSIS_OPTION1 = NVL(A1.DEFAULT_VALUE, 0)
434 AND KM.INDICATOR = A2.INDICATOR (+)
435 AND KM.ANALYSIS_OPTION2 = NVL(A2.DEFAULT_VALUE, 0)
436 AND DS.DATASET_ID = KM.DATASET_ID
437 )
438 WHERE SOURCE <> 'BSC';
439
440
441 --If There is not PMF Measures as Default
442 IF v_Num_KPI_Default_PMF = 0 Then
443
444 --Evaluate the number of Dimention Set in the tab, on which a Common Dimension Level must be belong to.
445 --It does not take in account PMF Dimension Sets
446 SELECT COUNT (DIM_SET_ID)
447 INTO v_Num_Dim_Sets_In_Tab
448 FROM (
449 SELECT DISTINCT INDICATOR, DIM_SET_ID, SOURCE
450 FROM
451 ( SELECT KB.INDICATOR, KDS.DIM_SET_ID, SL.DIM_LEVEL_ID, SL.SOURCE
452 FROM BSC_TAB_INDICATORS TI,
453 BSC_KPIS_B KB,
454 BSC_KPI_DIM_SETS_VL KDS,
455 BSC_KPI_DIM_GROUPS KDG,
456 BSC_SYS_DIM_LEVELS_BY_GROUP SLG,
457 BSC_SYS_DIM_LEVELS_B SL
458 WHERE TI.TAB_ID = p_Tab_Id
459 AND KB.INDICATOR = TI.INDICATOR
460 AND KB.PROTOTYPE_FLAG <> 2
461 AND KDS.INDICATOR = KB.INDICATOR
462 AND KDG.INDICATOR (+) = KDS.INDICATOR
463 AND NVL(KDG.DIM_SET_ID , KDS.DIM_SET_ID) = KDS.DIM_SET_ID
464 AND SLG.DIM_GROUP_ID (+) = KDG.DIM_GROUP_ID
465 AND SL.DIM_LEVEL_ID (+) = SLG.DIM_LEVEL_ID
466 ORDER BY KB.INDICATOR, KDS.DIM_SET_ID , KDG.DIM_GROUP_INDEX, SLG.DIM_LEVEL_INDEX
467 )
468 )
469 WHERE (SOURCE <> 'PMF' OR SOURCE IS NULL);
470
471 IF v_Num_Dim_Sets_In_Tab <> 0 Then
472 --Common Dimension are those that are in all the Dimension Sets existing
473 --in the Tab (Not including PMF Dimension)
474 --Rules: Level Status must to be <> 0 . It mean disabled
475 -- M x N RelationShips not apply for Common Dimensions
476 -- Dimensions with DEFAULT_KEY_VALUE not apply for common Dimensions:
477 -- (DEFAULT_VALUE <> 'T Or DEFAULT_KEY_VALUE IS NOT NULL)
478
479 --get The common Dimensions for the tab.
480 v_Index := 0;
481 v_Parent_Dim_Level_Id := -1;
482 OPEN c_Common_Levels;
483 LOOP
484 FETCH c_Common_Levels INTO v_Dim_Level_Rec.Bsc_Level_View_Name
485 ,v_Dim_Level_Rec.Bsc_Level_Index
486 ,v_Dim_Level_Rec.Bsc_Parent_Level_Index
487 ,v_Dim_Level_Rec.Bsc_Level_Id;
488 EXIT WHEN c_Common_Levels%NOTFOUND;
489 IF v_Index = v_Dim_Level_Rec.Bsc_Level_Index AND
490 (v_Dim_Level_Rec.Bsc_Level_Index = 0 OR v_Dim_Level_Rec.Bsc_Parent_Level_Index <> -1 ) then
491 v_Index := v_Index + 1 ;
492 v_Dim_Level_Rec.Bsc_Parent_Level_Id := v_Parent_Dim_Level_Id;
493 x_Dim_Level_Tbl(v_Index) := v_Dim_Level_Rec;
497 --DBMS_OUTPUT.PUT_LINE('Find_Common_Dim_Levels v_Dim_Level_Rec.Bsc_Level_Index ' || v_Dim_Level_Rec.Bsc_Level_Index );
494 v_Parent_Dim_Level_Id := v_Dim_Level_Rec.Bsc_Level_Id;
495
496 --DBMS_OUTPUT.PUT_LINE('Find_Common_Dim_Levels v_Dim_Level_Rec.Bsc_Level_View_Name ' || v_Dim_Level_Rec.Bsc_Level_View_Name);
498 ELSE
499 EXIT;
500 END IF;
501 END LOOP;
502
503 --DBMS_OUTPUT.PUT_LINE('Find_Common_Dim_Levels x_Dim_Level_Tbl.COUNT = ' || x_Dim_Level_Tbl.COUNT);
504
505 ------------ Disable list button when one of the children of the common
506 -- dimension objects doesn't enter in TOTAL
507 -- fixed bug 3518610
508 l_last_KPI_Code := -999;
509 l_Last_Dim_Set_Id := -999;
510 l_Child_Dim_Obj_Flag := TRUE;
511
512 FOR CD IN c_child_validation LOOP
513 l_Dim_Set_Changed_Flag := (CD.INDICATOR <> l_last_KPI_Code) OR (CD.DIM_SET_ID <> l_Last_Dim_Set_Id);
514 IF l_Dim_Set_Changed_Flag THEN
515 l_Firt_Dim_Family_Flag := TRUE;
516 END IF;
517 l_last_KPI_Code := CD.INDICATOR;
518 l_Last_Dim_Set_Id := CD.DIM_SET_ID;
519 l_Child_Dim_Obj_Flag := l_Dim_Set_Changed_Flag OR (l_Dim_Set_Changed_Flag = FALSE AND CD.PARENT_LEVEL_INDEX IS NOT NULL);
520 l_Firt_Dim_Family_Flag := l_Firt_Dim_Family_Flag AND l_Child_Dim_Obj_Flag;
521 --MEM 07/10/00 Bug #1343648 Add condition on DEFAULT_KEY_VALUE. We disable the list also when some common
522 --dimension enter in a key value
523 IF (UPPER(CD.DEFAULT_VALUE) <> 'T' OR CD.DEFAULT_KEY_VALUE IS NOT NULL) AND l_Firt_Dim_Family_Flag THEN
524 -- Clear comman Dimensions
525 x_Dim_Level_Tbl.DELETE;
526 EXIT;
527 END IF;
528 END LOOP;
529 -----------------------------------
530 --DBMS_OUTPUT.PUT_LINE('Find_Common_Dim_Levels x_Dim_Level_Tbl.COUNT = ' || x_Dim_Level_Tbl.COUNT);
531
532 END IF;
533 END IF;
534 --DBMS_OUTPUT.PUT_LINE('End Find_Common_Dim_Levels');
535
536 --debug messages
537 /*
538 BSC_MESSAGE.Add(x_message => 'completed run Find_Common_Dim_Levels',
539 x_source => 'BSC_COMMON_DIM_LEVELS_PUB',
540 x_mode => 'I');
541 commit;
542 */
543 EXCEPTION
544 WHEN FND_API.G_EXC_ERROR THEN
545 FND_MSG_PUB.Count_And_Get
546 ( p_encoded => FND_API.G_FALSE
547 , p_count => x_msg_count
548 , p_data => x_msg_data
549 );
550 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
551 x_return_status := FND_API.G_RET_STS_ERROR;
552 RAISE;
553 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
554 FND_MSG_PUB.Count_And_Get
555 ( p_encoded => FND_API.G_FALSE
556 , p_count => x_msg_count
557 , p_data => x_msg_data
558 );
559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
560 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
561 RAISE;
562 WHEN NO_DATA_FOUND THEN
563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
564 IF (x_msg_data IS NOT NULL) THEN
565 x_msg_data := x_msg_data||' -> BSC_COMMON_DIM_LEVELS_PUB.Find_Common_Dim_Levels ';
566 ELSE
567 x_msg_data := SQLERRM||' at BSC_COMMON_DIM_LEVELS_PUB.Find_Common_Dim_Levels ';
568 END IF;
569 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
570 RAISE;
571 WHEN OTHERS THEN
572 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
573 IF (x_msg_data IS NOT NULL) THEN
574 x_msg_data := x_msg_data||' -> BSC_COMMON_DIM_LEVELS_PUB.Find_Common_Dim_Levels ';
575 ELSE
576 x_msg_data := SQLERRM||' at BSC_COMMON_DIM_LEVELS_PUB.Find_Common_Dim_Levels ';
577 END IF;
578 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
579 RAISE;
580 END Find_Common_Dim_Levels;
581
582
583 /*-------------------------------------------------------------------------------------------------------------------
584 Retrieve_Common_Dim_Levels
585 -------------------------------------------------------------------------------------------------------------------*/
586 PROCEDURE Retrieve_Common_Dim_Levels(
587 p_commit IN varchar2 := FND_API.G_FALSE
588 ,p_Tab_Id IN number
589 ,x_Dim_Level_Tbl OUT NOCOPY BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Tbl_Type
590 ,x_return_status OUT NOCOPY varchar2
591 ,x_msg_count OUT NOCOPY number
592 ,x_msg_data OUT NOCOPY varchar2
593 ) IS
594
595 -- used
596
597 v_Dim_Level_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
598 v_Index number;
599 v_Parent_Dim_Level_Id number;
600
601
602 --Cursor to get The common Dimensions for the tab.
603 CURSOR c_Common_Levels IS
604 SELECT SL.LEVEL_TABLE_NAME,
605 CL.DIM_LEVEL_INDEX,
606 CL.PARENT_LEVEL_INDEX,
607 CL.DIM_LEVEL_ID,
608 CL.PARENT_DIM_LEVEL_ID
609 FROM BSC_SYS_DIM_LEVELS_B SL,
610 BSC_SYS_COM_DIM_LEVELS CL
611 WHERE CL.TAB_ID = p_Tab_Id
612 AND SL.DIM_LEVEL_ID (+) = CL.DIM_LEVEL_ID
613 ORDER BY CL.DIM_LEVEL_INDEX;
614
615 BEGIN
616 FND_MSG_PUB.Initialize;
620 v_Index := 0;
617 x_return_status := FND_API.G_RET_STS_SUCCESS;
618 --DBMS_OUTPUT.PUT_LINE('Begin Retrieve_Common_Dim_Levels');
619
621 v_Parent_Dim_Level_Id := -1;
622 OPEN c_Common_Levels;
623 LOOP
624 FETCH c_Common_Levels INTO v_Dim_Level_Rec.Bsc_Level_View_Name
625 ,v_Dim_Level_Rec.Bsc_Level_Index
626 ,v_Dim_Level_Rec.Bsc_Parent_Level_Index
627 ,v_Dim_Level_Rec.Bsc_Level_Id
628 ,v_Dim_Level_Rec.Bsc_Parent_Level_Id;
629 EXIT WHEN c_Common_Levels%NOTFOUND;
630 v_Index := v_Index + 1 ;
631 x_Dim_Level_Tbl(v_Index) := v_Dim_Level_Rec;
632
633 --DBMS_OUTPUT.PUT_LINE('Retrieve_Common_Dim_Levels v_Dim_Level_Rec.Bsc_Level_View_Name ' || v_Dim_Level_Rec.Bsc_Level_View_Name);
634 --DBMS_OUTPUT.PUT_LINE('Retrieve_Common_Dim_Levels v_Dim_Level_Rec.Bsc_Level_Index ' || v_Dim_Level_Rec.Bsc_Level_Index );
635
636 END LOOP;
637
638
639 --DBMS_OUTPUT.PUT_LINE('End Retrieve_Common_Dim_Levels');
640
641 EXCEPTION
642 WHEN FND_API.G_EXC_ERROR THEN
643 FND_MSG_PUB.Count_And_Get
644 ( p_encoded => FND_API.G_FALSE
645 , p_count => x_msg_count
646 , p_data => x_msg_data
647 );
648 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
649 x_return_status := FND_API.G_RET_STS_ERROR;
650 RAISE;
651 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
652 FND_MSG_PUB.Count_And_Get
653 ( p_encoded => FND_API.G_FALSE
654 , p_count => x_msg_count
655 , p_data => x_msg_data
656 );
657 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
658 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
659 RAISE;
660 WHEN NO_DATA_FOUND THEN
661 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
662 IF (x_msg_data IS NOT NULL) THEN
663 x_msg_data := x_msg_data||' -> BSC_COMMON_DIM_LEVELS_PUB.Retrieve_Common_Dim_Levels ';
664 ELSE
665 x_msg_data := SQLERRM||' at BSC_COMMON_DIM_LEVELS_PUB.Retrieve_Common_Dim_Levels ';
666 END IF;
667 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
668 RAISE;
669 WHEN OTHERS THEN
670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
671 IF (x_msg_data IS NOT NULL) THEN
672 x_msg_data := x_msg_data||' -> BSC_COMMON_DIM_LEVELS_PUB.Retrieve_Common_Dim_Levels ';
673 ELSE
674 x_msg_data := SQLERRM||' at BSC_COMMON_DIM_LEVELS_PUB.Retrieve_Common_Dim_Levels ';
675 END IF;
676 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
677 RAISE;
678 END Retrieve_Common_Dim_Levels;
679
680 /*-------------------------------------------------------------------------------------------------------------------
681 Check_Dim_Level_Default_Value
682 -------------------------------------------------------------------------------------------------------------------*/
683 PROCEDURE Check_Dim_Level_Default_Value(
684 p_commit IN varchar2 := FND_API.G_FALSE
685 ,p_Tab_Id IN number
686 ,x_return_status OUT NOCOPY varchar2
687 ,x_msg_count OUT NOCOPY number
688 ,x_msg_data OUT NOCOPY varchar2
689 ) IS
690
691 v_Common_Level_ReTrieved_Tbl BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Tbl_Type;
692 v_Dim_Level_Rec_R BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
693 v_Indicator number;
694 v_index number;
695
696 CURSOR c_KPIs IS
697 SELECT INDICATOR
698 FROM BSC_TAB_INDICATORS
699 WHERE TAB_ID = p_Tab_Id;
700
701 BEGIN
702 FND_MSG_PUB.Initialize;
703 x_return_status := FND_API.G_RET_STS_SUCCESS;
704 SAVEPOINT BSCChkDimLevDefPUB;
705
706 --DBMS_OUTPUT.PUT_LINE('Begin Check_Dim_Level_Default_Value');
707
708 -- Get the Common Dimension Level already define in the DB.
709 Retrieve_Common_Dim_Levels(p_commit, p_Tab_Id, v_Common_Level_ReTrieved_Tbl
710 ,x_return_status ,x_msg_count ,x_msg_data );
711
712 OPEN c_KPIs;
713 LOOP
714 FETCH c_KPIs INTO v_Indicator;
715 EXIT WHEN c_KPIs%NOTFOUND;
716
717 UPDATE BSC_KPI_DIM_LEVELS_B SET DEFAULT_VALUE = 'T'
718 WHERE INDICATOR = v_Indicator AND DEFAULT_VALUE Like 'D%';
719
720 for v_Index IN 1.. v_Common_Level_ReTrieved_Tbl.COUNT LOOP
721 v_Dim_Level_Rec_R := v_Common_Level_ReTrieved_Tbl(v_Index);
722 UPDATE BSC_KPI_DIM_LEVELS_B SET DEFAULT_VALUE = 'D' || v_Dim_Level_Rec_R.Bsc_Level_Index
723 WHERE INDICATOR = v_Indicator AND LEVEL_TABLE_NAME = v_Dim_Level_Rec_R.Bsc_Level_View_Name;
724
725 --DBMS_OUTPUT.PUT_LINE('Check_Dim_Level_Default_Value v_Indicator ' || v_Indicator);
726 --DBMS_OUTPUT.PUT_LINE('Check_Dim_Level_Default_Value v_Dim_Level_Rec_R.Bsc_Level_View_Name ' || v_Dim_Level_Rec_R.Bsc_Level_View_Name);
727 --DBMS_OUTPUT.PUT_LINE('Check_Dim_Level_Default_Value v_Dim_Level_Rec_R.Bsc_Level_Index ' || v_Dim_Level_Rec_R.Bsc_Level_Index );
728
729 end loop;
730
731 END LOOP;
732
733 if p_commit = FND_API.G_TRUE then
734 commit;
735 end if;
739 WHEN FND_API.G_EXC_ERROR THEN
736 --DBMS_OUTPUT.PUT_LINE('End Check_Dim_Level_Default_Value');
737
738 EXCEPTION
740 ROLLBACK TO BSCChkDimLevDefPUB;
741 FND_MSG_PUB.Count_And_Get
742 ( p_encoded => FND_API.G_FALSE
743 , p_count => x_msg_count
744 , p_data => x_msg_data
745 );
746 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
747 x_return_status := FND_API.G_RET_STS_ERROR;
748 RAISE;
749 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
750 ROLLBACK TO BSCChkDimLevDefPUB;
751 FND_MSG_PUB.Count_And_Get
752 ( p_encoded => FND_API.G_FALSE
753 , p_count => x_msg_count
754 , p_data => x_msg_data
755 );
756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
757 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
758 RAISE;
759 WHEN NO_DATA_FOUND THEN
760 ROLLBACK TO BSCChkDimLevDefPUB;
761 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
762 IF (x_msg_data IS NOT NULL) THEN
763 x_msg_data := x_msg_data||' -> BSC_COMMON_DIM_LEVELS_PUB.Check_Dim_Level_Default_Value ';
764 ELSE
765 x_msg_data := SQLERRM||' at BSC_COMMON_DIM_LEVELS_PUB.Check_Dim_Level_Default_Value ';
766 END IF;
767 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
768 RAISE;
769 WHEN OTHERS THEN
770 ROLLBACK TO BSCChkDimLevDefPUB;
771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
772 IF (x_msg_data IS NOT NULL) THEN
773 x_msg_data := x_msg_data||' -> BSC_COMMON_DIM_LEVELS_PUB.Check_Dim_Level_Default_Value ';
774 ELSE
775 x_msg_data := SQLERRM||' at BSC_COMMON_DIM_LEVELS_PUB.Check_Dim_Level_Default_Value ';
776 END IF;
777 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
778 RAISE;
779 END Check_Dim_Level_Default_Value;
780
781 /*******************************************************************
782 Name : Validate_List_Button
783 Description : This procedure will validate the common dim levels
784 within the tab.
785 This procedure can accept Kpi id and dim level id.
786 If kpi id passed then Check_Common_Dim_Levels is called.
787 If dimension obhect is passed then Check_Common_Dim_Levels_DL
788 will be called.
789 Inputs : p_Kpi_Id
790 p_Dim_Level_Id
791 Creator : ashankar 26-MAR-2004
792 Note: This API takes care of shared indicators also.So don't need to call
793 this API for shared indiactors.
794
795 The below API does the validation for the list buttons
796 Common Dimension are those which are common across the tabs.
797 If the tab contains 10 dimension sets, then all these dimension sets
798 must have the same dimension levels and in the same order.
799 The following is the Logic :-
800 1.First check if the indicator is already assigned to the tab.
801 2.if yes then get the tab id corresponding to the KPI.
802 3.call the common dimension level sanity test API.
803 4.Call the same logic for all the shared indiactors also
804
805 /******************************************************************/
806 PROCEDURE Validate_List_Button
807 (
808 p_Kpi_Id IN BSC_KPIS_B.indicator%TYPE := NULL
809 , p_Dim_Level_Id IN NUMBER := NULL
810 , x_return_status OUT NOCOPY VARCHAR2
811 , x_msg_count OUT NOCOPY NUMBER
812 , x_msg_data OUT NOCOPY VARCHAR2
813 )IS
814 l_Kpi_Id BSC_KPIS_B.indicator%TYPE;
815 l_count NUMBER;
816 l_tab_id BSC_TABS_B.Tab_Id%TYPE;
817
818 CURSOR c_kpi_ids IS
819 SELECT indicator
820 FROM BSC_KPIS_B
821 WHERE Source_Indicator = l_Kpi_Id
822 AND Prototype_Flag <> 2;
823
824 CURSOR c_tab_id IS
825 SELECT tab_id
826 FROM BSC_TAB_INDICATORS
827 WHERE indicator = l_Kpi_Id;
828
829 BEGIN
830 FND_MSG_PUB.Initialize;
831 x_return_status := FND_API.G_RET_STS_SUCCESS;
832
833 IF(p_Kpi_Id IS NOT NULL) THEN
834 l_Kpi_Id := p_Kpi_Id;
835 FOR cd IN c_tab_id LOOP
836 l_tab_id := cd.tab_id;
837 BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels
838 (
839 p_Tab_Id => l_tab_id
840 ,x_return_status => x_return_status
841 ,x_msg_count => x_msg_count
842 ,x_msg_data => x_msg_data
843 );
844 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
845 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.Create_Dim_Set Failed: at BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels');
846 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
847 END IF;
848 END LOOP;
849 /***************For Shared Indiactors ***********************************/
850
851 FOR cd IN c_kpi_ids LOOP
852 l_Kpi_Id := cd.indicator;
853
854 IF(c_tab_id%ISOPEN ) THEN
855 CLOSE c_tab_id;
856 END IF;
857
858 OPEN c_tab_id;
859 FETCH c_tab_id INTO l_tab_id;
860 EXIT WHEN c_tab_id%NOTFOUND;
861 CLOSE c_tab_id;
862
863 BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels
864 (
865 p_Tab_Id => l_tab_id
866 ,x_return_status => x_return_status
867 ,x_msg_count => x_msg_count
868 ,x_msg_data => x_msg_data
869 );
870 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
871 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.Create_Dim_Set Failed: at BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels');
872 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
873 END IF;
874 END LOOP;
875
876 ELSIF(p_Dim_Level_Id IS NOT NULL) THEN
877
878 BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels_DL
879 (
880 p_Dim_Level_Id => p_Dim_Level_Id
881 ,x_return_status => x_return_status
882 ,x_msg_count => x_msg_count
883 ,x_msg_data => x_msg_data
884 );
885 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
886 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.Create_Dim_Set Failed: at BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels');
887 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
888 END IF;
889 END IF;
890
891 EXCEPTION
892 WHEN FND_API.G_EXC_ERROR THEN
893 FND_MSG_PUB.Count_And_Get
894 ( p_encoded => FND_API.G_FALSE
895 , p_count => x_msg_count
896 , p_data => x_msg_data
897 );
898 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
899 x_return_status := FND_API.G_RET_STS_ERROR;
900 RAISE;
901 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
902 FND_MSG_PUB.Count_And_Get
903 ( p_encoded => FND_API.G_FALSE
904 , p_count => x_msg_count
905 , p_data => x_msg_data
906 );
907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
908 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
909 RAISE;
910 WHEN NO_DATA_FOUND THEN
911 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
912 IF (x_msg_data IS NOT NULL) THEN
913 x_msg_data := x_msg_data||' -> BSC_COMMON_DIM_LEVELS_PUB.Validate_List_Button ';
914 ELSE
915 x_msg_data := SQLERRM||' at BSC_COMMON_DIM_LEVELS_PUB.Validate_List_Button ';
916 END IF;
917 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
918 RAISE;
919 WHEN OTHERS THEN
920 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
921 IF (x_msg_data IS NOT NULL) THEN
922 x_msg_data := x_msg_data||' -> BSC_COMMON_DIM_LEVELS_PUB.Validate_List_Button ';
923 ELSE
924 x_msg_data := SQLERRM||' at BSC_COMMON_DIM_LEVELS_PUB.Validate_List_Button ';
925 END IF;
926 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
927 RAISE;
928 END Validate_List_Button;
929
930
931
932 END BSC_COMMON_DIM_LEVELS_PUB;
[Home] [Help]