SELECT DISTINCT p.property_id, get_prop_info(p.property_id), p.property_type_code, ptc.property_type_desc, p.document, p.description prop_desc, p.evidence, p.type_of_quantity, p.primary_color, p.secondary_color, p.drug_type, p.quantity, p.make, p.model, p.serial_number, p.VALUE, p.misc, p.document_number, p.bank, p.document_date, p.account_name, p.account_number, p.payable_to, p.amount, p.endorsee, p.barrel_length, p.calgag_code, p.gun_act_code, p.gun_typ_code, p.currency_code, p.rec_id, --ip.status_code p_status_code, ip.incsup_incident_id, ip.incsup_supp_seq, JOIN ( CURSOR (SELECT DISTINCT EJS_PERSON_PKG.GET_PERSON_SUMMARY(INPE.PER_PERSON_ID) FROM INCIDENT_PEOPLE INPE LEFT OUTER JOIN BOLO_PEOPLE BP ON BP.PERSON_ID = INPE.PER_PERSON_ID WHERE INPE.INC_PER_ID = IP.INC_PER_ID AND BP.Bolo_Person_Id IS NULL UNION SELECT B.BUSINESS_NAME FROM INCIDENT_BUSINESSES IB INNER JOIN BUSINESSES B ON IB.BUSNS_BUSINESS_NUMBER=B.BUSINESS_NUMBER WHERE IB.INC_INCIDENT_ID = IP.IB_INC_INCIDENT_ID AND IB.SUPP_SEQ = IP.IB_BUSNS_BUSINESS_NUMBER ),' | ') AS PROPERTY_OWNER, JOIN ( CURSOR (SELECT DISTINCT EJS_PERSON_PKG.GET_PERSON_SUMMARY(IPEOP.PER_PERSON_ID) FROM INCIDENT_PEOPLE IPEOP INNER JOIN INCIDENT_PERSON_ROLES IPR ON IPEOP.INC_PER_ID = IPR.INC_PER_ID INNER JOIN ROLE_CODES RC ON IPR.ROLE_TYPE = RC.ROLE_TYPE WHERE IPEOP.INC_INCIDENT_ID = ISUPP.INC_INCIDENT_ID AND IPEOP.SUPP_SEQ = ISUPP.SUPP_SEQ AND RC.INC_SECTION_CODE = 'OFFENDERS' ) ,' | ') AS OFFENDER_NAME, 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), 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.inc_addr_comment, e.evidence_id, e.evid_loc_code, e.disposition_code, e.status_code e_status_code, e.description e_description, e.disposition_date, e.sched_disp_date, e.disp_order_date, e.off1_officer_id, e.ejs_emp_id, elc.evid_loc_desc, coc.custody_off_to, DECODE (coc.custody_off_to, NULL, NULL, off_to.lname || ', ' || off_to.fname || ' - ' || off_to.internal_id ) cust_to_officer_name_bdg, off_to.lname off_to_lname, off_to.fname off_to_fname, off_to.internal_id off_to_badge, coc.custody_id, coc.custody_date, coc.custody_off_from, DECODE (coc.custody_off_from, NULL, NULL, off_fr.lname || ', ' || off_fr.fname || ' - ' || off_fr.internal_id ) cust_fr_officer_name_bdg, off_fr.lname off_fr_lname, off_fr.fname off_fr_fname, off_fr.internal_id off_fr_badge, coc.custody_emp_to, coc.custody_emp_from, coc.purpose coc_purpose, coc.location coc_location, coc.coc_comment, coc.received_date, coc.due_date, e.disp_order_date REL_DEST_ORDER_DATE, e.date_of_info SEIZED_DATE, JOIN (CURSOR(select 'Quantity: '||TO_CHAR(quantity)||', Denomination: '||ec.description||', Sub Total: '||TO_CHAR(sub_total) from property_cash pc, ejs_codes ec where ec.code = denomination_code and EC.CODE_TYPE = PC.DENOMINATION_CODE_TYPE and property_id = p.property_id),', ') AS CASH, e.agncy_cd_agency_code e_agency_code, (SELECT ac.agency_desc FROM agency_codes ac WHERE UPPER (TRIM (e.agncy_cd_agency_code)) = UPPER (TRIM (ac.agency_code))) AS e_agency_desc, (SELECT ejsdisp.description FROM ejs_codes ejsdisp WHERE ejsdisp.code = e.disposition_code AND ejsdisp.code_type = e.disposition_code_type) AS e_disposition_desc, (SELECT ejs.description FROM ejs_codes ejs WHERE ejs.code = p.drug_type AND ejs.code_type = p.drug_type_type) AS drug_type_desc, e.internal_tracking_num, ejcevscode.description e_status_desc, coc.evid_dest_id, (select edc.description from evidence_destination_codes edc where EDC.EVID_DEST_ID = coc.evid_dest_id), SYSDATE curr_date, NVL(e.disposition_date, sysdate) AS max_custody_date, (SELECT NVL(e.date_of_info, coc2.custody_date) FROM chain_of_custodies coc2 WHERE coc2.evidence_id = e.evidence_id AND coc2.custody_id = (select min(custody_id) from chain_of_custodies cust_min where cust_min.evidence_id = e.evidence_id)) AS min_custody_date FROM properties p INNER JOIN evidence e ON p.property_id = e.pro_property_id INNER JOIN chain_of_custodies coc ON e.evidence_id = coc.evidence_id INNER JOIN property_type_codes ptc ON p.property_type_code = ptc.property_type LEFT OUTER JOIN incident_properties ip ON p.property_id = ip.property_id -- LEFT OUTER JOIN incident_supplements isupp ON isupp.inc_incident_id = ip.incsup_incident_id AND isupp.supp_seq = ip.incsup_supp_seq LEFT OUTER JOIN incidents i ON i.incident_id = isupp.inc_incident_id LEFT OUTER JOIN evidence_location_codes elc ON e.evid_loc_code = elc.evid_loc_code AND elc.agncy_cd_agency_code = e.agncy_cd_agency_code LEFT OUTER JOIN ejs_codes ejsc ON e.status_code = ejsc.code AND e.status_code_type = ejsc.code_type LEFT OUTER JOIN ejs_codes ecprimary ON ecprimary.code = p.primary_color AND ecprimary.code_type = 'COLOR_CODES' LEFT OUTER JOIN ejs_codes ecsec ON ecsec.code = p.secondary_color AND ecsec.code_type = 'COLOR_CODES' LEFT OUTER JOIN ejs_codes ec ON ec.code = p.currency_code AND ec.code_type = p.currency_code_type LEFT OUTER JOIN ejs_codes ecdgtype ON ecdgtype.code = p.drug_type AND ecdgtype.code_type = p.drug_type_type LEFT OUTER JOIN ejs_codes ectypeqty ON p.type_of_quantity = ectypeqty.code AND p.type_of_quantity_type = ectypeqty.code_type LEFT OUTER JOIN ejs_codes ejcgtcode ON p.gun_typ_code = ejcgtcode.code AND p.gun_typ_code_type = ejcgtcode.code_type LEFT OUTER JOIN ejs_codes ejcgacode ON p.gun_act_code = ejcgacode.code AND p.gun_act_code_type = ejcgacode.code_type LEFT OUTER JOIN ejs_codes ejcevscode ON e.status_code = ejcevscode.code AND e.status_code_type = ejcevscode.code_type LEFT OUTER JOIN officers off_to ON coc.custody_off_to = TO_CHAR (off_to.officer_id) LEFT OUTER JOIN officers off_fr ON coc.custody_off_from = TO_CHAR (off_fr.officer_id) LEFT OUTER JOIN incident_addresses ia ON i.incident_id = ia.incident_id WHERE custody_id IN (SELECT MAX(custody_id) FROM chain_of_custodies where evidence_id = e.evidence_id)