[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select null
from ff_formulas_f ff ,
per_business_groups pbg
where ff.formula_id = p_auto_enrt_mthd_rl
and ff.formula_type_id = -146
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;
select null
from ff_formulas_f ff ,
per_business_groups pbg
where ff.formula_id = p_enrt_rl
and ff.formula_type_id = -393
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;
select null
from ff_formulas_f ff ,
per_business_groups pbg
where ff.formula_id = p_enrt_cvg_strt_dt_rl
and ff.formula_type_id = -29
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;
select null
from ff_formulas_f ff ,
per_business_groups pbg
where ff.formula_id = p_enrt_cvg_end_dt_rl
and ff.formula_type_id = -30
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;
select null
from ff_formulas_f ff ,
per_business_groups pbg
where ff.formula_id = p_dpnt_cvg_strt_dt_rl
and ff.formula_type_id = -27
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;
select null
from ff_formulas_f ff ,
per_business_groups pbg
where ff.formula_id = p_rt_end_dt_rl
and ff.formula_type_id = -67
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;
select null
from ff_formulas_f ff ,
per_business_groups pbg
where ff.formula_id = p_rt_strt_dt_rl
and ff.formula_type_id = -66
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;
select null
from ff_formulas_f ff ,
per_business_groups pbg
where ff.formula_id = p_dpnt_cvg_end_dt_rl
and ff.formula_type_id = -28
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;
select null
from ff_formulas_f ff ,
per_business_groups pbg
where ff.formula_id = p_prtn_end_dt_rl
and ff.formula_type_id = -83
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;
select null
from ff_formulas_f ff ,
per_business_groups pbg
where ff.formula_id = p_prtn_strt_dt_rl
and ff.formula_type_id = -82
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 lookup_code
from hr_lookups
where lookup_type = 'BEN_STAT'
and p_effective_date between start_date_active
and end_date_active
and enabled_flag = 'Y';
cursor c1 is select null
from ben_pgm_f
Where pgm_id <> nvl(p_pgm_id,-1)
and name = p_name
and business_group_id = p_business_group_id;
cursor c1 is select null
from ben_prtn_elig_f prtn
,ben_prtn_elig_prfl_f prfl
where prtn.pgm_id = p_pgm_id
and prtn.business_group_id = p_business_group_id
and p_effective_date between prtn.effective_start_date
and prtn.effective_end_date
and prtn.prtn_elig_id = prfl.prtn_elig_id
and prfl.business_group_id = p_business_group_id
and p_effective_date between prfl.effective_start_date
and prfl.effective_end_date;
cursor c2 is select null
from ben_prtn_elig_f prtn
,ben_eligy_rl_f rl
where prtn.pgm_id = p_pgm_id
and prtn.business_group_id = p_business_group_id
and p_effective_date between prtn.effective_start_date
and prtn.effective_end_date
and prtn.prtn_elig_id = rl.prtn_elig_id
and rl.business_group_id = p_business_group_id
and p_effective_date between rl.effective_start_date
and rl.effective_end_date;
cursor c1 is select currency_code
from fnd_currencies_vl
where currency_code = p_pgm_uom
and enabled_flag = 'Y';
select null
from ff_formulas_f ff,
per_business_groups pbg
where ff.formula_id = p_rule
and ff.formula_type_id = p_rule_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;
p_Update_salary_cd in varchar2,
p_Scores_calc_mthd_cd in varchar2,
p_salary_calc_mthd_cd in varchar2,
p_effective_date in date,
p_object_version_number in number) is
--
l_proc varchar2(72) := g_package||'chk_lookup_codes';
and p_Update_salary_cd
<> nvl(ben_pgm_shd.g_old_rec.Update_salary_cd,hr_api.g_varchar2)
or not l_api_updating) then
--
-- check if value of lookup falls within lookup type.
--
--
if p_Update_salary_cd is not null then
if hr_api.not_exists_in_hr_lookups
(p_lookup_type => 'PQH_GSP_SAL_UPD_MTHD',
p_lookup_code => p_Update_salary_cd,
p_effective_date => p_effective_date) then
--
-- raise error as does not exist as lookup
--
fnd_message.set_name('BEN','BEN_91628_LOOKUP_TYPE_GENERIC');
fnd_message.set_token('VALUE', p_Update_salary_cd);
fnd_message.set_token('FIELD', 'Update_salary_cd');
cursor c1 is select null
from ben_plip_f cpp
where cpp.pgm_id = p_pgm_id
and dflt_enrt_mthd_cd = 'A'
and cpp.business_group_id +0 = p_business_group_id
and p_effective_date between cpp.effective_start_date
and cpp.effective_end_date;
cursor c1 is select null
from ben_plip_f cpp
where cpp.pgm_id = p_pgm_id
and (cpp.dflt_enrt_mthd_cd is not null or
cpp.dflt_enrt_det_rl is not null or
dflt_flag = 'Y')
and cpp.business_group_id +0 = p_business_group_id
and p_effective_date between cpp.effective_start_date
and cpp.effective_end_date;
cursor c1 is select null
from ben_lee_rsn_f len,
ben_popl_enrt_typ_cycl_f pet
where pet.pgm_id = p_pgm_id
and len.popl_enrt_typ_cycl_id = pet.popl_enrt_typ_cycl_id
and pet.business_group_id +0 = p_business_group_id
and len.business_group_id +0 = p_business_group_id
and p_effective_date between pet.effective_start_date
and pet.effective_end_date
and p_effective_date between len.effective_start_date
and len.effective_end_date
and len.dys_aftr_end_to_dflt_num is not null;
cursor c1 is select 'x'
from ben_ler_chg_dpnt_cvg_f ldc
where ldc.pgm_id = p_pgm_id
and ldc.business_group_id + 0 = p_business_group_id
and p_effective_date between ldc.effective_start_date
and ldc.effective_end_date;
cursor c2 is select 'x'
from ben_apld_dpnt_cvg_elig_prfl_f ade
where ade.pgm_id = p_pgm_id
and ade.business_group_id + 0 = p_business_group_id
and p_effective_date between ade.effective_start_date
and ade.effective_end_date;
cursor c3 is select 'x'
from ben_pgm_dpnt_cvg_ctfn_f pgc
where pgc.pgm_id = p_pgm_id
and pgc.business_group_id + 0 = p_business_group_id
and p_effective_date between pgc.effective_start_date
and pgc.effective_end_date;
fnd_message.set_name('BEN','BEN_92519_DELETE_LDC1');
fnd_message.set_name('BEN','BEN_92520_DELETE_ADE1');
fnd_message.set_name('BEN','BEN_92521_DELETE_PGC');
cursor c1 is select null
from ben_ler_chg_dpnt_cvg_f ldc
where ldc.pgm_id = p_pgm_id
and ldc.business_group_id + 0 = p_business_group_id
and p_effective_date between ldc.effective_start_date
and ldc.effective_end_date;
cursor c2 is select null
from ben_apld_dpnt_cvg_elig_prfl_f ade
where ade.pgm_id = p_pgm_id
and ade.business_group_id + 0 = p_business_group_id
and p_effective_date between ade.effective_start_date
and ade.effective_end_date;
cursor c3 is select 'x'
from ben_pgm_dpnt_cvg_ctfn_f pgc
where pgc.pgm_id = p_pgm_id
and pgc.business_group_id + 0 = p_business_group_id
and p_effective_date between pgc.effective_start_date
and pgc.effective_end_date;
cursor c1 is select null
from ben_ptip_f ctp
where ctp.pgm_id = p_pgm_id
and (ctp.dpnt_dsgn_cd is not null or
ctp.drvd_fctr_dpnt_cvg_flag = 'Y' or
ctp.dpnt_dsgn_no_cvg_ctfn_rqd = 'Y')
and ctp.business_group_id + 0 = p_business_group_id
and p_effective_date between ctp.effective_start_date
and ctp.effective_end_date;
cursor c1 is select null
from BEN_PTIP_F ctp,
BEN_LER_CHG_DPNT_CVG_F ldc
where ctp.pgm_id = p_pgm_id
and ctp.business_group_id +0 = p_business_group_id
and p_effective_date between ctp.effective_start_date
and ctp.effective_end_date
and ldc.ptip_id = ctp.ptip_id
and ldc.business_group_id +0 = p_business_group_id
and p_effective_date between ldc.effective_start_date
and ldc.effective_end_date;
cursor c2 is select null
from BEN_PTIP_F ctp,
BEN_APLD_DPNT_CVG_ELIG_PRFL_F ade
where ctp.pgm_id = p_pgm_id
and ctp.business_group_id +0 = p_business_group_id
and p_effective_date between ctp.effective_start_date
and ctp.effective_end_date
and ade.ptip_id = ctp.ptip_id
and ade.business_group_id +0 = p_business_group_id
and p_effective_date between ade.effective_start_date
and ade.effective_end_date;
cursor c3 is select null
from BEN_PTIP_F ctp,
BEN_PTIP_DPNT_CVG_CTFN_F pyd
where ctp.pgm_id = p_pgm_id
and ctp.business_group_id +0 = p_business_group_id
and p_effective_date between ctp.effective_start_date
and ctp.effective_end_date
and pyd.ptip_id = ctp.ptip_id
and pyd.business_group_id +0 = p_business_group_id
and p_effective_date between pyd.effective_start_date
and pyd.effective_end_date;
Procedure dt_update_validate
(p_dpnt_cvg_strt_dt_rl in number default hr_api.g_number,
p_dpnt_cvg_end_dt_rl in number default hr_api.g_number,
p_datetrack_mode in varchar2,
p_enrt_cvg_strt_dt_rl in number,
p_enrt_cvg_end_dt_rl in number,
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_pgm_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';
select 1
from ben_popl_yr_perd t
where t.pl_id = p_pgm_id ;
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);
End dt_delete_validate;
Procedure insert_validate
(p_rec in ben_pgm_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';
p_Update_salary_cd => p_rec.Update_salary_cd,
p_Scores_calc_mthd_cd => p_rec.Scores_calc_mthd_cd,
p_salary_calc_mthd_cd => p_rec.salary_calc_mthd_cd,
p_effective_date => p_effective_date,
p_object_version_number => p_rec.object_version_number);
End insert_validate;
Procedure update_validate
(p_rec in ben_pgm_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';
p_Update_salary_cd => p_rec.Update_salary_cd,
p_Scores_calc_mthd_cd => p_rec.Scores_calc_mthd_cd,
p_salary_calc_mthd_cd => p_rec.salary_calc_mthd_cd,
p_effective_date => p_effective_date,
p_object_version_number => p_rec.object_version_number);
dt_update_validate
(p_dpnt_cvg_strt_dt_rl => p_rec.dpnt_cvg_strt_dt_rl,
p_dpnt_cvg_end_dt_rl => p_rec.dpnt_cvg_end_dt_rl,
p_datetrack_mode => p_datetrack_mode,
p_enrt_cvg_strt_dt_rl => p_rec.enrt_cvg_strt_dt_rl,
p_enrt_cvg_end_dt_rl => p_rec.enrt_cvg_end_dt_rl,
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_pgm_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_pgm_id => p_rec.pgm_id);
End delete_validate;
select a.legislation_code
from per_business_groups a,
ben_pgm_f b
where b.pgm_id = p_pgm_id
and a.business_group_id = b.business_group_id;