SELECT * FROM (

  SELECT
    U.BranchID,
    U.BranchRegion,
    U.ProductCategory,
    U.ConsultantID,
    U.Consultant,
    U.EmployeeCode,
    U.PayrollRegister,
    U.PayrollInstance,
    U.PastelBillingCode,
    U.ClientID,
    U.Client,
    U.ClientBranch,
    IF(U.IsHeadOffice > 0, '<b>Head Office</b>', 'branch') AS HeadOffice,
    IF(fnClientLabourNetRegionCount(U.ClientID) > 1, '<b>National</b>', 'local') AS National,
    U.ProductID,
    U.ProductName,
    fnClientCoverPeriodEnd(U.ClientID, @aToDate) AS CoverEndDate,
    U.NextIncreaseDate,
    U.RetainerFee
  FROM (

    SELECT
      Y.BranchID,
      Y.BranchRegion,
      Y.ProductCategory,
      Y.ConsultantID,
      Y.Consultant,
      Y.EmployeeCode,
      Y.PayrollRegister,
      Y.PayrollInstance,
      Y.PastelBillingCode,
      Y.ClientID,
      Y.Client,
      Y.ClientBranch,
      Y.ProductID,
      Y.ProductName,
      Y.Status,
      MAX(Y.IsHeadOffice) AS IsHeadOffice,
      MIN(Y.NextIncreaseDate) AS NextIncreaseDate,
      SUM(Y.RetainerFee) AS RetainerFee
    FROM (

      SELECT
        X.BranchID,
        X.BranchRegion,
        X.ProductCategory,
        X.ConsultantID,
        X.Consultant,
        X.EmployeeCode,
        X.ClientID,
        X.Client,
        X.PayrollRegister,
        X.PayrollInstance,
        X.PastelBillingCode,
        X.ClientBranch,
        X.IsHeadOffice,
        X.ProductID,
        X.ProductName,
        X.NextIncreaseDate,
        X.ClientBranchProductID,
        X.ClientBranchID,
        X.ClientProductID,
        X.Status,
        COALESCE(fnClientBranchProductRetainer(X.ClientBranchProductID, @aToDate), 0.0) AS RetainerFee
      FROM (

        SELECT
          BR.BranchID,
          BR.BranchRegion,
          IF(P3.TrackingCategory IS NULL, PR.ProductCategory, P2.ProductCategory) AS ProductCategory,
          BP.ConsultantID,
          U1.GivenName AS Consultant,
          U1.EmployeeCode,
          IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, CS.ClientID, CS.ParentClientID), CS.ClientID) AS ClientID,
          IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, CS.GroupName, CSP.GroupName), CS.GroupName) AS Client,
          IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, CS.PayrollRegister, CSP.PayrollRegister), CS.PayrollRegister) AS PayrollRegister,
          IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, CS.PayrollInstance, CSP.PayrollInstance), CS.PayrollInstance) AS PayrollInstance,
          IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, CS.PastelBillingCode, CSP.PastelBillingCode), CS.PastelBillingCode) AS PastelBillingCode,
          CB.BranchName AS ClientBranch,
          CB.IsHeadOffice,
          IF(P3.TrackingCategory IS NULL, CP.ProductID, P1.ProductID) AS ProductID,
          IF(P3.TrackingCategory IS NULL, PR.ProductName, P2.ProductName) AS ProductName,
          CP.NextIncreaseDate,
          BP.ClientBranchProductID,
          BP.ClientBranchID,
          IF(P3.TrackingCategory IS NULL, CP.ClientProductID, P1.ClientProductID) AS ClientProductID,
          BP.Status
        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 taClients         AS CS ON (CS.ClientID = CP.ClientID)
            LEFT JOIN taProducts        AS PR ON (PR.ProductID = CP.ProductID)
            LEFT JOIN taUsers           AS U1 ON (U1.UserID = BP.ConsultantID)
            LEFT JOIN taBranches        AS BR ON (BR.BranchID = CB.LabourNetBranchID)
            LEFT JOIN taClientProducts  AS P1 ON (P1.ClientProductID = CP.ParentClientProductID)
            LEFT JOIN taProducts        AS P2 ON (P2.ProductID       = P1.ProductID)
            LEFT JOIN taProductCategory AS P3 ON (P3.ProductCategory = P2.ProductCategory)
            LEFT JOIN taClients         AS CSP ON (CSP.ClientID = CS.ParentClientID)
        WHERE
          (BR.BranchID IS NOT NULL) AND (BR.BranchID > 0) AND
          (PR.ProductID IS NOT NULL) AND (PR.ProductID > 0) AND
          (BP.ConsultantID IS NOT NULL) AND (BP.ConsultantID > 0) AND
          (CS.ClientID IS NOT NULL) AND (CS.ClientID > 0) AND
          ((CS.ExcludeFromReport IS NULL) OR (CS.ExcludeFromReport = 0))

          @pBranchID
          @pProductID
          @pConsultantID
          @pClientID

          @pBranchRegion
          @pProductCategory
          @pConsultant
          @pClient

      ) AS X

    ) AS Y
    @pAllowZeroRetainer
    GROUP BY
      1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

  ) AS U

) AS Z