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