USP_WPNONPROFITAFFILIATION_ADD
Adds WealthPoint Nonprofit Affiliation information for a prospect.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @WEALTHID | uniqueidentifier | IN | |
| @SOURCE | nvarchar(100) | IN | |
| @GSID | int | IN | |
| @REVISION | int | IN | |
| @ORIGINALID | int | IN | |
| @NEWROW | bit | IN | |
| @FULLHASH | nvarchar(32) | IN | |
| @PARTIALHASH | nvarchar(32) | IN | |
| @MC | nvarchar(3) | IN | |
| @FULLNAME | nvarchar(150) | IN | |
| @TITLE | nvarchar(100) | IN | |
| @SALARY | money | IN | |
| @DN_ORGANIZATION | nvarchar(150) | IN | |
| @DN_ZIP5 | nvarchar(5) | IN | |
| @EIN | nvarchar(30) | IN | |
| @LINE1 | nvarchar(150) | IN | |
| @CITY | nvarchar(50) | IN | |
| @ZIP | nvarchar(10) | IN | |
| @REVENUE | money | IN | |
| @STATE | nvarchar(100) | IN | |
| @PHONE | nvarchar(20) | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @FORMYEAR | UDT_YEAR | IN | |
| @FILEDDATE | datetime | IN | |
| @RECURSIVEMATCH | bit | IN | |
| @DESCRIPTION | nvarchar(200) | IN | |
| @TOTALASSETS | money | IN | |
| @WEBSITE | UDT_WEBADDRESS | IN | |
| @ORGFORMYEAR | UDT_YEAR | IN | |
| @RULINGYEAR | UDT_YEAR | IN | |
| @LOCATION | nvarchar(100) | IN | |
| @HCITY | nvarchar(100) | IN | |
| @HSTATE | nvarchar(2) | IN | |
| @HZIP | nvarchar(10) | IN | |
| @SPOUSEFLAG | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_WPNONPROFITAFFILIATION_ADD (
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@WEALTHID uniqueidentifier,
@SOURCE nvarchar(100),
@GSID int = 0,
@REVISION int = 0,
@ORIGINALID int = 0,
@NEWROW bit = 0,
@FULLHASH nvarchar(32) = '',
@PARTIALHASH nvarchar(32) = '',
@MC nvarchar(3) = '',
@FULLNAME nvarchar(150) = '',
@TITLE nvarchar(100) = '',
@SALARY money = 0,
@DN_ORGANIZATION nvarchar(150) = '',
@DN_ZIP5 nvarchar(5) = '',
@EIN nvarchar(30) = '',
@LINE1 nvarchar(150) = '',
@CITY nvarchar(50) = '',
@ZIP nvarchar(10) = '',
@REVENUE money = 0,
@STATE nvarchar(100) = '',
@PHONE nvarchar(20) = '',
@CURRENTAPPUSERID uniqueidentifier = null,
@FORMYEAR dbo.UDT_YEAR = 0,
@FILEDDATE datetime = null,
@RECURSIVEMATCH bit = 0,
@DESCRIPTION nvarchar(200) = '',
@TOTALASSETS money = 0,
@WEBSITE dbo.UDT_WEBADDRESS = '',
@ORGFORMYEAR dbo.UDT_YEAR = 0,
@RULINGYEAR dbo.UDT_YEAR = 0,
@LOCATION nvarchar(100) = '',
@HCITY nvarchar(100) = '',
@HSTATE nvarchar(2) = '',
@HZIP nvarchar(10) = '',
@SPOUSEFLAG bit = 0
) as begin
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
declare @NEWRECORD bit;
declare @UPDATEDRECORD bit;
declare @CURRENTDATE datetime;
set @NEWRECORD = 0;
set @UPDATEDRECORD = 0;
set @CURRENTDATE = getdate();
/*Handling Data corruption issue
This has to stay in play until we change data vendors from GuideStar
*/
declare @DELETEMIN datetime = '2010-07-01T00:00:00.000';
declare @DELETEMAX datetime = '2012-03-31T00:00:00.000';
delete from dbo.WPNONPROFITAFFILIATION
where WEALTHID=@WEALTHID and
SOURCE = N'GuideStar' and
DATEADDED between @DELETEMIN and @DELETEMAX and
CONFIRMED = 0 and
REJECTED = 0 and
MC <> '';
/*End Handling Data corruption issue */
--Get any existing ID
select
@ID = ID
from
dbo.WPNONPROFITAFFILIATION
where
WEALTHID=@WEALTHID and PARTIALHASH=@PARTIALHASH;
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.WPNONPROFITAFFILIATION where WEALTHID=@WEALTHID and FULLHASH=@FULLHASH)
begin
--Record exists but needs to be updated
set @UPDATEDRECORD = 1
update dbo.WPRELATIONSHIP_NPA set
EIN = coalesce(nullif(@EIN, ''), EIN),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
where
ID = (select WPRELATIONSHIP_NPA_ID from dbo.WPNONPROFITAFFILIATION where WEALTHID=@WEALTHID and PARTIALHASH=@PARTIALHASH)
update
dbo.WPNONPROFITAFFILIATION
set
MC = @MC,
RECURSIVEMATCH = @RECURSIVEMATCH,
FULLNAME = coalesce(nullif(@FULLNAME, ''), FULLNAME),
TITLE = coalesce(nullif(@TITLE, ''), TITLE),
SALARY = coalesce(nullif(@SALARY, 0), SALARY),
DN_ORGANIZATION = coalesce(nullif(@DN_ORGANIZATION, ''), DN_ORGANIZATION),
DN_ZIP5 = coalesce(nullif(@DN_ZIP5, ''), DN_ZIP5),
LINE1 = coalesce(nullif(@LINE1, ''), LINE1),
CITY = coalesce(nullif(@CITY, ''), CITY),
ZIP = coalesce(nullif(@ZIP, ''), ZIP),
REVENUE = coalesce(nullif(@REVENUE, 0), REVENUE),
STATE = coalesce(nullif(@STATE, ''), STATE),
PHONE = coalesce(nullif(@PHONE, ''), PHONE),
FORMYEAR = coalesce(nullif(@FORMYEAR, 0), FORMYEAR),
FILEDDATE = coalesce(@FILEDDATE, FILEDDATE),
FULLHASH = @FULLHASH,
DESCRIPTION = coalesce(nullif(@DESCRIPTION, ''), DESCRIPTION),
TOTALASSETS = coalesce(nullif(@TOTALASSETS, 0), TOTALASSETS),
WEBSITE = coalesce(nullif(@WEBSITE, ''), WEBSITE),
ORGFORMYEAR = coalesce(nullif(@ORGFORMYEAR, 0), ORGFORMYEAR),
RULINGYEAR = coalesce(nullif(@RULINGYEAR, 0), RULINGYEAR),
HCITY = coalesce(nullif(@HCITY, ''), HCITY),
HSTATE = coalesce(nullif(@HSTATE, ''), HSTATE),
HZIP = coalesce(nullif(@HZIP, ''), HZIP),
SOURCE = coalesce(nullif(@SOURCE, ''), SOURCE),
SPOUSEFLAG = coalesce(nullif(@SPOUSEFLAG, 0), SPOUSEFLAG),
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 PARTIALHASH=@PARTIALHASH;
end
end
else
begin
--Record is new and needs to be Added
set @NEWRECORD = 1
if @ID is null
set @ID = newid();
set @CURRENTDATE = getdate();
declare @WPRELATIONSHIP_NPA_ID uniqueidentifier;
--Get common nonprofit association record ID
exec dbo.USP_WPRELATIONSHIP_NPA_ADD @ID = @WPRELATIONSHIP_NPA_ID output, @EIN = @EIN, @CHANGEAGENTID = @CHANGEAGENTID;
insert into dbo.WPNONPROFITAFFILIATION (
ID,
WEALTHID,
SOURCE,
GSID,
REVISION,
ORIGINALID,
NEWROW,
FULLHASH,
PARTIALHASH,
MC,
FULLNAME,
TITLE,
SALARY,
DN_ORGANIZATION,
DN_ZIP5,
LINE1,
CITY,
ZIP,
REVENUE,
STATE,
PHONE,
DESCRIPTION,
TOTALASSETS,
WEBSITE,
ORGFORMYEAR,
RULINGYEAR,
CHANGEDBYID,
ADDEDBYID,
DATEADDED,
DATECHANGED,
CONFIRMED,
CONFIRMEDBYAPPUSERID,
DATECONFIRMED,
REJECTED,
REJECTEDBYAPPUSERID,
DATEREJECTED,
FORMYEAR,
FILEDDATE,
RECURSIVEMATCH,
WPRELATIONSHIP_NPA_ID,
HCITY,
HSTATE,
HZIP,
SPOUSEFLAG
) values (
@ID,
@WEALTHID,
coalesce(@SOURCE, ''),
coalesce(@GSID, 0),
coalesce(@REVISION, 0),
coalesce(@ORIGINALID, 0),
coalesce(@NEWROW, 0),
coalesce(@FULLHASH, ''),
coalesce(@PARTIALHASH, ''),
coalesce(@MC, ''),
coalesce(@FULLNAME, ''),
coalesce(@TITLE, ''),
coalesce(@SALARY, 0),
coalesce(@DN_ORGANIZATION, ''),
coalesce(@DN_ZIP5, ''),
coalesce(@LINE1, ''),
coalesce(@CITY, ''),
coalesce(@ZIP, ''),
coalesce(@REVENUE, 0),
coalesce(@STATE, ''),
coalesce(@PHONE, ''),
coalesce(@DESCRIPTION, ''),
coalesce(@TOTALASSETS, 0),
coalesce(@WEBSITE, ''),
coalesce(@ORGFORMYEAR, 0),
coalesce(@RULINGYEAR, 0),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@CONFIRMED,
@CONFIRMEDBYAPPUSERID,
@DATECONFIRMED,
@REJECTED,
@REJECTEDBYAPPUSERID,
@DATEREJECTED,
@FORMYEAR,
@FILEDDATE,
@RECURSIVEMATCH,
@WPRELATIONSHIP_NPA_ID,
coalesce(@HCITY, ''),
coalesce(@HSTATE, ''),
coalesce(@HZIP, ''),
coalesce(@SPOUSEFLAG, 0)
);
end
if @NEWRECORD = 1 or @UPDATEDRECORD = 1
begin
--Log record into the history table
insert into dbo.WPNONPROFITAFFILIATIONHISTORY (
WPNONPROFITAFFILIATIONID,
SOURCE,
FULLHASH,
PARTIALHASH,
TITLE,
SALARY,
DN_ORGANIZATION,
LINE1,
CITY,
STATE,
ZIP,
REVENUE,
PHONE,
DESCRIPTION,
TOTALASSETS,
WEBSITE,
ORGFORMYEAR,
RULINGYEAR,
HCITY,
HSTATE,
HZIP,
FORMYEAR,
FILEDDATE,
SPOUSEFLAG,
CHANGEDBYID,
ADDEDBYID,
DATEADDED,
DATECHANGED
) values (
@ID,
coalesce(@SOURCE, ''),
coalesce(@FULLHASH, ''),
coalesce(@PARTIALHASH, ''),
coalesce(@TITLE, ''),
coalesce(@SALARY, 0),
coalesce(@DN_ORGANIZATION, ''),
coalesce(@LINE1, ''),
coalesce(@CITY, ''),
coalesce(@STATE, ''),
coalesce(@ZIP, ''),
coalesce(@REVENUE, 0),
coalesce(@PHONE, ''),
coalesce(@DESCRIPTION, ''),
coalesce(@TOTALASSETS, 0),
coalesce(@WEBSITE, ''),
coalesce(@ORGFORMYEAR, 0),
coalesce(@RULINGYEAR, 0),
coalesce(@HCITY, ''),
coalesce(@HSTATE, ''),
coalesce(@HZIP, ''),
@FORMYEAR,
@FILEDDATE,
coalesce(@SPOUSEFLAG, ''),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
return 0;
end;