[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pil.lf_evt_ocrd_dt,
epe.pgm_id,
epe.pl_id,
epe.oipl_id,
epe.per_in_ler_id,
epe.yr_perd_id,
pel.enrt_perd_id,
pel.lee_rsn_id,
pil.business_group_id
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl pel,
ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id
and pil.business_group_id = p_business_group_id
and epe.per_in_ler_id = pil.per_in_ler_id
and epe.pgm_id = p_pgm_id
and epe.pl_id = p_pl_id
and epe.per_in_ler_id = p_per_in_ler_id
and epe.pil_elctbl_chc_popl_id = pel.pil_elctbl_chc_popl_id ;
select yrp.start_date , yrp.end_date
from ben_yr_perd yrp
where yrp.yr_perd_id = l_get_epe.yr_perd_id
and yrp.business_group_id = p_business_group_id;
select acty_base_rt_id
from ben_prtt_rt_val
where prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prtt_reimbmt_rqst_id is null ;
select name
from ben_acty_base_rt_f
where acty_base_rt_id = l_acty_base_rt_id
and p_effective_date between effective_start_date and effective_end_date ;
select abr.ptd_comp_lvl_fctr_id,
abr.clm_comp_lvl_fctr_id,
abr.det_pl_ytd_cntrs_cd,
abr.acty_base_rt_id
from ben_acty_base_rt_f abr
where acty_base_rt_id = l_acty_base_rt_id
and p_effective_date between
abr.effective_start_date and
abr.effective_end_date;
select distinct prv.acty_base_rt_id prv_rate,
abr.name abr_name,
clf.*
from ben_prtt_rt_val prv,
ben_acty_base_rt_f abr,
ben_comp_lvl_fctr clf
where prv.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and prv.acty_base_rt_id = abr.acty_base_rt_id
and abr.acty_typ_cd not like 'PRD%'
and abr.acty_typ_cd <> 'PRFRFS'
and abr.ttl_comp_lvl_fctr_id = clf.comp_lvl_fctr_id (+)
and prv.prtt_rt_val_stat_cd is null
-- and p_incrd_from_dt between --2272862
-- and p_exp_incurd_dt between
-- prv.rt_strt_dt and prv.rt_end_dt
and p_effective_date between
abr.effective_start_date and abr.effective_end_date;
select paf.assignment_id
from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.business_group_id = p_business_group_id
and paf.primary_flag = 'Y'
and paf.assignment_type <> 'C'
and paf.assignment_type = p_assignment_type
and p_effective_date
between paf.effective_start_date
and paf.effective_end_date;
select yrp.end_date
from ben_yr_perd yrp,
ben_popl_yr_perd cyp
where yrp.yr_perd_id = cyp.yr_perd_id
-- and p_incrd_from_dt between yrp.start_date and yrp.end_date -- 2278262
and p_exp_incurd_dt between yrp.start_date and yrp.end_date
and cyp.pl_id = p_pl_id;
select bnb.val
from ben_per_bnfts_bal_f bnb
where bnb.bnfts_bal_id = p_bnfts_bal_id
and bnb.person_id = p_person_id
and bnb.business_group_id = p_business_group_id
and p_effective_date
between bnb.effective_start_date
and bnb.effective_end_date;
/*cursor c1 is select sum(nvl(prc.aprvd_for_pymt_amt,0))
from ben_prtt_reimbmt_rqst_f prc,
ben_pl_f pl,
ben_popl_yr_perd pyr,
ben_yr_perd yr
where prc.submitter_person_id = p_person_id
and prc.prtt_reimbmt_rqst_stat_cd not in ('DND','VOIDED','DPLICT')
and pl.pl_id = p_pl_id
and pl.pl_id = prc.pl_id
and p_effective_date between pl.effective_start_date and pl.effective_end_date
and prc.effective_end_date = hr_api.g_eot --future created entry to be taken for calc
and (p_prtt_reimbmt_rqst_id is null
or prc.prtt_reimbmt_rqst_id <> p_prtt_reimbmt_rqst_id)
and pl.pl_id = pyr.pl_id
and pyr.yr_perd_id = yr.yr_perd_id
-- if the reimp belong to the current year then the
-- both condition has to match
and p_exp_incurd_dt between yr.start_date and yr.end_date
and prc.exp_incurd_dt between yr.start_date and yr.end_date
-- and p_incrd_from_dt between yr.start_date and yr.end_date -- 2272862
-- and prc.incrd_from_dt between yr.start_date and yr.end_date
and prc.business_group_id = p_business_group_id
and pl.business_group_id = p_business_group_id
and pyr.business_group_id = p_business_group_id
and yr.business_group_id = p_business_group_id;
select sum(nvl(prc.amt_year1,0))
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 prc.pl_id = p_pl_id
and prc.effective_end_date = hr_api.g_eot
and (p_prtt_reimbmt_rqst_id is null
or prc.prtt_reimbmt_rqst_id <> p_prtt_reimbmt_rqst_id)
and prc.popl_yr_perd_id_1 = p_popl_yr_perd;
select sum(nvl(prc.amt_year2,0)) --+ sum(nvl(prc.amt_year2,0))
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 prc.pl_id = p_pl_id
and prc.effective_end_date = hr_api.g_eot
and (p_prtt_reimbmt_rqst_id is null
or prc.prtt_reimbmt_rqst_id <> p_prtt_reimbmt_rqst_id)
and prc.popl_yr_perd_id_2 = p_popl_yr_perd;
select null
from ben_gd_or_svc_typ a
where a.gd_or_svc_typ_id = p_gd_or_svc_typ_id;
select null
from hr_all_organization_units a
where a.organization_id = p_provider_person_id;
select null
from per_all_people_f
where person_id = p_provider_person_id;
select 'x' from
ben_prtt_clm_gd_or_svc_typ pcg
where prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id ;
select 'x'
from fnd_currencies curr
where curr.currency_code = p_rqst_amt_uom
and curr.enabled_flag = 'Y'
and p_effective_date
between nvl(curr.start_date_active, p_effective_date)
and nvl(curr.end_date_active, p_effective_date) ;
select pen.bnft_amt
, pln.cmpr_clms_to_cvg_or_bal_cd
,pen.pgm_id
,per_in_ler_id
from ben_prtt_enrt_rslt_f pen,
ben_pl_f pln,
ben_popl_yr_perd cpy,
ben_yr_perd yrp
where pln.pl_id = p_pl_id
and pln.pl_id = pen.pl_id
and pen.person_id = p_submitter_person_id
and pln.business_group_id = p_business_group_id
and cpy.pl_id = pln.pl_id
and cpy.yr_perd_id = yrp.yr_perd_id
and cpy.popl_yr_perd_id = p_popl_yr_perd_id
and pen.enrt_cvg_strt_dt <= yrp.end_date
and pen.enrt_cvg_thru_dt >= yrp.start_date
and pen.prtt_enrt_rslt_stat_cd is null
AND pen.enrt_cvg_thru_dt >= pen.effective_start_date /* Bug 5607655 : To remove invalid records */
and pen.effective_start_date =
(select max(pen_1.effective_start_date)
from ben_prtt_enrt_rslt_f pen_1
where pen_1.person_id = pen.person_id
and pen_1.pl_id = pen.pl_id
and pen_1.prtt_enrt_rslt_stat_cd is null
and pen_1.enrt_cvg_strt_dt <= yrp.end_date
and pen_1.enrt_cvg_thru_dt >= yrp.start_date ) ;
select pln.cmpr_clms_to_cvg_or_bal_cd
from ben_pl_f pln
where pln.pl_id = p_pl_id
and pln.business_group_id = p_business_group_id
and p_effective_date between pln.effective_start_date
and pln.effective_end_date;
select 'x' from ben_prtt_reimbmt_rqst_f
where prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
and effective_start_date > p_effective_date ;
select assignment_id
,payroll_id
from per_all_assignments_f
where person_id = p_submitter_person_id
and assignment_type <> 'C'
and p_effective_Date between
effective_start_date and effective_end_date ;
select acty_base_rt_id , rt_strt_dt
from ben_prtt_rt_val
where prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id ;
select 'x'
from ben_prtt_enrt_rslt_f pen, ben_pl_f pln
where pln.pl_id = p_pl_id
and pln.pl_id = pen.pl_id
and pen.person_id = p_submitter_person_id
and pln.business_group_id = p_business_group_id
and pen.effective_start_date > p_effective_date
and p_effective_date between
pln.effective_start_date and pln.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null ;
select /*+ leading(d) use_nl(d i e h a j g b) index(h PAY_RUN_RESULTS_N50) */
max(g.end_date) end_date
from
pay_run_result_values a,
pay_element_types_f b,
pay_assignment_actions d,
pay_payroll_actions e,
per_time_periods g,
pay_run_results h,
ben_acty_base_rt_f i,
pay_input_values_f j
where d.assignment_id = p_assignment_id
and d.payroll_action_id = e.payroll_action_id
and i.input_value_id = j.input_value_id
and i.element_type_id = b.element_type_id
and i.acty_base_rt_id = p_acty_base_rt_id
and p_effective_date
between i.effective_start_date and i.effective_end_date
and i.business_group_id = p_business_group_id
and g.payroll_id = p_payroll_id
and b.element_type_id = h.element_type_id
and d.assignment_action_id = h.assignment_action_id
and e.date_earned between
g.start_date and g.end_date
and a.input_value_id = j.input_value_id
and a.run_result_id = h.run_result_id
and j.element_type_id = b.element_type_id
and p_effective_date between
b.effective_start_date and b.effective_end_date
and p_effective_date between
j.effective_start_date and j.effective_end_date;
select distinct
pen_l.enrt_cvg_strt_dt enrt_cvg_strt_dt
,nvl(pen.enrt_cvg_thru_dt,pen_l.enrt_cvg_thru_dt) enrt_cvg_thru_dt
,yrp.start_date
,yrp.end_date
,nvl(cpy.acpt_clm_rqsts_thru_dt, pen.enrt_cvg_thru_dt) acpt_clm_rqsts_thru_dt
,nvl(cpy.PY_CLMS_THRU_DT, yrp.end_date) PY_CLMS_THRU_DT
from ben_prtt_enrt_rslt_f pen,
ben_prtt_enrt_rslt_f pen_l,
ben_popl_yr_perd cpy,
ben_yr_perd yrp
where cpy.pl_id = p_pl_id
and pen.pl_id = cpy.pl_id
and pen.person_id = p_submitter_person_id
and cpy.yr_perd_id = yrp.yr_perd_id
and pen.prtt_enrt_rslt_stat_cd is null
and pen_l.prtt_enrt_rslt_stat_cd is null
and pen.effective_end_date = hr_api.g_eot
and pen_l.effective_end_date = hr_api.g_eot
and p_exp_incurd_dt >= yrp.start_date
and p_exp_incurd_dt <= nvl(cpy.PY_CLMS_THRU_DT, yrp.end_date)
and pen.pl_id = pen_l.pl_id
and pen.person_id = pen_l.person_id
and cpy.business_group_id = p_business_group_id
and yrp.business_group_id = p_business_group_id
and pen.business_group_id = p_business_group_id
-- to find the highest possible record within the year #2469785
--and pen.enrt_cvg_strt_dt <= yrp.end_date
--and pen.enrt_cvg_thru_dt >= yrp.start_date
--and pen_l.enrt_cvg_strt_dt <= yrp.end_date
--and pen_l.enrt_cvg_thru_dt >= yrp.start_date
--- effective date is not used to control
--- there is poosibility of cvg may start
--- much before effective date start
--and pen.effective_start_date <= yrp.end_date
--and pen.effective_end_date >= yrp.start_date
--and pen_l.effective_start_date <= yrp.end_date
--and pen_l.effective_end_date >= yrp.start_date
---
and pen.prtt_enrt_rslt_id =
(select max(pen2.prtt_enrt_rslt_id)
from ben_prtt_enrt_rslt_f pen2
where pen2.person_id = pen.person_id
and pen2.pl_id = pen.pl_id
and pen2.prtt_enrt_rslt_stat_cd is null
and pen2.SSPNDD_FLAG = 'N'
and pen2.enrt_cvg_strt_dt <= yrp.end_date
and pen2.enrt_cvg_thru_dt >= yrp.start_date
and pen2.effective_end_date = hr_api.g_eot
)
and pen_l.prtt_enrt_rslt_id =
(select min(pen_l2.prtt_enrt_rslt_id)
from ben_prtt_enrt_rslt_f pen_l2
where pen_l2.person_id = pen_l.person_id
and pen_l2.pl_id = pen_l.pl_id
and pen_l2.SSPNDD_FLAG = 'N'
and pen_l2.prtt_enrt_rslt_stat_cd is null
and pen_l2.enrt_cvg_strt_dt <= yrp.end_date
and pen_l2.enrt_cvg_thru_dt >= yrp.start_date
and pen_l.effective_end_date = hr_api.g_eot
)
;
select gds.name,
decode(pgs.GD_SVC_RECD_BASIS_CD, 'DATE', GD_SVC_RECD_BASIS_DT,
decode(pgs.GD_SVC_RECD_BASIS_CD, 'MOINCRDT', add_months(p_exp_incurd_dt,pgs.GD_SVC_RECD_BASIS_MO),
decode(pgs.GD_SVC_RECD_BASIS_CD, 'MOPLYRND', add_months(yrp.end_date,pgs.GD_SVC_RECD_BASIS_MO),yrp.end_date))) earliest_submit_date
from ben_prtt_enrt_rslt_f pen,
ben_popl_yr_perd cpy,
ben_yr_perd yrp,
ben_prtt_clm_gd_or_svc_typ pcg,
ben_pl_gd_or_svc_f pgs,
ben_gd_or_svc_typ gds
where pcg.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
and pcg.pl_gd_or_svc_id = pgs.pl_gd_or_svc_id
and pgs.gd_or_svc_typ_id = gds.gd_or_svc_typ_id
and cpy.pl_id = pgs.pl_id
and pen.pl_id = cpy.pl_id
and pen.person_id = p_submitter_person_id
and cpy.yr_perd_id = yrp.yr_perd_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_exp_incurd_dt >= yrp.start_date
and p_exp_incurd_dt <= nvl(cpy.PY_CLMS_THRU_DT, yrp.end_date)
and p_incrd_from_dt >= pen.enrt_cvg_strt_dt
and p_incrd_to_dt <= pen.enrt_cvg_thru_dt
and p_incrd_from_dt <= p_incrd_to_dt
and p_incrd_from_dt >= yrp.start_date
and p_incrd_to_dt > decode(pgs.GD_SVC_RECD_BASIS_CD, 'DATE', GD_SVC_RECD_BASIS_DT,
decode(pgs.GD_SVC_RECD_BASIS_CD, 'MOINCRDT', add_months(p_exp_incurd_dt,pgs.GD_SVC_RECD_BASIS_MO),
decode(pgs.GD_SVC_RECD_BASIS_CD, 'MOPLYRND', add_months(yrp.end_date,pgs.GD_SVC_RECD_BASIS_MO),yrp.end_date)))
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and p_effective_date between
pgs.effective_start_date and pgs.effective_end_date
and cpy.business_group_id = p_business_group_id
and yrp.business_group_id = p_business_group_id
and pen.business_group_id = p_business_group_id
and pcg.business_group_id = p_business_group_id
and pgs.business_group_id = p_business_group_id
and gds.business_group_id = p_business_group_id;
select nvl(cpy.acpt_clm_rqsts_thru_dt, pen.enrt_cvg_thru_dt)
from ben_prtt_enrt_rslt_f pen,
ben_popl_yr_perd cpy,
ben_yr_perd yrp
where cpy.pl_id = p_pl_id
and pen.pl_id = p_pl_id
and pen.person_id = p_submitter_person_id
and cpy.yr_perd_id = yrp.yr_perd_id
and pen.prtt_enrt_rslt_stat_cd is null
and p_incrd_from_dt >= pen.enrt_cvg_strt_dt
and p_incrd_to_dt <= pen.enrt_cvg_thru_dt
and p_incrd_from_dt <= p_incrd_to_dt
and p_incrd_from_dt >= yrp.start_date
and p_incrd_to_dt <= yrp.end_date
--and p_incrd_to_dt <= p_effective_date // allowing futur date with pndg status
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
--and p_effective_date >= yrp.start_date
and cpy.business_group_id = p_business_group_id
and yrp.business_group_id = p_business_group_id
and pen.business_group_id = p_business_group_id;
Procedure dt_update_validate
(p_pl_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_update_validate';
End dt_update_validate;
select null
from ben_prtt_rt_val a
where a.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id;
Procedure dt_delete_validate
(p_prtt_reimbmt_rqst_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 = '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 out nocopy ben_prc_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 out nocopy ben_prc_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_pl_id => p_rec.pl_id,
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_prc_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_reimbmt_rqst_id => p_rec.prtt_reimbmt_rqst_id);
End delete_validate;
select a.legislation_code
from per_business_groups a,
ben_prtt_reimbmt_rqst_f b
where b.prtt_reimbmt_rqst_id = p_prtt_reimbmt_rqst_id
and a.business_group_id = b.business_group_id;