USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTGETHOUSEHOLDANDSPOUSE

The load procedure used by the view dataform template "Constituent Get Household And Spouse View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@HOUSEHOLDNAME	nvarchar(154)	INOUT	Constituent's household name
@HOUSEHOLDID	uniqueidentifier	INOUT	Constituent's household ID
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@SPOUSENAME	nvarchar(154)	INOUT	Constituent's spouse's name
@SPOUSEID	uniqueidentifier	INOUT	Constituent's spouse's ID

Definition

Copy



                CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_CONSTITUENTGETHOUSEHOLDANDSPOUSE
                (
                    @ID uniqueidentifier,
                    @HOUSEHOLDNAME nvarchar(154) = null output,
                    @HOUSEHOLDID uniqueidentifier = null output,
                    @DATALOADED bit = 0 output,
                    @SPOUSENAME nvarchar(154) = null output,
                    @SPOUSEID uniqueidentifier = null output
                )
                as
                    set nocount on;

                    -- Indicate data loaded even if the constituent isn't a member of a household.  In that case,

                    -- HOUSEHOLDNAME should be null.

                    set @DATALOADED = 1;

                    declare @CURRENTDATEEARLIESTTIME date;
                    set @CURRENTDATEEARLIESTTIME = getdate();            

                    select
                        @HOUSEHOLDID = C.ID,
                        @HOUSEHOLDNAME = C.NAME
                    from dbo.CONSTITUENT C
                    inner join dbo.GROUPMEMBER GM on C.ID = GM.GROUPID
                    inner join dbo.GROUPDATA GD on GM.GROUPID = GD.ID
                    left join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
                    where
                        GM.MEMBERID = @ID and
                        dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1 and
                        GD.GROUPTYPECODE = 0
                        -- Make sure their membership is current

                        and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATEEARLIESTTIME))
                        or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME)) 
                        or (GMDR.DATEFROM <= @CURRENTDATEEARLIESTTIME and GMDR.DATETO > @CURRENTDATEEARLIESTTIME));

                    select
                        @SPOUSEID = C.ID,
                        @SPOUSENAME = C.NAME
                    from
                        dbo.RELATIONSHIP R
                    inner join
                        dbo.CONSTITUENT C on C.ID = R.RECIPROCALCONSTITUENTID
                    where
                        R.RELATIONSHIPCONSTITUENTID = @ID
                    and
                        R.ISSPOUSE = 1;