USP_DATALIST_CONSTITUENT_REVENUESUMMARYBREAKDOWN_2
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @SITEFILTERMODE | tinyint | IN | |
| @SITESSELECTED | nvarchar(max) | IN | |
| @SECURITYFEATUREID | uniqueidentifier | IN | |
| @SECURITYFEATURETYPE | tinyint | IN | |
| @CURRENCYCODE | tinyint | IN | |
| @DATEFILTER | tinyint | IN | |
| @REVENUEFILTERID | uniqueidentifier | IN | |
| @BREAKDOWNBY | tinyint | IN | |
| @CAMPAIGNFILTERMODE | tinyint | IN | |
| @CAMPAIGNSSELECTED | nvarchar(max) | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_CONSTITUENT_REVENUESUMMARYBREAKDOWN_2
(
@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,
@STARTDATE datetime = null,
@ENDDATE datetime = null
)
with execute as OWNER
as
set nocount on;
declare
@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;
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
/*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;
if object_id('tempdb..#TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUES') is not null
drop table #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUES
create table #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUES
(
REVENUEID uniqueidentifier
,TRANSACTIONTYPECODE tinyint
,[DATE] datetime
,CONSTITUENTID uniqueidentifier
);
create table #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS
(
ID uniqueidentifier,
REVENUEID uniqueidentifier,
TRANSACTIONTYPECODE tinyint,
[DATE] datetime,
CONSTITUENTID uniqueidentifier,
AMOUNT money
);
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 + '
declare @CONSTITS table (CONSTITUENTID uniqueidentifier, STARTDATE datetime, ENDDATE datetime);
insert into @CONSTITS
select @CONSTITUENTID as CONSTITUENTID, @STARTDATE as STARTDATE, @ENDDATE 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 = @CONSTITUENTID;' + char(13);
set @SQL = @SQL + '
insert into #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUES(REVENUEID, TRANSACTIONTYPECODE,[DATE],CONSTITUENTID)
select REVENUE.ID as REVENUEID, REVENUE.TYPECODE TRANSACTIONTYPECODE, REVENUE.CALCULATEDDATE as [DATE], REVENUE.CONSTITUENTID
from FINANCIALTRANSACTION as REVENUE' + char(13);
if @INCLUDEMEMBERGIVING = 1
set @SQL = @SQL + '
inner join @CONSTITS as CONSTITS on CONSTITS.CONSTITUENTID = REVENUE.CONSTITUENTID
where
(REVENUE.CALCULATEDDATE >= isnull(CONSTITS.STARTDATE, REVENUE.CALCULATEDDATE)) and
(REVENUE.CALCULATEDDATE <= isnull(CONSTITS.ENDDATE, REVENUE.CALCULATEDDATE)) ' + char(13);
else
begin
set @SQL = @SQL + 'where REVENUE.CONSTITUENTID = @CONSTITUENTID ' + char(13);
if @STARTDATE is not null
set @SQL = @SQL + 'and (REVENUE.CALCULATEDDATE >= @STARTDATE) '+ char(13);
if @ENDDATE is not null
set @SQL = @SQL + 'and (REVENUE.CALCULATEDDATE <= @ENDDATE) '+ char(13);
end
set @SQL = @SQL + '
and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15)
and REVENUE.DELETEDON is null;' + char(13);
set @SQL = @SQL + '
insert into #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS(ID,REVENUEID,TRANSACTIONTYPECODE,DATE,CONSTITUENTID,AMOUNT)
select FINANCIALTRANSACTIONLINEITEM.ID,FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,REVENUE.TRANSACTIONTYPECODE,REVENUE.DATE, REVENUE.CONSTITUENTID,FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
from #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUES REVENUE
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUE.REVENUEID
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 +
'
where
-- Exclude order payment splits and use the order splits
REVENUESPLIT_EXT.APPLICATIONCODE <> 10 and
FINANCIALTRANSACTIONLINEITEM.DELETEDON is null ' + char(13);
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*/
if (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) <> 1 or @SITEFILTERMODE != 0) and exists(select top 1 1 from dbo.SITE)
begin
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)
)
)
end
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,
AMOUNT money
);
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,AMOUNT)
select FILTERED.ID,FILTERED.REVENUEID,FILTERED.[DATE],FILTERED.TRANSACTIONTYPECODE,FILTERED.AMOUNT
from #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS FILTERED
where FILTERED.CONSTITUENTID <> @CONSTITUENTID;
end
else
begin
set @SQL =
'declare @HOUSEHOLDANDMEMBERS table (CONSTITUENTID uniqueidentifier, STARTDATE datetime, ENDDATE datetime)
insert into @HOUSEHOLDANDMEMBERS (CONSTITUENTID, STARTDATE, ENDDATE)
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,AMOUNT)
select FINANCIALTRANSACTIONLINEITEM.ID,FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,REVENUE.TYPECODE,cast(REVENUE.DATE as datetime),FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
from @HOUSEHOLDANDMEMBERS CONSTITS
inner join dbo.FINANCIALTRANSACTION as REVENUE on REVENUE.CONSTITUENTID = CONSTITS.CONSTITUENTID
inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REVENUE.ID
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 +
'
where
REVENUE.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
FINANCIALTRANSACTIONLINEITEM.DELETEDON is null and
(cast(REVENUE.DATE as datetime) >= isnull(CONSTITS.STARTDATE, cast(REVENUE.DATE as datetime))) and
(cast(REVENUE.DATE as datetime) <= isnull(CONSTITS.ENDDATE, cast(REVENUE.DATE as datetime)))' + char(13);
exec sp_executesql @SQL, N'@HOUSEHOLDID uniqueidentifier, @STARTDATE datetime, @ENDDATE datetime',
@HOUSEHOLDID, @STARTDATE, @ENDDATE;
end
if (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) <> 1 or @SITEFILTERMODE != 0) and exists(select top 1 1 from dbo.SITE)
begin
/*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
end;
--Do not check for the existence of refunds unless basic programs is installed
if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('BB1C17BC-9E0B-4683-B490-EE40D511FA05') = 1
with CONSTIT_CTE
as
(
select
case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETLABEL(FILTERED.DATE) else cast(YEAR(FILTERED.DATE) as nvarchar(24)) end [YEAR],
FILTERED.REVENUEID ID,
REVENUESPLIT.AMOUNTINCURRENCY 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
),
HH_CTE
as
(
select
case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETLABEL(FILTERED.DATE) else cast(YEAR(FILTERED.DATE) as nvarchar(24)) end [YEAR],
FILTERED.REVENUEID ID,
REVENUESPLIT.AMOUNTINCURRENCY 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
)
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
case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETLABEL(CREDIT.TRANSACTIONDATE) else cast(YEAR(CREDIT.TRANSACTIONDATE) as nvarchar(24)) 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 case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETLABEL(CREDIT.TRANSACTIONDATE) else cast(YEAR(CREDIT.TRANSACTIONDATE) as nvarchar(24)) end
) CONSTITREFUNDS
on
CONSTITREFUNDS.YEAR = CONSTIT.YEAR
left join
(
select
case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETLABEL(CREDIT.TRANSACTIONDATE) else cast(YEAR(CREDIT.TRANSACTIONDATE) as nvarchar(24)) 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 case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETLABEL(CREDIT.TRANSACTIONDATE) else cast(YEAR(CREDIT.TRANSACTIONDATE) as nvarchar(24)) end
) HHREFUNDS
on
HHREFUNDS.YEAR = CONSTIT.YEAR
order by YEAR;
else
begin
--Do not compensate for multicurrency if it is not installed
if dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION(N'Multicurrency') = 0
with CONSTIT_CTE
as
(
select
case
when @BREAKDOWNBY = 1 then
dbo.UFN_GLFISCALYEAR_GETLABEL(FILTERED.DATE)
else
cast(YEAR(FILTERED.DATE) as nvarchar(24))
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
15 --Membership Installment Plan
) then
dbo.UFN_PLEDGE_GETSPLITBALANCE(FILTERED.REVENUEID, FILTERED.ID)
else
FILTERED.AMOUNT
end AMOUNT
from #TMP_DATA_REVENUESUMMARYBREAKDOWN_CONSTITREVENUESPLITIDS FILTERED
where FILTERED.TRANSACTIONTYPECODE <> 2 --Exclude RGs
),
HH_CTE
as
(
select
case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETLABEL(FILTERED.DATE) else cast(YEAR(FILTERED.DATE) as nvarchar(24)) 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
15 --Membership Installment Plan
)
then
dbo.UFN_PLEDGE_GETSPLITBALANCE(FILTERED.REVENUEID, FILTERED.ID)
else
FILTERED.AMOUNT
end [AMOUNT]
from #TMP_DATA_REVENUESUMMARYBREAKDOWN_HOUSEHOLDREVENUESPLITIDS FILTERED
where FILTERED.TRANSACTIONTYPECODE <> 2 --Exclude RGs
)
select
coalesce(CONSTIT.YEAR, HH.YEAR) as [YEAR],
CONSTIT.TOTALNUMBER as TOTALNUMBER,
CONSTIT.TOTALAMOUNT as TOTALAMOUNT,
HH.TOTALAMOUNT 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
order by YEAR;
else
with CONSTIT_CTE
as
(
select
case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETLABEL(FILTERED.DATE) else cast(YEAR(FILTERED.DATE) as nvarchar(24)) 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
15 --Membership Installment Plan
)
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
),
HH_CTE
as
(
select
case when @BREAKDOWNBY = 1 then dbo.UFN_GLFISCALYEAR_GETLABEL(FILTERED.DATE) else cast(YEAR(FILTERED.DATE) as nvarchar(24)) 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
15 --Membership Installment Plan
)
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
)
select
coalesce(CONSTIT.YEAR, HH.YEAR) as [YEAR],
CONSTIT.TOTALNUMBER as TOTALNUMBER,
CONSTIT.TOTALAMOUNT as TOTALAMOUNT,
HH.TOTALAMOUNT 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
order by YEAR;
end
return 0;