EMPL_CTG_ESP_VW

(SQL View)
 Index Back

Employee Category View ESP


SELECT A.EMPLID , A.EFFDT , A.CONTRACT_NUM , B.SETID , A.LABOR_AGREEMENT , A.EMPL_CTG , A.EMPL_CTG_L1 , A.EMPL_CTG_L2 , B.DESCR , B.SOC_SEC_WRK_GRP_CD FROM PS_JOB A , PS_EMPL_CTG_L1 B WHERE A.SETID_LBR_AGRMNT = B.SETID AND A.LABOR_AGREEMENT = B.LABOR_AGREEMENT AND A.EMPL_CTG = B.EMPL_CTG AND B.EFFDT = ( SELECT MAX (ED.EFFDT) FROM PS_EMPL_CTG_L1 ED WHERE A.SETID_LBR_AGRMNT = ED.SETID AND A.LABOR_AGREEMENT = ED.LABOR_AGREEMENT AND A.EMPL_CTG = ED.EMPL_CTG 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 LABOR_AGREEMENT Character(6) VARCHAR2(6) NOT NULL Labor Agreement

    Prompt Table: EMPL_CTG

    6 EMPL_CTG Character(6) VARCHAR2(6) NOT NULL Employee Category

    Prompt Table: EMPL_CTG_L1

    7 EMPL_CTG_L1 Character(6) VARCHAR2(6) NOT NULL Employee Subcategory

    Prompt Table: EMPL_CTG_L2

    8 EMPL_CTG_L2 Character(6) VARCHAR2(6) NOT NULL Employee Subcategory 2
    9 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    10 SOC_SEC_WRK_GRP_CD Character(2) VARCHAR2(2) NOT NULL Social Security Work Group
    01=01-Engineers
    02=02-Technicals
    03=03-Administrative
    04=04-Assistants
    05=05-White Collar
    06=06-Subordinates
    07=07-Administrative assistants
    08=08-1st and 2nd degree
    09=09-3rd degree
    10=10-Over 18 years
    11=11-Under 18 years