View Definition:
SELECT
i.incident_id,
i.inc_report_number,
isupp.supp_agency_code
"SUPP_AGENCY_CODE",
i.report_date,
i.start_date,
i.end_date,
isupp.isc_status_code
"REPORT_STATUS",
isupp.supp_seq,
isupp.responsible_user_id
"REPORT_OWNER",
isupp.agency_only,
isupp.cid,
isupp.security_level,
i.summary
"REPORT_SUMMARY",
a.address_id,
ejs_addresses.address_summary (a.address_id)
address_summary,
a.street_number,
a.dirct_cd_direction_code
"STREET_DIRECTION_CODE",
a.street_name,
a.street_cd_street_type_code
"STREET_TYPE_CODE",
a.sub_number
"STREET_SUB_NUMBER",
a.city
"ADDR_CITY",
a.state_cd_state_code
"ADDR_STATE_CODE",
a.zip5,
a.zip4,
ia.beat
"REPORT_ADDR_BEAT",
ia.sub_beat
"REPORT_ADDR_SUB_BEAT",
m.lname,
m.fname,
m.mname,
TRIM (m.lname || ', ' || m.fname || ' ' || m.mname)
person_name_lfm,
IP.APPROX_AGE,
IPR.ROLE_TYPE,
mp.sex_code,
EJS_GET_EJS_CODE_DESCR ('SEX_CODES', mp.sex_code)
"SEX_DESC",
mp.race_code,
EJS_GET_EJS_CODE_DESCR ('RACE_CODES', mp.sex_code)
"RACE_DESC",
ofcr.officer_id,
ofcr.lname
"OFFICER_LNAME",
ofcr.fname
"OFFICER_FNAME",
ofcr.agncy_cd_agency_code
"OFFICER_AGENCY_CODE",
ofcr.internal_id
"BADGE_NO",
ofcr.title
"OFFICER_TITLE",
o.offense_number,
o.offns_cd_offense_code
"OFFENSE_CODE",
oc.offense_desc
"OFFENSE_DESC",
o.place_place_code
"OFFENSE_PLACE_CODE",
EJS_GET_EJS_CODE_DESCR ('PLACE_CODES', o.place_place_code)
"OFFENSE_PLACE_DESC",
o.offnse_cd_offense_status_code
"OFFENSE_STATUS_CODE",
o.bias_code
"OFFENSE_BIAS_CODE",
EJS_GET_EJS_CODE_DESCR ('BIAS_CODES', o.bias_code)
"OFFENSE_BIAS_DESC",
o.fec_forced_entry_code
" OFFENSE_FORCED_ENTRY_CODE",
o.ac_code
"OFFENSE_CSAU_CODE"
FROM incidents i,
incident_supplements isupp,
officer_incidents oi,
addresses a,
officers ofcr,
master_names m,
incident_people ip,
people p,
master_people mp,
incident_addresses ia,
incident_person_roles ipr,
offenses o,
offense_codes OC
WHERE isupp.inc_incident_id = i.incident_id
AND isupp.inc_incident_id = ip.inc_incident_id
AND isupp.supp_seq = ip.supp_seq
AND isupp.inc_incident_id = oi.inc_incident_id(+)
AND isupp.supp_seq = oi.supp_seq(+)
AND i.incident_id = ia.incident_id(+)
AND ip.per_person_id = p.person_id
AND p.person_id = m.per_person_id
AND p.master_person_id = mp.master_person_id
AND ia.address_id = a.address_id
AND oi.off1_officer_id = ofcr.officer_id(+)
AND IP.INC_PER_ID = IPR.INC_PER_ID
AND o.inc_incident_id = isupp.inc_incident_id
AND o.supp_seq = isupp.supp_seq
AND o.offns_cd_offense_code=OC.OFFENSE_CODE
AND O.SUPP_SEQ = (SELECT MAX(O2.SUPP_SEQ)
FROM OFFENSES O2 WHERE O2.INC_INCIDENT_ID = O.INC_INCIDENT_ID
AND O2.OFFENSE_NUMBER = O.OFFENSE_NUMBER
AND O2.OFFNS_CD_OFFENSE_CODE = O.OFFNS_CD_OFFENSE_CODE)
