[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select *
from ben_cwb_person_rates
where group_per_in_ler_id = v_group_per_in_ler_id
and pl_id = v_pl_id
and oipl_id = v_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_rates_summary(p_rates_old csr_rates%rowtype,
p_rates_new csr_rates%rowtype) 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;
select decode(ws_nnmntry_uom, null, xchg_rate, 1) ws_xchg_rt
,decode(elig_sal_nnmntry_uom, null, xchg_rate, 1) elig_sal_xchg_rt
,decode(stat_sal_nnmntry_uom, null, xchg_rate, 1) stat_sal_xchg_rt
,decode(oth_comp_nnmntry_uom, null, xchg_rate, 1) oth_comp_xchg_rt
,decode(tot_comp_nnmntry_uom, null, xchg_rate, 1) tot_comp_xchg_rt
,decode(misc1_nnmntry_uom, null, xchg_rate, 1) misc1_xchg_rt
,decode(misc2_nnmntry_uom, null, xchg_rate, 1) misc2_xchg_rt
,decode(misc3_nnmntry_uom, null, xchg_rate, 1) misc3_xchg_rt
,decode(rec_nnmntry_uom, null, xchg_rate, 1) rec_xchg_rt
,uses_bdgt_flag uses_bdgt_flag
from ben_cwb_pl_dsgn pl
,ben_cwb_xchg xchg
where pl.pl_id = p_pl_id
and pl.oipl_id = p_oipl_id
and pl.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and xchg.group_pl_id = pl.group_pl_id
and xchg.lf_evt_ocrd_dt = pl.lf_evt_ocrd_dt
and xchg.currency = p_currency;
l_proc varchar2(72) :=g_package||'update_person_rates_summary';
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_rates_old.group_pl_id
,p_group_oipl_id => p_rates_old.group_oipl_id
,p_elig_count_direct => l_elig_count_d * l_immd_mgr
,p_elig_count_all => l_elig_count_d
,p_emp_recv_count_direct => l_emp_recv_count_d * l_immd_mgr
,p_emp_recv_count_all => l_emp_recv_count_d
,p_elig_sal_val_direct => l_elig_sal_val_d * l_immd_mgr
,p_elig_sal_val_all => l_elig_sal_val_d
,p_ws_val_direct => l_ws_val_d * l_immd_mgr
,p_ws_val_all => l_ws_val_d
,p_stat_sal_val_direct => l_stat_sal_val_d * l_immd_mgr
,p_stat_sal_val_all => l_stat_sal_val_d
,p_oth_comp_val_direct => l_oth_comp_val_d * l_immd_mgr
,p_oth_comp_val_all => l_oth_comp_val_d
,p_tot_comp_val_direct => l_tot_comp_val_d * l_immd_mgr
,p_tot_comp_val_all => l_tot_comp_val_d
,p_rec_val_direct => l_rec_val_d * l_immd_mgr
,p_rec_val_all => l_rec_val_d
,p_rec_mn_val_direct => l_rec_mn_val_d * l_immd_mgr
,p_rec_mn_val_all => l_rec_mn_val_d
,p_rec_mx_val_direct => l_rec_mx_val_d * l_immd_mgr
,p_rec_mx_val_all => l_rec_mx_val_d
,p_misc1_val_direct => l_misc1_val_d * l_immd_mgr
,p_misc1_val_all => l_misc1_val_d
,p_misc2_val_direct => l_misc2_val_d * l_immd_mgr
,p_misc2_val_all => l_misc2_val_d
,p_misc3_val_direct => l_misc3_val_d * l_immd_mgr
,p_misc3_val_all => l_misc3_val_d
);
end; -- update_rates_summary
select employee_id into l_person_id
from fnd_user
where user_id = l_rates_new.last_updated_by;
hr_utility.set_location('user_id:'|| l_rates_new.last_updated_by, 24);
select meaning into l_cd_meaning_old
from hr_lookups
where lookup_type='BEN_CWB_ELIG_CRITERIA'
and lookup_code = p_rates_old.elig_flag;
select meaning into l_cd_meaning_new
from hr_lookups
where lookup_type='BEN_CWB_ELIG_CRITERIA'
and lookup_code = l_rates_new.elig_flag;
select meaning into l_cd_meaning_old
from hr_lookups
where lookup_type='BEN_INELG_RSN'
and lookup_code = p_rates_old.inelig_rsn_cd;
select meaning into l_cd_meaning_new
from hr_lookups
where lookup_type='BEN_INELG_RSN'
and lookup_code = l_rates_new.inelig_rsn_cd;
procedure update_person_rate
(p_validate in boolean default false
,p_group_per_in_ler_id in number
,p_pl_id in number
,p_oipl_id in number
,p_group_pl_id in number default hr_api.g_number
,p_group_oipl_id in number default hr_api.g_number
,p_lf_evt_ocrd_dt in date default hr_api.g_date
,p_person_id in number default hr_api.g_number
,p_assignment_id in number default hr_api.g_number
,p_elig_flag in varchar2 default hr_api.g_varchar2
,p_ws_val in number default hr_api.g_number
,p_ws_mn_val in number default hr_api.g_number
,p_ws_mx_val in number default hr_api.g_number
,p_ws_incr_val in number default hr_api.g_number
,p_elig_sal_val in number default hr_api.g_number
,p_stat_sal_val in number default hr_api.g_number
,p_oth_comp_val in number default hr_api.g_number
,p_tot_comp_val in number default hr_api.g_number
,p_misc1_val in number default hr_api.g_number
,p_misc2_val in number default hr_api.g_number
,p_misc3_val in number default hr_api.g_number
,p_rec_val in number default hr_api.g_number
,p_rec_mn_val in number default hr_api.g_number
,p_rec_mx_val in number default hr_api.g_number
,p_rec_incr_val in number default hr_api.g_number
,p_ws_val_last_upd_date in date default hr_api.g_date
,p_ws_val_last_upd_by in number default hr_api.g_number
,p_pay_proposal_id in number default hr_api.g_number
,p_element_entry_value_id in number default hr_api.g_number
,p_inelig_rsn_cd in varchar2 default hr_api.g_varchar2
,p_elig_ovrid_dt in date default hr_api.g_date
,p_elig_ovrid_person_id in number default hr_api.g_number
,p_copy_dist_bdgt_val in number default hr_api.g_number
,p_copy_ws_bdgt_val in number default hr_api.g_number
,p_copy_rsrv_val in number default hr_api.g_number
,p_copy_dist_bdgt_mn_val in number default hr_api.g_number
,p_copy_dist_bdgt_mx_val in number default hr_api.g_number
,p_copy_dist_bdgt_incr_val in number default hr_api.g_number
,p_copy_ws_bdgt_mn_val in number default hr_api.g_number
,p_copy_ws_bdgt_mx_val in number default hr_api.g_number
,p_copy_ws_bdgt_incr_val in number default hr_api.g_number
,p_copy_rsrv_mn_val in number default hr_api.g_number
,p_copy_rsrv_mx_val in number default hr_api.g_number
,p_copy_rsrv_incr_val in number default hr_api.g_number
,p_copy_dist_bdgt_iss_val in number default hr_api.g_number
,p_copy_ws_bdgt_iss_val in number default hr_api.g_number
,p_copy_dist_bdgt_iss_date in date default hr_api.g_date
,p_copy_ws_bdgt_iss_date in date default hr_api.g_date
,p_comp_posting_date in date default hr_api.g_date
,p_ws_rt_start_date in date default hr_api.g_date
,p_currency 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 ws_mn_val
,ws_mx_val
,ws_incr_val
,rec_mn_val
,rec_mx_val
,rec_incr_val
from ben_cwb_person_rates rts
where group_per_in_ler_id = p_group_per_in_ler_id
and pl_id = p_pl_id
and oipl_id = p_oipl_id;
l_proc varchar2(72) := g_package||'update_person_rate';
savepoint update_person_rate;
ben_cwb_person_rates_bk2.update_person_rate_b
(p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_pl_id
,p_oipl_id => p_oipl_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_person_id => p_person_id
,p_assignment_id => p_assignment_id
,p_elig_flag => p_elig_flag
,p_ws_val => p_ws_val
,p_ws_mn_val => p_ws_mn_val
,p_ws_mx_val => p_ws_mx_val
,p_ws_incr_val => p_ws_incr_val
,p_elig_sal_val => p_elig_sal_val
,p_stat_sal_val => p_stat_sal_val
,p_oth_comp_val => p_oth_comp_val
,p_tot_comp_val => p_tot_comp_val
,p_misc1_val => p_misc1_val
,p_misc2_val => p_misc2_val
,p_misc3_val => p_misc3_val
,p_rec_val => p_rec_val
,p_rec_mn_val => p_rec_mn_val
,p_rec_mx_val => p_rec_mx_val
,p_rec_incr_val => p_rec_incr_val
,p_ws_val_last_upd_date => p_ws_val_last_upd_date
,p_ws_val_last_upd_by => p_ws_val_last_upd_by
,p_pay_proposal_id => p_pay_proposal_id
,p_element_entry_value_id => p_element_entry_value_id
,p_inelig_rsn_cd => p_inelig_rsn_cd
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_copy_dist_bdgt_val => p_copy_dist_bdgt_val
,p_copy_ws_bdgt_val => p_copy_ws_bdgt_val
,p_copy_rsrv_val => p_copy_rsrv_val
,p_copy_dist_bdgt_mn_val => p_copy_dist_bdgt_mn_val
,p_copy_dist_bdgt_mx_val => p_copy_dist_bdgt_mx_val
,p_copy_dist_bdgt_incr_val => p_copy_dist_bdgt_incr_val
,p_copy_ws_bdgt_mn_val => p_copy_ws_bdgt_mn_val
,p_copy_ws_bdgt_mx_val => p_copy_ws_bdgt_mx_val
,p_copy_ws_bdgt_incr_val => p_copy_ws_bdgt_incr_val
,p_copy_rsrv_mn_val => p_copy_rsrv_mn_val
,p_copy_rsrv_mx_val => p_copy_rsrv_mx_val
,p_copy_rsrv_incr_val => p_copy_rsrv_incr_val
,p_copy_dist_bdgt_iss_val => p_copy_dist_bdgt_iss_val
,p_copy_ws_bdgt_iss_val => p_copy_ws_bdgt_iss_val
,p_copy_dist_bdgt_iss_date => p_copy_dist_bdgt_iss_date
,p_copy_ws_bdgt_iss_date => p_copy_ws_bdgt_iss_date
,p_comp_posting_date => p_comp_posting_date
,p_ws_rt_start_date => p_ws_rt_start_date
,p_currency => p_currency
,p_object_version_number => l_object_version_number
);
(p_module_name => 'UPDATE_PERSON_RATE'
,p_hook_type => 'BP'
);
ben_cwb_person_rates_bk2.update_person_rate_a
(p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_pl_id
,p_oipl_id => p_oipl_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_person_id => p_person_id
,p_assignment_id => p_assignment_id
,p_elig_flag => p_elig_flag
,p_ws_val => p_ws_val
,p_ws_mn_val => p_ws_mn_val
,p_ws_mx_val => p_ws_mx_val
,p_ws_incr_val => p_ws_incr_val
,p_elig_sal_val => p_elig_sal_val
,p_stat_sal_val => p_stat_sal_val
,p_oth_comp_val => p_oth_comp_val
,p_tot_comp_val => p_tot_comp_val
,p_misc1_val => p_misc1_val
,p_misc2_val => p_misc2_val
,p_misc3_val => p_misc3_val
,p_rec_val => p_rec_val
,p_rec_mn_val => p_rec_mn_val
,p_rec_mx_val => p_rec_mx_val
,p_rec_incr_val => p_rec_incr_val
,p_ws_val_last_upd_date => p_ws_val_last_upd_date
,p_ws_val_last_upd_by => p_ws_val_last_upd_by
,p_pay_proposal_id => p_pay_proposal_id
,p_element_entry_value_id => p_element_entry_value_id
,p_inelig_rsn_cd => p_inelig_rsn_cd
,p_elig_ovrid_dt => p_elig_ovrid_dt
,p_elig_ovrid_person_id => p_elig_ovrid_person_id
,p_copy_dist_bdgt_val => p_copy_dist_bdgt_val
,p_copy_ws_bdgt_val => p_copy_ws_bdgt_val
,p_copy_rsrv_val => p_copy_rsrv_val
,p_copy_dist_bdgt_mn_val => p_copy_dist_bdgt_mn_val
,p_copy_dist_bdgt_mx_val => p_copy_dist_bdgt_mx_val
,p_copy_dist_bdgt_incr_val => p_copy_dist_bdgt_incr_val
,p_copy_ws_bdgt_mn_val => p_copy_ws_bdgt_mn_val
,p_copy_ws_bdgt_mx_val => p_copy_ws_bdgt_mx_val
,p_copy_ws_bdgt_incr_val => p_copy_ws_bdgt_incr_val
,p_copy_rsrv_mn_val => p_copy_rsrv_mn_val
,p_copy_rsrv_mx_val => p_copy_rsrv_mx_val
,p_copy_rsrv_incr_val => p_copy_rsrv_incr_val
,p_copy_dist_bdgt_iss_val => p_copy_dist_bdgt_iss_val
,p_copy_ws_bdgt_iss_val => p_copy_ws_bdgt_iss_val
,p_copy_dist_bdgt_iss_date => p_copy_dist_bdgt_iss_date
,p_copy_ws_bdgt_iss_date => p_copy_ws_bdgt_iss_date
,p_comp_posting_date => p_comp_posting_date
,p_ws_rt_start_date => p_ws_rt_start_date
,p_currency => p_currency
,p_object_version_number => l_object_version_number
);
(p_module_name => 'UPDATE_PERSON_RATE'
,p_hook_type => 'AP'
);
update_rates_summary(l_rates_old,l_rates_new);
rollback to update_person_rate;
rollback to update_person_rate;
end update_person_rate;
procedure delete_person_rate
(p_validate in boolean default false
,p_group_per_in_ler_id in number
,p_pl_id in number
,p_oipl_id in number
,p_object_version_number in number
,p_update_summary in boolean default false
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'delete_person_rate';
savepoint delete_person_rate;
ben_cwb_person_rates_bk3.delete_person_rate_b
(p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_pl_id
,p_oipl_id => p_oipl_id
,p_object_version_number => p_object_version_number
);
(p_module_name => 'DELETE_PERSON_RATE'
,p_hook_type => 'BP'
);
ben_cwb_person_rates_bk3.delete_person_rate_a
(p_group_per_in_ler_id => p_group_per_in_ler_id
,p_pl_id => p_pl_id
,p_oipl_id => p_oipl_id
,p_object_version_number => p_object_version_number
);
(p_module_name => 'DELETE_PERSON_RATE'
,p_hook_type => 'AP'
);
if p_update_summary then
l_rates_new := l_rates_old;
update_rates_summary(l_rates_old,l_rates_new);
rollback to delete_person_rate;
rollback to delete_person_rate;
end delete_person_rate;