[Home] [Help]
PACKAGE BODY: APPS.BSC_COMMON_DIMENSIONS_PVT
Source
1 PACKAGE BODY BSC_COMMON_DIMENSIONS_PVT AS
2 /* $Header: BSCVLIBB.pls 120.2.12000000.1 2007/07/17 07:44:52 appldev noship $ */
3
4 PROCEDURE Get_Parent_level_properties
5 (
6 p_tab_id IN BSC_TABS_B.tab_id%TYPE
7 ,p_level_index IN BSC_KPI_DIM_LEVELS_B.dim_level_index%TYPE
8 ,x_parent_level_id OUT NOCOPY BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE
9 ,x_parent_level_index OUT NOCOPY BSC_KPI_DIM_LEVELS_B.parent_level_index%TYPE
10 );
11
12 -- The following API saves LIST BUTTON (Common Dimension) configuration
13 -- for a particular SCORECARD.
14 -- INPUT :
15 -- p_new_list_config A semicolon(;) seperated values of common dimension objects
16 -- that have to be saved.
17 -- NOTE: Each common dimension object record contains a commma seperated list of the following
18 -- properties in order:
19 -- (dim_level_index, dim_level_id, parent_level_index, parent_level_id)
20
21 PROCEDURE insert_common_dimensions
22 (
23 p_tab_id IN NUMBER
24 ,p_new_list_config IN VARCHAR2
25 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
26 ,x_return_status OUT NOCOPY VARCHAR2
27 ,x_msg_count OUT NOCOPY NUMBER
28 ,x_msg_data OUT NOCOPY VARCHAR2
29 ) IS
30
31 l_row_cnt NUMBER;
32 l_dim_level_id NUMBER;
33 l_parent_level_id NUMBER;
34 l_dim_level_index NUMBER;
35 l_parent_level_index NUMBER;
36 l_dim_obj_recs BSC_UTILITY.varchar_tabletype;
37 l_dim_obj_cnt NUMBER;
38 l_dim_props BSC_UTILITY.varchar_tabletype;
39 l_cnt NUMBER;
40
41 BEGIN
42
43 IF (p_tab_id IS NOT NULL AND p_new_list_config IS NOT NULL) THEN
44 BSC_UTILITY.Parse_String(
45 p_List => p_new_list_config,
46 p_Separator => ';',
47 p_List_Data => l_dim_obj_recs,
48 p_List_number => l_dim_obj_cnt
49 );
50
51 FOR i IN 1..l_dim_obj_cnt LOOP
52
53 BSC_UTILITY.Parse_String(
54 p_List => l_dim_obj_recs(i),
55 p_Separator => ',',
56 p_List_Data => l_dim_props,
57 p_List_number => l_cnt
58 );
59
60 l_dim_level_id := TO_NUMBER(l_dim_props(2));
61 l_dim_level_index := TO_NUMBER(l_dim_props(1));
62 l_parent_level_index := TO_NUMBER(l_dim_props(3));
63 IF (l_cnt = 4) THEN
64 l_parent_level_id := TO_NUMBER(l_dim_props(4));
65 ELSE
66 l_parent_level_id := NULL;
67 END IF;
68
69
70
71 IF (l_dim_level_id IS NOT NULL AND l_dim_level_index IS NOT NULL) THEN
72
73 SELECT count(0) INTO l_row_cnt
74 FROM bsc_sys_com_dim_levels
75 WHERE tab_id = p_tab_id
76 AND dim_level_id = l_dim_level_id
77 AND dim_level_index = l_dim_level_index;
78
79 IF (l_row_cnt = 0) THEN
80 -- before creating records we will update default_value in bsc_kpi_dim_levels_b.the same way as it was
81 -- happening in VB
82 BSC_COMMON_DIMENSIONS_PVT.set_dim_default_value
83 (
84 p_dim_level_id => l_dim_level_id
85 ,p_default_value => 'D'||l_dim_level_index
86 ,p_Tab_Id => p_tab_id
87 ,x_return_status => x_return_status
88 ,x_msg_count => x_msg_count
89 ,x_msg_data => x_msg_data
90 );
91 IF(x_return_status IS NOT NULL AND x_return_status <> FND_API.G_RET_STS_SUCCESS)THEN
92 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
93 END IF;
94
95 BSC_COMMON_DIMENSIONS_PVT.Get_Parent_level_properties
96 (
97 p_tab_id => p_tab_id
98 ,p_level_index => l_dim_level_index
99 ,x_parent_level_id => l_parent_level_id
100 ,x_parent_level_index => l_parent_level_index
101 );
102
103 INSERT
104 INTO bsc_sys_com_dim_levels (tab_id, dim_level_index, dim_level_id, parent_level_index, parent_dim_level_id)
105 VALUES(p_tab_id, l_dim_level_index, l_dim_level_id, l_parent_level_index, l_parent_level_id);
106 END IF;
107 END IF;
108 END LOOP;
109
110 IF (p_commit = FND_API.G_TRUE) THEN
111 COMMIT;
112 END IF;
113 END IF;
114
115 EXCEPTION
116 WHEN FND_API.G_EXC_ERROR THEN
117 FND_MSG_PUB.Count_And_Get
118 ( p_encoded => FND_API.G_FALSE
119 , p_count => x_msg_count
120 , p_data => x_msg_data
121 );
122
123 x_return_status := FND_API.G_RET_STS_ERROR;
124 RAISE;
125 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
126 FND_MSG_PUB.Count_And_Get
127 ( p_encoded => FND_API.G_FALSE
128 , p_count => x_msg_count
129 , p_data => x_msg_data
130 );
131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
132
133 RAISE;
134 WHEN NO_DATA_FOUND THEN
135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
136 IF (x_msg_data IS NOT NULL) THEN
137 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PVT.insert_common_dimensions ';
138 ELSE
139 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PVT.insert_common_dimensions ';
140 END IF;
141
142 RAISE;
143 WHEN OTHERS THEN
144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
145 IF (x_msg_data IS NOT NULL) THEN
146 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PVT.insert_common_dimensions ';
147 ELSE
148 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PVT.insert_common_dimensions ';
149 END IF;
150
151 RAISE;
152 END insert_common_dimensions;
153
154
155
156 -- The following API removes common dimensions for a given scorecard.
157
158 PROCEDURE delete_common_dimensions
159 (
160 p_tab_id IN NUMBER
161 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
162 ,x_return_status OUT NOCOPY VARCHAR2
163 ,x_msg_count OUT NOCOPY NUMBER
164 ,x_msg_data OUT NOCOPY VARCHAR2
165 ) IS
166 BEGIN
167 x_return_status := FND_API.G_RET_STS_SUCCESS;
168
169 IF (p_tab_id IS NOT NULL ) THEN
170 DELETE
171 FROM bsc_sys_com_dim_levels
172 WHERE tab_id = p_tab_id;
173
174 END IF;
175 IF (p_commit = FND_API.G_TRUE) THEN
176 COMMIT;
177 END IF;
178
179 EXCEPTION
180 WHEN FND_API.G_EXC_ERROR THEN
181 FND_MSG_PUB.Count_And_Get
182 ( p_encoded => FND_API.G_FALSE
183 , p_count => x_msg_count
184 , p_data => x_msg_data
185 );
186
187 x_return_status := FND_API.G_RET_STS_ERROR;
188 RAISE;
189 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
190 FND_MSG_PUB.Count_And_Get
191 ( p_encoded => FND_API.G_FALSE
192 , p_count => x_msg_count
193 , p_data => x_msg_data
194 );
195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
196
197 RAISE;
198 WHEN NO_DATA_FOUND THEN
199 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
200 IF (x_msg_data IS NOT NULL) THEN
201 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PVT.delete_common_dimensions ';
202 ELSE
203 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PVT.delete_common_dimensions ';
204 END IF;
205
206 RAISE;
207 WHEN OTHERS THEN
208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
209 IF (x_msg_data IS NOT NULL) THEN
210 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PVT.delete_common_dimensions ';
211 ELSE
212 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PVT.delete_common_dimensions ';
213 END IF;
214
215 RAISE;
216
217 END delete_common_dimensions;
218
219
220 PROCEDURE delete_common_dimensions_tabs (
221 p_commit IN VARCHAR2 := FND_API.G_FALSE
222 , p_tab_ids IN VARCHAR2
223 , x_return_status OUT NOCOPY VARCHAR2
224 , x_msg_count OUT NOCOPY NUMBER
225 , x_msg_data OUT NOCOPY VARCHAR2
226 )
227 IS
228 l_tab_ids VARCHAR2(1000);
229 l_tab_id VARCHAR2(10);
230 BEGIN
231
232 x_return_status := FND_API.G_RET_STS_SUCCESS;
233
234 IF p_tab_ids IS NOT NULL THEN
235 l_tab_ids := p_tab_ids ;
236 WHILE (BSC_BIS_KPI_MEAS_PUB.is_more ( p_dim_short_names => l_tab_ids
237 , p_dim_short_name => l_tab_id)) LOOP
238 delete_common_dimensions (
239 p_tab_id => l_tab_id
240 , p_commit => p_commit
241 , x_return_status => x_return_status
242 , x_msg_count => x_msg_count
243 , x_msg_data => x_msg_data
244 );
245
246 IF (x_return_status IS NULL OR x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
248 END IF;
249
250 END LOOP;
251
252 END IF;
253
254 EXCEPTION
255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
256 FND_MSG_PUB.Count_And_Get
257 ( p_encoded => FND_API.G_FALSE
258 , p_count => x_msg_count
259 , p_data => x_msg_data
260 );
261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
262 RAISE;
263 WHEN OTHERS THEN
264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
265 IF (x_msg_data IS NOT NULL) THEN
266 x_msg_data := x_msg_data || ' -> BSC_COMMON_DIMENSIONS_PVT.delete_common_dimensions_tabs ';
267 ELSE
268 x_msg_data := SQLERRM || ' at BSC_COMMON_DIMENSIONS_PVT.delete_common_dimensions_tabs ';
269 END IF;
270 RAISE;
271 END delete_common_dimensions_tabs;
272
273
274 PROCEDURE delete_user_list_access
275 (
276 p_tab_id IN NUMBER
277 ,p_dim_level_index IN NUMBER
278 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
279 ,x_return_status OUT NOCOPY VARCHAR2
280 ,x_msg_count OUT NOCOPY NUMBER
281 ,x_msg_data OUT NOCOPY VARCHAR2
282 ) IS
283 BEGIN
284
285 IF (p_tab_id IS NOT NULL AND p_dim_level_index IS NOT NULL) THEN
286
287 DELETE
288 FROM BSC_USER_LIST_ACCESS
289 WHERE tab_id = p_tab_id
290 AND DIM_LEVEL_INDEX >= p_dim_level_index;
291
292 IF (p_commit = FND_API.G_TRUE) THEN
293 COMMIT;
294 END IF;
295
296 END IF;
297
298 EXCEPTION
299
300 WHEN FND_API.G_EXC_ERROR THEN
301 FND_MSG_PUB.Count_And_Get
302 ( p_encoded => FND_API.G_FALSE
303 , p_count => x_msg_count
304 , p_data => x_msg_data
305 );
306
307 x_return_status := FND_API.G_RET_STS_ERROR;
308 RAISE;
309 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
310 FND_MSG_PUB.Count_And_Get
311 ( p_encoded => FND_API.G_FALSE
312 , p_count => x_msg_count
313 , p_data => x_msg_data
314 );
315 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
316
317 RAISE;
318 WHEN NO_DATA_FOUND THEN
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_DIMENSIONS_PVT.delete_user_list_access ';
322 ELSE
323 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PVT.delete_user_list_access ';
324 END IF;
325
326 RAISE;
327 WHEN OTHERS THEN
328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
329 IF (x_msg_data IS NOT NULL) THEN
330 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PVT.delete_user_list_access ';
331 ELSE
332 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PVT.delete_user_list_access ';
333 END IF;
334
335 RAISE;
336
337 END delete_user_list_access;
338
339
340
341 PROCEDURE insert_user_list_access
342 (
343 p_responsibility_id IN bsc_user_list_access.responsibility_id%TYPE
344 ,p_tab_id IN bsc_user_list_access.tab_id%TYPE
345 ,p_dim_level_index IN bsc_user_list_access.dim_level_index%TYPE
346 ,p_dim_level_value IN bsc_user_list_access.dim_level_value%TYPE
347 ,p_creation_date IN bsc_user_list_access.creation_date%TYPE
348 ,p_created_by IN bsc_user_list_access.created_by%TYPE
349 ,p_last_update_date IN bsc_user_list_access.last_update_date%TYPE
350 ,p_last_updated_by IN bsc_user_list_access.last_updated_by%TYPE
351 ,p_last_update_login IN bsc_user_list_access.last_update_login%TYPE
352 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
353 ,x_return_status OUT NOCOPY VARCHAR2
354 ,x_msg_count OUT NOCOPY NUMBER
355 ,x_msg_data OUT NOCOPY VARCHAR2
356 )
357 IS
358
359 BEGIN
360
361 IF (p_responsibility_id IS NOT NULL AND p_tab_id IS NOT NULL AND p_dim_level_index IS NOT NULL AND p_dim_level_value IS NOT NULL ) THEN
362 IF (p_creation_date IS NOT NULL AND p_created_by IS NOT NULL AND p_last_update_date IS NOT NULL AND p_last_updated_by IS NOT NULL) THEN
363 INSERT
364 INTO
365 bsc_user_list_access(responsibility_id,
366 tab_id,
367 dim_level_index,
368 dim_level_value,
369 creation_date,
370 created_by,
371 last_update_date,
372 last_updated_by,
373 last_update_login)
374 VALUES ( p_responsibility_id
375 ,p_tab_id
376 ,p_dim_level_index
377 ,p_dim_level_value
378 ,p_creation_date
379 ,p_created_by
380 ,p_last_update_date
381 ,p_last_updated_by
382 ,p_last_update_login
383 );
384
385 IF (p_commit = FND_API.G_TRUE) THEN
386 COMMIT;
387 END IF;
388 END IF;
389 END IF;
390
391 EXCEPTION
392
393 WHEN FND_API.G_EXC_ERROR THEN
394 FND_MSG_PUB.Count_And_Get
395 ( p_encoded => FND_API.G_FALSE
396 , p_count => x_msg_count
397 , p_data => x_msg_data
398 );
399
400 x_return_status := FND_API.G_RET_STS_ERROR;
401 RAISE;
402 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
403 FND_MSG_PUB.Count_And_Get
404 ( p_encoded => FND_API.G_FALSE
405 , p_count => x_msg_count
406 , p_data => x_msg_data
407 );
408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
409
410 RAISE;
411 WHEN NO_DATA_FOUND THEN
412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
413 IF (x_msg_data IS NOT NULL) THEN
414 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PVT.insert_user_list_access ';
415 ELSE
416 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PVT.insert_user_list_access ';
417 END IF;
418
419 RAISE;
420 WHEN OTHERS THEN
421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
422 IF (x_msg_data IS NOT NULL) THEN
423 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PVT.insert_user_list_access ';
424 ELSE
425 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PVT.insert_user_list_access ';
426 END IF;
427
428 RAISE;
429
430 END insert_user_list_access;
431
432 /********************************************************
433 Procedure : reset_dim_default_value
434 This procedure resets the default value
435 to 'T' for all the common dimension objects
436 Input : Tab Id
437 Created By : ashankar
438 /********************************************************/
439
440 PROCEDURE reset_dim_default_value
441 (
442 p_Tab_Id IN BSC_TABS_B.tab_id%TYPE
443 ,x_return_status OUT NOCOPY VARCHAR2
444 ,x_msg_count OUT NOCOPY NUMBER
445 ,x_msg_data OUT NOCOPY VARCHAR2
446 ) IS
447 l_sql VARCHAR2(1000);
448 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
449
450 BEGIN
451 x_return_status := FND_API.G_RET_STS_SUCCESS;
452
453 UPDATE bsc_kpi_dim_levels_b
454 SET DEFAULT_VALUE = BSC_COMMON_DIMENSIONS_PVT.C_ALL
455 WHERE indicator IN (
456 SELECT indicator FROM bsc_tab_indicators WHERE tab_id = p_Tab_Id )
457 AND default_value like BSC_COMMON_DIMENSIONS_PVT.C_COM_DIM_DEFAULT_VALUE;
458
459 EXCEPTION
460
461 WHEN FND_API.G_EXC_ERROR THEN
462 FND_MSG_PUB.Count_And_Get
463 ( p_encoded => FND_API.G_FALSE
464 , p_count => x_msg_count
465 , p_data => x_msg_data
466 );
467
468 x_return_status := FND_API.G_RET_STS_ERROR;
469 RAISE;
470 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
471 FND_MSG_PUB.Count_And_Get
472 ( p_encoded => FND_API.G_FALSE
473 , p_count => x_msg_count
474 , p_data => x_msg_data
475 );
476 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
477
478 RAISE;
479 WHEN NO_DATA_FOUND THEN
480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
481 IF (x_msg_data IS NOT NULL) THEN
482 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PVT.reset_dim_default_value ';
483 ELSE
484 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PVT.reset_dim_default_value ';
485 END IF;
486
487 RAISE;
488 WHEN OTHERS THEN
489 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
490 IF (x_msg_data IS NOT NULL) THEN
491 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PVT.reset_dim_default_value ';
492 ELSE
493 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PVT.reset_dim_default_value ';
494 END IF;
495
496 RAISE;
497 END reset_dim_default_value;
498
499 /********************************************************
500 Procedure : reset_dim_default_value
501 This procedure resets the default value
502 to 'DX' for all the common dimension objects
503 Input : Tab Id
504 Created By : ashankar
505 /********************************************************/
506 PROCEDURE set_dim_default_value
507 (
508 p_dim_level_id IN BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE
509 ,p_default_value IN BSC_KPI_DIM_LEVELS_B.default_value%TYPE
510 ,p_Tab_Id IN BSC_TABS_B.tab_id%TYPE
511 ,x_return_status OUT NOCOPY VARCHAR2
512 ,x_msg_count OUT NOCOPY NUMBER
513 ,x_msg_data OUT NOCOPY VARCHAR2
514 ) IS
515 l_level_table_name BSC_KPI_DIM_LEVELS_B.level_table_name%TYPE;
516 l_sql VARCHAR2(1000);
517 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
518
519 BEGIN
520 x_return_status := FND_API.G_RET_STS_SUCCESS;
521
522 IF(p_dim_level_id IS NOT NULL AND p_default_value IS NOT NULL) THEN
523 SELECT level_table_name
524 INTO l_level_table_name
525 FROM bsc_sys_dim_levels_b
526 WHERE dim_level_id = p_dim_level_id;
527
528
529 UPDATE bsc_kpi_dim_levels_b
530 SET DEFAULT_VALUE = p_default_value
531 WHERE indicator IN (
532 SELECT indicator FROM bsc_tab_indicators WHERE tab_id = p_Tab_Id )
533 AND level_table_name = l_level_table_name;
534
535 END IF;
536
537 EXCEPTION
538
539 WHEN FND_API.G_EXC_ERROR THEN
540 FND_MSG_PUB.Count_And_Get
541 ( p_encoded => FND_API.G_FALSE
542 , p_count => x_msg_count
543 , p_data => x_msg_data
544 );
545
546 x_return_status := FND_API.G_RET_STS_ERROR;
547 RAISE;
548 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
549 FND_MSG_PUB.Count_And_Get
550 ( p_encoded => FND_API.G_FALSE
551 , p_count => x_msg_count
552 , p_data => x_msg_data
553 );
554 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
555
556 RAISE;
557 WHEN NO_DATA_FOUND THEN
558 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
559 IF (x_msg_data IS NOT NULL) THEN
560 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PVT.set_dim_default_value ';
561 ELSE
562 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PVT.set_dim_default_value ';
563 END IF;
564
565 RAISE;
566 WHEN OTHERS THEN
567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
568 IF (x_msg_data IS NOT NULL) THEN
569 x_msg_data := x_msg_data||' -> BSC_COMMON_DIMENSIONS_PVT.set_dim_default_value ';
570 ELSE
571 x_msg_data := SQLERRM||' at BSC_COMMON_DIMENSIONS_PVT.set_dim_default_value ';
572 END IF;
573
574 RAISE;
575 END set_dim_default_value;
576
577 PROCEDURE Get_Parent_level_properties
578 (
579 p_tab_id IN BSC_TABS_B.tab_id%TYPE
580 ,p_level_index IN BSC_KPI_DIM_LEVELS_B.dim_level_index%TYPE
581 ,x_parent_level_id OUT NOCOPY BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE
582 ,x_parent_level_index OUT NOCOPY BSC_KPI_DIM_LEVELS_B.parent_level_index%TYPE
583 ) IS
584 BEGIN
585 x_parent_level_id :=NULL;
586 x_parent_level_index :=NULL;
587
588 IF(p_level_index >0) THEN
589 x_parent_level_index := p_level_index -1;
590 SELECT dim_level_id
591 INTO x_parent_level_id
592 FROM bsc_sys_com_dim_levels
593 WHERE tab_id = p_tab_id
594 AND dim_level_index = x_parent_level_index;
595 END IF;
596
597 EXCEPTION
598 WHEN OTHERS THEN
599 x_parent_level_id :=NULL;
600 x_parent_level_index :=NULL;
601
602 END Get_Parent_level_properties;
603
604
605
606 END BSC_COMMON_DIMENSIONS_PVT;
607