USP_CONSTITUENT_REVENUESUMMARYEXPANDED
Returns recognition summary values for a constituent (used in enterprise expanded views).
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @ISGROUP | bit | IN | |
| @HOUSEHOLDID | uniqueidentifier | IN | |
| @REVENUEFILTERID | uniqueidentifier | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @SITEFILTERMODE | tinyint | IN | |
| @SITESSELECTED | xml | IN | |
| @SECURITYFEATUREID | uniqueidentifier | IN | |
| @SECURITYFEATURETYPE | tinyint | IN | |
| @CURRENCYCODE | tinyint | IN | |
| @TOTALNUMBER | int | INOUT | |
| @TOTALAMOUNT | money | INOUT | |
| @TOTALAMOUNT_HOUSEHOLD | money | INOUT | |
| @TOTALYEARS | int | INOUT | |
| @CONSECUTIVEYEARS | int | INOUT | |
| @GIVENSINCEFISCALYEAR | datetime | INOUT | |
| @TOTALREVENUEWITHGIFTAID | money | INOUT | |
| @FIRSTID | uniqueidentifier | INOUT | |
| @FIRSTRECORDID | uniqueidentifier | INOUT | |
| @FIRSTDATE | datetime | INOUT | |
| @FIRSTTYPECODE | tinyint | INOUT | |
| @FIRSTTYPE | nvarchar(50) | INOUT | |
| @FIRSTAMOUNT | money | INOUT | |
| @LATESTID | uniqueidentifier | INOUT | |
| @LATESTRECORDID | uniqueidentifier | INOUT | |
| @LATESTDATE | datetime | INOUT | |
| @LATESTTYPECODE | tinyint | INOUT | |
| @LATESTTYPE | nvarchar(50) | INOUT | |
| @LATESTAMOUNT | money | INOUT | |
| @CURRENCYISOCURRENCYCODE | nvarchar(3) | INOUT | |
| @CURRENCYDECIMALDIGITS | tinyint | INOUT | |
| @CURRENCYSYMBOL | nvarchar(5) | INOUT | |
| @CURRENCYSYMBOLDISPLAYSETTINGCODE | tinyint | INOUT | |
| @ONLYGETSUMMARY | bit | IN | |
| @BREAKDOWNBY | tinyint | IN | |
| @CAMPAIGNFILTERMODE | tinyint | IN | |
| @CAMPAIGNSSELECTED | xml | IN |
Definition
Copy
CREATE procedure [dbo].[USP_CONSTITUENT_REVENUESUMMARYEXPANDED](
@CONSTITUENTID uniqueidentifier,
@ISGROUP bit = 0,
@HOUSEHOLDID uniqueidentifier = null,
@REVENUEFILTERID uniqueidentifier = null,
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@CURRENCYCODE tinyint = 1,
@TOTALNUMBER int = null output,
@TOTALAMOUNT money = null output,
@TOTALAMOUNT_HOUSEHOLD money = null output,
@TOTALYEARS int = null output,
@CONSECUTIVEYEARS int = null output,
@GIVENSINCEFISCALYEAR datetime = null output,
@TOTALREVENUEWITHGIFTAID money = null output,
@FIRSTID uniqueidentifier = null output,
@FIRSTRECORDID uniqueidentifier = null output,
@FIRSTDATE datetime = null output,
@FIRSTTYPECODE tinyint = null output,
@FIRSTTYPE nvarchar(50) = null output,
@FIRSTAMOUNT money = null output,
@LATESTID uniqueidentifier = null output,
@LATESTRECORDID uniqueidentifier = null output,
@LATESTDATE datetime = null output,
@LATESTTYPECODE tinyint = null output,
@LATESTTYPE nvarchar(50) = null output,
@LATESTAMOUNT money = null output,
@CURRENCYISOCURRENCYCODE nvarchar(3) = null output,
@CURRENCYDECIMALDIGITS tinyint = 0 output,
@CURRENCYSYMBOL nvarchar(5) = null output,
@CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint = 0 output,
@ONLYGETSUMMARY bit = 0,
@BREAKDOWNBY tinyint = 0,
@CAMPAIGNFILTERMODE tinyint = 0,
@CAMPAIGNSSELECTED xml = null
)
with execute as OWNER
as
begin
set nocount on
declare @CONSTITID uniqueidentifier;
declare @DATEFROM datetime;
declare @DATETO datetime;
declare @ISUK bit;
declare @INCLUDEMEMBERGIVING bit = 0;
if @ISGROUP = 1
select @INCLUDEMEMBERGIVING =
case
when GROUPDATA.GROUPTYPECODE = 0 or GROUPTYPE.INCLUDEMEMBERGIVING = 1 then 1
else 0
end
from dbo.GROUPDATA
left join dbo.GROUPTYPE on GROUPDATA.GROUPTYPEID = GROUPTYPE.ID
where GROUPDATA.ID = @CONSTITUENTID;
set @ISUK = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D');
/* Get currency info */
declare @ORGANIZATIONCURRENCYID uniqueidentifier = null;
declare @CURRENCYID uniqueidentifier = null;
declare @CURRENCYROUNDINGTYPECODE tinyint;
set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @ORIGINCODE tinyint
select @ORIGINCODE = ORGANIZATIONAMOUNTORIGINCODE
from dbo.MULTICURRENCYCONFIGURATION;
if @CURRENCYCODE = 1
set @CURRENCYID = @ORGANIZATIONCURRENCYID;
else
set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
select
@CURRENCYISOCURRENCYCODE = CURRENCY.ISO4217,
@CURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
@CURRENCYSYMBOL = CURRENCY.CURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.CURRENCY where ID = @CURRENCYID;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
/* Get Recognition IDs */
if object_id('tempdb..#TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS') is not null
drop table #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS;
create table #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS
(
ID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
REVENUEID uniqueidentifier,
[DATE] datetime,
TRANSACTIONAMOUNT money,
TRANSACTIONTYPECODE tinyint,
TRANSACTIONTYPE nvarchar(50) collate database_default,
REVENUEDATEADDED datetime,
REVENUEBASECURRENCYID uniqueidentifier,
REVENUETRANSACTIONCURRENCYID uniqueidentifier,
REVENUESPLITTYPE nvarchar(50) collate database_default
);
create index IDX on #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS(ID,DATE,REVENUEBASECURRENCYID,REVENUEID,REVENUETRANSACTIONCURRENCYID,TRANSACTIONTYPECODE)
/* Performance: Replaced dynamic SQL with a SQL table variable *
* and added branching logic to filter the records based *
* @CAMPAIGNFILTERMODE or @INCLUDEMEMBERGIVING */
declare @CAMPAIGNFILTERTABLE table (ID uniqueidentifier)
if @CAMPAIGNFILTERMODE != 0
begin
insert into @CAMPAIGNFILTERTABLE
select T.c.value('(ID)[1]','uniqueidentifier')
from @CAMPAIGNSSELECTED.nodes('/CAMPAIGNSSELECTED/ITEM') T(c);
end
-- Using dynamic SQL so the proper plan can be generated for the revenue filter. When the revenue filter was passed in as a parameter,
-- the plan was created to account for all available revenue filters. Putting the value in the query allows the optimizer to create a
-- plan with only the correct revenue filter. Not using "option (recompile)" so the plan for the same filter can be re-used.
declare @FILTEREDREVENUESQL as nvarchar(max) = '
declare @CONSTITS table (CONSTITUENTID uniqueidentifier, STARTDATE datetime, ENDDATE datetime);
insert into @CONSTITS
values(@CONSTITUENTID, @STARTDATE, @ENDDATE);
if @INCLUDEMEMBERGIVING = 1
begin
insert into @CONSTITS
select
GROUPMEMBER.MEMBERID as CONSTITUENTID,
case
when GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM < @STARTDATE then
@STARTDATE
else
GROUPMEMBERDATERANGE.DATEFROM
end STARTDATE,
case
when GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @ENDDATE then
@ENDDATE
else
GROUPMEMBERDATERANGE.DATETO
end ENDDATE
from dbo.GROUPMEMBER
left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
where GROUPMEMBER.GROUPID = @CONSTITUENTID
end
if object_id(''tempdb..#TMP_DATA_REVENUESUMMARY_CONSTITUENTREVENUE'') is not null
drop table #TMP_DATA_REVENUESUMMARY_CONSTITUENTREVENUE;
create table #TMP_DATA_REVENUESUMMARY_CONSTITUENTREVENUE (
ID uniqueidentifier primary key,
CONSTITUENTID uniqueidentifier,
DATE datetime,
TYPECODE tinyint,
TYPE nvarchar(50),
DATEADDED datetime,
TRANSACTIONCURRENCYID uniqueidentifier,
PDACCOUNTSYSTEMID uniqueidentifier,
DELETEDON datetime
)
insert into #TMP_DATA_REVENUESUMMARY_CONSTITUENTREVENUE(
ID,
CONSTITUENTID,
DATE,
TYPECODE,
TYPE,
DATEADDED,
TRANSACTIONCURRENCYID,
PDACCOUNTSYSTEMID,
DELETEDON
)
select
REVENUE.ID,
REVENUE.CONSTITUENTID,
REVENUE.CALCULATEDDATE [DATE],
REVENUE.TYPECODE,
REVENUE.TYPE,
REVENUE.DATEADDED,
REVENUE.TRANSACTIONCURRENCYID,
REVENUE.PDACCOUNTSYSTEMID,
REVENUE.DELETEDON
from @CONSTITS as CONSTITS
inner join FINANCIALTRANSACTION as REVENUE on CONSTITS.CONSTITUENTID = REVENUE.CONSTITUENTID
inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
where
(REVENUE.CALCULATEDDATE >= isnull(CONSTITS.STARTDATE, REVENUE.CALCULATEDDATE)) and
(REVENUE.CALCULATEDDATE <= isnull(CONSTITS.ENDDATE, REVENUE.CALCULATEDDATE)) and
REVENUE.DELETEDON is null and
REVENUE.TYPECODE in (0,1,3,4,5,6,7,8,9,15)
-- NOTE: The columns selected need to match the columns on #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS since
-- when executing the select, it is inserted into #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS
select
REVENUESPLIT.ID
,REVENUE.CONSTITUENTID
,REVENUESPLIT.FINANCIALTRANSACTIONID
,REVENUE.DATE
,REVENUESPLIT.TRANSACTIONAMOUNT
,REVENUE.TYPECODE
,REVENUE.TYPE
,REVENUE.DATEADDED '
if dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION(N'Multicurrency') = 1
set @FILTEREDREVENUESQL += ',CS.BASECURRENCYID '
else
set @FILTEREDREVENUESQL += ',REVENUE.TRANSACTIONCURRENCYID '
set @FILTEREDREVENUESQL += ',REVENUE.TRANSACTIONCURRENCYID
,REVENUESPLIT_EXT.TYPE
from #TMP_DATA_REVENUESUMMARY_CONSTITUENTREVENUE as REVENUE
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT
on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT
on REVENUESPLIT_EXT.ID = REVENUESPLIT.ID '
if @REVENUEFILTERID is not null
begin
set @FILTEREDREVENUESQL = @FILTEREDREVENUESQL + '
-- Specifying an additional join clause when @REVENUEFILTERID is in use
inner join dbo.UFN_REVENUEFILTER_BYID(''' + cast(@REVENUEFILTERID as nvarchar(36)) + ''') FILTERED
on REVENUESPLIT.ID = FILTERED.ID'
end
if dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION(N'Multicurrency') = 1
set @FILTEREDREVENUESQL = @FILTEREDREVENUESQL + '
left join dbo.PDACCOUNTSYSTEM as PDA
on REVENUE.PDACCOUNTSYSTEMID = PDA.ID
left join dbo.CURRENCYSET as CS
on CS.ID = PDA.CURRENCYSETID '
set @FILTEREDREVENUESQL = @FILTEREDREVENUESQL + '
where
-- Exclude order payment splits and use the order splits
REVENUESPLIT_EXT.APPLICATIONCODE <> 10 and
REVENUESPLIT.DELETEDON is null;
drop table #TMP_DATA_REVENUESUMMARY_CONSTITUENTREVENUE;'
insert into #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS(
ID
,CONSTITUENTID
,REVENUEID
,DATE
,TRANSACTIONAMOUNT
,TRANSACTIONTYPECODE
,TRANSACTIONTYPE
,REVENUEDATEADDED
,REVENUEBASECURRENCYID
,REVENUETRANSACTIONCURRENCYID
,REVENUESPLITTYPE
)
exec sp_executesql @FILTEREDREVENUESQL, N'@CONSTITUENTID uniqueidentifier, @STARTDATE datetime, @ENDDATE datetime, @INCLUDEMEMBERGIVING bit',
@CONSTITUENTID, @STARTDATE, @ENDDATE, @INCLUDEMEMBERGIVING;
if @CAMPAIGNFILTERMODE != 0
begin
delete T from #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS as T
where
not exists (
select
1
from
dbo.REVENUESPLITCAMPAIGN
inner join @CAMPAIGNFILTERTABLE CAMPAIGNFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNFILTER.ID
where
T.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID
)
end
/*remove based on site filter*/
if (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) <> 1 or @SITEFILTERMODE != 0) and exists(select top 1 1 from dbo.SITE)
begin
delete FILTERED
from #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS FILTERED
where
not exists
(
select top 1 REVSITES.SITEID
from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FILTERED.ID) REVSITES
where
-- Using a case statement since the standard site extension filters
-- resulted in a poor plan
case
when exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)) then 1
else 0
end = 1
and
(
@SITEFILTERMODE = 0
or
exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
)
)
end
/* Get Constituent totals */
--Do not check for the existence of refunds unless basic programs is installed
if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('BB1C17BC-9E0B-4683-B490-EE40D511FA05') = 1
with REV_CTE as
(
select
FILTERED.REVENUEID ID,
case when FILTERED.TRANSACTIONTYPECODE in (
1, --Pledge
3, --Matching gift claim
4, --Planned gift
6, --Grant award
8, --Donor challenge claim
9, --Pending gift
15 --Membership Installment Plan
)
then
(select REVENUESPLITBALANCE.BALANCE
from dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY(
FILTERED.REVENUEID,
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@CURRENCYDECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE,
@CURRENTDATE,
@ORIGINCODE,
@CURRENCYCODE
) as REVENUESPLITBALANCE
where REVENUESPLITBALANCE.ID = FILTERED.ID)
else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(FILTERED.ID, @CURRENCYID) - coalesce(CREDITSPLIT.CREDITTOTAL, 0)
end AMOUNT,
case when @BREAKDOWNBY = 0 then
dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(FILTERED.DATE,0)
else dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(FILTERED.DATE) end YEAR,
case when @ISUK = 0 then null
else
case FILTERED.TRANSACTIONTYPECODE
when 1 then case @CURRENCYCODE
when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, FILTERED.REVENUEBASECURRENCYID)
when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, FILTERED.REVENUETRANSACTIONCURRENCYID)
else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, @CURRENCYID) end
when 15 then case @CURRENCYCODE
when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, FILTERED.REVENUEBASECURRENCYID)
when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, FILTERED.REVENUETRANSACTIONCURRENCYID)
else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, @CURRENCYID) end
when 4 then 0
else
case @CURRENCYCODE
when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(FILTERED.ID, 1,FILTERED.REVENUEBASECURRENCYID)
when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(FILTERED.ID, 1,FILTERED.REVENUETRANSACTIONCURRENCYID)
else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(FILTERED.ID, 1,@CURRENCYID)
end
end
end REVENUEWITHGIFTAID,
FILTERED.TRANSACTIONTYPECODE
from #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS FILTERED
left outer join dbo.UFN_CREDIT_GETSPLITCREDITAMOUNT_BULK() as CREDITSPLIT
on CREDITSPLIT.SOURCELINEITEMID = FILTERED.ID
)
select
@TOTALNUMBER = count(distinct ID),
@TOTALAMOUNT = sum(AMOUNT),
@TOTALYEARS = count(distinct(YEAR)),
@TOTALREVENUEWITHGIFTAID = sum(case when TRANSACTIONTYPECODE = 9 then 0 else REVENUEWITHGIFTAID end)
from REV_CTE
else
begin
--Do not compensate for multicurrency if it is not installed
if dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION(N'Multicurrency') = 0
with REV_CTE as
(
select
FILTERED.REVENUEID ID,
case when FILTERED.TRANSACTIONTYPECODE in (
1, --Pledge
3, --Matching gift claim
4, --Planned gift
6, --Grant award
8, --Donor challenge claim
9, --Pending gift
15 --Membership Installment Plan
)
then dbo.UFN_PLEDGE_GETSPLITBALANCE(FILTERED.REVENUEID, FILTERED.ID)
else FILTERED.TRANSACTIONAMOUNT
end AMOUNT,
case when @BREAKDOWNBY = 0 then
dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(FILTERED.DATE,0)
else dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(FILTERED.DATE) end YEAR,
case when @ISUK = 0 then null
else
case FILTERED.TRANSACTIONTYPECODE
when 1 then case @CURRENCYCODE
when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, FILTERED.REVENUEBASECURRENCYID)
when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, FILTERED.REVENUETRANSACTIONCURRENCYID)
else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, @CURRENCYID) end
when 15 then case @CURRENCYCODE
when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, FILTERED.REVENUEBASECURRENCYID)
when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, FILTERED.REVENUETRANSACTIONCURRENCYID)
else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, @CURRENCYID) end
when 4 then 0
else
case @CURRENCYCODE
when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(FILTERED.ID, 1,FILTERED.REVENUEBASECURRENCYID)
when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(FILTERED.ID, 1,FILTERED.REVENUETRANSACTIONCURRENCYID)
else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(FILTERED.ID, 1,@CURRENCYID)
end
end
end REVENUEWITHGIFTAID,
FILTERED.TRANSACTIONTYPECODE
from #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS FILTERED
)
select
@TOTALNUMBER = count(distinct ID),
@TOTALAMOUNT = sum(AMOUNT),
@TOTALYEARS = count(distinct(YEAR)),
@TOTALREVENUEWITHGIFTAID = sum(case when TRANSACTIONTYPECODE = 9 then 0 else REVENUEWITHGIFTAID end)
from REV_CTE
else
with REV_CTE as
(
select
FILTERED.REVENUEID ID,
case when FILTERED.TRANSACTIONTYPECODE in (
1, --Pledge
3, --Matching gift claim
4, --Planned gift
6, --Grant award
8, --Donor challenge claim
9, --Pending gift
15 --Membership Installment Plan
)
then
(select REVENUESPLITBALANCE.BALANCE
from dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY(
FILTERED.REVENUEID,
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@CURRENCYDECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE,
@CURRENTDATE,
@ORIGINCODE,
@CURRENCYCODE
) as REVENUESPLITBALANCE
where REVENUESPLITBALANCE.ID = FILTERED.ID)
else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(FILTERED.ID, @CURRENCYID)
end AMOUNT,
case when @BREAKDOWNBY = 0 then
dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(FILTERED.DATE,0)
else dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(FILTERED.DATE) end YEAR,
case when @ISUK = 0 then null
else
case FILTERED.TRANSACTIONTYPECODE
when 1 then case @CURRENCYCODE
when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, FILTERED.REVENUEBASECURRENCYID)
when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, FILTERED.REVENUETRANSACTIONCURRENCYID)
else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, @CURRENCYID) end
when 15 then case @CURRENCYCODE
when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, FILTERED.REVENUEBASECURRENCYID)
when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, FILTERED.REVENUETRANSACTIONCURRENCYID)
else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNTBALANCE(FILTERED.ID, @CURRENCYID) end
when 4 then 0
else
case @CURRENCYCODE
when 0 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(FILTERED.ID, 1,FILTERED.REVENUEBASECURRENCYID)
when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(FILTERED.ID, 1,FILTERED.REVENUETRANSACTIONCURRENCYID)
else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(FILTERED.ID, 1,@CURRENCYID)
end
end
end REVENUEWITHGIFTAID,
FILTERED.TRANSACTIONTYPECODE
from #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS FILTERED
)
select
@TOTALNUMBER = count(distinct ID),
@TOTALAMOUNT = sum(AMOUNT),
@TOTALYEARS = count(distinct(YEAR)),
@TOTALREVENUEWITHGIFTAID = sum(case when TRANSACTIONTYPECODE = 9 then 0 else REVENUEWITHGIFTAID end)
from REV_CTE
end
set @TOTALNUMBER = coalesce(@TOTALNUMBER, 0);
set @TOTALAMOUNT = coalesce(@TOTALAMOUNT, 0);
set @TOTALREVENUEWITHGIFTAID = coalesce(@TOTALREVENUEWITHGIFTAID, 0);
if @ONLYGETSUMMARY <> 1
begin
declare @YEARSQL nvarchar(max);
declare @YEARSNIPPET nvarchar(100);
if @BREAKDOWNBY = 0
set @YEARSNIPPET = 'dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(R.DATE,0)';
else
set @YEARSNIPPET = 'dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(R.DATE)';
set @YEARSQL =
'select ' + @YEARSNIPPET + ' FISCALYEAR_FIRSTDAY
from #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS R
group by ' + @YEARSNIPPET + '
order by ' + @YEARSNIPPET + ' desc'
declare @THISYEAR datetime;
declare @LASTYEAR datetime;
set @THISYEAR = case when @BREAKDOWNBY = 0 then dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(@CURRENTDATE, 0) else dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(@CURRENTDATE) end;
set @LASTYEAR = case when @BREAKDOWNBY = 0 then dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(dateadd(year,-1,@CURRENTDATE),0) else dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(dateadd(year,-1,@CURRENTDATE)) end;
--CONSECUTIVE YEARS
exec dbo.USP_CONSTITUENT_GETCONSECUTIVEYEARSFROMGIVENSET
@YEARSQL,
@THISYEAR,
@LASTYEAR,
@CONSECUTIVEYEARS output,@GIVENSINCEFISCALYEAR output
if dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION(N'Multicurrency') = 1
begin
--FIRST GIFT
select top 1
@FIRSTID = min(convert(nvarchar(36),RS.ID)),
@FIRSTRECORDID = FILTERED.REVENUEID,
@FIRSTDATE = FILTERED.DATE,
@FIRSTTYPECODE = FILTERED.TRANSACTIONTYPECODE,
@FIRSTTYPE = FILTERED.TRANSACTIONTYPE,
@FIRSTAMOUNT = sum(RS.AMOUNTINCURRENCY)
from #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS FILTERED
left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@CURRENCYDECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE) as RS on FILTERED.ID = RS.ID
group by
FILTERED.REVENUEID, FILTERED.DATE, FILTERED.TRANSACTIONTYPECODE, FILTERED.TRANSACTIONTYPE, FILTERED.REVENUEDATEADDED
order by
FILTERED.DATE asc, FILTERED.REVENUEDATEADDED asc;
--LATEST GIFT
select top 1
@LATESTID = min(convert(nvarchar(36),RS.ID)),
@LATESTRECORDID = FILTERED.REVENUEID,
@LATESTDATE = FILTERED.DATE,
@LATESTTYPECODE = FILTERED.TRANSACTIONTYPECODE,
@LATESTTYPE = FILTERED.TRANSACTIONTYPE,
@LATESTAMOUNT = sum(RS.AMOUNTINCURRENCY)
from #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS FILTERED
left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@CURRENCYDECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE) as RS on FILTERED.ID = RS.ID
group by
FILTERED.REVENUEID, FILTERED.DATE, FILTERED.TRANSACTIONTYPECODE, FILTERED.TRANSACTIONTYPE, FILTERED.REVENUEDATEADDED
order by
FILTERED.DATE desc, FILTERED.REVENUEDATEADDED desc;
end
else
begin
--FIRST GIFT
select top 1
@FIRSTID = min(convert(nvarchar(36),FILTERED.ID)),
@FIRSTRECORDID = FILTERED.REVENUEID,
@FIRSTDATE = FILTERED.DATE,
@FIRSTTYPECODE = FILTERED.TRANSACTIONTYPECODE,
@FIRSTTYPE = FILTERED.TRANSACTIONTYPE,
@FIRSTAMOUNT = sum(FILTERED.TRANSACTIONAMOUNT)
from #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS FILTERED
group by
FILTERED.REVENUEID, FILTERED.DATE, FILTERED.TRANSACTIONTYPECODE, FILTERED.TRANSACTIONTYPE, FILTERED.REVENUEDATEADDED
order by
FILTERED.DATE asc, FILTERED.REVENUEDATEADDED asc;
--LATEST GIFT
select top 1
@LATESTID = min(convert(nvarchar(36),FILTERED.ID)),
@LATESTRECORDID = FILTERED.REVENUEID,
@LATESTDATE = FILTERED.DATE,
@LATESTTYPECODE = FILTERED.TRANSACTIONTYPECODE,
@LATESTTYPE = FILTERED.TRANSACTIONTYPE,
@LATESTAMOUNT = sum(FILTERED.TRANSACTIONAMOUNT)
from #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS FILTERED
group by
FILTERED.REVENUEID, FILTERED.DATE, FILTERED.TRANSACTIONTYPECODE, FILTERED.TRANSACTIONTYPE, FILTERED.REVENUEDATEADDED
order by
FILTERED.DATE desc, FILTERED.REVENUEDATEADDED desc;
end
end
/* Get Group\Household figures */
if @INCLUDEMEMBERGIVING = 1 or @HOUSEHOLDID is not null
begin
if object_id('tempdb..#TMP_DATA_REVENUESUMMARY_HOUSEHOLDREVSPLITIDS') is not null
drop table #TMP_DATA_REVENUESUMMARY_HOUSEHOLDREVSPLITIDS;
create table #TMP_DATA_REVENUESUMMARY_HOUSEHOLDREVSPLITIDS
(
ID uniqueidentifier,
REVENUEID uniqueidentifier,
TRANSACTIONTYPECODE tinyint,
[DATE] datetime,
TRANSACTIONAMOUNT money,
REVENUESPLITTYPE nvarchar(50) collate database_default
);
--Get the revenue to include
if @ISGROUP = 1
begin
--Insert members from previous get to keep from having to get them twice
insert into #TMP_DATA_REVENUESUMMARY_HOUSEHOLDREVSPLITIDS(ID,REVENUEID,[DATE],TRANSACTIONAMOUNT,TRANSACTIONTYPECODE,REVENUESPLITTYPE)
select FILTERED.ID,FILTERED.REVENUEID,FILTERED.[DATE],FILTERED.TRANSACTIONAMOUNT,FILTERED.TRANSACTIONTYPECODE,FILTERED.REVENUESPLITTYPE
from #TMP_DATA_REVENUESUMMARY_CONSTITREVSPLITIDS FILTERED
where FILTERED.CONSTITUENTID <> @CONSTITUENTID;
end
else
begin
declare @SQL nvarchar(max) = '
declare @HOUSEHOLDANDMEMBERS table (CONSTITUENTID uniqueidentifier, STARTDATE datetime, ENDDATE datetime)
insert into @HOUSEHOLDANDMEMBERS (CONSTITUENTID, STARTDATE, ENDDATE)
select
@HOUSEHOLDID as CONSTITUENTID,
null as STARTDATE,
null as ENDDATE
union all
select
GROUPMEMBER.MEMBERID as CONSTITUENTID,
case when GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM < @STARTDATE then @STARTDATE else GROUPMEMBERDATERANGE.DATEFROM end STARTDATE,
case when GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @ENDDATE then @ENDDATE else GROUPMEMBERDATERANGE.DATETO end ENDDATE
from dbo.GROUPMEMBER
left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
where
GROUPMEMBER.GROUPID = @HOUSEHOLDID
';
if @CAMPAIGNFILTERMODE != 0
begin
set @SQL = @SQL + '
declare @CAMPAIGNFILTERTABLE table (ID uniqueidentifier)
insert into @CAMPAIGNFILTERTABLE
select T.c.value(''(ID)[1]'',''uniqueidentifier'')
from @CAMPAIGNSSELECTED.nodes(''/CAMPAIGNSSELECTED/ITEM'') T(c);' + char(13);
end
select @SQL = @SQL + '
-- NOTE: The columns selected need to match the columns on #TMP_DATA_REVENUESUMMARY_HOUSEHOLDREVSPLITIDS since
-- when executing the select, it is inserted into #TMP_DATA_REVENUESUMMARY_HOUSEHOLDREVSPLITIDS
select REVENUESPLIT.ID,REVENUESPLIT.FINANCIALTRANSACTIONID,REVENUE.TYPECODE,cast(REVENUE.DATE as datetime),REVENUESPLIT.TRANSACTIONAMOUNT,REVENUESPLIT_EXT.TYPE
from dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID' + char(13);
if @REVENUEFILTERID is not null
set @SQL = @SQL + 'inner join dbo.UFN_REVENUEFILTER_BYID(''' + cast(@REVENUEFILTERID as nvarchar(36)) + ''') FILTERED on REVENUESPLIT.ID = FILTERED.ID ' + char(13);
set @SQL = @SQL +
'
inner join dbo.FINANCIALTRANSACTION as REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
inner join @HOUSEHOLDANDMEMBERS CONSTITS on CONSTITS.CONSTITUENTID = REVENUE.CONSTITUENTID
where
REVENUE.TYPECODE in (0,1,3,4,5,6,7,8,9,15) and
-- Exclude order payment splits and use the order splits
REVENUESPLIT_EXT.APPLICATIONCODE <> 10 and
REVENUE.DELETEDON is null and
REVENUESPLIT.DELETEDON is null and
REVENUESPLIT.TYPECODE <> 1 and
(CONSTITS.STARTDATE is null or REVENUE.CALCULATEDDATE >= CONSTITS.STARTDATE) and
(CONSTITS.ENDDATE is null or REVENUE.CALCULATEDDATE <= CONSTITS.ENDDATE)';
if @CAMPAIGNFILTERMODE != 0
begin
set @SQL = @SQL +
'and exists (
select 1
from dbo.REVENUESPLITCAMPAIGN
inner join @CAMPAIGNFILTERTABLE CAMPAIGNFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNFILTER.ID
where REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
)' + char(13);
end
insert into #TMP_DATA_REVENUESUMMARY_HOUSEHOLDREVSPLITIDS(ID,REVENUEID,TRANSACTIONTYPECODE,DATE,TRANSACTIONAMOUNT,REVENUESPLITTYPE)
exec sp_executesql @SQL, N'@HOUSEHOLDID uniqueidentifier, @STARTDATE datetime, @ENDDATE datetime, @CAMPAIGNSSELECTED xml',
@HOUSEHOLDID, @STARTDATE, @ENDDATE, @CAMPAIGNSSELECTED;
end
/*remove based on site filter*/
if dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) <> 1 and exists(select top 1 1 from dbo.SITE)
begin
delete FILTERED
from #TMP_DATA_REVENUESUMMARY_HOUSEHOLDREVSPLITIDS FILTERED
where
not exists
(
select top 1 REVSITES.SITEID
from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FILTERED.ID) REVSITES
where
-- Using a case statement since the standard site extension filters
-- resulted in a poor plan
case
when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
when exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)) then 1
else 0
end = 1
and
(
@SITEFILTERMODE = 0
or
exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
)
);
end
/* Get Constituent totals */
if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('BB1C17BC-9E0B-4683-B490-EE40D511FA05') = 1
with REV_CTE as
(
select
case
when FILTERED.TRANSACTIONTYPECODE in (
1, --Pledge
3, --Matching gift claim
4, --Planned gift
6, --Grant award
8, --Donor challenge claim
9, --Pending gift
15 --Membership Installment Plan
)
then
(select REVENUESPLITBALANCE.BALANCE
from dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY(
FILTERED.REVENUEID,
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@CURRENCYDECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE,
@CURRENTDATE,
@ORIGINCODE,
@CURRENCYCODE
) as REVENUESPLITBALANCE
where REVENUESPLITBALANCE.ID = FILTERED.ID)
else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(FILTERED.ID, @CURRENCYID) - coalesce(CREDITSPLIT.CREDITTOTAL, 0)
end AMOUNT
from #TMP_DATA_REVENUESUMMARY_HOUSEHOLDREVSPLITIDS FILTERED
left outer join dbo.UFN_CREDIT_GETSPLITCREDITAMOUNT_BULK() as CREDITSPLIT
on CREDITSPLIT.SOURCELINEITEMID = FILTERED.ID
)
select
@TOTALAMOUNT_HOUSEHOLD = sum(AMOUNT)
from REV_CTE;
else
begin
--Do not compensate for multicurrency if it is not installed
if dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION(N'Multicurrency') = 0
with REV_CTE as
(
select
case
when FILTERED.TRANSACTIONTYPECODE in (
1, --Pledge
3, --Matching gift claim
4, --Planned gift
6, --Grant award
8, --Donor challenge claim
9, --Pending gift
15 --Membership Installment Plan
)
then dbo.UFN_PLEDGE_GETSPLITBALANCE(FILTERED.REVENUEID, FILTERED.ID)
else FILTERED.TRANSACTIONAMOUNT
end AMOUNT
from #TMP_DATA_REVENUESUMMARY_HOUSEHOLDREVSPLITIDS FILTERED
)
select
@TOTALAMOUNT_HOUSEHOLD = sum(AMOUNT)
from REV_CTE;
else
with REV_CTE as
(
select
case
when FILTERED.TRANSACTIONTYPECODE in (
1, --Pledge
3, --Matching gift claim
4, --Planned gift
6, --Grant award
8, --Donor challenge claim
9, --Pending gift
15 --Membership Installment Plan
)
then
(select REVENUESPLITBALANCE.BALANCE
from dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY(
FILTERED.REVENUEID,
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@CURRENCYDECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE,
@CURRENTDATE,
@ORIGINCODE,
@CURRENCYCODE
) as REVENUESPLITBALANCE
where REVENUESPLITBALANCE.ID = FILTERED.ID)
else dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(FILTERED.ID, @CURRENCYID)
end AMOUNT
from #TMP_DATA_REVENUESUMMARY_HOUSEHOLDREVSPLITIDS FILTERED
)
select
@TOTALAMOUNT_HOUSEHOLD = sum(AMOUNT)
from REV_CTE;
end
end
end