USP_WPPHILANTHROPICGIFT_ADD_BULK
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
create procedure dbo.[USP_WPPHILANTHROPICGIFT_ADD_BULK] (
@CHANGEAGENTID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier = null
) as
begin
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
begin try
;with DUPECTE as (
select RN = row_number() over(partition by
[ID]
,[SOURCE]
,[WEALTHID]
,[ORIGINALID]
,[FULLHASH]
,[PARTIALHASH]
,[MC]
,[RECURSIVEMATCH]
,[CDATE]
,[CVALUE]
,[ORGANIZATION]
,[LOCATION]
,[GIFTYEAR]
,[GIFTRANGE]
,[TYPE]
,[CATEGORY]
,[LO]
,[HI]
,[NAME]
,[ORGANIZATIONWEBADDRESS]
,[SOURCEMATERIAL]
,[GIFTYEARHI]
,[GIFTYEARLO]
,[EIN]
,[CNOTES]
,[MATCHHASH]
,[PHILANTHROPICGIFTID]
,[HOUSEHOLDID]
,[TASCORE]
,[MATCHADDRESS]
,[MATCHCITY]
,[MATCHSTATE]
,[MATCHZIP]
,[CATEGORYLIST] order by [ID]
)
from dbo.[#BULK_WPPHILANTHROPICGIFT]
)
delete from DUPECTE where RN > 1;
if exists (select WEALTHID, MATCHHASH from dbo.[#BULK_WPPHILANTHROPICGIFT] group by WEALTHID, MATCHHASH having count(*) > 1)
begin
declare @ID uniqueidentifier;
declare @SOURCE nvarchar(100);
declare @WEALTHID uniqueidentifier;
declare @ORIGINALID int;
declare @FULLHASH nvarchar(32);
declare @PARTIALHASH nvarchar(32);
declare @MC nvarchar(3);
declare @RECURSIVEMATCH bit;
declare @CDATE datetime;
declare @CVALUE money;
declare @ORGANIZATION nvarchar(200);
declare @LOCATION nvarchar(100);
declare @GIFTYEAR smallint;
declare @GIFTRANGE nvarchar(50);
declare @TYPE nvarchar(75);
declare @CATEGORY nvarchar(500);
declare @LO money;
declare @HI money;
declare @NAME nvarchar(200);
declare @ORGANIZATIONWEBADDRESS nvarchar(2000);
declare @SOURCEMATERIAL nvarchar(2000);
declare @GIFTYEARHI smallint;
declare @GIFTYEARLO smallint;
declare @EIN nvarchar(50);
declare @CNOTES nvarchar(1024);
declare @MATCHHASH nvarchar(32);
declare @PHILANTHROPICGIFTID int;
declare @HOUSEHOLDID nvarchar(30);
declare @TASCORE int;
declare @MATCHADDRESS nvarchar(100);
declare @MATCHCITY nvarchar(20);
declare @MATCHSTATE nvarchar(2);
declare @MATCHZIP nvarchar(5);
declare @CATEGORYLIST xml;
--@WMID int = 0,
--@REVISION int = 0,
--@NEWROW bit = 0,
--@TYPECODE int = 0,
declare cur cursor local fast_forward for
select T.[ID],T.[SOURCE],T.[WEALTHID],T.[ORIGINALID],T.[FULLHASH],T.[PARTIALHASH],T.[MC],T.[RECURSIVEMATCH],T.[CDATE],T.[CVALUE],T.[ORGANIZATION],T.[LOCATION],T.[GIFTYEAR],T.[GIFTRANGE],T.[TYPE],T.[CATEGORY],T.[LO],T.[HI],T.[NAME],T.[ORGANIZATIONWEBADDRESS],T.[SOURCEMATERIAL],T.[GIFTYEARHI],T.[GIFTYEARLO],T.[EIN],T.[CNOTES],T.[MATCHHASH],T.[PHILANTHROPICGIFTID],T.[HOUSEHOLDID],T.[TASCORE],T.[MATCHADDRESS],T.[MATCHCITY],T.[MATCHSTATE],T.[MATCHZIP],T.[CATEGORYLIST]
from
dbo.[#BULK_WPPHILANTHROPICGIFT] T
inner join
(select TS.WEALTHID,TS.MATCHHASH from dbo.[#BULK_WPPHILANTHROPICGIFT] TS group by TS.WEALTHID,TS.MATCHHASH having count(*) > 1) D
on
D.WEALTHID = T.WEALTHID and D.MATCHHASH = T.MATCHHASH
order by T.ROWNUM;
open cur;
fetch next from cur into @ID,@SOURCE,@WEALTHID,@ORIGINALID,@FULLHASH,@PARTIALHASH,@MC,@RECURSIVEMATCH,@CDATE,@CVALUE,@ORGANIZATION,@LOCATION,@GIFTYEAR,@GIFTRANGE,@TYPE,@CATEGORY,@LO,@HI,@NAME,@ORGANIZATIONWEBADDRESS,@SOURCEMATERIAL,@GIFTYEARHI,@GIFTYEARLO,@EIN,@CNOTES,@MATCHHASH,@PHILANTHROPICGIFTID,@HOUSEHOLDID,@TASCORE,@MATCHADDRESS,@MATCHCITY,@MATCHSTATE,@MATCHZIP,@CATEGORYLIST;
while @@FETCH_STATUS = 0
begin
exec dbo.USP_WPPHILANTHROPICGIFT_ADD
@ID=@ID,
@SOURCE=@SOURCE,
@WEALTHID=@WEALTHID,
@ORIGINALID=@ORIGINALID,
@FULLHASH=@FULLHASH,
@PARTIALHASH=@PARTIALHASH,
@MC=@MC,
@RECURSIVEMATCH=@RECURSIVEMATCH,
@CDATE=@CDATE,
@CVALUE=@CVALUE,
@ORGANIZATION=@ORGANIZATION,
@LOCATION=@LOCATION,
@GIFTYEAR=@GIFTYEAR,
@GIFTRANGE=@GIFTRANGE,
@TYPE=@TYPE,
@CATEGORY=@CATEGORY,
@LO=@LO,
@HI=@HI,
@NAME=@NAME,
@ORGANIZATIONWEBADDRESS=@ORGANIZATIONWEBADDRESS,
@SOURCEMATERIAL=@SOURCEMATERIAL,
@GIFTYEARHI=@GIFTYEARHI,
@GIFTYEARLO=@GIFTYEARLO,
@EIN=@EIN,
@CNOTES=@CNOTES,
@MATCHHASH=@MATCHHASH,
@PHILANTHROPICGIFTID=@PHILANTHROPICGIFTID,
@HOUSEHOLDID=@HOUSEHOLDID,
@TASCORE=@TASCORE,
@MATCHADDRESS=@MATCHADDRESS,
@MATCHCITY=@MATCHCITY,
@MATCHSTATE=@MATCHSTATE,
@MATCHZIP=@MATCHZIP,
@CATEGORYLIST=@CATEGORYLIST,
@CHANGEAGENTID=@CHANGEAGENTID,
@CURRENTAPPUSERID=@CURRENTAPPUSERID;
fetch next from cur into @ID,@SOURCE,@WEALTHID,@ORIGINALID,@FULLHASH,@PARTIALHASH,@MC,@RECURSIVEMATCH,@CDATE,@CVALUE,@ORGANIZATION,@LOCATION,@GIFTYEAR,@GIFTRANGE,@TYPE,@CATEGORY,@LO,@HI,@NAME,@ORGANIZATIONWEBADDRESS,@SOURCEMATERIAL,@GIFTYEARHI,@GIFTYEARLO,@EIN,@CNOTES,@MATCHHASH,@PHILANTHROPICGIFTID,@HOUSEHOLDID,@TASCORE,@MATCHADDRESS,@MATCHCITY,@MATCHSTATE,@MATCHZIP,@CATEGORYLIST;
end
close cur;
deallocate cur;
delete T
from
dbo.[#BULK_WPPHILANTHROPICGIFT] T
inner join
(select TS.WEALTHID,TS.MATCHHASH from dbo.[#BULK_WPPHILANTHROPICGIFT] TS group by TS.WEALTHID,TS.MATCHHASH having count(*) > 1) D
on
D.WEALTHID = T.WEALTHID and D.MATCHHASH = T.MATCHHASH;
end
if @CURRENTAPPUSERID is not null
begin
update T set
REJECTED = case coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE) when 5 then 0 when 0 then 1 end,
DATEREJECTED = case coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE) when 5 then NULL when 0 then @CURRENTDATE end,
REJECTEDBYAPPUSERID = case coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE) when 5 then NULL when 0 then @CURRENTAPPUSERID end,
CONFIRMED = case coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE) when 5 then 1 when 0 then 0 end,
DATECONFIRMED = case coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE) when 5 then @CURRENTDATE when 0 then NULL end,
CONFIRMEDBYAPPUSERID = case coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE) when 5 then @CURRENTAPPUSERID when 0 then NULL end
from
dbo.[#BULK_WPPHILANTHROPICGIFT] T
inner join
dbo.MATCHCODE MC
on
MC.MATCHCODE = T.MC
left outer join
dbo.WEALTHSOURCE WS
on
WS.[SOURCE] = T.[SOURCE]
left outer join
dbo.CONFIDENCERATING CR
on
CR.MATCHCODEID = MC.ID and
CR.WEALTHSOURCEID = WS.ID
where
coalesce(CR.CONFIDENCE,MC.DEFAULTCONFIDENCE) in (0,5);
end
update PG set
MATCHADDRESS = coalesce(T.MATCHADDRESS, ''),
MATCHCITY = coalesce(T.MATCHCITY, ''),
MATCHSTATE = coalesce(T.MATCHSTATE, ''),
MATCHZIP = coalesce(T.MATCHZIP, ''),
HOUSEHOLDID = coalesce(T.HOUSEHOLDID, ''),
TASCORE = coalesce(T.TASCORE, 0),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.[#BULK_WPPHILANTHROPICGIFT] T
inner join
dbo.WPPHILANTHROPICGIFT PG
on
PG.WEALTHID = T.WEALTHID and
PG.MATCHHASH = T.MATCHHASH
where
not exists(select PGS.* from dbo.WPPHILANTHROPICGIFT PGS where PGS.WEALTHID = T.WEALTHID and PGS.FULLHASH = T.FULLHASH) and
PG.MATCHADDRESS = '' and
PG.MATCHCITY = '' and
PG.MATCHSTATE = '' and
PG.MATCHZIP = '' and
(len(coalesce(T.MATCHADDRESS, '')) > 0 or len(coalesce(T.MATCHCITY, '')) > 0 or len(coalesce(T.MATCHSTATE, '')) > 0 or len(coalesce(T.MATCHZIP, '')) > 0);
update PG set
MC = T.MC,
FULLHASH = T.FULLHASH,
RECURSIVEMATCH = T.RECURSIVEMATCH,
PHILANTHROPICGIFTID = T.PHILANTHROPICGIFTID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
CONFIRMED = case when T.CONFIRMED = 1 and PG.CONFIRMED = 0 and PG.REJECTED = 0 then 1 else PG.CONFIRMED end,
CONFIRMEDBYAPPUSERID = case when T.CONFIRMED = 1 and PG.CONFIRMED = 0 and PG.REJECTED = 0 then T.CONFIRMEDBYAPPUSERID else PG.CONFIRMEDBYAPPUSERID end,
DATECONFIRMED = case when T.CONFIRMED = 1 and PG.CONFIRMED = 0 and PG.REJECTED = 0 then T.DATECONFIRMED else PG.DATECONFIRMED end
from
dbo.[#BULK_WPPHILANTHROPICGIFT] T
inner join
dbo.WPPHILANTHROPICGIFT PG
on
PG.WEALTHID = T.WEALTHID and
PG.MATCHHASH = T.MATCHHASH
where
not exists(select PGS.* from dbo.WPPHILANTHROPICGIFT PGS where PGS.WEALTHID = T.WEALTHID and PGS.FULLHASH = T.FULLHASH);
declare @tblNewRecs table (ROWNUM int not null primary key);
insert into @tblNewRecs
select
T.ROWNUM
from
dbo.[#BULK_WPPHILANTHROPICGIFT] T
where
not exists (select * from dbo.WPPHILANTHROPICGIFT PG where PG.WEALTHID = T.WEALTHID and PG.MATCHHASH = T.MATCHHASH);
insert into dbo.WPPHILANTHROPICGIFT
(
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
)
select
T.WEALTHID,
coalesce(T.[SOURCE], ''),
0,
0,
coalesce(T.ORIGINALID, 0),
0,
T.CDATE,
coalesce(T.CVALUE, 0),
coalesce(T.CNOTES, ''),
coalesce(T.FULLHASH, ''),
coalesce(T.PARTIALHASH, ''),
coalesce(T.MC, ''),
coalesce(T.ORGANIZATION, ''),
coalesce(T.LOCATION, ''),
coalesce(T.GIFTYEAR, 0),
coalesce(T.GIFTRANGE, ''),
coalesce(T.[TYPE], ''),
coalesce(T.CATEGORY, ''),
coalesce(T.LO, 0),
coalesce(T.HI, 0),
coalesce(T.NAME, ''),
coalesce(T.ORGANIZATIONWEBADDRESS, ''),
coalesce(T.SOURCEMATERIAL, ''),
coalesce(T.GIFTYEARHI, 0),
coalesce(T.GIFTYEARLO, 0),
0,
coalesce(T.EIN, ''),
coalesce(T.MATCHHASH, ''),
coalesce(T.PHILANTHROPICGIFTID, 0),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
T.CONFIRMED,
T.CONFIRMEDBYAPPUSERID,
T.DATECONFIRMED,
T.REJECTED,
T.REJECTEDBYAPPUSERID,
T.DATEREJECTED,
T.RECURSIVEMATCH,
coalesce(T.HOUSEHOLDID, ''),
coalesce(T.TASCORE, 0),
coalesce(T.MATCHADDRESS, ''),
coalesce(T.MATCHCITY, ''),
coalesce(T.MATCHSTATE, ''),
coalesce(T.MATCHZIP, '')
from
dbo.[#BULK_WPPHILANTHROPICGIFT] T
inner join
@tblNewRecs NR
on
NR.ROWNUM = T.ROWNUM;
insert into dbo.WPPHILANTHROPICGIFTCATEGORY
(
WPPHILANTHROPICGIFTID,
PHILANTHROPICGIFTCATEGORYID,
SEQUENCE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
PG.ID,
C.PHILANTHROPICGIFTCATEGORYID,
C.SEQUENCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.[#BULK_WPPHILANTHROPICGIFT] T
inner join
@tblNewRecs NR
on
NR.ROWNUM = T.ROWNUM
inner join
dbo.WPPHILANTHROPICGIFT PG
on
PG.WEALTHID = T.WEALTHID and
PG.MATCHHASH = T.MATCHHASH
cross apply
dbo.UFN_WPPHILANTHROPICGIFT_CATEGORIES_FROMITEMLISTXML(T.CATEGORYLIST) C
where
T.CATEGORYLIST is not null;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
return 0;
end