eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.BEN_SSPNDD_ENROLLMENT dependencies on BEN_ENRT_BNFT

Line 586: from ben_enrt_bnft enb

582: pil.per_in_ler_stat_cd is null);
583: --
584: cursor c_epe_from_enb(v_enrt_bnft_id number) is
585: select enb.*
586: from ben_enrt_bnft enb
587: where enb.enrt_bnft_id = v_enrt_bnft_id ;
588: --
589: l_epe_from_enb c_epe_from_enb%ROWTYPE;
590: --

Line 595: from ben_enrt_bnft enb

591: cursor c_enb_rng(v_epe_id number,
592: -- v_enrt_bnft_id number,
593: v_bnft_amt number) is
594: select enb.*
595: from ben_enrt_bnft enb
596: where -- enb.enrt_bnft_id = v_enrt_bnft_id
597: enb.val = p_interim_bnft_amt
598: and enb.elig_per_elctbl_chc_id = v_epe_id ;
599: --

Line 604: from ben_enrt_bnft enb

600: l_dummy varchar2(30);
601: --
602: cursor c_enb(v_elig_per_elctbl_chc_id number) is
603: select enb.*
604: from ben_enrt_bnft enb
605: where enb.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
606: and enb.MX_WO_CTFN_FLAG = 'N' ;
607: --
608: cursor c_enb_entr(v_elig_per_elctbl_chc_id number) is

Line 610: from ben_enrt_bnft enb

606: and enb.MX_WO_CTFN_FLAG = 'N' ;
607: --
608: cursor c_enb_entr(v_elig_per_elctbl_chc_id number) is
609: select enb.*
610: from ben_enrt_bnft enb
611: where enb.elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
612: and enb.MX_WO_CTFN_FLAG = 'Y' ;
613: --
614: /*

Line 629: from ben_enrt_bnft

625: */
626: --
627: cursor c_enbcount(v_elig_per_elctbl_chc_id number) is
628: select count(*)
629: from ben_enrt_bnft
630: where elig_per_elctbl_chc_id = v_elig_per_elctbl_chc_id
631: and ordr_num >= 0 ; --This will exclude the records being create for SAME case
632: --which are created with -1
633: --

Line 1277: ben_enrt_bnft enb

1273: enb.enrt_bnft_id
1274: from ben_per_in_ler pil,
1275: ben_elig_per_elctbl_chc epe,
1276: ben_pl_f pl,
1277: ben_enrt_bnft enb
1278: where
1279: pil.business_group_id=p_business_group_id and
1280: pil.person_id=p_person_id and
1281: --pil.per_in_ler_stat_cd='STRTD' and

Line 1314: ben_enrt_bnft enb

1310: enb.enrt_bnft_id
1311: from ben_per_in_ler pil,
1312: ben_elig_per_elctbl_chc epe,
1313: ben_pl_f pl,
1314: ben_enrt_bnft enb
1315: where
1316: pil.business_group_id=p_business_group_id and
1317: pil.person_id=p_person_id and
1318: --pil.per_in_ler_stat_cd='STRTD' and

Line 1356: ben_enrt_bnft enb

1352: enb.enrt_bnft_id
1353: from ben_per_in_ler pil,
1354: ben_elig_per_elctbl_chc epe,
1355: ben_pl_f pl,
1356: ben_enrt_bnft enb
1357: where
1358: pil.business_group_id=p_business_group_id and
1359: pil.person_id=p_person_id and
1360: --pil.per_in_ler_stat_cd='STRTD' and

Line 1399: ben_enrt_bnft enb

1395: enb.enrt_bnft_id
1396: from ben_per_in_ler pil,
1397: ben_elig_per_elctbl_chc epe,
1398: ben_pl_f pl,
1399: ben_enrt_bnft enb
1400: where
1401: pil.business_group_id=p_business_group_id and
1402: pil.person_id=p_person_id and
1403: --pil.per_in_ler_stat_cd='STRTD' and

Line 1438: ben_enrt_bnft eb

