USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTUPDATEBATCHDUPLICATEMATCH_7

    create procedure dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTUPDATEBATCHDUPLICATEMATCH_7 (
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @LOOKUPID nvarchar(50),
    @FIRSTNAME nvarchar(50),
    @MIDDLENAME nvarchar(50),
    @LASTNAME nvarchar(100),
    @SUFFIXCODEID uniqueidentifier,
    @TITLECODEID uniqueidentifier,
    @MAIDENNAME nvarchar(100),
    @NICKNAME nvarchar(50),
    @GENDERCODE tinyint,
    @BIRTHDATE dbo.UDT_FUZZYDATE,
    @ADDRESSID uniqueidentifier, -- matched addressID
    @ADDRESSTYPECODEID uniqueidentifier,
    @ADDRESS_ADDRESSBLOCK nvarchar(150),
    @ADDRESS_CITY nvarchar(50),
    @ADDRESS_STATEID uniqueidentifier,
    @ADDRESS_POSTCODE nvarchar(12),
    @ADDRESS_COUNTRYID uniqueidentifier,
    @PHONEID uniqueidentifier,
    @PHONETYPECODEID uniqueidentifier,
    @PHONENUMBER nvarchar(100),
    @EMAILID uniqueidentifier,
    @EMAILADDRESSTYPECODEID uniqueidentifier,
    @EMAILADDRESS dbo.UDT_EMAILADDRESS,
    @CREATEDON datetime,
    @DATECHANGED datetime,
    @ADDRESSES xml,
    @PHONES xml,
    @EMAILADDRESSES xml,
    @CONSTITUENTACTION tinyint,
    @ADDRESSACTION tinyint,
    @EMAILACTION tinyint,
    @PHONEACTION tinyint,
    @PRIMARYRECORDID uniqueidentifier, -- matched constituentID
    @EMAILISPRIMARY bit,
    @PHONEISPRIMARY bit,
    @ADDRESSISPRIMARY bit,
    @INCOMINGADDRESSID uniqueidentifier, -- the ID of the incoming address displayed on the screen
    @INCOMINGEMAILID uniqueidentifier, -- the ID of the incoming email displayed on the screen
    @INCOMINGPHONEID uniqueidentifier, -- the ID of the incoming phone displayed on the screen
    @SIMILARADDRESSCODE tinyint,
    @UNSIMILARADDRESSCODE tinyint,
    @NEWADDRESSPRIMARYCODE tinyint,
    @DIFFERENTPHONECODE tinyint,
    @NEWPHONEPRIMARYCODE tinyint,
    @DIFFERENTEMAILCODE tinyint,
    @NEWEMAILPRIMARYCODE tinyint,
    @DECEASED bit,
    @DECEASEDDATE dbo.UDT_FUZZYDATE,
    @GIVESANONYMOUSLY bit,
    @MARITALSTATUSCODEID uniqueidentifier,
    @WEBADDRESS dbo.UDT_WEBADDRESS,
    @ADDRESSHISTORICALSTARTDATE date,
    @ADDRESSHISTORICALENDDATE date,
    @ADDRESSDONOTMAIL bit,
    @ADDRESSDONOTMAILREASONCODEID uniqueidentifier,
    @ADDRESSSTARTDATE dbo.UDT_MONTHDAY,
    @ADDRESSENDDATE dbo.UDT_MONTHDAY,
    @ADDRESSDPC nvarchar(max),
    @ADDRESSCART nvarchar(max),
    @ADDRESSLOT nvarchar(5),
    @ADDRESSINFOSOURCECODEID uniqueidentifier,
    @ADDRESSINFOSOURCECOMMENTS nvarchar(256),
    @ADDRESSCOUNTYCODEID uniqueidentifier,
    @ADDRESSREGIONCODEID uniqueidentifier,
    @ADDRESSCONGRESSIONALDISTRICTCODEID uniqueidentifier,
    @ADDRESSSTATEHOUSEDISTRICTCODEID uniqueidentifier,
    @ADDRESSSTATESENATEDISTRICTCODEID uniqueidentifier,
    @ADDRESSLOCALPRECINCTCODEID uniqueidentifier,
    @ADDRESSCERTIFICATIONDATA int,
    @ADDRESSLASTVALIDATIONATTEMPTDATE date,
    @ADDRESSOMITFROMVALIDATION bit,
    @ADDRESSVALIDATIONMESSAGE nvarchar(200),
    @PHONEDONOTCALL bit,
    @PHONESTARTTIME dbo.UDT_HOURMINUTE,
    @PHONEENDTIME dbo.UDT_HOURMINUTE,
    @PHONEINFOSOURCECODEID uniqueidentifier,
    @PHONECOUNTRYID uniqueidentifier,
    @PHONESTARTDATE date,
    @PHONEENDDATE date,
    @PHONESEASONALSTARTDATE dbo.UDT_MONTHDAY,
    @PHONESEASONALENDDATE dbo.UDT_MONTHDAY,
    @EMAILADDRESSDONOTEMAIL bit,
    @EMAILADDRESSINFOSOURCECODEID uniqueidentifier,
    @EMAILADDRESSSTARTDATE date,
    @EMAILADDRESSENDDATE date,
    @CONSTITUENCIES xml,
    @ORIGINAL_KEYNAME nvarchar(100),
    @ORIGINAL_FIRSTNAME nvarchar(50),
    @SOLICITCODES xml,
    @GENDERCODEID uniqueidentifier
    )
