USP_WPINCOMECOMPENSATION_ADD
Adds WealthPoint Income/Compensation information for a prospect.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @WEALTHID | uniqueidentifier | IN | |
| @SOURCE | nvarchar(100) | IN | |
| @MGID | 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 | |
| @BB_FULLNAME | nvarchar(100) | IN | |
| @COMPANY | nvarchar(100) | IN | |
| @LONGTITLE | nvarchar(100) | IN | |
| @TICKER | nvarchar(8) | IN | |
| @AGE | int | IN | |
| @BIOGRAPHY | nvarchar(1500) | IN | |
| @OFFICER_DIRECTORFLAG | nvarchar(35) | IN | |
| @OFFSTARTY | UDT_YEAR | IN | |
| @DIRSTARTY | UDT_YEAR | IN | |
| @LATESTCOMPENSATIONYEAR | UDT_YEAR | IN | |
| @LATESTFISCALYEARSALARY | money | IN | |
| @LATESTFISCALYEARBONUS | money | IN | |
| @LATESTFISCALYEAROTHERSHORTTERMCOMPENSATION | money | IN | |
| @VALUEOFOPTIONSEXERCISED | money | IN | |
| @VALUEOFOPTIONSUNEXERCISED_EXERCISABLE | money | IN | |
| @VALUEOFOPTIONSUNEXERCISED_UNEXERCISABLE | money | IN | |
| @LATESTFISCALYEAROTHERLONGTERMCOMPENSATION | money | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @RECURSIVEMATCH | bit | IN | |
| @SETASCURRENT | bit | IN | |
| @WPSEARCHHISTORYID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_WPINCOMECOMPENSATION_ADD (
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@WEALTHID uniqueidentifier,
@SOURCE nvarchar(100),
@MGID 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) = '',
@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 = null,
@RECURSIVEMATCH bit = 0,
@SETASCURRENT bit = 0,
@WPSEARCHHISTORYID uniqueidentifier = null
) as begin
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
--Get any existing ID
select
@ID = ID
from
dbo.WPINCOMECOMPENSATION
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.WPINCOMECOMPENSATION where WEALTHID=@WEALTHID and FULLHASH=@FULLHASH)
begin
update
dbo.WPINCOMECOMPENSATION
set
FULLHASH = @FULLHASH,
MC = @MC,
RECURSIVEMATCH = @RECURSIVEMATCH,
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,
HISTORICCODE = case when HISTORICSET = 0 then 0 else HISTORICCODE end
where
WEALTHID=@WEALTHID and PARTIALHASH=@PARTIALHASH;
end
else
begin
update
dbo.WPINCOMECOMPENSATION
set
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
HISTORICCODE = 0
where WEALTHID = @WEALTHID and FULLHASH = @FULLHASH and HISTORICSET = 0;
end
end
else
begin
--Record is new and needs to be Added
if @ID is null
set @ID = newid();
insert into dbo.WPINCOMECOMPENSATION (
ID,
WEALTHID,
SOURCE,
MGID,
REVISION,
ORIGINALID,
NEWROW,
CDATE,
CVALUE,
CNOTES,
FULLHASH,
PARTIALHASH,
MC,
BB_FULLNAME,
COMPANY,
LONGTITLE,
TICKER,
AGE,
BIOGRAPHY,
OFFICER_DIRECTORFLAG,
OFFSTARTY,
DIRSTARTY,
LATESTCOMPENSATIONYEAR,
LATESTFISCALYEARSALARY,
LATESTFISCALYEARBONUS,
LATESTFISCALYEAROTHERSHORTTERMCOMPENSATION,
VALUEOFOPTIONSEXERCISED,
VALUEOFOPTIONSUNEXERCISED_EXERCISABLE,
VALUEOFOPTIONSUNEXERCISED_UNEXERCISABLE,
LATESTFISCALYEAROTHERLONGTERMCOMPENSATION,
CHANGEDBYID,
ADDEDBYID,
DATEADDED,
DATECHANGED,
CONFIRMED,
CONFIRMEDBYAPPUSERID,
DATECONFIRMED,
REJECTED,
REJECTEDBYAPPUSERID,
DATEREJECTED,
RECURSIVEMATCH,
HISTORICCODE,
HISTORICSET
) values (
@ID,
@WEALTHID,
coalesce(@SOURCE, ''),
coalesce(@MGID, 0),
coalesce(@REVISION, 0),
coalesce(@ORIGINALID, 0),
coalesce(@NEWROW, 0),
@CDATE,
coalesce(@CVALUE, 0),
coalesce(@CNOTES, ''),
coalesce(@FULLHASH, ''),
coalesce(@PARTIALHASH, ''),
coalesce(@MC, ''),
coalesce(@BB_FULLNAME, ''),
coalesce(@COMPANY, ''),
coalesce(@LONGTITLE, ''),
coalesce(@TICKER, ''),
coalesce(@AGE, 0),
coalesce(@BIOGRAPHY, ''),
coalesce(@OFFICER_DIRECTORFLAG, ''),
coalesce(@OFFSTARTY, 0),
coalesce(@DIRSTARTY, 0),
coalesce(@LATESTCOMPENSATIONYEAR, 0),
coalesce(@LATESTFISCALYEARSALARY, 0),
coalesce(@LATESTFISCALYEARBONUS, 0),
coalesce(@LATESTFISCALYEAROTHERSHORTTERMCOMPENSATION, 0),
coalesce(@VALUEOFOPTIONSEXERCISED, 0),
coalesce(@VALUEOFOPTIONSUNEXERCISED_EXERCISABLE, 0),
coalesce(@VALUEOFOPTIONSUNEXERCISED_UNEXERCISABLE, 0),
coalesce(@LATESTFISCALYEAROTHERLONGTERMCOMPENSATION, 0),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE,
@CONFIRMED,
@CONFIRMEDBYAPPUSERID,
@DATECONFIRMED,
@REJECTED,
@REJECTEDBYAPPUSERID,
@DATEREJECTED,
@RECURSIVEMATCH,
case when @SETASCURRENT = 1 then 0 else 2 end,
@SETASCURRENT
);
end
if @WPSEARCHHISTORYID is not null
begin
insert into
dbo.WPINCOMECOMPENSATION_WPSEARCHHISTORY
(WPINCOMECOMPENSATIONID, WPSEARCHHISTORYID)
values
(@ID, @WPSEARCHHISTORYID);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
return 0;
end