( SELECT DV.VEHICLE_ID, DV.UNIT_NUMBER, DV.AGENCY_CODE, (SELECT DESCRIPTION FROM ITEM_SUB_TYPE_CODES IST WHERE IST.TYPE_ID = DV.TYPE_ID), (SELECT DESCRIPTION FROM ITEM_MAKE_CODES IMC WHERE IMC.MAKE_ID = DV.MAKE_ID), (SELECT DESCRIPTION FROM ITEM_MODEL_CODES IMC WHERE IMC.MAKE_ID = DV.MAKE_ID AND IMC.MODEL_ID = DV.MODEL_ID), (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE=DV.VEH_CATEGORY_CODE_TYPE AND EC.CODE=DV.VEH_CATEGORY_CODE), DV.VEH_CATEGORY_CODE, DV.VEH_CATEGORY_CODE_TYPE, (SELECT EC.DESCRIPTION FROM EJS_CODES EC WHERE EC.CODE_TYPE = DV.STATUS_CODE_TYPE AND EC.CODE = DV.STATUS_CODE), EJS_FLEET_MGMT_SEARCH_PK.GET_MAX_VEHICLE_ODOMETER(DV.VEHICLE_ID), (SELECT MAX(MILEAGE) FROM DV_SERVICE_RECORDS SR2 WHERE SR2.VEHICLE_ID = DV.VEHICLE_ID AND SR2.CRASH_ID IS NULL AND SR2.RESET_INTERVAL_FLAG = 'Y'), (SELECT MAX(START_DATE) FROM DV_SERVICE_RECORDS SR3 WHERE SR3.VEHICLE_ID = DV.VEHICLE_ID AND SR3.CRASH_ID IS NULL AND SR3.RESET_INTERVAL_FLAG = 'Y'), EJS_FLEET_MGMT_SEARCH_PK.GET_NEXT_SERVICE_MILES(DV.VEHICLE_ID, DV.AGENCY_CODE), EJS_FLEET_MGMT_SEARCH_PK.GET_NEXT_SERVICE_DATE(DV.VEHICLE_ID, DV.AGENCY_CODE), DV.ASSIGNMENT_CODE FROM DEPARTMENT_VEHICLES DV WHERE ( -- AT LEAST 1 SERVICE RECORD WITH RESET FLAG (SELECT COUNT(*) FROM DV_SERVICE_RECORDS WHERE VEHICLE_ID = DV.VEHICLE_ID AND RESET_INTERVAL_FLAG = 'Y') > 0 -- NEXT SERVICE DATE IS LESS THAN CURRENT DATE OR EJS_FLEET_MGMT_SEARCH_PK.GET_NEXT_SERVICE_DATE(DV.VEHICLE_ID, DV.AGENCY_CODE) < SYSDATE -- NEXT SERVICE MILES IS LESS THAN CURRENT MILEAGE OR EJS_FLEET_MGMT_SEARCH_PK.GET_NEXT_SERVICE_MILES(DV.VEHICLE_ID, DV.AGENCY_CODE) < EJS_FLEET_MGMT_SEARCH_PK.GET_MAX_VEHICLE_ODOMETER(DV.VEHICLE_ID) ) AND (DV.STATUS_CODE IS NULL OR DV.STATUS_CODE != 'SOLD') )