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", -- Lead Investigator ico.assign_date, ico.end_date, CASE WHEN ico.role_code = 'LEAD_INVESTIGATOR' THEN off.lname|| ', '|| off.fname || DECODE (off.internal_id, NULL, NULL, ' - ' || off.internal_id) ELSE NULL END, -- Assisting Officer ico2.assign_date, ico2.end_date, CASE WHEN ico2.role_code = 'ASSISTING_OFFICER' THEN off2.lname|| ', '|| off2.fname || DECODE (off2.internal_id, NULL, NULL, ' - ' || off2.internal_id) ELSE NULL END, -- Victims JOIN(CURSOR(SELECT NVL(EJS_PERSON_PKG.GET_PERSON_SUMMARY(ip.per_person_id, ip.mn_mn_id)||' ('||RC.ROLE_DESC||')',NULL) FROM incident_people ip, incident_person_roles ipr, role_codes rc WHERE IP.INC_INCIDENT_ID = supp.inc_incident_id AND IP.SUPP_SEQ = SUPP.SUPP_SEQ AND IP.INC_PER_ID = IPR.INC_PER_ID AND IPR.ROLE_TYPE = RC.ROLE_TYPE AND RC.INC_SECTION_CODE = 'VICTIMS' ),' | '), --CASE WHEN RC.INC_SECTION_CODE = 'VICTIMS' THEN -- NVL(EJS_PERSON_PKG.GET_PERSON_SUMMARY(ip.per_person_id, ip.mn_mn_id)||' ('||RC.ROLE_DESC||')',NULL) ELSE NULL --END, -- Offenders JOIN(CURSOR(SELECT NVL(EJS_PERSON_PKG.GET_PERSON_SUMMARY(ip.per_person_id, ip.mn_mn_id)||' ('||RC.ROLE_DESC||')',NULL) FROM incident_people ip, incident_person_roles ipr, role_codes rc WHERE IP.INC_INCIDENT_ID = supp.inc_incident_id AND IP.SUPP_SEQ = SUPP.SUPP_SEQ AND IP.INC_PER_ID = IPR.INC_PER_ID AND IPR.ROLE_TYPE = RC.ROLE_TYPE AND RC.INC_SECTION_CODE = 'OFFENDERS' ),' | '), --CASE WHEN RC.INC_SECTION_CODE = 'OFFENDERS' THEN -- NVL(EJS_PERSON_PKG.GET_PERSON_SUMMARY(ip.per_person_id, ip.mn_mn_id)||' ('||RC.ROLE_DESC||')',NULL) ELSE NULL --END, 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 FROM officers off, officers off2, incident_cases ic, (select inc_case_id, officer_id, role_code, end_date, assigned_by, assign_date, officer_status from incident_case_officers where role_code = 'LEAD_INVESTIGATOR') ico, (select inc_case_id, officer_id, role_code, end_date, assigned_by, assign_date, officer_status from incident_case_officers where role_code = 'ASSISTING_OFFICER') ico2, 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 off2.officer_id = ico2.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 ic.inc_case_id = ico2.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(+)