I would recommend starting with these two parts of the query:
AND cf.TableName = REPLACE(REPLACE(fm.DestTable, 'Holding_', ''), 'HoldingTable_', '')
LTRIM(RTRIM(ISNULL(CAST(im.NewValue AS VARCHAR(MAX)), ''))) <> ''
The first one doesn't make sense - you are replacing the values in fm.DestTable that contain either Holding_ or HoldingTable_ with an empty string and comparing that to cf.TableName? Are there really values in cf.TableName that are empty that should be matched to your 'Holding' tables?
If that is the case - wouldn't it be better to modify the TableName in the #crf table where the entries are blank to either Holding_ or HoldingTable_ - and if you need to match on both entries in fm.DestTable for a single row in cf.TableName you can always add those additional rows to the temp table.
The second one definitely doesn't need RTRIM and LTRIM...it looks like here you are trying to include any row that is not null and is not an empty string and I don't believe you need to convert to VARCHAR(MAX) - unless that is defined as a TEXT column. Either way - move the convert into the generation of the temp table #idopfield and then you can just use im.NewValue > '' which will exclude all NULL values and empty strings (note: a string of spaces will be evaluated as an empty string - but if that is an issue, move that to the build of the temp table also).
If possible - try to convert the NOT IN to an IN...if that isn't possible then try converting to a NOT EXIST. You should be able to eliminate the reference to #crf in the NOT IN portion if you change to a NOT EXISTS.
You also have that portion filtered by the client ID ( @clientid ) but are not filtering the outer query. Thus, the OUTER APPLY is returning all rows for all clients where the passed in client does not have that field...this doesn't appear to be correct.