[Home] [Help]
PACKAGE BODY: APPS.BSC_DIM_FILTERS_PVT
Source
1 PACKAGE BODY BSC_DIM_FILTERS_PVT AS
2 /* $Header: BSCVFDLB.pls 120.2 2007/02/23 10:42:43 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: This Package handle Common Dimension Level for Scorecards |
13 REM | |
14 REM | NOTES |
15 REM | 16-MAR-2004 WCANO Created. |
16 REM | 05-NOV-2004 ashankar fix bug 3459282 |
17 REM | Changed procedure Synch_Fiters_And_Kpi_Dim |
18 REM | 16-12-2006 PSOMESUL E#5678943 MIGRATE COMMON DIMENSIONS AND DIMENSION FILTERS TO SCORECARD DESIGNER|
19 REM +=======================================================================+
20 */
21
22 /*-------------------------------------------------------------------------------------------------------------------
23 PROCEDURE TO get THE LEVEL VIEW NAME FOR DIMENSION Filter
24 RETURN NULL WHEN THE filter does NOT EXITs IN THE tab FOR THE specIFict
25 DIMENSION objec
26 -------------------------------------------------------------------------------------------------------------------*/
27 FUNCTION Get_Filter_View_Name
28 ( p_Tab_Id NUMBER
29 , p_Dim_Level_Id NUMBER
30 ) RETURN VARCHAR2 IS
31 l_Cursor BSC_BIS_LOCKS_PUB.t_cursor;
32 l_Level_View_Nane BSC_SYS_FILTERS_VIEWS.level_view_name%TYPE;
33 BEGIN
34 SELECT Level_View_Name
35 INTO l_Level_View_Nane
36 FROM BSC_SYS_FILTERS_VIEWS
37 WHERE Source_Type = BSC_DIM_FILTERS_PUB.SOURCE_TYPE_TAB
38 AND Source_Code = p_Tab_Id
39 AND Dim_Level_Id = p_Dim_Level_Id;
40
41 RETURN l_Level_View_Nane;
42 EXCEPTION
43 WHEN OTHERS THEN
44 RETURN NULL;
45 END Get_Filter_View_Name;
46
47 /*-------------------------------------------------------------------------------------------------------------------
48 Check_Filters_Not_Apply:
49 This PROCEDURE will CHECK FOR filters that NOT apply ANY more TO THE tabs
50 It will made one OF THE NEXT options:
51 1. CHECK FOR a ALL THE DIMENSION object IN a specIFic tab WHEN p_Dim_Level_Id IS NULL AND p_Tab_Id IS NOT NULL
52 2. CHECK FOR a ALL THE DIMENSION object IN ALL THE tab WHEN p_Dim_Level_Id IS NULL AND p_Tab_Id IS NULL
53 -------------------------------------------------------------------------------------------------------------------*/
54 PROCEDURE Check_Filters_Not_Apply
55 ( p_Tab_Id IN NUMBER := NULL
56 , x_return_status OUT NOCOPY VARCHAR2
57 , x_msg_COUNT OUT NOCOPY NUMBER
58 , x_msg_data OUT NOCOPY VARCHAR2
59 ) IS
60 l_Filtered_Dim_Level NUMBER;
61 l_Tab_Id NUMBER;
62
63 CURSOR c_Filters_Not_Apply IS
64 SELECT TF.Dim_Level_Id
65 FROM BSC_SYS_FILTERS_VIEWS TF
66 WHERE TF.Source_Type = BSC_DIM_FILTERS_PUB.SOURCE_TYPE_TAB
67 AND TF.Source_Code = p_Tab_Id
68 AND TF.Dim_Level_Id NOT IN
69 ( SELECT SL.Dim_Level_Id
70 FROM BSC_TAB_INDICATORS TI
71 , BSC_KPI_DIM_LEVELS_B K
72 , BSC_SYS_DIM_LEVELS_B SL
73 WHERE SL.SOURCE <> 'PMF'
74 AND TI.Tab_Id = p_Tab_Id
75 AND K.INDICATOR = TI.INDICATOR
76 AND SL.Level_Table_Name = K.Level_Table_Name
77 );
78
79 BEGIN
80 SAVEPOINT CheckFiltersNotApplyPvt;
81 --DBMS_OUTPUT.PUT_LINE('BEGIN Check_Filters_Not_Apply' );
82 ----DBMS_OUTPUT.PUT_LINE('Check_Filters_Not_Apply p_Tab_Id = ' || p_Tab_Id );
83
84 x_return_status := FND_API.G_RET_STS_SUCCESS;
85
86 IF (p_Tab_Id IS NOT NULL) THEN
87 -- Find Tab Dim Levels
88 OPEN c_Filters_Not_Apply;
89 ----DBMS_OUTPUT.PUT_LINE(' OPENed cursor c_Filters_Not_Apply ');
90 LOOP
91 FETCH c_Filters_Not_Apply
92 INTO l_Filtered_Dim_Level;
93
94 EXIT WHEN c_Filters_Not_Apply%NOTFOUND;
95 ----DBMS_OUTPUT.PUT_LINE('FETCH l_Filtered_Dim_Level : = ' || l_Filtered_Dim_Level );
96 --INSERT INTO TESTBUG values('FETCH l_Filtered_Dim_Level p_Tab_Id-->'|| to_char(p_Tab_Id));
97 --INSERT INTO TESTBUG values('FETCH l_Filtered_Dim_Level-->'|| to_char(l_Filtered_Dim_Level));
98 --commit;
99
100 Drop_Filter (
101 p_Tab_Id => p_Tab_Id
102 , p_Dim_Level_Id => l_Filtered_Dim_Level
103 , x_return_status => x_return_status
104 , x_msg_COUNT => x_msg_COUNT
105 , x_msg_data => x_msg_data
106 );
107 END LOOP;
108
109 CLOSE c_Filters_Not_Apply;
110 END IF;
111 ----DBMS_OUTPUT.PUT_LINE('END Check_Filters_Not_Apply' );
112 EXCEPTION
113 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
114 IF (c_Filters_Not_Apply%ISOPEN) THEN
115 CLOSE c_Filters_Not_Apply;
116 END IF;
117 ROLLBACK TO CheckFiltersNotApplyPvt;
118 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
119 FND_MSG_PUB.COUNT_And_Get
120 ( p_encoded => FND_API.G_FALSE
121 , p_COUNT => x_msg_COUNT
122 , p_data => x_msg_data
123 );
124 RAISE;
125 WHEN OTHERS THEN
126 IF (c_Filters_Not_Apply%ISOPEN) THEN
127 CLOSE c_Filters_Not_Apply;
128 END IF;
129 ROLLBACK TO CheckFiltersNotApplyPvt;
130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
131 FND_MSG_PUB.COUNT_And_Get
132 ( p_encoded => FND_API.G_FALSE
133 , p_COUNT => x_msg_COUNT
134 , p_data => x_msg_data
135 );
136 RAISE;
137 END Check_Filters_Not_Apply;
138
139 /*-------------------------------------------------------------------------------------------------------------------
140 Drop_Filter :
141 DELETE a Filter metadata AND filter VIEW object
142 AND CHECK IF EXISTS ANY filter FOR a CHILD DIMENSION IN ORDER TO
143 deleted OR recreated. (BY now it will be DELETE. Later will be more intalligent
144 -------------------------------------------------------------------------------------------------------------------*/
145 PROCEDURE Drop_Filter
146 ( p_Tab_Id IN NUMBER
147 , p_Dim_Level_Id IN NUMBER
148 , x_return_status OUT NOCOPY VARCHAR2
149 , x_msg_count OUT NOCOPY NUMBER
150 , x_msg_data OUT NOCOPY VARCHAR2
151 ) IS
152 l_Count NUMBER;
153 l_sql VARCHAR2(500);
154 l_Child_Dim_Level_Id NUMBER;
155 l_Filter_Level_View_Name BSC_SYS_FILTERS_VIEWS.level_view_name%TYPE;
156 l_count_filter_values NUMBER;
157
158 -- Cursor for get child dimension levels
159 CURSOR c_child_dim_obj IS
160 SELECT Dim_Level_Id
161 FROM BSC_SYS_DIM_LEVEL_RELS
162 WHERE Parent_Dim_Level_Id = p_Dim_Level_Id
163 AND Relation_Type = 1;
164
165 -- Cursor Velidate if the child has specifict values
166 -- difined
167
168 CURSOR c_count_filter_values IS
169 SELECT COUNT(A.DIM_LEVEL_VALUE)
170 FROM BSC_SYS_FILTERS A
171 WHERE A.SOURCE_TYPE = 1
172 AND A.SOURCE_CODE = p_Tab_Id
173 AND A.DIM_LEVEL_ID = l_Child_Dim_Level_Id;
174
175 BEGIN
176 --DBMS_OUTPUT.PUT_LINE('BEGIN Drop_Filter' );
177 --DBMS_OUTPUT.PUT_LINE('Drop_Filter p_Tab_Id = ' || p_Tab_Id );
178 --DBMS_OUTPUT.PUT_LINE('Drop_Filter p_Dim_Level_Id = ' || p_Dim_Level_Id );
179
180 x_return_status := FND_API.G_RET_STS_SUCCESS;
181
182 l_Filter_Level_View_Name := BSC_DIM_FILTERS_PVT.Get_Filter_View_Name(p_Tab_Id, p_Dim_Level_Id);
183
184 --DBMS_OUTPUT.PUT_LINE(' Drop_Filter l_Filter_Level_View_Name = ' || l_Filter_Level_View_Name );
185
186 IF (l_Filter_Level_View_Name IS NOT NULL) THEN
187 --------------------------------------------------------------
188 -- Cascading delete for child dimension Levels Filters
189 -- when the child filter it just a extension of the parent filter
190 --------------------------------------------------------------
191 OPEN c_child_dim_obj;
192 --DBMS_OUTPUT.PUT_LINE(' OPENed c_child_dim_obj');
193 LOOP
194 FETCH c_child_dim_obj
195 INTO l_Child_Dim_Level_Id;
196
197 EXIT WHEN c_child_dim_obj%NOTFOUND;
198 --DBMS_OUTPUT.PUT_LINE(' call Drop_Filter for l_Child_Dim_Level_Id = ' || l_Child_Dim_Level_Id );
199
200 OPEN c_count_filter_values;
201 FETCH c_count_filter_values INTO l_count_filter_values;
202 CLOSE c_count_filter_values;
203
204 if l_count_filter_values = 0 then
205 Drop_Filter
206 ( p_Tab_Id => p_Tab_Id
207 , p_Dim_Level_Id => l_Child_Dim_Level_Id
208 , x_return_status => x_return_status
209 , x_msg_COUNT => x_msg_COUNT
210 , x_msg_data => x_msg_data
211 );
212 end if;
213
214 END LOOP;
215 CLOSE c_child_dim_obj;
216
217 -- Save point for the current filters view
218 SAVEPOINT BcsFiltersPubDeleteFilterView;
219 --DBMS_OUTPUT.PUT_LINE(' SAVEPOINT BcsFiltersPubDeleteFilterView ');
220 --DBMS_OUTPUT.PUT_LINE(' Drop_Filter p_Tab_Id = ' || p_Tab_Id );
221 --DBMS_OUTPUT.PUT_LINE(' Drop_Filter p_Dim_Level_Id = ' || p_Dim_Level_Id );
222
223 Drop_Filter_Objects (
224 p_Tab_Id => p_Tab_Id
225 , p_Dim_Level_Id => p_Dim_Level_Id
226 , x_return_status => x_return_status
227 , x_msg_COUNT => x_msg_COUNT
228 , x_msg_data => x_msg_data
229 );
230
231 END IF;
232
233 --DBMS_OUTPUT.PUT_LINE('END Drop_Filter p_Tab_Id = ' || p_Tab_Id || ' p_Dim_Level_Id = ' || p_Dim_Level_Id );
234 EXCEPTION
235 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
236 IF (c_child_dim_obj%ISOPEN) THEN
237 CLOSE c_child_dim_obj;
238 END IF;
239 ROLLBACK TO BcsFiltersPubDeleteFilterView;
240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
241 FND_MSG_PUB.COUNT_And_Get
242 ( p_encoded => FND_API.G_FALSE
243 , p_COUNT => x_msg_COUNT
244 , p_data => x_msg_data
245 );
246 RAISE;
247 WHEN OTHERS THEN
248 IF (c_child_dim_obj%ISOPEN) THEN
249 CLOSE c_child_dim_obj;
250 END IF;
251 ROLLBACK TO BcsFiltersPubDeleteFilterView;
252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
253 IF (x_msg_data IS NOT NULL) THEN
254 x_msg_data := x_msg_data||' -> BSC_DIM_FILTERS_PVT.Drop_Filter';
255 ELSE
256 x_msg_data := SQLERRM||' at BSC_DIM_FILTERS_PVT.Drop_Filter';
257 END IF;
258 RAISE;
259 END Drop_Filter;
260
261 /*-------------------------------------------------------------------------------------------------------------------
262 Drop_Filter :
263 DELETE a Filter metadata AND filter VIEW object
264 -------------------------------------------------------------------------------------------------------------------*/
265 PROCEDURE Drop_Filter_Objects
266 ( p_Tab_Id IN NUMBER
267 , p_Dim_Level_Id IN NUMBER
268 , x_return_status OUT NOCOPY VARCHAR2
269 , x_msg_COUNT OUT NOCOPY NUMBER
270 , x_msg_data OUT NOCOPY VARCHAR2
271 ) IS
272 l_Count NUMBER;
273 l_sql VARCHAR2(500);
274 l_Child_Dim_Level_Id NUMBER;
275 l_Filter_Level_View_Name BSC_SYS_FILTERS_VIEWS.level_view_name%TYPE;
276
277 BEGIN
278 ----DBMS_OUTPUT.PUT_LINE('BEGIN Drop_Filter' );
279 ----DBMS_OUTPUT.PUT_LINE('Drop_Filter p_Tab_Id = ' || p_Tab_Id );
280 ----DBMS_OUTPUT.PUT_LINE('Drop_Filter p_Dim_Level_Id = ' || p_Dim_Level_Id );
281
282 x_return_status := FND_API.G_RET_STS_SUCCESS;
283 -- Save point for the current filters view
284 SAVEPOINT BcsFiltersPvtDeleteFilterView;
285
286 l_Filter_Level_View_Name := Get_Filter_View_Name(p_Tab_Id, p_Dim_Level_Id);
287
288 ----DBMS_OUTPUT.PUT_LINE(' Drop_Filter l_Filter_Level_View_Name = ' || l_Filter_Level_View_Name );
289 IF (l_Filter_Level_View_Name IS NOT NULL) THEN
290
291 ----DBMS_OUTPUT.PUT_LINE(' SAVEPOINT BcsFiltersPvtDeleteFilterView ');
292 ----DBMS_OUTPUT.PUT_LINE(' Drop_Filter p_Tab_Id = ' || p_Tab_Id );
293 ----DBMS_OUTPUT.PUT_LINE(' Drop_Filter p_Dim_Level_Id = ' || p_Dim_Level_Id );
294
295 --Delete Filter Level View metadata
296 DELETE FROM BSC_SYS_FILTERS_VIEWS
297 WHERE Source_Type = BSC_DIM_FILTERS_PUB.SOURCE_TYPE_TAB
298 AND Source_Code = p_Tab_Id
299 AND Dim_Level_Id = p_Dim_Level_Id;
300
301 ----DBMS_OUTPUT.PUT_LINE(' DELETE FROM BSC_SYS_FILTERS_VIEWS ' );
302
303 -- Delete Filter Level Values metadata
304 DELETE FROM BSC_SYS_FILTERS
305 WHERE Source_Type = BSC_DIM_FILTERS_PUB.SOURCE_TYPE_TAB
306 AND Source_Code = p_Tab_Id
307 AND Dim_Level_Id = p_Dim_Level_Id;
308
309 ----DBMS_OUTPUT.PUT_LINE(' BSC_SYS_FILTERS ' );
310
311 -- Syscronize Filters metadata with KPI dim obj metadat
312 Synch_Fiters_And_Kpi_Dim
313 ( p_Tab_Id => p_Tab_Id
314 , x_return_status => x_return_status
315 , x_msg_COUNT => x_msg_COUNT
316 , x_msg_data => x_msg_data
317 );
318 -------------------------------------------
319 -- Drop View Object
320 --------------------------------------------
321 -- sql_to_validate IF the filter view exists
322 SELECT COUNT(OBJECT_NAME)
323 INTO l_Count
324 FROM USER_OBJECTS
325 WHERE OBJECT_NAME = l_Filter_Level_View_Name ;
326
327 -- IF COUNT <> 0 means view exists and must to be delteted
328 IF (l_Count <> 0) THEN
329 -- sql_to_drop_view
330 l_sql:= 'DROP VIEW ' ||l_Filter_Level_View_Name ;
331 BSC_APPS.Init_Bsc_Apps;
332 --BSC_APPS.do_ddl(l_sql,x_statement_type, l_Filter_Level_View_Name);
333 BSC_APPS.Execute_DDL(l_sql);
334 --BSC_APPS.DO_DDL_AT(l_sql, ad_ddl.drop_view, l_Filter_Level_View_Name,
335 -- BSC_APPS.fnd_apps_schema, BSC_APPS.bsc_apps_short_name);
336 -- we need to commit after delete the view to ensure the View objects match
337 -- with the metadata defined for the View.
338 ----DBMS_OUTPUT.PUT_LINE(' Deleted Filter View : ' || l_Filter_Level_View_Name );
339 END IF;
340 --------------------------------------------
341 END IF;
342
343 ----DBMS_OUTPUT.PUT_LINE('END Drop_Filter p_Tab_Id = ' || p_Tab_Id || ' p_Dim_Level_Id = ' || p_Dim_Level_Id );
344 EXCEPTION
345 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
346 ROLLBACK TO BcsFiltersPvtDeleteFilterView;
347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
348 FND_MSG_PUB.COUNT_And_Get
349 ( p_encoded => FND_API.G_FALSE
350 , p_COUNT => x_msg_COUNT
351 , p_data => x_msg_data
352 );
353 RAISE;
354 WHEN OTHERS THEN
355 ROLLBACK TO BcsFiltersPvtDeleteFilterView;
356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
357 IF (x_msg_data IS NOT NULL) THEN
358 x_msg_data := x_msg_data||' -> BSC_DIM_FILTERS_PVT.Drop_Filter_Objects';
359 ELSE
360 x_msg_data := SQLERRM||' at BSC_DIM_FILTERS_PVT.Drop_Filter_Objects';
361 END IF;
362 RAISE;
363 END Drop_Filter_Objects;
364
365 PROCEDURE Synch_Fiters_And_Kpi_Dim
366 ( p_Tab_Id IN NUMBER
367 , x_return_status OUT NOCOPY VARCHAR2
368 , x_msg_COUNT OUT NOCOPY NUMBER
369 , x_msg_data OUT NOCOPY VARCHAR2
370 ) IS
371 l_indicator NUMBER;
372 l_kpi_flag NUMBER;
373
374 l_Sys_Table_Name BSC_SYS_DIM_LEVELS_B.Level_Table_Name%TYPE;
375 l_Sys_View_Name BSC_SYS_DIM_LEVELS_B.Level_View_Name%TYPE;
376 l_Kpi_View_Name BSC_KPI_DIM_LEVELS_B.Level_View_Name%TYPE;
377 l_New_View_Name BSC_KPI_DIM_LEVELS_B.Level_View_Name%TYPE;
378
379 -- CURSOT to get the KPI Dimension Levels that need to be synchronize with the
380 -- tab dimension filters
381
382 CURSOR c_Kpi_Dim_Obj_To_Synch IS
383 SELECT DISTINCT KD.INDICATOR --Distinct need it
384 , SD.Level_Table_Name SYS_TABLE
385 , SD.Level_View_Name SYS_VIEW
386 , KD.Level_View_Name KPI_VIEW
387 , NVL(FV.Level_View_Name, SD.Level_View_Name) NEW_VIEW
388 FROM BSC_TAB_INDICATORS TI
389 , BSC_KPI_DIM_LEVELS_B KD
390 , BSC_SYS_DIM_LEVELS_B SD
391 , (
392 SELECT *
393 FROM BSC_SYS_FILTERS_VIEWS A
394 WHERE A.Source_Type = BSC_DIM_FILTERS_PUB.SOURCE_TYPE_TAB
395 AND A.Source_Code = p_Tab_Id
396 ) FV
397 WHERE TI.Tab_Id = p_Tab_Id
398 AND KD.INDICATOR = TI.INDICATOR
399 AND KD.Level_Table_Name = SD.Level_Table_Name
400 AND FV.Level_Table_Name(+) = KD.Level_Table_Name
401 AND ( NVL(FV.Level_View_Name, SD.Level_View_Name) <> KD.Level_View_Name
402 -- OR KD.Level_View_Name IS NULL
403 );
404
405 -- Cursor to syncronize KPI NOT assigned to any scorecard:
406
407 CURSOR c_Kpi_Dim_Obj_To_Synch2 IS
408 SELECT DISTINCT KD.INDICATOR --Distinct need it
409 , SD.Level_Table_Name SYS_TABLE
410 , SD.Level_View_Name SYS_VIEW
411 , KD.Level_View_Name KPI_VIEW
412 FROM BSC_TAB_INDICATORS TI
413 , BSC_KPI_DIM_LEVELS_B KD
414 , BSC_SYS_DIM_LEVELS_B SD
415 WHERE KD.INDICATOR = TI.INDICATOR (+)
416 AND TI.Tab_Id IS NULL
417 AND KD.Level_Table_Name = SD.Level_Table_Name
418 AND KD.Level_View_Name <> SD.Level_View_Name;
419
420 BEGIN
421
422 x_return_status := FND_API.G_RET_STS_SUCCESS;
423 SAVEPOINT BcsFiltersPvtSynchKpiDim;
424 ----DBMS_OUTPUT.PUT_LINE('BEGIN Synch_Fiters_And_Kpi_Dim' );
425
426 IF p_Tab_Id IS NOT NULL THEN
427
428 OPEN c_Kpi_Dim_Obj_To_Synch;
429 ----DBMS_OUTPUT.PUT_LINE(' OPEN c_Kpi_Dim_Obj_To_Synch; ');
430 LOOP
431 FETCH c_Kpi_Dim_Obj_To_Synch
432 INTO l_indicator
433 , l_Sys_Table_Name
434 , l_Sys_View_Name
435 , l_Kpi_View_Name
436 , l_New_View_Name;
437
438 EXIT WHEN c_Kpi_Dim_Obj_To_Synch%NOTFOUND;
439
440 -- update table BSC_KPI_DIM_LEVELS_B
441 UPDATE BSC_KPI_DIM_LEVELS_B
442 SET Level_View_Name = l_New_View_Name
443 WHERE INDICATOR = l_indicator
444 AND Level_Table_Name = l_Sys_Table_Name;
445
446 ----DBMS_OUTPUT.PUT_LINE(' Upated BSC_KPI_DIM_LEVELS_B for INDICATOR = ' || l_indicator || ' AND LEVEL_TABLE_NAME = ' || l_New_View_Name );
447
448 -- Update KPI Prototype flag
449 IF (l_Kpi_View_Name = l_Sys_View_Name) THEN
450 --It change from No-Filter To Filter
451 l_kpi_flag := 1;
452 ELSIF (l_New_View_Name = l_Sys_View_Name) THEN
453 -- It change from To Filter to No-Filter
454 l_kpi_flag := 1;
455 ELSE
456 -- It change from To Filter to Filter
457 l_kpi_flag := 6;
458 END IF;
459 BSC_DESIGNER_PVT.ActionFlag_Change
460 ( x_indicator => l_indicator
461 , x_newflag => l_kpi_flag
462 );
463 ----DBMS_OUTPUT.PUT_LINE( ' flag 2' );
464 END LOOP;
465 CLOSE c_Kpi_Dim_Obj_To_Synch;
466
467 ELSE
468
469 OPEN c_Kpi_Dim_Obj_To_Synch2;
470 ----DBMS_OUTPUT.PUT_LINE(' OPEN c_Kpi_Dim_Obj_To_Synch2; ');
471 LOOP
472 FETCH c_Kpi_Dim_Obj_To_Synch2
473 INTO l_indicator
474 , l_Sys_Table_Name
475 , l_Sys_View_Name
476 , l_Kpi_View_Name;
477
478 EXIT WHEN c_Kpi_Dim_Obj_To_Synch2%NOTFOUND;
479
480 -- update table BSC_KPI_DIM_LEVELS_B
481 UPDATE BSC_KPI_DIM_LEVELS_B
482 SET Level_View_Name = l_Sys_View_Name
483 WHERE INDICATOR = l_indicator
484 AND Level_Table_Name = l_Sys_Table_Name;
485
486 ----DBMS_OUTPUT.PUT_LINE(' Upated BSC_KPI_DIM_LEVELS_B for INDICATOR = ' || l_indicator || ' AND LEVEL_TABLE_NAME = ' || l_Sys_View_Name );
487 END LOOP;
488 CLOSE c_Kpi_Dim_Obj_To_Synch2;
489
490 END IF;
491
492 ----DBMS_OUTPUT.PUT_LINE('END Synch_Fiters_And_Kpi_Dim' );
493 EXCEPTION
494 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
495 IF (c_Kpi_Dim_Obj_To_Synch%ISOPEN) THEN
496 CLOSE c_Kpi_Dim_Obj_To_Synch;
497 END IF;
498 IF (c_Kpi_Dim_Obj_To_Synch2%ISOPEN) THEN
499 CLOSE c_Kpi_Dim_Obj_To_Synch;
500 END IF;
501 ROLLBACK TO BcsFiltersPvtSynchKpiDim;
502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
503 FND_MSG_PUB.COUNT_And_Get
504 ( p_encoded => FND_API.G_FALSE
505 , p_COUNT => x_msg_COUNT
506 , p_data => x_msg_data
507 );
508 RAISE;
509 WHEN OTHERS THEN
510 IF (c_Kpi_Dim_Obj_To_Synch%ISOPEN) THEN
511 CLOSE c_Kpi_Dim_Obj_To_Synch;
512 END IF;
513 IF (c_Kpi_Dim_Obj_To_Synch%ISOPEN) THEN
514 CLOSE c_Kpi_Dim_Obj_To_Synch2;
515 END IF;
516 ROLLBACK TO BcsFiltersPvtSynchKpiDim;
517 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
518 IF (x_msg_data IS NOT NULL) THEN
519 x_msg_data := x_msg_data ||' -> BSC_DIM_FILTERS_PVT.Synch_Fiters_And_Kpi_Dim ';
520 ELSE
521 x_msg_data := SQLERRM ||' at BSC_DIM_FILTERS_PVT.Synch_Fiters_And_Kpi_Dim ';
522 END IF;
523 RAISE;
524 END Synch_Fiters_And_Kpi_Dim;
525
526
527 END BSC_DIM_FILTERS_PVT;