USP_DATAFORMTEMPLATE_ADD_ORGTOORG_RELATIONSHIP

The save procedure used by the add dataform template "Relationship Organization to Organization 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 organization
@RECIPROCALTYPECODEID	uniqueidentifier	IN	Organization is the
@RELATIONSHIPTYPECODEID	uniqueidentifier	IN	Constituent is the
@STARTDATE	datetime	IN	Start date
@ENDDATE	datetime	IN	End date
@COMMENTS	nvarchar(max)	IN	Comments
@ISMATCHINGGIFTRELATIONSHIP	bit	IN	The organization will match contributions for this 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
@ISPARENTRELATIONSHIP	bit	IN	Constituent is the parent organization
@ISPARENTRECIPROCAL	bit	IN	Organization is the parent organization

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_ORGTOORG_RELATIONSHIP
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @CONSTITUENTID uniqueidentifier,
  @RECIPROCALCONSTITUENTID uniqueidentifier,
  @RECIPROCALTYPECODEID uniqueidentifier,
  @RELATIONSHIPTYPECODEID uniqueidentifier,
  @STARTDATE datetime = null,
  @ENDDATE datetime = null,
  @COMMENTS nvarchar(max) = null,
  @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,
  @ISPARENTRELATIONSHIP bit =0,
  @ISPARENTRECIPROCAL bit = 0
)
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;

  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 @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,
      STARTDATE,
      ENDDATE,
      RELATIONSHIPSETID,
      COMMENTS,
      ISMATCHINGGIFTRELATIONSHIP,
      ADDEDBYID,
      CHANGEDBYID,
      DATEADDED,
      DATECHANGED
    )
    values
    (
      @ID,
      @CONSTITUENTID,
      @RECIPROCALCONSTITUENTID,
      @RELATIONSHIPTYPECODEID,
      @RECIPROCALTYPECODEID,
      @STARTDATE,
      @ENDDATE,
      @SETID,
      @COMMENTS,
      @ISMATCHINGGIFTRELATIONSHIP,
      @CHANGEAGENTID,
      @CHANGEAGENTID,
      @CURRENTDATE,
      @CURRENTDATE
    );

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

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

    declare @PARENTID uniqueidentifier = coalesce((select PARENTCORPID from dbo.ORGANIZATIONDATA where ID = @CONSTITUENTID),'00000000-0000-0000-0000-000000000000');
    declare @RECIPROCALPARENTID uniqueidentifier =coalesce((select PARENTCORPID from dbo.ORGANIZATIONDATA where ID = @RECIPROCALCONSTITUENTID),'00000000-0000-0000-0000-000000000000');
    declare @OLDPARENTORGID uniqueidentifier
    if @ISPARENTRELATIONSHIP = 1 and @ISPARENTRECIPROCAL = 0 and @PARENTID <> @RECIPROCALCONSTITUENTID
    begin
        set @OLDPARENTORGID = (select PARENTCORPID from dbo.ORGANIZATIONDATA where ID=@RECIPROCALCONSTITUENTID)

        update
          dbo.ORGANIZATIONDATA
        set
          PARENTCORPID = @CONSTITUENTID,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where
          ID = @RECIPROCALCONSTITUENTID;

        if @@ROWCOUNT = 0
          insert into dbo.ORGANIZATIONDATA
          (
            ID,
            PARENTCORPID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
  )
          values
          (
            @RECIPROCALCONSTITUENTID,
            @CONSTITUENTID,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
          );

        exec dbo.USP_RELATIONSHIPS_CREATEPARENTORGRELATIONSHIP @RECIPROCALCONSTITUENTID, @CONSTITUENTID, @STARTDATE, @CHANGEAGENTID, @CURRENTDATE, 0, @OLDPARENTORGID, @STARTDATE;
    end

    if @ISPARENTRELATIONSHIP = 0 and @ISPARENTRECIPROCAL = 1 and @RECIPROCALPARENTID <> @CONSTITUENTID
    begin
        set @OLDPARENTORGID = (select PARENTCORPID from dbo.ORGANIZATIONDATA where ID=@CONSTITUENTID)

        update
          dbo.ORGANIZATIONDATA
        set
          PARENTCORPID = @RECIPROCALCONSTITUENTID,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
        where
          ID = @CONSTITUENTID;

        if @@ROWCOUNT = 0
          insert into dbo.ORGANIZATIONDATA
          (
            ID,
            PARENTCORPID,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
          )
          values
          (
            @CONSTITUENTID,
            @RECIPROCALCONSTITUENTID,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
          );

        exec dbo.USP_RELATIONSHIPS_CREATEPARENTORGRELATIONSHIP @CONSTITUENTID, @RECIPROCALCONSTITUENTID, @STARTDATE, @CHANGEAGENTID, @CURRENTDATE, 0, @OLDPARENTORGID, @STARTDATE;
    end

  end try

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

  return 0;