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