[Home] [Help]
39: 23 DEC 98 jcarpent 115.14 Subtract forfeitures from
40: total flex credits.
41: 29 JAN 99 shdas 115.15 added codes for comp objs.
42: 17 MAR 99 shdas 115.17 modified create_rollover_enrollment.
43: 16 APR 99 shdas 115.18 changed ben_per_in_ler_f to ben_per_in_ler.
44: 29 APR 99 shdas 115.19 added parameters to genutils.formula.
45: 29 Apr 99 lmcdonal 115.20 prtt_enrt_rslt now has stat cd.
46: 04 May 99 shdas 115.21 added jurisdiction code.
47: 27 May 99 maagrawa 115.22 Added new procedures to re-calculate
1115: bpl.prvdd_val,
1116: bpl.object_version_number,
1117: bpl.effective_start_date
1118: from ben_bnft_prvdd_ldgr_f bpl,
1119: ben_per_in_ler pil
1120: where bpl.bnft_prvdr_pool_id=p_epe_rec.bnft_prvdr_pool_id
1121: and bpl.business_group_id=p_epe_rec.business_group_id
1122: and bpl.acty_base_rt_id = l_acty_base_rt_id
1123: and bpl.prtt_enrt_rslt_id=g_credit_pool_result_id
1405: bpl.used_val,
1406: bpl.object_version_number,
1407: bpl.effective_start_date
1408: from ben_bnft_prvdd_ldgr_f bpl,
1409: ben_per_in_ler pil
1410: where bpl.bnft_prvdr_pool_id=p_enrt_rt_rec.decr_bnft_prvdr_pool_id
1411: and bpl.business_group_id=p_epe_rec.business_group_id
1412: and bpl.acty_base_rt_id=p_enrt_rt_rec.acty_base_rt_id
1413: and bpl.prtt_enrt_rslt_id=g_credit_pool_result_id
1583: bpl.object_version_number,
1584: decode(enb.enrt_bnft_id, null,
1585: ecr2.rt_usg_cd,ecr1.rt_usg_cd) rt_usg_cd
1586: from ben_elig_per_elctbl_chc epe,
1587: ben_per_in_ler pil,
1588: ben_enrt_rt ecr1,
1589: ben_enrt_rt ecr2,
1590: ben_enrt_bnft enb,
1591: ben_bnft_prvdd_ldgr_f bpl,
1589: ben_enrt_rt ecr2,
1590: ben_enrt_bnft enb,
1591: ben_bnft_prvdd_ldgr_f bpl,
1592: ben_elig_per_elctbl_chc epe_flex,
1593: ben_per_in_ler pil_flex
1594: where pil.person_id=p_person_id and
1595: pil.business_group_id=p_business_group_id and
1596: pil.per_in_ler_stat_cd='STRTD' and
1597: pil.per_in_ler_id=epe.per_in_ler_id and
1625:
1626: cursor c_ldgr_exist(l_ldgr_id number,l_per_in_ler_id number) is
1627: select 'x'
1628: from ben_bnft_prvdd_ldgr_f bpl,
1629: ben_per_in_ler pil
1630: where bpl.bnft_prvdd_ldgr_id = l_ldgr_id
1631: and bpl.per_in_ler_id = l_per_in_ler_id
1632: -- UK change : Bug 1634870
1633: and bpl.per_in_ler_id = pil.per_in_ler_id
1693: bpl.prtt_ro_of_unusd_amt_flag,
1694: bpl.object_version_number,
1695: bpl.effective_start_date
1696: from ben_bnft_prvdd_ldgr_f bpl,
1697: ben_per_in_ler pil
1698: where bpl.acty_base_rt_id = l_acty_base_rt_id
1699: and bpl.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
1700: -- and bpl.effective_end_date = hr_api.g_eot
1701: and p_effective_date between bpl.effective_start_date
1740: epe.business_group_id,
1741: epe.per_in_ler_id
1742: from ben_elig_per_elctbl_chc epe1,
1743: ben_elig_per_elctbl_chc epe,
1744: ben_per_in_ler pil
1745: where epe1.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id and
1746: epe1.business_group_id=p_business_group_id and
1747: epe1.pgm_id = epe.pgm_id and
1748: epe1.per_in_ler_id = epe.per_in_ler_id and
1755: select bpl.bnft_prvdd_ldgr_id,
1756: bpl.object_version_number,
1757: effective_start_date
1758: from ben_bnft_prvdd_ldgr_f bpl,
1759: ben_per_in_ler pil
1760: where bpl.bnft_prvdr_pool_id = l_epe.bnft_prvdr_pool_id
1761: and bpl.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
1762: and bpl.acty_base_rt_id = p_acty_base_rt_id
1763: --and bpl.effective_end_date = hr_api.g_eot
2175: bpl.object_version_number,
2176: decode(enb.enrt_bnft_id,null,
2177: ecr2.rt_usg_cd,ecr1.rt_usg_cd) rt_usg_cd
2178: from ben_elig_per_elctbl_chc epe,
2179: ben_per_in_ler pil,
2180: ben_enrt_rt ecr1,
2181: ben_enrt_rt ecr2,
2182: ben_enrt_bnft enb,
2183: ben_bnft_prvdd_ldgr_f bpl,
2181: ben_enrt_rt ecr2,
2182: ben_enrt_bnft enb,
2183: ben_bnft_prvdd_ldgr_f bpl,
2184: ben_elig_per_elctbl_chc epe_flex,
2185: ben_per_in_ler pil_flex,
2186: ben_per_in_ler pil_flex1
2187: where pil.per_in_ler_id = p_per_in_ler_id and
2188: pil.business_group_id=p_business_group_id and
2189: epe.bnft_prvdr_pool_id is not null and
2182: ben_enrt_bnft enb,
2183: ben_bnft_prvdd_ldgr_f bpl,
2184: ben_elig_per_elctbl_chc epe_flex,
2185: ben_per_in_ler pil_flex,
2186: ben_per_in_ler pil_flex1
2187: where pil.per_in_ler_id = p_per_in_ler_id and
2188: pil.business_group_id=p_business_group_id and
2189: epe.bnft_prvdr_pool_id is not null and
2190: pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
2322: epe.pl_id,
2323: per.enrt_cvg_strt_dt,
2324: per.per_in_ler_id chc_pen_id
2325: from ben_elig_per_elctbl_chc epe,
2326: ben_per_in_ler pil,
2327: ben_prtt_enrt_rslt_f per
2328: where epe.comp_lvl_cd = 'PLANFC' and
2329: epe.per_in_ler_id = p_per_in_ler_id and
2330: per.prtt_enrt_rslt_stat_cd is null and
2795: bpl.frftd_val,
2796: bpl.object_version_number,
2797: bpl.effective_start_date
2798: from ben_bnft_prvdd_ldgr_f bpl,
2799: ben_per_in_ler pil
2800: where bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id
2801: and bpl.business_group_id=p_business_group_id
2802: and bpl.acty_base_rt_id = p_acty_base_rt_id
2803: and bpl.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
2815: bpl.cash_recd_val,
2816: bpl.object_version_number,
2817: bpl.effective_start_date
2818: from ben_bnft_prvdd_ldgr_f bpl,
2819: ben_per_in_ler pil
2820: where bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id
2821: and bpl.business_group_id=p_business_group_id
2822: -- and bpl.acty_base_rt_id = p_acty_base_rt_id /* Bug No 4714939 */
2823: and bpl.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
3103:
3104: cursor c_cash_abr is
3105: select bpl.acty_base_rt_id
3106: from ben_bnft_prvdd_ldgr_f bpl,
3107: ben_per_in_ler pil,
3108: ben_acty_base_rt_f abr -- Bug 4613270
3109: where bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id
3110: and bpl.business_group_id=p_business_group_id
3111: and bpl.prtt_enrt_rslt_id=g_credit_pool_result_id
3122: -- Bug 5447507
3123: CURSOR c_cash_abr_from_epe
3124: IS
3125: SELECT ecr.acty_base_rt_id
3126: FROM ben_per_in_ler pil,
3127: ben_elig_per_elctbl_chc epe,
3128: ben_enrt_rt ecr
3129: WHERE pil.person_id = p_person_id
3130: AND pil.business_group_id = p_business_group_id
3148: bpl.cash_recd_val,
3149: bpl.object_version_number,
3150: bpl.effective_start_date
3151: from ben_bnft_prvdd_ldgr_f bpl,
3152: ben_per_in_ler pil
3153: where bpl.bnft_prvdr_pool_id = c_bnft_prvdr_pool_id
3154: and bpl.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
3155: and bpl.cash_recd_val is not null
3156: and c_effective_date
3525: epe.pl_typ_id,
3526: epe.per_in_ler_id
3527: from
3528: ben_bnft_pool_rlovr_rqmt_f prr,
3529: ben_per_in_ler pil,
3530: ben_elig_per_elctbl_chc epe,
3531: ben_enrt_rt ecr,
3532: ben_prtt_rt_val prv,
3533: ben_prtt_enrt_rslt_f rslt,
3601: epe.pl_typ_id,
3602: epe.per_in_ler_id
3603: from
3604: ben_bnft_pool_rlovr_rqmt_f prr,
3605: ben_per_in_ler pil,
3606: ben_elig_per_elctbl_chc epe,
3607: ben_enrt_bnft enb,
3608: ben_enrt_rt ecr,
3609: ben_prtt_rt_val prv,
3655: nvl(sum(cash_recd_val),0),
3656: nvl(sum(decode(prtt_ro_of_unusd_amt_flag,'Y',used_val,0)),0) -- rollovers
3657: from ben_bnft_prvdd_ldgr_f bpl,
3658: ben_elig_per_elctbl_chc epe_flex,
3659: ben_per_in_ler pil_flex,
3660: ben_per_in_ler pil_flex1
3661: where p_effective_date between
3662: bpl.effective_start_date and bpl.effective_end_date and
3663: bpl.business_group_id=p_business_group_id and
3656: nvl(sum(decode(prtt_ro_of_unusd_amt_flag,'Y',used_val,0)),0) -- rollovers
3657: from ben_bnft_prvdd_ldgr_f bpl,
3658: ben_elig_per_elctbl_chc epe_flex,
3659: ben_per_in_ler pil_flex,
3660: ben_per_in_ler pil_flex1
3661: where p_effective_date between
3662: bpl.effective_start_date and bpl.effective_end_date and
3663: bpl.business_group_id=p_business_group_id and
3664: bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
3686: nvl(sum(cash_recd_val),0),
3687: nvl(sum(decode(prtt_ro_of_unusd_amt_flag,'Y',used_val,0)),0) -- rollovers
3688: from ben_bnft_prvdd_ldgr_f bpl,
3689: ben_elig_per_elctbl_chc epe_flex,
3690: ben_per_in_ler pil_flex,
3691: ben_per_in_ler pil_flex1
3692: where p_effective_date between
3693: bpl.effective_start_date and bpl.effective_end_date and
3694: bpl.business_group_id=p_business_group_id and
3687: nvl(sum(decode(prtt_ro_of_unusd_amt_flag,'Y',used_val,0)),0) -- rollovers
3688: from ben_bnft_prvdd_ldgr_f bpl,
3689: ben_elig_per_elctbl_chc epe_flex,
3690: ben_per_in_ler pil_flex,
3691: ben_per_in_ler pil_flex1
3692: where p_effective_date between
3693: bpl.effective_start_date and bpl.effective_end_date and
3694: bpl.business_group_id=p_business_group_id and
3695: bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
3715: bpl.object_version_number,
3716: bpl.effective_start_date
3717: from ben_bnft_prvdd_ldgr_f bpl,
3718: ben_elig_per_elctbl_chc epe_flex,
3719: ben_per_in_ler pil_flex,
3720: ben_per_in_ler pil_flex1
3721: where p_effective_date between
3722: bpl.effective_start_date and bpl.effective_end_date and
3723: bpl.business_group_id=p_business_group_id and
3716: bpl.effective_start_date
3717: from ben_bnft_prvdd_ldgr_f bpl,
3718: ben_elig_per_elctbl_chc epe_flex,
3719: ben_per_in_ler pil_flex,
3720: ben_per_in_ler pil_flex1
3721: where p_effective_date between
3722: bpl.effective_start_date and bpl.effective_end_date and
3723: bpl.business_group_id=p_business_group_id and
3724: bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
4286: pel.acty_ref_perd_cd,
4287: pen.prtt_enrt_rslt_id,
4288: pen.bnft_amt,
4289: pen.object_version_number
4290: from ben_per_in_ler pil,
4291: ben_elig_per_elctbl_chc epe,
4292: ben_pil_elctbl_chc_popl pel,
4293: ben_enrt_rt ecr1,
4294: ben_enrt_rt ecr2,
4543: nvl(sum(cash_recd_val),0) cash_recd_total,
4544: pil_flex.ler_id
4545: from ben_bnft_prvdd_ldgr_f bpl,
4546: ben_elig_per_elctbl_chc epe_flex,
4547: ben_per_in_ler pil_flex,
4548: ben_per_in_ler pil_flex1
4549: where p_effective_date between
4550: bpl.effective_start_date and bpl.effective_end_date and
4551: bpl.business_group_id=p_business_group_id and
4544: pil_flex.ler_id
4545: from ben_bnft_prvdd_ldgr_f bpl,
4546: ben_elig_per_elctbl_chc epe_flex,
4547: ben_per_in_ler pil_flex,
4548: ben_per_in_ler pil_flex1
4549: where p_effective_date between
4550: bpl.effective_start_date and bpl.effective_end_date and
4551: bpl.business_group_id=p_business_group_id and
4552: bpl.bnft_prvdr_pool_id is not null and
4580: -- get the acty-base-rt-id from the enrt-rt FLXCR row.
4581: cursor c_cash_abr is
4582: select ecr.acty_base_rt_id,
4583: epe.elig_per_elctbl_chc_id
4584: from ben_per_in_ler pil,
4585: ben_elig_per_elctbl_chc epe,
4586: ben_enrt_rt ecr
4587: where pil.per_in_ler_id=p_per_in_ler_id and
4588: pil.business_group_id=p_business_group_id and
4636: bpl.cash_recd_val,
4637: bpl.object_version_number,
4638: bpl.effective_start_date
4639: from ben_bnft_prvdd_ldgr_f bpl,
4640: ben_per_in_ler pil
4641: where bpl.bnft_prvdr_pool_id=l_ledger_totals.bnft_prvdr_pool_id
4642: and bpl.business_group_id=p_business_group_id
4643: and bpl.acty_base_rt_id = l_acty_base_rt_id
4644: and bpl.prtt_enrt_rslt_id=g_credit_pool_result_id
4663: --
4664: cursor c_get_ldgr(p_acty_base_rt_id in number) is
4665: select bpl.used_val
4666: from ben_bnft_prvdd_ldgr_f bpl,
4667: ben_per_in_ler pil
4668: where p_effective_date between
4669: bpl.effective_start_date and
4670: bpl.effective_end_date
4671: and bpl.acty_base_rt_id = p_acty_base_rt_id
5268: AND petc.effective_end_date;
5269: --
5270: cursor c_pil is
5271: select ler_id
5272: from ben_per_in_ler pil
5273: where pil.per_in_ler_id = p_ended_per_in_ler_id;
5274: --
5275: l_lee_rsn_id number := null ;
5276: --
5446: bpl.acty_base_rt_id,
5447: bpl.prtt_ro_of_unusd_amt_flag,
5448: bpl.used_val
5449: from ben_bnft_prvdd_ldgr_f bpl,
5450: ben_per_in_ler pil
5451: where bpl.bnft_prvdr_pool_id = p_bnft_prvdr_pool_id
5452: and bpl.prtt_enrt_rslt_id = p_flex_rslt_id
5453: and bpl.business_group_id = p_business_group_id
5454: and p_effective_date between
5710: nvl(sum(decode(bpl.prtt_ro_of_unusd_amt_flag,
5711: 'Y',bpl.used_val,
5712: 0)),0) -- rollovers
5713: from ben_bnft_prvdd_ldgr_f bpl,
5714: ben_per_in_ler pil
5715: where p_effective_date between
5716: bpl.effective_start_date and bpl.effective_end_date and
5717: bpl.business_group_id=p_business_group_id and
5718: bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
5734: bpl.used_val,
5735: bpl.object_version_number,
5736: bpl.effective_start_date
5737: from ben_bnft_prvdd_ldgr_f bpl,
5738: ben_per_in_ler pil
5739: where p_effective_date between
5740: bpl.effective_start_date and bpl.effective_end_date and
5741: bpl.business_group_id=p_business_group_id and
5742: bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
6091: nvl(sum(frftd_val),0) tot_frftd,
6092: nvl(sum(cash_recd_val),0) tot_cash,
6093: pil.ler_id
6094: from ben_bnft_prvdd_ldgr_f bpl,
6095: ben_per_in_ler pil
6096: where bpl.bnft_prvdr_pool_id = c_bnft_prvdr_pool_id
6097: and bpl.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
6098: and c_effective_date
6099: between bpl.effective_start_date and bpl.effective_end_date
6108: --
6109: cursor c_bpl is
6110: select bpl.acty_base_rt_id
6111: from ben_bnft_prvdd_ldgr_f bpl,
6112: ben_per_in_ler pil
6113: where bpl.bnft_prvdr_pool_id = p_bnft_prvdr_pool_id
6114: and bpl.prtt_enrt_rslt_id = p_flex_rslt_id
6115: and bpl.prvdd_val is not null
6116: and bpl.business_group_id = p_business_group_id
6799: bpl.prtt_enrt_rslt_id
6800: from ben_prtt_enrt_rslt_f enrt_pen,
6801: ben_prtt_rt_val enrt_prv,
6802: ben_bnft_prvdd_ldgr_f bpl,
6803: ben_per_in_ler pil,
6804: ben_prtt_enrt_rslt_f flex_pen
6805: where enrt_pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6806: and enrt_pen.prtt_enrt_rslt_stat_cd is null
6807: and enrt_pen.enrt_cvg_thru_dt = hr_api.g_eot
6842: bpl.prtt_enrt_rslt_id,
6843: bpp.uses_net_crs_mthd_flag
6844: from ben_prtt_enrt_rslt_f flex_pen,
6845: ben_bnft_prvdd_ldgr_f bpl,
6846: ben_per_in_ler pil,
6847: ben_bnft_prvdr_pool_f bpp
6848: where flex_pen.person_id = p_person_id
6849: and flex_pen.pgm_id = p_pgm_id
6850: and flex_pen.prtt_enrt_rslt_stat_cd is null
6867: --
6868: cursor c_frftd_bpl(v_pool_id in number) is
6869: select bpl.frftd_val
6870: from ben_bnft_prvdd_ldgr_f bpl,
6871: ben_per_in_ler pil
6872: where bpl.bnft_prvdr_pool_id = v_pool_id
6873: and bpl.prtt_enrt_rslt_id = l_flex_rslt_id
6874: and bpl.business_group_id = p_business_group_id
6875: and bpl.frftd_val is not null