Discussion in 'Oracle HRMS & Payroll' started by nestor, Sep 17, 2010.
please help me build an sql query to find the employees per organization. Thanks
that depeneds on your organization structure use the below query and modify according to your design
SELECT haou.name, papf.employee_number, papf.full_name
FROM per_all_people_f papf,
WHERE 1 = 1
AND papf.person_id = paaf.person_id
AND paaf.organization_id = haou.organization_id
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
GROUP BY haou.name, papf.employee_number, papf.full_name
employees based on organization or employees having organization?
Add papf.current_employee_flag= 'Y' to the query given by "bchaneelkumar" so that you can get only current employees.
it restricts all the applicants and ex-employees which can be avoided from the list.
Organization resides in Assignments form. So to restrict the query with organization basis, then we need to restrict PER_ALL_ASSIGNMENTS_F with organization_id and then get back to PER_ALL_PEOPLE_F by the help of person_id.
Note: To avoid duplication restrict it with current active employees