Monday, February 25, 2013

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
/

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect