XML_DLT_ESP_VW

(SQL View)
 Index Back

ESP Delta Injury Data

View Delta for Incident & Injury Data

SELECT A.EMPLID ,C.INCIDENT_DT ,A.ABS_BEGIN_DT ,A.EMPL_RCD ,A.INCIDENT_NBR ,A.DLT_INC_TYPE_ESP ,A.DLT_RELAPSE_ESP ,'1' ,A.DLT_CNO_ESP ,A.DLT_SEN_MONTH_ESP ,A.DLT_SEN_DAYS_ESP ,'01' ,A.DLT_NBR_OF_EMPL ,A.DLT_HIR ,A.DLT_ETT ,A.DLT_PLACE_CODE_ESP ,D.DLT_CAR_INC_ESP ,C.COUNTRY ,C.STATE ,C.CITY ,C.ADDRESS1 ,C.NUM1 ,A.DLT_EMPL_COMP ,A.FISCAL_ID_CD4_ESP ,A.FISCAL_ID_CD2_ESP ,A.FISCAL_ID_CD3_ESP ,A.DLT_COMP_NM_ESP ,A.DLT_ADDRESS1_ESP ,A.DLT_STATE_ESP ,A.DLT_CITY_ESP ,A.DLT_POSTAL_ESP ,A.DLT_PHONE_ESP ,A.DLT_STAFF_ESP ,A.DLT_SSN_EMPLER_ESP ,A.DLT_INDUSTRYCD_ESP ,A.DLT_PLACE_TYPE_ESP ,A.DLT_WORK_TYPE_ESP ,A.DLT_TASK_ESP ,A.DLT_USUAL_TOOL_ESP ,A.DLT_WORKHOUR_ESP ,A.DLT_INCWORK_ESP ,A.DLT_RISK_EVAL_ESP ,A.DLT_DEVIATION_ESP ,A.DLT_UNUS_TOOL_ESP ,A.DLT_SOURCE_ESP ,A.DLT_EMPL_FLG_ESP ,A.DLT_WITNS_FLG_ESP ,A.DLT_WITNS_DATA_ESP ,A.DLT_INJURY_ESP ,A.DLT_INJRY_GRD_ESP ,A.DLT_BODY_PART_ESP ,A.DLT_ASIST_TYPE_ESP ,A.CONTRIB_BASE_ESP ,A.CONTRIB_DAYS_ESP ,A.REG_BASE_ESP ,A.CONTRIB_OVER_ESP ,A.SUM_B1_B2_ESP ,A.AVERAGE_ESP ,A.AVERAGE_A_ESP ,A.AVERAGE_B_ESP ,A.AVERAGE_TOTAL_ESP ,A.AVERAGE_BNFIT_ESP ,A.RESPONSIBLE_ROLE ,A.STATE2 ,A.PRESENTED_DT FROM PS_INJURY_ILL_ESP A , PS_INJURY_ILL B , PS_INCIDENT_DATA C , PS_INCDNT_DATA_ESP D WHERE A.INCIDENT_NBR = C.INCIDENT_NBR AND A.INCIDENT_NBR = D.INCIDENT_NBR AND A.EMPLID = B.EMPLID AND A.INCIDENT_NBR = B.INCIDENT_NBR

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 INCIDENT_DT Date(10) DATE Incident Date
3 ABS_BEGIN_DT Date(10) DATE Absence Begin Date
4 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
5 INCIDENT_NBR Character(8) VARCHAR2(8) NOT NULL Incident Number
6 DLT_INC_TYPE_ESP Character(1) VARCHAR2(1) NOT NULL Incident Type
P=Industrial Acc. Sick Note
R=Industrial Acc. No Sick Note
7 DLT_RELAPSE_ESP Character(1) VARCHAR2(1) NOT NULL Relapse
1=Accident
2=Relapse
8 STATUS Character(1) VARCHAR2(1) NOT NULL Status Indicator
A=Active
I=Inactive
9 DLT_CNO_ESP Character(4) VARCHAR2(4) NOT NULL Occupational Code

Prompt Table: DLT_CNO_ESP_VW

10 DLT_SEN_MONTH_ESP Character(3) VARCHAR2(3) NOT NULL Seniority Months
11 DLT_SEN_DAYS_ESP Character(2) VARCHAR2(2) NOT NULL Seniority Days
12 SCHEME_ID_ESP Character(4) VARCHAR2(4) NOT NULL Scheme ID - ESP
13 DLT_NBR_OF_EMPL Number(6,0) INTEGER NOT NULL Number of Employees in the company.
14 DLT_HIR Character(1) VARCHAR2(1) NOT NULL Hiring Flag
15 DLT_ETT Character(1) VARCHAR2(1) NOT NULL Temporary Work Company
16 DLT_PLACE_CODE_ESP Character(1) VARCHAR2(1) NOT NULL Place Code
1=Usual Work Center
2=Taken Journey
3=In Itinere
4=Other Work Center
17 DLT_CAR_INC_ESP Character(1) VARCHAR2(1) NOT NULL Car Incident
18 COUNTRY_OTHER Character(3) VARCHAR2(3) NOT NULL Country field for ADDR_OTR_SBR and DERIVED_ADDR_OT
19 STATE_OTHER Character(6) VARCHAR2(6) NOT NULL State Other
20 CITY_OTHER Character(30) VARCHAR2(30) NOT NULL City Other
21 ADDRESS1_OTHER Character(55) VARCHAR2(55) NOT NULL Address 1 Other
22 NUM1_OTHER Character(6) VARCHAR2(6) NOT NULL Number 1 Other
23 DLT_EMPL_COMP Character(1) VARCHAR2(1) NOT NULL Center belongs to EE Company
24 FISCAL_ID_CD4_ESP Character(10) VARCHAR2(10) NOT NULL Fiscal ID Hiring
25 FISCAL_ID_CD2_ESP Character(10) VARCHAR2(10) NOT NULL Fiscal ID ETT
26 FISCAL_ID_CD3_ESP Character(10) VARCHAR2(10) NOT NULL Fiscal ID Other
27 DLT_COMP_NM_ESP Character(30) VARCHAR2(30) NOT NULL Company Name Delta File
28 DLT_ADDRESS1_ESP Character(60) VARCHAR2(60) NOT NULL Address Company
29 DLT_STATE_ESP Character(2) VARCHAR2(2) NOT NULL State Company
30 DLT_CITY_ESP Character(5) VARCHAR2(5) NOT NULL City Company
31 DLT_POSTAL_ESP Character(5) VARCHAR2(5) NOT NULL Postal Company
32 DLT_PHONE_ESP Character(14) VARCHAR2(14) NOT NULL Phone Company
33 DLT_STAFF_ESP Character(5) VARCHAR2(5) NOT NULL Staff
34 DLT_SSN_EMPLER_ESP Character(11) VARCHAR2(11) NOT NULL SSN Employer
35 DLT_INDUSTRYCD_ESP Character(5) VARCHAR2(5) NOT NULL Industry

