[Home] [Help]
116: 13-Feb-00 pbodla 115.50 - Put the version 115.48 with changes in 115.49
117: 30-Mar-01 kmahendr 115.51 - Bug#1708166 - when comp.object is replaced in the
118: subsequent life event, the flex credit entry for
119: the deenrolled comp.object is not being deleted
120: from ben_bnft_prvdd_ldgr_f with the result the
121: used value is wrongly shown - added codes to
122: cleanup_invalid_ledger_entries
123: 09-Apr-01 pbodla 115.52 - Bug 1711831 - While getting the activity base rate
124: for the provided credits choice row for the pool
1114: select bpl.bnft_prvdd_ldgr_id,
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
1238: end if;
1239: */
1240: Get_DT_Upd_Mode
1241: (p_effective_date => p_effective_date,
1242: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
1243: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
1244: p_base_key_value => l_bnft_prvdd_ldgr_id,
1245: p_mode => l_datetrack_mode);
1246: hr_utility.set_location('UPDATING LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),70);
1404: bpl.per_in_ler_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
1485: l_datetrack_mode:=hr_api.g_update;
1486: end if;*/
1487: Get_DT_Upd_Mode
1488: (p_effective_date => p_effective_date,
1489: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
1490: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
1491: p_base_key_value => p_bnft_prvdd_ldgr_id,
1492: p_mode => l_datetrack_mode);
1493: -- bug#2210322 - if there is no update on result row then no need to update ledger row
1552: l_effective_end_date date;
1553: l_object_version_number number;
1554: l_prtt_enrt_rslt_id number;
1555: l_acty_base_rt_id number;
1556: l_prtt_ro_of_unusd_amt_flag ben_bnft_prvdd_ldgr_f.prtt_ro_of_unusd_amt_flag%type;
1557: l_delete_bpl boolean default true;
1558: l_exists varchar2(1);
1559: --
1560: -- this cursor needs some explaination
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,
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
1624: pen.effective_start_date and pen.effective_end_date;
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
1692: select bpl.bnft_prvdd_ldgr_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
1754: cursor c_bnft_prvdd_ldgr_2 (p_acty_base_rt_id number) is
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
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,
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
2794: select bpl.bnft_prvdd_ldgr_id,
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
2814: select bpl.bnft_prvdd_ldgr_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 */
2870: end if;
2871: */
2872: Get_DT_Upd_Mode
2873: (p_effective_date => p_effective_date,
2874: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
2875: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
2876: p_base_key_value => l_bnft_prvdd_ldgr_id,
2877: p_mode => l_datetrack_mode);
2878: hr_utility.set_location('UPDATING LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),20);
2944: --end if;
2945: --
2946: Get_DT_Upd_Mode
2947: (p_effective_date => p_effective_date,
2948: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
2949: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
2950: p_base_key_value => l_bnft_prvdd_ldgr_id,
2951: p_mode => l_datetrack_mode);
2952: hr_utility.set_location('l_cash_recd_val is not null '||to_char(l_bnft_prvdd_ldgr_id),20);
3015: if l_cash_recd_val is not null and l_cash_recd_val < 0 then
3016: --
3017: Get_DT_Upd_Mode
3018: (p_effective_date => p_effective_date,
3019: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
3020: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
3021: p_base_key_value => l_bnft_prvdd_ldgr_id,
3022: p_mode => l_datetrack_mode);
3023: hr_utility.set_location('l_cash_recd_val is not null '||to_char(l_bnft_prvdd_ldgr_id),20.1);
3102: l_pgm_id number;
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
3147: select bpl.bnft_prvdd_ldgr_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
3407: l_datetrack_mode:=hr_api.g_update;
3408: end if; */
3409: Get_DT_Upd_Mode
3410: (p_effective_date => p_effective_date,
3411: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
3412: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
3413: p_base_key_value => l_bnft_prvdd_ldgr_id,
3414: p_mode => l_datetrack_mode);
3415: hr_utility.set_location('UPDATING LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),340);
3653: nvl(sum(prvdd_val),0),
3654: nvl(sum(decode(prtt_ro_of_unusd_amt_flag,'N',used_val,0)),0),-- non rollovers
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
3684: nvl(sum(prvdd_val),0),
3685: nvl(sum(decode(prtt_ro_of_unusd_amt_flag,'N',used_val,0)),0),-- non rollovers
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
3713: bnft_prvdd_ldgr_id,
3714: bpl.used_val,
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
3934: ;
3935: if c_old_ledger%found then
3936: Get_DT_Upd_Mode
3937: (p_effective_date => p_effective_date,
3938: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
3939: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
3940: p_base_key_value => l_bnft_prvdd_ldgr_id,
3941: p_mode => l_datetrack_mode);
3942: l_balance := 0;
4013: end if;
4014: */
4015: Get_DT_Upd_Mode
4016: (p_effective_date => p_effective_date,
4017: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
4018: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
4019: p_base_key_value => l_bnft_prvdd_ldgr_id,
4020: p_mode => l_datetrack_mode);
4021: hr_utility.set_location('UPDATING LDGR ID='||to_char(l_bnft_prvdd_ldgr_id),120);
4541: nvl(sum(used_val),0) used_total, -- include rollovers
4542: nvl(sum(frftd_val),0) frftd_total,
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
4597: -- Bug 1711831
4598: --
4599: cursor c_get_pool_abr is
4600: select abr.acty_base_rt_id
4601: from ben_bnft_prvdd_ldgr_f bpl,
4602: ben_acty_base_rt_f abr
4603: where bpl.bnft_prvdr_pool_id = l_ledger_totals.bnft_prvdr_pool_id and
4604: abr.rt_usg_cd = 'FLXCR' and
4605: bpl.business_group_id = abr.business_group_id and
4635: select bpl.bnft_prvdd_ldgr_id,
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
4662: ;
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
5445: bpl.effective_start_date,
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
5709: nvl(sum(bpl.cash_recd_val),0),
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
5733: bnft_prvdd_ldgr_id,
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
6005: end if;
6006: */
6007: Get_DT_Upd_Mode
6008: (p_effective_date => p_effective_date,
6009: p_base_table_name => 'BEN_BNFT_PRVDD_LDGR_F',
6010: p_base_key_column => 'BNFT_PRVDD_LDGR_ID',
6011: p_base_key_value => l_bnft_prvdd_ldgr_id,
6012: p_mode => l_datetrack_mode);
6013: hr_utility.set_location('UPDATING LEDGER ID='||to_char(l_bnft_prvdd_ldgr_id),120);
6090: nvl(sum(bpl.used_val),0) tot_used,
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
6107: -- excess amounts ledger.(Credit Ledger)
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
6798: bpl.bnft_prvdr_pool_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
6841: bpl.prvdd_val,
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
6866: -- Cursor to get forfeited amount for a pool.
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
6938: select bnft_prvdd_ldgr_id,
6939: effective_start_date,
6940: effective_end_date,
6941: object_version_number
6942: from ben_bnft_prvdd_ldgr_f bpl
6943: where bnft_prvdd_ldgr_id = p_bpl_id
6944: --and effective_start_date < p_eff_dt
6945: order by effective_start_date;
6946: --