[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pen.pgm_id,
pen.pl_typ_id,
pen.ler_id,
pen.per_in_ler_id
from ben_prtt_enrt_rslt_f pen
where pen.person_id = p_submitter_person_id
and pen.pl_id = p_pl_id
and pen.business_group_id = p_business_group_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and pen.enrt_cvg_thru_dt = hr_api.g_eot;
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_prtt_reimbmt_rqst_id = prc.prtt_reimbmt_rqst_id
and p_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_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
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;
select popl_yr_perd_id
from ben_popl_yr_perd cpy,
ben_yr_perd yrp
where p_exp_incurd_dt between yrp.start_date
and nvl(cpy.PY_CLMS_THRU_DT,yrp.end_date)
and p_effective_date between yrp.start_date
and nvl(cpy.ACPT_CLM_RQSTS_THRU_DT,yrp.end_date)
and cpy.yr_perd_id = yrp.yr_perd_id
and cpy.pl_id = p_pl_id
and cpy.business_group_id = p_business_group_id
and yrp.business_group_id = p_business_group_id
order by yrp.start_date;
select pen.prtt_enrt_rslt_id,
pen.pgm_id,
pen.per_in_ler_id
from ben_prtt_enrt_rslt_f pen
where pen.pl_id = p_pl_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen.person_id = p_submitter_person_id
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
and p_exp_incurd_dt 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 abr.acty_base_rt_stat_cd = 'A'
--and p_effective_date between
and p_exp_incurd_dt between
plp.effective_start_date and
plp.effective_end_date
and plp.plip_id = abr.plip_id
and abr.acty_typ_cd like 'PRD%'
--and p_effective_date between
and p_exp_incurd_dt between
abr.effective_start_date and
abr.effective_end_date;
procedure update_PRTT_REIMBMT_RQST
(p_validate in boolean default false
,p_prtt_reimbmt_rqst_id in number
,p_effective_start_date out nocopy date
,p_effective_end_date out nocopy date
,p_incrd_from_dt in date default hr_api.g_date
,p_incrd_to_dt in date default hr_api.g_date
,p_rqst_num in number default hr_api.g_number
,p_rqst_amt in number default hr_api.g_number
,p_rqst_amt_uom in varchar2 default hr_api.g_varchar2
,p_rqst_btch_num in number default hr_api.g_number
,p_prtt_reimbmt_rqst_stat_cd in out nocopy varchar2
,p_reimbmt_ctfn_typ_prvdd_cd in varchar2 default hr_api.g_varchar2
,p_rcrrg_cd in varchar2 default hr_api.g_varchar2
,p_submitter_person_id in number default hr_api.g_number
,p_recipient_person_id in number default hr_api.g_number
,p_provider_person_id in number default hr_api.g_number
,p_provider_ssn_person_id in number default hr_api.g_number
,p_pl_id in number default hr_api.g_number
,p_gd_or_svc_typ_id in number default hr_api.g_number
,p_contact_relationship_id in number default hr_api.g_number
,p_business_group_id in number default hr_api.g_number
,p_opt_id in number default hr_api.g_number
,p_popl_yr_perd_id_1 in number default hr_api.g_number
,p_popl_yr_perd_id_2 in number default hr_api.g_number
,p_amt_year1 in number default hr_api.g_number
,p_amt_year2 in number default hr_api.g_number
,p_prc_attribute_category in varchar2 default hr_api.g_varchar2
,p_prc_attribute1 in varchar2 default hr_api.g_varchar2
,p_prc_attribute2 in varchar2 default hr_api.g_varchar2
,p_prc_attribute3 in varchar2 default hr_api.g_varchar2
,p_prc_attribute4 in varchar2 default hr_api.g_varchar2
,p_prc_attribute5 in varchar2 default hr_api.g_varchar2
,p_prc_attribute6 in varchar2 default hr_api.g_varchar2
,p_prc_attribute7 in varchar2 default hr_api.g_varchar2
,p_prc_attribute8 in varchar2 default hr_api.g_varchar2
,p_prc_attribute9 in varchar2 default hr_api.g_varchar2
,p_prc_attribute10 in varchar2 default hr_api.g_varchar2
,p_prc_attribute11 in varchar2 default hr_api.g_varchar2
,p_prc_attribute12 in varchar2 default hr_api.g_varchar2
,p_prc_attribute13 in varchar2 default hr_api.g_varchar2
,p_prc_attribute14 in varchar2 default hr_api.g_varchar2
,p_prc_attribute15 in varchar2 default hr_api.g_varchar2
,p_prc_attribute16 in varchar2 default hr_api.g_varchar2
,p_prc_attribute17 in varchar2 default hr_api.g_varchar2
,p_prc_attribute18 in varchar2 default hr_api.g_varchar2
,p_prc_attribute19 in varchar2 default hr_api.g_varchar2
,p_prc_attribute20 in varchar2 default hr_api.g_varchar2
,p_prc_attribute21 in varchar2 default hr_api.g_varchar2
,p_prc_attribute22 in varchar2 default hr_api.g_varchar2
,p_prc_attribute23 in varchar2 default hr_api.g_varchar2
,p_prc_attribute24 in varchar2 default hr_api.g_varchar2
,p_prc_attribute25 in varchar2 default hr_api.g_varchar2
,p_prc_attribute26 in varchar2 default hr_api.g_varchar2
,p_prc_attribute27 in varchar2 default hr_api.g_varchar2
,p_prc_attribute28 in varchar2 default hr_api.g_varchar2
,p_prc_attribute29 in varchar2 default hr_api.g_varchar2
,p_prc_attribute30 in varchar2 default hr_api.g_varchar2
,p_prtt_enrt_rslt_id in number default hr_api.g_number
,p_comment_id in number default hr_api.g_number
,p_object_version_number in out nocopy number
,p_effective_date in date
,p_datetrack_mode in varchar2
,p_stat_rsn_cd in out nocopy varchar2
,p_pymt_stat_cd in out nocopy varchar2
,p_pymt_stat_rsn_cd in out nocopy varchar2
,p_stat_ovrdn_flag in varchar2 default hr_api.g_varchar2
,p_stat_ovrdn_rsn_cd in varchar2 default hr_api.g_varchar2
,p_stat_prr_to_ovrd in varchar2 default hr_api.g_varchar2
,p_pymt_stat_ovrdn_flag 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 in varchar2 default hr_api.g_varchar2
,p_Adjmt_flag in varchar2 default hr_api.g_varchar2
,p_Submtd_dt in date default hr_api.g_date
,p_Ttl_rqst_amt in number default hr_api.g_number
,p_Aprvd_for_pymt_amt in out nocopy number
,p_exp_incurd_dt in date default hr_api.g_date
) is
-- DECLARE CURSORS
--- Select old information to find the status
Cursor c_prcold is
select prc.*
From ben_prtt_reimbmt_rqst_f prc
where prc.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
and p_effective_date between
prc.effective_start_date and
prc.effective_end_date;
select pry.prtt_rmt_aprvd_fr_pymt_id
, pry.object_version_number
from ben_prtt_rmt_aprvd_fr_pymt_f pry
where pry.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id;
l_proc varchar2(72) := g_package||'update_PRTT_REIMBMT_RQST';
savepoint update_PRTT_REIMBMT_RQST;
ben_PRTT_REIMBMT_RQST_bk2.update_PRTT_REIMBMT_RQST_b
(
p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
,p_incrd_from_dt => p_incrd_from_dt
,p_incrd_to_dt => p_incrd_to_dt
,p_rqst_num => p_rqst_num
,p_rqst_amt => p_rqst_amt
,p_rqst_amt_uom => p_rqst_amt_uom
,p_rqst_btch_num => p_rqst_btch_num
,p_prtt_reimbmt_rqst_stat_cd => p_prtt_reimbmt_rqst_stat_cd
,p_reimbmt_ctfn_typ_prvdd_cd => p_reimbmt_ctfn_typ_prvdd_cd
,p_rcrrg_cd => p_rcrrg_cd
,p_submitter_person_id => p_submitter_person_id
,p_recipient_person_id => p_recipient_person_id
,p_provider_person_id => p_provider_person_id
,p_provider_ssn_person_id => p_provider_ssn_person_id
,p_pl_id => p_pl_id
,p_gd_or_svc_typ_id => p_gd_or_svc_typ_id
,p_contact_relationship_id => p_contact_relationship_id
,p_business_group_id => p_business_group_id
,p_opt_id => p_opt_id
,p_popl_yr_perd_id_1 => l_popl_yr_perd_id_1
,p_popl_yr_perd_id_2 => l_popl_yr_perd_id_2
,p_amt_year1 => p_amt_year1
,p_amt_year2 => p_amt_year2
,p_prc_attribute_category => p_prc_attribute_category
,p_prc_attribute1 => p_prc_attribute1
,p_prc_attribute2 => p_prc_attribute2
,p_prc_attribute3 => p_prc_attribute3
,p_prc_attribute4 => p_prc_attribute4
,p_prc_attribute5 => p_prc_attribute5
,p_prc_attribute6 => p_prc_attribute6
,p_prc_attribute7 => p_prc_attribute7
,p_prc_attribute8 => p_prc_attribute8
,p_prc_attribute9 => p_prc_attribute9
,p_prc_attribute10 => p_prc_attribute10
,p_prc_attribute11 => p_prc_attribute11
,p_prc_attribute12 => p_prc_attribute12
,p_prc_attribute13 => p_prc_attribute13
,p_prc_attribute14 => p_prc_attribute14
,p_prc_attribute15 => p_prc_attribute15
,p_prc_attribute16 => p_prc_attribute16
,p_prc_attribute17 => p_prc_attribute17
,p_prc_attribute18 => p_prc_attribute18
,p_prc_attribute19 => p_prc_attribute19
,p_prc_attribute20 => p_prc_attribute20
,p_prc_attribute21 => p_prc_attribute21
,p_prc_attribute22 => p_prc_attribute22
,p_prc_attribute23 => p_prc_attribute23
,p_prc_attribute24 => p_prc_attribute24
,p_prc_attribute25 => p_prc_attribute25
,p_prc_attribute26 => p_prc_attribute26
,p_prc_attribute27 => p_prc_attribute27
,p_prc_attribute28 => p_prc_attribute28
,p_prc_attribute29 => p_prc_attribute29
,p_prc_attribute30 => p_prc_attribute30
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_comment_id => p_comment_id
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date)
,P_STAT_RSN_CD => P_STAT_RSN_CD
,p_Pymt_stat_cd => p_Pymt_stat_cd
,p_pymt_stat_rsn_cd => p_pymt_stat_rsn_cd
,p_stat_ovrdn_flag => p_stat_ovrdn_flag
,p_stat_ovrdn_rsn_cd => p_stat_ovrdn_rsn_cd
,p_stat_prr_to_ovrd => p_stat_prr_to_ovrd
,p_pymt_stat_ovrdn_flag => p_pymt_stat_ovrdn_flag
,p_pymt_stat_ovrdn_rsn_cd => p_pymt_stat_ovrdn_rsn_cd
,p_pymt_stat_prr_to_ovrd => p_pymt_stat_prr_to_ovrd
,p_Adjmt_flag => p_Adjmt_flag
,p_Submtd_dt => trunc(p_Submtd_dt)
,p_Ttl_rqst_amt => p_Ttl_rqst_amt
,p_Aprvd_for_pymt_amt => p_Aprvd_for_pymt_amt
,p_exp_incurd_dt => p_exp_incurd_dt
);
(p_module_name => 'UPDATE_PRTT_REIMBMT_RQST'
,p_hook_type => 'BP'
);
ben_prtt_rmt_aprvd_pymt_api.delete_prtt_rmt_aprvd_pymt
(p_prtt_rmt_aprvd_fr_pymt_id => l_prtt_rmt_aprvd_pymt_id,
p_effective_start_date => l_pry_eff_strt_dt,
p_effective_end_date => l_pry_eff_end_dt,
p_object_version_number => l_pry_ovn,
p_effective_date => p_effective_date,
p_datetrack_mode => 'ZAP'
);
ben_PRTT_REIMBMT_RQST_bk2.update_PRTT_REIMBMT_RQST_a
(
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_incrd_from_dt => p_incrd_from_dt
,p_incrd_to_dt => p_incrd_to_dt
,p_rqst_num => p_rqst_num
,p_rqst_amt => p_rqst_amt
,p_rqst_amt_uom => p_rqst_amt_uom
,p_rqst_btch_num => p_rqst_btch_num
,p_prtt_reimbmt_rqst_stat_cd => p_prtt_reimbmt_rqst_stat_cd
,p_reimbmt_ctfn_typ_prvdd_cd => p_reimbmt_ctfn_typ_prvdd_cd
,p_rcrrg_cd => p_rcrrg_cd
,p_submitter_person_id => p_submitter_person_id
,p_recipient_person_id => p_recipient_person_id
,p_provider_person_id => p_provider_person_id
,p_provider_ssn_person_id => p_provider_ssn_person_id
,p_pl_id => p_pl_id
,p_gd_or_svc_typ_id => p_gd_or_svc_typ_id
,p_contact_relationship_id => p_contact_relationship_id
,p_business_group_id => p_business_group_id
,p_opt_id => p_opt_id
,p_popl_yr_perd_id_1 => l_popl_yr_perd_id_1
,p_popl_yr_perd_id_2 => l_popl_yr_perd_id_2
,p_amt_year1 => p_amt_year1
,p_amt_year2 => p_amt_year2
,p_prc_attribute_category => p_prc_attribute_category
,p_prc_attribute1 => p_prc_attribute1
,p_prc_attribute2 => p_prc_attribute2
,p_prc_attribute3 => p_prc_attribute3
,p_prc_attribute4 => p_prc_attribute4
,p_prc_attribute5 => p_prc_attribute5
,p_prc_attribute6 => p_prc_attribute6
,p_prc_attribute7 => p_prc_attribute7
,p_prc_attribute8 => p_prc_attribute8
,p_prc_attribute9 => p_prc_attribute9
,p_prc_attribute10 => p_prc_attribute10
,p_prc_attribute11 => p_prc_attribute11
,p_prc_attribute12 => p_prc_attribute12
,p_prc_attribute13 => p_prc_attribute13
,p_prc_attribute14 => p_prc_attribute14
,p_prc_attribute15 => p_prc_attribute15
,p_prc_attribute16 => p_prc_attribute16
,p_prc_attribute17 => p_prc_attribute17
,p_prc_attribute18 => p_prc_attribute18
,p_prc_attribute19 => p_prc_attribute19
,p_prc_attribute20 => p_prc_attribute20
,p_prc_attribute21 => p_prc_attribute21
,p_prc_attribute22 => p_prc_attribute22
,p_prc_attribute23 => p_prc_attribute23
,p_prc_attribute24 => p_prc_attribute24
,p_prc_attribute25 => p_prc_attribute25
,p_prc_attribute26 => p_prc_attribute26
,p_prc_attribute27 => p_prc_attribute27
,p_prc_attribute28 => p_prc_attribute28
,p_prc_attribute29 => p_prc_attribute29
,p_prc_attribute30 => p_prc_attribute30
,p_prtt_enrt_rslt_id => p_prtt_enrt_rslt_id
,p_comment_id => p_comment_id
,p_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
,P_STAT_RSN_CD => P_STAT_RSN_CD
,p_Pymt_stat_cd => p_Pymt_stat_cd
,p_pymt_stat_rsn_cd => p_pymt_stat_rsn_cd
,p_stat_ovrdn_flag => p_stat_ovrdn_flag
,p_stat_ovrdn_rsn_cd => p_stat_ovrdn_rsn_cd
,p_stat_prr_to_ovrd => p_stat_prr_to_ovrd
,p_pymt_stat_ovrdn_flag => p_pymt_stat_ovrdn_flag
,p_pymt_stat_ovrdn_rsn_cd => p_pymt_stat_ovrdn_rsn_cd
,p_pymt_stat_prr_to_ovrd => p_pymt_stat_prr_to_ovrd
,p_Adjmt_flag => p_Adjmt_flag
,p_Submtd_dt => trunc(p_Submtd_dt)
,p_Ttl_rqst_amt => p_Ttl_rqst_amt
,p_Aprvd_for_pymt_amt => p_Aprvd_for_pymt_amt
,p_exp_incurd_dt => p_exp_incurd_dt
);
(p_module_name => 'UPDATE_PRTT_REIMBMT_RQST'
,p_hook_type => 'AP'
);
ROLLBACK TO update_PRTT_REIMBMT_RQST;
ROLLBACK TO update_PRTT_REIMBMT_RQST;
end update_PRTT_REIMBMT_RQST;
procedure delete_PRTT_REIMBMT_RQST
(p_validate in boolean default false
,p_prtt_reimbmt_rqst_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
,p_submitter_person_id in number
) is
--
-- Declare cursors and local variables
--
l_proc varchar2(72) := g_package||'delete_PRTT_REIMBMT_RQST';
savepoint delete_PRTT_REIMBMT_RQST;
ben_PRTT_REIMBMT_RQST_bk3.delete_PRTT_REIMBMT_RQST_b
(
p_prtt_reimbmt_rqst_id => p_prtt_reimbmt_rqst_id
,p_object_version_number => p_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'DELETE_PRTT_REIMBMT_RQST'
,p_hook_type => 'BP'
);
ben_PRTT_REIMBMT_RQST_bk3.delete_PRTT_REIMBMT_RQST_a
(
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_object_version_number => l_object_version_number
,p_effective_date => trunc(p_effective_date)
,p_datetrack_mode => p_datetrack_mode
);
(p_module_name => 'DELETE_PRTT_REIMBMT_RQST'
,p_hook_type => 'AP'
);
ROLLBACK TO delete_PRTT_REIMBMT_RQST;
ROLLBACK TO delete_PRTT_REIMBMT_RQST;
end delete_PRTT_REIMBMT_RQST;