View: HR_DE_WORK_INCIDENTS_REPORT

Product:	PER - Human Resources
Description:	View for German Work Incidents reporting based on PER_WORK_INCIDENTS and server side functions
Implementation/DBA Data:	APPS.HR_DE_WORK_INCIDENTS_REPORT

View Text

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'

Columns