View Definition:
select c.crash_id,
c.crash_report_number,
c.vehicle_id,
c.agency_code,
(select ac.agency_desc from agency_codes ac where ac.agency_code=c.agency_code) as agency_desc,
c.officer_id,
get_off_emp_name(c.officer_id,'O') as officer_desc,
c.employee_id,
get_off_emp_name(c.employee_id,'E') as employee_desc,
c.crash_date,
c.hour_group_code,
(select ec.description from ejs_codes ec where ec.code_type=c.hour_group_code_type and ec.code=c.hour_group_code) as hour_group_desc,
c.county_code,
(select ec.description from ejs_codes ec where ec.code_type=c.county_code_type and ec.code=c.county_code) as county_code_desc,
c.status_code,
(select ec.description from ejs_codes ec where ec.code_type=c.status_code_type and ec.code=c.status_code) as status_code_desc,
c.crash_type_code,
(select ec.description from ejs_codes ec where ec.code_type=c.crash_type_code_type and ec.code=c.crash_type_code) as crash_type_desc,
c.crash_cause_code,
(select ec.description from ejs_codes ec where ec.code_type=c.crash_cause_code_type and ec.code=c.crash_cause_code) as crash_cause_desc,
c.address_id,
ejs_format_address(c.address_id) as address_desc,
c.location_comment,
c.summary,
c.is_preventable,
c.is_pursuit,
c.is_traffic_stop,
dv.unit_number,
nvl(ctow.towing_co,tcc.towing_co_name),
nvl(ctow.state_cd_state_code,tcc.towing_co_state),
nvl(ctow.towing_co_street,tcc.towing_co_street),
nvl(ctow.towing_co_zip,tcc.towing_co_zip),
nvl(ctow.towing_city,tcc.towing_co_city),
nvl(ctow.phone_prefix,tcc.phone_prefix),
nvl(ctow.phone_suffix,tcc.phone_suffix),
nvl(ctow.phone_area,tcc.phone_area),
ctow.driver,
ctow.tow_date,
ctow.reference_number,
ctow.tow_cost,
ctow.creator_id,
ctow.creator_date,
ctow.updator_id,
ctow.updator_date
from dv_crashes c
inner join department_vehicles dv on c.vehicle_id = dv.vehicle_id
inner join dv_crash_tows ctow on c.crash_id = ctow.crash_id
left outer join tow_company_codes tcc on ctow.towing_co_id = tcc.towing_co_id
