SELECT
  X.GroupName,
  X.FinNumber,
  X.ClientID,
  X.AccountBookName,
  X.National,
  X.Batch,
  X.Retainer,
  ROUND(X.Retainer * (1.0 + X.VatRate), 2) AS Inclusive
FROM (

  SELECT
    BE.GroupName,
    BE.FinNumber,
    CP.ClientID,
    CP.Batch,
    COALESCE(IF(CP.AccountBookType = 'Pastel', PB.DatabaseName, IF(CP.AccountBookType = 'Xero', XE.XeroEntityName, IF(CP.AccountBookType = 'Sage', SE.SageEntityName, NULL))), CONCAT(CP.AccountBookType, '_', CP.AccountBookID)) AS AccountBookName,
    IF(fnClientLabourNetRegionCount(CP.ClientID) > 1, 'National', 'Local') AS National,
    ROUND(fnClientProductRetainer(CP.ClientProductID, @aEffectiveDate), 2) AS Retainer,
    ROUND(fnVATRateOnDate(@aEffectiveDate, BE.TaxTypeID), 4) AS VatRate
  FROM
    taClientProducts AS CP
      LEFT JOIN taClients    AS BE ON (BE.ClientID = CP.ClientID)
      LEFT JOIN taPastelBook AS PB ON ((PB.PastelBookID = CP.AccountBookID) AND (CP.AccountBookType = 'Pastel'))
      LEFT JOIN taSageEntity AS SE ON ((SE.SageEntityID = CP.AccountBookID) AND (CP.AccountBookType = 'Sage'))
      LEFT JOIN taXeroEntity AS XE ON ((XE.XeroEntityID = CP.AccountBookID) AND (CP.AccountBookType = 'Xero'))
  WHERE
    (CP.Batch IS NULL) OR (1
      @pBatch
    )

) AS X
WHERE
  (X.Retainer > 0.0)
ORDER BY
  X.Batch,
  X.ClientID
