OSHA_300_NE_VW

(SQL View)

SELECT I.ESTABID ,A.CASE_NBR_USA ,B.NAME ,B.BUSINESS_TITLE ,I.INCIDENT_DT ,I.EXACT_LOCATION ,A.OSHA_200_DESCR ,A.FATALITY_SW ,A.INJURY_ILLNESS ,A.AWAY_FROM_WORKDAYS ,A.RESTRICTD_WORKDAYS ,A.TRANSFER_WORKDAYS ,L.OSHA_INJ_CLASS ,A.ILLNESS_RESULT ,A.PRIVACY_CASE ,L.EFFDT ,L.EFF_STATUS FROM PS_INJURY_ILL A , PS_NE_PERSONAL_DTA B , PS_INCIDENT_DATA I , PS_INJURY_ILL_NOI K , PS_INJURY_NATR_TBL L WHERE A.REPORTABLE_CASE = 'Y' AND I.INCIDENT_NBR = A.INCIDENT_NBR AND B.NON_EMPLOYEE_ID = A.NON_EMPLOYEE_ID AND K.INCIDENT_NBR = I.INCIDENT_NBR AND K.NON_EMPLOYEE_ID = A.NON_EMPLOYEE_ID AND K.INJURY_NATURE_CD = L.INJURY_NATURE_CD AND K.PRIMARY_INJURY = 'Y' AND L.SETID = ( SELECT EE.SETID FROM PS_SET_CNTRL_TBL EE WHERE SETCNTRLVALUE = ( SELECT MAX(EST.REG_REGION) FROM PS_ESTAB_TBL EST WHERE EST.ESTABID = I.ESTABID))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID

2 CASE_NBR_USA Character(5) VARCHAR2(5) NOT NULL Case Number

3 NAME Character(50) VARCHAR2(50) NOT NULL Name

4 BUSINESS_TITLE Character(30) VARCHAR2(30) NOT NULL Business Title

5 INCIDENT_DT Date(10) DATE Incident Date

6 EXACT_LOCATION Character(240) VARCHAR2(240) NOT NULL Exact Location

7 OSHA_200_DESCR Character(60) VARCHAR2(60) NOT NULL Log Comments

8 FATALITY_SW Character(1) VARCHAR2(1) NOT NULL Fatal Injuries Sustained

9 INJURY_ILLNESS Character(1) VARCHAR2(1) NOT NULL Primarily Injury/Illness
D=Death
L=Illness
N=Injury

10 AWAY_FROM_WORKDAYS Number(4,0) SMALLINT NOT NULL Days Away From Work

11 RESTRICTD_WORKDAYS Number(4,0) SMALLINT NOT NULL Restricted Workdays

12 TRANSFER_WORKDAYS Number(4,0) SMALLINT NOT NULL Temp Transfer Work Days:

13 OSHA_INJ_CLASS Character(1) VARCHAR2(1) NOT NULL OSHA Injury Classification
1=Injury
2=Skin Disorder
3=Respiratory Condition
4=Poisoning
5=Hearing Loss
6=All Other Illnesses

14 ILLNESS_RESULT Character(1) VARCHAR2(1) NOT NULL Illness Resulted In
N=Not Applicable
T=Termination
X=Transfer

15 PRIVACY_CASE Character(1) VARCHAR2(1) NOT NULL Privacy Case
Y/N Table Edit
Default Value: N

16 EFFDT Date(10) DATE Effective Date
Default Value: %date

17 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
A=Active
I=Inactive

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	ESTABID	Character(12)	VARCHAR2(12) NOT NULL	Establishment ID
2	CASE_NBR_USA	Character(5)	VARCHAR2(5) NOT NULL	Case Number
3	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
4	BUSINESS_TITLE	Character(30)	VARCHAR2(30) NOT NULL	Business Title
5	INCIDENT_DT	Date(10)	DATE	Incident Date
6	EXACT_LOCATION	Character(240)	VARCHAR2(240) NOT NULL	Exact Location
7	OSHA_200_DESCR	Character(60)	VARCHAR2(60) NOT NULL	Log Comments
8	FATALITY_SW	Character(1)	VARCHAR2(1) NOT NULL	Fatal Injuries Sustained
9	INJURY_ILLNESS	Character(1)	VARCHAR2(1) NOT NULL	Primarily Injury/Illness D=Death L=Illness N=Injury
10	AWAY_FROM_WORKDAYS	Number(4,0)	SMALLINT NOT NULL	Days Away From Work
11	RESTRICTD_WORKDAYS	Number(4,0)	SMALLINT NOT NULL	Restricted Workdays
12	TRANSFER_WORKDAYS	Number(4,0)	SMALLINT NOT NULL	Temp Transfer Work Days:
13	OSHA_INJ_CLASS	Character(1)	VARCHAR2(1) NOT NULL	OSHA Injury Classification 1=Injury 2=Skin Disorder 3=Respiratory Condition 4=Poisoning 5=Hearing Loss 6=All Other Illnesses
14	ILLNESS_RESULT	Character(1)	VARCHAR2(1) NOT NULL	Illness Resulted In N=Not Applicable T=Termination X=Transfer
15	PRIVACY_CASE	Character(1)	VARCHAR2(1) NOT NULL	Privacy Case Y/N Table Edit Default Value: N
16	EFFDT	Date(10)	DATE	Effective Date Default Value: %date
17	EFF_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Effective Status A=Active I=Inactive