DEPT_EE_APPT_VW

(SQL View)
 Index Back

Department Appointment View

DEPT_EE_APPT_VW selects the Emplid and Empl_Rcd# for all employees in a specific department.

SELECT DISTINCT A.SETID_DEPT ,B.BUDGET_DEPTID ,A.EMPLID ,A.EMPL_RCD FROM PS_JOB A , PS_DEPT_TBL B WHERE A.SETID_DEPT = B.SETID AND A.DEPTID = B.DEPTID AND ((A.EFFDT >= B.EFFDT AND NOT EXISTS ( SELECT 'X' FROM PS_JOB A1 WHERE A1.EMPLID = A.EMPLID AND A1.EMPL_RCD = A.EMPL_RCD AND A1.SETID_DEPT = A.SETID_DEPT AND A1.DEPTID = A.DEPTID AND A1.EFFDT < A.EFFDT AND A1.EFFDT > B.EFFDT AND A1.EFFSEQ = ( SELECT MAX(A11.EFFSEQ) FROM PS_JOB A11 WHERE A11.EMPLID = A1.EMPLID AND A11.EMPL_RCD = A1.EMPL_RCD AND A11.EFFDT = A1.EFFDT)) AND NOT EXISTS ( SELECT 'X' FROM PS_DEPT_TBL B1 WHERE B1.SETID = B.SETID AND B1.DEPTID = B.DEPTID AND B1.EFFDT < A.EFFDT AND B1.EFFDT > B.EFFDT)) OR A.EFFDT = ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A2.EMPLID = A.EMPLID AND A2.EMPL_RCD = A.EMPL_RCD AND A2.SETID_DEPT = A.SETID_DEPT AND A2.DEPTID = A.DEPTID AND A2.EFFDT <= B.EFFDT AND A2.EFFSEQ = ( SELECT MAX(A22.EFFSEQ) FROM PS_JOB A22 WHERE A22.EMPLID = A2.EMPLID AND A22.EMPL_RCD = A2.EMPL_RCD AND A22.EFFDT = A2.EFFDT))) AND A.EFFSEQ = ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A3.EMPLID = A.EMPLID AND A3.EMPL_RCD = A.EMPL_RCD AND A3.EFFDT = A.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
2 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr