USP_REPORT_DESIGNATIONLEVELREVENUE_BYDATE_2
Returns the date data necessary for the new design of the Fundraising Purpose Revenue report.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @START | datetime | IN | |
| @END | datetime | IN | |
| @CATEGORYCODEID | uniqueidentifier | IN | |
| @DESIGNATIONLEVELTYPEID | uniqueidentifier | IN | |
| @GROUPBY | tinyint | IN | |
| @SELECTIONID | uniqueidentifier | IN | |
| @CAMPAIGNHIERARCHYPATH | hierarchyid | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @CURRENCYCODE | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_REPORT_DESIGNATIONLEVELREVENUE_BYDATE_2
(
@START datetime = null,
@END datetime = null,
@CATEGORYCODEID uniqueidentifier = null,
@DESIGNATIONLEVELTYPEID uniqueidentifier = null,
@GROUPBY tinyint = 0,
@SELECTIONID uniqueidentifier = null,
@CAMPAIGNHIERARCHYPATH hierarchyid = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@CURRENCYCODE tinyint = null
)
as
set nocount on;
declare @PERIODOPTION tinyint;
set @PERIODOPTION = @GROUPBY;
set @START = dbo.UFN_DATE_GETEARLIESTTIME(@START);
set @END = dbo.UFN_DATE_GETLATESTTIME(@END);
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
if @END < @START
raiserror('The start date must be on or before the end date.',13,1);
declare @PERIODSPLITS table
(
DESIGNATIONLEVELID uniqueidentifier,
REVENUEID uniqueidentifier,
REVENUESPLITID uniqueidentifier,
REVENUESPLITAMOUNT money,
DESIGNATIONID uniqueidentifier,
APPEALID uniqueidentifier,
TRANSACTIONTYPECODE tinyint,
SPLITTYPECODE tinyint,
APPLICATIONCODE tinyint,
PERIODSEQUENCE int,
PERIODYEAR int,
PERIODSTARTDATE datetime,
PERIODENDDATE datetime,
BASECURRENCYID uniqueidentifier
);
declare @OVERALLSPLITS table
(
DESIGNATIONLEVELID uniqueidentifier,
REVENUEID uniqueidentifier,
REVENUESPLITID uniqueidentifier,
REVENUESPLITAMOUNT money,
DESIGNATIONID uniqueidentifier,
APPEALID uniqueidentifier,
TRANSACTIONTYPECODE tinyint,
SPLITTYPECODE tinyint,
APPLICATIONCODE tinyint,
PERIODSEQUENCE int,
PERIODYEAR int,
PERIODSTARTDATE datetime,
PERIODENDDATE datetime,
BASECURRENCYID uniqueidentifier
);
if @CURRENCYCODE = 0
begin
insert into @PERIODSPLITS
select
DESIGNATIONLEVEL.ID as [DESIGNATIONLEVELID],
[SPLITS].REVENUEID,
[SPLITS].REVENUESPLITID,
[SPLITS].REVENUESPLITAMOUNT,
[SPLITS].DESIGNATIONID,
[SPLITS].APPEALID,
[SPLITS].TRANSACTIONTYPECODE,
[SPLITS].SPLITTYPECODE,
[SPLITS].APPLICATIONCODE,
[PERIODS].SEQUENCE,
[PERIODS].PERIODYEAR,
[PERIODS].STARTDATE,
[PERIODS].ENDDATE,
DESIGNATIONLEVEL.BASECURRENCYID
from dbo.DESIGNATIONLEVEL with (nolock)
cross apply dbo.UFN_DESIGNATIONLEVEL_REVENUESPLITRECORDSINCURRENCY_2(DESIGNATIONLEVEL.ID, @START, @END, @CAMPAIGNHIERARCHYPATH, null, DESIGNATIONLEVEL.BASECURRENCYID, @ORGANIZATIONCURRENCYID) as [SPLITS]
inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = [SPLITS].REVENUEID
inner join dbo.UFN_DESIGNATIONREPORT_GETPERIODS(@START, @END, @PERIODOPTION) as [PERIODS] on [PERIODS].STARTDATE <= cast([REVENUE].DATE as datetime) and [PERIODS].ENDDATE >= cast([REVENUE].DATE as datetime)
where (@CATEGORYCODEID is null or DESIGNATIONLEVEL.DESIGNATIONLEVELCATEGORYCODEID = @CATEGORYCODEID)
and (@DESIGNATIONLEVELTYPEID is null or DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = @DESIGNATIONLEVELTYPEID)
and (@SELECTIONID is null or DESIGNATIONLEVEL.ID in (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID)))
and (@CURRENTAPPUSERID is null or dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, DESIGNATIONLEVEL.SITEID) = 1);
insert into @OVERALLSPLITS
select
DESIGNATIONLEVEL.ID as [DESIGNATIONLEVELID],
[SPLITS].REVENUEID,
[SPLITS].REVENUESPLITID,
[SPLITS].REVENUESPLITAMOUNT,
[SPLITS].DESIGNATIONID,
[SPLITS].APPEALID,
[SPLITS].TRANSACTIONTYPECODE,
[SPLITS].SPLITTYPECODE,
[SPLITS].APPLICATIONCODE,
-1,
-1,
null,
null,
DESIGNATIONLEVEL.BASECURRENCYID
from dbo.DESIGNATIONLEVEL with (nolock)
cross apply dbo.UFN_DESIGNATIONLEVEL_REVENUESPLITRECORDSINCURRENCY_2(DESIGNATIONLEVEL.ID, @START, @END, @CAMPAIGNHIERARCHYPATH, null, DESIGNATIONLEVEL.BASECURRENCYID, @ORGANIZATIONCURRENCYID ) as [SPLITS]
where (@CATEGORYCODEID is null or DESIGNATIONLEVEL.DESIGNATIONLEVELCATEGORYCODEID = @CATEGORYCODEID)
and (@DESIGNATIONLEVELTYPEID is null or DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = @DESIGNATIONLEVELTYPEID)
and (@SELECTIONID is null or DESIGNATIONLEVEL.ID in (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID)))
and (@CURRENTAPPUSERID is null or dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, DESIGNATIONLEVEL.SITEID) = 1);
with [OVERALLNEWCOMMITMENTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
[SPLITS].PERIODSEQUENCE as [PERIODSEQUENCE],
[SPLITS].PERIODYEAR as [PERIODYEAR]
from @OVERALLSPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE in (1, 6, 10)
union all
select
[SPLITS].DESIGNATIONLEVELID,
[SPLITS].REVENUEID,
[SPLITS].REVENUESPLITID,
case
when ([SPLITS].BASECURRENCYID = @ORGANIZATIONCURRENCYID)
then WRITEOFFSPLIT.ORGAMOUNT
when [SPLITS].BASECURRENCYID = V.BASECURRENCYID
then WRITEOFFSPLIT.BASEAMOUNT
else dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WRITEOFFSPLIT.ID, [SPLITS].BASECURRENCYID) * -1
end as [REVENUESPLITAMOUNT],
0 as [GIFTCOUNT],
[SPLITS].PERIODSEQUENCE as [PERIODSEQUENCE],
[SPLITS].PERIODYEAR as [PERIODYEAR]
from dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFSPLIT
inner join dbo.FINANCIALTRANSACTION WRITEOFF on WRITEOFFSPLIT.FINANCIALTRANSACTIONID = WRITEOFF.ID
inner join @OVERALLSPLITS [SPLITS] on [SPLITS].REVENUEID = WRITEOFF.PARENTID
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on WRITEOFFSPLIT.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
where [SPLITS].TRANSACTIONTYPECODE in (1,6)
),
[PERIODNEWCOMMITMENTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
[SPLITS].PERIODSEQUENCE as [PERIODSEQUENCE],
[SPLITS].PERIODYEAR as [PERIODYEAR]
from @PERIODSPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE in (1, 6)
union all
select
[SPLITS].DESIGNATIONLEVELID,
[SPLITS].REVENUEID,
[SPLITS].REVENUESPLITID,
case
when ([SPLITS].BASECURRENCYID = @ORGANIZATIONCURRENCYID)
then WRITEOFFSPLIT.ORGAMOUNT
when [SPLITS].BASECURRENCYID = V.BASECURRENCYID
then WRITEOFFSPLIT.BASEAMOUNT
else dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WRITEOFFSPLIT.ID, [SPLITS].BASECURRENCYID) * -1
end as [REVENUESPLITAMOUNT],
0 as [GIFTCOUNT],
[SPLITS].PERIODSEQUENCE as [PERIODSEQUENCE],
[SPLITS].PERIODYEAR as [PERIODYEAR]
from dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFSPLIT
inner join dbo.FINANCIALTRANSACTION WRITEOFF on WRITEOFFSPLIT.FINANCIALTRANSACTIONID = WRITEOFF.ID
inner join @PERIODSPLITS [SPLITS] on [SPLITS].REVENUEID = WRITEOFF.PARENTID
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on WRITEOFFSPLIT.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
where [SPLITS].TRANSACTIONTYPECODE in (1,6)
),
[OVERALLRECEIVED_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
[SPLITS].PERIODSEQUENCE as [PERIODSEQUENCE],
[SPLITS].PERIODYEAR as [PERIODYEAR]
from @OVERALLSPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE = 0
and [SPLITS].APPLICATIONCODE in (0, 1, 6, 7, 3, 10, 13)
and ([SPLITS].APPLICATIONCODE <> 10 or [SPLITS].SPLITTYPECODE = 0)
),
[PERIODRECEIVED_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
[SPLITS].PERIODSEQUENCE as [PERIODSEQUENCE],
[SPLITS].PERIODYEAR as [PERIODYEAR]
from @PERIODSPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE = 0
and [SPLITS].APPLICATIONCODE in (0, 1, 6, 7, 3, 10, 13)
and ([SPLITS].APPLICATIONCODE <> 10 or [SPLITS].SPLITTYPECODE = 0)
),
[OVERALLPLANNEDGIFTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
[SPLITS].PERIODSEQUENCE as [PERIODSEQUENCE],
[SPLITS].PERIODYEAR as [PERIODYEAR]
from @OVERALLSPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE = 4
),
[PERIODPLANNEDGIFTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
[SPLITS].PERIODSEQUENCE as [PERIODSEQUENCE],
[SPLITS].PERIODYEAR as [PERIODYEAR]
from @PERIODSPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE = 4
),
[PREREPORT_CTE] as
(
select
[PERIODRECEIVED_CTE].DESIGNATIONLEVELID,
sum(0) as [OVERALLRECEIVED_AMOUNT],
sum(0) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum(0) as [OVERALLPLANNEDGIFT_AMOUNT],
sum([PERIODRECEIVED_CTE].REVENUESPLITAMOUNT) as [PERIODRECEIVED_AMOUNT],
sum(0) as [PERIODNEWCOMMITMENTS_AMOUNT],
sum(0) as [PERIODPLANNEDGIFT_AMOUNT],
sum([PERIODRECEIVED_CTE].GIFTCOUNT) as [PERIODGIFTCOUNT],
PERIODSEQUENCE,
PERIODYEAR
from [PERIODRECEIVED_CTE]
group by DESIGNATIONLEVELID, PERIODSEQUENCE, PERIODYEAR
union all
select
[PERIODNEWCOMMITMENTS_CTE].DESIGNATIONLEVELID,
sum(0) as [OVERALLRECEIVED_AMOUNT],
sum(0) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum(0) as [OVERALLPLANNEDGIFT_AMOUNT],
sum(0) as [PERIODRECEIVED_AMOUNT],
sum([PERIODNEWCOMMITMENTS_CTE].REVENUESPLITAMOUNT) as [PERIODNEWCOMMITMENTS_AMOUNT],
sum(0) as [PERIODPLANNEDGIFT_AMOUNT],
sum([PERIODNEWCOMMITMENTS_CTE].GIFTCOUNT) as [PERIODGIFTCOUNT],
PERIODSEQUENCE,
PERIODYEAR
from [PERIODNEWCOMMITMENTS_CTE]
group by DESIGNATIONLEVELID, PERIODSEQUENCE, PERIODYEAR
union all
select
[PERIODPLANNEDGIFTS_CTE].DESIGNATIONLEVELID,
sum(0) as [OVERALLRECEIVED_AMOUNT],
sum(0) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum(0) as [OVERALLPLANNEDGIFT_AMOUNT],
sum(0) as [PERIODRECEIVED_AMOUNT],
sum(0) as [PERIODNEWCOMMITMENTS_AMOUNT],
sum([PERIODPLANNEDGIFTS_CTE].REVENUESPLITAMOUNT) as [PERIODPLANNEDGIFT_AMOUNT],
sum(0) as [PERIODGIFTCOUNT],
PERIODSEQUENCE,
PERIODYEAR
from [PERIODPLANNEDGIFTS_CTE]
group by DESIGNATIONLEVELID, PERIODSEQUENCE, PERIODYEAR
),
[REPORT_CTE] as
(
select
DESIGNATIONLEVEL.ID as [DESIGNATIONLEVELID],
[PERIODS].PERIODYEAR,
[PERIODS].SEQUENCE as [PERIODSEQUENCE],
(
select coalesce(sum([OVERALLRECEIVED_CTE].REVENUESPLITAMOUNT), 0)
from [OVERALLRECEIVED_CTE] where DESIGNATIONLEVELID = DESIGNATIONLEVEL.ID
) as [OVERALLRECEIVED_AMOUNT],
(
select coalesce(sum([OVERALLNEWCOMMITMENTS_CTE].REVENUESPLITAMOUNT), 0)
from [OVERALLNEWCOMMITMENTS_CTE] where DESIGNATIONLEVELID = DESIGNATIONLEVEL.ID
) as [OVERALLNEWCOMMITMENTS_AMOUNT],
(
select coalesce(sum([OVERALLPLANNEDGIFTS_CTE].REVENUESPLITAMOUNT), 0)
from [OVERALLPLANNEDGIFTS_CTE] where DESIGNATIONLEVELID = DESIGNATIONLEVEL.ID
) as [OVERALLPLANNEDGIFT_AMOUNT],
coalesce([PREREPORT_CTE].[PERIODRECEIVED_AMOUNT],0) as [PERIODRECEIVED_AMOUNT],
coalesce([PREREPORT_CTE].[PERIODNEWCOMMITMENTS_AMOUNT],0) as [PERIODNEWCOMMITMENTS_AMOUNT],
coalesce([PREREPORT_CTE].[PERIODPLANNEDGIFT_AMOUNT],0) as [PERIODPLANNEDGIFT_AMOUNT],
(
coalesce((select sum(GIFTCOUNT) from [OVERALLRECEIVED_CTE] where DESIGNATIONLEVELID = DESIGNATIONLEVEL.ID),0) +
coalesce((select sum(GIFTCOUNT) from [OVERALLNEWCOMMITMENTS_CTE] where DESIGNATIONLEVELID = DESIGNATIONLEVEL.ID), 0)
) as [OVERALLGIFTCOUNT],
[PREREPORT_CTE].[PERIODGIFTCOUNT]
from dbo.UFN_DESIGNATIONREPORT_GETPERIODS(@START, @END, @PERIODOPTION) as [PERIODS]
cross join dbo.DESIGNATIONLEVEL
left join [PREREPORT_CTE] on [PREREPORT_CTE].PERIODYEAR = [PERIODS].PERIODYEAR and [PREREPORT_CTE].PERIODSEQUENCE = [PERIODS].SEQUENCE and [PREREPORT_CTE].DESIGNATIONLEVELID = DESIGNATIONLEVEL.ID
)
select
'http://www.blackbaud.com/DESIGNATIONLEVELID?DESIGNATIONLEVELID=' + cast(DESIGNATIONLEVEL.ID as nvarchar(36)) as [DESIGNATIONLEVELID],
DESIGNATIONLEVEL.NAME,
coalesce([DATA].PERIODSEQUENCE, 1) as [SEQUENCE],
coalesce([DATA].PERIODYEAR, 1) as [PERIODYEAR],
coalesce(max([DATA].OVERALLGIFTCOUNT), 0) as [TOTALNUMGIFTS],
-1 as [TOTALDONORS],
-1 as [TOTALRAISED],
coalesce(sum([DATA].PERIODGIFTCOUNT), 0) as [PERIODNUMGIFTS],
-1 as [MAXGIFT],
-1 as [PERIODAVGGIFT],
coalesce(max([DATA].OVERALLRECEIVED_AMOUNT), 0) + coalesce(max([DATA].OVERALLNEWCOMMITMENTS_AMOUNT), 0) as [OVERALLTOTALRAISED],
coalesce(max([DATA].OVERALLRECEIVED_AMOUNT), 0) as [OVERALLTOTALRECEIVED],
coalesce(max([DATA].OVERALLNEWCOMMITMENTS_AMOUNT), 0) as [OVERALLTOTALEXPECTED],
-1 as [TOTALAVGGIFT],
coalesce(sum([DATA].PERIODRECEIVED_AMOUNT), 0) as [TOTALRECEIVED],
coalesce(sum([DATA].PERIODNEWCOMMITMENTS_AMOUNT), 0) as [TOTALEXPECTED],
0 as [PERIODNUMDONORS],
null as [APPEALNAME],
coalesce(max([DATA].OVERALLPLANNEDGIFT_AMOUNT), 0) as [OVERALLTOTALPLANNEDGIFTS],
coalesce(sum([DATA].PERIODPLANNEDGIFT_AMOUNT), 0) as [TOTALPLANNEDGIFTS],
CURRENCYPROPERTIES.ID as CURRENCYID,
CURRENCYPROPERTIES.ISO4217 as CURRENCYISO,
CURRENCYPROPERTIES.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
CURRENCYPROPERTIES.CURRENCYSYMBOL as CURRENCYSYMBOL,
CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
from DESIGNATIONLEVEL with (nolock)
left join [REPORT_CTE] as [DATA] on [DATA].DESIGNATIONLEVELID = DESIGNATIONLEVEL.ID
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(DESIGNATIONLEVEL.BASECURRENCYID) CURRENCYPROPERTIES
where (@CATEGORYCODEID is null or DESIGNATIONLEVEL.DESIGNATIONLEVELCATEGORYCODEID = @CATEGORYCODEID)
and (@DESIGNATIONLEVELTYPEID is null or DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = @DESIGNATIONLEVELTYPEID)
and (@SELECTIONID is null or DESIGNATIONLEVEL.ID in (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID)))
and (@CURRENTAPPUSERID is null or dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, DESIGNATIONLEVEL.SITEID) = 1)
group by DESIGNATIONLEVEL.ID, DESIGNATIONLEVEL.NAME, [DATA].PERIODYEAR, [DATA].PERIODSEQUENCE,DESIGNATIONLEVEL.BASECURRENCYID, CURRENCYPROPERTIES.ID,CURRENCYPROPERTIES.ISO4217,
CURRENCYPROPERTIES.DECIMALDIGITS, CURRENCYPROPERTIES.CURRENCYSYMBOL, CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE
order by DESIGNATIONLEVEL.NAME, [DATA].PERIODYEAR, [DATA].PERIODSEQUENCE
end
else
begin
declare @CURRENCYID uniqueidentifier;
declare @CURRENCYISO nvarchar(3);
declare @CURRENCYDECIMALDIGITS tinyint;
declare @CURRENCYSYMBOL nvarchar(5);
declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;
select
@CURRENCYID = ID,
@CURRENCYISO = ISO4217,
@CURRENCYDECIMALDIGITS = DECIMALDIGITS,
@CURRENCYSYMBOL = CURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE = SYMBOLDISPLAYSETTINGCODE
from dbo.CURRENCY where ID = @ORGANIZATIONCURRENCYID;
insert into @PERIODSPLITS
select
DESIGNATIONLEVEL.ID as [DESIGNATIONLEVELID],
[SPLITS].REVENUEID,
[SPLITS].REVENUESPLITID,
[SPLITS].REVENUESPLITAMOUNT,
[SPLITS].DESIGNATIONID,
[SPLITS].APPEALID,
[SPLITS].TRANSACTIONTYPECODE,
[SPLITS].SPLITTYPECODE,
[SPLITS].APPLICATIONCODE,
[PERIODS].SEQUENCE,
[PERIODS].PERIODYEAR,
[PERIODS].STARTDATE,
[PERIODS].ENDDATE,
DESIGNATIONLEVEL.BASECURRENCYID
from dbo.DESIGNATIONLEVEL with (nolock)
cross apply dbo.UFN_DESIGNATIONLEVEL_REVENUESPLITRECORDSINCURRENCY_2(DESIGNATIONLEVEL.ID, @START, @END, @CAMPAIGNHIERARCHYPATH, null, @CURRENCYID, @ORGANIZATIONCURRENCYID) as [SPLITS]
inner join dbo.FINANCIALTRANSACTION REVENUE on REVENUE.ID = [SPLITS].REVENUEID
inner join dbo.UFN_DESIGNATIONREPORT_GETPERIODS(@START, @END, @PERIODOPTION) as [PERIODS] on [PERIODS].STARTDATE <= cast([REVENUE].DATE as datetime) and [PERIODS].ENDDATE >= cast([REVENUE].DATE as datetime)
where (@CATEGORYCODEID is null or DESIGNATIONLEVEL.DESIGNATIONLEVELCATEGORYCODEID = @CATEGORYCODEID)
and (@DESIGNATIONLEVELTYPEID is null or DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = @DESIGNATIONLEVELTYPEID)
and (@SELECTIONID is null or DESIGNATIONLEVEL.ID in (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID)))
and (@CURRENTAPPUSERID is null or dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, DESIGNATIONLEVEL.SITEID) = 1);
insert into @OVERALLSPLITS
select
DESIGNATIONLEVEL.ID as [DESIGNATIONLEVELID],
[SPLITS].REVENUEID,
[SPLITS].REVENUESPLITID,
[SPLITS].REVENUESPLITAMOUNT,
[SPLITS].DESIGNATIONID,
[SPLITS].APPEALID,
[SPLITS].TRANSACTIONTYPECODE,
[SPLITS].SPLITTYPECODE,
[SPLITS].APPLICATIONCODE,
-1,
-1,
null,
null,
DESIGNATIONLEVEL.BASECURRENCYID
from dbo.DESIGNATIONLEVEL with (nolock)
cross apply dbo.UFN_DESIGNATIONLEVEL_REVENUESPLITRECORDSINCURRENCY_2(DESIGNATIONLEVEL.ID, @START, @END, @CAMPAIGNHIERARCHYPATH, null, @CURRENCYID, @ORGANIZATIONCURRENCYID) as [SPLITS]
where (@CATEGORYCODEID is null or DESIGNATIONLEVEL.DESIGNATIONLEVELCATEGORYCODEID = @CATEGORYCODEID)
and (@DESIGNATIONLEVELTYPEID is null or DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = @DESIGNATIONLEVELTYPEID)
and (@SELECTIONID is null or DESIGNATIONLEVEL.ID in (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID)))
and (@CURRENTAPPUSERID is null or dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, DESIGNATIONLEVEL.SITEID) = 1);
with [OVERALLNEWCOMMITMENTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
[SPLITS].PERIODSEQUENCE as [PERIODSEQUENCE],
[SPLITS].PERIODYEAR as [PERIODYEAR]
from @OVERALLSPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE in (1, 6, 10)
union all
select
[SPLITS].DESIGNATIONLEVELID,
[SPLITS].REVENUEID,
[SPLITS].REVENUESPLITID,
case
when (@CURRENCYID is null or @CURRENCYID = @ORGANIZATIONCURRENCYID)
then WRITEOFFSPLIT.ORGAMOUNT
when @CURRENCYID = V.BASECURRENCYID
then WRITEOFFSPLIT.BASEAMOUNT
else dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WRITEOFFSPLIT.ID, @CURRENCYID) * -1
end as [REVENUESPLITAMOUNT],
0 as [GIFTCOUNT],
[SPLITS].PERIODSEQUENCE as [PERIODSEQUENCE],
[SPLITS].PERIODYEAR as [PERIODYEAR]
from dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFSPLIT
inner join dbo.FINANCIALTRANSACTION WRITEOFF on WRITEOFFSPLIT.FINANCIALTRANSACTIONID = WRITEOFF.ID
inner join @OVERALLSPLITS [SPLITS] on [SPLITS].REVENUEID = WRITEOFF.PARENTID
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on WRITEOFFSPLIT.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
where [SPLITS].TRANSACTIONTYPECODE in (1,6)
),
[PERIODNEWCOMMITMENTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
[SPLITS].PERIODSEQUENCE as [PERIODSEQUENCE],
[SPLITS].PERIODYEAR as [PERIODYEAR]
from @PERIODSPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE in (1, 6)
union all
select
[SPLITS].DESIGNATIONLEVELID,
[SPLITS].REVENUEID,
[SPLITS].REVENUESPLITID,
case
when (@CURRENCYID is null or @CURRENCYID = @ORGANIZATIONCURRENCYID)
then WRITEOFFSPLIT.ORGAMOUNT
when @CURRENCYID = V.BASECURRENCYID
then WRITEOFFSPLIT.BASEAMOUNT
else dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WRITEOFFSPLIT.ID, @CURRENCYID) * -1
end as [REVENUESPLITAMOUNT],
0 as [GIFTCOUNT],
[SPLITS].PERIODSEQUENCE as [PERIODSEQUENCE],
[SPLITS].PERIODYEAR as [PERIODYEAR]
from dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFSPLIT
inner join dbo.FINANCIALTRANSACTION WRITEOFF on WRITEOFFSPLIT.FINANCIALTRANSACTIONID = WRITEOFF.ID
inner join @PERIODSPLITS [SPLITS] on [SPLITS].REVENUEID = WRITEOFF.PARENTID
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on WRITEOFFSPLIT.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
where [SPLITS].TRANSACTIONTYPECODE in (1,6)
),
[OVERALLRECEIVED_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
[SPLITS].PERIODSEQUENCE as [PERIODSEQUENCE],
[SPLITS].PERIODYEAR as [PERIODYEAR]
from @OVERALLSPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE = 0
and [SPLITS].APPLICATIONCODE in (0, 1, 6, 7, 3, 10, 13)
and ([SPLITS].APPLICATIONCODE <> 10 or [SPLITS].SPLITTYPECODE = 0)
),
[PERIODRECEIVED_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
[SPLITS].PERIODSEQUENCE as [PERIODSEQUENCE],
[SPLITS].PERIODYEAR as [PERIODYEAR]
from @PERIODSPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE = 0
and [SPLITS].APPLICATIONCODE in (0, 1, 6, 7, 3, 10, 13)
and ([SPLITS].APPLICATIONCODE <> 10 or [SPLITS].SPLITTYPECODE = 0)
),
[OVERALLPLANNEDGIFTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
[SPLITS].PERIODSEQUENCE as [PERIODSEQUENCE],
[SPLITS].PERIODYEAR as [PERIODYEAR]
from @OVERALLSPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE = 4
),
[PERIODPLANNEDGIFTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
[SPLITS].PERIODSEQUENCE as [PERIODSEQUENCE],
[SPLITS].PERIODYEAR as [PERIODYEAR]
from @PERIODSPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE = 4
),
[PREREPORT_CTE] as
(
select
[PERIODRECEIVED_CTE].DESIGNATIONLEVELID,
sum(0) as [OVERALLRECEIVED_AMOUNT],
sum(0) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum(0) as [OVERALLPLANNEDGIFT_AMOUNT],
sum([PERIODRECEIVED_CTE].REVENUESPLITAMOUNT) as [PERIODRECEIVED_AMOUNT],
sum(0) as [PERIODNEWCOMMITMENTS_AMOUNT],
sum(0) as [PERIODPLANNEDGIFT_AMOUNT],
sum([PERIODRECEIVED_CTE].GIFTCOUNT) as [PERIODGIFTCOUNT],
PERIODSEQUENCE,
PERIODYEAR
from [PERIODRECEIVED_CTE]
group by DESIGNATIONLEVELID, PERIODSEQUENCE, PERIODYEAR
union all
select
[PERIODNEWCOMMITMENTS_CTE].DESIGNATIONLEVELID,
sum(0) as [OVERALLRECEIVED_AMOUNT],
sum(0) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum(0) as [OVERALLPLANNEDGIFT_AMOUNT],
sum(0) as [PERIODRECEIVED_AMOUNT],
sum([PERIODNEWCOMMITMENTS_CTE].REVENUESPLITAMOUNT) as [PERIODNEWCOMMITMENTS_AMOUNT],
sum(0) as [PERIODPLANNEDGIFT_AMOUNT],
sum([PERIODNEWCOMMITMENTS_CTE].GIFTCOUNT) as [PERIODGIFTCOUNT],
PERIODSEQUENCE,
PERIODYEAR
from [PERIODNEWCOMMITMENTS_CTE]
group by DESIGNATIONLEVELID, PERIODSEQUENCE, PERIODYEAR
union all
select
[PERIODPLANNEDGIFTS_CTE].DESIGNATIONLEVELID,
sum(0) as [OVERALLRECEIVED_AMOUNT],
sum(0) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum(0) as [OVERALLPLANNEDGIFT_AMOUNT],
sum(0) as [PERIODRECEIVED_AMOUNT],
sum(0) as [PERIODNEWCOMMITMENTS_AMOUNT],
sum([PERIODPLANNEDGIFTS_CTE].REVENUESPLITAMOUNT) as [PERIODPLANNEDGIFT_AMOUNT],
sum(0) as [PERIODGIFTCOUNT],
PERIODSEQUENCE,
PERIODYEAR
from [PERIODPLANNEDGIFTS_CTE]
group by DESIGNATIONLEVELID, PERIODSEQUENCE, PERIODYEAR
),
[REPORT_CTE] as
(
select
DESIGNATIONLEVEL.ID as [DESIGNATIONLEVELID],
[PERIODS].PERIODYEAR,
[PERIODS].SEQUENCE as [PERIODSEQUENCE],
(
select coalesce(sum([OVERALLRECEIVED_CTE].REVENUESPLITAMOUNT), 0)
from [OVERALLRECEIVED_CTE] where DESIGNATIONLEVELID = DESIGNATIONLEVEL.ID
) as [OVERALLRECEIVED_AMOUNT],
(
select coalesce(sum([OVERALLNEWCOMMITMENTS_CTE].REVENUESPLITAMOUNT), 0)
from [OVERALLNEWCOMMITMENTS_CTE] where DESIGNATIONLEVELID = DESIGNATIONLEVEL.ID
) as [OVERALLNEWCOMMITMENTS_AMOUNT],
(
select coalesce(sum([OVERALLPLANNEDGIFTS_CTE].REVENUESPLITAMOUNT), 0)
from [OVERALLPLANNEDGIFTS_CTE] where DESIGNATIONLEVELID = DESIGNATIONLEVEL.ID
) as [OVERALLPLANNEDGIFT_AMOUNT],
coalesce([PREREPORT_CTE].[PERIODRECEIVED_AMOUNT],0) as [PERIODRECEIVED_AMOUNT],
coalesce([PREREPORT_CTE].[PERIODNEWCOMMITMENTS_AMOUNT],0) as [PERIODNEWCOMMITMENTS_AMOUNT],
coalesce([PREREPORT_CTE].[PERIODPLANNEDGIFT_AMOUNT],0) as [PERIODPLANNEDGIFT_AMOUNT],
(
coalesce((select sum(GIFTCOUNT) from [OVERALLRECEIVED_CTE] where DESIGNATIONLEVELID = DESIGNATIONLEVEL.ID),0) +
coalesce((select sum(GIFTCOUNT) from [OVERALLNEWCOMMITMENTS_CTE] where DESIGNATIONLEVELID = DESIGNATIONLEVEL.ID), 0)
) as [OVERALLGIFTCOUNT],
[PREREPORT_CTE].[PERIODGIFTCOUNT]
from dbo.UFN_DESIGNATIONREPORT_GETPERIODS(@START, @END, @PERIODOPTION) as [PERIODS]
cross join dbo.DESIGNATIONLEVEL
left join [PREREPORT_CTE] on [PREREPORT_CTE].PERIODYEAR = [PERIODS].PERIODYEAR and [PREREPORT_CTE].PERIODSEQUENCE = [PERIODS].SEQUENCE and [PREREPORT_CTE].DESIGNATIONLEVELID = DESIGNATIONLEVEL.ID
)
select
'http://www.blackbaud.com/DESIGNATIONLEVELID?DESIGNATIONLEVELID=' + cast(DESIGNATIONLEVEL.ID as nvarchar(36)) as [DESIGNATIONLEVELID],
DESIGNATIONLEVEL.NAME,
coalesce([DATA].PERIODSEQUENCE, 1) as [SEQUENCE],
coalesce([DATA].PERIODYEAR, 1) as [PERIODYEAR],
coalesce(max([DATA].OVERALLGIFTCOUNT), 0) as [TOTALNUMGIFTS],
-1 as [TOTALDONORS],
-1 as [TOTALRAISED],
coalesce(sum([DATA].PERIODGIFTCOUNT), 0) as [PERIODNUMGIFTS],
-1 as [MAXGIFT],
-1 as [PERIODAVGGIFT],
coalesce(max([DATA].OVERALLRECEIVED_AMOUNT), 0) + coalesce(max([DATA].OVERALLNEWCOMMITMENTS_AMOUNT), 0) as [OVERALLTOTALRAISED],
coalesce(max([DATA].OVERALLRECEIVED_AMOUNT), 0) as [OVERALLTOTALRECEIVED],
coalesce(max([DATA].OVERALLNEWCOMMITMENTS_AMOUNT), 0) as [OVERALLTOTALEXPECTED],
-1 as [TOTALAVGGIFT],
coalesce(sum([DATA].PERIODRECEIVED_AMOUNT), 0) as [TOTALRECEIVED],
coalesce(sum([DATA].PERIODNEWCOMMITMENTS_AMOUNT), 0) as [TOTALEXPECTED],
0 as [PERIODNUMDONORS],
null as [APPEALNAME],
coalesce(max([DATA].OVERALLPLANNEDGIFT_AMOUNT), 0) as [OVERALLTOTALPLANNEDGIFTS],
coalesce(sum([DATA].PERIODPLANNEDGIFT_AMOUNT), 0) as [TOTALPLANNEDGIFTS],
@CURRENCYID as CURRENCYID,
@CURRENCYISO as CURRENCYISO,
@CURRENCYDECIMALDIGITS as CURRENCYDECIMALDIGITS,
@CURRENCYSYMBOL as CURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
from DESIGNATIONLEVEL with (nolock)
left join [REPORT_CTE] as [DATA] on [DATA].DESIGNATIONLEVELID = DESIGNATIONLEVEL.ID
where (@CATEGORYCODEID is null or DESIGNATIONLEVEL.DESIGNATIONLEVELCATEGORYCODEID = @CATEGORYCODEID)
and (@DESIGNATIONLEVELTYPEID is null or DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = @DESIGNATIONLEVELTYPEID)
and (@SELECTIONID is null or DESIGNATIONLEVEL.ID in (select ID from dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID)))
and (@CURRENTAPPUSERID is null or dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID, DESIGNATIONLEVEL.SITEID) = 1)
group by DESIGNATIONLEVEL.ID, DESIGNATIONLEVEL.NAME, [DATA].PERIODYEAR, [DATA].PERIODSEQUENCE
order by DESIGNATIONLEVEL.NAME, [DATA].PERIODYEAR, [DATA].PERIODSEQUENCE
end