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!

Query To get List of Employees which has Supervisors

Discussion in 'Oracle Apps Technical' started by jagadekara, Jan 2, 2015.

  1. jagadekara

    jagadekara Forum Guru

    Likes Received:
    Trophy Points:
    The following is a Query To get List of Employees which has Supervisors.

    Code (SQL):
    SELECT ppf.full_name emp_name
          ,ppf1.full_name supervisor_name
    FROM per_all_people_f ppf
    ,per_all_assignments_f paaf
    ,per_all_people_f ppf1
    WHERE 1=1
    AND ppf.person_id=paaf.person_id
    AND ppf.employee_number IS NOT NULL
    --AND ppf.person_id=968956
    AND sysdate BETWEEN paaf.effective_start_date AND paaf.effective_end_date
    AND paaf.supervisor_id=ppf1.person_id
    ORDER BY 1
    Let me know if I am wrong.
  2. yowancristo

    yowancristo Forum Advisor

    Likes Received:
    Trophy Points:
    Hi Jagadekara,

    Since the tables PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F keeps history records, this query will fetch duplicates records, Also currently it will show In Active employees as well. Below conditions will help to avoid this.

    Code (SQL):

    AND TRUNC(SYSDATE) BETWEEN TRUNC(PPF.effective_start_date) AND   TRUNC(NVL(PPF.effective_end_date,SYSDATE))
    AND TRUNC(SYSDATE) BETWEEN TRUNC(PPF1.effective_start_date) AND  TRUNC(NVL(PPF1.effective_end_date,SYSDATE))
    Yowan Cristo
  3. rajenb

    rajenb Forum Expert

    Likes Received:
    Trophy Points:
    Hi Yowan/Jagadekara,

    Some additional information to complement above posts (just completed a few stuff on HR Module so it's still fresh !):

    1) Active v/s Terminated Employees
    Yowan's post will definitely resolve 99% of the issues related to multiple (not only duplicates) records being fetched for a given employee-supervisor combination. It will bring the latest supervisor name attached to a given employee. I've highlighted "latest" not active. Even with your corrections, it will bring "Terminated" employees (Ex-Employees) and their attached supervisors. If you want only active employees, you have to limit it with
    Code (SQL):
    ppf.person_type_id = <person_type_id>
    where <person_type_id> = person type id associated with type "Employee".

    2) Applicant
    If an employee applies for an internal vacancy, an extra record is also created in per_all_assignments_f table which may also be retrieved (job application active) in the query. This may be filtered by using the condition

    Code (SQL):
    AND paaf.assignment_number IS NOT NULL
    3) Multiple or Secondary Assignments
    An employee may also be assigned to multiple positions (secondary assignments). You may filter off those with
    Code (SQL):
    AND paaf.primary_flag = 'Y'
    where you select only the primary assignment.

    4) Multiple Business Group
    If you have multiple business groups implemented (typical Vision instance for example), then you need to add the conditions on the current business group (or you'll end up mixing person_id from 1 BG to another).
    Code (SQL):
    AND ppf.business_group_id = paaf.business_group_id
    AND ppf1.business_group_id = paaf.business_group_id
    You may have other scenarios which may lead to "duplicates" but these are the most common ones.

    Hope this helps and adds extra substance to the Article.