[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
cursor c1 is select null
from ben_acty_base_rt_f
Where acty_base_rt_id <> nvl(p_acty_base_rt_id,-1)
and name = p_name
and business_group_id = p_business_group_id;
select opt_id, pl_id
from ben_oipl_f
where oipl_id = p_rec.oipl_id
and p_effective_date between effective_start_date and effective_end_date;
select opt_typ_cd
from ben_pl_typ_f ptp,
ben_pl_f pln
where pln.pl_id = p_pl_id
and ptp.pl_typ_id = pln.pl_typ_id;
select null
from ben_acty_base_rt_f abr
where abr.element_type_id = p_element_type_id
and abr.acty_base_rt_id <> nvl(p_acty_base_rt_id,-1)
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.pl_id IN (
select pl_id from ben_pl_f pln, ben_pl_typ_f typ
where typ.opt_typ_cd ='ICM'
and typ.pl_typ_id = pln.pl_typ_id
and pln.pl_typ_id IN (
select pl_typ_id from ben_pl_f where pl_id IN
(select pl_id
from ben_acty_base_rt_f
where business_group_id = p_business_group_id
and element_type_id = p_element_type_id)));
select null
from ben_acty_base_rt_f abr
where abr.pl_id = nvl(p_pl_id,-1)
and abr.acty_base_rt_id <> nvl(p_acty_base_rt_id,-1)
and abr.business_group_id = p_business_group_id
and p_effective_date between abr.effective_start_date and abr.effective_end_date;
select null
from ben_acty_base_rt_f abr
where abr.oipl_id = nvl(p_oipl_id,-1)
and abr.acty_base_rt_id <> nvl(p_acty_base_rt_id,-1)
and abr.business_group_id = p_business_group_id
and p_effective_date between abr.effective_start_date and abr.effective_end_date;
select 1
from ben_acty_base_rt_f abr
where abr.acty_base_rt_id <> nvl(p_acty_base_rt_id,-9999)
and (
nvl(abr.pl_id,-1) = nvl(p_pl_id,-99999)
or nvl(abr.plip_id,-1) = nvl(p_plip_id,-99999)
or nvl(abr.oipl_id,-1) = nvl(p_oipl_id,-99999)
or nvl(abr.opt_id,-1) = nvl(p_opt_id,-99999)
or nvl(abr.oiplip_id,-1) = nvl(p_oiplip_id,-99999)
)
and abr.ordr_num = p_ordr_num
and abr.business_group_id = p_business_group_id
and p_effective_date between abr.effective_start_date and abr.effective_end_date;
select opt_dsply_fmt_cd
from ben_pl_typ_v plt,
ben_pl_v pln
where pln.pl_id = p_pl_id
and plt.pl_typ_id = pln.pl_typ_id;
select pl_id
from ben_plip_f
where plip_id = p_plip_id;
select pl_id
from ben_oipl_f
where oipl_id = p_oipl_id;
select oipl.pl_id
from ben_oiplip_f oiplip,
ben_oipl_f oipl
where oiplip.oiplip_id = p_oiplip_id
and oipl.oipl_id = oiplip.oipl_id;
cursor c1 is select null
from ben_extra_input_values
Where acty_base_rt_id = nvl(p_acty_base_rt_id,-1);
cursor c1 is select name
from ben_acty_base_rt_f
Where acty_base_rt_id <> nvl(p_acty_base_rt_id,-1)
and pl_id = p_pl_id
and (p_effective_start_date between effective_start_date and effective_end_date
or p_effective_start_date < effective_start_date ) ;
select '1' from BEN_PL_F PLN where pl_id = p_pl_id and
p_effective_start_date between effective_start_date and effective_end_date
and PLN.imptd_incm_calc_cd in ('PRTT','DCA','DPNT','SPS');
cursor c_cvg is select 'x' from ben_cvg_amt_calc_mthd_f
where pl_id = p_pl_id and
entr_val_at_enrt_flag = 'Y' and
p_effective_Date between effective_start_date and effective_end_Date ;
cursor c_cvg is select 'x' from ben_cvg_amt_calc_mthd_f
where oipl_id = p_oipl_id and
entr_val_at_enrt_flag = 'Y' and
p_effective_Date between effective_start_date and effective_end_Date ;
cursor c_oipl is select pl_id from
ben_plip_f where plip_id = p_plip_id and
p_effective_date between effective_start_date and effective_end_date ;
cursor c_oiplip is select oipl_id from
ben_oiplip_f where oiplip_id = p_oiplip_id and
p_effective_date between effective_start_date and effective_end_date ;
select opt_id, pl_id
from ben_oipl_f
where oipl_id = p_rec.oipl_id
and p_effective_date between effective_start_date and effective_end_date;
select nvl(opt_typ_cd,'ZZZ') --Bug 7042738
from ben_pl_typ_f ptp,
ben_pl_f pln
where pln.pl_id = p_pl_id
and ptp.pl_typ_id = pln.pl_typ_id;
/* As per bug update 3197632, error is no longer needed.
if p_det_pl_ytd_cntrs_cd is null then
--
fnd_message.set_name('BEN','BEN_93993_ANN_EXP_CONTRB_MDTRY');
select 'x'
from ben_pl_f pln
where pln.pl_id = p_pl_id
and pln.subj_to_imptd_incm_typ_cd is not null
and p_effective_date between pln.effective_start_date
and pln.effective_end_date ;
select 'x'
from ben_pl_f pln,
ben_oipl_f oipl
where oipl.oipl_id = p_oipl_id
and pln.pl_id = oipl.pl_id
and pln.subj_to_imptd_incm_typ_cd is not null
and p_effective_date between pln.effective_start_date
and pln.effective_end_date
and p_effective_date between oipl.effective_start_date
and oipl.effective_end_date
;
select 'x'
from ben_pl_f pln,
ben_plip_f plip
where plip.plip_id = p_plip_id
and pln.pl_id = plip.pl_id
and pln.subj_to_imptd_incm_typ_cd is not null
and p_effective_date between pln.effective_start_date
and pln.effective_end_date
and p_effective_date between plip.effective_start_date
and plip.effective_end_date
;
select 'x'
from ben_pl_f pln,
ben_oipl_f oipl,
ben_oiplip_f oiplip
where oiplip.oiplip_id = p_oiplip_id
and oipl.oipl_id = oiplip.oipl_id
and pln.pl_id = oipl.pl_id
and pln.subj_to_imptd_incm_typ_cd is not null
and p_effective_date between pln.effective_start_date
and pln.effective_end_date
and p_effective_date between oipl.effective_start_date
and oipl.effective_end_date
and p_effective_date between oiplip.effective_start_date
and oiplip.effective_end_date
;
select null
from ff_formulas_f ff
,per_business_groups pbg
where ff.formula_id = p_rule
and ff.formula_type_id = p_formula_type_id
and pbg.business_group_id = p_business_group_id
and nvl(ff.business_group_id, p_business_group_id) =
p_business_group_id
and nvl(ff.legislation_code, pbg.legislation_code) =
pbg.legislation_code
and p_effective_date
between ff.effective_start_date
and ff.effective_end_date;
cursor c1 is select null
from ben_acty_vrbl_rt_f
where acty_base_rt_id = p_acty_base_rt_id
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date
UNION select null
from ben_vrbl_rt_rl_f
where acty_base_rt_id = p_acty_base_rt_id
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date;
cursor c1 is select null
from ben_acty_base_rt_f
Where acty_base_rt_id <> nvl(p_acty_base_rt_id,-1)
and acty_typ_cd = p_acty_typ_cd
and pl_id = p_pl_id
and p_effective_start_date between effective_start_date and effective_end_date
and business_group_id = p_business_group_id;
cursor c_pln is select alws_reimbmts_flag
from ben_pl_f
where pl_id = p_pl_id
and p_effective_start_date between effective_start_date and effective_end_date
and business_group_id = p_business_group_id;
cursor c_plip is select pl.alws_reimbmts_flag
from ben_pl_f pl ,
ben_plip_f plip
where plip.plip_id = p_plip_id
and p_effective_start_date between plip.effective_start_date and plip.effective_end_date
and plip.business_group_id = p_business_group_id
and pl.pl_id = plip.pl_id
and p_effective_start_date between pl.effective_start_date and pl.effective_end_date
and pl.business_group_id = p_business_group_id;
select pl_id
from ben_oipl_f
where oipl_id = p_oipl_id
and business_group_id = p_business_group_id
and p_effective_start_date between effective_start_date and effective_end_date;
select null
from BEN_PL_TYP_F
where PL_TYP_ID in (select pl_typ_id
from BEN_PL_TYP_OPT_TYP_F
where opt_id = p_opt_id
and business_group_id = p_business_group_id
and p_effective_start_date between effective_start_date and effective_end_date)
and OPT_TYP_CD = 'ABS'
and p_effective_start_date between effective_start_date and effective_end_date
and business_group_id = p_business_group_id;
select null
from BEN_PL_TYP_F
where PL_TYP_ID = (select pl_typ_id from ben_pl_f
where pl_id = p_pl_id
and business_group_id = p_business_group_id
and p_effective_start_date between effective_start_date and effective_end_date)
and OPT_TYP_CD = 'ABS'
and p_effective_start_date between effective_start_date and effective_end_date
and business_group_id = p_business_group_id;
cursor c1 is select null
from ben_actl_prem_f
where actl_prem_id = p_actl_prem_id
and prem_asnmt_cd = 'PROC'
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date;
cursor c1 is select null
from ben_pgm_f
where pgm_id = p_pgm_id
and (pgm_typ_cd = 'FLEX' or pgm_typ_cd = 'FPC')
and business_group_id = p_business_group_id
and p_effective_date between effective_start_date
and effective_end_date;
cursor c2 is select null
from ben_pgm_f a, ben_ptip_f b
where b.ptip_id = p_ptip_id
and a.pgm_id = b.pgm_id
and (a.pgm_typ_cd = 'FLEX' or pgm_typ_cd = 'FPC')
and b.business_group_id = p_business_group_id
and p_effective_date between b.effective_start_date
and b.effective_end_date;
select null
from hr_organization_units a
where a.organization_id = p_organization_id;
select 'x'
From pay_grade_rules_f a , pay_rates b , per_spinal_points c,ben_opt_f opt
where a.grade_rule_id = p_pay_rate_grade_rule_id
and a.rate_id = b.rate_id
and a.rate_type = 'SP'
and c.spinal_point_id = a.grade_or_spinal_point_id
and c.spinal_point_id = opt.mapping_table_pk_id
and opt.mapping_table_name = 'PER_SPINAL_POINTS'
and a.business_group_id = p_business_group_id
and c.business_group_id = p_business_group_id
and b.business_group_id = p_business_group_id
and opt.business_group_id = p_business_group_id
and opt.opt_id = p_opt_id
and p_effective_date between a.effective_start_date and a.effective_end_date
and p_effective_date between opt.effective_start_date and opt.effective_end_date ;
select 'x'
From pay_grade_rules_f a , pay_rates b , per_grades c,ben_pl_f pl
where a.grade_rule_id = p_pay_rate_grade_rule_id
and a.rate_id = b.rate_id
and a.rate_type = 'G'
and c.grade_id = a.grade_or_spinal_point_id
and c.grade_id = pl.mapping_table_pk_id
and pl.mapping_table_name = 'PER_GRADES'
and a.business_group_id = p_business_group_id
and c.business_group_id = p_business_group_id
and b.business_group_id = p_business_group_id
and pl.business_group_id = p_business_group_id
and pl.pl_id = p_pl_id
and p_effective_date between a.effective_start_date and a.effective_end_date
and p_effective_date between pl.effective_start_date and pl.effective_end_date ;
select null
from fnd_currencies_tl
where currency_code = p_nnmntry_uom;
select null
from ben_oab_ele_typ a
where a.oab_element_typ_id = p_oab_element_typ_id;
select null
from ben_acty_base_rt abr
where abr.acty_base_rt_id <> nvl(p_acty_base_rt_id,-1)
and abr.acty_typ_cd = p_acty_typ_cd
and abr.pl_id = p_pl_id
and abr.business_group_id = p_business_group_id
and abr.acty_base_rt_stat_cd = 'A'
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date;
select null
from ben_acty_base_rt abr
where abr.acty_base_rt_id <> nvl(p_acty_base_rt_id,-1)
and abr.acty_typ_cd = p_acty_typ_cd
and abr.oipl_id = p_oipl_id
and abr.business_group_id = p_business_group_id
and abr.acty_base_rt_stat_cd = 'A'
and p_effective_date
between abr.effective_start_date
and abr.effective_end_date;
select 'X'
from ben_acty_base_rt_f
where pl_id = p_pl_id
and p_effective_date between effective_start_date and effective_end_date
and business_group_id = p_business_group_id
and acty_typ_cd = 'GSPSA';
select 1
from ben_acty_vrbl_rt_f
where acty_base_rt_id = p_acty_base_rt_id
and effective_start_date <= p_validation_end_date
and effective_end_date >= p_validation_start_date;
Procedure dt_update_validate
(p_prtl_mo_det_mthd_rl in number default hr_api.g_number,
p_prtl_mo_eff_dt_det_rl in number default hr_api.g_number,
p_rndg_rl in number default hr_api.g_number,
p_lwr_lmt_calc_rl in number default hr_api.g_number,
p_upr_lmt_calc_rl in number default hr_api.g_number,
p_val_calc_rl in number default hr_api.g_number,
p_vstg_for_acty_rt_id in number default hr_api.g_number,
p_actl_prem_id in number default hr_api.g_number,
p_parnt_acty_base_rt_id in number default hr_api.g_number,
p_pgm_id in number default hr_api.g_number,
p_ptip_id in number default hr_api.g_number,
p_oipl_id in number default hr_api.g_number,
p_opt_id in number default hr_api.g_number,
p_oiplip_id in number default hr_api.g_number,
p_plip_id in number default hr_api.g_number,
p_pl_id in number default hr_api.g_number,
-- bug: 5367301 start
p_element_type_id in number default hr_api.g_number,
p_input_va_calc_rl in number default hr_api.g_number,
p_element_det_rl in number default hr_api.g_number,
-- bug: 5367301 end
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) Is
--
l_proc varchar2(72) := g_package||'dt_update_validate';
End dt_update_validate;
Procedure dt_delete_validate
(p_acty_base_rt_id in number,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) Is
--
l_proc varchar2(72) := g_package||'dt_delete_validate';
If (p_datetrack_mode = 'DELETE' or
p_datetrack_mode = 'ZAP') then
--
--
-- Ensure the arguments are not null
--
hr_api.mandatory_arg_error
(p_api_name => l_proc,
p_argument => 'validation_start_date',
p_argument_value => p_validation_start_date);
If (p_datetrack_mode = 'DELETE_NEXT_CHANGE' or p_datetrack_mode = 'FUTURE_CHANGE')
and future_var_rt_recs_exist (
p_acty_base_rt_id ,
p_datetrack_mode ,
p_validation_start_date ,
p_validation_end_date )
then
l_table_name := 'ben_acty_vrbl_rt_f';
End dt_delete_validate;
Procedure insert_validate
(p_rec in ben_abr_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
l_proc varchar2(72) := g_package||'insert_validate';
End insert_validate;
Procedure update_validate
(p_rec in ben_abr_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72) := g_package||'update_validate';
dt_update_validate
(p_prtl_mo_det_mthd_rl => p_rec.prtl_mo_det_mthd_rl,
p_prtl_mo_eff_dt_det_rl => p_rec.prtl_mo_eff_dt_det_rl,
p_rndg_rl => p_rec.rndg_rl,
p_lwr_lmt_calc_rl => p_rec.lwr_lmt_calc_rl,
p_upr_lmt_calc_rl => p_rec.upr_lmt_calc_rl,
p_val_calc_rl => p_rec.val_calc_rl,
p_vstg_for_acty_rt_id => p_rec.vstg_for_acty_rt_id,
p_actl_prem_id => p_rec.actl_prem_id,
p_parnt_acty_base_rt_id => p_rec.parnt_acty_base_rt_id,
p_pgm_id => p_rec.pgm_id,
p_ptip_id => p_rec.ptip_id,
p_oipl_id => p_rec.oipl_id,
p_opt_id => p_rec.opt_id,
p_plip_id => p_rec.plip_id,
p_oiplip_id => p_rec.oiplip_id,
p_pl_id => p_rec.pl_id,
-- bug start
p_element_type_id => p_rec.element_type_id,
p_input_va_calc_rl => p_rec.input_va_calc_rl,
p_element_det_rl => p_rec.element_det_rl,
-- bug end
p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date);
End update_validate;
Procedure delete_validate
(p_rec in ben_abr_shd.g_rec_type,
p_effective_date in date,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date) is
--
l_proc varchar2(72) := g_package||'delete_validate';
dt_delete_validate
(p_datetrack_mode => p_datetrack_mode,
p_validation_start_date => p_validation_start_date,
p_validation_end_date => p_validation_end_date,
p_acty_base_rt_id => p_rec.acty_base_rt_id);
End delete_validate;
select a.legislation_code
from per_business_groups a,
ben_acty_base_rt_f b
where b.acty_base_rt_id = p_acty_base_rt_id
and a.business_group_id = b.business_group_id;