The following lines contain the word 'select', 'insert', 'update' or 'delete':
select null
from ben_eligy_prfl_f
where name = p_name
and eligy_prfl_id <> nvl(p_eligy_prfl_id, hr_api.g_number)
and business_group_id + 0 = p_business_group_id
and p_validation_start_date <= effective_end_date
and p_validation_end_date >= effective_start_date;
select 1
from per_cagr_entitlement_lines_f cagr
where cagr.eligy_prfl_id=p_eligy_prfl_id
/* and p_effective_date between cagr.effective_start_date
and cagr.effective_end_date;*/ --For date track updates it wont work.
select 1
from BEN_ELIG_OBJ_ELIG_PROFL_F eoep
where eoep.elig_prfl_id=p_eligy_prfl_id
and p_validation_start_date <= effective_end_date
and p_validation_end_date >= effective_start_date;
SELECT 'Y'
FROM ben_eligy_prfl_f
WHERE eligy_prfl_id = p_eligy_prfl_id
AND p_validation_start_date <= effective_end_date
AND p_validation_end_date >= effective_start_date
AND bnft_cagr_prtn_cd = 'LRNR';
SELECT meaning
INTO l_return
FROM fnd_lookup_values
WHERE lookup_type = 'BEN_BNFT_CAGR_PRTN'
AND lookup_code = 'LRNR'
AND language = userenv ('lang');
Procedure dt_update_validate
(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_eligy_prfl_id in number,
p_datetrack_mode in varchar2,
p_validation_start_date in date,
p_validation_end_date in date,
p_name in varchar2) 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);
End dt_delete_validate;
Procedure insert_validate
(p_rec in ben_elp_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_elp_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_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_elp_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';
SELECT elp.NAME
FROM ben_eligy_prfl_f elp
WHERE elp.eligy_prfl_id = p_rec.eligy_prfl_id
AND p_effective_date BETWEEN elp.effective_start_date
AND elp.effective_end_date;
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_eligy_prfl_id => p_rec.eligy_prfl_id,
p_name => l_elp_name);
IF (p_datetrack_mode = 'DELETE' OR p_datetrack_mode = 'ZAP')
THEN
--
IF ( p_rec.elig_enrld_plip_flag = 'Y'
OR p_rec.elig_cbr_quald_bnf_flag = 'Y'
OR p_rec.elig_enrld_ptip_flag = 'Y'
OR p_rec.elig_dpnt_cvrd_plip_flag = 'Y'
OR p_rec.elig_dpnt_cvrd_ptip_flag = 'Y'
OR p_rec.elig_dpnt_cvrd_pgm_flag = 'Y'
OR p_rec.elig_job_flag = 'Y'
OR p_rec.elig_hrly_slrd_flag = 'Y'
OR p_rec.elig_pstl_cd_flag = 'Y'
OR p_rec.elig_lbr_mmbr_flag = 'Y'
OR p_rec.elig_lgl_enty_flag = 'Y'
OR p_rec.elig_benfts_grp_flag = 'Y'
OR p_rec.elig_wk_loc_flag = 'Y'
OR p_rec.elig_brgng_unit_flag = 'Y'
OR p_rec.elig_age_flag = 'Y'
OR p_rec.elig_los_flag = 'Y'
OR p_rec.elig_per_typ_flag = 'Y'
OR p_rec.elig_fl_tm_pt_tm_flag = 'Y'
OR p_rec.elig_ee_stat_flag = 'Y'
OR p_rec.elig_grd_flag = 'Y'
OR p_rec.elig_pct_fl_tm_flag = 'Y'
OR p_rec.elig_asnt_set_flag = 'Y'
OR p_rec.elig_hrs_wkd_flag = 'Y'
OR p_rec.elig_comp_lvl_flag = 'Y'
OR p_rec.elig_org_unit_flag = 'Y'
OR p_rec.elig_loa_rsn_flag = 'Y'
OR p_rec.elig_pyrl_flag = 'Y'
OR p_rec.elig_schedd_hrs_flag = 'Y'
OR p_rec.elig_py_bss_flag = 'Y'
OR p_rec.eligy_prfl_rl_flag = 'Y'
OR p_rec.elig_cmbn_age_los_flag = 'Y'
OR p_rec.cntng_prtn_elig_prfl_flag = 'Y'
OR p_rec.elig_prtt_pl_flag = 'Y'
OR p_rec.elig_ppl_grp_flag = 'Y'
OR p_rec.elig_svc_area_flag = 'Y'
OR p_rec.elig_ptip_prte_flag = 'Y'
OR p_rec.elig_no_othr_cvg_flag = 'Y'
OR p_rec.elig_enrld_pl_flag = 'Y'
OR p_rec.elig_enrld_oipl_flag = 'Y'
OR p_rec.elig_enrld_pgm_flag = 'Y'
OR p_rec.elig_dpnt_cvrd_pl_flag = 'Y'
OR p_rec.elig_lvg_rsn_flag = 'Y'
OR p_rec.elig_optd_mdcr_flag = 'Y'
OR p_rec.elig_tbco_use_flag = 'Y'
OR p_rec.elig_dpnt_othr_ptip_flag = 'Y'
OR p_rec.elig_gndr_flag = 'Y'
OR p_rec.elig_dsblty_ctg_flag = 'Y'
OR p_rec.elig_dsblty_dgr_flag = 'Y'
OR p_rec.elig_dsblty_rsn_flag = 'Y'
OR p_rec.elig_mrtl_sts_flag = 'Y'
OR p_rec.elig_prbtn_perd_flag = 'Y'
OR p_rec.elig_sp_clng_prg_pt_flag = 'Y'
OR p_rec.elig_suppl_role_flag = 'Y'
OR p_rec.elig_qual_titl_flag = 'Y'
OR p_rec.elig_pstn_flag = 'Y'
OR p_rec.elig_dsbld_flag = 'Y'
OR p_rec.elig_ttl_cvg_vol_flag = 'Y'
OR p_rec.elig_ttl_prtt_flag = 'Y'
OR p_rec.elig_comptncy_flag = 'Y'
OR p_rec.elig_hlth_cvg_flag = 'Y'
OR p_rec.elig_anthr_pl_flag = 'Y'
OR p_rec.elig_perf_rtng_flag = 'Y'
OR p_rec.elig_qua_in_gr_flag = 'Y'
OR p_rec.elig_crit_values_flag = 'Y'
)
THEN
--
-- Bug 4057566
--
-- hr_utility.set_message (801, 'PAY_52681_BHT_CHILD_EXISTS');
End delete_validate;
select a.legislation_code
from per_business_groups a,
ben_eligy_prfl_f b
where b.eligy_prfl_id = p_eligy_prfl_id
and a.business_group_id = b.business_group_id;