[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select nvl(sspndd_flag,'N')
from ben_prtt_enrt_rslt_f pen
where prtt_enrt_rslt_id=p_prtt_enrt_rslt_id and
pen.prtt_enrt_rslt_stat_cd is null and
business_group_id=p_business_group_id and
p_effective_date <= effective_end_date
order by effective_Start_Date;
select processing_type
from pay_element_types_f
where element_type_id = l_element_type_id
and p_effective_date between effective_start_date
and effective_end_date ;
select name,
rcrrg_cd,
ele_rqd_flag,
element_type_id
from ben_acty_base_rt_f
where business_group_id = p_business_group_id
and p_acty_base_rt_id = acty_base_rt_id
and p_rt_strt_dt between effective_start_date
and effective_end_date ;
select rt_strt_dt,
rt_end_dt
from ben_prtt_rt_val
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and acty_base_rt_id = p_acty_base_rt_id
and prtt_rt_val_id <> nvl(p_prtt_rt_val_id,-1)
and prtt_rt_val_stat_cd is null
and ((p_new_rt_strt_dt between rt_strt_dt and rt_end_dt) or
(rt_strt_dt between p_new_rt_strt_dt and p_new_rt_end_dt));
select erc.*
from ben_enrt_rt_ctfn erc
where enrt_rt_id = p_enrt_rt_id
and business_group_id = p_business_group_id;
select null
from ben_pl_f pln,
ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.pl_id = pln.pl_id
and pln.pl_cd = 'MYNTBPGM'
and p_effective_date between pln.effective_start_date and
pln.effective_end_date ;
select name
from ben_acty_base_rt_f
where business_group_id = p_business_group_id
and p_acty_base_rt_id = acty_base_rt_id
and p_rt_strt_dt between effective_start_date
and effective_end_date ;
ben_enrollment_rate_api.update_enrollment_rate(
p_enrt_rt_id => p_enrt_rt_id,
p_prtt_rt_val_id => l_prtt_rt_val_id,
p_object_version_number => l_enrt_rt_ovn,
p_effective_date => p_effective_date
);
procedure update_prtt_rt_val
(p_validate in boolean default false
,p_prtt_rt_val_id in number
,p_person_id in number default hr_api.g_number
,p_input_value_id in number default hr_api.g_number
,p_element_type_id in number default hr_api.g_number
,p_enrt_rt_id in number default hr_api.g_number
,p_rt_strt_dt in date default hr_api.g_date
,p_rt_end_dt in date default hr_api.g_date
,p_rt_typ_cd in varchar2 default hr_api.g_varchar2
,p_tx_typ_cd in varchar2 default hr_api.g_varchar2
,p_ordr_num in number default hr_api.g_number
,p_acty_typ_cd in varchar2 default hr_api.g_varchar2
,p_mlt_cd in varchar2 default hr_api.g_varchar2
,p_acty_ref_perd_cd in varchar2 default hr_api.g_varchar2
,p_rt_val in number default hr_api.g_number
,p_ann_rt_val in number default hr_api.g_number
,p_cmcd_rt_val in number default hr_api.g_number
,p_cmcd_ref_perd_cd in varchar2 default hr_api.g_varchar2
,p_bnft_rt_typ_cd in varchar2 default hr_api.g_varchar2
,p_dsply_on_enrt_flag in varchar2 default hr_api.g_varchar2
,p_rt_ovridn_flag in varchar2 default hr_api.g_varchar2
,p_rt_ovridn_thru_dt in date default hr_api.g_date
,p_elctns_made_dt in date default hr_api.g_date
,p_prtt_rt_val_stat_cd in varchar2 default hr_api.g_varchar2
,p_prtt_enrt_rslt_id in number default hr_api.g_number
,p_cvg_amt_calc_mthd_id in number default hr_api.g_number
,p_actl_prem_id in number default hr_api.g_number
,p_comp_lvl_fctr_id in number default hr_api.g_number
,p_element_entry_value_id in number default hr_api.g_number
,p_per_in_ler_id in number default hr_api.g_number
,p_ended_per_in_ler_id in number default hr_api.g_number
,p_acty_base_rt_id in number default hr_api.g_number
,p_prtt_reimbmt_rqst_id in number default hr_api.g_number
,p_prtt_rmt_aprvd_fr_pymt_id in number default hr_api.g_number
,p_pp_in_yr_used_num in number default hr_api.g_number
,p_business_group_id in number default hr_api.g_number
,p_prv_attribute_category in varchar2 default hr_api.g_varchar2
,p_prv_attribute1 in varchar2 default hr_api.g_varchar2
,p_prv_attribute2 in varchar2 default hr_api.g_varchar2
,p_prv_attribute3 in varchar2 default hr_api.g_varchar2
,p_prv_attribute4 in varchar2 default hr_api.g_varchar2
,p_prv_attribute5 in varchar2 default hr_api.g_varchar2
,p_prv_attribute6 in varchar2 default hr_api.g_varchar2
,p_prv_attribute7 in varchar2 default hr_api.g_varchar2
,p_prv_attribute8 in varchar2 default hr_api.g_varchar2
,p_prv_attribute9 in varchar2 default hr_api.g_varchar2
,p_prv_attribute10 in varchar2 default hr_api.g_varchar2
,p_prv_attribute11 in varchar2 default hr_api.g_varchar2
,p_prv_attribute12 in varchar2 default hr_api.g_varchar2
,p_prv_attribute13 in varchar2 default hr_api.g_varchar2
,p_prv_attribute14 in varchar2 default hr_api.g_varchar2
,p_prv_attribute15 in varchar2 default hr_api.g_varchar2
,p_prv_attribute16 in varchar2 default hr_api.g_varchar2
,p_prv_attribute17 in varchar2 default hr_api.g_varchar2
,p_prv_attribute18 in varchar2 default hr_api.g_varchar2
,p_prv_attribute19 in varchar2 default hr_api.g_varchar2
,p_prv_attribute20 in varchar2 default hr_api.g_varchar2
,p_prv_attribute21 in varchar2 default hr_api.g_varchar2
,p_prv_attribute22 in varchar2 default hr_api.g_varchar2
,p_prv_attribute23 in varchar2 default hr_api.g_varchar2
,p_prv_attribute24 in varchar2 default hr_api.g_varchar2
,p_prv_attribute25 in varchar2 default hr_api.g_varchar2
,p_prv_attribute26 in varchar2 default hr_api.g_varchar2
,p_prv_attribute27 in varchar2 default hr_api.g_varchar2
,p_prv_attribute28 in varchar2 default hr_api.g_varchar2
,p_prv_attribute29 in varchar2 default hr_api.g_varchar2
,p_prv_attribute30 in varchar2 default hr_api.g_varchar2
,p_pk_id_table_name in varchar2 default hr_api.g_varchar2
,p_pk_id in number default hr_api.g_number
,p_no_end_element in boolean default false
,p_object_version_number in out nocopy number
,p_effective_date in date
) is
--
-- Declare cursors and local variables
--
--
cursor c_old_prv is
select prv.rt_end_dt,
prv.rt_strt_dt,
prv.prtt_enrt_rslt_id,
prv.element_entry_value_id,
prv.acty_base_rt_id,
prv.rt_val,
prv.cmcd_rt_val,
prv.ann_rt_val,
prv.acty_ref_perd_cd,
prv.per_in_ler_id,
prv.prtt_rt_val_stat_cd
from ben_prtt_rt_val prv
where prv.prtt_rt_val_id=p_prtt_rt_val_id and
prv.business_group_id=p_business_group_id;
select ele.element_entry_id,
ele.entry_type,
ele.original_entry_id,
elt.processing_type,
elk.element_type_id,
elk.effective_end_date
from pay_element_entry_values_f elv,
pay_element_entries_f ele,
pay_element_links_f elk,
pay_element_types_f elt
where elv.element_entry_value_id = p_element_entry_value_id
and elv.element_entry_id = ele.element_entry_id
and elv.effective_start_date between ele.effective_start_date
and ele.effective_end_date
and ele.element_link_id = elk.element_link_id
and ele.effective_start_date between elk.effective_start_date
and elk.effective_end_date
and elk.element_type_id = elt.element_type_id
and elk.effective_start_date between elt.effective_start_date
and elt.effective_end_date ;
select prv.object_version_number
from ben_prtt_rt_val prv
where prv.prtt_rt_val_id=p_prtt_rt_val_id;
l_proc varchar2(72) := g_package||'update_prtt_rt_val';
savepoint update_prtt_rt_val;
ben_prtt_rt_val_bk2.update_prtt_rt_val_b
(
p_prtt_rt_val_id => p_prtt_rt_val_id
,p_rt_strt_dt => p_rt_strt_dt
,p_rt_end_dt => l_rt_end_dt
,p_rt_typ_cd => p_rt_typ_cd
,p_tx_typ_cd => p_tx_typ_cd
,p_ordr_num => p_ordr_num
,p_acty_typ_cd => p_acty_typ_cd
,p_mlt_cd => p_mlt_cd
,p_acty_ref_perd_cd => p_acty_ref_perd_cd
,p_rt_val => p_rt_val
,p_ann_rt_val => p_ann_rt_val
,p_cmcd_rt_val => p_cmcd_rt_val
,p_cmcd_ref_perd_cd => p_cmcd_ref_perd_cd
,p_bnft_rt_typ_cd => p_bnft_rt_typ_cd
,p_dsply_on_enrt_flag => p_dsply_on_enrt_flag
,p_rt_ovridn_flag => p_rt_ovridn_flag
,p_rt_ovridn_thru_dt => p_rt_ovridn_thru_dt
,p_elctns_made_dt => p_elctns_made_dt
,p_prtt_rt_val_stat_cd => p_prtt_rt_val_stat_cd
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id
,p_actl_prem_id => p_actl_prem_id
,p_comp_lvl_fctr_id => p_comp_lvl_fctr_id
,p_element_entry_value_id => p_element_entry_value_id
,p_per_in_ler_id => p_per_in_ler_id
,p_ended_per_in_ler_id => p_ended_per_in_ler_id
,p_acty_base_rt_id => p_acty_base_rt_id
,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
,p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
,p_pp_in_yr_used_num => p_pp_in_yr_used_num
,p_business_group_id => p_business_group_id
,p_prv_attribute_category => p_prv_attribute_category
,p_prv_attribute1 => p_prv_attribute1
,p_prv_attribute2 => p_prv_attribute2
,p_prv_attribute3 => p_prv_attribute3
,p_prv_attribute4 => p_prv_attribute4
,p_prv_attribute5 => p_prv_attribute5
,p_prv_attribute6 => p_prv_attribute6
,p_prv_attribute7 => p_prv_attribute7
,p_prv_attribute8 => p_prv_attribute8
,p_prv_attribute9 => p_prv_attribute9
,p_prv_attribute10 => p_prv_attribute10
,p_prv_attribute11 => p_prv_attribute11
,p_prv_attribute12 => p_prv_attribute12
,p_prv_attribute13 => p_prv_attribute13
,p_prv_attribute14 => p_prv_attribute14
,p_prv_attribute15 => p_prv_attribute15
,p_prv_attribute16 => p_prv_attribute16
,p_prv_attribute17 => p_prv_attribute17
,p_prv_attribute18 => p_prv_attribute18
,p_prv_attribute19 => p_prv_attribute19
,p_prv_attribute20 => p_prv_attribute20
,p_prv_attribute21 => p_prv_attribute21
,p_prv_attribute22 => p_prv_attribute22
,p_prv_attribute23 => p_prv_attribute23
,p_prv_attribute24 => p_prv_attribute24
,p_prv_attribute25 => p_prv_attribute25
,p_prv_attribute26 => p_prv_attribute26
,p_prv_attribute27 => p_prv_attribute27
,p_prv_attribute28 => p_prv_attribute28
,p_prv_attribute29 => p_prv_attribute29
,p_prv_attribute30 => p_prv_attribute30
,p_pk_id_table_name => p_pk_id_table_name
,p_pk_id => p_pk_id
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date)
);
(p_module_name => 'UPDATE_prtt_rt_val'
,p_hook_type => 'BP'
);
,p_dt_delete_mode => null
,p_acty_base_rt_id => l_old_abr_id
,p_amt => l_old_rt_val
);
,p_dt_delete_mode => null
,p_acty_base_rt_id => l_old_abr_id
,p_amt => l_rt_val
);
ben_prtt_rt_val_bk2.update_prtt_rt_val_a
(
p_prtt_rt_val_id => p_prtt_rt_val_id
,p_rt_strt_dt => p_rt_strt_dt
,p_rt_end_dt => l_rt_end_dt
,p_rt_typ_cd => p_rt_typ_cd
,p_tx_typ_cd => p_tx_typ_cd
,p_ordr_num => p_ordr_num
,p_acty_typ_cd => p_acty_typ_cd
,p_mlt_cd => p_mlt_cd
,p_acty_ref_perd_cd => p_acty_ref_perd_cd
,p_rt_val => p_rt_val
,p_ann_rt_val => p_ann_rt_val
,p_cmcd_rt_val => p_cmcd_rt_val
,p_cmcd_ref_perd_cd => p_cmcd_ref_perd_cd
,p_bnft_rt_typ_cd => p_bnft_rt_typ_cd
,p_dsply_on_enrt_flag => p_dsply_on_enrt_flag
,p_rt_ovridn_flag => p_rt_ovridn_flag
,p_rt_ovridn_thru_dt => p_rt_ovridn_thru_dt
,p_elctns_made_dt => p_elctns_made_dt
,p_prtt_rt_val_stat_cd => p_prtt_rt_val_stat_cd
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_cvg_amt_calc_mthd_id => p_cvg_amt_calc_mthd_id
,p_actl_prem_id => p_actl_prem_id
,p_comp_lvl_fctr_id => p_comp_lvl_fctr_id
,p_element_entry_value_id => p_element_entry_value_id
,p_per_in_ler_id => p_per_in_ler_id
,p_ended_per_in_ler_id => p_ended_per_in_ler_id
,p_acty_base_rt_id => p_acty_base_rt_id
,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
,p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
,p_pp_in_yr_used_num => p_pp_in_yr_used_num
,p_business_group_id => p_business_group_id
,p_prv_attribute_category => p_prv_attribute_category
,p_prv_attribute1 => p_prv_attribute1
,p_prv_attribute2 => p_prv_attribute2
,p_prv_attribute3 => p_prv_attribute3
,p_prv_attribute4 => p_prv_attribute4
,p_prv_attribute5 => p_prv_attribute5
,p_prv_attribute6 => p_prv_attribute6
,p_prv_attribute7 => p_prv_attribute7
,p_prv_attribute8 => p_prv_attribute8
,p_prv_attribute9 => p_prv_attribute9
,p_prv_attribute10 => p_prv_attribute10
,p_prv_attribute11 => p_prv_attribute11
,p_prv_attribute12 => p_prv_attribute12
,p_prv_attribute13 => p_prv_attribute13
,p_prv_attribute14 => p_prv_attribute14
,p_prv_attribute15 => p_prv_attribute15
,p_prv_attribute16 => p_prv_attribute16
,p_prv_attribute17 => p_prv_attribute17
,p_prv_attribute18 => p_prv_attribute18
,p_prv_attribute19 => p_prv_attribute19
,p_prv_attribute20 => p_prv_attribute20
,p_prv_attribute21 => p_prv_attribute21
,p_prv_attribute22 => p_prv_attribute22
,p_prv_attribute23 => p_prv_attribute23
,p_prv_attribute24 => p_prv_attribute24
,p_prv_attribute25 => p_prv_attribute25
,p_prv_attribute26 => p_prv_attribute26
,p_prv_attribute27 => p_prv_attribute27
,p_prv_attribute28 => p_prv_attribute28
,p_prv_attribute29 => p_prv_attribute29
,p_prv_attribute30 => p_prv_attribute30
,p_pk_id_table_name => p_pk_id_table_name
,p_pk_id => p_pk_id
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
);
(p_module_name => 'UPDATE_prtt_rt_val'
,p_hook_type => 'AP'
);
ROLLBACK TO update_prtt_rt_val;
ROLLBACK TO update_prtt_rt_val;
end update_prtt_rt_val;
procedure delete_prtt_rt_val
(p_validate in boolean default false
,p_prtt_rt_val_id in number
,p_enrt_rt_id in number default null
,p_person_id in number
,p_business_group_id in number
,p_object_version_number in out nocopy number
,p_effective_date in date
) is
--
-- Declare cursors and local variables
--
cursor c_old_prv is
select prv.rt_end_dt,
prv.rt_strt_dt,
prv.prtt_enrt_rslt_id,
prv.element_entry_value_id,
prv.acty_base_rt_id,
prv.rt_val,
prv.acty_ref_perd_cd
from ben_prtt_rt_val prv
where prv.prtt_rt_val_id=p_prtt_rt_val_id and
prv.business_group_id=p_business_group_id;
select enrt_rt_id,
object_version_number
from ben_enrt_rt
where prtt_rt_val_id = p_prtt_rt_val_id;
l_proc varchar2(72); -- := g_package||'delete_prtt_rt_val';
l_proc := g_package||'delete_prtt_rt_val';
savepoint delete_prtt_rt_val;
ben_prtt_rt_val_bk3.delete_prtt_rt_val_b
(
p_prtt_rt_val_id => p_prtt_rt_val_id
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date)
);
(p_module_name => 'DELETE_prtt_rt_val'
,p_hook_type => 'BP'
);
ben_enrollment_rate_api.update_enrollment_rate(
p_enrt_rt_id => l_enrt_rt_id,
p_prtt_rt_val_id => null,
p_object_version_number => l_enrt_rt_ovn,
p_effective_date => p_effective_date
);
,p_dt_delete_mode => null
,p_acty_base_rt_id => l_old_abr_id
,p_amt => l_old_rt_val);
ben_prtt_rt_val_bk3.delete_prtt_rt_val_a
(
p_prtt_rt_val_id => p_prtt_rt_val_id
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
);
(p_module_name => 'DELETE_prtt_rt_val'
,p_hook_type => 'AP'
);
ROLLBACK TO delete_prtt_rt_val;
ROLLBACK TO delete_prtt_rt_val;
end delete_prtt_rt_val;