SELECT v.vehicle_id AS vid, UPPER(i.db_schema) ||'_'|| v.vehicle_id || '_' || vd.rec_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, vs.vehicle_style_desc AS vstyle, v.vst_vehicle_style_code AS vstylecode, v.YEAR AS vyear, vt.type_desc 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, TOP_COLOR.DESCRIPTION AS vtopcolor, BOT_COLOR.DESCRIPTION AS vbottomcolor, LIC_STATE.DESCRIPTION AS vlicstate, vd.bottom_color AS bcolor, vd.top_color AS tcolor, UPPER (vd.lic_number) AS licnum, translate(lower(vd.lic_number),'abcdefghijklmnopqrstuvwxyz1234567890 #-.,/=:*','abcdefghijklmnopqrstuvwxyz1234567890') AS licnum_plain, vd.misc_id AS miscnumber, to_char(vd.date_of_info,'MM/dd/yyyy') as DESCRIPTION_DATE_OF_INFO, VD.LIC_MONTH, vd.lic_year, VD.LIC_TYPE_CODE, LIC_TYPE.DESCRIPTION AS LIC_TYPE_DESC, v.creator_id AS createdby, v.creator_date AS creation_date, CONV_SOLR_CREATOR_DATE(v.creator_date, (SELECT ec.code FROM ejs_codes ec WHERE ec.code_type = 'DATABASE_TIMEZONE' AND ROWNUM = 1)) AS solr_creation_date, v.updator_date AS modification_date, v.updator_id AS modifiedby, to_number(to_char(vd.DATE_OF_INFO, 'yyyymmddhh24miss')) AS vdesc_date_int FROM install i, vehicles v LEFT OUTER JOIN vehicle_descriptions vd ON v.vehicle_id = vd.vehicle_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 VEHICLE_STYLES VS ON VS.VEHICLE_STYLE_CODE = V.VST_VEHICLE_STYLE_CODE AND VS.VTC_TYPE_CODE = V.VTC_TYPE_CODE LEFT OUTER JOIN VEHICLE_TYPE_CODES VT ON VT.TYPE_CODE = V.VTC_TYPE_CODE LEFT OUTER JOIN EJS_CODES TOP_COLOR ON TOP_COLOR.CODE_TYPE = VD.TOP_COLOR_TYPE AND TOP_COLOR.CODE = VD.TOP_COLOR LEFT OUTER JOIN EJS_CODES BOT_COLOR ON BOT_COLOR.CODE_TYPE = VD.BOTTOM_COLOR_TYPE AND BOT_COLOR.CODE = VD.BOTTOM_COLOR LEFT OUTER JOIN EJS_CODES LIC_STATE ON LIC_STATE.CODE_TYPE = VD.LIC_STATE_CODE_TYPE AND LIC_STATE.CODE = VD.LIC_STATE_CODE LEFT OUTER JOIN EJS_CODES LIC_TYPE ON LIC_TYPE.CODE_TYPE = VD.LIC_TYPE_CODE_TYPE AND LIC_TYPE.CODE = VD.LIC_TYPE_CODE