[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pln.nip_acty_ref_perd_cd
,pln.pl_id
,prc.prtt_enrt_rslt_id
,prc.submitter_person_id
,prc.incrd_from_dt
,prc.incrd_to_dt
,prc.exp_incurd_dt
from ben_pl_f pln ,
ben_prtt_reimbmt_rqst_f prc
where pln.pl_id = prc.pl_id
and prc.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
and p_effective_date
between prc.effective_start_date
and prc.effective_end_date
and p_effective_date
between pln.effective_start_date
and pln.effective_end_date;
select pen.pgm_id,
pen.per_in_ler_id
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.business_group_id = p_business_group_id
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and p_incrd_from_dt <= pen.enrt_cvg_thru_dt
and p_incrd_to_dt >= pen.enrt_cvg_strt_dt
;
select abr.acty_base_rt_id ,
abr.rt_typ_cd,
abr.tx_typ_cd,
abr.acty_typ_cd,
abr.rt_mlt_cd,
abr.bnft_rt_typ_cd,
abr.dsply_on_enrt_flag,
abr.comp_lvl_fctr_id,
abr.actl_prem_id,
abr.input_value_id,
abr.element_type_id
from ben_acty_base_rt_f abr
where pl_id = p_pl_id
and acty_typ_cd like 'PRD%'
and acty_base_rt_stat_cd = 'A'
and p_effective_date between
abr.effective_start_date and
abr.effective_end_date;
select abr.acty_base_rt_id,
abr.rt_typ_cd,
abr.tx_typ_cd,
abr.acty_typ_cd,
abr.rt_mlt_cd,
abr.bnft_rt_typ_cd,
abr.dsply_on_enrt_flag,
abr.comp_lvl_fctr_id,
abr.actl_prem_id,
abr.input_value_id,
abr.element_type_id
from ben_acty_base_rt_f abr,
ben_plip_f plp
where plp.pl_id = p_pl_id
and plp.pgm_id = p_pgm_id
and p_effective_date between
plp.effective_start_date and
plp.effective_end_date
and plp.plip_id = abr.plip_id
and acty_base_rt_stat_cd = 'A'
and abr.acty_typ_cd like 'PRD%'
and p_effective_date between
abr.effective_start_date and
abr.effective_end_date;
select ccm.cvg_amt_calc_mthd_id
from ben_cvg_amt_calc_mthd_f ccm
where pl_id = p_pl_id
and p_effective_date
between ccm.effective_start_date
and ccm.effective_end_date;
select pgm.acty_ref_perd_cd
from ben_pgm_f pgm
where pgm.pgm_id = c_pgm_id
and p_effective_date
between pgm.effective_start_date
and pgm.effective_end_date;
procedure update_prtt_rmt_aprvd_pymt
(p_validate in boolean default false
,p_prtt_rmt_aprvd_fr_pymt_id in number
,p_prtt_reimbmt_rqst_id in number default hr_api.g_number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_apprvd_fr_pymt_num in number default hr_api.g_number
,p_adjmt_flag in varchar2 default hr_api.g_varchar2
,p_aprvd_fr_pymt_amt in number default hr_api.g_number
,p_pymt_stat_cd in varchar2 default hr_api.g_varchar2
,p_pymt_stat_rsn_cd in varchar2 default hr_api.g_varchar2
,p_pymt_stat_ovrdn_rsn_cd in varchar2 default hr_api.g_varchar2
,p_pymt_stat_prr_to_ovrd_cd in varchar2 default hr_api.g_varchar2
,p_business_group_id in number default hr_api.g_number
,p_element_entry_value_id in number default hr_api.g_number
,p_pry_attribute_category in varchar2 default hr_api.g_varchar2
,p_pry_attribute1 in varchar2 default hr_api.g_varchar2
,p_pry_attribute2 in varchar2 default hr_api.g_varchar2
,p_pry_attribute3 in varchar2 default hr_api.g_varchar2
,p_pry_attribute4 in varchar2 default hr_api.g_varchar2
,p_pry_attribute5 in varchar2 default hr_api.g_varchar2
,p_pry_attribute6 in varchar2 default hr_api.g_varchar2
,p_pry_attribute7 in varchar2 default hr_api.g_varchar2
,p_pry_attribute8 in varchar2 default hr_api.g_varchar2
,p_pry_attribute9 in varchar2 default hr_api.g_varchar2
,p_pry_attribute10 in varchar2 default hr_api.g_varchar2
,p_pry_attribute11 in varchar2 default hr_api.g_varchar2
,p_pry_attribute12 in varchar2 default hr_api.g_varchar2
,p_pry_attribute13 in varchar2 default hr_api.g_varchar2
,p_pry_attribute14 in varchar2 default hr_api.g_varchar2
,p_pry_attribute15 in varchar2 default hr_api.g_varchar2
,p_pry_attribute16 in varchar2 default hr_api.g_varchar2
,p_pry_attribute17 in varchar2 default hr_api.g_varchar2
,p_pry_attribute18 in varchar2 default hr_api.g_varchar2
,p_pry_attribute19 in varchar2 default hr_api.g_varchar2
,p_pry_attribute20 in varchar2 default hr_api.g_varchar2
,p_pry_attribute21 in varchar2 default hr_api.g_varchar2
,p_pry_attribute22 in varchar2 default hr_api.g_varchar2
,p_pry_attribute23 in varchar2 default hr_api.g_varchar2
,p_pry_attribute24 in varchar2 default hr_api.g_varchar2
,p_pry_attribute25 in varchar2 default hr_api.g_varchar2
,p_pry_attribute26 in varchar2 default hr_api.g_varchar2
,p_pry_attribute27 in varchar2 default hr_api.g_varchar2
,p_pry_attribute28 in varchar2 default hr_api.g_varchar2
,p_pry_attribute29 in varchar2 default hr_api.g_varchar2
,p_pry_attribute30 in varchar2 default hr_api.g_varchar2
,p_object_version_number in out nocopy number
,p_effective_date in date
,p_datetrack_mode in varchar2
) is
--
-- Declare cursors and local variables
cursor c_pry is
select * From ben_prtt_rmt_aprvd_fr_pymt_f pry
where p_effective_date between pry.effective_start_date
and pry.effective_end_date
and pry.prtt_rmt_aprvd_fr_pymt_id = p_prtt_rmt_aprvd_fr_pymt_id;
select abr.input_value_id,
abr.element_type_id
from ben_acty_base_rt_f abr
where acty_base_rt_id = p_acty_base_rt
and p_effective_date between
abr.effective_start_date and
abr.effective_end_date;
select prc.submitter_person_id,
prc.pl_id,
prc.prtt_enrt_rslt_id
from ben_prtt_reimbmt_rqst_f prc
where prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
and p_effective_date between
prc.effective_start_date and
prc.effective_end_date;
select abr.acty_base_rt_id ,
abr.rt_typ_cd,
abr.tx_typ_cd,
abr.acty_typ_cd,
abr.rt_mlt_cd,
abr.bnft_rt_typ_cd,
abr.dsply_on_enrt_flag,
abr.comp_lvl_fctr_id,
abr.actl_prem_id,
abr.input_value_id,
abr.element_type_id
from ben_acty_base_rt_f abr
where pl_id = p_pl_id
and acty_typ_cd like 'PRD%'
and acty_base_rt_stat_cd = 'A'
and p_effective_date between
abr.effective_start_date and
abr.effective_end_date;
select abr.acty_base_rt_id,
abr.rt_typ_cd,
abr.tx_typ_cd,
abr.acty_typ_cd,
abr.rt_mlt_cd,
abr.bnft_rt_typ_cd,
abr.dsply_on_enrt_flag,
abr.comp_lvl_fctr_id,
abr.actl_prem_id,
abr.input_value_id,
abr.element_type_id
from ben_acty_base_rt_f abr,
ben_plip_f plp
where plp.pl_id = p_pl_id
and plp.pgm_id = p_pgm_id
and p_effective_date between
plp.effective_start_date and
plp.effective_end_date
and plp.plip_id = abr.plip_id
and acty_base_rt_stat_cd = 'A'
and abr.acty_typ_cd like 'PRD%'
and p_effective_date between
abr.effective_start_date and
abr.effective_end_date;
select pgm_id
from ben_prtt_enrt_rslt_f pen
where pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and pen.prtt_enrt_rslt_stat_cd is null;
l_proc varchar2(72) := g_package||'update_prtt_rmt_aprvd_pymt';
savepoint update_prtt_rmt_aprvd_pymt;
ben_prtt_rmt_aprvd_pymt_bk2.update_prtt_rmt_aprvd_pymt_b
(
p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
,p_apprvd_fr_pymt_num => p_apprvd_fr_pymt_num
,p_adjmt_flag => p_adjmt_flag
,p_aprvd_fr_pymt_amt => p_aprvd_fr_pymt_amt
,p_pymt_stat_cd => p_pymt_stat_cd
,p_pymt_stat_rsn_cd => p_pymt_stat_rsn_cd
,p_pymt_stat_ovrdn_rsn_cd => p_pymt_stat_ovrdn_rsn_cd
,p_pymt_stat_prr_to_ovrd_cd => p_pymt_stat_prr_to_ovrd_cd
,p_business_group_id => p_business_group_id
,p_element_entry_value_id => p_element_entry_value_id
,p_pry_attribute_category => p_pry_attribute_category
,p_pry_attribute1 => p_pry_attribute1
,p_pry_attribute2 => p_pry_attribute2
,p_pry_attribute3 => p_pry_attribute3
,p_pry_attribute4 => p_pry_attribute4
,p_pry_attribute5 => p_pry_attribute5
,p_pry_attribute6 => p_pry_attribute6
,p_pry_attribute7 => p_pry_attribute7
,p_pry_attribute8 => p_pry_attribute8
,p_pry_attribute9 => p_pry_attribute9
,p_pry_attribute10 => p_pry_attribute10
,p_pry_attribute11 => p_pry_attribute11
,p_pry_attribute12 => p_pry_attribute12
,p_pry_attribute13 => p_pry_attribute13
,p_pry_attribute14 => p_pry_attribute14
,p_pry_attribute15 => p_pry_attribute15
,p_pry_attribute16 => p_pry_attribute16
,p_pry_attribute17 => p_pry_attribute17
,p_pry_attribute18 => p_pry_attribute18
,p_pry_attribute19 => p_pry_attribute19
,p_pry_attribute20 => p_pry_attribute20
,p_pry_attribute21 => p_pry_attribute21
,p_pry_attribute22 => p_pry_attribute22
,p_pry_attribute23 => p_pry_attribute23
,p_pry_attribute24 => p_pry_attribute24
,p_pry_attribute25 => p_pry_attribute25
,p_pry_attribute26 => p_pry_attribute26
,p_pry_attribute27 => p_pry_attribute27
,p_pry_attribute28 => p_pry_attribute28
,p_pry_attribute29 => p_pry_attribute29
,p_pry_attribute30 => p_pry_attribute30
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => l_datetrack_mode --Bug 5558175
);
(p_module_name => 'UPDATE_prtt_rmt_aprvd_pymt'
,p_hook_type => 'BP'
);
ben_prtt_rmt_aprvd_pymt_bk2.update_prtt_rmt_aprvd_pymt_a
(
p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
,p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_apprvd_fr_pymt_num => p_apprvd_fr_pymt_num
,p_adjmt_flag => p_adjmt_flag
,p_aprvd_fr_pymt_amt => p_aprvd_fr_pymt_amt
,p_pymt_stat_cd => p_pymt_stat_cd
,p_pymt_stat_rsn_cd => p_pymt_stat_rsn_cd
,p_pymt_stat_ovrdn_rsn_cd => p_pymt_stat_ovrdn_rsn_cd
,p_pymt_stat_prr_to_ovrd_cd => p_pymt_stat_prr_to_ovrd_cd
,p_business_group_id => p_business_group_id
,p_element_entry_value_id => p_element_entry_value_id
,p_pry_attribute_category => p_pry_attribute_category
,p_pry_attribute1 => p_pry_attribute1
,p_pry_attribute2 => p_pry_attribute2
,p_pry_attribute3 => p_pry_attribute3
,p_pry_attribute4 => p_pry_attribute4
,p_pry_attribute5 => p_pry_attribute5
,p_pry_attribute6 => p_pry_attribute6
,p_pry_attribute7 => p_pry_attribute7
,p_pry_attribute8 => p_pry_attribute8
,p_pry_attribute9 => p_pry_attribute9
,p_pry_attribute10 => p_pry_attribute10
,p_pry_attribute11 => p_pry_attribute11
,p_pry_attribute12 => p_pry_attribute12
,p_pry_attribute13 => p_pry_attribute13
,p_pry_attribute14 => p_pry_attribute14
,p_pry_attribute15 => p_pry_attribute15
,p_pry_attribute16 => p_pry_attribute16
,p_pry_attribute17 => p_pry_attribute17
,p_pry_attribute18 => p_pry_attribute18
,p_pry_attribute19 => p_pry_attribute19
,p_pry_attribute20 => p_pry_attribute20
,p_pry_attribute21 => p_pry_attribute21
,p_pry_attribute22 => p_pry_attribute22
,p_pry_attribute23 => p_pry_attribute23
,p_pry_attribute24 => p_pry_attribute24
,p_pry_attribute25 => p_pry_attribute25
,p_pry_attribute26 => p_pry_attribute26
,p_pry_attribute27 => p_pry_attribute27
,p_pry_attribute28 => p_pry_attribute28
,p_pry_attribute29 => p_pry_attribute29
,p_pry_attribute30 => p_pry_attribute30
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => l_datetrack_mode --Bug 5558175
);
(p_module_name => 'UPDATE_prtt_rmt_aprvd_pymt'
,p_hook_type => 'AP'
);
ben_prtt_rt_val_api.update_prtt_rt_val(
p_prtt_rt_val_id => l_prv_rec.prtt_rt_val_id
,p_rt_end_dt => hr_api.g_eot
,p_acty_base_rt_id => l_prv_rec.acty_base_rt_id
,p_input_value_id => l_input_value_id
,p_element_type_id => l_element_type_id
,p_person_id => l_submitter_person_id
,p_ended_per_in_ler_id => null
,p_rt_val => p_aprvd_fr_pymt_amt
,p_business_group_id => l_prv_rec.business_group_id
,p_object_version_number => l_prv_rec.object_version_number
,p_effective_date => p_effective_date
);
ROLLBACK TO update_prtt_rmt_aprvd_pymt;
ROLLBACK TO update_prtt_rmt_aprvd_pymt;
end update_prtt_rmt_aprvd_pymt;
procedure delete_prtt_rmt_aprvd_pymt
(p_validate in boolean default false
,p_prtt_rmt_aprvd_fr_pymt_id in number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_object_version_number in out nocopy number
,p_effective_date in date
,p_datetrack_mode in varchar2
) is
--
-- Declare cursors and local variables
cursor c_pry is
select * From ben_prtt_rmt_aprvd_fr_pymt_f pry
where p_effective_date between pry.effective_start_date
and pry.effective_end_date
and pry.prtt_rmt_aprvd_fr_pymt_id = p_prtt_rmt_aprvd_fr_pymt_id;
select prc.submitter_person_id
,prc.prtt_reimbmt_rqst_id
,prc.business_group_id
from ben_prtt_reimbmt_rqst_f prc ,
ben_prtt_rmt_aprvd_fr_pymt_f pyr
where prtt_rmt_aprvd_fr_pymt_id = p_prtt_rmt_aprvd_fr_pymt_id
and prc.prtt_reimbmt_rqst_id = pyr.prtt_reimbmt_rqst_id
and p_effective_date between
pyr.effective_start_date and
pyr.effective_end_date
and p_effective_date between
prc.effective_start_date and
prc.effective_end_date;
l_proc varchar2(72) := g_package||'delete_prtt_rmt_aprvd_pymt';
savepoint delete_prtt_rmt_aprvd_pymt;
ben_prtt_rmt_aprvd_pymt_bk3.delete_prtt_rmt_aprvd_pymt_b
(
p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => l_datetrack_mode --Bug 5558175
);
(p_module_name => 'DELETE_prtt_rmt_aprvd_pymt'
,p_hook_type => 'BP'
);
ben_prtt_rmt_aprvd_pymt_bk3.delete_prtt_rmt_aprvd_pymt_a
(
p_prtt_rmt_aprvd_fr_pymt_id => p_prtt_rmt_aprvd_fr_pymt_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => l_datetrack_mode --Bug 5558175
);
(p_module_name => 'DELETE_prtt_rmt_aprvd_pymt'
,p_hook_type => 'AP'
);
ben_prtt_rt_val_api.delete_prtt_rt_val(
-- p_validate => p_validate
p_prtt_rt_val_id => l_prv_rec.prtt_rt_val_id
--,p_enrt_rt_id => l_prv_rec.enrt_rt_id
,p_person_id => l_submitter_person_id
,p_business_group_id => l_prv_rec.business_group_id
,p_object_version_number => l_prv_rec.object_version_number
,p_effective_date => p_effective_date );
ROLLBACK TO delete_prtt_rmt_aprvd_pymt;
ROLLBACK TO delete_prtt_rmt_aprvd_pymt;
end delete_prtt_rmt_aprvd_pymt;