USP_DATAFORMTEMPLATE_ADD_WPINCOMECOMPENSATION_2
The save procedure used by the add dataform template "WealthPoint Income/Compensation 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 |
| @BB_FULLNAME | nvarchar(100) | IN | Name |
| @COMPANY | nvarchar(100) | IN | Company |
| @LONGTITLE | nvarchar(100) | IN | Job title |
| @TICKER | nvarchar(8) | IN | Symbol |
| @AGE | int | IN | Age |
| @BIOGRAPHY | nvarchar(1500) | IN | Biography |
| @OFFICER_DIRECTORFLAG | nvarchar(35) | IN | Officer/Director |
| @OFFSTARTY | UDT_YEAR | IN | Officer start |
| @DIRSTARTY | UDT_YEAR | IN | Director start |
| @LATESTCOMPENSATIONYEAR | UDT_YEAR | IN | Year |
| @LATESTFISCALYEARSALARY | money | IN | Salary |
| @LATESTFISCALYEARBONUS | money | IN | Bonus |
| @LATESTFISCALYEAROTHERSHORTTERMCOMPENSATION | money | IN | Other short term |
| @VALUEOFOPTIONSEXERCISED | money | IN | Exercised |
| @VALUEOFOPTIONSUNEXERCISED_EXERCISABLE | money | IN | Unexercised |
| @VALUEOFOPTIONSUNEXERCISED_UNEXERCISABLE | money | IN | Unexercisable |
| @LATESTFISCALYEAROTHERLONGTERMCOMPENSATION | money | IN | Other long term |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_WPINCOMECOMPENSATION_2 (
@ID uniqueidentifier = null output,
@WEALTHID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@SOURCE nvarchar(100),
@CNOTES nvarchar(1024) = '',
@BB_FULLNAME nvarchar(100) = '',
@COMPANY nvarchar(100) = '',
@LONGTITLE nvarchar(100) = '',
@TICKER nvarchar(8) = '',
@AGE int = 0,
@BIOGRAPHY nvarchar(1500) = '',
@OFFICER_DIRECTORFLAG nvarchar(35) = '',
@OFFSTARTY dbo.UDT_YEAR = 0,
@DIRSTARTY dbo.UDT_YEAR = 0,
@LATESTCOMPENSATIONYEAR dbo.UDT_YEAR = 0,
@LATESTFISCALYEARSALARY money = 0,
@LATESTFISCALYEARBONUS money = 0,
@LATESTFISCALYEAROTHERSHORTTERMCOMPENSATION money = 0,
@VALUEOFOPTIONSEXERCISED money = 0,
@VALUEOFOPTIONSUNEXERCISED_EXERCISABLE money = 0,
@VALUEOFOPTIONSUNEXERCISED_UNEXERCISABLE money = 0,
@LATESTFISCALYEAROTHERLONGTERMCOMPENSATION money = 0,
@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();
exec dbo.USP_WEALTH_CREATE @WEALTHID, @CHANGEAGENTID;
set @AGE = case when @AGE is NULL then 0 else @age end;
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
--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.WEALTHCAPACITYFORMULAINCOMECOMPENSATIONSOURCE where SOURCE = @SOURCE)
and
not exists (select top 1 1 from dbo.WPINCOMECOMPENSATION where SOURCE = @SOURCE)
)
begin
insert into WEALTHCAPACITYFORMULAINCOMECOMPENSATIONSOURCE(
WEALTHCAPACITYFORMULAID,
[SOURCE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
ID,
@SOURCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from WEALTHCAPACITYFORMULA;
end
insert into dbo.WPINCOMECOMPENSATION (
ID,
WEALTHID,
CHANGEDBYID,
ADDEDBYID,
SOURCE,
CNOTES,
BB_FULLNAME,
COMPANY,
LONGTITLE,
TICKER,
AGE,
BIOGRAPHY,
OFFICER_DIRECTORFLAG,
OFFSTARTY,
DIRSTARTY,
LATESTCOMPENSATIONYEAR,
LATESTFISCALYEARSALARY,
LATESTFISCALYEARBONUS,
LATESTFISCALYEAROTHERSHORTTERMCOMPENSATION,
VALUEOFOPTIONSEXERCISED,
VALUEOFOPTIONSUNEXERCISED_EXERCISABLE,
VALUEOFOPTIONSUNEXERCISED_UNEXERCISABLE,
LATESTFISCALYEAROTHERLONGTERMCOMPENSATION,
CONFIRMED,
CONFIRMEDBYAPPUSERID,
DATECONFIRMED,
REJECTED,
REJECTEDBYAPPUSERID,
DATEREJECTED,
HISTORICSET
) values (
@ID,
@WEALTHID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@SOURCE,
@CNOTES,
@BB_FULLNAME,
@COMPANY,
@LONGTITLE,
@TICKER,
@AGE,
@BIOGRAPHY,
coalesce(@OFFICER_DIRECTORFLAG,''),
@OFFSTARTY,
@DIRSTARTY,
@LATESTCOMPENSATIONYEAR,
@LATESTFISCALYEARSALARY,
@LATESTFISCALYEARBONUS,
@LATESTFISCALYEAROTHERSHORTTERMCOMPENSATION,
@VALUEOFOPTIONSEXERCISED,
@VALUEOFOPTIONSUNEXERCISED_EXERCISABLE,
@VALUEOFOPTIONSUNEXERCISED_UNEXERCISABLE,
@LATESTFISCALYEAROTHERLONGTERMCOMPENSATION,
@CONFIRMED,
@CONFIRMEDBYAPPUSERID,
@DATECONFIRMED,
@REJECTED,
@REJECTEDBYAPPUSERID,
@DATEREJECTED,
1
);
exec dbo.USP_WEALTHPOINT_UPDATEWEALTHSUMMARY_INCOMECOMPENSATION @WEALTHID, @CHANGEAGENTID;
exec dbo.USP_WEALTHCAPACITY_UPDATE @WEALTHID, @CHANGEAGENTID;
return 0;
end