[Home] [Help]
PACKAGE BODY: APPS.BEN_PEN_BUS
Source
1 Package Body ben_pen_bus as
2 /* $Header: bepenrhi.pkb 120.21.12010000.2 2008/08/05 15:11:10 ubhat 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);
1198 l_step integer;
1199 -- 3427367
1200 cursor c_epe is
1201 select elig_per_elctbl_chc_id
1202 from ben_elig_per_elctbl_chc
1203 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
1204 --
1205 l_jurisdiction_code varchar2(30);
1206 l_elig_per_elctbl_chc_id number;
1207 -- 3427367
1208 begin
1209 hr_utility.set_location ('Entering '||l_proc,10);
1210 --
1211 -- Call formula initialise routine
1212 --
1213 -- 3427367
1214 open c_epe;
1215 fetch c_epe into l_elig_per_elctbl_chc_id;
1216 close c_epe;
1217 -- 3427367
1218 l_step := 20;
1219 l_outputs := benutils.formula
1220 (p_formula_id => p_mx_cvg_rl
1221 -- 3427367
1222 ,p_business_group_id => p_business_group_id
1223 ,p_organization_id => l_elig_per_elctbl_chc_id
1224 ,p_pgm_id => p_pgm_id
1225 ,p_pl_id => p_pl_id
1226 ,p_pl_typ_id => p_pl_typ_id
1227 ,p_opt_id => p_opt_id
1228 ,p_ler_id => p_ler_id
1229 ,p_jurisdiction_code => l_jurisdiction_code
1230 -- 3427367
1231 ,p_effective_date => p_effective_date
1232 ,p_assignment_id => p_assignment_id
1233 ,p_param1 => 'BEN_IV_PERSON_ID' -- Bug 5331889
1234 ,p_param1_value => to_char(p_person_id));
1235 --
1236 -- Formula will return Y or N
1237 --
1238 l_return := to_number(l_outputs(l_outputs.first).value);
1239
1240 return l_return;
1241 hr_utility.set_location ('Leaving '||l_proc,50);
1242 exception
1243 when others then
1244 hr_utility.set_location ('Fail in '||l_proc|| ' step in '||
1245 to_char(l_step),999);
1246 raise;
1247 end;
1248 --
1249 -- -------------------------------------------------------------------------
1250 -- |--------------------------< calc_mn_amt >------------------------------|
1251 -- -------------------------------------------------------------------------
1252 --
1253 function calc_mn_amt(p_mn_cvg_rl number
1254 ,p_assignment_id number
1255 ,p_effective_date date
1256 -- 3427367
1257 ,p_business_group_id number
1258 ,p_pgm_id number
1259 ,p_pl_id number
1260 ,p_pl_typ_id number
1261 ,p_opt_id number
1262 ,p_ler_id number
1263 ,p_prtt_enrt_rslt_id number --3427367
1264 ,p_person_id number -- 5331889
1265 ) return number is
1266
1267 l_proc varchar2(80) := g_package||'.calc_mn_amt';
1268 l_outputs ff_exec.outputs_t;
1269 l_return number(15);
1270 l_step integer;
1271 -- 3427367
1272 cursor c_epe is
1273 select elig_per_elctbl_chc_id
1274 from ben_elig_per_elctbl_chc
1275 where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
1276 --
1277 l_jurisdiction_code varchar2(30);
1278 l_elig_per_elctbl_chc_id number;
1279 -- 3427367
1280 begin
1281 hr_utility.set_location ('Entering '||l_proc,70);
1282 --
1283 -- Call formula initialise routine
1284 --
1285 -- 3427367
1286 open c_epe;
1287 fetch c_epe into l_elig_per_elctbl_chc_id;
1288 close c_epe;
1289 -- 3427367
1290 l_step := 20;
1291 l_outputs := benutils.formula
1292 (p_formula_id => p_mn_cvg_rl
1293 -- 3427367
1294 ,p_business_group_id => p_business_group_id
1295 ,p_organization_id => l_elig_per_elctbl_chc_id
1296 ,p_pgm_id => p_pgm_id
1297 ,p_pl_id => p_pl_id
1298 ,p_pl_typ_id => p_pl_typ_id
1299 ,p_opt_id => p_opt_id
1300 ,p_ler_id => p_ler_id
1301 ,p_jurisdiction_code => l_jurisdiction_code
1302 -- 3427367
1303 ,p_effective_date => p_effective_date
1304 ,p_assignment_id => p_assignment_id
1305 ,p_param1 => 'BEN_IV_PERSON_ID' -- Bug 5331889
1306 ,p_param1_value => to_char(p_person_id));
1307 --
1308 -- Formula will return Y or N
1309 --
1310 l_return := to_number(l_outputs(l_outputs.first).value);
1311
1312 return l_return;
1313 hr_utility.set_location ('Leaving '||l_proc,70);
1314 exception
1315 when others then
1316 hr_utility.set_location ('Fail in '||l_proc || ' step in ' ||
1317 to_char(l_step),999);
1318 raise;
1319 end;
1320 --
1321 -- ----------------------------------------------------------------------------
1322 -- |--------------------------<get_opts_and_cvg >-----------------------------|
1323 -- ----------------------------------------------------------------------------
1324 --
1325 procedure get_opts_and_cvg
1326 (p_enrt_tbl in enrt_table
1327 ,p_enrt_cnt in binary_integer
1328 ,p_tot_amt out nocopy number
1329 ,p_tot_opts out nocopy number
1330 ) is
1331 l_tot_amt number := 0; -- number(13) := 0;
1332 l_tot_opts number := 0; -- number(8) := 0; bug 2649163
1333 l_max_amt number := 0; -- number(13) := 0;
1334 l_max_opts number := 0; -- number(8) := 0; bug 2649163
1335 l_tot_amt_no_interim number := 0;
1336 l_max_amt_no_interim number := 0;
1337 begin
1338 hr_utility.set_location('Entering get_opts_and_cvg ',1234);
1339 hr_utility.set_location(' p_enrt_cnt '||p_enrt_cnt,1234);
1340 for i in 1..p_enrt_cnt loop
1341 l_tot_amt := 0;
1342 l_tot_opts := 0;
1343 l_tot_amt_no_interim := 0 ;
1344 for j in 1..p_enrt_cnt loop
1345 if (p_enrt_tbl(i).enrt_cvg_strt_dt between
1346 p_enrt_tbl(j).enrt_cvg_strt_dt and
1347 nvl(p_enrt_tbl(j).enrt_cvg_thru_dt,hr_api.g_eot)
1348 ) then
1349 hr_utility.set_location(' j '||j,1234);
1350 if (p_enrt_tbl(j).calc_interm = 1) then
1351 if (nvl(p_enrt_tbl(j).SSPNDD_FLAG, 'X') <> 'Y') then
1352 hr_utility.set_location(' l_tot_amt before '||l_tot_amt,1234);
1353 l_tot_amt := l_tot_amt+nvl(p_enrt_tbl(j).bnft_amt,0);
1354 hr_utility.set_location(' l_tot_amt after '||l_tot_amt,1234);
1355 end if;
1356 else
1357 if p_enrt_tbl(j).interim_flag = 'N' then
1358 hr_utility.set_location(' interim_flag before '||l_tot_amt,1234);
1359 l_tot_amt := l_tot_amt + nvl(p_enrt_tbl(j).bnft_amt,0);
1360 hr_utility.set_location(' interim_flag after '||l_tot_amt,1234);
1361 end if;
1362 end if;
1363 if (p_enrt_tbl(j).interim_flag = 'N') then
1364 l_tot_opts := l_tot_opts + 1;
1365 l_tot_amt_no_interim:=l_tot_amt_no_interim+nvl(p_enrt_tbl(j).bnft_amt,0);
1366 end if;
1367 end if;
1368 end loop;
1369 hr_utility.set_location(' before l_max_amt '||l_max_amt,1234);
1370 l_max_amt := greatest(l_tot_amt, l_max_amt);
1371 l_max_opts := greatest(l_max_opts,l_tot_opts);
1372 l_max_amt_no_interim := greatest(l_max_amt_no_interim,l_tot_amt_no_interim);
1373 hr_utility.set_location(' after l_max_amt '||l_max_amt,1234);
1374 end loop;
1375 hr_utility.set_location(' before p_tot_amt '||p_tot_amt,1234);
1376 p_tot_amt := l_max_amt;
1377 p_tot_opts := l_max_opts;
1378 --p_tot_amt_no_interim := l_tot_amt_no_interim;
1379 hr_utility.set_location(' after p_tot_amt '||p_tot_amt,1234);
1380 end get_opts_and_cvg;
1381 --
1382 -- ----------------------------------------------------------------------------
1383 -- |--------------------------<get_pls_and_cvg >-----------------------------|
1384 -- ----------------------------------------------------------------------------
1385 --
1386 procedure get_pls_and_cvg
1387 (p_enrt_tbl in enrt_table
1388 ,p_enrt_cnt in binary_integer
1389 ,p_tot_amt out nocopy number
1390 ,p_tot_pls out nocopy number
1391 ,p_dpnt_cvd_by_othr_apls_flag out nocopy varchar2
1392 ,p_tot_amt_no_interim out nocopy number
1393 ) is
1394 type l_pl_table is table of number(15) index by binary_integer;
1395 l_tot_amt number := 0; -- number(13) := 0; bug 2649163
1396 l_tot_pls number := 0; -- number(8) := 0; bug 2649163
1397 l_max_amt number := 0; -- number(13) := 0; bug 2649163
1398 l_max_pls number := 0; -- number(8) := 0; bug 2649163
1399 -- l_cnt Binary_integer := 0;
1400 -- l_pl_tbl l_pl_table;
1401 -- l_not_found boolean := TRUE;
1402 l_tot_amt_no_interim number := 0;
1403 l_max_amt_no_interim number := 0;
1404 begin
1405 --
1406 p_dpnt_cvd_by_othr_apls_flag := 'N';
1407 --
1408 for i in 1..p_enrt_cnt loop
1409 /*
1410 hr_utility.set_location('i INSIDE p_enrt_tbl(i).pl_id'||p_enrt_tbl(i).pl_id,111);
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 SSPNDD_FLAG'||p_enrt_tbl(i).SSPNDD_FLAG,111);
1413 hr_utility.set_location('i INSIDE Bnft I'||p_enrt_tbl(i).bnft_amt,111);
1414 */
1415 l_tot_amt := 0;
1416 l_tot_pls := 0;
1417 l_tot_amt_no_interim := 0 ;
1418 --l_pl_tbl.delete;
1419 --l_cnt := 0;
1420 for j in 1..p_enrt_cnt loop
1421 --
1422 hr_utility.set_location('J INSIDE p_enrt_tbl(j).pl_id'||p_enrt_tbl(j).pl_id,111);
1423 hr_utility.set_location('J INSIDE p_enrt_tbl(i).pl_id'||p_enrt_tbl(i).pl_id,111);
1424 hr_utility.set_location('J INSIDE SSPNDD_FLAG'||p_enrt_tbl(j).SSPNDD_FLAG,111);
1425 hr_utility.set_location('i INSIDE Bnft J '||p_enrt_tbl(j).bnft_amt,111);
1426 hr_utility.set_location('i INSIDE interim J'||p_enrt_tbl(j).interim_flag,111);
1427 --
1428 if (p_enrt_tbl(i).enrt_cvg_strt_dt between
1429 p_enrt_tbl(j).enrt_cvg_strt_dt and
1430 nvl(p_enrt_tbl(j).enrt_cvg_thru_dt,hr_api.g_eot)
1431 ) then
1432 --
1433 /*
1434 hr_utility.set_location('INSIDE p_enrt_tbl(j).pl_id'||p_enrt_tbl(j).pl_id,111);
1435 hr_utility.set_location('INSIDE p_enrt_tbl(i).pl_id'||p_enrt_tbl(i).pl_id,111);
1436 hr_utility.set_location('INSIDE SSPNDD_FLAG'||p_enrt_tbl(j).SSPNDD_FLAG,111);
1437 hr_utility.set_location('INSIDE calc_inter'||p_enrt_tbl(j).calc_interm,111);
1438 */
1439 --
1440 if (p_enrt_tbl(j).calc_interm = 1) then
1441 if (nvl(p_enrt_tbl(j).SSPNDD_FLAG, 'X') <> 'Y') then
1442 l_tot_amt := l_tot_amt+nvl(p_enrt_tbl(j).bnft_amt,0);
1443 end if;
1444 else
1445 if p_enrt_tbl(j).interim_flag = 'N' then
1446 l_tot_amt := l_tot_amt + nvl(p_enrt_tbl(j).bnft_amt,0);
1447 end if;
1448 end if;
1449 if (p_enrt_tbl(j).interim_flag = 'N') then
1450 -- Bug 1178690 comment out the pl_id check
1451 -- l_not_found := TRUE;
1452 -- for l in 1..l_cnt loop
1453 -- if (p_enrt_tbl(j).pl_id = l_pl_tbl(l)) then
1454 -- l_not_found := FALSE;
1455 -- exit;
1456 -- end if;
1457 -- end loop;
1458 -- if (l_not_found) then
1459 --
1460 /*
1461 hr_utility.set_location('p_enrt_tbl(j).pl_id'||p_enrt_tbl(j).pl_id,111);
1462 hr_utility.set_location('p_enrt_tbl(i).pl_id'||p_enrt_tbl(i).pl_id,111);
1463 hr_utility.set_location('enrt_tbl(j).bnft_amt'||nvl(p_enrt_tbl(j).bnft_amt,0),111);
1464 hr_utility.set_location('l_tot_amt_no_interim'||l_tot_amt_no_interim,111);
1465 */
1466 --
1467 l_tot_pls := l_tot_pls + 1;
1468 l_tot_amt_no_interim:=l_tot_amt_no_interim+nvl(p_enrt_tbl(j).bnft_amt,0);
1469 if p_enrt_tbl(j).dpnt_cvd_by_othr_apls_flag = 'Y' then
1470 p_dpnt_cvd_by_othr_apls_flag := 'Y';
1471 end if;
1472 -- l_cnt := l_cnt + 1;
1473 -- l_pl_tbl(l_cnt) := p_enrt_tbl(j).pl_id;
1474 -- end if;
1475 end if;
1476 end if;
1477 end loop;
1478 l_max_amt := greatest(l_tot_amt, l_max_amt);
1479 l_max_pls := greatest(l_max_pls,l_tot_pls);
1480 l_max_amt_no_interim := greatest(l_max_amt_no_interim,l_tot_amt_no_interim); /* Bug 4309146 Removed typo */
1481 end loop;
1482 p_tot_amt := l_max_amt;
1483 p_tot_pls := l_max_pls;
1484 p_tot_amt_no_interim := l_max_amt_no_interim ;
1485 end get_pls_and_cvg;
1486 --
1487 -- ----------------------------------------------------------------------------
1488 -- |------<cache_enrt_info >------|
1489 -- ----------------------------------------------------------------------------
1490 --
1491 -- Description
1492 -- This procedure is used to cached all programs, plan types, plans for
1493 -- a specified person_id..
1494 --
1495 -- Pre Conditions
1496 -- None.
1497 --
1498 -- In Parameters
1499 -- person_id Person ID
1500 -- effective_date effective date
1501 --
1502 -- Post Success
1503 -- Processing continues
1504 --
1505 -- Post Failure
1506 -- Error handled by procedure
1507 --
1508 -- Access Status
1509 -- Internal table handler use only.
1510 --
1511 Procedure cache_enrt_info
1512 (p_effective_date in date
1513 ,p_business_group_id in number
1514 ,p_person_id in number
1515 ,p_pgm_id in number
1516 ,p_assignment_id in number
1517 ,p_include_erl in varchar2
1518 )
1519 is
1520 --
1521 l_dup_ptip_id_list ben_cache.IdType;
1522 l_dup_pl_typ_id_list ben_cache.IdType;
1523 l_dup_pl_id_list ben_cache.IdType;
1524 --
1525 l_plnrow ben_cobj_cache.g_pl_inst_row;
1526 l_oiplrow ben_cobj_cache.g_oipl_inst_row;
1527 --
1528 -- Cursor Declaration.
1529 --
1530 -- Previous enrollment means previous enrollment, so we check that the
1531 -- per_in_ler_id is different.
1532 -- BUG 3695079 fixes. This cursor never returns any rows as the current
1533 -- datet rack peice always has the new PIL. We need to get the DT Peice
1534 -- of effective_date-1 when looking along with PIL
1535 --
1536 -- p_effective_date is the lf_evt_ocrd_dt. Please keep this in mind
1537 -- before making further changes to this cursor.
1538 --
1539 cursor l_c_prev_pl(p_pl_id number) is
1540 select nvl(sum(pen.bnft_amt),0)
1541 from ben_prtt_enrt_rslt_f pen,
1542 ben_per_in_ler pil
1543 where pen.person_id = p_person_id
1544 and pen.prtt_enrt_rslt_stat_cd is null
1545 and pen.pl_id = p_pl_id
1546 and pil.person_id = p_person_id
1547 and nvl(pen.per_in_ler_id, pil.per_in_ler_id) <> pil.per_in_ler_id
1548 and pil.per_in_ler_stat_cd = 'STRTD'
1549 and pen.business_group_id = p_business_group_id
1550 and pen.enrt_cvg_strt_dt < pen.effective_end_date
1551 and (p_effective_date-1) between pen.enrt_cvg_strt_dt
1552 and pen.enrt_cvg_thru_dt
1553 and pen.effective_end_date = ( select max(pen1.effective_start_date - 1)
1554 from ben_prtt_enrt_rslt_f pen1
1555 where pen1.per_in_ler_id = pil.per_in_ler_id
1556 and pen1.prtt_enrt_rslt_stat_cd is NULL
1557 and pen1.pl_id = p_pl_id
1558 and pen1.enrt_cvg_thru_dt = hr_api.g_eot
1559 and pen1.person_id = p_person_id ) ;
1560 --
1561 --Bug 2715942 fixes
1562 --
1563 -- to get the ler_id of the STRTD per_in_ler
1564 --
1565 cursor c_ler(cv_pl_id number) is
1566 select pil.ler_id
1567 from ben_per_in_ler pil,
1568 ben_prtt_enrt_rslt_f pen
1569 where pil.person_id = p_person_id
1570 and pil.per_in_ler_stat_cd = 'STRTD'
1571 and pil.per_in_ler_id = pen.per_in_ler_id
1572 and pen.person_id = p_person_id
1573 and pen.pl_id = cv_pl_id
1574 and pen.prtt_enrt_rslt_stat_cd is null ;
1575 --
1576 cursor c_ler_rstrn(cv_pl_id number, cv_ler_id number) is
1577 select rstrn.pl_id,
1578 rstrn.mx_cvg_wcfn_amt,
1579 rstrn.mx_cvg_incr_alwd_amt,
1580 rstrn.mx_cvg_incr_wcf_alwd_amt,
1581 rstrn.mn_cvg_amt mn_cvg_rqd_amt,
1582 rstrn.mx_cvg_alwd_amt,
1583 rstrn.mx_cvg_rl,
1584 rstrn.mn_cvg_rl
1585 from ben_ler_bnft_rstrn_f rstrn
1586 where rstrn.ler_id = cv_ler_id
1587 and rstrn.pl_id = cv_pl_id
1588 and p_effective_date
1589 between rstrn.effective_start_date
1590 and rstrn.effective_end_date;
1591 --bug#3480144
1592
1593 cursor other_pgm_enrolled
1594 (c_effective_date date
1595 ,c_business_group_id number
1596 ,c_person_id number
1597 ,c_pgm_id number
1598 )
1599 is
1600 select pen.pgm_id,
1601 pen.ptip_id,
1602 pen.pl_typ_id,
1603 NVL(epe.plip_id, cpp.plip_id) plip_id,
1604 pen.pl_id,
1605 pen.oipl_id,
1606 pen.enrt_cvg_strt_dt,
1607 pen.enrt_cvg_thru_dt,
1608 pen.prtt_enrt_rslt_id,
1609 pen.RPLCS_SSPNDD_RSLT_ID,
1610 pen.SSPNDD_FLAG,
1611 'N' interim_flag,
1612 pen.person_id,
1613 0 Calc_interm,
1614 nvl(pen.bnft_amt,0) bnft_amt,
1615 pen.uom,
1616 epe.elig_per_elctbl_chc_id,
1617 epe.MUST_ENRL_ANTHR_PL_ID,
1618 'N' dpnt_cvd_by_othr_apls_flag,
1619 -9999999999999999999999999999999999999 opt_id
1620 from ben_prtt_enrt_rslt_f pen,
1621 ben_elig_per_elctbl_chc epe,
1622 ben_plip_f cpp,
1623 ben_oipl_f cop,
1624 ben_pl_f pln
1625 where pen.person_id = c_person_id
1626 and pen.prtt_enrt_rslt_stat_cd is null
1627 and pen.pgm_id <> c_pgm_id
1628 and pen.effective_end_date = hr_api.g_eot
1629 and pen.enrt_cvg_thru_dt = hr_api.g_eot
1630 and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id (+)
1631 and pen.per_in_ler_id = epe.per_in_ler_id (+)
1632 and pen.comp_lvl_cd not in ('PLANFC','PLANIMP')
1633 and exists (select null
1634 from ben_per_in_ler pil
1635 where pil.per_in_ler_id = epe.per_in_ler_id
1636 and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT'))
1637 and cpp.pgm_id = pen.pgm_id
1638 and cpp.pl_id = pen.pl_id
1639 and cpp.business_group_id = c_business_group_id
1640 and c_effective_date between cpp.effective_start_date and cpp.effective_end_date
1641 and pen.oipl_id = cop.oipl_id (+)
1642 and c_effective_date
1643 between cop.effective_start_date (+) and cop.effective_end_date (+)
1644 and pen.pl_id = pln.pl_id
1645 and c_effective_date
1646 between pln.effective_start_date and pln.effective_end_date
1647 order by 1,2,3,4,5,6,7;
1648 --
1649 l_ler_rstrn c_ler_rstrn%rowtype ;
1650 --
1651 -- Type declaration.
1652 --
1653 type interim_table is table of ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%type
1654 index by binary_integer;
1655 --
1656 -- Local Variable declaration
1657 --
1658 l_prev_pgm_id ben_pgm_f.pgm_id%type := -99999;
1659 l_prev_ptip_id ben_ptip_f.ptip_id%type := -99999;
1660 l_prev_pl_typ_id ben_pl_typ_f.pl_typ_id%type := -99999;
1661 l_prev_pl_id ben_pl_f.pl_id%type := -99999;
1662 l_pl_typ_rec g_c_pl_typ%rowtype;
1663 l_interim_tbl interim_table;
1664 l_interim_cnt binary_integer := 0;
1665 i binary_integer;
1666 j binary_integer;
1667 l_interim_calc Boolean := FALSE;
1668 l_proc varchar2(72) := g_package||'cache_enrt_info';
1669 l_step integer;
1670 l_cnt integer := 0;
1671 l_frst_susp boolean := FALSE;
1672 l_enrt_tbl enrt_table;
1673 l_enrt_cnt binary_integer := 0;
1674 l_ler_id number ; --Bug 2715942
1675 l_enrt_tbl2 enrt_table;
1676 --
1677 begin
1678 --
1679 -- Store all comp. object into PL/SQL table g_enrt_tbl, and all the
1680 -- interim records prtt_enrt_rslt_id into l_interim_tbl PL/SQL table.
1681 --
1682 l_step := 5;
1683 hr_utility.set_location('Entering:'||l_proc,5);
1684 --
1685 -- Initialize globle variable.
1686 --
1687 l_interim_tbl.delete;
1688 g_enrt_tbl.delete;
1689 g_comp_obj_cnt := 0;
1690 g_pl_tbl.delete;
1691 g_pl_cnt := 0;
1692 g_pl_typ_tbl.delete;
1693 g_pl_typ_cnt := 0;
1694 g_ptip_tbl.delete;
1695 g_ptip_cnt := 0;
1696 g_tot_ee_lf_ins_amt := 0;
1697 g_tot_sps_lf_ins_amt := 0;
1698 g_tot_dpnt_lf_ins_amt := 0;
1699
1700 g_tot_ee_lf_ins_amt_no := 0;
1701 g_tot_sps_lf_ins_amt_no := 0;
1702 g_tot_dpnt_lf_ins_amt_no := 0;
1703
1704 g_mx_dpnt_pct_prtt_lf := 0;
1705 g_mx_sps_pct_prtt_lf := 0;
1706 --
1707 -- Open cursor.
1708 --
1709 -- The following loop stores all of the comp objects which are enrolled
1710 -- in as well as comp objects which are choices but are not enrolled in
1711 -- (I have just added the union to the g_c1 cursor for these choices)
1712 -- these rows are stored in the g_enrt_tbl to be used multiple times
1713 -- later on to summ up certain things. The approach is:
1714 -- 1) get comp objects store in g_enrt_tbl
1715 -- 2) initialize some special non-db fields
1716 -- 3) get unique pl, ptip, pl_typ records and put in cache tables
1717 -- 4) total/count up the numbers
1718 --
1719 open g_enrolled
1720 (c_effective_date => p_effective_date
1721 ,c_business_group_id => p_business_group_id
1722 ,c_person_id => p_person_id
1723 ,c_pgm_id => nvl(p_pgm_id, -999999)
1724 ,c_include_erl => p_include_erl
1725 );
1726 loop
1727 fetch g_enrolled into g_enrt_tbl(g_comp_obj_cnt+1);
1728 exit when g_enrolled%notfound;
1729 g_comp_obj_cnt := g_comp_obj_cnt+1;
1730 --
1731 if g_enrt_tbl(g_comp_obj_cnt).pl_id is not null then
1732 --
1733 ben_cobj_cache.get_pl_dets
1734 (p_business_group_id => p_business_group_id
1735 ,p_effective_date => p_effective_date
1736 ,p_pl_id => g_enrt_tbl(g_comp_obj_cnt).pl_id
1737 ,p_inst_row => l_plnrow
1738 );
1739 --
1740 g_enrt_tbl(g_comp_obj_cnt).dpnt_cvd_by_othr_apls_flag
1741 := l_plnrow.dpnt_cvd_by_othr_apls_flag;
1742 --
1743 end if;
1744 --
1745 if g_enrt_tbl(g_comp_obj_cnt).oipl_id is not null then
1746 --
1747 ben_cobj_cache.get_oipl_dets
1748 (p_business_group_id => p_business_group_id
1749 ,p_effective_date => p_effective_date
1750 ,p_oipl_id => g_enrt_tbl(g_comp_obj_cnt).oipl_id
1751 ,p_inst_row => l_oiplrow
1752 );
1753 --
1754 g_enrt_tbl(g_comp_obj_cnt).opt_id := l_oiplrow.opt_id;
1755 --
1756 else
1757 --
1758 g_enrt_tbl(g_comp_obj_cnt).opt_id := null;
1759 --
1760 end if;
1761 --
1762 l_cnt := l_cnt + 1;
1763 --
1764 -- If enrollment is suspended and allow interim coverage, then copy
1765 -- interim prtt_enrt_rslt_id into l_interim_tbl.
1766 --
1767 if (nvl(g_enrt_tbl(g_comp_obj_cnt).SSPNDD_FLAG,'X') = 'Y'
1768 and g_enrt_tbl(g_comp_obj_cnt).RPLCS_SSPNDD_RSLT_ID is not NULL)
1769 then
1770 --
1771 l_interim_cnt := l_interim_cnt + 1;
1772 l_interim_tbl(l_interim_cnt) :=
1773 g_enrt_tbl(g_comp_obj_cnt).RPLCS_SSPNDD_RSLT_ID;
1774 --
1775 end if;
1776 --
1777 end loop;
1778 close g_enrolled;
1779 --
1780 open g_epenotenrolled
1781 (c_effective_date => p_effective_date
1782 ,c_business_group_id => p_business_group_id
1783 ,c_person_id => p_person_id
1784 ,c_pgm_id => nvl(p_pgm_id, -999999)
1785 );
1786 loop
1787 fetch g_epenotenrolled into g_enrt_tbl(g_comp_obj_cnt+1);
1788 exit when g_epenotenrolled%notfound;
1789 g_comp_obj_cnt := g_comp_obj_cnt+1;
1790 --
1791 if g_enrt_tbl(g_comp_obj_cnt).oipl_id is not null then
1792 --
1793 ben_cobj_cache.get_oipl_dets
1794 (p_business_group_id => p_business_group_id
1795 ,p_effective_date => p_effective_date
1796 ,p_oipl_id => g_enrt_tbl(g_comp_obj_cnt).oipl_id
1797 ,p_inst_row => l_oiplrow
1798 );
1799 --
1800 g_enrt_tbl(g_comp_obj_cnt).opt_id := l_oiplrow.opt_id;
1801 --
1802 else
1803 --
1804 g_enrt_tbl(g_comp_obj_cnt).opt_id := null;
1805 --
1806 end if;
1807 --
1808 l_cnt := l_cnt + 1;
1809 --
1810 -- If enrollment is suspended and allow interim coverage, then copy
1811 -- interim prtt_enrt_rslt_id into l_interim_tbl.
1812 --
1813 if (nvl(g_enrt_tbl(g_comp_obj_cnt).SSPNDD_FLAG,'X') = 'Y' and
1814 g_enrt_tbl(g_comp_obj_cnt).RPLCS_SSPNDD_RSLT_ID is not NULL)
1815 then
1816 --
1817 l_interim_cnt := l_interim_cnt + 1;
1818 l_interim_tbl(l_interim_cnt) :=
1819 g_enrt_tbl(g_comp_obj_cnt).RPLCS_SSPNDD_RSLT_ID;
1820 --
1821 end if;
1822 --
1823 end loop;
1824 close g_epenotenrolled;
1825 --
1826 -- Check which record is interim record. If it is Interim, then set
1827 -- interim flag to 'Y'
1828 --
1829 l_step := 20;
1830 For i in 1..g_comp_obj_cnt loop
1831 for j in 1..l_interim_cnt loop
1832 if g_enrt_tbl(i).prtt_enrt_rslt_id = l_interim_tbl(j) then
1833 g_enrt_tbl(i).interim_flag := 'Y';
1834 exit;
1835 end if;
1836 end loop;
1837 End loop;
1838 --
1839 -- If program ID is not Null then get program information and store it in
1840 -- g_pgm_rec
1841 --
1842 l_step := 30;
1843 if (p_pgm_id is not NULL) then
1844 open g_c_pgm(p_effective_date,
1845 p_business_group_id,
1846 p_pgm_id);
1847 fetch g_c_pgm into g_pgm_rec;
1848 if (g_c_pgm%notfound) then
1849 close g_c_pgm;
1850 fnd_message.set_name('BEN','BEN_91468_PGM_MISSING');
1851 fnd_message.set_token('ID', to_char(p_pgm_id));
1852 fnd_message.raise_error;
1853 end if;
1854 close g_c_pgm;
1855 end if;
1856 --
1857 l_step := 40;
1858 --
1859 l_dup_ptip_id_list.delete;
1860 l_dup_pl_typ_id_list.delete;
1861 l_dup_pl_id_list.delete;
1862 --
1863 for i in 1..g_comp_obj_cnt loop
1864 --
1865 -- Get all of the unique PTIPs and cache them.
1866 -- If some limits are null then override with values from pl_typ.
1867 --
1868 l_interim_calc := FALSE;
1869 --
1870 if g_enrt_tbl(i).ptip_id is not null
1871 and not ben_cache.check_list_duplicate
1872 (p_list => l_dup_ptip_id_list
1873 ,p_id => g_enrt_tbl(i).ptip_id
1874 )
1875 then
1876 --
1877 open g_c_ptip(p_effective_date,
1878 p_business_group_id,
1879 g_enrt_tbl(i).ptip_id);
1880 fetch g_c_ptip into g_ptip_tbl(g_ptip_cnt+1);
1881 if (g_c_ptip%notfound) then
1882 close g_c_ptip;
1883 fnd_message.set_name('BEN','BEN_91462_PTIP_MISSING');
1884 fnd_message.set_token('ID', to_char(g_enrt_tbl(i).ptip_id) );
1885 fnd_message.raise_error;
1886 else
1887 g_ptip_cnt := g_ptip_cnt + 1;
1888 --
1889 -- if no_mx/mn_pl_typ_ov(e)rid_flag='N' and
1890 -- mx/mn_enrd_alwd/rqd_ovrid_num is null then can walk up
1891 -- hierarchy to get value from pl_typ
1892 --
1893 if ((g_ptip_tbl(g_ptip_cnt).no_mx_pl_typ_ovrid_flag='N' and
1894 g_ptip_tbl(g_ptip_cnt).mx_enrd_alwd_ovrid_num is null)
1895 or (g_ptip_tbl(g_ptip_cnt).no_mn_pl_typ_overid_flag='N' and
1896 g_ptip_tbl(g_ptip_cnt).mn_enrd_rqd_ovrid_num is null)
1897 ) then
1898 --
1899 -- Note: the pl_typ_rec is not stored as it is later,
1900 -- Just the override columns are used and stored over
1901 -- the null values at the ptip level.
1902 --
1903 open g_c_pl_typ(p_effective_date,
1904 p_business_group_id,
1905 g_enrt_tbl(i).pl_typ_id);
1906 fetch g_c_pl_typ into l_pl_typ_rec;
1907 if (g_c_pl_typ%notfound) then
1908 close g_c_pl_typ;
1909 fnd_message.set_name
1910 ('BEN','BEN_91469_PL_TYP_MISSING');
1911 fnd_message.set_token
1912 ('ID',to_char(g_enrt_tbl(i).pl_typ_id) );
1913 fnd_message.raise_error;
1914 end if;
1915 close g_c_pl_typ;
1916 if (g_ptip_tbl(g_ptip_cnt).no_mn_pl_typ_overid_flag='N' and
1917 g_ptip_tbl(g_ptip_cnt).mn_enrd_rqd_ovrid_num is null
1918 ) then
1919 g_ptip_tbl(g_ptip_cnt).MN_ENRD_RQD_OVRID_NUM :=
1920 l_pl_typ_rec.mn_enrl_rqd_num;
1921 end if;
1922 if (g_ptip_tbl(g_ptip_cnt).no_mx_pl_typ_ovrid_flag='N' and
1923 g_ptip_tbl(g_ptip_cnt).mx_enrd_alwd_ovrid_num is null
1924 ) then
1925 g_ptip_tbl(g_ptip_cnt).MX_ENRD_ALWD_OVRID_NUM :=
1926 l_pl_typ_rec.mx_enrl_alwd_num;
1927 end if;
1928 end if;
1929 if (g_ptip_tbl(g_ptip_cnt).sbj_to_sps_lf_ins_mx_flag = 'Y'
1930 or g_ptip_tbl(g_ptip_cnt).sbj_to_dpnt_lf_ins_mx_flag = 'Y'
1931 or g_ptip_tbl(g_ptip_cnt).use_to_sum_ee_lf_ins_flag = 'Y'
1932 ) then
1933 g_enrt_tbl(i).calc_interm := 1;
1934 end if;
1935 l_prev_ptip_id := g_enrt_tbl(i).ptip_id;
1936 end if;
1937 close g_c_ptip;
1938 elsif (g_enrt_tbl(i).ptip_id is not NULL) then
1939 if (g_ptip_tbl(g_ptip_cnt).sbj_to_sps_lf_ins_mx_flag = 'Y'
1940 or g_ptip_tbl(g_ptip_cnt).sbj_to_dpnt_lf_ins_mx_flag = 'Y'
1941 or g_ptip_tbl(g_ptip_cnt).use_to_sum_ee_lf_ins_flag = 'Y'
1942 ) then
1943 g_enrt_tbl(i).calc_interm := 1;
1944 end if;
1945 end if;
1946 --
1947 -- Get all unique plan types not in program.
1948 --
1949 l_step := 50;
1950 --
1951 if (not ben_cache.check_list_duplicate
1952 (p_list => l_dup_pl_typ_id_list
1953 ,p_id => g_enrt_tbl(i).pl_typ_id
1954 )
1955 and g_enrt_tbl(i).pl_typ_id is not NULL
1956 -- and g_enrt_tbl(i).ptip_id is NULL
1957 )
1958 then
1959 open g_c_pl_typ(p_effective_date,
1960 p_business_group_id,
1961 g_enrt_tbl(i).pl_typ_id);
1962 fetch g_c_pl_typ into g_pl_typ_tbl(g_pl_typ_cnt+1);
1963 if (g_c_pl_typ%notfound) then
1964 close g_c_pl_typ;
1965 fnd_message.set_name('BEN','BEN_91469_PL_TYP_MISSING');
1966 fnd_message.set_token('ID', to_char(g_enrt_tbl(i).pl_typ_id));
1967 fnd_message.raise_error;
1968 else
1969 g_pl_typ_cnt := g_pl_typ_cnt +1;
1970 l_prev_pl_typ_id := g_enrt_tbl(i).pl_typ_id;
1971 end if;
1972 close g_c_pl_typ;
1973 end if;
1974 --
1975 l_step := 60;
1976 --
1977 -- Get the unique plan information and cache it.
1978 -- If rules exist execute them and cache the results.
1979 --
1980 if not ben_cache.check_list_duplicate
1981 (p_list => l_dup_pl_id_list
1982 ,p_id => g_enrt_tbl(i).pl_id
1983 )
1984 then
1985 --
1986 open g_c_pl(p_effective_date
1987 ,p_business_group_id
1988 ,g_enrt_tbl(i).pl_id
1989 );
1990 fetch g_c_pl into g_pl_tbl(g_pl_cnt + 1);
1991 if (g_c_pl%notfound) then
1992 close g_c_pl;
1993 fnd_message.set_name('BEN','BEN_91460_PLAN_MISSING');
1994 fnd_message.set_token('ID', to_char(g_enrt_tbl(i).pl_id));
1995 fnd_message.raise_error;
1996 else
1997 g_pl_cnt := g_pl_cnt + 1 ;
1998 --
1999 -- Bug 2715942 code addition for ben_ler_bnft_rstrn_f
2000 open c_ler(g_enrt_tbl(i).pl_id);
2001 fetch c_ler into l_ler_id ;
2002 close c_ler ;
2003 --
2004 open c_ler_rstrn(g_enrt_tbl(i).pl_id,l_ler_id);
2005 fetch c_ler_rstrn into l_ler_rstrn ;
2006 if c_ler_rstrn%found then
2007 g_pl_tbl(g_pl_cnt).mx_cvg_wcfn_amt := l_ler_rstrn.mx_cvg_wcfn_amt ;
2008 g_pl_tbl(g_pl_cnt).mx_cvg_incr_alwd_amt := l_ler_rstrn.mx_cvg_incr_alwd_amt ;
2009 g_pl_tbl(g_pl_cnt).mx_cvg_incr_wcf_alwd_amt := l_ler_rstrn.mx_cvg_incr_wcf_alwd_amt ;
2010 g_pl_tbl(g_pl_cnt).mn_cvg_rqd_amt := l_ler_rstrn.mn_cvg_rqd_amt ;
2011 g_pl_tbl(g_pl_cnt).mx_cvg_alwd_amt := l_ler_rstrn.mx_cvg_alwd_amt ;
2012 g_pl_tbl(g_pl_cnt).mx_cvg_rl := l_ler_rstrn.mx_cvg_rl ;
2013 g_pl_tbl(g_pl_cnt).mn_cvg_rl := l_ler_rstrn.mn_cvg_rl ;
2014 end if ;
2015 close c_ler_rstrn ;
2016 -- End 2715942
2017 -- g_pl_cnt := g_pl_cnt + 1;
2018 --
2019 -- If mx_cvg_rl Rule exist, then override the mx cvg amt with
2020 -- the result of mx_cvg_rl rule.
2021 --
2022 if (g_pl_tbl(g_pl_cnt).mx_cvg_rl is not null) then
2023 g_pl_tbl(g_pl_cnt).mx_cvg_alwd_amt :=
2024 calc_mx_amt
2025 (p_mx_cvg_rl => g_pl_tbl(g_pl_cnt).mx_cvg_rl
2026 ,p_assignment_id => p_assignment_id
2027 ,p_effective_date => p_effective_date
2028 -- 3427367
2029 ,p_business_group_id => p_business_group_id
2030 ,p_pgm_id => g_enrt_tbl(i).pgm_id
2031 ,p_pl_id => g_pl_tbl(g_pl_cnt).pl_id
2032 ,p_pl_typ_id => g_pl_tbl(g_pl_cnt).pl_typ_id
2033 ,p_opt_id => g_enrt_tbl(i).opt_id
2034 ,p_ler_id => l_ler_id
2035 ,p_prtt_enrt_rslt_id => g_enrt_tbl(i).prtt_enrt_rslt_id -- 3427367
2036 ,p_person_id => p_person_id -- Bug 5331889
2037 );
2038 end if;
2039 g_pl_tbl(g_pl_cnt).ptip_id := g_enrt_tbl(i).ptip_id;
2040 --
2041 -- If mn_cvg_rl Rule exist, then override the mn cvg amt with
2042 -- result of mn_cvg_rl rule.
2043 --
2044 if (g_pl_tbl(g_pl_cnt).mn_cvg_rl is not null) then
2045 g_pl_tbl(g_pl_cnt).mn_cvg_rqd_amt :=
2046 calc_mn_amt
2047 (p_mn_cvg_rl => g_pl_tbl(g_pl_cnt).mn_cvg_rl
2048 ,p_assignment_id => p_assignment_id
2049 ,p_effective_date => p_effective_date
2050 -- 3427367
2051 ,p_business_group_id => p_business_group_id
2052 ,p_pgm_id => g_enrt_tbl(i).pgm_id
2053 ,p_pl_id => g_pl_tbl(g_pl_cnt).pl_id
2054 ,p_pl_typ_id => g_pl_tbl(g_pl_cnt).pl_typ_id
2055 ,p_opt_id => g_enrt_tbl(i).opt_id
2056 ,p_ler_id => l_ler_id
2057 ,p_prtt_enrt_rslt_id => g_enrt_tbl(i).prtt_enrt_rslt_id --3427367
2058 ,p_person_id => p_person_id -- Bug 5331889
2059 );
2060
2061 end if;
2062 l_prev_pl_id := g_enrt_tbl(i).pl_id;
2063 --
2064 if g_pl_tbl(g_pl_cnt).mx_cvg_incr_alwd_amt is not null then
2065 --
2066 -- Get prev coverage amt on plan level. Current, the process
2067 -- assume there is no Interim and suspended records for
2068 -- previous period.
2069 --
2070 open l_c_prev_pl(l_prev_pl_id);
2071 fetch l_c_prev_pl into g_pl_tbl(g_pl_cnt).prev_cvg_amt;
2072 if (l_c_prev_pl%notfound) then
2073 close l_c_prev_pl;
2074 g_pl_tbl(g_pl_cnt).prev_cvg_amt := 0;
2075 end if;
2076 close l_c_prev_pl;
2077 --
2078 end if;
2079 --
2080 end if;
2081 --
2082 close g_c_pl;
2083 --
2084 end if;
2085 end loop;
2086 --
2087 l_step := 70;
2088 --
2089 -- Create a table of enrollments for each plan then call get_opts_and_cvg
2090 -- to compute max and min enrollments.
2091 -- Ignore rows where the prtt_enrt_rslt_id is null since they are not
2092 -- enrolled. Just want to pick up the fact that there was 0 enrollments
2093 --
2094 for i in 1..g_pl_cnt loop
2095 l_enrt_cnt := 0;
2096 l_enrt_tbl.delete;
2097 --
2098 for j in 1..g_comp_obj_cnt loop
2099 if (g_enrt_tbl(j).pl_id = g_pl_tbl(i).pl_id and
2100 g_enrt_tbl(j).prtt_enrt_rslt_id is not null) then
2101 l_enrt_cnt := l_enrt_cnt + 1;
2102 l_enrt_tbl(l_enrt_cnt) := g_enrt_tbl(j);
2103 end if;
2104 end loop;
2105 If (l_enrt_cnt > 0) then
2106 hr_utility.set_location('Before g_pl_tbl(i).tot_cvg_amt '||g_pl_tbl(i).tot_cvg_amt,1234);
2107 get_opts_and_cvg
2108 (p_enrt_tbl => l_enrt_tbl
2109 ,p_enrt_cnt => l_enrt_cnt
2110 ,p_tot_amt => g_pl_tbl(i).tot_cvg_amt
2111 ,p_tot_opts => g_pl_tbl(i).tot_opt_enrld
2112 );
2113 hr_utility.set_location('After g_pl_tbl(i).tot_cvg_amt '||g_pl_tbl(i).tot_cvg_amt,1234);
2114 end if;
2115 --
2116 end loop;
2117 --
2118 l_step := 80;
2119 --
2120 --
2121 -- Create a table of enrollments for each ptip then call get_pls_and_cvg
2122 -- to compute max and min enrollments.
2123 -- Ignore rows where the prtt_enrt_rslt_id is null since they are not
2124 -- enrolled. Just want to pick up the fact that there was 0 enrollments
2125 --
2126 for i in 1..g_ptip_cnt loop
2127 l_enrt_cnt := 0;
2128 l_enrt_tbl.delete;
2129 for j in 1..g_comp_obj_cnt loop
2130 -- Bug No. 6454197 Added code to enforce limitation for enrollment at plantype
2131 if (g_enrt_tbl(j).ptip_id = g_ptip_tbl(i).ptip_id and
2132 g_enrt_tbl(j).prtt_enrt_rslt_id is not null
2133 and g_enrt_tbl(j).enrt_cvg_thru_dt = hr_api.g_eot) then
2134 l_enrt_cnt := l_enrt_cnt + 1;
2135 l_enrt_tbl(l_enrt_cnt) := g_enrt_tbl(j);
2136 end if;
2137 end loop;
2138 If (l_enrt_cnt > 0) then
2139 get_pls_and_cvg
2140 (p_enrt_tbl => l_enrt_tbl
2141 ,p_enrt_cnt => l_enrt_cnt
2142 ,p_tot_amt => g_ptip_tbl(i).tot_cvg_amt
2143 ,p_tot_pls => g_ptip_tbl(i).tot_pl_enrld
2144 ,p_dpnt_cvd_by_othr_apls_flag => g_ptip_tbl(i).dpnt_cvd_by_othr_apls_flag
2145 ,p_tot_amt_no_interim => g_ptip_tbl(i).tot_cvg_amt_no_interim
2146 );
2147 hr_utility.set_location('After get_pls_and_cvg 1'||g_ptip_tbl(i).tot_cvg_amt,111);
2148 hr_utility.set_location('After tot_cvg_no_interim'||g_ptip_tbl(i).tot_cvg_amt_no_interim,111);
2149
2150 End if;
2151 end loop;
2152 --
2153 l_step := 90;
2154 --
2155 --
2156 -- Create a table of enrollments for each pl typ then call get_pls_and_cvg
2157 -- to compute max and min enrollments.
2158 -- Ignore rows where the prtt_enrt_rslt_id is null since they are not
2159 -- enrolled. Just want to pick up the fact that there was 0 enrollments
2160 --
2161 -- bug#3480144
2162 if p_pgm_id is not null then
2163 open other_pgm_enrolled (c_effective_date => p_effective_date
2164 ,c_business_group_id => p_business_group_id
2165 ,c_person_id => p_person_id
2166 ,c_pgm_id => p_pgm_id
2167 );
2168 l_enrt_cnt := 0;
2169 loop
2170 fetch other_pgm_enrolled into l_enrt_tbl2(l_enrt_cnt+1);
2171 if other_pgm_enrolled%notfound then
2172 exit;
2173 end if;
2174 l_enrt_cnt := l_enrt_cnt+1;
2175 end loop;
2176 close other_pgm_enrolled;
2177 end if;
2178 for i in 1..g_pl_typ_cnt loop
2179 l_enrt_cnt := 0;
2180 l_enrt_tbl.delete;
2181 for j in 1..g_comp_obj_cnt loop
2182 if (g_enrt_tbl(j).pl_typ_id = g_pl_typ_tbl(i).pl_typ_id
2183 /*and g_enrt_tbl(j).ptip_id is null */ and
2184 g_enrt_tbl(j).prtt_enrt_rslt_id is not null) then
2185 l_enrt_cnt := l_enrt_cnt + 1;
2186 l_enrt_tbl(l_enrt_cnt) := g_enrt_tbl(j);
2187 end if;
2188 end loop;
2189 -- bug#3480144
2190 if l_enrt_tbl2.count > 0 then
2191 for j in 1..l_enrt_tbl2.count loop
2192 if (l_enrt_tbl2(j).pl_typ_id = g_pl_typ_tbl(i).pl_typ_id
2193 and l_enrt_tbl2(j).prtt_enrt_rslt_id is not null) then
2194 l_enrt_cnt := l_enrt_cnt + 1;
2195 l_enrt_tbl(l_enrt_cnt) := l_enrt_tbl2(j);
2196 end if;
2197 end loop;
2198 end if;
2199 --
2200 If (l_enrt_cnt > 0) then
2201 get_pls_and_cvg
2202 (p_enrt_tbl => l_enrt_tbl
2203 ,p_enrt_cnt => l_enrt_cnt
2204 ,p_tot_amt => g_pl_typ_tbl(i).tot_cvg_amt
2205 ,p_tot_pls => g_pl_typ_tbl(i).tot_pl_enrld
2206 ,p_dpnt_cvd_by_othr_apls_flag => g_pl_typ_tbl(i).dpnt_cvd_by_othr_apls_flag
2207 ,p_tot_amt_no_interim => g_pl_typ_tbl(i).tot_cvg_amt_no_interim
2208 );
2209 hr_utility.set_location('AfterPLTYP get_pls_and_cvg 2'||g_pl_typ_tbl(i).tot_cvg_amt,111);
2210 hr_utility.set_location('After tot_cvg_no_interim'||g_pl_typ_tbl(i).tot_cvg_amt_no_interim,111);
2211 End if;
2212 end loop;
2213 --
2214 l_step := 100;
2215 --
2216 hr_utility.set_location('Leaving:'||l_proc,500);
2217 Exception
2218 when others then
2219 hr_utility.set_location('Fail in '|| l_proc|| ' at step ' ||
2220 to_char(l_step), 999);
2221 if g_c_pgm%isopen then
2222 close g_c_pgm;
2223 end if;
2224 if g_c_ptip%isopen then
2225 close g_c_ptip;
2226 end if;
2227 if g_c_pl_typ%isopen then
2228 close g_c_pl_typ;
2229 end if;
2230 if g_c_pl%isopen then
2231 close g_c_pl;
2232 end if;
2233 raise;
2234 end;
2235 --
2236 -- ----------------------------------------------------------------------------
2237 -- |------<get_plan_name >------|
2238 -- ----------------------------------------------------------------------------
2239 --
2240 function get_plan_name
2241 (p_pl_id in number
2242 ,p_effective_date in date
2243 ,p_business_group_id in number
2244 )
2245 return varchar2
2246 is
2247 --
2248 l_pl_rec ben_cobj_cache.g_pl_inst_row;
2249 /*
2250 l_pl_rec ben_pl_f%rowtype;
2251 */
2252 --
2253 begin
2254 --
2255 ben_cobj_cache.get_pl_dets
2256 (p_business_group_id => p_business_group_id
2257 ,p_effective_date => p_effective_date
2258 ,p_pl_id => p_pl_id
2259 ,p_inst_row => l_pl_rec
2260 );
2261 --
2262 /*
2263 ben_comp_object.get_object(p_pl_id => p_pl_id,
2264 p_rec => l_pl_rec);
2265 */
2266 return l_pl_rec.name;
2267 --
2268 end;
2269 --
2270 -- ----------------------------------------------------------------------------
2271 -- |------<p_eva_rule_output >------|
2272 -- ----------------------------------------------------------------------------
2273 --
2274 Procedure p_eva_rule_output(p_rule_returns ff_exec.outputs_t,
2275 p_proc varchar2,
2276 p_rule_id number) is
2277 l_successful varchar2(30);
2278 l_error_message varchar2(240);
2279 begin
2280 --
2281 -- Load the rule into the required variables
2282 --
2283 if p_rule_returns.exists(1) then
2284 --
2285 for l_count in p_rule_returns.first..p_rule_returns.last loop
2286 --
2287 begin
2288 --
2289 if p_rule_returns(l_count).name = 'SUCCESSFUL' then
2290 --
2291 l_successful := p_rule_returns(l_count).value;
2292 --
2293 elsif p_rule_returns(l_count).name = 'ERROR_MESSAGE' then
2294 --
2295 l_error_message := p_rule_returns(l_count).value;
2296 --
2297 else
2298 --
2299 -- Account for cases where formula returns an unknown
2300 -- variable name
2301 --
2302 fnd_message.set_name('BEN', 'BEN_92310_FORMULA_RET_PARAM');
2303 fnd_message.set_token('PROC', p_proc);
2304 fnd_message.set_token('FORMULA', p_rule_id);
2305 fnd_message.set_token('PARAMETER', p_rule_returns(l_count).name);
2306 fnd_message.raise_error;
2307 --
2308 end if;
2309 --
2310 -- Code for type casting errors from formula return variables
2311 --
2312 exception
2313 --
2314 when others then
2315 --
2316 fnd_message.set_name('BEN','BEN_92311_FORMULA_VAL_PARAM');
2317 fnd_message.set_token('PROC',p_proc);
2318 fnd_message.set_token('FORMULA',p_rule_id);
2319 fnd_message.set_token('PARAMETER',p_rule_returns(l_count).name);
2320 fnd_message.raise_error;
2321 --
2322 end;
2323 --
2324 end loop;
2325 --
2326 if l_successful <> 'Y' then
2327 --
2328 fnd_message.set_name('BEN','BEN_92187_POST_ELCN_NOT_PASS');
2329 fnd_message.set_token('ERROR_MESSAGE',l_error_message);
2330 fnd_message.raise_error;
2331 --
2332 end if;
2333 --
2334 end if;
2335 --
2336 end p_eva_rule_output;
2337 --
2338 -- ----------------------------------------------------------------------------
2339 -- |------<chk_post_elcn_rl >------|
2340 -- ----------------------------------------------------------------------------
2341 --
2342 Procedure Chk_post_elcn_rl
2343 (p_pgm_id number
2344 ,p_pl_id number
2345 ,p_pl_typ_id number
2346 ,p_opt_id number
2347 ,p_person_id number
2348 ,p_business_group_id number
2349 ,p_effective_date date
2350 ,p_pl_post_edit_rl number
2351 ,p_plip_post_edit_rl number
2352 ,p_ptip_post_edit_rl number
2353 ,p_oipl_post_edit_rl number
2354 ) is
2355
2356 Cursor c_state is
2357 select loc.region_2, asg.assignment_id, asg.organization_id
2358 from hr_locations_all loc,per_all_assignments_f asg
2359 where loc.location_id(+) = asg.location_id
2360 and asg.person_id = p_person_id
2361 and asg.assignment_type <> 'C'
2362 and asg.primary_flag = 'Y'
2363 and p_effective_date between asg.effective_start_date and asg.effective_end_date
2364 and asg.business_group_id = p_business_group_id
2365 order by DECODE(asg.assignment_type,'E',1,'B',2,3); -- 5303252 Order by asg_type
2366
2367 l_state c_state%rowtype;
2368 l_rule_returns ff_exec.outputs_t;
2369 l_proc varchar2(80) := g_package || '.chk_post_elcn_rl';
2370 l_jurisdiction_code varchar2(30);
2371
2372 --
2373 Begin
2374 --
2375 hr_utility.set_location('Entering - '||l_proc, 5);
2376 --
2377 --
2378 if p_oipl_post_edit_rl is not null or
2379 p_pl_post_edit_rl is not null or
2380 p_ptip_post_edit_rl is not null or
2381 p_plip_post_edit_rl is not null then
2382 --
2383 if p_person_id is not null then
2384
2385 open c_state;
2386
2387 fetch c_state into l_state;
2388
2389 close c_state;
2390
2391 -- if l_state.region_2 is not null then
2392 -- l_jurisdiction_code :=
2393 -- pay_mag_utils.lookup_jurisdiction_code
2394 -- (p_state => l_state.region_2);
2395 -- end if;
2396
2397 end if;
2398 --
2399 end if;
2400
2401 hr_utility.set_location('l_state.assignment_id '|| l_state.assignment_id , 5);
2402
2403 if p_oipl_post_edit_rl is not null then
2404 --
2405 l_rule_returns :=
2406 benutils.formula
2407 (p_formula_id => p_oipl_post_edit_rl,
2408 p_assignment_id => l_state.assignment_id,
2409 p_business_group_id => p_business_group_id,
2410 p_organization_id => l_state.organization_id,
2411 p_pgm_id => p_pgm_id,
2412 p_pl_id => p_pl_id,
2413 p_pl_typ_id => p_pl_typ_id,
2414 p_opt_id => p_opt_id,
2415 p_jurisdiction_code => l_jurisdiction_code,
2416 p_effective_date => p_effective_date,
2417 p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
2418 p_param1_value => to_char(p_person_id)
2419 );
2420 --
2421 p_eva_rule_output(p_rule_returns => l_rule_returns,
2422 p_proc => l_proc,
2423 p_rule_id => p_oipl_post_edit_rl);
2424 end if;
2425 --
2426 if p_pl_post_edit_rl is not null then
2427 --
2428 l_rule_returns :=
2429 benutils.formula
2430 (p_formula_id => p_pl_post_edit_rl,
2431 p_assignment_id => l_state.assignment_id,
2432 p_business_group_id => p_business_group_id,
2433 p_organization_id => l_state.organization_id,
2434 p_pgm_id => p_pgm_id,
2435 p_pl_id => p_pl_id,
2436 p_pl_typ_id => p_pl_typ_id,
2437 p_opt_id => p_opt_id,
2438 p_jurisdiction_code => l_jurisdiction_code,
2439 p_effective_date => p_effective_date,
2440 p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
2441 p_param1_value => to_char(p_person_id));
2442 --
2443 p_eva_rule_output(p_rule_returns => l_rule_returns,
2444 p_proc => l_proc,
2445 p_rule_id => p_pl_post_edit_rl);
2446 end if;
2447 --
2448 if p_plip_post_edit_rl is not null then
2449 --
2450 l_rule_returns :=
2451 benutils.formula
2452 (p_formula_id => p_plip_post_edit_rl,
2453 p_assignment_id => l_state.assignment_id,
2454 p_business_group_id => p_business_group_id,
2455 p_organization_id => l_state.organization_id,
2456 p_pgm_id => p_pgm_id,
2457 p_pl_id => p_pl_id,
2458 p_pl_typ_id => p_pl_typ_id,
2459 p_opt_id => p_opt_id,
2460 p_jurisdiction_code => l_jurisdiction_code,
2461 p_effective_date => p_effective_date,
2462 p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
2463 p_param1_value => to_char(p_person_id));
2464 --
2465 p_eva_rule_output(p_rule_returns => l_rule_returns,
2466 p_proc => l_proc,
2467 p_rule_id => p_plip_post_edit_rl);
2468 end if;
2469 --
2470 if p_ptip_post_edit_rl is not null then
2471 --
2472 l_rule_returns :=
2473 benutils.formula
2474 (p_formula_id => p_ptip_post_edit_rl,
2475 p_assignment_id => l_state.assignment_id,
2476 p_business_group_id => p_business_group_id,
2477 p_organization_id => l_state.organization_id,
2478 p_pgm_id => p_pgm_id,
2479 p_pl_id => p_pl_id,
2480 p_pl_typ_id => p_pl_typ_id,
2481 p_opt_id => p_opt_id,
2482 p_jurisdiction_code => l_jurisdiction_code,
2483 p_effective_date => p_effective_date,
2484 p_param1 => 'BEN_IV_PERSON_ID', -- Bug 5331889
2485 p_param1_value => to_char(p_person_id));
2486 --
2487 p_eva_rule_output(p_rule_returns => l_rule_returns,
2488 p_proc => l_proc,
2489 p_rule_id => p_ptip_post_edit_rl);
2490 end if;
2491 --
2492 hr_utility.set_location('Leaving - '||l_proc, 10);
2493 --
2494 End Chk_post_elcn_rl;
2495 --
2496 -- ----------------------------------------------------------------------------
2497 -- |------<check_mandatory_comp_object >------|
2498 -- ----------------------------------------------------------------------------
2499 --
2500 Procedure Chk_mndtry_comp_obj
2501 (p_person_id Number
2502 ,p_pgm_id Number
2503 ,p_business_group_id Number
2504 ,p_effective_date Date
2505 ) is
2506 Cursor c1 is
2507 select null
2508 from ben_elig_per_elctbl_chc
2509 where business_group_id = p_business_group_id
2510 and nvl(pgm_id,hr_api.g_number) = nvl(p_pgm_id, hr_api.g_number)
2511 and MNDTRY_FLAG = 'Y'
2512 and pl_id in -- bug 1207161 removed 'not'
2513 (Select distinct pen.pl_id
2514 From ben_prtt_enrt_rslt_f pen
2515 Where nvl(pen.enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
2516 and p_effective_date between
2517 pen.effective_start_date and pen.effective_end_date
2518 and pen.effective_end_date = hr_api.g_eot
2519 and pen.person_id = p_person_id
2520 and nvl(pen.pgm_id,-1) = nvl(p_pgm_id,-1)
2521 and pen.prtt_enrt_rslt_stat_cd is null)
2522 and oipl_id not in -- bug 1207161 added this.
2523 (Select distinct nvl(pen.oipl_id, -1)
2524 From ben_prtt_enrt_rslt_f pen
2525 Where nvl(pen.enrt_cvg_thru_dt,hr_api.g_eot) = hr_api.g_eot
2526 and p_effective_date between
2527 pen.effective_start_date and pen.effective_end_date
2528 and pen.effective_end_date = hr_api.g_eot
2529 and pen.person_id = p_person_id
2530 and nvl(pen.pgm_id,-1) = nvl(p_pgm_id,-1)
2531 and pen.prtt_enrt_rslt_stat_cd is null)
2532 and per_in_ler_id in (select per_in_ler_id
2533 From ben_per_in_ler
2534 Where person_id = p_person_id
2535 and per_in_ler_stat_cd = 'STRTD'
2536 and business_group_id = p_business_group_id
2537 )
2538 ;
2539 l_dump number(15);
2540 l_proc varchar2(80) := g_package || '.chk_mndtry_comp_obj';
2541 Begin
2542 hr_utility.set_location('Entering - '||l_proc, 5);
2543
2544 -- Check that mandatory options are enrolled in.
2545 -- These are options that you MUST be enrolled in, IF you are
2546 -- enrolled in the plan at all.
2547 open c1;
2548 fetch c1 into l_dump;
2549 If c1%found then
2550 close c1;
2551 fnd_message.set_name('BEN', 'BEN_91962_MNDTRY_OBJ_NOT_ENRLD');
2552 fnd_message.raise_error;
2553 End if;
2554 close c1;
2555 hr_utility.set_location('Leaving - '||l_proc, 10);
2556 End;
2557 --
2558 --
2559 -- ----------------------------------------------------------------------------
2560 -- |------<Manage_person_type_usage >------|
2561 -- ----------------------------------------------------------------------------
2562 --
2563 Procedure manage_per_type_usages
2564 (p_person_id number
2565 ,p_business_group_id number
2566 ,p_effective_date date
2567 ) is
2568 -- RCHASE - rewritten for wwBug 1433274
2569 cursor c_pen is
2570 select distinct Enrt_Cvg_Strt_Dt CSD
2571 ,nvl(Enrt_Cvg_thru_Dt,hr_api.g_eot) CED
2572 ,'N' Match
2573 from ben_prtt_enrt_rslt_f
2574 where person_id = p_person_id
2575 and effective_end_date >= enrt_cvg_strt_dt
2576 and enrt_cvg_strt_dt <= nvl(Enrt_Cvg_thru_Dt,hr_api.g_eot) --bug 5257798
2577 and prtt_enrt_rslt_stat_cd is null
2578 and sspndd_flag = 'N'
2579 and effective_end_date = hr_api.g_eot -- Bug 2130842 added this clause
2580 order by 1 asc, 2 desc;
2581 --
2582 l_person_type_id per_person_type_usages_f.person_type_id%type:=null;
2583 --
2584 cursor c_ptu is
2585 select a.person_type_usage_id
2586 ,a.person_type_id
2587 ,a.effective_start_date ESD
2588 ,a.effective_end_date EED
2589 ,a.object_version_number OVN
2590 ,'N' Match
2591 from per_person_type_usages_f a
2592 where a.Person_id = p_person_id
2593 and a.person_type_id = l_person_type_id
2594 order by a.effective_start_date asc, a.effective_end_date desc;
2595 --
2596 cursor c_pt is
2597 select person_type_id
2598 from per_person_types
2599 where system_person_type = 'PRTN'
2600 and business_group_id = p_business_group_id;
2601 --
2602 type pen_record is table of c_pen%rowtype index by binary_integer;
2603 type ptu_record is table of c_ptu%rowtype index by binary_integer;
2604 l_pen pen_record;
2605 l_ptu ptu_record;
2606 l_person_type_usage_id per_person_type_usages_f.person_type_usage_id%type;
2607 l_object_version_number per_person_type_usages_f.object_version_number%type;
2608 l_esd per_person_type_usages_f.effective_start_date%type;
2609 l_eed per_person_type_usages_f.effective_end_date%type;
2610 l_next_ptu number:=1;
2611 l_next_pen number:=1;
2612 --
2613 e_no_pt exception;
2614 e_no_pen exception;
2615 l_proc varchar2(80) := g_package||'.manage_per_type_usages';
2616 begin
2617 hr_utility.set_location('Entering '||l_proc,10);
2618 -- Get participant person type id
2619 for r_pt in c_pt loop
2620 l_person_type_id := r_pt.person_type_id;
2621 end loop;
2622 if l_person_type_id is null then
2623 fnd_message.set_name('BEN','BEN_92468_PERSON_TYP_NOT_FOUND');
2624 fnd_message.raise_error;
2625 end if;
2626 -- Fetch enrollment results and create a distinct number or inclusive date records
2627 for r_pen in c_pen loop
2628 if l_pen.count > 0 then
2629 if r_pen.csd-1 >= l_pen(l_pen.count).ced then
2630 l_pen(l_pen.count+1) := r_pen;
2631 else
2632 if r_pen.ced > l_pen(l_pen.count).ced then
2633 l_pen(l_pen.count).ced := r_pen.ced;
2634 end if;
2635 end if;
2636 else
2637 l_pen(l_pen.count+1) := r_pen;
2638 end if;
2639 end loop;
2640
2641 -- Fetch all participant person type usage records
2642 for r_ptu in c_ptu loop
2643 l_ptu(l_ptu.count+1) := r_ptu;
2644 end loop;
2645
2646 hr_utility.set_location('counts prn : '||l_pen.count || ' PTU :' || l_ptu.count,90);
2647 if l_pen.count = 0 then
2648 -- if there is no enrollment result,
2649 -- 2899702 check for per type usage if both 0
2650 -- no need to process person type usage records
2651 -- or delete all the person_type usage of prtn
2652 -- when all enrollment is backed out
2653 if l_ptu.count > 0 then
2654 for j in 1..l_ptu.count loop
2655 hr_per_type_usage_internal.delete_person_type_usage
2656 (p_validate => FALSE
2657 ,p_person_type_usage_id => l_ptu(j).person_type_usage_id
2658 ,p_effective_date => l_ptu(j).ESD
2659 ,p_datetrack_mode => hr_api.g_zap
2660 ,p_object_version_number => l_ptu(j).OVN
2661 ,p_effective_start_date => l_esd
2662 ,p_effective_end_date => l_eed);
2663 end loop; -- zap person type usage loop
2664 end if ;
2665 hr_utility.set_location('Leaving betwen '||l_proc,90);
2666
2667 return;
2668 end if;
2669
2670 if l_ptu.count > 0 then
2671 -- Compare date ranges, find exact matches
2672 if l_pen(1).csd <> l_ptu(1).esd and l_pen(1).ced <> l_ptu(1).eed then
2673 null; -- first records do not match, all will be zapped and rebuilt
2674 else
2675 for i in 1..l_pen.count loop
2676 for j in l_next_ptu..l_ptu.count loop
2677 if l_pen(i).csd=l_ptu(j).esd and l_pen(i).ced=l_ptu(j).eed then
2678 l_pen(i).match := 'Y';
2679 l_ptu(j).match := 'Y';
2680 l_next_ptu := j+1;
2681 exit;
2682 end if;
2683 end loop; -- person type usage loop
2684 if l_pen(i).match <> 'Y' then
2685 exit; -- must zap remaining person type records and rebuild
2686 end if;
2687 end loop; -- enrollment loop
2688 end if;
2689 -- Remove person type usage records that did not match
2690 for j in 1..l_ptu.count loop
2691 if l_ptu(j).match = 'N' then
2692 hr_per_type_usage_internal.delete_person_type_usage
2693 (p_validate => FALSE
2694 ,p_person_type_usage_id => l_ptu(j).person_type_usage_id
2695 ,p_effective_date => l_ptu(j).ESD
2696 ,p_datetrack_mode => hr_api.g_zap
2697 ,p_object_version_number => l_ptu(j).OVN
2698 ,p_effective_start_date => l_esd
2699 ,p_effective_end_date => l_eed);
2700 end if;
2701 end loop; -- zap person type usage loop
2702 -- Create person type usage records for unmatched enrollment records
2703 end if;
2704 for i in 1..l_pen.count loop
2705 if l_pen(i).match = 'N' then
2706 hr_per_type_usage_internal.create_person_type_usage
2707 (p_validate => FALSE
2708 ,p_person_id => p_person_id
2709 ,p_person_type_id => l_person_type_id
2710 ,p_person_type_usage_id => l_person_type_usage_id
2711 ,p_effective_date => l_pen(i).csd
2712 ,p_object_version_number => l_object_version_number
2713 ,p_effective_start_date => l_esd
2714 ,p_effective_end_date => l_eed);
2715 -- if the enrollment record does not go to end of time
2716 -- make sure to end date the person type usage row
2717 if l_pen(i).ced <> hr_api.g_eot then
2718 hr_per_type_usage_internal.delete_person_type_usage
2719 (p_validate => FALSE
2720 ,p_person_type_usage_id => l_person_type_usage_id
2721 ,p_effective_date => l_pen(i).ced
2722 ,p_datetrack_mode => hr_api.g_delete
2723 ,p_object_version_number => l_object_version_number
2724 ,p_effective_start_date => l_esd
2725 ,p_effective_end_date => l_eed);
2726 end if;
2727 end if;
2728 end loop; -- create person type usage loop
2729 hr_utility.set_location('Leaving '||l_proc,90);
2730 exception
2731 when e_no_pt then
2732 hr_utility.set_location('Leaving '||l_proc||' No person type found.',100);
2733 raise;
2734 when e_no_pen then
2735 hr_utility.set_location('Leaving '||l_proc||' No enrollments found.',110);
2736 raise;
2737 when others then
2738 hr_utility.set_location('Leaving '||l_proc||' When others fired.',120);
2739 raise;
2740 End;
2741 --
2742 -- ----------------------------------------------------------------------------
2743 -- << Susp_Svg_pl_opts >> |
2744 -- ----------------------------------------------------------------------------
2745 -- Description
2746 -- This procedure will unsuspend the rest of options for a plan that has
2747 -- Enrt_pl_opt_flag set on
2748 --
2749 -- ============================================================================
2750 --
2751 Procedure susp_svg_pl_opts
2752 (p_person_id in number
2753 ,p_effective_date in date
2754 ,p_business_group_id in number
2755 ,p_pgm_id in number
2756 ) is
2757 --
2758 Cursor c1 is
2759 select distinct b.per_in_ler_id, b.ler_id, b.pl_id
2760 From ben_prtt_enrt_rslt_f b
2761 ,ben_pl_f c
2762 Where b.person_id = p_person_id
2763 And nvl(b.pgm_id,-1) = nvl(p_pgm_id,-1)
2764 And p_effective_date between
2765 b.effective_start_date and b.effective_end_date
2766 And b.business_group_id=p_business_group_id
2767 And b.enrt_cvg_strt_dt < nvl(b.effective_end_date,hr_api.g_eot)
2768 And b.oipl_id is NULL
2769 and b.prtt_enrt_rslt_stat_cd is null
2770 And b.sspndd_flag = 'Y'
2771 And b.pl_id = c.pl_id
2772 And p_effective_date between
2773 c.effective_start_date and c.effective_end_date
2774 And c.enrt_pl_opt_flag = 'Y'
2775 ;
2776 --
2777 Cursor c2 (c_pl_id number) is
2778 Select a.prtt_enrt_rslt_id
2779 ,a.effective_start_date
2780 ,a.effective_end_date
2781 ,a.object_version_number
2782 From ben_prtt_enrt_rslt_f a
2783 Where a.person_id = p_person_id
2784 And nvl(a.pgm_id,-1) = nvl(p_pgm_id,-1)
2785 And a.business_group_id=p_business_group_id
2786 And p_effective_date between
2787 a.effective_start_date and a.effective_end_date
2788 And a.enrt_cvg_strt_dt < nvl(a.effective_end_date, hr_api.g_eot)
2789 And a.oipl_id is not null
2790 and a.prtt_enrt_rslt_stat_cd is null
2791 And a.sspndd_flag = 'N'
2792 And a.pl_id = c_pl_id
2793 ;
2794 --
2795 -- Variables declaration
2796 --
2797 l_proc varchar2(72) := g_package||'susp_svg_pl_opts';
2798 l_step integer;
2799 l_datetrack_mode varchar2(30);
2800 --
2801 Begin
2802 hr_utility.set_location('Entering' || l_proc,5);
2803 --
2804 -- C1 is cursor is pull out all suspended plan with in program that user
2805 -- specified and cursor c2 is cursor to pull out all options are not suspended
2806 -- within the plan.
2807 --
2808 l_step := 100;
2809 For l_rec1 in c1 loop
2810 l_step := trunc(l_step,-1) + 10;
2811 For l_rec in c2(l_rec1.pl_id) Loop
2812 If (p_effective_date = l_rec.effective_start_date ) then
2813 l_datetrack_mode := hr_api.g_correction;
2814 Else
2815 l_datetrack_mode := hr_api.g_update;
2816 end if;
2817 --
2818 -- Noted:
2819 -- the following call need to changed to call suspended process after
2820 -- suspend process completed.
2821 --
2822 BEN_PRTT_ENRT_RESULT_API.Update_PRTT_ENRT_RESULT
2823 (p_validate => FALSE
2824 ,p_prtt_enrt_rslt_id => l_rec.prtt_enrt_rslt_id
2825 ,p_per_in_ler_id => l_rec1.per_in_ler_id
2826 ,p_ler_id => l_rec1.ler_id
2827 ,p_effective_start_date => l_rec.effective_start_date
2828 ,p_effective_end_date => l_rec.effective_end_date
2829 ,p_business_group_id => p_business_group_id
2830 ,p_object_version_number => l_rec.object_version_number
2831 ,p_datetrack_mode => l_datetrack_mode
2832 ,p_sspndd_flag => 'Y'
2833 ,p_effective_date => p_effective_date
2834 );
2835 End loop;
2836 End loop;
2837 hr_utility.set_location('Leaving' || l_proc,10);
2838 Exception
2839 when others then
2840 hr_utility.set_location('Fail at '||l_proc||' step - '||
2841 to_char(l_step),999);
2842 raise;
2843 End;
2844 --
2845 --
2846 -- ----------------------------------------------------------------------------
2847 -- |------<multi_row_edit >------|
2848 -- ----------------------------------------------------------------------------
2849 --
2850 -- Description
2851 -- This procedure is used to cached all programs, plan types, plans for
2852 -- a specified person_id..
2853 --
2854 -- Pre Conditions
2855 -- None.
2856 --
2857 -- In Parameters
2858 -- person_id Person ID.
2859 -- effective_date effective date.
2860 -- p_business_group_id Business Group ID.
2861 -- p_pgm_id Program ID If NULL then it means Plan not in
2862 -- program.
2863 --
2864 -- Post Success
2865 -- Processing continues
2866 --
2867 -- Post Failure
2868 -- Error handled by procedure
2869 --
2870 -- Access Status
2871 -- Internal table handler use only.
2872 --
2873 Procedure multi_rows_edit
2874 (p_person_id in number
2875 ,p_effective_date in date
2876 ,p_business_group_id in number
2877 ,p_pgm_id in number
2878 ,p_include_erl in varchar2
2879 ) is
2880 cursor c_paf is
2881 select assignment_id
2882 from per_all_assignments_f
2883 where person_id = p_person_id
2884 and assignment_type <> 'C'
2885 and primary_flag = 'Y'
2886 and p_effective_date between
2887 effective_start_date and effective_end_date
2888 and business_group_id = p_business_group_id
2889 order by assignment_type desc, effective_start_date desc ; -- bug 4124110
2890 --
2891 cursor c_pl_name(cv_pl_id number) is
2892 select pln.name
2893 from ben_pl_f pln
2894 where pln.pl_id = cv_pl_id
2895 and pln.business_group_id = p_business_group_id;
2896 --
2897
2898 --Bug 2390734 Changed the join condition to compare OPT.OPT_ID with OIPL.OPT_ID.(Intially it was being compared with itself)
2899 cursor c_opt_name(cv_oipl_id in number) is
2900 select ' : ' || opt.name
2901 from ben_oipl_f oipl,
2902 ben_opt_f opt
2903 where oipl.oipl_id = cv_oipl_id
2904 and opt.opt_id = oipl.opt_id --Bug 2390734
2905 and opt.business_group_id = p_business_group_id
2906 and oipl.business_group_id = p_business_group_id;
2907 --
2908 cursor c_erl (p_pl_typ_id number) is
2909 select 'Y'
2910 from ben_prtt_enrt_rslt_f pen,
2911 ben_enrt_bnft enb
2912 where pen.person_id = p_person_id
2913 and pen.prtt_enrt_rslt_stat_cd is null
2914 and enrt_cvg_thru_dt = hr_api.g_eot
2915 and pen.comp_lvl_cd <> 'PLANIMP'
2916 and pen.prtt_enrt_rslt_id = enb.prtt_enrt_rslt_id
2917 and pen.pgm_id = p_pgm_id
2918 and pen.pl_typ_id = p_pl_typ_id
2919 and enb.cvg_mlt_cd = 'ERL'
2920 and pen.effective_end_date = hr_api.g_eot;
2921 --
2922 i binary_integer :=0;
2923 j binary_integer :=0;
2924 l_pl_notfnd boolean :=TRUE;
2925 l_init_flag boolean :=TRUE;
2926 l_prev_opt_id ben_oipl_f.opt_id%type := 0;
2927 l_status boolean;
2928 l_step integer;
2929 l_proc varchar2(72) := g_package||'multi_rows_edit';
2930 l_prev_oipl_id number := -999999;
2931 l_prev_ptip_name ben_pl_typ_f.name%type; -- UTF8 Change Bug 2254683
2932 l_prev_pl_id number := -999999;
2933 l_prev_cvg_strt_dt date;
2934 l_prev_cvg_end_dt date;
2935 l_assignment_id per_assignments_f.assignment_id%type;
2936 l_increase number;
2937 l_plan_opt_names varchar2(3000); --UTF8 Change Bug 2254683
2938 l_chk_pln_cvg_lmts boolean;
2939 --
2940 -- Bug 2162121
2941 --
2942 l_ptip_tbl_ct number := g_ptip_tbl.count;
2943 l_plan_name ben_pl_f.name%type; -- UTF8 Change Bug 2254683
2944 l_option_name ben_opt_f.name%type; -- UTF8 Change Bug 2254683
2945 --
2946 l_pl_rec ben_cobj_cache.g_pl_inst_row;
2947 /*
2948 l_pl_rec ben_pl_f%rowtype;
2949 */
2950 l_oipl_rec ben_cobj_cache.g_oipl_inst_row;
2951 /*
2952 l_oipl_rec ben_oipl_f%rowtype;
2953 */
2954 l_plip_rec ben_cobj_cache.g_plip_inst_row;
2955 /*
2956 l_plip_rec ben_plip_f%rowtype;
2957 */
2958 l_ptip_rec ben_cobj_cache.g_ptip_inst_row;
2959 /*
2960 l_ptip_rec ben_ptip_f%rowtype;
2961 */
2962 l_erl varchar2(30);
2963
2964 --
2965 begin
2966 hr_utility.set_location(l_proc,5);
2967 --
2968 -- suspended all suspended saving plan's options.
2969 --
2970 l_step := 5;
2971 susp_svg_pl_opts
2972 (p_person_id => p_person_id
2973 ,p_effective_date => p_effective_date
2974 ,p_business_group_id => p_business_group_id
2975 ,p_pgm_id => p_pgm_id
2976 );
2977 --
2978 -- Retrieve asignment id from per_assignment_f.
2979 --
2980 l_step := 10;
2981 open c_paf;
2982 fetch c_paf into l_assignment_id;
2983 if (c_paf%notfound) then
2984 close c_paf;
2985 fnd_message.set_name('BEN','BEN_91698_NO_ASSIGNMENT_FND');
2986 fnd_message.set_token('ID' , to_char(p_person_id));
2987 --Bug# 2261610
2988 fnd_message.set_token('PROC' , l_proc);
2989 fnd_message.raise_error;
2990 end if;
2991 close c_paf;
2992 --
2993 hr_utility.set_location('l_assignment_id is :-'||l_assignment_id ,1234);
2994 -- Cache all comp objects belong to a specified person_id for a
2995 -- specified program or all program dependent on p_chk_all_pgm_flg,
2996 -- If Yes, then all program will be cached, otherwise, only the pgm_id
2997 -- is specified.
2998 --
2999 l_step := 20;
3000 cache_enrt_info(p_effective_date
3001 ,p_business_group_id
3002 ,p_person_id, p_pgm_id
3003 ,l_assignment_id
3004 ,p_include_erl);
3005 --
3006 -- Check enrollment limitation and coverage limitation of plan type
3007 -- in program.
3008 --
3009 for i in 1..g_ptip_cnt loop
3010 l_step := 25;
3011 -- * plan type in program enrollment limitation (Max and Min).
3012 if (g_ptip_tbl(i).tot_pl_enrld > g_ptip_tbl(i).MX_ENRD_ALWD_OVRID_NUM
3013 and g_ptip_tbl(i).MX_ENRD_ALWD_OVRID_NUM is not NULL
3014 and g_ptip_tbl(i).dpnt_cvd_by_othr_apls_flag = 'N') then
3015 --
3016 --bug #2162121
3017 --
3018 hr_utility.set_location('Get the plan name and option name.:-' ,1234);
3019 l_ptip_tbl_ct := nvl(g_ptip_tbl(i).tot_pl_enrld, 0); -- nvl(g_ptip_tbl.count, 0); Bug 2390734
3020 -- -- This change is made so that the error message for 92968 displays names of only
3021 -- those compensation objects, that the person is being enrolled in.
3022 if l_ptip_tbl_ct > 0 then
3023 --
3024 l_plan_opt_names := null;
3025 --
3026 if l_ptip_tbl_ct > 10 then
3027 l_ptip_tbl_ct := 10;
3028 end if;
3029 for i2 in 1..l_ptip_tbl_ct loop
3030
3031 if g_ptip_tbl(i).ptip_id = g_enrt_tbl(i2).ptip_id then
3032
3033 l_plan_name := null;
3034 open c_pl_name(g_enrt_tbl(i2).pl_id);
3035 fetch c_pl_name into l_plan_name;
3036 close c_pl_name;
3037
3038 -- l_plan_opt_names := l_plan_opt_names ||l_plan_name;
3039 hr_utility.set_location (l_plan_name|| 'l_plan_name :1 : ',1235);
3040
3041 if g_enrt_tbl(i2).oipl_id is not null then
3042 --
3043 l_option_name := null;
3044 open c_opt_name(g_enrt_tbl(i2).oipl_id);
3045 fetch c_opt_name into l_option_name;
3046 close c_opt_name;
3047
3048 l_plan_name := l_plan_name ||l_option_name;
3049 hr_utility.set_location (l_option_name|| 'l_option_name',1235);
3050 --
3051 end if ;
3052
3053 if l_plan_opt_names is null then
3054 l_plan_opt_names := l_plan_name;
3055 else
3056 l_plan_opt_names := l_plan_opt_names || ', ' ||l_plan_name;
3057 end if;
3058
3059 end if; --g_ptip_tbl(i).ptip_id = g_enrt_tbl(i2).ptip_id
3060
3061 hr_utility.set_location (l_plan_opt_names|| 'l_plan_opt_names',1235);
3062
3063 end loop;
3064 end if; --l_ptip_tbl_ct > 0
3065
3066 -- end 2162121
3067 fnd_message.set_name('BEN','BEN_92968_PL_ENRD_GT_MX_ALWD');
3068 fnd_message.set_token ('PLAN_OPTION_NAMES', l_plan_opt_names);
3069 fnd_message.set_token
3070 ('TOT_ENRD'
3071 ,to_char(g_ptip_tbl(i).tot_pl_enrld));
3072 fnd_message.set_token
3073 ('MX_ENRL'
3074 ,to_char(g_ptip_tbl(i).MX_ENRD_ALWD_OVRID_NUM));
3075 fnd_message.set_token('PL_TYP_NAME', g_ptip_tbl(i).name);
3076 fnd_message.raise_error;
3077 elsif(g_ptip_tbl(i).tot_pl_enrld < g_ptip_tbl(i).MN_ENRD_RQD_OVRID_NUM
3078 and g_ptip_tbl(i).MN_ENRD_RQD_OVRID_NUM is not NULL) then
3079 l_erl := 'N';
3080 if p_include_erl = 'N' then
3081 --
3082 open c_erl(g_ptip_tbl(i).pl_typ_id);
3083 fetch c_erl into l_erl;
3084 close c_erl;
3085 --
3086 end if;
3087 if l_erl = 'N' then
3088 --
3089 l_step := 26;
3090 fnd_message.set_name('BEN','BEN_91588_PL_ENRD_LT_MN_RQD');
3091 /* fnd_message.set_token
3092 ('TOT_ENRD'
3093 ,to_char(g_ptip_tbl(i).tot_pl_enrld));*/ -- Bug 5664907
3094 fnd_message.set_token
3095 ('MN_ENRL'
3096 ,to_char(g_ptip_tbl(i).MN_ENRD_RQD_OVRID_NUM));
3097 fnd_message.set_token('PL_TYP_NAME', g_ptip_tbl(i).name);
3098 fnd_message.raise_error;
3099 --
3100 end if;
3101 --
3102 end if;
3103 --
3104 -- Plan type in program coverage limitation (Max and Min).
3105 --
3106 l_step := 30;
3107 if (g_ptip_tbl(i).tot_cvg_amt > g_ptip_tbl(i).MX_CVG_ALWD_AMT
3108 and g_ptip_tbl(i).MX_CVG_ALWD_AMT is not NULL ) then
3109 fnd_message.set_name('BEN','BEN_92500_PL_CVG_AMT_GT_MX_ALW');
3110 fnd_message.set_token
3111 ('TOT_AMT'
3112 ,to_char(g_ptip_tbl(i).tot_cvg_amt));
3113 fnd_message.set_token
3114 ('MX_AMT'
3115 ,to_char(g_ptip_tbl(i).MX_CVG_ALWD_AMT));
3116 fnd_message.set_token('PL_TYP_NAME', g_ptip_tbl(i).name);
3117 fnd_message.raise_error;
3118 end if;
3119 --
3120 -- If plan type is spouse/dependent life ins then store amount for
3121 -- later used.
3122 --
3123 l_step := 35;
3124 if (g_ptip_tbl(i).sbj_to_sps_lf_ins_mx_flag = 'N') and
3125 (g_ptip_tbl(i).sbj_to_dpnt_lf_ins_mx_flag = 'N') and
3126 (g_ptip_tbl(i).use_to_sum_ee_lf_ins_flag = 'Y') then
3127 g_tot_ee_lf_ins_amt := g_tot_ee_lf_ins_amt + g_ptip_tbl(i).tot_cvg_amt;
3128 g_tot_ee_lf_ins_amt_no := g_tot_ee_lf_ins_amt_no + g_ptip_tbl(i).tot_cvg_amt_no_interim;
3129 end if;
3130 hr_utility.set_location('g_tot_ee_lf_ins_amt '||g_tot_ee_lf_ins_amt,111);
3131 hr_utility.set_location('g_tot_ee_lf_ins_amt_no '||g_tot_ee_lf_ins_amt_no,111);
3132 end loop;
3133 --
3134 -- Check for spouse and dependent coverage limitations.
3135 --
3136 for i in 1..g_ptip_cnt loop
3137 -- Bug 4613929, Started one more Plantype loop to check for coverage for
3138 -- Sps or dpnt's plantype is within participant's % maximum coverage
3139 l_step := 38;
3140 if (g_ptip_tbl(i).sbj_to_sps_lf_ins_mx_flag = 'Y') then
3141 g_tot_sps_lf_ins_amt := g_ptip_tbl(i).tot_cvg_amt;
3142 g_tot_sps_lf_ins_amt_no := g_ptip_tbl(i).tot_cvg_amt_no_interim;
3143 if (g_pgm_rec.pgm_id = g_ptip_tbl(i).pgm_id) then
3144 g_mx_sps_pct_prtt_lf := g_pgm_rec.MX_SPS_PCT_PRTT_LF_AMT;
3145 end if;
3146 elsif (g_ptip_tbl(i).sbj_to_dpnt_lf_ins_mx_flag = 'Y') then
3147 g_tot_dpnt_lf_ins_amt := g_ptip_tbl(i).tot_cvg_amt;
3148 g_tot_dpnt_lf_ins_amt_no := g_ptip_tbl(i).tot_cvg_amt_no_interim;
3149 if (g_pgm_rec.pgm_id = g_ptip_tbl(i).pgm_id) then
3150 g_mx_dpnt_pct_prtt_lf := g_pgm_rec.MX_DPNT_PCT_PRTT_LF_AMT;
3151 end if;
3152 end if;
3153 --
3154 hr_utility.set_location('g_tot_sps_lf_ins_amt'||g_tot_sps_lf_ins_amt,111);
3155 hr_utility.set_location('g_tot_dpnt_lf_ins_amt'||g_tot_dpnt_lf_ins_amt,111);
3156 hr_utility.set_location('g_tot_sps_lf_ins_amt_no'||g_tot_sps_lf_ins_amt_no,111);
3157 hr_utility.set_location('g_tot_dpnt_lf_ins_amt_no'||g_tot_dpnt_lf_ins_amt_no,111);
3158 --
3159 l_step := 40;
3160 if (((g_mx_dpnt_pct_prtt_lf * g_tot_ee_lf_ins_amt)/100)
3161 < g_tot_dpnt_lf_ins_amt) then
3162 fnd_message.set_name('BEN','BEN_91590_DPNT_LF_INS_OVER_LMT');
3163 fnd_message.set_token('TOT_INS',to_char(g_tot_dpnt_lf_ins_amt));
3164 fnd_message.set_token
3165 ('MX_INS'
3166 ,to_char((g_mx_dpnt_pct_prtt_lf * g_tot_ee_lf_ins_amt)/100));
3167 fnd_message.raise_error;
3168 elsif(((g_mx_sps_pct_prtt_lf * g_tot_ee_lf_ins_amt)/100)
3169 < g_tot_sps_lf_ins_amt) then
3170 fnd_message.set_name('BEN','BEN_91591_SP_LF_INS_OVER_LMT');
3171 fnd_message.set_token('TOT_INS', to_char(g_tot_sps_lf_ins_amt));
3172 fnd_message.set_token('MX_INS',
3173 to_char((g_mx_sps_pct_prtt_lf * g_tot_ee_lf_ins_amt)/100));
3174 fnd_message.raise_error;
3175 end if;
3176 --
3177 l_step := 45;
3178 --This Evaluates the suspended coverage also in determining the
3179 --limits.
3180 --
3181 if (((g_mx_dpnt_pct_prtt_lf * g_tot_ee_lf_ins_amt_no)/100)
3182 < g_tot_dpnt_lf_ins_amt_no) then
3183 fnd_message.set_name('BEN','BEN_91590_DPNT_LF_INS_OVER_LMT');
3184 fnd_message.set_token('TOT_INS',to_char(g_tot_dpnt_lf_ins_amt_no));
3185 fnd_message.set_token
3186 ('MX_INS'
3187 ,to_char((g_mx_dpnt_pct_prtt_lf * g_tot_ee_lf_ins_amt_no)/100));
3188 fnd_message.raise_error;
3189 elsif (((g_mx_sps_pct_prtt_lf * g_tot_ee_lf_ins_amt_no)/100)
3190 < g_tot_sps_lf_ins_amt_no) then
3191 fnd_message.set_name('BEN','BEN_91591_SP_LF_INS_OVER_LMT');
3192 fnd_message.set_token('TOT_INS', to_char(g_tot_sps_lf_ins_amt_no));
3193 fnd_message.set_token('MX_INS',
3194 to_char((g_mx_sps_pct_prtt_lf * g_tot_ee_lf_ins_amt_no)/100));
3195 fnd_message.raise_error;
3196 end if;
3197 end loop; -- Bug 4613929
3198
3199 -- Check for plan type in enrollment limitation
3200 --
3201 l_step := 50;
3202 for i in 1..g_pl_typ_cnt loop
3203 --
3204 -- Plan type enrollment limitation (Max and Min).
3205 --
3206 hr_utility.set_location('pl_typ_id='||to_char(g_pl_typ_tbl(i).pl_typ_id),99);
3207 hr_utility.set_location('val='||to_char(g_pl_typ_tbl(i).tot_pl_enrld)||
3208 ' min='||to_char(g_pl_typ_tbl(i).mn_enrl_rqd_num)||
3209 ' max='||to_char(g_pl_typ_tbl(i).mx_enrl_alwd_num)
3210 , 101);
3211 if (g_pl_typ_tbl(i).tot_pl_enrld > g_pl_typ_tbl(i).mx_enrl_alwd_num
3212 and g_pl_typ_tbl(i).mx_enrl_alwd_num is not NULL
3213 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*/
3214 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*/
3215 fnd_message.set_token
3216 ('TOT_ENRD'
3217 ,to_char(g_pl_typ_tbl(i).tot_pl_enrld));
3218 fnd_message.set_token
3219 ('MX_ENRL'
3220 , to_char(g_pl_typ_tbl(i).MX_ENRL_ALWD_NUM));
3221 fnd_message.set_token('PL_TYP_NAME', g_pl_typ_tbl(i).name);
3222 fnd_message.raise_error;
3223 elsif (g_pl_typ_tbl(i).tot_pl_enrld < g_pl_typ_tbl(i).mn_enrl_rqd_num
3224 and g_pl_typ_tbl(i).mn_enrl_rqd_num is not NULL ) then
3225 --
3226 l_erl := 'N';
3227 if p_include_erl = 'N' then
3228 --
3229 open c_erl(g_pl_typ_tbl(i).pl_typ_id);
3230 fetch c_erl into l_erl;
3231 close c_erl;
3232 --
3233 end if;
3234 if l_erl = 'N' then
3235 --
3236 fnd_message.set_name('BEN','BEN_91588_PL_ENRD_LT_MN_RQD');
3237 /* fnd_message.set_token
3238 ('TOT_ENRD'
3239 ,to_char(g_pl_typ_tbl(i).tot_pl_enrld));*/ --Bug 5664907
3240 fnd_message.set_token
3241 ('MN_ENRL'
3242 ,to_char(g_pl_typ_tbl(i).MN_ENRL_RQD_NUM));
3243 fnd_message.set_token('PL_TYP_NAME', g_pl_typ_tbl(i).name);
3244 fnd_message.raise_error;
3245 --
3246 end if;
3247 --
3248 end if;
3249 end loop;
3250 --
3251 -- Check min and max of options winthin plan.
3252 --
3253 l_step := 60;
3254 for i in 1..g_pl_cnt loop
3255 --
3256 -- BUG: 4590341
3257 -- Check for No. of Options Enrolled is b/w min.required and max.allowed
3258 --
3259 if (g_pl_tbl(i).interim_flag = 1 and g_pl_tbl(i).tot_opt_enrld = 0) then
3260 null;
3261 else
3262 if (g_pl_tbl(i).tot_opt_enrld > g_pl_tbl(i).mx_opts_alwd_num
3263 and g_pl_tbl(i).mx_opts_alwd_num is not NULL ) then
3264 fnd_message.set_name('BEN','BEN_91592_OPT_ENRD_GT_MX_ALWD');
3265 fnd_message.set_token('OPT_ENRD',to_char(g_pl_tbl(i).tot_opt_enrld));
3266 fnd_message.set_token('MX_ALWD',to_char(g_pl_tbl(i).MX_OPTS_ALWD_NUM));
3267 fnd_message.set_token('PL_NAME', g_pl_tbl(i).name);
3268 fnd_message.raise_error;
3269 elsif (g_pl_tbl(i).tot_opt_enrld < g_pl_tbl(i).mn_opts_rqd_num
3270 and g_pl_tbl(i).mn_opts_rqd_num is not NULL) then
3271 fnd_message.set_name('BEN','BEN_91593_OPT_ENRD_LT_MN_RQD');
3272 fnd_message.set_token('OPT_ENRD', to_char(g_pl_tbl(i).tot_opt_enrld));
3273 fnd_message.set_token('MN_RQD',to_char(g_pl_tbl(i).MN_OPTS_RQD_NUM));
3274 fnd_message.set_token('PL_NAME', g_pl_tbl(i).name);
3275 fnd_message.raise_error;
3276 end if;
3277 /* BUG: 3949327:
3278 The below coverage amount limits validations need
3279 to be performed only when enrolled into Plan.
3280 So, loop thru g_enrt_tbl and to find
3281 if enrolled into pln then check the following
3282 1. Total Coverage Amount b/w min.required and max.allowed
3283 2. If Previously enrolled, then increased_amount b/w min.incr.req. and max.incr allowed
3284 */
3285 l_chk_pln_cvg_lmts := FALSE;
3286 --
3287 FOR j IN 1 .. g_comp_obj_cnt LOOP
3288 --
3289 IF (g_enrt_tbl (j).pl_id = g_pl_tbl (i).pl_id
3290 AND g_enrt_tbl (j).prtt_enrt_rslt_id IS NOT NULL
3291 ) THEN
3292 hr_utility.set_location('l_chk_pln_cvg_lmts='|| 'TRUE' ,99);
3293 l_chk_pln_cvg_lmts := TRUE;
3294 EXIT;
3295 END IF;
3296 END LOOP;
3297 --
3298 hr_utility.set_location('g_pl_tbl(i).tot_cvg_amt='||g_pl_tbl(i).tot_cvg_amt,99);
3299 hr_utility.set_location('g_pl_tbl(i).mx_cvg_alwd_amt='||g_pl_tbl(i).mx_cvg_alwd_amt,99);
3300 hr_utility.set_location('g_pl_tbl(i).mx_cvg_wcfn_amt='||g_pl_tbl(i).mx_cvg_wcfn_amt,99);
3301 --
3302 IF (l_chk_pln_cvg_lmts) THEN
3303 -- Bug: 3949327 changes end;
3304 -- Check min coverage amount.
3305 --
3306 l_step := 70;
3307 --
3308 if (g_pl_tbl(i).tot_cvg_amt < g_pl_tbl(i).mn_cvg_rqd_amt
3309 and g_pl_tbl(i).mn_cvg_rqd_amt is not NULL ) then
3310 fnd_message.set_name('BEN','BEN_92286_PL_CVG_AMT_LT_MN_ALW');
3311 fnd_message.set_token('TOT_AMT',to_char(g_pl_tbl(i).tot_cvg_amt));
3312 fnd_message.set_token('MN_AMT',to_char(g_pl_tbl(i).mn_cvg_rqd_amt));
3313 fnd_message.set_token('PL_NAME', g_pl_tbl(i).name);
3314 fnd_message.raise_error;
3315 end if;
3316 --
3317 -- Check max coverage amount.
3318 --
3319 l_step := 75;
3320 --
3321 -- Coverage problem is possible if > max
3322 --
3323 if (g_pl_tbl(i).tot_cvg_amt > g_pl_tbl(i).mx_cvg_alwd_amt
3324 and g_pl_tbl(i).mx_cvg_alwd_amt is not NULL ) then
3325 if g_pl_tbl(i).tot_cvg_amt > g_pl_tbl(i).mx_cvg_wcfn_amt then
3326 --
3327 -- Over top even with certification
3328 --
3329 fnd_message.set_name('BEN','BEN_91589_PL_CVG_AMT_GT_MX_ALW');
3330 fnd_message.set_token('TOT_AMT',to_char(g_pl_tbl(i).tot_cvg_amt));
3331 fnd_message.set_token('MX_AMT',to_char(g_pl_tbl(i).MX_CVG_wcfn_AMT));
3332 fnd_message.set_token('PL_NAME', g_pl_tbl(i).name);
3333 fnd_message.raise_error;
3334 elsif g_pl_tbl(i).mx_cvg_wcfn_amt is null then
3335 --
3336 -- Over top and no certification limit exists
3337 --
3338 fnd_message.set_name('BEN','BEN_91589_PL_CVG_AMT_GT_MX_ALW');
3339 fnd_message.set_token('TOT_AMT',to_char(g_pl_tbl(i).tot_cvg_amt));
3340 fnd_message.set_token('MX_AMT',to_char(g_pl_tbl(i).MX_CVG_ALWD_AMT));
3341 fnd_message.set_token('PL_NAME', g_pl_tbl(i).name);
3342 fnd_message.raise_error;
3343 end if;
3344 end if;
3345 --
3346 -- Check max coverage increase amount.
3347 --
3348 l_step := 76;
3349 --
3350 -- Coverage problem is possible if increase > max
3351 --
3352 l_increase:=g_pl_tbl(i).tot_cvg_amt - g_pl_tbl(i).prev_cvg_amt;
3353 --
3354 -- Check increases only if the person was not previously enrolled.
3355 -- If the amount is zero, the person was not previously enrolled and
3356 -- is allowed to choose any coverage irrespective of the
3357 -- amount defined in (mx_cvg_incr_alwd_amt).
3358 --
3359 if (g_pl_tbl(i).prev_cvg_amt <> 0 and
3360 l_increase > g_pl_tbl(i).mx_cvg_incr_alwd_amt
3361 and g_pl_tbl(i).mx_cvg_incr_alwd_amt is not NULL ) then
3362 if l_increase > g_pl_tbl(i).mx_cvg_incr_wcf_alwd_amt then
3363 --
3364 -- Over top even with certification
3365 --
3366 fnd_message.set_name('BEN','BEN_91594_CERT_INCR_GT_MX_INCR');
3367 fnd_message.set_token('CERT_INCR',to_char(l_increase));
3368 fnd_message.set_token('MX_INCR',to_char(g_pl_tbl(i).MX_CVG_INCR_WCF_ALWD_AMT));
3369 fnd_message.raise_error;
3370 --
3371 elsif g_pl_tbl(i).mx_cvg_incr_wcf_alwd_amt is null then
3372 --
3373 -- Over top and no certification limit exists
3374 --
3375 fnd_message.set_name('BEN','BEN_91596_CVG_INCR_GT_MX_INCR');
3376 fnd_message.set_token('CVG_INCR',to_char(l_increase));
3377 fnd_message.set_token('MX_INCR',to_char(g_pl_tbl(i).MX_CVG_INCR_ALWD_AMT));
3378 fnd_message.raise_error;
3379 end if;
3380 --
3381 end if;
3382 --
3383 end if; --- l_chk_pln_cvg_lmts end-if
3384 --
3385 end if;
3386 --
3387 end loop;
3388 --
3389 -- Make sure not duplicate comp object with in a program such as
3390 -- same plan id and option id.
3391 --
3392 l_step := 80;
3393 -- Bug 6741391
3394 ben_cobj_cache.clear_down_cache;
3395 -- Bug 6741391
3396 for i in 1..g_comp_obj_cnt loop
3397 --
3398 if g_enrt_tbl(i).prtt_enrt_rslt_id is not null and
3399 g_enrt_tbl(i).interim_flag = 'N' then
3400 --
3401 -- RCHASE - Bug#1412801 - Moved post election edit rule from insert/update
3402 -- RCHASE validation calls to multi-row edit
3403 --
3404 ben_cobj_cache.get_pl_dets
3405 (p_business_group_id => p_business_group_id
3406 ,p_effective_date => p_effective_date
3407 ,p_pl_id => g_enrt_tbl(i).pl_id
3408 ,p_inst_row => l_pl_rec
3409 );
3410 --
3411 /*
3412 ben_comp_object.get_object(p_pl_id => g_enrt_tbl(i).pl_id,
3413 p_rec => l_pl_rec);
3414 */
3415 --
3416 if g_enrt_tbl(i).plip_id is not null then
3417 --
3418 ben_cobj_cache.get_plip_dets
3419 (p_business_group_id => p_business_group_id
3420 ,p_effective_date => p_effective_date
3421 ,p_plip_id => g_enrt_tbl(i).plip_id
3422 ,p_inst_row => l_plip_rec
3423 );
3424 --
3425 /*
3426 ben_comp_object.get_object(p_plip_id => g_enrt_tbl(i).plip_id,
3427 p_rec => l_plip_rec);
3428 */
3429 else
3430 l_plip_rec := null;
3431 end if;
3432 --
3433 if g_enrt_tbl(i).ptip_id is not null then
3434 --
3435 ben_cobj_cache.get_ptip_dets
3436 (p_business_group_id => p_business_group_id
3437 ,p_effective_date => p_effective_date
3438 ,p_ptip_id => g_enrt_tbl(i).ptip_id
3439 ,p_inst_row => l_ptip_rec
3440 );
3441 --
3442 /*
3443 ben_comp_object.get_object(p_ptip_id => g_enrt_tbl(i).ptip_id,
3444 p_rec => l_ptip_rec);
3445 */
3446 else
3447 l_ptip_rec := null;
3448 end if;
3449 --
3450 if g_enrt_tbl(i).oipl_id is not null then
3451 --
3452 ben_cobj_cache.get_oipl_dets
3453 (p_business_group_id => p_business_group_id
3454 ,p_effective_date => p_effective_date
3455 ,p_oipl_id => g_enrt_tbl(i).oipl_id
3456 ,p_inst_row => l_oipl_rec
3457 );
3458 --
3459 /*
3460 ben_comp_object.get_object(p_oipl_id => g_enrt_tbl(i).oipl_id,
3461 p_rec => l_oipl_rec);
3462 */
3463 else
3464 l_oipl_rec := null;
3465 end if;
3466
3467 if l_pl_rec.postelcn_edit_rl is not null or
3468 l_oipl_rec.postelcn_edit_rl is not null or
3469 l_plip_rec.postelcn_edit_rl is not null or
3470 l_ptip_rec.postelcn_edit_rl is not null then
3471 chk_post_elcn_rl
3472 (p_pgm_id => g_enrt_tbl(i).pgm_id,
3473 p_pl_id => g_enrt_tbl(i).pl_id,
3474 p_pl_typ_id => g_enrt_tbl(i).pl_typ_id,
3475 p_opt_id => g_enrt_tbl(i).opt_id,
3476 p_person_id => p_person_id,
3477 p_effective_date => p_effective_date,
3478 p_business_group_id => p_business_group_id,
3479 p_pl_post_edit_rl => l_pl_rec.postelcn_edit_rl,
3480 p_plip_post_edit_rl => l_plip_rec.postelcn_edit_rl,
3481 p_ptip_post_edit_rl => l_ptip_rec.postelcn_edit_rl,
3482 p_oipl_post_edit_rl => l_oipl_rec.postelcn_edit_rl);
3483 end if;
3484 -- RCHASE End
3485 --
3486 -- Check whether the plan is same as the previous one.
3487 -- Check the oipl to be same, if they are not null.
3488 -- This logic of just checking with the previous record works
3489 -- fine as the records are stored in the order pl_id, oipl_id.
3490 --
3491 if (l_prev_pl_id = g_enrt_tbl(i).pl_id) and
3492 ((g_enrt_tbl(i).oipl_id is null and l_prev_oipl_id is null) or
3493 l_prev_oipl_id = g_enrt_tbl(i).oipl_id) then
3494 --
3495 -- Plan and oipl are same, now check whether the coverage overlap.
3496 -- If not, it's fine to have two results. (Possible when a person
3497 -- re-enrolls in a comp-object after de-enrolling from it.)
3498 --
3499 if (l_prev_cvg_strt_dt between
3500 g_enrt_tbl(i).enrt_cvg_strt_dt
3501 and nvl(g_enrt_tbl(i).enrt_cvg_thru_dt,hr_api.g_eot))
3502 OR
3503 (g_enrt_tbl(i).enrt_cvg_strt_dt between
3504 l_prev_cvg_strt_dt
3505 and nvl(l_prev_cvg_end_dt,hr_api.g_eot)) then
3506 --
3507 fnd_message.set_name('BEN','BEN_91699_DUP_COMP_OBJ_IN_PGM');
3508 fnd_message.set_token
3509 ('PL_NAME'
3510 ,get_plan_name(p_pl_id => g_enrt_tbl(i).pl_id
3511 ,p_business_group_id => p_business_group_id
3512 ,p_effective_date => p_effective_date
3513 )
3514 );
3515 fnd_message.raise_error;
3516 --
3517 end if;
3518 --
3519 end if;
3520 --
3521 l_prev_pl_id := g_enrt_tbl(i).pl_id;
3522 l_prev_oipl_id := g_enrt_tbl(i).oipl_id;
3523 l_prev_cvg_strt_dt := g_enrt_tbl(i).enrt_cvg_strt_dt;
3524 l_prev_cvg_end_dt := g_enrt_tbl(i).enrt_cvg_thru_dt;
3525 --
3526 if (g_enrt_tbl(i).must_enrl_anthr_pl_id is not NULL) then
3527 l_pl_notfnd := TRUE;
3528 for j in 1..g_pl_cnt loop
3529 if (g_enrt_tbl(i).must_enrl_anthr_pl_id = g_pl_tbl(j).pl_id
3530 ) then
3531 l_pl_notfnd := FALSE;
3532 exit;
3533 end if;
3534 end loop;
3535 if (l_pl_notfnd) then
3536 fnd_message.set_name('BEN','BEN_91597_MUST_ENRL_PLAN');
3537 fnd_message.set_token('PL_ID', g_enrt_tbl(i).pl_id);
3538 fnd_message.set_token
3539 ('RQD_PL'
3540 ,get_plan_name
3541 (p_pl_id=> g_enrt_tbl(i).must_enrl_anthr_pl_id
3542 ,p_business_group_id => p_business_group_id
3543 ,p_effective_date => p_effective_date
3544 )
3545 );
3546 fnd_message.raise_error;
3547 end if;
3548 end if;
3549 end if;
3550 end loop;
3551 --
3552 -- Check for coordinate coverages. If coordinate flag set on in ptip
3553 -- level, then all options in each plan need to same.
3554 --
3555 l_step := 90;
3556 l_init_flag := true;
3557 for i in 1..g_ptip_cnt loop
3558 if (g_ptip_tbl(i).COORD_CVG_FOR_ALL_PLS_FLAG = 'Y') then
3559 for j in 1..g_comp_obj_cnt loop
3560 --
3561 if g_enrt_tbl(j).ptip_id = g_ptip_tbl(i).ptip_id and
3562 g_enrt_tbl(j).prtt_enrt_rslt_id is not null and
3563 g_enrt_tbl(j).enrt_cvg_thru_dt = hr_api.g_eot and
3564 g_enrt_tbl(j).interim_flag = 'N' and
3565 g_enrt_tbl(j).opt_id is not null then
3566 --
3567 if (l_init_flag) then
3568 l_prev_opt_id := g_enrt_tbl(j).opt_id;
3569 l_prev_ptip_name := g_ptip_tbl(i).name;
3570 l_init_flag := FALSE;
3571 elsif (l_prev_opt_id <> g_enrt_tbl(j).opt_id) then
3572 fnd_message.set_name('BEN','BEN_91598_OPT_NOT_COORD');
3573 fnd_message.set_token('PTIP_NAME1',g_ptip_tbl(i).name);
3574 fnd_message.set_token('PTIP_NAME2',l_prev_ptip_name);
3575 fnd_message.raise_error;
3576 end if;
3577 --
3578 end if;
3579 --
3580 end loop;
3581 end if;
3582 end loop;
3583 --
3584 -- Now check person_type_usage. Make sure participant coverage period is
3585 -- recorded in person type usage table.
3586 --
3587 l_step := 100;
3588 manage_per_type_usages
3589 (p_person_id => p_person_id
3590 ,p_business_group_id => p_business_group_id
3591 ,p_effective_date => p_effective_date
3592 );
3593 --
3594 -- Now check mandatory, If and plan is elected, then all oipl within the
3595 -- plan has mandatory flag set on need to be enrolled, otherwise the routine
3596 -- will bomb out.
3597 --
3598 l_step := 110;
3599 chk_mndtry_comp_obj
3600 (p_person_id => p_person_id
3601 ,p_pgm_id => p_pgm_id
3602 ,p_business_group_id => p_business_group_id
3603 ,p_effective_date => p_effective_date
3604 );
3605 hr_utility.set_location (l_proc, 10);
3606 Exception
3607 when others then
3608 hr_utility.set_location('Fail at '||l_proc||' step - '||
3609 to_char(l_step),999);
3610 raise;
3611 end;
3612 --
3613 /*--Bug#5088571
3614 -- ---------------------------------------------------------------------------
3615 -- |------------------------< chk_cvg_strt_end_dt >----------------------------|
3616 -- ---------------------------------------------------------------------------
3617 -- Description
3618 -- This procedure is used to check whether the Rate Start date is greater than Rate End date.
3619 --
3620 procedure chk_cvg_strt_end_dt(p_enrt_cvg_strt_dt in date,
3621 p_enrt_cvg_thru_dt in date,
3622 p_person_id in number
3623 ) is
3624 --
3625 l_proc varchar2(72) := g_package||'chk_cvg_strt_end_dt';
3626 l_person_id number;
3627 l_message_name varchar2(500) := 'BEN_94592_RT_STRT_GT_END_DT';
3628 --
3629 Begin
3630 --
3631 hr_utility.set_location('Entering:'||l_proc, 5);
3632 --
3633 if p_enrt_cvg_strt_dt > p_enrt_cvg_thru_dt then
3634 benutils.write(p_text=>fnd_message.get);
3635 ben_warnings.load_warning
3636 (p_application_short_name => 'BEN'
3637 ,p_message_name => l_message_name
3638 ,p_parma => 'Coverage End Date' || ' ' || fnd_date.date_to_displaydate(p_enrt_cvg_thru_dt)
3639 ,p_parmb => 'Coverage Start Date' ||' '|| fnd_date.date_to_displaydate(p_enrt_cvg_strt_dt)
3640 ,p_person_id => p_person_id
3641 );
3642 end if;
3643 --
3644 hr_utility.set_location('Leaving:'||l_proc,10);
3645 --
3646 end chk_cvg_strt_end_dt;
3647 --
3648 ----Bug#5088571*/
3649 -- ----------------------------------------------------------------------------
3650 -- |--------------------------< dt_update_validate >--------------------------|
3651 -- ----------------------------------------------------------------------------
3652 -- {Start Of Comments}
3653 --
3654 -- Description:
3655 -- This procedure is used for referential integrity of datetracked
3656 -- parent entities when a datetrack update operation is taking place
3657 -- and where there is no cascading of update defined for this entity.
3658 --
3659 -- Prerequisites:
3660 -- This procedure is called from the update_validate.
3661 --
3662 -- In Parameters:
3663 --
3664 -- Post Success:
3665 -- Processing continues.
3666 --
3667 -- Post Failure:
3668 --
3669 -- Developer Implementation Notes:
3670 -- This procedure should not need maintenance unless the HR Schema model
3671 -- changes.
3672 --
3673 -- Access Status:
3674 -- Internal Row Handler Use Only.
3675 --
3676 -- {End Of Comments}
3677 -- ----------------------------------------------------------------------------
3678 Procedure dt_update_validate
3679 (p_pgm_id in number default hr_api.g_number,
3680 p_oipl_id in number default hr_api.g_number,
3681 p_per_in_ler_id in number default hr_api.g_number,
3682 p_pl_id in number default hr_api.g_number,
3683 p_pl_typ_id in number default hr_api.g_number,
3684 p_prtt_enrt_rslt_id in number default hr_api.g_number,
3685 p_datetrack_mode in varchar2,
3686 p_validation_start_date in date,
3687 p_validation_end_date in date) Is
3688 --
3689 l_proc varchar2(72) := g_package||'dt_update_validate';
3690 l_integrity_error Exception;
3691 l_table_name all_tables.table_name%TYPE;
3692 --
3693 Begin
3694 hr_utility.set_location('Entering:'||l_proc, 5);
3695 --
3696 -- Ensure that the p_datetrack_mode argument is not null
3697 --
3698 hr_api.mandatory_arg_error
3699 (p_api_name => l_proc,
3700 p_argument => 'datetrack_mode',
3701 p_argument_value => p_datetrack_mode);
3702 --
3703 -- Only perform the validation if the datetrack update mode is valid
3704 --
3705 If (dt_api.validate_dt_upd_mode(p_datetrack_mode => p_datetrack_mode)) then
3706 --
3707 --
3708 -- Ensure the arguments are not null
3709 --
3710 hr_api.mandatory_arg_error
3711 (p_api_name => l_proc,
3712 p_argument => 'validation_start_date',
3713 p_argument_value => p_validation_start_date);
3714 --
3715 hr_api.mandatory_arg_error
3716 (p_api_name => l_proc,
3717 p_argument => 'validation_end_date',
3718 p_argument_value => p_validation_end_date);
3719 --
3720 If ((nvl(p_pgm_id, hr_api.g_number) <> hr_api.g_number) and
3721 NOT (dt_api.check_min_max_dates
3722 (p_base_table_name => 'ben_pgm_f',
3723 p_base_key_column => 'pgm_id',
3724 p_base_key_value => p_pgm_id,
3725 p_from_date => p_validation_start_date,
3726 p_to_date => p_validation_end_date))) Then
3727 l_table_name := 'ben_pgm_f';
3728 Raise l_integrity_error;
3729 End If;
3730 If ((nvl(p_oipl_id, hr_api.g_number) <> hr_api.g_number) and
3731 NOT (dt_api.check_min_max_dates
3732 (p_base_table_name => 'ben_oipl_f',
3733 p_base_key_column => 'oipl_id',
3734 p_base_key_value => p_oipl_id,
3735 p_from_date => p_validation_start_date,
3736 p_to_date => p_validation_end_date))) Then
3737 l_table_name := 'ben_oipl_f';
3738 Raise l_integrity_error;
3739 End If;
3740 If ((nvl(p_pl_id, hr_api.g_number) <> hr_api.g_number) and
3741 NOT (dt_api.check_min_max_dates
3742 (p_base_table_name => 'ben_pl_f',
3743 p_base_key_column => 'pl_id',
3744 p_base_key_value => p_pl_id,
3745 p_from_date => p_validation_start_date,
3746 p_to_date => p_validation_end_date))) Then
3747 l_table_name := 'ben_pl_f';
3748 Raise l_integrity_error;
3749 End If;
3750 If ((nvl(p_pl_typ_id, hr_api.g_number) <> hr_api.g_number) and
3751 NOT (dt_api.check_min_max_dates
3752 (p_base_table_name => 'ben_pl_typ_f',
3753 p_base_key_column => 'pl_typ_id',
3754 p_base_key_value => p_pl_typ_id,
3755 p_from_date => p_validation_start_date,
3756 p_to_date => p_validation_end_date))) Then
3757 l_table_name := 'ben_pl_typ_f';
3758 Raise l_integrity_error;
3759 End If;
3760 If ((nvl(p_prtt_enrt_rslt_id, hr_api.g_number) <> hr_api.g_number) and
3761 NOT (dt_api.check_min_max_dates
3762 (p_base_table_name => 'ben_prtt_enrt_rslt_f',
3763 p_base_key_column => 'prtt_enrt_rslt_id',
3764 p_base_key_value => p_prtt_enrt_rslt_id,
3765 p_from_date => p_validation_start_date,
3766 p_to_date => p_validation_end_date))) Then
3767 l_table_name := 'ben_prtt_enrt_rslt_f';
3768 Raise l_integrity_error;
3769 End If;
3770 End If;
3771 --
3772 hr_utility.set_location(' Leaving:'||l_proc, 10);
3773 Exception
3774 When l_integrity_error Then
3775 --
3776 -- A referential integrity check was violated therefore
3777 -- we must error
3778 --
3779 ben_utility.parent_integrity_error(p_table_name => l_table_name);
3780 --
3781 When Others Then
3782 --
3783 -- An unhandled or unexpected error has occurred which
3784 -- we must report
3785 --
3786 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
3787 fnd_message.set_token('PROCEDURE', l_proc);
3788 fnd_message.set_token('STEP','15');
3789 fnd_message.raise_error;
3790 End dt_update_validate;
3791 --
3792 -- ----------------------------------------------------------------------------
3793 -- |--------------------------< dt_delete_validate >--------------------------|
3794 -- ----------------------------------------------------------------------------
3795 -- {Start Of Comments}
3796 --
3797 -- Description:
3798 -- This procedure is used for referential integrity of datetracked
3799 -- child entities when either a datetrack DELETE or ZAP is in operation
3800 -- and where there is no cascading of delete defined for this entity.
3801 -- For the datetrack mode of DELETE or ZAP we must ensure that no
3802 -- datetracked child rows exist between the validation start and end
3803 -- dates.
3804 --
3805 -- Prerequisites:
3806 -- This procedure is called from the delete_validate.
3807 --
3808 -- In Parameters:
3809 --
3810 -- Post Success:
3811 -- Processing continues.
3812 --
3813 -- Post Failure:
3814 -- If a row exists by determining the returning Boolean value from the
3815 -- generic dt_api.rows_exist function then we must supply an error via
3816 -- the use of the local exception handler l_rows_exist.
3817 --
3818 -- Developer Implementation Notes:
3819 -- This procedure should not need maintenance unless the HR Schema model
3820 -- changes.
3821 --
3822 -- Access Status:
3823 -- Internal Row Handler Use Only.
3824 --
3825 -- {End Of Comments}
3826 -- ----------------------------------------------------------------------------
3827 Procedure dt_delete_validate
3828 (p_prtt_enrt_rslt_id in number,
3829 p_datetrack_mode in varchar2,
3830 p_effective_date in date,
3831 p_validation_start_date in date,
3832 p_validation_end_date in date) Is
3833
3834 l_proc varchar2(72) := g_package||'dt_delete_validate';
3835 l_rows_exist Exception;
3836 l_table_name all_tables.table_name%TYPE;
3837
3838 Begin
3839 hr_utility.set_location('Entering:'||l_proc, 5);
3840 --
3841 -- Ensure that the p_datetrack_mode argument is not null
3842 --
3843 hr_api.mandatory_arg_error
3844 (p_api_name => l_proc,
3845 p_argument => 'datetrack_mode',
3846 p_argument_value => p_datetrack_mode);
3847 --
3848 -- Only perform the validation if the datetrack mode is either
3849 -- DELETE or ZAP
3850 --
3851 If (p_datetrack_mode = 'DELETE' or
3852 p_datetrack_mode = 'ZAP') then
3853 --
3854 -- Ensure the arguments are not null
3855 --
3856 hr_api.mandatory_arg_error
3857 (p_api_name => l_proc,
3858 p_argument => 'validation_start_date',
3859 p_argument_value => p_validation_start_date);
3860
3861 hr_api.mandatory_arg_error
3862 (p_api_name => l_proc,
3863 p_argument => 'validation_end_date',
3864 p_argument_value => p_validation_end_date);
3865
3866 hr_api.mandatory_arg_error
3867 (p_api_name => l_proc,
3868 p_argument => 'prtt_enrt_rslt_id',
3869 p_argument_value => p_prtt_enrt_rslt_id);
3870 /*
3871 If (dt_api.rows_exist
3872 (p_base_table_name => 'ben_bnft_prvdd_ldgr_f',
3873 p_base_key_column => 'prtt_enrt_rslt_id',
3874 p_base_key_value => p_prtt_enrt_rslt_id,
3875 p_from_date => p_validation_start_date,
3876 p_to_date => p_validation_end_date)) Then
3877 l_table_name := 'ben_bnft_prvdd_ldgr_f';
3878 Raise l_rows_exist;
3879 End If;
3880 */
3881 If (dt_api.rows_exist
3882 (p_base_table_name => 'ben_pl_bnf_f',
3883 p_base_key_column => 'prtt_enrt_rslt_id',
3884 p_base_key_value => p_prtt_enrt_rslt_id,
3885 p_from_date => p_validation_start_date,
3886 p_to_date => p_validation_end_date)) Then
3887 l_table_name := 'ben_pl_bnf_f';
3888 Raise l_rows_exist;
3889 End If;
3890
3891 If p_effective_date < p_validation_start_date then
3892 -- Added the above condition for bug 3646239
3893 If (dt_api.rows_exist
3894 (p_base_table_name => 'ben_elig_cvrd_dpnt_f',
3895 p_base_key_column => 'prtt_enrt_rslt_id',
3896 p_base_key_value => p_prtt_enrt_rslt_id,
3897 p_from_date => p_validation_start_date,
3898 p_to_date => p_validation_end_date)) Then
3899 l_table_name := 'ben_elig_cvrd_dpnt_f';
3900 Raise l_rows_exist;
3901 End If;
3902 End If;
3903
3904 If (dt_api.rows_exist
3905 (p_base_table_name => 'ben_prtt_enrt_actn_f',
3906 p_base_key_column => 'prtt_enrt_rslt_id',
3907 p_base_key_value => p_prtt_enrt_rslt_id,
3908 p_from_date => p_validation_start_date,
3909 p_to_date => p_validation_end_date)) Then
3910 l_table_name := 'ben_prtt_enrt_actn_f';
3911 Raise l_rows_exist;
3912 End If;
3913 If (dt_api.rows_exist
3914 (p_base_table_name => 'ben_prtt_enrt_ctfn_prvdd_f',
3915 p_base_key_column => 'prtt_enrt_rslt_id',
3916 p_base_key_value => p_prtt_enrt_rslt_id,
3917 p_from_date => p_validation_start_date,
3918 p_to_date => p_validation_end_date)) Then
3919 l_table_name := 'ben_prtt_enrt_ctfn_prvdd_f';
3920 Raise l_rows_exist;
3921 End If;
3922 End If;
3923 --
3924 hr_utility.set_location(' Leaving:'||l_proc, 10);
3925 Exception
3926 When l_rows_exist Then
3927 --
3928 -- A referential integrity check was violated therefore
3929 -- we must error
3930 --
3931 ben_utility.child_exists_error(p_table_name => l_table_name);
3932 --
3933 When Others Then
3934 --
3935 -- An unhandled or unexpected error has occurred which
3936 -- we must report
3937 --
3938 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
3939 fnd_message.set_token('PROCEDURE', l_proc);
3940 fnd_message.set_token('STEP','15');
3941 fnd_message.raise_error;
3942 End dt_delete_validate;
3943 --
3944 -- ----------------------------------------------------------------------------
3945 -- |---------------------------< insert_validate >----------------------------|
3946 -- ----------------------------------------------------------------------------
3947 Procedure insert_validate
3948 (p_rec in ben_pen_shd.g_rec_type,
3949 p_effective_date in date,
3950 p_datetrack_mode in varchar2,
3951 p_validation_start_date in date,
3952 p_validation_end_date in date) is
3953 --
3954 l_proc varchar2(72) := g_package||'insert_validate';
3955 --
3956 Begin
3957 hr_utility.set_location('Entering:'||l_proc, 5);
3958 --
3959 -- Call context sensitive validate bgp cache routine
3960 --
3961 ben_batch_dt_api.batch_validate_bgp_id
3962 (p_business_group_id => p_rec.business_group_id
3963 );
3964 --
3965 /*
3966 --
3967 -- Call all supporting business operations
3968 --
3969 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
3970 */
3971 chk_prtt_enrt_rslt_id
3972 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
3973 p_effective_date => p_effective_date,
3974 p_object_version_number => p_rec.object_version_number);
3975 --
3976 chk_enrt_ovridn_flag
3977 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
3978 p_enrt_ovridn_flag => p_rec.enrt_ovridn_flag,
3979 p_effective_date => p_effective_date,
3980 p_object_version_number => p_rec.object_version_number);
3981 --
3982 chk_no_lngr_elig_flag
3983 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
3984 p_no_lngr_elig_flag => p_rec.no_lngr_elig_flag,
3985 p_effective_date => p_effective_date,
3986 p_object_version_number => p_rec.object_version_number);
3987 --
3988 chk_enrt_mthd_cd
3989 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
3990 p_enrt_mthd_cd => p_rec.enrt_mthd_cd,
3991 p_effective_date => p_effective_date,
3992 p_object_version_number => p_rec.object_version_number);
3993 --
3994 chk_uom
3995 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
3996 p_uom => p_rec.uom ,
3997 p_effective_date => p_effective_date,
3998 p_object_version_number => p_rec.object_version_number);
3999 --
4000 chk_prtt_is_cvrd_flag
4001 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4002 p_prtt_is_cvrd_flag => p_rec.prtt_is_cvrd_flag,
4003 p_effective_date => p_effective_date,
4004 p_object_version_number => p_rec.object_version_number);
4005 --
4006 chk_sspndd_flag
4007 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4008 p_sspndd_flag => p_rec.sspndd_flag,
4009 p_effective_date => p_effective_date,
4010 p_object_version_number => p_rec.object_version_number);
4011 --
4012 chk_comp_lvl_cd
4013 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4014 p_comp_lvl_cd => p_rec.comp_lvl_cd,
4015 p_effective_date => p_effective_date,
4016 p_object_version_number => p_rec.object_version_number);
4017
4018 --
4019 chk_bnft_nnmntry_uom
4020 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4021 p_bnft_nnmntry_uom => p_rec.bnft_nnmntry_uom,
4022 p_effective_date => p_effective_date,
4023 p_object_version_number => p_rec.object_version_number);
4024 --
4025 chk_bnft_typ_cd
4026 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4027 p_bnft_typ_cd => p_rec.bnft_typ_cd,
4028 p_effective_date => p_effective_date,
4029 p_object_version_number => p_rec.object_version_number);
4030 --
4031 chk_prtt_enrt_rslt_stat_cd
4032 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4033 p_prtt_enrt_rslt_stat_cd => p_rec.prtt_enrt_rslt_stat_cd,
4034 p_effective_date => p_effective_date,
4035 p_object_version_number => p_rec.object_version_number);
4036 --
4037 --
4038 chk_enrt_ovrid_rsn_cd
4039 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4040 p_enrt_ovrid_rsn_cd => p_rec.enrt_ovrid_rsn_cd,
4041 p_effective_date => p_effective_date,
4042 p_object_version_number => p_rec.object_version_number);
4043 --
4044 --
4045 -- RCHASE - Bug#1412801 - Moved post election edit rule from insert/update
4046 -- RCHASE validation calls to multi-row edit
4047 --chk_post_elcn_rl
4048 --(p_oipl_id => p_rec.oipl_id,
4049 -- p_pl_id => p_rec.pl_id,
4050 -- p_ptip_id => p_rec.ptip_id,
4051 -- p_pgm_id => p_rec.pgm_id,
4052 -- p_pl_typ_id => p_rec.pl_typ_id,
4053 -- p_person_id => p_rec.person_id,
4054 -- p_effective_date => p_effective_date,
4055 -- p_business_group_id => p_rec.business_group_id);
4056 --
4057 crt_ordr_warning
4058 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id
4059 ,p_per_in_ler_id => p_rec.per_in_ler_id /* Bug 4766655 */
4060 ,p_person_id => p_rec.person_id
4061 ,p_pgm_id => p_rec.pgm_id
4062 ,p_pl_id => p_rec.pl_id
4063 ,p_ptip_id => p_rec.ptip_id
4064 ,p_pl_typ_id => p_rec.pl_typ_id
4065 ,p_effective_date => p_effective_date
4066 ,p_enrt_cvg_strt_dt => p_rec.enrt_cvg_strt_dt
4067 ,p_enrt_cvg_thru_dt => p_rec.enrt_cvg_thru_dt
4068 ,p_business_group_id => p_rec.business_group_id);
4069 --
4070 /*chk_cvg_strt_end_dt
4071 (p_enrt_cvg_strt_dt => p_rec.enrt_cvg_strt_dt,
4072 p_enrt_cvg_thru_dt => p_rec.enrt_cvg_thru_dt,
4073 p_person_id => p_rec.person_id);*/
4074 --
4075 hr_utility.set_location(' Leaving:'||l_proc, 10);
4076 End insert_validate;
4077 --
4078 -- ----------------------------------------------------------------------------
4079 -- |---------------------------< update_validate >----------------------------|
4080 -- ----------------------------------------------------------------------------
4081 Procedure update_validate
4082 (p_rec in ben_pen_shd.g_rec_type,
4083 p_effective_date in date,
4084 p_datetrack_mode in varchar2,
4085 p_validation_start_date in date,
4086 p_validation_end_date in date) is
4087 --
4088 l_proc varchar2(72) := g_package||'update_validate';
4089 --
4090 Begin
4091 hr_utility.set_location('Entering:'||l_proc, 5);
4092 --
4093 -- Call context sensitive validate bgp cache routine
4094 --
4095 ben_batch_dt_api.batch_validate_bgp_id
4096 (p_business_group_id => p_rec.business_group_id
4097 );
4098 --
4099 /*
4100 --
4101 -- Call all supporting business operations
4102 --
4103 hr_api.validate_bus_grp_id(p_rec.business_group_id); -- Validate Bus Grp
4104 */
4105 chk_prtt_enrt_rslt_id
4106 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4107 p_effective_date => p_effective_date,
4108 p_object_version_number => p_rec.object_version_number);
4109 --
4110 chk_enrt_ovridn_flag
4111 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4112 p_enrt_ovridn_flag => p_rec.enrt_ovridn_flag,
4113 p_effective_date => p_effective_date,
4114 p_object_version_number => p_rec.object_version_number);
4115 --
4116 chk_no_lngr_elig_flag
4117 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4118 p_no_lngr_elig_flag => p_rec.no_lngr_elig_flag,
4119 p_effective_date => p_effective_date,
4120 p_object_version_number => p_rec.object_version_number);
4121 --
4122 chk_enrt_mthd_cd
4123 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4124 p_enrt_mthd_cd => p_rec.enrt_mthd_cd,
4125 p_effective_date => p_effective_date,
4126 p_object_version_number => p_rec.object_version_number);
4127 --
4128 chk_uom
4129 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4130 p_uom => p_rec.uom ,
4131 p_effective_date => p_effective_date,
4132 p_object_version_number => p_rec.object_version_number);
4133 --
4134 chk_prtt_is_cvrd_flag
4135 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4136 p_prtt_is_cvrd_flag => p_rec.prtt_is_cvrd_flag,
4137 p_effective_date => p_effective_date,
4138 p_object_version_number => p_rec.object_version_number);
4139 --
4140 chk_sspndd_flag
4141 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4142 p_sspndd_flag => p_rec.sspndd_flag,
4143 p_effective_date => p_effective_date,
4144 p_object_version_number => p_rec.object_version_number);
4145 --
4146 chk_comp_lvl_cd
4147 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4148 p_comp_lvl_cd => p_rec.comp_lvl_cd,
4149 p_effective_date => p_effective_date,
4150 p_object_version_number => p_rec.object_version_number);
4151
4152 chk_bnft_nnmntry_uom
4153 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4154 p_bnft_nnmntry_uom => p_rec.bnft_nnmntry_uom,
4155 p_effective_date => p_effective_date,
4156 p_object_version_number => p_rec.object_version_number);
4157 --
4158 chk_bnft_typ_cd
4159 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4160 p_bnft_typ_cd => p_rec.bnft_typ_cd,
4161 p_effective_date => p_effective_date,
4162 p_object_version_number => p_rec.object_version_number);
4163 --
4164 chk_prtt_enrt_rslt_stat_cd
4165 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4166 p_prtt_enrt_rslt_stat_cd => p_rec.prtt_enrt_rslt_stat_cd,
4167 p_effective_date => p_effective_date,
4168 p_object_version_number => p_rec.object_version_number);
4169 --
4170 --
4171 chk_enrt_ovrid_rsn_cd
4172 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4173 p_enrt_ovrid_rsn_cd => p_rec.enrt_ovrid_rsn_cd,
4174 p_effective_date => p_effective_date,
4175 p_object_version_number => p_rec.object_version_number);
4176 --
4177 -- RCHASE - Bug#1412801 - Moved post election edit rule from insert/update
4178 -- RCHASE validation calls to multi-row edit
4179 --chk_post_elcn_rl
4180 --(p_oipl_id => p_rec.oipl_id,
4181 -- p_pl_id => p_rec.pl_id,
4182 -- p_ptip_id => p_rec.ptip_id,
4183 -- p_pgm_id => p_rec.pgm_id,
4184 -- p_pl_typ_id => p_rec.pl_typ_id,
4185 -- p_person_id => p_rec.person_id,
4186 -- p_effective_date => p_effective_date,
4187 -- p_business_group_id => p_rec.business_group_id);
4188 --
4189 crt_ordr_warning
4190 (p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id
4191 ,p_per_in_ler_id => p_rec.per_in_ler_id /* Bug 4766655 */
4192 ,p_person_id => p_rec.person_id
4193 ,p_pgm_id => p_rec.pgm_id
4194 ,p_pl_id => p_rec.pl_id
4195 ,p_ptip_id => p_rec.ptip_id
4196 ,p_pl_typ_id => p_rec.pl_typ_id
4197 ,p_effective_date => p_effective_date
4198 ,p_enrt_cvg_strt_dt => p_rec.enrt_cvg_strt_dt
4199 ,p_enrt_cvg_thru_dt => p_rec.enrt_cvg_thru_dt
4200 ,p_business_group_id => p_rec.business_group_id);
4201 --
4202 --
4203 -- Call the datetrack update integrity operation
4204 --
4205 dt_update_validate
4206 (p_pgm_id => p_rec.pgm_id,
4207 p_oipl_id => p_rec.oipl_id,
4208 p_pl_id => p_rec.pl_id,
4209 p_per_in_ler_id => p_rec.per_in_ler_id,
4210 p_pl_typ_id => p_rec.pl_typ_id,
4211 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id,
4212 p_datetrack_mode => p_datetrack_mode,
4213 p_validation_start_date => p_validation_start_date,
4214 p_validation_end_date => p_validation_end_date);
4215 --
4216 /*chk_cvg_strt_end_dt
4217 (p_enrt_cvg_strt_dt => p_rec.enrt_cvg_strt_dt,
4218 p_enrt_cvg_thru_dt => p_rec.enrt_cvg_thru_dt,
4219 p_person_id => p_rec.person_id);*/
4220 --
4221 hr_utility.set_location(' Leaving:'||l_proc, 10);
4222 End update_validate;
4223 --
4224 -- ----------------------------------------------------------------------------
4225 -- |---------------------------< delete_validate >----------------------------|
4226 -- ----------------------------------------------------------------------------
4227 Procedure delete_validate
4228 (p_rec in ben_pen_shd.g_rec_type,
4229 p_effective_date in date,
4230 p_datetrack_mode in varchar2,
4231 p_validation_start_date in date,
4232 p_validation_end_date in date) is
4233 --
4234 l_proc varchar2(72) := g_package||'delete_validate';
4235 --
4236 Begin
4237 hr_utility.set_location('Entering:'||l_proc, 5);
4238 --
4239 -- Call all supporting business operations
4240 --
4241 dt_delete_validate
4242 (p_datetrack_mode => p_datetrack_mode,
4243 p_validation_start_date => p_validation_start_date,
4244 p_validation_end_date => p_validation_end_date,
4245 p_effective_date => p_effective_date, -- Added for bug 3646239
4246 p_prtt_enrt_rslt_id => p_rec.prtt_enrt_rslt_id);
4247 --
4248 hr_utility.set_location(' Leaving:'||l_proc, 10);
4249 End delete_validate;
4250 --
4251 --
4252 -- ---------------------------------------------------------------------------
4253 -- |---------------------< return_legislation_code >-------------------------|
4254 -- ---------------------------------------------------------------------------
4255 --
4256 function return_legislation_code
4257 (p_prtt_enrt_rslt_id in number) return varchar2 is
4258 --
4259 -- Declare cursor
4260 --
4261 cursor csr_leg_code is
4262 select a.legislation_code
4263 from per_business_groups a,
4264 ben_prtt_enrt_rslt_f b
4265 where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4266 and a.business_group_id = b.business_group_id;
4267 --
4268 -- Declare local variables
4269 --
4270 l_legislation_code varchar2(150);
4271 l_proc varchar2(72) := g_package||'return_legislation_code';
4272 --
4273 begin
4274 --
4275 hr_utility.set_location('Entering:'|| l_proc, 10);
4276 --
4277 -- Ensure that all the mandatory parameter are not null
4278 --
4279 hr_api.mandatory_arg_error(p_api_name => l_proc,
4280 p_argument => 'prtt_enrt_rslt_id',
4281 p_argument_value => p_prtt_enrt_rslt_id);
4282 --
4283 open csr_leg_code;
4284 --
4285 fetch csr_leg_code into l_legislation_code;
4286 --
4287 if csr_leg_code%notfound then
4288 --
4289 close csr_leg_code;
4290 --
4291 -- The primary key is invalid therefore we must error
4292 --
4293 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
4294 fnd_message.raise_error;
4295 --
4296 end if;
4297 --
4298 close csr_leg_code;
4299 --
4300 hr_utility.set_location(' Leaving:'|| l_proc, 20);
4301 --
4302 return l_legislation_code;
4303 --
4304 end return_legislation_code;
4305 --
4306 end ben_pen_bus;