USP_DATAFORMTEMPLATE_EDITLOAD_MKTACKNOWLEDGEMENTMAILINGTEMPLATE_2

The load procedure used by the edit dataform template "Marketing Acknowledgement 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
@MARKLETTERSACKNOWLEDGED	bit	INOUT	Mark letters 'Acknowledged/Receipted' when process completes
@ACKNOWLEDGEDATETYPECODE	tinyint	INOUT	Date
@ACKNOWLEDGEDATE	datetime	INOUT	Date
@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 marketing acknowledgements
@APPEALINFORMATION	xml	INOUT	Appeal information including the appeal searchlist and record source.
@MULTIPLELETTERSPERREVENUE	bit	INOUT	Allow multiple letters per revenue
@RUNACTIVATEANDEXPORT	bit	INOUT	Activate and export marketing acknowledgement when template processing completes
@EXPORTDESCRIPTION	nvarchar(255)	INOUT	Export description
@CODEVALUEID	uniqueidentifier	INOUT	Code value ID
@CACHESOURCEANALYSISRULEDATA	bit	INOUT	Capture source analysis rule data
@BASECURRENCYID	uniqueidentifier	INOUT	Base currency ID
@RUNMARKETINGEXCLUSIONSREPORT	bit	INOUT
@SEGMENTUSESADVANCEDNAMEFORMATOPTION	bit	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTACKNOWLEDGEMENTMAILINGTEMPLATE_2]
(
  @ID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @CODE nvarchar(10) = null output,
  @NAME nvarchar(50) = null output,
  @DESCRIPTION nvarchar(255) = null output,
  @MARKLETTERSACKNOWLEDGED bit = null output,
  @ACKNOWLEDGEDATETYPECODE tinyint = null output,
  @ACKNOWLEDGEDATE datetime = 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,
  @MULTIPLELETTERSPERREVENUE bit = null output,
  @RUNACTIVATEANDEXPORT bit = null output,
  @EXPORTDESCRIPTION nvarchar(255) = null output,
  @CODEVALUEID uniqueidentifier = null output,
  @CACHESOURCEANALYSISRULEDATA bit = null output,
  @BASECURRENCYID 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 = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[CODE],
    @CODEVALUEID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[PARTDEFINITIONVALUESID],
    @NAME = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[NAME],
    @DESCRIPTION = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[DESCRIPTION],
    @MARKLETTERSACKNOWLEDGED = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[MARKLETTERSACKNOWLEDGED],
    @ACKNOWLEDGEDATETYPECODE = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ACKNOWLEDGEDATETYPECODE],
    @ACKNOWLEDGEDATE = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ACKNOWLEDGEDATE],
    @SITEID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[SITEID],
    @SOURCECODEID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[SOURCECODEID],
    @ITEMLIST = dbo.[UFN_MKTACKNOWLEDGEMENTMAILINGTEMPLATESOURCECODEPART_GETITEMLIST2_TOITEMLISTXML]([MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ID]),
    @OWNERID = dbo.[UFN_BUSINESSPROCESSINSTANCE_GETOWNER]('1F72BAD2-F10A-4F43-9210-AB3B5CBBB576', [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ID]),
    @EXCLUSIONDATETYPECODE = [BUSINESSPROCESSCOMMPREF].[DATETYPECODE],
    @EXCLUSIONASOFDATE = [BUSINESSPROCESSCOMMPREF].[ASOFDATE],
    @EXCLUDEDECEASED = [BUSINESSPROCESSCOMMPREF].[EXCLUDEDECEASED],
    @EXCLUDEINACTIVE = [BUSINESSPROCESSCOMMPREF].[EXCLUDEINACTIVE],
    @EXCLUSIONS = dbo.[UFN_MKTSEGMENTATION_GETREQUIREDANDSELECTEDSOLICITCODEEXCLUSIONS_TOITEMLISTXML]([MKTACKNOWLEDGEMENTMAILINGPROCESS].[ID]),
    @USEADDRESSPROCESSING = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[USEADDRESSPROCESSING],
    @ADDRESSPROCESSINGOPTIONID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONID],
    @NAMEFORMATPARAMETERID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[NAMEFORMATPARAMETERID],
    @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
    @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
    @TSLONG = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[TSLONG],
    @MULTIPLELETTERSPERREVENUE = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[MULTIPLELETTERSPERREVENUE],
    @RUNACTIVATEANDEXPORT = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[RUNACTIVATEANDEXPORT],
    @EXPORTDESCRIPTION = [MKTACKNOWLEDGEMENTMAILINGTEMPLATEEXPORT].[DESCRIPTION],
    @CACHESOURCEANALYSISRULEDATA = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[CACHESOURCEANALYSISRULEDATA],
    @BASECURRENCYID = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[BASECURRENCYID],
    @RUNMARKETINGEXCLUSIONSREPORT = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[RUNMARKETINGEXCLUSIONSREPORT]
  from dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATE]
  inner join dbo.[MKTACKNOWLEDGEMENTMAILINGPROCESS] on [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ID]
  left outer join dbo.[BUSINESSPROCESSCOMMPREF] on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTACKNOWLEDGEMENTMAILINGPROCESS].[ID]
  left outer join dbo.[MKTACKNOWLEDGEMENTMAILINGTEMPLATEEXPORT] on [MKTACKNOWLEDGEMENTMAILINGTEMPLATEEXPORT].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ID]
  where [MKTACKNOWLEDGEMENTMAILINGTEMPLATE].[ID] = @ID;

  if @DATALOADED = 1
    begin
      set @SITEREQUIRED = dbo.[UFN_SITEREQUIREDFORUSERONFEATURE](@CURRENTAPPUSERID, '2E2E7D46-A882-480D-8F79-C12BFDC00914', 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_MKTACKNOWLEDGEMENTMAILINGTEMPLATEKPI_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],
          [MKTACKNOWLEDGEMENTMAILINGTEMPLATEAPPEAL].[APPEALSYSTEMID] as [APPEALSYSTEMID],
          [MKTACKNOWLEDGEMENTMAILINGTEMPLATEAPPEAL].[APPEALID] as [APPEALID],
          [MKTACKNOWLEDGEMENTMAILINGTEMPLATEAPPEAL].[APPEALDESCRIPTION] as [APPEALDESCRIPTION]
        from dbo.[MKTAPPEALRECORDSOURCE] 
        inner join [QUERYVIEWCATALOG] on [MKTAPPEALRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
        left join [MKTACKNOWLEDGEMENTMAILINGTEMPLATEAPPEAL] 
          on ([MKTACKNOWLEDGEMENTMAILINGTEMPLATEAPPEAL].[RECORDSOURCEID] = [MKTAPPEALRECORDSOURCE].[ID]
          and [MKTACKNOWLEDGEMENTMAILINGTEMPLATEAPPEAL].[ACKNOWLEDGEMENTTEMPLATEID] = @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;

      -- 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.[MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE]
                 inner join dbo.[NAMEFORMATPARAMETER] on [NAMEFORMATPARAMETER].[ID] = [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[NAMEFORMATPARAMETERID]
                 where 
                   [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[ACKNOWLEDGEMENTMAILINGTEMPLATEID] = @ID and
                   [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[OVERRIDEADDRESSPROCESSING] = 1 and
                   [MKTACKNOWLEDGEMENTMAILINGTEMPLATERULE].[USEADDRESSPROCESSING] = 1 and
                   ([NAMEFORMATPARAMETER].[JOINTEXCLUDESPOUSE] = 1 or [NAMEFORMATPARAMETER].[JOINTSPOUSESMAILEDSEPARATELY] = 1)
                 )
        set @SEGMENTUSESADVANCEDNAMEFORMATOPTION = 1;
      else
        set @SEGMENTUSESADVANCEDNAMEFORMATOPTION = 0;
    end

  return 0;