[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select pln.*
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 pgm.*
from ben_pgm_f pgm
where pgm.pgm_id = p_pgm_id
and pgm.business_group_id = p_business_group_id
and p_effective_date
between pgm.effective_start_date
and pgm.effective_end_date;
select cop.*
from ben_oipl_f cop
where cop.oipl_id = p_oipl_id
and cop.business_group_id = p_business_group_id
and p_effective_date
between cop.effective_start_date
and cop.effective_end_date;
select cpp.*
from ben_plip_f cpp
where cpp.plip_id = p_plip_id
and cpp.business_group_id = p_business_group_id
and p_effective_date
between cpp.effective_start_date
and cpp.effective_end_date;
select ctp.*
from ben_ptip_f ctp
where ctp.ptip_id = p_ptip_id
and ctp.business_group_id = p_business_group_id
and p_effective_date
between ctp.effective_start_date
and ctp.effective_end_date;
select opt.*
from ben_opt_f opt
where opt.opt_id = p_opt_id
and opt.business_group_id = p_business_group_id
and p_effective_date
between opt.effective_start_date
and opt.effective_end_date;
select opp.*
from ben_oiplip_f opp
where opp.oiplip_id = p_oiplip_id
and opp.business_group_id = p_business_group_id
and p_effective_date
between opp.effective_start_date
and opp.effective_end_date;
select distinct pln.*
from ben_pl_f pln,
ben_plip_f cpp,
ben_pl_regn_f prg,
ben_regn_f reg
where pln.pl_id = cpp.pl_id
and pln.business_group_id = l_env.business_group_id
and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
between pln.effective_start_date
and pln.effective_end_date
and cpp.business_group_id = pln.business_group_id
and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
between cpp.effective_start_date
and cpp.effective_end_date
and cpp.pgm_id = p_pgm_id
and prg.pl_id = pln.pl_id
and prg.business_group_id = pln.business_group_id
and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
between prg.effective_start_date
and prg.effective_end_date
and reg.regn_id = prg.regn_id
and reg.business_group_id = prg.business_group_id
and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
between reg.effective_start_date
and reg.effective_end_date
and reg.name = decode(p_only_pls_subj_cobra_flag,
'Y',
'COBRA',
reg.name)
and exists
( select 1
from ben_elig_per_f epo
,ben_per_in_ler pil
where epo.pgm_id = p_pgm_id
and epo.pl_id = l_env.pl_id
and epo.business_group_id = l_env.business_group_id
and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
between epo.effective_start_date
and epo.effective_end_date
and epo.elig_flag = 'Y'
and pil.per_in_ler_id(+)=epo.per_in_ler_id
and pil.business_group_id(+)=epo.business_group_id+0
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null -- outer join condition
)
)
;
/* select pln.*
from ben_pl_f pln,
ben_plip_f cpp,
ben_pl_regn_f prg,
ben_regn_f reg,
ben_elig_per_f epo
, ben_per_in_ler pil
where pln.pl_id = cpp.pl_id
and pln.business_group_id = l_env.business_group_id
and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
between pln.effective_start_date
and pln.effective_end_date
and cpp.business_group_id = pln.business_group_id
and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
between cpp.effective_start_date
and cpp.effective_end_date
and cpp.pgm_id = p_pgm_id
and prg.pl_id = pln.pl_id
and prg.business_group_id = pln.business_group_id
and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
between prg.effective_start_date
and prg.effective_end_date
and reg.regn_id = prg.regn_id
and reg.business_group_id = prg.business_group_id
and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
between reg.effective_start_date
and reg.effective_end_date
and epo.pgm_id = p_pgm_id
and epo.pl_id = l_env.pl_id
and epo.business_group_id = l_env.business_group_id
and nvl(l_env.lf_evt_ocrd_dt,l_env.effective_date)
between epo.effective_start_date
and epo.effective_end_date
and epo.elig_flag = 'Y'
and reg.name = decode(p_only_pls_subj_cobra_flag,
'Y',
'COBRA',
reg.name)
and pil.per_in_ler_id(+)=epo.per_in_ler_id
and pil.business_group_id(+)=epo.business_group_id+0
and ( pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT') -- found row condition
or pil.per_in_ler_stat_cd is null -- outer join condition
)
;
g_cache_pgm_rec.delete;
g_cache_pl_rec.delete;
g_cache_oipl_rec.delete;
g_cache_plip_rec.delete;
g_cache_ptip_rec.delete;
g_cache_opt_rec.delete;
g_cache_oiplip_rec.delete;
g_cache_pgm_cobra_lookup.delete;
g_cache_pgm_cobra_rec.delete;