APPS.BEN_PERSON_RECORD dependencies on PER_CONTACT_RELATIONSHIPS

Line 27: from per_contact_relationships pcr

23: and pgm.business_group_id = p_business_group_id) Program_Name,
24: ppf.first_name||' '||ppf.last_name || ' ' || ppf.suffix Dependent,
25: ppf.national_identifier Ssn,
26: (select HR_GENERAL.DECODE_LOOKUP('CONTACT',pcr.contact_type)
27: from per_contact_relationships pcr
28: where pcr.personal_flag = 'Y'
29: and pcr.person_id = pen.person_id
30: and pcr.contact_person_id = pdp.dpnt_person_id
31: and p_effective_date between nvl(pcr.date_start, p_effective_date )

Line 35: from per_contact_relationships pcr2

31: and p_effective_date between nvl(pcr.date_start, p_effective_date )
32: and nvl(pcr.date_end, p_effective_date )
33: and decode(pcr.contact_type,'S',1,'D',2,'A',3,'C',4,'O',5,'T',6,'P',7,8) <=
34: (select decode(pcr2.contact_type,'S',1,'D',2,'A',3,'C',4,'O',5,'T',6,'P',7,8)
35: from per_contact_relationships pcr2
36: where pcr2.person_id = pcr.person_id
37: and pcr2.contact_person_id = pcr.contact_person_id
38: and p_effective_date between nvl(pcr2.date_start, p_effective_date )
39: and nvl(pcr2.date_end, p_effective_date )

Line 53: per_contact_relationships pcr,

49: to_date(null) cvg_thru_dt
50: from ben_elig_per_elctbl_chc epe,
51: ben_prtt_enrt_rslt_f pen,
52: ben_elig_cvrd_dpnt_f pdp,
53: per_contact_relationships pcr,
54: per_people_f ppf,
55: ben_pl_typ_f plt,
56: ben_pl_f pln,
57: ben_opt_f opt,

Line 147: per_contact_relationships pcr,

143: pen.pl_ordr_num pl_ordr_num,
144: pen.oipl_ordr_num oipl_ordr_num,
145: pen.bnft_ordr_num bnft_ordr_num
146: from per_people_f ppf,
147: per_contact_relationships pcr,
148: ben_prtt_enrt_rslt_f pen,
149: ben_pl_bnf_f pbn,
150: ben_pl_typ_f plt,
151: ben_pl_f pln,

Line 220: from per_contact_relationships pcr2

216: (p_effective_date between
217: nvl(pcr.date_start, p_effective_date ) and
218: nvl(pcr.date_end, p_effective_date )) or
219: ((pcr.date_start = (select max(pcr2.date_start)
220: from per_contact_relationships pcr2
221: where pcr2.contact_person_id = pcr.contact_person_id
222: and pcr2.person_id = pcr.person_id
223: and pcr2.personal_flag = 'Y')) and
224: not exists (select null

Line 225: from PER_CONTACT_RELATIONSHIPS pcr3

221: where pcr2.contact_person_id = pcr.contact_person_id
222: and pcr2.person_id = pcr.person_id
223: and pcr2.personal_flag = 'Y')) and
224: not exists (select null
225: from PER_CONTACT_RELATIONSHIPS pcr3
226: where pcr3.contact_person_id = pcr.contact_person_id
227: and pcr3.person_id = pcr.person_id
228: and pcr3.personal_flag = 'Y'
229: and p_effective_date between