USP_DATAFORMTEMPLATE_PRELOAD_ADD_MKTACKNOWLEDGEMENTMAILINGTEMPLATE

The load procedure used by the edit dataform template "Marketing Acknowledgement Template Add Form"

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SOURCECODEID	uniqueidentifier	INOUT	Source code
@SMARTQUERIESEXIST	bit	INOUT	Smart queries exist?
@SITEREQUIRED	bit	INOUT	Site required?
@SITEID	uniqueidentifier	INOUT	Site
@ISBBEC	bit	INOUT	Is BBEC?
@EXCLUSIONS	xml	INOUT	Exclusions
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	INOUT	Address processing options
@NAMEFORMATPARAMETERID	uniqueidentifier	INOUT	Name format options
@CANUPDATEEXCLUDEDECEASED	bit	INOUT	Can update exclude deceased constituents?
@CANUPDATEEXCLUDEINACTIVE	bit	INOUT	Can update exclude inactive constituents?
@ACTIVATIONKPIS	xml	INOUT	Activation KPIs
@APPEALINFORMATION	xml	INOUT	Appeal information including the appeal searchlist and record source.
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_PRELOAD_ADD_MKTACKNOWLEDGEMENTMAILINGTEMPLATE]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @SOURCECODEID uniqueidentifier = null output,
  @SMARTQUERIESEXIST bit = null output,
  @SITEREQUIRED bit = null output,
  @SITEID uniqueidentifier = null output,
  @ISBBEC bit = null output,
  @EXCLUSIONS xml = null output,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null output,
  @NAMEFORMATPARAMETERID uniqueidentifier = null output,
  @CANUPDATEEXCLUDEDECEASED bit = null output,
  @CANUPDATEEXCLUDEINACTIVE bit = null output,
  @ACTIVATIONKPIS xml = null output,
  @APPEALINFORMATION xml = null output,
  @BASECURRENCYID uniqueidentifier = null output
)
as
  set nocount on;

  select top(1)
    @SOURCECODEID = [ID]
  from dbo.[MKTSOURCECODE]
  where dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [MKTSOURCECODE].[SITEID]) = 1
    and [ISDEFAULT] <> 0
    and [ISACTIVE] <> 0
    -- do not display empty source code layouts

    and exists (select top 1 1 from dbo.[MKTSOURCECODEITEM] where [MKTSOURCECODEITEM].[SOURCECODEID] = [MKTSOURCECODE].[ID]);

  set @SMARTQUERIESEXIST = dbo.[UFN_MKTSELECTION_SMARTQUERIESEXIST](1);

  set @SITEREQUIRED = dbo.[UFN_SITEREQUIREDFORUSERONFEATURE](@CURRENTAPPUSERID, '8932D5AA-5D63-49F4-9C45-685C3B889331', 1);
  set @SITEID = dbo.[UFN_APPUSER_DEFAULTSITEFORUSER](@CURRENTAPPUSERID);

  set @ISBBEC = (case when dbo.[UFN_INSTALLEDPRODUCTS_PRODUCTIS]('BB9873D7-F1ED-430A-8AB4-F09F47056538') = 0 then 1 else 0 end);

  set @EXCLUSIONS = dbo.[UFN_MKTSEGMENTATION_GETREQUIREDANDDEFAULTSOLICITCODEEXCLUSIONS_TOITEMLISTXML](@CURRENTAPPUSERID);

  set @BASECURRENCYID = dbo.[UFN_APPUSER_GETBASECURRENCY](@CURRENTAPPUSERID);

  set @CANUPDATEEXCLUDEDECEASED = 1;
  set @CANUPDATEEXCLUDEINACTIVE = 1;

  if dbo.[UFN_APPUSER_ISSYSADMIN](@CURRENTAPPUSERID) = 0
    begin
      set @CANUPDATEEXCLUDEDECEASED = dbo.[UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE](@CURRENTAPPUSERID, '2EEC593D-06B1-49E0-9031-A4076B07081C');
      set @CANUPDATEEXCLUDEINACTIVE = dbo.[UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'C0E02A6F-DF46-460C-ACA2-CC31C9C11BFC');
    end;

  select top 1 
    @ADDRESSPROCESSINGOPTIONID = [ID]
  from dbo.[ADDRESSPROCESSINGOPTION]
  where [ISDEFAULT] = 1 and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [SITEID]) = 1;

  select top 1
    @NAMEFORMATPARAMETERID = [ID]
  from dbo.[NAMEFORMATPARAMETER]
  where [ISDEFAULT] = 1 and dbo.[UFN_SITEALLOWEDFORUSER](@CURRENTAPPUSERID, [SITEID]) = 1;

  -- Load KPI values

  declare @KPITABLE as table
  (
    [KPICATALOGID] uniqueidentifier,
    [SELECTED] bit,
    [NAME] nvarchar(255),
    [GOALTYPECODE] tinyint,
    [DEFAULT] bit
  );

  insert into @KPITABLE
    exec dbo.[USP_MKTACKNOWLEDGEMENTMAILINGTEMPLATEKPI_GETFIELDS] null, 0, @CURRENTAPPUSERID;

  set @ACTIVATIONKPIS = (
    select 
      [KPICATALOGID],
      [SELECTED],
      [NAME],
      [GOALTYPECODE],
      [DEFAULT]
    from @KPITABLE 
  for xml raw('ITEM'), type, elements, root('ACTIVATIONKPIS'), binary base64);

  -- get appeal search catalog IDs with the record source name

  set @APPEALINFORMATION = (
    select 
      [MKTAPPEALRECORDSOURCE].[ID] as [RECORDSOURCEID],
      [QUERYVIEWCATALOG].[DISPLAYNAME] as [RECORDSOURCENAME],
      [MKTAPPEALRECORDSOURCE].[SEARCHLISTCATALOGID] as [SEARCHLISTCATALOGID],
      [MKTAPPEALRECORDSOURCE].[DESCRIPTIONFIELD] as [SEARCHLISTDESCRIPTIONFIELD]
    from dbo.[MKTAPPEALRECORDSOURCE] 
    inner join dbo.[QUERYVIEWCATALOG] on [MKTAPPEALRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
    where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([QUERYVIEWCATALOG].[ID]) = 1
    for xml raw('ITEM'), type, elements, root('APPEALINFORMATION'), binary base64);

  return 0;