Query Plan - Estimated Row Count Very High
Here is an alternative using CASE statement, Please test the code before run

INSERT INTO #TmpClientOnboardingTable
   SELECT 
    f.FundCode
   ,f.FundName
   ,tr.Period
   ,td.TaskName
   ,td.TaskDetailID
   --,tr.LatestComment
   ,[Tasks].[OverallTaskStatus](tr.TaskDetailID,tr.TagID,f.FundID,tr.Period) as 'OverallStatus'
   ,[Tasks].[LatestComment](tr.TaskDetailID,f.FundID,tr.Period) as 'LatestComment'

   --,[TaskDetailID]
   --,[TagID]
   --,[StatusFlagID]
   --,[ApprovalLevelID]
   --,[ApprovedByID]
   --,[ApprovedDateTime]
   --,[LatestComment]
   --,[CommentDateTime]
   --,[CommentPostedByID]
   FROM [Tasks].[TaskRecord] tr
   INNER JOIN dbo.Fund f
   ON f.FundID = tr.FundID
   INNER JOIN [Tasks].[Tag] t
   ON t.TagID = tr.TagID
   INNER JOIN [Tasks].[TaskDetail] td
   ON td.TaskDetailID = tr.TaskDetailID
   INNER JOIN [dbo].[StatusFlag] s
   ON s.StatusFlagID = tr.StatusFlagID
   LEFT JOIN [dbo].[UserDetails] u
   ON u.UserDetailID = tr.ApprovedByID
   WHERE tr.Period = @Period 
   AND t.TagID = 4; -- Client Onboarding View -- ('Cash Reconciliation', 'Non Trade Related')
   --AND tr.ApprovalLevelID = 1 -- analyst
   --ORDER BY f.FundCode  

  --optional if its unique
  --CREATE CLUSTERED INDEX CL_idx ON  #TmpClientOnboardingTable (Fundcode) 

    SELECT DISTINCT
  c.FundCode
 ,c.FundName
 ,c.Period
 ,MAX(CASE WHEN TaskName= 'Client Risk Assessment' THEN TaskDetailID ELSE NULL END) AS 'ClientRiskAssessmentParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Client Risk Assessment' THEN OverallStatus ELSE NULL END,'RED')) as 'ClientRiskAssessment'     
 ,MAX(CASE WHEN TaskName= 'AML/KYC' THEN TaskDetailID ELSE NULL END) AS 'AMLKYCParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'AML/KYC' THEN OverallStatus ELSE NULL END,'RED')) as 'AMLKYC'     
 ,MAX(CASE WHEN TaskName= 'Broker' THEN TaskDetailID ELSE NULL END) AS 'BrokerParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Broker' THEN OverallStatus ELSE NULL END,'RED')) as 'Broker'     
 ,MAX(CASE WHEN TaskName= 'Checklist' THEN TaskDetailID ELSE NULL END) AS 'ChecklistParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Checklist' THEN OverallStatus ELSE NULL END,'RED')) as 'Checklist'     
 ,MAX(CASE WHEN TaskName= 'Register of Assets' THEN TaskDetailID ELSE NULL END) AS 'RegisterOfAssetsParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Register of Assets' THEN OverallStatus ELSE NULL END,'RED')) as 'RegisterOfAssets'          
 ,MAX(CASE WHEN TaskName= 'Expense Budget' THEN TaskDetailID ELSE NULL END) AS 'ExpenseBudgetParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Expense Budget' THEN OverallStatus ELSE NULL END,'RED')) as 'ExpenseBudget'     
 ,MAX(CASE WHEN TaskName= 'Decisions' THEN TaskDetailID ELSE NULL END)  AS 'DecisionsParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Decisions' THEN OverallStatus ELSE NULL END,'RED')) as 'Decisions'
 ,MAX(CASE WHEN TaskName= 'Onboarding Review' THEN TaskDetailID ELSE NULL END) AS 'OnboardingReviewParentID'
 ,MAX(COALESCE(CASE WHEN TaskName= 'Onboarding Review' THEN OverallStatus ELSE NULL END,'RED')) as 'OnboardingReview'     
FROM #TmpClientOnboardingTable c
GROUP BY c.FundCode
        ,c.FundName
        ,c.Period


Also, the estimated number is high because you are joining to the same table for 9 times which bloats the number of rows.