USP_DATAFORMTEMPLATE_EDITSAVE_WPBUSINESSOWNERSHIP_3
The save procedure used by the edit dataform template "WealthPoint Business Ownership Edit Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @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 |
| @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_EDITSAVE_WPBUSINESSOWNERSHIP_3 (
@ID 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,
@COMPANY nvarchar(100),
@PHONE nvarchar(20),
@LINE_OF_BUSINESS_DESCRIPTION nvarchar(50),
@SALES_VOLUME money,
@YEAR_STARTED dbo.UDT_YEAR,
@EMPLOYEES_TOTAL int,
@SIC_CODE nvarchar(4),
@DUNS nvarchar(10),
@PERCENTAGE decimal(20,4),
@VALUATION money,
@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,
@GENDERCODE tinyint,
@RATIO decimal(20,4),
@WPBUSINESSOWNERSHIPBIOS xml,
@WPBUSINESSOWNERSHIPOWNERSHIP xml,
@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),
@RATIO2 decimal(20,4),
@RATIO3 decimal(20,4),
@RATIO4 decimal(20,4),
@RATIO5 decimal(20,4),
@RATIO6 decimal(20,4)
) as begin
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
declare @CURRENTDATE datetime = getdate();
if @EMPLOYEES_TOTAL is null
set @EMPLOYEES_TOTAL = 0
declare @WPRELATIONSHIP_BO_ID uniqueidentifier
exec dbo.USP_WPRELATIONSHIP_BO_ADD @ID=@WPRELATIONSHIP_BO_ID output, @DUNS=@DUNS, @CHANGEAGENTID=@CHANGEAGENTID;
--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
update dbo.WPBUSINESSOWNERSHIP set
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE,
SOURCE = @SOURCE,
CNOTES = @CNOTES,
FULLNAME = @FULLNAME,
TITLE = @TITLE,
LINE1 = @LINE1,
CITY = @CITY,
STATE = @STATE,
ZIP = @ZIP,
BIO = @BIO,
HELDCODE = @HELDCODE,
COMPANY = @COMPANY,
PHONE = @PHONE,
LINE_OF_BUSINESS_DESCRIPTION = @LINE_OF_BUSINESS_DESCRIPTION,
SALES_VOLUME = @SALES_VOLUME,
YEAR_STARTED = @YEAR_STARTED,
EMPLOYEES_TOTAL = @EMPLOYEES_TOTAL,
SIC_CODE = @SIC_CODE,
PERCENTAGE = @PERCENTAGE,
VALUATION = @VALUATION,
MAILINGLINE1 = @MAILINGLINE1,
MAILINGCITY = @MAILINGCITY,
MAILINGSTATE = @MAILINGSTATE,
MAILINGZIP = @MAILINGZIP,
HOMELINE1 = @HOMELINE1,
HOMECITY = @HOMECITY,
HOMESTATE = @HOMESTATE,
HOMEZIP = @HOMEZIP,
SICNAME = @SICNAME,
YEAROFBIRTH = @YEAROFBIRTH,
GENDERCODE = @GENDERCODE,
RATIO = @RATIO,
WPRELATIONSHIP_BO_ID = coalesce(@WPRELATIONSHIP_BO_ID, WPRELATIONSHIP_BO_ID),
SIC_CODE1 = @SIC_CODE1,
SIC_CODE2 = @SIC_CODE2,
SIC_CODE3 = @SIC_CODE3,
SIC_CODE4 = @SIC_CODE4,
SIC_CODE5 = @SIC_CODE5,
SIC_CODE6 = @SIC_CODE6,
RATIO1 = @RATIO1,
RATIO2 = @RATIO2,
RATIO3 = @RATIO3,
RATIO4 = @RATIO4,
RATIO5 = @RATIO5,
RATIO6 = @RATIO6
where
ID=@ID;
declare @WEALTHID uniqueidentifier;
select @WEALTHID=WEALTHID from dbo.WPBUSINESSOWNERSHIP where ID=@ID;
exec dbo.USP_WPBUSINESSOWNERSHIP_BIOS_UPDATEFROMXML @ID, @WPBUSINESSOWNERSHIPBIOS, @CHANGEAGENTID
exec dbo.USP_WPBUSINESSOWNERSHIP_OWNERSHIP_UPDATEFROMXML @ID, @WPBUSINESSOWNERSHIPOWNERSHIP, @CHANGEAGENTID
exec dbo.USP_WEALTHPOINT_UPDATEWEALTHSUMMARY_BUSINESSOWNERSHIP @WEALTHID, @CHANGEAGENTID;
exec dbo.USP_WEALTHCAPACITY_UPDATE @WEALTHID, @CHANGEAGENTID;
return 0;
end;