[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
502: epe.elig_per_elctbl_chc_id,
503: epe.object_version_number,
504: epe.prtt_enrt_rslt_id
505: from
506: ben_prtt_enrt_rslt_f pen,
507: ben_per_in_ler pil,
508: ben_elig_per_elctbl_chc epe
509: where pen.prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
510: and pen.effective_end_date = hr_api.g_eot
1531:
1532: /* Added for Bug 8604243 */
1533:
1534: cursor c_chk_enrt_prev_le(c_per_in_ler_id number) is
1535: select 'Y' from ben_prtt_enrt_rslt_f pen
1536: where prtt_enrt_rslt_stat_cd is null
1537: and per_in_ler_id = c_per_in_ler_id
1538: and enrt_cvg_strt_dt < enrt_cvg_thru_dt;
1539:
1550: and p_effective_date between
1551: ler.effective_start_date and ler.effective_end_date
1552: and ler.typ_cd not in ('IREC', 'SCHEDDU', 'COMP', 'GSP', 'ABS')
1553: and exists
1554: (select 'Y' from ben_prtt_enrt_rslt_f pen
1555: where pen.per_in_ler_id = pil.per_in_ler_id
1556: and pen.prtt_enrt_rslt_stat_cd is null
1557: and pen.enrt_cvg_strt_dt < pen.enrt_cvg_thru_dt)
1558: order by lf_evt_ocrd_dt desc ;
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
1577: AND leclr.effective_end_date = hr_api.g_eot
1576: AND leclr.prtt_enrt_rslt_stat_cd IS NULL
1577: AND leclr.effective_end_date = hr_api.g_eot
1578: AND leclr.enrt_cvg_strt_dt > p_lf_evt_ocrd_dt
1579: AND leclr.enrt_cvg_strt_dt < leclr.effective_end_date
1580: AND leclr.bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F'
1581: AND leclr.bkup_tbl_id = pen.prtt_enrt_rslt_id
1582: AND pen.per_in_ler_id = p_per_in_ler_id
1583: AND pen.prtt_enrt_rslt_stat_cd = 'BCKDT'
1584: AND pen.effective_end_date = hr_api.g_eot
1628: -- Tables that will be deleted from include and must be deleted from in
1629: -- a bottom to top fashion :
1630: --
1631: -- BEN_PER_IN_LER (PIL)
1632: -- BEN_PRTT_ENRT_RSLT_F (PEN)
1633: -- BEN_PL_BNF_F (PBN) (join to pil to check status)
1634: -- BEN_PRTT_ENRT_ACTN_F (PEA) (join to pil to check status)
1635: -- BEN_BNFT_PRVDD_LDGR_F (BPL)(join to pil to check status)
1636: -- BEN_PRTT_RT_VAL (PRV)
1800: p_copy_only => p_copy_only,
1801: p_effective_date => p_effective_date);
1802:
1803: -- mark results 'backed out'
1804: delete_routine(p_routine => 'BEN_PRTT_ENRT_RSLT_F',
1805: p_per_in_ler_id => p_per_in_ler_id,
1806: p_business_group_id => p_business_group_id,
1807: p_bckdt_prtt_enrt_rslt_id => p_bckdt_prtt_enrt_rslt_id,
1808: p_copy_only => p_copy_only,
2298: l_prior_to_prv_rslt_lvl_bckdt.LCR_ATTRIBUTE27,
2299: l_prior_to_prv_rslt_lvl_bckdt.LCR_ATTRIBUTE28,
2300: l_prior_to_prv_rslt_lvl_bckdt.LCR_ATTRIBUTE29,
2301: l_prior_to_prv_rslt_lvl_bckdt.BKUP_TBL_ID,
2302: 'BEN_PRTT_ENRT_RSLT_F_CORR',
2303: l_prior_to_prv_rslt_lvl_bckdt.PER_IN_LER_ID,
2304: l_prior_to_prv_rslt_lvl_bckdt.EFFECTIVE_START_DATE,
2305: l_prior_to_prv_rslt_lvl_bckdt.EFFECTIVE_END_DATE,
2306: l_prior_to_prv_rslt_lvl_bckdt.ENRT_CVG_STRT_DT,
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'
2374: AND business_group_id = p_business_group_id
2375: AND prtt_enrt_rslt_stat_cd IS NULL
2376: AND effective_end_date = hr_api.g_eot
2377: AND enrt_cvg_strt_dt > l_prv_lf_evt_ocrd_dt
2633: --
2634: cursor c_future_prv is
2635: select prv.*
2636: from ben_prtt_rt_val prv,
2637: ben_prtt_enrt_rslt_f pen,
2638: ben_acty_base_rt_f abr
2639: where prv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
2640: and pen.person_id = p_person_id
2641: and prv.prtt_rt_val_stat_cd is null
2821: and epo.per_in_ler_id = pil.per_in_ler_id
2822: and epo.business_group_id = pil.business_group_id
2823: --
2824: -- Do not use the effective date check, see
2825: -- comments at c_ben_prtt_enrt_rslt_f
2826: -- Rows should be selected based on per in ler only.
2827: --
2828: /*
2829: -- RCHASE Bug#5364 Must use pil.lf_evt_ocrd_dt instead of p_effective_date
2855: and nvl(per_in_ler_id , -1) <> p_per_in_ler_id
2856: and epo.business_group_id = p_business_group_id;
2857: --
2858: -- Do not use the effective date check, see
2859: -- comments at c_ben_prtt_enrt_rslt_f
2860: -- Rows should be selected based on per in ler only.
2861: --
2862: */
2863: /*
2909: and pep.per_in_ler_id = pil.per_in_ler_id
2910: and pep.business_group_id = pil.business_group_id
2911: --
2912: -- Do not use the effective date check, see
2913: -- comments at c_ben_prtt_enrt_rslt_f
2914: -- Rows should be selected based on per in ler only.
2915: --
2916: /*
2917: -- RCHASE Bug#5364 Must use pil.lf_evt_ocrd_dt instead of p_effective_date
2951:
2952: -- changed 7176884 begin
2953: Cursor c_get_enrt_mthd_cd(c_prtt_enrt_rslt_id in number) is
2954: select enrt_mthd_cd
2955: from ben_prtt_enrt_rslt_f
2956: where prtt_enrt_rslt_id = c_prtt_enrt_rslt_id
2957: and per_in_ler_id = p_per_in_ler_id
2958: and business_group_id = p_business_group_id
2959: and prtt_enrt_rslt_stat_cd is null
2958: and business_group_id = p_business_group_id
2959: and prtt_enrt_rslt_stat_cd is null
2960: order by effective_start_date desc;
2961:
2962: l_get_enrt_mthd_cd ben_prtt_enrt_rslt_f.enrt_mthd_cd%type;
2963: -- changed 7176884 end
2964: --
2965: -- Bug 8199189
2966: --
2967: Cursor c_get_cvg_thru_dt (p_prtt_enrt_rslt_id in number
2968: ,p_effective_date in date) is
2969: select enrt_cvg_thru_dt
2970: ,prtt_enrt_rslt_stat_cd
2971: from ben_prtt_enrt_rslt_f pen
2972: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
2973: and pen.business_group_id = p_business_group_id
2974: and pen.enrt_cvg_thru_dt <> hr_api.g_eot
2975: -- and pen.prtt_enrt_rslt_stat_cd is null
2976: and p_effective_date
2977: between pen.effective_start_date
2978: and pen.effective_end_date;
2979:
2980: l_cvg_thru_dt ben_prtt_enrt_rslt_f.enrt_cvg_thru_dt%type;
2981: l_prtt_enrt_rslt_stat_cd ben_prtt_enrt_rslt_f.prtt_enrt_rslt_stat_cd%type;
2982: -- end 8199189
2983: --
2984: -- Move deleted records to backup table
2977: between pen.effective_start_date
2978: and pen.effective_end_date;
2979:
2980: l_cvg_thru_dt ben_prtt_enrt_rslt_f.enrt_cvg_thru_dt%type;
2981: l_prtt_enrt_rslt_stat_cd ben_prtt_enrt_rslt_f.prtt_enrt_rslt_stat_cd%type;
2982: -- end 8199189
2983: --
2984: -- Move deleted records to backup table
2985: --
3040: and ppe.per_in_ler_id = pil.per_in_ler_id
3041: and ppe.business_group_id = pil.business_group_id
3042: --
3043: -- Do not use the effective date check, see
3044: -- comments at c_ben_prtt_enrt_rslt_f
3045: -- Rows should be selected based on per in ler only.
3046: --
3047: /*
3048: and p_effective_date
3095: -- Source Table
3096: -- BEN_PER_IN_LER
3097: --
3098: -- Bug 5222 : Removed the effective date check.
3099: -- See comments at c_ben_prtt_enrt_rslt_f cursor.
3100: --
3101: cursor c_ben_elig_cvrd_dpnt_f is
3102: select pdp.elig_cvrd_dpnt_id,
3103: pdp.object_version_number,
3172: and pbn.per_in_ler_id = pil.per_in_ler_id
3173: and pbn.business_group_id = pil.business_group_id
3174: --
3175: -- Do not use the effective date check, see
3176: -- comments at c_ben_prtt_enrt_rslt_f
3177: -- Rows should be selected based on per in ler only.
3178: --
3179: /*
3180: and p_effective_date
3232: --
3233: -- Source Table
3234: -- BEN_PER_IN_LER
3235: -- Drive Routes
3236: -- BEN_PRTT_ENRT_RSLT_F
3237: --
3238: --
3239: cursor c_ben_prmry_care_prvdr_f is
3240: select ppr.prmry_care_prvdr_id,
3240: select ppr.prmry_care_prvdr_id,
3241: ppr.effective_start_date,
3242: ppr.object_version_number
3243: from ben_prmry_care_prvdr_f ppr,
3244: ben_prtt_enrt_rslt_f pen,
3245: ben_per_in_ler pil
3246: where pil.per_in_ler_id = p_per_in_ler_id
3247: and pil.business_group_id = p_business_group_id
3248: and pen.per_in_ler_id = pil.per_in_ler_id
3253: and ppr.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
3254: and ppr.business_group_id = pen.business_group_id
3255: --
3256: -- Do not use the effective date check, see
3257: -- comments at c_ben_prtt_enrt_rslt_f
3258: -- Rows should be selected based on per in ler only.
3259: --
3260: /*
3261: and p_effective_date
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 */
3386: cursor c_adj_rec_curr_per_in_ler (p_per_in_ler_id in number) is
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
3393: select *
3402: select object_version_number,
3403: effective_start_date -- Bug 8507247:
3404: --APP-PAY-07155 error on backing out the life event which has created coverage adjustment records if
3405: --there are date track records for the enrollment after SYSDATE
3406: from ben_prtt_enrt_rslt_f
3407: where prtt_enrt_rslt_id=p_prtt_enrt_rslt_id
3408: and effective_end_date = hr_api.g_eot
3409: and prtt_enrt_rslt_stat_cd is null;
3410: -- End bug 7206471
3417: select pen.prtt_enrt_rslt_id,
3418: pen.object_version_number,
3419: pen.effective_end_date,
3420: pen.effective_start_date
3421: from ben_prtt_enrt_rslt_f pen
3422: where pen.business_group_id = p_business_group_id
3423: and pen.prtt_enrt_rslt_id = cp_prtt_enrt_rslt_id
3424: -- Commented per bug 1584238 and 1627373
3425: -- and cv_rt_strt_dt >= pen.enrt_cvg_strt_dt
3442: and prv.business_group_id = pil.business_group_id
3443: --START BUG 3495372
3444: and prv.prtt_enrt_rslt_id in
3445: (select prtt_enrt_rslt_id
3446: from ben_prtt_enrt_rslt_f
3447: where prtt_enrt_rslt_stat_cd is not null
3448: and sspndd_flag = 'N'
3449: ) -- to leave out Suspended results
3450: --END BUG 3495372
3447: where prtt_enrt_rslt_stat_cd is not null
3448: and sspndd_flag = 'N'
3449: ) -- to leave out Suspended results
3450: --END BUG 3495372
3451: and not exists (select null from ben_prtt_enrt_rslt_f where
3452: prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
3453: and prtt_enrt_rslt_stat_cd is not null ) -- to leave out VOIDD results
3454: group by prv.acty_base_rt_id
3455: having count(*) > 1;
3462: from ben_prtt_rt_val prv
3463: where prv.per_in_ler_id = p_per_in_ler_id
3464: and prv.prtt_rt_val_stat_cd is null
3465: and exists (select null
3466: from ben_prtt_enrt_rslt_f pen
3467: where pen.prtt_enrt_rslt_id = prv.prtt_enrt_rslt_id
3468: and pen.prtt_enrt_rslt_stat_cd is null
3469: and pen.sspndd_flag = 'N' ) -- to select only not voided and unsuspended results
3470: group by prv.acty_base_rt_id,prv.rt_strt_dt --BUG 4558512 otherwise.. we get into issue with FLAT RANGE
3496: --
3497: --
3498: -- END ----> DELETE ROUTINE FOR BEN_PRTT_RT_VAL
3499: --
3500: -- START ----> DELETE ROUTINE FOR BEN_PRTT_ENRT_RSLT_F
3501: --
3502: -- Source Table
3503: -- BEN_PER_IN_LER
3504: --
3503: -- BEN_PER_IN_LER
3504: --
3505:
3506:
3507: cursor c_ben_prtt_enrt_rslt_f is
3508: select pen.prtt_enrt_rslt_id,
3509: pen.object_version_number,
3510: pen.pl_id,
3511: pen.oipl_id,
3515: pen.effective_end_date,
3516: pen.effective_start_date,
3517: pen.per_in_ler_id
3518: ,pil.lf_evt_ocrd_dt
3519: from ben_prtt_enrt_rslt_f pen,
3520: ben_per_in_ler pil
3521: where pil.per_in_ler_id = p_per_in_ler_id
3522: and pil.business_group_id = p_business_group_id
3523: and pen.per_in_ler_id = pil.per_in_ler_id
3531: --- make sure results of prev per in ler id
3532: --- currected by curr per_in_ler_id due to same date enrollment
3533: --- are not backedout bug # 3086161
3534: --- after the # 3175382 new cursor created and the validation moved
3535: -- from c_ben_prtt_enrt_rslt_f. because for current per in ler id the backout entry has to be created
3536: --- unless the current per_in_ler can not be reinstated. so the cursor created to validate
3537: --- the delting part ofthe result. the result can not be deleted because the prev correctecd
3538: --- result has to be restored with the prev per in ler id
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'
3548: and lcnr.enrt_cvg_thru_dt = hr_api.g_eot --bug#5032364
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'
3548: and lcnr.enrt_cvg_thru_dt = hr_api.g_eot --bug#5032364
3549: and lcnr.per_in_ler_ended_id = pen.per_in_ler_id -- Bug 6632568
3550: and pen.per_in_ler_id = l_per_in_ler_id -- Bug 6632568
3551: and lcnr.effective_start_date between pen.effective_start_date -- Bug 6632568
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
3590: --bug#5032364
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
3590: --bug#5032364
3591: --and (pen.per_in_ler_id = lcnr.per_in_ler_ended_id
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
3619: --bug#5032364
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
3619: --bug#5032364
3620: --and (pen.per_in_ler_id = lcnr.per_in_ler_ended_id
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
3645: and (pdp.per_in_ler_id = lcnr.per_in_ler_ended_id
3646: or pdp.per_in_ler_id = lcnr.per_in_ler_id )
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
3668: and (pbn.per_in_ler_id = lcnr.per_in_ler_ended_id
3669: or pbn.per_in_ler_id = lcnr.per_in_ler_id )
3680: -- Bug#2821279 : Removed the max functions and logic is based
3681: -- on order by clause.
3682: cursor c_pen_max_esd_of_past_pil(v_prtt_enrt_rslt_id in number) is
3683: select pen.effective_end_date,pen.object_version_number, pen.per_in_ler_id
3684: from ben_prtt_enrt_rslt_f pen,
3685: ben_per_in_ler pil
3686: where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
3687: and nvl(pen.per_in_ler_id , -1) <> p_per_in_ler_id
3688: and pen.business_group_id = p_business_group_id
3691: and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
3692: order by pen.effective_end_date desc;
3693: --
3694: --
3695: l_prev_per_in_ler_id ben_prtt_enrt_rslt_f.per_in_ler_id%type;
3696: --
3697: -- CFW
3698: -- Move deleted records to backup table
3699: --
3699: --
3700: cursor c_deleted_pen(v_prtt_enrt_rslt_id in number,
3701: v_effective_date in date) is
3702: select *
3703: from ben_prtt_enrt_rslt_f
3704: where prtt_enrt_rslt_id = v_prtt_enrt_rslt_id
3705: and nvl(per_in_ler_id , -1) = p_per_in_ler_id
3706: and business_group_id = p_business_group_id
3707: and effective_end_date > v_effective_date;
3720: --
3721: l_epe c_epe%rowtype;
3722: --
3723: --
3724: -- END ----> DELETE ROUTINE FOR BEN_PRTT_ENRT_RSLT_F
3725: --
3726: --
3727: -- START ----> DELETE ROUTINE FOR BEN_BNFT_PRVDD_LDGR_F
3728: cursor c_ben_bnft_prvdd_ldgr_f(p_bnft_prvdd_ldgr_id number) is
3743: and (p_bnft_prvdd_ldgr_id is null
3744: or p_bnft_prvdd_ldgr_id = bpl.bnft_prvdd_ldgr_id
3745: )
3746: --bug#3702090
3747: and exists (select null from ben_prtt_enrt_rslt_f pen
3748: where pen.prtt_enrt_rslt_id = bpl.prtt_enrt_rslt_id)
3749: order by 1;
3750: --
3751: l_bpl c_ben_bnft_prvdd_ldgr_f%rowtype;
3857:
3858: --# bug 2546259 this cursor find the result voided by the LE
3859:
3860: cursor c_futur_pen is
3861: select pen.prtt_enrt_rslt_id from ben_prtt_enrt_rslt_f pen
3862: where per_in_ler_id = p_per_in_ler_id
3863: and pen.prtt_enrt_rslt_stat_cd = 'VOIDD'
3864: and pen.effective_start_date < pen.enrt_cvg_strt_dt
3865: /* Bug 9095753: When checking for future pen records check whether the enrollments of previous life event are end dated.
3865: /* Bug 9095753: When checking for future pen records check whether the enrollments of previous life event are end dated.
3866: If Open is the first LE,pen.effective_start_date < pen.enrt_cvg_strt_dt holds good for enrollment records that are VOIDD and tries to do delete future
3867: changes which raises APP-PAY-07187 error.So check whether enrolment records exists for previous LE*/
3868: and exists
3869: (select '1' from ben_prtt_enrt_rslt_f pen1,
3870: (select per_in_ler_id from ben_per_in_ler pil,
3871: ben_ler_f ler
3872: where pil.person_id = (select person_id
3873: from ben_per_in_ler pil2
3906: --
3907: cursor c_pen_sus is
3908: select pen.prtt_enrt_rslt_id,
3909: pen.per_in_ler_id
3910: from ben_prtt_enrt_rslt_f pen,
3911: ben_per_in_ler pil
3912: where pen.sspndd_flag = 'Y'
3913: and pen.per_in_ler_id <> pil.per_in_ler_id
3914: and pil.per_in_ler_id = p_per_in_ler_id
5985: end loop;
5986: close c_prtt_rt_val_adj;*/
5987: --
5988:
5989: elsif p_routine = 'BEN_PRTT_ENRT_RSLT_F' then
5990:
5991: hr_utility.set_location( 'backing out BEN_PRTT_ENRT_RSLT_F ' || p_per_in_ler_id , 99 );
5992: hr_utility.set_location( 'p_bckdt_prtt_enrt_rslt_id ' || p_bckdt_prtt_enrt_rslt_id , 99 );
5993: -- Results need to be 'un-ended' if they were ended due to this
5987: --
5988:
5989: elsif p_routine = 'BEN_PRTT_ENRT_RSLT_F' then
5990:
5991: hr_utility.set_location( 'backing out BEN_PRTT_ENRT_RSLT_F ' || p_per_in_ler_id , 99 );
5992: hr_utility.set_location( 'p_bckdt_prtt_enrt_rslt_id ' || p_bckdt_prtt_enrt_rslt_id , 99 );
5993: -- Results need to be 'un-ended' if they were ended due to this
5994: -- per-in-ler. The 'future change' date track mode handles that.
5995:
6017: end if ;
6018:
6019: l_prev_pk_id := -1; -- like null
6020: g_enrt_made_flag := 'N';
6021: open c_ben_prtt_enrt_rslt_f;
6022: loop
6023:
6024: fetch c_ben_prtt_enrt_rslt_f into l_pk_id,
6025: l_object_version_number,
6020: g_enrt_made_flag := 'N';
6021: open c_ben_prtt_enrt_rslt_f;
6022: loop
6023:
6024: fetch c_ben_prtt_enrt_rslt_f into l_pk_id,
6025: l_object_version_number,
6026: l_pl_id,
6027: l_oipl_id,
6028: l_enrt_cvg_strt_dt,
6033: l_pen_pil_id,
6034: -- CFW
6035: l_lf_evt_ocrd_dt;
6036: -- CFW
6037: if c_ben_prtt_enrt_rslt_f%found then
6038: g_enrt_made_flag := 'Y';
6039: end if;
6040: exit when c_ben_prtt_enrt_rslt_f%notfound;
6041:
6036: -- CFW
6037: if c_ben_prtt_enrt_rslt_f%found then
6038: g_enrt_made_flag := 'Y';
6039: end if;
6040: exit when c_ben_prtt_enrt_rslt_f%notfound;
6041:
6042: -- Get the maximum of effective end date for the enrollment
6043: -- result with past per_in_ler 's.
6044: --
6085: --
6086: --
6087: hr_utility.set_location( ' backup pil ' || l_deleted_pen_rec.PER_IN_LER_ID , 99 );
6088: hr_utility.set_location( ' backup pen ' || l_deleted_pen_rec.PRTT_ENRT_RSLT_ID , 99 );
6089: open c_bkp_row('BEN_PRTT_ENRT_RSLT_F',
6090: l_deleted_pen_rec.PER_IN_LER_ID,
6091: l_deleted_pen_rec.PRTT_ENRT_RSLT_ID,
6092: l_deleted_pen_rec.object_version_number);
6093: fetch c_bkp_row into l_row_id;
6177: PLIP_ORDR_NUM,
6178: PTIP_ORDR_NUM,
6179: OIPL_ORDR_NUM)
6180: values (
6181: 'BEN_PRTT_ENRT_RSLT_F',
6182: l_deleted_pen_rec.COMP_LVL_CD,
6183: l_deleted_pen_rec.PEN_ATTRIBUTE16,
6184: l_deleted_pen_rec.PEN_ATTRIBUTE17,
6185: l_deleted_pen_rec.PEN_ATTRIBUTE18,
6445: for l_deleted_pen_rec in c_deleted_pen(l_pk_id, l_pen_esd)
6446: loop
6447: --
6448: --
6449: open c_bkp_row('BEN_PRTT_ENRT_RSLT_F',
6450: l_deleted_pen_rec.PER_IN_LER_ID,
6451: l_deleted_pen_rec.PRTT_ENRT_RSLT_ID,
6452: l_deleted_pen_rec.object_version_number);
6453: fetch c_bkp_row into l_row_id;
6536: PLIP_ORDR_NUM,
6537: PTIP_ORDR_NUM,
6538: OIPL_ORDR_NUM)
6539: values (
6540: 'BEN_PRTT_ENRT_RSLT_F',
6541: l_deleted_pen_rec.COMP_LVL_CD,
6542: l_deleted_pen_rec.PEN_ATTRIBUTE16,
6543: l_deleted_pen_rec.PEN_ATTRIBUTE17,
6544: l_deleted_pen_rec.PEN_ATTRIBUTE18,
6754: end loop;
6755: --
6756: l_prev_pk_id := -1;
6757: --
6758: close c_ben_prtt_enrt_rslt_f;
6759: -- after result backed out determine the person type usage
6760: -- since the backout not calling the multiedit
6761: -- person type usage is not updated # 2899702
6762: ben_pen_bus.manage_per_type_usages
6767: --
6768: l_prev_pk_id := -1;
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: --
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
6890: , cqb.enrt_cvg_thru_dt = l_cvg_thru_dt -- Bug 8199189
6891: where cqb.per_in_ler_id = i.per_in_ler_id
6892: and cqb.per_in_ler_ended_id = p_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
6907: and cqb.per_in_ler_ended_id = p_per_in_ler_id
6908: and cqb.bkup_tbl_id = i.bkup_tbl_id ;
6909: --
7299: close c_prv_of_previous_pil;
7300: --
7301: end if;
7302: --
7303: if c_ben_prtt_enrt_rslt_f%isopen then
7304: --
7305: close c_ben_prtt_enrt_rslt_f;
7306: --
7307: end if;
7301: end if;
7302: --
7303: if c_ben_prtt_enrt_rslt_f%isopen then
7304: --
7305: close c_ben_prtt_enrt_rslt_f;
7306: --
7307: end if;
7308: --
7309: if c_ben_pil_elctbl_chc_popl%isopen then