The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pbg.security_group_id
from per_business_groups pbg
, ben_prtt_rmt_aprvd_fr_pymt_f pry
where pry.prtt_rmt_aprvd_fr_pymt_id = p_prtt_rmt_aprvd_fr_pymt_id
and pbg.business_group_id = pry.business_group_id;
select pbg.legislation_code
from per_business_groups pbg
, ben_prtt_rmt_aprvd_fr_pymt_f pry
where pry.prtt_rmt_aprvd_fr_pymt_id = p_prtt_rmt_aprvd_fr_pymt_id
and pbg.business_group_id (+) = pry.business_group_id;
select sum(nvl(pry.aprvd_fr_pymt_amt,0))
from ben_prtt_rmt_aprvd_fr_pymt_f pry
where pry.effective_end_date = hr_api.g_eot --future created entry to be taken for calc
and p_rec.prtt_reimbmt_rqst_id = pry.prtt_reimbmt_rqst_id
and (p_rec.prtt_rmt_aprvd_fr_pymt_id is null
or (p_rec.prtt_rmt_aprvd_fr_pymt_id <> pry.prtt_rmt_aprvd_fr_pymt_id ) )
and pry.business_group_id = p_rec.business_group_id ;
select nvl(prc.aprvd_for_pymt_amt,prc.rqst_amt),
prc.popl_yr_perd_id_1,
prc.popl_yr_perd_id_2,
prc.amt_year1,
prc.amt_year2
from ben_prtt_reimbmt_rqst_f prc
where p_rec.prtt_reimbmt_rqst_id = prc.prtt_reimbmt_rqst_id
and p_rec.business_group_id = prc.business_group_id
and p_effective_Date between prc.effective_start_date
and prc.effective_end_date ;
select pl.pl_id
,pl.cmpr_clms_to_cvg_or_bal_cd
,prc.SUBMITTER_PERSON_ID
,prc.PRTT_ENRT_RSLT_ID
,prc.EXP_INCURD_DT
from ben_prtt_reimbmt_rqst_f prc,
ben_pl_f pl
where prc.prtt_reimbmt_rqst_id = p_rec.prtt_reimbmt_rqst_id
and pl.pl_id = prc.pl_id
and prc.EXP_INCURD_DT between pl.effective_start_date
and pl.effective_end_date ;
select sum(nvl(pry.APRVD_FR_PYMT_AMT,0))
from ben_prtt_reimbmt_rqst_f prc,
ben_prtt_rmt_aprvd_fr_pymt_f pry
where prc.submitter_person_id = p_person_id
and prc.prtt_reimbmt_rqst_stat_cd not in ('DND','VOIDED','DPLICT')
and p_pl_id = prc.pl_id
and ((prc.popl_yr_perd_id_1 = p_popl_yr_perd and
prc.amt_year2 is null) or
(prc.popl_yr_perd_id_2 = p_popl_yr_perd
and prc.amt_year1 is null))
and prc.effective_end_date = hr_api.g_eot --future created entry to be taken for calc
and prc.prtt_reimbmt_rqst_id = pry.prtt_reimbmt_rqst_id
;
select prtt_reimbmt_rqst_id,
popl_yr_perd_id_1,
popl_yr_perd_id_2,
amt_year1,
amt_year2
from ben_prtt_reimbmt_rqst_f prc
where prc.submitter_person_id = p_person_id
and prc.prtt_reimbmt_rqst_stat_cd not in ('DND','VOIDED','DPLICT')
and p_pl_id = prc.pl_id
and (( prc.popl_yr_perd_id_1 = p_popl_yr_perd and prc.amt_year2 is not null)
or (prc.popl_yr_perd_id_2 = p_popl_yr_perd and prc.amt_year1 is not null))
;
select sum(nvl(pry.APRVD_FR_PYMT_AMT,0))
from ben_prtt_rmt_aprvd_fr_pymt_f pry
where pry.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id;
select END_DATE
from ben_yr_perd yrp,
ben_popl_yr_perd cpy
where cpy.popl_yr_perd_id = p_popl_yr_perd_id
and cpy.yr_perd_id = yrp.yr_perd_id;
Procedure chk_non_updateable_args
(p_effective_date in date
,p_rec in ben_pry_shd.g_rec_type
) IS
--
l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
End chk_non_updateable_args;
Procedure dt_update_validate
(p_prtt_reimbmt_rqst_id in number default hr_api.g_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_update_validate';
End dt_update_validate;
Procedure dt_delete_validate
(p_prtt_rmt_aprvd_fr_pymt_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 = hr_api.g_delete or
p_datetrack_mode = hr_api.g_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_pry_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_pry_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_prtt_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id
,p_datetrack_mode => p_datetrack_mode
,p_validation_start_date => p_validation_start_date
,p_validation_end_date => p_validation_end_date
);
chk_non_updateable_args
(p_effective_date => p_effective_date
,p_rec => p_rec
);
End update_validate;
Procedure delete_validate
(p_rec in ben_pry_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_prtt_rmt_aprvd_fr_pymt_id => p_rec.prtt_rmt_aprvd_fr_pymt_id
);
End delete_validate;