APPS.BEN_SSPNDD_ENROLLMENT dependencies on BEN_ELCTBL_CHC_CTFN

Line 621: from ben_elctbl_chc_ctfn ecc

617: select ecc.rqd_flag,
618: ecc.enrt_ctfn_typ_cd,
619: ecc.SUSP_IF_CTFN_NOT_PRVD_FLAG,
620: ecc.ctfn_determine_cd
621: from ben_elctbl_chc_ctfn ecc
622: where ecc.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
623: and ecc.enrt_bnft_id is null
624: and ecc.business_group_id = p_business_group_id ;
625: */

Line 1301: from ben_elctbl_chc_ctfn

1297: nvl(enb.dflt_flag,'Y') = 'Y' and
1298: nvl(enb.ctfn_rqd_flag,'N') ='N' and
1299: --bug#4186343
1300: not exists ( select 'Y'
1301: from ben_elctbl_chc_ctfn
1302: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1303: and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
1304: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1305: order by epe.plip_ordr_num,epe.oipl_ordr_num

Line 1338: from ben_elctbl_chc_ctfn

1334: nvl(enb.dflt_flag,'Y') = 'Y' and
1335: nvl(enb.ctfn_rqd_flag,'N') ='N' and
1336: --bug#4186343
1337: not exists ( select 'Y'
1338: from ben_elctbl_chc_ctfn
1339: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1340: and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
1341: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1342: order by epe.plip_ordr_num,epe.oipl_ordr_num

Line 1380: from ben_elctbl_chc_ctfn

1376: ((nvl(enb.dflt_flag,'Y') = 'Y' and
1377: nvl(enb.ctfn_rqd_flag,'N') ='N')
1378: or ( nvl(enb.mx_wo_ctfn_flag,'Y') = 'Y' and nvl(enb.ordr_num,0)=0 ) )
1379: and not exists ( select 'Y'
1380: from ben_elctbl_chc_ctfn
1381: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1382: and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
1383: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1384: -- If the default one is enter value at enrollment then ctfn is Y

Line 1422: from ben_elctbl_chc_ctfn

1418: enb.elig_per_elctbl_chc_id (+) = epe.elig_per_elctbl_chc_id and
1419: ((nvl(enb.dflt_flag,'Y') = 'Y' and nvl(enb.ctfn_rqd_flag,'N') ='N')
1420: or ( nvl(enb.mx_wo_ctfn_flag,'Y') = 'Y' and nvl(enb.ordr_num,0)=0 ) )
1421: and not exists ( select 'Y'
1422: from ben_elctbl_chc_ctfn
1423: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1424: and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
1425: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1426: -- If the default one is enter value at enrollment then ctfn is Y

Line 1462: from ben_elctbl_chc_ctfn

1458: pl.effective_start_date and pl.effective_end_date and
1459: epe.oipl_ordr_num is not null and
1460: epe.oipl_ordr_num< p_oipl_ordr_num
1461: and not exists ( select 'Y'
1462: from ben_elctbl_chc_ctfn
1463: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1464: and nvl(enrt_bnft_id,nvl(eb.enrt_bnft_id,-1)) = nvl(eb.enrt_bnft_id,-1)
1465: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1466: order by epe.oipl_ordr_num ;

Line 1499: from ben_elctbl_chc_ctfn

1495: nvl(enb.ordr_num,1) > 0 and
1496: epe.plip_ordr_num is not null and
1497: epe.plip_ordr_num<= p_plip_ordr_num
1498: and not exists ( select 'Y'
1499: from ben_elctbl_chc_ctfn
1500: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1501: and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
1502: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1503: order by epe.plip_ordr_num,epe.oipl_ordr_num ;

Line 1543: from ben_elctbl_chc_ctfn

1539: p_effective_date between
1540: pl.effective_start_date and pl.effective_end_date and
1541: epe.oipl_ordr_num < p_oipl_ordr_num
1542: and not exists ( select 'Y'
1543: from ben_elctbl_chc_ctfn
1544: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1545: and nvl(enrt_bnft_id,nvl(eb.enrt_bnft_id,-1)) = nvl(eb.enrt_bnft_id,-1)
1546: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1547: order by epe.oipl_ordr_num desc

Line 1594: from ben_elctbl_chc_ctfn

1590: (epe.plip_ordr_num <= p_plip_ordr_num and
1591: (epe.plip_ordr_num <> p_plip_ordr_num or
1592: (epe.oipl_ordr_num is null or epe.oipl_ordr_num < p_oipl_ordr_num ))) -- changed to < from <=
1593: and not exists ( select 'Y'
1594: from ben_elctbl_chc_ctfn
1595: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1596: and nvl(enrt_bnft_id,nvl(eb.enrt_bnft_id,-1)) = nvl(eb.enrt_bnft_id,-1)
1597: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1598: order by epe.plip_ordr_num desc, epe.oipl_ordr_num desc

Line 1633: from ben_elctbl_chc_ctfn

1629: enb.business_group_id=p_business_group_id and
1630: enb.ctfn_rqd_flag = 'N' and
1631: enb.ordr_num < p_bnft_ordr_num
1632: and not exists ( select 'Y'
1633: from ben_elctbl_chc_ctfn
1634: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1635: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y'
1636: and enrt_bnft_id = enb.enrt_bnft_id ) --BUG 5158595
1637: order by enb.ordr_num

Line 1675: from ben_elctbl_chc_ctfn

1671: eb.elig_per_elctbl_chc_id=epe.elig_per_elctbl_chc_id and
1672: eb.business_group_id=p_business_group_id and
1673: eb.ctfn_rqd_flag='N'
1674: and not exists ( select 'Y'
1675: from ben_elctbl_chc_ctfn
1676: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1677: and enrt_bnft_id = eb.enrt_bnft_id
1678: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1679: order by eb.ordr_num desc ;

Line 1733: from ben_elctbl_chc_ctfn

1729: and enb2.elig_per_elctbl_chc_id = enb1.elig_per_elctbl_chc_id
1730: and enb2.entr_val_at_enrt_flag = 'Y'
1731: and enb1.entr_val_at_enrt_flag = 'N'
1732: and not exists ( select 'Y'
1733: from ben_elctbl_chc_ctfn
1734: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1735: and enrt_bnft_id = enb1.enrt_bnft_id -- 5381200
1736: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y' )
1737: and enb1.ordr_num = 0 ;

Line 1765: from ben_elctbl_chc_ctfn

1761: and enb1.entr_val_at_enrt_flag = 'N'
1762: and enb1.ordr_num = 0
1763: and enb2.dflt_flag = 'Y'
1764: and not exists ( select 'Y'
1765: from ben_elctbl_chc_ctfn
1766: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1767: and enrt_bnft_id = enb1.enrt_bnft_id -- 5381200
1768: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y') ;
1769: -- RCHASE Bug#5353 add current benefit cursor