USP_DATAFORMTEMPLATE_EDIT_EMAILADDRESS

The save procedure used by the edit dataform template "Email Address Edit Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@EMAILADDRESSTYPECODEID	uniqueidentifier	IN	Type
@EMAILADDRESS	UDT_EMAILADDRESS	IN	Email address
@PRIMARY	bit	IN	Set as primary email address
@DONOTEMAIL	bit	IN	Do not send email to this address
@UPDATEMATCHINGSPOUSEEMAILADDRESS	bit	IN	Update matching email information for spouse
@UPDATEMATCHINGHOUSEHOLDEMAILADDRESS	bit	IN	Update matching email addresses in household
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_EMAILADDRESS
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @EMAILADDRESSTYPECODEID uniqueidentifier,
                    @EMAILADDRESS dbo.UDT_EMAILADDRESS,
                    @PRIMARY bit,
                    @DONOTEMAIL bit,
                    @UPDATEMATCHINGSPOUSEEMAILADDRESS bit,
                    @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS bit
                )
                as
                    set nocount on;

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

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

                    declare @CONSTITUENTID uniqueidentifier;
                    declare @OLDEMAILADDRESS dbo.UDT_EMAILADDRESS;
                    declare @OLDEMAILADDRESSTYPECODEID uniqueidentifier;

                    -- @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;

                    select
                        @CONSTITUENTID = EMAILADDRESS.[CONSTITUENTID],
                        @OLDEMAILADDRESS = EMAILADDRESS.[EMAILADDRESS],
                        @OLDEMAILADDRESSTYPECODEID = EMAILADDRESS.[EMAILADDRESSTYPECODEID]
                    from
                        dbo.[EMAILADDRESS]
                    where
                        EMAILADDRESS.[ID] = @ID;

                    begin try
                        if @PRIMARY = 1
                            update
                                dbo.EMAILADDRESS
                            set
                                ISPRIMARY = 0,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where
                                CONSTITUENTID = @CONSTITUENTID and
                                ISPRIMARY = 1 and
                                ID <> @ID;

                        update
                            dbo.EMAILADDRESS
                        set
                            EMAILADDRESSTYPECODEID = @EMAILADDRESSTYPECODEID,
                            EMAILADDRESS = @EMAILADDRESS,
                            ISPRIMARY = @PRIMARY,
                            DONOTEMAIL = @DONOTEMAIL,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where
                            ID = @ID;

                        if @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS = 1 begin
                            declare @MATCHINGCONSTITUENTS table(ID uniqueidentifier);
                            insert into @MATCHINGCONSTITUENTS select CONSTITUENTID from dbo.UFN_EMAILADDRESS_MATCHINGHOUSEHOLDRECORDS(@CONSTITUENTID, @OLDEMAILADDRESS, @OLDEMAILADDRESSTYPECODEID);

                            -- remove primary indicator if we're going to update with a new one

                            if @PRIMARY = 1
                                update dbo.EMAILADDRESS
                                set
                                    ISPRIMARY = 0,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CURRENTDATE
                                where
                                    CONSTITUENTID in (select ID from @MATCHINGCONSTITUENTS);

                            -- update the existing records

                            update dbo.EMAILADDRESS
                            set
                                EMAILADDRESSTYPECODEID = @EMAILADDRESSTYPECODEID,
                                EMAILADDRESS = @EMAILADDRESS,
                                ISPRIMARY = @PRIMARY,
                                DONOTEMAIL = @DONOTEMAIL,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where
                                EMAILADDRESS = @OLDEMAILADDRESS
                            and
                                EMAILADDRESSTYPECODEID = @OLDEMAILADDRESSTYPECODEID
                            and
                                CONSTITUENTID in (select ID from @MATCHINGCONSTITUENTS);

                        end
                    end try
                    begin catch
                        exec dbo.USP_RAISE_ERROR;
                        return 1;
                    end catch

                    return 0;