SELECT
  CS.ClientID,
  CS.GroupName,
  CS.ClientStatus,
  CP.ClientProductID,
  CP.ProductID,
  PR.ProductName,
  CP.JoinDate,
  CP.Status AS ClientProductStatus,
  PC.MustHaveParent,
  (SELECT M.Status FROM taClientProducts AS M WHERE (M.ClientProductID = CP.ParentClientProductID)) AS ParentStatus,
  CP.TerminationDate,
  CP.TerminationReason,
  CP.TerminationNotes,
  fnClientProductRetainer(CP.ClientProductID, @aEffectiveDate) AS Retainer
FROM
  taClientProducts AS CP
    LEFT JOIN taClients AS CS ON (CS.ClientID = CP.ClientID)
    LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
    LEFT JOIN taProductCategory AS PC ON (PC.ProductCategory = PR.ProductCategory)
WHERE
  (CP.ClientID = @nClientID)
