USP_DATAFORMTEMPLATE_ADD_RE7INTEGRATIONFROMRE7DATA2

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_RE7INTEGRATIONFROMRE7DATA2 (
                        @ID uniqueidentifier = null output,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @CURRENTAPPUSERID uniqueidentifier,
                        @CONSTITUENTID uniqueidentifier,
                        @SYNCBIOGRAPHICAL bit = 0,
                        @LASTNAME nvarchar(100) = '',
                        @FIRSTNAME nvarchar(50) = '',
                        @MIDDLENAME nvarchar(50) = '',
                        @DATEOFBIRTH dbo.UDT_FUZZYDATE = '00000000',
                        @SYNCADDRESS bit = 0,                  
                        @ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
                        @ADDRESS_CITY nvarchar(50) = '',
                        @ADDRESS_STATE nvarchar(50) = '',
                        @ADDRESS_STATE_ABBREV nvarchar(5) = '',                          
                        @ADDRESS_POSTCODE nvarchar(12) = '',
                        @ADDRESS_COUNTRY nvarchar(50) = '',
                        @ADDRESS_COUNTRY_ABBREV nvarchar(5) = '',
                        @ADDRESS_ADDRESSTYPE nvarchar(60) = '',
                        @SYNCBUSINESS bit = 0,                  
                        @BUSINESS_NAME nvarchar(100) = '',
                        @BUSINESS_ADDRESSBLOCK nvarchar(150) = '',
                        @BUSINESS_CITY nvarchar(50) = '',
                        @BUSINESS_STATE nvarchar(50) = null,
                        @BUSINESS_STATE_ABBREV nvarchar(5) = '',                  
                        @BUSINESS_POSTCODE nvarchar(12) = '',
                        @BUSINESS_COUNTRY nvarchar(50) = '',
                        @BUSINESS_COUNTRY_ABBREV nvarchar(5) = '',                  
                        @BUSINESS_ADDRESSTYPE nvarchar(60) = '',
                        @SYNCGIVING bit = 0,                  
                        @FIRSTGIFTAMOUNT money = 0,
                        @FIRSTGIFTDATE datetime = null,
                        @LATESTGIFTAMOUNT money = 0,
                        @LATESTGIFTDATE datetime = null,
                        @LARGESTGIFTAMOUNT money = 0,
                        @LARGESTGIFTDATE datetime = null,
                        @TOTALGIFTSGIVEN int = 0,
                        @TOTALGIFTAMOUNT money = 0,
                        @FIRSTGIFTTYPE nvarchar(100) = null,
                        @FIRSTGIFTDESIGNATION nvarchar(100) = null,
                        @LARGESTGIFTTYPE nvarchar(100) = null,
                        @LARGESTGIFTDESIGNATION nvarchar(100) = null,
                        @LATESTGIFTTYPE nvarchar(100) = null,
                        @LATESTGIFTDESIGNATION nvarchar(100) = null,
                        @NICKNAME nvarchar(50) = null,
                        @TITLE1 nvarchar(60) = null,
                        @TITLE2 nvarchar(60) = null,
                        @SUFFIX1 nvarchar(60) = null,
                        @SUFFIX2 nvarchar(60) = null,
                        @GENDER nvarchar(50) = null,
                        @RE7CONSTITUENTID nvarchar(20) = null,
                        @RE7RECORDID int = 0,
                        @SYNCPHONES bit = 0,
                        @PHONES xml = null,
            @ISINACTIVE bit = 0,
            @ISDECEASED bit = 0
                    ) as
                        set nocount on;

                        set @ID = @CONSTITUENTID;

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

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

                        begin try

                            -- Update Biographical Information     

                            if @SYNCBIOGRAPHICAL = 1 and
                               @LASTNAME != ''
                            begin

                                -- Get title 1 type code

                                declare @TITLECODEID uniqueidentifier

                                if len(@TITLE1) > 0 begin
                                    select 
                                        @TITLECODEID = TITLECODE.ID
                                    from
                                        dbo.TITLECODE
                                    where
                                        TITLECODE.DESCRIPTION = @TITLE1

                                    if @TITLECODEID is null
                                    begin
                                        exec dbo.USP_TITLECODE_CREATEENTRY
                                            @TITLE1,
                                            1,
                                            null,
                                            @CHANGEAGENTID,
                                            @TITLECODEID output

                                    end
                                end

                                -- Get title 2 type code

                                declare @TITLE2CODEID uniqueidentifier

                                if len(@TITLE2) > 0 begin
                                    select 
                                        @TITLE2CODEID = TITLECODE.ID
                                    from
                                        dbo.TITLECODE
                                    where
                                        TITLECODE.DESCRIPTION = @TITLE2

                                    if @TITLE2CODEID is null
                                    begin
                                        exec dbo.USP_TITLECODE_CREATEENTRY
                                            @TITLE2,
                                            1,
                                            null,
                                            @CHANGEAGENTID,
                                            @TITLE2CODEID output

                                    end
                                end

                                -- Get suffix 1 type code

                                declare @SUFFIXCODEID uniqueidentifier

                                if len(@SUFFIX1) > 0 begin
                                    select 
                                        @SUFFIXCODEID = SUFFIXCODE.ID
                                    from
                                        dbo.SUFFIXCODE
                                    where
                                        SUFFIXCODE.DESCRIPTION = @SUFFIX1

                                    if @SUFFIXCODEID is null
                                    begin
                                        exec dbo.USP_SUFFIXCODE_CREATEENTRY
                                            @SUFFIX1,
                                            1,
                                            null,
                                            @CHANGEAGENTID,
                                            @SUFFIXCODEID output

                                    end
                                end

                                -- Get suffix 2 type code

                                declare @SUFFIX2CODEID uniqueidentifier

                                if len(@SUFFIX2) > 0 begin
                                    select 
                                        @SUFFIX2CODEID = SUFFIXCODE.ID
                                    from
                                        dbo.SUFFIXCODE
                                    where
                                        SUFFIXCODE.DESCRIPTION = @SUFFIX2

                                    if @SUFFIX2CODEID is null
                                    begin
                                        exec dbo.USP_SUFFIXCODE_CREATEENTRY
                                            @SUFFIX2,
                                            1,
                                            null,
                                            @CHANGEAGENTID,
                                            @SUFFIX2CODEID output

                                    end
                                end

                                --update existing constituent

                                if (select count(ID) from dbo.CONSTITUENT where CONSTITUENT.ID = @ID) > 0
                                begin

                                    -- Get alternate lookup ID type code for old RP lookup ID

                                    declare @OLDRPTYPECODEID uniqueidentifier

                                    select 
                                        @OLDRPTYPECODEID = ALTERNATELOOKUPIDTYPECODE.ID
                                    from
                                        dbo.ALTERNATELOOKUPIDTYPECODE
                                    where
                                        ALTERNATELOOKUPIDTYPECODE.DESCRIPTION = 'Original RP Lookup ID'

                                    if @OLDRPTYPECODEID is null
                                    begin
                                        exec dbo.USP_ALTERNATELOOKUPIDTYPECODE_CREATEENTRY
                                            'Original RP Lookup ID',
                                            1,
                                            null,
                                            @CHANGEAGENTID,
                                            @OLDRPTYPECODEID output                
                                    end

                                    -- Insert old lookup ID into alternate lookup ID

                                    if not exists(select 1 from ALTERNATELOOKUPID where CONSTITUENTID = @ID and ALTERNATELOOKUPIDTYPECODEID = @OLDRPTYPECODEID) 
                                    begin

                                        declare @OLDLOOKUPID nvarchar(100)
                                        select @OLDLOOKUPID = LOOKUPID from dbo.CONSTITUENT where ID = @ID

                                        if @OLDLOOKUPID <> @RE7CONSTITUENTID 
                                          insert into dbo.ALTERNATELOOKUPID(
                                              CONSTITUENTID,
                                              ALTERNATELOOKUPIDTYPECODEID,
                                              ALTERNATELOOKUPID,
                                              ADDEDBYID,
                                              CHANGEDBYID,
                                              DATEADDED,
                                              DATECHANGED
                                          )values(
                                              @ID,
                                              @OLDRPTYPECODEID,
                                              @OLDLOOKUPID,
                                              @CHANGEAGENTID,
                                              @CHANGEAGENTID,
                                              @CURRENTDATE,
                                              @CURRENTDATE
                                          )
                                    end

                                    update
                                      dbo.CONSTITUENT
                                    set
                                      KEYNAME = coalesce(@LASTNAME, ''),
                                      FIRSTNAME = coalesce(@FIRSTNAME, ''),
                                      MIDDLENAME = coalesce(@MIDDLENAME, ''),
                                      NICKNAME = coalesce(@NICKNAME, ''),
                                      BIRTHDATE = coalesce(@DATEOFBIRTH, '00000000'),
                                      GENDERCODE = case when @GENDER = 'Male' then 1 when @GENDER = 'Female' then 2 else 0 end,
                                      TITLECODEID = @TITLECODEID,
                                      TITLE2CODEID = @TITLE2CODEID,
                                      SUFFIXCODEID = @SUFFIXCODEID,
                                      SUFFIX2CODEID = @SUFFIX2CODEID,
                                      CUSTOMIDENTIFIER = coalesce(@RE7CONSTITUENTID, ''),
                                      CHANGEDBYID = @CHANGEAGENTID,
                                      DATECHANGED = @CURRENTDATE
                                    where
                                      ID = @ID;

                --Mark Active

                if exists(select top 1 ID from dbo.CONSTITUENT where ID = @ID and ISINACTIVE = 1) and @ISINACTIVE = 0
                  exec dbo.USP_RECORDOPERATION_MARKCONSITUENTACTIVE @ID;
                else if exists(select top 1 ID from dbo.CONSTITUENT where ID = @ID and ISINACTIVE = 0) and @ISINACTIVE = 1
                  exec dbo.USP_RECORDOPERATION_MARKCONSITUENTINACTIVE @ID;

                                end
                                --add new constituent

                                else
                                begin                                    
                                    insert into dbo.CONSTITUENT(
                                        ID,
                                        KEYNAME,
                                        FIRSTNAME,
                                        MIDDLENAME,
                                        NICKNAME,
                                        BIRTHDATE,
                                        GENDERCODE,
                                        TITLECODEID,
                                        TITLE2CODEID,
                                        SUFFIXCODEID,
                                        SUFFIX2CODEID,
                    ISINACTIVE,
                                        CUSTOMIDENTIFIER,
                                        CHANGEDBYID,
                                        ADDEDBYID
                                    )values(
                                        @ID,
                                        coalesce(@LASTNAME, ''),
                                        coalesce(@FIRSTNAME, ''),
                                        coalesce(@MIDDLENAME, ''),
                                        coalesce(@NICKNAME, ''),
                                        coalesce(@DATEOFBIRTH, '00000000'),
                                        case when @GENDER = 'Male' then 1 when @GENDER = 'Female' then 2 else 0 end,
                                        @TITLECODEID,
                                        @TITLE2CODEID,
                                        @SUFFIXCODEID,
                                        @SUFFIX2CODEID,
                    @ISINACTIVE,
                                        coalesce(@RE7CONSTITUENTID, ''),
                                        @CHANGEAGENTID,
                                        @CHANGEAGENTID
                                    )    
                                end


                if exists(select top 1 1 from dbo.DECEASEDCONSTITUENT where ID = @ID)
                begin
                  if @ISDECEASED = 0
                    delete from dbo.DECEASEDCONSTITUENT where ID = @ID;
                end
                else
                begin
                  if @ISDECEASED = 1
                    insert into dbo.DECEASEDCONSTITUENT (
                      ID,
                      ADDEDBYID,
                      CHANGEDBYID
                    )
                    values (
                      @ID,
                      @CHANGEAGENTID,
                      @CHANGEAGENTID
                    );
                end


                                -- Link constituent

                                if @RE7RECORDID > 0 begin
                                    if not exists(select 1 from dbo.RE7INTEGRATIONCONSTITUENTMAP where ID = @ID)
                                        insert into dbo.RE7INTEGRATIONCONSTITUENTMAP (
                                            ID,
                                            RE7RECORDID,
                                            ADDEDBYID,
                                            CHANGEDBYID,
                                            DATEADDED,
                                            DATECHANGED)
                                        values (
                                            @ID,
                                            @RE7RECORDID,
                                            @CHANGEAGENTID,
                                            @CHANGEAGENTID,
                                            @CURRENTDATE,
                                            @CURRENTDATE);
                                end

                            end


                            -- Update Preferred Address Information

                            if @SYNCADDRESS = 1 and
                               @ADDRESS_ADDRESSTYPE != '' and
                               (@ADDRESS_STATE != '' or @ADDRESS_STATE_ABBREV != '' or  @ADDRESS_POSTCODE !='' or @ADDRESS_CITY !='' or @ADDRESS_ADDRESSBLOCK != '')        

                            begin         
                                declare @CURRENT_ADDRESS_ID uniqueidentifier

                                select 
                                    @CURRENT_ADDRESS_ID = ADDRESS.ID
                                from
                                    dbo.ADDRESS           
                                left outer join dbo.STATE 
                                    on ADDRESS.STATEID = STATE.ID
                                left outer join dbo.COUNTRY
                                    on ADDRESS.COUNTRYID = COUNTRY.ID
                                where
                                    ADDRESS.CONSTITUENTID = @ID and
                                    ADDRESS.ADDRESSBLOCK  = @ADDRESS_ADDRESSBLOCK and
                                    ADDRESS.CITY          = @ADDRESS_CITY and
                                    coalesce(STATE.ABBREVIATION,'') = coalesce(@ADDRESS_STATE_ABBREV,'') and
                                    ADDRESS.POSTCODE      = @ADDRESS_POSTCODE and
                                    (coalesce(COUNTRY.DESCRIPTION,'') = coalesce(@ADDRESS_COUNTRY,'') or @ADDRESS_COUNTRY = '')

                -- Get address type code

                                declare @ADDRESSTYPECODEID uniqueidentifier

                                select 
                                    @ADDRESSTYPECODEID = ADDRESSTYPECODE.ID
                                from
                                    dbo.ADDRESSTYPECODE
                                where
                                    ADDRESSTYPECODE.DESCRIPTION = @ADDRESS_ADDRESSTYPE               

                                if @ADDRESSTYPECODEID is null
                                begin
                                    exec dbo.USP_ADDRESSTYPECODE_CREATEENTRY
                                        @ADDRESS_ADDRESSTYPE,
                                        1,
                                        null,
                                        @CHANGEAGENTID,
                                        @ADDRESSTYPECODEID output

                                end 


                                if @CURRENT_ADDRESS_ID is not null
                                begin
                                    update
                                        dbo.ADDRESS
                                    set
                                        ADDRESS.ISPRIMARY = 0,
                                        ADDRESS.CHANGEDBYID = @CHANGEAGENTID,
                                        ADDRESS.DATECHANGED = getdate()                            
                                    where
                                        ADDRESS.ISPRIMARY = 1 and
                                        ADDRESS.CONSTITUENTID = @ID and
                                        ADDRESS.ID != @CURRENT_ADDRESS_ID

                                    update 
                                        dbo.ADDRESS
                                    set
                                        ADDRESS.ISPRIMARY = 1,
                                        ADDRESS.CHANGEDBYID = @CHANGEAGENTID,
                                        ADDRESS.DATECHANGED = getdate(),
                    ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID
                                    where
                                        ADDRESS.ID = @CURRENT_ADDRESS_ID and
                                        ADDRESS.CONSTITUENTID = @ID and                              
                                        (ADDRESS.ISPRIMARY = 0 or 
                      (ADDRESS.ADDRESSTYPECODEID is null and @ADDRESSTYPECODEID is not null) or 
                       ADDRESS.ADDRESSTYPECODEID <> @ADDRESSTYPECODEID)
                                end
                                else                        
                                begin 
                                    declare @COUNTRYID uniqueidentifier;
                                    if @ADDRESS_COUNTRY = ''
                                    begin
                                        set @COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT()
                                    end

                                    if @COUNTRYID is null
                                    begin
                                        set @COUNTRYID = dbo.UFN_COUNTRY_GETID(@ADDRESS_COUNTRY,0)
                                    end

                                    if @COUNTRYID is null
                                    begin
                                        set @COUNTRYID = dbo.UFN_COUNTRY_GETID(@ADDRESS_COUNTRY_ABBREV,1)
                                    end

                                    if @COUNTRYID is null
                                    begin
                                        declare @ADDRESSFORMATID uniqueidentifier

                                        select 
                                            @ADDRESSFORMATID = COUNTRYADDRESSFORMAT.ID
                                        from
                                            dbo.COUNTRYADDRESSFORMAT
                                        where 
                                            COUNTRYADDRESSFORMAT.FORMATNAME = 'United States'

                                        if @ADDRESSFORMATID is null

                                        begin
                                            select top(1)
                                                @ADDRESSFORMATID = COUNTRYADDRESSFORMAT.ID    
                                            from
                                                dbo.COUNTRYADDRESSFORMAT    
                                        end        

                                        if @ADDRESSFORMATID is not null
                                        begin
                                            exec dbo.USP_DATAFORMTEMPLATE_ADD_COUNTRY 
                                                @COUNTRYID output,
                                                @ADDRESS_COUNTRY,
                                                @ADDRESS_COUNTRY_ABBREV,
                                                @ADDRESSFORMATID,
                                                0, --INACTIVE = false

                                                0, --ALLOWFALIDATION = false

                                                0, --VALIDATIONCOUNTRYCODE = false

                                                @CHANGEAGENTID
                                        end
                                    end

                                    -- Get State ID if possible

                                    declare @STATEID uniqueidentifier

                                    if @COUNTRYID is not null
                                    begin

                                        set @STATEID = dbo.UFN_STATE_GETID(@COUNTRYID,@ADDRESS_STATE,0)

                                        if @STATEID is null
                                        begin
                                            set @STATEID = dbo.UFN_STATE_GETID(@COUNTRYID,@ADDRESS_STATE_ABBREV,1) 
                                        end

                                        if @STATEID is null and 
                                           @ADDRESS_STATE != '' and
                                           @ADDRESS_STATE_ABBREV != ''
                                        begin
                                            exec dbo.USP_DATAFORMTEMPLATE_ADD_STATE
                                                @STATEID output,
                                                @COUNTRYID,
                                                @ADDRESS_STATE,
                                                @ADDRESS_STATE_ABBREV,
                                                0,
                                                @CHANGEAGENTID

                                        end                                                   
                                    end

                                    -- Add address

                                    if @COUNTRYID is not null and
                                       @ADDRESSTYPECODEID is not null
                                    begin
                                        exec dbo.USP_DATAFORMTEMPLATE_ADD_ADDRESS
                                            null,
                                            @CHANGEAGENTID,
                                            @ID,
                                            @ADDRESSTYPECODEID,
                                            1,
                                            0,
                                            '0000',
                                            '0000',
                                            @COUNTRYID,
                                            @STATEID,
                                            @ADDRESS_ADDRESSBLOCK,
                                            @ADDRESS_CITY,
                                            @ADDRESS_POSTCODE
                                    end                
                                end                    
                            end


                            -- Update Phone Information

                            if @SYNCPHONES = 1 and
                                @PHONES is not null
                            begin

                                declare @PHONETABLE table(
                                    PHONEID uniqueidentifier,
                                    RE7PHONEID int,
                                    PHONETYPE nvarchar(100),
                                    NUMBER nvarchar(100),
                                    DONOTCALL bit,
                                    ISPRIMARY bit,
                                    SEQUENCE int,
                                    ISNEWPHONE bit,
                                    ISNEWMAP bit
                                )

                                insert into @PHONETABLE
                                select
                                    coalesce(MAP.ID, PHONE.ID, newid()),
                                    PHONES.RE7PHONEID,
                                    PHONES.PHONETYPE,
                                    PHONES.NUMBER,
                                    coalesce(PHONES.DONOTCALL, 0),
                                    coalesce(PHONES.ISPRIMARY, 0),
                                    row_number() over (order by PHONES.ISPRIMARY desc),
                                    case when PHONE.ID is null and MAP.ID is null then 1 else 0 end,
                                    case when MAP.ID is null then 1 else 0 end
                                from 
                                    dbo.UFN_RE7INTEGRATION_GETPHONES_FROMXML_ATTRIBUTES(@PHONES) PHONES
                                left join
                                    dbo.RE7INTEGRATIONPHONEMAP MAP on MAP.RE7PHONEID = PHONES.RE7PHONEID
                                left join
                                    dbo.PHONE on PHONES.PHONETYPE = (select DESCRIPTION from dbo.PHONETYPECODE where ID = PHONE.PHONETYPECODEID)
                                        and (PHONES.NUMBER = PHONE.NUMBER or PHONES.NUMBER = PHONE.NUMBERNOFORMAT)
                    and (PHONE.CONSTITUENTID = @ID)
                                where
                                    len(coalesce(PHONES.NUMBER, '')) > 0

                --Get distinct type codes to prevent attempting to add duplicates

                declare @PHONETYPECODETABLE table
                (
                  TYPECODEDESCRIPTION nvarchar(100)
                )

                insert into @PHONETYPECODETABLE
                select distinct PHONETYPE from @PHONETABLE

                                -- Insert any code table values that aren't present

                                insert into dbo.PHONETYPECODE
                                (
                                    DESCRIPTION,
                                    ACTIVE,
                                    SEQUENCE,
                                    ADDEDBYID,
                                    CHANGEDBYID,
                                    DATEADDED,
                                    DATECHANGED
                                )
                                select
                                    T.TYPECODEDESCRIPTION,
                                    1,
                                    row_number() over (order by T.TYPECODEDESCRIPTION asc) + (select coalesce(max(SEQUENCE), 1) from dbo.PHONETYPECODE),
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CURRENTDATE,
                                    @CURRENTDATE
                                from 
                                    @PHONETYPECODETABLE T
                                left join
                                    dbo.PHONETYPECODE PTC on T.TYPECODEDESCRIPTION = PTC.DESCRIPTION
                                where
                                    PTC.ID is null


                                -- Clear the constituent's primary flag so we don't clash with constraints

                                update dbo.PHONE
                                    set ISPRIMARY = 0
                                where
                                    CONSTITUENTID = @ID and ISPRIMARY = 1


                                -- Update any phone numbers that already exist

                                update dbo.PHONE
                                set
                                    PHONETYPECODEID = (select ID from dbo.PHONETYPECODE where DESCRIPTION = T.PHONETYPE),
                                    NUMBER = T.NUMBER,
                                    DONOTCALL = T.DONOTCALL,
                                    ISPRIMARY = T.ISPRIMARY,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CURRENTDATE
                                from
                                    @PHONETABLE T
                                where
                                    ID = T.PHONEID and T.ISNEWPHONE = 0

                                -- Insert any phone numbers that aren't present

                                insert into dbo.PHONE(
                                    ID,
                                    CONSTITUENTID,
                                    PHONETYPECODEID,
                                    NUMBER,
                                    ISPRIMARY,
                                    SEQUENCE,
                                    ADDEDBYID,
                                    CHANGEDBYID,
                                    DATEADDED,
                                    DATECHANGED,
                                    DONOTCALL,
                                    COUNTRYID,
                                    ISCONFIDENTIAL,
                                    ORIGINCODE
                                )
                                select
                                    T.PHONEID,
                                    @ID,
                                    (select ID from dbo.PHONETYPECODE where DESCRIPTION = T.PHONETYPE),
                                    T.NUMBER,
                                    T.ISPRIMARY,
                                    T.SEQUENCE + (select coalesce(max(SEQUENCE), 0) from dbo.PHONE where CONSTITUENTID = @ID),
                                    @CHANGEAGENTID,
                                    @CHANGEAGENTID,
                                    @CURRENTDATE,
                                    @CURRENTDATE,
                                    T.DONOTCALL,
                                    null,
                                    0,
                                    0
                                from 
                                    @PHONETABLE T
                                where
                                    T.ISNEWPHONE = 1

                                -- Update any phone numbers that already exist

                                update RE7INTEGRATIONPHONEMAP
                                set
                                    RE7PHONEID = T.RE7PHONEID,
                                    CHANGEDBYID = @CHANGEAGENTID,
                                    DATECHANGED = @CURRENTDATE
                                from dbo.RE7INTEGRATIONPHONEMAP
                                inner join @PHONETABLE T
                                    on T.PHONEID = RE7INTEGRATIONPHONEMAP.ID
                                where
                                    T.ISNEWMAP = 1

                                if @@rowcount = 0
                                  -- Add new mappings between RE phone numbers and RP phone numbers

                                  insert into dbo.RE7INTEGRATIONPHONEMAP(
                                      ID,
                                      RE7PHONEID,
                                      ADDEDBYID,
                                      CHANGEDBYID,
                                      DATEADDED,
                                      DATECHANGED
                                  )
                                  select
                                      T.PHONEID,
                                      T.RE7PHONEID,
                                      @CHANGEAGENTID,
                                      @CHANGEAGENTID,
                                      @CURRENTDATE,
                                      @CURRENTDATE
                                  from
                                      @PHONETABLE T
                                  where
                                      T.ISNEWMAP = 1;


                                -- Delete mappings that are no longer valid because the

                                -- number was deleted in RE7

                                delete from dbo.RE7INTEGRATIONPHONEMAP
                                where ID in (
                                    select ID 
                                    from
                                        dbo.PHONE
                                    left join
                                        @PHONETABLE T on PHONE.ID = T.PHONEID
                                    where
                                        PHONE.CONSTITUENTID = @ID and T.PHONEID is null
                                )
                            end


                            -- Update Preferred Business Information

                            if @SYNCBUSINESS = 1 and
                               @BUSINESS_NAME != ''
                            begin         
                                declare @RELATIONSHIP_ID uniqueidentifier
                                declare @BUSINESS_ID uniqueidentifier

                                -- Check current business relationships

                                select 
                                    @RELATIONSHIP_ID = RELATIONSHIP.ID,
                                    @BUSINESS_ID = BUSINESS.ID
                                from
                                    dbo.RELATIONSHIP
                                left outer join
                                    dbo.CONSTITUENT as BUSINESS
                                on
                                    BUSINESS.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
                                where 
                                    RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID and
                                    BUSINESS.NAME = @BUSINESS_NAME


                                if @RELATIONSHIP_ID is not null

                                --Set relationship to primary business

                                begin
                                    update
                                        dbo.RELATIONSHIP
                                    set
                                        RELATIONSHIP.ISPRIMARYBUSINESS = 1,
                                        RELATIONSHIP.CHANGEDBYID = @CHANGEAGENTID,
                                        RELATIONSHIP.DATECHANGED = getdate()    
                                    where
                                        RELATIONSHIP.ID = @RELATIONSHIP_ID
                                end        

                                else

                                begin
                                    -- Check if organization exists

                                    select
                                        @BUSINESS_ID = BUSINESS.ID
                                    from
                                        dbo.CONSTITUENT as BUSINESS
                                    where 
                                        BUSINESS.KEYNAME = @BUSINESS_NAME and 
                                        BUSINESS.ISORGANIZATION = 1

                                    if @BUSINESS_ID is null
                                    begin

                                        set @BUSINESS_ID = newid()

                                        insert into dbo.CONSTITUENT
                                        (
                                            ID,
                                            ISORGANIZATION,
                                            KEYNAME,
                                            ADDEDBYID,
                                            CHANGEDBYID
                                        ) values (
                                            @BUSINESS_ID,
                                            1,
                                            @BUSINESS_NAME,
                                            @CHANGEAGENTID,
                                            @CHANGEAGENTID
                                        );                
                                    end

                                    declare @RECIPROCALTYPECODEID uniqueidentifier
                                    declare @RELATIONSHIPTYPECODEID uniqueidentifier

                                    select 
                                        @RECIPROCALTYPECODEID = RELATIONSHIPTYPECODE.ID
                                    from
                                        dbo.RELATIONSHIPTYPECODE
                                    where 
                                        RELATIONSHIPTYPECODE.DESCRIPTION = 'Employer'

                                    if @RECIPROCALTYPECODEID is null
                                    begin
                                        exec USP_RELATIONSHIPTYPECODE_CREATEENTRY 'Employer',
                                            1,
                                            null,
                                            @CHANGEAGENTID,
                                            @RECIPROCALTYPECODEID output;
                                    end    

                                    select 
                                        @RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODE.ID
                                    from
                                        dbo.RELATIONSHIPTYPECODE
                                    where 
                                        RELATIONSHIPTYPECODE.DESCRIPTION = 'Employee'                        

                                    if @RELATIONSHIPTYPECODEID is null
                                    begin
                                        exec USP_RELATIONSHIPTYPECODE_CREATEENTRY 'Employee',
                                            1,
                                            null,
                                            @CHANGEAGENTID,
                                            @RELATIONSHIPTYPECODEID output;
                                    end



                                    exec dbo.USP_DATAFORMTEMPLATE_ADD_INDTOORG_RELATIONSHIP
                                        @RELATIONSHIP_ID,
                                        @CHANGEAGENTID,
                                        @ID,
                                        @BUSINESS_ID,
                                        @RECIPROCALTYPECODEID,             
                                        @RELATIONSHIPTYPECODEID,
                                        null,
                                        null,
                                        0,
                                        0,
                                        null,
                                        '',
                                        1,
                                        0
                                end   

                                --Set business address

                                if @BUSINESS_ADDRESSTYPE != '' and
                                  (@BUSINESS_STATE != '' or @BUSINESS_STATE_ABBREV != '' or  @BUSINESS_POSTCODE !='' or @BUSINESS_CITY !='' or @BUSINESS_ADDRESSBLOCK != '')


                                begin                      
                                    declare @CURRENT_BUSINESS_ADDRESS_ID uniqueidentifier

                                    select 
                                        @CURRENT_BUSINESS_ADDRESS_ID = ADDRESS.ID
                                    from
                                        dbo.ADDRESS           
                                    left outer join dbo.STATE 
                                        on ADDRESS.STATEID = STATE.ID
                                    left outer join dbo.COUNTRY
                                        on ADDRESS.COUNTRYID = COUNTRY.ID
                                    where
                                        ADDRESS.CONSTITUENTID             = @BUSINESS_ID and
                                        ADDRESS.ADDRESSBLOCK             = @BUSINESS_ADDRESSBLOCK and
                                        ADDRESS.CITY                     = @BUSINESS_CITY and
                                        coalesce(STATE.ABBREVIATION,'')  = coalesce(@BUSINESS_STATE_ABBREV,'')and
                                        ADDRESS.POSTCODE                 = @BUSINESS_POSTCODE and
                                        (coalesce(COUNTRY.DESCRIPTION,'') = coalesce(@BUSINESS_COUNTRY,'') or @BUSINESS_COUNTRY = '')

                          -- Get business address type code

                                    declare @BUSINESS_ADDRESSTYPECODEID uniqueidentifier

                                    select 
                                        @BUSINESS_ADDRESSTYPECODEID = ADDRESSTYPECODE.ID
                                    from
                                        dbo.ADDRESSTYPECODE
                                    where
                                        ADDRESSTYPECODE.DESCRIPTION = @BUSINESS_ADDRESSTYPE               

                                    if @BUSINESS_ADDRESSTYPECODEID is null
                                    begin
                                        exec dbo.USP_ADDRESSTYPECODE_CREATEENTRY
                                            @BUSINESS_ADDRESSTYPE,
                                            1,
                                            null,
                                            @CHANGEAGENTID,
                                            @BUSINESS_ADDRESSTYPECODEID output

                                    end 


                                    if @CURRENT_BUSINESS_ADDRESS_ID is not null
                                    begin
                                        update
                                            dbo.ADDRESS
                                        set
                                            ADDRESS.ISPRIMARY = 0,
                                            ADDRESS.CHANGEDBYID = @CHANGEAGENTID,
                                            ADDRESS.DATECHANGED = getdate()        
                                        where
                                            ADDRESS.ISPRIMARY = 1 and
                                            ADDRESS.CONSTITUENTID = @BUSINESS_ID and
                                            ADDRESS.ID != @CURRENT_BUSINESS_ADDRESS_ID

                                        update 
                                            dbo.ADDRESS
                                        set
                                            ADDRESS.ISPRIMARY = 1,
                                            ADDRESS.CHANGEDBYID = @CHANGEAGENTID,
                                            ADDRESS.DATECHANGED = getdate(),
                      ADDRESS.ADDRESSTYPECODEID = @BUSINESS_ADDRESSTYPECODEID
                                        where
                                            ADDRESS.ID = @CURRENT_BUSINESS_ADDRESS_ID and
                                            ADDRESS.CONSTITUENTID = @BUSINESS_ID and
                                            (ADDRESS.ISPRIMARY = 0 or 
                        (ADDRESS.ADDRESSTYPECODEID is null and @BUSINESS_ADDRESSTYPECODEID is not null) or
                        ADDRESS.ADDRESSTYPECODEID <> @BUSINESS_ADDRESSTYPECODEID)
                                    end
                                    else    
                                    begin 
                                        declare @BUSINESS_COUNTRYID uniqueidentifier;
                                        if @BUSINESS_COUNTRY = ''
                                        begin
                                            set @BUSINESS_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT()
                                        end

                                        if @BUSINESS_COUNTRYID is null
                                        begin
                                            set @BUSINESS_COUNTRYID = dbo.UFN_COUNTRY_GETID(@BUSINESS_COUNTRY,0)
                                        end

                                        if @BUSINESS_COUNTRYID is null
                                        begin
                                            set @BUSINESS_COUNTRYID = dbo.UFN_COUNTRY_GETID(@BUSINESS_COUNTRY_ABBREV,1)
                                        end

                                        if @BUSINESS_COUNTRYID is null
                                        begin
                                            declare @BUSINESS_ADDRESSFORMATID uniqueidentifier

                                            select 
                                                @BUSINESS_ADDRESSFORMATID = COUNTRYADDRESSFORMAT.ID
                                            from
                                                dbo.COUNTRYADDRESSFORMAT
                                            where 
                                                COUNTRYADDRESSFORMAT.FORMATNAME = 'United States'

                                            if @BUSINESS_ADDRESSFORMATID is null

                                            begin
                                                select top(1)
                                                    @BUSINESS_ADDRESSFORMATID = COUNTRYADDRESSFORMAT.ID    
                                                from
                                                    dbo.COUNTRYADDRESSFORMAT    
                                            end        

                                            if @BUSINESS_ADDRESSFORMATID is not null
                                            begin
                                                exec dbo.USP_DATAFORMTEMPLATE_ADD_COUNTRY
                                                    @BUSINESS_COUNTRYID output,
                                                    @BUSINESS_COUNTRY,
                                                    @BUSINESS_COUNTRY_ABBREV,
                                                    @BUSINESS_ADDRESSFORMATID,
                                                    0, --INACTIVE = false

                                                    0, --ALLOWFALIDATION = false

                                                    0, --VALIDATIONCOUNTRYCODE = false

                                                    @CHANGEAGENTID
                                            end
                                        end

                                        --Get State ID if possible

                                        declare @BUSINESS_STATEID uniqueidentifier

                                        if @BUSINESS_COUNTRYID is not null
                                        begin

                                            set @BUSINESS_STATEID = dbo.UFN_STATE_GETID(@BUSINESS_COUNTRYID,@BUSINESS_STATE,0)

                                            if @BUSINESS_STATEID is null
                                            begin
                                                set @BUSINESS_STATEID = dbo.UFN_STATE_GETID(@BUSINESS_COUNTRYID,@BUSINESS_STATE_ABBREV,1)
                                            end

                                            if @BUSINESS_STATEID is null and
                                               @BUSINESS_STATE != '' and
                                               @BUSINESS_STATE_ABBREV != ''                        

                                            begin
                                                exec dbo.USP_DATAFORMTEMPLATE_ADD_STATE
                                                    @BUSINESS_STATEID output,
                                                    @BUSINESS_COUNTRYID,
                                                    @BUSINESS_STATE,
                                                    @BUSINESS_STATE_ABBREV,
                                                    0,
                                                    @CHANGEAGENTID                                
                                            end                                                   
                                        end

                                        -- Add business address

                                        if @BUSINESS_COUNTRYID is not null and
                                           @BUSINESS_ADDRESSTYPECODEID is not null

                                        begin
                                            exec dbo.USP_DATAFORMTEMPLATE_ADD_ADDRESS
                                                null,
                                                @CHANGEAGENTID,
                                                @BUSINESS_ID,
                                                @BUSINESS_ADDRESSTYPECODEID,
                                                1,
                                                0,
                                                '0000',
                                                '0000',
                                                @BUSINESS_COUNTRYID,
                                                @BUSINESS_STATEID,
                                                @BUSINESS_ADDRESSBLOCK,
                                                @BUSINESS_CITY,
                                                @BUSINESS_POSTCODE
                                        end                
                                    end                    
                                end    
                            end    

                            -- Update Giving Information     

                            if @SYNCGIVING = 1             
                            begin
                                if (select
                                        count(ID)
                                    from
                                        dbo.RE7INTEGRATIONGIVINGSUMMARY
                                    where
                                        RE7INTEGRATIONGIVINGSUMMARY.ID = @ID
                                    ) > 0 
                                begin
                                    update
                                        dbo.RE7INTEGRATIONGIVINGSUMMARY
                                    set
                                        FIRSTGIFTAMOUNT =    @FIRSTGIFTAMOUNT,
                                        FIRSTGIFTDATE =        @FIRSTGIFTDATE,
                                        LARGESTGIFTAMOUNT = @LARGESTGIFTAMOUNT,
                                        LARGESTGIFTDATE =   @LARGESTGIFTDATE,                               
                                        LATESTGIFTAMOUNT =  @LATESTGIFTAMOUNT,
                                        LATESTGIFTDATE =    @LATESTGIFTDATE,
                                        TOTALGIFTSGIVEN =    @TOTALGIFTSGIVEN,
                                        TOTALGIFTAMOUNT =    @TOTALGIFTAMOUNT,
                                        FIRSTGIFTTYPE =        @FIRSTGIFTTYPE,
                                        LARGESTGIFTTYPE =    @LARGESTGIFTTYPE,
                                        LATESTGIFTTYPE =    @LATESTGIFTTYPE,
                                        LATESTGIFTDESIGNATION =     @LATESTGIFTDESIGNATION,
                                        FIRSTGIFTDESIGNATION =   @FIRSTGIFTDESIGNATION,
                                        LARGESTGIFTDESIGNATION = @LARGESTGIFTDESIGNATION,
                                        CHANGEDBYID =        @CHANGEAGENTID,
                                        DATECHANGED =        getdate()                           
                                    where
                                        RE7INTEGRATIONGIVINGSUMMARY.ID = @ID        
                                end     
                                else
                                begin
                                    insert into    dbo.RE7INTEGRATIONGIVINGSUMMARY(
                                        ID,
                                        FIRSTGIFTAMOUNT,
                                        FIRSTGIFTDATE,
                                        LARGESTGIFTAMOUNT,
                                        LARGESTGIFTDATE,                               
                                        LATESTGIFTAMOUNT,
                                        LATESTGIFTDATE,
                                        TOTALGIFTSGIVEN,
                                        TOTALGIFTAMOUNT,            
                                        FIRSTGIFTTYPE,
                                        LARGESTGIFTTYPE,
                                        LATESTGIFTTYPE,
                                        LATESTGIFTDESIGNATION,
                                        FIRSTGIFTDESIGNATION,
                                        LARGESTGIFTDESIGNATION,
                                        CHANGEDBYID,
                                        ADDEDBYID
                                    )values(
                                        @ID,
                                        @FIRSTGIFTAMOUNT,
                                        @FIRSTGIFTDATE,
                                        @LARGESTGIFTAMOUNT,
                                        @LARGESTGIFTDATE,                               
                                        @LATESTGIFTAMOUNT,
                                        @LATESTGIFTDATE,
                                        @TOTALGIFTSGIVEN,
                                        @TOTALGIFTAMOUNT,
                                        @FIRSTGIFTTYPE,
                                        @LARGESTGIFTTYPE,
                                        @LATESTGIFTTYPE,
                                        @LATESTGIFTDESIGNATION,
                                        @FIRSTGIFTDESIGNATION,
                                        @LARGESTGIFTDESIGNATION,
                                        @CHANGEAGENTID,
                                        @CHANGEAGENTID                               
                                    )
                                end
                                exec dbo.USP_WEALTHCAPACITY_UPDATE @ID, @CHANGEAGENTID;
                            end

                        end try


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

                        return 0;