USP_DATAFORMTEMPLATE_ADD_SPECIFIC_ORGTOIND_RELATIONSHIP

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

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	INOUT	The output parameter indicating the ID of the record added.
@CONTEXTID	nvarchar(72)	IN	Input parameter indicating the context ID for the record being added.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@RECIPROCALTYPECODEID	uniqueidentifier	IN	Reciprocal relationship type
@RELATIONSHIPTYPECODEID	uniqueidentifier	IN	Relationship type
@STARTDATE	datetime	IN	Start date
@ENDDATE	datetime	IN	End date
@ISCONTACT	bit	IN	The individual is a contact for this organization
@ISPRIMARYCONTACT	bit	IN	Primary contact
@CONTACTTYPECODEID	uniqueidentifier	IN	Contact type
@POSITION	nvarchar(100)	IN	Job title
@ISPRIMARYBUSINESS	bit	IN	Primary business
@ISMATCHINGGIFTRELATIONSHIP	bit	IN	Matching gift relationship
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS	bit	IN	Apply to constituent for revenue from organization
@PRIMARYSOFTCREDITMATCHFACTOR	decimal(5, 2)	IN	Recognition credit match percent
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS	bit	IN	Apply to organization 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

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_SPECIFIC_ORGTOIND_RELATIONSHIP
(
  @ID uniqueidentifier = null output,
  @CONTEXTID nvarchar(72),
  @CHANGEAGENTID uniqueidentifier = null,
  @RECIPROCALTYPECODEID uniqueidentifier,
  @RELATIONSHIPTYPECODEID uniqueidentifier,                
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @ISCONTACT bit = 0,
  @ISPRIMARYCONTACT bit = 0,
  @CONTACTTYPECODEID uniqueidentifier = null,
  @POSITION nvarchar(100) = '',
  @ISPRIMARYBUSINESS bit = 0,
  @ISMATCHINGGIFTRELATIONSHIP bit = 0,
  @PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = 0,
  @PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
  @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = 0,
  @RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = 100,
  @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null,
  @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null
)
as
  set nocount on;

  declare @CURRENTDATE datetime = getdate();

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

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

  declare @CONSTITUENTID uniqueidentifier;
  declare @RECIPROCALCONSTITUENTID uniqueidentifier;

  begin try
    set @CONSTITUENTID = convert(uniqueidentifier, substring(@CONTEXTID, 0, 37));
    set @RECIPROCALCONSTITUENTID = convert(uniqueidentifier, substring(@CONTEXTID, 37, 37));
  end try
  begin catch
    set @CONSTITUENTID = null;
    set @RECIPROCALCONSTITUENTID = null;                        
  end catch

  begin try
    if @ISMATCHINGGIFTRELATIONSHIP = 1
      update
        dbo.RELATIONSHIP
      set
        ISMATCHINGGIFTRELATIONSHIP = 0,
        DATECHANGED = @CURRENTDATE,
        CHANGEDBYID = @CHANGEAGENTID
      where
        RELATIONSHIPCONSTITUENTID = @CONSTITUENTID and
        RECIPROCALCONSTITUENTID = @RECIPROCALCONSTITUENTID and
        ISMATCHINGGIFTRELATIONSHIP = 1;

    declare @RELATIONSHIPSETID uniqueidentifier = newid();

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

    insert into dbo.RELATIONSHIP
    (
      ID,
      RELATIONSHIPCONSTITUENTID,
      RECIPROCALCONSTITUENTID,
      RELATIONSHIPTYPECODEID,
      RECIPROCALTYPECODEID,
      ISCONTACT,
      ISPRIMARYCONTACT,
      CONTACTTYPECODEID,
      STARTDATE,
      ENDDATE,
      ISPRIMARYBUSINESS,
      ISMATCHINGGIFTRELATIONSHIP,
      RELATIONSHIPSETID,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    )
    values
    (
      @ID,
      @CONSTITUENTID,
      @RECIPROCALCONSTITUENTID,
      @RELATIONSHIPTYPECODEID,
      @RECIPROCALTYPECODEID,
      @ISCONTACT,
      @ISPRIMARYCONTACT,
      @CONTACTTYPECODEID,
      @STARTDATE,
      @ENDDATE,
      @ISPRIMARYBUSINESS,
      @ISMATCHINGGIFTRELATIONSHIP,
      @RELATIONSHIPSETID,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

    exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @CONSTITUENTID, @RECIPROCALCONSTITUENTID, @RELATIONSHIPTYPECODEID, 
      @RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE;

    if len(@POSITION) > 0
      insert into dbo.RELATIONSHIPJOBINFO
      (
        RELATIONSHIPSETID,
        JOBTITLE,
        ADDEDBYID,
        CHANGEDBYID,
        DATEADDED,
        DATECHANGED
      )
      values
      (
        @RELATIONSHIPSETID,
        @POSITION,
        @CHANGEAGENTID,
        @CHANGEAGENTID,
        @CURRENTDATE,
        @CURRENTDATE
      );

    --This form was written for to allow a relationship to be added from the Add/Edit Matching Gift forms. Those forms need to have 

    --the ID of the reciprocal relationship returned to them

    select
      @ID = ID 
    from
      dbo.RELATIONSHIP
    where
      RELATIONSHIPCONSTITUENTID = @RECIPROCALCONSTITUENTID and
      RECIPROCALCONSTITUENTID = @CONSTITUENTID and
      RELATIONSHIPTYPECODEID = @RECIPROCALTYPECODEID and
      RECIPROCALTYPECODEID = @RELATIONSHIPTYPECODEID;

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

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

  return 0;