[Home] [Help]
PACKAGE BODY: APPS.BSC_DIM_FILTERS_PUB
Source
1 PACKAGE BODY BSC_DIM_FILTERS_PUB AS
2 /* $Header: BSCPFDLB.pls 120.4 2007/02/23 10:41:26 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 | 12-APR-2004 PAJOHRI Bug #3426566, added a new function |
17 REM | Get_Filter_View_Name |
18 REM | 16-12-2006 PSOMESUL E#5678943 MIGRATE COMMON DIMENSIONS AND DIMENSION FILTERS TO SCORECARD DESIGNER|
19 REM +=======================================================================+
20 */
21
22 /*-----------------------------------------------------------------------------
23 Check_Filters_Not_Apply:
24 This procedure will check for filters that NOT apply any more to the tabs
25 It will made one of the next options:
26 1. Check for a all the dimension object in a specIFic tab
27 WHEN p_Dim_Level_Id IS NULL and p_Tab_Id IS NOT NULL
28 -----------------------------------------------------------------------------*/
29 PROCEDURE Check_Filters_Not_Apply
30 ( p_Tab_Id IN NUMBER := NULL
31 , x_return_status OUT NOCOPY VARCHAR2
32 , x_msg_COUNT OUT NOCOPY NUMBER
33 , x_msg_data OUT NOCOPY VARCHAR2
34 ) IS
35 l_Tab_Id NUMBER;
36 -- Cursor to get all tab with filter views
37 CURSOR c_Tabs_With_Filters IS
38 SELECT DISTINCT Source_Code -- Distinct need it
39 FROM BSC_SYS_FILTERS_VIEWS
40 WHERE Source_Type = BSC_DIM_FILTERS_PUB.SOURCE_TYPE_TAB;
41
42 BEGIN
43 SAVEPOINT CheckFiltersNotApply;
44 --DBMS_OUTPUT.PUT_LINE('BEGIN Check_Filters_Not_Apply' );
45 --DBMS_OUTPUT.PUT_LINE('Check_Filters_Not_Apply p_Tab_Id = ' || p_Tab_Id );
46
47 x_return_status := FND_API.G_RET_STS_SUCCESS;
48
49 IF (p_Tab_Id IS NOT NULL) THEN
50
51 --INSERT INTO TESTBUG values('Check_Filters_Not_Apply pub-->'|| to_char(p_Tab_Id));
52 --commit;
53
54 BSC_DIM_FILTERS_PVT.Check_Filters_Not_Apply
55 ( p_Tab_Id => p_Tab_Id
56 ,x_return_status => x_return_status
57 ,x_msg_COUNT => x_msg_COUNT
58 ,x_msg_data => x_msg_data
59 );
60 ELSE
61 -- SQL to the the tabs with filters:
62 OPEN c_tabs_with_filters;
63 LOOP
64 FETCH c_tabs_with_filters
65 INTO l_Tab_Id;
66
67 EXIT WHEN c_tabs_with_filters%NOTFOUND;
68 BSC_DIM_FILTERS_PVT.Check_Filters_Not_Apply
69 ( p_Tab_Id => l_Tab_Id
70 ,x_return_status => x_return_status
71 ,x_msg_COUNT => x_msg_COUNT
72 ,x_msg_data => x_msg_data
73 );
74 END LOOP;
75 CLOSE c_tabs_with_filters;
76 END IF;
77 --DBMS_OUTPUT.PUT_LINE('END Check_Filters_Not_Apply' );
78 EXCEPTION
79 /*
80 WHEN FND_API.G_EXC_ERROR THEN
81 IF (c_tabs_with_filters%ISOPEN) THEN
82 CLOSE c_tabs_with_filters;
83 END IF;
84 ROLLBACK TO CheckFiltersNotApply;
85 x_return_status := FND_API.G_RET_STS_ERROR;
86 FND_MSG_PUB.COUNT_And_Get
87 ( p_encoded => FND_API.G_FALSE
88 , p_COUNT => x_msg_COUNT
89 , p_data => x_msg_data
90 );
91 RAISE;
92 */
93 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
94 IF (c_tabs_with_filters%ISOPEN) THEN
95 CLOSE c_tabs_with_filters;
96 END IF;
97 ROLLBACK TO CheckFiltersNotApply;
98 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
99 FND_MSG_PUB.COUNT_And_Get
100 ( p_encoded => FND_API.G_FALSE
101 , p_COUNT => x_msg_COUNT
102 , p_data => x_msg_data
103 );
104 RAISE;
105 WHEN OTHERS THEN
106 IF (c_tabs_with_filters%ISOPEN) THEN
107 CLOSE c_tabs_with_filters;
108 END IF;
109 ROLLBACK TO CheckFiltersNotApply;
110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
111 FND_MSG_PUB.COUNT_And_Get
112 ( p_encoded => FND_API.G_FALSE
113 , p_COUNT => x_msg_COUNT
114 , p_data => x_msg_data
115 );
116 RAISE;
117 END Check_Filters_Not_Apply;
118
119 PROCEDURE Check_Filters_Not_Apply_By_KPI
120 ( p_Kpi_Id IN BSC_KPIS_B.Indicator%TYPE
121 , x_return_status OUT NOCOPY VARCHAR2
122 , x_msg_count OUT NOCOPY NUMBER
123 , x_msg_data OUT NOCOPY VARCHAR2
124 ) IS
125 CURSOR c_Filters_Tab IS
126 SELECT DISTINCT C.Source_Code
127 FROM BSC_TAB_INDICATORS A
128 , BSC_KPIS_B B
129 , BSC_SYS_FILTERS_VIEWS C
130 WHERE A.Indicator = B.Indicator
131 AND C.Source_Type = BSC_DIM_FILTERS_PUB.Source_Type_Tab
132 AND C.Source_Code = A.Tab_Id
133 AND ((B.Indicator = p_kpi_id) OR (B.Source_Indicator = p_kpi_id));
134
135 BEGIN
136 --DBMS_OUTPUT.PUT_LINE('Entered inside BSC_BIS_KPI_MEAS_PUB.Check_Filters_Not_Apply Procedure');
137 x_return_status := FND_API.G_RET_STS_SUCCESS;
138
139 FOR cd IN c_Filters_Tab LOOP
140
141 --INSERT INTO TESTBUG values('Now deleting -->'|| to_char(cd.Source_Code));
142 --commit;
143
144 BSC_DIM_FILTERS_PUB.Check_Filters_Not_Apply
145 ( p_Tab_Id => cd.Source_Code
146 , x_return_status => x_return_status
147 , x_msg_count => x_msg_count
148 , x_msg_data => x_msg_data
149 );
150 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
151 --DBMS_OUTPUT.PUT_LINE('BSC_BIS_KPI_MEAS_PUB.Check_Filters_Not_Apply Failed: at BSC_DIM_FILTERS_PUB.Check_Filters_Not_Apply');
152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
153 END IF;
154 END LOOP;
155
156 --DBMS_OUTPUT.PUT_LINE('Exiting from BSC_BIS_KPI_MEAS_PUB.Check_Filters_Not_Apply Procedure');
157 EXCEPTION
158 WHEN FND_API.G_EXC_ERROR THEN
159 IF (x_msg_data IS NULL) THEN
160 FND_MSG_PUB.Count_And_Get
161 ( p_encoded => FND_API.G_FALSE
162 , p_count => x_msg_count
163 , p_data => x_msg_data
164 );
165 END IF;
166 x_return_status := FND_API.G_RET_STS_ERROR;
167 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
168 IF (x_msg_data IS NULL) THEN
169 FND_MSG_PUB.Count_And_Get
170 ( p_encoded => FND_API.G_FALSE
171 , p_count => x_msg_count
172 , p_data => x_msg_data
173 );
174 END IF;
175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
176 WHEN OTHERS THEN
177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
178 IF (x_msg_data IS NULL) THEN
179 x_msg_data := x_msg_data||' -> BSC_DIM_FILTERS_PUB.Check_Filters_Not_Apply_By_KPI ';
180 ELSE
181 x_msg_data := SQLERRM||' at BSC_DIM_FILTERS_PUB.Check_Filters_Not_Apply_By_KPI ';
182 END IF;
183 END Check_Filters_Not_Apply_By_KPI;
184
185 /*-------------------------------------------------------------------------------------------------------------------
186 Drop_Filter :
187 Delete a Filter View a and make cascading delete for child dimension Filter views
188 -------------------------------------------------------------------------------------------------------------------*/
189 PROCEDURE Drop_Filter
190 ( p_Tab_Id IN NUMBER
191 , p_Dim_Level_Id IN NUMBER
192 , x_return_status OUT NOCOPY VARCHAR2
193 , x_msg_COUNT OUT NOCOPY NUMBER
194 , x_msg_data OUT NOCOPY VARCHAR2
195 ) IS
196
197 BEGIN
198
199 BSC_DIM_FILTERS_PVT.Drop_Filter (
200 p_Tab_Id => p_Tab_Id
201 , p_Dim_Level_Id => p_Dim_Level_Id
202 , x_return_status => x_return_status
203 , x_msg_COUNT => x_msg_COUNT
204 , x_msg_data => x_msg_data
205 );
206
207
208 --DBMS_OUTPUT.PUT_LINE('END Drop_Filter p_Tab_Id = ' || p_Tab_Id || ' p_Dim_Level_Id = ' || p_Dim_Level_Id );
209 EXCEPTION
210 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
212 FND_MSG_PUB.COUNT_And_Get
213 ( p_encoded => FND_API.G_FALSE
214 , p_COUNT => x_msg_COUNT
215 , p_data => x_msg_data
216 );
217 RAISE;
218 WHEN OTHERS THEN
219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
220 IF (x_msg_data IS NOT NULL) THEN
221 x_msg_data := x_msg_data||' -> BSC_DIM_FILTERS_PUB.Drop_Filter';
222 ELSE
223 x_msg_data := SQLERRM||' at BSC_DIM_FILTERS_PUB.Drop_Filter';
224 END IF;
225 RAISE;
226 END Drop_Filter;
227
228 PROCEDURE Synch_Fiters_And_Kpi_Dim
229 ( p_Tab_Id IN NUMBER
230 , x_return_status OUT NOCOPY VARCHAR2
231 , x_msg_COUNT OUT NOCOPY NUMBER
232 , x_msg_data OUT NOCOPY VARCHAR2
233 ) IS
234
235 BEGIN
236 SAVEPOINT BcsFiltersPubSynchKpiDim;
237
238 BSC_DIM_FILTERS_PVT.Synch_Fiters_And_Kpi_Dim
239 ( p_Tab_Id => p_Tab_Id
240 , x_return_status => x_return_status
241 , x_msg_COUNT => x_msg_COUNT
242 , x_msg_data => x_msg_data
243 );
244
245 --DBMS_OUTPUT.PUT_LINE('END Synch_Fiters_And_Kpi_Dim' );
246 EXCEPTION
247 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
248 ROLLBACK TO BcsFiltersPubSynchKpiDim;
249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
250 FND_MSG_PUB.COUNT_And_Get
251 ( p_encoded => FND_API.G_FALSE
252 , p_COUNT => x_msg_COUNT
253 , p_data => x_msg_data
254 );
255 RAISE;
256 WHEN OTHERS THEN
257 ROLLBACK TO BcsFiltersPubSynchKpiDim;
258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
259 IF (x_msg_data IS NOT NULL) THEN
260 x_msg_data := x_msg_data ||' -> BSC_DIM_FILTERS_PUB.Synch_Fiters_And_Kpi_Dim ';
261 ELSE
262 x_msg_data := SQLERRM ||' at BSC_DIM_FILTERS_PUB.Synch_Fiters_And_Kpi_Dim ';
263 END IF;
264 RAISE;
265 END Synch_Fiters_And_Kpi_Dim;
266
267
268 PROCEDURE Drop_Filter_By_Dim_Obj
269 ( p_Dim_Level_Id IN NUMBER
270 , x_return_status OUT NOCOPY VARCHAR2
271 , x_msg_COUNT OUT NOCOPY NUMBER
272 , x_msg_data OUT NOCOPY VARCHAR2
273 ) IS
274 l_tab_id NUMBER;
275
276 -- SQL to get the Tab_Ids where the dimension has filters
277 CURSOR c_Tabs_With_Current_Dim_Obj IS
278 SELECT Source_Code TAB_ID
279 FROM BSC_SYS_FILTERS_VIEWS
280 WHERE Dim_Level_Id = p_Dim_Level_Id;
281 BEGIN
282 --DBMS_OUTPUT.PUT_LINE(' BEGIN Drop_Filter_By_Dim_Obj ');
283 --DBMS_OUTPUT.PUT_LINE(' p_Dim_Level_Id = ' || p_Dim_Level_Id);
284 x_return_status := FND_API.G_RET_STS_SUCCESS;
285
286 SAVEPOINT BcsFiltersPubDelFilterViewBDO;
287
288 OPEN c_tabs_with_current_dim_obj;
289 LOOP
290 FETCH c_Tabs_With_Current_Dim_Obj
291 INTO l_Tab_Id;
292
293 EXIT WHEN c_tabs_with_current_dim_obj%NOTFOUND;
294
295 --DBMS_OUTPUT.PUT_LINE(' call Drop_Filter_By_Dim_Obj for Tab_Id = ' || l_Tab_Id);
296 BSC_DIM_FILTERS_PUB.Drop_Filter
297 ( p_Tab_Id => l_Tab_Id
298 , p_Dim_Level_Id => p_Dim_Level_Id
299 , x_return_status => x_return_status
300 , x_msg_COUNT => x_msg_COUNT
301 , x_msg_data => x_msg_data
302 );
303 END LOOP;
304 CLOSE c_tabs_with_current_dim_obj;
305 --DBMS_OUTPUT.PUT_LINE(' END Drop_Filter_By_Dim_Obj ');
306 EXCEPTION
307 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
308 IF (c_tabs_with_current_dim_obj%ISOPEN) THEN
309 CLOSE c_tabs_with_current_dim_obj;
310 END IF;
311 ROLLBACK TO BcsFiltersPubDelFilterViewBDO;
312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313 FND_MSG_PUB.COUNT_And_Get
314 ( p_encoded => FND_API.G_FALSE
315 , p_COUNT => x_msg_COUNT
316 , p_data => x_msg_data
317 );
318 RAISE;
319 WHEN OTHERS THEN
320 IF (c_tabs_with_current_dim_obj%ISOPEN) THEN
321 CLOSE c_tabs_with_current_dim_obj;
322 END IF;
323 ROLLBACK TO BcsFiltersPubDelFilterViewBDO;
324 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
325 IF (x_msg_data IS NOT NULL) THEN
326 x_msg_data := x_msg_data||' -> BSC_DIM_FILTERS_PUB.Drop_Filter_By_Dim_Obj';
327 ELSE
328 x_msg_data := SQLERRM||' at BSC_DIM_FILTERS_PUB.Drop_Filter_By_Dim_Obj';
329 END IF;
330 RAISE;
331 END Drop_Filter_By_Dim_Obj;
332
333
334 PROCEDURE Drop_Filter_By_Tab
335 ( p_Tab_Id IN NUMBER
336 , x_return_status OUT NOCOPY VARCHAR2
337 , x_msg_COUNT OUT NOCOPY NUMBER
338 , x_msg_data OUT NOCOPY VARCHAR2
339 ) IS
340
341 -- Cursors to get the Dim_Obj with firters in the current tab
342 CURSOR c_dim_filters IS
343 SELECT Dim_Level_Id
344 FROM BSC_SYS_FILTERS_VIEWS
345 WHERE Source_Code = p_Tab_Id;
346
347 l_Dim_Level_id NUMBER;
348 BEGIN
349 x_return_status := FND_API.G_RET_STS_SUCCESS;
350 SAVEPOINT BcsFiltersPubDelFilterViewBT;
351 --DBMS_OUTPUT.PUT_LINE(' BEGIN Drop_Filter_By_Tab ');
352 --DBMS_OUTPUT.PUT_LINE(' p_Tab_Id = ' || p_Tab_Id);
353
354 OPEN c_dim_filters;
355 LOOP
356 FETCH c_dim_filters
357 INTO l_Dim_Level_id;
358
359 EXIT WHEN c_dim_filters%NOTFOUND;
360 --DBMS_OUTPUT.PUT_LINE(' call Drop_Filter_By_Tab -- l_Dim_Level_id = ' || l_Dim_Level_id);
361
362 BSC_DIM_FILTERS_PUB.Drop_Filter
363 ( p_Tab_Id => p_Tab_Id
364 , p_Dim_Level_Id => l_Dim_Level_id
365 , x_return_status => x_return_status
366 , x_msg_COUNT => x_msg_COUNT
367 , x_msg_data => x_msg_data
368 );
369 END LOOP;
370 CLOSE c_dim_filters;
371 --DBMS_OUTPUT.PUT_LINE(' END Drop_Filter_By_Tab ');
372 EXCEPTION
373 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
374 IF (c_dim_filters%ISOPEN) THEN
375 CLOSE c_dim_filters;
376 END IF;
377 ROLLBACK TO BcsFiltersPubDelFilterViewBT;
378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
379 FND_MSG_PUB.COUNT_And_Get
380 ( p_encoded => FND_API.G_FALSE
381 , p_COUNT => x_msg_COUNT
382 , p_data => x_msg_data
383 );
384 RAISE;
385 WHEN OTHERS THEN
386 IF (c_dim_filters%ISOPEN) THEN
387 CLOSE c_dim_filters;
388 END IF;
389 ROLLBACK TO BcsFiltersPubDelFilterViewBT;
390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
391 IF (x_msg_data IS NOT NULL) THEN
392 x_msg_data := x_msg_data||' -> BSC_DIM_FILTERS_PUB.Drop_Filter_By_Tab';
393 ELSE
394 x_msg_data := SQLERRM||' at BSC_DIM_FILTERS_PUB.Drop_Filter_By_Tab';
395 END IF;
396 RAISE;
397 END Drop_Filter_By_Tab;
398
399 /********************************************************************************
400 Function to return Filter View Name on the basis of KPI Id and Dim Level Id.
401 This is used in cascading the data through PMD while creating new entries
402 in BSC_KPI_DIM_LEVELS_B table
403 This function will return NULL if no view exists
404 ********************************************************************************/
405 FUNCTION Get_Filter_View_Name
406 ( p_Kpi_Id IN BSC_KPIS_B.Indicator%TYPE
407 , p_Dim_Level_Id IN BSC_SYS_DIM_LEVELS_B.Dim_Level_Id%TYPE
408 ) RETURN VARCHAR2
409 IS
410 CURSOR c_Filter_View_Name IS
411 SELECT B.Level_View_Name
412 FROM BSC_TAB_INDICATORS A
413 , BSC_SYS_FILTERS_VIEWS B
414 WHERE A.Indicator = p_Kpi_Id
415 AND B.Dim_Level_Id = p_Dim_Level_Id
416 AND B.Source_Code = A.Tab_Id
417 AND B.Source_Type = BSC_DIM_FILTERS_PUB.Source_Type_Tab;
418
419 l_Filter_View_Name BSC_KPI_DIM_LEVELS_B.Level_View_Name%TYPE := NULL;
420 BEGIN
421 IF (c_Filter_View_Name%ISOPEN) THEN
422 CLOSE c_Filter_View_Name;
423 END IF;
424 OPEN c_Filter_View_Name;
425 FETCH c_Filter_View_Name INTO l_Filter_View_Name;
426 CLOSE c_Filter_View_Name;
427 RETURN l_Filter_View_Name;
428 EXCEPTION
429 WHEN OTHERS THEN
430 IF (c_Filter_View_Name%ISOPEN) THEN
431 CLOSE c_Filter_View_Name;
432 END IF;
433 RETURN NULL;
434 END Get_Filter_View_Name;
435
436
437
438
439 END BSC_DIM_FILTERS_PUB;