as
set nocount on;

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

declare @CURRENTDATE datetime

set @CURRENTDATE = getdate()

declare @ISERB bit = 0;

if exists (
        select 'x'
        from dbo.BATCHREVENUE
        where ID = @ID
        )
    set @ISERB = 1;

if @CONSTITUENTACTION is null
    set @CONSTITUENTACTION = 0;

if @ADDRESSACTION is null
    set @ADDRESSACTION = 0;

if @EMAILACTION is null
    set @EMAILACTION = 0;

if @PHONEACTION is null
    set @PHONEACTION = 0;

begin try
    ------------CONSTITUENT HANDLING------------------------
    if @CONSTITUENTACTION = 1 --Add constituent as alias (same SP can be used for both individuals and organizations)
    begin
        declare @IGNOREDUPLICATE bit = 1;--if we are here we want to ignore the duplicate error message and continue on.                

        exec dbo.USP_ADD_INDIVIDUAL_ALIAS @CHANGEAGENTID = @CHANGEAGENTID,
            @CONSTITUENTID = @PRIMARYRECORDID,
            @KEYNAME = @LASTNAME,
            @FIRSTNAME = @FIRSTNAME,
            @MIDDLENAME = @MIDDLENAME,
            @TITLECODEID = @TITLECODEID,
            @SUFFIXCODEID = @SUFFIXCODEID,
            @IGNOREDUPLICATE = @IGNOREDUPLICATE;

        -- reload the fields with the correct data for the batch row
        select @LASTNAME = KEYNAME,
            @FIRSTNAME = FIRSTNAME,
            @MIDDLENAME = MIDDLENAME,
            @TITLECODEID = TITLECODEID,
            @SUFFIXCODEID = SUFFIXCODEID,
            @BIRTHDATE = BIRTHDATE,
            @LOOKUPID = LOOKUPID
        from dbo.CONSTITUENT
        where ID = @PRIMARYRECORDID;
    end

    -- always update the constituent data.
    update CUB
    set CUB.FIRSTNAME = coalesce(@FIRSTNAME, ''),
        CUB.MIDDLENAME = coalesce(@MIDDLENAME, ''),
        CUB.KEYNAME = @LASTNAME,
        CUB.ORIGINAL_FIRSTNAME = coalesce(@ORIGINAL_FIRSTNAME, ''),
        CUB.ORIGINAL_KEYNAME = coalesce(@ORIGINAL_KEYNAME, ''),
        CUB.SUFFIXCODEID = @SUFFIXCODEID,
        CUB.TITLECODEID = @TITLECODEID,
        CUB.BIRTHDATE = coalesce(@BIRTHDATE, '00000000'),
        CUB.CHANGEDBYID = @CHANGEAGENTID,
        CUB.DATECHANGED = @CURRENTDATE,
        CUB.PRIMARYRECORDID = @PRIMARYRECORDID,
        CUB.DOMANUALREVIEWFORAUTOMATCH = 0,
        CUB.LOOKUP_ID = coalesce(@LOOKUPID, ''),
        CUB.MARITALSTATUSCODEID = @MARITALSTATUSCODEID,
        CUB.GIVESANONYMOUSLY = @GIVESANONYMOUSLY,
        CUB.MAIDENNAME = coalesce(@MAIDENNAME, ''),
        CUB.NICKNAME = coalesce(@NICKNAME, ''),
        CUB.GENDERCODE = @GENDERCODE,
        CUB.DECEASED = @DECEASED,
        CUB.DECEASEDDATE = @DECEASEDDATE,
        CUB.WEBADDRESS = @WEBADDRESS,
        CUB.GENDERCODEID = @GENDERCODEID
    from dbo.BATCHCONSTITUENTUPDATE CUB
    where CUB.ID = @ID

    --------------ADDRESSES----------------------------------
    if @ADDRESSACTION = 1 -- Add address
    begin
        update CUBA
        set CUBA.ISPRIMARY = @ADDRESSISPRIMARY,
            CUBA.CHANGEDBYID = @CHANGEAGENTID,
            CUBA.DATECHANGED = @CURRENTDATE
        from dbo.BATCHCONSTITUENTUPDATEADDRESSES CUBA
        where CUBA.ID = @INCOMINGADDRESSID
    end
    else
        if @ADDRESSACTION = 2 -- Update current action 
        begin
            declare @PRIMARY bit,
                @UPDATECONTACTS bit,
                @UPDATEMATCHINGHOUSEHOLDADDRESSES bit;

            exec dbo.USP_ADDRESS_EDITLOAD @ADDRESSID,
                @PRIMARY = @PRIMARY output,
                @UPDATECONTACTS = @UPDATECONTACTS output,
                @UPDATEMATCHINGHOUSEHOLDADDRESSES = @UPDATEMATCHINGHOUSEHOLDADDRESSES output;

            update CUBA
            set CUBA.ADDRESSID = @ADDRESSID,
                CUBA.ADDRESSTYPECODEID = coalesce(nullif(CUBA.ADDRESSTYPECODEID, '00000000-0000-0000-0000-000000000000'), @ADDRESSTYPECODEID),
                CUBA.ADDRESSBLOCK = @ADDRESS_ADDRESSBLOCK,
                CUBA.CITY = @ADDRESS_CITY,
                CUBA.STATEID = @ADDRESS_STATEID,
                CUBA.POSTCODE = @ADDRESS_POSTCODE,
                CUBA.COUNTRYID = @ADDRESS_COUNTRYID,
                CUBA.ENDDATE = coalesce(nullif(CUBA.ENDDATE, '0000'), @ADDRESSENDDATE),
                CUBA.STARTDATE = coalesce(nullif(CUBA.STARTDATE, '0000'), @ADDRESSSTARTDATE),
                CUBA.HISTORICALSTARTDATE = coalesce(CUBA.HISTORICALSTARTDATE, @ADDRESSHISTORICALSTARTDATE),
                CUBA.HISTORICALENDDATE = coalesce(CUBA.HISTORICALENDDATE, @ADDRESSHISTORICALENDDATE),
                CUBA.CART = coalesce(nullif(CUBA.CART, ''), @ADDRESSCART),
                CUBA.DONOTMAIL = coalesce(nullif(CUBA.DONOTMAIL, 0), @ADDRESSDONOTMAIL),
                CUBA.DONOTMAILREASONCODEID = coalesce(CUBA.DONOTMAILREASONCODEID, @ADDRESSDONOTMAILREASONCODEID),
                CUBA.DPC = coalesce(nullif(CUBA.DPC, ''), @ADDRESSDPC),
                CUBA.ISPRIMARY = coalesce(nullif(CUBA.ISPRIMARY, 0), @PRIMARY),
                CUBA.LOT = coalesce(nullif(CUBA.LOT, ''), @ADDRESSLOT),
                CUBA.OMITFROMVALIDATION = coalesce(nullif(CUBA.OMITFROMVALIDATION, 0), coalesce(@ADDRESSOMITFROMVALIDATION, 0)),
                CUBA.COUNTYCODEID = coalesce(CUBA.COUNTYCODEID, @ADDRESSCOUNTYCODEID),
                CUBA.CONGRESSIONALDISTRICTCODEID = coalesce(CUBA.CONGRESSIONALDISTRICTCODEID, @ADDRESSCONGRESSIONALDISTRICTCODEID),
                CUBA.STATEHOUSEDISTRICTCODEID = coalesce(CUBA.STATEHOUSEDISTRICTCODEID, @ADDRESSSTATEHOUSEDISTRICTCODEID),
                CUBA.STATESENATEDISTRICTCODEID = coalesce(CUBA.STATESENATEDISTRICTCODEID, @ADDRESSSTATESENATEDISTRICTCODEID),
                CUBA.LOCALPRECINCTCODEID = coalesce(CUBA.LOCALPRECINCTCODEID, @ADDRESSLOCALPRECINCTCODEID),
                CUBA.INFOSOURCECODEID = coalesce(CUBA.INFOSOURCECODEID, @ADDRESSINFOSOURCECODEID),
                CUBA.REGIONCODEID = coalesce(CUBA.REGIONCODEID, @ADDRESSREGIONCODEID),
                CUBA.LASTVALIDATIONATTEMPTDATE = coalesce(CUBA.LASTVALIDATIONATTEMPTDATE, @ADDRESSLASTVALIDATIONATTEMPTDATE),
                CUBA.VALIDATIONMESSAGE = coalesce(nullif(CUBA.VALIDATIONMESSAGE, ''), coalesce(@ADDRESSVALIDATIONMESSAGE, '')),
                CUBA.CERTIFICATIONDATA = coalesce(nullif(CUBA.CERTIFICATIONDATA, 0), coalesce(@ADDRESSCERTIFICATIONDATA, 0)),
                CUBA.UPDATEHOUSEHOLD = coalesce(nullif(CUBA.UPDATEHOUSEHOLD, 0), coalesce(@UPDATEMATCHINGHOUSEHOLDADDRESSES, 0)),
                CUBA.INFOSOURCECOMMENTS = coalesce(nullif(CUBA.INFOSOURCECOMMENTS, ''), coalesce(@ADDRESSINFOSOURCECOMMENTS, '')),
                CUBA.CHANGEDBYID = @CHANGEAGENTID,
                CUBA.DATECHANGED = @CURRENTDATE
            from dbo.BATCHCONSTITUENTUPDATEADDRESSES CUBA
            where CUBA.ID = @INCOMINGADDRESSID
        end
        else -- ignore
        begin
            delete
            from dbo.BATCHCONSTITUENTUPDATEADDRESSES
            where ID = @INCOMINGADDRESSID
        end

    -- process all the other addresses here only if this a CUB (if it's a generate exception ERB originating scenario don't process)
    if @ISERB = 0
    begin
        -- if the incoming address was added as primary don't add another one as primary
        if @ADDRESSISPRIMARY = 1
            set @NEWADDRESSPRIMARYCODE = 0;

        -- if the rule for adding an address is manual review then add that address as new  
        if @SIMILARADDRESSCODE = 2
            set @SIMILARADDRESSCODE = 3;

        if @UNSIMILARADDRESSCODE = 2
            set @UNSIMILARADDRESSCODE = 3;

        -- apply the address rules on all the incoming addresses except for the one displayed in the resolution screen    
        exec dbo.USP_CONSTITUENTUPDATEBATCH_APPLYADDRESSRULES @ID,
            @PRIMARYRECORDID,
            @CHANGEAGENTID,
            1,
            @SIMILARADDRESSCODE,
            @UNSIMILARADDRESSCODE,
            @NEWADDRESSPRIMARYCODE,
            @INCOMINGADDRESSID
    end

    ----------------EMAILS----------------------------
    if @EMAILACTION = 1 -- add as secondary email
    begin
        update CUBE
        set CUBE.ISPRIMARY = @EMAILISPRIMARY,
            CUBE.CHANGEDBYID = @CHANGEAGENTID,
            CUBE.DATECHANGED = @CURRENTDATE
        from dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES CUBE
        where CUBE.ID = @INCOMINGEMAILID
    end
    else
        if @EMAILACTION = 2 -- update current email
        begin
            declare @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS bit,
                @EMAILINFOSOURCECOMMENTS nvarchar(256);

            -- invoke email load                             
            exec dbo.USP_EMAILADDRESS_EDITLOAD @EMAILID,
                @PRIMARY = @EMAILISPRIMARY output,
                @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS = @UPDATEMATCHINGHOUSEHOLDEMAILADDRESS output,
                @INFOSOURCECOMMENTS = @EMAILINFOSOURCECOMMENTS output;

            update CUBE
            set CUBE.EMAILADDRESS = @EMAILADDRESS,
                CUBE.EMAILADDRESSID = @EMAILID,
                CUBE.EMAILADDRESSTYPECODEID = coalesce(CUBE.EMAILADDRESSTYPECODEID, @EMAILADDRESSTYPECODEID),
                CUBE.ISPRIMARY = coalesce(nullif(CUBE.ISPRIMARY, 0), @EMAILISPRIMARY),
                CUBE.DONOTEMAIL = coalesce(nullif(CUBE.DONOTEMAIL, 0), @EMAILADDRESSDONOTEMAIL),
                CUBE.STARTDATE = coalesce(cube.STARTDATE, @EMAILADDRESSSTARTDATE),
                CUBE.ENDDATE = coalesce(cube.ENDDATE, @EMAILADDRESSENDDATE),
                CUBE.INFOSOURCECODEID = coalesce(cube.INFOSOURCECODEID, @EMAILADDRESSINFOSOURCECODEID),
                CUBE.UPDATEHOUSEHOLD = coalesce(nullif(CUBE.UPDATEHOUSEHOLD, 0), coalesce(@UPDATEMATCHINGHOUSEHOLDEMAILADDRESS, 0)),
                CUBE.CHANGEDBYID = @CHANGEAGENTID,
                CUBE.DATECHANGED = @CURRENTDATE
            from dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES CUBE
            where CUBE.ID = @INCOMINGEMAILID
        end
        else -- ignore means to delete from batch
        begin
            delete
            from dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES
            where ID = @INCOMINGEMAILID
        end

    -- process all the other emails here only if this a CUB (if it's a generate exception ERB originating scenario don't process)
    if @ISERB = 0
    begin
        -- if the incoming email was added as primary don't add another one as primary
        if @EMAILISPRIMARY = 1
            set @NEWEMAILPRIMARYCODE = 0;

        -- if the rule for adding an email is manual review then add that address as new  
        if @DIFFERENTEMAILCODE = 2
            set @DIFFERENTEMAILCODE = 3;

        -- apply the address rules on all the incoming email except for the one displayed in the resolution screen    
        exec dbo.USP_CONSTITUENTUPDATEBATCH_APPLYEMAILRULES @ID,
            @PRIMARYRECORDID,
            @CHANGEAGENTID,
            1,
            @DIFFERENTEMAILCODE,
            @NEWEMAILPRIMARYCODE,
            @INCOMINGEMAILID
    end

    ----------------PHONES----------------------------
    if @PHONEACTION = 1 -- add as secondary phone
    begin
        update CUBP
        set CUBP.ISPRIMARY = @PHONEISPRIMARY,
            CUBP.CHANGEDBYID = @CHANGEAGENTID,
            CUBP.DATECHANGED = @CURRENTDATE
        from dbo.BATCHCONSTITUENTUPDATEPHONES CUBP
        where CUBP.ID = @INCOMINGPHONEID
    end
    else
        if @PHONEACTION = 2 -- update current phone   
        begin
            declare @UPDATEMATCHINGHOUSEHOLDPHONE bit,
                @PHONEINFOSOURCECOMMENTS nvarchar(256),
                @PHONEDONOTCALLREASONCODEID uniqueidentifier;

            exec dbo.USP_PHONE_EDITLOAD @PHONEID,
                @PRIMARY = @PHONEISPRIMARY output,
                @UPDATEMATCHINGHOUSEHOLDPHONE = @UPDATEMATCHINGHOUSEHOLDPHONE output,
                @INFOSOURCECOMMENTS = @PHONEINFOSOURCECOMMENTS output,
                @DONOTCALLREASONCODEID = @PHONEDONOTCALLREASONCODEID output;

            update CUBP
            set CUBP.NUMBER = @PHONENUMBER,
                CUBP.PHONEID = @PHONEID,
                CUBP.ISPRIMARY = coalesce(nullif(CUBP.ISPRIMARY, 0), @PHONEISPRIMARY),
                CUBP.PHONETYPECODEID = coalesce(CUBP.PHONETYPECODEID, @PHONETYPECODEID),
                CUBP.DONOTCALL = coalesce(nullif(CUBP.DONOTCALL, 0), @PHONEDONOTCALL),
                CUBP.UPDATEHOUSEHOLD = coalesce(nullif(CUBP.UPDATEHOUSEHOLD, 0), coalesce(@UPDATEMATCHINGHOUSEHOLDPHONE, 0)),
                CUBP.COUNTRYID = coalesce(CUBP.COUNTRYID, @PHONECOUNTRYID),
                CUBP.SEASONALSTARTDATE = coalesce(nullif(CUBP.SEASONALSTARTDATE, '0000'), @PHONESEASONALSTARTDATE),
                CUBP.SEASONALENDDATE = coalesce(nullif(CUBP.SEASONALENDDATE, '0000'), @PHONESEASONALENDDATE),
                CUBP.STARTTIME = coalesce(nullif(CUBP.STARTTIME, ''), @PHONESTARTTIME),
                CUBP.ENDTIME = coalesce(nullif(CUBP.ENDTIME, ''), @PHONEENDTIME),
                CUBP.STARTDATE = coalesce(CUBP.STARTDATE, @PHONESTARTDATE),
                CUBP.ENDDATE = coalesce(CUBP.ENDDATE, @PHONEENDDATE),
                CUBP.INFOSOURCECODEID = coalesce(CUBP.INFOSOURCECODEID, @PHONEINFOSOURCECODEID),
                CUBP.CHANGEDBYID = @CHANGEAGENTID,
                CUBP.DATECHANGED = @CURRENTDATE
            from dbo.BATCHCONSTITUENTUPDATEPHONES CUBP
            where CUBP.ID = @INCOMINGPHONEID
        end
        else -- ignore
        begin
            delete
            from dbo.BATCHCONSTITUENTUPDATEPHONES
            where ID = @INCOMINGPHONEID
        end

    -- process all the other emails here only if this a CUB (if it's a generate exception ERB originating scenario don't process)
    if @ISERB = 0
    begin
        -- if the incoming phone was added as primary don't add another one as primary
        if @PHONEISPRIMARY = 1
            set @NEWPHONEPRIMARYCODE = 0;

        -- if the rule for adding an phone is manual review then add that address as new  
        if @DIFFERENTPHONECODE = 2
            set @DIFFERENTPHONECODE = 3;

        -- apply the address rules on all the incoming phone except for the one displayed in the resolution screen    
        exec dbo.USP_CONSTITUENTUPDATEBATCH_APPLYPHONERULES @ID,
            @PRIMARYRECORDID,
            @CHANGEAGENTID,
            1,
            @DIFFERENTPHONECODE,
            @NEWPHONEPRIMARYCODE,
            @INCOMINGPHONEID
    end

    ----------------SOLICITCODES---------------------------------
    -- Need to load the new codes from the @SOLICITCODES variable
    -- and also the in system solicit codes from the table 
    -- dbo.CONSTITUENTSOLICITCODE taking account of the dates
    -------------------------------------------------------------

    -- load existing solicit codes from dbo.CONSTITUENTSOLICITCODE
    insert into [dbo].[BATCHCONSTITUENTUPDATESOLICITCODE]
    (
        [ID],
        [BATCHCONSTITUENTUPDATEID],
        [SOLICITCODEID],
        [CONSTITUENTSOLICITCODEID],
        [STARTDATE],
        [ENDDATE],
        [COMMENTS],
        [SEQUENCE],
        [CONSENTPREFERENCECODE],
        [SOURCECODEID],
        [SOURCEFILEPATH],
        [PRIVACYPOLICYFILEPATH],
        [SUPPORTINGINFORMATION],
        [CONSENTSTATEMENT],
        [ADDEDBYID],
        [CHANGEDBYID],
        [DATEADDED],
        [DATECHANGED]
    )
    select
        newid() ID,
        @ID [BATCHCONSTITUENTUPDATEID],
        [SOLICITCODEID],
        [ID] CONSTITUENTSOLICITCODEID,
        [STARTDATE],
        [ENDDATE],
        [COMMENTS],
        [SEQUENCE],
        [CONSENTPREFERENCECODE],
        [SOURCECODEID],
        [SOURCEFILEPATH],
        [PRIVACYPOLICYFILEPATH],
        [SUPPORTINGINFORMATION],
        [CONSENTSTATEMENT],
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from    
        dbo.CONSTITUENTSOLICITCODE
    where
        [CONSTITUENTID] = @PRIMARYRECORDID
        and not ID in (select CONSTITUENTSOLICITCODEID from dbo.BATCHCONSTITUENTUPDATESOLICITCODE where BATCHCONSTITUENTUPDATEID = @ID and CONSTITUENTSOLICITCODEID is not null);

    -- address the auto end date issues for consent based SC
    exec USP_CONSTITUENTUPDATEBATCH_ADJUSTSOLICITCODEDATERANGES @PRIMARYRECORDID, @SOLICITCODES, @CHANGEAGENTID;

    ----------------CONSTITUENCIES----------------------------
    declare @CONSTITUENCIESTABLE table
    (
        CONSTITUENCYCODEID uniqueidentifier,
        ORIGINALCONSTITUENCYID uniqueidentifier,
        DATEFROM date,
        DATETO date
    )
    insert into @CONSTITUENCIESTABLE
    select
        CONSTITUENCIES.ITEM.value('@CONSTITUENCYCODEID[1]', 'uniqueidentifier'),
        CONSTITUENCIES.ITEM.value('@ORIGINALCONSTITUENCYID[1]', 'uniqueidentifier'),
        CONSTITUENCIES.ITEM.value('@DATEFROM[1]', 'date'),
        CONSTITUENCIES.ITEM.value('@DATETO[1]', 'date')
    from @CONSTITUENCIES.nodes('/CONSTITUENCIES/ITEM') as CONSTITUENCIES(ITEM)

    declare @EARLIESTDATE datetime = '1753-01-01'
    declare @LATESTDATE datetime = '9999-12-31'
    -- add user-defined constituencies
    insert into dbo.BATCHCONSTITUENTUPDATECONSTITUENCIES
    (
        BATCHCONSTITUENTUPDATEID,
        CONSTITUENCYCODEID,
        DATEFROM,
        DATETO,
        ORIGINALCONSTITUENCYID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        @ID,
        CONSTITUENCYCODEID,
        DATEFROM,
        DATETO,
        ORIGINALCONSTITUENCYID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from
        @CONSTITUENCIESTABLE CONSTITUENCIES
        left join dbo.CONSTITUENCYSYSTEMNAME
            on CONSTITUENCYSYSTEMNAME.ID = CONSTITUENCIES.CONSTITUENCYCODEID
    where
        CONSTITUENCYSYSTEMNAME.ID is null
    and not exists
    (
        select 1
        from
            dbo.BATCHCONSTITUENTUPDATECONSTITUENCIES
        where
            BATCHCONSTITUENTUPDATECONSTITUENCIES.BATCHCONSTITUENTUPDATEID = @ID
            and BATCHCONSTITUENTUPDATECONSTITUENCIES.CONSTITUENCYCODEID = CONSTITUENCIES.CONSTITUENCYCODEID
            and coalesce(BATCHCONSTITUENTUPDATECONSTITUENCIES.DATEFROM, @EARLIESTDATE) = coalesce(CONSTITUENCIES.DATEFROM, @EARLIESTDATE)
            and coalesce(BATCHCONSTITUENTUPDATECONSTITUENCIES.DATETO, @LATESTDATE) = coalesce(CONSTITUENCIES.DATETO, @LATESTDATE)
    )
    -- add system constituencies
    insert into dbo.BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM
    (
        BATCHCONSTITUENTUPDATEID,
        CONSTITUENCYCODEID,
        DATEFROM,
        DATETO,
        ORIGINALCONSTITUENCYID,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
    )
    select
        @ID,
        CONSTITUENCYCODEID,
        DATEFROM,
        DATETO,
        ORIGINALCONSTITUENCYID,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
    from
        @CONSTITUENCIESTABLE CONSTITUENCIES
        left join dbo.CONSTITUENCYSYSTEMNAME
            on CONSTITUENCYSYSTEMNAME.ID = CONSTITUENCIES.CONSTITUENCYCODEID
    where
        CONSTITUENCYSYSTEMNAME.ID is not null
        and not exists
        (
            select 1
                from
                    dbo.BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM
            where
                BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.BATCHCONSTITUENTUPDATEID = @ID
                and BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.CONSTITUENCYCODEID = CONSTITUENCIES.CONSTITUENCYCODEID
                and coalesce(BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.DATEFROM, @EARLIESTDATE) = coalesce(CONSTITUENCIES.DATEFROM, @EARLIESTDATE)
                and coalesce(BATCHCONSTITUENTUPDATECONSTITUENCIESSYSTEM.DATETO, @LATESTDATE) = coalesce(CONSTITUENCIES.DATETO, @LATESTDATE)
        )

-- Defaulting DATEFROM/DATETO if possible for constituency
    declare @ALLCONSTITUENCY xml = dbo.UFN_CONSTITUENTUPDATEBATCH_GETCONSTITUENCIES_TOITEMLISTXML(@ID);
    if @ALLCONSTITUENCY is not null
    begin
        exec USP_CONSTITUENTUPDATEBATCH_ADJUSTCONSTITUENCYDATERANGE @ID, @PRIMARYRECORDID, @ALLCONSTITUENCY, @CHANGEAGENTID;
    end

    -- reset the generate manual exception flag when the duplicate is resolved
    update dbo.BATCHCONSTITUENTUPDATEADDRESSES
    set ISMANUALEXCEPTION = 0,
        MANUALEXCEPTIONSIMILARADDRESSID = null,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    where BATCHCONSTITUENTUPDATEID = @ID

    update dbo.BATCHCONSTITUENTUPDATEEMAILADDRESSES
    set ISMANUALEXCEPTION = 0,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    where BATCHCONSTITUENTUPDATEID = @ID

    update dbo.BATCHCONSTITUENTUPDATEPHONES
    set ISMANUALEXCEPTION = 0,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    where BATCHCONSTITUENTUPDATEID = @ID

    -- remove the exception row when the duplicate is resolved
    delete
    from dbo.BATCHCONSTITUENTUPDATEBATCHSYSTEMMESSAGES
    where BATCHCONSTITUENTUPDATEID = @ID and MESSAGETYPECODE = 1;
end try

begin catch
    exec dbo.USP_RAISE_ERROR;

    return 1;
end catch

return 0;