VIEW: APPS.BEN_CWB_REPT_COMP_COMPARISON_V

Source

View Text - Preformatted

SELECT asnd_lf_evt_dt, manager_name, manager_full_name, plan_name, country, eligible_employees, Eligible_Emps_w_val, round((eligible_emps_w_val/decode(eligible_employees,0,1,eligible_employees)*100),1) pct_w_val, total_allocated , nvl(rate_units,pl_currency) units, round((total_allocated/decode(eligible_emps_w_val,0,1,eligible_emps_w_val))) average_allocated, decode(preserve_cd, 'A',budget_amount,(budget_percent*eligible_salaries/100)) budget, decode(preserve_cd, 'A',budget_amount,(budget_percent*eligible_salaries/100))-nvl(total_allocated,0) unused_budget, manager_id, enrt_perd_id from ( select ppf.person_id person_id, ppf.first_name || ' ' || ppf.last_name || ' ' || ppf.suffix manager_name , ppf.full_name manager_full_name, perd.asnd_lf_evt_dt asnd_lf_evt_dt, pl.name || '  ' || opt.name plan_name, bg.legislation_code country, lookup.meaning rate_units, pl.nip_pl_uom pl_currency, perd.prsvr_bdgt_cd preserve_cd, max(decode (hrchy_all.lvl_num ,'0', (select count(chc1.elig_per_elctbl_chc_id) from ben_pil_elctbl_chc_popl popl1, ben_elig_per_elctbl_chc chc1, ben_cwb_hrchy hrchy where popl.pil_elctbl_chc_popl_id = hrchy.mgr_pil_elctbl_chc_popl_id and popl1.pil_elctbl_chc_popl_id = hrchy.emp_pil_elctbl_chc_popl_id and hrchy.lvl_num = 1 and popl1.pil_elctbl_chc_popl_id = chc1.pil_elctbl_chc_popl_id and chc1.elig_flag='Y' and nvl(chc.oipl_id,chc.pl_id) = nvl(chc1.oipl_id , chc1.pl_id) ), (select count(chc2.elig_per_elctbl_chc_id) from ben_pil_elctbl_chc_popl popl2, ben_elig_per_elctbl_chc chc2, ben_cwb_hrchy hrchy where popl.pil_elctbl_chc_popl_id = hrchy.mgr_pil_elctbl_chc_popl_id and popl2.pil_elctbl_chc_popl_id = hrchy.emp_pil_elctbl_chc_popl_id and hrchy.lvl_num > 0 and popl2.pil_elctbl_chc_popl_id = chc2.pil_elctbl_chc_popl_id and chc2.elig_flag='Y' and nvl(chc.oipl_id,chc.pl_id) = nvl(chc2.oipl_id , chc2.pl_id) )) ) Eligible_Employees , max(decode (hrchy_all.lvl_num ,'0', (select count(chc1.elig_per_elctbl_chc_id) from ben_pil_elctbl_chc_popl popl1, ben_elig_per_elctbl_chc chc1, ben_cwb_hrchy hrchy, ben_enrt_rt enr where popl.pil_elctbl_chc_popl_id = hrchy.mgr_pil_elctbl_chc_popl_id and popl1.pil_elctbl_chc_popl_id = hrchy.emp_pil_elctbl_chc_popl_id and hrchy.lvl_num = 1 and popl1.pil_elctbl_chc_popl_id = chc1.pil_elctbl_chc_popl_id and chc1.elig_flag='Y' and nvl(chc.oipl_id,chc.pl_id) = nvl(chc1.oipl_id , chc1.pl_id) and chc1.elig_per_elctbl_chc_id = enr.elig_per_elctbl_chc_id and enr.acty_typ_cd = 'CWBWS' and enr.val > 0 ), (select count(chc2.elig_per_elctbl_chc_id) from ben_pil_elctbl_chc_popl popl2, ben_elig_per_elctbl_chc chc2, ben_cwb_hrchy hrchy, ben_enrt_rt enr where popl.pil_elctbl_chc_popl_id = hrchy.mgr_pil_elctbl_chc_popl_id and popl2.pil_elctbl_chc_popl_id = hrchy.emp_pil_elctbl_chc_popl_id and hrchy.lvl_num > 0 and popl2.pil_elctbl_chc_popl_id = chc2.pil_elctbl_chc_popl_id and chc2.elig_flag='Y' and nvl(chc.oipl_id,chc.pl_id) = nvl(chc2.oipl_id , chc2.pl_id) and chc2.elig_per_elctbl_chc_id = enr.elig_per_elctbl_chc_id and enr.acty_typ_cd = 'CWBWS' and enr.val > 0 ) )) Eligible_Emps_w_val, max(decode (hrchy_all.lvl_num ,'0', (select sum(elig_sal_drt.val) from ben_enrt_rt elig_sal_drt, ben_cwb_hrchy hrchy, ben_pil_elctbl_chc_popl popl1, ben_elig_per_elctbl_chc chc_dsal Where popl.pil_elctbl_chc_popl_id = hrchy.mgr_pil_elctbl_chc_popl_id and popl1.pil_elctbl_chc_popl_id = hrchy.emp_pil_elctbl_chc_popl_id and chc_dsal.pil_elctbl_chc_popl_id = popl1.pil_elctbl_chc_popl_id and chc_dsal.elig_flag='Y' and nvl(chc.oipl_id,chc.pl_id) = nvl(chc_dsal.oipl_id , chc_dsal.pl_id) and elig_sal_drt.elig_per_elctbl_chc_id(+) = chc_dsal.elig_per_elctbl_chc_id and elig_sal_drt.acty_typ_cd(+)='CWBES' and hrchy.lvl_num = 1 ), (select sum(elig_sal_drt.val) from ben_enrt_rt elig_sal_drt, ben_cwb_hrchy hrchy, ben_pil_elctbl_chc_popl popl2, ben_elig_per_elctbl_chc chc_asal Where popl.pil_elctbl_chc_popl_id = hrchy.mgr_pil_elctbl_chc_popl_id and popl2.pil_elctbl_chc_popl_id = hrchy.emp_pil_elctbl_chc_popl_id and chc_asal.pil_elctbl_chc_popl_id = popl2.pil_elctbl_chc_popl_id and chc_asal.elig_flag='Y' and nvl(chc.oipl_id,chc.pl_id) = nvl(chc_asal.oipl_id , chc_asal.pl_id) and elig_sal_drt.elig_per_elctbl_chc_id(+) = chc_asal.elig_per_elctbl_chc_id and elig_sal_drt.acty_typ_cd(+)='CWBES' and hrchy.lvl_num > 0 ) )) Eligible_Salaries , max(decode(perd.prsvr_bdgt_cd , 'A', (decode (hrchy_all.lvl_num ,'0',wb_bdgt.iss_val, nvl(db_bdgt.iss_val , (select sum(nvl(wb_bdgt.iss_val,0)) from ben_cwb_hrchy hrchy, ben_pil_elctbl_chc_popl popl1, ben_enrt_rt wb_bdgt, ben_elig_per_elctbl_chc chc_wb where popl.pil_elctbl_chc_popl_id = hrchy.mgr_pil_elctbl_chc_popl_id and popl1.pil_elctbl_chc_popl_id = hrchy.emp_pil_elctbl_chc_popl_id and chc_wb.pil_elctbl_chc_popl_id = popl1.pil_elctbl_chc_popl_id and chc_wb.elig_flag='Y' and hrchy.lvl_num >= 0 and nvl(chc.oipl_id,chc.pl_id) = nvl(chc_wb.oipl_id , chc_wb.pl_id) and wb_bdgt.elig_per_elctbl_chc_id(+) = chc_wb.elig_per_elctbl_chc_id and wb_bdgt.acty_typ_cd(+) ='CWBWB') ) ) ) , null ) ) budget_Amount , max(decode(perd.prsvr_bdgt_cd , 'P', (decode (hrchy_all.lvl_num ,'0', wb_bdgt.iss_val , nvl(db_bdgt.iss_val , ( (select nvl(sum(wb_rate.iss_val * lower_rate.val /100),0) amt from ben_enrt_rt lower_rate , ben_elig_per_elctbl_chc lower_chc , ben_cwb_hrchy lower_hrchy , ben_cwb_hrchy hrchy, ben_elig_per_elctbl_chc chc_asal , ben_pil_elctbl_chc_popl popl1, ben_enrt_rt wb_rate where popl.pil_elctbl_chc_popl_id = hrchy.mgr_pil_elctbl_chc_popl_id and popl1.pil_elctbl_chc_popl_id = hrchy.emp_pil_elctbl_chc_popl_id and chc_asal.pil_elctbl_chc_popl_id = popl1.pil_elctbl_chc_popl_id and nvl(chc.oipl_id,chc.pl_id) = nvl(chc_asal.oipl_id , chc_asal.pl_id) and wb_rate.elig_per_elctbl_chc_id(+)=chc_asal.elig_per_elctbl_chc_id and wb_rate.acty_typ_cd(+)='CWBWB' and hrchy.lvl_num > 0 and lower_hrchy.mgr_pil_elctbl_chc_popl_id = hrchy.emp_pil_elctbl_chc_popl_id and lower_chc.pil_elctbl_chc_popl_id = lower_hrchy.emp_pil_elctbl_chc_popl_id and nvl(chc.oipl_id,chc.pl_id) = nvl(lower_chc.oipl_id , lower_chc.pl_id) and lower_rate.elig_per_elctbl_chc_id(+)=lower_chc.elig_per_elctbl_chc_id and lower_rate.acty_typ_cd(+) = 'CWBES' and lower_chc.elig_flag='Y' and lower_hrchy.lvl_num = 1) + (select nvl(sum(mgr_rate.val * elig_sal_drt.val /100 ),0) amt from ben_enrt_rt elig_sal_drt, ben_cwb_hrchy hrchy, ben_elig_per_elctbl_chc mgr_chc, ben_elig_per_elctbl_chc chc_drt, ben_enrt_rt mgr_rate where popl.pil_elctbl_chc_popl_id = hrchy.mgr_pil_elctbl_chc_popl_id and mgr_chc.pil_elctbl_chc_popl_id = hrchy.mgr_pil_elctbl_chc_popl_id and nvl(chc.oipl_id,chc.pl_id) = nvl(mgr_chc.oipl_id , mgr_chc.pl_id) and mgr_rate.elig_per_elctbl_chc_id = mgr_chc.pil_elctbl_chc_popl_id and mgr_rate.acty_typ_cd(+)='CWBWB' and chc_drt.pil_elctbl_chc_popl_id = hrchy.emp_pil_elctbl_chc_popl_id and chc_drt.elig_flag='Y' and nvl(chc.oipl_id,chc.pl_id) = nvl(chc_drt.oipl_id , chc_drt.pl_id) and elig_sal_drt.elig_per_elctbl_chc_id(+)=chc_drt.elig_per_elctbl_chc_id and elig_sal_drt.acty_typ_cd(+)='CWBES' and hrchy.lvl_num = 1) ) / (select sum(elig_sal_drt.val) from ben_enrt_rt elig_sal_drt, ben_cwb_hrchy hrchy, ben_pil_elctbl_chc_popl popl1, ben_elig_per_elctbl_chc chc_asal where popl.pil_elctbl_chc_popl_id = hrchy.mgr_pil_elctbl_chc_popl_id and popl1.pil_elctbl_chc_popl_id = hrchy.emp_pil_elctbl_chc_popl_id and chc_asal.pil_elctbl_chc_popl_id = popl1.pil_elctbl_chc_popl_id and chc_asal.elig_flag='Y' and nvl(chc.oipl_id,chc.pl_id) = nvl(chc_asal.oipl_id , chc_asal.pl_id) and elig_sal_drt.elig_per_elctbl_chc_id(+)=chc_asal.elig_per_elctbl_chc_id and elig_sal_drt.acty_typ_cd(+)='CWBES' and hrchy.lvl_num > 0 ) * 100 ) ) ) , null ) ) budget_Percent , max(decode (hrchy_all.lvl_num ,'0', (select sum(used.val) from ben_cwb_hrchy hrchy, ben_enrt_rt used, ben_elig_per_elctbl_chc chc_dused, ben_pil_elctbl_chc_popl popl1 where popl.pil_elctbl_chc_popl_id = hrchy.mgr_pil_elctbl_chc_popl_id and popl1.pil_elctbl_chc_popl_id = hrchy.emp_pil_elctbl_chc_popl_id and chc_dused.pil_elctbl_chc_popl_id = popl1.pil_elctbl_chc_popl_id and chc_dused.elig_flag='Y' and nvl(chc.oipl_id,chc.pl_id) = nvl(chc_dused.oipl_id , chc_dused.pl_id) and hrchy.lvl_num = 1 and used.elig_per_elctbl_chc_id(+) = chc_dused.elig_per_elctbl_chc_id and used.acty_typ_cd(+) ='CWBWS' ), (select sum(used.val) from ben_cwb_hrchy hrchy, ben_enrt_rt used, ben_elig_per_elctbl_chc chc_aused, ben_pil_elctbl_chc_popl popl1 where popl.pil_elctbl_chc_popl_id = hrchy.mgr_pil_elctbl_chc_popl_id and popl1.pil_elctbl_chc_popl_id = hrchy.emp_pil_elctbl_chc_popl_id and chc_aused.pil_elctbl_chc_popl_id = popl1.pil_elctbl_chc_popl_id and chc_aused.elig_flag='Y' and nvl(chc.oipl_id,chc.pl_id) = nvl(chc_aused.oipl_id , chc_aused.pl_id) and hrchy.lvl_num > 0 and used.elig_per_elctbl_chc_id(+) = chc_aused.elig_per_elctbl_chc_id and used.acty_typ_cd(+) ='CWBWS' ) )) total_allocated, popl.ws_stat_cd ws_status, popl.bdgt_STAT_CD budget_status, chc.elig_per_elctbl_chc_id choice_id, popl.pil_elctbl_chc_popl_id popl_id , popl.bdgt_acc_cd bdgt_access, popl.object_version_number popl_obj_ver_num, popl.ws_acc_cd ws_access, mgrpil.person_id manager_id, perd.enrt_perd_id from ben_enrt_rt db_bdgt, ben_enrt_rt wb_bdgt, ben_cwb_hrchy hrchy_all, ben_cwb_hrchy hrchy_drt , per_all_people_f ppf, ben_elig_per_elctbl_chc chc, ben_pil_elctbl_chc_popl popl, ben_per_in_ler pil, ben_elig_per_elctbl_chc drt_chc_wb, ben_elig_per_elctbl_chc drt_chc_db, ben_per_in_ler mgrpil, ben_pil_elctbl_chc_popl mgrpopl, ben_enrt_perd perd, ben_oipl_f oipl, ben_opt_f opt, ben_pl_f pl, per_business_groups_perf bg, ben_acty_base_rt_f abr, hr_lookups lookup WHERE mgrpopl.per_in_ler_id = mgrpil.per_in_ler_id and mgrpopl.enrt_perd_id = perd.enrt_perd_id and mgrpopl.pil_elctbl_chc_popl_id = hrchy_all.mgr_pil_elctbl_chc_popl_id and hrchy_all.lvl_num >=0 and hrchy_drt.mgr_pil_elctbl_chc_popl_id = hrchy_all.emp_pil_elctbl_chc_popl_id and popl.pil_elctbl_chc_popl_id = chc.pil_elctbl_chc_popl_id and chc.pl_id = pl.pl_id and chc.oipl_id = oipl.oipl_id(+) and oipl.opt_id = opt.opt_id(+) and nvl(abr.oipl_id,abr.pl_id) = nvl(chc.oipl_id , pl.pl_id) and abr.acty_typ_cd = 'CWBWS' and lookup.lookup_code (+) = abr.nnmntry_uom and lookup.lookup_type (+) = 'BEN_NNMNTRY_UOM' and pl.business_group_id = bg.business_group_id and popl.pil_elctbl_chc_popl_id = hrchy_all.emp_pil_elctbl_chc_popl_id and hrchy_drt.lvl_num = 0 and drt_chc_db.pil_elctbl_chc_popl_id = hrchy_drt.mgr_pil_elctbl_chc_popl_id and db_bdgt.elig_per_elctbl_chc_id(+)= drt_chc_db.elig_per_elctbl_chc_id and db_bdgt.acty_typ_cd(+) = 'CWBDB' and chc.elig_per_elctbl_chc_id = drt_chc_db.elig_per_elctbl_chc_id and drt_chc_wb.pil_elctbl_chc_popl_id = hrchy_drt.emp_pil_elctbl_chc_popl_id and wb_bdgt.elig_per_elctbl_chc_id(+)= drt_chc_wb.elig_per_elctbl_chc_id and wb_bdgt.acty_typ_cd(+) = 'CWBWB' and chc.elig_per_elctbl_chc_id = drt_chc_wb.elig_per_elctbl_chc_id and pil.per_in_ler_id=popl.per_in_ler_id and pil.per_in_ler_stat_cd in ('STRTD','PROCD') and perd.asnd_lf_evt_dt between ppf.effective_start_Date and ppf.effective_end_date and ppf.person_id=pil.person_id group by hrchy_all.lvl_num, ppf.full_name, ppf.person_id , ppf.first_name || ' ' || ppf.last_name || ' ' || ppf.suffix , ppf.full_name, perd.asnd_lf_evt_dt , pl.name || '  ' || opt.name, bg.legislation_code, lookup.meaning , pl.nip_pl_uom , perd.prsvr_bdgt_cd , popl.ws_stat_cd , popl.bdgt_STAT_CD , chc.elig_per_elctbl_chc_id , popl.pil_elctbl_chc_popl_id , popl.bdgt_acc_cd , popl.object_version_number, popl.ws_acc_cd , chc.elig_per_elctbl_chc_id, mgrpil.person_id, perd.enrt_perd_id having hrchy_all.lvl_num between 0 and 1 order by perd.asnd_lf_evt_dt, ppf.full_name, pl.name || '  ' || opt.name )

