BVP for Carathéodory inclusions in Hilbert spaces: sharp existence conditions and applications