SELECT

  -- Client Details
  CS.ClientID,
  CONCAT('(',CS.FinNumber,') ', CS.GroupName) AS SubjectName,
  CS.GroupName AS ClientName,
  CS.FinNumber,
  CP.ClientProductID,
  CP.ProductID,
  PR.ProductCategory,
  SM.LabourNetBranch,
  SM.ClientRetainer,
  SM.AccountBalance,
  SM.OverdueBalance,
  SM.BranchCount,
  SM.NoOfEmployees,
  HO.LabourNetBranchID,
  BR.BranchName AS LabourNetBranchName,

  -- Termination Details
  CS.NoticeReceivedDate,
  CS.TerminationDate,
  CS.TerminationReason,
  CS.TerminationNotes,

  -- Consultant Names
  ( SELECT GROUP_CONCAT(DISTINCT U3.FullName SEPARATOR ',')
    FROM
      taClientBranchProducts AS BP
        LEFT JOIN taUsers AS U3 ON (U3.UserID = BP.ConsultantID)
    WHERE
      (BP.ConsultantID IS NOT NULL) AND
      (BP.ConsultantID > 0) AND
      (BP.Status = 1) AND
      (BP.ClientProductID = CP.ClientProductID)
  ) AS Consultants,

  -- Branch Manager
  BR.BranchManagerID,

  -- Regional Product Managers
  ( SELECT GROUP_CONCAT(DISTINCT ConsultantID)
    FROM taBranchStructure
    WHERE
      (BranchID = HO.LabourNetBranchID) AND
      (Description LIKE CONCAT('%Regional ', PR.ProductCategory, ' Manager%'))
  ) AS RegionalProductManagerID,

  -- Product Executive
  PC.ProductManagerID AS ProductExecutiveID,

  -- Allocated Consultants
  ( SELECT GROUP_CONCAT(DISTINCT BP.ConsultantID SEPARATOR ',')
    FROM taClientBranchProducts AS BP
    WHERE
      (BP.ConsultantID IS NOT NULL) AND
      (BP.ConsultantID > 0) AND
      (BP.Status = 1) AND
      (BP.ClientProductID = CP.ClientProductID)
  ) AS ConsultantID,

  -- Sales People
  ( SELECT GROUP_CONCAT(DISTINCT BP.SalesCloserID SEPARATOR ',')
    FROM taClientBranchProducts AS BP
    WHERE
      (BP.ConsultantID IS NOT NULL) AND
      (BP.ConsultantID > 0) AND
      (BP.Status = 1) AND
      (BP.ClientProductID = CP.ClientProductID)
  ) AS SalesPersonID,

  -- Branch Administrators
  ( SELECT GROUP_CONCAT(DISTINCT ConsultantID)
    FROM taBranchStructure
    WHERE
      (BranchID = HO.LabourNetBranchID) AND
      (Description LIKE '%Branch Administrator%')
  ) AS BranchAdministratorID,

  -- Debtor Clerk
  FC.DebtorClerkID
FROM
  taClientProducts AS CP
    LEFT JOIN taClients         AS CS ON (CS.ClientID        = CP.ClientID)
    LEFT JOIN taClientSummary   AS SM ON (SM.ClientID        = CP.ClientID)
    LEFT JOIN taFinCategory     AS FC ON (FC.FinCategoryID   = CS.FinCategory)
    LEFT JOIN taClientBranches  AS HO ON ((HO.ClientID = CS.ClientID) AND (HO.IsHeadOffice = 1) AND (HO.BranchStatus = CS.ClientStatus))
    LEFT JOIN taBranches        AS BR ON (BR.BranchID        = HO.LabourNetBranchID)
    LEFT JOIN taProducts        AS PR ON (PR.ProductID       = CP.ProductID)
    LEFT JOIN taProductCategory AS PC ON (PC.ProductCategory = PR.ProductCategory)
WHERE
  (CP.ClientProductID = @nClientProductID)
