[Home] [Help]
PACKAGE BODY: APPS.PSB_PAY_ELEMENT_OPTIONS_PVT
Source
1 PACKAGE BODY PSB_PAY_ELEMENT_OPTIONS_PVT AS
2 /* $Header: PSBVOPTB.pls 120.2 2005/07/13 11:27:27 shtripat ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PSB_PAY_ELEMENT_OPTIONS_PVT';
5
6 /* ----------------------------------------------------------------------- */
7
8 PROCEDURE INSERT_ROW
9 ( p_api_version IN NUMBER,
10 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
11 p_commit IN VARCHAR2 := FND_API.G_FALSE,
12 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
13 p_return_status OUT NOCOPY VARCHAR2,
14 p_msg_count OUT NOCOPY NUMBER,
15 p_msg_data OUT NOCOPY VARCHAR2,
16 --
17 P_PAY_ELEMENT_OPTION_ID in NUMBER,
18 P_PAY_ELEMENT_ID in NUMBER,
19 P_NAME in VARCHAR2,
20 P_GRADE_STEP in NUMBER,
21 P_SEQUENCE_NUMBER in NUMBER,
22 P_LAST_UPDATE_DATE in DATE,
23 P_LAST_UPDATED_BY in NUMBER,
24 P_LAST_UPDATE_LOGIN in NUMBER,
25 P_CREATED_BY in NUMBER,
26 P_CREATION_DATE in DATE
27 ) IS
28
29 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ROW';
30 l_api_version CONSTANT NUMBER := 1.0;
31 l_row_id varchar2(40);
32 --
33 cursor c1 is
34 select ROWID from psb_pay_element_options
35 where pay_element_option_id = p_pay_element_option_id
36 and pay_element_id = p_pay_element_id;
37
38 BEGIN
39
40 -- Standard Start of API savepoint
41
42 SAVEPOINT INSERT_ROW_PVT;
43
44 -- Standard call to check for call compatibility.
45
46 if not FND_API.Compatible_API_Call (l_api_version,
47 p_api_version,
48 l_api_name,
49 G_PKG_NAME)
50 then
51 raise FND_API.G_EXC_UNEXPECTED_ERROR;
52 end if;
53
54 -- Initialize message list if p_init_msg_list is set to TRUE.
55
56 if FND_API.to_Boolean (p_init_msg_list) then
57 FND_MSG_PUB.initialize;
58 end if;
59
60 -- Initialize API return status to success
61
62 p_return_status := FND_API.G_RET_STS_SUCCESS;
63
64
65 -- API body
66 INSERT INTO psb_pay_element_options
67 (
68 PAY_ELEMENT_OPTION_ID ,
69 PAY_ELEMENT_ID ,
70 NAME ,
71 GRADE_STEP ,
72 SEQUENCE_NUMBER ,
73 LAST_UPDATE_DATE ,
74 LAST_UPDATED_BY ,
75 LAST_UPDATE_LOGIN ,
76 CREATED_BY ,
77 CREATION_DATE
78 )
79 VALUES
80 (
81 P_PAY_ELEMENT_OPTION_ID ,
82 P_PAY_ELEMENT_ID ,
83 P_NAME ,
84 P_GRADE_STEP ,
85 P_SEQUENCE_NUMBER ,
86 P_LAST_UPDATE_DATE ,
87 P_LAST_UPDATED_BY ,
88 P_LAST_UPDATE_LOGIN ,
89 P_CREATED_BY ,
90 P_CREATION_DATE
91 );
92
93 open c1;
94 fetch c1 into l_row_id;
95 if (c1%notfound) then
96 close c1;
97 raise no_data_found;
98 end if;
99 -- End of API body.
100
101 -- Standard check of p_commit.
102
103 if FND_API.to_Boolean (p_commit) then
104 commit work;
105 end if;
106
107 -- Standard call to get message count and if count is 1, get message info.
108
109 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
110 p_data => p_msg_data);
111
112 EXCEPTION
113
114 when FND_API.G_EXC_ERROR then
115
116 rollback to INSERT_ROW_PVT;
117
118 p_return_status := FND_API.G_RET_STS_ERROR;
119
120 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
121 p_data => p_msg_data);
122
123
124 when FND_API.G_EXC_UNEXPECTED_ERROR then
125
126 rollback to INSERT_ROW_PVT;
127
128 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
129
130 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
131 p_data => p_msg_data);
132
133
134 when OTHERS then
135
136 rollback to INSERT_ROW_PVT;
137
138 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
139
140 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
141
142 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
143 l_api_name);
144 end if;
145
146 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
147 p_data => p_msg_data);
148
149 END INSERT_ROW;
150
151 PROCEDURE UPDATE_ROW
152 ( p_api_version IN NUMBER,
153 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
154 p_commit IN VARCHAR2 := FND_API.G_FALSE,
155 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
156 p_return_status OUT NOCOPY VARCHAR2,
157 p_msg_count OUT NOCOPY NUMBER,
158 p_msg_data OUT NOCOPY VARCHAR2,
159 --
160 P_PAY_ELEMENT_OPTION_ID in NUMBER,
161 P_PAY_ELEMENT_ID in NUMBER,
162 P_NAME in VARCHAR2,
163 P_GRADE_STEP in NUMBER,
164 P_SEQUENCE_NUMBER in NUMBER,
165 P_LAST_UPDATE_DATE in DATE,
166 P_LAST_UPDATED_BY in NUMBER,
167 P_LAST_UPDATE_LOGIN in NUMBER
168 ) IS
169
170 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ROW';
171 l_api_version CONSTANT NUMBER := 1.0;
172
173 BEGIN
174
175 -- Standard Start of API savepoint
176
177 SAVEPOINT UPDATE_ROW_PVT;
178
179 -- Standard call to check for call compatibility.
180
181 if not FND_API.Compatible_API_Call (l_api_version,
182 p_api_version,
183 l_api_name,
184 G_PKG_NAME)
185 then
186 raise FND_API.G_EXC_UNEXPECTED_ERROR;
187 end if;
188
189 -- Initialize message list if p_init_msg_list is set to TRUE.
190
191 if FND_API.to_Boolean (p_init_msg_list) then
192 FND_MSG_PUB.initialize;
193 end if;
194
195 -- Initialize API return status to success
196
197 p_return_status := FND_API.G_RET_STS_SUCCESS;
198
199 -- API body
200 UPDATE psb_pay_element_options SET
201 NAME = P_NAME ,
202 GRADE_STEP = P_GRADE_STEP ,
203 SEQUENCE_NUMBER = P_SEQUENCE_NUMBER ,
204 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE ,
205 LAST_UPDATED_BY = P_LAST_UPDATED_BY ,
206 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
207 WHERE pay_element_option_id = p_pay_element_option_id
208 AND pay_element_id = p_pay_element_id;
209
210 if (SQL%NOTFOUND) then
211 RAISE NO_DATA_FOUND;
212 end if;
213
214 -- End of API body.
215
216 -- Standard check of p_commit.
217
218 if FND_API.to_Boolean (p_commit) then
219 commit work;
220 end if;
221
222 -- Standard call to get message count and if count is 1, get message info.
223
224 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
225 p_data => p_msg_data);
226
227 EXCEPTION
228
229 when FND_API.G_EXC_ERROR then
230
231 rollback to UPDATE_ROW_PVT;
232
233 p_return_status := FND_API.G_RET_STS_ERROR;
234
235 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
236 p_data => p_msg_data);
237
238
239 when FND_API.G_EXC_UNEXPECTED_ERROR then
240
241 rollback to UPDATE_ROW_PVT;
242
243 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
244
245 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
246 p_data => p_msg_data);
247
248
249 when OTHERS then
250
251 rollback to UPDATE_ROW_PVT;
252
253 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
254
255 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
256
257 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
258 l_api_name);
259 end if;
260
261 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
262 p_data => p_msg_data);
263
264 END UPDATE_ROW;
265
266
267 PROCEDURE DELETE_ROW
268 ( p_api_version IN NUMBER,
269 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
270 p_commit IN VARCHAR2 := FND_API.G_FALSE,
271 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
272 p_return_status OUT NOCOPY VARCHAR2,
273 p_msg_count OUT NOCOPY NUMBER,
274 p_msg_data OUT NOCOPY VARCHAR2,
275 --
276 P_PAY_ELEMENT_OPTION_ID IN NUMBER,
277 P_PAY_ELEMENT_ID IN NUMBER
278 ) IS
279
280 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROW';
281 l_api_version CONSTANT NUMBER := 1.0;
282
283 BEGIN
284
285 -- Standard Start of API savepoint
286
287 SAVEPOINT DELETE_ROW_PVT;
288
289 -- Standard call to check for call compatibility.
290
291 if not FND_API.Compatible_API_Call (l_api_version,
292 p_api_version,
293 l_api_name,
294 G_PKG_NAME)
295 then
296 raise FND_API.G_EXC_UNEXPECTED_ERROR;
297 end if;
298
299 -- Initialize message list if p_init_msg_list is set to TRUE.
300
301 if FND_API.to_Boolean (p_init_msg_list) then
302 FND_MSG_PUB.initialize;
303 end if;
304
305 --Deleting detail record from psb_pay_element_options and
306 --from psb_pay_element_rates to maintain the isolated delete
307 --relation between the master and detail
308
309 DELETE FROM psb_pay_element_rates
310 WHERE pay_element_option_id = p_pay_element_option_id
311 AND pay_element_id = p_pay_element_id;
312
313 --Delete the record in the master table
314 DELETE FROM psb_pay_element_options
315 WHERE pay_element_option_id = p_pay_element_option_id
316 AND pay_element_id = p_pay_element_id;
317
318
319 if (SQL%NOTFOUND) then
320 RAISE NO_DATA_FOUND;
321 end if;
322
323 -- Standard call to get message count and if count is 1, get message info.
324
325 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
326 p_data => p_msg_data);
327
328 -- Standard check of p_commit.
329
330 if FND_API.to_Boolean (p_commit) then
331 commit work;
332 end if;
333
334
335 EXCEPTION
336
337 when FND_API.G_EXC_ERROR then
338
339 rollback to DELETE_ROW_PVT;
340
341 p_return_status := FND_API.G_RET_STS_ERROR;
342
343 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
344 p_data => p_msg_data);
345
346
347 when FND_API.G_EXC_UNEXPECTED_ERROR then
348
349 rollback to DELETE_ROW_PVT;
350
351 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
352
353 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
354 p_data => p_msg_data);
355
356
357 when OTHERS then
358
359 rollback to DELETE_ROW_PVT;
360
361 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
362
363 if FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) then
364
365 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
366 l_api_name);
367 end if;
368
369 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
370 p_data => p_msg_data);
371 END DELETE_ROW;
372
373 PROCEDURE LOCK_ROW(
374 p_api_version IN NUMBER,
375 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
376 p_commit IN VARCHAR2 := FND_API.G_FALSE,
377 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
378 p_return_status OUT NOCOPY VARCHAR2,
379 p_msg_count OUT NOCOPY NUMBER,
380 p_msg_data OUT NOCOPY VARCHAR2,
381 --
382 p_row_locked OUT NOCOPY VARCHAR2,
383 --
384 P_PAY_ELEMENT_OPTION_ID in NUMBER,
385 P_PAY_ELEMENT_ID in NUMBER,
386 P_NAME in VARCHAR2,
387 P_GRADE_STEP in NUMBER,
388 P_SEQUENCE_NUMBER in NUMBER
389
390 ) IS
391
392 l_api_name CONSTANT VARCHAR2(30) := 'LOCK_ROW';
393 l_api_version CONSTANT NUMBER := 1.0;
394 --
395 counter number;
396
397 CURSOR C IS SELECT * FROM PSB_PAY_ELEMENT_OPTIONS
398 WHERE pay_element_option_id = p_pay_element_option_id
399 AND pay_element_id = p_pay_element_id
400 FOR UPDATE of PAY_ELEMENT_OPTION_Id NOWAIT;
401 Recinfo C%ROWTYPE;
402
403 BEGIN
404 --
405 SAVEPOINT Lock_Row_Pvt ;
406 --
407 IF NOT FND_API.Compatible_API_Call ( l_api_version,
408 p_api_version,
409 l_api_name,
410 G_PKG_NAME )
411 THEN
412 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
413 END IF;
414 --
415
416 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
417 FND_MSG_PUB.initialize ;
418 END IF;
419 --
420 p_return_status := FND_API.G_RET_STS_SUCCESS ;
421 p_row_locked := FND_API.G_TRUE ;
422 --
423 OPEN C;
424 --
425 FETCH C INTO Recinfo;
426 IF (C%NOTFOUND) then
427 CLOSE C;
428 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
429 FND_MSG_PUB.Add;
430 RAISE FND_API.G_EXC_ERROR ;
431 END IF;
432
433 IF
434 (
435 (Recinfo.pay_element_option_id = p_pay_element_option_id)
436 AND (Recinfo.pay_element_id = p_pay_element_id)
437
438 AND ( (Recinfo.name = p_name)
439 OR ( (Recinfo.name IS NULL)
440 AND (p_name IS NULL)))
441
442 AND ( (Recinfo.grade_step = p_grade_step)
443 OR ( (Recinfo.grade_step IS NULL)
444 AND (p_grade_step IS NULL)))
445
446 AND ( (Recinfo.sequence_number = p_sequence_number)
447 OR ( (Recinfo.sequence_number IS NULL)
448 AND (p_sequence_number IS NULL)))
449 )
450
451 THEN
452 Null;
453 ELSE
454 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
455 FND_MSG_PUB.Add;
456 RAISE FND_API.G_EXC_ERROR ;
457 END IF;
458
459 --
460 IF FND_API.To_Boolean ( p_commit ) THEN
461 COMMIT WORK;
462 END iF;
463 --
464 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
465 p_data => p_msg_data );
466 --
467 EXCEPTION
468 --
469 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
470 --
471 ROLLBACK TO Lock_Row_Pvt ;
472 p_row_locked := FND_API.G_FALSE;
473 p_return_status := FND_API.G_RET_STS_ERROR;
474 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
475 p_data => p_msg_data );
476 --
477 WHEN FND_API.G_EXC_ERROR THEN
478 --
479 ROLLBACK TO Lock_Row_Pvt ;
480 p_return_status := FND_API.G_RET_STS_ERROR;
481 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
482 p_data => p_msg_data );
483 --
484 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
485 --
486 ROLLBACK TO Lock_Row_Pvt ;
487 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
488 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
489 p_data => p_msg_data );
490 --
491 WHEN OTHERS THEN
492 --
493 ROLLBACK TO Lock_Row_Pvt ;
494 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
495 --
496 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
497 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
498 l_api_name);
499 END if;
500 --
501 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
502 p_data => p_msg_data );
503 END LOCK_ROW;
504
505
506 PROCEDURE Check_Unique
507 (
508 p_api_version IN NUMBER,
509 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
510 p_commit IN VARCHAR2 := FND_API.G_FALSE,
511 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
512 p_return_status OUT NOCOPY VARCHAR2,
513 p_msg_count OUT NOCOPY NUMBER,
514 p_msg_data OUT NOCOPY VARCHAR2,
515 --
516 p_PAY_ELEMENT_OPTION_ID IN NUMBER,
517 p_PAY_ELEMENT_ID IN NUMBER,
518 p_NAME IN VARCHAR2,
519 p_GRADE_STEP IN NUMBER,
520 p_RETURN_VALUE IN OUT NOCOPY VARCHAR2
521 )
522 IS
523 --
524 l_api_name CONSTANT VARCHAR2(30) := 'Check_Unique';
525 l_api_version CONSTANT NUMBER := 1.0;
526 --
527 l_tmp VARCHAR2(1);
528
529 CURSOR c IS
530 SELECT '1'
531 FROM psb_pay_element_options
532 WHERE name = p_name
533 AND ( (p_pay_element_option_id IS NULL)
534 OR ( pay_element_option_id <> p_pay_element_option_id) )
535 AND ( (pay_element_id = p_pay_element_id) );
536
537 CURSOR c1 IS
538 SELECT '1'
539 FROM psb_pay_element_options
540 WHERE name = p_name
541 AND grade_step = p_grade_step
542 AND ( (p_pay_element_option_id IS NULL)
543 OR ( pay_element_option_id <> p_pay_element_option_id) )
544 AND ( ( pay_element_id = p_pay_element_id) );
545 BEGIN
546 --
547 SAVEPOINT Check_Unique_Pvt ;
548 --
549 IF NOT FND_API.Compatible_API_Call ( l_api_version,
550 p_api_version,
551 l_api_name,
552 G_PKG_NAME )
553 THEN
554 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
555 END IF;
556 --
557
558 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
559 FND_MSG_PUB.initialize ;
560 END IF;
561 --
562 p_return_status := FND_API.G_RET_STS_SUCCESS ;
563 --
564 -- Checking the Psb_set_relations table for references.
565 IF p_grade_step IS NULL THEN
566 OPEN c;
567 FETCH c INTO l_tmp;
568 CLOSE c;
569 ELSE
570 OPEN c1;
571 FETCH c1 INTO l_tmp;
572 CLOSE c1;
573 END IF;
574 --
575 -- p_Return_Value tells whether references exist or not.
576 IF (l_tmp IS NULL) THEN
577 p_Return_Value := 'FALSE';
578 ELSE
579 p_Return_Value := 'TRUE';
580 END IF;
581 --
582 IF FND_API.To_Boolean ( p_commit ) THEN
583 COMMIT WORK;
584 END iF;
585 --
586 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
587 p_data => p_msg_data );
588 --
589 EXCEPTION
590 --
591 WHEN FND_API.G_EXC_ERROR THEN
592 --
593 ROLLBACK TO Check_Unique_Pvt ;
594 p_return_status := FND_API.G_RET_STS_ERROR;
595 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
596 p_data => p_msg_data );
597 --
598 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
599 --
600 ROLLBACK TO Check_Unique_Pvt ;
601 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
602 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
603 p_data => p_msg_data );
604 --
605 WHEN OTHERS THEN
606 --
607 ROLLBACK TO Check_Unique_Pvt ;
608 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
609 --
610 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
611 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
612 l_api_name);
613 END if;
614 --
615 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
616 p_data => p_msg_data );
617 --
618 END Check_Unique;
619
620
621 PROCEDURE Check_References
622 (
623 p_api_version IN NUMBER,
624 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
625 p_commit IN VARCHAR2 := FND_API.G_FALSE,
626 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
627 p_return_status OUT NOCOPY VARCHAR2,
628 p_msg_count OUT NOCOPY NUMBER,
629 p_msg_data OUT NOCOPY VARCHAR2,
630 --
631 P_PAY_ELEMENT_OPTION_ID IN NUMBER,
632 p_PAY_ELEMENT_ID IN NUMBER,
633 p_Return_Value IN OUT NOCOPY VARCHAR2
634 )
635 IS
636 --
637 l_api_name CONSTANT VARCHAR2(30) := 'Check_References';
638 l_api_version CONSTANT NUMBER := 1.0;
639 --
640 l_tmp VARCHAR2(1);
641 l_tmp1 varchar2(1);
642
643 CURSOR c IS
644 SELECT '1'
645 FROM psb_position_assignments
646 WHERE pay_element_id = p_pay_element_Id
647 OR pay_element_option_id = p_pay_element_option_id;
648
649 CURSOR c1 IS
650 SELECT '1'
651 FROM psb_default_assignments
652 WHERE pay_element_id = p_pay_element_Id
653 OR pay_element_option_id = p_pay_element_option_id;
654
655
656 BEGIN
657 --
658 SAVEPOINT Check_References_Pvt ;
659 --
660 IF NOT FND_API.Compatible_API_Call ( l_api_version,
661 p_api_version,
662 l_api_name,
663 G_PKG_NAME )
664 THEN
665 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
666 END IF;
667 --
668
669 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
670 FND_MSG_PUB.initialize ;
671 END IF;
672 --
673 p_return_status := FND_API.G_RET_STS_SUCCESS ;
674 --
675
676 -- Checking the Psb_set_relations table for references.
677 OPEN c;
678 FETCH c INTO l_tmp;
679
680 OPEN c1;
681 FETCH c1 INTO l_tmp1;
682
683 --
684 -- p_Return_Value tells whether references exist or not.
685 IF ( (l_tmp IS NULL) AND (l_tmp1 IS NULL) ) THEN
686 p_Return_Value := 'FALSE';
687 ELSE
688 p_Return_Value := 'TRUE';
689 END IF;
690
691 CLOSE c;
692 CLOSE c1;
693 --
694 IF FND_API.To_Boolean ( p_commit ) THEN
695 COMMIT WORK;
696 END iF;
697 --
698 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
699 p_data => p_msg_data );
700
701 EXCEPTION
702 --
703 WHEN FND_API.G_EXC_ERROR THEN
704 --
705 ROLLBACK TO Check_References_Pvt ;
706 p_return_status := FND_API.G_RET_STS_ERROR;
707 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
708 p_data => p_msg_data );
709 --
710 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
711 --
712 ROLLBACK TO Check_References_Pvt ;
713 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
714 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
715 p_data => p_msg_data );
716 --
717 WHEN OTHERS THEN
718 --
719 ROLLBACK TO Check_References_Pvt ;
720 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
721 --
722 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
723 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
724 l_api_name);
725 END if;
726 --
727 FND_MSG_PUB.Count_And_Get ( p_count => p_msg_count,
728 p_data => p_msg_data );
729 --
730 END Check_References;
731
732
733
734
735 END PSB_PAY_ELEMENT_OPTIONS_PVT;