USP_DATAFORMTEMPLATE_ADD_WPBUSINESSOWNERSHIP_2
The save procedure used by the add dataform template "WealthPoint Business Ownership 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(100) | IN | Name |
| @TITLE | nvarchar(100) | IN | Job title |
| @LINE1 | nvarchar(150) | IN | Company address |
| @CITY | nvarchar(30) | IN | Company city |
| @STATE | nvarchar(100) | IN | Company state |
| @ZIP | nvarchar(10) | IN | Company ZIP |
| @BIO | nvarchar(1500) | IN | Biography |
| @HELDCODE | tinyint | IN | Held |
| @COMPANY | nvarchar(100) | IN | Company |
| @PHONE | nvarchar(20) | IN | Phone |
| @LINE_OF_BUSINESS_DESCRIPTION | nvarchar(50) | IN | Description |
| @SALES_VOLUME | money | IN | Sales |
| @YEAR_STARTED | UDT_YEAR | IN | Year started |
| @EMPLOYEES_TOTAL | int | IN | # of employees |
| @SIC_CODE | nvarchar(4) | IN | SIC code used |
| @DUNS | nvarchar(10) | IN | DUNS |
| @PERCENTAGE | decimal(20, 4) | IN | % ownership |
| @VALUATION | money | IN | Valuation |
| @SPOUSEIND | tinyint | IN | Spouse ind |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @MAILINGLINE1 | nvarchar(150) | IN | Mailing address |
| @MAILINGCITY | nvarchar(30) | IN | Mailing city |
| @MAILINGSTATE | nvarchar(100) | IN | Mailing state |
| @MAILINGZIP | nvarchar(10) | IN | Mailing ZIP |
| @HOMELINE1 | nvarchar(150) | IN | Home address |
| @HOMECITY | nvarchar(30) | IN | Home city |
| @HOMESTATE | nvarchar(100) | IN | Home state |
| @HOMEZIP | nvarchar(10) | IN | Home ZIP |
| @SICNAME | nvarchar(100) | IN | Trade name |
| @YEAROFBIRTH | UDT_YEAR | IN | Year of birth |
| @GENDERCODE | tinyint | IN | Gender |
| @RATIO | decimal(20, 4) | IN | Ratio used |
| @WPBUSINESSOWNERSHIPBIOS | xml | IN | |
| @WPBUSINESSOWNERSHIPOWNERSHIP | xml | IN | |
| @SIC_CODE1 | nvarchar(4) | IN | SIC code 1 |
| @SIC_CODE2 | nvarchar(4) | IN | SIC code 2 |
| @SIC_CODE3 | nvarchar(4) | IN | SIC code 3 |
| @SIC_CODE4 | nvarchar(4) | IN | SIC code 4 |
| @SIC_CODE5 | nvarchar(4) | IN | SIC code 5 |
| @SIC_CODE6 | nvarchar(4) | IN | SIC code 6 |
| @RATIO1 | decimal(20, 4) | IN | Ratio |
| @RATIO2 | decimal(20, 4) | IN | Ratio |
| @RATIO3 | decimal(20, 4) | IN | Ratio |
| @RATIO4 | decimal(20, 4) | IN | Ratio |
| @RATIO5 | decimal(20, 4) | IN | Ratio |
| @RATIO6 | decimal(20, 4) | IN | Ratio |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_WPBUSINESSOWNERSHIP_2 (
@ID uniqueidentifier = null output,
@WEALTHID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@SOURCE nvarchar(100),
@CNOTES nvarchar(1024) = '',
@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 dbo.UDT_YEAR = 0,
@EMPLOYEES_TOTAL int = 0,
@SIC_CODE nvarchar(4) = '',
@DUNS nvarchar(10) = '',
@PERCENTAGE decimal(20,4) = 0,
@VALUATION money = 0,
@SPOUSEIND tinyint = 0,
@CURRENTAPPUSERID uniqueidentifier,
@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,
@WPBUSINESSOWNERSHIPBIOS xml = null,
@WPBUSINESSOWNERSHIPOWNERSHIP xml = null,
@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
if @ID is null
set @ID = newid();
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;
declare @WPRELATIONSHIP_BO_ID uniqueidentifier
exec dbo.USP_WPRELATIONSHIP_BO_ADD @ID=@WPRELATIONSHIP_BO_ID output, @DUNS=@DUNS, @CHANGEAGENTID=@CHANGEAGENTID;
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.WEALTHCAPACITYFORMULABUSINESSOWNERSHIPSOURCE where SOURCE = @SOURCE)
and
not exists (select top 1 1 from dbo.WPBUSINESSOWNERSHIP where SOURCE = @SOURCE)
)
begin
insert into WEALTHCAPACITYFORMULABUSINESSOWNERSHIPSOURCE(
WEALTHCAPACITYFORMULAID,
[SOURCE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
ID,
@SOURCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from WEALTHCAPACITYFORMULA;
end
insert into dbo.WPBUSINESSOWNERSHIP (
ID,
WEALTHID,
CHANGEDBYID,
ADDEDBYID,
SOURCE,
CNOTES,
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,
CONFIRMED,
CONFIRMEDBYAPPUSERID,
DATECONFIRMED,
REJECTED,
REJECTEDBYAPPUSERID,
DATEREJECTED,
MAILINGLINE1,
MAILINGCITY,
MAILINGSTATE,
MAILINGZIP,
HOMELINE1,
HOMECITY,
HOMESTATE,
HOMEZIP,
SICNAME,
YEAROFBIRTH,
GENDERCODE,
RATIO,
WPRELATIONSHIP_BO_ID,
SIC_CODE1,
SIC_CODE2,
SIC_CODE3,
SIC_CODE4,
SIC_CODE5,
SIC_CODE6,
RATIO1,
RATIO2,
RATIO3,
RATIO4,
RATIO5,
RATIO6
) values (
@ID,
@WEALTHID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@SOURCE,
@CNOTES,
@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,
@CONFIRMED,
@CONFIRMEDBYAPPUSERID,
@DATECONFIRMED,
@REJECTED,
@REJECTEDBYAPPUSERID,
@DATEREJECTED,
@MAILINGLINE1,
@MAILINGCITY,
@MAILINGSTATE,
@MAILINGZIP,
@HOMELINE1,
@HOMECITY,
@HOMESTATE,
@HOMEZIP,
@SICNAME,
@YEAROFBIRTH,
@GENDERCODE,
@RATIO,
@WPRELATIONSHIP_BO_ID,
@SIC_CODE1,
@SIC_CODE2,
@SIC_CODE3,
@SIC_CODE4,
@SIC_CODE5,
@SIC_CODE6,
@RATIO1,
@RATIO2,
@RATIO3,
@RATIO4,
@RATIO5,
@RATIO6
);
exec dbo.USP_WPBUSINESSOWNERSHIP_BIOS_ADDFROMXML @ID, @WPBUSINESSOWNERSHIPBIOS, @CHANGEAGENTID;
exec dbo.USP_WPBUSINESSOWNERSHIP_OWNERSHIP_ADDFROMXML @ID, @WPBUSINESSOWNERSHIPOWNERSHIP, @CHANGEAGENTID;
exec dbo.USP_WEALTHPOINT_UPDATEWEALTHSUMMARY_BUSINESSOWNERSHIP @WEALTHID, @CHANGEAGENTID;
exec dbo.USP_WEALTHCAPACITY_UPDATE @WEALTHID, @CHANGEAGENTID;
return 0;
end;