SELECT
  BR.BranchID,
  IH.ClientID,
  CS.GroupName,
  CS.FinNumber,
  IH.Batch,
  COALESCE(IF(IH.AccountBookType = 'Xero', XE.XeroEntityName, IF(IH.AccountBookType = 'Sage', SE.SageEntityName, NULL)), CONCAT(IH.AccountBookType, '_', IH.AccountBookID)) AS AccountBookName,
  IH.DocumentNumber,
  IH.DocumentDate,
  JL.Description,
  BR.BranchRegion,
  PC.ProductCategory,
  JL.CreditAccount AS GLAccount,
  JL.CreditBranchRegion AS RegionTrackingCategory,
  JL.CreditProductCategory AS ProductTrackingCategory,
  JL.LineAmount AS RetainerRevenue
FROM
  taManualJournalLine AS JL
    LEFT JOIN taInvoiceHeader   AS IH ON (IH.CMSInvNo = JL.CMSInvNo)
    LEFT JOIN taSageEntity      AS SE ON ((SE.SageEntityID = IH.AccountBookID) AND (IH.AccountBookType = 'Sage'))
    LEFT JOIN taXeroEntity      AS XE ON ((XE.XeroEntityID = IH.AccountBookID) AND (IH.AccountBookType = 'Xero'))
    LEFT JOIN taBranches        AS BR ON (BR.TrackingCategory = JL.CreditBranchRegion)
    LEFT JOIN taProductCategory AS PC ON (PC.TrackingCategory = JL.CreditProductCategory)
    LEFT JOIN taClients         AS CS ON (CS.ClientID = IH.ClientID)
WHERE
  (IH.DocumentNumber IS NOT NULL) AND
  (IH.DocumentNumber <> 'VOID') AND
  (IH.InvoiceType = 'R') AND
  (IH.Updated = 1)
  @pAccountBook
  @pBranchRegion
  @pProductCategory
  @pFromTo
  @pBatch
ORDER BY
  IH.Batch,
  CS.GroupName
