SELECT
  BR.BranchID,
  BR.TrackingCategory,
  IF(BR.Status = 1, 'Active', 'Closed') AS Status,
  U1.FullName AS BranchManager,
  BR.BranchName,
  ( SELECT
      GROUP_CONCAT(CONCAT(U1.FullName, ' (', U1.AllowedProducts, ')') ORDER BY U1.FullName SEPARATOR ';')
    FROM
      taUsers AS U1
    WHERE
      (U1.Status = 1) AND
      (fnUserBranch(U1.UserID, NOW()) = BR.BranchID)
  ) AS ActiveConsultants,
  ( SELECT
      GROUP_CONCAT(CONCAT(B1.LevelID, ';', B1.Description, ';', U1.FullName)
          ORDER BY COALESCE(B2.PositionID, B1.PositionID),B1.LevelID,B1.Description,U1.FullName)
    FROM
      taBranchStructure AS B1
        LEFT JOIN taUsers AS U1 ON (U1.UserID = B1.ConsultantID)
        LEFT JOIN taBranchStructure AS B2 ON (B2.PositionID = B1.LeaderPositionID)
    WHERE
      (B1.BranchID = BR.BranchID)
    GROUP BY
      B1.BranchID
  ) AS BranchStructure,
  ( SELECT
      GROUP_CONCAT(CONCAT(U1.FullName, ' (', U1.AllowedProducts, ')') ORDER BY U1.FullName SEPARATOR ';')
    FROM
      taUsers AS U1
    WHERE
      (U1.Status = 1) AND
      (fnUserBranch(U1.UserID, NOW()) <> BR.BranchID) AND
      (CONCAT(',', U1.AllowedRegions, ',') LIKE CONCAT('%,', BR.BranchID, ',%'))
  ) AS ExternalConsultants
FROM
  taBranches AS BR
    LEFT JOIN taUsers AS U1 ON (U1.UserID = BR.BranchManagerID)
WHERE
  1
  @pBranchID
  @pBranchStatus
ORDER BY
  BR.BranchRegion,
  BR.BranchName