EMPLIDEFFDTDefault Value: %date
Prompt Table: PERS_CNTRCT_TYP
Default Value: %date
CTPR_CNT_VW_ESP(SQL View) |
Index Back |
|---|---|
Contract Printing View ESPContract Printing View ESP |
| SELECT CD.EMPLID , JB.EFFDT , CD.CONTRACT_NUM , A.COMPANY , JB.REG_REGION , C.CONTRACT_TYPE , C.EFFDT , CD.CONTRACT_BEGIN_DT , JR.SSN_EMPLOYER , ND.XML_NODE_ID FROM PS_CONTRACT_DATA CD , PS_PERSDATA_ESP_VW EMPL , PS_JOB JB , PS_JOB_JR JR , PS_WKF_CNT_TYPE C LEFT OUTER JOIN PS_WKF_CNT_TYP_ESP ND ON C.EMPLID = ND.EMPLID AND C.CONTRACT_NUM = ND.CONTRACT_NUM AND C.EFFDT = ND.EFFDT , PS_COMPANY_TBL A WHERE JB.EMPLID = JR.EMPLID AND JB.EMPL_RCD = JR.EMPL_RCD AND JB.JOB_INDICATOR = 'P' AND JB.EFFSEQ = JR.EFFSEQ AND JB.EFFDT = JR.EFFDT AND JB.EMPLID = CD.EMPLID AND JB.CONTRACT_NUM = CD.CONTRACT_NUM AND CD.EMPLID = JB.EMPLID AND CD.EMPLID = C.EMPLID AND CD.CONTRACT_NUM = C.CONTRACT_NUM AND JB.HR_STATUS = 'A' AND JB.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE JB.EMPLID = A_ED.EMPLID AND JB.EMPL_RCD = A_ED.EMPL_RCD AND JB.CONTRACT_NUM = A_ED.CONTRACT_NUM AND A_ED.HR_STATUS = 'A' AND (A_ED.EFFDT >= CD.CONTRACT_BEGIN_DT OR (A_ED.EFFDT <= %CurrentDateIn AND CD.CONTRACT_BEGIN_DT IS NULL)) AND JB.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE JB.EMPLID = A_ES.EMPLID AND JB.EMPL_RCD = A_ES.EMPL_RCD AND JB.CONTRACT_NUM = A_ES.CONTRACT_NUM AND A_ES.HR_STATUS = 'A' AND JB.EFFDT = A_ES.EFFDT) ) AND CD.EMPLID = EMPL.EMPLID AND CD.EMPLID = JB.EMPLID AND A.COMPANY = JB.COMPANY AND A.EFF_STATUS = 'A' AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_COMPANY_TBL A1 WHERE A1.COMPANY = A.COMPANY AND A1.EFF_STATUS = 'A' AND A1.EFFDT <= CD.CONTRACT_BEGIN_DT ) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EFFDT |
Date(10) | DATE |
Effective Date
Default Value: %date |
| 3 | CONTRACT_NUM | Character(25) | VARCHAR2(25) NOT NULL | Contract Number |
| 4 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 5 | REG_REGION | Character(5) | VARCHAR2(5) NOT NULL | Regulatory Region |
| 6 | CONTRACT_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
Contract Type
Prompt Table: PERS_CNTRCT_TYP |
| 7 | CONTRACT_TYPE_DT | Date(10) | DATE |
Contract Type Date
Default Value: %date |
| 8 | CONTRACT_BEGIN_DT | Date(10) | DATE | Contract Begin Date |
| 9 | SSN_EMPLOYER | Character(13) | VARCHAR2(13) NOT NULL | Employer Social Security number |
| 10 | XML_NODE_ID | Character(6) | VARCHAR2(6) NOT NULL | Root node |