[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
b.contact_relationship_id
, b.contact_person_id
, b.contact_type
, b.sequence_number
, b.PRIMARY_CONTACT_FLAG
, b.RLTD_PER_RSDS_W_DSGNTR_FLAG
, b.PERSONAL_FLAG
, b.THIRD_PARTY_PAY_FLAG
, b.DATE_START
, b.DATE_END
, b.start_life_reason_id
, b.end_life_reason_id
, c.last_name
, c.correspondence_language
, c.date_of_birth
, c.email_address
, c.first_name
, c.full_name
, c.marital_status
, c.middle_names
, c.nationality
, c.national_identifier
, c.registered_disabled_flag
, c.sex
, c.student_status
, c.suffix
, c.pre_name_adjunct prefix
, c.title
, c.date_of_death
, c.known_as
, c.pre_name_adjunct
, c.uses_tobacco_flag
, c.previous_last_name
from
per_contact_relationships b,
per_all_people_f c
where
c.person_id = b.contact_person_id
and b.person_id = p_person_id
and p_effective_date between c.effective_start_date
and c.effective_end_date
;
select
a.address_line1
, a.address_line2
, a.address_line3
, a.town_or_city
, a.region_2
, a.postal_code
, a.country
, a.date_from
, a.region_3
from per_addresses a
where
a.person_id = p_contact_person_id
and p_effective_date between nvl(a.date_from, p_effective_date)
and nvl(a.date_to, p_effective_date)
and a.primary_flag = 'Y'
;
select
a.address_line1
, a.address_line2
, a.address_line3
, a.town_or_city
, a.region_2
, a.postal_code
, a.country
, a.date_from
, a.region_3
from per_addresses a,
per_contact_relationships r
where
r.contact_person_id = p_contact_person_id
and r.person_id = p_person_id
and r.person_id = a.person_id
and p_effective_date between nvl(a.date_from, p_effective_date)
and nvl(a.date_to, p_effective_date)
and a.primary_flag = 'Y'
and r.rltd_per_rsds_w_dsgntr_flag = 'Y'
;
select
h.phone_number phone_home
, w.phone_number phone_work
, f.phone_number phone_fax
, m.phone_number phone_mobile
from per_all_people_f p
, per_phones h
, per_phones w
, per_phones f
, per_phones m
where p.person_id = p_contact_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 h.parent_id (+) = p.person_id
and w.parent_id (+) = p.person_id
and f.parent_id (+) = p.person_id
and m.parent_id (+) = p.person_id
and h.parent_table (+) = 'PER_ALL_PEOPLE_F'
and w.parent_table (+) = 'PER_ALL_PEOPLE_F'
and f.parent_table (+) = 'PER_ALL_PEOPLE_F'
and m.parent_table (+) = 'PER_ALL_PEOPLE_F'
and h.phone_type (+) = 'H1'
and w.phone_type (+) = 'W1'
and f.phone_type (+) = 'WF'
and m.phone_type (+) = 'M'
and p_effective_date between nvl(h.date_from, p_effective_date)
and nvl(h.date_to, p_effective_date)
and p_effective_date between nvl(w.date_from, p_effective_date)
and nvl(w.date_to, p_effective_date)
and p_effective_date between nvl(f.date_from, p_effective_date)
and nvl(f.date_to, p_effective_date)
and p_effective_date between nvl(m.date_from, p_effective_date)
and nvl(m.date_to, p_effective_date)
;
select 'Y'
from ben_elig_dpnt egd,
ben_elig_per_elctbl_chc epe,
ben_per_in_ler pil
where egd.dpnt_person_id = p_contact_person_id
and pil.person_id = p_person_id
and pil.per_in_ler_id = epe.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
and epe.elig_per_elctbl_chc_id = egd.elig_per_elctbl_chc_id
and p_effective_date between egd.elig_strt_dt and egd.elig_thru_dt;
select 'Y'
from ben_elig_cvrd_dpnt_f pdp,
ben_prtt_enrt_rslt_f pen
where pdp.dpnt_person_id = p_contact_person_id
and pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd not in ('VOIDD','BCKDT')
and pen.sspndd_flag = 'N'
and pen.prtt_enrt_rslt_id = pdp.prtt_enrt_rslt_id
and p_effective_date between pdp.cvg_strt_dt and pdp.cvg_thru_dt
and p_effective_date between pdp.effective_start_date and pdp.effective_end_date
and p_effective_date between pen.effective_start_date and pen.effective_end_date;
select 'Y'
from ben_pl_bnf_f pbn,
ben_prtt_enrt_rslt_f pen
where pbn.bnf_person_id = p_contact_person_id
and pen.person_id = p_person_id
and pen.prtt_enrt_rslt_stat_cd not in ('VOIDD','BCKDT')
and pen.sspndd_flag = 'N'
and pen.prtt_enrt_rslt_id = pbn.prtt_enrt_rslt_id
and p_effective_date between pbn.dsgn_strt_dt and pbn.dsgn_thru_dt
and p_effective_date between pbn.effective_start_date and pbn.effective_end_date
and p_effective_date between pen.effective_start_date and pen.effective_end_date;
select name
from ben_ler_f ler
where ler_id = p_start_life_reason_id
and p_effective_date between ler.effective_start_date and ler.effective_end_date;
select name
from ben_ler_f ler
where ler_id = p_end_life_reason_id
and p_effective_date between ler.effective_start_date and ler.effective_end_date;