USP_DATAFORMTEMPLATE_ADD_EMAILADDRESS_PRELOAD

The load procedure used by the edit dataform template "Email 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 email address
@SPOUSENAME	nvarchar(154)	INOUT
@ISHOUSEHOLD	bit	INOUT
@ISHOUSEHOLDMEMBER	bit	INOUT
@FORCEDPRIMARY	bit	INOUT
@CANEDITPRIMARY	bit	INOUT
@INVALIDFIELDS	nvarchar(256)	INOUT
Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_EMAILADDRESS_PRELOAD (
                        @CONSTITUENTID uniqueidentifier,
                        @PRIMARY bit = null output,
                        @SPOUSENAME nvarchar(154) = null output,
                        @ISHOUSEHOLD bit = null output,
                        @ISHOUSEHOLDMEMBER bit = 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
                        set nocount on;

                        if exists(select object_id from sys.objects where type = 'U' and name = 'RELATIONSHIP')
                            select
                                @PRIMARY = case when count(EMAILADDRESS.ID) = 0 then 1 else 0 end,
                                @SPOUSENAME = SPOUSE.NAME
                            from
                                dbo.CONSTITUENT
                            left join dbo.EMAILADDRESS on EMAILADDRESS.CONSTITUENTID = CONSTITUENT.ID and EMAILADDRESS.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(EMAILADDRESS.ID) = 0 then 1 else 0 end
                            from
                                dbo.EMAILADDRESS
                            where
                                EMAILADDRESS.CONSTITUENTID = @CONSTITUENTID;

                        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;