USP_DATALIST_EXPORTDEFINITION

Returns a list of a Export Definitions.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@SECURITYFEATUREID	uniqueidentifier	IN	Input parameter indicating the ID of the feature to use for site security checking.
@SECURITYFEATURETYPE	tinyint	IN	Input parameter indicating the type of the feature to use for site security checking.
@RECORDTYPEID	uniqueidentifier	IN	Record type

Definition

Copy



CREATE procedure dbo.USP_DATALIST_EXPORTDEFINITION
(
  @CURRENTAPPUSERID uniqueidentifier,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null,
  @RECORDTYPEID uniqueidentifier = null
)
as begin
  set nocount on;

  select
    EXPORTDEFINITION.ID,
    EXPORTDEFINITION.NAME,
    EXPORTDEFINITION.DESCRIPTION,
    ADDEDBY.USERNAME as ADDEDBY,
    CHANGEDBY.USERNAME as CHANGEDBY,
    EXPORTDEFINITION.DATEADDED,
    EXPORTDEFINITION.DATECHANGED, 
    cast(1 as bit) as USERCANEDIT,
    RECORDTYPE.NAME RECORDTYPE,
    isnull(SITE.NAME, 'All sites') as SITE,
    EXPORTDEFINITION.QUERYVIEWCATALOGID as QUERYVIEWCATALOGID
  from dbo.EXPORTDEFINITION
  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 join dbo.SITE on EXPORTDEFINITION.SITEID = SITE.ID
  where (@RECORDTYPEID is null or RECORDTYPE.ID = @RECORDTYPEID)
  and EXPORTDEFINITION.ISSYSTEM = 0
  and
  (
    EXPORTDEFINITION.SITEID is null
    or
    (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[EXPORTDEFINITION].[SITEID] or (SITEID is null and [EXPORTDEFINITION].[SITEID] is null)))
  )
  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')
  order by NAME;

  return 0;
end