1434: select epe.elig_per_elctbl_chc_id, eb.enrt_bnft_id
1435: from ben_per_in_ler pil,
1436: ben_elig_per_elctbl_chc epe,
1437: ben_pl_f pl,
1438: ben_enrt_bnft eb
1439: where
1440: pil.business_group_id=p_business_group_id and
1441: pil.person_id=p_person_id and
1442: --pil.per_in_ler_stat_cd='STRTD' and

Line 1475: ben_enrt_bnft enb,

1471: select epe.elig_per_elctbl_chc_id,
1472: enb.enrt_bnft_id
1473: from ben_per_in_ler pil,
1474: ben_elig_per_elctbl_chc epe,
1475: ben_enrt_bnft enb,
1476: ben_pl_f pl
1477: where pil.business_group_id=p_business_group_id and
1478: pil.person_id=p_person_id and
1479: --pil.per_in_ler_stat_cd='STRTD' and

Line 1511: ben_enrt_bnft eb

1507: eb.enrt_bnft_id
1508: from ben_per_in_ler pil,
1509: ben_elig_per_elctbl_chc epe,
1510: ben_pl_f pl,
1511: ben_enrt_bnft eb
1512: where
1513: pil.business_group_id=p_business_group_id and
1514: pil.person_id=p_person_id and
1515: --pil.per_in_ler_stat_cd='STRTD' and

Line 1559: ben_enrt_bnft eb,

1555: select epe.elig_per_elctbl_chc_id,
1556: eb.enrt_bnft_id
1557: from ben_per_in_ler pil,
1558: ben_elig_per_elctbl_chc epe,
1559: ben_enrt_bnft eb,
1560: ben_pl_f pl
1561: where
1562: pil.business_group_id=p_business_group_id and
1563: pil.person_id=p_person_id and

Line 1608: ben_enrt_bnft enb,

1604: select epe.elig_per_elctbl_chc_id,
1605: enb.enrt_bnft_id
1606: from ben_per_in_ler pil,
1607: ben_elig_per_elctbl_chc epe,
1608: ben_enrt_bnft enb,
1609: ben_pl_f pl
1610: where
1611: pil.business_group_id=p_business_group_id and
1612: pil.person_id=p_person_id and

Line 1649: ben_enrt_bnft eb,

1645: select epe.elig_per_elctbl_chc_id,
1646: eb.enrt_bnft_id
1647: from ben_per_in_ler pil,
1648: ben_elig_per_elctbl_chc epe,
1649: ben_enrt_bnft eb,
1650: ben_pl_f pl
1651: where
1652: pil.business_group_id=p_business_group_id and
1653: pil.person_id=p_person_id and

Line 1693: from ben_enrt_bnft enb1,

1689: cursor c_bnft is
1690: select nvl(enb2.mn_val, 0) min_bnft_amt,
1691: nvl(enb2.dflt_val, 0) dflt_bnft_amt, -- Bug 1886183
1692: enb1.enrt_bnft_id
1693: from ben_enrt_bnft enb1,
1694: ben_enrt_bnft enb2
1695: where enb1.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1696: and enb1.cvg_mlt_cd = 'FLFX'
1697: and enb2.elig_per_elctbl_chc_id = enb1.elig_per_elctbl_chc_id

Line 1694: ben_enrt_bnft enb2

1690: select nvl(enb2.mn_val, 0) min_bnft_amt,
1691: nvl(enb2.dflt_val, 0) dflt_bnft_amt, -- Bug 1886183
1692: enb1.enrt_bnft_id
1693: from ben_enrt_bnft enb1,
1694: ben_enrt_bnft enb2
1695: where enb1.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1696: and enb1.cvg_mlt_cd = 'FLFX'
1697: and enb2.elig_per_elctbl_chc_id = enb1.elig_per_elctbl_chc_id
1698: and enb2.entr_val_at_enrt_flag = 'Y'

Line 1714: from ben_enrt_bnft enb1,

1710: --
1711: cursor c_bnft is
1712: select enb1.val bnft_amt,
1713: enb1.enrt_bnft_id
1714: from ben_enrt_bnft enb1,
1715: ben_enrt_bnft enb2,
1716: ben_pl_f pl,
1717: ben_elig_per_elctbl_chc epe --Bug 3042379 Dont select 'R' cases
1718: --to make it consistent with other

Line 1715: ben_enrt_bnft enb2,

