SELECT DISTINCT i.incident_id, a.address_id, 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, --EJS_FORMAT_ADDRESS(a.address_id), '_INC_LOC_', 'Incident Location' FROM addresses a, incidents i, incident_supplements isupp, incident_addresses ia, address_common_names acn WHERE ia.incident_id = i.incident_id AND ia.address_id = a.address_id AND isupp.inc_incident_id = i.incident_id AND a.address_id = acn.addr_address_id(+) UNION SELECT DISTINCT i.incident_id, a.address_id, 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, --EJS_FORMAT_ADDRESS(a.address_id), IL.LOC_TYPE_CODE, LC.DESCRIPTION FROM addresses a, incidents i, incident_supplements isupp, incident_addresses ia, incident_locations il, address_common_names acn, ejs_codes lc WHERE ia.incident_id = i.incident_id AND isupp.inc_incident_id = il.incident_Id AND isupp.supp_seq = il.supp_seq AND il.address_id = a.address_id AND isupp.inc_incident_id = i.incident_id AND a.address_id = acn.addr_address_id(+) AND IL.LOC_TYPE_CODE_TYPE = lc.code_type AND IL.LOC_TYPE_CODE = lc.code UNION SELECT DISTINCT f.fldint_id, a.address_id, 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, --EJS_FORMAT_ADDRESS(a.address_id), 'OTHR', 'Field Contact Location' FROM FIELD_INTERVIEWS F, ADDRESSES A, FLDINT_ADDRESSES FA,ADDRESS_COMMON_NAMES acn WHERE FA.ADDRESS_ID = A.ADDRESS_ID AND A.ADDRESS_ID = ACN.ADDR_ADDRESS_ID(+) AND FA.FLDINT_ID = F.FLDINT_ID