SELECT
  CS.ClientID,
  CS.GroupName,
  CS.ClientStatus,
  CB.ClientBranchID,
  CB.BranchName AS ClientBranchName,
  CB.BranchStatus AS ClientBranchStatus,
  CP.ClientProductID,
  CP.ProductID,
  PR.ProductName,
  CP.Status AS ClientProductStatus,
  BP.ClientBranchProductID,
  BP.JoinDate,
  BP.Status AS BranchProductStatus,
  BP.TerminationDate,
  BP.TerminationReason,
  BP.TerminationNotes,
  fnClientBranchProductRetainer(BP.ClientBranchProductID, @aEffectiveDate) * 1.0 AS Retainer
FROM
  taClientBranchProducts AS BP
    LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
    LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
    LEFT JOIN taClients AS CS ON (CS.ClientID = CB.ClientID)
WHERE
  (CP.ClientProductID = @nClientProductID)
