USP_DATALIST_IMPORTFILETEMPLATES

CREATE procedure dbo.USP_DATALIST_IMPORTFILETEMPLATES
(
  @CURRENTAPPUSERID uniqueidentifier,
    @SITEFILTERMODE tinyint = 0,
    @SITESSELECTED xml = null,
    @SECURITYFEATUREID uniqueidentifier = null,
    @SECURITYFEATURETYPE tinyint = null
)
with execute as caller
as
    set nocount on;

    select 
    IMPORTFILETEMPLATE.ID,
        IMPORTFILETEMPLATE.NAME,
        BATCHTEMPLATE.NAME as BATCHTEMPLATE,
    0,    
    (select count(ID) from dbo.IMPORTPROCESS where IMPORTPROCESS.DELIMITEDFILETEMPLATEID = IMPORTFILETEMPLATE.ID) as INSTANCECOUNT,        
    isnull(SITE.NAME,'All sites') as SITE,
    0
    from 
    dbo.IMPORTFILETEMPLATE 
    inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.ID = IMPORTFILETEMPLATE.BATCHTEMPLATEID
    left join dbo.SITE on SITE.ID = IMPORTFILETEMPLATE.SITEID
  where
    (
            (--Check site security

                select count(*) 
                from dbo.UFN_SITEID_MAPFROM_IMPORTDELIMITEDFILETEMPLATE(IMPORTFILETEMPLATE.ID) as TEMPLATETABLE 
                where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[TEMPLATETABLE].[SITEID] or (SITEID is null and [TEMPLATETABLE].[SITEID] is null)))
            ) > 0
        )
        and (
            @SITEFILTERMODE = 0
            or 
      IMPORTFILETEMPLATE.SITEID in ( 
                select 
          SITEID
                from 
          dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
            )
        )
  union
  select 
    IMPORTFIXEDWIDTHFILETEMPLATE.ID,
        IMPORTFIXEDWIDTHFILETEMPLATE.NAME,
        BATCHTEMPLATE.NAME as BATCHTEMPLATE,
    1,    
    (select count(ID) from dbo.IMPORTPROCESS where IMPORTPROCESS.FIXEDWIDTHFILETEMPLATEID = IMPORTFIXEDWIDTHFILETEMPLATE.ID) as INSTANCECOUNT,        
    isnull(SITE.NAME,'All sites') as SITE,
    1
    from 
    dbo.IMPORTFIXEDWIDTHFILETEMPLATE 
    inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.ID = IMPORTFIXEDWIDTHFILETEMPLATE.BATCHTEMPLATEID
    left join dbo.SITE on SITE.ID = IMPORTFIXEDWIDTHFILETEMPLATE.SITEID
  where
    (
            (--Check site security

                select count(*) 
                from dbo.UFN_SITEID_MAPFROM_IMPORTFIXEDWIDTHFILETEMPLATE(IMPORTFIXEDWIDTHFILETEMPLATE.ID) as TEMPLATETABLE 
                where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[TEMPLATETABLE].[SITEID] or (SITEID is null and [TEMPLATETABLE].[SITEID] is null)))
            ) > 0
        )
        and (
            @SITEFILTERMODE = 0
            or 
      IMPORTFIXEDWIDTHFILETEMPLATE.SITEID in ( 
                select 
          SITEID
                from 
          dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
            )
        )
  order by
    2