JOBCODE_ESP_VW

(SQL View)
 Index Back

Employee JobCode VW ESP


SELECT A.EMPLID , A.EFFDT , A.CONTRACT_NUM , B.SETID , A.JOBCODE , B.DESCR , B.CNO_ESP FROM PS_JOB A , PS_JOBCODE_TBL B WHERE A.SETID_JOBCODE = B.SETID AND A.JOBCODE = B.JOBCODE AND B.EFFDT = ( SELECT MAX (ED.EFFDT) FROM PS_JOBCODE_TBL ED WHERE A.SETID_JOBCODE = ED.SETID AND A.JOBCODE = ED.JOBCODE AND ED.EFFDT <= A.EFFDT) AND A.EFFSEQ= ( SELECT MAX(A_ED.EFFSEQ) FROM PS_JOB A_ED WHERE A.EMPLID=A_ED.EMPLID AND A.EFFDT=A_ED.EFFDT AND A.CONTRACT_NUM=A_ED.CONTRACT_NUM)

  • Parent record: EMPL_CTG_L2

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

    Default Value: %date

    3 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
    4 SETID Character(5) VARCHAR2(5) NOT NULL SetID

    Default Value: OPR_DEF_TBL_HR.SETID

    Prompt Table: SETID_TBL

    5 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    6 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    7 CNO_ESP Character(4) VARCHAR2(4) NOT NULL Occupational Code

    Prompt Table: CNO4_ESPVW