[Home] [Help]
PACKAGE BODY: APPS.BEN_PGM_BUS
Source
1 Package Body ben_pgm_bus as
2 /* $Header: bepgmrhi.pkb 120.1.12020000.2 2012/07/03 13:10:53 amnaraya ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_pgm_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |-----------------------------< chk_pgm_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 -- pgm_id PK of record being inserted or updated.
24 -- effective_date Effective Date of session
25 -- object_version_number Object version number of record being
26 -- inserted or updated.
27 --
28 -- Post Success
29 -- Processing continues
30 --
31 -- Post Failure
32 -- Errors handled by the procedure
33 --
34 -- Access Status
35 -- Internal table handler use only.
36 --
37 Procedure chk_pgm_id(p_pgm_id in number,
38 p_effective_date in date,
39 p_object_version_number in number) is
40 --
41 l_proc varchar2(72) := g_package||'chk_pgm_id';
42 l_api_updating boolean;
43 --
44 Begin
45 --
46 hr_utility.set_location('Entering:'||l_proc, 5);
47 --
48 l_api_updating := ben_pgm_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_pgm_id => p_pgm_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_pgm_id,hr_api.g_number)
55 <> ben_pgm_shd.g_old_rec.pgm_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_pgm_shd.constraint_error('BEN_PGM_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_pgm_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_ade_shd.constraint_error('BEN_PGM_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_pgm_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |------------------------< chk_auto_enrt_mthd_rl >-----------------------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to check that the Formula Rule is valid.
85 --
86 -- Pre Conditions
87 -- None.
88 --
89 -- In Parameters
90 -- pgm_id PK of record being inserted or updated.
91 -- auto_enrt_mthd_rl Value of formula rule id.
92 -- effective_date effective date
93 -- object_version_number Object version number of record being
94 -- inserted or updated.
95 --
96 -- Post Success
97 -- Processing continues
98 --
99 -- Post Failure
100 -- Error handled by procedure
101 --
102 -- Access Status
103 -- Internal table handler use only.
104 --
105 Procedure chk_auto_enrt_mthd_rl(p_pgm_id in number,
106 p_auto_enrt_mthd_rl in number,
107 p_effective_date in date,
108 p_object_version_number in number,
109 p_business_group_id in number) is
110 --
111 l_proc varchar2(72) := g_package||'chk_auto_enrt_mthd_rl';
112 l_api_updating boolean;
113 l_dummy varchar2(1);
114 --
115 cursor c1 is
116 select null
117 from ff_formulas_f ff ,
118 per_business_groups pbg
119 where ff.formula_id = p_auto_enrt_mthd_rl
120 and ff.formula_type_id = -146
121 and pbg.business_group_id = p_business_group_id
122 and nvl(ff.business_group_id, p_business_group_id) =
123 p_business_group_id
124 and nvl(ff.legislation_code, pbg.legislation_code) =
125 pbg.legislation_code
126 and p_effective_date
127 between ff.effective_start_date
128 and ff.effective_end_date;
129 --
130 Begin
131 --
132 hr_utility.set_location('Entering:'||l_proc, 5);
133 --
134 l_api_updating := ben_pgm_shd.api_updating
135 (p_pgm_id => p_pgm_id,
136 p_effective_date => p_effective_date,
137 p_object_version_number => p_object_version_number);
138 --
139 if (l_api_updating
140 and nvl(p_auto_enrt_mthd_rl,hr_api.g_number)
141 <> ben_pgm_shd.g_old_rec.auto_enrt_mthd_rl
142 or not l_api_updating)
143 and p_auto_enrt_mthd_rl is not null then
144 --
145 -- check if value of formula rule is valid.
146 --
147 open c1;
148 --
149 -- fetch value from cursor if it returns a record then the
150 -- formula is valid otherwise its invalid
151 --
152 fetch c1 into l_dummy;
153 if c1%notfound then
154 --
155 close c1;
156 --
157 -- raise error
158 --
159 fnd_message.set_name('BEN','BEN_91953_NVLD_AUTO_ENR_MTH_RL');
160 fnd_message.raise_error;
161 --
162 end if;
163 --
164 close c1;
165 --
166 end if;
167 --
168 hr_utility.set_location('Leaving:'||l_proc,10);
169 --
170 end chk_auto_enrt_mthd_rl;
171 --
172
173 -- --------------------------------chk_cd_rl_combination >-------------------------------|
174 -- ----------------------------------------------------------------------------
175 --
176 -- Description
177 -- This procedure is used to check the code is RULE then the rule must be
178 -- defined else it should not be.
179 --
180 -- Pre Conditions
181 -- None.
182 --
183 -- In Parameters
184 -- p_vrfy_fmly_mmbr_cd in varchar2,
185 -- p_vrfy_fmly_mmbr_rl in number
186 --
187 -- Post Success
188 -- Processing continues
189 --
190 -- Post Failure
191 -- Error handled by procedure
192 --
193 -- Access Status
194 -- Internal table handler use only.
195 --
196 procedure chk_cd_rl_combination
197 (
198 p_vrfy_fmly_mmbr_cd in varchar2,
199 p_vrfy_fmly_mmbr_rl in number ) IS
200 l_proc varchar2(72) := g_package||'chk_cd_rl_combination';
201 BEGIN
202 hr_utility.set_location('Entering:'||l_proc, 5);
203 --
204 if ( p_vrfy_fmly_mmbr_cd <> 'RL' and p_vrfy_fmly_mmbr_rl is not null)
205 then
206 fnd_message.set_name('BEN','BEN_91730_NO_RULE');
207 fnd_message.raise_error;
208 end if;
209
210 if ( p_vrfy_fmly_mmbr_cd = 'RL' and p_vrfy_fmly_mmbr_rl is null)
211 then
212 fnd_message.set_name('BEN','BEN_91731_RULE');
213 fnd_message.raise_error;
214 end if;
215 --
216 --leaving Procedure.
217 --
218 hr_utility.set_location('Leaving:'||l_proc,10);
219 --
220 end chk_cd_rl_combination;
221
222 ---
223 -- ----------------------------------------------------------------------------
224 -- |------------------------< chk_enrt_rl >-----------------------|
225 -- ----------------------------------------------------------------------------
226 --
227 -- Description
228 -- This procedure is used to check that the Formula Rule is valid.
229 --
230 -- Pre Conditions
231 -- None.
232 --
233 -- In Parameters
234 -- pgm_id PK of record being inserted or updated.
235 -- enrt_rl Value of formula rule id.
236 -- effective_date effective date
237 -- object_version_number Object version number of record being
238 -- inserted or updated.
239 --
240 -- Post Success
241 -- Processing continues
242 --
243 -- Post Failure
244 -- Error handled by procedure
245 --
246 -- Access Status
247 -- Internal table handler use only.
248 --
249 Procedure chk_enrt_rl
250 (p_pgm_id in number,
251 p_enrt_rl in number,
252 p_effective_date in date,
253 p_object_version_number in number,
254 p_business_group_id in number) is
255 --
256 l_proc varchar2(72) := g_package||'chk_enrt_rl';
257 l_api_updating boolean;
258 l_dummy varchar2(1);
259 --
260 cursor c1 is
261 select null
262 from ff_formulas_f ff ,
263 per_business_groups pbg
264 where ff.formula_id = p_enrt_rl
265 and ff.formula_type_id = -393
266 and pbg.business_group_id = p_business_group_id
267 and nvl(ff.business_group_id, p_business_group_id) =
268 p_business_group_id
269 and nvl(ff.legislation_code, pbg.legislation_code) =
270 pbg.legislation_code
271 and p_effective_date
272 between ff.effective_start_date
273 and ff.effective_end_date;
274 --
275 Begin
276 --
277 hr_utility.set_location('Entering:'||l_proc, 5);
278 --
279 l_api_updating := ben_pgm_shd.api_updating
280 (p_pgm_id => p_pgm_id,
281 p_effective_date => p_effective_date,
282 p_object_version_number => p_object_version_number);
283 --
284 if (l_api_updating
285 and nvl(p_enrt_rl,hr_api.g_number)
286 <> ben_pgm_shd.g_old_rec.enrt_rl
287 or not l_api_updating)
288 and p_enrt_rl is not null then
289 --
290 -- check if value of formula rule is valid.
291 --
292 open c1;
293 --
294 -- fetch value from cursor if it returns a record then the
295 -- formula is valid otherwise its invalid
296 --
297 fetch c1 into l_dummy;
298 if c1%notfound then
299 --
300 close c1;
301 --
302 -- raise error
303 --
304 fnd_message.set_name('BEN','BEN_91952_INVLD_ENRT_RL');
305 fnd_message.raise_error;
306 --
307 end if;
308 --
309 close c1;
310 --
311 end if;
312 --
313 hr_utility.set_location('Leaving:'||l_proc,10);
314 --
315 end chk_enrt_rl;
316 --
317 -- ----------------------------------------------------------------------------
318 -- |------------------------< chk_enrt_cvg_strt_dt_rl >-----------------------|
319 -- ----------------------------------------------------------------------------
320 --
321 -- Description
322 -- This procedure is used to check that the Formula Rule is valid.
323 --
324 -- Pre Conditions
325 -- None.
326 --
327 -- In Parameters
328 -- pgm_id PK of record being inserted or updated.
329 -- enrt_cvg_strt_dt_rl Value of formula rule id.
330 -- effective_date effective date
331 -- object_version_number Object version number of record being
332 -- inserted or updated.
333 --
334 -- Post Success
335 -- Processing continues
336 --
337 -- Post Failure
338 -- Error handled by procedure
339 --
340 -- Access Status
341 -- Internal table handler use only.
342 --
343 Procedure chk_enrt_cvg_strt_dt_rl(p_pgm_id in number,
344 p_enrt_cvg_strt_dt_rl in number,
345 p_effective_date in date,
346 p_object_version_number in number,
347 p_business_group_id in number) is
348 --
349 l_proc varchar2(72) := g_package||'chk_enrt_cvg_strt_dt_rl';
350 l_api_updating boolean;
351 l_dummy varchar2(1);
352 --
353 cursor c1 is
354 select null
355 from ff_formulas_f ff ,
356 per_business_groups pbg
357 where ff.formula_id = p_enrt_cvg_strt_dt_rl
358 and ff.formula_type_id = -29
359 and pbg.business_group_id = p_business_group_id
360 and nvl(ff.business_group_id, p_business_group_id) =
361 p_business_group_id
362 and nvl(ff.legislation_code, pbg.legislation_code) =
363 pbg.legislation_code
364 and p_effective_date
365 between ff.effective_start_date
366 and ff.effective_end_date;
367 --
368 Begin
369 --
370 hr_utility.set_location('Entering:'||l_proc, 5);
371 --
372 l_api_updating := ben_pgm_shd.api_updating
373 (p_pgm_id => p_pgm_id,
374 p_effective_date => p_effective_date,
375 p_object_version_number => p_object_version_number);
376 --
377 if (l_api_updating
378 and nvl(p_enrt_cvg_strt_dt_rl,hr_api.g_number)
379 <> ben_pgm_shd.g_old_rec.enrt_cvg_strt_dt_rl
380 or not l_api_updating)
381 and p_enrt_cvg_strt_dt_rl is not null then
382 --
383 -- check if value of formula rule is valid.
384 --
385 open c1;
386 --
387 -- fetch value from cursor if it returns a record then the
388 -- formula is valid otherwise its invalid
389 --
390 fetch c1 into l_dummy;
391 if c1%notfound then
392 --
393 close c1;
394 --
395 -- raise error
396 --
397 fnd_message.set_name('BEN','BEN_91251_INV_ENRT_START_DT_RL');
398 fnd_message.raise_error;
399 --
400 end if;
401 --
402 close c1;
403 --
404 end if;
405 --
406 hr_utility.set_location('Leaving:'||l_proc,10);
407 --
408 end chk_enrt_cvg_strt_dt_rl;
409 --
410 -- ----------------------------------------------------------------------------
411 -- |------------------------< chk_enrt_cvg_end_dt_rl >------------------------|
412 -- ----------------------------------------------------------------------------
413 --
414 -- Description
415 -- This procedure is used to check that the Formula Rule is valid.
416 --
417 -- Pre Conditions
418 -- None.
419 --
420 -- In Parameters
421 -- pgm_id PK of record being inserted or updated.
422 -- enrt_cvg_end_dt_rl Value of formula rule id.
423 -- effective_date effective date
424 -- object_version_number Object version number of record being
425 -- inserted or updated.
426 --
427 -- Post Success
428 -- Processing continues
429 --
430 -- Post Failure
431 -- Error handled by procedure
432 --
433 -- Access Status
434 -- Internal table handler use only.
435 --
436 Procedure chk_enrt_cvg_end_dt_rl(p_pgm_id in number,
437 p_enrt_cvg_end_dt_rl in number,
438 p_effective_date in date,
439 p_object_version_number in number,
440 p_business_group_id in number) is
441 --
442 l_proc varchar2(72) := g_package||'chk_enrt_cvg_end_dt_rl';
443 l_api_updating boolean;
444 l_dummy varchar2(1);
445 --
446 cursor c1 is
447 select null
448 from ff_formulas_f ff ,
449 per_business_groups pbg
450 where ff.formula_id = p_enrt_cvg_end_dt_rl
451 and ff.formula_type_id = -30
452 and pbg.business_group_id = p_business_group_id
453 and nvl(ff.business_group_id, p_business_group_id) =
454 p_business_group_id
455 and nvl(ff.legislation_code, pbg.legislation_code) =
456 pbg.legislation_code
457 and p_effective_date
458 between ff.effective_start_date
459 and ff.effective_end_date;
460 --
461 Begin
462 --
463 hr_utility.set_location('Entering:'||l_proc, 5);
464 --
465 l_api_updating := ben_pgm_shd.api_updating
466 (p_pgm_id => p_pgm_id,
467 p_effective_date => p_effective_date,
468 p_object_version_number => p_object_version_number);
469 --
470 if (l_api_updating
471 and nvl(p_enrt_cvg_end_dt_rl,hr_api.g_number)
472 <> ben_pgm_shd.g_old_rec.enrt_cvg_end_dt_rl
473 or not l_api_updating)
474 and p_enrt_cvg_end_dt_rl is not null then
475 --
476 -- check if value of formula rule is valid.
477 --
478 open c1;
479 --
480 -- fetch value from cursor if it returns a record then the
481 -- formula is valid otherwise its invalid
482 --
483 fetch c1 into l_dummy;
484 if c1%notfound then
485 --
486 close c1;
487 --
488 -- raise error
489 --
490 fnd_message.set_name('BEN','BEN_91250_INV_ENRT_END_DT_RL');
491 fnd_message.raise_error;
492 --
493 end if;
494 --
495 close c1;
496 --
497 end if;
498 --
499 hr_utility.set_location('Leaving:'||l_proc,10);
500 --
501 end chk_enrt_cvg_end_dt_rl;
502 --
503
504
505 Procedure chk_vrfy_fmly_mmbr_cd(p_pgm_id in number,
506 p_vrfy_fmly_mmbr_cd in varchar2,
507 p_effective_date in date,
508 p_object_version_number in number) is
509
510 l_proc varchar2(72) := g_package||'chk_vrfy_fmly_mmbr_cd';
511 l_api_updating boolean;
512 --
513 Begin
514 --
515 hr_utility.set_location('Entering:'||l_proc, 5);
516 --
517 l_api_updating := ben_pgm_shd.api_updating
518 (p_pgm_id => p_pgm_id,
519 p_effective_date => p_effective_date,
520 p_object_version_number => p_object_version_number);
521 --
522 if (l_api_updating
523 and p_vrfy_fmly_mmbr_cd
524 <> nvl(ben_pgm_shd.g_old_rec.vrfy_fmly_mmbr_cd,hr_api.g_varchar2)
525 or not l_api_updating)
526 and p_vrfy_fmly_mmbr_cd is not null
527 then
528 --
529 -- check if value of lookup falls within lookup type.
530 --
531
532 if hr_api.not_exists_in_hr_lookups
533 (p_lookup_type => 'BEN_FMLY_MMBR',
534 p_lookup_code => p_vrfy_fmly_mmbr_cd,
535 p_effective_date => p_effective_date) then
536 --
537 -- raise error as does not exist as lookup
538 --
539 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
540 fnd_message.set_token('FIELD', 'p_vrfy_fmly_mmbr_cd');
541 fnd_message.set_token('TYPE','BEN_FMLY_MMBR');
542 fnd_message.raise_error;
543 --
544 end if;
545
546 --
547 end if;
548 --
549 hr_utility.set_location('Leaving:'||l_proc,10);
550 --
551 end chk_vrfy_fmly_mmbr_cd;
552
553 -------------------------
554
555
556
557 Procedure chk_vrfy_fmly_mmbr_rl
558 (p_pgm_id in number
559 ,p_vrfy_fmly_mmbr_rl in number
560 ,p_business_group_id in number
561 ,p_effective_date in date
562 ,p_object_version_number in number)
563 is
564 --
565 l_proc varchar2(72) := g_package||'chk_vrfy_fmly_mmbr_rl';
566 l_api_updating boolean;
567 --
568 Begin
569 --
570 hr_utility.set_location('Entering:'||l_proc, 5);
571 --
572 l_api_updating := ben_pgm_shd.api_updating
573 (p_effective_date => p_effective_date,
574 p_pgm_id => p_pgm_id,
575 p_object_version_number => p_object_version_number);
576 --
577 if (l_api_updating
578 and nvl(p_vrfy_fmly_mmbr_rl,hr_api.g_number)
579 <> ben_pgm_shd.g_old_rec.vrfy_fmly_mmbr_rl
580 or not l_api_updating)
581 and p_vrfy_fmly_mmbr_rl is not null then
582 --
583 -- check if value of formula rule is valid.
584 --
585 if not benutils.formula_exists
586
587 (p_formula_id => p_vrfy_fmly_mmbr_rl,
588 p_formula_type_id => -21,
589 p_business_group_id => p_business_group_id,
590 p_effective_date => p_effective_date) then
591 --
592 -- raise error
593 --
594 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
595 fnd_message.set_token('ID',p_vrfy_fmly_mmbr_rl);
596 fnd_message.set_token('TYPE_ID',-21);
597 fnd_message.raise_error;
598 --
599 end if;
600 --
601 end if;
602 --
603 hr_utility.set_location('Leaving:'||l_proc,10);
604 end chk_vrfy_fmly_mmbr_rl;
605 --
606
607
608
609
610
611 -- ----------------------------------------------------------------------------
612 -- |------< chk_dpnt_cvg_strt_dt_rl >------|
613 -- ----------------------------------------------------------------------------
614 --
615 -- Description
616 -- This procedure is used to check that the Formula Rule is valid.
617 --
618 -- Pre Conditions
619 -- None.
620 --
621 -- In Parameters
622 -- pgm_id PK of record being inserted or updated.
623 -- effective_date effective date
624 -- object_version_number Object version number of record being
625 -- inserted or updated.
626 --
627 -- Post Success
628 -- Processing continues
629 --
630 -- Post Failure
631 -- Error handled by procedure
632 --
633 -- Access Status
634 -- Internal table handler use only.
635 --
636 Procedure chk_dpnt_cvg_strt_dt_rl(p_pgm_id in number,
637 p_dpnt_cvg_strt_dt_rl in number,
638 p_effective_date in date,
639 p_object_version_number in number,
640 p_business_group_id in number) is
641 --
642 l_proc varchar2(72) := g_package||'chk_dpnt_cvg_strt_dt_rl';
643 l_api_updating boolean;
644 l_dummy varchar2(1);
645 --
646 cursor c1 is
647 select null
648 from ff_formulas_f ff ,
649 per_business_groups pbg
650 where ff.formula_id = p_dpnt_cvg_strt_dt_rl
651 and ff.formula_type_id = -27
652 and pbg.business_group_id = p_business_group_id
653 and nvl(ff.business_group_id, p_business_group_id) =
654 p_business_group_id
655 and nvl(ff.legislation_code, pbg.legislation_code) =
656 pbg.legislation_code
657 and p_effective_date
658 between ff.effective_start_date
659 and ff.effective_end_date;
660 --
661 Begin
662 --
663 hr_utility.set_location('Entering:'||l_proc, 5);
664 --
665 l_api_updating := ben_pgm_shd.api_updating
666 (p_pgm_id => p_pgm_id,
667 p_effective_date => p_effective_date,
668 p_object_version_number => p_object_version_number);
669 --
670 if (l_api_updating
671 and nvl(p_dpnt_cvg_strt_dt_rl,hr_api.g_number)
672 <> ben_pgm_shd.g_old_rec.dpnt_cvg_strt_dt_rl
673 or not l_api_updating)
674 and p_dpnt_cvg_strt_dt_rl is not null then
675 --
676 -- check if value of formula rule is valid.
677 --
678 open c1;
679 --
680 -- fetch value from cursor if it returns a record then the
681 -- formula is valid otherwise its invalid
682 --
683 fetch c1 into l_dummy;
684 if c1%notfound then
685 --
686 close c1;
687 --
688 -- raise error
689 --
690 fnd_message.set_name('BEN','BEN_91252_INV_DPT_CV_ST_DT_RL');
691 fnd_message.raise_error;
692 --
693 end if;
694 --
695 close c1;
696 --
697 end if;
698 --
699 hr_utility.set_location('Leaving:'||l_proc,10);
700 --
701 end chk_dpnt_cvg_strt_dt_rl;
702 --
703 -- ----------------------------------------------------------------------------
704 -- |------< chk_rt_end_dt_rl >------|
705 -- ----------------------------------------------------------------------------
706 --
707 -- Description
708 -- This procedure is used to check that the Formula Rule is valid.
709 --
710 -- Pre Conditions
711 -- None.
712 --
713 -- In Parameters
714 -- pgm_id PK of record being inserted or updated.
715 -- effective_date effective date
716 -- object_version_number Object version number of record being
717 -- inserted or updated.
718 --
719 -- Post Success
720 -- Processing continues
721 --
722 -- Post Failure
723 -- Error handled by procedure
724 --
725 -- Access Status
726 -- Internal table handler use only.
727 --
728 Procedure chk_rt_end_dt_rl(p_pgm_id in number,
729 p_rt_end_dt_rl in number,
730 p_effective_date in date,
731 p_object_version_number in number,
732 p_business_group_id in number) is
733 --
734 l_proc varchar2(72) := g_package||'chk_rt_end_dt_rl';
735 l_api_updating boolean;
736 l_dummy varchar2(1);
737 --
738 cursor c1 is
739 select null
740 from ff_formulas_f ff ,
741 per_business_groups pbg
742 where ff.formula_id = p_rt_end_dt_rl
743 and ff.formula_type_id = -67
744 and pbg.business_group_id = p_business_group_id
745 and nvl(ff.business_group_id, p_business_group_id) =
746 p_business_group_id
747 and nvl(ff.legislation_code, pbg.legislation_code) =
748 pbg.legislation_code
749 and p_effective_date
750 between ff.effective_start_date
751 and ff.effective_end_date;
752 --
753 Begin
754 --
755 hr_utility.set_location('Entering:'||l_proc, 5);
756 --
757 l_api_updating := ben_pgm_shd.api_updating
758 (p_pgm_id => p_pgm_id,
759 p_effective_date => p_effective_date,
760 p_object_version_number => p_object_version_number);
761 --
762 if (l_api_updating
763 and nvl(p_rt_end_dt_rl,hr_api.g_number)
764 <> ben_pgm_shd.g_old_rec.rt_end_dt_rl
765 or not l_api_updating)
766 and p_rt_end_dt_rl is not null then
767 --
768 -- check if value of formula rule is valid.
769 --
770 open c1;
771 --
772 -- fetch value from cursor if it returns a record then the
773 -- formula is valid otherwise its invalid
774 --
775 fetch c1 into l_dummy;
776 if c1%notfound then
777 --
778 close c1;
779 --
780 -- raise error
781 --
782 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
783 fnd_message.set_token('ID',p_rt_end_dt_rl);
784 fnd_message.set_token('TYPE_ID',-67);
785 fnd_message.raise_error;
786 --
787 end if;
788 --
789 close c1;
790 --
791 end if;
792 --
793 hr_utility.set_location('Leaving:'||l_proc,10);
794 --
795 end chk_rt_end_dt_rl;
796 --
797 -- ----------------------------------------------------------------------------
798 -- |------< chk_rt_strt_dt_rl >------|
799 -- ----------------------------------------------------------------------------
800 --
801 -- Description
802 -- This procedure is used to check that the Formula Rule is valid.
803 --
804 -- Pre Conditions
805 -- None.
806 --
807 -- In Parameters
808 -- pgm_id PK of record being inserted or updated.
809 -- effective_date effective date
810 -- object_version_number Object version number of record being
811 -- inserted or updated.
812 --
813 -- Post Success
814 -- Processing continues
815 --
816 -- Post Failure
817 -- Error handled by procedure
818 --
819 -- Access Status
820 -- Internal table handler use only.
821 --
822 Procedure chk_rt_strt_dt_rl(p_pgm_id in number,
823 p_rt_strt_dt_rl in number,
824 p_effective_date in date,
825 p_object_version_number in number,
826 p_business_group_id in number) is
827 --
828 l_proc varchar2(72) := g_package||'chk_rt_strt_dt_rl';
829 l_api_updating boolean;
830 l_dummy varchar2(1);
831 --
832 cursor c1 is
833 select null
834 from ff_formulas_f ff ,
835 per_business_groups pbg
836 where ff.formula_id = p_rt_strt_dt_rl
837 and ff.formula_type_id = -66
838 and pbg.business_group_id = p_business_group_id
839 and nvl(ff.business_group_id, p_business_group_id) =
840 p_business_group_id
841 and nvl(ff.legislation_code, pbg.legislation_code) =
842 pbg.legislation_code
843 and p_effective_date
844 between ff.effective_start_date
845 and ff.effective_end_date;
846 --
847 Begin
848 --
849 hr_utility.set_location('Entering:'||l_proc, 5);
850 --
851 l_api_updating := ben_pgm_shd.api_updating
852 (p_pgm_id => p_pgm_id,
853 p_effective_date => p_effective_date,
854 p_object_version_number => p_object_version_number);
855 --
856 if (l_api_updating
857 and nvl(p_rt_strt_dt_rl,hr_api.g_number)
858 <> ben_pgm_shd.g_old_rec.rt_strt_dt_rl
859 or not l_api_updating)
860 and p_rt_strt_dt_rl is not null then
861 --
862 -- check if value of formula rule is valid.
863 --
864 open c1;
865 --
866 -- fetch value from cursor if it returns a record then the
867 -- formula is valid otherwise its invalid
868 --
869 fetch c1 into l_dummy;
870 if c1%notfound then
871 --
872 close c1;
873 --
874 -- raise error
875 --
876 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
877 fnd_message.set_token('ID',p_rt_strt_dt_rl);
878 fnd_message.set_token('TYPE_ID',-66);
879 fnd_message.raise_error;
880 --
881 end if;
882 --
883 close c1;
884 --
885 end if;
886 --
887 hr_utility.set_location('Leaving:'||l_proc,10);
888 --
889 end chk_rt_strt_dt_rl;
890 --
891 -- ----------------------------------------------------------------------------
892 -- |------< chk_dpnt_cvg_end_dt_rl >------|
893 -- ----------------------------------------------------------------------------
894 --
895 -- Description
896 -- This procedure is used to check that the Formula Rule is valid.
897 --
898 -- Pre Conditions
899 -- None.
900 --
901 -- In Parameters
902 -- pgm_id PK of record being inserted or updated.
903 -- effective_date effective date
904 -- object_version_number Object version number of record being
905 -- inserted or updated.
906 --
907 -- Post Success
908 -- Processing continues
909 --
910 -- Post Failure
911 -- Error handled by procedure
912 --
913 -- Access Status
914 -- Internal table handler use only.
915 --
916 Procedure chk_dpnt_cvg_end_dt_rl
917 (p_pgm_id in number,
918 p_dpnt_cvg_end_dt_rl in number,
919 p_effective_date in date,
920 p_object_version_number in number,
921 p_business_group_id in number) is
922 --
923 l_proc varchar2(72) := g_package||'chk_dpnt_cvg_end_dt_rl';
924 l_api_updating boolean;
925 l_dummy varchar2(1);
926 --
927 cursor c1 is
928 select null
929 from ff_formulas_f ff ,
930 per_business_groups pbg
931 where ff.formula_id = p_dpnt_cvg_end_dt_rl
932 and ff.formula_type_id = -28
933 and pbg.business_group_id = p_business_group_id
934 and nvl(ff.business_group_id, p_business_group_id) =
935 p_business_group_id
936 and nvl(ff.legislation_code, pbg.legislation_code) =
937 pbg.legislation_code
938 and p_effective_date
939 between ff.effective_start_date
940 and ff.effective_end_date;
941 --
942 Begin
943 --
944 hr_utility.set_location('Entering:'||l_proc, 5);
945 --
946 l_api_updating := ben_pgm_shd.api_updating
947 (p_pgm_id => p_pgm_id,
948 p_effective_date => p_effective_date,
949 p_object_version_number => p_object_version_number);
950 --
951 if (l_api_updating
952 and nvl(p_dpnt_cvg_end_dt_rl,hr_api.g_number)
953 <> ben_pgm_shd.g_old_rec.dpnt_cvg_end_dt_rl
954 or not l_api_updating)
955 and p_dpnt_cvg_end_dt_rl is not null then
956 --
957 -- check if value of formula rule is valid.
958 --
959 open c1;
960 --
961 -- fetch value from cursor if it returns a record then the
962 -- formula is valid otherwise its invalid
963 --
964 fetch c1 into l_dummy;
965 if c1%notfound then
966 --
967 close c1;
968 --
969 -- raise error
970 --
971 fnd_message.set_name('BEN','BEN_91253_INV_DPT_CV_EN_DT_RL');
972 fnd_message.raise_error;
973 --
974 end if;
975 --
976 close c1;
977 --
978 end if;
979 --
980 hr_utility.set_location('Leaving:'||l_proc,10);
981 --
982 end chk_dpnt_cvg_end_dt_rl;
983 --
984 /*
985 -- ----------------------------------------------------------------------------
986 -- |------< chk_prtn_end_dt_rl >------|
987 -- ----------------------------------------------------------------------------
988 --
989 -- Description
990 -- This procedure is used to check that the Formula Rule is valid.
991 --
992 -- Pre Conditions
993 -- None.
994 --
995 -- In Parameters
996 -- pgm_id PK of record being inserted or updated.
997 -- effective_date effective date
998 -- object_version_number Object version number of record being
999 -- inserted or updated.
1000 --
1001 -- Post Success
1002 -- Processing continues
1003 --
1004 -- Post Failure
1005 -- Error handled by procedure
1006 --
1007 -- Access Status
1008 -- Internal table handler use only.
1009 --
1010 Procedure chk_prtn_end_dt_rl(p_pgm_id in number,
1011 p_prtn_end_dt_rl in number,
1012 p_effective_date in date,
1013 p_object_version_number in number,
1014 p_business_group_id in number) is
1015 --
1016 l_proc varchar2(72) := g_package||'chk_prtn_end_dt_rl';
1017 l_api_updating boolean;
1018 l_dummy varchar2(1);
1019 --
1020 cursor c1 is
1021 select null
1022 from ff_formulas_f ff ,
1023 per_business_groups pbg
1024 where ff.formula_id = p_prtn_end_dt_rl
1025 and ff.formula_type_id = -83
1026 and pbg.business_group_id = p_business_group_id
1027 and nvl(ff.business_group_id, p_business_group_id) =
1028 p_business_group_id
1029 and nvl(ff.legislation_code, pbg.legislation_code) =
1030 pbg.legislation_code
1031 and p_effective_date between ff.effective_start_date
1032 and ff.effective_end_date;
1033 --
1034 Begin
1035 --
1036 hr_utility.set_location('Entering:'||l_proc, 5);
1037 --
1038 l_api_updating := ben_pgm_shd.api_updating
1039 (p_pgm_id => p_pgm_id,
1040 p_effective_date => p_effective_date,
1041 p_object_version_number => p_object_version_number);
1042 --
1043 if (l_api_updating
1044 and nvl(p_prtn_end_dt_rl,hr_api.g_number)
1045 <> ben_pgm_shd.g_old_rec.prtn_end_dt_rl
1046 or not l_api_updating)
1047 and p_prtn_end_dt_rl is not null then
1048 --
1049 -- check if value of formula rule is valid.
1050 --
1051 open c1;
1052 --
1053 -- fetch value from cursor if it returns a record then the
1054 -- formula is valid otherwise its invalid
1055 --
1056 fetch c1 into l_dummy;
1057 if c1%notfound then
1058 --
1059 close c1;
1060 --
1061 -- raise error
1062 --
1063 fnd_message.set_name('BEN','BEN_91254_INV_PRTN_END_DT_RL');
1064 fnd_message.raise_error;
1065 --
1066 end if;
1067 --
1068 close c1;
1069 --
1070 end if;
1071 --
1072 hr_utility.set_location('Leaving:'||l_proc,10);
1073 --
1074 end chk_prtn_end_dt_rl;
1075 --
1076 --
1077 -- ----------------------------------------------------------------------------
1078 -- |------< chk_prtn_strt_dt_rl >------|
1079 -- ----------------------------------------------------------------------------
1080 --
1081 -- Description
1082 -- This procedure is used to check that the Formula Rule is valid.
1083 --
1084 -- Pre Conditions
1085 -- None.
1086 --
1087 -- In Parameters
1088 -- pgm_id PK of record being inserted or updated.
1089 -- prtn_strt_dt_rl Value of formula rule id.
1090 -- effective_date effective date
1091 -- object_version_number Object version number of record being
1092 -- inserted or updated.
1093 --
1094 -- Post Success
1095 -- Processing continues
1096 --
1097 -- Post Failure
1098 -- Error handled by procedure
1099 --
1100 -- Access Status
1101 -- Internal table handler use only.
1102 --
1103 Procedure chk_prtn_strt_dt_rl(p_pgm_id in number,
1104 p_prtn_strt_dt_rl in number,
1105 p_effective_date in date,
1106 p_object_version_number in number,
1107 p_business_group_id in number) is
1108 --
1109 l_proc varchar2(72) := g_package||'chk_prtn_strt_dt_rl';
1110 l_api_updating boolean;
1111 l_dummy varchar2(1);
1112 --
1113 cursor c1 is
1114 select null
1115 from ff_formulas_f ff ,
1116 per_business_groups pbg
1117 where ff.formula_id = p_prtn_strt_dt_rl
1118 and ff.formula_type_id = -82
1119 and pbg.business_group_id = p_business_group_id
1120 and nvl(ff.business_group_id, p_business_group_id) =
1121 p_business_group_id
1122 and nvl(ff.legislation_code, pbg.legislation_code) =
1123 pbg.legislation_code
1124 and p_effective_date
1125 between ff.effective_start_date
1126 and ff.effective_end_date;
1127 --
1128 Begin
1129 --
1130 hr_utility.set_location('Entering:'||l_proc, 5);
1131 --
1132 l_api_updating := ben_pgm_shd.api_updating
1133 (p_pgm_id => p_pgm_id,
1134 p_effective_date => p_effective_date,
1135 p_object_version_number => p_object_version_number);
1136 --
1137 if (l_api_updating
1138 and nvl(p_prtn_strt_dt_rl,hr_api.g_number)
1139 <> ben_pgm_shd.g_old_rec.prtn_strt_dt_rl
1140 or not l_api_updating)
1141 and p_prtn_strt_dt_rl is not null then
1142 --
1143 -- check if value of formula rule is valid.
1144 --
1145 open c1;
1146 --
1147 -- fetch value from cursor if it returns a record then the
1148 -- formula is valid otherwise its invalid
1149 --
1150 fetch c1 into l_dummy;
1151 if c1%notfound then
1152 --
1153 close c1;
1154 --
1155 -- raise error
1156 --
1157 fnd_message.set_name('BEN','BEN_91255_INV_PRTN_STA_DT_RL');
1158 fnd_message.raise_error;
1159 --
1160 end if;
1161 --
1162 close c1;
1163 --
1164 end if;
1165 --
1166 hr_utility.set_location('Leaving:'||l_proc,10);
1167 --
1168 end chk_prtn_strt_dt_rl;
1169 --
1170 */
1171 --
1172 -- ----------------------------------------------------------------------------
1173 -- |------< chk_dpnt_adrs_rqd_flag >------|
1174 -- ----------------------------------------------------------------------------
1175 --
1176 -- Description
1177 -- This procedure is used to check that the Flag is in the allowed value set.
1178 --
1179 -- Pre Conditions
1180 -- None.
1181 --
1182 -- In Parameters
1183 -- pgm_id PK of record being inserted or updated.
1184 -- effective_date effective date
1185 -- object_version_number Object version number of record being
1186 -- inserted or updated.
1187 --
1188 -- Post Success
1189 -- Processing continues
1190 --
1191 -- Post Failure
1192 -- Error handled by procedure
1193 --
1194 -- Access Status
1195 -- Internal table handler use only.
1196 --
1197 Procedure chk_dpnt_adrs_rqd_flag(p_pgm_id in number,
1198 p_dpnt_adrs_rqd_flag in varchar2,
1199 p_effective_date in date,
1200 p_object_version_number in number)
1201 is
1202 --
1203 l_proc varchar2(72) := g_package||'chk_dpnt_adrs_rqd_flag';
1204 l_api_updating boolean;
1205 l_dummy varchar2(1);
1206 --
1207 Begin
1208 --
1209 hr_utility.set_location('Entering:'||l_proc, 5);
1210 --
1211 l_api_updating := ben_pgm_shd.api_updating
1212 (p_pgm_id => p_pgm_id,
1213 p_effective_date => p_effective_date,
1214 p_object_version_number => p_object_version_number);
1215 --
1216 if (l_api_updating
1217 and p_dpnt_adrs_rqd_flag
1218 <> nvl(ben_pgm_shd.g_old_rec.dpnt_adrs_rqd_flag,hr_api.g_varchar2)
1219 or not l_api_updating)
1220 and p_dpnt_adrs_rqd_flag is not null then
1221 --
1222 -- check if value of lookup falls within lookup type.
1223 --
1224 if hr_api.not_exists_in_hr_lookups
1225 (p_lookup_type => 'YES_NO',
1226 p_lookup_code => p_dpnt_adrs_rqd_flag,
1227 p_effective_date => p_effective_date) then
1228 --
1229 -- raise error as does not exist as lookup
1230 --
1231 fnd_message.set_name('BEN','BEN_91256_INV_DPT_ADRS_RQD_FLG');
1232 fnd_message.raise_error;
1233 --
1234 end if;
1235 --
1236 end if;
1237 --
1238 hr_utility.set_location('Leaving:'||l_proc,10);
1239 --
1240 end chk_dpnt_adrs_rqd_flag;
1241 --
1242 /*
1243 -- ----------------------------------------------------------------------------
1244 -- |------< chk_elig_apls_to_all_pls_flag >------|
1245 -- ----------------------------------------------------------------------------
1246 --
1247 -- Description
1248 -- This procedure is used to check that the Flag is in the allowed value set.
1249 --
1250 -- Pre Conditions
1251 -- None.
1252 --
1253 -- In Parameters
1254 -- pgm_id PK of record being inserted or updated.
1255 -- effective_date effective date
1256 -- object_version_number Object version number of record being
1257 -- inserted or updated.
1258 --
1259 -- Post Success
1260 -- Processing continues
1261 --
1262 -- Post Failure
1263 -- Error handled by procedure
1264 --
1265 -- Access Status
1266 -- Internal table handler use only.
1267 --
1268 Procedure chk_elig_apls_to_all_pls_flag(p_pgm_id in number,
1269 p_elig_apls_to_all_pls_flag in varchar2,
1270 p_effective_date in date,
1271 p_object_version_number in number)
1272 is
1273 --
1274 l_proc varchar2(72) := g_package||'elig_apls_to_all_pls_flag';
1275 l_api_updating boolean;
1276 l_dummy varchar2(1);
1277 --
1278 Begin
1279 --
1280 hr_utility.set_location('Entering:'||l_proc, 5);
1281 --
1282 l_api_updating := ben_pgm_shd.api_updating
1283 (p_pgm_id => p_pgm_id,
1284 p_effective_date => p_effective_date,
1285 p_object_version_number => p_object_version_number);
1286 --
1287 if (l_api_updating
1288 and p_elig_apls_to_all_pls_flag
1289 <> nvl(ben_pgm_shd.g_old_rec.elig_apls_to_all_pls_flag,hr_api.g_varchar2)
1290 or not l_api_updating)
1291 and p_elig_apls_to_all_pls_flag is not null then
1292 --
1293 -- check if value of lookup falls within lookup type.
1294 --
1295 if hr_api.not_exists_in_hr_lookups
1296 (p_lookup_type => 'YES_NO',
1297 p_lookup_code => p_elig_apls_to_all_pls_flag,
1298 p_effective_date => p_effective_date) then
1299 --
1300 -- raise error as does not exist as lookup
1301 --
1302 fnd_message.set_name('BEN','BEN_91257_INV_ELG_APLS_ALL_FLG');
1303 fnd_message.raise_error;
1304 --
1305 end if;
1306 --
1307 end if;
1308 --
1309 hr_utility.set_location('Leaving:'||l_proc,10);
1310 --
1311 end chk_elig_apls_to_all_pls_flag;
1312 --
1313 */
1314 -- ----------------------------------------------------------------------------
1315 -- |------< chk_dpnt_dob_rqd_flag >------|
1316 -- ----------------------------------------------------------------------------
1317 --
1318 -- Description
1319 -- This procedure is used to check that the Flag is in the allowed value set.
1320 --
1321 -- Pre Conditions
1322 -- None.
1323 --
1324 -- In Parameters
1325 -- pgm_id PK of record being inserted or updated.
1326 -- effective_date effective date
1327 -- object_version_number Object version number of record being
1328 -- inserted or updated.
1329 --
1330 -- Post Success
1331 -- Processing continues
1332 --
1333 -- Post Failure
1334 -- Error handled by procedure
1335 --
1336 -- Access Status
1337 -- Internal table handler use only.
1338 --
1339 Procedure chk_dpnt_dob_rqd_flag(p_pgm_id in number,
1340 p_dpnt_dob_rqd_flag in varchar2,
1341 p_effective_date in date,
1342 p_object_version_number in number)
1343 is
1344 --
1345 l_proc varchar2(72) := g_package||'dpnt_dob_rqd_flag';
1346 l_api_updating boolean;
1347 l_dummy varchar2(1);
1348 --
1349 Begin
1350 --
1351 hr_utility.set_location('Entering:'||l_proc, 5);
1352 --
1353 l_api_updating := ben_pgm_shd.api_updating
1354 (p_pgm_id => p_pgm_id,
1355 p_effective_date => p_effective_date,
1356 p_object_version_number => p_object_version_number);
1357 --
1358 if (l_api_updating
1359 and p_dpnt_dob_rqd_flag
1360 <> nvl(ben_pgm_shd.g_old_rec.dpnt_dob_rqd_flag,hr_api.g_varchar2)
1361 or not l_api_updating)
1362 and p_dpnt_dob_rqd_flag is not null then
1363 --
1364 -- check if value of lookup falls within lookup type.
1365 --
1366 if hr_api.not_exists_in_hr_lookups
1367 (p_lookup_type => 'YES_NO',
1368 p_lookup_code => p_dpnt_dob_rqd_flag,
1369 p_effective_date => p_effective_date) then
1370 --
1371 -- raise error as does not exist as lookup
1372 --
1373 fnd_message.set_name('BEN','BEN_91528_INV_DPT_DOB_RQD_FLG');
1374 fnd_message.raise_error;
1375 --
1376 end if;
1377 --
1378 end if;
1379 --
1380 hr_utility.set_location('Leaving:'||l_proc,10);
1381 --
1382 end chk_dpnt_dob_rqd_flag;
1383 --
1384 -- ----------------------------------------------------------------------------
1385 -- |------< chk_pgm_prvd_no_auto_enrt_flag >------|
1386 -- ----------------------------------------------------------------------------
1387 --
1388 -- Description
1389 -- This procedure is used to check that the Flag is in the allowed value set.
1390 --
1391 -- Pre Conditions
1392 -- None.
1393 --
1394 -- In Parameters
1395 -- pgm_id PK of record being inserted or updated.
1396 -- effective_date effective date
1397 -- object_version_number Object version number of record being
1398 -- inserted or updated.
1399 --
1400 -- Post Success
1401 -- Processing continues
1402 --
1403 -- Post Failure
1404 -- Error handled by procedure
1405 --
1406 -- Access Status
1407 -- Internal table handler use only.
1408 --
1409 Procedure chk_pgm_prvd_no_auto_enrt_flag(p_pgm_id in number,
1410 p_pgm_prvds_no_auto_enrt_flag in varchar2,
1411 p_effective_date in date,
1412 p_object_version_number in number)
1413 is
1414 --
1415 l_proc varchar2(72) := g_package||'chk_pgm_prvds_no_auto_enrt_flag';
1416 l_api_updating boolean;
1417 l_dummy varchar2(1);
1418 --
1419 Begin
1420 --
1421 hr_utility.set_location('Entering:'||l_proc, 5);
1422 --
1423 l_api_updating := ben_pgm_shd.api_updating
1424 (p_pgm_id => p_pgm_id,
1425 p_effective_date => p_effective_date,
1426 p_object_version_number => p_object_version_number);
1427 --
1428 if (l_api_updating
1429 and p_pgm_prvds_no_auto_enrt_flag
1430 <> nvl(ben_pgm_shd.g_old_rec.pgm_prvds_no_auto_enrt_flag,hr_api.g_varchar2)
1431 or not l_api_updating)
1432 and p_pgm_prvds_no_auto_enrt_flag is not null then
1433 --
1434 -- check if value of lookup falls within lookup type.
1435 --
1436 if hr_api.not_exists_in_hr_lookups
1437 (p_lookup_type => 'YES_NO',
1438 p_lookup_code => p_pgm_prvds_no_auto_enrt_flag,
1439 p_effective_date => p_effective_date) then
1440 --
1441 -- raise error as does not exist as lookup
1442 --
1443 fnd_message.set_name('BEN','BEN_91259_INV_PGM_NO_AUTO_FLG');
1444 fnd_message.raise_error;
1445 --
1446 end if;
1447 --
1448 end if;
1449 --
1450 hr_utility.set_location('Leaving:'||l_proc,10);
1451 --
1452 end chk_pgm_prvd_no_auto_enrt_flag;
1453 --
1454 -- ----------------------------------------------------------------------------
1455 -- |------< chk_pgm_prvd_no_dflt_enrt_flag >------|
1456 -- ----------------------------------------------------------------------------
1457 --
1458 -- Description
1459 -- This procedure is used to check that the Flag is in the allowed value set.
1460 --
1461 -- Pre Conditions
1462 -- None.
1463 --
1464 -- In Parameters
1465 -- pgm_id PK of record being inserted or updated.
1466 -- effective_date effective date
1467 -- object_version_number Object version number of record being
1468 -- inserted or updated.
1469 --
1470 -- Post Success
1471 -- Processing continues
1472 --
1473 -- Post Failure
1474 -- Error handled by procedure
1475 --
1476 -- Access Status
1477 -- Internal table handler use only.
1478 --
1479 Procedure chk_pgm_prvd_no_dflt_enrt_flag(p_pgm_id in number,
1480 p_pgm_prvds_no_dflt_enrt_flag in varchar2,
1481 p_effective_date in date,
1482 p_object_version_number in number)
1483 is
1484 --
1485 l_proc varchar2(72) := g_package||'pgm_prvd_no_dflt_enrt_flag';
1486 l_api_updating boolean;
1487 l_dummy varchar2(1);
1488 --
1489 Begin
1490 --
1491 hr_utility.set_location('Entering:'||l_proc, 5);
1492 --
1493 l_api_updating := ben_pgm_shd.api_updating
1494 (p_pgm_id => p_pgm_id,
1495 p_effective_date => p_effective_date,
1496 p_object_version_number => p_object_version_number);
1497 --
1498 if (l_api_updating
1499 and p_pgm_prvds_no_dflt_enrt_flag
1500 <> nvl(ben_pgm_shd.g_old_rec.pgm_prvds_no_dflt_enrt_flag,hr_api.g_varchar2)
1501 or not l_api_updating)
1502 and p_pgm_prvds_no_dflt_enrt_flag is not null then
1503 --
1504 -- check if value of lookup falls within lookup type.
1505 --
1506 if hr_api.not_exists_in_hr_lookups
1507 (p_lookup_type => 'YES_NO',
1508 p_lookup_code => p_pgm_prvds_no_dflt_enrt_flag,
1509 p_effective_date => p_effective_date) then
1510 --
1511 -- raise error as does not exist as lookup
1512 --
1513 fnd_message.set_name('BEN','BEN_91260_INV_PGM_NO_DFLT_FLG');
1514 fnd_message.raise_error;
1515 --
1516 end if;
1517 --
1518 end if;
1519 --
1520 hr_utility.set_location('Leaving:'||l_proc,10);
1521 --
1522 end chk_pgm_prvd_no_dflt_enrt_flag;
1523 --
1524 -- ----------------------------------------------------------------------------
1525 -- |------< chk_dpnt_legv_id_rqd_flag >------|
1526 -- ----------------------------------------------------------------------------
1527 --
1528 -- Description
1529 -- This procedure is used to check that the Flag is in the allowed value set.
1530 --
1531 -- Pre Conditions
1532 -- None.
1533 --
1534 -- In Parameters
1535 -- pgm_id PK of record being inserted or updated.
1536 -- effective_date effective date
1537 -- object_version_number Object version number of record being
1538 -- inserted or updated.
1539 --
1540 -- Post Success
1541 -- Processing continues
1542 --
1543 -- Post Failure
1544 -- Error handled by procedure
1545 --
1546 -- Access Status
1547 -- Internal table handler use only.
1548 --
1549 Procedure chk_dpnt_legv_id_rqd_flag(p_pgm_id in number,
1550 p_dpnt_legv_id_rqd_flag in varchar2,
1551 p_effective_date in date,
1552 p_object_version_number in number)
1553 is
1554 --
1555 l_proc varchar2(72) := g_package||'dpnt_legv_id_rqd_flag';
1556 l_api_updating boolean;
1557 l_dummy varchar2(1);
1558 --
1559 Begin
1560 --
1561 hr_utility.set_location('Entering:'||l_proc, 5);
1562 --
1563 l_api_updating := ben_pgm_shd.api_updating
1564 (p_pgm_id => p_pgm_id,
1565 p_effective_date => p_effective_date,
1566 p_object_version_number => p_object_version_number);
1567 --
1568 if (l_api_updating
1569 and p_dpnt_legv_id_rqd_flag
1570 <> nvl(ben_pgm_shd.g_old_rec.dpnt_legv_id_rqd_flag,hr_api.g_varchar2)
1571 or not l_api_updating)
1572 and p_dpnt_legv_id_rqd_flag is not null then
1573 --
1574 -- check if value of lookup falls within lookup type.
1575 --
1576 if hr_api.not_exists_in_hr_lookups
1577 (p_lookup_type => 'YES_NO',
1578 p_lookup_code => p_dpnt_legv_id_rqd_flag,
1579 p_effective_date => p_effective_date) then
1580 --
1581 -- raise error as does not exist as lookup
1582 --
1583 fnd_message.set_name('BEN','BEN_91261_INV_DPT_LID_RQD_FLG');
1584 fnd_message.raise_error;
1585 --
1586 end if;
1587 --
1588 end if;
1589 --
1590 hr_utility.set_location('Leaving:'||l_proc,10);
1591 --
1592 end chk_dpnt_legv_id_rqd_flag;
1593 --
1594 -- ----------------------------------------------------------------------------
1595 -- |------< chk_elig_apls_flag >------|
1596 -- ----------------------------------------------------------------------------
1597 --
1598 -- Description
1599 -- This procedure is used to check that the Flag is in the allowed value set.
1600 --
1601 -- Pre Conditions
1602 -- None.
1603 --
1604 -- In Parameters
1605 -- pgm_id PK of record being inserted or updated.
1606 -- effective_date effective date
1607 -- object_version_number Object version number of record being
1608 -- inserted or updated.
1609 --
1610 -- Post Success
1611 -- Processing continues
1612 --
1613 -- Post Failure
1614 -- Error handled by procedure
1615 --
1616 -- Access Status
1617 -- Internal table handler use only.
1618 --
1619 Procedure chk_elig_apls_flag(p_pgm_id in number,
1620 p_elig_apls_flag in varchar2,
1621 p_effective_date in date,
1622 p_object_version_number in number)
1623 is
1624 --
1625 l_proc varchar2(72) := g_package||'chk_elig_apls_flag';
1626 l_api_updating boolean;
1627 l_dummy varchar2(1);
1628 --
1629 Begin
1630 --
1631 hr_utility.set_location('Entering:'||l_proc, 5);
1632 --
1633 l_api_updating := ben_pgm_shd.api_updating
1634 (p_pgm_id => p_pgm_id,
1635 p_effective_date => p_effective_date,
1636 p_object_version_number => p_object_version_number);
1637 --
1638 if (l_api_updating
1639 and p_elig_apls_flag
1640 <> nvl(ben_pgm_shd.g_old_rec.elig_apls_flag,hr_api.g_varchar2)
1641 or not l_api_updating)
1642 and p_elig_apls_flag is not null then
1643 --
1644 -- check if value of lookup falls within lookup type.
1645 --
1646 if hr_api.not_exists_in_hr_lookups
1647 (p_lookup_type => 'YES_NO',
1648 p_lookup_code => p_elig_apls_flag,
1649 p_effective_date => p_effective_date) then
1650 --
1651 -- raise error as does not exist as lookup
1652 --
1653 fnd_message.set_name('BEN','BEN_91262_INV_ELIG_APLS_FLG');
1654 fnd_message.raise_error;
1655 --
1656 end if;
1657 --
1658 end if;
1659 --
1660 hr_utility.set_location('Leaving:'||l_proc,10);
1661 --
1662 end chk_elig_apls_flag;
1663 --
1664 -- ----------------------------------------------------------------------------
1665 -- |------< chk_uses_all_asmts_for_rts_fla >------|
1666 -- ----------------------------------------------------------------------------
1667 --
1668 -- Description
1669 -- This procedure is used to check that the Flag is in the allowed value set.
1670 --
1671 -- Pre Conditions
1672 -- None.
1673 --
1674 -- In Parameters
1675 -- pgm_id PK of record being inserted or updated.
1676 -- effective_date effective date
1677 -- object_version_number Object version number of record being
1678 -- inserted or updated.
1679 --
1680 -- Post Success
1681 -- Processing continues
1682 --
1683 -- Post Failure
1684 -- Error handled by procedure
1685 --
1686 -- Access Status
1687 -- Internal table handler use only.
1688 --
1689 Procedure chk_uses_all_asmts_for_rts_fla(p_pgm_id in number,
1690 p_uses_all_asmts_for_rts_flag in varchar2,
1691 p_effective_date in date,
1692 p_object_version_number in number)
1693 is
1694 --
1695 l_proc varchar2(72) := g_package||'chk_uses_all_asmts_for_rts_fla';
1696 l_api_updating boolean;
1697 l_dummy varchar2(1);
1698 --
1699 Begin
1700 --
1701 hr_utility.set_location('Entering:'||l_proc, 5);
1702 --
1703 l_api_updating := ben_pgm_shd.api_updating
1704 (p_pgm_id => p_pgm_id,
1705 p_effective_date => p_effective_date,
1706 p_object_version_number => p_object_version_number);
1707 --
1708 if (l_api_updating
1709 and p_uses_all_asmts_for_rts_flag
1710 <> nvl(ben_pgm_shd.g_old_rec.uses_all_asmts_for_rts_flag,hr_api.g_varchar2)
1711 or not l_api_updating)
1712 and p_uses_all_asmts_for_rts_flag is not null then
1713 --
1714 -- check if value of lookup falls within lookup type.
1715 --
1716 if hr_api.not_exists_in_hr_lookups
1717 (p_lookup_type => 'YES_NO',
1718 p_lookup_code => p_uses_all_asmts_for_rts_flag,
1719 p_effective_date => p_effective_date) then
1720 --
1721 -- raise error as does not exist as lookup
1722 --
1723 fnd_message.set_name('BEN','BEN_91263_INV_PRTT_UNCRS_FLG');
1724 fnd_message.raise_error;
1725 --
1726 end if;
1727 --
1728 end if;
1729 --
1730 hr_utility.set_location('Leaving:'||l_proc,10);
1731 --
1732 end chk_uses_all_asmts_for_rts_fla;
1733 --
1734 -- ----------------------------------------------------------------------------
1735 -- |------< chk_prtn_elig_ovrid_alwd_flag >------|
1736 -- ----------------------------------------------------------------------------
1737 --
1738 -- Description
1739 -- This procedure is used to check that the Flag is in the allowed value set.
1740 --
1741 -- Pre Conditions
1742 -- None.
1743 --
1744 -- In Parameters
1745 -- pgm_id PK of record being inserted or updated.
1746 -- effective_date effective date
1747 -- object_version_number Object version number of record being
1748 -- inserted or updated.
1749 --
1750 -- Post Success
1751 -- Processing continues
1752 --
1753 -- Post Failure
1754 -- Error handled by procedure
1755 --
1756 -- Access Status
1757 -- Internal table handler use only.
1758 --
1759 Procedure chk_prtn_elig_ovrid_alwd_flag(p_pgm_id in number,
1760 p_prtn_elig_ovrid_alwd_flag in varchar2,
1761 p_effective_date in date,
1762 p_object_version_number in number)
1763 is
1764 --
1765 l_proc varchar2(72) := g_package||'chk_prtn_elig_ovrid_alwd_flag';
1766 l_api_updating boolean;
1767 l_dummy varchar2(1);
1768 --
1769 Begin
1770 --
1771 hr_utility.set_location('Entering:'||l_proc, 5);
1772 --
1773 l_api_updating := ben_pgm_shd.api_updating
1774 (p_pgm_id => p_pgm_id,
1775 p_effective_date => p_effective_date,
1776 p_object_version_number => p_object_version_number);
1777 --
1778 if (l_api_updating
1779 and p_prtn_elig_ovrid_alwd_flag
1780 <> nvl(ben_pgm_shd.g_old_rec.prtn_elig_ovrid_alwd_flag,hr_api.g_varchar2)
1781 or not l_api_updating)
1782 and p_prtn_elig_ovrid_alwd_flag is not null then
1783 --
1784 -- check if value of lookup falls within lookup type.
1785 --
1786 if hr_api.not_exists_in_hr_lookups
1787 (p_lookup_type => 'YES_NO',
1788 p_lookup_code => p_prtn_elig_ovrid_alwd_flag,
1789 p_effective_date => p_effective_date) then
1790 --
1791 -- raise error as does not exist as lookup
1792 --
1793 fnd_message.set_name('BEN','BEN_91265_INV_PRTN_ELG_OVR_FLG');
1794 fnd_message.raise_error;
1795 --
1796 end if;
1797 --
1798 end if;
1799 --
1800 hr_utility.set_location('Leaving:'||l_proc,10);
1801 --
1802 end chk_prtn_elig_ovrid_alwd_flag;
1803
1804 --
1805 /*
1806 -- ----------------------------------------------------------------------------
1807 -- |------< chk_tmprl_fctr_apls_rts_flag >------|
1808 -- ----------------------------------------------------------------------------
1809 --
1810 -- Description
1811 -- This procedure is used to check that the Flag is in the allowed value set.
1812 --
1813 -- Pre Conditions
1814 -- None.
1815 --
1816 -- In Parameters
1817 -- pgm_id PK of record being inserted or updated.
1818 -- effective_date effective date
1819 -- object_version_number Object version number of record being
1820 -- inserted or updated.
1821 --
1822 -- Post Success
1823 -- Processing continues
1824 --
1825 -- Post Failure
1826 -- Error handled by procedure
1827 --
1828 -- Access Status
1829 -- Internal table handler use only.
1830 --
1831 Procedure chk_tmprl_fctr_apls_rts_flag(p_pgm_id in number,
1832 p_tmprl_fctr_apls_rts_flag in varchar2,
1833 p_effective_date in date,
1834 p_object_version_number in number)
1835 is
1836 --
1837 l_proc varchar2(72) := g_package||'chk_tmprl_fctr_apls_rts_flag';
1838 l_api_updating boolean;
1839 l_dummy varchar2(1);
1840 --
1841 Begin
1842 --
1843 hr_utility.set_location('Entering:'||l_proc, 5);
1844 --
1845 l_api_updating := ben_pgm_shd.api_updating
1846 (p_pgm_id => p_pgm_id,
1847 p_effective_date => p_effective_date,
1848 p_object_version_number => p_object_version_number);
1849 --
1850 if (l_api_updating
1851 and p_tmprl_fctr_apls_rts_flag
1852 <> nvl(ben_pgm_shd.g_old_rec.tmprl_fctr_apls_rts_flag,hr_api.g_varchar2)
1853 or not l_api_updating)
1854 and p_tmprl_fctr_apls_rts_flag is not null then
1855 --
1856 -- check if value of lookup falls within lookup type.
1857 --
1858 if hr_api.not_exists_in_hr_lookups
1859 (p_lookup_type => 'YES_NO',
1860 p_lookup_code => p_tmprl_fctr_apls_rts_flag,
1861 p_effective_date => p_effective_date) then
1862 --
1863 -- raise error as does not exist as lookup
1864 --
1865 fnd_message.set_name('BEN','BEN_91266_INV_TMP_FCTR_RTS_FLG');
1866 fnd_message.raise_error;
1867 --
1868 end if;
1869 --
1870 end if;
1871 --
1872 hr_utility.set_location('Leaving:'||l_proc,10);
1873 --
1874 end chk_tmprl_fctr_apls_rts_flag;
1875 */
1876 --
1877 -- ----------------------------------------------------------------------------
1878 -- |------< chk_pgm_use_all_asnt_elig_flag >------|
1879 -- ----------------------------------------------------------------------------
1880 --
1881 -- Description
1882 -- This procedure is used to check that the Flag is in the allowed value set.
1883 --
1884 -- Pre Conditions
1885 -- None.
1886 --
1887 -- In Parameters
1888 -- pgm_id PK of record being inserted or updated.
1889 -- effective_date effective date
1890 -- object_version_number Object version number of record being
1891 -- inserted or updated.
1892 --
1893 -- Post Success
1894 -- Processing continues
1895 --
1896 -- Post Failure
1897 -- Error handled by procedure
1898 --
1899 -- Access Status
1900 -- Internal table handler use only.
1901 --
1902 Procedure chk_pgm_use_all_asnt_elig_flag(p_pgm_id in number,
1903 p_pgm_use_all_asnts_elig_flag in varchar2,
1904 p_effective_date in date,
1905 p_object_version_number in number)
1906 is
1907 --
1908 l_proc varchar2(72) := g_package||'chk_pgm_use_all_asnt_elig_flag';
1909 l_api_updating boolean;
1910 l_dummy varchar2(1);
1911 --
1912 Begin
1913 --
1914 hr_utility.set_location('Entering:'||l_proc, 5);
1915 --
1916 l_api_updating := ben_pgm_shd.api_updating
1917 (p_pgm_id => p_pgm_id,
1918 p_effective_date => p_effective_date,
1919 p_object_version_number => p_object_version_number);
1920 --
1921 if (l_api_updating
1922 and p_pgm_use_all_asnts_elig_flag
1923 <> nvl(ben_pgm_shd.g_old_rec.pgm_use_all_asnts_elig_flag,hr_api.g_varchar2)
1924 or not l_api_updating)
1925 and p_pgm_use_all_asnts_elig_flag is not null then
1926 --
1927 -- check if value of lookup falls within lookup type.
1928 --
1929 if hr_api.not_exists_in_hr_lookups
1930 (p_lookup_type => 'YES_NO',
1931 p_lookup_code => p_pgm_use_all_asnts_elig_flag,
1932 p_effective_date => p_effective_date) then
1933 --
1934 -- raise error as does not exist as lookup
1935 --
1936 fnd_message.set_name('BEN','BEN_91267_INV_PGM_AST_ELG_FLG');
1937 fnd_message.raise_error;
1938 --
1939 end if;
1940 --
1941 end if;
1942 --
1943 hr_utility.set_location('Leaving:'||l_proc,10);
1944 --
1945 end chk_pgm_use_all_asnt_elig_flag;
1946
1947 --
1948 -- ----------------------------------------------------------------------------
1949 -- |------< chk_coord_cvg_for_all_pls_flag >------|
1950 -- ----------------------------------------------------------------------------
1951 --
1952 -- Description
1953 -- This procedure is used to check that the Flag is in the allowed value set.
1954 --
1955 -- Pre Conditions
1956 -- None.
1957 --
1958 -- In Parameters
1959 -- pgm_id PK of record being inserted or updated.
1960 -- effective_date effective date
1961 -- object_version_number Object version number of record being
1962 -- inserted or updated.
1963 --
1964 -- Post Success
1965 -- Processing continues
1966 --
1967 -- Post Failure
1968 -- Error handled by procedure
1969 --
1970 -- Access Status
1971 -- Internal table handler use only.
1972 --
1973 Procedure chk_coord_cvg_for_all_pls_flg(p_pgm_id in number,
1974 p_coord_cvg_for_all_pls_flg in varchar2,
1975 p_effective_date in date,
1976 p_object_version_number in number)
1977 is
1978 --
1979 l_proc varchar2(72) := g_package||'chk_coord_cvg_for_all_pls_flg';
1980 l_api_updating boolean;
1981 l_dummy varchar2(1);
1982 --
1983 Begin
1984 --
1985 hr_utility.set_location('Entering:'||l_proc, 5);
1986 --
1987 l_api_updating := ben_pgm_shd.api_updating
1988 (p_pgm_id => p_pgm_id,
1989 p_effective_date => p_effective_date,
1990 p_object_version_number => p_object_version_number);
1991 --
1992 if (l_api_updating
1993 and p_coord_cvg_for_all_pls_flg
1994 <> nvl(ben_pgm_shd.g_old_rec.coord_cvg_for_all_pls_flg,hr_api.g_varchar2)
1995 or not l_api_updating)
1996 and p_coord_cvg_for_all_pls_flg is not null then
1997 --
1998 -- check if value of lookup falls within lookup type.
1999 --
2000 if hr_api.not_exists_in_hr_lookups
2001 (p_lookup_type => 'YES_NO',
2002 p_lookup_code => p_coord_cvg_for_all_pls_flg,
2003 p_effective_date => p_effective_date) then
2004 --
2005 -- raise error as does not exist as lookup
2006 --
2007 fnd_message.set_name('BEN','BEN_91237_INV_COORD_CVG_FLAG');
2008 fnd_message.raise_error;
2009 --
2010 end if;
2011 --
2012 end if;
2013 --
2014 hr_utility.set_location('Leaving:'||l_proc,10);
2015 --
2016 end chk_coord_cvg_for_all_pls_flg;
2017
2018 --
2019 -- ----------------------------------------------------------------------------
2020 -- |------< chk_drvbl_fctr_dpnt_elig_flag >------|
2021 -- ----------------------------------------------------------------------------
2022 --
2023 -- Description
2024 -- This procedure is used to check that the Flag is in the allowed value set.
2025 --
2026 -- Pre Conditions
2027 -- None.
2028 --
2029 -- In Parameters
2030 -- pgm_id PK of record being inserted or updated.
2031 -- effective_date effective date
2032 -- object_version_number Object version number of record being
2033 -- inserted or updated.
2034 --
2035 -- Post Success
2036 -- Processing continues
2037 --
2038 -- Post Failure
2039 -- Error handled by procedure
2040 --
2041 -- Access Status
2042 -- Internal table handler use only.
2043 --
2044 Procedure chk_drvbl_fctr_dpnt_elig_flag(p_pgm_id in number,
2045 p_drvbl_fctr_dpnt_elig_flag in varchar2,
2046 p_effective_date in date,
2047 p_object_version_number in number)
2048 is
2049 --
2050 l_proc varchar2(72) := g_package||'chk_drvbl_fctr_dpnt_elig_flag';
2051 l_api_updating boolean;
2052 l_dummy varchar2(1);
2053 --
2054 Begin
2055 --
2056 hr_utility.set_location('Entering:'||l_proc, 5);
2057 --
2058 l_api_updating := ben_pgm_shd.api_updating
2059 (p_pgm_id => p_pgm_id,
2060 p_effective_date => p_effective_date,
2061 p_object_version_number => p_object_version_number);
2062 --
2063 if (l_api_updating
2064 and p_drvbl_fctr_dpnt_elig_flag
2065 <> nvl(ben_pgm_shd.g_old_rec.drvbl_fctr_dpnt_elig_flag,hr_api.g_varchar2)
2066 or not l_api_updating)
2067 and p_drvbl_fctr_dpnt_elig_flag is not null then
2068 --
2069 -- check if value of lookup falls within lookup type.
2070 --
2071 if hr_api.not_exists_in_hr_lookups
2072 (p_lookup_type => 'YES_NO',
2073 p_lookup_code => p_drvbl_fctr_dpnt_elig_flag,
2074 p_effective_date => p_effective_date) then
2075 --
2076 -- raise error as does not exist as lookup
2077 --
2078 fnd_message.set_name('BEN','BEN_91288_INV_DRV_FCT_DPT_FLAG');
2079 fnd_message.raise_error;
2080 --
2081 end if;
2082 --
2083 end if;
2084 --
2085 hr_utility.set_location('Leaving:'||l_proc,10);
2086 --
2087 end chk_drvbl_fctr_dpnt_elig_flag;
2088
2089 --
2090 /*
2091 -- ----------------------------------------------------------------------------
2092 -- |------< chk_mt_one_dpnt_cvg_elig_flag >------|
2093 -- ----------------------------------------------------------------------------
2094 --
2095 -- Description
2096 -- This procedure is used to check that the Flag is in the allowed value set.
2097 --
2098 -- Pre Conditions
2099 -- None.
2100 --
2101 -- In Parameters
2102 -- pgm_id PK of record being inserted or updated.
2103 -- effective_date effective date
2104 -- object_version_number Object version number of record being
2105 -- inserted or updated.
2106 --
2107 -- Post Success
2108 -- Processing continues
2109 --
2110 -- Post Failure
2111 -- Error handled by procedure
2112 --
2113 -- Access Status
2114 -- Internal table handler use only.
2115 --
2116 Procedure chk_mt_one_dpnt_cvg_elig_flag(p_pgm_id in number,
2117 p_mt_one_dpnt_cvg_elig_flag in varchar2,
2118 p_effective_date in date,
2119 p_object_version_number in number)
2120 is
2121 --
2122 l_proc varchar2(72) := g_package||'chk_mt_one_dpnt_cvg_elig_flag';
2123 l_api_updating boolean;
2124 l_dummy varchar2(1);
2125 --
2126 Begin
2127 --
2128 hr_utility.set_location('Entering:'||l_proc, 5);
2129 --
2130 l_api_updating := ben_pgm_shd.api_updating
2131 (p_pgm_id => p_pgm_id,
2132 p_effective_date => p_effective_date,
2133 p_object_version_number => p_object_version_number);
2134 --
2135 if (l_api_updating
2136 and p_mt_one_dpnt_cvg_elig_flag
2137 <> nvl(ben_pgm_shd.g_old_rec.mt_one_dpnt_cvg_elig_flag,hr_api.g_varchar2)
2138 or not l_api_updating)
2139 and p_mt_one_dpnt_cvg_elig_flag is not null then
2140 --
2141 -- check if value of lookup falls within lookup type.
2142 --
2143 if hr_api.not_exists_in_hr_lookups
2144 (p_lookup_type => 'YES_NO',
2145 p_lookup_code => p_mt_one_dpnt_cvg_elig_flag,
2146 p_effective_date => p_effective_date) then
2147 --
2148 -- raise error as does not exist as lookup
2149 --
2150 fnd_message.set_name('BEN','BEN_91289_INV_MT_ONE_ELG_FLAG');
2151 fnd_message.raise_error;
2152 --
2153 end if;
2154 --
2155 end if;
2156 --
2157 hr_utility.set_location('Leaving:'||l_proc,10);
2158 --
2159 end chk_mt_one_dpnt_cvg_elig_flag;
2160 */
2161 --
2162 -- ----------------------------------------------------------------------------
2163 -- |------< chk_drvbl_fctr_prtn_elig_flag >------|
2164 -- ----------------------------------------------------------------------------
2165 --
2166 -- Description
2167 -- This procedure is used to check that the Flag is in the allowed value set.
2168 --
2169 -- Pre Conditions
2170 -- None.
2171 --
2172 -- In Parameters
2173 -- pgm_id PK of record being inserted or updated.
2174 -- effective_date effective date
2175 -- object_version_number Object version number of record being
2176 -- inserted or updated.
2177 --
2178 -- Post Success
2179 -- Processing continues
2180 --
2181 -- Post Failure
2182 -- Error handled by procedure
2183 --
2184 -- Access Status
2185 -- Internal table handler use only.
2186 --
2187 Procedure chk_drvbl_fctr_prtn_elig_flag(p_pgm_id in number,
2188 p_drvbl_fctr_prtn_elig_flag in varchar2,
2189 p_effective_date in date,
2190 p_object_version_number in number)
2191 is
2192 --
2193 l_proc varchar2(72) := g_package||'chk_drvbl_fctr_prtn_elig_flag';
2194 l_api_updating boolean;
2195 l_dummy varchar2(1);
2196 --
2197 Begin
2198 --
2199 hr_utility.set_location('Entering:'||l_proc, 5);
2200 --
2201 l_api_updating := ben_pgm_shd.api_updating
2202 (p_pgm_id => p_pgm_id,
2203 p_effective_date => p_effective_date,
2204 p_object_version_number => p_object_version_number);
2205 --
2206 if (l_api_updating
2207 and p_drvbl_fctr_prtn_elig_flag
2208 <> nvl(ben_pgm_shd.g_old_rec.drvbl_fctr_prtn_elig_flag,hr_api.g_varchar2)
2209 or not l_api_updating)
2210 and p_drvbl_fctr_prtn_elig_flag is not null then
2211 --
2212 -- check if value of lookup falls within lookup type.
2213 --
2214 if hr_api.not_exists_in_hr_lookups
2215 (p_lookup_type => 'YES_NO',
2216 p_lookup_code => p_drvbl_fctr_prtn_elig_flag,
2217 p_effective_date => p_effective_date) then
2218 --
2219 -- raise error as does not exist as lookup
2220 --
2221 fnd_message.set_name('BEN','BEN_91290_INV_DRV_FCT_PRTN_FLG');
2222 fnd_message.raise_error;
2223 --
2224 end if;
2225 --
2226 end if;
2227 --
2228 hr_utility.set_location('Leaving:'||l_proc,10);
2229 --
2230 end chk_drvbl_fctr_prtn_elig_flag;
2231
2232 --
2233 -- ----------------------------------------------------------------------------
2234 -- |--------------------< chk_alws_unrstrctd_enrt_flag >----------------------|
2235 -- ----------------------------------------------------------------------------
2236 --
2237 -- Description
2238 -- This procedure is used to check that the Flag is in the allowed value set.
2239 --
2240 -- Pre Conditions
2241 -- None.
2242 --
2243 -- In Parameters
2244 -- pgm_id PK of record being inserted or updated.
2245 -- alws_unrstrctd_enrt_flag value of flag being checked.
2246 -- effective_date effective date
2247 -- object_version_number Object version number of record being
2248 -- inserted or updated.
2249 --
2250 -- Post Success
2251 -- Processing continues
2252 --
2253 -- Post Failure
2254 -- Error handled by procedure
2255 --
2256 -- Access Status
2257 -- Internal table handler use only.
2258 --
2259 Procedure chk_alws_unrstrctd_enrt_flag
2260 (p_pgm_id in number,
2261 p_alws_unrstrctd_enrt_flag in varchar2,
2262 p_effective_date in date,
2263 p_object_version_number in number)
2264 is
2265 --
2266 l_proc varchar2(72) := g_package||'chk_alws_unrstrctd_enrt_flag';
2267 l_api_updating boolean;
2268 l_dummy varchar2(1);
2269 --
2270 Begin
2271 --
2272 hr_utility.set_location('Entering:'||l_proc, 5);
2273 --
2274 l_api_updating := ben_pgm_shd.api_updating
2275 (p_pgm_id => p_pgm_id,
2276 p_effective_date => p_effective_date,
2277 p_object_version_number => p_object_version_number);
2278 --
2279 if (l_api_updating
2280 and p_alws_unrstrctd_enrt_flag
2281 <> nvl(ben_pgm_shd.g_old_rec.alws_unrstrctd_enrt_flag,hr_api.g_varchar2)
2282 or not l_api_updating)
2283 and p_alws_unrstrctd_enrt_flag is not null then
2284 --
2285 -- check if value of lookup falls within lookup type.
2286 --
2287 if hr_api.not_exists_in_hr_lookups
2288 (p_lookup_type => 'YES_NO',
2289 p_lookup_code => p_alws_unrstrctd_enrt_flag,
2290 p_effective_date => p_effective_date) then
2291 --
2292 -- raise error as does not exist as lookup
2293 --
2294 fnd_message.set_name('BEN','BEN_91949_ALWS_UNRSTR_ENRT_FLG');
2295 fnd_message.raise_error;
2296 --
2297 end if;
2298 --
2299 end if;
2300 --
2301 hr_utility.set_location('Leaving:'||l_proc,10);
2302 --
2303 end chk_alws_unrstrctd_enrt_flag;
2304
2305 -- ----------------------------------------------------------------------------
2306 -- |------< chk_drvbl_fctr_apls_rts_flag >------|
2307 -- ----------------------------------------------------------------------------
2308 --
2309 -- Description
2310 -- This procedure is used to check that the Flag is in the allowed value set.
2311 --
2312 -- Pre Conditions
2313 -- None.
2314 --
2315 -- In Parameters
2316 -- pgm_id PK of record being inserted or updated.
2317 -- effective_date effective date
2318 -- object_version_number Object version number of record being
2319 -- inserted or updated.
2320 --
2321 -- Post Success
2322 -- Processing continues
2323 --
2324 -- Post Failure
2325 -- Error handled by procedure
2326 --
2327 -- Access Status
2328 -- Internal table handler use only.
2329 --
2330 Procedure chk_drvbl_fctr_apls_rts_flag(p_pgm_id in number,
2331 p_drvbl_fctr_apls_rts_flag in varchar2,
2332 p_effective_date in date,
2333 p_object_version_number in number)
2334 is
2335 --
2336 l_proc varchar2(72) := g_package||'chk_drvbl_fctr_apls_rts_flag';
2337 l_api_updating boolean;
2338 l_dummy varchar2(1);
2339 --
2340 Begin
2341 --
2342 hr_utility.set_location('Entering:'||l_proc, 5);
2343 --
2344 l_api_updating := ben_pgm_shd.api_updating
2345 (p_pgm_id => p_pgm_id,
2346 p_effective_date => p_effective_date,
2347 p_object_version_number => p_object_version_number);
2348 --
2349 if (l_api_updating
2350 and p_drvbl_fctr_apls_rts_flag
2351 <> nvl(ben_pgm_shd.g_old_rec.drvbl_fctr_apls_rts_flag,hr_api.g_varchar2)
2352 or not l_api_updating)
2353 and p_drvbl_fctr_apls_rts_flag is not null then
2354 --
2355 -- check if value of lookup falls within lookup type.
2356 --
2357 if hr_api.not_exists_in_hr_lookups
2358 (p_lookup_type => 'YES_NO',
2359 p_lookup_code => p_drvbl_fctr_apls_rts_flag,
2360 p_effective_date => p_effective_date) then
2361 --
2362 -- raise error as does not exist as lookup
2363 --
2364 fnd_message.set_name('BEN','BEN_91291_INV_DRV_FCT_RTS_FLAG');
2365 fnd_message.raise_error;
2366 --
2367 end if;
2368 --
2369 end if;
2370 --
2371 hr_utility.set_location('Leaving:'||l_proc,10);
2372 --
2373 end chk_drvbl_fctr_apls_rts_flag;
2374
2375 --
2376 /*
2377 -- ----------------------------------------------------------------------------
2378 -- |------< chk_tmprl_fctr_dpnt_elig_flag >------|
2379 -- ----------------------------------------------------------------------------
2380 --
2381 -- Description
2382 -- This procedure is used to check that the Flag is in the allowed value set.
2383 --
2384 -- Pre Conditions
2385 -- None.
2386 --
2387 -- In Parameters
2388 -- pgm_id PK of record being inserted or updated.
2389 -- effective_date effective date
2390 -- object_version_number Object version number of record being
2391 -- inserted or updated.
2392 --
2393 -- Post Success
2394 -- Processing continues
2395 --
2396 -- Post Failure
2397 -- Error handled by procedure
2398 --
2399 -- Access Status
2400 -- Internal table handler use only.
2401 --
2402 Procedure chk_tmprl_fctr_dpnt_elig_flag(p_pgm_id in number,
2403 p_tmprl_fctr_dpnt_elig_flag in varchar2,
2404 p_effective_date in date,
2405 p_object_version_number in number)
2406 is
2407 --
2408 l_proc varchar2(72) := g_package||'chk_tmprl_fctr_dpnt_elig_flag';
2409 l_api_updating boolean;
2410 l_dummy varchar2(1);
2411 --
2412 Begin
2413 --
2414 hr_utility.set_location('Entering:'||l_proc, 5);
2415 --
2416 l_api_updating := ben_pgm_shd.api_updating
2417 (p_pgm_id => p_pgm_id,
2418 p_effective_date => p_effective_date,
2419 p_object_version_number => p_object_version_number);
2420 --
2421 if (l_api_updating
2422 and p_tmprl_fctr_dpnt_elig_flag
2423 <> nvl(ben_pgm_shd.g_old_rec.tmprl_fctr_dpnt_elig_flag,hr_api.g_varchar2)
2424 or not l_api_updating)
2425 and p_tmprl_fctr_dpnt_elig_flag is not null then
2426 --
2427 -- check if value of lookup falls within lookup type.
2428 --
2429 if hr_api.not_exists_in_hr_lookups
2430 (p_lookup_type => 'YES_NO',
2431 p_lookup_code => p_tmprl_fctr_dpnt_elig_flag,
2432 p_effective_date => p_effective_date) then
2433 --
2434 -- raise error as does not exist as lookup
2435 --
2436 fnd_message.set_name('BEN','BEN_91292_INV_TMP_FCT_DPT_FLAG');
2437 fnd_message.raise_error;
2438 --
2439 end if;
2440 --
2441 end if;
2442 --
2443 hr_utility.set_location('Leaving:'||l_proc,10);
2444 --
2445 end chk_tmprl_fctr_dpnt_elig_flag;
2446
2447 --
2448 -- ----------------------------------------------------------------------------
2449 -- |------< chk_tmprl_fctr_prtn_elig_flag >------|
2450 -- ----------------------------------------------------------------------------
2451 --
2452 -- Description
2453 -- This procedure is used to check that the Flag is in the allowed value set.
2454 --
2455 -- Pre Conditions
2456 -- None.
2457 --
2458 -- In Parameters
2459 -- pgm_id PK of record being inserted or updated.
2460 -- effective_date effective date
2461 -- object_version_number Object version number of record being
2462 -- inserted or updated.
2463 --
2464 -- Post Success
2465 -- Processing continues
2466 --
2467 -- Post Failure
2468 -- Error handled by procedure
2469 --
2470 -- Access Status
2471 -- Internal table handler use only.
2472 --
2473 Procedure chk_tmprl_fctr_prtn_elig_flag(p_pgm_id in number,
2474 p_tmprl_fctr_prtn_elig_flag in varchar2,
2475 p_effective_date in date,
2476 p_object_version_number in number)
2477 is
2478 --
2479 l_proc varchar2(72) := g_package||'chk_tmprl_fctr_prtn_elig_flag';
2480 l_api_updating boolean;
2481 l_dummy varchar2(1);
2482 --
2483 Begin
2484 --
2485 hr_utility.set_location('Entering:'||l_proc, 5);
2486 --
2487 l_api_updating := ben_pgm_shd.api_updating
2488 (p_pgm_id => p_pgm_id,
2489 p_effective_date => p_effective_date,
2490 p_object_version_number => p_object_version_number);
2491 --
2492 if (l_api_updating
2493 and p_tmprl_fctr_prtn_elig_flag
2494 <> nvl(ben_pgm_shd.g_old_rec.tmprl_fctr_prtn_elig_flag,hr_api.g_varchar2)
2495 or not l_api_updating)
2496 and p_tmprl_fctr_prtn_elig_flag is not null then
2497 --
2498 -- check if value of lookup falls within lookup type.
2499 --
2500 if hr_api.not_exists_in_hr_lookups
2501 (p_lookup_type => 'YES_NO',
2502 p_lookup_code => p_tmprl_fctr_prtn_elig_flag,
2503 p_effective_date => p_effective_date) then
2504 --
2505 -- raise error as does not exist as lookup
2506 --
2507 fnd_message.set_name('BEN','BEN_91293_INV_TMP_FCT_PRTN_FLG');
2508 fnd_message.raise_error;
2509 --
2510 end if;
2511 --
2512 end if;
2513 --
2514 hr_utility.set_location('Leaving:'||l_proc,10);
2515 --
2516 end chk_tmprl_fctr_prtn_elig_flag;
2517 */
2518 --
2519 -- ----------------------------------------------------------------------------
2520 -- |------< chk_dpnt_dsgn_no_ctfn_rqd_flag >------|
2521 -- ----------------------------------------------------------------------------
2522 --
2523 -- Description
2524 -- This procedure is used to check that the Flag is in the allowed value set.
2525 --
2526 -- Pre Conditions
2527 -- None.
2528 --
2529 -- In Parameters
2530 -- pgm_id PK of record being inserted or updated.
2531 -- effective_date effective date
2532 -- object_version_number Object version number of record being
2533 -- inserted or updated.
2534 --
2535 -- Post Success
2536 -- Processing continues
2537 --
2538 -- Post Failure
2539 -- Error handled by procedure
2540 --
2541 -- Access Status
2542 -- Internal table handler use only.
2543 --
2544 Procedure chk_dpnt_dsgn_no_ctfn_rqd_flag(p_pgm_id in number,
2545 p_dpnt_dsgn_no_ctfn_rqd_flag in varchar2,
2546 p_effective_date in date,
2547 p_object_version_number in number)
2548 is
2549 --
2550 l_proc varchar2(72) := g_package||'chk_dpnt_dsg_no_ctfn_rqd_flag';
2551 l_api_updating boolean;
2552 l_dummy varchar2(1);
2553 --
2554 Begin
2555 --
2556 hr_utility.set_location('Entering:'||l_proc, 5);
2557 --
2558 l_api_updating := ben_pgm_shd.api_updating
2559 (p_pgm_id => p_pgm_id,
2560 p_effective_date => p_effective_date,
2561 p_object_version_number => p_object_version_number);
2562 --
2563 if (l_api_updating
2564 and p_dpnt_dsgn_no_ctfn_rqd_flag
2565 <> nvl(ben_pgm_shd.g_old_rec.dpnt_dsgn_no_ctfn_rqd_flag,hr_api.g_varchar2)
2566 or not l_api_updating)
2567 and p_dpnt_dsgn_no_ctfn_rqd_flag is not null then
2568 --
2569 -- check if value of lookup falls within lookup type.
2570 --
2571 if hr_api.not_exists_in_hr_lookups
2572 (p_lookup_type => 'YES_NO',
2573 p_lookup_code => p_dpnt_dsgn_no_ctfn_rqd_flag,
2574 p_effective_date => p_effective_date) then
2575 --
2576 -- raise error as does not exist as lookup
2577 --
2578 fnd_message.set_name('BEN','BEN_91294_INV_NO_CTFN_RQD_FLAG');
2579 fnd_message.raise_error;
2580 --
2581 end if;
2582 --
2583 end if;
2584 --
2585 hr_utility.set_location('Leaving:'||l_proc,10);
2586 --
2587 end chk_dpnt_dsgn_no_ctfn_rqd_flag;
2588
2589 --
2590 -- ----------------------------------------------------------------------------
2591 -- |------< chk_trk_inelig_per_flag >------|
2592 -- ----------------------------------------------------------------------------
2593 --
2594 -- Description
2595 -- This procedure is used to check that the Flag is in the allowed value set.
2596 --
2597 -- Pre Conditions
2598 -- None.
2599 --
2600 -- In Parameters
2601 -- pgm_id PK of record being inserted or updated.
2602 -- effective_date effective date
2603 -- object_version_number Object version number of record being
2604 -- inserted or updated.
2605 --
2606 -- Post Success
2607 -- Processing continues
2608 --
2609 -- Post Failure
2610 -- Error handled by procedure
2611 --
2612 -- Access Status
2613 -- Internal table handler use only.
2614 --
2615 Procedure chk_trk_inelig_per_flag(p_pgm_id in number,
2616 p_trk_inelig_per_flag in varchar2,
2617 p_effective_date in date,
2618 p_object_version_number in number)
2619 is
2620 --
2621 l_proc varchar2(72) := g_package||'chk_trk_inelig_per_flag';
2622 l_api_updating boolean;
2623 l_dummy varchar2(1);
2624 --
2625 Begin
2626 --
2627 hr_utility.set_location('Entering:'||l_proc, 5);
2628 --
2629 l_api_updating := ben_pgm_shd.api_updating
2630 (p_pgm_id => p_pgm_id,
2631 p_effective_date => p_effective_date,
2632 p_object_version_number => p_object_version_number);
2633 --
2634 if (l_api_updating
2635 and p_trk_inelig_per_flag
2636 <> nvl(ben_pgm_shd.g_old_rec.trk_inelig_per_flag,hr_api.g_varchar2)
2637 or not l_api_updating)
2638 and p_trk_inelig_per_flag is not null then
2639 --
2640 -- check if value of lookup falls within lookup type.
2641 --
2642 if hr_api.not_exists_in_hr_lookups
2643 (p_lookup_type => 'YES_NO',
2644 p_lookup_code => p_trk_inelig_per_flag,
2645 p_effective_date => p_effective_date) then
2646 --
2647 -- raise error as does not exist as lookup
2648 --
2649 fnd_message.set_name('BEN','BEN_91295_INV_TRK_INELG_FLAG');
2650 fnd_message.raise_error;
2651 --
2652 end if;
2653 --
2654 end if;
2655 --
2656 hr_utility.set_location('Leaving:'||l_proc,10);
2657 --
2658 end chk_trk_inelig_per_flag;
2659
2660 -- ----------------------------------------------------------------------------
2661 -- |------< chk_pgm_stat_cd_not_null >------|
2662 -- ----------------------------------------------------------------------------
2663 --
2664 -- Description
2665 -- This function is used to return an "Active" program status if
2666 -- left null upon insert
2667 --
2668 -- Pre Conditions
2669 -- None.
2670 --
2671 -- In Parameters
2672 -- p_pgm_stat_cd is program status of record being inserted.
2673 -- p_effective_date is effective date
2674 --
2675 -- Return Value
2676 -- Pgm_stat_cd that is not null
2677 --
2678 -- Post Success
2679 -- Processing continues
2680 --
2681 -- Post Failure
2682 -- Errors handled by the function
2683 --
2684 -- Access Status
2685 -- Internal table handler use only.
2686 --
2687 -- ----------------------------------------------------------------------------
2688 Function chk_pgm_stat_cd_not_null( p_pgm_stat_cd in varchar2
2689 ,p_effective_date in date)
2690 return varchar2
2691 is
2692 l_proc varchar2(72) := g_package||'chk_pgm_stat_cd_not_null';
2693 l_pgm_stat varchar2(30);
2694 cursor c1 is select lookup_code
2695 from hr_lookups
2696 where lookup_type = 'BEN_STAT'
2697 and p_effective_date between start_date_active
2698 and end_date_active
2699 and enabled_flag = 'Y';
2700 --
2701 Begin
2702 hr_utility.set_location('Entering:'||l_proc, 5);
2703 if p_pgm_stat_cd is null then
2704 open c1;
2705 fetch c1 into l_pgm_stat;
2706 if c1%notfound then
2707 close c1;
2708 -- raise error as problem getting pgm_stat_cd
2709 --
2710 fnd_message.set_name('BEN','BEN_91350_PGM_STATUS_RQD');
2711 fnd_message.raise_error;
2712 end if;
2713 close c1;
2714 hr_utility.set_location('Leaving:'||l_proc, 10);
2715 return l_pgm_stat;
2716 else
2717 hr_utility.set_location('Leaving:'||l_proc, 15);
2718 return p_pgm_stat_cd;
2719 end if;
2720 End chk_pgm_stat_cd_not_null;
2721 --
2722
2723 -- ----------------------------------------------------------------------------
2724 -- |------------------------< chk_name_unique >---------------------------------|
2725 -- ----------------------------------------------------------------------------
2726 --
2727 -- Description
2728 -- ensure that not two programs have the same name
2729 --
2730 -- Pre Conditions
2731 -- None.
2732 --
2733 -- In Parameters
2734 -- p_name is program name
2735 -- p_pgm_id is program id
2736 -- p_business_group_id
2737 --
2738 -- Post Success
2739 -- Processing continues
2740 --
2741 -- Post Failure
2742 -- Errors handled by the procedure
2743 --
2744 -- Access Status
2745 -- Internal table handler use only.
2746 --
2747 -- ----------------------------------------------------------------------------
2748 Procedure chk_name_unique
2749 ( p_pgm_id in varchar2
2750 ,p_name in varchar2
2751 ,p_business_group_id in number)
2752 is
2753 l_proc varchar2(72) := g_package||'chk_name_unique';
2754 l_dummy char(1);
2755 cursor c1 is select null
2756 from ben_pgm_f
2757 Where pgm_id <> nvl(p_pgm_id,-1)
2758 and name = p_name
2759 and business_group_id = p_business_group_id;
2760 --
2761 Begin
2762 hr_utility.set_location('Entering:'||l_proc, 5);
2763 --
2764 open c1;
2765 fetch c1 into l_dummy;
2766 if c1%found then
2767 close c1;
2768 fnd_message.set_name('BEN','BEN_91009_NAME_NOT_UNIQUE');
2769 fnd_message.raise_error;
2770 end if;
2771 --
2772 hr_utility.set_location('Leaving:'||l_proc, 15);
2773 End chk_name_unique;
2774 --
2775 /*
2776 -- ----------------------------------------------------------------------------
2777 -- |-------------------< chk_eligibility_defined >----------------------------|
2778 -- ----------------------------------------------------------------------------
2779 --
2780 -- Description
2781 --
2782 -- Pre Conditions
2783 -- None.
2784 --
2785 -- In Parameters
2786 -- p_elig_apls_flag
2787 -- p_pgm_id
2788 -- p_effective_date
2789 -- p_business_group_id
2790 --
2791 -- Post Success
2792 -- Processing continues
2793 --
2794 -- Post Failure
2795 -- Errors handled by the procedure
2796 --
2797 -- Access Status
2798 -- Internal table handler use only.
2799 --
2800 -- ----------------------------------------------------------------------------
2801 Procedure chk_eligibility_defined
2802 ( p_elig_apls_flag in varchar2
2803 ,p_pgm_id in number
2804 ,p_effective_date in date
2805 ,p_business_group_id in number)
2806 is
2807 l_proc varchar2(72) := g_package||' chk_eligibility_defined ';
2808 l_dummy char(1);
2809 cursor c1 is select null
2810 from ben_prtn_elig_f prtn
2811 ,ben_prtn_elig_prfl_f prfl
2812 where prtn.pgm_id = p_pgm_id
2813 and prtn.business_group_id = p_business_group_id
2814 and p_effective_date between prtn.effective_start_date
2815 and prtn.effective_end_date
2816 and prtn.prtn_elig_id = prfl.prtn_elig_id
2817 and prfl.business_group_id = p_business_group_id
2818 and p_effective_date between prfl.effective_start_date
2819 and prfl.effective_end_date;
2820
2821 cursor c2 is select null
2822 from ben_prtn_elig_f prtn
2823 ,ben_eligy_rl_f rl
2824 where prtn.pgm_id = p_pgm_id
2825 and prtn.business_group_id = p_business_group_id
2826 and p_effective_date between prtn.effective_start_date
2827 and prtn.effective_end_date
2828 and prtn.prtn_elig_id = rl.prtn_elig_id
2829 and rl.business_group_id = p_business_group_id
2830 and p_effective_date between rl.effective_start_date
2831 and rl.effective_end_date;
2832 --
2833 Begin
2834 --
2835 hr_utility.set_location('Entering:'||l_proc, 5);
2836 --
2837 if p_elig_apls_flag = 'Y' then
2838 --
2839 -- check to see if there is a profile set up...if so return
2840 --
2841 open c1;
2842 fetch c1 into l_dummy;
2843 if c1%found then
2844 close c1;
2845 hr_utility.set_location('Leaving:'||l_proc, 9);
2846 return;
2847 end if;
2848 close c1;
2849 --
2850 -- check to see if there is a profile rule set up...if so return
2851 --
2852 open c2;
2853 fetch c2 into l_dummy;
2854 if c2%found then
2855 close c2;
2856 hr_utility.set_location('Leaving:'||l_proc, 10);
2857 return;
2858 end if;
2859 close c2;
2860 --
2861 -- if have not returned out then raise error
2862 --
2863 fnd_message.set_name('BEN','BEN_91351_PGM_ELIG_APLS');
2864 fnd_message.raise_error;
2865 end if;
2866 --
2867 hr_utility.set_location('Leaving:'||l_proc, 15);
2868 --
2869 End chk_eligibility_defined;
2870 --
2871 */
2872 -- ----------------------------------------------------------------------------
2873 -- |-----------------------< chk_enrt_mthd_cd >-------------------------------|
2874 -- ----------------------------------------------------------------------------
2875 --
2876 -- Description
2877 -- This procedure is used to check that the lookup value is valid.
2878 --
2879 -- Pre Conditions
2880 -- None.
2881 --
2882 -- In Parameters
2883 -- pgm_id PK of record being inserted or updated.
2884 -- enrt_mthd_cd Value of lookup code.
2885 -- effective_date effective date
2886 -- object_version_number Object version number of record being
2887 -- inserted or updated.
2888 --
2889 -- Post Success
2890 -- Processing continues
2891 --
2892 -- Post Failure
2893 -- Error handled by procedure
2894 --
2895 -- Access Status
2896 -- Internal table handler use only.
2897 --
2898 Procedure chk_enrt_mthd_cd
2899 (p_pgm_id in number,
2900 p_enrt_mthd_cd in varchar2,
2901 p_effective_date in date,
2902 p_object_version_number in number) is
2903 --
2904 l_proc varchar2(72) := g_package||'chk_enrt_mthd_cd';
2905 l_api_updating boolean;
2906 --
2907 Begin
2908 --
2909 hr_utility.set_location('Entering:'||l_proc, 5);
2910
2911 --
2912 l_api_updating := ben_pgm_shd.api_updating
2913 (p_pgm_id => p_pgm_id,
2914 p_effective_date => p_effective_date,
2915 p_object_version_number => p_object_version_number);
2916 --
2917 if (l_api_updating
2918 and p_enrt_mthd_cd
2919 <> nvl(ben_pgm_shd.g_old_rec.enrt_mthd_cd,hr_api.g_varchar2)
2920 or not l_api_updating)
2921 and p_enrt_mthd_cd is not null then
2922 --
2923 -- check if value of lookup falls within lookup type.
2924 --
2925 --
2926 if hr_api.not_exists_in_hr_lookups
2927 (p_lookup_type => 'BEN_ENRT_MTHD',
2928 p_lookup_code => p_enrt_mthd_cd,
2929 p_effective_date => p_effective_date) then
2930 --
2931 -- raise error as does not exist as lookup
2932 --
2933 fnd_message.set_name('BEN','BEN_91951_INVLD_ENRT_MTHD_CD');
2934 fnd_message.raise_error;
2935 --
2936 end if;
2937 --
2938 end if;
2939 --
2940 hr_utility.set_location('Leaving:'||l_proc,10);
2941 --
2942 end chk_enrt_mthd_cd;
2943 --
2944 -- ----------------------------------------------------------------------------
2945 -- |-----------------------< chk_poe_lvl_cd >-------------------------------|
2946 -- ----------------------------------------------------------------------------
2947 --
2948 -- Description
2949 -- This procedure is used to check that the lookup value is valid.
2950 --
2951 -- Pre Conditions
2952 -- None.
2953 --
2954 -- In Parameters
2955 -- pgm_id PK of record being inserted or updated.
2956 -- poe_lvl_cd Value of lookup code.
2957 -- effective_date effective date
2958 -- object_version_number Object version number of record being
2959 -- inserted or updated.
2960 --
2961 -- Post Success
2962 -- Processing continues
2963 --
2964 -- Post Failure
2965 -- Error handled by procedure
2966 --
2967 -- Access Status
2968 -- Internal table handler use only.
2969 --
2970 Procedure chk_poe_lvl_cd
2971 (p_pgm_id in number,
2972 p_poe_lvl_cd in varchar2,
2973 p_effective_date in date,
2974 p_object_version_number in number) is
2975 --
2976 l_proc varchar2(72) := g_package||'chk_poe_lvl_cd';
2977 l_api_updating boolean;
2978 --
2979 Begin
2980 --
2981 hr_utility.set_location('Entering:'||l_proc, 5);
2982
2983 --
2984 l_api_updating := ben_pgm_shd.api_updating
2985 (p_pgm_id => p_pgm_id,
2986 p_effective_date => p_effective_date,
2987 p_object_version_number => p_object_version_number);
2988 --
2989 if (l_api_updating
2990 and p_poe_lvl_cd
2991 <> nvl(ben_pgm_shd.g_old_rec.poe_lvl_cd,hr_api.g_varchar2)
2992 or not l_api_updating)
2993 and p_poe_lvl_cd is not null then
2994 --
2995 -- check if value of lookup falls within lookup type.
2996 --
2997 --
2998 if hr_api.not_exists_in_hr_lookups
2999 (p_lookup_type => 'BEN_POE_LVL',
3000 p_lookup_code => p_poe_lvl_cd,
3001 p_effective_date => p_effective_date) then
3002 --
3003 -- raise error as does not exist as lookup
3004 --
3005 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3006 fnd_message.set_token('FIELD','p_poe_lvl_cd');
3007 fnd_message.set_token('TYPE','BEN_POE_LVL');
3008 fnd_message.raise_error;
3009 --
3010 end if;
3011 --
3012 end if;
3013 --
3014 hr_utility.set_location('Leaving:'||l_proc,10);
3015 --
3016 end chk_poe_lvl_cd;
3017 --
3018 -- ----------------------------------------------------------------------------
3019 -- |-------------------------< chk_enrt_cd >----------------------------------|
3020 -- ----------------------------------------------------------------------------
3021 --
3022 -- Description
3023 -- This procedure is used to check that the lookup value is valid.
3024 --
3025 -- Pre Conditions
3026 -- None.
3027 --
3028 -- In Parameters
3029 -- pgm_id PK of record being inserted or updated.
3030 -- enrt_cd Value of lookup code.
3031 -- effective_date effective date
3032 -- object_version_number Object version number of record being
3033 -- inserted or updated.
3034 --
3035 -- Post Success
3036 -- Processing continues
3037 --
3038 -- Post Failure
3039 -- Error handled by procedure
3040 --
3041 -- Access Status
3042 -- Internal table handler use only.
3043 --
3044 Procedure chk_enrt_cd(p_pgm_id in number,
3045 p_enrt_cd in varchar2,
3046 p_effective_date in date,
3047 p_object_version_number in number) is
3048 --
3049 l_proc varchar2(72) := g_package||'chk_enrt_cd';
3050 l_api_updating boolean;
3051 --
3052 Begin
3053 --
3054 hr_utility.set_location('Entering:'||l_proc, 5);
3055
3056 --
3057 l_api_updating := ben_pgm_shd.api_updating
3058 (p_pgm_id => p_pgm_id,
3059 p_effective_date => p_effective_date,
3060 p_object_version_number => p_object_version_number);
3061 --
3062 if (l_api_updating
3063 and p_enrt_cd
3064 <> nvl(ben_pgm_shd.g_old_rec.enrt_cd,hr_api.g_varchar2)
3065 or not l_api_updating)
3066 and p_enrt_cd is not null then
3067 --
3068 -- check if value of lookup falls within lookup type.
3069 --
3070 --
3071 if hr_api.not_exists_in_hr_lookups
3072 (p_lookup_type => 'BEN_ENRT',
3073 p_lookup_code => p_enrt_cd,
3074 p_effective_date => p_effective_date) then
3075 --
3076 -- raise error as does not exist as lookup
3077 --
3078 fnd_message.set_name('BEN','BEN_91950_INVALID_ENRT_CD');
3079 fnd_message.raise_error;
3080 --
3081 end if;
3082 --
3083 end if;
3084 --
3085 hr_utility.set_location('Leaving:'||l_proc,10);
3086 --
3087 end chk_enrt_cd;
3088 --
3089 -- ----------------------------------------------------------------------------
3090 -- |------< chk_pgm_stat_cd >------|
3091 -- ----------------------------------------------------------------------------
3092 --
3093 -- Description
3094 -- This procedure is used to check that the lookup value is valid.
3095 --
3096 -- Pre Conditions
3097 -- None.
3098 --
3099 -- In Parameters
3100 -- pgm_id PK of record being inserted or updated.
3101 -- pgm_stat_cd Value of lookup code.
3102 -- effective_date effective date
3103 -- object_version_number Object version number of record being
3104 -- inserted or updated.
3105 --
3106 -- Post Success
3107 -- Processing continues
3108 --
3109 -- Post Failure
3110 -- Error handled by procedure
3111 --
3112 -- Access Status
3113 -- Internal table handler use only.
3114 --
3115 Procedure chk_pgm_stat_cd(p_pgm_id in number,
3116 p_pgm_stat_cd in varchar2,
3117 p_effective_date in date,
3118 p_object_version_number in number) is
3119 --
3120 l_proc varchar2(72) := g_package||'chk_pgm_stat_cd';
3121 l_api_updating boolean;
3122 --
3123 Begin
3124 --
3125 hr_utility.set_location('Entering:'||l_proc, 5);
3126
3127 --
3128 l_api_updating := ben_pgm_shd.api_updating
3129 (p_pgm_id => p_pgm_id,
3130 p_effective_date => p_effective_date,
3131 p_object_version_number => p_object_version_number);
3132 --
3133 if (l_api_updating
3134 and p_pgm_stat_cd
3135 <> nvl(ben_pgm_shd.g_old_rec.pgm_stat_cd,hr_api.g_varchar2)
3136 or not l_api_updating) then
3137 --
3138 -- check if value of lookup falls within lookup type.
3139 --
3140 --
3141 if hr_api.not_exists_in_hr_lookups
3142 (p_lookup_type => 'BEN_STAT',
3143 p_lookup_code => p_pgm_stat_cd,
3144 p_effective_date => p_effective_date) then
3145 --
3146 -- raise error as does not exist as lookup
3147 --
3148 fnd_message.set_name('BEN','BEN_91217_INVLD_STAT_CD');
3149 fnd_message.raise_error;
3150 --
3151 end if;
3152 --
3153 end if;
3154 --
3155 hr_utility.set_location('Leaving:'||l_proc,10);
3156 --
3157 end chk_pgm_stat_cd;
3158 --
3159 -- ----------------------------------------------------------------------------
3160 -- |------< chk_dpnt_dsgn_lvl_cd >------|
3161 -- ----------------------------------------------------------------------------
3162 --
3163 -- Description
3164 -- This procedure is used to check that the lookup value is valid.
3165 --
3166 -- Pre Conditions
3167 -- None.
3168 --
3169 -- In Parameters
3170 -- pgm_id PK of record being inserted or updated.
3171 -- dpnt_dsgn_lvl_cd Value of lookup code.
3172 -- effective_date effective date
3173 -- object_version_number Object version number of record being
3174 -- inserted or updated.
3175 --
3176 -- Post Success
3177 -- Processing continues
3178 --
3179 -- Post Failure
3180 -- Error handled by procedure
3181 --
3182 -- Access Status
3183 -- Internal table handler use only.
3184 --
3185 Procedure chk_dpnt_dsgn_lvl_cd(p_pgm_id in number,
3186 p_dpnt_dsgn_lvl_cd in varchar2,
3187 p_effective_date in date,
3188 p_object_version_number in number) is
3189 --
3190 l_proc varchar2(72) := g_package||'chk_dpnt_dsgn_lvl_cd';
3191 l_api_updating boolean;
3192 --
3193 Begin
3194 --
3195 hr_utility.set_location('Entering:'||l_proc, 5);
3196 --
3197 l_api_updating := ben_pgm_shd.api_updating
3198 (p_pgm_id => p_pgm_id,
3199 p_effective_date => p_effective_date,
3200 p_object_version_number => p_object_version_number);
3201 --
3202 if (l_api_updating
3203 and p_dpnt_dsgn_lvl_cd
3204 <> nvl(ben_pgm_shd.g_old_rec.dpnt_dsgn_lvl_cd,hr_api.g_varchar2)
3205 or not l_api_updating) then
3206 --
3207 -- check if value of lookup falls within lookup type.
3208 --
3209 --
3210 if p_dpnt_dsgn_lvl_cd is not null then
3211 if hr_api.not_exists_in_hr_lookups
3212 (p_lookup_type => 'BEN_DPNT_DSGN_LVL',
3213 p_lookup_code => p_dpnt_dsgn_lvl_cd,
3214 p_effective_date => p_effective_date) then
3215 --
3216 -- raise error as does not exist as lookup
3217 --
3218 fnd_message.set_name('BEN','BEN_91296_INV_DPNT_DSGN_LVL_CD');
3219 fnd_message.raise_error;
3220 --
3221 end if;
3222 end if;
3223 --
3224 end if;
3225 --
3226 hr_utility.set_location('Leaving:'||l_proc,10);
3227 --
3228 end chk_dpnt_dsgn_lvl_cd;
3229 --
3230 -- ----------------------------------------------------------------------------
3231 -- |------< chk_pgm_typ_cd >------|
3232 -- ----------------------------------------------------------------------------
3233 --
3234 -- Description
3235 -- This procedure is used to check that the lookup value is valid.
3236 --
3237 -- Pre Conditions
3238 -- None.
3239 --
3240 -- In Parameters
3241 -- pgm_id PK of record being inserted or updated.
3242 -- pgm_typ_cd Value of lookup code.
3243 -- effective_date effective date
3244 -- object_version_number Object version number of record being
3245 -- inserted or updated.
3246 --
3247 -- Post Success
3248 -- Processing continues
3249 --
3250 -- Post Failure
3251 -- Error handled by procedure
3252 --
3253 -- Access Status
3254 -- Internal table handler use only.
3255 --
3256 Procedure chk_pgm_typ_cd(p_pgm_id in number,
3257 p_pgm_typ_cd in varchar2,
3258 p_effective_date in date,
3259 p_object_version_number in number) is
3260 --
3261 l_proc varchar2(72) := g_package||'chk_pgm_typ_cd';
3262 l_api_updating boolean;
3263 --
3264 Begin
3265 --
3266 hr_utility.set_location('Entering:'||l_proc, 5);
3267 --
3268 l_api_updating := ben_pgm_shd.api_updating
3269 (p_pgm_id => p_pgm_id,
3270 p_effective_date => p_effective_date,
3271 p_object_version_number => p_object_version_number);
3272 --
3273 if (l_api_updating
3274 and p_pgm_typ_cd
3275 <> nvl(ben_pgm_shd.g_old_rec.pgm_typ_cd,hr_api.g_varchar2)
3276 or not l_api_updating) then
3277 --
3278 -- check if value of lookup falls within lookup type.
3279 --
3280 if hr_api.not_exists_in_hr_lookups
3281 (p_lookup_type => 'BEN_PGM_TYP',
3282 p_lookup_code => p_pgm_typ_cd,
3283 p_effective_date => p_effective_date) then
3284 --
3285 -- raise error as does not exist as lookup
3286 --
3287 fnd_message.set_name('BEN','BEN_91297_INV_PGM_TYP_CD');
3288 fnd_message.raise_error;
3289 --
3290 end if;
3291 --
3292 end if;
3293 --
3294 hr_utility.set_location('Leaving:'||l_proc,10);
3295 --
3296 end chk_pgm_typ_cd;
3297 --
3298 -- ----------------------------------------------------------------------------
3299 -- |------< chk_dpnt_dsgn_cd >------|
3300 -- ----------------------------------------------------------------------------
3301 --
3302 -- Description
3303 -- This procedure is used to check that the lookup value is valid.
3304 -- It also checks that
3305 -- 1. if dpnt_dsgn_cd is not null then dpnt_cvg_strt_dt_cd and dpnt_cvg_end_dt_cd
3306 -- should also be not null.
3307 -- 2. if dpnt_dsgn_cd is null then dpnt_cvg_strt_dt_cd and dpnt_cvg_end_dt_cd
3308 -- should also be null.
3309 -- Pre Conditions
3310 -- None.
3311 --
3312 -- In Parameters
3313 -- pgm_id PK of record being inserted or updated.
3314 -- dpnt_dsgn_cd Value of lookup code.
3315 -- dpnt_cvg_strt_dt_cd Value of lookup code.
3316 -- dpnt_cvg_end_dt_cd Value of lookup code.
3317 -- effective_date effective date
3318 -- object_version_number Object version number of record being
3319 -- inserted or updated.
3320 --
3321 -- Post Success
3322 -- Processing continues
3323 --
3324 -- Post Failure
3325 -- Error handled by procedure
3326 --
3327 -- Access Status
3328 -- Internal table handler use only.
3329 --
3330 Procedure chk_dpnt_dsgn_cd(p_pgm_id in number,
3331 p_dpnt_dsgn_cd in varchar2,
3332 p_dpnt_cvg_strt_dt_cd in varchar2,
3333 p_dpnt_cvg_end_dt_cd in varchar2,
3334 p_effective_date in date,
3335 p_object_version_number in number) is
3336 --
3337 l_proc varchar2(72) := g_package||'chk_dpnt_dsgn_cd';
3338 l_api_updating boolean;
3339 --
3340 Begin
3341 --
3342 hr_utility.set_location('Entering:'||l_proc, 5);
3343 --
3344 l_api_updating := ben_pgm_shd.api_updating
3345 (p_pgm_id => p_pgm_id,
3346 p_effective_date => p_effective_date,
3347 p_object_version_number => p_object_version_number);
3348 --
3349 if (l_api_updating
3350 and p_dpnt_dsgn_cd
3351 <> nvl(ben_pgm_shd.g_old_rec.dpnt_dsgn_cd,hr_api.g_varchar2)
3352 or not l_api_updating) then
3353 --
3354 -- check if value of lookup falls within lookup type.
3355 --
3356 --
3357 if p_dpnt_dsgn_cd is not null then
3358 if hr_api.not_exists_in_hr_lookups
3359 (p_lookup_type => 'BEN_DPNT_DSGN',
3360 p_lookup_code => p_dpnt_dsgn_cd,
3361 p_effective_date => p_effective_date) then
3362 --
3363 -- raise error as does not exist as lookup
3364 --
3365 fnd_message.set_name('BEN','BEN_91236_INV_DPNT_DSGN_CD');
3366 fnd_message.raise_error;
3367 --
3368 end if;
3369 end if;
3370 --
3371 end if;
3372 --
3373 -- Commented out to Fix the bug 1403687
3374 /*
3375 if ( p_dpnt_dsgn_cd is not null) and
3376 (p_dpnt_cvg_strt_dt_cd is null or p_dpnt_cvg_end_dt_cd is null) then
3377
3378 fnd_message.set_name('BEN','BEN_92512_DPNDNT_CVRG_DT_RQD');
3379 fnd_message.raise_error;
3380 end if;
3381 */
3382
3383 --
3384
3385 if (p_dpnt_dsgn_cd is null) and
3386 (p_dpnt_cvg_strt_dt_cd is not null or p_dpnt_cvg_end_dt_cd is not null) then
3387 null;
3388 --fnd_message.set_name('BEN','BEN_92512_DPNDNT_CVRG_DT_RQD');
3389 --fnd_message.raise_error;
3390 end if;
3391 --
3392
3393 hr_utility.set_location('Leaving:'||l_proc,10);
3394 --
3395 end chk_dpnt_dsgn_cd;
3396 --
3397 -- ----------------------------------------------------------------------------
3398 -- |------< chk_pgm_grp_cd >------|
3399 -- ----------------------------------------------------------------------------
3400 --
3401 -- Description
3402 -- This procedure is used to check that the lookup value is valid.
3403 --
3404 -- Pre Conditions
3405 -- None.
3406 --
3407 -- In Parameters
3408 -- pgm_id PK of record being inserted or updated.
3409 -- pgm_grp_cd Value of lookup code.
3410 -- effective_date effective date
3411 -- object_version_number Object version number of record being
3412 -- inserted or updated.
3413 --
3414 -- Post Success
3415 -- Processing continues
3416 --
3417 -- Post Failure
3418 -- Error handled by procedure
3419 --
3420 -- Access Status
3421 -- Internal table handler use only.
3422 --
3423 Procedure chk_pgm_grp_cd(p_pgm_id in number,
3424 p_pgm_grp_cd in varchar2,
3425 p_effective_date in date,
3426 p_object_version_number in number) is
3427 --
3428 l_proc varchar2(72) := g_package||'chk_pgm_grp_cd';
3429 l_api_updating boolean;
3430 --
3431 Begin
3432 --
3433 hr_utility.set_location('Entering:'||l_proc, 5);
3434 --
3435 l_api_updating := ben_pgm_shd.api_updating
3436 (p_pgm_id => p_pgm_id,
3437 p_effective_date => p_effective_date,
3438 p_object_version_number => p_object_version_number);
3439 --
3440 if (l_api_updating
3441 and p_pgm_grp_cd
3442 <> nvl(ben_pgm_shd.g_old_rec.pgm_grp_cd,hr_api.g_varchar2)
3443 or not l_api_updating) then
3444 --
3445 -- check if value of lookup falls within lookup type.
3446 --
3447 --
3448 if p_pgm_grp_cd is not null then
3449 if hr_api.not_exists_in_hr_lookups
3450 (p_lookup_type => 'BEN_PGM_GRP',
3451 p_lookup_code => p_pgm_grp_cd,
3452 p_effective_date => p_effective_date) then
3453 --
3454 -- raise error as does not exist as lookup
3455 --
3456 fnd_message.set_name('BEN','BEN_91298_INV_PGM_GRP_CD');
3457 fnd_message.raise_error;
3458 --
3459 end if;
3460 end if;
3461 --
3462 end if;
3463 --
3464 hr_utility.set_location('Leaving:'||l_proc,10);
3465 --
3466 end chk_pgm_grp_cd;
3467 --
3468 -- ----------------------------------------------------------------------------
3469 -- |------< chk_acty_ref_perd_cd >------|
3470 -- ----------------------------------------------------------------------------
3471 --
3472 -- Description
3473 -- This procedure is used to check that the lookup value is valid.
3474 --
3475 -- Pre Conditions
3476 -- None.
3477 --
3478 -- In Parameters
3479 -- pgm_id PK of record being inserted or updated.
3480 -- acty_ref_perd_cd Value of lookup code.
3481 -- effective_date effective date
3482 -- object_version_number Object version number of record being
3483 -- inserted or updated.
3484 --
3485 -- Post Success
3486 -- Processing continues
3487 --
3488 -- Post Failure
3489 -- Error handled by procedure
3490 --
3491 -- Access Status
3492 -- Internal table handler use only.
3493 --
3494 Procedure chk_acty_ref_perd_cd(p_pgm_id in number,
3495 p_acty_ref_perd_cd in varchar2,
3496 p_effective_date in date,
3497 p_object_version_number in number) is
3498 --
3499 l_proc varchar2(72) := g_package||'chk_acty_ref_perd_cd';
3500 l_api_updating boolean;
3501 --
3502 Begin
3503 --
3504 hr_utility.set_location('Entering:'||l_proc, 5);
3505 --
3506 l_api_updating := ben_pgm_shd.api_updating
3507 (p_pgm_id => p_pgm_id,
3508 p_effective_date => p_effective_date,
3509 p_object_version_number => p_object_version_number);
3510 --
3511 if (l_api_updating
3512 and p_acty_ref_perd_cd
3513 <> nvl(ben_pgm_shd.g_old_rec.acty_ref_perd_cd,hr_api.g_varchar2)
3514 or not l_api_updating) then
3515 --
3516 -- check if value of lookup falls within lookup type.
3517 --
3518 --
3519 if p_acty_ref_perd_cd is not null then
3520 if hr_api.not_exists_in_hr_lookups
3521 (p_lookup_type => 'BEN_ACTY_REF_PERD',
3522 p_lookup_code => p_acty_ref_perd_cd,
3523 p_effective_date => p_effective_date) then
3524 --
3525 -- raise error as does not exist as lookup
3526 --
3527 fnd_message.set_name('BEN','BEN_91299_INV_ACTY_REF_PERD_CD');
3528 fnd_message.raise_error;
3529 --
3530 end if;
3531 end if;
3532 --
3533 end if;
3534 --
3535 hr_utility.set_location('Leaving:'||l_proc,10);
3536 --
3537 end chk_acty_ref_perd_cd;
3538 --
3539 -- ----------------------------------------------------------------------------
3540 -- |------< chk_enrt_cvg_strt_dt_cd >------|
3541 -- ----------------------------------------------------------------------------
3542 --
3543 -- Description
3544 -- This procedure is used to check that the lookup value is valid.
3545 --
3546 -- Pre Conditions
3547 -- None.
3548 --
3549 -- In Parameters
3550 -- pgm_id PK of record being inserted or updated.
3551 -- enrt_cvg_strt_dt_cd Value of lookup code.
3552 -- effective_date effective date
3553 -- object_version_number Object version number of record being
3554 -- inserted or updated.
3555 --
3556 -- Post Success
3557 -- Processing continues
3558 --
3559 -- Post Failure
3560 -- Error handled by procedure
3561 --
3562 -- Access Status
3563 -- Internal table handler use only.
3564 --
3565 Procedure chk_enrt_cvg_strt_dt_cd(p_pgm_id in number,
3566 p_enrt_cvg_strt_dt_cd in varchar2,
3567 p_effective_date in date,
3568 p_object_version_number in number) is
3569 --
3570 l_proc varchar2(72) := g_package||'chk_enrt_cvg_strt_dt_cd';
3571 l_api_updating boolean;
3572 --
3573 Begin
3574 --
3575 hr_utility.set_location('Entering:'||l_proc, 5);
3576 --
3577 l_api_updating := ben_pgm_shd.api_updating
3578 (p_pgm_id => p_pgm_id,
3579 p_effective_date => p_effective_date,
3580 p_object_version_number => p_object_version_number);
3581 --
3582 if (l_api_updating
3583 and p_enrt_cvg_strt_dt_cd
3584 <> nvl(ben_pgm_shd.g_old_rec.enrt_cvg_strt_dt_cd,hr_api.g_varchar2)
3585 or not l_api_updating) then
3586 --
3587 -- check if value of lookup falls within lookup type.
3588 --
3589 --
3590 if p_enrt_cvg_strt_dt_cd is not null then
3591 if hr_api.not_exists_in_hr_lookups
3592 (p_lookup_type => 'BEN_ENRT_CVG_STRT',
3593 p_lookup_code => p_enrt_cvg_strt_dt_cd,
3594 p_effective_date => p_effective_date) then
3595 --
3596 -- raise error as does not exist as lookup
3597 --
3598 fnd_message.set_name('BEN','BEN_91300_INV_ENRT_STRT_DT_CD');
3599 fnd_message.raise_error;
3600 --
3601 end if;
3602 end if;
3603 --
3604 end if;
3605 --
3606 hr_utility.set_location('Leaving:'||l_proc,10);
3607 --
3608 end chk_enrt_cvg_strt_dt_cd;
3609 --
3610 -- ----------------------------------------------------------------------------
3611 -- |------< chk_enrt_cvg_end_dt_cd >------|
3612 -- ----------------------------------------------------------------------------
3613 --
3614 -- Description
3615 -- This procedure is used to check that the lookup value is valid.
3616 --
3617 -- Pre Conditions
3618 -- None.
3619 --
3620 -- In Parameters
3621 -- pgm_id PK of record being inserted or updated.
3622 -- enrt_cvg_end_dt_cd Value of lookup code.
3623 -- effective_date effective date
3624 -- object_version_number Object version number of record being
3625 -- inserted or updated.
3626 --
3627 -- Post Success
3628 -- Processing continues
3629 --
3630 -- Post Failure
3631 -- Error handled by procedure
3632 --
3633 -- Access Status
3634 -- Internal table handler use only.
3635 --
3636 Procedure chk_enrt_cvg_end_dt_cd(p_pgm_id in number,
3637 p_enrt_cvg_end_dt_cd in varchar2,
3638 p_effective_date in date,
3639 p_object_version_number in number) is
3640 --
3641 l_proc varchar2(72) := g_package||'chk_enrt_cvg_end_dt_cd';
3642 l_api_updating boolean;
3643 --
3644 Begin
3645 --
3646 hr_utility.set_location('Entering:'||l_proc, 5);
3647 --
3648 l_api_updating := ben_pgm_shd.api_updating
3649 (p_pgm_id => p_pgm_id,
3650 p_effective_date => p_effective_date,
3651 p_object_version_number => p_object_version_number);
3652 --
3653 if (l_api_updating
3654 and p_enrt_cvg_end_dt_cd
3655 <> nvl(ben_pgm_shd.g_old_rec.enrt_cvg_end_dt_cd,hr_api.g_varchar2)
3656 or not l_api_updating) then
3657 --
3658 -- check if value of lookup falls within lookup type.
3659 --
3660 --
3661 if p_enrt_cvg_end_dt_cd is not null then
3662 if hr_api.not_exists_in_hr_lookups
3663 (p_lookup_type => 'BEN_ENRT_CVG_END',
3664 p_lookup_code => p_enrt_cvg_end_dt_cd,
3665 p_effective_date => p_effective_date) then
3666 --
3667 -- raise error as does not exist as lookup
3668 --
3669 fnd_message.set_name('BEN','BEN_91301_INV_ENRT_END_DT_CD');
3670 fnd_message.raise_error;
3671 --
3672 end if;
3673 end if;
3674 --
3675 end if;
3676 --
3677 hr_utility.set_location('Leaving:'||l_proc,10);
3678 --
3679 end chk_enrt_cvg_end_dt_cd;
3680 /*
3681 --
3682 -- ----------------------------------------------------------------------------
3683 -- |------< chk_prtn_strt_dt_cd >------|
3684 -- ----------------------------------------------------------------------------
3685 --
3686 -- Description
3687 -- This procedure is used to check that the lookup value is valid.
3688 --
3689 -- Pre Conditions
3690 -- None.
3691 --
3692 -- In Parameters
3693 -- pgm_id PK of record being inserted or updated.
3694 -- prtn_strt_dt_cd Value of lookup code.
3695 -- effective_date effective date
3696 -- object_version_number Object version number of record being
3697 -- inserted or updated.
3698 --
3699 -- Post Success
3700 -- Processing continues
3701 --
3702 -- Post Failure
3703 -- Error handled by procedure
3704 --
3705 -- Access Status
3706 -- Internal table handler use only.
3707 --
3708 Procedure chk_prtn_strt_dt_cd(p_pgm_id in number,
3709 p_prtn_strt_dt_cd in varchar2,
3710 p_effective_date in date,
3711 p_object_version_number in number) is
3712 --
3713 l_proc varchar2(72) := g_package||'chk_prtn_strt_dt_cd';
3714 l_api_updating boolean;
3715 --
3716 Begin
3717 --
3718 hr_utility.set_location('Entering:'||l_proc, 5);
3719 --
3720 l_api_updating := ben_pgm_shd.api_updating
3721 (p_pgm_id => p_pgm_id,
3722 p_effective_date => p_effective_date,
3723 p_object_version_number => p_object_version_number);
3724 --
3725 if (l_api_updating
3726 and p_prtn_strt_dt_cd
3727 <> nvl(ben_pgm_shd.g_old_rec.prtn_strt_dt_cd,hr_api.g_varchar2)
3728 or not l_api_updating) then
3729 --
3730 -- check if value of lookup falls within lookup type.
3731 --
3732 --
3733 if p_prtn_strt_dt_cd is not null then
3734 if hr_api.not_exists_in_hr_lookups
3735 (p_lookup_type => 'BEN_PRTN_ELIG_STRT',
3736 p_lookup_code => p_prtn_strt_dt_cd,
3737 p_effective_date => p_effective_date) then
3738 --
3739 -- raise error as does not exist as lookup
3740 --
3741 fnd_message.set_name('BEN','BEN_91304_INV_PRTN_STRT_DT_CD');
3742 fnd_message.raise_error;
3743 --
3744 end if;
3745 end if;
3746 --
3747 end if;
3748 --
3749 hr_utility.set_location('Leaving:'||l_proc,10);
3750 --
3751 end chk_prtn_strt_dt_cd;
3752 --
3753 -- ----------------------------------------------------------------------------
3754 -- |------< chk_prtn_end_dt_cd >------|
3755 -- ----------------------------------------------------------------------------
3756 --
3757 -- Description
3758 -- This procedure is used to check that the lookup value is valid.
3759 --
3760 -- Pre Conditions
3761 -- None.
3762 --
3763 -- In Parameters
3764 -- pgm_id PK of record being inserted or updated.
3765 -- prtn_end_dt_cd Value of lookup code.
3766 -- effective_date effective date
3767 -- object_version_number Object version number of record being
3768 -- inserted or updated.
3769 --
3770 -- Post Success
3771 -- Processing continues
3772 --
3773 -- Post Failure
3774 -- Error handled by procedure
3775 --
3776 -- Access Status
3777 -- Internal table handler use only.
3778 --
3779 Procedure chk_prtn_end_dt_cd(p_pgm_id in number,
3780 p_prtn_end_dt_cd in varchar2,
3781 p_effective_date in date,
3782 p_object_version_number in number) is
3783 --
3784 l_proc varchar2(72) := g_package||'chk_prtn_end_dt_cd';
3785 l_api_updating boolean;
3786 --
3787 Begin
3788 --
3789 hr_utility.set_location('Entering:'||l_proc, 5);
3790 --
3791 l_api_updating := ben_pgm_shd.api_updating
3792 (p_pgm_id => p_pgm_id,
3793 p_effective_date => p_effective_date,
3794 p_object_version_number => p_object_version_number);
3795 --
3796 if (l_api_updating
3797 and p_prtn_end_dt_cd
3798 <> nvl(ben_pgm_shd.g_old_rec.prtn_end_dt_cd,hr_api.g_varchar2)
3799 or not l_api_updating) then
3800 --
3801 -- check if value of lookup falls within lookup type.
3802 --
3803 --
3804 if p_prtn_end_dt_cd is not null then
3805 if hr_api.not_exists_in_hr_lookups
3806 (p_lookup_type => 'BEN_PRTN_ELIG_END',
3807 p_lookup_code => p_prtn_end_dt_cd,
3808 p_effective_date => p_effective_date) then
3809 --
3810 -- raise error as does not exist as lookup
3811 --
3812 fnd_message.set_name('BEN','BEN_91305_INV_PRTN_END_DT_CD');
3813 fnd_message.raise_error;
3814 --
3815 end if;
3816 end if;
3817 --
3818 end if;
3819 --
3820 hr_utility.set_location('Leaving:'||l_proc,10);
3821 --
3822 end chk_prtn_end_dt_cd;
3823 */
3824 --
3825 -- ----------------------------------------------------------------------------
3826 -- |------< chk_pgm_uom >------|
3827 -- ----------------------------------------------------------------------------
3828 --
3829 -- Description
3830 -- This procedure is used to check that the fk to fnd_currencies is valid.
3831 --
3832 -- Pre Conditions
3833 -- None.
3834 --
3835 -- In Parameters
3836 -- pgm_id PK of record being inserted or updated.
3837 -- pgm_uom Value of lookup code.
3838 -- effective_date effective date
3839 -- object_version_number Object version number of record being
3840 -- inserted or updated.
3841 --
3842 -- Post Success
3843 -- Processing continues
3844 --
3845 -- Post Failure
3846 -- Error handled by procedure
3847 --
3848 -- Access Status
3849 -- Internal table handler use only.
3850 --
3851 Procedure chk_pgm_uom(p_pgm_id in number,
3852 p_pgm_uom in varchar2,
3853 p_effective_date in date,
3854 p_object_version_number in number) is
3855 --
3856 cursor c1 is select currency_code
3857 from fnd_currencies_vl
3858 where currency_code = p_pgm_uom
3859 and enabled_flag = 'Y';
3860 --
3861 l_proc varchar2(72) := g_package||'chk_pgm_uom';
3862 l_api_updating boolean;
3863 l_dummy varchar2(30);
3864 --
3865 Begin
3866 --
3867 hr_utility.set_location('Entering:'||l_proc, 5);
3868 --
3869
3870 l_api_updating := ben_pgm_shd.api_updating
3871 (p_pgm_id => p_pgm_id,
3872 p_effective_date => p_effective_date,
3873 p_object_version_number => p_object_version_number);
3874 --
3875 if (l_api_updating
3876 and p_pgm_uom
3877 <> nvl(ben_pgm_shd.g_old_rec.pgm_uom,hr_api.g_varchar2)
3878 or not l_api_updating)
3879 and p_pgm_uom is not null then
3880 --
3881 -- check if value of lookup falls within fnd_currencies.
3882 --
3883 open c1;
3884 fetch c1 into l_dummy;
3885 if c1%notfound then
3886 close c1;
3887 -- raise error as currency not found
3888 --
3889 fnd_message.set_name('BEN','BEN_91306_INV_PGM_UOM');
3890 fnd_message.raise_error;
3891 end if;
3892 close c1;
3893 --
3894 end if;
3895 --
3896 hr_utility.set_location('Leaving:'||l_proc,10);
3897 --
3898 end chk_pgm_uom;
3899 --
3900 -- ----------------------------------------------------------------------------
3901 -- |--------------------------< chk_all_rules >-------------------------------|
3902 -- ----------------------------------------------------------------------------
3903 --
3904 -- Description
3905 -- This procedure is used to check that the Formula Rules are valid.
3906 --
3907 -- Pre Conditions
3908 -- None.
3909 --
3910 -- In Parameters
3911 -- pgm_id PK of record being inserted or updated.
3912 -- Dflt_step_rl Value of formula rule id.
3913 -- Scores_calc_rl Value of formula rule id.
3914 -- effective_date effective date
3915 -- object_version_number Object version number of record being
3916 -- inserted or updated.
3917 --
3918 -- Post Success
3919 -- Processing continues
3920 --
3921 -- Post Failure
3922 -- Error handled by procedure
3923 --
3924 -- Access Status
3925 -- Internal table handler use only.
3926 --
3927 Procedure chk_all_rules(p_pgm_id in number,
3928 p_business_group_id in number,
3929 p_Dflt_step_rl in number,
3930 p_Scores_calc_rl in number,
3931 p_effective_date in date,
3932 p_object_version_number in number) is
3933 --
3934 l_proc varchar2(72) := g_package||'chk_all_rules';
3935 l_api_updating boolean;
3936 l_dummy varchar2(1);
3937 --
3938 cursor c1(p_rule number,p_rule_type_id number) is
3939 select null
3940 from ff_formulas_f ff,
3941 per_business_groups pbg
3942 where ff.formula_id = p_rule
3943 and ff.formula_type_id = p_rule_type_id
3944 and pbg.business_group_id = p_business_group_id
3945 and nvl(ff.business_group_id,p_business_group_id) =
3946 p_business_group_id
3947 and nvl(ff.legislation_code,pbg.legislation_code) =
3948 pbg.legislation_code
3949 and p_effective_date
3950 between ff.effective_start_date
3951 and ff.effective_end_date;
3952 --
3953 Begin
3954 --
3955 hr_utility.set_location('Entering:'||l_proc, 5);
3956 --
3957 l_api_updating := ben_pgm_shd.api_updating
3958 (p_pgm_id => p_pgm_id,
3959 p_effective_date => p_effective_date,
3960 p_object_version_number => p_object_version_number);
3961 --
3962 if (l_api_updating
3963 and nvl(p_Dflt_step_rl,hr_api.g_number)
3964 <> ben_pgm_shd.g_old_rec.Dflt_step_rl
3965 or not l_api_updating)
3966 and p_Dflt_step_rl is not null then
3967 --
3968 -- check if value of formula rule is valid.
3969 --
3970 open c1(p_Dflt_step_rl,-449); -- BEN_DFLT_STEP
3971 --
3972 -- fetch value from cursor if it returns a record then the
3973 -- formula is valid otherwise its invalid
3974 --
3975 fetch c1 into l_dummy;
3976 if c1%notfound then
3977 --
3978 close c1;
3979 --
3980 -- raise error
3981 --
3982 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
3983 fnd_message.set_token('ID',p_Dflt_step_rl);
3984 fnd_message.set_token('TYPE_ID',-449);
3985 fnd_message.raise_error;
3986 --
3987 end if;
3988 --
3989 close c1;
3990 --
3991 end if;
3992 --
3993 if (l_api_updating
3994 and nvl(p_Scores_calc_rl,hr_api.g_number)
3995 <> ben_pgm_shd.g_old_rec.Scores_calc_rl
3996 or not l_api_updating)
3997 and p_Scores_calc_rl is not null then
3998 --
3999 -- check if value of formula rule is valid.
4000 --
4001 open c1(p_Scores_calc_rl,-550); -- BEN_SCORES_CALC
4002 --
4003 -- fetch value from cursor if it returns a record then the
4004 -- formula is valid otherwise its invalid
4005 --
4006 fetch c1 into l_dummy;
4007 if c1%notfound then
4008 --
4009 close c1;
4010 --
4011 -- raise error
4012 --
4013 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
4014 fnd_message.set_token('ID',p_Scores_calc_rl);
4015 fnd_message.set_token('TYPE_ID',-550);
4016 fnd_message.raise_error;
4017 --
4018 end if;
4019 --
4020 close c1;
4021 --
4022 end if;
4023 --
4024 end chk_all_rules;
4025 --
4026 -- ----------------------------------------------------------------------------
4027 -- |------< chk_lookup_codes >------|
4028 -- ----------------------------------------------------------------------------
4029 --
4030 -- Description
4031 -- This procedure is used to check that the lookup value is valid.
4032 --
4033 -- Pre Conditions
4034 -- None.
4035 --
4036 -- In Parameters
4037 -- pgm_id PK of record being inserted or updated.
4038 -- enrt_info_rt_freq_cd Value of lookup code.
4039 -- effective_date effective date
4040 -- object_version_number Object version number of record being
4041 -- inserted or updated.
4042 --
4043 -- Post Success
4044 -- Processing continues
4045 --
4046 -- Post Failure
4047 -- Error handled by procedure
4048 --
4049 -- Access Status
4050 -- Internal table handler use only.
4051 --
4052 Procedure chk_lookup_codes(p_pgm_id in number,
4053 p_Dflt_step_cd in varchar2,
4054 p_Update_salary_cd in varchar2,
4055 p_Scores_calc_mthd_cd in varchar2,
4056 p_salary_calc_mthd_cd in varchar2,
4057 p_effective_date in date,
4058 p_object_version_number in number) is
4059 --
4060 l_proc varchar2(72) := g_package||'chk_lookup_codes';
4061 l_api_updating boolean;
4062 --
4063 Begin
4064 --
4065 hr_utility.set_location('Entering:'||l_proc, 5);
4066 --
4067 l_api_updating := ben_pgm_shd.api_updating
4068 (p_pgm_id => p_pgm_id,
4069 p_effective_date => p_effective_date,
4070 p_object_version_number => p_object_version_number);
4071 --
4072 if (l_api_updating
4073 and p_Dflt_step_cd
4074 <> nvl(ben_pgm_shd.g_old_rec.Dflt_step_cd,hr_api.g_varchar2)
4075 or not l_api_updating) then
4076 --
4077 -- check if value of lookup falls within lookup type.
4078 --
4079 --
4080 if p_Dflt_step_cd is not null then
4081 if hr_api.not_exists_in_hr_lookups
4082 (p_lookup_type => 'BEN_GL_PROG_STYLE',
4083 p_lookup_code => p_dflt_step_cd,
4084 p_effective_date => p_effective_date) then
4085 --
4086 -- raise error as does not exist as lookup
4087 --
4088 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
4089 fnd_message.set_token('VALUE', p_dflt_step_cd);
4090 fnd_message.set_token('FIELD', 'bnf_dflt_bnf_cd');
4091 fnd_message.set_token('TYPE', 'BEN_GL_PROG_STYLE');
4092 fnd_message.raise_error;
4093 --
4094 end if;
4095 end if;
4096 --
4097 end if;
4098 --
4099 --
4100 if (l_api_updating
4101 and p_Update_salary_cd
4102 <> nvl(ben_pgm_shd.g_old_rec.Update_salary_cd,hr_api.g_varchar2)
4103 or not l_api_updating) then
4104 --
4105 -- check if value of lookup falls within lookup type.
4106 --
4107 --
4108 if p_Update_salary_cd is not null then
4109 if hr_api.not_exists_in_hr_lookups
4110 (p_lookup_type => 'PQH_GSP_SAL_UPD_MTHD',
4111 p_lookup_code => p_Update_salary_cd,
4112 p_effective_date => p_effective_date) then
4113 --
4114 -- raise error as does not exist as lookup
4115 --
4116 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
4117 fnd_message.set_token('VALUE', p_Update_salary_cd);
4118 fnd_message.set_token('FIELD', 'Update_salary_cd');
4119 fnd_message.set_token('TYPE', 'PQH_GSP_SAL_UPD_MTHD');
4120 fnd_message.raise_error;
4121 --
4122 end if;
4123 end if;
4124 --
4125 end if;
4126 --
4127 if (l_api_updating
4128 and p_Scores_calc_mthd_cd
4129 <> nvl(ben_pgm_shd.g_old_rec.Scores_calc_mthd_cd,hr_api.g_varchar2)
4130 or not l_api_updating) then
4131 --
4132 -- check if value of lookup falls within lookup type.
4133 --
4134 --
4135 if p_Scores_calc_mthd_cd is not null then
4136 if hr_api.not_exists_in_hr_lookups
4137 (p_lookup_type => 'BEN_SCORES_CALC_MTHD',
4138 p_lookup_code => p_Scores_calc_mthd_cd,
4139 p_effective_date => p_effective_date) then
4140 --
4141 -- raise error as does not exist as lookup
4142 --
4143 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
4144 fnd_message.set_token('VALUE', p_Scores_calc_mthd_cd);
4145 fnd_message.set_token('FIELD', 'Scores_calc_mthd_cd');
4146 fnd_message.set_token('TYPE', 'BEN_SCORES_CALC_MTHD');
4147 fnd_message.raise_error;
4148 --
4149 end if;
4150 end if;
4151 --
4152 end if;
4153 --
4154 --
4155 if (l_api_updating
4156 and p_salary_calc_mthd_cd
4157 <> nvl(ben_pgm_shd.g_old_rec.salary_calc_mthd_cd,hr_api.g_varchar2)
4158 or not l_api_updating) then
4159 --
4160 -- check if value of lookup falls within lookup type.
4161 --
4162 --
4163 if p_Scores_calc_mthd_cd is not null then
4164 if hr_api.not_exists_in_hr_lookups
4165 (p_lookup_type => 'PQH_GSP_SAL_CALC_METH',
4166 p_lookup_code => p_salary_calc_mthd_cd,
4167 p_effective_date => p_effective_date) then
4168 --
4169 -- raise error as does not exist as lookup
4170 --
4171 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
4172 fnd_message.set_token('VALUE', p_salary_calc_mthd_cd);
4173 fnd_message.set_token('FIELD', 'salary_calc_mthd_cd');
4174 fnd_message.set_token('TYPE', 'PQH_GSP_SAL_CALC_METH');
4175 fnd_message.raise_error;
4176 --
4177 end if;
4178 end if;
4179 --
4180 end if;
4181
4182 --
4183 hr_utility.set_location('Leaving:'||l_proc,10);
4184 --
4185 end chk_lookup_codes;
4186 --
4187 --
4188 -- ----------------------------------------------------------------------------
4189 -- |------< chk_enrt_info_rt_freq_cd >------|
4190 -- ----------------------------------------------------------------------------
4191 --
4192 -- Description
4193 -- This procedure is used to check that the lookup value is valid.
4194 --
4195 -- Pre Conditions
4196 -- None.
4197 --
4198 -- In Parameters
4199 -- pgm_id PK of record being inserted or updated.
4200 -- enrt_info_rt_freq_cd Value of lookup code.
4201 -- effective_date effective date
4202 -- object_version_number Object version number of record being
4203 -- inserted or updated.
4204 --
4205 -- Post Success
4206 -- Processing continues
4207 --
4208 -- Post Failure
4209 -- Error handled by procedure
4210 --
4211 -- Access Status
4212 -- Internal table handler use only.
4213 --
4214 Procedure chk_enrt_info_rt_freq_cd(p_pgm_id in number,
4215 p_enrt_info_rt_freq_cd in varchar2,
4216 p_effective_date in date,
4217 p_object_version_number in number) is
4218 --
4219 l_proc varchar2(72) := g_package||'chk_enrt_info_rt_freq_cd';
4220 l_api_updating boolean;
4221 --
4222 Begin
4223 --
4224 hr_utility.set_location('Entering:'||l_proc, 5);
4225 --
4226 l_api_updating := ben_pgm_shd.api_updating
4227 (p_pgm_id => p_pgm_id,
4228 p_effective_date => p_effective_date,
4229 p_object_version_number => p_object_version_number);
4230 --
4231 if (l_api_updating
4232 and p_enrt_info_rt_freq_cd
4233 <> nvl(ben_pgm_shd.g_old_rec.enrt_info_rt_freq_cd,hr_api.g_varchar2)
4234 or not l_api_updating) then
4235 --
4236 -- check if value of lookup falls within lookup type.
4237 --
4238 --
4239 if p_enrt_info_rt_freq_cd is not null then
4240 if hr_api.not_exists_in_hr_lookups
4241 (p_lookup_type => 'BEN_ENRT_INFO_RT_FREQ',
4242 p_lookup_code => p_enrt_info_rt_freq_cd,
4243 p_effective_date => p_effective_date) then
4244 --
4245 -- raise error as does not exist as lookup
4246 --
4247 fnd_message.set_name('BEN','BEN_91307_INV_ENRT_RT_FREQ_CD');
4248 fnd_message.raise_error;
4249 --
4250 end if;
4251 end if;
4252 --
4253 end if;
4254 --
4255 hr_utility.set_location('Leaving:'||l_proc,10);
4256 --
4257 end chk_enrt_info_rt_freq_cd;
4258 --
4259 -- ----------------------------------------------------------------------------
4260 -- |------< chk_dpnt_cvg_strt_dt_cd >------|
4261 -- ----------------------------------------------------------------------------
4262 --
4263 -- Description
4264 -- This procedure is used to check that the lookup value is valid.
4265 --
4266 -- Pre Conditions
4267 -- None.
4268 --
4269 -- In Parameters
4270 -- pgm_id PK of record being inserted or updated.
4271 -- dpnt_cvg_strt_dt_cd Value of lookup code.
4272 -- effective_date effective date
4273 -- object_version_number Object version number of record being
4274 -- inserted or updated.
4275 --
4276 -- Post Success
4277 -- Processing continues
4278 --
4279 -- Post Failure
4280 -- Error handled by procedure
4281 --
4282 -- Access Status
4283 -- Internal table handler use only.
4284 --
4285 Procedure chk_dpnt_cvg_strt_dt_cd(p_pgm_id in number,
4286 p_dpnt_cvg_strt_dt_cd in varchar2,
4287 p_effective_date in date,
4288 p_object_version_number in number) is
4289 --
4290 l_proc varchar2(72) := g_package||'chk_dpnt_cvg_strt_dt_cd';
4291 l_api_updating boolean;
4292 --
4293 Begin
4294 --
4295 hr_utility.set_location('Entering:'||l_proc, 5);
4296 --
4297 l_api_updating := ben_pgm_shd.api_updating
4298 (p_pgm_id => p_pgm_id,
4299 p_effective_date => p_effective_date,
4300 p_object_version_number => p_object_version_number);
4301 --
4302 if (l_api_updating
4303 and p_dpnt_cvg_strt_dt_cd
4304 <> nvl(ben_pgm_shd.g_old_rec.dpnt_cvg_strt_dt_cd,hr_api.g_varchar2)
4305 or not l_api_updating) then
4306 --
4307 -- check if value of lookup falls within lookup type.
4308 --
4309 --
4310 if p_dpnt_cvg_strt_dt_cd is not null then
4311 if hr_api.not_exists_in_hr_lookups
4312 (p_lookup_type => 'BEN_DPNT_CVG_STRT',
4313 p_lookup_code => p_dpnt_cvg_strt_dt_cd,
4314 p_effective_date => p_effective_date) then
4315 --
4316 -- raise error as does not exist as lookup
4317 --
4318 fnd_message.set_name('BEN','BEN_91308_INV_DPT_CV_ST_DT_CD');
4319 fnd_message.raise_error;
4320 --
4321 end if;
4322 end if;
4323 --
4324 end if;
4325 --
4326 hr_utility.set_location('Leaving:'||l_proc,10);
4327 --
4328 end chk_dpnt_cvg_strt_dt_cd;
4329 --
4330 -- ----------------------------------------------------------------------------
4331 -- |------< chk_dpnt_cvg_end_dt_cd >------|
4332 -- ----------------------------------------------------------------------------
4333 --
4334 -- Description
4335 -- This procedure is used to check that the lookup value is valid.
4336 --
4337 -- Pre Conditions
4338 -- None.
4339 --
4340 -- In Parameters
4341 -- pgm_id PK of record being inserted or updated.
4342 -- dpnt_cvg_end_dt_cd Value of lookup code.
4343 -- effective_date effective date
4344 -- object_version_number Object version number of record being
4345 -- inserted or updated.
4346 --
4347 -- Post Success
4348 -- Processing continues
4349 --
4350 -- Post Failure
4351 -- Error handled by procedure
4352 --
4353 -- Access Status
4354 -- Internal table handler use only.
4355 --
4356 Procedure chk_dpnt_cvg_end_dt_cd(p_pgm_id in number,
4357 p_dpnt_cvg_end_dt_cd in varchar2,
4358 p_effective_date in date,
4359 p_object_version_number in number) is
4360 --
4361 l_proc varchar2(72) := g_package||'chk_dpnt_cvg_end_dt_cd';
4362 l_api_updating boolean;
4363 --
4364 Begin
4365 --
4366 hr_utility.set_location('Entering:'||l_proc, 5);
4367 --
4368 l_api_updating := ben_pgm_shd.api_updating
4369 (p_pgm_id => p_pgm_id,
4370 p_effective_date => p_effective_date,
4371 p_object_version_number => p_object_version_number);
4372 --
4373 if (l_api_updating
4374 and p_dpnt_cvg_end_dt_cd
4375 <> nvl(ben_pgm_shd.g_old_rec.dpnt_cvg_end_dt_cd,hr_api.g_varchar2)
4376 or not l_api_updating) then
4377 --
4378 -- check if value of lookup falls within lookup type.
4379 --
4380 --
4381 if p_dpnt_cvg_end_dt_cd is not null then
4382 if hr_api.not_exists_in_hr_lookups
4383 (p_lookup_type => 'BEN_DPNT_CVG_END',
4384 p_lookup_code => p_dpnt_cvg_end_dt_cd,
4385 p_effective_date => p_effective_date) then
4386 --
4387 -- raise error as does not exist as lookup
4388 --
4389 fnd_message.set_name('BEN','BEN_91309_INV_DPT_CV_EN_DT_CD');
4390 fnd_message.raise_error;
4391 --
4392 end if;
4393 end if;
4394 --
4395 end if;
4396 --
4397 hr_utility.set_location('Leaving:'||l_proc,10);
4398 --
4399 end chk_dpnt_cvg_end_dt_cd;
4400 --
4401 -- ----------------------------------------------------------------------------
4402 -- |------< chk_enrl_strt_dt_dpndcy >------|
4403 -- ----------------------------------------------------------------------------
4404 --
4405 -- Description
4406 -- This procedure is used to check the code/rule dependency as following:
4407 -- If code = 'Rule' then rule must be selected.
4408 -- If code <> 'Rule' then code must not be selected.
4409 --
4410 -- Pre Conditions
4411 -- None.
4412 --
4413 -- In Parameters
4414 -- pgm_id PK of record being inserted or updated.
4415 -- enrt_cvg_strt_dt_cd Value of lookup code.
4416 -- enrt_cvg_strt_dt_rl
4417 -- effective_date effective date
4418 -- object_version_number Object version number of record being
4419 -- inserted or updated.
4420 --
4421 -- Post Success
4422 -- Processing continues
4423 --
4424 -- Post Failure
4425 -- Error handled by procedure
4426 --
4427 -- Access Status
4428 -- Internal table handler use only.
4429 --
4430 Procedure chk_enrl_strt_dt_dpndcy
4431 (p_pgm_id in number,
4432 p_enrt_cvg_strt_dt_cd in varchar2,
4433 p_enrt_cvg_strt_dt_rl in number,
4434 p_effective_date in date,
4435 p_object_version_number in number) is
4436 --
4437 l_proc varchar2(72) := g_package||'chk_enrl_strt_dt_dpndcy ';
4438 l_api_updating boolean;
4439 --
4440 Begin
4441 --
4442 hr_utility.set_location('Entering:'||l_proc, 5);
4443 --
4444 l_api_updating := ben_pgm_shd.api_updating
4445 (p_pgm_id => p_pgm_id,
4446 p_effective_date => p_effective_date,
4447 p_object_version_number => p_object_version_number);
4448 --
4449 if (l_api_updating
4450 and (nvl(p_enrt_cvg_strt_dt_cd,hr_api.g_varchar2)
4451 <> nvl(ben_pgm_shd.g_old_rec.enrt_cvg_strt_dt_cd,hr_api.g_varchar2) or
4452 nvl(p_enrt_cvg_strt_dt_rl,hr_api.g_number)
4453 <> nvl(ben_pgm_shd.g_old_rec.enrt_cvg_strt_dt_rl,hr_api.g_number))
4454 or not l_api_updating) then
4455 --
4456 if (p_enrt_cvg_strt_dt_cd = 'RL' and p_enrt_cvg_strt_dt_rl is null) then
4457 --
4458 fnd_message.set_name('BEN','BEN_91310_ENRT_STRT_CWOR');
4459 fnd_message.raise_error;
4460 --
4461 end if;
4462 --
4463 if nvl(p_enrt_cvg_strt_dt_cd,hr_api.g_varchar2) <> 'RL'
4464 and p_enrt_cvg_strt_dt_rl is not null then
4465 --
4466 fnd_message.set_name('BEN','BEN_91311_ENRT_STRT_RWOC');
4467 fnd_message.raise_error;
4468 --
4469 end if;
4470 --
4471 end if;
4472 --
4473 hr_utility.set_location('Leaving:'||l_proc,10);
4474 --
4475 end chk_enrl_strt_dt_dpndcy;
4476 --
4477 -- ----------------------------------------------------------------------------
4478 -- |------< chk_enrl_end_dt_dpndcy >------|
4479 -- ----------------------------------------------------------------------------
4480 --
4481 -- Description
4482 -- This procedure is used to check the code/rule dependency as following:
4483 -- If code = 'Rule' then rule must be selected.
4484 -- If code <> 'Rule' then code must not be selected.
4485 --
4486 -- Pre Conditions
4487 -- None.
4488 --
4489 -- In Parameters
4490 -- pgm_id PK of record being inserted or updated.
4491 -- enrt_cvg_end_dt_cd Value of lookup code.
4492 -- enrt_cvg_end_dt_rl
4493 -- effective_date effective date
4494 -- object_version_number Object version number of record being
4495 -- inserted or updated.
4496 --
4497 -- Post Success
4498 -- Processing continues
4499 --
4500 -- Post Failure
4501 -- Error handled by procedure
4502 --
4503 -- Access Status
4504 -- Internal table handler use only.
4505 --
4506 Procedure chk_enrl_end_dt_dpndcy
4507 (p_pgm_id in number,
4508 p_enrt_cvg_end_dt_cd in varchar2,
4509 p_enrt_cvg_end_dt_rl in number,
4510 p_effective_date in date,
4511 p_object_version_number in number) is
4512 --
4513 l_proc varchar2(72) := g_package||'chk_enrl_end_dt_dpndcy ';
4514 l_api_updating boolean;
4515 --
4516 Begin
4517 --
4518 hr_utility.set_location('Entering:'||l_proc, 5);
4519 --
4520 l_api_updating := ben_pgm_shd.api_updating
4521 (p_pgm_id => p_pgm_id,
4522 p_effective_date => p_effective_date,
4523 p_object_version_number => p_object_version_number);
4524 --
4525 if (l_api_updating
4526 and
4527 (nvl(p_enrt_cvg_end_dt_cd,hr_api.g_varchar2)
4528 <> nvl(ben_pgm_shd.g_old_rec.enrt_cvg_end_dt_cd,hr_api.g_varchar2) or
4529 nvl(p_enrt_cvg_end_dt_rl,hr_api.g_number)
4530 <> nvl(ben_pgm_shd.g_old_rec.enrt_cvg_end_dt_rl,hr_api.g_number))
4531 or not l_api_updating) then
4532 --
4533 if (p_enrt_cvg_end_dt_cd = 'RL' and p_enrt_cvg_end_dt_rl is null) then
4534 --
4535 fnd_message.set_name('BEN','BEN_91378_DFLT_DENRL_END_DT1');
4536 fnd_message.raise_error;
4537 --
4538 end if;
4539 --
4540 if nvl(p_enrt_cvg_end_dt_cd,hr_api.g_varchar2) <> 'RL'
4541 and p_enrt_cvg_end_dt_rl is not null then
4542 --
4543 fnd_message.set_name('BEN','BEN_91379_DFLT_DENRL_END_DT2');
4544 fnd_message.raise_error;
4545 --
4546 end if;
4547 --
4548 end if;
4549 --
4550 hr_utility.set_location('Leaving:'||l_proc,10);
4551 --
4552 end chk_enrl_end_dt_dpndcy;
4553 --
4554 -- ----------------------------------------------------------------------------
4555 -- |------< chk_dpnt_cvg_strt_dt_dpndcy >------|
4556 -- ----------------------------------------------------------------------------
4557 --
4558 -- Description
4559 -- This procedure is used to check the code/rule dependency as following:
4560 -- If code = 'Rule' then rule must be selected.
4561 -- If code <> 'Rule' then code must not be selected.
4562 --
4563 -- Pre Conditions
4564 -- None.
4565 --
4566 -- In Parameters
4567 -- lee_rsn_id PK of record being inserted or updated.
4568 -- dpnt_cvg_strt_cd Value of lookup code.
4569 -- dpnt_cvg_strt_rl
4570 -- effective_date effective date
4571 -- object_version_number Object version number of record being
4572 -- inserted or updated.
4573 --
4574 -- Post Success
4575 -- Processing continues
4576 --
4577 -- Post Failure
4578 -- Error handled by procedure
4579 --
4580 -- Access Status
4581 -- Internal table handler use only.
4582 --
4583 Procedure chk_dpnt_cvg_strt_dt_dpndcy(p_pgm_id in number,
4584 p_dpnt_cvg_strt_dt_cd in varchar2,
4585 p_dpnt_cvg_strt_dt_rl in number,
4586 p_effective_date in date,
4587 p_object_version_number in number) is
4588 --
4589 l_proc varchar2(72) := g_package||'chk_dpnt_cvg_strt_dt_dpndcy ';
4590 l_api_updating boolean;
4591 --
4592 Begin
4593 --
4594 hr_utility.set_location('Entering:'||l_proc, 5);
4595 --
4596 l_api_updating := ben_pgm_shd.api_updating
4597 (p_pgm_id => p_pgm_id,
4598 p_effective_date => p_effective_date,
4599 p_object_version_number => p_object_version_number);
4600 --
4601 if (l_api_updating
4602 and
4603 (nvl(p_dpnt_cvg_strt_dt_cd,hr_api.g_varchar2)
4604 <> nvl(ben_pgm_shd.g_old_rec.dpnt_cvg_strt_dt_cd,hr_api.g_varchar2) or
4605 nvl(p_dpnt_cvg_strt_dt_rl,hr_api.g_number)
4606 <> nvl(ben_pgm_shd.g_old_rec.dpnt_cvg_strt_dt_rl,hr_api.g_number))
4607 or not l_api_updating) then
4608 --
4609 if (p_dpnt_cvg_strt_dt_cd = 'RL' and p_dpnt_cvg_strt_dt_rl is null) then
4610 --
4611 fnd_message.set_name('BEN','BEN_91380_DPNT_CVG_ST_DT_1');
4612 fnd_message.raise_error;
4613 --
4614 end if;
4615 --
4616 if nvl(p_dpnt_cvg_strt_dt_cd,hr_api.g_varchar2) <> 'RL' and p_dpnt_cvg_strt_dt_rl is not null then
4617 --
4618 fnd_message.set_name('BEN','BEN_91381_DPNT_CVG_ST_DT_2');
4619 fnd_message.raise_error;
4620 --
4621 end if;
4622 --
4623 end if;
4624 --
4625 hr_utility.set_location('Leaving:'||l_proc,10);
4626 --
4627 end chk_dpnt_cvg_strt_dt_dpndcy;
4628 --
4629 -- ----------------------------------------------------------------------------
4630 -- |------< chk_dpnt_cvg_end_dt_dpndcy >------|
4631 -- ----------------------------------------------------------------------------
4632 --
4633 -- Description
4634 -- This procedure is used to check the code/rule dependency as following:
4635 -- If code = 'Rule' then rule must be selected.
4636 -- If code <> 'Rule' then code must not be selected.
4637 --
4638 -- Pre Conditions
4639 -- None.
4640 --
4641 -- In Parameters
4642 -- lee_rsn_id PK of record being inserted or updated.
4643 -- dpnt_cvg_end_dt_cd Value of lookup code.
4644 -- dpnt_cvg_end_dt_rl
4645 -- effective_date effective date
4646 -- object_version_number Object version number of record being
4647 -- inserted or updated.
4648 --
4649 -- Post Success
4650 -- Processing continues
4651 --
4652 -- Post Failure
4653 -- Error handled by procedure
4654 --
4655 -- Access Status
4656 -- Internal table handler use only.
4657 --
4658 Procedure chk_dpnt_cvg_end_dt_dpndcy(p_pgm_id in number,
4659 p_dpnt_cvg_end_dt_cd in varchar2,
4660 p_dpnt_cvg_end_dt_rl in number,
4661 p_effective_date in date,
4662 p_object_version_number in number) is
4663 --
4664 l_proc varchar2(72) := g_package||'chk_dpnt_cvg_end_dt_dpndcy ';
4665 l_api_updating boolean;
4666 --
4667 Begin
4668 --
4669 hr_utility.set_location('Entering:'||l_proc, 5);
4670 --
4671 l_api_updating := ben_pgm_shd.api_updating
4672 (p_pgm_id => p_pgm_id,
4673 p_effective_date => p_effective_date,
4674 p_object_version_number => p_object_version_number);
4675 --
4676 if (l_api_updating
4677 and
4678 (nvl(p_dpnt_cvg_end_dt_cd,hr_api.g_varchar2)
4679 <> nvl(ben_pgm_shd.g_old_rec.dpnt_cvg_end_dt_cd,hr_api.g_varchar2) or
4680 nvl(p_dpnt_cvg_end_dt_rl,hr_api.g_number)
4681 <> nvl(ben_pgm_shd.g_old_rec.dpnt_cvg_end_dt_rl,hr_api.g_number))
4682 or not l_api_updating) then
4683 --
4684 if (p_dpnt_cvg_end_dt_cd = 'RL' and p_dpnt_cvg_end_dt_rl is null) then
4685 --
4686 fnd_message.set_name('BEN','BEN_91352_DPNT_CVG_END_CD_NO_R');
4687 fnd_message.raise_error;
4688 --
4689 end if;
4690 --
4691 if nvl(p_dpnt_cvg_end_dt_cd,hr_api.g_varchar2) <> 'RL' and p_dpnt_cvg_end_dt_rl is not null then
4692 --
4693 fnd_message.set_name('BEN','BEN_91353_DPNT_CVG_END_R_NO_CD');
4694 fnd_message.raise_error;
4695 --
4696 end if;
4697 --
4698 end if;
4699 --
4700 hr_utility.set_location('Leaving:'||l_proc,10);
4701 --
4702 end chk_dpnt_cvg_end_dt_dpndcy;
4703 --
4704 -- ----------------------------------------------------------------------------
4705 -- |------< chk_dpnt_dsgn_cd_dpndcy >------|
4706 -- ----------------------------------------------------------------------------
4707 --
4708 -- Description
4709 -- If dependent designation code is not 'Required' or 'Optional', then the
4710 -- following must be null: Dpnt Coverage Start Dt code and rule,
4711 -- Dpnt Coverage End Dt code and rule, SSN req, Addr req, DOB req, Cert req
4712 -- Meets One Req Flag, Derivable factors apply flag.
4713 --
4714 -- Pre Conditions
4715 -- None.
4716 --
4717 -- In Parameters
4718 -- lee_rsn_id PK of record being inserted or updated.
4719 -- dpnt_dsgn_cd Value of lookup code.
4720 -- dpnt_cvg_strt_dt_cd Value of lookup code.
4721 -- dpnt_cvg_strt_dt_rl
4722 -- dpnt_cvg_end_dt_cd Value of lookup code.
4723 -- dpnt_cvg_end_dt_rl
4724 -- dpnt_adrs_rqd_flag
4725 -- dpnt_dob_rqd_flag
4726 -- dpnt_legv_id_rqd_flag
4727 -- dpnt_dsgn_no_ctfn_rqd_flag
4728 -- drvbl_fctr_dpnt_elig_flag
4729 -- effective_date effective date
4730 -- object_version_number Object version number of record being
4731 -- inserted or updated.
4732 --
4733 -- Post Success
4734 -- Processing continues
4735 --
4736 -- Post Failure
4737 -- Error handled by procedure
4738 --
4739 -- Access Status
4740 -- Internal table handler use only.
4741 --
4742 Procedure chk_dpnt_dsgn_cd_dpndcy(p_pgm_id in number,
4743 p_dpnt_dsgn_cd in varchar2,
4744 p_dpnt_cvg_strt_dt_cd in varchar2,
4745 p_dpnt_cvg_strt_dt_rl in number,
4746 p_dpnt_cvg_end_dt_cd in varchar2,
4747 p_dpnt_cvg_end_dt_rl in number,
4748 p_dpnt_adrs_rqd_flag in varchar2,
4749 p_dpnt_dob_rqd_flag in varchar2,
4750 p_dpnt_legv_id_rqd_flag in varchar2,
4751 p_dpnt_dsgn_no_ctfn_rqd_flag in varchar2,
4752 p_drvbl_fctr_dpnt_elig_flag in varchar2,
4753 p_effective_date in date,
4754 p_object_version_number in number) is
4755 --
4756 l_proc varchar2(72) := g_package||'chk_dpnt_dsgn_cd_dpndcy ';
4757 l_api_updating boolean;
4758 --
4759 Begin
4760 --
4761 hr_utility.set_location('Entering:'||l_proc, 5);
4762 --
4763 l_api_updating := ben_pgm_shd.api_updating
4764 (p_pgm_id => p_pgm_id,
4765 p_effective_date => p_effective_date,
4766 p_object_version_number => p_object_version_number);
4767 --
4768 if (l_api_updating
4769 and
4770 (nvl(p_dpnt_cvg_end_dt_cd,hr_api.g_varchar2)
4771 <> nvl(ben_pgm_shd.g_old_rec.dpnt_cvg_end_dt_cd,hr_api.g_varchar2) or
4772 nvl(p_dpnt_cvg_end_dt_rl,hr_api.g_number)
4773 <> nvl(ben_pgm_shd.g_old_rec.dpnt_cvg_end_dt_rl,hr_api.g_number) or
4774 nvl(p_dpnt_dsgn_cd,hr_api.g_varchar2)
4775 <> nvl(ben_pgm_shd.g_old_rec.dpnt_dsgn_cd,hr_api.g_varchar2) or
4776 nvl(p_dpnt_cvg_strt_dt_cd,hr_api.g_varchar2)
4777 <> nvl(ben_pgm_shd.g_old_rec.dpnt_cvg_strt_dt_cd,hr_api.g_varchar2) or
4778 nvl(p_dpnt_cvg_strt_dt_rl,hr_api.g_number)
4779 <> nvl(ben_pgm_shd.g_old_rec.dpnt_cvg_strt_dt_rl,hr_api.g_number) or
4780 nvl(p_dpnt_adrs_rqd_flag,hr_api.g_varchar2)
4781 <> nvl(ben_pgm_shd.g_old_rec.dpnt_adrs_rqd_flag,hr_api.g_varchar2) or
4782 nvl(p_dpnt_dob_rqd_flag,hr_api.g_varchar2)
4783 <> nvl(ben_pgm_shd.g_old_rec.dpnt_dob_rqd_flag,hr_api.g_varchar2) or
4784 nvl(p_dpnt_legv_id_rqd_flag,hr_api.g_varchar2)
4785 <> nvl(ben_pgm_shd.g_old_rec.dpnt_legv_id_rqd_flag,hr_api.g_varchar2) or
4786 nvl(p_dpnt_dsgn_no_ctfn_rqd_flag,hr_api.g_varchar2)
4787 <> nvl(ben_pgm_shd.g_old_rec.dpnt_dsgn_no_ctfn_rqd_flag,hr_api.g_varchar2) or
4788 nvl(p_drvbl_fctr_dpnt_elig_flag,hr_api.g_varchar2)
4789 <> nvl(ben_pgm_shd.g_old_rec.drvbl_fctr_dpnt_elig_flag,hr_api.g_varchar2)) or
4790 not l_api_updating) then
4791 --
4792 if nvl(p_dpnt_dsgn_cd,'X') not in ('R','O') and
4793 (p_dpnt_cvg_strt_dt_cd is not null or
4794 p_dpnt_cvg_strt_dt_rl is not null or
4795 p_dpnt_cvg_end_dt_cd is not null or
4796 p_dpnt_cvg_end_dt_rl is not null or
4797 p_dpnt_adrs_rqd_flag = 'Y' or
4798 p_dpnt_dob_rqd_flag = 'Y' or
4799 p_dpnt_legv_id_rqd_flag = 'Y' or
4800 p_dpnt_dsgn_no_ctfn_rqd_flag = 'Y' or
4801 p_drvbl_fctr_dpnt_elig_flag = 'Y') then
4802 --
4803 fnd_message.set_name('BEN','BEN_91375_PGM_DPNT_DSGN_RQD');
4804 fnd_message.raise_error;
4805 --
4806 end if;
4807 --
4808 end if;
4809 --
4810 hr_utility.set_location('Leaving:'||l_proc,10);
4811 --
4812 end chk_dpnt_dsgn_cd_dpndcy;
4813 --
4814 /*
4815 -- ----------------------------------------------------------------------------
4816 -- |------< chk_pgm_prvds_no_auto_dpndcy >------|
4817 -- ----------------------------------------------------------------------------
4818 --
4819 -- Description
4820 -- If Program Provides no Automatic Enrollment Flag = 'YES' then
4821 -- Default Enrollment Method Code in BEN_PLIP_F cannot be 'Automatic'.
4822 --
4823 --
4824 -- Pre Conditions
4825 -- None.
4826 --
4827 -- In Parameters
4828 -- pgm_id PK of record being inserted or updated.
4829 -- pgm_prvds_no_auto_enrt_flag
4830 -- pgm_id
4831 -- business_group_id
4832 -- effective_date effective date
4833 -- object_version_number Object version number of record being
4834 -- inserted or updated.
4835 --
4836 -- Post Success
4837 -- Processing continues
4838 --
4839 -- Post Failure
4840 -- Error handled by procedure
4841 --
4842 -- Access Status
4843 -- Internal table handler use only.
4844 --
4845 Procedure chk_pgm_prvds_no_auto_dpndcy
4846 (p_pgm_id in number,
4847 p_pgm_prvds_no_auto_enrt_flag in varchar2,
4848 p_business_group_id in number,
4849 p_effective_date in date,
4850 p_object_version_number in number) is
4851 --
4852 l_proc varchar2(72) := g_package || 'chk_pgm_prvds_no_auto_dpndcy';
4853 l_api_updating boolean;
4854 l_dummy varchar2(30);
4855 --
4856 cursor c1 is select null
4857 from ben_plip_f cpp
4858 where cpp.pgm_id = p_pgm_id
4859 and dflt_enrt_mthd_cd = 'A'
4860 and cpp.business_group_id +0 = p_business_group_id
4861 and p_effective_date between cpp.effective_start_date
4862 and cpp.effective_end_date;
4863 --
4864 Begin
4865 --
4866 hr_utility.set_location('Entering:'||l_proc, 5);
4867 --
4868 l_api_updating := ben_pgm_shd.api_updating
4869 (p_pgm_id => p_pgm_id,
4870 p_effective_date => p_effective_date,
4871 p_object_version_number => p_object_version_number);
4872 --
4873
4874 if (l_api_updating
4875 and nvl(p_pgm_prvds_no_auto_enrt_flag,hr_api.g_varchar2)
4876 <> nvl(ben_pgm_shd.g_old_rec.pgm_prvds_no_auto_enrt_flag,hr_api.g_varchar2)
4877 or not l_api_updating) then
4878 --
4879 -- If pgm provides no auto enroll flag = 'Y' then enrt mthd code can't be 'A'.
4880 --
4881 open c1;
4882 fetch c1 into l_dummy;
4883 if c1%found then
4884 if p_pgm_prvds_no_auto_enrt_flag = 'Y' then
4885 --
4886 close c1;
4887 fnd_message.set_name('BEN','BEN_91372_PGM_PRVD_NO_AUTOENRL');
4888 fnd_message.raise_error;
4889 --
4890 end if;
4891 --
4892 end if;
4893 close c1;
4894 --
4895 end if;
4896 --
4897 hr_utility.set_location('Leaving:'||l_proc,10);
4898 --
4899 end chk_pgm_prvds_no_auto_dpndcy;
4900 --
4901 -- ----------------------------------------------------------------------------
4902 -- |------< chk_pgm_prvds_no_dflt_dpndcy >------|
4903 -- ----------------------------------------------------------------------------
4904 --
4905 -- Description
4906 -- If Program Provides no Default Enrollment Flag = 'YES' then
4907 -- the following fields from ben_plip_f must be null: Default Flag,
4908 -- Default Enrollment Method Code, and Default Enrollment Determination
4909 -- Rule.
4910 --
4911 -- Pre Conditions
4912 -- None.
4913 --
4914 -- In Parameters
4915 -- pgm_id PK of record being inserted or updated.
4916 -- pgm_prvds_no_dflt_enrt_flag
4917 -- pgm_id
4918 -- business_group_id
4919 -- effective_date effective date
4920 -- object_version_number Object version number of record being
4921 -- inserted or updated.
4922 --
4923 -- Post Success
4924 -- Processing continues
4925 --
4926 -- Post Failure
4927 -- Error handled by procedure
4928 --
4929 -- Access Status
4930 -- Internal table handler use only.
4931 --
4932 Procedure chk_pgm_prvds_no_dflt_dpndcy(p_pgm_id in number,
4933 p_pgm_prvds_no_dflt_enrt_flag in varchar2,
4934 p_business_group_id in number,
4935 p_effective_date in date,
4936 p_object_version_number in number) is
4937 --
4938 l_proc varchar2(72) := g_package || 'chk_pgm_prvds_no_dflt_dpndcy';
4939 l_api_updating boolean;
4940 l_dummy varchar2(30);
4941 --
4942 cursor c1 is select null
4943 from ben_plip_f cpp
4944 where cpp.pgm_id = p_pgm_id
4945 and (cpp.dflt_enrt_mthd_cd is not null or
4946 cpp.dflt_enrt_det_rl is not null or
4947 dflt_flag = 'Y')
4948 and cpp.business_group_id +0 = p_business_group_id
4949 and p_effective_date between cpp.effective_start_date
4950 and cpp.effective_end_date;
4951 --
4952 Begin
4953 --
4954 hr_utility.set_location('Entering:'||l_proc, 5);
4955 --
4956 l_api_updating := ben_pgm_shd.api_updating
4957 (p_pgm_id => p_pgm_id,
4958 p_effective_date => p_effective_date,
4959 p_object_version_number => p_object_version_number);
4960 --
4961
4962 if (l_api_updating
4963 and nvl(p_pgm_prvds_no_dflt_enrt_flag,hr_api.g_varchar2)
4964 <> nvl(ben_pgm_shd.g_old_rec.pgm_prvds_no_dflt_enrt_flag,hr_api.g_varchar2)
4965 or not l_api_updating) then
4966 --
4967 -- If pgm provides no dflt enroll flag = 'Y' dflt fields must be null.
4968 --
4969 open c1;
4970 fetch c1 into l_dummy;
4971 if c1%found then
4972 --
4973 if p_pgm_prvds_no_dflt_enrt_flag = 'Y' then
4974 --
4975 close c1;
4976 fnd_message.set_name('BEN','BEN_91373_PRVD_NO_DFLT_ENRL_2');
4977 fnd_message.raise_error;
4978 --
4979 end if;
4980 --
4981 end if;
4982 close c1;
4983 --
4984 end if;
4985 --
4986 hr_utility.set_location('Leaving:'||l_proc,10);
4987 --
4988 end chk_pgm_prvds_no_dflt_dpndcy;
4989 --
4990 */
4991 -- ----------------------------------------------------------------------------
4992 -- |------< chk_pgm_prvds_no_dflt_dpndcy2 >------|
4993 -- ----------------------------------------------------------------------------
4994 --
4995 -- Description
4996 -- If Program Provides no Default Enrollment Flag = 'YES' then
4997 -- days after event to default number from ben_lee_rsn_f must be null.
4998 --
4999 -- Pre Conditions
5000 -- None.
5001 --
5002 -- In Parameters
5003 -- pgm_id PK of record being inserted or updated.
5004 -- pgm_prvds_no_dflt_enrt_flag
5005 -- pgm_id
5006 -- business_group_id
5007 -- effective_date effective date
5008 -- object_version_number Object version number of record being
5009 -- inserted or updated.
5010 --
5011 -- Post Success
5012 -- Processing continues
5013 --
5014 -- Post Failure
5015 -- Error handled by procedure
5016 --
5017 -- Access Status
5018 -- Internal table handler use only.
5019 --
5020 Procedure chk_pgm_prvds_no_dflt_dpndcy2
5021 (p_pgm_id in number,
5022 p_pgm_prvds_no_dflt_enrt_flag in varchar2,
5023 p_business_group_id in number,
5024 p_effective_date in date,
5025 p_object_version_number in number) is
5026 --
5027 l_proc varchar2(72) := g_package || 'chk_pgm_prvds_no_dflt_dpndcy2';
5028 l_api_updating boolean;
5029 l_dummy varchar2(30);
5030 --
5031 cursor c1 is select null
5032 from ben_lee_rsn_f len,
5033 ben_popl_enrt_typ_cycl_f pet
5034 where pet.pgm_id = p_pgm_id
5035 and len.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id
5036 and pet.business_group_id +0 = p_business_group_id
5037 and len.business_group_id +0 = p_business_group_id
5038 and p_effective_date between pet.effective_start_date
5039 and pet.effective_end_date
5040 and p_effective_date between len.effective_start_date
5041 and len.effective_end_date
5042 and len.dys_aftr_end_to_dflt_num is not null;
5043 --
5044 Begin
5045 --
5046 hr_utility.set_location('Entering:'||l_proc, 5);
5047 --
5048 l_api_updating := ben_pgm_shd.api_updating
5049 (p_pgm_id => p_pgm_id,
5050 p_effective_date => p_effective_date,
5051 p_object_version_number => p_object_version_number);
5052 --
5053
5054 if (l_api_updating
5055 and nvl(p_pgm_prvds_no_dflt_enrt_flag,hr_api.g_varchar2)
5056 <> nvl(ben_pgm_shd.g_old_rec.pgm_prvds_no_dflt_enrt_flag,hr_api.g_varchar2)
5057 or not l_api_updating) then
5058 --
5059 -- If cursor is found then at least one dflt num was found.
5060 --
5061 open c1;
5062 fetch c1 into l_dummy;
5063 if c1%found then
5064 if p_pgm_prvds_no_dflt_enrt_flag = 'Y' then
5065 --
5066 close c1;
5067 fnd_message.set_name('BEN','BEN_91374_PRVD_NO_DFLT_ENRL_1');
5068 fnd_message.raise_error;
5069 --
5070 end if;
5071 --
5072 end if;
5073 close c1;
5074 --
5075 end if;
5076 --
5077 hr_utility.set_location('Leaving:'||l_proc,10);
5078 --
5079 end chk_pgm_prvds_no_dflt_dpndcy2;
5080 --
5081 -- ----------------------------------------------------------------------------
5082 -- |------< chk_dpnt_dsgn_lvl_cd_dpndcy >------|
5083 -- ----------------------------------------------------------------------------
5084 --
5085 -- Description
5086 -- If dependent designation level is not 'Program' the following fields
5087 -- must be null: Dpnt Designation, Dpnt Coverage Start Dt code and rule,
5088 -- Dpnt Coverage End Dt code and rule, SSN req, Addr req, DOB req, Cert req
5089 -- Meets One Req Flag, Derivable factors apply flag.
5090 --
5091 -- Pre Conditions
5092 -- None.
5093 --
5094 -- In Parameters
5095 -- lee_rsn_id PK of record being inserted or updated.
5096 -- dpnt_dsgn_lvl_cd Value of lookup code.
5097 -- dpnt_dsgn_cd Value of lookup code.
5098 -- dpnt_cvg_strt_dt_cd Value of lookup code.
5099 -- dpnt_cvg_strt_dt_rl
5100 -- dpnt_cvg_end_dt_cd Value of lookup code.
5101 -- dpnt_cvg_end_dt_rl
5102 -- dpnt_adrs_rqd_flag
5103 -- dpnt_dob_rqd_flag
5104 -- dpnt_legv_id_rqd_flag
5105 -- dpnt_dsgn_no_ctfn_rqd_flag
5106 -- drvbl_fctr_dpnt_elig_flag
5107 -- effective_date effective date
5108 -- object_version_number Object version number of record being
5109 -- inserted or updated.
5110 --
5111 -- Post Success
5112 -- Processing continues
5113 --
5114 -- Post Failure
5115 -- Error handled by procedure
5116 --
5117 -- Access Status
5118 -- Internal table handler use only.
5119 --
5120 Procedure chk_dpnt_dsgn_lvl_cd_dpndcy(p_pgm_id in number,
5121 p_dpnt_dsgn_lvl_cd in varchar2,
5122 p_dpnt_dsgn_cd in varchar2,
5123 p_dpnt_cvg_strt_dt_cd in varchar2,
5124 p_dpnt_cvg_strt_dt_rl in number,
5125 p_dpnt_cvg_end_dt_cd in varchar2,
5126 p_dpnt_cvg_end_dt_rl in number,
5127 p_dpnt_adrs_rqd_flag in varchar2,
5128 p_dpnt_dob_rqd_flag in varchar2,
5129 p_dpnt_legv_id_rqd_flag in varchar2,
5130 p_dpnt_dsgn_no_ctfn_rqd_flag in varchar2,
5131 p_drvbl_fctr_dpnt_elig_flag in varchar2,
5132 p_effective_date in date,
5133 p_object_version_number in number) is
5134 --
5135 l_proc varchar2(72) := g_package||'chk_dpnt_dsgn_lvl_cd_dpndcy ';
5136 l_api_updating boolean;
5137 --
5138 Begin
5139 --
5140 hr_utility.set_location('Entering:'||l_proc, 5);
5141 --
5142 l_api_updating := ben_pgm_shd.api_updating
5143 (p_pgm_id => p_pgm_id,
5144 p_effective_date => p_effective_date,
5145 p_object_version_number => p_object_version_number);
5146 --
5147 if (l_api_updating
5148 and
5149 (nvl(p_dpnt_cvg_end_dt_cd,hr_api.g_varchar2)
5150 <> nvl(ben_pgm_shd.g_old_rec.dpnt_cvg_end_dt_cd,hr_api.g_varchar2) or
5151 nvl(p_dpnt_cvg_end_dt_rl,hr_api.g_number)
5152 <> nvl(ben_pgm_shd.g_old_rec.dpnt_cvg_end_dt_rl,hr_api.g_number) or
5153 nvl(p_dpnt_dsgn_cd,hr_api.g_varchar2)
5154 <> nvl(ben_pgm_shd.g_old_rec.dpnt_dsgn_cd,hr_api.g_varchar2) or
5155 nvl(p_dpnt_dsgn_lvl_cd,hr_api.g_varchar2)
5156 <> nvl(ben_pgm_shd.g_old_rec.dpnt_dsgn_lvl_cd,hr_api.g_varchar2) or
5157 nvl(p_dpnt_cvg_strt_dt_cd,hr_api.g_varchar2)
5158 <> nvl(ben_pgm_shd.g_old_rec.dpnt_cvg_strt_dt_cd,hr_api.g_varchar2) or
5159 nvl(p_dpnt_cvg_strt_dt_rl,hr_api.g_number)
5160 <> nvl(ben_pgm_shd.g_old_rec.dpnt_cvg_strt_dt_rl,hr_api.g_number) or
5161 nvl(p_dpnt_adrs_rqd_flag ,hr_api.g_varchar2)
5162 <> nvl(ben_pgm_shd.g_old_rec.dpnt_adrs_rqd_flag ,hr_api.g_varchar2) or
5163 nvl(p_dpnt_dob_rqd_flag ,hr_api.g_varchar2)
5164 <> nvl(ben_pgm_shd.g_old_rec.dpnt_dob_rqd_flag ,hr_api.g_varchar2) or
5165 nvl(p_dpnt_legv_id_rqd_flag,hr_api.g_varchar2)
5166 <> nvl(ben_pgm_shd.g_old_rec.dpnt_legv_id_rqd_flag,hr_api.g_varchar2) or
5167 nvl(p_dpnt_dsgn_no_ctfn_rqd_flag,hr_api.g_varchar2)
5168 <> nvl(ben_pgm_shd.g_old_rec.dpnt_dsgn_no_ctfn_rqd_flag,hr_api.g_varchar2) or
5169 nvl(p_drvbl_fctr_dpnt_elig_flag,hr_api.g_varchar2)
5170 <> nvl(ben_pgm_shd.g_old_rec.drvbl_fctr_dpnt_elig_flag,hr_api.g_varchar2) or
5171 not l_api_updating)) then
5172 --
5173 if nvl(p_dpnt_dsgn_lvl_cd,'X') not in ('PGM') and
5174 (p_dpnt_dsgn_cd is not null or
5175 p_dpnt_cvg_strt_dt_cd is not null or
5176 p_dpnt_cvg_strt_dt_rl is not null or
5177 p_dpnt_cvg_end_dt_cd is not null or
5178 p_dpnt_cvg_end_dt_rl is not null or
5179 p_dpnt_adrs_rqd_flag = 'Y' or
5180 p_dpnt_dob_rqd_flag = 'Y' or
5181 p_dpnt_legv_id_rqd_flag = 'Y' or
5182 p_dpnt_dsgn_no_ctfn_rqd_flag = 'Y' or
5183 p_drvbl_fctr_dpnt_elig_flag = 'Y') then
5184 --
5185 if p_dpnt_dsgn_lvl_cd is null then
5186 fnd_message.set_name('BEN','BEN_91400_PGM_DSGN_LVL_RQD');
5187 fnd_message.raise_error;
5188 else
5189 fnd_message.set_name('BEN','BEN_91401_INV_DSGN_LVL_PGM');
5190 fnd_message.raise_error;
5191 end if;
5192 --
5193 end if;
5194 --
5195 end if;
5196 --
5197 hr_utility.set_location('Leaving:'||l_proc,10);
5198 --
5199 end chk_dpnt_dsgn_lvl_cd_dpndcy;
5200 --
5201 -- ----------------------------------------------------------------------------
5202 -- |------< chk_dpnt_dsgn_cd_detail >------|
5203 -- ----------------------------------------------------------------------------
5204 --
5205 -- Description
5206 -- If dependent designation level is null then the following tables must
5207 -- contain no records for that program: BEN_LER_CHG_DPNT_CVG_F,
5208 -- BEN_APLD_DPNT_CVG_ELIG_PRFL_F, BEN_PGM_DPNT_CVG_CTFN_F.
5209 --
5210 -- Pre Conditions
5211 -- None.
5212 --
5213 -- In Parameters
5214 -- pgm_id PK of record being inserted or updated.
5215 -- dpnt_dsgn_cd Value of lookup code.
5216 -- pgm_id
5217 -- business_group_id
5218 -- effective_date effective date
5219 -- object_version_number Object version number of record being
5220 -- inserted or updated.
5221 --
5222 -- Post Success
5223 -- Processing continues
5224 --
5225 -- Post Failure
5226 -- Error handled by procedure
5227 --
5228 -- Access Status
5229 -- Internal table handler use only.
5230 --
5231 Procedure chk_dpnt_dsgn_cd_detail(p_pgm_id in number,
5232 p_dpnt_dsgn_cd in varchar2,
5233 p_business_group_id in number,
5234 p_effective_date in date,
5235 p_object_version_number in number) is
5236 --
5237 l_proc varchar2(72) := g_package || 'chk_dpnt_dsgn_cd_detail';
5238 l_api_updating boolean;
5239 l_dummy varchar2(1);
5240 --
5241 cursor c1 is select 'x'
5242 from ben_ler_chg_dpnt_cvg_f ldc
5243 where ldc.pgm_id = p_pgm_id
5244 and ldc.business_group_id + 0 = p_business_group_id
5245 and p_effective_date between ldc.effective_start_date
5246 and ldc.effective_end_date;
5247 --
5248 cursor c2 is select 'x'
5249 from ben_apld_dpnt_cvg_elig_prfl_f ade
5250 where ade.pgm_id = p_pgm_id
5251 and ade.business_group_id + 0 = p_business_group_id
5252 and p_effective_date between ade.effective_start_date
5253 and ade.effective_end_date;
5254 --
5255 cursor c3 is select 'x'
5256 from ben_pgm_dpnt_cvg_ctfn_f pgc
5257 where pgc.pgm_id = p_pgm_id
5258 and pgc.business_group_id + 0 = p_business_group_id
5259 and p_effective_date between pgc.effective_start_date
5260 and pgc.effective_end_date;
5261 --
5262 Begin
5263 --
5264 hr_utility.set_location('Entering:'||l_proc, 5);
5265 --
5266 l_api_updating := ben_pgm_shd.api_updating
5267 (p_pgm_id => p_pgm_id,
5268 p_effective_date => p_effective_date,
5269 p_object_version_number => p_object_version_number);
5270 --
5271
5272 if (l_api_updating
5273 and nvl(p_dpnt_dsgn_cd,hr_api.g_varchar2)
5274 <> nvl(ben_pgm_shd.g_old_rec.dpnt_dsgn_cd,hr_api.g_varchar2)
5275 or not l_api_updating) then
5276 null;
5277 --
5278 -- If ldc records exists and designation is null then error
5279 --
5280 if (p_dpnt_dsgn_cd is null) then
5281 open c1;
5282 fetch c1 into l_dummy;
5283 if c1%found then
5284 --
5285 close c1;
5286 fnd_message.set_name('BEN','BEN_92519_DELETE_LDC1');
5287 fnd_message.raise_error;
5288 --
5289 else
5290 close c1;
5291 end if;
5292 --
5293 end if;
5294 --close c1;
5295 --
5296 -- If ade records exists and designation is null then error
5297 --
5298 if (p_dpnt_dsgn_cd is null) then
5299 open c2;
5300 fetch c2 into l_dummy;
5301 if c2%found then
5302 --
5303 close c2;
5304 fnd_message.set_name('BEN','BEN_92520_DELETE_ADE1');
5305 fnd_message.raise_error;
5306 --
5307 else
5308 close c2;
5309 end if;
5310 --
5311 end if;
5312 --close c2;
5313 --
5314 -- If pgc records exists and designation is null then error
5315 --
5316 if (p_dpnt_dsgn_cd is null) then
5317 open c3;
5318 fetch c3 into l_dummy;
5319 if c3%found then
5320 --
5321 close c3;
5322 fnd_message.set_name('BEN','BEN_92521_DELETE_PGC');
5323 fnd_message.raise_error;
5324 --
5325 else
5326 close c3;
5327 end if;
5328 --
5329 end if;
5330 --close c3;
5331 --
5332 --
5333 end if;
5334 --
5335 hr_utility.set_location('Leaving:'||l_proc,10);
5336 --
5337 end chk_dpnt_dsgn_cd_detail;
5338 --
5339 -- ----------------------------------------------------------------------------
5340 -- |------< chk_dpnt_dsgn_lvl_cd_dpndcy2 >------|
5341 -- ----------------------------------------------------------------------------
5342 --
5343 -- Description
5344 -- If dependent designation level is not 'Program' the following tables must
5345 -- contain no records for that program: BEN_LER_CHG_DPNT_CVG_F,
5346 -- BEN_APLD_DPNT_CVG_ELIG_PRFL_F, BEN_PGM_DPNT_CVG_CTFN_F.
5347 --
5348 -- Pre Conditions
5349 -- None.
5350 --
5351 -- In Parameters
5352 -- pgm_id PK of record being inserted or updated.
5353 -- dpnt_dsgn_lvl_cd Value of lookup code.
5354 -- pgm_id
5355 -- business_group_id
5356 -- effective_date effective date
5357 -- object_version_number Object version number of record being
5358 -- inserted or updated.
5359 --
5360 -- Post Success
5361 -- Processing continues
5362 --
5363 -- Post Failure
5364 -- Error handled by procedure
5365 --
5366 -- Access Status
5367 -- Internal table handler use only.
5368 --
5369 Procedure chk_dpnt_dsgn_lvl_cd_dpndcy2(p_pgm_id in number,
5370 p_dpnt_dsgn_lvl_cd in varchar2,
5371 p_business_group_id in number,
5372 p_effective_date in date,
5373 p_object_version_number in number) is
5374 --
5375 l_proc varchar2(72) := g_package || 'chk_dpnt_dsgn_lvl_cd_dpndcy2';
5376 l_api_updating boolean;
5377 l_dummy varchar2(1);
5378 --
5379 cursor c1 is select null
5380 from ben_ler_chg_dpnt_cvg_f ldc
5381 where ldc.pgm_id = p_pgm_id
5382 and ldc.business_group_id + 0 = p_business_group_id
5383 and p_effective_date between ldc.effective_start_date
5384 and ldc.effective_end_date;
5385 --
5386 cursor c2 is select null
5387 from ben_apld_dpnt_cvg_elig_prfl_f ade
5388 where ade.pgm_id = p_pgm_id
5389 and ade.business_group_id + 0 = p_business_group_id
5390 and p_effective_date between ade.effective_start_date
5391 and ade.effective_end_date;
5392 --
5393 cursor c3 is select 'x'
5394 from ben_pgm_dpnt_cvg_ctfn_f pgc
5395 where pgc.pgm_id = p_pgm_id
5396 and pgc.business_group_id + 0 = p_business_group_id
5397 and p_effective_date between pgc.effective_start_date
5398 and pgc.effective_end_date;
5399 --
5400 Begin
5401 --
5402 hr_utility.set_location('Entering:'||l_proc, 5);
5403 --
5404 l_api_updating := ben_pgm_shd.api_updating
5405 (p_pgm_id => p_pgm_id,
5406 p_effective_date => p_effective_date,
5407 p_object_version_number => p_object_version_number);
5408 --
5409
5410 if (l_api_updating
5411 and nvl(p_dpnt_dsgn_lvl_cd,hr_api.g_varchar2)
5412 <> nvl(ben_pgm_shd.g_old_rec.dpnt_dsgn_lvl_cd,hr_api.g_varchar2)
5413 or not l_api_updating) then
5414 null;
5415 --
5416 -- If ldc records exists and designation level not 'PGM' then error
5417 --
5418 open c1;
5419 fetch c1 into l_dummy;
5420 if c1%found then
5421 if nvl(p_dpnt_dsgn_lvl_cd,'X') not in ('PGM') then
5422 --
5423 close c1;
5424 fnd_message.set_name('BEN','BEN_91376_DSGN_LVL_PGM_LDC');
5425 fnd_message.raise_error;
5426 --
5427 end if;
5428 --
5429 end if;
5430 close c1;
5431 --
5432 -- If ade records exists and designation level not 'PGM' then error
5433 --
5434 open c2;
5435 fetch c2 into l_dummy;
5436 if c2%found then
5437 if nvl(p_dpnt_dsgn_lvl_cd,'X') not in ('PGM') then
5438 --
5439 close c2;
5440 fnd_message.set_name('BEN','BEN_91395_DSGN_LVL_PGM_ADE');
5441 fnd_message.raise_error;
5442 --
5443 end if;
5444 --
5445 end if;
5446 close c2;
5447 --
5448 -- If pgc records exists and designation level not 'PGM' then error
5449 --
5450 open c3;
5451 fetch c3 into l_dummy;
5452 if c3%notfound then
5453 null;
5454 else
5455 if nvl(p_dpnt_dsgn_lvl_cd,'X') not in ('PGM') then
5456 --
5457 close c3;
5458 fnd_message.set_name('BEN','BEN_91396_DSGN_LVL_PGM_PGC');
5459 fnd_message.raise_error;
5460 --
5461 end if;
5462 --
5463 end if;
5464 close c3;
5465 --
5466 --
5467 end if;
5468 --
5469 hr_utility.set_location('Leaving:'||l_proc,10);
5470 --
5471 end chk_dpnt_dsgn_lvl_cd_dpndcy2;
5472 --
5473 /*
5474 -- ----------------------------------------------------------------------------
5475 -- |------< chk_dpnt_dsgn_lvl_cd_dpndcy3 >------|
5476 -- ----------------------------------------------------------------------------
5477 --
5478 -- Description
5479 -- If dependent designation level is not 'Plan Type' the following fields
5480 -- from BEN_PTIP_F must be null: Dpnt Designation, Cert req
5481 -- Derivable factors apply flag.
5482 --
5483 --
5484 -- Pre Conditions
5485 -- None.
5486 --
5487 -- In Parameters
5488 -- pgm_id PK of record being inserted or updated.
5489 -- dpnt_dsgn_lvl_cd Value of lookup code.
5490 -- pgm_id
5491 -- business_group_id
5492 -- effective_date effective date
5493 -- object_version_number Object version number of record being
5494 -- inserted or updated.
5495 --
5496 -- Post Success
5497 -- Processing continues
5498 --
5499 -- Post Failure
5500 -- Error handled by procedure
5501 --
5502 -- Access Status
5503 -- Internal table handler use only.
5504 --
5505 Procedure chk_dpnt_dsgn_lvl_cd_dpndcy3(p_pgm_id in number,
5506 p_dpnt_dsgn_lvl_cd in varchar2,
5507 p_business_group_id in number,
5508 p_effective_date in date,
5509 p_object_version_number in number) is
5510 --
5511 l_proc varchar2(72) := g_package || 'chk_dpnt_dsgn_lvl_cd_dpndcy3';
5512 l_api_updating boolean;
5513 l_dummy varchar2(30);
5514 --
5515 cursor c1 is select null
5516 from ben_ptip_f ctp
5517 where ctp.pgm_id = p_pgm_id
5518 and (ctp.dpnt_dsgn_cd is not null or
5519 ctp.drvd_fctr_dpnt_cvg_flag = 'Y' or
5520 ctp.dpnt_dsgn_no_cvg_ctfn_rqd = 'Y')
5521 and ctp.business_group_id + 0 = p_business_group_id
5522 and p_effective_date between ctp.effective_start_date
5523 and ctp.effective_end_date;
5524 --
5525 Begin
5526 --
5527 hr_utility.set_location('Entering:'||l_proc, 5);
5528 --
5529 l_api_updating := ben_pgm_shd.api_updating
5530 (p_pgm_id => p_pgm_id,
5531 p_effective_date => p_effective_date,
5532 p_object_version_number => p_object_version_number);
5533 --
5534
5535 if (l_api_updating
5536 and nvl(p_dpnt_dsgn_lvl_cd,hr_api.g_varchar2)
5537 <> nvl(ben_pgm_shd.g_old_rec.dpnt_dsgn_lvl_cd,hr_api.g_varchar2)
5538 or not l_api_updating) then
5539 --
5540 -- If dsgn_lvl not = PTIP and cursor found then error.
5541 --
5542 if nvl(p_dpnt_dsgn_lvl_cd,'X') not in ('PTIP') then
5543 open c1;
5544 fetch c1 into l_dummy;
5545 if c1%found then
5546 close c1;
5547 --
5548 fnd_message.set_name('BEN','BEN_91402_INV_DSGN_LVL_PT2');
5549 fnd_message.raise_error;
5550 --
5551 end if;
5552 close c1;
5553 end if;
5554 --
5555 end if;
5556 --
5557 hr_utility.set_location('Leaving:'||l_proc,10);
5558 --
5559 end chk_dpnt_dsgn_lvl_cd_dpndcy3;
5560 --
5561 */
5562 -- ----------------------------------------------------------------------------
5563 -- |------< chk_dpnt_dsgn_lvl_cd_dpndcy4 >------|
5564 -- ----------------------------------------------------------------------------
5565 --
5566 -- Description
5567 -- If dependent designation level is not 'Plan Type' the following tables must
5568 -- contain no records for that plan type: BEN_LER_CHG_DPNT_CVG_F,
5569 -- BEN_APLD_DPNT_CVG_ELIG_PRFL_F, BEN_PTIP_DPNT_CVG_CTFN_F.
5570 --
5571 -- Pre Conditions
5572 -- None.
5573 --
5574 -- In Parameters
5575 -- pgm_id PK of record being inserted or updated.
5576 -- dpnt_dsgn_lvl_cd Value of lookup code.
5577 -- pgm_id
5578 -- business_group_id
5579 -- effective_date effective date
5580 -- object_version_number Object version number of record being
5581 -- inserted or updated.
5582 --
5583 -- Post Success
5584 -- Processing continues
5585 --
5586 -- Post Failure
5587 -- Error handled by procedure
5588 --
5589 -- Access Status
5590 -- Internal table handler use only.
5591 --
5592 Procedure chk_dpnt_dsgn_lvl_cd_dpndcy4(p_pgm_id in number,
5593 p_dpnt_dsgn_lvl_cd in varchar2,
5594 p_business_group_id in number,
5595 p_effective_date in date,
5596 p_object_version_number in number) is
5597 --
5598 l_proc varchar2(72) := g_package || 'chk_dpnt_dsgn_lvl_cd_dpndcy4';
5599 l_api_updating boolean;
5600 l_dummy varchar2(30);
5601 --
5602 cursor c1 is select null
5603 from BEN_PTIP_F ctp,
5604 BEN_LER_CHG_DPNT_CVG_F ldc
5605 where ctp.pgm_id = p_pgm_id
5606 and ctp.business_group_id +0 = p_business_group_id
5607 and p_effective_date between ctp.effective_start_date
5608 and ctp.effective_end_date
5609 and ldc.ptip_id = ctp.ptip_id
5610 and ldc.business_group_id +0 = p_business_group_id
5611 and p_effective_date between ldc.effective_start_date
5612 and ldc.effective_end_date;
5613 --
5614 cursor c2 is select null
5615 from BEN_PTIP_F ctp,
5616 BEN_APLD_DPNT_CVG_ELIG_PRFL_F ade
5617 where ctp.pgm_id = p_pgm_id
5618 and ctp.business_group_id +0 = p_business_group_id
5619 and p_effective_date between ctp.effective_start_date
5620 and ctp.effective_end_date
5621 and ade.ptip_id = ctp.ptip_id
5622 and ade.business_group_id +0 = p_business_group_id
5623 and p_effective_date between ade.effective_start_date
5624 and ade.effective_end_date;
5625 --
5626 cursor c3 is select null
5627 from BEN_PTIP_F ctp,
5628 BEN_PTIP_DPNT_CVG_CTFN_F pyd
5629 where ctp.pgm_id = p_pgm_id
5630 and ctp.business_group_id +0 = p_business_group_id
5631 and p_effective_date between ctp.effective_start_date
5632 and ctp.effective_end_date
5633 and pyd.ptip_id = ctp.ptip_id
5634 and pyd.business_group_id +0 = p_business_group_id
5635 and p_effective_date between pyd.effective_start_date
5636 and pyd.effective_end_date;
5637 --
5638 Begin
5639 --
5640 hr_utility.set_location('Entering:'||l_proc, 5);
5641 --
5642 l_api_updating := ben_pgm_shd.api_updating
5643 (p_pgm_id => p_pgm_id,
5644 p_effective_date => p_effective_date,
5645 p_object_version_number => p_object_version_number);
5646 --
5647
5648 if (l_api_updating
5649 and nvl(p_dpnt_dsgn_lvl_cd,hr_api.g_varchar2)
5650 <> nvl(ben_pgm_shd.g_old_rec.dpnt_dsgn_lvl_cd,hr_api.g_varchar2)
5651 or not l_api_updating) then null;
5652 --
5653 -- If ldc records exists and designation level not 'PTIP' then error
5654 --
5655 open c1;
5656 fetch c1 into l_dummy;
5657 if c1%found then
5658 if nvl(p_dpnt_dsgn_lvl_cd,'X') not in ('PTIP') then
5659 --
5660 close c1;
5661 fnd_message.set_name('BEN','BEN_91397_DSGN_LVL_PTIP_LDC');
5662 fnd_message.raise_error;
5663 --
5664 end if;
5665 --
5666 end if;
5667 close c1;
5668 --
5669 -- If ade records exists and designation level not 'PTIP' then error
5670 --
5671 open c2;
5672 fetch c2 into l_dummy;
5673 if c2%found then
5674 if nvl(p_dpnt_dsgn_lvl_cd,'X') not in ('PTIP') then
5675 --
5676 close c2;
5677 fnd_message.set_name('BEN','BEN_91398_DSGN_LVL_PTIP_ADE');
5678 fnd_message.raise_error;
5679 --
5680 end if;
5681 --
5682 end if;
5683 close c2;
5684 --
5685 -- If pyd records exists and designation level not 'PTIP' then error
5686 --
5687 open c3;
5688 fetch c3 into l_dummy;
5689 if c3%found then
5690 if nvl(p_dpnt_dsgn_lvl_cd,'X') not in ('PTIP') then
5691 --
5692 close c3;
5693 fnd_message.set_name('BEN','BEN_91399_DSGN_LVL_PTIP_PYD');
5694 fnd_message.raise_error;
5695 --
5696 end if;
5697 --
5698 end if;
5699 close c3;
5700 --
5701 --
5702 end if;
5703 --
5704 hr_utility.set_location('Leaving:'||l_proc,10);
5705 --
5706 end chk_dpnt_dsgn_lvl_cd_dpndcy4;
5707 --
5708 -- ----------------------------------------------------------------------------
5709 -- |-------------------------< chk_enrt_cd_gsp >----------------------------------|
5710 -- ----------------------------------------------------------------------------
5711 --
5712 -- Description
5713 -- This procedure is used to ensure that lookup codes PQH_GSP_GP,
5714 -- PQH_GSP_GSP, PQH_GSP_NP, PQH_GSP_SP are used only for Grade Step.
5715 --
5716 -- Pre Conditions
5717 -- None.
5718 --
5719 -- In Parameters
5720 -- pgm_id PK of record being inserted or updated.
5721 -- enrt_cd Value of lookup code.
5722 -- effective_date effective date
5723 -- object_version_number Object version number of record being
5724 -- inserted or updated.
5725 --
5726 -- Post Success
5727 -- Processing continues
5728 --
5729 -- Post Failure
5730 -- Error handled by procedure
5731 --
5732 -- Access Status
5733 -- Internal table handler use only.
5734 --
5735 Procedure chk_enrt_cd_gsp
5736 (p_pgm_id in number,
5737 p_pgm_typ_cd in varchar2,
5738 p_enrt_cd in varchar2,
5739 p_effective_date in date,
5740 p_object_version_number in number) is
5741 --
5742 l_proc varchar2(72) := g_package||'chk_enrt_cd_gsp';
5743 l_api_updating boolean;
5744 --
5745 Begin
5746 --
5747 hr_utility.set_location('Entering:'||l_proc, 5);
5748
5749 --
5750 l_api_updating := ben_pgm_shd.api_updating
5751 (p_pgm_id => p_pgm_id,
5752 p_effective_date => p_effective_date,
5753 p_object_version_number => p_object_version_number);
5754 --
5755 if (l_api_updating
5756 and (
5757 p_enrt_cd <> nvl(ben_pgm_shd.g_old_rec.enrt_cd,hr_api.g_varchar2)
5758 or p_pgm_typ_cd <> nvl(ben_pgm_shd.g_old_rec.pgm_typ_cd,hr_api.g_varchar2)
5759 )
5760 or not l_api_updating)
5761 and p_enrt_cd is not null then
5762 --
5763 -- check if value of lookup falls within lookup type.
5764 --
5765 --
5766 if (p_pgm_typ_cd <> 'GSP'
5767 and p_enrt_cd in ('PQH_GSP_GP', 'PQH_GSP_GSP', 'PQH_GSP_NP', 'PQH_GSP_SP')
5768 ) then
5769 --
5770 -- raise error as does not exist as lookup
5771 --
5772 --fnd_message.set_name('BEN','BEN_91950_INVALID_ENRT_CD');
5773 fnd_message.set_name('BEN','BEN_93529_GSP_ENRT_CD');
5774 fnd_message.raise_error;
5775 --
5776 end if;
5777 --
5778 end if;
5779 --
5780 hr_utility.set_location('Leaving:'||l_proc,10);
5781 --
5782 end chk_enrt_cd_gsp;
5783 --
5784
5785 -- ----------------------------------------------------------------------------
5786 -- |--------------------------< dt_update_validate >--------------------------|
5787 -- ----------------------------------------------------------------------------
5788 -- {Start Of Comments}
5789 --
5790 -- Description:
5791 -- This procedure is used for referential integrity of datetracked
5792 -- parent entities when a datetrack update operation is taking place
5793 -- and where there is no cascading of update defined for this entity.
5794 --
5795 -- Prerequisites:
5796 -- This procedure is called from the update_validate.
5797 --
5798 -- In Parameters:
5799 --
5800 -- Post Success:
5801 -- Processing continues.
5802 --
5803 -- Post Failure:
5804 --
5805 -- Developer Implementation Notes:
5806 -- This procedure should not need maintenance unless the HR Schema model
5807 -- changes.
5808 --
5809 -- Access Status:
5810 -- Internal Row Handler Use Only.
5811 --
5812 -- {End Of Comments}
5813 -- ----------------------------------------------------------------------------
5814 Procedure dt_update_validate
5815 (p_dpnt_cvg_strt_dt_rl in number default hr_api.g_number,
5816 p_dpnt_cvg_end_dt_rl in number default hr_api.g_number,
5817 p_datetrack_mode in varchar2,
5818 p_enrt_cvg_strt_dt_rl in number,
5819 p_enrt_cvg_end_dt_rl in number,
5820 p_validation_start_date in date,
5821 p_validation_end_date in date) Is
5822 --
5823 l_proc varchar2(72) := g_package||'dt_update_validate';
5824 l_integrity_error Exception;
5825 l_table_name all_tables.table_name%TYPE;
5826 --
5827 Begin
5828 hr_utility.set_location('Entering:'||l_proc, 5);
5829 --
5830 -- Ensure that the p_datetrack_mode argument is not null
5831 --
5832 hr_api.mandatory_arg_error
5833 (p_api_name => l_proc,
5834 p_argument => 'datetrack_mode',
5835 p_argument_value => p_datetrack_mode);
5836 --
5837 -- Only perform the validation if the datetrack update mode is valid
5838 --
5839 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
5840 --
5841 --
5842 -- Ensure the arguments are not null
5843 --
5844 hr_api.mandatory_arg_error
5845 (p_api_name => l_proc,
5846 p_argument => 'validation_start_date',
5847 p_argument_value => p_validation_start_date);
5848 --
5849 hr_api.mandatory_arg_error
5850 (p_api_name => l_proc,
5851 p_argument => 'validation_end_date',
5852 p_argument_value => p_validation_end_date);
5853 --
5854
5855 If ((nvl(p_enrt_cvg_end_dt_rl, hr_api.g_number) <> hr_api.g_number) and
5856 NOT (dt_api.check_min_max_dates
5857 (p_base_table_name => 'ff_formulas_f',
5858 p_base_key_column => 'formula_id',
5859 p_base_key_value => p_enrt_cvg_end_dt_rl,
5860 p_from_date => p_validation_start_date,
5861 p_to_date => p_validation_end_date))) Then
5862 l_table_name := 'ff_formulas_f';
5863 Raise l_integrity_error;
5864 End If;
5865 If ((nvl(p_enrt_cvg_strt_dt_rl, hr_api.g_number) <> hr_api.g_number) and
5866 NOT (dt_api.check_min_max_dates
5867 (p_base_table_name => 'ff_formulas_f',
5868 p_base_key_column => 'formula_id',
5869 p_base_key_value => p_enrt_cvg_strt_dt_rl,
5870 p_from_date => p_validation_start_date,
5871 p_to_date => p_validation_end_date))) Then
5872 l_table_name := 'ff_formulas_f';
5873 Raise l_integrity_error;
5874 End If;
5875 If ((nvl(p_dpnt_cvg_strt_dt_rl, hr_api.g_number) <> hr_api.g_number) and
5876 NOT (dt_api.check_min_max_dates
5877 (p_base_table_name => 'ff_formulas_f',
5878 p_base_key_column => 'formula_id',
5879 p_base_key_value => p_dpnt_cvg_strt_dt_rl,
5880 p_from_date => p_validation_start_date,
5881 p_to_date => p_validation_end_date))) Then
5882 l_table_name := 'ff_formulas_f';
5883 Raise l_integrity_error;
5884 End If;
5885 If ((nvl(p_dpnt_cvg_end_dt_rl, hr_api.g_number) <> hr_api.g_number) and
5886 NOT (dt_api.check_min_max_dates
5887 (p_base_table_name => 'ff_formulas_f',
5888 p_base_key_column => 'formula_id',
5889 p_base_key_value => p_dpnt_cvg_end_dt_rl,
5890 p_from_date => p_validation_start_date,
5891 p_to_date => p_validation_end_date))) Then
5892 l_table_name := 'ff_formulas_f';
5893 Raise l_integrity_error;
5894 End If;
5895 /*
5896 If ((nvl(p_prtn_end_dt_rl, hr_api.g_number) <> hr_api.g_number) and
5897 NOT (dt_api.check_min_max_dates
5898 (p_base_table_name => 'ff_formulas_f',
5899 p_base_key_column => 'formula_id',
5900 p_base_key_value => p_prtn_end_dt_rl,
5901 p_from_date => p_validation_start_date,
5902 p_to_date => p_validation_end_date))) Then
5903 l_table_name := 'ff_formulas_f';
5904 Raise l_integrity_error;
5905 End If;
5906 If ((nvl(p_prtn_strt_dt_rl, hr_api.g_number) <> hr_api.g_number) and
5907 NOT (dt_api.check_min_max_dates
5908 (p_base_table_name => 'ff_formulas_f',
5909 p_base_key_column => 'formula_id',
5910 p_base_key_value => p_prtn_strt_dt_rl,
5911 p_from_date => p_validation_start_date,
5912 p_to_date => p_validation_end_date))) Then
5913 l_table_name := 'ff_formulas_f';
5914 Raise l_integrity_error;
5915 End If;
5916 */
5917 --
5918 End If;
5919 --
5920 hr_utility.set_location(' Leaving:'||l_proc, 10);
5921 Exception
5922 When l_integrity_error Then
5923 --
5924 -- A referential integrity check was violated therefore
5925 -- we must error
5926 --
5927 ben_utility.parent_integrity_error(p_table_name => l_table_name);
5928 When Others Then
5929 --
5930 -- An unhandled or unexpected error has occurred which
5931 -- we must report
5932 --
5933 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
5934 fnd_message.set_token('PROCEDURE', l_proc);
5935 fnd_message.set_token('STEP','15');
5936 fnd_message.raise_error;
5937 End dt_update_validate;
5938 --
5939 -- ----------------------------------------------------------------------------
5940 -- |--------------------------< dt_delete_validate >--------------------------|
5941 -- ----------------------------------------------------------------------------
5942 -- {Start Of Comments}
5943 --
5944 -- Description:
5945 -- This procedure is used for referential integrity of datetracked
5946 -- child entities when either a datetrack DELETE or ZAP is in operation
5947 -- and where there is no cascading of delete defined for this entity.
5948 -- For the datetrack mode of DELETE or ZAP we must ensure that no
5949 -- datetracked child rows exist between the validation start and end
5950 -- dates.
5951 --
5952 -- Prerequisites:
5953 -- This procedure is called from the delete_validate.
5954 --
5955 -- In Parameters:
5956 --
5957 -- Post Success:
5958 -- Processing continues.
5959 --
5960 -- Post Failure:
5961 -- If a row exists by determining the returning Boolean value from the
5962 -- generic dt_api.rows_exist function then we must supply an error via
5963 -- the use of the local exception handler l_rows_exist.
5964 --
5965 -- Developer Implementation Notes:
5966 -- This procedure should not need maintenance unless the HR Schema model
5967 -- changes.
5968 --
5969 -- Access Status:
5970 -- Internal Row Handler Use Only.
5971 --
5972 -- {End Of Comments}
5973 -- ----------------------------------------------------------------------------
5974 Procedure dt_delete_validate
5975 (p_pgm_id in number,
5976 p_datetrack_mode in varchar2,
5977 p_validation_start_date in date,
5978 p_validation_end_date in date) Is
5979 --
5980 l_proc varchar2(72) := g_package||'dt_delete_validate';
5981 l_rows_exist Exception;
5982 l_table_name all_tables.table_name%TYPE;
5983 --
5984 l_val number;
5985 --
5986 Cursor c_yr_perd_exists(p_pgm_id in number ) Is
5987 select 1
5988 from ben_popl_yr_perd t
5989 where t.pl_id = p_pgm_id ;
5990 --
5991 Begin
5992 hr_utility.set_location('Entering:'||l_proc, 5);
5993 --
5994 -- Ensure that the p_datetrack_mode argument is not null
5995 --
5996 hr_api.mandatory_arg_error
5997 (p_api_name => l_proc,
5998 p_argument => 'datetrack_mode',
5999 p_argument_value => p_datetrack_mode);
6000 --
6001 -- Only perform the validation if the datetrack mode is either
6002 -- DELETE or ZAP
6003 --
6004 If (p_datetrack_mode = 'DELETE' or
6005 p_datetrack_mode = 'ZAP') then
6006 --
6007 --
6008 -- Ensure the arguments are not null
6009 --
6010 hr_api.mandatory_arg_error
6011 (p_api_name => l_proc,
6012 p_argument => 'validation_start_date',
6013 p_argument_value => p_validation_start_date);
6014 --
6015 hr_api.mandatory_arg_error
6016 (p_api_name => l_proc,
6017 p_argument => 'validation_end_date',
6018 p_argument_value => p_validation_end_date);
6019 --
6020 hr_api.mandatory_arg_error
6021 (p_api_name => l_proc,
6022 p_argument => 'pgm_id',
6023 p_argument_value => p_pgm_id);
6024 --
6025 If (dt_api.rows_exist
6026 (p_base_table_name => 'ben_ptip_f',
6027 p_base_key_column => 'pgm_id',
6028 p_base_key_value => p_pgm_id,
6029 p_from_date => p_validation_start_date,
6030 p_to_date => p_validation_end_date)) Then
6031 l_table_name := 'ben_ptip_f';
6032 Raise l_rows_exist;
6033 End If;
6034 If (dt_api.rows_exist
6035 (p_base_table_name => 'ben_popl_enrt_typ_cycl_f',
6036 p_base_key_column => 'pgm_id',
6037 p_base_key_value => p_pgm_id,
6038 p_from_date => p_validation_start_date,
6039 p_to_date => p_validation_end_date)) Then
6040 l_table_name := 'ben_popl_enrt_typ_cycl_f';
6041 Raise l_rows_exist;
6042 End If;
6043 If (dt_api.rows_exist
6044 (p_base_table_name => 'ben_plip_f',
6045 p_base_key_column => 'pgm_id',
6046 p_base_key_value => p_pgm_id,
6047 p_from_date => p_validation_start_date,
6048 p_to_date => p_validation_end_date)) Then
6049 l_table_name := 'ben_plip_f';
6050 Raise l_rows_exist;
6051 End If;
6052 If (dt_api.rows_exist
6053 (p_base_table_name => 'ben_prtn_elig_f',
6054 p_base_key_column => 'pgm_id',
6055 p_base_key_value => p_pgm_id,
6056 p_from_date => p_validation_start_date,
6057 p_to_date => p_validation_end_date)) Then
6058 l_table_name := 'ben_prtn_elig_f';
6059 Raise l_rows_exist;
6060 End If;
6061 If (dt_api.rows_exist
6062 (p_base_table_name => 'ben_elig_to_prte_rsn_f',
6063 p_base_key_column => 'pgm_id',
6064 p_base_key_value => p_pgm_id,
6065 p_from_date => p_validation_start_date,
6066 p_to_date => p_validation_end_date)) Then
6067 l_table_name := 'ben_elig_to_prte_rsn_f';
6068 Raise l_rows_exist;
6069 End If;
6070 If (dt_api.rows_exist
6071 (p_base_table_name => 'ben_ler_chg_dpnt_cvg_f',
6072 p_base_key_column => 'pgm_id',
6073 p_base_key_value => p_pgm_id,
6074 p_from_date => p_validation_start_date,
6075 p_to_date => p_validation_end_date)) Then
6076 l_table_name := 'ben_ler_chg_dpnt_cvg_f';
6077 Raise l_rows_exist;
6078 End If;
6079 If (dt_api.rows_exist
6080 (p_base_table_name => 'ben_popl_org_f',
6081 p_base_key_column => 'pgm_id',
6082 p_base_key_value => p_pgm_id,
6083 p_from_date => p_validation_start_date,
6084 p_to_date => p_validation_end_date)) Then
6085 l_table_name := 'ben_popl_org_f';
6086 Raise l_rows_exist;
6087 End If;
6088 If (dt_api.rows_exist
6089 (p_base_table_name => 'ben_elig_per_f',
6090 p_base_key_column => 'pgm_id',
6091 p_base_key_value => p_pgm_id,
6092 p_from_date => p_validation_start_date,
6093 p_to_date => p_validation_end_date)) Then
6094 l_table_name := 'ben_elig_per_f';
6095 Raise l_rows_exist;
6096 End If;
6097 If (dt_api.rows_exist
6098 (p_base_table_name => 'ben_acty_base_rt_f',
6099 p_base_key_column => 'pgm_id',
6100 p_base_key_value => p_pgm_id,
6101 p_from_date => p_validation_start_date,
6102 p_to_date => p_validation_end_date)) Then
6103 l_table_name := 'ben_acty_base_rt_f';
6104 Raise l_rows_exist;
6105 End If;
6106 If (dt_api.rows_exist
6107 (p_base_table_name => 'ben_pgm_dpnt_cvg_ctfn_f',
6108 p_base_key_column => 'pgm_id',
6109 p_base_key_value => p_pgm_id,
6110 p_from_date => p_validation_start_date,
6111 p_to_date => p_validation_end_date)) Then
6112 l_table_name := 'ben_pgm_dpnt_cvg_ctfn_f';
6113 Raise l_rows_exist;
6114 End If;
6115 If (dt_api.rows_exist
6116 (p_base_table_name => 'ben_apld_dpnt_cvg_elig_prfl_f',
6117 p_base_key_column => 'pgm_id',
6118 p_base_key_value => p_pgm_id,
6119 p_from_date => p_validation_start_date,
6120 p_to_date => p_validation_end_date)) Then
6121 l_table_name := 'ben_apld_dpnt_cvg_elig_prfl_f';
6122 Raise l_rows_exist;
6123 End If;
6124 If (dt_api.rows_exist
6125 (p_base_table_name => 'ben_popl_rptg_grp_f',
6126 p_base_key_column => 'pgm_id',
6127 p_base_key_value => p_pgm_id,
6128 p_from_date => p_validation_start_date,
6129 p_to_date => p_validation_end_date)) Then
6130 l_table_name := 'ben_popl_rptg_grp_f';
6131 Raise l_rows_exist;
6132 End If;
6133 If (dt_api.rows_exist
6134 (p_base_table_name => 'ben_bnft_prvdr_pool_f',
6135 p_base_key_column => 'pgm_id',
6136 p_base_key_value => p_pgm_id,
6137 p_from_date => p_validation_start_date,
6138 p_to_date => p_validation_end_date)) Then
6139 l_table_name := 'ben_bnft_prvdr_pool_f';
6140 Raise l_rows_exist;
6141 End If;
6142 If (dt_api.rows_exist
6143 (p_base_table_name => 'ben_drvbl_fctr_uom',
6144 p_base_key_column => 'pgm_id',
6145 p_base_key_value => p_pgm_id,
6146 p_from_date => p_validation_start_date,
6147 p_to_date => p_validation_end_date)) Then
6148 l_table_name := 'ben_drvbl_fctr_uom';
6149 Raise l_rows_exist;
6150 End If;
6151 If (dt_api.rows_exist
6152 (p_base_table_name => 'ben_elig_cbr_quald_bnf_f',
6153 p_base_key_column => 'pgm_id',
6154 p_base_key_value => p_pgm_id,
6155 p_from_date => p_validation_start_date,
6156 p_to_date => p_validation_end_date)) Then
6157 l_table_name := 'ben_elig_cbr_quald_bnf_f';
6158 Raise l_rows_exist;
6159 End If;
6160 /*
6161 hr_utility.set_location('!!!!! 18', 5);
6162 If (dt_api.rows_exist
6163 (p_base_table_name => 'ben_elig_per_elctbl_chc',
6164 p_base_key_column => 'pgm_id',
6165 p_base_key_value => p_pgm_id,
6166 p_from_date => p_validation_start_date,
6167 p_to_date => p_validation_end_date)) Then
6168 l_table_name := 'ben_elig_per_elctbl_chc';
6169 Raise l_rows_exist;
6170 End If;
6171 hr_utility.set_location('!!!!! 19', 5);
6172
6173 If (dt_api.rows_exist -- Uncommented, Bug 4339842
6174 (p_base_table_name => 'ben_popl_yr_perd',
6175 p_base_key_column => 'pgm_id',
6176 p_base_key_value => p_pgm_id,
6177 p_from_date => p_validation_start_date,
6178 p_to_date => p_validation_end_date)) Then
6179 l_table_name := 'ben_popl_yr_perd';
6180 Raise l_rows_exist;
6181 End If;
6182 */
6183 open c_yr_perd_exists(p_pgm_id );
6184 fetch c_yr_perd_exists into l_val ;
6185 if c_yr_perd_exists%found
6186 then
6187 close c_yr_perd_exists;
6188 l_table_name := 'ben_popl_yr_perd';
6189 Raise l_rows_exist;
6190 end if;
6191 close c_yr_perd_exists;
6192 --
6193 End If;
6194 --
6195 hr_utility.set_location(' Leaving:'||l_proc, 10);
6196 Exception
6197 When l_rows_exist Then
6198 --
6199 -- A referential integrity check was violated therefore
6200 -- we must error
6201 --
6202 ben_utility.child_exists_error(p_table_name => l_table_name);
6203 When Others Then
6204 --
6205 -- An unhandled or unexpected error has occurred which
6206 -- we must report
6207 --
6208 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
6209 fnd_message.set_token('PROCEDURE', l_proc);
6210 fnd_message.set_token('STEP','15');
6211 fnd_message.raise_error;
6212 End dt_delete_validate;
6213 --
6214 -- ----------------------------------------------------------------------------
6215 -- |---------------------------< insert_validate >----------------------------|
6216 -- ----------------------------------------------------------------------------
6217 Procedure insert_validate
6218 (p_rec in ben_pgm_shd.g_rec_type,
6219 p_effective_date in date,
6220 p_datetrack_mode in varchar2,
6221 p_validation_start_date in date,
6222 p_validation_end_date in date) is
6223 --
6224 l_proc varchar2(72) := g_package||'insert_validate';
6225 --
6226 Begin
6227 hr_utility.set_location('Entering:'||l_proc, 5);
6228 --
6229 chk_pgm_id
6230 (p_pgm_id => p_rec.pgm_id,
6231 p_effective_date => p_effective_date,
6232 p_object_version_number => p_rec.object_version_number);
6233 --
6234 chk_enrt_cvg_end_dt_rl
6235 (p_pgm_id => p_rec.pgm_id,
6236 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
6237 p_effective_date => p_effective_date,
6238 p_object_version_number => p_rec.object_version_number,
6239 p_business_group_id => p_rec.business_group_id);
6240 --
6241 chk_enrt_cvg_strt_dt_rl
6242 (p_pgm_id => p_rec.pgm_id,
6243 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
6244 p_effective_date => p_effective_date,
6245 p_object_version_number => p_rec.object_version_number,
6246 p_business_group_id => p_rec.business_group_id);
6247 --
6248 chk_dpnt_cvg_strt_dt_rl
6249 (p_pgm_id => p_rec.pgm_id,
6250 p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
6251 p_effective_date => p_effective_date,
6252 p_object_version_number => p_rec.object_version_number,
6253 p_business_group_id => p_rec.business_group_id);
6254 --
6255 chk_rt_end_dt_rl
6256 (p_pgm_id => p_rec.pgm_id,
6257 p_rt_end_dt_rl => p_rec.rt_end_dt_rl,
6258 p_effective_date => p_effective_date,
6259 p_object_version_number => p_rec.object_version_number,
6260 p_business_group_id => p_rec.business_group_id);
6261 --
6262
6263 chk_cd_rl_combination
6264 (p_vrfy_fmly_mmbr_cd => p_rec.vrfy_fmly_mmbr_cd,
6265 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl);
6266 --
6267 chk_rt_strt_dt_rl
6268 (p_pgm_id => p_rec.pgm_id,
6269 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
6270 p_effective_date => p_effective_date,
6271 p_object_version_number => p_rec.object_version_number,
6272 p_business_group_id => p_rec.business_group_id);
6273 --
6274 chk_dpnt_cvg_end_dt_rl
6275 (p_pgm_id => p_rec.pgm_id,
6276 p_dpnt_cvg_end_dt_rl => p_rec.dpnt_cvg_end_dt_rl,
6277 p_effective_date => p_effective_date,
6278 p_object_version_number => p_rec.object_version_number,
6279 p_business_group_id => p_rec.business_group_id);
6280 --
6281 /*
6282 chk_prtn_end_dt_rl
6283 (p_pgm_id => p_rec.pgm_id,
6284 p_prtn_end_dt_rl => p_rec.prtn_end_dt_rl,
6285 p_effective_date => p_effective_date,
6286 p_object_version_number => p_rec.object_version_number,
6287 p_business_group_id => p_rec.business_group_id);
6288 --
6289 chk_prtn_strt_dt_rl
6290 (p_pgm_id => p_rec.pgm_id,
6291 p_prtn_strt_dt_rl => p_rec.prtn_strt_dt_rl,
6292 p_effective_date => p_effective_date,
6293 p_object_version_number => p_rec.object_version_number,
6294 p_business_group_id => p_rec.business_group_id);
6295 */
6296 --
6297 chk_dpnt_adrs_rqd_flag
6298 (p_pgm_id => p_rec.pgm_id,
6299 p_dpnt_adrs_rqd_flag => p_rec.dpnt_adrs_rqd_flag,
6300 p_effective_date => p_effective_date,
6301 p_object_version_number => p_rec.object_version_number);
6302 --
6303 /*
6304 chk_elig_apls_to_all_pls_flag
6305 (p_pgm_id => p_rec.pgm_id,
6306 p_elig_apls_to_all_pls_flag => p_rec.elig_apls_to_all_pls_flag,
6307 p_effective_date => p_effective_date,
6308 p_object_version_number => p_rec.object_version_number);
6309 */
6310 --
6311 chk_dpnt_dob_rqd_flag
6312 (p_pgm_id => p_rec.pgm_id,
6313 p_dpnt_dob_rqd_flag => p_rec.dpnt_dob_rqd_flag,
6314 p_effective_date => p_effective_date,
6315 p_object_version_number => p_rec.object_version_number);
6316 --
6317 chk_pgm_prvd_no_auto_enrt_flag
6318 (p_pgm_id => p_rec.pgm_id,
6319 p_pgm_prvds_no_auto_enrt_flag => p_rec.pgm_prvds_no_auto_enrt_flag,
6320 p_effective_date => p_effective_date,
6321 p_object_version_number => p_rec.object_version_number);
6322 --
6323 chk_pgm_prvd_no_dflt_enrt_flag
6324 (p_pgm_id => p_rec.pgm_id,
6325 p_pgm_prvds_no_dflt_enrt_flag => p_rec.pgm_prvds_no_dflt_enrt_flag,
6326 p_effective_date => p_effective_date,
6327 p_object_version_number => p_rec.object_version_number);
6328 --
6329 chk_dpnt_legv_id_rqd_flag
6330 (p_pgm_id => p_rec.pgm_id,
6331 p_dpnt_legv_id_rqd_flag => p_rec.dpnt_legv_id_rqd_flag,
6332 p_effective_date => p_effective_date,
6333 p_object_version_number => p_rec.object_version_number);
6334 --
6335 chk_elig_apls_flag
6336 (p_pgm_id => p_rec.pgm_id,
6337 p_elig_apls_flag => p_rec.elig_apls_flag,
6338 p_effective_date => p_effective_date,
6339 p_object_version_number => p_rec.object_version_number);
6340 --
6341 chk_uses_all_asmts_for_rts_fla
6342 (p_pgm_id => p_rec.pgm_id,
6343 p_uses_all_asmts_for_rts_flag => p_rec.uses_all_asmts_for_rts_flag,
6344 p_effective_date => p_effective_date,
6345 p_object_version_number => p_rec.object_version_number);
6346 --
6347 chk_prtn_elig_ovrid_alwd_flag
6348 (p_pgm_id => p_rec.pgm_id,
6349 p_prtn_elig_ovrid_alwd_flag => p_rec.prtn_elig_ovrid_alwd_flag,
6350 p_effective_date => p_effective_date,
6351 p_object_version_number => p_rec.object_version_number);
6352 --
6353 /*
6354 chk_tmprl_fctr_apls_rts_flag
6355 (p_pgm_id => p_rec.pgm_id,
6356 p_tmprl_fctr_apls_rts_flag => p_rec.tmprl_fctr_apls_rts_flag,
6357 p_effective_date => p_effective_date,
6358 p_object_version_number => p_rec.object_version_number);
6359 */
6360 --
6361 chk_pgm_use_all_asnt_elig_flag
6362 (p_pgm_id => p_rec.pgm_id,
6363 p_pgm_use_all_asnts_elig_flag => p_rec.pgm_use_all_asnts_elig_flag,
6364 p_effective_date => p_effective_date,
6365 p_object_version_number => p_rec.object_version_number);
6366 --
6367 chk_coord_cvg_for_all_pls_flg
6368 (p_pgm_id => p_rec.pgm_id,
6369 p_coord_cvg_for_all_pls_flg => p_rec.coord_cvg_for_all_pls_flg,
6370 p_effective_date => p_effective_date,
6371 p_object_version_number => p_rec.object_version_number);
6372 --
6373 chk_drvbl_fctr_dpnt_elig_flag
6374 (p_pgm_id => p_rec.pgm_id,
6375 p_drvbl_fctr_dpnt_elig_flag => p_rec.drvbl_fctr_dpnt_elig_flag,
6376 p_effective_date => p_effective_date,
6377 p_object_version_number => p_rec.object_version_number);
6378 --
6379 /*
6380 chk_mt_one_dpnt_cvg_elig_flag
6381 (p_pgm_id => p_rec.pgm_id,
6382 p_mt_one_dpnt_cvg_elig_flag => p_rec.mt_one_dpnt_cvg_elig_flag,
6383 p_effective_date => p_effective_date,
6384 p_object_version_number => p_rec.object_version_number);
6385 */
6386 --
6387 chk_drvbl_fctr_prtn_elig_flag
6388 (p_pgm_id => p_rec.pgm_id,
6389 p_drvbl_fctr_prtn_elig_flag => p_rec.drvbl_fctr_prtn_elig_flag,
6390 p_effective_date => p_effective_date,
6391 p_object_version_number => p_rec.object_version_number);
6392 --
6393 chk_drvbl_fctr_apls_rts_flag
6394 (p_pgm_id => p_rec.pgm_id,
6395 p_drvbl_fctr_apls_rts_flag => p_rec.drvbl_fctr_apls_rts_flag,
6396 p_effective_date => p_effective_date,
6397 p_object_version_number => p_rec.object_version_number);
6398 --
6399 /*
6400 chk_tmprl_fctr_dpnt_elig_flag
6401 (p_pgm_id => p_rec.pgm_id,
6402 p_tmprl_fctr_dpnt_elig_flag => p_rec.tmprl_fctr_dpnt_elig_flag,
6403 p_effective_date => p_effective_date,
6404 p_object_version_number => p_rec.object_version_number);
6405 --
6406 chk_tmprl_fctr_prtn_elig_flag
6407 (p_pgm_id => p_rec.pgm_id,
6408 p_tmprl_fctr_prtn_elig_flag => p_rec.tmprl_fctr_prtn_elig_flag,
6409 p_effective_date => p_effective_date,
6410 p_object_version_number => p_rec.object_version_number);
6411 */
6412 --
6413 chk_dpnt_dsgn_no_ctfn_rqd_flag
6414 (p_pgm_id => p_rec.pgm_id,
6415 p_dpnt_dsgn_no_ctfn_rqd_flag => p_rec.dpnt_dsgn_no_ctfn_rqd_flag,
6416 p_effective_date => p_effective_date,
6417 p_object_version_number => p_rec.object_version_number);
6418 --
6419 chk_trk_inelig_per_flag
6420 (p_pgm_id => p_rec.pgm_id,
6421 p_trk_inelig_per_flag => p_rec.trk_inelig_per_flag,
6422 p_effective_date => p_effective_date,
6423 p_object_version_number => p_rec.object_version_number);
6424 --
6425 chk_vrfy_fmly_mmbr_cd(p_pgm_id => p_rec.pgm_id ,
6426 p_vrfy_fmly_mmbr_cd => p_rec.vrfy_fmly_mmbr_cd ,
6427 p_effective_date => p_effective_date ,
6428 P_object_version_number =>p_rec.object_version_number);
6429
6430
6431 chk_vrfy_fmly_mmbr_rl
6432 (p_pgm_id => p_rec.pgm_id,
6433 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl,
6434 p_business_group_id => p_rec.business_group_id,
6435 p_effective_date => p_effective_date,
6436 p_object_version_number => p_rec.object_version_number);
6437
6438
6439 -----
6440 chk_pgm_stat_cd
6441 (p_pgm_id => p_rec.pgm_id,
6442 p_pgm_stat_cd => p_rec.pgm_stat_cd,
6443 p_effective_date => p_effective_date,
6444 p_object_version_number => p_rec.object_version_number);
6445 --
6446 chk_dpnt_dsgn_lvl_cd
6447 (p_pgm_id => p_rec.pgm_id,
6448 p_dpnt_dsgn_lvl_cd => p_rec.dpnt_dsgn_lvl_cd,
6449 p_effective_date => p_effective_date,
6450 p_object_version_number => p_rec.object_version_number);
6451 --
6452 chk_pgm_typ_cd
6453 (p_pgm_id => p_rec.pgm_id,
6454 p_pgm_typ_cd => p_rec.pgm_typ_cd,
6455 p_effective_date => p_effective_date,
6456 p_object_version_number => p_rec.object_version_number);
6457 --
6458 chk_dpnt_dsgn_cd
6459 (p_pgm_id => p_rec.pgm_id,
6460 p_dpnt_dsgn_cd => p_rec.dpnt_dsgn_cd,
6461 p_dpnt_cvg_strt_dt_cd => p_rec.dpnt_cvg_strt_dt_cd,
6462 p_dpnt_cvg_end_dt_cd => p_rec.dpnt_cvg_end_dt_cd,
6463 p_effective_date => p_effective_date,
6464 p_object_version_number => p_rec.object_version_number);
6465 --
6466 chk_pgm_grp_cd
6467 (p_pgm_id => p_rec.pgm_id,
6468 p_pgm_grp_cd => p_rec.pgm_grp_cd,
6469 p_effective_date => p_effective_date,
6470 p_object_version_number => p_rec.object_version_number);
6471 --
6472 chk_acty_ref_perd_cd
6473 (p_pgm_id => p_rec.pgm_id,
6474 p_acty_ref_perd_cd => p_rec.acty_ref_perd_cd,
6475 p_effective_date => p_effective_date,
6476 p_object_version_number => p_rec.object_version_number);
6477 --
6478 chk_enrt_cvg_strt_dt_cd
6479 (p_pgm_id => p_rec.pgm_id,
6480 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
6481 p_effective_date => p_effective_date,
6482 p_object_version_number => p_rec.object_version_number);
6483 --
6484 chk_enrt_cvg_end_dt_cd
6485 (p_pgm_id => p_rec.pgm_id,
6486 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
6487 p_effective_date => p_effective_date,
6488 p_object_version_number => p_rec.object_version_number);
6489 --
6490 /* chk_prtn_strt_dt_cd
6491 (p_pgm_id => p_rec.pgm_id,
6492 p_prtn_strt_dt_cd => p_rec.prtn_strt_dt_cd,
6493 p_effective_date => p_effective_date,
6494 p_object_version_number => p_rec.object_version_number);
6495 --
6496 chk_prtn_end_dt_cd
6497 (p_pgm_id => p_rec.pgm_id,
6498 p_prtn_end_dt_cd => p_rec.prtn_end_dt_cd,
6499 p_effective_date => p_effective_date,
6500 p_object_version_number => p_rec.object_version_number);
6501 */
6502 --
6503 chk_pgm_uom
6504 (p_pgm_id => p_rec.pgm_id,
6505 p_pgm_uom => p_rec.pgm_uom,
6506 p_effective_date => p_effective_date,
6507 p_object_version_number => p_rec.object_version_number);
6508 --
6509 chk_all_rules
6510 (p_pgm_id => p_rec.pgm_id,
6511 p_business_group_id => p_rec.business_group_id,
6512 p_Dflt_step_rl => p_rec.Dflt_step_rl,
6513 p_Scores_calc_rl => p_rec.Scores_calc_rl,
6514 p_effective_date => p_effective_date,
6515 p_object_version_number => p_rec.object_version_number);
6516 --
6517 chk_lookup_codes
6518 (p_pgm_id => p_rec.pgm_id,
6519 p_Dflt_step_cd => p_rec.Dflt_step_cd,
6520 p_Update_salary_cd => p_rec.Update_salary_cd,
6521 p_Scores_calc_mthd_cd => p_rec.Scores_calc_mthd_cd,
6522 p_salary_calc_mthd_cd => p_rec.salary_calc_mthd_cd,
6523 p_effective_date => p_effective_date,
6524 p_object_version_number => p_rec.object_version_number);
6525 --
6526 chk_enrt_info_rt_freq_cd
6527 (p_pgm_id => p_rec.pgm_id,
6528 p_enrt_info_rt_freq_cd => p_rec.enrt_info_rt_freq_cd,
6529 p_effective_date => p_effective_date,
6530 p_object_version_number => p_rec.object_version_number);
6531 --
6532 chk_dpnt_cvg_strt_dt_cd
6533 (p_pgm_id => p_rec.pgm_id,
6534 p_dpnt_cvg_strt_dt_cd => p_rec.dpnt_cvg_strt_dt_cd,
6535 p_effective_date => p_effective_date,
6536 p_object_version_number => p_rec.object_version_number);
6537 --
6538 chk_dpnt_cvg_end_dt_cd
6539 (p_pgm_id => p_rec.pgm_id,
6540 p_dpnt_cvg_end_dt_cd => p_rec.dpnt_cvg_end_dt_cd,
6541 p_effective_date => p_effective_date,
6542 p_object_version_number => p_rec.object_version_number);
6543 --
6544 chk_name_unique
6545 (p_pgm_id => p_rec.pgm_id,
6546 p_name => p_rec.name,
6547 p_business_group_id => p_rec.business_group_id);
6548 --
6549 /* temporarily comment out nocopy to get ty's stuff working!
6550 chk_eligibility_defined
6551 (p_pgm_id => p_rec.pgm_id,
6552 p_elig_apls_flag => p_rec.elig_apls_flag,
6553 p_effective_date => p_effective_date,
6554 p_business_group_id => p_rec.business_group_id);
6555 */
6556 --
6557 chk_enrl_strt_dt_dpndcy
6558 (p_pgm_id => p_rec.pgm_id,
6559 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
6560 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
6561 p_effective_date => p_effective_date,
6562 p_object_version_number => p_rec.object_version_number);
6563 --
6564 chk_enrl_end_dt_dpndcy
6565 (p_pgm_id => p_rec.pgm_id,
6566 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
6567 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
6568 p_effective_date => p_effective_date,
6569 p_object_version_number => p_rec.object_version_number);
6570 --
6571 chk_dpnt_cvg_strt_dt_dpndcy
6572 (p_pgm_id => p_rec.pgm_id,
6573 p_dpnt_cvg_strt_dt_cd => p_rec.dpnt_cvg_strt_dt_cd,
6574 p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
6575 p_effective_date => p_effective_date,
6576 p_object_version_number => p_rec.object_version_number);
6577 --
6578 chk_dpnt_cvg_end_dt_dpndcy
6579 (p_pgm_id => p_rec.pgm_id,
6580 p_dpnt_cvg_end_dt_cd => p_rec.dpnt_cvg_end_dt_cd,
6581 p_dpnt_cvg_end_dt_rl => p_rec.dpnt_cvg_end_dt_rl,
6582 p_effective_date => p_effective_date,
6583 p_object_version_number => p_rec.object_version_number);
6584 --
6585 chk_dpnt_dsgn_cd_dpndcy
6586 (p_pgm_id => p_rec.pgm_id,
6587 p_dpnt_dsgn_cd => p_rec.dpnt_dsgn_cd,
6588 p_dpnt_cvg_strt_dt_cd => p_rec.dpnt_cvg_strt_dt_cd,
6589 p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
6590 p_dpnt_cvg_end_dt_cd => p_rec.dpnt_cvg_end_dt_cd,
6591 p_dpnt_cvg_end_dt_rl => p_rec.dpnt_cvg_end_dt_rl,
6592 p_dpnt_adrs_rqd_flag => p_rec.dpnt_adrs_rqd_flag,
6593 p_dpnt_dob_rqd_flag => p_rec.dpnt_dob_rqd_flag,
6594 p_dpnt_legv_id_rqd_flag => p_rec.dpnt_legv_id_rqd_flag,
6595 p_dpnt_dsgn_no_ctfn_rqd_flag => p_rec.dpnt_dsgn_no_ctfn_rqd_flag,
6596 p_drvbl_fctr_dpnt_elig_flag => p_rec.drvbl_fctr_dpnt_elig_flag,
6597 p_effective_date => p_effective_date,
6598 p_object_version_number => p_rec.object_version_number);
6599 --
6600 /*
6601 chk_pgm_prvds_no_auto_dpndcy
6602 (p_pgm_id => p_rec.pgm_id,
6603 p_pgm_prvds_no_auto_enrt_flag => p_rec.pgm_prvds_no_auto_enrt_flag,
6604 p_business_group_id => p_rec.business_group_id,
6605 p_effective_date => p_effective_date,
6606 p_object_version_number => p_rec.object_version_number);
6607 --
6608 chk_pgm_prvds_no_dflt_dpndcy
6609 (p_pgm_id => p_rec.pgm_id,
6610 p_pgm_prvds_no_dflt_enrt_flag => p_rec.pgm_prvds_no_dflt_enrt_flag,
6611 p_business_group_id => p_rec.business_group_id,
6612 p_effective_date => p_effective_date,
6613 p_object_version_number => p_rec.object_version_number);
6614 */
6615 --
6616 chk_pgm_prvds_no_dflt_dpndcy2
6617 (p_pgm_id => p_rec.pgm_id,
6618 p_pgm_prvds_no_dflt_enrt_flag => p_rec.pgm_prvds_no_dflt_enrt_flag,
6619 p_business_group_id => p_rec.business_group_id,
6620 p_effective_date => p_effective_date,
6621 p_object_version_number => p_rec.object_version_number);
6622 --
6623 chk_dpnt_dsgn_lvl_cd_dpndcy
6624 (p_pgm_id => p_rec.pgm_id,
6625 p_dpnt_dsgn_lvl_cd => p_rec.dpnt_dsgn_lvl_cd,
6626 p_dpnt_dsgn_cd => p_rec.dpnt_dsgn_cd,
6627 p_dpnt_cvg_strt_dt_cd => p_rec.dpnt_cvg_strt_dt_cd,
6628 p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
6629 p_dpnt_cvg_end_dt_cd => p_rec.dpnt_cvg_end_dt_cd,
6630 p_dpnt_cvg_end_dt_rl => p_rec.dpnt_cvg_end_dt_rl,
6631 p_dpnt_adrs_rqd_flag => p_rec.dpnt_adrs_rqd_flag,
6632 p_dpnt_dob_rqd_flag => p_rec.dpnt_dob_rqd_flag,
6633 p_dpnt_legv_id_rqd_flag => p_rec.dpnt_legv_id_rqd_flag,
6634 p_dpnt_dsgn_no_ctfn_rqd_flag => p_rec.dpnt_dsgn_no_ctfn_rqd_flag,
6635 p_drvbl_fctr_dpnt_elig_flag => p_rec.drvbl_fctr_dpnt_elig_flag,
6636 p_effective_date => p_effective_date,
6637 p_object_version_number => p_rec.object_version_number);
6638 --
6639 chk_dpnt_dsgn_cd_detail
6640 (p_pgm_id => p_rec.pgm_id,
6641 p_dpnt_dsgn_cd => p_rec.dpnt_dsgn_cd,
6642 p_business_group_id => p_rec.business_group_id,
6643 p_effective_date => p_effective_date,
6644 p_object_version_number => p_rec.object_version_number);
6645 --
6646 chk_dpnt_dsgn_lvl_cd_dpndcy2
6647 (p_pgm_id => p_rec.pgm_id,
6648 p_dpnt_dsgn_lvl_cd => p_rec.dpnt_dsgn_lvl_cd,
6649 p_business_group_id => p_rec.business_group_id,
6650 p_effective_date => p_effective_date,
6651 p_object_version_number => p_rec.object_version_number);
6652 --
6653 /*
6654 chk_dpnt_dsgn_lvl_cd_dpndcy3
6655 (p_pgm_id => p_rec.pgm_id,
6656 p_dpnt_dsgn_lvl_cd => p_rec.dpnt_dsgn_lvl_cd,
6657 p_business_group_id => p_rec.business_group_id,
6658 p_effective_date => p_effective_date,
6659 p_object_version_number => p_rec.object_version_number);
6660 */
6661 --
6662 chk_dpnt_dsgn_lvl_cd_dpndcy4
6663 (p_pgm_id => p_rec.pgm_id,
6664 p_dpnt_dsgn_lvl_cd => p_rec.dpnt_dsgn_lvl_cd,
6665 p_business_group_id => p_rec.business_group_id,
6666 p_effective_date => p_effective_date,
6667 p_object_version_number => p_rec.object_version_number);
6668 --
6669 chk_alws_unrstrctd_enrt_flag
6670 (p_pgm_id => p_rec.pgm_id,
6671 p_alws_unrstrctd_enrt_flag => p_rec.alws_unrstrctd_enrt_flag,
6672 p_effective_date => p_effective_date,
6673 p_object_version_number => p_rec.object_version_number);
6674 --
6675 chk_enrt_cd
6676 (p_pgm_id => p_rec.pgm_id,
6677 p_enrt_cd => p_rec.enrt_cd,
6678 p_effective_date => p_effective_date,
6679 p_object_version_number => p_rec.object_version_number);
6680 --
6681 chk_enrt_mthd_cd
6682 (p_pgm_id => p_rec.pgm_id,
6683 p_enrt_mthd_cd => p_rec.enrt_mthd_cd,
6684 p_effective_date => p_effective_date,
6685 p_object_version_number => p_rec.object_version_number);
6686 --
6687 chk_poe_lvl_cd
6688 (p_pgm_id => p_rec.pgm_id,
6689 p_poe_lvl_cd => p_rec.poe_lvl_cd,
6690 p_effective_date => p_effective_date,
6691 p_object_version_number => p_rec.object_version_number);
6692 --
6693 chk_enrt_rl
6694 (p_pgm_id => p_rec.pgm_id,
6695 p_enrt_rl => p_rec.enrt_rl,
6696 p_effective_date => p_effective_date,
6697 p_object_version_number => p_rec.object_version_number,
6698 p_business_group_id => p_rec.business_group_id);
6699 --
6700 chk_auto_enrt_mthd_rl
6701 (p_pgm_id => p_rec.pgm_id,
6702 p_auto_enrt_mthd_rl => p_rec.auto_enrt_mthd_rl,
6703 p_effective_date => p_effective_date,
6704 p_object_version_number => p_rec.object_version_number,
6705 p_business_group_id => p_rec.business_group_id);
6706 --
6707 chk_enrt_cd_gsp
6708 (p_pgm_id => p_rec.pgm_id,
6709 p_pgm_typ_cd => p_rec.pgm_typ_cd,
6710 p_enrt_cd => p_rec.enrt_cd,
6711 p_effective_date => p_effective_date,
6712 p_object_version_number => p_rec.object_version_number);
6713
6714 --
6715 if p_rec.business_group_id is not null and p_rec.legislation_code is null then
6716 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
6717 end if;
6718 --
6719 hr_utility.set_location(' Leaving:'||l_proc, 10);
6720 End insert_validate;
6721 --
6722 -- ----------------------------------------------------------------------------
6723 -- |---------------------------< update_validate >----------------------------|
6724 -- ----------------------------------------------------------------------------
6725 Procedure update_validate
6726 (p_rec in ben_pgm_shd.g_rec_type,
6727 p_effective_date in date,
6728 p_datetrack_mode in varchar2,
6729 p_validation_start_date in date,
6730 p_validation_end_date in date) is
6731 --
6732 l_proc varchar2(72) := g_package||'update_validate';
6733 --
6734 Begin
6735 hr_utility.set_location('Entering:'||l_proc, 5);
6736 --
6737 -- Call all supporting business operations
6738 --
6739 --
6740 if p_rec.business_group_id is not null and p_rec.legislation_code is null then
6741 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
6742 end if;
6743 --
6744 chk_pgm_id
6745 (p_pgm_id => p_rec.pgm_id,
6746 p_effective_date => p_effective_date,
6747 p_object_version_number => p_rec.object_version_number);
6748 --
6749 chk_enrt_cvg_end_dt_rl
6750 (p_pgm_id => p_rec.pgm_id,
6751 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
6752 p_effective_date => p_effective_date,
6753 p_object_version_number => p_rec.object_version_number,
6754 p_business_group_id => p_rec.business_group_id);
6755 --
6756 chk_enrt_cvg_strt_dt_rl
6757 (p_pgm_id => p_rec.pgm_id,
6758 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
6759 p_effective_date => p_effective_date,
6760 p_object_version_number => p_rec.object_version_number,
6761 p_business_group_id => p_rec.business_group_id);
6762 --
6763 chk_dpnt_cvg_strt_dt_rl
6764 (p_pgm_id => p_rec.pgm_id,
6765 p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
6766 p_effective_date => p_effective_date,
6767 p_object_version_number => p_rec.object_version_number,
6768 p_business_group_id => p_rec.business_group_id);
6769 --
6770 chk_rt_end_dt_rl
6771 (p_pgm_id => p_rec.pgm_id,
6772 p_rt_end_dt_rl => p_rec.rt_end_dt_rl,
6773 p_effective_date => p_effective_date,
6774 p_object_version_number => p_rec.object_version_number,
6775 p_business_group_id => p_rec.business_group_id);
6776 --
6777 chk_rt_strt_dt_rl
6778 (p_pgm_id => p_rec.pgm_id,
6779 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
6780 p_effective_date => p_effective_date,
6781 p_object_version_number => p_rec.object_version_number,
6782 p_business_group_id => p_rec.business_group_id);
6783 --
6784
6785 chk_cd_rl_combination
6786 (p_vrfy_fmly_mmbr_cd => p_rec.vrfy_fmly_mmbr_cd,
6787 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl);
6788 --
6789 chk_dpnt_cvg_end_dt_rl
6790 (p_pgm_id => p_rec.pgm_id,
6791 p_dpnt_cvg_end_dt_rl => p_rec.dpnt_cvg_end_dt_rl,
6792 p_effective_date => p_effective_date,
6793 p_object_version_number => p_rec.object_version_number,
6794 p_business_group_id => p_rec.business_group_id);
6795 --
6796 /*
6797 chk_prtn_end_dt_rl
6798 (p_pgm_id => p_rec.pgm_id,
6799 p_prtn_end_dt_rl => p_rec.prtn_end_dt_rl,
6800 p_effective_date => p_effective_date,
6801 p_object_version_number => p_rec.object_version_number,
6802 p_business_group_id => p_rec.business_group_id);
6803 --
6804 chk_prtn_strt_dt_rl
6805 (p_pgm_id => p_rec.pgm_id,
6806 p_prtn_strt_dt_rl => p_rec.prtn_strt_dt_rl,
6807 p_effective_date => p_effective_date,
6808 p_object_version_number => p_rec.object_version_number,
6809 p_business_group_id => p_rec.business_group_id);
6810 */
6811 --
6812 chk_dpnt_adrs_rqd_flag
6813 (p_pgm_id => p_rec.pgm_id,
6814 p_dpnt_adrs_rqd_flag => p_rec.dpnt_adrs_rqd_flag,
6815 p_effective_date => p_effective_date,
6816 p_object_version_number => p_rec.object_version_number);
6817 --
6818 /*
6819 chk_elig_apls_to_all_pls_flag
6820 (p_pgm_id => p_rec.pgm_id,
6821 p_elig_apls_to_all_pls_flag => p_rec.elig_apls_to_all_pls_flag,
6822 p_effective_date => p_effective_date,
6823 p_object_version_number => p_rec.object_version_number);
6824 */
6825 --
6826 chk_dpnt_dob_rqd_flag
6827 (p_pgm_id => p_rec.pgm_id,
6828 p_dpnt_dob_rqd_flag => p_rec.dpnt_dob_rqd_flag,
6829 p_effective_date => p_effective_date,
6830 p_object_version_number => p_rec.object_version_number);
6831 --
6832 chk_pgm_prvd_no_auto_enrt_flag
6833 (p_pgm_id => p_rec.pgm_id,
6834 p_pgm_prvds_no_auto_enrt_flag => p_rec.pgm_prvds_no_auto_enrt_flag,
6835 p_effective_date => p_effective_date,
6836 p_object_version_number => p_rec.object_version_number);
6837 --
6838 chk_pgm_prvd_no_dflt_enrt_flag
6839 (p_pgm_id => p_rec.pgm_id,
6840 p_pgm_prvds_no_dflt_enrt_flag => p_rec.pgm_prvds_no_dflt_enrt_flag,
6841 p_effective_date => p_effective_date,
6842 p_object_version_number => p_rec.object_version_number);
6843 --
6844 chk_vrfy_fmly_mmbr_cd(p_pgm_id => p_rec.pgm_id ,
6845 p_vrfy_fmly_mmbr_cd => p_rec.vrfy_fmly_mmbr_cd ,
6846 p_effective_date => p_effective_date ,
6847 P_object_version_number =>p_rec.object_version_number);
6848
6849
6850 chk_vrfy_fmly_mmbr_rl
6851 (p_pgm_id => p_rec.pgm_id,
6852 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl,
6853 p_business_group_id => p_rec.business_group_id,
6854 p_effective_date => p_effective_date,
6855 p_object_version_number => p_rec.object_version_number);
6856 --
6857 chk_dpnt_legv_id_rqd_flag
6858 (p_pgm_id => p_rec.pgm_id,
6859 p_dpnt_legv_id_rqd_flag => p_rec.dpnt_legv_id_rqd_flag,
6860 p_effective_date => p_effective_date,
6861 p_object_version_number => p_rec.object_version_number);
6862 --
6863 chk_elig_apls_flag
6864 (p_pgm_id => p_rec.pgm_id,
6865 p_elig_apls_flag => p_rec.elig_apls_flag,
6866 p_effective_date => p_effective_date,
6867 p_object_version_number => p_rec.object_version_number);
6868 --
6869 chk_uses_all_asmts_for_rts_fla
6870 (p_pgm_id => p_rec.pgm_id,
6871 p_uses_all_asmts_for_rts_flag => p_rec.uses_all_asmts_for_rts_flag,
6872 p_effective_date => p_effective_date,
6873 p_object_version_number => p_rec.object_version_number);
6874 --
6875 chk_prtn_elig_ovrid_alwd_flag
6876 (p_pgm_id => p_rec.pgm_id,
6877 p_prtn_elig_ovrid_alwd_flag => p_rec.prtn_elig_ovrid_alwd_flag,
6878 p_effective_date => p_effective_date,
6879 p_object_version_number => p_rec.object_version_number);
6880 --
6881 /*
6882 chk_tmprl_fctr_apls_rts_flag
6883 (p_pgm_id => p_rec.pgm_id,
6884 p_tmprl_fctr_apls_rts_flag => p_rec.tmprl_fctr_apls_rts_flag,
6885 p_effective_date => p_effective_date,
6886 p_object_version_number => p_rec.object_version_number);
6887 */
6888 --
6889 chk_pgm_use_all_asnt_elig_flag
6890 (p_pgm_id => p_rec.pgm_id,
6891 p_pgm_use_all_asnts_elig_flag => p_rec.pgm_use_all_asnts_elig_flag,
6892 p_effective_date => p_effective_date,
6893 p_object_version_number => p_rec.object_version_number);
6894 --
6895 chk_coord_cvg_for_all_pls_flg
6896 (p_pgm_id => p_rec.pgm_id,
6897 p_coord_cvg_for_all_pls_flg => p_rec.coord_cvg_for_all_pls_flg,
6898 p_effective_date => p_effective_date,
6899 p_object_version_number => p_rec.object_version_number);
6900 --
6901 chk_drvbl_fctr_dpnt_elig_flag
6902 (p_pgm_id => p_rec.pgm_id,
6903 p_drvbl_fctr_dpnt_elig_flag => p_rec.drvbl_fctr_dpnt_elig_flag,
6904 p_effective_date => p_effective_date,
6905 p_object_version_number => p_rec.object_version_number);
6906 --
6907 /*
6908 chk_mt_one_dpnt_cvg_elig_flag
6909 (p_pgm_id => p_rec.pgm_id,
6910 p_mt_one_dpnt_cvg_elig_flag => p_rec.mt_one_dpnt_cvg_elig_flag,
6911 p_effective_date => p_effective_date,
6912 p_object_version_number => p_rec.object_version_number);
6913 */
6914 --
6915 chk_drvbl_fctr_prtn_elig_flag
6916 (p_pgm_id => p_rec.pgm_id,
6917 p_drvbl_fctr_prtn_elig_flag => p_rec.drvbl_fctr_prtn_elig_flag,
6918 p_effective_date => p_effective_date,
6919 p_object_version_number => p_rec.object_version_number);
6920 --
6921 chk_drvbl_fctr_apls_rts_flag
6922 (p_pgm_id => p_rec.pgm_id,
6923 p_drvbl_fctr_apls_rts_flag => p_rec.drvbl_fctr_apls_rts_flag,
6924 p_effective_date => p_effective_date,
6925 p_object_version_number => p_rec.object_version_number);
6926 --
6927 /*
6928 chk_tmprl_fctr_dpnt_elig_flag
6929 (p_pgm_id => p_rec.pgm_id,
6930 p_tmprl_fctr_dpnt_elig_flag => p_rec.tmprl_fctr_dpnt_elig_flag,
6931 p_effective_date => p_effective_date,
6932 p_object_version_number => p_rec.object_version_number);
6933 --
6934 chk_tmprl_fctr_prtn_elig_flag
6935 (p_pgm_id => p_rec.pgm_id,
6936 p_tmprl_fctr_prtn_elig_flag => p_rec.tmprl_fctr_prtn_elig_flag,
6937 p_effective_date => p_effective_date,
6938 p_object_version_number => p_rec.object_version_number);
6939 */
6940 --
6941 chk_dpnt_dsgn_no_ctfn_rqd_flag
6942 (p_pgm_id => p_rec.pgm_id,
6943 p_dpnt_dsgn_no_ctfn_rqd_flag => p_rec.dpnt_dsgn_no_ctfn_rqd_flag,
6944 p_effective_date => p_effective_date,
6945 p_object_version_number => p_rec.object_version_number);
6946 --
6947 chk_trk_inelig_per_flag
6948 (p_pgm_id => p_rec.pgm_id,
6949 p_trk_inelig_per_flag => p_rec.trk_inelig_per_flag,
6950 p_effective_date => p_effective_date,
6951 p_object_version_number => p_rec.object_version_number);
6952 --
6953 chk_pgm_stat_cd
6954 (p_pgm_id => p_rec.pgm_id,
6955 p_pgm_stat_cd => p_rec.pgm_stat_cd,
6956 p_effective_date => p_effective_date,
6957 p_object_version_number => p_rec.object_version_number);
6958 --
6959 chk_dpnt_dsgn_lvl_cd
6960 (p_pgm_id => p_rec.pgm_id,
6961 p_dpnt_dsgn_lvl_cd => p_rec.dpnt_dsgn_lvl_cd,
6962 p_effective_date => p_effective_date,
6963 p_object_version_number => p_rec.object_version_number);
6964 --
6965 chk_pgm_typ_cd
6966 (p_pgm_id => p_rec.pgm_id,
6967 p_pgm_typ_cd => p_rec.pgm_typ_cd,
6968 p_effective_date => p_effective_date,
6969 p_object_version_number => p_rec.object_version_number);
6970 --
6971 chk_dpnt_dsgn_cd
6972 (p_pgm_id => p_rec.pgm_id,
6973 p_dpnt_dsgn_cd => p_rec.dpnt_dsgn_cd,
6974 p_dpnt_cvg_strt_dt_cd => p_rec.dpnt_cvg_strt_dt_cd,
6975 p_dpnt_cvg_end_dt_cd => p_rec.dpnt_cvg_end_dt_cd,
6976 p_effective_date => p_effective_date,
6977 p_object_version_number => p_rec.object_version_number);
6978 --
6979 chk_pgm_grp_cd
6980 (p_pgm_id => p_rec.pgm_id,
6981 p_pgm_grp_cd => p_rec.pgm_grp_cd,
6982 p_effective_date => p_effective_date,
6983 p_object_version_number => p_rec.object_version_number);
6984 --
6985 chk_acty_ref_perd_cd
6986 (p_pgm_id => p_rec.pgm_id,
6987 p_acty_ref_perd_cd => p_rec.acty_ref_perd_cd,
6988 p_effective_date => p_effective_date,
6989 p_object_version_number => p_rec.object_version_number);
6990 --
6991 chk_enrt_cvg_strt_dt_cd
6992 (p_pgm_id => p_rec.pgm_id,
6993 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
6994 p_effective_date => p_effective_date,
6995 p_object_version_number => p_rec.object_version_number);
6996 --
6997 chk_enrt_cvg_end_dt_cd
6998 (p_pgm_id => p_rec.pgm_id,
6999 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
7000 p_effective_date => p_effective_date,
7001 p_object_version_number => p_rec.object_version_number);
7002 --
7003 /* chk_prtn_strt_dt_cd
7004 (p_pgm_id => p_rec.pgm_id,
7005 p_prtn_strt_dt_cd => p_rec.prtn_strt_dt_cd,
7006 p_effective_date => p_effective_date,
7007 p_object_version_number => p_rec.object_version_number);
7008 --
7009 chk_prtn_end_dt_cd
7010 (p_pgm_id => p_rec.pgm_id,
7011 p_prtn_end_dt_cd => p_rec.prtn_end_dt_cd,
7012 p_effective_date => p_effective_date,
7013 p_object_version_number => p_rec.object_version_number);
7014 */
7015 --
7016 chk_pgm_uom
7017 (p_pgm_id => p_rec.pgm_id,
7018 p_pgm_uom => p_rec.pgm_uom,
7019 p_effective_date => p_effective_date,
7020 p_object_version_number => p_rec.object_version_number);
7021 --
7022 chk_enrt_info_rt_freq_cd
7023 (p_pgm_id => p_rec.pgm_id,
7024 p_enrt_info_rt_freq_cd => p_rec.enrt_info_rt_freq_cd,
7025 p_effective_date => p_effective_date,
7026 p_object_version_number => p_rec.object_version_number);
7027 --
7028 chk_dpnt_cvg_strt_dt_cd
7029 (p_pgm_id => p_rec.pgm_id,
7030 p_dpnt_cvg_strt_dt_cd => p_rec.dpnt_cvg_strt_dt_cd,
7031 p_effective_date => p_effective_date,
7032 p_object_version_number => p_rec.object_version_number);
7033 --
7034 chk_dpnt_cvg_end_dt_cd
7035 (p_pgm_id => p_rec.pgm_id,
7036 p_dpnt_cvg_end_dt_cd => p_rec.dpnt_cvg_end_dt_cd,
7037 p_effective_date => p_effective_date,
7038 p_object_version_number => p_rec.object_version_number);
7039 --
7040 chk_name_unique
7041 (p_pgm_id => p_rec.pgm_id,
7042 p_name => p_rec.name,
7043 p_business_group_id => p_rec.business_group_id);
7044 --
7045 /* temporarily comment out nocopy to get ty's stuff working!
7046 chk_eligibility_defined
7047 (p_pgm_id => p_rec.pgm_id,
7048 p_elig_apls_flag => p_rec.elig_apls_flag,
7049 p_effective_date => p_effective_date,
7050 p_business_group_id => p_rec.business_group_id);
7051 */
7052 --
7053 chk_enrl_strt_dt_dpndcy
7054 (p_pgm_id => p_rec.pgm_id,
7055 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
7056 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
7057 p_effective_date => p_effective_date,
7058 p_object_version_number => p_rec.object_version_number);
7059 --
7060 chk_enrl_end_dt_dpndcy
7061 (p_pgm_id => p_rec.pgm_id,
7062 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
7063 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
7064 p_effective_date => p_effective_date,
7065 p_object_version_number => p_rec.object_version_number);
7066 --
7067 chk_dpnt_cvg_strt_dt_dpndcy
7068 (p_pgm_id => p_rec.pgm_id,
7069 p_dpnt_cvg_strt_dt_cd => p_rec.dpnt_cvg_strt_dt_cd,
7070 p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
7071 p_effective_date => p_effective_date,
7072 p_object_version_number => p_rec.object_version_number);
7073 --
7074 chk_dpnt_cvg_end_dt_dpndcy
7075 (p_pgm_id => p_rec.pgm_id,
7076 p_dpnt_cvg_end_dt_cd => p_rec.dpnt_cvg_end_dt_cd,
7077 p_dpnt_cvg_end_dt_rl => p_rec.dpnt_cvg_end_dt_rl,
7078 p_effective_date => p_effective_date,
7079 p_object_version_number => p_rec.object_version_number);
7080 --
7081 chk_dpnt_dsgn_cd_dpndcy
7082 (p_pgm_id => p_rec.pgm_id,
7083 p_dpnt_dsgn_cd => p_rec.dpnt_dsgn_cd,
7084 p_dpnt_cvg_strt_dt_cd => p_rec.dpnt_cvg_strt_dt_cd,
7085 p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
7086 p_dpnt_cvg_end_dt_cd => p_rec.dpnt_cvg_end_dt_cd,
7087 p_dpnt_cvg_end_dt_rl => p_rec.dpnt_cvg_end_dt_rl,
7088 p_dpnt_adrs_rqd_flag => p_rec.dpnt_adrs_rqd_flag,
7089 p_dpnt_dob_rqd_flag => p_rec.dpnt_dob_rqd_flag,
7090 p_dpnt_legv_id_rqd_flag => p_rec.dpnt_legv_id_rqd_flag,
7091 p_dpnt_dsgn_no_ctfn_rqd_flag => p_rec.dpnt_dsgn_no_ctfn_rqd_flag,
7092 p_drvbl_fctr_dpnt_elig_flag => p_rec.drvbl_fctr_dpnt_elig_flag,
7093 p_effective_date => p_effective_date,
7094 p_object_version_number => p_rec.object_version_number);
7095 --
7096 /*
7097 chk_pgm_prvds_no_auto_dpndcy
7098 (p_pgm_id => p_rec.pgm_id,
7099 p_pgm_prvds_no_auto_enrt_flag => p_rec.pgm_prvds_no_auto_enrt_flag,
7100 p_business_group_id => p_rec.business_group_id,
7101 p_effective_date => p_effective_date,
7102 p_object_version_number => p_rec.object_version_number);
7103 --
7104 chk_pgm_prvds_no_dflt_dpndcy
7105 (p_pgm_id => p_rec.pgm_id,
7106 p_pgm_prvds_no_dflt_enrt_flag => p_rec.pgm_prvds_no_dflt_enrt_flag,
7107 p_business_group_id => p_rec.business_group_id,
7108 p_effective_date => p_effective_date,
7109 p_object_version_number => p_rec.object_version_number);
7110 */
7111 --
7112 chk_pgm_prvds_no_dflt_dpndcy2
7113 (p_pgm_id => p_rec.pgm_id,
7114 p_pgm_prvds_no_dflt_enrt_flag => p_rec.pgm_prvds_no_dflt_enrt_flag,
7115 p_business_group_id => p_rec.business_group_id,
7116 p_effective_date => p_effective_date,
7117 p_object_version_number => p_rec.object_version_number);
7118 --
7119 chk_dpnt_dsgn_lvl_cd_dpndcy
7120 (p_pgm_id => p_rec.pgm_id,
7121 p_dpnt_dsgn_lvl_cd => p_rec.dpnt_dsgn_lvl_cd,
7122 p_dpnt_dsgn_cd => p_rec.dpnt_dsgn_cd,
7123 p_dpnt_cvg_strt_dt_cd => p_rec.dpnt_cvg_strt_dt_cd,
7124 p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
7125 p_dpnt_cvg_end_dt_cd => p_rec.dpnt_cvg_end_dt_cd,
7126 p_dpnt_cvg_end_dt_rl => p_rec.dpnt_cvg_end_dt_rl,
7127 p_dpnt_adrs_rqd_flag => p_rec.dpnt_adrs_rqd_flag,
7128 p_dpnt_dob_rqd_flag => p_rec.dpnt_dob_rqd_flag,
7129 p_dpnt_legv_id_rqd_flag => p_rec.dpnt_legv_id_rqd_flag,
7130 p_dpnt_dsgn_no_ctfn_rqd_flag => p_rec.dpnt_dsgn_no_ctfn_rqd_flag,
7131 p_drvbl_fctr_dpnt_elig_flag => p_rec.drvbl_fctr_dpnt_elig_flag,
7132 p_effective_date => p_effective_date,
7133 p_object_version_number => p_rec.object_version_number);
7134 --
7135 chk_dpnt_dsgn_cd_detail
7136 (p_pgm_id => p_rec.pgm_id,
7137 p_dpnt_dsgn_cd => p_rec.dpnt_dsgn_cd,
7138 p_business_group_id => p_rec.business_group_id,
7139 p_effective_date => p_effective_date,
7140 p_object_version_number => p_rec.object_version_number);
7141 --
7142 chk_dpnt_dsgn_lvl_cd_dpndcy2
7143 (p_pgm_id => p_rec.pgm_id,
7144 p_dpnt_dsgn_lvl_cd => p_rec.dpnt_dsgn_lvl_cd,
7145 p_business_group_id => p_rec.business_group_id,
7146 p_effective_date => p_effective_date,
7147 p_object_version_number => p_rec.object_version_number);
7148 --
7149 /*
7150 chk_dpnt_dsgn_lvl_cd_dpndcy3
7151 (p_pgm_id => p_rec.pgm_id,
7152 p_dpnt_dsgn_lvl_cd => p_rec.dpnt_dsgn_lvl_cd,
7153 p_business_group_id => p_rec.business_group_id,
7154 p_effective_date => p_effective_date,
7155 p_object_version_number => p_rec.object_version_number);
7156 */
7157 --
7158 chk_dpnt_dsgn_lvl_cd_dpndcy4
7159 (p_pgm_id => p_rec.pgm_id,
7160 p_dpnt_dsgn_lvl_cd => p_rec.dpnt_dsgn_lvl_cd,
7161 p_business_group_id => p_rec.business_group_id,
7162 p_effective_date => p_effective_date,
7163 p_object_version_number => p_rec.object_version_number);
7164 --
7165 chk_lookup_codes
7166 (p_pgm_id => p_rec.pgm_id,
7167 p_Dflt_step_cd => p_rec.Dflt_step_cd,
7168 p_Update_salary_cd => p_rec.Update_salary_cd,
7169 p_Scores_calc_mthd_cd => p_rec.Scores_calc_mthd_cd,
7170 p_salary_calc_mthd_cd => p_rec.salary_calc_mthd_cd,
7171 p_effective_date => p_effective_date,
7172 p_object_version_number => p_rec.object_version_number);
7173 --
7174 --
7175 -- Call the datetrack update integrity operation
7176 --
7177 dt_update_validate
7178 (p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
7179 p_dpnt_cvg_end_dt_rl => p_rec.dpnt_cvg_end_dt_rl,
7180 p_datetrack_mode => p_datetrack_mode,
7181 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
7182 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
7183 p_validation_start_date => p_validation_start_date,
7184 p_validation_end_date => p_validation_end_date);
7185 --
7186 chk_alws_unrstrctd_enrt_flag
7187 (p_pgm_id => p_rec.pgm_id,
7188 p_alws_unrstrctd_enrt_flag => p_rec.alws_unrstrctd_enrt_flag,
7189 p_effective_date => p_effective_date,
7190 p_object_version_number => p_rec.object_version_number);
7191 --
7192 chk_enrt_cd
7193 (p_pgm_id => p_rec.pgm_id,
7194 p_enrt_cd => p_rec.enrt_cd,
7195 p_effective_date => p_effective_date,
7196 p_object_version_number => p_rec.object_version_number);
7197 --
7198 chk_enrt_mthd_cd
7199 (p_pgm_id => p_rec.pgm_id,
7200 p_enrt_mthd_cd => p_rec.enrt_mthd_cd,
7201 p_effective_date => p_effective_date,
7202 p_object_version_number => p_rec.object_version_number);
7203 --
7204 chk_poe_lvl_cd
7205 (p_pgm_id => p_rec.pgm_id,
7206 p_poe_lvl_cd => p_rec.poe_lvl_cd,
7207 p_effective_date => p_effective_date,
7208 p_object_version_number => p_rec.object_version_number);
7209 --
7210 chk_enrt_rl
7211 (p_pgm_id => p_rec.pgm_id,
7212 p_enrt_rl => p_rec.enrt_rl,
7213 p_effective_date => p_effective_date,
7214 p_object_version_number => p_rec.object_version_number,
7215 p_business_group_id => p_rec.business_group_id);
7216 --
7217 chk_auto_enrt_mthd_rl
7218 (p_pgm_id => p_rec.pgm_id,
7219 p_auto_enrt_mthd_rl => p_rec.auto_enrt_mthd_rl,
7220 p_effective_date => p_effective_date,
7221 p_object_version_number => p_rec.object_version_number,
7222 p_business_group_id => p_rec.business_group_id);
7223 --
7224 chk_enrt_cd_gsp
7225 (p_pgm_id => p_rec.pgm_id,
7226 p_pgm_typ_cd => p_rec.pgm_typ_cd,
7227 p_enrt_cd => p_rec.enrt_cd,
7228 p_effective_date => p_effective_date,
7229 p_object_version_number => p_rec.object_version_number);
7230 --
7231 -- Call user defined business rules
7232 --
7233 hr_utility.set_location(' Leaving:'||l_proc, 10);
7234 End update_validate;
7235 --
7236 -- ----------------------------------------------------------------------------
7237 -- |---------------------------< delete_validate >----------------------------|
7238 -- ----------------------------------------------------------------------------
7239 Procedure delete_validate
7240 (p_rec in ben_pgm_shd.g_rec_type,
7241 p_effective_date in date,
7242 p_datetrack_mode in varchar2,
7243 p_validation_start_date in date,
7244 p_validation_end_date in date) is
7245 --
7246 l_proc varchar2(72) := g_package||'delete_validate';
7247 --
7248 Begin
7249 hr_utility.set_location('Entering:'||l_proc, 5);
7250 --
7251 -- Call all supporting business operations
7252 --
7253 dt_delete_validate
7254 (p_datetrack_mode => p_datetrack_mode,
7255 p_validation_start_date => p_validation_start_date,
7256 p_validation_end_date => p_validation_end_date,
7257 p_pgm_id => p_rec.pgm_id);
7258 --
7259 hr_utility.set_location(' Leaving:'||l_proc, 10);
7260 End delete_validate;
7261 --
7262 --
7263 -- ---------------------------------------------------------------------------
7264 -- |---------------------< return_legislation_code >-------------------------|
7265 -- ---------------------------------------------------------------------------
7266 --
7267 function return_legislation_code
7268 (p_pgm_id in number) return varchar2 is
7269 --
7270 -- Declare cursor
7271 --
7272 cursor csr_leg_code is
7273 select a.legislation_code
7274 from per_business_groups a,
7275 ben_pgm_f b
7276 where b.pgm_id = p_pgm_id
7277 and a.business_group_id = b.business_group_id;
7278 --
7279 -- Declare local variables
7280 --
7281 l_legislation_code varchar2(150);
7282 l_proc varchar2(72) := g_package||'return_legislation_code';
7283
7284 --
7285 begin
7286 --
7287 hr_utility.set_location('Entering:'|| l_proc, 10);
7288 --
7289 -- Ensure that all the mandatory parameter are not null
7290 --
7291 hr_api.mandatory_arg_error(p_api_name => l_proc,
7292 p_argument => 'pgm_id',
7293 p_argument_value => p_pgm_id);
7294 --
7295 open csr_leg_code;
7296 --
7297 fetch csr_leg_code into l_legislation_code;
7298
7299 --
7300 if csr_leg_code%notfound then
7301 --
7302 close csr_leg_code;
7303 --
7304 -- The primary key is invalid therefore we must error
7305 --
7306 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
7307 fnd_message.raise_error;
7308 --
7309 end if;
7310 --
7311 close csr_leg_code;
7312 --
7313 hr_utility.set_location(' Leaving:'|| l_proc, 20);
7314 --
7315 return l_legislation_code;
7316 --
7317 end return_legislation_code;
7318
7319 --
7320 end ben_pgm_bus;