INSERT INTO ttRetainerBatchMembersDue
SELECT
  O.Batch,
  O.ClientID,
  O.InvoiceGroup,
  O.AccountBookType,
  O.AccountBookID,
  SUM(O.RetainerFees) AS RetainerFees
FROM (
  SELECT
    N.Batch,
    N.ClientID,
    N.InvoiceGroup,
    N.AccountBookType,
    N.AccountBookID,
    N.RetainerFees
  FROM (
    SELECT
      M.Batch,
      M.ClientID,
      M.InvoiceGroup,
      M.AccountBookType,
      M.AccountBookID,
      M.ClientProductID,
      ROUND((
        SELECT
          SUM(PF.RetainerFee)
        FROM
          taClientProductFees AS PF
            INNER JOIN taClientBranchProducts AS BP ON (BP.ClientBranchProductID = PF.ClientBranchProductID)
            INNER JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
        WHERE
          (BP.Status = 1) AND
          (CP.ClientProductID = M.ClientProductID) AND
          (PF.ClientProductFeeID = (
            SELECT MAX(M.ClientProductFeeID)
            FROM taClientProductFees AS M
            WHERE
              (M.ClientBranchProductID = PF.ClientBranchProductID) AND
              (M.StartDate = (
                SELECT MAX(N.StartDate)
                FROM taClientProductFees AS N
                WHERE
                  (N.ClientBranchProductID = PF.ClientBranchProductID) AND
                  (N.StartDate <= @aActionDate)
              ))
          ))
        ), 4) AS RetainerFees
    FROM (
      SELECT DISTINCT
        CP.Batch,
        CP.ClientID,
        CP.AccountBookType,
        CP.AccountBookID,
        CP.ClientProductID,
        IF(IF(P3.TrackingCategory IS NULL, 1, 0),
          IF(IF(P3.TrackingCategory IS NULL, CP.DedicatedRetainerInvoice, P1.DedicatedRetainerInvoice) = 0,
              CONCAT("CombinedProducts_", IF(P3.TrackingCategory IS NULL, CP.AccountBookID, P1.AccountBookID), '_', CP.ClientID),
              CP.ClientProductID),
          IF(IF(P3.TrackingCategory IS NULL, CP.DedicatedRetainerInvoice, P1.DedicatedRetainerInvoice) = 0,
              CONCAT("CombinedProducts_", IF(P3.TrackingCategory IS NULL, CP.AccountBookID, P1.AccountBookID), '_', CP.ClientID),
              CP.ParentClientProductID)
        ) AS InvoiceGroup
      FROM
        taClientBranchProducts as BP
          INNER JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
          INNER JOIN taClients AS BE ON (BE.ClientID = CP.ClientID)
          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)
      WHERE
        (BE.ClientStatus = 1) AND
        (BE.FinNumber <> 'NONE') AND
        (CP.Status = 1) AND
        (BP.Status = 1) AND
        (CP.Batch IS NOT NULL) AND (CP.Batch > 0) AND
        ((CP.Batch = @nBatch) OR (@nBatch <= 0)) AND
        ((COALESCE(CP.BillingCycle, 1) = 1) OR (fnAnnualRetainerDue(CP.ClientProductID, @aActionDate) > 0.0))
    ) AS M
  ) AS N
  WHERE
    (N.RetainerFees > 0)
) AS O
GROUP BY
  O.Batch,
  O.ClientID,
  O.InvoiceGroup,
  O.AccountBookType,
  O.AccountBookID
