[Home] [Help]
196: select pen.*
197: from ben_prtt_enrt_rslt_f pen,
198: ben_elig_per_elctbl_chc epe
199: where pen.prtt_enrt_rslt_stat_cd is null
200: and pen.effective_end_date = hr_api.g_eot
201: and pen.enrt_cvg_thru_dt <> hr_api.g_eot
202: and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
203: and pen.per_in_ler_id <> p_per_in_ler_id -- 5365585 . result ended in the same life event will not be picked up .
204: and epe.per_in_ler_id = p_per_in_ler_id
197: from ben_prtt_enrt_rslt_f pen,
198: ben_elig_per_elctbl_chc epe
199: where pen.prtt_enrt_rslt_stat_cd is null
200: and pen.effective_end_date = hr_api.g_eot
201: and pen.enrt_cvg_thru_dt <> hr_api.g_eot
202: and pen.prtt_enrt_rslt_id = epe.prtt_enrt_rslt_id
203: and pen.per_in_ler_id <> p_per_in_ler_id -- 5365585 . result ended in the same life event will not be picked up .
204: and epe.per_in_ler_id = p_per_in_ler_id
205: and epe.CRNTLY_ENRD_FLAG = 'Y'
208: --Bug 5102337. we need to exclude interim enrollments being selected here.
209: and not exists ( select 'x'
210: from ben_prtt_enrt_rslt_f susp
211: where susp.RPLCS_SSPNDD_RSLT_ID = pen.prtt_enrt_rslt_id
212: and susp.effective_end_date <> hr_api.g_eot
213: and susp.prtt_enrt_rslt_stat_cd is null
214: and susp.enrt_cvg_thru_dt = hr_api.g_eot ) ;
215: -- bug#5345189
216: cursor c_future_results (p_person_id in number,
210: from ben_prtt_enrt_rslt_f susp
211: where susp.RPLCS_SSPNDD_RSLT_ID = pen.prtt_enrt_rslt_id
212: and susp.effective_end_date <> hr_api.g_eot
213: and susp.prtt_enrt_rslt_stat_cd is null
214: and susp.enrt_cvg_thru_dt = hr_api.g_eot ) ;
215: -- bug#5345189
216: cursor c_future_results (p_person_id in number,
217: p_enrt_cvg_thru_dt in date,
218: p_pgm_id in number,
224: select pen.*
225: from ben_prtt_enrt_rslt_f pen,
226: ben_per_in_ler pil
227: where pen.person_id = p_person_id
228: and pen.effective_end_date = hr_api.g_eot
229: and pen.business_group_id = p_business_group_id
230: and pil.business_group_id = p_business_group_id
231: and pen.enrt_cvg_strt_dt > p_enrt_cvg_thru_dt
232: and pen.per_in_ler_id = pil.per_in_ler_id
254: from ben_prtt_rt_val prv
255: where prv.prtt_enrt_rslt_id in
256: (select pen.prtt_enrt_rslt_id
257: from ben_prtt_enrt_rslt_f pen
258: where pen.effective_end_date = hr_api.g_eot
259: and pen.enrt_cvg_thru_dt <> hr_api.g_eot
260: and pen.person_id = p_person_id
261: and pen.pgm_id <> p_pgm_id
262: and pen.enrt_cvg_thru_dt < p_lf_evt_ocrd_dt
255: where prv.prtt_enrt_rslt_id in
256: (select pen.prtt_enrt_rslt_id
257: from ben_prtt_enrt_rslt_f pen
258: where pen.effective_end_date = hr_api.g_eot
259: and pen.enrt_cvg_thru_dt <> hr_api.g_eot
260: and pen.person_id = p_person_id
261: and pen.pgm_id <> p_pgm_id
262: and pen.enrt_cvg_thru_dt < p_lf_evt_ocrd_dt
263: and pen.prtt_enrt_rslt_stat_cd is null
274: p_business_group_id number,
275: p_pgm_id number) is
276: select pen.*
277: from ben_prtt_enrt_rslt_f pen
278: where pen.effective_end_date = hr_api.g_eot
279: and pen.enrt_cvg_thru_dt <> hr_api.g_eot
280: and pen.person_id = p_person_id
281: and pen.pgm_id <> p_pgm_id
282: and pen.pl_typ_id = p_pl_typ_id
275: p_pgm_id number) is
276: select pen.*
277: from ben_prtt_enrt_rslt_f pen
278: where pen.effective_end_date = hr_api.g_eot
279: and pen.enrt_cvg_thru_dt <> hr_api.g_eot
280: and pen.person_id = p_person_id
281: and pen.pgm_id <> p_pgm_id
282: and pen.pl_typ_id = p_pl_typ_id
283: and p_lf_evt_ocrd_dt between pen.enrt_cvg_strt_dt and
292: cursor c_pen (p_prtt_enrt_rslt_id number) is
293: select pen.effective_start_date,
294: pen.object_version_number
295: from ben_prtt_enrt_rslt_f pen
296: where pen.effective_end_date = hr_api.g_eot
297: and pen.prtt_enrt_rslt_stat_cd is null
298: and pen.business_group_id = p_business_group_id
299: and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
300: --
335: ,pen.person_id
336: from ben_prtt_enrt_rslt_f pen
337: ,ben_prtt_rt_val prv
338: ,ben_acty_base_rt_f abr
339: where pen.effective_end_date = hr_api.g_eot
340: and pen.enrt_cvg_thru_dt <> hr_api.g_eot
341: and pen.prtt_enrt_rslt_stat_cd is null
342: and pen.person_id = p_person_id
343: and pen.business_group_id = p_business_group_id
336: from ben_prtt_enrt_rslt_f pen
337: ,ben_prtt_rt_val prv
338: ,ben_acty_base_rt_f abr
339: where pen.effective_end_date = hr_api.g_eot
340: and pen.enrt_cvg_thru_dt <> hr_api.g_eot
341: and pen.prtt_enrt_rslt_stat_cd is null
342: and pen.person_id = p_person_id
343: and pen.business_group_id = p_business_group_id
344: and pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
356: ) is
357: select pen.*
358: from ben_prtt_enrt_rslt_f pen
359: ,ben_ptip_f ptip
360: where pen.effective_end_date = hr_api.g_eot -- '31-dec-4712'
361: and pen.enrt_cvg_thru_dt <> hr_api.g_eot -- '31-dec-4712'
362: and pen.prtt_enrt_rslt_stat_cd is null
363: and pen.person_id = p_person_id -- 318321
364: and pen.business_group_id = p_business_group_id -- 81545
357: select pen.*
358: from ben_prtt_enrt_rslt_f pen
359: ,ben_ptip_f ptip
360: where pen.effective_end_date = hr_api.g_eot -- '31-dec-4712'
361: and pen.enrt_cvg_thru_dt <> hr_api.g_eot -- '31-dec-4712'
362: and pen.prtt_enrt_rslt_stat_cd is null
363: and pen.person_id = p_person_id -- 318321
364: and pen.business_group_id = p_business_group_id -- 81545
365: and pen.ptip_id = p_ptip_id -- 54444
1243: p_effective_end_date => l_effective_end_date,
1244: p_business_group_id => p_business_group_id,
1245: p_object_version_number => l_get_enrt_rslts_for_pen.object_version_number,
1246: p_effective_date => p_effective_date,
1247: p_datetrack_mode => hr_api.g_correction,
1248: p_multi_row_validate => FALSE,
1249: p_enrt_cvg_thru_dt => l_get_elctbl_chc_for_cvg.enrt_cvg_strt_dt - 1
1250: );
1251: end loop; -- c_get_enrt_rslts_for_pen