USP_GLOBALCHANGE_SETALLMAILPREFERENCE

            CREATE procedure dbo.USP_GLOBALCHANGE_SETALLMAILPREFERENCE
            (
                @IDSETREGISTERID uniqueidentifier = null,  
                @OVERWRITEEXISTING bit = 0,
                @SENDMAIL bit = 1,
                @DELIVERYMETHODCODE tinyint = 0,
                @USESEASONALADDRESS bit = 1,
                @COMMENTS nvarchar(500) = '',
                @CHANGEAGENTID uniqueidentifier = null,
                @ASOF as datetime = null,
                @NUMBERADDED int = 0 output,
                @NUMBEREDITED int = 0 output,
                @NUMBERDELETED int = 0 output,
                @CURRENTAPPUSERID uniqueidentifier = null
            )
            as
                set nocount off;

                declare @CURRENTDATE datetime

                set @CURRENTDATE = getdate();
                set @NUMBERADDED = 0;
                set @NUMBEREDITED = 0;
                set @NUMBERDELETED = 0; 
                declare @BPID uniqueidentifier = '3269A1D1-31CB-4D28-945C-B7623A3EFCCA';

                declare @BYPASSSECURITY bit;
                declare @BYPASSSITESECURITY bit;                
                exec dbo.USP_SECURITY_APPUSER_BYPASSSECURITYFORBUSINESSPROCESS @CURRENTAPPUSERID, @BPID, @BYPASSSECURITY output, @BYPASSSITESECURITY output;                

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

                declare @USEPRIMARYADDRESS bit
                set @USEPRIMARYADDRESS = 0
                declare @USEPRIMARYEMAIL bit
                set @USEPRIMARYEMAIL = 0

                if @SENDMAIL = 1
                    begin
                        if @DELIVERYMETHODCODE=0
                            begin
                                set @USEPRIMARYADDRESS=1            
                            end

                        if @DELIVERYMETHODCODE=1
                            begin
                                set @USEPRIMARYEMAIL=1
                                set @USESEASONALADDRESS=0
                            end
                    end
                else
                    begin
                        set @USESEASONALADDRESS = 0; --JamesWill WI192065 2011-02-01 Override the seasonal address flag when choosing not to send mail. This mimics what the UI Model on the add form does. 

                    end


                declare @SELECTION table (ID uniqueidentifier not null primary key)

                begin try

                    insert into @SELECTION (ID)
                    select ID
                    from dbo.UFN_CONSTITUENT_GETRECORDSINSELECTION_FORBUSINESSPROCESS(@CURRENTAPPUSERID, @IDSETREGISTERID, @BPID, @BYPASSSECURITY, @BYPASSSITESECURITY);

                    if @OVERWRITEEXISTING = 1
                    begin
                        delete dbo.MAILPREFERENCE
                        where CONSTITUENTID in (select ID from @SELECTION)
                    end

                    declare @i int
                    set @i = 0

                    while @i < 9
                    begin 
                        insert into dbo.MAILPREFERENCE
                        (
                            ID, CONSTITUENTID, MAILTYPECODE, SENDMAIL, DELIVERYMETHODCODE, ADDRESSID, EMAILADDRESSID, USESEASONALADDRESS,
                            USEPRIMARYADDRESS, USEPRIMARYEMAIL, COMMENTS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                        )
                        select                    
                            newid(), selection.ID, @i, @SENDMAIL, @DELIVERYMETHODCODE, null, null, @USESEASONALADDRESS,
                            @USEPRIMARYADDRESS, @USEPRIMARYEMAIL, @COMMENTS, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                        from @SELECTION selection
                        where @OVERWRITEEXISTING = 1 or
                            dbo.UFN_MAILPREFERENCE_VALIDATEUNIQUE(null, selection.ID, @i, null, null, null, null, 
                                                                    null, null, null, null, null) = 1

                        set @NUMBERADDED = @NUMBERADDED + @@ROWCOUNT                                    
                        set @i = @i + 1
                    end

                end try

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