Oracle HRMS Scripts(Queries) - Employee Relation

SELECT 

rel.PERSON_ID Person_id , 

rel.CONTACT_PERSON_ID CONTACT_PERSON_ID,

per.full_name,

per.date_of_birth ,

hr_general.decode_lookup ('SEX', per.sex) gender ,

(SELECT

    look_up.meaning

 FROM hr_lookups look_up

 WHERE look_up.lookup_type = 'CONTACT'

    AND look_up.enabled_flag = 'Y'

    and look_up.lookup_code =  rel.CONTACT_TYPE

)  Relation 

FROM

    per_contact_relationships rel,

    per_people_f per

WHERE

    rel.CONTACT_PERSON_ID = per.person_id and

    per.BUSINESS_GROUP_ID=hr_general.GET_BUSINESS_GROUP_ID