View Definition:
SELECT i.incident_id,
i.inc_report_number,
isupp.supp_agency_code,
(SELECT agency_desc
FROM agency_codes
WHERE agency_code = isupp.supp_agency_code),
i.report_date,
i.start_date,
i.end_date,
isupp.isc_status_code,
(SELECT isc.description
FROM ejs_codes isc
WHERE isc.code_type = isupp.isc_status_code_type
AND isc.code = isupp.isc_status_code),
isupp.supp_seq,
isupp.responsible_user_id,
(SELECT internal_id
FROM officers
WHERE user_id = isupp.responsible_user_id),
isupp.agency_only,
isupp.cid,
isupp.security_level,
i.summary,
ISUPP.APPROVING_AGENCY_CODE,
(SELECT ag2.agency_desc
FROM agency_codes ag2
WHERE ISUPP.APPROVING_AGENCY_CODE = ag2.agency_code),
a.address_id,
EJS_FORMAT_ADDRESS (A.ADDRESS_ID),
ejs_addresses.intersect_street_summary (a.address_id),
a.longitude,
a.latitude,
INITCAP (a.city),
INITCAP (a.geo_city),
a.state_cd_state_code,
(SELECT st.description
FROM ejs_codes st
WHERE st.code_type = a.state_cd_state_code_type
AND st.code = a.state_cd_state_code),
A.ZIP5,
TO_CHAR (A.ZIP4, 'FM0000'),
(SELECT LISTAGG (acn.common_place_name, ' | ')
WITHIN GROUP (ORDER BY acn.addr_address_id)
FROM address_common_names acn
WHERE addr_address_id = A.ADDRESS_ID),
ia.inc_addr_comment,
CASE
WHEN IA.BEAT IS NOT NULL AND IA.SUB_BEAT IS NOT NULL
THEN
IA.BEAT || '-' || IA.SUB_BEAT
ELSE
IA.BEAT || IA.SUB_BEAT
END,
IA.LOC_AGENCY_CODE,
(SELECT ag1.agency_desc
FROM agency_codes ag1
WHERE IA.LOC_AGENCY_CODE = ag1.agency_code),
IA.CITY,
(SELECT C.description
FROM ejs_codes C
WHERE C.code_type = IA.CITY_TYPE_CODE AND C.code = IA.CITY),
IA.COUNTY_CODE,
(SELECT CNTY.description
FROM ejs_codes CNTY
WHERE CNTY.code_type = IA.COUNTY_CODE_TYPE
AND CNTY.code = IA.COUNTY_CODE),
TOWNSHIP_CODE,
(SELECT TWN.description
FROM ejs_codes TWN
WHERE TWN.code_type = IA.TOWNSHIP_CODE_TYPE
AND TWN.code = IA.TOWNSHIP_CODE),
o.ucr_number,
o.offense_number,
o.bias_code bias_bias_code,
bc.description bias_desc,
o.offnse_cd_offense_status_code,
(SELECT offense_status_desc
FROM offense_status_codes
WHERE offense_status_code = o.offnse_cd_offense_status_code),
o.offns_cd_offense_code,
OC.OFFENSE_DESC,
o.place_place_code,
PC.DESCRIPTION place_desc,
o.ac_code csau_csau_code,
(SELECT csau.csau_desc
FROM csau_codes csau
WHERE csau_code = o.ac_code),
o.fec_forced_entry_code,
o.remarks,
nbr.nibrs_code,
(SELECT n.nibrs_desc
FROM nibrs_codes n
WHERE n.nibrs_code = nbr.nibrs_code)
FROM offenses o,
incidents i,
incident_supplements isupp,
offense_codes OC,
EJS_CODES BC,
EJS_CODES PC,
nibrs_offenses nbr,
incident_addresses ia,
addresses a
WHERE o.inc_incident_id = i.incident_id
AND isupp.inc_incident_id = i.incident_id
AND I.INCIDENT_ID = IA.INCIDENT_ID(+)
AND IA.ADDRESS_ID = A.ADDRESS_ID(+)
AND o.supp_seq = isupp.supp_seq
AND o.offns_cd_offense_code = OC.OFFENSE_CODE
AND O.BIAS_CODE = BC.CODE(+)
AND O.BIAS_CODE_TYPE = BC.CODE_TYPE(+)
AND O.PLACE_PLACE_CODE = PC.CODE(+)
AND O.PLACE_CODE_TYPE = PC.CODE_TYPE(+)
AND o.offns_cd_offense_code = NBR.OFFENSE_CODE(+)
