USP_DATAFORMTEMPLATE_ADDSAVE_WPSECURITIES_2
The save procedure used by the add dataform template "WealthPoint Securities Add Form 2".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @WEALTHID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @SOURCE | nvarchar(100) | IN | Source |
| @CNOTES | nvarchar(1024) | IN | Notes |
| @FULLNAME | nvarchar(60) | IN | Name |
| @CUSIP | nvarchar(9) | IN | Cusip |
| @FILERID | nvarchar(20) | IN | Filer ID |
| @RELATIONSHIP | nvarchar(50) | IN | Relationship |
| @TICKER | nvarchar(8) | IN | Symbol |
| @ISSUERNAME | nvarchar(60) | IN | Issuer name |
| @REPORTDATEDIRECT | datetime | IN | Direct date |
| @REPORTDATEINDIRECT | datetime | IN | Indirect date |
| @AMOUNTOWNEDDIRECT | bigint | IN | Direct quantity |
| @AMOUNTOWNEDINDIRECT | bigint | IN | Indirect quantity |
| @PRICEDIRECT | money | IN | Price direct |
| @PRICEINDIRECT | money | IN | Price indirect |
| @VALUEDIRECT | money | IN | Value direct |
| @VALUEINDIRECT | money | IN | Value indirect |
| @ACTIVE | bit | IN | Active |
| @WPSECURITIESTRANSACTIONS | xml | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADDSAVE_WPSECURITIES_2(
@ID uniqueidentifier = null output,
@WEALTHID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@SOURCE nvarchar(100),
@CNOTES nvarchar(1024) = '',
@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,
@ACTIVE bit = 1,
@WPSECURITIESTRANSACTIONS xml = null,
@CURRENTAPPUSERID uniqueidentifier
) as begin
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @ID is null
set @ID = newid();
if @AMOUNTOWNEDDIRECT is null
set @AMOUNTOWNEDDIRECT = 0;
if @AMOUNTOWNEDINDIRECT is null
set @AMOUNTOWNEDINDIRECT = 0;
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
begin try
--wealth source updates
if(select
count(WEALTHSOURCE.SOURCE)
from
dbo.WEALTHSOURCE
where SOURCE = @SOURCE
) = 0
begin
insert into dbo.WEALTHSOURCE (
[SOURCE],
[ISBUILTIN],
[ADDEDBYID],
[CHANGEDBYID]
)values(
@SOURCE,
0,
@CHANGEAGENTID,
@CHANGEAGENTID
);
end
if(
not exists (select top 1 1 from dbo.WEALTHCAPACITYFORMULASECURITIESSOURCE where SOURCE = @SOURCE)
and
not exists (select top 1 1 from dbo.WPSECURITIES where SOURCE = @SOURCE)
)
begin
insert into WEALTHCAPACITYFORMULASECURITIESSOURCE(
WEALTHCAPACITYFORMULAID,
[SOURCE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
ID,
@SOURCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from WEALTHCAPACITYFORMULA;
end
--Calculate the total securities value to ensure it does not exceed the maximum amount
--An overflow error will be processed in the catch block if the maximum money amount is exceeded.
declare @TOTAL money;
set @TOTAL = @VALUEDIRECT + @VALUEINDIRECT
insert into dbo.WPSECURITIES (
ID,
WEALTHID,
CHANGEDBYID,
ADDEDBYID,
SOURCE,
CNOTES,
FULLNAME,
CUSIP,
FILERID,
RELATIONSHIP,
TICKER,
ISSUERNAME,
REPORTDATEDIRECT,
REPORTDATEINDIRECT,
AMOUNTOWNEDDIRECT,
AMOUNTOWNEDINDIRECT,
PRICEDIRECT,
PRICEINDIRECT,
VALUEDIRECT,
VALUEINDIRECT,
ACTIVE,
CONFIRMED,
CONFIRMEDBYAPPUSERID,
DATECONFIRMED,
REJECTED,
REJECTEDBYAPPUSERID,
DATEREJECTED
) values (
@ID,
@WEALTHID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@SOURCE,
@CNOTES,
@FULLNAME,
@CUSIP,
@FILERID,
@RELATIONSHIP,
@TICKER,
@ISSUERNAME,
@REPORTDATEDIRECT,
@REPORTDATEINDIRECT,
@AMOUNTOWNEDDIRECT,
@AMOUNTOWNEDINDIRECT,
@PRICEDIRECT,
@PRICEINDIRECT,
@VALUEDIRECT,
@VALUEINDIRECT,
@ACTIVE,
@CONFIRMED,
@CONFIRMEDBYAPPUSERID,
@DATECONFIRMED,
@REJECTED,
@REJECTEDBYAPPUSERID,
@DATEREJECTED
)
exec dbo.USP_WPSECURITIES_TRANSACTIONS_ADDFROMXML @ID, @WPSECURITIESTRANSACTIONS, @CHANGEAGENTID;
exec dbo.USP_WEALTHPOINT_UPDATEWEALTHSUMMARY_SECURITIES @WEALTHID, @CHANGEAGENTID;
exec dbo.USP_WEALTHCAPACITY_UPDATE @WEALTHID, @CHANGEAGENTID;
end try
begin catch
-- catch the datatype money overflow error
if ERROR_NUMBER() = 8115
begin
raiserror('ERR_SECURITIESTOTALVALUE_MONEYOVERFLOW', 13, 1)
end
end catch
return 0;
end