USP_WPBUSINESSOWNERSHIP_ADD
Adds WealthPoint Business Ownership information for a prospect.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @WEALTHID | uniqueidentifier | IN | |
| @SOURCE | nvarchar(100) | IN | |
| @DBID | 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 | |
| @FULLNAME | nvarchar(100) | IN | |
| @TITLE | nvarchar(100) | IN | |
| @LINE1 | nvarchar(150) | IN | |
| @CITY | nvarchar(30) | IN | |
| @STATE | nvarchar(100) | IN | |
| @ZIP | nvarchar(10) | IN | |
| @BIO | nvarchar(1500) | IN | |
| @HELDCODE | tinyint | IN | |
| @COMPANY | nvarchar(100) | IN | |
| @PHONE | nvarchar(20) | IN | |
| @LINE_OF_BUSINESS_DESCRIPTION | nvarchar(50) | IN | |
| @SALES_VOLUME | money | IN | |
| @YEAR_STARTED | int | IN | |
| @EMPLOYEES_TOTAL | int | IN | |
| @SIC_CODE | nvarchar(4) | IN | |
| @DUNS | nvarchar(9) | IN | |
| @PERCENTAGE | decimal(20, 4) | IN | |
| @VALUATION | money | IN | |
| @SPOUSEIND | tinyint | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @RECURSIVEMATCH | bit | IN | |
| @MAILINGLINE1 | nvarchar(150) | IN | |
| @MAILINGCITY | nvarchar(30) | IN | |
| @MAILINGSTATE | nvarchar(100) | IN | |
| @MAILINGZIP | nvarchar(10) | IN | |
| @HOMELINE1 | nvarchar(150) | IN | |
| @HOMECITY | nvarchar(30) | IN | |
| @HOMESTATE | nvarchar(100) | IN | |
| @HOMEZIP | nvarchar(10) | IN | |
| @SICNAME | nvarchar(100) | IN | |
| @YEAROFBIRTH | UDT_YEAR | IN | |
| @GENDERCODE | tinyint | IN | |
| @RATIO | decimal(20, 4) | IN | |
| @WPBUSINESSOWNERSHIPHISTORYID | uniqueidentifier | INOUT | |
| @SIC_CODE1 | nvarchar(4) | IN | |
| @SIC_CODE2 | nvarchar(4) | IN | |
| @SIC_CODE3 | nvarchar(4) | IN | |
| @SIC_CODE4 | nvarchar(4) | IN | |
| @SIC_CODE5 | nvarchar(4) | IN | |
| @SIC_CODE6 | nvarchar(4) | IN | |
| @RATIO1 | decimal(20, 4) | IN | |
| @RATIO2 | decimal(20, 4) | IN | |
| @RATIO3 | decimal(20, 4) | IN | |
| @RATIO4 | decimal(20, 4) | IN | |
| @RATIO5 | decimal(20, 4) | IN | |
| @RATIO6 | decimal(20, 4) | IN |
Definition
Copy
CREATE procedure dbo.USP_WPBUSINESSOWNERSHIP_ADD (
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@WEALTHID uniqueidentifier,
@SOURCE nvarchar(100),
@DBID 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) = '',
@FULLNAME nvarchar(100) = '',
@TITLE nvarchar(100) = '',
@LINE1 nvarchar(150) = '',
@CITY nvarchar(30) = '',
@STATE nvarchar(100) = '',
@ZIP nvarchar(10) = '',
@BIO nvarchar(1500) = '',
@HELDCODE tinyint = 2,
@COMPANY nvarchar(100) = '',
@PHONE nvarchar(20) = '',
@LINE_OF_BUSINESS_DESCRIPTION nvarchar(50) = '',
@SALES_VOLUME money = 0,
@YEAR_STARTED int = 0,
@EMPLOYEES_TOTAL int = 0,
@SIC_CODE nvarchar(4) = '',
@DUNS nvarchar(9) = '',
@PERCENTAGE decimal(20,4) = 0,
@VALUATION money = 0,
@SPOUSEIND tinyint = 0,
@CURRENTAPPUSERID uniqueidentifier = null,
@RECURSIVEMATCH bit = 0,
@MAILINGLINE1 nvarchar(150) = '',
@MAILINGCITY nvarchar(30) = '',
@MAILINGSTATE nvarchar(100) = '',
@MAILINGZIP nvarchar(10) = '',
@HOMELINE1 nvarchar(150) = '',
@HOMECITY nvarchar(30) = '',
@HOMESTATE nvarchar(100) = '',
@HOMEZIP nvarchar(10) = '',
@SICNAME nvarchar(100) = '',
@YEAROFBIRTH dbo.UDT_YEAR = 0,
@GENDERCODE tinyint = 0,
@RATIO decimal(20,4) = 0,
@WPBUSINESSOWNERSHIPHISTORYID uniqueidentifier = null output,
@SIC_CODE1 nvarchar(4) = '',
@SIC_CODE2 nvarchar(4) = '',
@SIC_CODE3 nvarchar(4) = '',
@SIC_CODE4 nvarchar(4) = '',
@SIC_CODE5 nvarchar(4) = '',
@SIC_CODE6 nvarchar(4) = '',
@RATIO1 decimal(20,4) = 0,
@RATIO2 decimal(20,4) = 0,
@RATIO3 decimal(20,4) = 0,
@RATIO4 decimal(20,4) = 0,
@RATIO5 decimal(20,4) = 0,
@RATIO6 decimal(20,4) = 0
) as begin
set nocount on;
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();
--Get any existing ID
select
@ID = ID
from
dbo.WPBUSINESSOWNERSHIP
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.WPBUSINESSOWNERSHIP where WEALTHID=@WEALTHID and FULLHASH=@FULLHASH)
begin
--Record exists but needs to be updated
set @UPDATEDRECORD = 1;
update
dbo.WPBUSINESSOWNERSHIP
set
MC = coalesce(nullif(@MC, 'EPB'), MC),
RECURSIVEMATCH = @RECURSIVEMATCH,
FULLNAME = coalesce(nullif(@FULLNAME, ''), FULLNAME),
TITLE = coalesce(nullif(@TITLE, ''), TITLE),
LINE1 = coalesce(nullif(@LINE1, ''), LINE1),
CITY = coalesce(nullif(@CITY, ''), CITY),
STATE = coalesce(nullif(@STATE, ''), STATE),
ZIP = coalesce(nullif(@ZIP, ''), ZIP),
BIO = coalesce(nullif(@BIO, ''), BIO),
HELDCODE = coalesce(nullif(@HELDCODE, 2),HELDCODE),
COMPANY = coalesce(nullif(@COMPANY, ''), COMPANY),
PHONE = coalesce(nullif(@PHONE, ''), PHONE),
LINE_OF_BUSINESS_DESCRIPTION = coalesce(nullif(@LINE_OF_BUSINESS_DESCRIPTION, ''), LINE_OF_BUSINESS_DESCRIPTION),
SALES_VOLUME = coalesce(nullif(@SALES_VOLUME, 0),SALES_VOLUME),
YEAR_STARTED = coalesce(nullif(@YEAR_STARTED, 0),YEAR_STARTED),
EMPLOYEES_TOTAL = coalesce(nullif(@EMPLOYEES_TOTAL, 0),EMPLOYEES_TOTAL),
SIC_CODE = coalesce(nullif(@SIC_CODE, ''), SIC_CODE),
PERCENTAGE = coalesce(nullif(@PERCENTAGE, 0),PERCENTAGE),
VALUATION = coalesce(nullif(@VALUATION, 0),VALUATION),
SPOUSEIND = coalesce(nullif(@SPOUSEIND, 0),SPOUSEIND),
MAILINGLINE1 = coalesce(nullif(@MAILINGLINE1, ''), MAILINGLINE1),
MAILINGCITY = coalesce(nullif(@MAILINGCITY, ''), MAILINGCITY),
MAILINGSTATE = coalesce(nullif(@MAILINGSTATE, ''), MAILINGSTATE),
MAILINGZIP = coalesce(nullif(@MAILINGZIP, ''), MAILINGZIP),
HOMELINE1 = coalesce(nullif(@HOMELINE1, ''), HOMELINE1),
HOMECITY = coalesce(nullif(@HOMECITY, ''), HOMECITY),
HOMESTATE = coalesce(nullif(@HOMESTATE, ''), HOMESTATE),
HOMEZIP = coalesce(nullif(@HOMEZIP, ''), HOMEZIP),
SICNAME = coalesce(nullif(@SICNAME, ''), SICNAME),
YEAROFBIRTH = coalesce(nullif(@YEAROFBIRTH, 0), YEAROFBIRTH),
GENDERCODE = coalesce(nullif(@GENDERCODE, 0), GENDERCODE),
RATIO = coalesce(nullif(@RATIO, 0), RATIO),
SIC_CODE1 = coalesce(nullif(@SIC_CODE1, ''), SIC_CODE1),
SIC_CODE2 = coalesce(nullif(@SIC_CODE2, ''), SIC_CODE2),
SIC_CODE3 = coalesce(nullif(@SIC_CODE3, ''), SIC_CODE3),
SIC_CODE4 = coalesce(nullif(@SIC_CODE4, ''), SIC_CODE4),
SIC_CODE5 = coalesce(nullif(@SIC_CODE5, ''), SIC_CODE5),
SIC_CODE6 = coalesce(nullif(@SIC_CODE6, ''), SIC_CODE6),
RATIO1 = coalesce(nullif(@RATIO1, 0), RATIO1),
RATIO2 = coalesce(nullif(@RATIO2, 0), RATIO2),
RATIO3 = coalesce(nullif(@RATIO3, 0), RATIO3),
RATIO4 = coalesce(nullif(@RATIO4, 0), RATIO4),
RATIO5 = coalesce(nullif(@RATIO5, 0), RATIO5),
RATIO6 = coalesce(nullif(@RATIO6, 0), RATIO6),
FULLHASH = @FULLHASH,
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();
declare @WPRELATIONSHIP_BO_ID uniqueidentifier
exec dbo.USP_WPRELATIONSHIP_BO_ADD @ID=@WPRELATIONSHIP_BO_ID output, @DUNS=@DUNS, @CHANGEAGENTID=@CHANGEAGENTID;
insert into dbo.WPBUSINESSOWNERSHIP (
ID,
WEALTHID,
SOURCE,
DBID,
REVISION,
ORIGINALID,
NEWROW,
CDATE,
CVALUE,
CNOTES,
FULLHASH,
PARTIALHASH,
MC,
FULLNAME,
TITLE,
LINE1,
CITY,
STATE,
ZIP,
BIO,
HELDCODE,
COMPANY,
PHONE,
LINE_OF_BUSINESS_DESCRIPTION,
SALES_VOLUME,
YEAR_STARTED,
EMPLOYEES_TOTAL,
SIC_CODE,
PERCENTAGE,
VALUATION,
SPOUSEIND,
MAILINGLINE1,
MAILINGCITY,
MAILINGSTATE,
MAILINGZIP,
HOMELINE1,
HOMECITY,
HOMESTATE,
HOMEZIP,
SICNAME,
YEAROFBIRTH,
GENDERCODE,
RATIO,
SIC_CODE1,
SIC_CODE2,
SIC_CODE3,
SIC_CODE4,
SIC_CODE5,
SIC_CODE6,
RATIO1,
RATIO2,
RATIO3,
RATIO4,
RATIO5,
RATIO6,
CHANGEDBYID,
ADDEDBYID,
DATEADDED,
DATECHANGED,
CONFIRMED,
CONFIRMEDBYAPPUSERID,
DATECONFIRMED,
REJECTED,
REJECTEDBYAPPUSERID,
DATEREJECTED,
RECURSIVEMATCH,
WPRELATIONSHIP_BO_ID
) values (
@ID,
@WEALTHID,
coalesce(@SOURCE, ''),
coalesce(@DBID, 0),
coalesce(@REVISION, 0),
coalesce(@ORIGINALID, 0),
coalesce(@NEWROW, 0),
@CDATE,
coalesce(@CVALUE, 0),
coalesce(@CNOTES, ''),
coalesce(@FULLHASH, ''),
coalesce(@PARTIALHASH, ''),
coalesce(@MC, ''),
coalesce(@FULLNAME, ''),
coalesce(@TITLE, ''),
coalesce(@LINE1, ''),
coalesce(@CITY, ''),
coalesce(@STATE, ''),
coalesce(@ZIP, ''),
coalesce(@BIO, ''),
coalesce(@HELDCODE, 0),
coalesce(@COMPANY, ''),
coalesce(@PHONE, ''),
coalesce(@LINE_OF_BUSINESS_DESCRIPTION, ''),
coalesce(@SALES_VOLUME, 0),
coalesce(@YEAR_STARTED, 0),
coalesce(@EMPLOYEES_TOTAL, 0),
coalesce(@SIC_CODE, ''),
coalesce(@PERCENTAGE, 0),
coalesce(@VALUATION, 0),
coalesce(@SPOUSEIND, 0),
coalesce(@MAILINGLINE1, ''),
coalesce(@MAILINGCITY, ''),
coalesce(@MAILINGSTATE, ''),
coalesce(@MAILINGZIP, ''),
coalesce(@HOMELINE1, ''),
coalesce(@HOMECITY, ''),
coalesce(@HOMESTATE, ''),
coalesce(@HOMEZIP, ''),
coalesce(@SICNAME, ''),
coalesce(@YEAROFBIRTH, 0),
coalesce(@GENDERCODE, 0),
coalesce(@RATIO, 0),
coalesce(@SIC_CODE1, ''),
coalesce(@SIC_CODE2, ''),
coalesce(@SIC_CODE3, ''),
coalesce(@SIC_CODE4, ''),
coalesce(@SIC_CODE5, ''),
coalesce(@SIC_CODE6, ''),
coalesce(@RATIO1, 0),
coalesce(@RATIO2, 0),
coalesce(@RATIO3, 0),
coalesce(@RATIO4, 0),
coalesce(@RATIO5, 0),
coalesce(@RATIO6, 0),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@CONFIRMED,
@CONFIRMEDBYAPPUSERID,
@DATECONFIRMED,
@REJECTED,
@REJECTEDBYAPPUSERID,
@DATEREJECTED,
@RECURSIVEMATCH,
@WPRELATIONSHIP_BO_ID
);
end
if @NEWRECORD = 1 or @UPDATEDRECORD = 1
begin
if @WPBUSINESSOWNERSHIPHISTORYID is null
set @WPBUSINESSOWNERSHIPHISTORYID = newid();
--Log record into the history table
insert into dbo.WPBUSINESSOWNERSHIPHISTORY (
ID,
WPBUSINESSOWNERSHIPID,
SOURCE,
FULLHASH,
PARTIALHASH,
FULLNAME,
TITLE,
LINE1,
CITY,
STATE,
ZIP,
BIO,
COMPANY,
PHONE,
LINE_OF_BUSINESS_DESCRIPTION,
SALES_VOLUME,
YEAR_STARTED,
EMPLOYEES_TOTAL,
SIC_CODE,
PERCENTAGE,
VALUATION,
SPOUSEIND,
MAILINGLINE1,
MAILINGCITY,
MAILINGSTATE,
MAILINGZIP,
HOMELINE1,
HOMECITY,
HOMESTATE,
HOMEZIP,
SICNAME,
YEAROFBIRTH,
GENDERCODE,
RATIO,
SIC_CODE1,
SIC_CODE2,
SIC_CODE3,
SIC_CODE4,
SIC_CODE5,
SIC_CODE6,
RATIO1,
RATIO2,
RATIO3,
RATIO4,
RATIO5,
RATIO6,
HELDCODE,
CHANGEDBYID,
ADDEDBYID,
DATEADDED,
DATECHANGED
) values (
@WPBUSINESSOWNERSHIPHISTORYID,
@ID,
coalesce(@SOURCE, ''),
coalesce(@FULLHASH, ''),
coalesce(@PARTIALHASH, ''),
coalesce(@FULLNAME, ''),
coalesce(@TITLE, ''),
coalesce(@LINE1, ''),
coalesce(@CITY, ''),
coalesce(@STATE, ''),
coalesce(@ZIP, ''),
coalesce(@BIO, ''),
coalesce(@COMPANY, ''),
coalesce(@PHONE, ''),
coalesce(@LINE_OF_BUSINESS_DESCRIPTION, ''),
coalesce(@SALES_VOLUME, 0),
coalesce(@YEAR_STARTED, 0),
coalesce(@EMPLOYEES_TOTAL, 0),
coalesce(@SIC_CODE, ''),
coalesce(@PERCENTAGE, 0),
coalesce(@VALUATION, 0),
coalesce(@SPOUSEIND, 0),
coalesce(@MAILINGLINE1, ''),
coalesce(@MAILINGCITY, ''),
coalesce(@MAILINGSTATE, ''),
coalesce(@MAILINGZIP, ''),
coalesce(@HOMELINE1, ''),
coalesce(@HOMECITY, ''),
coalesce(@HOMESTATE, ''),
coalesce(@HOMEZIP, ''),
coalesce(@SICNAME, ''),
coalesce(@YEAROFBIRTH, 0),
coalesce(@GENDERCODE, 0),
coalesce(@RATIO, 0),
coalesce(@SIC_CODE1, ''),
coalesce(@SIC_CODE2, ''),
coalesce(@SIC_CODE3, ''),
coalesce(@SIC_CODE4, ''),
coalesce(@SIC_CODE5, ''),
coalesce(@SIC_CODE6, ''),
coalesce(@RATIO1, 0),
coalesce(@RATIO2, 0),
coalesce(@RATIO3, 0),
coalesce(@RATIO4, 0),
coalesce(@RATIO5, 0),
coalesce(@RATIO6, 0),
coalesce(@HELDCODE, 0),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
return 0;
end;