BIP – Extract Worker Contact Details

SELECT DISTINCT papf.person_number	"Emp Person Number"
               ,papf_cont.person_number	"Contact Person Number"
			   ,hl.meaning 			"Relationship"
			   ,TO_CHAR (papf_cont.effective_start_date,'RRRR/MM/DD') "Contact Eff Start D"
			   ,TO_CHAR (papf_cont.effective_end_date,'RRRR/MM/DD') "Contact Eff End D"
			   ,TO_CHAR (papf_cont.start_date, 'RRRR/MM/DD') "Contact Start Date"
			   ,TO_CHAR (pcrf.effective_start_date,'RRRR/MM/DD') "Rel Start Date"
			   ,TO_CHAR (pcrf.effective_end_date,'RRRR/MM/DD') "Rel End Date"
			   ,TO_CHAR (pp.date_of_birth, 'RRRR/MM/DD') "Contact Person DOB"
			   ,ppnf.first_name	"Contact First Name"
			   ,ppnf.last_name 	"Contact Last Name"
          FROM per_contact_relships_f pcrf
		      ,per_all_people_f papf_cont
			  ,per_all_people_f papf
			  ,per_persons pp
			  ,per_person_names_f ppnf
			  ,hcm_lookups hl
         WHERE papf_cont.person_id = pcrf.contact_person_id
		   AND papf.person_id = pcrf.person_id
           AND pp.person_id = pcrf.contact_person_id
           AND ppnf.person_id = pcrf.contact_person_id
           AND ppnf.name_type = 'GLOBAL'
           AND hl.lookup_code = pcrf.contact_type
           AND hl.lookup_type = 'CONTACT'
      ORDER BY papf.person_number, hl.meaning