SELECT DISTINCT PRO.PROPERTY_ID, I.INC_REPORT_NUMBER AS IR_NUMBER, ISUPP.APPROVAL_DATE, CASE WHEN AC.ORI_NUMBER IS NULL THEN AC.AGENCY_CODE ELSE AC.ORI_NUMBER END AS ORI, CASE WHEN ISUPP.SUPP_REPORT_DATE IS NOT NULL THEN TO_CHAR (ISUPP.SUPP_REPORT_DATE, 'RRRR-MM-DD') || 'T' || TO_CHAR (ISUPP.SUPP_REPORT_DATE, 'HH24:MI:SS') || 'Z' ELSE '' END AS EDIT_DATE, ISUPP.UPDATOR_DATE, OC.OFFENSE_DESC AS CRIME, O.OFFNS_CD_OFFENSE_CODE AS OFFENSE_CODE, NBC.LAYER_CODE AS LAYER_CODE, NBC.NIBRS_CODE AS NIBRS_CODE, NCT.CRIME_AGAINST_CODE AS OBJECT_OF_ATTACK, '' AS SUSPECTS_ACT_AGN_PEO, MTC.DESCRIPTION AS SUSPECTS_ACT_AGN_PRO, LT.DESCRIPTION AS LOCATION_TYPE, PEC.DESCRIPTION AS POINT_OF_ENTRY, MEC.DESCRIPTION AS METHOD_OF_ENTRY, CASE WHEN AC.ORI_NUMBER IS NULL THEN AC.AGENCY_CODE ELSE AC.ORI_NUMBER END || ' ' || AC.AGENCY_DESC AS AGENCY, AC.AGENCY_CODE, PTC.PROPERTY_TYPE_DESC AS PROPERTY_TAKEN, PRO.VALUE AS PROPERTY_VALUE, PEXC.DESCRIPTION AS METHOD_OF_DEPARTURE, WT.DESCRIPTION AS WEAPON_TYPE, CASE WHEN I.START_DATE IS NOT NULL AND I.START_TIME IS NOT NULL THEN TO_CHAR (I.START_DATE, 'RRRR-MM-DD') || 'T' || TO_CHAR (I.START_TIME, 'HH24:MI:SS') || 'Z' ELSE '' END AS FIRST_DATE, CASE WHEN I.END_DATE IS NOT NULL AND I.END_TIME IS NOT NULL THEN TO_CHAR (I.END_DATE, 'RRRR-MM-DD') || 'T' || TO_CHAR (I.END_TIME, 'HH24:MI:SS') || 'Z' ELSE '' END AS LAST_DATE, O.UCR_NUMBER, CASE WHEN AD.STREET_NUMBER IS NOT NULL THEN AD.STREET_NUMBER || ' ' ELSE '' END || CASE WHEN AD.DIRCT_CD_DIRECTION_CODE IS NOT NULL THEN AD.DIRCT_CD_DIRECTION_CODE || ' ' ELSE '' END || CASE WHEN AD.STREET_NAME IS NOT NULL THEN AD.STREET_NAME || ' ' ELSE '' END || CASE WHEN AD.STREET_CD_STREET_TYPE_CODE IS NOT NULL THEN AD.STREET_CD_STREET_TYPE_CODE || ' ' ELSE '' END || CASE WHEN AD.DIRECT_SUFFIX IS NOT NULL THEN AD.DIRECT_SUFFIX || ' ' ELSE '' END || CASE WHEN AD.CITY IS NOT NULL THEN AD.CITY || ' ' ELSE '' END || CASE WHEN AD.STATE_CD_STATE_CODE IS NOT NULL THEN AD.STATE_CD_STATE_CODE || ' ' ELSE '' END || CASE WHEN AD.ZIP5 IS NOT NULL THEN AD.ZIP5 || ' ' ELSE '' END AS ADDRESS_OF_CRIME, CASE WHEN BUS.BUSINESS_NAME IS NOT NULL THEN BUS.BUSINESS_NAME ELSE (SELECT ADCNA.AD_NAME FROM ( SELECT LISTAGG ( ADC.COMMON_PLACE_NAME, ' ') WITHIN GROUP (ORDER BY ADCN.UPDATOR_DATE, ADCN.ADDR_ADDRESS_ID) AS AD_NAME, ADCN.ADDR_ADDRESS_ID FROM ADDRESS_COMMON_NAMES ADC JOIN ( SELECT ADDR_ADDRESS_ID, MAX (UPDATOR_DATE) AS UPDATOR_DATE FROM ADDRESS_COMMON_NAMES GROUP BY ADDR_ADDRESS_ID) ADCN ON ADC.ADDR_ADDRESS_ID = ADCN.ADDR_ADDRESS_ID AND ADC.UPDATOR_DATE = ADCN.UPDATOR_DATE GROUP BY ADCN.UPDATOR_DATE, ADCN.ADDR_ADDRESS_ID) ADCNA WHERE ADCNA.ADDR_ADDRESS_ID = AD.ADDRESS_ID) END AS ADDRESS_NAME, AD.BEAT AS BEAT, AD.SUB_BEAT AS RD, REPLACE (REPLACE (I.SUMMARY, CHR (10), ' '), CHR (13), ' ') AS SYNOPSIS_OF_CRIME, REPLACE (REPLACE (I.SUMMARY, CHR (10), ' '), CHR (13), ' ') AS PUBLIC_SYNOPSIS, (SELECT CASE WHEN COUNT (DISTINCT (IP.INC_PER_ID)) - 1 = -1 THEN 0 ELSE COUNT (DISTINCT (IP.INC_PER_ID)) - 1 END NO_OF_COMP FROM INCIDENT_PEOPLE IP, INCIDENT_PERSON_ROLES IPR WHERE IPR.ROLE_TYPE IN (SELECT ROLE_TYPE FROM ROLE_CODES WHERE INC_SECTION_CODE = 'OFFENDERS') AND IP.INC_PER_ID = IPR.INC_PER_ID AND IP.INC_INCIDENT_ID = O.INC_INCIDENT_ID) AS NO_OF_COMPANIONS, ADSUB.DESCRIPTION || CASE WHEN AD.SUB_NUMBER IS NOT NULL AND ADSUB.DESCRIPTION IS NOT NULL THEN ',' || AD.SUB_NUMBER ELSE AD.SUB_NUMBER END AS APT_NUMBER, AD.LONGITUDE AS X_COORDINATE, AD.LATITUDE AS Y_COORDINATE, 'ADD' AS ACTION FROM INCIDENTS I JOIN (SELECT ISS.* FROM INCIDENT_SUPPLEMENTS ISS, ( SELECT INC_INCIDENT_ID, MAX (SUPP_REPORT_DATE) AS SUPP_REPORT_DATE FROM INCIDENT_SUPPLEMENTS WHERE ISC_STATUS_CODE = 'A' GROUP BY INC_INCIDENT_ID) ISSUP WHERE ISS.INC_INCIDENT_ID = ISSUP.INC_INCIDENT_ID AND ISS.SUPP_REPORT_DATE = ISSUP.SUPP_REPORT_DATE) ISUPP ON I.INCIDENT_ID = ISUPP.INC_INCIDENT_ID LEFT JOIN (SELECT * FROM OFFENSES O LEFT JOIN OFFENSE_CODES OC ON O.OFFNS_CD_OFFENSE_CODE = OC.OFFENSE_CODE WHERE (O.INC_INCIDENT_ID, OC.OFFENSE_DESC, O.SUPP_SEQ) IN ( SELECT O.INC_INCIDENT_ID AS INCIDENT_ID, OC.OFFENSE_DESC, MAX ( O.SUPP_SEQ) FROM OFFENSES O JOIN INCIDENT_SUPPLEMENTS ISU ON O.INC_INCIDENT_ID = ISU.INC_INCIDENT_ID AND O.SUPP_SEQ = ISU.SUPP_SEQ LEFT JOIN OFFENSE_CODES OC ON O.OFFNS_CD_OFFENSE_CODE = OC.OFFENSE_CODE WHERE ISU.ISC_STATUS_CODE = 'A' GROUP BY O.INC_INCIDENT_ID, OC.OFFENSE_DESC)) O ON I.INCIDENT_ID = O.INC_INCIDENT_ID LEFT JOIN NIBRS_OFFENSES NBO ON O.OFFNS_CD_OFFENSE_CODE = NBO.OFFENSE_CODE LEFT JOIN NIBRS_CODES NBC ON NBO.NIBRS_CODE = NBC.NIBRS_CODE LEFT JOIN NIBRS_CRIME_TARGETS NCT ON NBO.NIBRS_CODE = NCT.NIBRS_CODE LEFT JOIN AGENCY_CODES AC ON I.AGNCY_CD_AGENCY_CODE = AC.AGENCY_CODE LEFT JOIN INCIDENT_ADDRESSES IA ON I.INCIDENT_ID = IA.INCIDENT_ID LEFT JOIN ADDRESSES AD ON IA.ADDRESS_ID = AD.ADDRESS_ID LEFT JOIN ( SELECT ADDRESS_ID, MAX (BUSINESS_NUMBER) AS BUSINESS_NUMBER FROM BUSINESS_ADDRESSES GROUP BY ADDRESS_ID) BA ON AD.ADDRESS_ID = BA.ADDRESS_ID LEFT JOIN BUSINESSES BUS ON BA.BUSINESS_NUMBER = BUS.BUSINESS_NUMBER LEFT JOIN MODUS_OPERANDI MO ON O.INC_INCIDENT_ID = MO.INCIDENT_ID AND O.SUPP_SEQ = MO.SUPP_SEQ AND O.OFFENSE_NUMBER = MO.OFFENSE_NUMBER LEFT JOIN OFFENSE_CODES OC ON O.OFFNS_CD_OFFENSE_CODE = OC.OFFENSE_CODE LEFT JOIN MO_METHOD_OF_ENTRY_CODES MEC ON MO.METHOD_OF_ENTRY_CODE = MEC.METHOD_OF_ENTRY_CODE LEFT JOIN MO_POINT_OF_ENTRY_CODES PEC ON MO.POINT_OF_ENTRY_CODE = PEC.POINT_OF_ENTRY_CODE LEFT JOIN MO_POINT_OF_EXIT_CODES PEXC ON MO.POINT_OF_EXIT_CODE = PEXC.POINT_OF_EXIT_CODE LEFT JOIN MO_TRADEMARK_CODES MTC ON MO.TRADEMARK_CODE = MTC.TRADEMARK_CODE LEFT JOIN WEAPONS W ON O.INC_INCIDENT_ID = W.OFFNSE_INC_INCIDENT_ID AND O.SUPP_SEQ = W.OFFNSE_SUPP_SEQ AND O.OFFENSE_NUMBER = W.OFFNSE_OFFENSE_NUMBER LEFT JOIN EJS_CODES WT ON W.WC_WEAPON_CODE = WT.CODE AND W.WC_WEAPON_CODE_TYPE = WT.CODE_TYPE LEFT JOIN EJS_CODES LT ON O.PLACE_PLACE_CODE = LT.CODE AND O.PLACE_CODE_TYPE = LT.CODE_TYPE LEFT JOIN (SELECT IPR.INC_PER_ID, IPR.OFFENSE_NUMBER, IPR.OFFENSE_INCIDENT_ID, IPR.PROPERTY_ID, IPR.INCSUP_SUPP_SEQ FROM INCIDENT_PROPERTIES IPR JOIN INCIDENT_SUPPLEMENTS ISU ON IPR.INCSUP_INCIDENT_ID = ISU.INC_INCIDENT_ID AND IPR.INCSUP_SUPP_SEQ = ISU.SUPP_SEQ WHERE ISU.ISC_STATUS_CODE = 'A') IPR ON O.INC_INCIDENT_ID = IPR.OFFENSE_INCIDENT_ID AND O.OFFENSE_NUMBER = IPR.OFFENSE_NUMBER LEFT JOIN PROPERTIES PRO ON IPR.PROPERTY_ID = PRO.PROPERTY_ID LEFT JOIN EJS_CODES ADSUB ON AD.ADDR_SC_ADDRESS_SUBTYPE_CODE = ADSUB.CODE AND AD.ADDR_SC_ADDRESS_SUBTYPE_C_TYPE = ADSUB.CODE_TYPE LEFT JOIN PROPERTY_TYPE_CODES PTC ON PRO.PROPERTY_TYPE_CODE = PTC.PROPERTY_TYPE