SELECT
  1 AS Section,
  BR.BranchID,
  IH.ClientID,
  CS.FinNumber,
  IL.Description,
  IH.DocumentNumber,
  IL.GLAccount,
  IL.BranchRegion AS RegionTrackingCategory,
  IL.ProductCategory AS ProductTrackingCategory,
  IF(IH.DocumentDate >= @aToDate12 AND IH.DocumentDate < @aToDate11, IL.ExclusivePrice, 0.0) AS RetainerFee12,
  IF(IH.DocumentDate >= @aToDate11 AND IH.DocumentDate < @aToDate10, IL.ExclusivePrice, 0.0) AS RetainerFee11,
  IF(IH.DocumentDate >= @aToDate10 AND IH.DocumentDate < @aToDate09, IL.ExclusivePrice, 0.0) AS RetainerFee10,
  IF(IH.DocumentDate >= @aToDate09 AND IH.DocumentDate < @aToDate08, IL.ExclusivePrice, 0.0) AS RetainerFee9,
  IF(IH.DocumentDate >= @aToDate08 AND IH.DocumentDate < @aToDate07, IL.ExclusivePrice, 0.0) AS RetainerFee8,
  IF(IH.DocumentDate >= @aToDate07 AND IH.DocumentDate < @aToDate06, IL.ExclusivePrice, 0.0) AS RetainerFee7,
  IF(IH.DocumentDate >= @aToDate06 AND IH.DocumentDate < @aToDate05, IL.ExclusivePrice, 0.0) AS RetainerFee6,
  IF(IH.DocumentDate >= @aToDate05 AND IH.DocumentDate < @aToDate04, IL.ExclusivePrice, 0.0) AS RetainerFee5,
  IF(IH.DocumentDate >= @aToDate04 AND IH.DocumentDate < @aToDate03, IL.ExclusivePrice, 0.0) AS RetainerFee4,
  IF(IH.DocumentDate >= @aToDate03 AND IH.DocumentDate < @aToDate02, IL.ExclusivePrice, 0.0) AS RetainerFee3,
  IF(IH.DocumentDate >= @aToDate02 AND IH.DocumentDate < @aToDate01, IL.ExclusivePrice, 0.0) AS RetainerFee2,
  IF(IH.DocumentDate >= @aToDate01 AND IH.DocumentDate < @aToDate00, IL.ExclusivePrice, 0.0) AS RetainerFee1
FROM
  taInvoiceLine AS IL
    LEFT JOIN taInvoiceHeader   AS IH ON (IH.CMSInvNo = IL.CMSInvNo)
    LEFT JOIN taBranches        AS BR ON (BR.TrackingCategory = IL.BranchRegion)
    LEFT JOIN taProductCategory AS PC ON (PC.TrackingCategory = IL.ProductCategory)
    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
  (IL.LineQuantity > 1) AND
  (IH.Updated = 1) AND
  (IH.DocumentDate >= @aToDate12) AND
  (IH.DocumentDate <= @aToDate00)
  @pAccountBook
  @pBranchRegion
  @pProductCategory

UNION

SELECT
  2 AS Section,
  BR.BranchID,
  IH.ClientID,
  CS.FinNumber,
  JL.Description,
  IH.DocumentNumber,
  JL.DebitAccount AS GLAccount,
  JL.DebitBranchRegion AS RegionTrackingCategory,
  JL.DebitProductCategory AS ProductTrackingCategory,
  IF(JH.JournalDate >= @aToDate12 AND JH.JournalDate < @aToDate11, 0.0 - JL.LineAmount, 0.0) AS RetainerFee12,
  IF(JH.JournalDate >= @aToDate11 AND JH.JournalDate < @aToDate10, 0.0 - JL.LineAmount, 0.0) AS RetainerFee11,
  IF(JH.JournalDate >= @aToDate10 AND JH.JournalDate < @aToDate09, 0.0 - JL.LineAmount, 0.0) AS RetainerFee10,
  IF(JH.JournalDate >= @aToDate09 AND JH.JournalDate < @aToDate08, 0.0 - JL.LineAmount, 0.0) AS RetainerFee9,
  IF(JH.JournalDate >= @aToDate08 AND JH.JournalDate < @aToDate07, 0.0 - JL.LineAmount, 0.0) AS RetainerFee8,
  IF(JH.JournalDate >= @aToDate07 AND JH.JournalDate < @aToDate06, 0.0 - JL.LineAmount, 0.0) AS RetainerFee7,
  IF(JH.JournalDate >= @aToDate06 AND JH.JournalDate < @aToDate05, 0.0 - JL.LineAmount, 0.0) AS RetainerFee6,
  IF(JH.JournalDate >= @aToDate05 AND JH.JournalDate < @aToDate04, 0.0 - JL.LineAmount, 0.0) AS RetainerFee5,
  IF(JH.JournalDate >= @aToDate04 AND JH.JournalDate < @aToDate03, 0.0 - JL.LineAmount, 0.0) AS RetainerFee4,
  IF(JH.JournalDate >= @aToDate03 AND JH.JournalDate < @aToDate02, 0.0 - JL.LineAmount, 0.0) AS RetainerFee3,
  IF(JH.JournalDate >= @aToDate02 AND JH.JournalDate < @aToDate01, 0.0 - JL.LineAmount, 0.0) AS RetainerFee2,
  IF(JH.JournalDate >= @aToDate01 AND JH.JournalDate < @aToDate00, 0.0 - JL.LineAmount, 0.0) AS RetainerFee1
