[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select cop.oipl_id,
cop.opt_id,
cop.drvbl_fctr_prtn_elig_flag,
cop.drvbl_fctr_apls_rts_flag,
cop.trk_inelig_per_flag
from
ben_pl_f pln,
ben_oipl_f cop
-- ben_popl_yr_perd cpy,
-- ben_yr_perd yrp
where pln.pl_id = c_pl_id
and c_effective_date
between pln.effective_start_date
and pln.effective_end_date
and cop.pl_id = pln.pl_id
and c_effective_date
between cop.effective_start_date
and cop.effective_end_date
and (p_mode IN ('P','G','D') or
exists (select null
from ben_popl_yr_perd cpy,
ben_yr_perd yrp
where cpy.pl_id = pln.pl_id
and cpy.yr_perd_id = yrp.yr_perd_id
and c_effective_date
between yrp.start_date
and yrp.end_date))
and pln.pl_stat_cd = 'A'
and cop.oipl_stat_cd = 'A'
order by cop.ordr_num;
select cop.oipl_id,
cop.opt_id,
cop.drvbl_fctr_prtn_elig_flag,
cop.drvbl_fctr_apls_rts_flag,
cop.trk_inelig_per_flag
from ben_pl_f pln,
ben_oipl_f cop,
-- ben_popl_yr_perd cpy,
-- ben_yr_perd yrp,
ben_opt_f opt
where pln.pl_id = c_pl_id
and c_effective_date
between pln.effective_start_date
and pln.effective_end_date
and cop.pl_id = pln.pl_id
and c_effective_date
between cop.effective_start_date
and cop.effective_end_date
and (p_mode IN ('P','G','D') or
exists (select null
from ben_popl_yr_perd cpy,
ben_yr_perd yrp
where cpy.pl_id = pln.pl_id
and cpy.yr_perd_id = yrp.yr_perd_id
and c_effective_date
between yrp.start_date
and yrp.end_date))
and cop.opt_id = opt.opt_id
and c_effective_date
between opt.effective_start_date
and opt.effective_end_date
and pln.pl_stat_cd = 'A'
and cop.oipl_stat_cd = 'A'
and opt.opt_id = nvl(c_opt_id,opt.opt_id)
/* Make sure that option being linked to is of the eligibility profile
that has been specified by the user. */
and (c_eligy_prfl_id is not null and exists
(select null
from ben_prtn_elig_f epa2,
ben_prtn_elig_prfl_f cep,
ben_eligy_prfl_f elp
where epa2.oipl_id = cop.oipl_id
and epa2.business_group_id = cop.business_group_id
and c_effective_date
between epa2.effective_start_date
and epa2.effective_end_date
and cep.prtn_elig_id = epa2.prtn_elig_id
and cep.business_group_id = epa2.business_group_id
and c_effective_date
between cep.effective_start_date
and cep.effective_end_date
and elp.eligy_prfl_id = cep.eligy_prfl_id
and elp.business_group_id = cep.business_group_id
and elp.eligy_prfl_id = c_eligy_prfl_id
and c_effective_date
between elp.effective_start_date
and elp.effective_end_date)
or c_eligy_prfl_id is null)
/* Make sure that plan being linked to is of the variable rate profile
that has been specified by the user. */
and (c_vrbl_rt_prfl_id is not null and exists
(select null
from ben_acty_base_rt_f abr,
ben_acty_vrbl_rt_f avr,
ben_vrbl_rt_prfl_f vpf
where abr.oipl_id = cop.oipl_id
and abr.business_group_id = pln.business_group_id
and c_effective_date
between abr.effective_start_date
and abr.effective_end_date
and avr.acty_base_rt_id = abr.acty_base_rt_id
and avr.business_group_id = abr.business_group_id
and c_effective_date
between avr.effective_start_date
and avr.effective_end_date
and vpf.vrbl_rt_prfl_id = avr.vrbl_rt_prfl_id
and vpf.business_group_id = avr.business_group_id
and vpf.vrbl_rt_prfl_id = c_vrbl_rt_prfl_id
and c_effective_date
between vpf.effective_start_date
and vpf.effective_end_date)
or c_vrbl_rt_prfl_id is null)
order by cop.ordr_num;
g_eedcop_parlookup.delete;
g_eedcop_lookup.delete;
g_eedcop_inst.delete;