[Home] [Help]
PACKAGE BODY: APPS.BEN_LEN_BUS
Source
4 -- ----------------------------------------------------------------------------
1 Package Body ben_len_bus as
2 /* $Header: belenrhi.pkb 120.2.12020000.2 2012/07/03 12:12:43 amnaraya ship $ */
3 --
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' ben_len_bus.'; -- Global package name
9 --
10 -- ----------------------------------------------------------------------------
11 -- |------< chk_lee_rsn_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 -- lee_rsn_id PK of record being inserted or updated.
24 -- effective_date Effective Date of session
25 -- object_version_number Object version number of record being
26 -- inserted or updated.
27 --
28 -- Post Success
29 -- Processing continues
30 --
31 -- Post Failure
32 -- Errors handled by the procedure
33 --
34 -- Access Status
35 -- Internal table handler use only.
36 --
37 Procedure chk_lee_rsn_id(p_lee_rsn_id in number,
38 p_effective_date in date,
39 p_object_version_number in number) is
40 --
41 l_proc varchar2(72) := g_package||'chk_lee_rsn_id';
42 l_api_updating boolean;
43 --
44 Begin
45 --
46 hr_utility.set_location('Entering:'||l_proc, 5);
47 --
48 l_api_updating := ben_len_shd.api_updating
49 (p_effective_date => p_effective_date,
50 p_lee_rsn_id => p_lee_rsn_id,
51 p_object_version_number => p_object_version_number);
52 --
53 if (l_api_updating
54 and nvl(p_lee_rsn_id,hr_api.g_number)
55 <> ben_len_shd.g_old_rec.lee_rsn_id) then
56 --
57 -- raise error as PK has changed
58 --
59 ben_len_shd.constraint_error('BEN_LEE_RSN_F_PK');
60 --
61 elsif not l_api_updating then
62 --
63 -- check if PK is null
64 --
65 if p_lee_rsn_id is not null then
66 --
67 -- raise error as PK is not null
68 --
69 ben_len_shd.constraint_error('BEN_LEE_RSN_F_PK');
70 --
71 end if;
72 --
73 end if;
74 --
75 hr_utility.set_location('Leaving:'||l_proc, 10);
76 --
77 End chk_lee_rsn_id;
78 --
79 -- ----------------------------------------------------------------------------
80 -- |--------------< chk_inelig_and_dflts >--------------------|
81 -- ----------------------------------------------------------------------------
82 --
83 -- Description
84 -- This procedure is used to check that Days after occurence of LE for
85 -- ineligibility is greater than Days after occurence of LE to apply defaults
86 -- if both are not null.
87 --
88 -- Pre Conditions
89 -- None.
90 --
91 -- In Parameters
92 -- dys_aftr_end_to_dflt_num Days after occurence of LE to apply defaults
93 -- dys_no_enrl_not_elig_num Days after occurence of LE for ineligibility
94 --
95 --
96 -- Post Success
97 -- Processing continues
98 --
99 -- Post Failure
100 -- Errors handled by the procedure
101 --
102 -- Access Status
103 -- Internal table handler use only.
104 --
105 Procedure chk_inelig_and_dflts
106 (p_dys_aftr_end_to_dflt_num in number,
107 p_dys_no_enrl_not_elig_num in number) is
108 --
109 l_proc varchar2(72) := g_package||'chk_inelig_and_dflts';
110 --
111 Begin
112 --
113 hr_utility.set_location('Entering:'||l_proc, 5);
114 --
115 if ((p_dys_aftr_end_to_dflt_num is not null and
116 p_dys_no_enrl_not_elig_num is not null)and
117 (p_dys_no_enrl_not_elig_num < p_dys_aftr_end_to_dflt_num)) then
118 --
119 -- raise error if both arguments are not null and p_dys_no_enrl_not_elig
120 -- _num is less than p_dys_aftr_end_to_dflt_num
121 --
122 fnd_message.set_name('BEN','BEN_91618_DFLT_LS_THN_INELG');
123 fnd_message.raise_error;
124 --
125 --
126 end if;
127 --
128 hr_utility.set_location('Leaving:'||l_proc, 10);
129 --
130 End chk_inelig_and_dflts;
131 --
132 -- ----------------------------------------------------------------------------
133 -- |------< chk_enrt_cvg_strt_dt_rl >------|
134 -- ----------------------------------------------------------------------------
135 --
136 -- Description
137 -- This procedure is used to check that the Formula Rule is valid.
138 --
139 -- Pre Conditions
140 -- None.
141 --
142 -- In Parameters
143 -- lee_rsn_id PK of record being inserted or updated.
144 -- enrt_cvg_strt_dt_rl Value of formula rule id.
145 -- effective_date effective date
146 -- object_version_number Object version number of record being
147 -- inserted or updated.
148 --
149 -- Post Success
150 -- Processing continues
151 --
152 -- Post Failure
153 -- Error handled by procedure
154 --
155 -- Access Status
159 p_enrt_cvg_strt_dt_rl in number,
156 -- Internal table handler use only.
157 --
158 Procedure chk_enrt_cvg_strt_dt_rl(p_lee_rsn_id in number,
160 p_business_group_id in number,
161 p_effective_date in date,
162 p_object_version_number in number) is
163 --
164 l_proc varchar2(72) := g_package||'chk_enrt_cvg_strt_dt_rl';
165 l_api_updating boolean;
166 --
167 Begin
168 --
169 hr_utility.set_location('Entering:'||l_proc, 5);
170 --
171 l_api_updating := ben_len_shd.api_updating
172 (p_lee_rsn_id => p_lee_rsn_id,
173 p_effective_date => p_effective_date,
174 p_object_version_number => p_object_version_number);
175 --
176 if (l_api_updating
177 and nvl(p_enrt_cvg_strt_dt_rl,hr_api.g_number)
178 <> ben_len_shd.g_old_rec.enrt_cvg_strt_dt_rl
179 or not l_api_updating)
180 and p_enrt_cvg_strt_dt_rl is not null then
181 --
182 -- check if value of formula rule is valid.
183 --
184 if not benutils.formula_exists
185 (p_formula_id => p_enrt_cvg_strt_dt_rl,
186 p_formula_type_id => -29,
187 p_business_group_id => p_business_group_id,
188 p_effective_date => p_effective_date) then
189 --
190 -- raise error
191 --
192 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
193 fnd_message.set_token('ID',p_enrt_cvg_strt_dt_rl);
194 fnd_message.set_token('TYPE_ID',-29);
195 fnd_message.raise_error;
196 --
197 end if;
198 --
199 end if;
200 --
201 hr_utility.set_location('Leaving:'||l_proc,10);
202 --
203 end chk_enrt_cvg_strt_dt_rl;
204 --
205 -- ----------------------------------------------------------------------------
206 -- |------< chk_enrt_cvg_end_dt_rl >------|
207 -- ----------------------------------------------------------------------------
208 --
209 -- Description
210 -- This procedure is used to check that the Formula Rule is valid.
211 --
212 -- Pre Conditions
213 -- None.
214 --
215 -- In Parameters
216 -- lee_rsn_id PK of record being inserted or updated.
217 -- enrt_cvg_end_dt_rl Value of formula rule id.
218 -- effective_date effective date
219 -- object_version_number Object version number of record being
220 -- inserted or updated.
221 --
222 -- Post Success
223 -- Processing continues
224 --
225 -- Post Failure
226 -- Error handled by procedure
227 --
228 -- Access Status
229 -- Internal table handler use only.
230 --
231 Procedure chk_enrt_cvg_end_dt_rl(p_lee_rsn_id in number,
232 p_enrt_cvg_end_dt_rl in number,
233 p_business_group_id in number,
234 p_effective_date in date,
235 p_object_version_number in number) is
236 --
237 l_proc varchar2(72) := g_package||'chk_enrt_cvg_end_dt_rl';
238 l_api_updating boolean;
239 --
240 Begin
241 --
242 hr_utility.set_location('Entering:'||l_proc, 5);
243 --
244 l_api_updating := ben_len_shd.api_updating
245 (p_lee_rsn_id => p_lee_rsn_id,
246 p_effective_date => p_effective_date,
247 p_object_version_number => p_object_version_number);
248 --
249 if (l_api_updating
250 and nvl(p_enrt_cvg_end_dt_rl,hr_api.g_number)
251 <> ben_len_shd.g_old_rec.enrt_cvg_end_dt_rl
252 or not l_api_updating)
253 and p_enrt_cvg_end_dt_rl is not null then
254 --
255 -- check if value of formula rule is valid.
256 --
257 if not benutils.formula_exists
258 (p_formula_id => p_enrt_cvg_end_dt_rl,
259 p_formula_type_id => -30,
260 p_business_group_id => p_business_group_id,
261 p_effective_date => p_effective_date) then
262 --
263 -- raise error
264 --
265 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
266 fnd_message.set_token('ID',p_enrt_cvg_end_dt_rl);
267 fnd_message.set_token('TYPE_ID',-30);
268 fnd_message.raise_error;
269 --
270 end if;
271 --
272 end if;
273 --
274 hr_utility.set_location('Leaving:'||l_proc,10);
275 --
276 end chk_enrt_cvg_end_dt_rl;
277 --
278 -- ----------------------------------------------------------------------------
279 -- |------< chk_rt_strt_dt_rl >------|
280 -- ----------------------------------------------------------------------------
281 --
282 -- Description
283 -- This procedure is used to check that the Formula Rule is valid.
284 --
285 -- Pre Conditions
286 -- None.
287 --
288 -- In Parameters
289 -- lee_rsn_id PK of record being inserted or updated.
290 -- rt_strt_dt_rl Value of formula rule id.
291 -- effective_date effective date
292 -- object_version_number Object version number of record being
293 -- inserted or updated.
294 --
295 -- Post Success
296 -- Processing continues
297 --
298 -- Post Failure
299 -- Error handled by procedure
300 --
301 -- Access Status
302 -- Internal table handler use only.
303 --
304 Procedure chk_rt_strt_dt_rl(p_lee_rsn_id in number,
305 p_rt_strt_dt_rl in number,
306 p_business_group_id in number,
310 l_proc varchar2(72) := g_package||'chk_rt_strt_dt_rl';
307 p_effective_date in date,
308 p_object_version_number in number) is
309 --
311 l_api_updating boolean;
312 --
313 Begin
314 --
315 hr_utility.set_location('Entering:'||l_proc, 5);
316 --
317 l_api_updating := ben_len_shd.api_updating
318 (p_lee_rsn_id => p_lee_rsn_id,
319 p_effective_date => p_effective_date,
320 p_object_version_number => p_object_version_number);
321 --
322 if (l_api_updating
323 and nvl(p_rt_strt_dt_rl,hr_api.g_number)
324 <> ben_len_shd.g_old_rec.rt_strt_dt_rl
325 or not l_api_updating)
326 and p_rt_strt_dt_rl is not null then
327 --
328 -- check if value of formula rule is valid.
329 --
330 if not benutils.formula_exists
331 (p_formula_id => p_rt_strt_dt_rl,
332 p_formula_type_id => -66,
333 p_business_group_id => p_business_group_id,
334 p_effective_date => p_effective_date) then
335 --
336 -- raise error
337 --
338 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
339 fnd_message.set_token('ID',p_rt_strt_dt_rl);
340 fnd_message.set_token('TYPE_ID',-66);
341 fnd_message.raise_error;
342 --
343 end if;
344 --
345 end if;
346 --
347 hr_utility.set_location('Leaving:'||l_proc,10);
348 --
349 end chk_rt_strt_dt_rl;
350 --
351 -- ----------------------------------------------------------------------------
352 -- |------< chk_rt_strt_dt_cd >------|
353 -- ----------------------------------------------------------------------------
354 --
355 -- Description
356 -- This procedure is used to check that the lookup value is valid.
357 --
358 -- Pre Conditions
359 -- None.
360 --
361 -- In Parameters
362 -- lee_rsn_id PK of record being inserted or updated.
363 -- rt_strt_dt_cd Value of lookup code.
364 -- effective_date effective date
365 -- object_version_number Object version number of record being
366 -- inserted or updated.
367 --
368 -- Post Success
369 -- Processing continues
370 --
371 -- Post Failure
372 -- Error handled by procedure
373 --
374 -- Access Status
375 -- Internal table handler use only.
376 --
377 Procedure chk_rt_strt_dt_cd(p_lee_rsn_id in number,
378 p_rt_strt_dt_cd in varchar2,
379 p_effective_date in date,
380 p_object_version_number in number) is
381 --
382 l_proc varchar2(72) := g_package||'chk_rt_strt_dt_cd';
383 l_api_updating boolean;
384 --
385 Begin
386 --
387 hr_utility.set_location('Entering:'||l_proc, 5);
388 --
389 l_api_updating := ben_len_shd.api_updating
390 (p_lee_rsn_id => p_lee_rsn_id,
391 p_effective_date => p_effective_date,
392 p_object_version_number => p_object_version_number);
393 --
394 if (l_api_updating
395 and p_rt_strt_dt_cd
396 <> nvl(ben_len_shd.g_old_rec.rt_strt_dt_cd,hr_api.g_varchar2)
397 or not l_api_updating)
398 and p_rt_strt_dt_cd is not null then
399 --
400 -- check if value of lookup falls within lookup type.
401 --
402 if hr_api.not_exists_in_hr_lookups
403 (p_lookup_type => 'BEN_RT_STRT',
404 p_lookup_code => p_rt_strt_dt_cd,
405 p_effective_date => p_effective_date) then
406 --
407 -- raise error as does not exist as lookup
408 --
409 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
410 fnd_message.set_token('FIELD', 'p_rt_strt_dt_cd');
411 fnd_message.set_token('TYPE', 'BEN_RT_STRT');
412 fnd_message.raise_error;
413 --
414 end if;
415 --
416 end if;
417 --
418 hr_utility.set_location('Leaving:'||l_proc,10);
419 --
420 end chk_rt_strt_dt_cd;
421 --
422
423 -- ----------------------------------------------------------------------------
424 -- |------< chk_rt_end_dt_rl >------|
425 -- ----------------------------------------------------------------------------
426 --
427 -- Description
428 -- This procedure is used to check that the Formula Rule is valid.
429 --
430 -- Pre Conditions
431 -- None.
432 --
433 -- In Parameters
434 -- lee_rsn_id PK of record being inserted or updated.
435 -- rt_end_dt_rl Value of formula rule id.
436 -- effective_date effective date
437 -- object_version_number Object version number of record being
438 -- inserted or updated.
439 --
440 -- Post Success
441 -- Processing continues
442 --
443 -- Post Failure
444 -- Error handled by procedure
445 --
446 -- Access Status
447 -- Internal table handler use only.
448 --
449 Procedure chk_rt_end_dt_rl(p_lee_rsn_id in number,
450 p_rt_end_dt_rl in number,
451 p_business_group_id in number,
452 p_effective_date in date,
453 p_object_version_number in number) is
454 --
455 l_proc varchar2(72) := g_package||'chk_rt_end_dt_rl';
456 l_api_updating boolean;
457 --
458 Begin
459 --
460 hr_utility.set_location('Entering:'||l_proc, 5);
461 --
462 l_api_updating := ben_len_shd.api_updating
466 --
463 (p_lee_rsn_id => p_lee_rsn_id,
464 p_effective_date => p_effective_date,
465 p_object_version_number => p_object_version_number);
467 if (l_api_updating
468 and nvl(p_rt_end_dt_rl,hr_api.g_number)
469 <> ben_len_shd.g_old_rec.rt_end_dt_rl
470 or not l_api_updating)
471 and p_rt_end_dt_rl is not null then
472 --
473 -- check if value of formula rule is valid.
474 --
475 if not benutils.formula_exists
476 (p_formula_id => p_rt_end_dt_rl,
477 p_formula_type_id => -67,
478 p_business_group_id => p_business_group_id,
479 p_effective_date => p_effective_date) then
480 --
481 -- raise error
482 --
483 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
484 fnd_message.set_token('ID',p_rt_end_dt_rl);
485 fnd_message.set_token('TYPE_ID',-67);
486 fnd_message.raise_error;
487 --
488 end if;
489 --
490 end if;
491 --
492 hr_utility.set_location('Leaving:'||l_proc,10);
493 --
494 end chk_rt_end_dt_rl;
495 --
496 -- ----------------------------------------------------------------------------
497 -- |------< chk_rt_end_dt_cd >------|
498 -- ----------------------------------------------------------------------------
499 --
500 -- Description
501 -- This procedure is used to check that the lookup value is valid.
502 --
503 -- Pre Conditions
504 -- None.
505 --
506 -- In Parameters
507 -- lee_rsn_id PK of record being inserted or updated.
508 -- rt_end_dt_cd Value of lookup code.
509 -- effective_date effective date
510 -- object_version_number Object version number of record being
511 -- inserted or updated.
512 --
513 -- Post Success
514 -- Processing continues
515 --
516 -- Post Failure
517 -- Error handled by procedure
518 --
519 -- Access Status
520 -- Internal table handler use only.
521 --
522 Procedure chk_rt_end_dt_cd(p_lee_rsn_id in number,
523 p_rt_end_dt_cd in varchar2,
524 p_effective_date in date,
525 p_object_version_number in number) is
526 --
527 l_proc varchar2(72) := g_package||'chk_rt_end_dt_cd';
528 l_api_updating boolean;
529 --
530 Begin
531 --
532 hr_utility.set_location('Entering:'||l_proc, 5);
533 --
534 l_api_updating := ben_len_shd.api_updating
535 (p_lee_rsn_id => p_lee_rsn_id,
536 p_effective_date => p_effective_date,
537 p_object_version_number => p_object_version_number);
538 --
539 if (l_api_updating
540 and p_rt_end_dt_cd
541 <> nvl(ben_len_shd.g_old_rec.rt_end_dt_cd,hr_api.g_varchar2)
542 or not l_api_updating)
543 and p_rt_end_dt_cd is not null then
544 --
545 -- check if value of lookup falls within lookup type.
546 --
547 if hr_api.not_exists_in_hr_lookups
548 (p_lookup_type => 'BEN_RT_END',
549 p_lookup_code => p_rt_end_dt_cd,
550 p_effective_date => p_effective_date) then
551 --
552 -- raise error as does not exist as lookup
553 --
554 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
555 fnd_message.set_token('FIELD', 'p_rt_end_dt_cd');
556 fnd_message.set_token('TYPE', 'BEN_RT_END');
557 fnd_message.raise_error;
558 --
559 end if;
560 --
561 end if;
562 --
563 hr_utility.set_location('Leaving:'||l_proc,10);
564 --
565 end chk_rt_end_dt_cd;
566 --
567 -- ----------------------------------------------------------------------------
568 -- |------< chk_enrt_perd_end_dt_rl >------|
569 -- ----------------------------------------------------------------------------
570 --
571 -- Description
572 -- This procedure is used to check that the Formula Rule is valid.
573 --
574 -- Pre Conditions
575 -- None.
576 --
577 -- In Parameters
578 -- lee_rsn_id PK of record being inserted or updated.
579 -- enrt_perd_end_dt_rl Value of formula rule id.
580 -- effective_date effective date
581 -- object_version_number Object version number of record being
582 -- inserted or updated.
583 --
584 -- Post Success
585 -- Processing continues
586 --
587 -- Post Failure
588 -- Error handled by procedure
589 --
590 -- Access Status
591 -- Internal table handler use only.
592 --
593 Procedure chk_enrt_perd_end_dt_rl(p_lee_rsn_id in number,
594 p_enrt_perd_end_dt_rl in number,
595 p_business_group_id in number,
596 p_effective_date in date,
597 p_object_version_number in number) is
598 --
599 l_proc varchar2(72) := g_package||'chk_enrt_perd_end_dt_rl';
600 l_api_updating boolean;
601 --
602 Begin
603 --
604 hr_utility.set_location('Entering:'||l_proc, 5);
605 --
606 l_api_updating := ben_len_shd.api_updating
607 (p_lee_rsn_id => p_lee_rsn_id,
608 p_effective_date => p_effective_date,
609 p_object_version_number => p_object_version_number);
610 --
611 if (l_api_updating
612 and nvl(p_enrt_perd_end_dt_rl,hr_api.g_number)
613 <> ben_len_shd.g_old_rec.enrt_perd_end_dt_rl
614 or not l_api_updating)
618 --
615 and p_enrt_perd_end_dt_rl is not null then
616 --
617 -- check if value of formula rule is valid.
619 if not benutils.formula_exists
620 (p_formula_id => p_enrt_perd_end_dt_rl,
621 p_formula_type_id => -503,
622 p_business_group_id => p_business_group_id,
623 p_effective_date => p_effective_date) then
624 --
625 -- raise error
626 --
627 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
628 fnd_message.set_token('ID',p_enrt_perd_end_dt_rl);
629 fnd_message.set_token('TYPE_ID',-503);
630 fnd_message.raise_error;
631 --
632 end if;
633 --
634 end if;
635 --
636 hr_utility.set_location('Leaving:'||l_proc,10);
637 --
638 end chk_enrt_perd_end_dt_rl;
639 --
640 -- ----------------------------------------------------------------------------
641 -- |------< chk_enrt_perd_end_dt_cd >------|
642 -- ----------------------------------------------------------------------------
643 --
644 -- Description
645 -- This procedure is used to check that the lookup value is valid.
646 --
647 -- Pre Conditions
648 -- None.
649 --
650 -- In Parameters
651 -- lee_rsn_id PK of record being inserted or updated.
652 -- enrt_perd_end_dt_cd Value of lookup code.
653 -- effective_date effective date
654 -- object_version_number Object version number of record being
655 -- inserted or updated.
656 --
657 -- Post Success
658 -- Processing continues
659 --
660 -- Post Failure
661 -- Error handled by procedure
662 --
663 -- Access Status
664 -- Internal table handler use only.
665 --
666 Procedure chk_enrt_perd_end_dt_cd(p_lee_rsn_id in number,
667 p_enrt_perd_end_dt_cd in varchar2,
668 p_effective_date in date,
669 p_object_version_number in number) is
670 --
671 l_proc varchar2(72) := g_package||'chk_enrt_perd_end_dt_cd';
672 l_api_updating boolean;
673 --
674 Begin
675 --
676 hr_utility.set_location('Entering:'||l_proc, 5);
677 --
678 l_api_updating := ben_len_shd.api_updating
679 (p_lee_rsn_id => p_lee_rsn_id,
680 p_effective_date => p_effective_date,
681 p_object_version_number => p_object_version_number);
682 --
683 if (l_api_updating
684 and p_enrt_perd_end_dt_cd
685 <> nvl(ben_len_shd.g_old_rec.enrt_perd_end_dt_cd,hr_api.g_varchar2)
686 or not l_api_updating)
687 and p_enrt_perd_end_dt_cd is not null then
688 --
689 -- check if value of lookup falls within lookup type.
690 --
691 if hr_api.not_exists_in_hr_lookups
692 (p_lookup_type => 'BEN_ENRT_PERD_END',
693 p_lookup_code => p_enrt_perd_end_dt_cd,
694 p_effective_date => p_effective_date) then
695 --
696 -- raise error as does not exist as lookup
697 --
698 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
699 fnd_message.set_token('FIELD', 'p_enrt_perd_end_dt_cd');
700 fnd_message.set_token('TYPE', 'BEN_ENRT_PERD_END');
701 fnd_message.raise_error;
702 --
703 end if;
704 --
705 end if;
706 --
707 hr_utility.set_location('Leaving:'||l_proc,10);
708 --
709 end chk_enrt_perd_end_dt_cd;
710 --
711 -- ----------------------------------------------------------------------------
712 -- |------< chk_enrt_perd_strt_dt_rl >------|
713 -- ----------------------------------------------------------------------------
714 --
715 -- Description
716 -- This procedure is used to check that the Formula Rule is valid.
717 --
718 -- Pre Conditions
719 -- None.
720 --
721 -- In Parameters
722 -- lee_rsn_id PK of record being inserted or updated.
723 -- enrt_perd_strt_dt_rl Value of formula rule id.
724 -- effective_date effective date
725 -- object_version_number Object version number of record being
726 -- inserted or updated.
727 --
728 -- Post Success
729 -- Processing continues
730 --
731 -- Post Failure
732 -- Error handled by procedure
733 --
734 -- Access Status
735 -- Internal table handler use only.
736 --
737 Procedure chk_enrt_perd_strt_dt_rl(p_lee_rsn_id in number,
738 p_enrt_perd_strt_dt_rl in number,
739 p_business_group_id in number,
740 p_effective_date in date,
741 p_object_version_number in number) is
742 --
743 l_proc varchar2(72) := g_package||'chk_enrt_perd_strt_dt_rl';
744 l_api_updating boolean;
745 --
746 Begin
747 --
748 hr_utility.set_location('Entering:'||l_proc, 5);
749 --
750 l_api_updating := ben_len_shd.api_updating
751 (p_lee_rsn_id => p_lee_rsn_id,
752 p_effective_date => p_effective_date,
753 p_object_version_number => p_object_version_number);
754 --
755 if (l_api_updating
756 and nvl(p_enrt_perd_strt_dt_rl,hr_api.g_number)
757 <> ben_len_shd.g_old_rec.enrt_perd_strt_dt_rl
758 or not l_api_updating)
759 and p_enrt_perd_strt_dt_rl is not null then
760 --
761 -- check if value of formula rule is valid.
762 --
763 if not benutils.formula_exists
767 p_effective_date => p_effective_date) then
764 (p_formula_id => p_enrt_perd_strt_dt_rl,
765 p_formula_type_id => -504,
766 p_business_group_id => p_business_group_id,
768 --
769 -- raise error
770 --
771 fnd_message.set_name('BEN','BEN_91471_FORMULA_NOT_FOUND');
772 fnd_message.set_token('ID',p_enrt_perd_strt_dt_rl);
773 fnd_message.set_token('TYPE_ID',-504);
774 fnd_message.raise_error;
775 --
776 end if;
777 --
778 end if;
779 --
780 hr_utility.set_location('Leaving:'||l_proc,10);
781 --
782 end chk_enrt_perd_strt_dt_rl;
783 --
784 -- ----------------------------------------------------------------------------
785 -- |------< chk_enrt_perd_strt_dt_cd >------|
786 -- ----------------------------------------------------------------------------
787 --
788 -- Description
789 -- This procedure is used to check that the lookup value is valid.
790 --
791 -- Pre Conditions
792 -- None.
793 --
794 -- In Parameters
795 -- lee_rsn_id PK of record being inserted or updated.
796 -- enrt_perd_strt_dt_cd Value of lookup code.
797 -- effective_date effective date
798 -- object_version_number Object version number of record being
799 -- inserted or updated.
800 --
801 -- Post Success
802 -- Processing continues
803 --
804 -- Post Failure
805 -- Error handled by procedure
806 --
807 -- Access Status
808 -- Internal table handler use only.
809 --
810 Procedure chk_enrt_perd_strt_dt_cd(p_lee_rsn_id in number,
811 p_enrt_perd_strt_dt_cd in varchar2,
812 p_effective_date in date,
813 p_object_version_number in number) is
814 --
815 l_proc varchar2(72) := g_package||'chk_enrt_perd_strt_dt_cd';
816 l_api_updating boolean;
817 --
818 Begin
819 --
820 hr_utility.set_location('Entering:'||l_proc, 5);
821 --
822 l_api_updating := ben_len_shd.api_updating
823 (p_lee_rsn_id => p_lee_rsn_id,
824 p_effective_date => p_effective_date,
825 p_object_version_number => p_object_version_number);
826 --
827 if (l_api_updating
828 and p_enrt_perd_strt_dt_cd
829 <> nvl(ben_len_shd.g_old_rec.enrt_perd_strt_dt_cd,hr_api.g_varchar2)
830 or not l_api_updating)
831 and p_enrt_perd_strt_dt_cd is not null then
832 --
833 -- check if value of lookup falls within lookup type.
834 --
835 if hr_api.not_exists_in_hr_lookups
836 (p_lookup_type => 'BEN_ENRT_PERD_STRT',
837 p_lookup_code => p_enrt_perd_strt_dt_cd,
838 p_effective_date => p_effective_date) then
839 --
840 -- raise error as does not exist as lookup
841 --
842 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
843 fnd_message.set_token('FIELD', 'p_enrt_perd_strt_dt_cd');
844 fnd_message.set_token('TYPE', 'BEN_ENRT_PERD_STRT');
845 fnd_message.raise_error;
846 --
847 end if;
848 --
849 end if;
850 --
851 hr_utility.set_location('Leaving:'||l_proc,10);
852 --
853 end chk_enrt_perd_strt_dt_cd;
854 --
855 -- ----------------------------------------------------------------------------
856 -- |------< chk_enrt_cvg_strt_dt_cd >------|
857 -- ----------------------------------------------------------------------------
858 --
859 -- Description
860 -- This procedure is used to check that the lookup value is valid.
861 --
862 -- Pre Conditions
863 -- None.
864 --
865 -- In Parameters
866 -- lee_rsn_id PK of record being inserted or updated.
867 -- enrt_cvg_strt_dt_cd Value of lookup code.
868 -- effective_date effective date
869 -- object_version_number Object version number of record being
870 -- inserted or updated.
871 --
872 -- Post Success
873 -- Processing continues
874 --
875 -- Post Failure
876 -- Error handled by procedure
877 --
878 -- Access Status
879 -- Internal table handler use only.
880 --
881 Procedure chk_enrt_cvg_strt_dt_cd(p_lee_rsn_id in number,
882 p_enrt_cvg_strt_dt_cd in varchar2,
883 p_effective_date in date,
884 p_object_version_number in number) is
885 --
886 l_proc varchar2(72) := g_package||'chk_enrt_cvg_strt_dt_cd';
887 l_api_updating boolean;
888 --
889 Begin
890 --
891 hr_utility.set_location('Entering:'||l_proc, 5);
892 --
893 l_api_updating := ben_len_shd.api_updating
894 (p_lee_rsn_id => p_lee_rsn_id,
895 p_effective_date => p_effective_date,
896 p_object_version_number => p_object_version_number);
897 --
898 if (l_api_updating
899 and p_enrt_cvg_strt_dt_cd
900 <> nvl(ben_len_shd.g_old_rec.enrt_cvg_strt_dt_cd,hr_api.g_varchar2)
901 or not l_api_updating)
902 and p_enrt_cvg_strt_dt_cd is not null then
903 --
904 -- check if value of lookup falls within lookup type.
905 --
906 if hr_api.not_exists_in_hr_lookups
907 (p_lookup_type => 'BEN_ENRT_CVG_STRT',
908 p_lookup_code => p_enrt_cvg_strt_dt_cd,
909 p_effective_date => p_effective_date) then
910 --
914 fnd_message.set_token('FIELD', 'p_enrt_cvg_strt_dt_cd');
911 -- raise error as does not exist as lookup
912 --
913 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
915 fnd_message.set_token('TYPE', 'BEN_ENRT_CVG_STRT');
916 fnd_message.raise_error;
917 --
918 end if;
919 --
920 end if;
921 --
922 hr_utility.set_location('Leaving:'||l_proc,10);
923 --
924 end chk_enrt_cvg_strt_dt_cd;
925 --
926 --
927 -- ----------------------------------------------------------------------------
928 -- |------< chk_reinstate_ovrdn_cd >------|
929 -- ----------------------------------------------------------------------------
930 --
931 -- Description
932 -- This procedure is used to check that the lookup value is valid.
933 --
934 -- Pre Conditions
935 -- None.
936 --
937 -- In Parameters
938 -- lee_rsn_id PK of record being inserted or updated.
939 -- reinstate_ovrdn_cd Value of lookup code.
940 -- effective_date effective date
941 -- object_version_number Object version number of record being
942 -- inserted or updated.
943 --
944 -- Post Success
945 -- Processing continues
946 --
947 -- Post Failure
948 -- Error handled by procedure
949 --
950 -- Access Status
951 -- Internal table handler use only.
952 --
953 Procedure chk_reinstate_ovrdn_cd(p_lee_rsn_id in number,
954 p_reinstate_ovrdn_cd in varchar2,
955 p_effective_date in date,
956 p_object_version_number in number) is
957 --
958 l_proc varchar2(72) := g_package||'chk_reinstate_ovrdn_cd';
959 l_api_updating boolean;
960 --
961 Begin
962 --
963 hr_utility.set_location('Entering:'||l_proc, 5);
964 --
965 l_api_updating := ben_len_shd.api_updating
966 (p_lee_rsn_id => p_lee_rsn_id,
967 p_effective_date => p_effective_date,
968 p_object_version_number => p_object_version_number);
969 --
970 if (l_api_updating
971 and p_reinstate_ovrdn_cd
972 <> nvl(ben_len_shd.g_old_rec.reinstate_ovrdn_cd,hr_api.g_varchar2)
973 or not l_api_updating)
974 and p_reinstate_ovrdn_cd is not null then
975 --
976 -- check if value of lookup falls within lookup type.
977 --
978 if hr_api.not_exists_in_hr_lookups
979 (p_lookup_type => 'BEN_REINSTATE_OVRDN',
980 p_lookup_code => p_reinstate_ovrdn_cd,
981 p_effective_date => p_effective_date) then
982 --
983 -- raise error as does not exist as lookup
984 --
985 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
986 fnd_message.set_token('FIELD', 'p_reinstate_ovrdn_cd');
987 fnd_message.set_token('VALUE', p_reinstate_ovrdn_cd);
988 fnd_message.set_token('TYPE', 'BEN_REINSTATE_OVRDN');
989 fnd_message.raise_error;
990 --
991 end if;
992 --
993 end if;
994 --
995 hr_utility.set_location('Leaving:'||l_proc,10);
996 --
997 end chk_reinstate_ovrdn_cd;
998 --
999 -- ----------------------------------------------------------------------------
1000 -- |------< chk_reinstate_cd >------|
1001 -- ----------------------------------------------------------------------------
1002 --
1003 -- Description
1004 -- This procedure is used to check that the lookup value is valid.
1005 --
1006 -- Pre Conditions
1007 -- None.
1008 --
1009 -- In Parameters
1010 -- lee_rsn_id PK of record being inserted or updated.
1011 -- reinstate_cd Value of lookup code.
1012 -- effective_date effective date
1013 -- object_version_number Object version number of record being
1014 -- inserted or updated.
1015 --
1016 -- Post Success
1017 -- Processing continues
1018 --
1019 -- Post Failure
1020 -- Error handled by procedure
1021 --
1022 -- Access Status
1023 -- Internal table handler use only.
1024 --
1025 Procedure chk_reinstate_cd(p_lee_rsn_id in number,
1026 p_reinstate_cd in varchar2,
1027 p_effective_date in date,
1028 p_object_version_number in number) is
1029 --
1030 l_proc varchar2(72) := g_package||'chk_reinstate_cd';
1031 l_api_updating boolean;
1032 --
1033 Begin
1034 --
1035 hr_utility.set_location('Entering:'||l_proc, 5);
1036 --
1037 l_api_updating := ben_len_shd.api_updating
1038 (p_lee_rsn_id => p_lee_rsn_id,
1039 p_effective_date => p_effective_date,
1040 p_object_version_number => p_object_version_number);
1041 --
1042 if (l_api_updating
1043 and p_reinstate_cd
1044 <> nvl(ben_len_shd.g_old_rec.reinstate_cd,hr_api.g_varchar2)
1045 or not l_api_updating)
1046 and p_reinstate_cd is not null then
1047 --
1048 -- check if value of lookup falls within lookup type.
1049 --
1050 if hr_api.not_exists_in_hr_lookups
1051 (p_lookup_type => 'BEN_REINSTATE',
1052 p_lookup_code => p_reinstate_cd,
1053 p_effective_date => p_effective_date) then
1054 --
1055 -- raise error as does not exist as lookup
1056 --
1057 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
1058 fnd_message.set_token('FIELD', 'p_reinstate_cd');
1062 --
1059 fnd_message.set_token('VALUE', p_reinstate_cd);
1060 fnd_message.set_token('TYPE', 'BEN_REINSTATE');
1061 fnd_message.raise_error;
1063 end if;
1064 --
1065 end if;
1066 --
1067 hr_utility.set_location('Leaving:'||l_proc,10);
1068 --
1069 end chk_reinstate_cd;
1070 -- ----------------------------------------------------------------------------
1071 -- |------< chk_enrt_cvg_end_dt_cd >------|
1072 -- ----------------------------------------------------------------------------
1073 --
1074 -- Description
1075 -- This procedure is used to check that the lookup value is valid.
1076 --
1077 -- Pre Conditions
1078 -- None.
1079 --
1080 -- In Parameters
1081 -- lee_rsn_id PK of record being inserted or updated.
1082 -- enrt_cvg_end_dt_cd Value of lookup code.
1083 -- effective_date effective date
1084 -- object_version_number Object version number of record being
1085 -- inserted or updated.
1086 --
1087 -- Post Success
1088 -- Processing continues
1089 --
1090 -- Post Failure
1091 -- Error handled by procedure
1092 --
1093 -- Access Status
1094 -- Internal table handler use only.
1095 --
1096 Procedure chk_enrt_cvg_end_dt_cd(p_lee_rsn_id in number,
1097 p_enrt_cvg_end_dt_cd in varchar2,
1098 p_effective_date in date,
1099 p_object_version_number in number) is
1100 --
1101 l_proc varchar2(72) := g_package||'chk_enrt_cvg_end_dt_cd';
1102 l_api_updating boolean;
1103 --
1104 Begin
1105 --
1106 hr_utility.set_location('Entering:'||l_proc, 5);
1107 --
1108 l_api_updating := ben_len_shd.api_updating
1109 (p_lee_rsn_id => p_lee_rsn_id,
1110 p_effective_date => p_effective_date,
1111 p_object_version_number => p_object_version_number);
1112 --
1113 if (l_api_updating
1114 and p_enrt_cvg_end_dt_cd
1115 <> nvl(ben_len_shd.g_old_rec.enrt_cvg_end_dt_cd,hr_api.g_varchar2)
1116 or not l_api_updating)
1117 and p_enrt_cvg_end_dt_cd is not null then
1118 --
1119 -- check if value of lookup falls within lookup type.
1120 --
1121 if hr_api.not_exists_in_hr_lookups
1122 (p_lookup_type => 'BEN_ENRT_CVG_END',
1123 p_lookup_code => p_enrt_cvg_end_dt_cd,
1124 p_effective_date => p_effective_date) then
1125 --
1126 -- raise error as does not exist as lookup
1127 --
1128 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
1129 fnd_message.set_token('FIELD', 'p_enrt_cvg_end_dt_cd');
1130 fnd_message.set_token('TYPE', 'BEN_ENRT_CVG_END');
1131 fnd_message.raise_error;
1132 --
1133 end if;
1134 --
1135 end if;
1136 --
1137 hr_utility.set_location('Leaving:'||l_proc,10);
1138 --
1139 end chk_enrt_cvg_end_dt_cd;
1140 --
1141 -- ----------------------------------------------------------------------------
1142 -- |------< chk_cls_enrt_dt_to_use_cd >------|
1143 -- ----------------------------------------------------------------------------
1144 --
1145 -- Description
1146 -- This procedure is used to check that the lookup value is valid.
1147 --
1148 -- Pre Conditions
1149 -- None.
1150 --
1151 -- In Parameters
1152 -- lee_rsn_id PK of record being inserted or updated.
1153 -- cls_enrt_dt_to_use_cd Value of lookup code.
1154 -- effective_date effective date
1155 -- object_version_number Object version number of record being
1156 -- inserted or updated.
1157 --
1158 -- Post Success
1159 -- Processing continues
1160 --
1161 -- Post Failure
1162 -- Error handled by procedure
1163 --
1164 -- Access Status
1165 -- Internal table handler use only.
1166 --
1167 Procedure chk_cls_enrt_dt_to_use_cd(p_lee_rsn_id in number,
1168 p_cls_enrt_dt_to_use_cd in varchar2,
1169 p_effective_date in date,
1170 p_object_version_number in number) is
1171 --
1172 l_proc varchar2(72) := g_package||'chk_cls_enrt_dt_to_use_cd';
1173 l_api_updating boolean;
1174 --
1175 Begin
1176 --
1177 hr_utility.set_location('Entering:'||l_proc, 5);
1178 --
1179 l_api_updating := ben_len_shd.api_updating
1180 (p_lee_rsn_id => p_lee_rsn_id,
1181 p_effective_date => p_effective_date,
1182 p_object_version_number => p_object_version_number);
1183 --
1184 if (l_api_updating
1185 and p_cls_enrt_dt_to_use_cd
1186 <> nvl(ben_len_shd.g_old_rec.cls_enrt_dt_to_use_cd,hr_api.g_varchar2)
1187 or not l_api_updating)
1188 and p_cls_enrt_dt_to_use_cd is not null then
1189 --
1190 -- check if value of lookup falls within lookup type.
1191 --
1192 if hr_api.not_exists_in_hr_lookups
1193 (p_lookup_type => 'BEN_CLS_ENRT_DT_TO_USE',
1194 p_lookup_code => p_cls_enrt_dt_to_use_cd,
1195 p_effective_date => p_effective_date) then
1196 --
1197 -- raise error as does not exist as lookup
1198 --
1199 fnd_message.set_name('BEN', 'BEN_91628_LOOKUP_TYPE_GENERIC');
1200 fnd_message.set_token('FIELD', 'p_cls_enrt_dt_to_use_cd');
1201 fnd_message.set_token('TYPE', 'BEN_CLS_ENRT_DT_TO_USE');
1202 fnd_message.raise_error;
1203 --
1204 end if;
1205 --
1206 end if;
1207 --
1208 hr_utility.set_location('Leaving:'||l_proc,10);
1209 --
1210 end chk_cls_enrt_dt_to_use_cd;
1211 --
1212 --
1216 --
1213 -- ----------------------------------------------------------------------------
1214 -- |------< chk_code_rule_dpnd >------|
1215 -- ----------------------------------------------------------------------------
1217 -- Description
1218 -- This procedure is used to check that the Rule is only allowed to
1219 -- have a value if the value of the Code = 'Rule', and if code is
1220 -- = RL then p_rule must have a value.
1221 --
1222 -- Pre Conditions
1223 -- None.
1224 --
1225 -- In Parameters
1226 -- P_CODE value of code item.
1227 -- P_RULE value of rule item
1228 --
1229 -- Post Success
1230 -- Processing continues
1231 --
1232 -- Post Failure
1233 -- Error handled by procedure
1234 --
1235 -- Access Status
1236 -- Internal table handler use only.
1237 --
1238 Procedure chk_code_rule_dpnd(p_code in varchar2,
1239 p_rule in number) is
1240 --
1241 l_proc varchar2(72) := g_package||'chk_code_rule_dpnd';
1242 --
1243 Begin
1244 --
1245 hr_utility.set_location('Entering:'||l_proc, 5);
1246 --
1247 if p_code <> 'RL' and p_rule is not null then
1248 --
1249 fnd_message.set_name('BEN','BEN_91624_CD_RL_2');
1250 fnd_message.raise_error;
1251 --
1252 elsif p_code = 'RL' and p_rule is null then
1253 --
1254 fnd_message.set_name('BEN','BEN_91623_CD_RL_1');
1255 fnd_message.raise_error;
1256 --
1257 end if;
1258 --
1259 hr_utility.set_location('Leaving:'||l_proc,10);
1260 --
1261 end chk_code_rule_dpnd;
1262
1263
1264
1265
1266
1267 Procedure chk_code_number_dpnd(p_code in varchar2,
1268 p_number in number,
1269 p_end_code in varchar2,
1270 p_end_number in number) is
1271 --
1272 l_proc varchar2(72) := g_package||'chk_code_number_dpnd';
1273 --
1274 Begin
1275 --
1276 hr_utility.set_location('Entering:'||l_proc, 5);
1277
1278 --
1279 /* Bug-4331059, Commented following check because number of days from start and end can take
1280 any value ( Including zero)
1281 if p_number is not null then -- remove the condition once the plan copy is done
1282 if p_code not in ( 'NUMDOE', 'NUMDON','NUMDOEN') and nvl(p_number,0) <> 0 then
1283 --
1284 fnd_message.set_name('BEN','BEN_94232_CD_NUMBER_OF_DAYS');
1285 fnd_message.raise_error;
1286 --
1287 elsif p_code in ( 'NUMDOE', 'NUMDON','NUMDOEN') and nvl(p_number,0) = 0 then
1288 --
1289 fnd_message.set_name('BEN','BEN_94232_CD_NUMBER_OF_DAYS');
1290 fnd_message.raise_error;
1291 --
1292 end if;
1293
1294 end if ;
1295
1296
1297 if p_end_number is not null then -- remove the condition once the plan copy is done
1298 if p_end_code not in ( 'NUMDOE', 'NUMDON','NUMDOEN') and nvl(p_end_number,0) <> 0 then
1299 --
1300 fnd_message.set_name('BEN','BEN_94232_CD_NUMBER_OF_DAYS');
1301 fnd_message.raise_error;
1302 --
1303 elsif p_end_code in ( 'NUMDOE', 'NUMDON','NUMDOEN') and nvl(p_end_number,0) = 0 then
1304 --
1305 fnd_message.set_name('BEN','BEN_94232_CD_NUMBER_OF_DAYS');
1306 fnd_message.raise_error;
1307 --
1308 end if ;
1309 end if;
1310 */
1311 if p_code in ( 'NUMDOE', 'NUMDON','NUMDOEN')
1312 and p_end_code = p_code
1313 and nvl(p_end_number,0) < nvl(p_number,0) then
1314
1315 fnd_message.set_name('BEN','BEN_94233_END_DT_MORE_STRT_DT');
1316 fnd_message.raise_error;
1317
1318 end if ;
1319
1320
1321 --
1322 hr_utility.set_location('Leaving:'||l_proc,10);
1323 --
1324 end chk_code_number_dpnd;
1325 --
1326 -- CWBGLOBAL
1327 procedure chk_cwb_ler_id
1328 ( p_lee_rsn_id in number,
1329 p_ler_id in number,
1330 p_popl_enrt_typ_cycl_id in number,
1331 p_effective_date in date
1332 )
1333 is
1334 cursor c_duplicate_ler is
1335 select 1
1336 from
1337 ben_popl_enrt_typ_cycl_f pet,
1338 ben_pl_f pln,
1339 ben_lee_rsn_f len1,
1340 ben_popl_enrt_typ_cycl_f pet1,
1341 ben_pl_f pln1
1342 where
1343 pet.popl_enrt_typ_cycl_id = p_popl_enrt_typ_cycl_id
1344 and p_effective_date between pet.effective_start_date
1345 and pet.effective_end_date
1346 and pet.pl_id = pln.pl_id
1347 and p_effective_date between pln.effective_start_date
1348 and pln.effective_end_date
1349 and
1350 (( pln.group_pl_id = pln.pl_id
1351 and pln1.group_pl_id = pln.pl_id
1352 )
1353 or
1354 ( pln.group_pl_id <> pln.pl_id
1355 and pln1.pl_id = pln.group_pl_id
1356 )
1357 )
1358 and p_effective_date between pln1.effective_start_date
1359 and pln1.effective_end_date
1360 and pln1.pl_id = pet1.pl_id
1361 and p_effective_date between pet1.effective_start_date
1362 and pet1.effective_end_date
1363 and pet1.popl_enrt_typ_cycl_id = len1.popl_enrt_typ_cycl_id
1364 and len1.ler_id = p_ler_id
1365 and p_effective_date between len1.effective_start_date
1366 and len1.effective_end_date;
1367 l_lee_rsn_id number;
1368 l_proc varchar2(72) := g_package||'chk_cwb_ler_id';
1369
1370 begin
1371
1372
1373 hr_utility.set_location('Entering:'||l_proc, 5);
1374 --hr_utility.set_location('p_ler_id:'||p_ler_id, 5.5);
1375 --hr_utility.set_location('p_lee_rsn_id:'||p_lee_rsn_id, 5.51);
1379 fetch c_duplicate_ler into l_lee_rsn_id;
1376 --hr_utility.set_location('p_popl_enrt_typ_cycl_id:'||p_popl_enrt_typ_cycl_id, 5.52);
1377
1378 open c_duplicate_ler;
1380 if c_duplicate_ler%FOUND then
1381 -- hr_utility.set_location('dupe found:', 5.53);
1382 close c_duplicate_ler;
1383 fnd_message.set_name ('PAY','VALUE IS NOT UNIQUE');
1384 fnd_message.raise_error;
1385 end if;
1386 --hr_utility.set_location('NOT found:', 5.54);
1387 close c_duplicate_ler;
1388 hr_utility.set_location('Leaving:'||l_proc, 10);
1389 end chk_cwb_ler_id ;
1390 --
1391 --------------------------------------------------------------------------------
1392 -- |----------------------< chk_defer_flag_set_pln_plip >--------------------|
1393 -- ----------------------------------------------------------------------------
1394 --
1395 -- Description
1396 -- This procedure is used to check that if defer_deenrol_flag can be set for PNIP
1397 -- or if the Plan is in Program then can't be set at Plan Level.
1398 --
1399 -- In Parameters
1400 -- enrt_perd_id PK of record being inserted or updated.
1401 --
1402 -- object_version_number Object version number of record being
1403 -- inserted or updated.
1404 --
1405 -- Post Success
1406 -- Processing continues
1407 --
1408 -- Post Failure
1409 -- Error handled by procedure
1410 --
1411 -- Access Status
1412 -- Internal table handler use only.
1413 --
1414 procedure chk_defer_flag_set_pln_plip
1415 (p_lee_rsn_id in number
1416 ,p_popl_enrt_typ_cycl_id in number
1417 ,p_object_version_number in number
1418 ,p_effective_date in date
1419 ,p_defer_deenrol_flag in varchar2
1420 ,p_business_group_id in number
1421 ) is
1422 --
1423 cursor c_pl_cd is
1424 select pln.pl_cd
1425 from ben_pl_f pln
1426 ,ben_popl_enrt_typ_cycl_f pet
1427 where pet.popl_enrt_typ_cycl_id = p_popl_enrt_typ_cycl_id
1428 and pet.pl_id = pln.pl_id
1429 and pln.business_group_id = p_business_group_id
1430 and pet.business_group_id = pln.business_group_id
1431 and p_effective_date between pln.effective_start_date and pln.effective_end_date
1432 and p_effective_date between pet.effective_start_date and pet.effective_end_date;
1433 --
1434 l_pl_cd ben_pl_f.pl_cd%TYPE;
1435 l_api_updating boolean;
1436 l_proc varchar2(72) := g_package||'chk_defer_flag_set_pln_plip';
1437 --
1438 begin
1439 --
1440 hr_utility.set_location('Entering:'|| l_proc, 9653);
1441 --
1442 l_api_updating := ben_len_shd.api_updating
1443 (p_effective_date => p_effective_date,
1444 p_lee_rsn_id => p_lee_rsn_id,
1445 p_object_version_number => p_object_version_number);
1446 --
1447 if (l_api_updating
1448 and p_defer_deenrol_flag <> nvl(ben_len_shd.g_old_rec.defer_deenrol_flag,hr_api.g_varchar2)
1449 or not l_api_updating)
1450 and p_defer_deenrol_flag is not null then
1451 --
1452 open c_pl_cd;
1453 fetch c_pl_cd into l_pl_cd;
1454 close c_pl_cd;
1455 --
1456 if l_pl_cd = 'MSTBPGM' then
1457 --
1458 if p_defer_deenrol_flag = 'Y' then
1459 --
1460 fnd_message.set_name('BEN','BEN_94880_DEFER_FLAG_VALID_LVL');
1461 fnd_message.raise_error;
1462 --
1463 end if;
1464 --
1465 end if;
1466 --
1467 end if;
1468 --
1469 hr_utility.set_location('Leaving:'|| l_proc, 9653);
1470 --
1471 end chk_defer_flag_set_pln_plip;
1472 --
1473 ---- ----------------------------------------------------------------------------
1474 -- |----------------------< chk_defer_flag_lookup >--------------------|
1475 -- ----------------------------------------------------------------------------
1476 --
1477 -- Description
1478 -- This procedure is used to check that the flag lookup value is valid.
1479 --
1480 -- In Parameters
1481 -- enrt_perd_id PK of record being inserted or updated.
1482 --
1483 -- object_version_number Object version number of record being
1484 -- inserted or updated.
1485 --
1486 -- Post Success
1487 -- Processing continues
1488 --
1489 -- Post Failure
1490 -- Error handled by procedure
1491 --
1492 -- Access Status
1493 -- Internal table handler use only.
1494 --
1495 procedure chk_defer_flag_lookup
1496 (p_lee_rsn_id in number
1497 ,p_effective_date in date
1498 ,p_defer_deenrol_flag in varchar2
1499 ,p_object_version_number in number
1500 ) is
1501 --
1502 l_api_updating boolean;
1503 l_proc varchar2(72) := g_package||'chk_defer_flag_lookup';
1504 --
1505 begin
1506 --
1507 hr_utility.set_location('Entering:'|| l_proc, 9653);
1508 --
1509 l_api_updating := ben_len_shd.api_updating
1510 (p_effective_date => p_effective_date,
1511 p_lee_rsn_id => p_lee_rsn_id,
1512 p_object_version_number => p_object_version_number);
1513 --
1514 if (l_api_updating
1515 and p_defer_deenrol_flag <> nvl(ben_len_shd.g_old_rec.defer_deenrol_flag,hr_api.g_varchar2)
1516 or not l_api_updating)
1517 and p_defer_deenrol_flag is not null then
1518 --
1519 if hr_api.not_exists_in_hr_lookups
1520 (p_lookup_type => 'YES_NO',
1521 p_lookup_code => p_defer_deenrol_flag,
1522 p_effective_date => p_effective_date) then
1523 --
1524 -- raise error as does not exist as lookup
1525 --
1526 fnd_message.set_name('PAY','HR_LOOKUP_DOES_NOT_EXIST');
1527 fnd_message.raise_error;
1528 --
1529 end if;
1533 hr_utility.set_location('Leaving:'|| l_proc, 9653);
1530 --
1531 end if;
1532 --
1534 --
1535 end chk_defer_flag_lookup;
1536 --
1537 --
1538 -- Bug 6000303
1539 -- ----------------------------------------------------------------------------
1540 -- |--------------------------< dt_update_validate >--------------------------|
1541 -- ----------------------------------------------------------------------------
1542 -- {Start Of Comments}
1543 --
1544 -- Description:
1545 -- This procedure is used for referential integrity of datetracked
1546 -- parent entities when a datetrack update operation is taking place
1547 -- and where there is no cascading of update defined for this entity.
1548 --
1549 -- Prerequisites:
1550 -- This procedure is called from the update_validate.
1551 --
1552 -- In Parameters:
1553 --
1554 -- Post Success:
1555 -- Processing continues.
1556 --
1557 -- Post Failure:
1558 --
1559 -- Developer Implementation Notes:
1560 -- This procedure should not need maintenance unless the HR Schema model
1561 -- changes.
1562 --
1563 -- Access Status:
1564 -- Internal Row Handler Use Only.
1565 --
1566 -- {End Of Comments}
1567 -- ----------------------------------------------------------------------------
1568 Procedure dt_update_validate
1569 (p_popl_enrt_typ_cycl_id in number default hr_api.g_number,
1570 p_datetrack_mode in varchar2,
1571 p_validation_start_date in date,
1572 p_validation_end_date in date) Is
1573 --
1574 l_proc varchar2(72) := g_package||'dt_update_validate';
1575 l_integrity_error Exception;
1576 l_table_name all_tables.table_name%TYPE;
1577 --
1578 Begin
1579 hr_utility.set_location('Entering:'||l_proc, 5);
1580 --
1581 -- Ensure that the p_datetrack_mode argument is not null
1582 --
1583 hr_api.mandatory_arg_error
1584 (p_api_name => l_proc,
1585 p_argument => 'datetrack_mode',
1586 p_argument_value => p_datetrack_mode);
1587 --
1588 -- Only perform the validation if the datetrack update mode is valid
1589 --
1590 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
1591 --
1592 --
1593 -- Ensure the arguments are not null
1594 --
1595 hr_api.mandatory_arg_error
1596 (p_api_name => l_proc,
1597 p_argument => 'validation_start_date',
1598 p_argument_value => p_validation_start_date);
1599 --
1600 hr_api.mandatory_arg_error
1601 (p_api_name => l_proc,
1602 p_argument => 'validation_end_date',
1603 p_argument_value => p_validation_end_date);
1604 --
1605 If ((nvl(p_popl_enrt_typ_cycl_id, hr_api.g_number) <> hr_api.g_number) and
1606 NOT (dt_api.check_min_max_dates
1607 (p_base_table_name => 'ben_popl_enrt_typ_cycl_f',
1608 p_base_key_column => 'popl_enrt_typ_cycl_id',
1609 p_base_key_value => p_popl_enrt_typ_cycl_id,
1610 p_from_date => p_validation_start_date,
1611 p_to_date => p_validation_end_date))) Then
1612 l_table_name := 'ben_popl_enrt_typ_cycl_f';
1613 Raise l_integrity_error;
1614 End If;
1615 --
1616 End If;
1617 --
1618 hr_utility.set_location(' Leaving:'||l_proc, 10);
1619 Exception
1620 When l_integrity_error Then
1621 --
1622 -- A referential integrity check was violated therefore
1623 -- we must error
1624 --
1625 hr_utility.set_message(801, 'HR_7216_DT_UPD_INTEGRITY_ERR');
1626 hr_utility.set_message_token('TABLE_NAME', l_table_name);
1627 hr_utility.raise_error;
1628 When Others Then
1629 --
1630 -- An unhandled or unexpected error has occurred which
1631 -- we must report
1632 --
1633 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1634 hr_utility.set_message_token('PROCEDURE', l_proc);
1635 hr_utility.set_message_token('STEP','15');
1636 hr_utility.raise_error;
1637 End dt_update_validate;
1638 --
1639 -- ----------------------------------------------------------------------------
1640 -- |--------------------------< dt_delete_validate >--------------------------|
1641 -- ----------------------------------------------------------------------------
1642 -- {Start Of Comments}
1643 --
1644 -- Description:
1645 -- This procedure is used for referential integrity of datetracked
1646 -- child entities when either a datetrack DELETE or ZAP is in operation
1647 -- and where there is no cascading of delete defined for this entity.
1648 -- For the datetrack mode of DELETE or ZAP we must ensure that no
1649 -- datetracked child rows exist between the validation start and end
1650 -- dates.
1651 --
1652 -- Prerequisites:
1653 -- This procedure is called from the delete_validate.
1654 --
1655 -- In Parameters:
1656 --
1657 -- Post Success:
1658 -- Processing continues.
1659 --
1660 -- Post Failure:
1661 -- If a row exists by determining the returning Boolean value from the
1662 -- generic dt_api.rows_exist function then we must supply an error via
1663 -- the use of the local exception handler l_rows_exist.
1664 --
1665 -- Developer Implementation Notes:
1666 -- This procedure should not need maintenance unless the HR Schema model
1667 -- changes.
1668 --
1669 -- Access Status:
1670 -- Internal Row Handler Use Only.
1671 --
1672 -- {End Of Comments}
1673 -- ----------------------------------------------------------------------------
1674 Procedure dt_delete_validate
1675 (p_lee_rsn_id in number,
1676 p_datetrack_mode in varchar2,
1680 l_proc varchar2(72) := g_package||'dt_delete_validate';
1677 p_validation_start_date in date,
1678 p_validation_end_date in date) Is
1679 --
1681 l_rows_exist Exception;
1682 l_table_name all_tables.table_name%TYPE;
1683 --
1684 Begin
1685 hr_utility.set_location('Entering:'||l_proc, 5);
1686 --
1687 -- Ensure that the p_datetrack_mode argument is not null
1688 --
1689 hr_api.mandatory_arg_error
1690 (p_api_name => l_proc,
1691 p_argument => 'datetrack_mode',
1692 p_argument_value => p_datetrack_mode);
1693 --
1694 -- Only perform the validation if the datetrack mode is either
1695 -- DELETE or ZAP
1696 --
1697 If (p_datetrack_mode = 'DELETE' or
1698 p_datetrack_mode = 'ZAP') then
1699 --
1700 --
1701 -- Ensure the arguments are not null
1702 --
1703 hr_api.mandatory_arg_error
1704 (p_api_name => l_proc,
1705 p_argument => 'validation_start_date',
1706 p_argument_value => p_validation_start_date);
1707 --
1708 hr_api.mandatory_arg_error
1709 (p_api_name => l_proc,
1710 p_argument => 'validation_end_date',
1711 p_argument_value => p_validation_end_date);
1712 --
1713 hr_api.mandatory_arg_error
1714 (p_api_name => l_proc,
1715 p_argument => 'lee_rsn_id',
1716 p_argument_value => p_lee_rsn_id);
1717 --
1718 If (dt_api.rows_exist
1719 (p_base_table_name => 'ben_lee_rsn_rl_f',
1720 p_base_key_column => 'lee_rsn_id',
1721 p_base_key_value => p_lee_rsn_id,
1722 p_from_date => p_validation_start_date,
1723 p_to_date => p_validation_end_date)) Then
1724 l_table_name := 'ben_lee_rsn_rl_f';
1725 Raise l_rows_exist;
1726 End If;
1727 If (dt_api.rows_exist
1728 (p_base_table_name => 'ben_enrt_perd_for_pl_f',
1729 p_base_key_column => 'lee_rsn_id',
1730 p_base_key_value => p_lee_rsn_id,
1731 p_from_date => p_validation_start_date,
1732 p_to_date => p_validation_end_date)) Then
1733 l_table_name := 'ben_enrt_perd_for_pl_f';
1734 Raise l_rows_exist;
1735 End If;
1736 --
1737 End If;
1738 --
1739 hr_utility.set_location(' Leaving:'||l_proc, 10);
1740 Exception
1741 When l_rows_exist Then
1742 --
1743 -- A referential integrity check was violated therefore
1744 -- we must error
1745 --
1746 hr_utility.set_message(801, 'HR_7215_DT_CHILD_EXISTS');
1747 hr_utility.set_message_token('TABLE_NAME', l_table_name);
1748 hr_utility.raise_error;
1749 When Others Then
1750 --
1751 -- An unhandled or unexpected error has occurred which
1752 -- we must report
1753 --
1754 hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
1755 hr_utility.set_message_token('PROCEDURE', l_proc);
1756 hr_utility.set_message_token('STEP','15');
1757 hr_utility.raise_error;
1758 End dt_delete_validate;
1759 --
1760 -- ----------------------------------------------------------------------------
1761 -- |---------------------------< insert_validate >----------------------------|
1762 -- ----------------------------------------------------------------------------
1763 Procedure insert_validate
1764 (p_rec in ben_len_shd.g_rec_type,
1765 p_effective_date in date,
1766 p_datetrack_mode in varchar2,
1767 p_validation_start_date in date,
1768 p_validation_end_date in date) is
1769 --
1770 l_proc varchar2(72) := g_package||'insert_validate';
1771 --
1772 Begin
1773 hr_utility.set_location('Entering:'||l_proc, 5);
1774 --
1775 -- Call all supporting business operations
1776 --
1777 --
1778 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1779 --
1780 chk_lee_rsn_id
1781 (p_lee_rsn_id => p_rec.lee_rsn_id,
1782 p_effective_date => p_effective_date,
1783 p_object_version_number => p_rec.object_version_number);
1784 --
1785 chk_inelig_and_dflts
1786 (p_dys_aftr_end_to_dflt_num => p_rec.dys_aftr_end_to_dflt_num,
1787 p_dys_no_enrl_not_elig_num => p_rec.dys_no_enrl_not_elig_num);
1788 --
1789 chk_enrt_cvg_strt_dt_rl
1790 (p_lee_rsn_id => p_rec.lee_rsn_id,
1791 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
1792 p_business_group_id => p_rec.business_group_id,
1793 p_effective_date => p_effective_date,
1794 p_object_version_number => p_rec.object_version_number);
1795 --
1796 chk_enrt_cvg_end_dt_rl
1797 (p_lee_rsn_id => p_rec.lee_rsn_id,
1798 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
1799 p_business_group_id => p_rec.business_group_id,
1800 p_effective_date => p_effective_date,
1801 p_object_version_number => p_rec.object_version_number);
1802 --
1803 chk_rt_strt_dt_rl
1804 (p_lee_rsn_id => p_rec.lee_rsn_id,
1805 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
1806 p_business_group_id => p_rec.business_group_id,
1807 p_effective_date => p_effective_date,
1808 p_object_version_number => p_rec.object_version_number);
1809 --
1810 chk_rt_strt_dt_cd
1811 (p_lee_rsn_id => p_rec.lee_rsn_id,
1812 p_rt_strt_dt_cd => p_rec.rt_strt_dt_cd,
1813 p_effective_date => p_effective_date,
1814 p_object_version_number => p_rec.object_version_number);
1815 --
1816 chk_rt_end_dt_rl
1817 (p_lee_rsn_id => p_rec.lee_rsn_id,
1821 p_object_version_number => p_rec.object_version_number);
1818 p_rt_end_dt_rl => p_rec.rt_end_dt_rl,
1819 p_business_group_id => p_rec.business_group_id,
1820 p_effective_date => p_effective_date,
1822 --
1823 chk_rt_end_dt_cd
1824 (p_lee_rsn_id => p_rec.lee_rsn_id,
1825 p_rt_end_dt_cd => p_rec.rt_end_dt_cd,
1826 p_effective_date => p_effective_date,
1827 p_object_version_number => p_rec.object_version_number);
1828 --
1829 chk_enrt_perd_end_dt_rl
1830 (p_lee_rsn_id => p_rec.lee_rsn_id,
1831 p_enrt_perd_end_dt_rl => p_rec.enrt_perd_end_dt_rl,
1832 p_business_group_id => p_rec.business_group_id,
1833 p_effective_date => p_effective_date,
1834 p_object_version_number => p_rec.object_version_number);
1835 --
1836 chk_enrt_perd_end_dt_cd
1837 (p_lee_rsn_id => p_rec.lee_rsn_id,
1838 p_enrt_perd_end_dt_cd => p_rec.enrt_perd_end_dt_cd,
1839 p_effective_date => p_effective_date,
1840 p_object_version_number => p_rec.object_version_number);
1841 --
1842 chk_enrt_perd_strt_dt_rl
1843 (p_lee_rsn_id => p_rec.lee_rsn_id,
1844 p_enrt_perd_strt_dt_rl => p_rec.enrt_perd_strt_dt_rl,
1845 p_business_group_id => p_rec.business_group_id,
1846 p_effective_date => p_effective_date,
1847 p_object_version_number => p_rec.object_version_number);
1848 --
1849 chk_enrt_perd_strt_dt_cd
1850 (p_lee_rsn_id => p_rec.lee_rsn_id,
1851 p_enrt_perd_strt_dt_cd => p_rec.enrt_perd_strt_dt_cd,
1852 p_effective_date => p_effective_date,
1853 p_object_version_number => p_rec.object_version_number);
1854 --
1855 chk_enrt_cvg_strt_dt_cd
1856 (p_lee_rsn_id => p_rec.lee_rsn_id,
1857 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
1858 p_effective_date => p_effective_date,
1859 p_object_version_number => p_rec.object_version_number);
1860 --
1861 chk_enrt_cvg_end_dt_cd
1862 (p_lee_rsn_id => p_rec.lee_rsn_id,
1863 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
1864 p_effective_date => p_effective_date,
1865 p_object_version_number => p_rec.object_version_number);
1866 --
1867 chk_cls_enrt_dt_to_use_cd
1868 (p_lee_rsn_id => p_rec.lee_rsn_id,
1869 p_cls_enrt_dt_to_use_cd => p_rec.cls_enrt_dt_to_use_cd,
1870 p_effective_date => p_effective_date,
1871 p_object_version_number => p_rec.object_version_number);
1872 --
1873 chk_code_rule_dpnd
1874 (p_code => p_rec.enrt_perd_strt_dt_cd,
1875 p_rule => p_rec.enrt_perd_strt_dt_rl);
1876 --
1877 chk_code_rule_dpnd
1878 (p_code => p_rec.enrt_perd_end_dt_cd,
1879 p_rule => p_rec.enrt_perd_end_dt_rl);
1880 --
1881 chk_code_rule_dpnd
1882 (p_code => p_rec.enrt_cvg_strt_dt_cd,
1883 p_rule => p_rec.enrt_cvg_strt_dt_rl);
1884 --
1885 chk_code_rule_dpnd
1886 (p_code => p_rec.enrt_cvg_end_dt_cd,
1887 p_rule => p_rec.enrt_cvg_end_dt_rl);
1888 --
1889 chk_code_rule_dpnd
1890 (p_code => p_rec.rt_strt_dt_cd,
1891 p_rule => p_rec.rt_strt_dt_rl);
1892 --
1893 chk_code_rule_dpnd
1894 (p_code => p_rec.rt_end_dt_cd,
1895 p_rule => p_rec.rt_end_dt_rl);
1896 --
1897 chk_cwb_ler_id
1898 (p_lee_rsn_id => p_rec.lee_rsn_id,
1899 p_ler_id => p_rec.ler_id,
1900 p_popl_enrt_typ_cycl_id => p_rec.popl_enrt_typ_cycl_id,
1901 p_effective_date => p_effective_date
1902 -- p_business_group_id => p_business_group_id
1903 );
1904
1905
1906 chk_code_number_dpnd(p_code => p_rec.enrt_perd_strt_dt_cd,
1907 p_number => p_rec.ENRT_PERD_STRT_DAYS ,
1908 p_end_code => p_rec.enrt_perd_end_dt_cd,
1909 p_end_number => p_rec.ENRT_PERD_END_DAYS) ;
1910 --
1911 --Reinstate Lookup validations
1912 chk_reinstate_ovrdn_cd(p_lee_rsn_id => p_rec.lee_rsn_id,
1913 p_reinstate_ovrdn_cd => p_rec.reinstate_ovrdn_cd,
1914 p_effective_date => p_effective_date,
1915 p_object_version_number => p_rec.object_version_number
1916 );
1917 chk_reinstate_cd(p_lee_rsn_id => p_rec.lee_rsn_id,
1918 p_reinstate_cd => p_rec.reinstate_cd,
1919 p_effective_date => p_effective_date,
1920 p_object_version_number => p_rec.object_version_number
1921 );
1922
1923 --
1924 chk_defer_flag_set_pln_plip
1925 (p_lee_rsn_id => p_rec.lee_rsn_id
1926 ,p_popl_enrt_typ_cycl_id => p_rec.popl_enrt_typ_cycl_id
1927 ,p_object_version_number => p_rec.object_version_number
1928 ,p_effective_date => p_effective_date
1929 ,p_defer_deenrol_flag => p_rec.defer_deenrol_flag
1930 ,p_business_group_id => p_rec.business_group_id
1931 );
1932 --
1933 chk_defer_flag_lookup
1934 (p_lee_rsn_id => p_rec.lee_rsn_id
1935 ,p_effective_date => p_effective_date
1936 ,p_defer_deenrol_flag => p_rec.defer_deenrol_flag
1937 ,p_object_version_number => p_rec.object_version_number
1938 );
1939 --
1940 hr_utility.set_location(' Leaving:'||l_proc, 10);
1941 End insert_validate;
1942 --
1943 -- ----------------------------------------------------------------------------
1944 -- |---------------------------< update_validate >----------------------------|
1945 -- ----------------------------------------------------------------------------
1946 Procedure update_validate
1947 (p_rec in ben_len_shd.g_rec_type,
1948 p_effective_date in date,
1952 --
1949 p_datetrack_mode in varchar2,
1950 p_validation_start_date in date,
1951 p_validation_end_date in date) is
1953 l_proc varchar2(72) := g_package||'update_validate';
1954 --
1955 Begin
1956 hr_utility.set_location('Entering:'||l_proc, 5);
1957 --
1958 -- Call all supporting business operations
1959 --
1960 --
1961 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
1962 --
1963 chk_lee_rsn_id
1964 (p_lee_rsn_id => p_rec.lee_rsn_id,
1965 p_effective_date => p_effective_date,
1966 p_object_version_number => p_rec.object_version_number);
1967 --
1968 chk_inelig_and_dflts
1969 (p_dys_aftr_end_to_dflt_num => p_rec.dys_aftr_end_to_dflt_num,
1970 p_dys_no_enrl_not_elig_num => p_rec.dys_no_enrl_not_elig_num);
1971 --
1972 chk_enrt_cvg_strt_dt_rl
1973 (p_lee_rsn_id => p_rec.lee_rsn_id,
1974 p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
1975 p_business_group_id => p_rec.business_group_id,
1976 p_effective_date => p_effective_date,
1977 p_object_version_number => p_rec.object_version_number);
1978 --
1979 chk_enrt_cvg_end_dt_rl
1980 (p_lee_rsn_id => p_rec.lee_rsn_id,
1981 p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
1982 p_business_group_id => p_rec.business_group_id,
1983 p_effective_date => p_effective_date,
1984 p_object_version_number => p_rec.object_version_number);
1985 --
1986 chk_rt_strt_dt_rl
1987 (p_lee_rsn_id => p_rec.lee_rsn_id,
1988 p_rt_strt_dt_rl => p_rec.rt_strt_dt_rl,
1989 p_business_group_id => p_rec.business_group_id,
1990 p_effective_date => p_effective_date,
1991 p_object_version_number => p_rec.object_version_number);
1992 --
1993 chk_rt_strt_dt_cd
1994 (p_lee_rsn_id => p_rec.lee_rsn_id,
1995 p_rt_strt_dt_cd => p_rec.rt_strt_dt_cd,
1996 p_effective_date => p_effective_date,
1997 p_object_version_number => p_rec.object_version_number);
1998 --
1999 chk_rt_end_dt_rl
2000 (p_lee_rsn_id => p_rec.lee_rsn_id,
2001 p_rt_end_dt_rl => p_rec.rt_end_dt_rl,
2002 p_business_group_id => p_rec.business_group_id,
2003 p_effective_date => p_effective_date,
2004 p_object_version_number => p_rec.object_version_number);
2005 --
2006 chk_rt_end_dt_cd
2007 (p_lee_rsn_id => p_rec.lee_rsn_id,
2008 p_rt_end_dt_cd => p_rec.rt_end_dt_cd,
2009 p_effective_date => p_effective_date,
2010 p_object_version_number => p_rec.object_version_number);
2011 --
2012 chk_enrt_perd_end_dt_rl
2013 (p_lee_rsn_id => p_rec.lee_rsn_id,
2014 p_enrt_perd_end_dt_rl => p_rec.enrt_perd_end_dt_rl,
2015 p_business_group_id => p_rec.business_group_id,
2016 p_effective_date => p_effective_date,
2017 p_object_version_number => p_rec.object_version_number);
2018 --
2019 chk_enrt_perd_end_dt_cd
2020 (p_lee_rsn_id => p_rec.lee_rsn_id,
2021 p_enrt_perd_end_dt_cd => p_rec.enrt_perd_end_dt_cd,
2022 p_effective_date => p_effective_date,
2023 p_object_version_number => p_rec.object_version_number);
2024 --
2025 chk_enrt_perd_strt_dt_rl
2026 (p_lee_rsn_id => p_rec.lee_rsn_id,
2027 p_enrt_perd_strt_dt_rl => p_rec.enrt_perd_strt_dt_rl,
2028 p_business_group_id => p_rec.business_group_id,
2029 p_effective_date => p_effective_date,
2030 p_object_version_number => p_rec.object_version_number);
2031 --
2032 chk_enrt_perd_strt_dt_cd
2033 (p_lee_rsn_id => p_rec.lee_rsn_id,
2034 p_enrt_perd_strt_dt_cd => p_rec.enrt_perd_strt_dt_cd,
2035 p_effective_date => p_effective_date,
2036 p_object_version_number => p_rec.object_version_number);
2037 --
2038 chk_enrt_cvg_strt_dt_cd
2039 (p_lee_rsn_id => p_rec.lee_rsn_id,
2040 p_enrt_cvg_strt_dt_cd => p_rec.enrt_cvg_strt_dt_cd,
2041 p_effective_date => p_effective_date,
2042 p_object_version_number => p_rec.object_version_number);
2043 --
2044 chk_enrt_cvg_end_dt_cd
2045 (p_lee_rsn_id => p_rec.lee_rsn_id,
2046 p_enrt_cvg_end_dt_cd => p_rec.enrt_cvg_end_dt_cd,
2047 p_effective_date => p_effective_date,
2048 p_object_version_number => p_rec.object_version_number);
2049 --
2050 chk_cls_enrt_dt_to_use_cd
2051 (p_lee_rsn_id => p_rec.lee_rsn_id,
2052 p_cls_enrt_dt_to_use_cd => p_rec.cls_enrt_dt_to_use_cd,
2053 p_effective_date => p_effective_date,
2054 p_object_version_number => p_rec.object_version_number);
2055 --
2056 chk_code_rule_dpnd
2057 (p_code => p_rec.enrt_perd_strt_dt_cd,
2058 p_rule => p_rec.enrt_perd_strt_dt_rl);
2059 --
2060 chk_code_rule_dpnd
2061 (p_code => p_rec.enrt_perd_end_dt_cd,
2062 p_rule => p_rec.enrt_perd_end_dt_rl);
2063 --
2064 chk_code_rule_dpnd
2065 (p_code => p_rec.enrt_cvg_strt_dt_cd,
2066 p_rule => p_rec.enrt_cvg_strt_dt_rl);
2067 --
2068 chk_code_rule_dpnd
2069 (p_code => p_rec.enrt_cvg_end_dt_cd,
2070 p_rule => p_rec.enrt_cvg_end_dt_rl);
2071 --
2072 chk_code_rule_dpnd
2073 (p_code => p_rec.rt_strt_dt_cd,
2074 p_rule => p_rec.rt_strt_dt_rl);
2075 --
2076 chk_code_rule_dpnd
2077 (p_code => p_rec.rt_end_dt_cd,
2078 p_rule => p_rec.rt_end_dt_rl);
2079 --
2080 chk_cwb_ler_id
2081 (p_lee_rsn_id => p_rec.lee_rsn_id,
2082 p_ler_id => p_rec.ler_id,
2083 p_popl_enrt_typ_cycl_id => p_rec.popl_enrt_typ_cycl_id,
2084 p_effective_date => p_effective_date
2085 --p_business_group_id => p_business_group_id
2086 );
2087
2088 -- Call the datetrack update integrity operation
2089 --
2093 p_validation_start_date => p_validation_start_date,
2090 dt_update_validate
2091 (p_popl_enrt_typ_cycl_id => p_rec.popl_enrt_typ_cycl_id,
2092 p_datetrack_mode => p_datetrack_mode,
2094 p_validation_end_date => p_validation_end_date);
2095 --
2096
2097 chk_code_number_dpnd(p_code => p_rec.enrt_perd_strt_dt_cd,
2098 p_number => p_rec.ENRT_PERD_STRT_DAYS ,
2099 p_end_code => p_rec.enrt_perd_end_dt_cd,
2100 p_end_number => p_rec.ENRT_PERD_END_DAYS) ;
2101
2102 --Reinstate Lookup validations
2103 chk_reinstate_ovrdn_cd(p_lee_rsn_id => p_rec.lee_rsn_id,
2104 p_reinstate_ovrdn_cd => p_rec.reinstate_ovrdn_cd,
2105 p_effective_date => p_effective_date,
2106 p_object_version_number => p_rec.object_version_number
2107 );
2108 chk_reinstate_cd(p_lee_rsn_id => p_rec.lee_rsn_id,
2109 p_reinstate_cd => p_rec.reinstate_cd,
2110 p_effective_date => p_effective_date,
2111 p_object_version_number => p_rec.object_version_number
2112 );
2113
2114 --
2115 chk_defer_flag_set_pln_plip
2116 (p_lee_rsn_id => p_rec.lee_rsn_id
2117 ,p_popl_enrt_typ_cycl_id => p_rec.popl_enrt_typ_cycl_id
2118 ,p_object_version_number => p_rec.object_version_number
2119 ,p_effective_date => p_effective_date
2120 ,p_defer_deenrol_flag => p_rec.defer_deenrol_flag
2121 ,p_business_group_id => p_rec.business_group_id
2122 );
2123 --
2124 chk_defer_flag_lookup
2125 (p_lee_rsn_id => p_rec.lee_rsn_id
2126 ,p_effective_date => p_effective_date
2127 ,p_defer_deenrol_flag => p_rec.defer_deenrol_flag
2128 ,p_object_version_number => p_rec.object_version_number
2129 );
2130 --
2131 hr_utility.set_location(' Leaving:'||l_proc, 10);
2132 --
2133 End update_validate;
2134 --
2135 -- ----------------------------------------------------------------------------
2136 -- |---------------------------< delete_validate >----------------------------|
2137 -- ----------------------------------------------------------------------------
2138 Procedure delete_validate
2139 (p_rec in ben_len_shd.g_rec_type,
2140 p_effective_date in date,
2141 p_datetrack_mode in varchar2,
2142 p_validation_start_date in date,
2143 p_validation_end_date in date) is
2144 --
2145 l_proc varchar2(72) := g_package||'delete_validate';
2146 --
2147 Begin
2148 hr_utility.set_location('Entering:'||l_proc, 5);
2149 --
2150 -- Call all supporting business operations
2151 --
2152 dt_delete_validate
2153 (p_datetrack_mode => p_datetrack_mode,
2154 p_validation_start_date => p_validation_start_date,
2155 p_validation_end_date => p_validation_end_date,
2156 p_lee_rsn_id => p_rec.lee_rsn_id);
2157 --
2158 hr_utility.set_location(' Leaving:'||l_proc, 10);
2159 End delete_validate;
2160 --
2161 --
2162 -- ---------------------------------------------------------------------------
2163 -- |---------------------< return_legislation_code >-------------------------|
2164 -- ---------------------------------------------------------------------------
2165 --
2166 function return_legislation_code
2167 (p_lee_rsn_id in number) return varchar2 is
2168 --
2169 -- Declare cursor
2170 --
2171 cursor csr_leg_code is
2172 select a.legislation_code
2173 from per_business_groups a,
2174 ben_lee_rsn_f b
2175 where b.lee_rsn_id = p_lee_rsn_id
2176 and a.business_group_id = b.business_group_id;
2177 --
2178 -- Declare local variables
2179 --
2180 l_legislation_code varchar2(150);
2181 l_proc varchar2(72) := g_package||'return_legislation_code';
2182 --
2183 begin
2184 --
2185 hr_utility.set_location('Entering:'|| l_proc, 10);
2186 --
2187 -- Ensure that all the mandatory parameter are not null
2188 --
2189 hr_api.mandatory_arg_error(p_api_name => l_proc,
2190 p_argument => 'lee_rsn_id',
2191 p_argument_value => p_lee_rsn_id);
2192 --
2193 open csr_leg_code;
2194 --
2195 fetch csr_leg_code into l_legislation_code;
2196 --
2197 if csr_leg_code%notfound then
2198 --
2199 close csr_leg_code;
2200 --
2201 -- The primary key is invalid therefore we must error
2202 --
2203 hr_utility.set_message(801,'HR_7220_INVALID_PRIMARY_KEY');
2204 hr_utility.raise_error;
2205 --
2206 end if;
2207 --
2208 close csr_leg_code;
2209 --
2210 hr_utility.set_location(' Leaving:'|| l_proc, 20);
2211 --
2212 return l_legislation_code;
2213 --
2214 end return_legislation_code;
2215 --
2216 end ben_len_bus;