USP_ADDRESS_UPDATE_LOAD

Retrieves the information needed to update an address.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@DATALOADED	bit	INOUT
@ADDRESSTYPECODEID	uniqueidentifier	INOUT
@PRIMARY	bit	INOUT
@DONOTMAIL	bit	INOUT
@STARTDATE	UDT_MONTHDAY	INOUT
@ENDDATE	UDT_MONTHDAY	INOUT
@COUNTRYID	uniqueidentifier	INOUT
@STATEID	uniqueidentifier	INOUT
@ADDRESSBLOCK	nvarchar(150)	INOUT
@CITY	nvarchar(50)	INOUT
@POSTCODE	nvarchar(12)	INOUT
@CART	nvarchar(10)	INOUT
@DPC	nvarchar(8)	INOUT
@LOT	nvarchar(5)	INOUT
@SPOUSENAME	nvarchar(154)	INOUT
@SPOUSEHASMATCHINGADDRESSES	bit	INOUT
@UPDATEMATCHINGSPOUSEADDRESSES	bit	INOUT
@OMITFROMVALIDATION	bit	INOUT
@COUNTYCODEID	uniqueidentifier	INOUT
@CONGRESSIONALDISTRICTCODEID	uniqueidentifier	INOUT
@STATEHOUSEDISTRICTCODEID	uniqueidentifier	INOUT
@STATESENATEDISTRICTCODEID	uniqueidentifier	INOUT
@LOCALPRECINCTCODEID	uniqueidentifier	INOUT
@INFOSOURCECODEID	uniqueidentifier	INOUT
@REGIONCODEID	uniqueidentifier	INOUT
@LASTVALIDATIONATTEMPTDATE	datetime	INOUT
@VALIDATIONMESSAGE	nvarchar(200)	INOUT
@CERTIFICATIONDATA	int	INOUT
@NCOALASTSUBMITDATE	datetime	INOUT
@NCOARETURN	nvarchar(150)	INOUT
@NCOAFOOTNOTE	nvarchar(150)	INOUT
@NCOADPVFOOTNOTE	nvarchar(150)	INOUT
@NCOAMOVEDATE	UDT_FUZZYDATE	INOUT
@NCOADMASUPPRESSION	bit	INOUT
@NCOAMAILGRADE	nvarchar(150)	INOUT
@TSLONG	bigint	INOUT
@VALIDATIONCOUNTRIES	xml	INOUT
@ZIPLOOKUPCOUNTRIES	xml	INOUT
@UPDATECONTACTS	bit	INOUT
@HASCONTACTS	bit	INOUT
@DONOTMAILREASONCODEID	uniqueidentifier	INOUT
@HISTORICALSTARTDATE	date	INOUT
@HISTORICALENDDATE	date	INOUT
@DATEADDED	date	INOUT
@INFOSOURCECOMMENTS	nvarchar(256)	INOUT
@ISCONFIDENTIAL	bit	INOUT
@ORIGINCODE	tinyint	INOUT
Definition