Prompt Table: INDSTRY_ACT_ESP

36 DLT_PLACE_TYPE_ESP Character(3) VARCHAR2(3) NOT NULL Industrial Accident Place Type

Prompt Table: DLT_PLC_ESP_VW

37 DLT_WORK_TYPE_ESP Character(2) VARCHAR2(2) NOT NULL Work Type

Prompt Table: DLT_WRK_ESP_VW

38 DLT_TASK_ESP Character(2) VARCHAR2(2) NOT NULL Task

Prompt Table: DLT_TSK_ESP_VW

39 DLT_USUAL_TOOL_ESP Character(11) VARCHAR2(11) NOT NULL Usual Working Tool

Prompt Table: DLT_TOOL_ESP_VW

40 DLT_WORKHOUR_ESP Character(2) VARCHAR2(2) NOT NULL Weeking Hour
0=In Itinere
1=Hour 1
10=Hour 10
11=Hour 11
12=Hour 12
2=Hour 2
3=Hour 3
4=Hour 4
5=Hour 5
6=Hour 6
7=Hour 7
8=Hour 8
9=Hour 9
99=In Itinere
41 DLT_INCWORK_ESP Character(1) VARCHAR2(1) NOT NULL Usual Work
42 DLT_RISK_EVAL_ESP Character(1) VARCHAR2(1) NOT NULL Risk Evaluation
43 DLT_DEVIATION_ESP Character(2) VARCHAR2(2) NOT NULL Deviation

Prompt Table: DLT_DEV_ESP_VW

44 DLT_UNUS_TOOL_ESP Character(11) VARCHAR2(11) NOT NULL Unusual Working Tool

Prompt Table: DLT_TOOL_ESP_VW

45 DLT_SOURCE_ESP Character(11) VARCHAR2(11) NOT NULL Source of Injury

Prompt Table: DLT_TOOL_ESP_VW

46 DLT_EMPL_FLG_ESP Character(1) VARCHAR2(1) NOT NULL Multiple Employees
47 DLT_WITNS_FLG_ESP Character(1) VARCHAR2(1) NOT NULL Witness
48 DLT_WITNS_DATA_ESP Character(50) VARCHAR2(50) NOT NULL Witness Data
49 DLT_INJURY_ESP Character(3) VARCHAR2(3) NOT NULL Injury

Prompt Table: DLT_INJ_ESP_VW

50 DLT_INJRY_GRD_ESP Character(1) VARCHAR2(1) NOT NULL Injury Grade
1=Low Damage
2=High Damage
3=Very High Damage
4=Death
51 DLT_BODYPART_ESP Character(8) VARCHAR2(8) NOT NULL Body Part

Prompt Table: DLT_DIA_ESP_VW

52 DLT_ASIST_TYPE_ESP Character(1) VARCHAR2(1) NOT NULL Assistance Type
1=Hospital
2=Ambulatory
53 CONTRIB_BASE_ESP Number(19,6) DECIMAL(18,6) NOT NULL Common Contingency Base
54 CONTRIB_DAYS_ESP Number(4,0) SMALLINT NOT NULL Contribution Days
55 REG_BASE_ESP Number(9,2) DECIMAL(8,2) NOT NULL Regulatory Base
56 CONTRIB_OVER_ESP Number(19,6) DECIMAL(18,6) NOT NULL Contribution Overtime
57 SUM_B1_B2_ESP Number(15,2) DECIMAL(14,2) NOT NULL Sum Base1 and Base 2
58 AVERAGE_ESP Number(9,2) DECIMAL(8,2) NOT NULL Average
59 AVERAGE_A_ESP Number(9,2) DECIMAL(8,2) NOT NULL Average A
60 AVERAGE_B_ESP Number(9,2) DECIMAL(8,2) NOT NULL Average B
61 AVERAGE_TOTAL_ESP Signed Number(10,2) DECIMAL(8,2) NOT NULL Average Total
62 AVERAGE_BNFIT_ESP Number(9,2) DECIMAL(8,2) NOT NULL Benefit
63 RESPONSIBLE_ROLE Character(30) VARCHAR2(30) NOT NULL Responsible Role
64 STATE2 Character(6) VARCHAR2(6) NOT NULL State
65 PRESENTED_DT Date(10) DATE Evidence Presented