[Home] [Help]
563: --
564: -- FONM
565: cursor c_opt(cv_oipl_id number, cv_effective_date date) is
566: select oipl.opt_id
567: from ben_oipl_f oipl
568: where oipl.oipl_id = cv_oipl_id
569: and cv_effective_date
570: between oipl.effective_start_date
571: and oipl.effective_end_date;
618: --
619: cursor c_other_pl_in_ptip_cvg(cv_effective_date date) is
620: select enb.val
621: from ben_elig_per_elctbl_chc epe,
622: ben_oipl_f oipl,
623: ben_oipl_f oipl2,
624: ben_enrt_bnft enb
625: where epe.ptip_id=l_epe.ptip_id
626: and nvl(epe.pgm_id,-1)=nvl(l_epe.pgm_id,-1)
619: cursor c_other_pl_in_ptip_cvg(cv_effective_date date) is
620: select enb.val
621: from ben_elig_per_elctbl_chc epe,
622: ben_oipl_f oipl,
623: ben_oipl_f oipl2,
624: ben_enrt_bnft enb
625: where epe.ptip_id=l_epe.ptip_id
626: and nvl(epe.pgm_id,-1)=nvl(l_epe.pgm_id,-1)
627: and epe.pl_typ_id=l_epe.pl_typ_id
679: /* Start of Changes for WWBUG: 2157614 */
680: cursor c_other_oipls_in_plan_type(cv_effective_date date) is
681: select 'Y'
682: from ben_pl_f pln,
683: ben_oipl_f oipl
684: where pln.pl_typ_id=l_epe.pl_typ_id
685: and pln.pl_id<>l_epe.pl_id
686: and cv_effective_date between -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date) between
687: pln.effective_start_date and pln.effective_end_date
843: --
844: -- Check if it is the only plan within the plan type that has opts
845: --
846: -- 6502657 The c_other_oipls_in_plan_type check is not correct.
847: -- It checks on ben_pl_f and ben_oipl_f tables only
848: -- and this does not ensure Active/ Eligible/Electable Plans.
849: -- A better approach is to check for options only in current Plan
850: /*
851: l_other_oipls_exist:='N';
1611: --
1612: -- FONM
1613: cursor c_opt(rqd_oipl_id number, cv_effective_date date) is
1614: select opt_id
1615: from ben_oipl_f
1616: where oipl_id = rqd_oipl_id
1617: and cv_effective_date -- nvl(p_lf_evt_ocrd_dt,p_effective_date)
1618: between effective_start_date
1619: and effective_end_date;