[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select *
from ben_cwb_person_groups
where group_per_in_ler_id = v_group_per_in_ler_id
and group_pl_id = v_group_pl_id
and group_oipl_id = v_group_oipl_id;
select full_name into l_person_name
from ben_cwb_person_info
where group_per_in_ler_id = p_group_per_in_ler_id;
procedure update_group_budget_summary
(p_grp_bdgt_old csr_grps%rowtype
,p_dist_bdgt_val number
,p_ws_bdgt_val number
,p_dist_bdgt_iss_val number
,p_ws_bdgt_iss_val number) is
--cursor to fetch the managers of the person
cursor csr_mgr_pil_ids(p_group_per_in_ler_id number) is
select mgr_per_in_ler_id
from ben_cwb_group_hrchy
where emp_per_in_ler_id = p_group_per_in_ler_id
and lvl_num > 0
order by lvl_num;
l_proc varchar2(72) := g_package||'update_group_budget_summary';
select uses_bdgt_flag, prsrv_bdgt_cd
into l_uses_bdgt_flag, l_prsrv_bdgt_cd
from ben_cwb_pl_dsgn pl
where pl.pl_id = p_grp_bdgt_old.group_pl_id
and pl.lf_evt_ocrd_dt = p_grp_bdgt_old.lf_evt_ocrd_dt
and pl.oipl_id = p_grp_bdgt_old.group_oipl_id;
select sum(elig_sal_val_direct) elig_sal_val_direct
,sum(elig_sal_val_all) elig_sal_val_all
into l_elig_sal_direct
,l_elig_sal_all
from ben_cwb_summary
where group_per_in_ler_id = p_grp_bdgt_old.group_per_in_ler_id
and group_pl_id = p_grp_bdgt_old.group_pl_id
and group_oipl_id = p_grp_bdgt_old.group_oipl_id;
ben_cwb_summary_pkg.update_or_insert_pl_sql_tab
(p_group_per_in_ler_id => mgr.mgr_per_in_ler_id
,p_group_pl_id => p_grp_bdgt_old.group_pl_id
,p_group_oipl_id => p_grp_bdgt_old.group_oipl_id
,p_ws_bdgt_val_direct => l_diff_ws_bdgt * l_immediate_mgr
,p_ws_bdgt_val_all => l_diff_ws_bdgt
,p_ws_bdgt_iss_val_direct => l_diff_ws_bdgt_iss*l_immediate_mgr
,p_ws_bdgt_iss_val_all => l_diff_ws_bdgt_iss
,p_bdgt_val_direct => l_diff_dist_bdgt * l_immediate_mgr
,p_bdgt_iss_val_direct => l_diff_dist_bdgt_iss*l_immediate_mgr
);
end; -- update_group_budget_summary
select * into l_pl_dsgn
from ben_cwb_pl_dsgn
where pl_id = p_grp_bdgt_old.group_pl_id
and lf_evt_ocrd_dt = p_grp_bdgt_old.lf_evt_ocrd_dt
and oipl_id = -1;
select employee_id into l_person_id
from fnd_user
where user_id = l_grp_bdgt_new.last_updated_by;
select meaning into l_cd_meaning_old
from hr_lookups
where lookup_type='BEN_SUBMIT_STAT'
and lookup_code=p_grp_bdgt_old.submit_cd;
select meaning into l_cd_meaning_new
from hr_lookups
where lookup_type='BEN_SUBMIT_STAT'
and lookup_code=l_grp_bdgt_new.submit_cd;
select meaning into l_cd_meaning_old
from hr_lookups
where lookup_type='BEN_APPR_STAT'
and lookup_code=p_grp_bdgt_old.approval_cd;
select meaning into l_cd_meaning_new
from hr_lookups
where lookup_type='BEN_APPR_STAT'
and lookup_code=l_grp_bdgt_new.approval_cd;
select meaning into l_cd_meaning_old
from hr_lookups
where lookup_type='BEN_WS_ACC'
and lookup_code=p_grp_bdgt_old.access_cd;
select meaning into l_cd_meaning_new
from hr_lookups
where lookup_type='BEN_WS_ACC'
and lookup_code=l_grp_bdgt_new.access_cd;
procedure update_group_budget
(p_validate in boolean default false
,p_group_per_in_ler_id in number
,p_group_pl_id in number
,p_group_oipl_id in number
,p_lf_evt_ocrd_dt in date default hr_api.g_date
,p_bdgt_pop_cd in varchar2 default hr_api.g_varchar2
,p_due_dt in date default hr_api.g_date
,p_access_cd in varchar2 default hr_api.g_varchar2
,p_approval_cd in varchar2 default hr_api.g_varchar2
,p_approval_date in date default hr_api.g_date
,p_approval_comments in varchar2 default hr_api.g_varchar2
,p_dist_bdgt_val in number default hr_api.g_number
,p_ws_bdgt_val in number default hr_api.g_number
,p_rsrv_val in number default hr_api.g_number
,p_dist_bdgt_mn_val in number default hr_api.g_number
,p_dist_bdgt_mx_val in number default hr_api.g_number
,p_dist_bdgt_incr_val in number default hr_api.g_number
,p_ws_bdgt_mn_val in number default hr_api.g_number
,p_ws_bdgt_mx_val in number default hr_api.g_number
,p_ws_bdgt_incr_val in number default hr_api.g_number
,p_rsrv_mn_val in number default hr_api.g_number
,p_rsrv_mx_val in number default hr_api.g_number
,p_rsrv_incr_val in number default hr_api.g_number
,p_dist_bdgt_iss_val in number default hr_api.g_number
,p_ws_bdgt_iss_val in number default hr_api.g_number
,p_dist_bdgt_iss_date in date default hr_api.g_date
,p_ws_bdgt_iss_date in date default hr_api.g_date
,p_ws_bdgt_val_last_upd_date in date default hr_api.g_date
,p_dist_bdgt_val_last_upd_date in date default hr_api.g_date
,p_rsrv_val_last_upd_date in date default hr_api.g_date
,p_ws_bdgt_val_last_upd_by in number default hr_api.g_number
,p_dist_bdgt_val_last_upd_by in number default hr_api.g_number
,p_rsrv_val_last_upd_by in number default hr_api.g_number
,p_submit_cd in varchar2 default hr_api.g_varchar2
,p_submit_date in date default hr_api.g_date
,p_submit_comments in varchar2 default hr_api.g_varchar2
,p_perf_min_max_edit in varchar2 default 'Y'
,p_object_version_number in out nocopy number
) is
--
-- Declare cursors and local variables
--
l_object_version_number number;
select dist_bdgt_mn_val
,dist_bdgt_mx_val
,dist_bdgt_incr_val
,ws_bdgt_mn_val
,ws_bdgt_mx_val
,ws_bdgt_incr_val
,rsrv_mn_val
,rsrv_mx_val
,rsrv_incr_val
,group_per_in_ler_id
from ben_cwb_person_groups grp
where group_per_in_ler_id = p_group_per_in_ler_id
and group_pl_id = p_group_pl_id
and group_oipl_id = p_group_oipl_id;
l_proc varchar2(72) := g_package||'update_group_budget';
savepoint update_group_budget;
ben_cwb_person_groups_bk2.update_group_budget_b
(p_group_per_in_ler_id => p_group_per_in_ler_id
,p_group_pl_id => p_group_pl_id
,p_group_oipl_id => p_group_oipl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
,p_bdgt_pop_cd => p_bdgt_pop_cd
,p_due_dt => p_due_dt
,p_access_cd => p_access_cd
,p_approval_cd => p_approval_cd
,p_approval_date => p_approval_date
,p_approval_comments => p_approval_comments
,p_dist_bdgt_val => p_dist_bdgt_val
,p_ws_bdgt_val => p_ws_bdgt_val
,p_rsrv_val => p_rsrv_val
,p_dist_bdgt_mn_val => p_dist_bdgt_mn_val
,p_dist_bdgt_mx_val => p_dist_bdgt_mx_val
,p_dist_bdgt_incr_val => p_dist_bdgt_incr_val
,p_ws_bdgt_mn_val => p_ws_bdgt_mn_val
,p_ws_bdgt_mx_val => p_ws_bdgt_mx_val
,p_ws_bdgt_incr_val => p_ws_bdgt_incr_val
,p_rsrv_mn_val => p_rsrv_mn_val
,p_rsrv_mx_val => p_rsrv_mx_val
,p_rsrv_incr_val => p_rsrv_incr_val
,p_dist_bdgt_iss_val => p_dist_bdgt_iss_val
,p_ws_bdgt_iss_val => p_ws_bdgt_iss_val
,p_dist_bdgt_iss_date => p_dist_bdgt_iss_date
,p_ws_bdgt_iss_date => p_ws_bdgt_iss_date
,p_ws_bdgt_val_last_upd_date => p_ws_bdgt_val_last_upd_date
,p_dist_bdgt_val_last_upd_date => p_dist_bdgt_val_last_upd_date
,p_rsrv_val_last_upd_date => p_rsrv_val_last_upd_date
,p_ws_bdgt_val_last_upd_by => p_ws_bdgt_val_last_upd_by
,p_dist_bdgt_val_last_upd_by => p_dist_bdgt_val_last_upd_by
,p_rsrv_val_last_upd_by => p_rsrv_val_last_upd_by
,p_submit_cd => p_submit_cd
,p_submit_date => p_submit_date
,p_submit_comments => p_submit_comments
,p_object_version_number => l_object_version_number
);
(p_module_name => 'update_group_budget'
,p_hook_type => 'BP'
);
ben_cwb_person_groups_bk2.update_group_budget_a
(p_group_per_in_ler_id => p_group_per_in_ler_id
,p_group_pl_id => p_group_pl_id
,p_group_oipl_id => p_group_oipl_id
,p_lf_evt_ocrd_dt => p_lf_evt_ocrd_dt
,p_bdgt_pop_cd => p_bdgt_pop_cd
,p_due_dt => p_due_dt
,p_access_cd => p_access_cd
,p_approval_cd => p_approval_cd
,p_approval_date => p_approval_date
,p_approval_comments => p_approval_comments
,p_dist_bdgt_val => p_dist_bdgt_val
,p_ws_bdgt_val => p_ws_bdgt_val
,p_rsrv_val => p_rsrv_val
,p_dist_bdgt_mn_val => p_dist_bdgt_mn_val
,p_dist_bdgt_mx_val => p_dist_bdgt_mx_val
,p_dist_bdgt_incr_val => p_dist_bdgt_incr_val
,p_ws_bdgt_mn_val => p_ws_bdgt_mn_val
,p_ws_bdgt_mx_val => p_ws_bdgt_mx_val
,p_ws_bdgt_incr_val => p_ws_bdgt_incr_val
,p_rsrv_mn_val => p_rsrv_mn_val
,p_rsrv_mx_val => p_rsrv_mx_val
,p_rsrv_incr_val => p_rsrv_incr_val
,p_dist_bdgt_iss_val => p_dist_bdgt_iss_val
,p_ws_bdgt_iss_val => p_ws_bdgt_iss_val
,p_dist_bdgt_iss_date => p_dist_bdgt_iss_date
,p_ws_bdgt_iss_date => p_ws_bdgt_iss_date
,p_ws_bdgt_val_last_upd_date => p_ws_bdgt_val_last_upd_date
,p_dist_bdgt_val_last_upd_date => p_dist_bdgt_val_last_upd_date
,p_rsrv_val_last_upd_date => p_rsrv_val_last_upd_date
,p_ws_bdgt_val_last_upd_by => p_ws_bdgt_val_last_upd_by
,p_dist_bdgt_val_last_upd_by => p_dist_bdgt_val_last_upd_by
,p_rsrv_val_last_upd_by => p_rsrv_val_last_upd_by
,p_submit_cd => p_submit_cd
,p_submit_date => p_submit_date
,p_submit_comments => p_submit_comments
,p_object_version_number => l_object_version_number
);
(p_module_name => 'update_group_budget'
,p_hook_type => 'AP'
);
update_group_budget_summary
(p_grp_bdgt_old => l_grp_bdgt_old
,p_dist_bdgt_val => p_dist_bdgt_val
,p_dist_bdgt_iss_val => p_dist_bdgt_iss_val
,p_ws_bdgt_val => p_ws_bdgt_val
,p_ws_bdgt_iss_val => p_ws_bdgt_iss_val);
rollback to update_group_budget;
rollback to update_group_budget;
end update_group_budget;
procedure delete_group_budget
(p_validate in boolean default false
,p_group_per_in_ler_id in number
,p_group_pl_id in number
,p_group_oipl_id in number
,p_object_version_number in out nocopy number
,p_update_summary in boolean default false
) is
--
-- Declare cursors and local variables
--
l_object_version_number number;
l_proc varchar2(72) := g_package||'delete_group_budget';
savepoint delete_group_budget;
BEN_CWB_PERSON_GROUPS_BK3.delete_group_budget_b
(p_group_per_in_ler_id => p_group_per_in_ler_id
,p_group_pl_id => p_group_pl_id
,p_group_oipl_id => p_group_oipl_id
,p_object_version_number => l_object_version_number
);
(p_module_name => 'delete_group_budget'
,p_hook_type => 'BP'
);
ben_cwb_person_groups_bk3.delete_group_budget_a
(p_group_per_in_ler_id => p_group_per_in_ler_id
,p_group_pl_id => p_group_pl_id
,p_group_oipl_id => p_group_oipl_id
,p_object_version_number => l_object_version_number
);
(p_module_name => 'delete_group_budget'
,p_hook_type => 'AP'
);
if p_update_summary then
update_group_budget_summary
(p_grp_bdgt_old => l_grp_bdgt_old
,p_dist_bdgt_val => null
,p_dist_bdgt_iss_val => null
,p_ws_bdgt_val => null
,p_ws_bdgt_iss_val => null);
rollback to delete_group_budget;
rollback to delete_group_budget;
end delete_group_budget;