[FEA] Investigate if rapids-cmake cpm overrides can work with CPM_SOURCE_CACHE

[robertmaynard]

Is your feature request related to a problem? Please describe.
The CPM_SOURCE_CACHE variable allows for users/envs to describe where to checkout out source code. In theory this should still work with rapids-cmake overrides as it only controls where a git clone occurs.

But currently rapids_cpm_package_override fixes the source tree location due to directly calling FetchContent_Declare instead of the higher level CPM function.

Describe the solution you'd like
We should investigate using CPM to register the override information that should ensure we respect CPM_SOURCE_CACHE.