SELECT distinct ic.inc_case_id, ic.inc_case_number, ic.agency_code, (select agency_desc from agency_codes where agency_code = ic.agency_code), ic.unit_code, ic.security_level "CASE_SECURITY_LEVEL", ici.incident_id, ic.rsc_status_code "REVIEW_STATUS_CODE", ec_rev.description "REVIEW_STATUS_DESC", ic.case_status_code, ec_cs.description "CASE_STATUS_DESC", ic.sc1_solvability_code "CASE_SOLVABILITY_CODE", ec_slv.description "CASE_SOLVABILITY_DESC", ic.date_cleared, ic.date_expired, ic.review_update, i.inc_report_number, i.agncy_cd_agency_code "INC_AGENCY_CODE", ac.agency_desc "INC_AGENCY_DESC", i.report_date, i.start_date, i.end_date, i.summary "REPORT_SUMMARY", ia.address_id, NVL(ejs_addresses.address_summary(ia.address_id),NULL) "ADDRESS_SUMMARY", 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.CITy, IA.CITY_TYPE_CODE, IA.COUNTY_CODE, (select cnty.description FROM ejs_codes cnty WHERE cnty.code_type = ia.county_code_type AND cnty.code = ia.county_code), IA.TOWNSHIP_CODE, (select twn.description FROM ejs_codes twn WHERE twn.code_type = ia.township_code_type AND twn.code = ia.township_code), ia.loc_agency_code, (select agc.agency_desc FROM agency_codes agc WHERE agc.agency_code = ia.loc_agency_code), supp.supp_seq, supp.supp_agency_code, supp.isc_status_code, supp.supp_report_date "SUPP_REPORT_DATE", supp.supp_report_time "SUPP_REPORT_TIME", supp.responsible_user_id "SUPP_REPORT_OWNER", supp.approval_date "SUPP_APPROVAL_DATE", supp.approval_user_id "SUPP_APPROVER", supp.cid "SUPP_CID", supp.security_level "SUPP_SECURITY_LEVEL", supp.agency_only "SUPP_AGENCY_ONLY", ico.end_date "OFFICER_END_DATE", ico.assign_date "OFFICER_ASSIGN_DATE", ICO.ROLE_CODE "OFFICER_ROLE_CODE", (select r.description FROM ejs_codes r WHERE r.code_type = ICO.ROLE_CODE_TYPE AND r.code = ico.role_code) "OFFICER_ROLE_DESC", off.officer_id, off.lname "OFFICER_LNAME", off.fname "OFFICER_FNAME", off.lname || ', ' || fname "OFFICER_NAME", off.lname || ', ' || fname || DECODE (internal_id, NULL, NULL, ' - ' || internal_id) "OFFICER_NAME_BADGE", off.title "OFFICER_TITLE", off.supervisor, off.user_id "OFFICER_LOGIN", off.internal_id "OFFICER_BADGE_NO", off.detective, off.active "ACTIVE_OFFICER", ip.inc_per_id, IP.PER_PERSON_ID, IP.MN_MN_ID, NVL(EJS_PERSON_PKG.GET_PERSON_SUMMARY(ip.per_person_id, ip.mn_mn_id),NULL), IPR.ROLE_TYPE, RC.ROLE_DESC, RC.INC_SECTION_CODE, o.ucr_number, o.offense_number, o.bias_code, bc.description bias_code_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, o.ac_code csau_csau_code, o.fec_forced_entry_code, o.remarks, SYSDATE "TODAYS_DATE" FROM officers off, incident_cases ic, incident_case_officers ico, incidents i, incident_supplements supp, incident_people ip, incident_person_roles ipr, incident_addresses ia, inc_case_incidents ici, ejs_codes ec_rev, ejs_codes ec_cs, ejs_codes ec_slv, ejs_codes ec_stat, agency_codes ac, offenses o, role_codes rc, offense_codes OC, EJS_CODES BC, EJS_CODES PC WHERE off.officer_id = ico.officer_id AND i.incident_id = supp.inc_incident_id AND i.incident_id = ia.incident_id(+) AND ic.inc_case_id = ici.inc_case_id(+) AND ic.inc_case_id = ico.inc_case_id AND ici.incident_id = i.incident_id(+) AND ic.rsc_status_code = ec_rev.code(+) AND ic.rsc_status_code_type = ec_rev.code_type(+) AND ac.agency_code = i.agncy_cd_agency_code AND ic.case_status_code = ec_cs.code(+) AND ic.case_status_code_type = ec_cs.code_type(+) AND ic.sc1_solvability_code = ec_slv.code(+) AND ic.sc1_solvability_code_type = ec_slv.code_type(+) AND supp.isc_status_code = ec_stat.code AND supp.isc_status_code_type = ec_stat.code_type AND supp.inc_incident_id = IP.INC_INCIDENT_ID (+) AND SUPP.SUPP_SEQ = IP.SUPP_SEQ (+) AND IP.INC_PER_ID = IPR.INC_PER_ID (+) AND IPR.ROLE_TYPE = RC.ROLE_TYPE (+) AND o.inc_incident_id = i.incident_id AND o.supp_seq = supp.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(+)