USP_DATAFORMTEMPLATE_EDIT_EMAILADDRESS_7

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@EMAILADDRESSTYPECODEID	uniqueidentifier	IN
@EMAILADDRESS	UDT_EMAILADDRESS	IN
@PRIMARY	bit	IN
@DONOTEMAIL	bit	IN
@UPDATEMATCHINGSPOUSEEMAILADDRESS	bit	IN
@UPDATEMATCHINGHOUSEHOLDEMAILADDRESS	bit	IN
@INFOSOURCECODEID	uniqueidentifier	IN
@INFOSOURCECOMMENTS	nvarchar(256)	IN
@CONSTITUENTDATAREVIEWROLLBACKREASONID	uniqueidentifier	IN
@STARTDATE	date	IN
@ENDDATE	date	IN
@EMAILISCONFIDENTIAL	bit	IN
@DONOTEMAILREASONCODEID	uniqueidentifier	IN

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_EMAILADDRESS_7
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @EMAILADDRESSTYPECODEID uniqueidentifier,
                    @EMAILADDRESS dbo.UDT_EMAILADDRESS,
                    @PRIMARY bit,
                    @DONOTEMAIL bit,
                    @UPDATEMATCHINGSPOUSEEMAILADDRESS bit,
                    @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS bit,
                    @INFOSOURCECODEID uniqueidentifier,
                    @INFOSOURCECOMMENTS nvarchar(256),
                    @CONSTITUENTDATAREVIEWROLLBACKREASONID uniqueidentifier,  -- used by constituent data review

                    @STARTDATE date,
                    @ENDDATE date,
                    @EMAILISCONFIDENTIAL bit,
                    @DONOTEMAILREASONCODEID uniqueidentifier
                )
                as
                    set nocount on;

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = getdate();

                    if @CHANGEAGENTID is null  
                        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                    -- #253763 Check to see if there are duplicate email addresses.  Same email address type and same email address

                    -- If nothing has changed on the edit form we don't want to check for duplicates.  This way if dups already exist and we

                    -- change nothing, we can still save the form without getting an error message.

                    if @ENDDATE is null
                    begin
                        declare @NULLVALUE uniqueidentifier = newid();
                        declare @NULLDATE date = '9999-01-01'
                        if exists (select 'X' from dbo.EMAILADDRESS a
                                    inner join dbo.EMAILADDRESS a2 on a2.CONSTITUENTID = a.CONSTITUENTID and a2.ID <> @ID
                                    where (coalesce(a.EMAILADDRESS,'') <> coalesce(@EMAILADDRESS,'')
                                            or coalesce(a.EMAILADDRESSTYPECODEID,@NULLVALUE) <> coalesce(@EMAILADDRESSTYPECODEID,@NULLVALUE)
                                            or coalesce(a.ENDDATE,@NULLDATE) <> coalesce(@ENDDATE,@NULLDATE))
                                    and a.ID = @ID
                                    and a2.ENDDATE is null
                                    and coalesce(a2.EMAILADDRESS,'') = coalesce(@EMAILADDRESS,'')
                                    and coalesce(a2.EMAILADDRESSTYPECODEID,@NULLVALUE) = coalesce(@EMAILADDRESSTYPECODEID,@NULLVALUE))
                                            raiserror('BBERR_EMAILADDRESS_DUPLICATENOTALLOWED', 13, 1);
                    end

                    -- @UPDATEMATCHINGSPOUSEEMAILADDRESS has been deprecated in favor of updating all of the members of a household

                    set @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS = case when @UPDATEMATCHINGSPOUSEEMAILADDRESS = 1 then 1 else @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS end;

                    exec dbo.USP_EMAILADDRESS_UPDATE @ID, @CHANGEAGENTID, @CURRENTDATE,
                        @EMAILADDRESSTYPECODEID, @EMAILADDRESS, @PRIMARY, @DONOTEMAIL,
                        @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS, @INFOSOURCECODEID, @INFOSOURCECOMMENTS, @STARTDATE = @STARTDATE, @ENDDATE = @ENDDATE,@EMAILISCONFIDENTIAL = @EMAILISCONFIDENTIAL, @DONOTEMAILREASONCODEID = @DONOTEMAILREASONCODEID;

                    return 0;