SELECT v.vehicle_id AS vid, UPPER(i.db_schema) ||'_'|| v.vehicle_id AS ID, UPPER(i.db_schema) AS county, v.vin AS vin, translate(lower(v.vin),'abcdefghijklmnopqrstuvwxyz1234567890 #-.,/=:*','abcdefghijklmnopqrstuvwxyz1234567890') AS vin_plain, VM.VEHICLE_MAKE_DESC AS vmake, v.vma_vehicle_make_code AS vmakecode, VMO.VEHICLE_MODEL_DESC AS vmodel, v.vmo_vehicle_model_code AS vmodelcode, (SELECT vs.vehicle_style_desc FROM vehicle_styles vs WHERE UPPER (vs.vehicle_style_code) = UPPER (v.vst_vehicle_style_code) AND UPPER (vs.vtc_type_code) = UPPER (v.vtc_type_code)) AS vstyle, v.vst_vehicle_style_code AS vstylecode, v.YEAR AS vyear, (SELECT vt.type_desc FROM vehicle_type_codes vt WHERE UPPER (vt.type_code) = UPPER (v.vtc_type_code)) AS vtype, v.vtc_type_code AS vtypecode, v.indx_sec_level_id AS vsecurity, v.indx_sec_level_code AS vseccode, v.indx_type_code AS vindxtype, v.mccs AS mccs, vd.lic_state_code AS licstate, (SELECT ec.description FROM ejs_codes ec WHERE UPPER (ec.code_type) = UPPER (vd.top_color_type) AND UPPER (ec.code) = UPPER (vd.top_color)) AS vtopcolor, (SELECT ec.description FROM ejs_codes ec WHERE UPPER (ec.code_type) = UPPER (vd.bottom_color_type) AND UPPER (ec.code) = UPPER (vd.bottom_color)) AS vbottomcolor, (SELECT ec.description FROM ejs_codes ec WHERE UPPER (ec.code_type) = UPPER (vd.lic_state_code_type) AND UPPER (ec.code) = UPPER (vd.lic_state_code)) AS vlicstate, vd.bottom_color AS bcolor, vd.top_color AS tcolor, UPPER (vd.lic_number) licnum, translate(lower(vd.lic_number),'abcdefghijklmnopqrstuvwxyz1234567890 #-.,/=:*','abcdefghijklmnopqrstuvwxyz1234567890') licnum_plain, vd.damage_code AS damage, vd.cond_code AS condition, pv.role_code AS personrole, mn.fname AS firstname, mn.lname AS lastname, mn.mname AS middlename, TO_CHAR (mn.dob, 'mm/dd/yyyy') AS dateofbirth, FLOOR (MONTHS_BETWEEN (SYSDATE, mn.dob) / 12) AS age, vd.misc_id AS miscnumber, bv.role_code AS businessrole, b.BUSINESS_ID AS businessnumber, b.business_name AS businessname, TCC.TOWING_CO_NAME as towingcompanyname, t.tow_date AS towingdate, IV.STATUS_CODE, IV.ROLE_CODE, to_char(vd.date_of_info,'MM/dd/yyyy') as DESCRIPTION_DATE_OF_INFO, VD.LIC_MONTH, vd.lic_year, VD.LIC_TYPE_CODE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = VD.LIC_TYPE_CODE_TYPE AND EC.CODE = VD.LIC_TYPE_CODE) AS LIC_TYPE_DESC, v.creator_id AS createdby, v.creator_date AS creation_date, to_char(from_tz(cast(v.creator_date as timestamp), (select ec.code from ejs_codes ec where ec.code_type = 'DATABASE_TIMEZONE' and rownum = 1)) at time zone 'UTC','YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"') AS solr_creation_date, v.updator_date AS modification_date, v.updator_id AS modifiedby, mi.id_number AS person_lic_number FROM install i, vehicles v LEFT OUTER JOIN vehicle_descriptions vd ON v.vehicle_id = vd.vehicle_id LEFT OUTER JOIN people_vehicles pv ON v.vehicle_id = pv.vehicle_id LEFT OUTER JOIN master_names mn ON pv.person_id = mn.per_person_id LEFT OUTER JOIN misc_ids mi ON pv.person_id = mi.per_person_id LEFT OUTER JOIN business_vehicles bv ON v.vehicle_id = bv.vehicle_id LEFT OUTER JOIN businesses b ON bv.business_number = b.business_number LEFT OUTER JOIN tows t ON v.vehicle_id = t.veh_vehicle_id LEFT OUTER JOIN tow_company_codes tcc on TCC.TOWING_CO_ID = T.TOWING_CO_ID LEFT OUTER JOIN VEHICLE_MAKES VM ON V.VMA_VEHICLE_MAKE_CODE = VM.VEHICLE_MAKE_CODE LEFT OUTER JOIN VEHICLE_MODELS VMO ON VM.VEHICLE_MAKE_CODE = VMO.VMA_VEHICLE_MAKE_CODE AND V.VMO_VEHICLE_MODEL_CODE = VMO.VEHICLE_MODEL_CODE LEFT OUTER JOIN INCIDENT_VEHICLES IV ON IV.VEHICLE_ID = V.VEHICLE_ID