EMPLIDEMPL_RCDINCIDENT_DTINCIDENT_NBRXML_JOB_ESP_VW(SQL View) |
Index Back |
|---|---|
Job Data DeltaAllow load for Delta- Hire_dt, contract... |
| SELECT A.EMPLID , A.EMPL_RCD , SE.INCIDENT_DT , SE.INCIDENT_NBR ,A.HIRE_DT ,A.SOC_SEC_RISK_CODE ,A.COMPANY ,A.ESTABID ,A.LABOR_AGREEMENT ,A.CONTRACT_NUM ,JR.SSN_EMPLOYER FROM PS_JOB A ,PS_XML_DLT_INJ_VW SE ,PS_JOB_JR JR WHERE A.EMPLID=SE.EMPLID AND A.EMPL_RCD=SE.EMPL_RCD AND A.EFFDT<=SE.INCIDENT_DT AND A.EMPLID = JR.EMPLID AND A.EMPL_RCD=JR.EMPL_RCD AND A.EFFDT=JR.EFFDT AND A.EFFSEQ=JR.EFFSEQ AND ( A.EFFDT = ( SELECT MAX(A_EX.EFFDT) FROM PS_JOB A_EX ,PS_XML_DLT_INJ_VW SE1 WHERE A.EMPLID = A_EX.EMPLID AND A.EMPL_RCD = A_EX.EMPL_RCD AND A_EX.EMPLID=SE1.EMPLID AND A_EX.EMPL_RCD=SE1.EMPL_RCD AND A_EX.EFFDT<=SE1.INCIDENT_DT ) AND A.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.EMPL_RCD = A_ES.EMPL_RCD AND A.EFFDT = A_ES.EFFDT) ) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Rcd Nbr |
| 3 | INCIDENT_DT |
Date(10) | DATE | Incident Date |
| 4 | INCIDENT_NBR |
Character(8) | VARCHAR2(8) NOT NULL | Incident Number |
| 5 | HIRE_DT | Date(10) | DATE | First Start Date |
| 6 | SOC_SEC_RISK_CODE | Character(3) | VARCHAR2(3) NOT NULL | Occupation Code |
| 7 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 8 | ESTABID | Character(12) | VARCHAR2(12) NOT NULL | Establishment ID |
| 9 | LABOR_AGREEMENT | Character(6) | VARCHAR2(6) NOT NULL | Labor Agreement |
| 10 | CONTRACT_NUM | Character(25) | VARCHAR2(25) NOT NULL | Contract Number |
| 11 | SSN_EMPLOYER | Character(13) | VARCHAR2(13) NOT NULL | Employer Social Security number |