USP_DATAFORMTEMPLATE_EDIT_RELATIONSHIP_INDTOORG_2
The save procedure used by the edit dataform template "Relationship Individual to Organization Edit Form 2".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @RELATIONSHIPTYPECODEID | uniqueidentifier | IN | Relationship type |
| @RECIPROCALTYPECODEID | uniqueidentifier | IN | Reciprocal 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 | Position |
| @ISPRIMARYBUSINESS | bit | IN | This is the primary business |
| @ISMATCHINGGIFTRELATIONSHIP | bit | IN | The organization will match contributions for this relationship. |
| @RELATIONADDRESSID | uniqueidentifier | IN | Address ID |
| @ADDADDRESS | bit | IN | Add Address |
| @EDITADDRESS | bit | IN | Edit 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 | |
| @UPDATECONTACTS | bit | IN | Update contacts that use this address |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_RELATIONSHIP_INDTOORG_2
(
@ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@RELATIONSHIPTYPECODEID uniqueidentifier,
@RECIPROCALTYPECODEID uniqueidentifier,
@RELATIONSHIPSTARTDATE datetime,
@RELATIONSHIPENDDATE datetime,
@ISCONTACT bit,
@ISPRIMARYCONTACT bit,
@CONTACTTYPECODEID uniqueidentifier,
@POSITION nvarchar(50),
@ISPRIMARYBUSINESS bit,
@ISMATCHINGGIFTRELATIONSHIP bit,
@RELATIONADDRESSID uniqueidentifier,
@ADDADDRESS bit,
@EDITADDRESS bit,
@ADDRESSTYPECODEID uniqueidentifier,
@PRIMARY bit,
@DONOTMAIL bit,
@STARTDATE dbo.UDT_MONTHDAY,
@ENDDATE dbo.UDT_MONTHDAY,
@COUNTRYID uniqueidentifier,
@STATEID uniqueidentifier,
@ADDRESSBLOCK nvarchar(150),
@CITY nvarchar(50),
@POSTCODE nvarchar(12),
@CART nvarchar(10),
@DPC nvarchar(8),
@LOT nvarchar(5),
@UPDATEMATCHINGSPOUSEADDRESSES bit,
@OMITFROMVALIDATION bit,
@COUNTYCODEID uniqueidentifier,
@CONGRESSIONALDISTRICTCODEID uniqueidentifier,
@STATEHOUSEDISTRICTCODEID uniqueidentifier,
@STATESENATEDISTRICTCODEID uniqueidentifier,
@LOCALPRECINCTCODEID uniqueidentifier,
@INFOSOURCECODEID uniqueidentifier,
@REGIONCODEID uniqueidentifier,
@LASTVALIDATIONATTEMPTDATE datetime,
@VALIDATIONMESSAGE nvarchar(200),
@CERTIFICATIONDATA integer,
@UPDATECONTACTS bit
)
as
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
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
ID in (
select
TOUPDATE.ID
from
dbo.RELATIONSHIP TOUPDATE
left join
dbo.RELATIONSHIP
on
RELATIONSHIP.ID = @ID
where
TOUPDATE.RELATIONSHIPCONSTITUENTID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID and
TOUPDATE.RECIPROCALCONSTITUENTID = RELATIONSHIP.RECIPROCALCONSTITUENTID and
TOUPDATE.ISMATCHINGGIFTRELATIONSHIP = 1 and
TOUPDATE.ID <> RELATIONSHIP.ID)
update
DBO.RELATIONSHIP
set
RELATIONSHIPTYPECODEID = @RELATIONSHIPTYPECODEID,
RECIPROCALTYPECODEID = @RECIPROCALTYPECODEID,
STARTDATE = @RELATIONSHIPSTARTDATE,
ENDDATE = @RELATIONSHIPENDDATE,
ISCONTACT = @ISCONTACT,
ISPRIMARYCONTACT = @ISPRIMARYCONTACT,
POSITION = @POSITION,
CONTACTTYPECODEID = @CONTACTTYPECODEID,
ISPRIMARYBUSINESS = @ISPRIMARYBUSINESS,
ISMATCHINGGIFTRELATIONSHIP = @ISMATCHINGGIFTRELATIONSHIP,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = @ID;
declare @RECIPROCALRELATIONSHIPID uniqueidentifier
declare @CONSTITUENTID uniqueidentifier
select @RECIPROCALRELATIONSHIPID=r2.ID, @CONSTITUENTID=r1.RELATIONSHIPCONSTITUENTID
from RELATIONSHIP r1
inner join RELATIONSHIP r2 on (r1.RELATIONSHIPCONSTITUENTID=r2.RECIPROCALCONSTITUENTID and
r1.RECIPROCALCONSTITUENTID=r2.RELATIONSHIPCONSTITUENTID and
r1.RELATIONSHIPTYPECODEID=r2.RECIPROCALTYPECODEID and
r1.RECIPROCALTYPECODEID=r2.RELATIONSHIPTYPECODEID)
where
r1.ID=@ID;
--Are we editting the existing address?
if @EDITADDRESS = 1
begin
exec dbo.USP_ADDRESS_UPDATE @RELATIONADDRESSID, @CHANGEAGENTID, @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, @UPDATECONTACTS
end
else
begin
--If not, are we adding a new address?
if @ADDADDRESS = 1
begin
exec dbo.USP_ADDRESS_CREATE @RELATIONADDRESSID output, @CHANGEAGENTID, @CONSTITUENTID, @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
end
end
--Is there currently an address associated with this relationship?
if @RELATIONADDRESSID is not null
begin
--If so, set the address's RELATIONSHIPID.
update ADDRESS
set
RELATIONSHIPID = @RECIPROCALRELATIONSHIPID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID=@RELATIONADDRESSID;
--Delete the old address, if there was one and if it's not currently primary.
delete ADDRESS
where
ID<>@RELATIONADDRESSID and
RELATIONSHIPID = @RECIPROCALRELATIONSHIPID and
ISPRIMARY=0
end
else
begin
--If there isn't currently an address, delete all address that refer to this relationship that aren't primary.
delete ADDRESS
where
RELATIONSHIPID = @RECIPROCALRELATIONSHIPID and
ISPRIMARY=0
--Disassociate any remaining addresses.
update ADDRESS
set
RELATIONSHIPID=null,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
RELATIONSHIPID=@RECIPROCALRELATIONSHIPID;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;