SELECT

  -- Prospect overview details.
  PS.ProspectID,
  PS.ProspectName,
  TRIM(PS.ProspectName) AS SubjectName,
  S1.OptionDescription AS Industry,
  S2.OptionDescription AS Seta,
  S3.OptionDescription AS SalesPhase,
  IF(PP.SalesPhaseID >= 5, 'Closed', 'Open') AS ProspectStatus,
  IF(TRIM(CONCAT(PP.ProductSubGroup, '')) = '', CONCAT(PP.ProductCategory, ''),
       CONCAT(TRIM(CONCAT(PP.ProductCategory, '')), ' - ', TRIM(CONCAT(PP.ProductSubGroup, '')))) AS Product,

  -- Sales Person
  (SELECT FullName FROM taUsers WHERE UserID = PP.SalesPersonID) AS SalesPerson,

  -- Branch Manager
  BR.BranchManagerID,

  -- Regional Product Managers
  NULL AS RegionalProductManagerID,

  -- Product Executives
  NULL AS ProductExecutiveID,

  -- Allocated Consultants
  PP.SpecialistUserID AS ConsultantID,

  -- Sales People
  PP.SalesPersonID,

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

  -- Debtor Clerk
  NULL AS DebtorClerkID
FROM
  taProspect AS PS
    LEFT JOIN taProspectProduct AS PP ON (PP.ProspectID = PS.ProspectID)
    LEFT JOIN taProductCategory AS PC ON (PC.ProductCategory = PP.ProductCategory)
    LEFT JOIN taSystemOption    AS S1 ON ((S1.OptionType = 'INDUSTRY') AND (S1.OptionID = PS.IndustryID))
    LEFT JOIN taSystemOption    AS S2 ON ((S2.OptionType = 'SETA')     AND (S2.OptionID = PS.SetaID))
    LEFT JOIN taSystemOption    AS S3 ON ((S3.OptionType = 'PROSPECT-SALES-PHASE') AND (S3.OptionID = PP.SalesPhaseID))
    LEFT JOIN taBranches        AS BR ON (BR.BranchID = PS.LabourNetBranchID)
WHERE
  (PS.ProspectID = @nProspectID) AND
  ((@aProductCategory = '') OR (PP.ProductCategory = @aProductCategory)) AND
  ((@aProductSubGroup = '') OR (PP.ProductSubGroup = @aProductSubGroup))
