SELECT
  PP.SalesPersonID,
  PS.ProspectID,
  PS.ProspectName,
  IF((SELECT COUNT(*) FROM taProspectProduct AS Z WHERE ((Z.SalesPhaseID IS NULL) OR (Z.SalesPhaseID < 5)) AND
          (Z.ProspectID = PS.ProspectID)) > 0, 'Open', 'Closed') AS ProspectStatus,
  PS.DateCreated,
  PS.LastChangeDate,
  S1.OptionDescription AS Industry,
  S2.OptionDescription AS Seta,
  PS.PaymentMethod,
  S3.OptionDescription AS Origin,
  PS.OriginDetail,
  NULL AS FinNumber,
  BR.BranchName AS LabourNetBranch,
  CI.GroupName AS IntroducingClient,
  U1.FullName AS IntroducingConsultant,
  U2.FullName AS SalesConsultant,
  U2.EmployeeCode AS SalesConsultantEmployeeCode,
  U2.TargetAmount AS SalesConsultantTarget,
  U3.FullName AS TechnicalSpecialist,
  IF(TRIM(CONCAT(PP.ProductSubGroup, '')) = '', CONCAT(PP.ProductCategory, ''),
          CONCAT(TRIM(CONCAT(PP.ProductCategory, '')), ' - ', TRIM(CONCAT(PP.ProductSubGroup, '')))) AS Product,
  PP.QuotedAmount,
  PP.DateQuoted,
  PP.DateExpected,
  PP.DateSigned,
  PP.LiveDate,
  S4.OptionDescription AS SalesPhase
FROM
  taProspect AS PS
    LEFT JOIN taProspectProduct AS PP ON (PP.ProspectID = PS.ProspectID)
    LEFT JOIN taBranches AS BR ON (BR.BranchID = PS.LabourNetBranchID)
    LEFT JOIN taClients AS CI ON (CI.ClientID = PS.IntroducingClientID)
    LEFT JOIN taUsers AS U1 ON (U1.UserID = PS.SalesReferrerID)
    LEFT JOIN taUsers AS U2 ON (U2.UserID = PP.SalesPersonID)
    LEFT JOIN taUsers AS U3 ON (U3.UserID = PP.SpecialistUserID)
    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-ORIGIN')      AND (S3.OptionID = PS.OriginID))
    LEFT JOIN taSystemOption AS S4 ON ((S4.OptionType = 'PROSPECT-SALES-PHASE') AND (S4.OptionID = PP.SalesPhaseID))
WHERE
  ((PS.ClientID IS NULL) OR (PS.ClientID <= 0))
  @pBranchID
  @pProductCategory
  @pSalesPersonBranchID
  @pSalesPersonID
  @pSalesReferrerID
  @pOriginID
  @pSalesPhaseID
  @pFromTo

UNION

SELECT
  PP.SalesPersonID,
  PS.ProspectID,
  PS.ProspectName,
  IF((SELECT COUNT(*) FROM taProspectProduct AS Z WHERE ((Z.SalesPhaseID IS NULL) OR (Z.SalesPhaseID < 5)) AND
          (Z.ProspectID = PS.ProspectID)) > 0, 'Open', 'Closed') AS ProspectStatus,
  PS.DateCreated,
  PS.LastChangeDate,
  S1.OptionDescription AS Industry,
  S2.OptionDescription AS Seta,
  PS.PaymentMethod,
  S3.OptionDescription AS Origin,
  PS.OriginDetail,
  CS.FinNumber AS FinNumber,
  BR.BranchName AS LabourNetBranch,
  CI.GroupName AS IntroducingClient,
  U1.FullName AS IntroducingConsultant,
  U2.FullName AS SalesConsultant,
  U2.EmployeeCode AS SalesConsultantEmployeeCode,
  U2.TargetAmount AS SalesConsultantTarget,
  U3.FullName AS TechnicalSpecialist,
  IF(TRIM(CONCAT(PP.ProductSubGroup, '')) = '', CONCAT(PP.ProductCategory, ''),
          CONCAT(TRIM(CONCAT(PP.ProductCategory, '')), ' - ', TRIM(CONCAT(PP.ProductSubGroup, '')))) AS Product,
  PP.QuotedAmount,
  PP.DateQuoted,
  PP.DateExpected,
  PP.DateSigned,
  PP.LiveDate,
  S4.OptionDescription AS SalesPhase
FROM
  taProspect AS PS
    LEFT JOIN taProspectProduct AS PP ON (PP.ProspectID = PS.ProspectID)
    LEFT JOIN taBranches AS BR ON (BR.BranchID = PS.LabourNetBranchID)
    LEFT JOIN taClients AS CI ON (CI.ClientID = PS.IntroducingClientID)
    LEFT JOIN taClients AS CS ON (CS.ClientID = PS.ClientID)
    LEFT JOIN taUsers AS U1 ON (U1.UserID = PS.SalesReferrerID)
    LEFT JOIN taUsers AS U2 ON (U2.UserID = PP.SalesPersonID)
    LEFT JOIN taUsers AS U3 ON (U3.UserID = PP.SpecialistUserID)
    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-ORIGIN')      AND (S3.OptionID = PS.OriginID))
    LEFT JOIN taSystemOption AS S4 ON ((S4.OptionType = 'PROSPECT-SALES-PHASE') AND (S4.OptionID = PP.SalesPhaseID))
WHERE
  (PS.ClientID IS NOT NULL) AND
  (PS.ClientID > 0)
  @pBranchID
  @pProductCategory
  @pSalesPersonBranchID
  @pSalesPersonID
  @pSalesReferrerID
  @pOriginID
  @pSalesPhaseID
  @pFromTo

ORDER BY 1,2