USP_DATAFORMTEMPLATE_ADD_WPPRIVATEFOUNDATION_2
The save procedure used by the add dataform template "WealthPoint Private Foundation 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 |
| @RULING_YEAR | UDT_YEAR | IN | Ruling year |
| @YEAR_ENDING | datetime | IN | Year ending |
| @EIN | nvarchar(30) | IN | EIN |
| @COMPANY | nvarchar(150) | IN | Organization |
| @LINE1 | nvarchar(150) | IN | Address |
| @CITY | nvarchar(30) | IN | City |
| @STATE | nvarchar(100) | IN | State |
| @ZIP | nvarchar(10) | IN | ZIP |
| @PHONE | nvarchar(25) | IN | Phone |
| @DESCRIPTION | nvarchar(150) | IN | Description |
| @TOTALASSETS_FMV | money | IN | Total assets |
| @FULLNAME | nvarchar(150) | IN | Name |
| @TITLE | nvarchar(30) | IN | Job title |
| @COMP | money | IN | Compensation |
| @FORMYEAR | UDT_YEAR | IN | Form year |
| @FILEDDATE | datetime | IN | Filed date |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @FAIRMARKETVALUEASSETS | money | IN | Fair market value assets |
| @LOCATION | nvarchar(100) | IN | Location |
| @REVENUE | money | IN | Revenue |
| @ORGFORMYEAR | UDT_YEAR | IN | Org. form year |
| @WEBSITE | UDT_WEBADDRESS | IN | Website |
| @HCITY | nvarchar(100) | IN | Location city |
| @HSTATE | nvarchar(2) | IN | Location state |
| @HZIP | nvarchar(10) | IN | Location ZIP |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_WPPRIVATEFOUNDATION_2 (
@ID uniqueidentifier = null output,
@WEALTHID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@SOURCE nvarchar(100),
@CNOTES nvarchar(1024) = '',
@RULING_YEAR dbo.UDT_YEAR = 0,
@YEAR_ENDING datetime = null,
@EIN nvarchar(30) = '',
@COMPANY nvarchar(150) = '',
@LINE1 nvarchar(150) = '',
@CITY nvarchar(30) = '',
@STATE nvarchar(100) = '',
@ZIP nvarchar(10) = '',
@PHONE nvarchar(25) = '',
@DESCRIPTION nvarchar(150) = '',
@TOTALASSETS_FMV money = 0,
@FULLNAME nvarchar(150) = '',
@TITLE nvarchar(30) = '',
@COMP money = 0,
@FORMYEAR dbo.UDT_YEAR = 0,
@FILEDDATE datetime = null,
@CURRENTAPPUSERID uniqueidentifier,
@FAIRMARKETVALUEASSETS money = 0,
@LOCATION nvarchar(100) = '',
@REVENUE money = 0,
@ORGFORMYEAR dbo.UDT_YEAR = 0,
@WEBSITE dbo.UDT_WEBADDRESS = '',
@HCITY nvarchar(100) = '',
@HSTATE nvarchar(2) = '',
@HZIP nvarchar(10) = ''
) 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;
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.WEALTHCAPACITYFORMULAPRIVATEFOUNDATIONSOURCE where SOURCE = @SOURCE)
and
not exists (select top 1 1 from dbo.WPPRIVATEFOUNDATION where SOURCE = @SOURCE)
)
begin
insert into WEALTHCAPACITYFORMULAPRIVATEFOUNDATIONSOURCE(
WEALTHCAPACITYFORMULAID,
[SOURCE],
[ADDEDBYID],
[CHANGEDBYID],
[DATEADDED],
[DATECHANGED]
)
select
ID,
@SOURCE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from WEALTHCAPACITYFORMULA;
end
declare @WPRELATIONSHIP_PF_ID uniqueidentifier;
exec dbo.USP_WPRELATIONSHIP_PF_ADD @ID=@WPRELATIONSHIP_PF_ID output, @EIN=@EIN, @CHANGEAGENTID=@CHANGEAGENTID;
insert into dbo.WPPRIVATEFOUNDATION (
ID,
WEALTHID,
CHANGEDBYID,
ADDEDBYID,
SOURCE,
CNOTES,
RULING_YEAR,
YEAR_ENDING,
COMPANY,
LINE1,
CITY,
STATE,
ZIP,
PHONE,
DESCRIPTION,
TOTALASSETS_FMV,
FULLNAME,
TITLE,
COMP,
FORMYEAR,
FILEDDATE,
CONFIRMED,
CONFIRMEDBYAPPUSERID,
DATECONFIRMED,
REJECTED,
REJECTEDBYAPPUSERID,
DATEREJECTED,
FAIRMARKETVALUEASSETS,
LOCATION,
REVENUE,
ORGFORMYEAR,
WPRELATIONSHIP_PF_ID,
WEBSITE,
HCITY,
HSTATE,
HZIP
) values (
@ID,
@WEALTHID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@SOURCE,
@CNOTES,
@RULING_YEAR,
@YEAR_ENDING,
@COMPANY,
@LINE1,
@CITY,
@STATE,
@ZIP,
@PHONE,
@DESCRIPTION,
@TOTALASSETS_FMV,
@FULLNAME,
@TITLE,
@COMP,
@FORMYEAR,
@FILEDDATE,
@CONFIRMED,
@CONFIRMEDBYAPPUSERID,
@DATECONFIRMED,
@REJECTED,
@REJECTEDBYAPPUSERID,
@DATEREJECTED,
@FAIRMARKETVALUEASSETS,
@LOCATION,
@REVENUE,
@ORGFORMYEAR,
@WPRELATIONSHIP_PF_ID,
@WEBSITE,
@HCITY,
@HSTATE,
@HZIP
);
exec dbo.USP_WEALTHPOINT_UPDATEWEALTHSUMMARY_PRIVATEFOUNDATION @WEALTHID, @CHANGEAGENTID;
exec dbo.USP_WEALTHCAPACITY_UPDATE @WEALTHID, @CHANGEAGENTID;
return 0;
end;