Copy


            CREATE procedure dbo.USP_ADDRESS_UPDATE_LOAD (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @ADDRESSTYPECODEID uniqueidentifier = null output,
                @PRIMARY bit = null output,
                @DONOTMAIL bit = null output,
                @STARTDATE dbo.UDT_MONTHDAY = null output,
                @ENDDATE dbo.UDT_MONTHDAY = null output,
                @COUNTRYID uniqueidentifier = null output,
                @STATEID uniqueidentifier = null output,
                @ADDRESSBLOCK nvarchar(150) = null output,
                @CITY nvarchar(50) = null output,
                @POSTCODE nvarchar(12) = null output,
                @CART nvarchar(10) = null output,
                @DPC nvarchar(8) = null output,
                @LOT nvarchar(5) = null output,
                @SPOUSENAME nvarchar(154) = null output,
                @SPOUSEHASMATCHINGADDRESSES bit = null output,
                @UPDATEMATCHINGSPOUSEADDRESSES bit = null output,
                @OMITFROMVALIDATION bit = null output,                    
                @COUNTYCODEID uniqueidentifier = null output,
                @CONGRESSIONALDISTRICTCODEID uniqueidentifier = null output,
                @STATEHOUSEDISTRICTCODEID uniqueidentifier = null output,
                @STATESENATEDISTRICTCODEID uniqueidentifier = null output,
                @LOCALPRECINCTCODEID uniqueidentifier = null output,
                @INFOSOURCECODEID uniqueidentifier = null output,
                @REGIONCODEID uniqueidentifier = null output,                    
                @LASTVALIDATIONATTEMPTDATE datetime = null output,
                @VALIDATIONMESSAGE nvarchar(200) = null output,
                @CERTIFICATIONDATA integer = null output,
                @NCOALASTSUBMITDATE datetime = null output,
                @NCOARETURN nvarchar(150) = null output,
                @NCOAFOOTNOTE nvarchar(150) = null output,
                @NCOADPVFOOTNOTE nvarchar(150) = null output,
                @NCOAMOVEDATE UDT_FUZZYDATE = null output,
                @NCOADMASUPPRESSION bit = null output,
                @NCOAMAILGRADE nvarchar(150) = null output,
                @TSLONG bigint = 0 output,
                @VALIDATIONCOUNTRIES xml = null output,
                @ZIPLOOKUPCOUNTRIES xml = null output,
                @UPDATECONTACTS bit = null output,
                @HASCONTACTS bit = null output,
                @DONOTMAILREASONCODEID uniqueidentifier = null output,
                @HISTORICALSTARTDATE date = null output,
                @HISTORICALENDDATE date = null output,
                @DATEADDED date = null output,
                @INFOSOURCECOMMENTS nvarchar(256) = null output,
                @ISCONFIDENTIAL bit = null output,
                @ORIGINCODE tinyint = null output
            )
            as
                set nocount on;

                declare @SPOUSEID uniqueidentifier;

                set @DATALOADED = 0;
                set @TSLONG = 0;
                set @SPOUSEHASMATCHINGADDRESSES = 0;
                set @UPDATEMATCHINGSPOUSEADDRESSES = 0;

                if exists(select object_id from sys.objects where type = 'U' and name = 'RELATIONSHIP')
                    begin
                        select
                            @DATALOADED = 1,
                            @ADDRESSTYPECODEID = ADDRESS.ADDRESSTYPECODEID,
                            @PRIMARY = ADDRESS.ISPRIMARY,
                            @DONOTMAIL = ADDRESS.DONOTMAIL,
                            @STARTDATE = ADDRESS.STARTDATE,
                            @ENDDATE = ADDRESS.ENDDATE,
                            @COUNTRYID = ADDRESS.COUNTRYID,
                            @STATEID = ADDRESS.STATEID,
                            @ADDRESSBLOCK = ADDRESS.ADDRESSBLOCK,
                            @CITY = ADDRESS.CITY,
                            @POSTCODE = ADDRESS.POSTCODE,
                            @CART = ADDRESS.CART,
                            @DPC = ADDRESS.DPC,
                            @LOT = ADDRESS.LOT,
                            @OMITFROMVALIDATION = ADDRESSVALIDATIONUPDATE.OMITFROMVALIDATION,                                
                            @COUNTYCODEID = ADDRESSVALIDATIONUPDATE.COUNTYCODEID,
                            @CONGRESSIONALDISTRICTCODEID = ADDRESSVALIDATIONUPDATE.CONGRESSIONALDISTRICTCODEID,
                            @STATEHOUSEDISTRICTCODEID = ADDRESSVALIDATIONUPDATE.STATEHOUSEDISTRICTCODEID,
                            @STATESENATEDISTRICTCODEID = ADDRESSVALIDATIONUPDATE.STATESENATEDISTRICTCODEID,
                            @LOCALPRECINCTCODEID = ADDRESSVALIDATIONUPDATE.LOCALPRECINCTCODEID,
                            @INFOSOURCECODEID = ADDRESSVALIDATIONUPDATE.INFOSOURCECODEID,
                            @INFOSOURCECOMMENTS = ADDRESSVALIDATIONUPDATE.INFOSOURCECOMMENTS,
                            @REGIONCODEID = ADDRESSVALIDATIONUPDATE.REGIONCODEID,                                
                            @LASTVALIDATIONATTEMPTDATE = ADDRESSVALIDATIONUPDATE.LASTVALIDATIONATTEMPTDATE,
                            @VALIDATIONMESSAGE = ADDRESSVALIDATIONUPDATE.VALIDATIONMESSAGE,
                            @CERTIFICATIONDATA = ADDRESSVALIDATIONUPDATE.CERTIFICATIONDATA,                                
                            @NCOALASTSUBMITDATE = ADDRESSVALIDATIONUPDATE.NCOALASTSUBMITDATE,
                            @NCOARETURN = ADDRESSVALIDATIONUPDATE.NCOARETURN,
                            @NCOAFOOTNOTE = ADDRESSVALIDATIONUPDATE.NCOAFOOTNOTE,
                            @NCOADPVFOOTNOTE = ADDRESSVALIDATIONUPDATE.NCOADPVFOOTNOTE,
                            @NCOAMOVEDATE = ADDRESSVALIDATIONUPDATE.NCOAMOVEDATE,
                            @NCOADMASUPPRESSION = ADDRESSVALIDATIONUPDATE.NCOADMASUPPRESSION,
                            @NCOAMAILGRADE = ADDRESSVALIDATIONUPDATE.NCOAMAILGRADE,
                            @SPOUSEID = SPOUSE.ID,
                            @SPOUSENAME = SPOUSE.NAME,                                                                
                            @TSLONG = ADDRESS.TSLONG,
                            @DONOTMAILREASONCODEID = ADDRESS.DONOTMAILREASONCODEID,
                            @HISTORICALSTARTDATE = ADDRESS.HISTORICALSTARTDATE,
                            @HISTORICALENDDATE = ADDRESS.HISTORICALENDDATE,
                            @DATEADDED = ADDRESS.DATEADDED,
                            @ISCONFIDENTIAL = ADDRESS.ISCONFIDENTIAL,
                            @ORIGINCODE = ADDRESSVALIDATIONUPDATE.ORIGINCODE
                        from
                            dbo.ADDRESS
                            left join dbo.ADDRESSVALIDATIONUPDATE on ADDRESS.ID = ADDRESSVALIDATIONUPDATE.ID
                            left join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = ADDRESS.CONSTITUENTID and RELATIONSHIP.ISSPOUSE = 1
                            left join dbo.CONSTITUENT as SPOUSE on SPOUSE.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
                        where
                            ADDRESS.ID = @ID;

                        if exists (
                            select ADDRESS.ID
                            from dbo.ADDRESS
                            where
                                ADDRESS.CONSTITUENTID = @SPOUSEID and
                                ADDRESS.COUNTRYID = @COUNTRYID and
                                ADDRESS.ADDRESSBLOCK = @ADDRESSBLOCK and
                                ADDRESS.CITY = @CITY and
                                (ADDRESS.STATEID = @STATEID or (ADDRESS.STATEID is null and @STATEID is null)) and
                                ADDRESS.POSTCODE = @POSTCODE
                            ) begin
                                set @SPOUSEHASMATCHINGADDRESSES = 1;    
                                set @UPDATEMATCHINGSPOUSEADDRESSES = 1;
                        end

                        if exists(
                            select r.ID
                            from dbo.RELATIONSHIP r
                                inner join dbo.ADDRESS a1 on a1.CONSTITUENTID=r.RELATIONSHIPCONSTITUENTID
                                inner join dbo.ADDRESS a2 on a2.RELATIONSHIPID=r.ID
                            where 
                                a1.ID=@ID and
                                r.ISCONTACT=1 and
                                a1.COUNTRYID=a2.COUNTRYID and
                                (a1.STATEID=a2.STATEID or (a1.STATEID is null and a2.STATEID is null)) and
                                (a1.ADDRESSBLOCK=a2.ADDRESSBLOCK or (a1.ADDRESSBLOCK is null and a2.ADDRESSBLOCK is null)) and
                                (a1.CITY=a2.CITY or (a1.CITY is null and a2.CITY is null)) and
                                (a1.POSTCODE=a2.POSTCODE or (a1.POSTCODE is null and a2.POSTCODE is null)))
                            begin
                                set @HASCONTACTS=1
                            end
                            else
                            begin
                                set @HASCONTACTS=0
                            end
                    end
                else
                    select
                        @DATALOADED = 1,
                        @HASCONTACTS = 0,
                        @ADDRESSTYPECODEID = ADDRESS.ADDRESSTYPECODEID,
                        @PRIMARY = ADDRESS.ISPRIMARY,
                        @DONOTMAIL = ADDRESS.DONOTMAIL,
                        @STARTDATE = ADDRESS.STARTDATE,
                        @ENDDATE = ADDRESS.ENDDATE,
                        @COUNTRYID = ADDRESS.COUNTRYID,
                        @STATEID = ADDRESS.STATEID,
                        @ADDRESSBLOCK = ADDRESS.ADDRESSBLOCK,
                        @CITY = ADDRESS.CITY,
                        @POSTCODE = ADDRESS.POSTCODE,
                        @CART = ADDRESS.CART,
                        @DPC = ADDRESS.DPC,
                        @LOT = ADDRESS.LOT,
                        @OMITFROMVALIDATION = ADDRESSVALIDATIONUPDATE.OMITFROMVALIDATION,                            
                        @COUNTYCODEID = ADDRESSVALIDATIONUPDATE.COUNTYCODEID,
                        @CONGRESSIONALDISTRICTCODEID = ADDRESSVALIDATIONUPDATE.CONGRESSIONALDISTRICTCODEID,
                        @STATEHOUSEDISTRICTCODEID = ADDRESSVALIDATIONUPDATE.STATEHOUSEDISTRICTCODEID,
                        @STATESENATEDISTRICTCODEID = ADDRESSVALIDATIONUPDATE.STATESENATEDISTRICTCODEID,
                        @LOCALPRECINCTCODEID = ADDRESSVALIDATIONUPDATE.LOCALPRECINCTCODEID,
                        @INFOSOURCECODEID = ADDRESSVALIDATIONUPDATE.INFOSOURCECODEID,
                        @INFOSOURCECOMMENTS = ADDRESSVALIDATIONUPDATE.INFOSOURCECOMMENTS,
                        @REGIONCODEID = ADDRESSVALIDATIONUPDATE.REGIONCODEID,                                
                        @LASTVALIDATIONATTEMPTDATE = ADDRESSVALIDATIONUPDATE.LASTVALIDATIONATTEMPTDATE,
                        @VALIDATIONMESSAGE = ADDRESSVALIDATIONUPDATE.VALIDATIONMESSAGE,
                        @CERTIFICATIONDATA = ADDRESSVALIDATIONUPDATE.CERTIFICATIONDATA,
                        @NCOALASTSUBMITDATE = ADDRESSVALIDATIONUPDATE.NCOALASTSUBMITDATE,
                        @NCOARETURN = ADDRESSVALIDATIONUPDATE.NCOARETURN,
                        @NCOAFOOTNOTE = ADDRESSVALIDATIONUPDATE.NCOAFOOTNOTE,
                        @NCOADPVFOOTNOTE = ADDRESSVALIDATIONUPDATE.NCOADPVFOOTNOTE,
                        @NCOAMOVEDATE = ADDRESSVALIDATIONUPDATE.NCOAMOVEDATE,
                        @NCOADMASUPPRESSION = ADDRESSVALIDATIONUPDATE.NCOADMASUPPRESSION,
                        @NCOAMAILGRADE = ADDRESSVALIDATIONUPDATE.NCOAMAILGRADE,
                        @TSLONG = ADDRESS.TSLONG,
                        @DONOTMAILREASONCODEID = ADDRESS.DONOTMAILREASONCODEID,
                        @HISTORICALSTARTDATE = ADDRESS.HISTORICALSTARTDATE,
                        @HISTORICALENDDATE = ADDRESS.HISTORICALENDDATE,
                        @DATEADDED = ADDRESS.DATEADDED,
                        @ISCONFIDENTIAL = ADDRESS.ISCONFIDENTIAL,
                        @ORIGINCODE = ADDRESSVALIDATIONUPDATE.ORIGINCODE
                    from
                        dbo.ADDRESS
                    left join dbo.ADDRESSVALIDATIONUPDATE on ADDRESS.ID = ADDRESSVALIDATIONUPDATE.ID
                    where
                        ADDRESS.ID = @ID;

                if @ORIGINCODE is null
                    set @ORIGINCODE = 0

                select @VALIDATIONCOUNTRIES = dbo.UFN_COUNTRY_GETVALIDATIONCOUNTRIES_TOITEMLISTXML();
                select @ZIPLOOKUPCOUNTRIES = dbo.UFN_ZIPCITYSTATE_GETCOUNTRIES_TOITEMLISTXML();

                return 0;