SELECT
  JH.CMSJournalNo,
  JH.CMSInvNo,
  JH.ItemNumber,
  JH.JournalType,
  JH.JournalDate,
  JH.Narration,
  (SELECT MAX(ItemNumber) FROM taManualJournalHeader WHERE (CMSInvNo = @nCMSInvNo)) AS MaxItemNumber,
  JH.AccountBookType,
  JH.AccountBookID,
  JH.ClientID AS CMSClientID,
  JH.AppointmentID,
  A.ItemRealisationAmount
FROM
  taManualJournalHeader AS JH
    LEFT JOIN (

      SELECT
        Z.CMSInvNo,
        Z.ItemNumber,
        SUM(Z.ItemAmount) AS ItemRealisationAmount
      FROM (

        SELECT X.CMSInvNo, X.ItemNumber, X.ItemAmount FROM (
          SELECT
            JL.CMSInvNo,
            JL.ItemNumber,
            SUM(JL.LineAmount) AS ItemAmount
          FROM
            taManualJournalLine AS JL
          WHERE
            (JL.CMSInvNo = @nCMSInvNo) AND
            (JL.ItemNumber IN (SELECT ItemNumber FROM taManualJournalHeader WHERE (CMSInvNo = @nCMSInvNo) AND (JournalType = 'realisation')))
          GROUP BY
            1,2
        ) AS X

        UNION ALL

        SELECT Y.CMSInvNo, Y.ItemNumber, Y.ItemAmount FROM (
          SELECT
            JL.LinkCMSInvNo AS CMSInvNo,
            JL.LinkItemNumber AS ItemNumber,
            SUM(-JL.LineAmount) AS ItemAmount
          FROM
            taManualJournalLine AS JL
          WHERE
            (JL.LinkCMSInvNo = @nCMSInvNo) AND
            (JL.LinkItemNumber IN (SELECT ItemNumber FROM taManualJournalHeader WHERE (CMSInvNo = @nCMSInvNo) AND (JournalType = 'realisation')))
          GROUP BY
            1,2
        ) AS Y

      ) AS Z
      GROUP BY
      1,2

    ) AS A ON ((A.CMSInvNo = JH.CMSInvNo) AND (A.ItemNumber = JH.ItemNumber))
WHERE
  (JH.JournalType = 'realisation') AND
  (JH.CMSInvNo = @nCMSInvNo)
ORDER BY
  JH.ItemNumber
