SELECT
  JH.CMSJournalNo,
  JH.CMSInvNo,
  JH.ItemNumber,
  JH.JournalType,
  JH.Narration,
  JH.AccountBookType,
  JH.AccountBookID,
  JH.ClientID AS CMSClientID,
  JH.AppointmentID,
  (SELECT MAX(ItemNumber) FROM taManualJournalHeader WHERE (CMSInvNo = @nCMSInvNo)) AS MaxItemNumber
FROM
  taManualJournalHeader AS JH
WHERE
  (JH.JournalType IN ('reallocation','amortization')) AND
  (JH.CMSInvNo = @nCMSInvNo)
ORDER BY
  JH.ItemNumber
