USP_EMAILADDRESS_CREATE

            CREATE procedure dbo.USP_EMAILADDRESS_CREATE
            (
                @ID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier = null,    
                @CURRENTDATE datetime = null,
                @CONSTITUENTID uniqueidentifier,
                @EMAILADDRESSTYPECODEID uniqueidentifier = null,
                @EMAILADDRESS dbo.UDT_EMAILADDRESS,
                @PRIMARY bit = 0,
                @DONOTEMAIL bit = 0,
                @INFOSOURCECODEID uniqueidentifier = null,
                @INFOSOURCECOMMENTS nvarchar(256) = '',
                @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS bit = 0,
                @RELATIONSHIPID uniqueidentifier = null,
                @ORIGINCODE tinyint = 0,
                @STARTDATE date = null,
                @EMAILISCONFIDENTIAL bit = 0,
                @DONOTEMAILREASONCODEID uniqueidentifier = null
            ) as
                set nocount on;

                if @ID is null
                    set @ID = newid();

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

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

                if @DONOTEMAIL = 0
                begin
                    set @DONOTEMAILREASONCODEID = null;
                end

                declare @SEQUENCE int;

                select
                    @SEQUENCE=coalesce(max(SEQUENCE),0)+1
                from
                    dbo.EMAILADDRESS
                where
                    CONSTITUENTID = @CONSTITUENTID;

                if @ORIGINCODE is not null and @ORIGINCODE <> 0
                    set @INFOSOURCECODEID = null
                else
                    set @ORIGINCODE = 0

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

                    insert into dbo.EMAILADDRESS
                    (
                        ID,
                        CONSTITUENTID,
                        EMAILADDRESSTYPECODEID,
                        EMAILADDRESS,
                        ISPRIMARY,
                        SEQUENCE,
                        DONOTEMAIL,
                        INFOSOURCECODEID,
                        INFOSOURCECOMMENTS,
                        RELATIONSHIPID,
                        ORIGINCODE,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED,
                        DATECHANGED,
                        STARTDATE,
                        ISCONFIDENTIAL,
                        DONOTEMAILREASONCODEID
                    )
                    values
                    (
                        @ID,
                        @CONSTITUENTID,
                        @EMAILADDRESSTYPECODEID,
                        @EMAILADDRESS,
                        @PRIMARY,
                        @SEQUENCE,
                        @DONOTEMAIL,
                        @INFOSOURCECODEID,
                        @INFOSOURCECOMMENTS,
                        @RELATIONSHIPID,
                        @ORIGINCODE,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE,
                        @STARTDATE,
                        @EMAILISCONFIDENTIAL,
                        @DONOTEMAILREASONCODEID
                    );

                    if @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS = 1 begin
                        declare @EARLIESTTIMECURRENTDATE date;
                        set @EARLIESTTIMECURRENTDATE = getdate();

                        -- if the constituent is an individual, householdid will be the household they are a member of

                        -- if the constituent is a household, householdid will be that household's id

                        declare @HOUSEHOLDID uniqueidentifier;
                        if dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1
                            set @HOUSEHOLDID = @CONSTITUENTID;
                        else
                            select
                                @HOUSEHOLDID = GM.GROUPID
                            from
                                dbo.GROUPMEMBER GM
                            left outer join
                                dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                            left outer join
                                dbo.GROUPDATA GD on GD.ID = GM.GROUPID
                            where
                                GM.MEMBERID = @CONSTITUENTID
                            and
                                GD.GROUPTYPECODE = 0
                            and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @EARLIESTTIMECURRENTDATE))
                                or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @EARLIESTTIMECURRENTDATE)) 
                                or (GMDR.DATEFROM <= @EARLIESTTIMECURRENTDATE and GMDR.DATETO > @EARLIESTTIMECURRENTDATE));
                        -- create a table of all of the members of the household previously identified

                        declare @IDSTOUPDATE table(ID uniqueidentifier, HASPRIMARY bit)
                        insert into @IDSTOUPDATE
                            select
                                GM.MEMBERID,
                                0
                            from
                                dbo.GROUPMEMBER GM
                            left outer join    
                                dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                            where
                                GM.GROUPID = @HOUSEHOLDID
                                and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @EARLIESTTIMECURRENTDATE))
                                    or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @EARLIESTTIMECURRENTDATE)) 
                                    or (GMDR.DATEFROM <= @EARLIESTTIMECURRENTDATE and GMDR.DATETO > @EARLIESTTIMECURRENTDATE))
                            union all
                            select
                                @HOUSEHOLDID, 0; -- include the household itself (for the case where the constituent is an individual)


                        delete from @IDSTOUPDATE where ID = @CONSTITUENTID;    -- (if the constituent was an individual, they'll show as a member - if they were a household it got union'd in)


                        -- Do not overwrite another constituent's primary email

                        update @IDSTOUPDATE
                        set
                            HASPRIMARY = 1        
                        from 
                            @IDSTOUPDATE I
                            inner join dbo.EMAILADDRESS on I.ID = EMAILADDRESS.CONSTITUENTID
                        where
                            EMAILADDRESS.ISPRIMARY = 1;

                        -- create a table of all matching email addresses

                        declare @MATCHING table (ID uniqueidentifier);
                        insert into @MATCHING
                        select
                            ID 
                        from
                            dbo.EMAILADDRESS 
                        where
                            EMAILADDRESS = @EMAILADDRESS 
                            and ( (EMAILADDRESSTYPECODEID = @EMAILADDRESSTYPECODEID) or (EMAILADDRESSTYPECODEID is null and @EMAILADDRESSTYPECODEID is null) )
                            and CONSTITUENTID in (select ID from @IDSTOUPDATE);

                        -- If the email does not exist, insert it, setting primary as necessary

                        insert into dbo.EMAILADDRESS
                            (CONSTITUENTID,EMAILADDRESSTYPECODEID,EMAILADDRESS,ISPRIMARY,INFOSOURCECODEID,INFOSOURCECOMMENTS,ORIGINCODE,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED,STARTDATE, DONOTEMAIL, ISCONFIDENTIAL, DONOTEMAILREASONCODEID)
                        select
                            IDSTOUPDATE.ID,@EMAILADDRESSTYPECODEID,@EMAILADDRESS, case when IDSTOUPDATE.HASPRIMARY = 1 then 0 else 1 end,@INFOSOURCECODEID,@INFOSOURCECOMMENTS,@ORIGINCODE,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE,@STARTDATE, @DONOTEMAIL, @EMAILISCONFIDENTIAL,@DONOTEMAILREASONCODEID
                        from
                            @IDSTOUPDATE IDSTOUPDATE
                        where
                            not exists (
                                    select 'x' 
                                    from dbo.EMAILADDRESS
                                    inner join @MATCHING as M on M.ID = EMAILADDRESS.ID
                                    where EMAILADDRESS.CONSTITUENTID = IDSTOUPDATE.ID
                                );
                    end
                end try
                begin catch
                    exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

                return 0;