Oracle HRMS Scripts(Queries) - Contacts

SELECT /*+ rule */
  xx.employee_number empno,
  xx.full_name employee_full_name,
  per.effective_start_date,
  per.last_name contact_last_name,
  per.first_name contact_first_name,
  per.full_name contact_full_name,
  InitCap(per.title) Title,
  per.pre_name_adjunct  prefix,
  per.suffix,
  per.middle_names,
  hr_general.decode_lookup ('SEX', per.sex) gender,
  ppt.USER_PERSON_TYPE Person_type,
  per.national_identifier national_identifier,
  per.date_of_birth,
  per.town_of_birth,
  hr_general.decode_lookup ('MAR_STATUS',per.marital_status) marital_status,
  per.region_of_birth,
  hr_general.decode_lookup ('NATIONALITY', per.nationality) nationality,
  per.country_of_birth,
  per.registered_disabled_flag,
  per.email_address,
  per.honors,
  per.known_as preffered_name,
  per.previous_last_name,
  per.correspondence_language,
  --
  --
  per.attribute1 religion,
  per.attribute2 place_of_birth,
  per.attribute3 no_of_wife,
  per.attribute4 hajj_leave_taken,
  per.attribute5 citizenship,
  per.attribute6 marriage_leave_taken,
  per.attribute7 marriage_date,
  per.attribute8 xemployee_number,
  --
       pcr.date_start Relationship_start_date ,
       lkp.meaning Relationship_type ,
       --koc_get.emp(pcr.PERSON_ID) Relation_ship_from_employee,
       pcr.CONTACT_PERSON_ID Related_to_employee,
       --
       pcr.primary_contact_flag Primary_contact,
       pcr.third_party_pay_flag Payment_Recipient,
       pcr.rltd_per_rsds_w_dsgntr_flag Shared_Residence,
       pcr.personal_flag Personal_Relationship,
       pcr.beneficiary_flag Beneficiary,
       pcr.dependent_flag Dependent,
       pcr.sequence_number Sequence_Number,
       pcr.bondholder_flag Create_Mirror,
       pcr.contact_type    Mirror_relaion_type,
       --
       pcr.cont_attribute1 Disabled,
       pcr.cont_attribute2 isZain_Employee,
       pcr.cont_attribute5 Dependancy_ceased_date
--       pcr.cont_attribute3 Dependancy_restarted_date,
--       pcr.cont_attribute6 Name_of_school,
--       pcr.cont_attribute7 School_grade,
--       pcr.cont_attribute8 School_class,
--       pcr.cont_attribute9 School_year,
--       pcr.cont_attribute10 EAP_CAP,
--       pcr.cont_attribute20 EAP_CAP_reciepient_number,
--       pcr.cont_attribute11 School_fees_paid_by_emp,
--       pcr.cont_attribute12 School_fees_paid_currency,
--       pcr.cont_attribute13 Claimed_date,
--       pcr.cont_attribute14 Residnecy_expire_date,
--       pcr.cont_attribute15 Resident_in_kuwait,
--       pcr.cont_attribute16 Diceased_date,
--       pcr.cont_attribute17 Nationaliy_group,
--     pcr.cont_attribute18 Medical_squence_Number,
--     pcr.cont_attribute19 Dependent_number
FROM
       per_all_people_f per ,
       per_periods_of_service pps,
       per_person_types ppt,
       per_contact_relationships pcr,
       hr_lookups lkp,
       per_people_f  xx
WHERE
  pcr.person_id = xx.person_id AND
  TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1))) BETWEEN per.effective_start_date AND  per.effective_end_date
  AND TRUNC(SYSDATE) BETWEEN xx.effective_start_date AND  xx.effective_end_date
  AND per.person_id = pps.PERSON_ID(+)
  AND per.person_type_Id = ppt.person_type_id(+)
  AND  lkp.lookup_type(+) = 'CONTACT'
  AND  lkp.lookup_code(+) = pcr.contact_type
  AND  pcr.CONTACT_PERSON_ID  = per.person_id
ORDER BY pcr.contact_person_id
/