USP_DATAFORMTEMPLATE_ADD_CONSTITUENTDATAREVIEW_ADDRESS

The save procedure used by the add dataform template "Constituent Data Review Address Add Data Form".

Parameters

Parameter	Parameter Type	Mode	Description
@AUDITID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@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.
@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_CONSTITUENTDATAREVIEW_ADDRESS
                    (
                        @AUDITID uniqueidentifier,
                        @ID uniqueidentifier = null output,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @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,
                        @ORIGINCODE tinyint = 0
                    )
                    as
                        declare @CONSTITUENTID uniqueidentifier;

                        select @CONSTITUENTID = CONSTITUENTID,
                               @ID = AUDITRECORDID
                        from dbo.ADDRESSAUDIT
                        where AUDITID = @AUDITID;

                        if @ID is null
                            raiserror('ERR_COULDNOTFINDAUDIT', 13, 1);

                        -- fix the last add's rollback, if any
                        update dbo.CONSTITUENTDATAREVIEWROLLBACK
                        set ROLLBACKAUDITID = (select top 1 AUDITID
                                               from dbo.ADDRESSAUDIT
                                               where AUDITRECORDID = @ID
                                               and DATEADDED = DATECHANGED
                                               order by AUDITDATE desc)
                        where CONTEXTRECORDID = @ID
                        and ROLLBACKAUDITID is null;

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

                        declare @CURRENTDATE datetime
                        set @CURRENTDATE = getdate()

                        insert into dbo.CONSTITUENTDATAREVIEWROLLBACK
                            (ID, CONTEXTRECORDID, ROLLBACKAUDITID, SOURCEAUDITID, CONSTITUENTDATAREVIEWROLLBACKREASONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values
                            (newid(), @ID, null, @AUDITID, @CONSTITUENTDATAREVIEWROLLBACKREASONID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)

                        return 0;