USP_DATAFORMTEMPLATE_ADD_WEALTHCAPACITYFORMULA
The save procedure used by the add dataform template "Wealth Capacity Formula Add Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @NAME | nvarchar(100) | IN | Name |
| @DESCRIPTION | nvarchar(255) | IN | Description |
| @ISDEFAULT | bit | IN | Set this as the default formula |
| @ASSETCALCULATIONMETHODCODE | tinyint | IN | Asset calculation method |
| @MAJORGIVINGMULTIPLIERTYPECODE | tinyint | IN | Major giving multiplier |
| @MAJORGIVINGSETVALUEMULTIPLIER | decimal(20, 4) | IN | Set value |
| @MAJORGIVINGOVER70MULTIPLIER | decimal(20, 4) | IN | Over 70 years |
| @MAJORGIVING60TO69MULTIPLIER | decimal(20, 4) | IN | Between 60-69 years |
| @MAJORGIVING50TO59MULTIPLIER | decimal(20, 4) | IN | Between 50-59 years |
| @MAJORGIVING40TO49MULTIPLIER | decimal(20, 4) | IN | Between 40-49 years |
| @MAJORGIVINGUNDER40MULTIPLIER | decimal(20, 4) | IN | Less than 40 years |
| @MAJORGIVINGAGEUNKNOWNMULTIPLIER | decimal(20, 4) | IN | Age unknown |
| @REALESTATEINCLUDE | bit | IN | Real estate |
| @REALESTATECONFIRMEDONLY | bit | IN | Use confirmed records only |
| @REALESTATEVALUEMULTIPLIERTYPECODE | tinyint | IN | Real estate multiplier type |
| @REALESTATEVALUEMULTIPLIERCUTOFFVALUE | money | IN | If less than |
| @REALESTATEVALUEMULTIPLIER | decimal(20, 4) | IN | Multiply value by |
| @REALESTATEVALUELESSTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | Multiply value by |
| @REALESTATEVALUEGREATERTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | Otherwise multiply by |
| @REALESTATEINCLUDEASSESSMENTANDDEEDRECORDS | tinyint | IN | Value to use in calculation |
| @REALESTATEAGGREGATETYPECODE | tinyint | IN | Real estate aggregate type |
| @REALESTATEUSETOTALMARKETVALUE | bit | IN | Total market value |
| @REALESTATEUSETOTALASSESSEDVALUE | bit | IN | Total assessed value |
| @REALESTATEUSESALEPRICE | bit | IN | Sale price |
| @REALESTATEUSELOANAMOUNT | bit | IN | Loan amount |
| @BUSINESSOWNERSHIPINCLUDE | bit | IN | Business ownership |
| @BUSINESSOWNERSHIPCONFIRMEDONLY | bit | IN | Use confirmed records only |
| @BUSINESSOWNERSHIPVALUEMULTIPLIERTYPECODE | tinyint | IN | Business ownership multiplier type |
| @BUSINESSOWNERSHIPVALUEMULTIPLIERCUTOFFVALUE | money | IN | If less than |
| @BUSINESSOWNERSHIPVALUEMULTIPLIER | decimal(20, 4) | IN | Multiply value by |
| @BUSINESSOWNERSHIPVALUELESSTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | Multiply value by |
| @BUSINESSOWNERSHIPVALUEGREATERTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | Otherwise multiply by |
| @BUSINESSOWNERSHIPVALUECODE | tinyint | IN | Value to use in calculation |
| @SECURITIESINCLUDE | bit | IN | Securities |
| @SECURITIESCONFIRMEDONLY | bit | IN | Use confirmed records only |
| @SECURITIESVALUEMULTIPLIERTYPECODE | tinyint | IN | Securities multiplier type |
| @SECURITIESVALUEMULTIPLIERCUTOFFVALUE | money | IN | If less than |
| @SECURITIESVALUEMULTIPLIER | decimal(20, 4) | IN | Multiply value by |
| @SECURITIESVALUELESSTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | Multiply value by |
| @SECURITIESVALUEGREATERTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | Otherwise multiply by |
| @SECURITIESVALUECODE | tinyint | IN | Value to use in calculation |
| @AFFLUENCEINDICATORINCLUDE | bit | IN | Affluence indicators |
| @AFFLUENCEINDICATORCONFIRMEDONLY | bit | IN | Use confirmed records only |
| @AFFLUENCEINDICATORVALUEMULTIPLIERTYPECODE | tinyint | IN | Affluence indicator multiplier type |
| @AFFLUENCEINDICATORVALUEMULTIPLIERCUTOFFVALUE | money | IN | If less than |
| @AFFLUENCEINDICATORVALUEMULTIPLIER | decimal(20, 4) | IN | Multiply value by |
| @AFFLUENCEINDICATORVALUELESSTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | Multiply value by |
| @AFFLUENCEINDICATORVALUEGREATERTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | Otherwise multiply by |
| @AFFLUENCEINDICATORVALUECODE | tinyint | IN | Value to use in calculation |
| @INCOMECOMPENSATIONINCLUDE | bit | IN | Income/compensation |
| @INCOMECOMPENSATIONCONFIRMEDONLY | bit | IN | Use confirmed records only |
| @INCOMECOMPENSATIONVALUEMULTIPLIERTYPECODE | tinyint | IN | Income/compensation multiplier type |
| @INCOMECOMPENSATIONVALUEMULTIPLIERCUTOFFVALUE | money | IN | If less than |
| @INCOMECOMPENSATIONVALUEMULTIPLIER | decimal(20, 4) | IN | Multiply value by |
| @INCOMECOMPENSATIONVALUELESSTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | Multiply value by |
| @INCOMECOMPENSATIONVALUEGREATERTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | Otherwise multiply by |
| @INCOMECOMPENSATIONINCLUDESALARY | bit | IN | Salary |
| @INCOMECOMPENSATIONINCLUDEBONUS | bit | IN | Bonus |
| @INCOMECOMPENSATIONINCLUDESHORTTERMCOMP | bit | IN | Other short term compensation |
| @INCOMECOMPENSATIONINCLUDELONGTERMCOMP | bit | IN | Other long term compensation |
| @INCOMECOMPENSATIONINCLUDEEXERCISEDOPTIONS | bit | IN | Exercised options |
| @INCOMECOMPENSATIONINCLUDEUNEXERCISEDOPTIONS | bit | IN | Unexercised options |
| @INCOMECOMPENSATIONINCLUDEUNEXERCISABLEOPTIONS | bit | IN | Unexercisable options |
| @OTHERASSETINCLUDE | bit | IN | Other assets |
| @OTHERASSETCONFIRMEDONLY | bit | IN | Use confirmed records only |
| @OTHERASSETVALUEMULTIPLIERTYPECODE | tinyint | IN | Other asset multiplier type |
| @OTHERASSETVALUEMULTIPLIERCUTOFFVALUE | money | IN | If less than |
| @OTHERASSETVALUEMULTIPLIER | decimal(20, 4) | IN | Multiply value by |
| @OTHERASSETVALUELESSTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | Multiply value by |
| @OTHERASSETVALUEGREATERTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | Otherwise multiply by |
| @OTHERASSETVALUECODE | tinyint | IN | Value to use in calculation |
| @NONPROFITAFFILIATIONINCLUDE | bit | IN | Nonprofit affiliations |
| @NONPROFITAFFILIATIONCONFIRMEDONLY | bit | IN | Use confirmed records only |
| @NONPROFITAFFILIATIONVALUEMULTIPLIERTYPECODE | tinyint | IN | Nonprofit affiliation multiplier type |
| @NONPROFITAFFILIATIONVALUEMULTIPLIERCUTOFFVALUE | money | IN | If less than |
| @NONPROFITAFFILIATIONVALUEMULTIPLIER | decimal(20, 4) | IN | Multiply value by |
| @NONPROFITAFFILIATIONVALUELESSTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | Multiply value by |
| @NONPROFITAFFILIATIONVALUEGREATERTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | Otherwise multiply by |
| @NONPROFITAFFILIATIONVALUECODE | tinyint | IN | Value to use in calculation |
| @PRIVATEFOUNDATIONINCLUDE | bit | IN | Private foundations |
| @PRIVATEFOUNDATIONCONFIRMEDONLY | bit | IN | Use confirmed records only |
| @PRIVATEFOUNDATIONVALUEMULTIPLIERTYPECODE | tinyint | IN | Private foundation multiplier type |
| @PRIVATEFOUNDATIONVALUEMULTIPLIERCUTOFFVALUE | money | IN | If less than |
| @PRIVATEFOUNDATIONVALUEMULTIPLIER | decimal(20, 4) | IN | Multiply value by |
| @PRIVATEFOUNDATIONVALUELESSTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | Multiply value by |
| @PRIVATEFOUNDATIONVALUEGREATERTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | Otherwise multiply by |
| @PRIVATEFOUNDATIONVALUECODE | tinyint | IN | Value to use in calculation |
| @APPLYFORMULA | bit | IN | Apply formula to |
| @APPLYTOCODE | tinyint | IN | Apply to |
| @CONSTITUENTID | uniqueidentifier | IN | Individual constituent |
| @RESEARCHGROUPID | uniqueidentifier | IN | Research group |
| @IDSETREGISTERID | uniqueidentifier | IN | Selection |
| @OTHERSCANMODIFY | bit | IN | Other users may modify this formula |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_WEALTHCAPACITYFORMULA (
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier,
@NAME nvarchar(100),
@DESCRIPTION nvarchar(255) = null,
@ISDEFAULT bit = 0,
@ASSETCALCULATIONMETHODCODE tinyint = 0,
@MAJORGIVINGMULTIPLIERTYPECODE tinyint = 0,
@MAJORGIVINGSETVALUEMULTIPLIER decimal(20,4) = 0,
@MAJORGIVINGOVER70MULTIPLIER decimal(20,4) = 0,
@MAJORGIVING60TO69MULTIPLIER decimal(20,4) = 0,
@MAJORGIVING50TO59MULTIPLIER decimal(20,4) = 0,
@MAJORGIVING40TO49MULTIPLIER decimal(20,4) = 0,
@MAJORGIVINGUNDER40MULTIPLIER decimal(20,4) = 0,
@MAJORGIVINGAGEUNKNOWNMULTIPLIER decimal(20,4) = 0,
@REALESTATEINCLUDE bit = 0,
@REALESTATECONFIRMEDONLY bit = 0,
@REALESTATEVALUEMULTIPLIERTYPECODE tinyint = 0,
@REALESTATEVALUEMULTIPLIERCUTOFFVALUE money = 0,
@REALESTATEVALUEMULTIPLIER decimal(20,4) = 0,
@REALESTATEVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@REALESTATEVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@REALESTATEINCLUDEASSESSMENTANDDEEDRECORDS tinyint = 0,
@REALESTATEAGGREGATETYPECODE tinyint = 0,
@REALESTATEUSETOTALMARKETVALUE bit = 0,
@REALESTATEUSETOTALASSESSEDVALUE bit = 0,
@REALESTATEUSESALEPRICE bit = 0,
@REALESTATEUSELOANAMOUNT bit = 0,
@BUSINESSOWNERSHIPINCLUDE bit = 0,
@BUSINESSOWNERSHIPCONFIRMEDONLY bit = 0,
@BUSINESSOWNERSHIPVALUEMULTIPLIERTYPECODE tinyint = 0,
@BUSINESSOWNERSHIPVALUEMULTIPLIERCUTOFFVALUE money = 0,
@BUSINESSOWNERSHIPVALUEMULTIPLIER decimal(20,4) = 0,
@BUSINESSOWNERSHIPVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@BUSINESSOWNERSHIPVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@BUSINESSOWNERSHIPVALUECODE tinyint = 0,
@SECURITIESINCLUDE bit = 0,
@SECURITIESCONFIRMEDONLY bit = 0,
@SECURITIESVALUEMULTIPLIERTYPECODE tinyint = 0,
@SECURITIESVALUEMULTIPLIERCUTOFFVALUE money = 0,
@SECURITIESVALUEMULTIPLIER decimal(20,4) = 0,
@SECURITIESVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@SECURITIESVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@SECURITIESVALUECODE tinyint = 0,
@AFFLUENCEINDICATORINCLUDE bit = 0,
@AFFLUENCEINDICATORCONFIRMEDONLY bit = 0,
@AFFLUENCEINDICATORVALUEMULTIPLIERTYPECODE tinyint = 0,
@AFFLUENCEINDICATORVALUEMULTIPLIERCUTOFFVALUE money = 0,
@AFFLUENCEINDICATORVALUEMULTIPLIER decimal(20,4) = 0,
@AFFLUENCEINDICATORVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@AFFLUENCEINDICATORVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@AFFLUENCEINDICATORVALUECODE tinyint = 0,
@INCOMECOMPENSATIONINCLUDE bit = 0,
@INCOMECOMPENSATIONCONFIRMEDONLY bit = 0,
@INCOMECOMPENSATIONVALUEMULTIPLIERTYPECODE tinyint = 0,
@INCOMECOMPENSATIONVALUEMULTIPLIERCUTOFFVALUE money = 0,
@INCOMECOMPENSATIONVALUEMULTIPLIER decimal(20,4) = 0,
@INCOMECOMPENSATIONVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@INCOMECOMPENSATIONVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@INCOMECOMPENSATIONINCLUDESALARY bit = 0,
@INCOMECOMPENSATIONINCLUDEBONUS bit = 0,
@INCOMECOMPENSATIONINCLUDESHORTTERMCOMP bit = 0,
@INCOMECOMPENSATIONINCLUDELONGTERMCOMP bit = 0,
@INCOMECOMPENSATIONINCLUDEEXERCISEDOPTIONS bit = 0,
@INCOMECOMPENSATIONINCLUDEUNEXERCISEDOPTIONS bit = 0,
@INCOMECOMPENSATIONINCLUDEUNEXERCISABLEOPTIONS bit = 0,
@OTHERASSETINCLUDE bit = 0,
@OTHERASSETCONFIRMEDONLY bit = 0,
@OTHERASSETVALUEMULTIPLIERTYPECODE tinyint = 0,
@OTHERASSETVALUEMULTIPLIERCUTOFFVALUE money = 0,
@OTHERASSETVALUEMULTIPLIER decimal(20,4) = 0,
@OTHERASSETVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@OTHERASSETVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@OTHERASSETVALUECODE tinyint = 0,
@NONPROFITAFFILIATIONINCLUDE bit = 0,
@NONPROFITAFFILIATIONCONFIRMEDONLY bit = 0,
@NONPROFITAFFILIATIONVALUEMULTIPLIERTYPECODE tinyint = 0,
@NONPROFITAFFILIATIONVALUEMULTIPLIERCUTOFFVALUE money = 0,
@NONPROFITAFFILIATIONVALUEMULTIPLIER decimal(20,4) = 0,
@NONPROFITAFFILIATIONVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@NONPROFITAFFILIATIONVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@NONPROFITAFFILIATIONVALUECODE tinyint = 0,
@PRIVATEFOUNDATIONINCLUDE bit = 0,
@PRIVATEFOUNDATIONCONFIRMEDONLY bit = 0,
@PRIVATEFOUNDATIONVALUEMULTIPLIERTYPECODE tinyint = 0,
@PRIVATEFOUNDATIONVALUEMULTIPLIERCUTOFFVALUE money = 0,
@PRIVATEFOUNDATIONVALUEMULTIPLIER decimal(20,4) = 0,
@PRIVATEFOUNDATIONVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@PRIVATEFOUNDATIONVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@PRIVATEFOUNDATIONVALUECODE tinyint = 0,
@APPLYFORMULA bit = 0,
@APPLYTOCODE tinyint = 0,
@CONSTITUENTID uniqueidentifier = null,
@RESEARCHGROUPID uniqueidentifier = null,
@IDSETREGISTERID uniqueidentifier = null,
@OTHERSCANMODIFY bit = 1
) with execute as owner as
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @ID is null
set @ID = newid();
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @REALESTATEINCLUDEASSESSMENTRECORDS bit
declare @REALESTATEINCLUDDEEDRECORDS bit
select @REALESTATEINCLUDEASSESSMENTRECORDS =
CASE @REALESTATEINCLUDEASSESSMENTANDDEEDRECORDS
WHEN 0 THEN 1
WHEN 1 THEN 1
WHEN 2 THEN 0
ELSE 0
END,
@REALESTATEINCLUDDEEDRECORDS =
CASE @REALESTATEINCLUDEASSESSMENTANDDEEDRECORDS
WHEN 0 THEN 1
WHEN 1 THEN 0
WHEN 2 THEN 1
ELSE 0
END
begin try
if @ISDEFAULT = 1
update
dbo.WEALTHCAPACITYFORMULA
set
ISDEFAULT = 0,
DATECHANGED = @CURRENTDATE,
CHANGEDBYID = @CHANGEAGENTID
where
ISDEFAULT = 1;
insert into dbo.WEALTHCAPACITYFORMULA (
ID,
NAME,
DESCRIPTION,
ISDEFAULT,
ASSETCALCULATIONMETHODCODE,
MAJORGIVINGMULTIPLIERTYPECODE,
MAJORGIVINGSETVALUEMULTIPLIER,
MAJORGIVINGOVER70MULTIPLIER,
MAJORGIVING60TO69MULTIPLIER,
MAJORGIVING50TO59MULTIPLIER,
MAJORGIVING40TO49MULTIPLIER,
MAJORGIVINGUNDER40MULTIPLIER,
MAJORGIVINGAGEUNKNOWNMULTIPLIER,
REALESTATEINCLUDE,
REALESTATECONFIRMEDONLY,
REALESTATEVALUEMULTIPLIERTYPECODE,
REALESTATEVALUEMULTIPLIERCUTOFFVALUE,
REALESTATEVALUEMULTIPLIER,
REALESTATEVALUELESSTHANCUTOFFMULTIPLIER,
REALESTATEVALUEGREATERTHANCUTOFFMULTIPLIER,
REALESTATEINCLUDEASSESSMENTRECORDS,
REALESTATEINCLUDDEEDRECORDS,
REALESTATEAGGREGATETYPECODE,
REALESTATEUSETOTALMARKETVALUE,
REALESTATEUSETOTALASSESSEDVALUE,
REALESTATEUSESALEPRICE,
REALESTATEUSELOANAMOUNT,
BUSINESSOWNERSHIPINCLUDE,
BUSINESSOWNERSHIPCONFIRMEDONLY,
BUSINESSOWNERSHIPVALUEMULTIPLIERTYPECODE,
BUSINESSOWNERSHIPVALUEMULTIPLIERCUTOFFVALUE,
BUSINESSOWNERSHIPVALUEMULTIPLIER,
BUSINESSOWNERSHIPVALUELESSTHANCUTOFFMULTIPLIER,
BUSINESSOWNERSHIPVALUEGREATERTHANCUTOFFMULTIPLIER,
BUSINESSOWNERSHIPVALUECODE,
SECURITIESINCLUDE,
SECURITIESCONFIRMEDONLY,
SECURITIESVALUEMULTIPLIERTYPECODE,
SECURITIESVALUEMULTIPLIERCUTOFFVALUE,
SECURITIESVALUEMULTIPLIER,
SECURITIESVALUELESSTHANCUTOFFMULTIPLIER,
SECURITIESVALUEGREATERTHANCUTOFFMULTIPLIER,
SECURITIESVALUECODE,
AFFLUENCEINDICATORINCLUDE,
AFFLUENCEINDICATORCONFIRMEDONLY,
AFFLUENCEINDICATORVALUEMULTIPLIERTYPECODE,
AFFLUENCEINDICATORVALUEMULTIPLIERCUTOFFVALUE,
AFFLUENCEINDICATORVALUEMULTIPLIER,
AFFLUENCEINDICATORVALUELESSTHANCUTOFFMULTIPLIER,
AFFLUENCEINDICATORVALUEGREATERTHANCUTOFFMULTIPLIER,
AFFLUENCEINDICATORVALUECODE,
INCOMECOMPENSATIONINCLUDE,
INCOMECOMPENSATIONCONFIRMEDONLY,
INCOMECOMPENSATIONVALUEMULTIPLIERTYPECODE,
INCOMECOMPENSATIONVALUEMULTIPLIERCUTOFFVALUE,
INCOMECOMPENSATIONVALUEMULTIPLIER,
INCOMECOMPENSATIONVALUELESSTHANCUTOFFMULTIPLIER,
INCOMECOMPENSATIONVALUEGREATERTHANCUTOFFMULTIPLIER,
INCOMECOMPENSATIONINCLUDESALARY,
INCOMECOMPENSATIONINCLUDEBONUS,
INCOMECOMPENSATIONINCLUDESHORTTERMCOMP,
INCOMECOMPENSATIONINCLUDELONGTERMCOMP,
INCOMECOMPENSATIONINCLUDEEXERCISEDOPTIONS,
INCOMECOMPENSATIONINCLUDEUNEXERCISEDOPTIONS,
INCOMECOMPENSATIONINCLUDEUNEXERCISABLEOPTIONS,
OTHERASSETINCLUDE,
OTHERASSETCONFIRMEDONLY,
OTHERASSETVALUEMULTIPLIERTYPECODE,
OTHERASSETVALUEMULTIPLIERCUTOFFVALUE,
OTHERASSETVALUEMULTIPLIER,
OTHERASSETVALUELESSTHANCUTOFFMULTIPLIER,
OTHERASSETVALUEGREATERTHANCUTOFFMULTIPLIER,
OTHERASSETVALUECODE,
NONPROFITAFFILIATIONINCLUDE,
NONPROFITAFFILIATIONCONFIRMEDONLY,
NONPROFITAFFILIATIONVALUEMULTIPLIERTYPECODE,
NONPROFITAFFILIATIONVALUEMULTIPLIERCUTOFFVALUE,
NONPROFITAFFILIATIONVALUEMULTIPLIER,
NONPROFITAFFILIATIONVALUELESSTHANCUTOFFMULTIPLIER,
NONPROFITAFFILIATIONVALUEGREATERTHANCUTOFFMULTIPLIER,
NONPROFITAFFILIATIONVALUECODE,
PRIVATEFOUNDATIONINCLUDE,
PRIVATEFOUNDATIONCONFIRMEDONLY,
PRIVATEFOUNDATIONVALUEMULTIPLIERTYPECODE,
PRIVATEFOUNDATIONVALUEMULTIPLIERCUTOFFVALUE,
PRIVATEFOUNDATIONVALUEMULTIPLIER,
PRIVATEFOUNDATIONVALUELESSTHANCUTOFFMULTIPLIER,
PRIVATEFOUNDATIONVALUEGREATERTHANCUTOFFMULTIPLIER,
PRIVATEFOUNDATIONVALUECODE,
OWNERID,
OTHERSCANMODIFY,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
values (
@ID,
@NAME,
@DESCRIPTION,
@ISDEFAULT,
@ASSETCALCULATIONMETHODCODE,
@MAJORGIVINGMULTIPLIERTYPECODE,
@MAJORGIVINGSETVALUEMULTIPLIER,
@MAJORGIVINGOVER70MULTIPLIER,
@MAJORGIVING60TO69MULTIPLIER,
@MAJORGIVING50TO59MULTIPLIER,
@MAJORGIVING40TO49MULTIPLIER,
@MAJORGIVINGUNDER40MULTIPLIER,
@MAJORGIVINGAGEUNKNOWNMULTIPLIER,
@REALESTATEINCLUDE,
@REALESTATECONFIRMEDONLY,
@REALESTATEVALUEMULTIPLIERTYPECODE,
@REALESTATEVALUEMULTIPLIERCUTOFFVALUE,
@REALESTATEVALUEMULTIPLIER,
@REALESTATEVALUELESSTHANCUTOFFMULTIPLIER,
@REALESTATEVALUEGREATERTHANCUTOFFMULTIPLIER,
@REALESTATEINCLUDEASSESSMENTRECORDS,
@REALESTATEINCLUDDEEDRECORDS,
@REALESTATEAGGREGATETYPECODE,
@REALESTATEUSETOTALMARKETVALUE,
@REALESTATEUSETOTALASSESSEDVALUE,
@REALESTATEUSESALEPRICE,
@REALESTATEUSELOANAMOUNT,
@BUSINESSOWNERSHIPINCLUDE,
@BUSINESSOWNERSHIPCONFIRMEDONLY,
@BUSINESSOWNERSHIPVALUEMULTIPLIERTYPECODE,
@BUSINESSOWNERSHIPVALUEMULTIPLIERCUTOFFVALUE,
@BUSINESSOWNERSHIPVALUEMULTIPLIER,
@BUSINESSOWNERSHIPVALUELESSTHANCUTOFFMULTIPLIER,
@BUSINESSOWNERSHIPVALUEGREATERTHANCUTOFFMULTIPLIER,
@BUSINESSOWNERSHIPVALUECODE,
@SECURITIESINCLUDE,
@SECURITIESCONFIRMEDONLY,
@SECURITIESVALUEMULTIPLIERTYPECODE,
@SECURITIESVALUEMULTIPLIERCUTOFFVALUE,
@SECURITIESVALUEMULTIPLIER,
@SECURITIESVALUELESSTHANCUTOFFMULTIPLIER,
@SECURITIESVALUEGREATERTHANCUTOFFMULTIPLIER,
@SECURITIESVALUECODE,
@AFFLUENCEINDICATORINCLUDE,
@AFFLUENCEINDICATORCONFIRMEDONLY,
@AFFLUENCEINDICATORVALUEMULTIPLIERTYPECODE,
@AFFLUENCEINDICATORVALUEMULTIPLIERCUTOFFVALUE,
@AFFLUENCEINDICATORVALUEMULTIPLIER,
@AFFLUENCEINDICATORVALUELESSTHANCUTOFFMULTIPLIER,
@AFFLUENCEINDICATORVALUEGREATERTHANCUTOFFMULTIPLIER,
@AFFLUENCEINDICATORVALUECODE,
@INCOMECOMPENSATIONINCLUDE,
@INCOMECOMPENSATIONCONFIRMEDONLY,
@INCOMECOMPENSATIONVALUEMULTIPLIERTYPECODE,
@INCOMECOMPENSATIONVALUEMULTIPLIERCUTOFFVALUE,
@INCOMECOMPENSATIONVALUEMULTIPLIER,
@INCOMECOMPENSATIONVALUELESSTHANCUTOFFMULTIPLIER,
@INCOMECOMPENSATIONVALUEGREATERTHANCUTOFFMULTIPLIER,
@INCOMECOMPENSATIONINCLUDESALARY,
@INCOMECOMPENSATIONINCLUDEBONUS,
@INCOMECOMPENSATIONINCLUDESHORTTERMCOMP,
@INCOMECOMPENSATIONINCLUDELONGTERMCOMP,
@INCOMECOMPENSATIONINCLUDEEXERCISEDOPTIONS,
@INCOMECOMPENSATIONINCLUDEUNEXERCISEDOPTIONS,
@INCOMECOMPENSATIONINCLUDEUNEXERCISABLEOPTIONS,
@OTHERASSETINCLUDE,
@OTHERASSETCONFIRMEDONLY,
@OTHERASSETVALUEMULTIPLIERTYPECODE,
@OTHERASSETVALUEMULTIPLIERCUTOFFVALUE,
@OTHERASSETVALUEMULTIPLIER,
@OTHERASSETVALUELESSTHANCUTOFFMULTIPLIER,
@OTHERASSETVALUEGREATERTHANCUTOFFMULTIPLIER,
@OTHERASSETVALUECODE,
@NONPROFITAFFILIATIONINCLUDE,
@NONPROFITAFFILIATIONCONFIRMEDONLY,
@NONPROFITAFFILIATIONVALUEMULTIPLIERTYPECODE,
@NONPROFITAFFILIATIONVALUEMULTIPLIERCUTOFFVALUE,
@NONPROFITAFFILIATIONVALUEMULTIPLIER,
@NONPROFITAFFILIATIONVALUELESSTHANCUTOFFMULTIPLIER,
@NONPROFITAFFILIATIONVALUEGREATERTHANCUTOFFMULTIPLIER,
@NONPROFITAFFILIATIONVALUECODE,
@PRIVATEFOUNDATIONINCLUDE,
@PRIVATEFOUNDATIONCONFIRMEDONLY,
@PRIVATEFOUNDATIONVALUEMULTIPLIERTYPECODE,
@PRIVATEFOUNDATIONVALUEMULTIPLIERCUTOFFVALUE,
@PRIVATEFOUNDATIONVALUEMULTIPLIER,
@PRIVATEFOUNDATIONVALUELESSTHANCUTOFFMULTIPLIER,
@PRIVATEFOUNDATIONVALUEGREATERTHANCUTOFFMULTIPLIER,
@PRIVATEFOUNDATIONVALUECODE,
@CURRENTAPPUSERID,
@OTHERSCANMODIFY,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
--apply wealth capacity formula
if @APPLYFORMULA = 1 and @APPLYTOCODE = 0 and @CONSTITUENTID is not null begin
update
dbo.WEALTHCAPACITY
set
WEALTHCAPACITYFORMULAID = @ID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.WEALTHCAPACITY
where
WEALTHCAPACITY.ID = @CONSTITUENTID and
WEALTHCAPACITY.CONFIRMED = 0;
insert into dbo.WEALTHCAPACITY (
ID,
WEALTHCAPACITYFORMULAID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
C.ID,
@ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.CONSTITUENT C
left join
dbo.WEALTHCAPACITY WC
on
WC.ID = C.ID
where
C.ID = @CONSTITUENTID and
WC.ID is null;
end
else if @APPLYFORMULA = 1 and @APPLYTOCODE = 1 and @RESEARCHGROUPID is not null begin
update
dbo.WEALTHCAPACITY
set
WEALTHCAPACITYFORMULAID = @ID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.WEALTHCAPACITY
left join
dbo.RESEARCHGROUPMEMBER RGM
on
RGM.CONSTITUENTID = WEALTHCAPACITY.ID
where
RGM.RESEARCHGROUPID = @RESEARCHGROUPID and
WEALTHCAPACITY.CONFIRMED = 0;
insert into dbo.WEALTHCAPACITY (
ID,
WEALTHCAPACITYFORMULAID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
RGM.CONSTITUENTID,
@ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
dbo.RESEARCHGROUPMEMBER RGM
left join
dbo.WEALTHCAPACITY WC
on
WC.ID = RGM.CONSTITUENTID
where
RGM.RESEARCHGROUPID = @RESEARCHGROUPID and
WC.ID is null;
end
else if @APPLYFORMULA = 1 and @APPLYTOCODE = 2 and @IDSETREGISTERID is not null begin
declare @DBOBJECTNAME nvarchar(128);
declare @DBOBJECTTYPE smallint;
select
@DBOBJECTNAME = IDSETREGISTER.DBOBJECTNAME,
@DBOBJECTTYPE = IDSETREGISTER.OBJECTTYPE
from
dbo.IDSETREGISTER
where
IDSETREGISTER.ID = @IDSETREGISTERID;
if not @DBOBJECTNAME is null and @DBOBJECTNAME <> '' begin
if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @ID) + ''')';
declare @SQLTOEXEC nvarchar(max);
set @SQLTOEXEC = N'set nocount on;
update
dbo.WEALTHCAPACITY
set
WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CURRENTDATE
from
dbo.WEALTHCAPACITY
inner join
' + nchar(13);
set @SQLTOEXEC = @SQLTOEXEC + @DBOBJECTNAME + N' as SELECTION
on
WEALTHCAPACITY.[ID] = SELECTION.[ID]
where
WEALTHCAPACITY.CONFIRMED = 0;
insert into dbo.WEALTHCAPACITY (
ID,
WEALTHCAPACITYFORMULAID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)
select
SELECTION.ID,
@WEALTHCAPACITYFORMULAID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
from
' + nchar(13);
set @SQLTOEXEC = @SQLTOEXEC + @DBOBJECTNAME + N' as SELECTION
inner join
dbo.WEALTHCAPACITY WC
on
WC.ID = SELECTION.ID
where
WC.ID is null;' + nchar(13);
exec sp_executesql @SQLTOEXEC,
N'@WEALTHCAPACITYFORMULAID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@CURRENTDATE datetime',
@WEALTHCAPACITYFORMULAID = @ID,
@CHANGEAGENTID=@CHANGEAGENTID,
@CURRENTDATE=@CURRENTDATE;
end
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;