USP_DATAFORMTEMPLATE_EDIT_ADDRESS_7

The save procedure used by the edit dataform template "Address Edit Form 4".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@ADDRESSTYPECODEID	uniqueidentifier	IN	Type
@PRIMARY	bit	IN	Set as primary address
@DONOTMAIL	bit	IN	Do not send mail to this address
@STARTDATE	UDT_MONTHDAY	IN	Start date
@ENDDATE	UDT_MONTHDAY	IN	End date
@COUNTRYID	uniqueidentifier	IN	Country
@STATEID	uniqueidentifier	IN	State
@ADDRESSBLOCK	nvarchar(150)	IN	Address
@CITY	nvarchar(50)	IN	City
@POSTCODE	nvarchar(12)	IN	ZIP
@CART	nvarchar(10)	IN	CART
@DPC	nvarchar(8)	IN	DPC
@LOT	nvarchar(5)	IN	LOT
@OMITFROMVALIDATION	bit	IN	Omit this address from validation
@COUNTYCODEID	uniqueidentifier	IN	County
@CONGRESSIONALDISTRICTCODEID	uniqueidentifier	IN	Congressional district
@STATEHOUSEDISTRICTCODEID	uniqueidentifier	IN	State house district
@STATESENATEDISTRICTCODEID	uniqueidentifier	IN	State senate district
@LOCALPRECINCTCODEID	uniqueidentifier	IN	Local precinct
@INFOSOURCECODEID	uniqueidentifier	IN	Information source
@REGIONCODEID	uniqueidentifier	IN	Region
@LASTVALIDATIONATTEMPTDATE	datetime	IN	Last attempt
@VALIDATIONMESSAGE	nvarchar(200)	IN	Validation message
@CERTIFICATIONDATA	int	IN
@UPDATECONTACTS	bit	IN	Update contacts that use this address
@DONOTMAILREASONCODEID	uniqueidentifier	IN	Reason
@UPDATEMATCHINGHOUSEHOLDADDRESSES	bit	IN	Update matching addresses in household
@HISTORICALSTARTDATE	date	IN	Start date
@HISTORICALENDDATE	date	IN	End date
@INFOSOURCECOMMENTS	nvarchar(256)	IN	Comments
@ISCONFIDENTIAL	bit	IN	This address is confidential
@CONSTITUENTDATAREVIEWROLLBACKREASONID	uniqueidentifier	IN	Reason

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_ADDRESS_7
                (
                    @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;

                    -- #253763 Check to see if there are duplicate addresses.  Same address type and same address

                    -- If nothing has changed on the edit form we don't want to check for duplicates.  This way if dups already exist and we

                    -- change nothing, we can still save the form without getting an error message.

                    if @HISTORICALENDDATE is null
                    begin
                        declare @NULLVALUE uniqueidentifier = newid();
                        declare @NULLDATE date = '9999-01-01'
                        if exists (select 'X' from dbo.ADDRESS a
                                    inner join dbo.ADDRESS a2 on a2.CONSTITUENTID = a.CONSTITUENTID and a2.ID <> @ID
                                    where (coalesce(a.ADDRESSBLOCK,'') <> coalesce(@ADDRESSBLOCK,'')
                                            or coalesce(a.ADDRESSTYPECODEID,@NULLVALUE) <> coalesce(@ADDRESSTYPECODEID,@NULLVALUE)
                                            or coalesce(a.STATEID,@NULLVALUE) <> coalesce(@STATEID,@NULLVALUE) 
                                            or coalesce(a.COUNTRYID,@NULLVALUE) <> coalesce(@COUNTRYID,@NULLVALUE) 
                                            or coalesce(a.POSTCODE,'') <> coalesce(@POSTCODE,'')
                                            or coalesce(a.HISTORICALENDDATE,@NULLDATE) <> coalesce(@HISTORICALENDDATE,@NULLDATE)
                                            -- Adding City comparison for WI#513923

                                            or coalesce(a.CITY,'') <> coalesce(@CITY,'')
                                            )
                                    and a.ID = @ID
                                    and a2.HISTORICALENDDATE is null
                                    and coalesce(a2.ADDRESSBLOCK,'') = coalesce(@ADDRESSBLOCK,'') 
                                    and coalesce(a2.ADDRESSTYPECODEID,@NULLVALUE) = coalesce(@ADDRESSTYPECODEID,@NULLVALUE)
                                    and coalesce(a2.STATEID,@NULLVALUE) = coalesce(@STATEID,@NULLVALUE) 
                                    and coalesce(a2.COUNTRYID,@NULLVALUE) = coalesce(@COUNTRYID,@NULLVALUE) 
                                    and coalesce(a2.POSTCODE,'') = coalesce(@POSTCODE,'')
                                    and coalesce(a2.CITY,'') = coalesce(@CITY,'')
                                    )
                                        raiserror('BBERR_ADDRESS_DUPLICATENOTALLOWED', 13, 1);
                    end

                    -- moved original code in the USP bellow, which is shared with other areas other then constituent address edit (duplicate search resolution screen)

                    exec dbo.USP_ADDRESS_EDITSAVE @ID, @CHANGEAGENTID, @ADDRESSTYPECODEID, @PRIMARY, @DONOTMAIL, @STARTDATE, @ENDDATE, @COUNTRYID, @STATEID,
                        @ADDRESSBLOCK, @CITY, @POSTCODE, @CART, @DPC, @LOT, @OMITFROMVALIDATION, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID,
                        @STATEHOUSEDISTRICTCODEID, @STATESENATEDISTRICTCODEID, @LOCALPRECINCTCODEID, @INFOSOURCECODEID, @REGIONCODEID,
                        @LASTVALIDATIONATTEMPTDATE, @VALIDATIONMESSAGE, @CERTIFICATIONDATA, @UPDATECONTACTS, @DONOTMAILREASONCODEID,
                        @UPDATEMATCHINGHOUSEHOLDADDRESSES, @HISTORICALSTARTDATE, @HISTORICALENDDATE, @INFOSOURCECOMMENTS, @ISCONFIDENTIAL,
                        @CONSTITUENTDATAREVIEWROLLBACKREASONID;

                    return 0;