USP_DATAFORMTEMPLATE_ADD_BATCHCOAUPDATECOMMIT

The save procedure used by the add dataform template "AddressFinder Batch Commit Add Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@VALIDATEONLY	bit	IN	Validate only
@BATCHNUMBER	nvarchar(100)	IN	Batch number
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@COAUPDATEID	uniqueidentifier	IN	AddressFinder ID
@ADDRESSID	uniqueidentifier	IN	Address ID
@CONSTITUENTID	uniqueidentifier	IN	Constituent
@ADDRESSBLOCK	nvarchar(150)	IN	Address
@CITY	nvarchar(50)	IN	City
@STATEID	uniqueidentifier	IN	State
@POSTCODE	nvarchar(12)	IN	Zip
@STDADDRESSBLOCK	nvarchar(150)	IN	Standardized address
@STDCITY	nvarchar(50)	IN	Standardized city
@STDSTATEID	uniqueidentifier	IN	Standardized state
@STDPOSTCODE	nvarchar(12)	IN	Standardized ZIP
@STDDPC	nvarchar(8)	IN	Standardized DPC
@STDCART	nvarchar(10)	IN	Standardized CART
@STDLOT	nvarchar(5)	IN	Standardized LOT
@COAADDRESSBLOCK	nvarchar(150)	IN	Change of address
@COACITY	nvarchar(50)	IN	Change of address city
@COASTATEID	uniqueidentifier	IN	Change of address state
@COAPOSTCODE	nvarchar(12)	IN	Change of address ZIP
@COADPC	nvarchar(8)	IN	Change of address DPC
@COACART	nvarchar(10)	IN	Change of address CART
@COALOT	nvarchar(5)	IN	Change of address LOT
@NCOARETURNCODE	tinyint	IN	NCOA return code
@NCOAFOOTNOTECODE	tinyint	IN	NCOA footnote
@NCOADPVFOOTNOTECODE	tinyint	IN	NCOA DPV footnote
@NCOAMOVEDATE	UDT_FUZZYDATE	IN	NCOA move date
@NCOAMAILGRADECODE	tinyint	IN	NCOA mail grade
@NCOADMASUPPRESSION	bit	IN	NCOA DMA suppression
@MOVETYPECODE	int	IN	Move type
Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_BATCHCOAUPDATECOMMIT
(
  @ID uniqueidentifier = null output,
  @VALIDATEONLY bit = 0,
  @BATCHNUMBER nvarchar(100),
  @CHANGEAGENTID uniqueidentifier,
  @COAUPDATEID uniqueidentifier,
  @ADDRESSID uniqueidentifier = null,
  @CONSTITUENTID uniqueidentifier = null,
  @ADDRESSBLOCK nvarchar(150) = '',
  @CITY nvarchar(50) = '',
  @STATEID uniqueidentifier = null,
  @POSTCODE nvarchar(12) = '',
  @STDADDRESSBLOCK nvarchar(150) = '',
  @STDCITY nvarchar(50) = '',
  @STDSTATEID uniqueidentifier = null,
  @STDPOSTCODE nvarchar(12) = '',
  @STDDPC nvarchar(8) = '',
  @STDCART nvarchar(10) = '',
  @STDLOT nvarchar(5) = '',
  @COAADDRESSBLOCK nvarchar(150) = '',
  @COACITY nvarchar(50) = '',
  @COASTATEID uniqueidentifier = null,
  @COAPOSTCODE nvarchar(12) = '',
  @COADPC nvarchar(8) = '',
  @COACART nvarchar(10) = '',
  @COALOT nvarchar(5) = '',
  @NCOARETURNCODE tinyint = 0,
  @NCOAFOOTNOTECODE tinyint = 0,
  @NCOADPVFOOTNOTECODE tinyint = 0,
  @NCOAMOVEDATE UDT_FUZZYDATE = null,
  @NCOAMAILGRADECODE tinyint = 0,
  @NCOADMASUPPRESSION bit = 0,
  @MOVETYPECODE int = 0
)
as
  set nocount on;

  exec dbo.[USP_SKIPSEARCHCONSTITUENTUPDATES];--skip the triggers while committing


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

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

  set @MOVETYPECODE = isnull(@MOVETYPECODE, 0);

  -- Get data tune-up settings and date last run

  declare @UPDATEADDRESSCODE tinyint;
  declare @SAVEFORMERADDRESS bit;
  declare @ADDRESSTYPECODEID uniqueidentifier;
  declare @NCOALASTSUBMITDATE datetime;
  declare @STEPCODE tinyint;
  declare @SUBMITHOUSEHOLD bit;
  declare @DONOTMAILREASONCODEID uniqueidentifier;                        
  declare @INFOSOURCECODEID uniqueidentifier;

  select
    @UPDATEADDRESSCODE = COAUPDATE.UPDATEADDRESSCODE,
    @SAVEFORMERADDRESS = COAUPDATE.SAVEFORMERADDRESS,
    @ADDRESSTYPECODEID = COAUPDATE.ADDRESSTYPECODEID,
    @NCOALASTSUBMITDATE = COAUPDATESUBMITPROCESS.DATELASTRUN,
    @STEPCODE = COAUPDATE.STEPCODE,
    @SUBMITHOUSEHOLD = COAUPDATESUBMITPROCESS.SUBMITHOUSEHOLD,
    @DONOTMAILREASONCODEID =
      case when @NCOAMAILGRADECODE = 4
        then COAUPDATE.MAILGRADEFREASONCODEID
        else case @NCOAFOOTNOTECODE
          when 1 then null    -- Depreciated - "Forwardable move" not applicable for do not mail reasons.

          when 2 then COAUPDATE.FOOTNOTECODE2REASONCODEID
          when 3 then COAUPDATE.FOOTNOTECODE3REASONCODEID
          when 4 then COAUPDATE.FOOTNOTECODE4REASONCODEID
          when 5 then null    -- Depreciated - "Forwardable move, but secondary address cannot be verified" not applicable for do not mail reasons.

          when 6 then COAUPDATE.FOOTNOTECODE6REASONCODEID
          when 7 then COAUPDATE.FOOTNOTECODE7REASONCODEID
          else null
        end
      end,
    @INFOSOURCECODEID = COAUPDATE.INFOSOURCECODEID
  from
    dbo.[COAUPDATE]
    inner join dbo.[COAUPDATESUBMITPROCESS] on COAUPDATE.ID = COAUPDATESUBMITPROCESS.COAUPDATEID
  where
    COAUPDATE.ID = @COAUPDATEID;

  -- Update change of address process step code

  if @STEPCODE <> 3
    update dbo.[COAUPDATE]
    set
      STEPCODE = 3,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    where
      ID = @COAUPDATEID;

  -- processing flags

  declare @INSERT bit;
  declare @UPDATE bit;

  if @ADDRESSID is not null
  begin
    set @ID = @ADDRESSID;

    if @UPDATEADDRESSCODE = 0 -- update only addresses with forwardable moves (NCOA changes)

    begin
      if @NCOAFOOTNOTECODE in (1,5) -- forwardable move

      begin
        if @SAVEFORMERADDRESS = 1
          set @INSERT = 1;
        else
          set @UPDATE = 1;
      end
    end
    else if @UPDATEADDRESSCODE = 1 -- update all addresses (NCOA changes AND validations)

    begin
      if @NCOAFOOTNOTECODE in (1,5) and @SAVEFORMERADDRESS = 1
        set @INSERT = 1;
      else
        set @UPDATE = 1;
    end
    else -- update only NCOA changes (@UPDATEADDRESSCODE = 2, good and bad footnotes) 

    begin
      if @NCOAFOOTNOTECODE in (1,5) -- forwardable move footnotes

      begin
        if @SAVEFORMERADDRESS = 1
          set @INSERT = 1;
        else
          set @UPDATE = 1;
      end
      else if @NCOAFOOTNOTECODE in (2,3,4,6,7) -- all other NCOA footnotes (all remaining footnotes except 0 which indicates no change)

        set @UPDATE = 1;
      -- This if branch intentionally left without an ending else. If additional footnotes are returned in the future, they will need accommodated.

    end

    -- Declare variables to be used

    declare @COUNTRYID uniqueidentifier;
    declare @OLDISPRIMARY bit;  -- Use this only when updating the main address, not shared addresses.

    declare @OLDSTATEID uniqueidentifier;
    declare @OLDADDRESSBLOCK nvarchar(150);
    declare @OLDCITY nvarchar(50);
    declare @OLDPOSTCODE nvarchar(12);
    declare @OLDADDRESSTYPECODEID uniqueidentifier;
    declare @OLDDONOTMAIL bit;  -- Use this only when updating the main address, not shared addresses.

    declare @OLDNCOALASTSUBMITDATE datetime;
    declare @MATCHING table(ID uniqueidentifier, CONSTITUENTID uniqueidentifier, ISPRIMARY bit, DONOTMAIL bit, ADDRESSTYPECODEID uniqueidentifier, RELATIONSHIPID uniqueidentifier);
    declare @UPDATEPRIMARYMEMBER bit;
    declare @PRIMARYMEMBERID uniqueidentifier;
    declare @PRIMARYMEMBERADDRESSID uniqueidentifier;
    declare @PRIMARYMEMEBEROLDISPRIMARY bit;
    declare @PRIMARYMEMEBEROLDDONOTMAIL bit;
    declare @TABLENAME nvarchar(128) = 'COAUPDATECREATE_' + replace(cast(@COAUPDATEID as nvarchar(36)), '-', '_'); -- This table contains data submitted in current execution

    declare @TEMPSQL nvarchar(max);
    declare @DELETESQL nvarchar(max);

    if object_id('tempdb..#MATCHINGCONSTITUENTS') is null
    begin
      create table #MATCHINGCONSTITUENTS (ID uniqueidentifier);
    end

    select
      @COUNTRYID = (select ID from dbo.COUNTRY where USEFORCOAUPDATE = 1),
      @OLDISPRIMARY = [ADDRESS].ISPRIMARY,
      @OLDADDRESSBLOCK  = [ADDRESS].ADDRESSBLOCK,
      @OLDCITY  = [ADDRESS].CITY,
      @OLDSTATEID  = [ADDRESS].STATEID,
      @OLDPOSTCODE  = [ADDRESS].POSTCODE,
      @OLDADDRESSTYPECODEID = [ADDRESS].ADDRESSTYPECODEID,
      @OLDDONOTMAIL = [ADDRESS].DONOTMAIL,
      @OLDNCOALASTSUBMITDATE = ADDRESSVALIDATIONUPDATE.NCOALASTSUBMITDATE
    from dbo.[ADDRESS]
    left outer join dbo.[ADDRESSVALIDATIONUPDATE] on [ADDRESS].ID = ADDRESSVALIDATIONUPDATE.ID
    where
      [ADDRESS].ID = @ADDRESSID;

    -- Check for household record, grab primary member address if individual move

    if exists (select ID from dbo.CONSTITUENT where ID = @CONSTITUENTID and ISGROUP = 1) and @MOVETYPECODE = 1
    begin
      select
        @PRIMARYMEMBERADDRESSID = [ADDRESS].ID,
        @PRIMARYMEMBERID = [ADDRESS].CONSTITUENTID,
        @PRIMARYMEMEBEROLDISPRIMARY = [ADDRESS].ISPRIMARY,
        @PRIMARYMEMEBEROLDDONOTMAIL = [ADDRESS].DONOTMAIL, 
        @UPDATEPRIMARYMEMBER = case when ADDRESSVALIDATIONUPDATE.NCOALASTSUBMITDATE <> @NCOALASTSUBMITDATE or ADDRESSVALIDATIONUPDATE.NCOALASTSUBMITDATE is null then 1 else 0 end
      from dbo.[ADDRESS]
        inner join dbo.CONSTITUENT on [ADDRESS].CONSTITUENTID = CONSTITUENT.ID
        inner join dbo.GROUPMEMBER on CONSTITUENT.ID = GROUPMEMBER.MEMBERID and GROUPMEMBER.ISPRIMARY = 1
        left join dbo.[ADDRESSVALIDATIONUPDATE] on ADDRESSVALIDATIONUPDATE.ID = [ADDRESS].ID
      where
        GROUPMEMBER.GROUPID = @CONSTITUENTID
        and(
          [ADDRESS].ADDRESSBLOCK = @OLDADDRESSBLOCK
          and [ADDRESS].CITY = @OLDCITY
          and ( ([ADDRESS].STATEID = @OLDSTATEID) or ([ADDRESS].STATEID is null and @OLDSTATEID is null) )
  and [ADDRESS].POSTCODE = @OLDPOSTCODE
          and ( ([ADDRESS].ADDRESSTYPECODEID = @OLDADDRESSTYPECODEID) or ([ADDRESS].ADDRESSTYPECODEID is null and @OLDADDRESSTYPECODEID is null) ))    
    end

    -- make sure the record hasn't already been updated

    if @OLDNCOALASTSUBMITDATE <> @NCOALASTSUBMITDATE or @OLDNCOALASTSUBMITDATE is null
    begin

      --determine matching addresses

      if (@INSERT = 1 or @UPDATE = 1)
      begin
        if @MOVETYPECODE = 2 --Household

        begin

          insert into #MATCHINGCONSTITUENTS
          (ID)
          (select distinct
            CONSTITUENTID
          from dbo.UFN_ADDRESS_MATCHINGHOUSEHOLDRECORDS(@CONSTITUENTID, @COUNTRYID, @OLDSTATEID, @OLDADDRESSBLOCK, @OLDCITY, @OLDPOSTCODE, @OLDADDRESSTYPECODEID));

          --Delete constituents who are already present in current process execution, since they will be processed on their own


            if exists(select 1 from sysobjects where XTYPE = 'U' and NAME = @TABLENAME) -- Checking table exist (In some unit test we don't have this process table we directly call commit)

            begin
              set @DELETESQL =   'delete from #MATCHINGCONSTITUENTS where ID in (Select CONSTITUENTID from dbo.' + @TABLENAME + ');'
              exec(@DELETESQL);
            end

          -- create table of matching addresses

          insert into @MATCHING
          (ID, CONSTITUENTID, ISPRIMARY, DONOTMAIL)
          (select
            [ADDRESS].ID,
            [ADDRESS].CONSTITUENTID,
            [ADDRESS].ISPRIMARY,
            [ADDRESS].DONOTMAIL
          from
            dbo.[ADDRESS] 
            left join dbo.[ADDRESSVALIDATIONUPDATE] on ADDRESSVALIDATIONUPDATE.ID = [ADDRESS].ID
          where
            COUNTRYID = @COUNTRYID 
            and ADDRESSBLOCK = @OLDADDRESSBLOCK 
            and CITY = @OLDCITY 
            and ( (STATEID = @OLDSTATEID) or (STATEID is null and @OLDSTATEID is null) ) 
            and POSTCODE = @OLDPOSTCODE
            and ( (ADDRESSTYPECODEID = @OLDADDRESSTYPECODEID) or (ADDRESSTYPECODEID is null and @OLDADDRESSTYPECODEID is null) )
            and CONSTITUENTID in (select ID from #MATCHINGCONSTITUENTS)
            and (ADDRESSVALIDATIONUPDATE.NCOALASTSUBMITDATE is null or ADDRESSVALIDATIONUPDATE.NCOALASTSUBMITDATE <> @NCOALASTSUBMITDATE));

          drop table #MATCHINGCONSTITUENTS;

          -- some constituent addresses may have already been updated by this process

          -- so only keep their records if they have at least one matching address

          -- that has not been updated


        end

        if @MOVETYPECODE = 3 --Business

        begin
          insert into @MATCHING
          (ID, CONSTITUENTID, ISPRIMARY, DONOTMAIL, ADDRESSTYPECODEID, RELATIONSHIPID)
          (select
            [ADDRESS].ID, [ADDRESS].CONSTITUENTID, [ADDRESS].ISPRIMARY, [ADDRESS].DONOTMAIL, [ADDRESS].ADDRESSTYPECODEID, [ADDRESS].RELATIONSHIPID
          from
            dbo.[ADDRESS]
            inner join dbo.RELATIONSHIP on RELATIONSHIP.ID = [ADDRESS].RELATIONSHIPID
            left join dbo.[ADDRESSVALIDATIONUPDATE] on ADDRESSVALIDATIONUPDATE.ID = [ADDRESS].ID
          where
            RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
            and RELATIONSHIP.ISCONTACT = 1
            and [ADDRESS].COUNTRYID = @COUNTRYID
            and [ADDRESS].ADDRESSBLOCK = @OLDADDRESSBLOCK
            and [ADDRESS].CITY = @OLDCITY
            and ( ([ADDRESS].STATEID = @OLDSTATEID) or ([ADDRESS].STATEID is null and @OLDSTATEID is null) )
            and [ADDRESS].POSTCODE = @OLDPOSTCODE
            -- Don't check address type code for contacts

            --and ( (ADDRESSTYPECODEID = @OLDADDRESSTYPECODEID) or (ADDRESSTYPECODEID is null and @OLDADDRESSTYPECODEID is null) )

            and (ADDRESSVALIDATIONUPDATE.NCOALASTSUBMITDATE is null or ADDRESSVALIDATIONUPDATE.NCOALASTSUBMITDATE <> @NCOALASTSUBMITDATE));

        end
      end

      if @INSERT = 1
      begin
        set @ID = newID();

        -- matching addresses to be updated on household members or business contacts

        if exists (select top 1 1 from @MATCHING)
        begin

          -- Clear all the primary flags for the constituent if the @MATCHING

          -- address for that constituent is primary.

          update dbo.[ADDRESS]
          set
            ISPRIMARY = 0,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where
            CONSTITUENTID in (
              select [MATCHING].CONSTITUENTID from @MATCHING as MATCHING
              where [MATCHING].ISPRIMARY = 1
            )
            and ISPRIMARY = 1;

          -- update the existing records

          update dbo.[ADDRESS]
          set
            ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
            DONOTMAIL = 1,
            DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
            HISTORICALENDDATE = coalesce(dbo.UFN_DATE_FROMFUZZYDATE(@NCOAMOVEDATE), @CURRENTDATE),
            RELATIONSHIPID =
              case
                when @MOVETYPECODE = 3 --Business

                  then null
                else RELATIONSHIPID
              end,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where
            exists (
              select ID
              from @MATCHING
              where ID = [ADDRESS].ID
            );

          -- insert address validation update data where there are no records with the new number

          if @MOVETYPECODE = 0 --Only insert/update records if this was not a move

          begin
            update dbo.[ADDRESSVALIDATIONUPDATE]
            set
              NCOALASTSUBMITDATE = @NCOALASTSUBMITDATE,
              NCOARETURNCODE = @NCOARETURNCODE,
              NCOAFOOTNOTECODE = @NCOAFOOTNOTECODE,
              NCOADPVFOOTNOTECODE = @NCOADPVFOOTNOTECODE,
              NCOAMOVEDATE = @NCOAMOVEDATE,
              NCOADMASUPPRESSION = @NCOADMASUPPRESSION,
              NCOAMAILGRADECODE = @NCOAMAILGRADECODE,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CURRENTDATE,
              INFOSOURCECODEID = coalesce(ADDRESSVALIDATIONUPDATE.INFOSOURCECODEID, @INFOSOURCECODEID)
            where
              exists (
                select ID
                from @MATCHING
                where ID = ADDRESSVALIDATIONUPDATE.ID
              );

            insert into dbo.[ADDRESSVALIDATIONUPDATE]
            (ID, NCOALASTSUBMITDATE, NCOARETURNCODE, NCOAFOOTNOTECODE, NCOADPVFOOTNOTECODE, NCOAMOVEDATE, NCOADMASUPPRESSION, NCOAMAILGRADECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, INFOSOURCECODEID)
            (select
              ID, @NCOALASTSUBMITDATE, @NCOARETURNCODE, @NCOAFOOTNOTECODE, @NCOADPVFOOTNOTECODE, @NCOAMOVEDATE, @NCOADMASUPPRESSION, @NCOAMAILGRADECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @INFOSOURCECODEID
            from dbo.[ADDRESS]
            where
              exists (
                select ID
                from @MATCHING MATCHING
                where ID = [ADDRESS].ID
                and ID not in (select ID from dbo.[ADDRESSVALIDATIONUPDATE])
              ));
          end
          -- Bug 451186 Aaron Crawford - This is now able to add address validation update information to any

          -- addresses added to shared household constituents by first pairing new address ID's with a constituent.


          -- Create table variable for Address and Constituent ID pairings

          declare @ADDRESSESTOADD table(ID uniqueidentifier default newId(), CONSTITUENTID uniqueidentifier, ADDRESSID uniqueidentifier);

          -- insert new address

     if @MOVETYPECODE = 3 --Business

          begin
            -- Create new guid/constituent pairings for inserting new addresses

            -- A business contact may have the same address linked to the same business more than once, all addresses should be updated

            insert into @ADDRESSESTOADD
            (CONSTITUENTID, ADDRESSID)
            (select
              [MATCHING].CONSTITUENTID, [MATCHING].ID
            from @MATCHING MATCHING);

            -- Insert new addresses using pairings from above

            insert into dbo.[ADDRESS]
            (ID, CONSTITUENTID, ADDRESSTYPECODEID, ADDRESSBLOCK, CITY, STATEID, POSTCODE, CART, DPC, LOT, COUNTRYID, ISPRIMARY, DONOTMAIL, RELATIONSHIPID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            (select
              PAIRED.ID, [MATCHING].CONSTITUENTID, [MATCHING].ADDRESSTYPECODEID, @STDADDRESSBLOCK, @STDCITY, @STDSTATEID, @STDPOSTCODE, @STDCART, @STDDPC, @STDLOT, @COUNTRYID, [MATCHING].ISPRIMARY, [MATCHING].DONOTMAIL, [MATCHING].RELATIONSHIPID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            from
              @ADDRESSESTOADD PAIRED
              left join @MATCHING MATCHING
                on PAIRED.ADDRESSID = [MATCHING].ID);

            -- Insert new footnotes to show validation info for the newly created addresses

            insert into dbo.[ADDRESSVALIDATIONUPDATE]
            (ID, NCOALASTSUBMITDATE, NCOARETURNCODE, NCOAFOOTNOTECODE, NCOADPVFOOTNOTECODE, NCOAMOVEDATE, NCOADMASUPPRESSION, NCOAMAILGRADECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, INFOSOURCECODEID)
            (select
              ID, @NCOALASTSUBMITDATE, @NCOARETURNCODE, @NCOAFOOTNOTECODE, @NCOADPVFOOTNOTECODE, @NCOAMOVEDATE, @NCOADMASUPPRESSION, @NCOAMAILGRADECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @INFOSOURCECODEID
            from @ADDRESSESTOADD);            
          end
          else
          begin
            -- Create new guid/constituent pairings for inserting new addresses

            -- Using "distinct" since non-business contacts shouldn't have the same address linked more than once

            insert into @ADDRESSESTOADD
            (CONSTITUENTID, ADDRESSID)
            (select distinct
              [MATCHING].CONSTITUENTID, [MATCHING].ID
            from
              @MATCHING MATCHING
            where [MATCHING].ID in
              (select top(1) TIEBREAKER.ID
              from @MATCHING TIEBREAKER
              where TIEBREAKER.CONSTITUENTID = [MATCHING].CONSTITUENTID
              order by ISPRIMARY desc, DONOTMAIL desc)
            ); -- The tiebreaker here is used to pick one of multiple addresses that will be used for ISPRIMARY and DONOTMAIL attributes, giving precedence in that order.


            -- Insert new addresses using pairings from above

            insert into dbo.[ADDRESS]
            (ID, CONSTITUENTID, ADDRESSTYPECODEID, ADDRESSBLOCK, CITY, STATEID, POSTCODE, CART, DPC, LOT, COUNTRYID, ISPRIMARY, DONOTMAIL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
            (select
              PAIRED.ID, [MATCHING].CONSTITUENTID, @OLDADDRESSTYPECODEID, @STDADDRESSBLOCK, @STDCITY, @STDSTATEID, @STDPOSTCODE, @STDCART, @STDDPC, @STDLOT, @COUNTRYID, [MATCHING].ISPRIMARY, [MATCHING].DONOTMAIL, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            from
              @ADDRESSESTOADD PAIRED
              inner join @MATCHING MATCHING
                on PAIRED.ADDRESSID = [MATCHING].ID);

            -- Insert new footnotes to show validation info for the newly created addresses

            insert into dbo.[ADDRESSVALIDATIONUPDATE]
            (ID, NCOALASTSUBMITDATE, NCOARETURNCODE, NCOAFOOTNOTECODE, NCOADPVFOOTNOTECODE, NCOAMOVEDATE, NCOADMASUPPRESSION, NCOAMAILGRADECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, INFOSOURCECODEID)
            (select
              ID, @NCOALASTSUBMITDATE, @NCOARETURNCODE, @NCOAFOOTNOTECODE, @NCOADPVFOOTNOTECODE, @NCOAMOVEDATE, @NCOADMASUPPRESSION, @NCOAMAILGRADECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @INFOSOURCECODEID
            from @ADDRESSESTOADD);
          end
        end

        -- update existing address and remove primary indicator

        update dbo.[ADDRESS]
        set
          ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
          DONOTMAIL = 1,
          DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
          ISPRIMARY = 0,
          HISTORICALENDDATE = coalesce(dbo.UFN_DATE_FROMFUZZYDATE(@NCOAMOVEDATE), @CURRENTDATE),
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where
          ID = @ADDRESSID;

        if exists (select ID from dbo.[ADDRESSVALIDATIONUPDATE] where ID = @ADDRESSID) and @MOVETYPECODE = 0
          update dbo.[ADDRESSVALIDATIONUPDATE]
          set 
            NCOALASTSUBMITDATE = @NCOALASTSUBMITDATE,
            NCOARETURNCODE = @NCOARETURNCODE,
            NCOAFOOTNOTECODE = @NCOAFOOTNOTECODE,
            NCOADPVFOOTNOTECODE = @NCOADPVFOOTNOTECODE,
            NCOAMOVEDATE = @NCOAMOVEDATE,
            NCOADMASUPPRESSION = @NCOADMASUPPRESSION,
            NCOAMAILGRADECODE = @NCOAMAILGRADECODE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE,
            INFOSOURCECODEID = coalesce(ADDRESSVALIDATIONUPDATE.INFOSOURCECODEID, @INFOSOURCECODEID)
          where
            ID = @ADDRESSID;
        else if @MOVETYPECODE = 0
          insert into dbo.[ADDRESSVALIDATIONUPDATE]
          (ID, NCOALASTSUBMITDATE, NCOARETURNCODE, NCOAFOOTNOTECODE, NCOADPVFOOTNOTECODE, NCOAMOVEDATE, NCOADMASUPPRESSION, NCOAMAILGRADECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, INFOSOURCECODEID)
          values
          (@ADDRESSID, @NCOALASTSUBMITDATE, @NCOARETURNCODE, @NCOAFOOTNOTECODE, @NCOADPVFOOTNOTECODE, @NCOAMOVEDATE, @NCOADMASUPPRESSION, @NCOAMAILGRADECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @INFOSOURCECODEID);

        -- insert new address for the main constituent

        insert into dbo.[ADDRESS]
        (ID, CONSTITUENTID, ADDRESSTYPECODEID, ADDRESSBLOCK, CITY, STATEID, POSTCODE, CART, DPC, LOT, COUNTRYID, ISPRIMARY, DONOTMAIL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values
        (@ID, @CONSTITUENTID, @OLDADDRESSTYPECODEID, @STDADDRESSBLOCK, @STDCITY, @STDSTATEID, @STDPOSTCODE, @STDCART, @STDDPC, @STDLOT, @COUNTRYID, @OLDISPRIMARY, @OLDDONOTMAIL, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

        -- insert new address validation update data for new address

        insert into dbo.[ADDRESSVALIDATIONUPDATE]
          (ID, NCOALASTSUBMITDATE, NCOARETURNCODE, NCOAFOOTNOTECODE, NCOADPVFOOTNOTECODE, NCOAMOVEDATE, NCOADMASUPPRESSION, NCOAMAILGRADECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, INFOSOURCECODEID)
        values
          (@ID, @NCOALASTSUBMITDATE, @NCOARETURNCODE, @NCOAFOOTNOTECODE, @NCOADPVFOOTNOTECODE, @NCOAMOVEDATE, @NCOADMASUPPRESSION, @NCOAMAILGRADECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @INFOSOURCECODEID);

        -- If primary member is already present in current process execution then we don't need to process it again so flipping the bit

       if @UPDATEPRIMARYMEMBER = 1 
       begin
            --Fetch CONSTITUENTS that are present in current process (i.e. submitted to Saboten)

            if exists(select 1 from sysobjects where XTYPE = 'U' and NAME = @TABLENAME) -- Checking table exist (In some unit test we don't have this process table we directly call commit)

            begin
                SET @TEMPSQL = 'If exists(select CONSTITUENTID from  dbo.' + @TABLENAME
                SET @TEMPSQL = @TEMPSQL + ' WHERE CONSTITUENTID = ''' + CONVERT(nvarchar(36),@PRIMARYMEMBERID) + ''')'
                SET @TEMPSQL = @TEMPSQL + ' SET @UPDATEPRIMARYMEMBER =  0;'
                exec sp_executesql @TEMPSQL, N'@TABLENAME nvarchar(128), @PRIMARYMEMBERID uniqueidentifier, @UPDATEPRIMARYMEMBER bit OUTPUT',@TABLENAME , @PRIMARYMEMBERID , @UPDATEPRIMARYMEMBER OUTPUT;
            end
        end

        -- If updating a household address and the move type is "Individual", update the primary member as well

        if @UPDATEPRIMARYMEMBER = 1
        begin
          -- update existing address and remove primary indicator

          update dbo.[ADDRESS]
          set
            ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
            DONOTMAIL = 1,
            DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
            ISPRIMARY = 0,
            HISTORICALENDDATE = coalesce(dbo.UFN_DATE_FROMFUZZYDATE(@NCOAMOVEDATE), @CURRENTDATE),
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where
            ID = @PRIMARYMEMBERADDRESSID;

          if exists (select ID from dbo.[ADDRESSVALIDATIONUPDATE] where ID = @PRIMARYMEMBERADDRESSID) and @MOVETYPECODE = 0
            update dbo.[ADDRESSVALIDATIONUPDATE]
            set 
              NCOALASTSUBMITDATE = @NCOALASTSUBMITDATE,
              NCOARETURNCODE = @NCOARETURNCODE,
              NCOAFOOTNOTECODE = @NCOAFOOTNOTECODE,
              NCOADPVFOOTNOTECODE = @NCOADPVFOOTNOTECODE,
              NCOAMOVEDATE = @NCOAMOVEDATE,
              NCOADMASUPPRESSION = @NCOADMASUPPRESSION,
              NCOAMAILGRADECODE = @NCOAMAILGRADECODE,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CURRENTDATE,
              INFOSOURCECODEID = coalesce(ADDRESSVALIDATIONUPDATE.INFOSOURCECODEID, @INFOSOURCECODEID)
            where
              ID = @PRIMARYMEMBERADDRESSID;
          else if  @MOVETYPECODE = 0
            insert into dbo.[ADDRESSVALIDATIONUPDATE]
            (ID, NCOALASTSUBMITDATE, NCOARETURNCODE, NCOAFOOTNOTECODE, NCOADPVFOOTNOTECODE, NCOAMOVEDATE, NCOADMASUPPRESSION, NCOAMAILGRADECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, INFOSOURCECODEID)
            values
            (@PRIMARYMEMBERADDRESSID, @NCOALASTSUBMITDATE, @NCOARETURNCODE, @NCOAFOOTNOTECODE, @NCOADPVFOOTNOTECODE, @NCOAMOVEDATE, @NCOADMASUPPRESSION, @NCOAMAILGRADECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @INFOSOURCECODEID);

          declare @PRIMARYMEMBERNEWADDRESSID uniqueidentifier = newid();

          -- insert new address

          insert into dbo.[ADDRESS]
          (ID, CONSTITUENTID, ADDRESSTYPECODEID, ADDRESSBLOCK, CITY, STATEID, POSTCODE, CART, DPC, LOT, COUNTRYID, ISPRIMARY, DONOTMAIL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
          values
          (@PRIMARYMEMBERNEWADDRESSID, @PRIMARYMEMBERID, @OLDADDRESSTYPECODEID, @STDADDRESSBLOCK, @STDCITY, @STDSTATEID, @STDPOSTCODE, @STDCART, @STDDPC, @STDLOT, @COUNTRYID, @PRIMARYMEMEBEROLDISPRIMARY, @PRIMARYMEMEBEROLDDONOTMAIL, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

          -- insert new address validation update data for new address

          insert into dbo.[ADDRESSVALIDATIONUPDATE]
            (ID, NCOALASTSUBMITDATE, NCOARETURNCODE, NCOAFOOTNOTECODE, NCOADPVFOOTNOTECODE, NCOAMOVEDATE, NCOADMASUPPRESSION, NCOAMAILGRADECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, INFOSOURCECODEID)
          values
            (@PRIMARYMEMBERNEWADDRESSID, @NCOALASTSUBMITDATE, @NCOARETURNCODE, @NCOAFOOTNOTECODE, @NCOADPVFOOTNOTECODE, @NCOAMOVEDATE, @NCOADMASUPPRESSION, @NCOAMAILGRADECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @INFOSOURCECODEID);
        end

      end

      if @UPDATE = 1
      begin
        -- matching addresses to be updated on household members or business contacts

        if exists (select top 1 1 from @MATCHING)
        begin
          -- Clear all the primary flags for the constituent if the @MATCHING

          -- address for that constituent is primary.  (Don't clear the primary

          -- flag for the primary address; this saves us an unnecessary update.)

          update dbo.[ADDRESS]
          set
            ISPRIMARY = 0,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where
            CONSTITUENTID in (
              select M.CONSTITUENTID from @MATCHING M
              where M.ISPRIMARY = 1
            )
            and ID not in (
              select M.ID from @MATCHING M
              where M.ISPRIMARY = 1
            )
            and ISPRIMARY = 1;

          -- update the existing records

          update 
            ADDRESS
          set
            [ADDRESS].ISPRIMARY = [MATCHING].ISPRIMARY,
            [ADDRESS].ADDRESSBLOCK = @STDADDRESSBLOCK,
            [ADDRESS].CITY = @STDCITY,
            [ADDRESS].STATEID = @STDSTATEID,
            [ADDRESS].POSTCODE = @STDPOSTCODE,
            [ADDRESS].CART = @STDCART,
            [ADDRESS].DPC = @STDDPC,
            [ADDRESS].LOT = @STDLOT,
            [ADDRESS].DONOTMAIL = case 
              when [ADDRESS].DONOTMAIL = 1 then 1
              when @NCOAFOOTNOTECODE in (0, 1, 5, 7) and (@NCOAMAILGRADECODE is null or @NCOAMAILGRADECODE <> 4) then 0
              else 1
            end,
            [ADDRESS].DONOTMAILREASONCODEID = case 
              when [ADDRESS].DONOTMAIL = 1 then [ADDRESS].DONOTMAILREASONCODEID
              when @NCOAFOOTNOTECODE in (0, 1, 5, 7) and (@NCOAMAILGRADECODE is null or @NCOAMAILGRADECODE <> 4) then null
              else @DONOTMAILREASONCODEID 
            end,
            [ADDRESS].HISTORICALENDDATE = case
              when [MATCHING].ISPRIMARY = 1 then null
              else [ADDRESS].HISTORICALENDDATE end,
            [ADDRESS].CHANGEDBYID = @CHANGEAGENTID,
            [ADDRESS].DATECHANGED = @CURRENTDATE
          from
            dbo.[ADDRESS]
          inner join
            @MATCHING as MATCHING on [MATCHING].ID = [ADDRESS].ID;

          update dbo.[ADDRESSVALIDATIONUPDATE]
          set
            NCOALASTSUBMITDATE = @NCOALASTSUBMITDATE,
            NCOARETURNCODE = @NCOARETURNCODE,
            NCOAFOOTNOTECODE = @NCOAFOOTNOTECODE,
            NCOADPVFOOTNOTECODE = @NCOADPVFOOTNOTECODE,
            NCOAMOVEDATE = @NCOAMOVEDATE,
            NCOADMASUPPRESSION = @NCOADMASUPPRESSION,
            NCOAMAILGRADECODE = @NCOAMAILGRADECODE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE,
            INFOSOURCECODEID = coalesce(ADDRESSVALIDATIONUPDATE.INFOSOURCECODEID, @INFOSOURCECODEID)
          where
            exists (
              select ID
              from @MATCHING 
              where ID = ADDRESSVALIDATIONUPDATE.ID
            );

          insert into dbo.[ADDRESSVALIDATIONUPDATE]
            (ID, NCOALASTSUBMITDATE, NCOARETURNCODE, NCOAFOOTNOTECODE, NCOADPVFOOTNOTECODE, NCOAMOVEDATE, NCOADMASUPPRESSION, NCOAMAILGRADECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, INFOSOURCECODEID)
          select
            ID, @NCOALASTSUBMITDATE, @NCOARETURNCODE, @NCOAFOOTNOTECODE, @NCOADPVFOOTNOTECODE, @NCOAMOVEDATE, @NCOADMASUPPRESSION, @NCOAMAILGRADECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @INFOSOURCECODEID
          from dbo.[ADDRESS]
          where
            exists (
              select ID
              from @MATCHING 
              where ID = [ADDRESS].ID
              and ID not in (select ID from dbo.[ADDRESSVALIDATIONUPDATE])
            );
        end

        update dbo.[ADDRESS]
        set
          ADDRESSBLOCK = @STDADDRESSBLOCK,
          CITY = @STDCITY,
          STATEID = @STDSTATEID,
          POSTCODE = @STDPOSTCODE,
          CART = @STDCART,
          DPC = @STDDPC,
          LOT = @STDLOT,
          DONOTMAIL = case 
            when DONOTMAIL = 1 then 1
            when @NCOAFOOTNOTECODE in (0, 1, 5, 7) and (@NCOAMAILGRADECODE is null or @NCOAMAILGRADECODE <> 4) then 0
            else 1
          end,
          DONOTMAILREASONCODEID = case 
            when DONOTMAIL = 1 then DONOTMAILREASONCODEID
            when @NCOAFOOTNOTECODE in (0, 1, 5, 7) and (@NCOAMAILGRADECODE is null or @NCOAMAILGRADECODE <> 4) then null
            else @DONOTMAILREASONCODEID 
          end,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where
          ID = @ADDRESSID;

        if exists (select ID from dbo.[ADDRESSVALIDATIONUPDATE] where ID = @ADDRESSID)
          update dbo.[ADDRESSVALIDATIONUPDATE]
          set
            NCOALASTSUBMITDATE = @NCOALASTSUBMITDATE,
            NCOARETURNCODE = @NCOARETURNCODE,
            NCOAFOOTNOTECODE = @NCOAFOOTNOTECODE,
            NCOADPVFOOTNOTECODE = @NCOADPVFOOTNOTECODE,
            NCOAMOVEDATE = @NCOAMOVEDATE,
            NCOADMASUPPRESSION = @NCOADMASUPPRESSION,
            NCOAMAILGRADECODE = @NCOAMAILGRADECODE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE,
            INFOSOURCECODEID = coalesce(ADDRESSVALIDATIONUPDATE.INFOSOURCECODEID, @INFOSOURCECODEID)
          where
            ID = @ADDRESSID;
        else
          insert into dbo.[ADDRESSVALIDATIONUPDATE]
          (ID, NCOALASTSUBMITDATE, NCOARETURNCODE, NCOAFOOTNOTECODE, NCOADPVFOOTNOTECODE, NCOAMOVEDATE, NCOADMASUPPRESSION, NCOAMAILGRADECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, INFOSOURCECODEID)
          values
          (@ADDRESSID, @NCOALASTSUBMITDATE, @NCOARETURNCODE, @NCOAFOOTNOTECODE, @NCOADPVFOOTNOTECODE, @NCOAMOVEDATE, @NCOADMASUPPRESSION, @NCOAMAILGRADECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @INFOSOURCECODEID);

        -- If updating a household address and the move type is "Individual", update the primary member as well

        if @UPDATEPRIMARYMEMBER = 1
        begin
          update dbo.[ADDRESS]
          set
            ADDRESSBLOCK = @STDADDRESSBLOCK,
            CITY = @STDCITY,
            STATEID = @STDSTATEID,
            POSTCODE = @STDPOSTCODE,
            CART = @STDCART,
            DPC = @STDDPC,
            LOT = @STDLOT,
            DONOTMAIL = case 
              when DONOTMAIL = 1 then 1
              when @NCOAFOOTNOTECODE in (0, 1, 5, 7) and (@NCOAMAILGRADECODE is null or @NCOAMAILGRADECODE <> 4) then 0
              else 1
            end,
            DONOTMAILREASONCODEID = case 
              when DONOTMAIL = 1 then DONOTMAILREASONCODEID
              when @NCOAFOOTNOTECODE in (0, 1, 5, 7) and (@NCOAMAILGRADECODE is null or @NCOAMAILGRADECODE <> 4) then null
              else @DONOTMAILREASONCODEID 
            end,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
          where
            ID = @PRIMARYMEMBERADDRESSID;

          if exists (select ID from dbo.[ADDRESSVALIDATIONUPDATE] where ID = @PRIMARYMEMBERADDRESSID)
            update dbo.[ADDRESSVALIDATIONUPDATE]
            set
              NCOALASTSUBMITDATE = @NCOALASTSUBMITDATE,
              NCOARETURNCODE = @NCOARETURNCODE,
              NCOAFOOTNOTECODE = @NCOAFOOTNOTECODE,
              NCOADPVFOOTNOTECODE = @NCOADPVFOOTNOTECODE,
              NCOAMOVEDATE = @NCOAMOVEDATE,
              NCOADMASUPPRESSION = @NCOADMASUPPRESSION,
              NCOAMAILGRADECODE = @NCOAMAILGRADECODE,
              CHANGEDBYID = @CHANGEAGENTID,
              DATECHANGED = @CURRENTDATE,
              INFOSOURCECODEID = coalesce(ADDRESSVALIDATIONUPDATE.INFOSOURCECODEID, @INFOSOURCECODEID)
            where
              ID = @PRIMARYMEMBERADDRESSID;
          else
            insert into dbo.[ADDRESSVALIDATIONUPDATE]
            (ID, NCOALASTSUBMITDATE, NCOARETURNCODE, NCOAFOOTNOTECODE, NCOADPVFOOTNOTECODE, NCOAMOVEDATE, NCOADMASUPPRESSION, NCOAMAILGRADECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, INFOSOURCECODEID)
            values
            (@PRIMARYMEMBERADDRESSID, @NCOALASTSUBMITDATE, @NCOARETURNCODE, @NCOAFOOTNOTECODE, @NCOADPVFOOTNOTECODE, @NCOAMOVEDATE, @NCOADMASUPPRESSION, @NCOAMAILGRADECODE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @INFOSOURCECODEID);
        end
      end
    end
  end

  return 0;