[Home] [Help]
1058: decode(enb.enrt_bnft_id,null,
1059: ecr2.acty_base_rt_id,ecr1.acty_base_rt_id) acty_base_rt_id,
1060: decode(enb.enrt_bnft_id,null,
1061: nvl(ecr2.dflt_val,ecr2.val),nvl(ecr1.dflt_val,ecr1.val)) val
1062: from ben_enrt_rt ecr1,
1063: ben_enrt_rt ecr2,
1064: ben_enrt_bnft enb
1065: where ((ecr1.elig_per_elctbl_chc_id=p_epe_rec.elig_per_elctbl_chc_id
1066: and (ecr1.enrt_bnft_id = enb.enrt_bnft_id
1059: ecr2.acty_base_rt_id,ecr1.acty_base_rt_id) acty_base_rt_id,
1060: decode(enb.enrt_bnft_id,null,
1061: nvl(ecr2.dflt_val,ecr2.val),nvl(ecr1.dflt_val,ecr1.val)) val
1062: from ben_enrt_rt ecr1,
1063: ben_enrt_rt ecr2,
1064: ben_enrt_bnft enb
1065: where ((ecr1.elig_per_elctbl_chc_id=p_epe_rec.elig_per_elctbl_chc_id
1066: and (ecr1.enrt_bnft_id = enb.enrt_bnft_id
1067: or ecr1.enrt_bnft_id is null)
1087: is
1088: select ecr.enrt_rt_id,
1089: ecr.acty_base_rt_id,
1090: nvl(ecr.dflt_val, ecr.val) val
1091: from ben_enrt_rt ecr
1092: where ecr.elig_per_elctbl_chc_id = c_epe_id
1093: and ecr.rt_usg_cd = 'FLXCR';
1094:
1095: cursor c_old_ledger is
1565: decode(enb.enrt_bnft_id, null,
1566: ecr2.rt_usg_cd,ecr1.rt_usg_cd) rt_usg_cd
1567: from ben_elig_per_elctbl_chc epe,
1568: ben_per_in_ler pil,
1569: ben_enrt_rt ecr1,
1570: ben_enrt_rt ecr2,
1571: ben_enrt_bnft enb,
1572: ben_bnft_prvdd_ldgr_f bpl,
1573: ben_elig_per_elctbl_chc epe_flex,
1566: ecr2.rt_usg_cd,ecr1.rt_usg_cd) rt_usg_cd
1567: from ben_elig_per_elctbl_chc epe,
1568: ben_per_in_ler pil,
1569: ben_enrt_rt ecr1,
1570: ben_enrt_rt ecr2,
1571: ben_enrt_bnft enb,
1572: ben_bnft_prvdd_ldgr_f bpl,
1573: ben_elig_per_elctbl_chc epe_flex,
1574: ben_per_in_ler pil_flex
1715: is
1716: select ecr.enrt_rt_id,
1717: ecr.acty_base_rt_id,
1718: nvl(ecr.dflt_val, ecr.val) val
1719: from ben_enrt_rt ecr
1720: where ecr.elig_per_elctbl_chc_id = c_epe_id
1721: and ecr.rt_usg_cd = 'FLXCR';
1722: --
1723: cursor c_person_enrolled is
1723: cursor c_person_enrolled is
1724: select null
1725: from ben_elig_per_elctbl_chc epe
1726: ,ben_prtt_enrt_rslt_f pen
1727: ,ben_enrt_rt ecr
1728: ,ben_enrt_bnft enb
1729: where epe.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id
1730: and pen.person_id = p_person_id
1731: and pen.prtt_enrt_rslt_stat_cd is null
2045: decode(enb.enrt_bnft_id,null,
2046: ecr2.rt_usg_cd,ecr1.rt_usg_cd) rt_usg_cd
2047: from ben_elig_per_elctbl_chc epe,
2048: ben_per_in_ler pil,
2049: ben_enrt_rt ecr1,
2050: ben_enrt_rt ecr2,
2051: ben_enrt_bnft enb,
2052: ben_bnft_prvdd_ldgr_f bpl,
2053: ben_elig_per_elctbl_chc epe_flex,
2046: ecr2.rt_usg_cd,ecr1.rt_usg_cd) rt_usg_cd
2047: from ben_elig_per_elctbl_chc epe,
2048: ben_per_in_ler pil,
2049: ben_enrt_rt ecr1,
2050: ben_enrt_rt ecr2,
2051: ben_enrt_bnft enb,
2052: ben_bnft_prvdd_ldgr_f bpl,
2053: ben_elig_per_elctbl_chc epe_flex,
2054: ben_per_in_ler pil_flex,
2160: is
2161: l_proc varchar2(72) := g_package||'.create_flex_credit_enrolment';
2162: l_acty_typ_cd ben_acty_base_rt_f.acty_typ_cd%type;
2163: l_prnt_prtt_rt_val_id ben_prtt_rt_val.prtt_rt_val_id%type;
2164: l_prnt_enrt_rt_id ben_enrt_rt.enrt_rt_id%type;
2165: l_prnt_acty_base_rt_id ben_acty_base_rt_f.acty_base_rt_id%type;
2166: l_child_prtt_rt_val_id ben_prtt_rt_val.prtt_rt_val_id%type;
2167: l_child_enrt_rt_id ben_enrt_rt.enrt_rt_id%type;
2168: l_child_acty_base_rt_id ben_acty_base_rt_f.acty_base_rt_id%type;
2163: l_prnt_prtt_rt_val_id ben_prtt_rt_val.prtt_rt_val_id%type;
2164: l_prnt_enrt_rt_id ben_enrt_rt.enrt_rt_id%type;
2165: l_prnt_acty_base_rt_id ben_acty_base_rt_f.acty_base_rt_id%type;
2166: l_child_prtt_rt_val_id ben_prtt_rt_val.prtt_rt_val_id%type;
2167: l_child_enrt_rt_id ben_enrt_rt.enrt_rt_id%type;
2168: l_child_acty_base_rt_id ben_acty_base_rt_f.acty_base_rt_id%type;
2169: l_prnt_rt_val number := 0;
2170: l_child_rt_val number := 0;
2171: l_dummy_num number;
2210: --
2211: cursor c_enrt_rt_flex is
2212: select ecr.prtt_rt_val_id,
2213: ecr.enrt_rt_id
2214: from ben_enrt_rt ecr,
2215: ben_acty_base_rt_f abr
2216: where ecr.elig_per_elctbl_chc_id=
2217: l_flex_credit_choice.elig_per_elctbl_chc_id
2218: and ecr.business_group_id=p_business_group_id
2226: cursor c_net_credits_rate(p_acty_typ_cd in varchar2) is
2227: select ecr.prtt_rt_val_id
2228: ,ecr.enrt_rt_id
2229: ,ecr.acty_base_rt_id
2230: from ben_enrt_rt ecr
2231: where ecr.elig_per_elctbl_chc_id=
2232: l_flex_credit_choice.elig_per_elctbl_chc_id
2233: and ecr.acty_typ_cd = p_acty_typ_cd
2234: and ecr.business_group_id=p_business_group_id;
2236: cursor c_net_credits_child(p_acty_base_rt_id in number) is
2237: select ecr.prtt_rt_val_id
2238: ,ecr.enrt_rt_id
2239: ,ecr.acty_base_rt_id
2240: from ben_enrt_rt ecr
2241: ,ben_acty_base_rt_f abr
2242: where ecr.elig_per_elctbl_chc_id=
2243: l_flex_credit_choice.elig_per_elctbl_chc_id
2244: and ecr.acty_base_rt_id = abr.acty_base_rt_id
2945: IS
2946: SELECT ecr.acty_base_rt_id
2947: FROM ben_per_in_ler pil,
2948: ben_elig_per_elctbl_chc epe,
2949: ben_enrt_rt ecr
2950: WHERE pil.person_id = p_person_id
2951: AND pil.business_group_id = p_business_group_id
2952: AND pil.per_in_ler_stat_cd NOT IN ('VOIDD', 'BCKDT')
2953: AND epe.per_in_ler_id = pil.per_in_ler_id
3348: from
3349: ben_bnft_pool_rlovr_rqmt_f prr,
3350: ben_per_in_ler pil,
3351: ben_elig_per_elctbl_chc epe,
3352: ben_enrt_rt ecr,
3353: ben_prtt_rt_val prv,
3354: ben_prtt_enrt_rslt_f rslt,
3355: per_all_assignments_f asg,
3356: hr_locations_all loc,
3425: ben_bnft_pool_rlovr_rqmt_f prr,
3426: ben_per_in_ler pil,
3427: ben_elig_per_elctbl_chc epe,
3428: ben_enrt_bnft enb,
3429: ben_enrt_rt ecr,
3430: ben_prtt_rt_val prv,
3431: ben_prtt_enrt_rslt_f rslt,
3432: per_all_assignments_f asg,
3433: hr_locations_all loc,
4083: select
4084: prv.rt_val,
4085: prv.prtt_rt_val_id
4086: from ben_prtt_rt_val prv,
4087: ben_enrt_rt ecr
4088: where ecr.enrt_rt_id = v_enrt_rt_id and
4089: prv.prtt_rt_val_id=ecr.prtt_rt_val_id and
4090: prv.prtt_rt_val_stat_cd is null and
4091: prv.per_in_ler_id = p_per_in_ler_id and
4110: pen.object_version_number
4111: from ben_per_in_ler pil,
4112: ben_elig_per_elctbl_chc epe,
4113: ben_pil_elctbl_chc_popl pel,
4114: ben_enrt_rt ecr1,
4115: ben_enrt_rt ecr2,
4116: ben_enrt_bnft enb,
4117: ben_prtt_enrt_rslt_f pen,
4118: ben_bnft_prvdr_pool_f bpp -- join to get only current pgm_id - rgajula
4111: from ben_per_in_ler pil,
4112: ben_elig_per_elctbl_chc epe,
4113: ben_pil_elctbl_chc_popl pel,
4114: ben_enrt_rt ecr1,
4115: ben_enrt_rt ecr2,
4116: ben_enrt_bnft enb,
4117: ben_prtt_enrt_rslt_f pen,
4118: ben_bnft_prvdr_pool_f bpp -- join to get only current pgm_id - rgajula
4119: where pil.per_in_ler_id=p_per_in_ler_id and
4403: select ecr.acty_base_rt_id,
4404: epe.elig_per_elctbl_chc_id
4405: from ben_per_in_ler pil,
4406: ben_elig_per_elctbl_chc epe,
4407: ben_enrt_rt ecr
4408: where pil.per_in_ler_id=p_per_in_ler_id and
4409: pil.business_group_id=p_business_group_id and
4410: pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') and
4411: epe.per_in_ler_id=pil.per_in_ler_id and
4435: prv.rt_strt_dt,
4436: prv.rt_val,
4437: abr.element_type_id
4438: from ben_prtt_rt_val prv,
4439: ben_enrt_rt ecr,
4440: ben_acty_base_rt_f abr,
4441: ben_prtt_enrt_rslt_f res
4442: where prv.prtt_rt_val_id=p_prtt_rt_val_id and
4443: prv.business_group_id=p_business_group_id and
5047: l_abr_rec c_abr%rowtype;
5048: --
5049: cursor c_ecr is
5050: select nvl(ecr1.enrt_rt_id, ecr2.enrt_rt_id) enrt_rt_id
5051: from ben_enrt_rt ecr1,
5052: ben_enrt_rt ecr2,
5053: ben_elig_per_elctbl_chc epe,
5054: ben_enrt_bnft enb
5055: where epe.per_in_ler_id = p_ended_per_in_ler_id
5048: --
5049: cursor c_ecr is
5050: select nvl(ecr1.enrt_rt_id, ecr2.enrt_rt_id) enrt_rt_id
5051: from ben_enrt_rt ecr1,
5052: ben_enrt_rt ecr2,
5053: ben_elig_per_elctbl_chc epe,
5054: ben_enrt_bnft enb
5055: where epe.per_in_ler_id = p_ended_per_in_ler_id
5056: and ecr1.prtt_rt_val_id(+) = p_prtt_rt_val_id