[Home] [Help]
857: --ben_prtt_enrt_actn_f
858: --
859: for l_pea in c_future_pea(l_bnf_types.prtt_enrt_actn_id,l_effective_date) loop
860: --
861: insert into BEN_LE_CLSN_N_RSTR (
862: BKUP_TBL_TYP_CD,
863: BKUP_TBL_ID,
864: EFFECTIVE_START_DATE,
865: EFFECTIVE_END_DATE,
977: --ben_prtt_enrt_ctfn_prvdd_f
978: --
979: for l_lcs in c_future_pcs(l_bnf_types.prtt_enrt_actn_id,l_effective_date) loop
980: --
981: insert into BEN_LE_CLSN_N_RSTR (
982: BKUP_TBL_TYP_CD,
983: BKUP_TBL_ID,
984: EFFECTIVE_START_DATE,
985: EFFECTIVE_END_DATE,
3771: l_count := l_count + 1;
3772: hr_utility.set_location('c_ppe1 found',1);
3773: hr_utility.set_location('l_ppe1.prtt_prem_id : '||l_ppe1.prtt_prem_id,1);
3774: ---bkup table
3775: insert into BEN_LE_CLSN_N_RSTR (
3776: BKUP_TBL_TYP_CD,
3777: LCR_ATTRIBUTE6,
3778: LCR_ATTRIBUTE7,
3779: LCR_ATTRIBUTE8,
4309: c_cvg_strt_dt date ,
4310: c_cvg_thru_dt date ,
4311: c_pen_stat_cd varchar2 ) is
4312: select 'x'
4313: from BEN_LE_CLSN_N_RSTR
4314: where bkup_tbl_id = c_pen_id
4315: and ((per_in_ler_id = c_pil_id
4316: and per_in_ler_ended_id = c_pil_end_id)
4317: or per_in_ler_ended_id = c_pil_id) -- 7197868
4317: or per_in_ler_ended_id = c_pil_id) -- 7197868
4318: and BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR'
4319: union
4320: select 'x'
4321: from BEN_LE_CLSN_N_RSTR
4322: where bkup_tbl_id = c_pen_id
4323: and per_in_ler_id = c_pil_id
4324: and BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F'
4325: and c_eff_strt_dt = EFFECTIVE_START_DATE
4412: fetch c_bckup_tbl_restore into l_dummy ;
4413: if c_bckup_tbl_restore%notfound then
4414: hr_utility.set_location(' backup row not found ' || p_prtt_enrt_rslt_id , 99 );
4415:
4416: insert into BEN_LE_CLSN_N_RSTR (
4417: BKUP_TBL_TYP_CD,
4418: COMP_LVL_CD,
4419: LCR_ATTRIBUTE16,
4420: LCR_ATTRIBUTE17,
6408: -- Bug 6165501 : Added union clause if its a Correction case, and interim has already
6409: -- been end-dated by the new pil, then no need to call delete_enrollment for the interim
6410: UNION
6411: select 'Y'
6412: from ben_prtt_enrt_rslt_f pen, ben_le_clsn_n_rstr cls
6413: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
6414: and pen.prtt_enrt_rslt_stat_cd is null
6415: and pen.enrt_cvg_thru_dt <> hr_api.g_eot
6416: and pen.per_in_ler_id = p_per_in_ler_id
6579: cursor c_old_corr_pen (c_pil_id number
6580: ,c_pen_id number
6581: ,c_pen_effective_start_date date) is -- bug 7197868
6582: select per_in_ler_id
6583: from BEN_LE_CLSN_N_RSTR
6584: where bkup_tbl_id = c_pen_id
6585: and per_in_ler_ended_id = c_pil_id
6586: and per_in_ler_id <> c_pil_id
6587: and effective_start_date = c_pen_effective_start_date -- bug 7197868
6585: and per_in_ler_ended_id = c_pil_id
6586: and per_in_ler_id <> c_pil_id
6587: and effective_start_date = c_pen_effective_start_date -- bug 7197868
6588: and enrt_cvg_thru_dt = hr_api.g_eot;
6589: l_corr_pil_id BEN_LE_CLSN_N_RSTR.per_in_ler_id%type ;
6590:
6591: cursor c_pen_obj_no (c_pil_id number
6592: ,c_pen_id number ) is
6593: select object_version_number
6697: ,cp_effective_date DATE
6698: ,cp_per_in_ler_id NUMBER
6699: ) IS
6700: SELECT 'X' place_holder
6701: FROM ben_le_clsn_n_rstr bcr
6702: WHERE bcr.bkup_tbl_id=cp_prtt_enrt_rslt_id
6703: AND bcr.effective_start_date=cp_effective_date
6704: UNION
6705: SELECT 'X' place_holder
6702: WHERE bcr.bkup_tbl_id=cp_prtt_enrt_rslt_id
6703: AND bcr.effective_start_date=cp_effective_date
6704: UNION
6705: SELECT 'X' place_holder
6706: FROM ben_le_clsn_n_rstr bcr
6707: WHERE bcr.bkup_tbl_id=cp_prtt_enrt_rslt_id
6708: AND bcr.per_in_ler_id <> cp_per_in_ler_id
6709: AND bcr.bkup_tbl_typ_cd LIKE '%CORR' ;
6710:
9009: /* -- added here for bug 6963660
9010: -- updated the corrected result in the back up table so that it behaves as
9011: -- if corrected on the p_effective_start_date
9012: --
9013: update ben_le_clsn_n_rstr
9014: set effective_start_date = p_effective_start_date
9015: where per_in_ler_id = l_corr_pil_id
9016: and bkup_tbl_id = p_prtt_enrt_rslt_id
9017: and bkup_tbl_typ_cd = 'BEN_PRTT_ENRT_RSLT_F_CORR';