SELECT distinct p.property_id ,p.property_type_code ,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 ,i.inc_report_number ,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 ,pc.quantity pc_quantity ,pc.sub_total ,pc.denomination_code ,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 ,(SELECT get_prop_info(p.property_id) FROM DUAL) AS property_information ,e.internal_tracking_num ,ejcevscode.description e_status_desc ,SYSDATE curr_date ,(SELECT MAX(custody_date) FROM chain_of_custodies coc1 WHERE coc1.evidence_id = e.evidence_id) AS max_custody_date ,(SELECT MIN(custody_date) FROM chain_of_custodies coc2 WHERE coc2.evidence_id = e.evidence_id) AS min_custody_date ,edc.DESCRIPTION AS COC_destination ,disp.DESCRIPTION AS DISPOSITION_desc , coc.LOCATION as DISPOSITION_Loc ,I.INCIDENT_ID FROM properties p INNER JOIN property_type_codes ptc ON p.property_type_code = ptc.property_type INNER JOIN evidence e ON p.property_id = e.pro_property_id LEFT OUTER JOIN incident_properties ip ON p.property_id = ip.property_id LEFT OUTER JOIN incidents i ON i.incident_id = ip.incsup_incident_id LEFT OUTER JOIN chain_of_custodies coc ON e.evidence_id = coc.evidence_id LEFT OUTER JOIN property_cash pc ON p.property_id = pc.property_id LEFT OUTER JOIN evidence_location_codes elc ON UPPER(coc.evid_loc_code) = UPPER(elc.evid_loc_code) AND UPPER(elc.agncy_cd_agency_code) = UPPER(e.agncy_cd_agency_code) LEFT OUTER JOIN ejs_codes ejsc ON UPPER(TRIM(e.status_code)) = UPPER(TRIM(ejsc.code)) AND UPPER(TRIM(e.status_code_type)) = UPPER(TRIM(ejsc.code_type)) LEFT OUTER JOIN ejs_codes ecprimary ON UPPER(TRIM(ecprimary.code)) = UPPER(TRIM(p.primary_color)) AND ecprimary.code_type = 'COLOR_CODES' LEFT OUTER JOIN ejs_codes ecsec ON UPPER(TRIM(ecsec.code)) = UPPER(TRIM(p.secondary_color)) AND ecsec.code_type = 'COLOR_CODES' LEFT OUTER JOIN ejs_codes ec ON UPPER(TRIM(ec.code)) = UPPER(TRIM(p.currency_code)) AND UPPER(TRIM(ec.code_type)) = UPPER(TRIM(p.currency_code_type)) LEFT OUTER JOIN ejs_codes ecdgtype ON UPPER(TRIM(ecdgtype.code)) = UPPER(TRIM(p.drug_type)) AND UPPER(TRIM(ecdgtype.code_type)) = UPPER(TRIM(p.drug_type_type)) LEFT OUTER JOIN ejs_codes ectypeqty ON UPPER(TRIM(p.type_of_quantity)) = UPPER(TRIM(ectypeqty.code)) AND UPPER(TRIM(p.type_of_quantity_type)) = UPPER(TRIM(ectypeqty.code_type)) LEFT OUTER JOIN ejs_codes ejcgtcode ON UPPER(TRIM(p.gun_typ_code)) = UPPER(TRIM(ejcgtcode.code)) AND UPPER(TRIM(p.gun_typ_code_type)) = UPPER(TRIM(ejcgtcode.code_type)) LEFT OUTER JOIN ejs_codes ejcgacode ON UPPER(TRIM(p.gun_act_code)) = UPPER(TRIM(ejcgacode.code)) AND UPPER(TRIM(p.gun_act_code_type)) = UPPER(TRIM(ejcgacode.code_type)) LEFT OUTER JOIN ejs_codes ejcevscode ON UPPER(TRIM(e.status_code)) = UPPER(TRIM(ejcevscode.code)) AND UPPER(TRIM(e.status_code_type)) = UPPER(TRIM(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 JOIN EVIDENCE_DESTINATION_CODES edc ON coc.EVID_DEST_ID = edc.EVID_DEST_ID AND coc.AGNCY_CD_AGENCY_CODE = edc.AGENCY_CODE LEFT JOIN ejs_codes Disp ON upper(trim(e.DISPOSITION_CODE)) = Upper(trim(disp.code)) AND disp.code_type = e.DISPOSITION_CODE_TYPE