Employee Query

SELECT pap.person_id,

       pap.full_name,

       paa.assignment_id,

       pap.last_name,

       pap.first_name,

       pap.middle_names father_name,

       pap.suffix grand_father_name,

       pap.known_as arabic_name,

       apps.hr_general.decode_lookup ('TITLE', pap.title) title,

       ppt.user_person_type person_type,

       ppt.system_person_type,

       pap.original_date_of_hire,

       ppos.date_start hire_date,

       ppos.actual_termination_date,

       flv.meaning leaving_reason_meaning,

       ppos.leaving_reason,

       pap.employee_number,

       pap.national_identifier,

       pap.date_of_birth,

       pap.town_of_birth,

       pap.party_id,

       pap.region_of_birth,

       pap.country_of_birth,

       ftv.territory_short_name country_of_brith_meaning,

       pap.effective_start_date effective_start_date_emp,

       pap.effective_end_date effective_end_date_emp,

       paa.organization_id,

       haou.NAME ORGANIZATION,

       ppg.segment1 paid_by_group,

       ppg.segment2 union1,

       ppg.segment3 currency,

       ppg.segment4 tax_paid_by_company,

       ppg.segment5 social_security,

       ppg.segment6 employee_type1,

       pj.NAME job,

       papo.NAME POSITION,

       pg.NAME grade,

       papf.payroll_name,

       hla.location_code,

       psstt.user_status assignment_status,

       paa.assignment_number,

       ppb.NAME salary_bases,

       paps.full_name sup_name,

       paps.employee_number worker_number,

       paa.effective_start_date effective_start_date_asg,

       paa.effective_end_date effective_end_date_asg,

       ptu.effective_start_date effective_start_date_pusage,

       ptu.effective_end_date effective_end_date_pusage,

       NVL (cost_ff.segment2, '0000') gl_cost_centre,

       paa.primary_flag,

       pap.attribute1 arabic_firstname,

       paa.probation_period,

       paa.probation_unit,

       pap.attribute2 arabic_middle_name,

       pap.attribute3 arabic_last_name,

       pap.attribute8 arabic_nationality,

       pap.attribute4 blood_group,

       pap.attribute5 ss_no,

       pap.attribute10 ss_start_date,

       pap.attribute6 first_ser_start_date,

       pap.attribute9 no_children,

       pap.attribute1 religion,

       cost_center_desc,

       cost_centre cost_centre_value,

       DECODE (pg.NAME, 'Manager', 30, 'Director', 30, 25) leave_ent,

       DECODE (ppb.NAME, 'Zain Iraq USD Salary Basis', 'USD', 'KWD') e_currency,

       (SELECT fu.user_id FROM apps.fnd_user fu WHERE fu.employee_id = pap.person_id AND ROWNUM = 1) user_id,

       (SELECT fu.user_name FROM apps.fnd_user fu WHERE fu.employee_id = pap.person_id AND ROWNUM = 1) user_name

  FROM apps.per_all_people_f pap,

       apps.per_assignments_f2 paa,

       apps.per_person_type_usages_f ptu,

       apps.per_person_types ppt,

       apps.fnd_territories_vl ftv,

       apps.hr_all_organization_units haou,

       apps.pay_people_groups ppg,

       apps.per_jobs pj,

       apps.per_grades pg,

       apps.hr_all_positions_f papo,

       apps.pay_all_payrolls_f papf,

       apps.hr_locations_all hla,

       apps.per_assignment_status_types_tl psstt,

       apps.per_pay_bases ppb,

       apps.per_people_x paps,

       apps.per_periods_of_service ppos,

       apps.fnd_lookup_values flv,

       apps.pay_cost_allocation_keyflex cost_ff

 WHERE pap.person_id = paa.person_id

   AND ptu.person_id = pap.person_id

   AND ppt.person_type_id = ptu.person_type_id

   AND ftv.territory_code(+) = pap.country_of_birth

   AND haou.organization_id = paa.organization_id

   AND ppg.people_group_id(+) = paa.people_group_id

   AND pj.job_id(+) = paa.job_id

   AND papo.position_id(+) = paa.position_id

   AND pg.grade_id(+) = paa.grade_id

   AND papf.payroll_id(+) = paa.payroll_id

   AND hla.location_id(+) = paa.location_id

   AND psstt.assignment_status_type_id = paa.assignment_status_type_id

   AND ppb.pay_basis_id(+) = paa.pay_basis_id

   AND paps.person_id(+) = paa.supervisor_id

   AND ppos.person_id = pap.person_id

   AND flv.lookup_type(+) = 'LEAV_REAS'

   AND flv.lookup_code(+) = ppos.leaving_reason

   AND cost_ff.cost_allocation_keyflex_id(+) = haou.cost_allocation_keyflex_id

   AND system_person_type = 'EMP'

   AND (paa.effective_start_date BETWEEN papo.effective_start_date AND papo.effective_end_date OR papo.position_id IS NULL)