View Text - HTML Formatted

SELECT ASND_LF_EVT_DT
, MANAGER_NAME
, MANAGER_FULL_NAME
, PLAN_NAME
, COUNTRY
, ELIGIBLE_EMPLOYEES
, ELIGIBLE_EMPS_W_VAL
, ROUND((ELIGIBLE_EMPS_W_VAL/DECODE(ELIGIBLE_EMPLOYEES
, 0
, 1
, ELIGIBLE_EMPLOYEES)*100)
, 1) PCT_W_VAL
, TOTAL_ALLOCATED
, NVL(RATE_UNITS
, PL_CURRENCY) UNITS
, ROUND((TOTAL_ALLOCATED/DECODE(ELIGIBLE_EMPS_W_VAL
, 0
, 1
, ELIGIBLE_EMPS_W_VAL))) AVERAGE_ALLOCATED
, DECODE(PRESERVE_CD
, 'A'
, BUDGET_AMOUNT
, (BUDGET_PERCENT*ELIGIBLE_SALARIES/100)) BUDGET
, DECODE(PRESERVE_CD
, 'A'
, BUDGET_AMOUNT
, (BUDGET_PERCENT*ELIGIBLE_SALARIES/100))-NVL(TOTAL_ALLOCATED
, 0) UNUSED_BUDGET
, MANAGER_ID
, ENRT_PERD_ID
FROM ( SELECT PPF.PERSON_ID PERSON_ID
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME || ' ' || PPF.SUFFIX MANAGER_NAME
, PPF.FULL_NAME MANAGER_FULL_NAME
, PERD.ASND_LF_EVT_DT ASND_LF_EVT_DT
, PL.NAME || ' ' || OPT.NAME PLAN_NAME
, BG.LEGISLATION_CODE COUNTRY
, LOOKUP.MEANING RATE_UNITS
, PL.NIP_PL_UOM PL_CURRENCY
, PERD.PRSVR_BDGT_CD PRESERVE_CD
, MAX(DECODE (HRCHY_ALL.LVL_NUM
, '0'
, (SELECT COUNT(CHC1.ELIG_PER_ELCTBL_CHC_ID)
FROM BEN_PIL_ELCTBL_CHC_POPL POPL1
, BEN_ELIG_PER_ELCTBL_CHC CHC1
, BEN_CWB_HRCHY HRCHY
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL1.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND HRCHY.LVL_NUM = 1
AND POPL1.PIL_ELCTBL_CHC_POPL_ID = CHC1.PIL_ELCTBL_CHC_POPL_ID
AND CHC1.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC1.OIPL_ID
, CHC1.PL_ID) )
, (SELECT COUNT(CHC2.ELIG_PER_ELCTBL_CHC_ID)
FROM BEN_PIL_ELCTBL_CHC_POPL POPL2
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_CWB_HRCHY HRCHY
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL2.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND HRCHY.LVL_NUM > 0
AND POPL2.PIL_ELCTBL_CHC_POPL_ID = CHC2.PIL_ELCTBL_CHC_POPL_ID
AND CHC2.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC2.OIPL_ID
, CHC2.PL_ID) )) ) ELIGIBLE_EMPLOYEES
, MAX(DECODE (HRCHY_ALL.LVL_NUM
, '0'
, (SELECT COUNT(CHC1.ELIG_PER_ELCTBL_CHC_ID)
FROM BEN_PIL_ELCTBL_CHC_POPL POPL1
, BEN_ELIG_PER_ELCTBL_CHC CHC1
, BEN_CWB_HRCHY HRCHY
, BEN_ENRT_RT ENR
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL1.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND HRCHY.LVL_NUM = 1
AND POPL1.PIL_ELCTBL_CHC_POPL_ID = CHC1.PIL_ELCTBL_CHC_POPL_ID
AND CHC1.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC1.OIPL_ID
, CHC1.PL_ID)
AND CHC1.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID
AND ENR.ACTY_TYP_CD = 'CWBWS'
AND ENR.VAL > 0 )
, (SELECT COUNT(CHC2.ELIG_PER_ELCTBL_CHC_ID)
FROM BEN_PIL_ELCTBL_CHC_POPL POPL2
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_CWB_HRCHY HRCHY
, BEN_ENRT_RT ENR
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL2.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND HRCHY.LVL_NUM > 0
AND POPL2.PIL_ELCTBL_CHC_POPL_ID = CHC2.PIL_ELCTBL_CHC_POPL_ID
AND CHC2.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC2.OIPL_ID
, CHC2.PL_ID)
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID
AND ENR.ACTY_TYP_CD = 'CWBWS'
AND ENR.VAL > 0 ) )) ELIGIBLE_EMPS_W_VAL
, MAX(DECODE (HRCHY_ALL.LVL_NUM
, '0'
, (SELECT SUM(ELIG_SAL_DRT.VAL)
FROM BEN_ENRT_RT ELIG_SAL_DRT
, BEN_CWB_HRCHY HRCHY
, BEN_PIL_ELCTBL_CHC_POPL POPL1
, BEN_ELIG_PER_ELCTBL_CHC CHC_DSAL
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL1.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND CHC_DSAL.PIL_ELCTBL_CHC_POPL_ID = POPL1.PIL_ELCTBL_CHC_POPL_ID
AND CHC_DSAL.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC_DSAL.OIPL_ID
, CHC_DSAL.PL_ID)
AND ELIG_SAL_DRT.ELIG_PER_ELCTBL_CHC_ID(+) = CHC_DSAL.ELIG_PER_ELCTBL_CHC_ID
AND ELIG_SAL_DRT.ACTY_TYP_CD(+)='CWBES'
AND HRCHY.LVL_NUM = 1 )
, (SELECT SUM(ELIG_SAL_DRT.VAL)
FROM BEN_ENRT_RT ELIG_SAL_DRT
, BEN_CWB_HRCHY HRCHY
, BEN_PIL_ELCTBL_CHC_POPL POPL2
, BEN_ELIG_PER_ELCTBL_CHC CHC_ASAL
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL2.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND CHC_ASAL.PIL_ELCTBL_CHC_POPL_ID = POPL2.PIL_ELCTBL_CHC_POPL_ID
AND CHC_ASAL.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC_ASAL.OIPL_ID
, CHC_ASAL.PL_ID)
AND ELIG_SAL_DRT.ELIG_PER_ELCTBL_CHC_ID(+) = CHC_ASAL.ELIG_PER_ELCTBL_CHC_ID
AND ELIG_SAL_DRT.ACTY_TYP_CD(+)='CWBES'
AND HRCHY.LVL_NUM > 0 ) )) ELIGIBLE_SALARIES
, MAX(DECODE(PERD.PRSVR_BDGT_CD
, 'A'
, (DECODE (HRCHY_ALL.LVL_NUM
, '0'
, WB_BDGT.ISS_VAL
, NVL(DB_BDGT.ISS_VAL
, (SELECT SUM(NVL(WB_BDGT.ISS_VAL
, 0))
FROM BEN_CWB_HRCHY HRCHY
, BEN_PIL_ELCTBL_CHC_POPL POPL1
, BEN_ENRT_RT WB_BDGT
, BEN_ELIG_PER_ELCTBL_CHC CHC_WB
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL1.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND CHC_WB.PIL_ELCTBL_CHC_POPL_ID = POPL1.PIL_ELCTBL_CHC_POPL_ID
AND CHC_WB.ELIG_FLAG='Y'
AND HRCHY.LVL_NUM >= 0
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC_WB.OIPL_ID
, CHC_WB.PL_ID)
AND WB_BDGT.ELIG_PER_ELCTBL_CHC_ID(+) = CHC_WB.ELIG_PER_ELCTBL_CHC_ID
AND WB_BDGT.ACTY_TYP_CD(+) ='CWBWB') ) ) )
, NULL ) ) BUDGET_AMOUNT
, MAX(DECODE(PERD.PRSVR_BDGT_CD
, 'P'
, (DECODE (HRCHY_ALL.LVL_NUM
, '0'
, WB_BDGT.ISS_VAL
, NVL(DB_BDGT.ISS_VAL
, ( (SELECT NVL(SUM(WB_RATE.ISS_VAL * LOWER_RATE.VAL /100)
, 0) AMT
FROM BEN_ENRT_RT LOWER_RATE
, BEN_ELIG_PER_ELCTBL_CHC LOWER_CHC
, BEN_CWB_HRCHY LOWER_HRCHY
, BEN_CWB_HRCHY HRCHY
, BEN_ELIG_PER_ELCTBL_CHC CHC_ASAL
, BEN_PIL_ELCTBL_CHC_POPL POPL1
, BEN_ENRT_RT WB_RATE
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL1.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND CHC_ASAL.PIL_ELCTBL_CHC_POPL_ID = POPL1.PIL_ELCTBL_CHC_POPL_ID
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC_ASAL.OIPL_ID
, CHC_ASAL.PL_ID)
AND WB_RATE.ELIG_PER_ELCTBL_CHC_ID(+)=CHC_ASAL.ELIG_PER_ELCTBL_CHC_ID
AND WB_RATE.ACTY_TYP_CD(+)='CWBWB'
AND HRCHY.LVL_NUM > 0
AND LOWER_HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND LOWER_CHC.PIL_ELCTBL_CHC_POPL_ID = LOWER_HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(LOWER_CHC.OIPL_ID
, LOWER_CHC.PL_ID)
AND LOWER_RATE.ELIG_PER_ELCTBL_CHC_ID(+)=LOWER_CHC.ELIG_PER_ELCTBL_CHC_ID
AND LOWER_RATE.ACTY_TYP_CD(+) = 'CWBES'
AND LOWER_CHC.ELIG_FLAG='Y'
AND LOWER_HRCHY.LVL_NUM = 1) + (SELECT NVL(SUM(MGR_RATE.VAL * ELIG_SAL_DRT.VAL /100 )
, 0) AMT
FROM BEN_ENRT_RT ELIG_SAL_DRT
, BEN_CWB_HRCHY HRCHY
, BEN_ELIG_PER_ELCTBL_CHC MGR_CHC
, BEN_ELIG_PER_ELCTBL_CHC CHC_DRT
, BEN_ENRT_RT MGR_RATE
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND MGR_CHC.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(MGR_CHC.OIPL_ID
, MGR_CHC.PL_ID)
AND MGR_RATE.ELIG_PER_ELCTBL_CHC_ID = MGR_CHC.PIL_ELCTBL_CHC_POPL_ID
AND MGR_RATE.ACTY_TYP_CD(+)='CWBWB'
AND CHC_DRT.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND CHC_DRT.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC_DRT.OIPL_ID
, CHC_DRT.PL_ID)
AND ELIG_SAL_DRT.ELIG_PER_ELCTBL_CHC_ID(+)=CHC_DRT.ELIG_PER_ELCTBL_CHC_ID
AND ELIG_SAL_DRT.ACTY_TYP_CD(+)='CWBES'
AND HRCHY.LVL_NUM = 1) ) / (SELECT SUM(ELIG_SAL_DRT.VAL)
FROM BEN_ENRT_RT ELIG_SAL_DRT
, BEN_CWB_HRCHY HRCHY
, BEN_PIL_ELCTBL_CHC_POPL POPL1
, BEN_ELIG_PER_ELCTBL_CHC CHC_ASAL
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL1.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND CHC_ASAL.PIL_ELCTBL_CHC_POPL_ID = POPL1.PIL_ELCTBL_CHC_POPL_ID
AND CHC_ASAL.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC_ASAL.OIPL_ID
, CHC_ASAL.PL_ID)
AND ELIG_SAL_DRT.ELIG_PER_ELCTBL_CHC_ID(+)=CHC_ASAL.ELIG_PER_ELCTBL_CHC_ID
AND ELIG_SAL_DRT.ACTY_TYP_CD(+)='CWBES'
AND HRCHY.LVL_NUM > 0 ) * 100 ) ) )
, NULL ) ) BUDGET_PERCENT
, MAX(DECODE (HRCHY_ALL.LVL_NUM
, '0'
, (SELECT SUM(USED.VAL)
FROM BEN_CWB_HRCHY HRCHY
, BEN_ENRT_RT USED
, BEN_ELIG_PER_ELCTBL_CHC CHC_DUSED
, BEN_PIL_ELCTBL_CHC_POPL POPL1
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL1.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND CHC_DUSED.PIL_ELCTBL_CHC_POPL_ID = POPL1.PIL_ELCTBL_CHC_POPL_ID
AND CHC_DUSED.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC_DUSED.OIPL_ID
, CHC_DUSED.PL_ID)
AND HRCHY.LVL_NUM = 1
AND USED.ELIG_PER_ELCTBL_CHC_ID(+) = CHC_DUSED.ELIG_PER_ELCTBL_CHC_ID
AND USED.ACTY_TYP_CD(+) ='CWBWS' )
, (SELECT SUM(USED.VAL)
FROM BEN_CWB_HRCHY HRCHY
, BEN_ENRT_RT USED
, BEN_ELIG_PER_ELCTBL_CHC CHC_AUSED
, BEN_PIL_ELCTBL_CHC_POPL POPL1
WHERE POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY.MGR_PIL_ELCTBL_CHC_POPL_ID
AND POPL1.PIL_ELCTBL_CHC_POPL_ID = HRCHY.EMP_PIL_ELCTBL_CHC_POPL_ID
AND CHC_AUSED.PIL_ELCTBL_CHC_POPL_ID = POPL1.PIL_ELCTBL_CHC_POPL_ID
AND CHC_AUSED.ELIG_FLAG='Y'
AND NVL(CHC.OIPL_ID
, CHC.PL_ID) = NVL(CHC_AUSED.OIPL_ID
, CHC_AUSED.PL_ID)
AND HRCHY.LVL_NUM > 0
AND USED.ELIG_PER_ELCTBL_CHC_ID(+) = CHC_AUSED.ELIG_PER_ELCTBL_CHC_ID
AND USED.ACTY_TYP_CD(+) ='CWBWS' ) )) TOTAL_ALLOCATED
, POPL.WS_STAT_CD WS_STATUS
, POPL.BDGT_STAT_CD BUDGET_STATUS
, CHC.ELIG_PER_ELCTBL_CHC_ID CHOICE_ID
, POPL.PIL_ELCTBL_CHC_POPL_ID POPL_ID
, POPL.BDGT_ACC_CD BDGT_ACCESS
, POPL.OBJECT_VERSION_NUMBER POPL_OBJ_VER_NUM
, POPL.WS_ACC_CD WS_ACCESS
, MGRPIL.PERSON_ID MANAGER_ID
, PERD.ENRT_PERD_ID
FROM BEN_ENRT_RT DB_BDGT
, BEN_ENRT_RT WB_BDGT
, BEN_CWB_HRCHY HRCHY_ALL
, BEN_CWB_HRCHY HRCHY_DRT
, PER_ALL_PEOPLE_F PPF
, BEN_ELIG_PER_ELCTBL_CHC CHC
, BEN_PIL_ELCTBL_CHC_POPL POPL
, BEN_PER_IN_LER PIL
, BEN_ELIG_PER_ELCTBL_CHC DRT_CHC_WB
, BEN_ELIG_PER_ELCTBL_CHC DRT_CHC_DB
, BEN_PER_IN_LER MGRPIL
, BEN_PIL_ELCTBL_CHC_POPL MGRPOPL
, BEN_ENRT_PERD PERD
, BEN_OIPL_F OIPL
, BEN_OPT_F OPT
, BEN_PL_F PL
, PER_BUSINESS_GROUPS_PERF BG
, BEN_ACTY_BASE_RT_F ABR
, HR_LOOKUPS LOOKUP
WHERE MGRPOPL.PER_IN_LER_ID = MGRPIL.PER_IN_LER_ID
AND MGRPOPL.ENRT_PERD_ID = PERD.ENRT_PERD_ID
AND MGRPOPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY_ALL.MGR_PIL_ELCTBL_CHC_POPL_ID
AND HRCHY_ALL.LVL_NUM >=0
AND HRCHY_DRT.MGR_PIL_ELCTBL_CHC_POPL_ID = HRCHY_ALL.EMP_PIL_ELCTBL_CHC_POPL_ID
AND POPL.PIL_ELCTBL_CHC_POPL_ID = CHC.PIL_ELCTBL_CHC_POPL_ID
AND CHC.PL_ID = PL.PL_ID
AND CHC.OIPL_ID = OIPL.OIPL_ID(+)
AND OIPL.OPT_ID = OPT.OPT_ID(+)
AND NVL(ABR.OIPL_ID
, ABR.PL_ID) = NVL(CHC.OIPL_ID
, PL.PL_ID)
AND ABR.ACTY_TYP_CD = 'CWBWS'
AND LOOKUP.LOOKUP_CODE (+) = ABR.NNMNTRY_UOM
AND LOOKUP.LOOKUP_TYPE (+) = 'BEN_NNMNTRY_UOM'
AND PL.BUSINESS_GROUP_ID = BG.BUSINESS_GROUP_ID
AND POPL.PIL_ELCTBL_CHC_POPL_ID = HRCHY_ALL.EMP_PIL_ELCTBL_CHC_POPL_ID
AND HRCHY_DRT.LVL_NUM = 0
AND DRT_CHC_DB.PIL_ELCTBL_CHC_POPL_ID = HRCHY_DRT.MGR_PIL_ELCTBL_CHC_POPL_ID
AND DB_BDGT.ELIG_PER_ELCTBL_CHC_ID(+)= DRT_CHC_DB.ELIG_PER_ELCTBL_CHC_ID
AND DB_BDGT.ACTY_TYP_CD(+) = 'CWBDB'
AND CHC.ELIG_PER_ELCTBL_CHC_ID = DRT_CHC_DB.ELIG_PER_ELCTBL_CHC_ID
AND DRT_CHC_WB.PIL_ELCTBL_CHC_POPL_ID = HRCHY_DRT.EMP_PIL_ELCTBL_CHC_POPL_ID
AND WB_BDGT.ELIG_PER_ELCTBL_CHC_ID(+)= DRT_CHC_WB.ELIG_PER_ELCTBL_CHC_ID
AND WB_BDGT.ACTY_TYP_CD(+) = 'CWBWB'
AND CHC.ELIG_PER_ELCTBL_CHC_ID = DRT_CHC_WB.ELIG_PER_ELCTBL_CHC_ID
AND PIL.PER_IN_LER_ID=POPL.PER_IN_LER_ID
AND PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PERD.ASND_LF_EVT_DT BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PPF.PERSON_ID=PIL.PERSON_ID GROUP BY HRCHY_ALL.LVL_NUM
, PPF.FULL_NAME
, PPF.PERSON_ID
, PPF.FIRST_NAME || ' ' || PPF.LAST_NAME || ' ' || PPF.SUFFIX
, PPF.FULL_NAME
, PERD.ASND_LF_EVT_DT
, PL.NAME || ' ' || OPT.NAME
, BG.LEGISLATION_CODE
, LOOKUP.MEANING
, PL.NIP_PL_UOM
, PERD.PRSVR_BDGT_CD
, POPL.WS_STAT_CD
, POPL.BDGT_STAT_CD
, CHC.ELIG_PER_ELCTBL_CHC_ID
, POPL.PIL_ELCTBL_CHC_POPL_ID
, POPL.BDGT_ACC_CD
, POPL.OBJECT_VERSION_NUMBER
, POPL.WS_ACC_CD
, CHC.ELIG_PER_ELCTBL_CHC_ID
, MGRPIL.PERSON_ID
, PERD.ENRT_PERD_ID HAVING HRCHY_ALL.LVL_NUM BETWEEN 0
AND 1 ORDER BY PERD.ASND_LF_EVT_DT
, PPF.FULL_NAME
, PL.NAME || ' ' || OPT.NAME )