The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_update boolean := true ;
if p_dml_operation = 'INSERT' then
--
l_datetrack_mode := hr_api.g_update;
l_update := true;
l_update := true;
elsif l_datetrack_mode in ('UPDATE_OVERRIDE','UPDATE' ) then
--
if p_effective_date = p_effective_start_date then
l_datetrack_mode := hr_api.g_correction ;
l_update := true;
if l_datetrack_mode in ('UPDATE_OVERRIDE') then
--
l_datetrack_mode := hr_api.g_update_override ;
l_update := false ;
elsif l_datetrack_mode in ('UPDATE') then
--
l_datetrack_mode := hr_api.g_update;
elsif l_datetrack_mode in ('UPDATE_CHANGE_INSERT') then
--
if p_effective_date = p_effective_start_date then
l_datetrack_mode := hr_api.g_correction ;
l_update := true;
l_datetrack_mode := hr_api.g_update_change_insert ;
l_update := true;
l_datetrack_mode := hr_api.g_update;
l_update := false;
if p_dml_operation = 'INSERT' then
--
l_datetrack_mode := hr_api.g_update;
l_update := false;
l_update := false;
elsif l_datetrack_mode in ('UPDATE_OVERRIDE','UPDATE' ) then
--
if p_effective_date = p_effective_start_date then
l_datetrack_mode := hr_api.g_correction ;
l_update := true;
l_datetrack_mode := hr_api.g_update ;
l_update := false ;
elsif l_datetrack_mode in ('UPDATE_CHANGE_INSERT') then
--
if p_effective_date = p_effective_start_date then
l_datetrack_mode := hr_api.g_correction ;
l_update := false;
l_datetrack_mode := hr_api.g_update ;
l_update := false;
l_datetrack_mode := hr_api.g_update;
l_update := false;
-- As of now, user can choose UPDATE or CORRECTION as the date track selection.
-- We are not allowing future data date track selections
IF (P_RATE_TYPE = hr_general.decode_lookup('BEN_MRT_RT_TYPE','STD_RT') or 'ABR' = P_RATE_TYPE) then
hr_utility.set_location('UPloading Standard Rate',15);
select 'Y' into l_future_data_exists
from
BEN_ACTY_BASE_RT_F a
where
a.acty_base_rt_id = p_rate_id
and p_effective_date between a.effective_start_date and a.effective_end_date
and a.effective_end_date < to_date('4712/12/31','YYYY/MM/DD')
and exists
( select 'Y' from BEN_ACTY_BASE_RT_F b
where b.acty_base_rt_id = a.acty_base_rt_id
and b.effective_start_date = a.effective_end_date + 1);
l_datetrack_mode := 'UPDATE';
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
,p_dml_operation => 'UPDATE' -- since we only allow updating existing rates, dml_operation is always UPDATE
,p_datetrack_mode => l_datetrack_mode);
select
pivt.input_value_id into l_input_value_id
from
pay_input_values_f_tl pivt,
pay_input_values_f piv
where
pivt.name = p_input_value_id
and piv.element_type_id = p_element_type_id
and p_effective_date between piv.effective_start_date and piv.effective_end_date
and piv.input_value_id = pivt.input_value_id
and pivt.language = userenv('LANG');
ben_acty_base_rate_api.update_acty_base_rate(
p_validate => false
,p_acty_base_rt_id => P_RATE_ID
-- ,p_acty_typ_cd => P_ACTY_TYP_CD
,p_rt_typ_cd => P_RT_TYP_CD
,p_bnft_rt_typ_cd => P_BNFT_RT_TYP_CD
-- ,p_rt_mlt_cd => P_RT_MLT_CD
,p_val => P_NEW_VAL
-- ,p_rndg_cd => P_RNDG_CD
,p_element_type_id => FND_NUMBER.canonical_to_number(P_ELEMENT_TYPE_ID)
,p_input_value_id => L_INPUT_VALUE_ID
,p_comp_lvl_fctr_id => FND_NUMBER.canonical_to_number(P_COMP_LVL_FCTR_ID)
,p_ele_entry_val_cd => P_ELE_ENTRY_VAL_CD
,p_object_version_number => l_object_version_number
,p_effective_date => P_EFFECTIVE_DATE
,p_datetrack_mode => l_datetrack_mode
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);
select 'Y' into l_future_data_exists
from
BEN_VRBL_RT_PRFL_F a
where
vrbl_rt_prfl_id = p_rate_id
and p_effective_date between a.effective_start_date and a.effective_end_date
and a.effective_end_date < to_date('4712/12/31','YYYY/MM/DD')
and exists
( select 'Y' from BEN_VRBL_RT_PRFL_F b
where b.vrbl_rt_prfl_id = a.vrbl_rt_prfl_id
and b.effective_start_date = a.effective_end_date + 1);
l_datetrack_mode := 'UPDATE';
l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
,p_dml_operation => 'UPDATE' -- since we only allow updating existing rates, dml_operation is always UPDATE
,p_datetrack_mode => l_datetrack_mode);
ben_vrbl_rate_profile_api.update_vrbl_rate_profile(
p_validate => false
,p_vrbl_rt_prfl_id => P_RATE_ID
-- ,p_acty_typ_cd => P_ACTY_TYP_CD
,p_rt_typ_cd => P_RT_TYP_CD
,p_bnft_rt_typ_cd => P_BNFT_RT_TYP_CD
-- ,p_mlt_cd => P_RT_MLT_CD
,p_val => P_NEW_VAL
-- ,p_rndg_cd => P_RNDG_CD
,p_comp_lvl_fctr_id => FND_NUMBER.canonical_to_number(P_COMP_LVL_FCTR_ID)
,p_object_version_number => l_object_version_number
,p_effective_date => P_EFFECTIVE_DATE
,p_datetrack_mode => l_datetrack_mode
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
);