[Home] [Help]
PACKAGE BODY: APPS.BSC_KPI_MEASURE_PROPS_PUB
Source
1 PACKAGE BODY BSC_KPI_MEASURE_PROPS_PUB AS
2 /* $Header: BSCPKMPB.pls 120.3 2008/04/24 06:34:34 bijain noship $ */
3
4
5 FUNCTION get_shared_obj_kpi_measure (
6 p_objective_id IN NUMBER
7 , p_kpi_measure_id IN NUMBER
8 , p_shared_objective_id IN NUMBER
9 ) RETURN NUMBER
10 IS
11 CURSOR c_analysis_option_comb(p_indicator NUMBER, p_kpi_measure_id NUMBER) IS
12 SELECT analysis_option0, analysis_option1, analysis_option2, series_id
13 FROM bsc_kpi_analysis_measures_b
14 WHERE indicator = p_indicator
15 AND kpi_measure_id = p_kpi_measure_id;
16 c_analysis_option_comb_rec c_analysis_option_comb%ROWTYPE;
17
18 CURSOR c_kpi_measure_id(p_indicator NUMBER, p_analysis_option0 NUMBER,
19 p_analysis_option1 NUMBER, p_analysis_option2 NUMBER, p_series_id NUMBER) IS
20 SELECT kpi_measure_id
21 FROM bsc_kpi_analysis_measures_b
22 WHERE indicator = p_indicator
23 AND analysis_option0 = p_analysis_option0
24 AND analysis_option1 = p_analysis_option1
25 AND analysis_option2 = p_analysis_option2
26 AND series_id = p_series_id;
27
28 l_kpi_measure_id NUMBER;
29
30 BEGIN
31 l_kpi_measure_id := NULL;
32
33 IF p_objective_id IS NOT NULL AND p_kpi_measure_id IS NOT NULL AND p_shared_objective_id IS NOT NULL THEN
34
35 IF c_analysis_option_comb%ISOPEN THEN
36 CLOSE c_analysis_option_comb;
37 END IF;
38 OPEN c_analysis_option_comb(p_objective_id, p_kpi_measure_id);
39 FETCH c_analysis_option_comb INTO c_analysis_option_comb_rec;
40 IF c_analysis_option_comb%NOTFOUND THEN
41 RAISE FND_API.G_EXC_ERROR;
42 END IF;
43 CLOSE c_analysis_option_comb;
44
45 IF c_kpi_measure_id%ISOPEN THEN
46 CLOSE c_kpi_measure_id;
47 END IF;
48 OPEN c_kpi_measure_id(p_shared_objective_id,
49 c_analysis_option_comb_rec.analysis_option0,
50 c_analysis_option_comb_rec.analysis_option1,
51 c_analysis_option_comb_rec.analysis_option2,
52 c_analysis_option_comb_rec.series_id);
53 FETCH c_kpi_measure_id INTO l_kpi_measure_id;
54 IF c_kpi_measure_id%NOTFOUND THEN
55 RAISE FND_API.G_EXC_ERROR;
56 END IF;
57 CLOSE c_kpi_measure_id;
58
59 RETURN l_kpi_measure_id;
60
61 END IF;
62
63 EXCEPTION
64 WHEN OTHERS THEN
65 IF c_analysis_option_comb%ISOPEN THEN
66 CLOSE c_analysis_option_comb;
67 END IF;
68 IF c_kpi_measure_id%ISOPEN THEN
69 CLOSE c_kpi_measure_id;
70 END IF;
71 RETURN NULL;
72 END get_shared_obj_kpi_measure;
73
74 /************************************************************************************
75 ************************************************************************************/
76 PROCEDURE Create_Kpi_Measure_Props (
77 p_commit IN VARCHAR2 := FND_API.G_FALSE
78 , p_kpi_measure_rec IN BSC_KPI_MEASURE_PROPS_PUB.kpi_measure_props_rec
79 , p_cascade_shared IN BOOLEAN := TRUE
80 , x_return_status OUT NOCOPY VARCHAR2
81 , x_msg_count OUT NOCOPY NUMBER
82 , x_msg_data OUT NOCOPY VARCHAR2
83 )
84 IS
85 CURSOR c_shared_obj(p_indicator NUMBER) IS
86 SELECT indicator
87 FROM bsc_kpis_b
88 WHERE source_indicator = p_indicator
89 AND share_flag = 2 -- shared objective.
90 AND prototype_flag <> 2;
91
92 l_kpi_measure_rec BSC_KPI_MEASURE_PROPS_PUB.kpi_measure_props_rec;
93 l_kpi_measure_id NUMBER;
94
95 BEGIN
96
97 FND_MSG_PUB.Initialize;
98 x_return_status := FND_API.G_RET_STS_SUCCESS;
99
100 SAVEPOINT BscKpiMeasurePub_Create;
101
102 -- Any Business Logic Validation
103
104 BSC_KPI_MEASURE_PROPS_PVT.Create_Kpi_Measure_Props
105 ( p_commit => p_commit
106 , p_kpi_measure_rec => p_kpi_measure_rec
107 , x_return_status => x_return_status
108 , x_msg_count => x_msg_count
109 , x_msg_data => x_msg_data
110 );
111 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
112 RAISE FND_API.G_EXC_ERROR;
113 END IF;
114
115 IF p_cascade_shared THEN
116 FOR c_shared IN c_shared_obj(p_kpi_measure_rec.objective_id) LOOP
117
118 l_kpi_measure_id := get_shared_obj_kpi_measure
119 ( p_objective_id => p_kpi_measure_rec.objective_id
120 , p_kpi_measure_id => p_kpi_measure_rec.kpi_measure_id
121 , p_shared_objective_id => c_shared.indicator
122 );
123 IF l_kpi_measure_id IS NULL THEN
124 RAISE FND_API.G_EXC_ERROR;
125 END IF;
126
127 l_kpi_measure_rec := p_kpi_measure_rec;
128 l_kpi_measure_rec.objective_id := c_shared.indicator;
129 l_kpi_measure_rec.kpi_measure_id := l_kpi_measure_id;
130
131 BSC_KPI_MEASURE_PROPS_PVT.Create_Kpi_Measure_Props
132 ( p_commit => p_commit
133 , p_kpi_measure_rec => l_kpi_measure_rec
134 , x_return_status => x_return_status
135 , x_msg_count => x_msg_count
136 , x_msg_data => x_msg_data
137 );
138 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
139 RAISE FND_API.G_EXC_ERROR;
140 END IF;
141
142 END LOOP;
143 END IF;
144
145
146 EXCEPTION
147 WHEN FND_API.G_EXC_ERROR THEN
148 ROLLBACK TO BscKpiMeasurePub_Create;
149 IF (x_msg_data IS NULL) THEN
150 FND_MSG_PUB.Count_And_Get
151 ( p_encoded => FND_API.G_FALSE
152 , p_count => x_msg_count
153 , p_data => x_msg_data
154 );
155 END IF;
156 x_return_status := FND_API.G_RET_STS_ERROR;
157 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
158 ROLLBACK TO BscKpiMeasurePub_Create;
159 IF (x_msg_data IS NULL) THEN
160 FND_MSG_PUB.Count_And_Get
161 ( p_encoded => FND_API.G_FALSE
162 , p_count => x_msg_count
163 , p_data => x_msg_data
164 );
165 END IF;
166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
167 WHEN NO_DATA_FOUND THEN
168 ROLLBACK TO BscKpiMeasurePub_Create;
169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
170 IF (x_msg_data IS NOT NULL) THEN
171 x_msg_data := x_msg_data || ' -> BSC_KPI_MEASURE_PROPS_PUB.Create_Kpi_Measure_Props ';
172 ELSE
173 x_msg_data := SQLERRM || ' at BSC_KPI_MEASURE_PROPS_PUB.Create_Kpi_Measure_Props ';
174 END IF;
175 WHEN OTHERS THEN
176 ROLLBACK TO BscKpiMeasurePub_Create;
177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
178 IF (x_msg_data IS NOT NULL) THEN
179 x_msg_data := x_msg_data || ' -> BSC_KPI_MEASURE_PROPS_PUB.Create_Kpi_Measure_Props ';
180 ELSE
181 x_msg_data := SQLERRM || ' at BSC_KPI_MEASURE_PROPS_PUB.Create_Kpi_Measure_Props ';
182 END IF;
183 END Create_Kpi_Measure_Props;
184
185 /************************************************************************************
186 ************************************************************************************/
187 PROCEDURE Create_Default_Kpi_Meas_Props (
188 p_commit IN VARCHAR2 := FND_API.G_FALSE
189 , p_objective_id IN NUMBER
190 , p_kpi_measure_id IN NUMBER
191 , p_cascade_shared IN BOOLEAN := TRUE
192 , x_return_status OUT NOCOPY VARCHAR2
193 , x_msg_count OUT NOCOPY NUMBER
194 , x_msg_data OUT NOCOPY VARCHAR2
195 )
196 IS
197 CURSOR c_shared_obj(p_indicator NUMBER) IS
198 SELECT indicator
199 FROM bsc_kpis_b
200 WHERE source_indicator = p_indicator
201 AND share_flag = 2 -- shared objective.
202 AND prototype_flag <> 2;
203
204 l_kpi_measure_rec BSC_KPI_MEASURE_PROPS_PUB.kpi_measure_props_rec;
205 l_kpi_measure_id NUMBER;
206 l_short_name VARCHAR2(30);
207 l_config_type BSC_KPIS_B.CONFIG_TYPE%TYPE;
208 BEGIN
209
210 FND_MSG_PUB.Initialize;
211 x_return_status := FND_API.G_RET_STS_SUCCESS;
212
213 SAVEPOINT BscKpiMeasurePub_CreateDef;
214
215 l_kpi_measure_rec.objective_id := p_objective_id;
216 l_kpi_measure_rec.kpi_measure_id := p_kpi_measure_id;
217 l_kpi_measure_rec.prototype_color := BSC_COLOR_REPOSITORY.EXCELLENT_COLOR;
218 l_kpi_measure_rec.prototype_trend := C_TREND_UNACC_DECREASE;
219 l_kpi_measure_rec.color_by_total := 1;
220 l_kpi_measure_rec.disable_color := 'F';
221 l_kpi_measure_rec.disable_trend := 'F';
222 l_kpi_measure_rec.apply_color_flag := 1;
223 l_kpi_measure_rec.default_calculation := NULL;
224 l_kpi_measure_rec.created_by := FND_GLOBAL.USER_ID;
225 l_kpi_measure_rec.creation_date := SYSDATE;
226 l_kpi_measure_rec.last_updated_by := FND_GLOBAL.USER_ID;
227 l_kpi_measure_rec.last_update_date := SYSDATE;
228 l_kpi_measure_rec.last_update_login := FND_GLOBAL.LOGIN_ID;
229
230 BEGIN
231 SELECT short_name,config_type into l_short_name,l_config_type
232 FROM bsc_kpis_b
233 WHERE indicator = p_objective_id;
234 EXCEPTION
235 WHEN OTHERS THEN
236 l_short_name := NULL;
237 END;
238
239 IF l_short_name IS NOT NULL AND l_config_type <> 7 THEN
240 l_kpi_measure_rec.disable_color := 'T';
241 l_kpi_measure_rec.disable_trend := 'T';
242 END IF;
243
244 BSC_KPI_MEASURE_PROPS_PVT.Create_Kpi_Measure_Props
245 ( p_commit => p_commit
246 , p_kpi_measure_rec => l_kpi_measure_rec
247 , x_return_status => x_return_status
248 , x_msg_count => x_msg_count
249 , x_msg_data => x_msg_data
250 );
251 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
252 RAISE FND_API.G_EXC_ERROR;
253 END IF;
254
255 IF p_cascade_shared THEN
256 FOR c_shared IN c_shared_obj(l_kpi_measure_rec.objective_id) LOOP
257
258 l_kpi_measure_id := get_shared_obj_kpi_measure
259 ( p_objective_id => l_kpi_measure_rec.objective_id
260 , p_kpi_measure_id => l_kpi_measure_rec.kpi_measure_id
261 , p_shared_objective_id => c_shared.indicator
262 );
263 IF l_kpi_measure_id IS NULL THEN
264 RAISE FND_API.G_EXC_ERROR;
265 END IF;
266
267 --l_kpi_measure_rec := p_kpi_measure_rec;
268 l_kpi_measure_rec.objective_id := c_shared.indicator;
269 l_kpi_measure_rec.kpi_measure_id := l_kpi_measure_id;
270
271 BSC_KPI_MEASURE_PROPS_PVT.Create_Kpi_Measure_Props
272 ( p_commit => p_commit
273 , p_kpi_measure_rec => l_kpi_measure_rec
274 , x_return_status => x_return_status
275 , x_msg_count => x_msg_count
276 , x_msg_data => x_msg_data
277 );
278 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
279 RAISE FND_API.G_EXC_ERROR;
280 END IF;
281
282 END LOOP;
283 END IF;
284
285
286 EXCEPTION
287 WHEN FND_API.G_EXC_ERROR THEN
288 ROLLBACK TO BscKpiMeasurePub_CreateDef;
289 IF (x_msg_data IS NULL) THEN
290 FND_MSG_PUB.Count_And_Get
291 ( p_encoded => FND_API.G_FALSE
292 , p_count => x_msg_count
293 , p_data => x_msg_data
294 );
295 END IF;
296 x_return_status := FND_API.G_RET_STS_ERROR;
297 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
298 ROLLBACK TO BscKpiMeasurePub_CreateDef;
299 IF (x_msg_data IS NULL) THEN
300 FND_MSG_PUB.Count_And_Get
301 ( p_encoded => FND_API.G_FALSE
302 , p_count => x_msg_count
303 , p_data => x_msg_data
304 );
305 END IF;
306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
307 WHEN NO_DATA_FOUND THEN
308 ROLLBACK TO BscKpiMeasurePub_CreateDef;
309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
310 IF (x_msg_data IS NOT NULL) THEN
311 x_msg_data := x_msg_data || ' -> BSC_KPI_MEASURE_PROPS_PUB.Create_Default_Kpi_Meas_Props ';
312 ELSE
313 x_msg_data := SQLERRM || ' at BSC_KPI_MEASURE_PROPS_PUB.Create_Default_Kpi_Meas_Props ';
314 END IF;
315 WHEN OTHERS THEN
316 ROLLBACK TO BscKpiMeasurePub_CreateDef;
317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
318 IF (x_msg_data IS NOT NULL) THEN
319 x_msg_data := x_msg_data || ' -> BSC_KPI_MEASURE_PROPS_PUB.Create_Default_Kpi_Meas_Props ';
320 ELSE
321 x_msg_data := SQLERRM || ' at BSC_KPI_MEASURE_PROPS_PUB.Create_Default_Kpi_Meas_Props ';
322 END IF;
323 END Create_Default_Kpi_Meas_Props;
324
325 /************************************************************************************
326 ************************************************************************************/
327 PROCEDURE Update_Kpi_Measure_Props (
328 p_commit IN VARCHAR2 := FND_API.G_FALSE
329 , p_kpi_measure_rec IN BSC_KPI_MEASURE_PROPS_PUB.kpi_measure_props_rec
330 , p_cascade_shared IN BOOLEAN := TRUE
331 , x_return_status OUT NOCOPY VARCHAR2
332 , x_msg_count OUT NOCOPY NUMBER
333 , x_msg_data OUT NOCOPY VARCHAR2
334 )
335 IS
336 CURSOR c_shared_obj(p_indicator NUMBER) IS
337 SELECT indicator
338 FROM bsc_kpis_b
339 WHERE source_indicator = p_indicator
340 AND share_flag = 2 -- shared objective.
341 AND prototype_flag <> 2;
342
343 l_kpi_measure_rec BSC_KPI_MEASURE_PROPS_PUB.kpi_measure_props_rec;
344 l_kpi_measure_id NUMBER;
345
346 BEGIN
347
348 FND_MSG_PUB.Initialize;
349 x_return_status := FND_API.G_RET_STS_SUCCESS;
350
351 SAVEPOINT BscKpiMeasurePub_Update;
352
353 -- Any Business Logic Validation
354
355 BSC_KPI_MEASURE_PROPS_PVT.Update_Kpi_Measure_Props
356 ( p_commit => p_commit
357 , p_kpi_measure_rec => p_kpi_measure_rec
358 , x_return_status => x_return_status
359 , x_msg_count => x_msg_count
360 , x_msg_data => x_msg_data
361 );
362 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
363 RAISE FND_API.G_EXC_ERROR;
364 END IF;
365
366 IF p_cascade_shared THEN
367 FOR c_shared IN c_shared_obj(p_kpi_measure_rec.objective_id) LOOP
368
369 l_kpi_measure_id := get_shared_obj_kpi_measure
370 ( p_objective_id => p_kpi_measure_rec.objective_id
371 , p_kpi_measure_id => p_kpi_measure_rec.kpi_measure_id
372 , p_shared_objective_id => c_shared.indicator
373 );
374 IF l_kpi_measure_id IS NULL THEN
375 RAISE FND_API.G_EXC_ERROR;
376 END IF;
377
378 l_kpi_measure_rec := p_kpi_measure_rec;
382 BSC_KPI_MEASURE_PROPS_PVT.Update_Kpi_Measure_Props
379 l_kpi_measure_rec.objective_id := c_shared.indicator;
380 l_kpi_measure_rec.kpi_measure_id := l_kpi_measure_id;
381
383 ( p_commit => p_commit
384 , p_kpi_measure_rec => l_kpi_measure_rec
385 , x_return_status => x_return_status
386 , x_msg_count => x_msg_count
387 , x_msg_data => x_msg_data
388 );
389 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
390 RAISE FND_API.G_EXC_ERROR;
391 END IF;
392
393 END LOOP;
394 END IF;
395
396 EXCEPTION
397 WHEN FND_API.G_EXC_ERROR THEN
398 ROLLBACK TO BscKpiMeasurePub_Update;
399 IF (x_msg_data IS NULL) THEN
400 FND_MSG_PUB.Count_And_Get
401 ( p_encoded => FND_API.G_FALSE
402 , p_count => x_msg_count
403 , p_data => x_msg_data
404 );
405 END IF;
406 x_return_status := FND_API.G_RET_STS_ERROR;
407 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
408 ROLLBACK TO BscKpiMeasurePub_Update;
409 IF (x_msg_data IS NULL) THEN
410 FND_MSG_PUB.Count_And_Get
411 ( p_encoded => FND_API.G_FALSE
412 , p_count => x_msg_count
413 , p_data => x_msg_data
414 );
415 END IF;
416 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
417 WHEN NO_DATA_FOUND THEN
418 ROLLBACK TO BscKpiMeasurePub_Update;
419 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
420 IF (x_msg_data IS NOT NULL) THEN
421 x_msg_data := x_msg_data || ' -> BSC_KPI_MEASURE_PROPS_PUB.Update_Kpi_Measure_Props ';
422 ELSE
423 x_msg_data := SQLERRM || ' at BSC_KPI_MEASURE_PROPS_PUB.Update_Kpi_Measure_Props ';
424 END IF;
425 WHEN OTHERS THEN
426 ROLLBACK TO BscKpiMeasurePub_Update;
427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
428 IF (x_msg_data IS NOT NULL) THEN
429 x_msg_data := x_msg_data || ' -> BSC_KPI_MEASURE_PROPS_PUB.Update_Kpi_Measure_Props ';
430 ELSE
431 x_msg_data := SQLERRM || ' at BSC_KPI_MEASURE_PROPS_PUB.Update_Kpi_Measure_Props ';
432 END IF;
433 END Update_Kpi_Measure_Props;
434
435 /************************************************************************************
436 ************************************************************************************/
437 PROCEDURE Delete_Kpi_Measure_Props (
438 p_commit IN VARCHAR2:= FND_API.G_FALSE
439 , p_objective_id IN NUMBER
440 , p_kpi_measure_id IN NUMBER
441 , p_cascade_shared IN BOOLEAN := TRUE
442 , x_return_status OUT NOCOPY VARCHAR2
443 , x_msg_count OUT NOCOPY NUMBER
444 , x_msg_data OUT NOCOPY VARCHAR2
445 )
446 IS
447 CURSOR c_shared_obj(p_indicator NUMBER) IS
448 SELECT indicator
449 FROM bsc_kpis_b
450 WHERE source_indicator = p_indicator
451 AND share_flag = 2 -- shared objective.
452 AND prototype_flag <> 2;
453
454 l_kpi_measure_id NUMBER;
455
456 BEGIN
457
458 FND_MSG_PUB.Initialize;
459 x_return_status := FND_API.G_RET_STS_SUCCESS;
460
461 SAVEPOINT BscKpiMeasurePub_Delete;
462
463 -- Any Business Logic Validation
464
465 BSC_KPI_MEASURE_PROPS_PVT.Delete_Kpi_Measure_Props
466 ( p_commit => p_commit
467 , p_objective_id => p_objective_id
468 , p_kpi_measure_id => p_kpi_measure_id
469 , x_return_status => x_return_status
470 , x_msg_count => x_msg_count
471 , x_msg_data => x_msg_data
472 );
473 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
474 RAISE FND_API.G_EXC_ERROR;
475 END IF;
476
477 IF p_cascade_shared THEN
478
479 FOR c_shared IN c_shared_obj(p_objective_id) LOOP
480
481 l_kpi_measure_id := get_shared_obj_kpi_measure
482 ( p_objective_id => p_objective_id
483 , p_kpi_measure_id => p_kpi_measure_id
484 , p_shared_objective_id => c_shared.indicator
485 );
486 IF l_kpi_measure_id IS NULL THEN
487 RAISE FND_API.G_EXC_ERROR;
488 END IF;
492 , p_objective_id => c_shared.indicator
489
490 BSC_KPI_MEASURE_PROPS_PVT.Delete_Kpi_Measure_Props
491 ( p_commit => p_commit
493 , p_kpi_measure_id => l_kpi_measure_id
494 , x_return_status => x_return_status
495 , x_msg_count => x_msg_count
496 , x_msg_data => x_msg_data
497 );
498 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
499 RAISE FND_API.G_EXC_ERROR;
500 END IF;
501
502 END LOOP;
503
504 END IF;
505
506 EXCEPTION
507 WHEN FND_API.G_EXC_ERROR THEN
508 ROLLBACK TO BscKpiMeasurePub_Delete;
509 IF (x_msg_data IS NULL) THEN
510 FND_MSG_PUB.Count_And_Get
511 ( p_encoded => FND_API.G_FALSE
512 , p_count => x_msg_count
513 , p_data => x_msg_data
514 );
515 END IF;
516 x_return_status := FND_API.G_RET_STS_ERROR;
517 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
518 ROLLBACK TO BscKpiMeasurePub_Delete;
519 IF (x_msg_data IS NULL) THEN
520 FND_MSG_PUB.Count_And_Get
521 ( p_encoded => FND_API.G_FALSE
522 , p_count => x_msg_count
523 , p_data => x_msg_data
524 );
525 END IF;
526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
527 WHEN NO_DATA_FOUND THEN
528 ROLLBACK TO BscKpiMeasurePub_Delete;
529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
530 IF (x_msg_data IS NOT NULL) THEN
531 x_msg_data := x_msg_data || ' -> BSC_KPI_MEASURE_PROPS_PUB.Delete_Kpi_Measure_Props ';
532 ELSE
533 x_msg_data := SQLERRM || ' at BSC_KPI_MEASURE_PROPS_PUB.Delete_Kpi_Measure_Props ';
534 END IF;
535 WHEN OTHERS THEN
536 ROLLBACK TO BscKpiMeasurePub_Delete;
537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
538 IF (x_msg_data IS NOT NULL) THEN
539 x_msg_data := x_msg_data || ' -> BSC_KPI_MEASURE_PROPS_PUB.Delete_Kpi_Measure_Props ';
540 ELSE
541 x_msg_data := SQLERRM || ' at BSC_KPI_MEASURE_PROPS_PUB.Delete_Kpi_Measure_Props ';
542 END IF;
543 END Delete_Kpi_Measure_Props;
544
545 /************************************************************************************
546 ************************************************************************************/
547 PROCEDURE Delete_Obj_Kpi_Measure_Props (
548 p_commit IN VARCHAR2:= FND_API.G_FALSE
549 , p_objective_id IN NUMBER
550 , p_cascade_shared IN BOOLEAN := TRUE
551 , x_return_status OUT NOCOPY VARCHAR2
552 , x_msg_count OUT NOCOPY NUMBER
553 , x_msg_data OUT NOCOPY VARCHAR2
554 )
555 IS
556 CURSOR c_shared_obj(p_indicator NUMBER) IS
557 SELECT indicator
558 FROM bsc_kpis_b
559 WHERE source_indicator = p_indicator
560 AND share_flag = 2 -- shared objective.
561 AND prototype_flag <> 2;
562
563 BEGIN
564
565 FND_MSG_PUB.Initialize;
566 x_return_status := FND_API.G_RET_STS_SUCCESS;
567
568 SAVEPOINT BscKpiMeasurePub_DeleteAll;
569
570 -- Any Business Logic Validation
571
572 BSC_KPI_MEASURE_PROPS_PVT.Delete_Obj_Kpi_Measure_Props
573 ( p_commit => p_commit
574 , p_objective_id => p_objective_id
575 , x_return_status => x_return_status
576 , x_msg_count => x_msg_count
577 , x_msg_data => x_msg_data
578 );
579 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
580 RAISE FND_API.G_EXC_ERROR;
581 END IF;
582
583 IF p_cascade_shared THEN
584
585 FOR c_shared IN c_shared_obj(p_objective_id) LOOP
586
587 BSC_KPI_MEASURE_PROPS_PVT.Delete_Obj_Kpi_Measure_Props
588 ( p_commit => p_commit
589 , p_objective_id => c_shared.indicator
590 , x_return_status => x_return_status
591 , x_msg_count => x_msg_count
592 , x_msg_data => x_msg_data
593 );
594 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
595 RAISE FND_API.G_EXC_ERROR;
596 END IF;
597
598 END LOOP;
599
600 END IF;
601
602 EXCEPTION
603 WHEN FND_API.G_EXC_ERROR THEN
604 ROLLBACK TO BscKpiMeasurePub_DeleteAll;
605 IF (x_msg_data IS NULL) THEN
606 FND_MSG_PUB.Count_And_Get
607 ( p_encoded => FND_API.G_FALSE
608 , p_count => x_msg_count
609 , p_data => x_msg_data
610 );
611 END IF;
612 x_return_status := FND_API.G_RET_STS_ERROR;
613 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
614 ROLLBACK TO BscKpiMeasurePub_DeleteAll;
615 IF (x_msg_data IS NULL) THEN
616 FND_MSG_PUB.Count_And_Get
617 ( p_encoded => FND_API.G_FALSE
618 , p_count => x_msg_count
619 , p_data => x_msg_data
620 );
621 END IF;
622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
623 WHEN NO_DATA_FOUND THEN
624 ROLLBACK TO BscKpiMeasurePub_DeleteAll;
625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
626 IF (x_msg_data IS NOT NULL) THEN
627 x_msg_data := x_msg_data || ' -> BSC_KPI_MEASURE_PROPS_PUB.Delete_Obj_Kpi_Measure_Props ';
628 ELSE
629 x_msg_data := SQLERRM || ' at BSC_KPI_MEASURE_PROPS_PUB.Delete_Obj_Kpi_Measure_Props ';
630 END IF;
631 WHEN OTHERS THEN
632 ROLLBACK TO BscKpiMeasurePub_DeleteAll;
633 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
634 IF (x_msg_data IS NOT NULL) THEN
635 x_msg_data := x_msg_data || ' -> BSC_KPI_MEASURE_PROPS_PUB.Delete_Obj_Kpi_Measure_Props ';
636 ELSE
637 x_msg_data := SQLERRM || ' at BSC_KPI_MEASURE_PROPS_PUB.Delete_Obj_Kpi_Measure_Props ';
638 END IF;
639 END Delete_Obj_Kpi_Measure_Props;
640
641 /************************************************************************************
642 ************************************************************************************/
643 PROCEDURE Retrieve_Kpi_Measure_Props (
644 p_objective_id IN NUMBER
645 , p_kpi_measure_id IN NUMBER
646 , x_kpi_measure_rec OUT NOCOPY BSC_KPI_MEASURE_PROPS_PUB.kpi_measure_props_rec
647 , x_return_status OUT NOCOPY VARCHAR2
648 , x_msg_count OUT NOCOPY NUMBER
649 , x_msg_data OUT NOCOPY VARCHAR2
650 )
651 IS
652 BEGIN
653
654 FND_MSG_PUB.Initialize;
655 x_return_status := FND_API.G_RET_STS_SUCCESS;
656
657 -- Any Business Logic Validation
658
659 BSC_KPI_MEASURE_PROPS_PVT.Retrieve_Kpi_Measure_Props
660 ( p_objective_id => p_objective_id
661 , p_kpi_measure_id => p_kpi_measure_id
662 , x_kpi_measure_rec => x_kpi_measure_rec
663 , x_return_status => x_return_status
664 , x_msg_count => x_msg_count
665 , x_msg_data => x_msg_data
666 );
667 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
668 RAISE FND_API.G_EXC_ERROR;
669 END IF;
670
671 EXCEPTION
672 WHEN FND_API.G_EXC_ERROR THEN
673 IF (x_msg_data IS NULL) THEN
674 FND_MSG_PUB.Count_And_Get
675 ( p_encoded => FND_API.G_FALSE
676 , p_count => x_msg_count
677 , p_data => x_msg_data
678 );
679 END IF;
680 x_return_status := FND_API.G_RET_STS_ERROR;
681 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
682 IF (x_msg_data IS NULL) THEN
683 FND_MSG_PUB.Count_And_Get
684 ( p_encoded => FND_API.G_FALSE
685 , p_count => x_msg_count
686 , p_data => x_msg_data
687 );
688 END IF;
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
690 WHEN NO_DATA_FOUND THEN
691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
692 IF (x_msg_data IS NOT NULL) THEN
693 x_msg_data := x_msg_data || ' -> BSC_KPI_MEASURE_PROPS_PUB.Delete_Obj_Kpi_Measure_Props ';
694 ELSE
695 x_msg_data := SQLERRM || ' at BSC_KPI_MEASURE_PROPS_PUB.Delete_Obj_Kpi_Measure_Props ';
696 END IF;
697 WHEN OTHERS THEN
698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
699 IF (x_msg_data IS NOT NULL) THEN
700 x_msg_data := x_msg_data || ' -> BSC_KPI_MEASURE_PROPS_PUB.Delete_Obj_Kpi_Measure_Props ';
701 ELSE
702 x_msg_data := SQLERRM || ' at BSC_KPI_MEASURE_PROPS_PUB.Delete_Obj_Kpi_Measure_Props ';
703 END IF;
704 END Retrieve_Kpi_Measure_Props;
705
706 END BSC_KPI_MEASURE_PROPS_PUB;