[Home] [Help]
PACKAGE BODY: APPS.BEN_EPE_BUS
Source
1 Package Body ben_epe_bus as
2 /* $Header: beeperhi.pkb 120.0 2005/05/28 02:36:58 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_epe_bus.'; -- Global package name
9 /*
10 --
11 -- ----------------------------------------------------------------------------
12 -- |------< chk_pil_elctbl_chc_popl_id >------|
13 -- ----------------------------------------------------------------------------
14 --
15 -- Description
16 -- This procedure checks that a referenced foreign key actually exists
17 -- in the referenced table.
18 --
19 -- Pre-Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- p_elig_per_elctbl_chc_id PK
24 -- p_pil_elctbl_chc_popl_id ID of FK column
25 -- p_effective_date session date
26 -- p_object_version_number object version number
27 --
28 -- Post Success
29 -- Processing continues
30 --
31 -- Post Failure
32 -- Error raised.
33 --
34 -- Access Status
35 -- Internal table handler use only.
36 --
37 Procedure chk_pil_elctbl_chc_popl_id (p_elig_per_elctbl_chc_id in number,
38 p_pil_elctbl_chc_popl_id in number,
39 p_effective_date in date,
40 p_object_version_number in number) is
41 --
42 l_proc varchar2(72) := g_package||'chk_pil_elctbl_chc_popl_id';
43 l_api_updating boolean;
44 l_dummy varchar2(1);
45 --
46 cursor c1 is
47 select null
48 from ben_pil_elctbl_chc_popl a
49 where a.pil_elctbl_chc_popl_id = p_pil_elctbl_chc_popl_id;
50 --
51 Begin
52 --
53 hr_utility.set_location('Entering:'||l_proc,5);
54 --
55 l_api_updating := ben_epe_shd.api_updating
56 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
57 p_object_version_number => p_object_version_number);
58 --
59 if (l_api_updating
60 and nvl(p_pil_elctbl_chc_popl_id,hr_api.g_number)
61 <> nvl(ben_epe_shd.g_old_rec.pil_elctbl_chc_popl_id,hr_api.g_number)
62 or not l_api_updating) and
63 p_pil_elctbl_chc_popl_id is not null then
64 --
65 -- check if pil_elctbl_chc_popl_id value exists in ben_pil_elctbl_chc_popl table
66 --
67 open c1;
68 --
69 fetch c1 into l_dummy;
70 if c1%notfound then
71 --
72 close c1;
73 --
74 -- raise error as FK does not relate to PK in ben_pil_elctbl_chc_popl
75 -- table.
76 --
77 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_?');
78 --
79 end if;
80 --
81 close c1;
82 --
83 end if;
84 --
85 hr_utility.set_location('Leaving:'||l_proc,10);
86 --
87 End chk_pil_elctbl_chc_popl_id;
88 */
89 --
90 -- ----------------------------------------------------------------------------
91 -- |------< chk_pl_typ_id >------|
92 -- ----------------------------------------------------------------------------
93 --
94 -- Description
95 -- This procedure checks that a referenced foreign key actually exists
96 -- in the referenced table.
97 --
98 -- Pre-Conditions
99 -- None.
100 --
101 -- In Parameters
102 -- p_elig_per_elctbl_chc_id PK
103 -- p_pl_typ_id ID of FK column
104 -- p_effective_date session date
105 -- p_object_version_number object version number
106 --
107 -- Post Success
108 -- Processing continues
109 --
110 -- Post Failure
111 -- Error raised.
112 --
113 -- Access Status
114 -- Internal table handler use only.
115 --
116 Procedure chk_pl_typ_id (p_elig_per_elctbl_chc_id in number,
117 p_pl_typ_id in number,
118 p_effective_date in date,
119 p_object_version_number in number) is
120 --
121 l_proc varchar2(72) := g_package||'chk_pl_typ_id';
122 l_api_updating boolean;
123 l_dummy varchar2(1);
124 --
125 cursor c1 is
126 select null
127 from ben_pl_typ_F a
128 where a.pl_typ_id = p_pl_typ_id;
129 --
130 Begin
131 --
132 hr_utility.set_location('Entering:'||l_proc,5);
133 --
134 l_api_updating := ben_epe_shd.api_updating
135 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
136 p_object_version_number => p_object_version_number);
137 --
138 if (l_api_updating
139 and nvl(p_pl_typ_id,hr_api.g_number)
140 <> nvl(ben_epe_shd.g_old_rec.pl_typ_id,hr_api.g_number)
141 or not l_api_updating) and
142 p_pl_typ_id is not null then
143 --
144 -- check if pl_typ_id value exists in ben_pl_typ table
145 --
146 open c1;
147 --
148 fetch c1 into l_dummy;
149 if c1%notfound then
150 --
151 close c1;
152 --
153 -- raise error a PK does not relate to PK in ben_pl_typ
154 -- table.
155 --
156 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_DT7');
157 --
158 end if;
159 --
160 close c1;
161 --
162 end if;
163 --
164 hr_utility.set_location('Leaving:'||l_proc,10);
165 --
166 End chk_pl_typ_id;
167 --
168 -- ----------------------------------------------------------------------------
169 -- |------< chk_spcl_rt_oipl_id >------|
170 -- ----------------------------------------------------------------------------
171 --
172 -- Description
173 -- This procedure checks that a referenced foreign key actually exists
174 -- in the referenced table.
175 --
176 -- Pre-Conditions
177 -- None.
178 --
179 -- In Parameters
180 -- p_elig_per_elctbl_chc_id PK
181 -- p_spcl_rt_oipl_id ID of FK column
182 -- p_effective_date session date
183 -- p_object_version_number object version number
184 --
185 -- Post Success
186 -- Processing continues
187 --
188 -- Post Failure
189 -- Error raised.
190 --
191 -- Access Status
192 -- Internal table handler use only.
193 --
194 Procedure chk_spcl_rt_oipl_id (p_elig_per_elctbl_chc_id in number,
195 p_spcl_rt_oipl_id in number,
196 p_effective_date in date,
197 p_object_version_number in number) is
198 --
199 l_proc varchar2(72) := g_package||'chk_spcl_rt_oipl_id';
200 l_api_updating boolean;
201 l_dummy varchar2(1);
202 --
203 cursor c1 is
204 select null
205 from ben_oipl_f a
206 where a.oipl_id = p_spcl_rt_oipl_id;
207 --
208 Begin
209 --
210 hr_utility.set_location('Entering:'||l_proc,5);
211 --
212 l_api_updating := ben_epe_shd.api_updating
213 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
214 p_object_version_number => p_object_version_number);
215 --
216 if (l_api_updating
217 and nvl(p_spcl_rt_oipl_id,hr_api.g_number)
218 <> nvl(ben_epe_shd.g_old_rec.spcl_rt_oipl_id,hr_api.g_number)
219 or not l_api_updating) and
220 p_spcl_rt_oipl_id is not null then
221 --
222 -- check if spcl_rt_oipl_id value exists in ben_spcl_rt_oipl table
223 --
224 open c1;
225 --
226 fetch c1 into l_dummy;
227 if c1%notfound then
228 --
229 close c1;
230 --
231 -- raise error as FK does not relate to PK in ben_spcl_rt_oipl
232 -- table.
233 --
234 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_DT13');
235 --
236 end if;
237 --
238 close c1;
239 --
240 end if;
241 --
242 hr_utility.set_location('Leaving:'||l_proc,10);
243 --
244 End chk_spcl_rt_oipl_id;
245 --
246 -- ----------------------------------------------------------------------------
247 -- |------< chk_spcl_rt_pl_id >------|
248 -- ----------------------------------------------------------------------------
249 --
250 -- Description
251 -- This procedure checks that a referenced foreign key actually exists
252 -- in the referenced table.
253 --
254 -- Pre-Conditions
255 -- None.
256 --
257 -- In Parameters
258 -- p_elig_per_elctbl_chc_id PK
259 -- p_spcl_rt_pl_id ID of FK column
260 -- p_effective_date session date
261 -- p_object_version_number object version number
262 --
263 -- Post Success
264 -- Processing continues
265 --
266 -- Post Failure
267 -- Error raised.
268 --
269 -- Access Status
270 -- Internal table handler use only.
271 --
272 Procedure chk_spcl_rt_pl_id (p_elig_per_elctbl_chc_id in number,
273 p_spcl_rt_pl_id in number,
274 p_effective_date in date,
275 p_object_version_number in number) is
276 --
277 l_proc varchar2(72) := g_package||'chk_spcl_rt_pl_id';
278 l_api_updating boolean;
279 l_dummy varchar2(1);
280 --
281 cursor c1 is
282 select null
283 from ben_pl_f a
284 where a.pl_id = p_spcl_rt_pl_id;
285 --
286 Begin
287 --
288 hr_utility.set_location('Entering:'||l_proc,5);
289 --
290 l_api_updating := ben_epe_shd.api_updating
291 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
292 p_object_version_number => p_object_version_number);
293 --
294 if (l_api_updating
295 and nvl(p_spcl_rt_pl_id,hr_api.g_number)
296 <> nvl(ben_epe_shd.g_old_rec.spcl_rt_pl_id,hr_api.g_number)
297 or not l_api_updating) and
298 p_spcl_rt_pl_id is not null then
299 --
300 -- check if spcl_rt_pl_id value exists in ben_spcl_rt_pl table
301 --
302 open c1;
303 --
304 fetch c1 into l_dummy;
305 if c1%notfound then
306 --
307 close c1;
308 --
309 -- raise error as FK does not relate to PK in ben_spcl_rt_pl
310 -- table.
311 --
312 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_DT12');
313 --
314 end if;
315 --
316 close c1;
317 --
318 end if;
319 --
320 hr_utility.set_location('Leaving:'||l_proc,10);
321 --
322 End chk_spcl_rt_pl_id;
323 --
324 -- ----------------------------------------------------------------------------
325 -- |------< chk_plip_id >------|
326 -- ----------------------------------------------------------------------------
327 --
328 -- Description
329 -- This procedure checks that a referenced foreign key actually exists
330 -- in the referenced table.
331 --
332 -- Pre-Conditions
333 -- None.
334 --
335 -- In Parameters
336 -- p_elig_per_elctbl_chc_id PK
337 -- p_plip_id ID of FK column
338 -- p_effective_date session date
339 -- p_object_version_number object version number
340 --
341 -- Post Success
342 -- Processing continues
343 --
344 -- Post Failure
345 -- Error raised.
346 --
347 -- Access Status
348 -- Internal table handler use only.
349 --
350 Procedure chk_plip_id (p_elig_per_elctbl_chc_id in number,
351 p_plip_id in number,
352 p_effective_date in date,
353 p_object_version_number in number) is
354 --
355 l_proc varchar2(72) := g_package||'chk_plip_id';
356 l_api_updating boolean;
357 l_dummy varchar2(1);
358 --
359 cursor c1 is
360 select null
361 from ben_plip_f a
362 where a.plip_id = p_plip_id;
363 --
364 Begin
365 --
366 hr_utility.set_location('Entering:'||l_proc,5);
367 --
368 l_api_updating := ben_epe_shd.api_updating
369 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
370 p_object_version_number => p_object_version_number);
371 --
372 if (l_api_updating
373 and nvl(p_plip_id,hr_api.g_number)
374 <> nvl(ben_epe_shd.g_old_rec.plip_id,hr_api.g_number)
375 or not l_api_updating) and
376 p_plip_id is not null then
377 --
378 -- check if plip_id value exists in ben_plip table
379 --
380 open c1;
381 --
382 fetch c1 into l_dummy;
383 if c1%notfound then
384 --
385 close c1;
386 --
387 -- raise error as FK does not relate to PK in ben_plip
388 -- table.
389 --
390 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_DT8');
391 --
392 end if;
393 --
394 close c1;
395 --
396 end if;
397 --
398 hr_utility.set_location('Leaving:'||l_proc,10);
399 --
400 End chk_plip_id;
401 --
402 -- ----------------------------------------------------------------------------
403 -- |------< chk_cmbn_ptip_id >------|
404 -- ----------------------------------------------------------------------------
405 --
406 -- Description
407 -- This procedure checks that a referenced foreign key actually exists
408 -- in the referenced table.
409 --
410 -- Pre-Conditions
411 -- None.
412 --
413 -- In Parameters
414 -- p_elig_per_elctbl_chc_id PK
415 -- p_cmbn_ptip_id ID of FK column
416 -- p_effective_date session date
417 -- p_object_version_number object version number
418 --
419 -- Post Success
420 -- Processing continues
421 --
422 -- Post Failure
423 -- Error raised.
424 --
425 -- Access Status
426 -- Internal table handler use only.
427 --
428 Procedure chk_cmbn_ptip_id (p_elig_per_elctbl_chc_id in number,
429 p_cmbn_ptip_id in number,
430 p_effective_date in date,
431 p_object_version_number in number) is
432 --
433 l_proc varchar2(72) := g_package||'chk_cmbn_ptip_id';
434 l_api_updating boolean;
435 l_dummy varchar2(1);
436 --
437 cursor c1 is
438 select null
439 from ben_cmbn_ptip_f a
440 where a.cmbn_ptip_id = p_cmbn_ptip_id;
441 --
442 Begin
443 --
444 hr_utility.set_location('Entering:'||l_proc,5);
445 --
446 l_api_updating := ben_epe_shd.api_updating
447 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
448 p_object_version_number => p_object_version_number);
449 --
450 if (l_api_updating
451 and nvl(p_cmbn_ptip_id,hr_api.g_number)
452 <> nvl(ben_epe_shd.g_old_rec.cmbn_ptip_id,hr_api.g_number)
453 or not l_api_updating) and
454 p_cmbn_ptip_id is not null then
455 --
456 -- check if cmbn_ptip_id value exists in ben_cmbn_ptip table
457 --
458 open c1;
459 --
460 fetch c1 into l_dummy;
461 if c1%notfound then
462 --
463 close c1;
464 --
465 -- raise error as FK does not relate to PK in ben_cmbn_ptip
466 -- table.
467 --
468 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_DT15');
469 --
470 end if;
471 --
472 close c1;
473 --
474 end if;
475 --
476 hr_utility.set_location('Leaving:'||l_proc,10);
477 --
478 End chk_cmbn_ptip_id;
479 --
480 -- ----------------------------------------------------------------------------
481 -- |------< chk_cmbn_plip_id >------|
482 -- ----------------------------------------------------------------------------
483 --
484 -- Description
485 -- This procedure checks that a referenced foreign key actually exists
486 -- in the referenced table.
487 --
488 -- Pre-Conditions
489 -- None.
490 --
491 -- In Parameters
492 -- p_elig_per_elctbl_chc_id PK
493 -- p_cmbn_plip_id ID of FK column
494 -- p_effective_date session date
495 -- p_object_version_number object version number
496 --
497 -- Post Success
498 -- Processing continues
499 --
500 -- Post Failure
501 -- Error raised.
502 --
503 -- Access Status
504 -- Internal table handler use only.
505 --
506 Procedure chk_cmbn_plip_id (p_elig_per_elctbl_chc_id in number,
507 p_cmbn_plip_id in number,
508 p_effective_date in date,
509 p_object_version_number in number) is
510 --
511 l_proc varchar2(72) := g_package||'chk_cmbn_plip_id';
512 l_api_updating boolean;
513 l_dummy varchar2(1);
514 --
515 cursor c1 is
516 select null
517 from ben_cmbn_plip_f a
518 where a.cmbn_plip_id = p_cmbn_plip_id;
519 --
520 Begin
521 --
522 hr_utility.set_location('Entering:'||l_proc,5);
523 --
524 l_api_updating := ben_epe_shd.api_updating
525 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
526 p_object_version_number => p_object_version_number);
527 --
528 if (l_api_updating
529 and nvl(p_cmbn_plip_id,hr_api.g_number)
530 <> nvl(ben_epe_shd.g_old_rec.cmbn_plip_id,hr_api.g_number)
531 or not l_api_updating) and
532 p_cmbn_plip_id is not null then
533 --
534 -- check if cmbn_plip_id value exists in ben_cmbn_plip table
535 --
536 open c1;
537 --
538 fetch c1 into l_dummy;
539 if c1%notfound then
540 --
541 close c1;
542 --
543 -- raise error as FK does not relate to PK in ben_cmbn_plip
544 -- table.
545 --
546 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_DT15');
547 --
548 end if;
549 --
550 close c1;
551 --
552 end if;
553 --
554 hr_utility.set_location('Leaving:'||l_proc,10);
555 --
556 End chk_cmbn_plip_id;
557 --
558 -- ----------------------------------------------------------------------------
559 -- |------< chk_ptip_id >------|
560 -- ----------------------------------------------------------------------------
561 --
562 -- Description
563 -- This procedure checks that a referenced foreign key actually exists
564 -- in the referenced table.
565 --
566 -- Pre-Conditions
567 -- None.
568 --
569 -- In Parameters
570 -- p_elig_per_elctbl_chc_id PK
571 -- p_ptip_id ID of FK column
572 -- p_effective_date session date
573 -- p_object_version_number object version number
574 --
575 -- Post Success
576 -- Processing continues
577 --
578 -- Post Failure
579 -- Error raised.
580 --
581 -- Access Status
582 -- Internal table handler use only.
583 --
584 Procedure chk_ptip_id (p_elig_per_elctbl_chc_id in number,
585 p_ptip_id in number,
586 p_effective_date in date,
587 p_object_version_number in number) is
588 --
589 l_proc varchar2(72) := g_package||'chk_ptip_id';
590 l_api_updating boolean;
591 l_dummy varchar2(1);
592 --
593 cursor c1 is
594 select null
595 from ben_ptip_f a
596 where a.ptip_id = p_ptip_id;
597 --
598 Begin
599 --
600 hr_utility.set_location('Entering:'||l_proc,5);
601 --
602 l_api_updating := ben_epe_shd.api_updating
603 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
604 p_object_version_number => p_object_version_number);
605 --
606 if (l_api_updating
607 and nvl(p_ptip_id,hr_api.g_number)
608 <> nvl(ben_epe_shd.g_old_rec.ptip_id,hr_api.g_number)
609 or not l_api_updating) and
610 p_ptip_id is not null then
611 --
612 -- check if ptip_id value exists in ben_ptip table
613 --
614 open c1;
615 --
616 fetch c1 into l_dummy;
617 if c1%notfound then
618 --
619 close c1;
620 --
621 -- raise error as FK does not relate to PK in ben_ptip
622 -- table.
623 --
624 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_DT9');
625 --
626 end if;
627 --
628 close c1;
629 --
630 end if;
631 --
632 hr_utility.set_location('Leaving:'||l_proc,10);
633 --
634 End chk_ptip_id;
635 --
636 -- ----------------------------------------------------------------------------
637 -- |------< chk_cmbn_ptip_opt_id >------|
638 -- ----------------------------------------------------------------------------
639 --
640 -- Description
641 -- This procedure checks that a referenced foreign key actually exists
642 -- in the referenced table.
643 --
644 -- Pre-Conditions
645 -- None.
646 --
647 -- In Parameters
648 -- p_elig_per_elctbl_chc_id PK
649 -- p_cmbn_ptip_opt_id ID of FK column
650 -- p_effective_date session date
651 -- p_object_version_number object version number
652 --
653 -- Post Success
654 -- Processing continues
655 --
656 -- Post Failure
657 -- Error raised.
658 --
659 -- Access Status
660 -- Internal table handler use only.
661 --
662 Procedure chk_cmbn_ptip_opt_id (p_elig_per_elctbl_chc_id in number,
663 p_cmbn_ptip_opt_id in number,
664 p_effective_date in date,
665 p_object_version_number in number) is
666 --
667 l_proc varchar2(72) := g_package||'chk_cmbn_ptip_opt_id';
668 l_api_updating boolean;
669 l_dummy varchar2(1);
670 --
671 cursor c1 is
672 select null
673 from ben_cmbn_ptip_opt_f a
674 where a.cmbn_ptip_opt_id = p_cmbn_ptip_opt_id;
675 --
676 Begin
677 --
678 hr_utility.set_location('Entering:'||l_proc,5);
679 --
680 l_api_updating := ben_epe_shd.api_updating
681 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
682 p_object_version_number => p_object_version_number);
683 --
684 if (l_api_updating
685 and nvl(p_cmbn_ptip_opt_id,hr_api.g_number)
686 <> nvl(ben_epe_shd.g_old_rec.cmbn_ptip_opt_id,hr_api.g_number)
687 or not l_api_updating) and
688 p_cmbn_ptip_opt_id is not null then
689 --
690 -- check if cmbn_ptip_opt_id value exists in ben_cmbn_ptip_opt table
691 --
692 open c1;
693 --
694 fetch c1 into l_dummy;
695 if c1%notfound then
696 --
697 close c1;
698 --
699 -- raise error as FK does not relate to PK in ben_cmbn_ptip_opt
700 -- table.
701 --
702 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_DT16');
703 --
704 end if;
705 --
706 close c1;
707 --
708 end if;
709 --
710 hr_utility.set_location('Leaving:'||l_proc,10);
711 --
712 End chk_cmbn_ptip_opt_id;
713 --
714 -- ----------------------------------------------------------------------------
715 -- |------< chk_pgm_id >------|
716 -- ----------------------------------------------------------------------------
717 --
718 -- Description
719 -- This procedure checks that a referenced foreign key actually exists
720 -- in the referenced table.
721 --
722 -- Pre-Conditions
723 -- None.
724 --
725 -- In Parameters
726 -- p_elig_per_elctbl_chc_id PK
727 -- p_pgm_id ID of FK column
728 -- p_effective_date session date
729 -- p_object_version_number object version number
730 --
731 -- Post Success
732 -- Processing continues
733 --
734 -- Post Failure
735 -- Error raised.
736 --
737 -- Access Status
738 -- Internal table handler use only.
739 --
740 Procedure chk_pgm_id (p_elig_per_elctbl_chc_id in number,
741 p_pgm_id in number,
742 p_effective_date in date,
743 p_object_version_number in number) is
744 --
745 l_proc varchar2(72) := g_package||'chk_pgm_id';
746 l_api_updating boolean;
747 l_dummy varchar2(1);
748 --
749 cursor c1 is
750 select null
751 from ben_pgm_f a
752 where a.pgm_id = p_pgm_id;
753 --
754 Begin
755 --
756 hr_utility.set_location('Entering:'||l_proc,5);
757 --
758 l_api_updating := ben_epe_shd.api_updating
759 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
760 p_object_version_number => p_object_version_number);
761 --
762 if (l_api_updating
763 and nvl(p_pgm_id,hr_api.g_number)
764 <> nvl(ben_epe_shd.g_old_rec.pgm_id,hr_api.g_number)
765 or not l_api_updating) and
766 p_pgm_id is not null then
767 --
768 -- check if pgm_id value exists in ben_pgm table
769 --
770 open c1;
771 --
772 fetch c1 into l_dummy;
773 if c1%notfound then
774 --
775 close c1;
776 --
777 -- raise error as FK does not relate to PK in ben_pgm
778 -- table.
779 --
780 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_DT3');
781 --
782 end if;
783 --
784 close c1;
785 --
786 end if;
787 --
788 hr_utility.set_location('Leaving:'||l_proc,10);
789 --
790 End chk_pgm_id;
791 --
792 -- ----------------------------------------------------------------------------
793 -- |------< chk_oipl_id >------|
794 -- ----------------------------------------------------------------------------
795 --
796 -- Description
797 -- This procedure checks that a referenced foreign key actually exists
798 -- in the referenced table.
799 --
800 -- Pre-Conditions
801 -- None.
802 --
803 -- In Parameters
804 -- p_elig_per_elctbl_chc_id PK
805 -- p_oipl_id ID of FK column
806 -- p_effective_date session date
807 -- p_object_version_number object version number
808 --
809 -- Post Success
810 -- Processing continues
811 --
812 -- Post Failure
813 -- Error raised.
814 --
815 -- Access Status
816 -- Internal table handler use only.
817 --
818 Procedure chk_oipl_id (p_elig_per_elctbl_chc_id in number,
819 p_oipl_id in number,
820 p_effective_date in date,
821 p_object_version_number in number) is
822 --
823 l_proc varchar2(72) := g_package||'chk_oipl_id';
824 l_api_updating boolean;
825 l_dummy varchar2(1);
826 --
827 cursor c1 is
828 select null
829 from ben_oipl_f a
830 where a.oipl_id = p_oipl_id;
831 --
832 Begin
833 --
834 hr_utility.set_location('Entering:'||l_proc,5);
835 --
836 l_api_updating := ben_epe_shd.api_updating
837 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
838 p_object_version_number => p_object_version_number);
839 --
840 if (l_api_updating
841 and nvl(p_oipl_id,hr_api.g_number)
842 <> nvl(ben_epe_shd.g_old_rec.oipl_id,hr_api.g_number)
843 or not l_api_updating) and
844 p_oipl_id is not null then
845 --
846 -- check if oipl_id value exists in ben_oipl table
847 --
848 open c1;
849 --
850 fetch c1 into l_dummy;
851 if c1%notfound then
852 --
853 close c1;
854 --
855 -- raise error as FK does not relate to PK in ben_oipl
856 -- table.
857 --
858 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_DT2');
859 --
860 end if;
861 --
862 close c1;
863 --
864 end if;
865 --
866 hr_utility.set_location('Leaving:'||l_proc,10);
867 --
868 End chk_oipl_id;
869 --
870 -- ----------------------------------------------------------------------------
871 -- |------< chk_pl_id >------|
872 -- ----------------------------------------------------------------------------
873 --
874 -- Description
875 -- This procedure checks that a referenced foreign key actually exists
876 -- in the referenced table.
877 --
878 -- Pre-Conditions
879 -- None.
880 --
881 -- In Parameters
882 -- p_elig_per_elctbl_chc_id PK
883 -- p_pl_id ID of FK column
884 -- p_effective_date session date
885 -- p_object_version_number object version number
886 --
887 -- Post Success
888 -- Processing continues
889 --
890 -- Post Failure
891 -- Error raised.
892 --
893 -- Access Status
894 -- Internal table handler use only.
895 --
896 Procedure chk_pl_id (p_elig_per_elctbl_chc_id in number,
897 p_pl_id in number,
898 p_effective_date in date,
899 p_object_version_number in number) is
900 --
901 l_proc varchar2(72) := g_package||'chk_pl_id';
902 l_api_updating boolean;
903 l_dummy varchar2(1);
904 --
905 cursor c1 is
906 select null
907 from ben_pl_f a
908 where a.pl_id = p_pl_id;
909 --
910 Begin
911 --
912 hr_utility.set_location('Entering:'||l_proc,5);
913 --
914 l_api_updating := ben_epe_shd.api_updating
915 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
916 p_object_version_number => p_object_version_number);
917 --
918 if (l_api_updating
919 and nvl(p_pl_id,hr_api.g_number)
920 <> nvl(ben_epe_shd.g_old_rec.pl_id,hr_api.g_number)
921 or not l_api_updating) and
922 p_pl_id is not null then
923 --
924 -- check if pl_id value exists in ben_pl table
925 --
926 open c1;
927 --
928 fetch c1 into l_dummy;
929 if c1%notfound then
930 --
931 close c1;
932 --
933 -- raise error as FK does not relate to PK in ben_pl
934 -- table.
935 --
936 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_DT2');
937 --
938 end if;
939 --
940 close c1;
941 --
942 end if;
943 --
944 hr_utility.set_location('Leaving:'||l_proc,10);
945 --
946 End chk_pl_id;
947 --
948 -- ----------------------------------------------------------------------------
949 -- |------< chk_bnft_prvdr_pool_id >------|
950 -- ----------------------------------------------------------------------------
951 --
952 -- Description
953 -- This procedure checks that a referenced foreign key actually exists
954 -- in the referenced table.
955 --
956 -- Pre-Conditions
957 -- None.
958 --
959 -- In Parameters
960 -- p_elig_per_elctbl_chc_id PK
961 -- p_bnft_prvdr_pool_id ID of FK column
962 -- p_effective_date session date
963 -- p_object_version_number object version number
964 --
965 -- Post Success
966 -- Processing continues
967 --
968 -- Post Failure
969 -- Error raised.
970 --
971 -- Access Status
972 -- Internal table handler use only.
973 --
974 Procedure chk_bnft_prvdr_pool_id (p_elig_per_elctbl_chc_id in number,
975 p_bnft_prvdr_pool_id in number,
976 p_effective_date in date,
977 p_object_version_number in number) is
978 --
979 l_proc varchar2(72) := g_package||'chk_bnft_prvdr_pool_id';
980 l_api_updating boolean;
981 l_dummy varchar2(1);
982 --
983 cursor c1 is
984 select null
985 from ben_bnft_prvdr_pool_f a
986 where a.bnft_prvdr_pool_id = p_bnft_prvdr_pool_id;
987 --
988 Begin
989 --
990 hr_utility.set_location('Entering:'||l_proc,5);
991 --
992 l_api_updating := ben_epe_shd.api_updating
993 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
994 p_object_version_number => p_object_version_number);
995 --
996 if (l_api_updating
997 and nvl(p_bnft_prvdr_pool_id,hr_api.g_number)
998 <> nvl(ben_epe_shd.g_old_rec.bnft_prvdr_pool_id,hr_api.g_number)
999 or not l_api_updating) and
1000 p_bnft_prvdr_pool_id is not null then
1001 --
1002 -- check if bnft_prvdr_pool_id value exists in ben_bnft_prvdr_pool table
1003 --
1004 open c1;
1005 --
1006 fetch c1 into l_dummy;
1007 if c1%notfound then
1008 --
1009 close c1;
1010 --
1011 -- raise error as FK does not relate to PK in ben_bnft_prvdr_pool
1012 -- table.
1013 --
1014 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_DT14');
1015 --
1016 end if;
1017 --
1018 close c1;
1019 --
1020 end if;
1021 --
1022 hr_utility.set_location('Leaving:'||l_proc,10);
1023 --
1024 End chk_bnft_prvdr_pool_id;
1025 --
1026 -- ----------------------------------------------------------------------------
1027 -- |------< chk_prtt_enrt_rslt_id >------|
1028 -- ----------------------------------------------------------------------------
1029 --
1030 -- Description
1031 -- This procedure checks that a referenced foreign key actually exists
1032 -- in the referenced table.
1033 --
1034 -- Pre-Conditions
1035 -- None.
1036 --
1037 -- In Parameters
1038 -- p_elig_per_elctbl_chc_id PK
1039 -- p_prtt_enrt_rslt_id ID of FK column
1040 -- p_effective_date session date
1041 -- p_object_version_number object version number
1042 --
1043 -- Post Success
1044 -- Processing continues
1045 --
1046 -- Post Failure
1047 -- Error raised.
1048 --
1049 -- Access Status
1050 -- Internal table handler use only.
1051 --
1052 Procedure chk_prtt_enrt_rslt_id (p_elig_per_elctbl_chc_id in number,
1053 p_prtt_enrt_rslt_id in number,
1054 p_effective_date in date,
1055 p_object_version_number in number) is
1056 --
1057 l_proc varchar2(72) := g_package||'chk_prtt_enrt_rslt_id';
1058 l_api_updating boolean;
1059 l_dummy varchar2(1);
1060 --
1061 cursor c1 is
1062 select null
1063 from ben_prtt_enrt_rslt_f a
1064 where a.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1065 and a.prtt_enrt_rslt_stat_cd is null;
1066 --
1067 Begin
1068 --
1069 hr_utility.set_location('Entering:'||l_proc,5);
1070 --
1071 l_api_updating := ben_epe_shd.api_updating
1072 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1073 p_object_version_number => p_object_version_number);
1074 --
1075 if (l_api_updating
1076 and nvl(p_prtt_enrt_rslt_id,hr_api.g_number)
1077 <> nvl(ben_epe_shd.g_old_rec.prtt_enrt_rslt_id,hr_api.g_number)
1078 or not l_api_updating) and
1079 p_prtt_enrt_rslt_id is not null then
1080 --
1081 -- check if prtt_enrt_rslt_id value exists in ben_prtt_enrt_rslt table.
1082 -- results that are voided or backed out should not be getting newly
1083 -- attached to choice records.
1084 --
1085 open c1;
1086 --
1087 fetch c1 into l_dummy;
1088 if c1%notfound then
1089 --
1090 close c1;
1091 --
1092 -- raise error as FK does not relate to PK in ben_prtt_enrt_rslt
1093 -- table.
1094 --
1095 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_DT5');
1096 --
1097 end if;
1098 --
1099 close c1;
1100 --
1101 end if;
1102 --
1103 hr_utility.set_location('Leaving:'||l_proc,10);
1104 --
1105 End chk_prtt_enrt_rslt_id;
1106 --
1107 -- ----------------------------------------------------------------------------
1108 -- |------< chk_per_in_ler_id >------|
1109 -- ----------------------------------------------------------------------------
1110 --
1111 -- Description
1112 -- This procedure checks that a referenced foreign key actually exists
1113 -- in the referenced table.
1114 --
1115 -- Pre-Conditions
1116 -- None.
1117 --
1118 -- In Parameters
1119 -- p_elig_per_elctbl_chc_id PK
1120 -- p_per_in_ler_id ID of FK column
1121 -- p_effective_date session date
1122 -- p_object_version_number object version number
1123 --
1124 -- Post Success
1125 -- Processing continues
1126 --
1127 -- Post Failure
1128 -- Error raised.
1129 --
1130 -- Access Status
1131 -- Internal table handler use only.
1132 --
1133 Procedure chk_per_in_ler_id (p_elig_per_elctbl_chc_id in number,
1134 p_per_in_ler_id in number,
1135 p_effective_date in date,
1136 p_object_version_number in number) is
1137 --
1138 l_proc varchar2(72) := g_package||'chk_per_in_ler_id';
1139 l_api_updating boolean;
1140 l_dummy varchar2(1);
1141 --
1142 cursor c1 is
1143 select null
1144 from ben_per_in_ler a
1145 where a.per_in_ler_id = p_per_in_ler_id
1146 and a.per_in_ler_stat_cd not in ('BCKDT', 'VOIDD');
1147 --
1148 Begin
1149 --
1150 hr_utility.set_location('Entering:'||l_proc,5);
1151 --
1152 l_api_updating := ben_epe_shd.api_updating
1153 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1154 p_object_version_number => p_object_version_number);
1155 --
1156 if (l_api_updating
1157 and nvl(p_per_in_ler_id,hr_api.g_number)
1158 <> nvl(ben_epe_shd.g_old_rec.per_in_ler_id,hr_api.g_number)
1159 or not l_api_updating) and
1160 p_per_in_ler_id is not null then
1161 --
1162 -- check if per_in_ler_id value exists in ben_per_in_ler table, and that
1163 -- the status of the per-in-ler_id that is being updated is a valid one.
1164 --
1165 open c1;
1166 --
1167 fetch c1 into l_dummy;
1168 if c1%notfound then
1169 --
1170 close c1;
1171 --
1172 -- raise error as FK does not relate to PK in ben_per_in_ler
1173 -- table.
1174 --
1175 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_DT6');
1176 --
1177 end if;
1178 --
1179 close c1;
1180 --
1181 end if;
1182 --
1183 hr_utility.set_location('Leaving:'||l_proc,10);
1184 --
1185 End chk_per_in_ler_id;
1186 --
1187 -- ----------------------------------------------------------------------------
1188 -- |------< chk_must_enrl_anthr_pl_id >------|
1189 -- ----------------------------------------------------------------------------
1190 --
1191 -- Description
1192 -- This procedure checks that a referenced foreign key actually exists
1193 -- in the referenced table.
1194 --
1195 -- Pre-Conditions
1196 -- None.
1197 --
1198 -- In Parameters
1199 -- p_elig_per_elctbl_chc_id PK
1200 -- p_must_enrl_anthr_pl_id ID of FK column
1201 -- p_effective_date session date
1202 -- p_object_version_number object version number
1203 --
1204 -- Post Success
1205 -- Processing continues
1206 --
1207 -- Post Failure
1208 -- Error raised.
1209 --
1210 -- Access Status
1211 -- Internal table handler use only.
1212 --
1213 Procedure chk_must_enrl_anthr_pl_id (p_elig_per_elctbl_chc_id in number,
1214 p_must_enrl_anthr_pl_id in number,
1215 p_effective_date in date,
1216 p_object_version_number in number) is
1217 --
1218 l_proc varchar2(72) := g_package||'chk_must_enrl_anthr_pl_id';
1219 l_api_updating boolean;
1220 l_dummy varchar2(1);
1221 --
1222 cursor c1 is
1223 select null
1224 from ben_pl_f a
1225 where a.pl_id = p_must_enrl_anthr_pl_id;
1226 --
1227 Begin
1228 --
1229 hr_utility.set_location('Entering:'||l_proc,5);
1230 --
1231 l_api_updating := ben_epe_shd.api_updating
1232 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1233 p_object_version_number => p_object_version_number);
1234 --
1235 if (l_api_updating
1236 and nvl(p_must_enrl_anthr_pl_id,hr_api.g_number)
1237 <> nvl(ben_epe_shd.g_old_rec.must_enrl_anthr_pl_id,hr_api.g_number)
1238 or not l_api_updating) and
1239 p_must_enrl_anthr_pl_id is not null then
1240 --
1241 -- check if must_enrl_anthr_pl_id value exists in ben_must_enrl_anthr_pl table
1242 --
1243 open c1;
1244 --
1245 fetch c1 into l_dummy;
1246 if c1%notfound then
1247 --
1248 close c1;
1249 --
1250 -- raise error as FK does not relate to PK in ben_must_enrl_anthr_pl
1251 -- table.
1252 --
1253 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_DT11');
1254 --
1255 end if;
1256 --
1257 close c1;
1258 --
1259 end if;
1260 --
1261 hr_utility.set_location('Leaving:'||l_proc,10);
1262 --
1263 End chk_must_enrl_anthr_pl_id;
1264 --
1265 -- ----------------------------------------------------------------------------
1266 -- |------< chk_int_elig_per_elctbl_chc_id >------|
1267 -- ----------------------------------------------------------------------------
1268 --
1269 -- Description
1270 -- This procedure checks that a referenced foreign key actually exists
1271 -- in the referenced table.
1272 --
1273 -- Pre-Conditions
1274 -- None.
1275 --
1276 -- In Parameters
1277 -- p_elig_per_elctbl_chc_id PK
1278 -- p_int_elig_per_elctbl_chc_id ID of FK column
1279 -- p_effective_date session date
1280 -- p_object_version_number object version number
1281 --
1282 -- Post Success
1283 -- Processing continues
1284 --
1285 -- Post Failure
1286 -- Error raised.
1287 --
1288 -- Access Status
1289 -- Internal table handler use only.
1290 --
1291 Procedure chk_int_elig_per_elctbl_chc_id (p_elig_per_elctbl_chc_id in number,
1292 p_int_elig_per_elctbl_chc_id in number,
1293 p_effective_date in date,
1294 p_object_version_number in number) is
1295 --
1296 l_proc varchar2(72) := g_package||'chk_int_elig_per_elctbl_chc_id';
1297 l_api_updating boolean;
1298 l_dummy varchar2(1);
1299 --
1300 cursor c1 is
1301 select null
1302 from ben_elig_per_elctbl_chc a
1303 where a.elig_per_elctbl_chc_id = p_int_elig_per_elctbl_chc_id;
1304 --
1305 Begin
1306 --
1307 hr_utility.set_location('Entering:'||l_proc,5);
1308 --
1309 l_api_updating := ben_epe_shd.api_updating
1310 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1311 p_object_version_number => p_object_version_number);
1312 --
1313 if (l_api_updating
1314 and nvl(p_int_elig_per_elctbl_chc_id,hr_api.g_number)
1315 <> nvl(ben_epe_shd.g_old_rec.int_elig_per_elctbl_chc_id,hr_api.g_number)
1316 or not l_api_updating) and
1317 p_int_elig_per_elctbl_chc_id is not null then
1318 --
1319 -- check if int_elig_per_elctbl_chc_id value exists in table
1320 --
1321 open c1;
1322 --
1323 fetch c1 into l_dummy;
1324 if c1%notfound then
1325 --
1326 close c1;
1327 --
1328 -- raise error as FK does not relate to PK in
1329 -- table.
1330 --
1331 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_FK18');
1332 --
1333 end if;
1334 --
1335 close c1;
1336 --
1337 end if;
1338 --
1339 hr_utility.set_location('Leaving:'||l_proc,10);
1340 --
1341 End chk_int_elig_per_elctbl_chc_id;
1342 --
1343 -- ----------------------------------------------------------------------------
1344 -- |------< chk_yr_perd_id >------|
1345 -- ----------------------------------------------------------------------------
1346 --
1347 -- Description
1348 -- This procedure checks that a referenced foreign key actually exists
1349 -- in the referenced table.
1350 --
1351 -- Pre-Conditions
1352 -- None.
1353 --
1354 -- In Parameters
1355 -- p_elig_per_elctbl_chc_id PK
1356 -- p_yr_perd_id ID of FK column
1357 -- p_effective_date session date
1358 -- p_object_version_number object version number
1359 --
1360 -- Post Success
1361 -- Processing continues
1362 --
1363 -- Post Failure
1364 -- Error raised.
1365 --
1366 -- Access Status
1367 -- Internal table handler use only.
1368 --
1369 Procedure chk_yr_perd_id (p_elig_per_elctbl_chc_id in number,
1370 p_yr_perd_id in number,
1371 p_effective_date in date,
1372 p_object_version_number in number) is
1373 --
1374 l_proc varchar2(72) := g_package||'chk_yr_perd_id';
1375 l_api_updating boolean;
1376 l_dummy varchar2(1);
1377 --
1378 cursor c1 is
1379 select null
1380 from ben_yr_perd a
1381 where a.yr_perd_id = p_yr_perd_id;
1382 --
1383 Begin
1384 --
1385 hr_utility.set_location('Entering:'||l_proc,5);
1386 --
1387 l_api_updating := ben_epe_shd.api_updating
1388 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1389 p_object_version_number => p_object_version_number);
1390 --
1391 if (l_api_updating
1392 and nvl(p_yr_perd_id,hr_api.g_number)
1393 <> nvl(ben_epe_shd.g_old_rec.yr_perd_id,hr_api.g_number)
1394 or not l_api_updating) and
1395 p_yr_perd_id is not null then
1396 --
1397 -- check if yr_perd_id value exists in ben_yr_perd table
1398 --
1399 open c1;
1400 --
1401 fetch c1 into l_dummy;
1402 if c1%notfound then
1403 --
1404 close c1;
1405 --
1406 -- raise error as FK does not relate to PK in ben_yr_perd
1407 -- table.
1408 --
1409 ben_epe_shd.constraint_error('BEN_ELIG_PER_ELCTBL_CHC_FK15');
1410 --
1411 end if;
1412 --
1413 close c1;
1414 --
1415 end if;
1416 --
1417 hr_utility.set_location('Leaving:'||l_proc,10);
1418 --
1419 End chk_yr_perd_id;
1420 --
1421 -- -------------------------------------------------------------------------
1422 -- |------< chk_enrt_ovrid_rsn_cd >------|
1423 -- -------------------------------------------------------------------------
1424 --
1425 -- Description
1426 -- This procedure is used to check that the lookup value is valid.
1427 --
1428 -- Pre Conditions
1429 -- None.
1430 --
1431 -- In Parameters
1432 -- prtt_enrt_rslt_id PK of record being inserted or updated.
1433 -- enrt_ovrid_rsn_cd Value of lookup code.
1434 -- effective_date effective date
1435 -- object_version_number Object version number of record being
1436 -- inserted or updated.
1437 --
1438 -- Post Success
1439 -- Processing continues
1440 --
1441 -- Post Failure
1442 -- Error handled by procedure
1443 --
1444 -- Access Status
1445 -- Internal table handler use only.
1446 --
1447 Procedure chk_all_cds
1448 (p_elig_per_elctbl_chc_id in number
1449 ,p_lookup_type in varchar2
1450 ,p_cd in varchar2
1451 ,p_old_cd in varchar2
1452 ,p_effective_date in date
1453 ,p_object_version_number in number) is
1454 --
1455 l_proc varchar2(72) := g_package||'chk_all_cds';
1456 l_api_updating boolean;
1457 --
1458 Begin
1459 --
1460 /*
1461 hr_utility.set_location('Entering:'||l_proc, 5);
1462 */
1463 --
1464 l_api_updating := ben_epe_shd.api_updating
1465 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1466 p_object_version_number => p_object_version_number);
1467
1468 if (l_api_updating
1469 and p_cd
1470 <> nvl(p_old_cd,hr_api.g_varchar2)
1471 or not l_api_updating)
1472 and p_cd is not null then
1473 --
1474 -- check if value of lookup falls within lookup type.
1475 --
1476 /*
1477 hr_utility.set_location('HRAPI_NEIHL:'||l_proc, 5);
1478 */
1479 if hr_api.not_exists_in_hr_lookups
1480 (p_lookup_type => p_lookup_type,
1481 p_lookup_code => p_cd,
1482 p_effective_date => p_effective_date) then
1483 --
1484 -- raise error as does not exist as lookup
1485 --
1486 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1487 fnd_message.set_token('FIELD', p_cd);
1488 fnd_message.set_token('TYPE',p_lookup_type);
1489 fnd_message.raise_error;
1490 end if;
1491 /*
1492 hr_utility.set_location('Dn HRAPI_NEIHL:'||l_proc, 5);
1493 */
1494 end if;
1495 /*
1496 hr_utility.set_location('Leaving:'||l_proc,10);
1497 */
1498 end chk_all_cds;
1499
1500 --
1501 -- ----------------------------------------------------------------------------
1502 -- |------< chk_all_flags >------|
1503 -- ----------------------------------------------------------------------------
1504 --
1505 -- Description
1506 -- This procedure is used to check that the lookup value is valid.
1507 --
1508 -- Pre Conditions
1509 -- None.
1510 --
1511 -- In Parameters
1512 -- elig_per_elctbl_chc_id PK of record being inserted or updated.
1513 -- flag Value of lookup code.
1514 -- old value of flag from old_rec.
1515 -- effective_date effective date
1516 -- object_version_number Object version number of record being
1517 -- inserted or updated.
1518 --
1519 -- Post Success
1520 -- Processing continues
1521 --
1522 -- Post Failure
1523 -- Error handled by procedure
1524 --
1525 -- Access Status
1526 -- Internal table handler use only.
1527 --
1528 Procedure chk_all_flags( p_elig_per_elctbl_chc_id in number
1529 ,p_flag in varchar2
1530 ,p_old_flag in varchar2
1531 ,p_effective_date in date
1532 ,p_object_version_number in number
1533 ) is
1534 l_proc varchar2(72) := g_package||'chk_all_flags';
1535 l_api_updating boolean;
1536
1537 Begin
1538 --
1539 /*
1540 hr_utility.set_location('Entering:'||l_proc, 5);
1541 --
1542 */
1543 l_api_updating := ben_epe_shd.api_updating
1544 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1545 p_object_version_number => p_object_version_number);
1546 --
1547 /*
1548 if l_api_updating then
1549 hr_utility.set_location(l_proc||' updating true', 10);
1550 else
1551 hr_utility.set_location(l_proc||' updating false', 20);
1552 end if;
1553 hr_utility.set_location(l_proc||' pflag='||p_flag, 30);
1554 */
1555 if (l_api_updating
1556 and p_flag
1557 <> nvl(p_old_flag,hr_api.g_varchar2)
1558 or not l_api_updating) then
1559 --
1560 -- check if value of lookup falls within lookup type.
1561 --
1562 --
1563 /*
1564 hr_utility.set_location('HRAPI_NEIHL:'||l_proc, 5);
1565 */
1566 if hr_api.not_exists_in_hr_lookups
1567 (p_lookup_type => 'YES_NO',
1568 p_lookup_code => p_flag,
1569 p_effective_date => p_effective_date) then
1570 --
1571 -- raise error as does not exist as lookup
1572 --
1573 /*
1574 hr_utility.set_location(l_proc, 40);
1575 */
1576 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1577 fnd_message.set_token('FIELD', p_flag);
1578 fnd_message.set_token('TYPE','YES_NO');
1579 fnd_message.raise_error;
1580 --
1581 end if;
1582 /*
1583 hr_utility.set_location('Dn HRAPI_NEIHL:'||l_proc, 5);
1584 */
1585 --
1586 end if;
1587 --
1588 /*
1589 hr_utility.set_location('Leaving:'||l_proc,50);
1590 --
1591 */
1592 end chk_all_flags;
1593 --
1594 -- ----------------------------------------------------------------------------
1595 -- |------< chk_enrt_cvg_strt_dt_rl >------|
1596 -- ----------------------------------------------------------------------------
1597 --
1598 -- Description
1599 -- This procedure is used to check that the Formula Rule is valid.
1600 --
1601 -- Pre Conditions
1602 -- None.
1603 --
1604 -- In Parameters
1605 -- elig_per_elctbl_chc_id PK of record being inserted or updated.
1606 -- enrt_cvg_strt_dt_rl Value of formula rule id.
1607 -- effective_date effective date
1608 -- object_version_number Object version number of record being
1609 -- inserted or updated.
1610 --
1611 -- Post Success
1612 -- Processing continues
1613 --
1614 -- Post Failure
1615 -- Error handled by procedure
1616 --
1617 -- Access Status
1618 -- Internal table handler use only.
1619 --
1620 --
1621 Procedure chk_enrt_cvg_strt_dt_rl(p_elig_per_elctbl_chc_id in number,
1622 p_enrt_cvg_strt_dt_rl in number,
1623 p_business_group_id in number,
1624 p_effective_date in date,
1625 p_object_version_number in number) is
1626 --
1627 l_proc varchar2(72) := g_package||'chk_enrt_cvg_strt_dt_rl';
1628 l_api_updating boolean;
1629 l_dummy varchar2(1);
1630 --
1631 cursor c1 is
1632 select null
1633 from ff_formulas_f ff
1634 ,per_business_groups pbg
1635 where ff.formula_id = p_enrt_cvg_strt_dt_rl
1636 and ff.formula_type_id = -29
1637 and pbg.business_group_id = p_business_group_id
1638 and nvl(ff.business_group_id, p_business_group_id) =
1639 p_business_group_id
1640 and nvl(ff.legislation_code, pbg.legislation_code) =
1641 pbg.legislation_code
1642 and p_effective_date
1643 between ff.effective_start_date
1644 and ff.effective_end_date;
1645 --
1646 Begin
1647 --
1648 hr_utility.set_location('Entering:'||l_proc, 5);
1649 --
1650 l_api_updating := ben_epe_shd.api_updating
1651 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1652 p_object_version_number => p_object_version_number);
1653 --
1654 if (l_api_updating
1655 and nvl(p_enrt_cvg_strt_dt_rl,hr_api.g_number)
1656 <> ben_epe_shd.g_old_rec.enrt_cvg_strt_dt_rl
1657 or not l_api_updating)
1658 and p_enrt_cvg_strt_dt_rl is not null then
1659 --
1660 -- check if value of formula rule is valid.
1661 --
1662 open c1;
1663 --
1664 -- fetch value from cursor if it returns a record then the
1665 -- formula is valid otherwise its invalid
1666 --
1667 fetch c1 into l_dummy;
1668 if c1%notfound then
1669 --
1670 close c1;
1671 --
1672 -- raise error
1673 --
1674 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1675 fnd_message.set_token('ID',p_enrt_cvg_strt_dt_rl);
1676 fnd_message.set_token('TYPE_ID',-29);
1677 fnd_message.raise_error;
1678 --
1679 end if;
1680 --
1681 close c1;
1682 --
1683 end if;
1684 --
1685 hr_utility.set_location('Leaving:'||l_proc,10);
1686 --
1687 end chk_enrt_cvg_strt_dt_rl;
1688 --
1689 -- ----------------------------------------------------------------------------
1690 -- |------< chk_dpnt_cvg_strt_dt_rl >------|
1691 -- ----------------------------------------------------------------------------
1692 --
1693 -- Description
1694 -- This procedure is used to check that the Formula Rule is valid.
1695 --
1696 -- Pre Conditions
1697 -- None.
1698 --
1699 -- In Parameters
1700 -- elig_per_elctbl_chc_id PK of record being inserted or updated.
1701 -- dpnt_cvg_strt_dt_rl Value of formula rule id.
1702 -- effective_date effective date
1703 -- object_version_number Object version number of record being
1704 -- inserted or updated.
1705 --
1706 -- Post Success
1707 -- Processing continues
1708 --
1709 -- Post Failure
1710 -- Error handled by procedure
1711 --
1712 -- Access Status
1713 -- Internal table handler use only.
1714 --
1715 --
1716 Procedure chk_dpnt_cvg_strt_dt_rl(p_elig_per_elctbl_chc_id in number,
1717 p_dpnt_cvg_strt_dt_rl in number,
1718 p_business_group_id in number,
1719 p_effective_date in date,
1720 p_object_version_number in number) is
1721 --
1722 l_proc varchar2(72) := g_package||'chk_dpnt_cvg_strt_dt_rl';
1723 l_api_updating boolean;
1724 l_dummy varchar2(1);
1725 --
1726 cursor c1 is
1727 select null
1728 from ff_formulas_f ff
1729 ,per_business_groups pbg
1730 where ff.formula_id = p_dpnt_cvg_strt_dt_rl
1731 and ff.formula_type_id = -27
1732 and pbg.business_group_id = p_business_group_id
1733 and nvl(ff.business_group_id, p_business_group_id) =
1734 p_business_group_id
1735 and nvl(ff.legislation_code, pbg.legislation_code) =
1736 pbg.legislation_code
1737 and p_effective_date
1738 between ff.effective_start_date
1739 and ff.effective_end_date;
1740 --
1741 Begin
1742 --
1743 hr_utility.set_location('Entering:'||l_proc, 5);
1744 --
1745 l_api_updating := ben_epe_shd.api_updating
1746 (p_elig_per_elctbl_chc_id => p_elig_per_elctbl_chc_id,
1747 p_object_version_number => p_object_version_number);
1748 --
1749 if (l_api_updating
1750 and nvl(p_dpnt_cvg_strt_dt_rl,hr_api.g_number)
1751 <> ben_epe_shd.g_old_rec.dpnt_cvg_strt_dt_rl
1752 or not l_api_updating)
1753 and p_dpnt_cvg_strt_dt_rl is not null then
1754 --
1755 -- check if value of formula rule is valid.
1756 --
1757 open c1;
1758 --
1759 -- fetch value from cursor if it returns a record then the
1760 -- formula is valid otherwise its invalid
1761 --
1762 fetch c1 into l_dummy;
1763 if c1%notfound then
1764 --
1765 close c1;
1766 --
1767 -- raise error
1768 --
1769 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1770 fnd_message.set_token('ID',p_dpnt_cvg_strt_dt_rl);
1771 fnd_message.set_token('TYPE_ID',-27);
1772 fnd_message.raise_error;
1773 --
1774 end if;
1775 --
1776 close c1;
1777 --
1778 end if;
1779 --
1780 hr_utility.set_location('Leaving:'||l_proc,10);
1781 --
1782 end chk_dpnt_cvg_strt_dt_rl;
1783 --
1784 -- ---------------------------------------------------------------------------
1785 -- |---------------------< return_legislation_code >-------------------------|
1786 -- ---------------------------------------------------------------------------
1787 --
1788 -- Description:
1789 -- Return the legislation code for a specific primary key value
1790 --
1791 -- Prerequisites:
1792 -- The primary key identified by p_elig_per_elctbl_chc_id already exists.
1793 --
1794 -- In Arguments:
1795 -- p_elig_per_elctbl_chc_id
1796 --
1797 -- Post Success:
1798 -- If the value is found this function will return the values business
1799 -- group legislation code.
1800 --
1801 -- Post Failure:
1802 -- An error is raised if the value does not exist.
1803 --
1804 -- Access Status:
1805 -- Internal Development Use Only.
1806 --
1807 function return_legislation_code
1808 (p_elig_per_elctbl_chc_id in number) return varchar2 is
1809 --
1810 -- Declare cursor
1811 --
1812 cursor csr_leg_code is
1813 select a.legislation_code
1814 from per_business_groups a,
1815 ben_elig_per_elctbl_chc b
1816 where b.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1817 and a.business_group_id = b.business_group_id;
1818 --
1819 -- Declare local variables
1820 --
1821 l_legislation_code varchar2(150);
1822 l_proc varchar2(72) := g_package||'return_legislation_code';
1823 --
1824 begin
1825 --
1826 hr_utility.set_location('Entering:'|| l_proc, 10);
1827 --
1828 -- Ensure that all the mandatory parameter are not null
1829 --
1830 hr_api.mandatory_arg_error(p_api_name => l_proc,
1831 p_argument => 'elig_per_elctbl_chc_id',
1832 p_argument_value => p_elig_per_elctbl_chc_id);
1833 --
1834 open csr_leg_code;
1835 --
1836 fetch csr_leg_code into l_legislation_code;
1837 --
1838 if csr_leg_code%notfound then
1839 --
1840 close csr_leg_code;
1841 --
1842 -- The primary key is invalid therefore we must error
1843 --
1844 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
1845 fnd_message.raise_error;
1846 --
1847 end if;
1848 --
1849 close csr_leg_code;
1850 --
1851 hr_utility.set_location(' Leaving:'|| l_proc, 20);
1852 --
1853 return l_legislation_code;
1854 --
1855 end return_legislation_code;
1856
1857 -- ----------------------------------------------------------------------------
1858 -- |---------------------------< insert_validate >----------------------------|
1859 -- ----------------------------------------------------------------------------
1860 Procedure insert_validate(p_rec in ben_epe_shd.g_rec_type
1861 ,p_effective_date in date) is
1862 --
1863 l_proc varchar2(72) := g_package||'insert_validate';
1864 --
1865 Begin
1866 hr_utility.set_location('Entering:'||l_proc, 5);
1867 --
1868 -- Call context sensitive validate bgp cache routine
1869 --
1870 ben_batch_dt_api.batch_validate_bgp_id
1871 (p_business_group_id => p_rec.business_group_id
1872 );
1873 --
1874 /*
1875 --
1876 -- Call all supporting business operations
1877 --
1878 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1879 */
1880 chk_yr_perd_id
1881 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1882 p_yr_perd_id => p_rec.yr_perd_id,
1883 p_effective_date => p_effective_date,
1884 p_object_version_number => p_rec.object_version_number);
1885 --
1886 chk_must_enrl_anthr_pl_id
1887 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1888 p_must_enrl_anthr_pl_id => p_rec.must_enrl_anthr_pl_id,
1889 p_effective_date => p_effective_date,
1890 p_object_version_number => p_rec.object_version_number);
1891 --
1892 chk_int_elig_per_elctbl_chc_id
1893 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1894 p_int_elig_per_elctbl_chc_id => p_rec.int_elig_per_elctbl_chc_id,
1895 p_effective_date => p_effective_date,
1896 p_object_version_number => p_rec.object_version_number);
1897 --
1898 chk_per_in_ler_id
1899 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1900 p_per_in_ler_id => p_rec.per_in_ler_id,
1901 p_effective_date => p_effective_date,
1902 p_object_version_number => p_rec.object_version_number);
1903 --
1904 chk_prtt_enrt_rslt_id
1905 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1906 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
1907 p_effective_date => p_effective_date,
1908 p_object_version_number => p_rec.object_version_number);
1909 --
1910 chk_bnft_prvdr_pool_id
1911 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1912 p_bnft_prvdr_pool_id => p_rec.bnft_prvdr_pool_id,
1913 p_effective_date => p_effective_date,
1914 p_object_version_number => p_rec.object_version_number);
1915 --
1916 chk_pl_id
1917 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1918 p_pl_id => p_rec.pl_id,
1919 p_effective_date => p_effective_date,
1920 p_object_version_number => p_rec.object_version_number);
1921 --
1922 chk_oipl_id
1923 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1924 p_oipl_id => p_rec.oipl_id,
1925 p_effective_date => p_effective_date,
1926 p_object_version_number => p_rec.object_version_number);
1927 --
1928 chk_pgm_id
1929 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1930 p_pgm_id => p_rec.pgm_id,
1931 p_effective_date => p_effective_date,
1932 p_object_version_number => p_rec.object_version_number);
1933 --
1934 chk_cmbn_ptip_opt_id
1935 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1936 p_cmbn_ptip_opt_id => p_rec.cmbn_ptip_opt_id,
1937 p_effective_date => p_effective_date,
1938 p_object_version_number => p_rec.object_version_number);
1939 --
1940 chk_ptip_id
1941 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1942 p_ptip_id => p_rec.ptip_id,
1943 p_effective_date => p_effective_date,
1944 p_object_version_number => p_rec.object_version_number);
1945 --
1946 chk_cmbn_ptip_id
1947 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1948 p_cmbn_ptip_id => p_rec.cmbn_ptip_id,
1949 p_effective_date => p_effective_date,
1950 p_object_version_number => p_rec.object_version_number);
1951 --
1952 chk_cmbn_plip_id
1953 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1954 p_cmbn_plip_id => p_rec.cmbn_plip_id,
1955 p_effective_date => p_effective_date,
1956 p_object_version_number => p_rec.object_version_number);
1957 --
1958 chk_plip_id
1959 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1960 p_plip_id => p_rec.plip_id,
1961 p_effective_date => p_effective_date,
1962 p_object_version_number => p_rec.object_version_number);
1963 --
1964 chk_spcl_rt_pl_id
1965 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1966 p_spcl_rt_pl_id => p_rec.spcl_rt_pl_id,
1967 p_effective_date => p_effective_date,
1968 p_object_version_number => p_rec.object_version_number);
1969 --
1970 chk_spcl_rt_oipl_id
1971 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1972 p_spcl_rt_oipl_id => p_rec.spcl_rt_oipl_id,
1973 p_effective_date => p_effective_date,
1974 p_object_version_number => p_rec.object_version_number);
1975 --
1976 chk_pl_typ_id
1977 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1978 p_pl_typ_id => p_rec.pl_typ_id,
1979 p_effective_date => p_effective_date,
1980 p_object_version_number => p_rec.object_version_number);
1981 --
1982 /* chk_pil_elctbl_chc_popl_id
1983 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1984 p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
1985 p_effective_date => p_effective_date,
1986 p_object_version_number => p_rec.object_version_number);
1987 */ --
1988 --
1989 -- chk_all_cds
1990 -- (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
1991 -- p_lookup_type => 'BEN_RT_STRT',
1992 -- p_cd => p_rec.rt_strt_dt_cd,
1993 -- p_old_cd => ben_epe_shd.g_old_rec.rt_strt_dt_cd,
1994 -- p_effective_date => p_effective_date,
1995 -- p_object_version_number => p_rec.object_version_number);
1996 --
1997 --
1998 chk_all_cds
1999 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2000 p_lookup_type => 'BEN_ENRT_CVG_STRT',
2001 p_cd => p_rec.enrt_cvg_strt_dt_cd,
2002 p_old_cd => ben_epe_shd.g_old_rec.enrt_cvg_strt_dt_cd,
2003 p_effective_date => p_effective_date,
2004 p_object_version_number => p_rec.object_version_number);
2005 --
2006 --
2007 chk_all_cds
2008 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2009 p_lookup_type => 'BEN_DPNT_CVG_STRT',
2010 p_cd => p_rec.dpnt_cvg_strt_dt_cd,
2011 p_old_cd => ben_epe_shd.g_old_rec.dpnt_cvg_strt_dt_cd,
2012 p_effective_date => p_effective_date,
2013 p_object_version_number => p_rec.object_version_number);
2014 --
2015 chk_all_cds
2016 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2017 p_lookup_type => 'BEN_COMP_LVL',
2018 p_cd => p_rec.comp_lvl_cd,
2019 p_old_cd => ben_epe_shd.g_old_rec.comp_lvl_cd,
2020 p_effective_date => p_effective_date,
2021 p_object_version_number => p_rec.object_version_number);
2022 --
2023 chk_all_cds
2024 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2025 p_lookup_type => 'BEN_LER_CHG_DPNT_CVG',
2026 p_cd => p_rec.ler_chg_dpnt_cvg_cd,
2027 p_old_cd => ben_epe_shd.g_old_rec.ler_chg_dpnt_cvg_cd,
2028 p_effective_date => p_effective_date,
2029 p_object_version_number => p_rec.object_version_number);
2030 --
2031 chk_all_cds
2032 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2033 p_lookup_type => 'BEN_DPNT_DSGN',
2034 p_cd => p_rec.dpnt_dsgn_cd,
2035 p_old_cd => ben_epe_shd.g_old_rec.dpnt_dsgn_cd,
2036 p_effective_date => p_effective_date,
2037 p_object_version_number => p_rec.object_version_number);
2038 --cwb
2039 chk_all_cds
2040 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2041 p_lookup_type => 'BEN_INELIG_RSN',
2042 p_cd => p_rec.inelig_rsn_cd,
2043 p_old_cd => ben_epe_shd.g_old_rec.inelig_rsn_cd,
2044 p_effective_date => p_effective_date,
2045 p_object_version_number => p_rec.object_version_number);
2046 --cwb
2047 chk_all_flags
2048 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2049 p_flag => p_rec.alws_dpnt_dsgn_flag,
2050 p_old_flag => ben_epe_shd.g_old_rec.alws_dpnt_dsgn_flag,
2051 p_effective_date => p_effective_date,
2052 p_object_version_number => p_rec.object_version_number);
2053 --
2054 chk_all_flags
2055 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2056 p_flag => p_rec.auto_enrt_flag,
2057 p_old_flag => ben_epe_shd.g_old_rec.auto_enrt_flag,
2058 p_effective_date => p_effective_date,
2059 p_object_version_number => p_rec.object_version_number);
2060 --
2061 chk_all_flags
2062 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2063 p_flag => p_rec.crntly_enrd_flag,
2064 p_old_flag => ben_epe_shd.g_old_rec.crntly_enrd_flag,
2065 p_effective_date => p_effective_date,
2066 p_object_version_number => p_rec.object_version_number);
2067 --
2068 chk_all_flags
2069 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2070 p_flag => p_rec.dflt_flag,
2071 p_old_flag => ben_epe_shd.g_old_rec.dflt_flag,
2072 p_effective_date => p_effective_date,
2073 p_object_version_number => p_rec.object_version_number);
2074 --
2075 chk_all_flags
2076 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2077 p_flag => p_rec.elctbl_flag,
2078 p_old_flag => ben_epe_shd.g_old_rec.elctbl_flag,
2079 p_effective_date => p_effective_date,
2080 p_object_version_number => p_rec.object_version_number);
2081 --
2082 chk_all_flags
2083 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2084 p_flag => p_rec.mndtry_flag,
2085 p_old_flag => ben_epe_shd.g_old_rec.mndtry_flag,
2086 p_effective_date => p_effective_date,
2087 p_object_version_number => p_rec.object_version_number);
2088 /** Temporarily commented
2089 --cwb
2090 chk_all_flags
2091 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2092 p_flag => p_rec.elig_flag,
2093 p_old_flag => ben_epe_shd.g_old_rec.elig_flag,
2094 p_effective_date => p_effective_date,
2095 p_object_version_number => p_rec.object_version_number);
2096 --cwb
2097 */
2098 -- bug 1830930
2099 /*
2100 chk_all_flags
2101 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2102 p_flag => p_rec.in_pndg_wkflow_flag,
2103 p_old_flag => ben_epe_shd.g_old_rec.mndtry_flag,
2104 p_effective_date => p_effective_date,
2105 p_object_version_number => p_rec.object_version_number);
2106 */
2107 --
2108 chk_all_flags
2109 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2110 p_flag => p_rec.roll_crs_flag,
2111 p_old_flag => ben_epe_shd.g_old_rec.roll_crs_flag,
2112 p_effective_date => p_effective_date,
2113 p_object_version_number => p_rec.object_version_number);
2114 --
2115 chk_enrt_cvg_strt_dt_rl
2116 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2117 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
2118 p_business_group_id => p_rec.business_group_id,
2119 p_effective_date => p_effective_date,
2120 p_object_version_number => p_rec.object_version_number);
2121 --
2122 chk_dpnt_cvg_strt_dt_rl
2123 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2124 p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
2125 p_business_group_id => p_rec.business_group_id,
2126 p_effective_date => p_effective_date,
2127 p_object_version_number => p_rec.object_version_number);
2128 --
2129 hr_utility.set_location(' Leaving:'||l_proc, 10);
2130 End insert_validate;
2131 --
2132 -- ----------------------------------------------------------------------------
2133 -- |---------------------------< update_validate >----------------------------|
2134 -- ----------------------------------------------------------------------------
2135 Procedure update_validate(p_rec in ben_epe_shd.g_rec_type,
2136 p_effective_date in date) is
2137 --
2138 l_proc varchar2(72) := g_package||'update_validate';
2139 --
2140 Begin
2141 hr_utility.set_location('Entering:'||l_proc, 5);
2142 --
2143 -- Call context sensitive validate bgp cache routine
2144 --
2145 ben_batch_dt_api.batch_validate_bgp_id
2146 (p_business_group_id => p_rec.business_group_id
2147 );
2148 --
2149 /*
2150 --
2151 -- Call all supporting business operations
2152 --
2153 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2154 */
2155 chk_yr_perd_id
2156 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2157 p_yr_perd_id => p_rec.yr_perd_id,
2158 p_effective_date => p_effective_date,
2159 p_object_version_number => p_rec.object_version_number);
2160 --
2161 chk_must_enrl_anthr_pl_id
2162 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2163 p_must_enrl_anthr_pl_id => p_rec.must_enrl_anthr_pl_id,
2164 p_effective_date => p_effective_date,
2165 p_object_version_number => p_rec.object_version_number);
2166 --
2167 chk_int_elig_per_elctbl_chc_id
2168 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2169 p_int_elig_per_elctbl_chc_id => p_rec.int_elig_per_elctbl_chc_id,
2170 p_effective_date => p_effective_date,
2171 p_object_version_number => p_rec.object_version_number);
2172 --
2173 chk_per_in_ler_id
2174 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2175 p_per_in_ler_id => p_rec.per_in_ler_id,
2176 p_effective_date => p_effective_date,
2177 p_object_version_number => p_rec.object_version_number);
2178 --
2179 chk_prtt_enrt_rslt_id
2180 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2181 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
2182 p_effective_date => p_effective_date,
2183 p_object_version_number => p_rec.object_version_number);
2184 --
2185 chk_bnft_prvdr_pool_id
2186 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2187 p_bnft_prvdr_pool_id => p_rec.bnft_prvdr_pool_id,
2188 p_effective_date => p_effective_date,
2189 p_object_version_number => p_rec.object_version_number);
2190 --
2191 chk_pl_id
2192 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2193 p_pl_id => p_rec.pl_id,
2194 p_effective_date => p_effective_date,
2195 p_object_version_number => p_rec.object_version_number);
2196 --
2197 chk_oipl_id
2198 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2199 p_oipl_id => p_rec.oipl_id,
2200 p_effective_date => p_effective_date,
2201 p_object_version_number => p_rec.object_version_number);
2202 --
2203 chk_pgm_id
2204 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2205 p_pgm_id => p_rec.pgm_id,
2206 p_effective_date => p_effective_date,
2207 p_object_version_number => p_rec.object_version_number);
2208 --
2209 chk_cmbn_ptip_opt_id
2210 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2211 p_cmbn_ptip_opt_id => p_rec.cmbn_ptip_opt_id,
2212 p_effective_date => p_effective_date,
2213 p_object_version_number => p_rec.object_version_number);
2214 --
2215 chk_ptip_id
2216 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2217 p_ptip_id => p_rec.ptip_id,
2218 p_effective_date => p_effective_date,
2219 p_object_version_number => p_rec.object_version_number);
2220 --
2221 chk_cmbn_ptip_id
2222 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2223 p_cmbn_ptip_id => p_rec.cmbn_ptip_id,
2224 p_effective_date => p_effective_date,
2225 p_object_version_number => p_rec.object_version_number);
2226 --
2227 chk_cmbn_plip_id
2228 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2229 p_cmbn_plip_id => p_rec.cmbn_plip_id,
2230 p_effective_date => p_effective_date,
2231 p_object_version_number => p_rec.object_version_number);
2232 --
2233 chk_plip_id
2234 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2235 p_plip_id => p_rec.plip_id,
2236 p_effective_date => p_effective_date,
2237 p_object_version_number => p_rec.object_version_number);
2238 --
2239 chk_spcl_rt_pl_id
2240 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2241 p_spcl_rt_pl_id => p_rec.spcl_rt_pl_id,
2242 p_effective_date => p_effective_date,
2243 p_object_version_number => p_rec.object_version_number);
2244 --
2245 chk_spcl_rt_oipl_id
2246 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2247 p_spcl_rt_oipl_id => p_rec.spcl_rt_oipl_id,
2248 p_effective_date => p_effective_date,
2249 p_object_version_number => p_rec.object_version_number);
2250 --
2251 chk_pl_typ_id
2252 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2253 p_pl_typ_id => p_rec.pl_typ_id,
2254 p_effective_date => p_effective_date,
2255 p_object_version_number => p_rec.object_version_number);
2256 --
2257 /* chk_pil_elctbl_chc_popl_id
2258 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2259 p_pil_elctbl_chc_popl_id => p_rec.pil_elctbl_chc_popl_id,
2260 p_effective_date => p_effective_date,
2261 p_object_version_number => p_rec.object_version_number);
2262 */ --
2263 --
2264 -- chk_all_cds
2265 -- (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2266 -- p_lookup_type => 'BEN_RT_STRT',
2267 -- p_cd => p_rec.rt_strt_dt_cd,
2268 -- p_old_cd => ben_epe_shd.g_old_rec.rt_strt_dt_cd,
2269 -- p_effective_date => p_effective_date,
2270 -- p_object_version_number => p_rec.object_version_number);
2271 --
2272 --
2273 chk_all_cds
2274 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2275 p_lookup_type => 'BEN_ENRT_CVG_STRT',
2276 p_cd => p_rec.enrt_cvg_strt_dt_cd,
2277 p_old_cd => ben_epe_shd.g_old_rec.enrt_cvg_strt_dt_cd,
2278 p_effective_date => p_effective_date,
2279 p_object_version_number => p_rec.object_version_number);
2280 --
2281 --
2282 chk_all_cds
2283 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2284 p_lookup_type => 'BEN_DPNT_CVG_STRT',
2285 p_cd => p_rec.dpnt_cvg_strt_dt_cd,
2286 p_old_cd => ben_epe_shd.g_old_rec.dpnt_cvg_strt_dt_cd,
2287 p_effective_date => p_effective_date,
2288 p_object_version_number => p_rec.object_version_number);
2289 --
2290 chk_all_cds
2291 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2292 p_lookup_type => 'BEN_COMP_LVL',
2293 p_cd => p_rec.comp_lvl_cd,
2294 p_old_cd => ben_epe_shd.g_old_rec.comp_lvl_cd,
2295 p_effective_date => p_effective_date,
2296 p_object_version_number => p_rec.object_version_number);
2297 --
2298 chk_all_cds
2299 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2300 p_lookup_type => 'BEN_LER_CHG_DPNT_CVG',
2301 p_cd => p_rec.ler_chg_dpnt_cvg_cd,
2302 p_old_cd => ben_epe_shd.g_old_rec.ler_chg_dpnt_cvg_cd,
2303 p_effective_date => p_effective_date,
2304 p_object_version_number => p_rec.object_version_number);
2305 --
2306 chk_all_cds
2307 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2308 p_lookup_type => 'BEN_DPNT_DSGN',
2309 p_cd => p_rec.dpnt_dsgn_cd,
2310 p_old_cd => ben_epe_shd.g_old_rec.dpnt_dsgn_cd,
2311 p_effective_date => p_effective_date,
2312 p_object_version_number => p_rec.object_version_number);
2313 --
2314 --cwb
2315 chk_all_cds
2316 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2317 p_lookup_type => 'BEN_INELIG_RSN',
2318 p_cd => p_rec.inelig_rsn_cd,
2319 p_old_cd => ben_epe_shd.g_old_rec.inelig_rsn_cd,
2320 p_effective_date => p_effective_date,
2321 p_object_version_number => p_rec.object_version_number);
2322 --cwb
2323 /** Temporarily commented
2324 --cwb
2325 chk_all_flags
2326 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2327 p_flag => p_rec.elig_flag,
2328 p_old_flag => ben_epe_shd.g_old_rec.elig_flag,
2329 p_effective_date => p_effective_date,
2330 p_object_version_number => p_rec.object_version_number);
2331 --cwb
2332 */
2333 chk_all_flags
2334 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2335 p_flag => p_rec.alws_dpnt_dsgn_flag,
2336 p_old_flag => ben_epe_shd.g_old_rec.alws_dpnt_dsgn_flag,
2337 p_effective_date => p_effective_date,
2338 p_object_version_number => p_rec.object_version_number);
2339 --
2340 chk_all_flags
2341 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2342 p_flag => p_rec.auto_enrt_flag,
2343 p_old_flag => ben_epe_shd.g_old_rec.auto_enrt_flag,
2344 p_effective_date => p_effective_date,
2345 p_object_version_number => p_rec.object_version_number);
2346 --
2347 chk_all_flags
2348 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2349 p_flag => p_rec.crntly_enrd_flag,
2350 p_old_flag => ben_epe_shd.g_old_rec.crntly_enrd_flag,
2351 p_effective_date => p_effective_date,
2352 p_object_version_number => p_rec.object_version_number);
2353 --
2354 chk_all_flags
2355 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2356 p_flag => p_rec.dflt_flag,
2357 p_old_flag => ben_epe_shd.g_old_rec.dflt_flag,
2358 p_effective_date => p_effective_date,
2359 p_object_version_number => p_rec.object_version_number);
2360 --
2361 chk_all_flags
2362 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2363 p_flag => p_rec.elctbl_flag,
2364 p_old_flag => ben_epe_shd.g_old_rec.elctbl_flag,
2365 p_effective_date => p_effective_date,
2366 p_object_version_number => p_rec.object_version_number);
2367 --
2368 chk_all_flags
2369 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2370 p_flag => p_rec.mndtry_flag,
2371 p_old_flag => ben_epe_shd.g_old_rec.mndtry_flag,
2372 p_effective_date => p_effective_date,
2373 p_object_version_number => p_rec.object_version_number);
2374 -- bug 1830930
2375 /*
2376 chk_all_flags
2377 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2378 p_flag => p_rec.in_pndg_wkflow_flag,
2379 p_old_flag => ben_epe_shd.g_old_rec.mndtry_flag,
2380 p_effective_date => p_effective_date,
2381 p_object_version_number => p_rec.object_version_number);
2382 */
2383 --
2384 chk_all_flags
2385 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2386 p_flag => p_rec.roll_crs_flag,
2387 p_old_flag => ben_epe_shd.g_old_rec.roll_crs_flag,
2388 p_effective_date => p_effective_date,
2389 p_object_version_number => p_rec.object_version_number);
2390 --
2391 chk_enrt_cvg_strt_dt_rl
2392 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2393 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
2394 p_business_group_id => p_rec.business_group_id,
2395 p_effective_date => p_effective_date,
2396 p_object_version_number => p_rec.object_version_number);
2397 --
2398 chk_dpnt_cvg_strt_dt_rl
2399 (p_elig_per_elctbl_chc_id => p_rec.elig_per_elctbl_chc_id,
2400 p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
2401 p_business_group_id => p_rec.business_group_id,
2402 p_effective_date => p_effective_date,
2403 p_object_version_number => p_rec.object_version_number);
2404 --
2405 hr_utility.set_location(' Leaving:'||l_proc, 10);
2406 End update_validate;
2407 --
2408 -- ----------------------------------------------------------------------------
2409 -- |---------------------------< delete_validate >----------------------------|
2410 -- ----------------------------------------------------------------------------
2411 Procedure delete_validate(p_rec in ben_epe_shd.g_rec_type
2412 ,p_effective_date in date) is
2413 --
2414 l_proc varchar2(72) := g_package||'delete_validate';
2415 --
2416 Begin
2417 hr_utility.set_location('Entering:'||l_proc, 5);
2418 --
2419 -- Call all supporting business operations
2420 --
2421 hr_utility.set_location(' Leaving:'||l_proc, 10);
2422 End delete_validate;
2423 --
2424 end ben_epe_bus;