USP_DATAFORMTEMPLATE_VIEW_MARRIAGEOPTIONSPAGEDATA

The load procedure used by the view dataform template "Marriage Options Page Expression View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@NAME	nvarchar(400)	INOUT	NAME
@HASSPOUSE	bit	INOUT	HASSPOUSE
@SPOUSEID	uniqueidentifier	INOUT	SPOUSEID
@SPOUSENAME	nvarchar(400)	INOUT	SPOUSENAME
@HASHOUSEHOLD	bit	INOUT	HASHOUSEHOLD
@HOUSEHOLDID	uniqueidentifier	INOUT	HOUSEHOLDID
@HOUSEHOLDNAME	nvarchar(400)	INOUT	HOUSEHOLDNAME
@SHOWCONTACTINFO	bit	INOUT	SHOWCONTACTINFO
@SHOWNAMEFORMATS	bit	INOUT	SHOWNAMEFORMATS
@SHOWHOUSEHOLDRECOGNITION	bit	INOUT	SHOWHOUSEHOLDRECOGNITION
@SEPERATEHOUSEHOLDS	bit	INOUT	SEPERATEHOUSEHOLDS
@SHOWINDIVIDUALRELATIONSHIPS	bit	INOUT	SHOWINDIVIDUALRELATIONSHIPS

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MARRIAGEOPTIONSPAGEDATA
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @NAME nvarchar(400) = null output,
  @HASSPOUSE bit = null output,
  @SPOUSEID uniqueidentifier = null output,
  @SPOUSENAME nvarchar(400) = null output,
  @HASHOUSEHOLD bit = null output,
  @HOUSEHOLDID uniqueidentifier = null output,
  @HOUSEHOLDNAME nvarchar(400) = null output,
  @SHOWCONTACTINFO bit = null output,
  @SHOWNAMEFORMATS bit = null output,
  @SHOWHOUSEHOLDRECOGNITION bit = null output,
  @SEPERATEHOUSEHOLDS bit = null output,
  @SHOWINDIVIDUALRELATIONSHIPS bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

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

  select
    @NAME = NF.NAME,
    @HASSPOUSE = case when RELATIONSHIP.ID is not null then 1 else 0 end,
    @SPOUSEID = RELATIONSHIP.RECIPROCALCONSTITUENTID,
    @SPOUSENAME = NF_SPOUSE.NAME,
    @DATALOADED = 1
  from
    dbo.CONSTITUENT CONSTIT
  left join
    dbo.RELATIONSHIP
  on
    RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTIT.ID and RELATIONSHIP.ISSPOUSE = 1
  left join
    dbo.CONSTITUENT SPOUSE
  on
    RELATIONSHIP.RECIPROCALCONSTITUENTID = SPOUSE.ID
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTIT.ID) NF
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(SPOUSE.ID) NF_SPOUSE
  where 
    CONSTIT.ID = @ID

  select
    @SHOWCONTACTINFO = UPDATECONTACTINFO,
    @SHOWNAMEFORMATS = UPDATENAMEFORMATS,
    @SHOWHOUSEHOLDRECOGNITION = UPDATEHOUSEHOLDRECOGNITION,
    @SHOWINDIVIDUALRELATIONSHIPS = UPDATEINDIVIDUALRELATIONSHIPS
  from
    dbo.UFN_MARRIAGEOPTION_GETRULES();

  --Household fields

  select
    @HASHOUSEHOLD = case when CONSTITUENT.ID is not null then 1 else 0 end,
    @HOUSEHOLDID = CONSTITUENT.ID,
    @HOUSEHOLDNAME = CONSTITUENT.NAME
  from
    dbo.GROUPMEMBER
  inner join
    dbo.GROUPDATA
  on
    GROUPDATA.ID = GROUPMEMBER.GROUPID and GROUPDATA.GROUPTYPECODE = 0
  left join
    dbo.CONSTITUENT
  on
    CONSTITUENT.ID = GROUPMEMBER.GROUPID
  left join 
    dbo.GROUPMEMBERDATERANGE as GMDR 
  on 
    GROUPMEMBER.ID = GMDR.GROUPMEMBERID
  where 
    GROUPMEMBER.MEMBERID = @ID
  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))

  --Separate Household?

  if @HOUSEHOLDID is not null and
  exists(
    select
      GROUPMEMBER.GROUPID
    from
      dbo.GROUPMEMBER
    inner join
      dbo.GROUPDATA
    on
      GROUPDATA.ID = GROUPMEMBER.GROUPID and GROUPDATA.GROUPTYPECODE = 0
    left join 
      dbo.GROUPMEMBERDATERANGE as GMDR 
    on 
      GROUPMEMBER.ID = GMDR.GROUPMEMBERID
    where 
      GROUPMEMBER.MEMBERID = @SPOUSEID
    and
      GROUPMEMBER.GROUPID <> @HOUSEHOLDID
    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))
    )
      set @SEPERATEHOUSEHOLDS = 1;
  else
      set @SEPERATEHOUSEHOLDS = 0;

return 0;