[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
1097: bpl.prvdd_val,
1098: bpl.object_version_number,
1099: bpl.effective_start_date
1100: from ben_bnft_prvdd_ldgr_f bpl,
1101: ben_per_in_ler pil
1102: where bpl.bnft_prvdr_pool_id=p_epe_rec.bnft_prvdr_pool_id
1103: and bpl.business_group_id=p_epe_rec.business_group_id
1104: and bpl.acty_base_rt_id = l_acty_base_rt_id
1105: and bpl.prtt_enrt_rslt_id=g_credit_pool_result_id
1386: bpl.used_val,
1387: bpl.object_version_number,
1388: bpl.effective_start_date
1389: from ben_bnft_prvdd_ldgr_f bpl,
1390: ben_per_in_ler pil
1391: where bpl.bnft_prvdr_pool_id=p_enrt_rt_rec.decr_bnft_prvdr_pool_id
1392: and bpl.business_group_id=p_epe_rec.business_group_id
1393: and bpl.acty_base_rt_id=p_enrt_rt_rec.acty_base_rt_id
1394: and bpl.prtt_enrt_rslt_id=g_credit_pool_result_id
1564: bpl.object_version_number,
1565: decode(enb.enrt_bnft_id, null,
1566: ecr2.rt_usg_cd,ecr1.rt_usg_cd) rt_usg_cd
1567: from ben_elig_per_elctbl_chc epe,
1568: ben_per_in_ler pil,
1569: ben_enrt_rt ecr1,
1570: ben_enrt_rt ecr2,
1571: ben_enrt_bnft enb,
1572: ben_bnft_prvdd_ldgr_f bpl,
1570: ben_enrt_rt ecr2,
1571: ben_enrt_bnft enb,
1572: ben_bnft_prvdd_ldgr_f bpl,
1573: ben_elig_per_elctbl_chc epe_flex,
1574: ben_per_in_ler pil_flex
1575: where pil.person_id=p_person_id and
1576: pil.business_group_id=p_business_group_id and
1577: pil.per_in_ler_stat_cd='STRTD' and
1578: pil.per_in_ler_id=epe.per_in_ler_id and
1606:
1607: cursor c_ldgr_exist(l_ldgr_id number,l_per_in_ler_id number) is
1608: select 'x'
1609: from ben_bnft_prvdd_ldgr_f bpl,
1610: ben_per_in_ler pil
1611: where bpl.bnft_prvdd_ldgr_id = l_ldgr_id
1612: and bpl.per_in_ler_id = l_per_in_ler_id
1613: -- UK change : Bug 1634870
1614: and bpl.per_in_ler_id = pil.per_in_ler_id
1653: bpl.prtt_ro_of_unusd_amt_flag,
1654: bpl.object_version_number,
1655: bpl.effective_start_date
1656: from ben_bnft_prvdd_ldgr_f bpl,
1657: ben_per_in_ler pil
1658: where bpl.acty_base_rt_id = l_acty_base_rt_id
1659: and bpl.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
1660: -- and bpl.effective_end_date = hr_api.g_eot
1661: and p_effective_date between bpl.effective_start_date
1680: epe.business_group_id,
1681: epe.per_in_ler_id
1682: from ben_elig_per_elctbl_chc epe1,
1683: ben_elig_per_elctbl_chc epe,
1684: ben_per_in_ler pil
1685: where epe1.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id and
1686: epe1.business_group_id=p_business_group_id and
1687: epe1.pgm_id = epe.pgm_id and
1688: epe1.per_in_ler_id = epe.per_in_ler_id and
1695: select bpl.bnft_prvdd_ldgr_id,
1696: bpl.object_version_number,
1697: effective_start_date
1698: from ben_bnft_prvdd_ldgr_f bpl,
1699: ben_per_in_ler pil
1700: where bpl.bnft_prvdr_pool_id = l_epe.bnft_prvdr_pool_id
1701: and bpl.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
1702: and bpl.acty_base_rt_id = p_acty_base_rt_id
1703: --and bpl.effective_end_date = hr_api.g_eot
2044: bpl.object_version_number,
2045: decode(enb.enrt_bnft_id,null,
2046: ecr2.rt_usg_cd,ecr1.rt_usg_cd) rt_usg_cd
2047: from ben_elig_per_elctbl_chc epe,
2048: ben_per_in_ler pil,
2049: ben_enrt_rt ecr1,
2050: ben_enrt_rt ecr2,
2051: ben_enrt_bnft enb,
2052: ben_bnft_prvdd_ldgr_f bpl,
2050: ben_enrt_rt ecr2,
2051: ben_enrt_bnft enb,
2052: ben_bnft_prvdd_ldgr_f bpl,
2053: ben_elig_per_elctbl_chc epe_flex,
2054: ben_per_in_ler pil_flex,
2055: ben_per_in_ler pil_flex1
2056: where pil.per_in_ler_id = p_per_in_ler_id and
2057: pil.business_group_id=p_business_group_id and
2058: epe.bnft_prvdr_pool_id is not null and
2051: ben_enrt_bnft enb,
2052: ben_bnft_prvdd_ldgr_f bpl,
2053: ben_elig_per_elctbl_chc epe_flex,
2054: ben_per_in_ler pil_flex,
2055: ben_per_in_ler pil_flex1
2056: where pil.per_in_ler_id = p_per_in_ler_id and
2057: pil.business_group_id=p_business_group_id and
2058: epe.bnft_prvdr_pool_id is not null and
2059: pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
2191: epe.pl_id,
2192: per.enrt_cvg_strt_dt,
2193: per.per_in_ler_id chc_pen_id
2194: from ben_elig_per_elctbl_chc epe,
2195: ben_per_in_ler pil,
2196: ben_prtt_enrt_rslt_f per
2197: where epe.comp_lvl_cd = 'PLANFC' and
2198: epe.per_in_ler_id = p_per_in_ler_id and
2199: per.prtt_enrt_rslt_stat_cd is null and
2664: bpl.frftd_val,
2665: bpl.object_version_number,
2666: bpl.effective_start_date
2667: from ben_bnft_prvdd_ldgr_f bpl,
2668: ben_per_in_ler pil
2669: where bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id
2670: and bpl.business_group_id=p_business_group_id
2671: and bpl.acty_base_rt_id = p_acty_base_rt_id
2672: and bpl.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
2684: bpl.cash_recd_val,
2685: bpl.object_version_number,
2686: bpl.effective_start_date
2687: from ben_bnft_prvdd_ldgr_f bpl,
2688: ben_per_in_ler pil
2689: where bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id
2690: and bpl.business_group_id=p_business_group_id
2691: -- and bpl.acty_base_rt_id = p_acty_base_rt_id /* Bug No 4714939 */
2692: and bpl.prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
2924:
2925: cursor c_cash_abr is
2926: select bpl.acty_base_rt_id
2927: from ben_bnft_prvdd_ldgr_f bpl,
2928: ben_per_in_ler pil,
2929: ben_acty_base_rt_f abr -- Bug 4613270
2930: where bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id
2931: and bpl.business_group_id=p_business_group_id
2932: and bpl.prtt_enrt_rslt_id=g_credit_pool_result_id
2943: -- Bug 5447507
2944: CURSOR c_cash_abr_from_epe
2945: IS
2946: SELECT ecr.acty_base_rt_id
2947: FROM ben_per_in_ler pil,
2948: ben_elig_per_elctbl_chc epe,
2949: ben_enrt_rt ecr
2950: WHERE pil.person_id = p_person_id
2951: AND pil.business_group_id = p_business_group_id
2969: bpl.cash_recd_val,
2970: bpl.object_version_number,
2971: bpl.effective_start_date
2972: from ben_bnft_prvdd_ldgr_f bpl,
2973: ben_per_in_ler pil
2974: where bpl.bnft_prvdr_pool_id = c_bnft_prvdr_pool_id
2975: and bpl.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
2976: and bpl.cash_recd_val is not null
2977: and c_effective_date
3346: epe.pl_typ_id,
3347: epe.per_in_ler_id
3348: from
3349: ben_bnft_pool_rlovr_rqmt_f prr,
3350: ben_per_in_ler pil,
3351: ben_elig_per_elctbl_chc epe,
3352: ben_enrt_rt ecr,
3353: ben_prtt_rt_val prv,
3354: ben_prtt_enrt_rslt_f rslt,
3422: epe.pl_typ_id,
3423: epe.per_in_ler_id
3424: from
3425: ben_bnft_pool_rlovr_rqmt_f prr,
3426: ben_per_in_ler pil,
3427: ben_elig_per_elctbl_chc epe,
3428: ben_enrt_bnft enb,
3429: ben_enrt_rt ecr,
3430: ben_prtt_rt_val prv,
3476: nvl(sum(cash_recd_val),0),
3477: nvl(sum(decode(prtt_ro_of_unusd_amt_flag,'Y',used_val,0)),0) -- rollovers
3478: from ben_bnft_prvdd_ldgr_f bpl,
3479: ben_elig_per_elctbl_chc epe_flex,
3480: ben_per_in_ler pil_flex,
3481: ben_per_in_ler pil_flex1
3482: where p_effective_date between
3483: bpl.effective_start_date and bpl.effective_end_date and
3484: bpl.business_group_id=p_business_group_id and
3477: nvl(sum(decode(prtt_ro_of_unusd_amt_flag,'Y',used_val,0)),0) -- rollovers
3478: from ben_bnft_prvdd_ldgr_f bpl,
3479: ben_elig_per_elctbl_chc epe_flex,
3480: ben_per_in_ler pil_flex,
3481: ben_per_in_ler pil_flex1
3482: where p_effective_date between
3483: bpl.effective_start_date and bpl.effective_end_date and
3484: bpl.business_group_id=p_business_group_id and
3485: bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
3507: nvl(sum(cash_recd_val),0),
3508: nvl(sum(decode(prtt_ro_of_unusd_amt_flag,'Y',used_val,0)),0) -- rollovers
3509: from ben_bnft_prvdd_ldgr_f bpl,
3510: ben_elig_per_elctbl_chc epe_flex,
3511: ben_per_in_ler pil_flex,
3512: ben_per_in_ler pil_flex1
3513: where p_effective_date between
3514: bpl.effective_start_date and bpl.effective_end_date and
3515: bpl.business_group_id=p_business_group_id and
3508: nvl(sum(decode(prtt_ro_of_unusd_amt_flag,'Y',used_val,0)),0) -- rollovers
3509: from ben_bnft_prvdd_ldgr_f bpl,
3510: ben_elig_per_elctbl_chc epe_flex,
3511: ben_per_in_ler pil_flex,
3512: ben_per_in_ler pil_flex1
3513: where p_effective_date between
3514: bpl.effective_start_date and bpl.effective_end_date and
3515: bpl.business_group_id=p_business_group_id and
3516: bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
3536: bpl.object_version_number,
3537: bpl.effective_start_date
3538: from ben_bnft_prvdd_ldgr_f bpl,
3539: ben_elig_per_elctbl_chc epe_flex,
3540: ben_per_in_ler pil_flex,
3541: ben_per_in_ler pil_flex1
3542: where p_effective_date between
3543: bpl.effective_start_date and bpl.effective_end_date and
3544: bpl.business_group_id=p_business_group_id and
3537: bpl.effective_start_date
3538: from ben_bnft_prvdd_ldgr_f bpl,
3539: ben_elig_per_elctbl_chc epe_flex,
3540: ben_per_in_ler pil_flex,
3541: ben_per_in_ler pil_flex1
3542: where p_effective_date between
3543: bpl.effective_start_date and bpl.effective_end_date and
3544: bpl.business_group_id=p_business_group_id and
3545: bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
4107: pel.acty_ref_perd_cd,
4108: pen.prtt_enrt_rslt_id,
4109: pen.bnft_amt,
4110: pen.object_version_number
4111: from ben_per_in_ler pil,
4112: ben_elig_per_elctbl_chc epe,
4113: ben_pil_elctbl_chc_popl pel,
4114: ben_enrt_rt ecr1,
4115: ben_enrt_rt ecr2,
4364: nvl(sum(cash_recd_val),0) cash_recd_total,
4365: pil_flex.ler_id
4366: from ben_bnft_prvdd_ldgr_f bpl,
4367: ben_elig_per_elctbl_chc epe_flex,
4368: ben_per_in_ler pil_flex,
4369: ben_per_in_ler pil_flex1
4370: where p_effective_date between
4371: bpl.effective_start_date and bpl.effective_end_date and
4372: bpl.business_group_id=p_business_group_id and
4365: pil_flex.ler_id
4366: from ben_bnft_prvdd_ldgr_f bpl,
4367: ben_elig_per_elctbl_chc epe_flex,
4368: ben_per_in_ler pil_flex,
4369: ben_per_in_ler pil_flex1
4370: where p_effective_date between
4371: bpl.effective_start_date and bpl.effective_end_date and
4372: bpl.business_group_id=p_business_group_id and
4373: bpl.bnft_prvdr_pool_id is not null and
4401: -- get the acty-base-rt-id from the enrt-rt FLXCR row.
4402: cursor c_cash_abr is
4403: select ecr.acty_base_rt_id,
4404: epe.elig_per_elctbl_chc_id
4405: from ben_per_in_ler pil,
4406: ben_elig_per_elctbl_chc epe,
4407: ben_enrt_rt ecr
4408: where pil.per_in_ler_id=p_per_in_ler_id and
4409: pil.business_group_id=p_business_group_id and
4457: bpl.cash_recd_val,
4458: bpl.object_version_number,
4459: bpl.effective_start_date
4460: from ben_bnft_prvdd_ldgr_f bpl,
4461: ben_per_in_ler pil
4462: where bpl.bnft_prvdr_pool_id=l_ledger_totals.bnft_prvdr_pool_id
4463: and bpl.business_group_id=p_business_group_id
4464: and bpl.acty_base_rt_id = l_acty_base_rt_id
4465: and bpl.prtt_enrt_rslt_id=g_credit_pool_result_id
4484: --
4485: cursor c_get_ldgr(p_acty_base_rt_id in number) is
4486: select bpl.used_val
4487: from ben_bnft_prvdd_ldgr_f bpl,
4488: ben_per_in_ler pil
4489: where p_effective_date between
4490: bpl.effective_start_date and
4491: bpl.effective_end_date
4492: and bpl.acty_base_rt_id = p_acty_base_rt_id
5091: AND petc.effective_end_date;
5092: --
5093: cursor c_pil is
5094: select ler_id
5095: from ben_per_in_ler pil
5096: where pil.per_in_ler_id = p_ended_per_in_ler_id;
5097: --
5098: l_lee_rsn_id number := null ;
5099: --
5269: bpl.acty_base_rt_id,
5270: bpl.prtt_ro_of_unusd_amt_flag,
5271: bpl.used_val
5272: from ben_bnft_prvdd_ldgr_f bpl,
5273: ben_per_in_ler pil
5274: where bpl.bnft_prvdr_pool_id = p_bnft_prvdr_pool_id
5275: and bpl.prtt_enrt_rslt_id = p_flex_rslt_id
5276: and bpl.business_group_id = p_business_group_id
5277: and p_effective_date between
5533: nvl(sum(decode(bpl.prtt_ro_of_unusd_amt_flag,
5534: 'Y',bpl.used_val,
5535: 0)),0) -- rollovers
5536: from ben_bnft_prvdd_ldgr_f bpl,
5537: ben_per_in_ler pil
5538: where p_effective_date between
5539: bpl.effective_start_date and bpl.effective_end_date and
5540: bpl.business_group_id=p_business_group_id and
5541: bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
5557: bpl.used_val,
5558: bpl.object_version_number,
5559: bpl.effective_start_date
5560: from ben_bnft_prvdd_ldgr_f bpl,
5561: ben_per_in_ler pil
5562: where p_effective_date between
5563: bpl.effective_start_date and bpl.effective_end_date and
5564: bpl.business_group_id=p_business_group_id and
5565: bpl.bnft_prvdr_pool_id=p_bnft_prvdr_pool_id and
5914: nvl(sum(frftd_val),0) tot_frftd,
5915: nvl(sum(cash_recd_val),0) tot_cash,
5916: pil.ler_id
5917: from ben_bnft_prvdd_ldgr_f bpl,
5918: ben_per_in_ler pil
5919: where bpl.bnft_prvdr_pool_id = c_bnft_prvdr_pool_id
5920: and bpl.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
5921: and c_effective_date
5922: between bpl.effective_start_date and bpl.effective_end_date
5931: --
5932: cursor c_bpl is
5933: select bpl.acty_base_rt_id
5934: from ben_bnft_prvdd_ldgr_f bpl,
5935: ben_per_in_ler pil
5936: where bpl.bnft_prvdr_pool_id = p_bnft_prvdr_pool_id
5937: and bpl.prtt_enrt_rslt_id = p_flex_rslt_id
5938: and bpl.prvdd_val is not null
5939: and bpl.business_group_id = p_business_group_id
6622: bpl.prtt_enrt_rslt_id
6623: from ben_prtt_enrt_rslt_f enrt_pen,
6624: ben_prtt_rt_val enrt_prv,
6625: ben_bnft_prvdd_ldgr_f bpl,
6626: ben_per_in_ler pil,
6627: ben_prtt_enrt_rslt_f flex_pen
6628: where enrt_pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6629: and enrt_pen.prtt_enrt_rslt_stat_cd is null
6630: and enrt_pen.enrt_cvg_thru_dt = hr_api.g_eot
6665: bpl.prtt_enrt_rslt_id,
6666: bpp.uses_net_crs_mthd_flag
6667: from ben_prtt_enrt_rslt_f flex_pen,
6668: ben_bnft_prvdd_ldgr_f bpl,
6669: ben_per_in_ler pil,
6670: ben_bnft_prvdr_pool_f bpp
6671: where flex_pen.person_id = p_person_id
6672: and flex_pen.pgm_id = p_pgm_id
6673: and flex_pen.prtt_enrt_rslt_stat_cd is null
6690: --
6691: cursor c_frftd_bpl(v_pool_id in number) is
6692: select bpl.frftd_val
6693: from ben_bnft_prvdd_ldgr_f bpl,
6694: ben_per_in_ler pil
6695: where bpl.bnft_prvdr_pool_id = v_pool_id
6696: and bpl.prtt_enrt_rslt_id = l_flex_rslt_id
6697: and bpl.business_group_id = p_business_group_id
6698: and bpl.frftd_val is not null