USP_DATAFORMTEMPLATE_PRELOAD_ADD_INDTOIND_RELATIONSHIP_2

The load procedure used by the edit dataform template "Relationship Individual to Individual Add Form 2"

Parameters

Parameter	Parameter Type	Mode	Description
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@CONSTITUENTNAME	nvarchar(154)	INOUT
@CONSTITUENTBELONGSTOHOUSEHOLD	bit	INOUT	Constituent belongs to a household
@CONSTITUENTHOUSEHOLDNAME	nvarchar(100)	INOUT	Constituent household name
@HASSPOUSE	bit	INOUT	Has spouse
@CONSTITUENTTYPE	int	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_PRELOAD_ADD_INDTOIND_RELATIONSHIP_2
(
  @CONSTITUENTID uniqueidentifier,
  @CONSTITUENTNAME nvarchar(154) = null output,
  @CONSTITUENTBELONGSTOHOUSEHOLD bit = null output,
  @CONSTITUENTHOUSEHOLDNAME nvarchar(100) = null output,
  @HASSPOUSE bit = null output,
  @CONSTITUENTTYPE int = null output
)
as
  set nocount on;

  select @CONSTITUENTNAME = NAME from dbo.CONSTITUENT where ID = @CONSTITUENTID;

  declare @CURRENTDATE datetime;
  set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

  set @CONSTITUENTBELONGSTOHOUSEHOLD = 0;

  select
    @CONSTITUENTBELONGSTOHOUSEHOLD = 1,
    @CONSTITUENTHOUSEHOLDNAME = C.KEYNAME
  from
    dbo.GROUPMEMBER GM
  inner join
    dbo.GROUPDATA GD on GM.GROUPID = GD.ID
  inner join
    dbo.CONSTITUENT C on GD.ID = C.ID
  left outer join
    dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
  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));        

  select @HASSPOUSE = 1
  from dbo.RELATIONSHIP R
  where R.RELATIONSHIPCONSTITUENTID = @CONSTITUENTID
  and R.ISSPOUSE = 1
  and ((R.ENDDATE is null) or (R.ENDDATE < @CURRENTDATE));

  set @CONSTITUENTTYPE = 0

  return 0;