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.
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
This comment has been removed by the author.
ReplyDeleteThere 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.
ReplyDeleteSELECT 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!
What if we are not using position management and need to fetch supervisor id ?
ReplyDeleteIt’s a nice blog posted by you. I was seeking for this type of blog that have a fresh and interesting content.
ReplyDeleteSQL Server Analysis Services
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
ReplyDelete) J WHERE J.RNK =1 AND J.EFF_STATUS='A'
Select from POSN_INCUMBENT table and you are all set.
ReplyDeleteCan you help me to get employee Supervisor's name.We use partial position management and use Report_to field instead of Supervisor_id
ReplyDelete