... new DB that has to be in different collation (Y Collation) that SQL Server Instance default collation (X Collation).
Why does the new database need to be in a different collation?
Temp Tables by default will be created with X Collation so any joins on text data types will fail unless I create temp table with Y collations on cols.
Correct. Though you do not need to use COLLATE DATABASE_DEFAULT, and in some cases that can be entirely incorrect / bad. The requirement is that either A) the collation is the same for string columns being joined, or used in concatenation / UNION, etc, or B) you specify the COLLATE clause in the predicate or expression to override one or more of the columns.
Meaning, if you know that your column has a collation of Z and your database has a default collation of Y, then create the temp table specifying COLLATE Z for that column. Keep in mind that table variables use the database's default collation, not the instance's collation. And, contained databases are yet another set of rules.
What comes to SQL Server text functions Replace, Substring etc. They will be always returned in X collation?
Functions usually return a string in the database's default collation, unless you pass in a column or an expression using the COLLATE clause.
do you see any potential data loss or maybe any different problems?
It depends on what datatype(s) you are using and what collations you are using. NVARCHAR / NCHAR / NTEXT / literals prefixed with an uppercase "N" are Unicode-only (UTF-16) and so there is no potential data loss if switching collations on those. VARCHAR / CHAR / TEXT / literals not prefixed with an uppercase "N" are 8-bit encodings, the particular encoding being determined by the code page association with the collation being used. Here there is potential data loss if switching collations to one that uses a different code page. But even that is not a guarantee of data loss since data loss only occurs when there are characters in the data that are not available in the new code page.
For a more thorough list of what collations do at the various levels and what to look out for when changing them, please see the following post of mine: