USP_ADDRESS_UPDATEMATCHINGADDRESSES

            CREATE procedure dbo.USP_ADDRESS_UPDATEMATCHINGADDRESSES
            (
                @ADDRESSID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @ADDRESSTYPECODEID uniqueidentifier,
                @PRIMARY bit = null,
                @DONOTMAIL bit,
                @STARTDATE dbo.UDT_MONTHDAY = null,
                @ENDDATE dbo.UDT_MONTHDAY = null,
                @COUNTRYID uniqueidentifier,
                @STATEID uniqueidentifier,
                @ADDRESSBLOCK nvarchar(150),
                @CITY nvarchar(50),
                @POSTCODE nvarchar(12),
                @CART nvarchar(10),
                @DPC nvarchar(8),
                @LOT nvarchar(5),
                @OMITFROMVALIDATION bit,                    
                @COUNTYCODEID uniqueidentifier,
                @CONGRESSIONALDISTRICTCODEID uniqueidentifier,
                @STATEHOUSEDISTRICTCODEID uniqueidentifier = null,
                @STATESENATEDISTRICTCODEID uniqueidentifier = null,
                @LOCALPRECINCTCODEID uniqueidentifier = null,
                @INFOSOURCECODEID uniqueidentifier = null,
                @REGIONCODEID uniqueidentifier = null,
                @LASTVALIDATIONATTEMPTDATE datetime,
                @VALIDATIONMESSAGE nvarchar(200),
                @CERTIFICATIONDATA integer,
                @DONOTMAILREASONCODEID uniqueidentifier,
                -- Revenue batch's constituent edit form doesn't edit certain fields 

                -- omit those fields from being updated when @UPDATEFROMREVENUEBATCH = 1

                @UPDATEFROMREVENUEBATCH bit = 0,
                @HISTORICALSTARTDATE date = null,
                @HISTORICALENDDATE date = null,
                @INFOSOURCECOMMENTS nvarchar(256) = '',
                @ISCONFIDENTIAL bit = 0
            )
            as
                set nocount on

                declare @CURRENTDATE datetime;

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

                set @CURRENTDATE = getdate();

                declare @CONSTITUENTID uniqueidentifier;
                declare @OLDCOUNTRYID uniqueidentifier;
                declare @OLDSTATEID uniqueidentifier;
                declare @OLDADDRESSBLOCK nvarchar(150);
                declare @OLDCITY nvarchar(50);
                declare @OLDPOSTCODE nvarchar(12);
                declare @OLDADDRESSTYPECODEID uniqueidentifier;
                declare @OLDHISTORICALSTARTDATE datetime;
                declare @OLDHISTORICALENDDATE datetime;
                declare @ISGROUP bit;

                select
                    @CONSTITUENTID = ADDRESS.[CONSTITUENTID],
                    @OLDCOUNTRYID = ADDRESS.[COUNTRYID],
                    @OLDADDRESSBLOCK  = ADDRESS.[ADDRESSBLOCK],
                    @OLDCITY  = ADDRESS.[CITY],
                    @OLDSTATEID  = ADDRESS.[STATEID],
                    @OLDPOSTCODE  = ADDRESS.[POSTCODE],
                    @OLDADDRESSTYPECODEID = ADDRESS.[ADDRESSTYPECODEID],
                    @OLDHISTORICALSTARTDATE = ADDRESS.[HISTORICALSTARTDATE],
                    @OLDHISTORICALENDDATE = ADDRESS.[HISTORICALENDDATE],
                    @ISGROUP = CONSTITUENT.ISGROUP
                from
                    dbo.ADDRESS
                inner join dbo.CONSTITUENT
                    on ADDRESS.CONSTITUENTID = CONSTITUENT.ID
                where
                    ADDRESS.[ID] = @ADDRESSID;

                declare @MATCHINGCONSTITUENTS table(ID uniqueidentifier);
                insert into @MATCHINGCONSTITUENTS select CONSTITUENTID from dbo.UFN_ADDRESS_MATCHINGHOUSEHOLDRECORDS(@CONSTITUENTID, @OLDCOUNTRYID, @OLDSTATEID, @OLDADDRESSBLOCK, @OLDCITY, @OLDPOSTCODE, @OLDADDRESSTYPECODEID);

                -- create table of matching addresses

                    declare @MATCHING table(ID uniqueidentifier);
                    insert into @MATCHING
                        select
                            ID 
                        from
                            dbo.ADDRESS 
                        where
                            COUNTRYID = @OLDCOUNTRYID 
                            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 ( (HISTORICALSTARTDATE = @OLDHISTORICALSTARTDATE) or (HISTORICALSTARTDATE is null and @OLDHISTORICALSTARTDATE is null) ) 
                            and ( (HISTORICALENDDATE = @OLDHISTORICALENDDATE) or (HISTORICALENDDATE is null and @OLDHISTORICALENDDATE is null) );

                        /*
                          WI 254227 MMR
                          We are changing the above comparison to include HISTORICAL dates as part of the comparison if two addresses match 
                        */


                -- DanielCo[8/21/09:46721] - Do not change matching constituents' primary address

                --if @PRIMARY = 1 

                --    update dbo.ADDRESS

                --    set

                --        ISPRIMARY = 0,

                --        CHANGEDBYID = @CHANGEAGENTID,

                --        DATECHANGED = @CURRENTDATE

                --    where

                --        CONSTITUENTID in (select ID from @MATCHINGCONSTITUENTS);


                -- update the existing records

                begin try
                    update dbo.ADDRESS
                    set
                        ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
                        DONOTMAIL = @DONOTMAIL,
                        DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
                        STARTDATE = case when @UPDATEFROMREVENUEBATCH = 1 then STARTDATE else @STARTDATE end,
                        ENDDATE = case when @UPDATEFROMREVENUEBATCH = 1 then ENDDATE else @ENDDATE end,
                        COUNTRYID = @COUNTRYID,
                        STATEID = @STATEID,
                        ADDRESSBLOCK = @ADDRESSBLOCK,
                        CITY = @CITY,
                        POSTCODE = @POSTCODE,
                        CART = @CART,
                        DPC = @DPC,
                        LOT = @LOT,
                        --ISPRIMARY = case when @UPDATEFROMREVENUEBATCH = 1 then ISPRIMARY else @PRIMARY end,

                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE,
                        HISTORICALSTARTDATE = @HISTORICALSTARTDATE,
                        HISTORICALENDDATE = @HISTORICALENDDATE,
                        ISCONFIDENTIAL = @ISCONFIDENTIAL
                    where
                        exists (
                            select ID
                            from @MATCHING
                            where ID = ADDRESS.ID
                        );
                end try
                begin catch
                    if PATINDEX('%CK_ADDRESS_FORMERADDRESSCANNOTBEPRIMARY%', ERROR_MESSAGE()) > 0
                        if @ISGROUP = 1
                          raiserror('ERR_ADDRESS_GROUP_ENDDATEINVALIDIFPRIMARYFORANOTHERMEMBER', 13, 1);
                        else
                          raiserror('ERR_ADDRESS_ENDDATEINVALIDIFPRIMARYFORANOTHERMEMBER', 13, 1);
                    else
                        exec dbo.USP_RAISE_ERROR;
                    return 1;
                end catch

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

                update dbo.ADDRESSVALIDATIONUPDATE
                set
                    OMITFROMVALIDATION = @OMITFROMVALIDATION,
                    COUNTYCODEID = @COUNTYCODEID,
                    CONGRESSIONALDISTRICTCODEID = @CONGRESSIONALDISTRICTCODEID,
                    STATEHOUSEDISTRICTCODEID = case when @UPDATEFROMREVENUEBATCH = 1 then STATEHOUSEDISTRICTCODEID else @STATEHOUSEDISTRICTCODEID end,
                    STATESENATEDISTRICTCODEID = case when @UPDATEFROMREVENUEBATCH = 1 then STATESENATEDISTRICTCODEID else @STATESENATEDISTRICTCODEID end,
                    LOCALPRECINCTCODEID = case when @UPDATEFROMREVENUEBATCH = 1 then LOCALPRECINCTCODEID else @LOCALPRECINCTCODEID end,
                    INFOSOURCECODEID = case when @UPDATEFROMREVENUEBATCH = 1 then INFOSOURCECODEID else @INFOSOURCECODEID end,
                    INFOSOURCECOMMENTS = case when @UPDATEFROMREVENUEBATCH = 1 then INFOSOURCECOMMENTS else @INFOSOURCECOMMENTS end,
                    REGIONCODEID = case when @UPDATEFROMREVENUEBATCH = 1 then REGIONCODEID else @REGIONCODEID end,
                    LASTVALIDATIONATTEMPTDATE = @LASTVALIDATIONATTEMPTDATE,
                    VALIDATIONMESSAGE = @VALIDATIONMESSAGE,
                    CERTIFICATIONDATA = @CERTIFICATIONDATA,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where
                    exists (
                        select ID
                        from @MATCHING
                        where ID = ADDRESSVALIDATIONUPDATE.ID
                    );

                insert into dbo.ADDRESSVALIDATIONUPDATE
                    (ID, OMITFROMVALIDATION, COUNTYCODEID, CONGRESSIONALDISTRICTCODEID, STATEHOUSEDISTRICTCODEID, STATESENATEDISTRICTCODEID, LOCALPRECINCTCODEID, INFOSOURCECODEID, INFOSOURCECOMMENTS, REGIONCODEID, LASTVALIDATIONATTEMPTDATE, VALIDATIONMESSAGE, CERTIFICATIONDATA, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                    ID, @OMITFROMVALIDATION, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID, @STATEHOUSEDISTRICTCODEID, @STATESENATEDISTRICTCODEID, @LOCALPRECINCTCODEID, @INFOSOURCECODEID, @INFOSOURCECOMMENTS, @REGIONCODEID, @LASTVALIDATIONATTEMPTDATE, @VALIDATIONMESSAGE, @CERTIFICATIONDATA, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from dbo.ADDRESS
                where
                    exists (
                        select ID
                        from @MATCHING
                        where ID = ADDRESS.ID
                        and ID not in (select ID from dbo.ADDRESSVALIDATIONUPDATE)
                    );