USP_WPPHILANTHROPICGIFT_ADD
Adds WealthPoint Philanthropic Gift information for a prospect.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @WEALTHID | uniqueidentifier | IN | |
| @SOURCE | nvarchar(100) | IN | |
| @WMID | int | IN | |
| @REVISION | int | IN | |
| @ORIGINALID | int | IN | |
| @NEWROW | bit | IN | |
| @CDATE | datetime | IN | |
| @CVALUE | money | IN | |
| @CNOTES | nvarchar(1024) | IN | |
| @FULLHASH | nvarchar(32) | IN | |
| @PARTIALHASH | nvarchar(32) | IN | |
| @MC | nvarchar(3) | IN | |
| @ORGANIZATION | nvarchar(200) | IN | |
| @LOCATION | nvarchar(100) | IN | |
| @GIFTYEAR | UDT_YEAR | IN | |
| @GIFTRANGE | nvarchar(50) | IN | |
| @TYPE | nvarchar(75) | IN | |
| @CATEGORY | nvarchar(500) | IN | |
| @LO | money | IN | |
| @HI | money | IN | |
| @NAME | nvarchar(200) | IN | |
| @ORGANIZATIONWEBADDRESS | nvarchar(2000) | IN | |
| @SOURCEMATERIAL | nvarchar(2000) | IN | |
| @GIFTYEARHI | UDT_YEAR | IN | |
| @GIFTYEARLO | UDT_YEAR | IN | |
| @TYPECODE | int | IN | |
| @EIN | nvarchar(50) | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @RECURSIVEMATCH | bit | IN | |
| @MATCHHASH | nvarchar(32) | IN | |
| @PHILANTHROPICGIFTID | int | IN | |
| @HOUSEHOLDID | nvarchar(30) | IN | |
| @TASCORE | int | IN | |
| @MATCHADDRESS | nvarchar(100) | IN | |
| @MATCHCITY | nvarchar(20) | IN | |
| @MATCHSTATE | nvarchar(2) | IN | |
| @MATCHZIP | nvarchar(5) | IN | |
| @CATEGORYLIST | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_WPPHILANTHROPICGIFT_ADD (
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@WEALTHID uniqueidentifier,
@SOURCE nvarchar(100),
@WMID int = 0,
@REVISION int = 0,
@ORIGINALID int = 0,
@NEWROW bit = 0,
@CDATE datetime = null,
@CVALUE money = 0,
@CNOTES nvarchar(1024) = '',
@FULLHASH nvarchar(32) = '',
@PARTIALHASH nvarchar(32) = '',
@MC nvarchar(3) = '',
@ORGANIZATION nvarchar(200) = '',
@LOCATION nvarchar(100) = '',
@GIFTYEAR dbo.UDT_YEAR = 0,
@GIFTRANGE nvarchar(50) = '',
@TYPE nvarchar(75) = '',
@CATEGORY nvarchar(500) = '',
@LO money = 0,
@HI money = 0,
@NAME nvarchar(200) = '',
@ORGANIZATIONWEBADDRESS nvarchar(2000) = '',
@SOURCEMATERIAL nvarchar(2000) = '',
@GIFTYEARHI dbo.UDT_YEAR = 0,
@GIFTYEARLO dbo.UDT_YEAR = 0,
@TYPECODE int = 0,
@EIN nvarchar(50) = '',
@CURRENTAPPUSERID uniqueidentifier = null,
@RECURSIVEMATCH bit = 0,
@MATCHHASH nvarchar(32) = '',
@PHILANTHROPICGIFTID int = 0,
@HOUSEHOLDID nvarchar(30) = '',
@TASCORE int = 0,
@MATCHADDRESS nvarchar(100) = '',
@MATCHCITY nvarchar(20) = '',
@MATCHSTATE nvarchar(2) = '',
@MATCHZIP nvarchar(5) = '',
@CATEGORYLIST xml = null
) as begin
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
--Get any existing ID
select
@ID = ID
from
dbo.WPPHILANTHROPICGIFT
where
WEALTHID=@WEALTHID and MATCHHASH = @MATCHHASH;
declare @CONFIRMED bit;
declare @CONFIRMEDBYAPPUSERID uniqueidentifier;
declare @DATECONFIRMED datetime;
declare @REJECTED bit;
declare @REJECTEDBYAPPUSERID uniqueidentifier;
declare @DATEREJECTED datetime;
set @CONFIRMED = 0;
set @REJECTED = 0;
if @CURRENTAPPUSERID is not null
begin
declare @CONFIDENCE int
select
@CONFIDENCE = coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE)
from
dbo.MATCHCODE MC
left outer join
dbo.WEALTHSOURCE WS on @SOURCE = WS.SOURCE
left outer join
dbo.CONFIDENCERATING CR on CR.MATCHCODEID = MC.ID and CR.WEALTHSOURCEID = WS.ID
where
MC.MATCHCODE = @MC
select
@REJECTED = case @CONFIDENCE
when 5 then 0
when 0 then 1
end,
@DATEREJECTED = case @CONFIDENCE
when 5 then NULL
when 0 then @CURRENTDATE
end,
@REJECTEDBYAPPUSERID = case @CONFIDENCE
when 5 then NULL
when 0 then @CURRENTAPPUSERID
end,
@CONFIRMED = case @CONFIDENCE
when 5 then 1
when 0 then 0
end,
@DATECONFIRMED = case @CONFIDENCE
when 5 then @CURRENTDATE
when 0 then NULL
end,
@CONFIRMEDBYAPPUSERID = case @CONFIDENCE
when 5 then @CURRENTAPPUSERID
when 0 then NULL
end
where
@CONFIDENCE in (0,5);
end
-- Record exists
if @ID is not null
begin
if not exists(select 1 from dbo.WPPHILANTHROPICGIFT where WEALTHID=@WEALTHID and FULLHASH=@FULLHASH)
begin
--Record exists but needs to be updated
update
dbo.WPPHILANTHROPICGIFT
set
MC = @MC,
FULLHASH = @FULLHASH,
RECURSIVEMATCH = @RECURSIVEMATCH,
PHILANTHROPICGIFTID = @PHILANTHROPICGIFTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
CONFIRMED = case when @CONFIRMED = 1 and CONFIRMED = 0 and REJECTED = 0 then 1 else CONFIRMED end,
CONFIRMEDBYAPPUSERID = case when @CONFIRMED = 1 and CONFIRMED = 0 and REJECTED = 0 then @CONFIRMEDBYAPPUSERID else CONFIRMEDBYAPPUSERID end,
DATECONFIRMED = case when @CONFIRMED = 1 and CONFIRMED = 0 and REJECTED = 0 then @DATECONFIRMED else DATECONFIRMED end
where
WEALTHID=@WEALTHID and MATCHHASH = @MATCHHASH;
--Record exists but address needs to be updated
--This should be rare so just using a separate update
--The only case this will likely occur is during the upgrade period from 2.9 to 2.91
if len(coalesce(@MATCHADDRESS, '')) > 0 or
len(coalesce(@MATCHCITY, '')) > 0 or
len(coalesce(@MATCHSTATE, '')) > 0 or
len(coalesce(@MATCHZIP, '')) > 0
begin
update
dbo.WPPHILANTHROPICGIFT
set
MATCHADDRESS = coalesce(@MATCHADDRESS, ''),
MATCHCITY = coalesce(@MATCHCITY, ''),
MATCHSTATE = coalesce(@MATCHSTATE, ''),
MATCHZIP = coalesce(@MATCHZIP, ''),
HOUSEHOLDID = coalesce(@HOUSEHOLDID, ''),
TASCORE = coalesce(@TASCORE, 0),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
WEALTHID=@WEALTHID and
MATCHHASH = @MATCHHASH and
MATCHADDRESS = '' and
MATCHCITY = '' and
MATCHSTATE = '' and
MATCHZIP = '';
end
end
end
else
begin
--Record is new and needs to be Added
if @ID is null
set @ID = newid();
insert into dbo.WPPHILANTHROPICGIFT (
ID,
WEALTHID,
SOURCE,
WMID,
REVISION,
ORIGINALID,
NEWROW,
CDATE,
CVALUE,
CNOTES,
FULLHASH,
PARTIALHASH,
MC,
ORGANIZATION,
LOCATION,
GIFTYEAR,
GIFTRANGE,
TYPE,
CATEGORY,
LO,
HI,
NAME,
ORGANIZATIONWEBADDRESS,
SOURCEMATERIAL,
GIFTYEARHI,
GIFTYEARLO,
TYPECODE,
EIN,
MATCHHASH,
PHILANTHROPICGIFTID,
CHANGEDBYID,
ADDEDBYID,
DATEADDED,
DATECHANGED,
CONFIRMED,
CONFIRMEDBYAPPUSERID,
DATECONFIRMED,
REJECTED,
REJECTEDBYAPPUSERID,
DATEREJECTED,
RECURSIVEMATCH,
HOUSEHOLDID,
TASCORE,
MATCHADDRESS,
MATCHCITY,
MATCHSTATE,
MATCHZIP
) values (
@ID,
@WEALTHID,
coalesce(@SOURCE, ''),
coalesce(@WMID, 0),
coalesce(@REVISION, 0),
coalesce(@ORIGINALID, 0),
coalesce(@NEWROW, 0),
@CDATE,
coalesce(@CVALUE, 0),
coalesce(@CNOTES, ''),
coalesce(@FULLHASH, ''),
coalesce(@PARTIALHASH, ''),
coalesce(@MC, ''),
coalesce(@ORGANIZATION, ''),
coalesce(@LOCATION, ''),
coalesce(@GIFTYEAR, 0),
coalesce(@GIFTRANGE, ''),
coalesce(@TYPE, ''),
coalesce(@CATEGORY, ''),
coalesce(@LO, 0),
coalesce(@HI, 0),
coalesce(@NAME, ''),
coalesce(@ORGANIZATIONWEBADDRESS, ''),
coalesce(@SOURCEMATERIAL, ''),
coalesce(@GIFTYEARHI, 0),
coalesce(@GIFTYEARLO, 0),
coalesce(@TYPECODE, 0),
coalesce(@EIN, ''),
coalesce(@MATCHHASH, ''),
coalesce(@PHILANTHROPICGIFTID, 0),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@CONFIRMED,
@CONFIRMEDBYAPPUSERID,
@DATECONFIRMED,
@REJECTED,
@REJECTEDBYAPPUSERID,
@DATEREJECTED,
@RECURSIVEMATCH,
coalesce(@HOUSEHOLDID, ''),
coalesce(@TASCORE, 0),
coalesce(@MATCHADDRESS, ''),
coalesce(@MATCHCITY, ''),
Coalesce(@MATCHSTATE, ''),
coalesce(@MATCHZIP, '')
);
if @CATEGORYLIST is not null
begin
exec dbo.USP_WPPHILANTHROPICGIFT_CATEGORIES_ADDFROMXML @ID, @CATEGORYLIST, @CHANGEAGENTID, @CURRENTDATE;
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
return 0;
end;