UFN_QUERY_MKTCOMMUNICATIONNAMESCHEME

CREATE function dbo.[UFN_QUERY_MKTCOMMUNICATIONNAMESCHEME]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null
)
returns table as
return 
(

  select
    [MKTCOMMUNICATIONNAMESCHEME].[ID],
    [MKTCOMMUNICATIONNAMESCHEME].[NAME],
    dbo.[UFN_MKTCOMMUNICATIONNAMESCHEME_GETCURRENTNAMESCHEMETEXT]([MKTCOMMUNICATIONNAMESCHEME].[ID]) as [CURRENTNAMESCHEME],
    [MKTCOMMUNICATIONNAMESCHEME].[ISACTIVE],
    [SITE].[NAME] as [SITE]

    /*#EXTENSION*/

  from dbo.[MKTCOMMUNICATIONNAMESCHEME]
  left join dbo.[SITE] on [SITE].[ID] = [MKTCOMMUNICATIONNAMESCHEME].[SITEID]
  where 
    [MKTCOMMUNICATIONNAMESCHEME].[PARENTCOMMUNICATIONNAMESCHEMEID] is null and [MKTCOMMUNICATIONNAMESCHEME].[MKTSEGMENTATIONID] is null

    /* Site security */
    and ((select [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @CURRENTAPPUSERID) = 1
       or exists (select top 1 1 
                  from dbo.[UFN_SITESFORUSERONFEATURE](@CURRENTAPPUSERID, '303AE852-1A56-48A5-9F0B-302ACB69661B', 10)
                  where [SITEID] = [MKTCOMMUNICATIONNAMESCHEME].[SITEID] or ([SITEID] is null and [MKTCOMMUNICATIONNAMESCHEME].[SITEID] is null)))

    and
    (
      @SITEFILTERMODE = 0
      or [MKTCOMMUNICATIONNAMESCHEME].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER] (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
    )
)