USP_DATAFORMTEMPLATE_EDIT_RELATIONSHIP_INDTOORG_9

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@RELATIONSHIPTYPECODEID	uniqueidentifier	IN
@RECIPROCALTYPECODEID	uniqueidentifier	IN
@RELATIONSHIPSTARTDATE	datetime	IN
@RELATIONSHIPENDDATE	datetime	IN
@ISCONTACT	bit	IN
@ISPRIMARYCONTACT	bit	IN
@CONTACTTYPECODEID	uniqueidentifier	IN
@POSITION	nvarchar(100)	IN
@ISPRIMARYBUSINESS	bit	IN
@ISMATCHINGGIFTRELATIONSHIP	bit	IN
@RELATIONADDRESSID	uniqueidentifier	IN
@ADDADDRESS	bit	IN
@EDITADDRESS	bit	IN
@ADDRESSTYPECODEID	uniqueidentifier	IN
@PRIMARY	bit	IN
@DONOTMAIL	bit	IN
@STARTDATE	UDT_MONTHDAY	IN
@ENDDATE	UDT_MONTHDAY	IN
@COUNTRYID	uniqueidentifier	IN
@STATEID	uniqueidentifier	IN
@ADDRESSBLOCK	nvarchar(150)	IN
@CITY	nvarchar(50)	IN
@POSTCODE	nvarchar(12)	IN
@CART	nvarchar(10)	IN
@DPC	nvarchar(8)	IN
@LOT	nvarchar(5)	IN
@UPDATEMATCHINGSPOUSEADDRESSES	bit	IN
@OMITFROMVALIDATION	bit	IN
@COUNTYCODEID	uniqueidentifier	IN
@CONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN
@STATEHOUSEDISTRICTCODEID	uniqueidentifier	IN
@STATESENATEDISTRICTCODEID	uniqueidentifier	IN
@LOCALPRECINCTCODEID	uniqueidentifier	IN
@INFOSOURCECODEID	uniqueidentifier	IN
@REGIONCODEID	uniqueidentifier	IN
@LASTVALIDATIONATTEMPTDATE	datetime	IN
@VALIDATIONMESSAGE	nvarchar(200)	IN
@CERTIFICATIONDATA	int	IN
@UPDATECONTACTS	bit	IN
@DONOTMAILREASONCODEID	uniqueidentifier	IN
@INFOSOURCECOMMENTS	nvarchar(256)	IN
@COMMENTS	nvarchar(max)	IN
@RELATIONPHONEID	uniqueidentifier	IN
@ADDPHONE	bit	IN
@EDITPHONE	bit	IN
@PHONETYPECODEID	uniqueidentifier	IN
@NUMBER	nvarchar(100)	IN
@PHONECOUNTRYID	uniqueidentifier	IN
@PHONESTARTTIME	UDT_HOURMINUTE	IN
@PHONEENDTIME	UDT_HOURMINUTE	IN
@PHONESTARTDATE	date	IN
@PHONEENDDATE	date	IN
@PHONEPRIMARY	bit	IN
@DONOTCALL	bit	IN
@DONOTCALLREASONCODEID	uniqueidentifier	IN
@PHONEISCONFIDENTIAL	bit	IN
@PHONEINFOSOURCECODEID	uniqueidentifier	IN
@PHONEINFOSOURCECOMMENTS	nvarchar(256)	IN
@RELATIONEMAILADDRESSID	uniqueidentifier	IN
@ADDEMAILADDRESS	bit	IN
@EDITEMAILADDRESS	bit	IN
@EMAILADDRESSTYPECODEID	uniqueidentifier	IN
@EMAILADDRESS	UDT_EMAILADDRESS	IN
@EMAILADDRESSPRIMARY	bit	IN
@DONOTEMAIL	bit	IN
@EMAILADDRESSINFOSOURCECODEID	uniqueidentifier	IN
@EMAILADDRESSINFOSOURCECOMMENTS	nvarchar(256)	IN
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS	bit	IN
@PRIMARYSOFTCREDITMATCHFACTOR	decimal(5, 2)	IN
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS	bit	IN
@RECIPROCALSOFTCREDITMATCHFACTOR	decimal(5, 2)	IN
@PRIMARYRECOGNITIONTYPECODEID	uniqueidentifier	IN
@RECIPROCALRECOGNITIONTYPECODEID	uniqueidentifier	IN
@HISTORICALSTARTDATE	date	IN
@HISTORICALENDDATE	date	IN
@EMAILSTARTDATE	date	IN
@EMAILENDDATE	date	IN
@SYNCENDDATETOHISTORY	bit	IN
Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RELATIONSHIP_INDTOORG_9
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @RELATIONSHIPTYPECODEID uniqueidentifier,
  @RECIPROCALTYPECODEID uniqueidentifier,
  @RELATIONSHIPSTARTDATE datetime,
  @RELATIONSHIPENDDATE datetime,
  @ISCONTACT bit,
  @ISPRIMARYCONTACT bit,
  @CONTACTTYPECODEID uniqueidentifier,
  @POSITION nvarchar(100),
  @ISPRIMARYBUSINESS bit,
  @ISMATCHINGGIFTRELATIONSHIP bit,
  @RELATIONADDRESSID uniqueidentifier,
  @ADDADDRESS bit,
  @EDITADDRESS bit,
  @ADDRESSTYPECODEID uniqueidentifier,
  @PRIMARY bit,
  @DONOTMAIL bit,
  @STARTDATE dbo.UDT_MONTHDAY,
  @ENDDATE dbo.UDT_MONTHDAY,
  @COUNTRYID uniqueidentifier,
  @STATEID uniqueidentifier,
  @ADDRESSBLOCK nvarchar(150),
  @CITY nvarchar(50),
  @POSTCODE nvarchar(12),
  @CART nvarchar(10),
  @DPC nvarchar(8),
  @LOT nvarchar(5),
  @UPDATEMATCHINGSPOUSEADDRESSES bit,
  @OMITFROMVALIDATION bit,
  @COUNTYCODEID uniqueidentifier,
  @CONGRESSIONALDISTRICTCODEID uniqueidentifier,
  @STATEHOUSEDISTRICTCODEID uniqueidentifier,
  @STATESENATEDISTRICTCODEID uniqueidentifier,
  @LOCALPRECINCTCODEID uniqueidentifier,
  @INFOSOURCECODEID uniqueidentifier,
  @REGIONCODEID uniqueidentifier,
  @LASTVALIDATIONATTEMPTDATE datetime,
  @VALIDATIONMESSAGE nvarchar(200),
  @CERTIFICATIONDATA integer,
  @UPDATECONTACTS bit,
  @DONOTMAILREASONCODEID uniqueidentifier,
  @INFOSOURCECOMMENTS nvarchar(256),
  @COMMENTS nvarchar(max),
  @RELATIONPHONEID uniqueidentifier,
  @ADDPHONE bit,
  @EDITPHONE bit,
  @PHONETYPECODEID uniqueidentifier,
  @NUMBER nvarchar(100),
  @PHONECOUNTRYID uniqueidentifier,
  @PHONESTARTTIME dbo.UDT_HOURMINUTE,
  @PHONEENDTIME dbo.UDT_HOURMINUTE,
  @PHONESTARTDATE date,
  @PHONEENDDATE date,
  @PHONEPRIMARY bit,
  @DONOTCALL bit,
  @DONOTCALLREASONCODEID uniqueidentifier,
  @PHONEISCONFIDENTIAL bit,
  @PHONEINFOSOURCECODEID uniqueidentifier,
  @PHONEINFOSOURCECOMMENTS nvarchar(256),
  @RELATIONEMAILADDRESSID uniqueidentifier,
  @ADDEMAILADDRESS bit,
  @EDITEMAILADDRESS bit,
  @EMAILADDRESSTYPECODEID uniqueidentifier,
  @EMAILADDRESS dbo.UDT_EMAILADDRESS,
  @EMAILADDRESSPRIMARY bit,
  @DONOTEMAIL bit,
  @EMAILADDRESSINFOSOURCECODEID uniqueidentifier,
  @EMAILADDRESSINFOSOURCECOMMENTS nvarchar(256),  
  @PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit,
  @PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2),
  @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit,
  @RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2),
  @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier,
  @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier,
  @HISTORICALSTARTDATE date,
  @HISTORICALENDDATE date,
  @EMAILSTARTDATE date,
  @EMAILENDDATE date,
  @SYNCENDDATETOHISTORY bit
)
as
  set nocount on;

  declare @contextCache varbinary(128);
  declare @CURRENTDATE datetime;
  set @CURRENTDATE = getdate();

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

  declare @RELATIONSHIPSETID uniqueidentifier;

  select @RELATIONSHIPSETID = RELATIONSHIPSETID
  from dbo.RELATIONSHIP where ID = @ID;

  if @RELATIONSHIPSETID is null
  begin
     exec dbo.USP_RELATIONSHIPSET_RETROCREATE @RELATIONSHIPSETID output, @CHANGEAGENTID, @ID;
  end

  begin try
    if exists(select 1 from 
          dbo.RELATIONSHIPORGANIZATION
      inner join 
          dbo.RELATIONSHIP on RELATIONSHIPORGANIZATION.RELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID
      where RELATIONSHIP.ID =  @ID
      )
      begin
        update RELATIONSHIPORGANIZATION
        set
          SYNCENDDATETOHISTORY = @SYNCENDDATETOHISTORY
        from
            dbo.RELATIONSHIPORGANIZATION
        inner join 
            dbo.RELATIONSHIP on RELATIONSHIPORGANIZATION.RELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID
        where RELATIONSHIP.ID =  @ID;
      end
    else
      begin
        insert into dbo.RELATIONSHIPORGANIZATION
        (
          RELATIONSHIPSETID,
          SYNCENDDATETOHISTORY,
          ADDEDBYID,
          CHANGEDBYID,
          DATEADDED,
          DATECHANGED
        )
        select 
          RELATIONSHIP.RELATIONSHIPSETID,
          @SYNCENDDATETOHISTORY,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        from dbo.RELATIONSHIP
        where RELATIONSHIP.ID =  @ID;
      end

    if @ISMATCHINGGIFTRELATIONSHIP = 1
      update
        dbo.RELATIONSHIP
      set
        ISMATCHINGGIFTRELATIONSHIP = 0,
        DATECHANGED = @CURRENTDATE,
        CHANGEDBYID = @CHANGEAGENTID
      where
        ID in (
          select
            TOUPDATE.ID
          from
            dbo.RELATIONSHIP TOUPDATE
          left join
            dbo.RELATIONSHIP on RELATIONSHIP.ID = @ID
          where
            TOUPDATE.RELATIONSHIPCONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID and
            TOUPDATE.RECIPROCALCONSTITUENTID = RELATIONSHIP.RECIPROCALCONSTITUENTID and
            TOUPDATE.ISMATCHINGGIFTRELATIONSHIP = 1 and
            TOUPDATE.ID <> RELATIONSHIP.ID)

    update
      dbo.RELATIONSHIP
    set    
      RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID,
      RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID,
      STARTDATE = @RELATIONSHIPSTARTDATE,
      ENDDATE = @RELATIONSHIPENDDATE,
      ISCONTACT = @ISCONTACT,
      ISPRIMARYCONTACT = @ISPRIMARYCONTACT,
      POSITION = @POSITION,
      CONTACTTYPECODEID = @CONTACTTYPECODEID,
      ISPRIMARYBUSINESS = @ISPRIMARYBUSINESS,
      ISMATCHINGGIFTRELATIONSHIP = @ISMATCHINGGIFTRELATIONSHIP,
      COMMENTS = @COMMENTS,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    where
      ID = @ID;

    declare @RECIPROCALRELATIONSHIPID uniqueidentifier;
    declare @CONSTITUENTID uniqueidentifier;
    declare @RECIPROCALCONSTITUENTID uniqueidentifier;

    select
      @RECIPROCALRELATIONSHIPID = r2.ID,
      @CONSTITUENTID = r1.RELATIONSHIPCONSTITUENTID,
      @RECIPROCALCONSTITUENTID = r1.RECIPROCALCONSTITUENTID
    from
      dbo.RELATIONSHIP r1
    inner join
      dbo.RELATIONSHIP r2 on (r1.RELATIONSHIPCONSTITUENTID = r2.RECIPROCALCONSTITUENTID and
        r1.RECIPROCALCONSTITUENTID = r2.RELATIONSHIPCONSTITUENTID and
        r1.RELATIONSHIPTYPECODEID = r2.RECIPROCALTYPECODEID and
        r1.RECIPROCALTYPECODEID = r2.RELATIONSHIPTYPECODEID)
    where
      r1.ID = @ID;

    if @EDITADDRESS = 1
    begin
      exec dbo.USP_ADDRESS_UPDATE @RELATIONADDRESSID, @CHANGEAGENTID, @ADDRESSTYPECODEID, @PRIMARY, @DONOTMAIL, @STARTDATE, @ENDDATE,
        @COUNTRYID, @STATEID, @ADDRESSBLOCK, @CITY, @POSTCODE, @CART, @DPC, @LOT, @UPDATEMATCHINGSPOUSEADDRESSES,
        @OMITFROMVALIDATION, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID, @STATEHOUSEDISTRICTCODEID,
        @STATESENATEDISTRICTCODEID, @LOCALPRECINCTCODEID, @INFOSOURCECODEID, @REGIONCODEID, @LASTVALIDATIONATTEMPTDATE,
        @VALIDATIONMESSAGE, @CERTIFICATIONDATA, @UPDATECONTACTS, @DONOTMAILREASONCODEID, @HISTORICALSTARTDATE, @HISTORICALENDDATE,
        @INFOSOURCECOMMENTS;
    end
    else if @ADDADDRESS = 1
    begin                                    
        select top 1 @RELATIONADDRESSID = ID
        from dbo.ADDRESS 
            where ADDRESS.CONSTITUENTID = @CONSTITUENTID 
            and (ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or (ADDRESS.ADDRESSTYPECODEID is null and @ADDRESSTYPECODEID is null)) 
            and ADDRESS.ADDRESSBLOCK = @ADDRESSBLOCK 
            and ADDRESS.CITY = @CITY 
            and (ADDRESS.STATEID = @STATEID or (ADDRESS.STATEID is null and  @STATEID is null))
            and (ADDRESS.COUNTRYID = @COUNTRYID or (ADDRESS.COUNTRYID is null and  @COUNTRYID is null))
            and ADDRESS.POSTCODE = @POSTCODE 
            and ADDRESS.RELATIONSHIPID is null

        if @RELATIONADDRESSID is null
          exec dbo.USP_ADDRESS_CREATE @RELATIONADDRESSID output, @CHANGEAGENTID, @CONSTITUENTID, @ADDRESSTYPECODEID, @PRIMARY, @DONOTMAIL,
            @STARTDATE, @ENDDATE, @COUNTRYID, @STATEID, @ADDRESSBLOCK, @CITY, @POSTCODE, @CART, @DPC, @LOT,
            @UPDATEMATCHINGSPOUSEADDRESSES, @OMITFROMVALIDATION, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID,
            @STATEHOUSEDISTRICTCODEID, @STATESENATEDISTRICTCODEID, @LOCALPRECINCTCODEID, @INFOSOURCECODEID,
            @REGIONCODEID, @LASTVALIDATIONATTEMPTDATE, @VALIDATIONMESSAGE, @CERTIFICATIONDATA, @DONOTMAILREASONCODEID,
            @HISTORICALSTARTDATE, @INFOSOURCECOMMENTS;

        if @RELATIONADDRESSID is not null
        begin                                                            
          update
            dbo.ADDRESS
          set
            RELATIONSHIPID = @RECIPROCALRELATIONSHIPID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where
            ID = @RELATIONADDRESSID;
        end
    end

    declare @ADDRESSESTODELETE table (ID uniqueidentifier);

    --Is there currently an address associated with this relationship?

    if @RELATIONADDRESSID is not null
    begin
      --If so, set the address's RELATIONSHIPID.                    

      update dbo.ADDRESS set
        RELATIONSHIPID = @RECIPROCALRELATIONSHIPID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        ID = @RELATIONADDRESSID;

      --Cache CONTEXT INFO

      set @contextCache = CONTEXT_INFO();

      if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID;

      --Delete the old address, if there was one and if it's not currently primary.

      insert into
        @ADDRESSESTODELETE
      select
        ID
      from
        dbo.ADDRESS
      where
        ID <> @RELATIONADDRESSID and
        RELATIONSHIPID = @RECIPROCALRELATIONSHIPID and
        ISPRIMARY = 0 and 
        not exists(select 1 from dbo.[SALESORDER] where [SALESORDER].[ADDRESSID] = [ADDRESS].[ID]);

      delete MAILPREFERENCE from
        dbo.MAILPREFERENCE
      inner join
        @ADDRESSESTODELETE ADDRESSESTODELETE on ADDRESSESTODELETE.ID = MAILPREFERENCE.ADDRESSID;

      --remove ADDRESSID from committed address update batches

      update BATCHCONSTITUENTADDRESSUPDATE
      set ADDRESSID = null
      from dbo.BATCHCONSTITUENTADDRESSUPDATE bcau
        inner join dbo.BATCH b on bcau.BATCHID = b.ID
        inner join @ADDRESSESTODELETE a on bcau.ADDRESSID = a.ID
      where b.STATUSCODE = 1

      delete ADDRESS from
        dbo.ADDRESS
      inner join
        @ADDRESSESTODELETE ADDRESSESTODELETE on ADDRESSESTODELETE.ID = ADDRESS.ID;

      --Restore CONTEXT_INFO

      if not @contextCache is null
        set CONTEXT_INFO @contextCache;

    end
    else
    begin
      --If there isn't currently an address, delete all address that refer to this relationship that aren't primary.

      --Cache CONTEXT INFO

      set @contextCache = CONTEXT_INFO();

      if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID;

      insert into
        @ADDRESSESTODELETE
      select
        ID
      from
        dbo.ADDRESS
      where
        RELATIONSHIPID = @RECIPROCALRELATIONSHIPID and
        ISPRIMARY = 0 and 
        not exists(select 1 from dbo.[SALESORDER] where [SALESORDER].[ADDRESSID] = [ADDRESS].[ID]);

      delete MAILPREFERENCE from
        dbo.MAILPREFERENCE
      inner join
        @ADDRESSESTODELETE ADDRESSESTODELETE on ADDRESSESTODELETE.ID = MAILPREFERENCE.ADDRESSID;

      --remove ADDRESSID from committed address update batches

      update BATCHCONSTITUENTADDRESSUPDATE
      set ADDRESSID = null
      from dbo.BATCHCONSTITUENTADDRESSUPDATE bcau
        inner join dbo.BATCH b on bcau.BATCHID = b.ID
        inner join @ADDRESSESTODELETE a on bcau.ADDRESSID = a.ID
      where b.STATUSCODE = 1

      delete ADDRESS from
        dbo.ADDRESS
      inner join
        @ADDRESSESTODELETE ADDRESSESTODELETE on ADDRESSESTODELETE.ID = ADDRESS.ID;

      --Restore CONTEXT_INFO

      if not @contextCache is null
        set CONTEXT_INFO @contextCache;

      --Disassociate any remaining addresses.

      update dbo.ADDRESS set
        RELATIONSHIPID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        RELATIONSHIPID = @RECIPROCALRELATIONSHIPID;
    end

    if @EDITPHONE = 1
    begin
      exec dbo.USP_PHONE_UPDATE @RELATIONPHONEID, @CHANGEAGENTID, @CURRENTDATE, @PHONETYPECODEID, @NUMBER,
        @PHONEPRIMARY, @DONOTCALL, 0, @PHONESTARTTIME, @PHONEENDTIME, @PHONEINFOSOURCECODEID, @PHONEINFOSOURCECOMMENTS,
        @PHONECOUNTRYID, @PHONESTARTDATE, @PHONEENDDATE, @DONOTCALLREASONCODEID, @PHONEISCONFIDENTIAL;
    end
    else if @ADDPHONE = 1
    begin
        select top 1 @RELATIONPHONEID = ID
        from dbo.PHONE 
        where PHONE.CONSTITUENTID = @CONSTITUENTID 
          and (PHONE.PHONETYPECODEID = @PHONETYPECODEID or (PHONE.PHONETYPECODEID is null and @PHONETYPECODEID is null))
          and PHONE.NUMBER = @NUMBER 
          and PHONE.RELATIONSHIPID is null;

        if @RELATIONPHONEID is null
          exec dbo.USP_PHONE_CREATE @RELATIONPHONEID output, @CHANGEAGENTID, @CURRENTDATE,
            @CONSTITUENTID, @PHONETYPECODEID, @NUMBER, @PHONEPRIMARY,
            @PHONESTARTTIME, @PHONEENDTIME, @PHONESTARTDATE, @DONOTCALL, @DONOTCALLREASONCODEID,
            @PHONEINFOSOURCECODEID, @PHONEINFOSOURCECOMMENTS, @PHONECOUNTRYID, @PHONEISCONFIDENTIAL,
            0, @RECIPROCALRELATIONSHIPID;

        else
            update dbo.PHONE
            set 
                RELATIONSHIPID = @RECIPROCALRELATIONSHIPID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where 
                PHONE.ID = @RELATIONPHONEID;

    end

    --Is there currently a phone associated with this relationship?

    if @RELATIONPHONEID is not null
    begin
      --Cache CONTEXT INFO

      set @contextCache = CONTEXT_INFO();

      if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID;

      --Delete the old phone, if there was one and if it's not currently primary or on a sales order.

      delete from
        dbo.PHONE
      where
        ID <> @RELATIONPHONEID and
        RELATIONSHIPID = @RECIPROCALRELATIONSHIPID and
        ISPRIMARY = 0 and 
        not exists(select 1 from dbo.[SALESORDER] where [SALESORDER].[PHONEID] = [PHONE].[ID]);

      --Restore CONTEXT_INFO

      if not @contextCache is null
        set CONTEXT_INFO @contextCache;
    end
    else
    begin
      --Cache CONTEXT INFO

      set @contextCache = CONTEXT_INFO();

      if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID;

      --If there isn't currently a phone, delete all phones that refer to this relationship that aren't primary or on a sales order.

      delete from
        dbo.PHONE
      where
        RELATIONSHIPID = @RECIPROCALRELATIONSHIPID and
        ISPRIMARY = 0 and 
        not exists(select 1 from dbo.[SALESORDER] where [SALESORDER].[PHONEID] = [PHONE].[ID]);

      --Restore CONTEXT_INFO

      if not @contextCache is null
        set CONTEXT_INFO @contextCache;

      --Disassociate any remaining phones.

      update dbo.PHONE set
        RELATIONSHIPID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        RELATIONSHIPID = @RECIPROCALRELATIONSHIPID;
    end

    --Are we editting the existing email address?

    if @EDITEMAILADDRESS = 1
    begin
      if isnull(@EMAILADDRESS, '') = ''
        raiserror('ERR_EMAILADDRESSREQUIRED', 13, 1);

      exec dbo.USP_EMAILADDRESS_UPDATE @RELATIONEMAILADDRESSID, @CHANGEAGENTID, @CURRENTDATE,
        @EMAILADDRESSTYPECODEID, @EMAILADDRESS, @EMAILADDRESSPRIMARY, @DONOTEMAIL,
        0, @EMAILADDRESSINFOSOURCECODEID, @EMAILADDRESSINFOSOURCECOMMENTS, @EMAILSTARTDATE, @EMAILENDDATE;
    end
    else if @ADDEMAILADDRESS = 1
    begin
      if isnull(@EMAILADDRESS, '') = ''
        raiserror('ERR_EMAILADDRESSREQUIRED', 13, 1);


        select top 1 @RELATIONEMAILADDRESSID = ID
        from dbo.EMAILADDRESS 
        where EMAILADDRESS.CONSTITUENTID = @CONSTITUENTID 
          and EMAILADDRESS = @EMAILADDRESS 
          and ( (EMAILADDRESSTYPECODEID = @EMAILADDRESSTYPECODEID) or (EMAILADDRESSTYPECODEID is null and @EMAILADDRESSTYPECODEID is null) )

        if @RELATIONEMAILADDRESSID is null
          exec dbo.USP_EMAILADDRESS_CREATE @RELATIONEMAILADDRESSID output, @CHANGEAGENTID, @CURRENTDATE,
            @CONSTITUENTID, @EMAILADDRESSTYPECODEID, @EMAILADDRESS, @EMAILADDRESSPRIMARY,
            @DONOTEMAIL, @EMAILADDRESSINFOSOURCECODEID, @EMAILADDRESSINFOSOURCECOMMENTS, 0, @RECIPROCALRELATIONSHIPID,
            0, @EMAILSTARTDATE;

        else
            update dbo.EMAILADDRESS
            set 
                RELATIONSHIPID = @RECIPROCALRELATIONSHIPID,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            where 
                EMAILADDRESS.ID = @RELATIONEMAILADDRESSID;

    end

    --Is there currently an email address associated with this relationship?

    if @RELATIONEMAILADDRESSID is not null
    begin
      --Cache CONTEXT INFO

      set @contextCache = CONTEXT_INFO();

      if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID;

      --Delete the old email address, if there was one and if it's not currently primary or on a sales order.

      delete from
        dbo.EMAILADDRESS
      where 
        ID <> @RELATIONEMAILADDRESSID and
        RELATIONSHIPID = @RECIPROCALRELATIONSHIPID and
        ISPRIMARY = 0 and 
        not exists(select 1 from dbo.[SALESORDER] where [SALESORDER].[EMAILADDRESSID] = [EMAILADDRESS].[ID]);

      --Restore CONTEXT_INFO

      if not @contextCache is null
        set CONTEXT_INFO @contextCache;
    end
    else
    begin
      --Cache CONTEXT INFO

      set @contextCache = CONTEXT_INFO();

      if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID;

      --If there isn't currently an email address, delete all email addresses that refer to this relationship that aren't primary or on a sales order.

      delete from
        dbo.EMAILADDRESS
      where
        RELATIONSHIPID = @RECIPROCALRELATIONSHIPID and
        ISPRIMARY = 0 and
        not exists(select 1 from dbo.[SALESORDER] where [SALESORDER].[EMAILADDRESSID] = [EMAILADDRESS].[ID]);

      --Restore CONTEXT_INFO

      if not @contextCache is null
        set CONTEXT_INFO @contextCache;

      --Disassociate any remaining email addresses.

      update dbo.EMAILADDRESS set
        RELATIONSHIPID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        RELATIONSHIPID = @RECIPROCALRELATIONSHIPID;
    end

    exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @CONSTITUENTID, @RECIPROCALCONSTITUENTID, @RELATIONSHIPSTARTDATE, 
      @RELATIONSHIPENDDATE, @PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @PRIMARYSOFTCREDITMATCHFACTOR, @PRIMARYRECOGNITIONTYPECODEID,
      @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS, @RECIPROCALSOFTCREDITMATCHFACTOR, @RECIPROCALRECOGNITIONTYPECODEID,
      @CHANGEAGENTID;

    exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @CONSTITUENTID, @RECIPROCALCONSTITUENTID, @RELATIONSHIPTYPECODEID, @RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE;

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

  return 0;