SELECT
  X.FinNumber,
  X.GroupName,
  X.NextBillingDate,
  X.LineQuantity,
  X.ProductCategory,
  X.BranchRegion,
  X.Description,
  X.ExclusivePrice AS UnitPriceExcl,
  ROUND(X.ExclusivePrice * X.LineQuantity, 2) AS ExclusivePrice,
  ROUND(X.ExclusivePrice * X.LineQuantity * (1.0 + X.TaxRate), 2) AS InclusivePrice
FROM (

  SELECT
    Y.ClientID,
    BE.FinNumber,
    BE.GroupName,
    Y.NextBillingDate,
    Y.ProductCategory,
    Y.BranchRegion,
    CONCAT(IF(Y.BillingCycle <= 3, 'Quarterly ', IF(Y.BillingCycle <= 6, 'Bi-Annual ', 'Annual ')),'Subscription Fees - ',
            DATE_FORMAT(Y.NextBillingDate, "%M"), ' ', DATE_FORMAT(Y.NextBillingDate, "%Y"), ' to ',
            DATE_FORMAT(DATE_ADD(Y.NextBillingDate, INTERVAL (Y.BillingCycle - 1) MONTH), "%M"), ' ',
            DATE_FORMAT(DATE_ADD(Y.NextBillingDate, INTERVAL (Y.BillingCycle - 1) MONTH), "%Y"), ' - ', Y.ProductName, ' - ', Y.BranchRegionName) AS Description,
    fnVATRateOnDate(Y.NextBillingDate, COALESCE(BE.TaxTypeID, 1)) AS TaxRate,
    Y.BillingCycle AS LineQuantity,
    Y.ExclusivePrice AS ExclusivePrice
  FROM (

    SELECT
      Z.ClientID,
      Z.ClientProductID,
      Z.BillingCycle,
      Z.NextBillingDate,
      Z.ProductCategory,
      Z.BranchRegion,
      Z.ProductName,
      Z.BranchRegionName,
      SUM(fnClientBranchProductRetainer(Z.ClientBranchProductID, Z.NextBillingDate) * 1.0) AS ExclusivePrice
    FROM (

      SELECT
        CP.ClientID,
        CP.ClientProductID,
        BP.ClientBranchProductID,
        CP.BillingCycle,
        IF(CP.LastInvoiceBillingDate IS NULL, @aFromDate, DATE_ADD(CP.LastInvoiceBillingDate, INTERVAL CP.BillingCycle MONTH)) AS NextBillingDate,
        PC.TrackingCategory AS ProductCategory,
        BR.TrackingCategory AS BranchRegion,
        PR.ProductName,
        BR.BranchRegion AS BranchRegionName
      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 taBranches        AS BR ON (BR.BranchID        = CB.LabourNetBranchID)
          LEFT JOIN taProductCategory AS PC ON (PC.ProductCategory = PR.ProductCategory)
      WHERE
        (BP.Status = 1) AND
        (COALESCE(CP.BillingCycle, 1) > 1)
        @pBranchRegion
        @pProductCategory

    ) AS Z
    WHERE
      (Z.NextBillingDate >= @aFromDate) AND
      (Z.NextBillingDate <= @aToDate)
    GROUP BY
      1,2,3,4,5,6,7

  ) AS Y
    LEFT JOIN taClients AS BE ON (BE.ClientID = Y.ClientID)

) AS X
WHERE
  (ROUND(X.ExclusivePrice * X.LineQuantity, 2) > 0)
ORDER BY
  X.FinNumber,
  X.BranchRegion,
  X.ProductCategory
