[Home] [Help]
348: l_sspnd_flag
349: 16 Sep 2004 mmudigon 115.108 Bug fix 3859152
350: 30-Sep-2004 hmani 115.109 If iRec then backout to VOIDD
351: 30-Nov-2004 kmahendr 115.110 Bug#3964234 - Modified cursor
352: c_BEN_LE_CLSN_N_RSTR_corr
353: 20-Dec-2004 tjesumic 115.111 cursor c_BEN_LE_CLSN_N_RSTR_dpnt modifued to
354: use table instead of view
355: 08-Feb-2005 tjesumic 115.112 copy_oly parameter added to copy the date to backup table
356: #4118315
349: 16 Sep 2004 mmudigon 115.108 Bug fix 3859152
350: 30-Sep-2004 hmani 115.109 If iRec then backout to VOIDD
351: 30-Nov-2004 kmahendr 115.110 Bug#3964234 - Modified cursor
352: c_BEN_LE_CLSN_N_RSTR_corr
353: 20-Dec-2004 tjesumic 115.111 cursor c_BEN_LE_CLSN_N_RSTR_dpnt modifued to
354: use table instead of view
355: 08-Feb-2005 tjesumic 115.112 copy_oly parameter added to copy the date to backup table
356: #4118315
357: 15-Feb-2005 kmahendr 115.113 Bug#4172989 - multi_row_actn parameter added
381: 07-oct-05 ssarkar 115.125 bug: 4615207 - Mulitple Rate chk to be performed only for GHR
382: 02-Jan-2006 abparekh 115.126 Bug 4919951 : Reset G_BACKOUT_FLAG in case of any exception
383: 10-Feb-2006 kmahendr 115.127 Bug#5032364-added delete_enrollment
384: to ben_prtt_enrt_rslt_F delete routine
385: 28-Feb-2006 kmahendr 115.128 Added cursor c_BEN_LE_CLSN_N_RSTR_del
386: 08-mar-2006 nhunur 115.129 skip pep, epo for cwb global backout.
387: 11-Apr-2006 rbingi 115.130 5148936: Added order by to cursor c_actn_item_for_past_pil
388: 27-apr-06 ssarkar 115.131 5187145 : added sub_query to cursor c_actn_item_for_past_pil
389: 06-Sep-2006 abparekh 115.132 Bug 5500864 : Added code for reinstatement of BPL records
403: 26-Jul-2007 sshetty 115.141 Bug 6216828, Fixed status code
404: issue in enrollment results table
405: 04-Sep-2007 swjain 115.142 Bug 6376239 : Made changes in delete_routine for
406: p_routine = 'BEN_BNFT_PRVDD_LDGR_F'
407: 07-Dec-2007 rtagarra 115.143 Bug 6489602 :Modified the cursor c_BEN_LE_CLSN_N_RSTR_corr.
408: 22-Feb-2008 rtagarra 115.144 6840074
409: 29-feb-2008 bachakra 115.146 Bug 6620291: Modified Cursor get_contacts_pils.
410: 03-mar-2008 bachakra 115.147 Bug 6632568: Modified Cursor c_corr_result_exist
411: so that when _corr result exisits for previous pil,
872: l_proc varchar2(72) := g_package||'restore_prev_pep';
873: --
874: cursor c_restore_pep( p_pil_id number ) IS
875: select *
876: from ben_le_clsn_n_rstr elig
877: where elig.per_in_ler_ended_id = p_pil_id
878: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_F_CORRECT' ;
879: --
880: cursor c_pep(p_elig_per_id number,p_effective_date date) IS
1078: close c_pep;
1079: --
1080: END LOOP;
1081: --
1082: --Now Delete the records from ben_le_clsn_n_rstr
1083: --
1084: delete from ben_le_clsn_n_rstr elig
1085: where elig.per_in_ler_ended_id = p_per_in_ler_id
1086: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_F_CORRECT' ;
1080: END LOOP;
1081: --
1082: --Now Delete the records from ben_le_clsn_n_rstr
1083: --
1084: delete from ben_le_clsn_n_rstr elig
1085: where elig.per_in_ler_ended_id = p_per_in_ler_id
1086: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_F_CORRECT' ;
1087: --
1088: hr_utility.set_location ('Leaving '||l_proc,10);
1096: l_proc varchar2(72) := g_package||'restore_cert_completion';
1097: --
1098: cursor c_restore_pcs( p_pil_id number) IS
1099: select pcs.*
1100: from ben_le_clsn_n_rstr pcs,
1101: ben_le_clsn_n_rstr pea
1102: where pcs.per_in_ler_ended_id = p_pil_id
1103: and pea.per_in_ler_ended_id = p_pil_id
1104: and pea.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_ACTN_F_UPD'
1097: --
1098: cursor c_restore_pcs( p_pil_id number) IS
1099: select pcs.*
1100: from ben_le_clsn_n_rstr pcs,
1101: ben_le_clsn_n_rstr pea
1102: where pcs.per_in_ler_ended_id = p_pil_id
1103: and pea.per_in_ler_ended_id = p_pil_id
1104: and pea.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_ACTN_F_UPD'
1105: and pcs.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_CTFN_PRVDD_F_UPD'
1219: close c_pcs;
1220: --
1221: END LOOP;
1222: --
1223: --Now Delete the records from ben_le_clsn_n_rstr
1224: --
1225: delete from ben_le_clsn_n_rstr pcs
1226: where pcs.per_in_ler_ended_id = p_per_in_ler_id
1227: and pcs.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_CTFN_PRVDD_F_UPD' ;
1221: END LOOP;
1222: --
1223: --Now Delete the records from ben_le_clsn_n_rstr
1224: --
1225: delete from ben_le_clsn_n_rstr pcs
1226: where pcs.per_in_ler_ended_id = p_per_in_ler_id
1227: and pcs.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_CTFN_PRVDD_F_UPD' ;
1228: --
1229: delete from ben_le_clsn_n_rstr pcs
1225: delete from ben_le_clsn_n_rstr pcs
1226: where pcs.per_in_ler_ended_id = p_per_in_ler_id
1227: and pcs.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_CTFN_PRVDD_F_UPD' ;
1228: --
1229: delete from ben_le_clsn_n_rstr pcs
1230: where pcs.per_in_ler_ended_id = p_per_in_ler_id
1231: and pcs.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_ACTN_F_UPD' ;
1232: --
1233: hr_utility.set_location ('Leaving '||l_proc,10);
1242: l_proc varchar2(72) := g_package||'restore_prev_epo';
1243: --
1244: cursor c_restore_epo( p_pil_id number ) IS
1245: select *
1246: from ben_le_clsn_n_rstr elig
1247: where elig.per_in_ler_ended_id = p_pil_id
1248: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_OPT_F_CORRECT' ;
1249: --
1250: cursor c_epo(p_elig_per_opt_id number,p_effective_date date) IS
1435: --
1436: END LOOP;
1437: --
1438: --
1439: --Now Delete the records from ben_le_clsn_n_rstr
1440: --
1441: delete from ben_le_clsn_n_rstr elig
1442: where elig.per_in_ler_ended_id = p_per_in_ler_id
1443: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_OPT_F_CORRECT';
1437: --
1438: --
1439: --Now Delete the records from ben_le_clsn_n_rstr
1440: --
1441: delete from ben_le_clsn_n_rstr elig
1442: where elig.per_in_ler_ended_id = p_per_in_ler_id
1443: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_OPT_F_CORRECT';
1444: --
1445: hr_utility.set_location ('Leaving '||l_proc,10);
1520: --
1521: cursor c_prv_rslt_lvl_bckdt( p_pil_id number,
1522: p_lf_evt_ocrd_dt date) is
1523: select effective_start_date
1524: from ben_le_clsn_n_rstr pen
1525: where per_in_ler_id = p_pil_id
1526: AND pen.business_group_id = p_business_group_id
1527: AND pen.prtt_enrt_rslt_stat_cd IS NULL
1528: AND pen.effective_end_date = hr_api.g_eot
1568:
1569: cursor c_prior_to_prv_rslt_lvl_bckdt( p_pil_id number,
1570: p_lf_evt_ocrd_dt date) is
1571: select leclr.*
1572: from ben_le_clsn_n_rstr leclr,
1573: ben_prtt_enrt_rslt_f pen
1574: where leclr.per_in_ler_id <> p_pil_id
1575: AND leclr.business_group_id = p_business_group_id
1576: AND leclr.prtt_enrt_rslt_stat_cd IS NULL
1920: hr_utility.set_location ('l_lf_evt_ocrd_dt' || l_prv_lf_evt_ocrd_dt, 199 );
1921: --
1922: --IK START
1923: -- Get all the BEN_ELIG_PER_F_CORRECTION and BEN_ELIG_PER_OPT_F_CORRECTION
1924: -- records from BEN_LE_CLSN_N_RSTR table for the per_in_ler being backed out
1925: -- and update the records with PEP and EPO records with the restored date
1926: -- in correction mode.
1927: -- After API call to Corrrect the PEP and EPO records delete the data from
1928: -- Collapse and restore table.
1990: --
1991: hr_utility.set_location ('prior to prev life event found in bkup table',44333);
1992: --
1993: -- Insert a correction row before updation as if, the enrollments were corrected by the next pil.
1994: INSERT INTO ben_le_clsn_n_rstr
1995: (COMP_LVL_CD,
1996: DSPLY_ON_ENRT_FLAG,
1997: RT_OVRIDN_FLAG,
1998: ACTY_REF_PERD_CD,
2365: l_prior_to_prv_rslt_lvl_bckdt.STD_PREM_UOM);
2366: --
2367: hr_utility.set_location('Inserted corrected Row', 44333);
2368: --
2369: UPDATE ben_le_clsn_n_rstr
2370: SET per_in_ler_id = l_prv_per_in_ler_id
2371: WHERE per_in_ler_id = l_prior_to_prv_rslt_lvl_bckdt.per_in_ler_id
2372: AND bkup_tbl_id = l_prior_to_prv_rslt_lvl_bckdt.bkup_tbl_id
2373: AND bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
2395: ,p_bckdt_per_in_ler_id => l_prv_per_in_ler_id
2396: ) ;
2397: -- once the result level backedout data is restored
2398: -- delete the data from the backout table
2399: delete from ben_le_clsn_n_rstr
2400: where per_in_ler_id = l_prv_per_in_ler_id
2401: AND business_group_id = p_business_group_id ;
2402:
2403:
3002: cursor c_ben_prtt_prem_f_corr is
3003: select ppe.*
3004: from ben_prtt_prem_f ppe,
3005: ben_per_in_ler pil,
3006: BEN_LE_CLSN_N_RSTR bkup
3007: where pil.per_in_ler_id = p_per_in_ler_id
3008: and pil.business_group_id = p_business_group_id
3009: and ppe.per_in_ler_id = pil.per_in_ler_id
3010: and ppe.business_group_id = pil.business_group_id
3021: cv_BKUP_TBL_ID in number,
3022: cv_effective_start_date in date,
3023: cv_effective_end_date in date) is
3024: select bkup.*,bkup.rowid
3025: from BEN_LE_CLSN_N_RSTR bkup
3026: where BKUP_TBL_TYP_CD = cv_BKUP_TBL_TYP_CD
3027: and BKUP_TBL_ID = cv_BKUP_TBL_ID
3028: and effective_start_date = cv_effective_start_date
3029: and effective_end_date = cv_effective_end_date
3307: prv.acty_base_rt_id,
3308: prv.prtt_rt_val_stat_cd, -- 8896851
3309: pil.person_id,
3310: decode( (select '1' from dual
3311: where exists (select '1' from ben_le_clsn_n_rstr bkp
3312: where bkp.per_in_ler_ended_id = p_per_in_ler_id
3313: and bkp.BKUP_TBL_ID = prv.prtt_rt_val_id
3314: and bkp.BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
3315: )
3352: --
3353: cursor c_get_bckdt_prv(p_prtt_rt_val_id number) is
3354: select lcr.rt_end_dt,
3355: lcr.per_in_ler_ended_id
3356: from ben_le_clsn_n_rstr lcr
3357: where lcr.per_in_ler_id = p_per_in_ler_id
3358: and bkup_tbl_typ_cd = 'BEN_PRTT_RT_VAL'
3359: and bkup_tbl_id = p_prtt_rt_val_id;
3360: --
3363: -- end 8896851
3364: --
3365: cursor c_prtt_rt_val_adj (p_per_in_ler_id number) is
3366: select *
3367: from ben_le_clsn_n_rstr
3368: where BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
3369: AND PER_IN_LER_ID = p_per_in_ler_id;
3370: --
3371: l_rt_adj c_prtt_rt_val_adj%rowtype;
3377: --
3378: -- Added for bug 7206471
3379: cursor c_prtt_enrt_rslt_adj (p_per_in_ler_id in number) is
3380: select *
3381: from ben_le_clsn_n_rstr
3382: where BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_ADJ'
3383: AND PER_IN_LER_ID = p_per_in_ler_id;
3384:
3385: /*Added cursors for Bug 12576710: Check whether there is any coverage,rate record is end by the backed out per_in_ler_id */
3384:
3385: /*Added cursors for Bug 12576710: Check whether there is any coverage,rate record is end by the backed out per_in_ler_id */
3386: cursor c_adj_rec_curr_per_in_ler (p_per_in_ler_id in number) is
3387: select *
3388: from ben_le_clsn_n_rstr
3389: where BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_ADJ'
3390: AND per_in_ler_ended_id = p_per_in_ler_id;
3391:
3392: cursor c_adj_rate_curr_per_in_ler (p_per_in_ler_id number,p_bkp_prv_id number) is
3390: AND per_in_ler_ended_id = p_per_in_ler_id;
3391:
3392: cursor c_adj_rate_curr_per_in_ler (p_per_in_ler_id number,p_bkp_prv_id number) is
3393: select *
3394: from ben_le_clsn_n_rstr
3395: where BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
3396: AND per_in_ler_ended_id = p_per_in_ler_id
3397: and BKUP_TBL_ID = nvl(p_bkp_prv_id,BKUP_TBL_ID);
3398: --
3539:
3540: cursor c_corr_result_exist (l_per_in_ler_id number ,
3541: l_prtt_enrt_rslt_id number ) is
3542: select 'x'
3543: from BEN_LE_CLSN_N_RSTR lcnr
3544: ,BEN_PRTT_ENRT_RSLT_F pen -- Bug 6632568
3545: where lcnr.bkup_tbl_id = pen.prtt_enrt_rslt_id -- Bug 6632568
3546: and pen.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id -- Bug 6632568
3547: and lcnr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR'
3568: between pen.effective_start_date
3569: and pen.effective_end_date;
3570: */
3571: -- bug # 3086161
3572: cursor c_BEN_LE_CLSN_N_RSTR_corr (c_pil_id number)
3573: is
3574: select lcnr.bkup_tbl_id,
3575: lcnr.effective_start_date,
3576: lcnr.effective_end_date,
3581: lcnr.sspndd_flag,
3582: lcnr.enrt_mthd_cd, -- Bug 7137371
3583: pen.effective_end_date pen_effective_end_date, -- Bug 7197868
3584: pen.object_version_number
3585: from BEN_LE_CLSN_N_RSTR lcnr,
3586: ben_prtt_enrt_rslt_f pen
3587: where lcnr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR'
3588: and lcnr.Per_in_ler_ended_id = c_pil_id
3589: and pen.prtt_enrt_rslt_id = lcnr.bkup_tbl_id
3599: pen.effective_start_date and pen.effective_end_date
3600: order by lcnr.bkup_tbl_id;
3601: --
3602:
3603: cursor c_BEN_LE_CLSN_N_RSTR_del (c_pil_id number)
3604: is
3605: select lcnr.bkup_tbl_id,
3606: lcnr.effective_start_date,
3607: lcnr.effective_end_date,
3610: lcnr.enrt_cvg_thru_dt,
3611: lcnr.prtt_enrt_rslt_stat_cd,
3612: lcnr.sspndd_flag,
3613: pen.object_version_number
3614: from BEN_LE_CLSN_N_RSTR lcnr,
3615: ben_prtt_enrt_rslt_f pen
3616: where lcnr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_DEL'
3617: and lcnr.Per_in_ler_ended_id = c_pil_id
3618: and pen.prtt_enrt_rslt_id = lcnr.bkup_tbl_id
3623: pen.effective_start_date and pen.effective_end_date
3624: order by lcnr.bkup_tbl_id;
3625:
3626:
3627: cursor c_BEN_LE_CLSN_N_RSTR_dpnt (c_pil_id number)
3628: is
3629: select lcnr.bkup_tbl_id,
3630: pdp.effective_start_date,
3631: pdp.effective_end_date,
3636: lcnr.effective_start_date bkp_effective_start_date, -- 7197868
3637: lcnr.effective_end_date bkp_effective_end_date, -- 7197868
3638: pdp.object_version_number,
3639: pdp.elig_cvrd_dpnt_id
3640: from BEN_LE_CLSN_N_RSTR lcnr,
3641: ben_elig_cvrd_dpnt_f pdp
3642: where lcnr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR'
3643: and lcnr.Per_in_ler_ended_id = c_pil_id
3644: and pdp.prtt_enrt_rslt_id = lcnr.bkup_tbl_id
3648: and lcnr.effective_start_date between
3649: pdp.effective_start_date and pdp.effective_end_date
3650: order by pdp.elig_cvrd_dpnt_id;
3651:
3652: cursor c_BEN_LE_CLSN_N_RSTR_pbn (c_pil_id number)
3653: is
3654: select lcnr.bkup_tbl_id,
3655: pbn.effective_start_date,
3656: pbn.effective_end_date,
3659: lcnr.enrt_cvg_thru_dt,
3660: lcnr.prtt_enrt_rslt_stat_cd,
3661: pbn.object_version_number,
3662: pbn.pl_bnf_id
3663: from BEN_LE_CLSN_N_RSTR lcnr,
3664: ben_pl_bnf_f pbn
3665: where lcnr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR'
3666: and lcnr.Per_in_ler_ended_id = c_pil_id
3667: and pbn.prtt_enrt_rslt_id = lcnr.bkup_tbl_id
3767: --
3768: CURSOR c_bpl_from_backup
3769: IS
3770: SELECT bpl.*
3771: FROM ben_le_clsn_n_rstr bpl
3772: WHERE bkup_tbl_typ_cd = 'BEN_BNFT_PRVDD_LDGR_F'
3773: AND per_in_ler_id = p_per_in_ler_id
3774: AND effective_end_date = hr_api.g_eot;
3775: --
3848: cv_PER_IN_LER_ID in number,
3849: cv_BKUP_TBL_ID in number,
3850: cv_object_version_number in number) is
3851: select rowid
3852: from BEN_LE_CLSN_N_RSTR
3853: where BKUP_TBL_TYP_CD = cv_BKUP_TBL_TYP_CD
3854: and PER_IN_LER_ID = cv_PER_IN_LER_ID
3855: and BKUP_TBL_ID = cv_BKUP_TBL_ID
3856: and object_version_number = cv_object_version_number;
4033: hr_utility.set_location ('backup notfound '||l_effective_date,10);
4034: --
4035: close c_bkp_row;
4036: --
4037: insert into BEN_LE_CLSN_N_RSTR (
4038: BKUP_TBL_TYP_CD,
4039: CVG_STRT_DT,
4040: CVG_THRU_DT,
4041: CVG_PNDG_FLAG,
4149: else
4150: --
4151: close c_bkp_row;
4152: --
4153: update BEN_LE_CLSN_N_RSTR set
4154: -- BKUP_TBL_TYP_CD = 'BEN_ELIG_CVRD_DPNT_F'
4155: CVG_STRT_DT = l_deleted_pdp_rec.CVG_STRT_DT,
4156: CVG_THRU_DT = l_deleted_pdp_rec.CVG_THRU_DT,
4157: CVG_PNDG_FLAG = l_deleted_pdp_rec.CVG_PNDG_FLAG,
4298:
4299: ---- if any of the result in corrected and stored in backp table for correction
4300: ---- correct the per_in_ler_id of related the dpnt table too bug # 3086161
4301:
4302: for i in c_BEN_LE_CLSN_N_RSTR_dpnt(p_per_in_ler_id )
4303: Loop
4304: if l_prev_elig_cvrd_dpnt_id <> i.elig_cvrd_dpnt_id then
4305: l_object_version_number := i.object_version_number ;
4306: hr_utility.set_location(' correcting ' || i.elig_cvrd_dpnt_id, 999 );
4405: --
4406: for l_deleted_pep_rec in c_deleted_pep(l_pk_id, l_effective_date)
4407: loop
4408: --
4409: insert into BEN_LE_CLSN_N_RSTR (
4410: BKUP_TBL_TYP_CD,
4411: PLIP_ID,
4412: PTIP_ID,
4413: WAIT_PERD_CMPLTN_DT,
4710: --
4711: for l_deleted_epo_rec in c_deleted_epo(l_pk_id, l_effective_date)
4712: loop
4713: --
4714: insert into BEN_LE_CLSN_N_RSTR (
4715: BKUP_TBL_TYP_CD,
4716: INELG_RSN_CD,
4717: PER_IN_LER_ID,
4718: AGE_UOM,
4964: ,p_datetrack_mode => 'CORRECTION'
4965:
4966: );
4967: --delete the rows from back-up table once the record is restored
4968: delete from ben_le_clsn_n_rstr cls
4969: where rowid = l_bkp_prem_row.rowid;
4970: end loop;
4971: end loop;
4972: -----------7133998
5022: then
5023: --
5024: close c_bkp_row;
5025: --
5026: insert into BEN_LE_CLSN_N_RSTR (
5027: BKUP_TBL_TYP_CD,
5028: LCR_ATTRIBUTE6,
5029: LCR_ATTRIBUTE7,
5030: LCR_ATTRIBUTE8,
5133: else
5134: --
5135: close c_bkp_row;
5136: --
5137: update BEN_LE_CLSN_N_RSTR set
5138: -- BKUP_TBL_TYP_CD,
5139: LCR_ATTRIBUTE6 = l_deleted_ppe_rec.PPE_ATTRIBUTE6,
5140: LCR_ATTRIBUTE7 = l_deleted_ppe_rec.PPE_ATTRIBUTE7,
5141: LCR_ATTRIBUTE8 = l_deleted_ppe_rec.PPE_ATTRIBUTE8,
5278: then
5279: --
5280: close c_bkp_row;
5281: --
5282: insert into BEN_LE_CLSN_N_RSTR (
5283: BKUP_TBL_TYP_CD,
5284: LCR_ATTRIBUTE1,
5285: LCR_ATTRIBUTE2,
5286: LCR_ATTRIBUTE3,
5402: else
5403: --
5404: close c_bkp_row;
5405: --
5406: update BEN_LE_CLSN_N_RSTR set
5407: LCR_ATTRIBUTE1 = l_deleted_pbn_rec.PBN_ATTRIBUTE1,
5408: LCR_ATTRIBUTE2 = l_deleted_pbn_rec.PBN_ATTRIBUTE2,
5409: LCR_ATTRIBUTE3 = l_deleted_pbn_rec.PBN_ATTRIBUTE3,
5410: LCR_ATTRIBUTE4 = l_deleted_pbn_rec.PBN_ATTRIBUTE4,
5507: --
5508: -- if any of the result in corrected and stored in backp table for corrn
5509: -- correct the per_in_ler_id of related the dpnt table too bug # 3086161
5510:
5511: for i in c_BEN_LE_CLSN_N_RSTR_pbn(p_per_in_ler_id )
5512: Loop
5513: l_object_version_number := i.object_version_number ;
5514: ben_plan_beneficiary_api.update_plan_beneficiary
5515: (p_validate => FALSE
5652: fetch c_multiple_rate into l_multiple_rate;
5653: if c_multiple_rate%found then
5654: --
5655: hr_utility.set_location('Multiple rate',111);
5656: insert into BEN_LE_CLSN_N_RSTR (
5657: BKUP_TBL_TYP_CD,
5658: BKUP_TBL_ID,
5659: per_in_ler_id,
5660: business_group_id,
5787: then
5788: --
5789: close c_bkp_row;
5790: --
5791: insert into BEN_LE_CLSN_N_RSTR (
5792: BKUP_TBL_TYP_CD,
5793: BKUP_TBL_ID,
5794: prtt_enrt_rslt_id, -- Used for prtt_rt_val_id,
5795: per_in_ler_id,
5806: else
5807: --
5808: close c_bkp_row;
5809: --
5810: update BEN_LE_CLSN_N_RSTR set
5811: prtt_enrt_rslt_id = l_ecr.prtt_rt_val_id,
5812: business_group_id = l_ecr.business_group_id
5813: where rowid = l_row_id;
5814: --
6097: --
6098: hr_utility.set_location( ' copying ' || l_deleted_pen_rec.PRTT_ENRT_RSLT_ID , 99 );
6099: close c_bkp_row;
6100: --
6101: insert into BEN_LE_CLSN_N_RSTR (
6102: BKUP_TBL_TYP_CD,
6103: COMP_LVL_CD,
6104: LCR_ATTRIBUTE16,
6105: LCR_ATTRIBUTE17,
6261: else
6262: --
6263: close c_bkp_row;
6264: --
6265: update BEN_LE_CLSN_N_RSTR set
6266: COMP_LVL_CD = l_deleted_pen_rec.COMP_LVL_CD,
6267: LCR_ATTRIBUTE16 = l_deleted_pen_rec.PEN_ATTRIBUTE16,
6268: LCR_ATTRIBUTE17 = l_deleted_pen_rec.PEN_ATTRIBUTE17,
6269: LCR_ATTRIBUTE18 = l_deleted_pen_rec.PEN_ATTRIBUTE18,
6456: then
6457: --
6458: close c_bkp_row;
6459: hr_utility.set_location(' creating backup ' || l_pk_id, 999);
6460: insert into BEN_LE_CLSN_N_RSTR (
6461: BKUP_TBL_TYP_CD,
6462: COMP_LVL_CD,
6463: LCR_ATTRIBUTE16,
6464: LCR_ATTRIBUTE17,
6620: else
6621: --
6622: close c_bkp_row;
6623: --
6624: update BEN_LE_CLSN_N_RSTR set
6625: COMP_LVL_CD = l_deleted_pen_rec.COMP_LVL_CD,
6626: LCR_ATTRIBUTE16 = l_deleted_pen_rec.PEN_ATTRIBUTE16,
6627: LCR_ATTRIBUTE17 = l_deleted_pen_rec.PEN_ATTRIBUTE17,
6628: LCR_ATTRIBUTE18 = l_deleted_pen_rec.PEN_ATTRIBUTE18,
6769:
6770:
6771: --- if result for the per_in_ler_id is found in 'BEN_PRTT_ENRT_RSLT_F_CORR
6772: --- correct the result with with per_in_ler_id and coverage dates bug # 3086161
6773: for i in c_BEN_LE_CLSN_N_RSTR_corr(p_per_in_ler_id)
6774: Loop
6775: --
6776: -- Get the enrollment coverage end date -- bug 8199189
6777: --
6872: or (l_prtt_enrt_rslt_stat_cd = 'VOIDD')) then -- 8199189
6873: hr_utility.set_location(' correcting ' || i.bkup_tbl_id, 999 );
6874: hr_utility.set_location(' i.per_in_ler_id ' || i.per_in_ler_id, 999 );
6875: hr_utility.set_location(' p_per_in_ler_id ' || p_per_in_ler_id, 999 );
6876: delete from ben_le_clsn_n_rstr cqb
6877: where cqb.per_in_ler_id = i.per_in_ler_id
6878: and cqb.per_in_ler_ended_id = p_per_in_ler_id
6879: and cqb.bkup_tbl_id = i.bkup_tbl_id ;
6880: else
6881: hr_utility.set_location(' enrt_rslt_id' || i.bkup_tbl_id, 999 );
6882: hr_utility.set_location(' p_per_in_ler_id ' || p_per_in_ler_id, 999 );
6883: hr_utility.set_location(' i.effective_start_date ' || i.effective_start_date, 999 );
6884: --
6885: update ben_le_clsn_n_rstr cqb
6886: set cqb.per_in_ler_id = p_per_in_ler_id
6887: , cqb.per_in_ler_ended_id = null
6888: , cqb.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F'
6889: , cqb.enrt_mthd_cd = l_get_enrt_mthd_cd
6898: --
6899: /* Bug 6034585 : Commented out the update statement as it was
6900: updating per_in_ler_id field with the incorrect per_in_ler_id
6901: --bug#3702033 - for reinstate retain the row with necessary updates
6902: update ben_le_clsn_n_rstr cqb
6903: set cqb.per_in_ler_id = p_per_in_ler_id,
6904: cqb.per_in_ler_ended_id = null,
6905: cqb.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F'
6906: where cqb.per_in_ler_id = i.per_in_ler_id
6914: --
6915: /*Bug 12963718: When backout package is called for backing out a single enrollment result(ie p_bckdt_prtt_enrt_rslt_id
6916: will not be NULL), do not call delete enrollment for backup records that exists in the backup table */
6917: if(p_bckdt_prtt_enrt_rslt_id is NULL) then
6918: for i in c_BEN_LE_CLSN_N_RSTR_del(p_per_in_ler_id)
6919: Loop
6920: hr_utility.set_location(' delete result ' || i.bkup_tbl_id, 99 );
6921: if l_prev_bkup_tbl_id <> i.bkup_tbl_id then
6922:
7017: elsif p_routine = 'BEN_BNFT_PRVDD_LDGR_F' then
7018: --
7019: -- Bug 5500864
7020: -- This part of code has been added to take care of reinstatements of BPLs that have been END-DATED
7021: -- Such BPL Records have been backed up in BEN_LE_CLSN_N_RSTR. See BEN_BPL_DEL.POST_DELETE
7022: -- The part of the code below (which queries cursor c_ben_bnft_prvdd_ldgr_f) takes care of rest
7023: -- of the scenarios and voiding of BPL associated with the life-event being backed out.
7024: --
7025: hr_utility.set_location('ACE p_per_in_ler_id = ' || p_per_in_ler_id, 9999);
7042: hr_utility.set_location('ACE Reopened = ' || l_bpl_from_backup.BKUP_TBL_ID, 9999);
7043: --
7044: -- Bug 6376239 : Remove the rows from backup table once the ledger rows are restored
7045: --
7046: delete from ben_le_clsn_n_rstr cls
7047: where cls.per_in_ler_id = l_bpl_from_backup.per_in_ler_id
7048: and cls.bkup_tbl_id = l_bpl_from_backup.bkup_tbl_id
7049: and cls.bkup_tbl_typ_cd = 'BEN_BNFT_PRVDD_LDGR_F'
7050: and effective_start_date = l_bpl_from_backup.effective_start_date
7189: then
7190: --
7191: close c_bkp_row;
7192: --
7193: insert into BEN_LE_CLSN_N_RSTR(
7194: bkup_tbl_typ_cd,
7195: bkup_tbl_id,
7196: elig_flag,
7197: elig_strt_dt,
7216: else
7217: --
7218: close c_bkp_row;
7219: --
7220: update BEN_LE_CLSN_N_RSTR set
7221: elig_flag = l_cqb_rec.quald_bnf_flag,
7222: elig_strt_dt = l_cqb_rec.cbr_elig_perd_strt_dt,
7223: elig_thru_dt = l_cqb_rec.cbr_elig_perd_end_dt,
7224: inelg_rsn_cd = l_cqb_rec.cbr_inelg_rsn_cd,