| View slot9.WORLD.TSTA.TDEX_RMS_VEHICLE_VW |
View Definition:
select distinct
'VE' HEADER,
null ACTION,
null LABEL,
--prop.updator_date UPDATE_DATE, --19518
incveh.updator_date UPDATE_DATE, --19518
--to_char(prop.updator_date, 'HH24MI') UPDATE_TIME, --19518
to_char(incveh.updator_date, 'HH24MI') UPDATE_TIME, --19518
ac.ori_number AGENCY,
inc.inc_report_number INCIDENT_NUMBER,
veh.vehicle_id VEHICLE_UNIQUE_ID,
--incprop.status_code PROPERTY_LOSS_TYPE, --19518
(select status_code
from properties pr, incident_properties ip
where ip.property_id = pr.property_id
and ip.incsup_incident_id = incsupp.inc_incident_id
and ip.incsup_supp_seq = incsupp.supp_seq
and pr.property_type_code in (select property_type from property_type_codes where property_category = 'VEH')
and rownum = 1) PROPERTY_LOSS_TYPE,
--prop.property_type_code PROPERTY_DESCRIPTION_TYPE, --19518
(select property_type_code from properties pr, incident_properties ip
where ip.property_id = pr.property_id
and ip.incsup_incident_id = incsupp.inc_incident_id
and ip.incsup_supp_seq = incsupp.supp_seq
and pr.property_type_code in (select property_type from property_type_codes where property_category = 'VEH')
and rownum = 1) PROPERTY_DESCRIPTION_TYPE,
--prop.description VEHICLE_DESCRIPTION, --19518
(select description from properties pr, incident_properties ip
where ip.property_id = pr.property_id
and ip.incsup_incident_id = incsupp.inc_incident_id
and ip.incsup_supp_seq = incsupp.supp_seq
and pr.property_type_code in (select property_type from property_type_codes where property_category = 'VEH')
and rownum = 1) VEHICLE_DESCRIPTION,
veh.vma_vehicle_make_code VEHICLE_MAKE,
veh.vmo_vehicle_model_code VEHICLE_MODEL,
veh.vst_vehicle_style_code VEHICLE_BODY_STYLE,
veh.vin VIN_NUMBER,
veh.year VEHICLE_MODEL_YEAR,
vehdesc.top_color VEHICLE_PRIMARY_COLOR,
vehdesc.bottom_color VEHICLE_SECOND_COLOR,
vehdesc.lic_number LICENSE_PLATE_NUMBER,
vehdesc.lic_state_code LICENSE_PLATE_STATE,
vehdesc.value VALUE_OF_VEHICLE,
--incprop.date_recovered RECOVERY_DATE, --19518
(select date_recovered
from properties pr, incident_properties ip
where ip.property_id = pr.property_id
and ip.incsup_incident_id = incsupp.inc_incident_id
and ip.incsup_supp_seq = incsupp.supp_seq
and pr.property_type_code in (select property_type from property_type_codes where property_category = 'VEH')
and rownum = 1) RECOVERY_DATE,
null RECOVERY_LOCATION, --Not in EJS.
--evid.evidence_id EVIDENCE_NUMBER, --19518
(select ev.evidence_id from evidence ev, properties pr, incident_properties ip
where ev.pro_property_id = pr.property_id
and ip.property_id = pr.property_id
and ip.incsup_incident_id = incsupp.inc_incident_id
and ip.incsup_supp_seq = incsupp.supp_seq
and pr.property_type_code in (select property_type from property_type_codes where property_category = 'VEH')
and rownum =1) EVIDENCE_NUMBER,
inc.incident_id INCIDENT_SYSTEM_NUMBER
from
--properties prop, --19518
--incident_properties incprop, --19518
incident_supplements incsupp,
incident_vehicles incveh,
vehicles veh,
vehicle_descriptions vehdesc,
incidents inc,
agency_codes ac
--evidence evid --19518
--where prop.property_type_code in (select property_type from property_type_codes where property_category = 'VEH') --19518
--and incprop.property_id = prop.property_id --19518
--and incsupp.inc_incident_id = incprop.incsup_incident_id --19518
--and incsupp.supp_seq = incprop.incsup_supp_seq --19518
where inc.incident_id = incsupp.inc_incident_id
and incveh.incident_id = incsupp.inc_incident_id
and incveh.supp_seq = incsupp.supp_seq
and veh.vehicle_id = incveh.vehicle_id
and vehdesc.vehicle_id = veh.vehicle_id
and ac.agency_code = inc.agncy_cd_agency_code
--and evid.pro_property_id (+) = prop.property_id --19518
and incsupp.isc_status_code = 'A'
Possibly Referenced Tables/Views: