[Home] [Help]
563: pil.ler_id,
564: epe.object_version_number,
565: epe.elctbl_flag,
566: epe.per_in_ler_id
567: from ben_elig_per_elctbl_chc epe,
568: ben_per_in_ler pil
569: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
570: and epe.per_in_ler_id = pil.per_in_ler_id;
571: --
575: cursor c_det_crnt_enrt(l_rslt_id number) is
576: select pen.bnft_amt,
577: pen.bnft_ordr_num
578: from ben_prtt_enrt_rslt_f pen,
579: ben_elig_per_elctbl_chc epe
580: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
581: and epe.prtt_enrt_rslt_id = l_rslt_id
582: and pen.prtt_enrt_rslt_id = l_rslt_id;
583: --
613: l_state c_state%rowtype;
614: --
615: cursor c_other_oipl_cvg is
616: select enb.val
617: from ben_elig_per_elctbl_chc epe,
618: ben_enrt_bnft enb
619: where epe.pl_id=l_epe.pl_id
620: and nvl(epe.pgm_id,-1)=nvl(l_epe.pgm_id,-1)
621: and epe.per_in_ler_id=l_epe.per_in_ler_id
627: ;
628: --
629: cursor c_other_pl_cvg is
630: select enb.val
631: from ben_elig_per_elctbl_chc epe,
632: ben_enrt_bnft enb
633: where epe.ptip_id=l_epe.ptip_id
634: and nvl(epe.pgm_id,-1)=nvl(l_epe.pgm_id,-1)
635: and epe.per_in_ler_id=l_epe.per_in_ler_id
641: ;
642: --
643: cursor c_other_pl_in_ptip_cvg(cv_effective_date date) is
644: select enb.val
645: from ben_elig_per_elctbl_chc epe,
646: ben_oipl_f oipl,
647: ben_oipl_f oipl2,
648: ben_enrt_bnft enb
649: where epe.ptip_id=l_epe.ptip_id
691: /*********************************
692: CODE PRIOR TO WWBUG: 2157614
693: cursor c_other_oipls_in_plan_type is
694: select 'Y'
695: from ben_elig_per_elctbl_chc epe
696: where epe.pl_typ_id=l_epe.pl_typ_id
697: and epe.pl_id<>l_epe.pl_id
698: and nvl(epe.pgm_id,-1)=nvl(l_epe.pgm_id,-1)
699: and epe.oipl_id is not null
1628: epe.business_group_id,
1629: epe.crntly_enrd_flag,
1630: epe.elctbl_flag,
1631: epe.object_version_number
1632: from ben_elig_per_elctbl_chc epe,
1633: ben_per_in_ler pil
1634: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1635: and epe.per_in_ler_id = pil.per_in_ler_id ;
1636: --
1637: --bug 1895846 to check for the pending work flow or suspended enrollment results
1638: --
1639: cursor c_epe_in_pndg is
1640: select null
1641: from ben_elig_per_elctbl_chc epe,
1642: ben_per_in_ler pil
1643: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1644: and epe.per_in_ler_id = pil.per_in_ler_id
1645: and nvl(epe.in_pndg_wkflow_flag,'N') <> 'N' ;
1867: pen.bnft_amt,
1868: enb.*
1869: from ben_prtt_enrt_rslt_f pen,
1870: ben_enrt_bnft enb,
1871: ben_elig_per_elctbl_chc epe,
1872: ben_per_in_ler pil
1873: where pen.business_group_id = l_business_group_id
1874: and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
1875: between pen.enrt_cvg_strt_dt
2019: epe.oipl_id,
2020: epe.pgm_id,
2021: epe.pl_typ_id
2022: FROM ben_per_in_ler pil,
2023: ben_elig_per_elctbl_chc epe
2024: WHERE pil.business_group_id = epe.business_group_id
2025: AND pil.per_in_ler_id = epe.per_in_ler_id
2026: AND epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2027: AND epe.crntly_enrd_flag = 'Y';
2060: l_chk_prev_sus_enr c_chk_prev_sus_enr%rowtype;
2061:
2062: cursor c_get_cvg_rstr(p_prev_pil_id number) is
2063: SELECT enb1.*
2064: FROM ben_elig_per_elctbl_chc epe,
2065: ben_elig_per_elctbl_chc epe1,
2066: ben_enrt_bnft enb1
2067: WHERE epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2068: AND epe.crntly_enrd_flag = 'Y'
2061:
2062: cursor c_get_cvg_rstr(p_prev_pil_id number) is
2063: SELECT enb1.*
2064: FROM ben_elig_per_elctbl_chc epe,
2065: ben_elig_per_elctbl_chc epe1,
2066: ben_enrt_bnft enb1
2067: WHERE epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2068: AND epe.crntly_enrd_flag = 'Y'
2069: AND epe1.per_in_ler_id = p_prev_pil_id