SELECT IC.INC_CASE_ID, IC.INC_CASE_NUMBER, CS.CODE AS STATUS_CODE, CS.DESCRIPTION AS STATUS_DESC, RC.CODE AS REVIEW_STATUS_CODE, RC.DESCRIPTION AS REVIEW_STATUS_DESC, AC.AGENCY_CODE, AC.AGENCY_DESC, IC.NEXT_UPDATE_DATE, IC.CREATOR_DATE, IC.RESTRICT_SG_EXCLSV_FLAG, A.STREET_NUMBER, A.DIRCT_CD_DIRECTION_CODE, A.STREET_NAME, A.STREET_CD_STREET_TYPE_CODE, A.ADDR_SC_ADDRESS_SUBTYPE_CODE, A.SUB_NUMBER, INITCAP (A.CITY), A.STATE_CD_STATE_CODE, A.ZIP5, A.ZIP4, A.DIRECT_SUFFIX, ACN.COMMON_PLACE_NAME, '_INC_LOC_', A.STREET_NUMBER || ' ' || DC.DESCRIPTION || ' ' || A.STREET_NAME || ' ' || SC.DESCRIPTION || CASE WHEN A.INTRSECT1_NAME IS NOT NULL THEN ', ' || A.INTRSECT1_STREET_NUMBER || ' ' || DC2.DESCRIPTION || ' ' || A.INTRSECT1_NAME || ' ' || SC2.DESCRIPTION END || ' ' || DSC.DESCRIPTION || ' ' || STC.DESCRIPTION || CASE WHEN A.SUB_NUMBER IS NOT NULL THEN ' #' || A.SUB_NUMBER ELSE '' END || ' ' || A.CITY || ' ' || A.STATE_CD_STATE_CODE || ' ' || A.ZIP5 || CASE WHEN A.ZIP4 IS NOT NULL THEN '-' || TO_CHAR (A.ZIP4, 'FM0000') ELSE '' END || CASE WHEN A.CNTY_CD_COUNTY_CODE IS NOT NULL THEN ' ' || CC.DESCRIPTION || ' County ' ELSE '' END || CASE WHEN A.CNTRY_CD_COUNTRY_TYPE_CODE IS NOT NULL THEN ' ' || CNTC.DESCRIPTION ELSE '' END AS FORMATTED FROM INCIDENT_CASES IC LEFT OUTER JOIN EJS_CODES CS ON IC.CASE_STATUS_CODE = CS.CODE AND IC.CASE_STATUS_CODE_TYPE = CS.CODE_TYPE LEFT OUTER JOIN EJS_CODES RC ON IC.RSC_STATUS_CODE = RC.CODE AND IC.RSC_STATUS_CODE_TYPE = RC.CODE_TYPE INNER JOIN AGENCY_CODES AC ON IC.AGENCY_CODE = AC.AGENCY_CODE INNER JOIN INC_CASE_INCIDENTS ICI ON IC.INC_CASE_ID = ICI.INC_CASE_ID INNER JOIN INCIDENT_SUPPLEMENTS ISUP ON ICI.INCIDENT_ID = ISUP.INC_INCIDENT_ID AND ICI.SUPP_SEQ = ISUP.SUPP_SEQ INNER JOIN INCIDENTS I ON ISUP.INC_INCIDENT_ID = I.INCIDENT_ID INNER JOIN INCIDENT_ADDRESSES IA ON IA.INCIDENT_ID = I.INCIDENT_ID INNER JOIN ADDRESSES A ON A.ADDRESS_ID = IA.ADDRESS_ID LEFT OUTER JOIN ADDRESS_COMMON_NAMES ACN ON ACN.ADDR_ADDRESS_ID = A.ADDRESS_ID LEFT OUTER JOIN EJS_CODES DC ON a.DIRCT_CD_DIRECTION_CODE = DC.CODE AND a.DIRCT_CD_DIRECTION_CODE_TYPE = DC.CODE_TYPE LEFT OUTER JOIN EJS_CODES DSC ON a.DIRECT_SUFFIX = DSC.CODE AND a.DIRECT_SUFFIX_TYPE = DSC.CODE_TYPE LEFT OUTER JOIN EJS_CODES SC ON a.STREET_CD_STREET_TYPE_CODE = SC.CODE AND a.STREET_CD_STREET_TYPE_COD_TYPE = SC.CODE_TYPE LEFT OUTER JOIN EJS_CODES SC2 ON a.INTRSECT1_STREET_TYPE_CODE = SC2.CODE AND a.INTRSECT1_STREET_TYPE_COD_TYPE = SC2.CODE_TYPE LEFT OUTER JOIN EJS_CODES STC ON a.ADDR_SC_ADDRESS_SUBTYPE_CODE = STC.CODE AND a.ADDR_SC_ADDRESS_SUBTYPE_C_TYPE = STC.CODE_TYPE LEFT OUTER JOIN EJS_CODES CC ON a.CNTY_CD_COUNTY_CODE = CC.CODE AND a.CNTY_CD_COUNTY_CODE_TYPE = CC.CODE_TYPE LEFT OUTER JOIN EJS_CODES CNTC ON a.CNTRY_CD_COUNTRY_TYPE_CODE = CNTC.CODE AND a.CNTRY_CD_COUNTRY_TYPE_COD_TYPE = CNTC.CODE_TYPE LEFT OUTER JOIN EJS_CODES DC2 ON a.INTRSECT1_DIR = DC2.CODE AND a.INTRSECT1_DIR_TYPE = DC2.CODE_TYPE UNION SELECT IC.INC_CASE_ID, IC.INC_CASE_NUMBER, CS.CODE AS STATUS_CODE, CS.DESCRIPTION AS STATUS_DESC, RC.CODE AS REVIEW_STATUS_CODE, RC.DESCRIPTION AS REVIEW_STATUS_DESC, AC.AGENCY_CODE, AC.AGENCY_DESC, IC.NEXT_UPDATE_DATE, IC.CREATOR_DATE, IC.RESTRICT_SG_EXCLSV_FLAG, A.STREET_NUMBER, A.DIRCT_CD_DIRECTION_CODE, A.STREET_NAME, A.STREET_CD_STREET_TYPE_CODE, A.ADDR_SC_ADDRESS_SUBTYPE_CODE, A.SUB_NUMBER, INITCAP (A.CITY), A.STATE_CD_STATE_CODE, A.ZIP5, A.ZIP4, A.DIRECT_SUFFIX, ACN.COMMON_PLACE_NAME, IL.LOC_TYPE_CODE, A.STREET_NUMBER || ' ' || DC.DESCRIPTION || ' ' || A.STREET_NAME || ' ' || SC.DESCRIPTION || CASE WHEN A.INTRSECT1_NAME IS NOT NULL THEN ', ' || A.INTRSECT1_STREET_NUMBER || ' ' || DC2.DESCRIPTION || ' ' || A.INTRSECT1_NAME || ' ' || SC2.DESCRIPTION END || ' ' || DSC.DESCRIPTION || ' ' || STC.DESCRIPTION || CASE WHEN A.SUB_NUMBER IS NOT NULL THEN ' #' || A.SUB_NUMBER ELSE '' END || ' ' || A.CITY || ' ' || A.STATE_CD_STATE_CODE || ' ' || CASE WHEN A.ZIP5 IS NOT NULL AND A.ZIP5 > 0 THEN TO_CHAR (A.ZIP5, 'FM00000') ELSE '' END || CASE WHEN A.ZIP4 IS NOT NULL THEN '-' || TO_CHAR (A.ZIP4, 'FM0000') ELSE '' END || CASE WHEN A.CNTY_CD_COUNTY_CODE IS NOT NULL THEN ' ' || CC.DESCRIPTION || ' County ' ELSE '' END || CASE WHEN A.CNTRY_CD_COUNTRY_TYPE_CODE IS NOT NULL THEN ' ' || CNTC.DESCRIPTION ELSE '' END AS FORMATTED FROM INCIDENT_CASES IC INNER JOIN EJS_CODES CS ON IC.CASE_STATUS_CODE = CS.CODE AND IC.CASE_STATUS_CODE_TYPE = CS.CODE_TYPE INNER JOIN EJS_CODES RC ON IC.RSC_STATUS_CODE = RC.CODE AND IC.RSC_STATUS_CODE_TYPE = RC.CODE_TYPE INNER JOIN AGENCY_CODES AC ON IC.AGENCY_CODE = AC.AGENCY_CODE INNER JOIN INC_CASE_INCIDENTS ICI ON IC.INC_CASE_ID = ICI.INC_CASE_ID INNER JOIN INCIDENT_SUPPLEMENTS ISUP ON ICI.INCIDENT_ID = ISUP.INC_INCIDENT_ID AND ICI.SUPP_SEQ = ISUP.SUPP_SEQ INNER JOIN INCIDENTS I ON ISUP.INC_INCIDENT_ID = I.INCIDENT_ID INNER JOIN INCIDENT_ADDRESSES IA ON IA.INCIDENT_ID = I.INCIDENT_ID INNER JOIN ADDRESSES A ON A.ADDRESS_ID = IA.ADDRESS_ID INNER JOIN INCIDENT_LOCATIONS IL ON IL.ADDRESS_ID = A.ADDRESS_ID LEFT OUTER JOIN ADDRESS_COMMON_NAMES ACN ON ACN.ADDR_ADDRESS_ID = A.ADDRESS_ID LEFT OUTER JOIN EJS_CODES DC ON a.DIRCT_CD_DIRECTION_CODE = DC.CODE AND a.DIRCT_CD_DIRECTION_CODE_TYPE = DC.CODE_TYPE LEFT OUTER JOIN EJS_CODES DSC ON a.DIRECT_SUFFIX = DSC.CODE AND a.DIRECT_SUFFIX_TYPE = DSC.CODE_TYPE LEFT OUTER JOIN EJS_CODES SC ON a.STREET_CD_STREET_TYPE_CODE = SC.CODE AND a.STREET_CD_STREET_TYPE_COD_TYPE = SC.CODE_TYPE LEFT OUTER JOIN EJS_CODES SC2 ON a.INTRSECT1_STREET_TYPE_CODE = SC2.CODE AND a.INTRSECT1_STREET_TYPE_COD_TYPE = SC2.CODE_TYPE LEFT OUTER JOIN EJS_CODES STC ON a.ADDR_SC_ADDRESS_SUBTYPE_CODE = STC.CODE AND a.ADDR_SC_ADDRESS_SUBTYPE_C_TYPE = STC.CODE_TYPE LEFT OUTER JOIN EJS_CODES CC ON a.CNTY_CD_COUNTY_CODE = CC.CODE AND a.CNTY_CD_COUNTY_CODE_TYPE = CC.CODE_TYPE LEFT OUTER JOIN EJS_CODES CNTC ON a.CNTRY_CD_COUNTRY_TYPE_CODE = CNTC.CODE AND a.CNTRY_CD_COUNTRY_TYPE_COD_TYPE = CNTC.CODE_TYPE LEFT OUTER JOIN EJS_CODES DC2 ON a.INTRSECT1_DIR = DC2.CODE AND a.INTRSECT1_DIR_TYPE = DC2.CODE_TYPE UNION SELECT IC.INC_CASE_ID, IC.INC_CASE_NUMBER, CS.CODE AS STATUS_CODE, CS.DESCRIPTION AS STATUS_DESC, RC.CODE AS REVIEW_STATUS_CODE, RC.DESCRIPTION AS REVIEW_STATUS_DESC, AC.AGENCY_CODE, AC.AGENCY_DESC, IC.NEXT_UPDATE_DATE, IC.CREATOR_DATE, IC.RESTRICT_SG_EXCLSV_FLAG, A.STREET_NUMBER, A.DIRCT_CD_DIRECTION_CODE, A.STREET_NAME, A.STREET_CD_STREET_TYPE_CODE, A.ADDR_SC_ADDRESS_SUBTYPE_CODE, A.SUB_NUMBER, INITCAP (A.CITY), A.STATE_CD_STATE_CODE, A.ZIP5, A.ZIP4, A.DIRECT_SUFFIX, ACN.COMMON_PLACE_NAME, 'OTHR', A.STREET_NUMBER || ' ' || DC.DESCRIPTION || ' ' || A.STREET_NAME || ' ' || SC.DESCRIPTION || CASE WHEN A.INTRSECT1_NAME IS NOT NULL THEN ', ' || A.INTRSECT1_STREET_NUMBER || ' ' || DC2.DESCRIPTION || ' ' || A.INTRSECT1_NAME || ' ' || SC2.DESCRIPTION END || ' ' || DSC.DESCRIPTION || ' ' || STC.DESCRIPTION || CASE WHEN A.SUB_NUMBER IS NOT NULL THEN ' #' || A.SUB_NUMBER ELSE '' END || ' ' || A.CITY || ' ' || A.STATE_CD_STATE_CODE || ' ' || CASE WHEN A.ZIP5 IS NOT NULL AND A.ZIP5 > 0 THEN TO_CHAR (A.ZIP5, 'FM00000') ELSE '' END || CASE WHEN A.ZIP4 IS NOT NULL THEN '-' || TO_CHAR (A.ZIP4, 'FM0000') ELSE '' END || CASE WHEN A.CNTY_CD_COUNTY_CODE IS NOT NULL THEN ' ' || CC.DESCRIPTION || ' County ' ELSE '' END || CASE WHEN A.CNTRY_CD_COUNTRY_TYPE_CODE IS NOT NULL THEN ' ' || CNTC.DESCRIPTION ELSE '' END AS FORMATTED FROM INCIDENT_CASES IC INNER JOIN EJS_CODES CS ON IC.CASE_STATUS_CODE = CS.CODE AND IC.CASE_STATUS_CODE_TYPE = CS.CODE_TYPE INNER JOIN EJS_CODES RC ON IC.RSC_STATUS_CODE = RC.CODE AND IC.RSC_STATUS_CODE_TYPE = RC.CODE_TYPE INNER JOIN AGENCY_CODES AC ON IC.AGENCY_CODE = AC.AGENCY_CODE INNER JOIN CASE_FIELD_INTS CFI ON IC.INC_CASE_ID = CFI.CASE_ID INNER JOIN FIELD_INTERVIEWS FI ON CFI.FLDINT_ID = FI.FLDINT_ID INNER JOIN FLDINT_ADDRESSES FA ON FA.FLDINT_ID = FI.FLDINT_ID INNER JOIN ADDRESSES A ON A.ADDRESS_ID = FA.ADDRESS_ID LEFT OUTER JOIN ADDRESS_COMMON_NAMES ACN ON ACN.ADDR_ADDRESS_ID = A.ADDRESS_ID LEFT OUTER JOIN EJS_CODES DC ON a.DIRCT_CD_DIRECTION_CODE = DC.CODE AND a.DIRCT_CD_DIRECTION_CODE_TYPE = DC.CODE_TYPE LEFT OUTER JOIN EJS_CODES DSC ON a.DIRECT_SUFFIX = DSC.CODE AND a.DIRECT_SUFFIX_TYPE = DSC.CODE_TYPE LEFT OUTER JOIN EJS_CODES SC ON a.STREET_CD_STREET_TYPE_CODE = SC.CODE AND a.STREET_CD_STREET_TYPE_COD_TYPE = SC.CODE_TYPE LEFT OUTER JOIN EJS_CODES SC2 ON a.INTRSECT1_STREET_TYPE_CODE = SC2.CODE AND a.INTRSECT1_STREET_TYPE_COD_TYPE = SC2.CODE_TYPE LEFT OUTER JOIN EJS_CODES STC ON a.ADDR_SC_ADDRESS_SUBTYPE_CODE = STC.CODE AND a.ADDR_SC_ADDRESS_SUBTYPE_C_TYPE = STC.CODE_TYPE LEFT OUTER JOIN EJS_CODES CC ON a.CNTY_CD_COUNTY_CODE = CC.CODE AND a.CNTY_CD_COUNTY_CODE_TYPE = CC.CODE_TYPE LEFT OUTER JOIN EJS_CODES CNTC ON a.CNTRY_CD_COUNTRY_TYPE_CODE = CNTC.CODE AND a.CNTRY_CD_COUNTRY_TYPE_COD_TYPE = CNTC.CODE_TYPE LEFT OUTER JOIN EJS_CODES DC2 ON a.INTRSECT1_DIR = DC2.CODE AND a.INTRSECT1_DIR_TYPE = DC2.CODE_TYPE