SQL Job Fails - Why?
As per the error message, more than a single row is being returned in a context where that is not permitted.

Looking at your query, the following line could easily be the culprit:

SET @FQN = (SELECT fqn FROM fulldatabasemapping WHERE rciaem = @name);


You can eliminate that as the cause of the problem by modifying that line to:

SET @FQN = (SELECT TOP(1) fqn FROM fulldatabasemapping WHERE rciaem = @name)