[Home] [Help]
678: ***************************/
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
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';
1567: l_rstrn_mn_cvg_rqd_amt number;
1568: --End Bug 4644489
1569:
1570: -- 5529258
1571: l_dflt_to_asn_pndg_ctfn_cd ben_pl_f.DFLT_TO_ASN_PNDG_CTFN_CD%type;
1572: l_INTERIM_ELCTBL_CHC_ID number;
1573: l_INTERIM_BNFT_AMT number;
1574:
1575: -- ends 5529258
1664: pln.no_mn_cvg_amt_apls_flag,
1665: pln.no_mx_cvg_amt_apls_flag,
1666: pln.dflt_to_asn_pndg_ctfn_cd, /*ENH*/
1667: pln.dflt_to_asn_pndg_ctfn_rl --5529258
1668: from ben_pl_f pln
1669: where pln.pl_id = l_pl_id
1670: and cv_effective_date -- FONM nvl(p_lf_evt_ocrd_dt,p_effective_date)
1671: between pln.effective_start_date
1672: and pln.effective_end_date;
1694: select ctfn.rqd_flag,
1695: ctfn.enrt_ctfn_typ_cd,
1696: ctfn.ctfn_rqd_when_rl,
1697: pln.ctfn_determine_cd
1698: from ben_bnft_rstrn_ctfn_f ctfn, ben_pl_f pln -- Bug 5887675
1699: where ctfn.pl_id = l_pl_id
1700: and ctfn.pl_id = pln.pl_id
1701: and ctfn.business_group_id = l_business_group_id
1702: and pln.business_group_id = l_business_group_id