USP_CONSTITUENT_WEALTHCAPACITYVALUES_FROMFORMULA_2
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @WEALTHID | uniqueidentifier | IN | |
| @WEALTHCAPACITYFORMULAID | uniqueidentifier | IN | |
| @ESTIMATEDWEALTHVALUE | money | INOUT | |
| @MAJORGIVINGCAPACITYVALUE | money | INOUT | |
| @MAJORGIVINGCAPACITYBASISVALUE | nvarchar(250) | INOUT |
Definition
Copy
create procedure dbo.USP_CONSTITUENT_WEALTHCAPACITYVALUES_FROMFORMULA_2 (
@WEALTHID uniqueidentifier,
@WEALTHCAPACITYFORMULAID uniqueidentifier,
@ESTIMATEDWEALTHVALUE money output,
@MAJORGIVINGCAPACITYVALUE money output,
@MAJORGIVINGCAPACITYBASISVALUE nvarchar(250) output
) as
set nocount on;
declare @ISGROUP bit;
select @ISGROUP = ISGROUP from dbo.CONSTITUENT where ID = @WEALTHID;
if @WEALTHID is not null and @ISGROUP = 0 begin
declare @FORMULALOADED bit;
declare @ASSETCALCULATIONMETHODCODE tinyint;
declare @MAJORGIVINGMULTIPLIERTYPECODE tinyint;
declare @MAJORGIVINGSETVALUEMULTIPLIER decimal(20,4);
declare @MAJORGIVINGOVER70MULTIPLIER decimal(20,4);
declare @MAJORGIVING60TO69MULTIPLIER decimal(20,4);
declare @MAJORGIVING50TO59MULTIPLIER decimal(20,4);
declare @MAJORGIVING40TO49MULTIPLIER decimal(20,4);
declare @MAJORGIVINGUNDER40MULTIPLIER decimal(20,4);
declare @MAJORGIVINGAGEUNKNOWNMULTIPLIER decimal(20,4);
declare @REALESTATEINCLUDE bit;
declare @REALESTATECONFIRMEDONLY bit;
declare @REALESTATEVALUEMULTIPLIERTYPECODE tinyint;
declare @REALESTATEVALUEMULTIPLIERCUTOFFVALUE money;
declare @REALESTATEVALUEMULTIPLIER decimal(20,4);
declare @REALESTATEVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4);
declare @REALESTATEVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4);
declare @REALESTATEAGGREGATETYPECODE tinyint;
declare @REALESTATEUSEPROPERTYVALUATION bit;
declare @REALESTATEUSEPROPERTYVALUEESTIMATE bit;
declare @REALESTATEINCLUDEHISTORICAL bit;
declare @BUSINESSOWNERSHIPINCLUDE bit;
declare @BUSINESSOWNERSHIPCONFIRMEDONLY bit;
declare @BUSINESSOWNERSHIPVALUEMULTIPLIERTYPECODE tinyint;
declare @BUSINESSOWNERSHIPVALUEMULTIPLIERCUTOFFVALUE money;
declare @BUSINESSOWNERSHIPVALUEMULTIPLIER decimal(20,4);
declare @BUSINESSOWNERSHIPVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4);
declare @BUSINESSOWNERSHIPVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4);
declare @BUSINESSOWNERSHIPVALUECODE tinyint;
declare @SECURITIESINCLUDE bit;
declare @SECURITIESCONFIRMEDONLY bit;
declare @SECURITIESVALUEMULTIPLIERTYPECODE tinyint;
declare @SECURITIESVALUEMULTIPLIERCUTOFFVALUE money;
declare @SECURITIESVALUEMULTIPLIER decimal(20,4);
declare @SECURITIESVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4);
declare @SECURITIESVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4);
declare @SECURITIESVALUECODE tinyint;
declare @INCOMECOMPENSATIONINCLUDE bit;
declare @INCOMECOMPENSATIONCONFIRMEDONLY bit;
declare @INCOMECOMPENSATIONVALUEMULTIPLIERTYPECODE tinyint;
declare @INCOMECOMPENSATIONVALUEMULTIPLIERCUTOFFVALUE money;
declare @INCOMECOMPENSATIONVALUEMULTIPLIER decimal(20,4);
declare @INCOMECOMPENSATIONVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4);
declare @INCOMECOMPENSATIONVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4);
declare @INCOMECOMPENSATIONINCLUDESALARY bit;
declare @INCOMECOMPENSATIONINCLUDEBONUS bit;
declare @INCOMECOMPENSATIONINCLUDESHORTTERMCOMP bit;
declare @INCOMECOMPENSATIONINCLUDELONGTERMCOMP bit;
declare @INCOMECOMPENSATIONINCLUDEEXERCISEDOPTIONS bit;
declare @INCOMECOMPENSATIONINCLUDEUNEXERCISEDOPTIONS bit;
declare @INCOMECOMPENSATIONINCLUDEUNEXERCISABLEOPTIONS bit;
declare @INCOMECOMPENSATIONINCLUDEHISTORICAL bit;
declare @OTHERASSETINCLUDE bit;
declare @OTHERASSETCONFIRMEDONLY bit;
declare @OTHERASSETVALUEMULTIPLIERTYPECODE tinyint;
declare @OTHERASSETVALUEMULTIPLIERCUTOFFVALUE money;
declare @OTHERASSETVALUEMULTIPLIER decimal(20,4);
declare @OTHERASSETVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4);
declare @OTHERASSETVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4);
declare @OTHERASSETVALUECODE tinyint;
declare @NONPROFITAFFILIATIONINCLUDE bit;
declare @NONPROFITAFFILIATIONCONFIRMEDONLY bit;
declare @NONPROFITAFFILIATIONVALUEMULTIPLIERTYPECODE tinyint;
declare @NONPROFITAFFILIATIONVALUEMULTIPLIERCUTOFFVALUE money;
declare @NONPROFITAFFILIATIONVALUEMULTIPLIER decimal(20,4);
declare @NONPROFITAFFILIATIONVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4);
declare @NONPROFITAFFILIATIONVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4);
declare @NONPROFITAFFILIATIONVALUECODE tinyint;
declare @PRIVATEFOUNDATIONINCLUDE bit;
declare @PRIVATEFOUNDATIONCONFIRMEDONLY bit;
declare @PRIVATEFOUNDATIONVALUEMULTIPLIERTYPECODE tinyint;
declare @PRIVATEFOUNDATIONVALUEMULTIPLIERCUTOFFVALUE money;
declare @PRIVATEFOUNDATIONVALUEMULTIPLIER decimal(20,4);
declare @PRIVATEFOUNDATIONVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4);
declare @PRIVATEFOUNDATIONVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4);
declare @PRIVATEFOUNDATIONVALUECODE tinyint;
declare @PHILANTHROPICGIFTINCLUDE bit;
declare @PHILANTHROPICGIFTCONFIRMEDONLY bit;
declare @PHILANTHROPICGIFTVALUEMULTIPLIERTYPECODE tinyint;
declare @PHILANTHROPICGIFTVALUEMULTIPLIERCUTOFFVALUE money;
declare @PHILANTHROPICGIFTVALUEMULTIPLIER decimal(20,4);
declare @PHILANTHROPICGIFTVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4);
declare @PHILANTHROPICGIFTVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4);
declare @PHILANTHROPICGIFTVALUECODE tinyint;
declare @PHILANTHROPICGIFTSOURCES xml;
declare @POLITICALDONATIONINCLUDE bit;
declare @POLITICALDONATIONCONFIRMEDONLY bit;
declare @POLITICALDONATIONVALUEMULTIPLIERTYPECODE tinyint;
declare @POLITICALDONATIONVALUEMULTIPLIERCUTOFFVALUE money;
declare @POLITICALDONATIONVALUEMULTIPLIER decimal(20,4);
declare @POLITICALDONATIONVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4);
declare @POLITICALDONATIONVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4);
declare @POLITICALDONATIONVALUECODE tinyint;
declare @POLITICALDONATIONSOURCES xml;
declare @GIVINGTOMYORGINCLUDE bit;
declare @GIVINGTOMYORGVALUEMULTIPLIERTYPECODE tinyint;
declare @GIVINGTOMYORGVALUEMULTIPLIERCUTOFFVALUE money;
declare @GIVINGTOMYORGVALUEMULTIPLIER decimal(20,4);
declare @GIVINGTOMYORGVALUELESSTHANCUTOFFMULTIPLIER decimal(20,4);
declare @GIVINGTOMYORGVALUEGREATERTHANCUTOFFMULTIPLIER decimal(20,4);
declare @GIVINGTOMYORGVALUECODE tinyint;
declare @CURRENTDATE datetime;
declare @CURRENTYEAR UDT_YEAR;
set @FORMULALOADED = 0;
set @CURRENTDATE = getdate();
set @CURRENTYEAR = datepart(yy, @CURRENTDATE);
select
@FORMULALOADED = 1,
@ASSETCALCULATIONMETHODCODE = WCF.ASSETCALCULATIONMETHODCODE,
@MAJORGIVINGMULTIPLIERTYPECODE = WCF.MAJORGIVINGMULTIPLIERTYPECODE,
@MAJORGIVINGSETVALUEMULTIPLIER = WCF.MAJORGIVINGSETVALUEMULTIPLIER,
@MAJORGIVINGOVER70MULTIPLIER = WCF.MAJORGIVINGOVER70MULTIPLIER,
@MAJORGIVING60TO69MULTIPLIER = WCF.MAJORGIVING60TO69MULTIPLIER,
@MAJORGIVING50TO59MULTIPLIER = WCF.MAJORGIVING50TO59MULTIPLIER,
@MAJORGIVING40TO49MULTIPLIER = WCF.MAJORGIVING40TO49MULTIPLIER,
@MAJORGIVINGUNDER40MULTIPLIER = WCF.MAJORGIVINGUNDER40MULTIPLIER,
@MAJORGIVINGAGEUNKNOWNMULTIPLIER = WCF.MAJORGIVINGAGEUNKNOWNMULTIPLIER,
@REALESTATEINCLUDE = WCF.REALESTATEINCLUDE,
@REALESTATECONFIRMEDONLY = WCF.REALESTATECONFIRMEDONLY,
@REALESTATEVALUEMULTIPLIERTYPECODE = WCF.REALESTATEVALUEMULTIPLIERTYPECODE,
@REALESTATEVALUEMULTIPLIERCUTOFFVALUE = WCF.REALESTATEVALUEMULTIPLIERCUTOFFVALUE,
@REALESTATEVALUEMULTIPLIER = WCF.REALESTATEVALUEMULTIPLIER,
@REALESTATEVALUELESSTHANCUTOFFMULTIPLIER = WCF.REALESTATEVALUELESSTHANCUTOFFMULTIPLIER,
@REALESTATEVALUEGREATERTHANCUTOFFMULTIPLIER = WCF.REALESTATEVALUEGREATERTHANCUTOFFMULTIPLIER,
@REALESTATEAGGREGATETYPECODE = WCF.REALESTATEAGGREGATETYPECODE,
@REALESTATEUSEPROPERTYVALUATION = WCF.REALESTATEUSEPROPERTYVALUATION,
@REALESTATEUSEPROPERTYVALUEESTIMATE = WCF.REALESTATEUSEPROPERTYVALUEESTIMATE,
@REALESTATEINCLUDEHISTORICAL = WCF.REALESTATEINCLUDEHISTORICAL,
@BUSINESSOWNERSHIPINCLUDE = WCF.BUSINESSOWNERSHIPINCLUDE,
@BUSINESSOWNERSHIPCONFIRMEDONLY = WCF.BUSINESSOWNERSHIPCONFIRMEDONLY,
@BUSINESSOWNERSHIPVALUEMULTIPLIERTYPECODE = WCF.BUSINESSOWNERSHIPVALUEMULTIPLIERTYPECODE,
@BUSINESSOWNERSHIPVALUEMULTIPLIERCUTOFFVALUE = WCF.BUSINESSOWNERSHIPVALUEMULTIPLIERCUTOFFVALUE,
@BUSINESSOWNERSHIPVALUEMULTIPLIER = WCF.BUSINESSOWNERSHIPVALUEMULTIPLIER,
@BUSINESSOWNERSHIPVALUELESSTHANCUTOFFMULTIPLIER = WCF.BUSINESSOWNERSHIPVALUELESSTHANCUTOFFMULTIPLIER,
@BUSINESSOWNERSHIPVALUEGREATERTHANCUTOFFMULTIPLIER = WCF.BUSINESSOWNERSHIPVALUEGREATERTHANCUTOFFMULTIPLIER,
@BUSINESSOWNERSHIPVALUECODE = WCF.BUSINESSOWNERSHIPVALUECODE,
@SECURITIESINCLUDE = WCF.SECURITIESINCLUDE,
@SECURITIESCONFIRMEDONLY = WCF.SECURITIESCONFIRMEDONLY,
@SECURITIESVALUEMULTIPLIERTYPECODE = WCF.SECURITIESVALUEMULTIPLIERTYPECODE,
@SECURITIESVALUEMULTIPLIERCUTOFFVALUE = WCF.SECURITIESVALUEMULTIPLIERCUTOFFVALUE,
@SECURITIESVALUEMULTIPLIER = WCF.SECURITIESVALUEMULTIPLIER,
@SECURITIESVALUELESSTHANCUTOFFMULTIPLIER = WCF.SECURITIESVALUELESSTHANCUTOFFMULTIPLIER,
@SECURITIESVALUEGREATERTHANCUTOFFMULTIPLIER = WCF.SECURITIESVALUEGREATERTHANCUTOFFMULTIPLIER,
@SECURITIESVALUECODE = WCF.SECURITIESVALUECODE,
@INCOMECOMPENSATIONINCLUDE = WCF.INCOMECOMPENSATIONINCLUDE,
@INCOMECOMPENSATIONCONFIRMEDONLY = WCF.INCOMECOMPENSATIONCONFIRMEDONLY,
@INCOMECOMPENSATIONVALUEMULTIPLIERTYPECODE = WCF.INCOMECOMPENSATIONVALUEMULTIPLIERTYPECODE,
@INCOMECOMPENSATIONVALUEMULTIPLIERCUTOFFVALUE = WCF.INCOMECOMPENSATIONVALUEMULTIPLIERCUTOFFVALUE,
@INCOMECOMPENSATIONVALUEMULTIPLIER = WCF.INCOMECOMPENSATIONVALUEMULTIPLIER,
@INCOMECOMPENSATIONVALUELESSTHANCUTOFFMULTIPLIER = WCF.INCOMECOMPENSATIONVALUELESSTHANCUTOFFMULTIPLIER,
@INCOMECOMPENSATIONVALUEGREATERTHANCUTOFFMULTIPLIER = WCF.INCOMECOMPENSATIONVALUEGREATERTHANCUTOFFMULTIPLIER,
@INCOMECOMPENSATIONINCLUDESALARY = WCF.INCOMECOMPENSATIONINCLUDESALARY,
@INCOMECOMPENSATIONINCLUDEBONUS = WCF.INCOMECOMPENSATIONINCLUDEBONUS,
@INCOMECOMPENSATIONINCLUDESHORTTERMCOMP = WCF.INCOMECOMPENSATIONINCLUDESHORTTERMCOMP,
@INCOMECOMPENSATIONINCLUDELONGTERMCOMP = WCF.INCOMECOMPENSATIONINCLUDELONGTERMCOMP,
@INCOMECOMPENSATIONINCLUDEEXERCISEDOPTIONS = WCF.INCOMECOMPENSATIONINCLUDEEXERCISEDOPTIONS,
@INCOMECOMPENSATIONINCLUDEUNEXERCISEDOPTIONS = WCF.INCOMECOMPENSATIONINCLUDEUNEXERCISEDOPTIONS,
@INCOMECOMPENSATIONINCLUDEUNEXERCISABLEOPTIONS = WCF.INCOMECOMPENSATIONINCLUDEUNEXERCISABLEOPTIONS,
@INCOMECOMPENSATIONINCLUDEHISTORICAL = WCF.INCOMECOMPENSATIONINCLUDEHISTORICAL,
@OTHERASSETINCLUDE = WCF.OTHERASSETINCLUDE,
@OTHERASSETCONFIRMEDONLY = WCF.OTHERASSETCONFIRMEDONLY,
@OTHERASSETVALUEMULTIPLIERTYPECODE = WCF.OTHERASSETVALUEMULTIPLIERTYPECODE,
@OTHERASSETVALUEMULTIPLIERCUTOFFVALUE = WCF.OTHERASSETVALUEMULTIPLIERCUTOFFVALUE,
@OTHERASSETVALUEMULTIPLIER = WCF.OTHERASSETVALUEMULTIPLIER,
@OTHERASSETVALUELESSTHANCUTOFFMULTIPLIER = WCF.OTHERASSETVALUELESSTHANCUTOFFMULTIPLIER,
@OTHERASSETVALUEGREATERTHANCUTOFFMULTIPLIER = WCF.OTHERASSETVALUEGREATERTHANCUTOFFMULTIPLIER,
@OTHERASSETVALUECODE = WCF.OTHERASSETVALUECODE,
@NONPROFITAFFILIATIONINCLUDE = WCF.NONPROFITAFFILIATIONINCLUDE,
@NONPROFITAFFILIATIONCONFIRMEDONLY = WCF.NONPROFITAFFILIATIONCONFIRMEDONLY,
@NONPROFITAFFILIATIONVALUEMULTIPLIERTYPECODE = WCF.NONPROFITAFFILIATIONVALUEMULTIPLIERTYPECODE,
@NONPROFITAFFILIATIONVALUEMULTIPLIERCUTOFFVALUE = WCF.NONPROFITAFFILIATIONVALUEMULTIPLIERCUTOFFVALUE,
@NONPROFITAFFILIATIONVALUEMULTIPLIER = WCF.NONPROFITAFFILIATIONVALUEMULTIPLIER,
@NONPROFITAFFILIATIONVALUELESSTHANCUTOFFMULTIPLIER = WCF.NONPROFITAFFILIATIONVALUELESSTHANCUTOFFMULTIPLIER,
@NONPROFITAFFILIATIONVALUEGREATERTHANCUTOFFMULTIPLIER = WCF.NONPROFITAFFILIATIONVALUEGREATERTHANCUTOFFMULTIPLIER,
@NONPROFITAFFILIATIONVALUECODE = WCF.NONPROFITAFFILIATIONVALUECODE,
@PRIVATEFOUNDATIONINCLUDE = WCF.PRIVATEFOUNDATIONINCLUDE,
@PRIVATEFOUNDATIONCONFIRMEDONLY = WCF.PRIVATEFOUNDATIONCONFIRMEDONLY,
@PRIVATEFOUNDATIONVALUEMULTIPLIERTYPECODE = WCF.PRIVATEFOUNDATIONVALUEMULTIPLIERTYPECODE,
@PRIVATEFOUNDATIONVALUEMULTIPLIERCUTOFFVALUE = WCF.PRIVATEFOUNDATIONVALUEMULTIPLIERCUTOFFVALUE,
@PRIVATEFOUNDATIONVALUEMULTIPLIER = WCF.PRIVATEFOUNDATIONVALUEMULTIPLIER,
@PRIVATEFOUNDATIONVALUELESSTHANCUTOFFMULTIPLIER = WCF.PRIVATEFOUNDATIONVALUELESSTHANCUTOFFMULTIPLIER,
@PRIVATEFOUNDATIONVALUEGREATERTHANCUTOFFMULTIPLIER = WCF.PRIVATEFOUNDATIONVALUEGREATERTHANCUTOFFMULTIPLIER,
@PRIVATEFOUNDATIONVALUECODE = WCF.PRIVATEFOUNDATIONVALUECODE,
@PHILANTHROPICGIFTINCLUDE = WCF.PHILANTHROPICGIFTINCLUDE,
@PHILANTHROPICGIFTCONFIRMEDONLY = WCF.PHILANTHROPICGIFTCONFIRMEDONLY,
@PHILANTHROPICGIFTVALUEMULTIPLIERTYPECODE = WCF.PHILANTHROPICGIFTVALUEMULTIPLIERTYPECODE,
@PHILANTHROPICGIFTVALUEMULTIPLIERCUTOFFVALUE = WCF.PHILANTHROPICGIFTVALUEMULTIPLIERCUTOFFVALUE,
@PHILANTHROPICGIFTVALUEMULTIPLIER = WCF.PHILANTHROPICGIFTVALUEMULTIPLIER,
@PHILANTHROPICGIFTVALUELESSTHANCUTOFFMULTIPLIER = WCF.PHILANTHROPICGIFTVALUELESSTHANCUTOFFMULTIPLIER,
@PHILANTHROPICGIFTVALUEGREATERTHANCUTOFFMULTIPLIER = WCF.PHILANTHROPICGIFTVALUEGREATERTHANCUTOFFMULTIPLIER,
@PHILANTHROPICGIFTVALUECODE = WCF.PHILANTHROPICGIFTVALUECODE,
@POLITICALDONATIONINCLUDE = WCF.POLITICALDONATIONINCLUDE,
@POLITICALDONATIONCONFIRMEDONLY = WCF.POLITICALDONATIONCONFIRMEDONLY,
@POLITICALDONATIONVALUEMULTIPLIERTYPECODE = WCF.POLITICALDONATIONVALUEMULTIPLIERTYPECODE,
@POLITICALDONATIONVALUEMULTIPLIERCUTOFFVALUE = WCF.POLITICALDONATIONVALUEMULTIPLIERCUTOFFVALUE,
@POLITICALDONATIONVALUEMULTIPLIER = WCF.POLITICALDONATIONVALUEMULTIPLIER,
@POLITICALDONATIONVALUELESSTHANCUTOFFMULTIPLIER = WCF.POLITICALDONATIONVALUELESSTHANCUTOFFMULTIPLIER,
@POLITICALDONATIONVALUEGREATERTHANCUTOFFMULTIPLIER = WCF.POLITICALDONATIONVALUEGREATERTHANCUTOFFMULTIPLIER,
@POLITICALDONATIONVALUECODE = WCF.POLITICALDONATIONVALUECODE,
@GIVINGTOMYORGINCLUDE = WCF.GIVINGTOMYORGINCLUDE,
@GIVINGTOMYORGVALUEMULTIPLIERTYPECODE = WCF.GIVINGTOMYORGVALUEMULTIPLIERTYPECODE,
@GIVINGTOMYORGVALUEMULTIPLIERCUTOFFVALUE = WCF.GIVINGTOMYORGVALUEMULTIPLIERCUTOFFVALUE,
@GIVINGTOMYORGVALUEMULTIPLIER = WCF.GIVINGTOMYORGVALUEMULTIPLIER,
@GIVINGTOMYORGVALUELESSTHANCUTOFFMULTIPLIER = WCF.GIVINGTOMYORGVALUELESSTHANCUTOFFMULTIPLIER,
@GIVINGTOMYORGVALUEGREATERTHANCUTOFFMULTIPLIER = WCF.GIVINGTOMYORGVALUEGREATERTHANCUTOFFMULTIPLIER,
@GIVINGTOMYORGVALUECODE = WCF.GIVINGTOMYORGVALUECODE
from
dbo.WEALTHCAPACITYFORMULA WCF
where
WCF.ID = @WEALTHCAPACITYFORMULAID;
declare @REALESTATEASSETS money;
declare @BUSINESSOWNERSHIPASSETS money;
declare @SECURITIESASSETS money;
declare @INCOMECOMPENSATIONASSETS money;
declare @OTHERASSETS money;
declare @NONPROFITAFFILIATIONASSETS money;
declare @PRIVATEFOUNDATIONASSETS money;
declare @PHILANTHROPICGIFTS money;
declare @POLITICALDONATIONS money;
declare @GIVINGTOMYORG money;
declare @TOTALASSETS money;
set @REALESTATEASSETS = 0;
set @BUSINESSOWNERSHIPASSETS = 0;
set @SECURITIESASSETS = 0;
set @INCOMECOMPENSATIONASSETS = 0;
set @OTHERASSETS = 0;
set @NONPROFITAFFILIATIONASSETS = 0;
set @PRIVATEFOUNDATIONASSETS = 0;
set @PHILANTHROPICGIFTS = 0;
set @POLITICALDONATIONS = 0;
set @GIVINGTOMYORG = 0;
set @TOTALASSETS = 0;
if @FORMULALOADED = 1 begin
if @REALESTATEINCLUDE = 1 begin
with REALESTATEROWVALUE_CTE as (
select
case @REALESTATEAGGREGATETYPECODE
when 0 then
case
when @REALESTATEUSEPROPERTYVALUATION = 1 and RE.PROPERTYVALUATION <> 0 and (RE.PROPERTYVALUATION >= RE.ESTIMATEDVALUE or @REALESTATEUSEPROPERTYVALUEESTIMATE = 0 or RE.ESTIMATEDVALUE = 0) then RE.PROPERTYVALUATION
when @REALESTATEUSEPROPERTYVALUEESTIMATE = 1 and RE.ESTIMATEDVALUE <> 0 then RE.ESTIMATEDVALUE
else 0
end
when 1 then
case
when @REALESTATEUSEPROPERTYVALUATION = 1 and RE.PROPERTYVALUATION <> 0 and (RE.PROPERTYVALUATION <= RE.ESTIMATEDVALUE or @REALESTATEUSEPROPERTYVALUEESTIMATE = 0 or RE.ESTIMATEDVALUE = 0) then RE.PROPERTYVALUATION
when @REALESTATEUSEPROPERTYVALUEESTIMATE = 1 and RE.ESTIMATEDVALUE <> 0 then RE.ESTIMATEDVALUE
else 0
end
when 2 then
case
when (@REALESTATEUSEPROPERTYVALUATION = 1 and RE.PROPERTYVALUATION <> 0) or (@REALESTATEUSEPROPERTYVALUEESTIMATE = 1 and RE.ESTIMATEDVALUE <> 0) then
((case @REALESTATEUSEPROPERTYVALUATION when 1 then RE.PROPERTYVALUATION else 0 end) + (case @REALESTATEUSEPROPERTYVALUEESTIMATE when 1 then RE.ESTIMATEDVALUE else 0 end))/((case when @REALESTATEUSEPROPERTYVALUATION = 1 and RE.PROPERTYVALUATION <> 0 then 1 else 0 end) + (case when @REALESTATEUSEPROPERTYVALUEESTIMATE = 1 and RE.ESTIMATEDVALUE <> 0 then 1 else 0 end))
else 0
end
else
0
end ROWVALUE
from
dbo.WPREALESTATE RE
where
RE.WEALTHID = @WEALTHID and
RE.SOURCE in (select SOURCE from dbo.WEALTHCAPACITYFORMULAREALESTATESOURCE where WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID) and
RE.REJECTED = 0 and
(RE.CONFIRMED = 1 or @REALESTATECONFIRMEDONLY = 0) and
(RE.HISTORICCODE <> 1 or @REALESTATEINCLUDEHISTORICAL = 1)
)
select
@REALESTATEASSETS = coalesce(
sum(
case @REALESTATEVALUEMULTIPLIERTYPECODE
when 0 then @REALESTATEVALUEMULTIPLIER * REALESTATEROWVALUE_CTE.ROWVALUE
when 1 then
case
when REALESTATEROWVALUE_CTE.ROWVALUE < @REALESTATEVALUEMULTIPLIERCUTOFFVALUE then @REALESTATEVALUELESSTHANCUTOFFMULTIPLIER * REALESTATEROWVALUE_CTE.ROWVALUE
else @REALESTATEVALUEGREATERTHANCUTOFFMULTIPLIER * REALESTATEROWVALUE_CTE.ROWVALUE
end
end
),0)
from
REALESTATEROWVALUE_CTE;
end;
if @BUSINESSOWNERSHIPINCLUDE = 1 begin
with BUSINESSOWNERSHIPROWVALUE_CTE as (
select
case @BUSINESSOWNERSHIPVALUECODE
when 0 then
BO.OWNERSHIPVALUE
when 1 then
BO.VALUATION
else
0
end ROWVALUE
from
dbo.WPBUSINESSOWNERSHIP BO
where
BO.WEALTHID = @WEALTHID and
BO.SOURCE in (select SOURCE from dbo.WEALTHCAPACITYFORMULABUSINESSOWNERSHIPSOURCE where WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID) and
BO.REJECTED = 0 and
(BO.CONFIRMED = 1 or @BUSINESSOWNERSHIPCONFIRMEDONLY = 0)
)
select
@BUSINESSOWNERSHIPASSETS = coalesce(
sum(
case @BUSINESSOWNERSHIPVALUEMULTIPLIERTYPECODE
when 0 then @BUSINESSOWNERSHIPVALUEMULTIPLIER * BUSINESSOWNERSHIPROWVALUE_CTE.ROWVALUE
when 1 then
case
when BUSINESSOWNERSHIPROWVALUE_CTE.ROWVALUE < @BUSINESSOWNERSHIPVALUEMULTIPLIERCUTOFFVALUE then @BUSINESSOWNERSHIPVALUELESSTHANCUTOFFMULTIPLIER * BUSINESSOWNERSHIPROWVALUE_CTE.ROWVALUE
else @BUSINESSOWNERSHIPVALUEGREATERTHANCUTOFFMULTIPLIER * BUSINESSOWNERSHIPROWVALUE_CTE.ROWVALUE
end
end
),0)
from
BUSINESSOWNERSHIPROWVALUE_CTE;
end;
if @SECURITIESINCLUDE = 1 begin
with SECURITIESROWVALUE_CTE as (
select
case @SECURITIESVALUECODE
when 0 then
SEC.VALUEDIRECT
when 1 then
SEC.VALUEINDIRECT
when 2 then
SEC.VALUEDIRECT + SEC.VALUEINDIRECT
else
0
end ROWVALUE
from
dbo.WPSECURITIES SEC
where
SEC.WEALTHID = @WEALTHID and
SEC.SOURCE in (select SOURCE from dbo.WEALTHCAPACITYFORMULASECURITIESSOURCE where WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID) and
SEC.REJECTED = 0 and
(SEC.CONFIRMED = 1 or @SECURITIESCONFIRMEDONLY = 0)
)
select
@SECURITIESASSETS = coalesce(
sum(
case @SECURITIESVALUEMULTIPLIERTYPECODE
when 0 then @SECURITIESVALUEMULTIPLIER * SECURITIESROWVALUE_CTE.ROWVALUE
when 1 then
case
when SECURITIESROWVALUE_CTE.ROWVALUE < @SECURITIESVALUEMULTIPLIERCUTOFFVALUE then @SECURITIESVALUELESSTHANCUTOFFMULTIPLIER * SECURITIESROWVALUE_CTE.ROWVALUE
else @SECURITIESVALUEGREATERTHANCUTOFFMULTIPLIER * SECURITIESROWVALUE_CTE.ROWVALUE
end
end
),0)
from
SECURITIESROWVALUE_CTE;
end;
if @INCOMECOMPENSATIONINCLUDE = 1 begin
with INCOMECOMPENSATIONROWVALUE_CTE as (
select (
case @INCOMECOMPENSATIONINCLUDESALARY when 1 then IC.LATESTFISCALYEARSALARY else 0 end +
case @INCOMECOMPENSATIONINCLUDEBONUS when 1 then IC.LATESTFISCALYEARBONUS else 0 end +
case @INCOMECOMPENSATIONINCLUDESHORTTERMCOMP when 1 then IC.LATESTFISCALYEAROTHERSHORTTERMCOMPENSATION else 0 end +
case @INCOMECOMPENSATIONINCLUDELONGTERMCOMP when 1 then IC.LATESTFISCALYEAROTHERLONGTERMCOMPENSATION else 0 end +
case @INCOMECOMPENSATIONINCLUDEEXERCISEDOPTIONS when 1 then IC.VALUEOFOPTIONSEXERCISED else 0 end +
case @INCOMECOMPENSATIONINCLUDEUNEXERCISEDOPTIONS when 1 then IC.VALUEOFOPTIONSUNEXERCISED_EXERCISABLE else 0 end +
case @INCOMECOMPENSATIONINCLUDEUNEXERCISABLEOPTIONS when 1 then IC.VALUEOFOPTIONSUNEXERCISED_UNEXERCISABLE else 0 end
) ROWVALUE
from
dbo.WPINCOMECOMPENSATION IC
where
IC.WEALTHID = @WEALTHID and
IC.SOURCE in (select SOURCE from dbo.WEALTHCAPACITYFORMULAINCOMECOMPENSATIONSOURCE where WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID) and
IC.REJECTED = 0 and
(IC.CONFIRMED = 1 or @INCOMECOMPENSATIONCONFIRMEDONLY = 0) and
(IC.HISTORICCODE <> 1 or @INCOMECOMPENSATIONINCLUDEHISTORICAL = 1)
)
select
@INCOMECOMPENSATIONASSETS = coalesce(
sum(
case @INCOMECOMPENSATIONVALUEMULTIPLIERTYPECODE
when 0 then @INCOMECOMPENSATIONVALUEMULTIPLIER * INCOMECOMPENSATIONROWVALUE_CTE.ROWVALUE
when 1 then
case
when INCOMECOMPENSATIONROWVALUE_CTE.ROWVALUE < @INCOMECOMPENSATIONVALUEMULTIPLIERCUTOFFVALUE then @INCOMECOMPENSATIONVALUELESSTHANCUTOFFMULTIPLIER * INCOMECOMPENSATIONROWVALUE_CTE.ROWVALUE
else @INCOMECOMPENSATIONVALUEGREATERTHANCUTOFFMULTIPLIER * INCOMECOMPENSATIONROWVALUE_CTE.ROWVALUE
end
end
),0)
from
INCOMECOMPENSATIONROWVALUE_CTE;
end;
if @OTHERASSETINCLUDE = 1 begin
with OTHERASSETROWVALUE_CTE as (
select
case @OTHERASSETVALUECODE
when 0 then
OA.VALUE
else
0
end ROWVALUE
from
dbo.WPOTHERASSET OA
where
OA.WEALTHID = @WEALTHID and
OA.SOURCE in (select SOURCE from dbo.WEALTHCAPACITYFORMULAOTHERASSETSOURCE where WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID) and
OA.REJECTED = 0 and
(OA.CONFIRMED = 1 or @OTHERASSETCONFIRMEDONLY = 0)
)
select
@OTHERASSETS = coalesce(
sum(
case @OTHERASSETVALUEMULTIPLIERTYPECODE
when 0 then @OTHERASSETVALUEMULTIPLIER * OTHERASSETROWVALUE_CTE.ROWVALUE
when 1 then
case
when OTHERASSETROWVALUE_CTE.ROWVALUE < @OTHERASSETVALUEMULTIPLIERCUTOFFVALUE then @OTHERASSETVALUELESSTHANCUTOFFMULTIPLIER * OTHERASSETROWVALUE_CTE.ROWVALUE
else @OTHERASSETVALUEGREATERTHANCUTOFFMULTIPLIER * OTHERASSETROWVALUE_CTE.ROWVALUE
end
end
),0)
from
OTHERASSETROWVALUE_CTE;
end;
if @NONPROFITAFFILIATIONINCLUDE = 1 begin
with NONPROFITAFFILIATIONMAXFORMYEAR_CTE as (
select
NA.DN_ORGANIZATION,
NA.CITY,
NA.STATE,
max(NA.FORMYEAR) MAXFORMYEAR
from
dbo.WPNONPROFITAFFILIATION NA
where
NA.WEALTHID = @WEALTHID and
NA.SOURCE in (select SOURCE from dbo.WEALTHCAPACITYFORMULANONPROFITAFFILIATIONSOURCE where WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID) and
NA.REJECTED = 0 and
(NA.CONFIRMED = 1 or @NONPROFITAFFILIATIONCONFIRMEDONLY = 0) and
NA.FORMYEAR > 0
group by
NA.DN_ORGANIZATION,
NA.CITY,
NA.STATE
),
NONPROFITAFFILIATIONMAXFORMYEARDATEADDED_CTE as (
select
NPAMFY.DN_ORGANIZATION,
NPAMFY.CITY,
NPAMFY.STATE,
NPAMFY.MAXFORMYEAR,
max(NA.DATEADDED) MAXDATEADDED
from
NONPROFITAFFILIATIONMAXFORMYEAR_CTE NPAMFY
inner join
dbo.WPNONPROFITAFFILIATION NA
on
NA.DN_ORGANIZATION = NPAMFY.DN_ORGANIZATION and
NA.CITY = NPAMFY.CITY and
NA.STATE = NPAMFY.STATE and
NA.FORMYEAR = NPAMFY.MAXFORMYEAR
where
NA.WEALTHID = @WEALTHID and
NA.SOURCE in (select SOURCE from dbo.WEALTHCAPACITYFORMULANONPROFITAFFILIATIONSOURCE where WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID) and
NA.REJECTED = 0 and
(NA.CONFIRMED = 1 or @NONPROFITAFFILIATIONCONFIRMEDONLY = 0) and
NA.FORMYEAR > 0
group by
NPAMFY.DN_ORGANIZATION,
NPAMFY.CITY,
NPAMFY.STATE,
NPAMFY.MAXFORMYEAR
),
NONPROFITAFFILIATIONROWVALUE_CTE as (
select
case @NONPROFITAFFILIATIONVALUECODE
when 0 then
NA.SALARY
else
0
end ROWVALUE
from
NONPROFITAFFILIATIONMAXFORMYEARDATEADDED_CTE NPAMFYDA
inner join
dbo.WPNONPROFITAFFILIATION NA
on
NA.DN_ORGANIZATION = NPAMFYDA.DN_ORGANIZATION and
NA.CITY = NPAMFYDA.CITY and
NA.STATE = NPAMFYDA.STATE and
NA.FORMYEAR = NPAMFYDA.MAXFORMYEAR and
NA.DATEADDED = NPAMFYDA.MAXDATEADDED
where
NA.WEALTHID = @WEALTHID and
NA.SOURCE in (select SOURCE from dbo.WEALTHCAPACITYFORMULANONPROFITAFFILIATIONSOURCE where WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID) and
NA.REJECTED = 0 and
(NA.CONFIRMED = 1 or @NONPROFITAFFILIATIONCONFIRMEDONLY = 0) and
NA.FORMYEAR > 0
)
select
@NONPROFITAFFILIATIONASSETS = coalesce(
sum(
case @NONPROFITAFFILIATIONVALUEMULTIPLIERTYPECODE
when 0 then @NONPROFITAFFILIATIONVALUEMULTIPLIER * NONPROFITAFFILIATIONROWVALUE_CTE.ROWVALUE
when 1 then
case
when NONPROFITAFFILIATIONROWVALUE_CTE.ROWVALUE < @NONPROFITAFFILIATIONVALUEMULTIPLIERCUTOFFVALUE then @NONPROFITAFFILIATIONVALUELESSTHANCUTOFFMULTIPLIER * NONPROFITAFFILIATIONROWVALUE_CTE.ROWVALUE
else @NONPROFITAFFILIATIONVALUEGREATERTHANCUTOFFMULTIPLIER * NONPROFITAFFILIATIONROWVALUE_CTE.ROWVALUE
end
end
),0)
from
NONPROFITAFFILIATIONROWVALUE_CTE;
end;
if @PRIVATEFOUNDATIONINCLUDE = 1 begin
with PRIVATEFOUNDATIONMAXFORMYEAR_CTE as (
select
PF.COMPANY,
PF.CITY,
PF.STATE,
max(PF.FORMYEAR) MAXFORMYEAR
from
dbo.WPPRIVATEFOUNDATION PF
where
PF.WEALTHID = @WEALTHID and
PF.SOURCE in (select SOURCE from dbo.WEALTHCAPACITYFORMULAPRIVATEFOUNDATIONSOURCE where WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID) and
PF.REJECTED = 0 and
(PF.CONFIRMED = 1 or @PRIVATEFOUNDATIONCONFIRMEDONLY = 0) and
PF.FORMYEAR > 0
group by
PF.COMPANY,
PF.CITY,
PF.STATE
),
PRIVATEFOUNDATIONMAXFORMYEARDATEADDED_CTE as (
select
PFMFY.COMPANY,
PFMFY.CITY,
PFMFY.STATE,
PFMFY.MAXFORMYEAR,
max(PF.DATEADDED) MAXDATEADDED
from
PRIVATEFOUNDATIONMAXFORMYEAR_CTE PFMFY
inner join
dbo.WPPRIVATEFOUNDATION PF
on
PF.COMPANY = PFMFY.COMPANY and
PF.CITY = PFMFY.CITY and
PF.STATE = PFMFY.STATE and
PF.FORMYEAR = PFMFY.MAXFORMYEAR
where
PF.WEALTHID = @WEALTHID and
PF.SOURCE in (select SOURCE from dbo.WEALTHCAPACITYFORMULAPRIVATEFOUNDATIONSOURCE where WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID) and
PF.REJECTED = 0 and
(PF.CONFIRMED = 1 or @PRIVATEFOUNDATIONCONFIRMEDONLY = 0) and
PF.FORMYEAR > 0
group by
PFMFY.COMPANY,
PFMFY.CITY,
PFMFY.STATE,
PFMFY.MAXFORMYEAR
),
PRIVATEFOUNDATIONROWVALUE_CTE as (
select
case @PRIVATEFOUNDATIONVALUECODE
when 0 then
PF.COMP
else
0
end ROWVALUE
from
PRIVATEFOUNDATIONMAXFORMYEARDATEADDED_CTE PFMFYDA
inner join
dbo.WPPRIVATEFOUNDATION PF
on
PF.COMPANY = PFMFYDA.COMPANY and
PF.CITY = PFMFYDA.CITY and
PF.STATE = PFMFYDA.STATE and
PF.FORMYEAR = PFMFYDA.MAXFORMYEAR and
PF.DATEADDED = PFMFYDA.MAXDATEADDED
where
PF.WEALTHID = @WEALTHID and
PF.SOURCE in (select SOURCE from dbo.WEALTHCAPACITYFORMULAPRIVATEFOUNDATIONSOURCE where WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID) and
PF.REJECTED = 0 and
(PF.CONFIRMED = 1 or @PRIVATEFOUNDATIONCONFIRMEDONLY = 0) and
PF.FORMYEAR > 0
)
select
@PRIVATEFOUNDATIONASSETS = coalesce(
sum(
case @PRIVATEFOUNDATIONVALUEMULTIPLIERTYPECODE
when 0 then @PRIVATEFOUNDATIONVALUEMULTIPLIER * PRIVATEFOUNDATIONROWVALUE_CTE.ROWVALUE
when 1 then
case
when PRIVATEFOUNDATIONROWVALUE_CTE.ROWVALUE < @PRIVATEFOUNDATIONVALUEMULTIPLIERCUTOFFVALUE then @PRIVATEFOUNDATIONVALUELESSTHANCUTOFFMULTIPLIER * PRIVATEFOUNDATIONROWVALUE_CTE.ROWVALUE
else @PRIVATEFOUNDATIONVALUEGREATERTHANCUTOFFMULTIPLIER * PRIVATEFOUNDATIONROWVALUE_CTE.ROWVALUE
end
end
),0)
from
PRIVATEFOUNDATIONROWVALUE_CTE;
end;
with CTE_ASSETS as (
select
@REALESTATEASSETS TOTALASSETVALUE
where
@REALESTATEINCLUDE = 1
union all
select
@BUSINESSOWNERSHIPASSETS
where
@BUSINESSOWNERSHIPINCLUDE = 1
union all
select
@SECURITIESASSETS
where
@SECURITIESINCLUDE = 1
union all
select
@INCOMECOMPENSATIONASSETS
where
@INCOMECOMPENSATIONINCLUDE = 1
union all
select
@OTHERASSETS
where
@OTHERASSETINCLUDE = 1
union all
select
@NONPROFITAFFILIATIONASSETS
where
@NONPROFITAFFILIATIONINCLUDE = 1
union all
select
@PRIVATEFOUNDATIONASSETS
where
@PRIVATEFOUNDATIONINCLUDE = 1
)
select
@TOTALASSETS = floor(coalesce(sum(CTE_ASSETS.TOTALASSETVALUE),0))
from
CTE_ASSETS;
if @PHILANTHROPICGIFTINCLUDE = 1 begin
with PHILANTHROPICGIFT_CTE as (
select
ID, LO, HI, GIFTYEAR
from
dbo.WPPHILANTHROPICGIFT PG
where
WEALTHID = @WEALTHID and
SOURCE in (select SOURCE from dbo.WEALTHCAPACITYFORMULAPHILANTHROPICGIFTSOURCE where WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID) and
REJECTED = 0 and
(CONFIRMED = 1 or @PHILANTHROPICGIFTCONFIRMEDONLY = 0)
),
PHILANTHROPICGIFTROWVALUE_CTE as (
select
case @PHILANTHROPICGIFTVALUECODE
when 0 then
max(PG.LO)
when 1 then
max(PG.HI)
when 2 then
sum(isnull(PGPASTTHREEYEARS.LO, 0))
when 3 then
sum(isnull(PGPASTTHREEYEARS.HI, 0))
when 4 then
sum(isnull(PGPASTFIVEYEARS.LO, 0))
when 5 then
sum(isnull(PGPASTFIVEYEARS.HI, 0))
else
0
end ROWVALUE
from
PHILANTHROPICGIFT_CTE PG
left join
PHILANTHROPICGIFT_CTE PGPASTTHREEYEARS on PGPASTTHREEYEARS.ID = PG.ID and PGPASTTHREEYEARS.GIFTYEAR >= (@CURRENTYEAR - 2) and PGPASTTHREEYEARS.GIFTYEAR <= @CURRENTYEAR
left join
PHILANTHROPICGIFT_CTE PGPASTFIVEYEARS on PGPASTFIVEYEARS.ID = PG.ID and PGPASTFIVEYEARS.GIFTYEAR >= (@CURRENTYEAR - 4) and PGPASTFIVEYEARS.GIFTYEAR <= @CURRENTYEAR
)
select
@PHILANTHROPICGIFTS = coalesce(
sum(
case @PHILANTHROPICGIFTVALUEMULTIPLIERTYPECODE
when 0 then @PHILANTHROPICGIFTVALUEMULTIPLIER * PHILANTHROPICGIFTROWVALUE_CTE.ROWVALUE
when 1 then
case
when PHILANTHROPICGIFTROWVALUE_CTE.ROWVALUE < @PHILANTHROPICGIFTVALUEMULTIPLIERCUTOFFVALUE then @PHILANTHROPICGIFTVALUELESSTHANCUTOFFMULTIPLIER * PHILANTHROPICGIFTROWVALUE_CTE.ROWVALUE
else @PHILANTHROPICGIFTVALUEGREATERTHANCUTOFFMULTIPLIER * PHILANTHROPICGIFTROWVALUE_CTE.ROWVALUE
end
end
),0)
from
PHILANTHROPICGIFTROWVALUE_CTE;
end;
if @POLITICALDONATIONINCLUDE = 1 begin
with POLITICALDONATION_CTE as (
select
ID, AMOUNT, TRANSACTIONDATE
from
dbo.WPPOLITICALDONATION PD
where
WEALTHID = @WEALTHID and
SOURCE in (select SOURCE from dbo.WEALTHCAPACITYFORMULAPOLITICALDONATIONSOURCE where WEALTHCAPACITYFORMULAID = @WEALTHCAPACITYFORMULAID) and
REJECTED = 0 and
(CONFIRMED = 1 or @POLITICALDONATIONCONFIRMEDONLY = 0)
),
POLITICALDONATIONROWVALUE_CTE as (
select
case @POLITICALDONATIONVALUECODE
when 0 then
max(PD.AMOUNT)
when 1 then
sum(isnull(PDLASTFOURYEARS.AMOUNT, 0))
when 2 then
sum(isnull(PDLASTEIGHTYEARS.AMOUNT, 0))
else
0
end ROWVALUE
from
POLITICALDONATION_CTE PD
left join
POLITICALDONATION_CTE PDLASTFOURYEARS on PDLASTFOURYEARS.ID = PD.ID and PDLASTFOURYEARS.TRANSACTIONDATE >= dbo.UFN_DATE_GETEARLIESTTIME(dateadd(yy, -4, @CURRENTDATE)) and PDLASTFOURYEARS.TRANSACTIONDATE < dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)
left join
POLITICALDONATION_CTE PDLASTEIGHTYEARS on PDLASTEIGHTYEARS.ID = PD.ID and PDLASTEIGHTYEARS.TRANSACTIONDATE >= dbo.UFN_DATE_GETEARLIESTTIME(dateadd(yy, -8, @CURRENTDATE)) and PDLASTEIGHTYEARS.TRANSACTIONDATE < dbo.UFN_DATE_GETEARLIESTTIME(@CURRENTDATE)
)
select
@POLITICALDONATIONS = coalesce(
sum(
case @POLITICALDONATIONVALUEMULTIPLIERTYPECODE
when 0 then @POLITICALDONATIONVALUEMULTIPLIER * POLITICALDONATIONROWVALUE_CTE.ROWVALUE
when 1 then
case
when POLITICALDONATIONROWVALUE_CTE.ROWVALUE < @POLITICALDONATIONVALUEMULTIPLIERCUTOFFVALUE then @POLITICALDONATIONVALUELESSTHANCUTOFFMULTIPLIER * POLITICALDONATIONROWVALUE_CTE.ROWVALUE
else @POLITICALDONATIONVALUEGREATERTHANCUTOFFMULTIPLIER * POLITICALDONATIONROWVALUE_CTE.ROWVALUE
end
end
),0)
from
POLITICALDONATIONROWVALUE_CTE;
end;
if @GIVINGTOMYORGINCLUDE = 1 begin
with GIVINGTOMYORGROWVALUE_CTE as (
select
case @GIVINGTOMYORGVALUECODE
when 0 then
TOTALGIFTAMOUNT/TOTALGIFTSGIVEN
when 1 then
LARGESTGIFTAMOUNT
when 2 then
LATESTGIFTAMOUNT
when 3 then
TOTALGIFTAMOUNT
else
0
end ROWVALUE
from
dbo.RE7INTEGRATIONGIVINGSUMMARY
where
ID = @WEALTHID
)
select
@GIVINGTOMYORG = coalesce(
sum(
case @GIVINGTOMYORGVALUEMULTIPLIERTYPECODE
when 0 then @GIVINGTOMYORGVALUEMULTIPLIER * GIVINGTOMYORGROWVALUE_CTE.ROWVALUE
when 1 then
case
when GIVINGTOMYORGROWVALUE_CTE.ROWVALUE < @GIVINGTOMYORGVALUEMULTIPLIERCUTOFFVALUE then @GIVINGTOMYORGVALUELESSTHANCUTOFFMULTIPLIER * GIVINGTOMYORGROWVALUE_CTE.ROWVALUE
else @GIVINGTOMYORGVALUEGREATERTHANCUTOFFMULTIPLIER * GIVINGTOMYORGROWVALUE_CTE.ROWVALUE
end
end
),0)
from
GIVINGTOMYORGROWVALUE_CTE;
end;
with CTE_MAJORGIFTS as (
select
@TOTALASSETS TOTALGIVINGVALUE
where
@TOTALASSETS <> 0
union all
select
@PHILANTHROPICGIFTS
where
@PHILANTHROPICGIFTINCLUDE = 1 and
@PHILANTHROPICGIFTS <> 0
union all
select
@POLITICALDONATIONS
where
@POLITICALDONATIONINCLUDE = 1 and
@POLITICALDONATIONS <> 0
union all
select
@GIVINGTOMYORG
where
@GIVINGTOMYORGINCLUDE = 1 and
@GIVINGTOMYORG <> 0
)
select
@ESTIMATEDWEALTHVALUE = floor(coalesce(case @ASSETCALCULATIONMETHODCODE
when 4 then avg(CTE_MAJORGIFTS.TOTALGIVINGVALUE)
when 5 then max(CTE_MAJORGIFTS.TOTALGIVINGVALUE)
else 0
end,0))
from
CTE_MAJORGIFTS;
if (@ASSETCALCULATIONMETHODCODE = 5)
begin
if (@ESTIMATEDWEALTHVALUE = 0)
begin
set @MAJORGIVINGCAPACITYBASISVALUE = 'None'
end
else
begin
if (@ESTIMATEDWEALTHVALUE = @TOTALASSETS)
begin
set @MAJORGIVINGCAPACITYBASISVALUE = 'Assets'
end
if (@ESTIMATEDWEALTHVALUE = @PHILANTHROPICGIFTS)
begin
set @MAJORGIVINGCAPACITYBASISVALUE = @MAJORGIVINGCAPACITYBASISVALUE + (case when @MAJORGIVINGCAPACITYBASISVALUE <> '' then (case when (@ESTIMATEDWEALTHVALUE <> @POLITICALDONATIONS and @ESTIMATEDWEALTHVALUE <> @GIVINGTOMYORG) then ' and ' else ', ' end) else '' end) + 'Philanthropic gifts'
end
if (@ESTIMATEDWEALTHVALUE = @POLITICALDONATIONS)
begin
set @MAJORGIVINGCAPACITYBASISVALUE = @MAJORGIVINGCAPACITYBASISVALUE + (case when @MAJORGIVINGCAPACITYBASISVALUE <> '' then (case when @ESTIMATEDWEALTHVALUE <> @GIVINGTOMYORG then ' and ' else ', ' end) else '' end) + 'Political donations'
end
if (@ESTIMATEDWEALTHVALUE = @GIVINGTOMYORG)
begin
set @MAJORGIVINGCAPACITYBASISVALUE = @MAJORGIVINGCAPACITYBASISVALUE + (case when @MAJORGIVINGCAPACITYBASISVALUE <> '' then ' and ' else '' end) + 'Giving to my organization'
end
end
end
else if (@ASSETCALCULATIONMETHODCODE = 4)
begin
if ((@REALESTATEINCLUDE = 1 or @BUSINESSOWNERSHIPINCLUDE = 1 or @SECURITIESINCLUDE = 1 or @INCOMECOMPENSATIONINCLUDE = 1
or @OTHERASSETINCLUDE = 1 or @NONPROFITAFFILIATIONINCLUDE = 1 or @PRIVATEFOUNDATIONINCLUDE = 1))
begin
set @MAJORGIVINGCAPACITYBASISVALUE = 'Assets'
end
if (@PHILANTHROPICGIFTINCLUDE = 1)
begin
set @MAJORGIVINGCAPACITYBASISVALUE = @MAJORGIVINGCAPACITYBASISVALUE + (case when @MAJORGIVINGCAPACITYBASISVALUE <> '' then (case when (@POLITICALDONATIONINCLUDE = 0 and @GIVINGTOMYORGINCLUDE = 0) then ' and ' else ', ' end) else '' end) + 'Philanthropic gifts'
end
if (@POLITICALDONATIONINCLUDE = 1)
begin
set @MAJORGIVINGCAPACITYBASISVALUE = @MAJORGIVINGCAPACITYBASISVALUE + (case when @MAJORGIVINGCAPACITYBASISVALUE <> '' then (case when @GIVINGTOMYORGINCLUDE = 0 then ' and ' else ', ' end) else '' end) + 'Political donations'
end
if (@GIVINGTOMYORGINCLUDE = 1)
begin
set @MAJORGIVINGCAPACITYBASISVALUE = @MAJORGIVINGCAPACITYBASISVALUE + (case when @MAJORGIVINGCAPACITYBASISVALUE <> '' then ' and ' else '' end) + 'Giving to my organization'
end
set @MAJORGIVINGCAPACITYBASISVALUE = 'Average of ' + @MAJORGIVINGCAPACITYBASISVALUE
end
else
begin
set @MAJORGIVINGCAPACITYBASISVALUE = 'None'
end
select
@MAJORGIVINGCAPACITYVALUE = floor(case @MAJORGIVINGMULTIPLIERTYPECODE
when 0 then @MAJORGIVINGSETVALUEMULTIPLIER * @ESTIMATEDWEALTHVALUE
when 1 then
case
when C.AGE >= 70 then @MAJORGIVINGOVER70MULTIPLIER * @ESTIMATEDWEALTHVALUE
when C.AGE >= 60 then @MAJORGIVING60TO69MULTIPLIER * @ESTIMATEDWEALTHVALUE
when C.AGE >= 50 then @MAJORGIVING50TO59MULTIPLIER * @ESTIMATEDWEALTHVALUE
when C.AGE >= 40 then @MAJORGIVING40TO49MULTIPLIER * @ESTIMATEDWEALTHVALUE
when C.BIRTHDATE = '00000000' then @MAJORGIVINGAGEUNKNOWNMULTIPLIER * @ESTIMATEDWEALTHVALUE
else @MAJORGIVINGUNDER40MULTIPLIER * @ESTIMATEDWEALTHVALUE
end
else 0
end)
from
dbo.CONSTITUENT C
where
C.ID = @WEALTHID;
end
else begin
-- Use old calculation method
-- Get the total assets that have been confirmed.
select
@REALESTATEASSETS = REALESTATEASSETSCONFIRMED,
@BUSINESSOWNERSHIPASSETS = BUSINESSOWNERSHIPASSETSCONFIRMED,
@SECURITIESASSETS = SECURITIESASSETSCONFIRMED,
@INCOMECOMPENSATIONASSETS = INCOMECOMPENSATIONASSETSCONFIRMED,
@OTHERASSETS = OTHERASSETSCONFIRMED
from
dbo.WEALTH
where
WEALTH.ID = @WEALTHID;
-- Calculate the estimated wealth.
set @ESTIMATEDWEALTHVALUE = floor(
coalesce(@REALESTATEASSETS, 0) +
coalesce(@SECURITIESASSETS, 0) +
coalesce(@BUSINESSOWNERSHIPASSETS, 0) +
coalesce(@INCOMECOMPENSATIONASSETS, 0) +
coalesce(@OTHERASSETS, 0));
-- Calculate the major giving capacity.
set @MAJORGIVINGCAPACITYVALUE = floor(@ESTIMATEDWEALTHVALUE * 0.05);
end
end
return 0;