View Definition:
SELECT
p.property_id,
p.property_type_code,
p.document,
p.description,
p.evidence,
p.type_of_quantity,
p.primary_color,
p.secondary_color,
p.drug_type,
p.drug_type_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 AS p_status_code,
INCS.INCIDENT_ID AS INCSUP_INCIDENT_ID,
IP.incsup_supp_seq AS incsup_supp_seq,
CASE WHEN INCS.INCIDENT_ID IS NOT NULL
THEN INCS.INC_REPORT_NUMBER || '|'|| INCS.INCIDENT_ID
ELSE ''
END AS inc_report_number,
CASE WHEN OC.OFFENSE_CODE IS NOT NULL
THEN OC.OFFENSE_CODE || ' - ' || OC.OFFENSE_DESC
ELSE ''
END AS INC_OFFENSES,
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.review_date,
e.off1_officer_id,
e.ejs_emp_id,
elc.evid_loc_desc,
coc.custody_off_to,
coc.custody_id,
coc.custody_date,
coc.custody_off_from,
coc.custody_emp_to,
coc.custody_emp_from,
coc.purpose,
coc.LOCATION,
coc.coc_comment,
coc.received_date,
coc.due_date,
coc.auction_amount,
pc.quantity pc_quantity,
pc.sub_total,
pc.denomination_code,
pc.PROP_CASH_COMMENT,
e.agncy_cd_agency_code,
( SELECT ac.agency_desc FROM agency_codes ac WHERE e.agncy_cd_agency_code = ac.agency_code ) AS agncy_cd_agency_desc,
( SELECT ejsdisp.description FROM ejs_codes ejsdisp WHERE ejsdisp.code_type = e.disposition_code_type AND ejsdisp.code = e.disposition_code ) AS disposition_desc,
( SELECT ejs.description FROM ejs_codes ejs WHERE ejs.code_type = p.drug_type_type AND ejs.code = p.drug_type ) AS drug_type_desc,
GET_PROP_INFO (P.PROPERTY_ID) AS property_information,
e.internal_tracking_num,
EJCEVSCODE.DESCRIPTION,
( SELECT description FROM EVIDENCE_DESTINATION_CODES edc WHERE COC.EVID_DEST_ID = edc.evid_dest_id ) AS coc_destination,
( SELECT edc.EVID_DEST_ID FROM EVIDENCE_DESTINATION_CODES edc WHERE COC.EVID_DEST_ID = edc.evid_dest_id ) AS coc_destination_id,
( SELECT edc.EVID_DEST_CODE FROM EVIDENCE_DESTINATION_CODES edc WHERE COC.EVID_DEST_ID = edc.evid_dest_id ) AS coc_destination_code,
E.CREATOR_DATE,
E.SCHED_DISP_COMMENT,
e.hold_code,
e.hold_code_type,
( SELECT ejs.description FROM ejs_codes ejs WHERE ejs.code_type = e.hold_code_type AND ejs.code = e.hold_code ) AS hold_desc,
e.hold_end_date,
( SELECT ec.ejs_comment FROM ejs_comments ec WHERE ec.ejs_comment_id = e.hold_comment_id ) AS hold_comments,
e.prop_type_code,
e.prop_type_code_type,
( SELECT ejs.description FROM ejs_codes ejs WHERE ejs.code_type = e.prop_type_code_type AND ejs.code = e.prop_type_code ) AS prop_type_desc
FROM properties p
INNER JOIN evidence e ON p.property_id = e.pro_property_id
LEFT OUTER JOIN chain_of_custodies coc ON e.evidence_id = coc.evidence_id AND e.evid_loc_code = coc.evid_loc_code
LEFT OUTER JOIN property_cash pc ON p.property_id = pc.property_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 ejcevscode ON e.status_code_type = ejcevscode.code_type AND e.status_code = ejcevscode.code
LEFT OUTER JOIN INCIDENT_PROPERTIES IP ON IP.EVIDENCE_ID = E.EVIDENCE_ID
LEFT OUTER JOIN INCIDENTS INCS ON INCS.INCIDENT_ID = IP.INCSUP_INCIDENT_ID
LEFT OUTER JOIN OFFENSES O ON O.INC_INCIDENT_ID = INCS.INCIDENT_ID
LEFT OUTER JOIN OFFENSE_CODES OC ON OC.OFFENSE_CODE = O.OFFNS_CD_OFFENSE_CODE
WHERE coc.custody_id = (
SELECT MAX(custody_id)
FROM chain_of_custodies coc2
WHERE coc2.evidence_id = coc.evidence_id
AND coc2.purpose NOT IN ( 'CUSTODY COMMENT')
)
