[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
a.create_dt
, a.elig_cvrd_dpnt_id
, a.elig_strt_dt
, a.elig_thru_dt
, a.ovrdn_flag
, a.ovrdn_thru_dt
, a.dpnt_person_id
, a.elig_dpnt_id
, b.contact_type
, b.sequence_number
, b.rltd_per_rsds_w_dsgntr_flag
, 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.title
, c.date_of_death
, c.known_as
, c.pre_name_adjunct
, c.uses_tobacco_flag
, c.previous_last_name
from ben_elig_dpnt a,
per_contact_relationships b,
per_all_people_f c
where
a.elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id
and a.dpnt_inelig_flag = 'N'
and a.dpnt_person_id = b.contact_person_id
and b.person_id = p_person_id
and c.person_id = a.dpnt_person_id
and p_effective_date between c.effective_start_date
and c.effective_end_date
and p_effective_date between b.date_start
and nvl( b.date_end,p_effective_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_dpnt_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_dpnt_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_dpnt_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)
;