[Home] [Help]
PACKAGE BODY: APPS.BSC_DIM_LEVEL_FILTERS_PUB
Source
1 PACKAGE BODY BSC_DIM_LEVEL_FILTERS_PUB AS
2 /* $Header: BSCPFILB.pls 120.5 2007/12/21 09:14:40 psomesul noship $ */
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 | BSCPFILB.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-12-2006 PSOMESUL E#5678943 MIGRATE COMMON DIMENSIONS AND DIMENSION |
16 REM | FILTERS TO SCORECARD DESIGNER |
17 REM | 27-Mar-07 psomesul B#5901412-Open issues of enh no. 5678943 |
18 REM | 08-MAY-07 ashankar Bug#5954327 Fixed numeric or value error: |
19 REM | 07-JUN-07 psomesul Bug#6116585 UNABLE TO ENABLE FILTER WITH KEY ITEM |
20 REM | DEFINED FOR MORE THAN 1 DIM OBJ |
21 REM | 05-JUN-07 ashankar Bug#5938321 Fixed the issues related to list button|
22 REM | security |
23 REM | 07-NOV-07 psomesul Bug#6375565 Handling Filters for MxM dimension objects|
24 REM +=======================================================================+
25 */
26
27
28 PROCEDURE Validate_List_Button_Security
29 (
30 p_tab_id IN NUMBER
31 ,p_dim_level_id IN NUMBER
32 ,p_level_vals_list IN VARCHAR2
33 ,x_return_status OUT NOCOPY VARCHAR2
34 ,x_msg_count OUT NOCOPY NUMBER
35 ,x_msg_data OUT NOCOPY VARCHAR2
36 )IS
37
38 CURSOR c_security IS
39 SELECT DISTINCT a.tab_id,
40 a.dim_level_index,
41 a.dim_level_value,
42 b.dim_level_id,
43 (SELECT level_view_name FROM bsc_sys_dim_levels_b WHERE dim_level_id =b.dim_level_id)level_view_name
44 FROM bsc_user_list_access a,
45 bsc_sys_com_dim_levels b
46 WHERE a.tab_id =b.tab_id
47 AND a.dim_level_index= b.dim_level_index
48 AND a.tab_id =p_tab_id
49 AND a.dim_level_value <>0
50 AND b.dim_level_id = p_dim_level_id
51 ORDER BY A.dim_level_value;
52
53 l_level_vals_list VARCHAR2(30000);
54 l_found VARCHAR2(2);
55 l_level_val VARCHAR2(100);
56 l_level_value NUMBER;
57 l_dim_val VARCHAR2(1000);
58
59
60 BEGIN
61 FND_MSG_PUB.Initialize;
62 x_return_status := FND_API.G_RET_STS_SUCCESS;
63
64
65 IF(p_level_vals_list IS NOT NULL AND p_tab_id IS NOT NULL
66 AND p_dim_level_id IS NOT NULL) THEN
67 FOR cd IN c_security LOOP
68 l_found := FND_API.G_FALSE;
69 l_level_vals_list := p_level_vals_list;
70
71 IF(cd.dim_level_id =p_dim_level_id ) THEN
72 WHILE (BSC_UTILITY.is_more
73 ( p_comma_sep_values => l_level_vals_list
74 , x_value => l_level_val
75 )
76 ) LOOP
77 l_level_value := TO_NUMBER(RTRIM(LTRIM(l_level_val)));
78 IF (l_level_value = cd.dim_level_value) THEN
79 l_found := FND_API.G_TRUE;
80 END IF;
81 END LOOP;
82 IF(l_found=FND_API.G_FALSE)THEN
83 l_dim_val := BSC_DEFAULT_KEY_ITEM_PUB.get_table_column_value(cd.level_view_name, 'NAME', 'CODE=' || cd.dim_level_value);
84 FND_MESSAGE.SET_NAME('BSC','BSC_LIST_SECURITY_ERROR');
85 FND_MESSAGE.SET_TOKEN('FIELD_NAME',l_dim_val, TRUE);
86 FND_MSG_PUB.ADD;
87 RAISE FND_API.G_EXC_ERROR;
88 END IF;
89 END IF;
90 END LOOP;
91 END IF;
92
93 EXCEPTION
94 WHEN FND_API.G_EXC_ERROR THEN
95 IF (x_msg_data IS NULL) THEN
96 FND_MSG_PUB.Count_And_Get
97 ( p_encoded => FND_API.G_FALSE
98 , p_count => x_msg_count
99 , p_data => x_msg_data
100 );
101 END IF;
102 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
103 x_return_status := FND_API.G_RET_STS_ERROR;
104
105 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
106
107 IF (x_msg_data IS NULL) THEN
108 FND_MSG_PUB.Count_And_Get
109 ( p_encoded => FND_API.G_FALSE
110 , p_count => x_msg_count
111 , p_data => x_msg_data
112 );
113 END IF;
114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
115 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
116
117 WHEN NO_DATA_FOUND THEN
118
119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
120 IF (x_msg_data IS NOT NULL) THEN
121 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.Validate_List_Button_Security ';
122 ELSE
123 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.Validate_List_Button_Security ';
124 END IF;
125 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
126
127 WHEN OTHERS THEN
128
129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
130 IF (x_msg_data IS NOT NULL) THEN
131 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.Validate_List_Button_Security ';
132 ELSE
133 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.Validate_List_Button_Security ';
134 END IF;
135 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
136 END Validate_List_Button_Security;
137
138 -----------------------------------------------------------------------------
139 -- The following API saves the filter values for a dimension object
140 -- of a scorecard (tab).
141 -- Input :
142 -- p_tab_id
143 -- p_dim_level_id
144 -- p_level_vals_list A comma seperated list of dim level value IDs.
145 -----------------------------------------------------------------------------
146
147 PROCEDURE save_filter
148 (p_tab_id IN NUMBER
149 ,p_dim_level_id IN NUMBER
150 ,p_level_vals_list IN OUT NOCOPY VARCHAR2
151 ,p_mismatch_keyitems OUT NOCOPY VARCHAR2
152 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
153 ,x_return_status OUT NOCOPY VARCHAR2
154 ,x_msg_count OUT NOCOPY NUMBER
155 ,x_msg_data OUT NOCOPY VARCHAR2
156 ) IS
157
158 l_filter_count NUMBER;
159 l_level_val VARCHAR2(100);
160 l_level_value NUMBER;
161 l_sql VARCHAR2(500);
162 l_key_item_recs BSC_UTILITY.varchar_tabletype;
163 l_key_item_cnt NUMBER;
164 l_key_item_props_recs BSC_UTILITY.varchar_tabletype;
165 l_key_item_props_cnt NUMBER;
166 l_key_name VARCHAR2(100);
167 l_dim_level_view VARCHAR2(100);
168 l_key_item NUMBER;
169
170 BEGIN
171 SAVEPOINT bscpfdlb_savepoint_save_filter;
172 FND_MSG_PUB.Initialize;
173 x_return_status := FND_API.G_RET_STS_SUCCESS;
174
175
176 IF (p_dim_level_id is null OR p_tab_id IS NULL) THEN
177 RETURN;
178 END IF;
179
180 p_mismatch_keyitems := NULL;
181
182 -- we will check the filter values
183
184 Validate_List_Button_Security
185 (
186 p_tab_id => p_tab_id
187 ,p_dim_level_id => p_dim_level_id
188 ,p_level_vals_list => p_level_vals_list
189 ,x_return_status => x_return_status
190 ,x_msg_count => x_msg_count
191 ,x_msg_data => x_msg_data
192 );
193
194 IF(x_return_status IS NOT NULL AND x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
195 RETURN;
196 END IF;
197
198 -- BSC_DIM_LEVEL_FILTERS_PUB.check_key_values(
199 validate_key_items(
200 p_tab_id => p_tab_id
201 ,p_dim_level_id => p_dim_level_id
202 ,p_level_vals_list => p_level_vals_list
203 ,p_mismatch_key_items => p_mismatch_keyitems
204 ,x_return_status => x_return_status
205 ,x_msg_count => x_msg_count
206 ,x_msg_data => x_msg_data
207 );
208 IF (p_mismatch_keyitems IS NOT NULL) THEN
209 RETURN;
210 END IF;
211
212 --Delete filter values already existing before inserting new values
213 BSC_DIM_LEVEL_FILTERS_PVT.delete_filters(
214 p_tab_id => p_tab_id
215 ,p_dim_level_id => p_dim_level_id
216 ,p_commit => FND_API.G_FALSE
217 ,x_return_status => x_return_status
218 ,x_msg_count => x_msg_count
219 ,x_msg_data => x_msg_data
220 );
221 -- DELETE FROM bsc_sys_filters WHERE source_type = 1 AND source_code = p_tab_id AND dim_level_id = p_dim_level_id;
222 -- INSERT all filter values in bsc_sys_filters
223 IF (p_level_vals_list IS NOT NULL) THEN --p_level_vals_list contains a comma seperated values of dim level values
224 WHILE (BSC_UTILITY.is_more(p_comma_sep_values => p_level_vals_list, x_value => l_level_val)) LOOP
225 l_level_value := TO_NUMBER(RTRIM(LTRIM(l_level_val)));
226 BSC_DIM_LEVEL_FILTERS_PVT.insert_filters(
227 p_source_type => 1
228 ,p_source_code => p_tab_id
229 ,p_dim_level_id => p_dim_level_id
230 ,p_dim_level_value => l_level_value
231 ,p_commit => FND_API.G_FALSE
232 ,x_return_status => x_return_status
233 ,x_msg_count => x_msg_count
234 ,x_msg_data => x_msg_data
235 );
236 -- INSERT INTO bsc_sys_filters(source_type,source_code, dim_level_id,dim_level_value) VALUES (1,p_tab_id ,p_dim_level_id, l_level_value );
237 END LOOP;
238
239 --INSERT 'ALL' value also.
240 BSC_DIM_LEVEL_FILTERS_PVT.insert_filters(
241 p_source_type => 1
242 ,p_source_code => p_tab_id
243 ,p_dim_level_id => p_dim_level_id
244 ,p_dim_level_value => 0
245 ,p_commit => FND_API.G_FALSE
246 ,x_return_status => x_return_status
247 ,x_msg_count => x_msg_count
248 ,x_msg_data => x_msg_data
249 );
250 --INSERT INTO bsc_sys_filters(source_type, source_code, dim_level_id,dim_level_value) VALUES (1,p_tab_id , p_dim_level_id, 0);
251 END IF;
252
253 --Validate and rebuild level values VIEW definition and validate each child dimension object
254 BSC_DIM_LEVEL_FILTERS_PUB.process_filter_view(
255 p_tab_id => p_tab_id
256 ,p_dim_level_id => p_dim_level_id
257 ,x_return_status => x_return_status
258 ,x_msg_count => x_msg_count
259 ,x_msg_data => x_msg_data
260 );
261 BSC_DIM_FILTERS_PVT.Synch_Fiters_And_Kpi_Dim(
262 p_tab_id => p_tab_id
263 ,x_return_status => x_return_status
264 ,x_msg_count => x_msg_count
265 ,x_msg_data => x_msg_data
266 );
267
268 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
269 BSC_DIM_LEVEL_FILTERS_PUB.update_tab_who_columns(
270 p_tab_id => p_tab_id
271 ,x_return_status => x_return_status
272 ,x_msg_count => x_msg_count
273 ,x_msg_data => x_msg_data
274 );
275 END IF;
276 -- BSC_DIM_FILTERS_PUB.validate_key_item_filter
277 EXCEPTION
278 WHEN FND_API.G_EXC_ERROR THEN
279 ROLLBACK TO bscpfdlb_savepoint_save_filter;
280 FND_MSG_PUB.Count_And_Get
281 ( p_encoded => FND_API.G_FALSE
282 , p_count => x_msg_count
283 , p_data => x_msg_data
284 );
285 x_return_status := FND_API.G_RET_STS_ERROR;
286 RAISE;
287
288 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
289 ROLLBACK TO bscpfdlb_savepoint_save_filter;
290 FND_MSG_PUB.Count_And_Get
291 ( p_encoded => FND_API.G_FALSE
292 , p_count => x_msg_count
293 , p_data => x_msg_data
294 );
295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
296
297 RAISE;
298
299 WHEN NO_DATA_FOUND THEN
300 ROLLBACK TO bscpfdlb_savepoint_save_filter;
301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
302 IF (x_msg_data IS NOT NULL) THEN
303 x_msg_data := x_msg_data||' -> BSC_DIM_FILTERS_PUB.save_filter ';
304 ELSE
305 x_msg_data := SQLERRM||' at BSC_DIM_FILTERS_PUB.save_filter ';
306 END IF;
307
308 RAISE;
309
310 WHEN OTHERS THEN
311 ROLLBACK TO bscpfdlb_savepoint_save_filter;
312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313 IF (x_msg_data IS NOT NULL) THEN
314 x_msg_data := x_msg_data||' -> BSC_DIM_FILTERS_PUB.save_filter ';
315 ELSE
316 x_msg_data := SQLERRM||' at BSC_DIM_FILTERS_PUB.save_filter ';
317 END IF;
318 RAISE;
319
320 END save_filter;
321
322
323
324 -- The following is a recrusive procedure that create/recreate filter views
325 -- For each child dimension object this api is called recrursively.
326
327
328 PROCEDURE process_filter_view
329 (
330 p_tab_id IN NUMBER
331 ,p_dim_level_id IN NUMBER
332 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
333 ,x_return_status OUT NOCOPY VARCHAR2
334 ,x_msg_count OUT NOCOPY NUMBER
335 ,x_msg_data OUT NOCOPY VARCHAR2
336 ) IS
337
338 l_view VARCHAR2(1000);
339 l_filter_count NUMBER;
340 l_need_view BOOLEAN;
341
342 CURSOR c_parents IS
343 SELECT a.dim_level_id, a.parent_dim_level_id, a.relation_col,
344 (SELECT b.name FROM bsc_sys_dim_levels_vl b WHERE b.dim_level_id = a.parent_dim_level_id) parent_name,
345 (SELECT c.level_view_name FROM bsc_sys_filters_views c WHERE a.parent_dim_level_id = c.dim_level_id AND c.source_type= 1
346 AND rownum <2 AND c.source_code=p_tab_id) parent_filter_view
347 FROM bsc_sys_dim_level_rels a
348 WHERE a.dim_level_id = p_dim_level_id
349 AND a.relation_type=1
350 AND a.dim_level_id IN
351 (SELECT dim_level_id
352 FROM bsc_kpi_dim_level_properties WHERE indicator IN
353 (SELECT indicator FROM bsc_tab_indicators WHERE tab_id = p_tab_id));
354
355 CURSOR c_childs IS
356 SELECT a.dim_level_id child_dim_level_id
357 FROM bsc_sys_dim_level_rels a
358 WHERE a.parent_dim_level_id = p_dim_level_id
359 AND a.relation_type=1
360 AND a.dim_level_id IN
361 (SELECT dim_level_id
362 FROM bsc_kpi_dim_level_properties WHERE indicator IN
363 (SELECT indicator FROM bsc_tab_indicators WHERE tab_id = p_tab_id));
364
365 CURSOR c_fil_view IS
366 SELECT level_view_name
367 FROM bsc_sys_filters_views
368 WHERE source_type = 1
369 AND source_code = p_tab_id
370 AND dim_level_id = p_dim_level_id;
371
372 CURSOR c_tab_kpis IS
373 SELECT DISTINCT kpi_measure_id, indicator
374 FROM bsc_kpi_analysis_measures_b
375 WHERE indicator IN (SELECT DISTINCT ti.indicator
376 FROM bsc_tab_indicators ti
377 WHERE ti.tab_id = p_tab_id);
378
379 BEGIN
380
381 IF ( p_dim_level_id IS NULL OR p_tab_id IS NULL) THEN
382 RETURN;
383 END IF;
384
385 SELECT COUNT(0) INTO l_filter_count
386 FROM bsc_sys_filters
387 WHERE source_type = 1
388 AND source_code=p_tab_id
389 AND dim_level_id = p_dim_level_id;
390
391
392
393 IF (l_filter_count > 0) THEN
394 l_need_view := TRUE;
395 ELSE
396 FOR cd IN c_parents LOOP -- Check for parent's filter view
397 IF (cd.parent_filter_view IS NOT NULL) THEN
398 l_need_view := TRUE;
399 END IF;
400 END LOOP;
401 END IF;
402
403 FOR cd IN c_fil_view LOOP
404 l_view := cd.level_view_name;
405 EXIT;
406 END LOOP;
407
408 IF (l_need_view) THEN
409 BSC_DIM_LEVEL_FILTERS_PUB.create_filter_view(
410 p_tab_id => p_tab_id
411 , p_dim_level_id => p_dim_level_id
412 , p_commit => p_commit
413 , x_return_status => x_return_status
414 , x_msg_count => x_msg_count
415 , x_msg_data => x_msg_data
416 );
417 ELSE -- NO FILTERS SHOULD BE PRESENT
418 IF (l_view IS NOT NULL) THEN
419
420 EXECUTE IMMEDIATE ('DROP VIEW ' || l_view); --DROP FILTER VIEW
421
422 --DELETE entry from bsc_sys_filters_views
423 BSC_DIM_LEVEL_FILTERS_PVT.delete_filters_view (
424 p_tab_id => p_tab_id
425 ,p_dim_level_id => p_dim_level_id
426 ,p_commit => FND_API.G_FALSE
427 ,x_return_status => x_return_status
428 ,x_msg_count => x_msg_count
429 ,x_msg_data => x_msg_data
430 );
431 --DELETE FROM bsc_sys_filters_views WHERE source_type=1 AND source_code = p_tab_id AND dim_level_id = p_dim_level_id;
432
433 --DELTER entry from bsc_sys_filters
434 BSC_DIM_LEVEL_FILTERS_PVT.delete_filters (
435 p_tab_id => p_tab_id
436 ,p_dim_level_id => p_dim_level_id
437 ,p_commit => FND_API.G_FALSE
438 ,x_return_status => x_return_status
439 ,x_msg_count => x_msg_count
440 ,x_msg_data => x_msg_data
441 );
442 -- DELETE FROM bsc_sys_filters WHERE source_type=1 AND source_code = p_tab_id AND dim_level_id = p_dim_level_id;
443
444 END IF;
445 END IF;
446 BSC_COMMON_DIMENSIONS_PUB.change_prototype_flag(
447 p_prototype_flag => 6,
448 p_tab_id => p_tab_id,
449 p_dim_level_id => p_dim_level_id,
450 p_commit => p_commit,
451 x_return_status => x_return_status,
452 x_msg_count => x_msg_count,
453 x_msg_data => x_msg_data
454 );
455 FOR cd IN c_tab_kpis LOOP
456
457 IF (cd.indicator IS NOT NULL AND cd.kpi_measure_id IS NOT NULL) THEN
458 BSC_KPI_COLOR_PROPERTIES_PUB.Change_Prototype_Flag
459 ( p_objective_id => cd.indicator
460 , p_kpi_measure_id => cd.kpi_measure_id
461 , p_prototype_flag => 7
462 , x_return_status => x_return_status
463 , x_msg_count => x_msg_count
464 , x_msg_data => x_msg_data
465 );
466 END IF;
467 END LOOP;
468
469
470
471 FOR cd IN c_childs LOOP
472 IF (l_need_view) THEN
473 BSC_DIM_LEVEL_FILTERS_PUB.del_filters_not_applicable(
474 p_tab_id => p_tab_id,
475 p_ch_level_id => cd.child_dim_level_id,
476 p_pa_level_id => p_dim_level_id,
477 p_commit => p_commit,
478 x_return_status => x_return_status,
479 x_msg_count => x_msg_count,
480 x_msg_data => x_msg_data
481 );
482 END IF;
483
484 --RECURSIVE CALL TO PROCESS THE CHILD
485 BSC_DIM_LEVEL_FILTERS_PUB.process_filter_view(
486 p_tab_id => p_tab_id,
487 p_dim_level_id => cd.child_dim_level_id,
488 x_return_status => x_return_status,
489 x_msg_count => x_msg_count,
490 x_msg_data => x_msg_data
491 );
492 END LOOP;
493
494 EXCEPTION
495
496 WHEN FND_API.G_EXC_ERROR THEN
497
498 FND_MSG_PUB.Count_And_Get
499 ( p_encoded => FND_API.G_FALSE
500 , p_count => x_msg_count
501 , p_data => x_msg_data
502 );
503 x_return_status := FND_API.G_RET_STS_ERROR;
504 RAISE;
505
506 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
507
508 FND_MSG_PUB.Count_And_Get
509 ( p_encoded => FND_API.G_FALSE
510 , p_count => x_msg_count
511 , p_data => x_msg_data
512 );
513 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
514 RAISE;
515
516 WHEN NO_DATA_FOUND THEN
517
518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
519 IF (x_msg_data IS NOT NULL) THEN
520 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.process_filter_view ';
521 ELSE
522 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.process_filter_view ';
523 END IF;
524
525 RAISE;
526
527 WHEN OTHERS THEN
528
529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
530 IF (x_msg_data IS NOT NULL) THEN
531 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.process_filter_view ';
532 ELSE
533 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.process_filter_view ';
534 END IF;
535 RAISE;
536 END process_filter_view;
537
538
539
540
541
542 -- The following API creates filter view and updates bsc_sys_filter_views
543
544 PROCEDURE create_filter_view
545 (
546 p_tab_id IN NUMBER
547 , p_dim_level_id IN NUMBER
548 , p_commit IN VARCHAR2 := FND_API.G_FALSE
549 , x_return_status OUT NOCOPY VARCHAR2
550 , x_msg_count OUT NOCOPY NUMBER
551 , x_msg_data OUT NOCOPY VARCHAR2
552 ) IS
553
554 l_sql VARCHAR2(3000);
555 l_table VARCHAR2(100);
556 l_filter_count NUMBER;
557 l_view VARCHAR2(100);
558 l_sql_tables VARCHAR2(3000);
559 l_sql_where_cond VARCHAR2(3000);
560 l_view_name VARCHAR2(100);
561 l_cnt NUMBER;
562
563 CURSOR c_parents IS
564 SELECT a.dim_level_id, a.parent_dim_level_id, a.relation_col,
565 (SELECT b.name FROM bsc_sys_dim_levels_vl b WHERE b.dim_level_id = a.parent_dim_level_id) parent_name,
566 (SELECT c.level_view_name FROM bsc_sys_filters_views c WHERE a.parent_dim_level_id = c.dim_level_id AND c.source_type= 1
567 AND rownum <2 AND c.source_code=p_tab_id) parent_filter_view
568 FROM bsc_sys_dim_level_rels a
569 WHERE a.dim_level_id = p_dim_level_id
570 AND a.relation_type=1;
571
572 CURSOR c_fil_view IS
573 SELECT level_view_name
574 FROM bsc_sys_filters_views
575 WHERE source_type = 1
576 AND source_code = p_tab_id
577 AND dim_level_id = p_dim_level_id;
578
579 CURSOR c_dim_table IS
580 SELECT level_table_name
581 FROM bsc_sys_dim_levels_b
582 WHERE dim_level_id = p_dim_level_id;
583
584 CURSOR c_dim_view IS
585 SELECT level_view_name
586 FROM bsc_sys_dim_levels_b
587 WHERE dim_level_id = p_dim_level_id;
588
589 BEGIN
590
591 FOR cd IN c_fil_view LOOP
592 l_view := cd.level_view_name;
593 EXIT;
594 END LOOP;
595
596 IF (l_view is NULL) THEN --CREATE NEW FILTER VIEW
597 FOR cd IN c_dim_table LOOP
598 l_table := cd.level_table_name;
599 EXIT;
600 END LOOP;
601
602 IF (l_table is NULL) THEN
603 RETURN;
604 END IF;
605
606 l_view := BSC_DIM_LEVEL_FILTERS_PUB.get_new_filter_view_name(
607 p_dimension_table => l_table,
608 x_return_status => x_return_status,
609 x_msg_count => x_msg_count,
610 x_msg_data => x_msg_data
611 ); --TODO :: USE PACKAGE_NAME.FUNCTION_NAME
612
613 BSC_DIM_LEVEL_FILTERS_PVT.insert_filters_view(
614 p_source_type => 1
615 ,p_source_code => p_tab_id
616 ,p_dim_level_id => p_dim_level_id
617 ,p_level_table_name => l_table
618 ,p_level_view_name => l_view
619 ,p_commit => FND_API.G_FALSE
620 ,x_return_status => x_return_status
621 ,x_msg_count => x_msg_count
622 ,x_msg_data => x_msg_data
623 );
624 ---INSERT INTO bsc_sys_filters_views(source_type, source_code, dim_level_id, level_table_name, level_view_name) VALUES (1, p_tab_id, p_dim_level_id, l_table,l_view);
625 END IF;
626
627 FOR cd IN c_dim_view LOOP
628 l_view_name := cd.level_view_name;
629 EXIT;
630 END LOOP;
631
632
633 SELECT COUNT(0) INTO l_filter_count
634 FROM bsc_sys_filters
635 WHERE source_type= 1
636 AND source_code = p_tab_id
637 AND dim_level_id = p_dim_level_id;
638
639
640 IF (l_filter_count > 0) THEN
641 l_sql_tables := 'bsc_sys_filters f, ' || l_view_name || ' d ';
642 l_sql_where_cond := ' f.source_type=1 AND f.source_code=' || p_tab_id || ' AND f.dim_level_id=' || p_dim_level_id || ' AND f.dim_level_value=d.code';
643
644 ELSE
645 l_cnt := 0;
646 l_sql_tables := ' ' || l_view_name || ' d ';
647
648 FOR cd IN c_parents LOOP
649 l_cnt := l_cnt + 1;
650 IF (cd.parent_filter_view IS NOT NULL AND CD.relation_col IS NOT NULL ) THEN
651 l_sql_tables := l_sql_tables || ' , ' || cd.parent_filter_view || ' p' || l_cnt;
652 l_sql_where_cond := l_sql_where_cond || ' AND ' || ' d.' || cd.relation_col || '=p' || l_cnt || '.code';
653 END IF;
654 END LOOP;
655
656 IF (l_sql_where_cond IS NOT NULL) THEN
657 l_sql_where_cond := SUBSTR(l_sql_where_cond, 6); --REMOVE extra AND at the beginning of the where clause
658 END IF;
659 END IF;
660
661
662 l_cnt := 0;
663 SELECT COUNT(0) INTO l_cnt
664 FROM user_objects
665 WHERE object_name = l_view;
666
667
668 IF (l_cnt <> 0) THEN
669 EXECUTE IMMEDIATE ('DROP VIEW ' || l_view);
670 END IF;
671
672 l_sql := 'CREATE VIEW ' || l_view || ' AS (SELECT d.* FROM ' || l_sql_tables || ' WHERE ' || l_sql_where_cond || ')';
673 EXECUTE IMMEDIATE l_sql;
674
675 EXCEPTION
676 WHEN FND_API.G_EXC_ERROR THEN
677
678
679 FND_MSG_PUB.Count_And_Get
680 ( p_encoded => FND_API.G_FALSE
681 , p_count => x_msg_count
682 , p_data => x_msg_data
683 );
684 x_return_status := FND_API.G_RET_STS_ERROR;
685 RAISE;
686
687 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
688 FND_MSG_PUB.Count_And_Get
689 ( p_encoded => FND_API.G_FALSE
690 , p_count => x_msg_count
691 , p_data => x_msg_data
692 );
693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
694 RAISE;
695
696 WHEN NO_DATA_FOUND THEN
697 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
698 IF (x_msg_data IS NOT NULL) THEN
699 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.create_filter_view ';
700 ELSE
701 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.create_filter_view ';
702 END IF;
703
704 RAISE;
705
706 WHEN OTHERS THEN
707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
708 IF (x_msg_data IS NOT NULL) THEN
709 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.create_filter_view ';
710 ELSE
711 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.create_filter_view ';
712 END IF;
713 RAISE;
714
715 END create_filter_view;
716
717
718
719 -- The following API returns a unique view name that is not existing.
720 -- INPUT:
721 -- p_dimension_table This is dimension object table name
722
723
724
725 FUNCTION get_new_filter_view_name(
726 p_dimension_table IN VARCHAR2
727 , x_return_status OUT NOCOPY VARCHAR2
728 , x_msg_count OUT NOCOPY NUMBER
729 , x_msg_data OUT NOCOPY VARCHAR2
730 )
731 RETURN VARCHAR2
732 IS
733
734 l_result VARCHAR2(100);
735 l_view_count NUMBER;
736 l_v_count NUMBER;
737 l_where_condition VARCHAR2(100);
738
739 BEGIN
740 IF (p_dimension_table IS NULL) THEN
741 RETURN NULL;
742 END IF;
743
744 SELECT COUNT(DISTINCT object_name) INTO l_view_count
745 FROM user_objects
746 WHERE object_name like p_dimension_table || '_V%';
747
748
749 l_view_count := l_view_count+1;
750 l_result := p_dimension_table || '_V' || l_view_count;
751
752
753 WHILE TRUE LOOP
754 SELECT COUNT(0) INTO l_v_count
755 FROM user_objects
756 WHERE object_name = l_result;
757
758 EXIT WHEN l_v_count = 0;
759
760 l_view_count := l_view_count +1;
761 l_result := p_dimension_table || '_V' || l_view_count;
762 END LOOP;
763
764 RETURN l_result;
765
766 EXCEPTION
767 WHEN FND_API.G_EXC_ERROR THEN
768
769 FND_MSG_PUB.Count_And_Get
770 ( p_encoded => FND_API.G_FALSE
771 , p_count => x_msg_count
772 , p_data => x_msg_data
773 );
774 x_return_status := FND_API.G_RET_STS_ERROR;
775 RAISE;
776
777 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
778
779 FND_MSG_PUB.Count_And_Get
780 ( p_encoded => FND_API.G_FALSE
781 , p_count => x_msg_count
782 , p_data => x_msg_data
783 );
784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
785 RAISE;
786
787 WHEN NO_DATA_FOUND THEN
788
789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
790 IF (x_msg_data IS NOT NULL) THEN
791 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.get_new_filter_view_name ';
792 ELSE
793 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.get_new_filter_view_name ';
794 END IF;
795
796 RAISE;
797
798 WHEN OTHERS THEN
799
800 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
801 IF (x_msg_data IS NOT NULL) THEN
802 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.get_new_filter_view_name ';
803 ELSE
804 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.get_new_filter_view_name ';
805 END IF;
806 RAISE;
807 END get_new_filter_view_name;
808
809
810
811 -- The following api builds SQL to retrieve dim level values
812 -- that can be filtered.
813 -- This api is called from UI, to build filter values VO dynamically.
814
815
816 PROCEDURE get_filter_dimension_SQL
817 ( p_tab_id IN NUMBER
818 , p_dim_level_id IN NUMBER
819 , x_sql OUT NOCOPY VARCHAR2
820 , p_commit IN VARCHAR2 := FND_API.G_FALSE
821 , x_return_status OUT NOCOPY VARCHAR2
822 , x_msg_count OUT NOCOPY NUMBER
823 , x_msg_data OUT NOCOPY VARCHAR2
824 )
825 IS
826
827 CURSOR c_parents IS
828 SELECT a.dim_level_id, a.parent_dim_level_id, a.relation_col,
829 (SELECT b.name FROM bsc_sys_dim_levels_vl b WHERE b.dim_level_id = a.parent_dim_level_id) parent_name,
830 (SELECT c.level_view_name FROM bsc_sys_filters_views c WHERE a.parent_dim_level_id = c.dim_level_id AND c.source_type= 1
831 AND rownum <2 AND c.source_code=p_tab_id) parent_filter_view
832 FROM bsc_sys_dim_level_rels a
833 WHERE a.dim_level_id = p_dim_level_id AND a.relation_type=1 ;
834
835 CURSOR c_dim_view IS
836 SELECT level_view_name
837 FROM bsc_sys_dim_levels_b
838 WHERE dim_level_id = p_dim_level_id;
839
840 l_rel_col VARCHAR2(100);
841 l_sql_tables VARCHAR2(1000);
842 l_sql_where_cond VARCHAR2(3000);
843 l_index NUMBER;
844 l_view_count NUMBER;
845
846 BEGIN
847
848 FND_MSG_PUB.Initialize;
849
850
851 IF (p_tab_id is NOT NULL AND p_dim_level_id IS NOT NULL) THEN
852
853 FOR cd IN c_dim_view LOOP
854 l_sql_tables := cd.level_view_name;
855 END LOOP;
856
857 IF (l_sql_tables IS NOT NULL) THEN
858 x_return_status := FND_API.G_RET_STS_SUCCESS;
859
860 l_sql_tables := l_sql_tables || ' d ';
861 l_sql_where_cond := ' d.code <> 0';
862 l_index := 0;
863
864 FOR cd IN c_parents LOOP
865 IF (cd.relation_col IS NOT NULL AND cd.parent_filter_view IS NOT NULL) THEN
866
867 BSC_BIS_DIM_REL_PUB.Verify_Recreate_Filter_Views(
868 p_source => p_tab_id,
869 p_level_view_name => cd.parent_filter_view,
870 p_dim_level_id => cd.parent_dim_level_id,
871 x_return_status => x_return_status,
872 x_msg_count => x_msg_count,
873 x_msg_data => x_msg_data
874 );
875
876 l_index := l_index + 1;
877 l_sql_tables := l_sql_tables || ' , ' || cd.parent_filter_view || ' p' || l_index ;
878 l_sql_where_cond := l_sql_where_cond || ' AND d.' || cd.relation_col || ' = p' || l_index || '.code';
879
880 END IF;
881 END LOOP;
882
883 x_sql := 'SELECT TO_CHAR(d.code) ID, d.name VALUE FROM ' || l_sql_tables || ' WHERE ' || l_sql_where_cond || ' ORDER BY VALUE ';
884
885 EXECUTE IMMEDIATE x_sql;
886
887 END IF;
888 END IF;
889 EXCEPTION
890
891 WHEN FND_API.G_EXC_ERROR THEN
892
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 x_return_status := FND_API.G_RET_STS_ERROR;
899 x_sql := NULL;
900 RAISE;
901
902 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
903
904 FND_MSG_PUB.Count_And_Get
905 ( p_encoded => FND_API.G_FALSE
906 , p_count => x_msg_count
907 , p_data => x_msg_data
908 );
909 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
910 x_sql := NULL;
911 RAISE;
912
913 WHEN NO_DATA_FOUND THEN
914
915 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
916 IF (x_msg_data IS NOT NULL) THEN
917 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.get_filter_dimension_SQL ';
918 ELSE
919 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.get_filter_dimension_SQL ';
920 END IF;
921 x_sql := NULL;
922 RAISE;
923
924 WHEN OTHERS THEN
925
926 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
927 IF (x_msg_data IS NOT NULL) THEN
928 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.get_filter_dimension_SQL ';
929 ELSE
930 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.get_filter_dimension_SQL ';
931 END IF;
932 x_sql := NULL;
933 RAISE;
934
935 END get_filter_dimension_SQL;
936
937
938 -- The following API returns a SQL to retrieve filtered dim level values.
939 -- This api is called from UI to build VO dynamically.
940
941 PROCEDURE get_filtered_dim_values_SQL
942 ( p_tab_id IN NUMBER
943 , p_dim_level_id IN NUMBER
944 , x_sql OUT NOCOPY VARCHAR2
945 , p_commit IN VARCHAR2 := FND_API.G_FALSE
946 , x_return_status OUT NOCOPY VARCHAR2
947 , x_msg_count OUT NOCOPY NUMBER
948 , x_msg_data OUT NOCOPY VARCHAR2
949 )
950 IS
951
952 CURSOR c_filter_view IS
953 SELECT level_view_name
954 FROM bsc_sys_filters_views
955 WHERE source_type=1
956 AND source_code = p_tab_id
957 AND dim_level_id = p_dim_level_id;
958
959 l_rel_col VARCHAR2(100);
960 l_sql_view VARCHAR2(1000);
961 l_sql_where_cond VARCHAR2(3000);
962 l_index NUMBER;
963 l_view_count NUMBER;
964 l_dummy_sql VARCHAR2(100);
965
966 BEGIN
967
968 FND_MSG_PUB.Initialize;
969 x_return_status := FND_API.G_RET_STS_SUCCESS;
970
971
972 -- The following is a dummy SQL that returns no rows.
973 l_dummy_sql := 'SELECT NULL ID, NULL VALUE FROM DUAL WHERE ROWNUM<1';
974
975 IF (p_tab_id is NOT NULL AND p_dim_level_id IS NOT NULL) THEN
976
977 FOR cd IN c_filter_view LOOP
978 l_sql_view := cd.level_view_name;
979 EXIT;
980 END LOOP;
981
982
983 IF (l_sql_view IS NOT NULL) THEN
984 BSC_BIS_DIM_REL_PUB.Verify_Recreate_Filter_Views(
985 p_source => p_tab_id,
986 p_level_view_name => l_sql_view,
987 p_dim_level_id => p_dim_level_id,
988 x_return_status => x_return_status,
989 x_msg_count => x_msg_count,
990 x_msg_data => x_msg_data
991 );
992
993
994 x_sql := 'SELECT TO_CHAR(f.dim_level_value) ID, ';
995 x_sql := x_sql || '(SELECT v.name FROM ' || l_sql_view || ' v ';
996 x_sql := x_sql || ' WHERE v.code=f.dim_level_value and rownum < 2 ) VALUE ';
997 x_sql := x_sql || ' FROM bsc_sys_filters f WHERE f.source_type=1 AND f.source_code=';
998 x_sql := x_sql || p_tab_id || ' AND f.dim_level_id=' || p_dim_level_id || ' AND f.dim_level_value <> 0';
999 x_sql := x_sql || ' ORDER BY VALUE ';
1000
1001 ELSE
1002 x_sql := l_dummy_sql;
1003 END IF;
1004 ELSE
1005 x_sql := l_dummy_sql;
1006 END IF;
1007
1008 EXECUTE IMMEDIATE x_sql;
1009
1010 EXCEPTION
1011 WHEN FND_API.G_EXC_ERROR THEN
1012
1013 FND_MSG_PUB.Count_And_Get
1014 ( p_encoded => FND_API.G_FALSE
1015 , p_count => x_msg_count
1016 , p_data => x_msg_data
1017 );
1018 x_return_status := FND_API.G_RET_STS_ERROR;
1019 x_sql := l_dummy_sql;
1020 RAISE;
1021
1022 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1023
1024 FND_MSG_PUB.Count_And_Get
1025 ( p_encoded => FND_API.G_FALSE
1026 , p_count => x_msg_count
1027 , p_data => x_msg_data
1028 );
1029 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1030 x_sql := l_dummy_sql;
1031 RAISE;
1032
1033 WHEN NO_DATA_FOUND THEN
1034
1035 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1036 IF (x_msg_data IS NOT NULL) THEN
1037 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.get_filtered_dim_values_SQL ';
1038 ELSE
1039 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.get_filtered_dim_values_SQL ';
1040 END IF;
1041 x_sql := l_dummy_sql;
1042 RAISE;
1043
1044 WHEN OTHERS THEN
1045
1046 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1047 IF (x_msg_data IS NOT NULL) THEN
1048 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.get_filtered_dim_values_SQL ';
1049 ELSE
1050 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.get_filtered_dim_values_SQL ';
1051 END IF;
1052 x_sql := l_dummy_sql;
1053 RAISE;
1054
1055 END get_filtered_dim_values_SQL;
1056
1057
1058
1059
1060
1061 -- The follwoing API deletes unmatched child dimension object filter values
1062 -- that are not matching with the filter values defined for the parent.
1063 -- INPUT :
1064 -- p_ch_level_id Dimension Object (dim level) id of child dimension object
1065 -- p_pa_level_id Dimension Object (dim level) id of parent dimension object
1066
1067
1068 PROCEDURE del_filters_not_applicable(
1069 p_tab_id IN NUMBER
1070 ,p_ch_level_id IN NUMBER
1071 ,p_pa_level_id IN NUMBER
1072 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1073 ,x_return_status OUT NOCOPY VARCHAR2
1074 ,x_msg_count OUT NOCOPY NUMBER
1075 ,x_msg_data OUT NOCOPY VARCHAR2
1076 ) IS
1077
1078 l_ch_view VARCHAR2(100);
1079 l_pa_view VARCHAR2(100);
1080 l_filter_count NUMBER;
1081 l_row_count NUMBER;
1082 l_rel_col VARCHAR2(100);
1083 l_cur_sql VARCHAR2(1000);
1084 l_code NUMBER;
1085 TYPE ref_cursor IS REF CURSOR;
1086 ref_cur ref_cursor;
1087
1088 CURSOR c_ch_dim_view IS
1089 SELECT level_view_name
1090 FROM bsc_sys_filters_views
1091 WHERE source_type = 1
1092 AND source_code = p_tab_id
1093 AND dim_level_id = p_ch_level_id;
1094
1095 CURSOR c_pa_dim_view IS
1096 SELECT level_view_name
1097 FROM bsc_sys_filters_views
1098 WHERE source_type = 1
1099 AND source_code = p_tab_id
1100 AND dim_level_id = p_pa_level_id;
1101
1102 CURSOR c_rel_col is
1103 SELECT relation_col
1104 FROM bsc_sys_dim_level_rels
1105 WHERE dim_level_id = p_ch_level_id AND parent_dim_level_id = p_pa_level_id;
1106
1107 BEGIN
1108
1109 FOR cd IN c_ch_dim_view LOOP
1110 l_ch_view:=cd.level_view_name;
1111 EXIT;
1112 END LOOP;
1113
1114 FOR cd IN c_pa_dim_view LOOP
1115 l_pa_view:=cd.level_view_name;
1116 EXIT;
1117 END LOOP;
1118
1119 IF (l_ch_view IS NOT NULL AND l_pa_view IS NOT NULL) THEN
1120 -- TODO : the following api should be replaced with existig Verify_Recreate_Filter_View() API in BSCRPMDB.pls
1121 -- Verify_Recreate_Filter_View() is present in BSCRPMDB.pls but not included in the spec.
1122
1123 BSC_BIS_DIM_REL_PUB.Verify_Recreate_Filter_Views(
1124 p_source => p_tab_id,
1125 p_level_view_name => l_ch_view,
1126 p_dim_level_id => p_ch_level_id,
1127 x_return_status => x_return_status,
1128 x_msg_count => x_msg_count,
1129 x_msg_data => x_msg_data
1130 );
1131 BSC_BIS_DIM_REL_PUB.Verify_Recreate_Filter_Views(
1132 p_source => p_tab_id,
1133 p_level_view_name => l_pa_view,
1134 p_dim_level_id => p_pa_level_id,
1135 x_return_status => x_return_status,
1136 x_msg_count => x_msg_count,
1137 x_msg_data => x_msg_data
1138 );
1139 FOR cd IN c_rel_col LOOP
1140 l_rel_col := cd.relation_col;
1141 EXIT;
1142 END LOOP;
1143
1144
1145
1146 IF (l_rel_col IS NOT NULL) THEN
1147 l_cur_sql := 'SELECT d.code FROM ' || l_ch_view || ' d, ' || l_pa_view || ' p WHERE d.' || l_rel_col || '=p.code(+) AND p.code IS NULL';
1148
1149 IF (ref_cur%ISOPEN) THEN
1150 CLOSE ref_cur;
1151 END IF;
1152
1153 OPEN ref_cur FOR l_cur_sql;
1154
1155 l_row_count := ref_cur%ROWCOUNT;
1156
1157 SELECT COUNT(0) INTO l_filter_count
1158 FROM bsc_sys_filters
1159 WHERE source_type= 1
1160 AND source_code = p_tab_id
1161 AND dim_level_id = p_ch_level_id;
1162
1163 IF (l_row_count >= l_filter_count - 1) THEN -- Unmatched filter values present in the child
1164 DELETE
1165 FROM bsc_sys_filters
1166 WHERE source_type = 1
1167 AND source_code = p_tab_id
1168 AND dim_level_id = p_ch_level_id;
1169
1170 ELSE
1171 LOOP
1172
1173 FETCH ref_cur INTO l_code;
1174 EXIT WHEN ref_cur%NOTFOUND;
1175
1176 IF (l_code IS NOT NULL) THEN
1177
1178 DELETE
1179 FROM bsc_sys_filters
1180 WHERE source_type = 1
1181 AND source_code = p_tab_id
1182 AND dim_level_id = p_ch_level_id
1183 AND dim_level_value = l_code;
1184
1185 END IF;
1186 END LOOP;
1187 END IF;
1188
1189 IF (ref_cur%ISOPEN) THEN
1190 CLOSE ref_cur;
1191 END IF;
1192
1193 SELECT COUNT(0) INTO l_filter_count
1194 FROM bsc_sys_filters
1195 WHERE source_type= 1
1196 AND source_code = p_tab_id
1197 AND dim_level_id = p_ch_level_id;
1198
1199 IF (l_filter_count = 1) THEN
1200
1201 DELETE
1202 FROM bsc_sys_filters
1203 WHERE source_type = 1
1204 AND source_code = p_tab_id
1205 AND dim_level_id = p_ch_level_id;
1206 END IF;
1207 END IF;
1208 END IF;
1209
1210 EXCEPTION
1211 WHEN FND_API.G_EXC_ERROR THEN
1212
1213 FND_MSG_PUB.Count_And_Get
1214 ( p_encoded => FND_API.G_FALSE
1215 , p_count => x_msg_count
1216 , p_data => x_msg_data
1217 );
1218 x_return_status := FND_API.G_RET_STS_ERROR;
1219
1220 IF (ref_cur%ISOPEN) THEN
1221 CLOSE ref_cur;
1222 END IF;
1223
1224 RAISE;
1225
1226 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1227
1228 FND_MSG_PUB.Count_And_Get
1229 ( p_encoded => FND_API.G_FALSE
1230 , p_count => x_msg_count
1231 , p_data => x_msg_data
1232 );
1233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1234 IF (ref_cur%ISOPEN) THEN
1235 CLOSE ref_cur;
1236 END IF;
1237
1238 RAISE;
1239
1240 WHEN NO_DATA_FOUND THEN
1241
1242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1243 IF (x_msg_data IS NOT NULL) THEN
1244 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.del_filters_not_applicable ';
1245 ELSE
1246 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.del_filters_not_applicable ';
1247 END IF;
1248 IF (ref_cur%ISOPEN) THEN
1249 CLOSE ref_cur;
1250 END IF;
1251
1252 RAISE;
1253
1254 WHEN OTHERS THEN
1255
1256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1257 IF (x_msg_data IS NOT NULL) THEN
1258 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.del_filters_not_applicable ';
1259 ELSE
1260 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.del_filters_not_applicable ';
1261 END IF;
1262 IF (ref_cur%ISOPEN) THEN
1263 CLOSE ref_cur;
1264 END IF;
1265
1266 RAISE;
1267
1268 END del_filters_not_applicable;
1269
1270
1271 ----------------------------------------------------------------------------
1272
1273 PROCEDURE update_tab_who_columns
1274 (
1275 p_tab_id IN NUMBER
1276 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
1277 ,x_return_status OUT NOCOPY VARCHAR2
1278 ,x_msg_count OUT NOCOPY NUMBER
1279 ,x_msg_data OUT NOCOPY VARCHAR2
1280 )
1281 IS
1282
1283 l_user_id VARCHAR2(100);
1284 l_login_id VARCHAR2(100);
1285 l_row_cnt NUMBER;
1286
1287 BEGIN
1288
1289 IF (p_tab_id IS NOT NULL ) THEN
1290
1291 SELECT COUNT(0) INTO l_row_cnt
1292 FROM bsc_tabs_b
1293 WHERE tab_id = p_tab_id;
1294
1295 IF (l_row_cnt = 1) THEN
1296
1297 l_user_id := fnd_global.USER_ID;
1298 l_login_id := fnd_global.LOGIN_ID;
1299
1300 UPDATE bsc_tabs_b
1301 SET last_updated_by = l_user_id,
1302 last_update_date = SYSDATE,
1303 last_update_login = l_login_id
1304 WHERE tab_id = p_tab_id;
1305
1306 END IF;
1307 END IF;
1308
1309 IF (p_commit = FND_API.G_TRUE) THEN
1310 COMMIT;
1311 END IF;
1312
1313 EXCEPTION
1314
1315 WHEN FND_API.G_EXC_ERROR THEN
1316
1317 FND_MSG_PUB.Count_And_Get
1318 ( p_encoded => FND_API.G_FALSE
1319 , p_count => x_msg_count
1320 , p_data => x_msg_data
1321 );
1322 x_return_status := FND_API.G_RET_STS_ERROR;
1323 RAISE;
1324
1325 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1326
1327 FND_MSG_PUB.Count_And_Get
1328 ( p_encoded => FND_API.G_FALSE
1329 , p_count => x_msg_count
1330 , p_data => x_msg_data
1331 );
1332 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1333 RAISE;
1334
1335 WHEN NO_DATA_FOUND THEN
1336
1337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1338 IF (x_msg_data IS NOT NULL) THEN
1339 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.update_tab_who_columns ';
1340 ELSE
1341 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.update_tab_who_columns ';
1342 END IF;
1343
1344 RAISE;
1345
1346 WHEN OTHERS THEN
1347
1348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1349 IF (x_msg_data IS NOT NULL) THEN
1350 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.update_tab_who_columns ';
1351 ELSE
1352 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.update_tab_who_columns ';
1353 END IF;
1354 RAISE;
1355
1356 END update_tab_who_columns;
1357
1358
1359 PROCEDURE validate_key_items(
1360 p_tab_id IN NUMBER
1361 ,p_dim_level_id IN NUMBER
1362 ,p_level_vals_list IN OUT NOCOPY VARCHAR2
1363 ,p_mismatch_key_items IN OUT NOCOPY VARCHAR2
1364 ,x_return_status OUT NOCOPY VARCHAR2
1365 ,x_msg_count OUT NOCOPY NUMBER
1366 ,x_msg_data OUT NOCOPY VARCHAR2
1367 )
1368
1369 IS
1370 l_filter_count NUMBER;
1371 l_key_value NUMBER;
1372 l_cnt NUMBER;
1373 l_key_count NUMBER;
1374 l_filter_view VARCHAR2(100);
1375 l_level_view VARCHAR2(100);
1376 l_level_vals_list VARCHAR2(30000);
1377 l_mismatch NUMBER;
1378 l_level_value NUMBER;
1379 l_key_name VARCHAR2(500);
1380 l_level_val VARCHAR2(100);
1381
1382 CURSOR c_key_items IS
1383 SELECT a.default_key_value,
1384 a.level_shortname,
1385 a.level_view_name
1386 FROM bsc_kpi_dim_levels_vl a
1387 WHERE a.indicator IN (SELECT b.indicator
1388 FROM bsc_tab_indicators b
1389 WHERE b.tab_id = p_tab_id)
1390 AND a.level_shortname IN ( SELECT d.short_name
1391 FROM bsc_sys_dim_levels_b d
1392 WHERE d.dim_level_id = p_dim_level_id);
1393
1394 CURSOR c_parents IS
1395 SELECT a.dim_level_id, a.parent_dim_level_id, a.relation_col
1396 FROM bsc_sys_dim_level_rels a
1397 WHERE a.dim_level_id = p_dim_level_id
1398 AND a.relation_type=1
1399 AND EXISTS ( SELECT indicator
1400 FROM bsc_kpi_dim_level_properties b
1401 WHERE b.dim_level_id = a.parent_dim_level_id
1402 AND indicator IN (SELECT indicator
1403 FROM bsc_tab_indicators WHERE tab_id = p_tab_id
1404 )
1405 );
1406
1407 CURSOR c_childs IS
1408 SELECT a.dim_level_id child_dim_level_id
1409 FROM bsc_sys_dim_level_rels a
1410 WHERE a.parent_dim_level_id = p_dim_level_id
1411 AND a.relation_type=1
1412 AND EXISTS (SELECT b.dim_level_id
1413 FROM bsc_kpi_dim_level_properties b
1414 WHERE b.indicator IN (SELECT indicator FROM bsc_tab_indicators WHERE tab_id = p_tab_id)
1415 and b.dim_level_id = a.dim_level_id );
1416
1417 BEGIN
1418
1419
1420
1421 IF (p_tab_id IS NOT NULL AND p_dim_level_id IS NOT NULL AND p_dim_level_id IS NOT NULL AND p_level_vals_list IS NOT NULL) THEN
1422
1423 FOR cd IN c_key_items LOOP
1424
1425 IF (cd.default_key_value IS NOT NULL) THEN
1426
1427
1428
1429 l_level_vals_list := p_level_vals_list;
1430 l_mismatch := 1;
1431
1432
1433
1434
1435 WHILE (BSC_UTILITY.is_more(p_comma_sep_values => l_level_vals_list, x_value => l_level_val)) LOOP
1436
1437 l_level_value := TO_NUMBER(RTRIM(LTRIM(l_level_val)));
1438
1439 IF (l_level_value = cd.default_key_value) THEN
1440 l_mismatch := 0;
1441 EXIT;
1442 END IF;
1443 END LOOP;
1444
1445
1446
1447 IF (l_mismatch = 1) THEN
1448
1449 l_key_name := BSC_DEFAULT_KEY_ITEM_PUB.get_table_column_value(cd.level_view_name, 'NAME', 'CODE=' || cd.default_key_value);
1450
1451
1452 IF (p_mismatch_key_items IS NULL) THEN
1453 p_mismatch_key_items := l_key_name;
1454 ELSE
1455 p_mismatch_key_items := p_mismatch_key_items || ',' || l_key_name;
1456 END IF;
1457 END IF;
1458
1459 END IF;
1460 END LOOP;
1461
1462
1463 FOR cd IN c_parents LOOP
1464 IF (cd.parent_dim_level_id IS NOT NULL) THEN
1465 validate_parent_key_items (
1466 p_tab_id => p_tab_id
1467 ,p_dim_level_id => p_dim_level_id
1468 ,p_parent_level_id => cd.parent_dim_level_id
1469 ,p_level_vals_list => p_level_vals_list
1470 ,p_mismatch_key_items => p_mismatch_key_items
1471 ,x_return_status => x_return_status
1472 ,x_msg_count => x_msg_count
1473 ,x_msg_data => x_msg_data
1474 );
1475 END IF;
1476 END LOOP;
1477
1478 FOR cd IN c_childs LOOP
1479 IF (cd.child_dim_level_id IS NOT NULL) THEN
1480 validate_child_key_items (
1481 p_tab_id => p_tab_id
1482 ,p_dim_level_id => p_dim_level_id
1483 ,p_child_level_id => cd.child_dim_level_id
1484 ,p_level_vals_list => p_level_vals_list
1485 ,p_mismatch_key_items => p_mismatch_key_items
1486 ,x_return_status => x_return_status
1487 ,x_msg_count => x_msg_count
1488 ,x_msg_data => x_msg_data
1489 );
1490 END IF;
1491 END LOOP;
1492 END IF;
1493
1494
1495 EXCEPTION
1496
1497 WHEN FND_API.G_EXC_ERROR THEN
1498
1499 FND_MSG_PUB.Count_And_Get
1500 ( p_encoded => FND_API.G_FALSE
1501 , p_count => x_msg_count
1502 , p_data => x_msg_data
1503 );
1504 x_return_status := FND_API.G_RET_STS_ERROR;
1505 RAISE;
1506
1507 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1508
1509 FND_MSG_PUB.Count_And_Get
1510 ( p_encoded => FND_API.G_FALSE
1511 , p_count => x_msg_count
1512 , p_data => x_msg_data
1513 );
1514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1515 RAISE;
1516
1517 WHEN NO_DATA_FOUND THEN
1518
1519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1520 IF (x_msg_data IS NOT NULL) THEN
1521 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.validate_key_items ';
1522 ELSE
1523 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.validate_key_items ';
1524 END IF;
1525
1526 RAISE;
1527
1528 WHEN OTHERS THEN
1529
1530 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1531 IF (x_msg_data IS NOT NULL) THEN
1532 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.validate_key_items ';
1533 ELSE
1534 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.validate_key_items ';
1535 END IF;
1536 RAISE;
1537
1538 END validate_key_items;
1539
1540
1541 PROCEDURE validate_parent_key_items(
1542 p_tab_id IN NUMBER
1543 ,p_dim_level_id IN NUMBER
1544 ,p_parent_level_id IN NUMBER
1545 ,p_level_vals_list IN VARCHAR2
1546 ,p_mismatch_key_items IN OUT NOCOPY VARCHAR2
1547 ,x_return_status OUT NOCOPY VARCHAR2
1548 ,x_msg_count OUT NOCOPY NUMBER
1549 ,x_msg_data OUT NOCOPY VARCHAR2
1550 )
1551 IS
1552
1553 l_mismatch_found BOOLEAN;
1554 l_key_item NUMBER;
1555 l_ch_view NUMBER;
1556 l_dim_short_name VARCHAR2(100);
1557 l_par_dim_short_name VARCHAR2(100);
1558 l_filter_values VARCHAR2(32000);
1559 l_sql VARCHAR2(1000);
1560 l_key_name VARCHAR2(1000);
1561 l_level_value NUMBER;
1562 l_rel_col VARCHAR2(100);
1563 l_ch_level_view VARCHAR2(100);
1564 l_pa_level_view VARCHAR2(100);
1565
1566 CURSOR c_dim_details(cp_dim_level_id NUMBER) IS
1567 SELECT * FROM bsc_sys_dim_levels_vl WHERE dim_level_id = cp_dim_level_id;
1568
1569 CURSOR c_kpi_dim_level_details(cp_level_shortname VARCHAR2) IS
1570 SELECT *
1571 FROM bsc_kpi_dim_levels_vl
1572 WHERE level_shortname = cp_level_shortname AND indicator IN (SELECT indicator
1573 FROM bsc_tab_indicators WHERE tab_id = p_tab_id
1574 );
1575
1576 CURSOR c_parents IS
1577 SELECT a.dim_level_id, a.parent_dim_level_id, a.relation_col
1578 FROM bsc_sys_dim_level_rels a
1579 WHERE a.dim_level_id = p_parent_level_id
1580 AND a.relation_type=1
1581 AND EXISTS ( SELECT indicator
1582 FROM bsc_kpi_dim_level_properties b
1583 WHERE b.dim_level_id = a.parent_dim_level_id
1584 AND indicator IN (SELECT indicator
1585 FROM bsc_tab_indicators WHERE tab_id = p_tab_id
1586 )
1587 );
1588
1589 TYPE ref_cursor IS REF CURSOR;
1590 ref_cur ref_cursor;
1591
1592
1593 BEGIN
1594 IF (p_tab_id IS NOT NULL AND p_dim_level_id is NOT NULL AND p_parent_level_id IS NOT NULL AND p_level_vals_list IS NOT NULL) THEN
1595 FOR cd IN c_dim_details(p_dim_level_id) LOOP
1596 l_dim_short_name := cd.short_name;
1597 l_ch_level_view := cd.level_view_name;
1598 EXIT;
1599 END LOOP;
1600
1601 FOR cd IN c_dim_details(p_parent_level_id) LOOP
1602 l_par_dim_short_name := cd.short_name;
1603 EXIT;
1604 END LOOP;
1605
1606 IF (l_dim_short_name IS NOT NULL AND l_dim_short_name IS NOT NULL ) THEN
1607
1608 FOR cd IN c_kpi_dim_level_details (l_dim_short_name) LOOP
1609 l_rel_col := cd.parent_level_rel;
1610 EXIT;
1611 END LOOP;
1612
1613 IF (l_rel_col IS NOT NULL AND l_ch_level_view IS NOT NULL) THEN
1614 FOR cd IN c_kpi_dim_level_details (l_par_dim_short_name) LOOP
1615 IF (cd.default_key_value IS NOT NULL) THEN
1616 l_pa_level_view := cd.level_view_name;
1617 l_mismatch_found := TRUE;
1618 l_sql := 'SELECT DISTINCT ' || l_rel_col || ' FROM ' || l_ch_level_view || ' WHERE CODE IN (' || p_level_vals_list || ' )';
1619
1620 IF (ref_cur%ISOPEN) THEN
1621 CLOSE ref_cur;
1622 END IF;
1623
1624 OPEN ref_cur for l_sql;
1625
1626
1627 LOOP
1628 FETCH ref_cur INTO l_level_value;
1629 EXIT WHEN ref_cur%NOTFOUND;
1630 IF (l_level_value IS NOT NULL AND l_level_value = cd.default_key_value) THEN
1631 l_mismatch_found := FALSE;
1632 EXIT;
1633 END IF;
1634 END LOOP;
1635
1636 CLOSE ref_cur;
1637
1638
1639 IF (l_mismatch_found AND l_pa_level_view IS NOT NULL) THEN
1640 l_key_name := BSC_DEFAULT_KEY_ITEM_PUB.get_table_column_value(
1641 p_table_name => l_pa_level_view
1642 ,p_column_name => 'NAME'
1643 ,p_where_cond => ' CODE = ' || cd.default_key_value
1644 );
1645 IF (l_key_name IS NOT NULL) THEN
1646 IF (p_mismatch_key_items IS NULL) THEN
1647 p_mismatch_key_items := l_key_name;
1648 ELSE
1649 p_mismatch_key_items := p_mismatch_key_items || ',' || l_key_name;
1650 END IF;
1651 END IF;
1652 END IF;
1653 END IF;
1654 END LOOP;
1655
1656
1657 FOR cd IN c_parents LOOP
1658 IF (cd.parent_dim_level_id IS NOT NULL) THEN
1659 l_filter_values := NULL;
1660 l_sql := 'SELECT DISTINCT ' || l_rel_col || ' FROM ' || l_ch_level_view || ' WHERE CODE IN (' || p_level_vals_list || ' )';
1661 IF (ref_cur%ISOPEN) THEN
1662 CLOSE ref_cur;
1663 END IF;
1664
1665 OPEN ref_cur for l_sql;
1666
1667 LOOP
1668 FETCH ref_cur INTO l_level_value;
1669 EXIT WHEN ref_cur%NOTFOUND;
1670 IF (l_level_value IS NOT NULL) THEN
1671 IF (l_filter_values IS NULL) THEN
1672 l_filter_values := l_level_value ;
1673 ELSE
1674 l_filter_values := l_filter_values || ',' || l_level_value ;
1675 END IF;
1676 END IF;
1677 END LOOP;
1678
1679 IF (l_filter_values IS NOT NULL) THEN
1680 validate_parent_key_items (
1681 p_tab_id => p_tab_id
1682 ,p_dim_level_id => p_parent_level_id
1683 ,p_parent_level_id => cd.parent_dim_level_id
1684 ,p_level_vals_list => l_filter_values
1685 ,p_mismatch_key_items => p_mismatch_key_items
1686 ,x_return_status => x_return_status
1687 ,x_msg_count => x_msg_count
1688 ,x_msg_data => x_msg_data
1689 );
1690 END IF;
1691 END IF;
1692 END LOOP;
1693 END IF;
1694 END IF;
1695 END IF;
1696
1697 EXCEPTION
1698
1699 WHEN FND_API.G_EXC_ERROR THEN
1700 IF (ref_cur%ISOPEN) THEN
1701 CLOSE ref_cur;
1702 END IF;
1703 FND_MSG_PUB.Count_And_Get
1704 ( p_encoded => FND_API.G_FALSE
1705 , p_count => x_msg_count
1706 , p_data => x_msg_data
1707 );
1708 x_return_status := FND_API.G_RET_STS_ERROR;
1709 RAISE;
1710
1711 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1712 IF (ref_cur%ISOPEN) THEN
1713 CLOSE ref_cur;
1714 END IF;
1715
1716 FND_MSG_PUB.Count_And_Get
1717 ( p_encoded => FND_API.G_FALSE
1718 , p_count => x_msg_count
1719 , p_data => x_msg_data
1720 );
1721 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1722 RAISE;
1723
1724 WHEN NO_DATA_FOUND THEN
1725 IF (ref_cur%ISOPEN) THEN
1726 CLOSE ref_cur;
1727 END IF;
1728
1729 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1730 IF (x_msg_data IS NOT NULL) THEN
1731 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.validate_parent_key_items ';
1732 ELSE
1733 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.validate_parent_key_items ';
1734 END IF;
1735
1736 RAISE;
1737
1738 WHEN OTHERS THEN
1739 IF (ref_cur%ISOPEN) THEN
1740 CLOSE ref_cur;
1741 END IF;
1742
1743 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1744 IF (x_msg_data IS NOT NULL) THEN
1745 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.validate_parent_key_items ';
1746 ELSE
1747 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.validate_parent_key_items ';
1748 END IF;
1749 RAISE;
1750
1751 END validate_parent_key_items;
1752
1753
1754 PROCEDURE validate_child_key_items(
1755 p_tab_id IN NUMBER
1756 ,p_dim_level_id IN NUMBER
1757 ,p_child_level_id IN NUMBER
1758 ,p_level_vals_list IN VARCHAR2
1759 ,p_mismatch_key_items IN OUT NOCOPY VARCHAR2
1760 ,x_return_status OUT NOCOPY VARCHAR2
1761 ,x_msg_count OUT NOCOPY NUMBER
1762 ,x_msg_data OUT NOCOPY VARCHAR2
1763 )
1764 IS
1765
1766 l_mismatch_found BOOLEAN;
1767 l_key_item NUMBER;
1768 l_ch_view NUMBER;
1769 l_dim_short_name VARCHAR2(100);
1770 l_ch_dim_short_name VARCHAR2(100);
1771 l_filter_values VARCHAR2(32000);
1772 l_sql VARCHAR2(1000);
1773 l_key_name VARCHAR2(1000);
1774 l_level_value VARCHAR2(1000);
1775 l_rel_col VARCHAR2(100);
1776 l_ch_level_view VARCHAR2(100);
1777 l_pa_level_view VARCHAR2(100);
1778
1779 CURSOR c_dim_details(cp_dim_level_id NUMBER) IS
1780 SELECT short_name FROM bsc_sys_dim_levels_vl WHERE dim_level_id = cp_dim_level_id;
1781
1782 CURSOR c_kpi_dim_level_details(cp_level_shortname VARCHAR2) IS
1783 SELECT *
1784 FROM bsc_kpi_dim_levels_vl
1785 WHERE level_shortname = cp_level_shortname AND indicator IN (SELECT indicator
1786 FROM bsc_tab_indicators WHERE tab_id = p_tab_id
1787 );
1788
1789 CURSOR c_childs IS
1790 SELECT a.dim_level_id child_dim_level_id,
1791 (SELECT b.short_name FROM bsc_sys_dim_levels_vl b WHERE b.dim_level_id = a.dim_level_id and rownum < 2) child_short_name
1792 FROM bsc_sys_dim_level_rels a
1793 WHERE a.parent_dim_level_id = p_dim_level_id
1794 AND a.relation_type=1
1795 AND a.dim_level_id IN
1796 (SELECT dim_level_id
1797 FROM bsc_kpi_dim_level_properties WHERE indicator IN
1798 (SELECT indicator FROM bsc_tab_indicators WHERE tab_id = p_tab_id));
1799
1800 TYPE ref_cursor IS REF CURSOR;
1801 ref_cur ref_cursor;
1802
1803
1804 BEGIN
1805 IF (p_tab_id IS NOT NULL AND p_dim_level_id is NOT NULL AND p_child_level_id IS NOT NULL AND p_level_vals_list IS NOT NULL) THEN
1806 FOR cd IN c_dim_details(p_dim_level_id) LOOP
1807 l_dim_short_name := cd.short_name;
1808 EXIT;
1809 END LOOP;
1810
1811 FOR cd IN c_dim_details(p_child_level_id) LOOP
1812 l_ch_dim_short_name := cd.short_name;
1813 EXIT;
1814 END LOOP;
1815
1816 FOR cd IN c_kpi_dim_level_details(l_dim_short_name) LOOP
1817 l_pa_level_view := cd.level_view_name;
1818 EXIT;
1819 END LOOP;
1820
1821
1822 IF (l_dim_short_name IS NOT NULL AND l_dim_short_name IS NOT NULL AND l_pa_level_view IS NOT NULL) THEN
1823
1824 FOR cd IN c_kpi_dim_level_details (l_ch_dim_short_name) LOOP
1825
1826 l_rel_col := cd.parent_level_rel;
1827 l_ch_level_view := cd.level_view_name ;
1828
1829
1830 IF (cd.default_key_value IS NOT NULL) THEN
1831 l_mismatch_found := TRUE;
1832 l_sql := 'SELECT DISTINCT CODE FROM ' || l_ch_level_view || ' WHERE ' || l_rel_col || ' IN (' || p_level_vals_list || ' )';
1833 IF (ref_cur%ISOPEN) THEN
1834 CLOSE ref_cur;
1835 END IF;
1836
1837 OPEN ref_cur for l_sql;
1838
1839 LOOP
1840 FETCH ref_cur INTO l_level_value;
1841 EXIT WHEN ref_cur%NOTFOUND;
1842 IF (l_level_value IS NOT NULL AND l_level_value = cd.default_key_value) THEN
1843 l_mismatch_found := FALSE;
1844 EXIT;
1845 END IF;
1846 END LOOP;
1847
1848 IF (l_mismatch_found ) THEN
1849
1850 l_key_name := BSC_DEFAULT_KEY_ITEM_PUB.get_table_column_value(
1851 p_table_name => l_ch_level_view
1852 ,p_column_name => 'NAME'
1853 ,p_where_cond => ' CODE = ' || cd.default_key_value
1854 );
1855
1856 IF (l_key_name IS NOT NULL) THEN
1857 IF (p_mismatch_key_items IS NULL) THEN
1858 p_mismatch_key_items := l_key_name;
1859 ELSE
1860 p_mismatch_key_items := p_mismatch_key_items || ',' || l_key_name;
1861 END IF;
1862 END IF;
1863
1864 END IF;
1865 END IF;
1866 END LOOP;
1867
1868
1869 FOR cd IN c_childs LOOP
1870 IF (cd.child_dim_level_id IS NOT NULL) THEN
1871 l_filter_values := NULL;
1872 FOR cdd IN c_kpi_dim_level_details (cd.child_short_name) LOOP
1873 l_rel_col := cdd.parent_level_rel;
1874 l_ch_level_view := cdd.level_view_name ;
1875 EXIT;
1876 END LOOP;
1877
1878
1879
1880
1881 IF (l_rel_col IS NOT NULL AND l_ch_level_view IS NOT NULL) THEN
1882
1883 l_sql := 'SELECT DISTINCT CODE FROM ' || l_ch_level_view || ' WHERE ' || l_rel_col || ' IN (' || p_level_vals_list || ' )';
1884
1885 IF (ref_cur%ISOPEN) THEN
1886 CLOSE ref_cur;
1887 END IF;
1888
1889 OPEN ref_cur for l_sql;
1890
1891
1892
1893 LOOP
1894 FETCH ref_cur INTO l_level_value;
1895 EXIT WHEN ref_cur%NOTFOUND;
1896 IF (l_level_value IS NOT NULL) THEN
1897 IF (l_filter_values IS NULL) THEN
1898 l_filter_values := l_level_value ;
1899 ELSE
1900 l_filter_values := l_filter_values || ',' || l_level_value ;
1901 END IF;
1902 END IF;
1903 END LOOP;
1904
1905
1906
1907 IF (l_filter_values IS NOT NULL) THEN
1908 validate_child_key_items (
1909 p_tab_id => p_tab_id
1910 ,p_dim_level_id => p_child_level_id
1911 ,p_child_level_id => cd.child_dim_level_id
1912 ,p_level_vals_list => l_filter_values
1913 ,p_mismatch_key_items => p_mismatch_key_items
1914 ,x_return_status => x_return_status
1915 ,x_msg_count => x_msg_count
1916 ,x_msg_data => x_msg_data
1917 );
1918 END IF;
1919 END IF;
1920 END IF;
1921 END LOOP;
1922
1923
1924 END IF;
1925 END IF;
1926
1927 EXCEPTION
1928
1929 WHEN FND_API.G_EXC_ERROR THEN
1930 IF (ref_cur%ISOPEN) THEN
1931 CLOSE ref_cur;
1932 END IF;
1933 FND_MSG_PUB.Count_And_Get
1934 ( p_encoded => FND_API.G_FALSE
1935 , p_count => x_msg_count
1936 , p_data => x_msg_data
1937 );
1938 x_return_status := FND_API.G_RET_STS_ERROR;
1939 RAISE;
1940
1941 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1942 IF (ref_cur%ISOPEN) THEN
1943 CLOSE ref_cur;
1944 END IF;
1945
1946 FND_MSG_PUB.Count_And_Get
1947 ( p_encoded => FND_API.G_FALSE
1948 , p_count => x_msg_count
1949 , p_data => x_msg_data
1950 );
1951 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1952 RAISE;
1953
1954 WHEN NO_DATA_FOUND THEN
1955 IF (ref_cur%ISOPEN) THEN
1956 CLOSE ref_cur;
1957 END IF;
1958
1959 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1960 IF (x_msg_data IS NOT NULL) THEN
1961 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.validate_child_key_items ';
1962 ELSE
1963 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.validate_child_key_items ';
1964 END IF;
1965
1966 RAISE;
1967
1968 WHEN OTHERS THEN
1969 IF (ref_cur%ISOPEN) THEN
1970 CLOSE ref_cur;
1971 END IF;
1972
1973 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1974 IF (x_msg_data IS NOT NULL) THEN
1975 x_msg_data := x_msg_data||' -> BSC_DIM_LEVEL_FILTERS_PUB.validate_child_key_items ';
1976 ELSE
1977 x_msg_data := SQLERRM||' at BSC_DIM_LEVEL_FILTERS_PUB.validate_child_key_items ';
1978 END IF;
1979 RAISE;
1980
1981 END validate_child_key_items;
1982
1983
1984 END BSC_DIM_LEVEL_FILTERS_PUB;