APPS.BEN_REOPEN_ENDED_RESULTS dependencies on BEN_ELIG_PER_ELCTBL_CHC

Line 189: FROM ben_elig_per_elctbl_chc

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: --

Line 198: ben_elig_per_elctbl_chc epe

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

Line 244: from ben_elig_per_elctbl_chc epe

240: --
241: cursor c_pl_typ (p_per_in_ler_id number) is
242: select epe.pgm_id,
243: epe.pl_typ_id
244: from ben_elig_per_elctbl_chc epe
245: where epe.per_in_ler_id = p_per_in_ler_id
246: and epe.pgm_id is not null
247: and epe.business_group_id = p_business_group_id
248: group by pgm_id, pl_typ_id;

Line 266: and not exists (select null from ben_elig_per_elctbl_chc

262: and pen.enrt_cvg_thru_dt < p_lf_evt_ocrd_dt
263: and pen.prtt_enrt_rslt_stat_cd is null
264: and pen.per_in_ler_id = p_per_in_ler_id
265: and pen.business_group_id = p_business_group_id
266: and not exists (select null from ben_elig_per_elctbl_chc
267: where per_in_ler_id = p_per_in_ler_id
268: and pgm_id = pen.pgm_id))
269: and prv.prtt_rt_val_stat_cd is null;
270:

Line 288: and not exists (select null from ben_elig_per_elctbl_chc

284: pen.enrt_cvg_thru_dt
285: and pen.prtt_enrt_rslt_stat_cd is null
286: and pen.per_in_ler_id <> p_per_in_ler_id
287: and pen.business_group_id = p_business_group_id
288: and not exists (select null from ben_elig_per_elctbl_chc
289: where per_in_ler_id = p_per_in_ler_id
290: and pgm_id = pen.pgm_id);
291: --
292: cursor c_pen (p_prtt_enrt_rslt_id number) is

Line 314: from ben_elig_per_elctbl_chc epe

310: -- 5376652
311: cursor c_epe (p_per_in_ler_id number,
312: p_prtt_enrt_rslt_id number) is
313: select elig_per_elctbl_chc_id
314: from ben_elig_per_elctbl_chc epe
315: where epe.per_in_ler_id in (select per_in_ler_id
316: from ben_prtt_enrt_rslt_f
317: where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id)
318: and epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id;

Line 374: from ben_elig_per_elctbl_chc epe

370: -- End bug 7206471
371: --
372: cursor c_get_pgm is
373: select distinct epe.pgm_id
374: from ben_elig_per_elctbl_chc epe
375: where epe.per_in_ler_id = p_per_in_ler_id;
376: --
377: -- Get program extra info to determine if rates should be adjusted.
378: --

Line 398: from ben_elig_per_elctbl_chc epe

394: --
395: cursor c_get_elctbl_chc is
396: select min(ecr.rt_strt_dt) rt_strt_dt
397: ,epe.ptip_id
398: from ben_elig_per_elctbl_chc epe
399: ,ben_enrt_rt ecr
400: ,ben_enrt_bnft enb
401: where epe.per_in_ler_id = p_per_in_ler_id
402: and epe.business_group_id = p_business_group_id

Line 415: from ben_elig_per_elctbl_chc epe

411: --
412: cursor c_get_elctbl_chc_for_cvg is
413: select min(epe.enrt_cvg_strt_dt) enrt_cvg_strt_dt
414: ,epe.ptip_id
415: from ben_elig_per_elctbl_chc epe
416: where epe.per_in_ler_id = p_per_in_ler_id
417: and epe.business_group_id = p_business_group_id
418: group by epe.ptip_id;
419: --

Line 846: update ben_elig_per_elctbl_chc set prtt_enrt_rslt_id = null,

842: end loop;
843: close c_epe;
844:
845: -- update to avoid 91711 error
846: update ben_elig_per_elctbl_chc set prtt_enrt_rslt_id = null,
847: CRNTLY_ENRD_FLAG = 'N'
848: where prtt_enrt_rslt_id = l_future_results.prtt_enrt_rslt_id
849: and per_in_ler_id = p_per_in_ler_id;
850: