USP_INDIVIDUALSPOUSEBUSINESS_ADD

CREATE procedure dbo.USP_INDIVIDUALSPOUSEBUSINESS_ADD
(
  @ID uniqueidentifier = null output,
  @CURRENTAPPUSERID uniqueidentifier,
  @DATAFORMTEMPLATEID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null,
  @LASTNAME nvarchar(100),
  @FIRSTNAME nvarchar(50) = '',
  @MIDDLENAME nvarchar(50) = '',
  @MAIDENNAME nvarchar(100) = '',
  @NICKNAME nvarchar(50) = '',
  --10

  @TITLECODEID uniqueidentifier = null,
  @SUFFIXCODEID uniqueidentifier = null,
  @GENDERCODE tinyint = 0,
  @BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
  @ADDRESS_ADDRESSTYPECODEID uniqueidentifier = null,
  @ADDRESS_DONOTMAIL bit = 0,
  @ADDRESS_COUNTRYID uniqueidentifier = null,
  @ADDRESS_STATEID uniqueidentifier = null,
  @ADDRESS_ADDRESSBLOCK nvarchar(150) = '',
  @ADDRESS_CITY nvarchar(50) = '',
  --20

  @ADDRESS_POSTCODE nvarchar(12) = '',
  @PHONE_PHONETYPECODEID uniqueidentifier = null,
  @PHONE_NUMBER nvarchar(100) = '',
  @EMAILADDRESS_EMAILADDRESSTYPECODEID    uniqueidentifier = null,
  @EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
  @MARITALSTATUSCODEID uniqueidentifier = null,
  @SKIP_ADDING_SECURITYGROUPS bit = 0,
  @SPOUSEID uniqueidentifier = null,
  @EXISTINGSPOUSE bit = 0,
  @SPOUSE_LASTNAME nvarchar(100) = '',
  --30

  @SPOUSE_FIRSTNAME nvarchar(50) = '',
  @SPOUSE_MIDDLENAME nvarchar(50) = '',
  @SPOUSE_MAIDENNAME nvarchar(100) = '',
  @SPOUSE_NICKNAME nvarchar(50) = '',
  @SPOUSE_TITLECODEID uniqueidentifier = null,
  @SPOUSE_SUFFIXCODEID uniqueidentifier = null,
  @SPOUSE_GENDERCODE tinyint = 0,
  @SPOUSE_BIRTHDATE dbo.UDT_FUZZYDATE = '00000000',
  @SPOUSE_RECIPROCALTYPECODEID uniqueidentifier = null,
  @SPOUSE_RELATIONSHIPTYPECODEID uniqueidentifier = null,
  --40

  @COPYPRIMARYINFORMATION bit = 1,
  @SPOUSE_STARTDATE datetime = null,
  @PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0,
  @PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
  @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0,
  @RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
  @ORGANIZATIONID uniqueidentifier = null,
  @EXISTINGORGANIZATION bit = 0,
  @ORGANIZATION_NAME nvarchar(100) = '',
  @ORGANIZATION_ADDRESSTYPECODEID uniqueidentifier = null,
  --50

  @ORGANIZATION_COUNTRYID uniqueidentifier = null,
  @ORGANIZATION_STATEID uniqueidentifier = null,
  @ORGANIZATION_ADDRESSBLOCK nvarchar(150) = '',
  @ORGANIZATION_CITY nvarchar(50) = '',
  @ORGANIZATION_POSTCODE nvarchar(12) = '',
  @ORGANIZATION_DONOTMAIL bit = 0,
  @ORGANIZATION_PHONETYPECODEID uniqueidentifier = null,
  @ORGANIZATION_NUMBER nvarchar(100) = '',
  @ORGANIZATION_RECIPROCALTYPECODEID uniqueidentifier = null,
  @ORGANIZATION_RELATIONSHIPTYPECODEID uniqueidentifier = null,
  --60

  @ORGANIZATION_STARTDATE datetime = null,
  @ORGANIZATION_ENDDATE datetime = null,
  @ISCONTACT bit = 0,
  @ISPRIMARYCONTACT bit = 0,
  @CONTACTTYPECODEID uniqueidentifier = null,
  @POSITION nvarchar(100) = '',
  @ISMATCHINGGIFTRELATIONSHIP bit = 0,
  @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null,
  @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null,
  @ADDRESS_OMITFROMVALIDATION bit = 0,
  --70

  @ADDRESS_CART nvarchar(10) = '',
  @ADDRESS_DPC nvarchar(8) = '',
  @ADDRESS_LOT nvarchar(5) = '',
  @ADDRESS_COUNTYCODEID uniqueidentifier = null,
  @ADDRESS_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
  @ADDRESS_LASTVALIDATIONATTEMPTDATE datetime = null,
  @ADDRESS_VALIDATIONMESSAGE nvarchar(200) = '',
  @ADDRESS_CERTIFICATIONDATA integer = 0,
  @ORGANIZATION_OMITFROMVALIDATION bit = 0,
  @ORGANIZATION_CART nvarchar(10) = '',
  --80

  @ORGANIZATION_DPC nvarchar(8) = '',
  @ORGANIZATION_LOT nvarchar(5) = '',
  @ORGANIZATION_COUNTYCODEID uniqueidentifier = null,
  @ORGANIZATION_CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
  @ORGANIZATION_LASTVALIDATIONATTEMPTDATE datetime = null,
  @ORGANIZATION_VALIDATIONMESSAGE nvarchar(200) = '',
  @ORGANIZATION_CERTIFICATIONDATA integer = 0,
  @ISSPOUSERELATIONSHIP bit = 1,
  @HOUSEHOLDCOPYPRIMARYCONTACTINFO bit = 0,
  @ADDRESS_DONOTMAILREASONCODEID uniqueidentifier = null,
  --90

  @ORGANIZATION_DONOTMAILREASONCODEID uniqueidentifier = null,
  @SKIP_ADDING_SITES bit = 0,
  @JOBCATEGORYCODEID uniqueidentifier = null,
  @CAREERLEVELCODEID uniqueidentifier = null,
  @ADDRESS_INFOSOURCECODEID uniqueidentifier = null,
  @ORGANIZATION_INFOSOURCECODEID uniqueidentifier = null,
  @TITLE2CODEID uniqueidentifier = null,
  @SUFFIX2CODEID uniqueidentifier = null,
  @SPOUSE_TITLE2CODEID uniqueidentifier = null,
  @SPOUSE_SUFFIX2CODEID uniqueidentifier = null,
  --100

  @ISCONSTITUENT bit = 1,
  @ORGANIZATION_PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0,
  @ORGANIZATION_PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
  @ORGANIZATION_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0,
  @ORGANIZATION_RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
  @ORGANIZATION_PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null,
  @ORGANIZATION_RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null,
  @SPOUSE_EMAILADDRESS_EMAILADDRESS dbo.UDT_EMAILADDRESS = '',
  @SPOUSE_PHONE_NUMBER nvarchar(100) = '',
  @GENDERCODEID uniqueidentifier = null,
  @SPOUSE_GENDERCODEID uniqueidentifier = null
)
as
begin -- sproc body 

  set nocount on;

  declare @CURRENTDATE datetime = getdate();
  declare @SETID uniqueidentifier;

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

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

  if @ADDRESS_DONOTMAIL = 0 
    set @ADDRESS_DONOTMAILREASONCODEID = null;

  if @ORGANIZATION_DONOTMAIL = 0 
    set @ORGANIZATION_DONOTMAILREASONCODEID = null;

  if @EMAILADDRESS_EMAILADDRESSTYPECODEID = '00000000-0000-0000-0000-000000000000'
    set @EMAILADDRESS_EMAILADDRESSTYPECODEID = null

  -------------- Insert Individual's Information --------------

  insert into dbo.CONSTITUENT
  (
    [ID],
    [ISORGANIZATION],
    [KEYNAME],
    [FIRSTNAME],
    [MIDDLENAME],
    [MAIDENNAME],
    [NICKNAME],
    [TITLECODEID],
    [TITLE2CODEID],
    [SUFFIXCODEID],
    [SUFFIX2CODEID],
    [GENDERCODE],
    [BIRTHDATE],
    [PICTURE],
    [PICTURETHUMBNAIL],
    [WEBADDRESS],
    [MARITALSTATUSCODEID],
    [ISCONSTITUENT],
    [ADDEDBYID],
    [CHANGEDBYID],
    [DATEADDED],
    [DATECHANGED],
    [GENDERCODEID]
  )
  values
  (
    @ID,
    0,
    @LASTNAME,
    @FIRSTNAME,
    @MIDDLENAME,
    @MAIDENNAME,
    @NICKNAME,
    @TITLECODEID,
    @TITLE2CODEID,
    @SUFFIXCODEID,
    @SUFFIX2CODEID,
    @GENDERCODE,
    @BIRTHDATE,
    null,
    null,
    '',
    @MARITALSTATUSCODEID,
    @ISCONSTITUENT,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CURRENTDATE,
    @CURRENTDATE,
    @GENDERCODEID
  );

  if @MAIDENNAME is not null and @MAIDENNAME != ''
  begin
    insert into dbo.ALIAS
      (CONSTITUENTID, KEYNAME, FIRSTNAME, MIDDLENAME, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
    values
      (@ID, @MAIDENNAME, @FIRSTNAME, @MIDDLENAME, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
  end

  declare @ADDRESSID uniqueidentifier;

  /* Start Individual Address */
  if((@ADDRESS_STATEID is not null) or (coalesce(@ADDRESS_ADDRESSBLOCK,'') <> '') or (coalesce(@ADDRESS_CITY,'') <> '') 
      or (coalesce(@ADDRESS_POSTCODE,'') <> '') or (@ADDRESS_ADDRESSTYPECODEID is not null) 
      or (@ADDRESS_COUNTRYID is not null) or (@ADDRESS_DONOTMAIL = 1) or (@ADDRESS_DONOTMAILREASONCODEID is not null))
  begin
    set @ADDRESSID = newid();

    exec dbo.USP_ADDRESS_ADD
        @ID = @ADDRESSID output,
        @CHANGEAGENTID = @CHANGEAGENTID,
        @CONSTITUENTID = @ID,
        @ADDRESSTYPECODEID = @ADDRESS_ADDRESSTYPECODEID,
        @PRIMARY = 1,
        @DONOTMAIL = @ADDRESS_DONOTMAIL,
        --@STARTDATE = '0000',

        --@ENDDATE = '0000',

        @COUNTRYID = @ADDRESS_COUNTRYID,
        @STATEID = @ADDRESS_STATEID,
        @ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK,
        @CITY = @ADDRESS_CITY,
        @POSTCODE = @ADDRESS_POSTCODE,
        --@HISTORICALSTARTDATE = null,

        @RECENTMOVE = 0,
        --@OLDADDRESSID = null,

        @CART = @ADDRESS_CART,
        @DPC = @ADDRESS_DPC,
        @LOT = @ADDRESS_LOT,
        @UPDATEMATCHINGSPOUSEADDRESSES = 0,
        @UPDATEMATCHINGHOUSEHOLDADDRESSES = 0,
        @OMITFROMVALIDATION = @ADDRESS_OMITFROMVALIDATION,
        @COUNTYCODEID = @ADDRESS_COUNTYCODEID,
        --@CONGRESSIONALDISTRICTCODEID = @ADDRESS_CONGRESSIONALDISTRICTCODEID,

        --@STATEHOUSEDISTRICTCODEID = @ADDRESS_STATEHOUSEDISTRICTCODEID,

        --@STATESENATEDISTRICTCODEID = @ADDRESS_STATESENATEDISTRICTCODEID,

        --@LOCALPRECINCTCODEID = @ADDRESS_LOCALPRECINCTCODEID,

        @INFOSOURCECODEID = @ADDRESS_INFOSOURCECODEID,
        --@REGIONCODEID = @ADDRESS_REGIONCODEID,

        @LASTVALIDATIONATTEMPTDATE = @ADDRESS_LASTVALIDATIONATTEMPTDATE,
        @VALIDATIONMESSAGE = @ADDRESS_VALIDATIONMESSAGE,
        @CERTIFICATIONDATA = @ADDRESS_CERTIFICATIONDATA,
        @DONOTMAILREASONCODEID = @ADDRESS_DONOTMAILREASONCODEID;
        --@INFOSOURCECOMMENTS = null,

        --@ISCONFIDENTIAL = null,

        --@CONSTITUENTDATAREVIEWROLLBACKREASONID = @CONSTITUENTDATAREVIEWROLLBACKREASONID,

        --@ORIGINCODE = @ORIGINCODE


  end
  /* End Individual Address */

  declare @PHONEID uniqueidentifier;
  declare @COUNTRYCODE nvarchar(10);

  /* Start Individual Primary Phone */

  declare @PHONE_COUNTRYID uniqueidentifier = @ADDRESS_COUNTRYID;
  if @ADDRESS_COUNTRYID is null
    exec @PHONE_COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;

  select @COUNTRYCODE = COUNTRYCODE
  from dbo.COUNTRY
  where ID = @PHONE_COUNTRYID;

  if left(@PHONE_NUMBER, len(@COUNTRYCODE) + 1) = '+' + @COUNTRYCODE
    set @PHONE_NUMBER = ltrim(right(@PHONE_NUMBER, len(@PHONE_NUMBER) - (len(@COUNTRYCODE) + 1)));

  if (@PHONE_PHONETYPECODEID is not null) or (coalesce(@PHONE_NUMBER,'') <> '')
  begin
    set @PHONEID = newid();

    insert into dbo.[PHONE]
    (
      [ID],
      [CONSTITUENTID],
      [PHONETYPECODEID],
      [NUMBER],
      [ISPRIMARY],
      [INFOSOURCECODEID],
      [COUNTRYID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    values
    (
      @PHONEID,
      @ID,
      @PHONE_PHONETYPECODEID,
      @PHONE_NUMBER,
      1,
      @ADDRESS_INFOSOURCECODEID,
      @PHONE_COUNTRYID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );
  end
  /* End Primary Phone */

  declare @EMAILADDRESSID uniqueidentifier;

  /* Start Individual Primary Email Address */
  if (@EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (coalesce(@EMAILADDRESS_EMAILADDRESS,'') <> '')
  begin
    set @EMAILADDRESSID = newid();

    insert into dbo.[EMAILADDRESS]
    (
      [ID],
      [CONSTITUENTID],
      [EMAILADDRESSTYPECODEID],
      [EMAILADDRESS],
      [ISPRIMARY],
      [INFOSOURCECODEID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED]
    )
    values
    (
      @EMAILADDRESSID,
      @ID,
      @EMAILADDRESS_EMAILADDRESSTYPECODEID,
      @EMAILADDRESS_EMAILADDRESS,
      1,
      @ADDRESS_INFOSOURCECODEID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );
  end
  /* End Primary Email Address */

  /* Start security*/
  if coalesce(@SKIP_ADDING_SECURITYGROUPS, 0) = 0
    exec dbo.USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD @APPUSERID = @CURRENTAPPUSERID,
      @DATAFORMTEMPLATEID = @DATAFORMTEMPLATEID, @CONSTITUENTID = @ID, @DATEADDEDTOUSE = @CURRENTDATE,
      @CHANGEAGENTID = @CHANGEAGENTID;

  if coalesce(@SKIP_ADDING_SITES, 0) = 0
    exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD  @APPUSERID = @CURRENTAPPUSERID, @DATAFORMTEMPLATEID = @DATAFORMTEMPLATEID,
      @CONSTITUENTID = @ID, @DATEADDEDTOUSE = @CURRENTDATE, @CHANGEAGENTID = @CHANGEAGENTID;
  /* End security*/

  /*Start name format defaults*/
  insert into [dbo].[NAMEFORMAT]
  (
    [CONSTITUENTID],
    [NAMEFORMATTYPECODEID],
    [NAMEFORMATFUNCTIONID],
    [ADDEDBYID],
    [CHANGEDBYID],
    [DATEADDED],
    [DATECHANGED],
    [PRIMARYADDRESSEE],
    [PRIMARYSALUTATION]
  )
  select
    @ID,
    NFD.NAMEFORMATTYPECODEID,
    NFD.NAMEFORMATFUNCTIONID,
    @CHANGEAGENTID,
    @CHANGEAGENTID,
    @CURRENTDATE,
    @CURRENTDATE,
    NFD.PRIMARYADDRESSEE,
    NFD.PRIMARYSALUTATION
  from
    dbo.NAMEFORMATDEFAULT as NFD
  where
    NFD.APPLYTOCODE = 0 
  /*End name format defaults*/

  -------------- Insert Household Information --------------

  -- second member information (all of the biographical fields referencing 'spouse' now are just generically a second member unless @ISSPOUSERELATIONSHIP is true)

  declare @RELATIONSHIPID uniqueidentifier;

  if (@SPOUSEID is not null)
  begin
    if dbo.UFN_CONSTITUENT_CANAPPUSERMODIFY(@SPOUSEID, @CURRENTAPPUSERID) = 1
    begin
      set @EXISTINGSPOUSE = 1;
    end
    else
    begin
      raiserror ('ERR_SPOUSE_NOACCESS',13,1);
      return 0;
    end
  end

  -- if no existing second member is declared, but the last name field is set, create a new constituent record

  if (@SPOUSEID is null) and (coalesce(@SPOUSE_LASTNAME, '') <> '')
  begin
    set @SPOUSEID = newid();

    -- second member information

    insert into dbo.CONSTITUENT
    (
      [ID],
      [ISORGANIZATION],
      [KEYNAME],
      [FIRSTNAME],
      [MIDDLENAME],
      [MAIDENNAME],
      [NICKNAME],
      [TITLECODEID],
      [TITLE2CODEID],
      [SUFFIXCODEID],
      [SUFFIX2CODEID],
      [GENDERCODE],
      [BIRTHDATE],
      [PICTURE],
      [PICTURETHUMBNAIL],
      [WEBADDRESS],
      [MARITALSTATUSCODEID],
      [ISCONSTITUENT],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED],
      [GENDERCODEID]
    )
    values
    (
      @SPOUSEID,
      0,
      @SPOUSE_LASTNAME,
      @SPOUSE_FIRSTNAME,
      @SPOUSE_MIDDLENAME,
      @SPOUSE_MAIDENNAME,
      @SPOUSE_NICKNAME,
      @SPOUSE_TITLECODEID,
      @SPOUSE_TITLE2CODEID,
      @SPOUSE_SUFFIXCODEID,
      @SPOUSE_SUFFIX2CODEID,
      @SPOUSE_GENDERCODE,
      @SPOUSE_BIRTHDATE,
      null,
      null,
      '',
      @MARITALSTATUSCODEID,
      case when @ISCONSTITUENT = 1 then dbo.UFN_CONSTITUENCYCRITERIASPOUSE_SPOUSEISCONSTITUENT() else 0 end,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE,
      @SPOUSE_GENDERCODEID
    );

    -- security

    if coalesce(@SKIP_ADDING_SECURITYGROUPS, 0) = 0
      exec dbo.USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD @APPUSERID = @CURRENTAPPUSERID,
        @DATAFORMTEMPLATEID = @DATAFORMTEMPLATEID, @CONSTITUENTID = @SPOUSEID, @DATEADDEDTOUSE = @CURRENTDATE,
        @CHANGEAGENTID = @CHANGEAGENTID;

    if coalesce(@SKIP_ADDING_SITES, 0) = 0
      exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD @APPUSERID = @CURRENTAPPUSERID,
        @DATAFORMTEMPLATEID = @DATAFORMTEMPLATEID, @CONSTITUENTID = @SPOUSEID, @DATEADDEDTOUSE = @CURRENTDATE,
        @CHANGEAGENTID = @CHANGEAGENTID;
    -- end security


    -- start name format defaults

    insert into [dbo].[NAMEFORMAT]
    (
      [CONSTITUENTID],
      [NAMEFORMATTYPECODEID],
      [NAMEFORMATFUNCTIONID],
      [ADDEDBYID],
      [CHANGEDBYID],
      [DATEADDED],
      [DATECHANGED],
      [PRIMARYADDRESSEE],
      [PRIMARYSALUTATION]
    )
    select
        @SPOUSEID,
        NFD.NAMEFORMATTYPECODEID,
        NFD.NAMEFORMATFUNCTIONID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE,
        NFD.PRIMARYADDRESSEE,
        NFD.PRIMARYSALUTATION
    from
      dbo.NAMEFORMATDEFAULT as NFD
    where
      NFD.APPLYTOCODE = 0 
    -- end name format defaults


  if @SPOUSE_MAIDENNAME is not null and @SPOUSE_MAIDENNAME != ''
  begin
    insert into dbo.ALIAS
      (CONSTITUENTID, KEYNAME, FIRSTNAME, MIDDLENAME, DATEADDED, DATECHANGED, ADDEDBYID, CHANGEDBYID)
    values
      (@SPOUSEID, @SPOUSE_MAIDENNAME, @SPOUSE_FIRSTNAME, @SPOUSE_MIDDLENAME, @CURRENTDATE, @CURRENTDATE, @CHANGEAGENTID, @CHANGEAGENTID)
  end

  end -- create spouse     


  declare @EXISTINGID uniqueidentifier;

  -- if a second household member record exists: copy/insert contact info, create a relationship, create a household

  if @SPOUSEID is not null
  begin -- create household/relationship/contact info


    -- copy primary contact info, If @HOUSEHOLDCOPYPRIMARY and @ISSPOUSERELATIONSHIP then the copying will take place elsewhere

    if @COPYPRIMARYINFORMATION = 1 and not (@ISSPOUSERELATIONSHIP = 1 and @HOUSEHOLDCOPYPRIMARYCONTACTINFO = 1)
    begin 
      -- start second member primary address

      if((@ADDRESS_STATEID is not null) or (coalesce(@ADDRESS_ADDRESSBLOCK,'') <> '') or (coalesce(@ADDRESS_CITY,'') <> '') 
        or (coalesce(@ADDRESS_POSTCODE,'') <> '') or (@ADDRESS_ADDRESSTYPECODEID is not null) 
        or (@ADDRESS_COUNTRYID is not null) or (@ADDRESS_DONOTMAIL = 1) or (@ADDRESS_DONOTMAILREASONCODEID is not null))
      begin
        set @EXISTINGID = null;

        if exists (select ID from dbo.ADDRESS where CONSTITUENTID = @SPOUSEID and ISPRIMARY = 1)
          update
            dbo.ADDRESS
          set
            ISPRIMARY = 0,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where
            CONSTITUENTID = @SPOUSEID and
            ISPRIMARY = 1;

        select
          @EXISTINGID = ID 
        from
          dbo.ADDRESS
        where                                
          CONSTITUENTID = @SPOUSEID and
          DONOTMAIL = @ADDRESS_DONOTMAIL and
          COUNTRYID = @ADDRESS_COUNTRYID and
          STATEID = @ADDRESS_STATEID and
          ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK and 
          CITY = @ADDRESS_CITY and
          POSTCODE = @ADDRESS_POSTCODE and
          ((ADDRESSTYPECODEID is null and @ADDRESS_ADDRESSTYPECODEID is null) or
          (ADDRESSTYPECODEID = @ADDRESS_ADDRESSTYPECODEID))

        if @EXISTINGID is null
        begin
          declare @NEW_ADDRESSID as uniqueidentifier = newid();

          insert into dbo.ADDRESS
          (
            ID,
            CONSTITUENTID,
            ADDRESSTYPECODEID,
            ISPRIMARY,
            DONOTMAIL,
            COUNTRYID,
            STATEID,
            ADDRESSBLOCK,
            CITY,
            POSTCODE,
            CART,
            DPC,
            LOT,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED,
            DONOTMAILREASONCODEID
          )
          values
          (
            @NEW_ADDRESSID,
            @SPOUSEID,
            @ADDRESS_ADDRESSTYPECODEID,
            1,
            @ADDRESS_DONOTMAIL,
            @ADDRESS_COUNTRYID,
            @ADDRESS_STATEID,
            @ADDRESS_ADDRESSBLOCK,
            @ADDRESS_CITY,
            @ADDRESS_POSTCODE,
            @ADDRESS_CART,
            @ADDRESS_DPC,
            @ADDRESS_LOT,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE,
            @ADDRESS_DONOTMAILREASONCODEID
          )

          set @EXISTINGID = @NEW_ADDRESSID;
        end
        else
          update
            dbo.ADDRESS
          set 
            ISPRIMARY = 1,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where 
            ID = @EXISTINGID;

        if exists (select ID from dbo.ADDRESSVALIDATIONUPDATE where ID = @EXISTINGID)
          update
            dbo.ADDRESSVALIDATIONUPDATE
          set
            COUNTYCODEID = @ADDRESS_COUNTYCODEID,
            INFOSOURCECODEID = @ADDRESS_INFOSOURCECODEID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where
            ID = @EXISTINGID
        else
          insert into dbo.ADDRESSVALIDATIONUPDATE
          (
   ID,
            COUNTYCODEID,
            INFOSOURCECODEID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
          )
          values
          (
            @NEW_ADDRESSID,
            @ADDRESS_COUNTYCODEID,
            @ADDRESS_INFOSOURCECODEID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          )

      end -- end second member primary address


      -- start second member primary phone

      set @EXISTINGID = null

    --if we have a new phone number, add it.  otherwise, copy from primary member

    if (coalesce(@SPOUSE_PHONE_NUMBER, '') <> '')
    begin

        insert into dbo.PHONE
        (
            CONSTITUENTID,
            NUMBER,
            ISPRIMARY,
            INFOSOURCECODEID,
            COUNTRYID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
        )
        values
        (
            @SPOUSEID,
            @SPOUSE_PHONE_NUMBER,
            1,
            @ADDRESS_INFOSOURCECODEID,
            @PHONE_COUNTRYID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
        );

    end
      else if (@PHONE_PHONETYPECODEID is not null) or (coalesce(@PHONE_NUMBER,'') <> '')
      begin
        if exists (select ID from dbo.PHONE where CONSTITUENTID = @SPOUSEID and ISPRIMARY = 1)
          update 
            dbo.PHONE
          set
            ISPRIMARY = 0,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where
            CONSTITUENTID = @SPOUSEID and
            ISPRIMARY = 1;

        select
        @EXISTINGID = ID
        from
        dbo.PHONE
        where
        CONSTITUENTID = @SPOUSEID and
        PHONETYPECODEID = @PHONE_PHONETYPECODEID and
        NUMBER = @PHONE_NUMBER

        if @EXISTINGID is null
          insert into dbo.PHONE
          (
            CONSTITUENTID,
            PHONETYPECODEID,
            NUMBER,
            ISPRIMARY,
            INFOSOURCECODEID,
            COUNTRYID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
          )
          values
          (
            @SPOUSEID,
            @PHONE_PHONETYPECODEID,
            @PHONE_NUMBER,
            1,
            @ADDRESS_INFOSOURCECODEID,
            @PHONE_COUNTRYID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          );
        else
          update 
            dbo.PHONE
          set
            ISPRIMARY = 1,
            INFOSOURCECODEID = @ADDRESS_INFOSOURCECODEID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where
            ID = @EXISTINGID;

      end -- end second member primary phone


      -- start second member primary email address

      set @EXISTINGID = null

    --if we have a new phone number, add it.  otherwise, copy from primary member

    if (coalesce(@SPOUSE_EMAILADDRESS_EMAILADDRESS, '') <> '')
    begin
        insert into dbo.EMAILADDRESS
        (
        CONSTITUENTID,
        EMAILADDRESS,
        ISPRIMARY,
        INFOSOURCECODEID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
        )
        values
        (
        @SPOUSEID,
        @SPOUSE_EMAILADDRESS_EMAILADDRESS,
        1,
        @ADDRESS_INFOSOURCECODEID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
        );
    end
      else if (@EMAILADDRESS_EMAILADDRESSTYPECODEID is not null) or (coalesce(@EMAILADDRESS_EMAILADDRESS,'') <> '')
      begin
        if exists (select ID from dbo.EMAILADDRESS where CONSTITUENTID = @SPOUSEID and ISPRIMARY = 1)
          update 
            dbo.EMAILADDRESS
          set
            ISPRIMARY = 0,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where
            CONSTITUENTID = @SPOUSEID and
            ISPRIMARY = 1;

        select
          @EXISTINGID = ID
        from
          dbo.EMAILADDRESS
        where
          CONSTITUENTID = @SPOUSEID and
          EMAILADDRESSTYPECODEID = @EMAILADDRESS_EMAILADDRESSTYPECODEID and
          EMAILADDRESS = @EMAILADDRESS_EMAILADDRESS;

        if @EXISTINGID is null
          insert into dbo.EMAILADDRESS
          (
            CONSTITUENTID,
            EMAILADDRESSTYPECODEID,
            EMAILADDRESS,
            ISPRIMARY,
            INFOSOURCECODEID,
            ADDEDBYID,
            CHANGEDBYID,
            DATEADDED,
            DATECHANGED
          )
          values
          (
            @SPOUSEID,
            @EMAILADDRESS_EMAILADDRESSTYPECODEID,
            @EMAILADDRESS_EMAILADDRESS,
            1,
            @ADDRESS_INFOSOURCECODEID,
            @CHANGEAGENTID,
            @CHANGEAGENTID,
            @CURRENTDATE,
            @CURRENTDATE
          );
        else
          update 
            dbo.EMAILADDRESS
          set
            ISPRIMARY = 1,
            INFOSOURCECODEID = @ADDRESS_INFOSOURCECODEID,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where
            ID = @EXISTINGID;
      end -- second member primary email address


    end -- second member copy primary contact info


    -- start second member relationship with primary

    if (@SPOUSE_RECIPROCALTYPECODEID is not null) or (@SPOUSE_RELATIONSHIPTYPECODEID is not null)
    begin
      set @RELATIONSHIPID = newid();
      set @SETID = newid();

      insert into dbo.RELATIONSHIPSET
      (
        ID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
      )
      values
      (
        @SETID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      );

      insert into dbo.RELATIONSHIP
      (
        ID,
        RELATIONSHIPCONSTITUENTID,
        RECIPROCALCONSTITUENTID,
        RELATIONSHIPTYPECODEID,
        RECIPROCALTYPECODEID,
        ISSPOUSE,
        STARTDATE,
        RELATIONSHIPSETID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
      )
      values
      (
        @RELATIONSHIPID,
        @ID,
        @SPOUSEID,
        @SPOUSE_RELATIONSHIPTYPECODEID,
        @SPOUSE_RECIPROCALTYPECODEID,
        @ISSPOUSERELATIONSHIP,
        @SPOUSE_STARTDATE,
        @SETID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      );

      exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @ID, @SPOUSEID, @SPOUSE_RELATIONSHIPTYPECODEID, 
      @SPOUSE_RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE;

      --Recognition credits default information is not really passed in.

      select @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
             @PRIMARYRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
             @PRIMARYSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
      from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD      
      where RRD.CONSTITUENTTYPECODE=0 and RRD.RELATIONSHIPTYPECODEID=@SPOUSE_RELATIONSHIPTYPECODEID

      select @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
             @RECIPROCALRECOGNITIONTYPECODEID=RRD.REVENUERECOGNITIONTYPECODEID,
             @RECIPROCALSOFTCREDITMATCHFACTOR= RRD.MATCHFACTOR
      from dbo.RECOGNITIONRELATIONSHIPDEFAULT as RRD      
      where RRD.CONSTITUENTTYPECODE=0 and RRD.RELATIONSHIPTYPECODEID=@SPOUSE_RECIPROCALTYPECODEID

      exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @ID, @SPOUSEID,
                              @SPOUSE_STARTDATE, NULL, @PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @PRIMARYSOFTCREDITMATCHFACTOR,
                              @PRIMARYRECOGNITIONTYPECODEID, @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS, 
                              @RECIPROCALSOFTCREDITMATCHFACTOR, @RECIPROCALRECOGNITIONTYPECODEID, @CHANGEAGENTID, 0;    


      if (@ISSPOUSERELATIONSHIP = 1) and (not exists(select ID from dbo.DISABLEDWEALTHUPDATES where ID = @SPOUSEID))
        insert into dbo.DISABLEDWEALTHUPDATES
        (
          ID,
        ADDEDBYID,
          CHANGEDBYID,
          DATEADDED,
          DATECHANGED
        )
        values
        (
          @SPOUSEID,
          @CHANGEAGENTID,
          @CHANGEAGENTID,
          @CURRENTDATE,
          @CURRENTDATE
        )

    end -- second member relationship


    -- begin household creation 

    if (@ISSPOUSERELATIONSHIP = 1)
    begin
      declare @UPDATECONSTITUENTMARITALSTATUS bit = 0;
      if @MARITALSTATUSCODEID is null
        set @UPDATECONSTITUENTMARITALSTATUS = 1;

      exec dbo.USP_SPOUSERELATIONSHIP_SETUPHOUSEHOLD @ID, @SPOUSEID, @HOUSEHOLDCOPYPRIMARYCONTACTINFO,
        @CHANGEAGENTID, @CURRENTDATE,@SKIP_ADDING_SECURITYGROUPS,@CURRENTAPPUSERID,@SKIP_ADDING_SITES,
        @UPDATECONSTITUENTMARITALSTATUS, @COPYPRIMARYINFORMATION;
    end


    else
    begin -- non-spousal household 

    -- mostly cut and paste from USP_SPOUSERELATIONSHIP_SETUPHOUSEHOLD, but processing rules might be different

      declare @SECONDMEMBERHOUSEHOLDID uniqueidentifier;     

      select
        @SECONDMEMBERHOUSEHOLDID = GD.ID
      from
        dbo.GROUPMEMBER GM
      left outer join
        dbo.GROUPDATA GD on GM.GROUPID = GD.ID
      left outer join
       dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
      where
        GM.MEMBERID = @SPOUSEID and
        GD.GROUPTYPECODE = 0 and
        ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
          or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
          or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE));

      -- create a new household

      if (@SECONDMEMBERHOUSEHOLDID is null)
      begin -- create new non-spousal household

        declare @HOUSEHOLDID uniqueidentifier = newid();
        declare @NAMEFORMATFUNCTIONID uniqueidentifier;
        declare @HOUSEHOLDNAME nvarchar(100);

        select top(1) @NAMEFORMATFUNCTIONID = NAMEFORMATFUNCTIONID from dbo.HOUSEHOLDINFO order by DATEADDED;

        select @HOUSEHOLDNAME = left(dbo.UFN_NAMEFORMAT_FROMID(@NAMEFORMATFUNCTIONID, @ID), 100);

        insert into dbo.CONSTITUENT
        (
          ID, 
          KEYNAME, 
          ISORGANIZATION, 
          ISGROUP, 
          ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        values
        (
          @HOUSEHOLDID, 
          @HOUSEHOLDNAME, 
          0, 
          1, 
          @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        );

        if coalesce(@SKIP_ADDING_SECURITYGROUPS, 0) = 0
        begin
          exec dbo.USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD @APPUSERID = @CURRENTAPPUSERID,
            @DATAFORMTEMPLATEID = @DATAFORMTEMPLATEID, @CONSTITUENTID = @HOUSEHOLDID, @DATEADDEDTOUSE = @CURRENTDATE,
            @CHANGEAGENTID = @CHANGEAGENTID;
        end

        if coalesce(@SKIP_ADDING_SITES, 0) = 0
          exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD @APPUSERID = @CURRENTAPPUSERID,
            @DATAFORMTEMPLATEID = @DATAFORMTEMPLATEID, @CONSTITUENTID = @HOUSEHOLDID, @DATEADDEDTOUSE = @CURRENTDATE,
            @CHANGEAGENTID = @CHANGEAGENTID;

        insert into dbo.GROUPDATA
        (
          ID,
          GROUPTYPECODE,
          STARTDATE,
          GROUPTYPEID,
          ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        values
        (
          @HOUSEHOLDID,
          0,
          @CURRENTDATE,
          null,
          @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        );

        if @HOUSEHOLDCOPYPRIMARYCONTACTINFO = 1
          exec dbo.USP_CONSTITUENT_COPYPRIMARYCONTACTINFO @CHANGEAGENTID, @ID, @HOUSEHOLDID;

        declare @GROUPMEMBERID uniqueidentifier;
        exec dbo.USP_GROUPMEMBERADD @GROUPMEMBERID output, @CHANGEAGENTID, @HOUSEHOLDID, @ID;

        update 
          dbo.GROUPMEMBER 
        set 
          ISPRIMARY = 1,
     CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where 
          GROUPMEMBER.ID = @GROUPMEMBERID;

        set @GROUPMEMBERID = null;
        exec dbo.USP_GROUPMEMBERADD @GROUPMEMBERID output, @CHANGEAGENTID, @HOUSEHOLDID, @SPOUSEID;
      end -- create new non-spousal household

      else
      begin -- add the new constituent to the second member's household

        exec dbo.USP_GROUPMEMBERADD @GROUPMEMBERID output, @CHANGEAGENTID, @SECONDMEMBERHOUSEHOLDID, @ID;
      end -- add the new constituent to the second member's household


    end    -- non-spousal household 

  end -- create household/relationship/contact info


  -------------- Insert Organization Information --------------

  declare @ISORGANIZATION bit;
  set @RELATIONSHIPID = null;

  if (@ORGANIZATIONID is not null)
  begin
    if dbo.UFN_CONSTITUENT_CANAPPUSERMODIFY(@ORGANIZATIONID, @CURRENTAPPUSERID) = 1
    begin
      set @EXISTINGORGANIZATION = 1;
    end
    else
    begin
      raiserror ('ERR_ORGANIZATION_NOACCESS',13,1);
      return 0;
    end
  end

  if (@ORGANIZATIONID is not null) or (coalesce(@ORGANIZATION_NAME,'') <> '')
    set @ISORGANIZATION = 1;
  else
    set @ISORGANIZATION = 0;

  --If there is an organization create the relationship

  if @ISORGANIZATION = 1
  begin -- there is an org 

    declare @KEYNAME nvarchar(100);
    declare @KEYNAMEPREFIX nvarchar(50);
    declare @BUSINESSADDRESSID uniqueidentifier = null;
    declare @BUSINESSPHONEID uniqueidentifier = null;

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

    --if you are creating a new organization make a new constituent entry

    if @EXISTINGORGANIZATION = 0
    begin -- create new org


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

      exec dbo.USP_PARSE_ORGANIZATION_NAME @ORGANIZATION_NAME, @KEYNAME output, @KEYNAMEPREFIX output;

      ---------Organization Constituent Information---------

      exec dbo.USP_ORGANIZATION_ADD
                  @ID = @ORGANIZATIONID,
                @CURRENTAPPUSERID = @CURRENTAPPUSERID,
                @CHANGEAGENTID = @CHANGEAGENTID,
                @NAME = @ORGANIZATION_NAME,
                @WEBADDRESS = '',
                @PICTURE = null,
                @PICTURETHUMBNAIL = null,
                @INDUSTRYCODEID = null,        
                @NUMEMPLOYEES = 0,
                @NUMSUBSIDIARIES = 0,
                @PARENTCORPID = null,
                @ADDRESS_ADDRESSTYPECODEID = @ORGANIZATION_ADDRESSTYPECODEID,
                @ADDRESS_DONOTMAIL = @ORGANIZATION_DONOTMAIL,
                @ADDRESS_COUNTRYID = @ORGANIZATION_COUNTRYID,
                @ADDRESS_STATEID = @ORGANIZATION_STATEID,
                @ADDRESS_ADDRESSBLOCK = @ORGANIZATION_ADDRESSBLOCK,
                @ADDRESS_CITY = @ORGANIZATION_CITY,
                @ADDRESS_POSTCODE = @ORGANIZATION_POSTCODE,
                @PHONE_PHONETYPECODEID = @ORGANIZATION_PHONETYPECODEID,
                @PHONE_NUMBER = @ORGANIZATION_NUMBER,
                @SKIP_ADDING_SECURITYGROUPS = @SKIP_ADDING_SECURITYGROUPS,
                @EMAILADDRESS_EMAILADDRESSTYPECODEID = null,
                @EMAILADDRESS_EMAILADDRESS = '',
                @OMITFROMVALIDATION = @ORGANIZATION_OMITFROMVALIDATION,
                @CART = @ORGANIZATION_CART,
                @DPC = @ORGANIZATION_DPC,
                @LOT = @ORGANIZATION_LOT,
                @COUNTYCODEID = @ORGANIZATION_COUNTYCODEID,
                @CONGRESSIONALDISTRICTCODEID = @ORGANIZATION_CONGRESSIONALDISTRICTCODEID,
                @LASTVALIDATIONATTEMPTDATE = @ORGANIZATION_LASTVALIDATIONATTEMPTDATE,
                @VALIDATIONMESSAGE = @ORGANIZATION_VALIDATIONMESSAGE,
                @CERTIFICATIONDATA = @ORGANIZATION_CERTIFICATIONDATA,
                @ADDRESS_DONOTMAILREASONCODEID = @ORGANIZATION_DONOTMAILREASONCODEID,
                @ISPRIMARY = 0,
                @SKIP_ADDING_SITES = @SKIP_ADDING_SITES,
                @INFOSOURCECODEID = @ORGANIZATION_INFOSOURCECODEID,
          @ISCONSTITUENT = 1;

      if COALESCE(@SKIP_ADDING_SECURITYGROUPS, 0) = 0
      begin
        exec dbo.USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD @APPUSERID = @CURRENTAPPUSERID,
          @DATAFORMTEMPLATEID = @DATAFORMTEMPLATEID, @CONSTITUENTID = @ORGANIZATIONID, @DATEADDEDTOUSE = @CURRENTDATE,
          @CHANGEAGENTID = @CHANGEAGENTID;
      end

      if coalesce(@SKIP_ADDING_SITES, 0) = 0
        exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD @APPUSERID = @CURRENTAPPUSERID,
          @DATAFORMTEMPLATEID = @DATAFORMTEMPLATEID, @CONSTITUENTID = @ORGANIZATIONID, @DATEADDEDTOUSE = @CURRENTDATE,
          @CHANGEAGENTID = @CHANGEAGENTID;
    end -- create new org 


    ---------Organization Relationship---------

    if @ISMATCHINGGIFTRELATIONSHIP = 1
      update
        dbo.RELATIONSHIP
      set
        ISMATCHINGGIFTRELATIONSHIP = 0,
        DATECHANGED = @CURRENTDATE,
        CHANGEDBYID = @CHANGEAGENTID
      where
        RELATIONSHIPCONSTITUENTID = @ID and
        RECIPROCALCONSTITUENTID = @ORGANIZATIONID and
        ISMATCHINGGIFTRELATIONSHIP = 1;

    set @SETID = newid();

    insert into dbo.RELATIONSHIPSET
    (
      ID,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    )
    values
    (
      @SETID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    insert into dbo.RELATIONSHIP
    (
      ID,
      RELATIONSHIPCONSTITUENTID,
      RECIPROCALCONSTITUENTID,
      RELATIONSHIPTYPECODEID,
      RECIPROCALTYPECODEID,
      ISCONTACT,
      ISPRIMARYCONTACT,
      CONTACTTYPECODEID,
      STARTDATE,
      ENDDATE,
      POSITION,
      ISPRIMARYBUSINESS,
      ISMATCHINGGIFTRELATIONSHIP,
      RELATIONSHIPSETID,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    )
    values
    (
      @RELATIONSHIPID,
      @ID,
      @ORGANIZATIONID,
      @ORGANIZATION_RELATIONSHIPTYPECODEID,
      @ORGANIZATION_RECIPROCALTYPECODEID,
      @ISCONTACT,
      @ISPRIMARYCONTACT,
      @CONTACTTYPECODEID,
      @ORGANIZATION_STARTDATE,
      @ORGANIZATION_ENDDATE,
      '',
      1,
      @ISMATCHINGGIFTRELATIONSHIP,
      @SETID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @ID, @ORGANIZATIONID, @ORGANIZATION_RELATIONSHIPTYPECODEID,
      @ORGANIZATION_RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE;

    if (@JOBCATEGORYCODEID is not null) or (@CAREERLEVELCODEID is not null) or (len(@POSITION) > 0)
      insert into dbo.RELATIONSHIPJOBINFO
      (
        RELATIONSHIPSETID,
        JOBTITLE,
        JOBCATEGORYCODEID,
        CAREERLEVELCODEID,
        STARTDATE,
        ENDDATE,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
      )
      values
      (
        @SETID,
        @POSITION,
        @JOBCATEGORYCODEID,
        @CAREERLEVELCODEID,
        @ORGANIZATION_STARTDATE,
        @ORGANIZATION_ENDDATE,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      );

    exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @ID, @ORGANIZATIONID, @ORGANIZATION_STARTDATE, @ORGANIZATION_ENDDATE,
      @ORGANIZATION_PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @ORGANIZATION_PRIMARYSOFTCREDITMATCHFACTOR,
      @ORGANIZATION_PRIMARYRECOGNITIONTYPECODEID, @ORGANIZATION_RECIPROCALSOFTCREDITRELATIONSHIPEXISTS,
      @ORGANIZATION_RECIPROCALSOFTCREDITMATCHFACTOR, @ORGANIZATION_RECIPROCALRECOGNITIONTYPECODEID, @CHANGEAGENTID;

    --If the constituent is a contact of the organization create a contact address.

    if @ISCONTACT = 1 and @ORGANIZATIONID is not null
    begin
      declare @ORGANIZATIONRELATIONSHIPID uniqueidentifier;

      select 
        @ORGANIZATIONRELATIONSHIPID = [ID]
      from 
        dbo.[RELATIONSHIP]
      where
        [RELATIONSHIPSETID] = @SETID and
        [RECIPROCALCONSTITUENTID] = @ID;

      declare @BUSINESSCONTACTADDRESSID uniqueidentifier = newid();
      declare @BUSINESSPRIMARYADDRESSID uniqueidentifier = @BUSINESSADDRESSID;
      declare @BUSINESSPRIMARYPHONEID uniqueidentifier = @BUSINESSPHONEID;

      if @BUSINESSPRIMARYADDRESSID is null
        select @BUSINESSPRIMARYADDRESSID = ID from dbo.ADDRESS where CONSTITUENTID = @ORGANIZATIONID and ISPRIMARY = 1;

      if @BUSINESSPRIMARYPHONEID is null
        select @BUSINESSPRIMARYPHONEID = ID from dbo.PHONE where CONSTITUENTID = @ORGANIZATIONID and ISPRIMARY = 1;

      insert into dbo.ADDRESS
      (
        ID,
        CONSTITUENTID,
        RELATIONSHIPID,
        ADDRESSTYPECODEID,
        ISPRIMARY,
        DONOTMAIL,
        DONOTMAILREASONCODEID,
        COUNTRYID,
        STATEID,
        ADDRESSBLOCK,
        CITY,
        POSTCODE,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
      )
      select
        @BUSINESSCONTACTADDRESSID,
        @ID,
        @ORGANIZATIONRELATIONSHIPID,
        ADDRESSTYPECODEID,
        case when @ADDRESSID is null then 1 else 0 end,
        DONOTMAIL,
        DONOTMAILREASONCODEID,
        COUNTRYID,
        STATEID,
        ADDRESSBLOCK,
        CITY,
        POSTCODE,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      from
        dbo.ADDRESS
      where
        ID = @BUSINESSPRIMARYADDRESSID;

      insert into dbo.ADDRESSVALIDATIONUPDATE
      (
        [ID],
        [COUNTYCODEID],
        [CONGRESSIONALDISTRICTCODEID],
        [LASTVALIDATIONATTEMPTDATE],
        [VALIDATIONMESSAGE],
        [CERTIFICATIONDATA],
        [OMITFROMVALIDATION],
        [INFOSOURCECODEID],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED]
      )
      select
        @BUSINESSCONTACTADDRESSID,
        COUNTYCODEID,
        CONGRESSIONALDISTRICTCODEID,
        LASTVALIDATIONATTEMPTDATE,
        VALIDATIONMESSAGE,
        CERTIFICATIONDATA,
        OMITFROMVALIDATION,
        INFOSOURCECODEID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      from
        dbo.ADDRESSVALIDATIONUPDATE
      where
        ID = @BUSINESSPRIMARYADDRESSID;

      insert into dbo.PHONE
      (
        CONSTITUENTID,
        RELATIONSHIPID,
        ISPRIMARY,
        PHONETYPECODEID,
        NUMBER,
        INFOSOURCECODEID,
        COUNTRYID,
        DONOTCALL,
        DONOTCALLREASONCODEID,
        STARTTIME,
        ENDTIME,
        STARTDATE,
        ENDDATE,
        ISCONFIDENTIAL,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
      )
      select
        @ID,
        @ORGANIZATIONRELATIONSHIPID,
        case when @PHONEID is null then 1 else 0 end,
        PHONETYPECODEID,
        NUMBER,
        INFOSOURCECODEID,
        COUNTRYID,
        DONOTCALL,
        DONOTCALLREASONCODEID,
        STARTTIME,
        ENDTIME,
        STARTDATE,
        ENDDATE,
        ISCONFIDENTIAL,
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
      from
        dbo.PHONE
      where
        ID = @BUSINESSPRIMARYPHONEID;

      insert into dbo.EMAILADDRESS
      (
        CONSTITUENTID,
        RELATIONSHIPID,
        ISPRIMARY,
        EMAILADDRESSTYPECODEID,
        EMAILADDRESS,
        INFOSOURCECODEID,
        DONOTEMAIL,
        ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
      )
      select
        @ID,
        @ORGANIZATIONRELATIONSHIPID,
        case when @EMAILADDRESSID is null then 1 else 0 end,
        EMAILADDRESSTYPECODEID,
        EMAILADDRESS,
        INFOSOURCECODEID,
        DONOTEMAIL,
        @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
      from
        dbo.EMAILADDRESS
      where
        CONSTITUENTID = @ORGANIZATIONID and
        ISPRIMARY = 1;

    end --copy org contact to constituent


  end -- there is an org


end -- sproc body