PER Full Work Details

select  con.contact_person_id
,       con.person_id
,       con.contact_relationship_id
 ,      hl1.meaning Contact_primary
,       hl2.meaning Relationship
,       hr3.meaning || decode(hr3.meaning,null,null,' ') || peo.first_name ||
       decode(peo.first_name,null,null,' ') || peo.last_name person
,       hr4.meaning dependent
from    per_contact_relationships con
,       per_all_people_f peo
,       hr_lookups hl1
,       hr_lookups hl2
,       hr_lookups hr3
,       hr_lookups hr4
where con.contact_person_id = :P_PERSON_ID
and     hl1.lookup_code = con.primary_contact_flag
and    hl1.lookup_type = 'YES_NO'
and    hl2.lookup_code = con.contact_type
and    hl2.lookup_type = 'CONTACT'
and    con.business_group_id = :p_business_group_id
and hr3.lookup_type  (+) = 'TITLE'
and hr3.lookup_code  (+) = peo.title
and  peo.person_id = con.person_id
and peo.effective_start_date = (select min(peo2.effective_start_date)
		                from  per_all_people_f peo2
		                where peo2.person_id = peo.person_id)
and hr4.lookup_type  (+) = 'YES_NO'
and hr4.lookup_code  (+) = con.dependent_flag
order by con.contact_relationship_id