Saturday, April 5, 2014

Oracle HRMS Scripts(Queries) - Employee Supervisor

SELECT asg.organization_id,
    emp.employee_number,
    emp.full_name emp_name,
    pos.NAME POSITION,
    sup.employee_number sup_emp_no,
    sup.full_name sup_name,
    sup_pos.NAME sup_pos,
    sppt.user_person_type,
    emp.effective_start_date emp_esd,
    emp.effective_end_date emp_eed,
    asg.effective_start_date asg_esd,
    asg.effective_end_date asg_eed,
    sup.effective_start_date sup_esd,
    sup.effective_end_date sup_eed,
    sup_asg.effective_start_date sup_asg_esd,
    sup_asg.effective_end_date sup_asg_eed,
    sptu.effective_start_date sptu_esd,
    sptu.effective_end_date sptu_eed      
  FROM per_all_people_f emp,
       per_all_assignments_f asg,
       per_all_positions pos,
       per_all_people_f sup,
       per_all_assignments_f sup_asg,
       per_all_positions sup_pos,
       per_person_type_usages_f sptu,
       per_person_types sppt
 WHERE asg.person_id = emp.person_id
   AND pos.position_id(+) = asg.position_id
   AND sup.person_id(+) = asg.supervisor_id
   AND sup_asg.person_id(+) = sup.person_id
   AND sup_pos.position_id(+) = sup_asg.position_id
   AND sptu.person_id = emp.person_id
   AND sppt.person_type_id = sptu.person_type_id
   and emp.person_type_id = 1120


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect