[Home] [Help]
34: -- cursor to fetch rates starting before earliest coverage start date of plan subject to imputed income
35: cursor c_del_imp_inc_rt(p_per_in_ler_id number)
36: is
37: select prv.*
38: from ben_prtt_rt_val prv, ben_per_in_ler pil
39: where pil.person_id = p_person_id
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')
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')
150: -- rate record in ben_prtt_rt_val to be sinc following code changes are made.
151: cursor c_del_imp_inc_rt(p_per_in_ler_id number)
152: is
153: select prv.*
154: from ben_prtt_rt_val prv, ben_per_in_ler pil
155: where pil.person_id = p_person_id
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')
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
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
363: and exists (select 1
364: from ben_per_in_ler
365: where per_in_ler_id = l_corr_pen_rec.per_in_ler_id
366: and per_in_ler_stat_cd not in ('BCKDT','VOIDD'))
367: and business_group_id = p_business_group_id
368: and person_id = l_corr_pen_rec.person_id;
429: --
430: cursor c_pil is
431: select pil.lf_evt_ocrd_dt,
432: pil.ler_id
433: from ben_per_in_ler pil
434: where pil.per_in_ler_id = p_per_in_ler_id;
435: --
436: l_pil_rec c_pil%rowtype;
437: --
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
450: where pen.person_id = p_person_id
451: and pen.prtt_enrt_rslt_stat_cd is null
452: and pen.enrt_cvg_thru_dt = p_eot
453: and pen.business_group_id = p_business_group_id
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
486: and pen.enrt_cvg_thru_dt = p_eot
487: and pen.business_group_id = p_business_group_id
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
549: -- and pen.enrt_cvg_thru_dt = p_eot
550: and pen.business_group_id = p_business_group_id
739: -- cursor used to determine l_cvg_strt_dt
740: cursor c_pil_popl
741: is
742: select pil.lf_evt_ocrd_dt, popl.elcns_made_dt, popl.dflt_asnd_dt, popl.dflt_enrt_dt
743: from ben_per_in_ler pil, ben_pil_elctbl_chc_popl popl
744: where popl.per_in_ler_id (+) = pil.per_in_ler_id
745: and pil.per_in_ler_id = p_per_in_ler_id;
746:
747: l_pil_popl c_pil_popl%rowtype;
755: epe.pl_typ_id,
756: epe.pl_id,
757: epe.fonm_cvg_strt_dt
758: from ben_elig_per_elctbl_chc epe,
759: ben_per_in_ler pil,
760: ben_pl_f pl,
761: ben_pil_elctbl_chc_popl pel /* bug 10262697 */
762: where epe.PER_IN_LER_ID = p_per_in_ler_id
763: and pil.per_in_ler_id = p_per_in_ler_id
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'
1667: and pen.per_in_ler_id = pil.per_in_ler_id
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
1687: and pen.pl_id = pl.pl_id
1688: and epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1689: and epe.per_in_ler_id = pil.per_in_ler_id
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'
1722: and pen.per_in_ler_id = pil.per_in_ler_id
1736:
1737: cursor c_ler_type
1738: is
1739: select ler.typ_cd
1740: from ben_per_in_ler pil , ben_ler_f ler
1741: where per_in_ler_id = p_per_in_ler_id
1742: and ler.ler_id = pil.ler_id
1743: and lf_evt_ocrd_dt between ler.effective_start_date and ler.effective_end_date;
1744:
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'
1795: and pen.per_in_ler_id = pil.per_in_ler_id