GPS_CNTRCTMAPVW(SQL View) |
Index Back |
---|---|
Contract Type Map ViewPrompt 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' |
# | 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 |