[Home] [Help]
PACKAGE BODY: APPS.BEN_ENP_BUS
Source
6 -- ----------------------------------------------------------------------------
1 Package Body ben_enp_bus as
2 /* $Header: beenprhi.pkb 120.4.12020000.2 2012/07/03 12:09:48 amnaraya ship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
7 --
8 g_package varchar2(33) := ' ben_enp_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_enrt_perd_id >------|
12 -- ----------------------------------------------------------------------------
13 --
14 -- Description
15 -- This procedure is used to check that the primary key for the table
16 -- is created properly. It should be null on insert and
17 -- should not be able to be updated.
18 --
19 -- Pre Conditions
20 -- None.
21 --
22 -- In Parameters
23 -- enrt_perd_id PK of record being inserted or updated.
24 -- object_version_number Object version number of record being
25 -- inserted or updated.
26 --
27 -- Post Success
28 -- Processing continues
29 --
30 -- Post Failure
31 -- Errors handled by the procedure
32 --
33 -- Access Status
34 -- Internal table handler use only.
35 --
36 Procedure chk_enrt_perd_id(p_enrt_perd_id in number,
37 p_object_version_number in number) is
38 --
39 l_proc varchar2(72) := g_package||'chk_enrt_perd_id';
40 l_api_updating boolean;
41 --
42 Begin
43 --
44 hr_utility.set_location('Entering:'||l_proc, 5);
45 --
46 l_api_updating := ben_enp_shd.api_updating
47 (p_enrt_perd_id => p_enrt_perd_id,
48 p_object_version_number => p_object_version_number);
49 --
50 if (l_api_updating
51 and nvl(p_enrt_perd_id,hr_api.g_number)
52 <> ben_enp_shd.g_old_rec.enrt_perd_id) then
53 --
54 -- raise error as PK has changed
55 --
56 ben_enp_shd.constraint_error('BEN_ENRT_PERD_PK');
57 --
58 elsif not l_api_updating then
59 --
60 -- check if PK is null
61 --
62 if p_enrt_perd_id is not null then
63 --
64 -- raise error as PK is not null
65 --
66 ben_enp_shd.constraint_error('BEN_ENRT_PERD_PK');
67 --
68 end if;
69 --
70 end if;
71 --
72 hr_utility.set_location('Leaving:'||l_proc, 10);
73 --
74 End chk_enrt_perd_id;
75 --
76 -- ----------------------------------------------------------------------------
77 -- |------< chk_popl_enrt_typ_cycl_id >------|
78 -- ----------------------------------------------------------------------------
79 --
80 -- Description
81 -- This procedure checks that a referenced foreign key actually exists
82 -- in the referenced table.
83 --
84 -- Pre-Conditions
85 -- None.
86 --
87 -- In Parameters
88 -- p_enrt_perd_id PK
89 -- p_popl_enrt_typ_cycl_id ID of FK column
90 -- p_effective_date Session Date of record
91 -- p_object_version_number object version number
92 --
93 -- Post Success
94 -- Processing continues
95 --
96 -- Post Failure
97 -- Error raised.
98 --
99 -- Access Status
100 -- Internal table handler use only.
101 --
102 Procedure chk_popl_enrt_typ_cycl_id (p_enrt_perd_id in number,
103 p_popl_enrt_typ_cycl_id in number,
104 p_effective_date in date,
105 p_object_version_number in number) is
106 --
107 l_proc varchar2(72) := g_package||'chk_popl_enrt_typ_cycl_id';
108 l_api_updating boolean;
109 l_dummy varchar2(1);
110 --
111 cursor c1 is
112 select null
113 from ben_popl_enrt_typ_cycl_f a
114 where a.popl_enrt_typ_cycl_id = p_popl_enrt_typ_cycl_id
115 and p_effective_date
116 between a.effective_start_date
117 and a.effective_end_date;
118 --
119 Begin
120 --
121 hr_utility.set_location('Entering:'||l_proc,5);
122 --
123 l_api_updating := ben_enp_shd.api_updating
124 (p_enrt_perd_id => p_enrt_perd_id,
125 p_object_version_number => p_object_version_number);
126 --
127 if (l_api_updating
128 and nvl(p_popl_enrt_typ_cycl_id,hr_api.g_number)
129 <> nvl(ben_enp_shd.g_old_rec.popl_enrt_typ_cycl_id,hr_api.g_number)
130 or not l_api_updating) then
131 --
132 -- check if popl_enrt_typ_cycl_id value exists in ben_popl_enrt_typ_cycl_f table
133 --
134 open c1;
135 --
136 fetch c1 into l_dummy;
137 if c1%notfound then
138 --
139 close c1;
140 --
141 -- raise error as FK does not relate to PK in ben_popl_enrt_typ_cycl_f
142 -- table.
143 --
144 ben_enp_shd.constraint_error('BEN_ENRT_PERD_DT1');
145 --
146 end if;
147 --
148 close c1;
152 hr_utility.set_location('Leaving:'||l_proc,10);
149 --
150 end if;
151 --
153 --
154 End chk_popl_enrt_typ_cycl_id;
155 --
156 -- ----------------------------------------------------------------------------
157 -- |------< chk_yr_perd_id >------|
158 -- ----------------------------------------------------------------------------
159 --
160 -- Description
161 -- This procedure checks that a referenced foreign key actually exists
162 -- in the referenced table.
163 --
164 -- Pre-Conditions
165 -- None.
166 --
167 -- In Parameters
168 -- p_enrt_perd_id PK
169 -- p_yr_perd_id ID of FK column
170 -- p_object_version_number object version number
171 --
172 -- Post Success
173 -- Processing continues
174 --
175 -- Post Failure
176 -- Error raised.
177 --
178 -- Access Status
179 -- Internal table handler use only.
180 --
181 Procedure chk_yr_perd_id (p_enrt_perd_id in number,
182 p_yr_perd_id in number,
183 p_popl_enrt_typ_cycl_id in number,
184 p_business_group_id in number,
185 p_object_version_number in number) is
186 --
187 l_proc varchar2(72) := g_package||'chk_yr_perd_id';
188 l_api_updating boolean;
189 l_dummy varchar2(1);
190 l_exists varchar2(1);
191
192 --
193 cursor c1 is
194 select null
195 from ben_yr_perd a
199 cursor chk_unique is
196 where a.yr_perd_id = p_yr_perd_id;
197 --
198 /*
200 select null
201 from ben_enrt_perd
202 where yr_perd_id = p_yr_perd_id
203 and enrt_perd_id <> nvl(p_enrt_perd_id, hr_api.g_number)
204 and popl_enrt_typ_cycl_id = p_popl_enrt_typ_cycl_id
205 and business_group_id + 0 = p_business_group_id;
206 */
207 -- Commented the above cursor and its use below so as to fix the bug #2103
208 -- and to allow a period to be used more than once and have different
209 -- enrollment periods in them.
210 Begin
211 --
212 hr_utility.set_location('Entering:'||l_proc,5);
213 --
214 l_api_updating := ben_enp_shd.api_updating
215 (p_enrt_perd_id => p_enrt_perd_id,
216 p_object_version_number => p_object_version_number);
217 --
218 if (l_api_updating
219 and nvl(p_yr_perd_id,hr_api.g_number)
220 <> nvl(ben_enp_shd.g_old_rec.yr_perd_id,hr_api.g_number)
221 or not l_api_updating) then
222 --
223 -- check if yr_perd_id value exists in ben_yr_perd table
224 --
225 open c1;
226 --
227 fetch c1 into l_dummy;
228 if c1%notfound then
229 --
230 close c1;
231 --
232 -- raise error as FK does not relate to PK in ben_yr_perd
233 -- table.
234 --
235 ben_enp_shd.constraint_error('BEN_ENRT_PERD_FK1');
236 --
237 end if;
238 --
239 close c1;
240 --
241 /*
242 open chk_unique;
243 --
244 fetch chk_unique into l_exists;
245 if chk_unique%found then
246 close chk_unique;
247 --
248 -- raise error as UK1 is violated
249 --
250 fnd_message.set_name('PAY','VALUE IS NOT UNIQUE');
251 fnd_message.raise_error;
252 --
253 end if;
254 --
255 close chk_unique;
256 --
257 */
258 end if;
259 --
260 hr_utility.set_location('Leaving:'||l_proc,10);
261 --
262 End chk_yr_perd_id;
263 --
264 -- ----------------------------------------------------------------------------
265 -- |------< chk_end_dt >------|
266 -- ----------------------------------------------------------------------------
267 --
268 -- Description
269 -- This procedure is used to check that end_dt > start_dt.
270 --
271 -- Pre Conditions
272 -- None.
273 --
274 -- In Parameters
275 -- enrt_perd_id PK of record being inserted or updated.
276 -- end date
277 -- start date
278 -- object_version_number Object version number of record being
279 -- inserted or updated.
280 --
281 -- Post Success
282 -- Processing continues
283 --
284 -- Post Failure
285 -- Error handled by procedure
286 --
287 -- Access Status
288 -- Internal table handler use only.
289 --
290 Procedure chk_end_dt(p_enrt_perd_id in number,
294 p_object_version_number in number) is
291 p_strt_dt in date,
292 p_end_dt in date,
293 p_asnd_lf_evt_dt in date,
295 --
296 l_proc varchar2(72) := g_package||'chk_end_dt';
297 l_api_updating boolean;
298 --
299 Begin
300 --
301 hr_utility.set_location('Entering:'||l_proc, 5);
302 --
303 hr_api.mandatory_arg_error(p_api_name => l_proc,
304 p_argument => 'strt_dt',
305 p_argument_value => p_strt_dt);
306 --
307 hr_api.mandatory_arg_error(p_api_name => l_proc,
308 p_argument => 'end_dt',
309 p_argument_value => p_end_dt);
310 --
311 hr_api.mandatory_arg_error(p_api_name => l_proc,
312 p_argument => 'asnd_lf_evt_dt',
313 p_argument_value => p_asnd_lf_evt_dt);
314
315 ---
316 l_api_updating := ben_enp_shd.api_updating
317 (p_enrt_perd_id => p_enrt_perd_id,
318 p_object_version_number => p_object_version_number);
319 --
320 -- check it if changing either start or end date.
321 --
322 if (l_api_updating
323 and (p_strt_dt <> nvl(ben_enp_shd.g_old_rec.strt_dt,hr_api.g_date) or
324 p_end_dt <> nvl(ben_enp_shd.g_old_rec.end_dt,hr_api.g_date)
325 /* or p_asnd_lf_evt_Dt <> nvl(ben_enp_shd.g_old_rec.asnd_lf_Evt_dt ,hr_api.g_date) */
326 )
327 or not l_api_updating)
328 then
329 --
330 -- check if value of lookup falls within lookup type.
331 --
332 if p_strt_dt > p_end_dt then
333 --
334 -- raise error
335 --
336 fnd_message.set_name('BEN','BEN_92979_INVLD_ENRT_END_DT');
337 fnd_message.raise_error;
338 --
339 end if;
340 --
341 end if;
342 -- chek for asnd_lf_evt_dt is to be added here
343 --
344 hr_utility.set_location('Leaving:'||l_proc,10);
345 --
346 end chk_end_dt;
347 --
348 -- ----------------------------------------------------------------------------
349 -- |---------------------< chk_dup_asnd_lf_evt_dt >---------------------------|
350 -- ----------------------------------------------------------------------------
351 --
352 -- Description
353 -- This procedure is used to check that there is no duplicate asnd_lf_evt_dt
354 -- for a given popl_enrt_typ_cycl_id. This has been added as part of bug fix
355 -- 2206551.
356 --
357 -- Pre Conditions
358 -- None.
359 --
360 -- In Parameters
361 -- enrt_perd_id PK of record being inserted or updated.
362 -- asnd_lf_evt_dt Assigned life event date
363 -- popl_enrt_typ_cycl_id Plan/Program Enrollment Type Cycle Id
364 -- object_version_number Object version number of record being
365 -- inserted or updated.
366 --
367 -- Post Success
368 -- Processing continues
369 --
370 -- Post Failure
371 -- Error handled by procedure
372 --
373 -- Access Status
374 -- Internal table handler use only.
375 --
376 Procedure chk_dup_asnd_lf_evt_dt(p_enrt_perd_id in number,
377 p_asnd_lf_evt_dt in date,
378 p_popl_enrt_typ_cycl_id in number,
379 p_business_group_id in number,
380 p_object_version_number in number) is
381 --
382 l_proc varchar2(72) := g_package||'chk_dup_asnd_lf_evt_dt';
383 l_api_updating boolean;
384 l_dummy char;
385 --
386 cursor c_dup_enp is
387 select null
388 from ben_enrt_perd enp
389 where enp.popl_enrt_typ_cycl_id = p_popl_enrt_typ_cycl_id
390 and enp.enrt_perd_id <> nvl(p_enrt_perd_id, hr_api.g_number)
391 and enp.asnd_lf_evt_dt = p_asnd_lf_evt_dt
392 and enp.business_group_id = p_business_group_id ;
393 Begin
394 --
395 hr_utility.set_location('Entering:'||l_proc, 5);
396 --
397 hr_api.mandatory_arg_error(p_api_name => l_proc,
398 p_argument => 'asnd_lf_evt_dt',
399 p_argument_value => p_asnd_lf_evt_dt);
400
401 ---
402 l_api_updating := ben_enp_shd.api_updating
403 (p_enrt_perd_id => p_enrt_perd_id,
404 p_object_version_number => p_object_version_number);
405 --
406 -- check it if changing assigned life event date.
407 --
408 if (l_api_updating
409 and (p_asnd_lf_evt_dt <> nvl(ben_enp_shd.g_old_rec.asnd_lf_evt_dt,hr_api.g_date) )
410 or not l_api_updating)
411 then
412 --
413 -- check if there is another enrollment period record with same assigned life event date
414 --
415 open c_dup_enp ;
416 fetch c_dup_enp into l_dummy ;
417 if c_dup_enp%found then
418 --
419 -- raise error
420 --
421 fnd_message.set_name('BEN','BEN_93231_DUP_ASND_LF_EVT_DT');
422 fnd_message.raise_error;
423 --
424 end if;
425 --
426 end if;
427 -- chek for asnd_lf_evt_dt is to be added here
428 --
429 hr_utility.set_location('Leaving:'||l_proc,10);
430 --
431 end chk_dup_asnd_lf_evt_dt;
432 --
433 -- ----------------------------------------------------------------------------
434 -- |------< chk_dflt_enrt_dt >------|
435 -- ----------------------------------------------------------------------------
436 --
437 -- Description
441 -- None.
438 -- This procedure is used to check that dflt_enrt_dt > end_dt.
439 --
440 -- Pre Conditions
442 --
443 -- In Parameters
444 -- enrt_perd_id PK of record being inserted or updated.
445 -- end date
446 -- default enrollment date
447 -- object_version_number Object version number of record being
448 -- inserted or updated.
449 --
450 -- Post Success
451 -- Processing continues
452 --
453 -- Post Failure
454 -- Error handled by procedure
455 --
456 -- Access Status
457 -- Internal table handler use only.
458 --
459 Procedure chk_dflt_enrt_dt(p_enrt_perd_id in number,
460 p_dflt_enrt_dt in date,
461 p_end_dt in date,
462 p_object_version_number in number) is
463 --
464 l_proc varchar2(72) := g_package||'chk_dflt_enrt_dt';
465 l_api_updating boolean;
466 --
467 Begin
468 --
469 hr_utility.set_location('Entering:'||l_proc, 5);
470 --
471 /* hr_api.mandatory_arg_error(p_api_name => l_proc,
472 p_argument => 'dflt_enrt_dt',
473 p_argument_value => p_dflt_enrt_dt);
474 */
475 --
476 hr_api.mandatory_arg_error(p_api_name => l_proc,
477 p_argument => 'end_dt',
478 p_argument_value => p_end_dt);
479 --
480 l_api_updating := ben_enp_shd.api_updating
481 (p_enrt_perd_id => p_enrt_perd_id,
482 p_object_version_number => p_object_version_number);
483 --
484 -- check it if changing either default enrollment date or end date.
485 --
486 if (l_api_updating
487 and (p_dflt_enrt_dt <> nvl(ben_enp_shd.g_old_rec.dflt_enrt_dt,hr_api.g_date) or
488 p_end_dt <> nvl(ben_enp_shd.g_old_rec.end_dt,hr_api.g_date)
489 )
490 or not l_api_updating)
491 then
492 --
493 -- check if value of lookup falls within lookup type.
494 --
495 if p_end_dt > p_dflt_enrt_dt then
496 --
497 -- raise error
498 --
499 fnd_message.set_name('PAY','end date > dflt enrt date');
500 fnd_message.raise_error;
501 --
502 end if;
503 --
504 end if;
505 --
506 hr_utility.set_location('Leaving:'||l_proc,10);
507 --
508 end chk_dflt_enrt_dt;
509 --
510 -- ----------------------------------------------------------------------------
511 -- |------< chk_rt_end_dt_rl >------|
512 -- ----------------------------------------------------------------------------
513 --
514 -- Description
515 -- This procedure is used to check that the Formula Rule is valid.
516 --
517 -- Pre Conditions
518 -- None.
519 --
520 -- In Parameters
521 -- enrt_perd_id PK of record being inserted or updated.
522 -- rt_end_dt_rl Value of formula rule id.
523 -- effective_date effective date
524 -- object_version_number Object version number of record being
525 -- inserted or updated.
526 --
527 -- Post Success
528 -- Processing continues
529 --
530 -- Post Failure
531 -- Error handled by procedure
532 --
533 -- Access Status
534 -- Internal table handler use only.
535 --
536 Procedure chk_rt_end_dt_rl(p_enrt_perd_id in number,
537 p_rt_end_dt_rl in number,
538 p_business_group_id in number,
539 p_effective_date in date,
540 p_object_version_number in number) is
541 --
542 l_proc varchar2(72) := g_package||'chk_rt_end_dt_rl';
543 l_api_updating boolean;
544 l_dummy varchar2(1);
545 --
546 cursor c1 is
547 select null
548 from ff_formulas_f ff ,
549 per_business_groups pbg
550 where ff.formula_id = p_rt_end_dt_rl
551 and ff.formula_type_id = -67 /*default enrollment det */
552 and pbg.business_group_id = p_business_group_id
553 and nvl(ff.business_group_id, p_business_group_id) =
554 p_business_group_id
555 and nvl(ff.legislation_code, pbg.legislation_code) =
556 pbg.legislation_code
557 and p_effective_date
558 between ff.effective_start_date
559 and ff.effective_end_date;
560 --
561 Begin
562 --
563 hr_utility.set_location('Entering:'||l_proc, 5);
564 --
565 l_api_updating := ben_enp_shd.api_updating
566 (p_enrt_perd_id => p_enrt_perd_id,
567 p_object_version_number => p_object_version_number);
568 --
569 if (l_api_updating
570 and nvl(p_rt_end_dt_rl,hr_api.g_number)
571 <> ben_enp_shd.g_old_rec.rt_end_dt_rl
572 or not l_api_updating)
573 and p_rt_end_dt_rl is not null then
574 --
575 -- check if value of formula rule is valid.
576 --
577 open c1;
578 --
579 -- fetch value from cursor if it returns a record then the
580 -- formula is valid otherwise its invalid
581 --
582 fetch c1 into l_dummy;
583 if c1%notfound then
584 --
585 close c1;
586 --
587 -- raise error
588 --
589 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
593 --
590 fnd_message.set_token('ID',p_rt_end_dt_rl);
591 fnd_message.set_token('TYPE_ID',-67);
592 fnd_message.raise_error;
594 end if;
595 --
596 close c1;
597 --
598 end if;
599 --
600 hr_utility.set_location('Leaving:'||l_proc,10);
601 --
602 end chk_rt_end_dt_rl;
603 -- ----------------------------------------------------------------------------
604 -- |------< chk_rt_end_dt_cd >------|
605 -- ----------------------------------------------------------------------------
606 --
607 -- Description
608 -- This procedure is used to check that the lookup value is valid.
609 --
610 -- Pre Conditions
611 -- None.
612 --
613 -- In Parameters
614 -- enrt_perd_id PK of record being inserted or updated.
615 -- rt_end_dt_cd Value of lookup code.
616 -- effective_date effective date
617 -- object_version_number Object version number of record being
618 -- inserted or updated.
619 --
620 -- Post Success
621 -- Processing continues
622 --
623 -- Post Failure
624 -- Error handled by procedure
625 --
626 -- Access Status
627 -- Internal table handler use only.
628 --
629 Procedure chk_rt_end_dt_cd(p_enrt_perd_id in number,
630 p_rt_end_dt_cd in varchar2,
631 p_effective_date in date,
632 p_object_version_number in number) is
633 --
634 l_proc varchar2(72) := g_package||'chk_rt_end_dt_cd';
635 l_api_updating boolean;
636 --
637 Begin
638 --
639 hr_utility.set_location('Entering:'||l_proc, 5);
640 --
641 l_api_updating := ben_enp_shd.api_updating
642 (p_enrt_perd_id => p_enrt_perd_id,
643 p_object_version_number => p_object_version_number);
644 --
645 if (l_api_updating
646 and p_rt_end_dt_cd
647 <> nvl(ben_enp_shd.g_old_rec.rt_end_dt_cd,hr_api.g_varchar2)
648 or not l_api_updating)
649 and p_rt_end_dt_cd is not null then
650 --
651 -- check if value of lookup falls within lookup type.
652 --
653 if hr_api.not_exists_in_hr_lookups
654 (p_lookup_type => 'BEN_RT_END',
655 p_lookup_code => p_rt_end_dt_cd,
656 p_effective_date => p_effective_date) then
657 --
658 -- raise error as does not exist as lookup
659 --
660 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
661 fnd_message.set_token('FIELD', 'p_rt_end_dt_cd');
662 fnd_message.set_token('VALUE', p_rt_end_dt_cd);
663 fnd_message.set_token('TYPE', 'BEN_RT_END');
664 fnd_message.raise_error;
665 --
666 end if;
667 --
668 end if;
669 --
670 hr_utility.set_location('Leaving:'||l_proc,10);
671 --
672 end chk_rt_end_dt_cd;
673 --
674 --
675 -- ----------------------------------------------------------------------------
676 -- |------< chk_reinstate_ovrdn_cd >------|
677 -- ----------------------------------------------------------------------------
678 --
679 -- Description
680 -- This procedure is used to check that the lookup value is valid.
681 --
682 -- Pre Conditions
683 -- None.
684 --
685 -- In Parameters
686 -- enrt_perd_id PK of record being inserted or updated.
687 -- reinstate_ovrdn_cd Value of lookup code.
688 -- effective_date effective date
689 -- object_version_number Object version number of record being
690 -- inserted or updated.
691 --
692 -- Post Success
693 -- Processing continues
694 --
695 -- Post Failure
696 -- Error handled by procedure
697 --
698 -- Access Status
699 -- Internal table handler use only.
700 --
701 Procedure chk_reinstate_ovrdn_cd(p_enrt_perd_id in number,
702 p_reinstate_ovrdn_cd in varchar2,
703 p_effective_date in date,
704 p_object_version_number in number) is
705 --
706 l_proc varchar2(72) := g_package||'chk_reinstate_ovrdn_cd';
707 l_api_updating boolean;
708 --
709 Begin
710 --
711 hr_utility.set_location('Entering:'||l_proc, 5);
712 --
713 l_api_updating := ben_enp_shd.api_updating
714 (p_enrt_perd_id => p_enrt_perd_id,
715 p_object_version_number => p_object_version_number);
716 --
717 if (l_api_updating
718 and p_reinstate_ovrdn_cd
719 <> nvl(ben_enp_shd.g_old_rec.reinstate_ovrdn_cd,hr_api.g_varchar2)
720 or not l_api_updating)
721 and p_reinstate_ovrdn_cd is not null then
722 --
723 -- check if value of lookup falls within lookup type.
724 --
725 if hr_api.not_exists_in_hr_lookups
726 (p_lookup_type => 'BEN_REINSTATE_OVRDN',
727 p_lookup_code => p_reinstate_ovrdn_cd,
728 p_effective_date => p_effective_date) then
729 --
730 -- raise error as does not exist as lookup
731 --
732 -- raise error as does not exist as lookup
733 --
734 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
735 fnd_message.set_token('FIELD', 'p_reinstate_ovrdn_cd');
736 fnd_message.set_token('VALUE', p_reinstate_ovrdn_cd);
737 fnd_message.set_token('TYPE', 'BEN_REINSTATE_OVRDN');
741 --
738 fnd_message.raise_error;
739 --
740 end if;
742 end if;
743 --
744 hr_utility.set_location('Leaving:'||l_proc,10);
745 --
746 end chk_reinstate_ovrdn_cd;
747 --
748 -- ----------------------------------------------------------------------------
749 -- |------< chk_reinstate_cd >------|
750 -- ----------------------------------------------------------------------------
751 --
752 -- Description
753 -- This procedure is used to check that the lookup value is valid.
754 --
755 -- Pre Conditions
756 -- None.
757 --
758 -- In Parameters
759 -- enrt_perd_id PK of record being inserted or updated.
760 -- reinstate_cd Value of lookup code.
761 -- effective_date effective date
762 -- object_version_number Object version number of record being
763 -- inserted or updated.
764 --
765 -- Post Success
766 -- Processing continues
767 --
768 -- Post Failure
769 -- Error handled by procedure
770 --
771 -- Access Status
772 -- Internal table handler use only.
773 --
774 Procedure chk_reinstate_cd(p_enrt_perd_id in number,
775 p_reinstate_cd in varchar2,
776 p_effective_date in date,
777 p_object_version_number in number) is
778 --
779 l_proc varchar2(72) := g_package||'chk_reinstate_cd';
780 l_api_updating boolean;
781 --
782 Begin
783 --
784 hr_utility.set_location('Entering:'||l_proc, 5);
785 --
786 l_api_updating := ben_enp_shd.api_updating
787 (p_enrt_perd_id => p_enrt_perd_id,
788 p_object_version_number => p_object_version_number);
789 --
790 if (l_api_updating
791 and p_reinstate_cd
792 <> nvl(ben_enp_shd.g_old_rec.reinstate_cd,hr_api.g_varchar2)
793 or not l_api_updating)
794 and p_reinstate_cd is not null then
795 --
796 -- check if value of lookup falls within lookup type.
797 --
798 if hr_api.not_exists_in_hr_lookups
799 (p_lookup_type => 'BEN_REINSTATE',
800 p_lookup_code => p_reinstate_cd,
801 p_effective_date => p_effective_date) then
802 --
803 -- raise error as does not exist as lookup
804 --
805 -- raise error as does not exist as lookup
806 --
807 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
808 fnd_message.set_token('FIELD', 'p_reinstate_cd');
809 fnd_message.set_token('VALUE', p_reinstate_cd);
810 fnd_message.set_token('TYPE', 'BEN_REINSTATE');
811 fnd_message.raise_error;
812 end if;
813 --
814 end if;
815 --
816 hr_utility.set_location('Leaving:'||l_proc,10);
817 --
818 end chk_reinstate_cd;
819
820 --
821
822 -- ----------------------------------------------------------------------------
823 -- |------< chk_rt_strt_dt_cd >------|
824 -- ----------------------------------------------------------------------------
825 --
826 -- Description
827 -- This procedure is used to check that the lookup value is valid.
828 --
829 -- Pre Conditions
830 -- None.
831 --
832 -- In Parameters
833 -- enrt_perd_id PK of record being inserted or updated.
834 -- rt_strt_dt_cd Value of lookup code.
835 -- effective_date effective date
836 -- object_version_number Object version number of record being
837 -- inserted or updated.
838 --
839 -- Post Success
840 -- Processing continues
841 --
842 -- Post Failure
843 -- Error handled by procedure
844 --
845 -- Access Status
846 -- Internal table handler use only.
847 --
848 Procedure chk_rt_strt_dt_cd(p_enrt_perd_id in number,
849 p_rt_strt_dt_cd in varchar2,
850 p_effective_date in date,
851 p_object_version_number in number) is
852 --
853 l_proc varchar2(72) := g_package||'chk_rt_strt_dt_cd';
854 l_api_updating boolean;
855 --
856 Begin
857 --
858 hr_utility.set_location('Entering:'||l_proc, 5);
859 --
860 l_api_updating := ben_enp_shd.api_updating
861 (p_enrt_perd_id => p_enrt_perd_id,
862 p_object_version_number => p_object_version_number);
863 --
864 if (l_api_updating
865 and p_rt_strt_dt_cd
866 <> nvl(ben_enp_shd.g_old_rec.rt_strt_dt_cd,hr_api.g_varchar2)
867 or not l_api_updating)
868 and p_rt_strt_dt_cd is not null then
869 --
870 -- check if value of lookup falls within lookup type.
871 --
872 if hr_api.not_exists_in_hr_lookups
873 (p_lookup_type => 'BEN_RT_STRT',
874 p_lookup_code => p_rt_strt_dt_cd,
875 p_effective_date => p_effective_date) then
876 --
877 -- raise error as does not exist as lookup
878 --
879 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
880 hr_utility.raise_error;
881 --
882 end if;
883 --
884 end if;
885 --
886 hr_utility.set_location('Leaving:'||l_proc,10);
887 --
888 end chk_rt_strt_dt_cd;
889 --
890 -- ----------------------------------------------------------------------------
891 -- |------< chk_enrt_cvg_end_dt_rl >------|
892 -- ----------------------------------------------------------------------------
893 --
894 -- Description
895 -- This procedure is used to check that the Formula Rule is valid.
896 --
897 -- Pre Conditions
898 -- None.
902 -- enrt_cvg_end_dt_rl Value of formula rule id.
899 --
900 -- In Parameters
901 -- enrt_perd_id PK of record being inserted or updated.
903 -- effective_date effective date
904 -- object_version_number Object version number of record being
905 -- inserted or updated.
906 --
907 -- Post Success
908 -- Processing continues
909 --
910 -- Post Failure
911 -- Error handled by procedure
912 --
913 -- Access Status
914 -- Internal table handler use only.
915 --
916 Procedure chk_enrt_cvg_end_dt_rl(p_enrt_perd_id in number,
917 p_enrt_cvg_end_dt_rl in number,
918 p_business_group_id in number,
919 p_effective_date in date,
920 p_object_version_number in number) is
921 --
922 l_proc varchar2(72) := g_package||'chk_enrt_cvg_end_dt_rl';
923 l_api_updating boolean;
924 l_dummy varchar2(1);
925 --
926 cursor c1 is
927 select null
928 from ff_formulas_f ff ,
929 per_business_groups pbg
930 where ff.formula_id = p_enrt_cvg_end_dt_rl
931 and ff.formula_type_id = -30 /*default enrollment det */
932 and pbg.business_group_id = p_business_group_id
933 and nvl(ff.business_group_id, p_business_group_id) =
934 p_business_group_id
935 and nvl(ff.legislation_code, pbg.legislation_code) =
936 pbg.legislation_code
937 and p_effective_date
938 between ff.effective_start_date
939 and ff.effective_end_date;
940 --
941 Begin
942 --
943 hr_utility.set_location('Entering:'||l_proc, 5);
944 --
945 l_api_updating := ben_enp_shd.api_updating
946 (p_enrt_perd_id => p_enrt_perd_id,
947 p_object_version_number => p_object_version_number);
948 --
949 if (l_api_updating
950 and nvl(p_enrt_cvg_end_dt_rl,hr_api.g_number)
951 <> ben_enp_shd.g_old_rec.enrt_cvg_end_dt_rl
952 or not l_api_updating)
953 and p_enrt_cvg_end_dt_rl is not null then
954 --
955 -- check if value of formula rule is valid.
956 --
957 open c1;
958 --
959 -- fetch value from cursor if it returns a record then the
960 -- formula is valid otherwise its invalid
961 --
962 fetch c1 into l_dummy;
963 if c1%notfound then
964 --
965 close c1;
966 --
967 -- raise error
968 --
969 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
970 fnd_message.set_token('ID',p_enrt_cvg_end_dt_rl);
971 fnd_message.set_token('TYPE_ID',-30);
972 fnd_message.raise_error;
973 --
974 end if;
975 --
976 close c1;
977 --
978 end if;
979 --
980 hr_utility.set_location('Leaving:'||l_proc,10);
981 --
982 end chk_enrt_cvg_end_dt_rl;
983 --
984 -- ----------------------------------------------------------------------------
985 -- |------< chk_enrt_cvg_strt_dt_rl >------|
986 -- ----------------------------------------------------------------------------
987 --
988 -- Description
989 -- This procedure is used to check that the Formula Rule is valid.
990 --
991 -- Pre Conditions
992 -- None.
993 --
994 -- In Parameters
995 -- enrt_perd_id PK of record being inserted or updated.
996 -- enrt_cvg_strt_dt_rl Value of formula rule id.
997 -- effective_date effective date
998 -- object_version_number Object version number of record being
999 -- inserted or updated.
1000 --
1001 -- Post Success
1002 -- Processing continues
1003 --
1004 -- Post Failure
1005 -- Error handled by procedure
1006 --
1007 -- Access Status
1008 -- Internal table handler use only.
1009 --
1010 Procedure chk_enrt_cvg_strt_dt_rl(p_enrt_perd_id in number,
1011 p_enrt_cvg_strt_dt_rl in number,
1012 p_business_group_id in number,
1013 p_effective_date in date,
1014 p_object_version_number in number) is
1015 --
1016 l_proc varchar2(72) := g_package||'chk_enrt_cvg_strt_dt_rl';
1017 l_api_updating boolean;
1018 l_dummy varchar2(1);
1019 --
1020 cursor c1 is
1021 select null
1022 from ff_formulas_f ff ,
1023 per_business_groups pbg
1024 where ff.formula_id = p_enrt_cvg_strt_dt_rl
1025 and ff.formula_type_id = -29 /*default enrollment det */
1026 and pbg.business_group_id = p_business_group_id
1027 and nvl(ff.business_group_id, p_business_group_id) =
1028 p_business_group_id
1029 and nvl(ff.legislation_code, pbg.legislation_code) =
1030 pbg.legislation_code
1031 and p_effective_date
1032 between ff.effective_start_date
1033 and ff.effective_end_date;
1034 --
1035 Begin
1036 --
1037 hr_utility.set_location('Entering:'||l_proc, 5);
1038 --
1039 l_api_updating := ben_enp_shd.api_updating
1040 (p_enrt_perd_id => p_enrt_perd_id,
1041 p_object_version_number => p_object_version_number);
1042 --
1043 if (l_api_updating
1044 and nvl(p_enrt_cvg_strt_dt_rl,hr_api.g_number)
1045 <> ben_enp_shd.g_old_rec.enrt_cvg_strt_dt_rl
1046 or not l_api_updating)
1047 and p_enrt_cvg_strt_dt_rl is not null then
1048 --
1049 -- check if value of formula rule is valid.
1050 --
1054 -- formula is valid otherwise its invalid
1051 open c1;
1052 --
1053 -- fetch value from cursor if it returns a record then the
1055 --
1056 fetch c1 into l_dummy;
1057 if c1%notfound then
1058 --
1059 close c1;
1060 --
1061 -- raise error
1062 --
1063 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1064 fnd_message.set_token('ID',p_enrt_cvg_strt_dt_rl);
1065 fnd_message.set_token('TYPE_ID',-29);
1066 fnd_message.raise_error;
1067 --
1068 end if;
1069 --
1070 close c1;
1071 --
1072 end if;
1073 --
1074 hr_utility.set_location('Leaving:'||l_proc,10);
1075 --
1076 end chk_enrt_cvg_strt_dt_rl;
1077 --
1078 -- ----------------------------------------------------------------------------
1079 -- |------< chk_enrt_cvg_end_dt_cd >------|
1080 -- ----------------------------------------------------------------------------
1081 --
1082 -- Description
1083 -- This procedure is used to check that the lookup value is valid.
1084 --
1085 -- Pre Conditions
1086 -- None.
1087 --
1088 -- In Parameters
1089 -- enrt_perd_id PK of record being inserted or updated.
1090 -- enrt_cvg_end_dt_cd Value of lookup code.
1091 -- effective_date effective date
1092 -- object_version_number Object version number of record being
1093 -- inserted or updated.
1094 --
1095 -- Post Success
1096 -- Processing continues
1097 --
1098 -- Post Failure
1099 -- Error handled by procedure
1100 --
1101 -- Access Status
1102 -- Internal table handler use only.
1103 --
1104 Procedure chk_enrt_cvg_end_dt_cd(p_enrt_perd_id in number,
1105 p_enrt_cvg_end_dt_cd in varchar2,
1106 p_effective_date in date,
1107 p_object_version_number in number) is
1108 --
1109 l_proc varchar2(72) := g_package||'chk_enrt_cvg_end_dt_cd';
1110 l_api_updating boolean;
1111 --
1112 Begin
1113 --
1114 hr_utility.set_location('Entering:'||l_proc, 5);
1115 --
1116 l_api_updating := ben_enp_shd.api_updating
1117 (p_enrt_perd_id => p_enrt_perd_id,
1118 p_object_version_number => p_object_version_number);
1119 --
1120 if (l_api_updating
1121 and p_enrt_cvg_end_dt_cd
1122 <> nvl(ben_enp_shd.g_old_rec.enrt_cvg_end_dt_cd,hr_api.g_varchar2)
1123 or not l_api_updating)
1124 and p_enrt_cvg_end_dt_cd is not null then
1125 --
1126 -- check if value of lookup falls within lookup type.
1127 --
1128 if hr_api.not_exists_in_hr_lookups
1129 (p_lookup_type => 'BEN_ENRT_CVG_END',
1130 p_lookup_code => p_enrt_cvg_end_dt_cd,
1131 p_effective_date => p_effective_date) then
1132 --
1133 -- raise error as does not exist as lookup
1134 --
1135 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1136 hr_utility.raise_error;
1137 --
1138 end if;
1139 --
1140 end if;
1141 --
1142 hr_utility.set_location('Leaving:'||l_proc,10);
1143 --
1144 end chk_enrt_cvg_end_dt_cd;
1145 --
1146 -- ----------------------------------------------------------------------------
1147 -- |------< chk_rt_strt_dt_rl >------|
1148 -- ----------------------------------------------------------------------------
1149 --
1150 -- Description
1151 -- This procedure is used to check that the Formula Rule is valid.
1152 --
1153 -- Pre Conditions
1154 -- None.
1155 --
1156 -- In Parameters
1157 -- enrt_perd_id PK of record being inserted or updated.
1158 -- rt_strt_dt_rl Value of formula rule id.
1159 -- effective_date effective date
1160 -- object_version_number Object version number of record being
1161 -- inserted or updated.
1162 --
1163 -- Post Success
1164 -- Processing continues
1165 --
1166 -- Post Failure
1167 -- Error handled by procedure
1168 --
1169 -- Access Status
1170 -- Internal table handler use only.
1171 --
1172 Procedure chk_rt_strt_dt_rl(p_enrt_perd_id in number,
1173 p_rt_strt_dt_rl in number,
1174 p_effective_date in date,
1175 p_business_group_id in number,
1176 p_object_version_number in number) is
1177 --
1178 l_proc varchar2(72) := g_package||'chk_rt_strt_dt_rl';
1179 l_api_updating boolean;
1180 l_dummy varchar2(1);
1181 --
1182 cursor c1 is
1183 select null
1184 from ff_formulas_f ff
1185 ,per_business_groups pbg
1186 where ff.formula_id = p_rt_strt_dt_rl
1187 and ff.formula_type_id = -66
1188 and pbg.business_group_id = p_business_group_id
1189 and nvl(ff.business_group_id, p_business_group_id) =
1190 p_business_group_id
1191 and nvl(ff.legislation_code, pbg.legislation_code) =
1192 pbg.legislation_code
1193 and p_effective_date
1194 between ff.effective_start_date
1195 and ff.effective_end_date;
1196 --
1197 Begin
1198 --
1199 hr_utility.set_location('Entering:'||l_proc, 5);
1200 --
1201 l_api_updating := ben_enp_shd.api_updating
1202 (p_enrt_perd_id => p_enrt_perd_id,
1203 p_object_version_number => p_object_version_number);
1204 --
1205 if (l_api_updating
1206 and nvl(p_rt_strt_dt_rl,hr_api.g_number)
1210 --
1207 <> ben_enp_shd.g_old_rec.rt_strt_dt_rl
1208 or not l_api_updating)
1209 and p_rt_strt_dt_rl is not null then
1211 -- check if value of formula rule is valid.
1212 --
1213 open c1;
1214 --
1215 -- fetch value from cursor if it returns a record then the
1216 -- formula is valid otherwise its invalid
1217 --
1218 fetch c1 into l_dummy;
1219 if c1%notfound then
1220 --
1221 close c1;
1222 --
1223 -- raise error
1224 --
1225 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1226 fnd_message.set_token('ID',p_rt_strt_dt_rl);
1227 fnd_message.set_token('TYPE_ID',-66);
1228 fnd_message.raise_error;
1229 --
1230 end if;
1231 --
1232 close c1;
1233 --
1234 end if;
1235 --
1236 hr_utility.set_location('Leaving:'||l_proc,10);
1237 --
1238 end chk_rt_strt_dt_rl;
1239 --
1240 -- ----------------------------------------------------------------------------
1241 -- |------< chk_enrt_cvg_strt_dt_cd >------|
1242 -- ----------------------------------------------------------------------------
1243 --
1244 -- Description
1245 -- This procedure is used to check that the lookup value is valid.
1246 --
1247 -- Pre Conditions
1248 -- None.
1249 --
1250 -- In Parameters
1251 -- enrt_perd_id PK of record being inserted or updated.
1252 -- enrt_cvg_strt_dt_cd Value of lookup code.
1253 -- effective_date effective date
1254 -- object_version_number Object version number of record being
1255 -- inserted or updated.
1256 --
1257 -- Post Success
1258 -- Processing continues
1259 --
1260 -- Post Failure
1261 -- Error handled by procedure
1262 --
1263 -- Access Status
1264 -- Internal table handler use only.
1265 --
1266 Procedure chk_enrt_cvg_strt_dt_cd(p_enrt_perd_id in number,
1267 p_enrt_cvg_strt_dt_cd in varchar2,
1268 p_effective_date in date,
1269 p_object_version_number in number) is
1270 --
1271 l_proc varchar2(72) := g_package||'chk_enrt_cvg_strt_dt_cd';
1272 l_api_updating boolean;
1273 --
1274 Begin
1275 --
1276 hr_utility.set_location('Entering:'||l_proc, 5);
1277 --
1278 l_api_updating := ben_enp_shd.api_updating
1279 (p_enrt_perd_id => p_enrt_perd_id,
1280 p_object_version_number => p_object_version_number);
1281 --
1282 if (l_api_updating
1283 and p_enrt_cvg_strt_dt_cd
1284 <> nvl(ben_enp_shd.g_old_rec.enrt_cvg_strt_dt_cd,hr_api.g_varchar2)
1285 or not l_api_updating)
1286 and p_enrt_cvg_strt_dt_cd is not null then
1287 --
1288 -- check if value of lookup falls within lookup type.
1289 --
1290 if hr_api.not_exists_in_hr_lookups
1291 (p_lookup_type => 'BEN_ENRT_CVG_STRT',
1292 p_lookup_code => p_enrt_cvg_strt_dt_cd,
1293 p_effective_date => p_effective_date) then
1294 --
1295 -- raise error as does not exist as lookup
1296 --
1297 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
1298 fnd_message.raise_error;
1299 --
1300 end if;
1301 --
1302 end if;
1303 --
1304 hr_utility.set_location('Leaving:'||l_proc,10);
1305 --
1306 end chk_enrt_cvg_strt_dt_cd;
1307 --
1308 -- ----------------------------------------------------------------------------
1309 -- |------< chk_cls_enrt_dt_to_use_cd >------|
1310 -- ----------------------------------------------------------------------------
1311 --
1312 -- Description
1313 -- This procedure is used to check that the lookup value is valid.
1314 --
1315 -- Pre Conditions
1316 -- None.
1317 --
1318 -- In Parameters
1319 -- enrt_perd_id PK of record being inserted or updated.
1320 -- cls_enrt_dt_to_use_cd Value of lookup code.
1321 -- effective_date effective date
1322 -- object_version_number Object version number of record being
1323 -- inserted or updated.
1324 --
1325 -- Post Success
1326 -- Processing continues
1327 --
1328 -- Post Failure
1329 -- Error handled by procedure
1330 --
1331 -- Access Status
1332 -- Internal table handler use only.
1333 --
1334 Procedure chk_cls_enrt_dt_to_use_cd(p_enrt_perd_id in number,
1335 p_cls_enrt_dt_to_use_cd in varchar2,
1336 p_effective_date in date,
1337 p_object_version_number in number) is
1338 --
1339 l_proc varchar2(72) := g_package||'chk_cls_enrt_dt_to_use_cd';
1340 l_api_updating boolean;
1341 --
1342 Begin
1343 --
1344 hr_utility.set_location('Entering:'||l_proc, 5);
1345 --
1346 l_api_updating := ben_enp_shd.api_updating
1347 (p_enrt_perd_id => p_enrt_perd_id,
1348 p_object_version_number => p_object_version_number);
1349 --
1350 if (l_api_updating
1351 and p_cls_enrt_dt_to_use_cd
1352 <> nvl(ben_enp_shd.g_old_rec.cls_enrt_dt_to_use_cd,hr_api.g_varchar2)
1353 or not l_api_updating)
1354 and p_cls_enrt_dt_to_use_cd is not null then
1355 --
1356 -- check if value of lookup falls within lookup type.
1357 --
1358 if hr_api.not_exists_in_hr_lookups
1359 (p_lookup_type => 'BEN_CLS_ENRT_DT_TO_USE',
1360 p_lookup_code => p_cls_enrt_dt_to_use_cd,
1364 --
1361 p_effective_date => p_effective_date) then
1362 --
1363 -- raise error as does not exist as lookup
1365 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1366 hr_utility.raise_error;
1367 --
1368 end if;
1369 --
1370 end if;
1371 --
1372 hr_utility.set_location('Leaving:'||l_proc,10);
1373 --
1374 end chk_cls_enrt_dt_to_use_cd;
1375 --
1376 -- ----------------------------------------------------------------------------
1377 -- |------< chk_enrl_strt_dt_dpndcy >------|
1378 -- ----------------------------------------------------------------------------
1379 --
1380 -- Description
1381 -- This procedure is used to check the code/rule dependency as following:
1382 -- If code = 'Rule' then rule must be selected.
1383 -- If code <> 'Rule' then code must not be selected.
1384 --
1385 -- Pre Conditions
1386 -- None.
1387 --
1388 -- In Parameters
1389 -- enrt_perd_id PK of record being inserted or updated.
1390 -- enrt_cvg_strt_dt_cd Value of lookup code.
1391 -- enrt_cvg_strt_dt_rl
1392 -- effective_date effective date
1393 -- object_version_number Object version number of record being
1394 -- inserted or updated.
1395 --
1396 -- Post Success
1397 -- Processing continues
1398 --
1399 -- Post Failure
1400 -- Error handled by procedure
1401 --
1402 -- Access Status
1403 -- Internal table handler use only.
1404 --
1405 Procedure chk_enrl_strt_dt_dpndcy
1406 (p_enrt_perd_id in number,
1407 p_enrt_cvg_strt_dt_cd in varchar2,
1408 p_enrt_cvg_strt_dt_rl in number,
1409 p_effective_date in date,
1410 p_object_version_number in number) is
1411 --
1412 l_proc varchar2(72) := g_package||'chk_enrl_strt_dt_dpndcy ';
1413 l_api_updating boolean;
1414 --
1415 Begin
1416 --
1417 hr_utility.set_location('Entering:'||l_proc, 5);
1418 --
1419 l_api_updating := ben_enp_shd.api_updating
1420 (p_enrt_perd_id => p_enrt_perd_id,
1421 p_object_version_number => p_object_version_number);
1422 --
1423 if (l_api_updating
1424 and (nvl(p_enrt_cvg_strt_dt_cd,hr_api.g_varchar2)
1425 <> nvl(ben_enp_shd.g_old_rec.enrt_cvg_strt_dt_cd,hr_api.g_varchar2) or
1426 nvl(p_enrt_cvg_strt_dt_rl,hr_api.g_number)
1427 <> nvl(ben_enp_shd.g_old_rec.enrt_cvg_strt_dt_rl,hr_api.g_number))
1428 or not l_api_updating) then
1429 --
1430 if (p_enrt_cvg_strt_dt_cd = 'RL' and p_enrt_cvg_strt_dt_rl is null) then
1431 --
1432 fnd_message.set_name('BEN','BEN_91310_ENRT_STRT_CWOR');
1433 fnd_message.raise_error;
1434 --
1435 end if;
1436 --
1437 if nvl(p_enrt_cvg_strt_dt_cd,hr_api.g_varchar2) <> 'RL'
1438 and p_enrt_cvg_strt_dt_rl is not null then
1439 --
1440 fnd_message.set_name('BEN','BEN_91311_ENRT_STRT_RWOC');
1441 fnd_message.raise_error;
1442 --
1443 end if;
1444 --
1445 end if;
1446 --
1447 hr_utility.set_location('Leaving:'||l_proc,10);
1448 --
1449 end chk_enrl_strt_dt_dpndcy;
1450 --
1451
1452 -- ----------------------------------------------------------------------------
1453 -- |------< chk_cwb_validations >------|
1454 -- ----------------------------------------------------------------------------
1455
1456 Procedure chk_cwb_validations
1457 (p_enrt_perd_id in number,
1458 p_data_freeze_date in date ,
1459 p_Sal_chg_reason_cd in varchar2,
1460 p_Approval_mode_cd in varchar2,
1461 p_hrchy_ame_trn_cd in varchar2,
1462 p_hrchy_rl in number,
1463 p_hrchy_ame_app_id in number,
1464 p_hrchy_to_use_cd in varchar2,
1465 p_effective_date in date ,
1466 p_pos_structure_version_id in number,
1467 p_object_version_number in number
1468 ) is
1469 --
1470 l_proc varchar2(72) := g_package||'chk_cwb_validations ';
1471 l_api_updating boolean;
1472
1473 cursor c_hrchy_ame is
1474 select 'x'
1475 from ame_transaction_types_v ame
1476 where ame.transaction_type_id = p_hrchy_ame_trn_cd
1477 and ame.fnd_application_id = p_hrchy_ame_app_id ;
1478 -- and p_effective_date between
1479 -- ame.start_date and nvl(ame.end_date, p_effective_date)
1480
1481
1482
1483 cursor c_hrchy_rl is
1484 select 'x'
1485 from ff_formulas_f ff ,
1486 per_business_groups pbg
1487 where ff.formula_id = p_hrchy_rl
1488 and ff.formula_type_id = -550 /*default enrollment det */
1489 and nvl(ff.legislation_code, pbg.legislation_code) = pbg.legislation_code
1490 and p_effective_date between ff.effective_start_date and ff.effective_end_date;
1491
1492
1493 l_dummy varchar2(1) ;
1494 --
1495 Begin
1496
1497 hr_utility.set_location('Entering:'||l_proc, 5);
1498 --
1499 l_api_updating := ben_enp_shd.api_updating
1500 (p_enrt_perd_id => p_enrt_perd_id,
1501 p_object_version_number => p_object_version_number);
1502
1503
1504 if (l_api_updating
1505 and
1506 ( nvl(p_Sal_chg_reason_cd,hr_api.g_varchar2)
1507 <> nvl(ben_enp_shd.g_old_rec.Sal_chg_reason_cd,hr_api.g_varchar2)
1511
1508 )
1509 or not l_api_updating)
1510 and p_Sal_chg_reason_cd is not null then
1512 hr_utility.set_location('validating SAl_CHG_REASON:'||p_Sal_chg_reason_cd, 5);
1513 if hr_api.not_exists_in_hr_lookups
1514 (p_lookup_type => 'PROPOSAL_REASON',
1515 p_lookup_code => p_Sal_chg_reason_cd,
1516 p_effective_date => p_effective_date) then
1517 --
1518 -- raise error as does not exist as lookup
1519 --
1520 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1521 hr_utility.raise_error;
1522 --
1523 end if;
1524
1525 end if ;
1526
1527 if (l_api_updating
1528 and (
1529 nvl( p_Approval_mode_cd,hr_api.g_varchar2) <>
1530 nvl(ben_enp_shd.g_old_rec.Approval_mode_cd,hr_api.g_varchar2)
1531 )
1532 or not l_api_updating)
1533 and p_Approval_mode_cd is not null then
1534
1535 hr_utility.set_location('validating Approval_mode_cd:'||p_Approval_mode_cd, 5);
1536 if hr_api.not_exists_in_hr_lookups
1537 (p_lookup_type => 'BEN_CWB_APPROVAL_MODE',
1538 p_lookup_code => p_Approval_mode_cd,
1539 p_effective_date => p_effective_date) then
1540 --
1541 -- raise error as does not exist as lookup
1542 --
1543 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1544 hr_utility.raise_error;
1545 --
1546 end if;
1547
1548 end if ;
1549
1550
1551
1552 if (l_api_updating
1553 and
1554 ( nvl( p_hrchy_ame_trn_cd,hr_api.g_varchar2) <>
1555 nvl(ben_enp_shd.g_old_rec.hrchy_ame_trn_cd,hr_api.g_varchar2)
1556 or nvl(p_hrchy_ame_app_id,hr_api.g_number) <>
1557 nvl(ben_enp_shd.g_old_rec.hrchy_ame_app_id,hr_api.g_number)
1558 --Bug 4083891 : Perform validation for change in Assingned Life Event Date also
1559 or nvl(p_effective_date,hr_api.g_date) <>
1560 nvl(ben_enp_shd.g_old_rec.asnd_lf_evt_dt,hr_api.g_date)
1561 )
1562 or not l_api_updating)
1563 and p_hrchy_ame_trn_cd is not null then
1564
1565 hr_utility.set_location('validating hrchy_ame_trn_cd:'||p_hrchy_ame_trn_cd, 5);
1566 open c_hrchy_ame ;
1567 fetch c_hrchy_ame into l_dummy ;
1568 if c_hrchy_ame%notfound then
1569 close c_hrchy_ame ;
1570 hr_utility.set_location('validating hrchy_ame_trn_cd:'||p_hrchy_ame_trn_cd, 7);
1571 fnd_message.set_name('BEN','BEN_93730_CW_HRCHY_AME_ERR');
1572 --Bug 4083891 : Passed Assigned Life Event Date parameter to the modified message
1573 fnd_message.set_token('ALED',fnd_date.date_to_displaydate(p_effective_date,calendar_aware=>2));
1574 fnd_message.raise_error;
1575
1576 end if ;
1577 close c_hrchy_ame ;
1578
1579 hr_utility.set_location('out validating hrchy_ame_trn_cd:'||p_hrchy_ame_trn_cd, 5);
1580 end if ;
1581
1582
1583 if (l_api_updating
1584 and
1585 ( nvl( p_hrchy_rl,hr_api.g_number) <>
1586 nvl(ben_enp_shd.g_old_rec.hrchy_rl,hr_api.g_number)
1587 )
1588 or not l_api_updating)
1589 and p_hrchy_rl is not null then
1590
1591 hr_utility.set_location('validating hrchy_rl:'||p_hrchy_rl, 5);
1592 open c_hrchy_rl ;
1593 fetch c_hrchy_rl into l_dummy;
1594 if c_hrchy_rl%notfound then
1595 --
1596 close c_hrchy_rl;
1597 --
1598 -- raise error
1599 --
1600 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
1601 fnd_message.set_token('ID',p_hrchy_rl);
1602 fnd_message.set_token('TYPE_ID',-549);
1603 fnd_message.raise_error;
1604 --
1605 end if;
1606 close c_hrchy_rl;
1607 --
1608 end if ;
1609
1610
1611 if (l_api_updating
1612 and
1613 ( nvl( p_hrchy_to_use_cd,hr_api.g_varchar2) <>
1614 nvl(ben_enp_shd.g_old_rec.hrchy_to_use_cd,hr_api.g_varchar2)
1615 )
1616 or not l_api_updating) then
1617 hr_utility.set_location('validating hrchy_to_use_cd:'||p_hrchy_to_use_cd, 5);
1618 if p_hrchy_to_use_cd is not null then
1619 if hr_api.not_exists_in_hr_lookups
1620 (p_lookup_type => 'BEN_HRCHY_TO_USE',
1621 p_lookup_code => p_hrchy_to_use_cd,
1622 p_effective_date => p_effective_date) then
1623 --
1624 -- raise error as does not exist as lookup
1625 --
1626 hr_utility.set_message(801,'HR_LOOKUP_DOES_NOT_EXIST');
1627 hr_utility.raise_error;
1628 --
1629 end if;
1630 end if ;
1631 end if ;
1632 --
1633 -- Bug 4498668 Moved out the checks out of if condition
1634 --
1635 hr_utility.set_location(' p_hrchy_to_use_cd ' || p_hrchy_to_use_cd, 99);
1636 hr_utility.set_location(' p_hrchy_ame_trn_cd ' || p_hrchy_ame_trn_cd, 99);
1637
1638 if (p_hrchy_to_use_cd = 'RL' and p_hrchy_rl is null)
1639 or ( nvl(p_hrchy_to_use_cd,hr_api.g_varchar2) <> 'RL'
1640 and p_hrchy_rl is not null ) then
1641 --
1642 fnd_message.set_name('BEN','BEN_93731_CWB_HRCHY_CWOR_RWOC');
1643 fnd_message.raise_error;
1644 --
1645 end if;
1646 --
1647
1648 if (p_hrchy_to_use_cd = 'AME' and p_hrchy_ame_trn_cd is null)
1649 or ( nvl(p_hrchy_to_use_cd,hr_api.g_varchar2) <> 'AME' and p_hrchy_ame_trn_cd is not null ) then
1653 --
1650 --
1651 fnd_message.set_name('BEN','BEN_93732_CWB_HRCHY_AME_CWOC');
1652 fnd_message.raise_error;
1654 end if;
1655
1656
1657 if (p_hrchy_to_use_cd = 'P' and p_pos_structure_version_id is null)
1658 or ( nvl(p_hrchy_to_use_cd,hr_api.g_varchar2) <> 'P'
1659 and p_pos_structure_version_id is not null ) then
1660 --
1661 fnd_message.set_name('BEN','BEN_93733_CWB_HRCHY_AME_CWOC');
1662 fnd_message.raise_error;
1663 --
1664 end if;
1665 --
1666 hr_utility.set_location('Leaving:'||l_proc,10);
1667 end chk_cwb_validations ;
1668
1669
1670 -- ----------------------------------------------------------------------------
1671 -- |------< chk_enrl_end_dt_dpndcy >------|
1672 -- ----------------------------------------------------------------------------
1673 --
1674 -- Description
1675 -- This procedure is used to check the code/rule dependency as following:
1676 -- If code = 'Rule' then rule must be selected.
1677 -- If code <> 'Rule' then code must not be selected.
1678 --
1679 -- Pre Conditions
1680 -- None.
1681 --
1682 -- In Parameters
1683 -- enrt_perd_id PK of record being inserted or updated.
1684 -- enrt_cvg_end_dt_cd Value of lookup code.
1685 -- enrt_cvg_end_dt_rl
1686 -- effective_date effective date
1687 -- object_version_number Object version number of record being
1688 -- inserted or updated.
1689 --
1690 -- Post Success
1691 -- Processing continues
1692 --
1693 -- Post Failure
1694 -- Error handled by procedure
1695 --
1696 -- Access Status
1697 -- Internal table handler use only.
1698 --
1699 Procedure chk_enrl_end_dt_dpndcy
1700 (p_enrt_perd_id in number,
1701 p_enrt_cvg_end_dt_cd in varchar2,
1702 p_enrt_cvg_end_dt_rl in number,
1703 p_effective_date in date,
1704 p_object_version_number in number) is
1705 --
1706 l_proc varchar2(72) := g_package||'chk_enrl_end_dt_dpndcy ';
1707 l_api_updating boolean;
1708 --
1709 Begin
1710 --
1711 hr_utility.set_location('Entering:'||l_proc, 5);
1712 --
1713 l_api_updating := ben_enp_shd.api_updating
1714 (p_enrt_perd_id => p_enrt_perd_id,
1715 p_object_version_number => p_object_version_number);
1716 --
1717 if (l_api_updating
1718 and
1719 (nvl(p_enrt_cvg_end_dt_cd,hr_api.g_varchar2)
1720 <> nvl(ben_enp_shd.g_old_rec.enrt_cvg_end_dt_cd,hr_api.g_varchar2) or
1721 nvl(p_enrt_cvg_end_dt_rl,hr_api.g_number)
1722 <> nvl(ben_enp_shd.g_old_rec.enrt_cvg_end_dt_rl,hr_api.g_number))
1723 or not l_api_updating) then
1724 --
1725 if (p_enrt_cvg_end_dt_cd = 'RL' and p_enrt_cvg_end_dt_rl is null) then
1726 --
1727 fnd_message.set_name('BEN','BEN_91378_DFLT_DENRL_END_DT1');
1728 fnd_message.raise_error;
1729 --
1730 end if;
1731 --
1732 if nvl(p_enrt_cvg_end_dt_cd,hr_api.g_varchar2) <> 'RL'
1733 and p_enrt_cvg_end_dt_rl is not null then
1734 --
1735 fnd_message.set_name('BEN','BEN_91379_DFLT_DENRL_END_DT2');
1736 fnd_message.raise_error;
1737 --
1738 end if;
1739 --
1740 end if;
1741 --
1742 hr_utility.set_location('Leaving:'||l_proc,10);
1743 --
1744 end chk_enrl_end_dt_dpndcy;
1745 --
1746 -- ----------------------------------------------------------------------------
1747 -- |------< chk_rt_strt_dt_dpndcy >------|
1748 -- ----------------------------------------------------------------------------
1749 --
1750 -- Description
1751 -- This procedure is used to check the code/rule dependency as following:
1752 -- If code = 'Rule' then rule must be selected.
1753 -- If code <> 'Rule' then code must not be selected.
1754 --
1755 -- Pre Conditions
1756 -- None.
1757 --
1758 -- In Parameters
1759 -- enrt_perd_id PK of record being inserted or updated.
1760 -- rt_strt_dt_cd Value of lookup code.
1761 -- rt_strt_dt_rl
1762 -- effective_date effective date
1763 -- object_version_number Object version number of record being
1764 -- inserted or updated.
1765 --
1766 -- Post Success
1767 -- Processing continues
1768 --
1769 -- Post Failure
1770 -- Error handled by procedure
1771 --
1772 -- Access Status
1773 -- Internal table handler use only.
1774 --
1775 Procedure chk_rt_strt_dt_dpndcy
1776 (p_enrt_perd_id in number,
1777 p_rt_strt_dt_cd in varchar2,
1778 p_rt_strt_dt_rl in number,
1779 p_effective_date in date,
1780 p_object_version_number in number) is
1781 --
1782 l_proc varchar2(72) := g_package||'chk_rt_strt_dt_dpndcy ';
1783 l_api_updating boolean;
1784 --
1785 Begin
1786 --
1787 hr_utility.set_location('Entering:'||l_proc, 5);
1788 --
1789 l_api_updating := ben_enp_shd.api_updating
1790 (p_enrt_perd_id => p_enrt_perd_id,
1791 p_object_version_number => p_object_version_number);
1792 --
1793 if (l_api_updating
1794 and (nvl(p_rt_strt_dt_cd,hr_api.g_varchar2)
1795 <> nvl(ben_enp_shd.g_old_rec.rt_strt_dt_cd,hr_api.g_varchar2) or
1799 --
1796 nvl(p_rt_strt_dt_rl,hr_api.g_number)
1797 <> nvl(ben_enp_shd.g_old_rec.rt_strt_dt_rl,hr_api.g_number))
1798 or not l_api_updating) then
1800 if (p_rt_strt_dt_cd = 'RL' and p_rt_strt_dt_rl is null) then
1801 --
1802 fnd_message.set_name('BEN','BEN_91623_CD_RL_1');
1803 fnd_message.raise_error;
1804 --
1805 end if;
1806 --
1807 if nvl(p_rt_strt_dt_cd,hr_api.g_varchar2) <> 'RL'
1808 and p_rt_strt_dt_rl is not null then
1809 --
1810 fnd_message.set_name('BEN','BEN_91624_CD_RL_2');
1811 fnd_message.raise_error;
1812 --
1813 end if;
1814 --
1815 end if;
1816 --
1817 hr_utility.set_location('Leaving:'||l_proc,10);
1818 --
1819 end chk_rt_strt_dt_dpndcy;
1820 --
1821 -- ----------------------------------------------------------------------------
1822 -- |------< chk_rt_end_dt_dpndcy >------|
1823 -- ----------------------------------------------------------------------------
1824 --
1825 -- Description
1826 -- This procedure is used to check the code/rule dependency as following:
1827 -- If code = 'Rule' then rule must be selected.
1828 -- If code <> 'Rule' then code must not be selected.
1829 --
1830 -- Pre Conditions
1831 -- None.
1832 --
1833 -- In Parameters
1834 -- enrt_perd_id PK of record being inserted or updated.
1835 -- rt_end_dt_cd Value of lookup code.
1836 -- rt_end_dt_rl
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_rt_end_dt_dpndcy
1851 (p_enrt_perd_id in number,
1852 p_rt_end_dt_cd in varchar2,
1853 p_rt_end_dt_rl in number,
1854 p_effective_date in date,
1855 p_object_version_number in number) is
1856 --
1857 l_proc varchar2(72) := g_package||'chk_rt_end_dt_dpndcy ';
1858 l_api_updating boolean;
1859 --
1860 Begin
1861 --
1862 hr_utility.set_location('Entering:'||l_proc, 5);
1863 --
1864 l_api_updating := ben_enp_shd.api_updating
1865 (p_enrt_perd_id => p_enrt_perd_id,
1866 p_object_version_number => p_object_version_number);
1867 --
1868 if (l_api_updating
1869 and
1870 (nvl(p_rt_end_dt_cd,hr_api.g_varchar2)
1871 <> nvl(ben_enp_shd.g_old_rec.rt_end_dt_cd,hr_api.g_varchar2) or
1872 nvl(p_rt_end_dt_rl,hr_api.g_number)
1873 <> nvl(ben_enp_shd.g_old_rec.rt_end_dt_rl,hr_api.g_number))
1874 or not l_api_updating) then
1875 --
1876 if (p_rt_end_dt_cd = 'RL' and p_rt_end_dt_rl is null) then
1877 --
1878 fnd_message.set_name('BEN','BEN_91623_CD_RL_1');
1879 fnd_message.raise_error;
1880 --
1881 end if;
1882 --
1883 if nvl(p_rt_end_dt_cd,hr_api.g_varchar2) <> 'RL'
1884 and p_rt_end_dt_rl is not null then
1885 --
1886 fnd_message.set_name('BEN','BEN_91624_CD_RL_2');
1887 fnd_message.raise_error;
1888 --
1889 end if;
1890 --
1891 end if;
1892 --
1893 hr_utility.set_location('Leaving:'||l_proc,10);
1894 --
1895 end chk_rt_end_dt_dpndcy;
1896 --
1897 -- ----------------------------------------------------------------------------
1898 -- |------< chk_bdgt_upd_end_dt>------|
1899 -- ----------------------------------------------------------------------------
1900 --
1901 -- Description
1902 -- This procedure is used to check that bdgt_upd_end_dt > bdgt_upd_strt_dt.
1903 --
1904 -- Pre Conditions
1905 -- None.
1906 --
1907 -- In Parameters
1908 -- enrt_perd_id PK of record being inserted or updated.
1909 -- budget update start date
1910 -- budget update end date
1911 -- object_version_number Object version number of record being
1912 -- inserted or updated.
1913 --
1914 -- Post Success
1915 -- Processing continues
1916 --
1917 -- Post Failure
1918 -- Error handled by procedure
1919 --
1920 -- Access Status
1921 -- Internal table handler use only.
1922 --
1923 Procedure chk_bdgt_upd_end_dt(p_enrt_perd_id in number,
1924 p_bdgt_upd_strt_dt in date,
1925 p_bdgt_upd_end_dt in date,
1926 p_object_version_number in number) is
1927 --
1928 l_proc varchar2(72) := g_package||'chk_bdgt_upd_end_dt';
1929 l_api_updating boolean;
1930 --
1931 Begin
1932 --
1933 hr_utility.set_location('Entering:'||l_proc, 5);
1934 --
1935
1936 l_api_updating := ben_enp_shd.api_updating
1937 (p_enrt_perd_id => p_enrt_perd_id,
1938 p_object_version_number => p_object_version_number);
1939 --
1940 -- check it if changing either start or end date.
1941 --
1942 if (p_bdgt_upd_strt_dt is not null and p_bdgt_upd_end_dt is not null)
1943 and
1944 ((l_api_updating
1945 and (p_bdgt_upd_strt_dt <> nvl(ben_enp_shd.g_old_rec.bdgt_upd_strt_dt,hr_api.g_date) or
1946 p_bdgt_upd_end_dt <> nvl(ben_enp_shd.g_old_rec.bdgt_upd_end_dt,hr_api.g_date)
1947 )
1948 )
1949 or not l_api_updating)
1950 then
1951 --
1952 -- check if start date greater than end date.
1953 --
1957 --
1954 if p_bdgt_upd_strt_dt > p_bdgt_upd_end_dt then
1955 --
1956 -- raise error
1958 fnd_message.set_name('BEN','BEN_92975_INVLD_BGT_UPD_END_DT');
1959 fnd_message.raise_error;
1960 --
1961 end if;
1962 --
1963 end if;
1964
1965 hr_utility.set_location('Leaving:'||l_proc,10);
1966 --
1967 end chk_bdgt_upd_end_dt;
1968 --
1969 -- ----------------------------------------------------------------------------
1970 -- |------< chk_ws_upd_end_dt>------|
1971 -- ----------------------------------------------------------------------------
1972 --
1973 -- Description
1974 -- This procedure is used to check that ws_upd_end_dt > ws_upd_strt_dt.
1975 --
1976 -- Pre Conditions
1977 -- None.
1978 --
1979 -- In Parameters
1980 -- enrt_perd_id PK of record being inserted or updated.
1981 -- worksheet start date
1982 -- worksheet end date
1983 -- object_version_number Object version number of record being
1984 -- inserted or updated.
1985 --
1986 -- Post Success
1987 -- Processing continues
1988 --
1989 -- Post Failure
1990 -- Error handled by procedure
1991 --
1992 -- Access Status
1993 -- Internal table handler use only.
1994 --
1995 Procedure chk_ws_upd_end_dt(p_enrt_perd_id in number,
1996 p_ws_upd_strt_dt in date,
1997 p_ws_upd_end_dt in date,
1998 p_object_version_number in number) is
1999 --
2000 l_proc varchar2(72) := g_package||'chk_ws_upd_end_dt';
2001 l_api_updating boolean;
2002 --
2003 Begin
2004 --
2005 hr_utility.set_location('Entering:'||l_proc, 5);
2006 --
2007
2008 l_api_updating := ben_enp_shd.api_updating
2009 (p_enrt_perd_id => p_enrt_perd_id,
2010 p_object_version_number => p_object_version_number);
2011 --
2012 -- check it if changing either start or end date.
2013 --
2014 if (p_ws_upd_strt_dt is not null and p_ws_upd_end_dt is not null)
2015 and
2016 ((l_api_updating
2017 and (p_ws_upd_strt_dt <> nvl(ben_enp_shd.g_old_rec.ws_upd_strt_dt,hr_api.g_date) or
2018 p_ws_upd_end_dt <> nvl(ben_enp_shd.g_old_rec.ws_upd_end_dt,hr_api.g_date)
2019 )
2020 )
2021 or not l_api_updating)
2022 then
2023 --
2024 -- check if start date greater than end date
2025 --
2026 if p_ws_upd_strt_dt > p_ws_upd_end_dt then
2027 --
2028 -- raise error
2029 --
2030 fnd_message.set_name('BEN','BEN_92976_INVLD_WS_UPD_END_DT');
2031 fnd_message.raise_error;
2032 --
2033 end if;
2034 --
2035 end if;
2036
2037 hr_utility.set_location('Leaving:'||l_proc,10);
2038 --
2039 end chk_ws_upd_end_dt;
2040 --
2041 --
2042 -- ----------------------------------------------------------------------------
2043 -- |------< chk_bdgt_upd_period>------|
2044 -- ----------------------------------------------------------------------------
2045 --
2046 -- Description
2047 -- This procedure is used to check that the Budget Update period falls within
2048 -- the Enrollment or Availability period.
2049 --
2050 -- Pre Conditions
2051 -- None.
2052 --
2053 -- In Parameters
2054 -- enrt_perd_id PK of record being inserted or updated.
2055 -- enrollment start date
2056 -- enrollment end date
2057 -- budget update start date
2058 -- budget update end date
2059 -- object_version_number Object version number of record being
2060 -- inserted or updated.
2061 --
2062 -- Post Success
2063 -- Processing continues
2064 --
2065 -- Post Failure
2066 -- Error handled by procedure
2067 --
2068 -- Access Status
2069 -- Internal table handler use only.
2070 --
2071 Procedure chk_bdgt_upd_period(p_enrt_perd_id in number,
2072 p_strt_dt in date,
2073 p_end_dt in date,
2074 p_bdgt_upd_strt_dt in date,
2075 p_bdgt_upd_end_dt in date,
2076 p_object_version_number in number) is
2077 --
2078 l_proc varchar2(72) := g_package||'chk_bdgt_upd_period';
2079 l_api_updating boolean;
2080 --
2081 Begin
2082 --
2083 hr_utility.set_location('Entering:'||l_proc, 5);
2084 --
2085 hr_api.mandatory_arg_error(p_api_name => l_proc,
2086 p_argument => 'strt_dt',
2087 p_argument_value => p_strt_dt);
2088 --
2089 hr_api.mandatory_arg_error(p_api_name => l_proc,
2090 p_argument => 'end_dt',
2091 p_argument_value => p_end_dt);
2092 --
2093
2094 l_api_updating := ben_enp_shd.api_updating
2095 (p_enrt_perd_id => p_enrt_perd_id,
2096 p_object_version_number => p_object_version_number);
2097 --
2098 -- check it if changing either start or end date.
2099 --
2100 if (l_api_updating
2101 and (nvl(p_bdgt_upd_strt_dt,hr_api.g_date) <> nvl(ben_enp_shd.g_old_rec.bdgt_upd_strt_dt,hr_api.g_date) or
2102 nvl(p_bdgt_upd_end_dt,hr_api.g_date) <> nvl(ben_enp_shd.g_old_rec.bdgt_upd_end_dt,hr_api.g_date) or
2103 p_strt_dt <> nvl(ben_enp_shd.g_old_rec.strt_dt,hr_api.g_date) or
2104 p_end_dt <> nvl(ben_enp_shd.g_old_rec.end_dt,hr_api.g_date)
2105 )
2106 or not l_api_updating)
2107 then
2108 --
2109 -- check if budget update period falls within enrollment or availability period
2113 --
2110 --
2111 if (p_bdgt_upd_strt_dt is not null) and
2112 not (p_bdgt_upd_strt_dt between p_strt_dt and p_end_dt) then
2114 -- raise error
2115 --
2116 fnd_message.set_name('BEN','BEN_92977_INVLD_BDGT_UPD_PERD');
2117 fnd_message.raise_error;
2118 --
2119 elsif (p_bdgt_upd_end_dt is not null) and
2120 not (p_bdgt_upd_end_dt between p_strt_dt and p_end_dt) then
2121 --
2122 -- raise error
2123 --
2124 fnd_message.set_name('BEN','BEN_92977_INVLD_BDGT_UPD_PERD');
2125 fnd_message.raise_error;
2126 --
2127 end if;
2128 --
2129 end if;
2130
2131 hr_utility.set_location('Leaving:'||l_proc,10);
2132 --
2133 end chk_bdgt_upd_period;
2134 --
2135 --
2136 -- ----------------------------------------------------------------------------
2137 -- |------< chk_ws_upd_period>------|
2138 -- ----------------------------------------------------------------------------
2139 --
2140 -- Description
2141 -- This procedure is used to check that the Worksheet Update period falls within
2142 -- the Enrollment or Availability period.
2143 --
2144 -- Pre Conditions
2145 -- None.
2146 --
2147 -- In Parameters
2148 -- enrt_perd_id PK of record being inserted or updated.
2149 -- enrollment start date
2150 -- enrollment end date
2151 -- worksheet update start date
2152 -- worksheet update end date
2153 -- object_version_number Object version number of record being
2154 -- inserted or updated.
2155 --
2156 -- Post Success
2157 -- Processing continues
2158 --
2159 -- Post Failure
2160 -- Error handled by procedure
2161 --
2162 -- Access Status
2163 -- Internal table handler use only.
2164 --
2165 Procedure chk_ws_upd_period(p_enrt_perd_id in number,
2166 p_strt_dt in date,
2167 p_end_dt in date,
2168 p_ws_upd_strt_dt in date,
2169 p_ws_upd_end_dt in date,
2170 p_object_version_number in number) is
2171 --
2172 l_proc varchar2(72) := g_package||'chk_ws_upd_period';
2173 l_api_updating boolean;
2174 --
2175 Begin
2176 --
2177 hr_utility.set_location('Entering:'||l_proc, 5);
2178 --
2179 hr_api.mandatory_arg_error(p_api_name => l_proc,
2180 p_argument => 'strt_dt',
2181 p_argument_value => p_strt_dt);
2182 --
2183 hr_api.mandatory_arg_error(p_api_name => l_proc,
2184 p_argument => 'end_dt',
2185 p_argument_value => p_end_dt);
2186 --
2187
2188 l_api_updating := ben_enp_shd.api_updating
2189 (p_enrt_perd_id => p_enrt_perd_id,
2190 p_object_version_number => p_object_version_number);
2191 --
2192 -- check it if changing either start or end date.
2193 --
2194 if (l_api_updating
2198 p_end_dt <> nvl(ben_enp_shd.g_old_rec.end_dt,hr_api.g_date)
2195 and (nvl(p_ws_upd_strt_dt,hr_api.g_date) <> nvl(ben_enp_shd.g_old_rec.ws_upd_strt_dt,hr_api.g_date) or
2196 nvl(p_ws_upd_end_dt,hr_api.g_date) <> nvl(ben_enp_shd.g_old_rec.ws_upd_end_dt,hr_api.g_date)or
2197 p_strt_dt <> nvl(ben_enp_shd.g_old_rec.strt_dt,hr_api.g_date) or
2199 )
2200 or not l_api_updating)
2201 then
2202 --
2203 -- check if worksheet update period falls within enrollment or availability period
2204 --
2205 if (p_ws_upd_strt_dt is not null) and
2206 not (p_ws_upd_strt_dt between p_strt_dt and p_end_dt) then
2207 --
2208 -- raise error
2209 --
2210 fnd_message.set_name('BEN','BEN_92978_INVLD_WS_UPD_PERD');
2211 fnd_message.raise_error;
2212 --
2213 elsif (p_ws_upd_end_dt is not null) and
2214 not (p_ws_upd_end_dt between p_strt_dt and p_end_dt) then
2215 --
2216 -- raise error
2217 --
2218 fnd_message.set_name('BEN','BEN_92978_INVLD_WS_UPD_PERD');
2219 fnd_message.raise_error;
2220 --
2221 end if;
2222 --
2223 end if;
2224
2225 hr_utility.set_location('Leaving:'||l_proc,10);
2226 --
2227 end chk_ws_upd_period;
2228 --
2229 --
2230 ---- ----------------------------------------------------------------------------
2234 -- Description
2231 -- |----------------------< chk_defer_flag_set_pln_plip >--------------------|
2232 -- ----------------------------------------------------------------------------
2233 --
2235 -- This procedure is used to check that if defer_deenrol_flag can be set for PNIP
2236 -- or if the Plan is in Program then can't be set at Plan Level.
2237 --
2238 -- In Parameters
2239 -- enrt_perd_id PK of record being inserted or updated.
2240 --
2241 -- object_version_number Object version number of record being
2242 -- inserted or updated.
2243 --
2244 -- Post Success
2245 -- Processing continues
2246 --
2247 -- Post Failure
2248 -- Error handled by procedure
2249 --
2250 -- Access Status
2251 -- Internal table handler use only.
2252 --
2253 procedure chk_defer_flag_set_pln_plip
2254 (p_enrt_perd_id in number
2255 ,p_popl_enrt_typ_cycl_id in number
2256 ,p_object_version_number in number
2257 ,p_effective_date in date
2258 ,p_defer_deenrol_flag in varchar2
2259 ,p_business_group_id in number
2260 ) is
2261 --
2262 cursor c_pl_cd is
2263 select pln.pl_cd
2264 from ben_pl_f pln
2265 ,ben_popl_enrt_typ_cycl_f pet
2266 ,ben_enrt_perd enp
2267 where pet.popl_enrt_typ_cycl_id = p_popl_enrt_typ_cycl_id
2268 and pet.pl_id = pln.pl_id
2269 and pln.business_group_id = p_business_group_id
2270 and pet.business_group_id = pln.business_group_id
2271 and p_effective_date between pln.effective_start_date and pln.effective_end_date
2272 and p_effective_date between pet.effective_start_date and pet.effective_end_date;
2273 --
2274 l_pl_cd ben_pl_f.pl_cd%TYPE;
2275 l_api_updating boolean;
2276 l_proc varchar2(72) := g_package||'chk_defer_flag_set_pln_plip';
2277 --
2278 begin
2279 --
2280 hr_utility.set_location('Entering:'|| l_proc, 9653);
2281 --
2282 l_api_updating := ben_enp_shd.api_updating
2283 (p_enrt_perd_id => p_enrt_perd_id,
2284 p_object_version_number => p_object_version_number);
2285 --
2286 if (l_api_updating
2287 and p_defer_deenrol_flag <> nvl(ben_enp_shd.g_old_rec.defer_deenrol_flag,hr_api.g_number)
2288 or not l_api_updating) and p_defer_deenrol_flag is not null then
2289 --
2290 open c_pl_cd;
2291 fetch c_pl_cd into l_pl_cd;
2292 close c_pl_cd;
2293 --
2294 if l_pl_cd = 'MSTBPGM' then
2295 --
2296 if p_defer_deenrol_flag = 'Y' then
2297 --
2298 fnd_message.set_name('BEN','BEN_94880_DEFER_FLAG_VALID_LVL');
2299 fnd_message.raise_error;
2300 --
2301 end if;
2302 --
2303 end if;
2304 --
2305 end if;
2306 hr_utility.set_location('Leaving:'|| l_proc, 9653);
2307 --
2308 end chk_defer_flag_set_pln_plip;
2309 --
2310
2311 --
2312 ---- ----------------------------------------------------------------------------
2313 -- |----------------------< chk_defer_flag_lookup >--------------------|
2314 -- ----------------------------------------------------------------------------
2315 --
2316 -- Description
2317 -- This procedure is used to check that the flag lookup value is valid.
2318 --
2319 -- In Parameters
2320 -- enrt_perd_id PK of record being inserted or updated.
2321 --
2322 -- object_version_number Object version number of record being
2323 -- inserted or updated.
2324 --
2325 -- Post Success
2326 -- Processing continues
2327 --
2328 -- Post Failure
2329 -- Error handled by procedure
2330 --
2331 -- Access Status
2332 -- Internal table handler use only.
2333 --
2334 procedure chk_defer_flag_lookup
2335 (p_enrt_perd_id in number
2336 ,p_effective_date in date
2337 ,p_defer_deenrol_flag in varchar2
2338 ,p_object_version_number in number
2339 ) is
2340 --
2341 l_api_updating boolean;
2342 l_proc varchar2(72) := g_package||'chk_defer_flag_lookup';
2343 --
2344 begin
2345 --
2346 hr_utility.set_location('Entering:'|| l_proc, 9653);
2347 --
2348 l_api_updating := ben_enp_shd.api_updating
2349 (p_enrt_perd_id => p_enrt_perd_id,
2350 p_object_version_number => p_object_version_number);
2351 --
2352 if (l_api_updating
2353 and p_defer_deenrol_flag <> nvl(ben_enp_shd.g_old_rec.defer_deenrol_flag,hr_api.g_varchar2)
2354 or not l_api_updating)
2355 and p_defer_deenrol_flag is not null then
2356 --
2357 if hr_api.not_exists_in_hr_lookups
2358 (p_lookup_type => 'YES_NO',
2359 p_lookup_code => p_defer_deenrol_flag,
2360 p_effective_date => p_effective_date) then
2361 --
2362 -- raise error as does not exist as lookup
2363 --
2364 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
2365 fnd_message.raise_error;
2366 --
2367 end if;
2368 --
2369 end if;
2370 --
2371 hr_utility.set_location('Leaving:'|| l_proc, 9653);
2372 --
2373 end chk_defer_flag_lookup;
2374 --
2375 --
2376 -- ----------------------------------------------------------------------------
2377 -- |-----------------------< insert_validate >-------------------------------|
2381 --
2378 -- ----------------------------------------------------------------------------
2379 Procedure insert_validate(p_rec in ben_enp_shd.g_rec_type
2380 ,p_effective_date in date) is
2382 l_proc varchar2(72) := g_package||'insert_validate';
2383 --
2384 Begin
2385 hr_utility.set_location('Entering:'||l_proc, 5);
2386 --
2387 -- Call all supporting business operations
2388 --
2389 --
2390 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2391 --
2392 chk_enrt_perd_id
2393 (p_enrt_perd_id => p_rec.enrt_perd_id,
2394 p_object_version_number => p_rec.object_version_number);
2395 --
2396 chk_yr_perd_id
2397 (p_enrt_perd_id => p_rec.enrt_perd_id,
2398 p_yr_perd_id => p_rec.yr_perd_id,
2399 p_popl_enrt_typ_cycl_id => p_rec.popl_enrt_typ_cycl_id,
2400 p_business_group_id => p_rec.business_group_id,
2401 p_object_version_number => p_rec.object_version_number);
2402 --
2403 chk_rt_end_dt_rl
2404 (p_enrt_perd_id => p_rec.enrt_perd_id,
2405 p_rt_end_dt_rl => p_rec.rt_end_dt_rl,
2406 p_business_group_id => p_rec.business_group_id,
2407 p_effective_date => p_effective_date,
2408 p_object_version_number => p_rec.object_version_number);
2409 --
2410 chk_rt_end_dt_cd
2411 (p_enrt_perd_id => p_rec.enrt_perd_id,
2412 p_rt_end_dt_cd => p_rec.rt_end_dt_cd,
2413 p_effective_date => p_effective_date,
2414 p_object_version_number => p_rec.object_version_number);
2415 --
2416 chk_rt_strt_dt_cd
2417 (p_enrt_perd_id => p_rec.enrt_perd_id,
2418 p_rt_strt_dt_cd => p_rec.rt_strt_dt_cd,
2419 p_effective_date => p_effective_date,
2420 p_object_version_number => p_rec.object_version_number);
2421 --
2422 chk_enrt_cvg_end_dt_rl
2423 (p_enrt_perd_id => p_rec.enrt_perd_id,
2424 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
2425 p_business_group_id => p_rec.business_group_id,
2426 p_effective_date => p_effective_date,
2427 p_object_version_number => p_rec.object_version_number);
2428 --
2429 chk_enrt_cvg_strt_dt_rl
2430 (p_enrt_perd_id => p_rec.enrt_perd_id,
2431 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
2432 p_business_group_id => p_rec.business_group_id,
2433 p_effective_date => p_effective_date,
2434 p_object_version_number => p_rec.object_version_number);
2435 --
2436 chk_enrt_cvg_end_dt_cd
2437 (p_enrt_perd_id => p_rec.enrt_perd_id,
2438 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
2439 p_effective_date => p_effective_date,
2440 p_object_version_number => p_rec.object_version_number);
2441 --
2442 chk_rt_strt_dt_rl
2443 (p_enrt_perd_id => p_rec.enrt_perd_id,
2444 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
2445 p_effective_date => p_effective_date,
2446 p_business_group_id => p_rec.business_group_id,
2447 p_object_version_number => p_rec.object_version_number);
2448 --
2449 chk_enrt_cvg_strt_dt_cd
2450 (p_enrt_perd_id => p_rec.enrt_perd_id,
2451 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
2452 p_effective_date => p_effective_date,
2453 p_object_version_number => p_rec.object_version_number);
2454 --
2455 chk_end_dt
2456 (p_enrt_perd_id => p_rec.enrt_perd_id,
2457 p_strt_dt => p_rec.strt_dt,
2458 p_end_dt => p_rec.end_dt,
2459 p_asnd_lf_evt_Dt => p_rec.asnd_lf_evt_dt,
2460 p_object_version_number => p_rec.object_version_number);
2461 --
2462 -- bug fix 2206551 - added chk_dup_asnd_lf_evt_dt
2463 --
2464 chk_dup_asnd_lf_evt_dt
2465 (p_enrt_perd_id => p_rec.enrt_perd_id,
2466 p_asnd_lf_evt_dt => p_rec.asnd_lf_evt_dt,
2467 p_popl_enrt_typ_cycl_id => p_rec.popl_enrt_typ_cycl_id,
2468 p_business_group_id => p_rec.business_group_id,
2469 p_object_version_number => p_rec.object_version_number);
2470 --
2471 --chk_dflt_enrt_dt
2472 --(p_enrt_perd_id => p_rec.enrt_perd_id,
2473 -- p_dflt_enrt_dt => p_rec.dflt_enrt_dt,
2474 -- p_end_dt => p_rec.end_dt,
2475 -- p_object_version_number => p_rec.object_version_number);
2476 --
2477 chk_cls_enrt_dt_to_use_cd
2478 (p_enrt_perd_id => p_rec.enrt_perd_id,
2479 p_cls_enrt_dt_to_use_cd => p_rec.cls_enrt_dt_to_use_cd,
2480 p_effective_date => p_effective_date,
2481 p_object_version_number => p_rec.object_version_number);
2482 --
2483 chk_enrl_strt_dt_dpndcy
2484 (p_enrt_perd_id => p_rec.enrt_perd_id,
2485 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
2486 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
2487 p_effective_date => p_effective_date,
2488 p_object_version_number => p_rec.object_version_number);
2489 --
2490 chk_enrl_end_dt_dpndcy
2491 (p_enrt_perd_id => p_rec.enrt_perd_id,
2492 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
2493 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
2494 p_effective_date => p_effective_date,
2495 p_object_version_number => p_rec.object_version_number);
2496 --
2497 chk_rt_strt_dt_dpndcy
2498 (p_enrt_perd_id => p_rec.enrt_perd_id,
2499 p_rt_strt_dt_cd => p_rec.rt_strt_dt_cd,
2500 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
2501 p_effective_date => p_effective_date,
2502 p_object_version_number => p_rec.object_version_number);
2503 --
2504 chk_rt_end_dt_dpndcy
2505 (p_enrt_perd_id => p_rec.enrt_perd_id,
2509 p_object_version_number => p_rec.object_version_number);
2506 p_rt_end_dt_cd => p_rec.rt_end_dt_cd,
2507 p_rt_end_dt_rl => p_rec.rt_end_dt_rl,
2508 p_effective_date => p_effective_date,
2510 --
2511 -- CWB
2512 --
2513 chk_bdgt_upd_end_dt
2514 (p_enrt_perd_id => p_rec.enrt_perd_id,
2515 p_bdgt_upd_strt_dt => p_rec.bdgt_upd_strt_dt,
2516 p_bdgt_upd_end_dt => p_rec.bdgt_upd_end_dt,
2517 p_object_version_number => p_rec.object_version_number);
2518 --
2519 chk_ws_upd_end_dt
2520 (p_enrt_perd_id => p_rec.enrt_perd_id,
2521 p_ws_upd_strt_dt => p_rec.ws_upd_strt_dt,
2522 p_ws_upd_end_dt => p_rec.ws_upd_end_dt,
2523 p_object_version_number => p_rec.object_version_number);
2524 --
2525 chk_bdgt_upd_period
2526 (p_enrt_perd_id => p_rec.enrt_perd_id,
2527 p_strt_dt => p_rec.strt_dt,
2528 p_end_dt => p_rec.end_dt,
2529 p_bdgt_upd_strt_dt => p_rec.bdgt_upd_strt_dt,
2530 p_bdgt_upd_end_dt => p_rec.bdgt_upd_end_dt,
2531 p_object_version_number => p_rec.object_version_number);
2532 --
2533 chk_ws_upd_period
2534 (p_enrt_perd_id => p_rec.enrt_perd_id,
2535 p_strt_dt => p_rec.strt_dt,
2536 p_end_dt => p_rec.end_dt,
2537 p_ws_upd_strt_dt => p_rec.ws_upd_strt_dt,
2538 p_ws_upd_end_dt => p_rec.ws_upd_end_dt,
2539 p_object_version_number => p_rec.object_version_number);
2540 --
2541 -- CWB
2542 --hr_utility.set_location(' p_rec.asnd_lf_evt_dt:'||p_rec.asnd_lf_evt_dt, 10);
2543 chk_cwb_validations
2544 (p_enrt_perd_id => p_rec.enrt_perd_id,
2545 p_data_freeze_date => p_rec.data_freeze_date,
2546 p_Sal_chg_reason_cd => p_rec.Sal_chg_reason_cd,
2547 p_Approval_mode_cd => p_rec.Approval_mode_cd,
2548 p_hrchy_ame_trn_cd => p_rec.hrchy_ame_trn_cd,
2549 p_hrchy_rl => p_rec.hrchy_rl,
2550 p_hrchy_ame_app_id => p_rec.hrchy_ame_app_id,
2551 p_hrchy_to_use_cd => p_rec.hrchy_to_use_cd,
2552 p_effective_date => p_rec.asnd_lf_evt_dt, --p_effective_date,
2553 p_pos_structure_version_id => p_rec.pos_structure_version_id,
2554 p_object_version_number => p_rec.object_version_number
2555 ) ;
2556
2557 chk_reinstate_ovrdn_cd(p_enrt_perd_id =>p_rec.enrt_perd_id,
2558 p_reinstate_ovrdn_cd =>p_rec.reinstate_ovrdn_cd,
2559 p_effective_date => p_effective_date,
2560 p_object_version_number => p_rec.object_version_number
2561 );
2562
2563 chk_reinstate_cd(p_enrt_perd_id =>p_rec.enrt_perd_id,
2564 p_reinstate_cd =>p_rec.reinstate_cd,
2565 p_effective_date => p_effective_date,
2566 p_object_version_number => p_rec.object_version_number
2567 );
2568 --
2569 chk_defer_flag_set_pln_plip
2570 (p_enrt_perd_id => p_rec.enrt_perd_id
2571 ,p_popl_enrt_typ_cycl_id => p_rec.popl_enrt_typ_cycl_id
2572 ,p_object_version_number => p_rec.object_version_number
2573 ,p_effective_date => p_effective_date
2574 ,p_defer_deenrol_flag => p_rec.defer_deenrol_flag
2575 ,p_business_group_id => p_rec.business_group_id
2576 );
2577 --
2578 chk_defer_flag_lookup
2579 (p_enrt_perd_id => p_rec.enrt_perd_id
2580 ,p_effective_date => p_effective_date
2581 ,p_defer_deenrol_flag => p_rec.defer_deenrol_flag
2582 ,p_object_version_number => p_rec.object_version_number
2583 );
2584 --
2585 hr_utility.set_location(' Leaving:'||l_proc, 10);
2586 --
2587 end insert_validate;
2588 -- ----------------------------------------------------------------------------
2589 -- |---------------------------< update_validate >----------------------------|
2590 -- ----------------------------------------------------------------------------
2591 Procedure update_validate(p_rec in ben_enp_shd.g_rec_type
2592 ,p_effective_date in date) is
2593 --
2594 l_proc varchar2(72) := g_package||'update_validate';
2595 --
2596 Begin
2597 hr_utility.set_location('Entering:'||l_proc, 5);
2598 --
2599 -- Call all supporting business operations
2600 --
2601 --
2602 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
2603 --
2604 chk_enrt_perd_id
2605 (p_enrt_perd_id => p_rec.enrt_perd_id,
2606 p_object_version_number => p_rec.object_version_number);
2607 --
2608 chk_yr_perd_id
2609 (p_enrt_perd_id => p_rec.enrt_perd_id,
2610 p_yr_perd_id => p_rec.yr_perd_id,
2611 p_popl_enrt_typ_cycl_id => p_rec.popl_enrt_typ_cycl_id,
2612 p_business_group_id => p_rec.business_group_id,
2613 p_object_version_number => p_rec.object_version_number);
2614 --
2615 chk_end_dt
2616 (p_enrt_perd_id => p_rec.enrt_perd_id,
2617 p_strt_dt => p_rec.strt_dt,
2618 p_end_dt => p_rec.end_dt,
2619 p_asnd_lf_evt_dt => p_rec.asnd_lf_evt_dt,
2620 p_object_version_number => p_rec.object_version_number);
2621 --
2622 -- bug fix 2206551 - added chk_dup_asnd_lf_evt_dt
2623 --
2624 chk_dup_asnd_lf_evt_dt
2625 (p_enrt_perd_id => p_rec.enrt_perd_id,
2626 p_asnd_lf_evt_dt => p_rec.asnd_lf_evt_dt,
2627 p_popl_enrt_typ_cycl_id => p_rec.popl_enrt_typ_cycl_id,
2628 p_business_group_id => p_rec.business_group_id,
2629 p_object_version_number => p_rec.object_version_number);
2630 --
2634 -- p_end_dt => p_rec.end_dt,
2631 --chk_dflt_enrt_dt
2632 --(p_enrt_perd_id => p_rec.enrt_perd_id,
2633 -- p_dflt_enrt_dt => p_rec.dflt_enrt_dt,
2635 -- p_object_version_number => p_rec.object_version_number);
2636 --
2637 chk_rt_end_dt_rl
2638 (p_enrt_perd_id => p_rec.enrt_perd_id,
2639 p_rt_end_dt_rl => p_rec.rt_end_dt_rl,
2640 p_business_group_id => p_rec.business_group_id,
2641 p_effective_date => p_effective_date,
2642 p_object_version_number => p_rec.object_version_number);
2643 --
2644 chk_rt_end_dt_cd
2645 (p_enrt_perd_id => p_rec.enrt_perd_id,
2646 p_rt_end_dt_cd => p_rec.rt_end_dt_cd,
2647 p_effective_date => p_effective_date,
2648 p_object_version_number => p_rec.object_version_number);
2649 --
2650 chk_rt_strt_dt_cd
2651 (p_enrt_perd_id => p_rec.enrt_perd_id,
2652 p_rt_strt_dt_cd => p_rec.rt_strt_dt_cd,
2653 p_effective_date => p_effective_date,
2654 p_object_version_number => p_rec.object_version_number);
2655 --
2656 chk_enrt_cvg_end_dt_rl
2657 (p_enrt_perd_id => p_rec.enrt_perd_id,
2658 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
2659 p_business_group_id => p_rec.business_group_id,
2660 p_effective_date => p_effective_date,
2661 p_object_version_number => p_rec.object_version_number);
2662 --
2663 chk_enrt_cvg_strt_dt_rl
2664 (p_enrt_perd_id => p_rec.enrt_perd_id,
2665 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
2666 p_business_group_id => p_rec.business_group_id,
2667 p_effective_date => p_effective_date,
2668 p_object_version_number => p_rec.object_version_number);
2669 --
2670 chk_enrt_cvg_end_dt_cd
2671 (p_enrt_perd_id => p_rec.enrt_perd_id,
2672 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
2673 p_effective_date => p_effective_date,
2674 p_object_version_number => p_rec.object_version_number);
2675 --
2676 chk_rt_strt_dt_rl
2677 (p_enrt_perd_id => p_rec.enrt_perd_id,
2678 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
2679 p_effective_date => p_effective_date,
2680 p_business_group_id => p_rec.business_group_id,
2681 p_object_version_number => p_rec.object_version_number);
2682 --
2683 chk_enrt_cvg_strt_dt_cd
2684 (p_enrt_perd_id => p_rec.enrt_perd_id,
2685 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
2686 p_effective_date => p_effective_date,
2687 p_object_version_number => p_rec.object_version_number);
2688 --
2689 chk_cls_enrt_dt_to_use_cd
2690 (p_enrt_perd_id => p_rec.enrt_perd_id,
2691 p_cls_enrt_dt_to_use_cd => p_rec.cls_enrt_dt_to_use_cd,
2692 p_effective_date => p_effective_date,
2693 p_object_version_number => p_rec.object_version_number);
2694 --
2695 chk_enrl_strt_dt_dpndcy
2696 (p_enrt_perd_id => p_rec.enrt_perd_id,
2697 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
2698 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
2699 p_effective_date => p_effective_date,
2700 p_object_version_number => p_rec.object_version_number);
2701 --
2702 chk_enrl_end_dt_dpndcy
2703 (p_enrt_perd_id => p_rec.enrt_perd_id,
2704 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
2705 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
2706 p_effective_date => p_effective_date,
2707 p_object_version_number => p_rec.object_version_number);
2708 --
2709 chk_rt_strt_dt_dpndcy
2710 (p_enrt_perd_id => p_rec.enrt_perd_id,
2711 p_rt_strt_dt_cd => p_rec.rt_strt_dt_cd,
2712 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
2713 p_effective_date => p_effective_date,
2714 p_object_version_number => p_rec.object_version_number);
2715 --
2716 chk_rt_end_dt_dpndcy
2717 (p_enrt_perd_id => p_rec.enrt_perd_id,
2718 p_rt_end_dt_cd => p_rec.rt_end_dt_cd,
2719 p_rt_end_dt_rl => p_rec.rt_end_dt_rl,
2720 p_effective_date => p_effective_date,
2721 p_object_version_number => p_rec.object_version_number);
2722 --
2723 --
2724 -- CWB
2725 --
2726 chk_bdgt_upd_end_dt
2727 (p_enrt_perd_id => p_rec.enrt_perd_id,
2728 p_bdgt_upd_strt_dt => p_rec.bdgt_upd_strt_dt,
2729 p_bdgt_upd_end_dt => p_rec.bdgt_upd_end_dt,
2730 p_object_version_number => p_rec.object_version_number);
2731 --
2732 chk_ws_upd_end_dt
2733 (p_enrt_perd_id => p_rec.enrt_perd_id,
2734 p_ws_upd_strt_dt => p_rec.ws_upd_strt_dt,
2735 p_ws_upd_end_dt => p_rec.ws_upd_end_dt,
2736 p_object_version_number => p_rec.object_version_number);
2737 --
2738 chk_bdgt_upd_period
2739 (p_enrt_perd_id => p_rec.enrt_perd_id,
2740 p_strt_dt => p_rec.strt_dt,
2741 p_end_dt => p_rec.end_dt,
2742 p_bdgt_upd_strt_dt => p_rec.bdgt_upd_strt_dt,
2743 p_bdgt_upd_end_dt => p_rec.bdgt_upd_end_dt,
2744 p_object_version_number => p_rec.object_version_number);
2745 --
2746 chk_ws_upd_period
2747 (p_enrt_perd_id => p_rec.enrt_perd_id,
2748 p_strt_dt => p_rec.strt_dt,
2749 p_end_dt => p_rec.end_dt,
2750 p_ws_upd_strt_dt => p_rec.ws_upd_strt_dt,
2751 p_ws_upd_end_dt => p_rec.ws_upd_end_dt,
2752 p_object_version_number => p_rec.object_version_number);
2753 --
2754 -- CWB
2755 chk_cwb_validations
2756 (p_enrt_perd_id => p_rec.enrt_perd_id,
2757 p_data_freeze_date => p_rec.data_freeze_date,
2758 p_Sal_chg_reason_cd => p_rec.Sal_chg_reason_cd,
2759 p_Approval_mode_cd => p_rec.Approval_mode_cd,
2760 p_hrchy_ame_trn_cd => p_rec.hrchy_ame_trn_cd,
2761 p_hrchy_rl => p_rec.hrchy_rl,
2762 p_hrchy_ame_app_id => p_rec.hrchy_ame_app_id,
2763 p_hrchy_to_use_cd => p_rec.hrchy_to_use_cd,
2764 p_effective_date => p_rec.asnd_lf_evt_dt, --p_effective_date,
2765 p_pos_structure_version_id => p_rec.pos_structure_version_id,
2766 p_object_version_number => p_rec.object_version_number
2767 ) ;
2768 --
2769 --Reinstate Lookup Validations
2770 chk_reinstate_ovrdn_cd(p_enrt_perd_id =>p_rec.enrt_perd_id,
2771 p_reinstate_ovrdn_cd =>p_rec.reinstate_ovrdn_cd,
2772 p_effective_date => p_effective_date,
2773 p_object_version_number => p_rec.object_version_number
2774 );
2775
2776 chk_reinstate_cd(p_enrt_perd_id =>p_rec.enrt_perd_id,
2777 p_reinstate_cd =>p_rec.reinstate_cd,
2778 p_effective_date => p_effective_date,
2779 p_object_version_number => p_rec.object_version_number
2780 );
2781 --
2782 chk_defer_flag_set_pln_plip
2783 (p_enrt_perd_id => p_rec.enrt_perd_id
2784 ,p_popl_enrt_typ_cycl_id => p_rec.popl_enrt_typ_cycl_id
2785 ,p_object_version_number => p_rec.object_version_number
2786 ,p_effective_date => p_effective_date
2787 ,p_defer_deenrol_flag => p_rec.defer_deenrol_flag
2788 ,p_business_group_id => p_rec.business_group_id
2789 );
2790 --
2791 chk_defer_flag_lookup
2792 (p_enrt_perd_id => p_rec.enrt_perd_id
2793 ,p_effective_date => p_effective_date
2794 ,p_defer_deenrol_flag => p_rec.defer_deenrol_flag
2795 ,p_object_version_number => p_rec.object_version_number
2796 );
2797 --
2798 hr_utility.set_location(' Leaving:'||l_proc, 10);
2799 End update_validate;
2800 --
2801 -- ----------------------------------------------------------------------------
2802 -- |---------------------------< delete_validate >----------------------------|
2803 -- ----------------------------------------------------------------------------
2804 Procedure delete_validate(p_rec in ben_enp_shd.g_rec_type
2805 ,p_effective_date in date) is
2806 --
2807 l_proc varchar2(72) := g_package||'delete_validate';
2808 --
2809 Begin
2810 hr_utility.set_location('Entering:'||l_proc, 5);
2811 --
2812 -- Call all supporting business operations
2813 --
2814 hr_utility.set_location(' Leaving:'||l_proc, 10);
2815 End delete_validate;
2816 --
2817 --
2818 -- ---------------------------------------------------------------------------
2819 -- |---------------------< return_legislation_code >-------------------------|
2820 -- ---------------------------------------------------------------------------
2821 --
2822 function return_legislation_code
2823 (p_enrt_perd_id in number) return varchar2 is
2824 --
2825 -- Declare cursor
2826 --
2827 cursor csr_leg_code is
2828 select a.legislation_code
2829 from per_business_groups a,
2830 ben_enrt_perd b
2831 where b.enrt_perd_id = p_enrt_perd_id
2832 and a.business_group_id = b.business_group_id;
2833 --
2834 -- Declare local variables
2835 --
2836 l_legislation_code varchar2(150);
2837 l_proc varchar2(72) := g_package||'return_legislation_code';
2838 --
2839 begin
2840 --
2841 hr_utility.set_location('Entering:'|| l_proc, 10);
2842 --
2843 -- Ensure that all the mandatory parameter are not null
2844 --
2845 hr_api.mandatory_arg_error(p_api_name => l_proc,
2846 p_argument => 'enrt_perd_id',
2847 p_argument_value => p_enrt_perd_id);
2848 --
2849 open csr_leg_code;
2850 --
2851 fetch csr_leg_code into l_legislation_code;
2852 --
2853 if csr_leg_code%notfound then
2854 --
2855 close csr_leg_code;
2856 --
2857 -- The primary key is invalid therefore we must error
2858 --
2859 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
2860 hr_utility.raise_error;
2861 --
2862 end if;
2863 --
2864 close csr_leg_code;
2865 --
2866 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2867 --
2868 return l_legislation_code;
2869 --
2870 end return_legislation_code;
2871 --
2872 end ben_enp_bus;