EMPL_FT_ESP_VW

(SQL View)
 Index Back

Employee Job Data FT VW ESP

Full Time Job Data

SELECT A.EMPLID , A.CONTRACT_NUM , A.EFFDT , CASE WHEN B.CNT_SCHED_TYPE_ESP = 'F' THEN %NumToChar(J.STD_HOURS) ELSE ' ' END , CASE WHEN B.CNT_SCHED_TYPE_ESP = 'P' THEN %NumToChar(A.PT_MAX_HRS_ESP) ELSE ' ' END , CASE WHEN B.CNT_REASON_ESP = 'JUB' THEN %NumToChar(A.CNT_PT_PCT_ESP)%Concat'%' ELSE ' ' END , A.PART_TIME_PERC_ESP , J.FULL_PART_TIME , B.CNT_DURTN_TYPE_ESP , B.CNT_SCHED_TYPE_ESP FROM PS_JOB J , PS_WKF_CNT_TYP_ESP A , PS_PERS_CNTRCT_TYP B WHERE J.EMPLID = A.EMPLID AND J.CONTRACT_NUM = A.CONTRACT_NUM AND A.CONTRACT_TYPE = B.CONTRACT_TYPE AND J.EFFDT = ( SELECT MAX (ED2.EFFDT) FROM PS_JOB ED2 WHERE ED2.EMPLID = J.EMPLID AND ED2.CONTRACT_NUM = J.CONTRACT_NUM AND ED2.EFFDT <= A.EFFDT) AND B.SETID = 'ESP' AND B.EFFDT = ( SELECT MAX (ED.EFFDT) FROM PS_PERS_CNTRCT_TYP ED WHERE ED.SETID = 'ESP' AND ED.CONTRACT_TYPE = B.CONTRACT_TYPE AND ED.EFFDT <= A.EFFDT)

  • 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 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
    3 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    4 FULL_TIME_HRS_ESP Character(8) VARCHAR2(8) NOT NULL Bonificated Collective. Used for Bonificated Contracts
    5 MAX_HRS_ESP Character(8) VARCHAR2(8) NOT NULL Bonificated Collective. Used for Bonificated Contracts
    6 PART_TIME_PCT_ESP Character(5) VARCHAR2(5) NOT NULL Part Time Percent. Char to Print.
    7 PART_TIME_PERC_ESP Number(3,0) SMALLINT NOT NULL Percentage of Part Time
    8 FULL_PART_TIME Character(1) VARCHAR2(1) NOT NULL Full/Part Time
    D=On Demand
    F=Full-Time
    P=Part-Time
    9 CNT_DURTN_TYPE_ESP Character(1) VARCHAR2(1) NOT NULL Contract Duration Type
    D=Fix Duration
    I=Permanent
    T=Temporary
    10 CNT_SCHED_TYPE_ESP Character(1) VARCHAR2(1) NOT NULL Schedule Type
    D=Permanent Intermitent
    F=Full Time
    P=Part Time