[Home] [Help]
PACKAGE BODY: APPS.BIS_MEASURE_SECURITY_PVT
Source
1 PACKAGE BODY BIS_MEASURE_SECURITY_PVT AS
2 /* $Header: BISVMSEB.pls 115.42 2003/12/01 14:05:26 gramasam ship $ */
3 --
4 /*
5 REM +=======================================================================+
6 REM | Copyright (c) 1998 Oracle Corporation, Redwood Shores, CA, USA |
7 REM | All rights reserved. |
8 REM +=======================================================================+
9 REM | FILENAME |
10 REM | BISVMSES.pls |
11 REM | |
12 REM | DESCRIPTION |
13 REM | Public API for creating and managing Performance Measurements
14 REM |
15 REM | NOTES |
16 REM | |
17 REM | HISTORY |
18 REM | 28-NOV-98 irchen Creation
19 REM | Sep-2000 JPRABHUD added a new procedure Get_Measure_Sec_Sorted
20 REM | 23-JAN-03 sugopal For having different local variables for IN and OUT |
21 REM | parameters (bug#2758428) |
22 REM | 25-NOV-03 gramasam Included a new procedure for deleting |
23 REM | responsibilities at target level |
24 REM +=======================================================================+
25 */
26 --
27 G_PKG_NAME CONSTANT VARCHAR2(30):='BIS_MEASURE_SECURITY_PVT';
28 --
29 -- creates one Measure_Security, with the dimensions sequenced in the order
30 -- they are passed in
31 Procedure Create_Measure_Security
32 ( p_api_version IN NUMBER
33 , p_commit IN VARCHAR2 := FND_API.G_FALSE
34 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
35 ,p_Measure_Security_Rec IN BIS_MEASURE_SECURITY_PUB.Measure_Security_Rec_Type
36 , p_owner IN VARCHAR2 := BIS_UTILITIES_PUB.G_CUSTOM_OWNER --2465354
37 , x_return_status OUT NOCOPY VARCHAR2
38 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
39 )
40 IS
41 l_user_id number;
42 l_login_id number;
43 l_id number;
44 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
45
46 --added this
47 DUPLICATE_DIMENSION_VALUE EXCEPTION;
48 PRAGMA EXCEPTION_INIT(DUPLICATE_DIMENSION_VALUE, -1);
49 BEGIN
50
51 x_return_status := FND_API.G_RET_STS_SUCCESS;
52 Validate_Measure_Security
53 ( p_api_version => p_api_version
54 , p_Measure_Security_Rec => p_Measure_Security_Rec
55 , x_return_status => x_return_status
56 , x_error_Tbl => x_error_Tbl
57 );
58
59 --added this check
60 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
61 RAISE FND_API.G_EXC_ERROR;
62 END IF;
63
64 --2465354
65 IF p_owner = BIS_UTILITIES_PUB.G_SEED_OWNER THEN
66 l_user_id := BIS_UTILITIES_PUB.G_SEED_USER_ID;
67 ELSE
68 l_user_id := fnd_global.USER_ID;
69 END IF;
70 --2465354
71
72 l_login_id := fnd_global.LOGIN_ID;
73 select bis_indicator_resps_s.NextVal into l_id from dual;
74
75 BEGIN
76 insert into bis_indicator_resps
77 (
78 INDICATOR_RESP_ID
79 , RESPONSIBILITY_ID
80 , TARGET_LEVEL_ID
81 , CREATION_DATE
82 , CREATED_BY
83 , LAST_UPDATE_DATE
84 , LAST_UPDATED_BY
85 , LAST_UPDATE_LOGIN
86 )
87 values
88 ( l_id
89 , p_Measure_Security_Rec.RESPONSIBILITY_ID
90 , p_Measure_Security_Rec.TARGET_LEVEL_ID
91 , SYSDATE
92 , l_user_id
93 , SYSDATE
94 , l_user_id
95 , l_login_id
96 );
97 EXCEPTION
98 WHEN OTHERS THEN
99 null;
100
101 END;
102
103 if (p_commit = FND_API.G_TRUE) then
104 commit;
105 end if;
106
107 --commented RAISE
108 EXCEPTION
109 --added this
110 WHEN DUPLICATE_DIMENSION_VALUE THEN
111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
112 l_error_tbl := x_error_tbl;
113 BIS_UTILITIES_PVT.Add_Error_Message
114 ( p_error_msg_name => 'BIS_TAR_LEVEL_UNIQUENESS_ERROR'
115 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
116 , p_error_proc_name => G_PKG_NAME||'.Create_Measure_Security'
117 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
118 , p_error_table => l_error_tbl
119 , x_error_table => x_error_tbl
120 );
121 WHEN NO_DATA_FOUND THEN
122 x_return_status := FND_API.G_RET_STS_ERROR ;
123 --RAISE FND_API.G_EXC_ERROR;
124 when FND_API.G_EXC_ERROR then
125 x_return_status := FND_API.G_RET_STS_ERROR ;
126 --RAISE FND_API.G_EXC_ERROR;
127 when FND_API.G_EXC_UNEXPECTED_ERROR then
128 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
129 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
130 when others then
131 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
132 --added last two params
133 l_error_tbl := x_error_tbl;
134 BIS_UTILITIES_PVT.Add_Error_Message
135 ( p_error_msg_id => SQLCODE
136 , p_error_description => SQLERRM
137 , p_error_proc_name => G_PKG_NAME||'.Create_Measure_Security'
138 , p_error_table => l_error_tbl
139 , x_error_table => x_error_tbl
140 );
141 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
142
143
144 END Create_Measure_Security;
145 --
146 --
147 -- Gets All Performance Measure_Securitys
148 -- If information about the dimensions are not required, set all_info to
149 -- FALSE
150 --
151 PROCEDURE Retrieve_Measure_Securities
152 ( p_api_version IN NUMBER
153 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
154 , x_Measure_Security_tbl
155 OUT NOCOPY BIS_MEASURE_SECURITY_PUB.Measure_Security_Tbl_Type
156 , x_return_status OUT NOCOPY VARCHAR2
157 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
158 )
159 IS
160 l_meas_rec BIS_MEASURE_SECURITY_PUB.Measure_Security_Rec_Type;
161 l_resp_rec BIS_Responsibility_PVT.Responsibility_rec_Type;
162 l_resp_rec_p BIS_Responsibility_PVT.Responsibility_rec_Type;
163
164 cursor sec_cursor is
165 select RESPONSIBILITY_ID
166 from bis_indicator_resps
167 where TARGET_LEVEL_ID=p_Target_Level_Rec.Target_Level_Id;
168
169 l_flag number := 0;
170 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
171 BEGIN
172
173 x_return_status := FND_API.G_RET_STS_SUCCESS;
174 -- htp.p('in Retrieve_Measure_Securities ');
175
176 --changed this call to the following call
177 /*
178 BIS_Target_Level_PVT.Validate_Target_Level
179 ( p_api_version => 1.0
180 , p_Target_Level_Rec => p_Target_Level_Rec
181 , x_return_status => x_return_status
182 , x_error_Tbl => x_error_Tbl
183 );
184 */
185 --call this instead
186 l_meas_rec.target_level_id := p_Target_Level_Rec.Target_Level_Id;
187 BIS_MEASURE_SEC_VALIDATE_PVT.Validate_Target_Level_ID
188 ( p_api_version => p_api_version
189 , p_MEASURE_Sec_Rec => l_meas_rec
190 , x_return_status => x_return_status
191 , x_error_Tbl => x_error_Tbl
192 );
193
194 -- htp.p('AFTER BIS_MEASURE_SEC_VALIDATE_PVT.Validate_Target_Level_ID: '
195 -- ||x_return_status );
196
197 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
198 x_return_status := FND_API.G_RET_STS_ERROR;
199 RAISE FND_API.G_EXC_ERROR;
200 END IF;
201
202 --
203 -- l_meas_rec.target_level_id := p_Target_Level_Rec.Target_Level_Id;
204
205
206 for cr in sec_cursor loop
207 -- l_flag :=1;
208 l_meas_rec.RESPONSIBILITY_ID := cr.RESPONSIBILITY_ID;
209 l_resp_rec.RESPONSIBILITY_ID := cr.RESPONSIBILITY_ID;
210 l_resp_rec_p := l_resp_rec;
211 BIS_RESPONSIBILITY_PVT.Retrieve_Responsibility
212 ( p_api_version => 1.0
213 , p_Responsibility_Rec => l_resp_rec_p
214 , x_Responsibility_Rec => l_resp_rec
215 , x_return_status => x_return_status
216 , x_error_tbl => x_error_tbl
217 );
218
219 l_meas_rec.Responsibility_Short_name
220 :=l_resp_rec.Responsibility_Short_name;
221 l_meas_rec.Responsibility_name:=l_resp_rec.Responsibility_name;
222 x_Measure_Security_tbl(x_Measure_Security_tbl.COUNT+1) := l_meas_rec;
223 END LOOP;
224
225 IF sec_cursor%ISOPEN THEN CLOSE sec_cursor; END IF;
226 /*
227 if(l_flag =0) then
228 RAISE FND_API.G_EXC_ERROR;
229 end if;
230 */
231 EXCEPTION
232 --WHEN OTHERS THEN
233 -- HTP.HEADER(5,'ERROR in measure sec: '||SQLERRM);
234 --added this entire section
235 WHEN NO_DATA_FOUND THEN
236 x_return_status := FND_API.G_RET_STS_ERROR ;
237 IF sec_cursor%ISOPEN THEN CLOSE sec_cursor; END IF;
238 --RAISE FND_API.G_EXC_ERROR;
239 when FND_API.G_EXC_ERROR then
240 x_return_status := FND_API.G_RET_STS_ERROR ;
241 IF sec_cursor%ISOPEN THEN CLOSE sec_cursor; END IF;
242 --RAISE FND_API.G_EXC_ERROR;
243 when FND_API.G_EXC_UNEXPECTED_ERROR then
244 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
245 IF sec_cursor%ISOPEN THEN CLOSE sec_cursor; END IF;
246 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
247 when others then
248 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
249 IF sec_cursor%ISOPEN THEN CLOSE sec_cursor; END IF;
250 --added last two params
251 l_error_tbl := x_error_tbl;
252 BIS_UTILITIES_PVT.Add_Error_Message
253 ( p_error_msg_id => SQLCODE
254 , p_error_description => SQLERRM
255 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Measure_Securities'
256 , p_error_table => l_error_tbl
257 , x_error_table => x_error_tbl
258 );
259 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
260
261 END Retrieve_Measure_Securities;
262
263
264
265
266 ----------------added this new procedure to return the list as a sorted list-------------------
267 -- Gets All Performance Measure_Securities in a soretd order
268 -- If information about the dimensions are not required, set all_info to
269 -- FALSE
270 --
271 PROCEDURE Retrieve_Measure_Sec_Sorted
272 ( p_api_version IN NUMBER
273 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
274 , x_Measure_Security_tbl
275 OUT NOCOPY BIS_MEASURE_SECURITY_PUB.Measure_Security_Tbl_Type
276 , x_return_status OUT NOCOPY VARCHAR2
277 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
278 )
279 IS
280 l_meas_rec BIS_MEASURE_SECURITY_PUB.Measure_Security_Rec_Type;
281 l_resp_rec BIS_Responsibility_PVT.Responsibility_rec_Type;
282
283 cursor sec_cursor is
284 select a.RESPONSIBILITY_ID
285 , a.RESPONSIBILITY_KEY
286 , a.RESPONSIBILITY_NAME
287 from fnd_responsibility_vl a, bis_indicator_resps b
288 where VERSION='W'
289 and start_date <= sysdate
290 and nvl(end_date, sysdate) >= sysdate
291 and a.RESPONSIBILITY_ID=b.RESPONSIBILITY_ID and b.TARGET_LEVEL_ID=p_Target_Level_Rec.Target_Level_Id
292 order by RESPONSIBILITY_NAME;
293
294 l_flag number := 0;
295 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
296 BEGIN
297
298 x_return_status := FND_API.G_RET_STS_SUCCESS;
299
300 --changed this call to the following call
301 /*
302 BIS_Target_Level_PVT.Validate_Target_Level
303 ( p_api_version => 1.0
304 , p_Target_Level_Rec => p_Target_Level_Rec
305 , x_return_status => x_return_status
306 , x_error_Tbl => x_error_Tbl
307 );
308 */
309 --call this instead
310 l_meas_rec.target_level_id := p_Target_Level_Rec.Target_Level_Id;
311 BIS_MEASURE_SEC_VALIDATE_PVT.Validate_Target_Level_ID
312 ( p_api_version => p_api_version
313 , p_MEASURE_Sec_Rec => l_meas_rec
314 , x_return_status => x_return_status
315 , x_error_Tbl => x_error_Tbl
316 );
317 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
318 x_return_status := FND_API.G_RET_STS_ERROR;
319 RAISE FND_API.G_EXC_ERROR;
320 END IF;
321
322 --
323 -- l_meas_rec.target_level_id := p_Target_Level_Rec.Target_Level_Id;
324
325
326 for cr in sec_cursor loop
327 -- l_flag :=1;
328 l_meas_rec.Responsibility_ID := cr.RESPONSIBILITY_ID ;
329 l_meas_rec.Responsibility_Short_Name := cr.RESPONSIBILITY_KEY;
330 l_meas_rec.Responsibility_Name := cr.RESPONSIBILITY_NAME;
331 x_Measure_Security_tbl(x_Measure_Security_tbl.COUNT+1) := l_meas_rec;
332 END LOOP;
333
334 IF sec_cursor%ISOPEN THEN CLOSE sec_cursor; END IF;
335 /*
336 if(l_flag =0) then
337 RAISE FND_API.G_EXC_ERROR;
338 end if;
339 */
340 EXCEPTION
341 --WHEN OTHERS THEN
342 -- HTP.HEADER(5,'ERROR in measure sec: '||SQLERRM);
343 --added this entire section
344 WHEN NO_DATA_FOUND THEN
345 x_return_status := FND_API.G_RET_STS_ERROR ;
346 IF sec_cursor%ISOPEN THEN CLOSE sec_cursor; END IF;
347 --RAISE FND_API.G_EXC_ERROR;
348 when FND_API.G_EXC_ERROR then
349 x_return_status := FND_API.G_RET_STS_ERROR ;
350 IF sec_cursor%ISOPEN THEN CLOSE sec_cursor; END IF;
351 --RAISE FND_API.G_EXC_ERROR;
352 when FND_API.G_EXC_UNEXPECTED_ERROR then
353 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
354 IF sec_cursor%ISOPEN THEN CLOSE sec_cursor; END IF;
355 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
356 when others then
357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
358 IF sec_cursor%ISOPEN THEN CLOSE sec_cursor; END IF;
359 --added last two params
360 l_error_tbl := x_error_tbl;
361 BIS_UTILITIES_PVT.Add_Error_Message
362 ( p_error_msg_id => SQLCODE
363 , p_error_description => SQLERRM
364 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Measure_Securities'
365 , p_error_table => l_error_tbl
366 , x_error_table => x_error_tbl
367 );
368 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
369
370 END Retrieve_Measure_Sec_Sorted;
371 -------------------------------
372
373 -- Gets Information for One Performance Measure_Security
374 -- If information about the dimension are not required, set all_info to FALSE.
375 --
376 --
377 PROCEDURE Retrieve_Measure_Security
378 ( p_api_version IN NUMBER
379 , p_Measure_Security_Rec IN BIS_Measure_SECURITY_PUB.Measure_Security_Rec_Type
380 , x_Measure_Security_Rec OUT NOCOPY BIS_Measure_SECURITY_PUB.Measure_Security_Rec_Type
381 , x_return_status OUT NOCOPY VARCHAR2
382 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
383 )
384 IS
385 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
386 BEGIN
387 x_return_status := FND_API.G_RET_STS_SUCCESS;
388 NULL;
389
390 --commented RAISE
391 EXCEPTION
392 WHEN NO_DATA_FOUND THEN
393 x_return_status := FND_API.G_RET_STS_ERROR ;
394 -- RAISE FND_API.G_EXC_ERROR;
395 when FND_API.G_EXC_ERROR then
396 x_return_status := FND_API.G_RET_STS_ERROR ;
397 -- RAISE FND_API.G_EXC_ERROR;
398 when FND_API.G_EXC_UNEXPECTED_ERROR then
399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
400 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
401 when others then
402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
403 --added last two params
404 l_error_tbl := x_error_tbl;
405 BIS_UTILITIES_PVT.Add_Error_Message
406 ( p_error_msg_id => SQLCODE
407 , p_error_description => SQLERRM
408 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Measure_Security'
409 , p_error_table => l_error_tbl
410 , x_error_table => x_error_tbl
411 );
412 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
413
414 END Retrieve_Measure_Security;
415 --
416 -- PLEASE VERIFY COMMENT BELOW
417 -- Update_Measure_Securitys one Measure_Security if
418 -- 1) no Measure_Security levels or targets exist
419 -- 2) no users have selected to see actuals for the Measure_Security
420 Procedure Update_Measure_Security
421 ( p_api_version IN NUMBER
422 , p_commit IN VARCHAR2 := FND_API.G_FALSE
423 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
424 , p_Measure_Security_Rec IN BIS_MEASURE_SECURITY_PUB.Measure_Security_Rec_Type
425 , x_return_status OUT NOCOPY VARCHAR2
426 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
427 )
428 IS
429 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
430 BEGIN
431
432 x_return_status := FND_API.G_RET_STS_SUCCESS;
433 Validate_Measure_Security
434 ( p_api_version => p_api_version
435 , p_Measure_Security_Rec => p_Measure_Security_Rec
436 , x_return_status => x_return_status
437 , x_error_Tbl => x_error_Tbl
438 );
439
440 if (p_commit = FND_API.G_TRUE) then
441 commit;
442 end if;
443
444 --commented RAISE
445 EXCEPTION
446 WHEN NO_DATA_FOUND THEN
447 x_return_status := FND_API.G_RET_STS_ERROR ;
448 -- RAISE FND_API.G_EXC_ERROR;
449 when FND_API.G_EXC_ERROR then
450 x_return_status := FND_API.G_RET_STS_ERROR ;
451 -- RAISE FND_API.G_EXC_ERROR;
452 when FND_API.G_EXC_UNEXPECTED_ERROR then
453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
454 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
455 when others then
456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
457 --added last two params
458 l_error_tbl := x_error_tbl;
459 BIS_UTILITIES_PVT.Add_Error_Message
460 ( p_error_msg_id => SQLCODE
461 , p_error_description => SQLERRM
462 , p_error_proc_name => G_PKG_NAME||'.Update_Measure_Security'
463 , p_error_table => l_error_tbl
464 , x_error_table => x_error_tbl
465 );
466 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
467
468 END Update_Measure_Security;
469 --
470 --
471 -- PLEASE VERIFY COMMENT BELOW
472 -- Deletes ALL responsibilities associated with a target level
473 Procedure Delete_Measure_Security
474 ( p_api_version IN NUMBER
475 , p_commit IN VARCHAR2 := FND_API.G_FALSE
476 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
477 , x_return_status OUT NOCOPY VARCHAR2
478 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
479 )
480 is
481 begin
482
483 x_return_status := FND_API.G_RET_STS_SUCCESS;
484 -- Validate_Measure_Security
485 -- ( p_api_version => p_api_version
486 -- , p_Measure_Security_Rec => p_Measure_Security_Rec
487 -- , x_return_status => x_return_status
488 -- , x_error_Tbl => x_error_Tbl-
489 -- );
490
491 delete from bis_indicator_resps
492 where target_level_id = p_Target_Level_Rec.Target_Level_Id;
493
494 if (p_commit = FND_API.G_TRUE) then
495 commit;
496 end if;
497
498
499 end Delete_Measure_Security;
500
501 Procedure Delete_Measure_Security
502 ( p_api_version IN NUMBER
503 , p_commit IN VARCHAR2 := FND_API.G_FALSE
504 , p_Measure_Security_Rec IN BIS_MEASURE_SECURITY_PUB.Measure_Security_Rec_Type
505 , x_return_status OUT NOCOPY VARCHAR2
506 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
507 )
508 IS
509 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
510 BEGIN
511
512 x_return_status := FND_API.G_RET_STS_SUCCESS;
513 Validate_Measure_Security
514 ( p_api_version => p_api_version
515 , p_Measure_Security_Rec => p_Measure_Security_Rec
516 , x_return_status => x_return_status
517 , x_error_Tbl => x_error_Tbl
518 );
519 --added this check
520 IF( x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
521 RAISE FND_API.G_EXC_ERROR;
522 END IF;
523
524 delete from bis_indicator_resps
525 where target_level_id = p_Measure_Security_Rec.Target_Level_Id
526 and RESPONSIBILITY_ID = p_Measure_Security_Rec.Responsibility_id;
527
528 if (p_commit = FND_API.G_TRUE) then
529 commit;
530 end if;
531
532 --commented RAISE
533 EXCEPTION
534 WHEN NO_DATA_FOUND THEN
535 x_return_status := FND_API.G_RET_STS_ERROR ;
536 -- RAISE FND_API.G_EXC_ERROR;
537 when FND_API.G_EXC_ERROR then
538 x_return_status := FND_API.G_RET_STS_ERROR ;
539 -- RAISE FND_API.G_EXC_ERROR;
540 when FND_API.G_EXC_UNEXPECTED_ERROR then
541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
542 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
543 when others then
544 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
545 --added last two params
546 l_error_tbl := x_error_tbl;
547 BIS_UTILITIES_PVT.Add_Error_Message
548 ( p_error_msg_id => SQLCODE
549 , p_error_description => SQLERRM
550 , p_error_proc_name => G_PKG_NAME||'.Delete_Measure_Security'
551 , p_error_table => l_error_tbl
552 , x_error_table => x_error_tbl
553 );
554 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
555
556 END Delete_Measure_Security;
557 --
558 -- Validates Measure_Security
559 PROCEDURE Validate_Measure_Security
560 ( p_api_version IN NUMBER
561 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
562 ,p_Measure_Security_Rec IN BIS_MEASURE_SECURITY_PUB.Measure_Security_Rec_Type
563 , x_return_status OUT NOCOPY VARCHAR2
564 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
565 )
566 IS
567 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
568 l_error VARCHAR2(10) := FND_API.G_FALSE;
569 l_error_Tbl_p BIS_UTILITIES_PUB.Error_Tbl_Type;
570 BEGIN
571
572 x_return_status := FND_API.G_RET_STS_SUCCESS;
573 BEGIN
574 BIS_MEASURE_SEC_VALIDATE_PVT.Validate_Target_Level_ID
575 ( p_api_version => p_api_version
576 , p_validation_level => p_validation_level
577 , p_MEASURE_Sec_Rec => p_MEASURE_Security_Rec
578 , x_return_status => x_return_status
579 , x_error_Tbl => l_error_Tbl
580 );
581 -- EXCEPTION
582 -- when FND_API.G_EXC_ERROR then
583 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
584 l_error := FND_API.G_TRUE;
585 l_error_Tbl_p := x_error_Tbl;
586 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
587 , l_error_Tbl
588 , x_error_tbl
589 );
590 x_return_status := FND_API.G_RET_STS_ERROR;
591 END IF;
592 END;
593
594 BEGIN
595 BIS_MEASURE_SEC_VALIDATE_PVT.Validate_Responsibility_Id
596 ( p_api_version => p_api_version
597 , p_validation_level => p_validation_level
598 , p_MEASURE_Sec_Rec => p_MEASURE_Security_Rec
599 , x_return_status => x_return_status
600 , x_error_Tbl => l_error_Tbl
601 );
602 -- EXCEPTION
603 -- when FND_API.G_EXC_ERROR then
604 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
605 l_error := FND_API.G_TRUE;
606 l_error_Tbl_p := x_error_Tbl;
607 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
608 , l_error_Tbl
609 , x_error_tbl
610 );
611 x_return_status := FND_API.G_RET_STS_ERROR;
612 END IF;
613 END;
614
615 BEGIN
616 BIS_MEASURE_SEC_VALIDATE_PVT.Validate_Record
617 ( p_api_version => p_api_version
618 , p_validation_level => p_validation_level
619 , p_MEASURE_Sec_Rec => p_MEASURE_Security_Rec
620 , x_return_status => x_return_status
621 , x_error_Tbl => l_error_Tbl
622 );
623
624 --changed this
625 /*
626 if (x_error_tbl.count > 0) then
627 BIS_UTILITIES_PVT.concatenateErrorTables( x_error_Tbl
628 , l_error_Tbl
629 , x_error_tbl
630 );
631 RAISE FND_API.G_EXC_ERROR;
632 end if;
633 */
634 --added this
635 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
636 l_error := FND_API.G_TRUE;
637 l_error_Tbl_p := x_error_Tbl;
638 BIS_UTILITIES_PVT.concatenateErrorTables( l_error_Tbl_p
639 , l_error_Tbl
640 , x_error_tbl
641 );
642 x_return_status := FND_API.G_RET_STS_ERROR;
643 END IF;
644 END;
645
646 --added this
647 if (l_error = FND_API.G_TRUE) then
648 RAISE FND_API.G_EXC_ERROR;
649 end if;
650
651 --commented RAISE
652 EXCEPTION
653 WHEN NO_DATA_FOUND THEN
654 x_return_status := FND_API.G_RET_STS_ERROR ;
655 -- RAISE FND_API.G_EXC_ERROR;
656 when FND_API.G_EXC_ERROR then
657 x_return_status := FND_API.G_RET_STS_ERROR ;
658 -- RAISE FND_API.G_EXC_ERROR;
659 when FND_API.G_EXC_UNEXPECTED_ERROR then
660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
661 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
662 when others then
663 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
664 --added last two params
665 l_error_tbl := x_error_tbl;
666 BIS_UTILITIES_PVT.Add_Error_Message
667 ( p_error_msg_id => SQLCODE
668 , p_error_description => SQLERRM
669 , p_error_proc_name => G_PKG_NAME||'.Validate_Measure_Security'
670 , p_error_table => l_error_tbl
671 , x_error_table => x_error_tbl
672 );
673 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
674
675 END Validate_Measure_Security;
676 --
677 -- Value - ID conversion
678 PROCEDURE Value_ID_Conversion
679 ( p_api_version IN NUMBER
680 , p_Measure_Security_Rec IN BIS_MEASURE_SECURITY_PUB.Measure_Security_Rec_Type
681 , x_Measure_Security_Rec OUT NOCOPY BIS_MEASURE_SECURITY_PUB.Measure_Security_Rec_Type
682 , x_return_status OUT NOCOPY VARCHAR2
683 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
684 )
685 IS
686 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
687 BEGIN
688
689 x_return_status := FND_API.G_RET_STS_SUCCESS;
690 x_Measure_Security_Rec := p_Measure_Security_Rec;
691
692 if (BIS_UTILITIES_PUB.Value_Missing(p_Measure_Security_Rec.Target_Level_id)=
693 FND_API.G_TRUE
694 OR BIS_UTILITIES_PUB.Value_NULL(p_Measure_Security_Rec.Target_Level_id)=
695 FND_API.G_TRUE) THEN
696 BIS_Target_Level_PVT.Value_ID_Conversion
697 ( p_api_version => 1.0
698 , p_Target_Level_Short_Name =>
699 p_Measure_Security_Rec.Target_Level_Short_Name
700 , p_Target_Level_Name => p_Measure_Security_Rec.Target_Level_Name
701 , x_Target_Level_ID => x_Measure_Security_Rec.Target_Level_ID
702 , x_return_status => x_return_status
703 , x_error_Tbl => x_error_Tbl
704 );
705 END IF;
706 --
707 -- This will be the value id for responsibility
708 -- BIS_Target_Level_PVT.Value_ID_Conversion
709 -- ( p_api_version => 1.0
710 -- - , p_Target_Level_Short_Name =>
711 -- p_Measure_Security_Rec.Target_Level_Short_Name
712 -- , p_Target_Level_Name => p_Measure_Security_Rec.Target_Level_Name
713 -- , x_Target_Level_ID => x_Measure_Security_Rec.Target_Level_ID
714 -- , x_return_status => x_return_status
715 -- , x_error_Tbl => x_error_Tbl
716 -- );
717
718 --commented RAISE
719 EXCEPTION
720 WHEN NO_DATA_FOUND THEN
721 x_return_status := FND_API.G_RET_STS_ERROR ;
722 --RAISE FND_API.G_EXC_ERROR;
723 when FND_API.G_EXC_ERROR then
724 x_return_status := FND_API.G_RET_STS_ERROR ;
725 -- RAISE FND_API.G_EXC_ERROR;
726 when FND_API.G_EXC_UNEXPECTED_ERROR then
727 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
728 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
729 when others then
730 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
731 --added last two params
732 l_error_tbl := x_error_tbl;
733 BIS_UTILITIES_PVT.Add_Error_Message
734 ( p_error_msg_id => SQLCODE
735 , p_error_description => SQLERRM
736 , p_error_proc_name => G_PKG_NAME||'.Value_ID_Conversion'
737 , p_error_table => l_error_tbl
738 , x_error_table => x_error_tbl
739 );
740 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
741
742 END Value_ID_Conversion;
743 --
744 Procedure Retrieve_Tar_Level_User_Resps
745 ( p_api_version IN NUMBER
746 , p_user_id IN NUMBER
747 , p_Target_Level_Rec IN BIS_Target_Level_PUB.Target_Level_Rec_Type
748 , x_Measure_security_Tbl OUT NOCOPY BIS_MEASURE_SECURITY_PUB.Measure_Security_Tbl_Type
749 , x_return_status OUT NOCOPY VARCHAR2
750 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
751 )
752 is
753
754 CURSOR ind_res_id IS
755 select il.target_level_id
756 , il.target_level_short_name
757 , il.target_level_name
758 , ir.Responsibility_ID
759 , fr.RESPONSIBILITY_NAME
760 , fr.RESPONSIBILITY_KEY
761 from bis_indicator_resps ir
762 , fnd_user_resp_groups ur
763 , bisbv_target_levels il
764 , fnd_responsibility_vl fr
765 where ur.user_id = p_user_id
766 and ir.responsibility_id = ur.responsibility_id
767 and ir.responsibility_id = fr.responsibility_id
768 and il.target_level_id = ir.target_level_id
769 and il.target_level_id = p_target_level_rec.target_level_id;
770
771 CURSOR ind_res_short_name IS
772 select il.target_level_id
773 , il.target_level_short_name
774 , il.target_level_name
775 , ir.Responsibility_ID
776 , fr.RESPONSIBILITY_NAME
777 , fr.RESPONSIBILITY_KEY
778 from bis_indicator_resps ir
779 , fnd_user_resp_groups ur
780 , bisbv_target_levels il
781 , fnd_responsibility_vl fr
782 where ur.user_id = p_user_id
783 and ir.responsibility_id = ur.responsibility_id
784 and ir.responsibility_id = fr.responsibility_id
785 and il.target_level_id = ir.target_level_id
786 and il.target_level_short_name=p_target_level_rec.target_level_short_name;
787
788 CURSOR ind_res_name IS
789 select il.target_level_id
790 , il.target_level_short_name
791 , il.target_level_name
792 , ir.Responsibility_ID
793 , fr.RESPONSIBILITY_NAME
794 , fr.RESPONSIBILITY_KEY
795 from bis_indicator_resps ir
796 , fnd_user_resp_groups ur
797 , bisbv_target_levels il
798 , fnd_responsibility_vl fr
799 where ur.user_id = p_user_id
800 and ir.responsibility_id = ur.responsibility_id
801 and ir.responsibility_id = fr.responsibility_id
802 and il.target_level_id = ir.target_level_id
803 and il.target_level_name = p_target_level_rec.target_level_name;
804
805 l_rec BIS_MEASURE_SECURITY_PUB.Measure_Security_Rec_Type;
806 l_flag number := 0;
807 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
808 BEGIN
809 x_return_status := FND_API.G_RET_STS_SUCCESS;
810
811 IF (BIS_UTILITIES_PUB.Value_Not_Missing(p_target_level_Rec.Target_Level_id)=
812 FND_API.G_TRUE)
813 THEN
814 for cr in ind_res_id loop
815 l_flag := 1;
816 l_rec.Target_Level_ID := cr.target_level_id ;
817 l_rec.Target_Level_Short_Name := cr.target_level_short_name;
818 l_rec.Target_Level_Name := cr.target_level_name;
819 l_rec.Responsibility_ID := cr.Responsibility_ID ;
820 l_rec.Responsibility_Short_Name := cr.RESPONSIBILITY_KEY;
821 l_rec.Responsibility_Name := cr.RESPONSIBILITY_NAME ;
822 x_Measure_security_Tbl(x_Measure_security_Tbl.count+1) := l_rec;
823 end loop;
824
825 ELSIF (BIS_UTILITIES_PUB.Value_Not_Missing
826 (p_target_level_Rec.Target_Level_short_name)=FND_API.G_TRUE)
827 THEN
828 for cr in ind_res_short_name loop
829 l_flag := 1;
830 l_rec.Target_Level_ID := cr.target_level_id ;
831 l_rec.Target_Level_Short_Name := cr.target_level_short_name;
832 l_rec.Target_Level_Name := cr.target_level_name;
833 l_rec.Responsibility_ID := cr.Responsibility_ID ;
834 l_rec.Responsibility_Short_Name := cr.RESPONSIBILITY_KEY;
835 l_rec.Responsibility_Name := cr.RESPONSIBILITY_NAME ;
836 x_Measure_security_Tbl(x_Measure_security_Tbl.count+1) := l_rec;
837 end loop;
838
839 ELSIF (BIS_UTILITIES_PUB.Value_Not_Missing
840 (p_target_level_Rec.Target_Level_name)=FND_API.G_TRUE)
841 THEN
842 for cr in ind_res_name loop
843 l_flag := 1;
844 l_rec.Target_Level_ID := cr.target_level_id ;
845 l_rec.Target_Level_Short_Name := cr.target_level_short_name;
846 l_rec.Target_Level_Name := cr.target_level_name;
847 l_rec.Responsibility_ID := cr.Responsibility_ID ;
848 l_rec.Responsibility_Short_Name := cr.RESPONSIBILITY_KEY;
849 l_rec.Responsibility_Name := cr.RESPONSIBILITY_NAME ;
850 x_Measure_security_Tbl(x_Measure_security_Tbl.count+1) := l_rec;
851 end loop;
852 ELSE
853 --added message
854 l_error_tbl := x_error_tbl;
855 BIS_UTILITIES_PVT.Add_Error_Message
856 ( p_error_msg_name => 'BIS_INVALID_MSR_SECURITY_VALUE'
857 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
858 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target_Level_User_Resps'
859 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
860 , p_error_table => l_error_tbl
861 , x_error_table => x_error_tbl
862 );
863 RAISE FND_API.G_EXC_ERROR;
864 END IF;
865
866 IF ind_res_name%ISOPEN THEN CLOSE ind_res_name; END IF;
867 --added this
868 if(l_flag =0) then
869 --added message
870 l_error_tbl := x_error_tbl;
871 BIS_UTILITIES_PVT.Add_Error_Message
872 ( p_error_msg_name => 'BIS_INVALID_MSR_SECURITY_VALUE'
873 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
874 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Target_Level_User_Resps'
875 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
876 , p_error_table => l_error_tbl
877 , x_error_table => x_error_tbl
878 );
879 RAISE FND_API.G_EXC_ERROR;
880 end if;
881
882
883
884 --commented RAISE
885 EXCEPTION
886 WHEN NO_DATA_FOUND THEN
887 x_return_status := FND_API.G_RET_STS_ERROR ;
888 IF ind_res_name%ISOPEN THEN CLOSE ind_res_name; END IF;
889 -- RAISE FND_API.G_EXC_ERROR;
890 when FND_API.G_EXC_ERROR then
891 x_return_status := FND_API.G_RET_STS_ERROR ;
892 IF ind_res_name%ISOPEN THEN CLOSE ind_res_name; END IF;
893 --RAISE FND_API.G_EXC_ERROR;
894 when FND_API.G_EXC_UNEXPECTED_ERROR then
895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
896 IF ind_res_name%ISOPEN THEN CLOSE ind_res_name; END IF;
897 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
898 when others then
899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
900 IF ind_res_name%ISOPEN THEN CLOSE ind_res_name; END IF;
901 --added last two params
902 l_error_tbl := x_error_tbl;
903 BIS_UTILITIES_PVT.Add_Error_Message
904 ( p_error_msg_id => SQLCODE
905 , p_error_description => SQLERRM
906 , p_error_proc_name => G_PKG_NAME||'.Retrieve_Tar_Level_User_Resps'
907 , p_error_table => l_error_tbl
908 , x_error_table => x_error_tbl
909 );
910 --RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
911
912 end Retrieve_Tar_Level_User_Resps;
913 --
914
915 --new API to validate Measure Security for bug 1716213
916 PROCEDURE Validate_Measure_Security
917 ( p_api_version IN NUMBER
918 , p_user_id IN NUMBER
919 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
920 , p_Measure_Security_Rec IN BIS_MEASURE_SECURITY_PUB.Measure_Security_Rec_Type
921 , x_return_status OUT NOCOPY VARCHAR2
922 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
923 )
924 IS
925 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
926 l_error VARCHAR2(10) := FND_API.G_FALSE;
927 l_dlc_sec VARCHAR2(1);
928 CURSOR c_resp IS
929 SELECT responsibility_id
930 FROM fnd_user_resp_groups
931 WHERE user_id=p_user_id;
932 CURSOR c_indresp(p_target_level_id IN NUMBER) IS
933 SELECT responsibility_id
934 FROM bis_indicator_Resps
935 WHERE target_level_id=p_target_level_id;
936
937 BEGIN
938
939 x_return_status := FND_API.G_RET_STS_SUCCESS;
940 BEGIN
941
942 BIS_MEASURE_SEC_VALIDATE_PVT.Validate_Target_Level_ID
943 ( p_api_version => p_api_version
944 , p_validation_level => p_validation_level
945 , p_MEASURE_Sec_Rec => p_MEASURE_Security_Rec
946 , x_return_status => x_return_status
947 , x_error_Tbl => l_error_Tbl
948 );
949
950 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
951 l_error := FND_API.G_TRUE;
952
953 l_error_tbl := x_error_tbl;
954 BIS_UTILITIES_PVT.Add_Error_Message
955 ( p_error_msg_name => 'BIS_INVALID_MSR_SECURITY_VALUE'
956 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
957 , p_error_proc_name => G_PKG_NAME||'.Validate_Measure_Security'
958 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
959 , p_error_table => l_error_tbl
960 , x_error_table => x_error_tbl
961 );
962 x_return_status := FND_API.G_RET_STS_ERROR;
963 RAISE FND_API.G_EXC_ERROR;
964 END IF;
965 END;
966
967 BEGIN
968
969 l_dlc_sec := 'N';
970 FOR c_rec IN c_resp LOOP
971 FOR c_indrec IN c_indresp(p_Measure_Security_Rec.target_level_id) LOOP
972 IF (c_indrec.responsibility_id = c_rec.responsibility_id)
973 THEN
974 l_dlc_sec := 'Y';
975 EXIT;
976 END IF;
977 END LOOP;
978 END LOOP;
979 IF (l_dlc_sec = 'N') THEN
980 l_error_tbl := x_error_tbl;
981 BIS_UTILITIES_PVT.Add_Error_Message
982 ( p_error_msg_name => 'BIS_INVALID_MSR_SECUIRTY_VALUE'
983 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
984 , p_error_proc_name => G_PKG_NAME||'.Validate_Measure_Security'
985 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
986 , p_error_table => l_error_tbl
987 , x_error_table => x_error_tbl
988 );
989 x_return_status := FND_API.G_RET_STS_ERROR;
990 l_error := FND_API.G_TRUE;
991 END IF;
992
993 END;
994
995
996
997 --added this
998 if (l_error = FND_API.G_TRUE) then
999 RAISE FND_API.G_EXC_ERROR;
1000 end if;
1001
1002 --commented RAISE
1003 EXCEPTION
1004 WHEN NO_DATA_FOUND THEN
1005 x_return_status := FND_API.G_RET_STS_ERROR ;
1006 -- RAISE FND_API.G_EXC_ERROR;
1007 when FND_API.G_EXC_ERROR then
1008 x_return_status := FND_API.G_RET_STS_ERROR ;
1009 -- RAISE FND_API.G_EXC_ERROR;
1010 when FND_API.G_EXC_UNEXPECTED_ERROR then
1011 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1012 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1013 when others then
1014 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1015 --added last two params
1016 l_error_tbl := x_error_tbl;
1017 BIS_UTILITIES_PVT.Add_Error_Message
1018 ( p_error_msg_id => SQLCODE
1019 , p_error_description => SQLERRM
1020 , p_error_proc_name => G_PKG_NAME||'.Validate_Measure_Security'
1021 , p_error_table => l_error_tbl
1022 , x_error_table => x_error_tbl
1023 );
1024 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1025
1026 END Validate_Measure_Security;
1027
1028 -- new API to delete the responsibilities attached to the target levels
1029 -- pertaining to the measure specified by the measure short name
1030 PROCEDURE Delete_TargetLevel_Resp
1031 ( p_commit IN VARCHAR2 := FND_API.G_FALSE
1032 , p_measure_short_name IN VARCHAR2
1033 , x_return_status OUT NOCOPY VARCHAR2
1034 , x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
1035 )
1036 IS
1037 l_indicator_id NUMBER;
1038 l_error_Tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
1039 CURSOR target_level_cursor(cp_indicator_id IN NUMBER) IS
1040 SELECT target_level_id FROM bis_target_levels
1041 WHERE indicator_id = cp_indicator_id;
1042
1043 BEGIN
1044 SAVEPOINT delete_resps;
1045 x_return_status := FND_API.G_RET_STS_SUCCESS;
1046 SELECT indicator_id INTO l_indicator_id FROM bis_indicators WHERE short_name = p_measure_short_name;
1047 FOR target_cursor IN target_level_cursor(l_indicator_id) LOOP
1048 DELETE FROM bis_indicator_resps
1049 WHERE target_level_id = target_cursor.target_level_id;
1050 END LOOP;
1051
1052 IF (p_commit = FND_API.G_TRUE) THEN
1053 COMMIT;
1054 END IF;
1055
1056 EXCEPTION
1057 WHEN NO_DATA_FOUND THEN
1058 ROLLBACK TO delete_resps;
1059 x_return_status := FND_API.G_RET_STS_ERROR ;
1060 l_error_tbl := x_error_tbl;
1061 BIS_UTILITIES_PVT.Add_Error_Message
1062 ( p_error_msg_name => 'BIS_INVALID_MEASURE_SHORT_NAME'
1063 , p_error_msg_level => FND_MSG_PUB.G_MSG_LVL_ERROR
1064 , p_error_proc_name => G_PKG_NAME||'.Delete_TargetLevel_Resp'
1065 , p_error_type => BIS_UTILITIES_PUB.G_ERROR
1066 , p_error_table => l_error_tbl
1067 , x_error_table => x_error_tbl
1068 );
1069 WHEN FND_API.G_EXC_ERROR THEN
1070 ROLLBACK TO delete_resps;
1071 x_return_status := FND_API.G_RET_STS_ERROR ;
1072 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1073 ROLLBACK TO delete_resps;
1074 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1075 WHEN OTHERS THEN
1076 ROLLBACK TO delete_resps;
1077 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1078 l_error_tbl := x_error_tbl;
1079 BIS_UTILITIES_PVT.Add_Error_Message
1080 ( p_error_msg_id => SQLCODE
1081 , p_error_description => SQLERRM
1082 , p_error_proc_name => G_PKG_NAME||'.Delete_TargetLevel_Resp'
1083 , p_error_table => l_error_tbl
1084 , x_error_table => x_error_tbl
1085 );
1086
1087 END Delete_TargetLevel_Resp;
1088
1089 END BIS_MEASURE_SECURITY_PVT;