USP_DATAFORMTEMPLATE_ADD_WEALTHCAPACITYFORMULA_2
The save procedure used by the add dataform template "Wealth Capacity Formula Add Form 2".
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 |
| @REALESTATEAGGREGATETYPECODE | tinyint | IN | Real estate aggregate type |
| @REALESTATEUSEPROPERTYVALUATION | bit | IN | Property valuation |
| @REALESTATEUSEPROPERTYVALUEESTIMATE | bit | IN | Property value estimate |
| @REALESTATESOURCES | xml | IN | Data source(s) to include |
| @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 |
| @BUSINESSOWNERSHIPSOURCES | xml | IN | Data source(s) to include |
| @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 |
| @SECURITIESSOURCES | xml | IN | Data source(s) to include |
| @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 |
| @INCOMECOMPENSATIONSOURCES | xml | IN | Data source(s) to include |
| @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 |
| @OTHERASSETSOURCES | xml | IN | Data source(s) to include |
| @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 |
| @NONPROFITAFFILIATIONSOURCES | xml | IN | Data source(s) to include |
| @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 |
| @PRIVATEFOUNDATIONSOURCES | xml | IN | Data source(s) to include |
| @APPLYFORMULA | bit | IN | Apply formula to |
| @APPLYTOCODE | tinyint | IN | Apply to |
| @CONSTITUENTID | uniqueidentifier | IN | Constituent |
| @RESEARCHGROUPID | uniqueidentifier | IN | Research group |
| @IDSETREGISTERID | uniqueidentifier | IN | Selection |
| @OTHERSCANMODIFY | bit | IN | Other users may modify this formula |
| @REALESTATEINCLUDEHISTORICAL | bit | IN | |
| @INCOMECOMPENSATIONINCLUDEHISTORICAL | bit | IN | |
| @PHILANTHROPICGIFTINCLUDE | bit | IN | |
| @PHILANTHROPICGIFTCONFIRMEDONLY | bit | IN | |
| @PHILANTHROPICGIFTVALUEMULTIPLIERTYPECODE | tinyint | IN | |
| @PHILANTHROPICGIFTVALUEMULTIPLIERCUTOFFVALUE | money | IN | |
| @PHILANTHROPICGIFTVALUEMULTIPLIER | decimal(20, 4) | IN | |
| @PHILANTHROPICGIFTVALUELESSTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | |
| @PHILANTHROPICGIFTVALUEGREATERTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | |
| @PHILANTHROPICGIFTVALUECODE | tinyint | IN | |
| @PHILANTHROPICGIFTSOURCES | xml | IN | |
| @POLITICALDONATIONINCLUDE | bit | IN | |
| @POLITICALDONATIONCONFIRMEDONLY | bit | IN | |
| @POLITICALDONATIONVALUEMULTIPLIERTYPECODE | tinyint | IN | |
| @POLITICALDONATIONVALUEMULTIPLIERCUTOFFVALUE | money | IN | |
| @POLITICALDONATIONVALUEMULTIPLIER | decimal(20, 4) | IN | |
| @POLITICALDONATIONVALUELESSTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | |
| @POLITICALDONATIONVALUEGREATERTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | |
| @POLITICALDONATIONVALUECODE | tinyint | IN | |
| @POLITICALDONATIONSOURCES | xml | IN | |
| @GIVINGTOMYORGINCLUDE | bit | IN | |
| @GIVINGTOMYORGVALUEMULTIPLIERTYPECODE | tinyint | IN | |
| @GIVINGTOMYORGVALUEMULTIPLIERCUTOFFVALUE | money | IN | |
| @GIVINGTOMYORGVALUEMULTIPLIER | decimal(20, 4) | IN | |
| @GIVINGTOMYORGVALUELESSTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | |
| @GIVINGTOMYORGVALUEGREATERTHANCUTOFFMULTIPLIER | decimal(20, 4) | IN | |
| @GIVINGTOMYORGVALUECODE | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_WEALTHCAPACITYFORMULA_2 (
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier,
@NAME nvarchar(100),
@DESCRIPTION nvarchar(255) = null,
@ISDEFAULT bit = 0,
@ASSETCALCULATIONMETHODCODE tinyint = 5,
@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,
@REALESTATEAGGREGATETYPECODE tinyint = 0,
@REALESTATEUSEPROPERTYVALUATION bit = 0,
@REALESTATEUSEPROPERTYVALUEESTIMATE bit = 0,
@REALESTATESOURCES xml = null,
@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,
@BUSINESSOWNERSHIPSOURCES xml = null,
@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,
@SECURITIESSOURCES xml = null,
@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,
@INCOMECOMPENSATIONSOURCES xml = null,
@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,
@OTHERASSETSOURCES xml = null,
@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,
@NONPROFITAFFILIATIONSOURCES xml = null,
@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,
@PRIVATEFOUNDATIONSOURCES xml = null,
@APPLYFORMULA bit = 0,
@APPLYTOCODE tinyint = 0,
@CONSTITUENTID uniqueidentifier = null,
@RESEARCHGROUPID uniqueidentifier = null,
@IDSETREGISTERID uniqueidentifier = null,
@OTHERSCANMODIFY bit = 1,
@REALESTATEINCLUDEHISTORICAL bit = 0,
@INCOMECOMPENSATIONINCLUDEHISTORICAL bit = 0,
@PHILANTHROPICGIFTINCLUDE bit = 0,
@PHILANTHROPICGIFTCONFIRMEDONLY bit = 0,
@PHILANTHROPICGIFTVALUEMULTIPLIERTYPECODE tinyint = 0,
@PHILANTHROPICGIFTVALUEMULTIPLIERCUTOFFVALUE money = 0,
@PHILANTHROPICGIFTVALUEMULTIPLIER decimal(20,4) = 0,
@PHILANTHROPICGIFTVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@PHILANTHROPICGIFTVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@PHILANTHROPICGIFTVALUECODE tinyint = 0,
@PHILANTHROPICGIFTSOURCES xml = null,
@POLITICALDONATIONINCLUDE bit = 0,
@POLITICALDONATIONCONFIRMEDONLY bit = 0,
@POLITICALDONATIONVALUEMULTIPLIERTYPECODE tinyint = 0,
@POLITICALDONATIONVALUEMULTIPLIERCUTOFFVALUE money = 0,
@POLITICALDONATIONVALUEMULTIPLIER decimal(20,4) = 0,
@POLITICALDONATIONVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@POLITICALDONATIONVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@POLITICALDONATIONVALUECODE tinyint = 0,
@POLITICALDONATIONSOURCES xml = null,
@GIVINGTOMYORGINCLUDE bit = 0,
@GIVINGTOMYORGVALUEMULTIPLIERTYPECODE tinyint = 0,
@GIVINGTOMYORGVALUEMULTIPLIERCUTOFFVALUE money = 0,
@GIVINGTOMYORGVALUEMULTIPLIER decimal(20,4) = 0,
@GIVINGTOMYORGVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@GIVINGTOMYORGVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4) = 0,
@GIVINGTOMYORGVALUECODE tinyint = 0
) 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;
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,
REALESTATEAGGREGATETYPECODE,
REALESTATEUSEPROPERTYVALUATION,
REALESTATEUSEPROPERTYVALUEESTIMATE,
REALESTATEINCLUDEHISTORICAL,
BUSINESSOWNERSHIPINCLUDE,
BUSINESSOWNERSHIPCONFIRMEDONLY,
BUSINESSOWNERSHIPVALUEMULTIPLIERTYPECODE,
BUSINESSOWNERSHIPVALUEMULTIPLIERCUTOFFVALUE,
BUSINESSOWNERSHIPVALUEMULTIPLIER,
BUSINESSOWNERSHIPVALUELESSTHANCUTOFFMULTIPLIER,
BUSINESSOWNERSHIPVALUEGREATERTHANCUTOFFMULTIPLIER,
BUSINESSOWNERSHIPVALUECODE,
SECURITIESINCLUDE,
SECURITIESCONFIRMEDONLY,
SECURITIESVALUEMULTIPLIERTYPECODE,
SECURITIESVALUEMULTIPLIERCUTOFFVALUE,
SECURITIESVALUEMULTIPLIER,
SECURITIESVALUELESSTHANCUTOFFMULTIPLIER,
SECURITIESVALUEGREATERTHANCUTOFFMULTIPLIER,
SECURITIESVALUECODE,
INCOMECOMPENSATIONINCLUDE,
INCOMECOMPENSATIONCONFIRMEDONLY,
INCOMECOMPENSATIONVALUEMULTIPLIERTYPECODE,
INCOMECOMPENSATIONVALUEMULTIPLIERCUTOFFVALUE,
INCOMECOMPENSATIONVALUEMULTIPLIER,
INCOMECOMPENSATIONVALUELESSTHANCUTOFFMULTIPLIER,
INCOMECOMPENSATIONVALUEGREATERTHANCUTOFFMULTIPLIER,
INCOMECOMPENSATIONINCLUDESALARY,
INCOMECOMPENSATIONINCLUDEBONUS,
INCOMECOMPENSATIONINCLUDESHORTTERMCOMP,
INCOMECOMPENSATIONINCLUDELONGTERMCOMP,
INCOMECOMPENSATIONINCLUDEEXERCISEDOPTIONS,
INCOMECOMPENSATIONINCLUDEUNEXERCISEDOPTIONS,
INCOMECOMPENSATIONINCLUDEUNEXERCISABLEOPTIONS,
INCOMECOMPENSATIONINCLUDEHISTORICAL,
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,
PHILANTHROPICGIFTINCLUDE,
PHILANTHROPICGIFTCONFIRMEDONLY,
PHILANTHROPICGIFTVALUEMULTIPLIERTYPECODE,
PHILANTHROPICGIFTVALUEMULTIPLIERCUTOFFVALUE,
PHILANTHROPICGIFTVALUEMULTIPLIER,
PHILANTHROPICGIFTVALUELESSTHANCUTOFFMULTIPLIER,
PHILANTHROPICGIFTVALUEGREATERTHANCUTOFFMULTIPLIER,
PHILANTHROPICGIFTVALUECODE,
POLITICALDONATIONINCLUDE,
POLITICALDONATIONCONFIRMEDONLY,
POLITICALDONATIONVALUEMULTIPLIERTYPECODE,
POLITICALDONATIONVALUEMULTIPLIERCUTOFFVALUE,
POLITICALDONATIONVALUEMULTIPLIER,
POLITICALDONATIONVALUELESSTHANCUTOFFMULTIPLIER,
POLITICALDONATIONVALUEGREATERTHANCUTOFFMULTIPLIER,
POLITICALDONATIONVALUECODE,
GIVINGTOMYORGINCLUDE,
GIVINGTOMYORGVALUEMULTIPLIERTYPECODE,
GIVINGTOMYORGVALUEMULTIPLIERCUTOFFVALUE,
GIVINGTOMYORGVALUEMULTIPLIER,
GIVINGTOMYORGVALUELESSTHANCUTOFFMULTIPLIER,
GIVINGTOMYORGVALUEGREATERTHANCUTOFFMULTIPLIER,
GIVINGTOMYORGVALUECODE
)
values (
@ID,
@NAME,
@DESCRIPTION,
@ISDEFAULT,
@ASSETCALCULATIONMETHODCODE,
@MAJORGIVINGMULTIPLIERTYPECODE,
@MAJORGIVINGSETVALUEMULTIPLIER,
@MAJORGIVINGOVER70MULTIPLIER,
@MAJORGIVING60TO69MULTIPLIER,
@MAJORGIVING50TO59MULTIPLIER,
@MAJORGIVING40TO49MULTIPLIER,
@MAJORGIVINGUNDER40MULTIPLIER,
@MAJORGIVINGAGEUNKNOWNMULTIPLIER,
@REALESTATEINCLUDE,
@REALESTATECONFIRMEDONLY,
@REALESTATEVALUEMULTIPLIERTYPECODE,
@REALESTATEVALUEMULTIPLIERCUTOFFVALUE,
@REALESTATEVALUEMULTIPLIER,
@REALESTATEVALUELESSTHANCUTOFFMULTIPLIER,
@REALESTATEVALUEGREATERTHANCUTOFFMULTIPLIER,
@REALESTATEAGGREGATETYPECODE,
@REALESTATEUSEPROPERTYVALUATION,
@REALESTATEUSEPROPERTYVALUEESTIMATE,
@REALESTATEINCLUDEHISTORICAL,
@BUSINESSOWNERSHIPINCLUDE,
@BUSINESSOWNERSHIPCONFIRMEDONLY,
@BUSINESSOWNERSHIPVALUEMULTIPLIERTYPECODE,
@BUSINESSOWNERSHIPVALUEMULTIPLIERCUTOFFVALUE,
@BUSINESSOWNERSHIPVALUEMULTIPLIER,
@BUSINESSOWNERSHIPVALUELESSTHANCUTOFFMULTIPLIER,
@BUSINESSOWNERSHIPVALUEGREATERTHANCUTOFFMULTIPLIER,
@BUSINESSOWNERSHIPVALUECODE,
@SECURITIESINCLUDE,
@SECURITIESCONFIRMEDONLY,
@SECURITIESVALUEMULTIPLIERTYPECODE,
@SECURITIESVALUEMULTIPLIERCUTOFFVALUE,
@SECURITIESVALUEMULTIPLIER,
@SECURITIESVALUELESSTHANCUTOFFMULTIPLIER,
@SECURITIESVALUEGREATERTHANCUTOFFMULTIPLIER,
@SECURITIESVALUECODE,
@INCOMECOMPENSATIONINCLUDE,
@INCOMECOMPENSATIONCONFIRMEDONLY,
@INCOMECOMPENSATIONVALUEMULTIPLIERTYPECODE,
@INCOMECOMPENSATIONVALUEMULTIPLIERCUTOFFVALUE,
@INCOMECOMPENSATIONVALUEMULTIPLIER,
@INCOMECOMPENSATIONVALUELESSTHANCUTOFFMULTIPLIER,
@INCOMECOMPENSATIONVALUEGREATERTHANCUTOFFMULTIPLIER,
@INCOMECOMPENSATIONINCLUDESALARY,
@INCOMECOMPENSATIONINCLUDEBONUS,
@INCOMECOMPENSATIONINCLUDESHORTTERMCOMP,
@INCOMECOMPENSATIONINCLUDELONGTERMCOMP,
@INCOMECOMPENSATIONINCLUDEEXERCISEDOPTIONS,
@INCOMECOMPENSATIONINCLUDEUNEXERCISEDOPTIONS,
@INCOMECOMPENSATIONINCLUDEUNEXERCISABLEOPTIONS,
@INCOMECOMPENSATIONINCLUDEHISTORICAL,
@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,
@PHILANTHROPICGIFTINCLUDE,
@PHILANTHROPICGIFTCONFIRMEDONLY,
@PHILANTHROPICGIFTVALUEMULTIPLIERTYPECODE,
@PHILANTHROPICGIFTVALUEMULTIPLIERCUTOFFVALUE,
@PHILANTHROPICGIFTVALUEMULTIPLIER,
@PHILANTHROPICGIFTVALUELESSTHANCUTOFFMULTIPLIER,
@PHILANTHROPICGIFTVALUEGREATERTHANCUTOFFMULTIPLIER,
@PHILANTHROPICGIFTVALUECODE,
@POLITICALDONATIONINCLUDE,
@POLITICALDONATIONCONFIRMEDONLY,
@POLITICALDONATIONVALUEMULTIPLIERTYPECODE,
@POLITICALDONATIONVALUEMULTIPLIERCUTOFFVALUE,
@POLITICALDONATIONVALUEMULTIPLIER,
@POLITICALDONATIONVALUELESSTHANCUTOFFMULTIPLIER,
@POLITICALDONATIONVALUEGREATERTHANCUTOFFMULTIPLIER,
@POLITICALDONATIONVALUECODE,
@GIVINGTOMYORGINCLUDE,
@GIVINGTOMYORGVALUEMULTIPLIERTYPECODE,
@GIVINGTOMYORGVALUEMULTIPLIERCUTOFFVALUE,
@GIVINGTOMYORGVALUEMULTIPLIER,
@GIVINGTOMYORGVALUELESSTHANCUTOFFMULTIPLIER,
@GIVINGTOMYORGVALUEGREATERTHANCUTOFFMULTIPLIER,
@GIVINGTOMYORGVALUECODE
);
exec dbo.USP_WEALTHCAPACITYFORMULA_REALESTATE_GETSOURCES_UPDATEFROMXML @ID, @REALESTATESOURCES, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_WEALTHCAPACITYFORMULA_SECURITIES_GETSOURCES_UPDATEFROMXML @ID, @SECURITIESSOURCES, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_WEALTHCAPACITYFORMULA_INCOMECOMPENSATION_GETSOURCES_UPDATEFROMXML @ID, @INCOMECOMPENSATIONSOURCES, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_WEALTHCAPACITYFORMULA_NONPROFITAFFILIATION_GETSOURCES_UPDATEFROMXML @ID, @NONPROFITAFFILIATIONSOURCES, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_WEALTHCAPACITYFORMULA_OTHERASSET_GETSOURCES_UPDATEFROMXML @ID, @OTHERASSETSOURCES, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_WEALTHCAPACITYFORMULA_PRIVATEFOUNDATION_GETSOURCES_UPDATEFROMXML @ID, @PRIVATEFOUNDATIONSOURCES, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_WEALTHCAPACITYFORMULA_BUSINESSOWNERSHIP_GETSOURCES_UPDATEFROMXML @ID, @BUSINESSOWNERSHIPSOURCES, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_WEALTHCAPACITYFORMULA_PHILANTHROPICGIFT_GETSOURCES_UPDATEFROMXML @ID, @PHILANTHROPICGIFTSOURCES, @CHANGEAGENTID, @CURRENTDATE;
exec dbo.USP_WEALTHCAPACITYFORMULA_POLITICALDONATION_GETSOURCES_UPDATEFROMXML @ID, @POLITICALDONATIONSOURCES, @CHANGEAGENTID, @CURRENTDATE;
--apply 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
left 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;