USP_DATAFORMTEMPLATE_VIEW_RELATIONSHIP
The load procedure used by the view dataform template "Relationship View 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 | Reciprocal constituent ID |
| @RELATIONSHIPCONSTITUENTID | uniqueidentifier | INOUT | Relationship constituent ID |
| @CONSTITUENTNAME | nvarchar(700) | INOUT | Constituent name |
| @RECIPROCALCONSTITUENTNAME | nvarchar(700) | INOUT | Related constituent |
| @RELATIONSHIPTYPECODE | nvarchar(100) | INOUT | Constituent is the |
| @RECIPROCALTYPECODE | nvarchar(100) | INOUT | Constituent is the |
| @STARTDATE | datetime | INOUT | Start date |
| @ENDDATE | datetime | INOUT | End date |
| @ISSPOUSE | bit | INOUT | Spouse |
| @PRIMARYRECOGNITIONRELATIONSHIPEXISTS | bit | INOUT | Apply to constituent from relationship |
| @PRIMARYRECOGNITIONTYPECODE | nvarchar(100) | INOUT | Recognition credit type |
| @PRIMARYRECOGNITIONMATCHFACTOR | decimal(5, 2) | INOUT | Recognition credit match percent |
| @RECIPROCALRECOGNITIONRELATIONSHIPEXISTS | bit | INOUT | Apply to relationship from constituent |
| @RECIPROCALRECOGNITIONTYPECODE | nvarchar(100) | INOUT | Recognition credit type |
| @RECIPROCALRECOGNITIONMATCHFACTOR | decimal(5, 2) | INOUT | Recognition credit match percent |
| @RECOGNITIONSSETTHROUGHMEMBERDEFAULTALLOTHERMEMBERS | bit | INOUT | Are recognition defaults set through household's members matching all other members |
| @ISCONTACT | bit | INOUT | Contact |
| @ISPRIMARYCONTACT | bit | INOUT | Primary contact |
| @CONTACTTYPECODE | nvarchar(100) | INOUT | Contact type |
| @HASJOBINFO | bit | INOUT | HASJOBINFO |
| @JOBTITLE | nvarchar(100) | INOUT | Job title |
| @CAREERLEVELCODE | nvarchar(100) | INOUT | Career level |
| @JOBCATEGORYCODE | nvarchar(100) | INOUT | Category |
| @INDUSTRYCODE | nvarchar(100) | INOUT | Industry |
| @JOBSTARTDATE | date | INOUT | Start date |
| @JOBENDDATE | date | INOUT | End date |
| @COMMENTS | nvarchar(max) | INOUT | Comments |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_RELATIONSHIP
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@RECIPROCALCONSTITUENTID uniqueidentifier = null output,
@RELATIONSHIPCONSTITUENTID uniqueidentifier = null output,
@CONSTITUENTNAME nvarchar(700) = null output,
@RECIPROCALCONSTITUENTNAME nvarchar(700) = null output,
@RELATIONSHIPTYPECODE nvarchar(100) = null output,
@RECIPROCALTYPECODE nvarchar(100) = null output,
@STARTDATE datetime = null output,
@ENDDATE datetime = null output,
--ind <> ind
@ISSPOUSE bit = null output,
@PRIMARYRECOGNITIONRELATIONSHIPEXISTS bit = null output,
@PRIMARYRECOGNITIONTYPECODE nvarchar(100) = null output,
@PRIMARYRECOGNITIONMATCHFACTOR decimal(5,2) = null output,
@RECIPROCALRECOGNITIONRELATIONSHIPEXISTS bit = null output,
@RECIPROCALRECOGNITIONTYPECODE nvarchar(100) = null output,
@RECIPROCALRECOGNITIONMATCHFACTOR decimal(5,2) = null output,
@RECOGNITIONSSETTHROUGHMEMBERDEFAULTALLOTHERMEMBERS bit = null output,
--ind <> org
@ISCONTACT bit = null output,
@ISPRIMARYCONTACT bit = null output,
@CONTACTTYPECODE nvarchar(100) = null output,
@HASJOBINFO bit = null output,
@JOBTITLE nvarchar(100) = null output,
@CAREERLEVELCODE nvarchar(100) = null output,
@JOBCATEGORYCODE nvarchar(100) = null output,
@INDUSTRYCODE nvarchar(100) = null output,
@JOBSTARTDATE date = null output,
@JOBENDDATE date = null output,
@COMMENTS nvarchar(max) = null output
)
as
set nocount on;
set @DATALOADED = 0;
declare @RELATIONSHIPSETID uniqueidentifier;
select
@DATALOADED = 1,
@RELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID,
@RECIPROCALCONSTITUENTID = RELATIONSHIP.RECIPROCALCONSTITUENTID,
@RELATIONSHIPCONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID,
@CONSTITUENTNAME = CONSTIT1_NF.NAME,
@RECIPROCALCONSTITUENTNAME = CONSTIT2_NF.NAME,
@RELATIONSHIPTYPECODE = dbo.UFN_RELATIONSHIPTYPECODE_GETDESCRIPTION(RELATIONSHIP.RELATIONSHIPTYPECODEID),
@RECIPROCALTYPECODE = dbo.UFN_RELATIONSHIPTYPECODE_GETDESCRIPTION(RELATIONSHIP.RECIPROCALTYPECODEID),
@STARTDATE = RELATIONSHIP.STARTDATE,
@ENDDATE = RELATIONSHIP.ENDDATE,
@ISSPOUSE = RELATIONSHIP.ISSPOUSE,
@ISCONTACT = RELATIONSHIP.ISCONTACT,
@ISPRIMARYCONTACT = RELATIONSHIP.ISPRIMARYCONTACT,
@CONTACTTYPECODE = dbo.UFN_CONTACTTYPECODE_GETDESCRIPTION(RELATIONSHIP.CONTACTTYPECODEID),
@COMMENTS = case when len(RELATIONSHIP.COMMENTS) > 256 then left(RELATIONSHIP.COMMENTS,256) + '...' else RELATIONSHIP.COMMENTS end
from
dbo.RELATIONSHIP
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIP.RELATIONSHIPCONSTITUENTID) CONSTIT1_NF
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATIONSHIP.RECIPROCALCONSTITUENTID) CONSTIT2_NF
where
RELATIONSHIP.ID = @ID;
set @PRIMARYRECOGNITIONRELATIONSHIPEXISTS = 0;
set @RECIPROCALRECOGNITIONRELATIONSHIPEXISTS = 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
set @PRIMARYRECOGNITIONRELATIONSHIPEXISTS = 1
set @RECIPROCALRECOGNITIONRELATIONSHIPEXISTS = 1
set @PRIMARYRECOGNITIONMATCHFACTOR = 100
set @RECIPROCALRECOGNITIONMATCHFACTOR = 100
set @RECOGNITIONSSETTHROUGHMEMBERDEFAULTALLOTHERMEMBERS = 1
end
end
if @RECOGNITIONSSETTHROUGHMEMBERDEFAULTALLOTHERMEMBERS is null
begin
select
@PRIMARYRECOGNITIONRELATIONSHIPEXISTS = 1,
@PRIMARYRECOGNITIONMATCHFACTOR = MATCHFACTOR,
@PRIMARYRECOGNITIONTYPECODE = dbo.UFN_REVENUERECOGNITIONTYPECODE_GETDESCRIPTION(REVENUERECOGNITIONTYPECODEID)
from
dbo.REVENUERECOGNITIONDEFAULT
where
SOURCECONSTITUENTID = @RELATIONSHIPCONSTITUENTID
and
RECIPIENTCONSTITUENTID = @RECIPROCALCONSTITUENTID
set @PRIMARYRECOGNITIONMATCHFACTOR = coalesce(@PRIMARYRECOGNITIONMATCHFACTOR, 100)
select
@RECIPROCALRECOGNITIONRELATIONSHIPEXISTS = 1,
@RECIPROCALRECOGNITIONMATCHFACTOR = MATCHFACTOR,
@RECIPROCALRECOGNITIONTYPECODE = dbo.UFN_REVENUERECOGNITIONTYPECODE_GETDESCRIPTION(REVENUERECOGNITIONTYPECODEID)
from
dbo.REVENUERECOGNITIONDEFAULT
where
SOURCECONSTITUENTID = @RECIPROCALCONSTITUENTID
and
RECIPIENTCONSTITUENTID = @RELATIONSHIPCONSTITUENTID
set @RECIPROCALRECOGNITIONMATCHFACTOR = coalesce(@RECIPROCALRECOGNITIONMATCHFACTOR, 100)
end
set @HASJOBINFO = 0;
select top 1
@HASJOBINFO = 1,
@JOBTITLE = JOBTITLE,
@CAREERLEVELCODE = dbo.UFN_CAREERLEVELCODE_GETDESCRIPTION(CAREERLEVELCODEID),
@JOBCATEGORYCODE = dbo.UFN_JOBCATEGORYCODE_GETDESCRIPTION(JOBCATEGORYCODEID),
@JOBSTARTDATE = STARTDATE,
@JOBENDDATE = ENDDATE
from RELATIONSHIPJOBINFO
where RELATIONSHIPSETID = @RELATIONSHIPSETID
order by RELATIONSHIPJOBINFO.STARTDATE desc;
--This field is only displayed for IND-ORG relations so just get the industry for either.
select
@INDUSTRYCODE = dbo.UFN_INDUSTRYCODE_GETDESCRIPTION(INDUSTRYCODEID)
from dbo.ORGANIZATIONDATA
where ID in (@RECIPROCALCONSTITUENTID, @RELATIONSHIPCONSTITUENTID);
return 0;