[Home] [Help]
185: SELECT elig_per_elctbl_chc_id,
186: pl_id,
187: pgm_id,
188: oipl_id
189: FROM ben_elig_per_elctbl_chc
190: WHERE per_in_ler_id = p_per_in_ler_id AND elctbl_flag = 'Y';
191:
192: l_rec_electable_epes csr_electable_epes%rowtype;
193: --
194: cursor c_ended_result (p_per_in_ler_id number,
195: p_business_group_id number) is
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
255: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
256: and pen.pl_typ_id = p_pl_typ_id
257: and pen.prtt_enrt_rslt_stat_cd is null
258: and exists (select null
259: from ben_elig_per_elctbl_chc epe
260: where nvl(epe.pgm_id,-1) = nvl(pen.pgm_id,-1)
261: and epe.per_in_ler_id = p_per_in_ler_id
262: and elctbl_flag = 'Y');
263: -- end 8968118
264: --
265: cursor c_pl_typ (p_per_in_ler_id number) is
266: select epe.pgm_id,
267: epe.pl_typ_id
268: from ben_elig_per_elctbl_chc epe
269: where epe.per_in_ler_id = p_per_in_ler_id
270: and epe.pgm_id is not null
271: and epe.business_group_id = p_business_group_id
272: group by pgm_id, pl_typ_id;
286: and pen.enrt_cvg_thru_dt < p_lf_evt_ocrd_dt
287: and pen.prtt_enrt_rslt_stat_cd is null
288: and pen.per_in_ler_id = p_per_in_ler_id
289: and pen.business_group_id = p_business_group_id
290: and not exists (select null from ben_elig_per_elctbl_chc
291: where per_in_ler_id = p_per_in_ler_id
292: and pgm_id = pen.pgm_id))
293: and prv.prtt_rt_val_stat_cd is null;
294:
308: pen.enrt_cvg_thru_dt
309: and pen.prtt_enrt_rslt_stat_cd is null
310: and pen.per_in_ler_id <> p_per_in_ler_id
311: and pen.business_group_id = p_business_group_id
312: and not exists (select null from ben_elig_per_elctbl_chc
313: where per_in_ler_id = p_per_in_ler_id
314: and pgm_id = pen.pgm_id);
315: --
316: cursor c_pen (p_prtt_enrt_rslt_id number) is
348: -- 5376652
349: cursor c_epe (p_per_in_ler_id number,
350: p_prtt_enrt_rslt_id number) is
351: select elig_per_elctbl_chc_id
352: from ben_elig_per_elctbl_chc epe
353: where epe.per_in_ler_id in (select per_in_ler_id
354: from ben_prtt_enrt_rslt_f
355: where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id)
356: and epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;
408: -- End bug 7206471
409: --
410: cursor c_get_pgm is
411: select distinct epe.pgm_id
412: from ben_elig_per_elctbl_chc epe
413: where epe.per_in_ler_id = p_per_in_ler_id;
414: --
415: -- Get program extra info to determine if rates should be adjusted.
416: --
432: --
433: cursor c_get_elctbl_chc is
434: select min(ecr.rt_strt_dt) rt_strt_dt
435: ,epe.ptip_id
436: from ben_elig_per_elctbl_chc epe
437: ,ben_enrt_rt ecr
438: ,ben_enrt_bnft enb
439: where epe.per_in_ler_id = p_per_in_ler_id
440: and epe.business_group_id = p_business_group_id
449: --
450: cursor c_get_elctbl_chc_for_cvg is
451: select min(epe.enrt_cvg_strt_dt) enrt_cvg_strt_dt
452: ,epe.ptip_id
453: from ben_elig_per_elctbl_chc epe
454: where epe.per_in_ler_id = p_per_in_ler_id
455: and epe.business_group_id = p_business_group_id
456: group by epe.ptip_id;
457: --
880: end loop;
881: close c_epe;
882:
883: -- update to avoid 91711 error
884: update ben_elig_per_elctbl_chc set prtt_enrt_rslt_id = null,
885: CRNTLY_ENRD_FLAG = 'N'
886: where prtt_enrt_rslt_id = l_future_results.prtt_enrt_rslt_id
887: and per_in_ler_id = p_per_in_ler_id;
888:
1354: close c_epe;
1355: --
1356: -- update to avoid 91711 error
1357: --
1358: update ben_elig_per_elctbl_chc
1359: set prtt_enrt_rslt_id = null
1360: ,CRNTLY_ENRD_FLAG = 'N'
1361: where prtt_enrt_rslt_id = l_future_results2.prtt_enrt_rslt_id
1362: and per_in_ler_id = p_per_in_ler_id;