JOB_ALL_VW

(SQL View)
 Index Back

Curent Job - All Persons

Selects all Persons with HR status active in JOB and shows their NAMES for each EMPLID. This view will include Employees, Contingent Workers, and Persons of Interest.

SELECT J.EMPLID ,A.NAME_DISPLAY ,A.LAST_NAME_SRCH ,A.FIRST_NAME_SRCH ,A.SECOND_LAST_SRCH ,A.NAME_AC FROM PS_NAMES A , PS_JOB J WHERE A.NAME_TYPE = 'PRI' AND J.EMPLID = A.EMPLID AND J.HR_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_NAMES B WHERE B.EMPLID = A.EMPLID AND B.NAME_TYPE = A.NAME_TYPE AND B.EFFDT <= %CurrentDateIn ) AND J.EFFDT = ( SELECT MAX(JOB2.EFFDT) FROM PS_JOB JOB2 WHERE J.EMPLID = JOB2.EMPLID AND J.EMPL_RCD = JOB2.EMPL_RCD AND JOB2.EFFDT <= %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(JOB3.EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = J.EMPLID AND JOB3.EMPL_RCD = J.EMPL_RCD AND JOB3.EFFDT = J.EFFDT)

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country
3 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
4 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
5 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
6 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name