USP_DATAFORMTEMPLATE_EDITLOAD_NAMEFORMATPARAMETER2

The load procedure used by the edit dataform template "Name Format Parameter 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.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@NAME	nvarchar(100)	INOUT	Name
@DESCRIPTION	nvarchar(255)	INOUT	Description
@INDIVIDUALADDRESSEES	xml	INOUT	Use format defined on individual's record
@ADDRESSEEFUNCTIONID	uniqueidentifier	INOUT	Otherwise, use this format
@INDIVIDUALSALUTATIONS	xml	INOUT	Use format defined on individual's record
@SALUTATIONFUNCTIONID	uniqueidentifier	INOUT	Otherwise, use this format
@ORGANIZATIONADDRESSEES	xml	INOUT	Use format defined on contact's record
@CONTACTADDRESSEEFUNCTIONID	uniqueidentifier	INOUT	Otherwise, use this format
@ORGANIZATIONSALUTATIONS	xml	INOUT	Use format defined on contact's record
@ORGSALUTATIONCODE	tinyint	INOUT	ORGSALUTATIONCODE
@CONTACTSALUTATIONFUNCTIONID	uniqueidentifier	INOUT	Otherwise, use this format
@CONTACTSALUTATIONOPTIONCODE	tinyint	INOUT	If contact is not available, use
@CUSTOMNAME	nvarchar(100)	INOUT	Custom name
@GROUPADDRESSEES	xml	INOUT	Use format defined on primary member's record
@GROUPADDRESSEEFUNCTIONID	uniqueidentifier	INOUT	Otherwise, use this format
@GROUPSALUTATIONCODE	tinyint	INOUT	GROUPSALUTATIONCODE
@GROUPSALUTATIONS	xml	INOUT	Use format defined on primary member's record
@GROUPSALUTATIONFUNCTIONID	uniqueidentifier	INOUT	Otherwise, use this format
@GROUPNOCONTACTOPTIONCODE	tinyint	INOUT	If member is not available, use
@GROUPNOCONTACTCUSTOMNAME	nvarchar(100)	INOUT	Custom name
@JOINTRULETYPECODE	tinyint	INOUT	JOINTRULETYPECODE
@JOINTSELECTIONID	uniqueidentifier	INOUT	Joint selection criteria
@JOINTSELECTIONBOTHRULETYPECODE	tinyint	INOUT	If both constituents are found in the selection, use the following
@JOINTSELECTIONNEITHERRULETYPECODE	tinyint	INOUT	If neither constituents are found in the selection, use the following
@JOINTEXCLUDESPOUSE	bit	INOUT	The spouse does not qualify or is excluded from the process
@JOINTSPOUSESMAILEDSEPARATELY	bit	INOUT	Both spouses will be mailed separately within the process
@SITEID	uniqueidentifier	INOUT	Site
@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.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@SITEREQUIRED	bit	INOUT
@USEDBYMARKETINGEFFORT	bit	INOUT

Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_NAMEFORMATPARAMETER2
                    (
                        @ID uniqueidentifier,
                        @DATALOADED bit = 0 output,
                        @NAME nvarchar(100) = null output,
                        @DESCRIPTION nvarchar(255) = null output,
                        @INDIVIDUALADDRESSEES xml = null output,
                        @ADDRESSEEFUNCTIONID uniqueidentifier = null output,
                        @INDIVIDUALSALUTATIONS xml = null output,
                        @SALUTATIONFUNCTIONID uniqueidentifier = null output,
                        @ORGANIZATIONADDRESSEES xml = null output,
                        @CONTACTADDRESSEEFUNCTIONID uniqueidentifier = null output,
                        @ORGANIZATIONSALUTATIONS xml = null output,
                        @ORGSALUTATIONCODE tinyint = null output,
                        @CONTACTSALUTATIONFUNCTIONID uniqueidentifier = null output,
                        @CONTACTSALUTATIONOPTIONCODE tinyint = null output,
                        @CUSTOMNAME nvarchar(100) = null output,
                        @GROUPADDRESSEES xml = null output,
                        @GROUPADDRESSEEFUNCTIONID uniqueidentifier = null output,
                        @GROUPSALUTATIONCODE tinyint = null output,
                        @GROUPSALUTATIONS xml = null output,
                        @GROUPSALUTATIONFUNCTIONID uniqueidentifier = null output,
                        @GROUPNOCONTACTOPTIONCODE tinyint = null output,
                        @GROUPNOCONTACTCUSTOMNAME nvarchar(100) = null output,
                        @JOINTRULETYPECODE tinyint = null output,
                        @JOINTSELECTIONID uniqueidentifier = null output,
                        @JOINTSELECTIONBOTHRULETYPECODE tinyint = null output,
                        @JOINTSELECTIONNEITHERRULETYPECODE tinyint = null output,
                        @JOINTEXCLUDESPOUSE bit = null output,
                        @JOINTSPOUSESMAILEDSEPARATELY bit = null output,
                        @SITEID uniqueidentifier = null output,
                        @TSLONG bigint = 0 output,
                        @CURRENTAPPUSERID uniqueidentifier = null,
                        @SITEREQUIRED bit = null output,
                        @USEDBYMARKETINGEFFORT bit = null output
                    )
                    as
                        set nocount on;

                        set @TSLONG = 0;

                        set @SITEREQUIRED = dbo.UFN_SITEREQUIREDFORUSER(@CURRENTAPPUSERID);

                        select @DATALOADED = 1,
                            @NAME = NAME,
                            @DESCRIPTION = DESCRIPTION,

                            @INDIVIDUALADDRESSEES = dbo.UFN_NAMEFORMATPARAMETER_GETINDADDRESSEES_TOITEMLISTXML(@ID),
                            @ADDRESSEEFUNCTIONID  = ADDRESSEEFUNCTIONID,
                            @INDIVIDUALSALUTATIONS = dbo.UFN_NAMEFORMATPARAMETER_GETINDSALUTATIONS_TOITEMLISTXML(@ID),
                            @SALUTATIONFUNCTIONID  = SALUTATIONFUNCTIONID,

                            @ORGANIZATIONADDRESSEES = dbo.UFN_NAMEFORMATPARAMETER_GETORGADDRESSEES_TOITEMLISTXML(@ID),
                            @CONTACTADDRESSEEFUNCTIONID  = CONTACTADDRESSEEFUNCTIONID,
                            @ORGANIZATIONSALUTATIONS = dbo.UFN_NAMEFORMATPARAMETER_GETORGSALUTATIONS_TOITEMLISTXML(@ID),
                            @ORGSALUTATIONCODE = ORGSALUTATIONCODE,
                            @CONTACTSALUTATIONFUNCTIONID  = CONTACTSALUTATIONFUNCTIONID,
                            @CONTACTSALUTATIONOPTIONCODE  = CONTACTSALUTATIONOPTIONCODE,
                            @CUSTOMNAME = CUSTOMNAME,

                            @GROUPADDRESSEES = dbo.UFN_NAMEFORMATPARAMETER_GETGRPADDRESSEES_TOITEMLISTXML(@ID),
                            @GROUPADDRESSEEFUNCTIONID  = GROUPADDRESSEEFUNCTIONID,
                            @GROUPSALUTATIONCODE = GROUPSALUTATIONCODE,
                            @GROUPSALUTATIONS = dbo.UFN_NAMEFORMATPARAMETER_GETGRPSALUTATIONS_TOITEMLISTXML(@ID),
                            @GROUPSALUTATIONFUNCTIONID  = GROUPSALUTATIONFUNCTIONID,
                            @GROUPNOCONTACTOPTIONCODE  = GROUPNOCONTACTOPTIONCODE,
                            @GROUPNOCONTACTCUSTOMNAME = GROUPNOCONTACTCUSTOMNAME,

                            @JOINTRULETYPECODE = JOINTRULETYPECODE,
                            @JOINTSELECTIONID = JOINTSELECTIONID,
                            @JOINTSELECTIONBOTHRULETYPECODE = JOINTSELECTIONBOTHRULETYPECODE,
                            @JOINTSELECTIONNEITHERRULETYPECODE = JOINTSELECTIONNEITHERRULETYPECODE,
                            @JOINTEXCLUDESPOUSE = JOINTEXCLUDESPOUSE,
                            @JOINTSPOUSESMAILEDSEPARATELY = JOINTSPOUSESMAILEDSEPARATELY,

              /* Returns true if there exists a marketing effort (or marketing effort segment) that has turned off the exclusions report 
                 and is using the name format */
                            @USEDBYMARKETINGEFFORT = case when exists (select top 1 1 from 
                                                         dbo.[MKTSEGMENTATION] 
                                                         inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATION].[ID]
                                                         where 
                                                           [MKTSEGMENTATION].[NAMEFORMATPARAMETERID] = @ID and 
                                                           [MKTSEGMENTATIONACTIVATEPROCESS].[RUNMARKETINGEXCLUSIONSREPORT] = 0) or

                                                                   exists (select top 1 1 
                                                         from dbo.[MKTSEGMENTATIONSEGMENT] 
                                                         inner join dbo.[MKTSEGMENTATIONACTIVATEPROCESS] on [MKTSEGMENTATIONACTIVATEPROCESS].[SEGMENTATIONID] = [MKTSEGMENTATIONSEGMENT].[SEGMENTATIONID]
                                                         where 
                                                           [MKTSEGMENTATIONSEGMENT].[NAMEFORMATPARAMETERID] = @ID and
                                                           [MKTSEGMENTATIONACTIVATEPROCESS].[RUNMARKETINGEXCLUSIONSREPORT] = 0) then 1 else 0 end,

                            @SITEID = SITEID,
                            @TSLONG = TSLONG
                        from
                            dbo.NAMEFORMATPARAMETER
                        where 
                            NAMEFORMATPARAMETER.ID = @ID;

                        if @JOINTSELECTIONID is not null
                            set @JOINTRULETYPECODE = 1;

                        return 0;