[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select rslt.pl_id,
rslt.enrt_cvg_strt_dt,
rslt.enrt_cvg_thru_dt,
rslt.sspndd_flag,
rslt.prtt_enrt_rslt_stat_cd,
rslt.enrt_mthd_cd,
rslt.pgm_id,
rslt.pl_typ_id,
rslt.per_in_ler_id,
rslt.prtt_enrt_rslt_id,
rslt.last_update_date,
pil.ler_id,
pil.per_in_ler_stat_cd,
pil.lf_evt_ocrd_dt,
pil.ntfn_dt
from ben_prtt_enrt_rslt_f rslt,
ben_per_in_ler pil
where rslt.person_id = p_person_id
AND pil.per_in_ler_id(+) = rslt.per_in_ler_id
and p_effective_date between rslt.effective_start_date
and rslt.effective_end_date;
select distinct rslt.person_id
FROM ben_prtt_enrt_rslt_f rslt,
ben_elig_cvrd_dpnt_f dpnt
WHERE rslt.prtt_enrt_rslt_id = dpnt.prtt_enrt_rslt_id
AND dpnt.dpnt_person_id = p_person_id
--AND p_effective_date between rslt.enrt_cvg_strt_dt
-- and rslt.enrt_cvg_thru_dt
-- when the person and dpnt coverd fonm, person extracted not the dpnt
AND p_effective_date between rslt.effective_start_date
and rslt.effective_end_date
AND p_effective_date between dpnt.effective_start_date
and dpnt.effective_end_date;
SELECT rslt.pl_id,
rslt.sspndd_flag,
dpnt.cvg_strt_dt,
dpnt.cvg_thru_dt,
rslt.prtt_enrt_rslt_stat_cd,
rslt.enrt_mthd_cd,
rslt.pgm_id,
rslt.pl_typ_id,
rslt.per_in_ler_id,
rslt.prtt_enrt_rslt_id,
rslt.last_update_date,
pil.ler_id,
pil.per_in_ler_stat_cd,
pil.lf_evt_ocrd_dt,
pil.ntfn_dt
FROM ben_prtt_enrt_rslt_f rslt,
ben_elig_cvrd_dpnt_f dpnt,
ben_per_in_ler pil
WHERE rslt.prtt_enrt_rslt_id = dpnt.prtt_enrt_rslt_id
and rslt.per_in_ler_id = pil.per_in_ler_id(+)
AND dpnt.dpnt_person_id = p_dpnt_person_id
and rslt.person_id = p_prtt_person_id
--AND p_effective_date between rslt.enrt_cvg_strt_dt
-- and rslt.enrt_cvg_thru_dt
AND p_effective_date between rslt.effective_start_date
and rslt.effective_end_date
AND p_effective_date between dpnt.effective_start_date
and dpnt.effective_end_date;
select c.contact_type,
p.national_identifier,
p.first_name,
p.last_name,
c.SEQUENCE_NUMBER
from
per_contact_relationships c,
per_all_people_f p
where
c.contact_person_id = p_dpnt_person_id
and c.person_id = p_person_id
and c.person_id = p.person_id
and p_effective_date between nvl(p.effective_start_date, p_effective_date)
and nvl(p.effective_end_date, p_effective_date)
and p_effective_date between nvl(c.date_Start,p_effective_date)
and nvl(c.date_end ,p_effective_date)
order by c.sequence_number, decode(c.contact_type,'EMRG',2,1) ;
select c.contact_type,
p.national_identifier,
p.first_name,
p.last_name,
c.SEQUENCE_NUMBER
from
per_contact_relationships c,
per_all_people_f p
where
c.contact_person_id = p_dpnt_person_id
and c.person_id = p_person_id
and c.person_id = p.person_id
and p_effective_date between nvl(p.effective_start_date, p_effective_date)
and nvl(p.effective_end_date, p_effective_date)
and p_effective_date >= nvl(c.date_Start,p_effective_date)
order by c.sequence_number, decode(c.contact_type,'EMRG',2,1),c.date_end desc ;
p_last_update_date => enrt.last_update_date,
p_ler_id => enrt.ler_id,
p_ntfn_dt => enrt.ntfn_dt,
p_lf_evt_ocrd_dt => enrt.lf_evt_ocrd_dt,
p_per_in_ler_stat_cd => enrt.per_in_ler_stat_cd,
p_per_in_ler_id => enrt.per_in_ler_id,
p_prtt_enrt_rslt_id => enrt.prtt_enrt_rslt_id,
p_effective_date => p_effective_date,
p_include => l_include);
p_last_update_date => j.last_update_date,
p_ler_id => j.ler_id,
p_ntfn_dt => j.ntfn_dt,
p_lf_evt_ocrd_dt => j.lf_evt_ocrd_dt,
p_per_in_ler_stat_cd => j.per_in_ler_stat_cd,
p_per_in_ler_id => j.per_in_ler_id,
p_prtt_enrt_rslt_id => j.prtt_enrt_rslt_id,
p_effective_date => p_effective_date,
p_include => l_include);