USP_BBNC_COMMITPROFILEUPDATEEMAILADDRESSES

Updates a constituent's email address from a Blackbaud Internet Solutions profile transaction.
Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@EMAILID	uniqueidentifier	IN
@EMAILADDRESSTYPECODEID	uniqueidentifier	IN
@EMAILADDRESS	nvarchar(100)	IN
@PREVIOUSEMAILADDRESSTYPECODEID	uniqueidentifier	IN
@ADDNEWEMAIL	bit	IN
@ISPRIMARY	bit	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN
@STARTDATE	date	IN
@ENDDATE	date	IN
@DONOTEMAIL	bit	IN
Definition

Copy


CREATE procedure dbo.USP_BBNC_COMMITPROFILEUPDATEEMAILADDRESSES
(
    @CONSTITUENTID uniqueidentifier = null,    
    @EMAILID uniqueidentifier = null,
    @EMAILADDRESSTYPECODEID uniqueidentifier = null,
    @EMAILADDRESS nvarchar(100) = '',
    @PREVIOUSEMAILADDRESSTYPECODEID uniqueidentifier = null,
    @ADDNEWEMAIL bit = null,
    @ISPRIMARY bit = null,
    @CHANGEAGENTID uniqueidentifier = null,
    @CHANGEDATE datetime = null,
    @STARTDATE date = null,
    @ENDDATE date = null,
    @DONOTEMAIL bit = null
) as
    set nocount on;

    declare @SEQUENCE int;
    declare @PRIMARYEMAILID uniqueidentifier;

    if @CONSTITUENTID is null
        begin
            raiserror('The constituent ID is required',16,1);
            return -2;
        end

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

    if @CHANGEDATE is null
        set @CHANGEDATE = getdate();

    declare @INFOSOURCECODEID uniqueidentifier;
    select top (1)
        @INFOSOURCECODEID = [INFOSOURCECODEID]
    from
        dbo.NETCOMMUNITYDEFAULTCODEMAP;

    begin try
    if @ISPRIMARY = 1
        begin
            select 
              @PRIMARYEMAILID = [ID]
            from 
              dbo.EMAILADDRESS
            where
              [CONSTITUENTID] = @CONSTITUENTID
              and [ISPRIMARY] = 1;

            if (@PRIMARYEMAILID is not null and @PRIMARYEMAILID <> coalesce(@EMAILID, '00000000-0000-0000-0000-000000000000')) or 
               (@PRIMARYEMAILID is not null and @PRIMARYEMAILID = @EMAILID and @ADDNEWEMAIL = 1)
              begin
                update dbo.EMAILADDRESS set
                  [ISPRIMARY] = 0
                where
                  [ID] = @PRIMARYEMAILID;
              end            
        end


    if @ADDNEWEMAIL = 0
        begin
            if @EMAILID is not null
            update dbo.EMAILADDRESS set
                [EMAILADDRESSTYPECODEID] = @EMAILADDRESSTYPECODEID,
                [EMAILADDRESS] = @EMAILADDRESS,
                [ISPRIMARY] = @ISPRIMARY,
                [INFOSOURCECODEID] = @INFOSOURCECODEID,
                [CHANGEDBYID] = @CHANGEAGENTID,
                [DATECHANGED] = @CHANGEDATE,
                [STARTDATE] = @STARTDATE,
                [ENDDATE] = @ENDDATE,
                [DONOTEMAIL] = @DONOTEMAIL
            where
                [ID] = @EMAILID
        end
    else
        begin
            if @PREVIOUSEMAILADDRESSTYPECODEID is not null
                begin
                    if @EMAILID is not null
                        update dbo.EMAILADDRESS set
                            [EMAILADDRESSTYPECODEID] = @PREVIOUSEMAILADDRESSTYPECODEID,
                            [DATECHANGED] = @CHANGEDATE,
                            [CHANGEDBYID] = @CHANGEAGENTID
                        where
                            [ID] = @EMAILID                          
                end

            select @SEQUENCE = max([SEQUENCE]) 
            from dbo.EMAILADDRESS
            where [CONSTITUENTID] = @CONSTITUENTID;

            if @SEQUENCE is null
                set @SEQUENCE = 0;

            insert into dbo.EMAILADDRESS
            (
                [ID],
                [CONSTITUENTID],
                [EMAILADDRESSTYPECODEID],
                [EMAILADDRESS],
                [ISPRIMARY],
                [SEQUENCE],
                [INFOSOURCECODEID],
                [ADDEDBYID],
                [CHANGEDBYID],
                [DATEADDED],
                [DATECHANGED],
                [STARTDATE],
                [ENDDATE],
                [DONOTEMAIL]
            )
            values
            (
                newid(),
                @CONSTITUENTID,
                @EMAILADDRESSTYPECODEID,
                @EMAILADDRESS,
                @ISPRIMARY,
                @SEQUENCE + 1,
                @INFOSOURCECODEID,
                @CHANGEAGENTID,
        @CHANGEAGENTID,
                @CHANGEDATE,
                @CHANGEDATE,
                @STARTDATE,
                @ENDDATE,
                @DONOTEMAIL
            );

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

    return 0;