USP_DATAFORMTEMPLATE_ADD_INDTOIND_RELATIONSHIP

The save procedure used by the add dataform template "Relationship Individual to Individual Add Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@CONSTITUENTID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@RECIPROCALCONSTITUENTID	uniqueidentifier	IN	Related individual
@RECIPROCALTYPECODEID	uniqueidentifier	IN	Individual is the
@RELATIONSHIPTYPECODEID	uniqueidentifier	IN	Constituent is the
@STARTDATE	datetime	IN	Start date
@ENDDATE	datetime	IN	End date
@ISSPOUSE	bit	IN	Is spouse?
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS	bit	IN	Apply to <Constituent> for revenue from <Reciprocal>
@PRIMARYSOFTCREDITMATCHFACTOR	decimal(5, 2)	IN	Recognition credit match percent
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS	bit	IN	Apply to <Reciprocal> for revenue from <Constituent>
@RECIPROCALSOFTCREDITMATCHFACTOR	decimal(5, 2)	IN	Recognition credit match percent
@PRIMARYRECOGNITIONTYPECODEID	uniqueidentifier	IN	Recognition credit type
@RECIPROCALRECOGNITIONTYPECODEID	uniqueidentifier	IN	Recognition credit type
@COPYCONTACTINFO	bit	IN	Copy primary contact information
@SKIP_ADDING_SECURITYGROUPS	bit	IN	Skip adding security groups
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@MEMBERSOFSAMEHOUSEHOLD	bit	IN	These individuals are members of the same household
@COMMENTS	nvarchar(max)	IN	Comments
@RELATIONSHIPS	xml	IN
@MARRIAGERELATIONSHIPID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_INDTOIND_RELATIONSHIP
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,    
  @CONSTITUENTID uniqueidentifier,
  @RECIPROCALCONSTITUENTID uniqueidentifier,
  @RECIPROCALTYPECODEID uniqueidentifier,
  @RELATIONSHIPTYPECODEID uniqueidentifier,
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @ISSPOUSE bit = 0,
  @PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0,
  @PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
  @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0,
  @RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
  @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null,
  @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null,
  @COPYCONTACTINFO bit = 0,
  @SKIP_ADDING_SECURITYGROUPS bit=0,
  @CURRENTAPPUSERID uniqueidentifier,
  @MEMBERSOFSAMEHOUSEHOLD bit = 0,
  @COMMENTS nvarchar(max) = null,
  @RELATIONSHIPS xml = null,
  @MARRIAGERELATIONSHIPID uniqueidentifier = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime;

  if @ID is null
    set @ID = newid();

  if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

  set @CURRENTDATE = getdate();

  begin try

    declare @RECIPROCAL_RELATEDTOMARRIAGE as bit = 0; --reciprocal constit shares a relationship with one of the spouses

    if not @MARRIAGERELATIONSHIPID is null 
    begin
      if exists(
        select SHARED.ID
        from dbo.RELATIONSHIP SHARED
        inner join 
            (select RELATIONSHIP.RELATIONSHIPCONSTITUENTID CONSTITUENTID
                from dbo.RELATIONSHIP 
                where ID = @MARRIAGERELATIONSHIPID
                union 
                select RELATIONSHIP.RECIPROCALCONSTITUENTID CONSTITUENTID
                from dbo.RELATIONSHIP 
                where ID = @MARRIAGERELATIONSHIPID) MARRIAGE
            on MARRIAGE.CONSTITUENTID = SHARED.RECIPROCALCONSTITUENTID
        where 
          SHARED.RELATIONSHIPCONSTITUENTID = @RECIPROCALCONSTITUENTID and
          (SHARED.STARTDATE is null or SHARED.STARTDATE <= @CURRENTDATE) and
          (SHARED.ENDDATE is null or SHARED.ENDDATE >= @CURRENTDATE))
      set @RECIPROCAL_RELATEDTOMARRIAGE = 1;
    end

    declare @SETID uniqueidentifier = newid();

    insert into dbo.RELATIONSHIPSET
    (
      ID,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    )
    values
    (
      @SETID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    insert into dbo.RELATIONSHIP
    (
      ID,
      RELATIONSHIPCONSTITUENTID,
      RECIPROCALCONSTITUENTID,
      RELATIONSHIPTYPECODEID,
      RECIPROCALTYPECODEID,
      ISSPOUSE,
      STARTDATE,
      ENDDATE,
      RELATIONSHIPSETID,
      COMMENTS,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    )
    values
    (
      @ID,
      @CONSTITUENTID,
      @RECIPROCALCONSTITUENTID,
      @RELATIONSHIPTYPECODEID,
      @RECIPROCALTYPECODEID,
      @ISSPOUSE,
      @STARTDATE,
      @ENDDATE,
      @SETID,
      @COMMENTS,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    exec dbo.USP_RECOGNITIONDEFAULTSUPDATE @CONSTITUENTID, @RECIPROCALCONSTITUENTID,
      @STARTDATE, @ENDDATE, @PRIMARYSOFTCREDITRELATIONSHIPEXISTS, @PRIMARYSOFTCREDITMATCHFACTOR,
      @PRIMARYRECOGNITIONTYPECODEID, @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS, 
      @RECIPROCALSOFTCREDITMATCHFACTOR, @RECIPROCALRECOGNITIONTYPECODEID, @CHANGEAGENTID;

    --Always copy contact info for the spouse

    if @ISSPOUSE = 1
      exec dbo.USP_SPOUSERELATIONSHIP_SETUPHOUSEHOLD @CONSTITUENTID, @RECIPROCALCONSTITUENTID, 
        1, @CHANGEAGENTID, @CURRENTDATE,    @SKIP_ADDING_SECURITYGROUPS,@CURRENTAPPUSERID;

    if (@MEMBERSOFSAMEHOUSEHOLD = 1) and (@ISSPOUSE = 0)
  exec dbo.USP_RELATIONSHIP_SETUPHOUSEHOLD @CONSTITUENTID, @RECIPROCALCONSTITUENTID, 
        @COPYCONTACTINFO, @CHANGEAGENTID, @CURRENTDATE,    @SKIP_ADDING_SECURITYGROUPS,@CURRENTAPPUSERID,'753F5883-1BF9-428f-A7D2-866C5CDFAFC1', 0, 1, 1, 0;

    if @MEMBERSOFSAMEHOUSEHOLD = 1
    begin
      exec dbo.USP_RELATIONSHIPS_ADDFROMXML @RELATIONSHIPS, @ID, @CHANGEAGENTID, @CURRENTDATE
      exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @CONSTITUENTID, @RECIPROCALCONSTITUENTID, @RELATIONSHIPTYPECODEID, @RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE
    end

    if @ISSPOUSE = 1
      exec dbo.USP_RELATIONSHIPS_MANAGELIFECHANGES @ID, @CONSTITUENTID, @RECIPROCALCONSTITUENTID, @CHANGEAGENTID;

    if not @MARRIAGERELATIONSHIPID is null 
      begin
        if @RECIPROCAL_RELATEDTOMARRIAGE = 1 
          exec dbo.USP_RELATIONSHIPBYMARRIAGE_CREATE @MARRIAGERELATIONSHIPID, @ID, @CHANGEAGENTID, @CURRENTDATE; --flag as a new shared relationship

      end
  end try

  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;