SELECT DISTINCT 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, isupp.agency_only, isupp.cid, isupp.security_level, i.summary, p.master_person_id AS person_id, mp.sex_code sex_sex_code, (select description from ejs_codes es where es.code = MP.SEX_CODE and es.code_type = MP.SEX_CODE_TYPE) as SEX_DESC, mp.race_code race_race_code, (select description from ejs_codes es where es.code = MP.RACE_CODE and es.code_type = MP.RACE_CODE_TYPE) as RACE_DESC, m.mn_id, m.name_type_code, m.lname, m.fname, m.dob, m.ssn, CASE WHEN m.lname IS NOT NULL THEN m.lname||', '||m.fname||' '||m.mname ELSE RTRIM(m.fname||' '||m.mname) END AS OFFENDER_NAME, EJS_PERSON_PKG.GET_PERSON_SUMMARY(P.PERSON_ID) as OFFENDER_INFO, CASE WHEN EXISTS (SELECT 1 FROM inc_per_forces ipr WHERE ipr.inc_per_id = ip.inc_per_id) THEN 'Y' ELSE NULL END AS rtc_code, FLOOR (MONTHS_BETWEEN (SYSDATE, m.dob) / 12) AS CURRENT_AGE, ipr.role_type AS role_role_type, rc.role_desc, isupp.approving_agency_Code, o.ucr_number, o.offense_number, o.bias_code bias_bias_code, (select description from ejs_codes bc where bc.code = o.bias_code and bc.code_type = o.bias_code_type) bias_code_desc, o.offnse_cd_offense_status_code, o.offns_cd_offense_code, OC.OFFENSE_DESC, o.offns_cd_offense_code||' - '||OC.OFFENSE_DESC OFFENSE_CODE_DESC, o.arrest_charge_code, (select arrest_charge_code||' - '||arrest_cha_desc from arrest_charge_codes where arrest_charge_code = o.arrest_charge_code) ARREST_CHARGE_DESC, o.place_place_code, (select description from ejs_codes pc where pc.code = o.place_place_code and pc.code_type = o.place_code_type) place_code_desc, o.ac_code csau_csau_code, o.fec_forced_entry_code, o.remarks, prop.property_id, ejs_get_property_desc(prop.property_id), prop.property_type_code, (select ptc.property_type_desc from property_type_codes ptc where ptc.property_type = prop.property_type_code) property_type_desc, prop.evidence, prop.document, prop.type_of_quantity, prop.primary_color, prop.secondary_color, prop.drug_type, prop.quantity, prop.description, prop.make, prop.model, prop.serial_number, prop.VALUE, prop.misc, prop.document_number, prop.bank, prop.document_date, prop.account_name, prop.account_number, prop.payable_to, prop.amount, prop.endorsee, prop.barrel_length, prop.calgag_code, prop.gun_act_code, prop.gun_typ_code, e.status_code e_status_code, (select description from ejs_codes stat where stat.code = e.status_code and stat.code_type = e.status_code_type) evid_status_desc, e.evid_loc_code, e.agncy_cd_agency_code, (select agency_desc from agency_codes ac where e.agncy_cd_agency_code = ac.agency_code) evid_description, e.evidence_id, e.description, e.disposition_code, (select description from ejs_codes disp where disp.code = e.disposition_code and disp.code_type = e.disposition_code_type) evid_disp_desc, e.sched_disp_date, e.disp_order_date, e.disposition_date, arr.arrest_date AS arrest_date, itype.itc_code, (select description from ejs_codes ityp where ityp.code = itype.itc_code and ityp.code_type = itype.itc_code_type) itc_desc, IA.ADDRESS_ID, UPPER(EJS_FORMAT_ADDRESS(IA.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) FROM INCIDENTS I INNER JOIN INCIDENT_SUPPLEMENTS ISUPP ON I.INCIDENT_ID = ISUPP.INC_INCIDENT_ID LEFT OUTER JOIN INCIDENT_TYPES ITYPE ON I.INCIDENT_ID = ITYPE.INCIDENT_ID INNER JOIN INCIDENT_PEOPLE IP ON ISUPP.INC_INCIDENT_ID = IP.INC_INCIDENT_ID AND ISUPP.SUPP_SEQ = IP.SUPP_SEQ INNER JOIN PEOPLE P ON IP.PER_PERSON_ID = P.PERSON_ID INNER JOIN MASTER_PEOPLE MP ON P.MASTER_PERSON_ID = MP.MASTER_PERSON_ID INNER JOIN MASTER_NAMES M ON P.PERSON_ID = M.PER_PERSON_ID AND M.NAME_TYPE_CODE = 'P' LEFT OUTER JOIN INCIDENT_PERSON_ROLES IPR ON IP.INC_PER_ID = IPR.INC_PER_ID LEFT OUTER JOIN ROLE_CODES RC ON IPR.ROLE_TYPE = RC.ROLE_TYPE AND RC.INC_SECTION_CODE = 'OFFENDERS' LEFT OUTER JOIN OFFENSES O ON ISUPP.INC_INCIDENT_ID = O.INC_INCIDENT_ID AND ISUPP.SUPP_SEQ = O.SUPP_SEQ LEFT OUTER JOIN OFFENSE_CODES OC ON O.OFFNS_CD_OFFENSE_CODE = OC.OFFENSE_CODE LEFT OUTER JOIN INCIDENT_ADDRESSES IA ON I.INCIDENT_ID = IA.INCIDENT_ID INNER JOIN INCIDENT_PROPERTIES IPROP ON ISUPP.INC_INCIDENT_ID = IPROP.INCSUP_INCIDENT_ID AND ISUPP.SUPP_SEQ = IPROP.INCSUP_SUPP_SEQ INNER JOIN PROPERTIES PROP ON IPROP.PROPERTY_ID = PROP.PROPERTY_ID INNER JOIN EVIDENCE E ON PROP.PROPERTY_ID = E.PRO_PROPERTY_ID LEFT OUTER JOIN INCIDENT_ARRESTS IARR ON ISUPP.INC_INCIDENT_ID = IARR.INCSUP_INC_INCIDENT_ID AND ISUPP.SUPP_SEQ = IARR.INCSUP_SUPP_SEQ LEFT OUTER JOIN ARRESTS ARR ON IARR.ARR_ARREST_ID = ARR.ARREST_ID AND P.PERSON_ID = ARR.PERSON_ID