USP_DATAFORMTEMPLATE_EDITLOAD_RELATIONSHIP_INDTOIND_3

The load procedure used by the edit dataform template "Relationship Individual to Individual Edit Form 3"

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.
@RECIPROCALCONSTITUENTID	uniqueidentifier	INOUT	Related constituent
@RELATIONSHIPCONSTITUENTID	uniqueidentifier	INOUT
@RELATIONSHIPTYPECODEID	uniqueidentifier	INOUT	Relationship type
@RECIPROCALTYPECODEID	uniqueidentifier	INOUT	Reciprocal relationship type
@CONSTITUENTNAME	nvarchar(700)	INOUT
@RECIPROCALCONSTITUENTNAME	nvarchar(700)	INOUT	Related constituent
@STARTDATE	datetime	INOUT	Start date
@ENDDATE	datetime	INOUT	End date
@ISSPOUSE	bit	INOUT	Is spouse?
@PRIMARYSOFTCREDITRELATIONSHIPEXISTS	bit	INOUT	Apply to <Constituent> for revenue from <Reciprocal>
@PRIMARYSOFTCREDITMATCHFACTOR	decimal(5, 2)	INOUT	Recognition credit match percent
@RECIPROCALSOFTCREDITRELATIONSHIPEXISTS	bit	INOUT	Apply to <Reciprocal> for revenue from <Constituent>
@RECIPROCALSOFTCREDITMATCHFACTOR	decimal(5, 2)	INOUT	Recognition credit match percent
@PRIMARYRECOGNITIONTYPECODEID	uniqueidentifier	INOUT	Recognition credit type
@RECIPROCALRECOGNITIONTYPECODEID	uniqueidentifier	INOUT	Recognition credit type
@RECOGNITIONSSETTHROUGHMEMBERDEFAULTALLOTHERMEMBERS	bit	INOUT	Are recognition defaults set through household's members matching all other members
@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.
@COMMENTS	nvarchar(max)	INOUT	Comments
@CONSTITUENTTYPE	int	INOUT
@PRIMARYISCONSTITUENT	bit	INOUT
@MEMBERSOFSAMEHOUSEHOLD	bit	INOUT	These individuals are members of the same household
@COPYCONTACTINFO	bit	INOUT	Copy primary contact information
@SKIP_ADDING_SECURITYGROUPS	bit	INOUT	Skip adding security groups
@IS_NEW_SPOUSE	bit	INOUT

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RELATIONSHIP_INDTOIND_3
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @RECIPROCALCONSTITUENTID uniqueidentifier = null output,
  @RELATIONSHIPCONSTITUENTID uniqueidentifier = null output,                        
  @RELATIONSHIPTYPECODEID uniqueidentifier = null output,
  @RECIPROCALTYPECODEID uniqueidentifier = null output,
  @CONSTITUENTNAME nvarchar(700) = null output,
  @RECIPROCALCONSTITUENTNAME nvarchar(700) = null output,                                            
  @STARTDATE datetime = null output,
  @ENDDATE datetime = null output,
  @ISSPOUSE bit = null output,
  @PRIMARYSOFTCREDITRELATIONSHIPEXISTS bit = null output,
  @PRIMARYSOFTCREDITMATCHFACTOR decimal(5,2) = null output,
  @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS bit = null output,                        
  @RECIPROCALSOFTCREDITMATCHFACTOR decimal(5,2) = null output,
  @PRIMARYRECOGNITIONTYPECODEID uniqueidentifier = null output,
  @RECIPROCALRECOGNITIONTYPECODEID uniqueidentifier = null output,
  @RECOGNITIONSSETTHROUGHMEMBERDEFAULTALLOTHERMEMBERS bit = null output,
  @TSLONG bigint = 0 output,
  @COMMENTS nvarchar(max) = null output,
  @CONSTITUENTTYPE int = null output,
  @PRIMARYISCONSTITUENT bit = null output,
  @MEMBERSOFSAMEHOUSEHOLD bit = null output,
  @COPYCONTACTINFO bit = null output,
  @SKIP_ADDING_SECURITYGROUPS bit = null output,
  @IS_NEW_SPOUSE bit = null output
)
as
  set nocount on;

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

  set @MEMBERSOFSAMEHOUSEHOLD = 0;
  set @COPYCONTACTINFO = 0;
  set @SKIP_ADDING_SECURITYGROUPS = 0;

  select 
    @DATALOADED = 1,
    @CONSTITUENTNAME = CONSTIT1_NF.NAME,
    @RECIPROCALCONSTITUENTNAME = CONSTIT2_NF.NAME,
    @RECIPROCALCONSTITUENTID = RECIPROCALCONSTITUENTID,
    @RELATIONSHIPCONSTITUENTID = RELATIONSHIPCONSTITUENTID,
    @RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODEID,
    @RECIPROCALTYPECODEID = RECIPROCALTYPECODEID,                             
    @STARTDATE = STARTDATE,
    @ENDDATE = ENDDATE,
    @ISSPOUSE = ISSPOUSE,
    @TSLONG = RELATIONSHIP.TSLONG,
    @COMMENTS = RELATIONSHIP.COMMENTS,
    @PRIMARYISCONSTITUENT = CONSTIT1.ISCONSTITUENT
  from dbo.RELATIONSHIP 
  left join dbo.CONSTITUENT as CONSTIT1 on RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTIT1.ID
  left join dbo.CONSTITUENT as CONSTIT2 on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONSTIT2.ID
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTIT1.ID) CONSTIT1_NF
  outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTIT2.ID) CONSTIT2_NF

  where 
    RELATIONSHIP.ID = @ID;

  set @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 0;
  set @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 0;

  -- If the two constituents are members of the same household and that same household recognizes all members

  -- for any member's revenue, then the two both have revenue defaults setup and the matchfactor is 100%

  declare @HOUSEHOLDID uniqueidentifier
  select 
    @HOUSEHOLDID = GM1.GROUPID
  from dbo.GROUPMEMBER GM1
  inner join dbo.GROUPMEMBER GM2 on GM1.GROUPID = GM2.GROUPID
  inner join dbo.GROUPDATA GD on GM1.GROUPID = GD.ID
  where
    GD.GROUPTYPECODE = 0 and
    GM1.MEMBERID = @RELATIONSHIPCONSTITUENTID and
    GM2.MEMBERID = @RECIPROCALCONSTITUENTID

  if @HOUSEHOLDID is not null
  begin
    declare @MEMBERRECOGNIZEOTHERMEMBERSCODE tinyint
    set @MEMBERRECOGNIZEOTHERMEMBERSCODE = dbo.UFN_HOUSEHOLD_GETMEMBERRECOGNIZEOTHERMEMBERSSETTING(@HOUSEHOLDID)

    if @MEMBERRECOGNIZEOTHERMEMBERSCODE = 1
    begin

        select
          @PRIMARYSOFTCREDITMATCHFACTOR = MATCHFACTOR,
          @PRIMARYRECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
        from dbo.UFN_CONSTITUENT_GETRECOGNITIONDEFAULTSBYSOURCE(@RELATIONSHIPCONSTITUENTID, 0, 1) RECDEF
        where RECDEF.RECIPIENTCONSTITUENTID = @RECIPROCALCONSTITUENTID

        select
  @RECIPROCALSOFTCREDITMATCHFACTOR = MATCHFACTOR,
          @RECIPROCALRECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
        from dbo.UFN_CONSTITUENT_GETRECOGNITIONDEFAULTSBYSOURCE(@RECIPROCALCONSTITUENTID, 0, 1) RECDEF
        where RECDEF.RECIPIENTCONSTITUENTID = @RELATIONSHIPCONSTITUENTID

      set @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1
      set @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1
      set @RECOGNITIONSSETTHROUGHMEMBERDEFAULTALLOTHERMEMBERS = 1
    end
  end

  if @RECOGNITIONSSETTHROUGHMEMBERDEFAULTALLOTHERMEMBERS is null
  begin
    select
      @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
      @PRIMARYSOFTCREDITMATCHFACTOR = MATCHFACTOR,
      @PRIMARYRECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
    from dbo.REVENUERECOGNITIONDEFAULT
    where
      SOURCECONSTITUENTID = @RELATIONSHIPCONSTITUENTID and
      RECIPIENTCONSTITUENTID = @RECIPROCALCONSTITUENTID and
      PREVENTRECOGNITIONSDEFAULTING = 0

    set @PRIMARYSOFTCREDITMATCHFACTOR = coalesce(@PRIMARYSOFTCREDITMATCHFACTOR, 100)

    select
      @RECIPROCALSOFTCREDITRELATIONSHIPEXISTS = 1,
      @RECIPROCALSOFTCREDITMATCHFACTOR = MATCHFACTOR,
      @RECIPROCALRECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODEID
    from dbo.REVENUERECOGNITIONDEFAULT
    where
      SOURCECONSTITUENTID = @RECIPROCALCONSTITUENTID and
      RECIPIENTCONSTITUENTID = @RELATIONSHIPCONSTITUENTID and
      PREVENTRECOGNITIONSDEFAULTING = 0

    set @RECIPROCALSOFTCREDITMATCHFACTOR = coalesce(@RECIPROCALSOFTCREDITMATCHFACTOR, 100)
  end

  return 0;