VIEW: APPS.HR_DE_WORK_INCIDENTS_REPORT

Source

View Text - Preformatted

SELECT distinct(ASS.assignment_id), WI.incident_id, PER.party_id, PER.person_id, PER.last_name, PER.first_name, PER.date_of_birth, PER.national_identifier, PER.sex, PER.marital_status, PER.nationality, ASS.primary_flag, ASS.organization_id, ORG.name, HR_DE_ORG_INFO.get_supervising_off(ASS.assignment_id, WI.incident_date), HR_DE_ORG_INFO.get_supervising_off_name(ASS.assignment_id), HR_DE_ORG_INFO.get_liab_prov_details(ASS.assignment_id, WI.incident_date), HR_DE_ORG_INFO.get_liab_prov_name(ASS.assignment_id), HR_DE_ORG_INFO.get_liab_prov_membership_no(ASS.assignment_id), Substr(HR_DE_ORG_INFO.get_location(ASS.assignment_id),1,15), HR_DE_ORG_INFO.get_addr_line1(ASS.assignment_id), HR_DE_ORG_INFO.get_addr_line2(ASS.assignment_id), HR_DE_ORG_INFO.get_addr_line3(ASS.assignment_id), HR_DE_ORG_INFO.get_town(ASS.assignment_id), HR_DE_ORG_INFO.get_country(ASS.assignment_id), HR_DE_ORG_INFO.get_postal_code(ASS.assignment_id), substr(HR_DE_ORG_INFO.get_liab_prov_details2(ASS.assignment_id, WI.incident_date), 31, 30), WI.Body_part, WI.Injury_type, WI.Doctor_name, WI.Incident_type, HR_GENERAL.decode_lookup('INCIDENT_TYPE', WI.Incident_type), WI.Incident_reference, WI.Hospital_details, WI.Incident_date, WI.Incident_time, WI.Location, WI.Remedial_HS_action, WI.Witness_details, WI.Description, WI.Inc_information1, WI.Inc_information2, WI.Inc_information3, WI.Inc_information4, WI.Inc_information5, HR_GENERAL.decode_lookup('YES_NO', WI.Inc_information5), WI.Inc_information6, HR_GENERAL.decode_lookup('YES_NO', WI.Inc_information6), WI.Inc_information7, WI.Inc_information8, HR_GENERAL.decode_lookup('DE_SPCL_REL', WI.Inc_information8), WI.Inc_information9, WI.Inc_information10, WI.Inc_information11, WI.Inc_information12, WI.Inc_information13, WI.Inc_information14, WI.Inc_information15, WI.Inc_information16, WI.Inc_information17, WI.Inc_information18, WI.Inc_information19, WI.Inc_information20, WI.Inc_information21, WI.Inc_information22, WI.Inc_information23, WI.Inc_information24, WI.Inc_information25, WI.Inc_information26, WI.Inc_information27, WI.Inc_information28, WI.Inc_information29, WI.Inc_information30 FROM per_work_incidents WI, per_all_people_f PER, per_all_assignments_f ASS, hr_all_organization_units ORG, hr_locations_all LOC WHERE WI.person_id = PER.person_id AND ASS.person_id = PER.person_id AND ASS.organization_id = ORG.organization_id AND WI.incident_date BETWEEN ASS.effective_start_date AND ASS.effective_end_date AND WI.incident_date BETWEEN PER.effective_start_date AND PER.effective_end_date AND ASS.primary_flag = 'Y'

View Text - HTML Formatted