FROM
  taManualJournalLine AS JL
    LEFT JOIN taManualJournalHeader AS JH ON ((JH.CMSInvNo = JL.CMSInvNo) AND (JH.ItemNumber = JL.ItemNumber))
    LEFT JOIN taBranches            AS BR ON (BR.TrackingCategory = JL.DebitBranchRegion)
    LEFT JOIN taProductCategory     AS PC ON (PC.TrackingCategory = JL.DebitProductCategory)
    LEFT JOIN taInvoiceHeader       AS IH ON (IH.CMSInvNo = JL.CMSInvNo)
    LEFT JOIN taClients             AS CS ON (CS.ClientID = IH.ClientID)
WHERE
  (JH.Updated = 1) AND
  (JH.JournalDate >= @aToDate12) AND
  (JH.JournalDate <= @aToDate00) AND
  (JL.DebitAccount <> @aDeferredGLCode)
  @pAccountBook
  @pBranchRegion
  @pProductCategory

UNION

SELECT
  X.Section,
  X.BranchID,
  X.ClientID,
  X.FinNumber,
  X.Description,
  X.DocumentNumber,
  X.GLAccount,
  X.RegionTrackingCategory,
  X.ProductTrackingCategory,
  SUM(X.RetainerFee12) AS RetainerFee12,
  SUM(X.RetainerFee11) AS RetainerFee11,
  SUM(X.RetainerFee10) AS RetainerFee10,
  SUM(X.RetainerFee9) AS RetainerFee9,
  SUM(X.RetainerFee8) AS RetainerFee8,
  SUM(X.RetainerFee7) AS RetainerFee7,
  SUM(X.RetainerFee6) AS RetainerFee6,
  SUM(X.RetainerFee5) AS RetainerFee5,
  SUM(X.RetainerFee4) AS RetainerFee4,
  SUM(X.RetainerFee3) AS RetainerFee3,
  SUM(X.RetainerFee2) AS RetainerFee2,
  SUM(X.RetainerFee1) AS RetainerFee1
FROM (
  SELECT
    3 AS Section,
    BR.BranchID,
    IH.ClientID,
    CS.FinNumber,
    'Realisation of Amortized Retainer' AS Description,
    IH.DocumentNumber,
    JH.JournalDate AS DocumentDate,
    JL.CreditAccount AS GLAccount,
    JL.CreditBranchRegion AS RegionTrackingCategory,
    JL.CreditProductCategory AS ProductTrackingCategory,
    IF(JH.JournalDate >= @aToDate12 AND JH.JournalDate < @aToDate11, JL.LineAmount, 0.0) AS RetainerFee12,
    IF(JH.JournalDate >= @aToDate11 AND JH.JournalDate < @aToDate10, JL.LineAmount, 0.0) AS RetainerFee11,
    IF(JH.JournalDate >= @aToDate10 AND JH.JournalDate < @aToDate09, JL.LineAmount, 0.0) AS RetainerFee10,
    IF(JH.JournalDate >= @aToDate09 AND JH.JournalDate < @aToDate08, JL.LineAmount, 0.0) AS RetainerFee9,
    IF(JH.JournalDate >= @aToDate08 AND JH.JournalDate < @aToDate07, JL.LineAmount, 0.0) AS RetainerFee8,
    IF(JH.JournalDate >= @aToDate07 AND JH.JournalDate < @aToDate06, JL.LineAmount, 0.0) AS RetainerFee7,
    IF(JH.JournalDate >= @aToDate06 AND JH.JournalDate < @aToDate05, JL.LineAmount, 0.0) AS RetainerFee6,
    IF(JH.JournalDate >= @aToDate05 AND JH.JournalDate < @aToDate04, JL.LineAmount, 0.0) AS RetainerFee5,
    IF(JH.JournalDate >= @aToDate04 AND JH.JournalDate < @aToDate03, JL.LineAmount, 0.0) AS RetainerFee4,
    IF(JH.JournalDate >= @aToDate03 AND JH.JournalDate < @aToDate02, JL.LineAmount, 0.0) AS RetainerFee3,
    IF(JH.JournalDate >= @aToDate02 AND JH.JournalDate < @aToDate01, JL.LineAmount, 0.0) AS RetainerFee2,
    IF(JH.JournalDate >= @aToDate01 AND JH.JournalDate < @aToDate00, JL.LineAmount, 0.0) AS RetainerFee1
  FROM
    taManualJournalLine AS JL
      LEFT JOIN taManualJournalHeader AS JH ON ((JH.CMSInvNo = JL.CMSInvNo) AND (JH.ItemNumber = JL.ItemNumber))
      LEFT JOIN taBranches            AS BR ON (BR.TrackingCategory = JL.CreditBranchRegion)
      LEFT JOIN taProductCategory     AS PC ON (PC.TrackingCategory = JL.CreditProductCategory)
      LEFT JOIN taInvoiceHeader       AS IH ON (IH.CMSInvNo = JL.CMSInvNo)
      LEFT JOIN taClients             AS CS ON (CS.ClientID = IH.ClientID)
  WHERE
    (JH.Updated = 1) AND
    (JH.JournalDate >= @aToDate12) AND
    (JH.JournalDate <= @aToDate00) AND
    (JL.DebitAccount = @aDeferredGLCode)
    @pAccountBook
    @pBranchRegion
    @pProductCategory
) AS X
GROUP BY 1,2,3,4,5,6,7,8,9

ORDER BY
  4,6,1,7