SELECT DISTINCT i.incident_id, i.inc_report_number, isupp.supp_agency_code, i.report_date, i.start_date, i.end_date, isupp.isc_status_code, --isupp.isc_status_code_type, --JOIN (CURSOR (SELECT ISUP.SUPP_SEQ -- FROM INCIDENT_SUPPLEMENTS ISUP -- WHERE ISUP.INC_INCIDENT_ID = I.INCIDENT_ID) --) AS SUPP_SEQS, isupp.supp_seq, isupp.responsible_user_id, isupp.agency_only, isupp.cid, isupp.security_level, i.summary, --'' AS rtc_code_type, --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.sex_code_type, --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 || ' ' || mname ELSE RTRIM (m.fname || ' ' || mname) END AS OFFENDER_NAME, JOIN ( CURSOR ( SELECT CASE WHEN m.lname IS NOT NULL THEN m.lname || ', ' || m.fname || ' ' || mname || ' (' || rc.role_desc || ') - SEX: ' || sex.description || ' - RACE: ' || race.description ELSE RTRIM (m.fname || ' ' || mname) || ' (' || rc.role_desc || ') - SEX: ' || sex.description || ' - RACE: ' || race.description END FROM incident_people ip, incident_person_roles ipr, people p, master_people mp, master_names m, role_codes rc, ejs_codes race, ejs_codes sex WHERE mp.master_person_id = p.master_person_id AND m.per_person_id = p.person_id AND m.name_type_code = 'P' AND ip.per_person_id = p.person_id AND ip.inc_per_id = ipr.inc_per_id AND ipr.role_type = rc.role_type AND race.code = mp.race_code AND race.code_type = mp.race_code_type AND sex.code = mp.sex_code AND sex.code_type = mp.sex_code_type AND rc.inc_section_code = 'OFFENDERS' AND ip.inc_incident_id = isupp.inc_incident_id AND ip.supp_seq = isupp.supp_seq), ' | ') 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, rc.role_desc, isupp.approving_agency_Code, (JOIN ( CURSOR ( SELECT o.offns_cd_offense_code || ' - ' || OC.OFFENSE_DESC || DECODE ( O.ARREST_CHARGE_CODE, NULL, NULL, ' / ' || O.ARREST_CHARGE_CODE || ' - ' || ACC.ARREST_CHA_DESC) FROM offenses o, offense_codes oc, arrest_charge_codes acc WHERE O.OFFNS_CD_OFFENSE_CODE = OC.OFFENSE_CODE AND O.ARREST_CHARGE_CODE = ACC.ARREST_CHARGE_CODE(+) AND O.INC_INCIDENT_ID = ISUPP.INC_INCIDENT_ID AND O.SUPP_SEQ = ISUPP.SUPP_SEQ), ' | ')) AS OFFENSE_CHARGE_INFO, o.ucr_number, o.offense_number, o.bias_code, (SELECT bc.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 || DECODE (O.ARREST_CHARGE_CODE, NULL, NULL, ' / ' || O.ARREST_CHARGE_CODE) 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_code, o.fec_forced_entry_code, o.remarks, (JOIN ( CURSOR ( SELECT DECODE ( pr.description, NULL, TO_CHAR (pr.property_id), TO_CHAR (pr.property_id) || ' - ' || pr.description) FROM properties pr WHERE pr.property_id = iprop.property_id), '| ')) property_ids, prop.property_id, prop.property_type_code , (SELECT property_type_desc FROM property_type_codes WHERE 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, (JOIN (CURSOR (SELECT ev.evidence_id FROM evidence ev WHERE ev.pro_property_id = prop.property_id), ', ')) evidence_ids, 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, (SELECT elc.evid_loc_desc FROM EVIDENCE_LOCATION_CODES elc WHERE elc.EVID_LOC_CODE = e.EVID_LOC_CODE AND ELC.AGNCY_CD_AGENCY_CODE = E.AGNCY_CD_AGENCY_CODE ) AS evid_loc_desc, e.agncy_cd_agency_code, (SELECT agency_desc FROM agency_codes ac WHERE e.agncy_cd_agency_code = ac.agency_code) AS evid_description, e.evidence_id, e.description, e.disposition_code, (SELECT disp.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 , (SELECT arrest_date FROM incident_arrests ia, arrests arr WHERE IA.ARR_ARREST_ID = ARR.ARREST_ID AND ARR.PERSON_ID = P.PERSON_ID AND IA.INCSUP_INC_INCIDENT_ID = ISUPP.INC_INCIDENT_ID AND IA.INCSUP_SUPP_SEQ = ISUPP.SUPP_SEQ) AS arrest_date FROM incident_people ip, incidents i, people p, master_people mp, master_names m, incident_supplements isupp, incident_person_roles ipr, role_codes rc, properties prop, incident_properties iprop, evidence e, offenses o, offense_codes oc WHERE i.incident_id = isupp.inc_incident_id AND ip.inc_incident_id = isupp.inc_incident_id AND ip.supp_seq = isupp.supp_seq AND ip.per_person_id = p.person_id AND mp.master_person_id = p.master_person_id AND m.per_person_id = p.person_id AND m.name_type_code = 'P' AND iprop.incsup_incident_id = isupp.inc_incident_id AND iprop.incsup_supp_seq = isupp.supp_seq AND prop.property_id = iprop.property_id AND iprop.property_id = e.pro_property_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 ip.inc_per_id = ipr.inc_per_id AND ipr.role_type = rc.role_type AND rc.inc_section_code = 'OFFENDERS'