SELECT DISTINCT(ASS.ASSIGNMENT_ID)
, WI.INCIDENT_ID
, PER.PARTY_ID
, PER.PERSON_ID
, PER.LAST_NAME
, PER.FIRST_NAME
, PER.DATE_OF_BIRTH
, PER.NATIONAL_IDENTIFIER
, PER.SEX
, PER.MARITAL_STATUS
, PER.NATIONALITY
, ASS.PRIMARY_FLAG
, ASS.ORGANIZATION_ID
, ORG.NAME
, HR_DE_ORG_INFO.GET_SUPERVISING_OFF(ASS.ASSIGNMENT_ID
, WI.INCIDENT_DATE)
, HR_DE_ORG_INFO.GET_SUPERVISING_OFF_NAME(ASS.ASSIGNMENT_ID)
, HR_DE_ORG_INFO.GET_LIAB_PROV_DETAILS(ASS.ASSIGNMENT_ID
, WI.INCIDENT_DATE)
, HR_DE_ORG_INFO.GET_LIAB_PROV_NAME(ASS.ASSIGNMENT_ID)
, HR_DE_ORG_INFO.GET_LIAB_PROV_MEMBERSHIP_NO(ASS.ASSIGNMENT_ID)
, SUBSTR(HR_DE_ORG_INFO.GET_LOCATION(ASS.ASSIGNMENT_ID)
, 1
, 15)
, HR_DE_ORG_INFO.GET_ADDR_LINE1(ASS.ASSIGNMENT_ID)
, HR_DE_ORG_INFO.GET_ADDR_LINE2(ASS.ASSIGNMENT_ID)
, HR_DE_ORG_INFO.GET_ADDR_LINE3(ASS.ASSIGNMENT_ID)
, HR_DE_ORG_INFO.GET_TOWN(ASS.ASSIGNMENT_ID)
, HR_DE_ORG_INFO.GET_COUNTRY(ASS.ASSIGNMENT_ID)
, HR_DE_ORG_INFO.GET_POSTAL_CODE(ASS.ASSIGNMENT_ID)
, SUBSTR(HR_DE_ORG_INFO.GET_LIAB_PROV_DETAILS2(ASS.ASSIGNMENT_ID
, WI.INCIDENT_DATE)
, 31
, 30)
, WI.BODY_PART
, WI.INJURY_TYPE
, WI.DOCTOR_NAME
, WI.INCIDENT_TYPE
, HR_GENERAL.DECODE_LOOKUP('INCIDENT_TYPE'
, WI.INCIDENT_TYPE)
, WI.INCIDENT_REFERENCE
, WI.HOSPITAL_DETAILS
, WI.INCIDENT_DATE
, WI.INCIDENT_TIME
, WI.LOCATION
, WI.REMEDIAL_HS_ACTION
, WI.WITNESS_DETAILS
, WI.DESCRIPTION
, WI.INC_INFORMATION1
, WI.INC_INFORMATION2
, WI.INC_INFORMATION3
, WI.INC_INFORMATION4
, WI.INC_INFORMATION5
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, WI.INC_INFORMATION5)
, WI.INC_INFORMATION6
, HR_GENERAL.DECODE_LOOKUP('YES_NO'
, WI.INC_INFORMATION6)
, WI.INC_INFORMATION7
, WI.INC_INFORMATION8
, HR_GENERAL.DECODE_LOOKUP('DE_SPCL_REL'
, WI.INC_INFORMATION8)
, WI.INC_INFORMATION9
, WI.INC_INFORMATION10
, WI.INC_INFORMATION11
, WI.INC_INFORMATION12
, WI.INC_INFORMATION13
, WI.INC_INFORMATION14
, WI.INC_INFORMATION15
, WI.INC_INFORMATION16
, WI.INC_INFORMATION17
, WI.INC_INFORMATION18
, WI.INC_INFORMATION19
, WI.INC_INFORMATION20
, WI.INC_INFORMATION21
, WI.INC_INFORMATION22
, WI.INC_INFORMATION23
, WI.INC_INFORMATION24
, WI.INC_INFORMATION25
, WI.INC_INFORMATION26
, WI.INC_INFORMATION27
, WI.INC_INFORMATION28
, WI.INC_INFORMATION29
, WI.INC_INFORMATION30
FROM PER_WORK_INCIDENTS WI
, PER_ALL_PEOPLE_F PER
, PER_ALL_ASSIGNMENTS_F ASS
, HR_ALL_ORGANIZATION_UNITS ORG
, HR_LOCATIONS_ALL LOC
WHERE WI.PERSON_ID = PER.PERSON_ID
AND ASS.PERSON_ID = PER.PERSON_ID
AND ASS.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND WI.INCIDENT_DATE BETWEEN ASS.EFFECTIVE_START_DATE
AND ASS.EFFECTIVE_END_DATE
AND WI.INCIDENT_DATE BETWEEN PER.EFFECTIVE_START_DATE
AND PER.EFFECTIVE_END_DATE
AND ASS.PRIMARY_FLAG = 'Y'