[Home] [Help]
1220: er.ordr_num,
1221: eb.cvg_mlt_cd,
1222: abr.rate_periodization_rl, --GEVITY
1223: nvl(eb.mx_wo_ctfn_flag,'N') interim_flag --BUG 5502202
1224: from ben_enrt_rt er,
1225: ben_enrt_bnft eb,
1226: ben_acty_base_rt_f abr
1227: where er.enrt_rt_id=p_enrt_rt_id
1228: and eb.enrt_bnft_id(+)=er.enrt_bnft_id
1558: IS
1559: SELECT abr.entr_val_at_enrt_flag, er.rt_mlt_cd,
1560: NVL (eb.entr_val_at_enrt_flag, 'N') entr_bnft_val_flag,
1561: eb.cvg_mlt_cd
1562: FROM ben_enrt_rt er,
1563: ben_enrt_bnft eb,
1564: ben_acty_base_rt_f abr,
1565: ben_prtt_enrt_rslt_f pen
1566: WHERE eb.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1670: cursor c_get_elctbl_chc is
1671: select min(ecr.rt_strt_dt) rt_strt_dt
1672: ,epe.ptip_id
1673: from ben_elig_per_elctbl_chc epe
1674: ,ben_enrt_rt ecr
1675: ,ben_enrt_bnft enb
1676: where epe.per_in_ler_id = p_per_in_ler_id
1677: and epe.business_group_id = p_business_group_id
1678: and ecr.enrt_rt_id = p_enrt_rt_id -- Bug 8300620
1683: and ecr.business_group_id = p_business_group_id
1684: group by epe.ptip_id;
1685:
1686: /* Bug 8507247: If the plan is non FONM(Ex:First of Pay Period after Effective Date ) the RT_STRT_DT gets recalculated again in
1687: election_rate_information and the same is not updated back to ben_enrt_rt table. So we cannot use the
1688: min(rt_strt_dt) from ben_enrt_rt table to adjust the rates since the actual RT_STRT_DT has changed.
1689: Instead since the adjustment is always done just before creating the new rate, we already know the new rt_strt_dt
1690: from which we are going to create the new rate i.e. the variable l_enrt_rt.rt_strt_dt passed to create_prtt_rt_val.
1691: The same should be used for adjusting the ended rates.*/
1684: group by epe.ptip_id;
1685:
1686: /* Bug 8507247: If the plan is non FONM(Ex:First of Pay Period after Effective Date ) the RT_STRT_DT gets recalculated again in
1687: election_rate_information and the same is not updated back to ben_enrt_rt table. So we cannot use the
1688: min(rt_strt_dt) from ben_enrt_rt table to adjust the rates since the actual RT_STRT_DT has changed.
1689: Instead since the adjustment is always done just before creating the new rate, we already know the new rt_strt_dt
1690: from which we are going to create the new rate i.e. the variable l_enrt_rt.rt_strt_dt passed to create_prtt_rt_val.
1691: The same should be used for adjusting the ended rates.*/
1692: cursor c_get_ptip_id is
1691: The same should be used for adjusting the ended rates.*/
1692: cursor c_get_ptip_id is
1693: select epe.ptip_id
1694: from ben_elig_per_elctbl_chc epe
1695: ,ben_enrt_rt ecr
1696: ,ben_enrt_bnft enb
1697: where epe.per_in_ler_id = p_per_in_ler_id
1698: and epe.business_group_id = p_business_group_id
1699: and ecr.enrt_rt_id = p_enrt_rt_id
4871: -- cvg_mlt_cd of SAAEAR and enter value at enrollment is not checked.
4872: /*
4873: cursor c_ert is
4874: select ann_val
4875: from ben_enrt_rt ert
4876: where ert.enrt_bnft_id = p_enrt_bnft_id
4877: and ENTR_VAL_AT_ENRT_FLAG = 'N'
4878: and ann_val is not null ;
4879: l_ann_enrt_rt number ;
4887: abr.acty_base_rt_id,
4888: abr.rate_periodization_rl,
4889: enb.elig_per_elctbl_chc_id,
4890: ert.entr_ann_val_flag
4891: from ben_enrt_rt ert,
4892: ben_acty_base_rt_f abr,
4893: ben_enrt_bnft enb
4894: where enb.enrt_bnft_id = p_enrt_bnft_id
4895: and ert.enrt_bnft_id = enb.enrt_bnft_id
4982: --
4983: -- 5612091 - Added OR condition with p_elig_per_elctbl_chc_id
4984: cursor c_enrt_rt (p_acty_base_rt_id number) is
4985: select enrt_rt_id
4986: from ben_enrt_rt ecr
4987: where (ecr.enrt_bnft_id = p_enrt_bnft_id
4988: or ecr.ELIG_PER_ELCTBL_CHC_ID = p_elig_per_elctbl_chc_id)
4989: and ecr.acty_base_rt_id = p_acty_base_rt_id;
4990: --
5129: select ecr.rt_strt_dt,
5130: ecr.rt_strt_dt_cd,
5131: ecr.rt_strt_dt_rl,
5132: nvl(ecr.elig_per_elctbl_chc_id,enb.elig_per_elctbl_chc_id) elig_per_elctbl_chc_id
5133: from ben_enrt_rt ecr,
5134: ben_enrt_bnft enb
5135: where ecr.prtt_rt_val_id = p_prtt_rt_val_id
5136: and ecr.enrt_bnft_id = enb.enrt_bnft_id (+) ;
5137: --
6413: --
6414: open c_enrt_rt(l_acty_base_rt_id2);
6415: fetch c_enrt_rt into l_enrt_rt_id;
6416: if c_enrt_rt%found then
6417: update ben_enrt_rt
6418: set prtt_rt_val_id = l_prtt_rt_val_id
6419: where enrt_rt_id = l_enrt_rt_id;
6420: --
6421: if (g_debug) then