WC_DIR_RPTS_VW

(SQL View)
 Index Back

Direct Reports View

Obtains a list of employee's who report directly to a manager for use in the Works Council functions.

SELECT DISTINCT D.OPRID ,A.EMPLID , B.NAME , B.LAST_NAME , B.FIRST_NAME FROM PS_JOB A , PS_PERSON_NAME B , PS_DEPT_TBL C , PSOPRDEFN D WHERE A.EMPLID = B.EMPLID AND A.EMPL_STATUS IN ('A', 'L', 'P', 'W') AND A.EFFDT= ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=%CurrentDateIn) AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_RCD AND A.EFFDT=A3.EFFDT ) AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 WHERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTID AND C2.EFFDT<=%CurrentDateIn) AND C.MANAGER_ID = D.EMPLID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 NAME Character(50) VARCHAR2(50) NOT NULL Name
4 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
5 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name