SELECT
  RR.RetainerReallocationID,
  RR.AccountBookType,
  RR.AccountBookID,
  RR.Batch,
  RR.YearMonth,
  COALESCE(IF(RR.AccountBookType = 'Xero', XE.XeroEntityName, IF(RR.AccountBookType = 'Sage', SE.SageEntityName, NULL)), CONCAT(RR.AccountBookType, '_', RR.AccountBookID)) AS AccountBookName,
  IF(RR.AccountBookType = 'Xero', XE.InvoicePrefix, IF(RR.AccountBookType = 'Sage', SE.InvoicePrefix, 'IN')) AS InvoicePrefix,
  RR.CMSInvNo,
  RR.ClientID,
  CS.FinNumber,
  CS.GroupName,
  RR.TaxTypeID,
  RR.Description,
  RR.DebitAccount,
  RR.DebitBranchRegion,
  RR.DebitProductCategory,
  RR.CreditAccount,
  RR.CreditBranchRegion,
  RR.CreditProductCategory,
  RR.LineAmount,
  RR.CMSJournalNo
FROM
  taRetainerReallocation AS RR
    LEFT JOIN taClients AS CS ON (CS.ClientID = RR.ClientID)
    LEFT JOIN taXeroEntity AS XE ON ((XE.XeroEntityID = RR.AccountBookID) AND (RR.AccountBookType = 'Xero'))
    LEFT JOIN taSageEntity AS SE ON ((SE.SageEntityID = RR.AccountBookID) AND (RR.AccountBookType = 'Sage'))
WHERE
  @pNoJournal
  @pRegions
  ((@nBatch = 0) OR (RR.Batch = @nBatch)) AND
  (RR.YearMonth = (YEAR(@aActionDate) * 100 + MONTH(@aActionDate)))
ORDER BY
  RR.AccountBookType,
  RR.AccountBookID,
  RR.ClientID
