JOB_DATE_VW

(SQL View)
 Index Back

Job Date View

JOB_DATE_VW is used within the Inquiry on Multiple Jobs to select those job records where either the primary job indicator or the employment status is different from the previous effective dated job record. The employment status is considered to be Active when the EMPL_STATUS is either 'A', 'L', 'P', 'S' or 'W'. Inactive otherwise. This view detects changes from Active to Inactive and vice versa and changes from 'Primary' to 'Non-primary' and vice versa.

SELECT DISTINCT A.EMPLID ,A.EFFDT FROM PS_JOB A WHERE A.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = A.EMPLID AND EMPL_RCD = A.EMPL_RCD AND EFFDT = A.EFFDT) AND NOT EXISTS ( SELECT 'X' FROM PS_JOB B WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = B.EMPLID AND EMPL_RCD = B.EMPL_RCD AND EFFDT < A.EFFDT) AND B.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = B.EMPLID AND EMPL_RCD = B.EMPL_RCD AND EFFDT = B.EFFDT) AND B.JOB_INDICATOR = A.JOB_INDICATOR AND ((B.EMPL_STATUS IN('A','L','P','S','W') AND A.EMPL_STATUS IN('A','L','P','S','W')) OR (B.EMPL_STATUS NOT IN('A','L','P','S','W') AND A.EMPL_STATUS NOT IN('A','L','P','S','W'))))

  • Parent record: PERSON

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 ASOFDATE Date(10) DATE As of Date