WITH

ttRetainerFee AS (
  SELECT
    PF.ClientBranchProductID,
    PF.RetainerFee
  FROM
    taClientProductFees AS PF
  WHERE
    (PF.ClientProductFeeID = (
      SELECT MAX(M.ClientProductFeeID)
      FROM taClientProductFees AS M
      WHERE
        (M.ClientBranchProductID = PF.ClientBranchProductID) AND
        (M.StartDate = (
          SELECT MAX(N.StartDate)
          FROM taClientProductFees AS N
          WHERE
            (N.ClientBranchProductID = M.ClientBranchProductID) AND
            (N.StartDate <= @aToDate)
        ))
    ))
),

ttClientLookup AS (
  SELECT
    CS.ClientID,
    BR.BranchRegion AS HeadOfficeBranchRegion,
    IF(fnClientLabourNetRegionCount(CS.ClientID) > 1, '<b>National</b>', 'local') AS National,
    fnClientCoverPeriodEnd(CS.ClientID, @aToDate) AS CoverEndDate
  FROM
    taClients AS CS
      LEFT JOIN taClients AS CSP ON (CSP.ClientID = CS.ParentClientID)
      LEFT JOIN (
        SELECT ClientID, MAX(ClientBranchID) AS ClientBranchID FROM taClientBranches WHERE (IsHeadOffice = 1) AND (BranchStatus = 1) GROUP BY ClientID
      ) AS X ON (X.ClientID = CS.ClientID)
      LEFT JOIN taClientBranches AS HO ON (HO.ClientBranchID = X.ClientBranchID)
      LEFT JOIN taBranches       AS BR ON (BR.BranchID = HO.LabourNetBranchID)
  WHERE
    1
    @pClientID
)

SELECT
  Z.BranchID,
  Z.BranchRegion,
  Z.ProductCategory,
  X.HeadOfficeBranchRegion,
  Z.ConsultantID,
  Z.Consultant,
  Z.EmployeeCode,
  Z.PayrollRegister,
  Z.PayrollInstance,
  Z.PastelBillingCode,
  Z.ClientID,
  Z.Client,
  Z.ClientBranch,
  IF(Z.IsHeadOffice > 0, '<b>Head Office</b>', 'branch') AS HeadOffice,
  X.National,
  Z.ProductID,
  Z.ProductName,
  X.CoverEndDate,
  Z.NextIncreaseDate,
  Z.RetainerFee
FROM (

  SELECT
    Y.*
  FROM (

    SELECT
      BR.BranchID,
      BR.BranchRegion,
      PR.ProductCategory,
      BP.ConsultantID,
      U1.GivenName AS Consultant,
      U1.EmployeeCode,
      IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, CS.ClientID, CS.ParentClientID), CS.ClientID) AS ClientID,
      IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, CS.GroupName, CSP.GroupName), CS.GroupName) AS Client,
      IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, CS.PayrollRegister, CSP.PayrollRegister), CS.PayrollRegister) AS PayrollRegister,
      IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, CS.PayrollInstance, CSP.PayrollInstance), CS.PayrollInstance) AS PayrollInstance,
      IF(@nRollUpIntoParentClient, IF(CSP.ClientID IS NULL, CS.PastelBillingCode, CSP.PastelBillingCode), CS.PastelBillingCode) AS PastelBillingCode,
      CB.BranchName AS ClientBranch,
      CB.IsHeadOffice,
      CP.ProductID,
      PR.ProductName,
      CP.NextIncreaseDate,
      BP.ClientBranchProductID,
      BP.ClientBranchID,
      BP.ClientProductID,
      BP.Status,
      COALESCE(RF.RetainerFee, 0.0) AS RetainerFee
    FROM
      taClientBranchProducts AS BP
        LEFT JOIN taClientProducts AS CP ON (CP.ClientProductID = BP.ClientProductID)
        LEFT JOIN taClientBranches AS CB ON (CB.ClientBranchID = BP.ClientBranchID)
        LEFT JOIN taClients        AS CS ON (CS.ClientID = CP.ClientID)
        LEFT JOIN taProducts       AS PR ON (PR.ProductID = CP.ProductID)
        LEFT JOIN taUsers          AS U1 ON (U1.UserID = BP.ConsultantID)
        LEFT JOIN taBranches       AS BR ON (BR.BranchID = CB.LabourNetBranchID)
        LEFT JOIN taClients        AS CSP ON (CSP.ClientID = CS.ParentClientID)
        LEFT JOIN ttRetainerFee    AS RF ON (RF.ClientBranchProductID = BP.ClientBranchProductID)
    WHERE
      (BR.BranchID IS NOT NULL) AND (BR.BranchID > 0) AND
      (PR.ProductID IS NOT NULL) AND (PR.ProductID > 0) AND
      (BP.ConsultantID IS NOT NULL) AND (BP.ConsultantID > 0) AND
      (CS.ClientID IS NOT NULL) AND (CS.ClientID > 0) AND
      ((CS.ExcludeFromReport IS NULL) OR (CS.ExcludeFromReport = 0))

      @pBranchID
      @pProductID
      @pConsultantID
      @pClientID

      @pBranchRegion
      @pProductCategory
      @pConsultant
      @pClient

  ) AS Y
  @pAllowZeroRetainer

) AS Z
LEFT JOIN ttClientLookup AS X ON (X.ClientID = Z.ClientID)