View Definition:
SELECT DE.DISPATCH_ID,
DE.DISPATCH_NUMBER,
DE.EVENT_TYPE_CODE,
DET.DESCRIPTION,
DE.DISPATCH_USER,
(SELECT FNAME||' '||LNAME FROM IJIS_USER_PROFILES WHERE LOGIN_ID = DE.DISPATCH_USER),
DE.CALL_TYPE,
(SELECT CTC.CALL_TYPE_DESC FROM CALL_TYPE_CODES CTC WHERE CTC.CALL_TYPE_CODE = DE.CALL_TYPE),
DE.CALL_DISPOSITION_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 ),
DE.CALL_PRIORITY,
DE.CLOSE_DATE,
DE.CALLER_NAME,
DE.CALLER_PHONE,
DE.INCIDENT_NOTE,
DE.CALLER_LOCATION,
DE.INCIDENT_LOCATION,
DE.COMMON_PLACE_NAME,
DE.CALL_DATE,
DE.DISP_STATUS_CODE,
(SELECT DISP.DESCRIPTION FROM EJS_CODES DISP WHERE DISP.CODE = DE.DISP_STATUS_CODE AND DISP.CODE_TYPE = DE.DISP_STATUS_CODE_TYPE) AS DISP_STATUS_DESC,
DE.CALL_PRIORITY_TYPE,
DE.CALL_RECEIVED_CODE,
(SELECT CRC.DESCRIPTION FROM CALL_RECEIVED_CODES CRC WHERE CRC.CALL_RECEIVED_CODE = DE.CALL_RECEIVED_CODE AND CRC.AGENCY_CODE = DE.AGENCY ) AS CALL_RECIEVED_DESC,
DE.DISPATCH_DATE,
DE.DISPATCH_TIME,
DE.AGENCY,
(SELECT AGENCY_DESC FROM AGENCY_CODES A WHERE DE.AGENCY = A.AGENCY_CODE),
(SELECT DEO.OFFICER_AGENCY FROM DISPATCH_EVENT_OFFICERS DEO WHERE DE.DISPATCH_ID = DEO.DISPATCH_ID AND PRIMARY = 'Y' AND ROWNUM = 1),
(SELECT AGENCY_DESC FROM AGENCY_CODES AC, DISPATCH_EVENT_OFFICERS DEO WHERE AC.AGENCY_CODE = DEO.OFFICER_AGENCY AND DEO.DISPATCH_ID = DE.DISPATCH_ID AND PRIMARY = 'Y' AND ROWNUM = 1),
CASE WHEN DE.BEAT IS NOT NULL AND DE.SUB_BEAT IS NOT NULL THEN DE.BEAT||'-'||DE.SUB_BEAT ELSE DE.BEAT||DE.SUB_BEAT END,
DE.LATITUDE,
DE.LONGITUDE,
DE.CREATOR_DATE,
DE.UPDATOR_DATE,
--DEO.OFFICER_FNAME||' '||deo.OFFICER_LNAME,
--DEC.INC_REPORT_NUMBER AS INC_REPORT_NUMBER,
--DEC.ASSIGNED_TO,
--DEC.AGENCY,
--(SELECT AGENCY_DESC FROM AGENCY_CODES ADEC WHERE ADEC.AGENCY_CODE = DEC.AGENCY) AS CASE_AGENCY,
--DEC.ASSIGNED_INCIDENT
JOIN(CURSOR(SELECT distinct dec.INC_REPORT_NUMBER||'-'||dec.AGENCY||decode(dec.ASSIGNED_TO,NULL,NULL,' / '||dec.ASSIGNED_TO)
FROM DISPATCH_EVENT_CASES dec
WHERE dec.dispatch_id = de.dispatch_id),' | ') AS CASE_INFO,
JOIN(CURSOR(SELECT distinct deo.OFFICER_LNAME||', '||deo.OFFICER_FNAME
FROM DISPATCH_EVENT_OFFICERS deo
WHERE deo.dispatch_id = de.dispatch_id
AND deo.primary = 'Y'),' | ') AS PRIMARY_OFFICERS
FROM DISPATCH_EVENTS de
LEFT OUTER JOIN DISPATCH_EVENT_TYPES DET ON DE.EVENT_TYPE_CODE = DET.EVENT_TYPE_CODE
--LEFT OUTER JOIN DISPATCH_EVENT_OFFICERS DEO ON DE.DISPATCH_ID = DEO.DISPATCH_ID AND PRIMARY = 'Y'
--LEFT OUTER JOIN DISPATCH_EVENT_CASES DEC ON DE.DISPATCH_ID = DEC.DISPATCH_ID
