[Home] [Help]
485: 11-jan-06 ikasire 115.231 4938498 fixed c_prv2 cursor to exclude voided and backedout
486: records.
487: 16-Jan-06 abparekh 115.232 Bug 4954541 : If bnft_amt is nullified, create new enrollment
488: 10-Feb-06 kmahendr 115.233 Bug#5032364 - before delete prtt row, it
489: is captured in ben_le_clsn_n_rstr table
490: for backout purpose
491: 22-Mar-06 rtagarra 115.234 Bug#5099296 : Changed the sspndd_flag parameter passed to update_enrollment.
492: 23-Mar-06 kmahendr 115.235 Bug#5105122 - coverage date is recomputed
493: before create_enrollment
497: 17-jul-06 ssarkar 115.238 Bug 5375381 - ben_determine_activity_base_rt being called for interim.
498: 03-aug-06 ssarkar 115.239 bug 5417132 - passing comp_lvl_cd to update_enrollment call
499: 27-Sep-06 ikasired 115.240 Bug 5502202 fix for Interim Rule. We need to recompute the rates when
500: interim rule returs amounts.
501: 10-Oct-06 abparekh 115.241 Bug 5572484 : Take backup in BEN_LE_CLSN_N_RSTR only if the correction
502: is due to a new life event.
503: 11-Oct-06 ssarkar 115.242 Bug 5555269 - commented out fix of bug 5032364 as well 5572484
504: 12-Oct-06 gsehgal 115.243 Bug 5584813 - move the code to calculate rates to procedure
505: calc_rt_ann_rt_vals and also call this when cvg_mlt_cd='SAAEAR'
603: to cursor c_get_ended_result.
604: 01-May-12 stee 115.296 Bug 12583091 - Check for per_in_ler_id when
605: determining if enrollment is suspended.
606: 9-Nov-12 velvanop 115.297 Bug 14803995 : Do not insert future coverage records in backup table if the Life event is Unrestricted
607: 9-Mar-13 velvanop 115.298 Bug 16396102 : While inserting record into backup table ben_le_clsn_n_rstr for rate adjustment records 'BEN_PRTT_RT_VAL_ADJ', populate person_id
608: while inserting into the table
609: */
610:
611: -------------------------------------------------------------------------------
1732: --
1733: cursor c_prtt_rt_val_adj (p_per_in_ler_id number,
1734: p_prtt_rt_val_id number) is
1735: select null
1736: from ben_le_clsn_n_rstr
1737: where BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
1738: AND BKUP_TBL_ID = p_prtt_rt_val_id
1739: AND PER_IN_LER_ID = p_per_in_ler_id;
1740:
1751: and prv.prtt_rt_val_stat_cd is null
1752: and prv.acty_base_rt_id = c_acty_base_rt_id
1753: and prv.rt_end_dt <> hr_api.g_eot
1754: and not exists
1755: (select '1' from ben_le_clsn_n_rstr bkp
1756: where BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
1757: and BKUP_TBL_ID = c_prtt_rt_val_id
1758: and bkp.PER_IN_LER_ENDED_ID = c_per_in_ler_id
1759: and bkp.acty_base_rt_id = c_acty_base_rt_id);
3723: hr_utility.set_location('inserting into backup table ',1999);
3724: hr_utility.set_location('l_enrt_rt.acty_base_rt_id '||l_enrt_rt.acty_base_rt_id,1999);
3725: hr_utility.set_location('l_future_end_rec.per_in_ler_id '||l_future_end_rec.per_in_ler_id,1999);
3726: hr_utility.set_location('l_future_end_rec.rt_end_dt '||l_future_end_rec.rt_end_dt,1999);
3727: insert into BEN_LE_CLSN_N_RSTR (
3728: person_id,
3729: BKUP_TBL_TYP_CD,
3730: BKUP_TBL_ID,
3731: per_in_ler_id,
3831: hr_utility.set_location('Adjusting rate '||l_enrt_rt.rt_strt_dt,111);
3832: open c_prtt_rt_val_adj(p_per_in_ler_id,l_pen.prtt_rt_val_id);
3833: fetch c_prtt_rt_val_adj into l_exists;
3834: if c_prtt_rt_val_adj%notfound then
3835: insert into BEN_LE_CLSN_N_RSTR (
3836: BKUP_TBL_TYP_CD,
3837: BKUP_TBL_ID,
3838: per_in_ler_id,
3839: person_id,
4015: hr_utility.set_location('Adjusting rate '||l_enrt_rt.rt_strt_dt,111);
4016: open c_prtt_rt_val_adj(p_per_in_ler_id,l_pen.prtt_rt_val_id);
4017: fetch c_prtt_rt_val_adj into l_exists;
4018: if c_prtt_rt_val_adj%notfound then
4019: insert into BEN_LE_CLSN_N_RSTR (
4020: BKUP_TBL_TYP_CD,
4021: BKUP_TBL_ID,
4022: per_in_ler_id,
4023: person_id,
4960: and pen2.effective_end_date = hr_api.g_eot
4961: and pen2.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
4962: and pen2.prtt_enrt_rslt_stat_cd is null)
4963: and not exists
4964: (select '1' from ben_le_clsn_n_rstr bkp where
4965: bkp.per_in_ler_id = pen.per_in_ler_id
4966: and bkp.effective_end_date = pen.effective_end_date
4967: and bkp.BKUP_TBL_ID = pen.prtt_enrt_rslt_id
4968: and bkp.enrt_cvg_thru_dt = pen.enrt_cvg_thru_dt
5304: and ptip.effective_end_date;
5305: --
5306: cursor c_prtt_enrt_rslt_adj (p_prtt_enrt_rslt_id number) is
5307: select null
5308: from ben_le_clsn_n_rstr leclr, ben_elig_per_elctbl_chc epe
5309: where leclr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_ADJ'
5310: AND leclr.BKUP_TBL_ID = p_prtt_enrt_rslt_id
5311: AND leclr.PER_IN_LER_ID = epe.per_in_ler_id
5312: AND epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
5336: l_get_prev_pil_enrt c_get_pil_enrt%rowtype;
5337: l_check_enrt_same_pil c_get_pil_enrt%rowtype;
5338: cursor c_bkup_pen_rec(p_pen_id number,p_pil_id number,p_person_id number,p_esd date,p_csd date) is
5339: SELECT bkup.*
5340: FROM ben_le_clsn_n_rstr bkup
5341: WHERE bkup.bkup_tbl_id = p_pen_id
5342: AND bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F_CORR'
5343: AND bkup.per_in_ler_id = p_pil_id
5344: AND bkup.person_id = p_person_id
6165: open c_enrt_rslt2;
6166: fetch c_enrt_rslt2 into l_enrt_rslt2;
6167: close c_enrt_rslt2;
6168: --
6169: -- Bug 5572484 : Take backup in BEN_LE_CLSN_N_RSTR only if the correction
6170: -- is due to a new life event. E.g. If you de-enrol in a life
6171: -- event and again enrol in the same life event, then we need
6172: -- not take backup of the de-enrolled PEN
6173: --
6197: open c_bkp_enrt_rslt_exist;
6198: fetch c_bkp_enrt_rslt_exist into l_bkp_enrt;
6199: if(c_bkp_enrt_rslt_exist%found) then
6200: hr_utility.set_location('inserting into backup table',11);
6201: insert into BEN_LE_CLSN_N_RSTR (
6202: BKUP_TBL_TYP_CD,
6203: COMP_LVL_CD,
6204: LCR_ATTRIBUTE16,
6205: LCR_ATTRIBUTE17,
7187: --
7188: open c_prtt_enrt_rslt_adj(l_get_enrt_rslts_for_pen.prtt_enrt_rslt_id);
7189: fetch c_prtt_enrt_rslt_adj into l_exists;
7190: if c_prtt_enrt_rslt_adj%notfound then
7191: insert into BEN_LE_CLSN_N_RSTR (
7192: BKUP_TBL_TYP_CD,
7193: BKUP_TBL_ID,
7194: per_in_ler_id,
7195: person_id,