USP_DATAFORMTEMPLATE_ADD_ADDRESS_COPYTOSPOUSE_PRELOAD

The load procedure used by the edit dataform template "Address Copy To Spouse Add Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ADDRESSID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CONSTITUENTID	uniqueidentifier	INOUT
@PRIMARY	bit	INOUT	Set as primary address
@ADDRESSTYPECODEID	uniqueidentifier	INOUT	Type
@COUNTRYID	uniqueidentifier	INOUT	Country
@STATEID	uniqueidentifier	INOUT	State
@ADDRESSBLOCK	nvarchar(150)	INOUT	Address
@CITY	nvarchar(50)	INOUT	City
@POSTCODE	nvarchar(12)	INOUT	ZIP
@CART	nvarchar(10)	INOUT	CART
@DPC	nvarchar(8)	INOUT	DPC
@LOT	nvarchar(5)	INOUT	LOT
@OMITFROMVALIDATION	bit	INOUT	Omit this address from validation
@COUNTYCODEID	uniqueidentifier	INOUT	County
@CONGRESSIONALDISTRICTCODEID	uniqueidentifier	INOUT	Congressional district
@STATEHOUSEDISTRICTCODEID	uniqueidentifier	INOUT	State house district
@STATESENATEDISTRICTCODEID	uniqueidentifier	INOUT	State senate district
@LOCALPRECINCTCODEID	uniqueidentifier	INOUT	Local precinct
@INFOSOURCECODEID	uniqueidentifier	INOUT	Information source
@REGIONCODEID	uniqueidentifier	INOUT	Region
@CERTIFICATIONDATA	int	INOUT
@DONOTMAILREASONCODEID	uniqueidentifier	INOUT	Reason
@INFOSOURCECOMMENTS	nvarchar(256)	INOUT	Comments
@DONOTMAIL	bit	INOUT	Do not send mail to this address
@ISCONFIDENTIAL	bit	INOUT	This address is confidential
@VALIDATIONCOUNTRIES	xml	INOUT
@ZIPLOOKUPCOUNTRIES	xml	INOUT
@FORCEDPRIMARY	bit	INOUT
@CANEDITPRIMARY	bit	INOUT
@INVALIDFIELDS	nvarchar(256)	INOUT

Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_ADDRESS_COPYTOSPOUSE_PRELOAD (
                        @ADDRESSID uniqueidentifier,
                        @CONSTITUENTID uniqueidentifier = null output,
                        @PRIMARY bit = null output,
                        @ADDRESSTYPECODEID uniqueidentifier = 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,
                        @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,                    
                        @CERTIFICATIONDATA integer = null output,
                        @DONOTMAILREASONCODEID uniqueidentifier = null output,
                        @INFOSOURCECOMMENTS nvarchar(256) = null output,
                        @DONOTMAIL bit = null output,
                        @ISCONFIDENTIAL bit = null output,

                        @VALIDATIONCOUNTRIES xml = null output,
                        @ZIPLOOKUPCOUNTRIES xml = null output,
                        @FORCEDPRIMARY bit = null output,            -- used by constituent data review
                        @CANEDITPRIMARY bit = null output,            -- used by constituent data review
                        @INVALIDFIELDS nvarchar(256) = null output    -- used by constituent data review
                    ) as 

                    declare @CURRENTDATE datetime;
                    set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

                        select
                            @CONSTITUENTID = SPOUSERELATIONSHIP.RECIPROCALCONSTITUENTID,
                            @ADDRESSTYPECODEID = ADDRESS.ADDRESSTYPECODEID,
                            @DONOTMAIL = ADDRESS.DONOTMAIL,
                            @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,                                
                            @DONOTMAILREASONCODEID = ADDRESS.DONOTMAILREASONCODEID,
                            @ISCONFIDENTIAL = ADDRESS.ISCONFIDENTIAL
                        from
                            dbo.ADDRESS
                            left join dbo.ADDRESSVALIDATIONUPDATE on ADDRESS.ID = ADDRESSVALIDATIONUPDATE.ID
                            left join dbo.RELATIONSHIP SPOUSERELATIONSHIP on ADDRESS.CONSTITUENTID = SPOUSERELATIONSHIP.RELATIONSHIPCONSTITUENTID and SPOUSERELATIONSHIP.ISSPOUSE = 1
                        where
                            ADDRESS.ID = @ADDRESSID;

                        select
                            @PRIMARY = case when count(ADDRESS.ID) = 0 then 1 else 0 end
                        from
                            dbo.ADDRESS
                        where
                            ADDRESS.CONSTITUENTID = @CONSTITUENTID;