SELECT DISPATCH_ID, DISPATCH_VEHICLE_ID, VEH_VEHICLE_ID, VIN, VEHICLE_YEAR, NVL( VMA.VEHICLE_MAKE_CODE,DEV.MAKE), NVL (VMA.VEHICLE_MAKE_DESC, DEV.MAKE), NVL(VMO.VEHICLE_MODEL_CODE,DEV.MODEL), NVL (VMO.VEHICLE_MODEL_DESC, DEV.MODEL), VTC.TYPE_CODE, NVL(VTC.TYPE_DESC,VEHICLE_TYPE), VSC.VEHICLE_STYLE_CODE, NVL(VSC.VEHICLE_STYLE_DESC,DEV.STYLE), LIC_NUMBER, LIC_MONTH, LIC_YEAR, LIC_TYPE, NVL(LC.DESCRIPTION,LIC_TYPE), EC.CODE, NVL(EC.DESCRIPTION,DEV.LIC_STATE), TC.CODE, NVL(TC.DESCRIPTION,DEV.TOP_COLOR), BC.CODE, NVL(BC.DESCRIPTION,DEV.BOTTOM_COLOR), DEV.COND, NVL(CC.DESCRIPTION,DEV.COND), DEV.DAMAGE, NVL(DC.DESCRIPTION,DEV.DAMAGE), DEV.MISC_ID, DEV.VEHICLE_VALUE, DEV.ODOMETER, DEV.DATE_OF_INFO, DEV.COMMENTS, null FROM DISPATCH_EVENT_VEHICLES DEV LEFT OUTER JOIN VEHICLE_MAKES VMA ON DEV.MAKE = VMA.VEHICLE_MAKE_CODE LEFT OUTER JOIN VEHICLE_MODELS VMO ON DEV.MAKE = VMO.VMA_VEHICLE_MAKE_CODE AND DEV.MODEL = VMO.VEHICLE_MODEL_CODE LEFT OUTER JOIN VEHICLE_TYPE_CODES VTC ON DEV.VEHICLE_TYPE = VTC.TYPE_CODE LEFT OUTER JOIN VEHICLE_STYLES VSC ON DEV.STYLE = VSC.VEHICLE_STYLE_CODE AND DEV.VEHICLE_TYPE = VSC.VTC_TYPE_CODE LEFT OUTER JOIN EJS_CODES EC ON EC.CODE_TYPE = 'STATE_CODES' AND DEV.LIC_STATE = EC.CODE LEFT OUTER JOIN EJS_CODES TC ON TC.CODE_TYPE = 'COLOR_CODES' AND TC.CODE = DEV.TOP_COLOR LEFT OUTER JOIN EJS_CODES BC ON BC.CODE_TYPE = 'COLOR_CODES' AND BC.CODE = DEV.BOTTOM_COLOR LEFT OUTER JOIN EJS_CODES DC ON DC.CODE_TYPE = 'DAMAGE_CODES' AND DC.CODE = DEV.DAMAGE LEFT OUTER JOIN EJS_CODES CC ON CC.CODE_TYPE = 'CONDITION_CODES' AND CC.CODE = DEV.COND LEFT OUTER JOIN EJS_CODES LC ON LC.CODE_TYPE = 'LIC_TYPE_CODES' AND LC.CODE = DEV.LIC_TYPE WHERE VEH_VEHICLE_ID IS NULL UNION SELECT DISPATCH_ID, DISPATCH_VEHICLE_ID, VEH_VEHICLE_ID, V.VIN, V.YEAR, NVL( VMA.VEHICLE_MAKE_CODE,DEV.MAKE), NVL (VMA.VEHICLE_MAKE_DESC, DEV.MAKE), NVL(VMO.VEHICLE_MODEL_CODE,DEV.MODEL), NVL (VMO.VEHICLE_MODEL_DESC, DEV.MODEL), VTC.TYPE_CODE, VTC.TYPE_DESC, VSC.VEHICLE_STYLE_CODE, VSC.VEHICLE_STYLE_DESC, VD.LIC_NUMBER, VD.LIC_MONTH, VD.LIC_YEAR, LC.CODE, LC.DESCRIPTION, EC.CODE, EC.DESCRIPTION, TC.CODE, TC.DESCRIPTION, BC.CODE, BC.DESCRIPTION, VD.COND_CODE, CC.DESCRIPTION, DC.CODE, DC.DESCRIPTION, VD.MISC_ID, VD.VALUE, VD.ODOMETER_READING, VD.DATE_OF_INFO, DEV.COMMENTS, V.INDX_SEC_LEVEL_CODE FROM DISPATCH_EVENT_VEHICLES DEV INNER JOIN VEHICLES V ON DEV.VEH_VEHICLE_ID = V.VEHICLE_ID LEFT OUTER JOIN VEHICLE_DESCRIPTIONS VD ON V.VEHICLE_ID = VD.VEHICLE_ID LEFT OUTER JOIN VEHICLE_MAKES VMA ON V.VMA_VEHICLE_MAKE_CODE = VMA.VEHICLE_MAKE_CODE LEFT OUTER JOIN VEHICLE_MODELS VMO ON V.VMA_VEHICLE_MAKE_CODE = VMO.VMA_VEHICLE_MAKE_CODE AND V.VMO_VEHICLE_MODEL_CODE = VMO.VEHICLE_MODEL_CODE LEFT OUTER JOIN VEHICLE_TYPE_CODES VTC ON V.VTC_TYPE_CODE = VTC.TYPE_CODE LEFT OUTER JOIN VEHICLE_STYLES VSC ON V.VST_VEHICLE_STYLE_CODE = VSC.VEHICLE_STYLE_CODE AND V.VTC_TYPE_CODE = VSC.VTC_TYPE_CODE LEFT OUTER JOIN EJS_CODES EC ON EC.CODE_TYPE = VD.LIC_STATE_CODE_TYPE AND VD.LIC_STATE_CODE = EC.CODE LEFT OUTER JOIN EJS_CODES TC ON TC.CODE_TYPE = VD.TOP_COLOR_TYPE AND TC.CODE = VD.TOP_COLOR LEFT OUTER JOIN EJS_CODES BC ON BC.CODE_TYPE = VD.BOTTOM_COLOR_TYPE AND BC.CODE = VD.BOTTOM_COLOR LEFT OUTER JOIN EJS_CODES DC ON DC.CODE_TYPE = VD.DAMAGE_CODE_TYPE AND DC.CODE = VD.DAMAGE_CODE LEFT OUTER JOIN EJS_CODES CC ON CC.CODE_TYPE = VD.COND_CODE_TYPE AND CC.CODE = VD.COND_CODE LEFT OUTER JOIN EJS_CODES LC ON LC.CODE_TYPE = VD.LIC_TYPE_CODE_TYPE AND LC.CODE = VD.LIC_TYPE_CODE WHERE (VD.DATE_OF_INFO IS NULL OR VD.DATE_OF_INFO = ( SELECT MAX(VD2.DATE_OF_INFO) FROM VEHICLE_DESCRIPTIONS VD2 WHERE VD2.VEHICLE_ID = VD.VEHICLE_ID ))