USP_DATALIST_MKTCOMMUNICATIONTEMPLATE

CREATE procedure dbo.[USP_DATALIST_MKTCOMMUNICATIONTEMPLATE]
(
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @ACTIVESTATUS bit = null,
  @NAME nvarchar(100) = null,
  @TEMPLATETYPECODE tinyint = 0,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null
)
as
  set nocount on;

  select
    [MKTCOMMUNICATIONTEMPLATE].[ID],
    [MKTCOMMUNICATIONTEMPLATE].[NAME],
    [MKTCOMMUNICATIONTEMPLATE].[DESCRIPTION],
    [MKTCOMMUNICATIONTEMPLATE].[TEMPLATETYPECODE],
    [MKTCOMMUNICATIONTEMPLATE].[TEMPLATETYPE],
    [MKTCOMMUNICATIONTEMPLATE].[ISACTIVE],
    [SITE].[NAME],
    cast(case when (select count(1) from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL] where [MKTCOMMUNICATIONTEMPLATEDEFAULTAPPEAL].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]) > 0 then 1 else 0 end as bit) as [HASAPPEAL],
    cast(case when (select count(1) from dbo.[MKTCOMMUNICATIONTEMPLATEDEFAULTASKLADDEROVERRIDE] where [MKTCOMMUNICATIONTEMPLATEDEFAULTASKLADDEROVERRIDE].[COMMUNICATIONTEMPLATEID] = [MKTCOMMUNICATIONTEMPLATE].[ID]) > 0 then 1 else 0 end as bit) as [HASASKLADDEROVERRIDES]
  from dbo.[MKTCOMMUNICATIONTEMPLATE]
  left join dbo.[SITE] on [SITE].[ID] = [MKTCOMMUNICATIONTEMPLATE].[SITEID]
  where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[MKTCOMMUNICATIONTEMPLATE].[SITEID] or (SITEID is null and [MKTCOMMUNICATIONTEMPLATE].[SITEID] is null)))
  and (@SITEFILTERMODE = 0 or [MKTCOMMUNICATIONTEMPLATE].[SITEID] in (select [SITEID] from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)))
  and (@ACTIVESTATUS is null or @ACTIVESTATUS = [MKTCOMMUNICATIONTEMPLATE].[ISACTIVE])
  and (@TEMPLATETYPECODE is null or @TEMPLATETYPECODE = [MKTCOMMUNICATIONTEMPLATE].[TEMPLATETYPECODE])
  and (@NAME is null or [MKTCOMMUNICATIONTEMPLATE].[NAME] like '%' + isnull(@NAME, '') + '%')
  and [MKTCOMMUNICATIONTEMPLATE].[MKTSEGMENTATIONID] is null and [MKTCOMMUNICATIONTEMPLATE].[PARENTCOMMUNICATIONTEMPLATEID] is null
  order by [MKTCOMMUNICATIONTEMPLATE].[NAME];

  return 0;