USP_DATAFORMTEMPLATE_ADD_ADDRESS_2

The save procedure used by the add dataform template "Address Add Form 2".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@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
@HISTORICALSTARTDATE	datetime	IN	Start date
@RECENTMOVE	bit	IN	Recently moved/changed from this address?
@OLDADDRESSID	uniqueidentifier	IN	Old address
@CART	nvarchar(10)	IN	CART
@DPC	nvarchar(8)	IN	DPC
@LOT	nvarchar(5)	IN	LOT
@UPDATEMATCHINGSPOUSEADDRESSES	bit	IN	Update matching address information for spouse
@UPDATEMATCHINGHOUSEHOLDADDRESSES	bit	IN	Copy address information to household members
@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
@DONOTMAILREASONCODEID	uniqueidentifier	IN	Reason
@INFOSOURCECOMMENTS	nvarchar(256)	IN	Comments
@ISCONFIDENTIAL	bit	IN	This address is confidential
@CONSTITUENTDATAREVIEWROLLBACKREASONID	uniqueidentifier	IN	Reason
@ORIGINCODE	tinyint	IN	Origin

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_ADDRESS_2
                    (
                        @ID uniqueidentifier = null output,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @CONSTITUENTID uniqueidentifier,
                        @ADDRESSTYPECODEID uniqueidentifier = null,
                        @PRIMARY bit = 0,
                        @DONOTMAIL bit = 0,
                        @STARTDATE dbo.UDT_MONTHDAY = '0000',
                        @ENDDATE dbo.UDT_MONTHDAY = '0000',
                        @COUNTRYID uniqueidentifier,
                        @STATEID uniqueidentifier = null,
                        @ADDRESSBLOCK nvarchar(150) = '',
                        @CITY nvarchar(50) = '',
                        @POSTCODE nvarchar(12) = '',
                        @HISTORICALSTARTDATE datetime = null,
                        @RECENTMOVE bit = 0,
                        @OLDADDRESSID uniqueidentifier = null,
                        @CART nvarchar(10) = '',
                        @DPC nvarchar(8) = '',
                        @LOT nvarchar(5) = '',
                        @UPDATEMATCHINGSPOUSEADDRESSES bit = 0,
                        @UPDATEMATCHINGHOUSEHOLDADDRESSES bit = null,
                        @OMITFROMVALIDATION bit = 0,
                        @COUNTYCODEID uniqueidentifier = null,
                        @CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
                        @STATEHOUSEDISTRICTCODEID uniqueidentifier = null,
                        @STATESENATEDISTRICTCODEID uniqueidentifier = null,
                        @LOCALPRECINCTCODEID uniqueidentifier = null,
                        @INFOSOURCECODEID uniqueidentifier = null,
                        @REGIONCODEID uniqueidentifier = null,
                        @LASTVALIDATIONATTEMPTDATE datetime = null,
                        @VALIDATIONMESSAGE nvarchar(200) = '',
                        @CERTIFICATIONDATA integer = 0,
                        @DONOTMAILREASONCODEID uniqueidentifier = null,
                        @INFOSOURCECOMMENTS nvarchar(256) = '',
                        @ISCONFIDENTIAL bit = 0,
                        @CONSTITUENTDATAREVIEWROLLBACKREASONID uniqueidentifier = null,  -- used by constituent data review

                        @ORIGINCODE tinyint = 0
                    ) as
                        set nocount on;

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

                        declare @NULLVALUE uniqueidentifier = newid();
                        if exists (select 'X' 
                                from dbo.ADDRESS
                                where  HISTORICALENDDATE is null and CONSTITUENTID = @CONSTITUENTID
                                and coalesce(ADDRESSBLOCK,'') = coalesce(@ADDRESSBLOCK,'') and coalesce(CITY,'') = coalesce(@CITY,'')
                                and coalesce(STATEID,@NULLVALUE) = coalesce(@STATEID,@NULLVALUE) 
                                and coalesce(ADDRESSTYPECODEID,@NULLVALUE) = coalesce(@ADDRESSTYPECODEID,@NULLVALUE)
                                and coalesce(COUNTRYID,@NULLVALUE) = coalesce(@COUNTRYID,@NULLVALUE) 
                                and coalesce(POSTCODE,'') =coalesce(@POSTCODE,''))
                                    raiserror('BBERR_ADDRESS_DUPLICATENOTALLOWED', 13, 1);    

                        exec dbo.USP_ADDRESS_ADD
                            @ID = @ID output,
                            @CHANGEAGENTID = @CHANGEAGENTID,    
                            @CONSTITUENTID = @CONSTITUENTID,
                            @ADDRESSTYPECODEID = @ADDRESSTYPECODEID,
                            @PRIMARY = @PRIMARY,
                            @DONOTMAIL = @DONOTMAIL,
                            @STARTDATE = @STARTDATE,
                            @ENDDATE = @ENDDATE,
                            @COUNTRYID = @COUNTRYID,
                            @STATEID = @STATEID,
                            @ADDRESSBLOCK = @ADDRESSBLOCK,
                            @CITY = @CITY,
                            @POSTCODE = @POSTCODE,
                            @HISTORICALSTARTDATE = @HISTORICALSTARTDATE,
                            @RECENTMOVE = @RECENTMOVE,
                            @OLDADDRESSID = @OLDADDRESSID,
                            @CART = @CART,
                            @DPC = @DPC,
                            @LOT = @LOT,
                            @UPDATEMATCHINGSPOUSEADDRESSES = @UPDATEMATCHINGSPOUSEADDRESSES,
                            @UPDATEMATCHINGHOUSEHOLDADDRESSES = @UPDATEMATCHINGHOUSEHOLDADDRESSES,
                            @OMITFROMVALIDATION = @OMITFROMVALIDATION,                        
                            @COUNTYCODEID = @COUNTYCODEID,
                            @CONGRESSIONALDISTRICTCODEID = @CONGRESSIONALDISTRICTCODEID,
                            @STATEHOUSEDISTRICTCODEID = @STATEHOUSEDISTRICTCODEID,
                            @STATESENATEDISTRICTCODEID = @STATESENATEDISTRICTCODEID,
                            @LOCALPRECINCTCODEID = @LOCALPRECINCTCODEID,
                            @INFOSOURCECODEID = @INFOSOURCECODEID,
                            @REGIONCODEID = @REGIONCODEID,
                            @LASTVALIDATIONATTEMPTDATE = @LASTVALIDATIONATTEMPTDATE,
                            @VALIDATIONMESSAGE = @VALIDATIONMESSAGE,
                            @CERTIFICATIONDATA = @CERTIFICATIONDATA,
                            @DONOTMAILREASONCODEID = @DONOTMAILREASONCODEID,
                            @INFOSOURCECOMMENTS = @INFOSOURCECOMMENTS,
                            @ISCONFIDENTIAL = @ISCONFIDENTIAL,
                            @CONSTITUENTDATAREVIEWROLLBACKREASONID = @CONSTITUENTDATAREVIEWROLLBACKREASONID,
                            @ORIGINCODE = @ORIGINCODE

                        return 0;