[Home] [Help]
48: BEN_PER_CM_F
49: - BEN_PER_CM_PRVDD_F
50: - BEN_PER_CM_TRGR_F
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
230: as effective_date
231: 15-May-2001 kmahendr 115.58 - Bug#1653733 - Rate change only event is not cal-
232: culating prtt_rt_val if event is backed out and
233: reprocessed - added delete_routine for the table
234: ben_bnft_prvdd_ldgr_f
235: 06-Jun-2001 pbodla 115.59 - Bug 1814166 : Causing primary key violation.
236: Commented pep, epo backup as data is not used
237: by benleclr.pkb
238: 16-Jul-01 kmahendr 115.60 - Unrestricted life event not be backedout - added
333: 29 Jun 2004 ikasire 115.101 Bug3709516 we are zaping ben_prmry_care_prvdr_f
334: commented the delete_routine for this as we
335: need to reinstate them.
336: 15 Jul 2004 kmahendr 115.102 Bug#3702090 - added exists condition for
337: c_ben_bnft_prvdd_ldgr_f
338: 16 Aug 2004 pbodla 115.103 IREC : Avoid backing out contact pils
339: iRec mode p_backout_contact_pils
340: 25 Aug 2004 pbodla 115.104 CFW : 2534391 :NEED TO LEAVE ACTION
341: ITEMS CERTIFICATIONS on subsequent
402: fetch future dated LE.
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
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)
1637: -- BEN_PRTT_REIMBMT_RQST_F
1638: -- BEN_PL_BNF_CTFN_PRVDD_F (PBC)(join to pil to check status)
1639: -- BEN_PRTT_ENRT_CTFN_PRVDD_F (PCS)(join to pil to check status)
1775: p_copy_only => p_copy_only,
1776: p_effective_date => p_effective_date);
1777:
1778: -- delete credit ledger rows
1779: delete_routine(p_routine => 'BEN_BNFT_PRVDD_LDGR_F',
1780: p_per_in_ler_id => p_per_in_ler_id,
1781: p_business_group_id => p_business_group_id,
1782: p_bckdt_prtt_enrt_rslt_id => p_bckdt_prtt_enrt_rslt_id,
1783: p_copy_only => p_copy_only,
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
3729: select bpl.bnft_prvdd_ldgr_id,
3730: bpl.object_version_number,
3731: bpl.acty_base_rt_id,
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
3729: select bpl.bnft_prvdd_ldgr_id,
3730: bpl.object_version_number,
3731: bpl.acty_base_rt_id,
3732: bpl.effective_end_date,
3731: bpl.acty_base_rt_id,
3732: bpl.effective_end_date,
3733: bpl.effective_start_date,
3734: bpl.per_in_ler_id
3735: from ben_bnft_prvdd_ldgr_f bpl,
3736: ben_per_in_ler pil
3737: where pil.per_in_ler_id = p_per_in_ler_id
3738: and pil.business_group_id = p_business_group_id
3739: and bpl.per_in_ler_id = pil.per_in_ler_id
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;
3752: --
3753: -- Bug 6376239
3754: cursor c_bpl_from_pen is
3755: select bpl.bnft_prvdd_ldgr_id
3752: --
3753: -- Bug 6376239
3754: cursor c_bpl_from_pen is
3755: select bpl.bnft_prvdd_ldgr_id
3756: from ben_bnft_prvdd_ldgr_f bpl, ben_prtt_rt_val prv
3757: where prv.prtt_enrt_rslt_id = p_bckdt_prtt_enrt_rslt_id
3758: and prv.acty_base_rt_id = bpl.acty_base_rt_id
3759: and bpl.per_in_ler_id = p_per_in_ler_id
3760: and prv.per_in_ler_id = bpl.per_in_ler_id
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: --
3776: l_bpl_from_backup c_bpl_from_backup%ROWTYPE;
3780: -- Bug#2592783 - per_in_ler_stat_cd is checked
3781: -- Bug#2646851 - cursor modified with order by clause and max function removed
3782: cursor c_bpl_max_esd_of_past_pil(v_bnft_prvdd_ldgr_id in number) is
3783: select bpl.effective_end_date, bpl.object_version_number
3784: from ben_bnft_prvdd_ldgr_f bpl,
3785: ben_per_in_ler pil
3786: where bpl.bnft_prvdd_ldgr_id = v_bnft_prvdd_ldgr_id
3787: and nvl(bpl.per_in_ler_id , -1) <> p_per_in_ler_id
3788: and bpl.business_group_id = p_business_group_id
3788: and bpl.business_group_id = p_business_group_id
3789: and bpl.per_in_ler_id = pil.per_in_ler_id
3790: and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
3791: order by bpl.effective_end_date desc;
3792: -- END ---> DELETE ROUTINE FOR BEN_BNFT_PRVDD_LDGR_F
3793:
3794: -- START ----> DELETE ROUTINE FOR BEN_PIL_ELCTBL_CHC_POPL
3795: -- -- Source Table -- BEN_PER_IN_LER --
3796: cursor c_ben_pil_elctbl_chc_popl is
7013: close c_adj_rec_curr_per_in_ler;
7014:
7015: end if;
7016:
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
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);
7026: --
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
7051: and effective_end_date = hr_api.g_eot;
7052: --
7053: -- End Bug 6376239
7067: end if;
7068: --
7069: -- End Bug 6376239
7070: --
7071: open c_ben_bnft_prvdd_ldgr_f(prev_bnft_prvdd_ldgr_id);
7072: loop
7073: l_effective_date := null;
7074: fetch c_ben_bnft_prvdd_ldgr_f into l_bpl;
7075: exit when c_ben_bnft_prvdd_ldgr_f%notfound;
7070: --
7071: open c_ben_bnft_prvdd_ldgr_f(prev_bnft_prvdd_ldgr_id);
7072: loop
7073: l_effective_date := null;
7074: fetch c_ben_bnft_prvdd_ldgr_f into l_bpl;
7075: exit when c_ben_bnft_prvdd_ldgr_f%notfound;
7076: l_pk_id := l_bpl.bnft_prvdd_ldgr_id;
7077: open c_bpl_max_esd_of_past_pil(l_pk_id);
7078: fetch c_bpl_max_esd_of_past_pil into l_effective_date,
7071: open c_ben_bnft_prvdd_ldgr_f(prev_bnft_prvdd_ldgr_id);
7072: loop
7073: l_effective_date := null;
7074: fetch c_ben_bnft_prvdd_ldgr_f into l_bpl;
7075: exit when c_ben_bnft_prvdd_ldgr_f%notfound;
7076: l_pk_id := l_bpl.bnft_prvdd_ldgr_id;
7077: open c_bpl_max_esd_of_past_pil(l_pk_id);
7078: fetch c_bpl_max_esd_of_past_pil into l_effective_date,
7079: l_max_object_version_number;
7125: end if;
7126: end if;
7127: end loop;
7128: --
7129: close c_ben_bnft_prvdd_ldgr_f;
7130: --
7131: elsif p_routine = 'BEN_PIL_ELCTBL_CHC_POPL' then
7132:
7133: -- pil popl's are updated to 'backed out'