USP_DATAFORMTEMPLATE_ADD_MKTTESTSEGMENTATION_PRELOAD

The load procedure used by the edit dataform template "Test Marketing Effort Add Form"

Parameters

Parameter	Parameter Type	Mode	Description
@SEGMENTATIONID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@NAME	nvarchar(100)	INOUT	Name
@DESCRIPTION	nvarchar(255)	INOUT	Description
@CODE	nvarchar(10)	INOUT	Code
@SAMPLESIZE	int	INOUT	Sample size
@SAMPLESIZETYPECODE	tinyint	INOUT	Sample size type
@SOURCECODEID	uniqueidentifier	INOUT	Source code
@ITEMLIST	xml	INOUT	Items
@MAILDATE	datetime	INOUT	Date
@HOUSEHOLDINGTYPECODE	tinyint	INOUT	Include
@ENABLEHOUSEHOLDING	bit	INOUT	Enable householding?
@ISBBEC	bit	INOUT	Is BBEC?
@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?
@ACTIVATIONKPIS	xml	INOUT	Activation KPIs
@APPEALINFORMATION	xml	INOUT	Appeal information including the appeal searchlist and record source.
@RUNACTIVATEANDEXPORT	bit	INOUT	Export marketing effort when activation 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
@HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD	bit	INOUT	Also include qualifying individuals who are not members of any household
@HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS	bit	INOUT	Also include qualifying households which do not have any members
@HOUSEHOLDINGONERECORDPERHOUSEHOLD	bit	INOUT	Send to one person per household
@CODEVALUEID	uniqueidentifier	INOUT	Code value ID
@RUNSEGMENTATIONSEGMENTREFRESHPROCESS	bit	INOUT	Refresh segment selections and filters
@CACHESOURCEANALYSISRULEDATA	bit	INOUT	Capture source analysis rule data
@OVERRIDEBUSINESSUNITS	bit	INOUT	Override appeal business units
@BUSINESSUNITS	xml	INOUT	Business units
@MAILINGTYPECODE	tinyint	INOUT	Mailing type code
@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

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_ADD_MKTTESTSEGMENTATION_PRELOAD]
(
  @SEGMENTATIONID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier,
  @NAME nvarchar(100) = null output,
  @DESCRIPTION nvarchar(255) = null output,
  @CODE nvarchar(10) = null output,
  @SAMPLESIZE int = null output,
  @SAMPLESIZETYPECODE tinyint = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @ITEMLIST xml = null output,
  @MAILDATE datetime = null output,
  @HOUSEHOLDINGTYPECODE tinyint = null output,
  @ENABLEHOUSEHOLDING bit = null output,
  @ISBBEC bit = 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,
  @ACTIVATIONKPIS xml = 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,
  @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD bit = null output,
  @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS bit = null output,
  @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit = null output,
  @CODEVALUEID uniqueidentifier = null output,
  @RUNSEGMENTATIONSEGMENTREFRESHPROCESS bit = null output,
  @CACHESOURCEANALYSISRULEDATA bit = null output,
  @OVERRIDEBUSINESSUNITS bit = null output,
  @BUSINESSUNITS xml = null output,
  @MAILINGTYPECODE tinyint = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @EXPORTDEFINITIONRECORDTYPE nvarchar(50) = null output,
  @EXPORTDEFINITIONRECORDTYPEID uniqueidentifier = null output,
  @EXPORTDEFINITIONQUERYVIEWID uniqueidentifier = null output
)
as
  set nocount on;

  --Check if the mailing is currently being activated...

  declare @R int;
  exec @R = dbo.[USP_MKTSEGMENTATION_CHECKACTIVATION] @SEGMENTATIONID;
  if @R <> 0
    return 1;

  select
    @NAME = [MKTSEGMENTATION].[NAME],
    @DESCRIPTION = [MKTSEGMENTATION].[DESCRIPTION],
    @CODE = [MKTSEGMENTATION].[CODE],
    @CODEVALUEID = [MKTSEGMENTATION].[PARTDEFINITIONVALUESID],
    @SAMPLESIZE = 5,
    @SAMPLESIZETYPECODE = 0,
    @SOURCECODEID = [MKTSEGMENTATION].[SOURCECODEID],
    @ITEMLIST = dbo.[UFN_MKTSOURCECODEPART_GETITEMLIST2_TOITEMLISTXML]([MKTSEGMENTATION].[ID]),
    @MAILDATE = [MKTSEGMENTATION].[MAILDATE],
    @HOUSEHOLDINGTYPECODE = [MKTSEGMENTATION].[HOUSEHOLDINGTYPECODE],
    @HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD = [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEINDIVIDUALSWITHNOHOUSEHOLD],
    @HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS = [MKTSEGMENTATION].[HOUSEHOLDINGINCLUDEHOUSEHOLDSWITHNOMEMBERS],
    @HOUSEHOLDINGONERECORDPERHOUSEHOLD = [MKTSEGMENTATION].[HOUSEHOLDINGONERECORDPERHOUSEHOLD],
    @MAILINGTYPECODE = [MKTSEGMENTATION].[MAILINGTYPECODE],
    @EXCLUSIONDATETYPECODE = isnull([BUSINESSPROCESSCOMMPREF].[DATETYPECODE], 0),
    @EXCLUSIONASOFDATE = [BUSINESSPROCESSCOMMPREF].[ASOFDATE],
    @EXCLUDEDECEASED = isnull([BUSINESSPROCESSCOMMPREF].[EXCLUDEDECEASED], 0),
    @EXCLUDEINACTIVE = isnull([BUSINESSPROCESSCOMMPREF].[EXCLUDEINACTIVE], 0),
    @EXCLUSIONS = dbo.[UFN_MKTSEGMENTATION_GETREQUIREDANDSELECTEDSOLICITCODEEXCLUSIONS_TOITEMLISTXML]([MKTSEGMENTATIONACTIVATEPROCESS].[ID]),
    @USEADDRESSPROCESSING = [MKTSEGMENTATION].[USEADDRESSPROCESSING],
    @ADDRESSPROCESSINGOPTIONID = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONID],
    @NAMEFORMATPARAMETERID = [MKTSEGMENTATION].[NAMEFORMATPARAMETERID],
    @ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATETYPECODE],
    @ADDRESSPROCESSINGOPTIONSEASONALASOFDATE = [MKTSEGMENTATION].[ADDRESSPROCESSINGOPTIONSEASONALASOFDATE],
    @RUNACTIVATEANDEXPORT = [MKTSEGMENTATION].[RUNACTIVATEANDEXPORT],
    @EXPORTDESCRIPTION = [MKTSEGMENTATIONEXPORTPROCESS].[DESCRIPTION],
    @MAILEXPORTDEFINITIONID = [MKTSEGMENTATIONEXPORTPROCESS].[MAILEXPORTDEFINITIONID],
    @EMAILEXPORTDEFINITIONID = [MKTSEGMENTATIONEXPORTPROCESS].[EMAILEXPORTDEFINITIONID],
    @PHONEEXPORTDEFINITIONID = [MKTSEGMENTATIONEXPORTPROCESS].[PHONEEXPORTDEFINITIONID],
    @RUNSEGMENTATIONSEGMENTREFRESHPROCESS = [MKTSEGMENTATIONACTIVATEPROCESS].[RUNSEGMENTATIONSEGMENTREFRESHPROCESS],
    @CACHESOURCEANALYSISRULEDATA = [MKTSEGMENTATIONACTIVATEPROCESS].[CACHESOURCEANALYSISRULEDATA],
    @OVERRIDEBUSINESSUNITS = [MKTSEGMENTATION].[OVERRIDEBUSINESSUNITS],
    @BASECURRENCYID = [MKTSEGMENTATION].[BASECURRENCYID]
  from dbo.[MKTSEGMENTATION]
  inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  inner join dbo.[MKTSEGMENTATIONEXPORTPROCESS] on [MKTSEGMENTATIONEXPORTPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
  left outer join dbo.[BUSINESSPROCESSCOMMPREF] on [BUSINESSPROCESSCOMMPREF].[BUSINESSPROCESSPARAMETERSETID] = [MKTSEGMENTATIONACTIVATEPROCESS].[ID]
  where [MKTSEGMENTATION].[ID] = @SEGMENTATIONID;

  if @MAILINGTYPECODE = 1
    set @ENABLEHOUSEHOLDING = 0;
  else
    select
      @ENABLEHOUSEHOLDING = (case when sum(case when isnull([MKTRECORDSOURCEFIELDMAPPINGS].[HOUSEHOLDIDFIELD],'') = '' then 0 else 1 end) = 0 then 0 else 1 end)
    from dbo.[MKTRECORDSOURCE]
    left join dbo.[MKTRECORDSOURCEFIELDMAPPINGS] on [MKTRECORDSOURCEFIELDMAPPINGS].[ID] = [MKTRECORDSOURCE].[ID]
    where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([MKTRECORDSOURCE].[ID]) = 1;

  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,
    [LOCKED] bit,
    [TEMPLATETYPECODE] tinyint
  );

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

  if @ISBBEC = 1
    begin
      if @OVERRIDEBUSINESSUNITS = 1
        set @BUSINESSUNITS =  dbo.[UFN_MKTSEGMENTATIONBUSINESSUNIT_GETBUSINESSUNITS_TOITEMLISTXML](@SEGMENTATIONID) 
      else
        begin
          declare @APPEALSYSTEMID uniqueidentifier;

          select 
            @APPEALSYSTEMID = convert(uniqueidentifier,[MKTSEGMENTATIONACTIVATE].[APPEALSYSTEMID])
          from dbo.[MKTAPPEALRECORDSOURCE] 
          inner join [QUERYVIEWCATALOG] on [MKTAPPEALRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
          left join [MKTSEGMENTATIONACTIVATE] on ([MKTSEGMENTATIONACTIVATE].[RECORDSOURCEID] = [MKTAPPEALRECORDSOURCE].[ID] and [MKTSEGMENTATIONACTIVATE].[SEGMENTATIONID] = @SEGMENTATIONID)
          where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([QUERYVIEWCATALOG].[ID]) = 1

          if @APPEALSYSTEMID is not null
            set @BUSINESSUNITS = dbo.[UFN_APPEALBUSINESSUNIT_GETBUSINESSUNITS_TOITEMLISTXML](@APPEALSYSTEMID)
        end
    end

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

  --Load default address processing options if they were not already set on the marketing effort

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

  return 0;