SELECT DISTINCT * FROM (

  SELECT

    --
    -- Client details.
    --
    CS.ClientID,
    CS.FinNumber,

    --
    -- Client branch details.
    --
    COALESCE(CB.ClientBranchID, 0) AS ClientBranchID,
    REPLACE(REPLACE(REPLACE(COALESCE(CB.BranchName, ''), '"', ''), '''', ''), '\\', '') AS ClientBranchName,
    IF(COALESCE(CB.IsHeadOffice, 0) = 1, "Yes", "No") AS IsHeadOffice,
    REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(CB.Address1, ''), '\t', ' '), '"', ''), '''', ''), '\\', '') AS Address1,
    REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(CB.Address2, ''), '\t', ' '), '"', ''), '''', ''), '\\', '') AS Address2,
    REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(CB.Address3, ''), '\t', ' '), '"', ''), '''', ''), '\\', '') AS Address3,
    REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(CB.Address4, ''), '\t', ' '), '"', ''), '''', ''), '\\', '') AS Address4,
    REPLACE(REPLACE(REPLACE(REPLACE(COALESCE(CB.Address5, ''), '\t', ' '), '"', ''), '''', ''), '\\', '') AS Address5,
    COALESCE(CB.MapReference, '') AS MapReference,
    COALESCE(CB.LabourNetBranchID, '') AS LabourNetBranchID,
    COALESCE(BR.BranchName, '') AS LabourNetBranchName,

    --
    -- Product details.
    --
    COALESCE(PR.ProductCategory, '') AS ProductCategory,

    --
    -- Client product details.
    --
    COALESCE(CP.ClientProductID, 0) AS ClientProductID,

    --
    -- Client branch product details.
    --
    COALESCE(BP.ClientBranchProductID, 0) AS ClientBranchProductID,
    COALESCE(U1.FullName, '') AS Consultant,

    --
    -- Retainer.
    --
    ROUND(fnClientBranchProductRetainer(BP.ClientBranchProductID, NOW()) * 1.0, 2) 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)
      LEFT JOIN taUsers          AS U1 ON (U1.UserID = BP.ConsultantID)
      LEFT JOIN taBranches       AS BR ON (BR.BranchID = CB.LabourNetBranchID)
  WHERE
    (CS.ClientStatus = 1) AND
    (CB.BranchStatus = 1) AND
    (BP.Status = 1) AND
    (
      (@aProductCategory = '') OR
      (PR.ProductCategory = @aProductCategory)
    ) AND (
      (@aBranchRegion = '') OR
      (BR.BranchRegion = @aBranchRegion)
    )

) AS X
WHERE
  (X.Retainer > 0.0)
ORDER BY
  X.FinNumber,
  X.ClientBranchName