USP_DATAFORMTEMPLATE_ADD_COMMUNICATIONPLANMAILING_PRELOAD

The load procedure used by the edit dataform template "Communication Plan Mailing Add Form"

Parameters

Parameter	Parameter Type	Mode	Description
@COMMUNICATIONPLANID	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.
@CONSTITUENTRECORDTYPEID	uniqueidentifier	INOUT	Constituent record type
@EXCLUSIONS	xml	INOUT	Exclusions
@HOUSEHOLDINGTYPECODE	tinyint	INOUT	Householding
@ENABLEHOUSEHOLDING	bit	INOUT	Enable householding?
@ADDRESSPROCESSINGOPTIONID	uniqueidentifier	INOUT	Address processing options
@NAMEFORMATPARAMETERID	uniqueidentifier	INOUT	Name format options
@SITEID	uniqueidentifier	INOUT	Site ID
@CANUPDATEEXCLUDEDECEASED	bit	INOUT	Can update exclude deceased constituents?
@CANUPDATEEXCLUDEINACTIVE	bit	INOUT	Can update exclude inactive constituents?
@ACTIVATIONKPIS	xml	INOUT	Activation KPIs

Definition

Copy



                create procedure dbo.[USP_DATAFORMTEMPLATE_ADD_COMMUNICATIONPLANMAILING_PRELOAD]
                (
                    @COMMUNICATIONPLANID uniqueidentifier,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @CONSTITUENTRECORDTYPEID uniqueidentifier = null output,
                    @EXCLUSIONS xml = null output,
                    @HOUSEHOLDINGTYPECODE tinyint = null output,
                    @ENABLEHOUSEHOLDING bit = 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
                )
                as
                begin
                    set nocount on;

                    select @CONSTITUENTRECORDTYPEID = [ID]
                    from dbo.[RECORDTYPE]
                    where upper([NAME]) = 'CONSTITUENT';

                    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
                        @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 [MKTRECORDSOURCE].[ID] = 'DFB4B8C1-5E9A-4C14-ACE3-01C096B53BA0';

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

                    set @HOUSEHOLDINGTYPECODE = (case when @ENABLEHOUSEHOLDING = 1 then 1 else 0 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_GETFIELDS null, 0

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

                    return 0;
                end