USP_DATALIST_CONSTITUENT_REVENUESUMMARYBREAKDOWN
This datalist returns a breakdown of a constituent's revenue.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @SITEFILTERMODE | tinyint | IN | Sites |
| @SITESSELECTED | nvarchar(max) | 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. |
| @CURRENCYCODE | tinyint | IN | Currency |
| @DATEFILTER | tinyint | IN | Date range |
| @REVENUEFILTERID | uniqueidentifier | IN | |
| @BREAKDOWNBY | tinyint | IN | Breakdown |
| @CAMPAIGNFILTERMODE | tinyint | IN | Campaigns |
| @CAMPAIGNSSELECTED | nvarchar(max) | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_CONSTITUENT_REVENUESUMMARYBREAKDOWN
(
@CONSTITUENTID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@SITEFILTERMODE tinyint = 0,
/*DanielCo [12/19/08] Dashboards don't support parameters that cannot
be cast as strings like this collection so an error will occur if
user selects more sites than fits here (~73)*/
@SITESSELECTED nvarchar(max) = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@CURRENCYCODE tinyint = 1,
@DATEFILTER tinyint,
@REVENUEFILTERID uniqueidentifier = null,
@BREAKDOWNBY tinyint = 0,
@CAMPAIGNFILTERMODE tinyint = 0,
@CAMPAIGNSSELECTED nvarchar(max) = null
)
with execute as OWNER
as
set nocount on;
declare
@STARTDATE datetime,
@ENDDATE datetime,
@ISGROUP bit = 0,
@HOUSEHOLDID uniqueidentifier = null,
@CURRENCYISOCURRENCYCODE nvarchar(3) = null,
@CURRENCYDECIMALDIGITS tinyint = 0,
@CURRENCYSYMBOL nvarchar(5) = null,
@CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint = 0,
@ORGANIZATIONCURRENCYID uniqueidentifier = null,
@CURRENCYID uniqueidentifier = null,
@CURRENCYROUNDINGTYPECODE tinyint,
@CONSTITID uniqueidentifier,
@DATEFROM datetime,
@DATETO datetime;
exec [dbo].[USP_RESOLVEDATEFILTER] @DATEFILTER, @STARTDATE output, @ENDDATE output;
/*Get Household ID*/
select top(1) @HOUSEHOLDID = GM.GROUPID
from dbo.GROUPMEMBER as GM
left outer join dbo.GROUPDATA as GD on GD.ID = GM.GROUPID
where GM.MEMBERID = @CONSTITUENTID
and GD.GROUPTYPECODE = 0
and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1
declare @INCLUDEMEMBERGIVING bit = 0;
if @HOUSEHOLDID is null
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;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
/* Get currency info */
set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @ORIGINCODE tinyint
select @ORIGINCODE = ORGANIZATIONAMOUNTORIGINCODE
from dbo.MULTICURRENCYCONFIGURATION;
if @CURRENCYCODE = 1
set @CURRENCYID = @ORGANIZATIONCURRENCYID;
else
set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
select
@CURRENCYISOCURRENCYCODE = CURRENCY.ISO4217,
@CURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE,
@CURRENCYSYMBOL = CURRENCY.CURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE = CURRENCY.SYMBOLDISPLAYSETTINGCODE
from dbo.CURRENCY where ID = @CURRENCYID;
/* Get Revenue Splits IDs */
if object_id('tempdb..#TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS') is not null
drop table #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS;
create table #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS
(
ID uniqueidentifier,
REVENUEID uniqueidentifier,
TRANSACTIONTYPECODE tinyint,
[DATE] datetime,
);
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
select @SQL = @SQL + '
with CONSTITS_CTE as (
select @CONSTITUENTID CONSTITUENTID, @STARTDATE STARTDATE, @ENDDATE ENDDATE' + char(13);
if @INCLUDEMEMBERGIVING = 1
set @SQL = @SQL + '
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_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS(ID,REVENUEID,TRANSACTIONTYPECODE,DATE)
select FINANCIALTRANSACTIONLINEITEM.ID,FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,FINANCIALTRANSACTION.TYPECODE,cast(FINANCIALTRANSACTION.DATE as datetime)
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID' + char(13);
if @REVENUEFILTERID is not null
set @SQL = @SQL + 'inner join dbo.UFN_REVENUEFILTER_BYID(''' + cast(@REVENUEFILTERID as nvarchar(36)) + ''') FILTERED on FINANCIALTRANSACTIONLINEITEM.ID = FILTERED.ID ' + char(13);
set @SQL = @SQL +
'
inner join dbo.FINANCIALTRANSACTION
on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join dbo.REVENUE_EXT
on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
inner join CONSTITS_CTE CONSTITS
on CONSTITS.CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID
where
FINANCIALTRANSACTION.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) and
-- Exclude order payment splits and use the order splits
REVENUESPLIT_EXT.APPLICATIONCODE <> 10 and
FINANCIALTRANSACTION.DELETEDON is null and
FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1 and
(CONSTITS.STARTDATE is null or cast(FINANCIALTRANSACTION.DATE as datetime) >= CONSTITS.STARTDATE)
and
(CONSTITS.ENDDATE is null or cast(FINANCIALTRANSACTION.DATE as datetime) <= CONSTITS.ENDDATE)';
if @CAMPAIGNFILTERMODE != 0
begin
set @SQL = @SQL +
'and exists (
select 1
from dbo.REVENUESPLITCAMPAIGN
inner join @CAMPAIGNFILTERTABLE CAMPAIGNFILTER on REVENUESPLITCAMPAIGN.CAMPAIGNID = CAMPAIGNFILTER.ID
where REVENUESPLITCAMPAIGN.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
)' + char(13);
end
exec sp_executesql @SQL, N'@CONSTITUENTID uniqueidentifier, @STARTDATE datetime, @ENDDATE datetime, @CAMPAIGNSSELECTED xml',
@CONSTITUENTID, @STARTDATE, @ENDDATE, @CAMPAIGNSSELECTED;
/*remove based on site filter*/
delete FILTERED
from #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS FILTERED
where
not exists
(
select top 1 REVSITES.SITEID
from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FILTERED.ID) REVSITES
where
-- Using a case statement since the standard site extension filters
-- resulted in a poor plan
case
when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
when exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)) then 1
else 0
end = 1
and
(
@SITEFILTERMODE = 0
or
exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
)
)
if object_id('tempdb..#TMP_DATA_REVENUESUMMARYBREAKDOWN_HOUSEHOLDREVENUESPLITIDS') is not null
drop table #TMP_DATA_REVENUESUMMARYBREAKDOWN_HOUSEHOLDREVENUESPLITIDS;
create table #TMP_DATA_REVENUESUMMARYBREAKDOWN_HOUSEHOLDREVENUESPLITIDS
(
ID uniqueidentifier,
REVENUEID uniqueidentifier,
TRANSACTIONTYPECODE tinyint,
[DATE] datetime,
);
if @INCLUDEMEMBERGIVING = 1 or @HOUSEHOLDID is not null
begin
--Get the revenue to include
if @ISGROUP = 1
begin
--Insert members from previous get to keep from having to get them twice
insert into #TMP_DATA_REVENUESUMMARYBREAKDOWN_HOUSEHOLDREVENUESPLITIDS(ID,REVENUEID,[DATE],TRANSACTIONTYPECODE)
select FILTERED.ID,FILTERED.REVENUEID,FILTERED.[DATE],FILTERED.TRANSACTIONTYPECODE
from #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS FILTERED
inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RS.ID = FILTERED.ID
inner join dbo.REVENUESPLIT_EXT on RS.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION R on R.ID = RS.FINANCIALTRANSACTIONID
inner join dbo.REVENUE_EXT on R.ID = REVENUE_EXT.ID
where R.CONSTITUENTID <> @CONSTITUENTID
and R.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
-- Exclude order payment splits and use the order splits
and REVENUESPLIT_EXT.APPLICATIONCODE <> 10
and R.DELETEDON is null
and RS.DELETEDON is null
and RS.TYPECODE <> 1;
end
else
begin
set @SQL =
'with CONSTITS_CTE as (
select
@HOUSEHOLDID as CONSTITUENTID,
null as STARTDATE,
null as ENDDATE
union all
select
GROUPMEMBER.MEMBERID as CONSTITUENTID,
case when GROUPMEMBERDATERANGE.DATEFROM is null or GROUPMEMBERDATERANGE.DATEFROM < @STARTDATE then @STARTDATE else GROUPMEMBERDATERANGE.DATEFROM end STARTDATE,
case when GROUPMEMBERDATERANGE.DATETO is null or GROUPMEMBERDATERANGE.DATETO > @ENDDATE then @ENDDATE else GROUPMEMBERDATERANGE.DATETO end ENDDATE
from dbo.GROUPMEMBER
left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
where
GROUPMEMBER.GROUPID = @HOUSEHOLDID
)
insert into #TMP_DATA_REVENUESUMMARYBREAKDOWN_HOUSEHOLDREVENUESPLITIDS(ID,REVENUEID,TRANSACTIONTYPECODE,DATE)
select FINANCIALTRANSACTIONLINEITEM.ID,FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,FINANCIALTRANSACTION.TYPECODE,cast(FINANCIALTRANSACTION.DATE as datetime)
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID ' + char(13);
if @REVENUEFILTERID is not null
set @SQL = @SQL + 'inner join dbo.UFN_REVENUEFILTER_BYID(''' + cast(@REVENUEFILTERID as nvarchar(36)) + ''') FILTERED on FINANCIALTRANSACTIONLINEITEM.ID = FILTERED.ID ' + char(13);
set @SQL = @SQL +
'
inner join dbo.FINANCIALTRANSACTION
on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join dbo.REVENUE_EXT
on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
inner join CONSTITS_CTE CONSTITS
on CONSTITS.CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID
where
FINANCIALTRANSACTION.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) and
-- Exclude order payment splits and use the order splits
REVENUESPLIT_EXT.APPLICATIONCODE <> 10 and
FINANCIALTRANSACTION.DELETEDON is null and
FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1 and
(CONSTITS.STARTDATE is null or cast(FINANCIALTRANSACTION.DATE as datetime) >= CONSTITS.STARTDATE)
and
(CONSTITS.ENDDATE is null or cast(FINANCIALTRANSACTION.DATE as datetime) <= CONSTITS.ENDDATE)';
exec sp_executesql @SQL, N'@HOUSEHOLDID uniqueidentifier, @STARTDATE datetime, @ENDDATE datetime',
@HOUSEHOLDID, @STARTDATE, @ENDDATE;
end
/*remove based on site filter*/
delete FILTERED
from #TMP_DATA_REVENUESUMMARYBREAKDOWN_HOUSEHOLDREVENUESPLITIDS FILTERED
where
not exists
(
select top 1 REVSITES.SITEID
from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FILTERED.ID) REVSITES
where
-- Using a case statement since the standard site extension filters
-- resulted in a poor plan
case
when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
when exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)) then 1
else 0
end = 1
and
(
@SITEFILTERMODE = 0
or
exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
)
);
end;
with CONSTIT_CTE
as
(
select
Year(case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(FILTERED.DATE) else FILTERED.DATE end) [YEAR],
FILTERED.REVENUEID ID,
case
when FILTERED.TRANSACTIONTYPECODE in (
1, --Pledge
3, --Matching gift claim
4, --Planned gift
6, --Grant award
8, --Donor challenge claim
9 --Pending gift
)
then
(select REVENUESPLITBALANCE.BALANCE
from dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY(
FILTERED.REVENUEID,
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@CURRENCYDECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE,
@CURRENTDATE,
@ORIGINCODE,
@CURRENCYCODE
) as REVENUESPLITBALANCE
where REVENUESPLITBALANCE.ID = FILTERED.ID)
else REVENUESPLIT.AMOUNTINCURRENCY
end AMOUNT
from #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS FILTERED
left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@CURRENCYDECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE) as REVENUESPLIT on FILTERED.ID = REVENUESPLIT.ID
where FILTERED.TRANSACTIONTYPECODE <> 2 --Exclude RGs
and FILTERED.TRANSACTIONTYPECODE <> 15 --Exclude membership installment plans
),
HH_CTE
as
(
select
Year(case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(FILTERED.DATE) else FILTERED.DATE end) [YEAR],
FILTERED.REVENUEID ID,
case
when FILTERED.TRANSACTIONTYPECODE in (
1, --Pledge
3, --Matching gift claim
4, --Planned gift
6, --Grant award
8, --Donor challenge claim
9 --Pending gift
)
then
(select REVENUESPLITBALANCE.BALANCE
from dbo.UFN_PLEDGE_GETREVALUEDSPLITBALANCESINCURRENCY(
FILTERED.REVENUEID,
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@CURRENCYDECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE,
@CURRENTDATE,
@ORIGINCODE,
@CURRENCYCODE
) as REVENUESPLITBALANCE
where REVENUESPLITBALANCE.ID = FILTERED.ID)
else REVENUESPLIT.AMOUNTINCURRENCY
end
AMOUNT
from #TMP_DATA_REVENUESUMMARYBREAKDOWN_HOUSEHOLDREVENUESPLITIDS FILTERED
left join dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@CURRENCYDECIMALDIGITS,
@CURRENCYROUNDINGTYPECODE) as REVENUESPLIT on FILTERED.ID = REVENUESPLIT.ID
where FILTERED.TRANSACTIONTYPECODE <> 2 --Exclude RGs
and FILTERED.TRANSACTIONTYPECODE <> 15 --Exclude membership installment plans
)
select
coalesce(CONSTIT.YEAR, HH.YEAR) as [YEAR],
CONSTIT.TOTALNUMBER as TOTALNUMBER,
CONSTIT.TOTALAMOUNT - coalesce(CONSTITREFUNDS.AMOUNT, 0) as TOTALAMOUNT,
HH.TOTALAMOUNT - coalesce(HHREFUNDS.AMOUNT, 0) as TOTALAMOUNT_HOUSEHOLD,
@CURRENCYISOCURRENCYCODE as CURRENCYISOCURRENCYCODE,
@CURRENCYDECIMALDIGITS as CURRENCYDECIMALDIGITS,
@CURRENCYSYMBOL as CURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
from
(
select YEAR,
count(distinct ID) TOTALNUMBER,
sum(AMOUNT) TOTALAMOUNT
from CONSTIT_CTE
group by [YEAR]
) CONSTIT
full join
(
select
[YEAR],
sum(AMOUNT) TOTALAMOUNT
from HH_CTE
group by [YEAR]
) HH
on CONSTIT.YEAR = HH.YEAR
left join
(
select
Year(case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(CREDIT.TRANSACTIONDATE) else CREDIT.TRANSACTIONDATE end) YEAR,
sum(CREDITITEM.TOTAL) - coalesce(sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT),0.00) AMOUNT
from CREDITITEM
inner join dbo.CREDIT
on CREDITITEM.CREDITID = CREDIT.ID
inner join dbo.SALESORDER
on CREDIT.SALESORDERID = SALESORDER.ID
inner join
(
select distinct
SALESORDERPAYMENT.SALESORDERID, REVENUESPLIT_EXT.TYPE, REVENUESPLIT_EXT.TYPECODE
from #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS FILTERED
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FILTERED.ID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
inner join dbo.SALESORDER on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = SALESORDER.REVENUEID
inner join dbo.SALESORDERPAYMENT on SALESORDER.ID = SALESORDERPAYMENT.SALESORDERID
--kwb temp table has IDs for the order, not the payment so this join was wrong
--inner join dbo.SALESORDERPAYMENT on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = SALESORDERPAYMENT.PAYMENTID
where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
) REVS
on SALESORDER.ID = REVS.SALESORDERID
and
((CREDITITEM.TYPE = REVS.TYPE) or (CREDITITEM.TYPECODE = 2 and REVS.TYPECODE = 0))
left outer join dbo.SALESORDERITEMORDERDISCOUNTDETAIL
on CREDITITEM.SALESORDERITEMID = SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID
where CREDIT.TYPECODE = 0
group by Year(case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(CREDIT.TRANSACTIONDATE) else CREDIT.TRANSACTIONDATE end)
) CONSTITREFUNDS
on
CONSTITREFUNDS.YEAR = CONSTIT.YEAR
left join
(
select
Year(case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(CREDIT.TRANSACTIONDATE) else CREDIT.TRANSACTIONDATE end) YEAR,
sum(CREDITITEM.TOTAL) - coalesce(sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT),0.00) AMOUNT
from CREDITITEM
inner join dbo.CREDIT
on CREDITITEM.CREDITID = CREDIT.ID
inner join dbo.SALESORDER
on CREDIT.SALESORDERID = SALESORDER.ID
inner join
(
select distinct
SALESORDERPAYMENT.SALESORDERID, REVENUESPLIT_EXT.TYPE, REVENUESPLIT_EXT.TYPECODE
from #TMP_DATA_REVENUESUMMARYBREAKDOWN_HOUSEHOLDREVENUESPLITIDS FILTERED
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FILTERED.ID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
inner join dbo.SALESORDER on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = SALESORDER.REVENUEID
inner join dbo.SALESORDERPAYMENT on SALESORDER.ID = SALESORDERPAYMENT.SALESORDERID
--kwb temp table has IDs for the order, not the payment so this join was wrong
--inner join dbo.SALESORDERPAYMENT on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = SALESORDERPAYMENT.PAYMENTID
where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
) REVS
on SALESORDER.ID = REVS.SALESORDERID
and
((CREDITITEM.TYPE = REVS.TYPE) or (CREDITITEM.TYPECODE = 2 and REVS.TYPECODE = 0))
left outer join dbo.SALESORDERITEMORDERDISCOUNTDETAIL
on CREDITITEM.SALESORDERITEMID = SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID
where CREDIT.TYPECODE = 0
group by Year(case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETYEARFROMDATE(CREDIT.TRANSACTIONDATE) else CREDIT.TRANSACTIONDATE end)
) HHREFUNDS
on
HHREFUNDS.YEAR = CONSTIT.YEAR
order by YEAR;
return 0;