View Definition:
SELECT
DE.DISPATCH_ID AS DISPATCH_ID,
DE.AGENCY AS AGENCY,
AC.AGENCY_DESC AS AGENCY_DESC,
AC.AGENCY_DISPLAY_CODE,
DE.DISPATCH_DATE,
DE.DISPATCH_TIME,
DE.CLOSE_DATE,
DE.ONSCENE_DATE,
DE.CALL_DATE,
IUP.FNAME || ' ' || IUP.LNAME AS DISPATCH_USER_NAME,
DE.DISPATCH_USER AS DISPATCH_USER,
DE.CALL_TYPE AS CALL_TYPE_CODE,
nvl(CTC.CALL_TYPE_DESC,de.call_type) as call_type_desc,
DE.CALL_DISPOSITION_CODE AS CALL_DISP_CODE,
(SELECT CDC.CALL_DISPOSITION_DESC FROM CALL_DISPOSITION_CODES CDC
WHERE CDC.CALL_DISPOSITION_CODE = DE.CALL_DISPOSITION_CODE
AND CDC.EVENT_TYPE_CODE = DE.EVENT_TYPE_CODE
AND CDC.AGENCY_CODE = DE.AGENCY) AS CALL_DISP_DESC,
DE.CALL_PRIORITY AS CALL_PRIORITY_CODE,
(SELECT EC.DESCRIPTION FROM EJS_CODES EC
WHERE EC.CODE_TYPE = 'DISPATCH_PRIORITY_CODES'
AND EC.CODE = DE.CALL_PRIORITY) AS CALL_PRIORITY_DESC,
DE.EVENT_TYPE_CODE,
DE.INCIDENT_NOTE AS INCIDENT_NOTE,
DE.INCIDENT_LOCATION AS INCIDENT_LOCATION,
DE.LATITUDE,
DE.LONGITUDE,
DE.COMMON_PLACE_NAME AS COMMON_PLACE_NAME,
DE.DISP_STATUS_CODE AS DISP_STATUS_CODE,
(SELECT EC.DESCRIPTION FROM EJS_CODES EC
WHERE EC.CODE_TYPE = DE.DISP_STATUS_CODE_TYPE
AND EC.CODE = DE.DISP_STATUS_CODE) AS DISP_STATUS_DESC,
DE.DISPATCH_NUMBER,
DE.CALLER_NAME AS CALLER_NAME,
DE.CALLER_PHONE AS CALLER_PHONE,
AC.AGENCY_DISPLAY_CODE AS AGENCY_DISPLAY_CODE,
(
SELECT
LISTAGG(x.inc_report_number, ',') WITHIN GROUP (ORDER BY x.inc_report_number)
FROM
(
SELECT dec.dispatch_id,dec.inc_report_number FROM DISPATCH_EVENT_CASES dec
) x WHERE x.dispatch_id = de.dispatch_id
) AS inc_report_numbers,
(SELECT AC.AGENCY_DESC
FROM DISPATCH_EVENT_OFFICERS DEO
INNER JOIN AGENCY_CODES AC ON DEO.UNIT_AGENCY = AC.AGENCY_CODE
WHERE DEO.DISPATCH_ID = DE.DISPATCH_ID
AND DEO.PRIMARY = 'Y'
AND ROWNUM = 1
) AS UNIT_AGENCY_DESC,
(SELECT AC.AGENCY_CODE
FROM DISPATCH_EVENT_OFFICERS DEO
INNER JOIN AGENCY_CODES AC ON DEO.OFFICER_AGENCY = AC.AGENCY_CODE
WHERE DEO.DISPATCH_ID = DE.DISPATCH_ID
AND DEO.PRIMARY = 'Y'
AND ROWNUM = 1
) AS OFFICER_AGENCY_CODE,
(SELECT AC.AGENCY_DESC
FROM DISPATCH_EVENT_OFFICERS DEO
INNER JOIN AGENCY_CODES AC ON DEO.OFFICER_AGENCY = AC.AGENCY_CODE
WHERE DEO.DISPATCH_ID = DE.DISPATCH_ID
AND DEO.PRIMARY = 'Y'
AND ROWNUM = 1
) AS OFFICER_AGENCY_DESC,
(SELECT AC.AGENCY_CODE
FROM DISPATCH_EVENT_OFFICERS DEO
INNER JOIN AGENCY_CODES AC ON DEO.SERVICE_AGENCY = AC.AGENCY_CODE
WHERE DEO.DISPATCH_ID = DE.DISPATCH_ID
AND DEO.PRIMARY = 'Y'
AND ROWNUM = 1
) AS SERVICE_AGENCY_CODE,
(SELECT AC.AGENCY_DESC
FROM DISPATCH_EVENT_OFFICERS DEO
INNER JOIN AGENCY_CODES AC ON DEO.SERVICE_AGENCY = AC.AGENCY_CODE
WHERE DEO.DISPATCH_ID = DE.DISPATCH_ID
AND DEO.PRIMARY = 'Y'
AND ROWNUM = 1
) AS SERVICE_AGENCY_DESC,
(SELECT GET_OFF_EMP_NAME(O.OFFICER_ID, 'O')
FROM DISPATCH_EVENT_OFFICERS DEO
INNER JOIN OFFICERS O ON DEO.OFFICER_ID = O.OFFICER_ID
WHERE DEO.DISPATCH_ID = DE.DISPATCH_ID
AND DEO.PRIMARY = 'Y'
AND ROWNUM = 1
) AS PRIMARY_OFFICER,
DE.SUB_BEAT,
DE.BEAT,
DE.STREET_NUMBER,
DE.STREET_DIRECTION,
DE.STREET_NAME,
DE.STREET_CD_STREET_TYPE_CODE,
DE.STREET_DIR_SUFFIX,
DE.CROSS_STREET_NUMBER,
DE.CROSS_STREET_DIRECTION,
DE.CROSS_STREET,
DE.CROSS_STREET_TYPE_CODE,
DE.CROSS_STREET_DIR_SUFFIX,
DE.SUB_NUMBER,
DE.SUB_TYPE,
DE.CITY,
DE.STATE,
DE.ZIP5,
DE.ZIP4,
DE.COUNTY
FROM DISPATCH_EVENTS DE
LEFT OUTER JOIN IJIS_USER_PROFILES IUP ON DE.DISPATCH_USER = IUP.LOGIN_ID
LEFT OUTER JOIN CALL_TYPE_CODES CTC ON CTC.CALL_TYPE_CODE = DE.CALL_TYPE AND CTC.AGENCY_CODE = DE.AGENCY
--LEFT OUTER JOIN PSEUDO_ORG_DESCRIPTORS POD ON POD.AGENCY_CODE = '''||In_Agency||'''
LEFT OUTER JOIN AGENCY_CODES AC ON AC.AGENCY_CODE = DE.AGENCY
