USP_DATALIST_CONSTITUENT_EXPANDED_REVENUEHISTORY
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 | |
| @REVENUEFILTERID | uniqueidentifier | IN | Revenue 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_REVENUEHISTORY (
@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
,@REVENUEFILTERID uniqueidentifier = '00000000-0000-0000-0000-000000000000'
,@CURRENCYCODE tinyint = 2
,@CAMPAIGNFILTERMODE tinyint = 0
,@CAMPAIGNSSELECTED xml = null
,@STARTDATE datetime = null
,@ENDDATE datetime = null
)
with execute as owner
as
set nocount on;
declare @ORGANIZATIONCURRENCYID uniqueidentifier = null
,@CURRENCYID uniqueidentifier = null
,@CURRENCYROUNDINGTYPECODE tinyint
,@CURRENCYISOCURRENCYCODE nvarchar(3) = null
,@CURRENCYDECIMALDIGITS tinyint = 0
,@CURRENCYSYMBOL nvarchar(5) = null
,@CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint = 0
,@CONSTITID uniqueidentifier
,@DATEFROM datetime
,@DATETO datetime
,@ISUK bit
,@ISGROUP bit = 0
,@INCLUDEMEMBERGIVING bit = 0;
--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 @REVENUEFILTERID = '00000000-0000-0000-0000-000000000000'
begin
select
@REVENUEFILTERID = ID
from
dbo.REVENUEFILTER
where
ISDEFAULT = 1;
if @REVENUEFILTERID = '00000000-0000-0000-0000-000000000000'
set @REVENUEFILTERID = 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();
declare @ORIGINCODE tinyint
select @ORIGINCODE = ORGANIZATIONAMOUNTORIGINCODE
from dbo.MULTICURRENCYCONFIGURATION;
if @CURRENCYCODE = 1
set @CURRENCYID = @ORGANIZATIONCURRENCYID
if @CURRENCYCODE = 3
begin
set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
if @CURRENCYID = @ORGANIZATIONCURRENCYID
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;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
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;
select @ISGROUP = 1
,@INCLUDEMEMBERGIVING = case
when GROUPDATA.GROUPTYPECODE = 0
or GROUPTYPE.INCLUDEMEMBERGIVING = 1
then 1
else 0
end
from dbo.GROUPDATA
left join dbo.GROUPTYPE on GROUPDATA.GROUPTYPEID = GROUPTYPE.ID
where GROUPDATA.ID = @CONSTITUENTID;
--Only create sites temp tables if needed
if @CHECKSITES = 1
begin
if object_id('tempdb..#TMP_CONSTITUENT_EXPANDED_REVENUEHISTORY_SITEONFEATURE') is not null
drop table #TMP_CONSTITUENT_EXPANDED_REVENUEHISTORY_SITEONFEATURE
create table #TMP_CONSTITUENT_EXPANDED_REVENUEHISTORY_SITEONFEATURE
(
SITEID uniqueidentifier
)
insert into #TMP_CONSTITUENT_EXPANDED_REVENUEHISTORY_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_REVENUEHISTORY_SITEFILTER') is not null
drop table #TMP_CONSTITUENT_EXPANDED_REVENUEHISTORY_SITEFILTER
create table #TMP_CONSTITUENT_EXPANDED_REVENUEHISTORY_SITEFILTER
(
SITEID uniqueidentifier
)
insert into #TMP_CONSTITUENT_EXPANDED_REVENUEHISTORY_SITEFILTER (SITEID)
select
SITEID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED)
end
/* Get RevSplit IDs */
if object_id('tempdb..#TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2') is not null
drop table #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2;
-- Adding REVENUEID to temp table solely to improve query plans - this value technically can always be grabbed off the FTLI, but having it here can speed things up
create table #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 (
ID uniqueidentifier
,REVENUEID uniqueidentifier
,CONSTITUENTID uniqueidentifier
,TRANSACTIONCURRENCYID uniqueidentifier
,[DATE] datetime
,TYPECODE tinyint
,TYPE nvarchar(27) collate database_default
,APPLICATIONCODE tinyint
,APPLICATION nvarchar(50) collate database_default
,TRANSACTIONAMOUNT money
,BASEAMOUNT money
,ORGAMOUNT money
,SPLIT_TYPECODE tinyint
,SPLIT_TYPE nvarchar(50) collate database_default
,DESIGNATIONID uniqueidentifier
,VISIBLE bit
,EXTRA bit
);
CREATE CLUSTERED INDEX IX_TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2_ID ON #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2(ID);
if object_id('tempdb..#TMP_DATA_REVENUEHISTORY_CONSTITUENTS') is not null
drop table #TMP_DATA_REVENUEHISTORY_CONSTITUENTS;
-- Adding REVENUEID to temp table solely to improve query plans - this value technically can always be grabbed off the FTLI, but having it here can speed things up
if @INCLUDEMEMBERGIVING = 1
create table #TMP_DATA_REVENUEHISTORY_CONSTITUENTS
(CONSTITUENTID uniqueidentifier primary key
,STARTDATE datetime
,ENDDATE datetime);
if object_id('tempdb..#TMP_DATA_REVENUEHISTORY_REVENUE') is not null
drop table #TMP_DATA_REVENUEHISTORY_REVENUE;
create table #TMP_DATA_REVENUEHISTORY_REVENUE
(ID uniqueidentifier primary key
,CONSTITUENTID uniqueidentifier
,TRANSACTIONCURRENCYID uniqueidentifier
,[DATE] datetime
,TYPECODE tinyint
,TYPE nvarchar(27) collate database_default)
declare @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);
end
if @INCLUDEMEMBERGIVING = 1
set @SQL = @SQL + '
insert into #TMP_DATA_REVENUEHISTORY_CONSTITUENTS (CONSTITUENTID, STARTDATE, ENDDATE)
select @CONSTITUENTID CONSTITUENTID, @STARTDATE STARTDATE, @ENDDATE ENDDATE
union all
select
GROUPMEMBER.MEMBERID as CONSTITUENTID,
case when GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM < @STARTDATE then @STARTDATE else GROUPMEMBERDATERANGE.DATEFROM end STARTDATE,
case when GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @ENDDATE then @ENDDATE else GROUPMEMBERDATERANGE.DATETO end ENDDATE
from dbo.GROUPMEMBER
left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
where
GROUPMEMBER.GROUPID = @CONSTITUENTID;
'
set @SQL = @SQL + '
insert into #TMP_DATA_REVENUEHISTORY_REVENUE(
ID
,CONSTITUENTID
,TRANSACTIONCURRENCYID
,[DATE]
,TYPECODE
,TYPE)
select
REVENUE.ID
,REVENUE.CONSTITUENTID
,REVENUE.TRANSACTIONCURRENCYID
,REVENUE.CALCULATEDDATE
,REVENUE.TYPECODE
,REVENUE.TYPE' + char(13);
if @INCLUDEMEMBERGIVING = 1
set @SQL = @SQL + '
from #TMP_DATA_REVENUEHISTORY_CONSTITUENTS CONSTITS
inner join dbo.FINANCIALTRANSACTION REVENUE on CONSTITS.CONSTITUENTID = REVENUE.CONSTITUENTID
where (CONSTITS.STARTDATE is null or REVENUE.CALCULATEDDATE >= CONSTITS.STARTDATE)
and (CONSTITS.ENDDATE is null or REVENUE.CALCULATEDDATE <= CONSTITS.ENDDATE)
and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
and REVENUE.CALCULATEDDATE between @STARTDATE and @ENDDATE;' + char(13);
else
set @SQL = @SQL + '
from dbo.FINANCIALTRANSACTION REVENUE
where REVENUE.CONSTITUENTID = @CONSTITUENTID
and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
and REVENUE.CALCULATEDDATE between @STARTDATE and @ENDDATE;' + char(13);
set @SQL = @SQL + '
insert into #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2(
ID
,REVENUEID
,CONSTITUENTID
,TRANSACTIONCURRENCYID
,[DATE]
,TYPECODE
,TYPE
,APPLICATIONCODE
,APPLICATION
,TRANSACTIONAMOUNT
,BASEAMOUNT
,ORGAMOUNT
,SPLIT_TYPECODE
,SPLIT_TYPE
,DESIGNATIONID
,VISIBLE)
select
REVENUESPLIT.ID
,REVENUESPLIT.FINANCIALTRANSACTIONID
,REVENUE.CONSTITUENTID
,REVENUE.TRANSACTIONCURRENCYID
,REVENUE.[DATE]
,REVENUE.TYPECODE
,REVENUE.TYPE
,REVENUESPLIT_EXT.APPLICATIONCODE
,REVENUESPLIT_EXT.APPLICATION
,REVENUESPLIT.TRANSACTIONAMOUNT
,REVENUESPLIT.BASEAMOUNT
,REVENUESPLIT.ORGAMOUNT
,REVENUESPLIT_EXT.TYPECODE
,REVENUESPLIT_EXT.TYPE
,REVENUESPLIT_EXT.DESIGNATIONID
,REVENUESPLIT.VISIBLE
from #TMP_DATA_REVENUEHISTORY_REVENUE REVENUE
inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID' + char(13);
if @REVENUEFILTERID is not null
begin
declare @REVENUEFILTERTABLE nvarchar(250) = dbo.[UFN_REVENUEFILTER_GETFUNCTIONNAME](@REVENUEFILTERID)
set @SQL = @SQL + 'inner join '+ @REVENUEFILTERTABLE +' FILTERED on REVENUESPLIT.ID = FILTERED.ID ' + char(13);
end
set @SQL = @SQL + '
where
REVENUESPLIT.DELETEDON is null' + char(13);
if @CAMPAIGNFILTERMODE != 0
begin
set @SQL = @SQL + 'and exists (
select top 1 1
from dbo.REVENUESPLITCAMPAIGN
inner join @CAMPAIGNFILTERTABLE CAMPAIGNFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNFILTER.ID
where REVENUESPLITCAMPAIGN.REVENUESPLITID = REVENUESPLIT.ID
)' + 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(REVENUESPLIT.ID) REVSITES
inner join #TMP_CONSTITUENT_EXPANDED_REVENUEHISTORY_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(REVENUESPLIT.ID) REVSITES
inner join #TMP_CONSTITUENT_EXPANDED_REVENUEHISTORY_SITEONFEATURE FEATURE on isnull(FEATURE.SITEID, ''00000000-0000-0000-0000-000000000000'') = isnull(REVSITES.SITEID, ''00000000-0000-0000-0000-000000000000'')
inner join #TMP_CONSTITUENT_EXPANDED_REVENUEHISTORY_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(REVENUESPLIT.ID) REVSITES
inner join #TMP_CONSTITUENT_EXPANDED_REVENUEHISTORY_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
,@REVENUEFILTERID uniqueidentifier
,@STARTDATE datetime
,@ENDDATE datetime
,@CAMPAIGNSSELECTED xml
,@CURRENTAPPUSERID uniqueidentifier
,@SECURITYFEATUREID uniqueidentifier
,@SECURITYFEATURETYPE tinyint
,@SITEFILTERMODE tinyint
,@SITESSELECTED xml'
,@CONSTITUENTID
,@REVENUEFILTERID
,@STARTDATE
,@ENDDATE
,@CAMPAIGNSSELECTED
,@CURRENTAPPUSERID
,@SECURITYFEATUREID
,@SECURITYFEATURETYPE
,@SITEFILTERMODE
,@SITESSELECTED;
if @GROUPBY = 0
and @REVENUEFILTERID is null
and @TRANSACTIONTYPEOPTIONCODE is null
begin
--Add Other People's Pledge like transactions
set @SQL = '
insert into #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 (
ID
,REVENUEID
,EXTRA
,CONSTITUENTID
,TRANSACTIONCURRENCYID
,[DATE]
,TYPECODE
,TYPE
,APPLICATIONCODE
,APPLICATION
,TRANSACTIONAMOUNT
,BASEAMOUNT
,ORGAMOUNT
,SPLIT_TYPECODE
,SPLIT_TYPE
,DESIGNATIONID
,VISIBLE
)
select distinct REVENUESPLIT.ID SPLITID
,REVENUESPLIT.FINANCIALTRANSACTIONID
,1
,REVENUE.CONSTITUENTID
,REVENUE.TRANSACTIONCURRENCYID
,REVENUE.CALCULATEDDATE
,REVENUE.TYPECODE
,REVENUE.TYPE
,REVENUESPLIT_EXT.APPLICATIONCODE
,REVENUESPLIT_EXT.APPLICATION
,REVENUESPLIT.TRANSACTIONAMOUNT
,REVENUESPLIT.BASEAMOUNT
,REVENUESPLIT.ORGAMOUNT
,REVENUESPLIT_EXT.TYPECODE
,REVENUESPLIT_EXT.TYPE
,REVENUESPLIT_EXT.DESIGNATIONID
,REVENUESPLIT.VISIBLE
from #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 FILTERED
inner join dbo.INSTALLMENTSPLITPAYMENT on FILTERED.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.FINANCIALTRANSACTIONID = INSTALLMENTSPLITPAYMENT.PLEDGEID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
where
REVENUESPLIT.ID not in (select INCLUDED.ID from #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 INCLUDED)
and REVENUE.CALCULATEDDATE between @STARTDATE and @ENDDATE
and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.TYPECODE <> 1' + char(13);
if @REVENUETYPEOPTIONCODE is not null
set @SQL = @SQL + '
and REVENUESPLIT_EXT.TYPECODE = @REVENUETYPEOPTIONCODE' + char(13);
if @INCLUDEMEMBERGIVING = 1
set @SQL = @SQL + '
and (
exists (
select G.GROUPMEMBERID
from dbo.GROUPMEMBERDATERANGE G
inner join dbo.GROUPMEMBER GM on G.GROUPMEMBERID = GM.ID
where GM.MEMBERID = REVENUE.CONSTITUENTID
and (
(
G.DATETO is not null
and REVENUE.CALCULATEDDATE < G.DATETO
)
or (G.DATETO is null)
)
)
)' + char(13);
--Add Other People's RG transactions
set @SQL = @SQL + '
insert into #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 (
ID
,REVENUEID
,EXTRA
,CONSTITUENTID
,TRANSACTIONCURRENCYID
,[DATE]
,TYPECODE
,TYPE
,APPLICATIONCODE
,APPLICATION
,TRANSACTIONAMOUNT
,BASEAMOUNT
,ORGAMOUNT
,SPLIT_TYPECODE
,SPLIT_TYPE
,DESIGNATIONID
,VISIBLE
)
select distinct REVENUESPLIT.ID SPLITID
,REVENUESPLIT.FINANCIALTRANSACTIONID
,1
,REVENUE.CONSTITUENTID
,REVENUE.TRANSACTIONCURRENCYID
,REVENUE.CALCULATEDDATE
,REVENUE.TYPECODE
,REVENUE.TYPE
,REVENUESPLIT_EXT.APPLICATIONCODE
,REVENUESPLIT_EXT.APPLICATION
,REVENUESPLIT.TRANSACTIONAMOUNT
,REVENUESPLIT.BASEAMOUNT
,REVENUESPLIT.ORGAMOUNT
,REVENUESPLIT_EXT.TYPECODE
,REVENUESPLIT_EXT.TYPE
,REVENUESPLIT_EXT.DESIGNATIONID
,REVENUESPLIT.VISIBLE
from #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 FILTERED
inner join dbo.RECURRINGGIFTACTIVITY on FILTERED.ID = RECURRINGGIFTACTIVITY.PAYMENTREVENUEID
inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.FINANCIALTRANSACTIONID = RECURRINGGIFTACTIVITY.SOURCEREVENUEID
inner join REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
where REVENUESPLIT.ID not in (select INCLUDED.ID from #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 INCLUDED)
and REVENUE.CALCULATEDDATE between @STARTDATE and @ENDDATE
and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.TYPECODE <> 1' + char(13);
if @REVENUETYPEOPTIONCODE is not null
set @SQL = @SQL + '
and REVENUESPLIT_EXT.TYPECODE = @REVENUETYPEOPTIONCODE' + char(13);
if @INCLUDEMEMBERGIVING = 1
set @SQL = @SQL + '
and (
exists (
select G.GROUPMEMBERID
from dbo.GROUPMEMBERDATERANGE G
inner join dbo.GROUPMEMBER GM on G.GROUPMEMBERID = GM.ID
where GM.MEMBERID = REVENUE.CONSTITUENTID
and (
(
G.DATETO is not null
and REVENUE.CALCULATEDDATE < G.DATETO
)
or (G.DATETO is null)
)
)
)' + char(13);
--Add pledge like payments by others
set @SQL = @SQL + '
insert into #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 (
ID
,REVENUEID
,EXTRA
,CONSTITUENTID
,TRANSACTIONCURRENCYID
,[DATE]
,TYPECODE
,TYPE
,APPLICATIONCODE
,APPLICATION
,TRANSACTIONAMOUNT
,BASEAMOUNT
,ORGAMOUNT
,SPLIT_TYPECODE
,SPLIT_TYPE
,DESIGNATIONID
,VISIBLE
)
select distinct REVENUESPLIT.ID SPLITID
,REVENUESPLIT.FINANCIALTRANSACTIONID
,1
,REVENUE.CONSTITUENTID
,REVENUE.TRANSACTIONCURRENCYID
,REVENUE.CALCULATEDDATE
,REVENUE.TYPECODE
,REVENUE.TYPE
,REVENUESPLIT_EXT.APPLICATIONCODE
,REVENUESPLIT_EXT.APPLICATION
,REVENUESPLIT.TRANSACTIONAMOUNT
,REVENUESPLIT.BASEAMOUNT
,REVENUESPLIT.ORGAMOUNT
,REVENUESPLIT_EXT.TYPECODE
,REVENUESPLIT_EXT.TYPE
,REVENUESPLIT_EXT.DESIGNATIONID
,REVENUESPLIT.VISIBLE
from #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 FILTERED
inner join dbo.INSTALLMENTSPLITPAYMENT on FILTERED.REVENUEID = INSTALLMENTSPLITPAYMENT.PLEDGEID
inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
where REVENUESPLIT.ID not in (select INCLUDED.ID from #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 INCLUDED)
and REVENUE.CALCULATEDDATE between @STARTDATE and @ENDDATE
and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.TYPECODE <> 1' + char(13);
if @REVENUETYPEOPTIONCODE is not null
set @SQL = @SQL + '
and REVENUESPLIT_EXT.TYPECODE = @REVENUETYPEOPTIONCODE' + char(13);
if @INCLUDEMEMBERGIVING = 1
set @SQL = @SQL + '
and (
exists (
select G.GROUPMEMBERID
from dbo.GROUPMEMBERDATERANGE G
inner join dbo.GROUPMEMBER GM on G.GROUPMEMBERID = GM.ID
where GM.MEMBERID = REVENUE.CONSTITUENTID
and (
(
G.DATETO is not null
and REVENUE.CALCULATEDDATE < G.DATETO
)
or (G.DATETO is null)
)
)
)' + char(13);
--Add RG payments by others
set @SQL = @SQL + '
insert into #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 (
ID
,REVENUEID
,EXTRA
,CONSTITUENTID
,TRANSACTIONCURRENCYID
,[DATE]
,TYPECODE
,TYPE
,APPLICATIONCODE
,APPLICATION
,TRANSACTIONAMOUNT
,BASEAMOUNT
,ORGAMOUNT
,SPLIT_TYPECODE
,SPLIT_TYPE
,DESIGNATIONID
,VISIBLE
)
select distinct REVENUESPLIT.ID SPLITID
,REVENUESPLIT.FINANCIALTRANSACTIONID
,1
,REVENUE.CONSTITUENTID
,REVENUE.TRANSACTIONCURRENCYID
,REVENUE.CALCULATEDDATE
,REVENUE.TYPECODE
,REVENUE.TYPE
,REVENUESPLIT_EXT.APPLICATIONCODE
,REVENUESPLIT_EXT.APPLICATION
,REVENUESPLIT.TRANSACTIONAMOUNT
,REVENUESPLIT.BASEAMOUNT
,REVENUESPLIT.ORGAMOUNT
,REVENUESPLIT_EXT.TYPECODE
,REVENUESPLIT_EXT.TYPE
,REVENUESPLIT_EXT.DESIGNATIONID
,REVENUESPLIT.VISIBLE
from #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 FILTERED
inner join dbo.RECURRINGGIFTACTIVITY on RECURRINGGIFTACTIVITY.SOURCEREVENUEID = FILTERED.REVENUEID
inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = RECURRINGGIFTACTIVITY.PAYMENTREVENUEID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
where REVENUESPLIT.ID not in (select INCLUDED.ID from #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 INCLUDED)
and REVENUE.CALCULATEDDATE between @STARTDATE and @ENDDATE
and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.TYPECODE <> 1' + char(13);
if @REVENUETYPEOPTIONCODE is not null
set @SQL = @SQL + '
and REVENUESPLIT_EXT.TYPECODE = @REVENUETYPEOPTIONCODE' + char(13);
--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(REVENUESPLIT.ID) REVSITES
inner join #TMP_CONSTITUENT_EXPANDED_REVENUEHISTORY_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(REVENUESPLIT.ID) REVSITES
inner join #TMP_CONSTITUENT_EXPANDED_REVENUEHISTORY_SITEONFEATURE FEATURE on isnull(FEATURE.SITEID, ''00000000-0000-0000-0000-000000000000'') = isnull(REVSITES.SITEID, ''00000000-0000-0000-0000-000000000000'')
inner join #TMP_CONSTITUENT_EXPANDED_REVENUEHISTORY_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(REVENUESPLIT.ID) REVSITES
inner join #TMP_CONSTITUENT_EXPANDED_REVENUEHISTORY_SITEFILTER FILTER on isnull(FILTER.SITEID, ''00000000-0000-0000-0000-000000000000'') = isnull(REVSITES.SITEID, ''00000000-0000-0000-0000-000000000000'')) '
end
if @INCLUDEMEMBERGIVING = 1
set @SQL = @SQL + '
and (
exists (
select G.GROUPMEMBERID
from dbo.GROUPMEMBERDATERANGE G
inner join dbo.GROUPMEMBER GM on G.GROUPMEMBERID = GM.ID
where GM.MEMBERID = REVENUE.CONSTITUENTID
and (
(
G.DATETO is not null
and REVENUE.CALCULATEDDATE < G.DATETO
)
or (G.DATETO is null)
)
)
);' + char(13);
exec sp_executesql @SQL
,N'@REVENUETYPEOPTIONCODE tinyint
,@STARTDATE datetime
,@ENDDATE datetime
,@CURRENTAPPUSERID uniqueidentifier
,@SECURITYFEATUREID uniqueidentifier
,@SECURITYFEATURETYPE tinyint
,@SITEFILTERMODE tinyint
,@SITESSELECTED xml'
,@REVENUETYPEOPTIONCODE
,@STARTDATE
,@ENDDATE
,@CURRENTAPPUSERID
,@SECURITYFEATUREID
,@SECURITYFEATURETYPE
,@SITEFILTERMODE
,@SITESSELECTED;
end
--Get Write-Offs
if object_id('tempdb..#TMP_DATA_REVENUEHISTORY_WRITEOFFSPLITS') is not null
drop table #TMP_DATA_REVENUEHISTORY_WRITEOFFSPLITS;
create table #TMP_DATA_REVENUEHISTORY_WRITEOFFSPLITS (
WRITEOFFID uniqueidentifier
,WRITEOFFSPLITID uniqueidentifier
,REVENUEID uniqueidentifier
,AMOUNT money
,[DATE] datetime
,DISPLAYCURRENCY uniqueidentifier
,WRITEOFFTYPE tinyint);
if @REVENUETYPEOPTIONCODE is null
and coalesce(@TRANSACTIONTYPEOPTIONCODE, - 1) in (
- 1
,90
)
begin
insert into #TMP_DATA_REVENUEHISTORY_WRITEOFFSPLITS (
WRITEOFFID
,WRITEOFFSPLITID
,REVENUEID
,AMOUNT
,[DATE]
,DISPLAYCURRENCY
,WRITEOFFTYPE)
select distinct WRITEOFFSPLIT.FINANCIALTRANSACTIONID
,WRITEOFFSPLIT.ID
,FILTERED.REVENUEID
,case @CURRENCYCODE
when 0
then WRITEOFFSPLIT.BASEAMOUNT
when 1
then WRITEOFFSPLIT.ORGAMOUNT
when 2
then WRITEOFFSPLIT.TRANSACTIONAMOUNT
else
case when @CURRENCYID = FILTERED.TRANSACTIONCURRENCYID or @MULTICURRENCYENABLED = 0
then WRITEOFFSPLIT.TRANSACTIONAMOUNT
else dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WRITEOFFSPLIT.ID, @CURRENCYID)
end
end as AMOUNT
,WRITEOFF.CALCULATEDDATE [DATE]
,case @CURRENCYCODE
when 0
then case when @MULTICURRENCYENABLED = 1
then (select top 1 B.BASECURRENCYID from dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I B where B.FINANCIALTRANSACTIONID = FILTERED.REVENUEID)
else @ORGANIZATIONCURRENCYID
end
when 1
then @ORGANIZATIONCURRENCYID
when 2
then FILTERED.TRANSACTIONCURRENCYID
else @CURRENCYID
end as DISPLAYCURRENCY
,0 -- Pledge
from #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 FILTERED
inner join dbo.FINANCIALTRANSACTION as WRITEOFF on WRITEOFF.PARENTID = FILTERED.REVENUEID and WRITEOFF.TYPECODE = 20
inner join dbo.WRITEOFF_EXT on WRITEOFF.ID = WRITEOFF_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as WRITEOFFSPLIT on WRITEOFFSPLIT.FINANCIALTRANSACTIONID = WRITEOFF.ID
where WRITEOFF.CALCULATEDDATE between @STARTDATE and @ENDDATE
and WRITEOFFSPLIT.DELETEDON is null
and WRITEOFFSPLIT.TYPECODE != 1
and (
@INCLUDEMEMBERGIVING = 0
or (
@INCLUDEMEMBERGIVING = 1
and ((
exists (
select G.GROUPMEMBERID
from dbo.GROUPMEMBERDATERANGE G
inner join dbo.GROUPMEMBER GM on G.GROUPMEMBERID = GM.ID
where GM.MEMBERID = FILTERED.CONSTITUENTID
and (
(
G.DATETO is not null
and FILTERED.[DATE] < G.DATETO
)
or (G.DATETO is null)
)
)
)
or FILTERED.CONSTITUENTID = @CONSTITUENTID
)
)
);
insert into #TMP_DATA_REVENUEHISTORY_WRITEOFFSPLITS (
WRITEOFFID
,WRITEOFFSPLITID
,REVENUEID
,AMOUNT
,[DATE]
,DISPLAYCURRENCY
,WRITEOFFTYPE
)
select distinct iw.WRITEOFFID
,iw.ID
,FILTERED.REVENUEID
,case
when @CURRENCYCODE = 2 or @MULTICURRENCYENABLED = 0 or (@CURRENCYCODE = 3 and @CURRENCYID = iw.TRANSACTIONCURRENCYID)
then case w.TYPECODE when 0 then iw.TRANSACTIONAMOUNT else i.TRANSACTIONAMOUNT end
when @CURRENCYCODE = 0 or (@CURRENCYCODE = 3 and @CURRENCYID = iw.BASECURRENCYID)
then case w.TYPECODE when 0 then iw.AMOUNT else i.AMOUNT end
when @CURRENCYCODE = 1
then case w.TYPECODE when 0 then iw.ORGANIZATIONAMOUNT else i.ORGANIZATIONAMOUNT end
else -- My base doesn't match any of the record's currencies
isnull(nullif(dbo.UFN_CURRENCY_CONVERT(case w.TYPECODE when 0 then iw.ORGANIZATIONAMOUNT else i.ORGANIZATIONAMOUNT end, dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(@ORGANIZATIONCURRENCYID, @CURRENCYID, iw.DATEADDED, 1, null)),0),
dbo.UFN_CURRENCY_CONVERTINVERSE(case w.TYPECODE when 0 then iw.ORGANIZATIONAMOUNT else i.ORGANIZATIONAMOUNT end, dbo.UFN_CURRENCYEXCHANGERATE_GETLATESTINCLUDEEXPIRED(@CURRENCYID, @ORGANIZATIONCURRENCYID, iw.DATEADDED, 1, null)))
end as AMOUNT
,w.[DATE]
,case @CURRENCYCODE
when 0
then iw.BASECURRENCYID
when 1
then @ORGANIZATIONCURRENCYID
when 2
then iw.TRANSACTIONCURRENCYID
else @CURRENCYID
end as DISPLAYCURRENCY
,case w.TYPECODE when 0 then 1 else 2 end -- Recurring gift, 1=write-off, 2=skip
from #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 FILTERED
inner join dbo.RECURRINGGIFTWRITEOFF w on w.REVENUEID = FILTERED.REVENUEID
inner join dbo.RECURRINGGIFTINSTALLMENTWRITEOFF iw on iw.WRITEOFFID = w.ID
inner join dbo.RECURRINGGIFTINSTALLMENT i on i.ID = iw.RECURRINGGIFTINSTALLMENTID
where cast(w.[DATE] as datetime) between @STARTDATE and @ENDDATE
and (
@INCLUDEMEMBERGIVING = 0
or (
@INCLUDEMEMBERGIVING = 1
and ((
exists (
select G.GROUPMEMBERID
from dbo.GROUPMEMBERDATERANGE G
inner join dbo.GROUPMEMBER GM on G.GROUPMEMBERID = GM.ID
where GM.MEMBERID = FILTERED.CONSTITUENTID
and (
(
G.DATETO is not null
and FILTERED.[DATE] < G.DATETO
)
or (G.DATETO is null)
)
)
)
or FILTERED.CONSTITUENTID = @CONSTITUENTID
)
)
);
end
--Get Refunds
declare @REFUNDSPLITS table (
CREDITID uniqueidentifier
,CREDITITEMID uniqueidentifier
,REVENUESPLITID uniqueidentifier
,PARENTID uniqueidentifier
,AMOUNT money
,[DATE] datetime
,DISPLAYCURRENCY uniqueidentifier
);
if @REVENUETYPEOPTIONCODE is null
and coalesce(@TRANSACTIONTYPEOPTIONCODE, -1) in (-1, 100)
and dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('BB1C17BC-9E0B-4683-B490-EE40D511FA05') = 1 --basic programs is installed
begin
-- refunds only
with CreditItem_Cte as
(
select
C.ID
, C.CREDITID
, C.SALESORDERID
, C.SALESORDERITEMID
, LI.SOURCELINEITEMID [REVENUESPLITID]
, ((LI.QUANTITY * LI.UNITVALUE) - C.DISCOUNTS) [TOTAL]
, C.TYPE
, C.TYPECODE
from dbo.CREDITITEM_EXT C
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on C.ID = LI.ID
inner join dbo.FINANCIALTRANSACTION FT on FT.ID = LI.FINANCIALTRANSACTIONID
inner join dbo.CREDIT_EXT CREDIT on FT.ID = CREDIT.ID
)
insert into @REFUNDSPLITS (
CREDITID
,CREDITITEMID
,REVENUESPLITID
,PARENTID
,AMOUNT
,[DATE]
,DISPLAYCURRENCY
)
select CREDIT.ID
,CREDITITEM.ID
,CREDITITEM.REVENUESPLITID
,SALESORDER.REVENUEID
,CREDITITEM.TOTAL - coalesce(sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT), 0.00)
,CREDITFT.DATE [DATE]
,@ORGANIZATIONCURRENCYID /*currency not supported*/
from dbo.SALESORDER
inner join dbo.SALESORDERITEM on SALESORDER.ID = SALESORDERITEM.SALESORDERID
inner join CreditItem_Cte CREDITITEM on CREDITITEM.SALESORDERITEMID = SALESORDERITEM.ID
inner join dbo.CREDIT_EXT CREDIT on CREDITITEM.CREDITID = CREDIT.ID
inner join dbo.FINANCIALTRANSACTION CREDITFT on CREDIT.ID = CREDITFT.ID
left join dbo.SALESORDERITEMORDERDISCOUNTDETAIL on CREDITITEM.SALESORDERITEMID = SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID
where SALESORDER.REVENUEID in
(
select REVENUESPLIT.FINANCIALTRANSACTIONID
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 FILTERED on REVENUESPLIT.ID = FILTERED.ID
where
(
CREDITITEM.TYPE = REVENUESPLIT_EXT.TYPE
or CREDITITEM.TYPECODE = 2
)
and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.TYPECODE <> 1
)
and CREDITFT.DATE between @STARTDATE and @ENDDATE
and
(
@INCLUDEMEMBERGIVING = 0
or
(
@INCLUDEMEMBERGIVING = 1
and
(
exists
(
select G.GROUPMEMBERID
from dbo.GROUPMEMBERDATERANGE G
inner join dbo.GROUPMEMBER GM on G.GROUPMEMBERID = GM.ID
where GM.MEMBERID = CREDITFT.CONSTITUENTID
and
(
(
G.DATETO is not null
and cast(CREDITFT.[DATE] as datetime) < G.DATETO
)
or G.DATETO is null
)
)
)
)
)
group by CREDIT.ID
,CREDITITEM.ID
,CREDITITEM.REVENUESPLITID
,SALESORDER.REVENUEID
,CREDITITEM.TOTAL
,CREDITFT.DATE
union all -- Group Sales Refunds
select FINANCIALTRANSACTION.ID
,FTLI.ID
,FTLI.SOURCELINEITEMID
,SOURCELI.ID
,FINANCIALTRANSACTION.TRANSACTIONAMOUNT
,FINANCIALTRANSACTION.[DATE]
,@ORGANIZATIONCURRENCYID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM SOURCELI on SOURCELI.ID = FTLI.SOURCELINEITEMID
inner join dbo.REVENUESPLIT_EXT SOURCELI_EXT on SOURCELI_EXT.ID = SOURCELI.ID
inner join dbo.CREDIT_EXT on CREDIT_EXT.ID = FINANCIALTRANSACTION.ID
inner join dbo.SALESORDER on SALESORDER.ID = CREDIT_EXT.SALESORDERID
where FINANCIALTRANSACTION.TYPECODE = 23 -- Refund
and convert(date, FINANCIALTRANSACTION.[DATE]) between @STARTDATE and @ENDDATE
and SOURCELI_EXT.TYPECODE in (13,19) -- Unearned Revenue
and SALESORDER.CONSTITUENTID = @CONSTITUENTID
and FINANCIALTRANSACTION.DELETEDON is null
union all --Memberships don't have a salesorder and aren't unearned so this should pick up refunds not in the two above sections
select FINANCIALTRANSACTION.ID
,FTLI.ID
,FTLI.SOURCELINEITEMID
,SOURCELI.FINANCIALTRANSACTIONID
,FTLI.TRANSACTIONAMOUNT
,FINANCIALTRANSACTION.[DATE]
,@ORGANIZATIONCURRENCYID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM SOURCELI on SOURCELI.ID = FTLI.SOURCELINEITEMID
inner join dbo.REVENUESPLIT_EXT SOURCELI_EXT on SOURCELI_EXT.ID = SOURCELI.ID
where FINANCIALTRANSACTION.TYPECODE = 23 -- Refund
and convert(date, FINANCIALTRANSACTION.[DATE]) between @STARTDATE and @ENDDATE
and FINANCIALTRANSACTION.CONSTITUENTID = @CONSTITUENTID
and FINANCIALTRANSACTION.DELETEDON is null
and not exists (select 1 from dbo.SALESORDER where SALESORDER.REVENUEID = SOURCELI.FINANCIALTRANSACTIONID)
and SOURCELI_EXT.TYPECODE not in (13,19)
end;
--Get Discounts
declare @CREDITSPLITS table (
CREDITID uniqueidentifier
,CREDITITEMID uniqueidentifier
,REVENUESPLITID uniqueidentifier
,REVENUEID uniqueidentifier
,AMOUNT money
,[DATE] datetime
,DISPLAYCURRENCY uniqueidentifier
);
if @REVENUETYPEOPTIONCODE is null
and coalesce(@TRANSACTIONTYPEOPTIONCODE, - 1) in (
- 1
,110
)
and dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('BB1C17BC-9E0B-4683-B490-EE40D511FA05') = 1 --basic programs is installed
begin
with CreditItem_Cte as
(
select
C.ID
, C.CREDITID
, C.SALESORDERID
, C.SALESORDERITEMID
, LI.SOURCELINEITEMID [REVENUESPLITID]
, ((LI.QUANTITY * LI.UNITVALUE) - C.DISCOUNTS) [TOTAL]
, C.TYPE
, c.TYPECODE
from dbo.CREDITITEM_EXT C
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on C.ID = LI.ID
inner join dbo.FINANCIALTRANSACTION FT on LI.FINANCIALTRANSACTIONID = FT.ID
where
LI.TYPECODE = 5 and FT.TYPECODE in (5, 99) -- discounts only
)
insert into @CREDITSPLITS (
CREDITID
,CREDITITEMID
,REVENUESPLITID
,REVENUEID
,AMOUNT
,[DATE]
,DISPLAYCURRENCY
)
select CREDIT.ID
,CREDITITEM.ID
,CREDITITEM.REVENUESPLITID
,REVENUESPLIT.FINANCIALTRANSACTIONID
,CREDITITEM.TOTAL
,REVENUE.[DATE]
,@ORGANIZATIONCURRENCYID /*currency not supported*/
from #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 FILTERED
inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = FILTERED.ID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
--inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
inner join CreditItem_Cte CREDITITEM on CREDITITEM.REVENUESPLITID = REVENUESPLIT.ID
inner join dbo.CREDIT_EXT CREDIT on CREDITITEM.CREDITID = CREDIT.ID
inner join dbo.FINANCIALTRANSACTION CREDITFT on CREDIT.ID = CREDITFT.ID
where
REVENUE.DELETEDON is null
and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.TYPECODE <> 1
and (
@INCLUDEMEMBERGIVING = 0
or (
@INCLUDEMEMBERGIVING = 1
and exists (
select G.GROUPMEMBERID
from dbo.GROUPMEMBERDATERANGE G
inner join dbo.GROUPMEMBER GM on G.GROUPMEMBERID = GM.ID
where GM.MEMBERID = REVENUE.CONSTITUENTID
and (
(
G.DATETO is not null
and cast(REVENUE.[DATE] as datetime) < G.DATETO
)
or (G.DATETO is null)
)
)
)
);
end;
if object_id('tempdb..#SPLITS_CTE') is not null
drop table #SPLITS_CTE;
create table #SPLITS_CTE (
SPLITID uniqueidentifier
,REVENUEID uniqueidentifier
,COMMITMENTREVENUEID uniqueidentifier
,AMOUNT money
,GROSSAMOUNT money
,BALANCE money
,TRANSACTIONTYPECODE int
,TRANSACTIONTYPE nvarchar(27) collate database_default
,APPLICATION nvarchar(700) collate database_default
,REVENUETYPE nvarchar(27) collate database_default
,REVENUETYPECODE int
,DESIGNATIONID uniqueidentifier
,REVENUESPLITDESCRIPTION nvarchar(700) collate database_default
,TRANSACTIONCURRENCYID uniqueidentifier
,CONSTITUENTID uniqueidentifier
,[DATE] datetime
,PAYMENTMETHOD nvarchar(100) collate database_default
);
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
);
set @SQL = '
insert into #SPLITS_CTE
select FILTERED.ID SPLITID
,FILTERED.REVENUEID as REVENUEID
,case
when @GROUPBY = 0 and FILTERED.APPLICATIONCODE in (0, 1, 4, 5, 9, 11, 12, 15, 16) then null --Values from function below that return null
when @GROUPBY = 0 and FILTERED.APPLICATIONCODE in (2, 3, 6, 7, 8, 10, 13, 19) then dbo.UFN_REVENUESPLIT_GETCOMMITMENTID(FILTERED.ID, FILTERED.APPLICATIONCODE)
else null
end COMMITMENTREVENUEID
,case
when @CURRENCYCODE = 2
then FILTERED.TRANSACTIONAMOUNT
when @CURRENCYCODE = 0
then FILTERED.BASEAMOUNT
when @CURRENCYID = FILTERED.TRANSACTIONCURRENCYID
then FILTERED.TRANSACTIONAMOUNT
when @CURRENCYID = @ORGANIZATIONCURRENCYID
then FILTERED.ORGAMOUNT
else (
select top 1 REVENUESPLITINCURRENCY.AMOUNTINCURRENCY
from dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE) as REVENUESPLITINCURRENCY
where FILTERED.ID = REVENUESPLITINCURRENCY.ID
)
end AMOUNT' + char(13);
if @ISUK = 1
set @SQL = @SQL + '
,case
when @ISUK = 0
then null
else case @CURRENCYCODE
when 0
then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(FILTERED.ID, 1, isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, (select top 1 B.BASECURRENCYID from dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I B where B.FINANCIALTRANSACTIONID = FILTERED.REVENUEID)))
when 2
then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(FILTERED.ID, 1, FILTERED.TRANSACTIONCURRENCYID)
else dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(FILTERED.ID, 1, @CURRENCYID)
end
end GROSSAMOUNT' + char(13);
else
set @SQL = @SQL + ',null as GROSSAMOUNT' + char(13);
set @SQL = @SQL + '
,case
when FILTERED.TYPECODE in (
1
,--Pledge
3
,--Matching gift claim
4
,--Planned gift
6
,--Grant award
8
,--Donor challenge claim
9
,--Pending Gift
15 --Membership installment plan
)
then
case when @MULTICURRENCYENABLED = 0 or @CURRENCYID = FILTERED.TRANSACTIONCURRENCYID
then dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCE_NO_MULTICURRENCY(FILTERED.ID, @CURRENTDATE)
else
(
select REVENUESPLITBALANCE.BALANCE
from dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY(FILTERED.REVENUEID, @CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @CURRENCYROUNDINGTYPECODE, @CURRENTDATE, @ORIGINCODE, @CURRENCYCODE) as REVENUESPLITBALANCE
where REVENUESPLITBALANCE.ID = FILTERED.ID
)
end
else null
end as BALANCE
,FILTERED.TYPECODE as TRANSACTIONTYPECODE
,FILTERED.TYPE as TRANSACTIONTYPE
,FILTERED.APPLICATION
,FILTERED.SPLIT_TYPE REVENUETYPE
,FILTERED.SPLIT_TYPECODE REVENUETYPECODE
,FILTERED.DESIGNATIONID
,cast(''('' + FILTERED.APPLICATION + '') '' + cast(FILTERED.TRANSACTIONAMOUNT as nvarchar(20)) + '' '' + coalesce(REVENUEPAYMENTMETHOD.PAYMENTMETHOD, ''None'') + '' - '' + ltrim(rtrim(CONSTITUENT.[NAME])) as nvarchar(700)) as REVENUESPLITDESCRIPTION
,FILTERED.TRANSACTIONCURRENCYID
,FILTERED.CONSTITUENTID
,FILTERED.[DATE]
,REVENUEPAYMENTMETHOD.PAYMENTMETHOD
from #TMP_DATA_REVENUEHISTORY_REVENUESPLITIDS2 FILTERED' + char(13);
if @ISUK = 1
set @SQL = @SQL + '
inner join dbo.REVENUE_EXT on FILTERED.REVENUEID = REVENUE_EXT.ID' + char(13);
set @SQL = @SQL + '
left join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = FILTERED.REVENUEID
left join dbo.CONSTITUENT on FILTERED.CONSTITUENTID = CONSTITUENT.ID
where (
FILTERED.APPLICATIONCODE <> 10 /*order*/
or FILTERED.VISIBLE = 1
)' + char(13);
if @TRANSACTIONTYPEOPTIONCODE is not null
set @SQL = @SQL + ' and FILTERED.TYPECODE = @TRANSACTIONTYPEOPTIONCODE' + char(13);
if @REVENUETYPEOPTIONCODE is not null
set @SQL = @SQL + ' and FILTERED.SPLIT_TYPECODE = @REVENUETYPEOPTIONCODE' + char(13);
exec sp_executesql @SQL
,N'@GROUPBY tinyint, @CURRENCYCODE tinyint, @CURRENCYID uniqueidentifier, @ORGANIZATIONCURRENCYID uniqueidentifier, @CURRENCYDECIMALDIGITS tinyint, @CURRENCYROUNDINGTYPECODE tinyint, @REVENUETYPEOPTIONCODE tinyint, @TRANSACTIONTYPEOPTIONCODE tinyint, @ISUK bit, @CURRENTDATE datetime, @ORIGINCODE tinyint, @MULTICURRENCYENABLED bit'
,@GROUPBY
,@CURRENCYCODE
,@CURRENCYID
,@ORGANIZATIONCURRENCYID
,@CURRENCYDECIMALDIGITS
,@CURRENCYROUNDINGTYPECODE
,@REVENUETYPEOPTIONCODE
,@TRANSACTIONTYPEOPTIONCODE
,@ISUK
,@CURRENTDATE
,@ORIGINCODE
,@MULTICURRENCYENABLED;
insert into #DESIGNATIONS (DESIGNATIONID, DESIGNATIONNAME)
select DESIGNATIONID, DESIGNATION.NAME
from
(select distinct DESIGNATIONID from #SPLITS_CTE) V1
inner join dbo.DESIGNATION on V1.DESIGNATIONID = DESIGNATION.ID;
insert into #CAMPAIGNSBYSPLIT (REVENUESPLITID, CAMPAIGNNAME)
select T1.SPLITID, dbo.UDA_BUILDLIST(CAMPAIGN.[NAME])
from #SPLITS_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 #SPLITS_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..#REVENUEHISTORY_SUMMEDREVENUE_CTE') is not null
drop table #REVENUEHISTORY_SUMMEDREVENUE_CTE;
create table #REVENUEHISTORY_SUMMEDREVENUE_CTE (
SPLITID uniqueidentifier
,REVENUEID uniqueidentifier
,PARENTID uniqueidentifier
,APPLICATION nvarchar(max) collate database_default
,REVENUETYPE 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
,BALANCE money
,GROSSAMOUNT money
,TRANSACTIONCURRENCYID uniqueidentifier
,CONSTITUENTID uniqueidentifier
,[DATE] datetime
,[TYPE] nvarchar(100) collate database_default
,PAYMENTMETHOD nvarchar(100) collate database_default
,TRANSACTIONTYPE nvarchar(27) collate database_default
);
if @GROUPBY = 1
insert into #REVENUEHISTORY_SUMMEDREVENUE_CTE
select
null as SPLITID
,REVENUEID
,null as PARENTID
,dbo.UDA_BUILDLIST(distinct SPLITS_CTE.APPLICATION) APPLICATION
,dbo.UDA_BUILDLIST(distinct SPLITS_CTE.REVENUETYPE) REVENUETYPE
,null DESIGNATIONS
,null REVENUECATEGORIES
,null CAMPAIGNS
,null SITES
,sum(AMOUNT) AMOUNT
,sum(BALANCE) BALANCE
,sum(GROSSAMOUNT) GROSSAMOUNT
,TRANSACTIONCURRENCYID
,CONSTITUENTID
,SPLITS_CTE.[DATE]
,SPLITS_CTE.REVENUETYPE [TYPE]
,SPLITS_CTE.PAYMENTMETHOD
,SPLITS_CTE.TRANSACTIONTYPE
from #SPLITS_CTE SPLITS_CTE
group by REVENUEID, TRANSACTIONCURRENCYID, CONSTITUENTID,SPLITS_CTE.[DATE],SPLITS_CTE.REVENUETYPE,SPLITS_CTE.PAYMENTMETHOD,SPLITS_CTE.TRANSACTIONTYPE
having COUNT(1) > 1;
if @GROUPBY = 0
insert into #REVENUEHISTORY_SUMMEDREVENUE_CTE
select null as SPLITID
,SPLITS_CTE.REVENUEID
,null as PARENTID
,dbo.UDA_BUILDLIST(distinct SPLITS_CTE.APPLICATION) APPLICATION
,dbo.UDA_BUILDLIST(distinct SPLITS_CTE.REVENUETYPE) REVENUETYPE
,dbo.UDA_BUILDLIST(isnull (#DESIGNATIONS.DESIGNATIONNAME, REVENUESPLITDESCRIPTION)) DESIGNATIONS
,dbo.UDA_BUILDLIST(distinct GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME) REVENUECATEGORIES
,#CAMPAIGNSBYREV.CAMPAIGNNAME CAMPAIGNS
,
case @HASSITES when 1 then
(select dbo.UDA_BUILDLIST(SITE.[NAME])
from dbo.UFN_SITEID_MAPFROM_REVENUEID(SPLITS_CTE.REVENUEID) RSITE
inner join dbo.SITE on SITE.ID = RSITE.SITEID)
else '' end as SITES
,sum(AMOUNT) AMOUNT
,sum(BALANCE) BALANCE
,sum(GROSSAMOUNT) GROSSAMOUNT
,SPLITS_CTE.TRANSACTIONCURRENCYID
,SPLITS_CTE.CONSTITUENTID
,SPLITS_CTE.[DATE]
,SPLITS_CTE.REVENUETYPE [TYPE]
,SPLITS_CTE.PAYMENTMETHOD
,SPLITS_CTE.TRANSACTIONTYPE
from #SPLITS_CTE SPLITS_CTE
left join #DESIGNATIONS on SPLITS_CTE.DESIGNATIONID = #DESIGNATIONS.DESIGNATIONID
left join #CAMPAIGNSBYREV on SPLITS_CTE.REVENUEID = #CAMPAIGNSBYREV.REVENUEID
left join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = SPLITS_CTE.SPLITID
left join dbo.GLREVENUECATEGORYMAPPING on GLREVENUECATEGORYMAPPING.ID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID
where SPLITS_CTE.TRANSACTIONTYPECODE <> 0
group by SPLITS_CTE.REVENUEID, SPLITS_CTE.TRANSACTIONCURRENCYID, SPLITS_CTE.CONSTITUENTID,SPLITS_CTE.[DATE],SPLITS_CTE.REVENUETYPE,SPLITS_CTE.PAYMENTMETHOD, #CAMPAIGNSBYREV.CAMPAIGNNAME,SPLITS_CTE.TRANSACTIONTYPE;
insert into #REVENUEHISTORY_SUMMEDREVENUE_CTE
select
SPLITID
,SPLITS_CTE.REVENUEID
,case @GROUPBY
when 0
then COMMITMENTREVENUEID
when 1
then REVENUEID
else null
end as PARENTID
,SPLITS_CTE.APPLICATION
,SPLITS_CTE.REVENUETYPE
,isnull (#DESIGNATIONS.DESIGNATIONNAME, REVENUESPLITDESCRIPTION) DESIGNATIONS
,GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME REVENUECATEGORIES
,#CAMPAIGNSBYSPLIT.CAMPAIGNNAME CAMPAIGNS
,case @HASSITES when 1 then
(select dbo.UDA_BUILDLIST(SITE.[NAME])
from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(SPLITS_CTE.SPLITID) RSITE
inner join dbo.SITE on SITE.ID = RSITE.SITEID)
else '' end as SITES
,SPLITS_CTE.AMOUNT
,BALANCE
,GROSSAMOUNT
,SPLITS_CTE.TRANSACTIONCURRENCYID
,SPLITS_CTE.CONSTITUENTID
,SPLITS_CTE.[DATE]
,SPLITS_CTE.REVENUETYPE [TYPE]
,SPLITS_CTE.PAYMENTMETHOD
,SPLITS_CTE.TRANSACTIONTYPE
from #SPLITS_CTE SPLITS_CTE
left join #DESIGNATIONS on SPLITS_CTE.DESIGNATIONID = #DESIGNATIONS.DESIGNATIONID
left join #CAMPAIGNSBYSPLIT on SPLITS_CTE.SPLITID = #CAMPAIGNSBYSPLIT.REVENUESPLITID
left join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = SPLITS_CTE.SPLITID
left join dbo.GLREVENUECATEGORYMAPPING on GLREVENUECATEGORYMAPPING.ID = REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID
where (
SPLITS_CTE.TRANSACTIONTYPECODE = 0
or coalesce(@GROUPBY, - 1) <> 0
);
select coalesce(a.SPLITID, a.REVENUEID) ID
,a.SPLITID
,a.REVENUEID
,coalesce(a.SPLITID, a.REVENUEID) RECORDID
,a.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 a.SPLITID is null
then 'F989EA79-EA4D-4FC7-B3F8-6EA606125CCA'
else '5DBB948F-BE5E-4E85-B652-CA3AEDE26A4F'
end VIEWFORMID
,a.[DATE]
,a.AMOUNT
,a.GROSSAMOUNT
,a.BALANCE
,a.TRANSACTIONTYPE TRANSACTIONTYPE
,a.APPLICATION
,a.REVENUETYPE
,a.DESIGNATIONS
,a.REVENUECATEGORIES
,a.CAMPAIGNS
,a.PAYMENTMETHOD
,NF.[NAME]
,a.SITES
,case @CURRENCYCODE
when 0
then case when @MULTICURRENCYENABLED = 1
then (select isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, I.BASECURRENCYID) from V_BASECURRENCYFORFINANCIALTRANSACTION_I I where I.FINANCIALTRANSACTIONID = REVENUE_EXT.ID)
else @ORGANIZATIONCURRENCYID
end
when 1
then @ORGANIZATIONCURRENCYID
when 2
then a.TRANSACTIONCURRENCYID
else @CURRENCYID
end as DISPLAYCURRENCY
from #REVENUEHISTORY_SUMMEDREVENUE_CTE a
inner join dbo.REVENUE_EXT on a.REVENUEID = REVENUE_EXT.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(a.CONSTITUENTID) NF
union all
select w.WRITEOFFID ID
,--JamesWill WI166057 2011-07-27 The appropriate ID for a write-off is the write-off ID. Otherwise, the PARENTID is the same as the ID and that really confuses the datalist renderer.
null SPLITID
,W.REVENUEID
,W.WRITEOFFID
,--JamesWill WI166057 2011-07-27 The datalist gets confused if this is W.REVENUEID (and adds extra rows to the datalist). So I've made it the WRITEOFF ID and changed the view form referenced by VIEWFORMID to convert any write-off IDs that come in to revenue IDs
case @GROUPBY
when 0
then W.REVENUEID
else null
end PARENTID
,null
,case
when W.WRITEOFFTYPE = 0
then 'FC475006-22FF-425B-8A2B-D35447215A2D' -- Pledge
else '97EE6E24-CADB-4FC9-AB4E-927780F80E76' -- Recurring gift
end VIEWFORMID
,W.[DATE]
,sum(W.AMOUNT)
,--WRITEOFFSPLIT.AMOUNT,
0 as GROSSAMOUNT
,null
,case W.WRITEOFFTYPE when 2 then 'Skip' else 'Write-off' end TRANSACTIONTYPE
,FINANCIALTRANSACTION.TYPE APPLICATION
,null
,null
,null
,null
,null
,CONSTITUENT.[NAME]
,null
,w.DISPLAYCURRENCY
from #TMP_DATA_REVENUEHISTORY_WRITEOFFSPLITS W
inner join dbo.FINANCIALTRANSACTION on W.REVENUEID = FINANCIALTRANSACTION.ID
left join dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
group by W.WRITEOFFID
,W.[DATE]
,W.REVENUEID
,w.DISPLAYCURRENCY
,FINANCIALTRANSACTION.TYPE
,CONSTITUENT.[NAME]
,W.WRITEOFFTYPE
union all -- normal (Order) refunds
select c.CREDITID ID
,null SPLITID
,c.PARENTID
,c.CREDITID RECORDID
,case
when @GROUPBY in (0,1)
then c.PARENTID
else null
end PARENTID
,null
,'d66c6daf-3f3f-404e-910b-df792e980293' VIEWFORMID
,c.[DATE]
,- sum(c.AMOUNT)
,--CREDIT.AMOUNT
0 as GROSSAMOUNT
,null
,'Refund' TRANSACTIONTYPE
,FINANCIALTRANSACTION.TYPE APPLICATION
,null
,null
,null
,null
,null
,CONSTITUENT.[NAME]
,null
,c.DISPLAYCURRENCY
from @REFUNDSPLITS c
inner join dbo.FINANCIALTRANSACTION on c.PARENTID = FINANCIALTRANSACTION.ID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
group by c.CREDITID
,c.[DATE]
,c.PARENTID
,FINANCIALTRANSACTION.TYPE
,CONSTITUENT.[NAME]
,c.DISPLAYCURRENCY
union all -- Group Sales refunds
select c.CREDITID ID
,null SPLITID
,c.PARENTID
,c.CREDITID RECORDID
,case
when @GROUPBY in (0,1)
then c.PARENTID
else null
end PARENTID
,null
,'d66c6daf-3f3f-404e-910b-df792e980293' VIEWFORMID
,c.[DATE]
,- sum(c.AMOUNT)
,--CREDIT.AMOUNT
0 as GROSSAMOUNT
,null
,'Refund' TRANSACTIONTYPE
,FINANCIALTRANSACTION.TYPE APPLICATION
,null
,null
,null
,null
,null
,CONSTITUENT.[NAME]
,null
,c.DISPLAYCURRENCY
from @REFUNDSPLITS c
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on C.PARENTID = FTLI.ID
inner join dbo.FINANCIALTRANSACTION on FTLI.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
group by c.CREDITID
,c.[DATE]
,c.PARENTID
,FINANCIALTRANSACTION.TYPE
,CONSTITUENT.[NAME]
,c.DISPLAYCURRENCY
union all
select c.CREDITID ID
,null SPLITID
,c.REVENUEID
,c.REVENUEID
,case
when @GROUPBY in (0,1)
then c.REVENUEID
else null
end PARENTID
,null
,'F989EA79-EA4D-4FC7-B3F8-6EA606125CCA' VIEWFORMID
,c.[DATE]
,- sum(c.AMOUNT)
,--CREDIT.AMOUNT
0 as GROSSAMOUNT
,null
,'Discount' TRANSACTIONTYPE
,FINANCIALTRANSACTION.TYPE APPLICATION
,null
,null
,null
,null
,null
,CONSTITUENT.[NAME]
,null
,c.DISPLAYCURRENCY
from @CREDITSPLITS c
left join dbo.FINANCIALTRANSACTION on c.REVENUEID = FINANCIALTRANSACTION.ID
--left join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
left join dbo.CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
group by c.CREDITID
,c.[DATE]
,c.REVENUEID
,FINANCIALTRANSACTION.TYPE
,CONSTITUENT.[NAME]
,c.DISPLAYCURRENCY
order by [DATE] desc
,[NAME]
,AMOUNT;