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

/