[Home] [Help]
587: --
588: -- FONM
589: cursor c_opt(cv_oipl_id number, cv_effective_date date) is
590: select oipl.opt_id
591: from ben_oipl_f oipl
592: where oipl.oipl_id = cv_oipl_id
593: and cv_effective_date
594: between oipl.effective_start_date
595: and oipl.effective_end_date;
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
650: and nvl(epe.pgm_id,-1)=nvl(l_epe.pgm_id,-1)
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
650: and nvl(epe.pgm_id,-1)=nvl(l_epe.pgm_id,-1)
651: and epe.pl_typ_id=l_epe.pl_typ_id
703: /* Start of Changes for WWBUG: 2157614 */
704: cursor c_other_oipls_in_plan_type(cv_effective_date date) is
705: select 'Y'
706: from ben_pl_f pln,
707: ben_oipl_f oipl
708: where pln.pl_typ_id=l_epe.pl_typ_id
709: and pln.pl_id<>l_epe.pl_id
710: and cv_effective_date between -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date) between
711: pln.effective_start_date and pln.effective_end_date
882: --
883: -- Check if it is the only plan within the plan type that has opts
884: --
885: -- 6502657 The c_other_oipls_in_plan_type check is not correct.
886: -- It checks on ben_pl_f and ben_oipl_f tables only
887: -- and this does not ensure Active/ Eligible/Electable Plans.
888: -- A better approach is to check for options only in current Plan
889: /*
890: l_other_oipls_exist:='N';
1650: --
1651: -- FONM
1652: cursor c_opt(rqd_oipl_id number, cv_effective_date date) is
1653: select opt_id
1654: from ben_oipl_f
1655: where oipl_id = rqd_oipl_id
1656: and cv_effective_date -- nvl(p_lf_evt_ocrd_dt,p_effective_date)
1657: between effective_start_date
1658: and effective_end_date;