Two different people can have the same first and last names. So you need to give these different UserNames, Using email as the unique identifier is often used.
DROP...

The where in query 3 is applied to this query:
select a.id_a, b.id_b, b.fname
from tmp_1 a
left join tmp_2 b on a.id_a = b.id_b
which returns these...

You need to create a table with the same format columns as the stored procedure returns, then just insert into it from the EXEC:
INSERT INTO myTable(col1 ,col2,...)

SQL Server seems to treat these special characters oddly.
The reverse conversion doesn't seem go back to the original character
select ascii('😀'), nchar(ascii('😀'))
DECLARE @a nchar(1)=N'😀'
select N'😀' [N'😀'],
       '😀'...

Is column mailingtype part of the primary key for table mailingpreference?

or instead of MAX you could use TOP(1) with an ORDER BY:
SELECT TOP(1) *
FROM dbo.Widgets
ORDER BY ColumnThatIncreasesWithInsert DESC

Ideally the ColumnThatIncreasesWithInsert will be indexed...

;
With START_POINT as
(
Select
BeneficiaryID, PCP,
(CAST( EligYear as VARCHAR) + RIGHT( ('0' + CAST(EligMonth as VARCHAR)),2) ) as MON
FROM #t
)

As PROD.cost is a float, I think all values in the expression will be converted to float to do the calculation.

Worth reading this as you are using SQL Server 2016: https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-2017

Performance...