SELECT
  JH.CMSJournalNo,
  JH.AccountBookType,
  JH.AccountBookID,
  JH.CMSInvNo,
  JH.ItemNumber,
  JH.JournalDate,
  JH.Updated,
  JH.ClientID AS CMSClientID,
  JH.AppointmentID,
  IH.DocumentNumber,
  JH.Narration,
  COALESCE(IF(JH.AccountBookType = 'Xero', XE.XeroEntityName, IF(JH.AccountBookType = 'Sage', SE.SageEntityName, NULL)), CONCAT(JH.AccountBookType, '_', JH.AccountBookID)) AS AccountBookName,
  X.JournalAmount,
  JH.ExternalID AS JournalExternalID,
  IH.ExternalID AS InvoiceExternalID
FROM
  taManualJournalHeader AS JH
    LEFT JOIN taInvoiceHeader AS IH ON (IH.CMSInvNo = JH.CMSInvNo)
    LEFT JOIN taXeroEntity AS XE ON (XE.XeroEntityID = JH.AccountBookID)
    LEFT JOIN taSageEntity AS SE ON (SE.SageEntityID = JH.AccountBookID)
    LEFT JOIN (
      SELECT CMSJournalNo, SUM(LineAmount) AS JournalAmount
      FROM taManualJournalLine
      GROUP BY CMSJournalNo
    ) AS X ON (X.CMSJournalNo = JH.CMSJournalNo)
WHERE
  1
  @pClientID
  @pShowUpdated
  @pFilter
