1 PACKAGE BODY ben_evaluate_dpnt_elg_profiles as
2 /* $Header: bendpelg.pkb 120.5.12010000.6 2008/08/05 14:40:00 ubhat ship $ */
3 -----------------------------------------------------------------------
4 /*
5 +==============================================================================+
6 | Copyright (c) 1998 Oracle Corporation |
7 | Redwood Shores, California, USA |
8 | All rights reserved. |
9 +==============================================================================+
10 Name
11 Manage Dependent Eligibility
12 Purpose
13 This package is used to determine if a specific dependent is eligible for
14 a specific electable choice for a participant. It returns the eligibility
15 in the p_dependent_eligible_flag as an out nocopy parameter.
16 History
17 Date Who Version What?
18 ---- --- ------- -----
19 09 Apr 98 M Rosen/JM 110.0 Created.
20 03 Jun 98 J Mohapatra Replaced age calculation with a new
21 procedure call.
22 24 Nov 98 jcarpent 115.3 Check rltd_per_rsds_w_dsgntr_flag
23 Get dsgntr address if Y
24 15 Dec 98 stee 115.4 Schema changes to
25 ben_dsgn_rqmt_f.
26 21 Dec 98 jcarpent 115.5 Track date of elig change
27 18 Jan 99 G Perry 115.6 LED V ED
28 16 Apr 99 jcarpent 115.8 Added call to dpnt_cvg_elig_det_rl
29 06 May 99 shdas 115.9 Added contexts to rule calls.
30 17 May 99 jcarpent 115.10 Handle dob of null
31 25 May 99 stee 115.11 Add new eligibility criteria
32 for COBRA.
33 06 Jun 99 stee 115.12 Fix check_cvrd_anthr_pl_cvg
34 to check for all plans in
35 the profile.
36 12 Jul 99 tguy 115.13 fixed from and to check for
37 postal zip range.
38 20-JUL-99 Gperry 115.14 genutils -> benutils package
39 rename.
40 30-Aug-99 maagrawa 115.15 Added p_dpnt_inelig_rsn_cd
41 to procedure main.
42 Added p_inelig_rsn_cd to
43 all check procedures.
44 07-Sep-99 tguy 115.16 fixed calls to pay_mag_util
45 14-Oct-99 Gperry 115.17 Changed dependent address
46 error to use the generic
47 exception rather than
48 fnd_message.raise_error
49 exception. Changed error
50 message to explain error
51 more thouroughly.
52 11-Nov-99 mhoyes 115.18 - Added trace messages.
53 11-Nov-99 Gperry 115.19 Fixed bug 3665.
54 Made l_age variable number
55 rather than number(15);
56 30-Nov-99 lmcdonal 115.20 Debugging messages.
57 12-dec-99 pbodla 115.21 - l_contact.person_id is
58 passed, as input value when
59 dpnt_cvg_elig_det_rl evaluated.
60 As the assignment id may not be
61 available all the time. This is
62 a work around to access the
63 contact person data. Customer
64 have to write the formula function.
65 20-dec-99 maagrawa 115.22 The dependent becomes ineligible
66 when the contact relationship
67 record ends. (Bug 4028)
68 28-jan-00 lmcdonal 115.23 Bug 1167262. Find ptip dpnt elig
69 profiles when processing plans
70 and oipls. Added c_ptip cursor.
71 08-feb-00 maagrawa 115.24 Set the g_elig_change_dt to
72 the date when the dpnt. crosses
73 the limit.(1178670)
74 09-feb-00 stee 115.25 Fix invalid cursor in
75 dsgntr_enrld_cvg_elig profile.
76 WWBUG# 1189088.
77 12-Feb-00 maagrawa 115.26 Default the g_elig_change_dt to
78 nvl(p_lf_evt_ocrd_dt,
79 p_effective_date)
80 24-Feb-00 maagrawa 115.27 Dpnt. having max age should be
81 ineligible. (1207798).
82 28-Feb-00 gperry 115.28 Added use of dpnt profile flags.
83 for performance.
84 07-Mar-00 pbodla 115.29 Bug 3531 : p_contact_person_id
85 passed to formula call.
86 31-Mar-00 maagrawa 115.30 For optional profiles, get the
87 latest date when eligibility
88 ends. (1244531).
89 The ineligibiltye date should
90 be between coverage start date
91 and effective_date (4929).
92 05-Apr-00 mmogel 115.31 Added tokens to messages to
93 make them more meaningful to
94 the user
95 26-Jun-00 gperry 115.32 Added p_contact_person_id to
96 drive off parent information
97 when needed.
98 30-Jul-00 mhoyes 115.33 - Tuned c_ade. Removed nvls and
99 business_group_id restrictions.
100 - Removed nvls and + 0s from cursors
101 30-nov-00 tilak 115.34 bug 1522219
102 01-Dec-00 mhoyes 115.35 - Fixed bug 1511643. Changed
103 check_military_elig to handle null
104 values.
105
106 04-dec-00 tilak 115.36 bug 1522219
107 07-dec-00 rchase 115.37 Bug 1518211. Make p_dob an out
108 parameter.
109 Lepfrog version based on 115.33
110 07-dec-00 jcarpent 115.38 Merge version of 115.37+115.36.
111 07-dec-00 tilak 115.39 bug : 1096978 address and sharing prtt addr
112 validated when postal teria is validated
113 15-jan-01 tilak 115.40 bug : 1540610 >= min and < max age validation is
114 change to >= min and < max+1
115 29-aug-01 pbodla 115.41 bug:1949361 jurisdiction code is
116 derived inside benutils.formula
117 18-Sep-01 ikasire 115.42 bug 1977901 fixed the logic in max/min
118 age validation
119 28-sep-01 tjesumic 115.43 bug : 1638727 fixed , error message for
120 depnt address is reomoved,dpnt wont be eligible
121 if address is not defined
122 30-Nov-01 ikasire 115.44 Bug 2101937 Ceil condition replace with a if
123 condition to handle wholes numbers and decimal
124 numbers seperately not to break the existing
125 functionality and to support decimal ranges in
126 derived factors.
127 15-Dec-01 ikasire 115.45 Added dbdrv lines.
128 Bug 2100564 - fixed the Exclude flag
129 functionality in the profiles like done in
130 bendetel and benrtprf.
131 08-Jun-02 pabodla 115.46 Do not select the contingent worker
132 assignment when assignment data is
133 fetched.
134 10-Oct-03 kmahendr 115.49 Performance fix - check_age_elig moved
135 down and check_contact_elig moved up.
136 13-aug-04 tjesumic 115.50 fonm changes
137 07-Feb-05 kmahendr 115.51 Bug#4157836 - the ineligible date on account
138 of contact type based on person table reversed
139 01-Apr-05 swjain 115.52 Bug 4271143 -- Added check to calculate apld_dpnt_cvg_elig_rl
140 since if Coverage Eligibility Rule is specified, then it should also
141 evaluate to true alongwith eligibility profile. Also added one more cursor c_ade1
142 in procedure main.
143 09-May-05 kmahendr 115.53 Bug#4318031 - added cursor contact2 and added
144 codes to check_contact_elig
145 07-Jun-05 kmahendr 115.54 Bug#4399894-added c_previous_per to check
146 previous value
147 13-jun-05 kmahendr 115.55 Fixes for other eligibility viz military,
148 student and marital
149 19-aug-05 ssarkar 115.56 bug 4546890 : set_elig_change_dt is called for rule evaluation of eligibility.
150 and modified proc set_elig_change_dt for FONM case.
151 24-Oct-07 swjain 115.57 Bug 6520270: Made changes in set_elig_change_dt
152 14-Nov-07 swjain 115.58 Bug 6615978: Updated procedure check_age_elig. Ineligibility date is calculated
153 as per the profile value set now.
154 14-jan-08 rtagarra 115.59 Bug 6738429: Added prtt_enrt_rslt_stat_cd clause in
155 Cursor c_get_elig_cvrd_dpnt for the issue in contact person
156 covered in other plan.
157
158 06-mar-08 bachakra 115.61 Bug 6870564: Corrected the prtt_enrt_rslt_stat_cd clause in cursor
159 c_get_prtt_enrt_rslt in the procedure
160 check_dsgntr_enrld_cvg_elig
161 22-APR-08 stee 115.62 Bug 6956648: Change cursor c_contact in check_contact_elig
162 to only check contact for the person.
163
164 */
165 -----------------------------------------------------------------------
166 --
167 -- Global to track eligibility change date
168 --
169 g_elig_change_dt date;
170 g_dpnt_cvg_strt_dt date;
171 g_effective_date date;
172 --
173 procedure set_elig_change_dt(p_elig_change_dt in date,
174 p_effective_date in date) is
175 --
176 l_proc varchar2(80) := g_package || '.set_elig_change_dt';
177 --
178 begin
179 --
180 hr_utility.set_location ('Entering '||l_proc,10);
181 hr_utility.set_location ('p_elig_change_dt '||p_elig_change_dt,10);
182 hr_utility.set_location ('g_elig_change_dt '||g_elig_change_dt,10);
183 hr_utility.set_location ('g_effective_date '||g_effective_date,10);
184 hr_utility.set_location ('** SUP p_effective_date '||p_effective_date,10);
185 hr_utility.set_location ('g_dpnt_cvg_strt_dt '||g_dpnt_cvg_strt_dt,10);
186 --
187 -- If dpnt. coverage start date is specified then, the elig change date
188 -- cannot be before that.
189 -- Additionally
190 -- - Mandatory profiles are checked first and once any of them are found
191 -- ineligible, we stop with that date. So, if it happens, it will be the
192 -- first call to this procedure and will never be called again.
193 -- - If optional profiles are found ineligible, we need to find the latest
194 -- day when all the optional profiles become ineligible. So, we update the
195 -- date only if the new date is greater than the old one.
196
197 -- bug 4546890 : compare p_elig_change_dt with the
198 -- l_effective_date which is the l_fonm_cvg_strt_dt rather than the life event
199 -- occured date,in case FONM flag = 'Y'.Otherwise compare with life event occured date .
200 -- SO changed g_effective_date , which always hold life event occured date , to p_effective_date
201
202 if (g_dpnt_cvg_strt_dt is null or
203 p_elig_change_dt >= g_dpnt_cvg_strt_dt) and
204 (g_elig_change_dt is null or
205 p_elig_change_dt > g_elig_change_dt) then
206 --
207 -- Bug 6520270: If p_elig_change_dt > p_effective_date, that is employee is losing eligibility in furture
208 -- then set the g_elig_change_dt to effective_date -1
209 --
210 if (p_elig_change_dt <= p_effective_date) then
211 g_elig_change_dt := p_elig_change_dt;
212 else
216 -- End 6520270
213 g_elig_change_dt := p_effective_date - 1;
214 end if;
215 --
217 --
218 hr_utility.set_location (' Setting g_elig_change_dt '||g_elig_change_dt,10);
219 end if;
220 --
221 hr_utility.set_location ('Leaving '||l_proc,10);
222 --
223 end set_elig_change_dt;
224 --
225 --
226 procedure main
227 (p_contact_relationship_id in number,
228 p_contact_person_id in number,
229 p_pgm_id in number default null,
230 p_pl_id in number,
231 p_ptip_id in number default null,
232 p_oipl_id in number default null,
233 p_business_group_id in number,
234 p_per_in_ler_id in number,
235 p_effective_date in date,
236 p_lf_evt_ocrd_dt in date,
237 p_dpnt_cvg_strt_dt in date default null,
238 p_level in varchar2 default null,
239 p_dependent_eligible_flag out nocopy varchar2,
240 p_dpnt_inelig_rsn_cd out nocopy varchar2) is
241 --
242 l_proc varchar2(80) := g_package || '.main';
243 l_level varchar2(30) := 'PLAN';
244 l_eligible_flag varchar2(30) := 'Y';
245 l_apld_eligible_flag varchar2(30) := 'Y';
246 l_inelig_rsn_cd varchar2(30) := null;
247 l_assignment_id number;
248 l_organization_id number;
249 l_region_2 hr_locations_all.region_2%type;
250 l_outputs ff_exec.outputs_t;
251 l_jurisdiction_code varchar2(30);
252 l_effective_date date ;
253 --
254 l_exists varchar2(1);
255 l_return varchar2(30);
256 --
257 l_fonm_cvg_strt_dt date ;
258 -- More cursors - this may go away when caching is used
259 --
260 cursor c_pgm
261 (c_effective_date in date
262 )
263 is
264 select dpnt_dsgn_lvl_cd,
265 dpnt_dsgn_cd,
266 dpnt_cvg_strt_dt_cd,
267 dpnt_cvg_strt_dt_rl,
268 dpnt_cvg_end_dt_cd,
269 dpnt_cvg_end_dt_rl
270 from ben_pgm_f pgm
271 where pgm.pgm_id = p_pgm_id
272 and c_effective_date
273 between pgm.effective_start_date
274 and pgm.effective_end_date;
275 --
276 l_pgm c_pgm%rowtype;
277 --
278 cursor c_contact
279 (c_effective_date in date
280 )
281 is
282 select contact_person_id,
283 contact_relationship_id,
284 contact_type,
285 personal_flag,
286 rltd_per_rsds_w_dsgntr_flag,
287 person_id,
288 date_end
289 from per_contact_relationships ctr
290 where ctr.contact_relationship_id = p_contact_relationship_id
291 and c_effective_date >=
292 nvl(date_start,c_effective_date);
293 --
294 l_contact c_contact%rowtype;
295 --
296 cursor c_contact2 (p_effective_date in date)
297 is
298 select contact_type
299 from per_contact_relationships ctr
300 where ctr.contact_relationship_id <> p_contact_relationship_id
301 and ctr.contact_person_id = p_contact_person_id
302 and ctr.personal_flag = 'Y'
303 and p_effective_date between nvl(ctr.date_start,p_effective_date)
304 and nvl(ctr.date_end,p_effective_date);
305 --
306 l_contact_type2 varchar2(300);
307 --
308 cursor c_dsgn
309 (c_effective_date in date
310 )
311 is
312 select null
313 from ben_dsgn_rqmt_f ddr
314 where (ddr.oipl_id = p_oipl_id
315 or ddr.pl_id = p_pl_id
316 or ddr.opt_id = (select oipl.opt_id
317 from ben_oipl_f oipl
318 where oipl.oipl_id = p_oipl_id
319 and oipl.business_group_id =
320 p_business_group_id
321 and c_effective_date
322 between oipl.effective_start_date
323 and oipl.effective_end_date))
324 and ddr.dsgn_typ_cd = 'DPNT'
325 and ddr.business_group_id = p_business_group_id
326 and c_effective_date
327 between ddr.effective_start_date
328 and ddr.effective_end_date;
329 --
330 cursor c_dsgn_rl_typ
331 (c_effective_date in date
332 ,p_contact_type in varchar2
333 )
334 is
335 select null
336 from ben_dsgn_rqmt_f ddr, ben_dsgn_rqmt_rlshp_typ rl
337 where ddr.dsgn_rqmt_id = rl.dsgn_rqmt_id(+)
338 and rl.rlshp_typ_cd = p_contact_type
339 and (ddr.oipl_id = p_oipl_id
340 or ddr.pl_id = p_pl_id
341 or ddr.opt_id = (select oipl.opt_id
342 from ben_oipl_f oipl
343 where oipl.oipl_id = p_oipl_id
344 and oipl.business_group_id =
348 and oipl.effective_end_date))
345 p_business_group_id
346 and c_effective_date
347 between oipl.effective_start_date
349 and ddr.dsgn_typ_cd = 'DPNT'
350 -- Commented and added new clause for Bug fix - 1859111
351 -- and (nvl(ddr.mn_dpnts_rqd_num,-1) <> 0
352 -- and nvl(ddr.mx_dpnts_alwd_num,-1) <> 0)
353 and ( (nvl(ddr.mn_dpnts_rqd_num,-1) = -1 and ddr.no_mn_num_dfnd_flag = 'Y')
354 or ( ddr.mn_dpnts_rqd_num is not null and ddr.no_mn_num_dfnd_flag = 'N') )
355 and ( (nvl(ddr.mx_dpnts_alwd_num,-1) = -1 and ddr.no_mx_num_dfnd_flag = 'Y')
356 or ( ddr.mx_dpnts_alwd_num <> 0 and ddr.no_mx_num_dfnd_flag = 'N') )
357 -- End of Bug fix - 1859111
358 and ddr.business_group_id = p_business_group_id
359 and c_effective_date
360 between ddr.effective_start_date
361 and ddr.effective_end_date;
362 --
363 cursor c_dsgn_grp
364 (c_effective_date in date
365 )
366 is
367 select null
368 from ben_dsgn_rqmt_f ddr
369 where ddr.grp_rlshp_cd is null
370 and (ddr.oipl_id = p_oipl_id
371 or ddr.pl_id = p_pl_id
372 or ddr.opt_id = (select oipl.opt_id
373 from ben_oipl_f oipl
374 where oipl.oipl_id = p_oipl_id
375 and oipl.business_group_id =
376 p_business_group_id
377 and c_effective_date
378 between oipl.effective_start_date
379 and oipl.effective_end_date))
380 and ddr.dsgn_typ_cd = 'DPNT'
381 -- Commented and added new clause for Bug fix - 1859111
382 -- and (nvl(ddr.mn_dpnts_rqd_num,-1) <> 0
383 -- and nvl(ddr.mx_dpnts_alwd_num,-1) <> 0)
384 and ( (nvl(ddr.mn_dpnts_rqd_num,-1) = -1 and ddr.no_mn_num_dfnd_flag = 'Y')
385 or ( ddr.mn_dpnts_rqd_num is not null and ddr.no_mn_num_dfnd_flag = 'N') )
386 and ( (nvl(ddr.mx_dpnts_alwd_num,-1) = -1 and ddr.no_mx_num_dfnd_flag = 'Y')
387 or ( ddr.mx_dpnts_alwd_num <> 0 and ddr.no_mx_num_dfnd_flag = 'N') )
388 -- End of Bug fix - 1859111
389 and ddr.business_group_id = p_business_group_id
390 and c_effective_date
391 between ddr.effective_start_date
392 and ddr.effective_end_date;
393 --
394 cursor c_dsgn_not_rl_typ
395 (c_effective_date in date
396 )
397 is
398 select null
399 from ben_dsgn_rqmt_f ddr,
400 ben_dsgn_rqmt_rlshp_typ rl
401 where ddr.dsgn_rqmt_id = rl.dsgn_rqmt_id(+)
402 and ddr.grp_rlshp_cd is not null
403 and rl.rlshp_typ_cd <> l_contact.contact_type
404 and (ddr.oipl_id = p_oipl_id
405 or ddr.pl_id = p_pl_id
406 or ddr.opt_id = (select oipl.opt_id
407 from ben_oipl_f oipl
408 where oipl.oipl_id = p_oipl_id
409 and oipl.business_group_id =
410 p_business_group_id
411 and c_effective_date
412 between oipl.effective_start_date
413 and oipl.effective_end_date))
414 and ddr.dsgn_typ_cd = 'DPNT'
415 and ddr.business_group_id = p_business_group_id
416 and c_effective_date
417 between ddr.effective_start_date
418 and ddr.effective_end_date;
419
420 cursor c_ptip
421 (c_effective_date in date
422 )
423 is
424 select ptip.ptip_id
425 from ben_ptip_f ptip, ben_pl_f pl
426 where ptip.pl_typ_id = pl.pl_typ_id
427 and ptip.pgm_id = p_pgm_id
428 and pl.pl_id = p_pl_id
429 and c_effective_date
430 between ptip.effective_start_date
431 and ptip.effective_end_date
432 and c_effective_date
433 between pl.effective_start_date
434 and pl.effective_end_date;
435
436 l_ptip c_ptip%rowtype;
437
438
439 --
440 -- Join the apld eligibility profiles to the dependent coverage
441 -- eligibility profile to weed out non-ACTIVE profiles
442 -- Process mandatory first
443 --
444 cursor c_ade
445 (c_lvl varchar2
446 ,c_effective_date date
447 ,c_pgm_id number
448 ,c_ptip_id number
449 ,c_pl_id number
450 )
451 is
452 select ade.dpnt_cvg_eligy_prfl_id,
453 ade.mndtry_flag,
454 ade.apld_dpnt_cvg_elig_rl, -- Bug No 4271143
455 dce.dpnt_cvg_elig_det_rl,
456 dce.dpnt_rlshp_flag,
457 dce.dpnt_age_flag,
458 dce.dpnt_stud_flag,
459 dce.dpnt_dsbld_flag,
460 dce.dpnt_mrtl_flag,
461 dce.dpnt_mltry_flag,
462 dce.dpnt_pstl_flag,
463 dce.dpnt_cvrd_in_anthr_pl_flag,
464 dce.dpnt_dsgnt_crntly_enrld_flag
465 from ben_apld_dpnt_cvg_elig_prfl_f ade,
469 'PTIP',c_ptip_id,
466 ben_dpnt_cvg_eligy_prfl_f dce
467 where decode(c_lvl,
468 'PL',c_pl_id,
470 'PGM', c_pgm_id) =
471 decode(c_lvl,
472 'PL',ade.pl_id,
473 'PTIP',ade.ptip_id,
474 'PGM', ade.pgm_id)
475 and c_effective_date
476 between ade.effective_start_date
477 and ade.effective_end_date
478 and dce.dpnt_cvg_eligy_prfl_id = ade.dpnt_cvg_eligy_prfl_id
479 and dce.dpnt_cvg_eligy_prfl_stat_cd = 'A'
480 and c_effective_date
481 between dce.effective_start_date
482 and dce.effective_end_date
483 order by decode(ade.mndtry_flag,'Y',1,2);
484 --
485 l_ade c_ade%rowtype;
486 --
487 -- Bug 4271143 : Added c_ade1 to fetch records where no eligibility profile
488 -- is attached, only dependent coverage eligibility rule is there
489 --
490 cursor c_ade1
491 (c_lvl varchar2
492 ,c_effective_date date
493 ,c_pgm_id number
494 ,c_ptip_id number
495 ,c_pl_id number
496 )
497 is
498 select ade.dpnt_cvg_eligy_prfl_id,
499 ade.mndtry_flag,
500 ade.apld_dpnt_cvg_elig_rl
501 from ben_apld_dpnt_cvg_elig_prfl_f ade
502 where decode(c_lvl,
503 'PL',c_pl_id,
504 'PTIP',c_ptip_id,
505 'PGM', c_pgm_id) =
506 decode(c_lvl,
507 'PL',ade.pl_id,
508 'PTIP',ade.ptip_id,
509 'PGM', ade.pgm_id)
510 and c_effective_date
511 between ade.effective_start_date
512 and ade.effective_end_date
513 and ade.dpnt_cvg_eligy_prfl_id is null
514 and ade.apld_dpnt_cvg_elig_rl is not null
515 order by decode(ade.mndtry_flag,'Y',1,2);
516 --
517 l_ade1 c_ade1%rowtype;
518 --
519 -- End 4271143
520 --
521 cursor c_per
522 (c_effective_date in date
523 )
524 is
525 select marital_status,
526 on_military_service,
527 student_status,
528 registered_disabled_flag,
529 effective_start_date
530 from per_all_people_f per
531 where per.person_id = p_contact_person_id
532 and c_effective_date
533 between per.effective_start_date
534 and per.effective_end_date;
535 --
536 cursor c_previous_per (p_effective_date in date)
537 is
538 select marital_status,
539 on_military_service,
540 student_status,
541 registered_disabled_flag
542 from per_all_people_f per
543 where per.person_id = p_contact_person_id
544 and p_effective_date
545 between per.effective_start_date
546 and per.effective_end_date;
547
548 l_per c_per%rowtype;
549 l_previous_per c_previous_per%rowtype;
550 --
551 cursor c_add
552 (c_effective_date in date
553 )
554 is
555 select addr.postal_code,
556 addr.date_from
557 from per_addresses addr
558 where addr.person_id = p_contact_person_id
559 and addr.primary_flag = 'Y'
560 and (c_effective_date >= addr.date_from
561 or addr.date_from is null)
562 and (c_effective_date <= addr.date_to
563 or addr.date_to is null);
564 --
565 l_add c_add%rowtype;
566 --
567 cursor c_add2
568 (c_effective_date in date
569 )
570 is
571 select addr.postal_code,
572 addr.date_from
573 from per_addresses addr
574 where addr.person_id = l_contact.person_id
575 and addr.primary_flag = 'Y'
576 and (c_effective_date >= addr.date_from
577 or addr.date_from is null)
578 and (c_effective_date <= addr.date_to
579 or addr.date_to is null);
580 --
581 cursor c_pl_typ
582 (c_effective_date in date
583 )
584 is
585 select pl.pl_typ_id
586 from ben_pl_f pl
587 where pl.pl_id = p_pl_id
588 and c_effective_date
589 between pl.effective_start_date
590 and pl.effective_end_date;
591
592 l_pl_typ c_pl_typ%rowtype;
593
594 cursor c_ler
595 (c_effective_date in date
596 )
597 is
598 select pil.ler_id
599 from ben_per_in_ler pil
600 where pil.per_in_ler_id = p_per_in_ler_id;
601
602 l_ler c_ler%rowtype;
603
604 cursor c_asg
605 (c_effective_date in date
606 )
607 is
608 select asg.assignment_id,asg.organization_id,loc.region_2
609 from per_all_assignments_f asg,hr_locations_all loc
610 where asg.person_id = l_contact.person_id
611 and asg.assignment_type <> 'C'
612 and asg.location_id = loc.location_id(+)
613 and asg.primary_flag='Y'
614 and c_effective_date
615 between asg.effective_start_date
619 (c_effective_date in date
616 and asg.effective_end_date;
617 --
618 cursor c_opt
620 )
621 is
622 select oipl.opt_id
623 from ben_oipl_f oipl
624 where oipl.oipl_id = p_oipl_id
625 and c_effective_date
626 between oipl.effective_start_date
627 and oipl.effective_end_date;
628
629 l_opt c_opt%rowtype;
630
631
632 begin
633 --
634 hr_utility.set_location ('Entering '||l_proc,10);
635 --
636 -- Initialize the globals.
637 --
638 g_elig_change_dt := null;
639 g_dpnt_cvg_strt_dt := p_dpnt_cvg_strt_dt;
640 g_effective_date := nvl(p_lf_evt_ocrd_dt, p_effective_date);
641 -- fonm
642 l_effective_date := nvl(p_lf_evt_ocrd_dt, p_effective_date);
643 if ben_manage_life_events.fonm = 'Y'
644 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
645 --
646 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
647 l_effective_date := nvl(l_fonm_cvg_strt_dt,l_effective_date ) ;
648
649 --
650 hr_utility.set_location ('Fonm Date '||g_effective_date ,10);
651 end if;
652
653 --
654 hr_utility.set_location ('p_contact_person_id '||to_char(p_contact_person_id),10);
655 hr_utility.set_location ('p_contact_relationship_id '||
656 to_char(p_contact_relationship_id),10);
657 hr_utility.set_location ('p_pgm_id:'||to_char(p_pgm_id)||
658 ' p_pl_id:'||to_char(p_pl_id)||' p_ptip_id:'||to_char(p_ptip_id)||
659 ' p_oipl_id:'||to_char(p_oipl_id),10);
660
661 --
662 -- If the eligibility is lost due to plan design type of issues
663 -- Not related to the particular person then use effective_date
664 --
665 -- g_elig_change_dt := nvl(p_lf_evt_ocrd_dt,p_effective_date)-1;
666 --
667 hr_utility.set_location ('Determining designation level ',20);
668
669 if p_level is null then
670 if p_pgm_id is not null then
671 open c_pgm
672 (c_effective_date => l_effective_date
673 );
674 fetch c_pgm into l_pgm;
675 if c_pgm%notfound then
676 close c_pgm;
677 hr_utility.set_location ('BEN_91470_PGM_NOT_FOUND ',10);
678 fnd_message.set_name('BEN','BEN_91470_PGM_NOT_FOUND');
679 fnd_message.set_token('PROC',l_proc);
680 fnd_message.set_token('PGM_ID',to_char(p_pgm_id));
681 fnd_message.raise_error;
682 end if;
683 close c_pgm;
684 l_level := l_pgm.dpnt_dsgn_lvl_cd;
685 else
686 l_level := 'PL';
687 end if;
688 else
689 l_level := p_level;
690 end if;
691 --
692 hr_utility.set_location ('l_level: '||l_level,20);
693
694 -- In order to find ptip eligibility profiles, this was added:
695 -- Bug 1167262
696 l_ptip.ptip_id := p_ptip_id;
697 if l_level = 'PTIP' and p_ptip_id is null and p_pgm_id is not null
698 and p_pl_id is not null then
699 open c_ptip
700 (c_effective_date => l_effective_date
701 );
702 fetch c_ptip into l_ptip;
703 close c_ptip;
704 end if;
705
706 --
707 -- Note:
708 -- contact must be opened before other cursors in order
709 -- to get the person_id (of the participant) used to
710 -- use per_in_ler_id, but this may be null from bendsgel
711 --
712 open c_contact
713 (c_effective_date => l_effective_date
714 );
715 --
716 fetch c_contact into l_contact;
717 if c_contact%notfound then
718 --
719 close c_contact;
720 hr_utility.set_location ('BEN_91480_MISSING_CONTACT_REL ',10);
721 fnd_message.set_name('BEN','BEN_91480_MISSING_CONTACT_REL');
722 fnd_message.set_token('PROC',l_proc);
723 fnd_message.set_token('CONT_RLSHP_ID',to_char(p_contact_relationship_id));
724 fnd_message.set_token('LF_EVT_OCRD_DT',to_char(p_lf_evt_ocrd_dt));
725 fnd_message.set_token('EFFECTIVE_DATE',to_char(p_effective_date));
726 fnd_message.raise_error;
727 --
728 end if;
729 --
730 close c_contact;
731 --
732 -- If the contact relationship has been ended as of the effective date then
733 -- the contact should no longer be eligible.
734 --
735 if l_contact.date_end < p_effective_date then
736 --
737 l_eligible_flag := 'N';
738 l_inelig_rsn_cd := 'REL';
739 set_elig_change_dt(p_elig_change_dt => l_contact.date_end,p_effective_date => l_effective_date); -- bug 4546890
740 -- g_elig_change_dt := l_contact.date_end;
741 --
742 end if;
743 --
744 -- If its an option in plan then the contact type
745 -- must be valid for the electable choice contact type
746 --
747 if l_eligible_flag = 'Y' and
748 p_oipl_id is not null then
749 --
750 -- Must match up with contact relationship type cd
751 --
752 -- If there are no designation requirements for the comp object
753 -- then any number or type of dependents can be designated.
754 --
755 hr_utility.set_location ('c_dsgn'||l_eligible_flag,20);
756 --
757 open c_dsgn
758 (c_effective_date => l_effective_date
759 );
760 --
764 --
761 fetch c_dsgn into l_exists;
762 --
763 if c_dsgn%notfound then
765 l_eligible_flag := 'Y';
766 l_inelig_rsn_cd := null;
767 --
768 else
769 --
770 -- if there is a designation requirement for the contact_type.
771 --
772 hr_utility.set_location ('c_dsgn_rl_typ'||l_eligible_flag,20);
773 --
774 open c_dsgn_rl_typ
775 (c_effective_date => l_effective_date,
776 p_contact_type => l_contact.contact_type
777 );
778 --
779 fetch c_dsgn_rl_typ into l_exists;
780 if c_dsgn_rl_typ%notfound then
781 --
782 l_eligible_flag := 'N';
783 l_inelig_rsn_cd := 'REL';
784 --
785 -- Check if there is a designation requirement for any
786 -- relationship type. If there is one, check if there are
787 -- other designation requirements defined for other contact
788 -- type. If there are, the dependent is not eligible.
789 --
790 open c_dsgn_grp
791 (c_effective_date => l_effective_date
792 );
793 --
794 fetch c_dsgn_grp into l_exists;
795 if c_dsgn_grp%found then
796 --
797 hr_utility.set_location ('c_dsgn_grp'||l_eligible_flag,20);
798 l_eligible_flag := 'Y';
799 l_inelig_rsn_cd := null;
800 --
801 open c_dsgn_not_rl_typ
802 (c_effective_date => l_effective_date
803 );
804 --
805 fetch c_dsgn_not_rl_typ into l_exists;
806 if c_dsgn_not_rl_typ%found then
807 --
808 hr_utility.set_location ('c_dsgn_not_rl_typ'||
809 l_eligible_flag,20);
810 l_eligible_flag := 'N';
811 l_inelig_rsn_cd := 'REL';
812 --
813 end if;
814 --
815 close c_dsgn_not_rl_typ;
816 --
817 end if;
818 --
819 close c_dsgn_grp;
820 --
821 end if;
822 --
823 close c_dsgn_rl_typ;
824 --
825 end if;
826 --
827 close c_dsgn;
828 --
829 end if;
830 --
831 --bug#4318031 - need to go through other contact types with person flag
832 if l_eligible_flag = 'N' then
833 --
834 open c_contact2(l_effective_date);
835 loop
836 fetch c_contact2 into l_contact_type2;
837 if c_contact2%notfound then
838 exit;
839 end if;
840 open c_dsgn_rl_typ(l_effective_date, l_contact_type2);
841 fetch c_dsgn_rl_typ into l_exists;
842 if c_dsgn_rl_typ%found then
843 --
844 l_eligible_flag := 'Y';
845 close c_dsgn_rl_typ;
846 exit;
847 --
848 end if;
849 close c_dsgn_rl_typ;
850 end loop;
851 --
852 close c_contact2;
853 --
854 end if;
855 hr_utility.set_location ('eligible flag'||l_eligible_flag,30);
856 --
857 -- Loop through all profiles
858 --
859 if l_eligible_flag = 'Y' then
860 hr_utility.set_location (l_proc||' l_eligible_flag=Y ',10);
861 --
862 -- Eventally call the cache
863 --
864 open c_per
865 (c_effective_date => l_effective_date
866 );
867 --
868 fetch c_per into l_per;
869 --
870 if c_per%notfound then
871 --
872 close c_per;
873 hr_utility.set_location ('BEN_91481_INVALID_PERSON ',10);
874 fnd_message.set_name('BEN','BEN_91481_INVALID_PERSON');
875 fnd_message.set_token('PROC',l_proc);
876 fnd_message.set_token('CONT_PER_ID',to_char(p_contact_person_id));
877 fnd_message.raise_error;
878 --
879 end if;
880 --
881 close c_per;
882 --
883 /*
884 -- Get address info
885 --
886 open c_add
887 (c_effective_date => l_effective_date
888 );
889 --
890 fetch c_add into l_add;
891 if c_add%notfound and
892 l_contact.rltd_per_rsds_w_dsgntr_flag='N' then
893 --
894 close c_add;
895 hr_utility.set_location ('BEN_91482_INVALID_ADDRESS ',10);
896 fnd_message.set_name('BEN','BEN_91482_INVALID_ADDRESS');
897 fnd_message.set_token('PROC',l_proc);
898 fnd_message.set_token('CONT_PER_ID',to_char(p_contact_person_id));
899 raise ben_manage_life_events.g_record_error;
900 --
901 elsif c_add%notfound and
902 l_contact.rltd_per_rsds_w_dsgntr_flag='Y' then
903 --
904 open c_add2
905 (c_effective_date => l_effective_date
906 );
907 --
908 fetch c_add2 into l_add;
909 if c_add2%notfound then
913 fnd_message.set_token('CONT_PER_ID',to_char(l_contact.person_id));
910 hr_utility.set_location ('BEN_91482_INVALID_ADDRESS - c_add2 ',10);
911 fnd_message.set_name('BEN','BEN_91482_INVALID_ADDRESS');
912 fnd_message.set_token('PROC',l_proc);
914 raise ben_manage_life_events.g_record_error;
915 --
916 end if;
917 --
918 close c_add2;
919 --
920 end if;
921 --
922 close c_add;
923 */
924 --
925 -- If the eligibility is lost due to person info then
926 -- use the date on which the person_info was changed
927 -- Note: overridden in check_age_elig for that case
928 -- overridden for postal elig for that case
929 --
930 -- g_elig_change_dt:=l_per.effective_start_date-1;
931 --
932 hr_utility.set_location (' g_elig_change_dt '||to_char(g_elig_change_dt),40);
933 open c_ade
934 (c_lvl => l_level
935 ,c_effective_date => l_effective_date
936 ,c_pgm_id => p_pgm_id
937 ,c_ptip_id => l_ptip.ptip_id
938 ,c_pl_id => p_pl_id
939 );
940 --
941 loop
942 --
943 -- All mandatory profiles are processed first
944 --
945 fetch c_ade into l_ade;
946 exit when c_ade%notfound;
947 --
948 -- Check all the factors for this profile
949 --
950 l_eligible_flag := 'Y';
951 --
952 /* moved the check at the end
953 if l_ade.dpnt_age_flag = 'Y' then
954 --
955 check_age_elig
956 (p_eligy_prfl_id => l_ade.dpnt_cvg_eligy_prfl_id,
957 p_person_id => p_contact_person_id,
958 p_contact_person_id => l_contact.person_id,
959 p_pgm_id => p_pgm_id,
960 p_pl_id => p_pl_id,
961 p_oipl_id => p_oipl_id,
962 p_business_group_id => p_business_group_id,
963 p_per_in_ler_id => p_per_in_ler_id,
964 p_effective_date => p_effective_date,
965 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
966 p_eligible_flag => l_eligible_flag,
967 p_inelig_rsn_cd => l_inelig_rsn_cd);
968 --
969 hr_utility.set_location ('eligible flag'||l_eligible_flag,40);
970 exit when (l_ade.mndtry_flag = 'Y' and l_eligible_flag <> 'Y');
971 --
972 end if;
973 --
974 */
975 if l_ade.dpnt_rlshp_flag = 'Y' and
976 l_eligible_flag = 'Y' then
977 --
978 check_contact_elig
979 (p_eligy_prfl_id => l_ade.dpnt_cvg_eligy_prfl_id,
980 p_person_id => p_contact_person_id,
981 p_contact_person_id => l_contact.person_id, -- Bug 6956648
982 p_business_group_id => p_business_group_id,
983 p_effective_date => p_effective_date,
984 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
985 p_contact_type => l_contact.contact_type,
986 p_eligible_flag => l_eligible_flag,
987 p_inelig_rsn_cd => l_inelig_rsn_cd);
988 --
989 --bug#4157836 - contact relationship is not a datetracked table and
990 --end of relationship is checked first before processing
991 /*
992 if l_eligible_flag <> 'Y' then
993 set_elig_change_dt(p_elig_change_dt=>l_per.effective_start_date-1);
994 end if;
995 */
996 --
997 hr_utility.set_location ('eligible flag'||l_eligible_flag,80);
998 exit when (l_ade.mndtry_flag = 'Y' and l_eligible_flag <> 'Y');
999 --
1000 end if;
1001 --
1002 if l_eligible_flag = 'Y' then
1003 --
1004 -- Run eligibility rule
1005 --
1006 if l_ade.dpnt_cvg_elig_det_rl is not null or
1007 l_ade.apld_dpnt_cvg_elig_rl is not null then -- Bug 4271143 Added apld_dpnt_cvg_elig_rl condition also
1008 --
1009 if p_pl_id is not null then
1010 --
1011 open c_pl_typ
1012 (c_effective_date => l_effective_date
1013 );
1014 --
1015 fetch c_pl_typ into l_pl_typ;
1016 --
1017 close c_pl_typ;
1018 --
1019 end if;
1020 --
1021 if p_oipl_id is not null then
1022 --
1023 open c_opt
1024 (c_effective_date => l_effective_date
1025 );
1026 --
1027 fetch c_opt into l_opt;
1028 --
1029 close c_opt;
1030 --
1031 end if;
1032 --
1033 if p_per_in_ler_id is not null then
1034 --
1035 open c_ler
1036 (c_effective_date => l_effective_date
1037 );
1038 --
1039 fetch c_ler into l_ler;
1040 --
1041 close c_ler;
1045 open c_asg
1042 --
1043 end if;
1044 --
1046 (c_effective_date => l_effective_date
1047 );
1048 --
1049 fetch c_asg into l_assignment_id,l_organization_id,l_region_2;
1050 --
1051 close c_asg;
1052 --
1053 /*
1054 if l_region_2 is not null then
1055 --
1056 l_jurisdiction_code := pay_mag_utils.lookup_jurisdiction_code
1057 (p_state => l_region_2);
1058 --
1059 end if;
1060 */
1061 --
1062 -- l_contact.person_id is passed, as the assignment id may not be
1063 -- available all the time. This is a work around to access the
1064 -- contact person data. Customer have to write the formula function.
1065 --
1066 if l_ade.dpnt_cvg_elig_det_rl is not null then
1067 l_outputs := benutils.formula
1068 (p_formula_id => l_ade.dpnt_cvg_elig_det_rl,
1069 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
1070 p_business_group_id => p_business_group_id,
1071 p_assignment_id => l_assignment_id,
1072 p_organization_id => l_organization_id,
1073 p_pgm_id => p_pgm_id,
1074 p_pl_id => p_pl_id,
1075 p_pl_typ_id => l_pl_typ.pl_typ_id,
1076 p_opt_id => l_opt.opt_id,
1077 p_ler_id => l_ler.ler_id,
1078 p_param1 => 'CON_PERSON_ID',
1079 p_param1_value => to_char(p_contact_person_id),
1080 p_jurisdiction_code => l_jurisdiction_code);
1081 --
1082 l_eligible_flag := l_outputs(l_outputs.first).value;
1083 --
1084 if l_eligible_flag = 'Y' then
1085 --
1086 l_inelig_rsn_cd := null;
1087 --
1088 else
1089 --
1090 l_inelig_rsn_cd := 'AGE';
1091 --hr_utility.set_location('** SUP l_effective_date-1'||l_effective_date,900);
1092 set_elig_change_dt(p_elig_change_dt=>l_effective_date-1,p_effective_date => l_effective_date); -- bug 4546890
1093 --
1094 end if;
1095 --
1096 end if;
1097 --
1098 -- Bug 4271143 Added apld_dpnt_cvg_elig_rl condition also as if
1099 -- Coverage Eligibility Rule is specified, then it should also evaluate to true
1100 --
1101 if l_ade.apld_dpnt_cvg_elig_rl is not null then
1102 l_outputs := benutils.formula
1103 (p_formula_id => l_ade.apld_dpnt_cvg_elig_rl,
1104 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
1105 p_business_group_id => p_business_group_id,
1106 p_assignment_id => l_assignment_id,
1107 p_organization_id => l_organization_id,
1108 p_pgm_id => p_pgm_id,
1109 p_pl_id => p_pl_id,
1110 p_pl_typ_id => l_pl_typ.pl_typ_id,
1111 p_opt_id => l_opt.opt_id,
1112 p_ler_id => l_ler.ler_id,
1113 p_param1 => 'CON_PERSON_ID',
1114 p_param1_value => to_char(p_contact_person_id),
1115 p_jurisdiction_code => l_jurisdiction_code);
1116 --
1117 l_apld_eligible_flag := l_outputs(l_outputs.first).value;
1118 --
1119 if l_apld_eligible_flag = 'Y' then
1120 --
1121 l_inelig_rsn_cd := null;
1122 --
1123 else
1124 --
1125 l_inelig_rsn_cd := 'AGE';
1126 --hr_utility.set_location('** SUP l_effective_date'||l_effective_date,901);
1127 set_elig_change_dt(p_elig_change_dt=>l_effective_date-1,p_effective_date => l_effective_date); -- bug 4546890
1128 --
1129 end if;
1130 --
1131 end if;
1132 --
1133 end if;
1134 --
1135 exit when (l_ade.mndtry_flag = 'Y' and (l_eligible_flag <> 'Y' or l_apld_eligible_flag <> 'Y'));
1136 --
1137 end if;
1138 --
1139 hr_utility.set_location ('apld dep eligible flag'||l_apld_eligible_flag,50);
1140 --
1141 -- End 4271143
1142 --
1143 hr_utility.set_location ('eligible flag'||l_eligible_flag,50);
1144 --
1145 if l_ade.dpnt_mrtl_flag = 'Y' and
1146 l_eligible_flag = 'Y' then
1147 --
1148 check_marital_elig
1149 (p_eligy_prfl_id => l_ade.dpnt_cvg_eligy_prfl_id,
1150 p_person_id => p_contact_person_id,
1151 p_business_group_id => p_business_group_id,
1152 p_effective_date => p_effective_date,
1153 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1154 p_marital_cd => l_per.marital_status,
1155 p_eligible_flag => l_eligible_flag,
1156 p_inelig_rsn_cd => l_inelig_rsn_cd);
1157 --
1158 if l_eligible_flag <> 'Y' then
1159 -- check previous value
1160 open c_previous_per(l_per.effective_start_date-1);
1161 fetch c_previous_per into l_previous_per;
1165 end if;
1162 if c_previous_per%found and nvl(l_previous_per.marital_status,'X')<>
1163 nvl(l_per.marital_status,'X') then
1164 set_elig_change_dt(p_elig_change_dt=>l_per.effective_start_date-1,p_effective_date => l_effective_date); -- bug 4546890
1166 close c_previous_per;
1167 end if;
1168 --
1169 exit when (l_ade.mndtry_flag = 'Y' and l_eligible_flag <> 'Y');
1170 --
1171 end if;
1172 --
1173 hr_utility.set_location ('eligible flag'||l_eligible_flag,50);
1174 --
1175 if l_ade.dpnt_mltry_flag = 'Y' and
1176 l_eligible_flag = 'Y' then
1177 --
1178 check_military_elig
1179 (p_eligy_prfl_id => l_ade.dpnt_cvg_eligy_prfl_id,
1180 p_person_id => p_contact_person_id,
1181 p_business_group_id => p_business_group_id,
1182 p_effective_date => p_effective_date,
1183 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1184 p_military_service => l_per.on_military_service,
1185 p_eligible_flag => l_eligible_flag,
1186 p_inelig_rsn_cd => l_inelig_rsn_cd);
1187 --
1188 if l_eligible_flag <> 'Y' then
1189 --check previous status
1190 open c_previous_per(l_per.effective_start_date-1);
1191 fetch c_previous_per into l_previous_per;
1192 if c_previous_per%found and nvl(l_previous_per.on_military_service,'X')<>
1193 nvl(l_per.on_military_service,'X') then
1194 set_elig_change_dt(p_elig_change_dt=>l_per.effective_start_date-1,p_effective_date => l_effective_date); -- bug 4546890
1195 end if;
1196 close c_previous_per;
1197 end if;
1198 --
1199 exit when (l_ade.mndtry_flag = 'Y' and l_eligible_flag <> 'Y');
1200 --
1201 end if;
1202 --
1203 hr_utility.set_location ('eligible flag'||l_eligible_flag,60);
1204 --
1205 if l_ade.dpnt_stud_flag = 'Y' and
1206 l_eligible_flag = 'Y' then
1207 --
1208 check_student_elig
1209 (p_eligy_prfl_id => l_ade.dpnt_cvg_eligy_prfl_id,
1210 p_person_id => p_contact_person_id,
1211 p_business_group_id => p_business_group_id,
1212 p_effective_date => p_effective_date,
1213 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1214 p_student_status => l_per.student_status,
1215 p_eligible_flag => l_eligible_flag,
1216 p_inelig_rsn_cd => l_inelig_rsn_cd);
1217 --
1218 if l_eligible_flag <> 'Y' then
1219 --check previous status
1220 open c_previous_per(l_per.effective_start_date-1);
1221 fetch c_previous_per into l_previous_per;
1222 if c_previous_per%found and nvl(l_previous_per.student_status,'X')<>
1223 nvl(l_per.student_status,'X') then
1224 set_elig_change_dt(p_elig_change_dt=>l_per.effective_start_date-1,p_effective_date => l_effective_date); -- bug 4546890
1225 end if;
1226 close c_previous_per;
1227 end if;
1228 --
1229 hr_utility.set_location ('eligible flag'||l_eligible_flag,70);
1230 exit when (l_ade.mndtry_flag = 'Y' and l_eligible_flag <> 'Y');
1231 --
1232 end if;
1233 --
1234 /* for performance reason the relationship criteria is checked first and age
1235 factor at the end
1236 if l_ade.dpnt_rlshp_flag = 'Y' and
1237 l_eligible_flag = 'Y' then
1238 --
1239 check_contact_elig
1240 (p_eligy_prfl_id => l_ade.dpnt_cvg_eligy_prfl_id,
1241 p_person_id => p_contact_person_id,
1242 p_contact_person_id => l_contact.person_id, -- Bug 6956648
1243 p_business_group_id => p_business_group_id,
1244 p_effective_date => p_effective_date,
1245 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1246 p_contact_type => l_contact.contact_type,
1247 p_eligible_flag => l_eligible_flag,
1248 p_inelig_rsn_cd => l_inelig_rsn_cd);
1249 --
1250 if l_eligible_flag <> 'Y' then
1251 set_elig_change_dt(p_elig_change_dt=>l_per.effective_start_date-1);
1252 end if;
1253 --
1254 hr_utility.set_location ('eligible flag'||l_eligible_flag,80);
1255 exit when (l_ade.mndtry_flag = 'Y' and l_eligible_flag <> 'Y');
1256 --
1257 end if;
1258 */
1259 --
1260 if l_ade.dpnt_dsbld_flag = 'Y' and
1261 l_eligible_flag = 'Y' then
1262 --
1263 check_disabled_elig
1264 (p_eligy_prfl_id => l_ade.dpnt_cvg_eligy_prfl_id,
1265 p_person_id => p_contact_person_id,
1266 p_business_group_id => p_business_group_id,
1267 p_effective_date => p_effective_date,
1268 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1269 p_per_dsbld_type => l_per.registered_disabled_flag,
1270 p_eligible_flag => l_eligible_flag,
1271 p_inelig_rsn_cd => l_inelig_rsn_cd);
1272 --
1273 if l_eligible_flag <> 'Y' then
1274 -- check the previous value
1275 open c_previous_per(l_per.effective_start_date-1);
1276 fetch c_previous_per into l_previous_per;
1280 end if;
1277 if c_previous_per%found and nvl(l_previous_per.registered_disabled_flag,'X')<>
1278 nvl(l_per.registered_disabled_flag,'X') then
1279 set_elig_change_dt(p_elig_change_dt=>l_per.effective_start_date-1,p_effective_date => l_effective_date); -- bug 4546890
1281 close c_previous_per;
1282 end if;
1283 --
1284 hr_utility.set_location ('eligible flag'||l_eligible_flag,90);
1285 exit when (l_ade.mndtry_flag = 'Y' and l_eligible_flag <> 'Y');
1286 --
1287 end if;
1288 --
1289 if l_ade.dpnt_pstl_flag = 'Y' and
1290 l_eligible_flag = 'Y' then
1291 --- Beofre calling postal eligibility check whehter the address/share address is defined
1292 --
1293 hr_utility.set_location('postal flag ', 219);
1294 open c_add
1295 (c_effective_date => l_effective_date
1296 );
1297 --
1298 fetch c_add into l_add;
1299 if c_add%notfound and l_contact.rltd_per_rsds_w_dsgntr_flag='N' then
1300 --
1301 --close c_add;
1302 hr_utility.set_location ('BEN_91482_INVALID_ADDRESS ',10);
1303 hr_utility.set_location ('contact '|| l_contact.rltd_per_rsds_w_dsgntr_flag ,10);
1304 --fnd_message.set_name('BEN','BEN_91482_INVALID_ADDRESS');
1305 --fnd_message.set_token('PROC',l_proc);
1306 --fnd_message.set_token('CONT_PER_ID',to_char(p_contact_person_id));
1307 --raise ben_manage_life_events.g_record_error;
1308
1309 elsif c_add%notfound and l_contact.rltd_per_rsds_w_dsgntr_flag='Y' then
1310 --
1311 open c_add2
1312 (c_effective_date => l_effective_date
1313 );
1314 --
1315 fetch c_add2 into l_add;
1316 if c_add2%notfound then
1317
1318 hr_utility.set_location ('contact '|| l_contact.rltd_per_rsds_w_dsgntr_flag ,10);
1319 hr_utility.set_location ('BEN_91482_INVALID_ADDRESS - c_add2 ',10);
1320 --fnd_message.set_name('BEN','BEN_91482_INVALID_ADDRESS');
1321 --fnd_message.set_token('PROC',l_proc);
1322 --fnd_message.set_token('CONT_PER_ID',to_char(l_contact.person_id));
1323 --raise ben_manage_life_events.g_record_error;
1324 --
1325 end if;
1326 --
1327 close c_add2;
1328 --
1329 end if;
1330 --
1331 close c_add;
1332 --
1333 hr_utility.set_location ('calling check_postal ' ,10);
1334 check_postal_elig
1335 (p_eligy_prfl_id => l_ade.dpnt_cvg_eligy_prfl_id,
1336 p_person_id => p_contact_person_id,
1337 p_business_group_id => p_business_group_id,
1338 p_effective_date => p_effective_date,
1339 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1340 p_postal_code => l_add.postal_code,
1341 p_eligible_flag => l_eligible_flag,
1342 p_inelig_rsn_cd => l_inelig_rsn_cd);
1343 --
1344 if l_eligible_flag<>'Y' and l_add.date_from is not null then
1345 --
1346 -- set the change date to the address change date
1347 --
1348 set_elig_change_dt(p_elig_change_dt => l_add.date_from-1,p_effective_date => l_effective_date); -- bug 4546890
1349 -- g_elig_change_dt:=l_add.date_from-1;
1350 --
1351 end if;
1352 --
1353 hr_utility.set_location ('eligible flag'||l_eligible_flag,100);
1354 exit when (l_ade.mndtry_flag = 'Y' and l_eligible_flag <> 'Y');
1355 --
1356 end if;
1357 --
1358 if l_ade.dpnt_cvrd_in_anthr_pl_flag = 'Y' and
1359 l_eligible_flag = 'Y' then
1360 --
1361 check_cvrd_anthr_pl_elig
1362 (p_eligy_prfl_id => l_ade.dpnt_cvg_eligy_prfl_id,
1363 p_person_id => p_contact_person_id,
1364 p_business_group_id => p_business_group_id,
1365 p_effective_date => p_effective_date,
1366 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1367 p_pl_id => p_pl_id,
1368 p_eligible_flag => l_eligible_flag,
1369 p_inelig_rsn_cd => l_inelig_rsn_cd);
1370 --
1371 if l_eligible_flag <> 'Y' then
1372 set_elig_change_dt(p_elig_change_dt=>l_per.effective_start_date-1,p_effective_date => l_effective_date);-- bug 4546890
1373 end if;
1374 --
1375 hr_utility.set_location ('eligible flag'||l_eligible_flag,110);
1376 exit when (l_ade.mndtry_flag = 'Y' and l_eligible_flag <> 'Y');
1377 --
1378 end if;
1379 --
1380 if l_ade.dpnt_age_flag = 'Y' and
1381 l_eligible_flag = 'Y' then
1382 --
1383 check_age_elig
1384 (p_eligy_prfl_id => l_ade.dpnt_cvg_eligy_prfl_id,
1385 p_person_id => p_contact_person_id,
1386 p_contact_person_id => l_contact.person_id,
1387 p_pgm_id => p_pgm_id,
1388 p_pl_id => p_pl_id,
1392 p_effective_date => p_effective_date,
1389 p_oipl_id => p_oipl_id,
1390 p_business_group_id => p_business_group_id,
1391 p_per_in_ler_id => p_per_in_ler_id,
1393 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1394 p_eligible_flag => l_eligible_flag,
1395 p_inelig_rsn_cd => l_inelig_rsn_cd);
1396 --
1397 hr_utility.set_location ('eligible flag'||l_eligible_flag,40);
1398 exit when (l_ade.mndtry_flag = 'Y' and l_eligible_flag <> 'Y');
1399 --
1400 end if;
1401 --
1402 if l_ade.dpnt_dsgnt_crntly_enrld_flag = 'Y' and
1403 l_eligible_flag = 'Y' then
1404 --
1405 check_dsgntr_enrld_cvg_elig
1406 (p_eligy_prfl_id => l_ade.dpnt_cvg_eligy_prfl_id,
1407 p_person_id => p_contact_person_id,
1408 p_dsgntr_id => l_contact.person_id,
1409 p_business_group_id => p_business_group_id,
1410 p_effective_date => p_effective_date,
1411 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1412 p_pgm_id => p_pgm_id,
1413 p_eligible_flag => l_eligible_flag,
1414 p_inelig_rsn_cd => l_inelig_rsn_cd);
1415 --
1416 if l_eligible_flag <> 'Y' then
1417 set_elig_change_dt(p_elig_change_dt=>l_per.effective_start_date-1,p_effective_date => l_effective_date); -- bug 4546890
1418 end if;
1419 --
1420 hr_utility.set_location ('eligible flag'||l_eligible_flag,110);
1421 exit when (l_ade.mndtry_flag = 'Y' and l_eligible_flag <> 'Y');
1422 --
1423 end if;
1424 --
1425 exit when (l_ade.mndtry_flag <> 'Y' and l_eligible_flag = 'Y');
1426 --
1427 end loop;
1428 --
1429 close c_ade;
1430 --
1431 -- Bug 4271143 : Fetch from c_ade1 for records where no eligibility profile
1432 -- is attached, only dependent coverage eligibility rule is there
1433 --
1434 hr_utility.set_location('eligible flag'||l_eligible_flag,115);
1435 hr_utility.set_location ('apld dep eligible flag'||l_apld_eligible_flag,115);
1436 -- fetch only if till now all the profiles with mandatory flag on have been satisfied
1437 if (not(l_ade.mndtry_flag = 'Y' and (l_eligible_flag <> 'Y' or l_apld_eligible_flag <> 'Y'))) then
1438 open c_ade1
1439 (c_lvl => l_level
1440 ,c_effective_date => l_effective_date
1441 ,c_pgm_id => p_pgm_id
1442 ,c_ptip_id => l_ptip.ptip_id
1443 ,c_pl_id => p_pl_id
1444 );
1445 --
1446 loop
1447 --
1448 -- All mandatory profiles are processed first
1449 --
1450 fetch c_ade1 into l_ade1;
1451 exit when c_ade1%notfound;
1452 if l_apld_eligible_flag = 'Y' then
1453 --
1454 -- Run eligibility rule
1455 --
1456 if l_ade1.apld_dpnt_cvg_elig_rl is not null then
1457 --
1458 if p_pl_id is not null then
1459 --
1460 open c_pl_typ
1461 (c_effective_date => l_effective_date
1462 );
1463 --
1464 fetch c_pl_typ into l_pl_typ;
1465 --
1466 close c_pl_typ;
1467 --
1468 end if;
1469 --
1470 if p_oipl_id is not null then
1471 --
1472 open c_opt
1473 (c_effective_date => l_effective_date
1474 );
1475 --
1476 fetch c_opt into l_opt;
1477 --
1478 close c_opt;
1479 --
1480 end if;
1481 --
1482 if p_per_in_ler_id is not null then
1483 --
1484 open c_ler
1485 (c_effective_date => l_effective_date
1486 );
1487 --
1488 fetch c_ler into l_ler;
1489 --
1490 close c_ler;
1491 --
1492 end if;
1493 --
1494 open c_asg
1495 (c_effective_date => l_effective_date
1496 );
1497 --
1498 fetch c_asg into l_assignment_id,l_organization_id,l_region_2;
1499 --
1500 close c_asg;
1501 --
1502 l_outputs := benutils.formula
1503 (p_formula_id => l_ade1.apld_dpnt_cvg_elig_rl,
1504 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
1505 p_business_group_id => p_business_group_id,
1506 p_assignment_id => l_assignment_id,
1507 p_organization_id => l_organization_id,
1508 p_pgm_id => p_pgm_id,
1509 p_pl_id => p_pl_id,
1510 p_pl_typ_id => l_pl_typ.pl_typ_id,
1511 p_opt_id => l_opt.opt_id,
1512 p_ler_id => l_ler.ler_id,
1513 p_param1 => 'CON_PERSON_ID',
1514 p_param1_value => to_char(p_contact_person_id),
1515 p_jurisdiction_code => l_jurisdiction_code);
1516 --
1520 --
1517 l_apld_eligible_flag := l_outputs(l_outputs.first).value;
1518 --
1519 if l_apld_eligible_flag = 'Y' then
1521 l_inelig_rsn_cd := null;
1522 --
1523 else
1524 --
1525 l_inelig_rsn_cd := 'AGE';
1526 --hr_utility.set_location('** SUP l_effective_date-1'||l_effective_date,902);
1527 set_elig_change_dt(p_elig_change_dt=>l_effective_date-1,p_effective_date => l_effective_date); -- bug 4546890
1528 --
1529 end if;
1530 --
1531 end if;
1532 --
1533 end if;
1534 --
1535 exit when (l_ade1.mndtry_flag = 'Y' and l_apld_eligible_flag <> 'Y');
1536 --
1537 hr_utility.set_location ('apld dep eligible flag'||l_apld_eligible_flag,115);
1538 end loop;
1539 close c_ade1;
1540 --
1541 end if;
1542 --
1543 -- End 4271143
1544 --
1545 end if;
1546 --
1547 if g_elig_change_dt is null then
1548 --
1549 -- If the eligibility is lost due to plan design type of issues
1550 -- Not related to the particular person then use effective_date
1551 --
1552 set_elig_change_dt
1553 (p_elig_change_dt => l_effective_date-1,p_effective_date => l_effective_date); -- bug 4546890
1554 --
1555 end if;
1556 --
1557 hr_utility.set_location ('eligible flag'||l_eligible_flag,120);
1558 hr_utility.set_location ('apld eligible flag'||l_apld_eligible_flag,120);
1559 --
1560 if (l_apld_eligible_flag = 'N') then -- Bug 4271143
1561 l_eligible_flag := 'N';
1562 end if;
1563 --
1564 p_dependent_eligible_flag := l_eligible_flag;
1565 p_dpnt_inelig_rsn_cd := l_inelig_rsn_cd;
1566 --
1567 hr_utility.set_location ('Leaving'||l_proc,130);
1568 --
1569 end main;
1570 --
1571 procedure check_age_elig
1572 (p_eligy_prfl_id in number,
1573 p_person_id in number,
1574 p_contact_person_id in number,
1575 p_pgm_id in number,
1576 p_pl_id in number,
1577 p_oipl_id in number,
1578 p_business_group_id in number,
1579 p_per_in_ler_id in number,
1580 p_effective_date in date,
1581 p_lf_evt_ocrd_dt in date,
1582 p_eligible_flag out nocopy varchar2,
1583 p_inelig_rsn_cd out nocopy varchar2) is
1584
1585 --
1586 l_proc varchar2(100):= g_package||'.check_age_elig';
1587 l_found_one varchar2(10) := 'N';
1588 l_elig varchar2(10) := 'N';
1589 l_eligy_prfl_id number(15) := p_eligy_prfl_id;
1590 l_bg_id number(15) := p_business_group_id;
1591 l_effective_date date := p_effective_date;
1592 l_age number;
1593 l_pgm_id number(15) := p_pgm_id;
1594 l_pl_id number(15) := p_pl_id;
1595 l_oipl_id number(15) := p_oipl_id;
1596 l_change_date date;
1597 l_elig_change_dt date := null;
1598 l_dob date;
1599 l_check_dob boolean := true;
1600 l_max_age_number number ;
1601 --fonm
1602 l_fonm_cvg_strt_dt date ;
1603 l_defct_brch_dtctd varchar2(100) := null;
1604 --
1605 -- cursor to get the dob (if null will be inelig)
1606 --
1607 cursor c_per is
1608 select date_of_birth
1609 from per_all_people_f per
1610 where per.person_id = p_person_id
1611 and per.business_group_id = p_business_group_id
1612 and l_effective_date
1613 between per.effective_start_date
1614 and per.effective_end_date;
1615 --
1616 -- Cursor to grab age requirement for eligibility profile.
1617 --
1618 cursor c_age_check is
1619 select agf.mx_age_num,
1620 agf.mn_age_num,
1621 agf.age_uom,
1622 agf.no_mn_age_flag,
1623 agf.no_mx_age_flag,
1624 eac.excld_flag,
1625 eac.age_fctr_id
1626 from ben_age_fctr agf,
1627 ben_elig_age_cvg_f eac
1628 where agf.age_fctr_id = eac.age_fctr_id
1629 and agf.business_group_id = p_business_group_id
1630 and eac.dpnt_cvg_eligy_prfl_id = p_eligy_prfl_id
1631 and eac.business_group_id = p_business_group_id
1632 and l_effective_date
1633 between eac.effective_start_date
1634 and eac.effective_end_date;
1635 --
1636 begin
1637 --
1638 hr_utility.set_location('Entering :'||l_proc, 10);
1639 hr_utility.set_location('PERSON ID :'||p_person_id, 10);
1640 hr_utility.set_location('BUSINESS_GROUP_ID :'||p_business_group_id, 10);
1641 hr_utility.set_location('EFFECTIVE_DATE :'||p_effective_date, 10);
1642 hr_utility.set_location('LIFE_EVENT_DATE :'||p_lf_evt_ocrd_dt, 10);
1643 hr_utility.set_location('PROFILE_ID :'||p_eligy_prfl_id, 10);
1644 --
1645
1646 -- fonm
1647 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date ) ;
1648 if ben_manage_life_events.fonm = 'Y'
1649 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
1650 --
1654 --
1651 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
1652 l_effective_date := nvl(l_fonm_cvg_strt_dt,l_effective_date ) ;
1653
1655 hr_utility.set_location ('Fonm Date '||l_effective_date ,10);
1656 end if;
1657
1658
1659
1660 for age in c_age_check loop
1661 --
1662 l_found_one := 'Y';
1663 --
1664 -- If person does not have a date of birth then he/she
1665 -- is not eligible.
1666 --
1667 if l_check_dob = true then
1668 hr_utility.set_location('HERE',10);
1669 open c_per;
1670 fetch c_per into l_dob;
1671 close c_per;
1672 if l_dob is null then
1673 l_elig := 'N';
1674 exit;
1675 end if;
1676 l_check_dob := false;
1677 end if;
1678 --
1679 -- Feed in proper ID
1680 -- This may not be correct logic
1681 --
1682 if l_oipl_id is not null then
1683 l_pl_id := null;
1684 l_pgm_id := null;
1685 elsif l_pl_id is not null then
1686 l_pgm_id := null;
1687 end if;
1688 --
1689 hr_utility.set_location('XXXX AGE = '||l_age,10);
1690 hr_utility.set_location('XXXX DOB = '||l_dob,10);
1691 hr_utility.set_location('XXXX MIN = '||age.mn_age_num,10);
1692 hr_utility.set_location('XXXX MAX = '||age.mx_age_num,10);
1693 hr_utility.set_location('XXXX EXCLD = '||age.excld_flag,10);
1694 --
1695 ben_derive_factors.determine_age
1696 (p_person_id => p_person_id ,
1697 p_per_dob => l_dob,
1698 p_age_fctr_id => age.age_fctr_id,
1699 p_pgm_id => l_pgm_id,
1700 p_pl_id => l_pl_id ,
1701 p_oipl_id => l_oipl_id ,
1702 p_per_in_ler_id => p_per_in_ler_id,
1703 p_effective_date => p_effective_date,
1704 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
1705 p_business_group_id => p_business_group_id,
1706 p_perform_rounding_flg => true,
1707 p_value => l_age,
1708 p_change_date => l_change_date,
1709 p_parent_person_id => p_contact_person_id,
1710 p_fonm_cvg_strt_dt => l_fonm_cvg_strt_dt );
1711 --
1712 hr_utility.set_location('XXXX AGE = '||l_age,10);
1713 hr_utility.set_location('XXXX DOB = '||l_dob,10);
1714 hr_utility.set_location('XXXX DOB = '||l_change_date,1965);
1715 hr_utility.set_location('XXXX MIN = '||age.mn_age_num,10);
1716 hr_utility.set_location('XXXX MAX = '||age.mx_age_num,10);
1717 hr_utility.set_location('XXXX EXCLD = '||age.excld_flag,10);
1718 hr_utility.set_location (' age chek in dep elig ' , 610);
1719 --Bug 2101937 we need to test the max limit differently for
1720 --whole numbers and decimal numbers.
1721 --if the age_max_num is a whole number we need to take age_max_num + 1
1722 --else we need to take the age_max_num + 0.00000001
1723 --
1724 if age.mx_age_num is not null then
1725 --
1726 l_max_age_number := null ;
1727 --
1728 if ( l_max_age_number <> trunc(l_max_age_number)
1729 OR ( age.mn_age_num is not null and age.mn_age_num <> trunc(age.mn_age_num) )) then
1730 --
1731 l_max_age_number := age.mx_age_num + 0.000000001 ;
1732 --
1733 else
1734 --
1735 l_max_age_number := age.mx_age_num + 1 ;
1736 --
1737 end if;
1738 --
1739 end if;
1740 --
1741 /**
1742 if ((l_age >= age.mn_age_num and l_age < ceil(age.mx_age_num+0.001) )
1743 --or (age.no_mn_age_flag = 'Y' and l_age < age.mx_age_num)
1744 --or (age.no_mx_age_flag = 'Y' and l_age > age.mn_age_num)) then
1745 --bug 1977901 fixes
1746 or (age.no_mn_age_flag = 'Y' and l_age < ceil(age.mx_age_num+0.001) )
1747 or (age.no_mx_age_flag = 'Y' and l_age >= age.mn_age_num)) then
1748 */
1749 --
1750 -- Bug 6615978 : Check the profile value and accordingly calculate the ineligibility date
1751 --
1752 l_defct_brch_dtctd := nvl(fnd_profile.VALUE ('BEN_DEFCT_BRCH_DTCTD'),'USE_ACTUAL_INELIG_DT');
1753
1754 if ((l_age >= age.mn_age_num and l_age < l_max_age_number)
1755 or (age.no_mn_age_flag = 'Y' and l_age < l_max_age_number)
1756 or (age.no_mx_age_flag = 'Y' and l_age >= age.mn_age_num) ) then
1757
1758 hr_utility.set_location (' passed age chekc in dep elig ' , 610);
1759 --
1760 if age.excld_flag = 'Y' then
1761 --
1762 -- exclude means if the criteria matches, the person is not eligible
1763 --
1764 l_elig := 'N';
1765 --
1766 -- Failed, set the change date
1767 -- Used 'GT_MIN' as the age can only increase and not decrease.
1768 -- g_elig_change_dt is used in bendsgel where the dpnt. was found
1769 -- previously eligible. So the only case for exclude can be when
1770 -- a dpnt crosses the minimum value.
1771 --
1772 if l_defct_brch_dtctd = 'USE_LE_OCRD_BRCH_DT' then
1773 l_elig_change_dt := l_effective_date - 1;
1774 else
1775 l_elig_change_dt:= benutils.derive_date(
1776 p_date => l_dob,
1777 p_uom => age.age_uom,
1778 p_min => age.mn_age_num,
1782 --
1779 p_max => age.mx_age_num,
1780 p_value => 'GT_MIN') - 1;
1781 end if;
1783 hr_utility.set_location('l_elig_change_dt :'||l_elig_change_dt, 5);
1784 --
1785 set_elig_change_dt(p_elig_change_dt => l_elig_change_dt,p_effective_date => l_effective_date); -- bug 4546890
1786 exit;
1787 --
1788 else -- age.excld_flag = 'N'
1789 --
1790 l_elig := 'Y';
1791 exit;
1792 --
1793 end if;
1794 --
1795 else
1796 --
1797 if age.excld_flag = 'Y' then
1798 --
1799 l_elig := 'Y';
1800 exit;
1801 --
1802 else
1803 --
1804 -- Used 'GT_MAX' as the age can only increase and not decrease.
1805 -- g_elig_change_dt is used in bendsgel where the dpnt. was found
1806 -- previously eligible. So the only case where the profile fails is
1807 -- when a dpnt crosses the maximum value.
1808 --
1809 l_elig := 'N';
1810 if l_defct_brch_dtctd = 'USE_LE_OCRD_BRCH_DT' then
1811 l_elig_change_dt := l_effective_date - 1;
1812 else
1813 l_elig_change_dt := benutils.derive_date(
1814 p_date => l_dob,
1815 p_uom => age.age_uom,
1816 p_min => age.mn_age_num,
1817 p_max => age.mx_age_num,
1818 p_value => 'GT_MAX') - 1;
1819 end if;
1820
1821 hr_utility.set_location('l_elig_change_dt :'||l_elig_change_dt, 10);
1822
1823 set_elig_change_dt(p_elig_change_dt => l_elig_change_dt,p_effective_date => l_effective_date); -- bug 4546890
1824 --
1825 end if;
1826 --
1827 end if;
1828 --
1829 -- End Bug 6615978
1830 --
1831 end loop;
1832 --
1833 if l_found_one = 'N' then
1834 --
1835 l_elig := 'Y';
1836 --
1837 end if;
1838 --
1839 hr_utility.set_location('l_elig :'||l_elig, 10);
1840 p_eligible_flag := l_elig;
1841 --
1842 if l_elig = 'Y' then
1843 p_inelig_rsn_cd := null;
1844 else
1845 p_inelig_rsn_cd := 'AGE';
1846 end if;
1847 --
1848 hr_utility.set_location('Leaving :'||l_proc, 20);
1849 --
1850 end check_age_elig;
1851 --
1852 procedure check_marital_elig
1853 (p_eligy_prfl_id in number,
1854 p_person_id in number,
1855 p_business_group_id in number,
1856 p_effective_date in date,
1857 p_lf_evt_ocrd_dt in date,
1858 p_marital_cd in varchar2,
1859 p_eligible_flag out nocopy varchar2,
1860 p_inelig_rsn_cd out nocopy varchar2) is
1861
1862 --
1863 l_proc varchar2(100):= g_package||'check_marital_elig';
1864 l_elig varchar2(10) := 'Y';
1865 l_eligy_prfl_id number(15) := p_eligy_prfl_id;
1866 l_bg_id number(15) := p_business_group_id;
1867 l_effective_date date := p_effective_date;
1868 --
1869 --fonm
1870 l_fonm_cvg_strt_dt date ;
1871
1872 -- Cursor to get marital status criteria
1873 --
1874 cursor c_ems is
1875 select mrtl_stat_cd
1876 from ben_elig_mrtl_stat_cvg_f ems
1877 where ems.dpnt_cvg_eligy_prfl_id = l_eligy_prfl_id
1878 and ems.business_group_id = l_bg_id
1879 and l_effective_date
1880 between ems.effective_start_date
1881 and ems.effective_end_date;
1882 --
1883 l_ems c_ems%rowtype;
1884 --
1885 begin
1886 --
1887 hr_utility.set_location('Entering: '||l_proc,10);
1888 --
1889
1890 -- fonm
1891 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date ) ;
1892 if ben_manage_life_events.fonm = 'Y'
1893 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
1894 --
1895 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
1896 l_effective_date := nvl(l_fonm_cvg_strt_dt,l_effective_date ) ;
1897
1898 --
1899 hr_utility.set_location ('Fonm Date '||l_effective_date ,10);
1900 end if;
1901
1902
1903 open c_ems;
1904 --
1905 loop
1906 --
1907 fetch c_ems into l_ems;
1908 exit when c_ems%notfound;
1909 --
1910 if l_ems.mrtl_stat_cd = p_marital_cd then
1911 --
1912 l_elig := 'Y';
1913 exit;
1914 --
1915 else
1916 --
1917 l_elig := 'N';
1918 --
1919 end if;
1920 --
1921 end loop;
1922 --
1923 close c_ems;
1924 --
1925 p_eligible_flag := l_elig;
1926 --
1927 if l_elig = 'Y' then
1928 p_inelig_rsn_cd := null;
1929 else
1930 p_inelig_rsn_cd := 'MRT';
1931 end if;
1932 --
1933 hr_utility.set_location('Leaving: '||l_proc,20);
1934 --
1935 end check_marital_elig;
1936 --
1937 procedure check_military_elig
1941 p_effective_date in date,
1938 (p_eligy_prfl_id in number,
1939 p_person_id in number,
1940 p_business_group_id in number,
1942 p_lf_evt_ocrd_dt in date,
1943 p_military_service in varchar2,
1944 p_eligible_flag out nocopy varchar2,
1945 p_inelig_rsn_cd out nocopy varchar2) is
1946
1947 --
1948 l_proc varchar2(100):= g_package || '.check_military_elig';
1949 l_elig varchar2(10) := 'Y';
1950 l_eligy_prfl_id number(15) := p_eligy_prfl_id;
1951 l_bg_id number(15) := p_business_group_id;
1952 l_effective_date date := p_effective_date;
1953 --
1954 --fonm
1955 l_fonm_cvg_strt_dt date ;
1956
1957 -- Cursor to get military status criteria
1958 --
1959 cursor c_emc is
1960 select mltry_stat_cd
1961 from ben_elig_mltry_stat_cvg_f emc
1962 where emc.dpnt_cvg_eligy_prfl_id = l_eligy_prfl_id
1963 and emc.business_group_id = l_bg_id
1964 and l_effective_date
1965 between emc.effective_start_date
1966 and emc.effective_end_date;
1967 --
1968 l_emc c_emc%rowtype;
1969 --
1970 begin
1971 --
1972 hr_utility.set_location('Entering: '||l_proc,10);
1973 --
1974 -- fonm
1975 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date ) ;
1976 if ben_manage_life_events.fonm = 'Y'
1977 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
1978 --
1979 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
1980 l_effective_date := nvl(l_fonm_cvg_strt_dt,l_effective_date ) ;
1981
1982 --
1983 hr_utility.set_location ('Fonm Date '||l_effective_date ,10);
1984 end if;
1985
1986
1987 open c_emc;
1988 --
1989 loop
1990 --
1991 fetch c_emc into l_emc;
1992 exit when c_emc%notfound;
1993 --
1994 if l_emc.mltry_stat_cd = nvl(p_military_service,'N') then
1995 --
1996 l_elig := 'Y';
1997 exit;
1998 --
1999 else
2000 --
2001 l_elig := 'N';
2002 --
2003 end if;
2004 --
2005 end loop;
2006 --
2007 close c_emc;
2008 --
2009 p_eligible_flag := l_elig;
2010 --
2011 if l_elig = 'Y' then
2012 p_inelig_rsn_cd := null;
2013 else
2014 p_inelig_rsn_cd := 'MLT';
2015 end if;
2016 --
2017 hr_utility.set_location('Leaving: '||l_proc,20);
2018 --
2019 end check_military_elig;
2020 --
2021 procedure check_student_elig
2022 (p_eligy_prfl_id in number,
2023 p_person_id in number,
2024 p_business_group_id in number,
2025 p_effective_date in date,
2026 p_lf_evt_ocrd_dt in date,
2027 p_student_status in varchar2,
2028 p_eligible_flag out nocopy varchar2,
2029 p_inelig_rsn_cd out nocopy varchar2) is
2030
2031 --
2032 l_proc varchar2(100):= g_package || '.check_student_elig';
2033 l_elig varchar2(10) := 'Y';
2034 l_eligy_prfl_id number(15) := p_eligy_prfl_id;
2035 l_bg_id number(15) := p_business_group_id;
2036 l_effective_date date := p_effective_date;
2037 --
2038 --fonm
2039 l_fonm_cvg_strt_dt date ;
2040
2041 -- Cursor to get student status criteria
2042 --
2043 cursor c_esc is
2044 select stdnt_stat_cd
2045 from ben_elig_stdnt_stat_cvg_f esc
2046 where esc.dpnt_cvg_eligy_prfl_id = l_eligy_prfl_id
2047 and esc.business_group_id = l_bg_id
2048 and l_effective_date
2049 between esc.effective_start_date
2050 and esc.effective_end_date;
2051 --
2052 l_esc c_esc%rowtype;
2053 --
2054 begin
2055 --
2056 hr_utility.set_location('Entering: '||l_proc,10);
2057 --
2058 -- fonm
2059 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date ) ;
2060 if ben_manage_life_events.fonm = 'Y'
2061 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
2062 --
2063 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
2064 l_effective_date := nvl(l_fonm_cvg_strt_dt,l_effective_date ) ;
2065
2066 --
2067 hr_utility.set_location ('Fonm Date '||l_effective_date ,10);
2068 end if;
2069
2070
2071 open c_esc;
2072 --
2073 loop
2074 --
2075 fetch c_esc into l_esc;
2076 exit when c_esc%notfound;
2077 --
2078 if l_esc.stdnt_stat_cd = p_student_status then
2079 --
2080 l_elig := 'Y';
2081 exit;
2082 --
2083 else
2084 --
2085 l_elig := 'N';
2086 --
2087 end if;
2088 --
2089 end loop;
2090 --
2091 close c_esc;
2092 --
2093 p_eligible_flag := l_elig;
2094 --
2095 if l_elig = 'Y' then
2096 p_inelig_rsn_cd := null;
2097 else
2098 p_inelig_rsn_cd := 'STU';
2099 end if;
2100 --
2101 hr_utility.set_location('Leaving: '||l_proc,20);
2105 procedure check_contact_elig
2102 --
2103 end check_student_elig;
2104 --
2106 (p_eligy_prfl_id in number,
2107 p_person_id in number,
2108 p_contact_person_id in number, -- Bug 6956648
2109 p_business_group_id in number,
2110 p_effective_date in date,
2111 p_lf_evt_ocrd_dt in date,
2112 p_contact_type in varchar2,
2113 p_eligible_flag out nocopy varchar2,
2114 p_inelig_rsn_cd out nocopy varchar2) is
2115 --
2116 l_proc varchar2(100):= g_package || '.check_contact_elig';
2117 l_elig varchar2(10) := 'Y';
2118 l_eligy_prfl_id number(15) := p_eligy_prfl_id;
2119 l_bg_id number(15) := p_business_group_id;
2120 l_effective_date date := p_effective_date;
2121 --
2122 --fonm
2123 l_fonm_cvg_strt_dt date ;
2124
2125 -- Cursor to get student status criteria
2126 --
2127 cursor c_dcr is
2128 select per_relshp_typ_cd
2129 from ben_dpnt_cvg_rqd_rlshp_f dcr
2130 where dcr.dpnt_cvg_eligy_prfl_id = l_eligy_prfl_id
2131 and dcr.business_group_id = l_bg_id
2132 and l_effective_date
2133 between dcr.effective_start_date
2134 and dcr.effective_end_date;
2135 --
2136 --
2137 cursor c_contact (p_contact_type in varchar2,
2138 p_effective_date in date) is
2139 select null
2140 from per_contact_relationships ctr
2141 where ctr.contact_type = p_contact_type
2142 and ctr.contact_person_id = p_person_id
2143 and ctr.person_id = p_contact_person_id -- Bug 6956648
2144 and ctr.personal_flag = 'Y'
2145 and p_effective_date between nvl(ctr.date_start,p_effective_date)
2146 and nvl(ctr.date_end,p_effective_date);
2147 --
2148 l_dummy varchar2(30);
2149 l_dcr c_dcr%rowtype;
2150 --
2151 begin
2152 --
2153 hr_utility.set_location('Entering: '||l_proc,10);
2154 --
2155 -- fonm
2156 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date ) ;
2157 if ben_manage_life_events.fonm = 'Y'
2158 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
2159 --
2160 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
2161 l_effective_date := nvl(l_fonm_cvg_strt_dt,l_effective_date ) ;
2162
2163 --
2164 hr_utility.set_location ('Fonm Date '||l_effective_date ,10);
2165 end if;
2166
2167
2168 open c_dcr;
2169 --
2170 loop
2171 --
2172 fetch c_dcr into l_dcr;
2173 exit when c_dcr%notfound;
2174 --
2175 if l_dcr.per_relshp_typ_cd = p_contact_type then
2176 --
2177 l_elig := 'Y';
2178 exit;
2179 --
2180 else
2181 --
2182 l_elig := 'N';
2183 -- bug#4318031 - check other relationships of contact person with personal flag
2184 open c_contact (l_dcr.per_relshp_typ_cd, l_effective_date);
2185 fetch c_contact into l_dummy;
2186 if c_contact%found then
2187 l_elig := 'Y';
2188 exit;
2189 end if;
2190 close c_contact;
2191 --
2192 end if;
2193 --
2194 end loop;
2195 --
2196 close c_dcr;
2197 --
2198 p_eligible_flag := l_elig;
2199 --
2200 if l_elig = 'Y' then
2201 p_inelig_rsn_cd := null;
2202 else
2203 p_inelig_rsn_cd := 'REL';
2204 end if;
2205 --
2206 hr_utility.set_location('Leaving: '||l_proc,20);
2207 --
2208 end check_contact_elig;
2209 --
2210 procedure check_disabled_elig
2211 (p_eligy_prfl_id in number,
2212 p_person_id in number,
2213 p_business_group_id in number,
2214 p_effective_date in date,
2215 p_lf_evt_ocrd_dt in date,
2216 p_per_dsbld_type in varchar2,
2217 p_eligible_flag out nocopy varchar2,
2218 p_inelig_rsn_cd out nocopy varchar2) is
2219 --
2220 l_proc varchar2(100):= g_package || '.check_disabled_elig';
2221 l_elig varchar2(10) := 'Y';
2222 l_eligy_prfl_id number(15) := p_eligy_prfl_id;
2223 l_bg_id number(15) := p_business_group_id;
2224 l_effective_date date := p_effective_date;
2225 --
2226 --fonm
2227 l_fonm_cvg_strt_dt date ;
2228
2229 -- Cursor to get disabled status criteria
2230 --
2231 cursor c_edc is
2232 select dsbld_cd
2233 from ben_elig_dsbld_stat_cvg_f edc
2234 where edc.dpnt_cvg_eligy_prfl_id = l_eligy_prfl_id
2235 and edc.business_group_id = l_bg_id
2236 and l_effective_date
2237 between edc.effective_start_date
2238 and edc.effective_end_date;
2239 --
2240 l_edc c_edc%rowtype;
2241 --
2242 begin
2243 --
2244 hr_utility.set_location('Entering: '||l_proc,10);
2245 hr_utility.set_location('*** SUP in Check disability p_effective_date '|| p_effective_date,10);
2246
2247 --
2248 -- fonm
2249 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date ) ;
2250 if ben_manage_life_events.fonm = 'Y'
2251 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
2255
2252 --
2253 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
2254 l_effective_date := nvl(l_fonm_cvg_strt_dt,l_effective_date ) ;
2256 --
2257 hr_utility.set_location ('Fonm Date '||l_effective_date ,10);
2258 end if;
2259
2260
2261 open c_edc;
2262 --
2263 loop
2264 --
2265 fetch c_edc into l_edc;
2266 exit when c_edc%notfound;
2267 --
2268 if l_edc.dsbld_cd = p_per_dsbld_type then
2269 --
2270 l_elig := 'Y';
2271 exit;
2272 --
2273 else
2274 --
2275 l_elig := 'N';
2276 --
2277 end if;
2278 --
2279 end loop;
2280 --
2281 close c_edc;
2282 --
2283 p_eligible_flag := l_elig;
2284 --
2285 if l_elig = 'Y' then
2286 p_inelig_rsn_cd := null;
2287 else
2288 p_inelig_rsn_cd := 'DSB';
2289 end if;
2290 --
2291 hr_utility.set_location('** sup in disability check p_eligible_flag '||p_eligible_flag,200);
2292 hr_utility.set_location('Leaving: '||l_proc,20);
2293 --
2294 end check_disabled_elig;
2295 --
2296 procedure check_postal_elig
2297 (p_eligy_prfl_id in number,
2298 p_person_id in number,
2299 p_business_group_id in number,
2300 p_effective_date in date,
2301 p_lf_evt_ocrd_dt in date,
2302 p_postal_code in varchar2,
2303 p_eligible_flag out nocopy varchar2,
2304 p_inelig_rsn_cd out nocopy varchar2) is
2305 --
2306 l_proc varchar2(100):= g_package || '.check_postal_elig';
2307 l_elig varchar2(10) := 'N';
2308 l_eligy_prfl_id number(15) := p_eligy_prfl_id;
2309 l_bg_id number(15) := p_business_group_id;
2310 l_effective_date date := p_effective_date;
2311 l_found_one varchar2(30) := 'N';
2312 --
2313 --fonm
2314 l_fonm_cvg_strt_dt date ;
2315
2316 -- Cursor to grab zip code range requirement.
2317 --
2318 cursor c_zip_code_rng is
2319 select rzr.from_value,
2320 rzr.to_value,
2321 epl.excld_flag
2322 from ben_pstl_zip_rng_f rzr,
2323 ben_elig_pstl_cd_r_rng_cvg_f epl
2324 where rzr.pstl_zip_rng_id = epl.pstl_zip_rng_id
2325 and epl.dpnt_cvg_eligy_prfl_id = l_eligy_prfl_id
2326 and epl.business_group_id = l_bg_id
2327 and rzr.business_group_id = l_bg_id
2328 and l_effective_date
2329 between epl.effective_start_date
2330 and epl.effective_end_date
2331 and l_effective_date
2332 between rzr.effective_start_date
2333 and rzr.effective_end_date
2334 order by epl.ordr_num;
2335 --
2336 l_zip c_zip_code_rng%rowtype;
2337 l_len number ;
2338 --
2339 begin
2340 --
2341 hr_utility.set_location('Entering : '||l_proc,10);
2342 --
2343 -- fonm
2344 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date ) ;
2345 if ben_manage_life_events.fonm = 'Y'
2346 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
2347 --
2348 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
2349 l_effective_date := nvl(l_fonm_cvg_strt_dt,l_effective_date ) ;
2350
2351 --
2352 hr_utility.set_location ('Fonm Date '||l_effective_date ,10);
2353 end if;
2354
2355
2356 open c_zip_code_rng;
2357 --
2358 loop
2359 --
2360 fetch c_zip_code_rng into l_zip;
2361 exit when c_zip_code_rng%notfound;
2362 --
2363 l_found_one := 'Y';
2364 hr_utility.set_location('Found zip criteria. from:'||
2365 l_zip.from_value||' to:'||
2366 l_zip.to_value||' '||p_postal_code,2219.2);
2367
2368 l_len := length(nvl(l_zip.from_value,'00000')) ;
2369 --
2370 hr_utility.set_location(' len ' || l_len , 2219.2);
2371 ---
2372 if nvl( length(p_postal_code) >= length(l_zip.from_value) and
2373 (substr(nvl(p_postal_code,'-1'),1,l_len )
2374 between nvl(l_zip.from_value,'00000') and
2375 nvl(l_zip.to_value,nvl(p_postal_code,'-1'))),false) then
2376 hr_utility.set_location('result true ' ,2219.2);
2377 --
2378 if l_zip.excld_flag = 'Y' then
2379 -- exclude means if the criteria matches, the person is not eligible
2380 l_elig := 'N';
2381 hr_utility.set_location(' l_elig := No ',99);
2382 exit;
2383 --
2384 else -- l_zip.excld_flag = 'N'
2385 -- one criteria instance passed, leave.
2386 l_elig := 'Y';
2387 hr_utility.set_location(' l_elig := yes ',99);
2388 exit;
2389 --
2390 end if;
2391 --
2392 elsif l_zip.excld_flag = 'Y' then -- No Match and exclude is 'Y' case
2393 --
2394 hr_utility.set_location('No Match and exclude is yes ' ,99);
2395 l_elig := 'Y';
2396 -- exit;
2397 --
2398 end if;
2399 -- one criteria instance failed. keep going to check others.
2400 end loop;
2401 --
2402 hr_utility.set_location(' l_elig '||l_elig ,99);
2406 --
2403 if l_found_one = 'N' then
2404 --
2405 -- No criteria passes
2407 l_elig := 'Y';
2408 --
2409 end if;
2410 --
2411 close c_zip_code_rng;
2412 --
2413 p_eligible_flag := l_elig;
2414 --
2415 if l_elig = 'Y' then
2416 p_inelig_rsn_cd := null;
2417 else
2418 p_inelig_rsn_cd := 'ZIP';
2419 end if;
2420 --
2421 hr_utility.set_location(' l_elig '||l_elig ,100);
2422 hr_utility.set_location('Leaving: '||l_proc,20);
2423 --
2424 end check_postal_elig;
2425 --
2426 procedure check_cvrd_anthr_pl_elig
2427 (p_eligy_prfl_id in number,
2428 p_person_id in number,
2429 p_business_group_id in number,
2430 p_effective_date in date,
2431 p_lf_evt_ocrd_dt in date,
2432 p_pl_id in number,
2433 p_eligible_flag out nocopy varchar2,
2434 p_inelig_rsn_cd out nocopy varchar2) is
2435 --
2436 l_proc varchar2(100):= g_package || '.check_cvrd_anthr_pl_elig';
2437 l_elig varchar2(10) := 'Y';
2438 l_cvg_det_dt date;
2439 l_exists varchar(1);
2440 --
2441 --fonm
2442 l_effective_date date ;
2443 l_fonm_cvg_strt_dt date ;
2444
2445 -- Cursor to get pl_id criteria
2446 --
2447 cursor c_get_cvrd_anthr_pl is
2448 select dpc.pl_id,dpc.cvg_det_dt_cd, dpc.excld_flag
2449 from ben_dpnt_cvrd_anthr_pl_cvg_f dpc
2450 where dpc.dpnt_cvg_eligy_prfl_id = p_eligy_prfl_id
2451 and dpc.business_group_id = p_business_group_id
2452 and l_effective_date
2453 between dpc.effective_start_date
2454 and dpc.effective_end_date;
2455 --
2456 cursor c_get_elig_cvrd_dpnt(p_pl_id in number) is
2457 select null
2458 from ben_elig_cvrd_dpnt_f pdp
2459 ,ben_prtt_enrt_rslt_f pen
2460 where pdp.dpnt_person_id = p_person_id
2461 and pdp.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
2462 --and pen.prtt_enrt_rslt_stat_cd not in ('VOIDD','BCKDT')
2463 and pen.prtt_enrt_rslt_stat_cd is null
2464 and pen.pl_id = p_pl_id
2465 and l_cvg_det_dt
2466 between pdp.cvg_strt_dt
2467 and nvl(pdp.cvg_thru_dt,hr_api.g_eot)
2468 and pdp.cvg_strt_dt is not null
2469 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
2470 between pdp.effective_start_date
2471 and pdp.effective_end_date
2472 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
2473 between pen.effective_start_date
2474 and pen.effective_end_date
2475 ;
2476
2477 --
2478 l_dpc c_get_cvrd_anthr_pl%rowtype;
2479 --
2480 begin
2481 --
2482 hr_utility.set_location('Entering: '||l_proc,10);
2483 --
2484 -- fonm
2485 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date ) ;
2486 if ben_manage_life_events.fonm = 'Y'
2487 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
2488 --
2489 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
2490 l_effective_date := nvl(l_fonm_cvg_strt_dt,l_effective_date ) ;
2491
2492 --
2493 hr_utility.set_location ('Fonm Date '||l_effective_date ,10);
2494 end if;
2495
2496
2497 open c_get_cvrd_anthr_pl;
2498 --
2499 loop
2500 --
2501 fetch c_get_cvrd_anthr_pl into l_dpc;
2502 exit when c_get_cvrd_anthr_pl%notfound;
2503 --
2504 -- Check if contact person is currently covered in
2505 -- under the plan.
2506 --
2507 ben_determine_date.main
2508 (p_date_cd => l_dpc.cvg_det_dt_cd,
2509 p_effective_date => p_effective_date,
2510 p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt,
2511 p_returned_date => l_cvg_det_dt,
2512 p_fonm_cvg_strt_dt=> l_fonm_cvg_strt_dt);
2513 --
2514 open c_get_elig_cvrd_dpnt(l_dpc.pl_id);
2515 fetch c_get_elig_cvrd_dpnt into l_exists;
2516 if c_get_elig_cvrd_dpnt%found then
2517 close c_get_elig_cvrd_dpnt;
2518 --
2519 -- If the dependent is covered under the plan
2520 -- and the exclude flag is set to 'Y' then he/she
2521 -- is not eligible.
2522 --
2523 hr_utility.set_location ('Found c_get_elig_cvrd_dpnt '||l_dpc.excld_flag,34634611);
2524 if l_dpc.excld_flag = 'Y' then
2525 l_elig := 'N';
2526 exit ;
2527 else
2528 exit;
2529 end if;
2530 --
2531 else
2532 --
2533 close c_get_elig_cvrd_dpnt;
2534 --
2535 -- If the dependent is not covered under plan and the
2536 -- exclude flag is set to 'Y', then the he/she is eligible.
2537 --
2538 hr_utility.set_location ('Not Found c_get_elig_cvrd_dpnt '||l_dpc.excld_flag,34634611);
2539 if l_dpc.excld_flag = 'N' then
2540 l_elig := 'N';
2541 else
2542 l_elig := 'Y' ;
2543 -- exit;
2544 end if;
2545 --
2546 end if;
2547 end loop;
2548 --
2549 close c_get_cvrd_anthr_pl;
2550 --
2551 p_eligible_flag := l_elig;
2552 --
2553 if l_elig = 'Y' then
2554 p_inelig_rsn_cd := null;
2555 else
2556 p_inelig_rsn_cd := 'CVP';
2557 end if;
2558 --
2559 hr_utility.set_location('Leaving: '||l_proc,20);
2560
2561 --
2562 end check_cvrd_anthr_pl_elig;
2563 --
2564 procedure check_dsgntr_enrld_cvg_elig
2565 (p_eligy_prfl_id in number,
2566 p_person_id in number,
2567 p_dsgntr_id in number,
2568 p_business_group_id in number,
2569 p_effective_date in date,
2570 p_lf_evt_ocrd_dt in date,
2571 p_pgm_id in number,
2572 p_eligible_flag out nocopy varchar2,
2573 p_inelig_rsn_cd out nocopy varchar2) is
2574 --
2575 l_proc varchar2(100):= g_package || '.check_dsgntr_enrld_cvg_elig';
2576 l_elig varchar2(10) := 'Y';
2577 l_exists varchar(1);
2578 l_dsgntr_crntly_enrld_flag varchar(1);
2579 --
2580 --fonm
2581 l_effective_date date ;
2582 l_fonm_cvg_strt_dt date ;
2583
2584 -- Cursor to get criteria
2585 --
2586 cursor c_get_dsgntr_enrld_cvg is
2587 select dec.dsgntr_crntly_enrld_flag
2588 from ben_dsgntr_enrld_cvg_f dec
2589 where dec.dpnt_cvg_eligy_prfl_id = p_eligy_prfl_id
2590 and dec.business_group_id = p_business_group_id
2591 and l_effective_date
2592 between dec.effective_start_date
2593 and dec.effective_end_date;
2594 --
2595 cursor c_get_prtt_enrt_rslt is
2596 select null
2597 from ben_prtt_enrt_rslt_f pen
2598 where pen.person_id = p_dsgntr_id
2599 and pen.pgm_id = p_pgm_id
2600 --and pen.prtt_enrt_rslt_stat_cd not in ('VOIDD','BCKDT')
2601 and pen.prtt_enrt_rslt_stat_cd is null
2602 and nvl(p_lf_evt_ocrd_dt,p_effective_date)
2603 between pen.enrt_cvg_strt_dt and pen.enrt_cvg_thru_dt
2604 and pen.enrt_cvg_thru_dt <= pen.effective_end_date;
2605
2606 --
2607 begin
2608 --
2609 hr_utility.set_location('Entering: '||l_proc,10);
2610 hr_utility.set_location('eligy_prfl: '||p_eligy_prfl_id,10);
2611 hr_utility.set_location('dsgntr_id: '||p_dsgntr_id,10);
2612 --
2613 -- fonm
2614 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date ) ;
2615 if ben_manage_life_events.fonm = 'Y'
2616 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
2617 --
2618 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
2619 l_effective_date := nvl(l_fonm_cvg_strt_dt,l_effective_date ) ;
2620
2621 --
2622 hr_utility.set_location ('Fonm Date '||l_effective_date ,10);
2623 end if;
2624
2625
2626 open c_get_dsgntr_enrld_cvg;
2627 --
2628 fetch c_get_dsgntr_enrld_cvg into l_dsgntr_crntly_enrld_flag;
2629 if c_get_dsgntr_enrld_cvg%found then
2630 --
2631 open c_get_prtt_enrt_rslt;
2632 fetch c_get_prtt_enrt_rslt into l_exists;
2633 if c_get_prtt_enrt_rslt%found then
2634 close c_get_prtt_enrt_rslt;
2635 --
2636 -- If designator is currently enrolled in the program and flag = N,
2637 -- then contact person is not eligible.
2638 --
2639 if l_dsgntr_crntly_enrld_flag = 'N' then
2640 l_elig := 'N';
2641 end if;
2642 else
2643 close c_get_prtt_enrt_rslt;
2644 --
2645 -- If designator is not enrolled in the program and flag = Y,
2646 -- then contact person is not eligible.
2647 --
2648 if l_dsgntr_crntly_enrld_flag = 'Y' then
2649 l_elig := 'N';
2650 end if;
2651 end if;
2652 --
2653 end if;
2654 close c_get_dsgntr_enrld_cvg;
2655 --
2656 p_eligible_flag := l_elig;
2657 --
2658 if l_elig = 'Y' then
2659 p_inelig_rsn_cd := null;
2660 else
2661 p_inelig_rsn_cd := 'DEG';
2662 end if;
2663 --
2664 hr_utility.set_location('Leaving: '||l_proc,20);
2665 --
2666 end check_dsgntr_enrld_cvg_elig;
2667 --
2668 function get_elig_change_dt return date is
2669 --
2670 begin
2671 --
2672 return nvl(g_elig_change_dt,g_effective_date);
2673 --
2674 end get_elig_change_dt;
2675 --
2676 END;
[Home] [Help]