No, inserting a row into tEmployees with a staus that isn't already in tEmployeesStatus fails because the check function will not return 'Y' so that row would violate the check constraint. The tEmployeesStatus table has to be maintained separately. This is reasonable if it doesn't cause a big space problem - so if there are not more than about a few hundred distinct statuses, and not too many employees (ie it only makes sense when there isn't much data).

However, the whole thing is abominably badly written; probably needs NOT NULL on all the column definitions, in tEmployeesStatus the single column should be declared as the primary key, in tEmployees something should be the primary key, and that UDF should be much shorter, everything between BEGIN and END (not inclusive) could be replaced by a single return(select...) statement using CASE and IN.