[Home] [Help]
40: and prv.per_in_ler_id = p_per_in_ler_id
41: and prv.per_in_ler_id = pil.per_in_ler_id
42: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
43: and prv.prtt_enrt_rslt_id in (select pen.prtt_enrt_rslt_id
44: from ben_prtt_enrt_rslt_f pen, ben_per_in_ler pil
45: where pil.person_id = p_person_id
46: and pen.per_in_ler_id = pil.per_in_ler_id
47: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
48: and pen.pl_id = l_pl_id
135: -- cursor to fetch imputed income enrollments for deletion
136: cursor c_del_imp_inc_enrt(p_per_in_ler_id number)
137: is
138: select pen.*
139: from ben_prtt_enrt_rslt_f pen, ben_per_in_ler pil
140: where pil.person_id = p_person_id
141: and pen.per_in_ler_id = p_per_in_ler_id
142: and pen.per_in_ler_id = pil.per_in_ler_id
143: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
156: and prv.per_in_ler_id = p_per_in_ler_id
157: and prv.per_in_ler_id = pil.per_in_ler_id
158: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
159: and prv.prtt_enrt_rslt_id in (select pen.prtt_enrt_rslt_id
160: from ben_prtt_enrt_rslt_f pen, ben_per_in_ler pil
161: where pil.person_id = p_person_id
162: and pen.per_in_ler_id = pil.per_in_ler_id
163: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
164: and pen.pl_id = l_pl_id
174: --
175: cursor c_current_epe_pen (p_prtt_enrt_rslt_id number, p_per_in_ler_id number, p_effective_date date)
176: is
177: select 1
178: from ben_prtt_enrt_rslt_f pen
179: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
180: and pen.per_in_ler_id <> p_per_in_ler_id
181: and p_effective_date between pen.effective_start_date and pen.effective_end_date
182: and pen.enrt_cvg_thru_dt = hr_api.g_eot;
186: --
187: cursor c_chk_rslt_status (p_prtt_enrt_rslt_id number, p_per_in_ler_id number)
188: is
189: select prtt_enrt_rslt_id,per_in_ler_id,pl_typ_id,pl_id
190: from ben_prtt_enrt_rslt_f
191: where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
192: and per_in_ler_id = p_per_in_ler_id
193: and prtt_enrt_rslt_Stat_cd is NULL;
194:
204: cursor c_del_imp_inc_enrt_prior(p_prtt_enrt_rslt_id number,
205: p_effective_date date)
206: is
207: select pen.*
208: from ben_prtt_enrt_rslt_f pen
209: where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
210: and p_effective_date between pen.effective_start_date and pen.effective_end_date
211: and pen.prtt_enrt_rslt_stat_cd is NULL
212: order by pen.prtt_enrt_rslt_id, pen.effective_start_date desc;
215: l_prtt_enrt_rslt_id number ) is
216: select lcnr.*
217: from BEN_LE_CLSN_N_RSTR lcnr
218: where lcnr.bkup_tbl_id = l_prtt_enrt_rslt_id
219: and lcnr.BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR'
220: and lcnr.enrt_cvg_thru_dt = hr_api.g_eot
221: and lcnr.Per_in_ler_ended_id = l_per_in_ler_id;
222:
223: l_corr_pen_rec c_corr_result_exist%rowtype;
305: l_mode := null;
306:
307: dt_api.find_dt_del_modes
308: (p_effective_date => l_imp_inc_enrt_tab_prior(l_imp_inc_enrt_tab_prior.first).effective_start_date,
309: p_base_table_name => 'BEN_PRTT_ENRT_RSLT_F',
310: p_base_key_column => 'PRTT_ENRT_RSLT_ID',
311: p_base_key_value => l_imp_inc_enrt_tab_prior(l_imp_inc_enrt_tab_prior.first).prtt_enrt_rslt_id,
312: p_zap => l_zap,
313: p_delete => l_delete,
354:
355: hr_utility.set_location(' corr pen rec exists, no zap, upd prev pil_id on pen',6695);
356:
357: -- as corr rec exists, upd the ler_id, pil_id of corr rec on the rslt
358: update ben_prtt_enrt_rslt_f
359: set ler_id = l_corr_pen_rec.ler_id,
360: per_in_ler_id = l_corr_pen_rec.per_in_ler_id
361: where prtt_enrt_rslt_id = l_corr_pen_rec.bkup_tbl_id
362: and effective_start_date = l_corr_pen_rec.effective_start_date
370: if sql%rowcount > 0 then
371: delete from BEN_LE_CLSN_N_RSTR
372: where bkup_tbl_id = nvl(l_corr_pen_rec.bkup_tbl_id,-1)
373: and Per_in_ler_ended_id = nvl(l_imp_inc_enrt_tab(r).per_in_ler_id,-1)
374: and BKUP_TBL_TYP_CD = 'BEN_PRTT_ENRT_RSLT_F_CORR';
375: end if;
376:
377: close c_corr_result_exist;
378: else
441: -- post tax employee contribution 2897063
442:
443: cursor c_post_tax_contrib(p_eot in date) is
444: select prv.rt_val , prv.tx_typ_cd,prv.acty_typ_cd
445: from ben_prtt_enrt_rslt_f pen,
446: ben_prtt_rt_val prv,
447: ben_acty_base_rt_f abr,
448: ben_pl_f pln,
449: ben_per_in_ler pil
477: -- 8716870: commented existing c_ben_amt
478: /*
479: cursor c_ben_amt(p_highly_comp in varchar2,p_eot in date) is
480: select sum(pen.bnft_amt)
481: from ben_prtt_enrt_rslt_f pen,
482: ben_pl_f pln,
483: ben_per_in_ler pil
484: where pen.person_id = p_person_id
485: and pen.prtt_enrt_rslt_stat_cd is null
540:
541: -- 8716870: Imp Inc Enh : Modified cursor c_ben_amt
542: cursor c_ben_amt(p_highly_comp in varchar2,p_cvg_strt_dt date) is
543: select sum(pen.bnft_amt)
544: from ben_prtt_enrt_rslt_f pen,
545: ben_pl_f pln,
546: ben_per_in_ler pil
547: where pen.person_id = p_person_id
548: and pen.prtt_enrt_rslt_stat_cd is null
621: pen.oipl_id,
622: pen.pgm_id,
623: pen.effective_start_date, -- 8716870
624: pen.pl_typ_id
625: from ben_prtt_enrt_rslt_f pen
626: where pen.business_group_id = p_business_group_id and
627: pen.prtt_enrt_rslt_stat_cd is null and
628: pen.pl_id = p_pl_id and
629: pen.oipl_id is null and
640: pen.pgm_id,
641: pen.effective_start_date, -- 8716870
642: pen.pl_typ_id
643: from ben_pl_f pln,
644: ben_prtt_enrt_rslt_f pen
645: where pln.imptd_incm_calc_cd = p_imptd_incm_calc_cd
646: and pln.pl_stat_cd = 'A'
647: and p_effective_date
648: between pln.effective_start_date
696: and prv.rt_end_dt = hr_api.g_eot;
697: --
698: cursor c_pgm_enrt_rslt_exists(p_pgm_id in number) is
699: select null
700: from ben_prtt_enrt_rslt_f pen,
701: ben_pl_f pln
702: where pen.business_group_id = p_business_group_id
703: and pen.prtt_enrt_rslt_stat_cd is null
704: and pen.pgm_id = p_pgm_id
832: l_asgn_on_enrt_flag varchar2(100);
833: l_use_to_calc_net_flx_cr_flag varchar2(100);
834: l_uom varchar2(100);
835: --
836: l_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
837: l_prtt_rt_val_id ben_prtt_rt_val.prtt_rt_val_id%TYPE;
838: l_prtt_rt_val_id1 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
839: l_prtt_rt_val_id2 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
840: l_prtt_rt_val_id3 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
844: l_prtt_rt_val_id7 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
845: l_prtt_rt_val_id8 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
846: l_prtt_rt_val_id9 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
847: l_prtt_rt_val_id10 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
848: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
849: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
850: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
851: l_nnmntry_uom varchar2(100);
852: l_entr_val_at_enrt_flag varchar2(30);
845: l_prtt_rt_val_id8 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
846: l_prtt_rt_val_id9 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
847: l_prtt_rt_val_id10 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
848: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
849: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
850: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
851: l_nnmntry_uom varchar2(100);
852: l_entr_val_at_enrt_flag varchar2(30);
853: l_dsply_on_enrt_flag varchar2(30);
846: l_prtt_rt_val_id9 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
847: l_prtt_rt_val_id10 ben_prtt_rt_val.prtt_rt_val_id%TYPE;
848: l_effective_start_date ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
849: l_effective_end_date ben_prtt_enrt_rslt_f.effective_end_date%TYPE;
850: l_object_version_number ben_prtt_enrt_rslt_f.object_version_number%TYPE;
851: l_nnmntry_uom varchar2(100);
852: l_entr_val_at_enrt_flag varchar2(30);
853: l_dsply_on_enrt_flag varchar2(30);
854: l_dpnt_actn_warning boolean;
1658: -- cursor to pick all enrollments subject to imputed income
1659: cursor c_enrt_subj_to_imp
1660: is
1661: select pen.prtt_enrt_rslt_id, pen.enrt_cvg_strt_Dt,pl.subj_to_imptd_incm_typ_cd
1662: from ben_prtt_enrt_rslt_f pen,
1663: ben_per_in_ler pil,
1664: ben_pl_f pl
1665: where pen.pl_id = pl.pl_id
1666: and pen.sspndd_flag = 'N'
1678: and pil.per_in_ler_id = p_per_in_ler_id
1679: and subj_to_imptd_incm_typ_cd is not null
1680: union
1681: select pen.prtt_enrt_rslt_id, pen.enrt_cvg_strt_Dt,pl.subj_to_imptd_incm_typ_cd
1682: from ben_prtt_enrt_rslt_f pen,
1683: ben_elig_per_elctbl_chc epe,
1684: ben_pl_f pl,
1685: ben_per_in_ler pil
1686: where epe.per_in_ler_id <> pen.per_in_ler_id
1713: -- enrollment in terms of coverage start date
1714: cursor c_erlst_cvg_strt(p_imptd_incm_typ_cd varchar2)
1715: is
1716: select pen.prtt_enrt_rslt_id, pen.enrt_cvg_strt_Dt,pl.subj_to_imptd_incm_typ_cd
1717: from ben_prtt_enrt_rslt_f pen,
1718: ben_per_in_ler pil,
1719: ben_pl_f pl
1720: where pen.pl_id = pl.pl_id
1721: and pen.sspndd_flag = 'N'
1759: pen.effective_start_date,
1760: pen.pl_typ_id,
1761: pen.enrt_cvg_strt_dt
1762: from ben_pl_f pln,
1763: ben_prtt_enrt_rslt_f pen
1764: where pln.imptd_incm_calc_cd = c_imp_inc_cd -- bug 12354818
1765: and pln.pl_stat_cd = 'A'
1766: and p_effective_date between pln.effective_start_date and pln.effective_end_date
1767: and pen.business_group_id = p_business_group_id
1786: is
1787: select pen.prtt_enrt_rslt_id,
1788: pen.enrt_cvg_strt_Dt,
1789: pl.subj_to_imptd_incm_typ_cd
1790: from ben_prtt_enrt_rslt_f pen,
1791: ben_per_in_ler pil,
1792: ben_pl_f pl
1793: where pen.pl_id = pl.pl_id
1794: and pen.sspndd_flag = 'N'
2120: and pln.effective_end_date;
2121: --
2122: cursor c_imp_shell is
2123: select 'Y'
2124: from ben_prtt_enrt_rslt_f pen
2125: where pen.person_id = p_person_id
2126: and pen.comp_lvl_cd = 'PLANIMP'
2127: and pen.effective_end_date = to_date('31-12-4712','dd-mm-yyyy')
2128: and pen.prtt_enrt_rslt_stat_cd is null
2143:
2144: cursor c_imp_inc_no_chc(p_per_in_ler_id number, p_imptd_incm_calc_cd varchar2)
2145: is
2146: select 'Y'
2147: from ben_prtt_enrt_rslt_f pen, ben_pl_f pl
2148: where pen.person_id = p_person_id
2149: and pen.comp_lvl_cd = 'PLANIMP'
2150: and pen.effective_end_date = hr_api.g_eot
2151: and pen.prtt_enrt_rslt_stat_cd is null