[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
b.contact_type bnf_rlshp
, b.sequence_number bnf_contact_seq_num
, b.rltd_per_rsds_w_dsgntr_flag
, b.contact_person_id bnf_person_id
, c.last_name bnf_lst_nm
, c.first_name bnf_fst_nm
, c.full_name bnf_fl_nm
, c.middle_names bnf_mid_nm
, c.national_identifier bnf_ssn
, c.suffix bnf_suffix
, c.pre_name_adjunct bnf_prefix
, c.title bnf_title
, c.previous_last_name bnf_prv_lst_nm
, c.pre_name_adjunct bnf_pre_nm_adjunct
, c.email_address bnf_email
, c.known_as bnf_known_as
, c.nationality bnf_nationality
, c.uses_tobacco_flag bnf_tobacco_usage
, c.sex bnf_gender
, c.date_of_birth bnf_dob
, c.marital_status bnf_marital_status
, c.registered_disabled_flag bnf_disabled_flag
, c.student_status bnf_student_status
, c.date_of_death bnf_dod
, c.correspondence_language bnf_language
, a.prmry_cntngnt_cd bnf_prmy_cont
, a.pct_dsgd_num bnf_pct_dsgd
, a.amt_dsgd_val bnf_amt_dsgd
, a.amt_dsgd_uom bnf_amt_uom
, a.pl_bnf_id pl_bnf_id
from ben_pl_bnf_f a,
ben_per_in_ler pil,
per_contact_relationships b,
per_all_people_f c
where
a.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
and a.bnf_person_id = b.contact_person_id
and b.person_id = p_person_id
and c.person_id = a.bnf_person_id
and p_effective_date between nvl(a.dsgn_strt_dt, p_effective_date)
and nvl(a.dsgn_thru_dt, p_effective_date)
and p_effective_date between nvl(a.effective_start_date, p_effective_date)
and nvl(a.effective_end_date, p_effective_date)
and p_effective_date between nvl(c.effective_start_date, p_effective_date)
and nvl(c.effective_end_date, p_effective_date)
and pil.per_in_ler_id=a.per_in_ler_id
-- and pil.business_group_id+0=a.business_group_id
and pil.per_in_ler_stat_cd not in ('VOIDD','BCKDT')
;
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 = c_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 = c_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 = c_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
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
, per_contact_relationships r
where r.contact_person_id = c_person_id
and p.person_id = r.person_id
and r.rltd_per_rsds_w_dsgntr_flag = 'Y'
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)
;