[Home] [Help]
PACKAGE BODY: APPS.BEN_BLI_BUS
Source
1 Package Body ben_bli_bus as
2 /* $Header: beblirhi.pkb 115.7 2002/12/10 15:17:02 bmanyam ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_bli_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------------------------------< chk_batch_ler_id >------------------------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- batch_ler_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_batch_ler_id(p_batch_ler_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_batch_ler_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := ben_bli_shd.api_updating
47 (p_batch_ler_id => p_batch_ler_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_batch_ler_id,hr_api.g_number)
52 <> ben_bli_shd.g_old_rec.batch_ler_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_bli_shd.constraint_error('BEN_BATCH_LER_INFO_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_batch_ler_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_bli_shd.constraint_error('BEN_BATCH_LER_INFO_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_batch_ler_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |-------------------------------< chk_ler_id >-----------------------------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure checks that a referenced foreign key actually exists
82 -- in the referenced table.
83 --
84 -- Pre-Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- p_batch_ler_id PK
89 -- p_ler_id ID of FK column
90 -- p_effective_date Session Date of record
91 -- p_object_version_number object version number
92 --
93 -- Post Success
94 -- Processing continues
95 --
96 -- Post Failure
97 -- Error raised.
98 --
99 -- Access Status
100 -- Internal table handler use only.
101 --
102 Procedure chk_ler_id (p_batch_ler_id in number,
103 p_ler_id in number,
104 p_effective_date in date,
105 p_object_version_number in number) is
106 --
107 l_proc varchar2(72) := g_package||'chk_ler_id';
108 l_api_updating boolean;
109 l_dummy varchar2(1);
110 --
111 cursor c1 is
112 select null
113 from ben_ler_f a
114 where a.ler_id = p_ler_id
115 and p_effective_date
116 between a.effective_start_date
117 and a.effective_end_date;
118 --
119 Begin
120 --
121 hr_utility.set_location('Entering:'||l_proc,5);
122 --
123 l_api_updating := ben_bli_shd.api_updating
124 (p_batch_ler_id => p_batch_ler_id,
125 p_object_version_number => p_object_version_number);
126 --
127 if (l_api_updating
128 and nvl(p_ler_id,hr_api.g_number)
129 <> nvl(ben_bli_shd.g_old_rec.ler_id,hr_api.g_number)
130 or not l_api_updating) then
131 --
132 -- check if ler_id value exists in ben_ler_f table
133 --
134 open c1;
135 --
136 fetch c1 into l_dummy;
137 if c1%notfound then
138 --
139 close c1;
140 --
141 -- raise error as FK does not relate to PK in ben_ler_f
142 -- table.
143 --
144 ben_bli_shd.constraint_error('BEN_BATCH_LER_INFO_DT2');
145 --
146 end if;
147 --
148 close c1;
149 --
150 end if;
151 --
152 hr_utility.set_location('Leaving:'||l_proc,10);
153 --
154 End chk_ler_id;
155 --
156 -- ----------------------------------------------------------------------------
157 -- |---------------------------< chk_person_id >------------------------------|
158 -- ----------------------------------------------------------------------------
159 --
160 -- Description
161 -- This procedure checks that a referenced foreign key actually exists
162 -- in the referenced table.
163 --
164 -- Pre-Conditions
165 -- None.
166 --
167 -- In Parameters
168 -- p_batch_ler_id PK
169 -- p_person_id ID of FK column
170 -- p_effective_date Session Date of record
171 -- p_object_version_number object version number
172 --
173 -- Post Success
174 -- Processing continues
175 --
176 -- Post Failure
177 -- Error raised.
178 --
179 -- Access Status
180 -- Internal table handler use only.
181 --
182 Procedure chk_person_id (p_batch_ler_id in number,
183 p_person_id in number,
184 p_effective_date in date,
185 p_object_version_number in number) is
186 --
187 l_proc varchar2(72) := g_package||'chk_person_id';
188 l_api_updating boolean;
189 l_dummy varchar2(1);
190 --
191 cursor c1 is
192 select null
193 from per_all_people_f a
194 where a.person_id = p_person_id
195 and p_effective_date
196 between a.effective_start_date
197 and a.effective_end_date;
198 --
199 Begin
200 --
201 hr_utility.set_location('Entering:'||l_proc,5);
202 --
203 l_api_updating := ben_bli_shd.api_updating
204 (p_batch_ler_id => p_batch_ler_id,
205 p_object_version_number => p_object_version_number);
206 --
207 if (l_api_updating
208 and nvl(p_person_id,hr_api.g_number)
209 <> nvl(ben_bli_shd.g_old_rec.person_id,hr_api.g_number)
210 or not l_api_updating) then
211 --
212 -- check if person_id value exists in per_all_people_f table
213 --
214 open c1;
215 --
216 fetch c1 into l_dummy;
217 if c1%notfound then
218 --
219 close c1;
220 --
221 -- raise error as FK does not relate to PK in per_all_people_f
222 -- table.
223 --
224 ben_bli_shd.constraint_error('BEN_BATCH_LER_INFO_DT1');
225 --
226 end if;
227 --
228 close c1;
229 --
230 end if;
231 --
232 hr_utility.set_location('Leaving:'||l_proc,10);
233 --
234 End chk_person_id;
235 --
236 -- ----------------------------------------------------------------------------
237 -- |-------------------------< chk_benefit_action_id >------------------------|
238 -- ----------------------------------------------------------------------------
239 --
240 -- Description
241 -- This procedure checks that a referenced foreign key actually exists
242 -- in the referenced table.
243 --
244 -- Pre-Conditions
245 -- None.
246 --
247 -- In Parameters
248 -- p_batch_ler_id PK
249 -- p_benefit_action_id ID of FK column
250 -- p_object_version_number object version number
251 --
252 -- Post Success
253 -- Processing continues
254 --
255 -- Post Failure
256 -- Error raised.
257 --
258 -- Access Status
259 -- Internal table handler use only.
260 --
261 Procedure chk_benefit_action_id (p_batch_ler_id in number,
262 p_benefit_action_id in number,
263 p_object_version_number in number) is
264 --
265 l_proc varchar2(72) := g_package||'chk_benefit_action_id';
266 l_api_updating boolean;
267 l_dummy varchar2(1);
268 --
269 cursor c1 is
270 select null
271 from ben_benefit_actions a
272 where a.benefit_action_id = p_benefit_action_id;
273 --
274 Begin
275 --
276 hr_utility.set_location('Entering:'||l_proc,5);
277 --
278 l_api_updating := ben_bli_shd.api_updating
279 (p_batch_ler_id => p_batch_ler_id,
280 p_object_version_number => p_object_version_number);
281 --
282 if (l_api_updating
283 and nvl(p_benefit_action_id,hr_api.g_number)
284 <> nvl(ben_bli_shd.g_old_rec.benefit_action_id,hr_api.g_number)
285 or not l_api_updating) then
286 --
287 -- check if benefit_action_id value exists in ben_benefit_actions table
288 --
289 open c1;
290 --
291 fetch c1 into l_dummy;
292 if c1%notfound then
293 --
294 close c1;
295 --
296 -- raise error as FK does not relate to PK in ben_benefit_actions
297 -- table.
298 --
299 ben_bli_shd.constraint_error('BEN_BATCH_LER_INFO_FK1');
300 --
301 end if;
302 --
303 close c1;
304 --
305 end if;
306 --
307 hr_utility.set_location('Leaving:'||l_proc,10);
308 --
309 End chk_benefit_action_id;
310 --
311 -- ----------------------------------------------------------------------------
312 -- |---------------------------< chk_dltd_flag >------------------------------|
313 -- ----------------------------------------------------------------------------
314 --
315 -- Description
316 -- This procedure is used to check that the lookup value is valid.
317 --
318 -- Pre Conditions
319 -- None.
320 --
321 -- In Parameters
322 -- batch_ler_id PK of record being inserted or updated.
323 -- dltd_flag Value of lookup code.
324 -- effective_date effective date
325 -- object_version_number Object version number of record being
326 -- inserted or updated.
327 --
328 -- Post Success
329 -- Processing continues
330 --
331 -- Post Failure
332 -- Error handled by procedure
333 --
334 -- Access Status
335 -- Internal table handler use only.
336 --
337 Procedure chk_dltd_flag(p_batch_ler_id in number,
338 p_dltd_flag in varchar2,
339 p_effective_date in date,
340 p_object_version_number in number) is
341 --
342 l_proc varchar2(72) := g_package||'chk_dltd_flag';
343 l_api_updating boolean;
344 --
345 Begin
346 --
347 hr_utility.set_location('Entering:'||l_proc, 5);
348 --
349 l_api_updating := ben_bli_shd.api_updating
350 (p_batch_ler_id => p_batch_ler_id,
351 p_object_version_number => p_object_version_number);
352 --
353 if (l_api_updating
354 and p_dltd_flag
355 <> nvl(ben_bli_shd.g_old_rec.dltd_flag,hr_api.g_varchar2)
356 or not l_api_updating) then
357 --
358 -- check if value of lookup falls within lookup type.
359 --
360 --
361 if hr_api.not_exists_in_hr_lookups
362 (p_lookup_type => 'YES_NO',
363 p_lookup_code => p_dltd_flag,
364 p_effective_date => p_effective_date) then
365 --
366 -- raise error as does not exist as lookup
367 --
368 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
369 fnd_message.set_token('FIELD','p_dltd_flag');
370 fnd_message.set_token('TYPE','YES_NO');
371 fnd_message.raise_error;
372 --
373 end if;
374 --
375 end if;
376 --
377 hr_utility.set_location('Leaving:'||l_proc,10);
378 --
379 end chk_dltd_flag;
380 --
381 -- ----------------------------------------------------------------------------
382 -- |---------------------------< chk_tmprl_flag >-----------------------------|
383 -- ----------------------------------------------------------------------------
384 --
385 -- Description
386 -- This procedure is used to check that the lookup value is valid.
387 --
388 -- Pre Conditions
389 -- None.
390 --
391 -- In Parameters
392 -- batch_ler_id PK of record being inserted or updated.
393 -- tmprl_flag Value of lookup code.
394 -- effective_date effective date
395 -- object_version_number Object version number of record being
396 -- inserted or updated.
397 --
398 -- Post Success
399 -- Processing continues
400 --
401 -- Post Failure
402 -- Error handled by procedure
403 --
404 -- Access Status
405 -- Internal table handler use only.
406 --
407 Procedure chk_tmprl_flag(p_batch_ler_id in number,
408 p_tmprl_flag in varchar2,
409 p_effective_date in date,
410 p_object_version_number in number) is
411 --
412 l_proc varchar2(72) := g_package||'chk_tmprl_flag';
413 l_api_updating boolean;
414 --
415 Begin
416 --
417 hr_utility.set_location('Entering:'||l_proc, 5);
418 --
419 l_api_updating := ben_bli_shd.api_updating
420 (p_batch_ler_id => p_batch_ler_id,
421 p_object_version_number => p_object_version_number);
422 --
423 if (l_api_updating
424 and p_tmprl_flag
425 <> nvl(ben_bli_shd.g_old_rec.tmprl_flag,hr_api.g_varchar2)
426 or not l_api_updating) then
427 --
428 -- check if value of lookup falls within lookup type.
429 --
430 --
431 if hr_api.not_exists_in_hr_lookups
432 (p_lookup_type => 'YES_NO',
433 p_lookup_code => p_tmprl_flag,
434 p_effective_date => p_effective_date) then
435 --
436 -- raise error as does not exist as lookup
437 --
438 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
439 fnd_message.set_token('FIELD','p_tmprl_flag');
440 fnd_message.set_token('TYPE','YES_NO');
441 fnd_message.raise_error;
442 --
443 end if;
444 --
445 end if;
446 --
447 hr_utility.set_location('Leaving:'||l_proc,10);
448 --
449 end chk_tmprl_flag;
450 --
451 -- ----------------------------------------------------------------------------
452 -- |--------------------------< chk_crtd_flag >-------------------------------|
453 -- ----------------------------------------------------------------------------
454 --
455 -- Description
456 -- This procedure is used to check that the lookup value is valid.
457 --
458 -- Pre Conditions
459 -- None.
460 --
461 -- In Parameters
462 -- batch_ler_id PK of record being inserted or updated.
463 -- crtd_flag Value of lookup code.
464 -- effective_date effective date
468 -- Post Success
465 -- object_version_number Object version number of record being
466 -- inserted or updated.
467 --
469 -- Processing continues
470 --
471 -- Post Failure
472 -- Error handled by procedure
473 --
474 -- Access Status
475 -- Internal table handler use only.
476 --
477 Procedure chk_crtd_flag(p_batch_ler_id in number,
478 p_crtd_flag in varchar2,
479 p_effective_date in date,
480 p_object_version_number in number) is
481 --
482 l_proc varchar2(72) := g_package||'chk_crtd_flag';
483 l_api_updating boolean;
484 --
485 Begin
486 --
487 hr_utility.set_location('Entering:'||l_proc, 5);
488 --
489 l_api_updating := ben_bli_shd.api_updating
490 (p_batch_ler_id => p_batch_ler_id,
491 p_object_version_number => p_object_version_number);
492 --
493 if (l_api_updating
494 and p_crtd_flag
495 <> nvl(ben_bli_shd.g_old_rec.crtd_flag,hr_api.g_varchar2)
496 or not l_api_updating) then
497 --
498 -- check if value of lookup falls within lookup type.
499 --
500 --
501 if hr_api.not_exists_in_hr_lookups
502 (p_lookup_type => 'YES_NO',
503 p_lookup_code => p_crtd_flag,
504 p_effective_date => p_effective_date) then
505 --
506 -- raise error as does not exist as lookup
507 --
508 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
509 fnd_message.set_token('FIELD','p_crtd_flag');
510 fnd_message.set_token('TYPE','YES_NO');
511 fnd_message.raise_error;
512 --
513 end if;
514 --
515 end if;
516 --
517 hr_utility.set_location('Leaving:'||l_proc,10);
518 --
519 end chk_crtd_flag;
520 --
521 -- ----------------------------------------------------------------------------
522 -- |--------------------------< chk_clpsd_flag >------------------------------|
523 -- ----------------------------------------------------------------------------
524 --
525 -- Description
526 -- This procedure is used to check that the lookup value is valid.
527 --
528 -- Pre Conditions
529 -- None.
530 --
531 -- In Parameters
532 -- batch_ler_id PK of record being inserted or updated.
533 -- clpsd_flag Value of lookup code.
534 -- effective_date effective date
535 -- object_version_number Object version number of record being
536 -- inserted or updated.
537 --
538 -- Post Success
539 -- Processing continues
540 --
541 -- Post Failure
542 -- Error handled by procedure
543 --
544 -- Access Status
545 -- Internal table handler use only.
546 --
547 Procedure chk_clpsd_flag(p_batch_ler_id in number,
548 p_clpsd_flag in varchar2,
549 p_effective_date in date,
550 p_object_version_number in number) is
551 --
552 l_proc varchar2(72) := g_package||'chk_clpsd_flag';
553 l_api_updating boolean;
554 --
555 Begin
556 --
557 hr_utility.set_location('Entering:'||l_proc, 5);
558 --
559 l_api_updating := ben_bli_shd.api_updating
560 (p_batch_ler_id => p_batch_ler_id,
561 p_object_version_number => p_object_version_number);
562 --
563 if (l_api_updating
564 and p_clpsd_flag
565 <> nvl(ben_bli_shd.g_old_rec.clpsd_flag,hr_api.g_varchar2)
566 or not l_api_updating) then
567 --
568 -- check if value of lookup falls within lookup type.
569 --
570 --
571 if hr_api.not_exists_in_hr_lookups
572 (p_lookup_type => 'YES_NO',
573 p_lookup_code => p_clpsd_flag,
574 p_effective_date => p_effective_date) then
575 --
576 -- raise error as does not exist as lookup
577 --
578 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
579 fnd_message.set_token('FIELD','p_clpsd_flag');
580 fnd_message.set_token('TYPE','YES_NO');
581 fnd_message.raise_error;
582 --
583 end if;
584 --
585 end if;
586 --
587 hr_utility.set_location('Leaving:'||l_proc,10);
588 --
589 end chk_clpsd_flag;
590 -- ----------------------------------------------------------------------------
591 -- |--------------------------< chk_clsn_flag >-------------------------------|
592 -- ----------------------------------------------------------------------------
593 --
594 -- Description
595 -- This procedure is used to check that the lookup value is valid.
596 --
597 -- Pre Conditions
598 -- None.
599 --
600 -- In Parameters
601 -- batch_ler_id PK of record being inserted or updated.
602 -- clsn_flag Value of lookup code.
603 -- effective_date effective date
604 -- object_version_number Object version number of record being
605 -- inserted or updated.
606 --
607 -- Post Success
608 -- Processing continues
609 --
610 -- Post Failure
614 -- Internal table handler use only.
611 -- Error handled by procedure
612 --
613 -- Access Status
615 --
616 Procedure chk_clsn_flag(p_batch_ler_id in number,
617 p_clsn_flag in varchar2,
618 p_effective_date in date,
619 p_object_version_number in number) is
620 --
621 l_proc varchar2(72) := g_package||'chk_crtd_flag';
622 l_api_updating boolean;
623 --
624 Begin
625 --
626 hr_utility.set_location('Entering:'||l_proc, 5);
627 --
628 l_api_updating := ben_bli_shd.api_updating
629 (p_batch_ler_id => p_batch_ler_id,
630 p_object_version_number => p_object_version_number);
631 --
632 if (l_api_updating
633 and p_clsn_flag
634 <> nvl(ben_bli_shd.g_old_rec.clsn_flag,hr_api.g_varchar2)
635 or not l_api_updating) then
636 --
637 -- check if value of lookup falls within lookup type.
638 --
639 --
640 if hr_api.not_exists_in_hr_lookups
641 (p_lookup_type => 'YES_NO',
642 p_lookup_code => p_clsn_flag,
643 p_effective_date => p_effective_date) then
644 --
645 -- raise error as does not exist as lookup
646 --
647 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
648 fnd_message.set_token('FIELD','p_clsn_flag');
649 fnd_message.set_token('TYPE','YES_NO');
650 fnd_message.raise_error;
651 --
652 end if;
653 --
654 end if;
655 --
656 hr_utility.set_location('Leaving:'||l_proc,10);
657 --
658 end chk_clsn_flag;
659 -- ----------------------------------------------------------------------------
660 -- |--------------------------< chk_no_effect_flag >--------------------------|
661 -- ----------------------------------------------------------------------------
662 --
663 -- Description
664 -- This procedure is used to check that the lookup value is valid.
665 --
666 -- Pre Conditions
667 -- None.
668 --
669 -- In Parameters
670 -- batch_ler_id PK of record being inserted or updated.
671 -- no_effect_flag Value of lookup code.
672 -- effective_date effective date
673 -- object_version_number Object version number of record being
674 -- inserted or updated.
675 --
676 -- Post Success
677 -- Processing continues
678 --
679 -- Post Failure
680 -- Error handled by procedure
681 --
682 -- Access Status
683 -- Internal table handler use only.
684 --
685 Procedure chk_no_effect_flag(p_batch_ler_id in number,
686 p_no_effect_flag in varchar2,
687 p_effective_date in date,
688 p_object_version_number in number) is
689 --
690 l_proc varchar2(72) := g_package||'chk_no_effect_flag';
691 l_api_updating boolean;
692 --
693 Begin
694 --
695 hr_utility.set_location('Entering:'||l_proc, 5);
696 --
697 l_api_updating := ben_bli_shd.api_updating
698 (p_batch_ler_id => p_batch_ler_id,
699 p_object_version_number => p_object_version_number);
700 --
701 if (l_api_updating
702 and p_no_effect_flag
703 <> nvl(ben_bli_shd.g_old_rec.no_effect_flag,hr_api.g_varchar2)
704 or not l_api_updating) then
705 --
706 -- check if value of lookup falls within lookup type.
707 --
708 --
709 if hr_api.not_exists_in_hr_lookups
710 (p_lookup_type => 'YES_NO',
711 p_lookup_code => p_no_effect_flag,
712 p_effective_date => p_effective_date) then
713 --
714 -- raise error as does not exist as lookup
715 --
716 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
717 fnd_message.set_token('FIELD','p_no_effect_flag');
718 fnd_message.set_token('TYPE','YES_NO');
719 fnd_message.raise_error;
720 --
721 end if;
722 --
723 end if;
724 --
725 hr_utility.set_location('Leaving:'||l_proc,10);
726 --
727 end chk_no_effect_flag;
728 -- ----------------------------------------------------------------------------
729 -- |--------------------------< chk_cvrge_rt_prem_flag >----------------------|
730 -- ----------------------------------------------------------------------------
731 --
732 -- Description
733 -- This procedure is used to check that the lookup value is valid.
734 --
735 -- Pre Conditions
736 -- None.
737 --
738 -- In Parameters
739 -- batch_ler_id PK of record being inserted or updated.
740 -- cvrge_rt_prem_flag Value of lookup code.
741 -- effective_date effective date
742 -- object_version_number Object version number of record being
743 -- inserted or updated.
744 --
745 -- Post Success
746 -- Processing continues
747 --
748 -- Post Failure
749 -- Error handled by procedure
750 --
751 -- Access Status
752 -- Internal table handler use only.
753 --
754 Procedure chk_cvrge_rt_prem_flag(p_batch_ler_id in number,
758 --
755 p_cvrge_rt_prem_flag in varchar2,
756 p_effective_date in date,
757 p_object_version_number in number) is
759 l_proc varchar2(72) := g_package||'chk_cvrge_rt_prem_flag';
760 l_api_updating boolean;
761 --
762 Begin
763 --
764 hr_utility.set_location('Entering:'||l_proc, 5);
765 --
766 l_api_updating := ben_bli_shd.api_updating
767 (p_batch_ler_id => p_batch_ler_id,
768 p_object_version_number => p_object_version_number);
769 --
770 if (l_api_updating
771 and p_cvrge_rt_prem_flag
772 <> nvl(ben_bli_shd.g_old_rec.cvrge_rt_prem_flag,hr_api.g_varchar2)
773 or not l_api_updating) then
774 --
775 -- check if value of lookup falls within lookup type.
776 --
777 --
778 if hr_api.not_exists_in_hr_lookups
779 (p_lookup_type => 'YES_NO',
780 p_lookup_code => p_cvrge_rt_prem_flag,
781 p_effective_date => p_effective_date) then
782 --
783 -- raise error as does not exist as lookup
784 --
785 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
786 fnd_message.set_token('FIELD','p_cvrge_rt_prem_flag');
787 fnd_message.set_token('TYPE','YES_NO');
788 fnd_message.raise_error;
789 --
790 end if;
791 --
792 end if;
793 --
794 hr_utility.set_location('Leaving:'||l_proc,10);
795 --
796 end chk_cvrge_rt_prem_flag;
797 -- ----------------------------------------------------------------------------
798 -- |----------------------------< chk_replcd_flag >---------------------------|
799 -- ----------------------------------------------------------------------------
800 --
801 -- Description
802 -- This procedure is used to check that the lookup value is valid.
803 --
804 -- Pre Conditions
805 -- None.
806 --
807 -- In Parameters
808 -- batch_ler_id PK of record being inserted or updated.
809 -- replcd_flag Value of lookup code.
810 -- effective_date effective date
811 -- object_version_number Object version number of record being
812 -- inserted or updated.
813 --
814 -- Post Success
815 -- Processing continues
816 --
817 -- Post Failure
818 -- Error handled by procedure
819 --
820 -- Access Status
821 -- Internal table handler use only.
822 --
823 Procedure chk_replcd_flag(p_batch_ler_id in number,
824 p_replcd_flag in varchar2,
825 p_effective_date in date,
826 p_object_version_number in number) is
827 --
828 l_proc varchar2(72) := g_package||'chk_replcd_flag';
829 l_api_updating boolean;
830 --
831 Begin
832 --
833 hr_utility.set_location('Entering:'||l_proc, 5);
834 --
835 l_api_updating := ben_bli_shd.api_updating
836 (p_batch_ler_id => p_batch_ler_id,
837 p_object_version_number => p_object_version_number);
838 --
839 if (l_api_updating
840 and p_replcd_flag
841 <> nvl(ben_bli_shd.g_old_rec.replcd_flag,hr_api.g_varchar2)
842 or not l_api_updating) then
843 --
844 -- check if value of lookup falls within lookup type.
845 --
846 --
847 if hr_api.not_exists_in_hr_lookups
848 (p_lookup_type => 'YES_NO',
849 p_lookup_code => p_replcd_flag,
850 p_effective_date => p_effective_date) then
851 --
852 -- raise error as does not exist as lookup
853 --
854 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
855 fnd_message.set_token('FIELD','p_replcd_flag');
856 fnd_message.set_token('TYPE','YES_NO');
857 fnd_message.raise_error;
858 --
859 end if;
860 --
861 end if;
862 --
863 hr_utility.set_location('Leaving:'||l_proc,10);
864 --
865 end chk_replcd_flag;
866 --
867 -- ----------------------------------------------------------------------------
868 -- |----------------------------< chk_open_and_clsd_flag >--------------------|
869 -- ----------------------------------------------------------------------------
870 --
871 -- Description
872 -- This procedure is used to check that the lookup value is valid.
873 --
874 -- Pre Conditions
875 -- None.
876 --
877 -- In Parameters
878 -- batch_ler_id PK of record being inserted or updated.
879 -- open_and_clsd_flag Value of lookup code.
880 -- effective_date effective date
881 -- object_version_number Object version number of record being
882 -- inserted or updated.
883 --
884 -- Post Success
885 -- Processing continues
886 --
887 -- Post Failure
888 -- Error handled by procedure
889 --
890 -- Access Status
891 -- Internal table handler use only.
892 --
893 Procedure chk_open_and_clsd_flag(p_batch_ler_id in number,
894 p_open_and_clsd_flag in varchar2,
895 p_effective_date in date,
899 l_api_updating boolean;
896 p_object_version_number in number) is
897 --
898 l_proc varchar2(72) := g_package||'chk_open_and_clsd_flag';
900 --
901 Begin
902 --
903 hr_utility.set_location('Entering:'||l_proc, 5);
904 --
905 l_api_updating := ben_bli_shd.api_updating
906 (p_batch_ler_id => p_batch_ler_id,
907 p_object_version_number => p_object_version_number);
908 --
909 if (l_api_updating
910 and p_open_and_clsd_flag
911 <> nvl(ben_bli_shd.g_old_rec.open_and_clsd_flag,hr_api.g_varchar2)
912 or not l_api_updating) then
913 --
914 -- check if value of lookup falls within lookup type.
915 --
916 --
917 if hr_api.not_exists_in_hr_lookups
918 (p_lookup_type => 'YES_NO',
919 p_lookup_code => p_open_and_clsd_flag,
920 p_effective_date => p_effective_date) then
921 --
922 -- raise error as does not exist as lookup
923 --
924 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
925 fnd_message.set_token('FIELD','p_open_and_clsd_flag');
926 fnd_message.set_token('TYPE','YES_NO');
927 fnd_message.raise_error;
928 --
929 end if;
930 --
931 end if;
932 --
933 hr_utility.set_location('Leaving:'||l_proc,10);
934 --
935 end chk_open_and_clsd_flag;
936 -- ----------------------------------------------------------------------------
937 -- |----------------------------< chk_clsd_flag >-----------------------------|
938 -- ----------------------------------------------------------------------------
939 --
940 -- Description
941 -- This procedure is used to check that the lookup value is valid.
942 --
943 -- Pre Conditions
944 -- None.
945 --
946 -- In Parameters
947 -- batch_ler_id PK of record being inserted or updated.
948 -- clsd_flag Value of lookup code.
949 -- effective_date effective date
950 -- object_version_number Object version number of record being
951 -- inserted or updated.
952 --
953 -- Post Success
954 -- Processing continues
955 --
956 -- Post Failure
957 -- Error handled by procedure
958 --
959 -- Access Status
960 -- Internal table handler use only.
961 --
962 Procedure chk_clsd_flag(p_batch_ler_id in number,
963 p_clsd_flag in varchar2,
964 p_effective_date in date,
965 p_object_version_number in number) is
966 --
967 l_proc varchar2(72) := g_package||'chk_clsd_flag';
968 l_api_updating boolean;
969 --
970 Begin
971 --
972 hr_utility.set_location('Entering:'||l_proc, 5);
973 --
974 l_api_updating := ben_bli_shd.api_updating
975 (p_batch_ler_id => p_batch_ler_id,
976 p_object_version_number => p_object_version_number);
977 --
978 if (l_api_updating
979 and p_clsd_flag
980 <> nvl(ben_bli_shd.g_old_rec.clsd_flag,hr_api.g_varchar2)
981 or not l_api_updating) then
982 --
983 -- check if value of lookup falls within lookup type.
984 --
985 --
986 if hr_api.not_exists_in_hr_lookups
987 (p_lookup_type => 'YES_NO',
988 p_lookup_code => p_clsd_flag,
989 p_effective_date => p_effective_date) then
990 --
991 -- raise error as does not exist as lookup
992 --
993 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
994 fnd_message.set_token('FIELD','p_clsd_flag');
995 fnd_message.set_token('TYPE','YES_NO');
996 fnd_message.raise_error;
997 --
998 end if;
999 --
1000 end if;
1001 --
1002 hr_utility.set_location('Leaving:'||l_proc,10);
1003 --
1004 end chk_clsd_flag;
1005 -- ----------------------------------------------------------------------------
1006 -- |----------------------------< chk_not_crtd_flag >-------------------------|
1007 -- ----------------------------------------------------------------------------
1008 --
1009 -- Description
1010 -- This procedure is used to check that the lookup value is valid.
1011 --
1012 -- Pre Conditions
1013 -- None.
1014 --
1015 -- In Parameters
1016 -- batch_ler_id PK of record being inserted or updated.
1017 -- not_crtd_flag Value of lookup code.
1018 -- effective_date effective date
1019 -- object_version_number Object version number of record being
1020 -- inserted or updated.
1021 --
1022 -- Post Success
1023 -- Processing continues
1024 --
1025 -- Post Failure
1026 -- Error handled by procedure
1027 --
1028 -- Access Status
1029 -- Internal table handler use only.
1030 --
1031 Procedure chk_not_crtd_flag(p_batch_ler_id in number,
1032 p_not_crtd_flag in varchar2,
1033 p_effective_date in date,
1034 p_object_version_number in number) is
1035 --
1036 l_proc varchar2(72) := g_package||'chk_not_crtd_flag';
1037 l_api_updating boolean;
1038 --
1039 Begin
1040 --
1041 hr_utility.set_location('Entering:'||l_proc, 5);
1042 --
1043 l_api_updating := ben_bli_shd.api_updating
1047 if (l_api_updating
1044 (p_batch_ler_id => p_batch_ler_id,
1045 p_object_version_number => p_object_version_number);
1046 --
1048 and p_not_crtd_flag
1049 <> nvl(ben_bli_shd.g_old_rec.not_crtd_flag,hr_api.g_varchar2)
1050 or not l_api_updating) then
1051 --
1052 -- check if value of lookup falls within lookup type.
1053 --
1054 --
1055 if hr_api.not_exists_in_hr_lookups
1056 (p_lookup_type => 'YES_NO',
1057 p_lookup_code => p_not_crtd_flag,
1058 p_effective_date => p_effective_date) then
1059 --
1060 -- raise error as does not exist as lookup
1061 --
1062 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1063 fnd_message.set_token('FIELD','p_not_crtd_flag');
1064 fnd_message.set_token('TYPE','YES_NO');
1065 fnd_message.raise_error;
1066 --
1067 end if;
1068 --
1069 end if;
1070 --
1071 hr_utility.set_location('Leaving:'||l_proc,10);
1072 --
1073 end chk_not_crtd_flag;
1074 -- ----------------------------------------------------------------------------
1075 -- |----------------------------< chk_stl_actv_flag >-------------------------|
1076 -- ----------------------------------------------------------------------------
1077 --
1078 -- Description
1079 -- This procedure is used to check that the lookup value is valid.
1080 --
1081 -- Pre Conditions
1082 -- None.
1083 --
1084 -- In Parameters
1085 -- batch_ler_id PK of record being inserted or updated.
1086 -- stl_actv_flag Value of lookup code.
1087 -- effective_date effective date
1088 -- object_version_number Object version number of record being
1089 -- inserted or updated.
1090 --
1091 -- Post Success
1092 -- Processing continues
1093 --
1094 -- Post Failure
1095 -- Error handled by procedure
1096 --
1097 -- Access Status
1098 -- Internal table handler use only.
1099 --
1100 Procedure chk_stl_actv_flag(p_batch_ler_id in number,
1101 p_stl_actv_flag in varchar2,
1102 p_effective_date in date,
1103 p_object_version_number in number) is
1104 --
1105 l_proc varchar2(72) := g_package||'chk_stl_actv_flag';
1106 l_api_updating boolean;
1107 --
1108 Begin
1109 --
1110 hr_utility.set_location('Entering:'||l_proc, 5);
1111 --
1112 l_api_updating := ben_bli_shd.api_updating
1113 (p_batch_ler_id => p_batch_ler_id,
1114 p_object_version_number => p_object_version_number);
1115 --
1116 if (l_api_updating
1117 and p_stl_actv_flag
1118 <> nvl(ben_bli_shd.g_old_rec.stl_actv_flag,hr_api.g_varchar2)
1119 or not l_api_updating) then
1120 --
1121 -- check if value of lookup falls within lookup type.
1122 --
1123 --
1124 if hr_api.not_exists_in_hr_lookups
1125 (p_lookup_type => 'YES_NO',
1126 p_lookup_code => p_stl_actv_flag,
1127 p_effective_date => p_effective_date) then
1128 --
1129 -- raise error as does not exist as lookup
1130 --
1131 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1132 fnd_message.set_token('FIELD','p_stl_actv_flag');
1133 fnd_message.set_token('TYPE','YES_NO');
1134 fnd_message.raise_error;
1135 --
1136 end if;
1137 --
1138 end if;
1139 --
1140 hr_utility.set_location('Leaving:'||l_proc,10);
1141 --
1142 end chk_stl_actv_flag;
1143 -- ----------------------------------------------------------------------------
1144 -- |---------------------------< insert_validate >----------------------------|
1145 -- ----------------------------------------------------------------------------
1146 Procedure insert_validate(p_rec in ben_bli_shd.g_rec_type
1147 ,p_effective_date in date) is
1148 --
1149 l_proc varchar2(72) := g_package||'insert_validate';
1150 --
1151 Begin
1152 hr_utility.set_location('Entering:'||l_proc, 5);
1153 --
1154 -- Call all supporting business operations
1155 --
1156 --
1157 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1158 --
1159 chk_batch_ler_id
1160 (p_batch_ler_id => p_rec.batch_ler_id,
1161 p_object_version_number => p_rec.object_version_number);
1162 --
1163 chk_ler_id
1164 (p_batch_ler_id => p_rec.batch_ler_id,
1165 p_ler_id => p_rec.ler_id,
1166 p_effective_date => p_effective_date,
1167 p_object_version_number => p_rec.object_version_number);
1168 --
1169 chk_person_id
1170 (p_batch_ler_id => p_rec.batch_ler_id,
1171 p_person_id => p_rec.person_id,
1172 p_effective_date => p_effective_date,
1173 p_object_version_number => p_rec.object_version_number);
1174 --
1175 chk_benefit_action_id
1176 (p_batch_ler_id => p_rec.batch_ler_id,
1177 p_benefit_action_id => p_rec.benefit_action_id,
1181 (p_batch_ler_id => p_rec.batch_ler_id,
1178 p_object_version_number => p_rec.object_version_number);
1179 --
1180 chk_dltd_flag
1182 p_dltd_flag => p_rec.dltd_flag,
1183 p_effective_date => p_effective_date,
1184 p_object_version_number => p_rec.object_version_number);
1185 --
1186 chk_tmprl_flag
1187 (p_batch_ler_id => p_rec.batch_ler_id,
1188 p_tmprl_flag => p_rec.tmprl_flag,
1189 p_effective_date => p_effective_date,
1190 p_object_version_number => p_rec.object_version_number);
1191 --
1192 chk_crtd_flag
1193 (p_batch_ler_id => p_rec.batch_ler_id,
1194 p_crtd_flag => p_rec.crtd_flag,
1195 p_effective_date => p_effective_date,
1196 p_object_version_number => p_rec.object_version_number);
1197 --
1198 chk_replcd_flag
1199 (p_batch_ler_id => p_rec.batch_ler_id,
1200 p_replcd_flag => p_rec.replcd_flag,
1201 p_effective_date => p_effective_date,
1202 p_object_version_number => p_rec.object_version_number);
1203 --
1204 chk_open_and_clsd_flag
1205 (p_batch_ler_id => p_rec.batch_ler_id,
1206 p_open_and_clsd_flag => p_rec.open_and_clsd_flag,
1207 p_effective_date => p_effective_date,
1208 p_object_version_number => p_rec.object_version_number);
1209 --
1210 chk_clsd_flag
1211 (p_batch_ler_id => p_rec.batch_ler_id,
1212 p_clsd_flag => p_rec.clsd_flag,
1213 p_effective_date => p_effective_date,
1214 p_object_version_number => p_rec.object_version_number);
1215 --
1216 chk_not_crtd_flag
1217 (p_batch_ler_id => p_rec.batch_ler_id,
1218 p_not_crtd_flag => p_rec.not_crtd_flag,
1219 p_effective_date => p_effective_date,
1220 p_object_version_number => p_rec.object_version_number);
1221 --
1222 chk_stl_actv_flag
1223 (p_batch_ler_id => p_rec.batch_ler_id,
1224 p_stl_actv_flag => p_rec.stl_actv_flag,
1225 p_effective_date => p_effective_date,
1226 p_object_version_number => p_rec.object_version_number);
1227 --
1228 chk_clpsd_flag
1229 (p_batch_ler_id => p_rec.batch_ler_id,
1230 p_clpsd_flag => p_rec.clpsd_flag,
1231 p_effective_date => p_effective_date,
1232 p_object_version_number => p_rec.object_version_number);
1233 --
1234 chk_clsn_flag
1235 (p_batch_ler_id => p_rec.batch_ler_id,
1236 p_clsn_flag => p_rec.clsn_flag,
1237 p_effective_date => p_effective_date,
1238 p_object_version_number => p_rec.object_version_number);
1239 --
1240 chk_no_effect_flag
1241 (p_batch_ler_id => p_rec.batch_ler_id,
1242 p_no_effect_flag => p_rec.no_effect_flag,
1243 p_effective_date => p_effective_date,
1244 p_object_version_number => p_rec.object_version_number);
1245 --
1246 chk_cvrge_rt_prem_flag
1247 (p_batch_ler_id => p_rec.batch_ler_id,
1248 p_cvrge_rt_prem_flag => p_rec.cvrge_rt_prem_flag,
1249 p_effective_date => p_effective_date,
1250 p_object_version_number => p_rec.object_version_number);
1251 --
1252 hr_utility.set_location(' Leaving:'||l_proc, 10);
1253 End insert_validate;
1254 --
1255 -- ----------------------------------------------------------------------------
1256 -- |---------------------------< update_validate >----------------------------|
1257 -- ----------------------------------------------------------------------------
1258 Procedure update_validate(p_rec in ben_bli_shd.g_rec_type
1259 ,p_effective_date in date) is
1260 --
1261 l_proc varchar2(72) := g_package||'update_validate';
1262 --
1263 Begin
1264 hr_utility.set_location('Entering:'||l_proc, 5);
1265 --
1266 -- Call all supporting business operations
1267 --
1268 --
1269 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1270 --
1271 chk_batch_ler_id
1272 (p_batch_ler_id => p_rec.batch_ler_id,
1273 p_object_version_number => p_rec.object_version_number);
1274 --
1275 chk_ler_id
1276 (p_batch_ler_id => p_rec.batch_ler_id,
1277 p_ler_id => p_rec.ler_id,
1278 p_effective_date => p_effective_date,
1279 p_object_version_number => p_rec.object_version_number);
1280 --
1281 chk_person_id
1282 (p_batch_ler_id => p_rec.batch_ler_id,
1283 p_person_id => p_rec.person_id,
1284 p_effective_date => p_effective_date,
1285 p_object_version_number => p_rec.object_version_number);
1286 --
1287 chk_benefit_action_id
1288 (p_batch_ler_id => p_rec.batch_ler_id,
1289 p_benefit_action_id => p_rec.benefit_action_id,
1290 p_object_version_number => p_rec.object_version_number);
1291 --
1292 chk_dltd_flag
1293 (p_batch_ler_id => p_rec.batch_ler_id,
1294 p_dltd_flag => p_rec.dltd_flag,
1298 chk_tmprl_flag
1295 p_effective_date => p_effective_date,
1296 p_object_version_number => p_rec.object_version_number);
1297 --
1299 (p_batch_ler_id => p_rec.batch_ler_id,
1300 p_tmprl_flag => p_rec.tmprl_flag,
1301 p_effective_date => p_effective_date,
1302 p_object_version_number => p_rec.object_version_number);
1303 --
1304 chk_crtd_flag
1305 (p_batch_ler_id => p_rec.batch_ler_id,
1306 p_crtd_flag => p_rec.crtd_flag,
1307 p_effective_date => p_effective_date,
1308 p_object_version_number => p_rec.object_version_number);
1309 --
1310 chk_replcd_flag
1311 (p_batch_ler_id => p_rec.batch_ler_id,
1312 p_replcd_flag => p_rec.replcd_flag,
1313 p_effective_date => p_effective_date,
1314 p_object_version_number => p_rec.object_version_number);
1315 --
1316 chk_open_and_clsd_flag
1317 (p_batch_ler_id => p_rec.batch_ler_id,
1318 p_open_and_clsd_flag => p_rec.open_and_clsd_flag,
1319 p_effective_date => p_effective_date,
1320 p_object_version_number => p_rec.object_version_number);
1321 --
1322 chk_clsd_flag
1323 (p_batch_ler_id => p_rec.batch_ler_id,
1324 p_clsd_flag => p_rec.clsd_flag,
1325 p_effective_date => p_effective_date,
1326 p_object_version_number => p_rec.object_version_number);
1327 --
1328 chk_not_crtd_flag
1329 (p_batch_ler_id => p_rec.batch_ler_id,
1330 p_not_crtd_flag => p_rec.not_crtd_flag,
1331 p_effective_date => p_effective_date,
1332 p_object_version_number => p_rec.object_version_number);
1333 --
1334 chk_stl_actv_flag
1335 (p_batch_ler_id => p_rec.batch_ler_id,
1336 p_stl_actv_flag => p_rec.stl_actv_flag,
1337 p_effective_date => p_effective_date,
1338 p_object_version_number => p_rec.object_version_number);
1339 --
1340 chk_clpsd_flag
1341 (p_batch_ler_id => p_rec.batch_ler_id,
1342 p_clpsd_flag => p_rec.clpsd_flag,
1343 p_effective_date => p_effective_date,
1344 p_object_version_number => p_rec.object_version_number);
1345 --
1346 chk_clsn_flag
1347 (p_batch_ler_id => p_rec.batch_ler_id,
1348 p_clsn_flag => p_rec.clsn_flag,
1349 p_effective_date => p_effective_date,
1350 p_object_version_number => p_rec.object_version_number);
1351 --
1352 chk_no_effect_flag
1353 (p_batch_ler_id => p_rec.batch_ler_id,
1354 p_no_effect_flag => p_rec.no_effect_flag,
1355 p_effective_date => p_effective_date,
1356 p_object_version_number => p_rec.object_version_number);
1357 --
1358 chk_cvrge_rt_prem_flag
1359 (p_batch_ler_id => p_rec.batch_ler_id,
1360 p_cvrge_rt_prem_flag => p_rec.cvrge_rt_prem_flag,
1361 p_effective_date => p_effective_date,
1362 p_object_version_number => p_rec.object_version_number);
1363 --
1364 hr_utility.set_location(' Leaving:'||l_proc, 10);
1365 End update_validate;
1366 --
1367 -- ----------------------------------------------------------------------------
1368 -- |---------------------------< delete_validate >----------------------------|
1369 -- ----------------------------------------------------------------------------
1370 Procedure delete_validate(p_rec in ben_bli_shd.g_rec_type
1371 ,p_effective_date in date) is
1372 --
1373 l_proc varchar2(72) := g_package||'delete_validate';
1374 --
1375 Begin
1376 hr_utility.set_location('Entering:'||l_proc, 5);
1377 --
1378 -- Call all supporting business operations
1379 --
1380 hr_utility.set_location(' Leaving:'||l_proc, 10);
1381 End delete_validate;
1382 --
1383 -- ---------------------------------------------------------------------------
1384 -- |---------------------< return_legislation_code >-------------------------|
1385 -- ---------------------------------------------------------------------------
1386 --
1387 function return_legislation_code
1388 (p_batch_ler_id in number) return varchar2 is
1389 --
1390 -- Declare cursor
1391 --
1392 cursor csr_leg_code is
1393 select a.legislation_code
1394 from per_business_groups a,
1395 ben_batch_ler_info b
1396 where b.batch_ler_id = p_batch_ler_id
1397 and a.business_group_id = b.business_group_id;
1398 --
1399 -- Declare local variables
1400 --
1401 l_legislation_code varchar2(150);
1402 l_proc varchar2(72) := g_package||'return_legislation_code';
1403 --
1404 begin
1405 --
1406 hr_utility.set_location('Entering:'|| l_proc, 10);
1407 --
1408 -- Ensure that all the mandatory parameter are not null
1409 --
1410 hr_api.mandatory_arg_error(p_api_name => l_proc,
1411 p_argument => 'batch_ler_id',
1412 p_argument_value => p_batch_ler_id);
1413 --
1414 open csr_leg_code;
1415 --
1416 fetch csr_leg_code into l_legislation_code;
1417 --
1418 if csr_leg_code%notfound then
1419 --
1420 close csr_leg_code;
1421 --
1422 -- The primary key is invalid therefore we must error
1423 --
1424 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
1425 hr_utility.raise_error;
1426 --
1427 end if;
1428 --
1429 close csr_leg_code;
1430 --
1431 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1432 --
1433 return l_legislation_code;
1434 --
1435 end return_legislation_code;
1436 --
1437 end ben_bli_bus;