USP_DATAFORMTEMPLATE_ADD_ORGTOIND_RELATIONSHIP_3
The save procedure used by the add dataform template "Relationship Organization to Individual Add Form 3".
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 constituent |
| @RECIPROCALTYPECODEID | uniqueidentifier | IN | Reciprocal relationship type |
| @RELATIONSHIPTYPECODEID | uniqueidentifier | IN | Relationship type |
| @RELATIONSHIPSTARTDATE | datetime | IN | Start date |
| @RELATIONSHIPENDDATE | 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(50) | IN | Job title |
| @ISPRIMARYBUSINESS | bit | IN | This is the primary business |
| @ISMATCHINGGIFTRELATIONSHIP | bit | IN | The organization will match contributions for this relationship |
| @ADDADDRESS | bit | IN | Add Address |
| @ADDRESSTYPECODEID | uniqueidentifier | IN | Type |
| @PRIMARY | bit | IN | Set as primary address |
| @DONOTMAIL | bit | IN | Do not send mail to this address |
| @STARTDATE | UDT_MONTHDAY | IN | Start date |
| @ENDDATE | UDT_MONTHDAY | IN | End date |
| @COUNTRYID | uniqueidentifier | IN | Country |
| @STATEID | uniqueidentifier | IN | State |
| @ADDRESSBLOCK | nvarchar(150) | IN | Address |
| @CITY | nvarchar(50) | IN | City |
| @POSTCODE | nvarchar(12) | IN | ZIP |
| @CART | nvarchar(10) | IN | CART |
| @DPC | nvarchar(8) | IN | DPC |
| @LOT | nvarchar(5) | IN | LOT |
| @UPDATEMATCHINGSPOUSEADDRESSES | bit | IN | Update matching address information for spouse |
| @OMITFROMVALIDATION | bit | IN | Omit this address from validation |
| @COUNTYCODEID | uniqueidentifier | IN | County |
| @CONGRESSIONALDISTRICTCODEID | uniqueidentifier | IN | Congressional district |
| @STATEHOUSEDISTRICTCODEID | uniqueidentifier | IN | State house district |
| @STATESENATEDISTRICTCODEID | uniqueidentifier | IN | State senate district |
| @LOCALPRECINCTCODEID | uniqueidentifier | IN | Local precinct |
| @INFOSOURCECODEID | uniqueidentifier | IN | Info source |
| @REGIONCODEID | uniqueidentifier | IN | Region |
| @LASTVALIDATIONATTEMPTDATE | datetime | IN | Last attempt |
| @VALIDATIONMESSAGE | nvarchar(200) | IN | Validation message |
| @CERTIFICATIONDATA | int | IN | |
| @DONOTMAILREASONCODEID | uniqueidentifier | IN | Reason |
| @KEYNAME | nvarchar(100) | IN | Last name |
| @FIRSTNAME | nvarchar(50) | IN | First name |
| @SKIP_ADDING_SECURITYGROUPS | bit | IN | Skip adding security groups |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @SKIP_ADDING_SITES | bit | IN | Skip adding sites |
| @JOBCATEGORYCODEID | uniqueidentifier | IN | Category |
| @CAREERLEVELCODEID | uniqueidentifier | IN | Career level |
| @REPLACERELATIONSHIP | bit | IN | This replaces the relationship with: |
| @REPLACEMENTRELATIONSHIPID | uniqueidentifier | IN | Replacement relationship |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_ORGTOIND_RELATIONSHIP_3
(
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@CONSTITUENTID uniqueidentifier,
@RECIPROCALCONSTITUENTID uniqueidentifier = null,
@RECIPROCALTYPECODEID uniqueidentifier,
@RELATIONSHIPTYPECODEID uniqueidentifier,
@RELATIONSHIPSTARTDATE datetime = null,
@RELATIONSHIPENDDATE datetime = null,
@ISCONTACT bit = 0,
@ISPRIMARYCONTACT bit = 0,
@CONTACTTYPECODEID uniqueidentifier = null,
@POSITION nvarchar(50) = '',
@ISPRIMARYBUSINESS bit = 0,
@ISMATCHINGGIFTRELATIONSHIP bit = 0,
@ADDADDRESS bit = 0,
@ADDRESSTYPECODEID uniqueidentifier = null,
@PRIMARY bit = 0,
@DONOTMAIL bit = 0,
@STARTDATE dbo.UDT_MONTHDAY = '0000',
@ENDDATE dbo.UDT_MONTHDAY = '0000',
@COUNTRYID uniqueidentifier = null,
@STATEID uniqueidentifier = null,
@ADDRESSBLOCK nvarchar(150) = '',
@CITY nvarchar(50) = '',
@POSTCODE nvarchar(12) = '',
@CART nvarchar(10) = '',
@DPC nvarchar(8) = '',
@LOT nvarchar(5) = '',
@UPDATEMATCHINGSPOUSEADDRESSES bit = 0,
@OMITFROMVALIDATION bit = 0,
@COUNTYCODEID uniqueidentifier = null,
@CONGRESSIONALDISTRICTCODEID uniqueidentifier = null,
@STATEHOUSEDISTRICTCODEID uniqueidentifier = null,
@STATESENATEDISTRICTCODEID uniqueidentifier = null,
@LOCALPRECINCTCODEID uniqueidentifier = null,
@INFOSOURCECODEID uniqueidentifier = null,
@REGIONCODEID uniqueidentifier = null,
@LASTVALIDATIONATTEMPTDATE datetime = null,
@VALIDATIONMESSAGE nvarchar(200) = '',
@CERTIFICATIONDATA integer = 0,
@DONOTMAILREASONCODEID uniqueidentifier = null,
@KEYNAME nvarchar(100),
@FIRSTNAME nvarchar(50) = null,
@SKIP_ADDING_SECURITYGROUPS bit = 0,
@CURRENTAPPUSERID uniqueidentifier,
@SKIP_ADDING_SITES bit = 0,
@JOBCATEGORYCODEID uniqueidentifier = null,
@CAREERLEVELCODEID uniqueidentifier = null,
@REPLACERELATIONSHIP bit = 0,
@REPLACEMENTRELATIONSHIPID uniqueidentifier = null
)
as
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @DONOTMAIL = 0
set @DONOTMAILREASONCODEID = null
begin try
if @RELATIONSHIPSTARTDATE > @CURRENTDATE
raiserror('BBERR_STARTDATEFUTURE', 13, 1)
if @REPLACERELATIONSHIP = 1 and @REPLACEMENTRELATIONSHIPID is not null
begin
--Check if start date was supplied for replace.
if @RELATIONSHIPSTARTDATE is null
raiserror('BBERR_STARTDATEREQUIRED', 13, 1)
update
dbo.RELATIONSHIP
set
ENDDATE = dateadd(d,-1, @RELATIONSHIPSTARTDATE),
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ID = @REPLACEMENTRELATIONSHIPID;
update
dbo.RELATIONSHIPJOBINFO
set
ENDDATE = dateadd(d,-1, @RELATIONSHIPSTARTDATE),
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
from dbo.RELATIONSHIPJOBINFO
inner join dbo.RELATIONSHIP
on RELATIONSHIPJOBINFO.RELATIONSHIPSETID = RELATIONSHIP.RELATIONSHIPSETID
where
RELATIONSHIP.ID = @REPLACEMENTRELATIONSHIPID
and
(RELATIONSHIPJOBINFO.ENDDATE is null
or
RELATIONSHIPJOBINFO.ENDDATE > @RELATIONSHIPSTARTDATE);
end
if @RECIPROCALCONSTITUENTID is null
begin
set @RECIPROCALCONSTITUENTID = newid();
insert into dbo.CONSTITUENT(ID,KEYNAME,FIRSTNAME,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
values (@RECIPROCALCONSTITUENTID,@KEYNAME,@FIRSTNAME,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE);
if coalesce(@SKIP_ADDING_SECURITYGROUPS,0) = 0
begin
exec dbo.USP_CONSTITUENT_ASSIGNSECURITYGROUPS_FORNEWRECORD
@APPUSERID = @CURRENTAPPUSERID,
@DATAFORMTEMPLATEID = 'AFB63308-8261-4EF1-8C53-9AA2F5F6822B',
@CONSTITUENTID = @RECIPROCALCONSTITUENTID,
@DATEADDEDTOUSE = @CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
if coalesce(@SKIP_ADDING_SITES,0) = 0
exec dbo.USP_CONSTITUENT_ASSIGNSITES_FORNEWRECORD
@APPUSERID = @CURRENTAPPUSERID,
@DATAFORMTEMPLATEID = 'AFB63308-8261-4EF1-8C53-9AA2F5F6822B',
@CONSTITUENTID = @RECIPROCALCONSTITUENTID,
@DATEADDEDTOUSE = @CURRENTDATE,
@CHANGEAGENTID = @CHANGEAGENTID;
end
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,
ISCONTACT,
ISPRIMARYCONTACT,
CONTACTTYPECODEID,
STARTDATE,
ENDDATE,
ISPRIMARYBUSINESS,
ISMATCHINGGIFTRELATIONSHIP,
RELATIONSHIPSETID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@ID,
@CONSTITUENTID,
@RECIPROCALCONSTITUENTID,
@RELATIONSHIPTYPECODEID,
@RECIPROCALTYPECODEID,
@ISCONTACT,
@ISPRIMARYCONTACT,
@CONTACTTYPECODEID,
@RELATIONSHIPSTARTDATE,
@RELATIONSHIPENDDATE,
@ISPRIMARYBUSINESS,
@ISMATCHINGGIFTRELATIONSHIP,
@SETID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
if @ADDADDRESS = 1
begin
declare @ADDRESSID uniqueidentifier
exec dbo.USP_ADDRESS_CREATE @ADDRESSID output, @CHANGEAGENTID, @RECIPROCALCONSTITUENTID, @ADDRESSTYPECODEID, @PRIMARY, @DONOTMAIL,
@STARTDATE, @ENDDATE, @COUNTRYID, @STATEID, @ADDRESSBLOCK, @CITY, @POSTCODE, @CART, @DPC, @LOT,
@UPDATEMATCHINGSPOUSEADDRESSES, @OMITFROMVALIDATION, @COUNTYCODEID, @CONGRESSIONALDISTRICTCODEID,
@STATEHOUSEDISTRICTCODEID, @STATESENATEDISTRICTCODEID, @LOCALPRECINCTCODEID, @INFOSOURCECODEID,
@REGIONCODEID, @LASTVALIDATIONATTEMPTDATE, @VALIDATIONMESSAGE, @CERTIFICATIONDATA, @DONOTMAILREASONCODEID
if @ADDRESSID is not null
begin
update ADDRESS
set
RELATIONSHIPID=@ID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID=@ADDRESSID
end
end
if (@JOBCATEGORYCODEID is not null) or
(@CAREERLEVELCODEID is not null) or
(len(@POSITION) > 0)
insert into dbo.RELATIONSHIPJOBINFO
(
RELATIONSHIPSETID,
JOBTITLE,
JOBCATEGORYCODEID,
CAREERLEVELCODEID,
STARTDATE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values
(
@SETID,
@POSITION,
@JOBCATEGORYCODEID,
@CAREERLEVELCODEID,
@RELATIONSHIPSTARTDATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
exec dbo.USP_RELATIONSHIPCONFIGURATION_CONFIGURE @CONSTITUENTID, @RECIPROCALCONSTITUENTID, @RELATIONSHIPTYPECODEID, @RECIPROCALTYPECODEID, @CHANGEAGENTID, @CURRENTDATE
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;