HRMS Contacts Query

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

/