PGM_ACTIVEEMPVW

(SQL View)
 Index Back

View of Active employees

Looks for all employees defined in PERSONAL_DATA and checks the employee status in the JOB record to retrieve only the currently active employees.

SELECT A.EMPLID ,A.NAME , A.LAST_NAME , A.FIRST_NAME FROM PS_PERSONAL_DATA A , PS_JOB B WHERE A.EMPLID = B.EMPLID AND B.EMPL_STATUS IN( 'A','P','L','W') AND B.EFFDT = ( SELECT MAX(B2.EFFDT) FROM PS_JOB B2 WHERE B.EMPLID = B2.EMPLID AND B.EMPL_RCD = B2.EMPL_RCD AND B2.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(B3.EFFSEQ) FROM PS_JOB B3 WHERE B.EMPLID = B3.EMPLID AND B.EMPL_RCD = B3.EMPL_RCD AND B.EFFDT = B3.EFFDT) AND B.EMPL_RCD = ( SELECT MIN(B0.EMPL_RCD) FROM PS_JOB B0 WHERE B.EMPLID = B0.EMPLID AND B0.EMPL_STATUS IN( 'A','P','L','W') AND B0.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_JOB B1 WHERE B1.EMPLID = B0.EMPLID AND B1.EMPL_RCD = B0.EMPL_RCD AND B1.EFFDT <= %CurrentDateIn) AND B0.EFFSEQ = ( SELECT MAX(B4.EFFSEQ) FROM PS_JOB B4 WHERE B4.EMPLID = B0.EMPLID AND B4.EMPL_RCD = B0.EMPL_RCD AND B4.EFFDT = B0.EFFDT))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 NAME Character(50) VARCHAR2(50) NOT NULL Name
3 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
4 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name