1 package body ben_elpro_check_eligibility as
2 /* $Header: bendtlep.pkb 120.7.12020000.4 2013/03/11 17:12:55 stee ship $ */
3 --
4 g_package varchar2(50) := 'ben_elpro_check_eligibility.';
5 g_rec benutils.g_batch_elig_rec;
6 --
7 procedure check_elig_othr_ptip_prte
8 (p_eligy_prfl_id in number
9 ,p_business_group_id in number
10 ,p_effective_date in date
11 ,p_lf_evt_ocrd_dt in date
12 ,p_person_id in number
13 --
14 ,p_per_in_ler_id in number default null
15 )
16
17 is
18 --
19 l_proc varchar2(100):='check_elig_othr_ptip_prte';
20 --
21 l_inst_dets ben_elp_cache.g_cache_elpeoy_instor;
22 l_inst_count number;
23 l_insttorrw_num binary_integer;
24 l_ok boolean := false;
25 l_rows_found boolean := false;
26 l_dummy varchar2(1);
27 l_pl_rec ben_comp_object.g_cache_pl_rec_table;
28 --
29 l_cur_found boolean := false;
30 -- fonm
31 l_effective_date date ;
32 --
33 -- bug 7540693: removed effective_date condition from pilc2
34 cursor pilc2
35 (c_per_in_ler_id number
36 ,c_ptip_id number
37 )
38 is
39 select /*+ bendtlep.check_elig_othr_ptip_prte.pilc1 */
40 null
41 from ben_elig_per_f epo
42 where epo.ptip_id = c_ptip_id
43 and epo.pl_id is null
44 and epo.per_in_ler_id = c_per_in_ler_id
45 and epo.elig_flag = 'Y';
46 --
47
48 cursor pilc1
49 (c_effective_date date
50 ,c_per_in_ler_id number
51 ,c_ptip_id number
52 ,c_only_pls_subj_cobra_flag varchar2
53 )
54 is
55 select /*+ bendtlep.check_elig_othr_ptip_prte.pilc1 */
56 null
57 from ben_pl_f pln,
58 ben_plip_f cpp,
59 ben_ptip_f ctp,
60 ben_pl_regn_f prg,
61 ben_regn_f reg,
62 ben_elig_per_f epo
63 where pln.pl_id = cpp.pl_id
64 and c_effective_date
65 between pln.effective_start_date
66 and pln.effective_end_date
67 and c_effective_date
68 between cpp.effective_start_date
69 and cpp.effective_end_date
70 and cpp.pgm_id = ctp.pgm_id
71 and pln.pl_typ_id = ctp.pl_typ_id
72 and ctp.ptip_id = c_ptip_id
73 and c_effective_date
74 between ctp.effective_start_date
75 and ctp.effective_end_date
76 and prg.pl_id = pln.pl_id
77 and c_effective_date
78 between prg.effective_start_date
79 and prg.effective_end_date
80 and reg.regn_id = prg.regn_id
81 and c_effective_date
82 between reg.effective_start_date
83 and reg.effective_end_date
84 and epo.per_in_ler_id = c_per_in_ler_id
85 and epo.pgm_id = ctp.pgm_id
86 and epo.pl_id = pln.pl_id
87 /*and c_effective_date
88 between epo.effective_start_date
89 and epo.effective_end_date */ /* bug 7540693 */
90 and epo.elig_flag = 'Y'
91 and reg.sttry_citn_name = decode(c_only_pls_subj_cobra_flag,
92 'Y',
93 'COBRA',
94 reg.sttry_citn_name);
95 cursor c1
96 (c_business_group_id in number
97 ,c_effective_date in date
98 ,c_person_id in number
99 ,c_ptip_id in number
100 ,c_only_pls_subj_cobra_flag in varchar2
101 )
102 is
103 select /*+ first_rows bendtlep.check_elig_othr_ptip_prte.c1 */ --Bug 5200242
104 null
105 from ben_pl_f pln,
106 ben_plip_f cpp,
107 ben_ptip_f ctp,
108 ben_pl_regn_f prg,
109 ben_regn_f reg,
110 ben_elig_per_f epo,
111 ben_per_in_ler pil
112 where pln.pl_id = cpp.pl_id
113 and pln.business_group_id = c_business_group_id
114 and c_effective_date
115 between pln.effective_start_date
116 and pln.effective_end_date
117 and cpp.business_group_id = pln.business_group_id
118 and c_effective_date
119 between cpp.effective_start_date
120 and cpp.effective_end_date
121 and cpp.pgm_id = ctp.pgm_id
122 and pln.pl_typ_id = ctp.pl_typ_id
123 and ctp.ptip_id = c_ptip_id
124 and ctp.business_group_id = pln.business_group_id
125 and c_effective_date
126 between ctp.effective_start_date
127 and ctp.effective_end_date
128 and prg.pl_id = pln.pl_id
129 and prg.business_group_id = pln.business_group_id
130 and c_effective_date
131 between prg.effective_start_date
132 and prg.effective_end_date
133 and reg.regn_id = prg.regn_id
134 and reg.business_group_id = prg.business_group_id
135 and c_effective_date
136 between reg.effective_start_date
137 and reg.effective_end_date
138 and epo.person_id = c_person_id
139 and epo.pgm_id = ctp.pgm_id
140 and epo.pl_id = pln.pl_id
141 and epo.business_group_id = c_business_group_id
142 and c_effective_date
143 between epo.effective_start_date
144 and epo.effective_end_date
145 and epo.elig_flag = 'Y'
146 and reg.sttry_citn_name = decode(c_only_pls_subj_cobra_flag,
147 'Y',
148 'COBRA',
149 reg.sttry_citn_name)
150 and pil.per_in_ler_id(+)=epo.per_in_ler_id
151 --and pil.business_group_id(+)=epo.business_group_id
152 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
153 or pil.per_in_ler_stat_cd is null -- outer join condition
154 )
155 ;
156 cursor c2
157 (c_business_group_id in number
158 ,c_effective_date in date
159 ,c_person_id in number
160 ,c_ptip_id in number
161 )
162 is
163 select /*+ bendtlep.check_elig_othr_ptip_prte.c1 */
164 null
165 from ben_elig_per_f epo,
166 ben_per_in_ler pil
167 where epo.person_id = c_person_id
168 and epo.pl_id is null
169 and epo.ptip_id = c_ptip_id
170 and epo.business_group_id = c_business_group_id
171 and c_effective_date
172 between epo.effective_start_date
173 and epo.effective_end_date
174 and epo.elig_flag = 'Y'
175 and pil.per_in_ler_id(+)=epo.per_in_ler_id
176 --and pil.business_group_id(+)=epo.business_group_id
177 and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
178 or pil.per_in_ler_stat_cd is null -- outer join condition
179 )
180 ;
181 --
182 begin
183 --
184 hr_utility.set_location('Entering: '||l_proc, 10);
185 hr_utility.set_location('LE dt : '||p_lf_evt_ocrd_dt, 10);
186 hr_utility.set_location('Ef dt : '||p_effective_date, 10);
187 hr_utility.set_location('p_per_in_ler_id : '||p_per_in_ler_id, 10);
188 --
189 -- Getting eligibility profile compensation level by eligibility profile
190 -- fonm
191 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date) ;
192 if ben_manage_life_events.fonm = 'Y'
193 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
194 --
195 l_effective_date := nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,l_effective_date);
196 --
197 END IF;
198
199 hr_utility.set_location('FONM : '||l_effective_date, 10);
200 hr_utility.set_location('per_in_ler_id : '||p_per_in_ler_id, 10);
201 --
202 ben_elp_cache.elpeoy_getcacdets
203 (p_effective_date => l_effective_date,
204 p_business_group_id => p_business_group_id,
205 p_eligy_prfl_id => p_eligy_prfl_id,
206 p_inst_set => l_inst_dets,
207 p_inst_count => l_inst_count);
208 --
209 if l_inst_count > 0 then
210 --
211 -- Operation
212 -- =========
213 -- 1) Grab all profiles for this eligibility profile id
214 -- 2) Look only at profiles for this PTIP_ID
215 -- 3) if program is not null then, get all the ptip and check if
216 --
217 -- 4) Derive set of plans for the pgm that the ptip refers to
218 -- 5) Set must be derived based on whether the plans are subject
219 -- to COBRA or not.
220 -- 6) If person eligible for any of the plans and exclude flag = 'Y'
221 -- then no problem.
222 -- 7) If person eligible for any of the plans and exclude flag = 'N'
223 -- then fail criteria.
224 --
225 for l_insttorrw_num in l_inst_dets.first .. l_inst_dets.last loop
226 --
227 l_rows_found := true;
228 --
229 if p_per_in_ler_id is not null
230 then
231 --
232 -- Check if person is eligible for PTIP - Bug 4545191.
233 --
234 open pilc2
235 (c_per_in_ler_id => p_per_in_ler_id
236 ,c_ptip_id => l_inst_dets(l_insttorrw_num).ptip_id
237 );
238 fetch pilc2 into l_dummy;
239 if pilc2%found then
240 --
241 l_cur_found := TRUE;
242 --
243 -- If person is eligible fo PTIP, check if the plans subject to
244 -- cobra regulations are still eligible - Bug 4545191.
245 --
246 if l_inst_dets(l_insttorrw_num).only_pls_subj_cobra_flag = 'Y' then
247 open pilc1
248 (c_effective_date => l_effective_date
249 ,c_per_in_ler_id => p_per_in_ler_id
250 ,c_ptip_id => l_inst_dets(l_insttorrw_num).ptip_id
251 ,c_only_pls_subj_cobra_flag => l_inst_dets(l_insttorrw_num).only_pls_subj_cobra_flag
252 );
253 fetch pilc1 into l_dummy;
254 if pilc1%notfound then
255 l_cur_found := FALSE;
256 end if;
257 close pilc1;
258 end if;
259 --
260 else
261 --
262 l_cur_found := FALSE;
263 --
264 end if;
265 close pilc2;
266 --
267 else
268 --
269 -- Check if person is eligible for PTIP - Bug 4545191.
270 --
271 open c2
272 (c_business_group_id => p_business_group_id
273 ,c_effective_date => l_effective_date
274 ,c_person_id => p_person_id
275 ,c_ptip_id => l_inst_dets(l_insttorrw_num).ptip_id
276 );
277 fetch c2 into l_dummy;
278 if c2%found then
279 --
280 l_cur_found := TRUE;
281 --
282 -- If person is eligible fo PTIP, check if the plans subject to
283 -- cobra regulations are still eligible - Bug 4545191.
284 --
285 if l_inst_dets(l_insttorrw_num).only_pls_subj_cobra_flag = 'Y' then
286 open c1
287 (c_business_group_id => p_business_group_id
288 ,c_effective_date => l_effective_date
289 ,c_person_id => p_person_id
290 ,c_ptip_id => l_inst_dets(l_insttorrw_num).ptip_id
291 ,c_only_pls_subj_cobra_flag => l_inst_dets(l_insttorrw_num).only_pls_subj_cobra_flag
292 );
293 fetch c1 into l_dummy;
294 if c1%notfound then
295 l_cur_found := FALSE;
296 end if;
297 close c1;
298 end if;
299 else
300 --
301 l_cur_found := FALSE;
302 --
303 end if;
304 close c2;
305 end if;
306 --
307 if l_cur_found then
308 --
309 if l_inst_dets(l_insttorrw_num).excld_flag = 'N' then
310 --
311 l_ok := true;
312 exit;
313 --
314 end if;
315 --
316 if l_inst_dets(l_insttorrw_num).excld_flag = 'Y' then
317 --
318 l_ok := false;
319 exit;
320 --
321 end if;
322 --
323 else
324 --
325 if l_inst_dets(l_insttorrw_num).excld_flag = 'Y' then
326 --
327 l_ok := true;
328 --
329 end if;
330 --
331 end if;
332 --
333 end loop;
334 --
335 end if;
336 --
337 if l_rows_found and
338 not l_ok then
339 --
340 ben_evaluate_elig_profiles.g_inelg_rsn_cd := 'EOY';
341 fnd_message.set_name('BEN','BEN_92226_EOY_ELIG_PRFL_FAIL');
342 hr_utility.set_location('Criteria Failed: '||l_proc,20);
343 raise ben_evaluate_elig_profiles.g_criteria_failed;
344 --
345 end if;
346 --
347 hr_utility.set_location('Leaving :'||l_proc,20);
348 --
349 end check_elig_othr_ptip_prte;
350 --
351
352
353 procedure check_elig_dpnt_othr_ptip
354 (p_eligy_prfl_id in number
355 ,p_business_group_id in number
356 ,p_effective_date in date
357 ,p_lf_evt_ocrd_dt in date
358 ,p_person_id in number
359 --
360 ,p_per_in_ler_id in number
361 )
362 is
363 --
364 l_proc varchar2(100):=g_package||'check_elig_dpnt_other_ptip';
365 --
366 l_inst_dets ben_elp_cache.g_cache_elpetd_instor;
367 l_inst_count number;
368 l_insttorrw_num binary_integer;
369 l_ok boolean := false;
370 l_rows_found boolean := false;
371 l_dummy varchar2(1);
372 l_pl_rec ben_comp_object.g_cache_pl_rec_table;
373 --
374 cursor c1
375 (c_ptip_id in number
376 ,c_bgp_id in number
377 ,c_eff_date in date
378 ,c_person_id in number
379 )
380 is
381 select /*+ bendtlep.check_elig_dpnt_othr_ptip.c1 */
382 null
383 from ben_pl_f pln,
384 ben_plip_f cpp,
385 ben_ptip_f ctp,
386 ben_elig_per_f epo,
387 ben_elig_dpnt edp,
388 ben_per_in_ler pil
389 --per_contact_relationships pcr -- bug 6811004 fix reverted for bug 9900419
390 where pln.pl_id = cpp.pl_id
391 and pln.business_group_id = c_bgp_id
392 and c_eff_date
393 between pln.effective_start_date and pln.effective_end_date
394 and cpp.business_group_id = pln.business_group_id
395 and c_eff_date
396 between cpp.effective_start_date and cpp.effective_end_date
397 and cpp.pgm_id = ctp.pgm_id
398 and pln.pl_typ_id = ctp.pl_typ_id
399 and ctp.ptip_id = c_ptip_id
400 and ctp.business_group_id = pln.business_group_id
401 and c_eff_date
402 between ctp.effective_start_date and ctp.effective_end_date
403 --and pcr.contact_person_id = c_person_id -- bug 6811004 fix reverted for bug 9900419
404 --and edp.dpnt_person_id = pcr.person_id -- bug 6811004 fix reverted for bug 9900419
405 and edp.dpnt_person_id = c_person_id --9900419
406 and epo.pgm_id = ctp.pgm_id
407 and epo.pl_id = pln.pl_id
408 and epo.business_group_id = c_bgp_id
409 and c_eff_date
410 between epo.effective_start_date and epo.effective_end_date
411 and epo.elig_flag = 'Y'
412 and edp.dpnt_inelig_flag = 'N'
413 and edp.create_dt = (select max(edp2.create_dt)
414 from ben_elig_dpnt edp2
415 ,ben_per_in_ler pil2
416 where edp2.dpnt_person_id = edp.dpnt_person_id
417 and edp2.elig_per_id = epo.elig_per_id
418 and pil2.per_in_ler_id(+)=edp2.per_in_ler_id
419 and pil2.business_group_id(+)=edp2.business_group_id
420 and edp2.create_dt <= c_eff_date --9900419
421 and (pil2.per_in_ler_stat_cd
422 not in ('VOIDD','BCKDT')
423 or pil2.per_in_ler_stat_cd is null))
424 and epo.elig_per_id = edp.elig_per_id
425 and pil.per_in_ler_id(+)=edp.per_in_ler_id
426 and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
427 or pil.per_in_ler_stat_cd is null
428 )
429 -- bug 14757304
430 and pil.lf_evt_ocrd_dt = (select max(pil3.lf_evt_ocrd_dt)
431 from ben_per_in_ler pil3
432 ,ben_elig_dpnt edp3
433 ,ben_elig_per_elctbl_chc epe -- 16218534
434 where pil3.per_in_ler_id = edp3.per_in_ler_id
435 and edp3.dpnt_person_id = c_person_id
436 and edp3.elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id -- 16218534
437 and epe.ptip_id = c_ptip_id -- 16218534
438 and pil3.per_in_ler_stat_cd
439 not in ('VOIDD','BCKDT')
440 )
441 -- end 14757304
442 -- and epo.per_in_ler_id = edp.per_in_ler_id -- 14142138
443 and c_eff_date
444 between epo.effective_start_date and epo.effective_end_date;
445 --
446 l_effective_date date ;
447 begin
448 --
449 hr_utility.set_location('Entering: '||l_proc, 10);
450 --
451 -- fonm
452 l_effective_date := nvl(p_lf_evt_ocrd_dt,p_effective_date) ;
453 if ben_manage_life_events.fonm = 'Y'
454 and ben_manage_life_events.g_fonm_cvg_strt_dt is not null then
455 --
456 l_effective_date := nvl(ben_manage_life_events.g_fonm_cvg_strt_dt,l_effective_date);
457 --
458 END IF;
459 hr_utility.set_location('FONM : '||l_effective_date, 10);
460 --
461
462 -- Getting eligibility profile compensation level by eligibility profile
463 --
464 ben_elp_cache.elpetd_getcacdets
465 (p_effective_date => l_effective_date,
466 p_business_group_id => p_business_group_id,
467 p_eligy_prfl_id => p_eligy_prfl_id,
468 p_inst_set => l_inst_dets,
469 p_inst_count => l_inst_count);
470 --
471 hr_utility.set_location('l_inst_count: '||l_inst_count, 10);
472 --
473 if l_inst_count > 0 then
474 --
475 -- Operation
476 -- =========
477 -- 1) Grab all profiles for this eligibility profile id
478 -- 2) Look only at profiles for this PTIP_ID
479 -- 3) if program is not null then, get all the ptip and check if
480 --
481 -- 4) Derive set of plans for the pgm that the ptip refers to
482 -- 5) If person eligible for any of the plans and exclude flag = 'Y'
483 -- then no problem.
484 -- 6) If person eligible for any of the plans and exclude flag = 'N'
485 -- then fail criteria.
486 --
487 for l_insttorrw_num in l_inst_dets.first .. l_inst_dets.last loop
488 --
489 l_rows_found := true;
490 --
491 -- Removed the nvls to resolve execute waiting problems for
492 --
493 hr_utility.set_location('ptip_id '|| l_inst_dets(l_insttorrw_num).ptip_id,44333);
494 hr_utility.set_location('p_business_group_id '||p_business_group_id,44333);
495 hr_utility.set_location('l_effective_date'||l_effective_date, 44333);
496 hr_utility.set_location('p_person_id '||p_person_id,44333);
497
498 open c1
499 (c_ptip_id => l_inst_dets(l_insttorrw_num).ptip_id
500 ,c_bgp_id => p_business_group_id
501 ,c_eff_date => l_effective_date
502 ,c_person_id => p_person_id
503 );
504 fetch c1 into l_dummy;
505 if c1%found then
506 --
507 close c1;
508 if l_inst_dets(l_insttorrw_num).excld_flag = 'N' then
509 --
510 l_ok := true;
511 exit;
512 --
513 end if;
514 --
515 if l_inst_dets(l_insttorrw_num).excld_flag = 'Y' then
516 --
517 l_ok := false;
518 exit;
519 --
520 end if;
521 --
522 else
523 --
524 close c1;
525 if l_inst_dets(l_insttorrw_num).excld_flag = 'Y' then
526 --
527 l_ok := true;
528 -- exit ;
529 --
530 end if;
531 end if;
532 --
533 end loop;
534 --
535 end if;
536 --
537 if l_rows_found and
538 not l_ok then
539 --
540 ben_evaluate_elig_profiles.g_inelg_rsn_cd := 'ETD';
541 fnd_message.set_name('BEN','BEN_92226_ETD_ELIG_PRFL_FAIL');
542 hr_utility.set_location('Criteria Failed: '||l_proc,20);
543 raise ben_evaluate_elig_profiles.g_criteria_failed;
544 --
545 end if;
546 --
547 hr_utility.set_location('Leaving :'||l_proc,20);
548 --
549 end check_elig_dpnt_othr_ptip;
550 --
551 end ben_elpro_check_eligibility;
[Home] [Help]