SELECT 'INCIDENT', I.INCIDENT_ID, NULL, I.INC_REPORT_NUMBER, I.START_DATE, IA.ADDRESS_ID, EJS_FORMAT_ADDRESS(A.ADDRESS_ID), A.LATITUDE, A.LONGITUDE, 'N', 'N' FROM INCIDENTS I INNER JOIN INCIDENT_ADDRESSES IA ON I.INCIDENT_ID = IA.INCIDENT_ID INNER JOIN ADDRESSES A ON IA.ADDRESS_ID = A.ADDRESS_ID WHERE A.GEOCODED_FLAG = 'Y' UNION SELECT 'ARREST', A.ARREST_ID, NULL, A.ARREST_NUM, A.ARREST_DATE, A.ADDR_ADDRESS_ID, EJS_FORMAT_ADDRESS(ADDR.ADDRESS_ID), ADDR.LATITUDE, ADDR.LONGITUDE, 'N', 'N' FROM ARRESTS A INNER JOIN ADDRESSES ADDR ON A.ADDR_ADDRESS_ID = ADDR.ADDRESS_ID WHERE ADDR.GEOCODED_FLAG = 'Y' UNION SELECT 'WARRANT', EW.WARRANT_ID, A.ADDRESS_ID, '', EW.DATE_ISSUED, A.ADDRESS_ID, EJS_FORMAT_ADDRESS(WSA.ADDRESS_ID), A.LATITUDE, A.LONGITUDE, 'N', 'Y' FROM E_WARRANTS EW INNER JOIN WARRANT_SERVICE_ADDRESSES WSA ON EW.WARRANT_ID = WSA.WARRANT_ID INNER JOIN ADDRESSES A ON WSA.ADDRESS_ID = A.ADDRESS_ID WHERE EW.WSC_CODE_TYPE = 'WARRANT_STATUS_CODES' and EW.WSC_CODE = 'A' AND A.GEOCODED_FLAG = 'Y' UNION SELECT 'RECOVERED_VEHICLE', IV.INC_VEH_ID, NULL, I.INC_REPORT_NUMBER, IV.DATE_RECOVERED, A.ADDRESS_ID, EJS_FORMAT_ADDRESS(A.ADDRESS_ID), A.LATITUDE, A.LONGITUDE, 'N', 'N' FROM INCIDENT_VEHICLES IV INNER JOIN ADDRESSES A ON IV.ADDRESS_ID = A.ADDRESS_ID INNER JOIN INCIDENTS I ON IV.INCIDENT_ID = I.INCIDENT_ID AND A.GEOCODED_FLAG = 'Y' UNION SELECT 'CITATION', CI.CITATION_ID, NULL, CI.TICKET_NUMBER, CI.CITATION_DATE, CA.ADDRESS_ID, EJS_FORMAT_ADDRESS(A.ADDRESS_ID), A.LATITUDE, A.LONGITUDE, 'N', 'N' FROM CITATIONS CI INNER JOIN CITATION_ADDRESSES CA ON CI.CITATION_ID = CA.CITATION_ID INNER JOIN ADDRESSES A ON CA.ADDRESS_ID = A.ADDRESS_ID WHERE A.GEOCODED_FLAG = 'Y' UNION SELECT 'FIELD_CONTACT', FI.FLDINT_ID, NULL, '', FI.FLDINT_DATE, A.ADDRESS_ID, EJS_FORMAT_ADDRESS(A.ADDRESS_ID), A.LATITUDE, A.LONGITUDE, 'N', 'N' FROM FIELD_INTERVIEWS FI INNER JOIN FLDINT_ADDRESSES FA ON FI.FLDINT_ID = FA.FLDINT_ID INNER JOIN ADDRESSES A ON FA.ADDRESS_ID = A.ADDRESS_ID UNION SELECT 'COURT_PAPER', CPSA.CP_PARTY_ID, CPSA.ADDRESS_ID, '', CP.COURT_DATE, A.ADDRESS_ID, EJS_FORMAT_ADDRESS(A.ADDRESS_ID), A.LATITUDE, A.LONGITUDE, 'N', 'Y' FROM COURT_PAPER_SERVICE_ADDRS CPSA INNER JOIN COURT_PAPER_PARTIES CPP ON CPSA.CP_PARTY_ID = CPP.CP_PARTY_ID INNER JOIN COURT_PAPER_SERVICE_DETAILS CPSD ON CPP.CP_PARTY_ID = CPSD.CP_PARTY_ID INNER JOIN EJS_CODES_RELATIONS ECR ON CPSD.CP_STATUS_CODE_TYPE = ECR.CHILD_CODE_TYPE AND CPSD.CP_STATUS_CODE = ECR.CHILD_CODE AND ECR.PARENT_CODE_TYPE = 'CP_STATUS_CODES' AND ECR.PARENT_CODE = 'OPEN' INNER JOIN COURT_PAPERS CP ON CPP.CP_ID = CP.CP_ID INNER JOIN ADDRESSES A ON CPSA.ADDRESS_ID = A.ADDRESS_ID WHERE A.GEOCODED_FLAG = 'Y' AND CP.CP_STATUS_CODE_TYPE = 'CP_STATUS_CODES' AND CP.CP_STATUS_CODE = 'OPEN' UNION SELECT MUS.SCREEN_EVENT_TYPE_CODE, UFQV.SCREEN_INSTANCE_ID, NULL, '', UFDV.VALUE, A.ADDRESS_ID, EJS_FORMAT_ADDRESS(A.ADDRESS_ID), A.LATITUDE, A.LONGITUDE, 'Y', 'N' FROM MAP_USER_SCREENS MUS INNER JOIN UF_QUERY_LOCATION_VW UFQV ON MUS.ADDRESS_FIELD_ID = UFQV.FIELD_ID INNER JOIN UF_QUERY_DATE_VW UFDV ON MUS.DATE_FIELD_ID = UFDV.FIELD_ID INNER JOIN ADDRESSES A ON UFQV.ADDRESS_ID = A.ADDRESS_ID WHERE A.GEOCODED_FLAG = 'Y'