1711: cursor c_bnft is
1712: select enb1.val bnft_amt,
1713: enb1.enrt_bnft_id
1714: from ben_enrt_bnft enb1,
1715: ben_enrt_bnft enb2,
1716: ben_pl_f pl,
1717: ben_elig_per_elctbl_chc epe --Bug 3042379 Dont select 'R' cases
1718: --to make it consistent with other
1719: --interim cursors.

Line 1744: from ben_enrt_bnft enb1,

1740: --
1741: cursor c_dflt_bnft is
1742: select enb1.val bnft_amt,
1743: enb1.enrt_bnft_id
1744: from ben_enrt_bnft enb1,
1745: ben_enrt_bnft enb2,
1746: ben_pl_f pl,
1747: ben_elig_per_elctbl_chc epe --Bug 3042379 Dont select 'R' cases
1748: --to make it consistent with other

Line 1745: ben_enrt_bnft enb2,

1741: cursor c_dflt_bnft is
1742: select enb1.val bnft_amt,
1743: enb1.enrt_bnft_id
1744: from ben_enrt_bnft enb1,
1745: ben_enrt_bnft enb2,
1746: ben_pl_f pl,
1747: ben_elig_per_elctbl_chc epe --Bug 3042379 Dont select 'R' cases
1748: --to make it consistent with other
1749: --interim cursors.

Line 1774: from ben_enrt_bnft enb

1770: --
1771: cursor c_cur_bnft(p_elig_per_elctbl_chc_id in number) is
1772: select enb.val bnft_amt,
1773: enb.enrt_bnft_id
1774: from ben_enrt_bnft enb
1775: where enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1776: ----Bug : 7195598
1777: and enb.ordr_num in (-1,1)
1778: ---Bug 7195598

Line 1902: from ben_enrt_bnft enb

1898: c_bnft_amt number,
1899: c_elig_per_elctbl_chc_id number ) is
1900: select enb.enrt_bnft_id,
1901: enb.val bnft_amt
1902: from ben_enrt_bnft enb
1903: where enb.ordr_num = c_ordr_num
1904: --enb.val = c_bnft_amt
1905: and enb.entr_val_at_enrt_flag = 'N'
1906: and enb.elig_per_elctbl_chc_id = c_elig_per_elctbl_chc_id ;

Line 1914: from ben_enrt_bnft enb

1910: c_bnft_amt number,
1911: c_elig_per_elctbl_chc_id number ) is
1912: select enb.enrt_bnft_id,
1913: enb.val bnft_amt
1914: from ben_enrt_bnft enb
1915: where --enb.ordr_num = c_ordr_num
1916: --enb.val = c_bnft_amt and -- Bug 9774647
1917: /*When salary change event occurs, the coverage amount of the interim before and after salary change LE will change.
1918: Hence commented the condition in cursor 'c_cf_bnft_sm' */

Line 1942: from ben_enrt_bnft enb

1938: /* Cursor to fetch the new Interim benfit amount */
1939: cursor c_interim_bnft(p_elig_per_elctbl_chc_id number) is
1940: select enb.val bnft_amt,
1941: enb.enrt_bnft_id
1942: from ben_enrt_bnft enb
1943: where enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
1944: --Bug 14059090
1945: and ((l_susp_interim_same_epe = 'Y' and enb.ordr_num in (0))
1946: or (l_susp_interim_same_epe = 'N' and enb.ordr_num in (-1,1)))

Line 2683: ,ben_enrt_bnft enb

2679: select ecr.enrt_rt_id
2680: ,ecr.dflt_val
2681: ,ecr.ANN_DFLT_VAL
2682: from ben_enrt_rt ecr
2683: ,ben_enrt_bnft enb
2684: where enb.enrt_bnft_id = ecr.enrt_bnft_id
2685: and ecr.business_group_id = p_business_group_id
2686: and enb.business_group_id = p_business_group_id
2687: and enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id

Line 2694: from ben_enrt_bnft

