[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
sum(nvl(ecr1.val,ecr2.val)
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr1,
ben_enrt_rt ecr2,
ben_enrt_bnft enb
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
and enb.enrt_bnft_id = ecr2.enrt_bnft_id (+)
and nvl(ecr1.tx_typ_cd,ecr2.tx_typ_cd) = 'PRETAX'
and nvl(ecr1.acty_typ_cd,ecr2.acty_typ_cd) IN ('EEPLC', 'EEIC', 'EEPYC', 'PBC', 'PBC2', 'PXC');
select
sum(ecr1.val)
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr1
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id
and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id
and ecr1.tx_typ_cd ='PRETAX'
and ecr1.acty_typ_cd in ('EEPLC', 'EEIC','EEPYC','PBC','PBC2','PXC') ;
select
sum(ecr2.val)
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr2,
ben_enrt_bnft enb
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id
and ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
and enb.enrt_bnft_id = ecr2.enrt_bnft_id
and ecr2.tx_typ_cd='PRETAX'
and ecr2.acty_typ_cd IN ('EEPLC','EEIC','EEPYC','PBC','PBC2','PXC') ;
select
sum(nvl(ecr1.val,ecr2.val))
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr1,
ben_enrt_rt ecr2,
ben_enrt_bnft enb
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
and enb.enrt_bnft_id = ecr2.enrt_bnft_id (+)
and nvl(ecr1.tx_typ_cd,ecr2.tx_typ_cd) = 'AFTERTAX'
and nvl(ecr1.acty_typ_cd,ecr2.acty_typ_cd) IN ('EEPLC', 'EEIC', 'EEPYC', 'PBC', 'PBC2', 'PXC');
select
sum(ecr1.val)
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr1
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id
and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id
and ecr1.tx_typ_cd='AFTERTAX'
and ecr1.acty_typ_cd in ('EEPLC', 'EEIC' , 'EEPYC', 'PBC', 'PBC2', 'PXC')
;
select
sum(ecr2.val)
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr2,
ben_enrt_bnft enb
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
and enb.enrt_bnft_id = ecr2.enrt_bnft_id
and ecr2.tx_typ_cd='AFTERTAX'
and ecr2.acty_typ_cd IN ('EEPLC', 'EEIC','EEPYC', 'PBC', 'PBC2', 'PXC')
;
select
sum(nvl(ecr1.val,ecr2.val))
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr1,
ben_enrt_rt ecr2,
ben_enrt_bnft enb
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
and enb.enrt_bnft_id = ecr2.enrt_bnft_id (+)
and nvl(ecr1.acty_typ_cd,ecr2.acty_typ_cd) IN ('EEPLC', 'EEIC', 'EEPYC', 'PBC', 'PBC2', 'PXC');
select
sum(ecr1.val)
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr1
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id
and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id
and ecr1.acty_typ_cd in ('EEPLC', 'EEIC' , 'EEPYC', 'PBC', 'PBC2', 'PXC')
;
select
sum(ecr2.val)
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr2,
ben_enrt_bnft enb
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
and enb.enrt_bnft_id = ecr2.enrt_bnft_id
and ecr2.acty_typ_cd IN ('EEPLC', 'EEIC','EEPYC', 'PBC', 'PBC2', 'PXC')
;
select
sum(nvl(ecr1.val,ecr2.val))
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr1,
ben_enrt_rt ecr2,
ben_enrt_bnft enb
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
and enb.enrt_bnft_id = ecr2.enrt_bnft_id (+)
and nvl(ecr1.acty_typ_cd,ecr2.acty_typ_cd) IN ('ERPYC', 'ERMPLC', 'ERC');
select
sum(ecr1.val)
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr1
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id
and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id
and ecr1.acty_typ_cd in ('ERPYC', 'ERMPLC', 'ERC')
;
select
sum(ecr2.val)
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr2,
ben_enrt_bnft enb
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
and enb.enrt_bnft_id = ecr2.enrt_bnft_id
and ecr2.acty_typ_cd IN ('ERPYC', 'ERMPLC', 'ERC')
;
select
sum(nvl(ecr1.val,ecr2.val))
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr1,
ben_enrt_rt ecr2,
ben_enrt_bnft enb
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
and enb.enrt_bnft_id = ecr2.enrt_bnft_id (+)
and nvl(ecr1.acty_typ_cd,ecr2.acty_typ_cd) IN ('EEPYD', 'EERIID', 'PBD', 'PXD', 'PXD1');
select
sum(ecr1.val)
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr1
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id
and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id
and ecr1.acty_typ_cd in ('EEPYD', 'EERIID', 'PBD', 'PXD', 'PXD1')
;
select
sum(ecr2.val)
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr2,
ben_enrt_bnft enb
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
and enb.enrt_bnft_id = ecr2.enrt_bnft_id
and ecr2.acty_typ_cd IN ('EEPYD', 'EERIID', 'PBD', 'PXD', 'PXD1')
;
select
sum(nvl(ecr1.val,ecr2.val))
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr1,
ben_enrt_rt ecr2,
ben_enrt_bnft enb
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
and enb.enrt_bnft_id = ecr2.enrt_bnft_id (+)
and nvl(ecr1.acty_typ_cd,ecr2.acty_typ_cd) IN ('ERPYD', 'ERD');
select
sum(ecr1.val)
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr1
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id
and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id
and ecr1.acty_typ_cd in ('ERPYD', 'ERD')
;
select
sum(ecr2.val)
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr2,
ben_enrt_bnft enb
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
and enb.enrt_bnft_id = ecr2.enrt_bnft_id
and ecr2.acty_typ_cd IN ('ERPYD', 'ERD')
;
select
sum(nvl(ecr1.val,ecr2.val))
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr1,
ben_enrt_rt ecr2,
ben_enrt_bnft enb
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
and enb.enrt_bnft_id = ecr2.enrt_bnft_id (+)
and nvl(ecr1.acty_typ_cd,ecr2.acty_typ_cd) NOT IN ('EEPYD', 'EEPRIID', 'PBD', 'PXD',
'PXD1', 'ERPYD', 'ERD', 'EEPLC',
'EEIC', 'EEPYC', 'PBC', 'PBC2',
'PXC', 'ERPYC', 'ERMPLC', 'ERC');
select
sum(ecr1.val)
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr1
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id
and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id
and ecr1.acty_typ_cd not in ('EEPYD', 'EEPRIID', 'PBD', 'PXD',
'PXD1', 'ERPYD', 'ERD', 'EEPLC',
'EEIC', 'EEPYC', 'PBC', 'PBC2',
'PXC', 'ERPYC', 'ERMPLC', 'ERC')
;
select
sum(ecr2.val)
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr2,
ben_enrt_bnft enb
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
and enb.enrt_bnft_id = ecr2.enrt_bnft_id
and ecr2.acty_typ_cd not IN ('EEPYD', 'EEPRIID', 'PBD', 'PXD',
'PXD1', 'ERPYD', 'ERD', 'EEPLC',
'EEIC', 'EEPYC', 'PBC', 'PBC2',
'PXC', 'ERPYC', 'ERMPLC', 'ERC')
;
select
enr.mn_elcn_val,
enr.mx_elcn_val,
enr.incrmt_elcn_val,
enr.dflt_val
from ben_enrt_rt enr
where enr.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id;
select
enr.mn_elcn_val,
enr.mx_elcn_val,
enr.incrmt_elcn_val,
enr.dflt_val
from ben_enrt_rt enr,
ben_enrt_bnft bnf
where bnf.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and bnf.enrt_bnft_id = enr.enrt_bnft_id;
select
sum(nvl(ecr1.val,ecr2.val))
from ben_elig_per_elctbl_chc ele,
ben_enrt_rt ecr1,
ben_enrt_rt ecr2,
ben_enrt_bnft enb
where
p_elig_per_elctbl_chc_id = ele.elig_per_elctbl_chc_id and
ele.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
and ele.elig_per_elctbl_chc_id = ecr1.elig_per_elctbl_chc_id(+)
and enb.enrt_bnft_id = ecr2.enrt_bnft_id (+)
and (ecr1.acty_typ_cd = c_typ_cd or (ecr1.acty_typ_cd is null and ecr2.acty_typ_cd = c_typ_cd)) ;
select + FIRST_ROWS(1) BEN_EXT_ELIG.main.c_elig
pler.ler_id ler_id,
pler.person_id person_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.pl_ordr_num pl_seq_num,
echc.plip_ordr_num plip_seq_num,
echc.ptip_ordr_num ptip_seq_num,
echc.oipl_ordr_num oipl_seq_num,
echc.pl_id pl_id,
echc.epe_attribute1 flex_01,
echc.epe_attribute2 flex_02,
echc.epe_attribute3 flex_03,
echc.epe_attribute4 flex_04,
echc.epe_attribute5 flex_05,
echc.epe_attribute6 flex_06,
echc.epe_attribute7 flex_07,
echc.epe_attribute8 flex_08,
echc.epe_attribute9 flex_09,
echc.epe_attribute10 flex_10,
pl.name pl_name,
pl.short_name pl_fd_name,
pl.short_code pl_fd_code,
pl.pln_attribute1 pl_flex_01,
pl.pln_attribute2 pl_flex_02,
pl.pln_attribute3 pl_flex_03,
pl.pln_attribute4 pl_flex_04,
pl.pln_attribute5 pl_flex_05,
pl.pln_attribute6 pl_flex_06,
pl.pln_attribute7 pl_flex_07,
pl.pln_attribute8 pl_flex_08,
pl.pln_attribute9 pl_flex_09,
pl.pln_attribute10 pl_flex_10,
ptp.name pl_type_name,
ptp.short_name ptp_fd_name,
ptp.short_code ptp_fd_code,
ptp.ptp_attribute1 ptp_flex_01,
ptp.ptp_attribute2 ptp_flex_02,
ptp.ptp_attribute3 ptp_flex_03,
ptp.ptp_attribute4 ptp_flex_04,
ptp.ptp_attribute5 ptp_flex_05,
ptp.ptp_attribute6 ptp_flex_06,
ptp.ptp_attribute7 ptp_flex_07,
ptp.ptp_attribute8 ptp_flex_08,
ptp.ptp_attribute9 ptp_flex_09,
ptp.ptp_attribute10 ptp_flex_10,
plip.short_name plip_fd_name,
plip.short_code plip_fd_code,
plip.ordr_num pl_ord_no,
plip.cpp_attribute1 plip_flex_01,
plip.cpp_attribute2 plip_flex_02,
plip.cpp_attribute3 plip_flex_03,
plip.cpp_attribute4 plip_flex_04,
plip.cpp_attribute5 plip_flex_05,
plip.cpp_attribute6 plip_flex_06,
plip.cpp_attribute7 plip_flex_07,
plip.cpp_attribute8 plip_flex_08,
plip.cpp_attribute9 plip_flex_09,
plip.cpp_attribute10 plip_flex_10,
echc.elig_per_elctbl_chc_id elig_per_elctbl_chc_id,
echc.enrt_cvg_strt_dt enrt_cvg_strt_dt,
echc.yr_perd_id yr_perd_id,
echc.pl_typ_id pl_typ_id,
echc.last_update_date last_update_date,
echc.per_in_ler_id per_in_ler_id,
echc.prtt_enrt_rslt_id prtt_enrt_rslt_id,
opt.name opt_name,
opt.opt_id opt_id,
opt.short_name opt_fd_name,
opt.short_code opt_fd_code,
oipl.short_name oipl_fd_name,
oipl.short_code oipl_fd_code,
oipl.ordr_num opt_ord_no,
oipl.cop_attribute1 oipl_flex_01,
oipl.cop_attribute2 oipl_flex_02,
oipl.cop_attribute3 oipl_flex_03,
oipl.cop_attribute4 oipl_flex_04,
oipl.cop_attribute5 oipl_flex_05,
oipl.cop_attribute6 oipl_flex_06,
oipl.cop_attribute7 oipl_flex_07,
oipl.cop_attribute8 oipl_flex_08,
oipl.cop_attribute9 oipl_flex_09,
oipl.cop_attribute10 oipl_flex_10,
ptip.short_name ptip_fd_name,
ptip.short_code ptip_fd_code,
enb.val cvg_amt,
enb.mn_val mn_val,
enb.mx_val mx_val,
enb.dflt_val dflt_val,
enb.incrmt_val incrmt_val,
decode(enb.enrt_bnft_id , null,echc.dflt_flag, enb.dflt_flag) dflt_flag,
enb.nnmntry_uom nnmntry_uom,
enb.bnft_typ_cd bnft_typ_cd,
enb.entr_val_at_enrt_flag entr_val_at_enrt_flag,
enb.cvg_mlt_cd cvg_mlt_cd,
enb.ordr_num ordr_num,
ppopl.enrt_perd_strt_dt enrt_strt_dt,
ppopl.enrt_perd_end_dt enrt_end_dt,
ppopl.dflt_enrt_dt dflt_enrt_dt,
ppopl.uom uom,
ppopl.elcns_made_dt elcn_made_dt,
pgm.pgm_id program_id,
pgm.name program_name,
pgm.short_name pgm_fd_name,
pgm.short_code pgm_fd_code,
pgm.pgm_attribute1 pgm_flex_01,
pgm.pgm_attribute2 pgm_flex_02,
pgm.pgm_attribute3 pgm_flex_03,
pgm.pgm_attribute4 pgm_flex_04,
pgm.pgm_attribute5 pgm_flex_05,
pgm.pgm_attribute6 pgm_flex_06,
pgm.pgm_attribute7 pgm_flex_07,
pgm.pgm_attribute8 pgm_flex_08,
pgm.pgm_attribute9 pgm_flex_09,
pgm.pgm_attribute10 pgm_flex_10,
opt.opt_attribute1 opt_flex_01,
opt.opt_attribute2 opt_flex_02,
opt.opt_attribute3 opt_flex_03,
opt.opt_attribute4 opt_flex_04,
opt.opt_attribute5 opt_flex_05,
opt.opt_attribute6 opt_flex_06,
opt.opt_attribute7 opt_flex_07,
opt.opt_attribute8 opt_flex_08,
opt.opt_attribute9 opt_flex_09,
opt.opt_attribute10 opt_flex_10,
pl.cobra_pymt_due_dy_num
from ben_per_in_ler pler,
ben_elig_per_elctbl_chc echc,
ben_pil_elctbl_chc_popl ppopl,
ben_enrt_bnft enb,
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_ptip_f ptip -- 2732104
where pler.person_id = p_person_id
and pler.per_in_ler_id = echc.per_in_ler_id
and echc.pil_elctbl_chc_popl_id = ppopl.pil_elctbl_chc_popl_id
and echc.elctbl_flag = 'Y'
and echc.pgm_id = pgm.pgm_id(+) --removed -1 nvl statement
and echc.pl_id = pl.pl_id(+) --removed -1 nvl statement
and oipl.opt_id = opt.opt_id(+) --removed -1 nvl statement
and echc.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
and echc.oipl_id = oipl.oipl_id(+) --removed -1 nvl statement
and echc.plip_id = plip.plip_id(+) --removed -1 nvl statement
and echc.ptip_id = ptip.ptip_id(+) --removed -1 nvl statement -- 2732104
and pl.pl_typ_id = ptp.pl_typ_id(+) --removed -1 nvl statement
and p_effective_date
between nvl(pl.effective_start_date,p_effective_date)
and nvl(pl.effective_end_date ,p_effective_date)
and p_effective_date
between nvl(opt.effective_start_date,p_effective_date)
and nvl(opt.effective_end_date ,p_effective_date)
and p_effective_date
between nvl(plip.effective_start_date,p_effective_date)
and nvl(plip.effective_end_date ,p_effective_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 nvl(ptp.effective_start_date,p_effective_date)
and nvl(ptp.effective_end_date ,p_effective_date)
and p_effective_date
between nvl(pgm.effective_start_date,p_effective_date)
and nvl(pgm.effective_end_date ,p_effective_date)
and p_effective_date
between nvl(ptip.effective_start_date,p_effective_date)
and nvl(ptip.effective_end_date ,p_effective_date)
;
l_elig_last_update_date_va t_date ;
select /*+ FIRST_ROWS(1) BEN_EXT_ELIG.main.c_elig */
pler.ler_id ler_id,
pler.person_id person_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.pl_ordr_num pl_seq_num,
echc.plip_ordr_num plip_seq_num,
echc.ptip_ordr_num ptip_seq_num,
echc.oipl_ordr_num oipl_seq_num,
echc.pl_id pl_id,
echc.epe_attribute1 flex_01,
echc.epe_attribute2 flex_02,
echc.epe_attribute3 flex_03,
echc.epe_attribute4 flex_04,
echc.epe_attribute5 flex_05,
echc.epe_attribute6 flex_06,
echc.epe_attribute7 flex_07,
echc.epe_attribute8 flex_08,
echc.epe_attribute9 flex_09,
echc.epe_attribute10 flex_10,
echc.elig_per_elctbl_chc_id elig_per_elctbl_chc_id,
echc.enrt_cvg_strt_dt enrt_cvg_strt_dt,
echc.yr_perd_id yr_perd_id,
echc.pl_typ_id pl_typ_id,
echc.plip_id plip_id,
echc.ptip_id ptip_id,
echc.last_update_date last_update_date,
echc.per_in_ler_id per_in_ler_id,
echc.prtt_enrt_rslt_id prtt_enrt_rslt_id,
echc.pgm_id program_id,
echc.oipl_id ,
ppopl.enrt_perd_strt_dt enrt_strt_dt,
ppopl.enrt_perd_end_dt enrt_end_dt,
ppopl.dflt_enrt_dt dflt_enrt_dt,
ppopl.uom uom,
ppopl.elcns_made_dt elcn_made_dt,
enb.val cvg_amt,
enb.mn_val mn_val,
enb.mx_val mx_val,
enb.dflt_val dflt_val,
enb.incrmt_val incrmt_val,
--decode(enb.enrt_bnft_id , null,echc.dflt_flag, enb.dflt_flag) dflt_flag,/* bug 5292 */
enb.enrt_bnft_id enrt_bnft_id,
echc.dflt_flag echc_dflt_flag ,
enb.dflt_flag dflt_flag,
enb.nnmntry_uom nnmntry_uom,
enb.bnft_typ_cd bnft_typ_cd,
enb.entr_val_at_enrt_flag entr_val_at_enrt_flag,
enb.cvg_mlt_cd cvg_mlt_cd,
enb.ordr_num ordr_num
from ben_per_in_ler pler,
ben_elig_per_elctbl_chc echc,
ben_pil_elctbl_chc_popl ppopl,
ben_enrt_bnft enb
where pler.person_id = p_person_id
and pler.per_in_ler_id = echc.per_in_ler_id
and echc.pil_elctbl_chc_popl_id = ppopl.pil_elctbl_chc_popl_id
and echc.elctbl_flag = 'Y'
and echc.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id(+)
;
select oipl.opt_id opt_id,
oipl.short_name oipl_fd_name,
oipl.short_code oipl_fd_code,
oipl.ordr_num opt_ord_no,
oipl.cop_attribute1 oipl_flex_01,
oipl.cop_attribute2 oipl_flex_02,
oipl.cop_attribute3 oipl_flex_03,
oipl.cop_attribute4 oipl_flex_04,
oipl.cop_attribute5 oipl_flex_05,
oipl.cop_attribute6 oipl_flex_06,
oipl.cop_attribute7 oipl_flex_07,
oipl.cop_attribute8 oipl_flex_08,
oipl.cop_attribute9 oipl_flex_09,
oipl.cop_attribute10 oipl_flex_10
from ben_oipl_f oipl
where oipl_id = p_oipl_id
and p_effective_date
between oipl.effective_start_date and oipl.effective_end_date
;
select pl.name pl_name,
pl.short_name pl_fd_name,
pl.short_code pl_fd_code,
pl.pln_attribute1 pl_flex_01,
pl.pln_attribute2 pl_flex_02,
pl.pln_attribute3 pl_flex_03,
pl.pln_attribute4 pl_flex_04,
pl.pln_attribute5 pl_flex_05,
pl.pln_attribute6 pl_flex_06,
pl.pln_attribute7 pl_flex_07,
pl.pln_attribute8 pl_flex_08,
pl.pln_attribute9 pl_flex_09,
pl.pln_attribute10 pl_flex_10,
pl.cobra_pymt_due_dy_num
from ben_pl_f pl
where pl.pl_id = p_pl_id
and p_effective_date
between pl.effective_start_date and pl.effective_end_date
;
select opt.name opt_name,
opt.short_name opt_fd_name,
opt.short_code opt_fd_code,
opt.opt_attribute1 opt_flex_01,
opt.opt_attribute2 opt_flex_02,
opt.opt_attribute3 opt_flex_03,
opt.opt_attribute4 opt_flex_04,
opt.opt_attribute5 opt_flex_05,
opt.opt_attribute6 opt_flex_06,
opt.opt_attribute7 opt_flex_07,
opt.opt_attribute8 opt_flex_08,
opt.opt_attribute9 opt_flex_09,
opt.opt_attribute10 opt_flex_10
from ben_opt_f opt
where opt.opt_id = p_opt_id
and p_effective_date
between opt.effective_start_date and opt.effective_end_date
;
select pgm.name program_name,
pgm.short_name pgm_fd_name,
pgm.short_code pgm_fd_code,
pgm.pgm_attribute1 pgm_flex_01,
pgm.pgm_attribute2 pgm_flex_02,
pgm.pgm_attribute3 pgm_flex_03,
pgm.pgm_attribute4 pgm_flex_04,
pgm.pgm_attribute5 pgm_flex_05,
pgm.pgm_attribute6 pgm_flex_06,
pgm.pgm_attribute7 pgm_flex_07,
pgm.pgm_attribute8 pgm_flex_08,
pgm.pgm_attribute9 pgm_flex_09,
pgm.pgm_attribute10 pgm_flex_10
from ben_pgm_f pgm
where pgm.pgm_id = p_pgm_id
and p_effective_date
between pgm.effective_start_date and pgm.effective_end_date
;
select ptp.name pl_type_name,
ptp.short_name ptp_fd_name,
ptp.short_code ptp_fd_code,
ptp.ptp_attribute1 ptp_flex_01,
ptp.ptp_attribute2 ptp_flex_02,
ptp.ptp_attribute3 ptp_flex_03,
ptp.ptp_attribute4 ptp_flex_04,
ptp.ptp_attribute5 ptp_flex_05,
ptp.ptp_attribute6 ptp_flex_06,
ptp.ptp_attribute7 ptp_flex_07,
ptp.ptp_attribute8 ptp_flex_08,
ptp.ptp_attribute9 ptp_flex_09,
ptp.ptp_attribute10 ptp_flex_10
from ben_pl_typ_f ptp
where p_pl_typ_id = ptp.pl_typ_id
and p_effective_date
between ptp.effective_start_date and ptp.effective_end_date
;
select plip.short_name plip_fd_name,
plip.short_code plip_fd_code,
plip.ordr_num pl_ord_no,
plip.cpp_attribute1 plip_flex_01,
plip.cpp_attribute2 plip_flex_02,
plip.cpp_attribute3 plip_flex_03,
plip.cpp_attribute4 plip_flex_04,
plip.cpp_attribute5 plip_flex_05,
plip.cpp_attribute6 plip_flex_06,
plip.cpp_attribute7 plip_flex_07,
plip.cpp_attribute8 plip_flex_08,
plip.cpp_attribute9 plip_flex_09,
plip.cpp_attribute10 plip_flex_10
from ben_plip_f plip
where p_plip_id = plip.plip_id
and p_effective_date
between plip.effective_start_date and plip.effective_end_date
;
select ptip.short_name ptip_fd_name,
ptip.short_code ptip_fd_code
from ben_ptip_f ptip
where p_ptip_id = ptip.ptip_id
and p_effective_date
between ptip.effective_start_date and ptip.effective_end_date
;
select eper.pl_id,
eper.age_val,
eper.age_uom,
eper.los_val,
eper.los_uom,
eper.comp_ref_amt,
eper.comp_ref_uom,
eper.cmbn_age_n_los_val,
eper.hrs_wkd_val,
eper.pct_fl_tm_val
from ben_elig_per_f eper
where eper.person_id = l_person_id
and eper.ler_id = l_ler_id
and eper.pl_id = l_pl_id
and eper.pgm_id is null
and p_effective_date
between eper.effective_start_date
and eper.effective_end_date;
select eper.pl_id,
eper.age_val,
eper.age_uom,
eper.los_val,
eper.los_uom,
eper.comp_ref_amt,
eper.comp_ref_uom,
eper.cmbn_age_n_los_val,
eper.hrs_wkd_val,
eper.pct_fl_tm_val
from ben_elig_per_f eper
where eper.person_id = l_person_id
and eper.ler_id = l_ler_id
and eper.pl_id = l_pl_id
and eper.pgm_id is not null
and p_effective_date
between eper.effective_start_date
and eper.effective_end_date;
select sum(epr.val),
epr.uom
from ben_enrt_prem epr
where epr.elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id
group by epr.uom;
select grp.rptg_grp_id,
grp.name
from ben_elig_per_elctbl_chc chc,
ben_popl_rptg_grp_f prpg,
ben_rptg_grp grp
where chc.elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id
and chc.pl_id = prpg.pl_id
and prpg.rptg_grp_id = grp.rptg_grp_id;
select yrpr.start_date,
yrpr.end_date
from ben_elig_per_elctbl_chc chc,
ben_yr_perd yrpr
where chc.elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id
and chc.yr_perd_id = yrpr.yr_perd_id;
select ler.ler_id
,ler.name
,pler.per_in_ler_stat_cd
,pler.ntfn_dt
,pler.lf_evt_ocrd_dt
from ben_per_in_ler pler
,ben_ler_f ler
where pler.person_id = p_person_id
and pler.per_in_ler_id = cp_per_in_ler_id
and pler.ler_id = ler.ler_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date;
select cpr.name admin_name,
cpo.organization_id
from ben_popl_org_role_f cpr,
ben_popl_org_f cpo
where
cpo.pl_id = p_pl_id
AND cpr.popl_org_id = cpo.popl_org_id
and cpr.org_role_typ_cd = 'ADM'
and p_effective_date between cpr.effective_Start_date
and cpr.effective_end_date
and p_effective_date between cpo.effective_Start_date
and cpo.effective_end_date ;
select cpr.name admin_name,
cpo.organization_id
from ben_popl_org_role_f cpr,
ben_popl_org_f cpo
where
cpo.pgm_id = p_pgm_id
AND cpr.popl_org_id = cpo.popl_org_id
and cpr.org_role_typ_cd = 'ADM'
and p_effective_date between cpr.effective_Start_date
and cpr.effective_end_date
and p_effective_date between cpo.effective_Start_date
and cpo.effective_end_date ;
select org.name admin_org_name,
loc.address_line_1 loc_addr1,
loc.address_line_2 loc_addr2,
loc.address_line_3 loc_addr3,
loc.town_or_city loc_city,
loc.region_2 loc_state,
loc.postal_code loc_zip,
loc.country loc_country,
loc.telephone_number_1 loc_phone
from hr_all_organization_units org
,hr_locations loc
where p_organization_id = org.organization_id
AND org.location_id = loc.location_id ;
l_elig_last_update_date_va ,
l_elig_per_in_ler_id_va ,
l_elig_prtt_enrt_rslt_id_va ,
l_elig_program_id_va ,
l_elig_oipl_id_va ,
l_elig_enrt_strt_dt_va ,
l_elig_enrt_end_dt_va ,
l_elig_dflt_enrt_dt_va ,
l_elig_uom_va ,
l_elig_elcn_made_dt_va ,
l_elig_cvg_amt_va ,
l_elig_mn_val_va ,
l_elig_mx_val_va ,
l_elig_dflt_val_va ,
l_elig_incrmt_val_va ,
l_elig_enrt_bnft_id_va ,
l_elig_echc_dflt_flag_va ,
l_elig_dflt_flag_va ,
l_elig_nnmntry_uom_va ,
l_elig_bnft_typ_cd_va ,
l_elig_entr_val_at_flag_va ,
l_elig_cvg_mlt_cd_va ,
l_elig_ordr_num_va ;
p_elct_last_upd_dt => l_elig_last_update_date_va(i),
p_elct_per_in_ler_id => l_elig_per_in_ler_id_va(i),
p_elct_ler_id => l_elig_ler_id_va(i),
p_elct_per_in_ler_stat_cd => l_elig_per_in_ler_stat_cd_va(i),
p_elct_lf_evt_ocrd_dt => l_elig_lf_evt_ocrd_dt_va(i),
p_elct_ntfn_dt => l_elig_ntfn_dt_va(i),
p_prtt_enrt_rslt_id => l_elig_prtt_enrt_rslt_id_va(i),
p_effective_date => p_effective_date,
p_include => l_include
);