SELECT
  CS.ClientID,
  CS.FinNumber,
  CS.GroupName,
  BP.ClientBranchProductID,
  BP.TerminationDate,
  PR.ProductName,
  CB.BranchName,
  fnClientBranchProductRetainer(BP.ClientBranchProductID, BP.TerminationDate) * 1.0 AS Retainer,
  (SELECT GROUP_CONCAT(DISTINCT ClientProductFeeID SEPARATOR ',') FROM taClientProductFees
   WHERE (StartDate > BP.TerminationDate) AND (ClientBranchProductID = BP.ClientBranchProductID)) AS FutureDatedFees
FROM
  taClientBranchProducts AS BP
    LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
    LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
    LEFT JOIN taProducts AS PR ON (PR.ProductID = CP.ProductID)
    LEFT JOIN taClients AS CS ON (CS.ClientID = CP.ClientID)
WHERE
  (BP.Status = 1) AND
  (BP.TerminationDate IS NOT NULL) AND
  (BP.TerminationDate <> '0000-00-00') AND
  (BP.TerminationDate <= @aEffectiveDate) AND
  (CP.CancelApproveDateManager IS NOT NULL) AND
  (CP.CancelApproveDateManager <> '0000-00-00') AND
  (CP.CancelApproveDateExecutive IS NOT NULL) AND
  (CP.CancelApproveDateExecutive <> '0000-00-00')
ORDER BY
  BP.TerminationDate,
  BP.ClientBranchProductID
