USP_DATAFORMTEMPLATE_EDITLOAD_RELATIONSHIP_INDTOIND

The load procedure used by the edit dataform template "Relationship Individual to Individual 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.
@RECIPROCALCONSTITUENTID	uniqueidentifier	INOUT	Related constituent
@RELATIONSHIPCONSTITUENTID	uniqueidentifier	INOUT
@RELATIONSHIPTYPECODEID	uniqueidentifier	INOUT	Relationship type
@RECIPROCALTYPECODEID	uniqueidentifier	INOUT	Reciprocal relationship type
@CONSTITUENTNAME	nvarchar(154)	INOUT
@RECIPROCALCONSTITUENTNAME	nvarchar(154)	INOUT
@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
@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.

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_RELATIONSHIP_INDTOIND
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @RECIPROCALCONSTITUENTID uniqueidentifier = null output,
  @RELATIONSHIPCONSTITUENTID uniqueidentifier = null output,                        
  @RELATIONSHIPTYPECODEID uniqueidentifier = null output,
  @RECIPROCALTYPECODEID uniqueidentifier = null output,
  @CONSTITUENTNAME nvarchar(154) = null output,
  @RECIPROCALCONSTITUENTNAME nvarchar(154) = 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,
  @TSLONG bigint = 0 output
)
as
  set nocount on;

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

  select 
    @DATALOADED = 1,
    @CONSTITUENTNAME = CONSTIT1.NAME,
    @RECIPROCALCONSTITUENTNAME = CONSTIT2.NAME,
    @RECIPROCALCONSTITUENTID = RECIPROCALCONSTITUENTID,
    @RELATIONSHIPCONSTITUENTID = RELATIONSHIPCONSTITUENTID,
    @RELATIONSHIPTYPECODEID = RELATIONSHIPTYPECODEID,
    @RECIPROCALTYPECODEID = RECIPROCALTYPECODEID,                             
    @STARTDATE = STARTDATE,
    @ENDDATE = ENDDATE,
    @ISSPOUSE = ISSPOUSE,
    @TSLONG = RELATIONSHIP.TSLONG
  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
  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

  select
    @PRIMARYSOFTCREDITRELATIONSHIPEXISTS = 1,
    @PRIMARYSOFTCREDITMATCHFACTOR = MATCHFACTOR
  from dbo.REVENUERECOGNITIONDEFAULT
  where
    SOURCECONSTITUENTID = @RELATIONSHIPCONSTITUENTID and
    RECIPIENTCONSTITUENTID = @RECIPROCALCONSTITUENTID and
    PREVENTRECOGNITIONSDEFAULTING = 0

  set @PRIMARYSOFTCREDITMATCHFACTOR = coalesce(@PRIMARYSOFTCREDITMATCHFACTOR, 100)

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

  set @RECIPROCALSOFTCREDITMATCHFACTOR = coalesce(@RECIPROCALSOFTCREDITMATCHFACTOR, 100)

  return 0;