Monday, August 4, 2014

SQL Query to Find Direct Reports for a Manager in PeopleSoft


In my everyday work I often require to get direct reports for a supervisor. I do even see my colleagues looking for such a query because as far as I know there is no delivered page or record view in PeopleSoft which could give us such info.

Finally I decided to write one of my own. Trust me, the SQL I am sharing has been tested well and its working perfectly. Note that the query will not consider terminated direct reports.

Just enter EMPLID of the supervisor in the highlighted area and run, you will get list of all the direct reports for this supervisor.


SELECT J.EMPLID FROM PS_JOB J
 WHERE J.HR_STATUS = 'A'
   AND J.EFFDT = (SELECT MAX(J1.EFFDT) FROM PS_JOB J1
                   WHERE J1.EMPLID = J.EMPLID
                     AND J1.EMPL_RCD = J.EMPL_RCD
                     AND J1.EFFDT <= SYSDATE
                 )
   AND J.EFFSEQ = (SELECT MAX(J2.EFFSEQ) FROM PS_JOB J2
                    WHERE J2.EMPLID = J.EMPLID
                      AND J2.EMPL_RCD = J.EMPL_RCD
                      AND J2.EFFDT = J.EFFDT
                  )
   AND J.REPORTS_TO  = (SELECT JB.POSITION_NBR FROM PS_JOB JB
                         WHERE JB.EMPLID = '<Enter Manager ID>'
                           AND JB.HR_STATUS = 'A'
                           AND JB.EFFDT = (SELECT MAX(JB1.EFFDT) FROM PS_JOB JB1
                                             WHERE JB1.EMPLID = JB.EMPLID
                                               AND JB1.EMPL_RCD = JB.EMPL_RCD
                                               AND JB1.EFFDT <=  JB.SYSDATE
                                           )
                           AND JB.EFFSEQ = (SELECT MAX(JB2.EFFSEQ) FROM PS_JOB JB2
                                             WHERE JB2.EMPLID = JB.EMPLID
                                               AND JB2.EMPL_RCD = JB.EMPL_RCD
                                               AND JB2.EFFDT =  JB.EFFDT
                                           )
                       )


Please also see
Simplified Way to Provide a Page Access in PeopleSoft
PeopleSoft Set Control Field
Adding and Maintaining Person Of Interest in PeopleSoft
Unable to See Future dated transactions in Job Data page in PeopleSoft
Hiring a Person in PeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft
Understanding Component Interface in PeopleSoft
FTE For Multiple Jobs in PoeopleSoft 
How to find the List of Users Assigned to a Role
How the FTE is Calculated in PeopleSoft
Understanding Future dated security in PeopleSoft
How to Resolve a Row Level Security Issue in PeopleSoft
Part Time/Full Time, Regular/Temporary and Contingent Workers in PeopleSoft 
'Override Position Data' and 'Use Position Data' Option in Job Data Page 
How the Various Date Fields on Employment Data Page are Updated 
PeopleSoft HRMS Online Training


Want to learn PeopleSoft technical module thoroughly? I have several videos with total duration of over 50 hours.


Following is the link to the YouTube videos Technical
Click here to see course contents

Click here to know how it works

However, if you want to save money by purchasing whole module instead of in parts then visit this page to get more details PeopleSoft Functional and technical online training

7 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. There is a simpler way to get the same results but with less SQL. The Current Job Record (View) contains the current information about each employee.

    SELECT EMPLID FROM PS_CURRENT_JOB
    WHERE REPORTS_TO = (SELECT POSITION_NBR FROM PS_CURRENT_JOB
    WHERE HR_STATUS = 'A'
    AND EMPLID = :1)
    AND HR_STATUS = 'A'

    Replace :1 with the EMPLID of the Manager/Supervisor. I use this currently in my work and find it to work out great!

    Hope it helps!

    Thanks!

    ReplyDelete
  3. What if we are not using position management and need to fetch supervisor id ?

    ReplyDelete
  4. It’s a nice blog posted by you. I was seeking for this type of blog that have a fresh and interesting content.

    SQL Server Analysis Services

    ReplyDelete
  5. select * from (SELECT CAT.INSTITUTION ,CAT.SUBJECT, CAT.DESCR, CAT.EFFDT , CAT.EFF_STATUS, RANK () OVER (PARTITION BY CAT.INSTITUTION ,CAT.SUBJECT ORDER BY effdt DESC) rnk FROM PS_SUBJECT_TBL CAT WHERE effdt <= sysdate
    ) J WHERE J.RNK =1 AND J.EFF_STATUS='A'

    ReplyDelete
  6. Select from POSN_INCUMBENT table and you are all set.

    ReplyDelete
  7. Can you help me to get employee Supervisor's name.We use partial position management and use Report_to field instead of Supervisor_id

    ReplyDelete