[Home] [Help]
PACKAGE BODY: APPS.BSC_DEFAULT_KEY_ITEM_PUB
Source
1 PACKAGE BODY BSC_DEFAULT_KEY_ITEM_PUB AS
2 /* $Header: BSCPDKIB.pls 120.4.12000000.2 2007/10/15 06:41:06 psomesul noship $ */
3
4 PROCEDURE Validate_key_shared_obj
5 (
6 p_kpi_id IN BSC_KPIS_B.indicator%TYPE
7 , p_params IN VARCHAR2
8 , x_return_status OUT NOCOPY VARCHAR2
9 , x_msg_count OUT NOCOPY NUMBER
10 , x_msg_data OUT NOCOPY VARCHAR2
11 )IS
12
13 l_dim_level_id BSC_SYS_DIM_LEVELS_B.dim_level_id%TYPE;
14 l_dim_level_val BSC_SYS_FILTERS.dim_level_value%TYPE;
15 l_score_list VARCHAR2(30000);
16 l_level_view_name BSC_KPI_DIM_LEVELS_B.level_view_name%TYPE;
17 l_dim_obj_recs BSC_UTILITY.varchar_tabletype;
18 l_dim_obj_rec VARCHAR2(200);
19 l_dim_obj_cnt NUMBER;
20 l_key_name VARCHAR2(1000);
21 l_dim_props BSC_UTILITY.varchar_tabletype;
22 l_dim_set_id VARCHAR2(20);
23 l_dim_level_index VARCHAR2(20);
24 l_def_key_id VARCHAR2(20);
25 l_cnt NUMBER;
26
27 CURSOR c_key IS
28 SELECT a.INDICATOR,
29 a.tab_id,
30 b.name,
31 DECODE (
32 (SELECT COUNT(0)
33 FROM bsc_sys_filters
34 WHERE source_type =1
35 AND source_code =a.tab_id
36 AND dim_level_id =l_dim_level_id
37 ),0,1,
38 (SELECT COUNT(0)
39 FROM bsc_sys_filters
40 WHERE source_type =1
41 AND source_code =a.tab_id
42 AND dim_level_id =l_dim_level_id
43 AND dim_level_value =l_def_key_id)) total
44 FROM bsc_tab_indicators a,
45 bsc_tabs_vl b
46 WHERE a.tab_id =b.tab_id
47 AND a.indicator IN
48 (SELECT INDICATOR
49 FROM bsc_kpis_vl
50 WHERE source_indicator =p_kpi_id
51 AND prototype_flag<>2);
52 BEGIN
53 FND_MSG_PUB.Initialize;
54 x_return_status := FND_API.G_RET_STS_SUCCESS;
55 IF(p_kpi_id IS NOT NULL AND p_params IS NOT NULL) THEN
56
57 BSC_UTILITY.Parse_String
58 (
59 p_List => p_params,
60 p_Separator => ';',
61 p_List_Data => l_dim_obj_recs,
62 p_List_number => l_dim_obj_cnt
63 );
64 FOR i IN 1..l_dim_obj_cnt LOOP
65 l_dim_obj_rec := l_dim_obj_recs(i);
66 BSC_UTILITY.Parse_String
67 (
68 p_List => l_dim_obj_rec,
69 p_Separator => ',',
70 p_List_Data => l_dim_props,
71 p_List_number => l_cnt
72 );
73
74 l_dim_set_id := l_dim_props(1);
75 l_dim_level_id := l_dim_props(2);
76 l_dim_level_index := l_dim_props(3);
77 l_def_key_id := l_dim_props(4);
78
79 IF(l_dim_set_id IS NOT NULL AND l_dim_level_id IS NOT NULL
80 AND l_dim_level_index IS NOT NULL AND l_def_key_id IS NOT NULL ) THEN
81
82 FOR cd IN c_key LOOP
83 IF(cd.total=0) THEN
84 IF(l_score_list IS NULL) THEN
85 l_score_list := cd.name;
86 ELSE
87 l_score_list := l_score_list || ',' || cd.name;
88 END IF;
89 END IF;
90 END LOOP;
91
92 IF(l_score_list IS NOT NULL) THEN
93
94 SELECT level_view_name
95 INTO l_level_view_name
96 FROM bsc_kpi_dim_levels_vl
97 WHERE indicator = p_kpi_id
98 AND dim_set_id = l_dim_set_id
99 AND dim_level_index = l_dim_level_index;
100
101 l_key_name := BSC_DEFAULT_KEY_ITEM_PUB.get_table_column_value
102 (
103 p_table_name => l_level_view_name
104 , p_column_name => 'NAME'
105 , p_where_cond => 'CODE=' || l_def_key_id
106 );
107 FND_MESSAGE.SET_NAME('BSC','BSC_SHARED_OBJ_KEY_ERROR');
108 FND_MESSAGE.SET_TOKEN('KEY_NAME',l_key_name, TRUE);
109 FND_MESSAGE.SET_TOKEN('LIST',l_score_list, TRUE);
110 FND_MSG_PUB.ADD;
111 RAISE FND_API.G_EXC_ERROR;
112 END IF;
113 END IF;
114 END LOOP;
115 END IF;
116 EXCEPTION
117 WHEN FND_API.G_EXC_ERROR THEN
118 IF (x_msg_data IS NULL) THEN
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 END IF;
125 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
126 x_return_status := FND_API.G_RET_STS_ERROR;
127 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
128 IF (x_msg_data IS NULL) THEN
129 FND_MSG_PUB.Count_And_Get
130 ( p_encoded => FND_API.G_FALSE
131 , p_count => x_msg_count
132 , p_data => x_msg_data
133 );
134 END IF;
135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
136 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
137
138 WHEN NO_DATA_FOUND THEN
139 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
140 IF (x_msg_data IS NOT NULL) THEN
141 x_msg_data := x_msg_data||' -> BSC_DEFAULT_KEY_ITEM_PUB.Validate_key_shared_obj ';
142 ELSE
143 x_msg_data := SQLERRM||' at BSC_DEFAULT_KEY_ITEM_PUB.Validate_key_shared_obj ';
144 END IF;
145 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
146 WHEN OTHERS THEN
147
148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
149 IF (x_msg_data IS NOT NULL) THEN
150 x_msg_data := x_msg_data||' -> BSC_DEFAULT_KEY_ITEM_PUB.Validate_key_shared_obj ';
151 ELSE
152 x_msg_data := SQLERRM||' at BSC_DEFAULT_KEY_ITEM_PUB.Validate_key_shared_obj ';
153 END IF;
154 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
155 END Validate_key_shared_obj;
156
157
158 /* The following API updates default key items
159 */
160
161 PROCEDURE Update_Default_Key_Items(
162 p_kpi_id IN VARCHAR2
163 , p_params IN VARCHAR2
164 , p_commit IN VARCHAR2 := FND_API.G_FALSE
165 , x_return_status OUT NOCOPY VARCHAR2
166 , x_msg_count OUT NOCOPY NUMBER
167 , x_msg_data OUT NOCOPY VARCHAR2
168 ) IS
169
170 l_count NUMBER;
171
172 BEGIN
173 FND_MSG_PUB.Initialize;
174 x_return_status := FND_API.G_RET_STS_SUCCESS;
175
176 IF (p_kpi_id IS NOT NULL AND p_params IS NOT NULL) THEN
177
178 SAVEPOINT bsc_key_item_pub_upd_key_item;
179
180 SELECT count(0) INTO l_count
181 FROM bsc_kpis_b
182 WHERE indicator = p_kpi_id
183 AND source_indicator IS NULL
184 AND (share_flag =1 OR share_flag = 0) -- OBJECTIVE should be a master or new objective
185 AND prototype_flag <> 2;
186
187 IF (l_count = 1) THEN -- If MASTER OBJECTIVE
188
189 Update_Key_Item
190 (
191 p_kpi_id => p_kpi_id
192 , p_params => p_params
193 , p_commit => p_commit
194 , x_return_status => x_return_status
195 , x_msg_count => x_msg_count
196 , x_msg_data => x_msg_data
197 );
198
199 IF(x_return_status IS NOT NULL AND x_return_status<>FND_API.G_RET_STS_SUCCESS)THEN
200 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
201 END IF;
202
203 Cascade_Key_Item_Changes --CASCADE the changes to the shared objective also
204 (
205 p_kpi_id => p_kpi_id
206 , p_params => p_params
207 , p_commit => p_commit
208 , x_return_status => x_return_status
209 , x_msg_count => x_msg_count
210 , x_msg_data => x_msg_data
211 );
212 IF(x_return_status IS NOT NULL AND x_return_status<>FND_API.G_RET_STS_SUCCESS)THEN
213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
214 END IF;
215 END IF;
216
217 END IF;
218
219 IF (p_commit = FND_API.G_TRUE) THEN
220 COMMIT;
221 END IF;
222
223 EXCEPTION
224 WHEN FND_API.G_EXC_ERROR THEN
225 ROLLBACK TO bsc_key_item_pub_upd_key_item;
226 IF (x_msg_data IS NULL) THEN
227 FND_MSG_PUB.Count_And_Get
228 ( p_encoded => FND_API.G_FALSE
229 , p_count => x_msg_count
230 , p_data => x_msg_data
231 );
232 END IF;
233 x_return_status := FND_API.G_RET_STS_ERROR;
234 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
235 ROLLBACK TO bsc_key_item_pub_upd_key_item;
236 IF (x_msg_data IS NULL) THEN
237 FND_MSG_PUB.Count_And_Get
238 ( p_encoded => FND_API.G_FALSE
239 , p_count => x_msg_count
240 , p_data => x_msg_data
241 );
242 END IF;
243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
244 WHEN OTHERS THEN
245 ROLLBACK TO bsc_key_item_pub_upd_key_item;
246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
247 IF (x_msg_data IS NOT NULL) THEN
248 x_msg_data := x_msg_data||' -> BSC_DEFAULT_KEY_ITEM_PUB.Update_Default_Key_Items ';
249 ELSE
250 x_msg_data := SQLERRM||' at BSC_DEFAULT_KEY_ITEM_PUB.Update_Default_Key_Items ';
251 END IF;
252 END Update_Default_Key_Items;
253
254
255 -- The following API returns a particular column value from a table/view
256 -- satisfying a where condition.
257
258 FUNCTION get_table_column_value(
259 p_table_name IN VARCHAR2
260 ,p_column_name IN VARCHAR2
261 ,p_where_cond IN VARCHAR2
262 ) RETURN VARCHAR2
263 IS
264 l_sql VARCHAR2(1000);
265 l_result VARCHAR2(100);
266
267 TYPE ref_cursor IS REF CURSOR;
268 ref_cur ref_cursor;
269
270 BEGIN
271
272 IF p_table_name IS NOT null AND p_column_name IS NOT null AND p_where_cond IS NOT null THEN
273 l_sql := 'SELECT ' || p_column_name || ' FROM ' || p_table_name || ' WHERE ' || p_where_cond;
274
275 OPEN ref_cur FOR l_sql;
276 FETCH ref_cur INTO l_result;
277 CLOSE ref_cur;
278 END IF;
279
280 return l_result;
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 RAISE;
285 END get_table_column_value;
286
287
288 PROCEDURE Set_Key_Item_Value
289 (
290 p_indicator IN BSC_KPIS_B.indicator%TYPE
291 , p_dim_id IN BSC_KPI_DIM_SETS_VL.dim_set_id%TYPE
292 , p_dim_obj_sht_name IN BSC_SYS_DIM_LEVELS_VL.short_name%TYPE
293 , p_key_value IN BSC_KPI_DIM_LEVEL_PROPERTIES.default_key_value%TYPE
294 , x_return_status OUT NOCOPY VARCHAR2
295 , x_msg_count OUT NOCOPY NUMBER
296 , x_msg_data OUT NOCOPY VARCHAR2
297 )IS
298 l_indicator BSC_KPIS_B.indicator%TYPE;
299
300 CURSOR c_dim_obj IS
301 SELECT a.dim_set_id,
302 a.dim_level_index,
303 b.dim_level_id
304 FROM bsc_kpi_dim_levels_vl a,
305 bsc_sys_dim_levels_b b
306 WHERE b.short_name =a.level_shortname
307 AND a.level_shortname =p_dim_obj_sht_name
308 AND a.indicator =p_indicator;
309
310 l_params VARCHAR2(32000);
311
312 BEGIN
313 FND_MSG_PUB.INITIALIZE;
314 x_return_status := FND_API.G_RET_STS_SUCCESS;
315
316 IF(p_indicator IS NOT NULL) THEN
317 l_indicator:=p_indicator;
318
319 IF(l_indicator IS NOT NULL) THEN
320 FOR cd1 IN c_dim_obj LOOP
321 l_params := p_dim_id||',' || cd1.dim_level_id || ',' || cd1.dim_level_index || ',';
322 IF(p_key_value IS NOT NULL) THEN
323 l_params := l_params ||p_key_value || ',,';
324 ELSE
325 l_params := l_params || ',,,';
326 END IF;
327 END LOOP;
328 --now call pradeep's update API
329
330 BSC_DEFAULT_KEY_ITEM_PUB.Update_Key_Item
331 (
332 p_kpi_id => l_indicator
333 , p_params => l_params
334 , p_commit => FND_API.G_FALSE
335 , x_return_status => x_return_status
336 , x_msg_count => x_msg_count
337 , x_msg_data => x_msg_data
338 );
339 IF(x_return_status IS NOT NULL AND x_return_status<>FND_API.G_RET_STS_SUCCESS)THEN
340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
341 END IF;
342 END IF;
343 END IF;
344
345 EXCEPTION
346 WHEN FND_API.G_EXC_ERROR THEN
347 IF (x_msg_data IS NULL) THEN
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 END IF;
354 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
355 x_return_status := FND_API.G_RET_STS_ERROR;
356 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
357 IF (x_msg_data IS NULL) THEN
358 FND_MSG_PUB.Count_And_Get
359 ( p_encoded => FND_API.G_FALSE
360 , p_count => x_msg_count
361 , p_data => x_msg_data
362 );
363 END IF;
364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
365 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
366 WHEN NO_DATA_FOUND THEN
367 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
368 IF (x_msg_data IS NOT NULL) THEN
369 x_msg_data := x_msg_data||' -> BSC_DEFAULT_KEY_ITEM_PUB.Set_Key_Item_Value ';
370 ELSE
371 x_msg_data := SQLERRM||' at BSC_DEFAULT_KEY_ITEM_PUB.Set_Key_Item_Value ';
372 END IF;
373 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
374 WHEN OTHERS THEN
375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
376 IF (x_msg_data IS NOT NULL) THEN
377 x_msg_data := x_msg_data||' -> BSC_DEFAULT_KEY_ITEM_PUB.Set_Key_Item_Value ';
378 ELSE
379 x_msg_data := SQLERRM||' at BSC_DEFAULT_KEY_ITEM_PUB.Set_Key_Item_Value ';
380 END IF;
381 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
382 END Set_Key_Item_Value;
383
384
385
386 PROCEDURE Update_Key_Item(
387 p_kpi_id IN VARCHAR2
388 , p_params IN VARCHAR2
389 , p_commit IN VARCHAR2 := FND_API.G_FALSE
390 , x_return_status OUT NOCOPY VARCHAR2
391 , x_msg_count OUT NOCOPY NUMBER
392 , x_msg_data OUT NOCOPY VARCHAR2
393 ) IS
394
395 l_dim_obj_recs BSC_UTILITY.varchar_tabletype;
399 l_dim_set_id VARCHAR2(20);
396 l_dim_obj_rec VARCHAR2(200);
397 l_dim_obj_cnt NUMBER;
398 l_dim_props BSC_UTILITY.varchar_tabletype;
400 l_dim_level_id VARCHAR2(20);
401 l_dim_level_index VARCHAR2(20);
402 l_def_key_id VARCHAR2(20);
403 l_init_def_key_id VARCHAR2(20);
404 l_parent_level_index VARCHAR2(20) ;
405 l_cnt NUMBER;
406 l_user_id VARCHAR2(100);
407 l_login_id VARCHAR2(100);
408 l_count NUMBER;
409 l_change_flag NUMBER;
410 l_dim_set_ids VARCHAR2(1000);
411 l_updated NUMBER;
412 l_sql VARCHAR2(1000);
413 l_kpi_measure_id BSC_KPI_ANALYSIS_MEASURES_B.kpi_measure_id%TYPE;
414
415 TYPE ref_cursor IS REF CURSOR;
416 ref_cur ref_cursor;
417
418
419 BEGIN
420 FND_MSG_PUB.Initialize;
421 x_return_status := FND_API.G_RET_STS_SUCCESS;
422
423 IF (p_kpi_id IS NOT NULL AND p_params IS NOT NULL) THEN
424
425 BSC_DEFAULT_KEY_ITEM_PUB.Validate_key_shared_obj
426 (
427 p_kpi_id => p_kpi_id
428 , p_params => p_params
429 , x_return_status => x_return_status
430 , x_msg_count => x_msg_count
431 , x_msg_data => x_msg_data
432 );
433
434 IF(x_return_status IS NOT NULL AND x_return_status<>FND_API.G_RET_STS_SUCCESS)THEN
435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
436 END IF;
437
438 BSC_UTILITY.Parse_String (
439 p_List => p_params,
440 p_Separator => ';',
441 p_List_Data => l_dim_obj_recs,
442 p_List_number => l_dim_obj_cnt
443 );
444
445 l_user_id := fnd_global.USER_ID;
446 l_login_id := fnd_global.LOGIN_ID;
447
448 l_change_flag := 0;
449 l_updated := 0;
450
451 FOR i IN 1..l_dim_obj_cnt LOOP
452 l_dim_obj_rec := l_dim_obj_recs(i);
453
454 BSC_UTILITY.Parse_String(
455 p_List => l_dim_obj_rec,
456 p_Separator => ',',
457 p_List_Data => l_dim_props,
458 p_List_number => l_cnt
459 );
460
461 l_dim_set_id := l_dim_props(1);
462 l_dim_level_id := l_dim_props(2);
463 l_dim_level_index := l_dim_props(3);
464 l_def_key_id := l_dim_props(4);
465 l_init_def_key_id := l_dim_props(5);
466
467 IF (l_cnt = 6) THEN
468 l_parent_level_index := l_dim_props(6);
469 ELSE
470 l_parent_level_index := NULL;
471 END IF;
472
473
474 IF (l_init_def_key_id IS NULL) THEN
475 l_init_def_key_id := -1;
476 END IF;
477
478 IF (l_def_key_id IS NULL) THEN
479 l_def_key_id := -1;
480 END IF;
481
482 IF (l_dim_set_id IS NOT NULL AND l_dim_level_id IS NOT NULL AND l_dim_level_index IS NOT NULL AND l_def_key_id <> l_init_def_key_id) THEN
483
484 SELECT count(0)
485 INTO l_count
486 FROM bsc_kpi_dim_level_properties
487 WHERE indicator = p_kpi_id
488 AND dim_set_id = l_dim_set_id
489 AND dim_level_id= l_dim_level_id;
490
491 IF (l_init_def_key_id = -1) THEN
492 l_init_def_key_id := NULL;
493 END IF;
494
495 IF (l_def_key_id = -1) THEN
496 l_def_key_id := NULL;
497 END IF;
498
499
500 IF (l_count > 0) THEN
501 l_count := 0;
502 l_updated := 1;
503
504
505 IF (l_dim_set_id IS NOT NULL) THEN
506 IF (l_dim_set_ids IS NOT NULL) THEN
507 l_dim_set_ids := l_dim_set_ids || ',' || l_dim_set_id;
508 ELSE
509 l_dim_set_ids := l_dim_set_id;
510 END IF;
511 END IF;
512
513 SELECT count(0) INTO l_count
514 FROM bsc_kpi_dim_levels_b
515 WHERE indicator = p_kpi_id
516 AND dim_set_id = l_dim_set_id
517 AND dim_level_index = l_dim_level_index;
518
519 IF (l_count > 0) THEN
520
521 UPDATE bsc_kpi_dim_level_properties
522 SET default_key_value = l_def_key_id
523 WHERE indicator = p_kpi_id
524 AND dim_set_id = l_dim_set_id
525 AND dim_level_id = l_dim_level_id;
526
527 UPDATE bsc_kpi_dim_levels_b
528 SET default_key_value = l_def_key_id
529 WHERE indicator = p_kpi_id
530 AND dim_set_id = l_dim_set_id
531 AND dim_level_index = l_dim_level_index;
532
533
534 IF (LENGTH(TRIM(TRANSLATE(l_def_key_id, ' +-0123456789',' '))) IS NOT NULL AND LENGTH(TRIM(TRANSLATE(l_init_def_key_id, ' +-0123456789',' '))) IS NOT NULL) THEN
535 IF (l_change_flag = 0) THEN
536 l_change_flag := 7;
537 END IF;
538 ELSE
539 l_change_flag := 5;
540 END IF;
541
542 END IF;
543 END IF;
544 END IF;
545 END LOOP;
546
547 IF (l_updated = 1) THEN
548 IF (l_dim_set_ids IS NOT NULL) THEN
549
550 l_sql := 'SELECT DISTINCT KPI_MEASURE_ID FROM BSC_DB_DATASET_DIM_SETS_V WHERE indicator = ';
551 l_sql := l_sql || p_kpi_id || ' AND dim_set_id IN (' || l_dim_set_ids || ')';
552
553
554 IF(ref_cur%ISOPEN) THEN
555 CLOSE ref_cur;
556 END IF;
557
558 OPEN ref_cur FOR l_sql;
559 LOOP
560 FETCH ref_cur INTO l_kpi_measure_id ;
561 EXIT WHEN ref_cur%NOTFOUND;
562 IF (l_kpi_measure_id IS NOT NULL) THEN
563 BSC_KPI_COLOR_PROPERTIES_PUB.Change_Prototype_Flag
564 ( p_objective_id => p_kpi_id
565 , p_kpi_measure_id => l_kpi_measure_id
566 , p_prototype_flag => 7
567 , x_return_status => x_return_status
568 , x_msg_count => x_msg_count
569 , x_msg_data => x_msg_data
570 );
571 END IF;
572 END LOOP;
573 CLOSE ref_cur;
574
575 END IF;
576
577 UPDATE bsc_tabs_b
578 SET last_updated_by = l_user_id,
579 last_update_date = SYSDATE,
580 last_update_login = l_login_id
581 WHERE tab_id IN (
582 SELECT tab_id
583 FROM bsc_tab_indicators
584 WHERE indicator = p_kpi_id);
585
586 UPDATE bsc_kpis_b
587 SET last_updated_by = l_user_id,
588 last_update_date = SYSDATE,
589 last_update_login = l_login_id
590 WHERE indicator = p_kpi_id;
591
592 END IF;
593
594
595 IF (l_change_flag <> 0) THEN
596
597 BSC_DESIGNER_PVT.ActionFlag_change(
598 x_indicator => p_kpi_id,
599 x_newflag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color
600 );
601
602 IF (l_change_flag = 5 ) THEN
603
604 BSC_COMMON_DIM_LEVELS_PUB.Validate_List_Button(
605 p_Kpi_Id => p_kpi_id,
606 p_Dim_Level_Id => NULL,
607 x_return_status => x_return_status,
608 x_msg_count => x_msg_count,
609 x_msg_data => x_msg_data
610 );
611
612
613 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
615 END IF;
616 END IF;
617 END IF;
618 END IF;
619
620 IF (p_commit = FND_API.G_TRUE) THEN
621 COMMIT;
622 END IF;
623
624 EXCEPTION
625 WHEN FND_API.G_EXC_ERROR THEN
626 IF (x_msg_data IS NULL) THEN
627 FND_MSG_PUB.Count_And_Get
628 ( p_encoded => FND_API.G_FALSE
629 , p_count => x_msg_count
630 , p_data => x_msg_data
631 );
632 END IF;
633 x_return_status := FND_API.G_RET_STS_ERROR;
634 IF(ref_cur%ISOPEN) THEN
635 CLOSE ref_cur;
636 END IF;
637
638 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
639 IF (x_msg_data IS NULL) THEN
640 FND_MSG_PUB.Count_And_Get
641 ( p_encoded => FND_API.G_FALSE
642 , p_count => x_msg_count
643 , p_data => x_msg_data
644 );
645 END IF;
646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
647 IF(ref_cur%ISOPEN) THEN
648 CLOSE ref_cur;
649 END IF;
650
651
652 WHEN OTHERS THEN
653 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
654 IF (x_msg_data IS NOT NULL) THEN
655 x_msg_data := x_msg_data||' -> BSC_DEFAULT_KEY_ITEM_PUB.Update_Key_Item ';
656 ELSE
657 x_msg_data := SQLERRM||' at BSC_DEFAULT_KEY_ITEM_PUB.Update_Key_Item ';
658 END IF;
659 IF(ref_cur%ISOPEN) THEN
660 CLOSE ref_cur;
661 END IF;
662
663
664 END Update_Key_Item;
665
666
667 PROCEDURE Cascade_Key_Item_Changes(
668 p_kpi_id IN VARCHAR2
669 , p_params IN VARCHAR2
670 , p_commit IN VARCHAR2 := FND_API.G_FALSE
671 , x_return_status OUT NOCOPY VARCHAR2
675
672 , x_msg_count OUT NOCOPY NUMBER
673 , x_msg_data OUT NOCOPY VARCHAR2
674 ) IS
676 CURSOR c_shared_objectives IS
677 SELECT indicator
678 FROM bsc_kpis_b
679 WHERE source_indicator = p_kpi_id
680 AND share_flag = 2
681 AND prototype_flag <> 2;
682
683 BEGIN
684
685 IF (p_kpi_id IS NOT NULL AND p_params IS NOT NULL) THEN
686
687 FOR cd IN c_shared_objectives LOOP
688
689 Update_Key_Item
690 (
691 p_kpi_id => cd.indicator
692 , p_params => p_params
693 , p_commit => p_commit
694 , x_return_status => x_return_status
695 , x_msg_count => x_msg_count
696 , x_msg_data => x_msg_data
697 );
698
699 END LOOP;
700
701 END IF;
702
703 IF (p_commit = FND_API.G_TRUE) THEN
704 COMMIT;
705 END IF;
706
707 EXCEPTION
708 WHEN FND_API.G_EXC_ERROR THEN
709
710 FND_MSG_PUB.Count_And_Get
711 ( p_encoded => FND_API.G_FALSE
712 , p_count => x_msg_count
713 , p_data => x_msg_data
714 );
715 x_return_status := FND_API.G_RET_STS_ERROR;
716 RAISE;
717
718 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
719
720 FND_MSG_PUB.Count_And_Get
721 ( p_encoded => FND_API.G_FALSE
722 , p_count => x_msg_count
723 , p_data => x_msg_data
724 );
725 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
726 RAISE;
727
728 WHEN OTHERS THEN
732 x_msg_data := x_msg_data||' -> BSC_DEFAULT_KEY_ITEM_PUB.Cascade_Key_Item_Changes ';
729
730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
731 IF (x_msg_data IS NOT NULL) THEN
733 ELSE
734 x_msg_data := SQLERRM||' at BSC_DEFAULT_KEY_ITEM_PUB.Cascade_Key_Item_Changes ';
735 END IF;
736 RAISE;
737 END Cascade_Key_Item_Changes;
738
739 END BSC_DEFAULT_KEY_ITEM_PUB;
740