OSHA_300_RPT(SQL View) |
Index Back |
|---|---|
OSHA 300/300A Report View |
| SELECT I.ESTABID ,A.CASE_NBR_USA ,B.NAME ,D.DESCRSHORT ,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_PERSONAL_DATA B , PS_JOBCODE_TBL D , PS_INCIDENT_DATA I , PS_JOB J , PS_INJURY_ILL_NOI K , PS_INJURY_NATR_TBL L WHERE A.REPORTABLE_CASE = 'Y' AND I.INCIDENT_NBR = A.INCIDENT_NBR AND B.EMPLID = A.EMPLID AND J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND D.JOBCODE = J.JOBCODE AND D.SETID = J.SETID_JOBCODE AND K.INCIDENT_NBR = I.INCIDENT_NBR AND K.EMPLID = A.EMPLID AND K.EMPL_RCD = A.EMPL_RCD 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)) AND J.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOB WHERE EMPLID = J.EMPLID AND EMPL_RCD = J.EMPL_RCD AND EFFDT <= I.INCIDENT_DT) AND J.EFFSEQ = ( SELECT MAX(EFFSEQ) FROM PS_JOB WHERE EMPLID = J.EMPLID AND EMPL_RCD = J.EMPL_RCD AND EFFDT = J.EFFDT) AND D.EFFDT = ( SELECT MAX(EFFDT) FROM PS_JOBCODE_TBL WHERE JOBCODE = D.JOBCODE AND SETID = D.SETID AND EFFDT <= I.INCIDENT_DT) |
| # | 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 | JOB_TITLE | Character(10) | VARCHAR2(10) NOT NULL | Job 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 |