USP_DATALIST_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY
This datalist returns a filtered list of revenue associated with a constituent.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @GROUPBY | tinyint | IN | Group by |
| @TRANSACTIONTYPEOPTIONCODE | int | IN | Type |
| @REVENUETYPEOPTIONCODE | int | IN | Revenue type |
| @DATEFILTER | tinyint | IN | Date range |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @SITEFILTERMODE | tinyint | IN | Sites |
| @SITESSELECTED | xml | IN | |
| @SECURITYFEATUREID | uniqueidentifier | IN | Input parameter indicating the ID of the feature to use for site security checking. |
| @SECURITYFEATURETYPE | tinyint | IN | Input parameter indicating the type of the feature to use for site security checking. |
| @INCLUDEGROUPMEMBERREVENUE | bit | IN | |
| @RECOGNITIONFILTERID | uniqueidentifier | IN | Recognition filter |
| @CURRENCYCODE | tinyint | IN | Currency |
| @CAMPAIGNFILTERMODE | tinyint | IN | Campaigns |
| @CAMPAIGNSSELECTED | xml | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY
(
@CONSTITUENTID uniqueidentifier,
@GROUPBY tinyint = 0, -- 0=Commitment, 1=Transaction, null=None
@TRANSACTIONTYPEOPTIONCODE int = null, -- -1=All, otherwise revenue.transactiontypecode
@REVENUETYPEOPTIONCODE int = null, -- -1=All, otherwise revenuesplit.revenuetypecode
@DATEFILTER tinyint = 16,
@CURRENTAPPUSERID uniqueidentifier = null,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@INCLUDEGROUPMEMBERREVENUE bit = 0,
@RECOGNITIONFILTERID uniqueidentifier = '00000000-0000-0000-0000-000000000000',
@CURRENCYCODE tinyint = 0,
@CAMPAIGNFILTERMODE tinyint = 0,
@CAMPAIGNSSELECTED xml = null,
@STARTDATE datetime = null,
@ENDDATE datetime = null
)
with execute as OWNER
as
set nocount on;
begin
declare
@CURRENCYID uniqueidentifier,
@ORGANIZATIONCURRENCYID uniqueidentifier,
@CURRENCYROUNDINGTYPECODE tinyint,
@CURRENCYISOCURRENCYCODE nvarchar(3) = null,
@CURRENCYDECIMALDIGITS tinyint = 0,
@CURRENCYSYMBOL nvarchar(5) = null,
@CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint = 0,
@ISUK bit;
--The platform does not pass any parameters when resetting datalist filters but this filter has a dynamic default value.
--To get around this use empty guid to signify that the parameter was not passed and set it to the default.
if @RECOGNITIONFILTERID = '00000000-0000-0000-0000-000000000000'
begin
select
@RECOGNITIONFILTERID = ID
from
dbo.RECOGNITIONFILTER
where
ISINDIVIDUALDEFAULT = 1;
if @RECOGNITIONFILTERID = '00000000-0000-0000-0000-000000000000'
set @RECOGNITIONFILTERID = null;
end
if @TRANSACTIONTYPEOPTIONCODE = -1
set @TRANSACTIONTYPEOPTIONCODE = null;
if @REVENUETYPEOPTIONCODE = -1
set @REVENUETYPEOPTIONCODE = null;
if @STARTDATE is null and @ENDDATE is null
exec [dbo].[USP_RESOLVEDATEFILTER] @DATEFILTER, @STARTDATE output, @ENDDATE output;
else
begin
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);
end
set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
if @CURRENCYCODE = 1
set @CURRENCYID = @ORGANIZATIONCURRENCYID
if @CURRENCYCODE = 3
begin
set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
if @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
set @CURRENCYCODE = 1
end
select
@CURRENCYISOCURRENCYCODE = CURRENCY.ISO4217,
@CURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
@CURRENCYSYMBOL = CURRENCY.CURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.CURRENCY
where ID = @CURRENCYID;
set @ISUK = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D');
declare @HASSITES bit = 0;
declare @CHECKSITES bit = 0;
declare @ISADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
declare @MULTICURRENCYENABLED bit = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency');
if (select top 1 1 from dbo.Site) = 1
set @HASSITES = 1
if @HASSITES = 1 and @ISADMIN = 0
set @CHECKSITES = 1;
--Only create sites temp tables if needed
if @CHECKSITES = 1
begin
if object_id('tempdb..#TMP_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY_SITEONFEATURE') is not null
drop table #TMP_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY_SITEONFEATURE
create table #TMP_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY_SITEONFEATURE
(
SITEID uniqueidentifier
)
insert into #TMP_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY_SITEONFEATURE (SITEID)
select
SITEID
from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE)
end
if @HASSITES = 1 and @SITEFILTERMODE != 0
begin
if object_id('tempdb..#TMP_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY_SITEFILTER') is not null
drop table #TMP_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY_SITEFILTER
create table #TMP_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY_SITEFILTER
(
SITEID uniqueidentifier
)
insert into #TMP_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY_SITEFILTER (SITEID)
select
SITEID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
end
/* Get RevSplit IDs */
if object_id('tempdb..#TMP_DATA_REVENUEHISTORY_RECOGNITIONIDS') is not null
drop table #TMP_DATA_REVENUEHISTORY_RECOGNITIONIDS;
create table #TMP_DATA_REVENUEHISTORY_RECOGNITIONIDS
(
RECOGNITIONID uniqueidentifier,
SPLITID uniqueidentifier,
REVENUEID uniqueidentifier,
TRANSACTIONTYPECODE tinyint,
AMOUNT money,
REVENUESPLITAMOUNT money,
CURRENCYID uniqueidentifier,
ISRECOGNITIONCREDIT bit
);
declare @SQL nvarchar(max) = '';
declare @RECOGNITIONCREDITS_SQL nvarchar(max) = '';
if @CAMPAIGNFILTERMODE != 0
begin
set @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);
set @RECOGNITIONCREDITS_SQL = replace(@SQL, '@CAMPAIGNFILTERTABLE', '@CAMPAIGNFILTERTABLE_RC');
end
select @SQL = @SQL + '
insert into #TMP_DATA_REVENUEHISTORY_RECOGNITIONIDS(RECOGNITIONID, SPLITID, REVENUEID, TRANSACTIONTYPECODE, AMOUNT, REVENUESPLITAMOUNT, CURRENCYID, ISRECOGNITIONCREDIT)
select distinct
REVENUERECOGNITION.ID as RECOGNITIONID,
FINANCIALTRANSACTIONLINEITEM.ID,
FINANCIALTRANSACTION.ID,
FINANCIALTRANSACTION.TYPECODE,
REVENUERECOGNITION.AMOUNT as AMOUNT,
FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
case when @CURRENCYCODE = 0
then REVENUERECOGNITION.BASECURRENCYID
else @CURRENCYID
end CURRENCYID,
0 as ISRECOGNITIONCREDIT
from dbo.REVENUERECOGNITION ' + char(13);
select @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + '
insert into #TMP_DATA_REVENUEHISTORY_RECOGNITIONIDS(RECOGNITIONID, SPLITID, REVENUEID, TRANSACTIONTYPECODE, AMOUNT, REVENUESPLITAMOUNT, CURRENCYID, ISRECOGNITIONCREDIT)
select distinct
RECOGNITIONCREDIT.ID as RECOGNITIONID,
FINANCIALTRANSACTIONLINEITEM.ID,
FINANCIALTRANSACTION.ID,
FINANCIALTRANSACTION.TYPECODE,
RECOGNITIONCREDIT.AMOUNT as AMOUNT,
FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
case when @CURRENCYCODE = 0
then RECOGNITIONCREDIT.BASECURRENCYID
else @CURRENCYID
end CURRENCYID,
1 as ISRECOGNITIONCREDIT
from dbo.RECOGNITIONCREDIT ' + char(13);
--BBNT\RyanDow 2012-04-20
--Pulling recognition and revenue filter SQL directly into this dynamic SQL for performance gains
--Bypass UFN_REVENUERECOGNITION_VALIDFILTER(@RECOGNITIONFILTERID)
--If this logic is changed, the other places that use revenue/recognition filters will need to be updated
declare @REVENUEFILTERID uniqueidentifier
declare @REVENUEFILTERFUNCTION nvarchar(250)
declare @PLEDGERECOGNITIONCODE tinyint
if @RECOGNITIONFILTERID is not null
begin
select
@REVENUEFILTERID = RECOGNITIONFILTER.REVENUEFILTERID,
@PLEDGERECOGNITIONCODE = RECOGNITIONFILTER.PLEDGERECOGNITIONCODE,
@REVENUEFILTERFUNCTION = REVENUEFILTER.FUNCTIONNAME
from
dbo.RECOGNITIONFILTER
left join dbo.REVENUEFILTER on REVENUEFILTER.ID = RECOGNITIONFILTER.REVENUEFILTERID
where
RECOGNITIONFILTER.ID = @RECOGNITIONFILTERID
if @REVENUEFILTERID is not null and @REVENUEFILTERFUNCTION is not null and @REVENUEFILTERFUNCTION <> ''
begin
select @SQL = @SQL + '
inner join ' + @REVENUEFILTERFUNCTION + '() FILTERED on FILTERED.ID = REVENUERECOGNITION.REVENUESPLITID ' + CHAR(13);
end
set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + '
inner join dbo.UFN_RECOGNITIONCREDIT_VALIDFILTER(@RECOGNITIONFILTERID) FILTERED on RECOGNITIONCREDIT.ID = FILTERED.ID ' + char(13);
end
set @SQL = @SQL + '
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID ' + char(13)
--BBNT\RyanDow 2012-04-20
--Pulling recognition and revenue filter SQL directly into this dynamic SQL for performance gains
--Bypass UFN_REVENUERECOGNITION_VALIDFILTER(@RECOGNITIONFILTERID)
--If this logic is changed, the other places that use revenue/recognition filters will need to be updated
if @RECOGNITIONFILTERID is not null
begin
select @SQL = @SQL + '
left join dbo.RECOGNITIONFILTERRECOGNTIONTYPE on RECOGNITIONFILTERRECOGNTIONTYPE.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID
and RECOGNITIONFILTERRECOGNTIONTYPE.RECOGNITIONFILTERID = @RECOGNITIONFILTERID ' + char(13)
if @PLEDGERECOGNITIONCODE <> 1
begin
select @SQL = @SQL + '
left join dbo.INSTALLMENTSPLITPAYMENT on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
left join dbo.FINANCIALTRANSACTION as PLEDGE on INSTALLMENTSPLITPAYMENT.PLEDGEID = PLEDGE.ID ' + CHAR(13)
end
end
select @SQL = @SQL + '
where REVENUERECOGNITION.CONSTITUENTID = @CONSTITUENTID
and FINANCIALTRANSACTION.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null ' + char(13);
set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + '
left join dbo.DONORCHALLENGEENCUMBERED on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
left join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = DONORCHALLENGEENCUMBERED.REVENUESPLITID
left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
left join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
where RECOGNITIONCREDIT.CONSTITUENTID = @CONSTITUENTID
and ((RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 0) or (RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1 and RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID is not null))' + char(13);
if @STARTDATE is not null
begin
set @SQL = @SQL + 'and REVENUERECOGNITION.EFFECTIVEDATE >= @STARTDATE ' + char(13);
set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + 'and RECOGNITIONCREDIT.EFFECTIVEDATE >= @STARTDATE ' + char(13);
end
if @ENDDATE is not null
begin
set @SQL = @SQL + 'and REVENUERECOGNITION.EFFECTIVEDATE <= @ENDDATE ' + char(13);
set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + 'and RECOGNITIONCREDIT.EFFECTIVEDATE <= @ENDDATE ' + char(13);
end
if @TRANSACTIONTYPEOPTIONCODE is not null
begin
set @SQL = @SQL + 'and FINANCIALTRANSACTION.TYPECODE = @TRANSACTIONTYPEOPTIONCODE ' + char(13);
--We want to show internal sponsor credits under 'Donor challenge claim' even though they are actually tied to payment
set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + 'and (FINANCIALTRANSACTION.TYPECODE is null or (FINANCIALTRANSACTION.TYPECODE = 0 and @TRANSACTIONTYPEOPTIONCODE = 8))' + char(13);
end
if @REVENUETYPEOPTIONCODE is not null
begin
set @SQL = @SQL + 'and REVENUESPLIT_EXT.TYPECODE = @REVENUETYPEOPTIONCODE ' + char(13);
set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + 'and (REVENUESPLIT_EXT.TYPECODE is null or REVENUESPLIT_EXT.TYPECODE = @REVENUETYPEOPTIONCODE) ' + char(13);
end
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 = FINANCIALTRANSACTIONLINEITEM.ID
) ' + char(13);
set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL +
'and ((FINANCIALTRANSACTIONLINEITEM.ID is null)
or exists (
select 1
from dbo.REVENUESPLITCAMPAIGN
inner join @CAMPAIGNFILTERTABLE_RC CAMPAIGNFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNFILTER.ID
where REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
))' + char(13);
end
--BBNT\RyanDow 2012-04-20
--Pulling recognition and revenue filter SQL directly into this dynamic SQL for performance gains
--Bypass UFN_REVENUERECOGNITION_VALIDFILTER(@RECOGNITIONFILTERID)
--If this logic is changed, the other places that use revenue/recognition filters will need to be updated
if @RECOGNITIONFILTERID is not null
begin
if @PLEDGERECOGNITIONCODE <> 1
begin
select @SQL = @SQL + '
and
(
-- Find commitments or payments other than pledges
(FINANCIALTRANSACTION.TYPECODE <> 0 or REVENUESPLIT_EXT.APPLICATIONCODE <> 2)
or
-- Find payments without pledge recognition
(
(FINANCIALTRANSACTION.TYPECODE = 0 and REVENUESPLIT_EXT.APPLICATIONCODE = 2)
and
(not exists (select top 1 RR.ID from dbo.REVENUERECOGNITION as RR inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI on FTLI.ID = RR.REVENUESPLITID where RR.CONSTITUENTID = @CONSTITUENTID and FTLI.FINANCIALTRANSACTIONID = PLEDGE.ID))
)
) ' + char(13)
end
select @SQL = @SQL + '
and
(
RECOGNITIONFILTERRECOGNTIONTYPE.ID is not null
or not exists(select top 1 ID from dbo.RECOGNITIONFILTERRECOGNTIONTYPE RSUB where RSUB.RECOGNITIONFILTERID = @RECOGNITIONFILTERID)
) ' + char(13)
end
--FILTER FOR SECURITY AND SITEFILTER
if @CHECKSITES = 1
begin
if @SITEFILTERMODE = 0
begin
set @SQL = @SQL + '
and exists (select 1 from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM.ID) REVSITES
inner join #TMP_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY_SITEONFEATURE FEATURE on isnull(FEATURE.SITEID, ''00000000-0000-0000-0000-000000000000'') = isnull(REVSITES.SITEID, ''00000000-0000-0000-0000-000000000000'')) '
set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + '
and exists (select 1 from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM.ID) REVSITES
inner join #TMP_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY_SITEONFEATURE FEATURE on isnull(FEATURE.SITEID, ''00000000-0000-0000-0000-000000000000'') = isnull(REVSITES.SITEID, ''00000000-0000-0000-0000-000000000000'')) '
end
else
begin
set @SQL = @SQL + '
and exists (select 1 from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM.ID) REVSITES
inner join #TMP_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY_SITEONFEATURE FEATURE on isnull(FEATURE.SITEID, ''00000000-0000-0000-0000-000000000000'') = isnull(REVSITES.SITEID, ''00000000-0000-0000-0000-000000000000'')
inner join #TMP_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY_SITEFILTER FILTER on isnull(FILTER.SITEID, ''00000000-0000-0000-0000-000000000000'') = isnull(REVSITES.SITEID, ''00000000-0000-0000-0000-000000000000'')) '
set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + '
and exists (select 1 from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM.ID) REVSITES
inner join #TMP_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY_SITEONFEATURE FEATURE on isnull(FEATURE.SITEID, ''00000000-0000-0000-0000-000000000000'') = isnull(REVSITES.SITEID, ''00000000-0000-0000-0000-000000000000'')
inner join #TMP_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY_SITEFILTER FILTER on isnull(FILTER.SITEID, ''00000000-0000-0000-0000-000000000000'') = isnull(REVSITES.SITEID, ''00000000-0000-0000-0000-000000000000'')) '
end
end
if @CHECKSITES = 0 and @HASSITES = 1 and @SITEFILTERMODE != 0
begin
set @SQL = @SQL + '
and exists (select 1 from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM.ID) REVSITES
inner join #TMP_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY_SITEFILTER FILTER on isnull(FILTER.SITEID, ''00000000-0000-0000-0000-000000000000'') = isnull(REVSITES.SITEID, ''00000000-0000-0000-0000-000000000000'')) '
set @RECOGNITIONCREDITS_SQL = @RECOGNITIONCREDITS_SQL + '
and exists (select 1 from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM.ID) REVSITES
inner join #TMP_CONSTITUENT_EXPANDED_RECOGNITIONHISTORY_SITEFILTER FILTER on isnull(FILTER.SITEID, ''00000000-0000-0000-0000-000000000000'') = isnull(REVSITES.SITEID, ''00000000-0000-0000-0000-000000000000'')) '
end
exec sp_executesql @SQL
,N'@CONSTITUENTID uniqueidentifier
,@RECOGNITIONFILTERID uniqueidentifier
,@STARTDATE datetime
,@ENDDATE datetime
,@TRANSACTIONTYPEOPTIONCODE int
,@REVENUETYPEOPTIONCODE int
,@CAMPAIGNSSELECTED xml
,@CURRENCYCODE tinyint
,@CURRENCYID uniqueidentifier
,@CURRENTAPPUSERID uniqueidentifier
,@SECURITYFEATUREID uniqueidentifier
,@SECURITYFEATURETYPE tinyint
,@SITEFILTERMODE tinyint
,@SITESSELECTED xml'
,@CONSTITUENTID
,@RECOGNITIONFILTERID
,@STARTDATE
,@ENDDATE
,@TRANSACTIONTYPEOPTIONCODE
,@REVENUETYPEOPTIONCODE
,@CAMPAIGNSSELECTED
,@CURRENCYCODE
,@CURRENCYID
,@CURRENTAPPUSERID
,@SECURITYFEATUREID
,@SECURITYFEATURETYPE
,@SITEFILTERMODE
,@SITESSELECTED;
exec sp_executesql @RECOGNITIONCREDITS_SQL
,N'@CONSTITUENTID uniqueidentifier
,@RECOGNITIONFILTERID uniqueidentifier
,@STARTDATE datetime
,@ENDDATE datetime
,@TRANSACTIONTYPEOPTIONCODE int
,@REVENUETYPEOPTIONCODE int
,@CAMPAIGNSSELECTED xml
,@CURRENCYCODE tinyint
,@CURRENCYID uniqueidentifier
,@CURRENTAPPUSERID uniqueidentifier
,@SECURITYFEATUREID uniqueidentifier
,@SECURITYFEATURETYPE tinyint
,@SITEFILTERMODE tinyint
,@SITESSELECTED xml'
,@CONSTITUENTID
,@RECOGNITIONFILTERID
,@STARTDATE
,@ENDDATE
,@TRANSACTIONTYPEOPTIONCODE
,@REVENUETYPEOPTIONCODE
,@CAMPAIGNSSELECTED
,@CURRENCYCODE
,@CURRENCYID
,@CURRENTAPPUSERID
,@SECURITYFEATUREID
,@SECURITYFEATURETYPE
,@SITEFILTERMODE
,@SITESSELECTED;
--Update the amounts to multicurrency if we aren't using the base currency
if @CURRENCYCODE <> 0
begin
update
RIC
set
RIC.AMOUNT = REVENUERECOGNITIONINCURRENCY.AMOUNTINCURRENCY
from
#TMP_DATA_REVENUEHISTORY_RECOGNITIONIDS RIC
inner join dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@CURRENCYDECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE
) as REVENUERECOGNITIONINCURRENCY on REVENUERECOGNITIONINCURRENCY.ID = RIC.RECOGNITIONID
where
RIC.ISRECOGNITIONCREDIT = 0;
update
RIC
set
RIC.AMOUNT = RECOGNITIONCREDITINCURRENCY.AMOUNTINCURRENCY
from
#TMP_DATA_REVENUEHISTORY_RECOGNITIONIDS RIC
inner join dbo.UFN_RECOGNITIONCREDIT_GETAMOUNTINCURRENCY_BULK(
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@CURRENCYDECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE
) as RECOGNITIONCREDITINCURRENCY on RECOGNITIONCREDITINCURRENCY.ID = RIC.RECOGNITIONID
where
RIC.ISRECOGNITIONCREDIT = 1;
update
RIC
set
RIC.REVENUESPLITAMOUNT = REVENUESPLITINCURRENCY.AMOUNTINCURRENCY
from
#TMP_DATA_REVENUEHISTORY_RECOGNITIONIDS RIC
left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@CURRENCYDECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE
) as REVENUESPLITINCURRENCY on REVENUESPLITINCURRENCY.ID = RIC.SPLITID;
end;
if object_id('tempdb..#TMP_DATA_REVENUEHISTORY_RECOGNITION_CTE') is not null
drop table #TMP_DATA_REVENUEHISTORY_RECOGNITION_CTE;
create table #TMP_DATA_REVENUEHISTORY_RECOGNITION_CTE(
RECOGNITIONID uniqueidentifier
,SPLITID uniqueidentifier
,REVENUEID uniqueidentifier
,COMMITMENTREVENUEID uniqueidentifier
,AMOUNT money
,GROSSAMOUNT money
,TRANSACTIONTYPECODE tinyint
,[APPLICATION] nvarchar(100) collate database_default
,REVENUETYPE nvarchar(100) collate database_default
,DESIGNATIONID uniqueidentifier
,REVENUESPLITDESCRIPTION nvarchar(700) collate database_default
,REVENUESPLITAMOUNT money
);
insert into #TMP_DATA_REVENUEHISTORY_RECOGNITION_CTE
select
RECOGNITIONINCURRENCY.RECOGNITIONID,
RECOGNITIONINCURRENCY.SPLITID,
RECOGNITIONINCURRENCY.REVENUEID,
case
when @GROUPBY = 0 then dbo.UFN_REVENUESPLIT_GETCOMMITMENTID(FINANCIALTRANSACTIONLINEITEM.ID, REVENUESPLIT_EXT.APPLICATIONCODE)
else null
end COMMITMENTREVENUEID,
RECOGNITIONINCURRENCY.AMOUNT,
case
when @ISUK = 0 then null
else(
case
when RECOGNITIONINCURRENCY.REVENUESPLITAMOUNT > 0 then
case RECOGNITIONINCURRENCY.TRANSACTIONTYPECODE
when 0 then
case
when RECOGNITIONINCURRENCY.AMOUNT > RECOGNITIONINCURRENCY.REVENUESPLITAMOUNT
then dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT_INCURRENCY(RECOGNITIONINCURRENCY.SPLITID, 1, RECOGNITIONINCURRENCY.CURRENCYID) + RECOGNITIONINCURRENCY.AMOUNT
else RECOGNITIONINCURRENCY.AMOUNT/RECOGNITIONINCURRENCY.REVENUESPLITAMOUNT * dbo.UFN_GIFTAIDREVENUESPLIT_GETSPLITTAXCLAIMAMOUNT_INCURRENCY(RECOGNITIONINCURRENCY.SPLITID, 1, RECOGNITIONINCURRENCY.CURRENCYID) + RECOGNITIONINCURRENCY.AMOUNT
end
when 1 then
case
when RECOGNITIONINCURRENCY.AMOUNT > RECOGNITIONINCURRENCY.REVENUESPLITAMOUNT
then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT_2(RECOGNITIONINCURRENCY.SPLITID, RECOGNITIONINCURRENCY.CURRENCYID) - RECOGNITIONINCURRENCY.REVENUESPLITAMOUNT + RECOGNITIONINCURRENCY.AMOUNT
else RECOGNITIONINCURRENCY.AMOUNT/RECOGNITIONINCURRENCY.REVENUESPLITAMOUNT * (dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGESPLITGROSSAMOUNT_2(RECOGNITIONINCURRENCY.SPLITID, RECOGNITIONINCURRENCY.CURRENCYID) - RECOGNITIONINCURRENCY.REVENUESPLITAMOUNT) + RECOGNITIONINCURRENCY.AMOUNT
end
else RECOGNITIONINCURRENCY.AMOUNT
end
else REVENUESPLITAMOUNT
end
)
end as [GROSSAMOUNT],
RECOGNITIONINCURRENCY.TRANSACTIONTYPECODE,
REVENUESPLIT_EXT.APPLICATION,
REVENUESPLIT_EXT.TYPE REVENUETYPE,
REVENUESPLIT_EXT.DESIGNATIONID,
case
when FINANCIALTRANSACTIONLINEITEM.DELETEDON is null then
cast('(' + REVENUESPLIT_EXT.APPLICATION + ') ' + cast(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT as nvarchar(20)) + ' ' + coalesce(REVENUEPAYMENTMETHOD.PAYMENTMETHOD, 'None') + ' - ' + ltrim(rtrim(CONSTITUENT.NAME)) as nvarchar(700))
else null end as REVENUESPLITDESCRIPTION,
RECOGNITIONINCURRENCY.REVENUESPLITAMOUNT
from #TMP_DATA_REVENUEHISTORY_RECOGNITIONIDS RECOGNITIONINCURRENCY
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = RECOGNITIONINCURRENCY.SPLITID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
left join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
left join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = FINANCIALTRANSACTION.ID
left outer join dbo.CONSTITUENT on FINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID;
create table #DESIGNATIONS (
DESIGNATIONID uniqueidentifier primary key
,DESIGNATIONNAME nvarchar(512) collate database_default
);
create table #CAMPAIGNSBYSPLIT (
REVENUESPLITID uniqueidentifier primary key
,CAMPAIGNNAME nvarchar(MAX) collate database_default
);
create table #CAMPAIGNSBYREV (
REVENUEID uniqueidentifier primary key
,CAMPAIGNNAME nvarchar(MAX) collate database_default
);
insert into #DESIGNATIONS (DESIGNATIONID, DESIGNATIONNAME)
select DESIGNATIONID, DN.NAME
from (select distinct DESIGNATIONID from #TMP_DATA_REVENUEHISTORY_RECOGNITION_CTE) V1
inner join dbo.DESIGNATION as DN on V1.DESIGNATIONID = DN.ID
insert into #CAMPAIGNSBYSPLIT (REVENUESPLITID, CAMPAIGNNAME)
select T1.SPLITID, dbo.UDA_BUILDLIST(CAMPAIGN.[NAME])
from #TMP_DATA_REVENUEHISTORY_RECOGNITION_CTE T1
inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = T1.SPLITID
inner join dbo.CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
group by T1.SPLITID;
insert into #CAMPAIGNSBYREV (REVENUEID, CAMPAIGNNAME)
select distinct T1.REVENUEID, dbo.UDA_BUILDLIST(CAMPAIGN.[NAME])
from #TMP_DATA_REVENUEHISTORY_RECOGNITION_CTE T1
inner join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = T1.SPLITID
inner join dbo.CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
group by T1.REVENUEID;
if object_id('tempdb..#TMP_DATA_REVENUEHISTORY_SUMMEDREVENUE_CTE') is not null
drop table #TMP_DATA_REVENUEHISTORY_SUMMEDREVENUE_CTE;
create table #TMP_DATA_REVENUEHISTORY_SUMMEDREVENUE_CTE (
RECOGNITIONID uniqueidentifier
,REVENUEID uniqueidentifier
,PARENTID uniqueidentifier
,APPLICATION nvarchar(max) collate database_default
,REVENUETYPE nvarchar(max) collate database_default
,RECOGNITIONCREDITTYPES nvarchar(max) collate database_default
,DESIGNATIONS nvarchar(MAX) collate database_default
,REVENUECATEGORIES nvarchar(MAX) collate database_default
,CAMPAIGNS nvarchar(MAX) collate database_default
,SITES nvarchar(MAX) collate database_default
,AMOUNT money
,GROSSAMOUNT money
,REVENUESPLITAMOUNT money
);
if @GROUPBY = 1
insert into #TMP_DATA_REVENUEHISTORY_SUMMEDREVENUE_CTE
select
null as RECOGNITIONID,
REVENUEID,
null as PARENTID,
dbo.UDA_BUILDLIST(distinct RECOGNITION_CTE.APPLICATION) APPLICATION,
dbo.UDA_BUILDLIST(distinct RECOGNITION_CTE.REVENUETYPE) REVENUETYPE,
null RECOGNITIONCREDITTYPES,
null DESIGNATIONS,
null REVENUECATEGORIES,
null CAMPAIGNS,
null SITES,
sum(coalesce(RECOGNITION_CTE.AMOUNT,0)) AMOUNT,
sum(coalesce(RECOGNITION_CTE.GROSSAMOUNT,0)) GROSSAMOUNT,
sum(coalesce(RECOGNITION_CTE.REVENUESPLITAMOUNT,0)) REVENUESPLITAMOUNT
from #TMP_DATA_REVENUEHISTORY_RECOGNITION_CTE RECOGNITION_CTE
group by REVENUEID
having COUNT(*) > 1;
if @GROUPBY = 0
insert into #TMP_DATA_REVENUEHISTORY_SUMMEDREVENUE_CTE
select
null RECOGNITIONID,
RECOGNITION_CTE.REVENUEID,
null as PARENTID,
dbo.UDA_BUILDLIST(distinct RECOGNITION_CTE.APPLICATION) APPLICATION,
dbo.UDA_BUILDLIST(distinct RECOGNITION_CTE.REVENUETYPE) REVENUETYPE,
dbo.UDA_BUILDLIST(distinct REVENUERECOGNITIONTYPECODE.DESCRIPTION) RECOGNITIONCREDITTYPES,
dbo.UDA_BUILDLIST(isnull (#DESIGNATIONS.DESIGNATIONNAME, REVENUESPLITDESCRIPTION)) DESIGNATIONS,
dbo.UDA_BUILDLIST(distinct GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME) REVENUECATEGORIES,
#CAMPAIGNSBYREV.CAMPAIGNNAME CAMPAIGNS,
(
select dbo.UDA_BUILDLIST(SITE.NAME)
from dbo.UFN_SITEID_MAPFROM_REVENUEID(RECOGNITION_CTE.REVENUEID) RSITE
inner join dbo.SITE on SITE.ID = RSITE.SITEID
) SITES,
sum(coalesce(RECOGNITION_CTE.AMOUNT,0)) AMOUNT,
sum(coalesce(RECOGNITION_CTE.GROSSAMOUNT,0)) GROSSAMOUNT,
sum(coalesce(RECOGNITION_CTE.REVENUESPLITAMOUNT,0)) REVENUESPLITAMOUNT
from #TMP_DATA_REVENUEHISTORY_RECOGNITION_CTE RECOGNITION_CTE
inner join dbo.REVENUERECOGNITION on RECOGNITION_CTE.RECOGNITIONID = REVENUERECOGNITION.ID
left join dbo.REVENUERECOGNITIONTYPECODE on REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
left join #DESIGNATIONS on RECOGNITION_CTE.DESIGNATIONID = #DESIGNATIONS.DESIGNATIONID
left join #CAMPAIGNSBYREV on RECOGNITION_CTE.REVENUEID = #CAMPAIGNSBYREV.REVENUEID
left join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = RECOGNITION_CTE.SPLITID
left join dbo.GLREVENUECATEGORYMAPPING on GLREVENUECATEGORYMAPPING.ID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID
where RECOGNITION_CTE.TRANSACTIONTYPECODE <> 0
group by RECOGNITION_CTE.REVENUEID, #CAMPAIGNSBYREV.CAMPAIGNNAME;
insert into #TMP_DATA_REVENUEHISTORY_SUMMEDREVENUE_CTE
select
RECOGNITION_CTE.RECOGNITIONID,
REVENUEID,
case @GROUPBY
when 0 then COMMITMENTREVENUEID
when 1 then REVENUEID
else null
end as PARENTID,
RECOGNITION_CTE.APPLICATION,
RECOGNITION_CTE.REVENUETYPE,
REVENUERECOGNITIONTYPECODE.DESCRIPTION RECOGNITIONCREDITTYPES,
isnull(#DESIGNATIONS.DESIGNATIONNAME, REVENUESPLITDESCRIPTION) DESIGNATIONS,
GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME REVENUECATEGORIES,
#CAMPAIGNSBYSPLIT.CAMPAIGNNAME CAMPAIGNS,
(
select dbo.UDA_BUILDLIST(SITE.NAME)
from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RECOGNITION_CTE.SPLITID) RSITE
inner join dbo.SITE on SITE.ID = RSITE.SITEID
) SITES,
RECOGNITION_CTE.AMOUNT,
RECOGNITION_CTE.GROSSAMOUNT,
RECOGNITION_CTE.REVENUESPLITAMOUNT
from #TMP_DATA_REVENUEHISTORY_RECOGNITION_CTE RECOGNITION_CTE
inner join dbo.REVENUERECOGNITION on RECOGNITION_CTE.RECOGNITIONID = REVENUERECOGNITION.ID
left join dbo.REVENUERECOGNITIONTYPECODE on REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
left join #DESIGNATIONS on RECOGNITION_CTE.DESIGNATIONID = #DESIGNATIONS.DESIGNATIONID
left join #CAMPAIGNSBYSPLIT on RECOGNITION_CTE.SPLITID = #CAMPAIGNSBYSPLIT.REVENUESPLITID
left join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = RECOGNITION_CTE.SPLITID
left join dbo.GLREVENUECATEGORYMAPPING on GLREVENUECATEGORYMAPPING.ID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID
where RECOGNITION_CTE.TRANSACTIONTYPECODE = 0
or coalesce(@GROUPBY, -1) <> 0;
insert into #TMP_DATA_REVENUEHISTORY_SUMMEDREVENUE_CTE
select
RECOGNITION_CTE.RECOGNITIONID,
REVENUEID,
case @GROUPBY
when 0 then COMMITMENTREVENUEID
when 1 then REVENUEID
else null
end as PARENTID,
RECOGNITION_CTE.APPLICATION,
RECOGNITION_CTE.REVENUETYPE,
REVENUERECOGNITIONTYPECODE.DESCRIPTION RECOGNITIONCREDITTYPES,
isnull(#DESIGNATIONS.DESIGNATIONNAME, REVENUESPLITDESCRIPTION) DESIGNATIONS,
GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME REVENUECATEGORIES,
#CAMPAIGNSBYSPLIT.CAMPAIGNNAME CAMPAIGNS,
(
select dbo.UDA_BUILDLIST(SITE.NAME)
from dbo.SITE
where SITE.ID = dbo.UFN_SITEID_MAPFROM_DESIGNATIONID(RECOGNITIONCREDIT.DESIGNATIONID)
) SITES,
RECOGNITION_CTE.AMOUNT,
RECOGNITION_CTE.GROSSAMOUNT,
RECOGNITION_CTE.REVENUESPLITAMOUNT
from #TMP_DATA_REVENUEHISTORY_RECOGNITION_CTE RECOGNITION_CTE
inner join dbo.RECOGNITIONCREDIT on RECOGNITION_CTE.RECOGNITIONID = RECOGNITIONCREDIT.ID
left join dbo.DONORCHALLENGEENCUMBERED on RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID = DONORCHALLENGEENCUMBERED.ID
left join dbo.REVENUERECOGNITIONTYPECODE on RECOGNITIONCREDIT.USERRECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
left join #DESIGNATIONS on RECOGNITION_CTE.DESIGNATIONID = #DESIGNATIONS.DESIGNATIONID
left join #CAMPAIGNSBYSPLIT on RECOGNITION_CTE.SPLITID = #CAMPAIGNSBYSPLIT.REVENUESPLITID
left join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = RECOGNITION_CTE.SPLITID
left join dbo.GLREVENUECATEGORYMAPPING on GLREVENUECATEGORYMAPPING.ID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID
where (RECOGNITION_CTE.TRANSACTIONTYPECODE = 0
or coalesce(@GROUPBY, -1) <> 0)
and ((RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 0) or (RECOGNITIONCREDIT.RECOGNITIONCREDITTYPECODE = 1 and RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID is not null));
select
case
when REVENUERECOGNITION.REVENUESPLITID is null
then
case
when RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID is null
then SUMMEDREVENUE_CTE.REVENUEID
else (select REVENUESPLITID from dbo.DONORCHALLENGEENCUMBERED where ID = RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID)
end
else REVENUERECOGNITION.REVENUESPLITID
end ID,
case when REVENUERECOGNITION.REVENUESPLITID is null
then (select REVENUESPLITID from dbo.DONORCHALLENGEENCUMBERED where ID = RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID)
else REVENUERECOGNITION.REVENUESPLITID
end as REVENUESPLITID,
case when RECOGNITIONCREDIT.ID is not null
then null --Disable go to payment for internal sponsor payment
else SUMMEDREVENUE_CTE.REVENUEID
end REVENUEID,
SUMMEDREVENUE_CTE.PARENTID,
case
when REVENUE_EXT.GIVENANONYMOUSLY = 1 then 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.gift_anonymous_16.png'
else ''
end IMAGEKEY,
case
when REVENUERECOGNITION.REVENUESPLITID is null and RECOGNITIONCREDIT.ID is null then 'F989EA79-EA4D-4FC7-B3F8-6EA606125CCA'
else '5DBB948F-BE5E-4E85-B652-CA3AEDE26A4F'
end VIEWFORMID,
case when REVENUERECOGNITION.ID is null then case when RECOGNITIONCREDIT.ID is null then cast(FINANCIALTRANSACTION.DATE as datetime) else RECOGNITIONCREDIT.EFFECTIVEDATE end else REVENUERECOGNITION.EFFECTIVEDATE end EFFECTIVEDATE,
SUMMEDREVENUE_CTE.AMOUNT,
SUMMEDREVENUE_CTE.GROSSAMOUNT,
RECOGNITIONCREDITTYPES as RECOGNITIONCREDITTYPE,
case
when RECOGNITIONCREDIT.ID is not null
then
'Donor challenge claim - Internal sponsor'
else FINANCIALTRANSACTION.TYPE
end as TRANSACTIONTYPE,
SUMMEDREVENUE_CTE.APPLICATION,
SUMMEDREVENUE_CTE.REVENUETYPE,
SUMMEDREVENUE_CTE.DESIGNATIONS,
SUMMEDREVENUE_CTE.REVENUECATEGORIES,
SUMMEDREVENUE_CTE.CAMPAIGNS,
cast(FINANCIALTRANSACTION.DATE as datetime) DATE,
SUMMEDREVENUE_CTE.REVENUESPLITAMOUNT,
case
when @ISUK = 0 then null
else
case @CURRENCYCODE
when 0 then
case
when SUMMEDREVENUE_CTE.RECOGNITIONID is null
then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(FINANCIALTRANSACTION.ID, 1,0)
else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUERECOGNITION.REVENUESPLITID, 1,0)
end
when 1 then
case
when SUMMEDREVENUE_CTE.RECOGNITIONID is null
then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(FINANCIALTRANSACTION.ID, 1,2)
else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUERECOGNITION.REVENUESPLITID, 1,2)
end
when 3 then
case @CURRENCYID
when case when FINANCIALTRANSACTION.DELETEDON is null then FINANCIALTRANSACTION.TRANSACTIONCURRENCYID else null end then
case
when SUMMEDREVENUE_CTE.RECOGNITIONID is null
then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNT_2(FINANCIALTRANSACTION.ID, 1,1)
else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNT_2(REVENUERECOGNITION.REVENUESPLITID,1,1)
end
else
case
when SUMMEDREVENUE_CTE.RECOGNITIONID is null
then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNTINCURRENCY(FINANCIALTRANSACTION.ID, 1,@CURRENCYID)
else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(REVENUERECOGNITION.REVENUESPLITID,1,@CURRENCYID)
end
end
end
end REVENUESPLITGROSSAMOUNT,
case
when RECOGNITIONCREDIT.ID is not null
then
(select DONORCHALLENGESPONSORCODE.DESCRIPTION
from dbo.DONORCHALLENGEENCUMBERED
inner join dbo.DONORCHALLENGE on DONORCHALLENGEENCUMBERED.DONORCHALLENGEID = DONORCHALLENGE.ID
inner join dbo.DONORCHALLENGESPONSORCODE on DONORCHALLENGE.INTERNALSPONSORCODEID = DONORCHALLENGESPONSORCODE.ID
where DONORCHALLENGEENCUMBERED.ID = RECOGNITIONCREDIT.DONORCHALLENGEENCUMBEREDID)
else NF.NAME
end,
SUMMEDREVENUE_CTE.SITES,
case @CURRENCYCODE
when 0 then case when FINANCIALTRANSACTION.DELETEDON is null then
case when @MULTICURRENCYENABLED = 1 then isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, (select V.BASECURRENCYID from dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V where V.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID))
else FINANCIALTRANSACTION.TRANSACTIONCURRENCYID end
else null end
when 1 then @ORGANIZATIONCURRENCYID
else @CURRENCYID
end as DISPLAYCURRENCY
from #TMP_DATA_REVENUEHISTORY_SUMMEDREVENUE_CTE SUMMEDREVENUE_CTE
inner join dbo.FINANCIALTRANSACTION on SUMMEDREVENUE_CTE.REVENUEID = FINANCIALTRANSACTION.ID
inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
left join dbo.REVENUERECOGNITION on SUMMEDREVENUE_CTE.RECOGNITIONID = REVENUERECOGNITION.ID
left join dbo.RECOGNITIONCREDIT on SUMMEDREVENUE_CTE.RECOGNITIONID = RECOGNITIONCREDIT.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FINANCIALTRANSACTION.CONSTITUENTID) NF
order by EFFECTIVEDATE desc, NF.NAME, AMOUNT
end