HR_DTLN_EMP_VW

(SQL View)
 Index Back

Active Employee View

view used to retrieve active employees current as of a particular date.

SELECT DISTINCT A.EMPLID , B.NAME_DISPLAY , A.SETID_DEPT , A.DEPTID , C.DESCR ,A.SETID_JOBCODE ,A.JOBCODE ,D.DESCR FROM PS_JOB A , PS_PERSON_NAME B , PS_SET_DEPT_VW C ,PS_SET_JOBCD_VW2 D WHERE A.EMPLID = B.EMPLID AND A.EMPL_STATUS IN ('A','L','P','S','W') AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.EFFDT<= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB A2 WHERE A2.EMPLID = A.EMPLID AND A2.EMPL_RCD = A.EMPL_RCD AND A2.EFFDT = A.EFFDT) AND A.SETID_DEPT = C.SETID_DEPT AND A.DEPTID = C.DEPTID AND %EffdtCheck(SET_DEPT_VW DEPT_ED, C, %CurrentDateIn) AND A.SETID_JOBCODE = D.SETID_JOBCODE AND A.jobcode = D.jobcode AND %EffdtCheck(SET_JOBCD_VW2 JOBCD_ED, D, %CurrentDateIn)

  • Related Language Record: HR_DTLN_EMP_LW

    • # 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 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID

    Prompt Table: SETID_TBL

    4 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: HR_DTLN_DEPT_VW

    5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    6 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code Set ID

    Prompt Table: SETID_TBL

    7 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

    Prompt Table: HR_DTLN_JBCD_VW

    8 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr