[Home] [Help]
1339: pln.effective_start_date and pln.effective_end_date;
1340: --
1341: cursor c_epe is
1342: select epe.fonm_cvg_strt_dt
1343: from ben_elig_per_elctbl_chc epe
1344: where epe.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
1345: and epe.per_in_ler_id = p_per_in_ler_id;
1346:
1347: --
1398: --
1399: cursor c_get_elctbl_chc is
1400: select min(ecr.rt_strt_dt) rt_strt_dt
1401: ,epe.ptip_id
1402: from ben_elig_per_elctbl_chc epe
1403: ,ben_enrt_rt ecr
1404: ,ben_enrt_bnft enb
1405: where epe.per_in_ler_id = p_per_in_ler_id
1406: and epe.business_group_id = p_business_group_id
4409: --Bug 2172036 populating the assignment_id in the pen
4410: cursor c_epe is
4411: select epe.assignment_id ,
4412: epe.fonm_cvg_strt_dt
4413: from ben_elig_per_elctbl_chc epe
4414: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id ;
4415: --
4416: cursor c_enb_pen is
4417: select enb.prtt_enrt_rslt_id
4563: l_ecr c_ecr%rowtype;
4564: -- bug 5621049
4565: cursor c_validate_epe_pen(cv_elig_per_elctblc_chc_id number,cv_prtt_enrt_rslt_id number ) is
4566: select null
4567: from ben_elig_per_elctbl_chc
4568: where elig_per_elctbl_chc_id = cv_elig_per_elctblc_chc_id
4569: and prtt_enrt_rslt_id = cv_prtt_enrt_rslt_id;
4570:
4571: -- Added for bug 7206471
4571: -- Added for bug 7206471
4572: --
4573: cursor c_get_prior_per_in_ler is
4574: select 'Y'
4575: from ben_elig_per_elctbl_chc epe, ben_per_in_ler pil,
4576: ben_per_in_ler pil2
4577: where epe.elig_per_elctbl_chc_id=p_elig_per_elctbl_chc_id
4578: and pil.per_in_ler_id <> epe.per_in_ler_id
4579: and trunc(pil.lf_evt_ocrd_dt, 'MM') = trunc(pil2.lf_evt_ocrd_dt, 'MM')
4585: l_exists varchar2(2);
4586: --
4587: cursor c_get_pgm is
4588: select distinct epe.pgm_id
4589: from ben_elig_per_elctbl_chc epe
4590: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
4591: --
4592: cursor c_get_pgm_extra_info_cvg(p_pgm_id number) is
4593: select pgi_information1
4599: --
4600: cursor c_get_elctbl_chc_for_cvg is
4601: select min(epe.enrt_cvg_strt_dt) enrt_cvg_strt_dt
4602: ,epe.ptip_id
4603: from ben_elig_per_elctbl_chc epe
4604: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
4605: and epe.business_group_id = p_business_group_id
4606: group by epe.ptip_id;
4607: --
4611: select pen.*, epe.per_in_ler_id pil_id
4612: from ben_prtt_enrt_rslt_f pen
4613: ,ben_ptip_f ptip
4614: ,ben_per_in_ler pil
4615: ,ben_elig_per_elctbl_chc epe
4616: where pen.effective_end_date = hr_api.g_eot -- '31-dec-4712'
4617: and pen.enrt_cvg_thru_dt <> hr_api.g_eot -- '31-dec-4712'
4618: and pen.prtt_enrt_rslt_stat_cd is null
4619: and pen.person_id = pil.person_id -- 318321
4627: and ptip.effective_end_date;
4628: --
4629: cursor c_prtt_enrt_rslt_adj (p_prtt_enrt_rslt_id number) is
4630: select null
4631: from ben_le_clsn_n_rstr leclr, ben_elig_per_elctbl_chc epe
4632: where leclr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_ADJ'
4633: AND leclr.BKUP_TBL_ID = p_prtt_enrt_rslt_id
4634: AND leclr.PER_IN_LER_ID = epe.per_in_ler_id
4635: AND epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
6964: cursor c1 is
6965: select ebr.enrt_bnft_id,
6966: ebr.object_version_number
6967: from ben_enrt_bnft ebr,
6968: ben_elig_per_elctbl_chc epe,
6969: ben_per_in_ler pil
6970: where ebr.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id and
6971: ( p_enrt_bnft_id is null OR
6972: ebr.enrt_bnft_id <> p_enrt_bnft_id )
7280: -- open c_pl_opt_name cursor only if error needs to be displayed.
7281: --
7282: cursor c_pl_opt_name is
7283: select pln.name || ' '|| opt.name
7284: from ben_elig_per_elctbl_chc epe,
7285: ben_pl_f pln,
7286: ben_oipl_f oipl,
7287: ben_opt_f opt
7288: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
7300: cursor c_elinf is
7301: select epe.crntly_enrd_flag
7302: ,epe.elctbl_flag
7303: ,epe.mndtry_flag
7304: from ben_elig_per_elctbl_chc epe
7305: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
7306: --
7307: l_bnft c_bnft%rowtype;
7308: l_elinf c_elinf%rowtype;