USP_DATAFORMTEMPLATE_EDITLOAD_REMOVESPOUSE

The load procedure used by the edit dataform template "Constituent Spouse Remove Edit 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.
@DISSOLVE	bit	INOUT	Dissolve household?
@REMOVEDATE	date	INOUT	End date
@DISSOLVEREASONCODEID	uniqueidentifier	INOUT	Reason
@HOUSEHOLDNAME	nvarchar(154)	INOUT	Household name
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.
@CONSTITUENTNAME	nvarchar(154)	INOUT	Constituent name
@SPOUSENAME	nvarchar(154)	INOUT	Spouse name
@RELATIONSHIPTYPECODEID	uniqueidentifier	INOUT	Relationship type
@RECIPROCALTYPECODEID	uniqueidentifier	INOUT	Reciprocal relationship type
@CONSTITUENTTYPE	int	INOUT
@SPOUSEID	uniqueidentifier	INOUT

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_REMOVESPOUSE 
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @DISSOLVE bit = null output,
  @REMOVEDATE date = null output,
  @DISSOLVEREASONCODEID uniqueidentifier = null output,
  @HOUSEHOLDNAME nvarchar(154) = null output, 
  @TSLONG bigint = 0 output,
  @CONSTITUENTNAME nvarchar(154) = null output,
  @SPOUSENAME nvarchar(154) = null output,
  @RELATIONSHIPTYPECODEID uniqueidentifier = null output,
  @RECIPROCALTYPECODEID uniqueidentifier = null output,
  @CONSTITUENTTYPE int = null output,
  @SPOUSEID uniqueidentifier = null output
)
as
set nocount on;

set @DATALOADED = 0;
set @TSLONG = 0;

--Check for spouse relationship
select
  @DATALOADED = 1,
  @SPOUSEID = RECIPROCALCONSTITUENTID,
  @REMOVEDATE = getdate(),
  @TSLONG = RELATIONSHIP.TSLONG,
  @CONSTITUENTNAME = RELATIONSHIPCONSTITUENT.NAME,
  @SPOUSENAME = SPOUSE.NAME,
  @RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODEID,
  @RECIPROCALTYPECODEID = RECIPROCALTYPECODEID
from
  dbo.RELATIONSHIP
  inner join dbo.CONSTITUENT as RELATIONSHIPCONSTITUENT on RELATIONSHIPCONSTITUENT.ID = @ID
  inner join dbo.CONSTITUENT as SPOUSE on SPOUSE.ID = RECIPROCALCONSTITUENTID                        
where
  RELATIONSHIPCONSTITUENTID = @ID
  and ISSPOUSE = 1;

declare @HOUSEHOLDID uniqueidentifier;
set @HOUSEHOLDID = null;
set @DISSOLVE = 0;
set @CONSTITUENTTYPE = 0

if @DATALOADED = 1 
  begin
    --Get household
    select top(1)
      @HOUSEHOLDID = GM.GROUPID
    from 
      dbo.GROUPMEMBER as GM
      inner join dbo.GROUPDATA as GD on GD.ID = GM.GROUPID
    where 
      GM.MEMBERID = @ID
      and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1 
      and GD.GROUPTYPECODE = 0;

    if not @HOUSEHOLDID is null
      begin
        set @DISSOLVE = 1;

        --Check that the household isn't already dissolved
        select
          @DISSOLVE = 0
        from
          dbo.DISSOLVEDGROUP
        where
          ID = @HOUSEHOLDID;

        --If the household is not dissolved check that the constituent 
        --and spouse are in the household and that the constituent or spouse is the primary member
        declare @CONSTITSPOUSEHOUSEHOLD tinyint;
        set @CONSTITSPOUSEHOUSEHOLD = 0;
        declare @CONSTITSPOUSEISPRIMARY tinyint;
        set @CONSTITSPOUSEISPRIMARY = 0;

        if @DISSOLVE = 1
          select
            @CONSTITSPOUSEHOUSEHOLD = count(GROUPMEMBER.ID),
            @CONSTITSPOUSEISPRIMARY = count(distinct ISPRIMARY)
          from
            dbo.GROUPMEMBER
            inner join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
          where
            GROUPID = @HOUSEHOLDID
            and GROUPMEMBERDATERANGE.DATETO is null
            and (MEMBERID = @ID
              or MEMBERID = @SPOUSEID);

        set @DISSOLVE = 0;
        if @CONSTITSPOUSEHOUSEHOLD = 2
          if @CONSTITSPOUSEISPRIMARY = 2
            set @DISSOLVE = 1;

        if @DISSOLVE = 1
          select
            @HOUSEHOLDNAME = NAME
          from
            dbo.CONSTITUENT
          where
            ID = @HOUSEHOLDID;
      end


  end

return 0;