[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,
717: l_proc varchar2(72) := g_package||'restore_prev_pep';
718: --
719: cursor c_restore_pep( p_pil_id number ) IS
720: select *
721: from ben_le_clsn_n_rstr elig
722: where elig.per_in_ler_ended_id = p_pil_id
723: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_F_CORRECT' ;
724: --
725: cursor c_pep(p_elig_per_id number,p_effective_date date) IS
836: close c_pep;
837: --
838: END LOOP;
839: --
840: --Now Delete the records from ben_le_clsn_n_rstr
841: --
842: delete from ben_le_clsn_n_rstr elig
843: where elig.per_in_ler_ended_id = p_per_in_ler_id
844: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_F_CORRECT' ;
838: END LOOP;
839: --
840: --Now Delete the records from ben_le_clsn_n_rstr
841: --
842: delete from ben_le_clsn_n_rstr elig
843: where elig.per_in_ler_ended_id = p_per_in_ler_id
844: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_F_CORRECT' ;
845: --
846: hr_utility.set_location ('Leaving '||l_proc,10);
854: l_proc varchar2(72) := g_package||'restore_cert_completion';
855: --
856: cursor c_restore_pcs( p_pil_id number) IS
857: select pcs.*
858: from ben_le_clsn_n_rstr pcs,
859: ben_le_clsn_n_rstr pea
860: where pcs.per_in_ler_ended_id = p_pil_id
861: and pea.per_in_ler_ended_id = p_pil_id
862: and pea.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_ACTN_F_UPD'
855: --
856: cursor c_restore_pcs( p_pil_id number) IS
857: select pcs.*
858: from ben_le_clsn_n_rstr pcs,
859: ben_le_clsn_n_rstr pea
860: where pcs.per_in_ler_ended_id = p_pil_id
861: and pea.per_in_ler_ended_id = p_pil_id
862: and pea.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_ACTN_F_UPD'
863: and pcs.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_CTFN_PRVDD_F_UPD'
977: close c_pcs;
978: --
979: END LOOP;
980: --
981: --Now Delete the records from ben_le_clsn_n_rstr
982: --
983: delete from ben_le_clsn_n_rstr pcs
984: where pcs.per_in_ler_ended_id = p_per_in_ler_id
985: and pcs.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_CTFN_PRVDD_F_UPD' ;
979: END LOOP;
980: --
981: --Now Delete the records from ben_le_clsn_n_rstr
982: --
983: delete from ben_le_clsn_n_rstr pcs
984: where pcs.per_in_ler_ended_id = p_per_in_ler_id
985: and pcs.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_CTFN_PRVDD_F_UPD' ;
986: --
987: delete from ben_le_clsn_n_rstr pcs
983: delete from ben_le_clsn_n_rstr pcs
984: where pcs.per_in_ler_ended_id = p_per_in_ler_id
985: and pcs.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_CTFN_PRVDD_F_UPD' ;
986: --
987: delete from ben_le_clsn_n_rstr pcs
988: where pcs.per_in_ler_ended_id = p_per_in_ler_id
989: and pcs.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_ACTN_F_UPD' ;
990: --
991: hr_utility.set_location ('Leaving '||l_proc,10);
1000: l_proc varchar2(72) := g_package||'restore_prev_epo';
1001: --
1002: cursor c_restore_epo( p_pil_id number ) IS
1003: select *
1004: from ben_le_clsn_n_rstr elig
1005: where elig.per_in_ler_ended_id = p_pil_id
1006: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_OPT_F_CORRECT' ;
1007: --
1008: cursor c_epo(p_elig_per_opt_id number,p_effective_date date) IS
1121: --
1122: END LOOP;
1123: --
1124: --
1125: --Now Delete the records from ben_le_clsn_n_rstr
1126: --
1127: delete from ben_le_clsn_n_rstr elig
1128: where elig.per_in_ler_ended_id = p_per_in_ler_id
1129: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_OPT_F_CORRECT';
1123: --
1124: --
1125: --Now Delete the records from ben_le_clsn_n_rstr
1126: --
1127: delete from ben_le_clsn_n_rstr elig
1128: where elig.per_in_ler_ended_id = p_per_in_ler_id
1129: and elig.BKUP_TBL_TYP_CD = 'BEN_ELIG_PER_OPT_F_CORRECT';
1130: --
1131: hr_utility.set_location ('Leaving '||l_proc,10);
1206: --
1207: cursor c_prv_rslt_lvl_bckdt( p_pil_id number,
1208: p_lf_evt_ocrd_dt date) is
1209: select effective_start_date
1210: from ben_le_clsn_n_rstr pen
1211: where per_in_ler_id = p_pil_id
1212: AND pen.business_group_id = p_business_group_id
1213: AND pen.prtt_enrt_rslt_stat_cd IS NULL
1214: AND pen.effective_end_date = hr_api.g_eot
1220:
1221: cursor c_prior_to_prv_rslt_lvl_bckdt( p_pil_id number,
1222: p_lf_evt_ocrd_dt date) is
1223: select leclr.*
1224: from ben_le_clsn_n_rstr leclr,
1225: ben_prtt_enrt_rslt_f pen
1226: where leclr.per_in_ler_id <> p_pil_id
1227: AND leclr.business_group_id = p_business_group_id
1228: AND leclr.prtt_enrt_rslt_stat_cd IS NULL
1572: hr_utility.set_location ('l_lf_evt_ocrd_dt' || l_prv_lf_evt_ocrd_dt, 199 );
1573: --
1574: --IK START
1575: -- Get all the BEN_ELIG_PER_F_CORRECTION and BEN_ELIG_PER_OPT_F_CORRECTION
1576: -- records from BEN_LE_CLSN_N_RSTR table for the per_in_ler being backed out
1577: -- and update the records with PEP and EPO records with the restored date
1578: -- in correction mode.
1579: -- After API call to Corrrect the PEP and EPO records delete the data from
1580: -- Collapse and restore table.
1605: --
1606: hr_utility.set_location ('prior to prev life event found in bkup table',44333);
1607: --
1608: -- Insert a correction row before updation as if, the enrollments were corrected by the next pil.
1609: INSERT INTO ben_le_clsn_n_rstr
1610: (COMP_LVL_CD,
1611: DSPLY_ON_ENRT_FLAG,
1612: RT_OVRIDN_FLAG,
1613: ACTY_REF_PERD_CD,
1980: l_prior_to_prv_rslt_lvl_bckdt.STD_PREM_UOM);
1981: --
1982: hr_utility.set_location('Inserted corrected Row', 44333);
1983: --
1984: UPDATE ben_le_clsn_n_rstr
1985: SET per_in_ler_id = l_prv_per_in_ler_id
1986: WHERE per_in_ler_id = l_prior_to_prv_rslt_lvl_bckdt.per_in_ler_id
1987: AND bkup_tbl_id = l_prior_to_prv_rslt_lvl_bckdt.bkup_tbl_id
1988: AND bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
2010: ,p_bckdt_per_in_ler_id => l_prv_per_in_ler_id
2011: ) ;
2012: -- once the result level backedout data is restored
2013: -- delete the data from the backout table
2014: delete from ben_le_clsn_n_rstr
2015: where per_in_ler_id = l_prv_per_in_ler_id
2016: AND business_group_id = p_business_group_id ;
2017:
2018:
2596: cursor c_ben_prtt_prem_f_corr is
2597: select ppe.*
2598: from ben_prtt_prem_f ppe,
2599: ben_per_in_ler pil,
2600: BEN_LE_CLSN_N_RSTR bkup
2601: where pil.per_in_ler_id = p_per_in_ler_id
2602: and pil.business_group_id = p_business_group_id
2603: and ppe.per_in_ler_id = pil.per_in_ler_id
2604: and ppe.business_group_id = pil.business_group_id
2615: cv_BKUP_TBL_ID in number,
2616: cv_effective_start_date in date,
2617: cv_effective_end_date in date) is
2618: select bkup.*,bkup.rowid
2619: from BEN_LE_CLSN_N_RSTR bkup
2620: where BKUP_TBL_TYP_CD = cv_BKUP_TBL_TYP_CD
2621: and BKUP_TBL_ID = cv_BKUP_TBL_ID
2622: and effective_start_date = cv_effective_start_date
2623: and effective_end_date = cv_effective_end_date
2930: l_next_prv c_next_prv%rowtype;
2931: --
2932: cursor c_prtt_rt_val_adj (p_per_in_ler_id number) is
2933: select *
2934: from ben_le_clsn_n_rstr
2935: where BKUP_TBL_TYP_CD = 'BEN_PRTT_RT_VAL_ADJ'
2936: AND PER_IN_LER_ID = p_per_in_ler_id;
2937: --
2938: l_rt_adj c_prtt_rt_val_adj%rowtype;
2944: --
2945: -- Added for bug 7206471
2946: cursor c_prtt_enrt_rslt_adj (p_per_in_ler_id in number) is
2947: select *
2948: from ben_le_clsn_n_rstr
2949: where BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_ADJ'
2950: AND PER_IN_LER_ID = p_per_in_ler_id;
2951: --
2952: l_cvg_adj c_prtt_enrt_rslt_adj%rowtype;
3087:
3088: cursor c_corr_result_exist (l_per_in_ler_id number ,
3089: l_prtt_enrt_rslt_id number ) is
3090: select 'x'
3091: from BEN_LE_CLSN_N_RSTR lcnr
3092: ,BEN_PRTT_ENRT_RSLT_F pen -- Bug 6632568
3093: where lcnr.bkup_tbl_id = pen.prtt_enrt_rslt_id -- Bug 6632568
3094: and pen.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id -- Bug 6632568
3095: and lcnr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR'
3116: between pen.effective_start_date
3117: and pen.effective_end_date;
3118: */
3119: -- bug # 3086161
3120: cursor c_BEN_LE_CLSN_N_RSTR_corr (c_pil_id number)
3121: is
3122: select lcnr.bkup_tbl_id,
3123: lcnr.effective_start_date,
3124: lcnr.effective_end_date,
3129: lcnr.sspndd_flag,
3130: lcnr.enrt_mthd_cd, -- Bug 7137371
3131: pen.effective_end_date pen_effective_end_date, -- Bug 7197868
3132: pen.object_version_number
3133: from BEN_LE_CLSN_N_RSTR lcnr,
3134: ben_prtt_enrt_rslt_f pen
3135: where lcnr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR'
3136: and lcnr.Per_in_ler_ended_id = c_pil_id
3137: and pen.prtt_enrt_rslt_id = lcnr.bkup_tbl_id
3147: pen.effective_start_date and pen.effective_end_date
3148: order by lcnr.bkup_tbl_id;
3149: --
3150:
3151: cursor c_BEN_LE_CLSN_N_RSTR_del (c_pil_id number)
3152: is
3153: select lcnr.bkup_tbl_id,
3154: lcnr.effective_start_date,
3155: lcnr.effective_end_date,
3158: lcnr.enrt_cvg_thru_dt,
3159: lcnr.prtt_enrt_rslt_stat_cd,
3160: lcnr.sspndd_flag,
3161: pen.object_version_number
3162: from BEN_LE_CLSN_N_RSTR lcnr,
3163: ben_prtt_enrt_rslt_f pen
3164: where lcnr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_DEL'
3165: and lcnr.Per_in_ler_ended_id = c_pil_id
3166: and pen.prtt_enrt_rslt_id = lcnr.bkup_tbl_id
3171: pen.effective_start_date and pen.effective_end_date
3172: order by lcnr.bkup_tbl_id;
3173:
3174:
3175: cursor c_BEN_LE_CLSN_N_RSTR_dpnt (c_pil_id number)
3176: is
3177: select lcnr.bkup_tbl_id,
3178: pdp.effective_start_date,
3179: pdp.effective_end_date,
3184: lcnr.effective_start_date bkp_effective_start_date, -- 7197868
3185: lcnr.effective_end_date bkp_effective_end_date, -- 7197868
3186: pdp.object_version_number,
3187: pdp.elig_cvrd_dpnt_id
3188: from BEN_LE_CLSN_N_RSTR lcnr,
3189: ben_elig_cvrd_dpnt_f pdp
3190: where lcnr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR'
3191: and lcnr.Per_in_ler_ended_id = c_pil_id
3192: and pdp.prtt_enrt_rslt_id = lcnr.bkup_tbl_id
3196: and lcnr.effective_start_date between
3197: pdp.effective_start_date and pdp.effective_end_date
3198: order by pdp.elig_cvrd_dpnt_id;
3199:
3200: cursor c_BEN_LE_CLSN_N_RSTR_pbn (c_pil_id number)
3201: is
3202: select lcnr.bkup_tbl_id,
3203: pbn.effective_start_date,
3204: pbn.effective_end_date,
3207: lcnr.enrt_cvg_thru_dt,
3208: lcnr.prtt_enrt_rslt_stat_cd,
3209: pbn.object_version_number,
3210: pbn.pl_bnf_id
3211: from BEN_LE_CLSN_N_RSTR lcnr,
3212: ben_pl_bnf_f pbn
3213: where lcnr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR'
3214: and lcnr.Per_in_ler_ended_id = c_pil_id
3215: and pbn.prtt_enrt_rslt_id = lcnr.bkup_tbl_id
3315: --
3316: CURSOR c_bpl_from_backup
3317: IS
3318: SELECT bpl.*
3319: FROM ben_le_clsn_n_rstr bpl
3320: WHERE bkup_tbl_typ_cd = 'BEN_BNFT_PRVDD_LDGR_F'
3321: AND per_in_ler_id = p_per_in_ler_id
3322: AND effective_end_date = hr_api.g_eot;
3323: --
3396: cv_PER_IN_LER_ID in number,
3397: cv_BKUP_TBL_ID in number,
3398: cv_object_version_number in number) is
3399: select rowid
3400: from BEN_LE_CLSN_N_RSTR
3401: where BKUP_TBL_TYP_CD = cv_BKUP_TBL_TYP_CD
3402: and PER_IN_LER_ID = cv_PER_IN_LER_ID
3403: and BKUP_TBL_ID = cv_BKUP_TBL_ID
3404: and object_version_number = cv_object_version_number;
3555: hr_utility.set_location ('backup notfound '||l_effective_date,10);
3556: --
3557: close c_bkp_row;
3558: --
3559: insert into BEN_LE_CLSN_N_RSTR (
3560: BKUP_TBL_TYP_CD,
3561: CVG_STRT_DT,
3562: CVG_THRU_DT,
3563: CVG_PNDG_FLAG,
3671: else
3672: --
3673: close c_bkp_row;
3674: --
3675: update BEN_LE_CLSN_N_RSTR set
3676: -- BKUP_TBL_TYP_CD = 'BEN_ELIG_CVRD_DPNT_F'
3677: CVG_STRT_DT = l_deleted_pdp_rec.CVG_STRT_DT,
3678: CVG_THRU_DT = l_deleted_pdp_rec.CVG_THRU_DT,
3679: CVG_PNDG_FLAG = l_deleted_pdp_rec.CVG_PNDG_FLAG,
3820:
3821: ---- if any of the result in corrected and stored in backp table for correction
3822: ---- correct the per_in_ler_id of related the dpnt table too bug # 3086161
3823:
3824: for i in c_BEN_LE_CLSN_N_RSTR_dpnt(p_per_in_ler_id )
3825: Loop
3826: if l_prev_elig_cvrd_dpnt_id <> i.elig_cvrd_dpnt_id then
3827: l_object_version_number := i.object_version_number ;
3828: hr_utility.set_location(' correcting ' || i.elig_cvrd_dpnt_id, 999 );
3924: --
3925: for l_deleted_pep_rec in c_deleted_pep(l_pk_id, l_effective_date)
3926: loop
3927: --
3928: insert into BEN_LE_CLSN_N_RSTR (
3929: BKUP_TBL_TYP_CD,
3930: PLIP_ID,
3931: PTIP_ID,
3932: WAIT_PERD_CMPLTN_DT,
4228: --
4229: for l_deleted_epo_rec in c_deleted_epo(l_pk_id, l_effective_date)
4230: loop
4231: --
4232: insert into BEN_LE_CLSN_N_RSTR (
4233: BKUP_TBL_TYP_CD,
4234: INELG_RSN_CD,
4235: PER_IN_LER_ID,
4236: AGE_UOM,
4482: ,p_datetrack_mode => 'CORRECTION'
4483:
4484: );
4485: --delete the rows from back-up table once the record is restored
4486: delete from ben_le_clsn_n_rstr cls
4487: where rowid = l_bkp_prem_row.rowid;
4488: end loop;
4489: end loop;
4490: -----------7133998
4540: then
4541: --
4542: close c_bkp_row;
4543: --
4544: insert into BEN_LE_CLSN_N_RSTR (
4545: BKUP_TBL_TYP_CD,
4546: LCR_ATTRIBUTE6,
4547: LCR_ATTRIBUTE7,
4548: LCR_ATTRIBUTE8,
4651: else
4652: --
4653: close c_bkp_row;
4654: --
4655: update BEN_LE_CLSN_N_RSTR set
4656: -- BKUP_TBL_TYP_CD,
4657: LCR_ATTRIBUTE6 = l_deleted_ppe_rec.PPE_ATTRIBUTE6,
4658: LCR_ATTRIBUTE7 = l_deleted_ppe_rec.PPE_ATTRIBUTE7,
4659: LCR_ATTRIBUTE8 = l_deleted_ppe_rec.PPE_ATTRIBUTE8,
4796: then
4797: --
4798: close c_bkp_row;
4799: --
4800: insert into BEN_LE_CLSN_N_RSTR (
4801: BKUP_TBL_TYP_CD,
4802: LCR_ATTRIBUTE1,
4803: LCR_ATTRIBUTE2,
4804: LCR_ATTRIBUTE3,
4920: else
4921: --
4922: close c_bkp_row;
4923: --
4924: update BEN_LE_CLSN_N_RSTR set
4925: LCR_ATTRIBUTE1 = l_deleted_pbn_rec.PBN_ATTRIBUTE1,
4926: LCR_ATTRIBUTE2 = l_deleted_pbn_rec.PBN_ATTRIBUTE2,
4927: LCR_ATTRIBUTE3 = l_deleted_pbn_rec.PBN_ATTRIBUTE3,
4928: LCR_ATTRIBUTE4 = l_deleted_pbn_rec.PBN_ATTRIBUTE4,
5025: --
5026: -- if any of the result in corrected and stored in backp table for corrn
5027: -- correct the per_in_ler_id of related the dpnt table too bug # 3086161
5028:
5029: for i in c_BEN_LE_CLSN_N_RSTR_pbn(p_per_in_ler_id )
5030: Loop
5031: l_object_version_number := i.object_version_number ;
5032: ben_plan_beneficiary_api.update_plan_beneficiary
5033: (p_validate => FALSE
5170: fetch c_multiple_rate into l_multiple_rate;
5171: if c_multiple_rate%found then
5172: --
5173: hr_utility.set_location('Multiple rate',111);
5174: insert into BEN_LE_CLSN_N_RSTR (
5175: BKUP_TBL_TYP_CD,
5176: BKUP_TBL_ID,
5177: per_in_ler_id,
5178: business_group_id,
5305: then
5306: --
5307: close c_bkp_row;
5308: --
5309: insert into BEN_LE_CLSN_N_RSTR (
5310: BKUP_TBL_TYP_CD,
5311: BKUP_TBL_ID,
5312: prtt_enrt_rslt_id, -- Used for prtt_rt_val_id,
5313: per_in_ler_id,
5324: else
5325: --
5326: close c_bkp_row;
5327: --
5328: update BEN_LE_CLSN_N_RSTR set
5329: prtt_enrt_rslt_id = l_ecr.prtt_rt_val_id,
5330: business_group_id = l_ecr.business_group_id
5331: where rowid = l_row_id;
5332: --
5537: --
5538: hr_utility.set_location( ' copying ' || l_deleted_pen_rec.PRTT_ENRT_RSLT_ID , 99 );
5539: close c_bkp_row;
5540: --
5541: insert into BEN_LE_CLSN_N_RSTR (
5542: BKUP_TBL_TYP_CD,
5543: COMP_LVL_CD,
5544: LCR_ATTRIBUTE16,
5545: LCR_ATTRIBUTE17,
5701: else
5702: --
5703: close c_bkp_row;
5704: --
5705: update BEN_LE_CLSN_N_RSTR set
5706: COMP_LVL_CD = l_deleted_pen_rec.COMP_LVL_CD,
5707: LCR_ATTRIBUTE16 = l_deleted_pen_rec.PEN_ATTRIBUTE16,
5708: LCR_ATTRIBUTE17 = l_deleted_pen_rec.PEN_ATTRIBUTE17,
5709: LCR_ATTRIBUTE18 = l_deleted_pen_rec.PEN_ATTRIBUTE18,
5881: then
5882: --
5883: close c_bkp_row;
5884: hr_utility.set_location(' creating backup ' || l_pk_id, 999);
5885: insert into BEN_LE_CLSN_N_RSTR (
5886: BKUP_TBL_TYP_CD,
5887: COMP_LVL_CD,
5888: LCR_ATTRIBUTE16,
5889: LCR_ATTRIBUTE17,
6045: else
6046: --
6047: close c_bkp_row;
6048: --
6049: update BEN_LE_CLSN_N_RSTR set
6050: COMP_LVL_CD = l_deleted_pen_rec.COMP_LVL_CD,
6051: LCR_ATTRIBUTE16 = l_deleted_pen_rec.PEN_ATTRIBUTE16,
6052: LCR_ATTRIBUTE17 = l_deleted_pen_rec.PEN_ATTRIBUTE17,
6053: LCR_ATTRIBUTE18 = l_deleted_pen_rec.PEN_ATTRIBUTE18,
6194:
6195:
6196: --- if result for the per_in_ler_id is found in 'BEN_PRTT_ENRT_RSLT_F_CORR
6197: --- correct the result with with per_in_ler_id and coverage dates bug # 3086161
6198: for i in c_BEN_LE_CLSN_N_RSTR_corr(p_per_in_ler_id)
6199: Loop
6200:
6201: hr_utility.set_location(' corrected result ' || i.bkup_tbl_id, 99 );
6202: if l_prev_bkup_tbl_id <> i.bkup_tbl_id and nvl(p_copy_only,'N') <> 'Y' then
6271: and i.pen_effective_end_date <> i.effective_end_date) then
6272: hr_utility.set_location(' correcting ' || i.bkup_tbl_id, 999 );
6273: hr_utility.set_location(' i.per_in_ler_id ' || i.per_in_ler_id, 999 );
6274: hr_utility.set_location(' p_per_in_ler_id ' || p_per_in_ler_id, 999 );
6275: delete from ben_le_clsn_n_rstr cqb
6276: where cqb.per_in_ler_id = i.per_in_ler_id
6277: and cqb.per_in_ler_ended_id = p_per_in_ler_id
6278: and cqb.bkup_tbl_id = i.bkup_tbl_id ;
6279: else
6277: and cqb.per_in_ler_ended_id = p_per_in_ler_id
6278: and cqb.bkup_tbl_id = i.bkup_tbl_id ;
6279: else
6280: --
6281: update ben_le_clsn_n_rstr cqb
6282: set cqb.per_in_ler_id = p_per_in_ler_id
6283: , cqb.per_in_ler_ended_id = null
6284: , cqb.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F'
6285: , cqb.enrt_mthd_cd = l_get_enrt_mthd_cd
6293: --
6294: /* Bug 6034585 : Commented out the update statement as it was
6295: updating per_in_ler_id field with the incorrect per_in_ler_id
6296: --bug#3702033 - for reinstate retain the row with necessary updates
6297: update ben_le_clsn_n_rstr cqb
6298: set cqb.per_in_ler_id = p_per_in_ler_id,
6299: cqb.per_in_ler_ended_id = null,
6300: cqb.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F'
6301: where cqb.per_in_ler_id = i.per_in_ler_id
6306: end loop ;
6307: --
6308: l_prev_bkup_tbl_id := -1;
6309: --
6310: for i in c_BEN_LE_CLSN_N_RSTR_del(p_per_in_ler_id)
6311: Loop
6312: hr_utility.set_location(' delete result ' || i.bkup_tbl_id, 99 );
6313: if l_prev_bkup_tbl_id <> i.bkup_tbl_id then
6314:
6365: elsif p_routine = 'BEN_BNFT_PRVDD_LDGR_F' then
6366: --
6367: -- Bug 5500864
6368: -- This part of code has been added to take care of reinstatements of BPLs that have been END-DATED
6369: -- Such BPL Records have been backed up in BEN_LE_CLSN_N_RSTR. See BEN_BPL_DEL.POST_DELETE
6370: -- The part of the code below (which queries cursor c_ben_bnft_prvdd_ldgr_f) takes care of rest
6371: -- of the scenarios and voiding of BPL associated with the life-event being backed out.
6372: --
6373: hr_utility.set_location('ACE p_per_in_ler_id = ' || p_per_in_ler_id, 9999);
6390: hr_utility.set_location('ACE Reopened = ' || l_bpl_from_backup.BKUP_TBL_ID, 9999);
6391: --
6392: -- Bug 6376239 : Remove the rows from backup table once the ledger rows are restored
6393: --
6394: delete from ben_le_clsn_n_rstr cls
6395: where cls.per_in_ler_id = l_bpl_from_backup.per_in_ler_id
6396: and cls.bkup_tbl_id = l_bpl_from_backup.bkup_tbl_id
6397: and cls.bkup_tbl_typ_cd = 'BEN_BNFT_PRVDD_LDGR_F'
6398: and effective_start_date = l_bpl_from_backup.effective_start_date
6537: then
6538: --
6539: close c_bkp_row;
6540: --
6541: insert into BEN_LE_CLSN_N_RSTR(
6542: bkup_tbl_typ_cd,
6543: bkup_tbl_id,
6544: elig_flag,
6545: elig_strt_dt,
6564: else
6565: --
6566: close c_bkp_row;
6567: --
6568: update BEN_LE_CLSN_N_RSTR set
6569: elig_flag = l_cqb_rec.quald_bnf_flag,
6570: elig_strt_dt = l_cqb_rec.cbr_elig_perd_strt_dt,
6571: elig_thru_dt = l_cqb_rec.cbr_elig_perd_end_dt,
6572: inelg_rsn_cd = l_cqb_rec.cbr_inelg_rsn_cd,