SELECT UPPER(inst.db_schema) AS COUNTY, AC.SOLR_STATE AS STATE, UPPER(inst.db_schema) ||'_'||I.INCIDENT_ID||'_'||ISUP.SUPP_SEQ AS ID, I.INCIDENT_ID as INCIDENT_ID, ISUP.SUPP_SEQ, ISUP.AGENCY_ONLY, ISUP.RESPONSIBLE_USER_ID, ISUP.SECURITY_LEVEL, JOIN(CURSOR(SELECT IW.WORKGROUP_CODE FROM INCIDENT_WORKGROUPS IW WHERE IW.INC_INCIDENT_ID = I.INCIDENT_ID AND IW.SUPP_SEQ = ISUP.SUPP_SEQ)) as WORKGROUPS, I.INC_REPORT_NUMBER AS REPORTNUMBER, TO_CHAR(I.START_DATE,'MM/DD/YYYY') AS REPORTDATE, TO_CHAR(I.START_DATE,'YYYY-MM-DD') AS STARTDATESORT, I.START_DATE AS START_DATE_DATE, JOIN(CURSOR(SELECT ISUP.SUPP_SEQ FROM INCIDENT_SUPPLEMENTS ISUP WHERE ISUP.INC_INCIDENT_ID = I.INCIDENT_ID)) AS SUPPS, JOIN(CURSOR(SELECT OC.OFFENSE_CODE || ' - ' || OC.OFFENSE_DESC FROM OFFENSES O INNER JOIN OFFENSE_CODES OC ON O.OFFNS_CD_OFFENSE_CODE = OC.OFFENSE_CODE WHERE O.INC_INCIDENT_ID = I.INCIDENT_ID AND O.SUPP_SEQ = ISUP.SUPP_SEQ ORDER BY O.UCR_NUMBER ASC )) AS OFFENSES, I.SUMMARY, A.LATITUDE, A.LONGITUDE, EJS_FORMAT_ADDRESS(IA.ADDRESS_ID) AS LOCATION, AC.AGENCY_CODE, AC.AGENCY_DESC, ISUP.RESTRICT_SG_EXCLSV_FLAG AS WORKGROUPRESTRICTED, STRIP_CLOB_HTML(EJS_APPEND_NARRATIVES(I.INCIDENT_ID,ISUP.SUPP_SEQ)) AS NARRATIVES, EJS_APPEND_OFFNAMES(ISUP.INC_INCIDENT_ID,ISUP.SUPP_SEQ) AS OFFNAMES, EJS_APPEND_VICTIMNAMES(ISUP.INC_INCIDENT_ID,ISUP.SUPP_SEQ) AS VICTIMNAMES, EJS_APPEND_VEHICLEDESCS(ISUP.INC_INCIDENT_ID,ISUP.SUPP_SEQ) AS VEHICLEDESCS, EJS_APPEND_PROPERTIES(ISUP.INC_INCIDENT_ID,ISUP.SUPP_SEQ) AS PROPERTYDESCS, CASE WHEN I.START_DATE IS NOT NULL THEN CONV_SOLR_CREATOR_DATE(I.START_DATE, (SELECT ec.code FROM ejs_codes ec WHERE ec.code_type = 'DATABASE_TIMEZONE' AND ROWNUM = 1)) ELSE '' END AS SOLR_CREATION_DATE FROM INSTALL INST, 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 INNER JOIN INCIDENT_SUPPLEMENTS ISUP ON I.INCIDENT_ID = ISUP.INC_INCIDENT_ID INNER JOIN AGENCY_CODES AC ON ISUP.SUPP_AGENCY_CODE = AC.AGENCY_CODE WHERE ISUP.ISC_STATUS_CODE = 'A'