SELECT
  BR.BranchName AS LabourNetBranch,
  U1.FullName AS Consultant,
  CB.ClientID,
  BE.FinNumber,
  BP.ClientBranchID,
  BP.ClientBranchProductID,
  CB.BranchName AS ClientBranchName,
  CP.Batch,
  IF(BP.Status = 0, 'Cancelled', 'Active') AS Status,
  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,
  BR.BranchRegion AS RegionTrackingCategory,
  PC.TrackingCategory AS ProductTrackingCategory,
  SM.ClientGroup,
  IF(fnClientLabourNetRegionCount(BE.ClientID) > 1, 'National', 'Local') AS National,
  BP.ClientProductID,
  CP.ProductID,
  PR.ProductName,
  fnClientCoverPeriodEnd(BE.ClientID, @aEffectiveDate) AS CoverEndDate,
  ROUND(fnClientBranchProductRetainer(BP.ClientBranchProductID, @aEffectiveDate) * 1.0, 2) AS Retainer
FROM
  taClientBranchProducts AS BP
    LEFT JOIN taClientBranches  AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
    LEFT JOIN taClientProducts  AS CP ON (CP.ClientProductID = BP.ClientProductID)
    LEFT JOIN taProducts        AS PR ON (PR.ProductID = CP.ProductID)
    LEFT JOIN taProductCategory AS PC ON (PC.ProductCategory = PR.ProductCategory)
    LEFT JOIN taClientSummary   AS SM ON (SM.ClientID = CB.ClientID)
    LEFT JOIN taUsers           AS U1 ON (U1.UserID = BP.ConsultantID)
    LEFT JOIN taBranches        AS BR ON (BR.BranchID = CB.LabourNetBranchID)
    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 NOT NULL) AND
  (fnClientBranchProductRetainer(BP.ClientBranchProductID, @aEffectiveDate) > 0)
  @pBranchID
  @pConsultantID
  @pBatch
  @pClientStatus
  @pClientGroup
ORDER BY
  CP.Batch,
  BR.BranchName,
  U1.FullName
