[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select rt.group_pl_id group_pl_id
,rt.group_oipl_id group_oipl_id
,1 elig_count
,decode(rt.ws_val,null,0,0,0,1) emp_recv_count
,rt.elig_sal_val/decode(pl.elig_sal_nnmntry_uom,null
,xchg.xchg_rate,1) elig_sal_val
,rt.ws_val/decode(pl.ws_nnmntry_uom,null
,xchg.xchg_rate,1) ws_val
,rt.stat_sal_val/decode(pl.stat_sal_nnmntry_uom,null
,xchg.xchg_rate,1) stat_sal_val
,rt.oth_comp_val/decode(pl.oth_comp_nnmntry_uom,null
,xchg.xchg_rate,1) oth_comp_val
,rt.tot_comp_val/decode(pl.tot_comp_nnmntry_uom,null
,xchg.xchg_rate,1) tot_comp_val
,rt.rec_val/decode(pl.rec_nnmntry_uom,null
,xchg.xchg_rate,1) rec_val
,rt.rec_mn_val/decode(pl.rec_nnmntry_uom,null
,xchg.xchg_rate,1) rec_mn_val
,rt.rec_mx_val/decode(pl.rec_nnmntry_uom,null
,xchg.xchg_rate,1) rec_mx_val
,rt.misc1_val/decode(pl.misc1_nnmntry_uom,null
,xchg.xchg_rate,1) misc1_val
,rt.misc2_val/decode(pl.misc2_nnmntry_uom,null
,xchg.xchg_rate,1) misc2_val
,rt.misc3_val/decode(pl.misc3_nnmntry_uom,null
,xchg.xchg_rate,1) misc3_val
from ben_cwb_person_rates rt
,ben_cwb_pl_dsgn pl
,ben_cwb_xchg xchg
where rt.group_per_in_ler_id = v_per_in_ler_id
and rt.pl_id = pl.pl_id
and rt.oipl_id = pl.oipl_id
and rt.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and rt.elig_flag = 'Y'
and xchg.group_pl_id = rt.group_pl_id
and xchg.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
and xchg.currency = rt.currency
order by rt.group_pl_id, rt.group_oipl_id;
select group_pl_id group_pl_id
,group_oipl_id group_oipl_id
,sum(elig_count_all) elig_count
,sum(emp_recv_count_all) emp_recv_count
,sum(elig_sal_val_all) elig_sal_val_all
,sum(ws_val_all) ws_val
,sum(stat_sal_val_all) stat_sal_val
,sum(oth_comp_val_all) oth_comp_val
,sum(tot_comp_val_all) tot_comp_val
,sum(rec_val_all) rec_val
,sum(rec_mn_val_all) rec_mn_val
,sum(rec_mx_val_all) rec_mx_val
,sum(misc1_val_all) misc1_val
,sum(misc2_val_all) misc2_val
,sum(misc3_val_all) misc3_val
from ben_cwb_summary
where group_per_in_ler_id = v_per_in_ler_id
group by group_pl_id, group_oipl_id
order by group_pl_id, group_oipl_id;
select mgr_per_in_ler_id
,lvl_num
from ben_cwb_group_hrchy hrchy1
where emp_per_in_ler_id = v_mgr_per_in_ler_id1
and mgr_per_in_ler_id not in
(select mgr_per_in_ler_id
from ben_cwb_group_hrchy hrchy2
where emp_per_in_ler_id = v_mgr_per_in_ler_id2
and mgr_per_in_ler_id <> v_mgr_per_in_ler_id1
and lvl_num > 0)
order by lvl_num;
select mgr_per_in_ler_id
,lvl_num
from ben_cwb_group_hrchy
where emp_per_in_ler_id = v_per_in_ler_id
and lvl_num > 0
order by lvl_num;
select 'Y'
from ben_cwb_summary
where group_per_in_ler_id = -1
and group_pl_id = p_group_pl_id
and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and status = 'R'
and person_id = -1;
procedure insert_refresh_job_marker(p_group_pl_id in number
,p_lf_evt_ocrd_dt in date) is
pragma autonomous_transaction;
insert into ben_cwb_summary
(summary_id
,group_per_in_ler_id
,group_pl_id
,lf_evt_ocrd_dt
,status
,person_id)
values
(ben_cwb_summary_s.nextval
,-1
,p_group_pl_id
,p_lf_evt_ocrd_dt
,'R'
,-1);
end insert_refresh_job_marker;
procedure delete_refresh_job_marker(p_group_pl_id in number
,p_lf_evt_ocrd_dt in date) is
pragma autonomous_transaction;
delete ben_cwb_summary
where group_per_in_ler_id = -1
and group_pl_id = p_group_pl_id
and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and status = 'R'
and person_id = -1;
end delete_refresh_job_marker;
procedure update_or_insert (p_sum_rec in ben_cwb_summary%rowtype) is
-- select all the summary ids for this combination
cursor csr_summary_id is
select summary_id
from ben_cwb_summary
where group_per_in_ler_id = p_sum_rec.group_per_in_ler_id
and group_pl_id = p_sum_rec.group_pl_id
and group_oipl_id = p_sum_rec.group_oipl_id;
select pil.person_id,
pil.lf_evt_ocrd_dt
from ben_per_in_ler pil
where pil.per_in_ler_id = p_sum_rec.group_per_in_ler_id;
l_proc varchar2(72) := g_package||'update_or_insert';
select null into l_dummy
from ben_cwb_summary
where summary_id = l_summary_id
for update nowait;
update ben_cwb_summary summ
set elig_count_direct = nvl2(summ.elig_count_direct,
summ.elig_count_direct+
nvl(p_sum_rec.elig_count_direct,0),
p_sum_rec.elig_count_direct)
,elig_count_all = nvl2(summ.elig_count_all,
summ.elig_count_all+
nvl(p_sum_rec.elig_count_all,0),
p_sum_rec.elig_count_all)
,emp_recv_count_direct = nvl2(summ.emp_recv_count_direct,
summ.emp_recv_count_direct+
nvl(p_sum_rec.emp_recv_count_direct,0),
p_sum_rec.emp_recv_count_direct)
,emp_recv_count_all = nvl2(summ.emp_recv_count_all,
summ.emp_recv_count_all+
nvl(p_sum_rec.emp_recv_count_all,0),
p_sum_rec.emp_recv_count_all)
,elig_sal_val_direct = nvl2(summ.elig_sal_val_direct,
summ.elig_sal_val_direct+
nvl(p_sum_rec.elig_sal_val_direct,0),
p_sum_rec.elig_sal_val_direct)
,elig_sal_val_all = nvl2(summ.elig_sal_val_all,
summ.elig_sal_val_all +
nvl(p_sum_rec.elig_sal_val_all,0),
p_sum_rec.elig_sal_val_all)
,ws_val_direct = nvl2(summ.ws_val_direct,
summ.ws_val_direct +
nvl(p_sum_rec.ws_val_direct,0),
p_sum_rec.ws_val_direct)
,ws_val_all = nvl2(summ.ws_val_all,
summ.ws_val_all +
nvl(p_sum_rec.ws_val_all,0),
p_sum_rec.ws_val_all)
,ws_bdgt_val_direct = nvl2(summ.ws_bdgt_val_direct,
summ.ws_bdgt_val_direct +
nvl(p_sum_rec.ws_bdgt_val_direct,0),
p_sum_rec.ws_bdgt_val_direct)
,ws_bdgt_val_all = nvl2(summ.ws_bdgt_val_all,
summ.ws_bdgt_val_all +
nvl(p_sum_rec.ws_bdgt_val_all,0),
p_sum_rec.ws_bdgt_val_all)
,ws_bdgt_iss_val_direct = nvl2(summ.ws_bdgt_iss_val_direct,
summ.ws_bdgt_iss_val_direct +
nvl(p_sum_rec.ws_bdgt_iss_val_direct,0),
p_sum_rec.ws_bdgt_iss_val_direct)
,ws_bdgt_iss_val_all = nvl2(summ.ws_bdgt_iss_val_all,
summ.ws_bdgt_iss_val_all +
nvl(p_sum_rec.ws_bdgt_iss_val_all,0),
p_sum_rec.ws_bdgt_iss_val_all)
,bdgt_val_direct = nvl2(summ.bdgt_val_direct,
summ.bdgt_val_direct +
nvl(p_sum_rec.bdgt_val_direct,0),
p_sum_rec.bdgt_val_direct)
,bdgt_iss_val_direct = nvl2(summ.bdgt_iss_val_direct,
summ.bdgt_iss_val_direct +
nvl(p_sum_rec.bdgt_iss_val_direct,0),
p_sum_rec.bdgt_iss_val_direct)
,stat_sal_val_direct = nvl2(summ.stat_sal_val_direct,
summ.stat_sal_val_direct +
nvl(p_sum_rec.stat_sal_val_direct,0),
p_sum_rec.stat_sal_val_direct)
,stat_sal_val_all = nvl2(summ.stat_sal_val_all,
summ.stat_sal_val_all +
nvl(p_sum_rec.stat_sal_val_all,0),
p_sum_rec.stat_sal_val_all)
,oth_comp_val_direct = nvl2(summ.oth_comp_val_direct,
summ.oth_comp_val_direct +
nvl(p_sum_rec.oth_comp_val_direct,0),
p_sum_rec.oth_comp_val_direct)
,oth_comp_val_all = nvl2(summ.oth_comp_val_all,
summ.oth_comp_val_all +
nvl(p_sum_rec.oth_comp_val_all,0),
p_sum_rec.oth_comp_val_all)
,tot_comp_val_direct = nvl2(summ.tot_comp_val_direct,
summ.tot_comp_val_direct +
nvl(p_sum_rec.tot_comp_val_direct,0),
p_sum_rec.tot_comp_val_direct)
,tot_comp_val_all = nvl2(summ.tot_comp_val_all,
summ.tot_comp_val_all +
nvl(p_sum_rec.tot_comp_val_all,0),
p_sum_rec.tot_comp_val_all)
,rec_val_direct = nvl2(summ.rec_val_direct,
summ.rec_val_direct +
nvl(p_sum_rec.rec_val_direct,0),
p_sum_rec.rec_val_direct)
,rec_val_all = nvl2(summ.rec_val_all,
summ.rec_val_all +
nvl(p_sum_rec.rec_val_all,0),
p_sum_rec.rec_val_all)
,rec_mn_val_direct = nvl2(summ.rec_mn_val_direct,
summ.rec_mn_val_direct +
nvl(p_sum_rec.rec_mn_val_direct,0),
p_sum_rec.rec_mn_val_direct)
,rec_mn_val_all = nvl2(summ.rec_mn_val_all,
summ.rec_mn_val_all +
nvl(p_sum_rec.rec_mn_val_all,0),
p_sum_rec.rec_mn_val_all)
,rec_mx_val_direct = nvl2(summ.rec_mx_val_direct,
summ.rec_mx_val_direct +
nvl(p_sum_rec.rec_mx_val_direct,0),
p_sum_rec.rec_mx_val_direct)
,rec_mx_val_all = nvl2(summ.rec_mx_val_all,
summ.rec_mx_val_all +
nvl(p_sum_rec.rec_mx_val_all,0),
p_sum_rec.rec_mx_val_all)
,misc1_val_direct = nvl2(summ.misc1_val_direct,
summ.misc1_val_direct +
nvl(p_sum_rec.misc1_val_direct,0),
p_sum_rec.misc1_val_direct)
,misc1_val_all = nvl2(summ.misc1_val_all,
summ.misc1_val_all +
nvl(p_sum_rec.misc1_val_all,0),
p_sum_rec.misc1_val_all)
,misc2_val_direct = nvl2(summ.misc2_val_direct,
summ.misc2_val_direct +
nvl(p_sum_rec.misc2_val_direct,0),
p_sum_rec.misc2_val_direct)
,misc2_val_all = nvl2(summ.misc2_val_all,
summ.misc2_val_all +
nvl(p_sum_rec.misc2_val_all,0),
p_sum_rec.misc2_val_all)
,misc3_val_direct = nvl2(summ.misc3_val_direct,
summ.misc3_val_direct +
nvl(p_sum_rec.misc3_val_direct,0),
p_sum_rec.misc3_val_direct)
,misc3_val_all = nvl2(summ.misc3_val_all,
summ.misc3_val_all +
nvl(p_sum_rec.misc3_val_all,0),
p_sum_rec.misc3_val_all)
where summ.summary_id = l_summary_id;
insert into ben_cwb_summary (
summary_id
,group_per_in_ler_id
,group_pl_id
,group_oipl_id
,status
,elig_count_direct
,elig_count_all
,emp_recv_count_direct
,emp_recv_count_all
,elig_sal_val_direct
,elig_sal_val_all
,ws_val_direct
,ws_val_all
,ws_bdgt_val_direct
,ws_bdgt_val_all
,ws_bdgt_iss_val_direct
,ws_bdgt_iss_val_all
,bdgt_val_direct
,bdgt_iss_val_direct
,stat_sal_val_direct
,stat_sal_val_all
,oth_comp_val_direct
,oth_comp_val_all
,tot_comp_val_direct
,tot_comp_val_all
,rec_val_direct
,rec_val_all
,rec_mn_val_direct
,rec_mn_val_all
,rec_mx_val_direct
,rec_mx_val_all
,misc1_val_direct
,misc1_val_all
,misc2_val_direct
,misc2_val_all
,misc3_val_direct
,misc3_val_all
,person_id
,lf_evt_ocrd_dt)
values (
ben_cwb_summary_s.nextval
,p_sum_rec.group_per_in_ler_id
,p_sum_rec.group_pl_id
,p_sum_rec.group_oipl_id
,l_status
,p_sum_rec.elig_count_direct
,p_sum_rec.elig_count_all
,p_sum_rec.emp_recv_count_direct
,p_sum_rec.emp_recv_count_all
,p_sum_rec.elig_sal_val_direct
,p_sum_rec.elig_sal_val_all
,p_sum_rec.ws_val_direct
,p_sum_rec.ws_val_all
,p_sum_rec.ws_bdgt_val_direct
,p_sum_rec.ws_bdgt_val_all
,p_sum_rec.ws_bdgt_iss_val_direct
,p_sum_rec.ws_bdgt_iss_val_all
,p_sum_rec.bdgt_val_direct
,p_sum_rec.bdgt_iss_val_direct
,p_sum_rec.stat_sal_val_direct
,p_sum_rec.stat_sal_val_all
,p_sum_rec.oth_comp_val_direct
,p_sum_rec.oth_comp_val_all
,p_sum_rec.tot_comp_val_direct
,p_sum_rec.tot_comp_val_all
,p_sum_rec.rec_val_direct
,p_sum_rec.rec_val_all
,p_sum_rec.rec_mn_val_direct
,p_sum_rec.rec_mn_val_all
,p_sum_rec.rec_mx_val_direct
,p_sum_rec.rec_mx_val_all
,p_sum_rec.misc1_val_direct
,p_sum_rec.misc1_val_all
,p_sum_rec.misc2_val_direct
,p_sum_rec.misc2_val_all
,p_sum_rec.misc3_val_direct
,p_sum_rec.misc3_val_all
,l_person_id
,l_lf_evt_ocrd_dt
);
end; -- of procedure update_or_insert
select summ.*
from ben_cwb_summary summ
where summ.person_id = p_person_id
and summ.status = 'P'
order by summ.group_per_in_ler_id, summ.group_pl_id, summ.group_oipl_id;
update_or_insert (l_sum_rec);
delete from ben_cwb_summary
where summary_id = pending_rec.summary_id;
update_or_insert(l_sum_rec);
select distinct(person_id) person_id
from ben_cwb_summary
where status = 'P';
procedure update_person_info(p_group_pl_id in number
,p_lf_evt_ocrd_dt in date) is
-- cursor to fetch the person_id from ben_per_in_ler for
-- group_per_in_ler_ids with -1 as person_id in ben_cwb_person_info
cursor csr_person_ids is
select pi.group_per_in_ler_id, pil.person_id
from ben_cwb_person_info pi
,ben_per_in_ler pil
where pi.person_id = -1
and pi.group_pl_id = p_group_pl_id
and pi.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and pi.group_per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd in ('PROCD','STRTD');
l_proc varchar2(72) :=g_package||'update_person_info';
'update_person_info';
update ben_cwb_person_info
set person_id = l_person_id_tab(i)
where group_per_in_ler_id = l_group_per_in_ler_id_tab(i);
l_group_per_in_ler_id_tab.delete;
l_person_id_tab.delete;
end update_person_info;
select hrchy.mgr_per_in_ler_id
,grp.group_pl_id
,grp.group_oipl_id,
sum(grp.ws_bdgt_val * summ.elig_sal_val_direct/100)
ws_bdgt_val_direct
,sum(grp.ws_bdgt_iss_val * summ.elig_sal_val_direct / 100)
ws_bdgt_iss_val_direct
from ben_cwb_group_hrchy hrchy
,ben_cwb_person_groups grp
,ben_cwb_person_info info
,ben_cwb_summary summ
where info.group_pl_id = p_group_pl_id
and info.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and info.group_per_in_ler_id = hrchy.emp_per_in_ler_id
and hrchy.lvl_num = 1
and hrchy.emp_per_in_ler_id = grp.group_per_in_ler_id
and grp.group_per_in_ler_id = summ.group_per_in_ler_id
and grp.group_pl_id = summ.group_pl_id
and grp.group_oipl_id = summ.group_oipl_id
group by hrchy.mgr_per_in_ler_id, grp.group_pl_id, grp.group_oipl_id;
select hrchy.mgr_per_in_ler_id
,grp.group_pl_id
,grp.group_oipl_id
,sum(grp.ws_bdgt_val) ws_bdgt_val_direct
,sum(grp.ws_bdgt_iss_val) ws_bdgt_iss_val_direct
,sum(decode(nvl(grp.dist_bdgt_val,0),0,grp.ws_bdgt_val,
grp.dist_bdgt_val)) bdgt_val_direct
,sum(decode(nvl(grp.dist_bdgt_val,0),0, grp.ws_bdgt_iss_val,
grp.dist_bdgt_iss_val)) bdgt_iss_val_direct
from ben_cwb_group_hrchy hrchy
,ben_cwb_person_groups grp
,ben_cwb_person_info info
where info.group_pl_id = p_group_pl_id
and info.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and info.group_per_in_ler_id = hrchy.emp_per_in_ler_id
and hrchy.lvl_num = 1
and hrchy.emp_per_in_ler_id = grp.group_per_in_ler_id
group by hrchy.mgr_per_in_ler_id, grp.group_pl_id, grp.group_oipl_id;
select hrchy.mgr_per_in_ler_id
,summ.group_pl_id, summ.group_oipl_id
,sum(elig_count_direct) elig_count_all
,sum(emp_recv_count_direct) emp_recv_count_all
,sum(elig_sal_val_direct) elig_sal_val_all
,sum(ws_val_direct) ws_val_all
,sum(ws_bdgt_val_direct)ws_bdgt_val_all
,sum(ws_bdgt_iss_val_direct) ws_bdgt_iss_val_all
,sum(stat_sal_val_direct) stat_sal_val_all
,sum(oth_comp_val_direct) oth_comp_val_all
,sum(tot_comp_val_direct) tot_comp_val_all
,sum(rec_val_direct) rec_val_all
,sum(rec_mn_val_direct) rec_mn_val_all
,sum(rec_mx_val_direct) rec_mx_val_all
,sum(misc1_val_direct) misc1_val_all
,sum(misc2_val_direct) misc2_val_all
,sum(misc3_val_direct) misc3_val_all
from ben_cwb_group_hrchy hrchy
,ben_cwb_summary summ
,ben_cwb_person_info info
where info.group_pl_id = p_group_pl_id
and info.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and info.group_per_in_ler_id = hrchy.emp_per_in_ler_id
and hrchy.lvl_num >=0
and hrchy.emp_per_in_ler_id = summ.group_per_in_ler_id
group by hrchy.mgr_per_in_ler_id, summ.group_pl_id, summ.group_oipl_id;
select hrchy.mgr_per_in_ler_id
,grp.group_pl_id
,grp.group_oipl_id
,sum(decode(nvl(grp.dist_bdgt_val,0),0,grp.ws_bdgt_val,
grp.dist_bdgt_val)*
decode(nvl(grp.dist_bdgt_val,0),0,summ.elig_sal_val_direct,
summ.elig_sal_val_all) /100) bdgt_val_direct
,sum(decode(nvl(grp.dist_bdgt_val,0),0, grp.ws_bdgt_iss_val,
grp.dist_bdgt_iss_val)* decode(nvl(grp.dist_bdgt_val,0),0,
summ.elig_sal_val_direct,summ.elig_sal_val_all) /100)
bdgt_iss_val_direct
from ben_cwb_group_hrchy hrchy
,ben_cwb_person_groups grp
,ben_cwb_summary summ
,ben_cwb_person_info info
where info.group_pl_id = p_group_pl_id
and info.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and info.group_per_in_ler_id = hrchy.emp_per_in_ler_id
and hrchy.lvl_num = 1
and hrchy.emp_per_in_ler_id = grp.group_per_in_ler_id
and grp.group_per_in_ler_id = summ.group_per_in_ler_id
and grp.group_pl_id = summ.group_pl_id
and grp.group_oipl_id = summ.group_oipl_id
group by hrchy.mgr_per_in_ler_id, grp.group_pl_id, grp.group_oipl_id;
select prsrv_bdgt_cd, uses_bdgt_flag
into l_prsrv_bdgt_cd, l_uses_bdgt_flag
from ben_cwb_pl_dsgn
where pl_id = p_group_pl_id
and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and oipl_id = -1;
update ben_cwb_summary
set ws_bdgt_val_direct = l_ws_bdgt_val_direct_tab(i)
,ws_bdgt_iss_val_direct = l_ws_bdgt_iss_val_direct_tab(i)
where group_per_in_ler_id = l_group_per_in_ler_id_tab(i)
and group_pl_id = l_group_pl_id_tab(i)
and group_oipl_id = l_group_oipl_id_tab(i);
l_group_per_in_ler_id_tab.delete;
l_group_pl_id_tab.delete;
l_group_oipl_id_tab.delete;
l_ws_bdgt_val_direct_tab.delete;
l_ws_bdgt_iss_val_direct_tab.delete;
update ben_cwb_summary
set ws_bdgt_val_direct = l_ws_bdgt_val_direct_tab(i)
,ws_bdgt_iss_val_direct = l_ws_bdgt_iss_val_direct_tab(i)
,bdgt_val_direct = l_bdgt_val_direct_tab(i)
,bdgt_iss_val_direct = l_bdgt_iss_val_direct_tab(i)
where group_per_in_ler_id = l_group_per_in_ler_id_tab(i)
and group_pl_id = l_group_pl_id_tab(i)
and group_oipl_id = l_group_oipl_id_tab(i);
l_group_per_in_ler_id_tab.delete;
l_group_pl_id_tab.delete;
l_group_oipl_id_tab.delete;
l_ws_bdgt_val_direct_tab.delete;
l_ws_bdgt_iss_val_direct_tab.delete;
l_bdgt_val_direct_tab.delete;
l_bdgt_iss_val_direct_tab.delete;
update ben_cwb_summary
set elig_count_all = l_elig_count_all_tab(i)
,emp_recv_count_all = l_emp_recv_count_all_tab(i)
,elig_sal_val_all = l_elig_sal_val_all_tab(i)
,ws_val_all = l_ws_val_all_tab(i)
,ws_bdgt_val_all = l_ws_bdgt_val_all_tab(i)
,ws_bdgt_iss_val_all = l_ws_bdgt_iss_val_all_tab(i)
,stat_sal_val_all = l_stat_sal_val_all_tab(i)
,oth_comp_val_all = l_oth_comp_val_all_tab(i)
,tot_comp_val_all = l_tot_comp_val_all_tab(i)
,rec_val_all = l_rec_val_all_tab(i)
,rec_mn_val_all = l_rec_mn_val_all_tab(i)
,rec_mx_val_all = l_rec_mx_val_all_tab(i)
,misc1_val_all = l_misc1_val_all_tab(i)
,misc2_val_all = l_misc2_val_all_tab(i)
,misc3_val_all = l_misc3_val_all_tab(i)
where group_per_in_ler_id = l_group_per_in_ler_id_tab(i)
and group_pl_id = l_group_pl_id_tab(i)
and group_oipl_id = l_group_oipl_id_tab(i);
l_group_per_in_ler_id_tab.delete;
l_group_pl_id_tab.delete;
l_group_oipl_id_tab.delete;
l_elig_count_all_tab.delete;
l_emp_recv_count_all_tab.delete;
l_elig_sal_val_all_tab.delete;
l_ws_val_all_tab.delete;
l_ws_bdgt_val_all_tab.delete;
l_ws_bdgt_iss_val_all_tab.delete;
l_stat_sal_val_all_tab.delete;
l_oth_comp_val_all_tab.delete;
l_tot_comp_val_all_tab.delete;
l_rec_val_all_tab.delete;
l_rec_mn_val_all_tab.delete;
l_rec_mx_val_all_tab.delete;
l_misc1_val_all_tab.delete;
l_misc2_val_all_tab.delete;
l_misc3_val_all_tab.delete;
update ben_cwb_summary
set bdgt_val_direct = l_bdgt_val_direct_tab(i)
,bdgt_iss_val_direct = l_bdgt_iss_val_direct_tab(i)
where group_per_in_ler_id = l_group_per_in_ler_id_tab(i)
and group_pl_id = l_group_pl_id_tab(i)
and group_oipl_id = l_group_oipl_id_tab(i);
l_group_per_in_ler_id_tab.delete;
l_group_pl_id_tab.delete;
l_group_oipl_id_tab.delete;
l_bdgt_val_direct_tab.delete;
l_bdgt_iss_val_direct_tab.delete;
select hrchy.mgr_per_in_ler_id group_per_in_ler_id
,rt.group_pl_id group_pl_id
,rt.group_oipl_id group_oipl_id
,count(rt.group_per_in_ler_id) elig_count_direct
,sum(decode(rt.ws_val,null,0,0,0,1)) emp_recv_count_direct
,sum(rt.elig_sal_val/decode(pl.elig_sal_nnmntry_uom,null
,xchg.xchg_rate,1)) elig_sal_val_direct
,sum(rt.ws_val/decode(pl.ws_nnmntry_uom,null
,xchg.xchg_rate,1)) ws_val_direct
,sum(rt.stat_sal_val/decode(pl.stat_sal_nnmntry_uom,null
,xchg.xchg_rate,1)) stat_sal_val_direct
,sum(rt.oth_comp_val/decode(pl.oth_comp_nnmntry_uom,null
,xchg.xchg_rate,1)) oth_comp_val_direct
,sum(rt.tot_comp_val/decode(pl.tot_comp_nnmntry_uom,null
,xchg.xchg_rate,1)) tot_comp_val_direct
,sum(rt.rec_val/decode(pl.rec_nnmntry_uom,null
,xchg.xchg_rate,1)) rec_val_direct
,sum(rt.rec_mn_val/decode(pl.rec_nnmntry_uom,null
,xchg.xchg_rate,1)) rec_mn_val_direct
,sum(rt.rec_mx_val/decode(pl.rec_nnmntry_uom,null
,xchg.xchg_rate,1)) rec_mx_val_direct
,sum(rt.misc1_val/decode(pl.misc1_nnmntry_uom,null
,xchg.xchg_rate,1)) misc1_val_direct
,sum(rt.misc2_val/decode(pl.misc2_nnmntry_uom,null
,xchg.xchg_rate,1)) misc2_val_direct
,sum(rt.misc3_val/decode(pl.misc3_nnmntry_uom,null
,xchg.xchg_rate,1)) misc3_val_direct
from ben_cwb_group_hrchy hrchy
,ben_cwb_person_rates rt
,ben_cwb_pl_dsgn pl
,ben_cwb_xchg xchg
where rt.group_pl_id = p_group_pl_id
and rt.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and rt.elig_flag = 'Y'
and rt.group_per_in_ler_id = hrchy.emp_per_in_ler_id
and hrchy.lvl_num = 1
and rt.pl_id = pl.pl_id
and pl.oipl_id = rt.oipl_id
and pl.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
and xchg.group_pl_id = rt.group_pl_id
and xchg.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
and xchg.currency = rt.currency
group by hrchy.mgr_per_in_ler_id,rt.group_pl_id, rt.group_oipl_id;
insert_refresh_job_marker(p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
delete from ben_cwb_summary summ
where group_pl_id = p_group_pl_id
and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and group_per_in_ler_id <> -1;
insert into ben_cwb_summary
(summary_id
,group_per_in_ler_id
,group_pl_id
,group_oipl_id
,person_id
,lf_evt_ocrd_dt)
select ben_cwb_summary_s.nextval
,hrchy.emp_per_in_ler_id
,p_group_pl_id
,grp.group_oipl_id
,pil.person_id
,p_lf_evt_ocrd_dt
from ben_cwb_group_hrchy hrchy
,ben_cwb_person_groups grp
,ben_per_in_ler pil
where hrchy.lvl_num=0
and hrchy.emp_per_in_ler_id = grp.group_per_in_ler_id
and pil.group_pl_id = p_group_pl_id
and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and grp.group_per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd in ('PROCD','STRTD');
update ben_cwb_summary
set elig_count_direct = l_elig_count_direct_tab(i)
,emp_recv_count_direct = l_emp_recv_count_direct_tab(i)
,elig_sal_val_direct = l_elig_sal_val_direct_tab(i)
,ws_val_direct = l_ws_val_direct_tab(i)
,stat_sal_val_direct = l_stat_sal_val_direct_tab(i)
,oth_comp_val_direct = l_oth_comp_val_direct_tab(i)
,tot_comp_val_direct = l_tot_comp_val_direct_tab(i)
,rec_val_direct = l_rec_val_direct_tab(i)
,rec_mn_val_direct = l_rec_mn_val_direct_tab(i)
,rec_mx_val_direct = l_rec_mx_val_direct_tab(i)
,misc1_val_direct = l_misc1_val_direct_tab(i)
,misc2_val_direct = l_misc2_val_direct_tab(i)
,misc3_val_direct = l_misc3_val_direct_tab(i)
where group_per_in_ler_id = l_group_per_in_ler_id_tab(i)
and group_pl_id = l_group_pl_id_tab(i)
and group_oipl_id = l_group_oipl_id_tab(i);
l_group_per_in_ler_id_tab.delete;
l_group_pl_id_tab.delete;
l_group_oipl_id_tab.delete;
l_elig_count_direct_tab.delete;
l_emp_recv_count_direct_tab.delete;
l_elig_sal_val_direct_tab.delete;
l_ws_val_direct_tab.delete;
l_stat_sal_val_direct_tab.delete;
l_oth_comp_val_direct_tab.delete;
l_tot_comp_val_direct_tab.delete;
l_rec_val_direct_tab.delete;
l_rec_mn_val_direct_tab.delete;
l_rec_mx_val_direct_tab.delete;
l_misc1_val_direct_tab.delete;
l_misc2_val_direct_tab.delete;
l_misc3_val_direct_tab.delete;
update_person_info(p_group_pl_id,p_lf_evt_ocrd_dt);
delete_refresh_job_marker(p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
delete_refresh_job_marker(p_group_pl_id => p_group_pl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt);
procedure update_budgets_summary(p_group_per_in_ler_id in number
,p_prsrv_bdgt_cd in varchar2
,p_only_all in boolean default false) is
-- cursor to compute the prcnt bdgts direct
cursor csr_prcnt_bdgts_direct(p_group_per_in_ler_id number) is
select grp.group_pl_id group_pl_id
,grp.group_oipl_id group_oipl_id
,sum(grp.ws_bdgt_val * summ.elig_sal_val_direct / 100)
ws_bdgt_val_direct
,sum(grp.ws_bdgt_iss_val * summ.elig_sal_val_direct / 100)
ws_bdgt_iss_val_direct
,sum(decode(nvl(grp.dist_bdgt_val,0),0,grp.ws_bdgt_val,
grp.dist_bdgt_val) * decode(nvl(grp.dist_bdgt_val,0),0,
summ.elig_sal_val_direct, summ.elig_sal_val_all) / 100)
bdgt_val_direct
,sum(decode(nvl(grp.dist_bdgt_val,0),0,grp.ws_bdgt_iss_val,
grp.dist_bdgt_iss_val) * decode(nvl(grp.dist_bdgt_val,0),0,
summ.elig_sal_val_direct, summ.elig_sal_val_all) / 100)
bdgt_iss_val_direct
from ben_cwb_group_hrchy hrchy
,ben_cwb_person_groups grp
,ben_cwb_summary summ
where hrchy.mgr_per_in_ler_id = p_group_per_in_ler_id
and hrchy.lvl_num = 1
and grp.group_per_in_ler_id = hrchy.emp_per_in_ler_id
and summ.group_per_in_ler_id (+)= grp.group_per_in_ler_id
and summ.group_pl_id (+)= grp.group_pl_id
and summ.group_oipl_id (+)= grp.group_oipl_id
group by grp.group_pl_id, grp.group_oipl_id
order by grp.group_pl_id, grp.group_oipl_id;
select grp.group_pl_id group_pl_id
,grp.group_oipl_id group_oipl_id
,sum(grp.ws_bdgt_val) ws_bdgt_val_direct
,sum(grp.ws_bdgt_iss_val) ws_bdgt_iss_val_direct
,sum(decode(nvl(grp.dist_bdgt_val,0),0,grp.ws_bdgt_val,
grp.dist_bdgt_val)) bdgt_val_direct
,sum(decode(nvl(grp.dist_bdgt_val,0),0,grp.ws_bdgt_iss_val,
grp.dist_bdgt_iss_val)) bdgt_iss_val_direct
from ben_cwb_group_hrchy hrchy
,ben_cwb_person_groups grp
where hrchy.mgr_per_in_ler_id = p_group_per_in_ler_id
and hrchy.lvl_num = 1
and grp.group_per_in_ler_id = hrchy.emp_per_in_ler_id
group by grp.group_pl_id, grp.group_oipl_id
order by grp.group_pl_id, grp.group_oipl_id;
select grp.group_pl_id group_pl_id
,grp.group_oipl_id group_oipl_id
,sum(grp.ws_bdgt_val * summ.elig_sal_val_direct / 100)
ws_bdgt_val_all
,sum(grp.ws_bdgt_iss_val * summ.elig_sal_val_direct / 100)
ws_bdgt_iss_val_all
from ben_cwb_group_hrchy hrchy
,ben_cwb_person_groups grp
,ben_cwb_summary summ
where hrchy.mgr_per_in_ler_id = p_group_per_in_ler_id
and hrchy.lvl_num >= 1
and grp.group_per_in_ler_id = hrchy.emp_per_in_ler_id
and summ.group_per_in_ler_id (+)= grp.group_per_in_ler_id
and summ.group_pl_id (+)= grp.group_pl_id
and summ.group_oipl_id (+)= grp.group_oipl_id
group by grp.group_pl_id, grp.group_oipl_id
order by grp.group_pl_id, grp.group_oipl_id;
select grp.group_pl_id group_pl_id
,grp.group_oipl_id group_oipl_id
,sum(grp.ws_bdgt_val) ws_bdgt_val_all
,sum(grp.ws_bdgt_iss_val) ws_bdgt_iss_val_all
from ben_cwb_group_hrchy hrchy
,ben_cwb_person_groups grp
where hrchy.mgr_per_in_ler_id = p_group_per_in_ler_id
and hrchy.lvl_num >= 1
and grp.group_per_in_ler_id = hrchy.emp_per_in_ler_id
group by grp.group_pl_id, grp.group_oipl_id
order by grp.group_pl_id, grp.group_oipl_id;
select sum(ws_bdgt_val_direct) ws_bdgt_val_direct
,sum(ws_bdgt_val_all) ws_bdgt_val_all
,sum(ws_bdgt_iss_val_direct) ws_bdgt_iss_val_direct
,sum(ws_bdgt_iss_val_all) ws_bdgt_iss_val_all
,sum(bdgt_val_direct) bdgt_val_direct
,sum(bdgt_iss_val_direct) bdgt_iss_val
from ben_cwb_summary
where group_per_in_ler_id = p_group_per_in_ler_id
group by group_pl_id, group_oipl_id
order by group_pl_id, group_oipl_id;
l_proc varchar2(72) := g_package||'update_budgets_summary';
update_or_insert_pl_sql_tab
(p_group_per_in_ler_id => p_group_per_in_ler_id
,p_group_pl_id => l_group_pl_id_tab(i)
,p_group_oipl_id => l_group_oipl_id_tab(i)
,p_ws_bdgt_val_direct => ben_cwb_utils.add_number_with_null_check
(l_ws_bdgt_val_direct_tab(i),
-l_old_ws_bdgt_val_direct_tab(i))
,p_ws_bdgt_val_all => ben_cwb_utils.add_number_with_null_check
(l_ws_bdgt_val_all_tab(i),
-l_old_ws_bdgt_val_all_tab(i))
,p_ws_bdgt_iss_val_direct => ben_cwb_utils.add_number_with_null_check
(l_ws_bdgt_iss_val_direct_tab(i),
-l_old_ws_bdgt_iss_val_dir_tab(i))
,p_ws_bdgt_iss_val_all => ben_cwb_utils.add_number_with_null_check
(l_ws_bdgt_iss_val_all_tab(i),
-l_old_ws_bdgt_iss_val_all_tab(i))
,p_bdgt_val_direct => ben_cwb_utils.add_number_with_null_check
(l_bdgt_val_direct_tab(i),
-l_old_bdgt_val_direct_tab(i))
,p_bdgt_iss_val_direct => ben_cwb_utils.add_number_with_null_check
(l_bdgt_iss_val_direct_tab(i),
-l_old_bdgt_iss_val_direct_tab(i)));
update_or_insert_pl_sql_tab
(p_group_per_in_ler_id => p_group_per_in_ler_id
,p_group_pl_id => l_group_pl_id_tab(i)
,p_group_oipl_id => l_group_oipl_id_tab(i)
,p_ws_bdgt_val_all => ben_cwb_utils.add_number_with_null_check
(l_ws_bdgt_val_all_tab(i),
-l_old_ws_bdgt_val_all_tab(i))
,p_ws_bdgt_iss_val_all => ben_cwb_utils.add_number_with_null_check
(l_ws_bdgt_iss_val_all_tab(i),
-l_old_ws_bdgt_iss_val_all_tab(i)));
end; -- update_budgets_summary
select per.group_per_in_ler_id
,hrchy.mgr_per_in_ler_id
from ben_cwb_person_info per
,ben_cwb_group_hrchy hrchy
where per.person_id = -1
and per.group_pl_id = p_group_pl_id
and per.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and per.group_per_in_ler_id = hrchy.emp_per_in_ler_id (+)
and per.group_per_in_ler_id = hrchy.mgr_per_in_ler_id (+);
select hrchy.mgr_per_in_ler_id
,p_group_pl_id group_pl_id
,grp.group_oipl_id
,pil.person_id
from ben_cwb_group_hrchy hrchy
,ben_cwb_person_info per
,ben_cwb_person_groups grp
,ben_per_in_ler pil
where per.person_id = -1
and per.group_per_in_ler_id = hrchy.emp_per_in_ler_id
and hrchy.mgr_per_in_ler_id = grp.group_per_in_ler_id
and grp.group_pl_id = p_group_pl_id
and grp.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and grp.group_per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd in ('PROCD','STRTD')
and not exists (select 'Y'
from ben_cwb_summary sm
where sm.group_per_in_ler_id = grp.group_per_in_ler_id
and sm.group_pl_id = grp.group_pl_id
and sm.group_oipl_id = grp.group_oipl_id)
group by hrchy.mgr_per_in_ler_id, group_oipl_id, pil.person_id;
select hrchy.mgr_per_in_ler_id group_per_in_ler_id
,rt.group_pl_id group_pl_id
,rt.group_oipl_id group_oipl_id
,count(rt.group_per_in_ler_id) elig_count
,sum(decode(rt.ws_val,null,0,0,0,1)) emp_recv_count
,sum(rt.elig_sal_val/decode(pl.elig_sal_nnmntry_uom,null
,xchg.xchg_rate,1)) elig_sal_val
,sum(rt.ws_val/decode(pl.ws_nnmntry_uom,null
,xchg.xchg_rate,1)) ws_val
,sum(rt.stat_sal_val/decode(pl.stat_sal_nnmntry_uom,null
,xchg.xchg_rate,1)) stat_sal_val
,sum(rt.oth_comp_val/decode(pl.oth_comp_nnmntry_uom,null
,xchg.xchg_rate,1)) oth_comp_val
,sum(rt.tot_comp_val/decode(pl.tot_comp_nnmntry_uom,null
,xchg.xchg_rate,1)) tot_comp_val
,sum(rt.rec_val/decode(pl.rec_nnmntry_uom,null
,xchg.xchg_rate,1)) rec_val
,sum(rt.rec_mn_val/decode(pl.rec_nnmntry_uom,null
,xchg.xchg_rate,1)) rec_mn_val
,sum(rt.rec_mx_val/decode(pl.rec_nnmntry_uom,null
,xchg.xchg_rate,1)) rec_mx_val
,sum(rt.misc1_val/decode(pl.misc1_nnmntry_uom,null
,xchg.xchg_rate,1)) misc1_val
,sum(rt.misc2_val/decode(pl.misc2_nnmntry_uom,null
,xchg.xchg_rate,1)) misc2_val
,sum(rt.misc3_val/decode(pl.misc3_nnmntry_uom,null
,xchg.xchg_rate,1)) misc3_val
from ben_cwb_group_hrchy hrchy
,ben_cwb_person_rates rt
,ben_cwb_pl_dsgn pl
,ben_cwb_xchg xchg
where hrchy.mgr_per_in_ler_id = v_group_per_in_ler_id
and rt.group_per_in_ler_id = hrchy.emp_per_in_ler_id
and hrchy.lvl_num = 1
and rt.elig_flag = 'Y'
and rt.pl_id = pl.pl_id
and pl.oipl_id = rt.oipl_id
and pl.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
and xchg.group_pl_id = rt.group_pl_id
and xchg.lf_evt_ocrd_dt = rt.lf_evt_ocrd_dt
and xchg.currency = rt.currency
group by hrchy.mgr_per_in_ler_id,rt.group_pl_id, rt.group_oipl_id;
select hrchy.mgr_per_in_ler_id
,summ.group_pl_id, summ.group_oipl_id
,sum(elig_count_direct) elig_count
,sum(emp_recv_count_direct) emp_recv_count
,sum(elig_sal_val_direct) elig_sal_val
,sum(ws_val_direct) ws_val
,sum(ws_bdgt_val_direct)ws_bdgt_val
,sum(ws_bdgt_iss_val_direct) ws_bdgt_iss_val
,sum(stat_sal_val_direct) stat_sal_val
,sum(oth_comp_val_direct) oth_comp_val
,sum(tot_comp_val_direct) tot_comp_val
,sum(rec_val_direct) rec_val
,sum(rec_mn_val_direct) rec_mn_val
,sum(rec_mx_val_direct) rec_mx_val
,sum(misc1_val_direct) misc1_val
,sum(misc2_val_direct) misc2_val
,sum(misc3_val_direct) misc3_val
from ben_cwb_group_hrchy hrchy
,ben_cwb_summary summ
where hrchy.mgr_per_in_ler_id = v_group_per_in_ler_id
and hrchy.lvl_num >=0
and hrchy.emp_per_in_ler_id = summ.group_per_in_ler_id
group by hrchy.mgr_per_in_ler_id, summ.group_pl_id, summ.group_oipl_id;
select h.mgr_per_in_ler_id
,h.lvl_num
,i.person_id
from ben_cwb_group_hrchy h
,ben_cwb_person_info i
where h.emp_per_in_ler_id = v_per_in_ler_id
and h.lvl_num > 0
and h.mgr_per_in_ler_id = i.group_per_in_ler_id
order by h.lvl_num;
select count(per.group_per_in_ler_id) into l_count
from ben_cwb_person_info per
where per.person_id = -1
and per.group_pl_id = p_group_pl_id
and per.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
insert into ben_cwb_summary
(summary_id
,group_per_in_ler_id
,group_pl_id
,group_oipl_id
,person_id
,lf_evt_ocrd_dt)
values (ben_cwb_summary_s.nextval
,l_empty_summ.mgr_per_in_ler_id
,l_empty_summ.group_pl_id
,l_empty_summ.group_oipl_id
,l_empty_summ.person_id
,p_lf_evt_ocrd_dt);
update_or_insert_pl_sql_tab
(p_group_per_in_ler_id => l_mgrs.mgr_per_in_ler_id
,p_group_pl_id => l_rates.group_pl_id
,p_group_oipl_id => l_rates.group_oipl_id
,p_elig_count_direct => l_rates.elig_count*l_immd_mgr
,p_elig_count_all => l_rates.elig_count
,p_emp_recv_count_direct => l_rates.emp_recv_count*l_immd_mgr
,p_emp_recv_count_all => l_rates.emp_recv_count
,p_elig_sal_val_direct => l_rates.elig_sal_val*l_immd_mgr
,p_elig_sal_val_all => l_rates.elig_sal_val
,p_ws_val_direct => l_rates.ws_val*l_immd_mgr
,p_ws_val_all => l_rates.ws_val
,p_stat_sal_val_direct => l_rates.stat_sal_val*l_immd_mgr
,p_stat_sal_val_all => l_rates.stat_sal_val
,p_oth_comp_val_direct => l_rates.oth_comp_val*l_immd_mgr
,p_oth_comp_val_all => l_rates.oth_comp_val
,p_tot_comp_val_direct => l_rates.tot_comp_val*l_immd_mgr
,p_tot_comp_val_all => l_rates.tot_comp_val
,p_rec_val_direct => l_rates.rec_val*l_immd_mgr
,p_rec_val_all => l_rates.rec_val
,p_rec_mn_val_direct => l_rates.rec_mn_val*l_immd_mgr
,p_rec_mn_val_all => l_rates.rec_mn_val
,p_rec_mx_val_direct => l_rates.rec_mx_val*l_immd_mgr
,p_rec_mx_val_all => l_rates.rec_mx_val
,p_misc1_val_direct => l_rates.misc1_val*l_immd_mgr
,p_misc1_val_all => l_rates.misc1_val
,p_misc2_val_direct => l_rates.misc2_val*l_immd_mgr
,p_misc2_val_all => l_rates.misc2_val
,p_misc3_val_direct => l_rates.misc3_val*l_immd_mgr
,p_misc3_val_all => l_rates.misc3_val);
update_or_insert_pl_sql_tab
(p_group_per_in_ler_id => l_emps.group_per_in_ler_id
,p_group_pl_id => l_directs.group_pl_id
,p_group_oipl_id => l_directs.group_oipl_id
,p_elig_count_direct => l_directs.elig_count
,p_emp_recv_count_direct => l_directs.emp_recv_count
,p_elig_sal_val_direct => l_directs.elig_sal_val
,p_ws_val_direct => l_directs.ws_val
,p_stat_sal_val_direct => l_directs.stat_sal_val
,p_oth_comp_val_direct => l_directs.oth_comp_val
,p_tot_comp_val_direct => l_directs.tot_comp_val
,p_rec_val_direct => l_directs.rec_val
,p_rec_mn_val_direct => l_directs.rec_mn_val
,p_rec_mx_val_direct => l_directs.rec_mx_val
,p_misc1_val_direct => l_directs.misc1_val
,p_misc2_val_direct => l_directs.misc2_val
,p_misc3_val_direct => l_directs.misc3_val);
update_or_insert_pl_sql_tab
(p_group_per_in_ler_id => l_emps.group_per_in_ler_id
,p_group_pl_id => l_all.group_pl_id
,p_group_oipl_id => l_all.group_oipl_id
,p_elig_count_all => l_all.elig_count
,p_emp_recv_count_all => l_all.emp_recv_count
,p_elig_sal_val_all => l_all.elig_sal_val
,p_ws_val_all => l_all.ws_val
,p_stat_sal_val_all => l_all.stat_sal_val
,p_oth_comp_val_all => l_all.oth_comp_val
,p_tot_comp_val_all => l_all.tot_comp_val
,p_rec_val_all => l_all.rec_val
,p_rec_mn_val_all => l_all.rec_mn_val
,p_rec_mx_val_all => l_all.rec_mx_val
,p_misc1_val_all => l_all.misc1_val
,p_misc2_val_all => l_all.misc2_val
,p_misc3_val_all => l_all.misc3_val);
update_or_insert_pl_sql_tab
(p_group_per_in_ler_id => l_mgrs.mgr_per_in_ler_id
,p_group_pl_id => l_all.group_pl_id
,p_group_oipl_id => l_all.group_oipl_id
,p_elig_count_all => l_all.elig_count
,p_emp_recv_count_all => l_all.emp_recv_count
,p_elig_sal_val_all => l_all.elig_sal_val
,p_ws_val_all => l_all.ws_val
,p_stat_sal_val_all => l_all.stat_sal_val
,p_oth_comp_val_all => l_all.oth_comp_val
,p_tot_comp_val_all => l_all.tot_comp_val
,p_rec_val_all => l_all.rec_val
,p_rec_mn_val_all => l_all.rec_mn_val
,p_rec_mx_val_all => l_all.rec_mx_val
,p_misc1_val_all => l_all.misc1_val
,p_misc2_val_all => l_all.misc2_val
,p_misc3_val_all => l_all.misc3_val);
select prsrv_bdgt_cd, uses_bdgt_flag
into l_prsrv_bdgt_cd, l_uses_bdgt_flag
from ben_cwb_pl_dsgn
where pl_id = p_group_pl_id
and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and oipl_id = -1;
update_budgets_summary(l_mgrs.mgr_per_in_ler_id,l_prsrv_bdgt_cd,
(l_mgrs.lvl_num > 2));
update_person_info(p_group_pl_id,p_lf_evt_ocrd_dt);
procedure update_or_insert_pl_sql_tab
(p_group_per_in_ler_id in number
,p_group_pl_id in number
,p_group_oipl_id in number
,p_elig_count_direct in number default null
,p_elig_count_all in number default null
,p_emp_recv_count_direct in number default null
,p_emp_recv_count_all in number default null
,p_elig_sal_val_direct in number default null
,p_elig_sal_val_all in number default null
,p_ws_val_direct in number default null
,p_ws_val_all in number default null
,p_ws_bdgt_val_direct in number default null
,p_ws_bdgt_val_all in number default null
,p_ws_bdgt_iss_val_direct in number default null
,p_ws_bdgt_iss_val_all in number default null
,p_bdgt_val_direct in number default null
,p_bdgt_iss_val_direct in number default null
,p_stat_sal_val_direct in number default null
,p_stat_sal_val_all in number default null
,p_oth_comp_val_direct in number default null
,p_oth_comp_val_all in number default null
,p_tot_comp_val_direct in number default null
,p_tot_comp_val_all in number default null
,p_rec_val_direct in number default null
,p_rec_val_all in number default null
,p_rec_mn_val_direct in number default null
,p_rec_mn_val_all in number default null
,p_rec_mx_val_direct in number default null
,p_rec_mx_val_all in number default null
,p_misc1_val_direct in number default null
,p_misc1_val_all in number default null
,p_misc2_val_direct in number default null
,p_misc2_val_all in number default null
,p_misc3_val_direct in number default null
,p_misc3_val_all in number default null
,p_person_id in number default null
,p_lf_evt_ocrd_dt in date default null) is
--
l_found boolean := false;
l_proc varchar2(72) := g_package||'update_or_insert_pl_sql_tab';
end; -- update_or_insert_pl_sql_tab
update_or_insert(g_summary_rec(i));
g_summary_rec.delete;
procedure delete_pl_sql_tab is
--
l_proc varchar2(72) := g_package||'delete_pl_sql_tab';
g_summary_rec.delete;
end; -- delete_pl_sql_tab
select hrchy.mgr_per_in_ler_id
from ben_cwb_group_hrchy hrchy
where hrchy.emp_per_in_ler_id = p_per_in_ler_id
and hrchy.lvl_num = p_lvl_up;
select sum(sm.elig_count_all)-sum(sm.elig_count_direct) indirect_count
,sum(sm.elig_count_all) all_count
,max(grp.bdgt_pop_cd) bdgt_pop_cd
from ben_cwb_summary sm
,ben_cwb_person_groups grp
where sm.group_per_in_ler_id = l_per_in_ler_id
and sm.group_oipl_id = -1
and sm.group_per_in_ler_id = grp.group_per_in_ler_id
and sm.group_pl_id = grp.group_pl_id
and sm.group_oipl_id = grp.group_oipl_id;
select grp.group_pl_id
,grp.group_oipl_id
,grp.lf_evt_ocrd_dt
,grp.object_version_number
,grp.dist_bdgt_val
,grp.dist_bdgt_iss_val
,grp.ws_bdgt_val
,grp.ws_bdgt_iss_val
,grp.ws_bdgt_iss_date
from ben_cwb_person_groups grp
where grp.group_per_in_ler_id = l_per_in_ler_id;
select grp.bdgt_pop_cd
from ben_cwb_group_hrchy hrchy
,ben_cwb_person_groups grp
where hrchy.emp_per_in_ler_id = l_per_in_ler_id
and hrchy.mgr_per_in_ler_id = grp.group_per_in_ler_id
and hrchy.lvl_num = 1
and grp.group_oipl_id = -1;
ben_cwb_person_groups_api.update_group_budget(
p_group_per_in_ler_id => l_per_in_ler_id
,p_group_pl_id => l_grps.group_pl_id
,p_group_oipl_id => l_grps.group_oipl_id
,p_lf_evt_ocrd_dt => l_grps.lf_evt_ocrd_dt
,p_bdgt_pop_cd => null
,p_dist_bdgt_val => null
,p_dist_bdgt_iss_val => null
,p_dist_bdgt_iss_date => null
,p_ws_bdgt_val => null
,p_ws_bdgt_iss_val => null
,p_ws_bdgt_iss_date => null
,p_object_version_number => l_grps.object_version_number
,p_perf_min_max_edit => 'N');
ben_cwb_person_groups_api.update_group_budget(
p_group_per_in_ler_id => l_per_in_ler_id
,p_group_pl_id => l_grps.group_pl_id
,p_group_oipl_id => l_grps.group_oipl_id
,p_lf_evt_ocrd_dt => l_grps.lf_evt_ocrd_dt
,p_bdgt_pop_cd => null
,p_dist_bdgt_val => null
,p_dist_bdgt_iss_val => null
,p_dist_bdgt_iss_date => null
,p_object_version_number => l_grps.object_version_number
,p_perf_min_max_edit => 'N');
ben_cwb_person_groups_api.update_group_budget(
p_group_per_in_ler_id => l_per_in_ler_id
,p_group_pl_id => l_grps.group_pl_id
,p_group_oipl_id => l_grps.group_oipl_id
,p_lf_evt_ocrd_dt => l_grps.lf_evt_ocrd_dt
,p_bdgt_pop_cd => 'D'
,p_dist_bdgt_val => l_grps.ws_bdgt_val
,p_dist_bdgt_iss_val => l_grps.ws_bdgt_iss_val
,p_dist_bdgt_iss_date => l_grps.ws_bdgt_iss_date
,p_object_version_number => l_grps.object_version_number
,p_perf_min_max_edit => 'N');
update_or_insert_pl_sql_tab
(p_group_per_in_ler_id => mgr.mgr_per_in_ler_id
,p_group_pl_id => l_rts_group_pl_id_tab(i)
,p_group_oipl_id => l_rts_group_oipl_id_tab(i)
,p_elig_count_direct => -(l_elig_count_tab(i)) *
l_immd_mgr
,p_elig_count_all => -(nvl(l_elig_count_tab(i),0)
+ nvl(l_elig_count_all_tab(i),0))
,p_emp_recv_count_direct => -(l_emp_recv_count_tab(i)) *
l_immd_mgr
,p_emp_recv_count_all =>
-(nvl(l_emp_recv_count_tab(i),0) +
nvl(l_emp_recv_count_all_tab(i),0))
,p_elig_sal_val_direct => -(l_elig_sal_val_tab(i)) *
l_immd_mgr
,p_elig_sal_val_all => - ben_cwb_utils.add_number_with_null_check
(l_elig_sal_val_tab(i),
l_elig_sal_val_all_tab(i))
,p_ws_val_direct =>-(l_ws_val_tab(i)) * l_immd_mgr
,p_ws_val_all => - ben_cwb_utils.add_number_with_null_check
(l_ws_val_tab(i),
l_ws_val_all_tab(i))
,p_stat_sal_val_direct => -(l_stat_sal_val_tab(i)) *
l_immd_mgr
,p_stat_sal_val_all => - ben_cwb_utils.add_number_with_null_check
(l_stat_sal_val_tab(i),
l_stat_sal_val_all_tab(i))
,p_oth_comp_val_direct => -(l_oth_comp_val_tab(i)) *
l_immd_mgr
,p_oth_comp_val_all => - ben_cwb_utils.add_number_with_null_check
(l_oth_comp_val_tab(i),
l_oth_comp_val_all_tab(i))
,p_tot_comp_val_direct => -(l_tot_comp_val_tab(i)) *
l_immd_mgr
,p_tot_comp_val_all => - ben_cwb_utils.add_number_with_null_check
(l_tot_comp_val_tab(i),
l_tot_comp_val_all_tab(i))
,p_rec_val_direct => -(l_rec_val_tab(i)) *
l_immd_mgr
,p_rec_val_all => - ben_cwb_utils.add_number_with_null_check
(l_rec_val_tab(i),
l_rec_val_all_tab(i))
,p_rec_mn_val_direct => -(l_rec_mn_val_tab(i)) *
l_immd_mgr
,p_rec_mn_val_all => - ben_cwb_utils.add_number_with_null_check
(l_rec_mn_val_tab(i),
l_rec_mn_val_all_tab(i))
,p_rec_mx_val_direct => -(l_rec_mx_val_tab(i)) *
l_immd_mgr
,p_rec_mx_val_all => - ben_cwb_utils.add_number_with_null_check
(l_rec_mx_val_tab(i),
l_rec_mx_val_all_tab(i))
,p_misc1_val_direct => -(l_misc1_val_tab(i)) *
l_immd_mgr
,p_misc1_val_all => - ben_cwb_utils.add_number_with_null_check
(l_misc1_val_tab(i),
l_misc1_val_all_tab(i))
,p_misc2_val_direct => -(l_misc2_val_tab(i)) *
l_immd_mgr
,p_misc2_val_all => - ben_cwb_utils.add_number_with_null_check
(l_misc2_val_tab(i),
l_misc2_val_all_tab(i))
,p_misc3_val_direct => -(l_misc3_val_all_tab(i)) *
l_immd_mgr
,p_misc3_val_all => - ben_cwb_utils.add_number_with_null_check
(l_misc3_val_tab(i),
l_misc3_val_all_tab(i))
);
update_or_insert_pl_sql_tab
(p_group_per_in_ler_id => mgr.mgr_per_in_ler_id
,p_group_pl_id => l_rts_group_pl_id_tab(i)
,p_group_oipl_id => l_rts_group_oipl_id_tab(i)
,p_elig_count_direct => (l_elig_count_tab(i)) *
l_immd_mgr
,p_elig_count_all => (nvl(l_elig_count_tab(i),0)
+ nvl(l_elig_count_all_tab(i),0))
,p_emp_recv_count_direct => (l_emp_recv_count_tab(i)) *
l_immd_mgr
,p_emp_recv_count_all =>
(nvl(l_emp_recv_count_tab(i),0) +
nvl(l_emp_recv_count_all_tab(i),0))
,p_elig_sal_val_direct => (l_elig_sal_val_tab(i)) *
l_immd_mgr
,p_elig_sal_val_all => ben_cwb_utils.add_number_with_null_check
(l_elig_sal_val_tab(i),
l_elig_sal_val_all_tab(i))
,p_ws_val_direct =>(l_ws_val_tab(i)) * l_immd_mgr
,p_ws_val_all => ben_cwb_utils.add_number_with_null_check
(l_ws_val_tab(i),
l_ws_val_all_tab(i))
,p_stat_sal_val_direct => (l_stat_sal_val_tab(i)) *
l_immd_mgr
,p_stat_sal_val_all => ben_cwb_utils.add_number_with_null_check
(l_stat_sal_val_tab(i),
l_stat_sal_val_all_tab(i))
,p_oth_comp_val_direct => (l_oth_comp_val_tab(i)) *
l_immd_mgr
,p_oth_comp_val_all => ben_cwb_utils.add_number_with_null_check
(l_oth_comp_val_tab(i),
l_oth_comp_val_all_tab(i))
,p_tot_comp_val_direct => (l_tot_comp_val_tab(i)) *
l_immd_mgr
,p_tot_comp_val_all => ben_cwb_utils.add_number_with_null_check
(l_tot_comp_val_tab(i),
l_tot_comp_val_all_tab(i))
,p_rec_val_direct => (l_rec_val_tab(i)) *
l_immd_mgr
,p_rec_val_all => ben_cwb_utils.add_number_with_null_check
(l_rec_val_tab(i),
l_rec_val_all_tab(i))
,p_rec_mn_val_direct => (l_rec_mn_val_tab(i)) *
l_immd_mgr
,p_rec_mn_val_all => ben_cwb_utils.add_number_with_null_check
(l_rec_mn_val_tab(i),
l_rec_mn_val_all_tab(i))
,p_rec_mx_val_direct => (l_rec_mx_val_tab(i)) *
l_immd_mgr
,p_rec_mx_val_all => ben_cwb_utils.add_number_with_null_check
(l_rec_mx_val_tab(i),
l_rec_mx_val_all_tab(i))
,p_misc1_val_direct => (l_misc1_val_tab(i)) *
l_immd_mgr
,p_misc1_val_all => ben_cwb_utils.add_number_with_null_check
(l_misc1_val_tab(i),
l_misc1_val_all_tab(i))
,p_misc2_val_direct => (l_misc2_val_tab(i)) *
l_immd_mgr
,p_misc2_val_all => ben_cwb_utils.add_number_with_null_check
(l_misc2_val_tab(i),
l_misc2_val_all_tab(i))
,p_misc3_val_direct => (l_misc3_val_all_tab(i)) *
l_immd_mgr
,p_misc3_val_all => ben_cwb_utils.add_number_with_null_check
(l_misc3_val_tab(i),
l_misc3_val_all_tab(i))
);
select pl.prsrv_bdgt_cd
,pl.uses_bdgt_flag
into l_prsrv_bdgt_cd
,l_uses_bdgt_flag
from ben_cwb_pl_dsgn pl
,ben_cwb_person_groups grp
where grp.group_per_in_ler_id = nvl(p_new_mgr_per_in_ler_id,p_old_mgr_per_in_ler_id)
and grp.group_oipl_id = -1
and pl.pl_id = grp.group_pl_id
and pl.oipl_id = grp.group_oipl_id
and pl.lf_evt_ocrd_dt = grp.lf_evt_ocrd_dt;
update_budgets_summary(mgr.mgr_per_in_ler_id
,l_prsrv_bdgt_cd
,(mgr.lvl_num > 1));
update_budgets_summary(mgr.mgr_per_in_ler_id
,l_prsrv_bdgt_cd
,(mgr.lvl_num > 1));
update_budgets_summary(l_mgrs.mgr_per_in_ler_id
,l_prsrv_bdgt_cd
,(l_mgrs.lvl_num > 1));
update_or_insert_pl_sql_tab
(p_group_per_in_ler_id => mgr.mgr_per_in_ler_id
,p_group_pl_id => l_rts_group_pl_id_tab(i)
,p_group_oipl_id => l_rts_group_oipl_id_tab(i)
,p_elig_count_direct => -(l_elig_count_tab(i)) *
l_immd_mgr
,p_elig_count_all => -(l_elig_count_tab(i))
,p_emp_recv_count_direct => -(l_emp_recv_count_tab(i))
* l_immd_mgr
,p_emp_recv_count_all => -(l_emp_recv_count_tab(i))
,p_elig_sal_val_direct => -(l_elig_sal_val_tab(i)) *
l_immd_mgr
,p_elig_sal_val_all => -(l_elig_sal_val_tab(i))
,p_ws_val_direct => -(l_ws_val_tab(i)) *
l_immd_mgr
,p_ws_val_all => -(l_ws_val_tab(i))
,p_stat_sal_val_direct => -(l_stat_sal_val_tab(i)) *
l_immd_mgr
,p_stat_sal_val_all => -(l_stat_sal_val_tab(i))
,p_oth_comp_val_direct => -(l_oth_comp_val_tab(i)) *
l_immd_mgr
,p_oth_comp_val_all => -(l_oth_comp_val_tab(i))
,p_tot_comp_val_direct => -(l_tot_comp_val_tab(i)) *
l_immd_mgr
,p_tot_comp_val_all => -(l_tot_comp_val_tab(i))
,p_rec_val_direct => -(l_rec_val_tab(i)) *
l_immd_mgr
,p_rec_val_all => -(l_rec_val_tab(i))
,p_rec_mn_val_direct => -(l_rec_mn_val_tab(i)) *
l_immd_mgr
,p_rec_mn_val_all => -(l_rec_mn_val_tab(i))
,p_rec_mx_val_direct => -(l_rec_mx_val_tab(i)) *
l_immd_mgr
,p_rec_mx_val_all => -(l_rec_mx_val_tab(i))
,p_misc1_val_direct => -(l_misc1_val_tab(i)) *
l_immd_mgr
,p_misc1_val_all => -(l_misc1_val_tab(i))
,p_misc2_val_direct => -(l_misc2_val_tab(i)) *
l_immd_mgr
,p_misc2_val_all => -(l_misc2_val_tab(i))
,p_misc3_val_direct => -(l_misc3_val_tab(i)) *
l_immd_mgr
,p_misc3_val_all => -(l_misc3_val_tab(i))
);
update_or_insert_pl_sql_tab
(p_group_per_in_ler_id => mgr.mgr_per_in_ler_id
,p_group_pl_id => l_rts_group_pl_id_tab(i)
,p_group_oipl_id => l_rts_group_oipl_id_tab(i)
,p_elig_count_direct => (l_elig_count_tab(i)) *
l_immd_mgr
,p_elig_count_all => (l_elig_count_tab(i))
,p_emp_recv_count_direct => (l_emp_recv_count_tab(i))
* l_immd_mgr
,p_emp_recv_count_all => (l_emp_recv_count_tab(i))
,p_elig_sal_val_direct => (l_elig_sal_val_tab(i)) *
l_immd_mgr
,p_elig_sal_val_all => (l_elig_sal_val_tab(i))
,p_ws_val_direct => (l_ws_val_tab(i)) *
l_immd_mgr
,p_ws_val_all => (l_ws_val_tab(i))
,p_stat_sal_val_direct => (l_stat_sal_val_tab(i)) *
l_immd_mgr
,p_stat_sal_val_all => (l_stat_sal_val_tab(i))
,p_oth_comp_val_direct => (l_oth_comp_val_tab(i)) *
l_immd_mgr
,p_oth_comp_val_all => (l_oth_comp_val_tab(i))
,p_tot_comp_val_direct => (l_tot_comp_val_tab(i)) *
l_immd_mgr
,p_tot_comp_val_all => (l_tot_comp_val_tab(i))
,p_rec_val_direct => (l_rec_val_tab(i)) *
l_immd_mgr
,p_rec_val_all => (l_rec_val_tab(i))
,p_rec_mn_val_direct => (l_rec_mn_val_tab(i)) *
l_immd_mgr
,p_rec_mn_val_all => (l_rec_mn_val_tab(i))
,p_rec_mx_val_direct => (l_rec_mx_val_tab(i)) *
l_immd_mgr
,p_rec_mx_val_all => (l_rec_mx_val_tab(i))
,p_misc1_val_direct => (l_misc1_val_tab(i)) *
l_immd_mgr
,p_misc1_val_all => (l_misc1_val_tab(i))
,p_misc2_val_direct => (l_misc2_val_tab(i)) *
l_immd_mgr
,p_misc2_val_all => (l_misc2_val_tab(i))
,p_misc3_val_direct => (l_misc3_val_tab(i)) *
l_immd_mgr
,p_misc3_val_all => (l_misc3_val_tab(i))
);
select pl.prsrv_bdgt_cd
,pl.uses_bdgt_flag
into l_prsrv_bdgt_cd
,l_uses_bdgt_flag
from ben_cwb_pl_dsgn pl
,ben_cwb_person_groups grp
where grp.group_per_in_ler_id = nvl(p_new_mgr_per_in_ler_id, p_old_mgr_per_in_ler_id)
and grp.group_oipl_id = -1
and pl.pl_id = grp.group_pl_id
and pl.oipl_id = grp.group_oipl_id
and pl.lf_evt_ocrd_dt = grp.lf_evt_ocrd_dt;
update_budgets_summary(mgr.mgr_per_in_ler_id
,l_prsrv_bdgt_cd
,(mgr.lvl_num>1));
update_budgets_summary(mgr.mgr_per_in_ler_id
,l_prsrv_bdgt_cd
,(mgr.lvl_num>1));
update_budgets_summary(l_mgrs.mgr_per_in_ler_id
,l_prsrv_bdgt_cd
,(l_mgrs.lvl_num>1));
procedure update_summary_on_reassignment(p_old_mgr_per_in_ler_id in number
,p_new_mgr_per_in_ler_id in number
,p_emp_per_in_ler_id in number) is
--
l_is_mgr varchar2(1);
l_insert_old_mgr boolean;
l_proc varchar2(72) := g_package||'update_summary_on_reassignment';
select null into l_dummy
from ben_cwb_group_hrchy
where mgr_per_in_ler_id = p_emp_per_in_ler_id
and lvl_num = 0;
select null into l_dummy
from ben_cwb_group_hrchy hrchy
where emp_per_in_ler_id = p_old_mgr_per_in_ler_id
and lvl_num = 0;
insert into ben_cwb_group_hrchy(mgr_per_in_ler_id
,emp_per_in_ler_id
,lvl_num)
values(p_old_mgr_per_in_ler_id
,p_old_mgr_per_in_ler_id
,0);
l_insert_old_mgr :=true;
insert into ben_cwb_summary
(summary_id
,group_per_in_ler_id
,group_pl_id
,group_oipl_id
,person_id
,lf_evt_ocrd_dt)
select ben_cwb_summary_s.nextval
,grp.group_per_in_ler_id
,grp.group_pl_id
,grp.group_oipl_id
,pil.person_id
,pil.lf_evt_ocrd_dt
from ben_cwb_person_groups grp
,ben_per_in_ler pil
where grp.group_per_in_ler_id = p_new_mgr_per_in_ler_id
and grp.group_per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd in ('PROCD','STRTD')
and not exists(select null
from ben_cwb_summary
where group_per_in_ler_id = p_new_mgr_per_in_ler_id);
if l_insert_old_mgr then
--
if g_debug then
hr_utility.set_location(l_proc, 50);
delete from ben_cwb_group_hrchy
where mgr_per_in_ler_id = p_old_mgr_per_in_ler_id
and lvl_num = 0;
procedure delete_summary_group_pl(p_group_pl_id number
,p_lf_evt_ocrd_dt date)
is
--
l_proc varchar2(72) := g_package||'delete_summary_group_pl';
delete from ben_cwb_summary
where group_pl_id = p_group_pl_id
and lf_evt_ocrd_dt = p_lf_evt_ocrd_dt;
select grp.group_pl_id
,grp.group_oipl_id
,grp.ws_bdgt_val * p_sal / 100 ws_bdgt_val
,grp.ws_bdgt_iss_val * p_sal / 100 ws_bdgt_iss_val
from ben_cwb_group_hrchy hrchy
,ben_cwb_person_groups grp
where hrchy.emp_per_in_ler_id = p_group_per_in_ler_id
and hrchy.mgr_per_in_ler_id = grp.group_per_in_ler_id
and hrchy.lvl_num = 1
order by grp.group_pl_id, grp.group_oipl_id;
select hrchy.mgr_per_in_ler_id per_in_ler_id
,mgr.mgr_per_in_ler_id next_mgr_id
from ben_cwb_group_hrchy hrchy
,ben_cwb_group_hrchy mgr
where hrchy.emp_per_in_ler_id = p_group_per_in_ler_id
and hrchy.lvl_num > 0
and mgr.emp_per_in_ler_id (+) = hrchy.mgr_per_in_ler_id
and mgr.lvl_num (+) = 1
order by hrchy.lvl_num;
select pl.prsrv_bdgt_cd
,pl.uses_bdgt_flag
into l_prsrv_bdgt_cd
,l_uses_bdgt_flag
from ben_cwb_pl_dsgn pl
,ben_cwb_person_groups grp
where grp.group_per_in_ler_id = p_group_per_in_ler_id
and grp.group_oipl_id = -1
and pl.pl_id = grp.group_pl_id
and pl.oipl_id = grp.group_oipl_id
and pl.lf_evt_ocrd_dt = grp.lf_evt_ocrd_dt;
select decode(nvl(grp.dist_bdgt_val,0),0,grp.ws_bdgt_val,
grp.dist_bdgt_val) * p_elig_sal_change /100
,decode(nvl(grp.dist_bdgt_iss_val,0),0,grp.ws_bdgt_iss_val,
grp.dist_bdgt_iss_val) * p_elig_sal_change /100
into l_bdgt_val
,l_bdgt_iss_val
from ben_cwb_person_groups grp
where grp.group_per_in_ler_id = mgr.per_in_ler_id
and grp.group_pl_id = l_group_pl_id_tab(i)
and grp.group_oipl_id = l_group_oipl_id_tab(i);
update_or_insert_pl_sql_tab
(p_group_per_in_ler_id => mgr.next_mgr_id
,p_group_pl_id => l_group_pl_id_tab(i)
,p_group_oipl_id => l_group_oipl_id_tab(i)
,p_ws_bdgt_val_direct => l_ws_bdgt_val_tab(i) * l_immd_mgr
,p_ws_bdgt_val_all => l_ws_bdgt_val_tab(i)
,p_ws_bdgt_iss_val_direct => l_ws_bdgt_iss_val_tab(i) * l_immd_mgr
,p_ws_bdgt_iss_val_all => l_ws_bdgt_iss_val_tab(i)
,p_bdgt_val_direct => l_bdgt_val
,p_bdgt_iss_val_direct => l_bdgt_iss_val
);
end; -- end of update_summary_on_elig_sal_change
select distinct group_pl_id
,lf_evt_ocrd_dt
from ben_cwb_person_info cpi
where cpi.person_id = -1;