USP_DATAFORMTEMPLATE_PRELOAD_ADD_MKTSEGMENTATION

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

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@PLANPATH	nvarchar(max)	INOUT	Plan
@MARKETINGPLANITEMID	uniqueidentifier	INOUT	Marketing plan item id
@SOURCECODEID	uniqueidentifier	INOUT	Source code
@SMARTQUERIESEXIST	bit	INOUT	Smart queries exist?
@HOUSEHOLDINGTYPECODE	tinyint	INOUT	Include
@ENABLEHOUSEHOLDING	bit	INOUT	Enable householding?
@SITEREQUIRED	bit	INOUT	Site required?
@ISBBEC	bit	INOUT	Is BBEC?
@EXCLUSIONS	xml	INOUT	Exclusions
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	INOUT	Address processing options
@NAMEFORMATPARAMETERID	uniqueidentifier	INOUT	Name format options
@SITEID	uniqueidentifier	INOUT	Site
@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.
@HOUSEHOLDINGONERECORDPERHOUSEHOLD	bit	INOUT	Send to one person per household
@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_PRELOAD_ADD_MKTSEGMENTATION]
(
  @CURRENTAPPUSERID uniqueidentifier,
  @PLANPATH nvarchar(max) = null output,
  @MARKETINGPLANITEMID uniqueidentifier = null output,
  @SOURCECODEID uniqueidentifier = null output,
  @SMARTQUERIESEXIST bit = null output,
  @HOUSEHOLDINGTYPECODE tinyint = null output,
  @ENABLEHOUSEHOLDING bit = null output,
  @SITEREQUIRED bit = null output,
  @ISBBEC bit = null output,
  @EXCLUSIONS xml = null output,
  @ADDRESSPROCESSINGOPTIONID uniqueidentifier = null output,
  @NAMEFORMATPARAMETERID uniqueidentifier = null output,
  @SITEID uniqueidentifier = null output,
  @CANUPDATEEXCLUDEDECEASED bit = null output,
  @CANUPDATEEXCLUDEINACTIVE bit = null output,
  @ACTIVATIONKPIS xml = null output,
  @APPEALINFORMATION xml = null output,
  @HOUSEHOLDINGONERECORDPERHOUSEHOLD bit = null output,
  @BASECURRENCYID uniqueidentifier = null output,
  @EXPORTDEFINITIONRECORDTYPE nvarchar(50) = null output,
  @EXPORTDEFINITIONRECORDTYPEID uniqueidentifier = null output,
  @EXPORTDEFINITIONQUERYVIEWID uniqueidentifier = null output
)
as
  set nocount on;

  set @PLANPATH = dbo.[UFN_MKTMARKETINGPLANITEM_PATH](@MARKETINGPLANITEMID,1);

  set @SOURCECODEID = dbo.[UFN_MKTSOURCECODE_GETDEFAULTSOURCECODEID2](@CURRENTAPPUSERID, '7160D0B7-DA77-4BC9-97B2-060447EDBCF4', 1);

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

  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;

  --If householding is enabled, then default to only mail one person per household...

  set @HOUSEHOLDINGTYPECODE = (case when @ENABLEHOUSEHOLDING = 1 then 1 else 0 end);
  set @HOUSEHOLDINGONERECORDPERHOUSEHOLD = @ENABLEHOUSEHOLDING;

  set @SITEREQUIRED = dbo.[UFN_SITEREQUIREDFORUSERONFEATURE](@CURRENTAPPUSERID, '7160D0B7-DA77-4BC9-97B2-060447EDBCF4', 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 @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_MKTSEGMENTATIONACTIVATEKPI_GETDEFAULTS] 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 QUERYVIEWCATALOG on [MKTAPPEALRECORDSOURCE].[ID] = [QUERYVIEWCATALOG].[ID]
    where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT]([QUERYVIEWCATALOG].[ID]) = 1
    for xml raw('ITEM'), type, elements, root('APPEALINFORMATION'), binary base64);

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

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

  return 0;