[Home] [Help]
22: --cursor to fetch imputed income plans
23: cursor c_imp_inc_plan(p_imptd_incm_calc_cd varchar2, p_business_group_id number, p_effective_date date)
24: is
25: select pln.pl_id
26: from ben_pl_f pln
27: where pln.imptd_incm_calc_cd = p_imptd_incm_calc_cd
28: and pln.pl_stat_cd = 'A'
29: and pln.business_group_id = p_business_group_id
30: and p_effective_date between pln.effective_start_date and pln.effective_end_date;
125: --cursor to fetch imputed income plans
126: cursor c_imp_inc_plan(p_imptd_incm_calc_cd varchar2, p_business_group_id number, p_effective_date date)
127: is
128: select pln.pl_id
129: from ben_pl_f pln
130: where pln.imptd_incm_calc_cd = p_imptd_incm_calc_cd
131: and pln.pl_stat_cd = 'A'
132: and pln.business_group_id = p_business_group_id
133: and p_effective_date between pln.effective_start_date and pln.effective_end_date;
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
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
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
486: and pen.enrt_cvg_thru_dt = p_eot
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
549: -- and pen.enrt_cvg_thru_dt = p_eot
605:
606: cursor c_imp_inc_plan is
607: select pln.pl_id,
608: pln.pl_cd
609: from ben_pl_f pln
610: where pln.imptd_incm_calc_cd = p_imptd_incm_calc_cd
611: and pln.pl_stat_cd = 'A'
612: and pln.business_group_id = p_business_group_id
613: and p_effective_date
639: pen.oipl_id,
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
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
705: and pen.person_id = p_person_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
764: and pil.per_in_ler_stat_cd not in ('BCKDT','VOIDD')
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
1668: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
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
1687: and pen.pl_id = pl.pl_id
1688: and epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
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
1723: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
1758: pen.pgm_id,
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
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
1796: and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
2093: --
2094: /*
2095: cursor c_imp_plan_exists is
2096: select 'Y'
2097: from ben_pl_f pln
2098: where pln.imptd_incm_calc_cd is not null
2099: and pln.pl_stat_cd = 'A'
2100: and pln.business_group_id = p_business_group_id
2101: and p_effective_date
2107: -- exist for the per_in_ler do not do the imputed income computation.
2108: --
2109: cursor c_imp_plan_exists is
2110: select 'Y'
2111: from ben_pl_f pln,
2112: ben_elig_per_elctbl_chc epe
2113: where pln.subj_to_imptd_incm_typ_cd is not null
2114: and pln.pl_id = epe.pl_id
2115: and epe.per_in_ler_id = p_per_in_ler_id
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