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