USP_CONSTITUENTIDSMARTFIELD_REVENUESPLIT
Returns revenue ID to be used in various constituent revenue application smart records.
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_CONSTITUENTIDSMARTFIELD_REVENUESPLIT
(
@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;
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
/* 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.
*/
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 as REVENUE
where REVENUE.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
where REVENUEAUDIT.AUDITDATE > @ASOF and REVENUEAUDIT.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
union
select REVENUE.CONSTITUENTID
from dbo.FINANCIALTRANSACTION as REVENUE
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
where REVENUESPLIT.DATECHANGED > @ASOF and REVENUE.CONSTITUENTID is not null
union
select ID from dbo.CONSTITUENT where DATEADDED > @ASOF;
end
else
begin
insert into #TMP_CONSTITUENTFILTER_REVENUESPLITAMOUNTS(ID)
select CONSTITUENTID from dbo.FINANCIALTRANSACTION as REVENUE
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = REVENUE.CONSTITUENTID
where REVENUE.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 REVENUE.CONSTITUENTID
from dbo.FINANCIALTRANSACTION as REVENUE
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = REVENUE.CONSTITUENTID
where REVENUESPLIT.DATECHANGED > @ASOF and REVENUE.CONSTITUENTID is not 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 as REVENUE where REVENUE.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
where REVENUEAUDIT.AUDITDATE > @ASOF and REVENUEAUDIT.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
union
select REVENUE.CONSTITUENTID
from dbo.FINANCIALTRANSACTION as REVENUE
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
where REVENUESPLIT.DATECHANGED > @ASOF and REVENUE.CONSTITUENTID is not null
union
select REVENUERECOGNITION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION as REVENUE
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
inner join dbo.REVENUERECOGNITION on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
where REVENUE.DATECHANGED > @ASOF and REVENUERECOGNITION.CONSTITUENTID is not null
union
select REVENUERECOGNITION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION as REVENUE
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
inner join dbo.REVENUERECOGNITION on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
where REVENUESPLIT.DATECHANGED > @ASOF and REVENUERECOGNITION.CONSTITUENTID is not 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 as REVENUE
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = REVENUE.CONSTITUENTID
where REVENUE.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 REVENUE.CONSTITUENTID
from dbo.FINANCIALTRANSACTION as REVENUE
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = REVENUE.CONSTITUENTID
where REVENUESPLIT.DATECHANGED > @ASOF and REVENUE.CONSTITUENTID is not null
union
select REVENUERECOGNITION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION as REVENUE
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
inner join dbo.REVENUERECOGNITION on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = REVENUE.CONSTITUENTID
where REVENUE.DATECHANGED > @ASOF and REVENUERECOGNITION.CONSTITUENTID is not null
union
select REVENUERECOGNITION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION as REVENUE
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
inner join dbo.REVENUERECOGNITION on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
inner join #CONSTITUENTSITEFILTER as CONSTITUENTSITEFILTER on CONSTITUENTSITEFILTER.ID = REVENUE.CONSTITUENTID
where REVENUESPLIT.DATECHANGED > @ASOF and REVENUERECOGNITION.CONSTITUENTID is not 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) = ''
set @revenuesql = 'declare @REVENUETYPESFILTER table
(
REVENUECODE tinyint primary key
);
declare @APPLICATIONTRANSACTIONTYPECODESFILTER table
(
APPLICATIONCODE tinyint,
TRANSACTIONTYPECODE tinyint
);
declare @RECOGNITIONTYPECODESFILTER table (
RECOGNITIONTYPECODEID uniqueidentifier primary key
);
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''),
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
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;'
if object_id('tempdb..#TMP_REVENUESPLITSITEFILTER') is not null
drop table #TMP_REVENUESPLITSITEFILTER
create table #TMP_REVENUESPLITSITEFILTER(ID uniqueidentifier);
declare @REVENUETABLEFUNCTIONALIAS nvarchar(100);
declare @REVENUETABLETYPECODENAME nvarchar(100);
declare @REVENUETABLESPLITID nvarchar(100);
if @CALCULATEDREVENUE = 0 --REVENUE
begin
if @SITES is not null
begin
insert into #TMP_REVENUESPLITSITEFILTER
select distinct
REVENUESPLIT.ID
from
REVENUESPLIT
cross apply
dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) as SITES
inner join
@SITESFILTER as SITESFILTER on SITESFILTER.ID = SITES.SITEID
end
set @revenuesql = @revenuesql +
'with REVENUE_CTE as
(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]
) ,0)
else
[REVENUESPLITAMOUNTINCURRENCY].[AMOUNTINCURRENCY] end as AMOUNT,
[REVENUESPLITAMOUNTINCURRENCY].[DATE],
[REVENUESPLITAMOUNTINCURRENCY].[REVENUEDATEADDED] as DATEADDED,
[REVENUESPLITAMOUNTINCURRENCY].[TSLONG] '
-- Include a row number if needed for calculating an extreme.
if @CALCULATIONTYPE = 4
set @revenuesql = @revenuesql + char(10) + ', ROW_NUMBER() over (partition by [REVENUESPLITAMOUNTINCURRENCY].[CONSTITUENTID] order by [REVENUESPLITAMOUNTINCURRENCY].[DATE] asc, [REVENUESPLITAMOUNTINCURRENCY].[REVENUEDATEADDED] asc, [REVENUESPLITAMOUNTINCURRENCY].[TSLONG] asc) as ROW '
else if @CALCULATIONTYPE = 5
set @revenuesql = @revenuesql + char(10) + ', ROW_NUMBER() over (partition by [REVENUESPLITAMOUNTINCURRENCY].[CONSTITUENTID] order by [REVENUESPLITAMOUNTINCURRENCY].[DATE] desc, [REVENUESPLITAMOUNTINCURRENCY].[REVENUEDATEADDED] desc, [REVENUESPLITAMOUNTINCURRENCY].[TSLONG] desc) as ROW '
set @revenuesql = @revenuesql + '
from dbo.[UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK](@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as [REVENUESPLITAMOUNTINCURRENCY] '
if @SITES is not null
begin
set @revenuesql = @revenuesql + ' inner join #TMP_REVENUESPLITSITEFILTER as [REVENUESPLITSITEFILTER] on [REVENUESPLITSITEFILTER].[ID] = [REVENUESPLITAMOUNTINCURRENCY].[ID]'
end
-- 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 @REVENUETABLESPLITID = 'ID';
end
else if @CALCULATEDREVENUE = 1 --Revenue Recognition
begin
insert into #TMP_REVENUESPLITSITEFILTER
select distinct
REVENUERECOGNITION.ID
from
REVENUERECOGNITION
inner join
REVENUESPLIT on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
cross apply
dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) as SITES
inner join
@SITESFILTER as SITESFILTER on SITESFILTER.ID = SITES.SITEID
set @revenuesql = @revenuesql + '
with REVENUE_CTE as
(select
[REVENUERECOGNITIONAMOUNTINCURRENCY].[REVENUESPLITID] as ID,
[REVENUERECOGNITIONAMOUNTINCURRENCY].[CONSTITUENTID],
[REVENUERECOGNITIONAMOUNTINCURRENCY].[AMOUNTINCURRENCY] as AMOUNT,
[REVENUERECOGNITIONAMOUNTINCURRENCY].[EFFECTIVEDATE] as DATE,
[REVENUERECOGNITIONAMOUNTINCURRENCY].[DATEADDED],
[REVENUERECOGNITIONAMOUNTINCURRENCY].TSLONG '
-- Include a row number if needed for calculating an extreme.
if @CALCULATIONTYPE = 4
set @revenuesql = @revenuesql + char(10) + ', ROW_NUMBER() over (partition by [REVENUERECOGNITIONAMOUNTINCURRENCY].[CONSTITUENTID] order by [REVENUERECOGNITIONAMOUNTINCURRENCY].[EFFECTIVEDATE] asc, [REVENUERECOGNITIONAMOUNTINCURRENCY].[DATEADDED] asc, [REVENUERECOGNITIONAMOUNTINCURRENCY].[TSLONG] asc) as ROW '
else if @CALCULATIONTYPE = 5
set @revenuesql = @revenuesql + char(10) + ', ROW_NUMBER() over (partition by [REVENUERECOGNITIONAMOUNTINCURRENCY].[CONSTITUENTID] order by [REVENUERECOGNITIONAMOUNTINCURRENCY].[EFFECTIVEDATE] desc, [REVENUERECOGNITIONAMOUNTINCURRENCY].[DATEADDED] desc, [REVENUERECOGNITIONAMOUNTINCURRENCY].[TSLONG] desc) as ROW '
set @revenuesql = @revenuesql + '
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 @STARTDATE is not null
begin
if LEN(@wheresql) > 0
set @wheresql = @wheresql + ' and '
set @wheresql = @wheresql + ' ([REVENUERECOGNITIONAMOUNTINCURRENCY].[DATE] >= @STARTDATE)'
end
if @ENDDATE is not null
begin
if LEN(@wheresql) > 0
set @wheresql = @wheresql + ' and '
set @wheresql = @wheresql + ' ([REVENUERECOGNITIONAMOUNTINCURRENCY].[DATE] <= @ENDDATE)'
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 @REVENUETABLESPLITID = '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
set @revenuesql = @revenuesql + char(10) + '
left join @APPLICATIONTRANSACTIONTYPECODESFILTER [FILTER]
on [FILTER].TRANSACTIONTYPECODE = [' + @REVENUETABLEFUNCTIONALIAS + '].[TRANSACTIONTYPECODE]
and [FILTER].APPLICATIONCODE = [' + @REVENUETABLEFUNCTIONALIAS + '].[APPLICATIONCODE] '
if LEN(@wheresql) > 0
set @wheresql = @wheresql + ' and '
set @wheresql = @wheresql + ' [FILTER].APPLICATIONCODE is not null '
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 @ISSTATIC = 1 and @ADHOCQUERYID is not null
select @DBOBJECTNAME = dbo.UFN_ADHOCQUERY_MAKEIDSETSTATICTABLENAME(@ADHOCQUERYID)
else
set @DBOBJECTNAME = 'UFN_IDSETREADER_GETRESULTS_GUID(@REVENUEAPPLICATIONSELECTION)'
end
if LEN(@wheresql) > 0
set @wheresql = @wheresql + ' and '
set @wheresql = @wheresql + '([' + @REVENUETABLEFUNCTIONALIAS + '].[' + @REVENUETABLESPLITID + '] in (select ID from dbo.' + @DBOBJECTNAME + '))'
end
if LEN(@wheresql) > 0
set @wheresql = ' where ' + @wheresql
set @revenuesql = @revenuesql + @wheresql
-- Backwards compatibility
if @SMARTFIELDID is null
set @revenuesql = @revenuesql + ')'
else
set @revenuesql = @revenuesql + '), SF_CTE (ID, VALUE) as ('
/*
Calculate totals based on calculation type.
*/
if @CALCULATIONTYPE = 0 --Largest
set @revenuesql = @revenuesql + '
select
CONSTITUENTFILTER.ID,
REVENUERECORDS.ID as VALUE
from
' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join (
select
ID,
CONSTITUENTID,
row_number() over(partition by CONSTITUENTID order by [AMOUNT] desc, [DATE] desc, [DATEADDED] desc, [TSLONG] desc) [ROW]
from REVENUE_CTE
) as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
where
REVENUERECORDS.CONSTITUENTID is null or REVENUERECORDS.ROW = 1';
else if @CALCULATIONTYPE = 1 --Smallest
set @revenuesql = @revenuesql + '
select
CONSTITUENTFILTER.ID,
REVENUERECORDS.ID as VALUE
from
' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join (
select
ID,
CONSTITUENTID,
row_number() over(partition by CONSTITUENTID order by [AMOUNT] asc, [DATE] desc, [DATEADDED] desc, [TSLONG] desc) [ROW]
from REVENUE_CTE
) as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
where
REVENUERECORDS.CONSTITUENTID is null or REVENUERECORDS.ROW = 1';
else --First revenue application, Latest revenue application
set @revenuesql = @revenuesql + '
select
CONSTITUENTFILTER.ID,
REVENUERECORDS.ID as VALUE
from
' + @CONSTITUENTFILTERVIEW + ' as CONSTITUENTFILTER
left outer join REVENUE_CTE as REVENUERECORDS on CONSTITUENTFILTER.ID = REVENUERECORDS.CONSTITUENTID
where
REVENUERECORDS.CONSTITUENTID is null or REVENUERECORDS.ROW = 1';
-- Backwards compatibility
if @SMARTFIELDID is null
insert into #TMP_CONSTITUENTSMARTFIELD_HH_REVENUESPLITANDRECOGNITIONS
exec sp_executesql @revenuesql,
N'@STARTDATE datetime,
@ENDDATE datetime,
@COMPUTEDSTARTDATE datetime,
@COMPUTEDENDDATE datetime,
@CURRENCYID uniqueidentifier,
@ORGANIZATIONCURRENCYID uniqueidentifier,
@DECIMALDIGITS tinyint,
@ROUNDINGTYPECODE tinyint,
@REVENUEAPPLICATIONSELECTION uniqueidentifier,
@RECOGNITIONTYPES xml,
@REVENUETYPECODES xml,
@APPLICATIONTRANSACTIONTYPECODES xml',
@STARTDATE = @STARTDATE,
@ENDDATE = @ENDDATE,
@COMPUTEDSTARTDATE = @COMPUTEDSTARTDATE,
@COMPUTEDENDDATE = @COMPUTEDENDDATE,
@CURRENCYID = @CURRENCYID,
@ORGANIZATIONCURRENCYID = @ORGANIZATIONCURRENCYID,
@DECIMALDIGITS = @DECIMALDIGITS,
@ROUNDINGTYPECODE = @ROUNDINGTYPECODE,
@REVENUEAPPLICATIONSELECTION = @REVENUEAPPLICATIONSELECTION,
@RECOGNITIONTYPES = @RECOGNITIONTYPES,
@REVENUETYPECODES = @REVENUETYPECODES,
@APPLICATIONTRANSACTIONTYPECODES = @APPLICATIONTRANSACTIONTYPECODES;
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 + char(10) + ')
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'@STARTDATE datetime,
@ENDDATE datetime,
@COMPUTEDSTARTDATE datetime,
@COMPUTEDENDDATE datetime,
@CURRENCYID uniqueidentifier,
@ORGANIZATIONCURRENCYID uniqueidentifier,
@DECIMALDIGITS tinyint,
@ROUNDINGTYPECODE tinyint,
@REVENUEAPPLICATIONSELECTION uniqueidentifier,
@RECOGNITIONTYPES xml,
@REVENUETYPECODES xml,
@APPLICATIONTRANSACTIONTYPECODES xml',
@STARTDATE = @STARTDATE,
@ENDDATE = @ENDDATE,
@COMPUTEDSTARTDATE = @COMPUTEDSTARTDATE,
@COMPUTEDENDDATE = @COMPUTEDENDDATE,
@CURRENCYID = @CURRENCYID,
@ORGANIZATIONCURRENCYID = @ORGANIZATIONCURRENCYID,
@DECIMALDIGITS = @DECIMALDIGITS,
@ROUNDINGTYPECODE = @ROUNDINGTYPECODE,
@REVENUEAPPLICATIONSELECTION = @REVENUEAPPLICATIONSELECTION,
@RECOGNITIONTYPES = @RECOGNITIONTYPES,
@REVENUETYPECODES = @REVENUETYPECODES,
@APPLICATIONTRANSACTIONTYPECODES = @APPLICATIONTRANSACTIONTYPECODES;
select @@ROWCOUNT;
end