[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
pler.ler_id ler_id,
pler.per_in_ler_stat_cd per_in_ler_stat_cd,
pler.lf_evt_ocrd_dt lf_evt_ocrd_dt,
pler.ntfn_dt ntfn_dt,
echc.enrt_cvg_strt_dt enrt_cvg_strt_dt,
echc.yr_perd_id yr_perd_id,
echc.per_in_ler_id per_in_ler_id,
echc.prtt_enrt_rslt_id prtt_enrt_rslt_id,
echc.last_update_date last_update_date,
echc.pl_id pl_id,
pl.name pl_name,
ptp.name pl_typ_name,
echc.elig_per_elctbl_chc_id elig_per_elctbl_chc_id,
echc.oipl_id opt_id,
echc.pl_typ_id pl_typ_id,
opt.name opt_name,
ppopl.uom uom,
nvl(ecr.dflt_val,ecr.val) flex_amt,
echc.pgm_id program_id,
pgm.name program_name,
echc.cmbn_plip_id cmbn_plip_id,
cmbn_plip.name cmbn_plip_name,
echc.cmbn_ptip_id cmbn_ptip_id,
cmbn_ptip.name cmbn_ptip_name,
echc.cmbn_ptip_opt_id cmbn_ptip_opt_id,
cmbn_ptip_opt.name cmbn_ptip_opt_name,
pool.bnft_prvdr_pool_id pool_id,
pool.name pool_name
from
ben_per_in_ler pler,
ben_elig_per_elctbl_chc echc,
ben_enrt_rt ecr,
ben_pil_elctbl_chc_popl ppopl,
ben_opt_f opt,
ben_pl_f pl,
ben_plip_f plip,
ben_oipl_f oipl,
ben_pgm_f pgm,
ben_pl_typ_f ptp,
ben_cmbn_plip_f cmbn_plip,
ben_cmbn_ptip_f cmbn_ptip,
ben_cmbn_ptip_opt_f cmbn_ptip_opt,
ben_bnft_prvdr_pool_f pool
where
pler.person_id = p_person_id
and pler.per_in_ler_id = echc.per_in_ler_id
and pler.per_in_ler_id = ppopl.per_in_ler_id -- 3662774: Performance fix: Added this join.
and echc.elig_per_elctbl_chc_id = ecr.elig_per_elctbl_chc_id
and echc.bnft_prvdr_pool_id = pool.bnft_prvdr_pool_id(+)
and ecr.rt_usg_cd = 'FLXCR'
-- 3662774: Performance fix: Removed the outer join
-- and echc.pil_elctbl_chc_popl_id(+) = ppopl.pil_elctbl_chc_popl_id
and echc.pil_elctbl_chc_popl_id = ppopl.pil_elctbl_chc_popl_id
-- 3662774: Performance fix: end;
p_elct_last_upd_dt => elig.last_update_date,
p_elct_per_in_ler_id => elig.per_in_ler_id,
p_elct_ler_id => elig.ler_id,
p_elct_per_in_ler_stat_cd => elig.per_in_ler_stat_cd,
p_elct_lf_evt_ocrd_dt => elig.lf_evt_ocrd_dt,
p_elct_ntfn_dt => elig.ntfn_dt,
p_prtt_enrt_rslt_id => elig.prtt_enrt_rslt_id,
p_effective_date => p_effective_date,
p_include => l_include
);