USP_ADDRESS_EDITSAVE

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@ADDRESSTYPECODEID	uniqueidentifier	IN
@PRIMARY	bit	IN
@DONOTMAIL	bit	IN
@STARTDATE	UDT_MONTHDAY	IN
@ENDDATE	UDT_MONTHDAY	IN
@COUNTRYID	uniqueidentifier	IN
@STATEID	uniqueidentifier	IN
@ADDRESSBLOCK	nvarchar(150)	IN
@CITY	nvarchar(50)	IN
@POSTCODE	nvarchar(12)	IN
@CART	nvarchar(10)	IN
@DPC	nvarchar(8)	IN
@LOT	nvarchar(5)	IN
@OMITFROMVALIDATION	bit	IN
@COUNTYCODEID	uniqueidentifier	IN
@CONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN
@STATEHOUSEDISTRICTCODEID	uniqueidentifier	IN
@STATESENATEDISTRICTCODEID	uniqueidentifier	IN
@LOCALPRECINCTCODEID	uniqueidentifier	IN
@INFOSOURCECODEID	uniqueidentifier	IN
@REGIONCODEID	uniqueidentifier	IN
@LASTVALIDATIONATTEMPTDATE	datetime	IN
@VALIDATIONMESSAGE	nvarchar(200)	IN
@CERTIFICATIONDATA	int	IN
@UPDATECONTACTS	bit	IN
@DONOTMAILREASONCODEID	uniqueidentifier	IN
@UPDATEMATCHINGHOUSEHOLDADDRESSES	bit	IN
@HISTORICALSTARTDATE	date	IN
@HISTORICALENDDATE	date	IN
@INFOSOURCECOMMENTS	nvarchar(256)	IN
@ISCONFIDENTIAL	bit	IN
@CONSTITUENTDATAREVIEWROLLBACKREASONID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_ADDRESS_EDITSAVE
  (
      @ID uniqueidentifier,
        @CHANGEAGENTID uniqueidentifier = null,
        @ADDRESSTYPECODEID uniqueidentifier,
        @PRIMARY bit,
        @DONOTMAIL bit,
        @STARTDATE dbo.UDT_MONTHDAY,
        @ENDDATE dbo.UDT_MONTHDAY,
        @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,
        @STATESENATEDISTRICTCODEID uniqueidentifier,
        @LOCALPRECINCTCODEID uniqueidentifier,
        @INFOSOURCECODEID uniqueidentifier,
        @REGIONCODEID uniqueidentifier,                    
        @LASTVALIDATIONATTEMPTDATE datetime,
        @VALIDATIONMESSAGE nvarchar(200),
        @CERTIFICATIONDATA integer,
        @UPDATECONTACTS bit,
        @DONOTMAILREASONCODEID uniqueidentifier,
        @UPDATEMATCHINGHOUSEHOLDADDRESSES bit,
        @HISTORICALSTARTDATE date,
        @HISTORICALENDDATE date,
        @INFOSOURCECOMMENTS nvarchar(256),
        @ISCONFIDENTIAL bit,
        @CONSTITUENTDATAREVIEWROLLBACKREASONID uniqueidentifier  -- used by constituent data review

    )
    as
        set nocount on;

        if @PRIMARY = 0 and (select count(*) from dbo.ADDRESS where CONSTITUENTID = (select CONSTITUENTID from ADDRESS as ADDSUB where ADDSUB.ID = @ID) and ISPRIMARY = 1) = 0
            raiserror('ERR_ADDRESS_MUSTHAVEPRIMARY', 13, 1);

        if @CERTIFICATIONDATA is null
            set @CERTIFICATIONDATA = 0;

        if @INFOSOURCECOMMENTS is null
            set @INFOSOURCECOMMENTS = '';

              if @HISTORICALSTARTDATE is not null
                  begin
                      if @HISTORICALSTARTDATE > GETDATE()
                          raiserror('Start date cannot be future dated.', 13, 1);
                  end
              if @HISTORICALENDDATE is not null
                  begin
                      if @HISTORICALENDDATE > GETDATE()
                          raiserror('End date cannot be future dated.', 13, 1);
                  end

        -- Clear county value if the country doesn't have a label for it.

        if @COUNTRYID is not null
            and not exists(
                select 1
                from dbo.COUNTRY C
                inner join dbo.COUNTRYADDRESSLABEL CAL on CAL.COUNTRYID = C.ID
                where C.ID = @COUNTRYID
                and (coalesce(CAL.COUNTYLABEL,'') <> '')
            )
        begin
            set @COUNTYCODEID = null;
        end

        if exists(select object_id from sys.objects where type = 'U' and name = 'GROUPMEMBER')
        begin
            begin try
                if @UPDATEMATCHINGHOUSEHOLDADDRESSES = 1 begin
                    exec dbo.USP_ADDRESS_UPDATEMATCHINGADDRESSES @ADDRESSID = @ID,
                        @CHANGEAGENTID = @CHANGEAGENTID,
                        @ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
                        @PRIMARY = @PRIMARY,
                        @DONOTMAIL = @DONOTMAIL,
                        @STARTDATE = @STARTDATE,
                        @ENDDATE = @ENDDATE,
                        @COUNTRYID = @COUNTRYID,
                        @STATEID = @STATEID,
                        @ADDRESSBLOCK = @ADDRESSBLOCK,
                        @CITY = @CITY,
                        @POSTCODE = @POSTCODE,
                        @CART = @CART,
                        @DPC = @DPC,
                        @LOT = @LOT,
                        @OMITFROMVALIDATION = @OMITFROMVALIDATION,
                        @COUNTYCODEID = @COUNTYCODEID,
                        @CONGRESSIONALDISTRICTCODEID = @CONGRESSIONALDISTRICTCODEID,
                        @STATEHOUSEDISTRICTCODEID = @STATEHOUSEDISTRICTCODEID,
                        @STATESENATEDISTRICTCODEID = @STATESENATEDISTRICTCODEID,
                        @LOCALPRECINCTCODEID = @LOCALPRECINCTCODEID,
                        @INFOSOURCECODEID = @INFOSOURCECODEID,
                        @REGIONCODEID = @REGIONCODEID,
                        @LASTVALIDATIONATTEMPTDATE = @LASTVALIDATIONATTEMPTDATE,
                        @VALIDATIONMESSAGE = @VALIDATIONMESSAGE,
                        @CERTIFICATIONDATA = @CERTIFICATIONDATA,
                        @DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
                        @HISTORICALSTARTDATE = @HISTORICALSTARTDATE,
                        @HISTORICALENDDATE = @HISTORICALENDDATE,
                        @INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS,
                        @ISCONFIDENTIAL = @ISCONFIDENTIAL
                end

                exec dbo.USP_ADDRESS_UPDATE @ID, @CHANGEAGENTID, @ADDRESSTYPECODEID, @PRIMARY, @DONOTMAIL, @STARTDATE, @ENDDATE,
                @COUNTRYID, @STATEID, @ADDRESSBLOCK, @CITY, @POSTCODE, @CART, @DPC, @LOT, 0, -- Spouse addresses should be taken care of through the household address updates

                @OMITFROMVALIDATION, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID, @STATEHOUSEDISTRICTCODEID,
                @STATESENATEDISTRICTCODEID, @LOCALPRECINCTCODEID, @INFOSOURCECODEID, @REGIONCODEID, @LASTVALIDATIONATTEMPTDATE,
                @VALIDATIONMESSAGE, @CERTIFICATIONDATA, @UPDATECONTACTS, @DONOTMAILREASONCODEID, @HISTORICALSTARTDATE, @HISTORICALENDDATE,
                @INFOSOURCECOMMENTS, @ISCONFIDENTIAL

            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch
        end