APPS.BEN_SSPNDD_ENROLLMENT dependencies on BEN_ELCTBL_CHC_CTFN

Line 584: from ben_elctbl_chc_ctfn ecc

580: select ecc.rqd_flag,
581: ecc.enrt_ctfn_typ_cd,
582: ecc.SUSP_IF_CTFN_NOT_PRVD_FLAG,
583: ecc.ctfn_determine_cd
584: from ben_elctbl_chc_ctfn ecc
585: where ecc.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
586: and ecc.enrt_bnft_id is null
587: and ecc.business_group_id = p_business_group_id ;
588: */

Line 1245: from ben_elctbl_chc_ctfn

1241: nvl(enb.dflt_flag,'Y') = 'Y' and
1242: nvl(enb.ctfn_rqd_flag,'N') ='N' and
1243: --bug#4186343
1244: not exists ( select 'Y'
1245: from ben_elctbl_chc_ctfn
1246: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1247: and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
1248: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1249: order by epe.plip_ordr_num,epe.oipl_ordr_num

Line 1282: from ben_elctbl_chc_ctfn

1278: nvl(enb.dflt_flag,'Y') = 'Y' and
1279: nvl(enb.ctfn_rqd_flag,'N') ='N' and
1280: --bug#4186343
1281: not exists ( select 'Y'
1282: from ben_elctbl_chc_ctfn
1283: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1284: and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
1285: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1286: order by epe.plip_ordr_num,epe.oipl_ordr_num

Line 1324: from ben_elctbl_chc_ctfn

1320: ((nvl(enb.dflt_flag,'Y') = 'Y' and
1321: nvl(enb.ctfn_rqd_flag,'N') ='N')
1322: or ( nvl(enb.mx_wo_ctfn_flag,'Y') = 'Y' and nvl(enb.ordr_num,0)=0 ) )
1323: and not exists ( select 'Y'
1324: from ben_elctbl_chc_ctfn
1325: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1326: and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
1327: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1328: -- If the default one is enter value at enrollment then ctfn is Y

Line 1366: from ben_elctbl_chc_ctfn

1362: enb.elig_per_elctbl_chc_id (+) = epe.elig_per_elctbl_chc_id and
1363: ((nvl(enb.dflt_flag,'Y') = 'Y' and nvl(enb.ctfn_rqd_flag,'N') ='N')
1364: or ( nvl(enb.mx_wo_ctfn_flag,'Y') = 'Y' and nvl(enb.ordr_num,0)=0 ) )
1365: and not exists ( select 'Y'
1366: from ben_elctbl_chc_ctfn
1367: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1368: and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
1369: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1370: -- If the default one is enter value at enrollment then ctfn is Y

Line 1406: from ben_elctbl_chc_ctfn

1402: pl.effective_start_date and pl.effective_end_date and
1403: epe.oipl_ordr_num is not null and
1404: epe.oipl_ordr_num< p_oipl_ordr_num
1405: and not exists ( select 'Y'
1406: from ben_elctbl_chc_ctfn
1407: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1408: and nvl(enrt_bnft_id,nvl(eb.enrt_bnft_id,-1)) = nvl(eb.enrt_bnft_id,-1)
1409: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1410: order by epe.oipl_ordr_num ;

Line 1443: from ben_elctbl_chc_ctfn

1439: nvl(enb.ordr_num,1) > 0 and
1440: epe.plip_ordr_num is not null and
1441: epe.plip_ordr_num<= p_plip_ordr_num
1442: and not exists ( select 'Y'
1443: from ben_elctbl_chc_ctfn
1444: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1445: and nvl(enrt_bnft_id,nvl(enb.enrt_bnft_id,-1)) = nvl(enb.enrt_bnft_id,-1)
1446: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1447: order by epe.plip_ordr_num,epe.oipl_ordr_num ;

Line 1477: from ben_elctbl_chc_ctfn

1473: p_effective_date between
1474: pl.effective_start_date and pl.effective_end_date and
1475: epe.oipl_ordr_num < p_oipl_ordr_num
1476: and not exists ( select 'Y'
1477: from ben_elctbl_chc_ctfn
1478: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1479: and nvl(enrt_bnft_id,nvl(eb.enrt_bnft_id,-1)) = nvl(eb.enrt_bnft_id,-1)
1480: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1481: order by epe.oipl_ordr_num desc

Line 1518: from ben_elctbl_chc_ctfn

1514: (epe.plip_ordr_num <= p_plip_ordr_num and
1515: (epe.plip_ordr_num <> p_plip_ordr_num or
1516: (epe.oipl_ordr_num is null or epe.oipl_ordr_num < p_oipl_ordr_num ))) -- changed to < from <=
1517: and not exists ( select 'Y'
1518: from ben_elctbl_chc_ctfn
1519: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1520: and nvl(enrt_bnft_id,nvl(eb.enrt_bnft_id,-1)) = nvl(eb.enrt_bnft_id,-1)
1521: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1522: order by epe.plip_ordr_num desc, epe.oipl_ordr_num desc

Line 1557: from ben_elctbl_chc_ctfn

1553: enb.business_group_id=p_business_group_id and
1554: enb.ctfn_rqd_flag = 'N' and
1555: enb.ordr_num < p_bnft_ordr_num
1556: and not exists ( select 'Y'
1557: from ben_elctbl_chc_ctfn
1558: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1559: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y'
1560: and enrt_bnft_id = enb.enrt_bnft_id ) --BUG 5158595
1561: order by enb.ordr_num

Line 1599: from ben_elctbl_chc_ctfn

1595: eb.elig_per_elctbl_chc_id=epe.elig_per_elctbl_chc_id and
1596: eb.business_group_id=p_business_group_id and
1597: eb.ctfn_rqd_flag='N'
1598: and not exists ( select 'Y'
1599: from ben_elctbl_chc_ctfn
1600: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1601: and enrt_bnft_id = eb.enrt_bnft_id
1602: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y')
1603: order by eb.ordr_num desc ;

Line 1657: from ben_elctbl_chc_ctfn

1653: and enb2.elig_per_elctbl_chc_id = enb1.elig_per_elctbl_chc_id
1654: and enb2.entr_val_at_enrt_flag = 'Y'
1655: and enb1.entr_val_at_enrt_flag = 'N'
1656: and not exists ( select 'Y'
1657: from ben_elctbl_chc_ctfn
1658: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1659: and enrt_bnft_id = enb1.enrt_bnft_id -- 5381200
1660: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y' )
1661: and enb1.ordr_num = 0 ;

Line 1689: from ben_elctbl_chc_ctfn

1685: and enb1.entr_val_at_enrt_flag = 'N'
1686: and enb1.ordr_num = 0
1687: and enb2.dflt_flag = 'Y'
1688: and not exists ( select 'Y'
1689: from ben_elctbl_chc_ctfn
1690: where elig_per_elctbl_chc_id = epe.elig_per_elctbl_chc_id
1691: and enrt_bnft_id = enb1.enrt_bnft_id -- 5381200
1692: and SUSP_IF_CTFN_NOT_PRVD_FLAG = 'Y') ;
1693: -- RCHASE Bug#5353 add current benefit cursor