2690: ;
2691: --
2692: cursor c_bnft is
2693: select enrt_bnft_id, val
2694: from ben_enrt_bnft
2695: where elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
2696: and business_group_id=p_business_group_id
2697: -- and dflt_flag = 'Y'
2698: and (enrt_bnft_id=p_enrt_bnft_id

Line 2706: l_bnft_amt ben_enrt_bnft.val%type;

2702: type enrt_id_table is table of c_rt%rowtype index by binary_integer;
2703: l_proc varchar2(80) := g_package || '.process_interim';
2704: l_rt enrt_id_table;
2705: l_tot_rt number(5) := 0;
2706: l_bnft_amt ben_enrt_bnft.val%type;
2707: l_bnft_id ben_enrt_bnft.enrt_bnft_id%type;
2708: l_suspend_flag varchar2(30);
2709: l_prtt_enrt_interim_id number(15);
2710: l_datetrack_mode varchar2(30);

Line 2707: l_bnft_id ben_enrt_bnft.enrt_bnft_id%type;

2703: l_proc varchar2(80) := g_package || '.process_interim';
2704: l_rt enrt_id_table;
2705: l_tot_rt number(5) := 0;
2706: l_bnft_amt ben_enrt_bnft.val%type;
2707: l_bnft_id ben_enrt_bnft.enrt_bnft_id%type;
2708: l_suspend_flag varchar2(30);
2709: l_prtt_enrt_interim_id number(15);
2710: l_datetrack_mode varchar2(30);
2711: l_effective_start_date date;

Line 3057: from ben_enrt_bnft enb

3053: cursor c_current_bnft (p_prtt_enrt_rslt_id number,
3054: p_elig_per_elctbl_chc_id number,
3055: p_bnft_ordr_num number ) is
3056: select enb.enrt_bnft_id
3057: from ben_enrt_bnft enb
3058: where enb.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
3059: and enb.elig_per_elctbl_chc_id=p_elig_per_elctbl_chc_id
3060: and enb.business_group_id=p_business_group_id
3061: union -- To get this when the enb is not update with the pen_id

Line 3065: from ben_enrt_bnft enb

3061: union -- To get this when the enb is not update with the pen_id
3062: -- This happens in the flex enrollment if the certifications is called
3063: -- from flex routine /*ENH*/
3064: select enb.enrt_bnft_id
3065: from ben_enrt_bnft enb
3066: where
3067: enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
3068: and enb.ordr_num = p_bnft_ordr_num ;
3069:

Line 3803: ,ben_enrt_bnft enb

3799: and ecr.business_group_id = p_business_group_id
3800: union
3801: select ecr.*
3802: from ben_enrt_rt ecr
3803: ,ben_enrt_bnft enb
3804: where enb.enrt_bnft_id = ecr.enrt_bnft_id
3805: and ecr.business_group_id = p_business_group_id
3806: and enb.business_group_id = p_business_group_id
3807: and enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;

Line 3948: from ben_enrt_bnft enb

3944: cursor c_enb(p_prtt_enrt_rslt_id number,
3945: p_elig_per_elctbl_chc_id number) is
3946: select enb.enrt_bnft_id,
3947: enb.object_version_number
3948: from ben_enrt_bnft enb
3949: where enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
3950: and enb.prtt_enrt_rslt_id is not null
3951: and enb.prtt_enrt_rslt_id <> p_prtt_enrt_rslt_id ;
3952: --

Line 3960: ben_enrt_bnft enb

3956: p_effective_date date) is
3957: select enb.enrt_bnft_id,
3958: enb.object_version_number
3959: from ben_prtt_enrt_rslt_f pen,
3960: ben_enrt_bnft enb
3961: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
3962: and p_effective_date between pen.effective_start_date and pen.effective_end_date
3963: and pen.bnft_ordr_num = enb.ordr_num
3964: and enb.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id

Line 4722: ben_enrt_bnft_api.update_enrt_bnft

4718: -- Need to clean the enb records for pen_id
4719: --
4720: for l_enb in c_enb(p_prtt_enrt_rslt_id,l_elig_per_elctbl_chc_id) loop
4721: --
4722: ben_enrt_bnft_api.update_enrt_bnft
4723: (p_enrt_bnft_id => l_enb.enrt_bnft_id
4724: ,p_effective_date => p_effective_date
4725: ,p_object_version_number => l_enb.object_version_number
4726: ,p_business_group_id => p_business_group_id