1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to find employee names per organization

Discussion in 'Oracle HRMS & Payroll' started by nestor, Sep 17, 2010.

  1. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    please help me build an sql query to find the employees per organization. Thanks
     
  2. bchaneelkumar

    bchaneelkumar Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    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,
    per_all_assignments_f paaf,
    hr_all_organization_units haou
    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 papf.effective_end_date
    AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
    AND paaf.effective_end_date
    GROUP BY haou.name, papf.employee_number, papf.full_name
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    employees based on organization or employees having organization?
     
  4. sivaramakrishna.d

    sivaramakrishna.d Active Member

    Messages:
    61
    Likes Received:
    6
    Trophy Points:
    160
    Dear

    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.

    Siva Ram
     
  5. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    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