USP_DATAFORMTEMPLATE_ADD_WPPHILANTHROPICGIFT_3
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @WEALTHID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @SOURCE | nvarchar(100) | IN | |
| @CNOTES | nvarchar(1024) | IN | |
| @ORGANIZATION | nvarchar(200) | IN | |
| @LOCATION | nvarchar(100) | IN | |
| @GIFTYEAR | UDT_YEAR | 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 | |
| @EIN | nvarchar(50) | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @MATCHADDRESS | nvarchar(100) | IN | |
| @MATCHCITY | nvarchar(20) | IN | |
| @MATCHSTATE | nvarchar(2) | IN | |
| @MATCHZIP | nvarchar(5) | IN | |
| @WPPHILANTHROPICGIFTCATEGORY | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_WPPHILANTHROPICGIFT_3(
@ID uniqueidentifier = null output,
@WEALTHID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@SOURCE nvarchar(100),
@CNOTES nvarchar(1024) = '',
@ORGANIZATION nvarchar(200) = '',
@LOCATION nvarchar(100) = '',
@GIFTYEAR dbo.UDT_YEAR = 0,
@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,
@EIN nvarchar(50) = '',
@CURRENTAPPUSERID uniqueidentifier,
@MATCHADDRESS nvarchar(100) = '',
@MATCHCITY nvarchar(20) = '',
@MATCHSTATE nvarchar(2) = '',
@MATCHZIP nvarchar(5) = '',
@WPPHILANTHROPICGIFTCATEGORY xml = null
) as begin
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @ID is null
set @ID = newid();
exec dbo.USP_WEALTH_CREATE @WEALTHID, @CHANGEAGENTID;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
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
select
@REJECTED = case MANUALCONFIDENCE
when 5 then 0
when 0 then 1
end,
@DATEREJECTED = case MANUALCONFIDENCE
when 5 then NULL
when 0 then @CURRENTDATE
end,
@REJECTEDBYAPPUSERID = case MANUALCONFIDENCE
when 5 then NULL
when 0 then @CURRENTAPPUSERID
end,
@CONFIRMED = case MANUALCONFIDENCE
when 5 then 1
when 0 then 0
end,
@DATECONFIRMED = case MANUALCONFIDENCE
when 5 then @CURRENTDATE
when 0 then NULL
end,
@CONFIRMEDBYAPPUSERID = case MANUALCONFIDENCE
when 5 then @CURRENTAPPUSERID
when 0 then NULL
end
from
dbo.WEALTHPOINTCONFIGURATION
where
MANUALCONFIDENCE in (0,5)
end
declare @CVALUE money;
set @CVALUE = (@LO + @HI) / 2.0
if not exists (select * from dbo.WEALTHSOURCE where WEALTHSOURCE.SOURCE = @SOURCE)
begin
insert into dbo.WEALTHSOURCE(
[SOURCE],
[ISBUILTIN],
[ADDEDBYID],
[CHANGEDBYID]
)
values(
@SOURCE,
0,
@CHANGEAGENTID,
@CHANGEAGENTID
);
end
if (
not exists (select * from dbo.WEALTHCAPACITYFORMULAPHILANTHROPICGIFTSOURCE where SOURCE = @SOURCE) and
not exists (select * from dbo.WPPHILANTHROPICGIFT where SOURCE = @SOURCE)
)
begin
insert into dbo.WEALTHCAPACITYFORMULAPHILANTHROPICGIFTSOURCE(
WEALTHCAPACITYFORMULAID,
[SOURCE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
ID,
@SOURCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from dbo.WEALTHCAPACITYFORMULA;
end
insert into dbo.WPPHILANTHROPICGIFT (
ID,
WEALTHID,
CHANGEDBYID,
ADDEDBYID,
SOURCE,
CNOTES,
ORGANIZATION,
LOCATION,
GIFTYEAR,
TYPE,
CATEGORY,
LO,
HI,
NAME,
ORGANIZATIONWEBADDRESS,
SOURCEMATERIAL,
GIFTYEARHI,
GIFTYEARLO,
EIN,
CONFIRMED,
CONFIRMEDBYAPPUSERID,
DATECONFIRMED,
REJECTED,
REJECTEDBYAPPUSERID,
DATEREJECTED,
CVALUE,
MATCHADDRESS,
MATCHCITY,
MATCHSTATE,
MATCHZIP
) values (
@ID,
@WEALTHID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@SOURCE,
@CNOTES,
@ORGANIZATION,
@LOCATION,
@GIFTYEAR,
@TYPE,
@CATEGORY,
@LO,
@HI,
@NAME,
@ORGANIZATIONWEBADDRESS,
@SOURCEMATERIAL,
@GIFTYEARHI,
@GIFTYEARLO,
@EIN,
@CONFIRMED,
@CONFIRMEDBYAPPUSERID,
@DATECONFIRMED,
@REJECTED,
@REJECTEDBYAPPUSERID,
@DATEREJECTED,
@CVALUE,
@MATCHADDRESS,
@MATCHCITY,
@MATCHSTATE,
@MATCHZIP
);
if @WPPHILANTHROPICGIFTCATEGORY is not null
begin
exec dbo.USP_WPPHILANTHROPICGIFT_CATEGORIES_ADDFROMXML @ID, @WPPHILANTHROPICGIFTCATEGORY, @CHANGEAGENTID, @CURRENTDATE;
end
exec dbo.USP_WEALTHPOINT_UPDATEWEALTHSUMMARY_PHILANTHROPICGIFT @WEALTHID, @CHANGEAGENTID;
exec dbo.USP_WEALTHCAPACITY_UPDATE @WEALTHID, @CHANGEAGENTID;
return 0;
end;