[Home] [Help]
PACKAGE BODY: APPS.BSC_KPI_PUB
Source
1 package body BSC_KPI_PUB as
2 /* $Header: BSCPKPPB.pls 120.12 2007/02/09 09:16:15 ashankar ship $ */
3 /*
4 +======================================================================================+
5 | Copyright (c) 2001 Oracle Corporation, Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +======================================================================================+
8 | FILENAME |
9 | BSCPKPPB.pls |
10 | |
11 | Creation Date: |
12 | October 22, 2001 |
13 | |
14 | Creator: |
15 | Mario-Jair Campos |
16 | |
17 | Description: Public Body version. |
18 | This package Creates, Retrieve, Update, Delete |
19 | for BSC KPI information. |
20 | |
21 | Modified By: PWALI for bug #2843082 20-MAR-03 |
22 | 13-MAY-2003 PWALI Bug #2942895, SQL BIND COMPLIANCE |
23 | 24-JUL-2003 Adeulgao fixed bug#3047536 |
24 | Granted access of KPIS to BSC_PMD_USER |
25 | 14-NOV-2003 ADRAO Modified for Bug #3248729, |
26 | 10-MAR-04 jxyu Modified for enhancement #3493589 |
27 | 06-MAY-04 ADRAO added code to handle BIS_DBI_ADMIN responsibility |
28 | 18-MAY-04 adrao Modified PL/SQL records and CRUD to accept SHORT_NAME |
29 | 10-JUN-04 adrao added XTD Calculation to BSC_KPI_CALCULATION |
30 | 20-JUL-04 adrao added PMU Responsibility for all S2E KPIs created from |
31 | Configure Region link from any responsibility. Bug#3775876 |
32 | 01-SEP-04 ashankar fix for the bug #3864002.Added the method |
33 | Delete_Custom_View_Links within Delete_Kpi |
34 | 10-SEP-04 adrao modified Create_Kpi_User_Access for Bug#3877636 |
35 | 15-DEC-04 adrao moved API Delete_Kpi_AT to be public to all, Bug#4064587 |
36 | 21-JUL-2005 ashankar Bug#4314386 |
37 | 28-JUL-2005 ashankar Bug#4517700 for the message BSC_GROUP_BESIDES_TO_BELOW |
38 | removed the BELOW_NAME token as it was not needed. |
39 | 22-AUG-2005 ashankar Bug#4220400 Modified the method Update_Kpi |
40 | 01-SEP-2005 adrao fixed Create_Kpi_User_Access() for Bug#4563456 |
41 | 24-Jan-2006 akoduri Bug#4958055 Dgrp dimension not getting deleted |
42 | while disassociating from objective |
43 | 08-MAR-2006 adrao Bug#5081180 Modified Get_KPI_Dim_ShortNames() |
44 | modified the Cursor c_imported_dims |
45 | 02-Aug-2006 ashankar bug fix#5400575 mahde changes to the method move_master|
46 | _kpi |
47 | 16-NOV-2006 ankgoel Color By KPI enh#5244136 |
48 | 31-Jan-2007 akoduri Enh #5679096 Migration of multibar functionality from |
49 | VB to Html |
50 | 09-feb-2007 ashankar Simulation Tree Enhacement 5386112 |
51 +======================================================================================+
52 */
53
54 G_PKG_NAME CONSTANT varchar2(30) := 'BSC_KPI_PUB';
55
56
57 --New procedure with OUT parameter
58 PROCEDURE Create_Kpi(
59 p_commit IN VARCHAR2 := FND_API.G_FALSE
60 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
61 ,x_Bsc_Kpi_Entity_Rec OUT NOCOPY BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
62 ,x_return_status OUT NOCOPY VARCHAR2
63 ,x_msg_count OUT NOCOPY NUMBER
64 ,x_msg_data OUT NOCOPY VARCHAR2
65 ) IS
66
67 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
68 l_No_Ind NUMBER;
69 l_Count NUMBER;
70 BEGIN
71
72 FND_MSG_PUB.Initialize;
73 x_return_status := FND_API.G_RET_STS_SUCCESS;
74 -- Assign all values in the passed "Record" parameter to the locally defined
75 -- "Record" variable.
76 l_Bsc_Kpi_Entity_Rec := p_Bsc_Kpi_Entity_Rec;
77
78 -- Assign certain default values if they are currently null.
79 IF l_Bsc_Kpi_Entity_Rec.Bsc_Edw_Flag IS NULL THEN
80 l_Bsc_Kpi_Entity_Rec.Bsc_Edw_Flag := 0;
81 END IF;
82
83 IF l_Bsc_Kpi_Entity_Rec.Bsc_Calendar_Id IS NULL THEN
84 l_Bsc_Kpi_Entity_Rec.Bsc_Calendar_Id := 1;
85 END IF;
86
87 -- Get the next id available for the current Kpi.
88 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := BSC_DIMENSION_LEVELS_PVT.Get_Next_Value( 'BSC_KPIS_B','INDICATOR');
89
90 -- If KPI name is null then assign default.
91 IF l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Name IS NOT NULL THEN
92 IF l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Help IS NULL THEN
93 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Help := l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Name;
94 END IF;
95 ELSE
96 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Name := 'Indicator ' || l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
97 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Help := l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Name;
98 END IF;
99
100 -- Set the prototype flag according with the System Stage
101 SELECT DECODE(property_value,1,1,3)
102 INTO l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Prototype_Flag
103 FROM bsc_sys_init
104 WHERE property_code = 'SYSTEM_STAGE';
105
106 if l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Group_Id is not null then
107
108 SELECT COUNT(1) INTO l_Count
109 FROM BSC_TAB_IND_GROUPS_B
110 WHERE IND_GROUP_ID = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Group_Id;
111
112 if l_count = 0 then
113 FND_MESSAGE.SET_NAME('BSC','BSC_INVALID_KGROUP_ID');
114 FND_MESSAGE.SET_TOKEN('BSC_KGROUP', l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Group_Id);
115 FND_MSG_PUB.ADD;
116 RAISE FND_API.G_EXC_ERROR;
117 end if;
118 else
119 FND_MESSAGE.SET_NAME('BSC','BSC_NO_KGROUP_ID_ENTERED');
120 FND_MSG_PUB.ADD;
121 RAISE FND_API.G_EXC_ERROR;
122 end if;
123
124 IF (l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Share_Flag <> 2) THEN
125 SELECT COUNT(B.Indicator)
126 INTO l_No_Ind
127 FROM BSC_TAB_IND_GROUPS_B A
128 , BSC_KPIS_B B
129 WHERE A.Ind_Group_Id = B.Ind_Group_Id
130 AND A.Group_Type = 1
131 AND A.Ind_Group_Id = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Group_Id
132 AND A.Tab_Id = -1
133 AND B.Prototype_Flag <> 2;
134 IF (l_No_Ind > 0) THEN
135 FND_MESSAGE.SET_NAME('BSC', 'BSC_GROUP_BESIDES_TO_BELOW');
136 FND_MESSAGE.SET_TOKEN('BESIDES_THE_NAME', BSC_APPS.Get_Lookup_Value('BSC_UI_BUILDER', 'BESIDE_THE_NAME'), TRUE);
137 FND_MSG_PUB.ADD;
138 RAISE FND_API.G_EXC_ERROR;
139 END IF;
140 END IF;
141
142
143 -- Call private version of procedure.
144 BSC_KPI_PVT.Create_Kpi(
145 p_commit => FND_API.G_FALSE
146 , p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
147 , x_return_status => x_return_status
148 , x_msg_count => x_msg_count
149 , x_msg_data => x_msg_data
150 );
151 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
152 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
153 END IF;
154
155 -- Call procedures to populate default metadata for the KPI.
156 Create_Kpi_Defaults( p_commit
157 ,l_Bsc_Kpi_Entity_Rec
158 ,x_return_status
159 ,x_msg_count
160 ,x_msg_data);
161 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
163 END IF;
164
165 x_Bsc_Kpi_Entity_Rec := l_Bsc_Kpi_Entity_Rec;
166
167 EXCEPTION
168 WHEN FND_API.G_EXC_ERROR THEN
169 IF (x_msg_data IS NULL) THEN
170 FND_MSG_PUB.Count_And_Get
171 ( p_encoded => FND_API.G_FALSE
172 , p_count => x_msg_count
173 , p_data => x_msg_data
174 );
175 END IF;
176 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
177 x_return_status := FND_API.G_RET_STS_ERROR;
178 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
179 IF (x_msg_data IS NULL) THEN
180 FND_MSG_PUB.Count_And_Get
181 ( p_encoded => FND_API.G_FALSE
182 , p_count => x_msg_count
183 , p_data => x_msg_data
184 );
185 END IF;
186 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
187 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
188 WHEN NO_DATA_FOUND THEN
189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
190 IF (x_msg_data IS NOT NULL) THEN
191 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi with parameter x_Bsc_Kpi_Entity_Rec ';
192 ELSE
193 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi with parameter x_Bsc_Kpi_Entity_Rec ';
194 END IF;
195 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
196 WHEN OTHERS THEN
197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
198 IF (x_msg_data IS NOT NULL) THEN
199 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi with parameter x_Bsc_Kpi_Entity_Rec ';
200 ELSE
201 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi with parameter x_Bsc_Kpi_Entity_Rec ';
202 END IF;
203 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
204 end Create_Kpi;
205
206
207 /************************************************************************************
208 ************************************************************************************/
209 --Modified procedure without OUT parameter
210 /*
211 *** IMPORTANT ***
212 Please review the following document, before proceeding to use this public API
213
214 http://files.oraclecorp.com/content/MySharedFolders/BSC5.2__ext/BSC5.2_ext-Public/
215 4_Build/1_Code_Performance/BSC/adrao_4064587/RootCauseAnalysis_Bug4064587.html
216
217 Please review and change for BSC5.3
218 */
219 procedure Create_Kpi(
220 p_commit IN varchar2 := FND_API.G_FALSE
221 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
222 ,x_return_status OUT NOCOPY varchar2
223 ,x_msg_count OUT NOCOPY number
224 ,x_msg_data OUT NOCOPY varchar2
225 ) is
226
227 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
228 l_Bsc_Kpi_Entity_Rec_Out BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
229
230 begin
231 FND_MSG_PUB.Initialize;
232 x_return_status := FND_API.G_RET_STS_SUCCESS;
233
234 l_Bsc_Kpi_Entity_Rec := p_Bsc_Kpi_Entity_Rec;
235
236 -- Call the create kpi API with OUT parameter for kpi entity record.
237 BSC_KPI_PUB.Create_Kpi( p_commit => p_commit
238 ,p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
239 ,x_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec_Out
240 ,x_return_status => x_return_status
241 ,x_msg_count => x_msg_count
242 ,x_msg_data => x_msg_data);
243 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
245 END IF;
246
247 EXCEPTION
248 WHEN FND_API.G_EXC_ERROR THEN
249 IF (x_msg_data IS NULL) THEN
250 FND_MSG_PUB.Count_And_Get
251 ( p_encoded => FND_API.G_FALSE
252 , p_count => x_msg_count
253 , p_data => x_msg_data
254 );
255 END IF;
256 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
257 x_return_status := FND_API.G_RET_STS_ERROR;
258 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
259 IF (x_msg_data IS NULL) THEN
260 FND_MSG_PUB.Count_And_Get
261 ( p_encoded => FND_API.G_FALSE
262 , p_count => x_msg_count
263 , p_data => x_msg_data
264 );
265 END IF;
266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
267 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
268 WHEN NO_DATA_FOUND THEN
269 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
270 IF (x_msg_data IS NOT NULL) THEN
271 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi ';
272 ELSE
273 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi ';
274 END IF;
275 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
276 WHEN OTHERS THEN
277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
278 IF (x_msg_data IS NOT NULL) THEN
279 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi ';
280 ELSE
281 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi ';
282 END IF;
283 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
284 end Create_Kpi;
285
286
287 /************************************************************************************
288 ************************************************************************************/
289 --new procedure. Initializing the kpi entity record.
290 procedure Initialize_Kpi_Entity_Rec(
291 p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
292 ,x_Bsc_Kpi_Entity_Rec OUT NOCOPY BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
293 ,x_return_status OUT NOCOPY varchar2
294 ,x_msg_count OUT NOCOPY number
295 ,x_msg_data OUT NOCOPY varchar2
296 ) is
297
298 begin
299 FND_MSG_PUB.Initialize;
300 x_return_status := FND_API.G_RET_STS_SUCCESS;
301
302 x_Bsc_Kpi_Entity_Rec := p_Bsc_Kpi_Entity_Rec;
303
304 -- set some default values.
305 x_Bsc_Kpi_Entity_Rec.Bsc_Anal_Group_Id := 0;
306 x_Bsc_Kpi_Entity_Rec.Bsc_Csf_Id := 0;
307 x_Bsc_Kpi_Entity_Rec.Bsc_Dependency_Flag := 0;
308 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Bm_Group_Id := 1;
309 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Color_Flag := 1;
310 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Config_Type := 1;
311 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Current_Period := 1;
312 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
313 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Display_Order := 0;
314 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Indicator_Type := 1;
315 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Level_Comb:= '?';
316 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Num_Years := 0;
317 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Id := 5;
318 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Previous_Years:= 0;
319 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Prototype_Color:= 'G';
320 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Prototype_Flag:= 3;
321 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Share_Flag := 0;
322 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 1;
323 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 1;
324 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Viewport_Default_Size:= 0;
325 x_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Viewport_Flag := 0;
326 x_Bsc_Kpi_Entity_Rec.Bsc_Language := 'US';
327 x_Bsc_Kpi_Entity_Rec.Bsc_Num_Options := 1;
328 x_Bsc_Kpi_Entity_Rec.Bsc_Parent_Anal_Id := 0;
329 x_Bsc_Kpi_Entity_Rec.Bsc_Source_Language:= 'US';
330 x_Bsc_Kpi_Entity_Rec.Created_By:= 0;
331 x_Bsc_Kpi_Entity_Rec.Last_Updated_By:= 0;
332 x_Bsc_Kpi_Entity_Rec.Last_Update_Login:= 0;
333 x_Bsc_Kpi_Entity_Rec.Last_Update_Login:= 0;
334
335 EXCEPTION
336 WHEN OTHERS THEN
337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
338 IF (x_msg_data IS NOT NULL) THEN
339 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Initialize_Kpi_Entity_Rec ';
340 ELSE
341 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Initialize_Kpi_Entity_Rec ';
342 END IF;
343 RAISE;
344 end Initialize_Kpi_Entity_Rec;
345
346
347 /************************************************************************************
348 ************************************************************************************/
349
350 procedure Retrieve_Kpi(
351 p_commit IN varchar2 := FND_API.G_FALSE
352 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
353 ,x_Bsc_Kpi_Entity_Rec IN OUT NOCOPY BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
354 ,x_return_status OUT NOCOPY varchar2
355 ,x_msg_count OUT NOCOPY number
356 ,x_msg_data OUT NOCOPY varchar2
357 ) is
358
359 begin
360 FND_MSG_PUB.Initialize;
361 x_return_status := FND_API.G_RET_STS_SUCCESS;
362 BSC_KPI_PVT.Retrieve_Kpi( p_commit
363 ,p_Bsc_Kpi_Entity_Rec
364 ,x_Bsc_Kpi_Entity_Rec
365 ,x_return_status
366 ,x_msg_count
367 ,x_msg_data);
368
369 Retrieve_Kpi_Defaults( p_commit
370 ,p_Bsc_Kpi_Entity_Rec
371 ,x_Bsc_Kpi_Entity_Rec
372 ,x_return_status
373 ,x_msg_count
374 ,x_msg_data);
375 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
376 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
377 END IF;
378
379 EXCEPTION
380 WHEN FND_API.G_EXC_ERROR THEN
381 IF (x_msg_data IS NULL) THEN
382 FND_MSG_PUB.Count_And_Get
383 ( p_encoded => FND_API.G_FALSE
384 , p_count => x_msg_count
385 , p_data => x_msg_data
386 );
387 END IF;
388 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
389 x_return_status := FND_API.G_RET_STS_ERROR;
390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
391 IF (x_msg_data IS NULL) THEN
392 FND_MSG_PUB.Count_And_Get
393 ( p_encoded => FND_API.G_FALSE
394 , p_count => x_msg_count
395 , p_data => x_msg_data
396 );
397 END IF;
398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
399 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
400 WHEN NO_DATA_FOUND THEN
401 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
402 IF (x_msg_data IS NOT NULL) THEN
403 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi ';
404 ELSE
405 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi ';
406 END IF;
407 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
408 WHEN OTHERS THEN
409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
410 IF (x_msg_data IS NOT NULL) THEN
411 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi ';
412 ELSE
413 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi ';
414 END IF;
415 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
416
417 end Retrieve_Kpi;
418
419 /************************************************************************************
420 ************************************************************************************/
421
422 procedure Update_Kpi(
423 p_commit IN varchar2 := FND_API.G_FALSE
424 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
425 ,x_return_status OUT NOCOPY varchar2
426 ,x_msg_count OUT NOCOPY number
427 ,x_msg_data OUT NOCOPY varchar2
428 ) is
429
430 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
431
432 TYPE Recdc_value IS REF CURSOR;
433 dc_value Recdc_value;
434
435 l_sql varchar2(1000);
436
437 l_count number;
438
439 CURSOR c_Select_Indicator IS
440 SELECT INDICATOR
441 FROM BSC_KPIS_B
442 WHERE SOURCE_INDICATOR = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
443 AND PROTOTYPE_FLAG <> BSC_KPI_PUB.Delete_Kpi_Flag;
444
445 begin
446 FND_MSG_PUB.Initialize;
447 x_return_status := FND_API.G_RET_STS_SUCCESS;
448
449 BSC_KPI_PVT.Update_Kpi( p_commit
450 ,p_Bsc_Kpi_Entity_Rec
451 ,x_return_status
452 ,x_msg_count
453 ,x_msg_data);
454
455 Update_Kpi_Defaults( p_commit
456 ,p_Bsc_Kpi_Entity_Rec
457 ,x_return_status
458 ,x_msg_count
459 ,x_msg_data);
460 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
461 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
462 END IF;
463
464 BSC_ANALYSIS_OPTION_PUB.Set_Default_Analysis_Option
465 (
466 p_commit => p_commit
467 , p_obj_id => p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
468 , p_Anal_Opt_Comb_Tbl => p_Bsc_Kpi_Entity_Rec.Bsc_Anal_Opt_Comb_Tbl
469 , p_Anal_Grp_Id => p_Bsc_Kpi_Entity_Rec.Bsc_Anal_Group_Id
470 , x_return_status => x_return_status
471 , x_msg_count => x_msg_count
472 , x_msg_data => x_msg_data
473 );
474 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
475 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
476 END IF;
477
478 -- if there are any shared KPIs update those also.
479 l_Bsc_Kpi_Entity_Rec := p_Bsc_Kpi_Entity_Rec;
480
481 FOR SrcInd IN c_Select_Indicator LOOP
482 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := SrcInd.INDICATOR;
483
484 BSC_KPI_PVT.Update_Kpi(
485 p_commit
486 , l_Bsc_Kpi_Entity_Rec
487 , x_return_status
488 , x_msg_count
489 , x_msg_data);
490 Update_Kpi_Defaults(
491 p_commit
492 , l_Bsc_Kpi_Entity_Rec
493 , x_return_status
494 , x_msg_count
495 , x_msg_data
496 );
497 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
498 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
499 END IF;
500
501 BSC_ANALYSIS_OPTION_PUB.Set_Default_Analysis_Option
502 (
503 p_commit => p_commit
504 , p_obj_id => l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
505 , p_Anal_Opt_Comb_Tbl => p_Bsc_Kpi_Entity_Rec.Bsc_Anal_Opt_Comb_Tbl
506 , p_Anal_Grp_Id => p_Bsc_Kpi_Entity_Rec.Bsc_Anal_Group_Id
507 , x_return_status => x_return_status
508 , x_msg_count => x_msg_count
509 , x_msg_data => x_msg_data
510 );
511 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
512 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
513 END IF;
514
515 END LOOP;
516
517 EXCEPTION
518 WHEN FND_API.G_EXC_ERROR THEN
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 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
527 x_return_status := FND_API.G_RET_STS_ERROR;
528 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
529 IF (x_msg_data IS NULL) THEN
530 FND_MSG_PUB.Count_And_Get
531 ( p_encoded => FND_API.G_FALSE
532 , p_count => x_msg_count
533 , p_data => x_msg_data
534 );
535 END IF;
536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
537 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
538 WHEN NO_DATA_FOUND THEN
539 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
540 IF (x_msg_data IS NOT NULL) THEN
541 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi ';
542 ELSE
543 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi ';
544 END IF;
545 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
546 WHEN OTHERS THEN
547 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
548 IF (x_msg_data IS NOT NULL) THEN
549 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi ';
550 ELSE
551 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi ';
552 END IF;
553 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
554 end Update_Kpi;
555
556 /************************************************************************************
557 Get_KPI_Dim_ShortNames: This API Retrieves all the dimension short names associated to a kpi
558
559 Modified the Cursor c_imported_dims for Bug#5081180 to ensure that the AG Report Objectives
560 are filtered out from the KPI Dimension SHort_Names.
561 ************************************************************************************/
562
563 PROCEDURE Get_KPI_Dim_ShortNames (
564 p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
565 ,x_dim_list OUT NOCOPY BSC_UTILITY.t_array_of_varchar2
566 ,x_return_status OUT NOCOPY varchar2
567 ,x_msg_count OUT NOCOPY number
568 ,x_msg_data OUT NOCOPY varchar2
569 )
570 IS
571 CURSOR c_imported_dims IS
572 SELECT
573 sys_dim.short_name
574 FROM
575 bsc_kpis_b kpi,
576 bsc_kpi_dim_groups kpi_dim,
577 bsc_sys_dim_groups_vl sys_dim
578 WHERE
579 kpi.indicator = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id AND
580 kpi.short_name IS NULL AND
581 kpi_dim.indicator = kpi.indicator AND
582 sys_dim.dim_group_id = kpi_dim.dim_group_id AND
583 bsc_bis_dimension_pub.get_dimension_source(sys_dim.short_name) = BSC_UTILITY.c_PMF;
584
585 l_regions VARCHAR2(32000);
586 l_row_count NUMBER := 0;
587
588 BEGIN
589 FOR c_dim IN c_imported_dims LOOP
590 l_row_count := l_row_count + 1;
591 x_dim_list(l_row_count) := c_dim.short_name;
592 END LOOP;
593 EXCEPTION
594 WHEN OTHERS THEN
595 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
596 IF (x_msg_data IS NOT NULL) THEN
597 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Get_KPI_Dim_ShortNames ';
598 ELSE
599 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Get_KPI_Dim_ShortNames ';
600 END IF;
601 END Get_KPI_Dim_ShortNames;
602
603 /************************************************************************************
604 Delete_Unused_Imported_Dims:- This API deletes all the dimensions imported while
605 adding Pmf Measures to an objective (DGRP dimgroups).They will be deleted if they
606 are not used in any reports
607 ************************************************************************************/
608
609 PROCEDURE Delete_Unused_Imported_Dims(
610 p_commit IN VARCHAR2 := FND_API.G_FALSE
611 ,p_dim_short_names IN BSC_UTILITY.t_array_of_varchar2
612 ,x_return_status OUT NOCOPY varchar2
613 ,x_msg_count OUT NOCOPY number
614 ,x_msg_data OUT NOCOPY varchar2
615 ) IS
616 l_regions VARCHAR2(32000);
617 BEGIN
618 FOR i in 1..p_dim_short_names.COUNT LOOP
619 l_regions := BSC_UTILITY.Is_Dim_In_AKReport(p_dim_short_names(i), BSC_UTILITY.c_DIMENSION);
620 IF (l_regions IS NULL) THEN
621 BSC_BIS_DIMENSION_PUB.Delete_Dimension
622 ( p_commit => FND_API.G_FALSE
623 , p_dim_short_name => p_dim_short_names(i)
624 , x_return_status => x_return_status
625 , x_msg_count => x_msg_count
626 , x_msg_data => x_msg_data
627 );
628 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
630 END IF;
631 END IF;
632 END LOOP;
633 IF (p_commit = FND_API.G_TRUE) THEN
634 COMMIT;
635 END IF;
636 EXCEPTION
637 WHEN OTHERS THEN
638 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
639 IF (x_msg_data IS NOT NULL) THEN
640 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Unused_Imported_Dims ';
641 ELSE
642 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Unused_Imported_Dims ';
643 END IF;
644 END Delete_Unused_Imported_Dims;
645
646 /************************************************************************************
647 ************************************************************************************/
648
649 PROCEDURE Delete_Kpi(
650 p_commit IN VARCHAR2 := FND_API.G_FALSE
651 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
652 ,x_return_status OUT NOCOPY VARCHAR2
653 ,x_msg_count OUT NOCOPY NUMBER
654 ,x_msg_data OUT NOCOPY VARCHAR2
655 ) IS
656 CURSOR c_Select_Indicator IS
657 SELECT INDICATOR,CONFIG_TYPE
658 FROM BSC_KPIS_B
659 WHERE SOURCE_INDICATOR = p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
660 AND PROTOTYPE_FLAG <> BSC_KPI_PUB.Delete_Kpi_Flag;
661
662 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
663
664 l_sql VARCHAR2(5000);
665
666 l_share_flag NUMBER;
667 l_count NUMBER;
668 l_ind_tab_count NUMBER;
669 l_tab_id NUMBER;
670 x_dim_short_names BSC_UTILITY.t_array_of_varchar2;
671 l_config_type BSC_KPIS_B.config_type%TYPE;
672
673 BEGIN
674 FND_MSG_PUB.Initialize;
675 x_return_status := FND_API.G_RET_STS_SUCCESS;
676 -- Assign all values in the passed "Record" parameter to the locally defined
677 -- "Record" variable.
678 l_Bsc_Kpi_Entity_Rec := p_Bsc_Kpi_Entity_Rec;
679
680 --Determine if Indicator assigned to a tab.
681 SELECT COUNT(indicator)
682 INTO l_ind_tab_count
683 FROM BSC_TAB_INDICATORS
684 WHERE indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
685
686
687 SELECT config_type
688 INTO l_config_type
689 from bsc_kpis_b
690 WHERE indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
691
692 -- if indicator assigned to tab get indicator.
693 IF(l_ind_tab_count > 0) THEN
694 SELECT tab_id
695 INTO l_tab_id
696 FROM BSC_TAB_INDICATORS
697 WHERE indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
698 END IF;
699
700 Get_KPI_Dim_ShortNames(l_Bsc_Kpi_Entity_Rec
701 ,x_dim_short_names
702 ,x_return_status
703 ,x_msg_count
704 ,x_msg_data);
705
706
707
708 Delete_Kpi_Defaults( p_commit
709 ,l_Bsc_Kpi_Entity_Rec
710 ,x_return_status
711 ,x_msg_count
712 ,x_msg_data);
713
714
715 Delete_Unused_Imported_Dims(p_commit
716 ,x_dim_short_names
717 ,x_return_status
718 ,x_msg_count
719 ,x_msg_data);
720
721
722 IF(l_tab_id IS NOT NULL ) THEN
723 BSC_CUSTOM_VIEW_PUB.Delete_Custom_View_Links
724 (
725 p_commit => FND_API.G_FALSE
726 , p_tab_id => l_tab_id
727 , p_obj_id => l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
728 , x_return_status => x_return_status
729 , x_msg_count => x_msg_count
730 , x_msg_data => x_msg_data
731 );
732
733 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
734 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
735 END IF;
736 END IF;
737
738 IF(l_config_type =7) THEN
739
740 BSC_KPI_PUB.Delete_Sim_Tree_Data
741 (
742 p_commit => p_commit
743 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
744 , x_return_status => x_return_status
745 , x_msg_count => x_msg_count
746 , x_msg_data => x_msg_data
747 );
748
749 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
750 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
751 END IF;
752
753
754 END IF;
755
756 BSC_KPI_PVT.Delete_Kpi( p_commit
757 ,l_Bsc_Kpi_Entity_Rec
758 ,x_return_status
759 ,x_msg_count
760 ,x_msg_data);
761 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
762 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
763 END IF;
764
765 -- Need to call procedure for list button logic.
766 BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels( p_commit
767 ,l_tab_id
768 ,x_return_status
769 ,x_msg_count
770 ,x_msg_data);
771 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
773 END IF;
774 -- Need to delete any shared KPIs.
775
776 FOR SrcInd IN c_Select_Indicator LOOP
777 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := SrcInd.INDICATOR;
778 l_config_type := SrcInd.config_type;
779
780
781 SELECT TAB_ID
782 INTO l_tab_id
783 FROM BSC_TAB_INDICATORS
784 WHERE INDICATOR = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
785
786
787 Delete_Kpi_Defaults( p_commit
788 ,l_Bsc_Kpi_Entity_Rec
789 ,x_return_status
790 ,x_msg_count
791 ,x_msg_data);
792 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
794 END IF;
795
796 IF(l_tab_id IS NOT NULL ) THEN
797 BSC_CUSTOM_VIEW_PUB.Delete_Custom_View_Links
798 (
799 p_commit => FND_API.G_FALSE
800 , p_tab_id => l_tab_id
801 , p_obj_id => l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
802 , x_return_status => x_return_status
803 , x_msg_count => x_msg_count
804 , x_msg_data => x_msg_data
805 );
806
807 IF ((x_return_status IS NOT NULL) AND (x_return_status <> FND_API.G_RET_STS_SUCCESS)) THEN
808 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
809 END IF;
810
811 END IF;
812
813 IF(l_config_type =7) THEN
814 BSC_KPI_PUB.Delete_Sim_Tree_Data
815 (
816 p_commit => p_commit
817 , p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
818 , x_return_status => x_return_status
819 , x_msg_count => x_msg_count
820 , x_msg_data => x_msg_data
821 );
822
823 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
825 END IF;
826
827 END IF;
828
829
830
831 BSC_KPI_PVT.Delete_Kpi( p_commit
832 , l_Bsc_Kpi_Entity_Rec
833 , x_return_status
834 , x_msg_count
835 , x_msg_data);
836
837 -- Need to call procedure for list button logic.
838 BSC_COMMON_DIM_LEVELS_PUB.Check_Common_Dim_Levels( p_commit
839 , l_tab_id
840 , x_return_status
841 , x_msg_count
842 , x_msg_data);
843 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
844 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
845 END IF;
846 END LOOP;
847
848 EXCEPTION
849 WHEN FND_API.G_EXC_ERROR THEN
850 IF (x_msg_data IS NULL) THEN
851 FND_MSG_PUB.Count_And_Get
852 ( p_encoded => FND_API.G_FALSE
853 , p_count => x_msg_count
854 , p_data => x_msg_data
855 );
856 END IF;
857 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
858 x_return_status := FND_API.G_RET_STS_ERROR;
859 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
860 IF (x_msg_data IS NULL) THEN
861 FND_MSG_PUB.Count_And_Get
862 ( p_encoded => FND_API.G_FALSE
863 , p_count => x_msg_count
864 , p_data => x_msg_data
865 );
866 END IF;
867 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
868 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
869 WHEN NO_DATA_FOUND THEN
870 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
871 IF (x_msg_data IS NOT NULL) THEN
872 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi ';
873 ELSE
874 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi ';
875 END IF;
876 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
877 WHEN OTHERS THEN
878 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
879 IF (x_msg_data IS NOT NULL) THEN
880 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi ';
881 ELSE
882 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi ';
883 END IF;
884 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
885
886 end Delete_Kpi;
887
888 /************************************************************************************
889 ************************************************************************************/
890
891 procedure Create_Kpi_Defaults(
892 p_commit IN varchar2 := FND_API.G_FALSE
893 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
894 ,x_return_status OUT NOCOPY varchar2
895 ,x_msg_count OUT NOCOPY number
896 ,x_msg_data OUT NOCOPY varchar2
897 ) is
898
899 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
900
901 l_Bsc_Dim_Rec BSC_DIMENSION_LEVELS_PUB.Bsc_Dim_Level_Rec_Type;
902
903 begin
904 FND_MSG_PUB.Initialize;
905 x_return_status := FND_API.G_RET_STS_SUCCESS;
906 -- Assign all values in the passed "Record" parameter to the locally defined
907 -- "Record" variable.
908 l_Bsc_Kpi_Entity_Rec := p_Bsc_Kpi_Entity_Rec;
909
910 -- Set values needed to populate default Dimension set.
911 -- l_Bsc_Dim_Rec.Bsc_Kpi_Id := p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
912 -- l_Bsc_Dim_Rec.Bsc_Dim_Set_Id := 0;
913 -- l_Bsc_Dim_Rec.Language := p_Bsc_Kpi_Entity_Rec.Bsc_Language;
914 -- l_Bsc_Dim_Rec.Source_Language := p_Bsc_Kpi_Entity_Rec.Bsc_Source_Language;
915
916 BSC_KPI_PVT.Create_Kpi_Defaults( p_commit
917 ,l_Bsc_Kpi_Entity_Rec
918 ,x_return_status
919 ,x_msg_count
920 ,x_msg_data);
921
922
923 -- Call procedure to Populate Kpi Properties.
924 Create_Kpi_Properties( p_commit
925 ,l_Bsc_Kpi_Entity_Rec
926 ,x_return_status
927 ,x_msg_count
928 ,x_msg_data);
929 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
931 END IF;
932 -- Call procedure to create default Analysis Group/Options.
933 Create_Kpi_Analysis( p_commit
934 ,l_Bsc_Kpi_Entity_Rec
935 ,x_return_status
936 ,x_msg_count
937 ,x_msg_data);
938
939 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
940 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
941 END IF;
942 -- Set some default values.
943 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Id := 5;
944 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Set_Id := 0;
945
946 -- Call procedure to create default periodicity.
947 Create_Kpi_Periodicity( p_commit
948 ,l_Bsc_Kpi_Entity_Rec
949 ,x_return_status
950 ,x_msg_count
951 ,x_msg_data);
952 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
953 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
954 END IF;
955 -- Call procedure to create default data for data_tables.
956 Create_Kpi_Data_Tables( p_commit
957 ,l_Bsc_Kpi_Entity_Rec
958 ,x_return_status
959 ,x_msg_count
960 ,x_msg_data);
961 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
962 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
963 END IF;
964 -- Call procedure to populate default calculations for the Kpi.
965 Create_Kpi_Calculations( p_commit
966 ,l_Bsc_Kpi_Entity_Rec
967 ,x_return_status
968 ,x_msg_count
969 ,x_msg_data);
970 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
971 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
972 END IF;
973 -- Call procedure to populate default Access
974 Create_Kpi_User_Access( p_commit
975 ,l_Bsc_Kpi_Entity_Rec
976 ,x_return_status
977 ,x_msg_count
978 ,x_msg_data);
979 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
980 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
981 END IF;
982 -- Call procedure to populate Kpi default values
983 Create_Kpi_Default_Values( p_commit
984 ,l_Bsc_Kpi_Entity_Rec
985 ,x_return_status
986 ,x_msg_count
987 ,x_msg_data);
988 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
989 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
990 END IF;
991 /* The following procedure should not be called when creating a KPI. Instead
992 it should be called directly from the UI or wrapper when assigning KPI to a Tab.
993
994
995 -- Call procedure to assign Kpi to a tab.
996 Create_Kpi_In_Tab( p_commit
997 ,l_Bsc_Kpi_Entity_Rec
998 ,x_return_status
999 ,x_msg_count
1000 ,x_msg_data);
1001
1002 */
1003
1004 EXCEPTION
1005 WHEN FND_API.G_EXC_ERROR THEN
1006 IF (x_msg_data IS NULL) THEN
1007 FND_MSG_PUB.Count_And_Get
1008 ( p_encoded => FND_API.G_FALSE
1009 , p_count => x_msg_count
1010 , p_data => x_msg_data
1011 );
1012 END IF;
1013 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1014 x_return_status := FND_API.G_RET_STS_ERROR;
1015 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1016 IF (x_msg_data IS NULL) THEN
1017 FND_MSG_PUB.Count_And_Get
1018 ( p_encoded => FND_API.G_FALSE
1019 , p_count => x_msg_count
1020 , p_data => x_msg_data
1021 );
1022 END IF;
1023 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1024 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1025 WHEN NO_DATA_FOUND THEN
1026 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1027 IF (x_msg_data IS NOT NULL) THEN
1028 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_Defaults ';
1029 ELSE
1030 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_Defaults ';
1031 END IF;
1032 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1033 WHEN OTHERS THEN
1034 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1035 IF (x_msg_data IS NOT NULL) THEN
1036 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_Defaults ';
1037 ELSE
1038 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_Defaults ';
1039 END IF;
1040 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1041 end Create_Kpi_Defaults;
1042
1043 /************************************************************************************
1044 ************************************************************************************/
1045
1046 procedure Retrieve_Kpi_Defaults(
1047 p_commit IN varchar2 := FND_API.G_FALSE
1048 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
1049 ,x_Bsc_Kpi_Entity_Rec IN OUT NOCOPY BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
1050 ,x_return_status OUT NOCOPY varchar2
1051 ,x_msg_count OUT NOCOPY number
1052 ,x_msg_data OUT NOCOPY varchar2
1053 ) is
1054
1055 begin
1056 FND_MSG_PUB.Initialize;
1057 x_return_status := FND_API.G_RET_STS_SUCCESS;
1058 BSC_KPI_PVT.Retrieve_Kpi_Defaults( p_commit
1059 ,p_Bsc_Kpi_Entity_Rec
1060 ,x_Bsc_Kpi_Entity_Rec
1061 ,x_return_status
1062 ,x_msg_count
1063 ,x_msg_data);
1064
1065 Retrieve_Kpi_Properties( p_commit
1066 ,p_Bsc_Kpi_Entity_Rec
1067 ,x_Bsc_Kpi_Entity_Rec
1068 ,x_return_status
1069 ,x_msg_count
1070 ,x_msg_data);
1071 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1072 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1073 END IF;
1074 Retrieve_Kpi_Periodicity( p_commit
1075 ,p_Bsc_Kpi_Entity_Rec
1076 ,x_Bsc_Kpi_Entity_Rec
1077 ,x_return_status
1078 ,x_msg_count
1079 ,x_msg_data);
1080 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1081 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1082 END IF;
1083 Retrieve_Kpi_Data_Tables( p_commit
1084 ,p_Bsc_Kpi_Entity_Rec
1085 ,x_Bsc_Kpi_Entity_Rec
1086 ,x_return_status
1087 ,x_msg_count
1088 ,x_msg_data);
1089 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1090 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1091 END IF;
1092 Retrieve_Kpi_Calculations( p_commit
1093 ,p_Bsc_Kpi_Entity_Rec
1094 ,x_Bsc_Kpi_Entity_Rec
1095 ,x_return_status
1096 ,x_msg_count
1097 ,x_msg_data);
1098 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1099 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1100 END IF;
1101 Retrieve_Kpi_User_Access( p_commit
1102 ,p_Bsc_Kpi_Entity_Rec
1103 ,x_Bsc_Kpi_Entity_Rec
1104 ,x_return_status
1105 ,x_msg_count
1106 ,x_msg_data);
1107 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1108 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1109 END IF;
1110 Retrieve_Kpi_Default_Values( p_commit
1111 ,p_Bsc_Kpi_Entity_Rec
1112 ,x_Bsc_Kpi_Entity_Rec
1113 ,x_return_status
1114 ,x_msg_count
1115 ,x_msg_data);
1116 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1117 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1118 END IF;
1119
1120 EXCEPTION
1121 WHEN FND_API.G_EXC_ERROR THEN
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 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1130 x_return_status := FND_API.G_RET_STS_ERROR;
1131 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1132 IF (x_msg_data IS NULL) THEN
1133 FND_MSG_PUB.Count_And_Get
1134 ( p_encoded => FND_API.G_FALSE
1135 , p_count => x_msg_count
1136 , p_data => x_msg_data
1137 );
1138 END IF;
1139 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1140 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1141 WHEN NO_DATA_FOUND THEN
1142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1143 IF (x_msg_data IS NOT NULL) THEN
1144 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_Defaults ';
1145 ELSE
1146 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_Defaults ';
1147 END IF;
1148 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1149 WHEN OTHERS THEN
1150 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1151 IF (x_msg_data IS NOT NULL) THEN
1152 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_Defaults ';
1153 ELSE
1154 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_Defaults ';
1155 END IF;
1156 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1157 end Retrieve_Kpi_Defaults;
1158
1159 /************************************************************************************
1160 ************************************************************************************/
1161
1162 procedure Update_Kpi_Defaults(
1163 p_commit IN varchar2 := FND_API.G_FALSE
1164 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
1165 ,x_return_status OUT NOCOPY varchar2
1166 ,x_msg_count OUT NOCOPY number
1167 ,x_msg_data OUT NOCOPY varchar2
1168 ) is
1169 begin
1170 FND_MSG_PUB.Initialize;
1171 x_return_status := FND_API.G_RET_STS_SUCCESS;
1172 IF (p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Property_Code IS NOT NULL) THEN
1173 Update_Kpi_Properties( p_commit
1174 ,p_Bsc_Kpi_Entity_Rec
1175 ,x_return_status
1176 ,x_msg_count
1177 ,x_msg_data);
1178 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1179 -- if updation fails, create it
1180 BSC_KPI_PVT.Create_Kpi_Properties( p_commit
1181 ,p_Bsc_Kpi_Entity_Rec
1182 ,x_return_status
1183 ,x_msg_count
1184 ,x_msg_data);
1185 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1187 END IF;
1188 END IF;
1189 END IF;
1190 IF (p_Bsc_Kpi_Entity_Rec.Bsc_Anal_Group_Id IS NOT NULL) THEN
1191 Update_Kpi_Analysis( p_commit
1192 ,p_Bsc_Kpi_Entity_Rec
1193 ,x_return_status
1194 ,x_msg_count
1195 ,x_msg_data);
1196 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1198 END IF;
1199 END IF;
1200 IF (p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Id IS NOT NULL) THEN
1201 Update_Kpi_Periodicity( p_commit
1202 ,p_Bsc_Kpi_Entity_Rec
1203 ,x_return_status
1204 ,x_msg_count
1205 ,x_msg_data);
1206 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1208 END IF;
1209 END IF;
1210 IF ((p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Set_Id IS NOT NULL) AND
1211 (p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Id IS NOT NULL)) THEN
1212 Update_Kpi_Data_Tables( p_commit
1213 ,p_Bsc_Kpi_Entity_Rec
1214 ,x_return_status
1215 ,x_msg_count
1216 ,x_msg_data);
1217 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1219 END IF;
1220 END IF;
1221 IF (p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id IS NOT NULL) THEN
1222 Update_Kpi_Calculations( p_commit
1223 ,p_Bsc_Kpi_Entity_Rec
1224 ,x_return_status
1225 ,x_msg_count
1226 ,x_msg_data);
1227 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1229 END IF;
1230 END IF;
1231 IF (p_Bsc_Kpi_Entity_Rec.Bsc_Responsibility_Id IS NOT NULL) THEN
1232 Update_Kpi_User_Access( p_commit
1233 ,p_Bsc_Kpi_Entity_Rec
1234 ,x_return_status
1235 ,x_msg_count
1236 ,x_msg_data);
1237 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1238 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1239 END IF;
1240 END IF;
1241 IF (p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value IS NOT NULL) THEN
1242 Update_Kpi_Default_Values( p_commit
1243 ,p_Bsc_Kpi_Entity_Rec
1244 ,x_return_status
1245 ,x_msg_count
1246 ,x_msg_data);
1247 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1248 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1249 END IF;
1250 END IF;
1251
1252 EXCEPTION
1253 WHEN FND_API.G_EXC_ERROR THEN
1254 IF (x_msg_data IS NULL) THEN
1255 FND_MSG_PUB.Count_And_Get
1256 ( p_encoded => FND_API.G_FALSE
1257 , p_count => x_msg_count
1258 , p_data => x_msg_data
1259 );
1260 END IF;
1261 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1262 x_return_status := FND_API.G_RET_STS_ERROR;
1263 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1264 IF (x_msg_data IS NULL) THEN
1265 FND_MSG_PUB.Count_And_Get
1266 ( p_encoded => FND_API.G_FALSE
1267 , p_count => x_msg_count
1268 , p_data => x_msg_data
1269 );
1270 END IF;
1271 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1272 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1273 WHEN NO_DATA_FOUND THEN
1274 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1275 IF (x_msg_data IS NOT NULL) THEN
1276 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_Defaults ';
1277 ELSE
1278 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_Defaults ';
1279 END IF;
1280 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1281 WHEN OTHERS THEN
1282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1283 IF (x_msg_data IS NOT NULL) THEN
1284 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_Defaults ';
1285 ELSE
1286 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_Defaults ';
1287 END IF;
1288 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1289 end Update_Kpi_Defaults;
1290
1291
1292 PROCEDURE Delete_Obj_Kpi_Measure_Props (
1293 p_commit IN VARCHAR2 := FND_API.G_FALSE
1294 , p_bsc_kpi_entity_rec IN BSC_KPI_PUB.bsc_kpi_entity_rec
1295 , x_return_status OUT NOCOPY VARCHAR2
1296 , x_msg_count OUT NOCOPY NUMBER
1297 , x_msg_data OUT NOCOPY VARCHAR2
1298 )
1299 IS
1300 BEGIN
1301
1302 FND_MSG_PUB.Initialize;
1303 x_return_status := FND_API.G_RET_STS_SUCCESS;
1304
1305 -- Delete from BSC_KPI_MEASURE_PROPS
1306 BSC_KPI_MEASURE_PROPS_PUB.Delete_Obj_Kpi_Measure_Props (
1307 p_commit => p_commit
1308 , p_objective_id => p_bsc_kpi_entity_rec.bsc_kpi_id
1309 , p_cascade_shared => FALSE
1310 , x_return_status => x_return_status
1311 , x_msg_count => x_msg_count
1312 , x_msg_data => x_msg_data
1313 );
1314 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1315 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1316 END IF;
1317
1318 -- Delete from BSC_KPI_MEASURE_WEIGHTS
1319 BSC_KPI_MEASURE_WEIGHTS_PUB.Del_Obj_Kpi_Measure_Weights (
1320 p_commit => p_commit
1321 , p_objective_id => p_bsc_kpi_entity_rec.bsc_kpi_id
1322 , p_cascade_shared => FALSE
1323 , x_return_status => x_return_status
1324 , x_msg_count => x_msg_count
1325 , x_msg_data => x_msg_data
1326 );
1327 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1328 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1329 END IF;
1330
1331 -- Delete from BSC_COLOR_TYPE_PROPS and BSC_COLOR_RANGES
1332 -- TBD:ankgoel
1333 BSC_COLOR_RANGES_PUB.Delete_Color_Prop_Ranges (
1334 p_commit => p_commit
1335 , p_objective_id => p_bsc_kpi_entity_rec.bsc_kpi_id
1336 , p_cascade_shared => FALSE
1337 , x_return_status => x_return_status
1338 , x_msg_count => x_msg_count
1339 , x_msg_data => x_msg_data
1340 );
1341 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1343 END IF;
1344
1345 EXCEPTION
1346 WHEN FND_API.G_EXC_ERROR THEN
1347 IF (x_msg_data IS NULL) THEN
1348 FND_MSG_PUB.Count_And_Get
1349 ( p_encoded => FND_API.G_FALSE
1350 , p_count => x_msg_count
1351 , p_data => x_msg_data
1352 );
1353 END IF;
1354 x_return_status := FND_API.G_RET_STS_ERROR;
1355 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1356 IF (x_msg_data IS NULL) THEN
1357 FND_MSG_PUB.Count_And_Get
1358 ( p_encoded => FND_API.G_FALSE
1359 , p_count => x_msg_count
1360 , p_data => x_msg_data
1361 );
1362 END IF;
1363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1364 WHEN NO_DATA_FOUND THEN
1365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1366 IF (x_msg_data IS NOT NULL) THEN
1367 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Obj_Kpi_Measure_Props ';
1368 ELSE
1369 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Obj_Kpi_Measure_Props ';
1370 END IF;
1371 WHEN OTHERS THEN
1372 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1373 IF (x_msg_data IS NOT NULL) THEN
1374 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Obj_Kpi_Measure_Props ';
1375 ELSE
1376 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Obj_Kpi_Measure_Props ';
1377 END IF;
1378 END Delete_Obj_Kpi_Measure_Props;
1379 /************************************************************************************
1380 ************************************************************************************/
1381
1382 procedure Delete_Objective_Color_Data(
1383 p_commit IN varchar2 := FND_API.G_FALSE
1384 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
1385 ,x_return_status OUT NOCOPY varchar2
1386 ,x_msg_count OUT NOCOPY number
1387 ,x_msg_data OUT NOCOPY varchar2
1388 ) is
1389
1390 l_count number;
1391
1392 begin
1393 FND_MSG_PUB.Initialize;
1394 x_return_status := FND_API.G_RET_STS_SUCCESS;
1395 BSC_KPI_PVT.Delete_Objective_Color_Data( p_commit
1396 ,p_Bsc_Kpi_Entity_Rec
1397 ,x_return_status
1398 ,x_msg_count
1399 ,x_msg_data);
1400 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1401 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1402 END IF;
1403
1404 EXCEPTION
1405 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1406 IF (x_msg_data IS NULL) THEN
1407 FND_MSG_PUB.Count_And_Get
1408 ( p_encoded => FND_API.G_FALSE
1409 , p_count => x_msg_count
1410 , p_data => x_msg_data
1411 );
1412 END IF;
1413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1414 WHEN OTHERS THEN
1415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1416 IF (x_msg_data IS NOT NULL) THEN
1417 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Objective_Color_Data ';
1418 ELSE
1419 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Objective_Color_Data ';
1420 END IF;
1421 end Delete_Objective_Color_Data;
1422
1423
1424 /************************************************************************************
1425 ************************************************************************************/
1426
1427 procedure Delete_Kpi_Defaults(
1428 p_commit IN varchar2 := FND_API.G_FALSE
1429 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
1430 ,x_return_status OUT NOCOPY varchar2
1431 ,x_msg_count OUT NOCOPY number
1432 ,x_msg_data OUT NOCOPY varchar2
1433 ) is
1434
1435 l_Bsc_Dim_Set_Rec BSC_DIMENSION_SETS_PUB.Bsc_Dim_Set_Rec_Type;
1436
1437 TYPE Rec_dc_value IS REF CURSOR;
1438 dc_value1 Rec_dc_value;
1439 dc_value2 Rec_dc_value;
1440
1441 l_sql1 varchar2(1000);
1442 l_sql2 varchar2(1000);
1443 begin
1444 FND_MSG_PUB.Initialize;
1445 x_return_status := FND_API.G_RET_STS_SUCCESS;
1446 -- Call procedure to Delete Dimension sets and assign appropriate values to the Record.
1447 l_Bsc_Dim_Set_Rec.Bsc_Kpi_Id := p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
1448
1449 -- Get the dimension sets for the KPI.
1450 l_sql1 := 'select distinct(dim_set_id) ' ||
1451 ' from BSC_KPI_DIM_SETS_TL ' ||
1452 ' where indicator = :1';
1453
1454 open dc_value1 for l_sql1 using l_Bsc_Dim_Set_Rec.Bsc_Kpi_Id;
1455 loop
1456 fetch dc_value1 into l_Bsc_Dim_Set_Rec.Bsc_Dim_Set_Id;
1457 exit when dc_value1%NOTFOUND;
1458
1459 BSC_DIMENSION_SETS_PVT.Delete_Dim_Levels( p_commit
1460 ,l_Bsc_Dim_Set_Rec
1461 ,x_return_status
1462 ,x_msg_count
1463 ,x_msg_data);
1464
1465 BSC_DIMENSION_SETS_PUB.Delete_Bsc_Kpi_Dim_Sets_Tl( p_commit
1466 ,l_Bsc_Dim_Set_Rec
1467 ,x_return_status
1468 ,x_msg_count
1469 ,x_msg_data);
1470 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1471 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1472 END IF;
1473
1474 -- Before we delete the Dimension set we need to delete the Dimension Groups associated
1475 -- with the dimension set. In order to get all groups in the Dim. set we need to use
1476 -- a cursor to query the database.
1477 l_sql2 := 'select distinct(dim_group_id) ' ||
1478 ' from BSC_KPI_DIM_GROUPS ' ||
1479 ' where indicator = :1' ||
1480 ' and dim_set_id = :2';
1481
1482 open dc_value2 for l_sql2 using l_Bsc_Dim_Set_Rec.Bsc_Kpi_Id, l_Bsc_Dim_Set_Rec.Bsc_Dim_Set_Id;
1483 loop
1484 fetch dc_value2 into l_Bsc_Dim_Set_Rec.Bsc_Dim_Level_Group_Id;
1485 exit when dc_value2%NOTFOUND;
1486 -- call the procedure to delete dimension groups frin dimension sets;
1487 BSC_DIMENSION_SETS_PUB.Delete_Dim_Group_In_Dset( p_commit
1488 ,l_Bsc_Dim_Set_Rec
1489 ,x_return_status
1490 ,x_msg_count
1491 ,x_msg_data);
1492
1493 end loop;
1494 close dc_value2;
1495
1496 end loop;
1497 close dc_value1;
1498
1499 -- Call procedure to Delete Kpi Properties.
1500 Delete_Kpi_Properties( p_commit
1501 ,p_Bsc_Kpi_Entity_Rec
1502 ,x_return_status
1503 ,x_msg_count
1504 ,x_msg_data);
1505 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1507 END IF;
1508
1509 Delete_Objective_Color_Data( p_commit
1510 ,p_Bsc_Kpi_Entity_Rec
1511 ,x_return_status
1512 ,x_msg_count
1513 ,x_msg_data);
1514 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1515 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1516 END IF;
1517
1518 -- Call procedure to delete default Analysis Group/Options.
1519 Delete_Kpi_Analysis( p_commit
1520 ,p_Bsc_Kpi_Entity_Rec
1521 ,x_return_status
1522 ,x_msg_count
1523 ,x_msg_data);
1524
1525 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1526 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1527 END IF;
1528 -- Call procedure to delete default periodicity.
1529 Delete_Kpi_Periodicity( p_commit
1530 ,p_Bsc_Kpi_Entity_Rec
1531 ,x_return_status
1532 ,x_msg_count
1533 ,x_msg_data);
1534 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1535 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1536 END IF;
1537 -- Call procedure to delete default data for data_tables.
1538 Delete_Kpi_Data_Tables( p_commit
1539 ,p_Bsc_Kpi_Entity_Rec
1540 ,x_return_status
1541 ,x_msg_count
1542 ,x_msg_data);
1543 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1544 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1545 END IF;
1546 -- Call procedure to delete default calculations for the Kpi.
1547 Delete_Kpi_Calculations( p_commit
1548 ,p_Bsc_Kpi_Entity_Rec
1549 ,x_return_status
1550 ,x_msg_count
1551 ,x_msg_data);
1552 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1554 END IF;
1555 -- Call procedure to delete Kpi from Tab.
1556 Delete_Kpi_In_Tab( p_commit
1557 ,p_Bsc_Kpi_Entity_Rec
1558 ,x_return_status
1559 ,x_msg_count
1560 ,x_msg_data);
1561 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1562 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1563 END IF;
1564 -- Call procedure to delete default Access
1565 Delete_Kpi_User_Access( p_commit
1566 ,p_Bsc_Kpi_Entity_Rec
1567 ,x_return_status
1568 ,x_msg_count
1569 ,x_msg_data);
1570 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1571 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1572 END IF;
1573 -- Call procedure to delete Kpi default values
1574 Delete_Kpi_Default_Values( p_commit
1575 ,p_Bsc_Kpi_Entity_Rec
1576 ,x_return_status
1577 ,x_msg_count
1578 ,x_msg_data);
1579 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1581 END IF;
1582
1583 Delete_Ind_Extra_Tables
1584 (
1585 p_commit => p_commit
1586 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
1587 , x_return_status => x_return_status
1588 , x_msg_count => x_msg_count
1589 , x_msg_data => x_msg_data
1590
1591 );
1592 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1594 END IF;
1595
1596 EXCEPTION
1597 WHEN FND_API.G_EXC_ERROR THEN
1598 IF (x_msg_data IS NULL) THEN
1599 FND_MSG_PUB.Count_And_Get
1600 ( p_encoded => FND_API.G_FALSE
1601 , p_count => x_msg_count
1602 , p_data => x_msg_data
1603 );
1604 END IF;
1605 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1606 x_return_status := FND_API.G_RET_STS_ERROR;
1607 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1608 IF (x_msg_data IS NULL) THEN
1609 FND_MSG_PUB.Count_And_Get
1610 ( p_encoded => FND_API.G_FALSE
1611 , p_count => x_msg_count
1612 , p_data => x_msg_data
1613 );
1614 END IF;
1615 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1616 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1617 WHEN NO_DATA_FOUND THEN
1618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1619 IF (x_msg_data IS NOT NULL) THEN
1620 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_Defaults ';
1621 ELSE
1622 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_Defaults ';
1623 END IF;
1624 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1625 WHEN OTHERS THEN
1626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1627 IF (x_msg_data IS NOT NULL) THEN
1628 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_Defaults ';
1629 ELSE
1630 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_Defaults ';
1631 END IF;
1632 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1633 end Delete_Kpi_Defaults;
1634
1635 /************************************************************************************
1636 ************************************************************************************/
1637
1638 procedure Create_Kpi_Properties(
1639 p_commit IN varchar2 := FND_API.G_FALSE
1640 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
1641 ,x_return_status OUT NOCOPY varchar2
1642 ,x_msg_count OUT NOCOPY number
1643 ,x_msg_data OUT NOCOPY varchar2
1644 ) is
1645
1646 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
1647 l_count NUMBER;
1648
1649 begin
1650 FND_MSG_PUB.Initialize;
1651 x_return_status := FND_API.G_RET_STS_SUCCESS;
1652 -- Assign all values in the passed "Record" parameter to the locally defined
1653 -- "Record" variable.
1654 l_Bsc_Kpi_Entity_Rec := p_Bsc_Kpi_Entity_Rec;
1655
1656 -- Set the values for the Properties table and call the private version of the API.
1657 -- This has to be done numerous times.
1658
1659 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Property_Code := 'LOCK_INDICATOR';
1660 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Property_Value := 0;
1661 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Secondary_Value := '';
1662
1663 BSC_KPI_PVT.Create_Kpi_Properties( p_commit
1664 ,l_Bsc_Kpi_Entity_Rec
1665 ,x_return_status
1666 ,x_msg_count
1667 ,x_msg_data);
1668
1669 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Property_Code := BSC_KPI_PUB.Benchmark_Kpi_Property;
1670 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Property_Value := BSC_KPI_PUB.Benchmark_Kpi_Line_Graph;
1671 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Secondary_Value := '';
1672
1673 BSC_KPI_PVT.Create_Kpi_Properties( p_commit
1674 ,l_Bsc_Kpi_Entity_Rec
1675 ,x_return_status
1676 ,x_msg_count
1677 ,x_msg_data);
1678
1679
1680 ---We need to check if the
1681 IF(l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Config_Type =BSC_BIS_KPI_CRUD_PUB.C_SIM_INDICATOR_CONFIG_TYPE)THEN
1682 SELECT COUNT(0)
1683 INTO l_count
1684 FROM bsc_kpi_properties
1685 WHERE indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
1686 AND property_code =BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID;
1687
1688 IF(l_count=0)THEN
1689
1690 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Property_Code := BSC_SIMULATION_VIEW_PUB.c_SIM_NODE_ID;
1691 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Property_Value := BSC_SIMULATION_VIEW_PUB.c_DEFAULT_DATASET_ID;
1692 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Secondary_Value := '';
1693
1694 BSC_KPI_PVT.Create_Kpi_Properties( p_commit
1695 ,l_Bsc_Kpi_Entity_Rec
1696 ,x_return_status
1697 ,x_msg_count
1698 ,x_msg_data);
1699
1700 END IF;
1701 END IF;
1702
1703
1704 EXCEPTION
1705 WHEN FND_API.G_EXC_ERROR THEN
1706 IF (x_msg_data IS NULL) THEN
1707 FND_MSG_PUB.Count_And_Get
1708 ( p_encoded => FND_API.G_FALSE
1709 , p_count => x_msg_count
1710 , p_data => x_msg_data
1711 );
1712 END IF;
1713 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1714 x_return_status := FND_API.G_RET_STS_ERROR;
1715 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1716 IF (x_msg_data IS NULL) THEN
1717 FND_MSG_PUB.Count_And_Get
1718 ( p_encoded => FND_API.G_FALSE
1719 , p_count => x_msg_count
1720 , p_data => x_msg_data
1721 );
1722 END IF;
1723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1724 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1725 WHEN NO_DATA_FOUND THEN
1726 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1727 IF (x_msg_data IS NOT NULL) THEN
1728 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_Properties ';
1729 ELSE
1730 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_Properties ';
1731 END IF;
1732 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1733 WHEN OTHERS THEN
1734 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1735 IF (x_msg_data IS NOT NULL) THEN
1736 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_Properties ';
1737 ELSE
1738 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_Properties ';
1739 END IF;
1740 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1741 end Create_Kpi_Properties;
1742
1743 /************************************************************************************
1744 ************************************************************************************/
1745
1746 procedure Retrieve_Kpi_Properties(
1747 p_commit IN varchar2 := FND_API.G_FALSE
1748 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
1749 ,x_Bsc_Kpi_Entity_Rec IN OUT NOCOPY BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
1750 ,x_return_status OUT NOCOPY varchar2
1751 ,x_msg_count OUT NOCOPY number
1752 ,x_msg_data OUT NOCOPY varchar2
1753 ) is
1754
1755 begin
1756 FND_MSG_PUB.Initialize;
1757 x_return_status := FND_API.G_RET_STS_SUCCESS;
1758 BSC_KPI_PVT.Retrieve_Kpi_Properties( p_commit
1759 ,p_Bsc_Kpi_Entity_Rec
1760 ,x_Bsc_Kpi_Entity_Rec
1761 ,x_return_status
1762 ,x_msg_count
1763 ,x_msg_data);
1764
1765 EXCEPTION
1766 WHEN FND_API.G_EXC_ERROR THEN
1767 IF (x_msg_data IS NULL) THEN
1768 FND_MSG_PUB.Count_And_Get
1769 ( p_encoded => FND_API.G_FALSE
1770 , p_count => x_msg_count
1771 , p_data => x_msg_data
1772 );
1773 END IF;
1774 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1775 x_return_status := FND_API.G_RET_STS_ERROR;
1776 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1777 IF (x_msg_data IS NULL) THEN
1778 FND_MSG_PUB.Count_And_Get
1779 ( p_encoded => FND_API.G_FALSE
1780 , p_count => x_msg_count
1781 , p_data => x_msg_data
1782 );
1783 END IF;
1784 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1785 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1786 WHEN NO_DATA_FOUND THEN
1787 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1788 IF (x_msg_data IS NOT NULL) THEN
1789 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_Properties ';
1790 ELSE
1791 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_Properties ';
1792 END IF;
1793 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1794 WHEN OTHERS THEN
1795 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1796 IF (x_msg_data IS NOT NULL) THEN
1797 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_Properties ';
1798 ELSE
1799 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_Properties ';
1800 END IF;
1801 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1802 end Retrieve_Kpi_Properties;
1803
1804 /************************************************************************************
1805 ************************************************************************************/
1806
1807 procedure Update_Kpi_Properties(
1808 p_commit IN varchar2 := FND_API.G_FALSE
1809 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
1810 ,x_return_status OUT NOCOPY varchar2
1811 ,x_msg_count OUT NOCOPY number
1812 ,x_msg_data OUT NOCOPY varchar2
1813 ) is
1814
1815 begin
1816 FND_MSG_PUB.Initialize;
1817 x_return_status := FND_API.G_RET_STS_SUCCESS;
1818 BSC_KPI_PVT.Update_Kpi_Properties( p_commit
1819 ,p_Bsc_Kpi_Entity_Rec
1820 ,x_return_status
1821 ,x_msg_count
1822 ,x_msg_data);
1823
1824 EXCEPTION
1825 WHEN FND_API.G_EXC_ERROR THEN
1826 IF (x_msg_data IS NULL) THEN
1827 FND_MSG_PUB.Count_And_Get
1828 ( p_encoded => FND_API.G_FALSE
1829 , p_count => x_msg_count
1830 , p_data => x_msg_data
1831 );
1832 END IF;
1833 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1834 x_return_status := FND_API.G_RET_STS_ERROR;
1835 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1836 IF (x_msg_data IS NULL) THEN
1837 FND_MSG_PUB.Count_And_Get
1838 ( p_encoded => FND_API.G_FALSE
1839 , p_count => x_msg_count
1840 , p_data => x_msg_data
1841 );
1842 END IF;
1843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1844 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1845 WHEN NO_DATA_FOUND THEN
1846 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1847 IF (x_msg_data IS NOT NULL) THEN
1848 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_Properties ';
1849 ELSE
1850 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_Properties ';
1851 END IF;
1852 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1853 WHEN OTHERS THEN
1854 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1855 IF (x_msg_data IS NOT NULL) THEN
1856 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_Properties ';
1857 ELSE
1858 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_Properties ';
1859 END IF;
1860 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1861 end Update_Kpi_Properties;
1862
1863 /************************************************************************************
1864 ************************************************************************************/
1865
1866 procedure Delete_Kpi_Properties(
1867 p_commit IN varchar2 := FND_API.G_FALSE
1868 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
1869 ,x_return_status OUT NOCOPY varchar2
1870 ,x_msg_count OUT NOCOPY number
1871 ,x_msg_data OUT NOCOPY varchar2
1872 ) is
1873
1874 l_count number;
1875
1876 begin
1877 FND_MSG_PUB.Initialize;
1878 x_return_status := FND_API.G_RET_STS_SUCCESS;
1879 BSC_KPI_PVT.Delete_Kpi_Properties( p_commit
1880 ,p_Bsc_Kpi_Entity_Rec
1881 ,x_return_status
1882 ,x_msg_count
1883 ,x_msg_data);
1884
1885
1886 EXCEPTION
1887 WHEN FND_API.G_EXC_ERROR THEN
1888 IF (x_msg_data IS NULL) THEN
1889 FND_MSG_PUB.Count_And_Get
1890 ( p_encoded => FND_API.G_FALSE
1891 , p_count => x_msg_count
1892 , p_data => x_msg_data
1893 );
1894 END IF;
1895 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1896 x_return_status := FND_API.G_RET_STS_ERROR;
1897 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1898 IF (x_msg_data IS NULL) THEN
1899 FND_MSG_PUB.Count_And_Get
1900 ( p_encoded => FND_API.G_FALSE
1901 , p_count => x_msg_count
1902 , p_data => x_msg_data
1903 );
1904 END IF;
1905 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1906 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1907 WHEN NO_DATA_FOUND THEN
1908 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1909 IF (x_msg_data IS NOT NULL) THEN
1910 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_Properties ';
1911 ELSE
1912 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_Properties ';
1913 END IF;
1914 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1915 WHEN OTHERS THEN
1916 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1917 IF (x_msg_data IS NOT NULL) THEN
1918 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_Properties ';
1919 ELSE
1920 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_Properties ';
1921 END IF;
1922 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1923 end Delete_Kpi_Properties;
1924
1925 /************************************************************************************
1926 ************************************************************************************/
1927
1928 procedure Create_Kpi_Analysis(
1929 p_commit IN varchar2 := FND_API.G_FALSE
1930 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
1931 ,x_return_status OUT NOCOPY varchar2
1932 ,x_msg_count OUT NOCOPY number
1933 ,x_msg_data OUT NOCOPY varchar2
1934 ) is
1935
1936 begin
1937 FND_MSG_PUB.Initialize;
1938 x_return_status := FND_API.G_RET_STS_SUCCESS;
1939 -- Call private version of procedure.
1940 BSC_KPI_PVT.Create_Kpi_Analysis( p_commit
1941 ,p_Bsc_Kpi_Entity_Rec
1942 ,x_return_status
1943 ,x_msg_count
1944 ,x_msg_data);
1945
1946 EXCEPTION
1947 WHEN FND_API.G_EXC_ERROR THEN
1948 IF (x_msg_data IS NULL) THEN
1949 FND_MSG_PUB.Count_And_Get
1950 ( p_encoded => FND_API.G_FALSE
1951 , p_count => x_msg_count
1952 , p_data => x_msg_data
1953 );
1954 END IF;
1955 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
1956 x_return_status := FND_API.G_RET_STS_ERROR;
1957 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1958 IF (x_msg_data IS NULL) THEN
1959 FND_MSG_PUB.Count_And_Get
1960 ( p_encoded => FND_API.G_FALSE
1961 , p_count => x_msg_count
1962 , p_data => x_msg_data
1963 );
1964 END IF;
1965 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1966 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
1967 WHEN NO_DATA_FOUND THEN
1968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1969 IF (x_msg_data IS NOT NULL) THEN
1970 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_Analysis ';
1971 ELSE
1972 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_Analysis ';
1973 END IF;
1974 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
1975 WHEN OTHERS THEN
1976 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1977 IF (x_msg_data IS NOT NULL) THEN
1978 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_Analysis ';
1979 ELSE
1980 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_Analysis ';
1981 END IF;
1982 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
1983 end Create_Kpi_Analysis;
1984
1985 /************************************************************************************
1986 ************************************************************************************/
1987
1988 procedure Retrieve_Kpi_Analysis(
1989 p_commit IN varchar2 := FND_API.G_FALSE
1990 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
1991 ,x_Bsc_Kpi_Entity_Rec IN OUT NOCOPY BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
1992 ,x_return_status OUT NOCOPY varchar2
1993 ,x_msg_count OUT NOCOPY number
1994 ,x_msg_data OUT NOCOPY varchar2
1995 ) is
1996
1997 begin
1998 FND_MSG_PUB.Initialize;
1999 x_return_status := FND_API.G_RET_STS_SUCCESS;
2000 BSC_KPI_PVT.Retrieve_Kpi_Analysis( p_commit
2001 ,p_Bsc_Kpi_Entity_Rec
2002 ,x_Bsc_Kpi_Entity_Rec
2003 ,x_return_status
2004 ,x_msg_count
2005 ,x_msg_data);
2006
2007 EXCEPTION
2008 WHEN FND_API.G_EXC_ERROR THEN
2009 IF (x_msg_data IS NULL) THEN
2010 FND_MSG_PUB.Count_And_Get
2011 ( p_encoded => FND_API.G_FALSE
2012 , p_count => x_msg_count
2013 , p_data => x_msg_data
2014 );
2015 END IF;
2016 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2017 x_return_status := FND_API.G_RET_STS_ERROR;
2018 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2019 IF (x_msg_data IS NULL) THEN
2020 FND_MSG_PUB.Count_And_Get
2021 ( p_encoded => FND_API.G_FALSE
2022 , p_count => x_msg_count
2023 , p_data => x_msg_data
2024 );
2025 END IF;
2026 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2027 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2028 WHEN NO_DATA_FOUND THEN
2029 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2030 IF (x_msg_data IS NOT NULL) THEN
2031 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_Analysis ';
2032 ELSE
2033 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_Analysis ';
2034 END IF;
2035 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2036 WHEN OTHERS THEN
2037 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2038 IF (x_msg_data IS NOT NULL) THEN
2039 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_Analysis ';
2040 ELSE
2041 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_Analysis ';
2042 END IF;
2043 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2044 end Retrieve_Kpi_Analysis;
2045
2046 /************************************************************************************
2047 ************************************************************************************/
2048
2049 procedure Update_Kpi_Analysis(
2050 p_commit IN varchar2 := FND_API.G_FALSE
2051 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
2052 ,x_return_status OUT NOCOPY varchar2
2053 ,x_msg_count OUT NOCOPY number
2054 ,x_msg_data OUT NOCOPY varchar2
2055 ) is
2056
2057 begin
2058 FND_MSG_PUB.Initialize;
2059 x_return_status := FND_API.G_RET_STS_SUCCESS;
2060 BSC_KPI_PVT.Update_Kpi_Analysis( p_commit
2061 ,p_Bsc_Kpi_Entity_Rec
2062 ,x_return_status
2063 ,x_msg_count
2064 ,x_msg_data);
2065
2066 EXCEPTION
2067 WHEN FND_API.G_EXC_ERROR THEN
2068 IF (x_msg_data IS NULL) THEN
2069 FND_MSG_PUB.Count_And_Get
2070 ( p_encoded => FND_API.G_FALSE
2071 , p_count => x_msg_count
2072 , p_data => x_msg_data
2073 );
2074 END IF;
2075 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2076 x_return_status := FND_API.G_RET_STS_ERROR;
2077 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2078 IF (x_msg_data IS NULL) THEN
2079 FND_MSG_PUB.Count_And_Get
2080 ( p_encoded => FND_API.G_FALSE
2081 , p_count => x_msg_count
2082 , p_data => x_msg_data
2083 );
2084 END IF;
2085 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2086 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2087 WHEN NO_DATA_FOUND THEN
2088 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2089 IF (x_msg_data IS NOT NULL) THEN
2090 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_Analysis ';
2091 ELSE
2092 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_Analysis ';
2093 END IF;
2094 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2095 WHEN OTHERS THEN
2096 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2097 IF (x_msg_data IS NOT NULL) THEN
2098 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_Analysis ';
2099 ELSE
2100 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_Analysis ';
2101 END IF;
2102 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2103 end Update_Kpi_Analysis;
2104
2105 /************************************************************************************
2106 ************************************************************************************/
2107
2108 procedure Delete_Kpi_Analysis(
2109 p_commit IN varchar2 := FND_API.G_FALSE
2110 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
2111 ,x_return_status OUT NOCOPY varchar2
2112 ,x_msg_count OUT NOCOPY number
2113 ,x_msg_data OUT NOCOPY varchar2
2114 ) is
2115
2116 l_count number;
2117
2118 begin
2119 FND_MSG_PUB.Initialize;
2120 x_return_status := FND_API.G_RET_STS_SUCCESS;
2121 -- Before deleting analysis Groups/Options which will delete the KPI_Measure_Id also,
2122 -- we need to delete from KPI Measure Properties from Props, Thresholds and Weights tables.
2123 Delete_Obj_Kpi_Measure_Props (
2124 p_commit => p_commit
2125 , p_bsc_kpi_entity_rec => p_Bsc_Kpi_Entity_Rec
2126 , x_return_status => x_return_status
2127 , x_msg_count => x_msg_count
2128 , x_msg_data => x_msg_data
2129 );
2130 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2132 END IF;
2133
2134 BSC_KPI_PVT.Delete_Kpi_Analysis( p_commit
2135 ,p_Bsc_Kpi_Entity_Rec
2136 ,x_return_status
2137 ,x_msg_count
2138 ,x_msg_data);
2139
2140 EXCEPTION
2141 WHEN FND_API.G_EXC_ERROR THEN
2142 IF (x_msg_data IS NULL) THEN
2143 FND_MSG_PUB.Count_And_Get
2144 ( p_encoded => FND_API.G_FALSE
2145 , p_count => x_msg_count
2146 , p_data => x_msg_data
2147 );
2148 END IF;
2149 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2150 x_return_status := FND_API.G_RET_STS_ERROR;
2151 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2152 IF (x_msg_data IS NULL) THEN
2153 FND_MSG_PUB.Count_And_Get
2154 ( p_encoded => FND_API.G_FALSE
2155 , p_count => x_msg_count
2156 , p_data => x_msg_data
2157 );
2158 END IF;
2159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2160 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2161 WHEN NO_DATA_FOUND THEN
2162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2163 IF (x_msg_data IS NOT NULL) THEN
2164 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_Analysis ';
2165 ELSE
2166 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_Analysis ';
2167 END IF;
2168 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2169 WHEN OTHERS THEN
2170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2171 IF (x_msg_data IS NOT NULL) THEN
2172 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_Analysis ';
2173 ELSE
2174 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_Analysis ';
2175 END IF;
2176 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2177 end Delete_Kpi_Analysis;
2178
2179 /************************************************************************************
2180 ************************************************************************************/
2181
2182 procedure Create_Kpi_Periodicity(
2183 p_commit IN varchar2 := FND_API.G_FALSE
2184 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
2185 ,x_return_status OUT NOCOPY varchar2
2186 ,x_msg_count OUT NOCOPY number
2187 ,x_msg_data OUT NOCOPY varchar2
2188 ) is
2189
2190
2191 begin
2192 FND_MSG_PUB.Initialize;
2193 x_return_status := FND_API.G_RET_STS_SUCCESS;
2194 BSC_KPI_PVT.Create_Kpi_Periodicity( p_commit
2195 ,p_Bsc_Kpi_Entity_Rec
2196 ,x_return_status
2197 ,x_msg_count
2198 ,x_msg_data);
2199
2200 EXCEPTION
2201 WHEN FND_API.G_EXC_ERROR THEN
2202 IF (x_msg_data IS NULL) THEN
2203 FND_MSG_PUB.Count_And_Get
2204 ( p_encoded => FND_API.G_FALSE
2205 , p_count => x_msg_count
2206 , p_data => x_msg_data
2207 );
2208 END IF;
2209 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2210 x_return_status := FND_API.G_RET_STS_ERROR;
2211 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2212 IF (x_msg_data IS NULL) THEN
2213 FND_MSG_PUB.Count_And_Get
2214 ( p_encoded => FND_API.G_FALSE
2215 , p_count => x_msg_count
2216 , p_data => x_msg_data
2217 );
2218 END IF;
2219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2220 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2221 WHEN NO_DATA_FOUND THEN
2222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2223 IF (x_msg_data IS NOT NULL) THEN
2224 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_Periodicity ';
2225 ELSE
2226 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_Periodicity ';
2227 END IF;
2228 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2229 WHEN OTHERS THEN
2230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2231 IF (x_msg_data IS NOT NULL) THEN
2232 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_Periodicity ';
2233 ELSE
2234 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_Periodicity ';
2235 END IF;
2236 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2237 end Create_Kpi_Periodicity;
2238
2239 /************************************************************************************
2240 ************************************************************************************/
2241
2242 procedure Retrieve_Kpi_Periodicity(
2243 p_commit IN varchar2 := FND_API.G_FALSE
2244 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
2245 ,x_Bsc_Kpi_Entity_Rec IN OUT NOCOPY BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
2246 ,x_return_status OUT NOCOPY varchar2
2247 ,x_msg_count OUT NOCOPY number
2248 ,x_msg_data OUT NOCOPY varchar2
2249 ) is
2250
2251 begin
2252 FND_MSG_PUB.Initialize;
2253 x_return_status := FND_API.G_RET_STS_SUCCESS;
2254 BSC_KPI_PVT.Retrieve_Kpi_Periodicity( p_commit
2255 ,p_Bsc_Kpi_Entity_Rec
2256 ,x_Bsc_Kpi_Entity_Rec
2257 ,x_return_status
2258 ,x_msg_count
2259 ,x_msg_data);
2260
2261 EXCEPTION
2262 WHEN FND_API.G_EXC_ERROR THEN
2263 IF (x_msg_data IS NULL) THEN
2264 FND_MSG_PUB.Count_And_Get
2265 ( p_encoded => FND_API.G_FALSE
2266 , p_count => x_msg_count
2267 , p_data => x_msg_data
2268 );
2269 END IF;
2270 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2271 x_return_status := FND_API.G_RET_STS_ERROR;
2272 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2273 IF (x_msg_data IS NULL) THEN
2274 FND_MSG_PUB.Count_And_Get
2275 ( p_encoded => FND_API.G_FALSE
2276 , p_count => x_msg_count
2277 , p_data => x_msg_data
2278 );
2279 END IF;
2280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2281 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2282 WHEN NO_DATA_FOUND THEN
2283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2284 IF (x_msg_data IS NOT NULL) THEN
2285 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_Periodicity ';
2286 ELSE
2287 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_Periodicity ';
2288 END IF;
2289 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2290 WHEN OTHERS THEN
2291 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2292 IF (x_msg_data IS NOT NULL) THEN
2293 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_Periodicity ';
2294 ELSE
2295 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_Periodicity ';
2296 END IF;
2297 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2298 end Retrieve_Kpi_Periodicity;
2299
2300 /************************************************************************************
2301 ************************************************************************************/
2302
2303 procedure Update_Kpi_Periodicity(
2304 p_commit IN varchar2 := FND_API.G_FALSE
2305 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
2306 ,x_return_status OUT NOCOPY varchar2
2307 ,x_msg_count OUT NOCOPY number
2308 ,x_msg_data OUT NOCOPY varchar2
2309 ) is
2310
2311 begin
2312 FND_MSG_PUB.Initialize;
2313 x_return_status := FND_API.G_RET_STS_SUCCESS;
2314 BSC_KPI_PVT.Update_Kpi_Periodicity( p_commit
2315 ,p_Bsc_Kpi_Entity_Rec
2316 ,x_return_status
2317 ,x_msg_count
2318 ,x_msg_data);
2319
2320 EXCEPTION
2321 WHEN FND_API.G_EXC_ERROR THEN
2322 IF (x_msg_data IS NULL) THEN
2323 FND_MSG_PUB.Count_And_Get
2324 ( p_encoded => FND_API.G_FALSE
2325 , p_count => x_msg_count
2326 , p_data => x_msg_data
2327 );
2328 END IF;
2329 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2330 x_return_status := FND_API.G_RET_STS_ERROR;
2331 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2332 IF (x_msg_data IS NULL) THEN
2333 FND_MSG_PUB.Count_And_Get
2334 ( p_encoded => FND_API.G_FALSE
2335 , p_count => x_msg_count
2336 , p_data => x_msg_data
2337 );
2338 END IF;
2339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2340 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2341 WHEN NO_DATA_FOUND THEN
2342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2343 IF (x_msg_data IS NOT NULL) THEN
2344 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_Periodicity ';
2345 ELSE
2346 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_Periodicity ';
2347 END IF;
2348 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2349 WHEN OTHERS THEN
2350 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2351 IF (x_msg_data IS NOT NULL) THEN
2352 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_Periodicity ';
2353 ELSE
2354 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_Periodicity ';
2355 END IF;
2356 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2357 end Update_Kpi_Periodicity;
2358
2359 /************************************************************************************
2360 ************************************************************************************/
2361
2362 procedure Delete_Kpi_Periodicity(
2363 p_commit IN varchar2 := FND_API.G_FALSE
2364 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
2365 ,x_return_status OUT NOCOPY varchar2
2366 ,x_msg_count OUT NOCOPY number
2367 ,x_msg_data OUT NOCOPY varchar2
2368 ) is
2369
2370 begin
2371 FND_MSG_PUB.Initialize;
2372 x_return_status := FND_API.G_RET_STS_SUCCESS;
2373 BSC_KPI_PVT.Delete_Kpi_Periodicity( p_commit
2374 ,p_Bsc_Kpi_Entity_Rec
2375 ,x_return_status
2376 ,x_msg_count
2377 ,x_msg_data);
2378
2379 EXCEPTION
2380 WHEN FND_API.G_EXC_ERROR THEN
2381 IF (x_msg_data IS NULL) THEN
2382 FND_MSG_PUB.Count_And_Get
2383 ( p_encoded => FND_API.G_FALSE
2384 , p_count => x_msg_count
2385 , p_data => x_msg_data
2386 );
2387 END IF;
2388 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2389 x_return_status := FND_API.G_RET_STS_ERROR;
2390 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2391 IF (x_msg_data IS NULL) THEN
2392 FND_MSG_PUB.Count_And_Get
2393 ( p_encoded => FND_API.G_FALSE
2394 , p_count => x_msg_count
2395 , p_data => x_msg_data
2396 );
2397 END IF;
2398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2399 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2400 WHEN NO_DATA_FOUND THEN
2401 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2402 IF (x_msg_data IS NOT NULL) THEN
2403 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_Periodicity ';
2404 ELSE
2405 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_Periodicity ';
2406 END IF;
2407 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2408 WHEN OTHERS THEN
2409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2410 IF (x_msg_data IS NOT NULL) THEN
2411 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_Periodicity ';
2412 ELSE
2413 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_Periodicity ';
2414 END IF;
2415 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2416 end Delete_Kpi_Periodicity;
2417
2418 /************************************************************************************
2419 ************************************************************************************/
2420
2421 procedure Create_Kpi_Data_Tables(
2422 p_commit IN varchar2 := FND_API.G_FALSE
2423 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
2424 ,x_return_status OUT NOCOPY varchar2
2425 ,x_msg_count OUT NOCOPY number
2426 ,x_msg_data OUT NOCOPY varchar2
2427 ) is
2428
2429 begin
2430 FND_MSG_PUB.Initialize;
2431 x_return_status := FND_API.G_RET_STS_SUCCESS;
2432 BSC_KPI_PVT.Create_Kpi_Data_Tables( p_commit
2433 ,p_Bsc_Kpi_Entity_Rec
2434 ,x_return_status
2435 ,x_msg_count
2436 ,x_msg_data);
2437
2438 EXCEPTION
2439 WHEN FND_API.G_EXC_ERROR THEN
2440 IF (x_msg_data IS NULL) THEN
2441 FND_MSG_PUB.Count_And_Get
2442 ( p_encoded => FND_API.G_FALSE
2443 , p_count => x_msg_count
2444 , p_data => x_msg_data
2445 );
2446 END IF;
2447 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2448 x_return_status := FND_API.G_RET_STS_ERROR;
2449 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2450 IF (x_msg_data IS NULL) THEN
2451 FND_MSG_PUB.Count_And_Get
2452 ( p_encoded => FND_API.G_FALSE
2453 , p_count => x_msg_count
2454 , p_data => x_msg_data
2455 );
2456 END IF;
2457 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2458 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2459 WHEN NO_DATA_FOUND THEN
2460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2461 IF (x_msg_data IS NOT NULL) THEN
2462 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_Data_Tables ';
2463 ELSE
2464 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_Data_Tables ';
2465 END IF;
2466 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2467 WHEN OTHERS THEN
2468 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2469 IF (x_msg_data IS NOT NULL) THEN
2470 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_Data_Tables ';
2471 ELSE
2472 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_Data_Tables ';
2473 END IF;
2474 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2475 end Create_Kpi_Data_Tables;
2476
2477 /************************************************************************************
2478 ************************************************************************************/
2479
2480 procedure Retrieve_Kpi_Data_Tables(
2481 p_commit IN varchar2 := FND_API.G_FALSE
2482 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
2483 ,x_Bsc_Kpi_Entity_Rec IN OUT NOCOPY BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
2484 ,x_return_status OUT NOCOPY varchar2
2485 ,x_msg_count OUT NOCOPY number
2486 ,x_msg_data OUT NOCOPY varchar2
2487 ) is
2488
2489 begin
2490 FND_MSG_PUB.Initialize;
2491 x_return_status := FND_API.G_RET_STS_SUCCESS;
2492 BSC_KPI_PVT.Retrieve_Kpi_Data_Tables( p_commit
2493 ,p_Bsc_Kpi_Entity_Rec
2494 ,x_Bsc_Kpi_Entity_Rec
2495 ,x_return_status
2496 ,x_msg_count
2497 ,x_msg_data);
2498
2499 EXCEPTION
2500 WHEN FND_API.G_EXC_ERROR THEN
2501 IF (x_msg_data IS NULL) THEN
2502 FND_MSG_PUB.Count_And_Get
2503 ( p_encoded => FND_API.G_FALSE
2504 , p_count => x_msg_count
2505 , p_data => x_msg_data
2506 );
2507 END IF;
2508 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2509 x_return_status := FND_API.G_RET_STS_ERROR;
2510 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2511 IF (x_msg_data IS NULL) THEN
2512 FND_MSG_PUB.Count_And_Get
2513 ( p_encoded => FND_API.G_FALSE
2514 , p_count => x_msg_count
2515 , p_data => x_msg_data
2516 );
2517 END IF;
2518 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2519 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2520 WHEN NO_DATA_FOUND THEN
2521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2522 IF (x_msg_data IS NOT NULL) THEN
2523 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_Data_Tables ';
2524 ELSE
2525 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_Data_Tables ';
2526 END IF;
2527 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2528 WHEN OTHERS THEN
2529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2530 IF (x_msg_data IS NOT NULL) THEN
2531 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_Data_Tables ';
2532 ELSE
2533 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_Data_Tables ';
2534 END IF;
2535 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2536 end Retrieve_Kpi_Data_Tables;
2537
2538 /************************************************************************************
2539 ************************************************************************************/
2540
2541 procedure Update_Kpi_Data_Tables(
2542 p_commit IN varchar2 := FND_API.G_FALSE
2543 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
2544 ,x_return_status OUT NOCOPY varchar2
2545 ,x_msg_count OUT NOCOPY number
2546 ,x_msg_data OUT NOCOPY varchar2
2547 ) is
2548
2549 begin
2550 FND_MSG_PUB.Initialize;
2551 x_return_status := FND_API.G_RET_STS_SUCCESS;
2552 BSC_KPI_PVT.Update_Kpi_Data_Tables( p_commit
2553 ,p_Bsc_Kpi_Entity_Rec
2554 ,x_return_status
2555 ,x_msg_count
2556 ,x_msg_data);
2557
2558 EXCEPTION
2559 WHEN FND_API.G_EXC_ERROR THEN
2560 IF (x_msg_data IS NULL) THEN
2561 FND_MSG_PUB.Count_And_Get
2562 ( p_encoded => FND_API.G_FALSE
2563 , p_count => x_msg_count
2564 , p_data => x_msg_data
2565 );
2566 END IF;
2567 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2568 x_return_status := FND_API.G_RET_STS_ERROR;
2569 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2570 IF (x_msg_data IS NULL) THEN
2571 FND_MSG_PUB.Count_And_Get
2572 ( p_encoded => FND_API.G_FALSE
2573 , p_count => x_msg_count
2574 , p_data => x_msg_data
2575 );
2576 END IF;
2577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2578 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2579 WHEN NO_DATA_FOUND THEN
2580 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2581 IF (x_msg_data IS NOT NULL) THEN
2582 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_Data_Tables ';
2583 ELSE
2584 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_Data_Tables ';
2585 END IF;
2586 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2587 WHEN OTHERS THEN
2588 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2589 IF (x_msg_data IS NOT NULL) THEN
2590 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_Data_Tables ';
2591 ELSE
2592 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_Data_Tables ';
2593 END IF;
2594 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2595 end Update_Kpi_Data_Tables;
2596
2597 /************************************************************************************
2598 ************************************************************************************/
2599
2600 procedure Delete_Kpi_Data_Tables(
2601 p_commit IN varchar2 := FND_API.G_FALSE
2602 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
2603 ,x_return_status OUT NOCOPY varchar2
2604 ,x_msg_count OUT NOCOPY number
2605 ,x_msg_data OUT NOCOPY varchar2
2606 ) is
2607
2608 begin
2609 FND_MSG_PUB.Initialize;
2610 x_return_status := FND_API.G_RET_STS_SUCCESS;
2611 BSC_KPI_PVT.Delete_Kpi_Data_Tables( p_commit
2612 ,p_Bsc_Kpi_Entity_Rec
2613 ,x_return_status
2614 ,x_msg_count
2615 ,x_msg_data);
2616
2617 EXCEPTION
2618 WHEN FND_API.G_EXC_ERROR THEN
2619 IF (x_msg_data IS NULL) THEN
2620 FND_MSG_PUB.Count_And_Get
2621 ( p_encoded => FND_API.G_FALSE
2622 , p_count => x_msg_count
2623 , p_data => x_msg_data
2624 );
2625 END IF;
2626 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2627 x_return_status := FND_API.G_RET_STS_ERROR;
2628 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2629 IF (x_msg_data IS NULL) THEN
2630 FND_MSG_PUB.Count_And_Get
2631 ( p_encoded => FND_API.G_FALSE
2632 , p_count => x_msg_count
2633 , p_data => x_msg_data
2634 );
2635 END IF;
2636 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2637 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2638 WHEN NO_DATA_FOUND THEN
2639 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2640 IF (x_msg_data IS NOT NULL) THEN
2641 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_Data_Tables ';
2642 ELSE
2643 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_Data_Tables ';
2644 END IF;
2645 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2646 WHEN OTHERS THEN
2647 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2648 IF (x_msg_data IS NOT NULL) THEN
2649 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_Data_Tables ';
2650 ELSE
2651 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_Data_Tables ';
2652 END IF;
2653 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2654 end Delete_Kpi_Data_Tables;
2655
2656 /************************************************************************************
2657 ************************************************************************************/
2658
2659 procedure Create_Kpi_Calculations(
2660 p_commit IN varchar2 := FND_API.G_FALSE
2661 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
2662 ,x_return_status OUT NOCOPY varchar2
2663 ,x_msg_count OUT NOCOPY number
2664 ,x_msg_data OUT NOCOPY varchar2
2665 ) is
2666
2667 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
2668
2669 begin
2670 FND_MSG_PUB.Initialize;
2671 x_return_status := FND_API.G_RET_STS_SUCCESS;
2672 -- Assign all values in the passed "Record" parameter to the locally defined
2673 -- "Record" variable.
2674 l_Bsc_Kpi_Entity_Rec := p_Bsc_Kpi_Entity_Rec;
2675
2676 -- Set some defaults and call procedure several times.
2677 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 0;
2678 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 2;
2679 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 2;
2680 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2681
2682 BSC_KPI_PVT.Create_Kpi_Calculations( p_commit
2683 ,l_Bsc_Kpi_Entity_Rec
2684 ,x_return_status
2685 ,x_msg_count
2686 ,x_msg_data);
2687
2688 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 1;
2689 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 2;
2690 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 2;
2691 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2692
2693 BSC_KPI_PVT.Create_Kpi_Calculations( p_commit
2694 ,l_Bsc_Kpi_Entity_Rec
2695 ,x_return_status
2696 ,x_msg_count
2697 ,x_msg_data);
2698
2699 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 2;
2700 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 2;
2701 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 2;
2702 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2703
2704 BSC_KPI_PVT.Create_Kpi_Calculations( p_commit
2705 ,l_Bsc_Kpi_Entity_Rec
2706 ,x_return_status
2707 ,x_msg_count
2708 ,x_msg_data);
2709
2710 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 3;
2711 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
2712 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
2713 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2714
2715 BSC_KPI_PVT.Create_Kpi_Calculations( p_commit
2716 ,l_Bsc_Kpi_Entity_Rec
2717 ,x_return_status
2718 ,x_msg_count
2719 ,x_msg_data);
2720
2721 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 4;
2722 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
2723 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
2724 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2725
2726 BSC_KPI_PVT.Create_Kpi_Calculations( p_commit
2727 ,l_Bsc_Kpi_Entity_Rec
2728 ,x_return_status
2729 ,x_msg_count
2730 ,x_msg_data);
2731
2732 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 5;
2733 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 2;
2734 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 2;
2735 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2736
2737 BSC_KPI_PVT.Create_Kpi_Calculations( p_commit
2738 ,l_Bsc_Kpi_Entity_Rec
2739 ,x_return_status
2740 ,x_msg_count
2741 ,x_msg_data);
2742
2743 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 6;
2744 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
2745 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
2746 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2747
2748 BSC_KPI_PVT.Create_Kpi_Calculations( p_commit
2749 ,l_Bsc_Kpi_Entity_Rec
2750 ,x_return_status
2751 ,x_msg_count
2752 ,x_msg_data);
2753
2754 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 7;
2755 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
2756 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
2757 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2758
2759 BSC_KPI_PVT.Create_Kpi_Calculations( p_commit
2760 ,l_Bsc_Kpi_Entity_Rec
2761 ,x_return_status
2762 ,x_msg_count
2763 ,x_msg_data);
2764
2765 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 8;
2766 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
2767 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
2768 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2769
2770 BSC_KPI_PVT.Create_Kpi_Calculations( p_commit
2771 ,l_Bsc_Kpi_Entity_Rec
2772 ,x_return_status
2773 ,x_msg_count
2774 ,x_msg_data);
2775
2776 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 9;
2777 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
2778 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
2779 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2780
2781 BSC_KPI_PVT.Create_Kpi_Calculations( p_commit
2782 ,l_Bsc_Kpi_Entity_Rec
2783 ,x_return_status
2784 ,x_msg_count
2785 ,x_msg_data);
2786
2787 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 10;
2788 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
2789 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
2790 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2791
2792 BSC_KPI_PVT.Create_Kpi_Calculations( p_commit
2793 ,l_Bsc_Kpi_Entity_Rec
2794 ,x_return_status
2795 ,x_msg_count
2796 ,x_msg_data);
2797
2798 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 11;
2799 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
2800 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
2801 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2802
2803 BSC_KPI_PVT.Create_Kpi_Calculations( p_commit
2804 ,l_Bsc_Kpi_Entity_Rec
2805 ,x_return_status
2806 ,x_msg_count
2807 ,x_msg_data);
2808
2809
2810 -- We need to make an ENTRY for XTD
2811 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 12;
2812 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
2813 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
2814 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2815
2816 BSC_KPI_PVT.Create_Kpi_Calculations( p_commit
2817 ,l_Bsc_Kpi_Entity_Rec
2818 ,x_return_status
2819 ,x_msg_count
2820 ,x_msg_data);
2821
2822
2823 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 20;
2824 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
2825 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
2826 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2827
2828 BSC_KPI_PVT.Create_Kpi_Calculations( p_commit
2829 ,l_Bsc_Kpi_Entity_Rec
2830 ,x_return_status
2831 ,x_msg_count
2832 ,x_msg_data);
2833
2834 EXCEPTION
2835 WHEN FND_API.G_EXC_ERROR THEN
2836 IF (x_msg_data IS NULL) THEN
2837 FND_MSG_PUB.Count_And_Get
2838 ( p_encoded => FND_API.G_FALSE
2839 , p_count => x_msg_count
2840 , p_data => x_msg_data
2841 );
2842 END IF;
2843 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2844 x_return_status := FND_API.G_RET_STS_ERROR;
2845 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2846 IF (x_msg_data IS NULL) THEN
2847 FND_MSG_PUB.Count_And_Get
2848 ( p_encoded => FND_API.G_FALSE
2849 , p_count => x_msg_count
2850 , p_data => x_msg_data
2851 );
2852 END IF;
2853 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2854 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2855 WHEN NO_DATA_FOUND THEN
2856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2857 IF (x_msg_data IS NOT NULL) THEN
2858 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_Calculations ';
2859 ELSE
2860 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_Calculations ';
2861 END IF;
2862 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2863 WHEN OTHERS THEN
2864 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2865 IF (x_msg_data IS NOT NULL) THEN
2866 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_Calculations ';
2867 ELSE
2868 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_Calculations ';
2869 END IF;
2870 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2871 end Create_Kpi_Calculations;
2872
2873 /************************************************************************************
2874 ************************************************************************************/
2875
2876 procedure Retrieve_Kpi_Calculations(
2877 p_commit IN varchar2 := FND_API.G_FALSE
2878 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
2879 ,x_Bsc_Kpi_Entity_Rec IN OUT NOCOPY BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
2880 ,x_return_status OUT NOCOPY varchar2
2881 ,x_msg_count OUT NOCOPY number
2882 ,x_msg_data OUT NOCOPY varchar2
2883 ) is
2884
2885 begin
2886 FND_MSG_PUB.Initialize;
2887 x_return_status := FND_API.G_RET_STS_SUCCESS;
2888 BSC_KPI_PVT.Retrieve_Kpi_Calculations( p_commit
2889 ,p_Bsc_Kpi_Entity_Rec
2890 ,x_Bsc_Kpi_Entity_Rec
2891 ,x_return_status
2892 ,x_msg_count
2893 ,x_msg_data);
2894
2895 EXCEPTION
2896 WHEN FND_API.G_EXC_ERROR THEN
2897 IF (x_msg_data IS NULL) THEN
2898 FND_MSG_PUB.Count_And_Get
2899 ( p_encoded => FND_API.G_FALSE
2900 , p_count => x_msg_count
2901 , p_data => x_msg_data
2902 );
2903 END IF;
2904 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
2905 x_return_status := FND_API.G_RET_STS_ERROR;
2906 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2907 IF (x_msg_data IS NULL) THEN
2908 FND_MSG_PUB.Count_And_Get
2909 ( p_encoded => FND_API.G_FALSE
2910 , p_count => x_msg_count
2911 , p_data => x_msg_data
2912 );
2913 END IF;
2914 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2915 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
2916 WHEN NO_DATA_FOUND THEN
2917 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2918 IF (x_msg_data IS NOT NULL) THEN
2919 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_Calculations ';
2920 ELSE
2921 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_Calculations ';
2922 END IF;
2923 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
2924 WHEN OTHERS THEN
2925 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2926 IF (x_msg_data IS NOT NULL) THEN
2927 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_Calculations ';
2928 ELSE
2929 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_Calculations ';
2930 END IF;
2931 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
2932 end Retrieve_Kpi_Calculations;
2933
2934 /************************************************************************************
2935 ************************************************************************************/
2936
2937 procedure Update_Kpi_Calculations(
2938 p_commit IN varchar2 := FND_API.G_FALSE
2939 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
2940 ,x_return_status OUT NOCOPY varchar2
2941 ,x_msg_count OUT NOCOPY number
2942 ,x_msg_data OUT NOCOPY varchar2
2943 ) is
2944
2945 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
2946
2947 begin
2948 FND_MSG_PUB.Initialize;
2949 x_return_status := FND_API.G_RET_STS_SUCCESS;
2950 -- At this point we are using default values. This should be redone.
2951
2952 -- Assign all values in the passed "Record" parameter to the locally defined
2953 -- "Record" variable.
2954 l_Bsc_Kpi_Entity_Rec := p_Bsc_Kpi_Entity_Rec;
2955
2956 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 0;
2957 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 2;
2958 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 2;
2959 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2960
2961 BSC_KPI_PVT.Update_Kpi_Calculations( p_commit
2962 ,l_Bsc_Kpi_Entity_Rec
2963 ,x_return_status
2964 ,x_msg_count
2965 ,x_msg_data);
2966
2967 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 1;
2968 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 2;
2969 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 2;
2970 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2971 BSC_KPI_PVT.Update_Kpi_Calculations( p_commit
2972 ,l_Bsc_Kpi_Entity_Rec
2973 ,x_return_status
2974 ,x_msg_count
2975 ,x_msg_data);
2976
2977
2978 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 2;
2979 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 2;
2980 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 2;
2981 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2982
2983 BSC_KPI_PVT.Update_Kpi_Calculations( p_commit
2984 ,l_Bsc_Kpi_Entity_Rec
2985 ,x_return_status
2986 ,x_msg_count
2987 ,x_msg_data);
2988
2989 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 3;
2990 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
2991 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
2992 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
2993
2994 BSC_KPI_PVT.Update_Kpi_Calculations( p_commit
2995 ,l_Bsc_Kpi_Entity_Rec
2996 ,x_return_status
2997 ,x_msg_count
2998 ,x_msg_data);
2999
3000 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 4;
3001 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
3002 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
3003 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
3004
3005 BSC_KPI_PVT.Update_Kpi_Calculations( p_commit
3006 ,l_Bsc_Kpi_Entity_Rec
3007 ,x_return_status
3008 ,x_msg_count
3009 ,x_msg_data);
3010
3011 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 5;
3012 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 2;
3013 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 2;
3014 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
3015
3016 BSC_KPI_PVT.Update_Kpi_Calculations( p_commit
3017 ,l_Bsc_Kpi_Entity_Rec
3018 ,x_return_status
3019 ,x_msg_count
3020 ,x_msg_data);
3021
3022 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 6;
3023 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
3024 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
3025 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
3026
3027 BSC_KPI_PVT.Update_Kpi_Calculations( p_commit
3028 ,l_Bsc_Kpi_Entity_Rec
3029 ,x_return_status
3030 ,x_msg_count
3031 ,x_msg_data);
3032
3033 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 7;
3034 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
3035 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
3036 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
3037
3038 BSC_KPI_PVT.Update_Kpi_Calculations( p_commit
3039 ,l_Bsc_Kpi_Entity_Rec
3040 ,x_return_status
3041 ,x_msg_count
3042 ,x_msg_data);
3043
3044 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 8;
3045 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
3046 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
3047 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
3048
3049 BSC_KPI_PVT.Update_Kpi_Calculations( p_commit
3050 ,l_Bsc_Kpi_Entity_Rec
3051 ,x_return_status
3052 ,x_msg_count
3053 ,x_msg_data);
3054
3055 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 9;
3056 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
3057 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
3058 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
3059
3060 BSC_KPI_PVT.Update_Kpi_Calculations( p_commit
3061 ,l_Bsc_Kpi_Entity_Rec
3062 ,x_return_status
3063 ,x_msg_count
3064 ,x_msg_data);
3065
3066 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 10;
3067 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
3068 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
3069 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
3070
3071 BSC_KPI_PVT.Update_Kpi_Calculations( p_commit
3072 ,l_Bsc_Kpi_Entity_Rec
3073 ,x_return_status
3074 ,x_msg_count
3075 ,x_msg_data);
3076
3077 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 11;
3078 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
3079 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
3080 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
3081
3082 BSC_KPI_PVT.Update_Kpi_Calculations( p_commit
3083 ,l_Bsc_Kpi_Entity_Rec
3084 ,x_return_status
3085 ,x_msg_count
3086 ,x_msg_data);
3087
3088 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Calculation_Id := 20;
3089 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level0 := 0;
3090 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_User_Level1 := 0;
3091 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Default_Value := 0;
3092
3093
3094 EXCEPTION
3095 WHEN FND_API.G_EXC_ERROR THEN
3096 IF (x_msg_data IS NULL) THEN
3097 FND_MSG_PUB.Count_And_Get
3098 ( p_encoded => FND_API.G_FALSE
3099 , p_count => x_msg_count
3100 , p_data => x_msg_data
3101 );
3102 END IF;
3103 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3104 x_return_status := FND_API.G_RET_STS_ERROR;
3105 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3106 IF (x_msg_data IS NULL) THEN
3107 FND_MSG_PUB.Count_And_Get
3108 ( p_encoded => FND_API.G_FALSE
3109 , p_count => x_msg_count
3110 , p_data => x_msg_data
3111 );
3112 END IF;
3113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3114 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3115 WHEN NO_DATA_FOUND THEN
3116 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3117 IF (x_msg_data IS NOT NULL) THEN
3118 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_Calculations ';
3119 ELSE
3120 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_Calculations ';
3121 END IF;
3122 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3123 WHEN OTHERS THEN
3124 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3125 IF (x_msg_data IS NOT NULL) THEN
3126 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_Calculations ';
3127 ELSE
3128 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_Calculations ';
3129 END IF;
3130 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3131
3132 end Update_Kpi_Calculations;
3133
3134 /************************************************************************************
3135 ************************************************************************************/
3136
3137 procedure Delete_Kpi_Calculations(
3138 p_commit IN varchar2 := FND_API.G_FALSE
3139 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
3140 ,x_return_status OUT NOCOPY varchar2
3141 ,x_msg_count OUT NOCOPY number
3142 ,x_msg_data OUT NOCOPY varchar2
3143 ) is
3144
3145 begin
3146 FND_MSG_PUB.Initialize;
3147 x_return_status := FND_API.G_RET_STS_SUCCESS;
3148 BSC_KPI_PVT.Delete_Kpi_Calculations( p_commit
3149 ,p_Bsc_Kpi_Entity_Rec
3150 ,x_return_status
3151 ,x_msg_count
3152 ,x_msg_data);
3153
3154 EXCEPTION
3155 WHEN FND_API.G_EXC_ERROR THEN
3156 IF (x_msg_data IS NULL) THEN
3157 FND_MSG_PUB.Count_And_Get
3158 ( p_encoded => FND_API.G_FALSE
3159 , p_count => x_msg_count
3160 , p_data => x_msg_data
3161 );
3162 END IF;
3163 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3164 x_return_status := FND_API.G_RET_STS_ERROR;
3165 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3166 IF (x_msg_data IS NULL) THEN
3167 FND_MSG_PUB.Count_And_Get
3168 ( p_encoded => FND_API.G_FALSE
3169 , p_count => x_msg_count
3170 , p_data => x_msg_data
3171 );
3172 END IF;
3173 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3174 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3175 WHEN NO_DATA_FOUND THEN
3176 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3177 IF (x_msg_data IS NOT NULL) THEN
3178 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_Calculations ';
3179 ELSE
3180 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_Calculations ';
3181 END IF;
3182 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3183 WHEN OTHERS THEN
3184 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3185 IF (x_msg_data IS NOT NULL) THEN
3186 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_Calculations ';
3187 ELSE
3188 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_Calculations ';
3189 END IF;
3190 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3191
3192 end Delete_Kpi_Calculations;
3193
3194 /************************************************************************************
3195 ************************************************************************************/
3196 /************************************************************************************
3197 Function : Create_Kpi_Access_For_Resp
3198 Description : This function will assign a objectitve to a given responsibility
3199 ***********************************************************************************/
3200 PROCEDURE Create_Kpi_Access_For_Resp(
3201 p_commit IN VARCHAR2 := FND_API.G_FALSE
3202 ,p_Comma_Sep_Resposibility_Key IN VARCHAR2
3203 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
3204 ,x_return_status OUT NOCOPY VARCHAR2
3205 ,x_msg_count OUT NOCOPY NUMBER
3206 ,x_msg_data OUT NOCOPY VARCHAR2
3207 )IS
3208 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
3209 l_Count NUMBER;
3210
3211 CURSOR c_Resp_Ids IS
3212 SELECT responsibility_id
3213 FROM fnd_responsibility
3214 WHERE INSTR(','||p_Comma_Sep_Resposibility_Key||',',','||responsibility_key||',') > 0;
3215
3216 BEGIN
3217 FND_MSG_PUB.Initialize;
3218 x_return_status := FND_API.G_RET_STS_SUCCESS;
3219 l_Bsc_Kpi_Entity_Rec := p_Bsc_Kpi_Entity_Rec;
3220
3221 FOR CD IN c_Resp_Ids LOOP
3222 SELECT COUNT(1)
3223 INTO l_Count
3224 FROM bsc_user_kpi_access
3225 WHERE indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id
3226 AND responsibility_id = CD.responsibility_id;
3227
3228 l_Bsc_Kpi_Entity_Rec.Bsc_Responsibility_Id := CD.responsibility_id;
3229
3230 IF(l_Count = 0) THEN
3231 BSC_KPI_PVT.Create_Kpi_User_Access
3232 ( p_commit => p_commit
3233 , p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
3234 , x_return_status => x_return_status
3235 , x_msg_count => x_msg_count
3236 , x_msg_data => x_msg_data
3237 );
3238 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3240 END IF;
3241 END IF;
3242
3243 END LOOP;
3244
3245 EXCEPTION
3246 WHEN FND_API.G_EXC_ERROR THEN
3247 IF (x_msg_data IS NULL) THEN
3248 FND_MSG_PUB.Count_And_Get
3249 ( p_encoded => FND_API.G_FALSE
3250 , p_count => x_msg_count
3251 , p_data => x_msg_data
3252 );
3253 END IF;
3254 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3255 x_return_status := FND_API.G_RET_STS_ERROR;
3256 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3257 IF (x_msg_data IS NULL) THEN
3258 FND_MSG_PUB.Count_And_Get
3259 ( p_encoded => FND_API.G_FALSE
3260 , p_count => x_msg_count
3261 , p_data => x_msg_data
3262 );
3263 END IF;
3264 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3265 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3266 WHEN NO_DATA_FOUND THEN
3267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3268 IF (x_msg_data IS NOT NULL) THEN
3269 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_Access_For_Resp ';
3270 ELSE
3271 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_Access_For_Resp ';
3272 END IF;
3273 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3274 WHEN OTHERS THEN
3275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3276 IF (x_msg_data IS NOT NULL) THEN
3277 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_Access_For_Resp ';
3278 ELSE
3279 x_msg_data := SQLERRM||' AT BSC_KPI_PUB.Create_Kpi_Access_For_Resp ';
3280 END IF;
3281 END Create_Kpi_Access_For_Resp;
3282 /**************************************************************************************/
3283
3284 PROCEDURE Create_Kpi_User_Access(
3285 p_commit IN VARCHAR2 := FND_API.G_FALSE
3286 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
3287 ,x_return_status OUT NOCOPY VARCHAR2
3288 ,x_msg_count OUT NOCOPY NUMBER
3289 ,x_msg_data OUT NOCOPY VARCHAR2
3290 ) IS
3291
3292 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
3293 l_responsibility_key FND_RESPONSIBILITY.RESPONSIBILITY_KEY%TYPE;
3294 l_Kpi_Short_Name BSC_KPIS_B.SHORT_NAME%TYPE;
3295 l_Comma_Sep_Resp_Key VARCHAR2(32000):= NULL;
3296
3297 BEGIN
3298 FND_MSG_PUB.Initialize;
3299 x_return_status := FND_API.G_RET_STS_SUCCESS;
3300 l_Bsc_Kpi_Entity_Rec := p_Bsc_Kpi_Entity_Rec;
3301
3302 BEGIN
3303 SELECT K.SHORT_NAME
3304 INTO l_Kpi_Short_Name
3305 FROM BSC_KPIS_B K
3306 WHERE K.INDICATOR = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id;
3307
3308 EXCEPTION
3309 WHEN NO_DATA_FOUND THEN
3310 l_Kpi_Short_Name := NULL;
3311 END;
3312
3313 -- added for Bug#4563456
3314 l_Responsibility_Key := BSC_UTILITY.Get_Responsibility_Key;
3315
3316 IF (UPPER(l_Responsibility_Key) = 'BSC_DESIGNER') OR (UPPER(l_Responsibility_Key) = 'BSC_MANAGER') OR (l_Kpi_Short_Name IS NOT NULL )THEN
3317
3318 l_Comma_Sep_Resp_Key := l_Comma_Sep_Resp_Key ||','||
3319 bsc_utility.c_BSC_Manager ||','||
3320 bsc_utility.c_BSC_DESIGNER ||','||
3321 bsc_utility.c_BSC_PMD_USER ||','||
3322 bsc_utility.c_BIS_BID_RESP ||','||
3323 bsc_utility.c_BIS_DBI_ADMIN||','||
3324 l_responsibility_key;
3325 ELSE
3326 l_Comma_Sep_Resp_Key := l_responsibility_key;
3327
3328 END IF;
3329
3330 Create_Kpi_Access_For_Resp
3331 ( p_commit => p_commit
3332 , p_Comma_Sep_Resposibility_Key => l_Comma_Sep_Resp_Key
3333 , p_Bsc_Kpi_Entity_Rec => l_Bsc_Kpi_Entity_Rec
3334 , x_return_status => x_return_status
3335 , x_msg_count => x_msg_count
3336 , x_msg_data => x_msg_data
3337 );
3338 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3339 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3340 END IF;
3341
3342
3343
3344 EXCEPTION
3345 WHEN FND_API.G_EXC_ERROR THEN
3346 IF (x_msg_data IS NULL) THEN
3347 FND_MSG_PUB.Count_And_Get
3348 ( p_encoded => FND_API.G_FALSE
3349 , p_count => x_msg_count
3350 , p_data => x_msg_data
3351 );
3352 END IF;
3353 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3354 x_return_status := FND_API.G_RET_STS_ERROR;
3355 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3356 IF (x_msg_data IS NULL) THEN
3357 FND_MSG_PUB.Count_And_Get
3358 ( p_encoded => FND_API.G_FALSE
3359 , p_count => x_msg_count
3360 , p_data => x_msg_data
3361 );
3362 END IF;
3363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3364 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3365 WHEN NO_DATA_FOUND THEN
3366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3367 IF (x_msg_data IS NOT NULL) THEN
3368 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_User_Access ';
3369 ELSE
3370 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_User_Access ';
3371 END IF;
3372 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3373 WHEN OTHERS THEN
3374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3375 IF (x_msg_data IS NOT NULL) THEN
3376 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_User_Access ';
3377 ELSE
3378 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_User_Access ';
3379 END IF;
3380 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3381 end Create_Kpi_User_Access;
3382
3383 /************************************************************************************
3384 ************************************************************************************/
3385
3386 procedure Retrieve_Kpi_User_Access(
3387 p_commit IN varchar2 := FND_API.G_FALSE
3388 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
3389 ,x_Bsc_Kpi_Entity_Rec IN OUT NOCOPY BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
3390 ,x_return_status OUT NOCOPY varchar2
3391 ,x_msg_count OUT NOCOPY number
3392 ,x_msg_data OUT NOCOPY varchar2
3393 ) is
3394
3395 begin
3396 FND_MSG_PUB.Initialize;
3397 x_return_status := FND_API.G_RET_STS_SUCCESS;
3398 BSC_KPI_PVT.Retrieve_Kpi_User_Access( p_commit
3399 ,p_Bsc_Kpi_Entity_Rec
3400 ,x_Bsc_Kpi_Entity_Rec
3401 ,x_return_status
3402 ,x_msg_count
3403 ,x_msg_data);
3404
3405 EXCEPTION
3406 WHEN FND_API.G_EXC_ERROR THEN
3407 IF (x_msg_data IS NULL) THEN
3408 FND_MSG_PUB.Count_And_Get
3409 ( p_encoded => FND_API.G_FALSE
3410 , p_count => x_msg_count
3411 , p_data => x_msg_data
3412 );
3413 END IF;
3414 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3415 x_return_status := FND_API.G_RET_STS_ERROR;
3416 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3417 IF (x_msg_data IS NULL) THEN
3418 FND_MSG_PUB.Count_And_Get
3419 ( p_encoded => FND_API.G_FALSE
3420 , p_count => x_msg_count
3421 , p_data => x_msg_data
3422 );
3423 END IF;
3424 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3425 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3426 WHEN NO_DATA_FOUND THEN
3427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3428 IF (x_msg_data IS NOT NULL) THEN
3429 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_User_Access ';
3430 ELSE
3431 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_User_Access ';
3432 END IF;
3433 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3434 WHEN OTHERS THEN
3435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3436 IF (x_msg_data IS NOT NULL) THEN
3437 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_User_Access ';
3438 ELSE
3439 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_User_Access ';
3440 END IF;
3441 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3442 end Retrieve_Kpi_User_Access;
3443
3444 /************************************************************************************
3445 ************************************************************************************/
3446
3447 procedure Update_Kpi_User_Access(
3448 p_commit IN varchar2 := FND_API.G_FALSE
3449 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
3450 ,x_return_status OUT NOCOPY varchar2
3451 ,x_msg_count OUT NOCOPY number
3452 ,x_msg_data OUT NOCOPY varchar2
3453 ) is
3454
3455 begin
3456 FND_MSG_PUB.Initialize;
3457 x_return_status := FND_API.G_RET_STS_SUCCESS;
3458 BSC_KPI_PVT.Update_Kpi_User_Access( p_commit
3459 ,p_Bsc_Kpi_Entity_Rec
3460 ,x_return_status
3461 ,x_msg_count
3462 ,x_msg_data);
3463
3464 EXCEPTION
3465 WHEN FND_API.G_EXC_ERROR THEN
3466 IF (x_msg_data IS NULL) THEN
3467 FND_MSG_PUB.Count_And_Get
3468 ( p_encoded => FND_API.G_FALSE
3469 , p_count => x_msg_count
3470 , p_data => x_msg_data
3471 );
3472 END IF;
3473 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3474 x_return_status := FND_API.G_RET_STS_ERROR;
3475 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3476 IF (x_msg_data IS NULL) THEN
3477 FND_MSG_PUB.Count_And_Get
3478 ( p_encoded => FND_API.G_FALSE
3479 , p_count => x_msg_count
3480 , p_data => x_msg_data
3481 );
3482 END IF;
3483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3484 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3485 WHEN NO_DATA_FOUND THEN
3486 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3487 IF (x_msg_data IS NOT NULL) THEN
3488 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_User_Access ';
3489 ELSE
3490 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_User_Access ';
3491 END IF;
3492 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3493 WHEN OTHERS THEN
3494 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3495 IF (x_msg_data IS NOT NULL) THEN
3496 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_User_Access ';
3497 ELSE
3498 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_User_Access ';
3499 END IF;
3500 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3501 end Update_Kpi_User_Access;
3502
3503 /************************************************************************************
3504 ************************************************************************************/
3505
3506 procedure Delete_Kpi_User_Access(
3507 p_commit IN varchar2 := FND_API.G_FALSE
3508 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
3509 ,x_return_status OUT NOCOPY varchar2
3510 ,x_msg_count OUT NOCOPY number
3511 ,x_msg_data OUT NOCOPY varchar2
3512 ) is
3513
3514 begin
3515 FND_MSG_PUB.Initialize;
3516 x_return_status := FND_API.G_RET_STS_SUCCESS;
3517 BSC_KPI_PVT.Delete_Kpi_User_Access( p_commit
3518 ,p_Bsc_Kpi_Entity_Rec
3519 ,x_return_status
3520 ,x_msg_count
3521 ,x_msg_data);
3522
3523 EXCEPTION
3524 WHEN FND_API.G_EXC_ERROR THEN
3525 IF (x_msg_data IS NULL) THEN
3526 FND_MSG_PUB.Count_And_Get
3527 ( p_encoded => FND_API.G_FALSE
3528 , p_count => x_msg_count
3529 , p_data => x_msg_data
3530 );
3531 END IF;
3532 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3533 x_return_status := FND_API.G_RET_STS_ERROR;
3534 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3535 IF (x_msg_data IS NULL) THEN
3536 FND_MSG_PUB.Count_And_Get
3537 ( p_encoded => FND_API.G_FALSE
3538 , p_count => x_msg_count
3539 , p_data => x_msg_data
3540 );
3541 END IF;
3542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3543 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3544 WHEN NO_DATA_FOUND THEN
3545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3546 IF (x_msg_data IS NOT NULL) THEN
3547 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_User_Access ';
3548 ELSE
3549 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_User_Access ';
3550 END IF;
3551 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3552 WHEN OTHERS THEN
3553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3554 IF (x_msg_data IS NOT NULL) THEN
3555 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_User_Access ';
3556 ELSE
3557 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_User_Access ';
3558 END IF;
3559 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3560
3561 end Delete_Kpi_User_Access;
3562
3563 /************************************************************************************
3564 ************************************************************************************/
3565
3566
3567 procedure Create_Kpi_Default_Values(
3568 p_commit IN varchar2 := FND_API.G_FALSE
3569 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
3570 ,x_return_status OUT NOCOPY varchar2
3571 ,x_msg_count OUT NOCOPY number
3572 ,x_msg_data OUT NOCOPY varchar2
3573 ) is
3574
3575 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
3576
3577 begin
3578 FND_MSG_PUB.Initialize;
3579 x_return_status := FND_API.G_RET_STS_SUCCESS;
3580 -- set the local record equal to the passed record.
3581 l_Bsc_Kpi_Entity_Rec := p_Bsc_Kpi_Entity_Rec;
3582
3583 -- set some defaults if they are null.
3584 if l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Format_Mask is null then
3585 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Format_Mask := '$ #,###,##0.00';
3586 end if;
3587
3588 if l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Color_Method is null then
3589 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Color_Method := 1;
3590 end if;
3591
3592 if l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level1_Value is null then
3593 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level1_Value := 0;
3594 end if;
3595
3596 if l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Period_Name is null then
3597 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Period_Name := '5-1';
3598 end if;
3599
3600 if l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Series_Name is null then
3601 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Series_Name := 'Default 0';
3602 end if;
3603
3604 if l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level1_Text is null then
3605 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level1_Text := 'XXX';
3606 end if;
3607
3608 select distinct c.source
3609 into l_Bsc_Kpi_Entity_Rec.Bsc_Measure_Source
3610 from BSC_KPI_ANALYSIS_MEASURES_B a,
3611 BSC_SYS_DATASETS_B b,
3612 BSC_SYS_MEASURES c,
3613 BSC_DB_COLOR_AO_DEFAULTS_V d
3614 where d.indicator = l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id and
3615 d.indicator = a.indicator and
3616 d.a0_default = a.analysis_option0 and
3617 a.analysis_option1 = 0 and
3618 a.analysis_option2 = 0 and
3619 a.dataset_id = b.dataset_id and
3620 b.measure_id1 = c.measure_id;
3621
3622
3623 BSC_KPI_PVT.Create_Kpi_Default_Values( p_commit
3624 ,l_Bsc_Kpi_Entity_Rec
3625 ,x_return_status
3626 ,x_msg_count
3627 ,x_msg_data);
3628
3629 EXCEPTION
3630 WHEN FND_API.G_EXC_ERROR THEN
3631 IF (x_msg_data IS NULL) THEN
3632 FND_MSG_PUB.Count_And_Get
3633 ( p_encoded => FND_API.G_FALSE
3634 , p_count => x_msg_count
3635 , p_data => x_msg_data
3636 );
3637 END IF;
3638 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3639 x_return_status := FND_API.G_RET_STS_ERROR;
3640 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3641 IF (x_msg_data IS NULL) THEN
3642 FND_MSG_PUB.Count_And_Get
3643 ( p_encoded => FND_API.G_FALSE
3644 , p_count => x_msg_count
3645 , p_data => x_msg_data
3646 );
3647 END IF;
3648 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3649 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3650 WHEN NO_DATA_FOUND THEN
3651 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3652 IF (x_msg_data IS NOT NULL) THEN
3653 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_Default_Values ';
3654 ELSE
3655 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_Default_Values ';
3656 END IF;
3657 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3658 WHEN OTHERS THEN
3659 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3660 IF (x_msg_data IS NOT NULL) THEN
3661 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_Default_Values ';
3662 ELSE
3663 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_Default_Values ';
3664 END IF;
3665 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3666 end Create_Kpi_Default_Values;
3667
3668 /************************************************************************************
3669 ************************************************************************************/
3670
3671 procedure Retrieve_Kpi_Default_Values(
3672 p_commit IN varchar2 := FND_API.G_FALSE
3673 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
3674 ,x_Bsc_Kpi_Entity_Rec IN OUT NOCOPY BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
3675 ,x_return_status OUT NOCOPY varchar2
3676 ,x_msg_count OUT NOCOPY number
3677 ,x_msg_data OUT NOCOPY varchar2
3678 ) is
3679
3680 begin
3681 FND_MSG_PUB.Initialize;
3682 x_return_status := FND_API.G_RET_STS_SUCCESS;
3683 BSC_KPI_PVT.Retrieve_Kpi_Default_Values( p_commit
3684 ,p_Bsc_Kpi_Entity_Rec
3685 ,x_Bsc_Kpi_Entity_Rec
3686 ,x_return_status
3687 ,x_msg_count
3688 ,x_msg_data);
3689
3690 EXCEPTION
3691 WHEN FND_API.G_EXC_ERROR THEN
3692 IF (x_msg_data IS NULL) THEN
3693 FND_MSG_PUB.Count_And_Get
3694 ( p_encoded => FND_API.G_FALSE
3695 , p_count => x_msg_count
3696 , p_data => x_msg_data
3697 );
3698 END IF;
3699 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3700 x_return_status := FND_API.G_RET_STS_ERROR;
3701 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3702 IF (x_msg_data IS NULL) THEN
3703 FND_MSG_PUB.Count_And_Get
3704 ( p_encoded => FND_API.G_FALSE
3705 , p_count => x_msg_count
3706 , p_data => x_msg_data
3707 );
3708 END IF;
3709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3710 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3711 WHEN NO_DATA_FOUND THEN
3712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3713 IF (x_msg_data IS NOT NULL) THEN
3714 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_Default_Values ';
3715 ELSE
3716 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_Default_Values ';
3717 END IF;
3718 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3719 WHEN OTHERS THEN
3720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3721 IF (x_msg_data IS NOT NULL) THEN
3722 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_Default_Values ';
3723 ELSE
3724 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_Default_Values ';
3725 END IF;
3726 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3727
3728 end Retrieve_Kpi_Default_Values;
3729
3730 /************************************************************************************
3731 ************************************************************************************/
3732
3733 procedure Update_Kpi_Default_Values(
3734 p_commit IN varchar2 := FND_API.G_FALSE
3735 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
3736 ,x_return_status OUT NOCOPY varchar2
3737 ,x_msg_count OUT NOCOPY number
3738 ,x_msg_data OUT NOCOPY varchar2
3739 ) is
3740
3741 begin
3742 FND_MSG_PUB.Initialize;
3743 x_return_status := FND_API.G_RET_STS_SUCCESS;
3744 BSC_KPI_PVT.Update_Kpi_Default_Values( p_commit
3745 ,p_Bsc_Kpi_Entity_Rec
3746 ,x_return_status
3747 ,x_msg_count
3748 ,x_msg_data);
3749
3750 EXCEPTION
3751 WHEN FND_API.G_EXC_ERROR THEN
3752 IF (x_msg_data IS NULL) THEN
3753 FND_MSG_PUB.Count_And_Get
3754 ( p_encoded => FND_API.G_FALSE
3755 , p_count => x_msg_count
3756 , p_data => x_msg_data
3757 );
3758 END IF;
3759 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3760 x_return_status := FND_API.G_RET_STS_ERROR;
3761 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3762 IF (x_msg_data IS NULL) THEN
3763 FND_MSG_PUB.Count_And_Get
3764 ( p_encoded => FND_API.G_FALSE
3765 , p_count => x_msg_count
3766 , p_data => x_msg_data
3767 );
3768 END IF;
3769 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3770 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3771 WHEN NO_DATA_FOUND THEN
3772 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3773 IF (x_msg_data IS NOT NULL) THEN
3774 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_Default_Values ';
3775 ELSE
3776 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_Default_Values ';
3777 END IF;
3778 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3779 WHEN OTHERS THEN
3780 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3781 IF (x_msg_data IS NOT NULL) THEN
3782 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_Default_Values ';
3783 ELSE
3784 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_Default_Values ';
3785 END IF;
3786 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3787
3788 end Update_Kpi_Default_Values;
3789
3790 /************************************************************************************
3791 ************************************************************************************/
3792
3793 procedure Delete_Kpi_Default_Values(
3794 p_commit IN varchar2 := FND_API.G_FALSE
3795 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
3796 ,x_return_status OUT NOCOPY varchar2
3797 ,x_msg_count OUT NOCOPY number
3798 ,x_msg_data OUT NOCOPY varchar2
3799 ) is
3800
3801 begin
3802 FND_MSG_PUB.Initialize;
3803 x_return_status := FND_API.G_RET_STS_SUCCESS;
3804 BSC_KPI_PVT.Delete_Kpi_Default_Values( p_commit
3805 ,p_Bsc_Kpi_Entity_Rec
3806 ,x_return_status
3807 ,x_msg_count
3808 ,x_msg_data);
3809
3810
3811 EXCEPTION
3812 WHEN FND_API.G_EXC_ERROR THEN
3813 IF (x_msg_data IS NULL) THEN
3814 FND_MSG_PUB.Count_And_Get
3815 ( p_encoded => FND_API.G_FALSE
3816 , p_count => x_msg_count
3817 , p_data => x_msg_data
3818 );
3819 END IF;
3820 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3821 x_return_status := FND_API.G_RET_STS_ERROR;
3822 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3823 IF (x_msg_data IS NULL) THEN
3824 FND_MSG_PUB.Count_And_Get
3825 ( p_encoded => FND_API.G_FALSE
3826 , p_count => x_msg_count
3827 , p_data => x_msg_data
3828 );
3829 END IF;
3830 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3831 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3832 WHEN NO_DATA_FOUND THEN
3833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3834 IF (x_msg_data IS NOT NULL) THEN
3835 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_Default_Values ';
3836 ELSE
3837 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_Default_Values ';
3838 END IF;
3839 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3840 WHEN OTHERS THEN
3841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3842 IF (x_msg_data IS NOT NULL) THEN
3843 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_Default_Values ';
3844 ELSE
3845 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_Default_Values ';
3846 END IF;
3847 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3848
3849 end Delete_Kpi_Default_Values;
3850
3851 /************************************************************************************
3852 ************************************************************************************/
3853
3854 -- :- This procedure assigns a KPI to a Tab. BSC does not allow the user to assign
3855 -- a KPI to a Tab without assigning the Kpi Group first. Therefore this assingns
3856 -- the group first, if it hasn't been assigned.
3857
3858 procedure Create_Kpi_In_Tab(
3859 p_commit IN varchar2 := FND_API.G_FALSE
3860 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
3861 ,x_return_status OUT NOCOPY varchar2
3862 ,x_msg_count OUT NOCOPY number
3863 ,x_msg_data OUT NOCOPY varchar2
3864 ) is
3865
3866 begin
3867 FND_MSG_PUB.Initialize;
3868 x_return_status := FND_API.G_RET_STS_SUCCESS;
3869 BSC_KPI_PVT.Create_Kpi_In_Tab( p_commit
3870 ,p_Bsc_Kpi_Entity_Rec
3871 ,x_return_status
3872 ,x_msg_count
3873 ,x_msg_data);
3874
3875
3876 EXCEPTION
3877 WHEN FND_API.G_EXC_ERROR THEN
3878 IF (x_msg_data IS NULL) THEN
3879 FND_MSG_PUB.Count_And_Get
3880 ( p_encoded => FND_API.G_FALSE
3881 , p_count => x_msg_count
3882 , p_data => x_msg_data
3883 );
3884 END IF;
3885 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3886 x_return_status := FND_API.G_RET_STS_ERROR;
3887 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3888 IF (x_msg_data IS NULL) THEN
3889 FND_MSG_PUB.Count_And_Get
3890 ( p_encoded => FND_API.G_FALSE
3891 , p_count => x_msg_count
3892 , p_data => x_msg_data
3893 );
3894 END IF;
3895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3896 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3897 WHEN NO_DATA_FOUND THEN
3898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3899 IF (x_msg_data IS NOT NULL) THEN
3900 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_In_Tab ';
3901 ELSE
3902 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_In_Tab ';
3903 END IF;
3904 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3905 WHEN OTHERS THEN
3906 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3907 IF (x_msg_data IS NOT NULL) THEN
3908 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Kpi_In_Tab ';
3909 ELSE
3910 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Kpi_In_Tab ';
3911 END IF;
3912 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3913
3914 end Create_Kpi_In_Tab;
3915
3916 /************************************************************************************
3917 ************************************************************************************/
3918
3919 procedure Retrieve_Kpi_In_Tab(
3920 p_commit IN varchar2 := FND_API.G_FALSE
3921 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
3922 ,x_Bsc_Kpi_Entity_Rec IN OUT NOCOPY BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
3923 ,x_return_status OUT NOCOPY varchar2
3924 ,x_msg_count OUT NOCOPY number
3925 ,x_msg_data OUT NOCOPY varchar2
3926 ) is
3927
3928 begin
3929 FND_MSG_PUB.Initialize;
3930 x_return_status := FND_API.G_RET_STS_SUCCESS;
3931 BSC_KPI_PVT.Retrieve_Kpi_In_Tab( p_commit
3932 ,p_Bsc_Kpi_Entity_Rec
3933 ,x_Bsc_Kpi_Entity_Rec
3934 ,x_return_status
3935 ,x_msg_count
3936 ,x_msg_data);
3937
3938 EXCEPTION
3939 WHEN FND_API.G_EXC_ERROR THEN
3940 IF (x_msg_data IS NULL) THEN
3941 FND_MSG_PUB.Count_And_Get
3942 ( p_encoded => FND_API.G_FALSE
3943 , p_count => x_msg_count
3944 , p_data => x_msg_data
3945 );
3946 END IF;
3947 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
3948 x_return_status := FND_API.G_RET_STS_ERROR;
3949 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3950 IF (x_msg_data IS NULL) THEN
3951 FND_MSG_PUB.Count_And_Get
3952 ( p_encoded => FND_API.G_FALSE
3953 , p_count => x_msg_count
3954 , p_data => x_msg_data
3955 );
3956 END IF;
3957 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3958 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
3959 WHEN NO_DATA_FOUND THEN
3960 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3961 IF (x_msg_data IS NOT NULL) THEN
3962 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_In_Tab ';
3963 ELSE
3964 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_In_Tab ';
3965 END IF;
3966 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
3967 WHEN OTHERS THEN
3968 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3969 IF (x_msg_data IS NOT NULL) THEN
3970 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Retrieve_Kpi_In_Tab ';
3971 ELSE
3972 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Retrieve_Kpi_In_Tab ';
3973 END IF;
3974 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
3975 end Retrieve_Kpi_In_Tab;
3976
3977 /************************************************************************************
3978 ************************************************************************************/
3979
3980 procedure Update_Kpi_In_Tab(
3981 p_commit IN varchar2 := FND_API.G_FALSE
3982 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
3983 ,x_return_status OUT NOCOPY varchar2
3984 ,x_msg_count OUT NOCOPY number
3985 ,x_msg_data OUT NOCOPY varchar2
3986 ) is
3987
3988 begin
3989
3990 /*
3991 -- If Dim set Id and Dim Level Id are null then assign 0 to both.
3992 if l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Set_Id is null then
3993 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Set_Id := 0;
3994 end if;
3995
3996 if l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level_Id is null then
3997 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Dim_Level_Id := 0;
3998 end if;
3999 */
4000 FND_MSG_PUB.Initialize;
4001 x_return_status := FND_API.G_RET_STS_SUCCESS;
4002 BSC_KPI_PVT.Update_Kpi_In_Tab( p_commit
4003 ,p_Bsc_Kpi_Entity_Rec
4004 ,x_return_status
4005 ,x_msg_count
4006 ,x_msg_data);
4007
4008 EXCEPTION
4009 WHEN FND_API.G_EXC_ERROR THEN
4010 IF (x_msg_data IS NULL) THEN
4011 FND_MSG_PUB.Count_And_Get
4012 ( p_encoded => FND_API.G_FALSE
4013 , p_count => x_msg_count
4014 , p_data => x_msg_data
4015 );
4016 END IF;
4017 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
4018 x_return_status := FND_API.G_RET_STS_ERROR;
4019 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4020 IF (x_msg_data IS NULL) THEN
4021 FND_MSG_PUB.Count_And_Get
4022 ( p_encoded => FND_API.G_FALSE
4023 , p_count => x_msg_count
4024 , p_data => x_msg_data
4025 );
4026 END IF;
4027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4028 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
4029 WHEN NO_DATA_FOUND THEN
4030 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4031 IF (x_msg_data IS NOT NULL) THEN
4032 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_In_Tab ';
4033 ELSE
4034 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_In_Tab ';
4035 END IF;
4036 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
4037 WHEN OTHERS THEN
4038 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4039 IF (x_msg_data IS NOT NULL) THEN
4040 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_In_Tab ';
4041 ELSE
4042 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_In_Tab ';
4043 END IF;
4044 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
4045
4046 end Update_Kpi_In_Tab;
4047
4048 /************************************************************************************
4049 ************************************************************************************/
4050
4051 procedure Delete_Kpi_In_Tab(
4052 p_commit IN varchar2 := FND_API.G_FALSE
4053 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
4054 ,x_return_status OUT NOCOPY varchar2
4055 ,x_msg_count OUT NOCOPY number
4056 ,x_msg_data OUT NOCOPY varchar2
4057 ) is
4058
4059 begin
4060 FND_MSG_PUB.Initialize;
4061 x_return_status := FND_API.G_RET_STS_SUCCESS;
4062 BSC_KPI_PVT.Delete_Kpi_In_Tab( p_commit
4063 ,p_Bsc_Kpi_Entity_Rec
4064 ,x_return_status
4065 ,x_msg_count
4066 ,x_msg_data);
4067
4068
4069 EXCEPTION
4070 WHEN FND_API.G_EXC_ERROR THEN
4071 IF (x_msg_data IS NULL) THEN
4072 FND_MSG_PUB.Count_And_Get
4073 ( p_encoded => FND_API.G_FALSE
4074 , p_count => x_msg_count
4075 , p_data => x_msg_data
4076 );
4077 END IF;
4078 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
4079 x_return_status := FND_API.G_RET_STS_ERROR;
4080 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4081 IF (x_msg_data IS NULL) THEN
4082 FND_MSG_PUB.Count_And_Get
4083 ( p_encoded => FND_API.G_FALSE
4084 , p_count => x_msg_count
4085 , p_data => x_msg_data
4086 );
4087 END IF;
4088 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4089 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
4090 WHEN NO_DATA_FOUND THEN
4091 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4092 IF (x_msg_data IS NOT NULL) THEN
4093 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_In_Tab ';
4094 ELSE
4095 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_In_Tab ';
4096 END IF;
4097 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
4098 WHEN OTHERS THEN
4099 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4100 IF (x_msg_data IS NOT NULL) THEN
4101 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Kpi_In_Tab ';
4102 ELSE
4103 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Kpi_In_Tab ';
4104 END IF;
4105 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
4106
4107 end Delete_Kpi_In_Tab;
4108
4109 /************************************************************************************
4110 ************************************************************************************/
4111
4112 procedure Update_Kpi_Time_Stamp(
4113 p_commit IN varchar2 := FND_API.G_FALSE
4114 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
4115 ,x_return_status OUT NOCOPY varchar2
4116 ,x_msg_count OUT NOCOPY number
4117 ,x_msg_data OUT NOCOPY varchar2
4118 ) is
4119
4120 begin
4121 FND_MSG_PUB.Initialize;
4122 x_return_status := FND_API.G_RET_STS_SUCCESS;
4123 BSC_KPI_PVT.Update_Kpi_Time_Stamp( p_commit
4124 ,p_Bsc_Kpi_Entity_Rec
4125 ,x_return_status
4126 ,x_msg_count
4127 ,x_msg_data);
4128
4129 EXCEPTION
4130 WHEN FND_API.G_EXC_ERROR THEN
4131 IF (x_msg_data IS NULL) THEN
4132 FND_MSG_PUB.Count_And_Get
4133 ( p_encoded => FND_API.G_FALSE
4134 , p_count => x_msg_count
4135 , p_data => x_msg_data
4136 );
4137 END IF;
4138 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
4139 x_return_status := FND_API.G_RET_STS_ERROR;
4140 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4141 IF (x_msg_data IS NULL) THEN
4142 FND_MSG_PUB.Count_And_Get
4143 ( p_encoded => FND_API.G_FALSE
4144 , p_count => x_msg_count
4145 , p_data => x_msg_data
4146 );
4147 END IF;
4148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4149 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
4150 WHEN NO_DATA_FOUND THEN
4151 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4152 IF (x_msg_data IS NOT NULL) THEN
4153 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_Time_Stamp ';
4154 ELSE
4155 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_Time_Stamp ';
4156 END IF;
4157 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
4158 WHEN OTHERS THEN
4159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4160 IF (x_msg_data IS NOT NULL) THEN
4161 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Update_Kpi_Time_Stamp ';
4162 ELSE
4163 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Update_Kpi_Time_Stamp ';
4164 END IF;
4165 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
4166
4167 end Update_Kpi_Time_Stamp;
4168
4169 /************************************************************************************
4170 ************************************************************************************/
4171
4172 procedure Create_Master_Kpi(
4173 p_commit IN varchar2 := FND_API.G_FALSE
4174 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
4175 ,x_return_status OUT NOCOPY varchar2
4176 ,x_msg_count OUT NOCOPY number
4177 ,x_msg_data OUT NOCOPY varchar2
4178 ) is
4179
4180 begin
4181 FND_MSG_PUB.Initialize;
4182 x_return_status := FND_API.G_RET_STS_SUCCESS;
4183 BSC_KPI_PVT.Create_Master_Kpi( p_commit
4184 ,p_Bsc_Kpi_Entity_Rec
4185 ,x_return_status
4186 ,x_msg_count
4187 ,x_msg_data);
4188
4189 EXCEPTION
4190 WHEN FND_API.G_EXC_ERROR THEN
4191 IF (x_msg_data IS NULL) THEN
4192 FND_MSG_PUB.Count_And_Get
4193 ( p_encoded => FND_API.G_FALSE
4194 , p_count => x_msg_count
4195 , p_data => x_msg_data
4196 );
4197 END IF;
4198 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
4199 x_return_status := FND_API.G_RET_STS_ERROR;
4200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4201 IF (x_msg_data IS NULL) THEN
4202 FND_MSG_PUB.Count_And_Get
4203 ( p_encoded => FND_API.G_FALSE
4204 , p_count => x_msg_count
4205 , p_data => x_msg_data
4206 );
4207 END IF;
4208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4209 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
4210 WHEN NO_DATA_FOUND THEN
4211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4212 IF (x_msg_data IS NOT NULL) THEN
4213 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Master_Kpi ';
4214 ELSE
4215 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Master_Kpi ';
4216 END IF;
4217 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
4218 WHEN OTHERS THEN
4219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4220 IF (x_msg_data IS NOT NULL) THEN
4221 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Master_Kpi ';
4222 ELSE
4223 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Master_Kpi ';
4224 END IF;
4225 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
4226
4227 end Create_Master_Kpi;
4228
4229 /************************************************************************************
4230 ************************************************************************************/
4231
4232 procedure Create_Shared_Kpi(
4233 p_commit IN varchar2 := FND_API.G_FALSE
4234 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
4235 ,x_return_status OUT NOCOPY varchar2
4236 ,x_msg_count OUT NOCOPY number
4237 ,x_msg_data OUT NOCOPY varchar2
4238 ) is
4239
4240 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
4241
4242 begin
4243 FND_MSG_PUB.Initialize;
4244 x_return_status := FND_API.G_RET_STS_SUCCESS;
4245 l_Bsc_Kpi_Entity_Rec := p_Bsc_Kpi_Entity_Rec;
4246
4247 if p_Bsc_Kpi_Entity_Rec.Bsc_Language is null then
4248 l_Bsc_Kpi_Entity_Rec.Bsc_Language := 'US';
4249 end if;
4250 if p_Bsc_Kpi_Entity_Rec.Bsc_Source_Language is null then
4251 l_Bsc_Kpi_Entity_Rec.Bsc_Source_Language := 'US';
4252 end if;
4253 if p_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Id is null then
4254 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Periodicity_Id := 5;
4255 end if;
4256
4257
4258 BSC_KPI_PVT.Create_Shared_Kpi( p_commit
4259 ,l_Bsc_Kpi_Entity_Rec
4260 ,x_return_status
4261 ,x_msg_count
4262 ,x_msg_data);
4263
4264 EXCEPTION
4265 WHEN FND_API.G_EXC_ERROR THEN
4266 IF (x_msg_data IS NULL) THEN
4267 FND_MSG_PUB.Count_And_Get
4268 ( p_encoded => FND_API.G_FALSE
4269 , p_count => x_msg_count
4270 , p_data => x_msg_data
4271 );
4272 END IF;
4273 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
4274 x_return_status := FND_API.G_RET_STS_ERROR;
4275 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4276 IF (x_msg_data IS NULL) THEN
4277 FND_MSG_PUB.Count_And_Get
4278 ( p_encoded => FND_API.G_FALSE
4279 , p_count => x_msg_count
4280 , p_data => x_msg_data
4281 );
4282 END IF;
4283 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4284 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
4285 WHEN NO_DATA_FOUND THEN
4286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4287 IF (x_msg_data IS NOT NULL) THEN
4288 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Shared_Kpi ';
4289 ELSE
4290 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Shared_Kpi ';
4291 END IF;
4292 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
4293 WHEN OTHERS THEN
4294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4295 IF (x_msg_data IS NOT NULL) THEN
4296 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Create_Shared_Kpi ';
4297 ELSE
4298 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Create_Shared_Kpi ';
4299 END IF;
4300 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
4301
4302 end Create_Shared_Kpi;
4303
4304 /************************************************************************************
4305 ************************************************************************************/
4306
4307 procedure Set_Default_Option(
4308 p_commit IN varchar2 := FND_API.G_FALSE
4309 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
4310 ,x_return_status OUT NOCOPY varchar2
4311 ,x_msg_count OUT NOCOPY number
4312 ,x_msg_data OUT NOCOPY varchar2
4313 ) is
4314
4315 begin
4316 FND_MSG_PUB.Initialize;
4317 x_return_status := FND_API.G_RET_STS_SUCCESS;
4318 BSC_KPI_PVT.Set_Default_Option( p_commit
4319 ,p_Bsc_Kpi_Entity_Rec
4320 ,x_return_status
4321 ,x_msg_count
4322 ,x_msg_data);
4323
4324
4325 EXCEPTION
4326 WHEN FND_API.G_EXC_ERROR THEN
4327 IF (x_msg_data IS NULL) THEN
4328 FND_MSG_PUB.Count_And_Get
4329 ( p_encoded => FND_API.G_FALSE
4330 , p_count => x_msg_count
4331 , p_data => x_msg_data
4332 );
4333 END IF;
4334 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
4335 x_return_status := FND_API.G_RET_STS_ERROR;
4336 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4337 IF (x_msg_data IS NULL) THEN
4338 FND_MSG_PUB.Count_And_Get
4339 ( p_encoded => FND_API.G_FALSE
4340 , p_count => x_msg_count
4341 , p_data => x_msg_data
4342 );
4343 END IF;
4344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4345 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
4346 WHEN NO_DATA_FOUND THEN
4347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4348 IF (x_msg_data IS NOT NULL) THEN
4349 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Set_Default_Option ';
4350 ELSE
4351 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Set_Default_Option ';
4352 END IF;
4353 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
4354 WHEN OTHERS THEN
4355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4356 IF (x_msg_data IS NOT NULL) THEN
4357 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Set_Default_Option ';
4358 ELSE
4359 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Set_Default_Option ';
4360 END IF;
4361 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
4362
4363 end Set_Default_Option;
4364
4365 /************************************************************************************
4366 ************************************************************************************/
4367
4368 procedure Set_Default_Option_MG(
4369 p_commit IN varchar2 := FND_API.G_FALSE
4370 ,p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
4371 ,x_return_status OUT NOCOPY varchar2
4372 ,x_msg_count OUT NOCOPY number
4373 ,x_msg_data OUT NOCOPY varchar2
4374 ) is
4375
4376 begin
4377 FND_MSG_PUB.Initialize;
4378 x_return_status := FND_API.G_RET_STS_SUCCESS;
4379 BSC_KPI_PUB.Set_Default_Option_MG( p_commit
4380 ,p_Bsc_Kpi_Entity_Rec
4381 ,x_return_status
4382 ,x_msg_count
4383 ,x_msg_data);
4384
4385 EXCEPTION
4386 WHEN FND_API.G_EXC_ERROR THEN
4387 IF (x_msg_data IS NULL) THEN
4388 FND_MSG_PUB.Count_And_Get
4389 ( p_encoded => FND_API.G_FALSE
4390 , p_count => x_msg_count
4391 , p_data => x_msg_data
4392 );
4393 END IF;
4394 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
4395 x_return_status := FND_API.G_RET_STS_ERROR;
4396 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4397 IF (x_msg_data IS NULL) THEN
4398 FND_MSG_PUB.Count_And_Get
4399 ( p_encoded => FND_API.G_FALSE
4400 , p_count => x_msg_count
4401 , p_data => x_msg_data
4402 );
4403 END IF;
4404 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4405 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
4406 WHEN NO_DATA_FOUND THEN
4407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4408 IF (x_msg_data IS NOT NULL) THEN
4409 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Set_Default_Option_MG ';
4410 ELSE
4411 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Set_Default_Option_MG ';
4412 END IF;
4413 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
4414 WHEN OTHERS THEN
4415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4416 IF (x_msg_data IS NOT NULL) THEN
4417 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Set_Default_Option_MG ';
4418 ELSE
4419 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Set_Default_Option_MG ';
4420 END IF;
4421 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
4422
4423 end Set_Default_Option_MG;
4424
4425 /************************************************************************************
4426 ************************************************************************************/
4427 procedure Assign_Analysis_Option(
4428 p_Bsc_kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_kpi_Entity_Rec
4429 ,x_return_status OUT NOCOPY varchar2
4430 ,x_msg_count OUT NOCOPY number
4431 ,x_msg_data OUT NOCOPY varchar2
4432 ) IS
4433
4434 Begin
4435 FND_MSG_PUB.Initialize;
4436 x_return_status := FND_API.G_RET_STS_SUCCESS;
4437 BSC_KPI_PVT.Assign_Analysis_Option(
4438 p_Bsc_kpi_Entity_Rec
4439 ,x_return_status
4440 ,x_msg_count
4441 ,x_msg_data );
4442
4443 EXCEPTION
4444 WHEN FND_API.G_EXC_ERROR THEN
4445 IF (x_msg_data IS NULL) THEN
4446 FND_MSG_PUB.Count_And_Get
4447 ( p_encoded => FND_API.G_FALSE
4448 , p_count => x_msg_count
4449 , p_data => x_msg_data
4450 );
4451 END IF;
4452 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
4453 x_return_status := FND_API.G_RET_STS_ERROR;
4454 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4455 IF (x_msg_data IS NULL) THEN
4456 FND_MSG_PUB.Count_And_Get
4457 ( p_encoded => FND_API.G_FALSE
4458 , p_count => x_msg_count
4459 , p_data => x_msg_data
4460 );
4461 END IF;
4462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4463 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
4464 WHEN NO_DATA_FOUND THEN
4465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4466 IF (x_msg_data IS NOT NULL) THEN
4467 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Assign_Analysis_Option ';
4468 ELSE
4469 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Assign_Analysis_Option ';
4470 END IF;
4471 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
4472 WHEN OTHERS THEN
4473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4474 IF (x_msg_data IS NOT NULL) THEN
4475 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Assign_Analysis_Option ';
4476 ELSE
4477 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Assign_Analysis_Option ';
4478 END IF;
4479 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
4480
4481 End Assign_Analysis_Option;
4482
4483 /************************************************************************************
4484 ************************************************************************************/
4485
4486 procedure Unassign_Analysis_Option(
4487 p_Bsc_kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_kpi_Entity_Rec
4488 ,x_return_status OUT NOCOPY varchar2
4489 ,x_msg_count OUT NOCOPY number
4490 ,x_msg_data OUT NOCOPY varchar2
4491 ) IS
4492
4493 Begin
4494 FND_MSG_PUB.Initialize;
4495 x_return_status := FND_API.G_RET_STS_SUCCESS;
4496 BSC_KPI_PVT.Unassign_Analysis_Option(
4497 p_Bsc_kpi_Entity_Rec
4498 ,x_return_status
4499 ,x_msg_count
4500 ,x_msg_data );
4501
4502 EXCEPTION
4503 WHEN FND_API.G_EXC_ERROR THEN
4504 IF (x_msg_data IS NULL) THEN
4505 FND_MSG_PUB.Count_And_Get
4506 ( p_encoded => FND_API.G_FALSE
4507 , p_count => x_msg_count
4508 , p_data => x_msg_data
4509 );
4510 END IF;
4511 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
4512 x_return_status := FND_API.G_RET_STS_ERROR;
4513 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4514 IF (x_msg_data IS NULL) THEN
4515 FND_MSG_PUB.Count_And_Get
4516 ( p_encoded => FND_API.G_FALSE
4517 , p_count => x_msg_count
4518 , p_data => x_msg_data
4519 );
4520 END IF;
4521 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4522 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
4523 WHEN NO_DATA_FOUND THEN
4524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4525 IF (x_msg_data IS NOT NULL) THEN
4526 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Unassign_Analysis_Option ';
4527 ELSE
4528 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Unassign_Analysis_Option ';
4529 END IF;
4530 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
4531 WHEN OTHERS THEN
4532 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4533 IF (x_msg_data IS NOT NULL) THEN
4534 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Unassign_Analysis_Option ';
4535 ELSE
4536 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Unassign_Analysis_Option ';
4537 END IF;
4538 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
4539
4540 End Unassign_Analysis_Option;
4541
4542
4543 /************************************************************************************
4544 ************************************************************************************/
4545
4546 function Is_Analysis_Option_Selected(
4547 p_Bsc_kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_kpi_Entity_Rec
4548 ,x_return_status OUT NOCOPY varchar2
4549 ,x_msg_count OUT NOCOPY number
4550 ,x_msg_data OUT NOCOPY varchar2
4551 ) return varchar2 IS
4552 l_temp varchar2(5);
4553 Begin
4554 FND_MSG_PUB.Initialize;
4555 x_return_status := FND_API.G_RET_STS_SUCCESS;
4556 l_temp := BSC_KPI_PVT.Is_Analysis_Option_Selected(
4557 p_Bsc_kpi_Entity_Rec
4558 ,x_return_status
4559 ,x_msg_count
4560 ,x_msg_data );
4561 return l_temp;
4562
4563 EXCEPTION
4564 WHEN FND_API.G_EXC_ERROR THEN
4565 IF (x_msg_data IS NULL) THEN
4566 FND_MSG_PUB.Count_And_Get
4567 ( p_encoded => FND_API.G_FALSE
4568 , p_count => x_msg_count
4569 , p_data => x_msg_data
4570 );
4571 END IF;
4572 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
4573 x_return_status := FND_API.G_RET_STS_ERROR;
4574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4575 IF (x_msg_data IS NULL) THEN
4576 FND_MSG_PUB.Count_And_Get
4577 ( p_encoded => FND_API.G_FALSE
4578 , p_count => x_msg_count
4579 , p_data => x_msg_data
4580 );
4581 END IF;
4582 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4583 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
4584 WHEN NO_DATA_FOUND THEN
4585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4586 IF (x_msg_data IS NOT NULL) THEN
4587 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Is_Analysis_Option_Selected ';
4588 ELSE
4589 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Is_Analysis_Option_Selected ';
4590 END IF;
4591 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
4592 WHEN OTHERS THEN
4593 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4594 IF (x_msg_data IS NOT NULL) THEN
4595 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Is_Analysis_Option_Selected ';
4596 ELSE
4597 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Is_Analysis_Option_Selected ';
4598 END IF;
4599 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
4600
4601 end Is_Analysis_Option_Selected;
4602
4603 /************************************************************************************
4604 ************************************************************************************/
4605
4606 function Is_Leaf_Analysis_Option(
4607 p_Bsc_kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_kpi_Entity_Rec
4608 ,x_return_status OUT NOCOPY varchar2
4609 ,x_msg_count OUT NOCOPY number
4610 ,x_msg_data OUT NOCOPY varchar2
4611 ) return varchar2 IS
4612 l_temp varchar2(5);
4613 Begin
4614 FND_MSG_PUB.Initialize;
4615 x_return_status := FND_API.G_RET_STS_SUCCESS;
4616 --DBMS_OUTPUT.PUT_LINE('Begin BSC_KPI_PUB.Is_Leaf_Analysis_Option ');
4617
4618 l_temp := BSC_KPI_PVT.Is_Leaf_Analysis_Option(
4619 p_Bsc_kpi_Entity_Rec
4620 ,x_return_status
4621 ,x_msg_count
4622 ,x_msg_data );
4623
4624 --DBMS_OUTPUT.PUT_LINE('End BSC_KPI_PUB.Is_Leaf_Analysis_Option - return ' || l_temp );
4625
4626 return l_temp;
4627
4628 EXCEPTION
4629 WHEN FND_API.G_EXC_ERROR THEN
4630 IF (x_msg_data IS NULL) THEN
4631 FND_MSG_PUB.Count_And_Get
4632 ( p_encoded => FND_API.G_FALSE
4633 , p_count => x_msg_count
4634 , p_data => x_msg_data
4635 );
4636 END IF;
4637 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
4638 x_return_status := FND_API.G_RET_STS_ERROR;
4639 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4640 IF (x_msg_data IS NULL) THEN
4641 FND_MSG_PUB.Count_And_Get
4642 ( p_encoded => FND_API.G_FALSE
4643 , p_count => x_msg_count
4644 , p_data => x_msg_data
4645 );
4646 END IF;
4647 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4648 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
4649 WHEN NO_DATA_FOUND THEN
4650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4651 IF (x_msg_data IS NOT NULL) THEN
4652 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Is_Leaf_Analysis_Option ';
4653 ELSE
4654 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Is_Leaf_Analysis_Option ';
4655 END IF;
4656 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
4657 WHEN OTHERS THEN
4658 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4659 IF (x_msg_data IS NOT NULL) THEN
4660 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Is_Leaf_Analysis_Option ';
4661 ELSE
4662 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Is_Leaf_Analysis_Option ';
4663 END IF;
4664 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
4665
4666 end Is_Leaf_Analysis_Option;
4667
4668 /************************************************************************************
4669 ************************************************************************************/
4670
4671 function get_KPI_Time_Stamp(
4672 p_kpi_id IN number
4673 ) return date is
4674
4675 l_time_stamp date;
4676 begin
4677
4678 select last_update_date
4679 into l_time_stamp
4680 from bsc_kpis_b
4681 where indicator = p_kpi_id;
4682 return l_time_stamp;
4683
4684 EXCEPTION
4685 WHEN OTHERS THEN
4686 return null;
4687
4688 end get_KPI_Time_Stamp;
4689
4690 /************************************************************************************
4691 ************************************************************************************/
4692
4693 /*
4694 This procedure get a Master KPI
4695 Change the Master KPI to the Tab where the only Share KPI belong
4696 (It care that the object already created with the share KPI in the respective
4697 tab don't loose; like cause-Effect and KPI Custom Viwe Objects)
4698 Then Deleted the Share KPI
4699
4700 */
4701 procedure move_master_kpi(
4702 p_master_kpi IN NUMBER
4703 ,x_return_status OUT NOCOPY varchar2
4704 ,x_msg_count OUT NOCOPY number
4705 ,x_msg_data OUT NOCOPY varchar2
4706 ) is
4707
4708 l_master_kpi NUMBER;
4709 l_share_kpi NUMBER;
4710 l_master_tab NUMBER;
4711 l_share_tab NUMBER;
4712 l_commit varchar2(10);
4713 l_Bsc_Kpi_Entity_Rec BSC_KPI_PUB.Bsc_Kpi_Entity_Rec;
4714 l_Bsc_Tab_Entity_Rec BSC_SCORECARD_PUB.Bsc_Tab_Entity_Rec;
4715
4716 cursor c is select *
4717 from BSC_TABS_B
4718 where TAB_ID = l_share_tab
4719 for update of TAB_ID nowait;
4720 recinfo c%rowtype;
4721
4722 CURSOR C_SHARE_DIM_LEVELS IS
4723 SELECT dim_set_id,dim_level_index,level_table_name,level_view_name
4724 FROM bsc_kpi_dim_levels_b
4725 WHERE indicator = l_share_kpi;
4726
4727 begin
4728 FND_MSG_PUB.Initialize;
4729 x_return_status := FND_API.G_RET_STS_SUCCESS;
4730 l_commit := FND_API.G_TRUE;
4731 l_master_kpi := p_master_kpi;
4732
4733 -- Get Share KPI code and Share Tab
4734 select indicator, tab_id
4735 into l_share_kpi, l_share_tab
4736 from BSC_TAB_INDICATORS
4737 where indicator in (select indicator
4738 from BSC_KPIS_B
4739 where source_indicator = l_master_kpi );
4740 -- Get Master Tab
4741 select tab_id
4742 into l_master_tab
4743 from BSC_TAB_INDICATORS
4744 where indicator = l_master_kpi;
4745
4746 -- handle Metadata object
4747 UPDATE BSC_TAB_INDICATORS SET TAB_ID = l_share_tab
4748 WHERE INDICATOR= l_master_kpi;
4749 UPDATE BSC_KPI_DEFAULTS_B SET TAB_ID = l_share_tab
4750 WHERE INDICATOR = l_master_kpi;
4751 UPDATE BSC_KPI_DEFAULTS_TL SET TAB_ID = l_share_tab
4752 WHERE INDICATOR= l_master_kpi;
4753
4754 DELETE FROM BSC_TAB_VIEW_KPI_TL
4755 WHERE TAB_ID = l_master_tab AND INDICATOR = l_master_kpi;
4756
4757 DELETE FROM BSC_KPI_CAUSE_EFFECT_RELS
4758 WHERE (((CAUSE_INDICATOR= l_master_kpi)) OR ((EFFECT_INDICATOR = l_master_kpi))
4759 AND (NVL(CAUSE_LEVEL,'KPI')= 'KPI') AND (NVL(EFFECT_LEVEL,'NVL')= 'KPI'));
4760
4761 UPDATE BSC_TAB_VIEW_KPI_TL
4762 SET INDICATOR = l_master_kpi, LAST_UPDATE_DATE = sysdate
4763 WHERE TAB_ID= l_share_tab AND INDICATOR= l_share_kpi;
4764
4765 UPDATE BSC_KPI_CAUSE_EFFECT_RELS
4766 SET EFFECT_INDICATOR = l_master_kpi
4767 WHERE EFFECT_INDICATOR = l_share_kpi
4768 AND (NVL(CAUSE_LEVEL,'KPI')= 'KPI') AND (NVL(EFFECT_LEVEL,'NVL')= 'KPI');
4769 UPDATE BSC_KPI_CAUSE_EFFECT_RELS SET CAUSE_INDICATOR = l_master_kpi
4770 WHERE CAUSE_INDICATOR = l_share_kpi
4771 AND (NVL(CAUSE_LEVEL,'KPI')= 'KPI') AND (NVL(EFFECT_LEVEL,'NVL')= 'KPI');
4772
4773 /*********************************************
4774 Shared objectives can have different filter views as compared
4775 to master objective.So we need to copy the filter views to master objective
4776 before deleting the shared objective
4777 /***********************************************/
4778 FOR cd IN C_SHARE_DIM_LEVELS LOOP
4779
4780 UPDATE bsc_kpi_dim_levels_b
4781 SET level_view_name = cd.level_view_name
4782 WHERE indicator = l_master_kpi
4783 AND dim_set_id = cd.dim_set_id
4784 AND dim_level_index = cd.dim_level_index
4785 AND level_table_name = cd.level_table_name;
4786
4787 END LOOP;
4788
4789 -- Delete the Share KPI
4790 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := l_share_kpi;
4791 l_Bsc_Kpi_Entity_Rec.Bsc_Tab_Id := l_share_tab;
4792 BSC_KPI_PUB.Delete_Kpi( l_commit
4793 ,l_Bsc_Kpi_Entity_Rec
4794 ,x_return_status
4795 ,x_msg_count
4796 ,x_msg_data);
4797 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4798 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4799 END IF;
4800 BSC_KPI_PUB.Update_Kpi_Time_Stamp( l_commit
4801 ,l_Bsc_Kpi_Entity_Rec
4802 ,x_return_status
4803 ,x_msg_count
4804 ,x_msg_data);
4805 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4807 END IF;
4808 l_Bsc_Kpi_Entity_Rec.Bsc_Kpi_Id := l_master_kpi;
4809 l_Bsc_Kpi_Entity_Rec.Bsc_Tab_Id := l_master_tab;
4810 BSC_KPI_PUB.Update_Kpi_Time_Stamp( l_commit
4811 ,l_Bsc_Kpi_Entity_Rec
4812 ,x_return_status
4813 ,x_msg_count
4814 ,x_msg_data);
4815 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4816 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4817 END IF;
4818 l_Bsc_Tab_Entity_Rec.Bsc_Tab_Id := l_share_tab;
4819
4820 open c;
4821 fetch c into recinfo;
4822 if (c%found) then
4823 BSC_SCORECARD_PUB.Update_Tab_Time_Stamp( l_commit
4824 ,l_Bsc_Tab_Entity_Rec
4825 ,x_return_status
4826 ,x_msg_count
4827 ,x_msg_data);
4828 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4829 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4830 END IF;
4831 end if;
4832 close c;
4833
4834 end move_master_kpi;
4835
4836
4837 /***************************************************************
4838 Name : Delete_Ind_Extra_Tables
4839 Description : This API is used to delete the data of objectives from
4840 other tables which was not happening till now from PMD.
4841 Created by : ashankar 21-JUL-2005
4842 /***************************************************************/
4843
4844 PROCEDURE Delete_Ind_Extra_Tables
4845 (
4846 p_commit IN VARCHAR2 := FND_API.G_FALSE
4847 , p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.BSC_KPI_ENTITY_REC
4848 , x_return_status OUT NOCOPY VARCHAR2
4849 , x_msg_count OUT NOCOPY NUMBER
4850 , x_msg_data OUT NOCOPY VARCHAR2
4851
4852 )IS
4853 BEGIN
4854 FND_MSG_PUB.Initialize;
4855 x_return_status := FND_API.G_RET_STS_SUCCESS;
4856
4857 BSC_KPI_PUB.Delete_Ind_User_Access
4858 (
4859 p_commit => p_commit
4860 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
4861 , x_return_status => x_return_status
4862 , x_msg_count => x_msg_count
4863 , x_msg_data => x_msg_data
4864 );
4865 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4866 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4867 END IF;
4868
4869 BSC_KPI_PUB.Delete_Ind_Tree_Nodes
4870 (
4871 p_commit => p_commit
4872 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
4873 , x_return_status => x_return_status
4874 , x_msg_count => x_msg_count
4875 , x_msg_data => x_msg_data
4876 );
4877 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4878 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4879 END IF;
4880
4881 BSC_KPI_PUB.Delete_Ind_Comments
4882 (
4883 p_commit => p_commit
4884 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
4885 , x_return_status => x_return_status
4886 , x_msg_count => x_msg_count
4887 , x_msg_data => x_msg_data
4888 );
4889 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4890 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4891 END IF;
4892
4893 BSC_KPI_PUB.Delete_Ind_Sys_Prop
4894 (
4895 p_commit => p_commit
4896 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
4897 , x_return_status => x_return_status
4898 , x_msg_count => x_msg_count
4899 , x_msg_data => x_msg_data
4900 );
4901 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4902 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4903 END IF;
4904
4905 BSC_KPI_PUB.Delete_Ind_Images
4906 (
4907 p_commit => p_commit
4908 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
4909 , x_return_status => x_return_status
4910 , x_msg_count => x_msg_count
4911 , x_msg_data => x_msg_data
4912 );
4913 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4914 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4915 END IF;
4916
4917 BSC_KPI_PUB.Delete_Ind_SeriesColors
4918 (
4919 p_commit => p_commit
4920 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
4921 , x_return_status => x_return_status
4922 , x_msg_count => x_msg_count
4923 , x_msg_data => x_msg_data
4924 );
4925 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4926 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4927 END IF;
4928
4929 BSC_KPI_PUB.Delete_Ind_Subtitles
4930 (
4931 p_commit => p_commit
4932 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
4933 , x_return_status => x_return_status
4934 , x_msg_count => x_msg_count
4935 , x_msg_data => x_msg_data
4936 );
4937 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4938 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4939 END IF;
4940
4941 BSC_KPI_PUB.Delete_Ind_MM_Controls
4942 (
4943 p_commit => p_commit
4944 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
4945 , x_return_status => x_return_status
4946 , x_msg_count => x_msg_count
4947 , x_msg_data => x_msg_data
4948 );
4949 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4950 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4951 END IF;
4952
4953 BSC_KPI_PUB.Delete_Ind_Shell_Cmds
4954 (
4955 p_commit => p_commit
4956 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
4957 , x_return_status => x_return_status
4958 , x_msg_count => x_msg_count
4959 , x_msg_data => x_msg_data
4960 );
4961 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4962 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4963 END IF;
4964
4965 BSC_KPI_PUB.Delete_Ind_Cause_Effect_Rels
4966 (
4967 p_commit => p_commit
4968 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
4969 , x_return_status => x_return_status
4970 , x_msg_count => x_msg_count
4971 , x_msg_data => x_msg_data
4972 );
4973 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4974 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4975 END IF;
4976
4977 EXCEPTION
4978 WHEN FND_API.G_EXC_ERROR THEN
4979 IF (x_msg_data IS NULL) THEN
4980 FND_MSG_PUB.Count_And_Get
4981 ( p_encoded => FND_API.G_FALSE
4982 , p_count => x_msg_count
4983 , p_data => x_msg_data
4984 );
4985 END IF;
4986 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
4987 x_return_status := FND_API.G_RET_STS_ERROR;
4988 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4989 IF (x_msg_data IS NULL) THEN
4990 FND_MSG_PUB.Count_And_Get
4991 ( p_encoded => FND_API.G_FALSE
4992 , p_count => x_msg_count
4993 , p_data => x_msg_data
4994 );
4995 END IF;
4996 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4997 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
4998 WHEN NO_DATA_FOUND THEN
4999 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5000 IF (x_msg_data IS NOT NULL) THEN
5001 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_Extra_Tables ';
5002 ELSE
5003 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_Extra_Tables ';
5004 END IF;
5005 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
5006 WHEN OTHERS THEN
5007 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5008 IF (x_msg_data IS NOT NULL) THEN
5009 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_Extra_Tables ';
5010 ELSE
5011 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_Extra_Tables ';
5012 END IF;
5013 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
5014 END Delete_Ind_Extra_Tables;
5015
5016 /*********************************************************
5017 Name : Delete_Ind_Cause_Effect_Rels
5018 Description : This API deletes CAUSE and EFFECT of objectives
5019 created by : ashankar 21-JUL-2005
5020 /********************************************************/
5021
5022 PROCEDURE Delete_Ind_Cause_Effect_Rels
5023 (
5024 p_commit IN VARCHAR2 := FND_API.G_FALSE
5025 , p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
5026 , x_return_status OUT NOCOPY VARCHAR2
5027 , x_msg_count OUT NOCOPY NUMBER
5028 , x_msg_data OUT NOCOPY VARCHAR2
5029 ) IS
5030 l_count NUMBER;
5031
5032 BEGIN
5033 FND_MSG_PUB.Initialize;
5034 x_return_status := FND_API.G_RET_STS_SUCCESS;
5035
5036 BSC_KPI_PVT.Delete_Ind_Cause_Effect_Rels
5037 (
5038 p_commit => p_commit
5039 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
5040 , x_return_status => x_return_status
5041 , x_msg_count => x_msg_count
5042 , x_msg_data => x_msg_data
5043 );
5044
5045 EXCEPTION
5046 WHEN FND_API.G_EXC_ERROR THEN
5047 IF (x_msg_data IS NULL) THEN
5048 FND_MSG_PUB.Count_And_Get
5049 ( p_encoded => FND_API.G_FALSE
5050 , p_count => x_msg_count
5051 , p_data => x_msg_data
5052 );
5053 END IF;
5054 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
5055 x_return_status := FND_API.G_RET_STS_ERROR;
5056 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5057 IF (x_msg_data IS NULL) THEN
5058 FND_MSG_PUB.Count_And_Get
5059 ( p_encoded => FND_API.G_FALSE
5060 , p_count => x_msg_count
5061 , p_data => x_msg_data
5062 );
5063 END IF;
5064 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5065 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
5066 WHEN NO_DATA_FOUND THEN
5067 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5068 IF (x_msg_data IS NOT NULL) THEN
5069 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_Cause_Effect_Rels ';
5070 ELSE
5071 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_Cause_Effect_Rels ';
5072 END IF;
5073 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
5074 WHEN OTHERS THEN
5075 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5076 IF (x_msg_data IS NOT NULL) THEN
5077 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_Cause_Effect_Rels ';
5078 ELSE
5079 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_Cause_Effect_Rels ';
5080 END IF;
5081 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
5082 END Delete_Ind_Cause_Effect_Rels;
5083
5084 /*********************************************************
5085 Name : Delete_Ind_Shell_Cmds
5086 Description : This API deletes shell commands attached to the objective
5087 created by : ashankar 21-JUL-2005
5088 /********************************************************/
5089
5090 PROCEDURE Delete_Ind_Shell_Cmds
5091 (
5092 p_commit IN VARCHAR2 := FND_API.G_FALSE
5093 , p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
5094 , x_return_status OUT NOCOPY VARCHAR2
5095 , x_msg_count OUT NOCOPY NUMBER
5096 , x_msg_data OUT NOCOPY VARCHAR2
5097 ) IS
5098 l_count NUMBER;
5099
5100 BEGIN
5101 FND_MSG_PUB.Initialize;
5102 x_return_status := FND_API.G_RET_STS_SUCCESS;
5103
5104 BSC_KPI_PVT.Delete_Ind_Shell_Cmds
5105 (
5106 p_commit => p_commit
5107 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
5108 , x_return_status => x_return_status
5109 , x_msg_count => x_msg_count
5110 , x_msg_data => x_msg_data
5111 );
5112
5113 EXCEPTION
5114 WHEN FND_API.G_EXC_ERROR THEN
5115 IF (x_msg_data IS NULL) THEN
5116 FND_MSG_PUB.Count_And_Get
5117 ( p_encoded => FND_API.G_FALSE
5118 , p_count => x_msg_count
5119 , p_data => x_msg_data
5120 );
5121 END IF;
5122 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
5123 x_return_status := FND_API.G_RET_STS_ERROR;
5124 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5125 IF (x_msg_data IS NULL) THEN
5126 FND_MSG_PUB.Count_And_Get
5127 ( p_encoded => FND_API.G_FALSE
5128 , p_count => x_msg_count
5129 , p_data => x_msg_data
5130 );
5131 END IF;
5132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5133 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
5134 WHEN NO_DATA_FOUND THEN
5135 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5136 IF (x_msg_data IS NOT NULL) THEN
5137 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_Shell_Cmds ';
5138 ELSE
5139 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_Shell_Cmds ';
5140 END IF;
5141 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
5142 WHEN OTHERS THEN
5143 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5144 IF (x_msg_data IS NOT NULL) THEN
5145 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_Shell_Cmds ';
5146 ELSE
5147 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_Shell_Cmds ';
5148 END IF;
5149 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
5150 END Delete_Ind_Shell_Cmds;
5151
5152 /*********************************************************
5153 Name : Delete_Ind_MM_Controls
5154 Description : This API deletes Multimedia controls attached to the objective
5155 created by : ashankar 21-JUL-2005
5156 /********************************************************/
5157
5158 PROCEDURE Delete_Ind_MM_Controls
5159 (
5160 p_commit IN VARCHAR2 := FND_API.G_FALSE
5161 , p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
5162 , x_return_status OUT NOCOPY VARCHAR2
5163 , x_msg_count OUT NOCOPY NUMBER
5164 , x_msg_data OUT NOCOPY VARCHAR2
5165 ) IS
5166 l_count NUMBER;
5167
5168 BEGIN
5169 FND_MSG_PUB.Initialize;
5170 x_return_status := FND_API.G_RET_STS_SUCCESS;
5171
5172 BSC_KPI_PVT.Delete_Ind_MM_Controls
5173 (
5174 p_commit => p_commit
5175 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
5176 , x_return_status => x_return_status
5177 , x_msg_count => x_msg_count
5178 , x_msg_data => x_msg_data
5179 );
5180
5181 EXCEPTION
5182 WHEN FND_API.G_EXC_ERROR THEN
5183 IF (x_msg_data IS NULL) THEN
5184 FND_MSG_PUB.Count_And_Get
5185 ( p_encoded => FND_API.G_FALSE
5186 , p_count => x_msg_count
5187 , p_data => x_msg_data
5188 );
5189 END IF;
5190 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
5191 x_return_status := FND_API.G_RET_STS_ERROR;
5192 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5193 IF (x_msg_data IS NULL) THEN
5194 FND_MSG_PUB.Count_And_Get
5195 ( p_encoded => FND_API.G_FALSE
5196 , p_count => x_msg_count
5197 , p_data => x_msg_data
5198 );
5199 END IF;
5200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5201 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
5202 WHEN NO_DATA_FOUND THEN
5203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5204 IF (x_msg_data IS NOT NULL) THEN
5205 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_MM_Controls ';
5206 ELSE
5207 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_MM_Controls ';
5208 END IF;
5209 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
5210 WHEN OTHERS THEN
5211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5212 IF (x_msg_data IS NOT NULL) THEN
5213 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_MM_Controls ';
5214 ELSE
5215 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_MM_Controls ';
5216 END IF;
5217 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
5218 END Delete_Ind_MM_Controls;
5219
5220 /*********************************************************
5221 Name : Delete_Ind_Subtitles
5222 Description : This API deletes subtitles attached to the objectives
5223 created by : ashankar 21-JUL-2005
5224 /********************************************************/
5225 PROCEDURE Delete_Ind_Subtitles
5226 (
5227 p_commit IN VARCHAR2 := FND_API.G_FALSE
5228 , p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
5229 , x_return_status OUT NOCOPY VARCHAR2
5230 , x_msg_count OUT NOCOPY NUMBER
5231 , x_msg_data OUT NOCOPY VARCHAR2
5232 ) IS
5233 l_count NUMBER;
5234
5235 BEGIN
5236 FND_MSG_PUB.Initialize;
5237 x_return_status := FND_API.G_RET_STS_SUCCESS;
5238
5239 BSC_KPI_PVT.Delete_Ind_Subtitles
5240 (
5241 p_commit => p_commit
5242 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
5243 , x_return_status => x_return_status
5244 , x_msg_count => x_msg_count
5245 , x_msg_data => x_msg_data
5246 );
5247
5248 EXCEPTION
5249 WHEN FND_API.G_EXC_ERROR THEN
5250 IF (x_msg_data IS NULL) THEN
5251 FND_MSG_PUB.Count_And_Get
5252 ( p_encoded => FND_API.G_FALSE
5253 , p_count => x_msg_count
5254 , p_data => x_msg_data
5255 );
5256 END IF;
5257 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
5258 x_return_status := FND_API.G_RET_STS_ERROR;
5259 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5260 IF (x_msg_data IS NULL) THEN
5261 FND_MSG_PUB.Count_And_Get
5262 ( p_encoded => FND_API.G_FALSE
5263 , p_count => x_msg_count
5264 , p_data => x_msg_data
5265 );
5266 END IF;
5267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5268 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
5269 WHEN NO_DATA_FOUND THEN
5270 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5271 IF (x_msg_data IS NOT NULL) THEN
5272 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_Subtitles ';
5273 ELSE
5274 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_Subtitles ';
5275 END IF;
5276 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
5277 WHEN OTHERS THEN
5278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5279 IF (x_msg_data IS NOT NULL) THEN
5280 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_Subtitles ';
5281 ELSE
5282 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_Subtitles ';
5283 END IF;
5284 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
5285 END Delete_Ind_Subtitles;
5286
5287 /*********************************************************
5288 Name : Delete_Ind_SeriesColors
5289 Description : This API deletes series colors attached to the objectives
5290 created by : ashankar 21-JUL-2005
5291 /********************************************************/
5292 PROCEDURE Delete_Ind_SeriesColors
5293 (
5294 p_commit IN VARCHAR2 := FND_API.G_FALSE
5295 , p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
5296 , x_return_status OUT NOCOPY VARCHAR2
5297 , x_msg_count OUT NOCOPY NUMBER
5298 , x_msg_data OUT NOCOPY VARCHAR2
5299 ) IS
5300 l_count NUMBER;
5301
5302 BEGIN
5303 FND_MSG_PUB.Initialize;
5304 x_return_status := FND_API.G_RET_STS_SUCCESS;
5305
5306 BSC_KPI_PVT.Delete_Ind_SeriesColors
5307 (
5308 p_commit => p_commit
5309 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
5310 , x_return_status => x_return_status
5311 , x_msg_count => x_msg_count
5312 , x_msg_data => x_msg_data
5313 );
5314
5315 EXCEPTION
5316 WHEN FND_API.G_EXC_ERROR THEN
5317 IF (x_msg_data IS NULL) THEN
5318 FND_MSG_PUB.Count_And_Get
5319 ( p_encoded => FND_API.G_FALSE
5320 , p_count => x_msg_count
5321 , p_data => x_msg_data
5322 );
5323 END IF;
5324 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
5325 x_return_status := FND_API.G_RET_STS_ERROR;
5326 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5327 IF (x_msg_data IS NULL) THEN
5328 FND_MSG_PUB.Count_And_Get
5329 ( p_encoded => FND_API.G_FALSE
5330 , p_count => x_msg_count
5331 , p_data => x_msg_data
5332 );
5333 END IF;
5334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5335 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
5336 WHEN NO_DATA_FOUND THEN
5337 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5338 IF (x_msg_data IS NOT NULL) THEN
5339 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_SeriesColors ';
5340 ELSE
5341 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_SeriesColors ';
5342 END IF;
5343 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
5344 WHEN OTHERS THEN
5345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5346 IF (x_msg_data IS NOT NULL) THEN
5347 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_SeriesColors ';
5348 ELSE
5349 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_SeriesColors ';
5350 END IF;
5351 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
5352 END Delete_Ind_SeriesColors;
5353
5354 /*********************************************************
5355 Name : Delete_Ind_Images
5356 Description : This API deletes objective images
5357 created by : ashankar 21-JUL-2005
5358 /********************************************************/
5359
5360 PROCEDURE Delete_Ind_Images
5361 (
5362 p_commit IN VARCHAR2 := FND_API.G_FALSE
5363 , p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
5364 , x_return_status OUT NOCOPY VARCHAR2
5365 , x_msg_count OUT NOCOPY NUMBER
5366 , x_msg_data OUT NOCOPY VARCHAR2
5367 ) IS
5368 l_count NUMBER;
5369
5370 BEGIN
5371 FND_MSG_PUB.Initialize;
5372 x_return_status := FND_API.G_RET_STS_SUCCESS;
5373
5374 BSC_KPI_PVT.Delete_Ind_Images
5375 (
5376 p_commit => p_commit
5377 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
5378 , x_return_status => x_return_status
5379 , x_msg_count => x_msg_count
5380 , x_msg_data => x_msg_data
5381 );
5382
5383 EXCEPTION
5384 WHEN FND_API.G_EXC_ERROR THEN
5385 IF (x_msg_data IS NULL) THEN
5386 FND_MSG_PUB.Count_And_Get
5387 ( p_encoded => FND_API.G_FALSE
5388 , p_count => x_msg_count
5389 , p_data => x_msg_data
5390 );
5391 END IF;
5392 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
5393 x_return_status := FND_API.G_RET_STS_ERROR;
5394 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5395 IF (x_msg_data IS NULL) THEN
5396 FND_MSG_PUB.Count_And_Get
5397 ( p_encoded => FND_API.G_FALSE
5398 , p_count => x_msg_count
5399 , p_data => x_msg_data
5400 );
5401 END IF;
5402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5403 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
5404 WHEN NO_DATA_FOUND THEN
5405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5406 IF (x_msg_data IS NOT NULL) THEN
5407 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_Images ';
5408 ELSE
5409 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_Images ';
5410 END IF;
5411 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
5412 WHEN OTHERS THEN
5413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5414 IF (x_msg_data IS NOT NULL) THEN
5415 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_Images ';
5416 ELSE
5417 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_Images ';
5418 END IF;
5419 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
5420 END Delete_Ind_Images;
5421
5422 /*********************************************************
5423 Name : Delete_Ind_Sys_Prop
5424 Description : This API deletes system properties of objectives
5425 created by : ashankar 21-JUL-2005
5426 /********************************************************/
5427 PROCEDURE Delete_Ind_Sys_Prop
5428 (
5429 p_commit IN VARCHAR2 := FND_API.G_FALSE
5430 , p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
5431 , x_return_status OUT NOCOPY VARCHAR2
5432 , x_msg_count OUT NOCOPY NUMBER
5433 , x_msg_data OUT NOCOPY VARCHAR2
5434 ) IS
5435 l_count NUMBER;
5436
5437 BEGIN
5438 FND_MSG_PUB.Initialize;
5439 x_return_status := FND_API.G_RET_STS_SUCCESS;
5440
5441 BSC_KPI_PVT.Delete_Ind_Sys_Prop
5442 (
5443 p_commit => p_commit
5444 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
5445 , x_return_status => x_return_status
5446 , x_msg_count => x_msg_count
5447 , x_msg_data => x_msg_data
5448 );
5449
5450 EXCEPTION
5451 WHEN FND_API.G_EXC_ERROR THEN
5452 IF (x_msg_data IS NULL) THEN
5453 FND_MSG_PUB.Count_And_Get
5454 ( p_encoded => FND_API.G_FALSE
5455 , p_count => x_msg_count
5456 , p_data => x_msg_data
5457 );
5458 END IF;
5459 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
5460 x_return_status := FND_API.G_RET_STS_ERROR;
5461 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5462 IF (x_msg_data IS NULL) THEN
5463 FND_MSG_PUB.Count_And_Get
5464 ( p_encoded => FND_API.G_FALSE
5465 , p_count => x_msg_count
5466 , p_data => x_msg_data
5467 );
5468 END IF;
5469 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5470 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
5471 WHEN NO_DATA_FOUND THEN
5472 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5473 IF (x_msg_data IS NOT NULL) THEN
5474 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_Sys_Prop ';
5475 ELSE
5476 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_Sys_Prop ';
5477 END IF;
5478 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
5479 WHEN OTHERS THEN
5480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5481 IF (x_msg_data IS NOT NULL) THEN
5482 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_Sys_Prop ';
5483 ELSE
5484 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_Sys_Prop ';
5485 END IF;
5486 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
5487 END Delete_Ind_Sys_Prop;
5488
5489 /*********************************************************
5490 Name : Delete_Ind_Comments
5491 Description : This API deletes comments of objectives
5492 created by : ashankar 21-JUL-2005
5493 /********************************************************/
5494
5495 PROCEDURE Delete_Ind_Comments
5496 (
5497 p_commit IN VARCHAR2 := FND_API.G_FALSE
5498 , p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
5499 , x_return_status OUT NOCOPY VARCHAR2
5500 , x_msg_count OUT NOCOPY NUMBER
5501 , x_msg_data OUT NOCOPY VARCHAR2
5502 ) IS
5503 l_count NUMBER;
5504
5505 BEGIN
5506 FND_MSG_PUB.Initialize;
5507 x_return_status := FND_API.G_RET_STS_SUCCESS;
5508
5509 BSC_KPI_PVT.Delete_Ind_Comments
5510 (
5511 p_commit => p_commit
5512 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
5513 , x_return_status => x_return_status
5514 , x_msg_count => x_msg_count
5515 , x_msg_data => x_msg_data
5516 );
5517
5518 EXCEPTION
5519 WHEN FND_API.G_EXC_ERROR THEN
5520 IF (x_msg_data IS NULL) THEN
5521 FND_MSG_PUB.Count_And_Get
5522 ( p_encoded => FND_API.G_FALSE
5523 , p_count => x_msg_count
5524 , p_data => x_msg_data
5525 );
5526 END IF;
5527 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
5528 x_return_status := FND_API.G_RET_STS_ERROR;
5529 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5530 IF (x_msg_data IS NULL) THEN
5531 FND_MSG_PUB.Count_And_Get
5532 ( p_encoded => FND_API.G_FALSE
5533 , p_count => x_msg_count
5534 , p_data => x_msg_data
5535 );
5536 END IF;
5537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5538 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
5539 WHEN NO_DATA_FOUND THEN
5540 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5541 IF (x_msg_data IS NOT NULL) THEN
5542 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_Comments ';
5543 ELSE
5544 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_Comments ';
5545 END IF;
5546 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
5547 WHEN OTHERS THEN
5548 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5549 IF (x_msg_data IS NOT NULL) THEN
5550 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_Comments ';
5551 ELSE
5552 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_Comments ';
5553 END IF;
5554 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
5555 END Delete_Ind_Comments;
5556
5557 /*********************************************************
5558 Name : Delete_Ind_Tree_Nodes
5559 Description : This API deletes Tree nodes of the objectives
5560 created by : ashankar 21-JUL-2005
5561 /********************************************************/
5562
5563 PROCEDURE Delete_Ind_Tree_Nodes
5564 (
5565 p_commit IN VARCHAR2 := FND_API.G_FALSE
5566 , p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
5567 , x_return_status OUT NOCOPY VARCHAR2
5568 , x_msg_count OUT NOCOPY NUMBER
5569 , x_msg_data OUT NOCOPY VARCHAR2
5570 ) IS
5571 l_count NUMBER;
5572
5573 BEGIN
5574 FND_MSG_PUB.Initialize;
5575 x_return_status := FND_API.G_RET_STS_SUCCESS;
5576
5577 BSC_KPI_PVT.Delete_Ind_Tree_Nodes
5578 (
5579 p_commit => p_commit
5580 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
5581 , x_return_status => x_return_status
5582 , x_msg_count => x_msg_count
5583 , x_msg_data => x_msg_data
5584 );
5585
5586 EXCEPTION
5587 WHEN FND_API.G_EXC_ERROR THEN
5588 IF (x_msg_data IS NULL) THEN
5589 FND_MSG_PUB.Count_And_Get
5590 ( p_encoded => FND_API.G_FALSE
5591 , p_count => x_msg_count
5592 , p_data => x_msg_data
5593 );
5594 END IF;
5595 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
5596 x_return_status := FND_API.G_RET_STS_ERROR;
5597 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5598 IF (x_msg_data IS NULL) THEN
5599 FND_MSG_PUB.Count_And_Get
5600 ( p_encoded => FND_API.G_FALSE
5601 , p_count => x_msg_count
5602 , p_data => x_msg_data
5603 );
5604 END IF;
5605 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5606 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
5607 WHEN NO_DATA_FOUND THEN
5608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5609 IF (x_msg_data IS NOT NULL) THEN
5610 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_Tree_Nodes ';
5611 ELSE
5612 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_Tree_Nodes ';
5613 END IF;
5614 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
5615 WHEN OTHERS THEN
5616 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5617 IF (x_msg_data IS NOT NULL) THEN
5618 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_Tree_Nodes ';
5619 ELSE
5620 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_Tree_Nodes ';
5621 END IF;
5622 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
5623 END Delete_Ind_Tree_Nodes;
5624
5625 /*********************************************************
5626 Name : Delete_Ind_User_Access
5627 Description : This API deletes user access of the objectives
5628 created by : ashankar 21-JUL-2005
5629 /********************************************************/
5630
5631 PROCEDURE Delete_Ind_User_Access
5632 (
5633 p_commit IN VARCHAR2 := FND_API.G_FALSE
5634 , p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
5635 , x_return_status OUT NOCOPY VARCHAR2
5636 , x_msg_count OUT NOCOPY NUMBER
5637 , x_msg_data OUT NOCOPY VARCHAR2
5638 ) IS
5639 l_count NUMBER;
5640
5641 BEGIN
5642 FND_MSG_PUB.Initialize;
5643 x_return_status := FND_API.G_RET_STS_SUCCESS;
5644
5645 BSC_KPI_PVT.Delete_Ind_User_Access
5646 (
5647 p_commit => p_commit
5648 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
5649 , x_return_status => x_return_status
5650 , x_msg_count => x_msg_count
5651 , x_msg_data => x_msg_data
5652 );
5653
5654 EXCEPTION
5655 WHEN FND_API.G_EXC_ERROR THEN
5656 IF (x_msg_data IS NULL) THEN
5657 FND_MSG_PUB.Count_And_Get
5658 ( p_encoded => FND_API.G_FALSE
5659 , p_count => x_msg_count
5660 , p_data => x_msg_data
5661 );
5662 END IF;
5663 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
5664 x_return_status := FND_API.G_RET_STS_ERROR;
5665 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5666 IF (x_msg_data IS NULL) THEN
5667 FND_MSG_PUB.Count_And_Get
5668 ( p_encoded => FND_API.G_FALSE
5669 , p_count => x_msg_count
5670 , p_data => x_msg_data
5671 );
5672 END IF;
5673 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5674 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
5675 WHEN NO_DATA_FOUND THEN
5676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5677 IF (x_msg_data IS NOT NULL) THEN
5678 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_User_Access ';
5679 ELSE
5680 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_User_Access ';
5681 END IF;
5682 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
5683 WHEN OTHERS THEN
5684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5685 IF (x_msg_data IS NOT NULL) THEN
5686 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Ind_User_Access ';
5687 ELSE
5688 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Ind_User_Access ';
5689 END IF;
5690 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
5691 END Delete_Ind_User_Access;
5692
5693
5694 PROCEDURE Delete_Sim_Tree_Data
5695 (
5696 p_commit IN VARCHAR2 := FND_API.G_FALSE
5697 , p_Bsc_Kpi_Entity_Rec IN BSC_KPI_PUB.Bsc_Kpi_Entity_Rec
5698 , x_return_status OUT NOCOPY VARCHAR2
5699 , x_msg_count OUT NOCOPY NUMBER
5700 , x_msg_data OUT NOCOPY VARCHAR2
5701 )IS
5702
5703 BEGIN
5704 FND_MSG_PUB.Initialize;
5705 x_return_status := FND_API.G_RET_STS_SUCCESS;
5706
5707
5708
5709 BSC_KPI_PVT.Delete_Sim_Tree_Data
5710 (
5711 p_commit => p_commit
5712 , p_Bsc_Kpi_Entity_Rec => p_Bsc_Kpi_Entity_Rec
5713 , x_return_status => x_return_status
5714 , x_msg_count => x_msg_count
5715 , x_msg_data => x_msg_data
5716 );
5717
5718
5719 EXCEPTION
5720 WHEN FND_API.G_EXC_ERROR THEN
5721 IF (x_msg_data IS NULL) THEN
5722 FND_MSG_PUB.Count_And_Get
5723 ( p_encoded => FND_API.G_FALSE
5724 , p_count => x_msg_count
5725 , p_data => x_msg_data
5726 );
5727 END IF;
5728 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_ERROR '||x_msg_data);
5729 x_return_status := FND_API.G_RET_STS_ERROR;
5730 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5731 IF (x_msg_data IS NULL) THEN
5732 FND_MSG_PUB.Count_And_Get
5733 ( p_encoded => FND_API.G_FALSE
5734 , p_count => x_msg_count
5735 , p_data => x_msg_data
5736 );
5737 END IF;
5738 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5739 --DBMS_OUTPUT.PUT_LINE('EXCEPTION FND_API.G_EXC_UNEXPECTED_ERROR '||x_msg_data);
5740 WHEN NO_DATA_FOUND THEN
5741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5742 IF (x_msg_data IS NOT NULL) THEN
5743 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Sim_Tree_Data ';
5744 ELSE
5745 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Sim_Tree_Data ';
5746 END IF;
5747 --DBMS_OUTPUT.PUT_LINE('EXCEPTION NO_DATA_FOUND '||x_msg_data);
5748 WHEN OTHERS THEN
5749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5750 IF (x_msg_data IS NOT NULL) THEN
5751 x_msg_data := x_msg_data||' -> BSC_KPI_PUB.Delete_Sim_Tree_Data ';
5752 ELSE
5753 x_msg_data := SQLERRM||' at BSC_KPI_PUB.Delete_Sim_Tree_Data ';
5754 END IF;
5755 --DBMS_OUTPUT.PUT_LINE('EXCEPTION OTHERS '||x_msg_data);
5756
5757 END Delete_Sim_Tree_Data;
5758
5759 END BSC_KPI_PUB;