USP_DATAFORMTEMPLATE_ADD_ADDRESS_PRELOAD

The load procedure used by the edit dataform template "Address Add Form"
Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@PRIMARY	bit	INOUT	Set as primary address
@COUNTRYID	uniqueidentifier	INOUT	Country
@SPOUSENAME	nvarchar(154)	INOUT
@ZIPLOOKUPCOUNTRIES	xml	INOUT
@ISHOUSEHOLD	bit	INOUT
@ISHOUSEHOLDMEMBER	bit	INOUT
Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_ADDRESS_PRELOAD (
                        @CONSTITUENTID uniqueidentifier,
                        @PRIMARY bit = null output,
                        @COUNTRYID uniqueidentifier = null output,
                        @SPOUSENAME nvarchar(154) = null output,
                        @ZIPLOOKUPCOUNTRIES xml = null output,
                        @ISHOUSEHOLD bit = null output,
                        @ISHOUSEHOLDMEMBER bit = null output
                    ) as 
                        set nocount on;

                        exec @COUNTRYID = dbo.UFN_COUNTRY_GETDEFAULT;

                        if exists(select object_id from sys.objects where type = 'U' and name = 'RELATIONSHIP')
                            select
                                @PRIMARY = case when count(ADDRESS.ID) = 0 then 1 else 0 end,
                                @SPOUSENAME = SPOUSE.NAME
                            from
                                dbo.CONSTITUENT
                            left join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID and ADDRESS.ISPRIMARY = 1
                            left join dbo.RELATIONSHIP on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID and RELATIONSHIP.ISSPOUSE = 1
                            left join dbo.CONSTITUENT as SPOUSE on SPOUSE.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID
                            where
                                CONSTITUENT.ID = @CONSTITUENTID
                            group by
                                SPOUSE.NAME;
                        else
                            select
                                @PRIMARY = case when count(ADDRESS.ID) = 0 then 1 else 0 end
                            from
                                dbo.ADDRESS
                            where
                                ADDRESS.CONSTITUENTID = @CONSTITUENTID;

                        select @ZIPLOOKUPCOUNTRIES = dbo.UFN_ZIPCITYSTATE_GETCOUNTRIES_TOITEMLISTXML();

                        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;

                        return 0;