[Home] [Help]
702: ***************************/
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
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';
1606: l_rstrn_mn_cvg_rqd_amt number;
1607: --End Bug 4644489
1608:
1609: -- 5529258
1610: l_dflt_to_asn_pndg_ctfn_cd ben_pl_f.DFLT_TO_ASN_PNDG_CTFN_CD%type;
1611: l_INTERIM_ELCTBL_CHC_ID number;
1612: l_INTERIM_BNFT_AMT number;
1613:
1614: -- ends 5529258
1703: pln.no_mn_cvg_amt_apls_flag,
1704: pln.no_mx_cvg_amt_apls_flag,
1705: pln.dflt_to_asn_pndg_ctfn_cd, /*ENH*/
1706: pln.dflt_to_asn_pndg_ctfn_rl --5529258
1707: from ben_pl_f pln
1708: where pln.pl_id = l_pl_id
1709: and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
1710: between pln.effective_start_date
1711: and pln.effective_end_date;
1733: select ctfn.rqd_flag,
1734: ctfn.enrt_ctfn_typ_cd,
1735: ctfn.ctfn_rqd_when_rl,
1736: pln.ctfn_determine_cd
1737: from ben_bnft_rstrn_ctfn_f ctfn, ben_pl_f pln -- Bug 5887675
1738: where ctfn.pl_id = l_pl_id
1739: and ctfn.pl_id = pln.pl_id
1740: and ctfn.business_group_id = l_business_group_id
1741: and pln.business_group_id = l_business_group_id