The following lines contain the word 'select', 'insert', 'update' or 'delete':
select typ.element_type_id
,typ.effective_start_date
,typ.effective_end_date
,nvl(typtl.description,typtl.element_name) use_name
,typ.input_currency_code
,typ.processing_type
from pay_element_types_f typ
,pay_element_types_f_tl typtl
where typ.element_type_id = p_element_type_id
and p_effective_date between
typ.effective_start_date and typ.effective_end_date
and typ.element_type_id = typtl.element_type_id
and typtl.language (+) = userenv('lang');
select opt.opt_id
from ben_opt_f opt
where opt.business_group_id = p_business_group_id
and p_effective_date between
opt.effective_start_date and opt.effective_end_date
and opt.name = v_name;
select pon.pl_typ_opt_typ_id
from ben_pl_typ_opt_typ_f pon
where pon.opt_id = v_opt_id
and pon.pl_typ_id = v_pl_typ_id
and p_effective_date between
pon.effective_start_date and pon.effective_end_date;
select max(oipl.ordr_num)
from ben_oipl_f oipl
where oipl.pl_id = l_pl_id
and p_effective_date between
oipl.effective_start_date and oipl.effective_end_date;
procedure delete_elig(p_prtn_elig_id in number,
p_effective_date in date,
p_object_version_number in out nocopy number) is
cursor c_prfl is
select prfl.prtn_elig_prfl_id
,prfl.effective_start_date
,prfl.effective_end_date
,prfl.object_version_number
from ben_prtn_elig_prfl_f prfl
where prfl.prtn_elig_id = p_prtn_elig_id
and p_effective_date between
prfl.effective_start_date and prfl.effective_end_date;
ben_prtn_elig_prfl_api.delete_prtn_elig_prfl
(p_validate => false,
p_prtn_elig_prfl_id => l_prfl.prtn_elig_prfl_id,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_object_version_number => l_prfl.object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_zap);
ben_participation_elig_api.delete_participation_elig
(p_validate => false,
p_prtn_elig_id => p_prtn_elig_id,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_object_version_number => p_object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_zap);
end delete_elig;
procedure delete_setup(p_element_type_id in number
,p_business_group_id in number
,p_effective_date in date) is
l_esd date;
select abr.acty_base_rt_id
,abr.object_version_number
,abr.context_pl_id pl_id
,abr.oipl_id
,pln.pl_typ_id
from ben_acty_base_rt_f abr
,ben_pl_f pln
,ben_pl_typ_f typ
where abr.element_type_id = p_element_type_id
and abr.business_group_id = p_business_group_id
and p_effective_date between
abr.effective_start_date and abr.effective_end_date
and abr.context_pl_id = pln.pl_id
and p_effective_date between
pln.effective_start_date and pln.effective_end_date
and pln.pl_typ_id = typ.pl_typ_id
and p_effective_date between
typ.effective_start_date and typ.effective_end_date
and typ.opt_typ_cd = 'ICM';
select prtn.prtn_elig_id
,prtn.object_version_number
from ben_prtn_elig_f prtn
where prtn.oipl_id = v_oipl_id
and p_effective_date between
prtn.effective_start_date and prtn.effective_end_date;
select prtn.prtn_elig_id
,prtn.object_version_number
from ben_prtn_elig_f prtn
where prtn.pl_id = v_pl_id
and p_effective_date between
prtn.effective_start_date and prtn.effective_end_date;
select oipl.object_version_number
,oipl.opt_id
from ben_oipl_f oipl
where oipl.oipl_id = v_oipl_id
and p_effective_date between
oipl.effective_start_date and oipl.effective_end_date;
select pon.pl_typ_opt_typ_id
,pon.object_version_number
from ben_pl_typ_opt_typ_f pon
where pon.opt_id = v_opt_id
and pon.pl_typ_id = v_pl_typ_id
and p_effective_date between
pon.effective_start_date and pon.effective_end_date
and not exists
(select 'Y'
from ben_oipl_f oipl
,ben_pl_f pln
where oipl.opt_id = v_opt_id
and oipl.pl_id = pln.pl_id
and pln.pl_typ_id = v_pl_typ_id);
select opt.object_version_number
from ben_opt_f opt
where opt.opt_id = v_opt_id
and p_effective_date between
opt.effective_start_date and opt.effective_end_date
and not exists
(select 'Y'
from ben_pl_typ_opt_typ_f pon
where pon.opt_id = v_opt_id);
select pln.object_version_number
from ben_pl_f pln
where pln.pl_id = v_pl_id
and p_effective_date between
pln.effective_start_date and pln.effective_end_date
and not exists
(select 'Y'
from ben_oipl_f oipl
where oipl.pl_id = v_pl_id);
select typ.object_version_number
from ben_pl_typ_f typ
where typ.pl_typ_id = v_pl_typ_id
and p_effective_date between
typ.effective_start_date and typ.effective_end_date
and not exists
(select 'Y'
from ben_pl_f pln
where pln.pl_typ_id = v_pl_typ_id);
select abr.acty_base_rt_id
from ben_acty_base_rt_f abr
,ben_pl_f pln
,ben_pl_typ_f typ
where abr.element_type_id = p_element_type_id
and abr.context_pl_id = pln.pl_id
and pln.pl_typ_id = typ.pl_typ_id
and typ.opt_typ_cd = 'ICM';
ben_acty_base_rate_api.delete_acty_base_rate
(p_validate => false,
p_acty_base_rt_id => l_abr.acty_base_rt_id,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_object_version_number => l_abr.object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_zap);
delete_elig(p_prtn_elig_id => l_prtn_elig.prtn_elig_id,
p_effective_date => p_effective_date,
p_object_version_number => l_prtn_elig.object_version_number);
ben_option_in_plan_api.delete_option_in_plan
(p_validate => false,
p_oipl_id => l_abr.oipl_id,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_object_version_number => l_oipl.object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_zap);
ben_plan_type_option_type_api.delete_plan_type_option_type
(p_validate => false,
p_pl_typ_opt_typ_id => l_pon.pl_typ_opt_typ_id,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_object_version_number => l_pon.object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_zap);
ben_option_definition_api.delete_option_definition
(p_validate => false,
p_opt_id => l_oipl.opt_id,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_object_version_number => l_opt.object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_zap);
delete_elig(p_prtn_elig_id => l_prtn_elig.prtn_elig_id,
p_effective_date => p_effective_date,
p_object_version_number => l_prtn_elig.object_version_number);
ben_plan_api.delete_plan
(p_validate => false,
p_pl_id => l_abr.pl_id,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_object_version_number => l_pln.object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_zap);
ben_plan_type_api.delete_plan_type
(p_validate => false,
p_pl_typ_id => l_abr.pl_typ_id,
p_effective_start_date => l_esd,
p_effective_end_date => l_eed,
p_object_version_number => l_typ.object_version_number,
p_effective_date => p_effective_date,
p_datetrack_mode => hr_api.g_zap);
ben_icd_flex_field_setup.delete_icd_config
(p_element_type_id => p_element_type_id,
p_effective_date => p_effective_date);
end delete_setup;