SELECT
  Z.ClientID,
  Z.FinNumber,
  Z.Batch,
  Z.GroupName,
  Z.BranchRegion,
  Z.ProductCategory,
  Z.RetainerFee AS ClientAllocation,
  M.RetainerRevenue AS RetainerRevenue,
  (COALESCE(M.RetainerRevenue, 0) - Z.RetainerFee) AS Difference
FROM (
      SELECT
        Y.ClientID,
        Y.FinNumber,
        Y.Batch,
        Y.GroupName,
        Y.BranchRegion,
        Y.ProductCategory,
        SUM(Y.RetainerFee) AS RetainerFee
      FROM (
        SELECT
          X.ClientID,
          X.FinNumber,
          X.Batch,
          X.GroupName,
          X.BranchRegion,
          X.ProductCategory,
          fnClientBranchProductRetainer(X.ClientBranchProductID, @aEffectiveDate) * 1.0 AS RetainerFee
        FROM (
            SELECT
              CS.ClientID,
              CS.FinNumber,
              CP.Batch,
              CS.GroupName,
              BR.BranchRegion,
              PR.ProductCategory,
              BP.ClientBranchProductID
            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 taBranches       AS BR ON (BR.BranchID = CB.LabourNetBranchID)
            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
              ((0 IN (@nBatch)) OR (CP.Batch IN (@nBatch)))
              @pProductCategoryA
              @pBranchRegion
          ) AS X
        ) AS Y
        GROUP BY
          1,2,3,4,5,6
    ) AS Z
    LEFT JOIN (
      SELECT
        N.ClientID,
        N.FinNumber,
        N.Batch,
        N.GroupName,
        N.BranchRegion,
        N.ProductCategory,
        SUM(N.RetainerRevenue) AS RetainerRevenue
      FROM (
        SELECT
          IH.ClientID,
          CS.FinNumber,
          CAST(TRIM(SUBSTR(Batch, LOCATE(' ', IH.Batch))) AS SIGNED) AS Batch,
          CS.GroupName,
          BR.BranchRegion,
          PC.ProductCategory,
          JL.LineAmount AS RetainerRevenue
        FROM
          taManualJournalLine AS JL
            LEFT JOIN taInvoiceHeader   AS IH ON (IH.CMSInvNo = JL.CMSInvNo)
            LEFT JOIN taBranches        AS BR ON (BR.TrackingCategory = JL.CreditBranchRegion)
            LEFT JOIN taProductCategory AS PC ON (PC.TrackingCategory = JL.CreditProductCategory)
            LEFT JOIN taClients         AS CS ON (CS.ClientID = IH.ClientID)
        WHERE
          (IH.DocumentNumber IS NOT NULL) AND
          (IH.DocumentNumber <> 'VOID') AND
          (IH.InvoiceType = 'R') AND
          (IH.Updated = 1) AND
          ((CS.ExcludeFromReport IS NULL) OR (CS.ExcludeFromReport = 0)) AND
          (IH.DocumentDate >= @aFromDate) AND
          (IH.DocumentDate <= @aToDate) AND
          ((0 IN (@nBatch)) OR (CAST(TRIM(SUBSTR(Batch, LOCATE(' ', IH.Batch))) AS SIGNED) IN (@nBatch)))
          @pProductCategoryB
          @pBranchRegion
      ) AS N
      GROUP BY
        1,2,3,4,5,6
    ) AS M ON ((M.ClientID = Z.ClientID) AND (M.Batch = Z.Batch) AND (M.BranchRegion = Z.BranchRegion) AND (M.ProductCategory = Z.ProductCategory))
WHERE
  (ABS(Z.RetainerFee - COALESCE(M.RetainerRevenue, 0)) > 0.01)
ORDER BY
  3,2