SELECT
  JH.CMSJournalNo,
  JH.CMSInvNo,
  JH.ItemNumber,
  JH.JournalDate,
  CASE JH.JournalType
    WHEN 'amortization' THEN 'MDRJ'
    WHEN 'realisation' THEN 'MDRJ'
    WHEN 'reallocation' THEN 'MRJ'
    WHEN 'creditnote_reallocation' THEN 'MCMJ'
    WHEN 'creditnote_amortization' THEN 'MCMJ'
    WHEN 'creditnote_realisation' THEN 'MCMJ'
    WHEN 'creditnote_' THEN 'MCMJ'
    WHEN 'reallocation_void' THEN 'MRJ'
    WHEN 'internal_retainer' THEN 'SJ'
    WHEN 'internal_appointment' THEN 'SJ'
  END AS JournalCode,
  JH.Narration AS Description,
  JH.ExternalID,
  JH.Updated,
  JH.AccountBookType,
  JH.AccountBookID,
  JH.ClientID AS CMSClientID,
  IH.AccountCode,
  IH.DocumentNumber,
  IH.ExternalID AS InvoiceExternalID,
  '' AS LineItems
FROM
  taManualJournalHeader AS JH
    LEFT JOIN taInvoiceHeader AS IH ON (IH.CMSInvNo = JH.CMSInvNo)
WHERE
  (JH.AccountBookType = 'Sage') AND
  ((IH.CMSInvNo IS NULL) OR (IH.Updated = 1)) AND
  ((IH.CMSInvNo IS NULL) OR (IH.DocumentNumber <> 'VOID')) AND
  (JH.Updated = 0)
  @pFilter
ORDER BY
  JH.AccountBookID,
  JH.CMSJournalNo
