SELECT incident_id, inc_report_number, "AGNCY_CD_AGENCY_CODE", "Report Date_Hrs", "Report_Date", "Start Date", "Nature", "General Location", "Disposition", "Incident Type" FROM (SELECT DISTINCT i.incident_id, i.inc_report_number, i.report_time, o.OFFENSE_NUMBER, isupp.supp_agency_code "AGNCY_CD_AGENCY_CODE", TO_CHAR (i.REPORT_date, 'MM/DD/RR HH24MI') || ' Hrs' AS "Report Date_Hrs", i.report_date AS "Report_Date", CASE WHEN i.End_date IS NOT NULL THEN TO_CHAR (i.Start_date, 'MM/DD/RR HH24MI') || 'Hrs - ' || TO_CHAR (i.End_date, 'MM/DD/RR HH24MI') || 'Hrs' WHEN i.Start_date IS NOT NULL THEN TO_CHAR (i.Start_date, 'MM/DD/RR HH24MI') || 'Hrs' ELSE NULL END AS "Start Date", INITCAP (OC.OFFENSE_DESC) AS "Nature", CASE WHEN 'Y' = 'Y' OR ( ia.cpn_id IS NULL AND (SELECT COUNT (*) FROM address_common_names acn WHERE ACN.ADDR_ADDRESS_ID = a.address_id) = 0) THEN CASE CC_IS_NUMBER (a.street_number) WHEN 1 THEN NVL2 ( (SELECT acn.common_place_name FROM address_common_names acn WHERE ACN.ADDR_ADDRESS_ID = a.address_id AND acn.updator_date = (SELECT MAX ( updator_date) FROM address_common_names acn2 WHERE acn2.addr_address_id = a.address_id) AND ROWNUM = 1), (SELECT acn.common_place_name FROM address_common_names acn WHERE ACN.ADDR_ADDRESS_ID = a.address_id AND acn.updator_date = (SELECT MAX ( updator_date) FROM address_common_names acn2 WHERE acn2.addr_address_id = a.address_id) AND ROWNUM = 1) || ' <> ', '') || TRUNC ( TO_NUMBER ( RTRIM (a.street_number)) / 100) * 100 || ' BLK' END || ' ' || RTRIM (a.dirct_cd_direction_code) || ' ' || RTRIM (a.street_name) || ' ' || RTRIM (a.street_cd_street_type_code) || ' ' || RTRIM (a.direct_suffix) || ' ' || CASE WHEN a.INTRSECT1_NAME IS NOT NULL THEN 'Int:' || RTRIM (a.INTRSECT1_NAME) ELSE NULL END || ' - ' || RTRIM (INITCAP (a.CITY)) ELSE NVL ( CASE WHEN ia.cpn_id IS NOT NULL THEN (SELECT common_place_name FROM common_place_names cpn WHERE cpn.cpn_id = ia.cpn_id) ELSE (SELECT acn.common_place_name FROM address_common_names acn WHERE ACN.ADDR_ADDRESS_ID = a.address_id AND acn.updator_date = (SELECT MAX ( updator_date) FROM address_common_names acn2 WHERE acn2.addr_address_id = a.address_id)) END || '(CPN)', '*****') END AS "General Location", OSC.OFFENSE_STATUS_DESC AS "Disposition", (SELECT ITYPE.DESCRIPTION FROM EJS_CODES ITYPE WHERE ITYPE.CODE = IT.ITC_CODE AND ITYPE.CODE_TYPE = 'INCIDENT_TYPE_CODES') AS "Incident Type" FROM incidents i LEFT JOIN incident_supplements isupp ON i.INCIDENT_ID = isupp.INC_INCIDENT_ID -- AND isupp.SUPP_SEQ = 0 LEFT JOIN offenses o ON i.INCIDENT_ID = o.INC_INCIDENT_ID AND isupp.SUPP_SEQ = o.SUPP_SEQ LEFT JOIN OFFENSE_STATUS_CODES OSC ON OSC.OFFENSE_STATUS_CODE = o.OFFNSE_CD_OFFENSE_STATUS_CODE LEFT JOIN incident_types IT ON i.INCIDENT_ID = it.INCIDENT_ID LEFT JOIN offense_codes OC ON OC.OFFENSE_CODE = o.OFFNS_CD_OFFENSE_CODE LEFT JOIN INCIDENT_ADDRESSES ia ON i.INCIDENT_ID = ia.INCIDENT_ID LEFT JOIN ADDRESSES a ON A.ADDRESS_ID = IA.ADDRESS_ID WHERE o.OFFNS_CD_OFFENSE_CODE IS NOT NULL AND ISUPP.SUPP_SEQ = (SELECT MAX (SUPP_SEQ) FROM OFFENSES WHERE INC_INCIDENT_ID = I.INCIDENT_ID AND offense_number = (SELECT MIN (offense_number) FROM OFFENSES WHERE INC_INCIDENT_ID = I.INCIDENT_ID)) AND o.offense_number = (SELECT MIN (offense_number) FROM OFFENSES WHERE INC_INCIDENT_ID = I.INCIDENT_ID)) ORDER BY report_time, inc_report_number, offense_number