[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
a.elig_cvrd_dpnt_id
, a.cvg_strt_dt
, a.cvg_thru_dt
, a.dpnt_person_id
, a.per_in_ler_id
, a.prtt_enrt_rslt_id
, a.last_update_date
, 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.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 ben_elig_cvrd_dpnt_f a,
per_contact_relationships b,
per_all_people_f c
where
a.prtt_enrt_rslt_id = l_prtt_enrt_rslt_id
and a.cvg_pndg_flag = 'N'
-- remarked for bug 1394604
-- and p_effective_date between a.cvg_strt_dt
-- and a.cvg_thru_dt
-- and a.cvg_thru_dt <= a.effective_end_date
--
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 a.effective_start_date
and a.effective_end_date
and p_effective_date between nvl(b.date_Start,p_effective_date)
and nvl(b.date_end ,p_effective_date)
;
select pil.PER_IN_LER_STAT_CD,
pil.LF_EVT_OCRD_DT,
pil.NTFN_DT,
pil.LER_ID
from ben_per_in_ler pil
where pil.per_in_ler_id = p_per_in_ler_id ;
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'
/* Start of Changes for WWBUG: 1856611: addition */
and p_effective_date between r.date_start
and nvl(r.date_end,hr_api.g_eot)
/* End of Changes for WWBUG: 1856611: addition */
;
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)
;
SELECT name
,ext_ident
,prmry_care_prvdr_typ_cd
,effective_start_date
,effective_end_date
FROM ben_prmry_care_prvdr_f ppr
WHERE ppr.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
AND p_effective_date between ppr.effective_start_date
and ppr.effective_end_date;
p_last_update_date => dpnt.last_update_date,
p_ler_id => nvl(l_ler_id,ben_ext_person.g_enrt_ler_id),
p_ntfn_dt => nvl(l_NTFN_DT,ben_ext_person.g_enrt_lfevt_note_dt),
p_lf_evt_ocrd_dt => nvl(l_LF_EVT_OCRD_DT,ben_ext_person.g_enrt_lfevt_ocrd_dt) ,
p_per_in_ler_stat_cd => nvl(l_per_in_ler_stat_cd,ben_ext_person.g_enrt_lfevt_status),
p_per_in_ler_id => dpnt.per_in_ler_id,
p_prtt_enrt_rslt_id => dpnt.prtt_enrt_rslt_id,
p_effective_date => p_effective_date,
p_dpnt_id => dpnt.dpnt_person_id,
p_include => l_include);