[Home] [Help]
PACKAGE BODY: APPS.BEN_CPP_BUS
Source
1 Package Body ben_cpp_bus as
2 /* $Header: becpprhi.pkb 120.0 2005/05/28 01:16:55 appldev noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_cpp_bus.'; -- Global package name
9 --
10 --
11 -- ----------------------------------------------------------------------------
12 -- |--------------------< chk_duplicate_ordr_num >----------------------------|
13 -- ----------------------------------------------------------------------------
14 --
15 -- Description
16 --
17 -- Pre Conditions
18 -- None.
19 --
20 -- In Parameters
21 -- p_plip_id
22 -- p_ordr_num
23 -- p_effective_date
24 -- p_object_version_number
25 --
26 -- Post Success
27 -- Processing continues
28 --
29 -- Post Failure
30 -- Errors handled by the procedure
31 --
32 -- Access Status
33 -- Internal table handler use only.
34 --
35 -- ----------------------------------------------------------------------------
36 Procedure chk_duplicate_ordr_num
37 ( p_plip_id in number
38 ,p_pgm_id in number
39 ,p_ordr_num in number
40 ,p_effective_date in date
41 ,p_validation_start_date in date
42 ,p_validation_end_date in date
43 ,p_business_group_id in number)
44 is
45 l_proc varchar2(72) := g_package||' chk_duplicate_ordr_num ';
46 l_dummy char(1);
47 cursor c1 is select null
48 from ben_plip_f
49 where plip_id <> nvl(p_plip_id,-1)
50 and pgm_id = p_pgm_id
51 and business_group_id + 0 = p_business_group_id
52 and ordr_num = p_ordr_num
53 and p_validation_start_date <= effective_end_date
54 and p_validation_end_date >= effective_start_date;
55
56 --
57 Begin
58 hr_utility.set_location('Entering:'||l_proc, 5);
59 --
60 open c1;
61 fetch c1 into l_dummy;
62 if c1%found then
63 fnd_message.set_name('BEN','BEN_91001_SEQ_NOT_UNIQUE');
64 fnd_message.raise_error;
65 end if;
66 close c1;
67 --
68 hr_utility.set_location('Leaving:'||l_proc, 15);
69 End chk_duplicate_ordr_num;
70 --
71 -- ----------------------------------------------------------------------------
72 -- |--------------------< chk_duplicate_pl_id_in_pgm>------------------------|
73 -- ----------------------------------------------------------------------------
74 --
75 -- Description
76 -- This procedure checks whether one pl_Id is associated
77 -- to a program id once
78 --
79 -- Pre Conditions
80 -- None.
81 --
82 -- In Parameters
83 -- p_pl_id Plan Id
84 -- p_pgm_id Program Id
85 -- p_effective_date effective_date
86 -- p_business_group_id business_group_id
87 -- p_plip_id PK of record being inserted ot updated
88 --
89 -- Post Success
90 -- Processing continues
91 --
92 -- Post Failure
93 -- Errors handled by the procedure
94 --
95 -- Access Status
96 -- Internal table handler use only.
97 --
98 -- ----------------------------------------------------------------------------
99 Procedure chk_duplicate_pl_id_in_pgm(p_pl_id in number
100 ,p_effective_date in date
101 ,p_business_group_id in number
102 ,p_pgm_id in number
103 ,p_plip_id in number
104 ,p_validation_start_date in date
105 ,p_validation_end_date in date)
106 is
107 l_proc varchar2(72) := g_package||' chk_duplicate_pl_id_in_pgm ';
108 l_dummy char(1);
109
110 cursor c1 is select null
111 from ben_plip_f
112 where pgm_id = p_pgm_id
113 and business_group_id + 0 = p_business_group_id
114 and pl_id = p_pl_id
115 and plip_id <> nvl(p_plip_id, -1)
116 and p_validation_start_date <= effective_end_date
117 and p_validation_end_date >= effective_start_date;
118 --
119 Begin
120 hr_utility.set_location('Entering:'||l_proc, 5);
121 hr_utility.set_location('p_validation_start_date:'||p_validation_start_date, 5);
122 hr_utility.set_location('p_validation_end_date:'||p_validation_end_date, 5);
123 open c1;
124 fetch c1 into l_dummy;
125 if c1%found then
126 fnd_message.set_name('BEN','BEN_91721_DUP_PL_ID_IN_PGM');
127 fnd_message.raise_error;
128 end if;
129 close c1;
130 hr_utility.set_location('Leaving:'||l_proc, 15);
131 End chk_duplicate_pl_id_in_pgm;
132 --
133 -- ----------------------------------------------------------------------------
134 -- |------< chk_plan_allowed_in_pgm >------|
135 -- ----------------------------------------------------------------------------
136 --
137 -- Description
138 --
139 -- Pre Conditions
140 -- None.
141 --
142 -- In Parameters
143 -- pl_id
144 -- effective_date
145 -- business_group_id
146 --
147 -- Post Success
148 -- Processing continues
149 --
150 -- Post Failure
151 -- Errors handled by the procedure
152 --
153 -- Access Status
154 -- Internal table handler use only.
155 --
156 -- ----------------------------------------------------------------------------
157 Procedure chk_plan_allowed_in_pgm(p_pl_id in varchar2
158 ,p_effective_date in date
159 ,p_business_group_id in number)
160 is
161 l_proc varchar2(72) := g_package||' chk_plan_allowed_in_pgm ';
162 l_pl_cd varchar2(30);
163 cursor c1 is select pl_cd
164 from ben_pl_f
165 where pl_id = p_pl_id
166 and p_effective_date between effective_start_date
167 and effective_end_date
168 and business_group_id = p_business_group_id;
169 --
170 Begin
171 hr_utility.set_location('Entering:'||l_proc, 5);
172 open c1;
173 fetch c1 into l_pl_cd;
174 close c1;
175 if l_pl_cd = 'MYNTBPGM' then
176 fnd_message.set_name('BEN','BEN_91787_PL_ALWD_IN_PGM');
177 fnd_message.raise_error;
178 end if;
179 hr_utility.set_location('Leaving:'||l_proc, 15);
180 End chk_plan_allowed_in_pgm;
181 --
182
183 -- --------------------------------chk_cd_rl_combination >-------------------------------|
184 -- ----------------------------------------------------------------------------
185 --
186 -- Description
187 -- This procedure is used to check the code is RULE then the rule must be
188 -- defined else it should not be.
189 --
190 -- Pre Conditions
191 -- None.
192 --
193 -- In Parameters
194 -- p_vrfy_fmly_mmbr_cd in varchar2,
195 -- p_vrfy_fmly_mmbr_rl in number
196 --
197 -- Post Success
198 -- Processing continues
199 --
200 -- Post Failure
201 -- Error handled by procedure
202 --
203 -- Access Status
204 -- Internal table handler use only.
205 --
206 procedure chk_cd_rl_combination
207 (
208 p_vrfy_fmly_mmbr_cd in varchar2,
209 p_vrfy_fmly_mmbr_rl in number ) IS
210 l_proc varchar2(72) := g_package||'chk_cd_rl_combination';
211 BEGIN
212 hr_utility.set_location('Entering:'||l_proc, 5);
213 --
214 if ( p_vrfy_fmly_mmbr_cd <> 'RL' and p_vrfy_fmly_mmbr_rl is not null)
215 then
216 fnd_message.set_name('BEN','BEN_91730_NO_RULE');
217 fnd_message.raise_error;
218 end if;
219
220 if ( p_vrfy_fmly_mmbr_cd = 'RL' and p_vrfy_fmly_mmbr_rl is null)
221 then
222 fnd_message.set_name('BEN','BEN_91731_RULE');
223 fnd_message.raise_error;
224 end if;
225 --
226 --leaving Procedure.
227 --
228 hr_utility.set_location('Leaving:'||l_proc,10);
229 --
230 end chk_cd_rl_combination;
231
232 ---
233 -- ----------------------------------------------------------------------------
234 -- |------< chk_plip_id >------|
235 -- ----------------------------------------------------------------------------
236 --
237 -- Description
238 -- This procedure is used to check that the primary key for the table
239 -- is created properly. It should be null on insert and
240 -- should not be able to be updated.
241 --
242 -- Pre Conditions
243 -- None.
244 --
245 -- In Parameters
246 -- plip_id PK of record being inserted or updated.
247 -- effective_date Effective Date of session
248 -- object_version_number Object version number of record being
249 -- inserted or updated.
250 --
251 -- Post Success
252 -- Processing continues
253 --
254 -- Post Failure
255 -- Errors handled by the procedure
256 --
257 -- Access Status
258 -- Internal table handler use only.
259 --
260 Procedure chk_plip_id(p_plip_id in number,
261 p_effective_date in date,
262 p_object_version_number in number) is
263 --
264 l_proc varchar2(72) := g_package||'chk_plip_id';
265 l_api_updating boolean;
266 --
267 Begin
268 --
269 hr_utility.set_location('Entering:'||l_proc, 5);
270 --
271 l_api_updating := ben_cpp_shd.api_updating
272 (p_effective_date => p_effective_date,
273 p_plip_id => p_plip_id,
274 p_object_version_number => p_object_version_number);
275 --
276 if (l_api_updating
277 and nvl(p_plip_id,hr_api.g_number)
278 <> ben_cpp_shd.g_old_rec.plip_id) then
279 --
280 -- raise error as PK has changed
281 --
282 ben_cpp_shd.constraint_error('BEN_PLIP_PK');
283 --
284 elsif not l_api_updating then
285 --
286 -- check if PK is null
287 --
288 if p_plip_id is not null then
289 --
290 -- raise error as PK is not null
291 --
292 ben_cpp_shd.constraint_error('BEN_PLIP_PK');
293 --
294 end if;
295 --
296 end if;
297 --
298 hr_utility.set_location('Leaving:'||l_proc, 10);
299 --
300 End chk_plip_id;
301 --
302 -- ----------------------------------------------------------------------------
303 -- |------< chk_dflt_enrt_det_rl >------|
304 -- ----------------------------------------------------------------------------
305 --
306 -- Description
307 -- This procedure is used to check that the Formula Rule is valid.
308 --
309 -- Pre Conditions
310 -- None.
311 --
312 -- In Parameters
313 -- plip_id PK of record being inserted or updated.
314 -- dflt_enrt_det_rl Value of formula rule id.
315 -- effective_date effective date
316 -- object_version_number Object version number of record being
317 -- inserted or updated.
318 --
319 -- Post Success
320 -- Processing continues
321 --
322 -- Post Failure
323 -- Error handled by procedure
324 --
325 -- Access Status
326 -- Internal table handler use only.
327 --
328 Procedure chk_dflt_enrt_det_rl
329 (p_plip_id in number,
330 p_dflt_enrt_det_rl in number,
331 p_effective_date in date,
332 p_object_version_number in number,
333 p_business_group_id in number) is
334 --
335 l_proc varchar2(72) := g_package||'chk_dflt_enrt_det_rl';
336 l_api_updating boolean;
337 l_dummy varchar2(1);
338 --
339 cursor c1 is
340 select null
341 from ff_formulas_f ff ,
342 per_business_groups pbg
343 where ff.formula_id = p_dflt_enrt_det_rl
344 and ff.formula_type_id = -32 /*default enrollment det */
345 and pbg.business_group_id = p_business_group_id
346 and nvl(ff.business_group_id, p_business_group_id) =
347 p_business_group_id
348 and nvl(ff.legislation_code, pbg.legislation_code) =
349 pbg.legislation_code
350 and p_effective_date
351 between ff.effective_start_date
352 and ff.effective_end_date;
353 --
354 Begin
355 --
356 hr_utility.set_location('Entering:'||l_proc, 5);
357 --
358 l_api_updating := ben_cpp_shd.api_updating
359 (p_plip_id => p_plip_id,
360 p_effective_date => p_effective_date,
361 p_object_version_number => p_object_version_number);
362 --
363 if (l_api_updating
364 and nvl(p_dflt_enrt_det_rl,hr_api.g_number)
365 <> ben_cpp_shd.g_old_rec.dflt_enrt_det_rl
366 or not l_api_updating)
367 and p_dflt_enrt_det_rl is not null then
368 --
369 -- check if value of formula rule is valid.
370 --
371 open c1;
372 --
373 -- fetch value from cursor if it returns a record then the
374 -- formula is valid otherwise its invalid
375 --
376 fetch c1 into l_dummy;
377 if c1%notfound then
378 --
379 close c1;
380 --
381 -- raise error
382 --
383 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
384 fnd_message.set_token('ID',p_dflt_enrt_det_rl);
385 fnd_message.set_token('TYPE_ID',-32);
386 fnd_message.raise_error;
387 --
388 end if;
389 --
390 close c1;
391 --
392 end if;
393 --
394 hr_utility.set_location('Leaving:'||l_proc,10);
395 --
396 end chk_dflt_enrt_det_rl;
397 --
398 -- ----------------------------------------------------------------------------
399 -- |------< chk_auto_enrt_mthd_rl >------|
400 -- ----------------------------------------------------------------------------
401 --
402 -- Description
403 -- This procedure is used to check that the Formula Rule is valid.
404 --
405 -- Pre Conditions
406 -- None.
407 --
408 -- In Parameters
409 -- plip_id PK of record being inserted or updated.
410 -- auto_enrt_mthd_rl Value of formula rule id.
411 -- effective_date effective date
412 -- object_version_number Object version number of record being
413 -- inserted or updated.
414 --
415 -- Post Success
416 -- Processing continues
417 --
418 -- Post Failure
419 -- Error handled by procedure
420 --
421 -- Access Status
422 -- Internal table handler use only.
423 --
424 Procedure chk_auto_enrt_mthd_rl
425 (p_plip_id in number,
426 p_auto_enrt_mthd_rl in number,
427 p_effective_date in date,
428 p_object_version_number in number,
429 p_business_group_id in number) is
430 --
431 l_proc varchar2(72) := g_package||'chk_auto_enrt_mthd_rl';
432 l_api_updating boolean;
433 l_dummy varchar2(1);
434 --
435 cursor c1 is
436 select null
437 from ff_formulas_f ff ,
438 per_business_groups pbg
439 where ff.formula_id = p_auto_enrt_mthd_rl
440 and ff.formula_type_id = -146 /*default enrollment det */
441 and pbg.business_group_id = p_business_group_id
442 and nvl(ff.business_group_id, p_business_group_id) =
443 p_business_group_id
444 and nvl(ff.legislation_code, pbg.legislation_code) =
445 pbg.legislation_code
446 and p_effective_date
447 between ff.effective_start_date
448 and ff.effective_end_date;
449 --
450 Begin
451 --
452 hr_utility.set_location('Entering:'||l_proc, 5);
453 --
454 l_api_updating := ben_cpp_shd.api_updating
458 --
455 (p_plip_id => p_plip_id,
456 p_effective_date => p_effective_date,
457 p_object_version_number => p_object_version_number);
459 if (l_api_updating
460 and nvl(p_auto_enrt_mthd_rl,hr_api.g_number)
461 <> ben_cpp_shd.g_old_rec.auto_enrt_mthd_rl
462 or not l_api_updating)
463 and p_auto_enrt_mthd_rl is not null then
464 --
465 -- check if value of formula rule is valid.
466 --
467 open c1;
468 --
469 -- fetch value from cursor if it returns a record then the
470 -- formula is valid otherwise its invalid
471 --
472 fetch c1 into l_dummy;
473 if c1%notfound then
474 --
475 close c1;
476 --
477 -- raise error
478 --
479 fnd_message.set_name('BEN','BEN_91953_NVLD_AUTO_ENR_MTH_RL');
480 fnd_message.raise_error;
481 --
482 end if;
483 --
484 close c1;
485 --
486 end if;
487 --
488 hr_utility.set_location('Leaving:'||l_proc,10);
489 --
490 end chk_auto_enrt_mthd_rl;
491 --
492
493
494 Procedure chk_vrfy_fmly_mmbr_cd(p_plip_id in number,
495 p_vrfy_fmly_mmbr_cd in varchar2,
496 p_effective_date in date,
497 p_object_version_number in number) is
498
499 l_proc varchar2(72) := g_package||'chk_vrfy_fmly_mmbr_cd';
500 l_api_updating boolean;
501 --
502 Begin
503 --
504 hr_utility.set_location('Entering:'||l_proc, 5);
505 --
506 l_api_updating := ben_cpp_shd.api_updating
507 (p_plip_id => p_plip_id,
508 p_effective_date => p_effective_date,
509 p_object_version_number => p_object_version_number);
510 --
511 if (l_api_updating
512 and p_vrfy_fmly_mmbr_cd
513 <> nvl(ben_cpp_shd.g_old_rec.vrfy_fmly_mmbr_cd,hr_api.g_varchar2)
514 or not l_api_updating)
515 and p_vrfy_fmly_mmbr_cd is not null
516 then
517 --
518 -- check if value of lookup falls within lookup type.
519 --
520
521 if hr_api.not_exists_in_hr_lookups
522 (p_lookup_type => 'BEN_FMLY_MMBR',
523 p_lookup_code => p_vrfy_fmly_mmbr_cd,
524 p_effective_date => p_effective_date) then
525 --
526 -- raise error as does not exist as lookup
527 --
528 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
529 fnd_message.set_token('FIELD', 'p_vrfy_fmly_mmbr_cd');
530 fnd_message.set_token('TYPE','BEN_FMLY_MMBR');
531 fnd_message.raise_error;
532 --
533 end if;
534 --
535 end if;
536 --
537 hr_utility.set_location('Leaving:'||l_proc,10);
538 --
539 end chk_vrfy_fmly_mmbr_cd;
540
541 -------------------------
542
543
544
545 Procedure chk_use_csd_rsd_prccng_cd(p_plip_id in number,
546 p_use_csd_rsd_prccng_cd in varchar2,
547 p_effective_date in date,
548 p_object_version_number in number) is
549
550 l_proc varchar2(72) := g_package||'chk_vrfy_fmly_mmbr_cd';
551 l_api_updating boolean;
552 --
553 Begin
554 --
555 hr_utility.set_location('Entering:'||l_proc, 5);
556 --
557 l_api_updating := ben_cpp_shd.api_updating
558 (p_plip_id => p_plip_id,
559 p_effective_date => p_effective_date,
560 p_object_version_number => p_object_version_number);
561 --
562 if (l_api_updating
563 and p_use_csd_rsd_prccng_cd
564 <> nvl(ben_cpp_shd.g_old_rec.use_csd_rsd_prccng_cd,hr_api.g_varchar2)
565 or not l_api_updating)
566 and p_use_csd_rsd_prccng_cd is not null
567 then
568 -- check if value of lookup falls within lookup type.
569 --
570
571 if hr_api.not_exists_in_hr_lookups
572 (p_lookup_type => 'BEN_USE_CSD_RSD_PRCCNG',
573 p_lookup_code => p_use_csd_rsd_prccng_cd,
574 p_effective_date => p_effective_date) then
575 --
576 -- raise error as does not exist as lookup
577 --
578 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
579 fnd_message.set_token('FIELD', 'p_use_csd_rsd_prccng_cd');
580 fnd_message.set_token('TYPE','BEN_USE_CSD_RSD_PRCCNG');
581 fnd_message.raise_error;
582 --
583 end if;
584 --
585 end if;
586 --
587 hr_utility.set_location('Leaving:'||l_proc,10);
588 --
589 end chk_use_csd_rsd_prccng_cd;
590
591
592 --------
593
594 Procedure chk_vrfy_fmly_mmbr_rl
595 (p_plip_id in number
596 ,p_vrfy_fmly_mmbr_rl in number
597 ,p_business_group_id in number
598 ,p_effective_date in date
599 ,p_object_version_number in number)
600 is
601 --
602 l_proc varchar2(72) := g_package||'chk_vrfy_fmly_mmbr_rl';
603 l_api_updating boolean;
604 --
605 Begin
606 --
607 hr_utility.set_location('Entering:'||l_proc, 5);
608 --
609 l_api_updating := ben_cpp_shd.api_updating
613 --
610 (p_effective_date => p_effective_date,
611 p_plip_id => p_plip_id,
612 p_object_version_number => p_object_version_number);
614 if (l_api_updating
615 and nvl(p_vrfy_fmly_mmbr_rl,hr_api.g_number)
616 <> ben_cpp_shd.g_old_rec.vrfy_fmly_mmbr_rl
617 or not l_api_updating)
618 and p_vrfy_fmly_mmbr_rl is not null then
619 --
620 -- check if value of formula rule is valid.
621 --
622 if not benutils.formula_exists
623 (p_formula_id => p_vrfy_fmly_mmbr_rl,
624 p_formula_type_id => -21,
625 p_business_group_id => p_business_group_id,
626 p_effective_date => p_effective_date) then
627 --
628 -- raise error
629 --
630 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
631 fnd_message.set_token('ID',p_vrfy_fmly_mmbr_rl);
632 fnd_message.set_token('TYPE_ID',-21);
633 fnd_message.raise_error;
634 --
635 end if;
636 --
637 end if;
638 --
639 hr_utility.set_location('Leaving:'||l_proc,10);
640 end chk_vrfy_fmly_mmbr_rl;
641 --
642 --
643
644
645
646 -- ----------------------------------------------------------------------------
647 -- |------< chk_enrt_rl >------|
648 -- ----------------------------------------------------------------------------
649 --
650 -- Description
651 -- This procedure is used to check that the Formula Rule is valid.
652 --
653 -- Pre Conditions
654 -- None.
655 --
656 -- In Parameters
657 -- plip_id PK of record being inserted or updated.
658 -- enrt_rl Value of formula rule id.
659 -- effective_date effective date
660 -- object_version_number Object version number of record being
661 -- inserted or updated.
662 --
663 -- Post Success
664 -- Processing continues
665 --
666 -- Post Failure
667 -- Error handled by procedure
668 --
669 -- Access Status
670 -- Internal table handler use only.
671 --
672 Procedure chk_enrt_rl
673 (p_plip_id in number,
674 p_enrt_rl in number,
675 p_effective_date in date,
676 p_object_version_number in number,
677 p_business_group_id in number) is
678 --
679 l_proc varchar2(72) := g_package||'chk_enrt_rl';
680 l_api_updating boolean;
681 l_dummy varchar2(1);
682 --
683 cursor c1 is
684 select null
685 from ff_formulas_f ff ,
686 per_business_groups pbg
687 where ff.formula_id = p_enrt_rl
688 and ff.formula_type_id = -393 /*default enrollment det */
689 and pbg.business_group_id = p_business_group_id
690 and nvl(ff.business_group_id, p_business_group_id) =
691 p_business_group_id
692 and nvl(ff.legislation_code, pbg.legislation_code) =
693 pbg.legislation_code
694 and p_effective_date
695 between ff.effective_start_date
696 and ff.effective_end_date;
697 --
698 Begin
699 --
700 hr_utility.set_location('Entering:'||l_proc, 5);
701 --
702 l_api_updating := ben_cpp_shd.api_updating
703 (p_plip_id => p_plip_id,
704 p_effective_date => p_effective_date,
705 p_object_version_number => p_object_version_number);
706 --
707 if (l_api_updating
708 and nvl(p_enrt_rl,hr_api.g_number)
709 <> ben_cpp_shd.g_old_rec.enrt_rl
710 or not l_api_updating)
711 and p_enrt_rl is not null then
712 --
713 -- check if value of formula rule is valid.
714 --
715 open c1;
716 --
717 -- fetch value from cursor if it returns a record then the
718 -- formula is valid otherwise its invalid
719 --
720 fetch c1 into l_dummy;
721 if c1%notfound then
722 --
723 close c1;
724 --
725 -- raise error
726 --
727 fnd_message.set_name('BEN','BEN_91952_INVLD_ENRT_RL');
728 fnd_message.raise_error;
729 --
730 end if;
731 --
732 close c1;
733 --
734 end if;
735 --
736 hr_utility.set_location('Leaving:'||l_proc,10);
737 --
738 end chk_enrt_rl;
739 --
740 -- ----------------------------------------------------------------------------
741 -- |------< chk_dflt_to_asn_pndg_ctfn_rl >------|
742 -- ----------------------------------------------------------------------------
743 --
744 -- Description
745 -- This procedure is used to check that the Formula Rule is valid.
746 --
747 -- Pre Conditions
748 -- None.
749 --
750 -- In Parameters
751 -- plip_id PK of record being inserted or updated.
752 -- enrt_rl Value of formula rule id.
753 -- effective_date effective date
754 -- object_version_number Object version number of record being
755 -- inserted or updated.
756 --
757 -- Post Success
758 -- Processing continues
759 --
760 -- Post Failure
761 -- Error handled by procedure
762 --
763 -- Access Status
764 -- Internal table handler use only.
765 --
766 Procedure chk_dflt_to_asn_pndg_ctfn_rl
770 p_object_version_number in number,
767 (p_plip_id in number,
768 p_dflt_to_asn_pndg_ctfn_rl in number,
769 p_effective_date in date,
771 p_business_group_id in number) is
772 --
773 l_proc varchar2(72) := g_package||'chk_dflt_to_asn_pndg_ctfn_rl';
774 l_api_updating boolean;
775 l_dummy varchar2(1);
776 --
777 cursor c1 is
778 select null
779 from ff_formulas_f ff ,
780 per_business_groups pbg
781 where ff.formula_id = p_dflt_to_asn_pndg_ctfn_rl
782 and ff.formula_type_id = -454
783 and pbg.business_group_id = p_business_group_id
784 and nvl(ff.business_group_id, p_business_group_id) =
785 p_business_group_id
786 and nvl(ff.legislation_code, pbg.legislation_code) =
787 pbg.legislation_code
788 and p_effective_date
789 between ff.effective_start_date
790 and ff.effective_end_date;
791 --
792 Begin
793 --
794 hr_utility.set_location('Entering:'||l_proc, 5);
795 --
796 l_api_updating := ben_cpp_shd.api_updating
797 (p_plip_id => p_plip_id,
798 p_effective_date => p_effective_date,
799 p_object_version_number => p_object_version_number);
800 --
801 if (l_api_updating
802 and nvl(p_dflt_to_asn_pndg_ctfn_rl,hr_api.g_number)
803 <> ben_cpp_shd.g_old_rec.dflt_to_asn_pndg_ctfn_rl
804 or not l_api_updating)
805 and p_dflt_to_asn_pndg_ctfn_rl is not null then
806 --
807 -- check if value of formula rule is valid.
808 --
809 open c1;
810 --
811 -- fetch value from cursor if it returns a record then the
812 -- formula is valid otherwise its invalid
813 --
814 fetch c1 into l_dummy;
815 if c1%notfound then
816 --
817 close c1;
818 --
819 -- raise error
820 --
821 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
822 fnd_message.set_token('ID',p_dflt_to_asn_pndg_ctfn_rl);
823 fnd_message.set_token('TYPE_ID',-454);
824 fnd_message.raise_error;
825 --
826 end if;
827 --
828 close c1;
829 --
830 end if;
831 --
832 hr_utility.set_location('Leaving:'||l_proc,10);
833 --
834 end chk_dflt_to_asn_pndg_ctfn_rl;
835 --
836 -- ----------------------------------------------------------------------------
837 -- |------< chk_mn_cvg_rl >------|
838 -- ----------------------------------------------------------------------------
839 --
840 -- Description
841 -- This procedure is used to check that the Formula Rule is valid.
842 --
843 -- Pre Conditions
844 -- None.
845 --
846 -- In Parameters
847 -- plip_id PK of record being inserted or updated.
848 -- enrt_rl Value of formula rule id.
849 -- effective_date effective date
850 -- object_version_number Object version number of record being
851 -- inserted or updated.
852 --
853 -- Post Success
854 -- Processing continues
855 --
856 -- Post Failure
857 -- Error handled by procedure
858 --
859 -- Access Status
860 -- Internal table handler use only.
861 --
862 Procedure chk_mn_cvg_rl
863 (p_plip_id in number,
864 p_mn_cvg_rl in number,
865 p_effective_date in date,
866 p_object_version_number in number,
867 p_business_group_id in number) is
868 --
869 l_proc varchar2(72) := g_package||'chk_mn_cvg_rl';
870 l_api_updating boolean;
871 l_dummy varchar2(1);
872 --
873 cursor c1 is
874 select null
875 from ff_formulas_f ff ,
876 per_business_groups pbg
877 where ff.formula_id = p_mn_cvg_rl
878 and ff.formula_type_id = -164
879 and pbg.business_group_id = p_business_group_id
880 and nvl(ff.business_group_id, p_business_group_id) =
881 p_business_group_id
882 and nvl(ff.legislation_code, pbg.legislation_code) =
883 pbg.legislation_code
884 and p_effective_date
885 between ff.effective_start_date
886 and ff.effective_end_date;
887 --
888 Begin
889 --
890 hr_utility.set_location('Entering:'||l_proc, 5);
891 --
892 l_api_updating := ben_cpp_shd.api_updating
893 (p_plip_id => p_plip_id,
894 p_effective_date => p_effective_date,
895 p_object_version_number => p_object_version_number);
896 --
897 if (l_api_updating
898 and nvl(p_mn_cvg_rl,hr_api.g_number)
899 <> ben_cpp_shd.g_old_rec.mn_cvg_rl
900 or not l_api_updating)
901 and p_mn_cvg_rl is not null then
902 --
903 -- check if value of formula rule is valid.
904 --
905 open c1;
906 --
907 -- fetch value from cursor if it returns a record then the
908 -- formula is valid otherwise its invalid
909 --
910 fetch c1 into l_dummy;
911 if c1%notfound then
912 --
916 --
913 close c1;
914 --
915 -- raise error
917 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
918 fnd_message.set_token('ID',p_mn_cvg_rl);
919 fnd_message.set_token('TYPE_ID',-164);
920 fnd_message.raise_error;
921 --
922 end if;
923 --
924 close c1;
925 --
926 end if;
927 --
928 hr_utility.set_location('Leaving:'||l_proc,10);
929 --
930 end chk_mn_cvg_rl;
931 --
932 -- ----------------------------------------------------------------------------
933 -- |------< chk_mx_cvg_rl >------|
934 -- ----------------------------------------------------------------------------
935 --
936 -- Description
937 -- This procedure is used to check that the Formula Rule is valid.
938 --
939 -- Pre Conditions
940 -- None.
941 --
942 -- In Parameters
943 -- plip_id PK of record being inserted or updated.
944 -- enrt_rl Value of formula rule id.
945 -- effective_date effective date
946 -- object_version_number Object version number of record being
947 -- inserted or updated.
948 --
949 -- Post Success
950 -- Processing continues
951 --
952 -- Post Failure
953 -- Error handled by procedure
954 --
955 -- Access Status
956 -- Internal table handler use only.
957 --
958 Procedure chk_mx_cvg_rl
959 (p_plip_id in number,
960 p_mx_cvg_rl in number,
961 p_effective_date in date,
962 p_object_version_number in number,
963 p_business_group_id in number) is
964 --
965 l_proc varchar2(72) := g_package||'chk_mx_cvg_rl';
966 l_api_updating boolean;
967 l_dummy varchar2(1);
968 --
969 cursor c1 is
970 select null
971 from ff_formulas_f ff ,
972 per_business_groups pbg
973 where ff.formula_id = p_mx_cvg_rl
974 and ff.formula_type_id = -161
975 and pbg.business_group_id = p_business_group_id
976 and nvl(ff.business_group_id, p_business_group_id) =
977 p_business_group_id
978 and nvl(ff.legislation_code, pbg.legislation_code) =
979 pbg.legislation_code
980 and p_effective_date
981 between ff.effective_start_date
982 and ff.effective_end_date;
983 --
984 Begin
985 --
986 hr_utility.set_location('Entering:'||l_proc, 5);
987 --
988 l_api_updating := ben_cpp_shd.api_updating
989 (p_plip_id => p_plip_id,
990 p_effective_date => p_effective_date,
991 p_object_version_number => p_object_version_number);
992 --
993 if (l_api_updating
994 and nvl(p_mx_cvg_rl,hr_api.g_number)
995 <> ben_cpp_shd.g_old_rec.mx_cvg_rl
996 or not l_api_updating)
997 and p_mx_cvg_rl is not null then
998 --
999 -- check if value of formula rule is valid.
1000 --
1001 open c1;
1002 --
1003 -- fetch value from cursor if it returns a record then the
1004 -- formula is valid otherwise its invalid
1005 --
1006 fetch c1 into l_dummy;
1007 if c1%notfound then
1008 --
1009 close c1;
1010 --
1011 -- raise error
1012 --
1013 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1014 fnd_message.set_token('ID',p_mx_cvg_rl);
1015 fnd_message.set_token('TYPE_ID',-161);
1016 fnd_message.raise_error;
1017 --
1018 end if;
1019 --
1020 close c1;
1021 --
1022 end if;
1023 --
1024 hr_utility.set_location('Leaving:'||l_proc,10);
1025 --
1026 end chk_mx_cvg_rl;
1027 --
1028 -- ----------------------------------------------------------------------------
1029 -- |------< chk_prort_prtl_yr_cvg_rstrn_rl >------|
1030 -- ----------------------------------------------------------------------------
1031 --
1032 -- Description
1033 -- This procedure is used to check that the Formula Rule is valid.
1034 --
1035 -- Pre Conditions
1036 -- None.
1037 --
1038 -- In Parameters
1039 -- plip_id PK of record being inserted or updated.
1040 -- enrt_rl Value of formula rule id.
1041 -- effective_date effective date
1042 -- object_version_number Object version number of record being
1043 -- inserted or updated.
1044 --
1045 -- Post Success
1046 -- Processing continues
1047 --
1048 -- Post Failure
1049 -- Error handled by procedure
1050 --
1051 -- Access Status
1052 -- Internal table handler use only.
1053 --
1054 Procedure chk_prort_prtl_yr_cvg_rstrn_rl
1055 (p_plip_id in number,
1056 p_prort_prtl_yr_cvg_rstrn_rl in number,
1057 p_effective_date in date,
1058 p_object_version_number in number,
1059 p_business_group_id in number) is
1060 --
1061 l_proc varchar2(72) := g_package||'chk_prort_prtl_yr_cvg_rstrn_rl';
1062 l_api_updating boolean;
1063 l_dummy varchar2(1);
1064 --
1068 per_business_groups pbg
1065 cursor c1 is
1066 select null
1067 from ff_formulas_f ff ,
1069 where ff.formula_id = p_prort_prtl_yr_cvg_rstrn_rl
1070 and ff.formula_type_id = -166
1071 and pbg.business_group_id = p_business_group_id
1072 and nvl(ff.business_group_id, p_business_group_id) =
1073 p_business_group_id
1074 and nvl(ff.legislation_code, pbg.legislation_code) =
1075 pbg.legislation_code
1076 and p_effective_date
1077 between ff.effective_start_date
1078 and ff.effective_end_date;
1079 --
1080 Begin
1081 --
1082 hr_utility.set_location('Entering:'||l_proc, 5);
1083 --
1084 l_api_updating := ben_cpp_shd.api_updating
1085 (p_plip_id => p_plip_id,
1086 p_effective_date => p_effective_date,
1087 p_object_version_number => p_object_version_number);
1088 --
1089 if (l_api_updating
1090 and nvl(p_prort_prtl_yr_cvg_rstrn_rl,hr_api.g_number)
1091 <> ben_cpp_shd.g_old_rec.prort_prtl_yr_cvg_rstrn_rl
1092 or not l_api_updating)
1093 and p_prort_prtl_yr_cvg_rstrn_rl is not null then
1094 --
1095 -- check if value of formula rule is valid.
1096 --
1097 open c1;
1098 --
1099 -- fetch value from cursor if it returns a record then the
1100 -- formula is valid otherwise its invalid
1101 --
1102 fetch c1 into l_dummy;
1103 if c1%notfound then
1104 --
1105 close c1;
1106 --
1107 -- raise error
1108 --
1109 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1110 fnd_message.set_token('ID',p_prort_prtl_yr_cvg_rstrn_rl);
1111 fnd_message.set_token('TYPE_ID',-166);
1112 fnd_message.raise_error;
1113 --
1114 end if;
1115 --
1116 close c1;
1117 --
1118 end if;
1119 --
1120 hr_utility.set_location('Leaving:'||l_proc,10);
1121 --
1122 end chk_prort_prtl_yr_cvg_rstrn_rl;
1123 --
1124 -- ----------------------------------------------------------------------------
1125 -- |------< chk_dflt_enrt_cd >------|
1126 -- ----------------------------------------------------------------------------
1127 --
1128 -- Description
1129 -- This procedure is used to check that the lookup value is valid.
1130 --
1131 -- Pre Conditions
1132 -- None.
1133 --
1134 -- In Parameters
1135 -- plip_id PK of record being inserted or updated.
1136 -- dflt_enrt_cd Value of lookup code.
1137 -- effective_date effective date
1138 -- object_version_number Object version number of record being
1139 -- inserted or updated.
1140 --
1141 -- Post Success
1142 -- Processing continues
1143 --
1144 -- Post Failure
1145 -- Error handled by procedure
1146 --
1147 -- Access Status
1148 -- Internal table handler use only.
1149 --
1150 Procedure chk_dflt_enrt_cd(p_plip_id in number,
1151 p_dflt_enrt_cd in varchar2,
1152 p_effective_date in date,
1153 p_object_version_number in number) is
1154 --
1155 l_proc varchar2(72) := g_package||'chk_dflt_enrt_cd';
1156 l_api_updating boolean;
1157 --
1158 Begin
1159 --
1160 hr_utility.set_location('Entering:'||l_proc, 5);
1161 --
1162 l_api_updating := ben_cpp_shd.api_updating
1163 (p_plip_id => p_plip_id,
1164 p_effective_date => p_effective_date,
1165 p_object_version_number => p_object_version_number);
1166 --
1167 if (l_api_updating
1168 and p_dflt_enrt_cd
1169 <> nvl(ben_cpp_shd.g_old_rec.dflt_enrt_cd,hr_api.g_varchar2)
1170 or not l_api_updating)
1171 and p_dflt_enrt_cd is not null then
1172 --
1173 -- check if value of lookup falls within lookup type.
1174 --
1175 if hr_api.not_exists_in_hr_lookups
1176 (p_lookup_type => 'BEN_DFLT_ENRT',
1177 p_lookup_code => p_dflt_enrt_cd,
1178 p_effective_date => p_effective_date) then
1179 --
1180 -- raise error as does not exist as lookup
1181 --
1182 fnd_message.set_name('BEN','BEN_91216_INV_DFLT_ENRT_MTHD');
1183 fnd_message.raise_error;
1184 --
1185 end if;
1186 --
1187 end if;
1188 --
1189 hr_utility.set_location('Leaving:'||l_proc,10);
1190 --
1191 end chk_dflt_enrt_cd;
1192 --
1193 -- ----------------------------------------------------------------------------
1194 -- |------< chk_dflt_to_asn_pndg_ctfn_cd >------|
1195 -- ----------------------------------------------------------------------------
1196 --
1197 -- Description
1198 -- This procedure is used to check that the lookup value is valid.
1199 --
1200 -- Pre Conditions
1201 -- None.
1202 --
1203 -- In Parameters
1204 -- plip_id PK of record being inserted or updated.
1205 -- dflt_enrt_cd Value of lookup code.
1206 -- effective_date effective date
1207 -- object_version_number Object version number of record being
1208 -- inserted or updated.
1209 --
1210 -- Post Success
1211 -- Processing continues
1212 --
1213 -- Post Failure
1214 -- Error handled by procedure
1215 --
1216 -- Access Status
1220 p_dflt_to_asn_pndg_ctfn_cd in varchar2,
1217 -- Internal table handler use only.
1218 --
1219 Procedure chk_dflt_to_asn_pndg_ctfn_cd(p_plip_id in number,
1221 p_effective_date in date,
1222 p_object_version_number in number) is
1223 --
1224 l_proc varchar2(72) := g_package||'chk_dflt_to_asn_pndg_ctfn_cd';
1225 l_api_updating boolean;
1226 --
1227 Begin
1228 --
1229 hr_utility.set_location('Entering:'||l_proc, 5);
1230 --
1231 l_api_updating := ben_cpp_shd.api_updating
1232 (p_plip_id => p_plip_id,
1233 p_effective_date => p_effective_date,
1234 p_object_version_number => p_object_version_number);
1235 --
1236 if (l_api_updating
1237 and p_dflt_to_asn_pndg_ctfn_cd
1238 <> nvl(ben_cpp_shd.g_old_rec.dflt_to_asn_pndg_ctfn_cd,hr_api.g_varchar2)
1239 or not l_api_updating)
1240 and p_dflt_to_asn_pndg_ctfn_cd is not null then
1241 --
1242 -- check if value of lookup falls within lookup type.
1243 --
1244 if hr_api.not_exists_in_hr_lookups
1245 (p_lookup_type => 'BEN_DFLT_TO_ASN_PNDG_CTFN',
1246 p_lookup_code => p_dflt_to_asn_pndg_ctfn_cd,
1247 p_effective_date => p_effective_date) then
1248 --
1249 -- raise error as does not exist as lookup
1250 --
1251 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1252 fnd_message.set_token('FIELD', 'p_dflt_to_asn_pndg_ctfn_cd');
1253 fnd_message.set_token('TYPE', 'BEN_DFLT_TO_ASN_PNDG_CTFN');
1254 fnd_message.raise_error;
1255 --
1256 end if;
1257 --
1258 end if;
1259 --
1260 hr_utility.set_location('Leaving:'||l_proc,10);
1261 --
1262 end chk_dflt_to_asn_pndg_ctfn_cd;
1263 --
1264 -- ----------------------------------------------------------------------------
1265 -- |------< chk_unsspnd_enrt_cd >------|
1266 -- ----------------------------------------------------------------------------
1267 --
1268 -- Description
1269 -- This procedure is used to check that the lookup value is valid.
1270 --
1271 -- Pre Conditions
1272 -- None.
1273 --
1274 -- In Parameters
1275 -- plip_id PK of record being inserted or updated.
1276 -- dflt_enrt_cd Value of lookup code.
1277 -- effective_date effective date
1278 -- object_version_number Object version number of record being
1279 -- inserted or updated.
1280 --
1281 -- Post Success
1282 -- Processing continues
1283 --
1284 -- Post Failure
1285 -- Error handled by procedure
1286 --
1287 -- Access Status
1288 -- Internal table handler use only.
1289 --
1290 Procedure chk_unsspnd_enrt_cd(p_plip_id in number,
1291 p_unsspnd_enrt_cd in varchar2,
1292 p_effective_date in date,
1293 p_object_version_number in number) is
1294 --
1295 l_proc varchar2(72) := g_package||'chk_unsspnd_enrt_cd';
1296 l_api_updating boolean;
1297 --
1298 Begin
1299 --
1300 hr_utility.set_location('Entering:'||l_proc, 5);
1301 --
1302 l_api_updating := ben_cpp_shd.api_updating
1303 (p_plip_id => p_plip_id,
1304 p_effective_date => p_effective_date,
1305 p_object_version_number => p_object_version_number);
1306 --
1307 if (l_api_updating
1308 and p_unsspnd_enrt_cd
1309 <> nvl(ben_cpp_shd.g_old_rec.unsspnd_enrt_cd,hr_api.g_varchar2)
1310 or not l_api_updating)
1311 and p_unsspnd_enrt_cd is not null then
1312 --
1313 -- check if value of lookup falls within lookup type.
1314 --
1315 if hr_api.not_exists_in_hr_lookups
1316 (p_lookup_type => 'BEN_UNSSPND_ENRT',
1317 p_lookup_code => p_unsspnd_enrt_cd,
1318 p_effective_date => p_effective_date) then
1319 --
1320 -- raise error as does not exist as lookup
1321 --
1322 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1323 fnd_message.set_token('FIELD', 'p_unsspnd_enrt_cd');
1324 fnd_message.set_token('TYPE', 'BEN_UNSSPND_ENRT');
1325 fnd_message.raise_error;
1326 --
1327 end if;
1328 --
1329 end if;
1330 --
1331 hr_utility.set_location('Leaving:'||l_proc,10);
1332 --
1333 end chk_unsspnd_enrt_cd;
1334 --
1335 -- ----------------------------------------------------------------------------
1336 -- |------< chk_prort_prtl_yr_cvg_rstrn_cd >------|
1337 -- ----------------------------------------------------------------------------
1338 --
1339 -- Description
1340 -- This procedure is used to check that the lookup value is valid.
1341 --
1342 -- Pre Conditions
1343 -- None.
1344 --
1345 -- In Parameters
1346 -- plip_id PK of record being inserted or updated.
1347 -- dflt_enrt_cd Value of lookup code.
1348 -- effective_date effective date
1349 -- object_version_number Object version number of record being
1350 -- inserted or updated.
1351 --
1352 -- Post Success
1353 -- Processing continues
1354 --
1355 -- Post Failure
1356 -- Error handled by procedure
1357 --
1358 -- Access Status
1359 -- Internal table handler use only.
1360 --
1364 p_object_version_number in number) is
1361 Procedure chk_prort_prtl_yr_cvg_rstrn_cd(p_plip_id in number,
1362 p_prort_prtl_yr_cvg_rstrn_cd in varchar2,
1363 p_effective_date in date,
1365 --
1366 l_proc varchar2(72) := g_package||'chk_prort_prtl_yr_cvg_rstrn_cd';
1367 l_api_updating boolean;
1368 --
1369 Begin
1370 --
1371 hr_utility.set_location('Entering:'||l_proc, 5);
1372 --
1373 l_api_updating := ben_cpp_shd.api_updating
1374 (p_plip_id => p_plip_id,
1375 p_effective_date => p_effective_date,
1376 p_object_version_number => p_object_version_number);
1377 --
1378 if (l_api_updating
1379 and p_prort_prtl_yr_cvg_rstrn_cd
1380 <> nvl(ben_cpp_shd.g_old_rec.prort_prtl_yr_cvg_rstrn_cd,hr_api.g_varchar2)
1381 or not l_api_updating)
1382 and p_prort_prtl_yr_cvg_rstrn_cd is not null then
1383 --
1384 -- check if value of lookup falls within lookup type.
1385 --
1386 if hr_api.not_exists_in_hr_lookups
1387 (p_lookup_type => 'BEN_PRORT_PRTL_YR_CVG_RSTRN',
1388 p_lookup_code => p_prort_prtl_yr_cvg_rstrn_cd,
1389 p_effective_date => p_effective_date) then
1390 --
1391 -- raise error as does not exist as lookup
1392 --
1393 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1394 fnd_message.set_token('FIELD', 'p_prort_prtl_yr_cvg_rstrn_cd');
1395 fnd_message.set_token('TYPE', 'BEN_PRORT_PRTL_YR_CVG_RSTRN');
1396 fnd_message.raise_error;
1397 --
1398 end if;
1399 --
1400 end if;
1401 --
1402 hr_utility.set_location('Leaving:'||l_proc,10);
1403 --
1404 end chk_prort_prtl_yr_cvg_rstrn_cd;
1405 --
1406 -- ----------------------------------------------------------------------------
1407 -- |------< chk_cvg_incr_r_decr_only_cd >------|
1408 -- ----------------------------------------------------------------------------
1409 --
1410 -- Description
1411 -- This procedure is used to check that the lookup value is valid.
1412 --
1413 -- Pre Conditions
1414 -- None.
1415 --
1416 -- In Parameters
1417 -- plip_id PK of record being inserted or updated.
1418 -- dflt_enrt_cd Value of lookup code.
1419 -- effective_date effective date
1420 -- object_version_number Object version number of record being
1421 -- inserted or updated.
1422 --
1423 -- Post Success
1424 -- Processing continues
1425 --
1426 -- Post Failure
1427 -- Error handled by procedure
1428 --
1429 -- Access Status
1430 -- Internal table handler use only.
1431 --
1432 Procedure chk_cvg_incr_r_decr_only_cd(p_plip_id in number,
1433 p_cvg_incr_r_decr_only_cd in varchar2,
1434 p_effective_date in date,
1435 p_object_version_number in number) is
1436 --
1437 l_proc varchar2(72) := g_package||'chk_cvg_incr_r_decr_only_cd';
1438 l_api_updating boolean;
1439 --
1440 Begin
1441 --
1442 hr_utility.set_location('Entering:'||l_proc, 5);
1443 --
1444 l_api_updating := ben_cpp_shd.api_updating
1445 (p_plip_id => p_plip_id,
1446 p_effective_date => p_effective_date,
1447 p_object_version_number => p_object_version_number);
1448 --
1449 if (l_api_updating
1450 and p_cvg_incr_r_decr_only_cd
1451 <> nvl(ben_cpp_shd.g_old_rec.cvg_incr_r_decr_only_cd,hr_api.g_varchar2)
1452 or not l_api_updating)
1453 and p_cvg_incr_r_decr_only_cd is not null then
1454 --
1455 -- check if value of lookup falls within lookup type.
1456 --
1457 if hr_api.not_exists_in_hr_lookups
1458 (p_lookup_type => 'BEN_CVG_INCR_R_DECR_ONLY',
1459 p_lookup_code => p_cvg_incr_r_decr_only_cd,
1460 p_effective_date => p_effective_date) then
1461 --
1462 -- raise error as does not exist as lookup
1463 --
1464 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1465 fnd_message.set_token('FIELD','p_cvg_incr_r_decr_only_cd');
1466 fnd_message.set_token('TYPE','YES_NO');
1467 fnd_message.raise_error;
1468 --
1469 end if;
1470 --
1471 end if;
1472 --
1473 hr_utility.set_location('Leaving:'||l_proc,10);
1474 --
1475 end chk_cvg_incr_r_decr_only_cd;
1476 --
1477 -- ----------------------------------------------------------------------------
1478 -- |------< chk_bnft_or_option_rstrctn_cd >------|
1479 -- ----------------------------------------------------------------------------
1480 --
1481 -- Description
1482 -- This procedure is used to check that the lookup value is valid.
1483 --
1484 -- Pre Conditions
1485 -- None.
1486 --
1487 -- In Parameters
1488 -- plip_id PK of record being inserted or updated.
1489 -- dflt_enrt_cd Value of lookup code.
1490 -- effective_date effective date
1491 -- object_version_number Object version number of record being
1492 -- inserted or updated.
1493 --
1494 -- Post Success
1495 -- Processing continues
1496 --
1497 -- Post Failure
1498 -- Error handled by procedure
1499 --
1500 -- Access Status
1501 -- Internal table handler use only.
1502 --
1506 p_object_version_number in number) is
1503 Procedure chk_bnft_or_option_rstrctn_cd(p_plip_id in number,
1504 p_bnft_or_option_rstrctn_cd in varchar2,
1505 p_effective_date in date,
1507 --
1508 l_proc varchar2(72) := g_package||'chk_bnft_or_option_rstrctn_cd';
1509 l_api_updating boolean;
1510 --
1511 Begin
1512 --
1513 hr_utility.set_location('Entering:'||l_proc, 5);
1514 --
1515 l_api_updating := ben_cpp_shd.api_updating
1516 (p_plip_id => p_plip_id,
1517 p_effective_date => p_effective_date,
1518 p_object_version_number => p_object_version_number);
1519 --
1520 if (l_api_updating
1521 and p_bnft_or_option_rstrctn_cd
1522 <> nvl(ben_cpp_shd.g_old_rec.bnft_or_option_rstrctn_cd,hr_api.g_varchar2)
1523 or not l_api_updating)
1524 and p_bnft_or_option_rstrctn_cd is not null then
1525 --
1526 -- check if value of lookup falls within lookup type.
1527 --
1528 if hr_api.not_exists_in_hr_lookups
1529 (p_lookup_type => 'BEN_BNFT_R_OPT_RSTRN',
1530 p_lookup_code => p_bnft_or_option_rstrctn_cd,
1531 p_effective_date => p_effective_date) then
1532 --
1533 -- raise error as does not exist as lookup
1534 --
1535 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1536 fnd_message.set_token('FIELD','p_bnft_or_option_rstrctn_cd');
1537 fnd_message.set_token('TYPE','BEN_BNFT_R_OPT_RSTRN');
1538 fnd_message.raise_error;
1539 --
1540 end if;
1541 --
1542 end if;
1543 --
1544 hr_utility.set_location('Leaving:'||l_proc,10);
1545 --
1546 end chk_bnft_or_option_rstrctn_cd;
1547 --
1548 -- ----------------------------------------------------------------------------
1549 -- |------< chk_enrt_mthd_cd >------|
1550 -- ----------------------------------------------------------------------------
1551 --
1552 -- Description
1553 -- This procedure is used to check that the lookup value is valid.
1554 --
1555 -- Pre Conditions
1556 -- None.
1557 --
1558 -- In Parameters
1559 -- plip_id PK of record being inserted or updated.
1560 -- enrt_mthd_cd Value of lookup code.
1561 -- effective_date effective date
1562 -- object_version_number Object version number of record being
1563 -- inserted or updated.
1564 --
1565 -- Post Success
1566 -- Processing continues
1567 --
1568 -- Post Failure
1569 -- Error handled by procedure
1570 --
1571 -- Access Status
1572 -- Internal table handler use only.
1573 --
1574 Procedure chk_enrt_mthd_cd(p_plip_id in number,
1575 p_enrt_mthd_cd in varchar2,
1576 p_effective_date in date,
1577 p_object_version_number in number) is
1578 --
1579 l_proc varchar2(72) := g_package||'chk_enrt_mthd_cd';
1580 l_api_updating boolean;
1581 --
1582 Begin
1583 --
1584 hr_utility.set_location('Entering:'||l_proc, 5);
1585 --
1586 l_api_updating := ben_cpp_shd.api_updating
1587 (p_plip_id => p_plip_id,
1588 p_effective_date => p_effective_date,
1589 p_object_version_number => p_object_version_number);
1590 --
1591 if (l_api_updating
1592 and p_enrt_mthd_cd
1593 <> nvl(ben_cpp_shd.g_old_rec.enrt_mthd_cd,hr_api.g_varchar2)
1594 or not l_api_updating)
1595 and p_enrt_mthd_cd is not null then
1596 --
1597 -- check if value of lookup falls within lookup type.
1598 --
1599 if hr_api.not_exists_in_hr_lookups
1600 (p_lookup_type => 'BEN_ENRT_MTHD',
1601 p_lookup_code => p_enrt_mthd_cd,
1602 p_effective_date => p_effective_date) then
1603 --
1604 -- raise error as does not exist as lookup
1605 --
1606 fnd_message.set_name('BEN','BEN_91951_INVLD_ENRT_MTHD_CD');
1607 fnd_message.raise_error;
1608 --
1609 end if;
1610 --
1611 end if;
1612 --
1613 hr_utility.set_location('Leaving:'||l_proc,10);
1614 --
1615 end chk_enrt_mthd_cd;
1616 --
1617 -- ----------------------------------------------------------------------------
1618 -- |------< chk_enrt_cd >------|
1619 -- ----------------------------------------------------------------------------
1620 --
1621 -- Description
1622 -- This procedure is used to check that the lookup value is valid.
1623 --
1624 -- Pre Conditions
1625 -- None.
1626 --
1627 -- In Parameters
1628 -- plip_id PK of record being inserted or updated.
1629 -- enrt_cd Value of lookup code.
1630 -- effective_date effective date
1631 -- object_version_number Object version number of record being
1632 -- inserted or updated.
1633 --
1634 -- Post Success
1635 -- Processing continues
1636 --
1637 -- Post Failure
1638 -- Error handled by procedure
1639 --
1640 -- Access Status
1641 -- Internal table handler use only.
1642 --
1643 Procedure chk_enrt_cd(p_plip_id in number,
1644 p_enrt_cd in varchar2,
1645 p_effective_date in date,
1649 l_api_updating boolean;
1646 p_object_version_number in number) is
1647 --
1648 l_proc varchar2(72) := g_package||'chk_enrt_cd';
1650 --
1651 Begin
1652 --
1653 hr_utility.set_location('Entering:'||l_proc, 5);
1654 --
1655 l_api_updating := ben_cpp_shd.api_updating
1656 (p_plip_id => p_plip_id,
1657 p_effective_date => p_effective_date,
1658 p_object_version_number => p_object_version_number);
1659 --
1660 if (l_api_updating
1661 and p_enrt_cd
1662 <> nvl(ben_cpp_shd.g_old_rec.enrt_cd,hr_api.g_varchar2)
1663 or not l_api_updating)
1664 and p_enrt_cd is not null then
1665 --
1666 -- check if value of lookup falls within lookup type.
1667 --
1668 if hr_api.not_exists_in_hr_lookups
1669 (p_lookup_type => 'BEN_ENRT',
1670 p_lookup_code => p_enrt_cd,
1671 p_effective_date => p_effective_date) then
1672 --
1673 -- raise error as does not exist as lookup
1674 --
1675 fnd_message.set_name('BEN','BEN_91950_INVALID_ENRT_CD');
1676 fnd_message.raise_error;
1677 --
1678 end if;
1679 --
1680 end if;
1681 --
1682 hr_utility.set_location('Leaving:'||l_proc,10);
1683 --
1684 end chk_enrt_cd;
1685 --
1686 -- ----------------------------------------------------------------------------
1687 -- |------< chk_plip_stat_cd >------|
1688 -- ----------------------------------------------------------------------------
1689 --
1690 -- Description
1691 -- This procedure is used to check that the lookup value is valid.
1692 --
1693 -- Pre Conditions
1694 -- None.
1695 --
1696 -- In Parameters
1697 -- plip_id PK of record being inserted or updated.
1698 -- plip_stat_cd Value of lookup code.
1699 -- effective_date effective date
1700 -- object_version_number Object version number of record being
1701 -- inserted or updated.
1702 --
1703 -- Post Success
1704 -- Processing continues
1705 --
1706 -- Post Failure
1707 -- Error handled by procedure
1708 --
1709 -- Access Status
1710 -- Internal table handler use only.
1711 --
1712 Procedure chk_plip_stat_cd(p_plip_id in number,
1713 p_plip_stat_cd in varchar2,
1714 p_effective_date in date,
1715 p_object_version_number in number) is
1716 --
1717 l_proc varchar2(72) := g_package||'chk_plip_stat_cd';
1718 l_api_updating boolean;
1719 --
1720 Begin
1721 --
1722 hr_utility.set_location('Entering:'||l_proc, 5);
1723 --
1724 l_api_updating := ben_cpp_shd.api_updating
1725 (p_plip_id => p_plip_id,
1726 p_effective_date => p_effective_date,
1727 p_object_version_number => p_object_version_number);
1728 --
1729 if (l_api_updating
1730 and p_plip_stat_cd
1731 <> nvl(ben_cpp_shd.g_old_rec.plip_stat_cd,hr_api.g_varchar2)
1732 or not l_api_updating) then
1733 --
1734 -- check if value of lookup falls within lookup type.
1735 --
1736 --
1737 if hr_api.not_exists_in_hr_lookups
1738 (p_lookup_type => 'BEN_STAT',
1739 p_lookup_code => p_plip_stat_cd,
1740 p_effective_date => p_effective_date) then
1741 --
1742 -- raise error as does not exist as lookup
1743 --
1744 fnd_message.set_name('BEN','BEN_91217_INVLD_STAT_CD');
1745 fnd_message.raise_error;
1746 --
1747 end if;
1748 --
1749 end if;
1750 --
1751 hr_utility.set_location('Leaving:'||l_proc,10);
1752 --
1753 end chk_plip_stat_cd;
1754 --
1755 -- ----------------------------------------------------------------------------
1756 -- |------< chk_dflt_flag >------|
1757 -- ----------------------------------------------------------------------------
1758 --
1759 -- Description
1760 -- This procedure is used to check that the lookup value is valid.
1761 --
1762 -- Pre Conditions
1763 -- None.
1764 --
1765 -- In Parameters
1766 -- plip_id PK of record being inserted or updated.
1767 -- dflt_flag Value of lookup code.
1768 -- effective_date effective date
1769 -- object_version_number Object version number of record being
1770 -- inserted or updated.
1771 --
1772 -- Post Success
1773 -- Processing continues
1774 --
1775 -- Post Failure
1776 -- Error handled by procedure
1777 --
1778 -- Access Status
1779 -- Internal table handler use only.
1780 --
1781 Procedure chk_dflt_flag(p_plip_id in number,
1782 p_dflt_flag in varchar2,
1783 p_effective_date in date,
1784 p_object_version_number in number) is
1785 --
1786 l_proc varchar2(72) := g_package||'chk_dflt_flag';
1787 l_api_updating boolean;
1788 --
1789 Begin
1790 --
1791 hr_utility.set_location('Entering:'||l_proc, 5);
1792 --
1793 l_api_updating := ben_cpp_shd.api_updating
1794 (p_plip_id => p_plip_id,
1795 p_effective_date => p_effective_date,
1796 p_object_version_number => p_object_version_number);
1797 --
1801 or not l_api_updating)
1798 if (l_api_updating
1799 and p_dflt_flag
1800 <> nvl(ben_cpp_shd.g_old_rec.dflt_flag,hr_api.g_varchar2)
1802 and p_dflt_flag is not null then
1803 --
1804 -- check if value of lookup falls within lookup type.
1805 --
1806 if hr_api.not_exists_in_hr_lookups
1807 (p_lookup_type => 'YES_NO',
1808 p_lookup_code => p_dflt_flag,
1809 p_effective_date => p_effective_date) then
1810 --
1811 -- raise error as does not exist as lookup
1812 --
1813 fnd_message.set_name('BEN','BEN_91210_INVLD_DFLT_FLAG');
1814 fnd_message.raise_error;
1815 --
1816 end if;
1817 --
1818 end if;
1819 --
1820 hr_utility.set_location('Leaving:'||l_proc,10);
1821 --
1822 end chk_dflt_flag;
1823 --
1824 -- ----------------------------------------------------------------------------
1825 -- |------< chk_alws_unrstrctd_enrt_flag >------|
1826 -- ----------------------------------------------------------------------------
1827 --
1828 -- Description
1829 -- This procedure is used to check that the lookup value is valid.
1830 --
1831 -- Pre Conditions
1832 -- None.
1833 --
1834 -- In Parameters
1835 -- plip_id PK of record being inserted or updated.
1836 -- alws_unrstrctd_enrt_flag Value of lookup code.
1837 -- effective_date effective date
1838 -- object_version_number Object version number of record being
1839 -- inserted or updated.
1840 --
1841 -- Post Success
1842 -- Processing continues
1843 --
1844 -- Post Failure
1845 -- Error handled by procedure
1846 --
1847 -- Access Status
1848 -- Internal table handler use only.
1849 --
1850 Procedure chk_alws_unrstrctd_enrt_flag(p_plip_id in number,
1851 p_alws_unrstrctd_enrt_flag in varchar2,
1852 p_effective_date in date,
1853 p_object_version_number in number) is
1854 --
1855 l_proc varchar2(72) := g_package||'chk_alws_unrstrctd_enrt_flag';
1856 l_api_updating boolean;
1857 --
1858 Begin
1859 --
1860 hr_utility.set_location('Entering:'||l_proc, 5);
1861 --
1862 l_api_updating := ben_cpp_shd.api_updating
1863 (p_plip_id => p_plip_id,
1864 p_effective_date => p_effective_date,
1865 p_object_version_number => p_object_version_number);
1866 --
1867 if (l_api_updating
1868 and p_alws_unrstrctd_enrt_flag
1869 <> nvl(ben_cpp_shd.g_old_rec.alws_unrstrctd_enrt_flag,hr_api.g_varchar2)
1870 or not l_api_updating)
1871 and p_alws_unrstrctd_enrt_flag is not null then
1872 --
1873 -- check if value of lookup falls within lookup type.
1874 --
1875 if hr_api.not_exists_in_hr_lookups
1876 (p_lookup_type => 'YES_NO',
1877 p_lookup_code => p_alws_unrstrctd_enrt_flag,
1878 p_effective_date => p_effective_date) then
1879 --
1880 -- raise error as does not exist as lookup
1881 --
1882 fnd_message.set_name('BEN','BEN_91949_ALWS_UNRSTR_ENRT_FLAG');
1883 fnd_message.raise_error;
1884 --
1885 end if;
1886 --
1887 end if;
1888 --
1889 hr_utility.set_location('Leaving:'||l_proc,10);
1890 --
1891 end chk_alws_unrstrctd_enrt_flag;
1892 --
1893 -- ----------------------------------------------------------------------------
1894 -- |------< chk_no_mn_cvg_amt_apls_flag >------|
1895 -- ----------------------------------------------------------------------------
1896 --
1897 -- Description
1898 -- This procedure is used to check that the lookup value is valid.
1899 --
1900 -- Pre Conditions
1901 -- None.
1902 --
1903 -- In Parameters
1904 -- plip_id PK of record being inserted or updated.
1905 -- no_mn_cvg_amt_apls_flag Value of lookup code.
1906 -- effective_date effective date
1907 -- object_version_number Object version number of record being
1908 -- inserted or updated.
1909 --
1910 -- Post Success
1911 -- Processing continues
1912 --
1913 -- Post Failure
1914 -- Error handled by procedure
1915 --
1916 -- Access Status
1917 -- Internal table handler use only.
1918 --
1919 Procedure chk_no_mn_cvg_amt_apls_flag(p_plip_id in number,
1920 p_no_mn_cvg_amt_apls_flag in varchar2,
1921 p_effective_date in date,
1922 p_object_version_number in number) is
1923 --
1924 l_proc varchar2(72) := g_package||'chk_no_mn_cvg_amt_apls_flag';
1925 l_api_updating boolean;
1926 --
1927 Begin
1928 --
1929 hr_utility.set_location('Entering:'||l_proc, 5);
1930 --
1931 l_api_updating := ben_cpp_shd.api_updating
1932 (p_plip_id => p_plip_id,
1933 p_effective_date => p_effective_date,
1934 p_object_version_number => p_object_version_number);
1935 --
1936 if (l_api_updating
1937 and p_no_mn_cvg_amt_apls_flag
1938 <> nvl(ben_cpp_shd.g_old_rec.no_mn_cvg_amt_apls_flag,hr_api.g_varchar2)
1939 or not l_api_updating)
1940 and p_no_mn_cvg_amt_apls_flag is not null then
1944 if hr_api.not_exists_in_hr_lookups
1941 --
1942 -- check if value of lookup falls within lookup type.
1943 --
1945 (p_lookup_type => 'YES_NO',
1946 p_lookup_code => p_no_mn_cvg_amt_apls_flag,
1947 p_effective_date => p_effective_date) then
1948 --
1949 -- raise error as does not exist as lookup
1950 --
1951 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
1952 fnd_message.set_token('FIELD','p_no_mn_cvg_amt_apls_flag');
1953 fnd_message.set_token('TYPE','YES_NO');
1954 fnd_message.raise_error;
1955 --
1956 end if;
1957 --
1958 end if;
1959 --
1960 hr_utility.set_location('Leaving:'||l_proc,10);
1961 --
1962 end chk_no_mn_cvg_amt_apls_flag;
1963 --
1964 -- ----------------------------------------------------------------------------
1965 -- |------< chk_no_mn_cvg_incr_apls_flag >------|
1966 -- ----------------------------------------------------------------------------
1967 --
1968 -- Description
1969 -- This procedure is used to check that the lookup value is valid.
1970 --
1971 -- Pre Conditions
1972 -- None.
1973 --
1974 -- In Parameters
1975 -- plip_id PK of record being inserted or updated.
1976 -- no_mn_cvg_incr_apls_flag Value of lookup code.
1977 -- effective_date effective date
1978 -- object_version_number Object version number of record being
1979 -- inserted or updated.
1980 --
1981 -- Post Success
1982 -- Processing continues
1983 --
1984 -- Post Failure
1985 -- Error handled by procedure
1986 --
1987 -- Access Status
1988 -- Internal table handler use only.
1989 --
1990 Procedure chk_no_mn_cvg_incr_apls_flag(p_plip_id in number,
1991 p_no_mn_cvg_incr_apls_flag in varchar2,
1992 p_effective_date in date,
1993 p_object_version_number in number) is
1994 --
1995 l_proc varchar2(72) := g_package||'chk_no_mn_cvg_incr_apls_flag';
1996 l_api_updating boolean;
1997 --
1998 Begin
1999 --
2000 hr_utility.set_location('Entering:'||l_proc, 5);
2001 --
2002 l_api_updating := ben_cpp_shd.api_updating
2003 (p_plip_id => p_plip_id,
2004 p_effective_date => p_effective_date,
2005 p_object_version_number => p_object_version_number);
2006 --
2007 if (l_api_updating
2008 and p_no_mn_cvg_incr_apls_flag
2009 <> nvl(ben_cpp_shd.g_old_rec.no_mn_cvg_incr_apls_flag,hr_api.g_varchar2)
2010 or not l_api_updating)
2011 and p_no_mn_cvg_incr_apls_flag is not null then
2012 --
2013 -- check if value of lookup falls within lookup type.
2014 --
2015 if hr_api.not_exists_in_hr_lookups
2016 (p_lookup_type => 'YES_NO',
2017 p_lookup_code => p_no_mn_cvg_incr_apls_flag,
2018 p_effective_date => p_effective_date) then
2019 --
2020 -- raise error as does not exist as lookup
2021 --
2022 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
2023 fnd_message.set_token('FIELD','p_no_mn_cvg_incr_apls_flag');
2024 fnd_message.set_token('TYPE','YES_NO');
2025 fnd_message.raise_error;
2026 --
2027 end if;
2028 --
2029 end if;
2030 --
2031 hr_utility.set_location('Leaving:'||l_proc,10);
2032 --
2033 end chk_no_mn_cvg_incr_apls_flag;
2034 --
2035 -- ----------------------------------------------------------------------------
2036 -- |------< chk_no_mx_cvg_amt_apls_flag >------|
2037 -- ----------------------------------------------------------------------------
2038 --
2039 -- Description
2040 -- This procedure is used to check that the lookup value is valid.
2041 --
2042 -- Pre Conditions
2043 -- None.
2044 --
2045 -- In Parameters
2046 -- plip_id PK of record being inserted or updated.
2047 -- no_mx_cvg_amt_apls_flag Value of lookup code.
2048 -- effective_date effective date
2049 -- object_version_number Object version number of record being
2050 -- inserted or updated.
2051 --
2052 -- Post Success
2053 -- Processing continues
2054 --
2055 -- Post Failure
2056 -- Error handled by procedure
2057 --
2058 -- Access Status
2059 -- Internal table handler use only.
2060 --
2061 Procedure chk_no_mx_cvg_amt_apls_flag(p_plip_id in number,
2062 p_no_mx_cvg_amt_apls_flag in varchar2,
2063 p_effective_date in date,
2064 p_object_version_number in number) is
2065 --
2066 l_proc varchar2(72) := g_package||'chk_no_mx_cvg_amt_apls_flag';
2067 l_api_updating boolean;
2068 --
2069 Begin
2070 --
2071 hr_utility.set_location('Entering:'||l_proc, 5);
2072 --
2073 l_api_updating := ben_cpp_shd.api_updating
2074 (p_plip_id => p_plip_id,
2075 p_effective_date => p_effective_date,
2076 p_object_version_number => p_object_version_number);
2077 --
2078 if (l_api_updating
2079 and p_no_mx_cvg_amt_apls_flag
2080 <> nvl(ben_cpp_shd.g_old_rec.no_mx_cvg_amt_apls_flag,hr_api.g_varchar2)
2084 -- check if value of lookup falls within lookup type.
2081 or not l_api_updating)
2082 and p_no_mx_cvg_amt_apls_flag is not null then
2083 --
2085 --
2086 if hr_api.not_exists_in_hr_lookups
2087 (p_lookup_type => 'YES_NO',
2088 p_lookup_code => p_no_mx_cvg_amt_apls_flag,
2089 p_effective_date => p_effective_date) then
2090 --
2091 -- raise error as does not exist as lookup
2092 --
2093 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
2094 fnd_message.set_token('FIELD','p_no_mx_cvg_amt_apls_flag');
2095 fnd_message.set_token('TYPE','YES_NO');
2096 fnd_message.raise_error;
2097 --
2098 end if;
2099 --
2100 end if;
2101 --
2102 hr_utility.set_location('Leaving:'||l_proc,10);
2103 --
2104 end chk_no_mx_cvg_amt_apls_flag;
2105 --
2106 -- ----------------------------------------------------------------------------
2107 -- |------< chk_no_mx_cvg_incr_apls_flag >------|
2108 -- ----------------------------------------------------------------------------
2109 --
2110 -- Description
2111 -- This procedure is used to check that the lookup value is valid.
2112 --
2113 -- Pre Conditions
2114 -- None.
2115 --
2116 -- In Parameters
2117 -- plip_id PK of record being inserted or updated.
2118 -- no_mx_cvg_incr_apls_flag Value of lookup code.
2119 -- effective_date effective date
2120 -- object_version_number Object version number of record being
2121 -- inserted or updated.
2122 --
2123 -- Post Success
2124 -- Processing continues
2125 --
2126 -- Post Failure
2127 -- Error handled by procedure
2128 --
2129 -- Access Status
2130 -- Internal table handler use only.
2131 --
2132 Procedure chk_no_mx_cvg_incr_apls_flag(p_plip_id in number,
2133 p_no_mx_cvg_incr_apls_flag in varchar2,
2134 p_effective_date in date,
2135 p_object_version_number in number) is
2136 --
2137 l_proc varchar2(72) := g_package||'chk_no_mx_cvg_incr_apls_flag';
2138 l_api_updating boolean;
2139 --
2140 Begin
2141 --
2142 hr_utility.set_location('Entering:'||l_proc, 5);
2143 --
2144 l_api_updating := ben_cpp_shd.api_updating
2145 (p_plip_id => p_plip_id,
2146 p_effective_date => p_effective_date,
2147 p_object_version_number => p_object_version_number);
2148 --
2149 if (l_api_updating
2150 and p_no_mx_cvg_incr_apls_flag
2151 <> nvl(ben_cpp_shd.g_old_rec.no_mx_cvg_incr_apls_flag,hr_api.g_varchar2)
2152 or not l_api_updating)
2153 and p_no_mx_cvg_incr_apls_flag is not null then
2154 --
2155 -- check if value of lookup falls within lookup type.
2156 --
2157 if hr_api.not_exists_in_hr_lookups
2158 (p_lookup_type => 'YES_NO',
2159 p_lookup_code => p_no_mx_cvg_incr_apls_flag,
2160 p_effective_date => p_effective_date) then
2161 --
2162 -- raise error as does not exist as lookup
2163 --
2164 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
2165 fnd_message.set_token('FIELD','p_no_mx_cvg_incr_apls_flag');
2166 fnd_message.set_token('TYPE','YES_NO');
2167 fnd_message.raise_error;
2168 --
2169 end if;
2170 --
2171 end if;
2172 --
2173 hr_utility.set_location('Leaving:'||l_proc,10);
2174 --
2175 end chk_no_mx_cvg_incr_apls_flag;
2176 --
2177 -- ----------------------------------------------------------------------------
2178 -- |-------------------------< chk_all_no_amount_flags >----------------------|
2179 -- ----------------------------------------------------------------------------
2180 --
2181 -- Description
2182 -- This procedure is used to check if the combination of the
2183 -- "no amount" flags and the "amount" values is valid.
2184 --
2185 -- Pre Conditions
2186 -- None.
2187 --
2188 -- In Parameters
2189 -- no_mn_cvg_dfnd_flag
2190 -- mn_cvg_amt
2191 -- no_mx_cvg_dfnd_flag
2192 -- mx_cvg_alwd_amt
2193 --
2194 -- Post Success
2195 -- Processing continues
2196 --
2197 -- Post Failure
2198 -- Error handled by procedure
2199 --
2200 -- Access Status
2201 -- Internal table handler use only.
2202 --
2203 Procedure chk_all_no_amount_flags
2204 (p_no_mn_cvg_amt_apls_flag in varchar2,
2205 p_mn_cvg_amt in number,
2206 p_no_mx_cvg_amt_apls_flag in varchar2,
2207 p_mx_cvg_alwd_amt in number) is
2208 --
2209 l_proc varchar2(72) := g_package||'chk_all_no_amount_flags';
2210 --
2211 Begin
2212 --
2213 hr_utility.set_location('Entering:'||l_proc, 5);
2214 --
2215 -- check if it is a valid combination
2216 --
2217 if ((p_no_mn_cvg_amt_apls_flag='Y' and p_mn_cvg_amt>0) or
2218 (p_no_mn_cvg_amt_apls_flag='N' and p_mn_cvg_amt=0)) then
2219 --
2220 -- raise error as is not a valid combination
2221 --
2222 fnd_message.set_name('BEN','BEN_91150_NO_MIN_CVG_APLS_FLAG');
2223 fnd_message.raise_error;
2224 --
2225 end if;
2226 --
2227 -- check if it is a valid combination
2231 --
2228 --
2229 if ((p_no_mx_cvg_amt_apls_flag='Y' and p_mx_cvg_alwd_amt>0) or
2230 (p_no_mx_cvg_amt_apls_flag='N' and p_mx_cvg_alwd_amt=0)) then
2232 -- raise error as is not a valid combination
2233 --
2234 fnd_message.set_name('BEN','BEN_91149_NO_MAX_CVG_APLS_FLAG');
2235 fnd_message.raise_error;
2236 --
2237 end if;
2238 --
2239 hr_utility.set_location('Leaving:'||l_proc,10);
2240 --
2241 end chk_all_no_amount_flags;
2242 --
2243 -- ----------------------------------------------------------------------------
2244 -- |------< chk_mn_val_mn_flag_mn_rule >------|
2245 -- ----------------------------------------------------------------------------
2246 --
2247 -- Description
2248 -- This procedure is used to check that either the minimum value, no
2249 -- minimum flag, or the minimum rule is entered. More than one of the
2250 -- above mentioned may not have be entered.
2251 --
2252 -- Pre Conditions
2253 -- None.
2254 --
2255 -- In Parameters
2256 -- p_mn_cvg_amt value of Minimum Value
2257 -- p_no_mn_cvg_amt_apls_flag value of No Minimum Flag
2258 -- p_mn_cvg_rl value of Minimum Rule
2259 --
2260 -- Post Success
2261 -- Processing continues
2262 --
2263 -- Post Failure
2264 -- Error handled by procedure
2265 --
2266 -- Access Status
2267 -- Internal table handler use only.
2268 --
2269 Procedure chk_mn_val_mn_flag_mn_rule(p_mn_cvg_amt in number,
2270 p_no_mn_cvg_amt_apls_flag in varchar2,
2271 p_mn_cvg_rl in number) is
2272 --
2273 l_proc varchar2(72) := g_package||'chk_mn_val_mn_flag_mn_rule';
2274 --
2275 Begin
2276 --
2277 hr_utility.set_location('Entering:'||l_proc, 5);
2278 --
2279 if p_mn_cvg_amt is not null and (p_no_mn_cvg_amt_apls_flag = 'Y' or
2280 p_mn_cvg_rl is not null) then
2281 --
2282 fnd_message.set_name('BEN','BEN_91945_MN_VAL_FLAG_RULE');
2283 fnd_message.raise_error;
2284 --
2285 elsif p_mn_cvg_rl is not null and (p_no_mn_cvg_amt_apls_flag = 'Y' or
2286 p_mn_cvg_amt is not null) then
2287 --
2288 fnd_message.set_name('BEN','BEN_91945_MN_VAL_FLAG_RULE');
2289 fnd_message.raise_error;
2290 --
2291 elsif p_no_mn_cvg_amt_apls_flag = 'Y' and (p_mn_cvg_amt is not null or
2292 p_mn_cvg_rl is not null) then
2293 --
2294 fnd_message.set_name('BEN','BEN_91945_MN_VAL_FLAG_RULE');
2295 fnd_message.raise_error;
2296 --
2297 end if;
2298 --
2299 hr_utility.set_location('Leaving:'||l_proc,10);
2300 --
2301 end chk_mn_val_mn_flag_mn_rule;
2302 --
2303 -- ----------------------------------------------------------------------------
2304 -- |------< chk_mx_val_mx_flag_mx_rule >------|
2305 -- ----------------------------------------------------------------------------
2306 --
2307 -- Description
2308 -- This procedure is used to check that either the maximum value, no
2309 -- maximum flag, or the maximum rule is entered. More than one of the
2310 -- above mentioned may not have be entered.
2311 --
2312 -- Pre Conditions
2313 -- None.
2314 --
2315 -- In Parameters
2316 -- p_mx_cvg_alwd_amt value of Minimum Value
2317 -- p_no_mx_cvg_amt_apls_flag value of No Minimum Flag
2318 -- p_mx_cvg_rl value of Minimum Rule
2319 --
2320 -- Post Success
2321 -- Processing continues
2322 --
2323 -- Post Failure
2324 -- Error handled by procedure
2325 --
2326 -- Access Status
2327 -- Internal table handler use only.
2328 --
2329 Procedure chk_mx_val_mx_flag_mx_rule(p_mx_cvg_alwd_amt in number,
2330 p_no_mx_cvg_amt_apls_flag in varchar2,
2331 p_mx_cvg_rl in number) is
2332 --
2333 l_proc varchar2(72) := g_package||'chk_mx_val_mx_flag_mx_rule';
2334 --
2335 Begin
2336 --
2337 hr_utility.set_location('Entering:'||l_proc, 5);
2338 --
2339 if p_mx_cvg_alwd_amt is not null and (p_no_mx_cvg_amt_apls_flag = 'Y' or
2340 p_mx_cvg_rl is not null) then
2341 --
2342 fnd_message.set_name('BEN','BEN_91946_MX_VAL_FLAG_RULE');
2343 fnd_message.raise_error;
2344 --
2345 elsif p_mx_cvg_rl is not null and (p_no_mx_cvg_amt_apls_flag = 'Y' or
2346 p_mx_cvg_alwd_amt is not null) then
2347 --
2348 fnd_message.set_name('BEN','BEN_91946_MX_VAL_FLAG_RULE');
2349 fnd_message.raise_error;
2350 --
2351 elsif p_no_mx_cvg_amt_apls_flag = 'Y' and (p_mx_cvg_alwd_amt is not null or
2352 p_mx_cvg_rl is not null) then
2353 --
2354 fnd_message.set_name('BEN','BEN_91946_MX_VAL_FLAG_RULE');
2355 fnd_message.raise_error;
2356 --
2357 end if;
2358 --
2359 hr_utility.set_location('Leaving:'||l_proc,10);
2360 --
2361 end chk_mx_val_mx_flag_mx_rule;
2362 --
2363 -- ----------------------------------------------------------------------------
2364 -- |------< chk_dflt_enrt_cd_dpndcy >------|
2365 -- ----------------------------------------------------------------------------
2366 --
2367 -- Description
2368 -- If Program Provides no Automatic Enrollment Flag (ben_pgm_f) = 'YES' then
2369 -- Enrollment Method Code cannot be 'Automatic'.
2370 --
2371 --
2375 -- In Parameters
2372 -- Pre Conditions
2373 -- None.
2374 --
2376 -- plip_id PK of record being inserted or updated.
2377 -- dflt_enrt_cd
2378 -- pgm_id
2379 -- business_group_id
2380 -- effective_date effective date
2381 -- object_version_number Object version number of record being
2382 -- inserted or updated.
2383 --
2384 -- Post Success
2385 -- Processing continues
2386 --
2387 -- Post Failure
2388 -- Error handled by procedure
2389 --
2390 -- Access Status
2391 -- Internal table handler use only.
2392 --
2393 Procedure chk_dflt_enrt_cd_dpndcy
2394 (p_plip_id in number,
2395 p_pgm_id in number,
2396 p_dflt_enrt_cd in varchar2,
2397 p_business_group_id in number,
2398 p_effective_date in date,
2399 p_object_version_number in number) is
2400 --
2401 l_proc varchar2(72) := g_package || 'chk_dflt_enrt_cd_dpndcy';
2402 l_api_updating boolean;
2403 l_value varchar2(30);
2404 --
2405 cursor c1 is select pgm_prvds_no_auto_enrt_flag
2406 from ben_pgm_f pgm
2407 where pgm.pgm_id = p_pgm_id
2408 and pgm.business_group_id +0 = p_business_group_id
2409 and p_effective_date between pgm.effective_start_date
2410 and pgm.effective_end_date;
2411 --
2412 Begin
2413 --
2414 hr_utility.set_location('Entering:'||l_proc, 5);
2415 --
2416 l_api_updating := ben_cpp_shd.api_updating
2417 (p_plip_id => p_plip_id,
2418 p_effective_date => p_effective_date,
2419 p_object_version_number => p_object_version_number);
2420 --
2421
2422 if (l_api_updating
2423 and nvl(p_dflt_enrt_cd,hr_api.g_varchar2)
2424 <> nvl(ben_cpp_shd.g_old_rec.dflt_enrt_cd,hr_api.g_varchar2)
2425 or not l_api_updating) then
2426 --
2427 -- If pgm provides no auto enroll flag = 'Y' then enrt mthd code can't be 'A'.
2428 -- Bypass this edit if mthd is null.
2429 --
2430 if p_dflt_enrt_cd is not null then
2431 open c1;
2432 fetch c1 into l_value;
2433 if c1%found then
2434 if l_value = 'Y' and p_dflt_enrt_cd = 'A' then
2435 --
2436 close c1;
2437 fnd_message.set_name('BEN','BEN_91219_DFLT_ENRT_MTHD_DPNDC');
2438 fnd_message.raise_error;
2439 --
2440 end if;
2441 end if;
2442 close c1;
2443 end if;
2444 --
2445 end if;
2446 --
2447 hr_utility.set_location('Leaving:'||l_proc,10);
2448 --
2449 end chk_dflt_enrt_cd_dpndcy;
2450 --
2451 -- ----------------------------------------------------------------------------
2452 -- |------< chk_dflt_dpndcy >------|
2453 -- ----------------------------------------------------------------------------
2454 --
2455 -- Description
2456 -- If Program Provides no Default Enrollment Flag (ben_pgm_f) = 'YES' then
2457 -- the following fields must be null: Default Flag, Default Enrollment
2458 -- Method Code, and Default Enrollment Determination Rule.
2459 --
2460 --
2461 -- Pre Conditions
2462 -- None.
2463 --
2464 -- In Parameters
2465 -- plip_id PK of record being inserted or updated.
2466 -- dflt_enrt_cd
2467 -- dflt_enrt_det_rl
2468 -- dflt_flag
2469 -- pgm_id
2470 -- business_group_id
2471 -- effective_date effective date
2472 -- object_version_number Object version number of record being
2473 -- inserted or updated.
2474 --
2475 -- Post Success
2476 -- Processing continues
2477 --
2478 -- Post Failure
2479 -- Error handled by procedure
2480 --
2481 -- Access Status
2482 -- Internal table handler use only.
2483 --
2484 Procedure chk_dflt_dpndcy(p_plip_id in number,
2485 p_pgm_id in number,
2486 p_dflt_enrt_cd in varchar2,
2487 p_dflt_enrt_det_rl in number,
2488 p_dflt_flag in varchar2,
2489 p_business_group_id in number,
2490 p_effective_date in date,
2491 p_object_version_number in number) is
2492 --
2493 l_proc varchar2(72) := g_package || 'chk_dflt_dpndcy';
2494 l_api_updating boolean;
2495 l_value varchar2(30);
2496 --
2497 cursor c1 is select pgm_prvds_no_dflt_enrt_flag
2498 from ben_pgm_f pgm
2499 where pgm.pgm_id = p_pgm_id
2500 and pgm.business_group_id +0 = p_business_group_id
2501 and p_effective_date between pgm.effective_start_date
2502 and pgm.effective_end_date;
2503 --
2504 Begin
2505 --
2506 hr_utility.set_location('Entering:'||l_proc, 5);
2507 --
2508 l_api_updating := ben_cpp_shd.api_updating
2509 (p_plip_id => p_plip_id,
2510 p_effective_date => p_effective_date,
2511 p_object_version_number => p_object_version_number);
2512 --
2513
2514 if (l_api_updating
2515 and
2516 (nvl(p_dflt_enrt_cd,hr_api.g_varchar2)
2520 nvl(p_dflt_flag,hr_api.g_varchar2)
2517 <> nvl(ben_cpp_shd.g_old_rec.dflt_enrt_cd,hr_api.g_varchar2) or
2518 nvl(p_dflt_enrt_det_rl,hr_api.g_number)
2519 <> nvl(ben_cpp_shd.g_old_rec.dflt_enrt_det_rl,hr_api.g_number) or
2521 <> nvl(ben_cpp_shd.g_old_rec.dflt_flag,hr_api.g_varchar2)
2522 )
2523 or not l_api_updating) then
2524 --
2525 -- If pgm provides no dflt enroll flag = 'Y' then dflt fields must be null
2526 --
2527 open c1;
2528 fetch c1 into l_value;
2529 if c1%found then
2530 --
2531 if l_value = 'Y' and
2532 (p_dflt_enrt_cd is not null or
2533 p_dflt_enrt_det_rl is not null or
2534 p_dflt_flag = 'Y' ) then -- Bug 2717870
2535 --
2536 close c1;
2537 fnd_message.set_name('BEN','BEN_91224_DFLT_FLAG_DPNDCY');
2538 fnd_message.raise_error;
2539 --
2540 end if;
2541 end if;
2542 close c1;
2543 --
2544 end if;
2545 --
2546 hr_utility.set_location('Leaving:'||l_proc,10);
2547 --
2548 end chk_dflt_dpndcy;
2549 --
2550 -- ----------------------------------------------------------------------------
2551 -- |------< chk_dflt_flag_dependency >------|
2552 -- ----------------------------------------------------------------------------
2553 --
2554 -- Description
2555 -- If Default Flag = 'Y', then Default Enrollment Method Code cannot be null.
2556 --
2557 -- Pre Conditions
2558 -- None.
2559 --
2560 -- In Parameters
2561 -- plip_id PK of record being inserted or updated.
2562 -- dflt_enrt_cd
2563 -- dflt_flag
2564 -- effective_date effective date
2565 -- object_version_number Object version number of record being
2566 -- inserted or updated.
2567 --
2568 -- Post Success
2569 -- Processing continues
2570 --
2571 -- Post Failure
2572 -- Error handled by procedure
2573 --
2574 -- Access Status
2575 -- Internal table handler use only.
2576 --
2577 Procedure chk_dflt_flag_dependency(p_plip_id in number,
2578 p_dflt_enrt_cd in varchar2,
2579 p_dflt_flag in varchar2,
2580 p_effective_date in date,
2581 p_object_version_number in number) is
2582 --
2583 l_proc varchar2(72) := g_package||'chk_dflt_flag_dependency';
2584 l_api_updating boolean;
2585 --
2586 Begin
2587 --
2588 hr_utility.set_location('Entering:'||l_proc, 5);
2589 --
2590 l_api_updating := ben_cpp_shd.api_updating
2591 (p_plip_id => p_plip_id,
2592 p_effective_date => p_effective_date,
2593 p_object_version_number => p_object_version_number);
2594 --
2595 if (l_api_updating
2596 and
2597 (nvl(p_dflt_enrt_cd,hr_api.g_varchar2)
2598 <> nvl(ben_cpp_shd.g_old_rec.dflt_enrt_cd,hr_api.g_varchar2) or
2599 nvl(p_dflt_flag,hr_api.g_varchar2)
2600 <> nvl(ben_cpp_shd.g_old_rec.dflt_flag,hr_api.g_varchar2)
2601 )
2602 or not l_api_updating) then
2603 --
2604 -- check dependency
2605 --
2606 if p_dflt_flag = 'Y' and p_dflt_enrt_cd is null then
2607 --
2608 fnd_message.set_name('BEN','BEN_91221_DFLT_FLAG_DPNDCY2');
2609 fnd_message.raise_error;
2610 --
2611 end if;
2612 --
2613 end if;
2614 --
2615 hr_utility.set_location('Leaving:'||l_proc,10);
2616 --
2617 end chk_dflt_flag_dependency;
2618 --
2619 -- ----------------------------------------------------------------------------
2620 -- |------< chk_dflt_enrt_mthd_dpndcy >------|
2621 -- ----------------------------------------------------------------------------
2622 --
2623 -- Description
2624 -- This procedure is used to check the code/rule dependency as following:
2625 -- If code = 'Rule' then rule must be selected.
2626 -- If code <> 'Rule' then code must not be selected.
2627 --
2628 -- Pre Conditions
2629 -- None.
2630 --
2631 -- In Parameters
2632 -- plip_id PK of record being inserted or updated.
2633 -- dflt_enrt_cd Value of lookup code.
2634 -- dflt_enrt_det_rl
2635 -- effective_date effective date
2636 -- object_version_number Object version number of record being
2637 -- inserted or updated.
2638 --
2639 -- Post Success
2640 -- Processing continues
2641 --
2642 -- Post Failure
2643 -- Error handled by procedure
2644 --
2645 -- Access Status
2646 -- Internal table handler use only.
2647 --
2648 Procedure chk_dflt_enrt_mthd_dpndcy(p_plip_id in number,
2649 p_dflt_enrt_cd in varchar2,
2650 p_dflt_enrt_det_rl in number,
2651 p_effective_date in date,
2652 p_object_version_number in number) is
2653 --
2654 l_proc varchar2(72) := g_package||'chk_dflt_enrt_mthd_dpndcy ';
2655 l_api_updating boolean;
2656 --
2657 Begin
2658 --
2659 hr_utility.set_location('Entering:'||l_proc, 5);
2660 --
2661 l_api_updating := ben_cpp_shd.api_updating
2662 (p_plip_id => p_plip_id,
2666 if (l_api_updating
2663 p_effective_date => p_effective_date,
2664 p_object_version_number => p_object_version_number);
2665 --
2667 and
2668 (nvl(p_dflt_enrt_cd,hr_api.g_varchar2)
2669 <> nvl(ben_cpp_shd.g_old_rec.dflt_enrt_cd,hr_api.g_varchar2) or
2670 nvl(p_dflt_enrt_det_rl,hr_api.g_number)
2671 <> nvl(ben_cpp_shd.g_old_rec.dflt_enrt_det_rl,hr_api.g_number))
2672 or not l_api_updating) then
2673 --
2674 if (p_dflt_enrt_cd = 'RL' and p_dflt_enrt_det_rl is null) then
2675 --
2676 fnd_message.set_name('BEN','BEN_91222_DFLT_ENRT_MTHD_CWOR');
2677 fnd_message.raise_error;
2678 --
2679 end if;
2680 --
2681 if nvl(p_dflt_enrt_cd,hr_api.g_varchar2) <> 'RL' and p_dflt_enrt_det_rl is not null then
2682 --
2683 fnd_message.set_name('BEN','BEN_91223_DFLT_ENRT_MTHD_RWOC');
2684 fnd_message.raise_error;
2685 --
2686 end if;
2687 --
2688 end if;
2689 --
2690 hr_utility.set_location('Leaving:'||l_proc,10);
2691 --
2692 end chk_dflt_enrt_mthd_dpndcy;
2693 --
2694 -- ----------------------------------------------------------------------------
2695 -- |------< chk_invk_imptd_incm_per_pgm >------|
2696 -- ----------------------------------------------------------------------------
2697 --
2698 -- Description
2699 -- Only one imputed income plan allowed in a program.
2700 --
2701 --
2702 -- Pre Conditions
2703 -- None.
2704 --
2705 -- In Parameters
2706 -- plip_id PK of record being inserted or updated.
2707 -- pgm_id
2708 -- pl_id
2709 -- business_group_id
2710 -- effective_date effective date
2711 -- object_version_number Object version number of record being
2712 -- inserted or updated.
2713 --
2714 -- Post Success
2715 -- Processing continues
2716 --
2717 -- Post Failure
2718 -- Error handled by procedure
2719 --
2720 -- Access Status
2721 -- Internal table handler use only.
2722 --
2723 Procedure chk_invk_imptd_incm_per_pgm(p_plip_id in number,
2724 p_pgm_id in number,
2725 p_pl_id in number,
2726 p_business_group_id in number,
2727 p_effective_date in date,
2728 p_object_version_number in number) is
2729 --
2730 l_proc varchar2(72) := g_package || 'chk_invk_imptd_incm_per_pgm';
2731 l_count number;
2732 l_imptd_incm_cd varchar2(30);
2733 --
2734 cursor pl_is_imputed is select imptd_incm_calc_cd
2735 from ben_pl_f
2736 where pl_id = p_pl_id
2737 and p_effective_date between effective_start_date
2738 and effective_end_date
2739 and business_group_id +0 = p_business_group_id;
2740 --
2741 cursor count_plans is select count(distinct pl.pl_id)
2742 from ben_pl_f pl,
2743 ben_plip_f plip
2744 where plip.pgm_id = p_pgm_id
2745 and plip.pl_id = pl.pl_id
2746 and pl.imptd_incm_calc_cd = 'PRTT'
2747 and plip.plip_id <> nvl(p_plip_id, -1)
2748 and pl.business_group_id +0 = p_business_group_id
2749 and p_effective_date between pl.effective_start_date
2750 and pl.effective_end_date
2751 and p_effective_date between plip.effective_start_date
2752 and plip.effective_end_date;
2753 --
2754 Begin
2755 --
2756 hr_utility.set_location('Entering:'||l_proc, 5);
2757 --
2758 open pl_is_imputed;
2759 fetch pl_is_imputed into l_imptd_incm_cd;
2760 close pl_is_imputed;
2761 --
2762 if l_imptd_incm_cd = 'PRTT' then
2763 --
2764 open count_plans;
2765 fetch count_plans into l_count;
2766 close count_plans;
2767 if nvl(l_count,0) > 0 then
2768 --
2769 -- Raise error as there is a different Plan with the Invoke
2770 -- Imputed Plan set to ON for this Program
2771 --
2772 fnd_message.set_name('BEN','BEN_91763_INVK_IMPTD_FLAG_PGM');
2773 fnd_message.raise_error;
2774 --
2775 --
2776 end if;
2777 end if;
2778 --
2779 hr_utility.set_location('Leaving:'||l_proc,10);
2780 --
2781 end chk_invk_imptd_incm_per_pgm;
2782 --
2783 --
2784 -- ----------------------------------------------------------------------------
2785 -- |------< chk_invk_flx_crpl_per_pgm >------|
2786 -- ----------------------------------------------------------------------------
2787 --
2788 -- Description
2789 -- INVK_FLX_CR_PL_FLAG on plan can be 'Y' for only one plan in program.
2790 --
2791 --
2792 -- Pre Conditions
2793 -- None.
2794 --
2795 -- In Parameters
2796 -- plip_id PK of record being inserted or updated.
2797 -- pgm_id
2798 -- pl_id
2799 -- business_group_id
2800 -- effective_date effective date
2801 -- object_version_number Object version number of record being
2802 -- inserted or updated.
2803 --
2807 -- Post Failure
2804 -- Post Success
2805 -- Processing continues
2806 --
2808 -- Error handled by procedure
2809 --
2810 -- Access Status
2811 -- Internal table handler use only.
2812 --
2813 Procedure chk_invk_flx_crpl_per_pgm(p_plip_id in number,
2814 p_pgm_id in number,
2815 p_pl_id in number,
2816 p_business_group_id in number,
2817 p_effective_date in date,
2818 p_object_version_number in number) is
2819 --
2820 l_proc varchar2(72) := g_package || 'chk_invk_flx_crpl_per_pgm';
2821 l_count number;
2822 l_flag varchar2(30);
2823 l_pgm_typ_cd ben_pgm_f.pgm_typ_cd%type;
2824 --
2825 cursor pl_is_flex is select invk_flx_cr_pl_flag
2826 from ben_pl_f
2827 where pl_id = p_pl_id
2828 and p_effective_date between effective_start_date
2829 and effective_end_date
2830 and business_group_id = p_business_group_id;
2831 --
2832 cursor c_flex_pgm is select pgm_typ_cd
2833 from ben_pgm_f
2834 where pgm_id = p_pgm_id
2835 and p_effective_date between effective_start_date and effective_end_date
2836 and business_group_id = p_business_group_id;
2837 --
2838 cursor count_plans is select count(distinct pl.pl_id)
2839 from ben_pl_f pl,
2840 ben_plip_f plip
2841 where plip.pgm_id = p_pgm_id
2842 and plip.pl_id = pl.pl_id
2843 and pl.invk_flx_cr_pl_flag = 'Y'
2844 and plip.plip_id <> nvl(p_plip_id, -1)
2845 and pl.business_group_id = p_business_group_id
2846 and p_effective_date between pl.effective_start_date
2847 and pl.effective_end_date
2848 and p_effective_date between plip.effective_start_date
2849 and plip.effective_end_date;
2850 --
2851 Begin
2852 --
2853 hr_utility.set_location('Entering:'||l_proc, 5);
2854 --
2855 open c_flex_pgm;
2856 fetch c_flex_pgm into l_pgm_typ_cd;
2857 close c_flex_pgm;
2858 --
2859
2860 open pl_is_flex;
2861 fetch pl_is_flex into l_flag;
2862 close pl_is_flex;
2863 --
2864 if l_flag = 'Y' then
2865 --
2866 --
2867 If l_pgm_typ_cd not in ('FLEX','FPC','COBRAFLX') then
2868 --
2869 -- Raise error as Flex plans can only be included into
2870 -- programs that are set up as Flex Credit Programs
2871 --
2872 fnd_message.set_name('BEN','BEN_93224_FLXCR_PL_NONFLX_PGM');
2873 fnd_message.raise_error;
2874 end if;
2875 --
2876 open count_plans;
2877 fetch count_plans into l_count;
2878 close count_plans;
2879 if nvl(l_count,0) > 0 then
2880 --
2881 -- Raise error as there is a different Plan with the Flex
2882 -- Credit Plan set to ON for this Program
2883 --
2884 fnd_message.set_name('BEN','BEN_91764_INVK_FLXCR_FLAG_PGM_');
2885 fnd_message.raise_error;
2886 --
2887 --
2888 end if;
2889 end if;
2890 --
2891 hr_utility.set_location('Leaving:'||l_proc,10);
2892 --
2893 end chk_invk_flx_crpl_per_pgm;
2894 --
2895 -- ----------------------------------------------------------------------------
2896 -- |------------------------< chk_enrt_cvg_strt_dt_rl >-----------------------|
2897 -- ----------------------------------------------------------------------------
2898 --
2899 -- Description
2900 -- This procedure is used to check that the Formula Rule is valid.
2901 --
2902 -- Pre Conditions
2903 -- None.
2904 --
2905 -- In Parameters
2906 -- plip_id PK of record being inserted or updated.
2907 -- enrt_cvg_strt_dt_rl Value of formula rule id.
2908 -- effective_date effective date
2909 -- object_version_number Object version number of record being
2910 -- inserted or updated.
2911 --
2912 -- Post Success
2913 -- Processing continues
2914 --
2915 -- Post Failure
2916 -- Error handled by procedure
2917 --
2918 -- Access Status
2919 -- Internal table handler use only.
2920 --
2921 Procedure chk_enrt_cvg_strt_dt_rl(p_plip_id in number,
2922 p_enrt_cvg_strt_dt_rl in number,
2923 p_effective_date in date,
2924 p_object_version_number in number,
2925 p_business_group_id in number) is
2926 --
2927 l_proc varchar2(72) := g_package||'chk_enrt_cvg_strt_dt_rl';
2928 l_api_updating boolean;
2929 l_dummy varchar2(1);
2930 --
2931 cursor c1 is
2932 select null
2933 from ff_formulas_f ff ,
2934 per_business_groups pbg
2935 where ff.formula_id = p_enrt_cvg_strt_dt_rl
2936 and ff.formula_type_id = -29
2937 and pbg.business_group_id = p_business_group_id
2938 and nvl(ff.business_group_id, p_business_group_id) =
2939 p_business_group_id
2943 between ff.effective_start_date
2940 and nvl(ff.legislation_code, pbg.legislation_code) =
2941 pbg.legislation_code
2942 and p_effective_date
2944 and ff.effective_end_date;
2945 --
2946 Begin
2947 --
2948 hr_utility.set_location('Entering:'||l_proc, 5);
2949 --
2950 l_api_updating := ben_cpp_shd.api_updating
2951 (p_plip_id => p_plip_id,
2952 p_effective_date => p_effective_date,
2953 p_object_version_number => p_object_version_number);
2954 --
2955 if (l_api_updating
2956 and nvl(p_enrt_cvg_strt_dt_rl,hr_api.g_number)
2957 <> ben_cpp_shd.g_old_rec.enrt_cvg_strt_dt_rl
2958 or not l_api_updating)
2959 and p_enrt_cvg_strt_dt_rl is not null then
2960 --
2961 -- check if value of formula rule is valid.
2962 --
2963 open c1;
2964 --
2965 -- fetch value from cursor if it returns a record then the
2966 -- formula is valid otherwise its invalid
2967 --
2968 fetch c1 into l_dummy;
2969 if c1%notfound then
2970 --
2971 close c1;
2972 --
2973 -- raise error
2974 --
2975 fnd_message.set_name('BEN','BEN_91251_INV_ENRT_START_DT_RL');
2976 fnd_message.raise_error;
2977 --
2978 end if;
2979 --
2980 close c1;
2981 --
2982 end if;
2983 --
2984 hr_utility.set_location('Leaving:'||l_proc,10);
2985 --
2986 end chk_enrt_cvg_strt_dt_rl;
2987 --
2988 -- ----------------------------------------------------------------------------
2989 -- |------------------------< chk_enrt_cvg_end_dt_rl >------------------------|
2990 -- ----------------------------------------------------------------------------
2991 --
2992 -- Description
2993 -- This procedure is used to check that the Formula Rule is valid.
2994 --
2995 -- Pre Conditions
2996 -- None.
2997 --
2998 -- In Parameters
2999 -- plip_id PK of record being inserted or updated.
3000 -- enrt_cvg_end_dt_rl Value of formula rule id.
3001 -- effective_date effective date
3002 -- object_version_number Object version number of record being
3003 -- inserted or updated.
3004 --
3005 -- Post Success
3006 -- Processing continues
3007 --
3008 -- Post Failure
3009 -- Error handled by procedure
3010 --
3011 -- Access Status
3012 -- Internal table handler use only.
3013 --
3014 Procedure chk_enrt_cvg_end_dt_rl(p_plip_id in number,
3015 p_enrt_cvg_end_dt_rl in number,
3016 p_effective_date in date,
3017 p_object_version_number in number,
3018 p_business_group_id in number) is
3019 --
3020 l_proc varchar2(72) := g_package||'chk_enrt_cvg_end_dt_rl';
3021 l_api_updating boolean;
3022 l_dummy varchar2(1);
3023 --
3024 cursor c1 is
3025 select null
3026 from ff_formulas_f ff ,
3027 per_business_groups pbg
3028 where ff.formula_id = p_enrt_cvg_end_dt_rl
3029 and ff.formula_type_id = -30
3030 and pbg.business_group_id = p_business_group_id
3031 and nvl(ff.business_group_id, p_business_group_id) =
3032 p_business_group_id
3033 and nvl(ff.legislation_code, pbg.legislation_code) =
3034 pbg.legislation_code
3035 and p_effective_date
3036 between ff.effective_start_date
3037 and ff.effective_end_date;
3038 --
3039 Begin
3040 --
3041 hr_utility.set_location('Entering:'||l_proc, 5);
3042 --
3043 l_api_updating := ben_cpp_shd.api_updating
3044 (p_plip_id => p_plip_id,
3045 p_effective_date => p_effective_date,
3046 p_object_version_number => p_object_version_number);
3047 --
3048 if (l_api_updating
3049 and nvl(p_enrt_cvg_end_dt_rl,hr_api.g_number)
3050 <> ben_cpp_shd.g_old_rec.enrt_cvg_end_dt_rl
3051 or not l_api_updating)
3052 and p_enrt_cvg_end_dt_rl is not null then
3053 --
3054 -- check if value of formula rule is valid.
3055 --
3056 open c1;
3057 --
3058 -- fetch value from cursor if it returns a record then the
3059 -- formula is valid otherwise its invalid
3060 --
3061 fetch c1 into l_dummy;
3062 if c1%notfound then
3063 --
3064 close c1;
3065 --
3066 -- raise error
3067 --
3068 fnd_message.set_name('BEN','BEN_91250_INV_ENRT_END_DT_RL');
3069 fnd_message.raise_error;
3070 --
3071 end if;
3072 --
3073 close c1;
3074 --
3075 end if;
3076 --
3077 hr_utility.set_location('Leaving:'||l_proc,10);
3078 --
3079 end chk_enrt_cvg_end_dt_rl;
3080 --
3081 -- ----------------------------------------------------------------------------
3082 -- |------< chk_enrt_cvg_strt_dt_cd >------|
3083 -- ----------------------------------------------------------------------------
3084 --
3085 -- Description
3086 -- This procedure is used to check that the lookup value is valid.
3087 --
3088 -- Pre Conditions
3089 -- None.
3090 --
3091 -- In Parameters
3092 -- plip_id PK of record being inserted or updated.
3096 -- inserted or updated.
3093 -- enrt_cvg_strt_dt_cd Value of lookup code.
3094 -- effective_date effective date
3095 -- object_version_number Object version number of record being
3097 --
3098 -- Post Success
3099 -- Processing continues
3100 --
3101 -- Post Failure
3102 -- Error handled by procedure
3103 --
3104 -- Access Status
3105 -- Internal table handler use only.
3106 --
3107 Procedure chk_enrt_cvg_strt_dt_cd(p_plip_id in number,
3108 p_enrt_cvg_strt_dt_cd in varchar2,
3109 p_effective_date in date,
3110 p_object_version_number in number) is
3111 --
3112 l_proc varchar2(72) := g_package||'chk_enrt_cvg_strt_dt_cd';
3113 l_api_updating boolean;
3114 --
3115 Begin
3116 --
3117 hr_utility.set_location('Entering:'||l_proc, 5);
3118 --
3119 l_api_updating := ben_cpp_shd.api_updating
3120 (p_plip_id => p_plip_id,
3121 p_effective_date => p_effective_date,
3122 p_object_version_number => p_object_version_number);
3123 --
3124 if (l_api_updating
3125 and p_enrt_cvg_strt_dt_cd
3126 <> nvl(ben_cpp_shd.g_old_rec.enrt_cvg_strt_dt_cd,hr_api.g_varchar2)
3127 or not l_api_updating) then
3128 --
3129 -- check if value of lookup falls within lookup type.
3130 --
3131 --
3132 if p_enrt_cvg_strt_dt_cd is not null then
3133 if hr_api.not_exists_in_hr_lookups
3134 (p_lookup_type => 'BEN_ENRT_CVG_STRT',
3135 p_lookup_code => p_enrt_cvg_strt_dt_cd,
3136 p_effective_date => p_effective_date) then
3137 --
3138 -- raise error as does not exist as lookup
3139 --
3140 fnd_message.set_name('BEN','BEN_91300_INV_ENRT_STRT_DT_CD');
3141 fnd_message.raise_error;
3142 --
3143 end if;
3144 end if;
3145 --
3146 end if;
3147 --
3148 hr_utility.set_location('Leaving:'||l_proc,10);
3149 --
3150 end chk_enrt_cvg_strt_dt_cd;
3151 --
3152 -- ----------------------------------------------------------------------------
3153 -- |------< chk_enrt_cvg_end_dt_cd >------|
3154 -- ----------------------------------------------------------------------------
3155 --
3156 -- Description
3157 -- This procedure is used to check that the lookup value is valid.
3158 --
3159 -- Pre Conditions
3160 -- None.
3161 --
3162 -- In Parameters
3163 -- plip_id PK of record being inserted or updated.
3164 -- enrt_cvg_end_dt_cd Value of lookup code.
3165 -- effective_date effective date
3166 -- object_version_number Object version number of record being
3167 -- inserted or updated.
3168 --
3169 -- Post Success
3170 -- Processing continues
3171 --
3172 -- Post Failure
3173 -- Error handled by procedure
3174 --
3175 -- Access Status
3176 -- Internal table handler use only.
3177 --
3178 Procedure chk_enrt_cvg_end_dt_cd(p_plip_id in number,
3179 p_enrt_cvg_end_dt_cd in varchar2,
3180 p_effective_date in date,
3181 p_object_version_number in number) is
3182 --
3183 l_proc varchar2(72) := g_package||'chk_enrt_cvg_end_dt_cd';
3184 l_api_updating boolean;
3185 --
3186 Begin
3187 --
3188 hr_utility.set_location('Entering:'||l_proc, 5);
3189 --
3190 l_api_updating := ben_cpp_shd.api_updating
3191 (p_plip_id => p_plip_id,
3192 p_effective_date => p_effective_date,
3193 p_object_version_number => p_object_version_number);
3194 --
3195 if (l_api_updating
3196 and p_enrt_cvg_end_dt_cd
3197 <> nvl(ben_cpp_shd.g_old_rec.enrt_cvg_end_dt_cd,hr_api.g_varchar2)
3198 or not l_api_updating) then
3199 --
3200 -- check if value of lookup falls within lookup type.
3201 --
3202 --
3203 if p_enrt_cvg_end_dt_cd is not null then
3204 if hr_api.not_exists_in_hr_lookups
3205 (p_lookup_type => 'BEN_ENRT_CVG_END',
3206 p_lookup_code => p_enrt_cvg_end_dt_cd,
3207 p_effective_date => p_effective_date) then
3208 --
3209 -- raise error as does not exist as lookup
3210 --
3211 fnd_message.set_name('BEN','BEN_91301_INV_ENRT_END_DT_CD');
3212 fnd_message.raise_error;
3213 --
3214 end if;
3215 end if;
3216 --
3217 end if;
3218 --
3219 hr_utility.set_location('Leaving:'||l_proc,10);
3220 --
3221 end chk_enrt_cvg_end_dt_cd;
3222 --
3223 -- ----------------------------------------------------------------------------
3224 -- |------< chk_rt_strt_dt_rl >------|
3225 -- ----------------------------------------------------------------------------
3226 --
3227 -- Description
3228 -- This procedure is used to check that the Formula Rule is valid.
3229 --
3230 -- Pre Conditions
3231 -- None.
3232 --
3233 -- In Parameters
3234 -- plip_id PK of record being inserted or updated.
3235 -- rt_strt_dt_rl Value of formula rule id.
3236 -- effective_date effective date
3237 -- object_version_number Object version number of record being
3241 -- Processing continues
3238 -- inserted or updated.
3239 --
3240 -- Post Success
3242 --
3243 -- Post Failure
3244 -- Error handled by procedure
3245 --
3246 -- Access Status
3247 -- Internal table handler use only.
3248 --
3249 Procedure chk_rt_strt_dt_rl(p_plip_id in number,
3250 p_rt_strt_dt_rl in number,
3251 p_effective_date in date,
3252 p_object_version_number in number,
3253 p_business_group_id in number) is
3254 --
3255 l_proc varchar2(72) := g_package||'chk_rt_strt_dt_rl';
3256 l_api_updating boolean;
3257 l_dummy varchar2(1);
3258 --
3259 cursor c1 is
3260 select null
3261 from ff_formulas_f ff ,
3262 per_business_groups pbg
3263 where ff.formula_id = p_rt_strt_dt_rl
3264 and ff.formula_type_id = -66
3265 and pbg.business_group_id = p_business_group_id
3266 and nvl(ff.business_group_id, p_business_group_id) =
3267 p_business_group_id
3268 and nvl(ff.legislation_code, pbg.legislation_code) =
3269 pbg.legislation_code
3270 and p_effective_date
3271 between ff.effective_start_date
3272 and ff.effective_end_date;
3273 --
3274 Begin
3275 --
3276 hr_utility.set_location('Entering:'||l_proc, 5);
3277 --
3278 l_api_updating := ben_cpp_shd.api_updating
3279 (p_plip_id => p_plip_id,
3280 p_effective_date => p_effective_date,
3281 p_object_version_number => p_object_version_number);
3282 --
3283 if (l_api_updating
3284 and nvl(p_rt_strt_dt_rl,hr_api.g_number)
3285 <> ben_cpp_shd.g_old_rec.rt_strt_dt_rl
3286 or not l_api_updating)
3287 and p_rt_strt_dt_rl is not null then
3288 --
3289 -- check if value of formula rule is valid.
3290 --
3291 open c1;
3292 --
3293 -- fetch value from cursor if it returns a record then the
3294 -- formula is valid otherwise its invalid
3295 --
3296 fetch c1 into l_dummy;
3297 if c1%notfound then
3298 --
3299 close c1;
3300 --
3301 -- raise error
3302 --
3303 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
3304 fnd_message.set_token('ID',p_rt_strt_dt_rl);
3305 fnd_message.set_token('TYPE_ID',-66);
3306 fnd_message.raise_error;
3307 --
3308 end if;
3309 --
3310 close c1;
3311 --
3312 end if;
3313 --
3314 hr_utility.set_location('Leaving:'||l_proc,10);
3315 --
3316 end chk_rt_strt_dt_rl;
3317 --
3318 -- ----------------------------------------------------------------------------
3319 -- |------< chk_rt_strt_dt_cd >------|
3320 -- ----------------------------------------------------------------------------
3321 --
3322 -- Description
3323 -- This procedure is used to check that the lookup value is valid.
3324 --
3325 -- Pre Conditions
3326 -- None.
3327 --
3328 -- In Parameters
3329 -- plip_id PK of record being inserted or updated.
3330 -- rt_strt_dt_cd Value of lookup code.
3331 -- effective_date effective date
3332 -- object_version_number Object version number of record being
3333 -- inserted or updated.
3334 --
3335 -- Post Success
3336 -- Processing continues
3337 --
3338 -- Post Failure
3339 -- Error handled by procedure
3340 --
3341 -- Access Status
3342 -- Internal table handler use only.
3343 --
3344 Procedure chk_rt_strt_dt_cd(p_plip_id in number,
3345 p_rt_strt_dt_cd in varchar2,
3346 p_effective_date in date,
3347 p_object_version_number in number) is
3348 --
3349 l_proc varchar2(72) := g_package||'chk_rt_strt_dt_cd';
3350 l_api_updating boolean;
3351 --
3352 Begin
3353 --
3354 hr_utility.set_location('Entering:'||l_proc, 5);
3355 --
3356 l_api_updating := ben_cpp_shd.api_updating
3357 (p_plip_id => p_plip_id,
3358 p_effective_date => p_effective_date,
3359 p_object_version_number => p_object_version_number);
3360 --
3361 if (l_api_updating
3362 and p_rt_strt_dt_cd
3363 <> nvl(ben_cpp_shd.g_old_rec.rt_strt_dt_cd,hr_api.g_varchar2)
3364 or not l_api_updating)
3365 and p_rt_strt_dt_cd is not null then
3366 --
3367 -- check if value of lookup falls within lookup type.
3368 --
3369 if hr_api.not_exists_in_hr_lookups
3370 (p_lookup_type => 'BEN_RT_STRT',
3371 p_lookup_code => p_rt_strt_dt_cd,
3372 p_effective_date => p_effective_date) then
3373 --
3374 -- raise error as does not exist as lookup
3375 --
3376 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3377 fnd_message.set_token('FIELD','p_rt_strt_dt_cd');
3378 fnd_message.set_token('TYPE','BEN_RT_STRT');
3379 fnd_message.raise_error;
3380 --
3381 end if;
3382 --
3383 end if;
3384 --
3385 hr_utility.set_location('Leaving:'||l_proc,10);
3386 --
3387 end chk_rt_strt_dt_cd;
3388 --
3392 --
3389 -- ----------------------------------------------------------------------------
3390 -- |------< chk_rt_end_dt_rl >------|
3391 -- ----------------------------------------------------------------------------
3393 -- Description
3394 -- This procedure is used to check that the Formula Rule is valid.
3395 --
3396 -- Pre Conditions
3397 -- None.
3398 --
3399 -- In Parameters
3400 -- plip_id PK of record being inserted or updated.
3401 -- rt_end_dt_rl Value of formula rule id.
3402 -- effective_date effective date
3403 -- object_version_number Object version number of record being
3404 -- inserted or updated.
3405 --
3406 -- Post Success
3407 -- Processing continues
3408 --
3409 -- Post Failure
3410 -- Error handled by procedure
3411 --
3412 -- Access Status
3413 -- Internal table handler use only.
3414 --
3415 Procedure chk_rt_end_dt_rl(p_plip_id in number,
3416 p_rt_end_dt_rl in number,
3417 p_effective_date in date,
3418 p_object_version_number in number,
3419 p_business_group_id in number) is
3420 --
3421 l_proc varchar2(72) := g_package||'chk_rt_end_dt_rl';
3422 l_api_updating boolean;
3423 l_dummy varchar2(1);
3424 --
3425 cursor c1 is
3426 select null
3427 from ff_formulas_f ff ,
3428 per_business_groups pbg
3429 where ff.formula_id = p_rt_end_dt_rl
3430 and ff.formula_type_id = -67
3431 and pbg.business_group_id = p_business_group_id
3432 and nvl(ff.business_group_id, p_business_group_id) =
3433 p_business_group_id
3434 and nvl(ff.legislation_code, pbg.legislation_code) =
3435 pbg.legislation_code
3436 and p_effective_date
3437 between ff.effective_start_date
3438 and ff.effective_end_date;
3439 --
3440 Begin
3441 --
3442 hr_utility.set_location('Entering:'||l_proc, 5);
3443 --
3444 l_api_updating := ben_cpp_shd.api_updating
3445 (p_plip_id => p_plip_id,
3446 p_effective_date => p_effective_date,
3447 p_object_version_number => p_object_version_number);
3448 --
3449 if (l_api_updating
3450 and nvl(p_rt_end_dt_rl,hr_api.g_number)
3451 <> ben_cpp_shd.g_old_rec.rt_end_dt_rl
3452 or not l_api_updating)
3453 and p_rt_end_dt_rl is not null then
3454 --
3455 -- check if value of formula rule is valid.
3456 --
3457 open c1;
3458 --
3459 -- fetch value from cursor if it returns a record then the
3460 -- formula is valid otherwise its invalid
3461 --
3462 fetch c1 into l_dummy;
3463 if c1%notfound then
3464 --
3465 close c1;
3466 --
3467 -- raise error
3468 --
3469 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
3470 fnd_message.set_token('ID',p_rt_end_dt_rl);
3471 fnd_message.set_token('TYPE_ID',-67);
3472 fnd_message.raise_error;
3473 --
3474 end if;
3475 --
3476 close c1;
3477 --
3478 end if;
3479 --
3480 hr_utility.set_location('Leaving:'||l_proc,10);
3481 --
3482 end chk_rt_end_dt_rl;
3483 --
3484 -- ----------------------------------------------------------------------------
3485 -- |------< chk_postelcn_edit_rl >------|
3486 -- ----------------------------------------------------------------------------
3487 --
3488 -- Description
3489 -- This procedure is used to check that the Formula Rule is valid.
3490 --
3491 -- Pre Conditions
3492 -- None.
3493 --
3494 -- In Parameters
3495 -- plip_id PK of record being inserted or updated.
3496 -- postelcn_edit_rl Value of formula rule id.
3497 -- effective_date effective date
3498 -- object_version_number Object version number of record being
3499 -- inserted or updated.
3500 --
3501 -- Post Success
3502 -- Processing continues
3503 --
3504 -- Post Failure
3505 -- Error handled by procedure
3506 --
3507 -- Access Status
3508 -- Internal table handler use only.
3509 --
3510 Procedure chk_postelcn_edit_rl(p_plip_id in number,
3511 p_postelcn_edit_rl in number,
3512 p_effective_date in date,
3513 p_object_version_number in number,
3514 p_business_group_id in number) is
3515 --
3516 l_proc varchar2(72) := g_package||'chk_postelcn_edit_rl';
3517 l_api_updating boolean;
3518 l_dummy varchar2(1);
3519 --
3520 cursor c1 is
3521 select null
3522 from ff_formulas_f ff ,
3523 per_business_groups pbg
3524 where ff.formula_id = p_postelcn_edit_rl
3525 and ff.formula_type_id = -215
3526 and pbg.business_group_id = p_business_group_id
3527 and nvl(ff.business_group_id, p_business_group_id) =
3528 p_business_group_id
3529 and nvl(ff.legislation_code, pbg.legislation_code) =
3530 pbg.legislation_code
3531 and p_effective_date
3532 between ff.effective_start_date
3533 and ff.effective_end_date;
3534 --
3538 --
3535 Begin
3536 --
3537 hr_utility.set_location('Entering:'||l_proc, 5);
3539 l_api_updating := ben_cpp_shd.api_updating
3540 (p_plip_id => p_plip_id,
3541 p_effective_date => p_effective_date,
3542 p_object_version_number => p_object_version_number);
3543 --
3544 if (l_api_updating
3545 and nvl(p_postelcn_edit_rl,hr_api.g_number)
3546 <> ben_cpp_shd.g_old_rec.postelcn_edit_rl
3547 or not l_api_updating)
3548 and p_postelcn_edit_rl is not null then
3549 --
3550 -- check if value of formula rule is valid.
3551 --
3552 open c1;
3553 --
3554 -- fetch value from cursor if it returns a record then the
3555 -- formula is valid otherwise its invalid
3556 --
3557 fetch c1 into l_dummy;
3558 if c1%notfound then
3559 --
3560 close c1;
3561 --
3562 -- raise error
3563 --
3564 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
3565 fnd_message.set_token('ID',p_postelcn_edit_rl);
3566 fnd_message.set_token('TYPE_ID',-215);
3567 fnd_message.raise_error;
3568 --
3569 end if;
3570 --
3571 close c1;
3572 --
3573 end if;
3574 --
3575 hr_utility.set_location('Leaving:'||l_proc,10);
3576 --
3577 end chk_postelcn_edit_rl;
3578 --
3579 -- ----------------------------------------------------------------------------
3580 -- |------< chk_rt_end_dt_cd >------|
3581 -- ----------------------------------------------------------------------------
3582 --
3583 -- Description
3584 -- This procedure is used to check that the lookup value is valid.
3585 --
3586 -- Pre Conditions
3587 -- None.
3588 --
3589 -- In Parameters
3590 -- plip_id PK of record being inserted or updated.
3591 -- rt_end_dt_cd Value of lookup code.
3592 -- effective_date effective date
3593 -- object_version_number Object version number of record being
3594 -- inserted or updated.
3595 --
3596 -- Post Success
3597 -- Processing continues
3598 --
3599 -- Post Failure
3600 -- Error handled by procedure
3601 --
3602 -- Access Status
3603 -- Internal table handler use only.
3604 --
3605 Procedure chk_rt_end_dt_cd(p_plip_id in number,
3606 p_rt_end_dt_cd in varchar2,
3607 p_effective_date in date,
3608 p_object_version_number in number) is
3609 --
3610 l_proc varchar2(72) := g_package||'chk_rt_end_dt_cd';
3611 l_api_updating boolean;
3612 --
3613 Begin
3614 --
3615 hr_utility.set_location('Entering:'||l_proc, 5);
3616 --
3617 l_api_updating := ben_cpp_shd.api_updating
3618 (p_plip_id => p_plip_id,
3619 p_effective_date => p_effective_date,
3620 p_object_version_number => p_object_version_number);
3621 --
3622 if (l_api_updating
3623 and p_rt_end_dt_cd
3624 <> nvl(ben_cpp_shd.g_old_rec.rt_end_dt_cd,hr_api.g_varchar2)
3625 or not l_api_updating)
3626 and p_rt_end_dt_cd is not null then
3627 --
3628 -- check if value of lookup falls within lookup type.
3629 --
3630 if hr_api.not_exists_in_hr_lookups
3631 (p_lookup_type => 'BEN_RT_END',
3632 p_lookup_code => p_rt_end_dt_cd,
3633 p_effective_date => p_effective_date) then
3634 --
3635 -- raise error as does not exist as lookup
3636 --
3637 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3638 fnd_message.set_token('FIELD','p_rt_end_dt_cd');
3639 fnd_message.set_token('TYPE','BEN_RT_END');
3640 fnd_message.raise_error;
3641 --
3642 end if;
3643 --
3644 end if;
3645 --
3646 hr_utility.set_location('Leaving:'||l_proc,10);
3647 --
3648 end chk_rt_end_dt_cd;
3649 --
3650 -- ----------------------------------------------------------------------------
3651 -- |------------------< chk_drvbl_fctr_apls_rts_flag >------------------------|
3652 -- ----------------------------------------------------------------------------
3653 --
3654 -- Description
3655 -- This procedure is used to check that the lookup value is valid.
3656 --
3657 -- Pre Conditions
3658 -- None.
3659 --
3660 -- In Parameters
3661 -- plip_id PK of record being inserted or updated.
3662 -- drvbl_fctr_apls_rts_flag Value of lookup code.
3663 -- effective_date effective date
3664 -- object_version_number Object version number of record being
3665 -- inserted or updated.
3666 --
3667 -- Post Success
3668 -- Processing continues
3669 --
3670 -- Post Failure
3671 -- Error handled by procedure
3672 --
3673 -- Access Status
3674 -- Internal table handler use only.
3675 --
3676 Procedure chk_drvbl_fctr_apls_rts_flag(p_plip_id in number,
3677 p_drvbl_fctr_apls_rts_flag in varchar2,
3678 p_effective_date in date,
3679 p_object_version_number in number) is
3680 --
3681 l_proc varchar2(72) := g_package||'chk_drvbl_fctr_apls_rts_flag';
3682 l_api_updating boolean;
3686 hr_utility.set_location('Entering:'||l_proc, 5);
3683 --
3684 Begin
3685 --
3687 --
3688 l_api_updating := ben_cpp_shd.api_updating
3689 (p_plip_id => p_plip_id,
3690 p_effective_date => p_effective_date,
3691 p_object_version_number => p_object_version_number);
3692 --
3693 if (l_api_updating
3694 and p_drvbl_fctr_apls_rts_flag
3695 <> nvl(ben_cpp_shd.g_old_rec.drvbl_fctr_apls_rts_flag,hr_api.g_varchar2)
3696 or not l_api_updating)
3697 and p_drvbl_fctr_apls_rts_flag is not null then
3698 --
3699 -- check if value of lookup falls within lookup type.
3700 --
3701 if hr_api.not_exists_in_hr_lookups
3702 (p_lookup_type => 'YES_NO',
3703 p_lookup_code => p_drvbl_fctr_apls_rts_flag,
3704 p_effective_date => p_effective_date) then
3705 --
3706 -- raise error as does not exist as lookup
3707 --
3708 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3709 fnd_message.set_token('FIELD', 'p_drvbl_fctr_apls_rts_flag');
3710 fnd_message.set_token('TYPE', 'YES_NO');
3711 fnd_message.raise_error;
3712 --
3713 end if;
3714 --
3715 end if;
3716 --
3717 hr_utility.set_location('Leaving:'||l_proc,10);
3718 --
3719 end chk_drvbl_fctr_apls_rts_flag;
3720 --
3721 -- ----------------------------------------------------------------------------
3722 -- |------------------< chk_drvbl_fctr_prtn_elig_flag >----------------------|
3723 -- ----------------------------------------------------------------------------
3724 --
3725 -- Description
3726 -- This procedure is used to check that the lookup value is valid.
3727 --
3728 -- Pre Conditions
3729 -- None.
3730 --
3731 -- In Parameters
3732 -- plip_id PK of record being inserted or updated.
3733 -- drvbl_fctr_prtn_elig_flag Value of lookup code.
3734 -- effective_date effective date
3735 -- object_version_number Object version number of record being
3736 -- inserted or updated.
3737 --
3738 -- Post Success
3739 -- Processing continues
3740 --
3741 -- Post Failure
3742 -- Error handled by procedure
3743 --
3744 -- Access Status
3745 -- Internal table handler use only.
3746 --
3747 Procedure chk_drvbl_fctr_prtn_elig_flag(p_plip_id in number,
3748 p_drvbl_fctr_prtn_elig_flag in varchar2,
3749 p_effective_date in date,
3750 p_object_version_number in number) is --
3751 l_proc varchar2(72) := g_package||'chk_drvbl_fctr_prtn_elig_flag';
3752 l_api_updating boolean;
3753 --
3754 Begin
3755 --
3756 hr_utility.set_location('Entering:'||l_proc, 5);
3757 --
3758 l_api_updating := ben_cpp_shd.api_updating
3759 (p_plip_id => p_plip_id,
3760 p_effective_date => p_effective_date,
3761 p_object_version_number => p_object_version_number);
3762 --
3763 if (l_api_updating
3764 and p_drvbl_fctr_prtn_elig_flag
3765 <> nvl(ben_cpp_shd.g_old_rec.drvbl_fctr_prtn_elig_flag,hr_api.g_varchar2)
3766 or not l_api_updating)
3767 and p_drvbl_fctr_prtn_elig_flag is not null then
3768 --
3769 -- check if value of lookup falls within lookup type.
3770 --
3771 if hr_api.not_exists_in_hr_lookups
3772 (p_lookup_type => 'YES_NO',
3773 p_lookup_code => p_drvbl_fctr_prtn_elig_flag,
3774 p_effective_date => p_effective_date) then
3775 --
3776 -- raise error as does not exist as lookup
3777 --
3778 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3779 fnd_message.set_token('FIELD', 'p_drvbl_fctr_prtn_elig_flag');
3780 fnd_message.set_token('TYPE', 'YES_NO');
3781 fnd_message.raise_error;
3782 --
3783 end if;
3784 --
3785 end if;
3786 --
3787 hr_utility.set_location('Leaving:'||l_proc,10);
3788 --
3789 end chk_drvbl_fctr_prtn_elig_flag;
3790 --
3791 -- ----------------------------------------------------------------------------
3792 -- |------------------< chk_elig_apls_flag >----------------------|
3793 -- ----------------------------------------------------------------------------
3794 --
3795 -- Description
3796 -- This procedure is used to check that the lookup value is valid.
3797 --
3798 -- Pre Conditions
3799 -- None.
3800 --
3801 -- In Parameters
3802 -- plip_id PK of record being inserted or updated.
3803 -- elig_apls_flag Value of lookup code.
3804 -- effective_date effective date
3805 -- object_version_number Object version number of record being
3806 -- inserted or updated.
3807 --
3808 -- Post Success
3809 -- Processing continues
3810 --
3811 -- Post Failure
3812 -- Error handled by procedure
3813 --
3814 -- Access Status
3815 -- Internal table handler use only.
3816 --
3817 Procedure chk_elig_apls_flag(p_plip_id in number,
3818 p_elig_apls_flag in varchar2,
3819 p_effective_date in date,
3820 p_object_version_number in number) is
3821 --
3822 l_proc varchar2(72) := g_package||'chk_elig_apls_flag';
3826 --
3823 l_api_updating boolean;
3824 --
3825 Begin
3827 hr_utility.set_location('Entering:'||l_proc, 5);
3828 --
3829 l_api_updating := ben_cpp_shd.api_updating
3830 (p_plip_id => p_plip_id,
3831 p_effective_date => p_effective_date,
3832 p_object_version_number => p_object_version_number);
3833 --
3834 if (l_api_updating
3835 and p_elig_apls_flag
3836 <> nvl(ben_cpp_shd.g_old_rec.elig_apls_flag,hr_api.g_varchar2)
3837 or not l_api_updating)
3838 and p_elig_apls_flag is not null then
3839 --
3840 -- check if value of lookup falls within lookup type.
3841 --
3842 if hr_api.not_exists_in_hr_lookups
3843 (p_lookup_type => 'YES_NO',
3844 p_lookup_code => p_elig_apls_flag,
3845 p_effective_date => p_effective_date) then
3846 --
3847 -- raise error as does not exist as lookup
3848 --
3849 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3850 fnd_message.set_token('FIELD', 'p_elig_apls_flag');
3851 fnd_message.set_token('TYPE', 'YES_NO');
3852 fnd_message.raise_error;
3853 --
3854 end if;
3855 --
3856 end if;
3857 --
3858 hr_utility.set_location('Leaving:'||l_proc,10);
3859 --
3860 end chk_elig_apls_flag;
3861 --
3862 -- ----------------------------------------------------------------------------
3863 -- |------------------< chk_prtn_elig_ovrid_alwd_flag >----------------------|
3864 -- ----------------------------------------------------------------------------
3865 --
3866 -- Description
3867 -- This procedure is used to check that the lookup value is valid.
3868 --
3869 -- Pre Conditions
3870 -- None.
3871 --
3872 -- In Parameters
3873 -- plip_id PK of record being inserted or updated.
3874 -- prtn_elig_ovrid_alwd_flag Value of lookup code.
3875 -- effective_date effective date
3876 -- object_version_number Object version number of record being
3877 -- inserted or updated.
3878 --
3879 -- Post Success
3880 -- Processing continues
3881 --
3882 -- Post Failure
3883 -- Error handled by procedure
3884 --
3885 -- Access Status
3886 -- Internal table handler use only.
3887 --
3888 Procedure chk_prtn_elig_ovrid_alwd_flag(p_plip_id in number,
3889 p_prtn_elig_ovrid_alwd_flag in varchar2,
3890 p_effective_date in date,
3891 p_object_version_number in number)
3892 is
3893 --
3894 l_proc varchar2(72) := g_package||'chk_prtn_elig_ovrid_alwd_flag';
3895 l_api_updating boolean;
3896 --
3897 Begin
3898 --
3899 hr_utility.set_location('Entering:'||l_proc, 5);
3900 --
3901 l_api_updating := ben_cpp_shd.api_updating
3902 (p_plip_id => p_plip_id,
3903 p_effective_date => p_effective_date,
3904 p_object_version_number => p_object_version_number);
3905 --
3906 if (l_api_updating
3907 and p_prtn_elig_ovrid_alwd_flag
3908 <> nvl(ben_cpp_shd.g_old_rec.prtn_elig_ovrid_alwd_flag,hr_api.g_varchar2)
3909 or not l_api_updating)
3910 and p_prtn_elig_ovrid_alwd_flag is not null then
3911 --
3912 -- check if value of lookup falls within lookup type.
3913 --
3914 if hr_api.not_exists_in_hr_lookups
3915 (p_lookup_type => 'YES_NO',
3916 p_lookup_code => p_prtn_elig_ovrid_alwd_flag,
3917 p_effective_date => p_effective_date) then
3918 --
3919 -- raise error as does not exist as lookup
3920 --
3921 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3922 fnd_message.set_token('FIELD', 'p_prtn_elig_ovrid_alwd_flag');
3923 fnd_message.set_token('TYPE', 'YES_NO');
3924 fnd_message.raise_error;
3925 --
3926 end if;
3927 --
3928 end if;
3929 --
3930 hr_utility.set_location('Leaving:'||l_proc,10);
3931 --
3932 end chk_prtn_elig_ovrid_alwd_flag;
3933 --
3934 -- ----------------------------------------------------------------------------
3935 -- |------------------< chk_trk_inelig_per_flag >----------------------|
3936 -- ----------------------------------------------------------------------------
3937 --
3938 -- Description
3939 -- This procedure is used to check that the lookup value is valid.
3940 --
3941 -- Pre Conditions
3942 -- None.
3943 --
3944 -- In Parameters
3945 -- plip_id PK of record being inserted or updated.
3946 -- trk_inelig_per_flag Value of lookup code.
3947 -- effective_date effective date
3948 -- object_version_number Object version number of record being
3949 -- inserted or updated.
3950 --
3951 -- Post Success
3952 -- Processing continues
3953 --
3954 -- Post Failure
3955 -- Error handled by procedure
3956 --
3957 -- Access Status
3958 -- Internal table handler use only.
3959 --
3960 Procedure chk_trk_inelig_per_flag(p_plip_id in number,
3961 p_trk_inelig_per_flag in varchar2,
3962 p_effective_date in date,
3963 p_object_version_number in number)
3964 is
3965 --
3969 Begin
3966 l_proc varchar2(72) := g_package||'chk_trk_inelig_per_flag';
3967 l_api_updating boolean;
3968 --
3970 --
3971 hr_utility.set_location('Entering:'||l_proc, 5);
3972 --
3973 l_api_updating := ben_cpp_shd.api_updating
3974 (p_plip_id => p_plip_id,
3975 p_effective_date => p_effective_date,
3976 p_object_version_number => p_object_version_number);
3977 --
3978 if (l_api_updating
3979 and p_trk_inelig_per_flag
3980 <> nvl(ben_cpp_shd.g_old_rec.trk_inelig_per_flag,hr_api.g_varchar2)
3981 or not l_api_updating)
3982 and p_trk_inelig_per_flag is not null then
3983 --
3984 -- check if value of lookup falls within lookup type.
3985 --
3986 if hr_api.not_exists_in_hr_lookups
3987 (p_lookup_type => 'YES_NO',
3988 p_lookup_code => p_trk_inelig_per_flag,
3989 p_effective_date => p_effective_date) then
3990 --
3991 -- raise error as does not exist as lookup
3992 --
3993 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
3994 fnd_message.set_token('FIELD', 'p_trk_inelig_per_flag');
3995 fnd_message.set_token('TYPE', 'YES_NO');
3996 fnd_message.raise_error;
3997 --
3998 end if;
3999 --
4000 end if;
4001 --
4002 hr_utility.set_location('Leaving:'||l_proc,10);
4003 --
4004 end chk_trk_inelig_per_flag;
4005 --
4006 -- ----------------------------------------------------------------------------
4007 -- |---------------------------------< chk_interim_cd_cvg_calc_mthd >----------------------------|
4008 -- ----------------------------------------------------------------------------
4009 --
4010 -- Description
4011 -- This procedure is used to check the Interim Assign Code.
4012 -- If the Coverage Calculation is set to Flat Amount (FLFX)
4013 -- and Enter Value at Enrollment is checked in the Coverages Form,
4014 -- this procedure will not allow to set the interim assign code to
4015 -- Next Lower
4016 --
4017 -- Pre Conditions
4018 -- None.
4019 --
4020 -- In Parameters
4021 -- pl_id PK of record being inserted or updated.
4022 -- effective_date Effective Date of session
4023 --
4024 -- Post Success
4025 -- Processing continues
4026 --
4027 -- Post Failure
4028 -- Errors handled by the procedure
4029 --
4030 -- Access Status
4031 -- Internal table handler use only.
4032 --
4033 Procedure chk_interim_cd_cvg_calc_mthd(
4034 p_dflt_to_asn_pndg_ctfn_cd in varchar2,
4035 p_plip_id in number,
4036 p_pl_id in number,
4037 p_effective_date in date,
4038 p_business_group_id in number,
4039 p_object_version_number in number) is
4040 --
4041 l_proc varchar2(72) := g_package||'chk_interim_cd_cvg_calc_mthd';
4042 l_api_updating boolean;
4043 l_dummy varchar2(1);
4044 --
4045 cursor c1 is
4046 select null from BEN_CVG_AMT_CALC_MTHD_F cvg
4047 where nvl(cvg.pl_id,-1) = p_pl_id
4048 and cvg.cvg_mlt_cd = 'FLFX'
4049 and cvg.entr_val_at_enrt_flag = 'Y'
4050 and cvg.business_group_id = p_business_group_id
4051 and p_effective_date between cvg.effective_start_date and cvg.effective_end_date;
4052 --
4053 Begin
4054 --
4055 hr_utility.set_location('Entering:'||l_proc, 5);
4056 --
4057 l_api_updating := ben_cpp_shd.api_updating
4058 (p_effective_date => p_effective_date,
4059 p_plip_id => p_plip_id,
4060 p_object_version_number => p_object_version_number);
4061 --
4062 if (l_api_updating
4063 and nvl(p_dflt_to_asn_pndg_ctfn_cd,hr_api.g_varchar2)
4064 <> nvl(ben_cpp_shd.g_old_rec.dflt_to_asn_pndg_ctfn_cd, '***')
4065 or not l_api_updating)
4066 and p_dflt_to_asn_pndg_ctfn_cd is not null then
4067 --
4068 hr_utility.set_location(l_proc, 15);
4069 --
4070 if (instr(p_dflt_to_asn_pndg_ctfn_cd,'NL'))>0 then
4071 --
4072 hr_utility.set_location(l_proc, 25);
4073 --
4074 open c1;
4075 fetch c1 into l_dummy;
4076 if c1%found then
4077 --
4078 close c1;
4079 hr_utility.set_location(l_proc, 35);
4080 fnd_message.set_name('BEN', 'BEN_93113_CD_CANNOT_NEXTLOWER');
4081 fnd_message.raise_error;
4082 --
4083 else
4084 --
4085 close c1;
4086 --
4087 end if;
4088 --
4089 end if; -- End of instr end if
4090 --
4091 end if;
4092 --
4093 hr_utility.set_location('Leaving:'||l_proc, 50);
4094 --
4095 End chk_interim_cd_cvg_calc_mthd;
4096
4097 -- ----------------------------------------------------------------------------
4098 -- BUG 3966957
4099 -- |------< chk_plan_delete_in_pgm >------|
4100 -- ----------------------------------------------------------------------------
4101 --
4102 -- Description
4103 --
4104 -- Pre Conditions
4105 -- None.
4106 --
4107 -- In Parameters
4108 -- plip_id
4109 -- effective_date
4110 --
4111 --
4112 -- Post Success
4113 -- Processing continues
4114 --
4115 -- Post Failure
4116 -- Errors handled by the procedure
4117 --
4118 -- Access Status
4122 Procedure chk_plan_delete_in_pgm( p_plip_id in number
4119 -- Internal table handler use only.
4120 --
4121 -- ----------------------------------------------------------------------------
4123 ,p_validation_start_date in date
4124 ,p_validation_end_date in date
4125 ,p_effective_date in date
4126 ) is
4127
4128 l_proc varchar2(72) := g_package||' chk_plan_delete_in_pgm ';
4129 l_pl_id number;
4130 l_pgm_id number;
4131
4132 cursor c2 is select pgm_id,pl_id
4133 from ben_plip_f
4134 where plip_id =p_plip_id
4135 and p_effective_date between effective_start_date
4136 and effective_end_date;
4137
4138
4139
4140 cursor c1 is select erp.pl_id
4141 from
4142 ben_enrt_perd_for_pl_f erp
4143 where
4144 (
4145 ( enrt_perd_id in
4146 (
4147 select enrt_perd_id
4148 from ben_enrt_perd enp,ben_POPL_ENRT_TYP_CYCL_F pet
4149 where enp.POPL_ENRT_TYP_CYCL_id = pet.POPL_ENRT_TYP_CYCL_id and
4150 pet.pgm_id=l_pgm_id
4151 )
4152 )
4153 or
4154 ( lee_rsn_id in
4155 (
4156 select lee_rsn_id
4157 from ben_lee_rsn_f len,ben_POPL_ENRT_TYP_CYCL_F pet
4158 where len.POPL_ENRT_TYP_CYCL_id = pet.POPL_ENRT_TYP_CYCL_id and
4159 pet.pgm_id = l_pgm_id
4160 )
4161 )
4162 )
4163 and pl_id = l_pl_id
4164 and p_validation_start_date <= erp.effective_end_date
4165 and p_validation_end_date >= erp.effective_start_date ;
4166
4167
4168
4169
4170 Begin
4171 hr_utility.set_location('Entering:'||l_proc, 5);
4172
4173 open c2;
4174 fetch c2 into l_pgm_id,l_pl_id;
4175 close c2;
4176
4177
4178
4179 open c1;
4180 fetch c1 into l_pl_id;
4181
4182 if c1%found then
4183
4184 close c1;
4185
4186 fnd_message.set_name('BEN','BEN_94109_DELETE_PL_ID');
4187 fnd_message.raise_error;
4188
4189 end if;
4190 close c1;
4191 hr_utility.set_location('Leaving:'||l_proc, 15);
4192 End chk_plan_delete_in_pgm;
4193 --
4194 -- ----------------------------------------------------------------------------
4195 -- |--------------------------< dt_update_validate >--------------------------|
4196 -- ----------------------------------------------------------------------------
4197 -- {Start Of Comments}
4198 --
4199 -- Description:
4200 -- This procedure is used for referential integrity of datetracked
4201 -- parent entities when a datetrack update operation is taking place
4202 -- and where there is no cascading of update defined for this entity.
4203 --
4204 -- Prerequisites:
4205 -- This procedure is called from the update_validate.
4206 --
4207 -- In Parameters:
4208 --
4209 -- Post Success:
4210 -- Processing continues.
4211 --
4212 -- Post Failure:
4213 --
4214 -- Developer Implementation Notes:
4215 -- This procedure should not need maintenance unless the HR Schema model
4216 -- changes.
4217 --
4218 -- Access Status:
4219 -- Internal Row Handler Use Only.
4220 --
4221 -- {End Of Comments}
4222 -- ----------------------------------------------------------------------------
4223 Procedure dt_update_validate
4224 (p_dflt_enrt_det_rl in number ,
4225 p_pl_id in number ,
4226 p_pgm_id in number ,
4227 p_datetrack_mode in varchar2,
4228 p_validation_start_date in date,
4229 p_validation_end_date in date) Is
4230 --
4231 l_proc varchar2(72) := g_package||'dt_update_validate';
4232 l_integrity_error Exception;
4233 l_table_name all_tables.table_name%TYPE;
4234 --
4235 Begin
4236 hr_utility.set_location('Entering:'||l_proc, 5);
4237 --
4238 -- Ensure that the p_datetrack_mode argument is not null
4239 --
4240 hr_api.mandatory_arg_error
4241 (p_api_name => l_proc,
4242 p_argument => 'datetrack_mode',
4243 p_argument_value => p_datetrack_mode);
4244 --
4245 -- Only perform the validation if the datetrack update mode is valid
4246 --
4247 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
4248 --
4249 --
4250 -- Ensure the arguments are not null
4251 --
4252 hr_api.mandatory_arg_error
4253 (p_api_name => l_proc,
4254 p_argument => 'validation_start_date',
4255 p_argument_value => p_validation_start_date);
4256 --
4257 hr_api.mandatory_arg_error
4258 (p_api_name => l_proc,
4259 p_argument => 'validation_end_date',
4260 p_argument_value => p_validation_end_date);
4261 --
4262 If ((nvl(p_dflt_enrt_det_rl, hr_api.g_number) <> hr_api.g_number) and
4263 NOT (dt_api.check_min_max_dates
4264 (p_base_table_name => 'ff_formulas_f',
4265 p_base_key_column => 'formula_id',
4266 p_base_key_value => p_dflt_enrt_det_rl,
4267 p_from_date => p_validation_start_date,
4268 p_to_date => p_validation_end_date))) Then
4269 l_table_name := 'ff_formulas_f';
4270 Raise l_integrity_error;
4274 (p_base_table_name => 'ben_pl_f',
4271 End If;
4272 If ((nvl(p_pl_id, hr_api.g_number) <> hr_api.g_number) and
4273 NOT (dt_api.check_min_max_dates
4275 p_base_key_column => 'pl_id',
4276 p_base_key_value => p_pl_id,
4277 p_from_date => p_validation_start_date,
4278 p_to_date => p_validation_end_date))) Then
4279 l_table_name := 'ben_pl_f';
4280 Raise l_integrity_error;
4281 End If;
4282 If ((nvl(p_pgm_id, hr_api.g_number) <> hr_api.g_number) and
4283 NOT (dt_api.check_min_max_dates
4284 (p_base_table_name => 'ben_pgm_f',
4285 p_base_key_column => 'pgm_id',
4286 p_base_key_value => p_pgm_id,
4287 p_from_date => p_validation_start_date,
4288 p_to_date => p_validation_end_date))) Then
4289 l_table_name := 'ben_pgm_f';
4290 Raise l_integrity_error;
4291 End If;
4292 --
4293 End If;
4294 --
4295 hr_utility.set_location(' Leaving:'||l_proc, 10);
4296 Exception
4297 When l_integrity_error Then
4298 --
4299 -- A referential integrity check was violated therefore
4300 -- we must error
4301 --
4302 ben_utility.parent_integrity_error(p_table_name => l_table_name);
4303 When Others Then
4304 --
4305 -- An unhandled or unexpected error has occurred which
4306 -- we must report
4307 --
4308 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
4309 fnd_message.set_token('PROCEDURE', l_proc);
4310 fnd_message.set_token('STEP','15');
4311 fnd_message.raise_error;
4312 End dt_update_validate;
4313 --
4314 -- ----------------------------------------------------------------------------
4315 -- |--------------------------< dt_delete_validate >--------------------------|
4316 -- ----------------------------------------------------------------------------
4317 -- {Start Of Comments}
4318 --
4319 -- Description:
4320 -- This procedure is used for referential integrity of datetracked
4321 -- child entities when either a datetrack DELETE or ZAP is in operation
4322 -- and where there is no cascading of delete defined for this entity.
4323 -- For the datetrack mode of DELETE or ZAP we must ensure that no
4324 -- datetracked child rows exist between the validation start and end
4325 -- dates.
4326 --
4327 -- Prerequisites:
4328 -- This procedure is called from the delete_validate.
4329 --
4330 -- In Parameters:
4331 --
4332 -- Post Success:
4333 -- Processing continues.
4334 --
4335 -- Post Failure:
4336 -- If a row exists by determining the returning Boolean value from the
4337 -- generic dt_api.rows_exist function then we must supply an error via
4338 -- the use of the local exception handler l_rows_exist.
4339 --
4340 -- Developer Implementation Notes:
4341 -- This procedure should not need maintenance unless the HR Schema model
4342 -- changes.
4343 --
4344 -- Access Status:
4345 -- Internal Row Handler Use Only.
4346 --
4347 -- {End Of Comments}
4348 -- ----------------------------------------------------------------------------
4349 Procedure dt_delete_validate
4350 (p_plip_id in number,
4351 p_datetrack_mode in varchar2,
4352 p_validation_start_date in date,
4353 p_validation_end_date in date) Is
4354 --
4355 l_proc varchar2(72) := g_package||'dt_delete_validate';
4356 l_rows_exist Exception;
4357 l_table_name all_tables.table_name%TYPE;
4358 --
4359 Begin
4360 hr_utility.set_location('Entering:'||l_proc, 5);
4361 --
4362 -- Ensure that the p_datetrack_mode argument is not null
4363 --
4364 hr_api.mandatory_arg_error
4365 (p_api_name => l_proc,
4366 p_argument => 'datetrack_mode',
4367 p_argument_value => p_datetrack_mode);
4368 --
4369 -- Only perform the validation if the datetrack mode is either
4370 -- DELETE or ZAP
4371 --
4372 If (p_datetrack_mode = 'DELETE' or
4373 p_datetrack_mode = 'ZAP') then
4374 --
4375 --
4376 -- Ensure the arguments are not null
4377 --
4378 hr_api.mandatory_arg_error
4379 (p_api_name => l_proc,
4380 p_argument => 'validation_start_date',
4381 p_argument_value => p_validation_start_date);
4382 --
4383 hr_api.mandatory_arg_error
4384 (p_api_name => l_proc,
4385 p_argument => 'validation_end_date',
4386 p_argument_value => p_validation_end_date);
4387 --
4388 hr_api.mandatory_arg_error
4389 (p_api_name => l_proc,
4390 p_argument => 'plip_id',
4391 p_argument_value => p_plip_id);
4392 --
4393 If (dt_api.rows_exist
4394 (p_base_table_name => 'ben_acty_base_rt_f',
4395 p_base_key_column => 'plip_id',
4396 p_base_key_value => p_plip_id,
4397 p_from_date => p_validation_start_date,
4398 p_to_date => p_validation_end_date)) Then
4399 l_table_name := 'ben_acty_base_rt_f';
4400 Raise l_rows_exist;
4401 End If;
4402 If (dt_api.rows_exist
4403 (p_base_table_name => 'ben_elig_per_f',
4404 p_base_key_column => 'plip_id',
4405 p_base_key_value => p_plip_id,
4406 p_from_date => p_validation_start_date,
4407 p_to_date => p_validation_end_date)) Then
4411 --
4408 l_table_name := 'ben_elig_per_f';
4409 Raise l_rows_exist;
4410 End If;
4412 End If;
4413 --
4414 hr_utility.set_location(' Leaving:'||l_proc, 10);
4415 Exception
4416 When l_rows_exist Then
4417 --
4418 -- A referential integrity check was violated therefore
4419 -- we must error
4420 --
4421 ben_utility.child_exists_error(p_table_name => l_table_name);
4422 When Others Then
4423 --
4424 -- An unhandled or unexpected error has occurred which
4425 -- we must report
4426 --
4427 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
4428 fnd_message.set_token('PROCEDURE', l_proc);
4429 fnd_message.set_token('STEP','15');
4430 fnd_message.raise_error;
4431 End dt_delete_validate;
4432 --
4433 -- ----------------------------------------------------------------------------
4434 -- |---------------------------< insert_validate >----------------------------|
4435 -- ----------------------------------------------------------------------------
4436 Procedure insert_validate
4437 (p_rec in ben_cpp_shd.g_rec_type,
4438 p_effective_date in date,
4439 p_datetrack_mode in varchar2,
4440 p_validation_start_date in date,
4441 p_validation_end_date in date) is
4442 --
4443 l_proc varchar2(72) := g_package||'insert_validate';
4444 --
4445 Begin
4446 hr_utility.set_location('Entering:'||l_proc, 5);
4447 --
4448 -- Call all supporting business operations
4449 --
4450 --
4451 if p_rec.business_group_id is not null and p_rec.legislation_code is null then
4452 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
4453 end if;
4454 --
4455 chk_plip_id
4456 (p_plip_id => p_rec.plip_id,
4457 p_effective_date => p_effective_date,
4458 p_object_version_number => p_rec.object_version_number);
4459 --
4460 chk_dflt_enrt_det_rl
4461 (p_plip_id => p_rec.plip_id,
4462 p_dflt_enrt_det_rl => p_rec.dflt_enrt_det_rl,
4463 p_effective_date => p_effective_date,
4464 p_object_version_number => p_rec.object_version_number,
4465 p_business_group_id => p_rec.business_group_id);
4466 --
4467 chk_dflt_to_asn_pndg_ctfn_rl
4468 (p_plip_id => p_rec.plip_id,
4469 p_dflt_to_asn_pndg_ctfn_rl => p_rec.dflt_to_asn_pndg_ctfn_rl,
4470 p_effective_date => p_effective_date,
4471 p_object_version_number => p_rec.object_version_number,
4472 p_business_group_id => p_rec.business_group_id);
4473 --
4474 chk_mn_cvg_rl
4475 (p_plip_id => p_rec.plip_id,
4476 p_mn_cvg_rl => p_rec.mn_cvg_rl,
4477 p_effective_date => p_effective_date,
4478 p_object_version_number => p_rec.object_version_number,
4479 p_business_group_id => p_rec.business_group_id);
4480 --
4481 chk_mx_cvg_rl
4482 (p_plip_id => p_rec.plip_id,
4483 p_mx_cvg_rl => p_rec.mx_cvg_rl,
4484 p_effective_date => p_effective_date,
4485 p_object_version_number => p_rec.object_version_number,
4486 p_business_group_id => p_rec.business_group_id);
4487 --
4488
4489 chk_cd_rl_combination
4490 (p_vrfy_fmly_mmbr_cd => p_rec.vrfy_fmly_mmbr_cd,
4491 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl);
4492 --
4493 chk_prort_prtl_yr_cvg_rstrn_rl
4494 (p_plip_id => p_rec.plip_id,
4495 p_prort_prtl_yr_cvg_rstrn_rl => p_rec.prort_prtl_yr_cvg_rstrn_rl,
4496 p_effective_date => p_effective_date,
4497 p_object_version_number => p_rec.object_version_number,
4498 p_business_group_id => p_rec.business_group_id);
4499 --
4500 chk_auto_enrt_mthd_rl
4501 (p_plip_id => p_rec.plip_id,
4502 p_auto_enrt_mthd_rl => p_rec.auto_enrt_mthd_rl,
4503 p_effective_date => p_effective_date,
4504 p_object_version_number => p_rec.object_version_number,
4505 p_business_group_id => p_rec.business_group_id);
4506 --
4507 chk_enrt_rl
4508 (p_plip_id => p_rec.plip_id,
4509 p_enrt_rl => p_rec.enrt_rl,
4510 p_effective_date => p_effective_date,
4511 p_object_version_number => p_rec.object_version_number,
4512 p_business_group_id => p_rec.business_group_id);
4513 --
4514 chk_dflt_enrt_cd
4515 (p_plip_id => p_rec.plip_id,
4516 p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
4517 p_effective_date => p_effective_date,
4518 p_object_version_number => p_rec.object_version_number);
4519 --
4520 chk_dflt_to_asn_pndg_ctfn_cd
4521 (p_plip_id => p_rec.plip_id,
4522 p_dflt_to_asn_pndg_ctfn_cd => p_rec.dflt_to_asn_pndg_ctfn_cd,
4523 p_effective_date => p_effective_date,
4524 p_object_version_number => p_rec.object_version_number);
4525 --
4526 -- Bug 2562196
4527 /*
4528 chk_interim_cd_cvg_calc_mthd
4529 (p_plip_id => p_rec.plip_id,
4530 p_dflt_to_asn_pndg_ctfn_cd => p_rec.dflt_to_asn_pndg_ctfn_cd,
4531 p_pl_id => p_rec.pl_id,
4532 p_effective_date => p_effective_date,
4533 p_business_group_id => p_rec.business_group_id,
4534 p_object_version_number => p_rec.object_version_number);
4535 */
4539 p_unsspnd_enrt_cd => p_rec.unsspnd_enrt_cd,
4536 --
4537 chk_unsspnd_enrt_cd
4538 (p_plip_id => p_rec.plip_id,
4540 p_effective_date => p_effective_date,
4541 p_object_version_number => p_rec.object_version_number);
4542 --
4543 chk_prort_prtl_yr_cvg_rstrn_cd
4544 (p_plip_id => p_rec.plip_id,
4545 p_prort_prtl_yr_cvg_rstrn_cd => p_rec.prort_prtl_yr_cvg_rstrn_cd,
4546 p_effective_date => p_effective_date,
4547 p_object_version_number => p_rec.object_version_number);
4548 --
4549 chk_cvg_incr_r_decr_only_cd
4550 (p_plip_id => p_rec.plip_id,
4551 p_cvg_incr_r_decr_only_cd => p_rec.cvg_incr_r_decr_only_cd,
4552 p_effective_date => p_effective_date,
4553 p_object_version_number => p_rec.object_version_number);
4554 --
4555 chk_bnft_or_option_rstrctn_cd
4556 (p_plip_id => p_rec.plip_id,
4557 p_bnft_or_option_rstrctn_cd => p_rec.bnft_or_option_rstrctn_cd,
4558 p_effective_date => p_effective_date,
4559 p_object_version_number => p_rec.object_version_number);
4560 --
4561 chk_enrt_mthd_cd
4562 (p_plip_id => p_rec.plip_id,
4563 p_enrt_mthd_cd => p_rec.enrt_mthd_cd,
4564 p_effective_date => p_effective_date,
4565 p_object_version_number => p_rec.object_version_number);
4566 --
4567 chk_enrt_cd
4568 (p_plip_id => p_rec.plip_id,
4569 p_enrt_cd => p_rec.enrt_cd,
4570 p_effective_date => p_effective_date,
4571 p_object_version_number => p_rec.object_version_number);
4572 --
4573 chk_plip_stat_cd
4574 (p_plip_id => p_rec.plip_id,
4575 p_plip_stat_cd => p_rec.plip_stat_cd,
4576 p_effective_date => p_effective_date,
4577 p_object_version_number => p_rec.object_version_number);
4578 --
4579 chk_dflt_flag
4580 (p_plip_id => p_rec.plip_id,
4581 p_dflt_flag => p_rec.dflt_flag,
4582 p_effective_date => p_effective_date,
4583 p_object_version_number => p_rec.object_version_number);
4584 --
4585 chk_alws_unrstrctd_enrt_flag
4586 (p_plip_id => p_rec.plip_id,
4587 p_alws_unrstrctd_enrt_flag => p_rec.alws_unrstrctd_enrt_flag,
4588 p_effective_date => p_effective_date,
4589 p_object_version_number => p_rec.object_version_number);
4590 --
4591 chk_no_mn_cvg_amt_apls_flag
4592 (p_plip_id => p_rec.plip_id,
4593 p_no_mn_cvg_amt_apls_flag => p_rec.no_mn_cvg_amt_apls_flag,
4594 p_effective_date => p_effective_date,
4595 p_object_version_number => p_rec.object_version_number);
4596 --
4597 chk_no_mn_cvg_incr_apls_flag
4598 (p_plip_id => p_rec.plip_id,
4599 p_no_mn_cvg_incr_apls_flag => p_rec.no_mn_cvg_incr_apls_flag,
4600 p_effective_date => p_effective_date,
4601 p_object_version_number => p_rec.object_version_number);
4602 --
4603 chk_no_mx_cvg_amt_apls_flag
4604 (p_plip_id => p_rec.plip_id,
4605 p_no_mx_cvg_amt_apls_flag => p_rec.no_mx_cvg_amt_apls_flag,
4606 p_effective_date => p_effective_date,
4607 p_object_version_number => p_rec.object_version_number);
4608 --
4609 chk_no_mx_cvg_incr_apls_flag
4610 (p_plip_id => p_rec.plip_id,
4611 p_no_mx_cvg_incr_apls_flag => p_rec.no_mx_cvg_incr_apls_flag,
4612 p_effective_date => p_effective_date,
4613 p_object_version_number => p_rec.object_version_number);
4614 --
4615 chk_mn_val_mn_flag_mn_rule
4616 (p_mn_cvg_amt => p_rec.mn_cvg_amt,
4617 p_no_mn_cvg_amt_apls_flag => p_rec.no_mn_cvg_amt_apls_flag,
4618 p_mn_cvg_rl => p_rec.mn_cvg_rl);
4619 --
4620 chk_mx_val_mx_flag_mx_rule
4621 (p_mx_cvg_alwd_amt => p_rec.mx_cvg_alwd_amt,
4622 p_no_mx_cvg_amt_apls_flag => p_rec.no_mx_cvg_amt_apls_flag,
4623 p_mx_cvg_rl => p_rec.mx_cvg_rl);
4624 --
4625 chk_all_no_amount_flags
4626 (p_no_mn_cvg_amt_apls_flag => p_rec.no_mn_cvg_amt_apls_flag,
4627 p_mn_cvg_amt => p_rec.mn_cvg_amt,
4628 p_no_mx_cvg_amt_apls_flag => p_rec.no_mx_cvg_amt_apls_flag,
4629 p_mx_cvg_alwd_amt => p_rec.mx_cvg_alwd_amt);
4630 --
4631 chk_duplicate_ordr_num
4632 (p_rec.plip_id
4633 ,p_rec.pgm_id
4634 ,p_rec.ordr_num
4635 ,p_effective_date
4636 ,p_validation_start_date
4637 ,p_validation_end_date
4638 ,p_rec.business_group_id);
4639 --
4640 chk_plan_allowed_in_pgm
4641 (p_rec.pl_id
4642 ,p_effective_date
4643 ,p_rec.business_group_id);
4644 --
4645 chk_dflt_enrt_cd_dpndcy
4646 (p_plip_id => p_rec.plip_id,
4647 p_pgm_id => p_rec.pgm_id,
4648 p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
4649 p_business_group_id => p_rec.business_group_id,
4650 p_effective_date => p_effective_date,
4651 p_object_version_number => p_rec.object_version_number);
4652 --
4653 chk_dflt_dpndcy
4654 (p_plip_id => p_rec.plip_id,
4655 p_pgm_id => p_rec.pgm_id,
4656 p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
4657 p_dflt_enrt_det_rl => p_rec.dflt_enrt_det_rl,
4658 p_dflt_flag => p_rec.dflt_flag,
4659 p_business_group_id => p_rec.business_group_id,
4660 p_effective_date => p_effective_date,
4661 p_object_version_number => p_rec.object_version_number);
4662 --
4666 p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
4663 /*
4664 chk_dflt_flag_dependency
4665 (p_plip_id => p_rec.plip_id,
4667 p_dflt_flag => p_rec.dflt_flag,
4668 p_effective_date => p_effective_date,
4669 p_object_version_number => p_rec.object_version_number);
4670 */
4671 --
4672 chk_dflt_enrt_mthd_dpndcy
4673 (p_plip_id => p_rec.plip_id,
4674 p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
4675 p_dflt_enrt_det_rl => p_rec.dflt_enrt_det_rl,
4676 p_effective_date => p_effective_date,
4677 p_object_version_number => p_rec.object_version_number);
4678 --
4679 chk_duplicate_pl_id_in_pgm(p_pl_id => p_rec.pl_id
4680 ,p_effective_date => p_effective_date
4681 ,p_business_group_id => p_rec.business_group_id
4682 ,p_pgm_id => p_rec.pgm_id
4683 ,p_validation_start_date => p_validation_start_date
4684 ,p_validation_end_date => p_validation_end_date
4685 ,p_plip_id => p_rec.plip_id);
4686 --
4687 chk_vrfy_fmly_mmbr_cd(p_plip_id => p_rec.plip_id ,
4688 p_vrfy_fmly_mmbr_cd => p_rec.vrfy_fmly_mmbr_cd ,
4689 p_effective_date => p_effective_date ,
4690 P_object_version_number =>p_rec.object_version_number);
4691
4692
4693 chk_use_csd_rsd_prccng_cd(p_plip_id => p_rec.plip_id ,
4694 p_use_csd_rsd_prccng_cd => p_rec.use_csd_rsd_prccng_cd ,
4695 p_effective_date => p_effective_date ,
4696 P_object_version_number =>p_rec.object_version_number);
4697
4698 chk_vrfy_fmly_mmbr_rl
4699 (p_plip_id => p_rec.plip_id,
4700 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl,
4701 p_business_group_id => p_rec.business_group_id,
4702 p_effective_date => p_effective_date,
4703 p_object_version_number => p_rec.object_version_number);
4704
4705 chk_invk_imptd_incm_per_pgm
4706 (p_plip_id => p_rec.plip_id,
4707 p_pgm_id => p_rec.pgm_id,
4708 p_pl_id => p_rec.pl_id,
4709 p_business_group_id => p_rec.business_group_id,
4710 p_effective_date => p_effective_date,
4711 p_object_version_number => p_rec.object_version_number);
4712 --
4713 chk_invk_flx_crpl_per_pgm
4714 (p_plip_id => p_rec.plip_id,
4715 p_pgm_id => p_rec.pgm_id,
4716 p_pl_id => p_rec.pl_id,
4717 p_business_group_id => p_rec.business_group_id,
4718 p_effective_date => p_effective_date,
4719 p_object_version_number => p_rec.object_version_number);
4720 --
4721 chk_enrt_cvg_strt_dt_rl
4722 (p_plip_id => p_rec.plip_id,
4723 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
4724 p_effective_date => p_effective_date,
4725 p_object_version_number => p_rec.object_version_number,
4726 p_business_group_id => p_rec.business_group_id);
4727 --
4728 chk_enrt_cvg_end_dt_rl
4729 (p_plip_id => p_rec.plip_id,
4730 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
4731 p_effective_date => p_effective_date,
4732 p_object_version_number => p_rec.object_version_number,
4733 p_business_group_id => p_rec.business_group_id);
4734 --
4735 chk_enrt_cvg_strt_dt_cd
4736 (p_plip_id => p_rec.plip_id,
4737 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
4738 p_effective_date => p_effective_date,
4739 p_object_version_number => p_rec.object_version_number);
4740 --
4741 chk_enrt_cvg_end_dt_cd
4742 (p_plip_id => p_rec.plip_id,
4743 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
4744 p_effective_date => p_effective_date,
4745 p_object_version_number => p_rec.object_version_number);
4746 --
4747 chk_rt_strt_dt_rl
4748 (p_plip_id => p_rec.plip_id,
4749 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
4750 p_effective_date => p_effective_date,
4751 p_object_version_number => p_rec.object_version_number,
4752 p_business_group_id => p_rec.business_group_id);
4753 --
4754 chk_rt_strt_dt_cd
4755 (p_plip_id => p_rec.plip_id,
4756 p_rt_strt_dt_cd => p_rec.rt_strt_dt_cd,
4757 p_effective_date => p_effective_date,
4758 p_object_version_number => p_rec.object_version_number);
4759 --
4760 chk_rt_end_dt_rl
4761 (p_plip_id => p_rec.plip_id,
4762 p_rt_end_dt_rl => p_rec.rt_end_dt_rl,
4763 p_effective_date => p_effective_date,
4764 p_object_version_number => p_rec.object_version_number,
4765 p_business_group_id => p_rec.business_group_id);
4766 --
4767 chk_postelcn_edit_rl
4768 (p_plip_id => p_rec.plip_id,
4769 p_postelcn_edit_rl => p_rec.postelcn_edit_rl,
4770 p_effective_date => p_effective_date,
4771 p_object_version_number => p_rec.object_version_number,
4772 p_business_group_id => p_rec.business_group_id);
4773 --
4774 chk_rt_end_dt_cd
4775 (p_plip_id => p_rec.plip_id,
4776 p_rt_end_dt_cd => p_rec.rt_end_dt_cd,
4777 p_effective_date => p_effective_date,
4778 p_object_version_number => p_rec.object_version_number);
4779 --
4783 p_effective_date => p_effective_date,
4780 chk_drvbl_fctr_apls_rts_flag
4781 (p_plip_id => p_rec.plip_id,
4782 p_drvbl_fctr_apls_rts_flag => p_rec.drvbl_fctr_apls_rts_flag,
4784 p_object_version_number => p_rec.object_version_number);
4785 --
4786 chk_drvbl_fctr_prtn_elig_flag
4787 (p_plip_id => p_rec.plip_id,
4788 p_drvbl_fctr_prtn_elig_flag => p_rec.drvbl_fctr_prtn_elig_flag,
4789 p_effective_date => p_effective_date,
4790 p_object_version_number => p_rec.object_version_number);
4791 --
4792 chk_elig_apls_flag
4793 (p_plip_id => p_rec.plip_id,
4794 p_elig_apls_flag => p_rec.elig_apls_flag,
4795 p_effective_date => p_effective_date,
4796 p_object_version_number => p_rec.object_version_number);
4797 --
4798 chk_prtn_elig_ovrid_alwd_flag
4799 (p_plip_id => p_rec.plip_id,
4800 p_prtn_elig_ovrid_alwd_flag => p_rec.prtn_elig_ovrid_alwd_flag,
4801 p_effective_date => p_effective_date,
4802 p_object_version_number => p_rec.object_version_number);
4803 --
4804 chk_trk_inelig_per_flag
4805 (p_plip_id => p_rec.plip_id,
4806 p_trk_inelig_per_flag => p_rec.trk_inelig_per_flag,
4807 p_effective_date => p_effective_date,
4808 p_object_version_number => p_rec.object_version_number);
4809 --
4810 hr_utility.set_location(' Leaving:'||l_proc, 10);
4811 End insert_validate;
4812 --
4813 -- ----------------------------------------------------------------------------
4814 -- |---------------------------< update_validate >----------------------------|
4815 -- ----------------------------------------------------------------------------
4816 Procedure update_validate
4817 (p_rec in ben_cpp_shd.g_rec_type,
4818 p_effective_date in date,
4819 p_datetrack_mode in varchar2,
4820 p_validation_start_date in date,
4821 p_validation_end_date in date) is
4822 --
4823 l_proc varchar2(72) := g_package||'update_validate';
4824 --
4825 Begin
4826 hr_utility.set_location('Entering:'||l_proc, 5);
4827 --
4828 -- Call all supporting business operations
4829 --
4830 --
4831 if p_rec.business_group_id is not null and p_rec.legislation_code is null then
4832 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
4833 end if;
4834 --
4835 chk_plip_id
4836 (p_plip_id => p_rec.plip_id,
4837 p_effective_date => p_effective_date,
4838 p_object_version_number => p_rec.object_version_number);
4839 --
4840 chk_dflt_enrt_det_rl
4841 (p_plip_id => p_rec.plip_id,
4842 p_dflt_enrt_det_rl => p_rec.dflt_enrt_det_rl,
4843 p_effective_date => p_effective_date,
4844 p_object_version_number => p_rec.object_version_number,
4845 p_business_group_id => p_rec.business_group_id);
4846 --
4847 chk_dflt_to_asn_pndg_ctfn_rl
4848 (p_plip_id => p_rec.plip_id,
4849 p_dflt_to_asn_pndg_ctfn_rl => p_rec.dflt_to_asn_pndg_ctfn_rl,
4850 p_effective_date => p_effective_date,
4851 p_object_version_number => p_rec.object_version_number,
4852 p_business_group_id => p_rec.business_group_id);
4853 --
4854 chk_mn_cvg_rl
4855 (p_plip_id => p_rec.plip_id,
4856 p_mn_cvg_rl => p_rec.mn_cvg_rl,
4857 p_effective_date => p_effective_date,
4858 p_object_version_number => p_rec.object_version_number,
4859 p_business_group_id => p_rec.business_group_id);
4860 --
4861 chk_mx_cvg_rl
4862 (p_plip_id => p_rec.plip_id,
4863 p_mx_cvg_rl => p_rec.mx_cvg_rl,
4864 p_effective_date => p_effective_date,
4865 p_object_version_number => p_rec.object_version_number,
4866 p_business_group_id => p_rec.business_group_id);
4867 --
4868 chk_prort_prtl_yr_cvg_rstrn_rl
4869 (p_plip_id => p_rec.plip_id,
4870 p_prort_prtl_yr_cvg_rstrn_rl => p_rec.prort_prtl_yr_cvg_rstrn_rl,
4871 p_effective_date => p_effective_date,
4872 p_object_version_number => p_rec.object_version_number,
4873 p_business_group_id => p_rec.business_group_id);
4874 --
4875
4876 chk_cd_rl_combination
4877 (p_vrfy_fmly_mmbr_cd => p_rec.vrfy_fmly_mmbr_cd,
4878 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl);
4879 --
4880 chk_auto_enrt_mthd_rl
4881 (p_plip_id => p_rec.plip_id,
4882 p_auto_enrt_mthd_rl => p_rec.auto_enrt_mthd_rl,
4883 p_effective_date => p_effective_date,
4884 p_object_version_number => p_rec.object_version_number,
4885 p_business_group_id => p_rec.business_group_id);
4886 --
4887 chk_enrt_rl
4888 (p_plip_id => p_rec.plip_id,
4889 p_enrt_rl => p_rec.enrt_rl,
4890 p_effective_date => p_effective_date,
4891 p_object_version_number => p_rec.object_version_number,
4892 p_business_group_id => p_rec.business_group_id);
4893 --
4894 chk_dflt_enrt_cd
4895 (p_plip_id => p_rec.plip_id,
4896 p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
4897 p_effective_date => p_effective_date,
4898 p_object_version_number => p_rec.object_version_number);
4899 --
4900
4901 chk_vrfy_fmly_mmbr_cd(p_plip_id => p_rec.plip_id ,
4905 ------
4902 p_vrfy_fmly_mmbr_cd => p_rec.vrfy_fmly_mmbr_cd ,
4903 p_effective_date => p_effective_date ,
4904 P_object_version_number =>p_rec.object_version_number);
4906 chk_use_csd_rsd_prccng_cd(p_plip_id => p_rec.plip_id ,
4907 p_use_csd_rsd_prccng_cd => p_rec.use_csd_rsd_prccng_cd ,
4908 p_effective_date => p_effective_date ,
4909 P_object_version_number =>p_rec.object_version_number);
4910 -----
4911 chk_vrfy_fmly_mmbr_rl
4912 (p_plip_id => p_rec.plip_id,
4913 p_vrfy_fmly_mmbr_rl => p_rec.vrfy_fmly_mmbr_rl,
4914 p_business_group_id => p_rec.business_group_id,
4915 p_effective_date => p_effective_date,
4916 p_object_version_number => p_rec.object_version_number);
4917
4918
4919 --
4920 chk_dflt_to_asn_pndg_ctfn_cd
4921 (p_plip_id => p_rec.plip_id,
4922 p_dflt_to_asn_pndg_ctfn_cd => p_rec.dflt_to_asn_pndg_ctfn_cd,
4923 p_effective_date => p_effective_date,
4924 p_object_version_number => p_rec.object_version_number);
4925 -- Bug 2562196
4926 /*
4927 chk_interim_cd_cvg_calc_mthd
4928 (p_plip_id => p_rec.plip_id,
4929 p_dflt_to_asn_pndg_ctfn_cd => p_rec.dflt_to_asn_pndg_ctfn_cd,
4930 p_pl_id => p_rec.pl_id,
4931 p_effective_date => p_effective_date,
4932 p_business_group_id => p_rec.business_group_id,
4933 p_object_version_number => p_rec.object_version_number);
4934 */
4935 --
4936 chk_unsspnd_enrt_cd
4937 (p_plip_id => p_rec.plip_id,
4938 p_unsspnd_enrt_cd => p_rec.unsspnd_enrt_cd,
4939 p_effective_date => p_effective_date,
4940 p_object_version_number => p_rec.object_version_number);
4941 --
4942 chk_prort_prtl_yr_cvg_rstrn_cd
4943 (p_plip_id => p_rec.plip_id,
4944 p_prort_prtl_yr_cvg_rstrn_cd => p_rec.prort_prtl_yr_cvg_rstrn_cd,
4945 p_effective_date => p_effective_date,
4946 p_object_version_number => p_rec.object_version_number);
4947 --
4948 chk_cvg_incr_r_decr_only_cd
4949 (p_plip_id => p_rec.plip_id,
4950 p_cvg_incr_r_decr_only_cd => p_rec.cvg_incr_r_decr_only_cd,
4951 p_effective_date => p_effective_date,
4952 p_object_version_number => p_rec.object_version_number);
4953 --
4954 chk_bnft_or_option_rstrctn_cd
4955 (p_plip_id => p_rec.plip_id,
4956 p_bnft_or_option_rstrctn_cd => p_rec.bnft_or_option_rstrctn_cd,
4957 p_effective_date => p_effective_date,
4958 p_object_version_number => p_rec.object_version_number);
4959 --
4960 chk_enrt_mthd_cd
4961 (p_plip_id => p_rec.plip_id,
4962 p_enrt_mthd_cd => p_rec.enrt_mthd_cd,
4963 p_effective_date => p_effective_date,
4964 p_object_version_number => p_rec.object_version_number);
4965 --
4966 chk_enrt_cd
4967 (p_plip_id => p_rec.plip_id,
4968 p_enrt_cd => p_rec.enrt_cd,
4969 p_effective_date => p_effective_date,
4970 p_object_version_number => p_rec.object_version_number);
4971 --
4972 chk_plip_stat_cd
4973 (p_plip_id => p_rec.plip_id,
4974 p_plip_stat_cd => p_rec.plip_stat_cd,
4975 p_effective_date => p_effective_date,
4976 p_object_version_number => p_rec.object_version_number);
4977 --
4978 chk_dflt_flag
4979 (p_plip_id => p_rec.plip_id,
4980 p_dflt_flag => p_rec.dflt_flag,
4981 p_effective_date => p_effective_date,
4982 p_object_version_number => p_rec.object_version_number);
4983 --
4984 chk_alws_unrstrctd_enrt_flag
4985 (p_plip_id => p_rec.plip_id,
4986 p_alws_unrstrctd_enrt_flag => p_rec.alws_unrstrctd_enrt_flag,
4987 p_effective_date => p_effective_date,
4988 p_object_version_number => p_rec.object_version_number);
4989 --
4990 chk_no_mn_cvg_amt_apls_flag
4991 (p_plip_id => p_rec.plip_id,
4992 p_no_mn_cvg_amt_apls_flag => p_rec.no_mn_cvg_amt_apls_flag,
4993 p_effective_date => p_effective_date,
4994 p_object_version_number => p_rec.object_version_number);
4995 --
4996 chk_no_mn_cvg_incr_apls_flag
4997 (p_plip_id => p_rec.plip_id,
4998 p_no_mn_cvg_incr_apls_flag => p_rec.no_mn_cvg_incr_apls_flag,
4999 p_effective_date => p_effective_date,
5000 p_object_version_number => p_rec.object_version_number);
5001 --
5002 chk_no_mx_cvg_amt_apls_flag
5003 (p_plip_id => p_rec.plip_id,
5004 p_no_mx_cvg_amt_apls_flag => p_rec.no_mx_cvg_amt_apls_flag,
5005 p_effective_date => p_effective_date,
5006 p_object_version_number => p_rec.object_version_number);
5007 --
5008 chk_no_mx_cvg_incr_apls_flag
5009 (p_plip_id => p_rec.plip_id,
5010 p_no_mx_cvg_incr_apls_flag => p_rec.no_mx_cvg_incr_apls_flag,
5011 p_effective_date => p_effective_date,
5012 p_object_version_number => p_rec.object_version_number);
5013 --
5014 chk_mn_val_mn_flag_mn_rule
5015 (p_mn_cvg_amt => p_rec.mn_cvg_amt,
5016 p_no_mn_cvg_amt_apls_flag => p_rec.no_mn_cvg_amt_apls_flag,
5017 p_mn_cvg_rl => p_rec.mn_cvg_rl);
5018 --
5019 chk_mx_val_mx_flag_mx_rule
5020 (p_mx_cvg_alwd_amt => p_rec.mx_cvg_alwd_amt,
5021 p_no_mx_cvg_amt_apls_flag => p_rec.no_mx_cvg_amt_apls_flag,
5025 (p_no_mn_cvg_amt_apls_flag => p_rec.no_mn_cvg_amt_apls_flag,
5022 p_mx_cvg_rl => p_rec.mx_cvg_rl);
5023 --
5024 chk_all_no_amount_flags
5026 p_mn_cvg_amt => p_rec.mn_cvg_amt,
5027 p_no_mx_cvg_amt_apls_flag => p_rec.no_mx_cvg_amt_apls_flag,
5028 p_mx_cvg_alwd_amt => p_rec.mx_cvg_alwd_amt);
5029 --
5030 --
5031 chk_duplicate_ordr_num
5032 (p_rec.plip_id
5033 ,p_rec.pgm_id
5034 ,p_rec.ordr_num
5035 ,p_effective_date
5036 ,p_validation_start_date
5037 ,p_validation_end_date
5038 ,p_rec.business_group_id);
5039 --
5040 chk_plan_allowed_in_pgm
5041 (p_rec.pl_id
5042 ,p_effective_date
5043 ,p_rec.business_group_id);
5044 --
5045 chk_dflt_enrt_cd_dpndcy
5046 (p_plip_id => p_rec.plip_id,
5047 p_pgm_id => p_rec.pgm_id,
5048 p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
5049 p_business_group_id => p_rec.business_group_id,
5050 p_effective_date => p_effective_date,
5051 p_object_version_number => p_rec.object_version_number);
5052 --
5053 chk_dflt_dpndcy
5054 (p_plip_id => p_rec.plip_id,
5055 p_pgm_id => p_rec.pgm_id,
5056 p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
5057 p_dflt_enrt_det_rl => p_rec.dflt_enrt_det_rl,
5058 p_dflt_flag => p_rec.dflt_flag,
5059 p_business_group_id => p_rec.business_group_id,
5060 p_effective_date => p_effective_date,
5061 p_object_version_number => p_rec.object_version_number);
5062 --
5063 /*
5064 chk_dflt_flag_dependency
5065 (p_plip_id => p_rec.plip_id,
5066 p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
5067 p_dflt_flag => p_rec.dflt_flag,
5068 p_effective_date => p_effective_date,
5069 p_object_version_number => p_rec.object_version_number);
5070 */
5071 --
5072 chk_dflt_enrt_mthd_dpndcy
5073 (p_plip_id => p_rec.plip_id,
5074 p_dflt_enrt_cd => p_rec.dflt_enrt_cd,
5075 p_dflt_enrt_det_rl => p_rec.dflt_enrt_det_rl,
5076 p_effective_date => p_effective_date,
5077 p_object_version_number => p_rec.object_version_number);
5078 --
5079 chk_duplicate_pl_id_in_pgm
5080 (p_pl_id => p_rec.pl_id
5081 ,p_effective_date => p_effective_date
5082 ,p_business_group_id => p_rec.business_group_id
5083 ,p_pgm_id => p_rec.pgm_id
5084 ,p_validation_start_date => p_validation_start_date
5085 ,p_validation_end_date => p_validation_end_date
5086 ,p_plip_id => p_rec.plip_id);
5087 --
5088 chk_invk_imptd_incm_per_pgm
5089 (p_plip_id => p_rec.plip_id,
5090 p_pgm_id => p_rec.pgm_id,
5091 p_pl_id => p_rec.pl_id,
5092 p_business_group_id => p_rec.business_group_id,
5093 p_effective_date => p_effective_date,
5094 p_object_version_number => p_rec.object_version_number);
5095 --
5096 chk_invk_flx_crpl_per_pgm
5097 (p_plip_id => p_rec.plip_id,
5098 p_pgm_id => p_rec.pgm_id,
5099 p_pl_id => p_rec.pl_id,
5100 p_business_group_id => p_rec.business_group_id,
5101 p_effective_date => p_effective_date,
5102 p_object_version_number => p_rec.object_version_number);
5103 --
5104 chk_enrt_cvg_strt_dt_rl
5105 (p_plip_id => p_rec.plip_id,
5106 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
5107 p_effective_date => p_effective_date,
5108 p_object_version_number => p_rec.object_version_number,
5109 p_business_group_id => p_rec.business_group_id);
5110 --
5111 chk_enrt_cvg_end_dt_rl
5112 (p_plip_id => p_rec.plip_id,
5113 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
5114 p_effective_date => p_effective_date,
5115 p_object_version_number => p_rec.object_version_number,
5116 p_business_group_id => p_rec.business_group_id);
5117 --
5118 chk_enrt_cvg_strt_dt_cd
5119 (p_plip_id => p_rec.plip_id,
5120 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
5121 p_effective_date => p_effective_date,
5122 p_object_version_number => p_rec.object_version_number);
5123 --
5124 chk_enrt_cvg_end_dt_cd
5125 (p_plip_id => p_rec.plip_id,
5126 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
5127 p_effective_date => p_effective_date,
5128 p_object_version_number => p_rec.object_version_number);
5129 --
5130 chk_rt_strt_dt_rl
5131 (p_plip_id => p_rec.plip_id,
5132 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
5133 p_effective_date => p_effective_date,
5134 p_object_version_number => p_rec.object_version_number,
5135 p_business_group_id => p_rec.business_group_id);
5136 --
5137 chk_rt_strt_dt_cd
5138 (p_plip_id => p_rec.plip_id,
5139 p_rt_strt_dt_cd => p_rec.rt_strt_dt_cd,
5140 p_effective_date => p_effective_date,
5141 p_object_version_number => p_rec.object_version_number);
5142 --
5143 chk_rt_end_dt_rl
5144 (p_plip_id => p_rec.plip_id,
5145 p_rt_end_dt_rl => p_rec.rt_end_dt_rl,
5146 p_effective_date => p_effective_date,
5147 p_object_version_number => p_rec.object_version_number,
5148 p_business_group_id => p_rec.business_group_id);
5149 --
5150 chk_postelcn_edit_rl
5151 (p_plip_id => p_rec.plip_id,
5152 p_postelcn_edit_rl => p_rec.postelcn_edit_rl,
5153 p_effective_date => p_effective_date,
5154 p_object_version_number => p_rec.object_version_number,
5155 p_business_group_id => p_rec.business_group_id);
5156 --
5157 chk_rt_end_dt_cd
5158 (p_plip_id => p_rec.plip_id,
5159 p_rt_end_dt_cd => p_rec.rt_end_dt_cd,
5160 p_effective_date => p_effective_date,
5161 p_object_version_number => p_rec.object_version_number);
5162 --
5163 chk_drvbl_fctr_apls_rts_flag
5164 (p_plip_id => p_rec.plip_id,
5165 p_drvbl_fctr_apls_rts_flag => p_rec.drvbl_fctr_apls_rts_flag,
5166 p_effective_date => p_effective_date,
5167 p_object_version_number => p_rec.object_version_number);
5168 --
5169 chk_drvbl_fctr_prtn_elig_flag
5170 (p_plip_id => p_rec.plip_id,
5171 p_drvbl_fctr_prtn_elig_flag => p_rec.drvbl_fctr_prtn_elig_flag,
5172 p_effective_date => p_effective_date,
5173 p_object_version_number => p_rec.object_version_number);
5174 --
5175 chk_elig_apls_flag
5176 (p_plip_id => p_rec.plip_id,
5177 p_elig_apls_flag => p_rec.elig_apls_flag,
5178 p_effective_date => p_effective_date,
5179 p_object_version_number => p_rec.object_version_number);
5180 --
5181 chk_prtn_elig_ovrid_alwd_flag
5182 (p_plip_id => p_rec.plip_id,
5186 --
5183 p_prtn_elig_ovrid_alwd_flag => p_rec.prtn_elig_ovrid_alwd_flag,
5184 p_effective_date => p_effective_date,
5185 p_object_version_number => p_rec.object_version_number);
5187 chk_trk_inelig_per_flag
5188 (p_plip_id => p_rec.plip_id,
5189 p_trk_inelig_per_flag => p_rec.trk_inelig_per_flag,
5190 p_effective_date => p_effective_date,
5191 p_object_version_number => p_rec.object_version_number);
5192 --
5193 -- Call the datetrack update integrity operation
5194 --
5195 dt_update_validate
5196 (p_dflt_enrt_det_rl => p_rec.dflt_enrt_det_rl,
5197 p_pl_id => p_rec.pl_id,
5198 p_pgm_id => p_rec.pgm_id,
5199 p_datetrack_mode => p_datetrack_mode,
5200 p_validation_start_date => p_validation_start_date,
5201 p_validation_end_date => p_validation_end_date);
5202 --
5203 hr_utility.set_location(' Leaving:'||l_proc, 10);
5204 End update_validate;
5205 --
5206 -- ----------------------------------------------------------------------------
5207 -- |---------------------------< delete_validate >----------------------------|
5208 -- ----------------------------------------------------------------------------
5209 Procedure delete_validate
5210 (p_rec in ben_cpp_shd.g_rec_type,
5211 p_effective_date in date,
5212 p_datetrack_mode in varchar2,
5213 p_validation_start_date in date,
5214 p_validation_end_date in date) is
5215 --
5216 l_proc varchar2(72) := g_package||'delete_validate';
5217 --
5218 Begin
5219 hr_utility.set_location('Entering:'||l_proc, 5);
5220 --
5221 -- Call all supporting business operations
5222 --
5223 dt_delete_validate
5224 (p_datetrack_mode => p_datetrack_mode,
5225 p_validation_start_date => p_validation_start_date,
5226 p_validation_end_date => p_validation_end_date,
5227 p_plip_id => p_rec.plip_id);
5228
5229 --bug 3966957
5230 chk_plan_delete_in_pgm(p_plip_id => p_rec.plip_id,
5231 p_validation_start_date => p_validation_start_date ,
5232 p_validation_end_date => p_validation_end_date,
5233 p_effective_date => p_effective_date
5234 );
5235
5236
5237 --
5238 hr_utility.set_location(' Leaving:'||l_proc, 10);
5239 End delete_validate;
5240 --
5241 --
5242 -- ---------------------------------------------------------------------------
5243 -- |---------------------< return_legislation_code >-------------------------|
5244 -- ---------------------------------------------------------------------------
5245 --
5246 function return_legislation_code
5247 (p_plip_id in number) return varchar2 is
5248 --
5249 -- Declare cursor
5250 --
5251 cursor csr_leg_code is
5252 select a.legislation_code
5253 from per_business_groups a,
5254 ben_plip_f b
5255 where b.plip_id = p_plip_id
5256 and a.business_group_id = b.business_group_id;
5257 --
5258 -- Declare local variables
5259 --
5260 l_legislation_code varchar2(150);
5261 l_proc varchar2(72) := g_package||'return_legislation_code';
5262 --
5263 begin
5264 --
5265 hr_utility.set_location('Entering:'|| l_proc, 10);
5266 --
5267 -- Ensure that all the mandatory parameter are not null
5268 --
5269 hr_api.mandatory_arg_error(p_api_name => l_proc,
5270 p_argument => 'plip_id',
5271 p_argument_value => p_plip_id);
5272 --
5273 open csr_leg_code;
5274 --
5275 fetch csr_leg_code into l_legislation_code;
5276 --
5277 if csr_leg_code%notfound then
5278 --
5279 close csr_leg_code;
5280 --
5281 -- The primary key is invalid therefore we must error
5282 --
5283 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
5284 fnd_message.raise_error;
5285 --
5286 end if;
5287 --
5288 close csr_leg_code;
5289 --
5290 hr_utility.set_location(' Leaving:'|| l_proc, 20);
5291 --
5292 return l_legislation_code;
5293
5294 --
5295 end return_legislation_code;
5296
5297 --
5298 end ben_cpp_bus;