VIEW: APPS.BEN_CWB_REP_TOTAL_COMP_V

Source

View Text - Preformatted

SELECT min(hier.lvl_num+1) levels_down, ppf.first_name || ' ' || ppf.last_name emp_brief_name, ppf.full_name emp_full_name, popl.uom currency, ppp.proposed_salary_n beg_salary, sum(decode(plt.comp_typ_cd,'ICM7',nvl(nvl(prt.rt_val,enr.val),0))) salary_val, ppp.proposed_salary_n + sum(decode(plt.comp_typ_cd,'ICM7',nvl(nvl(prt.rt_val,enr.val),0))) end_salary, sum(decode(plt.comp_typ_cd,'ICM',nvl(nvl(prt.rt_val,enr.val),0))) bonus_val, nvl(ppp.proposed_salary_n + sum(decode(plt.comp_typ_cd,'ICM7',nvl(prt.rt_val,enr.val))) + sum(decode(plt.comp_typ_cd,'ICM',nvl(prt.rt_val,enr.val))),0) total_comp, sum(decode(plt.comp_typ_cd,'ICM4',decode (enr.nnmntry_uom, 'SHR',nvl(prt.rt_val,enr.val), '',round(nvl(prt.rt_val,enr.val)/nvl(prt2.rt_val,enr2.val),0)))) stock_shares, nvl(sum(decode(plt.comp_typ_cd,'ICM4', decode (enr.nnmntry_uom, 'SHR', nvl(prt.rt_val,enr.val)*nvl(prt2.rt_val,enr2.val), '',nvl(prt.rt_val,enr.val)))),0) stock_val, chc.ws_mgr_id mgr_id_param, chc.yr_perd_id perd_id_param, 1 levels_down_param from ben_elig_per_elctbl_chc chc, ben_pil_elctbl_chc_popl popl, ben_pl_f pl, ben_pl_typ_f plt, ben_cwb_mgr_hrchy hier, ben_per_in_ler pil, ben_elig_per_elctbl_chc chc2, ben_enrt_rt enr, ben_prtt_rt_val prt, ben_enrt_rt enr2, ben_prtt_rt_val prt2, ben_yr_perd yr, per_all_people_f ppf, per_pay_proposals ppp WHERE chc.yr_perd_id = yr.yr_perd_id and chc.pl_id = pl.pl_id and chc.pl_typ_id = plt.pl_typ_id and plt.comp_typ_cd in ('ICM','ICM4','ICM7') and sysdate between pl.effective_start_date and pl.effective_end_date and sysdate between plt.effective_start_date and plt.effective_end_date and chc.elig_per_elctbl_chc_id = hier.mgr_elig_per_elctbl_chc_id and hier.lvl_num+1 > 0 and hier.lvl_num+1 <= 1 and hier.emp_elig_per_elctbl_chc_id = chc2.elig_per_elctbl_chc_id and chc2.elig_per_elctbl_chc_id = enr.elig_per_elctbl_chc_id and enr.acty_typ_cd = 'CWBWS' and enr.prtt_rt_val_id = prt.prtt_rt_val_id (+) and chc2.elig_per_elctbl_chc_id = enr2.elig_per_elctbl_chc_id (+) and enr2.acty_typ_cd(+) = 'CWBGP' and enr2.prtt_rt_val_id = prt2.prtt_rt_val_id (+) and chc2.per_in_ler_id = pil.per_in_ler_id and chc2.elig_flag = 'Y' and chc2.elig_flag = 'Y' and pil.per_in_ler_stat_cd in ('STRTD','PROCD') and pil.person_id = ppf.person_id and sysdate between ppf.effective_start_date and ppf.effective_end_date and chc2.pil_elctbl_chc_popl_id = popl.pil_elctbl_chc_popl_id and chc2.assignment_id = ppp.assignment_id (+) and ppp.approved (+) = 'Y' and ppp.change_date = nvl((SELECT max(ppp2.change_date) from per_pay_proposals ppp2 where ppp2.assignment_id = ppp.assignment_id and ppp2.approved = 'Y' and ppp2.change_date <= yr.start_date), (SELECT min(ppp2.change_date) from per_pay_proposals ppp2 where ppp2.assignment_id = ppp.assignment_id and ppp2.approved = 'Y' and ppp2.change_date > yr.start_date)) group by chc.ws_mgr_id , chc.yr_perd_id , ppf.first_name || ' ' || ppf.last_name , ppf.full_name , popl.uom, ppp.proposed_salary_n UNION SELECT min(hier.lvl_num+1) levels_down, ppf.first_name || ' ' || ppf.last_name emp_brief_name, ppf.full_name emp_full_name, popl.uom currency, ppp.proposed_salary_n beg_salary, sum(decode(plt.comp_typ_cd,'ICM7',nvl(nvl(prt.rt_val,enr.val),0))) salary_val, ppp.proposed_salary_n + sum(decode(plt.comp_typ_cd,'ICM7',nvl(nvl(prt.rt_val,enr.val),0))) end_salary, sum(decode(plt.comp_typ_cd,'ICM',nvl(nvl(prt.rt_val,enr.val),0))) bonus_val, nvl(ppp.proposed_salary_n + sum(decode(plt.comp_typ_cd,'ICM7',nvl(prt.rt_val,enr.val))) + sum(decode(plt.comp_typ_cd,'ICM',nvl(prt.rt_val,enr.val))),0) total_comp, sum(decode(plt.comp_typ_cd,'ICM4',decode (enr.nnmntry_uom, 'SHR',nvl(prt.rt_val,enr.val), '',round(nvl(prt.rt_val,enr.val)/nvl(prt2.rt_val,enr2.val),0)))) stock_shares, nvl(sum(decode(plt.comp_typ_cd,'ICM4', decode (enr.nnmntry_uom, 'SHR', nvl(prt.rt_val,enr.val)*nvl(prt2.rt_val,enr2.val), '',nvl(prt.rt_val,enr.val)))),0) stock_val, chc.ws_mgr_id , chc.yr_perd_id , 2 from ben_elig_per_elctbl_chc chc, ben_pil_elctbl_chc_popl popl, ben_pl_f pl, ben_pl_typ_f plt, ben_cwb_mgr_hrchy hier, ben_per_in_ler pil, ben_elig_per_elctbl_chc chc2, ben_enrt_rt enr, ben_prtt_rt_val prt, ben_enrt_rt enr2, ben_prtt_rt_val prt2, ben_yr_perd yr, per_all_people_f ppf, per_pay_proposals ppp where chc.yr_perd_id = yr.yr_perd_id and chc.pl_id = pl.pl_id and chc.pl_typ_id = plt.pl_typ_id and plt.comp_typ_cd in ('ICM','ICM4','ICM7') and sysdate between pl.effective_start_date and pl.effective_end_date and sysdate between plt.effective_start_date and plt.effective_end_date and chc.elig_per_elctbl_chc_id = hier.mgr_elig_per_elctbl_chc_id and hier.lvl_num+1 > 0 and hier.lvl_num+1 <= 2 and hier.emp_elig_per_elctbl_chc_id = chc2.elig_per_elctbl_chc_id and chc2.elig_per_elctbl_chc_id = enr.elig_per_elctbl_chc_id and enr.acty_typ_cd = 'CWBWS' and enr.prtt_rt_val_id = prt.prtt_rt_val_id (+) and chc2.elig_per_elctbl_chc_id = enr2.elig_per_elctbl_chc_id (+) and enr2.acty_typ_cd(+) = 'CWBGP' and enr2.prtt_rt_val_id = prt2.prtt_rt_val_id (+) and chc2.per_in_ler_id = pil.per_in_ler_id and chc2.elig_flag = 'Y' and chc2.elig_flag = 'Y' and pil.per_in_ler_stat_cd in ('STRTD','PROCD') and pil.person_id = ppf.person_id and sysdate between ppf.effective_start_date and ppf.effective_end_date and chc2.pil_elctbl_chc_popl_id = popl.pil_elctbl_chc_popl_id and chc2.assignment_id = ppp.assignment_id (+) and ppp.approved (+) = 'Y' and ppp.change_date = nvl((SELECT max(ppp2.change_date) from per_pay_proposals ppp2 where ppp2.assignment_id = ppp.assignment_id and ppp2.approved = 'Y' and ppp2.change_date <= yr.start_date), (SELECT min(ppp2.change_date) from per_pay_proposals ppp2 where ppp2.assignment_id = ppp.assignment_id and ppp2.approved = 'Y' and ppp2.change_date > yr.start_date)) group by chc.ws_mgr_id , chc.yr_perd_id , ppf.first_name || ' ' || ppf.last_name , ppf.full_name , popl.uom, ppp.proposed_salary_n UNION SELECT min(hier.lvl_num+1) levels_down, ppf.first_name || ' ' || ppf.last_name emp_brief_name, ppf.full_name emp_full_name, popl.uom currency, ppp.proposed_salary_n beg_salary, sum(decode(plt.comp_typ_cd,'ICM7',nvl(nvl(prt.rt_val,enr.val),0))) salary_val, ppp.proposed_salary_n + sum(decode(plt.comp_typ_cd,'ICM7',nvl(nvl(prt.rt_val,enr.val),0))) end_salary, sum(decode(plt.comp_typ_cd,'ICM',nvl(nvl(prt.rt_val,enr.val),0))) bonus_val, nvl(ppp.proposed_salary_n + sum(decode(plt.comp_typ_cd,'ICM7',nvl(prt.rt_val,enr.val))) + sum(decode(plt.comp_typ_cd,'ICM',nvl(prt.rt_val,enr.val))),0) total_comp, sum(decode(plt.comp_typ_cd,'ICM4',decode (enr.nnmntry_uom, 'SHR',nvl(prt.rt_val,enr.val), '',round(nvl(prt.rt_val,enr.val)/nvl(prt2.rt_val,enr2.val),0)))) stock_shares, nvl(sum(decode(plt.comp_typ_cd,'ICM4', decode (enr.nnmntry_uom, 'SHR', nvl(prt.rt_val,enr.val)*nvl(prt2.rt_val,enr2.val), '',nvl(prt.rt_val,enr.val)))),0) stock_val, chc.ws_mgr_id , chc.yr_perd_id , 3 from ben_elig_per_elctbl_chc chc, ben_pil_elctbl_chc_popl popl, ben_pl_f pl, ben_pl_typ_f plt, ben_cwb_mgr_hrchy hier, ben_per_in_ler pil, ben_elig_per_elctbl_chc chc2, ben_enrt_rt enr, ben_prtt_rt_val prt, ben_enrt_rt enr2, ben_prtt_rt_val prt2, ben_yr_perd yr, per_all_people_f ppf, per_pay_proposals ppp where chc.yr_perd_id = yr.yr_perd_id and chc.pl_id = pl.pl_id and chc.pl_typ_id = plt.pl_typ_id and plt.comp_typ_cd in ('ICM','ICM4','ICM7') and sysdate between pl.effective_start_date and pl.effective_end_date and sysdate between plt.effective_start_date and plt.effective_end_date and chc.elig_per_elctbl_chc_id = hier.mgr_elig_per_elctbl_chc_id and hier.lvl_num+1 > 0 and hier.lvl_num+1 <= 3 and hier.emp_elig_per_elctbl_chc_id = chc2.elig_per_elctbl_chc_id and chc2.elig_per_elctbl_chc_id = enr.elig_per_elctbl_chc_id and enr.acty_typ_cd = 'CWBWS' and enr.prtt_rt_val_id = prt.prtt_rt_val_id (+) and chc2.elig_per_elctbl_chc_id = enr2.elig_per_elctbl_chc_id (+) and enr2.acty_typ_cd(+) = 'CWBGP' and enr2.prtt_rt_val_id = prt2.prtt_rt_val_id (+) and chc2.per_in_ler_id = pil.per_in_ler_id and chc2.elig_flag = 'Y' and chc2.elig_flag = 'Y' and pil.per_in_ler_stat_cd in ('STRTD','PROCD') and pil.person_id = ppf.person_id and sysdate between ppf.effective_start_date and ppf.effective_end_date and chc2.pil_elctbl_chc_popl_id = popl.pil_elctbl_chc_popl_id and chc2.assignment_id = ppp.assignment_id (+) and ppp.approved (+) = 'Y' and ppp.change_date = nvl((SELECT max(ppp2.change_date) from per_pay_proposals ppp2 where ppp2.assignment_id = ppp.assignment_id and ppp2.approved = 'Y' and ppp2.change_date <= yr.start_date), (SELECT min(ppp2.change_date) from per_pay_proposals ppp2 where ppp2.assignment_id = ppp.assignment_id and ppp2.approved = 'Y' and ppp2.change_date > yr.start_date)) group by chc.ws_mgr_id , chc.yr_perd_id , ppf.first_name || ' ' || ppf.last_name , ppf.full_name , popl.uom, ppp.proposed_salary_n UNION SELECT min(hier.lvl_num+1) levels_down, ppf.first_name || ' ' || ppf.last_name emp_brief_name, ppf.full_name emp_full_name, popl.uom currency, ppp.proposed_salary_n beg_salary, sum(decode(plt.comp_typ_cd,'ICM7',nvl(nvl(prt.rt_val,enr.val),0))) salary_val, ppp.proposed_salary_n + sum(decode(plt.comp_typ_cd,'ICM7',nvl(nvl(prt.rt_val,enr.val),0))) end_salary, sum(decode(plt.comp_typ_cd,'ICM',nvl(nvl(prt.rt_val,enr.val),0))) bonus_val, nvl(ppp.proposed_salary_n + sum(decode(plt.comp_typ_cd,'ICM7',nvl(prt.rt_val,enr.val))) + sum(decode(plt.comp_typ_cd,'ICM',nvl(prt.rt_val,enr.val))),0) total_comp, sum(decode(plt.comp_typ_cd,'ICM4',decode (enr.nnmntry_uom, 'SHR',nvl(prt.rt_val,enr.val), '',round(nvl(prt.rt_val,enr.val)/nvl(prt2.rt_val,enr2.val),0)))) stock_shares, nvl(sum(decode(plt.comp_typ_cd,'ICM4', decode (enr.nnmntry_uom, 'SHR', nvl(prt.rt_val,enr.val)*nvl(prt2.rt_val,enr2.val), '',nvl(prt.rt_val,enr.val)))),0) stock_val, chc.ws_mgr_id , chc.yr_perd_id , 4 from ben_elig_per_elctbl_chc chc, ben_pil_elctbl_chc_popl popl, ben_pl_f pl, ben_pl_typ_f plt, ben_cwb_mgr_hrchy hier, ben_per_in_ler pil, ben_elig_per_elctbl_chc chc2, ben_enrt_rt enr, ben_prtt_rt_val prt, ben_enrt_rt enr2, ben_prtt_rt_val prt2, ben_yr_perd yr, per_all_people_f ppf, per_pay_proposals ppp where chc.yr_perd_id = yr.yr_perd_id and chc.pl_id = pl.pl_id and chc.pl_typ_id = plt.pl_typ_id and plt.comp_typ_cd in ('ICM','ICM4','ICM7') and sysdate between pl.effective_start_date and pl.effective_end_date and sysdate between plt.effective_start_date and plt.effective_end_date and chc.elig_per_elctbl_chc_id = hier.mgr_elig_per_elctbl_chc_id and hier.lvl_num+1 > 0 and hier.lvl_num+1 <= 4 and hier.emp_elig_per_elctbl_chc_id = chc2.elig_per_elctbl_chc_id and chc2.elig_per_elctbl_chc_id = enr.elig_per_elctbl_chc_id and enr.acty_typ_cd = 'CWBWS' and enr.prtt_rt_val_id = prt.prtt_rt_val_id (+) and chc2.elig_per_elctbl_chc_id = enr2.elig_per_elctbl_chc_id (+) and enr2.acty_typ_cd(+) = 'CWBGP' and enr2.prtt_rt_val_id = prt2.prtt_rt_val_id (+) and chc2.per_in_ler_id = pil.per_in_ler_id and chc2.elig_flag = 'Y' and chc2.elig_flag = 'Y' and pil.per_in_ler_stat_cd in ('STRTD','PROCD') and pil.person_id = ppf.person_id and sysdate between ppf.effective_start_date and ppf.effective_end_date and chc2.pil_elctbl_chc_popl_id = popl.pil_elctbl_chc_popl_id and chc2.assignment_id = ppp.assignment_id (+) and ppp.approved (+) = 'Y' and ppp.change_date = nvl((SELECT max(ppp2.change_date) from per_pay_proposals ppp2 where ppp2.assignment_id = ppp.assignment_id and ppp2.approved = 'Y' and ppp2.change_date <= yr.start_date), (SELECT min(ppp2.change_date) from per_pay_proposals ppp2 where ppp2.assignment_id = ppp.assignment_id and ppp2.approved = 'Y' and ppp2.change_date > yr.start_date)) group by chc.ws_mgr_id , chc.yr_perd_id , ppf.first_name || ' ' || ppf.last_name , ppf.full_name , popl.uom, ppp.proposed_salary_n UNION SELECT min(hier.lvl_num+1) levels_down, ppf.first_name || ' ' || ppf.last_name emp_brief_name, ppf.full_name emp_full_name, popl.uom currency, ppp.proposed_salary_n beg_salary, sum(decode(plt.comp_typ_cd,'ICM7',nvl(nvl(prt.rt_val,enr.val),0))) salary_val, ppp.proposed_salary_n + sum(decode(plt.comp_typ_cd,'ICM7',nvl(nvl(prt.rt_val,enr.val),0))) end_salary, sum(decode(plt.comp_typ_cd,'ICM',nvl(nvl(prt.rt_val,enr.val),0))) bonus_val, nvl(ppp.proposed_salary_n + sum(decode(plt.comp_typ_cd,'ICM7',nvl(prt.rt_val,enr.val))) + sum(decode(plt.comp_typ_cd,'ICM',nvl(prt.rt_val,enr.val))),0) total_comp, sum(decode(plt.comp_typ_cd,'ICM4',decode (enr.nnmntry_uom, 'SHR',nvl(prt.rt_val,enr.val), '',round(nvl(prt.rt_val,enr.val)/nvl(prt2.rt_val,enr2.val),0)))) stock_shares, nvl(sum(decode(plt.comp_typ_cd,'ICM4', decode (enr.nnmntry_uom, 'SHR', nvl(prt.rt_val,enr.val)*nvl(prt2.rt_val,enr2.val), '',nvl(prt.rt_val,enr.val)))),0) stock_val, chc.ws_mgr_id , chc.yr_perd_id , 5 from ben_elig_per_elctbl_chc chc, ben_pil_elctbl_chc_popl popl, ben_pl_f pl, ben_pl_typ_f plt, ben_cwb_mgr_hrchy hier, ben_per_in_ler pil, ben_elig_per_elctbl_chc chc2, ben_enrt_rt enr, ben_prtt_rt_val prt, ben_enrt_rt enr2, ben_prtt_rt_val prt2, ben_yr_perd yr, per_all_people_f ppf, per_pay_proposals ppp where chc.yr_perd_id = yr.yr_perd_id and chc.pl_id = pl.pl_id and chc.pl_typ_id = plt.pl_typ_id and plt.comp_typ_cd in ('ICM','ICM4','ICM7') and sysdate between pl.effective_start_date and pl.effective_end_date and sysdate between plt.effective_start_date and plt.effective_end_date and chc.elig_per_elctbl_chc_id = hier.mgr_elig_per_elctbl_chc_id and hier.lvl_num+1 > 0 and hier.lvl_num+1 <= 5 and hier.emp_elig_per_elctbl_chc_id = chc2.elig_per_elctbl_chc_id and chc2.elig_per_elctbl_chc_id = enr.elig_per_elctbl_chc_id and enr.acty_typ_cd = 'CWBWS' and enr.prtt_rt_val_id = prt.prtt_rt_val_id (+) and chc2.elig_per_elctbl_chc_id = enr2.elig_per_elctbl_chc_id (+) and enr2.acty_typ_cd(+) = 'CWBGP' and enr2.prtt_rt_val_id = prt2.prtt_rt_val_id (+) and chc2.per_in_ler_id = pil.per_in_ler_id and chc2.elig_flag = 'Y' and chc2.elig_flag = 'Y' and pil.per_in_ler_stat_cd in ('STRTD','PROCD') and pil.person_id = ppf.person_id and sysdate between ppf.effective_start_date and ppf.effective_end_date and chc2.pil_elctbl_chc_popl_id = popl.pil_elctbl_chc_popl_id and chc2.assignment_id = ppp.assignment_id (+) and ppp.approved (+) = 'Y' and ppp.change_date = nvl((SELECT max(ppp2.change_date) from per_pay_proposals ppp2 where ppp2.assignment_id = ppp.assignment_id and ppp2.approved = 'Y' and ppp2.change_date <= yr.start_date), (SELECT min(ppp2.change_date) from per_pay_proposals ppp2 where ppp2.assignment_id = ppp.assignment_id and ppp2.approved = 'Y' and ppp2.change_date > yr.start_date)) group by chc.ws_mgr_id , chc.yr_perd_id , ppf.first_name || ' ' || ppf.last_name , ppf.full_name , popl.uom, ppp.proposed_salary_n UNION SELECT min(hier.lvl_num+1) levels_down, ppf.first_name || ' ' || ppf.last_name emp_brief_name, ppf.full_name emp_full_name, popl.uom currency, ppp.proposed_salary_n beg_salary, sum(decode(plt.comp_typ_cd,'ICM7',nvl(nvl(prt.rt_val,enr.val),0))) salary_val, ppp.proposed_salary_n + sum(decode(plt.comp_typ_cd,'ICM7',nvl(nvl(prt.rt_val,enr.val),0))) end_salary, sum(decode(plt.comp_typ_cd,'ICM',nvl(nvl(prt.rt_val,enr.val),0))) bonus_val, nvl(ppp.proposed_salary_n + sum(decode(plt.comp_typ_cd,'ICM7',nvl(prt.rt_val,enr.val))) + sum(decode(plt.comp_typ_cd,'ICM',nvl(prt.rt_val,enr.val))),0) total_comp, sum(decode(plt.comp_typ_cd,'ICM4',decode (enr.nnmntry_uom, 'SHR',nvl(prt.rt_val,enr.val), '',round(nvl(prt.rt_val,enr.val)/nvl(prt2.rt_val,enr2.val),0)))) stock_shares, nvl(sum(decode(plt.comp_typ_cd,'ICM4', decode (enr.nnmntry_uom, 'SHR', nvl(prt.rt_val,enr.val)*nvl(prt2.rt_val,enr2.val), '',nvl(prt.rt_val,enr.val)))),0) stock_val, chc.ws_mgr_id , chc.yr_perd_id , 6 from ben_elig_per_elctbl_chc chc, ben_pil_elctbl_chc_popl popl, ben_pl_f pl, ben_pl_typ_f plt, ben_cwb_mgr_hrchy hier, ben_per_in_ler pil, ben_elig_per_elctbl_chc chc2, ben_enrt_rt enr, ben_prtt_rt_val prt, ben_enrt_rt enr2, ben_prtt_rt_val prt2, ben_yr_perd yr, per_all_people_f ppf, per_pay_proposals ppp where chc.yr_perd_id = yr.yr_perd_id and chc.pl_id = pl.pl_id and chc.pl_typ_id = plt.pl_typ_id and plt.comp_typ_cd in ('ICM','ICM4','ICM7') and sysdate between pl.effective_start_date and pl.effective_end_date and sysdate between plt.effective_start_date and plt.effective_end_date and chc.elig_per_elctbl_chc_id = hier.mgr_elig_per_elctbl_chc_id and hier.lvl_num+1 > 0 and hier.emp_elig_per_elctbl_chc_id = chc2.elig_per_elctbl_chc_id and chc2.elig_per_elctbl_chc_id = enr.elig_per_elctbl_chc_id and enr.acty_typ_cd = 'CWBWS' and enr.prtt_rt_val_id = prt.prtt_rt_val_id (+) and chc2.elig_per_elctbl_chc_id = enr2.elig_per_elctbl_chc_id (+) and enr2.acty_typ_cd(+) = 'CWBGP' and enr2.prtt_rt_val_id = prt2.prtt_rt_val_id (+) and chc2.per_in_ler_id = pil.per_in_ler_id and chc2.elig_flag = 'Y' and chc2.elig_flag = 'Y' and pil.per_in_ler_stat_cd in ('STRTD','PROCD') and pil.person_id = ppf.person_id and sysdate between ppf.effective_start_date and ppf.effective_end_date and chc2.pil_elctbl_chc_popl_id = popl.pil_elctbl_chc_popl_id and chc2.assignment_id = ppp.assignment_id (+) and ppp.approved (+) = 'Y' and ppp.change_date = nvl((SELECT max(ppp2.change_date) from per_pay_proposals ppp2 where ppp2.assignment_id = ppp.assignment_id and ppp2.approved = 'Y' and ppp2.change_date <= yr.start_date), (SELECT min(ppp2.change_date) from per_pay_proposals ppp2 where ppp2.assignment_id = ppp.assignment_id and ppp2.approved = 'Y' and ppp2.change_date > yr.start_date)) group by chc.ws_mgr_id , chc.yr_perd_id , ppf.first_name || ' ' || ppf.last_name , ppf.full_name , popl.uom, ppp.proposed_salary_n

