[Home] [Help]
PACKAGE BODY: APPS.BSC_SIMULATION_VIEW_PUB
Source
1 PACKAGE BODY BSC_SIMULATION_VIEW_PUB AS
2 /* $Header: BSCSIMPB.pls 120.3.12000000.1 2007/07/17 07:44:26 appldev noship $ */
3
4 PROCEDURE Set_Obj_Kpi_Prototype
5 (
6 p_indicator IN BSC_KPIS_B.indicator%TYPE
7 ,p_dataset_id IN BSC_SYS_DATASETS_B.dataset_id%TYPE
8 ,x_return_status OUT NOCOPY VARCHAR2
9 ,x_msg_count OUT NOCOPY NUMBER
10 ,x_msg_data OUT NOCOPY VARCHAR2
11 );
12
13
14 FUNCTION Is_More
15 ( p_list_ids IN OUT NOCOPY VARCHAR2
16 ,p_id OUT NOCOPY VARCHAR2
17 ) RETURN BOOLEAN
18 IS
19 l_pos_ids NUMBER;
20 l_pos_rel_types NUMBER;
21 l_pos_rel_columns NUMBER;
22 BEGIN
23 IF (p_list_ids IS NOT NULL) THEN
24 l_pos_ids := INSTR(p_list_ids, ',');
25 IF (l_pos_ids > 0) THEN
26 p_id := TRIM(SUBSTR(p_list_ids, 1, l_pos_ids - 1));
27 p_list_ids := TRIM(SUBSTR(p_list_ids, l_pos_ids + 1));
28 ELSE
29 p_id := TRIM(p_list_ids);
30 p_list_ids := NULL;
31 END IF;
32 RETURN TRUE;
33 ELSE
34 RETURN FALSE;
35 END IF;
36 END Is_More;
37
38
39 FUNCTION Get_Kpi_MeasureCol
40 (
41 p_DatasetId IN bsc_sys_datasets_b.dataset_id%TYPE
42 ) RETURN VARCHAR2 IS
43
44 l_measure_col bsc_sys_measures.measure_col%TYPE;
45 BEGIN
46
47 IF(p_DatasetId IS NOT NULL) THEN
48 SELECT measure_col
49 INTO l_measure_col
50 FROM bsc_sys_datasets_b b
51 , bsc_sys_measures a
52 WHERE b.measure_id1 =a.measure_id
53 AND b.dataset_id =p_DatasetId;
54 END IF;
55 RETURN l_measure_col;
56
57 END Get_Kpi_MeasureCol;
58
59
60 FUNCTION Get_Formula_Base_Columns
61 (
62 p_indicator IN bsc_kpis_b.indicator%TYPE
63 ,p_Dataset_Id IN bsc_sys_datasets_b.dataset_id%TYPE
64 ,p_Meas_Col IN bsc_sys_measures.measure_col%TYPE
65 ) RETURN VARCHAR2 IS
66
67 l_measure_col bsc_sys_measures.measure_col%TYPE;
68 l_short_name bsc_kpis_b.short_name%TYPE;
69 l_kpi_short_name bis_indicators.short_name%TYPE;
70 l_formula VARCHAR2(32000);
71 l_count NUMBER;
72
73 l_Ak_Null_Tbl BSC_SIMULATION_VIEW_PUB.Bsc_Ak_Region_Items_Tbl_Type ;
74 l_Ak_NotNull_Tbl BSC_SIMULATION_VIEW_PUB.Bsc_Ak_Region_Items_Tbl_Type ;
75
76 CURSOR c_meas_null IS
77 SELECT v.attribute_code,
78 v.attribute2,
79 v.attribute3,
80 b.operation|| '('|| b.measure_col || ')' as measure_col
81 FROM ak_region_items_vl v
82 ,bsc_sys_measures b
83 WHERE v.attribute2 =b.short_name
84 AND v.region_code =l_short_name
85 AND v.attribute1 =BSC_SIMULATION_VIEW_PUB.c_MEASURE_NO_TARGET
86 AND v.attribute3 IS NULL
87 ORDER BY v.display_sequence;
88
89
90 CURSOR c_meas_notnull IS
91 SELECT v.attribute_code,
92 v.attribute2,
93 v.attribute3,
94 b.measure_col
95 FROM ak_region_items_vl v
96 ,bsc_sys_measures b
97 WHERE v.attribute2 =b.short_name
98 AND v.region_code =l_short_name
99 AND v.attribute1 =BSC_SIMULATION_VIEW_PUB.c_MEASURE_NO_TARGET
100 AND v.attribute3 IS NOT NULL
101 ORDER BY v.display_sequence;
102
103 BEGIN
104
105 SELECT short_name
106 INTO l_short_name
107 FROM bsc_kpis_b
108 WHERE indicator =p_indicator;
109
110 IF(l_short_name IS NOT NULL)THEN
111 l_count :=0;
112
113 FOR cd IN c_meas_null LOOP
114 l_Ak_Null_Tbl(l_count).Attribute_Code := cd.attribute_code ;
115 l_Ak_Null_Tbl(l_count).shortName := cd.attribute2 ;
116 l_Ak_Null_Tbl(l_count).Formula := cd.attribute3 ;
117 l_Ak_Null_Tbl(l_count).Measure_Col := cd.measure_col ;
118 l_Ak_Null_Tbl(l_count).Acutual_Formula:= cd.attribute3 ;
119 l_count := l_count + 1;
120 END LOOP;
121
122 l_count :=0;
123
124 FOR cd IN c_meas_notnull LOOP
125 l_Ak_NotNull_Tbl(l_count).Attribute_Code := cd.attribute_code ;
126 l_Ak_NotNull_Tbl(l_count).shortName := cd.attribute2 ;
127 l_Ak_NotNull_Tbl(l_count).Formula := cd.attribute3 ;
128 l_Ak_NotNull_Tbl(l_count).Measure_Col := cd.measure_col ;
129 l_Ak_NotNull_Tbl(l_count).Acutual_Formula:= cd.attribute3 ;
130 l_count := l_count + 1;
131 END LOOP;
132
133 END IF;
134
135 --/////////////////////Test case //////////////////////////////
136
137 /* l_Ak_Null_Tbl(0).Attribute_Code := 'BIS_COLUMN_9' ;
138 l_Ak_Null_Tbl(0).shortName := 'SHORT_NAME' ;
139 l_Ak_Null_Tbl(0).Formula := NULL;
140 l_Ak_Null_Tbl(0).Measure_Col := 'SUM(M1)' ;
141 l_Ak_Null_Tbl(0).Acutual_Formula:= NULL ;
142
143 l_Ak_Null_Tbl(1).Attribute_Code := 'BIS_COLUMN_13';
144 l_Ak_Null_Tbl(1).shortName := 'SHORT_NAME1' ;
145 l_Ak_Null_Tbl(1).Formula := NULL;
146 l_Ak_Null_Tbl(1).Measure_Col := 'AVG(M2)';
147 l_Ak_Null_Tbl(1).Acutual_Formula:= NULL;
148
149
150 l_Ak_NotNull_Tbl(0).Attribute_Code := 'BIS_COLUMN_16';
151 l_Ak_NotNull_Tbl(0).shortName := 'SHORT_NAME2' ;
152 l_Ak_NotNull_Tbl(0).Formula := 'BIS_COLUMN_9+2*BIS_COLUMN_13';
153 l_Ak_NotNull_Tbl(0).Measure_Col := 'BIS_COLUMN_9+2*BIS_COLUMN_13' ;
154 l_Ak_NotNull_Tbl(0).Acutual_Formula:= 'BIS_COLUMN_9+2*BIS_COLUMN_13';
155
156
157 l_Ak_NotNull_Tbl(1).Attribute_Code := 'BIS_COLUMN_19';
158 l_Ak_NotNull_Tbl(1).shortName := 'SHORT_NAME3' ;
159 l_Ak_NotNull_Tbl(1).Formula := 'BIS_COLUMN_16+POWER(BIS_COLUMN_13,BIS_COLUMN_9)';
160 l_Ak_NotNull_Tbl(1).Measure_Col := 'BIS_COLUMN_16+POWER(BIS_COLUMN_13,BIS_COLUMN_9)';
161 l_Ak_NotNull_Tbl(1).Acutual_Formula:= 'BIS_COLUMN_16+POWER(BIS_COLUMN_13,BIS_COLUMN_9)';
162
163
164
165 l_Ak_NotNull_Tbl(2).Attribute_Code := 'BIS_COLUMN_21';
166 l_Ak_NotNull_Tbl(2).shortName := 'SHORT_NAME4' ;
167 l_Ak_NotNull_Tbl(2).Formula := 'BIS_COLUMN_19+POWER(BIS_COLUMN_19,BIS_COLUMN_16)';
168 l_Ak_NotNull_Tbl(2).Measure_Col := 'BIS_COLUMN_19+POWER(BIS_COLUMN_19,BIS_COLUMN_16)';
169 l_Ak_NotNull_Tbl(2).Acutual_Formula:= 'BIS_COLUMN_19+POWER(BIS_COLUMN_19,BIS_COLUMN_16)'; */
170
171 --/////////////////////////////Test case Ended/////////////////////////////////////////
172
173
174
175
176 FOR i IN 0..l_Ak_Null_Tbl.COUNT - 1 LOOP
177 FOR j IN 0 ..l_Ak_NotNull_Tbl.COUNT - 1 LOOP
178 IF(INSTR(l_Ak_NotNull_Tbl(j).Measure_Col,l_Ak_Null_Tbl(i).Attribute_Code)>0) THEN
179 l_Ak_NotNull_Tbl(j).Measure_Col := REPLACE(l_Ak_NotNull_Tbl(j).Measure_Col,l_Ak_Null_Tbl(i).Attribute_Code,l_Ak_Null_Tbl(i).Measure_Col);
180 END IF;
181 END LOOP;
182 END LOOP;
183
184 FOR i IN 0 ..l_Ak_NotNull_Tbl.COUNT - 1 LOOP
185 FOR j IN 0..l_Ak_NotNull_Tbl.COUNT - 1 LOOP
186 IF(l_Ak_NotNull_Tbl.EXISTS(j) AND INSTR(l_Ak_NotNull_Tbl(j).Measure_Col,l_Ak_NotNull_Tbl(i).Attribute_Code)>0) THEN
187 l_Ak_NotNull_Tbl(j).Measure_Col := REPLACE(l_Ak_NotNull_Tbl(j).Measure_Col,l_Ak_NotNull_Tbl(i).Attribute_Code,l_Ak_NotNull_Tbl(i).Measure_Col);
188 END IF;
189 END LOOP;
190 END LOOP;
191
192
193
194 FOR i IN l_Ak_NotNull_Tbl.COUNT - 1..0 LOOP
195 FOR j IN i..0 LOOP
196 IF(l_Ak_NotNull_Tbl.EXISTS(j) AND INSTR(l_Ak_NotNull_Tbl(j).Measure_Col,l_Ak_NotNull_Tbl(i).Attribute_Code)>0) THEN
197 l_Ak_NotNull_Tbl(j).Measure_Col := REPLACE(l_Ak_NotNull_Tbl(j).Measure_Col,l_Ak_NotNull_Tbl(i).Attribute_Code,l_Ak_NotNull_Tbl(i).Measure_Col);
198 END IF;
199 END LOOP;
200 END LOOP;
201
202
203 /*FOR i IN 0..l_Ak_NotNull_Tbl.COUNT - 1 LOOP
204 --DBMS_OUTPUT.PUT_LINE('l_Ak_NotNull_Tbl('|| i||').Attribute_Code-->'||l_Ak_NotNull_Tbl(i).Attribute_Code);
205 --DBMS_OUTPUT.PUT_LINE('l_Ak_NotNull_Tbl('|| i||').shortName-->'||l_Ak_NotNull_Tbl(i).shortName);
206 --DBMS_OUTPUT.PUT_LINE('l_Ak_NotNull_Tbl('|| i||').Formula-->'||l_Ak_NotNull_Tbl(i).Formula);
207 --DBMS_OUTPUT.PUT_LINE('l_Ak_NotNull_Tbl('|| i||').Measure_Col-->'||l_Ak_NotNull_Tbl(i).Measure_Col);
208 END LOOP; */
209
210 SELECT short_name
211 INTO l_short_name
212 FROM bis_indicators
213 WHERE dataset_id = p_Dataset_Id;
214
215 --l_short_name := 'SHORT_NAME4';
216
217 FOR i IN 0..l_Ak_NotNull_Tbl.COUNT - 1 LOOP
218 IF(l_Ak_NotNull_Tbl(i).shortName=l_short_name) THEN
219 l_formula:= l_Ak_NotNull_Tbl(i).Measure_Col;
220 EXIT;
221 END IF;
222 END LOOP;
223
224 RETURN l_formula;
225
226
227 END Get_Formula_Base_Columns;
228
229
230
231 PROCEDURE Create_Sim_Tree_bg (
232 p_obj_id IN NUMBER
233 ,p_file_name IN VARCHAR2
234 ,p_description IN VARCHAR2
235 ,p_width IN NUMBER
236 ,p_height IN NUMBER
237 ,p_mime_type IN VARCHAR2
238 ,x_image_id OUT NOCOPY NUMBER
239 ,x_return_status OUT NOCOPY VARCHAR2
240 ,x_msg_count OUT NOCOPY NUMBER
241 ,x_msg_data OUT NOCOPY VARCHAR2
242 ) IS
243 l_next_image_id NUMBER;
244 l_str VARCHAR2(100);
245 BEGIN
246
247 SAVEPOINT CreateSimTreebg;
248 FND_MSG_PUB.INITIALIZE;
249 x_return_status := FND_API.G_RET_STS_SUCCESS;
250
251 SELECT BSC_SYS_IMAGE_ID_S.NEXTVAL
252 INTO l_next_image_id
253 FROM dual;
254
255 x_image_id := l_next_image_id;
256
257 BEGIN
258 BSC_SYS_IMAGES_PKG.INSERT_ROW
259 (
260 X_IMAGE_ID => l_next_image_id
261 ,X_FILE_NAME => p_file_name
262 ,X_DESCRIPTION => p_description
263 ,X_WIDTH => p_width
264 ,X_HEIGHT => p_height
265 ,X_MIME_TYPE => p_mime_type
266 ,X_CREATED_BY => fnd_global.user_id
267 ,X_LAST_UPDATED_BY => fnd_global.user_id
268 ,X_LAST_UPDATE_LOGIN=> fnd_global.login_id
269 );
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
274 x_msg_data := 'Insertion to BSC_SYS_IMAGES_PKG failed' || SQLERRM;
275 RAISE;
276 END;
277
278 BSC_SYS_IMAGES_MAP_PKG.INSERT_ROW
279 (
280 X_ROWID => l_str
281 ,X_SOURCE_TYPE => BSC_SIMULATION_VIEW_PUB.c_INDICATOR_TYPE
282 ,X_SOURCE_CODE => p_obj_id
283 ,X_TYPE => BSC_SIMULATION_VIEW_PUB.c_TYPE
284 ,X_IMAGE_ID => l_next_image_id
285 ,X_CREATION_DATE => SYSDATE
286 ,X_CREATED_BY => fnd_global.user_id
287 ,X_LAST_UPDATE_DATE => SYSDATE
288 ,X_LAST_UPDATED_BY => fnd_global.user_id
289 ,X_LAST_UPDATE_LOGIN => fnd_global.login_id
290 );
291 EXCEPTION
292 WHEN others THEN
293 ROLLBACK TO CreateSimTreebg;
294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
295 x_msg_data := SQLERRM;
296 RAISE;
297 END Create_Sim_Tree_bg;
298
299
300 /*********************************************************
301 Procedure : Add_Or_Update_Tab_View_Bg
302 Description : This proceudres update or add a new canvas image to the simulation tree.
303 We will continue to use the tables BSC_SYS_IMAGES and
304 BSC_SYS_IMAGE_MAPS_TL table for storing the simulation tree background
305 images.
306 Source_Type column in BSC_SYS_IMAGE_MAPS_TL will be set to 2 for indicators
307
308 SOURCE_TYPE --> 1 [ For tabs ]
309 --> 2 [ For indicators ]
310
311 /*********************************************************/
312
313 PROCEDURE Add_Or_Update_Sim_Tree_Bg (
314 p_obj_id IN NUMBER
315 ,p_image_id IN NUMBER
316 ,p_file_name IN VARCHAR2
317 ,p_description IN VARCHAR2
318 ,p_width IN NUMBER
319 ,p_height IN NUMBER
320 ,p_mime_type IN VARCHAR2
321 ,x_image_id OUT NOCOPY NUMBER
322 ,x_return_status OUT NOCOPY VARCHAR2
323 ,x_msg_count OUT NOCOPY NUMBER
324 ,x_msg_data OUT NOCOPY VARCHAR2
325 ) IS
326 l_count NUMBER;
327 l_next_image_id BSC_SYS_IMAGES.image_id%TYPE;
328 BEGIN
329 SAVEPOINT AddOrUpdateSimTreeBg;
330 FND_MSG_PUB.INITIALIZE;
331 x_return_status := FND_API.G_RET_STS_SUCCESS;
332
333
334 SELECT COUNT(0)
335 INTO l_count
336 FROM bsc_sys_images bsi,
337 bsc_sys_images_map_vl bsim
338 WHERE bsim.source_type =BSC_SIMULATION_VIEW_PUB.c_INDICATOR_TYPE
339 AND bsim.source_code = p_obj_id
340 AND bsim.type = BSC_SIMULATION_VIEW_PUB.c_TYPE
341 AND bsim.image_id = p_image_id
342 AND bsim.image_id = bsi.image_id;
343
344
345 IF (l_count > 0) THEN
346 --check if the image is owned by current NLS session
347
348 SELECT COUNT(0)
349 INTO l_count
350 FROM bsc_sys_images_map_TL
351 WHERE source_type =BSC_SIMULATION_VIEW_PUB.c_INDICATOR_TYPE
352 AND source_code = p_obj_id
353 AND type = BSC_SIMULATION_VIEW_PUB.c_TYPE
354 AND image_id = p_image_id
355 AND source_lang = USERENV('LANG');
356
357 IF (l_count > 0) THEN
358 --image owned by this NLS session, just simply update the same image
359 x_image_id := p_image_id;
360
361 BEGIN
362 UPDATE BSC_SYS_IMAGES
363 SET FILE_NAME = p_file_name,
364 DESCRIPTION = p_description,
365 WIDTH = p_width,
366 HEIGHT = p_height,
367 MIME_TYPE = p_mime_type,
368 LAST_UPDATE_DATE = SYSDATE,
369 LAST_UPDATED_BY = fnd_global.user_id,
370 LAST_UPDATE_LOGIN = fnd_global.login_id,
371 FILE_BODY = EMPTY_BLOB()
372 WHERE IMAGE_ID = p_image_id;
373 EXCEPTION
374 WHEN OTHERS THEN
375 ROLLBACK TO AddOrUpdateSimTreeBg;
376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
377 x_msg_data := 'Update to BSC_SYS_IMAGES failed' || SQLERRM;
378 RETURN;
379 END;
380
381 BSC_SYS_IMAGES_MAP_PKG.UPDATE_ROW
382 (
383 X_SOURCE_TYPE => BSC_SIMULATION_VIEW_PUB.c_INDICATOR_TYPE
384 ,X_SOURCE_CODE => p_obj_id
385 ,X_TYPE => BSC_SIMULATION_VIEW_PUB.c_TYPE
386 ,X_IMAGE_ID => p_image_id
387 ,X_CREATION_DATE => SYSDATE
388 ,X_CREATED_BY => fnd_global.user_id
389 ,X_LAST_UPDATE_DATE => SYSDATE
390 ,X_LAST_UPDATED_BY => fnd_global.user_id
391 ,X_LAST_UPDATE_LOGIN => fnd_global.login_id
392 );
393
394 ELSE
395 --image not owned by this NLS session, need to create a new image and update the image map
396 SELECT BSC_SYS_IMAGE_ID_S.NEXTVAL
397 INTO l_next_image_id
398 FROM dual;
399
400 x_image_id := l_next_image_id;
401
402 BEGIN
403 BSC_SYS_IMAGES_PKG.INSERT_ROW
404 (
405 X_IMAGE_ID => l_next_image_id
406 ,X_FILE_NAME => p_file_name
407 ,X_DESCRIPTION => p_description
408 ,X_WIDTH => p_width
409 ,X_HEIGHT => p_height
410 ,X_MIME_TYPE => p_mime_type
411 ,X_CREATED_BY => fnd_global.user_id
412 ,X_LAST_UPDATED_BY => fnd_global.user_id
413 ,X_LAST_UPDATE_LOGIN => fnd_global.login_id
414 );
415
416 EXCEPTION
417 WHEN OTHERS THEN
418 ROLLBACK TO AddOrUpdateSimTreeBg;
419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
420 x_msg_data := 'Insertion to BSC_SYS_IMAGES_PKG failed' || SQLERRM;
421 RETURN;
422 END;
423
424 BSC_SYS_IMAGES_MAP_PKG.UPDATE_ROW
425 (
426 X_SOURCE_TYPE => BSC_SIMULATION_VIEW_PUB.c_INDICATOR_TYPE
427 ,X_SOURCE_CODE => p_obj_id
428 ,X_TYPE => BSC_SIMULATION_VIEW_PUB.c_TYPE
429 ,X_IMAGE_ID => p_image_id
430 ,X_CREATION_DATE => SYSDATE
431 ,X_CREATED_BY => fnd_global.user_id
432 ,X_LAST_UPDATE_DATE => SYSDATE
433 ,X_LAST_UPDATED_BY => fnd_global.user_id
434 ,X_LAST_UPDATE_LOGIN => fnd_global.login_id
435 );
436 END IF;
437 ELSE
438 --create a new image for this Simulation Tree Objective
439 Create_Sim_Tree_bg (
440 p_obj_id => p_obj_id
441 ,p_file_name => p_file_name
442 ,p_description => p_description
443 ,p_width => p_width
444 ,p_height => p_height
445 ,p_mime_type => p_mime_type
446 ,x_image_id => x_image_id
447 ,x_return_status => x_return_status
448 ,x_msg_count => x_msg_count
449 ,x_msg_data => x_msg_data
450 );
451 END IF;
452
453 EXCEPTION
454 WHEN OTHERS THEN
455 ROLLBACK TO AddOrUpdateSimTreeBg;
456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
457 x_msg_data := SQLERRM;
458
459 END Add_Or_Update_Sim_Tree_Bg;
460
461
462
463
464
465 PROCEDURE Get_Objective_Details
466 (
467 p_Region_Code IN AK_REGIONS.REGION_CODE%TYPE
468 ,x_indicator OUT NOCOPY VARCHAR2
469 ,x_ind_group_id OUT NOCOPY VARCHAR2
470 ,x_tab_id OUT NOCOPY VARCHAR2
471 ,x_prototype_flag OUT NOCOPY VARCHAR2
472 ,x_ind_name OUT NOCOPY VARCHAR2
473 ,x_ytd_enabled OUT NOCOPY VARCHAR2
474 ,x_return_status OUT NOCOPY VARCHAR2
475 ,x_msg_count OUT NOCOPY NUMBER
476 ,x_msg_data OUT NOCOPY VARCHAR2
477 )IS
478 l_count NUMBER := 0;
479 BEGIN
480 --DBMS_OUTPUT.PUT_LINE('entering -->'||l_count);
481 FND_MSG_PUB.INITIALIZE;
482 x_return_status := FND_API.G_RET_STS_SUCCESS;
483 --DBMS_OUTPUT.PUT_LINE('entering 1 -->'||l_count);
484
485 x_indicator := NULL;
486 x_ind_group_id := NULL;
487 x_tab_id := NULL;
488 x_prototype_flag := NULL;
489 x_ind_name:=NULL;
490 x_ytd_enabled:=NULL;
491
492 IF(p_Region_Code IS NOT NULL)THEN
493
494 SELECT COUNT(0)
495 INTO l_count
496 FROM bsc_kpis_b
497 WHERE SHORT_NAME = p_Region_Code;
498
499
500 --DBMS_OUTPUT.PUT_LINE('l_count -->'||l_count);
501
502 IF(l_count<>0)THEN
503
504 SELECT a.INDICATOR
505 ,a.ind_group_id
506 ,b.tab_id
507 ,a.prototype_flag
508 ,a.name
509 ,c.attribute21
510 INTO x_indicator
511 ,x_ind_group_id
512 ,x_tab_id
513 ,x_prototype_flag
514 ,x_ind_name
515 ,x_ytd_enabled
516 FROM bsc_kpis_vl a,
517 bsc_tab_indicators b,
518 bis_ak_region_extension c
519 WHERE a.short_name = c.region_code(+)
520 AND a.short_name = p_Region_Code
521 AND a.INDICATOR =b.INDICATOR(+)
522 AND a.prototype_flag<>2
523 AND a.share_flag<>2;
524
525 END IF;
526 END IF;
527
528 EXCEPTION
529 WHEN FND_API.G_EXC_ERROR THEN
530 --DBMS_OUTPUT.PUT_LINE('FND_API.G_EXC_ERROR -->');
531 FND_MSG_PUB.Count_And_Get
532 ( p_encoded => FND_API.G_FALSE
533 , p_count => x_msg_count
534 , p_data => x_msg_data
535 );
536 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
537 x_return_status := FND_API.G_RET_STS_ERROR;
538 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
539 --DBMS_OUTPUT.PUT_LINE('FND_API.G_EXC_UNEXPECTED_ERROR -->');
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 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
546 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
547 WHEN NO_DATA_FOUND THEN
548 --DBMS_OUTPUT.PUT_LINE('FND_API.NO_DATA_FOUND -->');
549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
550 IF (x_msg_data IS NOT NULL) THEN
551 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Get_Objective_Details ';
552 ELSE
553 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Get_Objective_Details ';
554 END IF;
555 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
556 WHEN OTHERS THEN
557 --DBMS_OUTPUT.PUT_LINE('FND_API.OTHERS -->');
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_SIMULATION_VIEW_PUB.Get_Objective_Details ';
561 ELSE
562 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Get_Objective_Details ';
563 END IF;
564 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
565
566 END Get_Objective_Details;
567
568
569 PROCEDURE add_or_update_measure
570 (
571 p_tab_id IN NUMBER
572 ,p_tab_view_id IN NUMBER
573 ,p_text_object_id IN NUMBER
574 ,p_text_flag IN NUMBER
575 ,p_font_size IN NUMBER
576 ,p_font_style IN NUMBER
577 ,p_font_color IN NUMBER
578 ,p_text_left IN NUMBER
579 ,p_text_top IN NUMBER
580 ,p_text_width IN NUMBER
581 ,p_text_height IN NUMBER
582 ,p_slider_object_id IN NUMBER
583 ,p_slider_flag IN NUMBER
584 ,p_slider_left IN NUMBER
585 ,p_slider_top IN NUMBER
586 ,p_slider_width IN NUMBER
587 ,p_slider_height IN NUMBER
588 ,p_actual_object_id IN NUMBER
589 ,p_actual_flag IN NUMBER
590 ,p_actual_left IN NUMBER
591 ,p_actual_top IN NUMBER
592 ,p_actual_width IN NUMBER
593 ,p_actual_height IN NUMBER
594 ,p_change_object_id IN NUMBER
595 ,p_change_flag IN NUMBER
596 ,p_change_left IN NUMBER
597 ,p_change_top IN NUMBER
598 ,p_change_width IN NUMBER
599 ,p_change_height IN NUMBER
600 ,p_color_object_id IN NUMBER
601 ,p_color_flag IN NUMBER
602 ,p_color_left IN NUMBER
603 ,p_color_top IN NUMBER
604 ,p_color_width IN NUMBER
605 ,p_color_height IN NUMBER
606 ,p_indicator_id IN NUMBER
607 ,p_function_id IN NUMBER
608 ,p_Node_Id IN NUMBER
609 ,p_Node_Name IN VARCHAR2
610 ,p_Node_Help IN VARCHAR2
611 ,p_SimulateFlag IN NUMBER
612 ,p_Format_id IN NUMBER
613 ,p_Node_Color_flag IN NUMBER
614 ,p_Node_Color_method IN NUMBER
615 ,p_Navigates_to_trend IN NUMBER
616 ,p_Top_position IN NUMBER
617 ,p_Left_position IN NUMBER
618 ,p_Width IN NUMBER
619 ,p_Height IN NUMBER
620 ,p_Autoscale_flag IN NUMBER
621 ,p_Y_axis_title IN VARCHAR2
622 ,p_Node_Attr_Code IN VARCHAR2
623 ,p_Node_Short_Name IN VARCHAR2
624 ,p_default_node IN NUMBER
625 ,p_color_thresholds IN VARCHAR2
626 ,p_color_by_total IN NUMBER
627 ,x_return_status OUT NOCOPY VARCHAR2
628 ,x_msg_count OUT NOCOPY NUMBER
629 ,x_msg_data OUT NOCOPY VARCHAR2
630 ) IS
631 l_count NUMBER;
632 l_dataset_id BSC_SYS_DATASETS_B.dataset_id%TYPE;
633 BEGIN
634 FND_MSG_PUB.INITIALIZE;
635 x_return_status := FND_API.G_RET_STS_SUCCESS;
636
637 l_dataset_id := p_indicator_id;
638 IF(p_SimulateFlag=BSC_SIMULATION_VIEW_PUB.c_NON_SIM_NODE AND p_Node_Id =BSC_SIMULATION_VIEW_PUB.c_DEFAULT_SIM_NODE_ID)THEN
639 l_dataset_id := BSC_BIS_KPI_CRUD_PUB.Get_Dataset_Id(p_Node_Short_Name);
640 END IF;
641
642 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_measure
643 (
644 p_tab_id => p_tab_id
645 ,p_tab_view_id => p_tab_view_id
646 ,p_text_object_id => p_text_object_id
647 ,p_text_flag => p_text_flag
648 ,p_font_size => p_font_size
649 ,p_font_style => p_font_style
650 ,p_font_color => p_font_color
651 ,p_text_left => p_text_left
652 ,p_text_top => p_text_top
653 ,p_text_width => p_text_width
654 ,p_text_height => p_text_height
655 ,p_slider_object_id => p_slider_object_id
656 ,p_slider_flag => p_slider_flag
657 ,p_slider_left => p_slider_left
658 ,p_slider_top => p_slider_top
659 ,p_slider_width => p_slider_width
660 ,p_slider_height => p_slider_height
661 ,p_actual_object_id => p_actual_object_id
662 ,p_actual_flag => p_actual_flag
663 ,p_actual_left => p_actual_left
664 ,p_actual_top => p_actual_top
665 ,p_actual_width => p_actual_width
666 ,p_actual_height => p_actual_height
667 ,p_change_object_id => p_change_object_id
668 ,p_change_flag => p_change_flag
669 ,p_change_left => p_change_left
670 ,p_change_top => p_change_top
671 ,p_change_width => p_change_width
672 ,p_change_height => p_change_height
673 ,p_indicator_id => l_dataset_id
674 ,p_function_id => p_function_id
675 ,x_return_status => x_return_status
676 ,x_msg_count => x_msg_count
677 ,x_msg_data => x_msg_data
678 );
679
680 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS)THEN
681 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
682 END IF;
683
684 --save the color into BSC_TAB_VIEW_LABELS table
685
686 BSC_CUSTOM_VIEW_UI_WRAPPER.add_or_update_tab_view_label
687 (
688 p_tab_id => p_tab_id
689 , p_tab_view_id => p_tab_view_id
690 , p_object_id => p_color_object_id
691 , p_object_type => BSC_SIMULATION_VIEW_PUB.C_TYPE_MEASURE_COLOR
692 , p_label_text => BSC_SIMULATION_VIEW_PUB.C_MEASURE_COLOR
693 , p_text_flag => p_color_flag
694 , p_font_color => p_font_color
695 , p_font_size => p_font_size
696 , p_font_style => p_font_style
697 , p_left => p_color_left
698 , p_top => p_color_top
699 , p_width => p_color_width
700 , p_height => p_color_height
701 , p_note_text => NULL
702 , p_link_id => l_dataset_id
703 , p_function_id => p_function_id
704 , x_return_status => x_return_status
705 , x_msg_count => x_msg_count
706 , x_msg_data => x_msg_data
707 );
708
709 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS)THEN
710 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
711 END IF;
712
713 --Now save the node properties
714
715 BSC_SIMULATION_VIEW_PUB.add_or_update_sim_node_props
716 (
717 p_indicator => p_tab_view_id
718 ,p_Node_Id => l_dataset_id
719 ,p_Node_Name => p_Node_Name
720 ,p_Node_Help => p_Node_Help
721 ,p_SimulateFlag => p_SimulateFlag
722 ,p_Format_id => p_Format_id
723 ,p_Color_flag => p_Node_Color_flag
724 ,p_Color_method => p_Node_Color_method
725 ,p_Navigates_to_trend => p_Navigates_to_trend
726 ,p_Top_position => p_Top_position
727 ,p_Left_position => p_Left_position
728 ,p_Width => p_Width
729 ,p_Height => p_Height
730 ,p_Autoscale_flag => p_Autoscale_flag
731 ,p_Y_axis_title => p_Y_axis_title
732 ,p_Node_Attr_Code => p_Node_Attr_Code
733 ,p_Node_Short_Name => p_Node_Short_Name
734 ,x_return_status => x_return_status
735 ,x_msg_count => x_msg_count
736 ,x_msg_data => x_msg_data
737 );
738
739 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS)THEN
740 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
741 END IF;
742
743 --Change the default node and set the objective to
744 -- the color change
745 BSC_SIMULATION_VIEW_PUB.set_default_node
746 (
747 p_indicator => p_tab_view_id
748 ,p_default_node => p_default_node
749 ,p_dataset_id => l_dataset_id
750 ,x_return_status => x_return_status
751 ,x_msg_count => x_msg_count
752 ,x_msg_data => x_msg_data
753 );
754 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS)THEN
755 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
756 END IF;
757
758 BSC_SIMULATION_VIEW_PUB.Save_Color_Ranges
759 (
760 p_indicator => p_tab_view_id
761 ,p_dataset_id => l_dataset_id
762 ,p_color_ranges => p_color_thresholds
763 ,x_return_status => x_return_status
764 ,x_msg_count => x_msg_count
765 ,x_msg_data => x_msg_data
766 );
767 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS)THEN
768 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
769 END IF;
770
771 --We need to set the color_flag in bsc_kpi_measure_props
772 --
773
774 IF(p_SimulateFlag=BSC_SIMULATION_VIEW_PUB.c_NON_SIM_NODE)THEN
775
776 BSC_SIMULATION_VIEW_PVT.Set_Kpi_Color_Method
777 (
778 p_indicator => p_tab_view_id
779 ,p_dataset_id => l_dataset_id
780 ,p_color_method => p_Node_Color_method
781 ,x_return_status => x_return_status
782 ,x_msg_count => x_msg_count
783 ,x_msg_data => x_msg_data
784
785 );
786 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS)THEN
787 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
788 END IF;
789 END IF;
790
791 BSC_SIMULATION_VIEW_PVT.Set_Kpi_Color_Flag
792 (
793 p_indicator => p_tab_view_id
794 ,p_dataset_id => l_dataset_id
795 ,p_color_flag => p_Node_Color_flag
796 ,p_color_by_total => p_color_by_total
797 ,x_return_status => x_return_status
798 ,x_msg_count => x_msg_count
799 ,x_msg_data => x_msg_data
800
801 );
802 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS)THEN
803 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
804 END IF;
805
806 BSC_SIMULATION_VIEW_PVT.Set_Ak_Format_Id
807 (
808 p_indicator => p_tab_view_id
809 ,p_dataset_Id => l_dataset_id
810 ,p_format_Id => p_Format_id
811 ,x_return_status => x_return_status
812 ,x_msg_count => x_msg_count
813 ,x_msg_data => x_msg_data
814 );
815
816 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS)THEN
817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
818 END IF;
819
820 EXCEPTION
821 WHEN FND_API.G_EXC_ERROR THEN
822 FND_MSG_PUB.Count_And_Get
823 ( p_encoded => FND_API.G_FALSE
824 , p_count => x_msg_count
825 , p_data => x_msg_data
826 );
827 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
828 x_return_status := FND_API.G_RET_STS_ERROR;
829 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
830 FND_MSG_PUB.Count_And_Get
831 ( p_encoded => FND_API.G_FALSE
832 , p_count => x_msg_count
833 , p_data => x_msg_data
834 );
835 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
836 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
837 WHEN NO_DATA_FOUND THEN
838 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
839 IF (x_msg_data IS NOT NULL) THEN
840 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.add_or_update_measure ';
841 ELSE
842 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.add_or_update_measure ';
843 END IF;
844 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
845 WHEN OTHERS THEN
846 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
847 IF (x_msg_data IS NOT NULL) THEN
848 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.add_or_update_measure ';
849 ELSE
850 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.add_or_update_measure ';
851 END IF;
852 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
853
854 END add_or_update_measure;
855
856
857 PROCEDURE add_or_update_sim_node_props
858 (
859 p_indicator IN NUMBER
860 ,p_Node_Id IN NUMBER
861 ,p_Node_Name IN VARCHAR2
862 ,p_Node_Help IN VARCHAR2
863 ,p_SimulateFlag IN NUMBER
864 ,p_Format_id IN NUMBER
865 ,p_Color_flag IN NUMBER
866 ,p_Color_method IN NUMBER
867 ,p_Navigates_to_trend IN NUMBER
868 ,p_Top_position IN NUMBER
869 ,p_Left_position IN NUMBER
870 ,p_Width IN NUMBER
871 ,p_Height IN NUMBER
872 ,p_Autoscale_flag IN NUMBER
873 ,p_Y_axis_title IN VARCHAR2
874 ,p_Node_Attr_Code IN VARCHAR2
875 ,p_Node_Short_Name IN VARCHAR2
876 ,x_return_status OUT NOCOPY VARCHAR2
877 ,x_msg_count OUT NOCOPY NUMBER
878 ,x_msg_data OUT NOCOPY VARCHAR2
879 ) IS
880 l_str VARCHAR2(100);
881 l_count NUMBER;
882
883 BEGIN
884 SAVEPOINT addorupdatesimnodeprops;
885 fnd_msg_pub.initialize;
886 x_return_status := FND_API.G_RET_STS_SUCCESS;
887
888 --Node id will be the same as the dataset id
889 --There cannot be two nodes whose dataset_ids are same.
890
891 SELECT COUNT(1)
892 INTO l_count
893 FROM bsc_kpi_tree_nodes_vl
894 WHERE indicator = p_indicator
895 AND node_id =p_Node_Id;
896
897 IF (l_count = 0) THEN
898
899 BSC_KPI_TREE_NODES_PKG.INSERT_ROW
900 (
901 X_ROWID => l_str
902 ,X_INDICATOR => p_indicator
903 ,X_NODE_ID => p_Node_Id
904 ,X_SIMULATE_FLAG => p_SimulateFlag
905 ,X_FORMAT_ID => p_Format_id
906 ,X_COLOR_FLAG => p_Color_flag
907 ,X_COLOR_METHOD => p_Color_method
908 ,X_NAVIGATES_TO_TREND => p_Navigates_to_trend
909 ,X_TOP_POSITION => p_Top_position
910 ,X_LEFT_POSITION => p_Left_position
911 ,X_WIDTH => p_Width
912 ,X_HEIGHT => p_Height
913 ,X_NAME => p_Node_Name
914 ,X_HELP => p_Node_Name --Right now node help will be same
915 ,X_Y_AXIS_TITLE => p_Y_axis_title
916 );
917 ELSE
918 BSC_KPI_TREE_NODES_PKG.UPDATE_ROW
919 (
920 X_INDICATOR => p_indicator
921 ,X_NODE_ID => p_Node_Id
922 ,X_SIMULATE_FLAG => p_SimulateFlag
923 ,X_FORMAT_ID => p_Format_id
924 ,X_COLOR_FLAG => p_Color_flag
925 ,X_COLOR_METHOD => p_Color_method
926 ,X_NAVIGATES_TO_TREND => p_Navigates_to_trend
927 ,X_TOP_POSITION => p_Top_position
928 ,X_LEFT_POSITION => p_Left_position
929 ,X_WIDTH => p_Width
930 ,X_HEIGHT => p_Height
931 ,X_NAME => p_Node_Name
932 ,X_HELP => p_Node_Name
933 ,X_Y_AXIS_TITLE => p_Y_axis_title
934 );
935
936 END IF;
937
938 EXCEPTION
939 WHEN OTHERS THEN
940 ROLLBACK TO addorupdatesimnodeprops;
941 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
942 x_msg_data := SQLERRM;
943 END add_or_update_sim_node_props;
944
945
946 PROCEDURE set_default_node
947 (
948 p_indicator IN NUMBER
949 ,p_default_node IN NUMBER
950 ,p_dataset_id IN NUMBER
951 ,x_return_status OUT NOCOPY VARCHAR2
952 ,x_msg_count OUT NOCOPY NUMBER
953 ,x_msg_data OUT NOCOPY VARCHAR2
954 )IS
955 l_prev_default_node NUMBER;
956 l_count NUMBER;
957 BEGIN
958 BSC_SIMULATION_VIEW_PVT.set_default_node
959 (
960 p_indicator => p_indicator
961 ,p_default_node => p_default_node
962 ,p_dataset_id => p_dataset_id
963 ,x_return_status => x_return_status
964 ,x_msg_count => x_msg_count
965 ,x_msg_data => x_msg_data
966 ) ;
967
968 IF(x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
969 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
970 END IF;
971 EXCEPTION
972 WHEN FND_API.G_EXC_ERROR THEN
973
974 FND_MSG_PUB.Count_And_Get
975 ( p_encoded => FND_API.G_FALSE
976 , p_count => x_msg_count
977 , p_data => x_msg_data
978 );
979 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
980 x_return_status := FND_API.G_RET_STS_ERROR;
981 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
982
983 FND_MSG_PUB.Count_And_Get
984 ( p_encoded => FND_API.G_FALSE
985 , p_count => x_msg_count
986 , p_data => x_msg_data
987 );
988 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
989 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
990 WHEN NO_DATA_FOUND THEN
991
992 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
993 IF (x_msg_data IS NOT NULL) THEN
994 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.set_default_node ';
995 ELSE
996 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.set_default_node ';
997 END IF;
998 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
999 WHEN OTHERS THEN
1000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1001 IF (x_msg_data IS NOT NULL) THEN
1002 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.set_default_node ';
1003 ELSE
1004 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.set_default_node ';
1005 END IF;
1006 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1007 END set_default_node;
1008
1009
1010 /*************************************************
1011 c_type_measure CONSTANT NUMBER := 10;
1012 c_type_measure_actual CONSTANT NUMBER := 11;
1013 c_type_measure_change CONSTANT NUMBER := 12;
1014 c_type_measure_slider CONSTANT NUMBER := 14;
1015 c_type_measure_color CONSTANT NUMBER := 16;
1016 /*************************************************/
1017
1018 PROCEDURE remove_simulation_view_items
1019 (
1020 p_tab_id IN NUMBER
1021 ,p_obj_Id IN NUMBER
1022 ,p_labels IN VARCHAR2
1023 ,x_return_status OUT NOCOPY VARCHAR2
1024 ,x_msg_count OUT NOCOPY NUMBER
1025 ,x_msg_data OUT NOCOPY VARCHAR2
1026 )IS
1027
1028 TYPE index_table_type IS TABLE OF NUMBER INDEX BY binary_integer;
1029 l_lables_table index_table_type;
1030
1031 l_id NUMBER;
1032 l_labels VARCHAR2(8000);
1033 l_links_table BSC_UTILITY.varchar_tabletype;
1034 l_index NUMBER;
1035 l_measure_type BIS_INDICATORS.measure_type%TYPE;
1036 l_range_id NUMBER;
1037 l_dataset_id BIS_INDICATORS.dataset_id%TYPE;
1038 l_Anal_Opt_Rec BSC_ANALYSIS_OPTION_PUB.Bsc_Option_Rec_Type;
1039 l_default_node bsc_kpi_properties.property_value%TYPE;
1040 l_count NUMBER;
1041
1042
1043 CURSOR label_cur IS
1044 SELECT label_id,label_type,link_id
1045 FROM bsc_tab_view_labels_vl
1046 WHERE tab_id = p_tab_id
1047 AND tab_view_id = p_obj_Id;
1048
1049 CURSOR measure_cur IS
1050 SELECT measure_type
1051 FROM bis_indicators
1052 WHERE dataset_id = l_dataset_id;
1053
1054
1055 CURSOR c_default IS
1056 SELECT property_value
1057 FROM bsc_kpi_properties
1058 WHERE indicator = p_obj_Id
1059 AND property_code =BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID;
1060
1061 l_label_cur label_cur%ROWTYPE;
1062
1063 BEGIN
1064
1065 --SET INDICATOR TO PROTOTYPE MODE IF ANY OF THE BASE MEASURES ARE REMOVED.
1066 --Before deleting label get the label type and check if it is labeltype is with --in the range of (10,11,12,14,13)
1067 -- if it is then get the link id for it and delete the corresponding entry from
1068 -- BSC_KPI_TREE_NODES_VL
1069 SAVEPOINT removesimviewitems;
1070 FND_MSG_PUB.INITIALIZE;
1071 x_return_status := FND_API.G_RET_STS_SUCCESS;
1072
1073 l_labels := p_labels;
1074
1075 WHILE (Is_More(p_list_ids => l_labels, p_id => l_id))
1076 LOOP
1077 l_lables_table(l_id) := 1;
1078 END LOOP;
1079
1080 l_index := 0;
1081
1082 FOR l_label_cur IN label_cur LOOP
1083 IF (l_lables_table.exists(l_label_cur.label_id) = FALSE) THEN
1084 IF(l_label_cur.label_type =BSC_CUSTOM_VIEW_UI_WRAPPER.c_type_measure)THEN
1085 l_links_table(l_index):=l_label_cur.link_id;
1086 l_index := l_index +1;
1087 END IF;
1088
1089 BSC_TAB_VIEW_LABELS_PKG.DELETE_ROW
1090 (
1091 X_TAB_ID => p_tab_id
1092 ,X_TAB_VIEW_ID => p_obj_Id
1093 ,X_LABEL_ID => l_label_cur.label_id
1094 );
1095 END IF;
1096 END LOOP;
1097
1098 -- now delete the entries from BSC_KPI_TREE_NODES_B/TL tables
1099
1100 IF(l_index<>0)THEN
1101 FOR cd IN 0..l_index-1 LOOP
1102
1103 BSC_KPI_TREE_NODES_PKG.DELETE_ROW
1104 (
1105 X_INDICATOR => p_obj_Id
1106 ,X_NODE_ID => l_links_table(cd)
1107 );
1108
1109 l_dataset_id := l_links_table(cd);
1110 FOR cd IN measure_cur LOOP
1111
1112 IF(cd.measure_type IS NULL)THEN
1113 BSC_DESIGNER_PVT.ActionFlag_Change(p_obj_Id, BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure);
1114 END IF;
1115 END LOOP;
1116 END LOOP;
1117 END IF;
1118
1119 --Delete the color ranges for the deleted measures from the objective
1120 l_Anal_Opt_Rec.Bsc_Kpi_Id := p_obj_Id;
1121
1122 BSC_ANALYSIS_OPTION_PUB.Cascade_Deletion_Color_Props
1123 (
1124 p_commit => FND_API.G_FALSE
1125 ,p_Anal_Opt_Rec => l_Anal_Opt_Rec
1126 ,x_return_status => x_return_status
1127 ,x_msg_count => x_msg_count
1128 ,x_msg_data => x_msg_data
1129 ) ;
1130 IF (x_return_status IS NOT NULL AND x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1132 END IF;
1133
1134
1135 --Now we will check if the default node was deleted or not.
1136 --if yes then we will set the default node back to -1
1137
1138 FOR cd IN c_default LOOP
1139 l_default_node := cd.property_value;
1140 SELECT COUNT(0)
1141 INTO l_count
1142 FROM bsc_kpi_tree_nodes_b
1143 WHERE indicator =p_obj_Id;
1144
1145 IF(l_count=0) THEN
1146 BSC_SIMULATION_VIEW_PUB.set_default_node
1147 (
1148 p_indicator => p_obj_Id
1149 ,p_default_node => 1
1150 ,p_dataset_id => BSC_SIMULATION_VIEW_PUB.c_DEFAULT_DATASET_ID
1151 ,x_return_status => x_return_status
1152 ,x_msg_count => x_msg_count
1153 ,x_msg_data => x_msg_data
1154 );
1155
1156 IF (x_return_status IS NOT NULL AND x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1158 END IF;
1159 END IF;
1160 END LOOP;
1161
1162 EXCEPTION
1163 WHEN FND_API.G_EXC_ERROR THEN
1164 ROLLBACK TO removesimviewitems;
1165 FND_MSG_PUB.Count_And_Get
1166 ( p_encoded => FND_API.G_FALSE
1167 , p_count => x_msg_count
1168 , p_data => x_msg_data
1169 );
1170 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1171 x_return_status := FND_API.G_RET_STS_ERROR;
1172 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1173 ROLLBACK TO removesimviewitems;
1174 FND_MSG_PUB.Count_And_Get
1175 ( p_encoded => FND_API.G_FALSE
1176 , p_count => x_msg_count
1177 , p_data => x_msg_data
1178 );
1179 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1180 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1181 WHEN NO_DATA_FOUND THEN
1182 ROLLBACK TO removesimviewitems;
1183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1184 IF (x_msg_data IS NOT NULL) THEN
1185 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.remove_simulation_view_items ';
1186 ELSE
1187 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.remove_simulation_view_items ';
1188 END IF;
1189 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1190 WHEN OTHERS THEN
1191 ROLLBACK TO removesimviewitems;
1192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1193 IF (x_msg_data IS NOT NULL) THEN
1194 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.remove_simulation_view_items ';
1195 ELSE
1196 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.remove_simulation_view_items ';
1197 END IF;
1198 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1199 END remove_simulation_view_items;
1200
1201
1202 PROCEDURE Duplicate_kpi_metadata
1203 (
1204 p_source_kpi IN NUMBER
1205 ,p_target_kpi IN NUMBER
1206 ,x_return_status OUT NOCOPY VARCHAR2
1207 ,x_msg_count OUT NOCOPY NUMBER
1208 ,x_msg_data OUT NOCOPY VARCHAR2
1209 )IS
1210 BEGIN
1211 SAVEPOINT Duplicatekpimetadata;
1212 FND_MSG_PUB.INITIALIZE;
1213 x_return_status := FND_API.G_RET_STS_SUCCESS;
1214
1215 --We need to copy the source indicator data from the following tables
1216 --BSC_SYS_IMAGES
1217 --BSC_SYS_IMAGES_MAP_TL
1218 --BSC_KPI_TREE_NODES
1219 --BSC_TAB_VIEW_LABELS_B/TL
1220
1221 --First validate if both the indicators are valid or not
1222 BSC_BIS_LOCKS_PUB.LOCK_KPI
1223 ( p_Kpi_Id => p_source_kpi
1224 , p_time_stamp => NULL
1225 , p_Full_Lock_Flag => FND_API.G_FALSE
1226 , x_return_status => x_return_status
1227 , x_msg_count => x_msg_count
1228 , x_msg_data => x_msg_data
1229 );
1230 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1231 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1232 END IF;
1233
1234 BSC_SIMULATION_VIEW_PVT.Duplicate_sim_metadata
1235 (
1236 p_source_kpi => p_source_kpi
1237 ,p_target_kpi => p_target_kpi
1238 ,x_return_status => x_return_status
1239 ,x_msg_count => x_msg_count
1240 ,x_msg_data => x_msg_data
1241 );
1242
1243 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1245 END IF;
1246
1247 EXCEPTION
1248 WHEN FND_API.G_EXC_ERROR THEN
1249 ROLLBACK TO Duplicatekpimetadata;
1250 FND_MSG_PUB.Count_And_Get
1251 ( p_encoded => FND_API.G_FALSE
1252 , p_count => x_msg_count
1253 , p_data => x_msg_data
1254 );
1255 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1256 x_return_status := FND_API.G_RET_STS_ERROR;
1257 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1258 ROLLBACK TO Duplicatekpimetadata;
1259 FND_MSG_PUB.Count_And_Get
1260 ( p_encoded => FND_API.G_FALSE
1261 , p_count => x_msg_count
1262 , p_data => x_msg_data
1263 );
1264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1265 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1266 WHEN NO_DATA_FOUND THEN
1267 ROLLBACK TO Duplicatekpimetadata;
1268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1269 IF (x_msg_data IS NOT NULL) THEN
1270 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Duplicate_kpi_metadata ';
1271 ELSE
1272 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Duplicate_kpi_metadata ';
1273 END IF;
1274 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1275 WHEN OTHERS THEN
1276 ROLLBACK TO Duplicatekpimetadata;
1277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1278 IF (x_msg_data IS NOT NULL) THEN
1279 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Duplicate_kpi_metadata ';
1280 ELSE
1281 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Duplicate_kpi_metadata ';
1282 END IF;
1283 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1284
1285 END Duplicate_kpi_metadata;
1286
1287
1288 PROCEDURE Validate_Name_In_Tab
1289 (
1290 p_name IN VARCHAR2
1291 ,p_tabId IN NUMBER
1292 ,x_return_status OUT NOCOPY VARCHAR2
1293 ,x_msg_count OUT NOCOPY NUMBER
1294 ,x_msg_data OUT NOCOPY VARCHAR2
1295 )IS
1296 l_same_name NUMBER;
1297 BEGIN
1298
1299 FND_MSG_PUB.INITIALIZE;
1300 x_return_status := FND_API.G_RET_STS_SUCCESS;
1301
1302
1303 SELECT COUNT(0)
1304 INTO l_same_name
1305 FROM bsc_tab_indicators
1306 WHERE tab_id = p_tabId
1307 AND indicator IN (SELECT indicator
1308 FROM BSC_KPIS_TL
1309 WHERE UPPER(name) = UPPER(p_name));
1310 -- if there are kpis in this tab which have the same name it throws an error.
1311 IF l_same_name <> 0 then
1312 FND_MESSAGE.SET_NAME('BSC','BSC_B_NO_SAMEKPI_TAB');
1313 FND_MESSAGE.SET_TOKEN('Indicator name: ', p_name);
1314 FND_MSG_PUB.ADD;
1315 RAISE FND_API.G_EXC_ERROR;
1316 END IF;
1317
1318
1319 EXCEPTION
1320 WHEN FND_API.G_EXC_ERROR THEN
1321
1322 FND_MSG_PUB.Count_And_Get
1323 ( p_encoded => FND_API.G_FALSE
1324 , p_count => x_msg_count
1325 , p_data => x_msg_data
1326 );
1327 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1328 x_return_status := FND_API.G_RET_STS_ERROR;
1329 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1330
1331 FND_MSG_PUB.Count_And_Get
1332 ( p_encoded => FND_API.G_FALSE
1333 , p_count => x_msg_count
1334 , p_data => x_msg_data
1335 );
1336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1337 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1338 WHEN NO_DATA_FOUND THEN
1339
1340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1341 IF (x_msg_data IS NOT NULL) THEN
1342 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Validate_Name_In_Tab ';
1343 ELSE
1344 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Validate_Name_In_Tab ';
1345 END IF;
1346 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1347 WHEN OTHERS THEN
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_SIMULATION_VIEW_PUB.Validate_Name_In_Tab ';
1351 ELSE
1352 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Validate_Name_In_Tab ';
1353 END IF;
1354 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1355
1356
1357 END Validate_Name_In_Tab;
1358
1359
1360 PROCEDURE Add_Or_Update_YTD
1361 (
1362 p_indicator IN NUMBER
1363 ,p_YTD IN VARCHAR2
1364 ,p_prev_YTD IN VARCHAR2
1365 ,x_return_status OUT NOCOPY VARCHAR2
1366 ,x_msg_count OUT NOCOPY NUMBER
1367 ,x_msg_data OUT NOCOPY VARCHAR2
1368 ) IS
1369 BEGIN
1370 FND_MSG_PUB.INITIALIZE;
1371 x_return_status := FND_API.G_RET_STS_SUCCESS;
1372
1373 BSC_SIMULATION_VIEW_PVT.Add_Or_Update_YTD
1374 (
1375 p_indicator => p_indicator
1376 ,p_YTD => p_YTD
1377 ,p_prev_YTD => p_prev_YTD
1378 ,x_return_status => x_return_status
1379 ,x_msg_count => x_msg_count
1380 ,x_msg_data => x_msg_data
1381 );
1382 IF(x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
1383 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1384 END IF;
1385
1386 EXCEPTION
1387 WHEN FND_API.G_EXC_ERROR THEN
1388
1389 FND_MSG_PUB.Count_And_Get
1390 ( p_encoded => FND_API.G_FALSE
1391 , p_count => x_msg_count
1392 , p_data => x_msg_data
1393 );
1394 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1395 x_return_status := FND_API.G_RET_STS_ERROR;
1396 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1397
1398 FND_MSG_PUB.Count_And_Get
1399 ( p_encoded => FND_API.G_FALSE
1400 , p_count => x_msg_count
1401 , p_data => x_msg_data
1402 );
1403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1404 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1405 WHEN NO_DATA_FOUND THEN
1406
1407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1408 IF (x_msg_data IS NOT NULL) THEN
1409 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Add_Or_Update_YTD ';
1410 ELSE
1411 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Add_Or_Update_YTD ';
1412 END IF;
1413 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1414 WHEN OTHERS THEN
1415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1416 IF (x_msg_data IS NOT NULL) THEN
1417 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Add_Or_Update_YTD ';
1418 ELSE
1419 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Add_Or_Update_YTD ';
1420 END IF;
1421 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1422
1423 END Add_Or_Update_YTD;
1424
1425
1426 PROCEDURE Save_Color_Ranges
1427 (
1428 p_indicator IN NUMBER
1429 ,p_dataset_id IN NUMBER
1430 ,p_color_ranges IN VARCHAR2
1431 ,x_return_status OUT NOCOPY VARCHAR2
1432 ,x_msg_count OUT NOCOPY NUMBER
1433 ,x_msg_data OUT NOCOPY VARCHAR2
1434 )IS
1435 l_kpi_measure_id bsc_kpi_analysis_measures_b.kpi_measure_id%TYPE;
1436 l_count NUMBER;
1437 l_Bsc_Kpi_Color_Range_Rec BSC_COLOR_RANGES_PUB.Bsc_Color_Range_Rec;
1438 l_Bsc_Kpi_Color_Range_New_Rec BSC_COLOR_RANGES_PUB.Bsc_Color_Range_Rec;
1439 l_color_ranges_changed VARCHAR2(2) := FND_API.G_FALSE;
1440
1441 CURSOR c_color_ranges IS
1442 SELECT a.color_range_sequence,a.low,a.high,a.color_id
1443 FROM bsc_color_ranges a,
1444 bsc_color_type_props b
1445 WHERE a.color_range_id =b.color_range_id
1446 AND b.INDICATOR=p_indicator
1447 AND b.kpi_measure_id =l_kpi_measure_id
1448 ORDER BY a.color_range_sequence;
1449
1450 BEGIN
1451 FND_MSG_PUB.INITIALIZE;
1452 x_return_status := FND_API.G_RET_STS_SUCCESS;
1453
1454 l_kpi_measure_id := BSC_SIMULATION_VIEW_PUB.Get_Kpi_Measure_Id
1455 (
1456 p_indicator => p_indicator
1457 , p_dataset_id => p_dataset_id
1458 );
1459
1460 l_count :=0;
1461 FOR cd IN c_color_ranges LOOP
1462 l_Bsc_Kpi_Color_Range_Rec(l_count).color_range_sequence := cd.color_range_sequence;
1463 l_Bsc_Kpi_Color_Range_Rec(l_count).low := cd.low;
1464 l_Bsc_Kpi_Color_Range_Rec(l_count).high := cd.high;
1465 l_Bsc_Kpi_Color_Range_Rec(l_count).color_id := cd.color_id;
1466 l_count := l_count + 1;
1467 END LOOP;
1468
1469 BSC_COLOR_RANGES_PUB.Save_Color_Prop_Ranges
1470 (
1471 p_commit => FND_API.G_FALSE
1472 , p_objective_id => p_indicator
1473 , p_kpi_measure_id => l_kpi_measure_id
1474 , p_color_type => BSC_SIMULATION_VIEW_PUB.c_PERCENT_OF_TARGET
1475 , p_threshold_color => p_color_ranges
1476 , p_cascade_shared => TRUE
1477 , p_time_stamp => NULL
1478 , x_return_status => x_return_status
1479 , x_msg_count => x_msg_count
1480 , x_msg_data => x_msg_data
1481 );
1482
1483 IF(x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
1484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1485 END IF;
1486
1487 l_count :=0;
1488 FOR cd IN c_color_ranges LOOP
1489 l_Bsc_Kpi_Color_Range_New_Rec(l_count).color_range_sequence := cd.color_range_sequence;
1490 l_Bsc_Kpi_Color_Range_New_Rec(l_count).low := cd.low;
1491 l_Bsc_Kpi_Color_Range_New_Rec(l_count).high := cd.high;
1492 l_Bsc_Kpi_Color_Range_New_Rec(l_count).color_id := cd.color_id;
1493 l_count := l_count + 1;
1494 END LOOP;
1495
1496 -- Now we need to compare both the old and new color ranges..
1497 -- if they differ then we need to
1498 IF((l_Bsc_Kpi_Color_Range_Rec IS NOT NULL) AND (l_Bsc_Kpi_Color_Range_New_Rec IS NOT NULL)
1499 AND l_Bsc_Kpi_Color_Range_Rec.COUNT <> l_Bsc_Kpi_Color_Range_New_Rec.COUNT)THEN
1500
1501
1502 Set_Obj_Kpi_Prototype
1503 (
1504 p_indicator => p_indicator
1505 ,p_dataset_id => p_dataset_id
1506 ,x_return_status => x_return_status
1507 ,x_msg_count => x_msg_count
1508 ,x_msg_data => x_msg_data
1509 );
1510 IF(x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
1511 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1512 END IF;
1513
1514
1515 ELSE
1516 FOR l_count IN 0..l_Bsc_Kpi_Color_Range_Rec.COUNT-1 LOOP
1517 IF((l_Bsc_Kpi_Color_Range_Rec(l_count).low <> l_Bsc_Kpi_Color_Range_New_Rec(l_count).low)
1518 OR (l_Bsc_Kpi_Color_Range_Rec(l_count).high <> l_Bsc_Kpi_Color_Range_New_Rec(l_count).high)) THEN
1519 l_color_ranges_changed := FND_API.G_TRUE;
1520
1521 EXIT;
1522 END IF;
1523 END LOOP;
1524
1525 IF(l_color_ranges_changed =FND_API.G_TRUE)THEN
1526
1527 Set_Obj_Kpi_Prototype
1528 (
1529 p_indicator => p_indicator
1530 ,p_dataset_id => p_dataset_id
1531 ,x_return_status => x_return_status
1532 ,x_msg_count => x_msg_count
1533 ,x_msg_data => x_msg_data
1534 );
1535 IF(x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
1536 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1537 END IF;
1538 END IF;
1539 END IF;
1540
1541
1542 EXCEPTION
1543 WHEN FND_API.G_EXC_ERROR THEN
1544
1545 FND_MSG_PUB.Count_And_Get
1546 ( p_encoded => FND_API.G_FALSE
1547 , p_count => x_msg_count
1548 , p_data => x_msg_data
1549 );
1550 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1551 x_return_status := FND_API.G_RET_STS_ERROR;
1552 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1553
1554 FND_MSG_PUB.Count_And_Get
1555 ( p_encoded => FND_API.G_FALSE
1556 , p_count => x_msg_count
1557 , p_data => x_msg_data
1558 );
1559 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1560 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1561 WHEN NO_DATA_FOUND THEN
1562
1563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1564 IF (x_msg_data IS NOT NULL) THEN
1565 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.set_color_ranges ';
1566 ELSE
1567 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.set_color_ranges ';
1568 END IF;
1569 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1570 WHEN OTHERS THEN
1571 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1572 IF (x_msg_data IS NOT NULL) THEN
1573 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.set_color_ranges ';
1574 ELSE
1575 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.set_color_ranges ';
1576 END IF;
1577 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1578
1579 END Save_Color_Ranges;
1580
1581
1582
1583 FUNCTION Get_Kpi_Measure_Id
1584 (
1585 p_indicator IN NUMBER
1586 ,p_dataset_id IN NUMBER
1587 ) RETURN NUMBER
1588 IS
1589 l_kpi_measure_id bsc_kpi_analysis_measures_b.kpi_measure_id%TYPE;
1590 BEGIN
1591
1592 SELECT kpi_measure_id
1593 INTO l_kpi_measure_id
1594 FROM bsc_kpi_analysis_measures_b
1595 WHERE indicator = p_indicator
1596 AND dataset_id = p_dataset_id;
1597
1598 RETURN l_kpi_measure_id;
1599 END Get_Kpi_Measure_Id;
1600
1601
1602 PROCEDURE copy_sim_metadata
1603 (
1604 p_source_kpi IN NUMBER
1605 ,p_target_kpi IN NUMBER
1606 ,x_return_status OUT NOCOPY VARCHAR2
1607 ,x_msg_count OUT NOCOPY NUMBER
1608 ,x_msg_data OUT NOCOPY VARCHAR2
1609 )IS
1610 BEGIN
1611
1612 BSC_SIMULATION_VIEW_PVT.copy_sim_metadata
1613 (
1614 p_source_kpi => p_source_kpi
1615 ,p_target_kpi => p_target_kpi
1616 ,x_return_status => x_return_status
1617 ,x_msg_count => x_msg_count
1618 ,x_msg_data => x_msg_data
1619 );
1620
1621 IF(x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
1622 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1623 END IF;
1624
1625 EXCEPTION
1626 WHEN FND_API.G_EXC_ERROR THEN
1627
1628 FND_MSG_PUB.Count_And_Get
1629 ( p_encoded => FND_API.G_FALSE
1630 , p_count => x_msg_count
1631 , p_data => x_msg_data
1632 );
1633 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1634 x_return_status := FND_API.G_RET_STS_ERROR;
1635 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1636
1637 FND_MSG_PUB.Count_And_Get
1638 ( p_encoded => FND_API.G_FALSE
1639 , p_count => x_msg_count
1640 , p_data => x_msg_data
1641 );
1642 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1643 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1644 WHEN NO_DATA_FOUND THEN
1645
1646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1647 IF (x_msg_data IS NOT NULL) THEN
1648 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.copy_sim_metadata ';
1649 ELSE
1650 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.copy_sim_metadata ';
1651 END IF;
1652 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1653 WHEN OTHERS THEN
1654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1655 IF (x_msg_data IS NOT NULL) THEN
1656 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.copy_sim_metadata ';
1657 ELSE
1658 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.copy_sim_metadata ';
1659 END IF;
1660 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1661
1662 END copy_sim_metadata;
1663
1664 /***************************************************
1665 Set_Sim_Key_Values : API is used to set the Key Items for
1666 Simulation Tree objectives.
1667 Creator : ashankar 29-03-07
1668 /***************************************************/
1669
1670 PROCEDURE Set_Sim_Key_Values
1671 (
1672 p_ind_Sht_Name IN BSC_KPIS_B.short_name%TYPE
1673 ,p_indicator IN BSC_KPIS_B.indicator%TYPE
1674 ,x_return_status OUT NOCOPY VARCHAR2
1675 ,x_msg_count OUT NOCOPY NUMBER
1676 ,x_msg_data OUT NOCOPY VARCHAR2
1677 ) IS
1678
1679 CURSOR c_shared_obj IS
1680 SELECT indicator
1681 ,short_name
1682 FROM bsc_kpis_b
1683 WHERE prototype_flag<>2
1684 AND share_flag =2
1685 AND config_type =7
1686 AND source_indicator = p_indicator;
1687
1688 BEGIN
1689
1690 BSC_SIMULATION_VIEW_PVT.Set_Sim_Key_Values
1691 (
1692 p_ind_Sht_Name => p_ind_Sht_Name
1693 ,p_indicator => p_indicator
1694 ,x_return_status => x_return_status
1695 ,x_msg_count => x_msg_count
1696 ,x_msg_data => x_msg_data
1697 );
1698
1699 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
1700 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1701 END IF;
1702
1703 --/////////////Handle Shared Objectives /////////////
1704
1705 --//First refresh the shared objectives from the master.
1706 BSC_SIMULATION_VIEW_PVT.Handle_Shared_Objectives
1707 (
1708 p_indicator => p_indicator
1709 ,x_return_status => x_return_status
1710 ,x_msg_count => x_msg_count
1711 ,x_msg_data => x_msg_data
1712 );
1713 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
1714 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1715 END IF;
1716
1717 FOR cd IN c_shared_obj LOOP
1718 BSC_SIMULATION_VIEW_PVT.Set_Sim_Key_Values
1719 (
1720 p_ind_Sht_Name => cd.short_name
1721 ,p_indicator => cd.indicator
1722 ,x_return_status => x_return_status
1723 ,x_msg_count => x_msg_count
1724 ,x_msg_data => x_msg_data
1725 );
1726
1727 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
1728 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1729 END IF;
1730 END LOOP;
1731
1732 EXCEPTION
1733 WHEN FND_API.G_EXC_ERROR THEN
1734
1735 FND_MSG_PUB.Count_And_Get
1736 ( p_encoded => FND_API.G_FALSE
1737 , p_count => x_msg_count
1738 , p_data => x_msg_data
1739 );
1740 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1741 x_return_status := FND_API.G_RET_STS_ERROR;
1742 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1743
1744 FND_MSG_PUB.Count_And_Get
1745 ( p_encoded => FND_API.G_FALSE
1746 , p_count => x_msg_count
1747 , p_data => x_msg_data
1748 );
1749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1750 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1751 WHEN NO_DATA_FOUND THEN
1752
1753 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1754 IF (x_msg_data IS NOT NULL) THEN
1755 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Set_Sim_Key_Values ';
1756 ELSE
1757 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Set_Sim_Key_Values ';
1758 END IF;
1759 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1760 WHEN OTHERS THEN
1761 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1762 IF (x_msg_data IS NOT NULL) THEN
1763 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Set_Sim_Key_Values ';
1764 ELSE
1765 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Set_Sim_Key_Values ';
1766 END IF;
1767 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1768
1769 END Set_Sim_Key_Values;
1770
1771 PROCEDURE Set_Obj_Kpi_Prototype
1772 (
1773 p_indicator IN BSC_KPIS_B.indicator%TYPE
1774 ,p_dataset_id IN BSC_SYS_DATASETS_B.dataset_id%TYPE
1775 ,x_return_status OUT NOCOPY VARCHAR2
1776 ,x_msg_count OUT NOCOPY NUMBER
1777 ,x_msg_data OUT NOCOPY VARCHAR2
1778
1779 )IS
1780
1781 CURSOR c_def_node IS
1782 SELECT property_value
1783 FROM bsc_kpi_properties
1784 WHERE indicator =p_indicator
1785 AND property_code =BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID;
1786
1787 l_count NUMBER;
1788 l_kpi_measure_id bsc_kpi_analysis_measures_b.kpi_measure_id%TYPE;
1789
1790 BEGIN
1791 FND_MSG_PUB.INITIALIZE;
1792 x_return_status := FND_API.G_RET_STS_SUCCESS;
1793
1794 l_kpi_measure_id := BSC_SIMULATION_VIEW_PUB.Get_Kpi_Measure_Id
1795 (
1796 p_indicator => p_indicator
1797 , p_dataset_id => p_dataset_id
1798 );
1799
1800 BSC_KPI_COLOR_PROPERTIES_PUB.Kpi_Prototype_Flag_Change
1801 (
1802 p_objective_id => p_indicator
1803 , p_kpi_measure_id => l_kpi_measure_id
1804 , p_prototype_flag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color
1805 , x_return_status => x_return_status
1806 , x_msg_count => x_msg_count
1807 , x_msg_data => x_msg_data
1808 );
1809
1810 IF(x_return_status IS NOT NULL AND x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
1811 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1812 END IF;
1813
1814 FOR cd IN c_def_node LOOP
1815 IF(cd.property_value =p_dataset_id) THEN
1816 BSC_KPI_COLOR_PROPERTIES_PUB.Obj_Prototype_Flag_Change
1817 (
1818 p_objective_id => p_indicator
1819 , p_prototype_flag => BSC_DESIGNER_PVT.G_ActionFlag.GAA_Color
1820 , x_return_status => x_return_status
1821 , x_msg_count => x_msg_count
1822 , x_msg_data => x_msg_data
1823 );
1824 IF(x_return_status <>FND_API.G_RET_STS_SUCCESS) THEN
1825 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1826 END IF;
1827 END IF;
1828 END LOOP;
1829 EXCEPTION
1830 WHEN FND_API.G_EXC_ERROR THEN
1831
1832 FND_MSG_PUB.Count_And_Get
1833 ( p_encoded => FND_API.G_FALSE
1834 , p_count => x_msg_count
1835 , p_data => x_msg_data
1836 );
1837 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1838 x_return_status := FND_API.G_RET_STS_ERROR;
1839 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1840
1841 FND_MSG_PUB.Count_And_Get
1842 ( p_encoded => FND_API.G_FALSE
1843 , p_count => x_msg_count
1844 , p_data => x_msg_data
1845 );
1846 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1847 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1848 WHEN NO_DATA_FOUND THEN
1849
1850 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1851 IF (x_msg_data IS NOT NULL) THEN
1852 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Set_Obj_Kpi_Prototype ';
1853 ELSE
1854 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Set_Obj_Kpi_Prototype ';
1855 END IF;
1856 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1857 WHEN OTHERS THEN
1858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1859 IF (x_msg_data IS NOT NULL) THEN
1860 x_msg_data := x_msg_data||' -> BSC_SIMULATION_VIEW_PUB.Set_Obj_Kpi_Prototype ';
1861 ELSE
1862 x_msg_data := SQLERRM||' at BSC_SIMULATION_VIEW_PUB.Set_Obj_Kpi_Prototype ';
1863 END IF;
1864 ----DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1865
1866 END Set_Obj_Kpi_Prototype;
1867
1868 END BSC_SIMULATION_VIEW_PUB;