Monday, February 25, 2013

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)
  

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect