SELECT DISTINCT USER || IC.INC_CASE_ID AS ID, UPPER (USER) AS county, IC.INC_CASE_ID AS INC_CASE_ID, IC.AGENCY_CODE AS AGENCY_CODE, EJS_GET_AGENCY_CODE_DESCR(IC.AGENCY_CODE) AS AGENCY_DESC, IC.SECURITY_LEVEL AS SECURITY_LEVEL, IC.SC1_SOLVABILITY_CODE AS Solvability_Code, EJS_GET_EJS_CODE_DESCR(IC.SC1_SOLVABILITY_CODE_TYPE , IC.SC1_SOLVABILITY_CODE ) AS SOLVABILITY_DESC , EJS_GET_EJS_CODE_DESCR(IC.CASE_STATUS_CODE_TYPE , IC.CASE_STATUS_CODE ) AS CASE_STATUS, IC.RSC_STATUS_CODE AS Review_Status_Code, EJS_GET_EJS_CODE_DESCR(IC.RSC_STATUS_CODE_TYPE , IC.RSC_STATUS_CODE ) AS Review_Status, IC.RSC_STATUS_CODE AS Revw_Status_Code, IC.Case_Status_Code AS Case_Status_Code, EJS_GET_EJS_CODE_DESCR(IC.CASE_STATUS_CODE_TYPE , IC.CASE_STATUS_CODE ) AS Case_Status_Desc, EJS_GET_IC_INC_REPORT_NUMBERS(IC.INC_CASE_ID ) AS Incident_Report, I.INCIDENT_ID AS INCIDENT_ID, TO_CHAR(NEXT_UPDATE_DATE,'MM/DD/YYYY') AS Next_Review_Date, EJS_GET_IC_ASSIGN_DATES(IC.INC_CASE_ID) AS DATE_ASSIGNED, IT.ITC_CODE AS INCIDENT_TYPE_CODE, EJS_GET_EJS_CODE_DESCR(IT.ITC_CODE_TYPE ,IT.ITC_CODE ) AS INCIDENT_TYPE_DESC, TO_CHAR(I.REPORT_DATE,'MM/DD/YYYY') AS REPORT_DATE, EJS_GET_IC_INCIDENT_SUMMARY(IC.INC_CASE_ID) AS Incident_Summary, TO_CHAR(IC.CREATOR_DATE,'MM/DD/YYYY') AS EJS_CREATION_DATE, MN.LNAME AS LNAME, MN.FNAME AS FNAME, MN.MNAME AS MNAME, MN.SSN AS SSN, MP.RACE_CODE AS RACE_CODE, MP.SEX_CODE AS SEX_CODE, INITCAP(EJS_GET_EJS_CODE_DESCR(MP.RACE_CODE_TYPE , MP.RACE_CODE)) AS RACE_DESCRIPTION, INITCAP(EJS_GET_EJS_CODE_DESCR(MP.SEX_CODE_TYPE , MP.SEX_CODE)) AS SEX_DESCRIPTION, TO_CHAR(MN.DOB,'MM/DD/YYYY') AS DOB, (FLOOR(MONTHS_BETWEEN(SYSDATE, MN.DOB ) / 12 )) AS AGE, RC.ROLE_TYPE AS ROLE_TYPE, RC.ROLE_DESC AS ROLE_DESC, IC.CREATOR_ID AS IC_CREATOR_ID, (CASE WHEN O.LNAME IS NULL THEN '' ELSE UPPER(O.LNAME) END ||''|| CASE WHEN O.FNAME IS NULL THEN '' ELSE ', '|| UPPER(O.FNAME) END ||''|| CASE WHEN O.MNAME IS NULL THEN '' ELSE ' '|| UPPER(O.MNAME) END ) AS OFFICER_NAME, O.INTERNAL_ID AS BATCH_ID, ICO.INC_CASE_ID AS OFFICER_CASE_ID, O.OFFICER_ID AS OFFICER_ID, EJS_GET_EJS_CODE_DESCR(ICA.ACTIVITY_CODE_TYPE , ICA.ACTIVITY_CODE ) AS CASE_NOTE_DESC, O.USER_ID AS OFFICER_USER_ID, TO_CHAR(ICA.ACTIVITY_DATE,'MM/DD/YYYY') AS ACTIVITY_DATE, ICA.ACTIVITY_CODE AS ACTIVITY_CODE, IC.INC_CASE_NUMBER AS INC_CASE_NUMBER, (CASE RC.INC_SECTION_CODE WHEN 'OFFENDERS' THEN 'OFFENDER' WHEN 'VICTIMS' THEN 'VICTIM' ELSE 'Other Person Role' END ) AS INCIDENT_ROLE_DESC, EJS_GET_IC_PERSON_SUMMARY(IC.INC_CASE_ID) AS PERSON_SUMMARY, EJS_GET_IC_PER_SUM_NO_RACE_SEX(IC.INC_CASE_ID) AS PERSON_SUMMARY1, EJS_GET_IC_LEA_INVESTIGATOR( IC.INC_CASE_ID) AS LEA_INVESTGATOR, ICO.ROLE_CODE AS OFFICER_ROLE_CODE, '' AS OFFENSE, TO_CHAR(ICO.END_DATE,'MM/DD/YYYY') AS OFFICER_END_DATE, MN.SSN AS PERSON_SSN, MN.PER_PERSON_ID AS PERSON_ID, (SELECT IC.INC_CASE_NUMBER FROM INCIDENT_CASES IC WHERE IC.INC_CASE_ID = ICX.INC_CASE_ID) AS ASS_LEA_CASE_NUMBER, IC.UPDATOR_DATE AS UPDATOR_DATE FROM INCIDENT_CASES IC LEFT OUTER JOIN INC_CASE_INCIDENTS ICI ON IC.INC_CASE_ID = ICI.INC_CASE_ID LEFT OUTER JOIN INCIDENTS I ON ICI.INCIDENT_ID = I.INCIDENT_ID LEFT OUTER JOIN INCIDENT_CASE_OFFICERS ICO ON IC.INC_CASE_ID = ICO.INC_CASE_ID AND ICO.OFFICER_STATUS = 'Y' LEFT OUTER JOIN INCIDENT_TYPES IT ON I.INCIDENT_ID = IT.INCIDENT_ID LEFT OUTER JOIN INCIDENT_PEOPLE IP ON ICI.INCIDENT_ID = IP.INC_INCIDENT_ID LEFT OUTER JOIN INCIDENT_PERSON_ROLES IPR ON IP.INC_PER_ID = IPR.INC_PER_ID LEFT OUTER JOIN MASTER_NAMES MN ON MN.PER_PERSON_ID = IP.PER_PERSON_ID AND MN.NAME_TYPE_CODE = 'P' LEFT OUTER JOIN PEOPLE P ON MN.PER_PERSON_ID = P.PERSON_ID LEFT OUTER JOIN MASTER_PEOPLE MP ON P.MASTER_PERSON_ID= MP.MASTER_PERSON_ID LEFT OUTER JOIN ROLE_CODES RC ON IPR.ROLE_TYPE = RC.ROLE_TYPE LEFT OUTER JOIN OFFICERS O ON ICO.OFFICER_ID = O.OFFICER_ID LEFT OUTER JOIN INC_CASE_ACTIVITIES ICA ON IC.INC_CASE_ID = ICA.INC_CASE_ID LEFT OUTER JOIN INC_CASE_XREFS ICX ON IC.INC_CASE_ID = ICX.PARENT_INC_CASE_ID