[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select sum(b.rt_val), sum(p.screen_entry_value)
from ben_prtt_rt_val b, pay_element_entry_values_f p
where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between nvl(b.rt_strt_dt, p_effective_date)
and nvl(b.rt_end_dt, p_effective_date)
and b.tx_typ_cd = 'PRETAX'
and b.acty_typ_cd IN ('EEPLC', 'EEIC', 'EEPYC', 'PBC', 'PBC2', 'PXC')
and b.element_entry_value_id = p.element_entry_value_id (+)
and p_effective_date between nvl(p.effective_start_date, p_effective_date)
and nvl(p.effective_end_date, p_effective_date);
select sum(b.rt_val), sum(p.screen_entry_value)
from ben_prtt_rt_val b, pay_element_entry_values_f p
where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between nvl(b.rt_strt_dt, p_effective_date)
and nvl(b.rt_end_dt, p_effective_date)
and b.tx_typ_cd = 'AFTERTAX'
and b.acty_typ_cd IN ('EEPLC', 'EEIC', 'EEPYC', 'PBC', 'PBC2', 'PXC')
and b.element_entry_value_id = p.element_entry_value_id (+)
and p_effective_date between nvl(p.effective_start_date, p_effective_date)
and nvl(p.effective_end_date, p_effective_date);
select sum(b.rt_val), sum(p.screen_entry_value)
from ben_prtt_rt_val b, pay_element_entry_values_f p
where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between nvl(b.rt_strt_dt, p_effective_date)
and nvl(b.rt_end_dt, p_effective_date)
and b.acty_typ_cd IN ('EEPLC', 'EEIC', 'EEPYC', 'PBC', 'PBC2', 'PXC')
and b.element_entry_value_id = p.element_entry_value_id (+)
and p_effective_date between nvl(p.effective_start_date, p_effective_date)
and nvl(p.effective_end_date, p_effective_date);
select sum(b.rt_val), sum(p.screen_entry_value)
from ben_prtt_rt_val b, pay_element_entry_values_f p
where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between nvl(b.rt_strt_dt, p_effective_date)
and nvl(b.rt_end_dt, p_effective_date)
and b.acty_typ_cd IN ('ERPYC', 'ERMPLC', 'ERC')
and b.element_entry_value_id = p.element_entry_value_id (+)
and p_effective_date between nvl(p.effective_start_date, p_effective_date)
and nvl(p.effective_end_date, p_effective_date);
select sum(b.rt_val), sum(p.screen_entry_value)
from ben_prtt_rt_val b, pay_element_entry_values_f p
where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between nvl(b.rt_strt_dt, p_effective_date)
and nvl(b.rt_end_dt, p_effective_date)
and b.acty_typ_cd IN ('EEPYD', 'EEPRIID', 'PBD', 'PXD', 'PXD1')
and b.element_entry_value_id = p.element_entry_value_id (+)
and p_effective_date between nvl(p.effective_start_date, p_effective_date)
and nvl(p.effective_end_date, p_effective_date);
select sum(b.rt_val), sum(p.screen_entry_value)
from ben_prtt_rt_val b, pay_element_entry_values_f p
where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between nvl(b.rt_strt_dt, p_effective_date)
and nvl(b.rt_end_dt, p_effective_date)
and b.acty_typ_cd IN ('ERPYD', 'ERD')
and b.element_entry_value_id = p.element_entry_value_id (+)
and p_effective_date between nvl(p.effective_start_date, p_effective_date)
and nvl(p.effective_end_date, p_effective_date);
select sum(b.rt_val), sum(p.screen_entry_value)
from ben_prtt_rt_val b, pay_element_entry_values_f p
where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and p_effective_date between nvl(b.rt_strt_dt, p_effective_date)
and nvl(b.rt_end_dt, p_effective_date)
and b.acty_typ_cd NOT IN ('EEPYD', 'EEPRIID', 'PBD', 'PXD', 'PXD1', 'ERPYD',
'ERD', 'EEPLC', 'EEIC', 'EEPYC', 'PBC', 'PBC2',
'PXC', 'ERPYC', 'ERMPLC', 'ERC')
and b.element_entry_value_id = p.element_entry_value_id (+)
and p_effective_date between nvl(p.effective_start_date, p_effective_date)
and nvl(p.effective_end_date, p_effective_date);
select sum(b.rt_val), sum(p.screen_entry_value)
from ben_prtt_rt_val b, pay_element_entry_values_f p
where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
/* if the type is non recirring rate is creatted for one day so it may not return any row
and p_effective_date between nvl(b.rt_strt_dt, p_effective_date)
and nvl(b.rt_end_dt, p_effective_date) */
and b.acty_typ_cd = c_acty_typ_cd
and b.element_entry_value_id = p.element_entry_value_id (+)
and p_effective_date between p.effective_start_date (+)
and p.effective_end_date (+);
select sum(b.rt_val), sum(p.screen_entry_value)
from ben_prtt_rt_val b, pay_element_entry_values_f p
where b.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id
and b.acty_typ_cd IN ('PRDPER', 'PRDPPR', 'PRDPR')
and b.element_entry_value_id = p.element_entry_value_id (+)
and p_effective_date between nvl(p.effective_start_date, p_effective_date)
and nvl(p.effective_end_date, p_effective_date);
select
enrt.prtt_enrt_rslt_id prtt_enrt_rslt_id,
enrt.pl_id pl_id,
enrt.oipl_id oipl_id,
enrt.orgnl_enrt_dt orgn_strdt,
enrt.prtt_enrt_rslt_stat_cd status_cd,
enrt.enrt_cvg_strt_dt cvg_strt_dt,
enrt.enrt_cvg_thru_dt cvg_thru_dt,
enrt.enrt_mthd_cd mthd_cd,
enrt.enrt_ovridn_flag ovridn_flag,
enrt.enrt_ovrid_thru_dt ovrid_thru_dt,
enrt.enrt_ovrid_rsn_cd ovrid_rsn_cd,
enrt.sspndd_flag sspndd_flag,
enrt.effective_start_date effct_strdt,
enrt.bnft_amt bnft_amt,
enrt.bnft_ordr_num bnft_order_num,
enrt.pgm_id pgm_id,
enrt.last_update_date,
enrt.prtt_enrt_rslt_stat_cd,
enrt.per_in_ler_id,
enrt.pl_ordr_num pl_seq_num,
enrt.plip_ordr_num plip_seq_num,
enrt.ptip_ordr_num ptip_seq_num,
enrt.oipl_ordr_num oipl_seq_num,
enrt.ler_id enrt_ler_id,
enrt.assignment_id assignment_id,
enrt.rplcs_sspndd_rslt_id ,
enrt.uom uom,
enrt.pen_attribute1,
enrt.pen_attribute2,
enrt.pen_attribute3,
enrt.pen_attribute4,
enrt.pen_attribute5,
enrt.pen_attribute6,
enrt.pen_attribute7,
enrt.pen_attribute8,
enrt.pen_attribute9,
enrt.pen_attribute10,
pl.name pl_name,
pl.short_name pl_fd_name,
pl.short_code pl_fd_code,
pl.pl_typ_id pl_typ_id,
pl.pln_attribute1,
pl.pln_attribute2,
pl.pln_attribute3,
pl.pln_attribute4,
pl.pln_attribute5,
pl.pln_attribute6,
pl.pln_attribute7,
pl.pln_attribute8,
pl.pln_attribute9,
pl.pln_attribute10,
ptp.name pl_typ_name,
ptp.short_name ptp_fd_name,
ptp.short_code ptp_fd_code,
ptp.ptp_attribute1,
ptp.ptp_attribute2,
ptp.ptp_attribute3,
ptp.ptp_attribute4,
ptp.ptp_attribute5,
ptp.ptp_attribute6,
ptp.ptp_attribute7,
ptp.ptp_attribute8,
ptp.ptp_attribute9,
ptp.ptp_attribute10
from ben_prtt_enrt_rslt_f enrt,
ben_pl_f pl,
ben_pl_typ_f ptp
where enrt.person_id = p_person_id
and p_effective_date between enrt.effective_start_date
and enrt.effective_end_date
and enrt.pl_id = pl.pl_id
and p_effective_date between pl.effective_start_date and pl.effective_end_date
and pl.pl_typ_id = ptp.pl_typ_id
and p_effective_date between ptp.effective_start_date
and ptp.effective_end_date
;
select pil.lf_evt_ocrd_dt,
pil.ntfn_dt,
pil.ler_id,
pil.per_in_ler_stat_cd,
ler.name ler_name
from ben_per_in_ler pil,
ben_ler_f ler
where pil.per_in_ler_id = p_pil_id
and ler.ler_id = pil.ler_id
and p_effective_date between ler.effective_start_date and ler.effective_end_date
;
select pgm.name pgm_name,
pgm.short_name pgm_fd_name,
pgm.short_code pgm_fd_code,
pgm.pgm_attribute1,
pgm.pgm_attribute2,
pgm.pgm_attribute3,
pgm.pgm_attribute4,
pgm.pgm_attribute5,
pgm.pgm_attribute6,
pgm.pgm_attribute7,
pgm.pgm_attribute8,
pgm.pgm_attribute9,
pgm.pgm_attribute10
from ben_pgm_f pgm
where p_pgm_id = pgm.pgm_id
and p_effective_date between pgm.effective_start_date
and pgm.effective_end_date
;
select oipl.short_name oipl_fd_name,
oipl.short_code oipl_fd_code,
oipl.cop_attribute1,
oipl.cop_attribute2,
oipl.cop_attribute3,
oipl.cop_attribute4,
oipl.cop_attribute5,
oipl.cop_attribute6,
oipl.cop_attribute7,
oipl.cop_attribute8,
oipl.cop_attribute9,
oipl.cop_attribute10,
opt.opt_id opt_id,
opt.name opt_name,
opt.short_name opt_fd_name,
opt.short_code opt_fd_code,
opt.opt_attribute1,
opt.opt_attribute2,
opt.opt_attribute3,
opt.opt_attribute4,
opt.opt_attribute5,
opt.opt_attribute6,
opt.opt_attribute7,
opt.opt_attribute8,
opt.opt_attribute9,
opt.opt_attribute10
from ben_oipl_f oipl,
ben_opt_f opt
where p_oipl_id = oipl.oipl_id
and p_effective_date between oipl.effective_start_date
and oipl.effective_end_date
and opt.opt_id = oipl.opt_id
and p_effective_date between opt.effective_start_date
and opt.effective_end_date
;
select cpp.cpp_attribute1,
cpp.cpp_attribute2,
cpp.cpp_attribute3,
cpp.cpp_attribute4,
cpp.cpp_attribute5,
cpp.cpp_attribute6,
cpp.cpp_attribute7,
cpp.cpp_attribute8,
cpp.cpp_attribute9,
cpp.cpp_attribute10,
cpp.short_name plip_fd_name,
cpp.short_code plip_fd_code
from ben_plip_f cpp
where p_pl_id = cpp.pl_id
and p_pgm_id = cpp.pgm_id
and p_effective_date between cpp.effective_start_date
and cpp.effective_end_date
;
select ppopl.elcns_made_dt elec_made_dt
from ben_elig_per_elctbl_chc ece,
ben_pil_elctbl_chc_popl ppopl
where (p_pgm_id is null or p_pgm_id = ece.pgm_id)
and p_pl_id = ece.pl_id
and (p_oipl_id is null or p_oipl_id = ece.oipl_id)
and (p_pil_id is null or p_pil_id = ece.per_in_ler_id )
and ece.pil_elctbl_chc_popl_id = ppopl.pil_elctbl_chc_popl_id
;
select popl.elcns_made_dt elec_made_dt
from ben_pil_elctbl_chc_popl popl
where ( (p_pgm_id is not null and p_pgm_id = popl.pgm_id)
or ( p_pgm_id is null and p_pl_id = popl.pl_id )
)
and p_pil_id = popl.per_in_ler_id
;
select short_name , short_code
from ben_ptip_f ptip
where pl_typ_id = l_pl_typ_id
and pgm_id = l_pgm_id
and p_effective_date between nvl(ptip.effective_start_date, p_effective_date)
and nvl(ptip.effective_end_date, p_effective_date)
;
select ppl.plcy_r_grp
from ben_popl_org_f ppl ,
per_all_assignments_f asg
where pl_id = l_pl_id
and plcy_r_grp is not null
and asg.assignment_id = ben_ext_person.g_assignment_id
and ppl.organization_id = asg.organization_id
and p_effective_date between ppl.effective_start_date
and ppl.effective_end_date
and p_effective_date between asg.effective_start_date
and asg.effective_end_date
;
SELECT name
,ext_ident
,prmry_care_prvdr_typ_cd
,effective_start_date
,effective_end_date
FROM ben_prmry_care_prvdr_f ppr
WHERE ppr.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
AND p_effective_date between ppr.effective_start_date
and ppr.effective_end_date;
select
sum(ppr.std_prem_val)
, ppr.std_prem_uom
from ben_prtt_prem_f ppr,
ben_per_in_ler pil
where ppr.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
and p_effective_date between nvl(ppr.effective_start_date, p_effective_date)
and nvl(ppr.effective_end_date, p_effective_date)
and pil.per_in_ler_id=ppr.per_in_ler_id
and pil.business_group_id+0=ppr.business_group_id+0
group by ppr.std_prem_uom
;
select grp.rptg_grp_id,
grp.name
from ben_prtt_enrt_rslt_f prst,
ben_popl_rptg_grp_f prpg,
ben_rptg_grp grp
where
prst.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
and prst.pl_id = prpg.pl_id
and prpg.rptg_grp_id = grp.rptg_grp_id;
select yrp.start_date,yrp.end_date
from ben_yr_perd yrp,
ben_popl_yr_perd pop,
ben_prtt_enrt_rslt_f pen
where pop.yr_perd_id = yrp.yr_perd_id
and pop.pl_id = pen.pl_id
and p_effective_date between yrp.start_date and nvl(yrp.end_date,p_effective_date)
and p_effective_date between pen.effective_start_date and pen.effective_end_date
and pen.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
and yrp.business_group_id = pen.business_group_id
and pop.business_group_id = pen.business_group_id;
select yrp.start_date,yrp.end_date
from ben_yr_perd yrp,
ben_popl_yr_perd pop,
ben_prtt_enrt_rslt_f pen
where pop.yr_perd_id = yrp.yr_perd_id
and pop.pgm_id = pen.pgm_id
and p_effective_date between yrp.start_date and nvl(yrp.end_date,p_effective_date)
and p_effective_date between pen.effective_start_date and pen.effective_end_date
and pen.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
and yrp.business_group_id = pen.business_group_id
and pop.business_group_id = pen.business_group_id;
select sum(prm.val)
,sum(prm.cr_val)
from ben_prtt_prem_by_mo_f prm
, ben_prtt_prem_f ppe
where ppe.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
and ppe.prtt_prem_id = prm.prtt_prem_id
and prm.yr_num = to_number(to_char(p_effective_date,'YYYY'))
and prm.mo_num = to_number(to_char(p_effective_date,'MM'));
select 1
from ben_prtt_enrt_rslt_f enrt
where enrt.rplcs_sspndd_rslt_id = l_prtt_enrt_rslt_id
and enrt.person_id = p_person_id
and sspndd_flag = 'Y'
and p_effective_date between enrt.effective_start_date
and enrt.effective_end_date
and p_effective_date between enrt.ENRT_CVG_STRT_DT
and enrt.ENRT_CVG_THRU_DT ;
select
enrt.pl_id pl_id,
pl.name pl_name,
enrt.oipl_id,
opt.opt_id opt_id,
opt.name opt_name,
enrt.bnft_amt cvg_amt
from ben_prtt_enrt_rslt_f enrt,
ben_pl_f pl,
ben_oipl_f oipl,
ben_opt_f opt
where enrt.prtt_enrt_rslt_id = l_rplcs_sspndd_rslt_id
and enrt.pl_id = pl.pl_id
and enrt.oipl_id = oipl.oipl_id (+)
and opt.opt_id (+) = oipl.opt_id
and p_effective_date between enrt.effective_start_date
and enrt.effective_end_date
and p_effective_date between pl.effective_start_date
and pl.effective_end_date
and p_effective_date between nvl(oipl.effective_start_date, p_effective_date)
and nvl(oipl.effective_end_date, p_effective_date)
and p_effective_date between opt.effective_start_date
and opt.effective_end_date
and enrt.person_id = p_person_id;
select count(*) from
ben_ext_rcd a,
ben_ext_rcd_in_file b
where a.ext_rcd_id = b.ext_rcd_id
and b.ext_file_id = p_ext_file_id
and b.rqd_flag = 'Y';
p_last_update_date => trunc(enrt.last_update_date),
p_ler_id => l_pil_rslt.ler_id,
p_ntfn_dt => l_pil_rslt.ntfn_dt,
p_lf_evt_ocrd_dt => l_pil_rslt.lf_evt_ocrd_dt,
p_per_in_ler_stat_cd => l_pil_rslt.per_in_ler_stat_cd,
p_per_in_ler_id => enrt.per_in_ler_id,
p_prtt_enrt_rslt_id => enrt.prtt_enrt_rslt_id,
p_effective_date => p_effective_date,
p_include => l_include
);