USP_WPSECURITIES_ADD
Adds WealthPoint Securities information for a prospect.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @WEALTHID | uniqueidentifier | IN | |
| @SOURCE | nvarchar(100) | IN | |
| @VIID | 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(60) | IN | |
| @CUSIP | nvarchar(9) | IN | |
| @FILERID | nvarchar(20) | IN | |
| @RELATIONSHIP | nvarchar(50) | IN | |
| @TICKER | nvarchar(8) | IN | |
| @ISSUERNAME | nvarchar(60) | IN | |
| @REPORTDATEDIRECT | datetime | IN | |
| @REPORTDATEINDIRECT | datetime | IN | |
| @AMOUNTOWNEDDIRECT | bigint | IN | |
| @AMOUNTOWNEDINDIRECT | bigint | IN | |
| @PRICEDIRECT | money | IN | |
| @PRICEINDIRECT | money | IN | |
| @VALUEDIRECT | money | IN | |
| @VALUEINDIRECT | money | IN | |
| @UPDATEDVALUEDIRECT | money | IN | |
| @UPDATEDVALUEINDIRECT | money | IN | |
| @UPDATEDDATE | datetime | IN | |
| @UPDATEDPRICE | money | IN | |
| @ACTIVE | bit | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @RECURSIVEMATCH | bit | IN | |
| @SECURITYID | int | IN |
Definition
Copy
CREATE procedure dbo.USP_WPSECURITIES_ADD (
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@WEALTHID uniqueidentifier,
@SOURCE nvarchar(100),
@VIID 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(60) = '',
@CUSIP nvarchar(9) = '',
@FILERID nvarchar(20) = '',
@RELATIONSHIP nvarchar(50) = '',
@TICKER nvarchar(8) = '',
@ISSUERNAME nvarchar(60) = '',
@REPORTDATEDIRECT datetime = null,
@REPORTDATEINDIRECT datetime = null,
@AMOUNTOWNEDDIRECT bigint = 0,
@AMOUNTOWNEDINDIRECT bigint = 0,
@PRICEDIRECT money = 0,
@PRICEINDIRECT money = 0,
@VALUEDIRECT money = 0,
@VALUEINDIRECT money = 0,
@UPDATEDVALUEDIRECT money = 0,
@UPDATEDVALUEINDIRECT money = 0,
@UPDATEDDATE datetime = null,
@UPDATEDPRICE money = 0,
@ACTIVE bit = 0,
@CURRENTAPPUSERID uniqueidentifier = null,
@RECURSIVEMATCH bit = 0,
@SECURITYID int = 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.WPSECURITIES
where
WEALTHID=@WEALTHID and PARTIALHASH=@PARTIALHASH;
-- Record exists
if @ID is not null
begin
if not exists(select 1 from dbo.WPSECURITIES where WEALTHID=@WEALTHID and FULLHASH=@FULLHASH)
begin
--Record exists but needs to be updated
set @UPDATEDRECORD = 1
update
dbo.WPSECURITIES
set
MC = coalesce(nullif(@MC, ''), MC),
RECURSIVEMATCH = coalesce(nullif(@RECURSIVEMATCH, ''), RECURSIVEMATCH),
FULLNAME = coalesce(nullif(@FULLNAME, ''), FULLNAME),
CUSIP= coalesce(nullif(@CUSIP, ''), CUSIP),
RELATIONSHIP = coalesce(nullif(@RELATIONSHIP, ''), RELATIONSHIP),
TICKER = coalesce(nullif(@TICKER, ''), TICKER),
ISSUERNAME = coalesce(nullif(@ISSUERNAME, ''), ISSUERNAME),
REPORTDATEDIRECT = coalesce(@REPORTDATEDIRECT, REPORTDATEDIRECT),
REPORTDATEINDIRECT = coalesce(@REPORTDATEINDIRECT, REPORTDATEINDIRECT),
AMOUNTOWNEDDIRECT = coalesce(nullif(@AMOUNTOWNEDDIRECT, 0), AMOUNTOWNEDDIRECT),
AMOUNTOWNEDINDIRECT = coalesce(nullif(@AMOUNTOWNEDINDIRECT, 0), AMOUNTOWNEDINDIRECT),
PRICEDIRECT = coalesce(nullif(@PRICEDIRECT, 0), PRICEDIRECT),
PRICEINDIRECT = coalesce(nullif(@PRICEINDIRECT, 0), PRICEINDIRECT),
VALUEDIRECT = coalesce(nullif(@VALUEDIRECT, 0), VALUEDIRECT),
VALUEINDIRECT = coalesce(nullif(@VALUEINDIRECT, 0), VALUEINDIRECT),
UPDATEDVALUEDIRECT = coalesce(nullif(@UPDATEDVALUEDIRECT, 0), UPDATEDVALUEDIRECT),
UPDATEDVALUEINDIRECT = coalesce(nullif(@UPDATEDVALUEINDIRECT, 0), UPDATEDVALUEINDIRECT),
UPDATEDDATE = coalesce(@UPDATEDDATE, UPDATEDDATE),
UPDATEDPRICE = coalesce(nullif(@UPDATEDPRICE, 0), UPDATEDPRICE),
ACTIVE = coalesce(nullif(@ACTIVE, 0), ACTIVE),
FULLHASH = @FULLHASH,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
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 @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
insert into dbo.WPSECURITIES (
ID,
WEALTHID,
VIID,
SECURITYID,
SOURCE,
REVISION,
ORIGINALID,
NEWROW,
CDATE,
CVALUE,
CNOTES,
FULLHASH,
PARTIALHASH,
MC,
FULLNAME,
CUSIP,
FILERID,
RELATIONSHIP,
TICKER,
ISSUERNAME,
REPORTDATEDIRECT,
REPORTDATEINDIRECT,
AMOUNTOWNEDDIRECT,
AMOUNTOWNEDINDIRECT,
PRICEDIRECT,
PRICEINDIRECT,
VALUEDIRECT,
VALUEINDIRECT,
UPDATEDVALUEDIRECT,
UPDATEDVALUEINDIRECT,
UPDATEDDATE,
UPDATEDPRICE,
ACTIVE,
CHANGEDBYID,
ADDEDBYID,
DATEADDED,
DATECHANGED,
CONFIRMED,
CONFIRMEDBYAPPUSERID,
DATECONFIRMED,
REJECTED,
REJECTEDBYAPPUSERID,
DATEREJECTED,
RECURSIVEMATCH
) values (
@ID,
@WEALTHID,
@VIID,
@SECURITYID,
coalesce(@SOURCE, ''),
coalesce(@REVISION, 0),
coalesce(@ORIGINALID, 0),
coalesce(@NEWROW, 0),
@CDATE,
coalesce(@CVALUE, 0),
coalesce(@CNOTES, ''),
coalesce(@FULLHASH, ''),
coalesce(@PARTIALHASH, ''),
coalesce(@MC, ''),
coalesce(@FULLNAME, ''),
coalesce(@CUSIP, ''),
coalesce(@FILERID, ''),
coalesce(@RELATIONSHIP, ''),
coalesce(@TICKER, ''),
coalesce(@ISSUERNAME, ''),
@REPORTDATEDIRECT,
@REPORTDATEINDIRECT,
coalesce(@AMOUNTOWNEDDIRECT, 0),
coalesce(@AMOUNTOWNEDINDIRECT, 0),
coalesce(@PRICEDIRECT, 0),
coalesce(@PRICEINDIRECT, 0),
coalesce(@VALUEDIRECT, 0),
coalesce(@VALUEINDIRECT, 0),
coalesce(@UPDATEDVALUEDIRECT, 0),
coalesce(@UPDATEDVALUEINDIRECT, 0),
@UPDATEDDATE,
coalesce(@UPDATEDPRICE, 0),
coalesce(@ACTIVE, 0),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@CONFIRMED,
@CONFIRMEDBYAPPUSERID,
@DATECONFIRMED,
@REJECTED,
@REJECTEDBYAPPUSERID,
@DATEREJECTED,
@RECURSIVEMATCH
);
end
if @NEWRECORD = 1 or @UPDATEDRECORD = 1
begin
--Log record into the history table
insert into dbo.WPSECURITIESHISTORY (
WPSECURITIESID,
SOURCE,
FULLHASH,
PARTIALHASH,
FULLNAME,
CUSIP,
FILERID,
RELATIONSHIP,
TICKER,
ISSUERNAME,
REPORTDATEDIRECT,
REPORTDATEINDIRECT,
AMOUNTOWNEDDIRECT,
AMOUNTOWNEDINDIRECT,
CHANGEDBYID,
ADDEDBYID,
DATEADDED,
DATECHANGED
) values (
@ID,
coalesce(@SOURCE, ''),
coalesce(@FULLHASH, ''),
coalesce(@PARTIALHASH, ''),
coalesce(@FULLNAME, ''),
coalesce(@CUSIP, ''),
coalesce(@FILERID, ''),
coalesce(@RELATIONSHIP, ''),
coalesce(@TICKER, ''),
coalesce(@ISSUERNAME, ''),
@REPORTDATEDIRECT,
@REPORTDATEINDIRECT,
coalesce(@AMOUNTOWNEDDIRECT, 0),
coalesce(@AMOUNTOWNEDINDIRECT, 0),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
return 0;
end