SELECT
  COALESCE(IF(AJ.AccountBookType = 'Xero', XE.XeroEntityName, IF(AJ.AccountBookType = 'Sage', SE.SageEntityName, NULL)), CONCAT(AJ.AccountBookType, '_', AJ.AccountBookID)) AS AccountBookName,
  XE.ShortCode,
  'Missing From CMS' AS ExceptionType,
  AJ.JournalDate AS DocumentDate,
  AJ.JournalNumber AS DocumentNumber,
  AJ.SourceType,
  AJ.SourceExternalID AS RecordID,
  JL.Description,
  JL.AccountCode AS GLAccount,
  JL.BranchRegion AS RegionTrackingCategory,
  JL.ProductCategory AS ProductTrackingCategory,
  - JL.NetAmount AS AmountExclusive,
  0.0 AS AmountExclusiveCMS
FROM
  taAccountBookJournal AS AJ
    LEFT JOIN taAccountBookJournalLine AS JL ON ((JL.AccountBookType = AJ.AccountBookType) AND (JL.AccountBookID = AJ.AccountBookID) AND (JL.JournalNumber = AJ.JournalNumber))
    LEFT JOIN taXeroEntity             AS XE ON ((XE.XeroEntityID = AJ.AccountBookID) AND (AJ.AccountBookType = 'Xero'))
    LEFT JOIN taSageEntity             AS SE ON ((SE.SageEntityID = AJ.AccountBookID) AND (AJ.AccountBookType = 'Sage'))
    LEFT JOIN taInvoiceHeader          AS IH ON (IH.ExternalID = AJ.SourceExternalID)
WHERE
  (AJ.AccountBookType = @aAccountBookType) AND
  (AJ.AccountBookID = @nAccountBookID) AND
  (JL.AccountCode = @aAccountCode) AND
  (IH.CMSInvNo IS NULL) AND
  (AJ.SourceExternalID IN (

    SELECT X.SourceExternalID FROM (
      SELECT
        AJ.SourceExternalID,
        SUM(JL.NetAmount) AS NetAmount
      FROM
        taAccountBookJournal AS AJ
          INNER JOIN taAccountBookJournalLine AS JL ON ((JL.AccountBookType = AJ.AccountBookType) AND (JL.AccountBookID = AJ.AccountBookID) AND (JL.JournalNumber = AJ.JournalNumber))
          LEFT JOIN taInvoiceHeader AS IH ON (IH.ExternalID = AJ.SourceExternalID)
      WHERE
        (AJ.AccountBookType = @aAccountBookType) AND
        (AJ.AccountBookID = @nAccountBookID) AND
        (JL.AccountCode = @aAccountCode) AND
        (IH.CMSInvNo IS NULL)
        @pJournalFromTo
      GROUP BY
        1
    ) AS X WHERE (X.NetAmount <> 0.0)

  ))
  @pJournalFromTo

UNION

SELECT
  COALESCE(IF(IH.AccountBookType = 'Xero', XE.XeroEntityName, IF(IH.AccountBookType = 'Sage', SE.SageEntityName, NULL)), CONCAT(IH.AccountBookType, '_', IH.AccountBookID)) AS AccountBookName,
  XE.ShortCode,
  'Missing From Xero' AS ExceptionType,
  IH.DocumentDate,
  IH.DocumentNumber,
  'CMSINVOICE' AS SourceType,
  IH.CMSInvNo AS RecordID,
  IL.Description,
  IL.GLAccount,
  IL.BranchRegion AS RegionTrackingCategory,
  IL.ProductCategory AS ProductTrackingCategory,
  0.0 AS AmountExclusive,
  IL.ExclusivePrice AS AmountExclusiveCMS
FROM
  taInvoiceLine AS IL
    LEFT JOIN taInvoiceHeader      AS IH ON (IH.CMSInvNo = IL.CMSInvNo)
    LEFT JOIN taXeroEntity         AS XE ON ((XE.XeroEntityID = IH.AccountBookID) AND (IH.AccountBookType = 'Xero'))
    LEFT JOIN taSageEntity         AS SE ON ((SE.SageEntityID = IH.AccountBookID) AND (IH.AccountBookType = 'Sage'))
    LEFT JOIN taAccountBookJournal AS AJ ON ((AJ.AccountBookType = IH.AccountBookType) AND (AJ.AccountBookID = IH.AccountBookID) AND (AJ.SourceExternalID = IH.ExternalID))
