USP_DATALIST_MKTLETTERCODE

Returns a list of letters.

Parameters

Parameter	Parameter Type	Mode	Description
@INCLUDEINACTIVE	bit	IN	Include inactive letters
@MAILINGTYPECODE	tinyint	IN	Type
@SITEFILTERMODE	tinyint	IN	Sites
@SITESSELECTED	xml	IN
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@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.

Definition

Copy



CREATE procedure dbo.[USP_DATALIST_MKTLETTERCODE]
(
  @INCLUDEINACTIVE bit = 0,
  @MAILINGTYPECODE tinyint = 255,
  @SITEFILTERMODE tinyint = 0,
  @SITESSELECTED xml = null,
  @CURRENTAPPUSERID uniqueidentifier = null,
  @SECURITYFEATUREID uniqueidentifier = null,
  @SECURITYFEATURETYPE tinyint = null
)
as
  set nocount on;

  select
    [LETTERCODE].[ID],
    [LETTERCODE].[NAME],
    [LETTERCODE].[DESCRIPTION],
    [LETTERCODE].[WORDTEMPLATEFILENAME],
    case dbo.[UFN_MKTEXPORTDEFINITION_GETMAILINGTYPECODEFROMRECORDTYPEID]([EXPORTDEFINITION].[RECORDTYPEID])
      when 0 then 'Direct marketing'
      when 1 then 'Marketing acknowledgement'
      when 2 then 'Membership'
      when 3 then 'Sponsorship'
      else '' end as [MAILINGTYPE],
    [EXPORTDEFINITION].[NAME],
    case [LETTERCODE].[ISACTIVE] when 1 then 'Active' else 'Inactive' end as [STATUS],
    [LETTERCODE].[WORDTEMPLATEFILENAME],
    'WORDFILE',
    '33B074F0-9EDF-4EB2-B21F-3338C4522819' as [VIEWFORMID],
    [LETTERCODE].[INCLUDESRECEIPT],
    [LETTERCODE].[ISACTIVE] [ISACTIVE],
    stuff(
           (
             select ', ' + [SITE].[NAME]
             from dbo.[SITE]
             inner join dbo.[UFN_SITEID_MAPFROM_LETTERCODEID]([LETTERCODE].[ID]) as [LETTERCODESITES] on [LETTERCODESITES].[SITEID] = [SITE].[ID]
             order by [SITE].[NAME]
             for xml path(''), type
           ).value('.', 'varchar(max)')
           , 1, 2, ''
         ) as [SITES]
  from dbo.[LETTERCODE]
  inner join dbo.[EXPORTDEFINITION] on [EXPORTDEFINITION].[ID] = [LETTERCODE].[EXPORTDEFINITIONID]
  left outer join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [EXPORTDEFINITION].[RECORDTYPEID]
  where (@INCLUDEINACTIVE = 1 or (@INCLUDEINACTIVE = 0 and [LETTERCODE].[ISACTIVE] = 1))
  and (@MAILINGTYPECODE is null or @MAILINGTYPECODE = 255 or dbo.[UFN_MKTEXPORTDEFINITION_GETMAILINGTYPECODEFROMRECORDTYPEID]([EXPORTDEFINITION].[RECORDTYPEID]) = @MAILINGTYPECODE)
  and [LETTERCODE].[ISSYSTEM] = 0
  and (
      select count(1) 
      from dbo.[UFN_SITEID_MAPFROM_LETTERCODEID]([LETTERCODE].[ID]) [LETTERCODESITES]
      where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[LETTERCODESITES].[SITEID] or (SITEID is null and [LETTERCODESITES].[SITEID] is null)))
    ) > 0
    and (
      @SITEFILTERMODE = 0
        or exists(
          select 1
          from dbo.[UFN_SITE_BUILDDATALISTSITEFILTER](@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) [SITEFILTER]
            inner join dbo.[UFN_SITEID_MAPFROM_LETTERCODEID]([LETTERCODE].[ID]) [LETTERCODESITES] on [LETTERCODESITES].[SITEID] = [SITEFILTER].[SITEID]
        )
    )
    and (dbo.[UFN_MKTEXPORTDEFINITION_GETMAILINGTYPECODEFROMRECORDTYPE]([RECORDTYPE].[NAME]) in (0, 1, 2, 3))
  order by [LETTERCODE].[NAME];

  return 0;