UFN_QUERY_EXPORTDEFINITIONLIST

create function dbo.UFN_QUERY_EXPORTDEFINITIONLIST
(
  @CURRENTAPPUSERID uniqueidentifier,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
returns table
as
  return (
    with ISSYSADMIN (ISSYSADMIN) as (
      select ISSYSADMIN from dbo.APPUSER where ID = @CURRENTAPPUSERID
    ), EXPORTDEFINITIONS (ID, NAME, DESCRIPTION, RECORDTYPEID, RECORDTYPE, SITE, ADDEDBY, DATEADDED, CHANGEDBY, DATECHANGED, USERCANEDIT) as (
      select
        EXPORTDEFINITION.ID as ID,
        EXPORTDEFINITION.NAME as NAME,
        EXPORTDEFINITION.DESCRIPTION as DESCRIPTION,
        RECORDTYPE.ID as RECORDTYPEID,
        RECORDTYPE.NAME as RECORDTYPE,
        isnull(SITE.NAME, 'All sites') as SITE,
        ADDEDBY.USERNAME as ADDEDBY,
        EXPORTDEFINITION.DATEADDED as DATEADDED,
        CHANGEDBY.USERNAME as CHANGEDBY,
        EXPORTDEFINITION.DATECHANGED as DATECHANGED,
        cast(1 as bit) as USERCANEDIT
      from dbo.EXPORTDEFINITION
      cross apply ISSYSADMIN
      inner join dbo.UFN_EXPORTDEFINITION_APPLYQUERYVIEWSECURITY(@CURRENTAPPUSERID) as GRANTEDEXPORTDEFINITIONS on GRANTEDEXPORTDEFINITIONS.ID = EXPORTDEFINITION.ID
      inner join dbo.CHANGEAGENT as ADDEDBY on EXPORTDEFINITION.ADDEDBYID = ADDEDBY.ID
      inner join dbo.CHANGEAGENT as CHANGEDBY on EXPORTDEFINITION.CHANGEDBYID = CHANGEDBY.ID
      inner join dbo.RECORDTYPE on EXPORTDEFINITION.RECORDTYPEID = RECORDTYPE.ID
      left outer join dbo.SITE on EXPORTDEFINITION.SITEID = SITE.ID
      where
        EXPORTDEFINITION.ISSYSTEM = 0
        and
        (
          EXPORTDEFINITION.SITEID is null
          or
          ISSYSADMIN.ISSYSADMIN = 1
          or
          exists (select top 1 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) where SITEID = EXPORTDEFINITION.SITEID)
        )
        and
        (
          @SITEFILTERMODE = 0
          or
          EXPORTDEFINITION.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
        )
        -- hide marketing effort export record types

        and EXPORTDEFINITION.RECORDTYPEID not in (select ID from dbo.RECORDTYPE where NAME like '% Segment Member Export')
    ), COUNTS (ID, EXPORTS) as (
      select
        EXPORTPROCESS.EXPORTDEFINITIONID as ID,
        count(EXPORTPROCESS.ID) as EXPORTS
      from EXPORTDEFINITIONS
      cross apply ISSYSADMIN
      inner join dbo.EXPORTPROCESS on EXPORTDEFINITIONS.ID = EXPORTPROCESS.EXPORTDEFINITIONID
      left outer join dbo.BUSINESSPROCESSINSTANCE as BPI on EXPORTPROCESS.ID = BPI.BUSINESSPROCESSPARAMETERSETID
      where
        --Start Security Check

        (
          ISSYSADMIN.ISSYSADMIN = 1
          or
          (
            dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESSINSTANCE_IN_SYSTEMROLE(@CURRENTAPPUSERID, EXPORTPROCESS.ID) = 1
            and
            --Check site security

            (select count(*) from dbo.UFN_SITEID_MAPFROM_EXPORTPROCESSID(EXPORTPROCESS.ID) as EXPORTPROCESSSITE) > 0
          )
        )
        --End Security Check

        and
        --JamesWill WI242118 2012-11-13 Apply the user site filter in addition to doing security checks instead of as part of the security checks

        (
          @SITEFILTERMODE = 0
          or
          BPI.SITEID in (select SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED))
        )
      group by EXPORTPROCESS.EXPORTDEFINITIONID
    )
    select
      EXPORTDEFINITIONS.ID,
      EXPORTDEFINITIONS.NAME,
      EXPORTDEFINITIONS.DESCRIPTION,
      EXPORTDEFINITIONS.RECORDTYPEID,
      EXPORTDEFINITIONS.RECORDTYPE,
      EXPORTDEFINITIONS.SITE,
      EXPORTDEFINITIONS.ADDEDBY,
      EXPORTDEFINITIONS.DATEADDED,
      EXPORTDEFINITIONS.CHANGEDBY,
      EXPORTDEFINITIONS.DATECHANGED,
      EXPORTDEFINITIONS.USERCANEDIT,
      isnull(COUNTS.EXPORTS, 0) as EXPORTS
    from EXPORTDEFINITIONS
    left outer join COUNTS on COUNTS.ID = EXPORTDEFINITIONS.ID
);