GPS_CNTRCTMAPVW

(SQL View)
Index Back

Contract Type Map View

Prompt view against GPS_EMPCAT_CNTR, JOB and CONTRACT_DATA.

SELECT A.EMPLID , A.CONTRACT_NUM , C.EFFDT , C.CONTRACT_TYPE , D.DESCR FROM PS_CONTRACT_DATA A , PS_JOB B , PS_GPS_EMPCAT_CNTR C , PS_PERS_CNTRCT_TYP D WHERE B.EMPLID = A.EMPLID AND B.CONTRACT_NUM = A.CONTRACT_NUM AND B.EFFDT = ( SELECT MIN(B1.EFFDT) FROM PS_JOB B1 WHERE B1.EMPLID = B.EMPLID AND B1.EMPL_RCD = B.EMPL_RCD AND B1.EFFDT >= A.CONTRACT_BEGIN_DT) AND B.EFFSEQ = ( SELECT MAX (B2.EFFSEQ ) FROM PS_JOB B2 WHERE B2.EMPLID = B.EMPLID AND B2.EMPL_RCD = B.EMPL_RCD AND B2.EFFDT = B.EFFDT) AND C.SETID = B.SETID_LBR_AGRMNT AND C.LABOR_AGREEMENT = B.LABOR_AGREEMENT AND C.EMPL_CTG = B.EMPL_CTG AND %EffdtCheck(GPS_EMPCAT_CNTR C1, C, B.EFFDT) AND D.SETID = C.SETID AND D.CONTRACT_TYPE = C.CONTRACT_TYPE AND %EffdtCheck(PERS_CNTRCT_TYP D1, D, C.EFFDT) AND D.EFF_STATUS = 'A'

  • Related Language Record: GPS_CNTRCTMAPVL
  • # 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 CONTRACT_TYPE Character(3) VARCHAR2(3) NOT NULL Contract Type

    Prompt Table: PERS_CNTRCT_TYP

    5 DESCR Character(30) VARCHAR2(30) NOT NULL Description