USP_MATCHFINDER_UPDATECONSTITUENT

            CREATE procedure dbo.USP_MATCHFINDER_UPDATECONSTITUENT
            (
                @APPUSERID uniqueidentifier,
                @CONSTITUENTID uniqueidentifier,

                @UPDATEORGNAME bit,
                @NEWORGNAME nvarchar(100),

                @UPDATEADDRESS bit,
                @NEWADDRESSBLOCK nvarchar(150),
                @NEWCITY nvarchar(50), 
                @NEWSTATEID uniqueidentifier,
                @NEWCOUNTRYID uniqueidentifier,
                @NEWPOSTCODE nvarchar(12),

                @UPDATEPHONE bit,
                @NEWPHONE nvarchar(100),

                @UPDATEFAX bit,
                @NEWFAX nvarchar(100),

                @UPDATEWEBADDRESS bit,
                @NEWWEBADDRESS nvarchar(2047),

                @UPDATEALIAS bit,
                @NEWALIAS nvarchar(100),

                @UPDATEINDUSTRY bit,
                @NEWINDUSTRY nvarchar(100),

                @UPDATEMATCHINGFACTOR bit,
                @NEWMATCHINGFACTOR decimal(5,2),

                @UPDATEMINMATCHPERGIFT bit,
                @NEWMINMATCHPERGIFT money,

                @UPDATEMAXMATCHPERGIFT bit,
                @NEWMAXMATCHPERGIFT money,

                @UPDATEMAXMATCHTOTAL bit,
                @NEWMAXMATCHTOTAL money,

                @UPDATEMAXMATCHANNUAL bit,
                @NEWMAXMATCHANNUAL money,

                @UPDATEMATCHNOTES bit,
                @NEWMATCHNOTES nvarchar(max),

                @CREATENEWCONTACT bit,
                @UPDATECONTACT bit,
                @CONTACTLINKID uniqueidentifier,

                @UPDATECONTACTNAME bit,
                @CONTACTFIRSTNAME nvarchar(50),
                @CONTACTMIDDLENAME nvarchar(50),
                @CONTACTKEYNAME nvarchar(50),

                @UPDATECONTACTEMAIL bit,
                @NEWCONTACTEMAIL nvarchar(100),

                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime
            )    
            as
            set nocount on;

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

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

            begin try
                declare @SEQUENCE int;

                declare @ADDRESSTYPECODEID uniqueidentifier;
                declare @PHONETYPECODEID uniqueidentifier;
                declare @FAXTYPECODEID uniqueidentifier;
                declare @CONTACTTYPECODEID uniqueidentifier;
                declare @CONTACTRELATIONSHIPTYPECODEID uniqueidentifier; 
                declare @CONTACTRECIPROCALRELATIONSHIPTYPECODEID uniqueidentifier;
                declare @CONTACTEMAILADDRESSTYPECODEID uniqueidentifier;
                declare @ALIASTYPECODEID uniqueidentifier;
                declare @MATCHINGGIFTCONDITIONTYPECODEID uniqueidentifier;
                declare @INFOSOURCECODEID uniqueidentifier;

                exec dbo.USP_MATCHFINDER_LOADCONFIG 1, @ADDRESSTYPECODEID output, 1, @PHONETYPECODEID output, 1, @FAXTYPECODEID output, 
                                                    1, @CONTACTTYPECODEID output, 1, @CONTACTRELATIONSHIPTYPECODEID output, 
                                                    1, @CONTACTRECIPROCALRELATIONSHIPTYPECODEID output, 1, @CONTACTEMAILADDRESSTYPECODEID output, 
                                                    0, @ALIASTYPECODEID output, 1, @MATCHINGGIFTCONDITIONTYPECODEID output, @INFOSOURCECODEID output


                --JamesWill 05/19/2008 CR300867-051608 Added a "Do Not Update Contact" to the UI which is defined to be true when "Create New Contact" and "Update Contact" are both false. So do not try to 

                --update the contact name or email when neither create new contact or update contact are set.

                if @CREATENEWCONTACT = 0 and @UPDATECONTACT = 0
                    select @UPDATECONTACTNAME = 0, @UPDATECONTACTEMAIL = 0;

                if @UPDATEORGNAME = 1
                begin
                    update dbo.CONSTITUENT
                        set KEYNAME = @NEWORGNAME,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CHANGEDATE
                    where CONSTITUENT.ID = @CONSTITUENTID
                end

                if @UPDATEADDRESS = 1
                begin
                    if @NEWSTATEID = '00000000-0000-0000-0000-000000000000'
                        set @NEWSTATEID = null;

                    declare @ADDRESSID uniqueidentifier;
                    select top 1
                        @ADDRESSID = ADDRESS.ID
                    from dbo.ADDRESS
                    where ADDRESS.CONSTITUENTID = @CONSTITUENTID
                        and ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID
                    order by ADDRESS.ISPRIMARY desc, ADDRESS.CITY, ADDRESS.ADDRESSBLOCK

                    if not @ADDRESSID is null
                        update dbo.ADDRESS
                            set COUNTRYID = @NEWCOUNTRYID,
                                STATEID = @NEWSTATEID,
                                ADDRESSBLOCK = @NEWADDRESSBLOCK,
                                CITY = @NEWCITY,
                                POSTCODE = @NEWPOSTCODE,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CHANGEDATE
                        where ADDRESS.ID = @ADDRESSID;
                    else
                    begin
                        declare @PRIMARYADDRESS bit
                        set @PRIMARYADDRESS = case when dbo.UFN_ADDRESS_PRIMARYCOUNT(@CONSTITUENTID) = 0 then 1 else 0 end

                        select @SEQUENCE = coalesce(max(SEQUENCE), 0) from dbo.ADDRESS where ADDRESS.CONSTITUENTID = @CONSTITUENTID;
                        set @SEQUENCE = @SEQUENCE + 1;
                        set @ADDRESSID = newid();

                        insert into dbo.ADDRESS(ID, CONSTITUENTID, ADDRESSTYPECODEID, COUNTRYID, STATEID, ADDRESSBLOCK, CITY, POSTCODE, ISPRIMARY, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            values(@ADDRESSID, @CONSTITUENTID, @ADDRESSTYPECODEID, @NEWCOUNTRYID, @NEWSTATEID, @NEWADDRESSBLOCK, @NEWCITY, @NEWPOSTCODE, @PRIMARYADDRESS, @SEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                    end    

                    update dbo.ADDRESSVALIDATIONUPDATE
                    set INFOSOURCECODEID = @INFOSOURCECODEID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CHANGEDATE
                    where ID = @ADDRESSID

                    if @@ROWCOUNT = 0
                        insert into dbo.ADDRESSVALIDATIONUPDATE(ID, INFOSOURCECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values(@ADDRESSID, @INFOSOURCECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);

                end

                if @UPDATEPHONE = 1
                begin
                    declare @PHONEID uniqueidentifier;
                    select top 1 
                        @PHONEID = ID
                    from dbo.PHONE
                    where PHONE.CONSTITUENTID = @CONSTITUENTID
                        and PHONE.PHONETYPECODEID = @PHONETYPECODEID;

                    if not @NEWPHONE is null and len(@NEWPHONE) > 0 
                    begin
                        if not @PHONEID is null
                            update dbo.PHONE
                                set NUMBER = @NEWPHONE,
                                    INFOSOURCECODEID = @INFOSOURCECODEID,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CHANGEDATE
                            where ID = @PHONEID
                        else
                        begin
                            declare @PRIMARYPHONE bit
                            set @PRIMARYPHONE = case when dbo.UFN_PHONE_PRIMARYCOUNT(@CONSTITUENTID) = 0 then 1 else 0 end

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

                            insert into dbo.PHONE(CONSTITUENTID, PHONETYPECODEID, NUMBER, ISPRIMARY, SEQUENCE, INFOSOURCECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values(@CONSTITUENTID, @PHONETYPECODEID, @NEWPHONE, @PRIMARYPHONE, @SEQUENCE, @INFOSOURCECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);    
                        end
                    end
                    else
                    begin
                        --JamesWill 01/02/2008 CR290548-122407 Don't try to update empty phone numbers: delete them

                        if not @PHONEID is null
                            exec dbo.USP_PHONE_DELETEBYID_WITHCHANGEAGENTID @PHONEID, @CHANGEAGENTID;
                    end
                end

                if @UPDATEFAX = 1
                begin
                    declare @FAXID uniqueidentifier;
                    select top 1 
                        @FAXID = ID
                    from dbo.PHONE
                    where PHONE.CONSTITUENTID = @CONSTITUENTID
                        and PHONE.PHONETYPECODEID = @FAXTYPECODEID;

                    if not @NEWFAX is null and len(@NEWFAX) > 0
                    begin
                        if not @FAXID is null
                            update dbo.PHONE
                                set NUMBER = @NEWFAX,
                                    INFOSOURCECODEID = @INFOSOURCECODEID,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CHANGEDATE
                            where ID = @FAXID;
                        else
                        begin
                            select @SEQUENCE = coalesce(max(SEQUENCE), 0) from dbo.PHONE where PHONE.CONSTITUENTID = @CONSTITUENTID;
                            set @SEQUENCE = @SEQUENCE + 1;

                            insert into dbo.PHONE(CONSTITUENTID, PHONETYPECODEID, NUMBER, SEQUENCE, INFOSOURCECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values(@CONSTITUENTID, @FAXTYPECODEID, @NEWFAX, @SEQUENCE, @INFOSOURCECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                        end
                    end
                    else
                    begin
                        if not @FAXID is null and not @FAXTYPECODEID = @PHONETYPECODEID
                            --JamesWill 01/02/2008 CR290548-122407 Don't try to update empty phone numbers: delete them

                            exec dbo.USP_PHONE_DELETEBYID_WITHCHANGEAGENTID @FAXID, @CHANGEAGENTID;
                    end
                end

                if @UPDATEWEBADDRESS = 1
                begin
                    update dbo.CONSTITUENT
                        set WEBADDRESS = @NEWWEBADDRESS,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CHANGEDATE
                    where CONSTITUENT.ID = @CONSTITUENTID;
                end

                if @UPDATEALIAS = 1
                begin
                    declare @ALIASID uniqueidentifier;
                    select top 1 @ALIASID = ID from dbo.ALIAS where ALIAS.CONSTITUENTID = @CONSTITUENTID and ALIAS.ALIASTYPECODEID = @ALIASTYPECODEID;

                    if not @ALIASID is null
                    begin
                        if len(@NEWALIAS) > 0
                            update dbo.ALIAS
                                set KEYNAME = @NEWALIAS,
                                    FIRSTNAME = N'',
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CHANGEDATE
                            where ID = @ALIASID;
                        else
                            exec dbo.USP_ALIAS_DELETEBYID_WITHCHANGEAGENTID @ALIASID, @CHANGEAGENTID;
                    end
                    else
                        if len(@NEWALIAS) > 0
                            insert into dbo.ALIAS(CONSTITUENTID, ALIASTYPECODEID, KEYNAME, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values(@CONSTITUENTID, @ALIASTYPECODEID, @NEWALIAS, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);                            
                end

                if @UPDATEINDUSTRY = 1
                begin
                    declare @INDUSTRYCODEID uniqueidentifier;
                    select top 1 @INDUSTRYCODEID = ID from dbo.INDUSTRYCODE where DESCRIPTION = @NEWINDUSTRY;

                    if @INDUSTRYCODEID is null and len(@NEWINDUSTRY) > 0
                    begin
                        declare @CANADD bit;
                        set @CANADD = 0;

                        --JamesWill 12/05/2007 CR289112-120307 Need to check for sysadmin rights as well as code table rights

                        select @CANADD = coalesce(ISSYSADMIN, 0) from dbo.APPUSER where ID = @APPUSERID;

                        if @CANADD = 0
                            set @CANADD = dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRYADD_IN_SYSTEMROLE(@APPUSERID, '7df7c84f-84db-4620-b620-76b4a2f67581');

                        if @CANADD = 0
                            raiserror('ERR_MFUPDATE_CANNOTADDINDUSTRYCODE_NOPERMISSION', 16, 1);

                        select @SEQUENCE = coalesce(max(SEQUENCE), 0) from dbo.INDUSTRYCODE;
                        set @SEQUENCE = @SEQUENCE + 1;

                        set @INDUSTRYCODEID = newid();

                        insert into dbo.INDUSTRYCODE(ID, DESCRIPTION, ACTIVE, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            values(@INDUSTRYCODEID, @NEWINDUSTRY, 1, @SEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                    end

                    --JamesWill 01/03/2008 CR289847-121107 Not all organizations have an entry in dbo.ORGANIZATIONDATA so add it if it's not already there.

                    if (select count(ID) from dbo.ORGANIZATIONDATA where ID = @CONSTITUENTID) = 0 
                        insert into dbo.ORGANIZATIONDATA(ID, INDUSTRYCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            values(@CONSTITUENTID, @INDUSTRYCODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                    else
                        update dbo.ORGANIZATIONDATA
                            set INDUSTRYCODEID = @INDUSTRYCODEID,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CHANGEDATE
                        where ID = @CONSTITUENTID;
                end

                declare @MGCONDITIONID uniqueidentifier;

                if @UPDATEMATCHINGFACTOR = 1 or @UPDATEMINMATCHPERGIFT = 1 or @UPDATEMAXMATCHPERGIFT = 1 or @UPDATEMAXMATCHTOTAL = 1 or @UPDATEMAXMATCHANNUAL = 1 or @UPDATEMATCHNOTES = 1
                begin
                    select top 1 @MGCONDITIONID = ID from dbo.MATCHINGGIFTCONDITION where ORGANIZATIONID = @CONSTITUENTID and MATCHINGGIFTCONDITIONTYPECODEID = @MATCHINGGIFTCONDITIONTYPECODEID;

                    if not @MGCONDITIONID is null
                    begin
                        if @UPDATEMATCHINGFACTOR = 1 
                            update dbo.MATCHINGGIFTCONDITION
                                set MATCHINGFACTOR = @NEWMATCHINGFACTOR,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CHANGEDATE
                            where ID = @MGCONDITIONID;

                        if @UPDATEMINMATCHPERGIFT = 1
                            update dbo.MATCHINGGIFTCONDITION
                                set MINMATCHPERGIFT = @NEWMINMATCHPERGIFT,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CHANGEDATE
                            where ID = @MGCONDITIONID;

                        if @UPDATEMAXMATCHPERGIFT = 1
                            update dbo.MATCHINGGIFTCONDITION
                                set MAXMATCHPERGIFT = @NEWMAXMATCHPERGIFT,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CHANGEDATE
                            where ID = @MGCONDITIONID;

                        if @UPDATEMAXMATCHTOTAL = 1
                            update dbo.MATCHINGGIFTCONDITION
                                set MAXMATCHTOTAL = @NEWMAXMATCHTOTAL,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CHANGEDATE
                            where ID = @MGCONDITIONID;

                        if @UPDATEMAXMATCHANNUAL = 1
                            update dbo.MATCHINGGIFTCONDITION
                                set MAXMATCHANNUAL = @NEWMAXMATCHANNUAL,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CHANGEDATE
                            where ID = @MGCONDITIONID;

                        if @UPDATEMATCHNOTES = 1
                            update dbo.MATCHINGGIFTCONDITION
                                set NOTES = @NEWMATCHNOTES,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CHANGEDATE
                            where ID = @MGCONDITIONID;            
                    end                        
                    else
                    begin
                        --Set table defaults for fields we aren't updating, so we can just do one big add instead of an add and lots of little updates

                        if @UPDATEMATCHINGFACTOR = 0
                            set @NEWMATCHINGFACTOR = 0;
                        if @UPDATEMINMATCHPERGIFT = 0
                            set @NEWMINMATCHPERGIFT = 0;
                        if @UPDATEMAXMATCHPERGIFT = 0
                            set @NEWMAXMATCHPERGIFT = 0;
                        if @UPDATEMAXMATCHTOTAL = 0
                            set @NEWMAXMATCHTOTAL = 0;
                        if @UPDATEMAXMATCHANNUAL = 0
                            set @NEWMAXMATCHANNUAL = 0;
                        if @UPDATEMATCHNOTES = 0
                            set @NEWMATCHNOTES = N'';

                        declare @MATCHTYPECODE tinyint;
                        set @MATCHTYPECODE = 1;

                        insert into dbo.MATCHINGGIFTCONDITION(ORGANIZATIONID, MATCHINGGIFTCONDITIONTYPECODEID, MATCHINGFACTOR, MAXMATCHANNUAL, MAXMATCHPERGIFT, MAXMATCHTOTAL, MINMATCHPERGIFT, NOTES, [SEQUENCE], ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, MATCHTYPECODE)
                            values(@CONSTITUENTID, @MATCHINGGIFTCONDITIONTYPECODEID, @NEWMATCHINGFACTOR, @NEWMAXMATCHANNUAL, @NEWMAXMATCHPERGIFT, @NEWMAXMATCHTOTAL, @NEWMINMATCHPERGIFT, @NEWMATCHNOTES, coalesce((select max(SEQUENCE) from dbo.MATCHINGGIFTCONDITION where ORGANIZATIONID = @CONSTITUENTID) + 1, 0), @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, @MATCHTYPECODE);

                    end
                end

                if @UPDATECONTACTNAME = 1 or @UPDATECONTACTEMAIL = 1
                begin
                    declare @CONTACTID uniqueidentifier;

                    if @CREATENEWCONTACT = 1 and @UPDATECONTACT = 1
                        raiserror('ERR_MFCONTACT_UPDATEANDCREATE_INVALID', 16, 1);

                    if @CREATENEWCONTACT = 0 and @UPDATECONTACT = 0
                        raiserror('ERR_MFCONTACT_MUSTUPDATEORCREATE', 16, 1);

                    --JamesWill 12/06/2007 CR289213-120407 

                    if @CREATENEWCONTACT = 1 
                    begin
                        set @CONTACTID = newid();

                        if @UPDATECONTACTNAME = 0 
                            raiserror('ERR_MFCREATECONTACT_MUSTUPDATECONTACTNAME', 16, 1);

                        if @CONTACTKEYNAME is null or len(@CONTACTKEYNAME) = 0
                            raiserror('ERR_MFCREATECONTACT_CONTACTKEYNAME_REQUIRED', 16, 1);

                        insert into dbo.CONSTITUENT(ID, KEYNAME, MIDDLENAME, FIRSTNAME, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            values(@CONTACTID, @CONTACTKEYNAME, @CONTACTMIDDLENAME, @CONTACTFIRSTNAME, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);

                        insert into dbo.CONSTITUENTORIGINATION(ID, INFOSOURCECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            values(@CONTACTID, @INFOSOURCECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);

                        --JamesWill 09/09/2008 Work Item 4947

                        insert into dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT(ID, CONSTIT_SECURITY_ATTRIBUTEID, CONSTITUENTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            select
                                newid(),
                                CONSTIT_SECURITY_ATTRIBUTEID,
                                @CONTACTID,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CHANGEDATE,
                                @CHANGEDATE
                            from dbo.CONSTIT_SECURITY_ATTRIBUTE_ASSIGNMENT
                            where CONSTITUENTID = @CONSTITUENTID;

                        insert into dbo.CONSTITUENTSITE
                        (
                            ID, 
                            SITEID, 
                            CONSTITUENTID, 
                            ADDEDBYID, 
                            CHANGEDBYID, 
                            DATEADDED, 
                            DATECHANGED
                        )
                        select
                            newid(),
                            SITEID,
                            @CONTACTID,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CHANGEDATE,
                            @CHANGEDATE
                        from dbo.CONSTITUENTSITE
                        where CONSTITUENTID = @CONSTITUENTID;                            

                        insert into dbo.[NAMEFORMAT]
                            ([CONSTITUENTID]
                            ,[NAMEFORMATTYPECODEID]
                            ,[NAMEFORMATFUNCTIONID]
                            ,[ADDEDBYID]
                            ,[CHANGEDBYID]
                            ,[DATEADDED]
                            ,[DATECHANGED]
                            ,[PRIMARYADDRESSEE]
                            ,[PRIMARYSALUTATION])
                        select
                            @CONTACTID
                            ,NFD.NAMEFORMATTYPECODEID
                            ,NFD.NAMEFORMATFUNCTIONID
                            ,@CHANGEAGENTID
                            ,@CHANGEAGENTID
                            ,@CHANGEDATE
                            ,@CHANGEDATE
                            ,NFD.PRIMARYADDRESSEE
                            ,NFD.PRIMARYSALUTATION
                        from dbo.NAMEFORMATDEFAULT as NFD
                        where NFD.APPLYTOCODE = 0 
                    end

                    if @UPDATECONTACT = 1
                        set @CONTACTID = @CONTACTLINKID;

                    if not @CONTACTID is null
                    begin
                        declare @RELATIONSHIPID uniqueidentifier;
                        select top 1 @RELATIONSHIPID = ID                             
                        from dbo.RELATIONSHIP 
                        where RELATIONSHIPCONSTITUENTID = @CONTACTID 
                            and RECIPROCALCONSTITUENTID = @CONSTITUENTID 
                            and RELATIONSHIPTYPECODEID = @CONTACTRELATIONSHIPTYPECODEID 
                            and RECIPROCALTYPECODEID = @CONTACTRECIPROCALRELATIONSHIPTYPECODEID;

                        if @RELATIONSHIPID is null
                        begin
                            insert into dbo.RELATIONSHIP(RELATIONSHIPCONSTITUENTID, RECIPROCALCONSTITUENTID, RELATIONSHIPTYPECODEID, RECIPROCALTYPECODEID, ISCONTACT, CONTACTTYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                values(@CONTACTID, @CONSTITUENTID, @CONTACTRELATIONSHIPTYPECODEID, @CONTACTRECIPROCALRELATIONSHIPTYPECODEID, 1, @CONTACTTYPECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                        end
                        else
                        begin
                            declare @RELATIONSHIPISCONTACT bit;
                            declare @RELATIONSHIPCONTACTTYPECODEID uniqueidentifier;

                            select @RELATIONSHIPISCONTACT = ISCONTACT, @RELATIONSHIPCONTACTTYPECODEID = CONTACTTYPECODEID from dbo.RELATIONSHIP where ID = @RELATIONSHIPID;

                            if @RELATIONSHIPISCONTACT = 0
                                update dbo.RELATIONSHIP
                                    set ISCONTACT = 1,
                                        CONTACTTYPECODEID = @CONTACTTYPECODEID,
                                        CHANGEDBYID = @CHANGEAGENTID,
                                        DATECHANGED = @CHANGEDATE
                                where ID = @RELATIONSHIPID
                            else if @RELATIONSHIPCONTACTTYPECODEID <> @CONTACTTYPECODEID
                                update dbo.RELATIONSHIP
                                    set CONTACTTYPECODEID = @CONTACTTYPECODEID,
                                        CHANGEDBYID = @CHANGEAGENTID,
                                        DATECHANGED = @CHANGEDATE
                                where ID = @RELATIONSHIPID
                        end

                        if @UPDATECONTACTNAME = 1
                        update dbo.CONSTITUENT
                            set KEYNAME = @CONTACTKEYNAME,
                                MIDDLENAME = @CONTACTMIDDLENAME,
                                FIRSTNAME = @CONTACTFIRSTNAME,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CHANGEDATE
                        where CONSTITUENT.ID = @CONTACTID;

                        if @UPDATECONTACTEMAIL = 1 and not @CONTACTID is null
                        begin
                            declare @RECIPROCALRELATIONSHIPID uniqueidentifier;
                            select top 1
                              @RECIPROCALRELATIONSHIPID = ID
                            from
                              dbo.RELATIONSHIP 
                            where
                              RECIPROCALCONSTITUENTID = @CONTACTID and
                              RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
                              RECIPROCALTYPECODEID = @CONTACTRELATIONSHIPTYPECODEID and
                              RELATIONSHIPTYPECODEID = @CONTACTRECIPROCALRELATIONSHIPTYPECODEID;

                            declare @EMAILID uniqueidentifier;
                            select top 1 @EMAILID = ID from dbo.EMAILADDRESS where CONSTITUENTID = @CONTACTID and EMAILADDRESSTYPECODEID = @CONTACTEMAILADDRESSTYPECODEID;

                            if not @EMAILID is null
                                update dbo.EMAILADDRESS
                                    set EMAILADDRESS = @NEWCONTACTEMAIL,
                                        INFOSOURCECODEID = @INFOSOURCECODEID,
                                        RELATIONSHIPID = @RECIPROCALRELATIONSHIPID,
                                        CHANGEDBYID = @CHANGEAGENTID,
                                        DATECHANGED = @CHANGEDATE
                                where ID = @EMAILID;
                            else
                            begin
                                declare @PRIMARYCONTACTEMAIL bit
                                set @PRIMARYCONTACTEMAIL = case when dbo.UFN_EMAILADDRESS_PRIMARYCOUNT(@CONTACTID) = 0 then 1 else 0 end

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

                                insert into dbo.EMAILADDRESS(CONSTITUENTID, EMAILADDRESSTYPECODEID, EMAILADDRESS, ISPRIMARY, SEQUENCE, INFOSOURCECODEID, RELATIONSHIPID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                                    values(@CONTACTID, @CONTACTEMAILADDRESSTYPECODEID, @NEWCONTACTEMAIL, @PRIMARYCONTACTEMAIL, @SEQUENCE, @INFOSOURCECODEID, @RECIPROCALRELATIONSHIPID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                            end

                        end    
                    end    


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