[Home] [Help]
51: - BEN_PER_CM_USG_F
52: BEN_BNFT_PRVDD_LDGR_F
53: BEN_PRMRY_CARE_PRVDR_F
54: Delete logic changed for
55: BEN_PRTT_ENRT_RSLT_F
56: BEN_ELIG_CVRD_DPNT_F
57: BEN_PL_BNF_F
58: BEN_PRTT_RT_VAL and
59: communication tables
91: Removed because of per_in_ler status checks
92: 08-jul-99 pbodla 115.17 Code to backup data from ben_pl_bnf_f, BEN_PRTT_RT_VAL,
93: ben_prtt_prem, ben_elig_per, ben_elig_per_opt
94: ben_elig_cvrd_dpnt_f, ben_pil_elctbl_chc_popl,
95: ben_prtt_enrt_rslt_f added.
96: 09-jul-99 shdas 115.18 Moved unprocess_ptnl_ler proc here from benptnle.
97: 15-jul-99 jcarpent 115.19 Added calls to ben_ext_chlg.log_benefit_chg
98: 24-Aug-99 maagrawa 115.20 Dependents table changes.
99: 31-Aug-99 gperry 115.21 Changed call to ptnl_ler_for_per
206: BEN_ELIG_PER_F
207: BEN_ELIG_PER_OPT_F
208: BEN_PRTT_PREM_F
209: BEN_PL_BNF_F
210: BEN_PRTT_ENRT_RSLT_F
211: 14-Sep-2000 jcarpent 115.51 Bug 1269016. added bolfe
212: effective_date global.
213: 27-SEP-2000 cdaniels 115.52 - Tar# 1090241.996. Eliminated test
214: for not NULL l_person_id prior to
380: to null when an event is backed out.
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
392: 28-Nov-2006 gsehgal 115.135 bug 5668052 deleting person type usage for beneficiary
393: 23-feb-2007 ssarkar 115.136 bug 5895645 typo fix for c_pbn_max_esd_of_past_pil
394: and dependent when back out life event
395: 09-May-2007 swjain 115.137 Bug 6034585: Updated procedure delete_routine -
396: p_routine = 'BEN_PRTT_ENRT_RSLT_F'
397: 11-May-2007 ikasired 115.138 Bug 5985777 Added new procedure to reinstate the
398: completed actions items from the last life event.
399: 26-Jun-2007 mkommuri 115.139 Bug6152593 updated cursor
400: c_pep_max_esd_of_past_pil
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
1229: AND leclr.effective_end_date = hr_api.g_eot
1228: AND leclr.prtt_enrt_rslt_stat_cd IS NULL
1229: AND leclr.effective_end_date = hr_api.g_eot
1230: AND leclr.enrt_cvg_strt_dt > p_lf_evt_ocrd_dt
1231: AND leclr.enrt_cvg_strt_dt < leclr.effective_end_date
1232: AND leclr.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
1233: AND leclr.bkup_tbl_id = pen.prtt_enrt_rslt_id
1234: AND pen.per_in_ler_id = p_per_in_ler_id
1235: AND pen.prtt_enrt_rslt_stat_cd = 'BCKDT'
1236: AND pen.effective_end_date = hr_api.g_eot
1280: -- Tables that will be deleted from include and must be deleted from in
1281: -- a bottom to top fashion :
1282: --
1283: -- BEN_PER_IN_LER (PIL)
1284: -- BEN_PRTT_ENRT_RSLT_F (PEN)
1285: -- BEN_PL_BNF_F (PBN) (join to pil to check status)
1286: -- BEN_PRTT_ENRT_ACTN_F (PEA) (join to pil to check status)
1287: -- BEN_BNFT_PRVDD_LDGR_F (BPL)(join to pil to check status)
1288: -- BEN_PRTT_RT_VAL (PRV)
1452: p_copy_only => p_copy_only,
1453: p_effective_date => p_effective_date);
1454:
1455: -- mark results 'backed out'
1456: delete_routine(p_routine => 'BEN_PRTT_ENRT_RSLT_F',
1457: p_per_in_ler_id => p_per_in_ler_id,
1458: p_business_group_id => p_business_group_id,
1459: p_bckdt_prtt_enrt_rslt_id => p_bckdt_prtt_enrt_rslt_id,
1460: p_copy_only => p_copy_only,
1913: l_prior_to_prv_rslt_lvl_bckdt.LCR_ATTRIBUTE27,
1914: l_prior_to_prv_rslt_lvl_bckdt.LCR_ATTRIBUTE28,
1915: l_prior_to_prv_rslt_lvl_bckdt.LCR_ATTRIBUTE29,
1916: l_prior_to_prv_rslt_lvl_bckdt.BKUP_TBL_ID,
1917: 'BEN_PRTT_ENRT_RSLT_F_CORR',
1918: l_prior_to_prv_rslt_lvl_bckdt.PER_IN_LER_ID,
1919: l_prior_to_prv_rslt_lvl_bckdt.EFFECTIVE_START_DATE,
1920: l_prior_to_prv_rslt_lvl_bckdt.EFFECTIVE_END_DATE,
1921: l_prior_to_prv_rslt_lvl_bckdt.ENRT_CVG_STRT_DT,
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'
1989: AND business_group_id = p_business_group_id
1990: AND prtt_enrt_rslt_stat_cd IS NULL
1991: AND effective_end_date = hr_api.g_eot
1992: AND enrt_cvg_strt_dt > l_prv_lf_evt_ocrd_dt
2248: --
2249: cursor c_future_prv is
2250: select prv.*
2251: from ben_prtt_rt_val prv,
2252: ben_prtt_enrt_rslt_f pen,
2253: ben_acty_base_rt_f abr
2254: where prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
2255: and pen.person_id = p_person_id
2256: and prv.prtt_rt_val_stat_cd is null
2435: and epo.per_in_ler_id = pil.per_in_ler_id
2436: and epo.business_group_id = pil.business_group_id
2437: --
2438: -- Do not use the effective date check, see
2439: -- comments at c_ben_prtt_enrt_rslt_f
2440: -- Rows should be selected based on per in ler only.
2441: --
2442: /*
2443: -- RCHASE Bug#5364 Must use pil.lf_evt_ocrd_dt instead of p_effective_date
2469: and nvl(per_in_ler_id , -1) <> p_per_in_ler_id
2470: and epo.business_group_id = p_business_group_id;
2471: --
2472: -- Do not use the effective date check, see
2473: -- comments at c_ben_prtt_enrt_rslt_f
2474: -- Rows should be selected based on per in ler only.
2475: --
2476: */
2477: /*
2522: and pep.per_in_ler_id = pil.per_in_ler_id
2523: and pep.business_group_id = pil.business_group_id
2524: --
2525: -- Do not use the effective date check, see
2526: -- comments at c_ben_prtt_enrt_rslt_f
2527: -- Rows should be selected based on per in ler only.
2528: --
2529: /*
2530: -- RCHASE Bug#5364 Must use pil.lf_evt_ocrd_dt instead of p_effective_date
2564:
2565: -- changed 7176884 begin
2566: Cursor c_get_enrt_mthd_cd(c_prtt_enrt_rslt_id in number) is
2567: select enrt_mthd_cd
2568: from ben_prtt_enrt_rslt_f
2569: where prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
2570: and per_in_ler_id = p_per_in_ler_id
2571: and business_group_id = p_business_group_id
2572: and prtt_enrt_rslt_stat_cd is null
2571: and business_group_id = p_business_group_id
2572: and prtt_enrt_rslt_stat_cd is null
2573: order by effective_start_date desc;
2574:
2575: l_get_enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type;
2576: -- changed 7176884 end
2577: --
2578: -- Move deleted records to backup table
2579: --
2634: and ppe.per_in_ler_id = pil.per_in_ler_id
2635: and ppe.business_group_id = pil.business_group_id
2636: --
2637: -- Do not use the effective date check, see
2638: -- comments at c_ben_prtt_enrt_rslt_f
2639: -- Rows should be selected based on per in ler only.
2640: --
2641: /*
2642: and p_effective_date
2689: -- Source Table
2690: -- BEN_PER_IN_LER
2691: --
2692: -- Bug 5222 : Removed the effective date check.
2693: -- See comments at c_ben_prtt_enrt_rslt_f cursor.
2694: --
2695: cursor c_ben_elig_cvrd_dpnt_f is
2696: select pdp.elig_cvrd_dpnt_id,
2697: pdp.object_version_number,
2766: and pbn.per_in_ler_id = pil.per_in_ler_id
2767: and pbn.business_group_id = pil.business_group_id
2768: --
2769: -- Do not use the effective date check, see
2770: -- comments at c_ben_prtt_enrt_rslt_f
2771: -- Rows should be selected based on per in ler only.
2772: --
2773: /*
2774: and p_effective_date
2826: --
2827: -- Source Table
2828: -- BEN_PER_IN_LER
2829: -- Drive Routes
2830: -- BEN_PRTT_ENRT_RSLT_F
2831: --
2832: --
2833: cursor c_ben_prmry_care_prvdr_f is
2834: select ppr.prmry_care_prvdr_id,
2834: select ppr.prmry_care_prvdr_id,
2835: ppr.effective_start_date,
2836: ppr.object_version_number
2837: from ben_prmry_care_prvdr_f ppr,
2838: ben_prtt_enrt_rslt_f pen,
2839: ben_per_in_ler pil
2840: where pil.per_in_ler_id = p_per_in_ler_id
2841: and pil.business_group_id = p_business_group_id
2842: and pen.per_in_ler_id = pil.per_in_ler_id
2847: and ppr.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
2848: and ppr.business_group_id = pen.business_group_id
2849: --
2850: -- Do not use the effective date check, see
2851: -- comments at c_ben_prtt_enrt_rslt_f
2852: -- Rows should be selected based on per in ler only.
2853: --
2854: /*
2855: and p_effective_date
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;
2953: --
2952: l_cvg_adj c_prtt_enrt_rslt_adj%rowtype;
2953: --
2954: cursor c_pen_ovn (p_prtt_enrt_rslt_id in number) is
2955: select object_version_number
2956: from ben_prtt_enrt_rslt_f
2957: where prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
2958: and effective_end_date = hr_api.g_eot
2959: and prtt_enrt_rslt_stat_cd is null;
2960: -- End bug 7206471
2967: select pen.prtt_enrt_rslt_id,
2968: pen.object_version_number,
2969: pen.effective_end_date,
2970: pen.effective_start_date
2971: from ben_prtt_enrt_rslt_f pen
2972: where pen.business_group_id = p_business_group_id
2973: and pen.prtt_enrt_rslt_id = cp_prtt_enrt_rslt_id
2974: -- Commented per bug 1584238 and 1627373
2975: -- and cv_rt_strt_dt >= pen.enrt_cvg_strt_dt
2992: and prv.business_group_id = pil.business_group_id
2993: --START BUG 3495372
2994: and prv.prtt_enrt_rslt_id in
2995: (select prtt_enrt_rslt_id
2996: from ben_prtt_enrt_rslt_f
2997: where prtt_enrt_rslt_stat_cd is not null
2998: and sspndd_flag = 'N'
2999: ) -- to leave out Suspended results
3000: --END BUG 3495372
2997: where prtt_enrt_rslt_stat_cd is not null
2998: and sspndd_flag = 'N'
2999: ) -- to leave out Suspended results
3000: --END BUG 3495372
3001: and not exists (select null from ben_prtt_enrt_rslt_f where
3002: prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
3003: and prtt_enrt_rslt_stat_cd is not null ) -- to leave out VOIDD results
3004: group by prv.acty_base_rt_id
3005: having count(*) > 1;
3012: from ben_prtt_rt_val prv
3013: where prv.per_in_ler_id = p_per_in_ler_id
3014: and prv.prtt_rt_val_stat_cd is null
3015: and exists (select null
3016: from ben_prtt_enrt_rslt_f pen
3017: where pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
3018: and pen.prtt_enrt_rslt_stat_cd is null
3019: and pen.sspndd_flag = 'N' ) -- to select only not voided and unsuspended results
3020: group by prv.acty_base_rt_id,prv.rt_strt_dt --BUG 4558512 otherwise.. we get into issue with FLAT RANGE
3046: --
3047: --
3048: -- END ----> DELETE ROUTINE FOR BEN_PRTT_RT_VAL
3049: --
3050: -- START ----> DELETE ROUTINE FOR BEN_PRTT_ENRT_RSLT_F
3051: --
3052: -- Source Table
3053: -- BEN_PER_IN_LER
3054: --
3051: --
3052: -- Source Table
3053: -- BEN_PER_IN_LER
3054: --
3055: cursor c_ben_prtt_enrt_rslt_f is
3056: select pen.prtt_enrt_rslt_id,
3057: pen.object_version_number,
3058: pen.pl_id,
3059: pen.oipl_id,
3063: pen.effective_end_date,
3064: pen.effective_start_date,
3065: pen.per_in_ler_id
3066: ,pil.lf_evt_ocrd_dt
3067: from ben_prtt_enrt_rslt_f pen,
3068: ben_per_in_ler pil
3069: where pil.per_in_ler_id = p_per_in_ler_id
3070: and pil.business_group_id = p_business_group_id
3071: and pen.per_in_ler_id = pil.per_in_ler_id
3079: --- make sure results of prev per in ler id
3080: --- currected by curr per_in_ler_id due to same date enrollment
3081: --- are not backedout bug # 3086161
3082: --- after the # 3175382 new cursor created and the validation moved
3083: -- from c_ben_prtt_enrt_rslt_f. because for current per in ler id the backout entry has to be created
3084: --- unless the current per_in_ler can not be reinstated. so the cursor created to validate
3085: --- the delting part ofthe result. the result can not be deleted because the prev correctecd
3086: --- result has to be restored with the prev per in ler id
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'
3096: and lcnr.enrt_cvg_thru_dt = hr_api.g_eot --bug#5032364
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'
3096: and lcnr.enrt_cvg_thru_dt = hr_api.g_eot --bug#5032364
3097: and lcnr.per_in_ler_ended_id = pen.per_in_ler_id -- Bug 6632568
3098: and pen.per_in_ler_id = l_per_in_ler_id -- Bug 6632568
3099: and lcnr.effective_start_date between pen.effective_start_date -- Bug 6632568
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
3138: --bug#5032364
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
3138: --bug#5032364
3139: --and (pen.per_in_ler_id = lcnr.per_in_ler_ended_id
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
3167: --bug#5032364
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
3167: --bug#5032364
3168: --and (pen.per_in_ler_id = lcnr.per_in_ler_ended_id
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
3193: and (pdp.per_in_ler_id = lcnr.per_in_ler_ended_id
3194: or pdp.per_in_ler_id = lcnr.per_in_ler_id )
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
3216: and (pbn.per_in_ler_id = lcnr.per_in_ler_ended_id
3217: or pbn.per_in_ler_id = lcnr.per_in_ler_id )
3228: -- Bug#2821279 : Removed the max functions and logic is based
3229: -- on order by clause.
3230: cursor c_pen_max_esd_of_past_pil(v_prtt_enrt_rslt_id in number) is
3231: select pen.effective_end_date,pen.object_version_number, pen.per_in_ler_id
3232: from ben_prtt_enrt_rslt_f pen,
3233: ben_per_in_ler pil
3234: where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
3235: and nvl(pen.per_in_ler_id , -1) <> p_per_in_ler_id
3236: and pen.business_group_id = p_business_group_id
3239: and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
3240: order by pen.effective_end_date desc;
3241: --
3242: --
3243: l_prev_per_in_ler_id ben_prtt_enrt_rslt_f.per_in_ler_id%type;
3244: --
3245: -- CFW
3246: -- Move deleted records to backup table
3247: --
3247: --
3248: cursor c_deleted_pen(v_prtt_enrt_rslt_id in number,
3249: v_effective_date in date) is
3250: select *
3251: from ben_prtt_enrt_rslt_f
3252: where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
3253: and nvl(per_in_ler_id , -1) = p_per_in_ler_id
3254: and business_group_id = p_business_group_id
3255: and effective_end_date > v_effective_date;
3268: --
3269: l_epe c_epe%rowtype;
3270: --
3271: --
3272: -- END ----> DELETE ROUTINE FOR BEN_PRTT_ENRT_RSLT_F
3273: --
3274: --
3275: -- START ----> DELETE ROUTINE FOR BEN_BNFT_PRVDD_LDGR_F
3276: cursor c_ben_bnft_prvdd_ldgr_f(p_bnft_prvdd_ldgr_id number) is
3291: and (p_bnft_prvdd_ldgr_id is null
3292: or p_bnft_prvdd_ldgr_id = bpl.bnft_prvdd_ldgr_id
3293: )
3294: --bug#3702090
3295: and exists (select null from ben_prtt_enrt_rslt_f pen
3296: where pen.prtt_enrt_rslt_id = bpl.prtt_enrt_rslt_id)
3297: order by 1;
3298: --
3299: l_bpl c_ben_bnft_prvdd_ldgr_f%rowtype;
3405:
3406: --# bug 2546259 this cursor find the result voided by the LE
3407:
3408: cursor c_futur_pen is
3409: select pen.prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f pen
3410: where per_in_ler_id = p_per_in_ler_id
3411: and pen.prtt_enrt_rslt_stat_cd = 'VOIDD'
3412: and pen.effective_start_date < pen.enrt_cvg_strt_dt ;
3413:
3430: --
3431: cursor c_pen_sus is
3432: select pen.prtt_enrt_rslt_id,
3433: pen.per_in_ler_id
3434: from ben_prtt_enrt_rslt_f pen,
3435: ben_per_in_ler pil
3436: where pen.sspndd_flag = 'Y'
3437: and pen.per_in_ler_id <> pil.per_in_ler_id
3438: and pil.per_in_ler_id = p_per_in_ler_id
5425: end loop;
5426: close c_prtt_rt_val_adj;
5427: --
5428:
5429: elsif p_routine = 'BEN_PRTT_ENRT_RSLT_F' then
5430:
5431: hr_utility.set_location( 'backing out BEN_PRTT_ENRT_RSLT_F ' || p_per_in_ler_id , 99 );
5432: hr_utility.set_location( 'p_bckdt_prtt_enrt_rslt_id ' || p_bckdt_prtt_enrt_rslt_id , 99 );
5433: -- Results need to be 'un-ended' if they were ended due to this
5427: --
5428:
5429: elsif p_routine = 'BEN_PRTT_ENRT_RSLT_F' then
5430:
5431: hr_utility.set_location( 'backing out BEN_PRTT_ENRT_RSLT_F ' || p_per_in_ler_id , 99 );
5432: hr_utility.set_location( 'p_bckdt_prtt_enrt_rslt_id ' || p_bckdt_prtt_enrt_rslt_id , 99 );
5433: -- Results need to be 'un-ended' if they were ended due to this
5434: -- per-in-ler. The 'future change' date track mode handles that.
5435:
5457: end if ;
5458:
5459: l_prev_pk_id := -1; -- like null
5460: g_enrt_made_flag := 'N';
5461: open c_ben_prtt_enrt_rslt_f;
5462: loop
5463:
5464: fetch c_ben_prtt_enrt_rslt_f into l_pk_id,
5465: l_object_version_number,
5460: g_enrt_made_flag := 'N';
5461: open c_ben_prtt_enrt_rslt_f;
5462: loop
5463:
5464: fetch c_ben_prtt_enrt_rslt_f into l_pk_id,
5465: l_object_version_number,
5466: l_pl_id,
5467: l_oipl_id,
5468: l_enrt_cvg_strt_dt,
5473: l_pen_pil_id,
5474: -- CFW
5475: l_lf_evt_ocrd_dt;
5476: -- CFW
5477: if c_ben_prtt_enrt_rslt_f%found then
5478: g_enrt_made_flag := 'Y';
5479: end if;
5480: exit when c_ben_prtt_enrt_rslt_f%notfound;
5481:
5476: -- CFW
5477: if c_ben_prtt_enrt_rslt_f%found then
5478: g_enrt_made_flag := 'Y';
5479: end if;
5480: exit when c_ben_prtt_enrt_rslt_f%notfound;
5481:
5482: -- Get the maximum of effective end date for the enrollment
5483: -- result with past per_in_ler 's.
5484: --
5525: --
5526: --
5527: hr_utility.set_location( ' backup pil ' || l_deleted_pen_rec.PER_IN_LER_ID , 99 );
5528: hr_utility.set_location( ' backup pen ' || l_deleted_pen_rec.PRTT_ENRT_RSLT_ID , 99 );
5529: open c_bkp_row('BEN_PRTT_ENRT_RSLT_F',
5530: l_deleted_pen_rec.PER_IN_LER_ID,
5531: l_deleted_pen_rec.PRTT_ENRT_RSLT_ID,
5532: l_deleted_pen_rec.object_version_number);
5533: fetch c_bkp_row into l_row_id;
5617: PLIP_ORDR_NUM,
5618: PTIP_ORDR_NUM,
5619: OIPL_ORDR_NUM)
5620: values (
5621: 'BEN_PRTT_ENRT_RSLT_F',
5622: l_deleted_pen_rec.COMP_LVL_CD,
5623: l_deleted_pen_rec.PEN_ATTRIBUTE16,
5624: l_deleted_pen_rec.PEN_ATTRIBUTE17,
5625: l_deleted_pen_rec.PEN_ATTRIBUTE18,
5870: for l_deleted_pen_rec in c_deleted_pen(l_pk_id, l_pen_esd)
5871: loop
5872: --
5873: --
5874: open c_bkp_row('BEN_PRTT_ENRT_RSLT_F',
5875: l_deleted_pen_rec.PER_IN_LER_ID,
5876: l_deleted_pen_rec.PRTT_ENRT_RSLT_ID,
5877: l_deleted_pen_rec.object_version_number);
5878: fetch c_bkp_row into l_row_id;
5961: PLIP_ORDR_NUM,
5962: PTIP_ORDR_NUM,
5963: OIPL_ORDR_NUM)
5964: values (
5965: 'BEN_PRTT_ENRT_RSLT_F',
5966: l_deleted_pen_rec.COMP_LVL_CD,
5967: l_deleted_pen_rec.PEN_ATTRIBUTE16,
5968: l_deleted_pen_rec.PEN_ATTRIBUTE17,
5969: l_deleted_pen_rec.PEN_ATTRIBUTE18,
6179: end loop;
6180: --
6181: l_prev_pk_id := -1;
6182: --
6183: close c_ben_prtt_enrt_rslt_f;
6184: -- after result backed out determine the person type usage
6185: -- since the backout not calling the multiedit
6186: -- person type usage is not updated # 2899702
6187: ben_pen_bus.manage_per_type_usages
6192: --
6193: l_prev_pk_id := -1;
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:
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
6286: where cqb.per_in_ler_id = i.per_in_ler_id
6287: and cqb.per_in_ler_ended_id = p_per_in_ler_id
6288: and cqb.bkup_tbl_id = i.bkup_tbl_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
6302: and cqb.per_in_ler_ended_id = p_per_in_ler_id
6303: and cqb.bkup_tbl_id = i.bkup_tbl_id ;
6304: --
6647: close c_prv_of_previous_pil;
6648: --
6649: end if;
6650: --
6651: if c_ben_prtt_enrt_rslt_f%isopen then
6652: --
6653: close c_ben_prtt_enrt_rslt_f;
6654: --
6655: end if;
6649: end if;
6650: --
6651: if c_ben_prtt_enrt_rslt_f%isopen then
6652: --
6653: close c_ben_prtt_enrt_rslt_f;
6654: --
6655: end if;
6656: --
6657: if c_ben_pil_elctbl_chc_popl%isopen then