USP_DATAFORMTEMPLATE_EDITLOAD_MKTSPONSORSHIPMAILINGTEMPLATE_2

The load procedure used by the edit dataform template "Sponsorship Effort Template Edit Form 2"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@CODE	nvarchar(10)	INOUT	Code
@NAME	nvarchar(50)	INOUT	Name
@DESCRIPTION	nvarchar(255)	INOUT	Description
@SITEID	uniqueidentifier	INOUT	Site
@SOURCECODEID	uniqueidentifier	INOUT	Source code
@ITEMLIST	xml	INOUT	Items
@SITEREQUIRED	bit	INOUT	Site required?
@SITECANBECHANGED	bit	INOUT	Site can be changed?
@ISBBEC	bit	INOUT	Is BBEC?
@OWNERID	uniqueidentifier	INOUT	Owner ID
@EXCLUSIONDATETYPECODE	tinyint	INOUT	Consider exclusions as of
@EXCLUSIONASOFDATE	datetime	INOUT	Consider exclusions as of
@EXCLUDEDECEASED	bit	INOUT	Exclude deceased constituents
@EXCLUDEINACTIVE	bit	INOUT	Exclude inactive constituents
@EXCLUSIONS	xml	INOUT	Exclusions
@USEADDRESSPROCESSING	bit	INOUT	Use address processing?
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	INOUT	Address processing options
@NAMEFORMATPARAMETERID	uniqueidentifier	INOUT	Name format options
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE	tinyint	INOUT	Consider seasonal addresses as of
@ADDRESSPROCESSINGOPTIONSEASONALASOFDATE	datetime	INOUT	Consider seasonal addresses as of
@CANUPDATEEXCLUDEDECEASED	bit	INOUT	Can update exclude deceased constituents?
@CANUPDATEEXCLUDEINACTIVE	bit	INOUT	Can update exclude inactive constituents?
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@ACTIVATIONKPIS	xml	INOUT	Activation KPIs
@USEKPISASDEFAULT	bit	INOUT	Use the chosen KPIs as the default for future sponsorship efforts
@APPEALINFORMATION	xml	INOUT	Appeal information including the appeal searchlist and record source.
@RUNACTIVATEANDEXPORT	bit	INOUT	Activate and export sponsorship effort when template processing completes
@EXPORTDESCRIPTION	nvarchar(255)	INOUT	Export description
@MAILEXPORTDEFINITIONID	uniqueidentifier	INOUT	Mail export definition
@EMAILEXPORTDEFINITIONID	uniqueidentifier	INOUT	Email export definition
@PHONEEXPORTDEFINITIONID	uniqueidentifier	INOUT	Phone export definition
@CODEVALUEID	uniqueidentifier	INOUT	Code value ID
@RUNSEGMENTATIONSEGMENTREFRESHPROCESS	bit	INOUT	Refresh segment selections and filters
@CACHESOURCEANALYSISRULEDATA	bit	INOUT	Capture source analysis rule data
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@EXPORTDEFINITIONRECORDTYPE	nvarchar(50)	INOUT	Export definition record type
@EXPORTDEFINITIONRECORDTYPEID	uniqueidentifier	INOUT	Export definition record type ID
@EXPORTDEFINITIONQUERYVIEWID	uniqueidentifier	INOUT	Export definition query view ID
@RUNMARKETINGEXCLUSIONSREPORT	bit	INOUT
@SEGMENTUSESADVANCEDNAMEFORMATOPTION	bit	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSPONSORSHIPMAILINGTEMPLATE_2]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @CODE nvarchar(10) = null output,
  @NAME nvarchar(50) = null output,
  @DESCRIPTION nvarchar(255) = null output,
  @SITEID uniqueidentifier = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @ITEMLIST xml = null output,
  @SITEREQUIRED bit = null output,
  @SITECANBECHANGED bit = null output,
  @ISBBEC bit = null output,
  @OWNERID uniqueidentifier = null output,
  @EXCLUSIONDATETYPECODE tinyint = null output,
  @EXCLUSIONASOFDATE datetime = null output,
  @EXCLUDEDECEASED bit = null output,
  @EXCLUDEINACTIVE bit = null output,
  @EXCLUSIONS xml = null output,
  @USEADDRESSPROCESSING bit = null output,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null output,
  @NAMEFORMATPARAMETERID uniqueidentifier = null output,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE tinyint = null output,
  @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE datetime = null output,
  @CANUPDATEEXCLUDEDECEASED bit = null output,
  @CANUPDATEEXCLUDEINACTIVE bit = null output,
  @TSLONG bigint = 0 output,
  @ACTIVATIONKPIS xml = null output,
  @USEKPISASDEFAULT bit = null output,
  @APPEALINFORMATION xml = null output,
  @RUNACTIVATEANDEXPORT bit = null output,
  @EXPORTDESCRIPTION nvarchar(255) = null output,
  @MAILEXPORTDEFINITIONID uniqueidentifier = null output,
  @EMAILEXPORTDEFINITIONID uniqueidentifier = null output,
  @PHONEEXPORTDEFINITIONID uniqueidentifier = null output,
  @CODEVALUEID uniqueidentifier = null output,
  @RUNSEGMENTATIONSEGMENTREFRESHPROCESS bit = null output,
  @CACHESOURCEANALYSISRULEDATA bit = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @EXPORTDEFINITIONRECORDTYPE nvarchar(50) = null output,
  @EXPORTDEFINITIONRECORDTYPEID uniqueidentifier = null output,
  @EXPORTDEFINITIONQUERYVIEWID uniqueidentifier = null output,
  @RUNMARKETINGEXCLUSIONSREPORT bit = null output,
  @SEGMENTUSESADVANCEDNAMEFORMATOPTION bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;
  set @TSLONG = 0;

  select
    @DATALOADED = 1,
    @CODE = [MKTSPONSORSHIPMAILINGTEMPLATE].[CODE],
    @CODEVALUEID = [MKTSPONSORSHIPMAILINGTEMPLATE].[PARTDEFINITIONVALUESID],
    @NAME = [MKTSPONSORSHIPMAILINGTEMPLATE].[NAME],
    @DESCRIPTION = [MKTSPONSORSHIPMAILINGTEMPLATE].[DESCRIPTION],
    @SITEID = [MKTSPONSORSHIPMAILINGTEMPLATE].[SITEID],
    @SOURCECODEID = [MKTSPONSORSHIPMAILINGTEMPLATE].[SOURCECODEID],
    @ITEMLIST = dbo.[UFN_MKTSPONSORSHIPMAILINGTEMPLATESOURCECODEPART_GETITEMLIST2_TOITEMLISTXML]([MKTSPONSORSHIPMAILINGTEMPLATE].[ID]),
    @OWNERID = dbo.[UFN_BUSINESSPROCESSINSTANCE_GETOWNER]('1F72BAD2-F10A-4F43-9210-AB3B5CBBB576', [MKTSPONSORSHIPMAILINGPROCESS].[ID]),
    @EXCLUSIONDATETYPECODE = [BUSINESSPROCESSCOMMPREF].[DATETYPECODE],
    @EXCLUSIONASOFDATE = [BUSINESSPROCESSCOMMPREF].[ASOFDATE],
    @EXCLUDEDECEASED = [BUSINESSPROCESSCOMMPREF].[EXCLUDEDECEASED],
    @EXCLUDEINACTIVE = [BUSINESSPROCESSCOMMPREF].[EXCLUDEINACTIVE],
    @EXCLUSIONS = dbo.[UFN_MKTSEGMENTATION_GETREQUIREDANDSELECTEDSOLICITCODEEXCLUSIONS_TOITEMLISTXML]([MKTSPONSORSHIPMAILINGPROCESS].[ID]),
    @USEADDRESSPROCESSING = [MKTSPONSORSHIPMAILINGTEMPLATE].[USEADDRESSPROCESSING],
    @ADDRESSPROCESSINGOPTIONID = [MKTSPONSORSHIPMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONID],
    @NAMEFORMATPARAMETERID = [MKTSPONSORSHIPMAILINGTEMPLATE].[NAMEFORMATPARAMETERID],
    @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [MKTSPONSORSHIPMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
    @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [MKTSPONSORSHIPMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
    @TSLONG = [MKTSPONSORSHIPMAILINGTEMPLATE].[TSLONG],
    @RUNACTIVATEANDEXPORT = [MKTSPONSORSHIPMAILINGTEMPLATE].[RUNACTIVATEANDEXPORT],
    @EXPORTDESCRIPTION = [MKTSPONSORSHIPMAILINGTEMPLATEEXPORT].[DESCRIPTION],
    @MAILEXPORTDEFINITIONID = [MKTSPONSORSHIPMAILINGTEMPLATEEXPORT].[MAILEXPORTDEFINITIONID],
    @EMAILEXPORTDEFINITIONID = [MKTSPONSORSHIPMAILINGTEMPLATEEXPORT].[EMAILEXPORTDEFINITIONID],
    @PHONEEXPORTDEFINITIONID = [MKTSPONSORSHIPMAILINGTEMPLATEEXPORT].[PHONEEXPORTDEFINITIONID],
    @RUNSEGMENTATIONSEGMENTREFRESHPROCESS = [MKTSPONSORSHIPMAILINGTEMPLATE].[RUNSEGMENTATIONSEGMENTREFRESHPROCESS],
    @CACHESOURCEANALYSISRULEDATA = [MKTSPONSORSHIPMAILINGTEMPLATE].[CACHESOURCEANALYSISRULEDATA],
    @BASECURRENCYID = [MKTSPONSORSHIPMAILINGTEMPLATE].[BASECURRENCYID],
    @RUNMARKETINGEXCLUSIONSREPORT = [MKTSPONSORSHIPMAILINGTEMPLATE].[RUNMARKETINGEXCLUSIONSREPORT]
  from dbo.[MKTSPONSORSHIPMAILINGTEMPLATE]
  inner join dbo.[MKTSPONSORSHIPMAILINGPROCESS] on [MKTSPONSORSHIPMAILINGPROCESS].[SPONSORSHIPMAILINGTEMPLATEID] = [MKTSPONSORSHIPMAILINGTEMPLATE].[ID]
  left outer join dbo.[BUSINESSPROCESSCOMMPREF] on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTSPONSORSHIPMAILINGPROCESS].[ID]
  left outer join dbo.[MKTSPONSORSHIPMAILINGTEMPLATEEXPORT] on [MKTSPONSORSHIPMAILINGTEMPLATEEXPORT].[SPONSORSHIPMAILINGTEMPLATEID] = [MKTSPONSORSHIPMAILINGTEMPLATE].[ID]
  where [MKTSPONSORSHIPMAILINGTEMPLATE].[ID] = @ID;

  set @SITEREQUIRED = dbo.[UFN_SITEREQUIREDFORUSERONFEATURE](@CURRENTAPPUSERID, 'e11a6ae6-7e66-4dad-8fe5-af32917c6bfe', 1);
  set @SITECANBECHANGED = 1; -- obsolete


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

  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;

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

  insert into @KPITABLE
    exec dbo.[USP_MKTSPONSORSHIPMAILINGTEMPLATEKPI_GETFIELDS] @ID, 1, @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],
      [MKTSPONSORSHIPMAILINGTEMPLATEAPPEAL].[APPEALSYSTEMID] as [APPEALSYSTEMID],
      [MKTSPONSORSHIPMAILINGTEMPLATEAPPEAL].[APPEALID] as [APPEALID],
      [MKTSPONSORSHIPMAILINGTEMPLATEAPPEAL].[APPEALDESCRIPTION] as [APPEALDESCRIPTION]
    from dbo.[MKTAPPEALRECORDSOURCE] 
    inner join [QUERYVIEWCATALOG] on [MKTAPPEALRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
    left join [MKTSPONSORSHIPMAILINGTEMPLATEAPPEAL] 
      on ([MKTSPONSORSHIPMAILINGTEMPLATEAPPEAL].[RECORDSOURCEID] = [MKTAPPEALRECORDSOURCE].[ID]
      and [MKTSPONSORSHIPMAILINGTEMPLATEAPPEAL].[SPONSORSHIPMAILINGTEMPLATEID] = @ID)
    where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([QUERYVIEWCATALOG].[ID]) = 1
    for xml raw('ITEM'), type, elements, root('APPEALINFORMATION'), binary base64);

  --Set this value

  set @USEKPISASDEFAULT = 1;

  set @EXPORTDEFINITIONRECORDTYPE = dbo.[UFN_MKTEXPORTDEFINITION_GETRECORDTYPEFROMMAILINGTYPECODE](3);
  set @EXPORTDEFINITIONRECORDTYPEID = dbo.[UFN_MKTEXPORTDEFINITION_GETRECORDTYPEIDFROMMAILINGTYPECODE](3);
  set @EXPORTDEFINITIONQUERYVIEWID = dbo.[UFN_MKTEXPORTDEFINITION_GETQUERYVIEWIDFROMMAILINGTYPECODE](3);

  -- See if any rules override the address processing option with a name format that contains an advanced spouse option.

  if exists (select top 1 1
              from dbo.[MKTSPONSORSHIPMAILINGTEMPLATERULE]
              inner join dbo.[NAMEFORMATPARAMETER] on [NAMEFORMATPARAMETER].[ID] = [MKTSPONSORSHIPMAILINGTEMPLATERULE].[NAMEFORMATPARAMETERID]
              where 
                [MKTSPONSORSHIPMAILINGTEMPLATERULE].[SPONSORSHIPMAILINGTEMPLATEID] = @ID and
                [MKTSPONSORSHIPMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 and
                [MKTSPONSORSHIPMAILINGTEMPLATERULE].[USEADDRESSPROCESSING] = 1 and
                ([NAMEFORMATPARAMETER].[JOINTEXCLUDESPOUSE] = 1 or [NAMEFORMATPARAMETER].[JOINTSPOUSESMAILEDSEPARATELY] = 1)
              )
    set @SEGMENTUSESADVANCEDNAMEFORMATOPTION = 1;
  else
    set @SEGMENTUSESADVANCEDNAMEFORMATOPTION = 0;

  return 0;