[Home] [Help]
PACKAGE BODY: APPS.BEN_PEN_BUS
Source
1 Package Body ben_pen_bus as
2 /* $Header: bepenrhi.pkb 120.23.12020000.2 2012/07/03 14:17:34 amnaraya ship $ */
3 --
4 -- ------------------------------------------------------------------------------------------------------------------------------------------------
5 --
6 g_package varchar2(33) := ' ben_pen_bus.'; -- Global package name
7 --
8 -- ----------------------------------------------------------------------------
9 -- |------< chk_prtt_enrt_rslt_id >------|
10 -- ----------------------------------------------------------------------------
11 --
12 -- Description
13 -- This procedure is used to check that the primary key for the table
14 -- is created properly. It should be null on insert and
15 -- should not be able to be updated.
16 --
17 -- Pre Conditions
18 -- None.
19 --
20 -- In Parameters
21 -- prtt_enrt_rslt_id PK of record being inserted or updated.
22 -- effective_date Effective Date of session
23 -- object_version_number Object version number of record being
24 -- inserted or updated.
25 --
26 -- Post Success
27 -- Processing continues
28 --
29 -- Post Failure
30 -- Errors handled by the procedure
31 --
32 -- Access Status
33 -- Internal table handler use only.
34 --
35 Procedure chk_prtt_enrt_rslt_id(p_prtt_enrt_rslt_id in number
36 ,p_effective_date in date
37 ,p_object_version_number in number
38 ) is
39 l_proc varchar2(72) := g_package||'chk_prtt_enrt_rslt_id';
40 l_api_updating boolean;
41 Begin
42 hr_utility.set_location('Entering:'||l_proc, 5);
43 l_api_updating := ben_pen_shd.api_updating
44 (p_effective_date => p_effective_date,
45 p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
46 p_object_version_number => p_object_version_number);
47 if (l_api_updating
48 and nvl(p_prtt_enrt_rslt_id,hr_api.g_number)
49 <> ben_pen_shd.g_old_rec.prtt_enrt_rslt_id) then
50 --
51 -- raise error as PK has changed
52 --
53 ben_pen_shd.constraint_error('BEN_PRTT_ENRT_RSLT_PK');
54 --
55 elsif not l_api_updating then
56 --
57 -- check if PK is null
58 --
59 if p_prtt_enrt_rslt_id is not null then
60 --
61 -- raise error as PK is not null
62 --
63 ben_pen_shd.constraint_error('BEN_PRTT_ENRT_RSLT_PK');
64 --
65 end if;
66 --
67 end if;
68 hr_utility.set_location('Leaving:'||l_proc, 10);
69 End chk_prtt_enrt_rslt_id;
70 --
71 -- ----------------------------------------------------------------------------
72 -- |------< chk_enrt_ovridn_flag >------|
73 -- ----------------------------------------------------------------------------
74 --
75 -- Description
76 -- This procedure is used to check that the lookup value is valid.
77 --
78 -- Pre Conditions
79 -- None.
80 --
81 -- In Parameters
82 -- prtt_enrt_rslt_id PK of record being inserted or updated.
83 -- enrt_ovridn_flag Value of lookup code.
84 -- effective_date effective date
85 -- object_version_number Object version number of record being
86 -- inserted or updated.
87 --
88 -- Post Success
89 -- Processing continues
90 --
91 -- Post Failure
92 -- Error handled by procedure
93 --
94 -- Access Status
95 -- Internal table handler use only.
96 --
97 Procedure chk_enrt_ovridn_flag(p_prtt_enrt_rslt_id in number
98 ,p_enrt_ovridn_flag in varchar2
99 ,p_effective_date in date
100 ,p_object_version_number in number
101 ) is
102 l_proc varchar2(72) := g_package||'chk_enrt_ovridn_flag';
103 l_api_updating boolean;
104 Begin
105 --
106 hr_utility.set_location('Entering:'||l_proc, 5);
107 --
108 l_api_updating := ben_pen_shd.api_updating
109 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
110 p_effective_date => p_effective_date,
111 p_object_version_number => p_object_version_number);
112 --
113 if (l_api_updating
114 and p_enrt_ovridn_flag
115 <> nvl(ben_pen_shd.g_old_rec.enrt_ovridn_flag,hr_api.g_varchar2)
116 or not l_api_updating) then
117 --
118 -- check if value of lookup falls within lookup type.
119 --
120 --
121 if hr_api.not_exists_in_hr_lookups
122 (p_lookup_type => 'YES_NO',
123 p_lookup_code => p_enrt_ovridn_flag,
124 p_effective_date => p_effective_date) then
125 --
126 -- raise error as does not exist as lookup
127 --
128 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
129 fnd_message.set_token('FIELD', p_enrt_ovridn_flag);
130 fnd_message.set_token('TYPE','YES_NO');
131 fnd_message.raise_error;
132 --
133 end if;
134 --
135 end if;
136 --
137 hr_utility.set_location('Leaving:'||l_proc,10);
138 --
139 end chk_enrt_ovridn_flag;
140 --
141 -- ----------------------------------------------------------------------------
142 -- |------< chk_no_lngr_elig_flag >------|
143 -- ----------------------------------------------------------------------------
144 --
145 -- Description
146 -- This procedure is used to check that the lookup value is valid.
147 --
148 -- Pre Conditions
149 -- None.
150 --
151 -- In Parameters
152 -- prtt_enrt_rslt_id PK of record being inserted or updated.
153 -- no_lngr_elig_flag Value of lookup code.
154 -- effective_date effective date
155 -- object_version_number Object version number of record being
156 -- inserted or updated.
157 --
158 -- Post Success
159 -- Processing continues
160 --
161 -- Post Failure
162 -- Error handled by procedure
163 --
164 -- Access Status
165 -- Internal table handler use only.
166 --
167 Procedure chk_no_lngr_elig_flag(p_prtt_enrt_rslt_id in number
168 ,p_no_lngr_elig_flag in varchar2
169 ,p_effective_date in date
170 ,p_object_version_number in number
171 ) is
172 l_proc varchar2(72) := g_package||'chk_no_lngr_elig_flag';
173 l_api_updating boolean;
174 Begin
175 --
176 hr_utility.set_location('Entering:'||l_proc, 5);
177 --
178 l_api_updating := ben_pen_shd.api_updating
179 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
180 p_effective_date => p_effective_date,
181 p_object_version_number => p_object_version_number);
182 --
183 if (l_api_updating
184 and p_no_lngr_elig_flag
185 <> nvl(ben_pen_shd.g_old_rec.no_lngr_elig_flag,hr_api.g_varchar2)
186 or not l_api_updating) then
187 --
188 -- check if value of lookup falls within lookup type.
189 --
190 --
191 if hr_api.not_exists_in_hr_lookups
192 (p_lookup_type => 'YES_NO',
193 p_lookup_code => p_no_lngr_elig_flag,
194 p_effective_date => p_effective_date) then
195 --
196 -- raise error as does not exist as lookup
197 --
198 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
199 fnd_message.set_token('FIELD', p_no_lngr_elig_flag);
200 fnd_message.set_token('TYPE','YES_NO');
201 fnd_message.raise_error;
202 --
203 end if;
204 --
205 end if;
206 --
207 hr_utility.set_location('Leaving:'||l_proc,10);
208 --
209 end chk_no_lngr_elig_flag;
210 --
211 --
212 -- ----------------------------------------------------------------------------
213 -- |------< chk_enrt_mthd_cd >------|
214 -- ----------------------------------------------------------------------------
215 --
216 -- Description
217 -- This procedure is used to check that the lookup value is valid.
218 --
219 -- Pre Conditions
220 -- None.
221 --
222 -- In Parameters
223 -- prtt_enrt_rslt_id PK of record being inserted or updated.
224 -- enrt_mthd_cd Value of lookup code.
225 -- effective_date effective date
226 -- object_version_number Object version number of record being
227 -- inserted or updated.
228 --
229 -- Post Success
230 -- Processing continues
231 --
232 -- Post Failure
233 -- Error handled by procedure
234 --
235 -- Access Status
236 -- Internal table handler use only.
237 --
238 Procedure chk_enrt_mthd_cd(p_prtt_enrt_rslt_id in number,
239 p_enrt_mthd_cd in varchar2,
240 p_effective_date in date,
241 p_object_version_number in number) is
242 --
243 l_proc varchar2(72) := g_package||'chk_enrt_mthd_cd';
244 l_api_updating boolean;
245 --
246 Begin
247 --
248 hr_utility.set_location('Entering:'||l_proc, 5);
249 --
250 l_api_updating := ben_pen_shd.api_updating
251 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
252 p_effective_date => p_effective_date,
253 p_object_version_number => p_object_version_number);
254 --
255 if (l_api_updating
256 and p_enrt_mthd_cd
257 <> nvl(ben_pen_shd.g_old_rec.enrt_mthd_cd,hr_api.g_varchar2)
258 or not l_api_updating) then
259 --
260 -- check if value of lookup falls within lookup type.
261 --
262 --
263 if hr_api.not_exists_in_hr_lookups
264 (p_lookup_type => 'BEN_ENRT_RSLT_MTHD',
265 p_lookup_code => p_enrt_mthd_cd,
266 p_effective_date => p_effective_date) then
267 --
268 -- raise error as does not exist as lookup
269 --
270 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
271 fnd_message.set_token('FIELD',p_enrt_mthd_cd);
272 fnd_message.set_token('TYPE','BEN_ENRT_RSLT_MTHD');
273 fnd_message.raise_error;
274 end if;
275 --
276 end if;
277 hr_utility.set_location('Leaving:'||l_proc,10);
278 end chk_enrt_mthd_cd;
279 --
280 -- ----------------------------------------------------------------------------
281 -- |------< chk_uom >------|
282 -- ----------------------------------------------------------------------------
283 --
284 -- Description
285 -- This procedure is used to check that the lookup value is valid.
286 --
287 -- Pre Conditions
288 -- None.
289 --
290 -- In Parameters
291 -- prtt_enrt_rslt_id PK of record being inserted or updated.
292 -- uom Value of lookup code.
293 -- effective_date effective date
294 -- object_version_number Object version number of record being
295 -- inserted or updated.
296 --
297 -- Post Success
298 -- Processing continues
299 --
300 -- Post Failure
301 -- Error handled by procedure
302 --
303 -- Access Status
304 -- Internal table handler use only.
305 --
306 Procedure chk_uom (p_prtt_enrt_rslt_id in number
307 ,p_uom in varchar2
308 ,p_effective_date in date
309 ,p_object_version_number in number) is
310 l_proc varchar2(72) := g_package||'chk_uom';
311 l_api_updating boolean;
312 l_dummy varchar2(1);
313
314 cursor c1 is
315 select null
316 from fnd_currencies_tl
317 where currency_code = p_uom ;
318 Begin
319 --
320 hr_utility.set_location('Entering:'||l_proc, 5);
321 --
322 l_api_updating := ben_pen_shd.api_updating
323 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
324 p_effective_date => p_effective_date,
325 p_object_version_number => p_object_version_number);
326 --
327 if (l_api_updating
328 and p_uom
329 <> nvl(ben_pen_shd.g_old_rec.uom,hr_api.g_varchar2)
330 or not l_api_updating)
331 and p_uom is not null then
332 --
333 -- check if value of lookup falls within lookup type.
334 --
335 open c1;
336 fetch c1 into l_dummy;
337 if c1%notfound then
338 --
339 -- raise error as does not exist as lookup
340 --
341 close c1;
342 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
343 fnd_message.set_token('FIELD', p_uom );
344 fnd_message.set_token('TYPE','FND_CURRENCY_TBL');
345 fnd_message.raise_error;
346 end if;
347 close c1;
348 --
349 end if;
350 --
351 hr_utility.set_location('Leaving:'||l_proc,10);
352 --
353 end chk_uom ;
354 --
355 -- ----------------------------------------------------------------------------
356 -- |------< chk_prtt_is_cvrd_flag >------|
357 -- ----------------------------------------------------------------------------
358 --
359 -- Description
360 -- This procedure is used to check that the lookup value is valid.
361 --
362 -- Pre Conditions
363 -- None.
364 --
365 -- In Parameters
366 -- prtt_enrt_rslt_id PK of record being inserted or updated.
367 -- prtt_is_cvrd_flag Value of lookup code.
368 -- effective_date effective date
369 -- object_version_number Object version number of record being
370 -- inserted or updated.
371 --
372 -- Post Success
373 -- Processing continues
374 --
375 -- Post Failure
376 -- Error handled by procedure
377 --
378 -- Access Status
379 -- Internal table handler use only.
380 --
381 Procedure chk_prtt_is_cvrd_flag(p_prtt_enrt_rslt_id in number,
382 p_prtt_is_cvrd_flag in varchar2,
383 p_effective_date in date,
384 p_object_version_number in number) is
385 --
386 l_proc varchar2(72) := g_package||'chk_prtt_is_cvrd_flag';
387 l_api_updating boolean;
388 --
389 Begin
390 --
391 hr_utility.set_location('Entering:'||l_proc, 5);
392 --
393 l_api_updating := ben_pen_shd.api_updating
394 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
395 p_effective_date => p_effective_date,
396 p_object_version_number => p_object_version_number);
397 --
398 if (l_api_updating
399 and p_prtt_is_cvrd_flag
400 <> nvl(ben_pen_shd.g_old_rec.prtt_is_cvrd_flag,hr_api.g_varchar2)
401 or not l_api_updating) then
402 --
403 -- check if value of lookup falls within lookup type.
404 --
405 --
406 if hr_api.not_exists_in_hr_lookups
407 (p_lookup_type => 'YES_NO',
408 p_lookup_code => p_prtt_is_cvrd_flag,
409 p_effective_date => p_effective_date) then
410 --
411 -- raise error as does not exist as lookup
412 --
413 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
414 fnd_message.set_token('FIELD',p_prtt_is_cvrd_flag);
415 fnd_message.set_token('TYPE','YES_NO');
416 fnd_message.raise_error;
417 end if;
418 --
419 end if;
420 --
421 hr_utility.set_location('Leaving:'||l_proc,10);
422 --
423 end chk_prtt_is_cvrd_flag;
424 --
425 -- ----------------------------------------------------------------------------
426 -- |------< chk_sspndd_flag >------|
427 -- ----------------------------------------------------------------------------
428 --
429 -- Description
430 -- This procedure is used to check that the lookup value is valid.
431 --
432 -- Pre Conditions
433 -- None.
434 --
435 -- In Parameters
436 -- prtt_enrt_rslt_id PK of record being inserted or updated.
437 -- sspndd_flag Value of lookup code.
438 -- effective_date effective date
439 -- object_version_number Object version number of record being
440 -- inserted or updated.
441 --
442 -- Post Success
443 -- Processing continues
444 --
445 -- Post Failure
446 -- Error handled by procedure
447 --
448 -- Access Status
449 -- Internal table handler use only.
450 --
451 Procedure chk_sspndd_flag(p_prtt_enrt_rslt_id in number,
452 p_sspndd_flag in varchar2,
453 p_effective_date in date,
454 p_object_version_number in number) is
455 --
456 l_proc varchar2(72) := g_package||'chk_sspndd_flag';
457 l_api_updating boolean;
458 --
459 Begin
460 --
461 hr_utility.set_location('Entering:'||l_proc, 5);
462 --
463 l_api_updating := ben_pen_shd.api_updating
464 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
465 p_effective_date => p_effective_date,
466 p_object_version_number => p_object_version_number);
467 --
468 if (l_api_updating
469 and p_sspndd_flag <>
470 nvl(ben_pen_shd.g_old_rec.sspndd_flag,hr_api.g_varchar2)
471 or not l_api_updating) then
472 --
473 -- check if value of lookup falls within lookup type.
474 --
475 --
476 if hr_api.not_exists_in_hr_lookups
477 (p_lookup_type => 'YES_NO',
478 p_lookup_code => p_sspndd_flag,
479 p_effective_date => p_effective_date) then
480 --
481 -- raise error as does not exist as lookup
482 --
483 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
484 fnd_message.set_token('FIELD', p_sspndd_flag);
485 fnd_message.set_token('TYPE','YES_NO');
486 fnd_message.raise_error;
487 end if;
488 --
489 end if;
490 --
491 hr_utility.set_location('Leaving:'||l_proc,10);
492 --
493 end chk_sspndd_flag;
494 --
495 -- ----------------------------------------------------------------------------
496 -- |------< chk_comp_lvl_cd >------|
497 -- ----------------------------------------------------------------------------
498 --
499 -- Description
500 -- This procedure is used to check that the lookup value is valid.
501 --
502 -- Pre Conditions
503 -- None.
504 --
505 -- In Parameters
506 -- prtt_enrt_rslt_id PK of record being inserted or updated.
507 -- sspndd_flag Value of lookup code.
508 -- effective_date effective date
509 -- object_version_number Object version number of record being
510 -- inserted or updated.
511 --
512 -- Post Success
513 -- Processing continues
514 --
515 -- Post Failure
516 -- Error handled by procedure
517 --
518 -- Access Status
519 -- Internal table handler use only.
520 --
521 Procedure chk_comp_lvl_cd(p_prtt_enrt_rslt_id in number,
522 p_comp_lvl_cd in varchar2,
523 p_effective_date in date,
524 p_object_version_number in number) is
525 --
526 l_proc varchar2(72) := g_package||'chk_comp_lvl_cd';
527 l_api_updating boolean;
528 --
529 Begin
530 --
531 hr_utility.set_location('Entering:'||l_proc, 5);
532 --
533 l_api_updating := ben_pen_shd.api_updating
534 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
535 p_effective_date => p_effective_date,
536 p_object_version_number => p_object_version_number);
537 --
538 if (l_api_updating
539 and p_comp_lvl_cd <>
540 nvl(ben_pen_shd.g_old_rec.comp_lvl_cd,hr_api.g_varchar2)
541 or not l_api_updating) then
542 --
543 -- check if value of lookup falls within lookup type.
544 --
545 --
546 if hr_api.not_exists_in_hr_lookups
547 (p_lookup_type => 'BEN_COMP_LVL',
548 p_lookup_code => p_comp_lvl_cd,
549 p_effective_date => p_effective_date) then
550 --
551 -- raise error as does not exist as lookup
552 --
553 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
554 fnd_message.set_token('FIELD', 'p_comp_lvl_cd '||p_comp_lvl_cd);
555 fnd_message.set_token('TYPE','BEN_COMP_LVL');
556 fnd_message.raise_error;
557 end if;
558 --
559 end if;
560 --
561 hr_utility.set_location('Leaving:'||l_proc,10);
562 --
563 end chk_comp_lvl_cd;
564 --
565 -- --------------------------------------------------------------------------
566 -- |------------------------< chk_bnft_nnmntry_uom >-------------------------|
567 -- --------------------------------------------------------------------------
568 --
569 -- Description
570 -- This procedure is used to check that the lookup value is valid.
571 --
572 -- Pre Conditions
573 -- None.
574 --
575 -- In Parameters
576 -- prtt_enrt_rslt_id PK of record being inserted or updated.
577 -- bnft_nnmntry_uom Value of lookup code.
578 -- effective_date effective date
579 -- object_version_number Object version number of record being
580 -- inserted or updated.
581 --
582 -- Post Success
583 -- Processing continues
584 --
585 -- Post Failure
586 -- Error handled by procedure
587 --
588 -- Access Status
589 -- Internal table handler use only.
590 --
591 Procedure chk_bnft_nnmntry_uom(p_prtt_enrt_rslt_id in number,
592 p_bnft_nnmntry_uom in varchar2,
593 p_effective_date in date,
594 p_object_version_number in number) is
595 --
596 l_proc varchar2(72) := g_package||'chk_bnft_nnmntry_uom';
597 l_api_updating boolean;
598 --
599 Begin
600 --
601 hr_utility.set_location('Entering:'||l_proc, 5);
602 --
603 l_api_updating := ben_pen_shd.api_updating
604 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
605 p_effective_date => p_effective_date,
606 p_object_version_number => p_object_version_number);
607 --
608 if (l_api_updating
609 and p_bnft_nnmntry_uom
610 <> nvl(ben_pen_shd.g_old_rec.bnft_nnmntry_uom,hr_api.g_varchar2)
611 or not l_api_updating)
612 and p_bnft_nnmntry_uom is not null then
613 --
614 -- check if value of lookup falls within lookup type.
615 --
616 if hr_api.not_exists_in_hr_lookups
617 (p_lookup_type => 'BEN_NNMNTRY_UOM',
618 p_lookup_code => p_bnft_nnmntry_uom,
619 p_effective_date => p_effective_date) then
620 --
621 -- raise error as does not exist as lookup
622 --
623 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
624 fnd_message.set_token('FIELD',p_bnft_nnmntry_uom);
625 fnd_message.set_token('TYPE','BEN_NNMNTRY_UOM');
626 fnd_message.raise_error;
627 end if;
628 end if;
629 hr_utility.set_location('Leaving:'||l_proc,10);
630 --
631 end chk_bnft_nnmntry_uom;
632 --
633 -- --------------------------------------------------------------------------
634 -- |------< chk_bnft_typ_cd >------|
635 -- --------------------------------------------------------------------------
636 --
637 -- Description
638 -- This procedure is used to check that the lookup value is valid.
639 --
640 -- Pre Conditions
641 -- None.
642 --
643 -- In Parameters
644 -- prtt_enrt_rslt_id PK of record being inserted or updated.
645 -- bnft_typ_cd Value of lookup code.
646 -- effective_date effective date
647 -- object_version_number Object version number of record being
648 -- inserted or updated.
649 --
650 -- Post Success
651 -- Processing continues
652 --
653 -- Post Failure
654 -- Error handled by procedure
655 --
656 -- Access Status
657 -- Internal table handler use only.
658 --
659 Procedure chk_bnft_typ_cd(p_prtt_enrt_rslt_id in number,
660 p_bnft_typ_cd in varchar2,
661 p_effective_date in date,
662 p_object_version_number in number) is
663
664 l_proc varchar2(72) := g_package||'chk_bnft_typ_cd';
665 l_api_updating boolean;
666 --
667 Begin
668 --
669 hr_utility.set_location('Entering:'||l_proc, 5);
670 --
671 l_api_updating := ben_pen_shd.api_updating
672 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
673 p_effective_date => p_effective_date,
674 p_object_version_number => p_object_version_number);
675 --
676 if (l_api_updating
677 and p_bnft_typ_cd
678 <> nvl(ben_pen_shd.g_old_rec.bnft_typ_cd,hr_api.g_varchar2)
679 or not l_api_updating)
680 and p_bnft_typ_cd is not null then
681 --
682 -- check if value of lookup falls within lookup type.
683 --
684 if hr_api.not_exists_in_hr_lookups
685 (p_lookup_type => 'BEN_BNFT_TYP',
686 p_lookup_code => p_bnft_typ_cd,
687 p_effective_date => p_effective_date) then
688 --
689 -- raise error as does not exist as lookup
690 --
691 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
692 fnd_message.set_token('FIELD',p_bnft_typ_cd);
693 fnd_message.set_token('TYPE','BEN_BNFT_TYP');
694 fnd_message.raise_error;
695 end if;
696 --
697 end if;
698 hr_utility.set_location('Leaving:'||l_proc,10);
699
700 end chk_bnft_typ_cd;
701 --
702 -- --------------------------------------------------------------------------
703 -- |------< chk_prtt_enrt_rslt_stat_cd >------|
704 -- --------------------------------------------------------------------------
705 --
706 -- Description
707 -- This procedure is used to check that the lookup value is valid.
708 --
709 -- Pre Conditions
710 -- None.
711 --
712 -- In Parameters
713 -- prtt_enrt_rslt_id PK of record being inserted or updated.
714 -- prtt_enrt_rslt_stat_cd Value of lookup code.
715 -- effective_date effective date
716 -- object_version_number Object version number of record being
717 -- inserted or updated.
718 --
719 -- Post Success
720 -- Processing continues
721 --
722 -- Post Failure
723 -- Error handled by procedure
724 --
725 -- Access Status
726 -- Internal table handler use only.
727 --
728 Procedure chk_prtt_enrt_rslt_stat_cd(p_prtt_enrt_rslt_id in number,
729 p_prtt_enrt_rslt_stat_cd in varchar2,
730 p_effective_date in date,
731 p_object_version_number in number) is
732 --
733 l_proc varchar2(72) := g_package||'chk_prtt_enrt_rslt_stat_cd';
734 l_api_updating boolean;
735 --
736 Begin
737 --
738 hr_utility.set_location('Entering:'||l_proc, 5);
739 --
740 l_api_updating := ben_pen_shd.api_updating
741 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
742 p_effective_date => p_effective_date,
743 p_object_version_number => p_object_version_number);
744 --
745 if (l_api_updating
746 and p_prtt_enrt_rslt_stat_cd
747 <> nvl(ben_pen_shd.g_old_rec.prtt_enrt_rslt_stat_cd,hr_api.g_varchar2)
748 or not l_api_updating)
749 and p_prtt_enrt_rslt_stat_cd is not null then
750 --
751 -- check if value of lookup falls within lookup type.
752 --
753 if hr_api.not_exists_in_hr_lookups
754 (p_lookup_type => 'BEN_PRTT_ENRT_RSLT_STAT',
755 p_lookup_code => p_prtt_enrt_rslt_stat_cd,
756 p_effective_date => p_effective_date) then
757 --
758 -- raise error as does not exist as lookup
759 --
760 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
761 fnd_message.set_token('FIELD',p_prtt_enrt_rslt_stat_cd);
762 fnd_message.set_token('TYPE','BEN_PRTT_ENRT_RSLT_STAT');
763 fnd_message.raise_error;
764 end if;
765 --
766 end if;
767 hr_utility.set_location('Leaving:'||l_proc,10);
768
769 end chk_prtt_enrt_rslt_stat_cd;
770 --
771 --
772 -- -------------------------------------------------------------------------
773 -- |------< chk_enrt_ovrid_rsn_cd >------|
774 -- -------------------------------------------------------------------------
775 --
776 -- Description
777 -- This procedure is used to check that the lookup value is valid.
778 --
779 -- Pre Conditions
780 -- None.
781 --
782 -- In Parameters
783 -- prtt_enrt_rslt_id PK of record being inserted or updated.
784 -- enrt_ovrid_rsn_cd Value of lookup code.
785 -- effective_date effective date
786 -- object_version_number Object version number of record being
787 -- inserted or updated.
788 --
789 -- Post Success
790 -- Processing continues
791 --
792 -- Post Failure
793 -- Error handled by procedure
794 --
795 -- Access Status
796 -- Internal table handler use only.
797 --
798 Procedure chk_enrt_ovrid_rsn_cd
799 (p_prtt_enrt_rslt_id in number
800 ,p_enrt_ovrid_rsn_cd in varchar2
801 ,p_effective_date in date
802 ,p_object_version_number in number) is
803 --
804 l_proc varchar2(72) := g_package||'chk_enrt_ovrid_rsn_cd';
805 l_api_updating boolean;
806 --
807 Begin
808 --
809 hr_utility.set_location('Entering:'||l_proc, 5);
810 --
811 l_api_updating := ben_pen_shd.api_updating
812 (p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id,
813 p_effective_date => p_effective_date,
814 p_object_version_number => p_object_version_number);
815 if (l_api_updating
816 and p_enrt_ovrid_rsn_cd
817 <> nvl(ben_pen_shd.g_old_rec.enrt_ovrid_rsn_cd,hr_api.g_varchar2)
818 or not l_api_updating)
819 and p_enrt_ovrid_rsn_cd is not null then
820 --
821 -- check if value of lookup falls within lookup type.
822 --
823 if hr_api.not_exists_in_hr_lookups
824 (p_lookup_type => 'BEN_OVRID_RSN',
825 p_lookup_code => p_enrt_ovrid_rsn_cd,
826 p_effective_date => p_effective_date) then
827 --
828 -- raise error as does not exist as lookup
829 --
830 fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
831 fnd_message.set_token('FIELD', p_enrt_ovrid_rsn_cd);
832 fnd_message.set_token('TYPE','BEN_OVRID_RSN');
833 fnd_message.raise_error;
834 end if;
835 end if;
836 hr_utility.set_location('Leaving:'||l_proc,10);
837 end chk_enrt_ovrid_rsn_cd;
838 --
839 -- ---------------------------------------------------------------------------
840 -- |------------------------< crt_ordr_warning >----------------------------|
841 -- ---------------------------------------------------------------------------
842 -- Procedure used to create warning messages for crt_ordrs.
843 --
844 -- Description
845 -- This procedure is used to create warning messages for persons
846 -- not designated as covered dependents but reqired to be covered
847 -- under court orders.
848 --
849 -- Pre Conditions
850 -- None.
851 --
852 -- In Parameters
853 -- prtt_enrt_rslt_id PK of record being inserted or updated.
854 -- effective_date effective date
855 --
856 -- Post Success
857 -- Processing continues
858 --
859 -- Post Failure
860 -- Error handled by procedure
861 --
862 -- Access Status
863 -- Internal table handler use only.
864 --
865 PROCEDURE crt_ordr_warning (
866 p_prtt_enrt_rslt_id IN NUMBER,
867 p_per_in_ler_id IN NUMBER,
868 p_person_id IN NUMBER,
869 p_pgm_id IN NUMBER,
870 p_pl_id IN NUMBER,
871 p_ptip_id IN NUMBER,
872 p_pl_typ_id IN NUMBER,
873 p_effective_date IN DATE,
874 p_enrt_cvg_strt_dt IN DATE,
875 p_enrt_cvg_thru_dt IN DATE,
876 p_business_group_id IN NUMBER
877 )
878 IS
879 --
880 l_proc VARCHAR2 (72) := g_package || 'crt_ordr_warning';
881 l_api_updating BOOLEAN;
882 l_level VARCHAR2 (30) := 'PL';
883 l_code VARCHAR2 (30);
884 --
885 l_pgm_rec ben_cobj_cache.g_pgm_inst_row;
886 l_pl_rec ben_cobj_cache.g_pl_inst_row;
887 l_ptip_rec ben_cobj_cache.g_ptip_inst_row;
888 l_benefit_name ben_pl_typ_f.NAME%TYPE;
889 --
890 l_lf_evt_ocrd_dt DATE;
891 --
892 CURSOR c_lf_evt_ocrd_dt
893 IS
894 SELECT lf_evt_ocrd_dt
895 FROM ben_per_in_ler pil
896 WHERE pil.per_in_ler_id = p_per_in_ler_id;
897 --
898 CURSOR c_crt_ordr
899 IS
900 SELECT per.first_name || ' ' || per.last_name NAME, lkp.meaning,
901 cvr.person_id, bpl.NAME, crt.crt_ordr_typ_cd
902 FROM ben_crt_ordr crt,
903 ben_crt_ordr_cvrd_per cvr,
904 per_all_people_f per,
905 per_contact_relationships con,
906 hr_lookups lkp,
907 ben_pl_f bpl
908 WHERE crt.crt_ordr_typ_cd IN ('QMCSO','QDRO')
909 AND crt.person_id = p_person_id
910 AND crt.pl_id = p_pl_id
911 AND crt.crt_ordr_id = cvr.crt_ordr_id
912 AND cvr.person_id = per.person_id
913 AND cvr.person_id = con.contact_person_id
914 AND con.contact_type = lkp.lookup_code
915 AND lkp.lookup_type = 'CONTACT'
916 AND p_effective_date BETWEEN NVL (lkp.start_date_active,
917 p_effective_date
918 )
919 AND NVL (lkp.end_date_active,
920 p_effective_date
921 )
922 AND GREATEST(l_lf_evt_ocrd_dt, p_enrt_cvg_strt_dt)
923 BETWEEN GREATEST (NVL (apls_perd_strtg_dt,
924 p_effective_date
925 ),
926 NVL (detd_qlfd_ordr_dt,
927 apls_perd_strtg_dt
928 )
929 )
930 AND NVL (apls_perd_endg_dt,
931 p_enrt_cvg_thru_dt
932 )
933 AND crt.business_group_id = p_business_group_id
934 AND cvr.business_group_id = p_business_group_id
935 AND p_effective_date BETWEEN NVL (con.date_start, p_effective_date)
936 AND NVL (con.date_end, p_effective_date)
937 AND con.business_group_id = p_business_group_id
938 AND bpl.pl_id = p_pl_id
939 AND p_effective_date BETWEEN NVL (bpl.effective_start_date,
940 p_effective_date
941 )
942 AND NVL (bpl.effective_end_date,
943 p_effective_date
944 )
945 UNION
946 SELECT per.first_name || ' ' || per.last_name NAME, lkp.meaning,
947 cvr.person_id, bpt.NAME, crt.crt_ordr_typ_cd
948 FROM ben_crt_ordr crt,
949 ben_crt_ordr_cvrd_per cvr,
950 per_all_people_f per,
951 per_contact_relationships con,
952 hr_lookups lkp,
953 ben_pl_typ_f bpt
954 WHERE crt.crt_ordr_typ_cd IN ('QMCSO','QDRO')
955 AND crt.person_id = p_person_id
956 AND crt.pl_typ_id = l_pl_rec.pl_typ_id
957 AND crt.crt_ordr_id = cvr.crt_ordr_id
958 AND cvr.person_id = per.person_id
959 AND cvr.person_id = con.contact_person_id
960 AND con.contact_type = lkp.lookup_code
961 AND lkp.lookup_type = 'CONTACT'
962 AND p_effective_date BETWEEN NVL (lkp.start_date_active,
963 p_effective_date
964 )
965 AND NVL (lkp.end_date_active,
966 p_effective_date
967 )
968 AND GREATEST(l_lf_evt_ocrd_dt, p_enrt_cvg_strt_dt)
969 BETWEEN GREATEST (NVL (apls_perd_strtg_dt,
970 p_effective_date
971 ),
972 NVL (detd_qlfd_ordr_dt,
973 apls_perd_strtg_dt
974 )
975 )
976 AND NVL (apls_perd_endg_dt,
977 p_enrt_cvg_thru_dt
978 )
979 AND crt.business_group_id = p_business_group_id
980 AND cvr.business_group_id = p_business_group_id
981 AND p_effective_date BETWEEN NVL (con.date_start, p_effective_date)
982 AND NVL (con.date_end, p_effective_date)
983 AND con.business_group_id = p_business_group_id
984 AND bpt.pl_typ_id = l_pl_rec.pl_typ_id
985 AND p_effective_date BETWEEN NVL (bpt.effective_start_date,
986 p_effective_date
987 )
988 AND NVL (bpt.effective_end_date,
989 p_effective_date
990 );
991
992 --
993 l_name VARCHAR2 (500); -- UTF8 Change Bug 2254683
994 l_contact_type VARCHAR2 (80); -- Bug 5706254
995 l_dpnt_id NUMBER (15);
996 l_crt_ordr_typ_cd VARCHAR2 (30);
997 l_crt_ordr_meaning VARCHAR2(80);
998
999 --
1000 -- Bug 4718038 : Check PDP record for court order warning as of life event occurred date
1001 --
1002 CURSOR c_elig_dpnt
1003 IS
1004 SELECT NULL
1005 FROM ben_elig_cvrd_dpnt_f pdp, ben_per_in_ler pil
1006 WHERE pdp.dpnt_person_id = l_dpnt_id
1007 AND pdp.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1008 AND p_effective_date BETWEEN pdp.effective_start_date
1009 AND pdp.effective_end_date
1010 AND GREATEST (pil.lf_evt_ocrd_dt, p_enrt_cvg_strt_dt) BETWEEN cvg_strt_dt
1011 AND cvg_thru_dt
1012 AND pdp.business_group_id = p_business_group_id
1013 AND pil.business_group_id = p_business_group_id
1014 AND pdp.per_in_ler_id = pil.per_in_ler_id
1015 AND pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT');
1016
1017 --
1018 l_dummy VARCHAR2 (1);
1019 l_message fnd_new_messages.message_name%TYPE := 'BEN_92430_CRT_ORD_WARNING';
1020 l_cobra_pgm BOOLEAN := FALSE;
1021 --
1022 BEGIN
1023 hr_utility.set_location ('Entering:' || l_proc, 5);
1024 --
1025 hr_utility.set_location ('Checking court order at PEN level', 12);
1026 --
1027 ben_cobj_cache.get_pl_dets (p_business_group_id => p_business_group_id,
1028 p_effective_date => p_effective_date,
1029 p_pl_id => p_pl_id,
1030 p_inst_row => l_pl_rec
1031 );
1032
1033 --
1034 --
1035 IF l_pl_rec.alws_qmcso_flag = 'Y' OR /* Qualified Medical Child Support Order */
1036 l_pl_rec.alws_qdro_flag = 'Y' /* Qualified Domestic Relations Order */
1037 THEN
1038 --
1039 IF p_pgm_id IS NOT NULL
1040 THEN
1041 --
1042 -- find the level from the program
1043 --
1044 ben_cobj_cache.get_pgm_dets
1045 (p_business_group_id => p_business_group_id,
1046 p_effective_date => p_effective_date,
1047 p_pgm_id => p_pgm_id,
1048 p_inst_row => l_pgm_rec
1049 );
1050 --
1051 l_level := l_pgm_rec.dpnt_dsgn_lvl_cd;
1052 --
1053 IF l_pgm_rec.pgm_typ_cd IN ('COBRANFLX', 'COBRAFLX')
1054 THEN
1055 --
1056 l_cobra_pgm := TRUE;
1057 --
1058 END IF;
1059 --
1060 ELSE
1061 --
1062 -- PLAN level
1063 l_level := 'PL';
1064 --
1065 END IF;
1066 --
1067 --
1068 -- Retrieve designation code
1069 --
1070 hr_utility.set_location ('Level = ' || l_level, 40);
1071
1072 IF l_level = 'PGM'
1073 THEN
1074 --
1075 l_code := l_pgm_rec.dpnt_dsgn_cd;
1076 --
1077 ELSIF l_level = 'PTIP'
1078 THEN
1079 --
1080 ben_cobj_cache.get_ptip_dets
1081 (p_business_group_id => p_business_group_id,
1082 p_effective_date => p_effective_date,
1083 p_ptip_id => p_ptip_id,
1084 p_inst_row => l_ptip_rec
1085 );
1086 --
1087 l_code := l_ptip_rec.dpnt_dsgn_cd;
1088 --
1089 ELSIF l_level = 'PL'
1090 THEN
1091 --
1092 l_code := l_pl_rec.dpnt_dsgn_cd;
1093 --
1094 ELSE
1095 --
1096 l_code := NULL;
1097 --
1098 END IF;
1099 --
1100 hr_utility.set_location ('dsgn code = ' || l_code, 40);
1101 --
1102 IF l_code IS NOT NULL AND
1103 NOT l_cobra_pgm
1104 THEN
1105 --
1106 /*
1107 hr_utility.set_location ('ACE p_prtt_enrt_rslt_id = ' || p_prtt_enrt_rslt_id, 9999 );
1108 hr_utility.set_location ('ACE p_effective_date = ' || p_effective_date, 9999 );
1109 hr_utility.set_location ('ACE p_business_group_id = ' || p_business_group_id, 9999 );
1110 hr_utility.set_location ('ACE p_enrt_cvg_strt_dt = ' || p_enrt_cvg_strt_dt, 9999 );
1111 */
1112 --
1113 OPEN c_lf_evt_ocrd_dt;
1114 --
1115 FETCH c_lf_evt_ocrd_dt into l_lf_evt_ocrd_dt;
1116 --
1117 CLOSE c_lf_evt_ocrd_dt;
1118 --
1119 OPEN c_crt_ordr;
1120 LOOP
1121 --
1122 FETCH c_crt_ordr INTO l_name,
1123 l_contact_type,
1124 l_dpnt_id,
1125 l_benefit_name,
1126 l_crt_ordr_typ_cd;
1127 --
1128 EXIT WHEN c_crt_ordr%NOTFOUND;
1129 --
1130 /*
1131 hr_utility.set_location ('ACE l_name = ' || l_name, 9999);
1132 hr_utility.set_location ('ACE l_dpnt_id = ' || l_dpnt_id, 9999);
1133 hr_utility.set_location ('ACE l_benefit_name = ' || l_benefit_name, 9999);
1134 hr_utility.set_location ('ACE l_lf_evt_ocrd_dt = ' || l_lf_evt_ocrd_dt, 9999 );
1135 hr_utility.set_location ('ACE l_crt_ordr_typ_cd = ' || l_crt_ordr_typ_cd, 9999 );
1136 */
1137 hr_utility.set_location('Found Court Order', 9999);
1138 --
1139 OPEN c_elig_dpnt;
1140 --
1141 FETCH c_elig_dpnt INTO l_dummy;
1142 --
1143 IF c_elig_dpnt%NOTFOUND
1144 THEN
1145 --
1146 hr_utility.set_location ('C_ELIG_DPNT Not Found', 9999);
1147 --
1148 l_crt_ordr_meaning := hr_general.decode_lookup
1149 (p_lookup_type => 'BEN_CRT_ORDR_TYP',
1150 p_lookup_code => l_crt_ordr_typ_cd
1151 );
1152 --
1153 ben_warnings.load_warning
1154 (p_application_short_name => 'BEN',
1155 p_message_name => l_message,
1156 p_parma => l_benefit_name,
1157 p_parmb => l_contact_type || ' , ' || l_name,
1158 p_parmc => l_crt_ordr_meaning,
1159 p_person_id => p_person_id
1160 );
1161 --
1162 END IF;
1163 --
1164 CLOSE c_elig_dpnt;
1165 --
1166 END LOOP;
1167 --
1168 CLOSE c_crt_ordr;
1169 --
1170 END IF;
1171 --
1172 END IF;
1173 --
1174 hr_utility.set_location ('Leaving:' || l_proc, 10);
1175 --
1176 END crt_ordr_warning;
1177 --
1178 --
1179 -- -------------------------------------------------------------------------
1180 -- |--------------------------< calc_mx_amt >------------------------------|
1181 -- -------------------------------------------------------------------------
1182 function calc_mx_amt(p_mx_cvg_rl number
1183 ,p_assignment_id number
1184 ,p_effective_date date
1185 -- 3427367
1186 ,p_business_group_id number
1187 ,p_pgm_id number
1188 ,p_pl_id number
1189 ,p_pl_typ_id number
1190 ,p_opt_id number
1191 ,p_ler_id number
1192 ,p_prtt_enrt_rslt_id number --3427367
1193 ,p_person_id number -- 5331889
1194 ) return number is
1195 l_proc varchar2(80) := g_package||'.calc_mx_amt';
1196 l_outputs ff_exec.outputs_t;
1197 -- l_return number(15); -- commented for Bug 14127835
1198 l_return number; --Bug 14127835
1199 l_step integer;
1200 -- 3427367
1201 cursor c_epe is
1202 select elig_per_elctbl_chc_id
1203 from ben_elig_per_elctbl_chc
1204 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
1205 --
1206 l_jurisdiction_code varchar2(30);
1207 l_elig_per_elctbl_chc_id number;
1208 -- 3427367
1209 begin
1210 hr_utility.set_location ('Entering '||l_proc,10);
1211 --
1212 -- Call formula initialise routine
1213 --
1214 -- 3427367
1215 open c_epe;
1216 fetch c_epe into l_elig_per_elctbl_chc_id;
1217 close c_epe;
1218 -- 3427367
1219 l_step := 20;
1220 l_outputs := benutils.formula
1221 (p_formula_id => p_mx_cvg_rl
1222 -- 3427367
1223 ,p_business_group_id => p_business_group_id
1224 ,p_organization_id => l_elig_per_elctbl_chc_id
1225 ,p_pgm_id => p_pgm_id
1226 ,p_pl_id => p_pl_id
1227 ,p_pl_typ_id => p_pl_typ_id
1228 ,p_opt_id => p_opt_id
1229 ,p_ler_id => p_ler_id
1230 ,p_jurisdiction_code => l_jurisdiction_code
1231 -- 3427367
1232 ,p_effective_date => p_effective_date
1233 ,p_assignment_id => p_assignment_id
1234 ,p_param1 => 'BEN_IV_PERSON_ID' -- Bug 5331889
1235 ,p_param1_value => to_char(p_person_id));
1236 --
1237 -- Formula will return Y or N
1238 --
1239 l_return := to_number(l_outputs(l_outputs.first).value);
1240
1241 return l_return;
1242 hr_utility.set_location ('Leaving '||l_proc,50);
1243 exception
1244 when others then
1245 hr_utility.set_location ('Fail in '||l_proc|| ' step in '||
1246 to_char(l_step),999);
1247 raise;
1248 end;
1249 --
1250 -- -------------------------------------------------------------------------
1251 -- |--------------------------< calc_mn_amt >------------------------------|
1252 -- -------------------------------------------------------------------------
1253 --
1254 function calc_mn_amt(p_mn_cvg_rl number
1255 ,p_assignment_id number
1256 ,p_effective_date date
1257 -- 3427367
1258 ,p_business_group_id number
1259 ,p_pgm_id number
1260 ,p_pl_id number
1261 ,p_pl_typ_id number
1262 ,p_opt_id number
1263 ,p_ler_id number
1264 ,p_prtt_enrt_rslt_id number --3427367
1265 ,p_person_id number -- 5331889
1266 ) return number is
1267
1268 l_proc varchar2(80) := g_package||'.calc_mn_amt';
1269 l_outputs ff_exec.outputs_t;
1270 l_return number(15);
1271 l_step integer;
1272 -- 3427367
1273 cursor c_epe is
1274 select elig_per_elctbl_chc_id
1275 from ben_elig_per_elctbl_chc
1276 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
1277 --
1278 l_jurisdiction_code varchar2(30);
1279 l_elig_per_elctbl_chc_id number;
1280 -- 3427367
1281 begin
1282 hr_utility.set_location ('Entering '||l_proc,70);
1283 --
1284 -- Call formula initialise routine
1285 --
1286 -- 3427367
1287 open c_epe;
1288 fetch c_epe into l_elig_per_elctbl_chc_id;
1289 close c_epe;
1290 -- 3427367
1291 l_step := 20;
1292 l_outputs := benutils.formula
1293 (p_formula_id => p_mn_cvg_rl
1294 -- 3427367
1295 ,p_business_group_id => p_business_group_id
1296 ,p_organization_id => l_elig_per_elctbl_chc_id
1297 ,p_pgm_id => p_pgm_id
1298 ,p_pl_id => p_pl_id
1299 ,p_pl_typ_id => p_pl_typ_id
1300 ,p_opt_id => p_opt_id
1301 ,p_ler_id => p_ler_id
1302 ,p_jurisdiction_code => l_jurisdiction_code
1303 -- 3427367
1304 ,p_effective_date => p_effective_date
1305 ,p_assignment_id => p_assignment_id
1306 ,p_param1 => 'BEN_IV_PERSON_ID' -- Bug 5331889
1307 ,p_param1_value => to_char(p_person_id));
1308 --
1309 -- Formula will return Y or N
1310 --
1311 l_return := to_number(l_outputs(l_outputs.first).value);
1312
1313 return l_return;
1314 hr_utility.set_location ('Leaving '||l_proc,70);
1315 exception
1316 when others then
1317 hr_utility.set_location ('Fail in '||l_proc || ' step in ' ||
1318 to_char(l_step),999);
1319 raise;
1320 end;
1321 --
1322 -- ----------------------------------------------------------------------------
1323 -- |--------------------------<get_opts_and_cvg >-----------------------------|
1324 -- ----------------------------------------------------------------------------
1325 --
1326 procedure get_opts_and_cvg
1327 (p_enrt_tbl in enrt_table
1328 ,p_enrt_cnt in binary_integer
1329 ,p_tot_amt out nocopy number
1330 ,p_tot_opts out nocopy number
1331 ) is
1332 l_tot_amt number := 0; -- number(13) := 0;
1333 l_tot_opts number := 0; -- number(8) := 0; bug 2649163
1334 l_max_amt number := 0; -- number(13) := 0;
1335 l_max_opts number := 0; -- number(8) := 0; bug 2649163
1336 l_tot_amt_no_interim number := 0;
1337 l_max_amt_no_interim number := 0;
1338 begin
1339 hr_utility.set_location('Entering get_opts_and_cvg ',1234);
1340 hr_utility.set_location(' p_enrt_cnt '||p_enrt_cnt,1234);
1341 for i in 1..p_enrt_cnt loop
1342 l_tot_amt := 0;
1343 l_tot_opts := 0;
1344 l_tot_amt_no_interim := 0 ;
1345 for j in 1..p_enrt_cnt loop
1346 if (p_enrt_tbl(i).enrt_cvg_strt_dt between
1347 p_enrt_tbl(j).enrt_cvg_strt_dt and
1348 nvl(p_enrt_tbl(j).enrt_cvg_thru_dt,hr_api.g_eot)
1349 ) then
1350 hr_utility.set_location(' j '||j,1234);
1351 if (p_enrt_tbl(j).calc_interm = 1) then
1352 if (nvl(p_enrt_tbl(j).SSPNDD_FLAG, 'X') <> 'Y') then
1353 hr_utility.set_location(' l_tot_amt before '||l_tot_amt,1234);
1354 l_tot_amt := l_tot_amt+nvl(p_enrt_tbl(j).bnft_amt,0);
1355 hr_utility.set_location(' l_tot_amt after '||l_tot_amt,1234);
1356 end if;
1357 else
1358 if p_enrt_tbl(j).interim_flag = 'N' then
1359 hr_utility.set_location(' interim_flag before '||l_tot_amt,1234);
1360 l_tot_amt := l_tot_amt + nvl(p_enrt_tbl(j).bnft_amt,0);
1361 hr_utility.set_location(' interim_flag after '||l_tot_amt,1234);
1362 end if;
1363 end if;
1364 if (p_enrt_tbl(j).interim_flag = 'N') then
1365 l_tot_opts := l_tot_opts + 1;
1366 l_tot_amt_no_interim:=l_tot_amt_no_interim+nvl(p_enrt_tbl(j).bnft_amt,0);
1367 end if;
1368 end if;
1369 end loop;
1370 hr_utility.set_location(' before l_max_amt '||l_max_amt,1234);
1371 l_max_amt := greatest(l_tot_amt, l_max_amt);
1372 l_max_opts := greatest(l_max_opts,l_tot_opts);
1373 l_max_amt_no_interim := greatest(l_max_amt_no_interim,l_tot_amt_no_interim);
1374 hr_utility.set_location(' after l_max_amt '||l_max_amt,1234);
1375 end loop;
1376 hr_utility.set_location(' before p_tot_amt '||p_tot_amt,1234);
1377 p_tot_amt := l_max_amt;
1378 p_tot_opts := l_max_opts;
1379 --p_tot_amt_no_interim := l_tot_amt_no_interim;
1380 hr_utility.set_location(' after p_tot_amt '||p_tot_amt,1234);
1381 end get_opts_and_cvg;
1382 --
1383 -- ----------------------------------------------------------------------------
1384 -- |--------------------------<get_pls_and_cvg >-----------------------------|
1385 -- ----------------------------------------------------------------------------
1386 --
1387 procedure get_pls_and_cvg
1388 (p_enrt_tbl in enrt_table
1389 ,p_enrt_cnt in binary_integer
1390 ,p_tot_amt out nocopy number
1391 ,p_tot_pls out nocopy number
1392 ,p_dpnt_cvd_by_othr_apls_flag out nocopy varchar2
1393 ,p_tot_amt_no_interim out nocopy number
1394 ) is
1395 type l_pl_table is table of number(15) index by binary_integer;
1396 l_tot_amt number := 0; -- number(13) := 0; bug 2649163
1397 l_tot_pls number := 0; -- number(8) := 0; bug 2649163
1398 l_max_amt number := 0; -- number(13) := 0; bug 2649163
1399 l_max_pls number := 0; -- number(8) := 0; bug 2649163
1400 -- l_cnt Binary_integer := 0;
1401 -- l_pl_tbl l_pl_table;
1402 -- l_not_found boolean := TRUE;
1403 l_tot_amt_no_interim number := 0;
1404 l_max_amt_no_interim number := 0;
1405 begin
1406 --
1407 p_dpnt_cvd_by_othr_apls_flag := 'N';
1408 --
1409 for i in 1..p_enrt_cnt loop
1410 /*
1411 hr_utility.set_location('i INSIDE p_enrt_tbl(i).pl_id'||p_enrt_tbl(i).pl_id,111);
1412 hr_utility.set_location('i INSIDE p_enrt_tbl(i).pl_id'||p_enrt_tbl(i).pl_id,111);
1413 hr_utility.set_location('i INSIDE SSPNDD_FLAG'||p_enrt_tbl(i).SSPNDD_FLAG,111);
1414 hr_utility.set_location('i INSIDE Bnft I'||p_enrt_tbl(i).bnft_amt,111);
1415 */
1416 l_tot_amt := 0;
1417 l_tot_pls := 0;
1418 l_tot_amt_no_interim := 0 ;
1419 --l_pl_tbl.delete;
1420 --l_cnt := 0;
1421 for j in 1..p_enrt_cnt loop
1422 --
1423 hr_utility.set_location('J INSIDE p_enrt_tbl(j).pl_id'||p_enrt_tbl(j).pl_id,111);
1424 hr_utility.set_location('J INSIDE p_enrt_tbl(i).pl_id'||p_enrt_tbl(i).pl_id,111);
1425 hr_utility.set_location('J INSIDE SSPNDD_FLAG'||p_enrt_tbl(j).SSPNDD_FLAG,111);
1426 hr_utility.set_location('i INSIDE Bnft J '||p_enrt_tbl(j).bnft_amt,111);
1427 hr_utility.set_location('i INSIDE interim J'||p_enrt_tbl(j).interim_flag,111);
1428 --
1429 if (p_enrt_tbl(i).enrt_cvg_strt_dt between
1430 p_enrt_tbl(j).enrt_cvg_strt_dt and
1431 nvl(p_enrt_tbl(j).enrt_cvg_thru_dt,hr_api.g_eot)
1432 ) then
1433 --
1434 /*
1435 hr_utility.set_location('INSIDE p_enrt_tbl(j).pl_id'||p_enrt_tbl(j).pl_id,111);
1436 hr_utility.set_location('INSIDE p_enrt_tbl(i).pl_id'||p_enrt_tbl(i).pl_id,111);
1437 hr_utility.set_location('INSIDE SSPNDD_FLAG'||p_enrt_tbl(j).SSPNDD_FLAG,111);
1438 hr_utility.set_location('INSIDE calc_inter'||p_enrt_tbl(j).calc_interm,111);
1439 */
1440 --
1441 if (p_enrt_tbl(j).calc_interm = 1) then
1442 if (nvl(p_enrt_tbl(j).SSPNDD_FLAG, 'X') <> 'Y') then
1443 l_tot_amt := l_tot_amt+nvl(p_enrt_tbl(j).bnft_amt,0);
1444 end if;
1445 else
1446 if p_enrt_tbl(j).interim_flag = 'N' then
1447 l_tot_amt := l_tot_amt + nvl(p_enrt_tbl(j).bnft_amt,0);
1448 end if;
1449 end if;
1450 if (p_enrt_tbl(j).interim_flag = 'N') then
1451 -- Bug 1178690 comment out the pl_id check
1452 -- l_not_found := TRUE;
1453 -- for l in 1..l_cnt loop
1454 -- if (p_enrt_tbl(j).pl_id = l_pl_tbl(l)) then
1455 -- l_not_found := FALSE;
1456 -- exit;
1457 -- end if;
1458 -- end loop;
1459 -- if (l_not_found) then
1460 --
1461 /*
1462 hr_utility.set_location('p_enrt_tbl(j).pl_id'||p_enrt_tbl(j).pl_id,111);
1463 hr_utility.set_location('p_enrt_tbl(i).pl_id'||p_enrt_tbl(i).pl_id,111);
1464 hr_utility.set_location('enrt_tbl(j).bnft_amt'||nvl(p_enrt_tbl(j).bnft_amt,0),111);
1465 hr_utility.set_location('l_tot_amt_no_interim'||l_tot_amt_no_interim,111);
1466 */
1467 --
1468 l_tot_pls := l_tot_pls + 1;
1469 l_tot_amt_no_interim:=l_tot_amt_no_interim+nvl(p_enrt_tbl(j).bnft_amt,0);
1470 if p_enrt_tbl(j).dpnt_cvd_by_othr_apls_flag = 'Y' then
1471 p_dpnt_cvd_by_othr_apls_flag := 'Y';
1472 end if;
1473 -- l_cnt := l_cnt + 1;
1474 -- l_pl_tbl(l_cnt) := p_enrt_tbl(j).pl_id;
1475 -- end if;
1476 end if;
1477 end if;
1478 end loop;
1479 l_max_amt := greatest(l_tot_amt, l_max_amt);
1480 l_max_pls := greatest(l_max_pls,l_tot_pls);
1481 l_max_amt_no_interim := greatest(l_max_amt_no_interim,l_tot_amt_no_interim); /* Bug 4309146 Removed typo */
1482 end loop;
1483 p_tot_amt := l_max_amt;
1484 p_tot_pls := l_max_pls;
1485 p_tot_amt_no_interim := l_max_amt_no_interim ;
1486 end get_pls_and_cvg;
1487 --
1488 -- ----------------------------------------------------------------------------
1489 -- |------<cache_enrt_info >------|
1490 -- ----------------------------------------------------------------------------
1491 --
1492 -- Description
1493 -- This procedure is used to cached all programs, plan types, plans for
1494 -- a specified person_id..
1495 --
1496 -- Pre Conditions
1497 -- None.
1498 --
1499 -- In Parameters
1500 -- person_id Person ID
1501 -- effective_date effective date
1502 --
1503 -- Post Success
1504 -- Processing continues
1505 --
1506 -- Post Failure
1507 -- Error handled by procedure
1508 --
1509 -- Access Status
1510 -- Internal table handler use only.
1511 --
1512 Procedure cache_enrt_info
1513 (p_effective_date in date
1514 ,p_business_group_id in number
1515 ,p_person_id in number
1516 ,p_pgm_id in number
1517 ,p_assignment_id in number
1518 ,p_include_erl in varchar2
1519 )
1520 is
1521 --
1522 l_dup_ptip_id_list ben_cache.IdType;
1523 l_dup_pl_typ_id_list ben_cache.IdType;
1524 l_dup_pl_id_list ben_cache.IdType;
1525 --
1526 l_plnrow ben_cobj_cache.g_pl_inst_row;
1527 l_oiplrow ben_cobj_cache.g_oipl_inst_row;
1528 --
1529 -- Cursor Declaration.
1530 --
1531 -- Previous enrollment means previous enrollment, so we check that the
1532 -- per_in_ler_id is different.
1533 -- BUG 3695079 fixes. This cursor never returns any rows as the current
1534 -- datet rack peice always has the new PIL. We need to get the DT Peice
1535 -- of effective_date-1 when looking along with PIL
1536 --
1537 -- p_effective_date is the lf_evt_ocrd_dt. Please keep this in mind
1538 -- before making further changes to this cursor.
1539 --
1540 cursor l_c_prev_pl(p_pl_id number) is
1541 select nvl(sum(pen.bnft_amt),0)
1542 from ben_prtt_enrt_rslt_f pen,
1543 ben_per_in_ler pil
1544 where pen.person_id = p_person_id
1545 and pen.prtt_enrt_rslt_stat_cd is null
1546 and pen.pl_id = p_pl_id
1547 and pil.person_id = p_person_id
1548 and nvl(pen.per_in_ler_id, pil.per_in_ler_id) <> pil.per_in_ler_id
1549 and pil.per_in_ler_stat_cd = 'STRTD'
1550 and pen.business_group_id = p_business_group_id
1551 and pen.enrt_cvg_strt_dt < pen.effective_end_date
1552 and (p_effective_date-1) between pen.enrt_cvg_strt_dt
1553 and pen.enrt_cvg_thru_dt
1554 and pen.effective_end_date = ( select max(pen1.effective_start_date - 1)
1555 from ben_prtt_enrt_rslt_f pen1
1556 where pen1.per_in_ler_id = pil.per_in_ler_id
1557 and pen1.prtt_enrt_rslt_stat_cd is NULL
1558 and pen1.pl_id = p_pl_id
1559 and pen1.enrt_cvg_thru_dt = hr_api.g_eot
1560 and pen1.person_id = p_person_id ) ;
1561 --
1562 --Bug 2715942 fixes
1563 --
1564 -- to get the ler_id of the STRTD per_in_ler
1565 --
1566 cursor c_ler(cv_pl_id number) is
1567 select pil.ler_id
1568 from ben_per_in_ler pil,
1569 ben_prtt_enrt_rslt_f pen
1570 where pil.person_id = p_person_id
1571 and pil.per_in_ler_stat_cd = 'STRTD'
1572 and pil.per_in_ler_id = pen.per_in_ler_id
1573 and pen.person_id = p_person_id
1574 and pen.pl_id = cv_pl_id
1575 and pen.prtt_enrt_rslt_stat_cd is null ;
1576 --
1577 cursor c_ler_rstrn(cv_pl_id number, cv_ler_id number) is
1578 select rstrn.pl_id,
1579 rstrn.mx_cvg_wcfn_amt,
1580 rstrn.mx_cvg_incr_alwd_amt,
1581 rstrn.mx_cvg_incr_wcf_alwd_amt,
1582 rstrn.mn_cvg_amt mn_cvg_rqd_amt,
1583 rstrn.mx_cvg_alwd_amt,
1584 rstrn.mx_cvg_rl,
1585 rstrn.mn_cvg_rl
1586 from ben_ler_bnft_rstrn_f rstrn
1587 where rstrn.ler_id = cv_ler_id
1588 and rstrn.pl_id = cv_pl_id
1589 and p_effective_date
1590 between rstrn.effective_start_date
1591 and rstrn.effective_end_date;
1592 --bug#3480144
1593
1594 cursor other_pgm_enrolled
1595 (c_effective_date date
1596 ,c_business_group_id number
1597 ,c_person_id number
1598 ,c_pgm_id number
1599 )
1600 is
1601 select pen.pgm_id,
1602 pen.ptip_id,
1603 pen.pl_typ_id,
1604 NVL(epe.plip_id, cpp.plip_id) plip_id,
1605 pen.pl_id,
1606 pen.oipl_id,
1607 pen.enrt_cvg_strt_dt,
1608 pen.enrt_cvg_thru_dt,
1609 pen.prtt_enrt_rslt_id,
1610 pen.RPLCS_SSPNDD_RSLT_ID,
1611 pen.SSPNDD_FLAG,
1612 'N' interim_flag,
1613 pen.person_id,
1614 0 Calc_interm,
1615 nvl(pen.bnft_amt,0) bnft_amt,
1616 pen.uom,
1617 epe.elig_per_elctbl_chc_id,
1618 epe.MUST_ENRL_ANTHR_PL_ID,
1619 'N' dpnt_cvd_by_othr_apls_flag,
1620 -9999999999999999999999999999999999999 opt_id
1621 from ben_prtt_enrt_rslt_f pen,
1622 ben_elig_per_elctbl_chc epe,
1623 ben_plip_f cpp,
1624 ben_oipl_f cop,
1625 ben_pl_f pln
1626 where pen.person_id = c_person_id
1627 and pen.prtt_enrt_rslt_stat_cd is null
1628 and pen.pgm_id <> c_pgm_id
1629 and pen.effective_end_date = hr_api.g_eot
1630 and pen.enrt_cvg_thru_dt = hr_api.g_eot
1631 and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id (+)
1632 and pen.per_in_ler_id = epe.per_in_ler_id (+)
1633 and pen.comp_lvl_cd not in ('PLANFC','PLANIMP')
1634 and exists (select null
1635 from ben_per_in_ler pil
1636 where pil.per_in_ler_id = epe.per_in_ler_id
1637 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'))
1638 and cpp.pgm_id = pen.pgm_id
1639 and cpp.pl_id = pen.pl_id
1640 and cpp.business_group_id = c_business_group_id
1641 and c_effective_date between cpp.effective_start_date and cpp.effective_end_date
1642 and pen.oipl_id = cop.oipl_id (+)
1643 and c_effective_date
1644 between cop.effective_start_date (+) and cop.effective_end_date (+)
1645 and pen.pl_id = pln.pl_id
1646 and c_effective_date
1647 between pln.effective_start_date and pln.effective_end_date
1648 order by 1,2,3,4,5,6,7;
1649 --
1650 l_ler_rstrn c_ler_rstrn%rowtype ;
1651 --
1652 -- Type declaration.
1653 --
1654 type interim_table is table of ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type
1655 index by binary_integer;
1656 --
1657 -- Local Variable declaration
1658 --
1659 l_prev_pgm_id ben_pgm_f.pgm_id%type := -99999;
1660 l_prev_ptip_id ben_ptip_f.ptip_id%type := -99999;
1661 l_prev_pl_typ_id ben_pl_typ_f.pl_typ_id%type := -99999;
1662 l_prev_pl_id ben_pl_f.pl_id%type := -99999;
1663 l_pl_typ_rec g_c_pl_typ%rowtype;
1664 l_interim_tbl interim_table;
1665 l_interim_cnt binary_integer := 0;
1666 i binary_integer;
1667 j binary_integer;
1668 l_interim_calc Boolean := FALSE;
1669 l_proc varchar2(72) := g_package||'cache_enrt_info';
1670 l_step integer;
1671 l_cnt integer := 0;
1672 l_frst_susp boolean := FALSE;
1673 l_enrt_tbl enrt_table;
1674 l_enrt_cnt binary_integer := 0;
1675 l_ler_id number ; --Bug 2715942
1676 l_enrt_tbl2 enrt_table;
1677 --
1678 begin
1679 --
1680 -- Store all comp. object into PL/SQL table g_enrt_tbl, and all the
1681 -- interim records prtt_enrt_rslt_id into l_interim_tbl PL/SQL table.
1682 --
1683 l_step := 5;
1684 hr_utility.set_location('Entering:'||l_proc,5);
1685 --
1686 -- Initialize globle variable.
1687 --
1688 l_interim_tbl.delete;
1689 g_enrt_tbl.delete;
1690 g_comp_obj_cnt := 0;
1691 g_pl_tbl.delete;
1692 g_pl_cnt := 0;
1693 g_pl_typ_tbl.delete;
1694 g_pl_typ_cnt := 0;
1695 g_ptip_tbl.delete;
1696 g_ptip_cnt := 0;
1697 g_tot_ee_lf_ins_amt := 0;
1698 g_tot_sps_lf_ins_amt := 0;
1699 g_tot_dpnt_lf_ins_amt := 0;
1700
1701 g_tot_ee_lf_ins_amt_no := 0;
1702 g_tot_sps_lf_ins_amt_no := 0;
1703 g_tot_dpnt_lf_ins_amt_no := 0;
1704
1705 g_mx_dpnt_pct_prtt_lf := 0;
1706 g_mx_sps_pct_prtt_lf := 0;
1707 --
1708 -- Open cursor.
1709 --
1710 -- The following loop stores all of the comp objects which are enrolled
1711 -- in as well as comp objects which are choices but are not enrolled in
1712 -- (I have just added the union to the g_c1 cursor for these choices)
1713 -- these rows are stored in the g_enrt_tbl to be used multiple times
1714 -- later on to summ up certain things. The approach is:
1715 -- 1) get comp objects store in g_enrt_tbl
1716 -- 2) initialize some special non-db fields
1717 -- 3) get unique pl, ptip, pl_typ records and put in cache tables
1718 -- 4) total/count up the numbers
1719 --
1720 open g_enrolled
1721 (c_effective_date => p_effective_date
1722 ,c_business_group_id => p_business_group_id
1723 ,c_person_id => p_person_id
1724 ,c_pgm_id => nvl(p_pgm_id, -999999)
1725 ,c_include_erl => p_include_erl
1726 );
1727 loop
1728 fetch g_enrolled into g_enrt_tbl(g_comp_obj_cnt+1);
1729 exit when g_enrolled%notfound;
1730 g_comp_obj_cnt := g_comp_obj_cnt+1;
1731 --
1732 if g_enrt_tbl(g_comp_obj_cnt).pl_id is not null then
1733 --
1734 ben_cobj_cache.get_pl_dets
1735 (p_business_group_id => p_business_group_id
1736 ,p_effective_date => p_effective_date
1737 ,p_pl_id => g_enrt_tbl(g_comp_obj_cnt).pl_id
1738 ,p_inst_row => l_plnrow
1739 );
1740 --
1741 g_enrt_tbl(g_comp_obj_cnt).dpnt_cvd_by_othr_apls_flag
1742 := l_plnrow.dpnt_cvd_by_othr_apls_flag;
1743 --
1744 end if;
1745 --
1746 if g_enrt_tbl(g_comp_obj_cnt).oipl_id is not null then
1747 --
1748 ben_cobj_cache.get_oipl_dets
1749 (p_business_group_id => p_business_group_id
1750 ,p_effective_date => p_effective_date
1751 ,p_oipl_id => g_enrt_tbl(g_comp_obj_cnt).oipl_id
1752 ,p_inst_row => l_oiplrow
1753 );
1754 --
1755 g_enrt_tbl(g_comp_obj_cnt).opt_id := l_oiplrow.opt_id;
1756 --
1757 else
1758 --
1759 g_enrt_tbl(g_comp_obj_cnt).opt_id := null;
1760 --
1761 end if;
1762 --
1763 l_cnt := l_cnt + 1;
1764 --
1765 -- If enrollment is suspended and allow interim coverage, then copy
1766 -- interim prtt_enrt_rslt_id into l_interim_tbl.
1767 --
1768 if (nvl(g_enrt_tbl(g_comp_obj_cnt).SSPNDD_FLAG,'X') = 'Y'
1769 and g_enrt_tbl(g_comp_obj_cnt).RPLCS_SSPNDD_RSLT_ID is not NULL)
1770 then
1771 --
1772 l_interim_cnt := l_interim_cnt + 1;
1773 l_interim_tbl(l_interim_cnt) :=
1774 g_enrt_tbl(g_comp_obj_cnt).RPLCS_SSPNDD_RSLT_ID;
1775 --
1776 end if;
1777 --
1778 end loop;
1779 close g_enrolled;
1780 --
1781 open g_epenotenrolled
1782 (c_effective_date => p_effective_date
1783 ,c_business_group_id => p_business_group_id
1784 ,c_person_id => p_person_id
1785 ,c_pgm_id => nvl(p_pgm_id, -999999)
1786 );
1787 loop
1788 fetch g_epenotenrolled into g_enrt_tbl(g_comp_obj_cnt+1);
1789 exit when g_epenotenrolled%notfound;
1790 g_comp_obj_cnt := g_comp_obj_cnt+1;
1791 --
1792 if g_enrt_tbl(g_comp_obj_cnt).oipl_id is not null then
1793 --
1794 ben_cobj_cache.get_oipl_dets
1795 (p_business_group_id => p_business_group_id
1796 ,p_effective_date => p_effective_date
1797 ,p_oipl_id => g_enrt_tbl(g_comp_obj_cnt).oipl_id
1798 ,p_inst_row => l_oiplrow
1799 );
1800 --
1801 g_enrt_tbl(g_comp_obj_cnt).opt_id := l_oiplrow.opt_id;
1802 --
1803 else
1804 --
1805 g_enrt_tbl(g_comp_obj_cnt).opt_id := null;
1806 --
1807 end if;
1808 --
1809 l_cnt := l_cnt + 1;
1810 --
1811 -- If enrollment is suspended and allow interim coverage, then copy
1812 -- interim prtt_enrt_rslt_id into l_interim_tbl.
1813 --
1814 if (nvl(g_enrt_tbl(g_comp_obj_cnt).SSPNDD_FLAG,'X') = 'Y' and
1815 g_enrt_tbl(g_comp_obj_cnt).RPLCS_SSPNDD_RSLT_ID is not NULL)
1816 then
1817 --
1818 l_interim_cnt := l_interim_cnt + 1;
1819 l_interim_tbl(l_interim_cnt) :=
1820 g_enrt_tbl(g_comp_obj_cnt).RPLCS_SSPNDD_RSLT_ID;
1821 --
1822 end if;
1823 --
1824 end loop;
1825 close g_epenotenrolled;
1826 --
1827 -- Check which record is interim record. If it is Interim, then set
1828 -- interim flag to 'Y'
1829 --
1830 l_step := 20;
1831 For i in 1..g_comp_obj_cnt loop
1832 for j in 1..l_interim_cnt loop
1833 if g_enrt_tbl(i).prtt_enrt_rslt_id = l_interim_tbl(j) then
1834 g_enrt_tbl(i).interim_flag := 'Y';
1835 exit;
1836 end if;
1837 end loop;
1838 End loop;
1839 --
1840 -- If program ID is not Null then get program information and store it in
1841 -- g_pgm_rec
1842 --
1843 l_step := 30;
1844 if (p_pgm_id is not NULL) then
1845 open g_c_pgm(p_effective_date,
1846 p_business_group_id,
1847 p_pgm_id);
1848 fetch g_c_pgm into g_pgm_rec;
1849 if (g_c_pgm%notfound) then
1850 close g_c_pgm;
1851 fnd_message.set_name('BEN','BEN_91468_PGM_MISSING');
1852 fnd_message.set_token('ID', to_char(p_pgm_id));
1853 fnd_message.raise_error;
1854 end if;
1855 close g_c_pgm;
1856 end if;
1857 --
1858 l_step := 40;
1859 --
1860 l_dup_ptip_id_list.delete;
1861 l_dup_pl_typ_id_list.delete;
1862 l_dup_pl_id_list.delete;
1863 --
1864 for i in 1..g_comp_obj_cnt loop
1865 --
1866 -- Get all of the unique PTIPs and cache them.
1867 -- If some limits are null then override with values from pl_typ.
1868 --
1869 l_interim_calc := FALSE;
1870 --
1871 if g_enrt_tbl(i).ptip_id is not null
1872 and not ben_cache.check_list_duplicate
1873 (p_list => l_dup_ptip_id_list
1874 ,p_id => g_enrt_tbl(i).ptip_id
1875 )
1876 then
1877 --
1878 open g_c_ptip(p_effective_date,
1879 p_business_group_id,
1880 g_enrt_tbl(i).ptip_id);
1881 fetch g_c_ptip into g_ptip_tbl(g_ptip_cnt+1);
1882 if (g_c_ptip%notfound) then
1883 close g_c_ptip;
1884 fnd_message.set_name('BEN','BEN_91462_PTIP_MISSING');
1885 fnd_message.set_token('ID', to_char(g_enrt_tbl(i).ptip_id) );
1886 fnd_message.raise_error;
1887 else
1888 g_ptip_cnt := g_ptip_cnt + 1;
1889 --
1890 -- if no_mx/mn_pl_typ_ov(e)rid_flag='N' and
1891 -- mx/mn_enrd_alwd/rqd_ovrid_num is null then can walk up
1892 -- hierarchy to get value from pl_typ
1893 --
1894 if ((g_ptip_tbl(g_ptip_cnt).no_mx_pl_typ_ovrid_flag='N' and
1895 g_ptip_tbl(g_ptip_cnt).mx_enrd_alwd_ovrid_num is null)
1896 or (g_ptip_tbl(g_ptip_cnt).no_mn_pl_typ_overid_flag='N' and
1897 g_ptip_tbl(g_ptip_cnt).mn_enrd_rqd_ovrid_num is null)
1898 ) then
1899 --
1900 -- Note: the pl_typ_rec is not stored as it is later,
1901 -- Just the override columns are used and stored over
1902 -- the null values at the ptip level.
1903 --
1904 open g_c_pl_typ(p_effective_date,
1905 p_business_group_id,
1906 g_enrt_tbl(i).pl_typ_id);
1907 fetch g_c_pl_typ into l_pl_typ_rec;
1908 if (g_c_pl_typ%notfound) then
1909 close g_c_pl_typ;
1910 fnd_message.set_name
1911 ('BEN','BEN_91469_PL_TYP_MISSING');
1912 fnd_message.set_token
1913 ('ID',to_char(g_enrt_tbl(i).pl_typ_id) );
1914 fnd_message.raise_error;
1915 end if;
1916 close g_c_pl_typ;
1917 if (g_ptip_tbl(g_ptip_cnt).no_mn_pl_typ_overid_flag='N' and
1918 g_ptip_tbl(g_ptip_cnt).mn_enrd_rqd_ovrid_num is null
1919 ) then
1920 g_ptip_tbl(g_ptip_cnt).MN_ENRD_RQD_OVRID_NUM :=
1921 l_pl_typ_rec.mn_enrl_rqd_num;
1922 end if;
1923 if (g_ptip_tbl(g_ptip_cnt).no_mx_pl_typ_ovrid_flag='N' and
1924 g_ptip_tbl(g_ptip_cnt).mx_enrd_alwd_ovrid_num is null
1925 ) then
1926 g_ptip_tbl(g_ptip_cnt).MX_ENRD_ALWD_OVRID_NUM :=
1927 l_pl_typ_rec.mx_enrl_alwd_num;
1928 end if;
1929 end if;
1930 if (g_ptip_tbl(g_ptip_cnt).sbj_to_sps_lf_ins_mx_flag = 'Y'
1931 or g_ptip_tbl(g_ptip_cnt).sbj_to_dpnt_lf_ins_mx_flag = 'Y'
1932 or g_ptip_tbl(g_ptip_cnt).use_to_sum_ee_lf_ins_flag = 'Y'
1933 ) then
1934 g_enrt_tbl(i).calc_interm := 1;
1935 end if;
1936 l_prev_ptip_id := g_enrt_tbl(i).ptip_id;
1937 end if;
1938 close g_c_ptip;
1939 elsif (g_enrt_tbl(i).ptip_id is not NULL) then
1940 if (g_ptip_tbl(g_ptip_cnt).sbj_to_sps_lf_ins_mx_flag = 'Y'
1941 or g_ptip_tbl(g_ptip_cnt).sbj_to_dpnt_lf_ins_mx_flag = 'Y'
1942 or g_ptip_tbl(g_ptip_cnt).use_to_sum_ee_lf_ins_flag = 'Y'
1943 ) then
1944 g_enrt_tbl(i).calc_interm := 1;
1945 end if;
1946 end if;
1947 --
1948 -- Get all unique plan types not in program.
1949 --
1950 l_step := 50;
1951 --
1952 if (not ben_cache.check_list_duplicate
1953 (p_list => l_dup_pl_typ_id_list
1954 ,p_id => g_enrt_tbl(i).pl_typ_id
1955 )
1956 and g_enrt_tbl(i).pl_typ_id is not NULL
1957 -- and g_enrt_tbl(i).ptip_id is NULL
1958 )
1959 then
1960 open g_c_pl_typ(p_effective_date,
1961 p_business_group_id,
1962 g_enrt_tbl(i).pl_typ_id);
1963 fetch g_c_pl_typ into g_pl_typ_tbl(g_pl_typ_cnt+1);
1964 if (g_c_pl_typ%notfound) then
1965 close g_c_pl_typ;
1966 fnd_message.set_name('BEN','BEN_91469_PL_TYP_MISSING');
1967 fnd_message.set_token('ID', to_char(g_enrt_tbl(i).pl_typ_id));
1968 fnd_message.raise_error;
1969 else
1970 g_pl_typ_cnt := g_pl_typ_cnt +1;
1971 l_prev_pl_typ_id := g_enrt_tbl(i).pl_typ_id;
1972 end if;
1973 close g_c_pl_typ;
1974 end if;
1975 --
1976 l_step := 60;
1977 --
1978 -- Get the unique plan information and cache it.
1979 -- If rules exist execute them and cache the results.
1980 --
1981 if not ben_cache.check_list_duplicate
1982 (p_list => l_dup_pl_id_list
1983 ,p_id => g_enrt_tbl(i).pl_id
1984 )
1985 then
1986 --
1987 open g_c_pl(p_effective_date
1988 ,p_business_group_id
1989 ,g_enrt_tbl(i).pl_id
1990 );
1991 fetch g_c_pl into g_pl_tbl(g_pl_cnt + 1);
1992 if (g_c_pl%notfound) then
1993 close g_c_pl;
1994 fnd_message.set_name('BEN','BEN_91460_PLAN_MISSING');
1995 fnd_message.set_token('ID', to_char(g_enrt_tbl(i).pl_id));
1996 fnd_message.raise_error;
1997 else
1998 g_pl_cnt := g_pl_cnt + 1 ;
1999 --
2000 -- Bug 2715942 code addition for ben_ler_bnft_rstrn_f
2001 open c_ler(g_enrt_tbl(i).pl_id);
2002 fetch c_ler into l_ler_id ;
2003 close c_ler ;
2004 --
2005 open c_ler_rstrn(g_enrt_tbl(i).pl_id,l_ler_id);
2006 fetch c_ler_rstrn into l_ler_rstrn ;
2007 if c_ler_rstrn%found then
2008 g_pl_tbl(g_pl_cnt).mx_cvg_wcfn_amt := l_ler_rstrn.mx_cvg_wcfn_amt ;
2009 g_pl_tbl(g_pl_cnt).mx_cvg_incr_alwd_amt := l_ler_rstrn.mx_cvg_incr_alwd_amt ;
2010 g_pl_tbl(g_pl_cnt).mx_cvg_incr_wcf_alwd_amt := l_ler_rstrn.mx_cvg_incr_wcf_alwd_amt ;
2011 g_pl_tbl(g_pl_cnt).mn_cvg_rqd_amt := l_ler_rstrn.mn_cvg_rqd_amt ;
2012 g_pl_tbl(g_pl_cnt).mx_cvg_alwd_amt := l_ler_rstrn.mx_cvg_alwd_amt ;
2013 g_pl_tbl(g_pl_cnt).mx_cvg_rl := l_ler_rstrn.mx_cvg_rl ;
2014 g_pl_tbl(g_pl_cnt).mn_cvg_rl := l_ler_rstrn.mn_cvg_rl ;
2015 end if ;
2016 close c_ler_rstrn ;
2017 -- End 2715942
2018 -- g_pl_cnt := g_pl_cnt + 1;
2019 --
2020 -- If mx_cvg_rl Rule exist, then override the mx cvg amt with
2021 -- the result of mx_cvg_rl rule.
2022 --
2023 if (g_pl_tbl(g_pl_cnt).mx_cvg_rl is not null) then
2024 g_pl_tbl(g_pl_cnt).mx_cvg_alwd_amt :=
2025 calc_mx_amt
2026 (p_mx_cvg_rl => g_pl_tbl(g_pl_cnt).mx_cvg_rl
2027 ,p_assignment_id => p_assignment_id
2028 ,p_effective_date => p_effective_date
2029 -- 3427367
2030 ,p_business_group_id => p_business_group_id
2031 ,p_pgm_id => g_enrt_tbl(i).pgm_id
2032 ,p_pl_id => g_pl_tbl(g_pl_cnt).pl_id
2033 ,p_pl_typ_id => g_pl_tbl(g_pl_cnt).pl_typ_id
2034 ,p_opt_id => g_enrt_tbl(i).opt_id
2035 ,p_ler_id => l_ler_id
2036 ,p_prtt_enrt_rslt_id => g_enrt_tbl(i).prtt_enrt_rslt_id -- 3427367
2037 ,p_person_id => p_person_id -- Bug 5331889
2038 );
2039 end if;
2040 g_pl_tbl(g_pl_cnt).ptip_id := g_enrt_tbl(i).ptip_id;
2041 --
2042 -- If mn_cvg_rl Rule exist, then override the mn cvg amt with
2043 -- result of mn_cvg_rl rule.
2044 --
2045 if (g_pl_tbl(g_pl_cnt).mn_cvg_rl is not null) then
2046 g_pl_tbl(g_pl_cnt).mn_cvg_rqd_amt :=
2047 calc_mn_amt
2048 (p_mn_cvg_rl => g_pl_tbl(g_pl_cnt).mn_cvg_rl
2049 ,p_assignment_id => p_assignment_id
2050 ,p_effective_date => p_effective_date
2051 -- 3427367
2052 ,p_business_group_id => p_business_group_id
2053 ,p_pgm_id => g_enrt_tbl(i).pgm_id
2054 ,p_pl_id => g_pl_tbl(g_pl_cnt).pl_id
2055 ,p_pl_typ_id => g_pl_tbl(g_pl_cnt).pl_typ_id
2056 ,p_opt_id => g_enrt_tbl(i).opt_id
2057 ,p_ler_id => l_ler_id
2058 ,p_prtt_enrt_rslt_id => g_enrt_tbl(i).prtt_enrt_rslt_id --3427367
2059 ,p_person_id => p_person_id -- Bug 5331889
2060 );
2061
2062 end if;
2063 l_prev_pl_id := g_enrt_tbl(i).pl_id;
2064 --
2065 if g_pl_tbl(g_pl_cnt).mx_cvg_incr_alwd_amt is not null then
2066 --
2067 -- Get prev coverage amt on plan level. Current, the process
2068 -- assume there is no Interim and suspended records for
2069 -- previous period.
2070 --
2071 open l_c_prev_pl(l_prev_pl_id);
2072 fetch l_c_prev_pl into g_pl_tbl(g_pl_cnt).prev_cvg_amt;
2073 if (l_c_prev_pl%notfound) then
2074 close l_c_prev_pl;
2075 g_pl_tbl(g_pl_cnt).prev_cvg_amt := 0;
2076 end if;
2077 close l_c_prev_pl;
2078 --
2079 end if;
2080 --
2081 end if;
2082 --
2083 close g_c_pl;
2084 --
2085 end if;
2086 end loop;
2087 --
2088 l_step := 70;
2089 --
2090 -- Create a table of enrollments for each plan then call get_opts_and_cvg
2091 -- to compute max and min enrollments.
2092 -- Ignore rows where the prtt_enrt_rslt_id is null since they are not
2093 -- enrolled. Just want to pick up the fact that there was 0 enrollments
2094 --
2095 for i in 1..g_pl_cnt loop
2096 l_enrt_cnt := 0;
2097 l_enrt_tbl.delete;
2098 --
2099 for j in 1..g_comp_obj_cnt loop
2100 if (g_enrt_tbl(j).pl_id = g_pl_tbl(i).pl_id and
2101 g_enrt_tbl(j).prtt_enrt_rslt_id is not null) then
2102 l_enrt_cnt := l_enrt_cnt + 1;
2103 l_enrt_tbl(l_enrt_cnt) := g_enrt_tbl(j);
2104 end if;
2105 end loop;
2106 If (l_enrt_cnt > 0) then
2107 hr_utility.set_location('Before g_pl_tbl(i).tot_cvg_amt '||g_pl_tbl(i).tot_cvg_amt,1234);
2108 get_opts_and_cvg
2109 (p_enrt_tbl => l_enrt_tbl
2110 ,p_enrt_cnt => l_enrt_cnt
2111 ,p_tot_amt => g_pl_tbl(i).tot_cvg_amt
2112 ,p_tot_opts => g_pl_tbl(i).tot_opt_enrld
2113 );
2114 hr_utility.set_location('After g_pl_tbl(i).tot_cvg_amt '||g_pl_tbl(i).tot_cvg_amt,1234);
2115 end if;
2116 --
2117 end loop;
2118 --
2119 l_step := 80;
2120 --
2121 --
2122 -- Create a table of enrollments for each ptip then call get_pls_and_cvg
2123 -- to compute max and min enrollments.
2124 -- Ignore rows where the prtt_enrt_rslt_id is null since they are not
2125 -- enrolled. Just want to pick up the fact that there was 0 enrollments
2126 --
2127 for i in 1..g_ptip_cnt loop
2128 l_enrt_cnt := 0;
2129 l_enrt_tbl.delete;
2130 for j in 1..g_comp_obj_cnt loop
2131 -- Bug No. 6454197 Added code to enforce limitation for enrollment at plantype
2132 if (g_enrt_tbl(j).ptip_id = g_ptip_tbl(i).ptip_id and
2133 g_enrt_tbl(j).prtt_enrt_rslt_id is not null
2134 and g_enrt_tbl(j).enrt_cvg_thru_dt = hr_api.g_eot) then
2135 l_enrt_cnt := l_enrt_cnt + 1;
2136 l_enrt_tbl(l_enrt_cnt) := g_enrt_tbl(j);
2137 end if;
2138 end loop;
2139 If (l_enrt_cnt > 0) then
2140 get_pls_and_cvg
2141 (p_enrt_tbl => l_enrt_tbl
2142 ,p_enrt_cnt => l_enrt_cnt
2143 ,p_tot_amt => g_ptip_tbl(i).tot_cvg_amt
2144 ,p_tot_pls => g_ptip_tbl(i).tot_pl_enrld
2145 ,p_dpnt_cvd_by_othr_apls_flag => g_ptip_tbl(i).dpnt_cvd_by_othr_apls_flag
2146 ,p_tot_amt_no_interim => g_ptip_tbl(i).tot_cvg_amt_no_interim
2147 );
2148 hr_utility.set_location('After get_pls_and_cvg 1'||g_ptip_tbl(i).tot_cvg_amt,111);
2149 hr_utility.set_location('After tot_cvg_no_interim'||g_ptip_tbl(i).tot_cvg_amt_no_interim,111);
2150
2151 End if;
2152 end loop;
2153 --
2154 l_step := 90;
2155 --
2156 --
2157 -- Create a table of enrollments for each pl typ then call get_pls_and_cvg
2158 -- to compute max and min enrollments.
2159 -- Ignore rows where the prtt_enrt_rslt_id is null since they are not
2160 -- enrolled. Just want to pick up the fact that there was 0 enrollments
2161 --
2162 -- bug#3480144
2163 if p_pgm_id is not null then
2164 open other_pgm_enrolled (c_effective_date => p_effective_date
2165 ,c_business_group_id => p_business_group_id
2166 ,c_person_id => p_person_id
2167 ,c_pgm_id => p_pgm_id
2168 );
2169 l_enrt_cnt := 0;
2170 loop
2171 fetch other_pgm_enrolled into l_enrt_tbl2(l_enrt_cnt+1);
2172 if other_pgm_enrolled%notfound then
2173 exit;
2174 end if;
2175 l_enrt_cnt := l_enrt_cnt+1;
2176 end loop;
2177 close other_pgm_enrolled;
2178 end if;
2179 for i in 1..g_pl_typ_cnt loop
2180 l_enrt_cnt := 0;
2181 l_enrt_tbl.delete;
2182 for j in 1..g_comp_obj_cnt loop
2183 if (g_enrt_tbl(j).pl_typ_id = g_pl_typ_tbl(i).pl_typ_id
2184 /*and g_enrt_tbl(j).ptip_id is null */ and
2185 g_enrt_tbl(j).prtt_enrt_rslt_id is not null) then
2186 l_enrt_cnt := l_enrt_cnt + 1;
2187 l_enrt_tbl(l_enrt_cnt) := g_enrt_tbl(j);
2188 end if;
2189 end loop;
2190 -- bug#3480144
2191 if l_enrt_tbl2.count > 0 then
2192 for j in 1..l_enrt_tbl2.count loop
2193 if (l_enrt_tbl2(j).pl_typ_id = g_pl_typ_tbl(i).pl_typ_id
2194 and l_enrt_tbl2(j).prtt_enrt_rslt_id is not null) then
2195 l_enrt_cnt := l_enrt_cnt + 1;
2196 l_enrt_tbl(l_enrt_cnt) := l_enrt_tbl2(j);
2197 end if;
2198 end loop;
2199 end if;
2200 --
2201 If (l_enrt_cnt > 0) then
2202 get_pls_and_cvg
2203 (p_enrt_tbl => l_enrt_tbl
2204 ,p_enrt_cnt => l_enrt_cnt
2205 ,p_tot_amt => g_pl_typ_tbl(i).tot_cvg_amt
2206 ,p_tot_pls => g_pl_typ_tbl(i).tot_pl_enrld
2207 ,p_dpnt_cvd_by_othr_apls_flag => g_pl_typ_tbl(i).dpnt_cvd_by_othr_apls_flag
2208 ,p_tot_amt_no_interim => g_pl_typ_tbl(i).tot_cvg_amt_no_interim
2209 );
2210 hr_utility.set_location('AfterPLTYP get_pls_and_cvg 2'||g_pl_typ_tbl(i).tot_cvg_amt,111);
2211 hr_utility.set_location('After tot_cvg_no_interim'||g_pl_typ_tbl(i).tot_cvg_amt_no_interim,111);
2212 End if;
2213 end loop;
2214 --
2215 l_step := 100;
2216 --
2217 hr_utility.set_location('Leaving:'||l_proc,500);
2218 Exception
2219 when others then
2220 hr_utility.set_location('Fail in '|| l_proc|| ' at step ' ||
2221 to_char(l_step), 999);
2222 if g_c_pgm%isopen then
2223 close g_c_pgm;
2224 end if;
2225 if g_c_ptip%isopen then
2226 close g_c_ptip;
2227 end if;
2228 if g_c_pl_typ%isopen then
2229 close g_c_pl_typ;
2230 end if;
2231 if g_c_pl%isopen then
2232 close g_c_pl;
2233 end if;
2234 raise;
2235 end;
2236 --
2237 -- ----------------------------------------------------------------------------
2238 -- |------<get_plan_name >------|
2239 -- ----------------------------------------------------------------------------
2240 --
2241 function get_plan_name
2242 (p_pl_id in number
2243 ,p_effective_date in date
2244 ,p_business_group_id in number
2245 )
2246 return varchar2
2247 is
2248 --
2249 l_pl_rec ben_cobj_cache.g_pl_inst_row;
2250 /*
2251 l_pl_rec ben_pl_f%rowtype;
2252 */
2253 --
2254 begin
2255 --
2256 ben_cobj_cache.get_pl_dets
2257 (p_business_group_id => p_business_group_id
2258 ,p_effective_date => p_effective_date
2259 ,p_pl_id => p_pl_id
2260 ,p_inst_row => l_pl_rec
2261 );
2262 --
2263 /*
2264 ben_comp_object.get_object(p_pl_id => p_pl_id,
2265 p_rec => l_pl_rec);
2266 */
2267 return l_pl_rec.name;
2268 --
2269 end;
2270 --
2271 -- ----------------------------------------------------------------------------
2272 -- |------<p_eva_rule_output >------|
2273 -- ----------------------------------------------------------------------------
2274 --
2275 Procedure p_eva_rule_output(p_rule_returns ff_exec.outputs_t,
2276 p_proc varchar2,
2277 p_rule_id number) is
2278 l_successful varchar2(30);
2279 l_error_message varchar2(240);
2280 begin
2281 --
2282 -- Load the rule into the required variables
2283 --
2284 if p_rule_returns.exists(1) then
2285 --
2286 for l_count in p_rule_returns.first..p_rule_returns.last loop
2287 --
2288 begin
2289 --
2290 if p_rule_returns(l_count).name = 'SUCCESSFUL' then
2291 --
2292 l_successful := p_rule_returns(l_count).value;
2293 --
2294 elsif p_rule_returns(l_count).name = 'ERROR_MESSAGE' then
2295 --
2296 l_error_message := p_rule_returns(l_count).value;
2297 --
2298 else
2299 --
2300 -- Account for cases where formula returns an unknown
2301 -- variable name
2302 --
2303 fnd_message.set_name('BEN', 'BEN_92310_FORMULA_RET_PARAM');
2304 fnd_message.set_token('PROC', p_proc);
2305 fnd_message.set_token('FORMULA', p_rule_id);
2306 fnd_message.set_token('PARAMETER', p_rule_returns(l_count).name);
2307 fnd_message.raise_error;
2308 --
2309 end if;
2310 --
2311 -- Code for type casting errors from formula return variables
2312 --
2313 exception
2314 --
2315 when others then
2316 --
2317 fnd_message.set_name('BEN','BEN_92311_FORMULA_VAL_PARAM');
2318 fnd_message.set_token('PROC',p_proc);
2319 fnd_message.set_token('FORMULA',p_rule_id);
2320 fnd_message.set_token('PARAMETER',p_rule_returns(l_count).name);
2321 fnd_message.raise_error;
2322 --
2323 end;
2324 --
2325 end loop;
2326 --
2327 if l_successful <> 'Y' then
2328 --
2329 fnd_message.set_name('BEN','BEN_92187_POST_ELCN_NOT_PASS');
2330 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
2331 fnd_message.raise_error;
2332 --
2333 end if;
2334 --
2335 end if;
2336 --
2337 end p_eva_rule_output;
2338 --
2339 -- ----------------------------------------------------------------------------
2340 -- |------<chk_post_elcn_rl >------|
2341 -- ----------------------------------------------------------------------------
2342 --
2343 Procedure Chk_post_elcn_rl
2344 (p_pgm_id number
2345 ,p_pl_id number
2346 ,p_pl_typ_id number
2347 ,p_opt_id number
2348 ,p_person_id number
2349 ,p_business_group_id number
2350 ,p_effective_date date
2351 ,p_pl_post_edit_rl number
2352 ,p_plip_post_edit_rl number
2353 ,p_ptip_post_edit_rl number
2354 ,p_oipl_post_edit_rl number
2355 ) is
2356
2357 Cursor c_state is
2358 select loc.region_2, asg.assignment_id, asg.organization_id
2359 from hr_locations_all loc,per_all_assignments_f asg
2360 where loc.location_id(+) = asg.location_id
2361 and asg.person_id = p_person_id
2362 and asg.assignment_type <> 'C'
2363 and asg.primary_flag = 'Y'
2364 and p_effective_date between asg.effective_start_date and asg.effective_end_date
2365 and asg.business_group_id = p_business_group_id
2366 order by DECODE(asg.assignment_type,'E',1,'B',2,3); -- 5303252 Order by asg_type
2367
2368 l_state c_state%rowtype;
2369 l_rule_returns ff_exec.outputs_t;
2370 l_proc varchar2(80) := g_package || '.chk_post_elcn_rl';
2371 l_jurisdiction_code varchar2(30);
2372
2373 --
2374 Begin
2375 --
2376 hr_utility.set_location('Entering - '||l_proc, 5);
2377 --
2378 --
2379 if p_oipl_post_edit_rl is not null or
2380 p_pl_post_edit_rl is not null or
2381 p_ptip_post_edit_rl is not null or
2382 p_plip_post_edit_rl is not null then
2383 --
2384 if p_person_id is not null then
2385
2386 open c_state;
2387
2388 fetch c_state into l_state;
2389
2390 close c_state;
2391
2392 -- if l_state.region_2 is not null then
2393 -- l_jurisdiction_code :=
2394 -- pay_mag_utils.lookup_jurisdiction_code
2395 -- (p_state => l_state.region_2);
2396 -- end if;
2397
2398 end if;
2399 --
2400 end if;
2401
2402 hr_utility.set_location('l_state.assignment_id '|| l_state.assignment_id , 5);
2403
2404 if p_oipl_post_edit_rl is not null then
2405 --
2406 l_rule_returns :=
2407 benutils.formula
2408 (p_formula_id => p_oipl_post_edit_rl,
2409 p_assignment_id => l_state.assignment_id,
2410 p_business_group_id => p_business_group_id,
2411 p_organization_id => l_state.organization_id,
2412 p_pgm_id => p_pgm_id,
2413 p_pl_id => p_pl_id,
2414 p_pl_typ_id => p_pl_typ_id,
2415 p_opt_id => p_opt_id,
2416 p_jurisdiction_code => l_jurisdiction_code,
2417 p_effective_date => p_effective_date,
2418 p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
2419 p_param1_value => to_char(p_person_id)
2420 );
2421 --
2422 p_eva_rule_output(p_rule_returns => l_rule_returns,
2423 p_proc => l_proc,
2424 p_rule_id => p_oipl_post_edit_rl);
2425 end if;
2426 --
2427 if p_pl_post_edit_rl is not null then
2428 --
2429 l_rule_returns :=
2430 benutils.formula
2431 (p_formula_id => p_pl_post_edit_rl,
2432 p_assignment_id => l_state.assignment_id,
2433 p_business_group_id => p_business_group_id,
2434 p_organization_id => l_state.organization_id,
2435 p_pgm_id => p_pgm_id,
2436 p_pl_id => p_pl_id,
2437 p_pl_typ_id => p_pl_typ_id,
2438 p_opt_id => p_opt_id,
2439 p_jurisdiction_code => l_jurisdiction_code,
2440 p_effective_date => p_effective_date,
2441 p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
2442 p_param1_value => to_char(p_person_id));
2443 --
2444 p_eva_rule_output(p_rule_returns => l_rule_returns,
2445 p_proc => l_proc,
2446 p_rule_id => p_pl_post_edit_rl);
2447 end if;
2448 --
2449 if p_plip_post_edit_rl is not null then
2450 --
2451 l_rule_returns :=
2452 benutils.formula
2453 (p_formula_id => p_plip_post_edit_rl,
2454 p_assignment_id => l_state.assignment_id,
2455 p_business_group_id => p_business_group_id,
2456 p_organization_id => l_state.organization_id,
2457 p_pgm_id => p_pgm_id,
2458 p_pl_id => p_pl_id,
2459 p_pl_typ_id => p_pl_typ_id,
2460 p_opt_id => p_opt_id,
2461 p_jurisdiction_code => l_jurisdiction_code,
2462 p_effective_date => p_effective_date,
2463 p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
2464 p_param1_value => to_char(p_person_id));
2465 --
2466 p_eva_rule_output(p_rule_returns => l_rule_returns,
2467 p_proc => l_proc,
2468 p_rule_id => p_plip_post_edit_rl);
2469 end if;
2470 --
2471 if p_ptip_post_edit_rl is not null then
2472 --
2473 l_rule_returns :=
2474 benutils.formula
2475 (p_formula_id => p_ptip_post_edit_rl,
2476 p_assignment_id => l_state.assignment_id,
2477 p_business_group_id => p_business_group_id,
2478 p_organization_id => l_state.organization_id,
2479 p_pgm_id => p_pgm_id,
2480 p_pl_id => p_pl_id,
2481 p_pl_typ_id => p_pl_typ_id,
2482 p_opt_id => p_opt_id,
2483 p_jurisdiction_code => l_jurisdiction_code,
2484 p_effective_date => p_effective_date,
2485 p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
2486 p_param1_value => to_char(p_person_id));
2487 --
2488 p_eva_rule_output(p_rule_returns => l_rule_returns,
2489 p_proc => l_proc,
2490 p_rule_id => p_ptip_post_edit_rl);
2491 end if;
2492 --
2493 hr_utility.set_location('Leaving - '||l_proc, 10);
2494 --
2495 End Chk_post_elcn_rl;
2496 --
2497 -- ----------------------------------------------------------------------------
2498 -- |------<check_mandatory_comp_object >------|
2499 -- ----------------------------------------------------------------------------
2500 --
2501 Procedure Chk_mndtry_comp_obj
2502 (p_person_id Number
2503 ,p_pgm_id Number
2504 ,p_business_group_id Number
2505 ,p_effective_date Date
2506 ) is
2507 Cursor c1 is
2508 select null
2509 from ben_elig_per_elctbl_chc
2510 where business_group_id = p_business_group_id
2511 and nvl(pgm_id,hr_api.g_number) = nvl(p_pgm_id, hr_api.g_number)
2512 and MNDTRY_FLAG = 'Y'
2513 and pl_id in -- bug 1207161 removed 'not'
2514 (Select distinct pen.pl_id
2515 From ben_prtt_enrt_rslt_f pen
2516 Where nvl(pen.enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
2517 and p_effective_date between
2518 pen.effective_start_date and pen.effective_end_date
2519 and pen.effective_end_date = hr_api.g_eot
2520 and pen.person_id = p_person_id
2521 and nvl(pen.pgm_id,-1) = nvl(p_pgm_id,-1)
2522 and pen.prtt_enrt_rslt_stat_cd is null)
2523 and oipl_id not in -- bug 1207161 added this.
2524 (Select distinct nvl(pen.oipl_id, -1)
2525 From ben_prtt_enrt_rslt_f pen
2526 Where nvl(pen.enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
2527 and p_effective_date between
2528 pen.effective_start_date and pen.effective_end_date
2529 and pen.effective_end_date = hr_api.g_eot
2530 and pen.person_id = p_person_id
2531 and nvl(pen.pgm_id,-1) = nvl(p_pgm_id,-1)
2532 and pen.prtt_enrt_rslt_stat_cd is null)
2533 and per_in_ler_id in (select per_in_ler_id
2534 From ben_per_in_ler
2535 Where person_id = p_person_id
2536 and per_in_ler_stat_cd = 'STRTD'
2537 and business_group_id = p_business_group_id
2538 )
2539 ;
2540 l_dump number(15);
2541 l_proc varchar2(80) := g_package || '.chk_mndtry_comp_obj';
2542 Begin
2543 hr_utility.set_location('Entering - '||l_proc, 5);
2544
2545 -- Check that mandatory options are enrolled in.
2546 -- These are options that you MUST be enrolled in, IF you are
2547 -- enrolled in the plan at all.
2548 open c1;
2549 fetch c1 into l_dump;
2550 If c1%found then
2551 close c1;
2552 fnd_message.set_name('BEN', 'BEN_91962_MNDTRY_OBJ_NOT_ENRLD');
2553 fnd_message.raise_error;
2554 End if;
2555 close c1;
2556 hr_utility.set_location('Leaving - '||l_proc, 10);
2557 End;
2558 --
2559 --
2560 -- ----------------------------------------------------------------------------
2561 -- |------<Manage_person_type_usage >------|
2562 -- ----------------------------------------------------------------------------
2563 --
2564 Procedure manage_per_type_usages
2565 (p_person_id number
2566 ,p_business_group_id number
2567 ,p_effective_date date
2568 ) is
2569 -- RCHASE - rewritten for wwBug 1433274
2570 cursor c_pen is
2571 select distinct Enrt_Cvg_Strt_Dt CSD
2572 ,nvl(Enrt_Cvg_thru_Dt,hr_api.g_eot) CED
2573 ,'N' Match
2574 from ben_prtt_enrt_rslt_f
2575 where person_id = p_person_id
2576 and effective_end_date >= enrt_cvg_strt_dt
2577 and enrt_cvg_strt_dt <= nvl(Enrt_Cvg_thru_Dt,hr_api.g_eot) --bug 5257798
2578 and prtt_enrt_rslt_stat_cd is null
2579 and sspndd_flag = 'N'
2580 and effective_end_date = hr_api.g_eot -- Bug 2130842 added this clause
2581 order by 1 asc, 2 desc;
2582 --
2583 l_person_type_id per_person_type_usages_f.person_type_id%type:=null;
2584 --
2585 cursor c_ptu is
2586 select a.person_type_usage_id
2587 ,a.person_type_id
2588 ,a.effective_start_date ESD
2589 ,a.effective_end_date EED
2590 ,a.object_version_number OVN
2591 ,'N' Match
2592 from per_person_type_usages_f a
2593 where a.Person_id = p_person_id
2594 and a.person_type_id = l_person_type_id
2595 order by a.effective_start_date asc, a.effective_end_date desc;
2596 --
2597 cursor c_pt is
2598 select person_type_id
2599 from per_person_types
2600 where system_person_type = 'PRTN'
2601 and business_group_id = p_business_group_id;
2602 --
2603 type pen_record is table of c_pen%rowtype index by binary_integer;
2604 type ptu_record is table of c_ptu%rowtype index by binary_integer;
2605 l_pen pen_record;
2606 l_ptu ptu_record;
2607 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%type;
2608 l_object_version_number per_person_type_usages_f.object_version_number%type;
2609 l_esd per_person_type_usages_f.effective_start_date%type;
2610 l_eed per_person_type_usages_f.effective_end_date%type;
2611 l_next_ptu number:=1;
2612 l_next_pen number:=1;
2613 --
2614 e_no_pt exception;
2615 e_no_pen exception;
2616 l_proc varchar2(80) := g_package||'.manage_per_type_usages';
2617 begin
2618 hr_utility.set_location('Entering '||l_proc,10);
2619 -- Get participant person type id
2620 for r_pt in c_pt loop
2621 l_person_type_id := r_pt.person_type_id;
2622 end loop;
2623 if l_person_type_id is null then
2624 fnd_message.set_name('BEN','BEN_92468_PERSON_TYP_NOT_FOUND');
2625 fnd_message.raise_error;
2626 end if;
2627 -- Fetch enrollment results and create a distinct number or inclusive date records
2628 for r_pen in c_pen loop
2629 if l_pen.count > 0 then
2630 if r_pen.csd-1 >= l_pen(l_pen.count).ced then
2631 l_pen(l_pen.count+1) := r_pen;
2632 else
2633 if r_pen.ced > l_pen(l_pen.count).ced then
2634 l_pen(l_pen.count).ced := r_pen.ced;
2635 end if;
2636 end if;
2637 else
2638 l_pen(l_pen.count+1) := r_pen;
2639 end if;
2640 end loop;
2641
2642 -- Fetch all participant person type usage records
2643 for r_ptu in c_ptu loop
2644 l_ptu(l_ptu.count+1) := r_ptu;
2645 end loop;
2646
2647 hr_utility.set_location('counts prn : '||l_pen.count || ' PTU :' || l_ptu.count,90);
2648 if l_pen.count = 0 then
2649 -- if there is no enrollment result,
2650 -- 2899702 check for per type usage if both 0
2651 -- no need to process person type usage records
2652 -- or delete all the person_type usage of prtn
2653 -- when all enrollment is backed out
2654 if l_ptu.count > 0 then
2655 for j in 1..l_ptu.count loop
2656 hr_per_type_usage_internal.delete_person_type_usage
2657 (p_validate => FALSE
2658 ,p_person_type_usage_id => l_ptu(j).person_type_usage_id
2659 ,p_effective_date => l_ptu(j).ESD
2660 ,p_datetrack_mode => hr_api.g_zap
2661 ,p_object_version_number => l_ptu(j).OVN
2662 ,p_effective_start_date => l_esd
2663 ,p_effective_end_date => l_eed);
2664 end loop; -- zap person type usage loop
2665 end if ;
2666 hr_utility.set_location('Leaving betwen '||l_proc,90);
2667
2668 return;
2669 end if;
2670
2671 if l_ptu.count > 0 then
2672 -- Compare date ranges, find exact matches
2673 if l_pen(1).csd <> l_ptu(1).esd and l_pen(1).ced <> l_ptu(1).eed then
2674 null; -- first records do not match, all will be zapped and rebuilt
2675 else
2676 for i in 1..l_pen.count loop
2677 for j in l_next_ptu..l_ptu.count loop
2678 if l_pen(i).csd=l_ptu(j).esd and l_pen(i).ced=l_ptu(j).eed then
2679 l_pen(i).match := 'Y';
2680 l_ptu(j).match := 'Y';
2681 l_next_ptu := j+1;
2682 exit;
2683 end if;
2684 end loop; -- person type usage loop
2685 if l_pen(i).match <> 'Y' then
2686 exit; -- must zap remaining person type records and rebuild
2687 end if;
2688 end loop; -- enrollment loop
2689 end if;
2690 -- Remove person type usage records that did not match
2691 for j in 1..l_ptu.count loop
2692 if l_ptu(j).match = 'N' then
2693 hr_per_type_usage_internal.delete_person_type_usage
2694 (p_validate => FALSE
2695 ,p_person_type_usage_id => l_ptu(j).person_type_usage_id
2696 ,p_effective_date => l_ptu(j).ESD
2697 ,p_datetrack_mode => hr_api.g_zap
2698 ,p_object_version_number => l_ptu(j).OVN
2699 ,p_effective_start_date => l_esd
2700 ,p_effective_end_date => l_eed);
2701 end if;
2702 end loop; -- zap person type usage loop
2703 -- Create person type usage records for unmatched enrollment records
2704 end if;
2705 for i in 1..l_pen.count loop
2706 if l_pen(i).match = 'N' then
2707 hr_per_type_usage_internal.create_person_type_usage
2708 (p_validate => FALSE
2709 ,p_person_id => p_person_id
2710 ,p_person_type_id => l_person_type_id
2711 ,p_person_type_usage_id => l_person_type_usage_id
2712 ,p_effective_date => l_pen(i).csd
2713 ,p_object_version_number => l_object_version_number
2714 ,p_effective_start_date => l_esd
2715 ,p_effective_end_date => l_eed);
2716 -- if the enrollment record does not go to end of time
2717 -- make sure to end date the person type usage row
2718 if l_pen(i).ced <> hr_api.g_eot then
2719 hr_per_type_usage_internal.delete_person_type_usage
2720 (p_validate => FALSE
2721 ,p_person_type_usage_id => l_person_type_usage_id
2722 ,p_effective_date => l_pen(i).ced
2723 ,p_datetrack_mode => hr_api.g_delete
2724 ,p_object_version_number => l_object_version_number
2725 ,p_effective_start_date => l_esd
2726 ,p_effective_end_date => l_eed);
2727 end if;
2728 end if;
2729 end loop; -- create person type usage loop
2730 hr_utility.set_location('Leaving '||l_proc,90);
2731 exception
2732 when e_no_pt then
2733 hr_utility.set_location('Leaving '||l_proc||' No person type found.',100);
2734 raise;
2735 when e_no_pen then
2736 hr_utility.set_location('Leaving '||l_proc||' No enrollments found.',110);
2737 raise;
2738 when others then
2739 hr_utility.set_location('Leaving '||l_proc||' When others fired.',120);
2740 raise;
2741 End;
2742 --
2743 -- ----------------------------------------------------------------------------
2744 -- << Susp_Svg_pl_opts >> |
2745 -- ----------------------------------------------------------------------------
2746 -- Description
2747 -- This procedure will unsuspend the rest of options for a plan that has
2748 -- Enrt_pl_opt_flag set on
2749 --
2750 -- ============================================================================
2751 --
2752 Procedure susp_svg_pl_opts
2753 (p_person_id in number
2754 ,p_effective_date in date
2755 ,p_business_group_id in number
2756 ,p_pgm_id in number
2757 ) is
2758 --
2759 Cursor c1 is
2760 select distinct b.per_in_ler_id, b.ler_id, b.pl_id
2761 From ben_prtt_enrt_rslt_f b
2762 ,ben_pl_f c
2763 Where b.person_id = p_person_id
2764 And nvl(b.pgm_id,-1) = nvl(p_pgm_id,-1)
2765 And p_effective_date between
2766 b.effective_start_date and b.effective_end_date
2767 And b.business_group_id=p_business_group_id
2768 And b.enrt_cvg_strt_dt < nvl(b.effective_end_date,hr_api.g_eot)
2769 And b.oipl_id is NULL
2770 and b.prtt_enrt_rslt_stat_cd is null
2771 And b.sspndd_flag = 'Y'
2772 And b.pl_id = c.pl_id
2773 And p_effective_date between
2774 c.effective_start_date and c.effective_end_date
2775 And c.enrt_pl_opt_flag = 'Y'
2776 ;
2777 --
2778 Cursor c2 (c_pl_id number) is
2779 Select a.prtt_enrt_rslt_id
2780 ,a.effective_start_date
2781 ,a.effective_end_date
2782 ,a.object_version_number
2783 From ben_prtt_enrt_rslt_f a
2784 Where a.person_id = p_person_id
2785 And nvl(a.pgm_id,-1) = nvl(p_pgm_id,-1)
2786 And a.business_group_id=p_business_group_id
2787 And p_effective_date between
2788 a.effective_start_date and a.effective_end_date
2789 And a.enrt_cvg_strt_dt < nvl(a.effective_end_date, hr_api.g_eot)
2790 And a.oipl_id is not null
2791 and a.prtt_enrt_rslt_stat_cd is null
2792 And a.sspndd_flag = 'N'
2793 And a.pl_id = c_pl_id
2794 ;
2795 --
2796 -- Variables declaration
2797 --
2798 l_proc varchar2(72) := g_package||'susp_svg_pl_opts';
2799 l_step integer;
2800 l_datetrack_mode varchar2(30);
2801 --
2802 Begin
2803 hr_utility.set_location('Entering' || l_proc,5);
2804 --
2805 -- C1 is cursor is pull out all suspended plan with in program that user
2806 -- specified and cursor c2 is cursor to pull out all options are not suspended
2807 -- within the plan.
2808 --
2809 l_step := 100;
2810 For l_rec1 in c1 loop
2811 l_step := trunc(l_step,-1) + 10;
2812 For l_rec in c2(l_rec1.pl_id) Loop
2813 If (p_effective_date = l_rec.effective_start_date ) then
2814 l_datetrack_mode := hr_api.g_correction;
2815 Else
2816 l_datetrack_mode := hr_api.g_update;
2817 end if;
2818 --
2819 -- Noted:
2820 -- the following call need to changed to call suspended process after
2821 -- suspend process completed.
2822 --
2823 BEN_PRTT_ENRT_RESULT_API.Update_PRTT_ENRT_RESULT
2824 (p_validate => FALSE
2825 ,p_prtt_enrt_rslt_id => l_rec.prtt_enrt_rslt_id
2826 ,p_per_in_ler_id => l_rec1.per_in_ler_id
2827 ,p_ler_id => l_rec1.ler_id
2828 ,p_effective_start_date => l_rec.effective_start_date
2829 ,p_effective_end_date => l_rec.effective_end_date
2830 ,p_business_group_id => p_business_group_id
2831 ,p_object_version_number => l_rec.object_version_number
2832 ,p_datetrack_mode => l_datetrack_mode
2833 ,p_sspndd_flag => 'Y'
2834 ,p_effective_date => p_effective_date
2835 );
2836 End loop;
2837 End loop;
2838 hr_utility.set_location('Leaving' || l_proc,10);
2839 Exception
2840 when others then
2841 hr_utility.set_location('Fail at '||l_proc||' step - '||
2842 to_char(l_step),999);
2843 raise;
2844 End;
2845 --
2846 --
2847 -- ----------------------------------------------------------------------------
2848 -- |------<multi_row_edit >------|
2849 -- ----------------------------------------------------------------------------
2850 --
2851 -- Description
2852 -- This procedure is used to cached all programs, plan types, plans for
2853 -- a specified person_id..
2854 --
2855 -- Pre Conditions
2856 -- None.
2857 --
2858 -- In Parameters
2859 -- person_id Person ID.
2860 -- effective_date effective date.
2861 -- p_business_group_id Business Group ID.
2862 -- p_pgm_id Program ID If NULL then it means Plan not in
2863 -- program.
2864 --
2865 -- Post Success
2866 -- Processing continues
2867 --
2868 -- Post Failure
2869 -- Error handled by procedure
2870 --
2871 -- Access Status
2872 -- Internal table handler use only.
2873 --
2874 Procedure multi_rows_edit
2875 (p_person_id in number
2876 ,p_effective_date in date
2877 ,p_business_group_id in number
2878 ,p_pgm_id in number
2879 ,p_include_erl in varchar2
2880 ) is
2881 cursor c_paf is
2882 select assignment_id
2883 from per_all_assignments_f
2884 where person_id = p_person_id
2885 and assignment_type <> 'C'
2886 and primary_flag = 'Y'
2887 and p_effective_date between
2888 effective_start_date and effective_end_date
2889 and business_group_id = p_business_group_id
2890 order by assignment_type desc, effective_start_date desc ; -- bug 4124110
2891 --
2892 cursor c_pl_name(cv_pl_id number) is
2893 select pln.name
2894 from ben_pl_f pln
2895 where pln.pl_id = cv_pl_id
2896 and pln.business_group_id = p_business_group_id;
2897 --
2898
2899 --Bug 2390734 Changed the join condition to compare OPT.OPT_ID with OIPL.OPT_ID.(Intially it was being compared with itself)
2900 cursor c_opt_name(cv_oipl_id in number) is
2901 select ' : ' || opt.name
2902 from ben_oipl_f oipl,
2903 ben_opt_f opt
2904 where oipl.oipl_id = cv_oipl_id
2905 and opt.opt_id = oipl.opt_id --Bug 2390734
2906 and opt.business_group_id = p_business_group_id
2907 and oipl.business_group_id = p_business_group_id;
2908 --
2909 cursor c_erl (p_pl_typ_id number) is
2910 select 'Y'
2911 from ben_prtt_enrt_rslt_f pen,
2912 ben_enrt_bnft enb
2913 where pen.person_id = p_person_id
2914 and pen.prtt_enrt_rslt_stat_cd is null
2915 and enrt_cvg_thru_dt = hr_api.g_eot
2916 and pen.comp_lvl_cd <> 'PLANIMP'
2917 and pen.prtt_enrt_rslt_id = enb.prtt_enrt_rslt_id
2918 and pen.pgm_id = p_pgm_id
2919 and pen.pl_typ_id = p_pl_typ_id
2920 and enb.cvg_mlt_cd = 'ERL'
2921 and pen.effective_end_date = hr_api.g_eot;
2922 --
2923 i binary_integer :=0;
2924 j binary_integer :=0;
2925 l_pl_notfnd boolean :=TRUE;
2926 l_init_flag boolean :=TRUE;
2927 l_prev_opt_id ben_oipl_f.opt_id%type := 0;
2928 l_status boolean;
2929 l_step integer;
2930 l_proc varchar2(72) := g_package||'multi_rows_edit';
2931 l_prev_oipl_id number := -999999;
2932 l_prev_ptip_name ben_pl_typ_f.name%type; -- UTF8 Change Bug 2254683
2933 l_prev_pl_id number := -999999;
2934 l_prev_cvg_strt_dt date;
2935 l_prev_cvg_end_dt date;
2936 l_assignment_id per_assignments_f.assignment_id%type;
2937 l_increase number;
2938 l_plan_opt_names varchar2(3000); --UTF8 Change Bug 2254683
2939 l_chk_pln_cvg_lmts boolean;
2940 --
2941 -- Bug 2162121
2942 --
2943 l_ptip_tbl_ct number := g_ptip_tbl.count;
2944 l_plan_name ben_pl_f.name%type; -- UTF8 Change Bug 2254683
2945 l_option_name ben_opt_f.name%type; -- UTF8 Change Bug 2254683
2946 --
2947 l_pl_rec ben_cobj_cache.g_pl_inst_row;
2948 /*
2949 l_pl_rec ben_pl_f%rowtype;
2950 */
2951 l_oipl_rec ben_cobj_cache.g_oipl_inst_row;
2952 /*
2953 l_oipl_rec ben_oipl_f%rowtype;
2954 */
2955 l_plip_rec ben_cobj_cache.g_plip_inst_row;
2956 /*
2957 l_plip_rec ben_plip_f%rowtype;
2958 */
2959 l_ptip_rec ben_cobj_cache.g_ptip_inst_row;
2960 /*
2961 l_ptip_rec ben_ptip_f%rowtype;
2962 */
2963 l_erl varchar2(30);
2964
2965 --
2966 begin
2967 hr_utility.set_location(l_proc,5);
2968 --
2969 -- suspended all suspended saving plan's options.
2970 --
2971 l_step := 5;
2972 susp_svg_pl_opts
2973 (p_person_id => p_person_id
2974 ,p_effective_date => p_effective_date
2975 ,p_business_group_id => p_business_group_id
2976 ,p_pgm_id => p_pgm_id
2977 );
2978 --
2979 -- Retrieve asignment id from per_assignment_f.
2980 --
2981 l_step := 10;
2982 open c_paf;
2983 fetch c_paf into l_assignment_id;
2984 if (c_paf%notfound) then
2985 close c_paf;
2986 fnd_message.set_name('BEN','BEN_91698_NO_ASSIGNMENT_FND');
2987 fnd_message.set_token('ID' , to_char(p_person_id));
2988 --Bug# 2261610
2989 fnd_message.set_token('PROC' , l_proc);
2990 fnd_message.raise_error;
2991 end if;
2992 close c_paf;
2993 --
2994 hr_utility.set_location('l_assignment_id is :-'||l_assignment_id ,1234);
2995 -- Cache all comp objects belong to a specified person_id for a
2996 -- specified program or all program dependent on p_chk_all_pgm_flg,
2997 -- If Yes, then all program will be cached, otherwise, only the pgm_id
2998 -- is specified.
2999 --
3000 l_step := 20;
3001 cache_enrt_info(p_effective_date
3002 ,p_business_group_id
3003 ,p_person_id, p_pgm_id
3004 ,l_assignment_id
3005 ,p_include_erl);
3006 --
3007 -- Check enrollment limitation and coverage limitation of plan type
3008 -- in program.
3009 --
3010 for i in 1..g_ptip_cnt loop
3011 l_step := 25;
3012 -- * plan type in program enrollment limitation (Max and Min).
3013 if (g_ptip_tbl(i).tot_pl_enrld > g_ptip_tbl(i).MX_ENRD_ALWD_OVRID_NUM
3014 and g_ptip_tbl(i).MX_ENRD_ALWD_OVRID_NUM is not NULL
3015 and g_ptip_tbl(i).dpnt_cvd_by_othr_apls_flag = 'N') then
3016 --
3017 --bug #2162121
3018 --
3019 hr_utility.set_location('Get the plan name and option name.:-' ,1234);
3020 l_ptip_tbl_ct := nvl(g_ptip_tbl(i).tot_pl_enrld, 0); -- nvl(g_ptip_tbl.count, 0); Bug 2390734
3021 -- -- This change is made so that the error message for 92968 displays names of only
3022 -- those compensation objects, that the person is being enrolled in.
3023 if l_ptip_tbl_ct > 0 then
3024 --
3025 l_plan_opt_names := null;
3026 --
3027 if l_ptip_tbl_ct > 10 then
3028 l_ptip_tbl_ct := 10;
3029 end if;
3030 for i2 in 1..l_ptip_tbl_ct loop
3031
3032 if g_ptip_tbl(i).ptip_id = g_enrt_tbl(i2).ptip_id then
3033
3034 l_plan_name := null;
3035 open c_pl_name(g_enrt_tbl(i2).pl_id);
3036 fetch c_pl_name into l_plan_name;
3037 close c_pl_name;
3038
3039 -- l_plan_opt_names := l_plan_opt_names ||l_plan_name;
3040 hr_utility.set_location (l_plan_name|| 'l_plan_name :1 : ',1235);
3041
3042 if g_enrt_tbl(i2).oipl_id is not null then
3043 --
3044 l_option_name := null;
3045 open c_opt_name(g_enrt_tbl(i2).oipl_id);
3046 fetch c_opt_name into l_option_name;
3047 close c_opt_name;
3048
3049 l_plan_name := l_plan_name ||l_option_name;
3050 hr_utility.set_location (l_option_name|| 'l_option_name',1235);
3051 --
3052 end if ;
3053
3054 if l_plan_opt_names is null then
3055 l_plan_opt_names := l_plan_name;
3056 else
3057 l_plan_opt_names := l_plan_opt_names || ', ' ||l_plan_name;
3058 end if;
3059
3060 end if; --g_ptip_tbl(i).ptip_id = g_enrt_tbl(i2).ptip_id
3061
3062 hr_utility.set_location (l_plan_opt_names|| 'l_plan_opt_names',1235);
3063
3064 end loop;
3065 end if; --l_ptip_tbl_ct > 0
3066
3067 -- end 2162121
3068 fnd_message.set_name('BEN','BEN_92968_PL_ENRD_GT_MX_ALWD');
3069 fnd_message.set_token ('PLAN_OPTION_NAMES', l_plan_opt_names);
3070 fnd_message.set_token
3071 ('TOT_ENRD'
3072 ,to_char(g_ptip_tbl(i).tot_pl_enrld));
3073 fnd_message.set_token
3074 ('MX_ENRL'
3075 ,to_char(g_ptip_tbl(i).MX_ENRD_ALWD_OVRID_NUM));
3076 fnd_message.set_token('PL_TYP_NAME', g_ptip_tbl(i).name);
3077 fnd_message.raise_error;
3078 elsif(g_ptip_tbl(i).tot_pl_enrld < g_ptip_tbl(i).MN_ENRD_RQD_OVRID_NUM
3079 and g_ptip_tbl(i).MN_ENRD_RQD_OVRID_NUM is not NULL) then
3080 l_erl := 'N';
3081 if p_include_erl = 'N' then
3082 --
3083 open c_erl(g_ptip_tbl(i).pl_typ_id);
3084 fetch c_erl into l_erl;
3085 close c_erl;
3086 --
3087 end if;
3088 if l_erl = 'N' then
3089 --
3090 l_step := 26;
3091 fnd_message.set_name('BEN','BEN_91588_PL_ENRD_LT_MN_RQD');
3092 /* fnd_message.set_token
3093 ('TOT_ENRD'
3094 ,to_char(g_ptip_tbl(i).tot_pl_enrld));*/ -- Bug 5664907
3095 fnd_message.set_token
3096 ('MN_ENRL'
3097 ,to_char(g_ptip_tbl(i).MN_ENRD_RQD_OVRID_NUM));
3098 fnd_message.set_token('PL_TYP_NAME', g_ptip_tbl(i).name);
3099 fnd_message.raise_error;
3100 --
3101 end if;
3102 --
3103 end if;
3104 --
3105 -- Plan type in program coverage limitation (Max and Min).
3106 --
3107 l_step := 30;
3108 if (g_ptip_tbl(i).tot_cvg_amt > g_ptip_tbl(i).MX_CVG_ALWD_AMT
3109 and g_ptip_tbl(i).MX_CVG_ALWD_AMT is not NULL ) then
3110 fnd_message.set_name('BEN','BEN_92500_PL_CVG_AMT_GT_MX_ALW');
3111 fnd_message.set_token
3112 ('TOT_AMT'
3113 ,to_char(g_ptip_tbl(i).tot_cvg_amt));
3114 fnd_message.set_token
3115 ('MX_AMT'
3116 ,to_char(g_ptip_tbl(i).MX_CVG_ALWD_AMT));
3117 fnd_message.set_token('PL_TYP_NAME', g_ptip_tbl(i).name);
3118 fnd_message.raise_error;
3119 end if;
3120 --
3121 -- If plan type is spouse/dependent life ins then store amount for
3122 -- later used.
3123 --
3124 l_step := 35;
3125 if (g_ptip_tbl(i).sbj_to_sps_lf_ins_mx_flag = 'N') and
3126 (g_ptip_tbl(i).sbj_to_dpnt_lf_ins_mx_flag = 'N') and
3127 (g_ptip_tbl(i).use_to_sum_ee_lf_ins_flag = 'Y') then
3128 g_tot_ee_lf_ins_amt := g_tot_ee_lf_ins_amt + g_ptip_tbl(i).tot_cvg_amt;
3129 g_tot_ee_lf_ins_amt_no := g_tot_ee_lf_ins_amt_no + g_ptip_tbl(i).tot_cvg_amt_no_interim;
3130 end if;
3131 hr_utility.set_location('g_tot_ee_lf_ins_amt '||g_tot_ee_lf_ins_amt,111);
3132 hr_utility.set_location('g_tot_ee_lf_ins_amt_no '||g_tot_ee_lf_ins_amt_no,111);
3133 end loop;
3134 --
3135 -- Check for spouse and dependent coverage limitations.
3136 --
3137 for i in 1..g_ptip_cnt loop
3138 -- Bug 4613929, Started one more Plantype loop to check for coverage for
3139 -- Sps or dpnt's plantype is within participant's % maximum coverage
3140 l_step := 38;
3141 if (g_ptip_tbl(i).sbj_to_sps_lf_ins_mx_flag = 'Y') then
3142 g_tot_sps_lf_ins_amt := g_ptip_tbl(i).tot_cvg_amt;
3143 g_tot_sps_lf_ins_amt_no := g_ptip_tbl(i).tot_cvg_amt_no_interim;
3144 if (g_pgm_rec.pgm_id = g_ptip_tbl(i).pgm_id) then
3145 g_mx_sps_pct_prtt_lf := g_pgm_rec.MX_SPS_PCT_PRTT_LF_AMT;
3146 end if;
3147 elsif (g_ptip_tbl(i).sbj_to_dpnt_lf_ins_mx_flag = 'Y') then
3148 g_tot_dpnt_lf_ins_amt := g_ptip_tbl(i).tot_cvg_amt;
3149 g_tot_dpnt_lf_ins_amt_no := g_ptip_tbl(i).tot_cvg_amt_no_interim;
3150 if (g_pgm_rec.pgm_id = g_ptip_tbl(i).pgm_id) then
3151 g_mx_dpnt_pct_prtt_lf := g_pgm_rec.MX_DPNT_PCT_PRTT_LF_AMT;
3152 end if;
3153 end if;
3154 --
3155 hr_utility.set_location('g_tot_sps_lf_ins_amt'||g_tot_sps_lf_ins_amt,111);
3156 hr_utility.set_location('g_tot_dpnt_lf_ins_amt'||g_tot_dpnt_lf_ins_amt,111);
3157 hr_utility.set_location('g_tot_sps_lf_ins_amt_no'||g_tot_sps_lf_ins_amt_no,111);
3158 hr_utility.set_location('g_tot_dpnt_lf_ins_amt_no'||g_tot_dpnt_lf_ins_amt_no,111);
3159 --
3160 l_step := 40;
3161 if (((g_mx_dpnt_pct_prtt_lf * g_tot_ee_lf_ins_amt)/100)
3162 < g_tot_dpnt_lf_ins_amt) then
3163 fnd_message.set_name('BEN','BEN_91590_DPNT_LF_INS_OVER_LMT');
3164 fnd_message.set_token('TOT_INS',to_char(g_tot_dpnt_lf_ins_amt));
3165 fnd_message.set_token
3166 ('MX_INS'
3167 ,to_char((g_mx_dpnt_pct_prtt_lf * g_tot_ee_lf_ins_amt)/100));
3168 fnd_message.raise_error;
3169 elsif(((g_mx_sps_pct_prtt_lf * g_tot_ee_lf_ins_amt)/100)
3170 < g_tot_sps_lf_ins_amt) then
3171 fnd_message.set_name('BEN','BEN_91591_SP_LF_INS_OVER_LMT');
3172 fnd_message.set_token('TOT_INS', to_char(g_tot_sps_lf_ins_amt));
3173 fnd_message.set_token('MX_INS',
3174 to_char((g_mx_sps_pct_prtt_lf * g_tot_ee_lf_ins_amt)/100));
3175 fnd_message.raise_error;
3176 end if;
3177 --
3178 l_step := 45;
3179 --This Evaluates the suspended coverage also in determining the
3180 --limits.
3181 --
3182 if (((g_mx_dpnt_pct_prtt_lf * g_tot_ee_lf_ins_amt_no)/100)
3183 < g_tot_dpnt_lf_ins_amt_no) then
3184 fnd_message.set_name('BEN','BEN_91590_DPNT_LF_INS_OVER_LMT');
3185 fnd_message.set_token('TOT_INS',to_char(g_tot_dpnt_lf_ins_amt_no));
3186 fnd_message.set_token
3187 ('MX_INS'
3188 ,to_char((g_mx_dpnt_pct_prtt_lf * g_tot_ee_lf_ins_amt_no)/100));
3189 fnd_message.raise_error;
3190 elsif (((g_mx_sps_pct_prtt_lf * g_tot_ee_lf_ins_amt_no)/100)
3191 < g_tot_sps_lf_ins_amt_no) then
3192 fnd_message.set_name('BEN','BEN_91591_SP_LF_INS_OVER_LMT');
3193 fnd_message.set_token('TOT_INS', to_char(g_tot_sps_lf_ins_amt_no));
3194 fnd_message.set_token('MX_INS',
3195 to_char((g_mx_sps_pct_prtt_lf * g_tot_ee_lf_ins_amt_no)/100));
3196 fnd_message.raise_error;
3197 end if;
3198 end loop; -- Bug 4613929
3199
3200 -- Check for plan type in enrollment limitation
3201 --
3202 l_step := 50;
3203 for i in 1..g_pl_typ_cnt loop
3204 --
3205 -- Plan type enrollment limitation (Max and Min).
3206 --
3207 hr_utility.set_location('pl_typ_id='||to_char(g_pl_typ_tbl(i).pl_typ_id),99);
3208 hr_utility.set_location('val='||to_char(g_pl_typ_tbl(i).tot_pl_enrld)||
3209 ' min='||to_char(g_pl_typ_tbl(i).mn_enrl_rqd_num)||
3210 ' max='||to_char(g_pl_typ_tbl(i).mx_enrl_alwd_num)
3211 , 101);
3212 if (g_pl_typ_tbl(i).tot_pl_enrld > g_pl_typ_tbl(i).mx_enrl_alwd_num
3213 and g_pl_typ_tbl(i).mx_enrl_alwd_num is not NULL
3214 and g_pl_typ_tbl(i).dpnt_cvd_by_othr_apls_flag = 'N') then /* and g_ptip_tbl(i).dpnt_cvd_by_othr_apls_flag = 'N') then Bug 2093956*/
3215 fnd_message.set_name('BEN','BEN_91587_PL_ENRD_GT_MX_ALWD'); /* Modified the above line to check for the flag from the pl_typ table instead of PTIP table*/
3216 fnd_message.set_token
3217 ('TOT_ENRD'
3218 ,to_char(g_pl_typ_tbl(i).tot_pl_enrld));
3219 fnd_message.set_token
3220 ('MX_ENRL'
3221 , to_char(g_pl_typ_tbl(i).MX_ENRL_ALWD_NUM));
3222 fnd_message.set_token('PL_TYP_NAME', g_pl_typ_tbl(i).name);
3223 fnd_message.raise_error;
3224 elsif (g_pl_typ_tbl(i).tot_pl_enrld < g_pl_typ_tbl(i).mn_enrl_rqd_num
3225 and g_pl_typ_tbl(i).mn_enrl_rqd_num is not NULL ) then
3226 --
3227 l_erl := 'N';
3228 if p_include_erl = 'N' then
3229 --
3230 open c_erl(g_pl_typ_tbl(i).pl_typ_id);
3231 fetch c_erl into l_erl;
3232 close c_erl;
3233 --
3234 end if;
3235 if l_erl = 'N' then
3236 --
3237 fnd_message.set_name('BEN','BEN_91588_PL_ENRD_LT_MN_RQD');
3238 /* fnd_message.set_token
3239 ('TOT_ENRD'
3240 ,to_char(g_pl_typ_tbl(i).tot_pl_enrld));*/ --Bug 5664907
3241 fnd_message.set_token
3242 ('MN_ENRL'
3243 ,to_char(g_pl_typ_tbl(i).MN_ENRL_RQD_NUM));
3244 fnd_message.set_token('PL_TYP_NAME', g_pl_typ_tbl(i).name);
3245 fnd_message.raise_error;
3246 --
3247 end if;
3248 --
3249 end if;
3250 end loop;
3251 --
3252 -- Check min and max of options winthin plan.
3253 --
3254 l_step := 60;
3255 for i in 1..g_pl_cnt loop
3256 --
3257 -- BUG: 4590341
3258 -- Check for No. of Options Enrolled is b/w min.required and max.allowed
3259 --
3260 if (g_pl_tbl(i).interim_flag = 1 and g_pl_tbl(i).tot_opt_enrld = 0) then
3261 null;
3262 else
3263 if (g_pl_tbl(i).tot_opt_enrld > g_pl_tbl(i).mx_opts_alwd_num
3264 and g_pl_tbl(i).mx_opts_alwd_num is not NULL ) then
3265 fnd_message.set_name('BEN','BEN_91592_OPT_ENRD_GT_MX_ALWD');
3266 fnd_message.set_token('OPT_ENRD',to_char(g_pl_tbl(i).tot_opt_enrld));
3267 fnd_message.set_token('MX_ALWD',to_char(g_pl_tbl(i).MX_OPTS_ALWD_NUM));
3268 fnd_message.set_token('PL_NAME', g_pl_tbl(i).name);
3269 fnd_message.raise_error;
3270 elsif (g_pl_tbl(i).tot_opt_enrld < g_pl_tbl(i).mn_opts_rqd_num
3271 and g_pl_tbl(i).mn_opts_rqd_num is not NULL) then
3272 fnd_message.set_name('BEN','BEN_91593_OPT_ENRD_LT_MN_RQD');
3273 fnd_message.set_token('OPT_ENRD', to_char(g_pl_tbl(i).tot_opt_enrld));
3274 fnd_message.set_token('MN_RQD',to_char(g_pl_tbl(i).MN_OPTS_RQD_NUM));
3275 fnd_message.set_token('PL_NAME', g_pl_tbl(i).name);
3276 fnd_message.raise_error;
3277 end if;
3278 /* BUG: 3949327:
3279 The below coverage amount limits validations need
3280 to be performed only when enrolled into Plan.
3281 So, loop thru g_enrt_tbl and to find
3282 if enrolled into pln then check the following
3283 1. Total Coverage Amount b/w min.required and max.allowed
3284 2. If Previously enrolled, then increased_amount b/w min.incr.req. and max.incr allowed
3285 */
3286 l_chk_pln_cvg_lmts := FALSE;
3287 --
3288 FOR j IN 1 .. g_comp_obj_cnt LOOP
3289 --
3290 IF (g_enrt_tbl (j).pl_id = g_pl_tbl (i).pl_id
3291 AND g_enrt_tbl (j).prtt_enrt_rslt_id IS NOT NULL
3292 ) THEN
3293 hr_utility.set_location('l_chk_pln_cvg_lmts='|| 'TRUE' ,99);
3294 l_chk_pln_cvg_lmts := TRUE;
3295 EXIT;
3296 END IF;
3297 END LOOP;
3298 --
3299 hr_utility.set_location('g_pl_tbl(i).tot_cvg_amt='||g_pl_tbl(i).tot_cvg_amt,99);
3300 hr_utility.set_location('g_pl_tbl(i).mx_cvg_alwd_amt='||g_pl_tbl(i).mx_cvg_alwd_amt,99);
3301 hr_utility.set_location('g_pl_tbl(i).mx_cvg_wcfn_amt='||g_pl_tbl(i).mx_cvg_wcfn_amt,99);
3302 --
3303 IF (l_chk_pln_cvg_lmts) THEN
3304 -- Bug: 3949327 changes end;
3305 -- Check min coverage amount.
3306 --
3307 l_step := 70;
3308 --
3309 if (g_pl_tbl(i).tot_cvg_amt < g_pl_tbl(i).mn_cvg_rqd_amt
3310 and g_pl_tbl(i).mn_cvg_rqd_amt is not NULL ) then
3311 fnd_message.set_name('BEN','BEN_92286_PL_CVG_AMT_LT_MN_ALW');
3312 fnd_message.set_token('TOT_AMT',to_char(g_pl_tbl(i).tot_cvg_amt));
3313 fnd_message.set_token('MN_AMT',to_char(g_pl_tbl(i).mn_cvg_rqd_amt));
3314 fnd_message.set_token('PL_NAME', g_pl_tbl(i).name);
3315 fnd_message.raise_error;
3316 end if;
3317 --
3318 -- Check max coverage amount.
3319 --
3320 l_step := 75;
3321 --
3322 -- Coverage problem is possible if > max
3323 --
3324 if (g_pl_tbl(i).tot_cvg_amt > g_pl_tbl(i).mx_cvg_alwd_amt
3325 and g_pl_tbl(i).mx_cvg_alwd_amt is not NULL ) then
3326 if g_pl_tbl(i).tot_cvg_amt > g_pl_tbl(i).mx_cvg_wcfn_amt then
3327 --
3328 -- Over top even with certification
3329 --
3330 fnd_message.set_name('BEN','BEN_91589_PL_CVG_AMT_GT_MX_ALW');
3331 fnd_message.set_token('TOT_AMT',to_char(g_pl_tbl(i).tot_cvg_amt));
3332 fnd_message.set_token('MX_AMT',to_char(g_pl_tbl(i).MX_CVG_wcfn_AMT));
3333 fnd_message.set_token('PL_NAME', g_pl_tbl(i).name);
3334 fnd_message.raise_error;
3335 elsif g_pl_tbl(i).mx_cvg_wcfn_amt is null then
3336 --
3337 -- Over top and no certification limit exists
3338 --
3339 fnd_message.set_name('BEN','BEN_91589_PL_CVG_AMT_GT_MX_ALW');
3340 fnd_message.set_token('TOT_AMT',to_char(g_pl_tbl(i).tot_cvg_amt));
3341 fnd_message.set_token('MX_AMT',to_char(g_pl_tbl(i).MX_CVG_ALWD_AMT));
3342 fnd_message.set_token('PL_NAME', g_pl_tbl(i).name);
3343 fnd_message.raise_error;
3344 end if;
3345 end if;
3346 --
3347 -- Check max coverage increase amount.
3348 --
3349 l_step := 76;
3350 --
3351 -- Coverage problem is possible if increase > max
3352 --
3353 l_increase:=g_pl_tbl(i).tot_cvg_amt - g_pl_tbl(i).prev_cvg_amt;
3354 --
3355 -- Check increases only if the person was not previously enrolled.
3356 -- If the amount is zero, the person was not previously enrolled and
3357 -- is allowed to choose any coverage irrespective of the
3358 -- amount defined in (mx_cvg_incr_alwd_amt).
3359 --
3360 if (g_pl_tbl(i).prev_cvg_amt <> 0 and
3361 l_increase > g_pl_tbl(i).mx_cvg_incr_alwd_amt
3362 and g_pl_tbl(i).mx_cvg_incr_alwd_amt is not NULL ) then
3363 if l_increase > g_pl_tbl(i).mx_cvg_incr_wcf_alwd_amt then
3364 --
3365 -- Over top even with certification
3366 --
3367 fnd_message.set_name('BEN','BEN_91594_CERT_INCR_GT_MX_INCR');
3368 fnd_message.set_token('CERT_INCR',to_char(l_increase));
3369 fnd_message.set_token('MX_INCR',to_char(g_pl_tbl(i).MX_CVG_INCR_WCF_ALWD_AMT));
3370 fnd_message.raise_error;
3371 --
3372 elsif g_pl_tbl(i).mx_cvg_incr_wcf_alwd_amt is null then
3373 --
3374 -- Over top and no certification limit exists
3375 --
3376 fnd_message.set_name('BEN','BEN_91596_CVG_INCR_GT_MX_INCR');
3377 fnd_message.set_token('CVG_INCR',to_char(l_increase));
3378 fnd_message.set_token('MX_INCR',to_char(g_pl_tbl(i).MX_CVG_INCR_ALWD_AMT));
3379 fnd_message.raise_error;
3380 end if;
3381 --
3382 end if;
3383 --
3384 end if; --- l_chk_pln_cvg_lmts end-if
3385 --
3386 end if;
3387 --
3388 end loop;
3389 --
3390 -- Make sure not duplicate comp object with in a program such as
3391 -- same plan id and option id.
3392 --
3393 l_step := 80;
3394 -- Bug 6741391
3395 ben_cobj_cache.clear_down_cache;
3396 -- Bug 6741391
3397 for i in 1..g_comp_obj_cnt loop
3398 --
3399 if g_enrt_tbl(i).prtt_enrt_rslt_id is not null and
3400 g_enrt_tbl(i).interim_flag = 'N' then
3401 --
3402 -- RCHASE - Bug#1412801 - Moved post election edit rule from insert/update
3403 -- RCHASE validation calls to multi-row edit
3404 --
3405 ben_cobj_cache.get_pl_dets
3406 (p_business_group_id => p_business_group_id
3407 ,p_effective_date => p_effective_date
3408 ,p_pl_id => g_enrt_tbl(i).pl_id
3409 ,p_inst_row => l_pl_rec
3410 );
3411 --
3412 /*
3413 ben_comp_object.get_object(p_pl_id => g_enrt_tbl(i).pl_id,
3414 p_rec => l_pl_rec);
3415 */
3416 --
3417 if g_enrt_tbl(i).plip_id is not null then
3418 --
3419 ben_cobj_cache.get_plip_dets
3420 (p_business_group_id => p_business_group_id
3421 ,p_effective_date => p_effective_date
3422 ,p_plip_id => g_enrt_tbl(i).plip_id
3423 ,p_inst_row => l_plip_rec
3424 );
3425 --
3426 /*
3427 ben_comp_object.get_object(p_plip_id => g_enrt_tbl(i).plip_id,
3428 p_rec => l_plip_rec);
3429 */
3430 else
3431 l_plip_rec := null;
3432 end if;
3433 --
3434 if g_enrt_tbl(i).ptip_id is not null then
3435 --
3436 ben_cobj_cache.get_ptip_dets
3437 (p_business_group_id => p_business_group_id
3438 ,p_effective_date => p_effective_date
3439 ,p_ptip_id => g_enrt_tbl(i).ptip_id
3440 ,p_inst_row => l_ptip_rec
3441 );
3442 --
3443 /*
3444 ben_comp_object.get_object(p_ptip_id => g_enrt_tbl(i).ptip_id,
3445 p_rec => l_ptip_rec);
3446 */
3447 else
3448 l_ptip_rec := null;
3449 end if;
3450 --
3451 if g_enrt_tbl(i).oipl_id is not null then
3452 --
3453 ben_cobj_cache.get_oipl_dets
3454 (p_business_group_id => p_business_group_id
3455 ,p_effective_date => p_effective_date
3456 ,p_oipl_id => g_enrt_tbl(i).oipl_id
3457 ,p_inst_row => l_oipl_rec
3458 );
3459 --
3460 /*
3461 ben_comp_object.get_object(p_oipl_id => g_enrt_tbl(i).oipl_id,
3462 p_rec => l_oipl_rec);
3463 */
3464 else
3465 l_oipl_rec := null;
3466 end if;
3467
3468 if l_pl_rec.postelcn_edit_rl is not null or
3469 l_oipl_rec.postelcn_edit_rl is not null or
3470 l_plip_rec.postelcn_edit_rl is not null or
3471 l_ptip_rec.postelcn_edit_rl is not null then
3472 chk_post_elcn_rl
3473 (p_pgm_id => g_enrt_tbl(i).pgm_id,
3474 p_pl_id => g_enrt_tbl(i).pl_id,
3475 p_pl_typ_id => g_enrt_tbl(i).pl_typ_id,
3476 p_opt_id => g_enrt_tbl(i).opt_id,
3477 p_person_id => p_person_id,
3478 p_effective_date => p_effective_date,
3479 p_business_group_id => p_business_group_id,
3480 p_pl_post_edit_rl => l_pl_rec.postelcn_edit_rl,
3481 p_plip_post_edit_rl => l_plip_rec.postelcn_edit_rl,
3482 p_ptip_post_edit_rl => l_ptip_rec.postelcn_edit_rl,
3483 p_oipl_post_edit_rl => l_oipl_rec.postelcn_edit_rl);
3484 end if;
3485 -- RCHASE End
3486 --
3487 -- Check whether the plan is same as the previous one.
3488 -- Check the oipl to be same, if they are not null.
3489 -- This logic of just checking with the previous record works
3490 -- fine as the records are stored in the order pl_id, oipl_id.
3491 --
3492 if (l_prev_pl_id = g_enrt_tbl(i).pl_id) and
3493 ((g_enrt_tbl(i).oipl_id is null and l_prev_oipl_id is null) or
3494 l_prev_oipl_id = g_enrt_tbl(i).oipl_id) then
3495 --
3496 -- Plan and oipl are same, now check whether the coverage overlap.
3497 -- If not, it's fine to have two results. (Possible when a person
3498 -- re-enrolls in a comp-object after de-enrolling from it.)
3499 --
3500 if (l_prev_cvg_strt_dt between
3501 g_enrt_tbl(i).enrt_cvg_strt_dt
3502 and nvl(g_enrt_tbl(i).enrt_cvg_thru_dt,hr_api.g_eot))
3503 OR
3504 (g_enrt_tbl(i).enrt_cvg_strt_dt between
3505 l_prev_cvg_strt_dt
3506 and nvl(l_prev_cvg_end_dt,hr_api.g_eot)) then
3507 --
3508 fnd_message.set_name('BEN','BEN_91699_DUP_COMP_OBJ_IN_PGM');
3509 fnd_message.set_token
3510 ('PL_NAME'
3511 ,get_plan_name(p_pl_id => g_enrt_tbl(i).pl_id
3512 ,p_business_group_id => p_business_group_id
3513 ,p_effective_date => p_effective_date
3514 )
3515 );
3516 fnd_message.raise_error;
3517 --
3518 end if;
3519 --
3520 end if;
3521 --
3522 l_prev_pl_id := g_enrt_tbl(i).pl_id;
3523 l_prev_oipl_id := g_enrt_tbl(i).oipl_id;
3524 l_prev_cvg_strt_dt := g_enrt_tbl(i).enrt_cvg_strt_dt;
3525 l_prev_cvg_end_dt := g_enrt_tbl(i).enrt_cvg_thru_dt;
3526 --
3527 if (g_enrt_tbl(i).must_enrl_anthr_pl_id is not NULL) then
3528 l_pl_notfnd := TRUE;
3529 for j in 1..g_pl_cnt loop
3530 if (g_enrt_tbl(i).must_enrl_anthr_pl_id = g_pl_tbl(j).pl_id
3531 ) then
3532 l_pl_notfnd := FALSE;
3533 exit;
3534 end if;
3535 end loop;
3536 if (l_pl_notfnd) then
3537 fnd_message.set_name('BEN','BEN_91597_MUST_ENRL_PLAN');
3538 fnd_message.set_token('PL_ID', g_enrt_tbl(i).pl_id);
3539 fnd_message.set_token
3540 ('RQD_PL'
3541 ,get_plan_name
3542 (p_pl_id=> g_enrt_tbl(i).must_enrl_anthr_pl_id
3543 ,p_business_group_id => p_business_group_id
3544 ,p_effective_date => p_effective_date
3545 )
3546 );
3547 fnd_message.raise_error;
3548 end if;
3549 end if;
3550 end if;
3551 end loop;
3552 --
3553 -- Check for coordinate coverages. If coordinate flag set on in ptip
3554 -- level, then all options in each plan need to same.
3555 --
3556 l_step := 90;
3557 l_init_flag := true;
3558 for i in 1..g_ptip_cnt loop
3559 if (g_ptip_tbl(i).COORD_CVG_FOR_ALL_PLS_FLAG = 'Y') then
3560 for j in 1..g_comp_obj_cnt loop
3561 --
3562 if g_enrt_tbl(j).ptip_id = g_ptip_tbl(i).ptip_id and
3563 g_enrt_tbl(j).prtt_enrt_rslt_id is not null and
3564 g_enrt_tbl(j).enrt_cvg_thru_dt = hr_api.g_eot and
3565 g_enrt_tbl(j).interim_flag = 'N' and
3566 g_enrt_tbl(j).opt_id is not null then
3567 --
3568 if (l_init_flag) then
3569 l_prev_opt_id := g_enrt_tbl(j).opt_id;
3570 l_prev_ptip_name := g_ptip_tbl(i).name;
3571 l_init_flag := FALSE;
3572 elsif (l_prev_opt_id <> g_enrt_tbl(j).opt_id) then
3573 fnd_message.set_name('BEN','BEN_91598_OPT_NOT_COORD');
3574 fnd_message.set_token('PTIP_NAME1',g_ptip_tbl(i).name);
3575 fnd_message.set_token('PTIP_NAME2',l_prev_ptip_name);
3576 fnd_message.raise_error;
3577 end if;
3578 --
3579 end if;
3580 --
3581 end loop;
3582 end if;
3583 end loop;
3584 --
3585 -- Now check person_type_usage. Make sure participant coverage period is
3586 -- recorded in person type usage table.
3587 --
3588 l_step := 100;
3589 manage_per_type_usages
3590 (p_person_id => p_person_id
3591 ,p_business_group_id => p_business_group_id
3592 ,p_effective_date => p_effective_date
3593 );
3594 --
3595 -- Now check mandatory, If and plan is elected, then all oipl within the
3596 -- plan has mandatory flag set on need to be enrolled, otherwise the routine
3597 -- will bomb out.
3598 --
3599 l_step := 110;
3600 chk_mndtry_comp_obj
3601 (p_person_id => p_person_id
3602 ,p_pgm_id => p_pgm_id
3603 ,p_business_group_id => p_business_group_id
3604 ,p_effective_date => p_effective_date
3605 );
3606 hr_utility.set_location (l_proc, 10);
3607 Exception
3608 when others then
3609 hr_utility.set_location('Fail at '||l_proc||' step - '||
3610 to_char(l_step),999);
3611 raise;
3612 end;
3613 --
3614 /*--Bug#5088571
3615 -- ---------------------------------------------------------------------------
3616 -- |------------------------< chk_cvg_strt_end_dt >----------------------------|
3617 -- ---------------------------------------------------------------------------
3618 -- Description
3619 -- This procedure is used to check whether the Rate Start date is greater than Rate End date.
3620 --
3621 procedure chk_cvg_strt_end_dt(p_enrt_cvg_strt_dt in date,
3622 p_enrt_cvg_thru_dt in date,
3623 p_person_id in number
3624 ) is
3625 --
3626 l_proc varchar2(72) := g_package||'chk_cvg_strt_end_dt';
3627 l_person_id number;
3628 l_message_name varchar2(500) := 'BEN_94592_RT_STRT_GT_END_DT';
3629 --
3630 Begin
3631 --
3632 hr_utility.set_location('Entering:'||l_proc, 5);
3633 --
3634 if p_enrt_cvg_strt_dt > p_enrt_cvg_thru_dt then
3635 benutils.write(p_text=>fnd_message.get);
3636 ben_warnings.load_warning
3637 (p_application_short_name => 'BEN'
3638 ,p_message_name => l_message_name
3639 ,p_parma => 'Coverage End Date' || ' ' || fnd_date.date_to_displaydate(p_enrt_cvg_thru_dt)
3640 ,p_parmb => 'Coverage Start Date' ||' '|| fnd_date.date_to_displaydate(p_enrt_cvg_strt_dt)
3641 ,p_person_id => p_person_id
3642 );
3643 end if;
3644 --
3645 hr_utility.set_location('Leaving:'||l_proc,10);
3646 --
3647 end chk_cvg_strt_end_dt;
3648 --
3649 ----Bug#5088571*/
3650 -- ----------------------------------------------------------------------------
3651 -- |--------------------------< dt_update_validate >--------------------------|
3652 -- ----------------------------------------------------------------------------
3653 -- {Start Of Comments}
3654 --
3655 -- Description:
3656 -- This procedure is used for referential integrity of datetracked
3657 -- parent entities when a datetrack update operation is taking place
3658 -- and where there is no cascading of update defined for this entity.
3659 --
3660 -- Prerequisites:
3661 -- This procedure is called from the update_validate.
3662 --
3663 -- In Parameters:
3664 --
3665 -- Post Success:
3666 -- Processing continues.
3667 --
3668 -- Post Failure:
3669 --
3670 -- Developer Implementation Notes:
3671 -- This procedure should not need maintenance unless the HR Schema model
3672 -- changes.
3673 --
3674 -- Access Status:
3675 -- Internal Row Handler Use Only.
3676 --
3677 -- {End Of Comments}
3678 -- ----------------------------------------------------------------------------
3679 Procedure dt_update_validate
3680 (p_pgm_id in number default hr_api.g_number,
3681 p_oipl_id in number default hr_api.g_number,
3682 p_per_in_ler_id in number default hr_api.g_number,
3683 p_pl_id in number default hr_api.g_number,
3684 p_pl_typ_id in number default hr_api.g_number,
3685 p_prtt_enrt_rslt_id in number default hr_api.g_number,
3686 p_datetrack_mode in varchar2,
3687 p_validation_start_date in date,
3688 p_validation_end_date in date) Is
3689 --
3690 l_proc varchar2(72) := g_package||'dt_update_validate';
3691 l_integrity_error Exception;
3692 l_table_name all_tables.table_name%TYPE;
3693 --
3694 Begin
3695 hr_utility.set_location('Entering:'||l_proc, 5);
3696 --
3697 -- Ensure that the p_datetrack_mode argument is not null
3698 --
3699 hr_api.mandatory_arg_error
3700 (p_api_name => l_proc,
3701 p_argument => 'datetrack_mode',
3702 p_argument_value => p_datetrack_mode);
3703 --
3704 -- Only perform the validation if the datetrack update mode is valid
3705 --
3706 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
3707 --
3708 --
3709 -- Ensure the arguments are not null
3710 --
3711 hr_api.mandatory_arg_error
3712 (p_api_name => l_proc,
3713 p_argument => 'validation_start_date',
3714 p_argument_value => p_validation_start_date);
3715 --
3716 hr_api.mandatory_arg_error
3717 (p_api_name => l_proc,
3718 p_argument => 'validation_end_date',
3719 p_argument_value => p_validation_end_date);
3720 --
3721 If ((nvl(p_pgm_id, hr_api.g_number) <> hr_api.g_number) and
3722 NOT (dt_api.check_min_max_dates
3723 (p_base_table_name => 'ben_pgm_f',
3724 p_base_key_column => 'pgm_id',
3725 p_base_key_value => p_pgm_id,
3726 p_from_date => p_validation_start_date,
3727 p_to_date => p_validation_end_date))) Then
3728 l_table_name := 'ben_pgm_f';
3729 Raise l_integrity_error;
3730 End If;
3731 If ((nvl(p_oipl_id, hr_api.g_number) <> hr_api.g_number) and
3732 NOT (dt_api.check_min_max_dates
3733 (p_base_table_name => 'ben_oipl_f',
3734 p_base_key_column => 'oipl_id',
3735 p_base_key_value => p_oipl_id,
3736 p_from_date => p_validation_start_date,
3737 p_to_date => p_validation_end_date))) Then
3738 l_table_name := 'ben_oipl_f';
3739 Raise l_integrity_error;
3740 End If;
3741 If ((nvl(p_pl_id, hr_api.g_number) <> hr_api.g_number) and
3742 NOT (dt_api.check_min_max_dates
3743 (p_base_table_name => 'ben_pl_f',
3744 p_base_key_column => 'pl_id',
3745 p_base_key_value => p_pl_id,
3746 p_from_date => p_validation_start_date,
3747 p_to_date => p_validation_end_date))) Then
3748 l_table_name := 'ben_pl_f';
3749 Raise l_integrity_error;
3750 End If;
3751 If ((nvl(p_pl_typ_id, hr_api.g_number) <> hr_api.g_number) and
3752 NOT (dt_api.check_min_max_dates
3753 (p_base_table_name => 'ben_pl_typ_f',
3754 p_base_key_column => 'pl_typ_id',
3755 p_base_key_value => p_pl_typ_id,
3756 p_from_date => p_validation_start_date,
3757 p_to_date => p_validation_end_date))) Then
3758 l_table_name := 'ben_pl_typ_f';
3759 Raise l_integrity_error;
3760 End If;
3761 If ((nvl(p_prtt_enrt_rslt_id, hr_api.g_number) <> hr_api.g_number) and
3762 NOT (dt_api.check_min_max_dates
3763 (p_base_table_name => 'ben_prtt_enrt_rslt_f',
3764 p_base_key_column => 'prtt_enrt_rslt_id',
3765 p_base_key_value => p_prtt_enrt_rslt_id,
3766 p_from_date => p_validation_start_date,
3767 p_to_date => p_validation_end_date))) Then
3768 l_table_name := 'ben_prtt_enrt_rslt_f';
3769 Raise l_integrity_error;
3770 End If;
3771 End If;
3772 --
3773 hr_utility.set_location(' Leaving:'||l_proc, 10);
3774 Exception
3775 When l_integrity_error Then
3776 --
3777 -- A referential integrity check was violated therefore
3778 -- we must error
3779 --
3780 ben_utility.parent_integrity_error(p_table_name => l_table_name);
3781 --
3782 When Others Then
3783 --
3784 -- An unhandled or unexpected error has occurred which
3785 -- we must report
3786 --
3787 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
3788 fnd_message.set_token('PROCEDURE', l_proc);
3789 fnd_message.set_token('STEP','15');
3790 fnd_message.raise_error;
3791 End dt_update_validate;
3792 --
3793 -- ----------------------------------------------------------------------------
3794 -- |--------------------------< dt_delete_validate >--------------------------|
3795 -- ----------------------------------------------------------------------------
3796 -- {Start Of Comments}
3797 --
3798 -- Description:
3799 -- This procedure is used for referential integrity of datetracked
3800 -- child entities when either a datetrack DELETE or ZAP is in operation
3801 -- and where there is no cascading of delete defined for this entity.
3802 -- For the datetrack mode of DELETE or ZAP we must ensure that no
3803 -- datetracked child rows exist between the validation start and end
3804 -- dates.
3805 --
3806 -- Prerequisites:
3807 -- This procedure is called from the delete_validate.
3808 --
3809 -- In Parameters:
3810 --
3811 -- Post Success:
3812 -- Processing continues.
3813 --
3814 -- Post Failure:
3815 -- If a row exists by determining the returning Boolean value from the
3816 -- generic dt_api.rows_exist function then we must supply an error via
3817 -- the use of the local exception handler l_rows_exist.
3818 --
3819 -- Developer Implementation Notes:
3820 -- This procedure should not need maintenance unless the HR Schema model
3821 -- changes.
3822 --
3823 -- Access Status:
3824 -- Internal Row Handler Use Only.
3825 --
3826 -- {End Of Comments}
3827 -- ----------------------------------------------------------------------------
3828 Procedure dt_delete_validate
3829 (p_prtt_enrt_rslt_id in number,
3830 p_datetrack_mode in varchar2,
3831 p_effective_date in date,
3832 p_validation_start_date in date,
3833 p_validation_end_date in date) Is
3834
3835 l_proc varchar2(72) := g_package||'dt_delete_validate';
3836 l_rows_exist Exception;
3837 l_table_name all_tables.table_name%TYPE;
3838
3839 Begin
3840 hr_utility.set_location('Entering:'||l_proc, 5);
3841 --
3842 -- Ensure that the p_datetrack_mode argument is not null
3843 --
3844 hr_api.mandatory_arg_error
3845 (p_api_name => l_proc,
3846 p_argument => 'datetrack_mode',
3847 p_argument_value => p_datetrack_mode);
3848 --
3849 -- Only perform the validation if the datetrack mode is either
3850 -- DELETE or ZAP
3851 --
3852 If (p_datetrack_mode = 'DELETE' or
3853 p_datetrack_mode = 'ZAP') then
3854 --
3855 -- Ensure the arguments are not null
3856 --
3857 hr_api.mandatory_arg_error
3858 (p_api_name => l_proc,
3859 p_argument => 'validation_start_date',
3860 p_argument_value => p_validation_start_date);
3861
3862 hr_api.mandatory_arg_error
3863 (p_api_name => l_proc,
3864 p_argument => 'validation_end_date',
3865 p_argument_value => p_validation_end_date);
3866
3867 hr_api.mandatory_arg_error
3868 (p_api_name => l_proc,
3869 p_argument => 'prtt_enrt_rslt_id',
3870 p_argument_value => p_prtt_enrt_rslt_id);
3871 /*
3872 If (dt_api.rows_exist
3873 (p_base_table_name => 'ben_bnft_prvdd_ldgr_f',
3874 p_base_key_column => 'prtt_enrt_rslt_id',
3875 p_base_key_value => p_prtt_enrt_rslt_id,
3876 p_from_date => p_validation_start_date,
3877 p_to_date => p_validation_end_date)) Then
3878 l_table_name := 'ben_bnft_prvdd_ldgr_f';
3879 Raise l_rows_exist;
3880 End If;
3881 */
3882 If (dt_api.rows_exist
3883 (p_base_table_name => 'ben_pl_bnf_f',
3884 p_base_key_column => 'prtt_enrt_rslt_id',
3885 p_base_key_value => p_prtt_enrt_rslt_id,
3886 p_from_date => p_validation_start_date,
3887 p_to_date => p_validation_end_date)) Then
3888 l_table_name := 'ben_pl_bnf_f';
3889 Raise l_rows_exist;
3890 End If;
3891
3892 If p_effective_date < p_validation_start_date then
3893 -- Added the above condition for bug 3646239
3894 If (dt_api.rows_exist
3895 (p_base_table_name => 'ben_elig_cvrd_dpnt_f',
3896 p_base_key_column => 'prtt_enrt_rslt_id',
3897 p_base_key_value => p_prtt_enrt_rslt_id,
3898 p_from_date => p_validation_start_date,
3899 p_to_date => p_validation_end_date)) Then
3900 l_table_name := 'ben_elig_cvrd_dpnt_f';
3901 Raise l_rows_exist;
3902 End If;
3903 End If;
3904
3905 If (dt_api.rows_exist
3906 (p_base_table_name => 'ben_prtt_enrt_actn_f',
3907 p_base_key_column => 'prtt_enrt_rslt_id',
3908 p_base_key_value => p_prtt_enrt_rslt_id,
3909 p_from_date => p_validation_start_date,
3910 p_to_date => p_validation_end_date)) Then
3911 l_table_name := 'ben_prtt_enrt_actn_f';
3912 Raise l_rows_exist;
3913 End If;
3914 If (dt_api.rows_exist
3915 (p_base_table_name => 'ben_prtt_enrt_ctfn_prvdd_f',
3916 p_base_key_column => 'prtt_enrt_rslt_id',
3917 p_base_key_value => p_prtt_enrt_rslt_id,
3918 p_from_date => p_validation_start_date,
3919 p_to_date => p_validation_end_date)) Then
3920 l_table_name := 'ben_prtt_enrt_ctfn_prvdd_f';
3921 Raise l_rows_exist;
3922 End If;
3923 End If;
3924 --
3925 hr_utility.set_location(' Leaving:'||l_proc, 10);
3926 Exception
3927 When l_rows_exist Then
3928 --
3929 -- A referential integrity check was violated therefore
3930 -- we must error
3931 --
3932 ben_utility.child_exists_error(p_table_name => l_table_name);
3933 --
3934 When Others Then
3935 --
3936 -- An unhandled or unexpected error has occurred which
3937 -- we must report
3938 --
3939 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
3940 fnd_message.set_token('PROCEDURE', l_proc);
3941 fnd_message.set_token('STEP','15');
3942 fnd_message.raise_error;
3943 End dt_delete_validate;
3944 --
3945 -- ----------------------------------------------------------------------------
3946 -- |---------------------------< insert_validate >----------------------------|
3947 -- ----------------------------------------------------------------------------
3948 Procedure insert_validate
3949 (p_rec in ben_pen_shd.g_rec_type,
3950 p_effective_date in date,
3951 p_datetrack_mode in varchar2,
3952 p_validation_start_date in date,
3953 p_validation_end_date in date) is
3954 --
3955 l_proc varchar2(72) := g_package||'insert_validate';
3956 --
3957 Begin
3958 hr_utility.set_location('Entering:'||l_proc, 5);
3959 --
3960 -- Call context sensitive validate bgp cache routine
3961 --
3962 ben_batch_dt_api.batch_validate_bgp_id
3963 (p_business_group_id => p_rec.business_group_id
3964 );
3965 --
3966 /*
3967 --
3968 -- Call all supporting business operations
3969 --
3970 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
3971 */
3972 chk_prtt_enrt_rslt_id
3973 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
3974 p_effective_date => p_effective_date,
3975 p_object_version_number => p_rec.object_version_number);
3976 --
3977 chk_enrt_ovridn_flag
3978 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
3979 p_enrt_ovridn_flag => p_rec.enrt_ovridn_flag,
3980 p_effective_date => p_effective_date,
3981 p_object_version_number => p_rec.object_version_number);
3982 --
3983 chk_no_lngr_elig_flag
3984 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
3985 p_no_lngr_elig_flag => p_rec.no_lngr_elig_flag,
3986 p_effective_date => p_effective_date,
3987 p_object_version_number => p_rec.object_version_number);
3988 --
3989 chk_enrt_mthd_cd
3990 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
3991 p_enrt_mthd_cd => p_rec.enrt_mthd_cd,
3992 p_effective_date => p_effective_date,
3993 p_object_version_number => p_rec.object_version_number);
3994 --
3995 chk_uom
3996 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
3997 p_uom => p_rec.uom ,
3998 p_effective_date => p_effective_date,
3999 p_object_version_number => p_rec.object_version_number);
4000 --
4001 chk_prtt_is_cvrd_flag
4002 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4003 p_prtt_is_cvrd_flag => p_rec.prtt_is_cvrd_flag,
4004 p_effective_date => p_effective_date,
4005 p_object_version_number => p_rec.object_version_number);
4006 --
4007 chk_sspndd_flag
4008 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4009 p_sspndd_flag => p_rec.sspndd_flag,
4010 p_effective_date => p_effective_date,
4011 p_object_version_number => p_rec.object_version_number);
4012 --
4013 chk_comp_lvl_cd
4014 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4015 p_comp_lvl_cd => p_rec.comp_lvl_cd,
4016 p_effective_date => p_effective_date,
4017 p_object_version_number => p_rec.object_version_number);
4018
4019 --
4020 chk_bnft_nnmntry_uom
4021 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4022 p_bnft_nnmntry_uom => p_rec.bnft_nnmntry_uom,
4023 p_effective_date => p_effective_date,
4024 p_object_version_number => p_rec.object_version_number);
4025 --
4026 chk_bnft_typ_cd
4027 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4028 p_bnft_typ_cd => p_rec.bnft_typ_cd,
4029 p_effective_date => p_effective_date,
4030 p_object_version_number => p_rec.object_version_number);
4031 --
4032 chk_prtt_enrt_rslt_stat_cd
4033 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4034 p_prtt_enrt_rslt_stat_cd => p_rec.prtt_enrt_rslt_stat_cd,
4035 p_effective_date => p_effective_date,
4036 p_object_version_number => p_rec.object_version_number);
4037 --
4038 --
4039 chk_enrt_ovrid_rsn_cd
4040 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4041 p_enrt_ovrid_rsn_cd => p_rec.enrt_ovrid_rsn_cd,
4042 p_effective_date => p_effective_date,
4043 p_object_version_number => p_rec.object_version_number);
4044 --
4045 --
4046 -- RCHASE - Bug#1412801 - Moved post election edit rule from insert/update
4047 -- RCHASE validation calls to multi-row edit
4048 --chk_post_elcn_rl
4049 --(p_oipl_id => p_rec.oipl_id,
4050 -- p_pl_id => p_rec.pl_id,
4051 -- p_ptip_id => p_rec.ptip_id,
4052 -- p_pgm_id => p_rec.pgm_id,
4053 -- p_pl_typ_id => p_rec.pl_typ_id,
4054 -- p_person_id => p_rec.person_id,
4055 -- p_effective_date => p_effective_date,
4056 -- p_business_group_id => p_rec.business_group_id);
4057 --
4058 crt_ordr_warning
4059 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id
4060 ,p_per_in_ler_id => p_rec.per_in_ler_id /* Bug 4766655 */
4061 ,p_person_id => p_rec.person_id
4062 ,p_pgm_id => p_rec.pgm_id
4063 ,p_pl_id => p_rec.pl_id
4064 ,p_ptip_id => p_rec.ptip_id
4065 ,p_pl_typ_id => p_rec.pl_typ_id
4066 ,p_effective_date => p_effective_date
4067 ,p_enrt_cvg_strt_dt => p_rec.enrt_cvg_strt_dt
4068 ,p_enrt_cvg_thru_dt => p_rec.enrt_cvg_thru_dt
4069 ,p_business_group_id => p_rec.business_group_id);
4070 --
4071 /*chk_cvg_strt_end_dt
4072 (p_enrt_cvg_strt_dt => p_rec.enrt_cvg_strt_dt,
4073 p_enrt_cvg_thru_dt => p_rec.enrt_cvg_thru_dt,
4074 p_person_id => p_rec.person_id);*/
4075 --
4076 hr_utility.set_location(' Leaving:'||l_proc, 10);
4077 End insert_validate;
4078 --
4079 -- ----------------------------------------------------------------------------
4080 -- |---------------------------< update_validate >----------------------------|
4081 -- ----------------------------------------------------------------------------
4082 Procedure update_validate
4083 (p_rec in ben_pen_shd.g_rec_type,
4084 p_effective_date in date,
4085 p_datetrack_mode in varchar2,
4086 p_validation_start_date in date,
4087 p_validation_end_date in date) is
4088 --
4089 l_proc varchar2(72) := g_package||'update_validate';
4090 --
4091 Begin
4092 hr_utility.set_location('Entering:'||l_proc, 5);
4093 --
4094 -- Call context sensitive validate bgp cache routine
4095 --
4096 ben_batch_dt_api.batch_validate_bgp_id
4097 (p_business_group_id => p_rec.business_group_id
4098 );
4099 --
4100 /*
4101 --
4102 -- Call all supporting business operations
4103 --
4104 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
4105 */
4106 chk_prtt_enrt_rslt_id
4107 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4108 p_effective_date => p_effective_date,
4109 p_object_version_number => p_rec.object_version_number);
4110 --
4111 chk_enrt_ovridn_flag
4112 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4113 p_enrt_ovridn_flag => p_rec.enrt_ovridn_flag,
4114 p_effective_date => p_effective_date,
4115 p_object_version_number => p_rec.object_version_number);
4116 --
4117 chk_no_lngr_elig_flag
4118 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4119 p_no_lngr_elig_flag => p_rec.no_lngr_elig_flag,
4120 p_effective_date => p_effective_date,
4121 p_object_version_number => p_rec.object_version_number);
4122 --
4123 chk_enrt_mthd_cd
4124 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4125 p_enrt_mthd_cd => p_rec.enrt_mthd_cd,
4126 p_effective_date => p_effective_date,
4127 p_object_version_number => p_rec.object_version_number);
4128 --
4129 chk_uom
4130 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4131 p_uom => p_rec.uom ,
4132 p_effective_date => p_effective_date,
4133 p_object_version_number => p_rec.object_version_number);
4134 --
4135 chk_prtt_is_cvrd_flag
4136 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4137 p_prtt_is_cvrd_flag => p_rec.prtt_is_cvrd_flag,
4138 p_effective_date => p_effective_date,
4139 p_object_version_number => p_rec.object_version_number);
4140 --
4141 chk_sspndd_flag
4142 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4143 p_sspndd_flag => p_rec.sspndd_flag,
4144 p_effective_date => p_effective_date,
4145 p_object_version_number => p_rec.object_version_number);
4146 --
4147 chk_comp_lvl_cd
4148 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4149 p_comp_lvl_cd => p_rec.comp_lvl_cd,
4150 p_effective_date => p_effective_date,
4151 p_object_version_number => p_rec.object_version_number);
4152
4153 chk_bnft_nnmntry_uom
4154 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4155 p_bnft_nnmntry_uom => p_rec.bnft_nnmntry_uom,
4156 p_effective_date => p_effective_date,
4157 p_object_version_number => p_rec.object_version_number);
4158 --
4159 chk_bnft_typ_cd
4160 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4161 p_bnft_typ_cd => p_rec.bnft_typ_cd,
4162 p_effective_date => p_effective_date,
4163 p_object_version_number => p_rec.object_version_number);
4164 --
4165 chk_prtt_enrt_rslt_stat_cd
4166 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4167 p_prtt_enrt_rslt_stat_cd => p_rec.prtt_enrt_rslt_stat_cd,
4168 p_effective_date => p_effective_date,
4169 p_object_version_number => p_rec.object_version_number);
4170 --
4171 --
4172 chk_enrt_ovrid_rsn_cd
4173 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4174 p_enrt_ovrid_rsn_cd => p_rec.enrt_ovrid_rsn_cd,
4175 p_effective_date => p_effective_date,
4176 p_object_version_number => p_rec.object_version_number);
4177 --
4178 -- RCHASE - Bug#1412801 - Moved post election edit rule from insert/update
4179 -- RCHASE validation calls to multi-row edit
4180 --chk_post_elcn_rl
4181 --(p_oipl_id => p_rec.oipl_id,
4182 -- p_pl_id => p_rec.pl_id,
4183 -- p_ptip_id => p_rec.ptip_id,
4184 -- p_pgm_id => p_rec.pgm_id,
4185 -- p_pl_typ_id => p_rec.pl_typ_id,
4186 -- p_person_id => p_rec.person_id,
4187 -- p_effective_date => p_effective_date,
4188 -- p_business_group_id => p_rec.business_group_id);
4189 --
4190 crt_ordr_warning
4191 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id
4192 ,p_per_in_ler_id => p_rec.per_in_ler_id /* Bug 4766655 */
4193 ,p_person_id => p_rec.person_id
4194 ,p_pgm_id => p_rec.pgm_id
4195 ,p_pl_id => p_rec.pl_id
4196 ,p_ptip_id => p_rec.ptip_id
4197 ,p_pl_typ_id => p_rec.pl_typ_id
4198 ,p_effective_date => p_effective_date
4199 ,p_enrt_cvg_strt_dt => p_rec.enrt_cvg_strt_dt
4200 ,p_enrt_cvg_thru_dt => p_rec.enrt_cvg_thru_dt
4201 ,p_business_group_id => p_rec.business_group_id);
4202 --
4203 --
4204 -- Call the datetrack update integrity operation
4205 --
4206 dt_update_validate
4207 (p_pgm_id => p_rec.pgm_id,
4208 p_oipl_id => p_rec.oipl_id,
4209 p_pl_id => p_rec.pl_id,
4210 p_per_in_ler_id => p_rec.per_in_ler_id,
4211 p_pl_typ_id => p_rec.pl_typ_id,
4212 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4213 p_datetrack_mode => p_datetrack_mode,
4214 p_validation_start_date => p_validation_start_date,
4215 p_validation_end_date => p_validation_end_date);
4216 --
4217 /*chk_cvg_strt_end_dt
4218 (p_enrt_cvg_strt_dt => p_rec.enrt_cvg_strt_dt,
4219 p_enrt_cvg_thru_dt => p_rec.enrt_cvg_thru_dt,
4220 p_person_id => p_rec.person_id);*/
4221 --
4222 hr_utility.set_location(' Leaving:'||l_proc, 10);
4223 End update_validate;
4224 --
4225 -- ----------------------------------------------------------------------------
4226 -- |---------------------------< delete_validate >----------------------------|
4227 -- ----------------------------------------------------------------------------
4228 Procedure delete_validate
4229 (p_rec in ben_pen_shd.g_rec_type,
4230 p_effective_date in date,
4231 p_datetrack_mode in varchar2,
4232 p_validation_start_date in date,
4233 p_validation_end_date in date) is
4234 --
4235 l_proc varchar2(72) := g_package||'delete_validate';
4236 --
4237 Begin
4238 hr_utility.set_location('Entering:'||l_proc, 5);
4239 --
4240 -- Call all supporting business operations
4241 --
4242 dt_delete_validate
4243 (p_datetrack_mode => p_datetrack_mode,
4244 p_validation_start_date => p_validation_start_date,
4245 p_validation_end_date => p_validation_end_date,
4246 p_effective_date => p_effective_date, -- Added for bug 3646239
4247 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id);
4248 --
4249 hr_utility.set_location(' Leaving:'||l_proc, 10);
4250 End delete_validate;
4251 --
4252 --
4253 -- ---------------------------------------------------------------------------
4254 -- |---------------------< return_legislation_code >-------------------------|
4255 -- ---------------------------------------------------------------------------
4256 --
4257 function return_legislation_code
4258 (p_prtt_enrt_rslt_id in number) return varchar2 is
4259 --
4260 -- Declare cursor
4261 --
4262 cursor csr_leg_code is
4263 select a.legislation_code
4264 from per_business_groups a,
4265 ben_prtt_enrt_rslt_f b
4266 where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4267 and a.business_group_id = b.business_group_id;
4268 --
4269 -- Declare local variables
4270 --
4271 l_legislation_code varchar2(150);
4272 l_proc varchar2(72) := g_package||'return_legislation_code';
4273 --
4274 begin
4275 --
4276 hr_utility.set_location('Entering:'|| l_proc, 10);
4277 --
4278 -- Ensure that all the mandatory parameter are not null
4279 --
4280 hr_api.mandatory_arg_error(p_api_name => l_proc,
4281 p_argument => 'prtt_enrt_rslt_id',
4282 p_argument_value => p_prtt_enrt_rslt_id);
4283 --
4284 open csr_leg_code;
4285 --
4286 fetch csr_leg_code into l_legislation_code;
4287 --
4288 if csr_leg_code%notfound then
4289 --
4290 close csr_leg_code;
4291 --
4292 -- The primary key is invalid therefore we must error
4293 --
4294 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
4295 fnd_message.raise_error;
4296 --
4297 end if;
4298 --
4299 close csr_leg_code;
4300 --
4301 hr_utility.set_location(' Leaving:'|| l_proc, 20);
4302 --
4303 return l_legislation_code;
4304 --
4305 end return_legislation_code;
4306 --
4307 end ben_pen_bus;