RELEV_ID_ESP_VW

(SQL View)
 Index Back

Substituted Emplid Name Prompt

Only Contracts checked as Partial Retirement ( CNT_REASON_ESP = 'JUB') are elegibles to be Replaced.

SELECT A.EMPLID ,A.EFFDT ,B.NAME ,B.LAST_NAME ,B.SECOND_LAST_NAME ,B.FIRST_NAME ,A.CONTRACT_TYPE ,C.DESCR50 ,C.CNT_REASON_ESP ,A.CNT_PT_PCT_ESP ,A.CONTRACT_NUM ,A.CNT_BGN_DT_ESP ,A.CNT_END_DT_ESP ,A.CONTRACT_ID_ESP ,A.CNT_INEM_DT_ESP ,A.HIRE_CTR_CD_ESP ,D.BIRTHDATE ,J.LOCATION ,J.JOBCODE ,J.LABOR_AGREEMENT ,J.EMPL_CTG ,J.BUSINESS_UNIT ,J.REG_REGION ,H.EMPLMNT_OFF_CD_ESP FROM PS_WKF_CNT_TYP_ESP A , PS_PERSON_NAME B , PS_PERS_CNTRCT_TYP C , PS_PERSON D , PS_JOB J , PS_HIRING_CTR_ESP H WHERE A.EMPLID = B.EMPLID AND A.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 A.CNT_ACTION_ESP <> '002' AND C.SETID = 'ESP' AND A.CONTRACT_TYPE = C.CONTRACT_TYPE AND C.EFFDT = ( SELECT MAX(C1.EFFDT) FROM PS_PERS_CNTRCT_TYP C1 WHERE C1.CONTRACT_TYPE = C.CONTRACT_TYPE AND C1.EFFDT <= A.EFFDT) AND A.EMPLID = D.EMPLID AND A.EMPLID = J.EMPLID AND A.CONTRACT_NUM = J.CONTRACT_NUM AND J.EFFDT = ( SELECT MAX(EJ.EFFDT) FROM PS_JOB EJ WHERE A.EMPLID = EJ.EMPLID AND A.CONTRACT_NUM = EJ.CONTRACT_NUM AND EJ.EFFDT <= A.EFFDT) AND A.HIRE_CTR_CD_ESP = H.HIRE_CTR_CD_ESP AND H.EFFDT = ( SELECT MAX (HED.EFFDT) FROM PS_HIRING_CTR_ESP HED WHERE H.HIRE_CTR_CD_ESP = HED.HIRE_CTR_CD_ESP AND HED.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

    Prompt Table: PERSON_NAME

    2 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    3 NAME Character(50) VARCHAR2(50) NOT NULL Name
    4 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name
    5 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name
    6 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
    7 CONTRACT_TYPE Character(3) VARCHAR2(3) NOT NULL Contract Type

    Default Value: 100

    Prompt Table: CNT_TYPE_VW_ESP

    8 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50
    9 CNT_REASON_ESP Character(3) VARCHAR2(3) NOT NULL Contract Reason
    DIS=Disabled People
    FEU=European Funds
    FOM=Employment Promotion
    FOR=Training
    INS=Insertion
    INT=Interinity
    INT=Replacement
    JUB=Partial Retirement
    ORD=Regular
    PRO=Production
    REL=Relieve
    SER=Service
    10 CNT_PT_PCT_ESP Number(6,2) DECIMAL(5,2) NOT NULL Percentage of Part Time
    11 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
    12 CNT_BGN_DT_ESP Date(10) DATE Contract Begin Dt
    13 CNT_END_DT_ESP Date(10) DATE Contract End Date
    14 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
    15 CNT_INEM_DT_ESP Date(10) DATE SEPE Registration Date
    16 HIRE_CTR_CD_ESP Character(3) VARCHAR2(3) NOT NULL Hiring Center

    Prompt Table: HIRING_CTR_ESP

    17 BIRTHDATE Date(10) DATE Date of Birth
    18 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

    Prompt Table: LOCATION_TBL

    19 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

    Prompt Table: JOBCODE_TBL

    20 LABOR_AGREEMENT Character(6) VARCHAR2(6) NOT NULL Labor Agreement
    21 EMPL_CTG Character(6) VARCHAR2(6) NOT NULL Employee Category

    Prompt Table: EMPL_CTG_L1_VW

    22 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    23 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region
    24 EMPLMNT_OFF_CD_ESP Character(8) VARCHAR2(8) NOT NULL Employment Office Code.

    Prompt Table: SEPE_ESP_VW