Oracle HRMS Contacts Queries

/* Formatted on 5/15/2014 8:58:38 AM (QP5 v5.115.810.9015) */
  SELECT       /*+ rule */ -- SQL Optimizer hints
        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