GPDE_SS_RCD_VW

(SQL View)
 Index Back

ESS - Retrieve Descr for Job

ESS Germany - this view returns descriptions for Departments and JobCodes of an employee

SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.DEPTID , A.SETID_DEPT , B.DESCR , A.JOBCODE , A.SETID_JOBCODE , C.DESCR , A.SUPERVISOR_ID , A.EMPL_STATUS FROM PS_JOB A , PS_DEPT_TBL B , PS_JOBCODE_TBL C WHERE A.EFFDT=( SELECT MAX(EFFDT) FROM PS_JOB JOB WHERE JOB.EMPLID=A.EMPLID AND JOB.EMPL_RCD=A.EMPL_RCD AND JOB.EFFDT<=%CurrentDateIn) AND EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = A.EMPLID AND EMPL_RCD = A.EMPL_RCD AND EFFDT = A.EFFDT) AND A.SETID_JOBCODE = C.SETID AND A.JOBCODE = C.JOBCODE AND %EffdtCheck(JOBCODE_TBL, C, A.EFFDT) AND C.EFF_STATUS = 'A' AND A.SETID_DEPT = B.SETID AND A.DEPTID = B.DEPTID AND %EffdtCheck(DEPT_TBL, B, A.EFFDT) AND B.EFF_STATUS = 'A'

  • Related Language Record: GPDE_SSEEVWLNG

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

    Prompt Table: DEPT_TBL

    4 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department SetID
    5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    6 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

    Prompt Table: JOBCODE_TBL

    7 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code SetID
    8 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    9 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID
    10 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
    A=Active
    D=Deceased
    L=Leave of Absence
    P=Leave With Pay
    Q=Retired With Pay
    R=Retired
    S=Suspended
    T=Terminated
    U=Terminated With Pay
    V=Terminated Pension Pay Out
    W=Short Work Break
    X=Retired-Pension Administration