Dynamic View for Supervisor

Just came across basic requirement on creating PS query which shows all those employees ID which you are supervisor to them.

Its looks simply but bit trick to construct it. Below are the steps to achieve this.


• Create the view NGB_MANAGER_VW based on this sql : which basically join on JOB & PSOPRDEFN table to get the list of the employees ID for which you are the supervisor for them

SELECT DISTINCT B.OPRID,A.EMPLID FROM PS_JOB A, PSOPRDEFN B
WHERE A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.EMPL_RCD = A_ED.EMPL_RCD
AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR,GETDATE(),102), 1, 10))
AND A.EFFSEQ = (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.EMPL_RCD = A_ES.EMPL_RCD
AND A.EFFDT = A_ES.EFFDT)
AND A.SUPERVISOR_ID = B.EMPLID

• The NGB_MANAGER_VIEW contains following two fields OPRID & EMPLID both as key …… You can added additional fields based on your requirement like First Name, Last Name , Name etc.,

• Added this view to query security tree [Navigation : PeopleTools > Security > Query Access Manager] Select appropriate tree and add the view to it.

• Create the PS Query (NGB_EMPLOYEELIST_BY_SUPERVISORID) on this view NGB_MANAGER_VW select emplid field and you will see in view sql by the default OperidID criteria get added to query by this feature the supervisor can see the list of employees under him.


You can used this PS QUERY in creating page let and added to your home page.