WHERE
  (IH.AccountBookType = @aAccountBookType) AND
  (IH.AccountBookID = @nAccountBookID) AND
  (IH.DocumentNumber IS NOT NULL) AND
  (IH.DocumentNumber <> 'VOID') AND
  (IH.Updated = 1) AND
  (IL.GLAccount = @aAccountCode) AND
  (IL.ExclusivePrice > 0.0) AND
  (AJ.JournalNumber IS NULL)
  @pInvoiceFromTo

UNION

SELECT
  X.AccountBookName,
  X.ShortCode,
  'Value Differs' AS ExceptionType,
  X.DocumentDate,
  X.DocumentNumber,
  Y.SourceType,
  Y.SourceExternalID AS RecordID,
  X.Description,
  X.GLAccount,
  X.BranchRegion AS RegionTrackingCategory,
  X.ProductCategory AS ProductTrackingCategory,
  ABS(Y.AmountExclusive) AS AmountExclusive,
  X.AmountExclusiveCMS
FROM (

    SELECT
      IH.AccountBookType,
      IH.AccountBookID,
      COALESCE(IF(IH.AccountBookType = 'Xero', XE.XeroEntityName, IF(IH.AccountBookType = 'Sage', SE.SageEntityName, NULL)), CONCAT(IH.AccountBookType, '_', IH.AccountBookID)) AS AccountBookName,
      XE.ShortCode,
      IH.DocumentDate,
      IH.DocumentNumber,
      IH.ExternalID,
      IL.GLAccount,
      IL.BranchRegion,
      IL.ProductCategory,
      GROUP_CONCAT(DISTINCT IL.Description SEPARATOR '<br>') AS Description,
      SUM(IL.ExclusivePrice) AS AmountExclusiveCMS
    FROM
      taInvoiceLine AS IL
        LEFT JOIN taInvoiceHeader AS IH ON (IH.CMSInvNo = IL.CMSInvNo)
        LEFT JOIN taXeroEntity    AS XE ON ((XE.XeroEntityID = IH.AccountBookID) AND (IH.AccountBookType = 'Xero'))
        LEFT JOIN taSageEntity    AS SE ON ((SE.SageEntityID = IH.AccountBookID) AND (IH.AccountBookType = 'Sage'))
    WHERE
      (IH.AccountBookType = @aAccountBookType) AND
      (IH.AccountBookID = @nAccountBookID) AND
      (IH.DocumentNumber IS NOT NULL) AND
      (IH.DocumentNumber <> 'VOID') AND
      (IH.Updated = 1) AND
      (IL.GLAccount = @aAccountCode) AND
      (IL.ExclusivePrice > 0.0)
      @pInvoiceFromTo
    GROUP BY
      1,2,3,4,5,6,7,8,9,10

  ) AS X
    LEFT JOIN (
      SELECT
        AJ.AccountBookType,
        AJ.AccountBookID,
        AJ.SourceType,
        AJ.SourceExternalID,
        JL.BranchRegion,
        JL.ProductCategory,
        SUM(JL.NetAmount) AS AmountExclusive
      FROM
        taAccountBookJournal AS AJ
          LEFT JOIN taAccountBookJournalLine AS JL ON
            ((JL.AccountBookType = AJ.AccountBookType) AND (JL.AccountBookID = AJ.AccountBookID) AND (JL.JournalNumber = AJ.JournalNumber))
      WHERE
        (AJ.AccountBookType = @aAccountBookType) AND
        (AJ.AccountBookID = @nAccountBookID) AND
        (JL.AccountCode = @aAccountCode)
        @pJournalFromTo
      GROUP BY
        1,2,3,4,5,6
    ) AS Y ON ((Y.AccountBookType = X.AccountBookType) AND (Y.AccountBookID = X.AccountBookID) AND
               (Y.SourceExternalID = X.ExternalID) AND (Y.BranchRegion = X.BranchRegion) AND (Y.ProductCategory = X.ProductCategory))
WHERE
  (Y.SourceExternalID IS NOT NULL) AND
  (
    ((X.AmountExclusiveCMS - ABS(Y.AmountExclusive)) < -0.01) OR
    ((X.AmountExclusiveCMS - ABS(Y.AmountExclusive)) > 0.01)
  )

ORDER BY
  1,2,3,4
