SELECT
  X.InvoiceGroup,
  X.AccountBookType,
  X.AccountBookID,
  X.AccountBookName,
  X.ClientID,
  X.AccountCode,
  X.GroupName,
  X.VATNumber,
  X.PaymentMethod,
  X.InternalClient,
  X.SingleLineInvoice,
  X.InvoiceText,
  X.Address1,
  X.Address2,
  X.Address3,
  X.Address4,
  X.Address5,
  X.Postal1,
  X.Postal2,
  X.Postal3,
  X.PostalCode,
  X.EmailAddress,
  X.Telephone,
  X.Fax,
  X.InvoicePrefix,
  X.Consultant,
  X.ClientProductID,
  X.ParentClientProductID,
  @nBatch AS Batch,
  (YEAR(@aActionDate) * 100 + MONTH(@aActionDate)) AS YearMonth,
  0 AS CMSInvNo,
  0 AS CMSLineNo,
  NULL AS DocumentNumber,
  X.ProductCategory,
  X.ParentProductCategory,
  X.BranchRegion,
  X.IsParentClientProduct,
  X.GLAccount,
  COALESCE(X.Description, 'Subscription Fees') AS Description,
  X.TaxTypeID,
  X.TaxRate,
  X.LineQuantity,
  X.ExclusivePrice AS UnitPriceExcl,
  X.ExclusivePrice * (1.0 + X.TaxRate) AS UnitPriceIncl,
  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 AS AccountCode,
    BE.GroupName,
    BE.VATNumber,
    BE.PaymentMethod,
    BE.InternalClient,
    BE.SingleLineInvoice,
    BE.InvoiceText,
    COALESCE(HO.Address1, "") AS Address1,
    COALESCE(HO.Address2, "") AS Address2,
    COALESCE(HO.Address3, "") AS Address3,
    COALESCE(HO.Address4, "") AS Address4,
    COALESCE(HO.Address5, "") AS Address5,
    COALESCE(BE.Postal1, "") AS Postal1,
    COALESCE(BE.Postal2, "") AS Postal2,
    COALESCE(BE.Postal3, "") AS Postal3,
    COALESCE(BE.PostalCode, "") AS PostalCode,
    COALESCE(BE.EmailAddress, "") AS EmailAddress,
    CONCAT(COALESCE(HO.TelephoneAreaCode, ""), COALESCE(HO.TelephoneNo, "")) AS Telephone,
    CONCAT(COALESCE(HO.FaxAreaCode, ""), COALESCE(HO.FaxNo, "")) AS Fax,
    COALESCE(IF(Y.AccountBookType = 'Xero', XE.XeroEntityName, IF(Y.AccountBookType = 'Sage', SE.SageEntityName, NULL)), CONCAT(Y.AccountBookType, '_', Y.AccountBookID)) AS AccountBookName,
    IF(Y.AccountBookType = 'Xero', XE.InvoicePrefix, IF(Y.AccountBookType = 'Sage', SE.InvoicePrefix, '')) AS InvoicePrefix,
    ( SELECT
        U1.FullName
      FROM
        taClientBranchProducts AS BP1
          LEFT JOIN taClientProducts AS CP1 ON (CP1.ClientProductID = BP1.ClientProductID)
          LEFT JOIN taUsers AS U1 ON (U1.UserID = BP1.ConsultantID)
      WHERE
        (BP1.Status = 1) AND
        (CP1.ClientProductID = Y.ClientProductID)
      ORDER BY
        fnClientBranchProductRetainer(BP1.ClientBranchProductID, @aActionDate) DESC
      LIMIT 1
    ) AS Consultant,
    Y.ClientProductID,
    Y.ParentClientProductID,
    IF(Y.IsParentClientProduct,
      IF(COALESCE(Y.DedicatedRetainerInvoice, 0) = 0, CONCAT("CombinedProducts_", Y.AccountBookID, '_', Y.ClientID), Y.ClientProductID),
      IF(COALESCE(Y.DedicatedRetainerInvoice, 0) = 0, CONCAT("CombinedProducts_", Y.AccountBookID, '_', Y.ClientID), Y.ParentClientProductID)
    ) AS InvoiceGroup,
    Y.AccountBookType,
    Y.AccountBookID,
    Y.ProductCategory,
    Y.ParentProductCategory,
    Y.BranchRegion,
    Y.IsParentClientProduct,
    IF(Y.AnnualRetainerDue > 1, @aXeroAnnualGLCode, @aXeroGLCode) AS GLAccount,
    IF(Y.AnnualRetainerDue > 1,
      CONCAT(IF(Y.AnnualRetainerDue <= 3, 'Quarterly ', IF(Y.AnnualRetainerDue <= 6, 'Bi-Annual ', 'Annual ')),'Subscription Fees - ',
             DATE_FORMAT(@aActionDate, "%M"), ' ', DATE_FORMAT(@aActionDate, "%Y"), ' to ',
             DATE_FORMAT(DATE_ADD(@aActionDate, INTERVAL (Y.AnnualRetainerDue - 1) MONTH), "%M"), ' ',
             DATE_FORMAT(DATE_ADD(@aActionDate, INTERVAL (Y.AnnualRetainerDue - 1) MONTH), "%Y"), ' - ', COALESCE(Y.ProductName, ''), ' - ', COALESCE(Y.BranchRegionName, '')),
      CONCAT('Subscription Fees - ', DATE_FORMAT(@aActionDate, "%b"), ' ', DATE_FORMAT(@aActionDate, "%Y"), ' - ', COALESCE(Y.ProductName, ''), ' - ', COALESCE(Y.BranchRegionName, ''))
    ) AS Description,
    COALESCE(BE.TaxTypeID, 1) AS TaxTypeID,
    fnVATRateOnDate(@aActionDate, COALESCE(BE.TaxTypeID, 1)) AS TaxRate,
    Y.AnnualRetainerDue AS LineQuantity,
    Y.ExclusivePrice AS ExclusivePrice
  FROM (

    SELECT
      CP.ClientID,
      CP.ClientProductID,
      IF(P3.TrackingCategory IS NULL, CP.ClientProductID, P1.ClientProductID) AS ParentClientProductID,
      IF(P3.TrackingCategory IS NULL, COALESCE(CP.DedicatedRetainerInvoice, 0), COALESCE(P1.DedicatedRetainerInvoice, 0)) AS DedicatedRetainerInvoice,
      IF(P3.TrackingCategory IS NULL, CP.AccountBookType, P1.AccountBookType) AS AccountBookType,
      IF(P3.TrackingCategory IS NULL, CP.AccountBookID, P1.AccountBookID) AS AccountBookID,
      IF(P3.TrackingCategory IS NULL, fnAnnualRetainerDue(CP.ClientProductID, @aActionDate), fnAnnualRetainerDue(P1.ClientProductID, @aActionDate)) AS AnnualRetainerDue,
      IF(P3.TrackingCategory IS NULL, 1, 0) AS IsParentClientProduct,
      BR.TrackingCategory AS BranchRegion,
      PC.TrackingCategory AS ProductCategory,
      IF(P3.TrackingCategory IS NULL, PC.TrackingCategory, P3.TrackingCategory) AS ParentProductCategory,
      COALESCE(PR.ProductName, COALESCE(CP.ProductID, BP.ClientProductID)) AS ProductName,
      COALESCE(BR.BranchRegion, COALESCE(CB.LabourNetBranchID, BP.ClientBranchID)) AS BranchRegionName,
      SUM(fnClientBranchProductRetainer(BP.ClientBranchProductID, @aActionDate) * 1.0) AS ExclusivePrice
    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)
        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
      (BP.Status = 1) AND
      (
        (COALESCE(PC.MustHaveParent, 0) = 0) OR
        ((COALESCE(PC.MustHaveParent, 0) = 1) AND (P3.TrackingCategory IS NOT NULL) AND (P1.Status = 1))
      ) AND
      (CP.Batch = @nBatch)
      @pClientID
    GROUP BY
      1,2,3,4,5,6,7,8,9,10,11,12,13

  ) AS Y
    LEFT JOIN taClients AS BE ON (BE.ClientID = Y.ClientID)
    LEFT JOIN taClientBranches AS HO ON ((HO.ClientID = BE.ClientID) AND (HO.IsHeadOffice = 1) AND (HO.BranchStatus = 1))
    LEFT JOIN taXeroEntity AS XE ON ((XE.XeroEntityID = Y.AccountBookID) AND (Y.AccountBookType = 'Xero'))
    LEFT JOIN taSageEntity AS SE ON ((SE.SageEntityID = Y.AccountBookID) AND (Y.AccountBookType = 'Sage'))
) AS X
WHERE
  (ROUND(X.ExclusivePrice * X.LineQuantity, 2) > 0)
  @pInvoiceGroup
ORDER BY
  X.InvoiceGroup,
  X.IsParentClientProduct DESC,
  X.ProductCategory,
  X.BranchRegion
