[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select /*+ bendtlep.check_elig_othr_ptip_prte.pilc1 */
null
from ben_elig_per_f epo
where epo.ptip_id = c_ptip_id
and epo.pl_id = null
and c_effective_date
between epo.effective_start_date
and epo.effective_end_date
and epo.per_in_ler_id = c_per_in_ler_id
and epo.elig_flag = 'Y';
select /*+ bendtlep.check_elig_othr_ptip_prte.pilc1 */
null
from ben_pl_f pln,
ben_plip_f cpp,
ben_ptip_f ctp,
ben_pl_regn_f prg,
ben_regn_f reg,
ben_elig_per_f epo
where pln.pl_id = cpp.pl_id
and c_effective_date
between pln.effective_start_date
and pln.effective_end_date
and c_effective_date
between cpp.effective_start_date
and cpp.effective_end_date
and cpp.pgm_id = ctp.pgm_id
and pln.pl_typ_id = ctp.pl_typ_id
and ctp.ptip_id = c_ptip_id
and c_effective_date
between ctp.effective_start_date
and ctp.effective_end_date
and prg.pl_id = pln.pl_id
and c_effective_date
between prg.effective_start_date
and prg.effective_end_date
and reg.regn_id = prg.regn_id
and c_effective_date
between reg.effective_start_date
and reg.effective_end_date
and epo.per_in_ler_id = c_per_in_ler_id
and epo.pgm_id = ctp.pgm_id
and epo.pl_id = pln.pl_id
and c_effective_date
between epo.effective_start_date
and epo.effective_end_date
and epo.elig_flag = 'Y'
and reg.sttry_citn_name = decode(c_only_pls_subj_cobra_flag,
'Y',
'COBRA',
reg.sttry_citn_name);
select /*+ first_rows bendtlep.check_elig_othr_ptip_prte.c1 */ --Bug 5200242
null
from ben_pl_f pln,
ben_plip_f cpp,
ben_ptip_f ctp,
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 = c_business_group_id
and c_effective_date
between pln.effective_start_date
and pln.effective_end_date
and cpp.business_group_id = pln.business_group_id
and c_effective_date
between cpp.effective_start_date
and cpp.effective_end_date
and cpp.pgm_id = ctp.pgm_id
and pln.pl_typ_id = ctp.pl_typ_id
and ctp.ptip_id = c_ptip_id
and ctp.business_group_id = pln.business_group_id
and c_effective_date
between ctp.effective_start_date
and ctp.effective_end_date
and prg.pl_id = pln.pl_id
and prg.business_group_id = pln.business_group_id
and c_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 c_effective_date
between reg.effective_start_date
and reg.effective_end_date
and epo.person_id = c_person_id
and epo.pgm_id = ctp.pgm_id
and epo.pl_id = pln.pl_id
and epo.business_group_id = c_business_group_id
and c_effective_date
between epo.effective_start_date
and epo.effective_end_date
and epo.elig_flag = 'Y'
and reg.sttry_citn_name = decode(c_only_pls_subj_cobra_flag,
'Y',
'COBRA',
reg.sttry_citn_name)
and pil.per_in_ler_id(+)=epo.per_in_ler_id
--and pil.business_group_id(+)=epo.business_group_id
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 /*+ bendtlep.check_elig_othr_ptip_prte.c1 */
null
from ben_elig_per_f epo,
ben_per_in_ler pil
where epo.person_id = c_person_id
and epo.pl_id is null
and epo.ptip_id = c_ptip_id
and epo.business_group_id = c_business_group_id
and c_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
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 /*+ bendtlep.check_elig_dpnt_othr_ptip.c1 */
null
from ben_pl_f pln,
ben_plip_f cpp,
ben_ptip_f ctp,
ben_elig_per_f epo,
ben_elig_dpnt edp,
ben_per_in_ler pil,
per_contact_relationships pcr -- bug 6811004
where pln.pl_id = cpp.pl_id
and pln.business_group_id = c_bgp_id
and c_eff_date
between pln.effective_start_date and pln.effective_end_date
and cpp.business_group_id = pln.business_group_id
and c_eff_date
between cpp.effective_start_date and cpp.effective_end_date
and cpp.pgm_id = ctp.pgm_id
and pln.pl_typ_id = ctp.pl_typ_id
and ctp.ptip_id = c_ptip_id
and ctp.business_group_id = pln.business_group_id
and c_eff_date
between ctp.effective_start_date and ctp.effective_end_date
and pcr.contact_person_id = c_person_id -- bug 6811004
and edp.dpnt_person_id = pcr.person_id -- bug 6811004
and epo.pgm_id = ctp.pgm_id
and epo.pl_id = pln.pl_id
and epo.business_group_id = c_bgp_id
and c_eff_date
between epo.effective_start_date and epo.effective_end_date
and epo.elig_flag = 'Y'
and edp.dpnt_inelig_flag = 'N'
and edp.create_dt = (select max(edp2.create_dt)
from ben_elig_dpnt edp2
,ben_per_in_ler pil2
where edp2.dpnt_person_id = edp.dpnt_person_id
and edp2.elig_per_id = epo.elig_per_id
and pil2.per_in_ler_id(+)=edp2.per_in_ler_id
and pil2.business_group_id(+)=edp2.business_group_id
and (pil2.per_in_ler_stat_cd
not in ('VOIDD','BCKDT')
or pil2.per_in_ler_stat_cd is null))
and epo.elig_per_id = edp.elig_per_id
and pil.per_in_ler_id(+)=edp.per_in_ler_id
and (pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
or pil.per_in_ler_stat_cd is null
)
and epo.per_in_ler_id = edp.per_in_ler_id
and c_eff_date
between epo.effective_start_date and epo.effective_end_date;