[Home] [Help]
PACKAGE BODY: APPS.BEN_DETERMINE_ELIGIBILITY
Source
1 package body ben_determine_eligibility as
2 /* $Header: bendetel.pkb 120.5.12010000.3 2008/11/13 18:13:07 krupani ship $ */
3 --
4 -- -----------------------------------------------------------------------------
5 -- |-----------------------< determine_elig_prfls >----------------------------|
6 -- -----------------------------------------------------------------------------
7 --
8 -- This procedure is the main calling procedure.
9 -- It also determines all profiles or rules associated
10 -- with the comp object that is being passed through.
11 --
12 procedure determine_elig_prfls
13 (p_comp_obj_tree_row in out NOCOPY ben_manage_life_events.g_cache_proc_objects_rec
14 ,p_par_elig_state in out NOCOPY ben_comp_obj_filter.g_par_elig_state_rec
15 ,p_per_row in out NOCOPY per_all_people_f%rowtype
16 ,p_empasg_row in out NOCOPY per_all_assignments_f%rowtype
17 ,p_benasg_row in out NOCOPY per_all_assignments_f%rowtype
18 ,p_appasg_row in out NOCOPY ben_person_object.g_cache_ass_table
19 ,p_empasgast_row in out NOCOPY per_assignment_status_types%rowtype
20 ,p_benasgast_row in out NOCOPY per_assignment_status_types%rowtype
21 ,p_pil_row in out NOCOPY ben_per_in_ler%rowtype
22 ,p_person_id in number
23 ,p_business_group_id in number
24 ,p_effective_date in date
25 ,p_lf_evt_ocrd_dt in date
26 ,p_pl_id in number
27 ,p_pgm_id in number
28 ,p_oipl_id in number
29 ,p_plip_id in number
30 ,p_ptip_id in number
31 ,p_ler_id in number
32 ,p_comp_rec in out NOCOPY ben_derive_part_and_rate_facts.g_cache_structure
33 ,p_oiplip_rec in out NOCOPY ben_derive_part_and_rate_facts.g_cache_structure
34 --
35 ,p_eligible out nocopy boolean
36 ,p_not_eligible out nocopy boolean
37 --
38 ,p_newly_elig out nocopy boolean
39 ,p_newly_inelig out nocopy boolean
40 ,p_first_elig out nocopy boolean
41 ,p_first_inelig out nocopy boolean
42 ,p_still_elig out nocopy boolean
43 ,p_still_inelig out nocopy boolean
44 )
45 is
46 --
47 l_proc varchar2(100):= 'ben_determine_eligibility.determine_elig_prfls';
48 --
49 l_eligprof_dets ben_cep_cache.g_cobcep_odcache;
50 l_tmpelp_dets ben_cep_cache.g_cobcep_odcache := ben_cep_cache.g_cobcep_odcache();
51 /*
52 l_eligprof_dets ben_elp_cache.g_cobcep_cache;
53 l_tmpelp_dets ben_elp_cache.g_cobcep_cache;
54 */
55 --
56 l_elptorrw_num binary_integer;
57 l_inst_count number;
58 l_elig_flag boolean := false;
59 l_elig_per_id number;
60 l_prtn_ovridn_thru_dt date;
61 l_effective_date date;
62 l_prtn_ovridn_flag ben_elig_per_f.prtn_ovridn_flag%type;
63 l_rl_count number;
64 l_match_one boolean := false;
65 l_match_one_rl varchar2(15) := 'FALSE';
66 l_outputs ff_exec.outputs_t;
67 l_ok_so_far varchar2(1) := 'Y';
68 l_mx_wtg_perd_prte_elig boolean := false;
69 l_elig_apls_flag varchar2(30);
70 l_dpnt_elig_flag varchar2(1) := 'Y';
71 l_dependent_elig_flag varchar2(1) := 'Y';
72 l_dpnt_inelig_rsn_cd ben_elig_dpnt.inelg_rsn_cd%type;
73 l_per_in_ler_id ben_per_in_ler.per_in_ler_id%type;
74 l_per_cvrd_cd ben_pl_f.per_cvrd_cd%type;
75 l_elig_inelig_cd ben_elig_to_prte_rsn_f.elig_inelig_cd%type;
76 l_dpnt_pl_id ben_pl_f.pl_id%type;
77 l_exists varchar2(30);
78 --
79 l_terminated per_assignment_status_types.per_system_status%type;
80 l_assignment_id per_all_assignments_f.assignment_id%type;
81 l_found_profile varchar2(1) := 'N';
82 l_pgm_rec ben_cobj_cache.g_pgm_inst_row;
83 l_pl_rec ben_cobj_cache.g_pl_inst_row;
84 l_pl2_rec ben_pl_f%rowtype;
85 l_pl3_rec ben_pl_f%rowtype;
86 l_ptip2_rec ben_ptip_f%rowtype;
87 l_oipl_rec ben_cobj_cache.g_oipl_inst_row;
88 l_plip_rec ben_cobj_cache.g_plip_inst_row;
89 l_ptip_rec ben_cobj_cache.g_ptip_inst_row;
90 l_elig_to_prte_rsn_row ben_cobj_cache.g_etpr_inst_row;
91 --
92 -- Task 130 : Variables used for extracting vrfy_fmly_mmbr_cd
93 -- Need to look at the heirarchy.
94 --
95 l_par_pgm_rec ben_cobj_cache.g_pgm_inst_row;
96 l_par_ptip_rec ben_cobj_cache.g_ptip_inst_row;
97 l_par_plip_rec ben_cobj_cache.g_plip_inst_row;
98 l_par_pl_rec ben_cobj_cache.g_pl_inst_row;
99 l_par_pgm_elig_rec ben_cobj_cache.g_etpr_inst_row;
100 l_par_ptip_elig_rec ben_cobj_cache.g_etpr_inst_row;
101 l_par_plip_elig_rec ben_cobj_cache.g_etpr_inst_row;
102 l_par_pl_elig_rec ben_cobj_cache.g_etpr_inst_row;
103 --
104 l_inst_set ben_elig_rl_cache.g_elig_rl_inst_tbl;
105 l_elig_rl_cnt number := 0;
106 l_ctr_count number := 0;
107 l_jurisdiction_code varchar2(30);
108 --
109 l_per_rec per_all_people_f%rowtype;
110 l_ass_rec per_all_assignments_f%rowtype;
111 l_loc_rec hr_locations_all%rowtype;
112 l_ctr_rec per_contact_relationships%rowtype;
113 l_hsc_rec hr_soft_coding_keyflex%rowtype;
114 l_org_rec hr_all_organization_units%rowtype;
115 l_loop_count number;
116 --
117 l_elig_for_pgm_flag varchar(30);
118 l_elig_for_ptip_flag varchar(30);
119 l_elig_for_plip_flag varchar(30);
120 l_elig_for_pl_flag varchar(30);
121 l_cagrelig_cnt pls_integer;
122 --
123 --FONM
124 l_fonm_cvg_strt_dt DATE ;
125 --END FONM
126 cursor c_get_contacts is
127 select ctr.*
128 from per_contact_relationships ctr,
129 per_all_people_f ppf
130 where ctr.person_id = p_person_id
131 and ctr.personal_flag = 'Y'
132 and ctr.contact_person_id = ppf.person_id
133 and nvl(l_fonm_cvg_strt_dt,nvl(p_lf_evt_ocrd_dt,p_effective_date))
134 between ppf.effective_start_date
135 and ppf.effective_end_date
136 and nvl(l_fonm_cvg_strt_dt,nvl(p_lf_evt_ocrd_dt,p_effective_date))
137 between nvl(ctr.date_start,hr_api.g_sot)
138 and nvl(ctr.date_end,hr_api.g_eot)
139 and ctr.business_group_id = p_business_group_id;
140 --
141 cursor c_get_no_of_contacts is
142 select count(ctr.person_id)
143 from per_contact_relationships ctr,
144 per_all_people_f ppf
145 where ctr.person_id = p_person_id
146 and ctr.personal_flag = 'Y'
147 and ctr.contact_person_id = ppf.person_id
148 and nvl(l_fonm_cvg_strt_dt,nvl(p_lf_evt_ocrd_dt,p_effective_date))
149 between ppf.effective_start_date
150 and ppf.effective_end_date
151 and nvl(l_fonm_cvg_strt_dt,nvl(p_lf_evt_ocrd_dt,p_effective_date))
152 between nvl(ctr.date_start,hr_api.g_sot)
153 and nvl(ctr.date_end,hr_api.g_eot)
154 and ctr.business_group_id = p_business_group_id;
155
156
157 -- bug # 2424041 begins
158 cursor c_ptip_pl_typ(c_effective_date date) is
159 select pl_typ_id
160 from ben_ptip_f
161 where ptip_id = p_ptip_id
162 and c_effective_date
163 between effective_start_date
164 and effective_end_date;
165 --
166 cursor c_plip_pl_typ(c_effective_date date) is
167 select pln.pl_typ_id
168 from ben_plip_f plip,
169 ben_pl_f pln
170 where plip_id = p_plip_id
171 and c_effective_date
172 between plip.effective_start_date and plip.effective_end_date
173 and pln.pl_id = plip.pl_id
174 and c_effective_date
175 between pln.effective_start_date and pln.effective_end_date ;
176 --
177 cursor c_oipl_pl_typ(c_effective_date date) is
178 select pln.pl_typ_id
179 from ben_oipl_f oipl,
180 ben_pl_f pln
181 where oipl_id = p_oipl_id
182 and c_effective_date
183 between oipl.effective_start_date and oipl.effective_end_date
184 and pln.pl_id = oipl.pl_id
185 and c_effective_date
186 between pln.effective_start_date and pln.effective_end_date ;
187 --
188 l_pl_typ_id number;
189 --
190 -- bug 2424041 ends
191 --
192 l_typ_rec ben_person_object.g_cache_typ_table;
193 l_ast_rec per_assignment_status_types%rowtype;
194 l_appass_rec ben_person_object.g_cache_ass_table;
195 --
196 cursor c_ff_use_asg(cv_formula_id in number) is
197 select 'Y'
198 from ff_fdi_usages_f
199 where FORMULA_ID = cv_formula_id
200 and ITEM_NAME = 'ASSIGNMENT_ID'
201 and usage = 'U';
202 --
203 l_ff_use_asg_id_flag varchar2(1);
204 l_vrfy_fmly_mmbr_cd varchar2(30);
205 l_vrfy_fmly_mmbr_rl number;
206 l_pl_id number;
207 --
208 -- l_env ben_env_object.g_global_env_rec_type;
209 l_env_rec ben_env_object.g_global_env_rec_type;
210 l_benmngle_parm_rec benutils.g_batch_param_rec;
211 --
212 l_score_tab ben_evaluate_elig_profiles.scoreTab;
213 l_ctp_rec ben_ptip_f%rowtype;
214 l_pln_rec ben_pl_f%rowtype;
215 begin
216 --
217 g_debug := hr_utility.debug_enabled;
218 --
219 if g_debug then
220 hr_utility.set_location('Entering :'||l_proc,10);
221 end if;
222 --
223 if ben_manage_life_events.fonm = 'Y'
224 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
225 --
226 l_fonm_cvg_strt_dt := ben_manage_life_events.g_fonm_cvg_strt_dt ;
227 --
228 end if;
229 --
230 l_per_rec := p_per_row;
231 --
232 -- Set assignment information rows
233 --
234 -- Assumption for IREC is that only app assg row would be passed and we would pick
235 -- up the right assignment
236 if p_empasg_row.assignment_id is null then
237 --
238 l_ass_rec := p_benasg_row;
239 --
240 -- If benefit assignment not found, get applicant assignment.
241 --
242 if l_ass_rec.assignment_id is null then
243 --
244 l_appass_rec := p_appasg_row;
245 --
246 else
247 --
248 l_ast_rec := p_benasgast_row;
249 --
250 end if;
251 --
252 else
253 --
254 -- Bug : 1735996 : Assignment row is properly set.
255 -- Added the line below.
256 --
257 l_ass_rec := p_empasg_row;
258 l_ast_rec := p_empasgast_row;
259 --
260 end if;
261 --
262 l_terminated := l_ast_rec.per_system_status;
263 l_assignment_id := l_ass_rec.assignment_id;
264 --
265 -- Get the environment record.
266 --
267 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date);
268 -- Set up cache structures depending on comp object passed in
269 --
270 if p_pgm_id is not null then
271 --
272 if g_debug then
273 hr_utility.set_location('PGM_ID '||p_pgm_id,12);
274 end if;
275 --
276 l_pgm_rec := ben_cobj_cache.g_pgm_currow;
277 --
278 elsif p_pl_id is not null then
279 --
280 if g_debug then
281 hr_utility.set_location('PL_ID '||p_pl_id,14);
282 end if;
283 --
284 l_pl_rec := ben_cobj_cache.g_pl_currow;
285 --
286 elsif p_oipl_id is not null then
287 --
288 if g_debug then
289 hr_utility.set_location('OIPL_ID '||p_oipl_id,16);
290 end if;
291 --
292 l_oipl_rec := ben_cobj_cache.g_oipl_currow;
293 --
294 elsif p_plip_id is not null then
295 --
296 if g_debug then
297 hr_utility.set_location('PLIP_ID '||p_plip_id,18);
298 end if;
299 --
300 l_plip_rec := ben_cobj_cache.g_plip_currow;
301 --
302 elsif p_ptip_id is not null then
303 --
304 if g_debug then
305 hr_utility.set_location('PTIP_ID '||p_ptip_id,20);
306 end if;
307 --
308 l_ptip_rec := ben_cobj_cache.g_ptip_currow;
309 --
310 end if;
311 --
312 if g_debug then
313 hr_utility.set_location('l_pl_typ_id : '|| l_pl_typ_id ,16871);
314 end if;
315 --
316 -- Check parent eligibility
317 --
318 if g_debug then
319 hr_utility.set_location(l_proc||' Parent elig ',10);
320 end if;
321 --
322 if p_ptip_id is not null
323 and p_par_elig_state.elig_for_pgm_flag = 'N'
324 then
325 --
326 -- Person is not eligible for the program
327 -- therefore person is not eligible for
328 -- plan in program
329 --
330 -- The pl, ptip and plip eligibilty sets the elig_for_pl_flag
331 --
332 ben_manage_life_events.g_output_string :=
333 ben_manage_life_events.g_output_string||
334 'Elg: No '||
335 'Rsn: Inelig Parent Pgm';
336 --
337 fnd_message.set_name('BEN','BEN_92219_INELIG_PGM');
338 raise g_not_eligible;
339 --
340 elsif p_plip_id is not null
341 and p_par_elig_state.elig_for_ptip_flag = 'N'
342 then
343 --
344 -- Person is not eligible for the program
345 -- therefore person is not eligible for
346 -- plan in program
347 --
348 -- The pl, ptip and plip eligibilty sets the elig_for_pl_flag
349 --
350 ben_manage_life_events.g_output_string :=
351 ben_manage_life_events.g_output_string||
352 'Elg: No '||
353 'Rsn: Inelig Parent Pgm';
354 --
355 fnd_message.set_name('BEN','BEN_92219_INELIG_PGM');
356 raise g_not_eligible;
357 --
358 elsif p_pl_id is not null
359 and p_par_elig_state.elig_for_plip_flag = 'N'
360 then
361 --
362 -- Person is not eligible for the program
363 -- therefore person is not eligible for
364 -- plan in program
365 --
366 -- The pl, ptip and plip eligibilty sets the elig_for_pl_flag
367 --
368 ben_manage_life_events.g_output_string :=
369 ben_manage_life_events.g_output_string||
370 'Elg: No '||
371 'Rsn: Inelig Parent Pgm';
372 --
373 fnd_message.set_name('BEN','BEN_92219_INELIG_PGM');
374 raise g_not_eligible;
375 --
376 elsif p_oipl_id is not null
377 and p_par_elig_state.elig_for_pl_flag = 'N'
378 then
379 --
380 -- Person is not eligible for the plan
381 -- therefore person not eligible for the
382 -- option in plan
383 --
384 ben_manage_life_events.g_output_string :=
385 ben_manage_life_events.g_output_string||
386 'Elg: No '||
387 'Rsn: Inelig Pln';
388 --
389 fnd_message.set_name('BEN','BEN_92221_INELIG_PLN');
390 raise g_not_eligible;
391 --
392 end if;
393 --
394 -- Check if its worth doing the profile checking at all, this only occurs
395 -- if the participation override allowed flag is on for the comp object and
396 -- the elig per participation override flag is on and the effective date is
397 -- less than (or equal to) the participation override thru date
398 --
399 if g_debug then
400 hr_utility.set_location(l_proc||' Part Ovr ',10);
401 end if;
402 --
403 if p_pgm_id is not null then
404 --
405 l_elig_apls_flag := l_pgm_rec.elig_apls_flag;
406 --
407 l_elig_to_prte_rsn_row := ben_cobj_cache.g_pgmetpr_currow;
408 --
409 l_elig_inelig_cd := l_elig_to_prte_rsn_row.elig_inelig_cd;
410 --
411 --if the participantion override flag is on then participation override thru date
412 --should see the life event occurred date not the effective date
413
414 --bug 7264617: changed condition to >= in order to include lf_evt_ocrd_dt in ovridn_thru_dt
415 if l_pgm_rec.prtn_elig_ovrid_alwd_flag = 'Y' and
416 p_comp_rec.prtn_ovridn_flag = 'Y' and
417 nvl(p_comp_rec.prtn_ovridn_thru_dt,hr_api.g_eot) >= nvl(l_fonm_cvg_strt_dt,
418 nvl(p_lf_evt_ocrd_dt,p_effective_date)) and
419 (nvl(l_elig_to_prte_rsn_row.ignr_prtn_ovrid_flag,'N') = 'N' or
420 p_ler_id is null) then
421 --
422 -- Make person what they were before
423 -- e.g. eligible stays eligible, ineligible stays ineligible.
424 --
425 if p_comp_rec.elig_flag = 'Y' then
426 --
427 ben_manage_life_events.g_output_string :=
428 ben_manage_life_events.g_output_string ||
429 'Elg: Yes '||
430 'Rsn: Prtn Ovrid';
431 raise g_eligible;
432 --
433 elsif p_comp_rec.elig_flag = 'N' then
434 --
435 ben_manage_life_events.g_output_string :=
436 ben_manage_life_events.g_output_string ||
437 'Elg: No '||
438 'Rsn: Prtn Ovrid';
439 --
440 fnd_message.set_name('BEN','BEN_92223_PRTN_OVERRIDE');
441 raise g_not_eligible;
442 --
443 end if;
444 --
445 end if;
446 --
447 elsif p_pl_id is not null then
448 --
449 l_elig_apls_flag := l_pl_rec.elig_apls_flag;
450 --
451 l_elig_to_prte_rsn_row := ben_cobj_cache.g_pletpr_currow;
452 --
453 l_elig_inelig_cd := l_elig_to_prte_rsn_row.elig_inelig_cd;
454 --
455 --if the participantion override flag is on then participation override thru date
456 --should see the life event occurred date not the effective date
457 --
458 --bug 7264617: changed condition to >= in order to include lf_evt_ocrd_dt in ovridn_thru_dt
459 if l_pl_rec.prtn_elig_ovrid_alwd_flag = 'Y' and
460 p_comp_rec.prtn_ovridn_flag = 'Y' and
461 nvl(p_comp_rec.prtn_ovridn_thru_dt,hr_api.g_eot) >= nvl(l_fonm_cvg_strt_dt,
462 nvl(p_lf_evt_ocrd_dt,p_effective_date)) and
463 (nvl(l_elig_to_prte_rsn_row.ignr_prtn_ovrid_flag,'N') = 'N' or
464 p_ler_id is null) then
465 --
466 -- Make person what they were before
467 -- e.g. eligible stays eligible, ineligible stays ineligible.
468 --
469 if p_comp_rec.elig_flag = 'Y' then
470 --
471 ben_manage_life_events.g_output_string :=
472 ben_manage_life_events.g_output_string||
473 'Elg: Yes '||
474 'Rsn: Prtn Ovrid';
475 --
476 raise g_eligible;
477 --
478 elsif p_comp_rec.elig_flag = 'N' then
479 --
480 ben_manage_life_events.g_output_string :=
481 ben_manage_life_events.g_output_string||
482 'Elg: No '||
483 'Rsn: Prtn Ovrid';
484 --
485 fnd_message.set_name('BEN','BEN_92223_PRTN_OVERRIDE');
486 raise g_not_eligible;
487 --
488 end if;
489 --
490 end if;
491 --
492 elsif p_plip_id is not null then
493 --
494 l_elig_apls_flag := 'Y';
495 --
496 l_elig_to_prte_rsn_row := ben_cobj_cache.g_plipetpr_currow;
497 --
498 l_elig_inelig_cd := l_elig_to_prte_rsn_row.elig_inelig_cd;
499 --
500 --if the participantion override flag is on then participation override thru date
501 --should see the life event occurred date not the effective date
502 --
503 --bug 7264617: changed condition to >= in order to include lf_evt_ocrd_dt in ovridn_thru_dt
504 if l_plip_rec.prtn_elig_ovrid_alwd_flag = 'Y' and
505 p_comp_rec.prtn_ovridn_flag = 'Y' and
506 nvl(p_comp_rec.prtn_ovridn_thru_dt,hr_api.g_eot) >= nvl(l_fonm_cvg_strt_dt,
507 nvl(p_lf_evt_ocrd_dt,p_effective_date)) and
508 (nvl(l_elig_to_prte_rsn_row.ignr_prtn_ovrid_flag,'N') = 'N' or
509 p_ler_id is null) then
510 --
511 -- Make person what they were before
512 -- e.g. eligible stays eligible, ineligible stays ineligible.
513 --
514 if p_comp_rec.elig_flag = 'Y' then
515 --
516 raise g_eligible;
517 --
518 elsif p_comp_rec.elig_flag = 'N' then
519 --
520 ben_manage_life_events.g_output_string :=
521 ben_manage_life_events.g_output_string||
522 'Elg: No '||
523 'Rsn: Prtn Ovrid';
524 fnd_message.set_name('BEN','BEN_92223_PRTN_OVERRIDE');
525 raise g_not_eligible;
526 --
527 end if;
528 --
529 end if;
530 --
531 elsif p_ptip_id is not null then
532 --
533 l_elig_apls_flag := 'Y';
534 --
535 l_elig_to_prte_rsn_row := ben_cobj_cache.g_ptipetpr_currow;
536 --
537 l_elig_inelig_cd := l_elig_to_prte_rsn_row.elig_inelig_cd;
538 --
539 --if the participantion override flag is on then participation override thru date
540 --should see the life event occurred date not the effective date
541 --
542 --bug 7264617: changed condition to >= in order to include lf_evt_ocrd_dt in ovridn_thru_dt
543 if l_ptip_rec.prtn_elig_ovrid_alwd_flag = 'Y' and
544 p_comp_rec.prtn_ovridn_flag = 'Y' and
545 nvl(p_comp_rec.prtn_ovridn_thru_dt,hr_api.g_eot) >= nvl(l_fonm_cvg_strt_dt,
546 nvl(p_lf_evt_ocrd_dt,p_effective_date)) and
547 (nvl(l_elig_to_prte_rsn_row.ignr_prtn_ovrid_flag,'N') = 'N' or
548 p_ler_id is null) then
549 --
550 -- Make person what they were before
551 -- e.g. eligible stays eligible, ineligible stays ineligible.
552 --
553 if p_comp_rec.elig_flag = 'Y' then
554 --
555 raise g_eligible;
556 --
557 elsif p_comp_rec.elig_flag = 'N' then
558 --
559 ben_manage_life_events.g_output_string :=
560 ben_manage_life_events.g_output_string||
561 'Elg: No '||
562 'Rsn: Prtn Ovrid';
563 fnd_message.set_name('BEN','BEN_92223_PRTN_OVERRIDE');
564 raise g_not_eligible;
565 --
566 end if;
567 --
568 end if;
569 --
570 elsif p_oipl_id is not null then
571 --
572 l_elig_apls_flag := l_oipl_rec.elig_apls_flag;
573 --
574 l_elig_to_prte_rsn_row := ben_cobj_cache.g_oipletpr_currow;
575 --
576 l_elig_inelig_cd := l_elig_to_prte_rsn_row.elig_inelig_cd;
577 --
578 --if the participantion override flag is on then participation override thru date
579 --should see the life event occurred date not the effective date
580 --
581 --bug 7264617: changed condition to >= in order to include lf_evt_ocrd_dt in ovridn_thru_dt
582 if l_oipl_rec.prtn_elig_ovrid_alwd_flag = 'Y' and
583 p_comp_rec.prtn_ovridn_flag = 'Y' and
584 nvl(p_comp_rec.prtn_ovridn_thru_dt,hr_api.g_eot) >= nvl(l_fonm_cvg_strt_dt,
585 nvl(p_lf_evt_ocrd_dt,p_effective_date)) and
586 (nvl(l_elig_to_prte_rsn_row.ignr_prtn_ovrid_flag,'N') = 'N' or
587 p_ler_id is null) then
588 --
589 -- Make person what they were before
590 -- e.g. eligible stays eligible, ineligible stays ineligible.
591 --
592 if p_comp_rec.elig_flag = 'Y' then
593 --
594 ben_manage_life_events.g_output_string :=
595 ben_manage_life_events.g_output_string||
596 'Elg: Yes '||
597 'Rsn: Prtn Ovrid';
598 raise g_eligible;
599 --
600 elsif p_comp_rec.elig_flag = 'N' then
601 --
602 ben_manage_life_events.g_output_string :=
603 ben_manage_life_events.g_output_string||
604 'Elg: No '||
605 'Rsn: Prtn Ovrid';
606 fnd_message.set_name('BEN','BEN_92223_PRTN_OVERRIDE');
607 raise g_not_eligible;
608 --
609 end if;
610 --
611 end if;
612 --
613 end if;
614 --
615 -- Check if program, plan or option has an eligibility profile
616 if g_debug then
617 hr_utility.set_location(l_proc||' Before profile loop',22);
618 end if;
619 --
620 -- Check life event eligibility. If a life event is specified and
621 -- elig_inelig_code =
622 -- 1. ELIG - person is eligible, no need to check other profiles.
623 -- 2. INELIG - person is not eligible.
624 -- 3. ETHREI - If person is eligible, also check other profiles.
625 --
626 if (p_ler_id is not null
627 and l_elig_inelig_cd is not null) then
628 --
629 if l_elig_inelig_cd = 'ELIG' then
630 raise g_eligible;
631 elsif l_elig_inelig_cd = 'INELIG' then
632 g_inelg_rsn_cd := 'EVT';
633 fnd_message.set_name('BEN','BEN_92303_LIFE_EVENT_NOT_ELIG');
634 raise g_not_eligible;
635 end if;
636 --
637 end if;
638 --
639 -- Check for person's eligibility based upon contact/dependent data.
640 -- (e.g. eligible for dependent only coverage in an HMO if dependent
641 -- lives in service area and participant is full time).
642 -- Only check for plan, plip and oipl.
643 if g_debug then
644 hr_utility.set_location('p_pl_id '||p_pl_id,24);
645 hr_utility.set_location('p_oipl_id '||p_oipl_id,24);
646 hr_utility.set_location('p_pgm_id '||p_comp_obj_tree_row.par_pgm_id,24);
647 end if;
648 --BUG 4055771
649 ben_env_object.get(p_rec => l_env_rec);
650 benutils.get_batch_parameters(p_benefit_action_id => l_env_rec.benefit_action_id
651 ,p_rec => l_benmngle_parm_rec);
652 --
653 -- BUG 4055771 l_env.mode_cd Never got initialized
654 if g_debug then
655 hr_utility.set_location(l_proc||' Family mem stuff ',10);
656 end if;
657 --
658 if (p_pl_id is not null
659 or p_oipl_id is not null) and NVL(l_benmngle_parm_rec.mode_cd,'X') not in ('I','D') -- BUG 4055771
660 -- l_env.mode_cd <>'I' -- IREC
661 then
662 --
663 -- Task 130 : Get vrfy_fmly_mmbr_cd from levels pgm, ptip,
664 -- plip, pl, oipl. If it is available at higher levels
665 -- that will take precedence over lower levels.
666 -- If vrfy_fmly_mmbr_cd is defined at ler level for
667 -- a comp object that will take precedence.
668 if g_debug then
669 hr_utility.set_location('p_par_pgm_id '||p_comp_obj_tree_row.par_pgm_id,25);
670 hr_utility.set_location('p_par_ptip_id '||p_comp_obj_tree_row.par_ptip_id,25);
671 hr_utility.set_location('p_par_plip_id '||p_comp_obj_tree_row.par_plip_id,25);
672 hr_utility.set_location('p_par_pl_id '||p_comp_obj_tree_row.par_pl_id,25);
673 hr_utility.set_location('l_pl_name '||l_pl_rec.name,26);
674 end if;
675 --
676 if p_comp_obj_tree_row.par_pgm_id is not null
677 then
678 --
679 l_par_pgm_elig_rec := ben_cobj_cache.g_pgmetpr_currow;
680 --
681 if l_par_pgm_elig_rec.vrfy_fmly_mmbr_cd is not null then
682 l_vrfy_fmly_mmbr_cd := l_par_pgm_elig_rec.vrfy_fmly_mmbr_cd;
683 l_vrfy_fmly_mmbr_rl := l_par_pgm_elig_rec.vrfy_fmly_mmbr_rl;
684 else
685 l_par_pgm_rec := ben_cobj_cache.g_pgm_currow;
686 l_vrfy_fmly_mmbr_cd := l_par_pgm_rec.vrfy_fmly_mmbr_cd;
687 l_vrfy_fmly_mmbr_rl := l_par_pgm_rec.vrfy_fmly_mmbr_rl;
688 end if;
689 --
690 end if;
691 --
692 if l_vrfy_fmly_mmbr_cd is null and
693 p_comp_obj_tree_row.par_ptip_id is not null
694 then
695 --
696 l_par_ptip_elig_rec := ben_cobj_cache.g_ptipetpr_currow;
697 --
698 if l_par_ptip_elig_rec.vrfy_fmly_mmbr_cd is not null then
699 l_vrfy_fmly_mmbr_cd := l_par_ptip_elig_rec.vrfy_fmly_mmbr_cd;
700 l_vrfy_fmly_mmbr_rl := l_par_ptip_elig_rec.vrfy_fmly_mmbr_rl;
701 else
702 l_par_ptip_rec := ben_cobj_cache.g_ptip_currow;
703 l_vrfy_fmly_mmbr_cd := l_par_ptip_rec.vrfy_fmly_mmbr_cd;
704 l_vrfy_fmly_mmbr_rl := l_par_ptip_rec.vrfy_fmly_mmbr_rl;
705 end if;
706 --
707 if g_debug then
708 hr_utility.set_location('l_ptip level '||l_vrfy_fmly_mmbr_cd,26);
709 end if;
710 end if;
711 --
712 if l_vrfy_fmly_mmbr_cd is null and
713 p_comp_obj_tree_row.par_plip_id is not null
714 then
715 --
716 l_par_plip_elig_rec := ben_cobj_cache.g_plipetpr_currow;
717 --
718 if l_par_plip_elig_rec.vrfy_fmly_mmbr_cd is not null then
719 l_vrfy_fmly_mmbr_cd := l_par_plip_elig_rec.vrfy_fmly_mmbr_cd;
720 l_vrfy_fmly_mmbr_rl := l_par_plip_elig_rec.vrfy_fmly_mmbr_rl;
721 else
722 --
723 l_par_plip_rec := ben_cobj_cache.g_plip_currow;
724 --
725 l_vrfy_fmly_mmbr_cd := l_par_plip_rec.vrfy_fmly_mmbr_cd;
726 l_vrfy_fmly_mmbr_rl := l_par_plip_rec.vrfy_fmly_mmbr_rl;
727 end if;
728 -- hr_utility.set_location('l_plip level '||l_vrfy_fmly_mmbr_cd,26);
729 --
730 end if;
731 --
732 if l_vrfy_fmly_mmbr_cd is null and
733 p_comp_obj_tree_row.par_pl_id is not null
734 then
735 --
736 -- BUG 3168805 l_par_pl_elig_rec := ben_cobj_cache.g_pgmetpr_currow;
737 -- looks like typo, we should be looking at plan rec not pgm rec
738 l_par_pl_elig_rec := ben_cobj_cache.g_pletpr_currow;
739 --
740 if l_par_pl_elig_rec.vrfy_fmly_mmbr_cd is not null then
741 l_vrfy_fmly_mmbr_cd := l_par_pl_elig_rec.vrfy_fmly_mmbr_cd;
742 l_vrfy_fmly_mmbr_rl := l_par_pl_elig_rec.vrfy_fmly_mmbr_rl;
743 else
744 --
745 l_par_pl_rec := ben_cobj_cache.g_pl_currow;
746 --
747 l_vrfy_fmly_mmbr_cd := l_par_pl_rec.vrfy_fmly_mmbr_cd;
748 l_vrfy_fmly_mmbr_rl := l_par_pl_rec.vrfy_fmly_mmbr_rl;
749 end if;
750 --
751 if g_debug then
752 hr_utility.set_location('l_pl level '||l_vrfy_fmly_mmbr_cd,26);
753 end if;
754 end if;
755 --
756 if p_pl_id is not null then
757 --
758 -- Get the person covered code from the plan cache.
759 --
760 l_per_cvrd_cd := l_pl_rec.per_cvrd_cd;
761 --
762 l_pl_id := p_pl_id;
763 l_dpnt_pl_id := p_pl_id;
764 --
765 elsif p_oipl_id is not null then
766 if g_debug then
767 hr_utility.set_location(l_proc||' oipl NN ',28);
768 hr_utility.set_location(' vrfy fmly mmbr cd from ben_elig_to_prte_rsn_f '
769 || l_elig_to_prte_rsn_row.vrfy_fmly_mmbr_cd ,29);
770 end if;
771 --
772 if l_vrfy_fmly_mmbr_cd is null then
773 --
774 l_vrfy_fmly_mmbr_cd := l_oipl_rec.vrfy_fmly_mmbr_cd;
775 l_vrfy_fmly_mmbr_rl := l_oipl_rec.vrfy_fmly_mmbr_rl;
776 --
777 if l_elig_to_prte_rsn_row.vrfy_fmly_mmbr_cd is not null then
778 l_vrfy_fmly_mmbr_cd := l_elig_to_prte_rsn_row.vrfy_fmly_mmbr_cd;
779 l_vrfy_fmly_mmbr_rl := l_elig_to_prte_rsn_row.vrfy_fmly_mmbr_rl;
780 end if;
781
782 end if;
783
784 l_pl_id := l_oipl_rec.pl_id;
785 --
786 -- Check dependent eligibility.
787 --
788 if l_oipl_rec.per_cvrd_cd is null then
789 ben_comp_object.get_object(p_pl_id => l_oipl_rec.pl_id,
790 p_rec => l_pl2_rec);
791 l_per_cvrd_cd := l_pl2_rec.per_cvrd_cd;
792 else
793 l_per_cvrd_cd := l_oipl_rec.per_cvrd_cd;
794 end if;
795 --
796 l_dpnt_pl_id := l_oipl_rec.pl_id;
797 --
798 end if;
799 --
800 -- Check verify family member code. CDR - Check
801 -- Designation requirements.
802 --
803 if g_debug then
804 hr_utility.set_location(l_proc||' Ver FM Code ',10);
805 hr_utility.set_location(' l_vrfy_fmly_mmbr_cd ' || l_vrfy_fmly_mmbr_cd , 25 );
806 end if;
807 --
808 if l_vrfy_fmly_mmbr_cd = 'CDR' then
809 --
810 -- Get contacts.
811 --
812 ben_determine_eligibility3.check_dsgn_rqmts
813 (p_oipl_id => p_oipl_id
814 ,p_pl_id => l_pl_id
815 ,p_opt_id => l_oipl_rec.opt_id
816 ,p_person_id => p_person_id
817 ,p_business_group_id => p_business_group_id
818 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
819 ,p_effective_date => p_effective_date
820 ,p_vrfy_fmm => true
821 ,p_dpnt_elig_flag => l_dpnt_elig_flag);
822 --
823 if l_dpnt_elig_flag = 'N' then
824 --
825 g_inelg_rsn_cd := 'FMM';
826 fnd_message.set_name('BEN','BEN_92321_FMM_INELIG');
827 raise g_not_eligible;
828 --
829 end if;
830 --
831 elsif l_vrfy_fmly_mmbr_rl is not null then
832 --
833 if l_ass_rec.location_id is not null then
834 --
835 ben_location_object.get_object
836 (p_location_id => l_ass_rec.location_id
837 ,p_rec => l_loc_rec);
838 --
839 end if;
840 --
841 /*
842 if l_loc_rec.region_2 is not null then
843 --
844 l_jurisdiction_code := pay_mag_utils.lookup_jurisdiction_code
845 (l_loc_rec.region_2);
846 --
847 end if;
848 */
849 --
850 if g_debug then
851 hr_utility.set_location(l_proc||' formula ',30);
852 end if;
853 --
854 l_outputs := benutils.formula
855 (p_formula_id => l_vrfy_fmly_mmbr_rl,
856 p_effective_date => l_effective_date,
857 p_business_group_id => p_business_group_id,
858 p_assignment_id => l_ass_rec.assignment_id,
859 p_organization_id => l_ass_rec.organization_id,
860 p_pgm_id => p_comp_obj_tree_row.par_pgm_id,
861 p_pl_id => l_pl_id,
862 p_pl_typ_id => l_pl_rec.pl_typ_id,
863 p_opt_id => l_oipl_rec.opt_id,
864 p_ler_id => p_ler_id,
865 p_param1 => 'PERSON_ID',
866 p_param1_value => to_char(p_person_id),
867 p_param2 => 'BEN_IV_RT_STRT_DT',
868 p_param2_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt),
869 p_param3 => 'BEN_IV_CVG_STRT_DT',
870 p_param3_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt),
871 p_jurisdiction_code => l_jurisdiction_code);
872 --
873 if l_outputs(l_outputs.first).value = 'N' then
874 g_inelg_rsn_cd := 'FMM';
875 fnd_message.set_name('BEN','BEN_92321_FMM_INELIG');
876 raise g_not_eligible;
877 end if;
878 --
879 end if;
880 --
881 if g_debug then
882 hr_utility.set_location('l_per_cvrd_cd '||l_per_cvrd_cd,32);
883 hr_utility.set_location(l_proc||' PCVRDCD',32);
884 end if;
885 --
886 if l_per_cvrd_cd in ('PRTTDPNT', 'DPNTELIG') or
887 l_vrfy_fmly_mmbr_cd = 'CDR' then
888 --
889 -- Get per_in_ler_id
890 --
891 /*
892 l_per_in_ler_id := benutils.get_per_in_ler_id
893 (p_person_id => p_person_id
894 ,p_business_group_id => p_business_group_id
895 ,p_effective_date => p_effective_date
896 );
897 */
898 -- added for unrestricted enhancement
899 l_per_in_ler_id := p_pil_row.per_in_ler_id;
900 --
901 -- Clear PL/SQL table.
902 --
903 g_elig_dpnt_rec.delete;
904 --
905 -- Get all personal contacts for the person.
906 --
907 l_ctr_count := 0;
908 --
909 if g_debug then
910 hr_utility.set_location(l_proc||' Contact loop ',10);
911 end if;
912 --
913 for l_ctr_rec in c_get_contacts
914 loop
915 --
916 -- Call dependent eligibility
917 --
918 if g_debug then
919 hr_utility.set_location('BEDEP_MN :'||l_proc,34);
920 end if;
921 --
922 ben_evaluate_dpnt_elg_profiles.main
923 (p_contact_relationship_id => l_ctr_rec.contact_relationship_id
924 ,p_contact_person_id => l_ctr_rec.contact_person_id
925 ,p_pgm_id => p_comp_obj_tree_row.par_pgm_id
926 ,p_pl_id => l_dpnt_pl_id
927 ,p_ptip_id => p_ptip_id
928 ,p_oipl_id => p_oipl_id
929 ,p_business_group_id => p_business_group_id
930 ,p_effective_date => p_effective_date
931 ,p_per_in_ler_id => l_per_in_ler_id
932 ,p_lf_evt_ocrd_dt => l_effective_date
933 ,p_dependent_eligible_flag => l_dependent_elig_flag
934 ,p_dpnt_inelig_rsn_cd => l_dpnt_inelig_rsn_cd
935 );
936 if g_debug then
937 hr_utility.set_location('Dn BEDEP_MN :'||l_proc,36);
938 end if;
939 --
940 -- Load dependent data into a plsql table to check
941 -- for designation requirements.
942 --
943 if l_dependent_elig_flag = 'Y' then
944 -- at least one dpnt is elig, set flag.
945 l_dpnt_elig_flag := 'Y';
946 g_elig_dpnt_rec(l_ctr_count) := l_ctr_rec;
947 l_ctr_count := l_ctr_count + 1;
948 end if;
949 --
950 end loop;
951 --
952 -- If person have contact(s) that are eligible dependents,
953 -- check if the eligible dependents meets the designation
954 -- requirements.
955 --
956 ben_determine_eligibility3.check_dsgn_rqmts
957 (p_oipl_id => p_oipl_id
958 ,p_pl_id => l_pl_id
959 ,p_opt_id => l_oipl_rec.opt_id
960 ,p_person_id => p_person_id
961 ,p_business_group_id => p_business_group_id
962 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
963 ,p_effective_date => p_effective_date
964 ,p_vrfy_fmm => false
965 ,p_dpnt_elig_flag => l_dpnt_elig_flag);
966 --
967 if l_dpnt_elig_flag = 'N' then
968 if g_debug then
969 hr_utility.set_location('BEN_92255_DPNT_NOT_ELIG: from check_dsgn_rqmts',38);
970 end if;
971 g_inelg_rsn_cd := 'DLG';
972 fnd_message.set_name('BEN','BEN_92255_DPNT_NOT_ELIG');
973 raise g_not_eligible;
974 end if;
975 --
976 -- By pass all participant processing if person covered code is
977 -- 'DPNTELIG'.
978 --
979 if l_per_cvrd_cd = 'DPNTELIG' then
980 raise g_eligible;
981 end if;
982 --
983 end if;
984 --
985 end if;
986 --
987 -- If the comp objects eligibility applies flag = 'N' then we don't need
988 -- to bother checking eligibility profiles. That means that someone is
989 -- instantly eligible, scary thing is though that the form needs to enforce
990 -- this and we have data out there which doesn't enforce use of the flag.
991 --
992 if l_elig_apls_flag = 'Y' then
993 --
994 -- Get eligibility profile details for the business group, plan or
995 -- program or option combination as of the process date
996 if g_debug then
997 hr_utility.set_location('PGM_ID '||p_pgm_id,44);
998 hr_utility.set_location('PL_ID '||p_pl_id,44);
999 hr_utility.set_location('OIPL_ID '||p_oipl_id,44);
1000 hr_utility.set_location('PLIP_ID '||p_plip_id,44);
1001 hr_utility.set_location('PTIP_ID '||p_ptip_id,44);
1002 hr_utility.set_location(l_proc||' Before Cache call',44);
1003 hr_utility.set_location('p_pil_row.per_in_ler_id '||p_pil_row.per_in_ler_id,44);
1004 end if;
1005 --
1006 if not ben_evaluate_elig_profiles.eligible
1007 (p_person_id => p_person_id
1008 ,p_business_group_id => p_business_group_id
1009 ,p_effective_date => p_effective_date
1010 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
1011 ,p_dpr_rec => p_comp_rec
1012 ,p_per_in_ler_id => p_pil_row.per_in_ler_id --l_per_in_ler_id -- bug 7119125
1013 ,p_ler_id => p_ler_id
1014 ,p_pgm_id => p_pgm_id
1015 ,p_ptip_id => p_ptip_id
1016 ,p_plip_id => p_plip_id
1017 ,p_pl_id => p_pl_id
1018 ,p_oipl_id => p_oipl_id
1019 ,p_oiplip_id => null
1020 ,p_pl_typ_id => l_pl_rec.pl_typ_id
1021 ,p_opt_id => l_oipl_rec.opt_id
1022 ,p_par_pgm_id => p_comp_obj_tree_row.par_pgm_id
1023 ,p_par_plip_id => p_comp_obj_tree_row.par_plip_id
1024 ,p_par_pl_id => p_comp_obj_tree_row.par_pl_id
1025 ,p_par_opt_id => p_comp_obj_tree_row.par_opt_id
1026 ,p_asg_status => l_terminated
1027 ,p_score_tab => l_score_tab
1028 ) then
1029 raise g_not_eligible;
1030 end if;
1031 --
1032 -- determine further eligibility based upon the rules associated
1033 -- with participation eligibility
1034 --
1035 if g_debug then
1036 hr_utility.set_location('before rules: '||l_proc,72);
1037 end if;
1038 --
1039 ben_elig_rl_cache.get_elig_rl_cache
1040 (p_pgm_id => p_pgm_id
1041 ,p_pl_id => p_pl_id
1042 ,p_oipl_id => p_oipl_id
1043 ,p_plip_id => p_plip_id
1044 ,p_ptip_id => p_ptip_id
1045 ,p_effective_date => p_effective_date
1046 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
1047 ,p_business_group_id => p_business_group_id
1048 ,p_inst_set => l_inst_set
1049 ,p_inst_count => l_elig_rl_cnt
1050 );
1051 if g_debug then
1052 hr_utility.set_location(l_proc||' done rules: ',74);
1053 hr_utility.set_location('Number of rules : ' || l_inst_set.count, 74);
1054 end if;
1055 --
1056 if l_elig_rl_cnt > 0 then
1057 --
1058 for i in l_inst_set.first .. l_inst_set.last loop
1059 --
1060 l_ok_so_far := 'N';
1061 --
1062 if l_assignment_id is null then
1063 --
1064 -- The person has no assingments and the rule requires assignment id
1065 -- If the rule is mandatory, the person is not eligible.
1066 -- If the rule is optional, then there is no need to check.
1067 --
1068 if l_inst_set(i).mndtry_flag = 'Y'
1069 then
1070 --
1071 -- Bug : 5059 : If the person have no assignment id, and formula
1072 -- uses data base items based on assignment id, then formula raises
1073 -- error like : A SQL SELECT statement, obtained from the application
1074 -- dictionary returned no rows. If assignement id is null and formula
1075 -- uses any DBI's which depend on it, make the person ineligible.
1076 --
1077 l_ff_use_asg_id_flag := 'N';
1078 open c_ff_use_asg(l_inst_set(i).formula_id);
1079 fetch c_ff_use_asg into l_ff_use_asg_id_flag;
1080 close c_ff_use_asg;
1081 --
1082 if l_ff_use_asg_id_flag = 'Y' then
1083 --
1084 raise g_not_eligible;
1085 --
1086 end if;
1087 --
1088 else
1089 --
1090 raise g_eligible;
1091 --
1092 end if;
1093 --
1094 end if;
1095 --
1096 if l_ass_rec.location_id is not null then
1097 --
1098 ben_location_object.get_object
1099 (p_location_id => l_ass_rec.location_id
1100 ,p_rec => l_loc_rec);
1101 --
1102 end if;
1103 --
1104 l_ctp_rec := NULL;
1105 l_pln_rec := NULL;
1106 --
1107 if (p_ptip_id IS NOT NULL) THEN
1108 -- 5482868 Find pl_typ_id for PTIP records
1109 if g_debug then
1110 hr_utility.set_location('Fetch pl_typ_id from cache p_ptip_id ' || p_ptip_id, 75);
1111 end if;
1112 --
1113 BEN_COMP_OBJECT.get_object(p_ptip_id => p_ptip_id, p_rec => l_ctp_rec);
1114 elsif (p_plip_id IS NOT NULL)
1115 and (p_comp_obj_tree_row.par_pl_id is not null) then
1116 -- 5482868 Find pl_typ_id for PLIP records
1117 BEN_COMP_OBJECT.get_object(p_pl_id => p_comp_obj_tree_row.par_pl_id, p_rec => l_pln_rec);
1118 --
1119 elsif (l_oipl_rec.pl_id IS NOT NULL) then
1120 -- 5482868 Find pl_typ_id for PLIP records
1121 BEN_COMP_OBJECT.get_object(p_pl_id => l_oipl_rec.pl_id, p_rec => l_pln_rec);
1122 --
1123 end if;
1124 --
1125 --
1126 l_outputs := benutils.formula
1127 (p_formula_id => l_inst_set(i).formula_id,
1128 p_effective_date => nvl(p_lf_evt_ocrd_dt,p_effective_date),
1129 p_business_group_id => p_business_group_id,
1130 p_assignment_id => l_ass_rec.assignment_id,
1131 p_organization_id => l_ass_rec.organization_id,
1132 p_pgm_id => NVL(p_pgm_id, p_comp_obj_tree_row.par_pgm_id), -- 5482868 : pass parent_id
1133 p_pl_id => NVL(p_pl_id, p_comp_obj_tree_row.par_pl_id), -- 5482868 : pass parent_id
1134 p_pl_typ_id => NVL(l_pl_rec.pl_typ_id, NVL(l_ctp_rec.pl_typ_id, l_pln_rec.pl_typ_id)),
1135 p_opt_id => l_oipl_rec.opt_id,
1136 p_ler_id => p_ler_id,
1137 p_param1 => 'BEN_IV_RT_STRT_DT',
1138 p_param1_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_rt_strt_dt),
1139 p_param2 => 'BEN_IV_CVG_STRT_DT',
1140 p_param2_value => fnd_date.date_to_canonical(ben_manage_life_events.g_fonm_cvg_strt_dt),
1141 p_param3 => 'BEN_IV_PERSON_ID', -- Bug 5331889
1142 p_param3_value => to_char(p_person_id),
1143 p_jurisdiction_code => l_jurisdiction_code);
1144 --
1145 if l_outputs(l_outputs.first).value = 'Y' then
1146 --
1147 l_ok_so_far := 'Y';
1148 --
1149 if l_inst_set(i).mndtry_flag = 'N' then
1150 --
1151 -- If we are in a optional rule, that means we passed all
1152 -- the mandatory rules.
1153 --
1154 raise g_eligible;
1155 --
1156 end if;
1157 --
1158 elsif l_outputs(l_outputs.first).value = 'N' then
1159 --
1160 ben_manage_life_events.g_output_string :=
1161 ben_manage_life_events.g_output_string||
1162 'Elg: No '||
1163 'Rsn: Rule No Pass';
1164 --
1165 l_ok_so_far := 'N';
1166 --
1167 if l_inst_set(i).mndtry_flag = 'Y' then
1168 --
1169 -- you must pass all mandatory rules.
1170 --
1171 raise g_not_eligible;
1172 --
1173 end if;
1174 --
1175 else
1176 --
1177 fnd_message.set_name('BEN','BEN_91329_FORMULA_RETURN');
1178 fnd_message.set_token('RL','formula_id :'||l_inst_set(i).formula_id);
1179 fnd_message.set_token('PROC',l_proc);
1180 raise ben_manage_life_events.g_record_error;
1181 --
1182 end if;
1183 --
1184 end loop; -- elig rules
1185 --
1186 -- If we are here, either:
1187 -- there were no rules or
1188 -- all mandatory rules passed
1189 --
1190 -- there were no optional rules
1191 -- or there are optional ones, mt_one flag is off and
1192 -- ALL optional rules failed.
1193 end if; -- inst count
1194 --
1195 if l_ok_so_far = 'N' then
1196 --
1197 raise g_not_eligible;
1198 --
1199 else
1200 --
1201 raise g_eligible;
1202 --
1203 end if;
1204 --
1205 else
1206 --
1207 raise g_eligible;
1208 --
1209 end if; -- elig_apls_flag
1210 if g_debug then
1211 hr_utility.set_location('Leaving: '||l_proc,99);
1212 end if;
1213 --
1214 exception
1215 --
1216 when g_eligible then
1217 --
1218 -- benutils.write(p_text => 'BENDETEL g_eligible ');
1219 if g_debug then
1220 hr_utility.set_location(l_proc||' Exc g_eligible ',800);
1221 end if;
1222 --
1223 -- Set out parameters
1224 --
1225 p_eligible := TRUE;
1226 --
1227 l_elig_flag := TRUE;
1228 --
1229 g_rec.person_id := p_person_id;
1230 g_rec.pgm_id := p_pgm_id;
1231 g_rec.pl_id := p_pl_id;
1232 g_rec.oipl_id := p_oipl_id;
1233 g_rec.elig_flag := 'Y';
1234 g_rec.inelig_text := null;
1235 g_rec.business_group_id := p_business_group_id;
1236 g_rec.effective_date := p_effective_date;
1237 --
1238 if g_debug then
1239 hr_utility.set_location(l_proc||' BU_WRITE g_eligible ',812);
1240 end if;
1241 --
1242 benutils.write(p_rec => g_rec);
1243 benutils.write(p_text => ben_manage_life_events.g_output_string);
1244 ben_manage_life_events.g_output_string := null;
1245 --
1246 if g_debug then
1247 hr_utility.set_location(l_proc||' BDE_CPE g_eligible ',814);
1248 hr_utility.set_location('AGE VAL in CPE'||p_comp_rec.age_val,814);
1249 end if;
1250 --
1251 --
1252 ben_determine_eligibility2.check_prev_elig
1253 (p_comp_obj_tree_row => p_comp_obj_tree_row
1254 --
1255 ,p_per_row => p_per_row
1256 ,p_empasg_row => p_empasg_row
1257 ,p_benasg_row => p_benasg_row
1258 ,p_pil_row => p_pil_row
1259 --
1260 ,p_elig_flag => l_elig_flag
1261 ,p_person_id => p_person_id
1262 ,p_business_group_id => p_business_group_id
1263 ,p_effective_date => p_effective_date
1264 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
1265 ,p_pl_id => p_pl_id
1266 ,p_pgm_id => p_pgm_id
1267 ,p_oipl_id => p_oipl_id
1268 ,p_plip_id => p_plip_id
1269 ,p_ptip_id => p_ptip_id
1270 ,p_ler_id => p_ler_id
1271 ,p_comp_rec => p_comp_rec
1272 ,p_oiplip_rec => p_oiplip_rec
1273 ,p_inelg_rsn_cd => null
1274 --
1275 ,p_newly_elig => p_newly_elig
1276 ,p_newly_inelig => p_newly_inelig
1277 ,p_first_elig => p_first_elig
1278 ,p_first_inelig => p_first_inelig
1279 ,p_still_elig => p_still_elig
1280 ,p_still_inelig => p_still_inelig
1281 ,p_score_tab => l_score_tab
1282 );
1283 --
1284 --
1285 if g_debug then
1286 hr_utility.set_location(l_proc||' Dn Exc g_eligible ',816);
1287 end if;
1288 --
1289 when g_not_eligible then
1290 --
1291 -- benutils.write(p_text => 'BENDETEL g_not_eligible ');
1292 if g_debug then
1293 hr_utility.set_location(l_proc||' Exc g_not_eligible ',818);
1294 end if;
1295 --
1296 -- Set out parameters
1297 --
1298 p_not_eligible := TRUE;
1299 --
1300 l_elig_flag := FALSE;
1301 --
1302 g_rec.person_id := p_person_id;
1303 g_rec.pgm_id := p_pgm_id;
1304 g_rec.pl_id := p_pl_id;
1305 g_rec.oipl_id := p_oipl_id;
1306 g_rec.elig_flag := 'N';
1307 g_rec.inelig_text := fnd_message.get;
1308 g_rec.business_group_id := p_business_group_id;
1309 g_rec.effective_date := p_effective_date;
1310 --
1311 if g_debug then
1312 hr_utility.set_location(l_proc||' Dn BU_WRI g_not_eligible ',822);
1313 end if;
1314 --
1315 benutils.write(p_rec => g_rec);
1316 benutils.write(p_text => ben_manage_life_events.g_output_string);
1317 ben_manage_life_events.g_output_string := null;
1318 --
1319 if g_debug then
1320 hr_utility.set_location(l_proc||' Dn BDE_CPE g_not_eligible ',824);
1321 hr_utility.set_location('g_inelg_rsn_cd ' || g_inelg_rsn_cd ,824);
1322 hr_utility.set_location('ben_evaluate_elig_profiles.g_inelg_rsn_cd ' || ben_evaluate_elig_profiles.g_inelg_rsn_cd,824);
1323 end if;
1324 --
1325 ben_determine_eligibility2.check_prev_elig
1326 (p_comp_obj_tree_row => p_comp_obj_tree_row
1327 --
1328 ,p_per_row => p_per_row
1329 ,p_empasg_row => p_empasg_row
1330 ,p_benasg_row => p_benasg_row
1331 ,p_pil_row => p_pil_row
1332 --
1333 ,p_elig_flag => l_elig_flag
1334 ,p_person_id => p_person_id
1335 ,p_business_group_id => p_business_group_id
1336 ,p_effective_date => p_effective_date
1337 ,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
1338 ,p_pl_id => p_pl_id
1339 ,p_pgm_id => p_pgm_id
1340 ,p_oipl_id => p_oipl_id
1341 ,p_plip_id => p_plip_id
1342 ,p_ptip_id => p_ptip_id
1343 ,p_ler_id => p_ler_id
1344 ,p_comp_rec => p_comp_rec
1345 ,p_oiplip_rec => p_oiplip_rec
1346 ,p_inelg_rsn_cd => nvl(ben_evaluate_elig_profiles.g_inelg_rsn_cd, g_inelg_rsn_cd)
1347 --
1348 ,p_newly_elig => p_newly_elig
1349 ,p_newly_inelig => p_newly_inelig
1350 ,p_first_elig => p_first_elig
1351 ,p_first_inelig => p_first_inelig
1352 ,p_still_elig => p_still_elig
1353 ,p_still_inelig => p_still_inelig
1354 ,p_score_tab => l_score_tab
1355 );
1356 --
1357 --
1358 if g_debug then
1359 hr_utility.set_location(l_proc||' Dn Exc g_not_eligible ',826);
1360 end if;
1361 --
1362 end determine_elig_prfls;
1363 --
1364 end ben_determine_eligibility;