[Home] [Help]
537: l_interim_bnft_amt NUMBER := p_interim_bnft_amt;
538: --
539: cursor c_epe(v_elig_per_elctbl_chc_id number) is
540: select epe.*
541: from ben_elig_per_elctbl_chc epe, ben_per_in_ler pil
542: where epe.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
543: and epe.per_in_ler_id = pil.per_in_ler_id(+) /* Bug 5225780 */
544: and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') or
545: pil.per_in_ler_stat_cd is null);
924: epe.pl_id,
925: epe.pl_typ_id,
926: epe.oipl_id,
927: pil.ler_id
928: from ben_elig_per_elctbl_chc epe,ben_per_in_ler pil
929: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
930: and pil.per_in_ler_id = epe.per_in_ler_id;
931:
932: l_epe c_epe%rowtype;
1010: epe.elig_per_elctbl_chc_id,
1011: -- RCHASE Bug#5353 added
1012: per.prtt_enrt_rslt_id
1013: from ben_prtt_enrt_rslt_f per,
1014: ben_elig_per_elctbl_chc epe,
1015: ben_per_in_ler pil
1016: where pil.business_group_id=p_business_group_id and
1017: pil.person_id=p_person_id and
1018: pil.per_in_ler_stat_cd='STRTD' and
1063: per.pl_id = nvl(v_pl_id, per.pl_id) and
1064: nvl(per.oipl_id,-1) = nvl(v_oipl_id,nvl(per.oipl_id,-1)) and
1065: not exists (
1066: select null
1067: from ben_elig_per_elctbl_chc epe,
1068: ben_per_in_ler pil
1069: where pil.business_group_id=p_business_group_id and
1070: pil.person_id=p_person_id and
1071: pil.per_in_ler_stat_cd='STRTD' and
1215: cursor c_default_epe is
1216: select epe.elig_per_elctbl_chc_id,
1217: enb.enrt_bnft_id
1218: from ben_per_in_ler pil,
1219: ben_elig_per_elctbl_chc epe,
1220: ben_pl_f pl,
1221: ben_enrt_bnft enb
1222: where
1223: pil.business_group_id=p_business_group_id and
1252: cursor c_default_pl_epe is
1253: select epe.elig_per_elctbl_chc_id,
1254: enb.enrt_bnft_id
1255: from ben_per_in_ler pil,
1256: ben_elig_per_elctbl_chc epe,
1257: ben_pl_f pl,
1258: ben_enrt_bnft enb
1259: where
1260: pil.business_group_id=p_business_group_id and
1294: cursor c_default_bnft_epe is
1295: select epe.elig_per_elctbl_chc_id,
1296: enb.enrt_bnft_id
1297: from ben_per_in_ler pil,
1298: ben_elig_per_elctbl_chc epe,
1299: ben_pl_f pl,
1300: ben_enrt_bnft enb
1301: where
1302: pil.business_group_id=p_business_group_id and
1337: cursor c_default_bnft_pl_epe is
1338: select epe.elig_per_elctbl_chc_id,
1339: enb.enrt_bnft_id
1340: from ben_per_in_ler pil,
1341: ben_elig_per_elctbl_chc epe,
1342: ben_pl_f pl,
1343: ben_enrt_bnft enb
1344: where
1345: pil.business_group_id=p_business_group_id and
1376: --
1377: cursor c_min_oipl_epe is
1378: select epe.elig_per_elctbl_chc_id, eb.enrt_bnft_id
1379: from ben_per_in_ler pil,
1380: ben_elig_per_elctbl_chc epe,
1381: ben_pl_f pl,
1382: ben_enrt_bnft eb
1383: where
1384: pil.business_group_id=p_business_group_id and
1414: cursor c_min_pl_epe is
1415: select epe.elig_per_elctbl_chc_id,
1416: enb.enrt_bnft_id
1417: from ben_per_in_ler pil,
1418: ben_elig_per_elctbl_chc epe,
1419: ben_enrt_bnft enb,
1420: ben_pl_f pl
1421: where pil.business_group_id=p_business_group_id and
1422: pil.person_id=p_person_id and
1449: cursor c_next_lower_pl_epe is
1450: select epe.elig_per_elctbl_chc_id,
1451: eb.enrt_bnft_id
1452: from ben_per_in_ler pil,
1453: ben_elig_per_elctbl_chc epe,
1454: ben_pl_f pl,
1455: ben_enrt_bnft eb
1456: where
1457: pil.business_group_id=p_business_group_id and
1488: cursor c_next_lower_pl_typ_epe is
1489: select epe.elig_per_elctbl_chc_id,
1490: eb.enrt_bnft_id
1491: from ben_per_in_ler pil,
1492: ben_elig_per_elctbl_chc epe,
1493: ben_enrt_bnft eb,
1494: ben_pl_f pl
1495: where
1496: pil.business_group_id=p_business_group_id and
1527: cursor c_min_bnft_epe is
1528: select epe.elig_per_elctbl_chc_id,
1529: enb.enrt_bnft_id
1530: from ben_per_in_ler pil,
1531: ben_elig_per_elctbl_chc epe,
1532: ben_enrt_bnft enb,
1533: ben_pl_f pl
1534: where
1535: pil.business_group_id=p_business_group_id and
1568: cursor c_next_lower_bnft_pl_epe is
1569: select epe.elig_per_elctbl_chc_id,
1570: eb.enrt_bnft_id
1571: from ben_per_in_ler pil,
1572: ben_elig_per_elctbl_chc epe,
1573: ben_enrt_bnft eb,
1574: ben_pl_f pl
1575: where
1576: pil.business_group_id=p_business_group_id and
1637: enb1.enrt_bnft_id
1638: from ben_enrt_bnft enb1,
1639: ben_enrt_bnft enb2,
1640: ben_pl_f pl,
1641: ben_elig_per_elctbl_chc epe --Bug 3042379 Dont select 'R' cases
1642: --to make it consistent with other
1643: --interim cursors.
1644: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1645: and nvl(epe.dpnt_dsgn_cd,'O')='O'
1667: enb1.enrt_bnft_id
1668: from ben_enrt_bnft enb1,
1669: ben_enrt_bnft enb2,
1670: ben_pl_f pl,
1671: ben_elig_per_elctbl_chc epe --Bug 3042379 Dont select 'R' cases
1672: --to make it consistent with other
1673: --interim cursors.
1674: where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1675: and nvl(epe.dpnt_dsgn_cd,'O')='O'
1709: --
1710: /*
1711: cursor c_current_same_epe(c_current_epe_id number) is
1712: select epe_new.elig_per_elctbl_chc_id
1713: from ben_elig_per_elctbl_chc epe_current,
1714: ben_elig_per_elctbl_chc epe_new,
1715: ben_per_in_ler pil_new
1716: where epe_current.elig_per_elctbl_chc_id = c_current_epe_id
1717: and epe_current.pl_id = epe_new.pl_id
1710: /*
1711: cursor c_current_same_epe(c_current_epe_id number) is
1712: select epe_new.elig_per_elctbl_chc_id
1713: from ben_elig_per_elctbl_chc epe_current,
1714: ben_elig_per_elctbl_chc epe_new,
1715: ben_per_in_ler pil_new
1716: where epe_current.elig_per_elctbl_chc_id = c_current_epe_id
1717: and epe_current.pl_id = epe_new.pl_id
1718: and nvl(epe_current.oipl_id,-1) = nvl(epe_new.oipl_id,-1)
1728: --
1729: cursor c_current_same_epe(c_current_pen_id number) is
1730: select epe_new.elig_per_elctbl_chc_id
1731: from ben_prtt_enrt_rslt_f pen_current,
1732: ben_elig_per_elctbl_chc epe_new,
1733: ben_per_in_ler pil_new
1734: where pen_current.prtt_enrt_rslt_id = c_current_pen_id
1735: and pen_current.pl_id = epe_new.pl_id
1736: and nvl(pen_current.pgm_id,-1) = nvl(epe_new.pgm_id,-1)
1804: select new_epe.elig_per_elctbl_chc_id,
1805: interim.bnft_ordr_num ordr_num,
1806: interim.bnft_amt
1807: from ben_prtt_enrt_rslt_f interim,
1808: ben_elig_per_elctbl_chc new_epe
1809: where interim.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
1810: and interim.per_in_ler_id <> c_per_in_ler_id
1811: and interim.effective_end_date = hr_api.g_eot
1812: and interim.enrt_cvg_thru_dt = hr_api.g_eot
2507: pen.pl_id pen_pl_id,
2508: pen.oipl_id pen_oipl_id,
2509: pen.enrt_cvg_thru_dt,
2510: pen.object_version_number
2511: From ben_elig_per_elctbl_chc epe,
2512: ben_prtt_enrt_rslt_f pen
2513: Where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2514: and epe.business_group_id = p_business_group_id
2515: and pen.prtt_enrt_rslt_id(+)=epe.prtt_enrt_rslt_id
2889: ,pen.plip_ordr_num /*ENH*/
2890: ,pen.bnft_ordr_num /*ENH*/
2891: ,epe.dpnt_dsgn_cd
2892: From ben_prtt_enrt_rslt_f pen
2893: ,ben_elig_per_elctbl_chc epe
2894: ,ben_per_in_ler pil
2895: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2896: and pen.business_group_id= p_business_group_id
2897: and pen.prtt_enrt_rslt_stat_cd is null
2931: pen.effective_start_date and pen.effective_end_date
2932: ;
2933: Cursor c_epe (c_elig_per_elctbl_chc_id number) is
2934: select *
2935: From ben_elig_per_elctbl_chc
2936: Where elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id
2937: ;
2938: cursor c_prv_ee (c_prtt_enrt_rslt_id number) is
2939: select prv.prtt_rt_val_id
3619: --END Bug 2958032
3620: from ben_prtt_enrt_rslt_f pen,
3621: ben_pil_elctbl_chc_popl pel,
3622: ben_per_in_ler pil,
3623: ben_elig_per_elctbl_chc epe
3624: where pen.prtt_enrt_rslt_id=c_prtt_enrt_rslt_id
3625: and p_effective_date between
3626: pen.effective_start_date and pen.effective_end_date
3627: and pen.person_id=pil.person_id