View Definition:
SELECT
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,
i.summary,
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,
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 e.agncy_cd_agency_code = ac.agency_code ) AS e_agency_desc,
( SELECT ejsdisp.description FROM ejs_codes ejsdisp WHERE ejsdisp.code_type = e.disposition_code_type AND ejsdisp.code = e.disposition_code ) AS e_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,
e.internal_tracking_num,
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,
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,
e.CREATOR_DATE,
e.CREATOR_ID,
e.UPDATOR_DATE,
e.UPDATOR_ID,
e.DATE_OF_INFO,
e.RECOVERY_LOCATION,
EVID_TYPE.CODE,
EVID_TYPE.CODE_TYPE,
EVID_TYPE.DESCRIPTION
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 ip.evidence_id = e.evidence_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 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_type = ejsc.code_type AND e.status_code = ejsc.code
LEFT OUTER JOIN ejs_codes ecprimary ON ecprimary.code_type = 'COLOR_CODES' AND ecprimary.code = p.primary_color
LEFT OUTER JOIN ejs_codes ecsec ON ecsec.code_type = 'COLOR_CODES' AND ecsec.code = p.secondary_color
LEFT OUTER JOIN ejs_codes ec ON ec.code_type = p.currency_code_type AND ec.code = p.currency_code
LEFT OUTER JOIN ejs_codes ecdgtype ON ecdgtype.code_type = p.drug_type_type AND ecdgtype.code = p.drug_type
LEFT OUTER JOIN ejs_codes ectypeqty ON p.type_of_quantity_type = ectypeqty.code_type AND p.type_of_quantity = ectypeqty.code
LEFT OUTER JOIN ejs_codes ejcgtcode ON p.gun_typ_code_type = ejcgtcode.code_type AND p.gun_typ_code = ejcgtcode.code
LEFT OUTER JOIN ejs_codes ejcgacode ON p.gun_act_code_type = ejcgacode.code_type AND p.gun_act_code = ejcgacode.code
LEFT OUTER JOIN ejs_codes ejcevscode ON e.status_code_type = ejcevscode.code_type AND e.status_code = ejcevscode.code
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 EJS_CODES EVID_TYPE ON EVID_TYPE.CODE_TYPE = E.PROP_TYPE_CODE_TYPE AND EVID_TYPE.CODE = E.PROP_TYPE_CODE
WHERE custody_id IN (
SELECT MAX (custody_id)
FROM chain_of_custodies
GROUP BY evidence_id )
