XML_JOB_ESP_VW

(SQL View)
 Index Back

Job Data Delta

Allow 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 ,A.JOBCODE ,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 Record
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 Social Security Risk Code
7 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
8 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID

Prompt Table: ESTAB_TBL

9 LABOR_AGREEMENT Character(6) VARCHAR2(6) NOT NULL Labor Agreement
10 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
11 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code
12 SSN_EMPLOYER Character(13) VARCHAR2(13) NOT NULL Employer Social Security number

Prompt Table: ALL_CCC_ESP_VW