USP_BBNC_COMMITMGINFORMATION
Adds a matching gift company's information from a Blackbaud Internet Solutions donation transaction to the system from a given batch.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @BBNCMAPID | int | INOUT | |
| @KEYNAME | nvarchar(100) | IN | |
| @MATCHINGFACTOR | decimal(5, 2) | IN | |
| @MAXMATCHPERGIFT | money | IN | |
| @ADDRESS | nvarchar(300) | IN | |
| @CITY | nvarchar(100) | IN | |
| @STATEID | uniqueidentifier | IN | |
| @COUNTRYID | uniqueidentifier | IN | |
| @ZIP | nvarchar(20) | IN | |
| @PHONE | nvarchar(50) | IN | |
| @FAX | nvarchar(50) | IN | |
| @INDUSTRYCODEID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN | |
| @ALIAS | nvarchar(100) | IN | |
| @WEBADDRESS | nvarchar(2047) | IN | |
| @MINMATCHPERGIFT | money | IN | |
| @MAXMATCHANNUAL | money | IN | |
| @TOTALMATCH | money | IN | |
| @MATCHNOTES | nvarchar(max) | IN |
Definition
Copy
CREATE procedure dbo.USP_BBNC_COMMITMGINFORMATION
(
@ID uniqueidentifier = null output,
@BBNCMAPID int = null output,
@KEYNAME nvarchar(100) = '',
@MATCHINGFACTOR decimal(5, 2) = 0,
@MAXMATCHPERGIFT money = 0,
@ADDRESS nvarchar(300) = '',
@CITY nvarchar(100) = '',
@STATEID uniqueidentifier = null,
@COUNTRYID uniqueidentifier = null,
@ZIP nvarchar(20) = '',
@PHONE nvarchar(50) = '',
@FAX nvarchar(50) = '',
@INDUSTRYCODEID uniqueidentifier = null,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null,
@ALIAS nvarchar(100) = null,
@WEBADDRESS nvarchar(2047) = null,
@MINMATCHPERGIFT money = null,
@MAXMATCHANNUAL money = null,
@TOTALMATCH money = null,
@MATCHNOTES nvarchar(max) = null
)
as
set nocount on;
declare @KEYNAMEBODY nvarchar(100);
declare @KEYNAMEPREFIX nvarchar(50);
declare @MGTYPECODEID uniqueidentifier;
declare @ADDRESSTYPECODEID uniqueidentifier;
declare @PHONETYPECODEID uniqueidentifier;
declare @FAXTYPECODEID uniqueidentifier;
declare @ALIASTYPECODEID uniqueidentifier;
declare @INFOSOURCECODEID uniqueidentifier;
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
exec dbo.USP_PARSE_ORGANIZATION_NAME @KEYNAME, @KEYNAMEBODY output, @KEYNAMEPREFIX output;
begin try
select
@MGTYPECODEID = MGCONDITIONTYPECODEID,
@ADDRESSTYPECODEID = BUSINESSADDRESSTYPECODEID,
@PHONETYPECODEID = BUSINESSPHONECODEID,
@FAXTYPECODEID = FAXPHONECODEID,
@ALIASTYPECODEID = MGALIASTYPECODEID,
@INFOSOURCECODEID = INFOSOURCECODEID
from dbo.NETCOMMUNITYDEFAULTCODEMAP;
if (select count(ID) from dbo.CONSTITUENT where ID = @ID) > 0
begin
--CONSTITUENT record already exists
update dbo.CONSTITUENT
set KEYNAME = @KEYNAMEBODY,
KEYNAMEPREFIX = @KEYNAMEPREFIX,
ISORGANIZATION = 1,
WEBADDRESS = coalesce(@WEBADDRESS, N''),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = @ID;
--Cache CONTEXT INFO
declare @contextCache varbinary(128);
set @contextCache = CONTEXT_INFO();
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
delete from ALIAS where CONSTITUENTID = @ID and ALIASTYPECODEID = @ALIASTYPECODEID;
--Restore CONTEXT INFO
if not @contextCache is null
set CONTEXT_INFO @contextCache;
if not @ALIAS is null and len(@ALIAS) > 0
begin
insert into dbo.ALIAS(ID, CONSTITUENTID, ALIASTYPECODEID, KEYNAME, FIRSTNAME, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(newid(), @ID, @ALIASTYPECODEID, @ALIAS, N'', @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
end
if not @INDUSTRYCODEID is null
begin
if (select count(ID) from dbo.ORGANIZATIONDATA where ID = @ID) > 0
begin
--Update org data
update dbo.ORGANIZATIONDATA
set INDUSTRYCODEID = @INDUSTRYCODEID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = @ID;
end
else
begin
--Create new org data
insert into dbo.ORGANIZATIONDATA(ID, INDUSTRYCODEID, NUMEMPLOYEES, NUMSUBSIDIARIES, PARENTCORPID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(@ID, @INDUSTRYCODEID, 0, 0, null, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
end
end
if len(@ADDRESS) > 0 or len(@CITY) > 0 or len(@ZIP) > 0 or not @STATEID is null or not @COUNTRYID is null
begin
--Create or update the address
declare @ADDRESSID uniqueidentifier;
select top (1)
@ADDRESSID = ID
from
dbo.ADDRESS
where
CONSTITUENTID = @ID and ADDRESSTYPECODEID = @ADDRESSTYPECODEID
order by
ISPRIMARY, SEQUENCE;
if @ADDRESSID is not null
begin
--Address already exists
update dbo.ADDRESS
set ADDRESSBLOCK = @ADDRESS,
CITY = @CITY,
STATEID = @STATEID,
POSTCODE = @ZIP,
COUNTRYID = @COUNTRYID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = @ADDRESSID;
end
else
begin
declare @ADDRESSISPRIMARY bit;
declare @ADDRESSSEQUENCE int;
select
@ADDRESSISPRIMARY = case when count(ADDRESS.ID) = 0 then 1 else 0 end,
@ADDRESSSEQUENCE = coalesce(max(ADDRESS.SEQUENCE), 0) + 1
from
dbo.ADDRESS
where
CONSTITUENTID = @ID;
--Address does not exist
insert into dbo.ADDRESS(ID, CONSTITUENTID, ADDRESSTYPECODEID, ISPRIMARY, COUNTRYID, STATEID, ADDRESSBLOCK, CITY, POSTCODE, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(newid(), @ID, @ADDRESSTYPECODEID, @ADDRESSISPRIMARY, @COUNTRYID, @STATEID, @ADDRESS, @CITY, @ZIP, @ADDRESSSEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
end
end
declare @PHONEISPRIMARY bit;
declare @PHONESEQUENCE int;
if len(@PHONE) > 0
begin
--Create or update the phone
declare @PHONEID uniqueidentifier;
select top (1)
@PHONEID = ID
from
dbo.PHONE
where
CONSTITUENTID = @ID and PHONETYPECODEID = @PHONETYPECODEID
order by
ISPRIMARY, SEQUENCE;
if @PHONEID is not null
begin
--Phone already exists
update dbo.PHONE
set NUMBER = @PHONE,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = @PHONEID;
end
else
begin
--Phone does not exist
select
@PHONEISPRIMARY = case when count(PHONE.ID) = 0 then 1 else 0 end,
@PHONESEQUENCE = coalesce(max(PHONE.SEQUENCE),0) + 1
from
dbo.PHONE
where
CONSTITUENTID = @ID;
insert into dbo.PHONE(ID, CONSTITUENTID, PHONETYPECODEID, ISPRIMARY, NUMBER, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(newid(), @ID, @PHONETYPECODEID, @PHONEISPRIMARY, @PHONE, @PHONESEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
end
end
if len(@FAX) > 0
begin
--Create or update the fax
declare @FAXID uniqueidentifier;
select top (1)
@FAXID = ID
from
dbo.PHONE
where
CONSTITUENTID = @ID and PHONETYPECODEID = @FAXTYPECODEID
order by
ISPRIMARY, SEQUENCE;
if @FAXID is not null
begin
--Phone already exists
update dbo.PHONE
set NUMBER = @FAX,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID = @FAXID;
end
else
begin
--Fax does not exist
select
@PHONEISPRIMARY = case when count(PHONE.ID) = 0 then 1 else 0 end,
@PHONESEQUENCE = coalesce(max(PHONE.SEQUENCE),0) + 1
from
dbo.PHONE
where
CONSTITUENTID = @ID;
insert into dbo.PHONE(ID, CONSTITUENTID, PHONETYPECODEID, ISPRIMARY, NUMBER, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(newid(), @ID, @FAXTYPECODEID, @PHONEISPRIMARY, @FAX, @PHONESEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
end
end
end
else
begin
--CONSTITUENT record does not exist
insert into dbo.CONSTITUENT(ID, KEYNAME, KEYNAMEPREFIX, ISORGANIZATION, WEBADDRESS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(@ID, @KEYNAMEBODY, @KEYNAMEPREFIX, 1, coalesce(@WEBADDRESS, N''), @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
if @INFOSOURCECODEID is not null
begin
exec dbo.USP_DATAFORMTEMPLATE_EDIT_CONSTITUENTORIGIN @ID = @ID,
@CHANGEAGENTID=@CHANGEAGENTID,
@INFOSOURCECODEID = @INFOSOURCECODEID,
@REVENUEID = null
end
if not @ALIAS is null and len(@ALIAS) > 0
insert into dbo.ALIAS(ID, CONSTITUENTID, ALIASTYPECODEID, KEYNAME, FIRSTNAME, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(newid(), @ID, @ALIASTYPECODEID, @ALIAS, N'', @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
if not @INDUSTRYCODEID is null
insert into dbo.ORGANIZATIONDATA(ID, INDUSTRYCODEID, NUMEMPLOYEES, NUMSUBSIDIARIES, PARENTCORPID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(@ID, @INDUSTRYCODEID, 0, 0, null, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
if len(@ADDRESS) > 0 or len(@CITY) > 0 or len(@ZIP) > 0 or not @STATEID is null or not @COUNTRYID is null
insert into dbo.ADDRESS(ID, CONSTITUENTID, ADDRESSTYPECODEID, ISPRIMARY, COUNTRYID, STATEID, ADDRESSBLOCK, CITY, POSTCODE, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(newid(), @ID, @ADDRESSTYPECODEID, 1, @COUNTRYID, @STATEID, @ADDRESS, @CITY, @ZIP, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
if len(@PHONE) > 0
insert into dbo.PHONE(ID, CONSTITUENTID, PHONETYPECODEID, ISPRIMARY, NUMBER, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(newid(), @ID, @PHONETYPECODEID, 1, @PHONE, 1, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
if len(@FAX) > 0
insert into dbo.PHONE(ID, CONSTITUENTID, PHONETYPECODEID, ISPRIMARY, NUMBER, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select newid(), @ID, @FAXTYPECODEID, case when len(@PHONE) > 0 then 0 else 1 end, @FAX, case when len(@PHONE) > 0 then 2 else 1 end, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE;
end
select @BBNCMAPID = SEQUENCEID from dbo.CONSTITUENT where ID = @ID;
if (select count(ID) from dbo.MATCHINGGIFTCONDITION where ORGANIZATIONID = @ID and MATCHINGGIFTCONDITIONTYPECODEID = @MGTYPECODEID) > 0
begin
--MATCHINGGIFTCONDITION record already exists
update dbo.MATCHINGGIFTCONDITION
set MATCHINGFACTOR = @MATCHINGFACTOR,
MAXMATCHPERGIFT = @MAXMATCHPERGIFT,
MINMATCHPERGIFT = coalesce(@MINMATCHPERGIFT, 0),
MAXMATCHANNUAL = coalesce(@MAXMATCHANNUAL, 0),
MAXMATCHTOTAL = coalesce(@TOTALMATCH, 0),
NOTES = coalesce(@MATCHNOTES, N''),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ORGANIZATIONID = @ID and MATCHINGGIFTCONDITIONTYPECODEID = @MGTYPECODEID;
end
else
begin
--MATCHINGGIFTCONDITION record does not exist
insert into dbo.MATCHINGGIFTCONDITION(ID, ORGANIZATIONID, MATCHINGGIFTCONDITIONTYPECODEID, MATCHINGFACTOR, MAXMATCHPERGIFT, MINMATCHPERGIFT, MAXMATCHANNUAL, MAXMATCHTOTAL, NOTES, [SEQUENCE], ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values(newid(), @ID, @MGTYPECODEID, @MATCHINGFACTOR, @MAXMATCHPERGIFT, coalesce(@MINMATCHPERGIFT, 0), coalesce(@MAXMATCHANNUAL, 0), coalesce(@TOTALMATCH, 0), coalesce(@MATCHNOTES, N''), coalesce((select max(SEQUENCE) from dbo.MATCHINGGIFTCONDITION where ORGANIZATIONID = @ID) + 1, 0), @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;