USP_DATAFORMTEMPLATE_ADD_CONSTITUENTDATAREVIEW_ADDRESS_PRELOAD

The load procedure used by the edit 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.
@ADDRESSTYPECODEID	uniqueidentifier	INOUT	Type
@PRIMARY	bit	INOUT	Set as primary address
@DONOTMAIL	bit	INOUT	Do not send mail to this address
@STARTDATE	UDT_MONTHDAY	INOUT	Start date
@ENDDATE	UDT_MONTHDAY	INOUT	End date
@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
@LASTVALIDATIONATTEMPTDATE	datetime	INOUT	Last attempt
@VALIDATIONMESSAGE	nvarchar(200)	INOUT	Validation message
@CERTIFICATIONDATA	int	INOUT
@VALIDATIONCOUNTRIES	xml	INOUT
@ZIPLOOKUPCOUNTRIES	xml	INOUT
@DONOTMAILREASONCODEID	uniqueidentifier	INOUT	Reason
@ISHOUSEHOLD	bit	INOUT
@ISHOUSEHOLDMEMBER	bit	INOUT
@UPDATEMATCHINGHOUSEHOLDADDRESSES	bit	INOUT	Copy address information to household members
@HISTORICALSTARTDATE	date	INOUT	Start date
@INFOSOURCECOMMENTS	nvarchar(256)	INOUT	Comments
@ISCONFIDENTIAL	bit	INOUT	This address is confidential
@SPOUSENAME	nvarchar(154)	INOUT
@FORCEDPRIMARY	bit	INOUT
@CANEDITPRIMARY	bit	INOUT
@INVALIDFIELDS	nvarchar(256)	INOUT
@ORIGINCODE	tinyint	INOUT	Origin

Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_CONSTITUENTDATAREVIEW_ADDRESS_PRELOAD (
                    @AUDITID uniqueidentifier,
                    @ADDRESSTYPECODEID uniqueidentifier = null output,
                    @PRIMARY bit = null output,
                    @DONOTMAIL bit = null output,
                    @STARTDATE dbo.UDT_MONTHDAY = null output,
                    @ENDDATE dbo.UDT_MONTHDAY = 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,                    
                    @LASTVALIDATIONATTEMPTDATE datetime = null output,
                    @VALIDATIONMESSAGE nvarchar(200) = null output,
                    @CERTIFICATIONDATA integer = null output,
                    @VALIDATIONCOUNTRIES xml = null output,
                    @ZIPLOOKUPCOUNTRIES xml = null output,
                    @DONOTMAILREASONCODEID uniqueidentifier = null output,
                    @ISHOUSEHOLD bit = null output,
                    @ISHOUSEHOLDMEMBER bit = null output,
                    @UPDATEMATCHINGHOUSEHOLDADDRESSES bit = null output,
                    @HISTORICALSTARTDATE date = null output,
                    @INFOSOURCECOMMENTS nvarchar(256) = null output,
                    @ISCONFIDENTIAL bit = null output,
                    @SPOUSENAME nvarchar(154) = null output,
                    @FORCEDPRIMARY bit = null output,
                    @CANEDITPRIMARY bit = null output,
                    @INVALIDFIELDS nvarchar(256) = null output,
                    @ORIGINCODE tinyint = null output
                )
                as
                    set nocount on;

                    declare @DATALOADED bit;
                    set @DATALOADED = 0;

                    declare @CONSTITUENTID uniqueidentifier;

                    set @FORCEDPRIMARY = 0;
                    set @CANEDITPRIMARY = 0;

                    exec dbo.USP_CONSTITUENTDATAREVIEW_ADDRESS_LOADAUDITDATA
                        @AUDITID, @DATALOADED output, null, @CONSTITUENTID output,
                        @ADDRESSTYPECODEID output, @PRIMARY output, @DONOTMAIL output,
                        @STARTDATE output, @ENDDATE output, @COUNTRYID output, @STATEID output,
                        @ADDRESSBLOCK output, @CITY output, @POSTCODE output, @CART output,
                        @DPC output, @LOT output, @SPOUSENAME output, null, null, @OMITFROMVALIDATION output,
                        @COUNTYCODEID output, @CONGRESSIONALDISTRICTCODEID output,
                        @STATEHOUSEDISTRICTCODEID output, @STATESENATEDISTRICTCODEID output,
                        @LOCALPRECINCTCODEID output, @INFOSOURCECODEID output,
                        @REGIONCODEID output, @LASTVALIDATIONATTEMPTDATE output,
                        @VALIDATIONMESSAGE output, @CERTIFICATIONDATA output,
                        null, null, null, null, null, null, null,
                        @VALIDATIONCOUNTRIES output, @ZIPLOOKUPCOUNTRIES output, null, null,
                        @DONOTMAILREASONCODEID output, @HISTORICALSTARTDATE output, null, null,
                        @INFOSOURCECOMMENTS output, @ISCONFIDENTIAL output, @ORIGINCODE output

                    if @DATALOADED = 0
                        raiserror('Could not find the requested audit record.', 13, 1);

                    if exists(select object_id from sys.objects where type = 'U' and name = 'GROUPMEMBER')
                    begin
                        declare @CURRENTDATE date;
                        set @CURRENTDATE = getdate();

                        set @ISHOUSEHOLD = dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID);
                        set @ISHOUSEHOLDMEMBER = case when exists (
                            select 1 
                            from dbo.GROUPMEMBER GM 
                            left outer join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
                            left outer join dbo.GROUPDATA GD on GD.ID = GM.GROUPID
                            where GM.MEMBERID = @CONSTITUENTID 
                            and GD.GROUPTYPECODE = 0
                            and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
                                or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE)) 
                                or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE))
                        ) then 1 else 0 end;                                
                    end

                    -- handle primary
                    declare @COUNT smallint;

                    select @COUNT = count(*)
                    from dbo.ADDRESS
                    where CONSTITUENTID = @CONSTITUENTID;

                    if @PRIMARY = 0 and @COUNT = 0
                    begin
                        set @PRIMARY = 1;
                        set @FORCEDPRIMARY = 1;
                    end
                    else
                    begin
                        -- if another record exists, it must be primary, so allow the user
                        -- to edit the primary checkbox in this scenario
                        if @PRIMARY = 1 and @COUNT > 0
                            set @CANEDITPRIMARY = 1;
                    end

                    exec dbo.USP_CONSTITUENTDATAREVIEW_ADDRESS_CHECKGUIDS
                        @INVALIDFIELDS output,
                        @ADDRESSTYPECODEID output,
                        @COUNTRYID output,
                        @STATEID output,
                        @COUNTYCODEID output,
                        @CONGRESSIONALDISTRICTCODEID output,
                        @STATEHOUSEDISTRICTCODEID output,
                        @STATESENATEDISTRICTCODEID output,
                        @LOCALPRECINCTCODEID output,
                        @INFOSOURCECODEID output,
                        @REGIONCODEID output,
                        @DONOTMAILREASONCODEID output

                    return 0;