[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.