USP_CONSTITUENTSMARTFIELD_REVENUESPLITAMOUNTS
Returns aggregate revenue values to be used in various constituent revenue application smart fields.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @CALCULATIONTYPE | tinyint | IN | |
| @CALCULATEDREVENUE | tinyint | IN | |
| @ASOF | datetime | IN | |
| @DATETYPECODE | tinyint | IN | |
| @STARTDATERECENTDATEUNITCODE | tinyint | IN | |
| @STARTDATERECENTDATEINTERVAL | int | IN | |
| @REVENUETYPECODES | xml | IN | |
| @APPLICATIONTRANSACTIONTYPECODES | xml | IN | |
| @REVENUEAPPLICATIONSELECTION | uniqueidentifier | IN | |
| @RECOGNITIONTYPES | xml | IN | |
| @CURRENCYID | uniqueidentifier | IN | |
| @SMARTFIELDID | uniqueidentifier | IN | |
| @SITES | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_CONSTITUENTSMARTFIELD_REVENUESPLITAMOUNTS
(
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@CALCULATIONTYPE tinyint,
@CALCULATEDREVENUE tinyint,
@ASOF datetime,
@DATETYPECODE tinyint = null,
@STARTDATERECENTDATEUNITCODE tinyint = null,
@STARTDATERECENTDATEINTERVAL int = null,
@REVENUETYPECODES xml = null,
@APPLICATIONTRANSACTIONTYPECODES xml = null,
@REVENUEAPPLICATIONSELECTION uniqueidentifier = null,
@RECOGNITIONTYPES xml = null,
@CURRENCYID uniqueidentifier = null,
@SMARTFIELDID uniqueidentifier = null,
@SITES xml = null
)
with execute as owner
as
/*
This smart field will return an aggregation of revenue application values for ALL constituents in the database
*/
declare @DECIMALDIGITS tinyint;
declare @ROUNDINGTYPECODE tinyint;
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.[UFN_CURRENCY_GETORGANIZATIONCURRENCY]();
if @CURRENCYID is null
set @CURRENCYID = @ORGANIZATIONCURRENCYID;
select
@DECIMALDIGITS = [CURRENCY].[DECIMALDIGITS],
@ROUNDINGTYPECODE = [CURRENCY].[ROUNDINGTYPECODE]
from dbo.[CURRENCY]
where [CURRENCY].[ID] = @CURRENCYID;
if object_id('tempdb..#TMP_REVENUERECORDS_REVENUESPLITAMOUNTS') is not null
drop table #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS
create table #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS
(
ID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
AMOUNT money,
DATE datetime,
DATEADDED datetime
);
declare @REVENUETYPESFILTER table
(
REVENUECODE tinyint primary key
);
declare @APPLICATIONTRANSACTIONTYPECODESFILTER table
(
APPLICATIONCODE tinyint,
TRANSACTIONTYPECODE tinyint
);
declare @RECOGNITIONTYPECODESFILTER table (
RECOGNITIONTYPECODEID uniqueidentifier primary key
);
if object_id('tempdb..#TMP_CONSTITUENTFILTER_REVENUESPLITAMOUNTS') is not null
drop table #TMP_CONSTITUENTFILTER_REVENUESPLITAMOUNTS
create table #TMP_CONSTITUENTFILTER_REVENUESPLITAMOUNTS
(
ID uniqueidentifier primary key
);
/*
Calculate start and end dates
*/
declare @CURRENTDATEEARLIESTTIME datetime;
set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
declare @COMPUTEDSTARTDATE datetime;
declare @COMPUTEDENDDATE datetime;
if @DATETYPECODE = 0
begin
set @COMPUTEDSTARTDATE = @STARTDATE;
set @COMPUTEDENDDATE = @ENDDATE;
end
else if @DATETYPECODE = 1
begin
set @COMPUTEDSTARTDATE =
case @STARTDATERECENTDATEUNITCODE
when 0 then dateadd(year, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
when 1 then dateadd(quarter, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
when 2 then dateadd(month, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
when 3 then dateadd(week, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
when 4 then dateadd(day, -(@STARTDATERECENTDATEINTERVAL), @CURRENTDATEEARLIESTTIME)
end;
set @COMPUTEDENDDATE = null;
end
set @COMPUTEDSTARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@COMPUTEDSTARTDATE);
set @COMPUTEDENDDATE = dbo.UFN_DATE_GETLATESTTIME(@COMPUTEDENDDATE);
/* Load filter tables based on user selected conditions. */
if @REVENUETYPECODES is not null
begin
insert into @REVENUETYPESFILTER(REVENUECODE)
select distinct T.c.value('(REVENUETYPES)[1]','tinyint')
from @REVENUETYPECODES.nodes('/REVENUETYPECODES/ITEM') T(c);
if not exists (select 1 from @REVENUETYPESFILTER)
set @REVENUETYPECODES = null;
end
if @APPLICATIONTRANSACTIONTYPECODES is not null
begin
insert into @APPLICATIONTRANSACTIONTYPECODESFILTER(APPLICATIONCODE, TRANSACTIONTYPECODE)
select
T.c.value('(APPLICATION)[1]','tinyint') [APPLICATIONCODE],
T.c.value('(TRANSACTIONTYPE)[1]','tinyint') [TRANSACTIONTYPECODE]
from @APPLICATIONTRANSACTIONTYPECODES.nodes('/SELECTIONS/ITEM') T(c)
where T.c.value('(TRANSACTIONTYPE)[1]','tinyint') is not null;
if not exists (select 1 from @APPLICATIONTRANSACTIONTYPECODESFILTER)
set @APPLICATIONTRANSACTIONTYPECODES = null;
end
if @RECOGNITIONTYPES is not null
begin
insert into @RECOGNITIONTYPECODESFILTER(RECOGNITIONTYPECODEID)
select T.c.value('(ID)[1]','uniqueidentifier')
from @RECOGNITIONTYPES.nodes('/RECOGNITIONTYPECODES/ITEM') T(c);
--JamesWill 2010-01-20 Some smart fields seem to be using the above XML and others the XML here.
--The correct fix would be to update all the smart fields to use the same XML. But we ship next week
--and I am not at all confident that I wouldn't miss one. I'd rather do this and ship without the bug.
if not exists (select 1 from @RECOGNITIONTYPECODESFILTER)
insert into @RECOGNITIONTYPECODESFILTER(RECOGNITIONTYPECODEID)
select T.c.value('(RECOGNITIONTYPECODEID)[1]','uniqueidentifier')
from @RECOGNITIONTYPES.nodes('/RECOGNITIONTYPES/ITEM') T(c);
if not exists (select 1 from @RECOGNITIONTYPECODESFILTER)
set @RECOGNITIONTYPES = null;
end
declare @SITESFILTER table(ID uniqueidentifier primary key);
insert into
@SITESFILTER(ID)
select distinct
T.c.value('(SITEID)[1]','uniqueidentifier')
FROM
@SITES.nodes('/SITES/ITEM') T(c)
create table #CONSTITUENTSITEFILTER (ID uniqueidentifier primary key);
insert into
#CONSTITUENTSITEFILTER(ID)
select distinct
CONSTITUENTSITE.CONSTITUENTID
from
CONSTITUENTSITE
inner join
@SITESFILTER as SITES on SITES.ID = CONSTITUENTSITE.SITEID
/*
Find all valid constituents based on the date that the smart field was last updated.
If there is no @ASOF value then all constituents need to be processed.
If the date type is 'Recent dates' then all constituents need to be processed.
If the calculation type is 'Consecutive giving year' then all constituents need to be processed.
*/
/*
if the query used by this smart field has changed since this smartfield was last processed, then
we need to process as if the smart field had never been processed, so clear the @ASOF date
*/
if @ASOF is not null
and nullif(@REVENUEAPPLICATIONSELECTION,'00000000-0000-0000-0000-000000000000') is not null
and exists(select top 1 1
from dbo.SMARTFIELD
where ID=@SMARTFIELDID
and LASTRUNON < coalesce(
(
select top 1 DATECHANGED
from dbo.IDSETREGISTER
where ID=@REVENUEAPPLICATIONSELECTION
)
,LASTRUNON
)
)
set @ASOF=null;
declare @CONSTITUENTFILTERVIEW as nvarchar(50) = '#TMP_CONSTITUENTFILTER_REVENUESPLITAMOUNTS'
if @ASOF is null or @CALCULATIONTYPE = 11 or (@DATETYPECODE = 1 and @STARTDATERECENTDATEINTERVAL is not null)
begin
if @SITES is null
begin
set @CONSTITUENTFILTERVIEW = 'dbo.CONSTITUENT'
end
else
begin
insert into
#TMP_CONSTITUENTFILTER_REVENUESPLITAMOUNTS
select
ID
from
#CONSTITUENTSITEFILTER
end
end
else if @CALCULATEDREVENUE = 0 --REVENUE
begin
if @SITES is null
begin
insert into #TMP_CONSTITUENTFILTER_REVENUESPLITAMOUNTS(ID)
select CONSTITUENTID from dbo.FINANCIALTRANSACTION
where FINANCIALTRANSACTION.DATECHANGED > @ASOF and CONSTITUENTID is not null and FINANCIALTRANSACTION.DELETEDON is null
union
select REVENUEAUDIT.CONSTITUENTID
from dbo.FINANCIALTRANSACTIONAUDIT as REVENUEAUDIT
inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUEAUDIT.CONSTITUENTID
where REVENUEAUDIT.AUDITDATE > @ASOF and REVENUEAUDIT.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
union
select FINANCIALTRANSACTION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
where FINANCIALTRANSACTIONLINEITEM.DATECHANGED > @ASOF and FINANCIALTRANSACTION.CONSTITUENTID is not null
and FINANCIALTRANSACTION.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
union
select ID from dbo.CONSTITUENT where DATEADDED > @ASOF;
end
else
begin
insert into #TMP_CONSTITUENTFILTER_REVENUESPLITAMOUNTS(ID)
select CONSTITUENTID from dbo.FINANCIALTRANSACTION
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = FINANCIALTRANSACTION.CONSTITUENTID
where FINANCIALTRANSACTION.DATECHANGED > @ASOF and CONSTITUENTID is not null and FINANCIALTRANSACTION.DELETEDON is null
union
select CONSTITUENTSITE.CONSTITUENTID
from dbo.CONSTITUENTSITE as CONSTITUENTSITE
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = CONSTITUENTSITE.CONSTITUENTID
where CONSTITUENTSITE.DATECHANGED > @ASOF and CONSTITUENTID is not null
union
select REVENUEAUDIT.CONSTITUENTID
from dbo.FINANCIALTRANSACTIONAUDIT as REVENUEAUDIT
inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUEAUDIT.CONSTITUENTID
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = REVENUEAUDIT.CONSTITUENTID
where REVENUEAUDIT.AUDITDATE > @ASOF and REVENUEAUDIT.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
union
select FINANCIALTRANSACTION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = FINANCIALTRANSACTION.CONSTITUENTID
where FINANCIALTRANSACTIONLINEITEM.DATECHANGED > @ASOF and FINANCIALTRANSACTION.CONSTITUENTID is not null
and FINANCIALTRANSACTION.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
union
select CONSTITUENT.ID from dbo.CONSTITUENT
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = CONSTITUENT.ID
where DATEADDED > @ASOF;
end
end
else if @CALCULATEDREVENUE = 1 --Revenue Recognition
begin
if @SITES is null
begin
insert into #TMP_CONSTITUENTFILTER_REVENUESPLITAMOUNTS(ID)
select CONSTITUENTID from dbo.REVENUERECOGNITION where REVENUERECOGNITION.DATECHANGED > @ASOF and CONSTITUENTID is not null
union
select CONSTITUENTID from dbo.REVENUERECOGNITIONAUDIT where REVENUERECOGNITIONAUDIT.AUDITDATE > @ASOF and CONSTITUENTID is not null
union
select CONSTITUENTID from dbo.FINANCIALTRANSACTION where FINANCIALTRANSACTION.DATECHANGED > @ASOF and CONSTITUENTID is not null and FINANCIALTRANSACTION.DELETEDON is null
union
select REVENUEAUDIT.CONSTITUENTID
from dbo.FINANCIALTRANSACTIONAUDIT as REVENUEAUDIT
inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUEAUDIT.CONSTITUENTID
where REVENUEAUDIT.AUDITDATE > @ASOF and REVENUEAUDIT.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
union
select FINANCIALTRANSACTION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
where FINANCIALTRANSACTIONLINEITEM.DATECHANGED > @ASOF and FINANCIALTRANSACTION.CONSTITUENTID is not null
and FINANCIALTRANSACTION.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
union
select REVENUERECOGNITION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join dbo.REVENUERECOGNITION on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
where FINANCIALTRANSACTION.DATECHANGED > @ASOF and REVENUERECOGNITION.CONSTITUENTID is not null
and FINANCIALTRANSACTION.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
union
select REVENUERECOGNITION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join dbo.REVENUERECOGNITION on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
where FINANCIALTRANSACTIONLINEITEM.DATECHANGED > @ASOF and REVENUERECOGNITION.CONSTITUENTID is not null
and FINANCIALTRANSACTION.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
union
select ID from dbo.CONSTITUENT where DATEADDED > @ASOF;
end
else
begin
insert into #TMP_CONSTITUENTFILTER_REVENUESPLITAMOUNTS(ID)
select CONSTITUENTID from dbo.REVENUERECOGNITION
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = REVENUERECOGNITION.CONSTITUENTID
where REVENUERECOGNITION.DATECHANGED > @ASOF and CONSTITUENTID is not null
union
select CONSTITUENTID from dbo.REVENUERECOGNITIONAUDIT
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = REVENUERECOGNITIONAUDIT.CONSTITUENTID
where REVENUERECOGNITIONAUDIT.AUDITDATE > @ASOF and CONSTITUENTID is not null
union
select CONSTITUENTID from dbo.FINANCIALTRANSACTION
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = FINANCIALTRANSACTION.CONSTITUENTID
where FINANCIALTRANSACTION.DATECHANGED > @ASOF and CONSTITUENTID is not null
and FINANCIALTRANSACTION.DELETEDON is null
union
select CONSTITUENTSITE.CONSTITUENTID
from dbo.CONSTITUENTSITE as CONSTITUENTSITE
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = CONSTITUENTSITE.CONSTITUENTID
where CONSTITUENTSITE.DATECHANGED > @ASOF and CONSTITUENTID is not null
union
select REVENUEAUDIT.CONSTITUENTID
from dbo.FINANCIALTRANSACTIONAUDIT as REVENUEAUDIT
inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUEAUDIT.CONSTITUENTID
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = REVENUEAUDIT.CONSTITUENTID
where REVENUEAUDIT.AUDITDATE > @ASOF and REVENUEAUDIT.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
union
select FINANCIALTRANSACTION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = FINANCIALTRANSACTION.CONSTITUENTID
where FINANCIALTRANSACTIONLINEITEM.DATECHANGED > @ASOF and FINANCIALTRANSACTION.CONSTITUENTID is not null
and FINANCIALTRANSACTION.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
union
select REVENUERECOGNITION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join dbo.REVENUERECOGNITION on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = FINANCIALTRANSACTION.CONSTITUENTID
where FINANCIALTRANSACTION.DATECHANGED > @ASOF and REVENUERECOGNITION.CONSTITUENTID is not null
and FINANCIALTRANSACTION.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
union
select REVENUERECOGNITION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join dbo.REVENUERECOGNITION on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = FINANCIALTRANSACTION.CONSTITUENTID
where FINANCIALTRANSACTIONLINEITEM.DATECHANGED > @ASOF and REVENUERECOGNITION.CONSTITUENTID is not null
and FINANCIALTRANSACTION.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
union
select CONSTITUENT.ID from dbo.CONSTITUENT
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = CONSTITUENT.ID
where DATEADDED > @ASOF;
end
end
drop table #CONSTITUENTSITEFILTER;
/*
Retrieve revenue application data based on filter criteria. Only revenue data for constituents modified since the @ASOF value will be returned by this SP.
*/
declare @revenuesql nvarchar(max) = ''
declare @wheresql nvarchar(max) = ''
declare @tempsql nvarchar(max) = ''
set @revenuesql = 'declare @REVENUETYPESFILTER table
(
REVENUECODE tinyint primary key
);
declare @APPLICATIONTRANSACTIONTYPECODESFILTER table
(
APPLICATIONCODE tinyint,
TRANSACTIONTYPECODE tinyint
);
declare @RECOGNITIONTYPECODESFILTER table (
RECOGNITIONTYPECODEID uniqueidentifier primary key
);
declare @REVENUETYPECODES xml = ' + coalesce('''' + cast(@REVENUETYPECODES as nvarchar(max)) + '''', 'null') + '
if @REVENUETYPECODES is not null
begin
insert into @REVENUETYPESFILTER(REVENUECODE)
select distinct T.c.value(''(REVENUETYPES)[1]'',''tinyint'')
from @REVENUETYPECODES.nodes(''/REVENUETYPECODES/ITEM'') T(c);
if not exists (select 1 from @REVENUETYPESFILTER)
set @REVENUETYPECODES = null;
end
declare @APPLICATIONTRANSACTIONTYPECODES xml = ' + coalesce('''' + cast(@APPLICATIONTRANSACTIONTYPECODES as nvarchar(max)) + '''', 'null') + '
if @APPLICATIONTRANSACTIONTYPECODES is not null
begin
insert into @APPLICATIONTRANSACTIONTYPECODESFILTER(APPLICATIONCODE, TRANSACTIONTYPECODE)
select
T.c.value(''(APPLICATION)[1]'',''tinyint''),
T.c.value(''(TRANSACTIONTYPE)[1]'',''tinyint'')
from @APPLICATIONTRANSACTIONTYPECODES.nodes(''/SELECTIONS/ITEM'') T(c)
where T.c.value(''(TRANSACTIONTYPE)[1]'',''tinyint'') is not null;
if not exists (select 1 from @APPLICATIONTRANSACTIONTYPECODESFILTER)
set @APPLICATIONTRANSACTIONTYPECODES = null;
end
declare @RECOGNITIONTYPES xml = ' + coalesce('''' + cast(@RECOGNITIONTYPES as nvarchar(max)) + '''', 'null') + '
if @RECOGNITIONTYPES is not null
begin
insert into @RECOGNITIONTYPECODESFILTER(RECOGNITIONTYPECODEID)
select T.c.value(''(RECOGNITIONTYPECODEID)[1]'',''uniqueidentifier'')
from @RECOGNITIONTYPES.nodes(''/RECOGNITIONTYPES/ITEM'') T(c);
if not exists (select 1 from @RECOGNITIONTYPECODESFILTER)
set @RECOGNITIONTYPES = null;
end
declare @REVENUEAPPLICATIONSELECTION uniqueidentifier = ' + coalesce('''' + cast(@REVENUEAPPLICATIONSELECTION as nvarchar(36)) + '''', 'null') + ' '
declare @REVENUETABLEFUNCTIONALIAS nvarchar(100);
declare @REVENUETABLETYPECODENAME nvarchar(100);
declare @REVENUESPLITIDNAME nvarchar(100);
-- We need revenuesql's declared parameters except the SELECT clause
set @tempsql = @revenuesql + ';';
if @CALCULATEDREVENUE = 0 --REVENUE
begin
set @revenuesql = @revenuesql + 'select
[REVENUESPLITAMOUNTINCURRENCY].[ID],
[REVENUESPLITAMOUNTINCURRENCY].[CONSTITUENTID],
case when [REVENUESPLITAMOUNTINCURRENCY].[TRANSACTIONTYPECODE] in(1,3) then
[REVENUESPLITAMOUNTINCURRENCY].[AMOUNTINCURRENCY] - coalesce((
select
sum([WRITEOFFSPLITAMOUNTINCURRENCY].[AMOUNTINCURRENCY]) as [AMOUNT]
from dbo.[UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY_BULK](@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as [WRITEOFFSPLITAMOUNTINCURRENCY]
where [WRITEOFFSPLITAMOUNTINCURRENCY].[REVENUEID] = [REVENUESPLITAMOUNTINCURRENCY].[REVENUEID] and [WRITEOFFSPLITAMOUNTINCURRENCY].[DESIGNATIONID] = [REVENUESPLITAMOUNTINCURRENCY].[DESIGNATIONID]
) ,0)
else
[REVENUESPLITAMOUNTINCURRENCY].[AMOUNTINCURRENCY] end as [AMOUNT],
[REVENUESPLITAMOUNTINCURRENCY].[DATE],
[REVENUESPLITAMOUNTINCURRENCY].[REVENUEDATEADDED]
from dbo.[UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK](@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as [REVENUESPLITAMOUNTINCURRENCY]'
-- If the constituent filter is all constituents, then simply filtering out null is faster than joining
if @CONSTITUENTFILTERVIEW != 'dbo.CONSTITUENT'
begin
set @revenuesql = @revenuesql + ' inner join ' + @CONSTITUENTFILTERVIEW + ' as [CONSTITUENTFILTER] on [CONSTITUENTFILTER].[ID] = [REVENUESPLITAMOUNTINCURRENCY].[CONSTITUENTID]'
end
else
begin
if LEN(@wheresql) > 0 set @wheresql = @wheresql + ' and '
set @wheresql = @wheresql + ' ([REVENUESPLITAMOUNTINCURRENCY].[CONSTITUENTID] is not null)'
end
if @COMPUTEDSTARTDATE is not null
begin
if LEN(@wheresql) > 0 set @wheresql = @wheresql + ' and '
set @wheresql = @wheresql + ' ([REVENUESPLITAMOUNTINCURRENCY].[DATE] >= @COMPUTEDSTARTDATE)'
end
if @COMPUTEDENDDATE is not null
begin
if LEN(@wheresql) > 0 set @wheresql = @wheresql + ' and '
set @wheresql = @wheresql + ' ([REVENUESPLITAMOUNTINCURRENCY].[DATE] <= @COMPUTEDENDDATE)'
end
set @REVENUETABLEFUNCTIONALIAS = 'REVENUESPLITAMOUNTINCURRENCY';
set @REVENUETABLETYPECODENAME = 'TYPECODE';
set @REVENUESPLITIDNAME = 'ID';
end
else if @CALCULATEDREVENUE = 1 --Revenue Recognition
begin
set @revenuesql = @revenuesql + 'select
[REVENUERECOGNITIONAMOUNTINCURRENCY].[REVENUESPLITID],
[REVENUERECOGNITIONAMOUNTINCURRENCY].[CONSTITUENTID],
[REVENUERECOGNITIONAMOUNTINCURRENCY].[AMOUNTINCURRENCY],
[REVENUERECOGNITIONAMOUNTINCURRENCY].[EFFECTIVEDATE],
[REVENUERECOGNITIONAMOUNTINCURRENCY].[REVENUEDATEADDED],
[REVENUERECOGNITIONAMOUNTINCURRENCY].[REVENUERECOGNITIONTYPECODEID],
[REVENUERECOGNITIONAMOUNTINCURRENCY].[REVENUESPLITTYPECODE],
[REVENUERECOGNITIONAMOUNTINCURRENCY].[TRANSACTIONTYPECODE],
[REVENUERECOGNITIONAMOUNTINCURRENCY].[APPLICATIONCODE]
from dbo.[UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK](@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as [REVENUERECOGNITIONAMOUNTINCURRENCY]'
-- If the constituent filter is all constituents, then simply filtering out null is faster than joining
if @CONSTITUENTFILTERVIEW != 'dbo.CONSTITUENT'
begin
set @revenuesql = @revenuesql + ' inner join ' + @CONSTITUENTFILTERVIEW + ' as [CONSTITUENTFILTER] on [CONSTITUENTFILTER].[ID] = [REVENUERECOGNITIONAMOUNTINCURRENCY].[CONSTITUENTID]'
end
else
begin
if LEN(@wheresql) > 0 set @wheresql = @wheresql + ' and '
set @wheresql = @wheresql + ' ([REVENUERECOGNITIONAMOUNTINCURRENCY].[CONSTITUENTID] is not null)'
end
if @COMPUTEDSTARTDATE is not null
begin
if LEN(@wheresql) > 0 set @wheresql = @wheresql + ' and '
set @wheresql = @wheresql + ' ([REVENUERECOGNITIONAMOUNTINCURRENCY].[DATE] >= @COMPUTEDSTARTDATE)'
end
if @COMPUTEDENDDATE is not null
begin
if LEN(@wheresql) > 0 set @wheresql = @wheresql + ' and '
set @wheresql = @wheresql + ' ([REVENUERECOGNITIONAMOUNTINCURRENCY].[DATE] <= @COMPUTEDENDDATE)'
end
if @RECOGNITIONTYPES is not null
begin
if LEN(@wheresql) > 0 set @wheresql = @wheresql + ' and '
set @wheresql = @wheresql + ' [REVENUERECOGNITIONAMOUNTINCURRENCY].[REVENUERECOGNITIONTYPECODEID] in (select [FILTER].[RECOGNITIONTYPECODEID] from @RECOGNITIONTYPECODESFILTER as [FILTER]) '
end
set @REVENUETABLEFUNCTIONALIAS = 'REVENUERECOGNITIONAMOUNTINCURRENCY';
set @REVENUETABLETYPECODENAME = 'REVENUESPLITTYPECODE';
set @REVENUESPLITIDNAME = 'REVENUESPLITID';
end
if @REVENUETYPECODES is not null
begin
if LEN(@wheresql) > 0 set @wheresql = @wheresql + ' and '
set @wheresql = @wheresql + ' ([' + @REVENUETABLEFUNCTIONALIAS + '].[' + @REVENUETABLETYPECODENAME + '] in (select [SPLITTYPECODESFILTER].[REVENUECODE] from @REVENUETYPESFILTER as [SPLITTYPECODESFILTER])) '
end
if @APPLICATIONTRANSACTIONTYPECODES is not null
begin
if LEN(@wheresql) > 0 set @wheresql = @wheresql + ' and '
set @wheresql = @wheresql + ' exists (select 1 from @APPLICATIONTRANSACTIONTYPECODESFILTER as [FILTER]
where [FILTER].[TRANSACTIONTYPECODE] = [' + @REVENUETABLEFUNCTIONALIAS + '].[TRANSACTIONTYPECODE]
and [FILTER].[APPLICATIONCODE] = [' + @REVENUETABLEFUNCTIONALIAS + '].[APPLICATIONCODE])'
end
if @REVENUEAPPLICATIONSELECTION is not null or @REVENUEAPPLICATIONSELECTION <> '00000000-0000-0000-0000-000000000000'
begin
declare @ISSTATIC bit = 0
declare @DBOBJECTTYPE tinyint = 0
declare @DBOBJECTNAME nvarchar(128)
declare @ADHOCQUERYID uniqueidentifier
select @DBOBJECTNAME = IDSETREGISTER.DBOBJECTNAME, @DBOBJECTTYPE = IDSETREGISTER.OBJECTTYPE, @ISSTATIC = IDSETREGISTER.STATIC, @ADHOCQUERYID=IDSETREGISTERADHOCQUERY.ADHOCQUERYID from dbo.IDSETREGISTER
left join dbo.IDSETREGISTERADHOCQUERY on IDSETREGISTER.ID = IDSETREGISTERADHOCQUERY.IDSETREGISTERID
where IDSETREGISTER.ID = @REVENUEAPPLICATIONSELECTION
if @DBOBJECTTYPE > 0
begin
if @DBOBJECTTYPE = 1 and @ADHOCQUERYID is not null
begin
if @ISSTATIC = 1
select @DBOBJECTNAME = dbo.UFN_ADHOCQUERY_MAKEIDSETSTATICTABLENAME(@ADHOCQUERYID)
else
select @DBOBJECTNAME = dbo.UFN_ADHOCQUERY_MAKEIDSETFUNCTIONNAME(@ADHOCQUERYID) + '()'
end
else
set @DBOBJECTNAME = 'UFN_IDSETREADER_GETRESULTS_GUID(@REVENUEAPPLICATIONSELECTION)'
end
end
if LEN(@DBOBJECTNAME) > 0
set @revenuesql = @revenuesql + '
inner join dbo.' + @DBOBJECTNAME + ' as [REVENUESPLITFILTER] on [' + @REVENUETABLEFUNCTIONALIAS + '].[' + @REVENUESPLITIDNAME + '] = [REVENUESPLITFILTER].[ID]';
if LEN(@wheresql) > 0 set @wheresql = ' where ' + @wheresql
if @CALCULATEDREVENUE = 1 --Revenue Recognition
begin
set @revenuesql = @revenuesql + ' option (recompile)'
--New temp table to store all records without filters
if object_id('tempdb..#TMP_REVENUERECORDS_REVENUESPLITAMOUNTS_ALLDATA') is not null
drop table #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS_ALLDATA ;
create table #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS_ALLDATA
(
ID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
AMOUNT money,
DATE datetime,
DATEADDED datetime,
REVENUERECOGNITIONTYPECODEID uniqueidentifier,
REVENUESPLITTYPECODE tinyint,
TRANSACTIONTYPECODE tinyint,
APPLICATIONCODE tinyint
);
--Records without filters applied
insert into #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS_ALLDATA(ID, CONSTITUENTID, AMOUNT, DATE, DATEADDED,REVENUERECOGNITIONTYPECODEID,REVENUESPLITTYPECODE,TRANSACTIONTYPECODE,APPLICATIONCODE)
exec sp_executesql @revenuesql, N'@CURRENCYID uniqueidentifier, @ORGANIZATIONCURRENCYID uniqueidentifier, @DECIMALDIGITS tinyint, @ROUNDINGTYPECODE tinyint, @COMPUTEDSTARTDATE datetime, @COMPUTEDENDDATE datetime',
@CURRENCYID = @CURRENCYID,
@ORGANIZATIONCURRENCYID = @ORGANIZATIONCURRENCYID,
@DECIMALDIGITS = @DECIMALDIGITS,
@ROUNDINGTYPECODE = @ROUNDINGTYPECODE,
@COMPUTEDSTARTDATE = @COMPUTEDSTARTDATE,
@COMPUTEDENDDATE = @COMPUTEDENDDATE;
--Query to filter the records based on the @wheresql clause
set @tempsql = @tempsql + ' select ID, CONSTITUENTID, AMOUNT, DATE, DATEADDED
from #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS_ALLDATA REVENUERECOGNITIONAMOUNTINCURRENCY ' + @wheresql + ';'
--Records after filters applied
insert into #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS(ID, CONSTITUENTID, AMOUNT, DATE, DATEADDED)
exec sp_executesql @tempsql,N'@CURRENCYID uniqueidentifier, @ORGANIZATIONCURRENCYID uniqueidentifier, @DECIMALDIGITS tinyint, @ROUNDINGTYPECODE tinyint, @COMPUTEDSTARTDATE datetime, @COMPUTEDENDDATE datetime',
@CURRENCYID = @CURRENCYID,
@ORGANIZATIONCURRENCYID = @ORGANIZATIONCURRENCYID,
@DECIMALDIGITS = @DECIMALDIGITS,
@ROUNDINGTYPECODE = @ROUNDINGTYPECODE,
@COMPUTEDSTARTDATE = @COMPUTEDSTARTDATE,
@COMPUTEDENDDATE = @COMPUTEDENDDATE;
drop table #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS_ALLDATA;
end
else
begin
set @revenuesql = @revenuesql + @wheresql + ' option (recompile)'
insert into #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS(ID, CONSTITUENTID, AMOUNT, DATE, DATEADDED)
exec sp_executesql @revenuesql, N'@CURRENCYID uniqueidentifier, @ORGANIZATIONCURRENCYID uniqueidentifier, @DECIMALDIGITS tinyint, @ROUNDINGTYPECODE tinyint, @COMPUTEDSTARTDATE datetime, @COMPUTEDENDDATE datetime',
@CURRENCYID = @CURRENCYID,
@ORGANIZATIONCURRENCYID = @ORGANIZATIONCURRENCYID,
@DECIMALDIGITS = @DECIMALDIGITS,
@ROUNDINGTYPECODE = @ROUNDINGTYPECODE,
@COMPUTEDSTARTDATE = @COMPUTEDSTARTDATE,
@COMPUTEDENDDATE = @COMPUTEDENDDATE;
end
create index IX_TMP_REVENUERECORDS_REVENUESPLITAMOUNTS_CONSTITUENTID_DATE_DATEADDED on #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS (CONSTITUENTID,DATE,DATEADDED)
create index IX_TMP_REVENUERECORDS_REVENUESPLITAMOUNTS_CONSTITUENTID_ID on #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS (CONSTITUENTID,ID) include(AMOUNT)
create index IX_TMP_REVENUERECORDS_REVENUESPLITAMOUNTS_ID on #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS (ID)
--Removing records based on site security
if @SITES is not null
begin
if @CALCULATEDREVENUE = 0
begin
delete from #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS
where ID not in
(
select distinct
TMP.ID
from
#TMP_REVENUERECORDS_REVENUESPLITAMOUNTS TMP
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = TMP.ID
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(TMP.ID) as SITES
inner join @SITESFILTER as SITESFILTER on SITESFILTER.ID = SITES.SITEID
where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
)
end
if @CALCULATEDREVENUE = 1
begin
delete from #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS
where ID not in
(
select distinct
TMP.ID
from
REVENUERECOGNITION
inner join #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS TMP on TMP.ID = REVENUERECOGNITION.REVENUESPLITID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = TMP.ID
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(TMP.ID) as SITES
inner join @SITESFILTER as SITESFILTER on SITESFILTER.ID = SITES.SITEID
where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
)
end
end
/*
Calculate totals based on calculation type.
*/
-- Backwards compatibility
if @SMARTFIELDID is not null
set @revenuesql = 'with SF_CTE (ID, VALUE) as ('
else
set @revenuesql = ''
if @CALCULATIONTYPE = 0 --Max(amount)
set @revenuesql = @revenuesql + char(10) + '
select
CONSTITUENTFILTER.ID,
coalesce(max(REVENUERECORDS.AMOUNT),0)
from ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join (select REVENUERECORDS.ID, REVENUERECORDS.CONSTITUENTID, sum(REVENUERECORDS.AMOUNT) AMOUNT from #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as REVENUERECORDS group by REVENUERECORDS.CONSTITUENTID, REVENUERECORDS.ID) REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by CONSTITUENTFILTER.ID'
else if @CALCULATIONTYPE = 1 --Min(amount)
set @revenuesql = @revenuesql + char(10) + '
select
CONSTITUENTFILTER.ID,
coalesce(min(REVENUERECORDS.AMOUNT),0)
from ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join (select REVENUERECORDS.ID, REVENUERECORDS.CONSTITUENTID, sum(REVENUERECORDS.AMOUNT) AMOUNT from #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as REVENUERECORDS group by REVENUERECORDS.CONSTITUENTID, REVENUERECORDS.ID) REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by CONSTITUENTFILTER.ID'
else if @CALCULATIONTYPE = 2 --Sum(amount)
set @revenuesql = @revenuesql + char(10) + '
select
CONSTITUENTFILTER.ID,
coalesce(sum(REVENUERECORDS.AMOUNT),0)
from ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by CONSTITUENTFILTER.ID'
else if @CALCULATIONTYPE = 3 --Avg(amount)
set @revenuesql = @revenuesql + char(10) + '
select
CONSTITUENTFILTER.ID,
coalesce(avg(REVENUERECORDS.AMOUNT),0)
from ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join (select REVENUERECORDS.ID, REVENUERECORDS.CONSTITUENTID, sum(REVENUERECORDS.AMOUNT) AMOUNT from #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as REVENUERECORDS group by REVENUERECORDS.CONSTITUENTID, REVENUERECORDS.ID) REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by CONSTITUENTFILTER.ID'
else if @CALCULATIONTYPE = 4 --First revenue application amount
set @revenuesql = @revenuesql + char(10) + '
select
CONSTITUENTFILTER.ID,
coalesce(sum(REVENUERECORDS.AMOUNT), 0)
from ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
where
((REVENUERECORDS.ID is null)
or (REVENUERECORDS.ID = (select top 1 ID from #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as SUBR where SUBR.CONSTITUENTID = CONSTITUENTFILTER.id order by SUBR.DATE asc, SUBR.DATEADDED asc)))
group by CONSTITUENTFILTER.ID'
else if @CALCULATIONTYPE = 5 --Latest revenue application amount
set @revenuesql = @revenuesql + char(10) + '
select
CONSTITUENTFILTER.ID,
coalesce(sum(REVENUERECORDS.AMOUNT), 0)
from ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
where
((REVENUERECORDS.ID is null)
or (REVENUERECORDS.ID = (select top 1 ID from #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as SUBR where SUBR.CONSTITUENTID = CONSTITUENTFILTER.id order by SUBR.DATE desc, SUBR.DATEADDED desc)))
group by CONSTITUENTFILTER.ID'
else if @CALCULATIONTYPE = 6 --Min(date)
set @revenuesql = @revenuesql + char(10) + '
select
CONSTITUENTFILTER.ID,
min(REVENUERECORDS.DATE)
from ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by CONSTITUENTFILTER.ID'
else if @CALCULATIONTYPE = 7 --Max(date)
set @revenuesql = @revenuesql + char(10) + '
select
CONSTITUENTFILTER.ID,
max(REVENUERECORDS.DATE)
from ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by CONSTITUENTFILTER.ID'
else if @CALCULATIONTYPE = 8 --Giving years
set @revenuesql = @revenuesql + char(10) + '
select
CONSTITUENTFILTER.ID,
coalesce(datediff(year,min(REVENUERECORDS.DATE), max(REVENUERECORDS.DATE)), -1) + 1
from ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by CONSTITUENTFILTER.ID, REVENUERECORDS.CONSTITUENTID'
else if @CALCULATIONTYPE = 9 --Distinct giving years
set @revenuesql = @revenuesql + char(10) + '
select
CONSTITUENTFILTER.ID,
count(distinct REVENUEDATES.YEAR)
from ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join (select distinct REVENUERECORDS.CONSTITUENTID, datepart(year, REVENUERECORDS.DATE) as YEAR from #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as REVENUERECORDS) as REVENUEDATES on REVENUEDATES.CONSTITUENTID = CONSTITUENTFILTER.ID
group by CONSTITUENTFILTER.ID'
else if @CALCULATIONTYPE = 10 --Count(revenue application)
set @revenuesql = @revenuesql + char(10) + '
select
CONSTITUENTFILTER.ID,
count(REVENUERECORDS.ID)
from ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by CONSTITUENTFILTER.ID'
else if @CALCULATIONTYPE = 11 --Consecutive giving years
begin
declare @totalfiscalyears int;
select
@totalfiscalyears = count(1)
from
(
select GLFISCALYEARID from dbo.GLFISCALPERIOD
where STARTDATE between @COMPUTEDSTARTDATE and @COMPUTEDENDDATE
and ENDDATE between @COMPUTEDSTARTDATE and @COMPUTEDENDDATE
group by GLFISCALYEARID
)
GETFISCALYEARS;
set @revenuesql =
'with MAXREVENUEDATE([CONSITITUENTID], [DATE]) as
(
select [REVENUERECORDS].[CONSTITUENTID], max([REVENUERECORDS].[DATE])
from #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as REVENUERECORDS
group by [REVENUERECORDS].[CONSTITUENTID]
having max([REVENUERECORDS].[DATE]) > getdate()
),
[BANDING]([CONSTITUENTID], [BAND]) as
(
select
CONSTITUENTFILTER.ID,
-1
from ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
union all
select
REVENUERECORDS.CONSTITUENTID,
datediff(year, [REVENUERECORDS].[DATE], coalesce([MAXREVENUEDATE].[DATE], @COMPUTEDENDDATE, getdate()))
from #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as REVENUERECORDS
left join [MAXREVENUEDATE] on [MAXREVENUEDATE].[CONSITITUENTID] = [REVENUERECORDS].[CONSTITUENTID]
),
[GAPS]([CONSTITUENTID],[BAND]) as
(
select distinct
[B1].[CONSTITUENTID],
case when '+ cast(@totalfiscalyears as varchar) +'= '''' then [B1].[BAND] + 1
when '+ cast(@totalfiscalyears as varchar) +'<= [B1].[BAND] then [B1].[BAND]
else [B1].[BAND] + 1 end
from [BANDING] [B1]
where not exists (select 1 from [BANDING] [B2] where [B2].[CONSTITUENTID] = [B1].[CONSTITUENTID] and [B2].[BAND] = [B1].[BAND] + 1)
)'
if @SMARTFIELDID is not null
set @revenuesql = @revenuesql + ', SF_CTE (ID, VALUE) as ('
set @revenuesql = @revenuesql + char(10) +
'select
[CONSTITUENTID],
case when min([BAND]) <0 then 0 else min([BAND]) end
from [GAPS]
group by [CONSTITUENTID]'
end
else if @CALCULATIONTYPE = 12 --First date of largest application amount
set @revenuesql = @revenuesql + char(10) + '
select
CONSTITUENTFILTER.ID,
REVENUERECORDS.DATE
from ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
outer apply (select top 1 DATE from #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as SUBR where SUBR.CONSTITUENTID = CONSTITUENTFILTER.id order by SUBR.AMOUNT desc, SUBR.DATE) REVENUERECORDS'
else if @CALCULATIONTYPE = 13 --Is annual giver
set @revenuesql = @revenuesql + char(10) + '
select
CONSTITUENTFILTER.ID,
case
when (coalesce(datediff(year,min(REVENUERECORDS.DATE), max(REVENUERECORDS.DATE)), 0) = (REVENUEDATES.YEARS - 1)) and (REVENUEDATES.YEARS > 1)
then cast(1 as bit)
else
cast(0 as bit)
end
from ' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join (select REVENUERECORDS.CONSTITUENTID, count(distinct datepart(year, REVENUERECORDS.DATE)) as YEARS from #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as REVENUERECORDS group by REVENUERECORDS.CONSTITUENTID) as REVENUEDATES on REVENUEDATES.CONSTITUENTID = CONSTITUENTFILTER.ID
left outer join #TMP_REVENUERECORDS_REVENUESPLITAMOUNTS as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
group by CONSTITUENTFILTER.ID, REVENUEDATES.YEARS'
-- Backwards compatibility
if @SMARTFIELDID is null
begin
insert into #TMP_CONSTITUENTSMARTFIELD_HH_REVENUESPLITANDRECOGNITIONS
exec sp_executesql @revenuesql, N'@COMPUTEDENDDATE datetime', @COMPUTEDENDDATE = @COMPUTEDENDDATE;
end
else
begin
declare @TARGET nvarchar(100)
declare @TARGET_FIELD nvarchar(128)
select
@TARGET = TABLECATALOG.TABLENAME,
@TARGET_FIELD = SMARTFIELD.VALUECOLUMNNAME
from dbo.SMARTFIELD
left join dbo.TABLECATALOG on SMARTFIELD.TABLECATALOGID = TABLECATALOG.ID
where SMARTFIELD.ID = @SMARTFIELDID
set @revenuesql = @revenuesql + ')
merge dbo.' + @TARGET + ' as target
using SF_CTE as source
on target.ID = source.ID
when matched then
update set ' + @TARGET_FIELD + ' = source.VALUE
when not matched by target then
insert (ID, ' + @TARGET_FIELD + ')
values (source.ID, source.VALUE);';
exec sp_executesql @revenuesql, N'@COMPUTEDENDDATE datetime', @COMPUTEDENDDATE = @COMPUTEDENDDATE;
select @@ROWCOUNT;
end