UFN_MKTSPONSORSHIPMAILINGTEMPLATESEED_GETSEEDS_2

create function dbo.[UFN_MKTSPONSORSHIPMAILINGTEMPLATESEED_GETSEEDS_2]
(
  @SPONSORSHIPMAILINGTEMPLATEID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @SELECTEDONLY bit = 0,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint
)
returns table
as
  return 
  (
    select 
      [MKTSEED].[ID],
      (case when [MKTSPONSORSHIPMAILINGTEMPLATESEED].[ID] is null then 0 else 1 end) as [SELECTED],
      [MKTSEED].[INACTIVE],
      [TITLECODE].[DESCRIPTION] as [TITLE],
      [MKTSEED].[FIRSTNAME],
      [MKTSEED].[MIDDLENAME],
      [MKTSEED].[LASTNAME],
      [SUFFIXCODE].[DESCRIPTION] as [SUFFIX],
      [COUNTRY].[ABBREVIATION] as [COUNTRY],
      [MKTSEED].[ADDRESSBLOCK],
      [MKTSEED].[CITY],
      [STATE].[ABBREVIATION] as [STATE],
      [MKTSEED].[POSTCODE],
      [MKTSEED].[CART],
      [MKTSEED].[DPC],
      [MKTSEED].[LOT],
      [MKTSEED].[PHONENUMBER],
      [MKTSEED].[EMAILADDRESS],
      dbo.[UFN_TRANSLATIONFUNCTION_SITE_GETNAME]([MKTSEED].[SITEID]) as [SITE]
    from dbo.[MKTSEED]
    left outer join dbo.[MKTSPONSORSHIPMAILINGTEMPLATESEED] on dbo.[MKTSPONSORSHIPMAILINGTEMPLATESEED].[SEEDID] = dbo.[MKTSEED].[ID] and [MKTSPONSORSHIPMAILINGTEMPLATESEED].[SPONSORSHIPMAILINGTEMPLATEID] = @SPONSORSHIPMAILINGTEMPLATEID
    left outer join dbo.[TITLECODE] on [MKTSEED].[TITLECODEID] = [TITLECODE].[ID]
    left outer join dbo.[SUFFIXCODE] on [MKTSEED].[SUFFIXCODEID] = [SUFFIXCODE].[ID]
    left outer join dbo.[COUNTRY] on [MKTSEED].[COUNTRYID] = [COUNTRY].[ID]
    left outer join dbo.[STATE] on [MKTSEED].[STATEID] = [STATE].[ID]
    where ([MKTSPONSORSHIPMAILINGTEMPLATESEED].[ID] is not null or @SELECTEDONLY = 0)
    and ([MKTSPONSORSHIPMAILINGTEMPLATESEED].[ID] is not null or [MKTSEED].[INACTIVE] = 0)
    and ([MKTSPONSORSHIPMAILINGTEMPLATESEED].[ID] is not null or
        ( -- check site security

          select count(*) 
          from
            (select [SITEID]
              from dbo.[MKTSEED] as [SEEDSITE]
              where [SEEDSITE].[ID] = [MKTSEED].[ID]) 
          as [SEEDSITE]
          where (dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID) = 1 or exists (select top 1 1 from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) where [SITEID] = [SEEDSITE].[SITEID] or ([SITEID] is null and [SEEDSITE].[SITEID] is null)))
        ) > 0
    )
  );