View Text - HTML Formatted

SELECT MIN(HIER.LVL_NUM+1) LEVELS_DOWN
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME EMP_BRIEF_NAME
, PPF.FULL_NAME EMP_FULL_NAME
, POPL.UOM CURRENCY
, PPP.PROPOSED_SALARY_N BEG_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) SALARY_VAL
, PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) END_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) BONUS_VAL
, NVL(PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(PRT.RT_VAL
, ENR.VAL))) + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(PRT.RT_VAL
, ENR.VAL)))
, 0) TOTAL_COMP
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)
, ''
, ROUND(NVL(PRT.RT_VAL
, ENR.VAL)/NVL(PRT2.RT_VAL
, ENR2.VAL)
, 0)))) STOCK_SHARES
, NVL(SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)*NVL(PRT2.RT_VAL
, ENR2.VAL)
, ''
, NVL(PRT.RT_VAL
, ENR.VAL))))
, 0) STOCK_VAL
, CHC.WS_MGR_ID MGR_ID_PARAM
, CHC.YR_PERD_ID PERD_ID_PARAM
, 1 LEVELS_DOWN_PARAM
FROM BEN_ELIG_PER_ELCTBL_CHC CHC
, BEN_PIL_ELCTBL_CHC_POPL POPL
, BEN_PL_F PL
, BEN_PL_TYP_F PLT
, BEN_CWB_MGR_HRCHY HIER
, BEN_PER_IN_LER PIL
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_ENRT_RT ENR
, BEN_PRTT_RT_VAL PRT
, BEN_ENRT_RT ENR2
, BEN_PRTT_RT_VAL PRT2
, BEN_YR_PERD YR
, PER_ALL_PEOPLE_F PPF
, PER_PAY_PROPOSALS PPP
WHERE CHC.YR_PERD_ID = YR.YR_PERD_ID
AND CHC.PL_ID = PL.PL_ID
AND CHC.PL_TYP_ID = PLT.PL_TYP_ID
AND PLT.COMP_TYP_CD IN ('ICM'
, 'ICM4'
, 'ICM7')
AND SYSDATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PLT.EFFECTIVE_START_DATE
AND PLT.EFFECTIVE_END_DATE
AND CHC.ELIG_PER_ELCTBL_CHC_ID = HIER.MGR_ELIG_PER_ELCTBL_CHC_ID
AND HIER.LVL_NUM+1 > 0
AND HIER.LVL_NUM+1 <= 1
AND HIER.EMP_ELIG_PER_ELCTBL_CHC_ID = CHC2.ELIG_PER_ELCTBL_CHC_ID
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID
AND ENR.ACTY_TYP_CD = 'CWBWS'
AND ENR.PRTT_RT_VAL_ID = PRT.PRTT_RT_VAL_ID (+)
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR2.ELIG_PER_ELCTBL_CHC_ID (+)
AND ENR2.ACTY_TYP_CD(+) = 'CWBGP'
AND ENR2.PRTT_RT_VAL_ID = PRT2.PRTT_RT_VAL_ID (+)
AND CHC2.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND CHC2.ELIG_FLAG = 'Y'
AND CHC2.ELIG_FLAG = 'Y'
AND PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PIL.PERSON_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND CHC2.PIL_ELCTBL_CHC_POPL_ID = POPL.PIL_ELCTBL_CHC_POPL_ID
AND CHC2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID (+)
AND PPP.APPROVED (+) = 'Y'
AND PPP.CHANGE_DATE = NVL((SELECT MAX(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE <= YR.START_DATE)
, (SELECT MIN(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE > YR.START_DATE)) GROUP BY CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME
, PPF.FULL_NAME
, POPL.UOM
, PPP.PROPOSED_SALARY_N UNION SELECT MIN(HIER.LVL_NUM+1) LEVELS_DOWN
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME EMP_BRIEF_NAME
, PPF.FULL_NAME EMP_FULL_NAME
, POPL.UOM CURRENCY
, PPP.PROPOSED_SALARY_N BEG_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) SALARY_VAL
, PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) END_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) BONUS_VAL
, NVL(PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(PRT.RT_VAL
, ENR.VAL))) + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(PRT.RT_VAL
, ENR.VAL)))
, 0) TOTAL_COMP
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)
, ''
, ROUND(NVL(PRT.RT_VAL
, ENR.VAL)/NVL(PRT2.RT_VAL
, ENR2.VAL)
, 0)))) STOCK_SHARES
, NVL(SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)*NVL(PRT2.RT_VAL
, ENR2.VAL)
, ''
, NVL(PRT.RT_VAL
, ENR.VAL))))
, 0) STOCK_VAL
, CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, 2
FROM BEN_ELIG_PER_ELCTBL_CHC CHC
, BEN_PIL_ELCTBL_CHC_POPL POPL
, BEN_PL_F PL
, BEN_PL_TYP_F PLT
, BEN_CWB_MGR_HRCHY HIER
, BEN_PER_IN_LER PIL
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_ENRT_RT ENR
, BEN_PRTT_RT_VAL PRT
, BEN_ENRT_RT ENR2
, BEN_PRTT_RT_VAL PRT2
, BEN_YR_PERD YR
, PER_ALL_PEOPLE_F PPF
, PER_PAY_PROPOSALS PPP
WHERE CHC.YR_PERD_ID = YR.YR_PERD_ID
AND CHC.PL_ID = PL.PL_ID
AND CHC.PL_TYP_ID = PLT.PL_TYP_ID
AND PLT.COMP_TYP_CD IN ('ICM'
, 'ICM4'
, 'ICM7')
AND SYSDATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PLT.EFFECTIVE_START_DATE
AND PLT.EFFECTIVE_END_DATE
AND CHC.ELIG_PER_ELCTBL_CHC_ID = HIER.MGR_ELIG_PER_ELCTBL_CHC_ID
AND HIER.LVL_NUM+1 > 0
AND HIER.LVL_NUM+1 <= 2
AND HIER.EMP_ELIG_PER_ELCTBL_CHC_ID = CHC2.ELIG_PER_ELCTBL_CHC_ID
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID
AND ENR.ACTY_TYP_CD = 'CWBWS'
AND ENR.PRTT_RT_VAL_ID = PRT.PRTT_RT_VAL_ID (+)
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR2.ELIG_PER_ELCTBL_CHC_ID (+)
AND ENR2.ACTY_TYP_CD(+) = 'CWBGP'
AND ENR2.PRTT_RT_VAL_ID = PRT2.PRTT_RT_VAL_ID (+)
AND CHC2.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND CHC2.ELIG_FLAG = 'Y'
AND CHC2.ELIG_FLAG = 'Y'
AND PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PIL.PERSON_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND CHC2.PIL_ELCTBL_CHC_POPL_ID = POPL.PIL_ELCTBL_CHC_POPL_ID
AND CHC2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID (+)
AND PPP.APPROVED (+) = 'Y'
AND PPP.CHANGE_DATE = NVL((SELECT MAX(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE <= YR.START_DATE)
, (SELECT MIN(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE > YR.START_DATE)) GROUP BY CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME
, PPF.FULL_NAME
, POPL.UOM
, PPP.PROPOSED_SALARY_N UNION SELECT MIN(HIER.LVL_NUM+1) LEVELS_DOWN
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME EMP_BRIEF_NAME
, PPF.FULL_NAME EMP_FULL_NAME
, POPL.UOM CURRENCY
, PPP.PROPOSED_SALARY_N BEG_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) SALARY_VAL
, PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) END_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) BONUS_VAL
, NVL(PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(PRT.RT_VAL
, ENR.VAL))) + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(PRT.RT_VAL
, ENR.VAL)))
, 0) TOTAL_COMP
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)
, ''
, ROUND(NVL(PRT.RT_VAL
, ENR.VAL)/NVL(PRT2.RT_VAL
, ENR2.VAL)
, 0)))) STOCK_SHARES
, NVL(SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)*NVL(PRT2.RT_VAL
, ENR2.VAL)
, ''
, NVL(PRT.RT_VAL
, ENR.VAL))))
, 0) STOCK_VAL
, CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, 3
FROM BEN_ELIG_PER_ELCTBL_CHC CHC
, BEN_PIL_ELCTBL_CHC_POPL POPL
, BEN_PL_F PL
, BEN_PL_TYP_F PLT
, BEN_CWB_MGR_HRCHY HIER
, BEN_PER_IN_LER PIL
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_ENRT_RT ENR
, BEN_PRTT_RT_VAL PRT
, BEN_ENRT_RT ENR2
, BEN_PRTT_RT_VAL PRT2
, BEN_YR_PERD YR
, PER_ALL_PEOPLE_F PPF
, PER_PAY_PROPOSALS PPP
WHERE CHC.YR_PERD_ID = YR.YR_PERD_ID
AND CHC.PL_ID = PL.PL_ID
AND CHC.PL_TYP_ID = PLT.PL_TYP_ID
AND PLT.COMP_TYP_CD IN ('ICM'
, 'ICM4'
, 'ICM7')
AND SYSDATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PLT.EFFECTIVE_START_DATE
AND PLT.EFFECTIVE_END_DATE
AND CHC.ELIG_PER_ELCTBL_CHC_ID = HIER.MGR_ELIG_PER_ELCTBL_CHC_ID
AND HIER.LVL_NUM+1 > 0
AND HIER.LVL_NUM+1 <= 3
AND HIER.EMP_ELIG_PER_ELCTBL_CHC_ID = CHC2.ELIG_PER_ELCTBL_CHC_ID
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID
AND ENR.ACTY_TYP_CD = 'CWBWS'
AND ENR.PRTT_RT_VAL_ID = PRT.PRTT_RT_VAL_ID (+)
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR2.ELIG_PER_ELCTBL_CHC_ID (+)
AND ENR2.ACTY_TYP_CD(+) = 'CWBGP'
AND ENR2.PRTT_RT_VAL_ID = PRT2.PRTT_RT_VAL_ID (+)
AND CHC2.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND CHC2.ELIG_FLAG = 'Y'
AND CHC2.ELIG_FLAG = 'Y'
AND PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PIL.PERSON_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND CHC2.PIL_ELCTBL_CHC_POPL_ID = POPL.PIL_ELCTBL_CHC_POPL_ID
AND CHC2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID (+)
AND PPP.APPROVED (+) = 'Y'
AND PPP.CHANGE_DATE = NVL((SELECT MAX(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE <= YR.START_DATE)
, (SELECT MIN(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE > YR.START_DATE)) GROUP BY CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME
, PPF.FULL_NAME
, POPL.UOM
, PPP.PROPOSED_SALARY_N UNION SELECT MIN(HIER.LVL_NUM+1) LEVELS_DOWN
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME EMP_BRIEF_NAME
, PPF.FULL_NAME EMP_FULL_NAME
, POPL.UOM CURRENCY
, PPP.PROPOSED_SALARY_N BEG_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) SALARY_VAL
, PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) END_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) BONUS_VAL
, NVL(PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(PRT.RT_VAL
, ENR.VAL))) + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(PRT.RT_VAL
, ENR.VAL)))
, 0) TOTAL_COMP
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)
, ''
, ROUND(NVL(PRT.RT_VAL
, ENR.VAL)/NVL(PRT2.RT_VAL
, ENR2.VAL)
, 0)))) STOCK_SHARES
, NVL(SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)*NVL(PRT2.RT_VAL
, ENR2.VAL)
, ''
, NVL(PRT.RT_VAL
, ENR.VAL))))
, 0) STOCK_VAL
, CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, 4
FROM BEN_ELIG_PER_ELCTBL_CHC CHC
, BEN_PIL_ELCTBL_CHC_POPL POPL
, BEN_PL_F PL
, BEN_PL_TYP_F PLT
, BEN_CWB_MGR_HRCHY HIER
, BEN_PER_IN_LER PIL
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_ENRT_RT ENR
, BEN_PRTT_RT_VAL PRT
, BEN_ENRT_RT ENR2
, BEN_PRTT_RT_VAL PRT2
, BEN_YR_PERD YR
, PER_ALL_PEOPLE_F PPF
, PER_PAY_PROPOSALS PPP
WHERE CHC.YR_PERD_ID = YR.YR_PERD_ID
AND CHC.PL_ID = PL.PL_ID
AND CHC.PL_TYP_ID = PLT.PL_TYP_ID
AND PLT.COMP_TYP_CD IN ('ICM'
, 'ICM4'
, 'ICM7')
AND SYSDATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PLT.EFFECTIVE_START_DATE
AND PLT.EFFECTIVE_END_DATE
AND CHC.ELIG_PER_ELCTBL_CHC_ID = HIER.MGR_ELIG_PER_ELCTBL_CHC_ID
AND HIER.LVL_NUM+1 > 0
AND HIER.LVL_NUM+1 <= 4
AND HIER.EMP_ELIG_PER_ELCTBL_CHC_ID = CHC2.ELIG_PER_ELCTBL_CHC_ID
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID
AND ENR.ACTY_TYP_CD = 'CWBWS'
AND ENR.PRTT_RT_VAL_ID = PRT.PRTT_RT_VAL_ID (+)
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR2.ELIG_PER_ELCTBL_CHC_ID (+)
AND ENR2.ACTY_TYP_CD(+) = 'CWBGP'
AND ENR2.PRTT_RT_VAL_ID = PRT2.PRTT_RT_VAL_ID (+)
AND CHC2.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND CHC2.ELIG_FLAG = 'Y'
AND CHC2.ELIG_FLAG = 'Y'
AND PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PIL.PERSON_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND CHC2.PIL_ELCTBL_CHC_POPL_ID = POPL.PIL_ELCTBL_CHC_POPL_ID
AND CHC2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID (+)
AND PPP.APPROVED (+) = 'Y'
AND PPP.CHANGE_DATE = NVL((SELECT MAX(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE <= YR.START_DATE)
, (SELECT MIN(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE > YR.START_DATE)) GROUP BY CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME
, PPF.FULL_NAME
, POPL.UOM
, PPP.PROPOSED_SALARY_N UNION SELECT MIN(HIER.LVL_NUM+1) LEVELS_DOWN
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME EMP_BRIEF_NAME
, PPF.FULL_NAME EMP_FULL_NAME
, POPL.UOM CURRENCY
, PPP.PROPOSED_SALARY_N BEG_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) SALARY_VAL
, PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) END_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) BONUS_VAL
, NVL(PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(PRT.RT_VAL
, ENR.VAL))) + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(PRT.RT_VAL
, ENR.VAL)))
, 0) TOTAL_COMP
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)
, ''
, ROUND(NVL(PRT.RT_VAL
, ENR.VAL)/NVL(PRT2.RT_VAL
, ENR2.VAL)
, 0)))) STOCK_SHARES
, NVL(SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)*NVL(PRT2.RT_VAL
, ENR2.VAL)
, ''
, NVL(PRT.RT_VAL
, ENR.VAL))))
, 0) STOCK_VAL
, CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, 5
FROM BEN_ELIG_PER_ELCTBL_CHC CHC
, BEN_PIL_ELCTBL_CHC_POPL POPL
, BEN_PL_F PL
, BEN_PL_TYP_F PLT
, BEN_CWB_MGR_HRCHY HIER
, BEN_PER_IN_LER PIL
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_ENRT_RT ENR
, BEN_PRTT_RT_VAL PRT
, BEN_ENRT_RT ENR2
, BEN_PRTT_RT_VAL PRT2
, BEN_YR_PERD YR
, PER_ALL_PEOPLE_F PPF
, PER_PAY_PROPOSALS PPP
WHERE CHC.YR_PERD_ID = YR.YR_PERD_ID
AND CHC.PL_ID = PL.PL_ID
AND CHC.PL_TYP_ID = PLT.PL_TYP_ID
AND PLT.COMP_TYP_CD IN ('ICM'
, 'ICM4'
, 'ICM7')
AND SYSDATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PLT.EFFECTIVE_START_DATE
AND PLT.EFFECTIVE_END_DATE
AND CHC.ELIG_PER_ELCTBL_CHC_ID = HIER.MGR_ELIG_PER_ELCTBL_CHC_ID
AND HIER.LVL_NUM+1 > 0
AND HIER.LVL_NUM+1 <= 5
AND HIER.EMP_ELIG_PER_ELCTBL_CHC_ID = CHC2.ELIG_PER_ELCTBL_CHC_ID
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID
AND ENR.ACTY_TYP_CD = 'CWBWS'
AND ENR.PRTT_RT_VAL_ID = PRT.PRTT_RT_VAL_ID (+)
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR2.ELIG_PER_ELCTBL_CHC_ID (+)
AND ENR2.ACTY_TYP_CD(+) = 'CWBGP'
AND ENR2.PRTT_RT_VAL_ID = PRT2.PRTT_RT_VAL_ID (+)
AND CHC2.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND CHC2.ELIG_FLAG = 'Y'
AND CHC2.ELIG_FLAG = 'Y'
AND PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PIL.PERSON_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND CHC2.PIL_ELCTBL_CHC_POPL_ID = POPL.PIL_ELCTBL_CHC_POPL_ID
AND CHC2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID (+)
AND PPP.APPROVED (+) = 'Y'
AND PPP.CHANGE_DATE = NVL((SELECT MAX(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE <= YR.START_DATE)
, (SELECT MIN(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE > YR.START_DATE)) GROUP BY CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME
, PPF.FULL_NAME
, POPL.UOM
, PPP.PROPOSED_SALARY_N UNION SELECT MIN(HIER.LVL_NUM+1) LEVELS_DOWN
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME EMP_BRIEF_NAME
, PPF.FULL_NAME EMP_FULL_NAME
, POPL.UOM CURRENCY
, PPP.PROPOSED_SALARY_N BEG_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) SALARY_VAL
, PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) END_SALARY
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(NVL(PRT.RT_VAL
, ENR.VAL)
, 0))) BONUS_VAL
, NVL(PPP.PROPOSED_SALARY_N + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM7'
, NVL(PRT.RT_VAL
, ENR.VAL))) + SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM'
, NVL(PRT.RT_VAL
, ENR.VAL)))
, 0) TOTAL_COMP
, SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)
, ''
, ROUND(NVL(PRT.RT_VAL
, ENR.VAL)/NVL(PRT2.RT_VAL
, ENR2.VAL)
, 0)))) STOCK_SHARES
, NVL(SUM(DECODE(PLT.COMP_TYP_CD
, 'ICM4'
, DECODE (ENR.NNMNTRY_UOM
, 'SHR'
, NVL(PRT.RT_VAL
, ENR.VAL)*NVL(PRT2.RT_VAL
, ENR2.VAL)
, ''
, NVL(PRT.RT_VAL
, ENR.VAL))))
, 0) STOCK_VAL
, CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, 6
FROM BEN_ELIG_PER_ELCTBL_CHC CHC
, BEN_PIL_ELCTBL_CHC_POPL POPL
, BEN_PL_F PL
, BEN_PL_TYP_F PLT
, BEN_CWB_MGR_HRCHY HIER
, BEN_PER_IN_LER PIL
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_ENRT_RT ENR
, BEN_PRTT_RT_VAL PRT
, BEN_ENRT_RT ENR2
, BEN_PRTT_RT_VAL PRT2
, BEN_YR_PERD YR
, PER_ALL_PEOPLE_F PPF
, PER_PAY_PROPOSALS PPP
WHERE CHC.YR_PERD_ID = YR.YR_PERD_ID
AND CHC.PL_ID = PL.PL_ID
AND CHC.PL_TYP_ID = PLT.PL_TYP_ID
AND PLT.COMP_TYP_CD IN ('ICM'
, 'ICM4'
, 'ICM7')
AND SYSDATE BETWEEN PL.EFFECTIVE_START_DATE
AND PL.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PLT.EFFECTIVE_START_DATE
AND PLT.EFFECTIVE_END_DATE
AND CHC.ELIG_PER_ELCTBL_CHC_ID = HIER.MGR_ELIG_PER_ELCTBL_CHC_ID
AND HIER.LVL_NUM+1 > 0
AND HIER.EMP_ELIG_PER_ELCTBL_CHC_ID = CHC2.ELIG_PER_ELCTBL_CHC_ID
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID
AND ENR.ACTY_TYP_CD = 'CWBWS'
AND ENR.PRTT_RT_VAL_ID = PRT.PRTT_RT_VAL_ID (+)
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR2.ELIG_PER_ELCTBL_CHC_ID (+)
AND ENR2.ACTY_TYP_CD(+) = 'CWBGP'
AND ENR2.PRTT_RT_VAL_ID = PRT2.PRTT_RT_VAL_ID (+)
AND CHC2.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND CHC2.ELIG_FLAG = 'Y'
AND CHC2.ELIG_FLAG = 'Y'
AND PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PIL.PERSON_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND CHC2.PIL_ELCTBL_CHC_POPL_ID = POPL.PIL_ELCTBL_CHC_POPL_ID
AND CHC2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID (+)
AND PPP.APPROVED (+) = 'Y'
AND PPP.CHANGE_DATE = NVL((SELECT MAX(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE <= YR.START_DATE)
, (SELECT MIN(PPP2.CHANGE_DATE)
FROM PER_PAY_PROPOSALS PPP2
WHERE PPP2.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
AND PPP2.APPROVED = 'Y'
AND PPP2.CHANGE_DATE > YR.START_DATE)) GROUP BY CHC.WS_MGR_ID
, CHC.YR_PERD_ID
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME
, PPF.FULL_NAME
, POPL.UOM
, PPP.PROPOSED_SALARY_N