USP_DATAFORMTEMPLATE_ADD_PHONE_PRELOAD

The load procedure used by the edit dataform template "Phone 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 phone number
@SPOUSENAME	nvarchar(154)	INOUT
@ISHOUSEHOLD	bit	INOUT
@ISHOUSEHOLDMEMBER	bit	INOUT
@COUNTRYID	uniqueidentifier	INOUT	Country
@COUNTRYCODES	xml	INOUT
@FORCEDPRIMARY	bit	INOUT
@CANEDITPRIMARY	bit	INOUT
@INVALIDFIELDS	nvarchar(256)	INOUT
Definition

Copy

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

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

                        select @COUNTRYID = ADDRESS.COUNTRYID from dbo.ADDRESS where ADDRESS.CONSTITUENTID = @CONSTITUENTID and ADDRESS.ISPRIMARY = 1
                        set @COUNTRYID = coalesce(@COUNTRYID, dbo.UFN_COUNTRY_GETDEFAULT());                  

                        select @COUNTRYCODES = dbo.UFN_COUNTRY_GETCOUNTRYCODES_TOITEMLISTXML();

                        return 0;