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 taManualJournalHeader   AS IJ ON (IJ.ExternalID = AJ.SourceExternalID)
WHERE
  (AJ.AccountBookType = @aAccountBookType) AND
  (AJ.AccountBookID = @nAccountBookID) AND
  (JL.AccountCode = @aAccountCode) AND
  (IJ.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 taManualJournalHeader AS IJ ON (IJ.ExternalID = AJ.SourceExternalID)
      WHERE
        (AJ.AccountBookType = @aAccountBookType) AND
        (AJ.AccountBookID = @nAccountBookID) AND
        (JL.AccountCode = @aAccountCode) AND
        (IJ.CMSInvNo IS NULL)
        @pJournalFromTo
      GROUP BY
        1
    ) AS X WHERE (X.NetAmount <> 0.0)

  ))
  @pJournalFromTo

UNION

SELECT
  COALESCE(IF(JH.AccountBookType = 'Xero', XE.XeroEntityName, IF(JH.AccountBookType = 'Sage', SE.SageEntityName, NULL)), CONCAT(JH.AccountBookType, '_', JH.AccountBookID)) AS AccountBookName,
  XE.ShortCode,
  'Missing From Xero' AS ExceptionType,
  IH.DocumentDate,
  IH.DocumentNumber,
  'CMSINVOICE' AS SourceType,
  JH.CMSInvNo AS RecordID,
  JL.Description,
  JL.CreditAccount AS GLAccount,
  JL.CreditBranchRegion AS RegionTrackingCategory,
  JL.CreditProductCategory AS ProductTrackingCategory,
  0.0 AS AmountExclusive,
  (JL.LineAmount - COALESCE(A.LineAmountCredited, 0.0)) AS AmountExclusiveCMS
FROM
  taManualJournalLine AS JL
    LEFT JOIN taManualJournalHeader AS JH ON (JH.CMSInvNo = JL.CMSInvNo)
    LEFT JOIN taInvoiceHeader       AS IH ON (IH.CMSInvNo = JH.CMSInvNo)
    LEFT JOIN taXeroEntity          AS XE ON ((XE.XeroEntityID = JH.AccountBookID) AND (JH.AccountBookType = 'Xero'))
    LEFT JOIN taSageEntity          AS SE ON ((SE.SageEntityID = JH.AccountBookID) AND (JH.AccountBookType = 'Sage'))
    LEFT JOIN taAccountBookJournal  AS AJ ON ((AJ.AccountBookType = IH.AccountBookType) AND (AJ.AccountBookID = IH.AccountBookID) AND (AJ.SourceExternalID = JH.ExternalID))
    LEFT JOIN (

      SELECT
        JL1.LinkCMSInvNo AS CMSInvNo,
        JL1.LinkItemNumber AS ItemNumber,
        JL1.LinkCMSLineNo AS CMSLineNo,
        SUM(JL1.LineAmount) AS LineAmountCredited
      FROM
        taManualJournalLine AS JL1
          INNER JOIN taManualJournalHeader AS JH1 ON (JH1.CMSJournalNo = JL1.CMSJournalNo)
      WHERE
        (JH1.AccountBookID = @nAccountBookID) AND
        (JL1.DebitAccount = @aAccountCode) AND
        (JL1.LinkCMSInvNo IS NOT NULL)
      GROUP BY
        1,2,3

    ) AS A ON ((A.CMSInvNo = JL.CMSInvNo) AND (A.ItemNumber = JL.ItemNumber) AND (A.CMSLineNo = JL.CMSLineNo))
WHERE
  (IH.AccountBookType = @aAccountBookType) AND
  (IH.AccountBookID = @nAccountBookID) AND
  (IH.DocumentNumber IS NOT NULL) AND
  (IH.DocumentNumber <> 'VOID') AND
  (IH.Updated = 1) AND
  (JH.Updated = 1) AND
  (JL.CreditAccount = @aAccountCode) AND
  (JL.LineAmount > 0.0) AND
  (AJ.JournalNumber IS NULL) AND
  (ABS(JL.LineAmount - COALESCE(A.LineAmountCredited, 0.0)) > 0.004)
  @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(JH.AccountBookType = 'Xero', XE.XeroEntityName, IF(JH.AccountBookType = 'Sage', SE.SageEntityName, NULL)), CONCAT(JH.AccountBookType, '_', JH.AccountBookID)) AS AccountBookName,
      XE.ShortCode,
      IH.DocumentDate,
      IH.DocumentNumber,
      JH.ExternalID,
      JL.CreditAccount AS GLAccount,
      JL.CreditBranchRegion AS BranchRegion,
      JL.CreditProductCategory AS ProductCategory,
      GROUP_CONCAT(DISTINCT JL.Description SEPARATOR '<br>') AS Description,
      SUM(JL.LineAmount) AS AmountExclusiveCMS
    FROM
      taManualJournalLine AS JL
        LEFT JOIN taManualJournalHeader AS JH ON (JH.CMSInvNo = JL.CMSInvNo)
        LEFT JOIN taInvoiceHeader       AS IH ON (IH.CMSInvNo = JH.CMSInvNo)
        LEFT JOIN taXeroEntity          AS XE ON ((XE.XeroEntityID = JH.AccountBookID) AND (JH.AccountBookType = 'Xero'))
        LEFT JOIN taSageEntity          AS SE ON ((SE.SageEntityID = JH.AccountBookID) AND (JH.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
      (JL.CreditAccount = @aAccountCode) AND
      (JL.LineAmount > 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.004) OR
    ((X.AmountExclusiveCMS - ABS(Y.AmountExclusive)) > 0.004)
  )

ORDER BY
  1,2,3,4
