[Home] [Help]
PACKAGE BODY: APPS.BSC_COPY_INDICATOR_PUB
Source
1 PACKAGE BODY BSC_COPY_INDICATOR_PUB AS
2 /* $Header: BSCPCINB.pls 120.7.12000000.1 2007/07/17 07:43:41 appldev noship $ */
3 /*=======================================================================+
4 | Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME |
8 | BSCPCINB.pls |
9 | |
10 | Creation Date: |
11 | March 21, 2007 |
12 | |
13 | Creator: |
14 | Ajitha Koduri |
15 | |
16 | Description: |
17 | Public Body version. |
18 | This package contains all the APIs related to copy and move |
19 | indicators |
20 | |
21 | History: |
22 | 21-MAR-2007 akoduri Copy Indicator Enh#5943238 |
23 | 11-APR-2007 akoduri Bug 5982764 Move of Indicator is not |
24 | the new group to scorecard |
25 | 12-APR-2007 akoduri Bug 5982815 Key items are not retained |
26 | in incremental migration |
27 | 16-MAR-2007 akoduri Bug 5988082 Issue with incremental |
28 | migration when objectives with common measures |
29 | are chosen |
30 | 05-JUN-2007 akoduri Bug 5982136 Default Periodicity property |
31 | is not retained |
32 | 06-JUN-2007 akoduri Bug 5958688 Enable YTD as default at KPI |
33 | 14-JUN-2007 akoduri Bug 6129225 viewport_flag (Number of |
34 | periods is getting reset for custom periodicities|
35 *=======================================================================*/
36
37 g_base_message VARCHAR2(4000);
38 g_message VARCHAR2(4000);
39
40 /************************************************************************************
41 -- API name : Get_Ind_Group_Id
42 -- Type : Public
43 -- Function :
44 -- This API retrieves the old Objective Group Id to which the indicator is
45 -- attached
46 --
47 ************************************************************************************/
48
49 FUNCTION Get_Ind_Group_Id(
50 p_Indicator IN NUMBER
51 )
52 RETURN NUMBER IS
53
54 l_Group_Id bsc_kpis_b.ind_group_id%TYPE;
55
56 CURSOR c_Ind_Group_Id IS
57 SELECT
58 a.ind_group_id
59 FROM
60 bsc_kpis_b a
61 WHERE
62 a.indicator = p_Indicator;
63 BEGIN
64 OPEN c_Ind_Group_Id;
65 FETCH c_Ind_Group_Id INTO l_Group_Id;
66 CLOSE c_Ind_Group_Id;
67
68 RETURN l_group_id;
69
70 EXCEPTION
71 WHEN OTHERS THEN
72 RETURN NULL;
73 END Get_Ind_Group_Id;
74
75 /************************************************************************************
76 -- API name : Assign_Ind_Group_To_Tab
77 -- Type : Public
78 -- Function :
79 -- This API will check whether the indicator group is already attached to the
80 -- scorecard. If it is attached then the new group will be attached
81 ************************************************************************************/
82 PROCEDURE Assign_Ind_Group_To_Tab (
83 p_commit IN VARCHAR2 := FND_API.G_FALSE
84 , p_Indicator IN NUMBER
85 , p_New_Indicator_Group IN NUMBER
86 , p_Old_Indicator_Group IN NUMBER
87 , x_return_status OUT NOCOPY VARCHAR2
88 , x_msg_count OUT NOCOPY NUMBER
89 , x_msg_data OUT NOCOPY VARCHAR2
90 )
91 IS
92 l_Tab_Id bsc_tabs_b.tab_id%TYPE;
93 l_Check_Association NUMBER := 0;
94 l_Indicator_Count NUMBER := 0;
95 l_Bsc_Kpi_Group_Rec BSC_KPI_GROUP_PUB.Bsc_Kpi_Group_Rec;
96 CURSOR c_Tab_Id IS
97 SELECT
98 tab_id
99 FROM
100 bsc_tab_indicators
101 WHERE
102 indicator = p_Indicator;
103
104 BEGIN
105 FND_MSG_PUB.Initialize;
106 x_return_status := FND_API.G_RET_STS_SUCCESS;
107
108 SAVEPOINT BscAssignIndGroupTab;
109
110 OPEN c_Tab_Id;
111 FETCH c_Tab_Id INTO l_Tab_Id;
112 CLOSE c_Tab_Id;
113
114 IF l_Tab_Id IS NOT NULL THEN
115 SELECT
116 COUNT(1)
117 INTO
118 l_Check_Association
119 FROM
120 bsc_tab_ind_groups_vl
121 WHERE
122 tab_id = p_Indicator
123 AND ind_group_id = p_New_Indicator_Group;
124
125 IF l_Check_Association IS NOT NULL AND l_Check_Association = 0 THEN
126 l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id := p_New_Indicator_Group;
127 l_Bsc_Kpi_Group_Rec.Bsc_Tab_Id := l_Tab_Id;
128 BSC_KPI_GROUP_PVT.Update_Kpi_Group(
129 p_commit => FND_API.G_FALSE
130 ,p_Bsc_Kpi_Group_Rec => l_Bsc_Kpi_Group_Rec
131 ,x_return_status => x_return_status
132 ,x_msg_count => x_msg_count
133 ,x_msg_data => x_msg_data
134 );
135 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
136 RAISE FND_API.G_EXC_ERROR;
137 END IF;
138 END IF;
139
140 SELECT
141 COUNT(1)
142 INTO
143 l_Indicator_Count
144 FROM
145 bsc_tab_indicators ti,
146 bsc_kpis_vl k
147 WHERE
148 ti.tab_id = l_Tab_Id
149 AND ti.indicator = k.indicator
150 AND k.ind_group_id = p_Old_Indicator_Group;
151
152 IF l_Indicator_Count = 0 THEN
153 l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id := p_Old_Indicator_Group;
154 l_Bsc_Kpi_Group_Rec.Bsc_Tab_Id := l_Tab_Id;
155 BSC_KPI_GROUP_PVT.Delete_Kpi_Group(
156 p_commit => FND_API.G_FALSE
157 ,p_Bsc_Kpi_Group_Rec => l_Bsc_Kpi_Group_Rec
158 ,x_return_status => x_return_status
159 ,x_msg_count => x_msg_count
160 ,x_msg_data => x_msg_data
161 );
162 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
163 RAISE FND_API.G_EXC_ERROR;
164 END IF;
165 END IF;
166
167 END IF;
168
169 IF (p_commit = FND_API.G_TRUE) THEN
170 COMMIT;
171 END IF;
172 EXCEPTION
173 WHEN FND_API.G_EXC_ERROR THEN
174 ROLLBACK TO BscAssignIndGroupTab;
175 IF (x_msg_data IS NULL) THEN
176 FND_MSG_PUB.Count_And_Get
177 ( p_encoded => FND_API.G_FALSE
178 , p_count => x_msg_count
179 , p_data => x_msg_data
180 );
181 END IF;
182 x_return_status := FND_API.G_RET_STS_ERROR;
183 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
184 ROLLBACK TO BscAssignIndGroupTab;
185 IF (x_msg_data IS NULL) THEN
186 FND_MSG_PUB.Count_And_Get
187 ( p_encoded => FND_API.G_FALSE
188 , p_count => x_msg_count
189 , p_data => x_msg_data
190 );
191 END IF;
192 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
193 WHEN NO_DATA_FOUND THEN
194 ROLLBACK TO BscAssignIndGroupTab;
195 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
196 IF (x_msg_data IS NOT NULL) THEN
197 x_msg_data := x_msg_data || ' ->BSC_COPY_INDICATOR_PUB.Assign_Ind_Group_To_Tab ';
198 ELSE
199 x_msg_data := SQLERRM || 'at BSC_COPY_INDICATOR_PUB.Assign_Ind_Group_To_Tab ';
200 END IF;
201 WHEN OTHERS THEN
202 ROLLBACK TO BscAssignIndGroupTab;
203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
204 IF (x_msg_data IS NOT NULL) THEN
205 x_msg_data := x_msg_data || ' ->BSC_COPY_INDICATOR_PUB.Assign_Ind_Group_To_Tab ';
206 ELSE
207 x_msg_data := SQLERRM || ' at BSC_COPY_INDICATOR_PUB.Assign_Ind_Group_To_Tab ';
208 END IF;
209 END Assign_Ind_Group_To_Tab;
210
211 /************************************************************************************
212 -- API name : Update_Kpi_Group_Properties
213 -- Type : Public
214 -- Function :
215 -- This API will update the indicator group type in the following scenarios
216 -- 1. If the group type is 1 and if it has already 1 kpi attached then
217 -- the group type will be updated to 0
218 -- Add any cases if required
219 ************************************************************************************/
220 PROCEDURE Update_Kpi_Group_Properties (
221 p_commit IN VARCHAR2 := FND_API.G_FALSE
222 , p_New_Indicator_Group IN NUMBER
223 , x_return_status OUT NOCOPY VARCHAR2
224 , x_msg_count OUT NOCOPY NUMBER
225 , x_msg_data OUT NOCOPY VARCHAR2
226 )
227 IS
228 l_Bsc_Kpi_Group_Rec BSC_KPI_GROUP_PUB.Bsc_Kpi_Group_Rec;
229 l_Kpi_Count NUMBER := 0;
230 BEGIN
231 FND_MSG_PUB.Initialize;
232 x_return_status := FND_API.G_RET_STS_SUCCESS;
233
234 SAVEPOINT BscUpdateKpiGroupProperties;
235
236 l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Id := p_New_Indicator_Group;
237
238 BSC_KPI_GROUP_PUB.Retrieve_Kpi_Group(
239 p_commit => FND_API.G_FALSE
240 ,p_Bsc_Kpi_Group_Rec => l_Bsc_Kpi_Group_Rec
241 ,x_Bsc_Kpi_Group_Rec => l_Bsc_Kpi_Group_Rec
242 ,x_return_status => x_return_status
243 ,x_msg_count => x_msg_count
244 ,x_msg_data => x_msg_data
245 );
246 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
247 RAISE FND_API.G_EXC_ERROR;
248 END IF;
249
250 SELECT COUNT(1)
251 INTO
252 l_Kpi_Count
253 FROM
254 bsc_kpis_b
255 WHERE
256 ind_group_id = p_New_Indicator_Group
257 AND prototype_flag <> -2
258 AND share_flag <> 2;
259
260 IF l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Type = BSC_COPY_INDICATOR_PUB.INDICATOR_BELOW_NAME
261 AND l_Kpi_Count >= 1 THEN
262 l_Bsc_Kpi_Group_Rec.Bsc_Kpi_Group_Type := BSC_COPY_INDICATOR_PUB.INDICATOR_BESIDE_NAME;
263 END IF;
264
265 BSC_KPI_GROUP_PUB.Update_Kpi_Group(
266 p_commit => FND_API.G_FALSE
267 ,p_Bsc_Kpi_Group_Rec => l_Bsc_Kpi_Group_Rec
268 ,x_return_status => x_return_status
269 ,x_msg_count => x_msg_count
270 ,x_msg_data => x_msg_data
271 );
272 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
273 RAISE FND_API.G_EXC_ERROR;
274 END IF;
275
276 IF (p_commit = FND_API.G_TRUE) THEN
277 COMMIT;
278 END IF;
279 EXCEPTION
280 WHEN FND_API.G_EXC_ERROR THEN
281 ROLLBACK TO BscUpdateKpiGroupProperties;
282 IF (x_msg_data IS NULL) THEN
283 FND_MSG_PUB.Count_And_Get
284 ( p_encoded => FND_API.G_FALSE
285 , p_count => x_msg_count
286 , p_data => x_msg_data
287 );
288 END IF;
289 x_return_status := FND_API.G_RET_STS_ERROR;
290 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
291 ROLLBACK TO BscUpdateKpiGroupProperties;
292 IF (x_msg_data IS NULL) THEN
293 FND_MSG_PUB.Count_And_Get
294 ( p_encoded => FND_API.G_FALSE
295 , p_count => x_msg_count
296 , p_data => x_msg_data
297 );
298 END IF;
299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
300 WHEN NO_DATA_FOUND THEN
301 ROLLBACK TO BscUpdateKpiGroupProperties;
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303 IF (x_msg_data IS NOT NULL) THEN
304 x_msg_data := x_msg_data || ' -> BSC_COPY_INDICATOR_PUB.Update_Kpi_Group_Properties ';
305 ELSE
306 x_msg_data := SQLERRM || ' at BSC_COPY_INDICATOR_PUB.Update_Kpi_Group_Properties ';
307 END IF;
308 WHEN OTHERS THEN
309 ROLLBACK TO BscUpdateKpiGroupProperties;
310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
311 IF (x_msg_data IS NOT NULL) THEN
312 x_msg_data := x_msg_data || ' -> BSC_COPY_INDICATOR_PUB.Update_Kpi_Group_Properties ';
313 ELSE
314 x_msg_data := SQLERRM || ' at BSC_COPY_INDICATOR_PUB.Update_Kpi_Group_Properties ';
315 END IF;
316 END Update_Kpi_Group_Properties;
317 /************************************************************************************
318 -- API name : Move_Indicator
319 -- Type : Public
320 -- Function :
321 -- This API is used to move an indicator from one indicator group to another
322 -- This can also be used to reposition the indicator with the same group
323 -- 1. Update Group properties if earlier group type is 1
324 -- (Color Box above objective label) and the kpi count in the objective
325 -- is already 1
326 -- 2. Update the Ind_Group_Id to the new group
327 ************************************************************************************/
328 PROCEDURE Move_Indicator (
329 p_commit IN VARCHAR2 := FND_API.G_FALSE
330 , p_Indicator IN NUMBER
331 , p_New_Indicator_Group IN NUMBER
332 , p_Assign_Group_To_Tab IN VARCHAR2 := FND_API.G_TRUE
333 , x_return_status OUT NOCOPY VARCHAR2
334 , x_msg_count OUT NOCOPY NUMBER
335 , x_msg_data OUT NOCOPY VARCHAR2
336 ) IS
337 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
338 l_Old_Indicator_Group bsc_tab_ind_groups_b.ind_group_id%TYPE;
339 BEGIN
340
341 FND_MSG_PUB.Initialize;
342 x_return_status := FND_API.G_RET_STS_SUCCESS;
343
344 SAVEPOINT BscMovIndicatorSavePnt;
345
346 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := p_Indicator;
347 BSC_KPI_PVT.Retrieve_Kpi(
348 p_commit => FND_API.G_FALSE
349 ,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
350 ,x_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
351 ,x_return_status => x_return_status
352 ,x_msg_count => x_msg_count
353 ,x_msg_data => x_msg_data
354 );
355 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
356 RAISE FND_API.G_EXC_ERROR;
357 END IF;
358
359 l_Old_Indicator_Group := l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Group_Id;
360 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Group_Id := p_New_Indicator_Group;
361 BSC_KPI_PVT.Update_Kpi(
362 p_commit => FND_API.G_FALSE
363 ,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
364 ,x_return_status => x_return_status
365 ,x_msg_count => x_msg_count
366 ,x_msg_data => x_msg_data
367 );
368 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
369 RAISE FND_API.G_EXC_ERROR;
370 END IF;
371
372 IF p_Assign_Group_To_Tab = FND_API.G_TRUE THEN
373 Assign_Ind_Group_To_Tab (
374 p_commit => FND_API.G_FALSE
375 ,p_Indicator => p_Indicator
376 ,p_New_Indicator_Group => p_New_Indicator_Group
377 ,p_Old_Indicator_Group => l_Old_Indicator_Group
378 ,x_return_status => x_return_status
379 ,x_msg_count => x_msg_count
380 ,x_msg_data => x_msg_data
381 );
382 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
383 RAISE FND_API.G_EXC_ERROR;
384 END IF;
385 END IF;
386
387 IF (p_commit = FND_API.G_TRUE) THEN
388 COMMIT;
389 END IF;
390
391 EXCEPTION
392 WHEN FND_API.G_EXC_ERROR THEN
393 ROLLBACK TO BscMovIndicatorSavePnt;
394 IF (x_msg_data IS NULL) THEN
395 FND_MSG_PUB.Count_And_Get
396 ( p_encoded => FND_API.G_FALSE
397 , p_count => x_msg_count
398 , p_data => x_msg_data
399 );
400 END IF;
401 x_return_status := FND_API.G_RET_STS_ERROR;
402 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
403 ROLLBACK TO BscMovIndicatorSavePnt;
404 IF (x_msg_data IS NULL) THEN
405 FND_MSG_PUB.Count_And_Get
406 ( p_encoded => FND_API.G_FALSE
407 , p_count => x_msg_count
408 , p_data => x_msg_data
409 );
410 END IF;
411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
412 WHEN NO_DATA_FOUND THEN
413 ROLLBACK TO BscMovIndicatorSavePnt;
414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
415 IF (x_msg_data IS NOT NULL) THEN
416 x_msg_data := x_msg_data || ' -> BSC_COPY_INDICATOR_PUB.Move_Indicator ';
417 ELSE
418 x_msg_data := SQLERRM || ' at BSC_COPY_INDICATOR_PUB.Move_Indicator ';
419 END IF;
420 WHEN OTHERS THEN
421 ROLLBACK TO BscMovIndicatorSavePnt;
422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
423 IF (x_msg_data IS NOT NULL) THEN
424 x_msg_data := x_msg_data || ' -> BSC_COPY_INDICATOR_PUB.Move_Indicator ';
425 ELSE
426 x_msg_data := SQLERRM || ' at BSC_COPY_INDICATOR_PUB.Move_Indicator ';
427 END IF;
428 END Move_Indicator;
429
430 /************************************************************************************
431 -- API name : Reposition_Indicator
432 -- Type : Public
433 -- Function :
434 -- This API is used to reposition the indicators in a group
435 ************************************************************************************/
436 PROCEDURE Reposition_Indicator (
437 p_commit IN VARCHAR2 := FND_API.G_FALSE
438 , p_Indicator IN NUMBER
439 , p_New_Indicator_Group IN NUMBER
440 , p_New_Position IN NUMBER
441 , x_return_status OUT NOCOPY VARCHAR2
442 , x_msg_count OUT NOCOPY NUMBER
443 , x_msg_data OUT NOCOPY VARCHAR2
444 ) IS
445 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
446 l_Display_Order bsc_kpis_b.disp_order%TYPE := 1;
447 l_Current_Disp_Order bsc_kpis_b.disp_order%TYPE := 1;
448 CURSOR c_Group_Indicators IS
449 SELECT
450 indicator
451 FROM
452 bsc_kpis_b
453 WHERE
454 ind_group_id = p_New_Indicator_Group
455 AND prototype_flag <> 2
456 AND share_flag <> 2
457 AND BSC_BIS_KPI_CRUD_PUB.is_KPI_EndToEnd_KPI(short_name) <> 'T'
458 ORDER BY
459 disp_order,indicator;
460
461 CURSOR c_shared_obj(p_Indicator_id NUMBER) IS
462 SELECT
463 indicator
464 FROM
465 bsc_kpis_b
466 WHERE
467 source_indicator = p_Indicator_id
468 AND share_flag = 2
469 AND prototype_flag <> 2;
470
471 BEGIN
472
473 FND_MSG_PUB.Initialize;
474 x_return_status := FND_API.G_RET_STS_SUCCESS;
475
476 SAVEPOINT BscRepositionIndicatorSavePnt;
477
478 IF l_Display_Order = p_New_Position THEN
479 l_Display_Order := l_Display_Order + 1;
480 END IF;
481
482 FOR cInd IN c_Group_Indicators LOOP
483 IF p_Indicator = cInd.indicator THEN
484 l_Current_Disp_Order := p_New_Position;
485 ELSE
486 l_Current_Disp_Order := l_Display_Order;
487 l_Display_Order := l_Display_Order + 1;
488 IF l_Display_Order = p_New_Position THEN
489 l_Display_Order := l_Display_Order + 1;
490 END IF;
491 END IF;
492 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := cInd.Indicator;
493 BSC_KPI_PVT.Retrieve_Kpi(
494 p_commit => FND_API.G_FALSE
495 ,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
496 ,x_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
497 ,x_return_status => x_return_status
498 ,x_msg_count => x_msg_count
499 ,x_msg_data => x_msg_data
500 );
501 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
502 RAISE FND_API.G_EXC_ERROR;
503 END IF;
504
505 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Display_Order := l_Current_Disp_Order;
506 BSC_KPI_PVT.Update_Kpi(
507 p_commit => FND_API.G_FALSE
508 ,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
509 ,x_return_status => x_return_status
510 ,x_msg_count => x_msg_count
511 ,x_msg_data => x_msg_data
512 );
513 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
514 RAISE FND_API.G_EXC_ERROR;
515 END IF;
516
517 FOR cd IN c_shared_obj(cInd.indicator) LOOP
518 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := cd.Indicator;
519 BSC_KPI_PVT.Retrieve_Kpi(
520 p_commit => FND_API.G_FALSE
521 ,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
522 ,x_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
523 ,x_return_status => x_return_status
524 ,x_msg_count => x_msg_count
525 ,x_msg_data => x_msg_data
526 );
527 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
528 RAISE FND_API.G_EXC_ERROR;
529 END IF;
530
531 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Display_Order := l_Current_Disp_Order;
532 BSC_KPI_PVT.Update_Kpi(
533 p_commit => FND_API.G_FALSE
534 ,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
535 ,x_return_status => x_return_status
536 ,x_msg_count => x_msg_count
537 ,x_msg_data => x_msg_data
538 );
539 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
540 RAISE FND_API.G_EXC_ERROR;
541 END IF;
542 END LOOP;
543 END LOOP;
544
545 IF (p_commit = FND_API.G_TRUE) THEN
546 COMMIT;
547 END IF;
548
549 EXCEPTION
550 WHEN FND_API.G_EXC_ERROR THEN
551 ROLLBACK TO BscRepositionIndicatorSavePnt;
552 IF (x_msg_data IS NULL) THEN
553 FND_MSG_PUB.Count_And_Get
554 ( p_encoded => FND_API.G_FALSE
555 , p_count => x_msg_count
556 , p_data => x_msg_data
557 );
558 END IF;
559 x_return_status := FND_API.G_RET_STS_ERROR;
560 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
561 ROLLBACK TO BscRepositionIndicatorSavePnt;
562 IF (x_msg_data IS NULL) THEN
563 FND_MSG_PUB.Count_And_Get
564 ( p_encoded => FND_API.G_FALSE
565 , p_count => x_msg_count
566 , p_data => x_msg_data
567 );
568 END IF;
569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
570 WHEN NO_DATA_FOUND THEN
571 ROLLBACK TO BscRepositionIndicatorSavePnt;
572 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
573 IF (x_msg_data IS NOT NULL) THEN
574 x_msg_data := x_msg_data || ' -> BSC_COPY_INDICATOR_PUB.Reposition_Indicator ';
575 ELSE
576 x_msg_data := SQLERRM || ' at BSC_COPY_INDICATOR_PUB.Reposition_Indicator ';
577 END IF;
578 WHEN OTHERS THEN
579 ROLLBACK TO BscRepositionIndicatorSavePnt;
580 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
581 IF (x_msg_data IS NOT NULL) THEN
582 x_msg_data := x_msg_data || ' -> BSC_COPY_INDICATOR_PUB.Reposition_Indicator ';
583 ELSE
584 x_msg_data := SQLERRM || ' at BSC_COPY_INDICATOR_PUB.Reposition_Indicator ';
585 END IF;
586 END Reposition_Indicator;
587
588 /************************************************************************************
589 -- API name : Move_Indicator_UI_Wrap
590 -- Type : Public
591 -- Function :
592 -- This API is used to move an indicator from one indicator group to another
593 -- This can also be used to reposition the indicator with the same group
594 -- 1. Update Group properties if earlier group type is 1
595 -- (Color Box above objective label) and the kpi count in the objective
596 -- is already 1
597 -- 2. Update the Ind_Group_Id to the new group
598 ************************************************************************************/
599 PROCEDURE Move_Indicator_UI_Wrap (
600 p_commit IN VARCHAR2 := FND_API.G_FALSE
601 , p_Indicator IN NUMBER
602 , p_New_Indicator_Group IN NUMBER
603 , p_New_Position IN NUMBER
604 , p_Time_Stamp IN VARCHAR2 := NULL
605 , x_return_status OUT NOCOPY VARCHAR2
606 , x_msg_count OUT NOCOPY NUMBER
607 , x_msg_data OUT NOCOPY VARCHAR2
608 )
609 IS
610 l_old_group_id bsc_kpis_b.ind_group_id%TYPE;
611 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
612 CURSOR c_shared_obj IS
613 SELECT
614 indicator
615 FROM
616 bsc_kpis_b
617 WHERE
618 source_indicator = p_Indicator
619 AND share_flag = 2
620 AND prototype_flag <> 2;
621
622 BEGIN
623
624 FND_MSG_PUB.Initialize;
625 x_return_status := FND_API.G_RET_STS_SUCCESS;
626
627 SAVEPOINT BscMovIndicatorUIWrap;
628
629 BSC_BIS_LOCKS_PUB.Lock_Kpi
630 ( p_Kpi_Id => p_Indicator
631 , p_time_stamp => p_time_stamp
632 , p_Full_Lock_Flag => FND_API.G_FALSE
633 , x_return_status => x_return_status
634 , x_msg_count => x_msg_count
635 , x_msg_data => x_msg_data
636 );
637 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
639 END IF;
640
641 l_old_group_id := Get_Ind_Group_Id(p_Indicator);
642 IF l_old_group_id <> p_New_Indicator_Group THEN
643 -- A warning should be displayed in the UI
644 Update_Kpi_Group_Properties (
645 p_commit => FND_API.G_FALSE
646 ,p_New_Indicator_Group => p_New_Indicator_Group
647 ,x_return_status => x_return_status
648 ,x_msg_count => x_msg_count
649 ,x_msg_data => x_msg_data
650 );
651 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
652 RAISE FND_API.G_EXC_ERROR;
653 END IF;
654
655 Move_Indicator (
656 p_commit => FND_API.G_FALSE
657 ,p_Indicator => p_Indicator
658 ,p_New_Indicator_Group => p_New_Indicator_Group
659 ,x_return_status => x_return_status
660 ,x_msg_count => x_msg_count
661 ,x_msg_data => x_msg_data
662 );
663 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
664 RAISE FND_API.G_EXC_ERROR;
665 END IF;
666
667 FOR cd in c_shared_obj LOOP
668 Move_Indicator (
669 p_commit => FND_API.G_FALSE
670 ,p_Indicator => cd.indicator
671 ,p_New_Indicator_Group => p_New_Indicator_Group
672 ,x_return_status => x_return_status
673 ,x_msg_count => x_msg_count
674 ,x_msg_data => x_msg_data
675 );
676 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
677 RAISE FND_API.G_EXC_ERROR;
678 END IF;
679 END LOOP;
680 END IF;
681
682 Reposition_Indicator (
683 p_commit => FND_API.G_FALSE
684 ,p_Indicator => p_Indicator
685 ,p_New_Indicator_Group => p_New_Indicator_Group
686 ,p_New_Position => p_New_Position
687 ,x_return_status => x_return_status
688 ,x_msg_count => x_msg_count
689 ,x_msg_data => x_msg_data
690 );
691 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
692 RAISE FND_API.G_EXC_ERROR;
693 END IF;
694
695 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := p_Indicator;
696 BSC_KPI_PUB.Update_Kpi_Time_Stamp (
697 p_commit => FND_API.G_FALSE
698 ,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
699 ,x_return_status => x_return_status
700 ,x_msg_count => x_msg_count
701 ,x_msg_data => x_msg_data
702 );
703 FOR cd in c_shared_obj LOOP
704 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := cd.Indicator;
705 BSC_KPI_PUB.Update_Kpi_Time_Stamp (
706 p_commit => FND_API.G_FALSE
707 ,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
708 ,x_return_status => x_return_status
709 ,x_msg_count => x_msg_count
710 ,x_msg_data => x_msg_data
711 );
712 END LOOP;
713
714 IF (p_commit = FND_API.G_TRUE) THEN
715 COMMIT;
716 END IF;
717
718 EXCEPTION
719 WHEN FND_API.G_EXC_ERROR THEN
720 ROLLBACK TO BscMovIndicatorUIWrap;
721 IF (x_msg_data IS NULL) THEN
722 FND_MSG_PUB.Count_And_Get
723 ( p_encoded => FND_API.G_FALSE
724 , p_count => x_msg_count
725 , p_data => x_msg_data
726 );
727 END IF;
728 x_return_status := FND_API.G_RET_STS_ERROR;
729 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
730 ROLLBACK TO BscMovIndicatorUIWrap;
731 IF (x_msg_data IS NULL) THEN
732 FND_MSG_PUB.Count_And_Get
733 ( p_encoded => FND_API.G_FALSE
734 , p_count => x_msg_count
735 , p_data => x_msg_data
736 );
737 END IF;
738 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
739 WHEN NO_DATA_FOUND THEN
740 ROLLBACK TO BscMovIndicatorUIWrap;
741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
742 IF (x_msg_data IS NOT NULL) THEN
743 x_msg_data := x_msg_data || ' -> BSC_COPY_INDICATOR_PUB.Move_Indicator_UI_Wrap ';
744 ELSE
745 x_msg_data := SQLERRM || ' at BSC_COPY_INDICATOR_PUB.Move_Indicator_UI_Wrap ';
746 END IF;
747 WHEN OTHERS THEN
748 ROLLBACK TO BscMovIndicatorUIWrap;
749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
750 IF (x_msg_data IS NOT NULL) THEN
751 x_msg_data := x_msg_data || ' -> BSC_COPY_INDICATOR_PUB.Move_Indicator_UI_Wrap ';
752 ELSE
753 x_msg_data := SQLERRM || ' at BSC_COPY_INDICATOR_PUB.Move_Indicator_UI_Wrap ';
754 END IF;
755 END Move_Indicator_UI_Wrap;
756
757
758 /************************************************************************************
759 -- API name : Validate_Indicator_Copy
760 -- Type : Public
761 -- Function :
762 -- This API is used to validate whether copy is allowed or not
763 -- Will check the following conditions
764 -- 1. Whether the indicator is an EDW type
765 -- 2. Whether the indicator has PMF measures attached
766 -- Both the above conditions will be invalid if the copy is across systems
767 ************************************************************************************/
768 PROCEDURE Validate_Indicator_Copy (
769 p_Source_Indicator IN NUMBER
770 , x_return_status OUT NOCOPY VARCHAR2
771 , x_msg_count OUT NOCOPY NUMBER
772 , x_msg_data OUT NOCOPY VARCHAR2
773 ) IS
774
775 l_EDW_Flag bsc_kpis_b.edw_flag%TYPE;
776 l_PMF_Meas_Cnt NUMBER := 0;
777 l_sql VARCHAR2(32000);
778 TYPE c_cur_type IS REF CURSOR;
779 c_cursor c_cur_type;
780 l_indicator_type bsc_kpis_b.indicator_type%TYPE;
781 l_config_type bsc_kpis_b.config_type%TYPE;
782 BEGIN
783
784 FND_MSG_PUB.Initialize;
785 x_return_status := FND_API.G_RET_STS_SUCCESS;
786
787 SAVEPOINT BscValIndCopyUIWrap;
788
789 IF BSC_DESIGNER_PVT.g_DbLink_Name IS NOT NULL THEN
790
791 l_sql := BSC_DESIGNER_PVT.Format_DbLink_String(' SELECT NVL(edw_flag,0) FROM bsc_kpis_b');
792 l_sql := l_sql || ' WHERE indicator = :1';
793 OPEN c_cursor FOR l_sql USING p_Source_Indicator;
794 FETCH c_cursor INTO l_EDW_Flag;
795 CLOSE c_cursor;
796 IF l_EDW_Flag <> 0 THEN
797 FND_MESSAGE.SET_NAME('BSC','BSC_CAN_NOT_COPY_EDW_KPI');
798 FND_MSG_PUB.ADD;
799 RAISE FND_API.G_EXC_ERROR;
800 END IF;
801
802 l_sql := BSC_DESIGNER_PVT.Format_DbLink_String('SELECT indicator_type,config_type FROM bsc_kpis_b');
803 l_sql := l_sql || ' WHERE indicator = :1';
804 OPEN c_cursor FOR l_sql USING p_Source_Indicator;
805 FETCH c_cursor INTO l_indicator_type,l_config_type;
806 CLOSE c_cursor;
807
808 IF NOT (l_indicator_type = 1 AND l_config_type = 7) THEN
809 l_sql := BSC_DESIGNER_PVT.Format_DbLink_String(' SELECT COUNT(1) FROM bsc_kpi_analysis_measures_b');
810 l_sql := l_sql || BSC_DESIGNER_PVT.Format_DbLink_String(' km ,bsc_sys_datasets_b');
811 l_sql := l_sql || ' ds WHERE km.indicator = :1 AND ds.dataset_id = km.dataset_id AND ds.source = :2';
812 OPEN c_cursor FOR l_sql USING p_Source_Indicator, 'PMF';
813 FETCH c_cursor INTO l_PMF_Meas_Cnt;
814 CLOSE c_cursor;
815 IF l_PMF_Meas_Cnt > 0 THEN
816 FND_MESSAGE.SET_NAME('BSC','BSC_CAN_NOT_COPY_PMF_KPI');
817 FND_MSG_PUB.ADD;
818 RAISE FND_API.G_EXC_ERROR;
819 END IF;
820 END IF;
821
822 END IF;
823
824 EXCEPTION
825 WHEN FND_API.G_EXC_ERROR THEN
826 ROLLBACK TO BscValIndCopyUIWrap;
827 IF (x_msg_data IS NULL) THEN
828 FND_MSG_PUB.Count_And_Get
829 ( p_encoded => FND_API.G_FALSE
830 , p_count => x_msg_count
831 , p_data => x_msg_data
832 );
833 END IF;
834 x_return_status := FND_API.G_RET_STS_ERROR;
835 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
836 ROLLBACK TO BscValIndCopyUIWrap;
837 IF (x_msg_data IS NULL) THEN
838 FND_MSG_PUB.Count_And_Get
839 ( p_encoded => FND_API.G_FALSE
840 , p_count => x_msg_count
841 , p_data => x_msg_data
842 );
843 END IF;
844 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
845 WHEN NO_DATA_FOUND THEN
846 ROLLBACK TO BscValIndCopyUIWrap;
847 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
848 IF (x_msg_data IS NOT NULL) THEN
849 x_msg_data := x_msg_data || ' -> BSC_COPY_INDICATOR_PUB.Validate_Indicator_Copy ';
850 ELSE
851 x_msg_data := SQLERRM || ' at BSC_COPY_INDICATOR_PUB.Validate_Indicator_Copy ';
852 END IF;
853 WHEN OTHERS THEN
854 ROLLBACK TO BscValIndCopyUIWrap;
855 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
856 IF (x_msg_data IS NOT NULL) THEN
857 x_msg_data := x_msg_data || ' -> BSC_COPY_INDICATOR_PUB.Validate_Indicator_Copy ';
858 ELSE
859 x_msg_data := SQLERRM || ' at BSC_COPY_INDICATOR_PUB.Validate_Indicator_Copy ';
860 END IF;
861 END Validate_Indicator_Copy;
862
863 /************************************************************************************
864 -- API name : Lock_Target_Entities
865 -- Type : Private
866 -- Function :
867 -- All the measures, dimension objects, dimensions , periodicities, calendars
868 -- that will be used have to be locked
869 ************************************************************************************/
870 PROCEDURE Lock_Target_Entities (
871 p_DataSet_Map IN FND_TABLE_OF_NUMBER
872 , p_DimLevel_Map IN FND_TABLE_OF_NUMBER
873 , p_DimGroup_Map IN FND_TABLE_OF_NUMBER
874 , p_Periodicity_Map IN FND_TABLE_OF_NUMBER
875 , p_Calendar IN NUMBER
876 , p_Time_Stamp IN VARCHAR2 := NULL
877 , x_return_status OUT NOCOPY VARCHAR2
878 , x_msg_count OUT NOCOPY NUMBER
879 , x_msg_data OUT NOCOPY VARCHAR2
880 ) IS
881 i NUMBER;
882 l_sql VARCHAR2(32000);
883 BEGIN
884
885 FND_MSG_PUB.Initialize;
886 x_return_status := FND_API.G_RET_STS_SUCCESS;
887
888 FOR i IN 1..p_DataSet_Map.COUNT LOOP
889 BSC_BIS_LOCKS_PUB.Lock_Dataset (
890 p_dataset_id => p_DataSet_Map(i)
891 ,p_time_stamp => p_Time_Stamp
892 ,x_return_status => x_return_status
893 ,x_msg_count => x_msg_count
894 ,x_msg_data => x_msg_data
895 );
896 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
897 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
898 END IF;
899 END LOOP;
900
901 FOR i IN 1..p_DimLevel_Map.COUNT LOOP
902 BSC_BIS_LOCKS_PUB.Lock_Dim_Level (
903 p_dim_level_id => p_DimLevel_Map(i)
904 ,p_time_stamp => p_Time_Stamp
905 ,x_return_status => x_return_status
906 ,x_msg_count => x_msg_count
907 ,x_msg_data => x_msg_data
908 );
909 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
911 END IF;
912 END LOOP;
913
914 FOR i IN 1..p_DimGroup_Map.COUNT LOOP
915 BSC_BIS_LOCKS_PUB.Lock_Dim_Group (
916 p_dim_group_id => p_DimGroup_Map(i)
917 ,p_time_stamp => p_Time_Stamp
918 ,x_return_status => x_return_status
919 ,x_msg_count => x_msg_count
920 ,x_msg_data => x_msg_data
921 );
922 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
923 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
924 END IF;
925 END LOOP;
926
927 FOR i IN 1..p_Periodicity_Map.COUNT LOOP
928 BSC_BIS_LOCKS_PUB.Lock_Periodicity (
929 p_Periodicity_Id => p_Periodicity_Map(i)
930 ,p_time_stamp => p_Time_Stamp
931 ,x_return_status => x_return_status
932 ,x_msg_count => x_msg_count
933 ,x_msg_data => x_msg_data
934 );
935 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
936 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
937 END IF;
938 END LOOP;
939
940 BSC_BIS_LOCKS_PUB.Lock_Calendar (
941 p_Calendar_Id => p_Calendar
942 ,p_time_stamp => p_Time_Stamp
943 ,x_return_status => x_return_status
944 ,x_msg_count => x_msg_count
945 ,x_msg_data => x_msg_data
946 );
947 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
948 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
949 END IF;
950
951 EXCEPTION
952 WHEN OTHERS THEN
953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
954 IF (x_msg_data IS NOT NULL) THEN
955 x_msg_data := x_msg_data || ' -> BSC_COPY_INDICATOR_PUB.Lock_Target_Entities ';
956 ELSE
957 x_msg_data := SQLERRM || ' at BSC_COPY_INDICATOR_PUB.Lock_Target_Entities ';
958 END IF;
959 END Lock_Target_Entities;
960
961 /************************************************************************************
962 Function : Create_Kpi_Access_For_Resp
963 Description : This function will assign a objectitve to a given responsibility
964 ***********************************************************************************/
965 PROCEDURE Create_Kpi_Access_Wrap(
966 p_commit IN VARCHAR2 := FND_API.G_FALSE
967 ,p_Comma_Sep_Resposibility_Key IN VARCHAR2
968 ,p_Indicator_Id IN NUMBER
969 ,x_return_status OUT NOCOPY VARCHAR2
970 ,x_msg_count OUT NOCOPY NUMBER
971 ,x_msg_data OUT NOCOPY VARCHAR2
972 )IS
973 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
974
975 BEGIN
976
977 FND_MSG_PUB.Initialize;
978 x_return_status := FND_API.G_RET_STS_SUCCESS;
979
980 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := p_Indicator_Id;
981 l_Bsc_Kpi_Entity_Rec.Bsc_Responsibility_Id := FND_GLOBAL.RESP_ID;
982 l_Bsc_Kpi_Entity_Rec.Created_By := FND_GLOBAL.USER_ID;
983 l_Bsc_Kpi_Entity_Rec.Last_Updated_By := FND_GLOBAL.USER_ID;
984 l_Bsc_Kpi_Entity_Rec.Last_Update_Login := FND_GLOBAL.LOGIN_ID;
985 l_Bsc_Kpi_Entity_Rec.Bsc_Resp_Start_Date := SYSDATE;
986 l_Bsc_Kpi_Entity_Rec.Bsc_Resp_End_Date := NULL;
987
988 BSC_KPI_PUB.Create_Kpi_Access_For_Resp
989 ( p_commit => p_commit
990 , p_Comma_Sep_Resposibility_Key => p_Comma_Sep_Resposibility_Key
991 , p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
992 , x_return_status => x_return_status
993 , x_msg_count => x_msg_count
994 , x_msg_data => x_msg_data
995 );
996 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
998 END IF;
999
1000 EXCEPTION
1001 WHEN FND_API.G_EXC_ERROR THEN
1002 IF (x_msg_data IS NULL) THEN
1003 FND_MSG_PUB.Count_And_Get
1004 ( p_encoded => FND_API.G_FALSE
1005 , p_count => x_msg_count
1006 , p_data => x_msg_data
1007 );
1008 END IF;
1009 x_return_status := FND_API.G_RET_STS_ERROR;
1010 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1011 IF (x_msg_data IS NULL) THEN
1012 FND_MSG_PUB.Count_And_Get
1013 ( p_encoded => FND_API.G_FALSE
1014 , p_count => x_msg_count
1015 , p_data => x_msg_data
1016 );
1017 END IF;
1018 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1019 WHEN NO_DATA_FOUND THEN
1020 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1021 IF (x_msg_data IS NOT NULL) THEN
1022 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Create_Kpi_Access_Wrap ';
1023 ELSE
1024 x_msg_data := SQLERRM||' at BSC_COPY_INDICATOR_PUB.Create_Kpi_Access_Wrap ';
1025 END IF;
1026 WHEN OTHERS THEN
1027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1028 IF (x_msg_data IS NOT NULL) THEN
1029 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Create_Kpi_Access_Wrap ';
1030 ELSE
1031 x_msg_data := SQLERRM||' AT BSC_COPY_INDICATOR_PUB.Create_Kpi_Access_Wrap ';
1032 END IF;
1033 END Create_Kpi_Access_Wrap;
1034
1035
1036 /************************************************************************************
1037 -- API name : Copy_Analysis_Measures
1038 -- Type : Private
1039 -- Function :
1040 -- Maps the datasets to the analysis measures of the target indicator
1041 ************************************************************************************/
1042
1043 PROCEDURE Copy_Analysis_Measures(
1044 p_commit IN VARCHAR2 := FND_API.G_FALSE
1045 , p_Source_Indicator IN NUMBER
1046 , p_Target_Indicator IN NUMBER
1047 , p_Old_DataSet_Map IN FND_TABLE_OF_NUMBER
1048 , p_New_DataSet_Map IN FND_TABLE_OF_NUMBER
1049 , x_return_status OUT NOCOPY VARCHAR2
1050 , x_msg_count OUT NOCOPY NUMBER
1051 , x_msg_data OUT NOCOPY VARCHAR2
1052 )IS
1053 TYPE c_cur_type IS REF CURSOR;
1054 c_Ana_Meas c_cur_type;
1055 l_analysis_option0 bsc_kpi_analysis_measures_b.analysis_option0%TYPE;
1056 l_analysis_option1 bsc_kpi_analysis_measures_b.analysis_option1%TYPE;
1057 l_analysis_option2 bsc_kpi_analysis_measures_b.analysis_option2%TYPE;
1058 l_series_id bsc_kpi_analysis_measures_b.series_id%TYPE;
1059 l_dataset_id bsc_kpi_analysis_measures_b.dataset_id%TYPE;
1060
1061 l_sql VARCHAR2(32000);
1062
1063 BEGIN
1064
1065 FND_MSG_PUB.Initialize;
1066 x_return_status := FND_API.G_RET_STS_SUCCESS;
1067 SAVEPOINT BscCopyIndicAnaMeasPub;
1068
1069 l_sql := 'SELECT analysis_option0 ,analysis_option1 ,analysis_option2,series_id';
1070 l_sql := l_sql || BSC_DESIGNER_PVT.Format_DbLink_String(',dataset_id FROM bsc_kpi_analysis_measures_b');
1071 l_sql := l_sql || 'WHERE indicator = :1 ORDER BY analysis_option0';
1072 l_sql := l_sql || ',analysis_option1 ,analysis_option2 ,series_id';
1073 OPEN c_Ana_Meas FOR l_sql USING p_Source_Indicator;
1074 LOOP
1075 FETCH c_Ana_Meas INTO l_analysis_option0,l_analysis_option1, l_analysis_option2, l_series_id,l_dataset_id;
1076 EXIT WHEN c_Ana_Meas%NOTFOUND;
1077 FOR i IN 1..p_Old_DataSet_Map.COUNT LOOP
1078 IF p_Old_DataSet_Map(i) = l_dataset_id THEN
1079 UPDATE
1080 bsc_kpi_analysis_measures_b
1081 SET
1082 dataset_id = p_New_DataSet_Map(i)
1083 WHERE
1084 indicator = p_Target_Indicator
1085 AND analysis_option0 = l_analysis_option0
1086 AND analysis_option1 = l_analysis_option1
1087 AND analysis_option2 = l_analysis_option2
1088 AND series_id = l_series_id;
1089
1090 IF p_Old_DataSet_Map(i) <> p_New_DataSet_Map(i) AND
1091 BSC_DESIGNER_PVT.g_DbLink_Name IS NULL THEN
1092 UPDATE bsc_kpi_analysis_measures_tl km
1093 SET name = (SELECT d.name FROM bsc_sys_datasets_tl d WHERE
1094 d.dataset_id = p_New_DataSet_Map(i) AND d.language = km.language),
1095 help = (SELECT d.help FROM bsc_sys_datasets_tl d WHERE
1096 d.dataset_id = p_New_DataSet_Map(i) AND d.language = km.language)
1097 WHERE indicator = p_Target_Indicator;
1098 END IF;
1099 EXIT;
1100 END IF;
1101 END LOOP;
1102 END LOOP;
1103 CLOSE c_Ana_Meas;
1104
1105 IF (p_commit = FND_API.G_TRUE) THEN
1106 COMMIT;
1107 END IF;
1108
1109 EXCEPTION
1110 WHEN FND_API.G_EXC_ERROR THEN
1111 ROLLBACK TO BscCopyIndicAnaMeasPub;
1112 IF (x_msg_data IS NULL) THEN
1113 FND_MSG_PUB.Count_And_Get
1114 ( p_encoded => FND_API.G_FALSE
1115 , p_count => x_msg_count
1116 , p_data => x_msg_data
1117 );
1118 END IF;
1119 x_return_status := FND_API.G_RET_STS_ERROR;
1120 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1121 ROLLBACK TO BscCopyIndicAnaMeasPub;
1122 IF (x_msg_data IS NULL) THEN
1123 FND_MSG_PUB.Count_And_Get
1124 ( p_encoded => FND_API.G_FALSE
1125 , p_count => x_msg_count
1126 , p_data => x_msg_data
1127 );
1128 END IF;
1129 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1130 WHEN NO_DATA_FOUND THEN
1131 ROLLBACK TO BscCopyIndicAnaMeasPub;
1132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1133 IF (x_msg_data IS NOT NULL) THEN
1134 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Copy_Analysis_Measures ';
1135 ELSE
1136 x_msg_data := SQLERRM||' at BSC_COPY_INDICATOR_PUB.Copy_Analysis_Measures ';
1137 END IF;
1138 WHEN OTHERS THEN
1139 ROLLBACK TO BscCopyIndicAnaMeasPub;
1140 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1141 IF (x_msg_data IS NOT NULL) THEN
1142 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Copy_Analysis_Measures ';
1143 ELSE
1144 x_msg_data := SQLERRM||' AT BSC_COPY_INDICATOR_PUB.Copy_Analysis_Measures ';
1145 END IF;
1146 END Copy_Analysis_Measures;
1147
1148 /************************************************************************************
1149 -- API name : Update_Annual_Current_Period
1150 -- Type : Private
1151 -- Function :
1152 -- This checks whether there is any annual periodicity in the objective.
1153 -- It will check the current period for the new periodicity and
1154 ************************************************************************************/
1155
1156 PROCEDURE Update_Annual_Current_Period(
1157 p_commit IN VARCHAR2 := FND_API.G_FALSE
1158 , p_Target_Indicator IN NUMBER
1159 , x_return_status OUT NOCOPY VARCHAR2
1160 , x_msg_count OUT NOCOPY NUMBER
1161 , x_msg_data OUT NOCOPY VARCHAR2
1162 )IS
1163
1164 CURSOR c_Periodicity IS
1165 SELECT
1166 sp.periodicity_id ,
1167 sc.fiscal_year
1168 FROM
1169 bsc_kpi_periodicities kp,
1170 bsc_sys_periodicities_vl sp,
1171 bsc_sys_calendars_vl sc
1172 WHERE
1173 kp.indicator = p_Target_Indicator AND
1174 sp.periodicity_id = kp.periodicity_id AND
1175 sp.periodicity_type = 1 AND
1176 sc.calendar_id = sp.calendar_id ;
1177
1178 BEGIN
1179
1180 FND_MSG_PUB.Initialize;
1181 x_return_status := FND_API.G_RET_STS_SUCCESS;
1182 SAVEPOINT BscUpdAnnCurPeriod;
1183
1184 FOR cd IN c_Periodicity LOOP
1185 UPDATE
1186 bsc_kpi_periodicities
1187 SET
1188 current_period = cd.fiscal_year
1189 WHERE
1190 indicator = p_Target_Indicator AND
1191 periodicity_id = cd.periodicity_id ;
1192 END LOOP;
1193
1194 IF (p_commit = FND_API.G_TRUE) THEN
1195 COMMIT;
1196 END IF;
1197
1198 EXCEPTION
1199 WHEN FND_API.G_EXC_ERROR THEN
1200 ROLLBACK TO BscUpdAnnCurPeriod;
1201 IF (x_msg_data IS NULL) THEN
1202 FND_MSG_PUB.Count_And_Get
1203 ( p_encoded => FND_API.G_FALSE
1204 , p_count => x_msg_count
1205 , p_data => x_msg_data
1206 );
1207 END IF;
1208 x_return_status := FND_API.G_RET_STS_ERROR;
1209 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1210 ROLLBACK TO BscUpdAnnCurPeriod;
1211 IF (x_msg_data IS NULL) THEN
1212 FND_MSG_PUB.Count_And_Get
1213 ( p_encoded => FND_API.G_FALSE
1214 , p_count => x_msg_count
1215 , p_data => x_msg_data
1216 );
1217 END IF;
1218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1219 WHEN NO_DATA_FOUND THEN
1220 ROLLBACK TO BscUpdAnnCurPeriod;
1221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1222 IF (x_msg_data IS NOT NULL) THEN
1223 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Update_Annual_Current_Period ';
1224 ELSE
1225 x_msg_data := SQLERRM||' at BSC_COPY_INDICATOR_PUB.Update_Annual_Current_Period ';
1226 END IF;
1227 WHEN OTHERS THEN
1228 ROLLBACK TO BscUpdAnnCurPeriod;
1229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1230 IF (x_msg_data IS NOT NULL) THEN
1231 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Update_Annual_Current_Period ';
1232 ELSE
1233 x_msg_data := SQLERRM||' AT BSC_COPY_INDICATOR_PUB.Update_Annual_Current_Period ';
1234 END IF;
1235 END Update_Annual_Current_Period;
1236
1237 /************************************************************************************
1238 -- API name : Copy_Periodicities
1239 -- Type : Private
1240 -- Function :
1241 -- Maps the periodicities from the source objective to periodicities in target
1242 -- system
1243 ************************************************************************************/
1244
1245 PROCEDURE Copy_Periodicities(
1246 p_commit IN VARCHAR2 := FND_API.G_FALSE
1247 , p_Source_Indicator IN NUMBER
1248 , p_Target_Indicator IN NUMBER
1249 , p_Target_Calendar IN NUMBER
1250 , p_Old_Periodicities IN FND_TABLE_OF_NUMBER
1251 , p_New_Periodicities IN FND_TABLE_OF_NUMBER
1252 , x_return_status OUT NOCOPY VARCHAR2
1253 , x_msg_count OUT NOCOPY NUMBER
1254 , x_msg_data OUT NOCOPY VARCHAR2
1255 )IS
1256 l_Source_Per_Type bsc_sys_periodicities.periodicity_type%TYPE;
1257 l_Target_Per_Type bsc_sys_periodicities.periodicity_type%TYPE;
1258 l_sql VARCHAR2(32000);
1259 TYPE c_cur_type IS REF CURSOR;
1260 c_cursor c_cur_type;
1261 l_Source_Calendar bsc_kpis_b.calendar_id%TYPE;
1262 l_Default_Periodicity bsc_kpis_b.periodicity_id%TYPE;
1263 l_New_Periodicity bsc_kpis_b.periodicity_id%TYPE := NULL;
1264 l_Count NUMBER := 0;
1265 l_Deleted_Periodicities FND_TABLE_OF_NUMBER;
1266 l_Periodicity_Id bsc_sys_periodicities.periodicity_id%TYPE;
1267 l_Found BOOLEAN := FALSE;
1268 BEGIN
1269
1270 FND_MSG_PUB.Initialize;
1271 x_return_status := FND_API.G_RET_STS_SUCCESS;
1272 SAVEPOINT BscCopyIndicPeriodsPub;
1273
1274 IF p_New_Periodicities.COUNT = 0 THEN
1275 Update_Annual_Current_Period (
1276 p_commit => FND_API.G_FALSE
1277 ,p_Target_Indicator => p_Target_Indicator
1278 ,x_return_status => x_return_status
1279 ,x_msg_count => x_msg_count
1280 ,x_msg_data => x_msg_data
1281 );
1282 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1284 END IF;
1285 RETURN;
1286 END IF;
1287
1288 l_sql := BSC_DESIGNER_PVT.Format_DbLink_String('SELECT periodicity_id FROM bsc_kpi_periodicities');
1289 l_sql := l_sql || 'WHERE indicator = :1';
1290 l_Deleted_Periodicities := FND_TABLE_OF_NUMBER();
1291 OPEN c_cursor FOR l_sql USING p_Source_Indicator;
1292 LOOP
1293 FETCH c_cursor INTO l_Periodicity_Id;
1294 EXIT WHEN c_cursor%NOTFOUND;
1295 l_Found := FALSE;
1296 FOR i IN 1..p_Old_Periodicities.COUNT LOOP
1297 IF p_Old_Periodicities(i) = l_Periodicity_Id THEN
1298 l_Found := TRUE;
1299 END IF;
1300 END LOOP;
1301 IF NOT l_Found THEN
1302 l_Deleted_Periodicities.EXTEND(1);
1303 l_Deleted_Periodicities(l_Deleted_Periodicities.LAST) := l_Periodicity_Id;
1304 END IF;
1305 END LOOP;
1306 CLOSE c_cursor;
1307
1308 IF l_Deleted_Periodicities.COUNT > 0 THEN
1309 l_sql := ' DELETE FROM bsc_kpi_periodicities';
1310 l_sql := l_sql || ' WHERE indicator = :1 AND periodicity_id IN (' ;
1311 FOR i IN 1..l_Deleted_Periodicities.COUNT LOOP
1312 l_sql := l_sql || l_Deleted_Periodicities(i) || ',';
1313 END LOOP;
1314 l_sql := SUBSTR(l_sql, 0, LENGTH(l_sql) - 1);
1315 l_sql := l_sql || ')';
1316 EXECUTE IMMEDIATE l_sql USING p_Target_Indicator;
1317 END IF;
1318
1319 FOR i IN 1..p_Old_Periodicities.COUNT LOOP
1320 -- If it is mapped to some other periodicity
1321 IF p_Old_Periodicities(i) <> p_New_Periodicities(i) THEN
1322 UPDATE
1323 bsc_kpi_periodicities
1324 SET
1325 periodicity_id = p_New_Periodicities(i) ,
1326 display_order = (i - 1)
1327 WHERE
1328 indicator = p_Target_Indicator AND
1329 periodicity_id = p_Old_Periodicities(i);
1330 END IF;
1331
1332 l_sql := BSC_DESIGNER_PVT.Format_DbLink_String('SELECT calendar_id FROM bsc_kpis_b');
1333 l_sql := l_sql || 'WHERE indicator = :1';
1334
1335 OPEN c_cursor FOR l_sql USING p_Source_Indicator;
1336 FETCH c_cursor INTO l_Source_Calendar;
1337 CLOSE c_cursor;
1338
1339 l_sql := BSC_DESIGNER_PVT.Format_DbLink_String('SELECT periodicity_type FROM bsc_sys_periodicities');
1340 l_sql := l_sql || 'WHERE calendar_id = :1 AND periodicity_id = :2 ';
1341
1342 OPEN c_cursor FOR l_sql USING l_Source_Calendar, p_Old_Periodicities(i);
1343 FETCH c_cursor INTO l_Source_Per_Type;
1344 CLOSE c_cursor;
1345
1346 SELECT
1347 periodicity_type
1348 INTO
1349 l_Target_Per_Type
1350 FROM
1351 bsc_sys_periodicities
1352 WHERE
1353 calendar_id = p_Target_Calendar AND
1354 periodicity_id = p_New_Periodicities(i);
1355
1356
1357 IF l_Source_Per_Type <> l_Target_Per_Type THEN
1358 l_sql := ' UPDATE bsc_kpi_periodicities';
1359 l_sql := l_sql || ' SET viewport_flag = 0';
1360 IF l_Target_Per_Type = 1 THEN
1361 l_sql := l_sql || ' , num_of_years = 2 , previous_years = 1 ';
1362 ELSE
1363 l_sql := l_sql || ' , current_period = 1';
1364 IF l_Source_Per_Type = 1 THEN
1365 l_sql := l_sql || ' , num_of_years = 0 , previous_years = 0 ';
1366 END IF;
1367 END IF;
1368 l_sql := l_sql || 'WHERE indicator = :1 AND periodicity_id = :2';
1369
1370 EXECUTE IMMEDIATE l_sql USING p_Target_Indicator,p_New_Periodicities(i);
1371 END IF;
1372 END LOOP;
1373
1374 Update_Annual_Current_Period(
1375 p_commit => FND_API.G_FALSE
1376 ,p_Target_Indicator => p_Target_Indicator
1377 ,x_return_status => x_return_status
1378 ,x_msg_count => x_msg_count
1379 ,x_msg_data => x_msg_data
1380 );
1381 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1382 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1383 END IF;
1384
1385
1386 SELECT
1387 periodicity_id
1388 INTO
1389 l_Default_Periodicity
1390 FROM
1391 bsc_kpis_b
1392 WHERE
1393 indicator = p_Target_Indicator;
1394
1395 FOR i IN 1..p_Old_Periodicities.COUNT LOOP
1396 IF p_Old_Periodicities(i) = l_Default_Periodicity THEN
1397 l_New_Periodicity := p_New_Periodicities(i);
1398 END IF;
1399 END LOOP;
1400
1401 IF l_New_Periodicity IS NULL THEN
1402 SELECT
1403 periodicity_id
1404 INTO
1405 l_New_Periodicity
1406 FROM
1407 bsc_kpi_periodicities
1408 WHERE
1409 indicator = p_Target_Indicator AND
1410 ROWNUM < 2
1411 ORDER BY
1412 display_order;
1413 END IF;
1414
1415 UPDATE
1416 bsc_kpis_b
1417 SET
1418 periodicity_id = l_New_Periodicity ,
1419 calendar_id = p_Target_Calendar
1420 WHERE
1421 indicator = p_Target_Indicator;
1422
1423
1424 IF (p_commit = FND_API.G_TRUE) THEN
1425 COMMIT;
1426 END IF;
1427
1428 EXCEPTION
1429 WHEN FND_API.G_EXC_ERROR THEN
1430 ROLLBACK TO BscCopyIndicPeriodsPub;
1431 IF (x_msg_data IS NULL) THEN
1432 FND_MSG_PUB.Count_And_Get
1433 ( p_encoded => FND_API.G_FALSE
1434 , p_count => x_msg_count
1435 , p_data => x_msg_data
1436 );
1437 END IF;
1438 x_return_status := FND_API.G_RET_STS_ERROR;
1439 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1440 ROLLBACK TO BscCopyIndicPeriodsPub;
1441 IF (x_msg_data IS NULL) THEN
1442 FND_MSG_PUB.Count_And_Get
1443 ( p_encoded => FND_API.G_FALSE
1444 , p_count => x_msg_count
1445 , p_data => x_msg_data
1446 );
1447 END IF;
1448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1449 WHEN NO_DATA_FOUND THEN
1450 ROLLBACK TO BscCopyIndicPeriodsPub;
1451 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1452 IF (x_msg_data IS NOT NULL) THEN
1453 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Copy_Periodicities ';
1454 ELSE
1455 x_msg_data := SQLERRM||' at BSC_COPY_INDICATOR_PUB.Copy_Periodicities ';
1456 END IF;
1457 WHEN OTHERS THEN
1458 ROLLBACK TO BscCopyIndicPeriodsPub;
1459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1460 IF (x_msg_data IS NOT NULL) THEN
1461 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Copy_Periodicities ';
1462 ELSE
1463 x_msg_data := SQLERRM||' AT BSC_COPY_INDICATOR_PUB.Copy_Periodicities ';
1464 END IF;
1465 END Copy_Periodicities;
1466
1467
1468 /************************************************************************************
1469 -- API name : Update_Bsc_Kpi_Props
1470 -- Type : Private
1471 -- Function :
1472 --
1473 ************************************************************************************/
1474
1475 PROCEDURE Update_Bsc_Kpi_Props(
1476 p_commit IN VARCHAR2 := FND_API.G_FALSE
1477 , p_Target_Indicator IN NUMBER
1478 , p_Property_code IN VARCHAR2
1479 , p_Property_value IN NUMBER
1480 , x_return_status OUT NOCOPY VARCHAR2
1481 , x_msg_count OUT NOCOPY NUMBER
1482 , x_msg_data OUT NOCOPY VARCHAR2
1483 )IS
1484 l_Count NUMBER := 0;
1485 BEGIN
1486
1487 FND_MSG_PUB.Initialize;
1488 x_return_status := FND_API.G_RET_STS_SUCCESS;
1489 SAVEPOINT BscCopyUpdKpiProps;
1490
1491 l_Count := 0;
1492 SELECT
1493 COUNT(1)
1494 INTO
1495 l_Count
1496 FROM
1497 bsc_kpi_properties
1498 WHERE
1499 indicator = p_Target_Indicator AND
1500 UPPER(property_code) = p_Property_code ;
1501
1502 IF l_Count = 0 THEN
1503 INSERT INTO bsc_kpi_properties (
1504 indicator
1505 ,property_code
1506 ,property_value)
1507 VALUES
1508 (p_Target_Indicator
1509 ,p_Property_code
1510 ,p_Property_value);
1511 ELSE
1512 UPDATE
1513 bsc_kpi_properties
1514 SET
1515 property_value = p_Property_value
1516 WHERE
1517 indicator = p_Target_Indicator AND
1518 property_code = p_Property_code;
1519 END IF;
1520
1521 IF (p_commit = FND_API.G_TRUE) THEN
1522 COMMIT;
1523 END IF;
1524
1525 EXCEPTION
1526 WHEN FND_API.G_EXC_ERROR THEN
1527 ROLLBACK TO BscCopyUpdKpiProps;
1528 IF (x_msg_data IS NULL) THEN
1529 FND_MSG_PUB.Count_And_Get
1530 ( p_encoded => FND_API.G_FALSE
1531 , p_count => x_msg_count
1532 , p_data => x_msg_data
1533 );
1534 END IF;
1535 x_return_status := FND_API.G_RET_STS_ERROR;
1536 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1537 ROLLBACK TO BscCopyUpdKpiProps;
1538 IF (x_msg_data IS NULL) THEN
1539 FND_MSG_PUB.Count_And_Get
1540 ( p_encoded => FND_API.G_FALSE
1541 , p_count => x_msg_count
1542 , p_data => x_msg_data
1543 );
1544 END IF;
1545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1546 WHEN NO_DATA_FOUND THEN
1547 ROLLBACK TO BscCopyUpdKpiProps;
1548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1549 IF (x_msg_data IS NOT NULL) THEN
1550 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Update_Bsc_Kpi_Props ';
1551 ELSE
1552 x_msg_data := SQLERRM||' at BSC_COPY_INDICATOR_PUB.Update_Bsc_Kpi_Props ';
1553 END IF;
1554 WHEN OTHERS THEN
1555 ROLLBACK TO BscCopyUpdKpiProps;
1556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1557 IF (x_msg_data IS NOT NULL) THEN
1558 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Update_Bsc_Kpi_Props ';
1559 ELSE
1560 x_msg_data := SQLERRM||' AT BSC_COPY_INDICATOR_PUB.Update_Bsc_Kpi_Props ';
1561 END IF;
1562 END Update_Bsc_Kpi_Props;
1563
1564 /************************************************************************************
1565 -- API name : Copy_Dim_Level_Props
1566 -- Type : Private
1567 -- Function :
1568 -- Creates the entries in bsc_kpi_dim_level_properties
1569 ************************************************************************************/
1570
1571 PROCEDURE Copy_Dim_Level_Props(
1572 p_commit IN VARCHAR2 := FND_API.G_FALSE
1573 , p_Source_Indicator IN NUMBER
1574 , p_Target_Indicator IN NUMBER
1575 , p_Old_Dim_Levels IN FND_TABLE_OF_NUMBER
1576 , p_New_Dim_Levels IN FND_TABLE_OF_NUMBER
1577 , p_Old_Dim_Groups IN FND_TABLE_OF_NUMBER
1578 , p_New_Dim_Groups IN FND_TABLE_OF_NUMBER
1579 , p_Region_Code IN VARCHAR2
1580 , p_Old_Region_Code IN VARCHAR2
1581 , x_return_status OUT NOCOPY VARCHAR2
1582 , x_msg_count OUT NOCOPY NUMBER
1583 , x_msg_data OUT NOCOPY VARCHAR2
1584 )IS
1585 l_sql VARCHAR2(32000);
1586 TYPE c_cur_type IS REF CURSOR;
1587 c_Dim_Group_Info c_cur_type;
1588 l_Short_Name bsc_sys_dim_groups_vl.short_name%TYPE;
1589
1590 l_dim_set_id bsc_kpi_dim_groups.dim_set_id%TYPE;
1591 l_dim_group_id bsc_kpi_dim_groups.dim_group_id%TYPE;
1592 l_dim_group_index bsc_kpi_dim_groups.dim_group_index%TYPE;
1593
1594 BEGIN
1595
1596 FND_MSG_PUB.Initialize;
1597 x_return_status := FND_API.G_RET_STS_SUCCESS;
1598 SAVEPOINT BscCopyDimLevProps;
1599 IF p_Region_Code IS NOT NULL THEN -- Simulation tree
1600 BSC_SIMULATION_VIEW_PVT.Copy_Dimension_Group (
1601 p_commit => FND_API.G_FALSE
1602 ,p_Indicator => p_Target_Indicator
1603 ,p_Region_Code => p_Region_Code
1604 ,p_Old_Region_Code => p_Old_Region_Code
1605 ,p_New_Dim_Levels => p_New_Dim_Levels
1606 ,p_DbLink_Name => BSC_DESIGNER_PVT.g_DbLink_Name
1607 ,x_return_status => x_return_status
1608 ,x_msg_count => x_msg_count
1609 ,x_msg_data => x_msg_data
1610 );
1611 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1612 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1613 END IF;
1614
1615 BSC_BIS_KPI_MEAS_PUB.Assign_Unassign_Dimensions(
1616 p_commit => FND_API.G_FALSE
1617 ,p_kpi_id => p_Target_Indicator
1618 ,p_dim_set_id => 0
1619 ,p_assign_dim_names => p_Region_Code
1620 ,p_unassign_dim_names => NULL
1621 ,p_time_stamp => NULL
1622 ,x_return_status => x_return_status
1623 ,x_msg_count => x_msg_count
1624 ,x_msg_data => x_msg_data
1625 );
1626 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1627 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1628 END IF;
1629
1630 ELSE
1631 l_sql := BSC_DESIGNER_PVT.Format_DbLink_String('SELECT dim_set_id,dim_group_id,dim_group_index FROM bsc_kpi_dim_groups');
1632 l_sql := l_sql || 'WHERE indicator = :1 ORDER BY dim_set_id,dim_group_index';
1633 OPEN c_Dim_Group_Info FOR l_sql USING p_Source_Indicator;
1634 LOOP
1635 FETCH c_Dim_Group_Info INTO l_dim_set_id, l_dim_group_id, l_dim_group_index;
1636 EXIT WHEN c_Dim_Group_Info%NOTFOUND;
1637 l_Short_Name := NULL;
1638 FOR i IN 1..p_Old_Dim_Groups.COUNT LOOP
1639 IF p_Old_Dim_Groups(i) = l_dim_group_id THEN
1640 SELECT
1641 short_name
1642 INTO
1643 l_Short_Name
1644 FROM
1645 bsc_sys_dim_groups_vl
1646 WHERE
1647 dim_group_id = p_New_Dim_Groups(i);
1648 EXIT;
1649 END IF;
1650 END LOOP;
1651 IF l_Short_Name IS NOT NULL THEN
1652 BSC_BIS_KPI_MEAS_PUB.Assign_Unassign_Dimensions(
1653 p_commit => FND_API.G_FALSE
1654 ,p_kpi_id => p_Target_Indicator
1655 ,p_dim_set_id => l_dim_set_id
1656 ,p_assign_dim_names => l_Short_Name
1657 ,p_unassign_dim_names => NULL
1658 ,p_time_stamp => NULL
1659 ,x_return_status => x_return_status
1660 ,x_msg_count => x_msg_count
1661 ,x_msg_data => x_msg_data
1662 );
1663 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1664 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1665 END IF;
1666 END IF;
1667 END LOOP;
1668 CLOSE c_Dim_Group_Info;
1669
1670 l_sql := BSC_DESIGNER_PVT.Format_DbLink_String('SELECT distinct dim_set_id FROM bsc_kpi_dim_levels_vl');
1671 l_sql := l_sql || 'WHERE indicator = :1 MINUS SELECT distinct dim_set_id FROM bsc_kpi_dim_levels_vl';
1672 l_sql := l_sql || ' WHERE indicator = :2';
1673 OPEN c_Dim_Group_Info FOR l_sql USING p_Source_Indicator,p_Target_Indicator;
1674 LOOP
1675 FETCH c_Dim_Group_Info INTO l_dim_set_Id;
1676 EXIT WHEN c_Dim_Group_Info%NOTFOUND;
1677 IF l_dim_set_Id IS NOT NULL THEN
1678 BSC_BIS_KPI_MEAS_PUB.Assign_Unassign_Dimensions(
1679 p_commit => FND_API.G_FALSE
1680 ,p_kpi_id => p_Target_Indicator
1681 ,p_dim_set_id => l_dim_set_id
1682 ,p_assign_dim_names => NULL
1683 ,p_unassign_dim_names => NULL
1684 ,p_time_stamp => NULL
1685 ,x_return_status => x_return_status
1686 ,x_msg_count => x_msg_count
1687 ,x_msg_data => x_msg_data
1688 );
1689 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1690 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1691 END IF;
1692 END IF;
1693 END LOOP;
1694 CLOSE c_Dim_Group_Info;
1695 END IF;
1696
1697 IF (p_commit = FND_API.G_TRUE) THEN
1698 COMMIT;
1699 END IF;
1700
1701 EXCEPTION
1702 WHEN FND_API.G_EXC_ERROR THEN
1703 ROLLBACK TO BscCopyDimLevProps;
1704 IF (x_msg_data IS NULL) THEN
1705 FND_MSG_PUB.Count_And_Get
1706 ( p_encoded => FND_API.G_FALSE
1707 , p_count => x_msg_count
1708 , p_data => x_msg_data
1709 );
1710 END IF;
1711 x_return_status := FND_API.G_RET_STS_ERROR;
1712 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1713 ROLLBACK TO BscCopyDimLevProps;
1714 IF (x_msg_data IS NULL) THEN
1715 FND_MSG_PUB.Count_And_Get
1716 ( p_encoded => FND_API.G_FALSE
1717 , p_count => x_msg_count
1718 , p_data => x_msg_data
1719 );
1720 END IF;
1721 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1722 WHEN NO_DATA_FOUND THEN
1723 ROLLBACK TO BscCopyDimLevProps;
1724 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1725 IF (x_msg_data IS NOT NULL) THEN
1726 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Copy_Dim_Level_Props ';
1727 ELSE
1728 x_msg_data := SQLERRM||' at BSC_COPY_INDICATOR_PUB.Copy_Dim_Level_Props ';
1729 END IF;
1730 WHEN OTHERS THEN
1731 ROLLBACK TO BscCopyDimLevProps;
1732 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1733 IF (x_msg_data IS NOT NULL) THEN
1734 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Copy_Dim_Level_Props ';
1735 ELSE
1736 x_msg_data := SQLERRM||' AT BSC_COPY_INDICATOR_PUB.Copy_Dim_Level_Props ';
1737 END IF;
1738 END Copy_Dim_Level_Props;
1739
1740 /************************************************************************************
1741 -- API name : Is_Numeric_Field_Equal
1742 -- Type : Private
1743 -- Function :
1744 --
1745 ************************************************************************************/
1746
1747 FUNCTION Is_Numeric_Field_Equal(
1748 p_Old_Value NUMBER
1749 ,p_New_Value NUMBER
1750 ) RETURN BOOLEAN IS
1751
1752 BEGIN
1753
1754 IF (p_Old_Value IS NULL AND p_New_Value IS NOT NULL) OR
1755 (p_Old_Value IS NOT NULL AND p_New_Value IS NULL) OR
1756 (p_New_Value <> p_Old_Value) THEN
1757 RETURN FALSE;
1758 END IF;
1759
1760 RETURN TRUE;
1761
1762 EXCEPTION
1763 WHEN OTHERS THEN
1764 RETURN TRUE;
1765 END Is_Numeric_Field_Equal;
1766
1767 /************************************************************************************
1768 -- API name : Is_Varchar2_Field_Equal
1769 -- Type : Private
1770 -- Function :
1771 --
1772 ************************************************************************************/
1773
1774 FUNCTION Is_Varchar2_Field_Equal(
1775 p_Old_Value VARCHAR2
1776 ,p_New_Value VARCHAR2
1777 ) RETURN BOOLEAN IS
1778
1779 BEGIN
1780
1781 IF (p_Old_Value IS NULL AND p_New_Value IS NOT NULL) OR
1782 (p_Old_Value IS NOT NULL AND p_New_Value IS NULL) OR
1783 (p_New_Value <> p_Old_Value) THEN
1784 RETURN FALSE;
1785 END IF;
1786
1787 RETURN TRUE;
1788
1789 EXCEPTION
1790 WHEN OTHERS THEN
1791 RETURN TRUE;
1792 END Is_Varchar2_Field_Equal;
1793
1794
1795 /************************************************************************************
1796 -- API name : Check_Key_Item_Props
1797 -- Type : Private
1798 -- Function :
1799 -- Creates the entries in bsc_kpi_dim_level_properties
1800 ************************************************************************************/
1801
1802 PROCEDURE Check_Key_Item_Props(
1803 p_commit IN VARCHAR2 := FND_API.G_FALSE
1804 , p_Source_Indicator IN NUMBER
1805 , p_Target_Indicator IN NUMBER
1806 , p_Old_Dim_Levels IN FND_TABLE_OF_NUMBER
1807 , p_New_Dim_Levels IN FND_TABLE_OF_NUMBER
1808 , x_return_status OUT NOCOPY VARCHAR2
1809 , x_msg_count OUT NOCOPY NUMBER
1810 , x_msg_data OUT NOCOPY VARCHAR2
1811 )IS
1812 l_sql VARCHAR2(32000);
1813 TYPE c_cur_type IS REF CURSOR;
1814 c_cursor c_cur_type;
1815 l_config_change BOOLEAN := FALSE;
1816 i NUMBER;
1817 l_Old_Dim_Level_Id bsc_sys_dim_levels_b.dim_level_id%TYPE;
1818
1819 CURSOR c_Check_Config_Change IS
1820 SELECT
1821 kl.dim_set_id,dim_level_index,kl.level_table_name,kl.level_pk_col,dl.dim_level_id,
1822 parent_level_index,parent_level_rel,table_relation,
1823 parent_level_index2,parent_level_rel2
1824 FROM
1825 bsc_kpi_dim_levels_b kl,
1826 bsc_sys_dim_levels_b dl
1827 WHERE
1828 kl.indicator = p_Target_Indicator AND
1829 kl.level_table_name = dl.level_Table_name
1830 ORDER BY
1831 dim_set_id,dim_level_index;
1832 l_rec c_Check_Config_Change%ROWTYPE;
1833
1834 BEGIN
1835
1836 FND_MSG_PUB.Initialize;
1837 x_return_status := FND_API.G_RET_STS_SUCCESS;
1838 SAVEPOINT BscChkKeyItemProps;
1839
1840 l_sql := 'SELECT kl.dim_set_id,dim_level_index,kl.level_table_name,kl.level_pk_col,dl.dim_level_id,';
1841 l_sql := l_sql || 'parent_level_index,parent_level_rel,table_relation,';
1842 l_sql := l_sql || 'parent_level_index2,parent_level_rel2 ';
1843 l_sql := l_sql || BSC_DESIGNER_PVT.Format_DbLink_String('FROM bsc_kpi_dim_levels_b');
1844 l_sql := l_sql || BSC_DESIGNER_PVT.Format_DbLink_String(' kl ,bsc_sys_dim_levels_b');
1845 l_sql := l_sql || ' dl WHERE kl.indicator = :1 AND dl.dim_level_id = :2 AND kl.dim_set_id = :3 AND ';
1846 l_sql := l_sql || ' kl.level_table_name = dl.level_Table_name ORDER BY dim_set_id,dim_level_index ';
1847
1848 FOR cd IN c_Check_Config_Change LOOP
1849 l_Old_Dim_Level_Id := NULL;
1850 FOR i IN 1..p_New_Dim_Levels.COUNT LOOP
1851 IF p_New_Dim_Levels(i) = cd.dim_level_id THEN
1852 l_Old_Dim_Level_Id := p_Old_Dim_Levels(i);
1853 END IF;
1854 END LOOP;
1855 IF l_Old_Dim_Level_Id IS NOT NULL THEN
1856 OPEN c_cursor FOR l_sql USING p_Source_Indicator, l_Old_Dim_Level_Id , cd.dim_set_id;
1857 LOOP
1858 FETCH c_cursor INTO l_rec;
1859 EXIT WHEN c_cursor%notfound;
1860 IF NOT Is_Varchar2_Field_Equal(l_rec.level_table_name,cd.level_table_name) OR
1861 NOT Is_Varchar2_Field_Equal(l_rec.level_pk_col,cd.level_pk_col) OR
1862 NOT Is_Varchar2_Field_Equal(l_rec.parent_level_rel,cd.parent_level_rel) OR
1863 NOT Is_Varchar2_Field_Equal(l_rec.table_relation,cd.table_relation) OR
1864 NOT Is_Varchar2_Field_Equal(l_rec.parent_level_rel2,cd.parent_level_rel2) OR
1865 NOT Is_Numeric_Field_Equal(l_rec.parent_level_index,cd.parent_level_index) OR
1866 NOT Is_Numeric_Field_Equal(l_rec.parent_level_index2,cd.parent_level_index2) THEN
1867
1868 l_config_change := TRUE ;
1869
1870 END IF;
1871 END LOOP;
1872 CLOSE c_cursor;
1873 END IF;
1874 END LOOP;
1875
1876 IF l_config_change THEN
1877 UPDATE
1878 bsc_kpi_dim_level_properties
1879 SET
1880 default_key_value = NULL
1881 ,target_level=1
1882 WHERE
1883 indicator = p_Target_Indicator;
1884
1885 UPDATE
1886 bsc_kpi_dim_levels_b
1887 SET
1888 default_key_value = NULL
1889 ,target_level=1
1890 WHERE
1891 indicator = p_Target_Indicator;
1892
1893 /* Update_Bsc_Kpi_Props (
1894 p_commit => FND_API.G_FALSE
1895 ,p_Target_Indicator => p_Target_Indicator
1896 ,p_Property_code => 'DB_TRANSFORM'
1897 ,p_Property_value => 2
1898 ,x_return_status => x_return_status
1899 ,x_msg_count => x_msg_count
1900 ,x_msg_data => x_msg_data
1901 );
1902 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1903 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1904 END IF;*/
1905
1906 BSC_DESIGNER_PVT.ActionFlag_Change( p_Target_Indicator, BSC_DESIGNER_PVT.G_ActionFlag.GAA_Structure);
1907 ELSE
1908 l_sql := 'UPDATE bsc_kpi_dim_levels_b tar SET tar.default_key_value = (SELECT src.default_key_value ';
1909 l_sql := l_sql || BSC_DESIGNER_PVT.Format_DbLink_String('FROM bsc_kpi_dim_levels_b');
1910 l_sql := l_sql || ' src WHERE src.indicator = :1 AND src.dim_set_id = tar.dim_set_id AND';
1911 l_sql := l_sql || ' src.dim_level_index = tar.dim_level_index) WHERE indicator = :2';
1912 EXECUTE IMMEDIATE l_sql USING p_Source_Indicator,p_Target_Indicator;
1913
1914 l_sql := 'UPDATE bsc_kpi_dim_level_properties tar SET tar.default_key_value = (SELECT src.default_key_value ';
1915 l_sql := l_sql || BSC_DESIGNER_PVT.Format_DbLink_String('FROM bsc_kpi_dim_level_properties');
1916 l_sql := l_sql || BSC_DESIGNER_PVT.Format_DbLink_String(' src, bsc_sys_dim_levels_b');
1917 l_sql := l_sql || BSC_DESIGNER_PVT.Format_DbLink_String(' sys, bsc_kpi_dim_levels_b');
1918 l_sql := l_sql || BSC_DESIGNER_PVT.Format_DbLink_String(' dl WHERE src.indicator = dl.indicator ');
1919 l_sql := l_sql || ' AND src.dim_level_id = sys.dim_level_id AND sys.level_table_name = dl.level_table_name AND ';
1920 l_sql := l_sql || ' src.dim_set_id = dl.dim_set_id AND src.indicator = :1 AND src.dim_set_id = tar.dim_set_id AND';
1921 l_sql := l_sql || ' sys.dim_level_id = tar.dim_level_id) WHERE tar.indicator = :2';
1922 EXECUTE IMMEDIATE l_sql USING p_Source_Indicator,p_Target_Indicator;
1923 END IF;
1924
1925 IF (p_commit = FND_API.G_TRUE) THEN
1926 COMMIT;
1927 END IF;
1928
1929 EXCEPTION
1930 WHEN FND_API.G_EXC_ERROR THEN
1931 ROLLBACK TO BscChkKeyItemProps;
1932 IF (x_msg_data IS NULL) THEN
1933 FND_MSG_PUB.Count_And_Get
1934 ( p_encoded => FND_API.G_FALSE
1935 , p_count => x_msg_count
1936 , p_data => x_msg_data
1937 );
1938 END IF;
1939 x_return_status := FND_API.G_RET_STS_ERROR;
1940 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1941 ROLLBACK TO BscChkKeyItemProps;
1942 IF (x_msg_data IS NULL) THEN
1943 FND_MSG_PUB.Count_And_Get
1944 ( p_encoded => FND_API.G_FALSE
1945 , p_count => x_msg_count
1946 , p_data => x_msg_data
1947 );
1948 END IF;
1949 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1950 WHEN NO_DATA_FOUND THEN
1951 ROLLBACK TO BscChkKeyItemProps;
1952 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1953 IF (x_msg_data IS NOT NULL) THEN
1954 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Check_Key_Item_Props ';
1955 ELSE
1956 x_msg_data := SQLERRM||' at BSC_COPY_INDICATOR_PUB.Check_Key_Item_Props ';
1957 END IF;
1958 WHEN OTHERS THEN
1959 ROLLBACK TO BscChkKeyItemProps;
1960 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1961 IF (x_msg_data IS NOT NULL) THEN
1962 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Check_Key_Item_Props ';
1963 ELSE
1964 x_msg_data := SQLERRM||' AT BSC_COPY_INDICATOR_PUB.Check_Key_Item_Props ';
1965 END IF;
1966 END Check_Key_Item_Props;
1967
1968
1969 /************************************************************************************
1970 -- API name : Check_Color_By_Total_Props
1971 -- Type : Private
1972 -- Function :
1973 -- Creates the entries in bsc_kpi_dim_level_properties
1974 ************************************************************************************/
1975
1976 PROCEDURE Check_Color_By_Total_Props(
1977 p_commit IN VARCHAR2 := FND_API.G_FALSE
1978 , p_Target_Indicator IN NUMBER
1979 , x_return_status OUT NOCOPY VARCHAR2
1980 , x_msg_count OUT NOCOPY NUMBER
1981 , x_msg_data OUT NOCOPY VARCHAR2
1982 )IS
1983 l_dim_set_id bsc_kpi_dim_sets_vl.dim_set_id%TYPE;
1984 l_count NUMBER := 0;
1985
1986 BEGIN
1987
1988 FND_MSG_PUB.Initialize;
1989 x_return_status := FND_API.G_RET_STS_SUCCESS;
1990 SAVEPOINT BscChkColorByTotal;
1991
1992 SELECT
1993 dim_set_id
1994 INTO
1995 l_dim_set_id
1996 FROM
1997 bsc_oaf_analysys_opt_comb_v da,
1998 bsc_db_basic_dim_sets_v ds
1999 WHERE
2000 ds.indicator = da.indicator AND
2001 ds.a0 = da.analysis_option0 AND
2002 ds.a1 = da.analysis_option1 AND
2003 ds.a2 = da.analysis_option2 AND
2004 ds.series_id = da.series_id AND
2005 da.default_flag = 1 AND
2006 ds.indicator = p_Target_Indicator;
2007
2008
2009 IF l_dim_set_id IS NOT NULL THEN
2010 SELECT
2011 COUNT(1)
2012 INTO
2013 l_count
2014 FROM
2015 bsc_kpi_dim_levels_b
2016 WHERE
2017 indicator = p_Target_Indicator AND
2018 dim_set_id = l_dim_set_id AND
2019 default_key_value IS NULL AND UPPER(default_value)= 'C';
2020
2021 IF l_count > 0 THEN
2022 Update_Bsc_Kpi_Props (
2023 p_commit => FND_API.G_FALSE
2024 ,p_Target_Indicator => p_Target_Indicator
2025 ,p_Property_code => 'COLOR_BY_TOTAL'
2026 ,p_Property_value => 0
2027 ,x_return_status => x_return_status
2028 ,x_msg_count => x_msg_count
2029 ,x_msg_data => x_msg_data
2030 );
2031 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2032 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2033 END IF;
2034 END IF;
2035 END IF;
2036
2037 IF (p_commit = FND_API.G_TRUE) THEN
2038 COMMIT;
2039 END IF;
2040
2041 EXCEPTION
2042 WHEN FND_API.G_EXC_ERROR THEN
2043 ROLLBACK TO BscChkColorByTotal;
2044 IF (x_msg_data IS NULL) THEN
2045 FND_MSG_PUB.Count_And_Get
2046 ( p_encoded => FND_API.G_FALSE
2047 , p_count => x_msg_count
2048 , p_data => x_msg_data
2049 );
2050 END IF;
2051 x_return_status := FND_API.G_RET_STS_ERROR;
2052 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2053 ROLLBACK TO BscChkColorByTotal;
2054 IF (x_msg_data IS NULL) THEN
2055 FND_MSG_PUB.Count_And_Get
2056 ( p_encoded => FND_API.G_FALSE
2057 , p_count => x_msg_count
2058 , p_data => x_msg_data
2059 );
2060 END IF;
2061 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2062 WHEN NO_DATA_FOUND THEN
2063 ROLLBACK TO BscChkColorByTotal;
2064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2065 IF (x_msg_data IS NOT NULL) THEN
2066 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Check_Color_By_Total_Props ';
2067 ELSE
2068 x_msg_data := SQLERRM||' at BSC_COPY_INDICATOR_PUB.Check_Color_By_Total_Props ';
2069 END IF;
2070 WHEN OTHERS THEN
2071 ROLLBACK TO BscChkColorByTotal;
2072 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2073 IF (x_msg_data IS NOT NULL) THEN
2074 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Check_Color_By_Total_Props ';
2075 ELSE
2076 x_msg_data := SQLERRM||' AT BSC_COPY_INDICATOR_PUB.Check_Color_By_Total_Props ';
2077 END IF;
2078 END Check_Color_By_Total_Props;
2079
2080 /************************************************************************************
2081 -- API name : Check_Profit_Loss_Properties
2082 -- Type : Private
2083 -- Function :
2084 --
2085 ************************************************************************************/
2086
2087 PROCEDURE Check_Profit_Loss_Properties(
2088 p_commit IN VARCHAR2 := FND_API.G_FALSE
2089 , p_Target_Indicator IN NUMBER
2090 , x_return_status OUT NOCOPY VARCHAR2
2091 , x_msg_count OUT NOCOPY NUMBER
2092 , x_msg_data OUT NOCOPY VARCHAR2
2093 )IS
2094 l_sql VARCHAR2(32000);
2095 l_Indic_Type bsc_kpis_b.indicator_type%TYPE;
2096 l_Config_Type bsc_kpis_b.config_type%TYPE;
2097 TYPE c_cur_type IS REF CURSOR;
2098 c_cursor c_cur_type;
2099
2100 CURSOR c_Indic IS
2101 SELECT
2102 indicator_type,config_type
2103 FROM
2104 bsc_kpis_vl
2105 WHERE
2106 indicator = p_Target_Indicator;
2107 l_Count NUMBER := 0;
2108 l_Drill_Flag NUMBER := 0;
2109 BEGIN
2110
2111 FND_MSG_PUB.Initialize;
2112 x_return_status := FND_API.G_RET_STS_SUCCESS;
2113 SAVEPOINT BscCopyIndicPLUpd;
2114
2115 OPEN c_Indic;
2116 FETCH c_Indic INTO l_Indic_Type, l_Config_Type;
2117 CLOSE c_Indic;
2118
2119 IF l_Indic_Type = 1 AND l_Config_Type = 3 THEN
2120 SELECT
2121 COUNT(1)
2122 INTO
2123 l_Count
2124 FROM
2125 bsc_kpi_dim_levels_b
2126 WHERE
2127 indicator = p_Target_Indicator;
2128
2129 IF l_Count > 3 THEN
2130 l_Drill_Flag := 1;
2131 END IF;
2132
2133 Update_Bsc_Kpi_Props (
2134 p_commit => FND_API.G_FALSE
2135 ,p_Target_Indicator => p_Target_Indicator
2136 ,p_Property_code => 'PL_DRILL_FLAG'
2137 ,p_Property_value => l_Drill_Flag
2138 ,x_return_status => x_return_status
2139 ,x_msg_count => x_msg_count
2140 ,x_msg_data => x_msg_data
2141 );
2142 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2144 END IF;
2145
2146 END IF;
2147
2148
2149 IF (p_commit = FND_API.G_TRUE) THEN
2150 COMMIT;
2151 END IF;
2152
2153 EXCEPTION
2154 WHEN FND_API.G_EXC_ERROR THEN
2155 ROLLBACK TO BscCopyIndicPLUpd;
2156 IF (x_msg_data IS NULL) THEN
2157 FND_MSG_PUB.Count_And_Get
2158 ( p_encoded => FND_API.G_FALSE
2159 , p_count => x_msg_count
2160 , p_data => x_msg_data
2161 );
2162 END IF;
2163 x_return_status := FND_API.G_RET_STS_ERROR;
2164 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2165 ROLLBACK TO BscCopyIndicPLUpd;
2166 IF (x_msg_data IS NULL) THEN
2167 FND_MSG_PUB.Count_And_Get
2168 ( p_encoded => FND_API.G_FALSE
2169 , p_count => x_msg_count
2170 , p_data => x_msg_data
2171 );
2172 END IF;
2173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2174 WHEN NO_DATA_FOUND THEN
2175 ROLLBACK TO BscCopyIndicPLUpd;
2176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2177 IF (x_msg_data IS NOT NULL) THEN
2178 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Check_Profit_Loss_Properties ';
2179 ELSE
2180 x_msg_data := SQLERRM||' at BSC_COPY_INDICATOR_PUB.Check_Profit_Loss_Properties ';
2181 END IF;
2182 WHEN OTHERS THEN
2183 ROLLBACK TO BscCopyIndicPLUpd;
2184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2185 IF (x_msg_data IS NOT NULL) THEN
2186 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Check_Profit_Loss_Properties ';
2187 ELSE
2188 x_msg_data := SQLERRM||' AT BSC_COPY_INDICATOR_PUB.Check_Profit_Loss_Properties ';
2189 END IF;
2190 END Check_Profit_Loss_Properties;
2191
2192 /************************************************************************************
2193 -- API name : Check_Default_Record_Data_Tbls
2194 -- Type : Private
2195 -- Function :
2196 --
2197 ************************************************************************************/
2198
2199 PROCEDURE Check_Default_Record_Data_Tbls(
2200 p_commit IN VARCHAR2 := FND_API.G_FALSE
2201 , p_Target_Indicator IN NUMBER
2202 , x_return_status OUT NOCOPY VARCHAR2
2203 , x_msg_count OUT NOCOPY NUMBER
2204 , x_msg_data OUT NOCOPY VARCHAR2
2205 )IS
2206 l_sql VARCHAR2(32000);
2207 TYPE c_cur_type IS REF CURSOR;
2208 c_cursor c_cur_type;
2209
2210 l_Count NUMBER := 0;
2211 BEGIN
2212
2213 FND_MSG_PUB.Initialize;
2214 x_return_status := FND_API.G_RET_STS_SUCCESS;
2215 SAVEPOINT BscCopyDfltDataTbl;
2216
2217 SELECT
2218 COUNT(1)
2219 INTO
2220 l_Count
2221 FROM
2222 bsc_kpi_data_tables
2223 WHERE
2224 indicator = p_Target_Indicator;
2225
2226 IF l_Count = 0 THEN
2227 INSERT INTO bsc_kpi_data_tables (
2228 indicator
2229 ,periodicity_id
2230 ,dim_set_id
2231 ,level_comb
2232 ,table_name
2233 ,filter_condition)
2234 (SELECT
2235 indicator indicator
2236 ,periodicity_id periodicity_id
2237 ,0 dim_set_id
2238 ,'?' level_comb
2239 ,NULL table_name
2240 ,NULL filter_condition
2241 FROM
2242 bsc_kpi_periodicities
2243 WHERE
2244 INDICATOR = p_Target_Indicator);
2245 END IF;
2246
2247 IF (p_commit = FND_API.G_TRUE) THEN
2248 COMMIT;
2249 END IF;
2250
2251 EXCEPTION
2252 WHEN FND_API.G_EXC_ERROR THEN
2253 ROLLBACK TO BscCopyDfltDataTbl;
2254 IF (x_msg_data IS NULL) THEN
2255 FND_MSG_PUB.Count_And_Get
2256 ( p_encoded => FND_API.G_FALSE
2257 , p_count => x_msg_count
2258 , p_data => x_msg_data
2259 );
2260 END IF;
2261 x_return_status := FND_API.G_RET_STS_ERROR;
2262 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2263 ROLLBACK TO BscCopyDfltDataTbl;
2264 IF (x_msg_data IS NULL) THEN
2265 FND_MSG_PUB.Count_And_Get
2266 ( p_encoded => FND_API.G_FALSE
2267 , p_count => x_msg_count
2268 , p_data => x_msg_data
2269 );
2270 END IF;
2271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2272 WHEN NO_DATA_FOUND THEN
2273 ROLLBACK TO BscCopyDfltDataTbl;
2274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2275 IF (x_msg_data IS NOT NULL) THEN
2276 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Check_Default_Record_Data_Tbls ';
2277 ELSE
2278 x_msg_data := SQLERRM||' at BSC_COPY_INDICATOR_PUB.Check_Default_Record_Data_Tbls ';
2279 END IF;
2280 WHEN OTHERS THEN
2281 ROLLBACK TO BscCopyDfltDataTbl;
2282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2283 IF (x_msg_data IS NOT NULL) THEN
2284 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Check_Default_Record_Data_Tbls ';
2285 ELSE
2286 x_msg_data := SQLERRM||' AT BSC_COPY_INDICATOR_PUB.Check_Default_Record_Data_Tbls ';
2287 END IF;
2288 END Check_Default_Record_Data_Tbls;
2289
2290 /************************************************************************************
2291 -- API name : Check_KPI_Name
2292 -- Type : Private
2293 -- Function :
2294 --
2295 ************************************************************************************/
2296
2297 PROCEDURE Check_KPI_Name(
2298 p_commit IN VARCHAR2 := FND_API.G_FALSE
2299 , p_Target_Indicator IN NUMBER
2300 , p_Name IN VARCHAR2
2301 , p_Description IN VARCHAR2
2302 , x_return_status OUT NOCOPY VARCHAR2
2303 , x_msg_count OUT NOCOPY NUMBER
2304 , x_msg_data OUT NOCOPY VARCHAR2
2305 )IS
2306 CURSOR c_Kpi_Lang IS
2307 SELECT
2308 distinct source_lang
2309 ,name
2310 FROM
2311 bsc_kpis_tl
2312 WHERE indicator = p_Target_Indicator;
2313
2314
2315 l_Count NUMBER := 0;
2316 l_source_lang bsc_kpis_tl.source_lang%TYPE;
2317 l_name bsc_kpis_tl.name%TYPE;
2318 l_new_name bsc_kpis_tl.name%TYPE;
2319 BEGIN
2320
2321 FND_MSG_PUB.Initialize;
2322 x_return_status := FND_API.G_RET_STS_SUCCESS;
2323 SAVEPOINT BscCopyIndicChkKpiName;
2324
2325 l_new_name := p_Name;
2326 FOR cd IN c_Kpi_Lang LOOP
2327 SELECT
2328 COUNT(1)
2329 INTO
2330 l_Count
2331 FROM
2332 bsc_kpis_vl
2333 WHERE
2334 UPPER(name) = UPPER(cd.Name);
2335
2336 IF l_Count > 0 THEN
2337 --l_new_name := SUBSTR(cd.Name, 0, (LENGTH(cd.Name) -3)) || ' ' || l_Count;
2338 IF p_Name IS NULL THEN
2339 l_new_name := BSC_UTILITY.get_Next_Name(
2340 p_Name => cd.Name
2341 ,p_Max_Count => 150
2342 ,p_Table_Name => 'BSC_KPIS_TL'
2343 ,p_Column_Name => 'NAME'
2344 ,p_Character => ' '
2345 );
2346 END IF;
2347 UPDATE
2348 bsc_kpis_tl
2349 SET
2350 name = l_new_name
2351 WHERE
2352 indicator = p_Target_Indicator AND
2353 source_lang = cd.source_lang;
2354 IF p_Description IS NOT NULL THEN
2355 UPDATE
2356 bsc_kpis_tl
2357 SET
2358 help = p_Description
2359 WHERE
2360 indicator = p_Target_Indicator AND
2361 source_lang = cd.source_lang;
2362 END IF;
2363 END IF;
2364
2365 END LOOP;
2366
2367 IF (p_commit = FND_API.G_TRUE) THEN
2368 COMMIT;
2369 END IF;
2370
2371 EXCEPTION
2372 WHEN FND_API.G_EXC_ERROR THEN
2373 ROLLBACK TO BscCopyIndicChkKpiName;
2374 IF (x_msg_data IS NULL) THEN
2375 FND_MSG_PUB.Count_And_Get
2376 ( p_encoded => FND_API.G_FALSE
2377 , p_count => x_msg_count
2378 , p_data => x_msg_data
2379 );
2380 END IF;
2381 x_return_status := FND_API.G_RET_STS_ERROR;
2382 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2383 ROLLBACK TO BscCopyIndicChkKpiName;
2384 IF (x_msg_data IS NULL) THEN
2385 FND_MSG_PUB.Count_And_Get
2386 ( p_encoded => FND_API.G_FALSE
2387 , p_count => x_msg_count
2388 , p_data => x_msg_data
2389 );
2390 END IF;
2391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2392 WHEN NO_DATA_FOUND THEN
2393 ROLLBACK TO BscCopyIndicChkKpiName;
2394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2395 IF (x_msg_data IS NOT NULL) THEN
2396 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Check_KPI_Name ';
2397 ELSE
2398 x_msg_data := SQLERRM||' at BSC_COPY_INDICATOR_PUB.Check_KPI_Name ';
2399 END IF;
2400 WHEN OTHERS THEN
2401 ROLLBACK TO BscCopyIndicChkKpiName;
2402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2403 IF (x_msg_data IS NOT NULL) THEN
2404 x_msg_data := x_msg_data||' -> BSC_COPY_INDICATOR_PUB.Check_KPI_Name ';
2405 ELSE
2406 x_msg_data := SQLERRM||' AT BSC_COPY_INDICATOR_PUB.Check_KPI_Name ';
2407 END IF;
2408 END Check_KPI_Name;
2409
2410 /************************************************************************************
2411 -- API name : CopyNew_Indicator_UI_Wrap
2412 -- Type : Public
2413 -- Function :
2414 -- This API is used to copy an indicator from one indicator group to another
2415 ************************************************************************************/
2416 PROCEDURE CopyNew_Indicator_UI_Wrap (
2417 p_commit IN VARCHAR2 := FND_API.G_FALSE
2418 , p_DbLink_Name IN VARCHAR2
2419 , p_Name IN VARCHAR2 := NULL
2420 , p_Description IN VARCHAR2 := NULL
2421 , p_Source_Indicator IN NUMBER
2422 , p_Target_Group IN NUMBER
2423 , p_New_Position IN NUMBER
2424 , p_Old_Dim_Levels IN FND_TABLE_OF_NUMBER
2425 , p_New_Dim_Levels IN FND_TABLE_OF_NUMBER
2426 , p_Old_Dim_Groups IN FND_TABLE_OF_NUMBER
2427 , p_New_Dim_Groups IN FND_TABLE_OF_NUMBER
2428 , p_Old_DataSet_Map IN FND_TABLE_OF_NUMBER
2429 , p_New_DataSet_Map IN FND_TABLE_OF_NUMBER
2430 , p_Target_Calendar IN NUMBER
2431 , p_Old_Periodicities IN FND_TABLE_OF_NUMBER
2432 , p_New_Periodicities IN FND_TABLE_OF_NUMBER
2433 , p_Time_Stamp IN VARCHAR2 := NULL
2434 , x_return_status OUT NOCOPY VARCHAR2
2435 , x_msg_count OUT NOCOPY NUMBER
2436 , x_msg_data OUT NOCOPY VARCHAR2
2437 ) IS
2438 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
2439 l_Target_Indicator bsc_kpis_b.indicator%TYPE;
2440 l_sql VARCHAR2(32000);
2441 l_Responsibility_Key_List VARCHAR2(200);
2442 l_Short_Name bsc_kpis_b.short_name%TYPE;
2443 l_Region_Code ak_regions.region_code%TYPE;
2444 BEGIN
2445
2446 FND_MSG_PUB.Initialize;
2447 x_return_status := FND_API.G_RET_STS_SUCCESS;
2448
2449 SAVEPOINT BscCopyIndicatorUIWrap;
2450
2451 BSC_DESIGNER_PVT.g_DbLink_Name := p_DbLink_Name;
2452 Validate_Indicator_Copy (
2453 p_Source_Indicator => p_Source_Indicator
2454 ,x_return_status => x_return_status
2455 ,x_msg_count => x_msg_count
2456 ,x_msg_data => x_msg_data
2457 );
2458 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2459 BSC_APPS.Write_Line_Log('Validation of objective copy failed : Objective [ ' ||p_Source_Indicator||'] ' , BSC_APPS.OUTPUT_FILE);
2460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2461 END IF;
2462
2463 IF p_DbLink_Name IS NULL THEN
2464 --l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := p_Source_Indicator;
2465 BSC_BIS_LOCKS_PUB.Lock_Kpi (
2466 p_kpi_Id => p_Source_Indicator
2467 ,p_time_stamp => p_Time_Stamp
2468 ,p_full_lock_flag => FND_API.G_FALSE
2469 ,x_return_status => x_return_status
2470 ,x_msg_count => x_msg_count
2471 ,x_msg_data => x_msg_data
2472 );
2473 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2474 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2475 END IF;
2476 END IF;
2477
2478 /*
2479 --Remove this p_Db_LInk Name .Even the datasets,dimsets in the source have to be locked
2480 --IF p_DbLink_Name IS NOT NULL THEN -- Only if the target is different than source
2481 Lock_Target_Entities (
2482 p_DataSet_Map => p_New_DataSet_Map
2483 p_DimLevel_Map => p_New_Dim_Levels
2484 p_DimGroup_Map => p_New_Dim_Groups
2485 p_Periodicity_Map => p_New_Periodicities
2486 p_Calendar => p_Target_Calendar
2487 ,p_time_stamp => p_Time_Stamp
2488 ,x_return_status => x_return_status
2489 ,x_msg_count => x_msg_count
2490 ,x_msg_data => x_msg_data
2491 );
2492 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2493 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2494 END IF;
2495 --END IF;*/
2496
2497 BSC_DESIGNER_PVT.Copy_Kpi_Metadata(
2498 p_commit => FND_API.G_FALSE
2499 ,p_DbLink_Name => p_DbLink_Name
2500 ,p_Source_Indicator => p_Source_Indicator
2501 ,x_Target_Indicator => l_Target_Indicator
2502 ,x_return_status => x_return_status
2503 ,x_msg_count => x_msg_count
2504 ,x_msg_data => x_msg_data
2505 );
2506 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2507 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2508 END IF;
2509
2510 SELECT
2511 short_name
2512 INTO
2513 l_Short_Name
2514 FROM
2515 bsc_kpis_b
2516 WHERE
2517 indicator = l_Target_Indicator;
2518
2519 Move_Indicator (
2520 p_commit => FND_API.G_FALSE
2521 ,p_Indicator => l_Target_Indicator
2522 ,p_New_Indicator_Group => p_Target_Group
2523 ,p_Assign_Group_To_Tab => FND_API.G_FALSE
2524 ,x_return_status => x_return_status
2525 ,x_msg_count => x_msg_count
2526 ,x_msg_data => x_msg_data
2527 );
2528 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2529 RAISE FND_API.G_EXC_ERROR;
2530 END IF;
2531
2532 l_Responsibility_Key_List := 'BSC_Manager,BSC_DESIGNER,BSC_PMD_USER';
2533 Create_Kpi_Access_Wrap (
2534 p_commit => FND_API.G_FALSE
2535 ,p_Comma_Sep_Resposibility_Key => l_Responsibility_Key_List
2536 ,p_Indicator_Id => l_Target_Indicator
2537 ,x_return_status => x_return_status
2538 ,x_msg_count => x_msg_count
2539 ,x_msg_data => x_msg_data
2540 );
2541 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2542 RAISE FND_API.G_EXC_ERROR;
2543 END IF;
2544
2545 BSC_DESIGNER_PVT.ActionFlag_Change(
2546 x_indicator => l_Target_Indicator
2547 ,x_newflag => 1 );
2548
2549 Check_KPI_Name (
2550 p_commit => FND_API.G_FALSE
2551 ,p_Target_Indicator => l_Target_Indicator
2552 ,p_Name => p_Name
2553 ,p_Description => p_Description
2554 ,x_return_status => x_return_status
2555 ,x_msg_count => x_msg_count
2556 ,x_msg_data => x_msg_data
2557 );
2558 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2560 END IF;
2561
2562 IF l_Short_Name IS NOT NULL THEN
2563 l_Region_Code := BSC_UTILITY.get_Next_Name(
2564 p_Name => l_short_name
2565 ,p_Max_Count => 30
2566 ,p_Table_Name => 'AK_REGIONS'
2567 ,p_Column_Name => 'REGION_CODE'
2568 ,p_Character => '_'
2569 );
2570 END IF;
2571 IF p_New_Dim_Levels.COUNT > 0 OR p_New_Dim_Groups.COUNT > 0
2572 OR p_Old_DataSet_Map.COUNT > 0 OR p_New_Periodicities.COUNT > 0 THEN
2573
2574 -- IF p_New_Dim_Groups.COUNT > 0 THEN
2575 Copy_Dim_Level_Props (
2576 p_commit => FND_API.G_FALSE
2577 ,p_Source_Indicator => p_Source_Indicator
2578 ,p_Target_Indicator => l_Target_Indicator
2579 ,p_Old_Dim_Levels => p_Old_Dim_Levels
2580 ,p_New_Dim_Levels => p_New_Dim_Levels
2581 ,p_Old_Dim_Groups => p_Old_Dim_Groups
2582 ,p_New_Dim_Groups => p_New_Dim_Groups
2583 ,p_Region_Code => l_Region_Code
2584 ,p_Old_Region_Code => l_Short_Name
2585 ,x_return_status => x_return_status
2586 ,x_msg_count => x_msg_count
2587 ,x_msg_data => x_msg_data
2588 );
2589 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2590 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2591 END IF;
2592 Check_Key_Item_Props (
2593 p_commit => FND_API.G_FALSE
2594 ,p_Source_Indicator => p_Source_Indicator
2595 ,p_Target_Indicator => l_Target_Indicator
2596 ,p_Old_Dim_Levels => p_Old_Dim_Levels
2597 ,p_New_Dim_Levels => p_New_Dim_Levels
2598 ,x_return_status => x_return_status
2599 ,x_msg_count => x_msg_count
2600 ,x_msg_data => x_msg_data
2601 );
2602 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2604 END IF;
2605
2606 BSC_DESIGNER_PVT.Deflt_Update_Dim_Values (
2607 x_indicator => l_Target_Indicator
2608 );
2609
2610 BSC_DESIGNER_PVT.Deflt_Update_Dim_Names (
2611 x_indicator => l_Target_Indicator
2612 );
2613
2614 -- END IF;
2615
2616 Copy_Analysis_Measures (
2617 p_commit => FND_API.G_FALSE
2618 ,p_Source_Indicator => p_Source_Indicator
2619 ,p_Target_Indicator => l_Target_Indicator
2620 ,p_Old_DataSet_Map => p_Old_DataSet_Map
2621 ,p_New_DataSet_Map => p_New_DataSet_Map
2622 ,x_return_status => x_return_status
2623 ,x_msg_count => x_msg_count
2624 ,x_msg_data => x_msg_data
2625 );
2626 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2627 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2628 END IF;
2629
2630 BSC_OBJ_ANALYSIS_OPTIONS_PUB.Check_YTD_Apply (
2631 p_commit => FND_API.G_FALSE
2632 ,p_Indicator => l_Target_Indicator
2633 ,x_return_status => x_return_status
2634 ,x_msg_count => x_msg_count
2635 ,x_msg_data => x_msg_data
2636 );
2637 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2639 END IF;
2640
2641 BSC_DESIGNER_PVT.Deflt_Update_SN_FM_CM (
2642 x_indicator => l_Target_Indicator
2643 );
2644
2645 Copy_Periodicities (
2646 p_commit => FND_API.G_FALSE
2647 ,p_Source_Indicator => p_Source_Indicator
2648 ,p_Target_Indicator => l_Target_Indicator
2649 ,p_Target_Calendar => p_Target_Calendar
2650 ,p_Old_Periodicities => p_Old_Periodicities
2651 ,p_New_Periodicities => p_New_Periodicities
2652 ,x_return_status => x_return_status
2653 ,x_msg_count => x_msg_count
2654 ,x_msg_data => x_msg_data
2655 );
2656 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2657 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2658 END IF;
2659
2660 Check_Default_Record_Data_Tbls (
2661 p_commit => FND_API.G_FALSE
2662 ,p_Target_Indicator => l_Target_Indicator
2663 ,x_return_status => x_return_status
2664 ,x_msg_count => x_msg_count
2665 ,x_msg_data => x_msg_data
2666 );
2667 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2669 END IF;
2670
2671 Check_Profit_Loss_Properties (
2672 p_commit => FND_API.G_FALSE
2673 ,p_Target_Indicator => l_Target_Indicator
2674 ,x_return_status => x_return_status
2675 ,x_msg_count => x_msg_count
2676 ,x_msg_data => x_msg_data
2677 );
2678 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2679 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2680 END IF;
2681
2682
2683 IF l_Short_Name IS NOT NULL THEN
2684 BSC_MIGRATION.Migrate_Sim_Data (
2685 p_commit => FND_API.G_FALSE
2686 ,p_Src_indicator => p_Source_Indicator
2687 ,p_Trg_indicator => l_Target_Indicator
2688 ,p_Region_Code => l_Region_Code
2689 ,p_Old_Region_Code => l_Short_Name
2690 ,p_Old_Dim_Levels => p_Old_Dim_Levels
2691 ,p_New_Dim_Levels => p_New_Dim_Levels
2692 ,p_Old_Dim_Groups => p_Old_Dim_Groups
2693 ,p_New_Dim_Groups => p_New_Dim_Groups
2694 ,p_Old_DataSet_Map => p_Old_DataSet_Map
2695 ,p_New_DataSet_Map => p_New_DataSet_Map
2696 ,p_Target_Calendar => p_Target_Calendar
2697 ,p_Old_Periodicities => p_Old_Periodicities
2698 ,p_New_Periodicities => p_New_Periodicities
2699 ,x_return_status => x_return_status
2700 ,x_msg_count => x_msg_count
2701 ,x_msg_data => x_msg_data
2702 );
2703 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2705 END IF;
2706 END IF;
2707
2708 END IF;
2709
2710 Reposition_Indicator (
2711 p_commit => FND_API.G_FALSE
2712 ,p_Indicator => l_Target_Indicator
2713 ,p_New_Indicator_Group => p_Target_Group
2714 ,p_New_Position => p_New_Position
2715 ,x_return_status => x_return_status
2716 ,x_msg_count => x_msg_count
2717 ,x_msg_data => x_msg_data
2718 );
2719 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2720 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2721 END IF;
2722
2723 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := l_Target_Indicator;
2724
2725 BSC_KPI_PUB.Update_Kpi_Time_Stamp
2726 ( p_commit => FND_API.G_FALSE
2727 , p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
2728 , x_return_status => x_return_status
2729 , x_msg_count => x_msg_count
2730 , x_msg_data => x_msg_data
2731 );
2732 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2733 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2734 END IF;
2735
2736 IF (p_commit = FND_API.G_TRUE) THEN
2737 COMMIT;
2738 END IF;
2739
2740 EXCEPTION
2741 WHEN FND_API.G_EXC_ERROR THEN
2742 ROLLBACK TO BscCopyIndicatorUIWrap;
2743 IF (x_msg_data IS NULL) THEN
2744 FND_MSG_PUB.Count_And_Get
2745 ( p_encoded => FND_API.G_FALSE
2746 , p_count => x_msg_count
2747 , p_data => x_msg_data
2748 );
2749 END IF;
2750 x_return_status := FND_API.G_RET_STS_ERROR;
2751 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2752 ROLLBACK TO BscCopyIndicatorUIWrap;
2753 IF (x_msg_data IS NULL) THEN
2754 FND_MSG_PUB.Count_And_Get
2755 ( p_encoded => FND_API.G_FALSE
2756 , p_count => x_msg_count
2757 , p_data => x_msg_data
2758 );
2759 END IF;
2760 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2761 WHEN NO_DATA_FOUND THEN
2762 ROLLBACK TO BscCopyIndicatorUIWrap;
2763 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2764 IF (x_msg_data IS NOT NULL) THEN
2765 x_msg_data := x_msg_data || ' -> BSC_COPY_INDICATOR_PUB.CopyNew_Indicator_UI_Wrap ';
2766 ELSE
2767 x_msg_data := SQLERRM || ' at BSC_COPY_INDICATOR_PUB.CopyNew_Indicator_UI_Wrap ';
2768 END IF;
2769 WHEN OTHERS THEN
2770 ROLLBACK TO BscCopyIndicatorUIWrap;
2771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2772 IF (x_msg_data IS NOT NULL) THEN
2773 x_msg_data := x_msg_data || ' -> BSC_COPY_INDICATOR_PUB.CopyNew_Indicator_UI_Wrap ';
2774 ELSE
2775 x_msg_data := SQLERRM || ' at BSC_COPY_INDICATOR_PUB.CopyNew_Indicator_UI_Wrap ';
2776 END IF;
2777 END CopyNew_Indicator_UI_Wrap;
2778
2779
2780 END BSC_COPY_INDICATOR_PUB;