RELV_DAT_ESP_VW

(SQL View)
 Index Back

Part Retirement Data ESP VW

Part Retiremnt Data ESP VW

SELECT A.EMPLID ,A.CONTRACT_NUM ,A.EFFDT ,B.NAME ,B.LAST_NAME_SRCH ,B.SECOND_LAST_SRCH ,B.FIRST_NAME_SRCH ,A.SUBSTN_EMPLID_ESP ,D.CNT_BGN_DT_ESP ,D.CNT_END_DT_ESP ,D.CONTRACT_TYPE ,D.EMPLMNT_OFF_CD_ESP ,D.CONTRACT_ID_ESP ,D.CNT_INEM_DT_ESP ,J.LOCATION ,LOC.DESCR ,J.JOBCODE ,JC.DESCR ,J.EMPL_CTG_L1 ,E.BIRTHDATE FROM PS_WKF_CNT_TYP_ESP A , PS_PERSON_NAME B , PS_PERS_CNTRCT_TYP C , PS_WKF_CNT_TYP_ESP D , PS_JOB J , PS_PERSON E , PS_LOCATION_TBL LOC , PS_JOBCODE_TBL JC WHERE D.EMPLID = B.EMPLID AND D.EMPLID = E.EMPLID AND A.SUBSTN_EMPLID_ESP = D.EMPLID AND D.CNT_ACTION_ESP = '001' AND D.CONTRACT_TYPE = C.CONTRACT_TYPE AND D.EFFDT = ( SELECT MAX (ED.EFFDT) FROM PS_WKF_CNT_TYP_ESP ED WHERE ED.EMPLID = A.EMPLID AND ED.CONTRACT_NUM = A.CONTRACT_NUM ) AND C.SETID = 'ESP' AND C.EFFDT = ( SELECT MAX (EC.EFFDT) FROM PS_PERS_CNTRCT_TYP EC WHERE EC.CONTRACT_TYPE = C.CONTRACT_TYPE AND EC.SETID = 'ESP' AND EC.EFFDT <= A.EFFDT) AND C.CNT_REASON_ESP = 'JUB' AND J.EMPLID = D.EMPLID AND J.CONTRACT_NUM = D.CONTRACT_NUM AND J.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = D.EMPLID AND JOB3.CONTRACT_NUM = D.CONTRACT_NUM AND JOB3.EFFDT <= A.EFFDT) AND LOC.LOCATION = J.LOCATION AND LOC.SETID = J.SETID_LOCATION AND LOC.EFFDT = ( SELECT MAX (EDL.EFFDT) FROM PS_LOCATION_TBL EDL WHERE EDL.SETID = LOC.SETID AND EDL.EFFDT <= A.EFFDT) AND JC.JOBCODE = J.JOBCODE AND JC.SETID = J.SETID_JOBCODE AND JC.EFFDT = ( SELECT MAX (EDJ.EFFDT) FROM PS_JOBCODE_TBL EDJ WHERE EDJ.SETID = JC.SETID AND EDJ.EFFDT <= A.EFFDT)

  • Parent record: PER_ORG_ASGN

    • # 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 Effective Date

    Default Value: %date

    4 NAME Character(50) VARCHAR2(50) NOT NULL Name
    5 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    6 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Last Name
    7 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name
    8 SUBSTN_EMPLID_ESP Character(11) VARCHAR2(11) NOT NULL Replaced Empl ID

    Prompt Table: RELEV_ID_ESP_VW

    9 CNT_BGN_DT_ESP Date(10) DATE Contract Begin Dt
    10 CNT_END_DT_ESP Date(10) DATE Contract End Date
    11 CONTRACT_TYPE Character(3) VARCHAR2(3) NOT NULL Contract Type

    Prompt Table: CNT_TYPE_VW_ESP

    12 EMPLMNT_OFF_CD_ESP Character(8) VARCHAR2(8) NOT NULL Employment Office Code.

    Prompt Table: SEPE_ESP_VW

    13 CONTRACT_ID_ESP Character(18) VARCHAR2(18) NOT NULL Contract ID assigned by the Employment Agency
    C=Company
    E=Employee
    N=None
    S=SS Contribution Center
    14 CNT_INEM_DT_ESP Date(10) DATE SEPE Registration Date
    15 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

    Prompt Table: LOCATION_TBL

    16 DESCR_LOCATION Character(30) VARCHAR2(30) NOT NULL Description
    17 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
    18 DESCR_JOBCODE Character(30) VARCHAR2(30) NOT NULL Description
    19 EMPL_CTG_L1 Character(6) VARCHAR2(6) NOT NULL Employee Subcategory
    20 BIRTHDATE Date(10) DATE Date of Birth