USP_REPORT_DESIGNATIONLEVELREVENUE_BYAPPEAL_2
Returns the appeal 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_BYAPPEAL_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 = 0
)
as
set nocount on;
declare @APPEALOPTION tinyint;
set @APPEALOPTION = @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 @APPEALSPLITS table
(
DESIGNATIONLEVELID uniqueidentifier,
REVENUEID uniqueidentifier,
REVENUESPLITID uniqueidentifier,
REVENUESPLITAMOUNT money,
DESIGNATIONID uniqueidentifier,
APPEALID uniqueidentifier,
TRANSACTIONTYPECODE tinyint,
SPLITTYPECODE tinyint,
APPLICATIONCODE tinyint,
BASECURRENCYID uniqueidentifier
);
declare @OVERALLSPLITS table
(
DESIGNATIONLEVELID uniqueidentifier,
REVENUEID uniqueidentifier,
REVENUESPLITID uniqueidentifier,
REVENUESPLITAMOUNT money,
DESIGNATIONID uniqueidentifier,
APPEALID uniqueidentifier,
TRANSACTIONTYPECODE tinyint,
SPLITTYPECODE tinyint,
APPLICATIONCODE tinyint,
BASECURRENCYID uniqueidentifier
);
if @CURRENCYCODE = 0
begin
insert into @APPEALSPLITS
select
DESIGNATIONLEVEL.ID as [DESIGNATIONLEVELID],
[SPLITS].REVENUEID,
[SPLITS].REVENUESPLITID,
[SPLITS].REVENUESPLITAMOUNT,
[SPLITS].DESIGNATIONID,
[SPLITS].APPEALID,
[SPLITS].TRANSACTIONTYPECODE,
[SPLITS].SPLITTYPECODE,
[SPLITS].APPLICATIONCODE,
DESIGNATIONLEVEL.BASECURRENCYID
from dbo.DESIGNATIONLEVEL with (nolock)
cross apply dbo.UFN_DESIGNATIONLEVEL_REVENUESPLITRECORDSINCURRENCY_2(DESIGNATIONLEVEL.ID, @START, @END, @CAMPAIGNHIERARCHYPATH, @APPEALOPTION, 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);
insert into @OVERALLSPLITS
select
DESIGNATIONLEVEL.ID as [DESIGNATIONLEVELID],
[SPLITS].REVENUEID,
[SPLITS].REVENUESPLITID,
[SPLITS].REVENUESPLITAMOUNT,
[SPLITS].DESIGNATIONID,
[SPLITS].APPEALID,
[SPLITS].TRANSACTIONTYPECODE,
[SPLITS].SPLITTYPECODE,
[SPLITS].APPLICATIONCODE,
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 [APPEALS_CTE] as
(
select distinct
case @APPEALOPTION
when 0 then APPEAL.ID
when 2 then APPEAL.APPEALREPORT1CODEID
end as [APPEALID],
case @APPEALOPTION
when 0 then APPEAL.NAME
when 2 then [APPEALREPORTCODE].DESCRIPTION
end as [APPEALNAME]
from dbo.APPEAL
left join dbo.APPEALREPORT1CODE as [APPEALREPORTCODE] on [APPEALREPORTCODE].ID = APPEAL.APPEALREPORT1CODEID
where @APPEALOPTION in (0,2)
union all
select
BUSINESSUNITCODE.ID as [APPEALID],
BUSINESSUNITCODE.DESCRIPTION as [APPEALNAME]
from dbo.BUSINESSUNITCODE where @APPEALOPTION= 1
),
[OVERALLNEWCOMMITMENTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
null as [APPEALID]
from @OVERALLSPLITS [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],
null as [APPEALID]
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)
),
[APPEALNEWCOMMITMENTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
null as [APPEALID]
from @APPEALSPLITS [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],
null as [APPEALID]
from dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFSPLIT
inner join dbo.FINANCIALTRANSACTION WRITEOFF on WRITEOFFSPLIT.FINANCIALTRANSACTIONID = WRITEOFF.ID
inner join @APPEALSPLITS [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],
null as [APPEALID]
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)
),
[APPEALRECEIVED_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
[SPLITS].APPEALID as [APPEALID]
from @APPEALSPLITS [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],
null as [APPEALID]
from @OVERALLSPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE = 4
),
[APPEALPLANNEDGIFTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
[SPLITS].APPEALID as [APPEALID]
from @APPEALSPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE = 4
),
[REPORT_CTE] as
(
select
[OVERALLRECEIVED_CTE].DESIGNATIONLEVELID,
sum([OVERALLRECEIVED_CTE].REVENUESPLITAMOUNT) as [OVERALLRECEIVED_AMOUNT],
sum(0) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum(0) as [OVERALLPLANNEDGIFT_AMOUNT],
sum(0) as [APPEALRECEIVED_AMOUNT],
sum(0) as [APPEALNEWCOMMITMENTS_AMOUNT],
sum(0) as [APPEALPLANNEDGIFT_AMOUNT],
sum([OVERALLRECEIVED_CTE].GIFTCOUNT) as [OVERALLGIFTCOUNT],
sum(0) as [APPEALGIFTCOUNT],
null as [APPEALID]
from [OVERALLRECEIVED_CTE]
group by DESIGNATIONLEVELID
union all
select
[APPEALRECEIVED_CTE].DESIGNATIONLEVELID,
sum(0) as [OVERALLRECEIVED_AMOUNT],
sum(0) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum(0) as [OVERALLPLANNEDGIFT_AMOUNT],
sum([APPEALRECEIVED_CTE].REVENUESPLITAMOUNT) as [APPEALRECEIVED_AMOUNT],
sum(0) as [APPEALNEWCOMMITMENTS_AMOUNT],
sum(0) as [APPEALPLANNEDGIFT_AMOUNT],
sum(0) as [OVERALLGIFTCOUNT],
sum([APPEALRECEIVED_CTE].GIFTCOUNT) as [APPEALGIFTCOUNT],
[APPEALRECEIVED_CTE].APPEALID
from [APPEALRECEIVED_CTE]
group by DESIGNATIONLEVELID, APPEALID
union all
select
[OVERALLNEWCOMMITMENTS_CTE].DESIGNATIONLEVELID,
sum(0) as [OVERALLRECEIVED_AMOUNT],
sum([OVERALLNEWCOMMITMENTS_CTE].REVENUESPLITAMOUNT) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum(0) as [OVERALLPLANNEDGIFT_AMOUNT],
sum(0) as [APPEALRECEIVED_AMOUNT],
sum(0) as [APPEALNEWCOMMITMENTS_AMOUNT],
sum(0) as [APPEALPLANNEDGIFT_AMOUNT],
sum([OVERALLNEWCOMMITMENTS_CTE].GIFTCOUNT) as [OVERALLGIFTCOUNT],
sum(0) as [APPEALGIFTCOUNT],
null as [APPEALID]
from [OVERALLNEWCOMMITMENTS_CTE]
group by DESIGNATIONLEVELID
union all
select
[APPEALNEWCOMMITMENTS_CTE].DESIGNATIONLEVELID,
sum(0) as [OVERALLRECEIVED_AMOUNT],
sum(0) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum(0) as [OVERALLPLANNEDGIFT_AMOUNT],
sum(0) as [APPEALRECEIVED_AMOUNT],
sum([APPEALNEWCOMMITMENTS_CTE].REVENUESPLITAMOUNT) as [APPEALNEWCOMMITMENTS_AMOUNT],
sum(0) as [APPEALPLANNEDGIFT_AMOUNT],
sum(0) as [OVERALLGIFTCOUNT],
sum([APPEALNEWCOMMITMENTS_CTE].GIFTCOUNT) as [APPEALGIFTCOUNT],
[APPEALNEWCOMMITMENTS_CTE].APPEALID
from [APPEALNEWCOMMITMENTS_CTE]
group by DESIGNATIONLEVELID, APPEALID
union all
select
[OVERALLPLANNEDGIFTS_CTE].DESIGNATIONLEVELID,
sum(0) as [OVERALLRECEIVED_AMOUNT],
sum(0) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum([OVERALLPLANNEDGIFTS_CTE].REVENUESPLITAMOUNT) as [OVERALLPLANNEDGIFT_AMOUNT],
sum(0) as [APPEALRECEIVED_AMOUNT],
sum(0) as [APPEALNEWCOMMITMENTS_AMOUNT],
sum(0) as [APPEALPLANNEDGIFT_AMOUNT],
sum(0) as [OVERALLGIFTCOUNT],
sum(0) as [APPEALGIFTCOUNT],
null as [APPEALID]
from [OVERALLPLANNEDGIFTS_CTE]
group by DESIGNATIONLEVELID
union all
select
[APPEALPLANNEDGIFTS_CTE].DESIGNATIONLEVELID,
sum(0) as [OVERALLRECEIVED_AMOUNT],
sum(0) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum(0) as [OVERALLPLANNEDGIFT_AMOUNT],
sum(0) as [APPEALRECEIVED_AMOUNT],
sum(0) as [APPEALNEWCOMMITMENTS_AMOUNT],
sum([APPEALPLANNEDGIFTS_CTE].REVENUESPLITAMOUNT) as [APPEALPLANNEDGIFT_AMOUNT],
sum(0) as [OVERALLGIFTCOUNT],
sum(0) as [APPEALGIFTCOUNT],
[APPEALPLANNEDGIFTS_CTE].APPEALID
from [APPEALPLANNEDGIFTS_CTE]
group by DESIGNATIONLEVELID, APPEALID
)
select
'http://www.blackbaud.com/DESIGNATIONLEVELID?DESIGNATIONLEVELID=' + cast(DESIGNATIONLEVEL.ID as nvarchar(36)) as [DESIGNATIONLEVELID],
DESIGNATIONLEVEL.NAME,
0 as [SEQUENCE],
YEAR(@END) as [PERIODYEAR],
coalesce(sum([DATA].OVERALLGIFTCOUNT), 0) as [TOTALNUMGIFTS],
-1 as [TOTALDONORS],
-1 as [TOTALRAISED],
coalesce(sum([DATA].APPEALGIFTCOUNT), 0) as [PERIODNUMGIFTS],
-1 as [MAXGIFT],
-1 as [PERIODAVGGIFT],
coalesce(sum([DATA].OVERALLRECEIVED_AMOUNT), 0) + coalesce(sum([DATA].OVERALLNEWCOMMITMENTS_AMOUNT), 0) as [OVERALLTOTALRAISED],
coalesce(sum([DATA].OVERALLRECEIVED_AMOUNT), 0) as [OVERALLTOTALRECEIVED],
coalesce(sum([DATA].OVERALLNEWCOMMITMENTS_AMOUNT), 0) as [OVERALLTOTALEXPECTED],
-1 as [TOTALAVGGIFT],
coalesce(sum([DATA].APPEALRECEIVED_AMOUNT), 0) as [TOTALRECEIVED],
coalesce(sum([DATA].APPEALNEWCOMMITMENTS_AMOUNT), 0) as [TOTALEXPECTED],
0 as [PERIODNUMDONORS],
[APPEALS_CTE].APPEALNAME as [APPEALNAME],
coalesce(sum([DATA].OVERALLPLANNEDGIFT_AMOUNT), 0) as [OVERALLTOTALPLANNEDGIFTS],
coalesce(sum([DATA].APPEALPLANNEDGIFT_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
left join [APPEALS_CTE] on [APPEALS_CTE].APPEALID = [DATA].APPEALID
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, [APPEALS_CTE].APPEALNAME,CURRENCYPROPERTIES.ID,CURRENCYPROPERTIES.ISO4217,
CURRENCYPROPERTIES.DECIMALDIGITS, CURRENCYPROPERTIES.CURRENCYSYMBOL, CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, DESIGNATIONLEVEL.BASECURRENCYID, [DATA].DESIGNATIONLEVELID
order by DESIGNATIONLEVEL.NAME, [APPEALS_CTE].APPEALNAME
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 @APPEALSPLITS
select
DESIGNATIONLEVEL.ID as [DESIGNATIONLEVELID],
[SPLITS].REVENUEID,
[SPLITS].REVENUESPLITID,
[SPLITS].REVENUESPLITAMOUNT,
[SPLITS].DESIGNATIONID,
[SPLITS].APPEALID,
[SPLITS].TRANSACTIONTYPECODE,
[SPLITS].SPLITTYPECODE,
[SPLITS].APPLICATIONCODE,
DESIGNATIONLEVEL.BASECURRENCYID
from dbo.DESIGNATIONLEVEL with (nolock)
cross apply dbo.UFN_DESIGNATIONLEVEL_REVENUESPLITRECORDSINCURRENCY_2(DESIGNATIONLEVEL.ID, @START, @END, @CAMPAIGNHIERARCHYPATH, @APPEALOPTION, @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);
insert into @OVERALLSPLITS
select
DESIGNATIONLEVEL.ID as [DESIGNATIONLEVELID],
[SPLITS].REVENUEID,
[SPLITS].REVENUESPLITID,
[SPLITS].REVENUESPLITAMOUNT,
[SPLITS].DESIGNATIONID,
[SPLITS].APPEALID,
[SPLITS].TRANSACTIONTYPECODE,
[SPLITS].SPLITTYPECODE,
[SPLITS].APPLICATIONCODE,
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 [APPEALS_CTE] as
(
select distinct
case @APPEALOPTION
when 0 then APPEAL.ID
when 2 then APPEAL.APPEALREPORT1CODEID
end as [APPEALID],
case @APPEALOPTION
when 0 then APPEAL.NAME
when 2 then [APPEALREPORTCODE].DESCRIPTION
end as [APPEALNAME]
from dbo.APPEAL
left join dbo.APPEALREPORT1CODE as [APPEALREPORTCODE] on [APPEALREPORTCODE].ID = APPEAL.APPEALREPORT1CODEID
where @APPEALOPTION in (0,2)
union all
select
BUSINESSUNITCODE.ID as [APPEALID],
BUSINESSUNITCODE.DESCRIPTION as [APPEALNAME]
from dbo.BUSINESSUNITCODE where @APPEALOPTION= 1
),
[OVERALLNEWCOMMITMENTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
null as [APPEALID]
from @OVERALLSPLITS [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],
null as [APPEALID]
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)
),
[APPEALNEWCOMMITMENTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
null as [APPEALID]
from @APPEALSPLITS [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],
null as [APPEALID]
from dbo.FINANCIALTRANSACTIONLINEITEM WRITEOFFSPLIT
inner join dbo.FINANCIALTRANSACTION WRITEOFF on WRITEOFFSPLIT.FINANCIALTRANSACTIONID = WRITEOFF.ID
inner join @APPEALSPLITS [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],
null as [APPEALID]
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)
),
[APPEALRECEIVED_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
[SPLITS].APPEALID as [APPEALID]
from @APPEALSPLITS [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],
null as [APPEALID]
from @OVERALLSPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE = 4
),
[APPEALPLANNEDGIFTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT],
[SPLITS].APPEALID as [APPEALID]
from @APPEALSPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE = 4
),
[REPORT_CTE] as
(
select
[OVERALLRECEIVED_CTE].DESIGNATIONLEVELID,
sum([OVERALLRECEIVED_CTE].REVENUESPLITAMOUNT) as [OVERALLRECEIVED_AMOUNT],
sum(0) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum(0) as [OVERALLPLANNEDGIFT_AMOUNT],
sum(0) as [APPEALRECEIVED_AMOUNT],
sum(0) as [APPEALNEWCOMMITMENTS_AMOUNT],
sum(0) as [APPEALPLANNEDGIFT_AMOUNT],
sum([OVERALLRECEIVED_CTE].GIFTCOUNT) as [OVERALLGIFTCOUNT],
sum(0) as [APPEALGIFTCOUNT],
null as [APPEALID]
from [OVERALLRECEIVED_CTE]
group by DESIGNATIONLEVELID
union all
select
[APPEALRECEIVED_CTE].DESIGNATIONLEVELID,
sum(0) as [OVERALLRECEIVED_AMOUNT],
sum(0) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum(0) as [OVERALLPLANNEDGIFT_AMOUNT],
sum([APPEALRECEIVED_CTE].REVENUESPLITAMOUNT) as [APPEALRECEIVED_AMOUNT],
sum(0) as [APPEALNEWCOMMITMENTS_AMOUNT],
sum(0) as [APPEALPLANNEDGIFT_AMOUNT],
sum(0) as [OVERALLGIFTCOUNT],
sum([APPEALRECEIVED_CTE].GIFTCOUNT) as [APPEALGIFTCOUNT],
[APPEALRECEIVED_CTE].APPEALID
from [APPEALRECEIVED_CTE]
group by DESIGNATIONLEVELID, APPEALID
union all
select
[OVERALLNEWCOMMITMENTS_CTE].DESIGNATIONLEVELID,
sum(0) as [OVERALLRECEIVED_AMOUNT],
sum([OVERALLNEWCOMMITMENTS_CTE].REVENUESPLITAMOUNT) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum(0) as [OVERALLPLANNEDGIFT_AMOUNT],
sum(0) as [APPEALRECEIVED_AMOUNT],
sum(0) as [APPEALNEWCOMMITMENTS_AMOUNT],
sum(0) as [APPEALPLANNEDGIFT_AMOUNT],
sum([OVERALLNEWCOMMITMENTS_CTE].GIFTCOUNT) as [OVERALLGIFTCOUNT],
sum(0) as [APPEALGIFTCOUNT],
null as [APPEALID]
from [OVERALLNEWCOMMITMENTS_CTE]
group by DESIGNATIONLEVELID
union all
select
[APPEALNEWCOMMITMENTS_CTE].DESIGNATIONLEVELID,
sum(0) as [OVERALLRECEIVED_AMOUNT],
sum(0) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum(0) as [OVERALLPLANNEDGIFT_AMOUNT],
sum(0) as [APPEALRECEIVED_AMOUNT],
sum([APPEALNEWCOMMITMENTS_CTE].REVENUESPLITAMOUNT) as [APPEALNEWCOMMITMENTS_AMOUNT],
sum(0) as [APPEALPLANNEDGIFT_AMOUNT],
sum(0) as [OVERALLGIFTCOUNT],
sum([APPEALNEWCOMMITMENTS_CTE].GIFTCOUNT) as [APPEALGIFTCOUNT],
[APPEALNEWCOMMITMENTS_CTE].APPEALID
from [APPEALNEWCOMMITMENTS_CTE]
group by DESIGNATIONLEVELID, APPEALID
union all
select
[OVERALLPLANNEDGIFTS_CTE].DESIGNATIONLEVELID,
sum(0) as [OVERALLRECEIVED_AMOUNT],
sum(0) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum([OVERALLPLANNEDGIFTS_CTE].REVENUESPLITAMOUNT) as [OVERALLPLANNEDGIFT_AMOUNT],
sum(0) as [APPEALRECEIVED_AMOUNT],
sum(0) as [APPEALNEWCOMMITMENTS_AMOUNT],
sum(0) as [APPEALPLANNEDGIFT_AMOUNT],
sum(0) as [OVERALLGIFTCOUNT],
sum(0) as [APPEALGIFTCOUNT],
null as [APPEALID]
from [OVERALLPLANNEDGIFTS_CTE]
group by DESIGNATIONLEVELID
union all
select
[APPEALPLANNEDGIFTS_CTE].DESIGNATIONLEVELID,
sum(0) as [OVERALLRECEIVED_AMOUNT],
sum(0) as [OVERALLNEWCOMMITMENTS_AMOUNT],
sum(0) as [OVERALLPLANNEDGIFT_AMOUNT],
sum(0) as [APPEALRECEIVED_AMOUNT],
sum(0) as [APPEALNEWCOMMITMENTS_AMOUNT],
sum([APPEALPLANNEDGIFTS_CTE].REVENUESPLITAMOUNT) as [APPEALPLANNEDGIFT_AMOUNT],
sum(0) as [OVERALLGIFTCOUNT],
sum(0) as [APPEALGIFTCOUNT],
[APPEALPLANNEDGIFTS_CTE].APPEALID
from [APPEALPLANNEDGIFTS_CTE]
group by DESIGNATIONLEVELID, APPEALID
)
select
'http://www.blackbaud.com/DESIGNATIONLEVELID?DESIGNATIONLEVELID=' + cast(DESIGNATIONLEVEL.ID as nvarchar(36)) as [DESIGNATIONLEVELID],
DESIGNATIONLEVEL.NAME,
0 as [SEQUENCE],
YEAR(@END) as [PERIODYEAR],
coalesce(sum([DATA].OVERALLGIFTCOUNT), 0) as [TOTALNUMGIFTS],
-1 as [TOTALDONORS],
-1 as [TOTALRAISED],
coalesce(sum([DATA].APPEALGIFTCOUNT), 0) as [PERIODNUMGIFTS],
-1 as [MAXGIFT],
-1 as [PERIODAVGGIFT],
coalesce(sum([DATA].OVERALLRECEIVED_AMOUNT), 0) + coalesce(sum([DATA].OVERALLNEWCOMMITMENTS_AMOUNT), 0) as [OVERALLTOTALRAISED],
coalesce(sum([DATA].OVERALLRECEIVED_AMOUNT), 0) as [OVERALLTOTALRECEIVED],
coalesce(sum([DATA].OVERALLNEWCOMMITMENTS_AMOUNT), 0) as [OVERALLTOTALEXPECTED],
-1 as [TOTALAVGGIFT],
coalesce(sum([DATA].APPEALRECEIVED_AMOUNT), 0) as [TOTALRECEIVED],
coalesce(sum([DATA].APPEALNEWCOMMITMENTS_AMOUNT), 0) as [TOTALEXPECTED],
0 as [PERIODNUMDONORS],
[APPEALS_CTE].APPEALNAME as [APPEALNAME],
coalesce(sum([DATA].OVERALLPLANNEDGIFT_AMOUNT), 0) as [OVERALLTOTALPLANNEDGIFTS],
coalesce(sum([DATA].APPEALPLANNEDGIFT_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
left join [APPEALS_CTE] on [APPEALS_CTE].APPEALID = [DATA].APPEALID
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, [APPEALS_CTE].APPEALNAME, [DATA].DESIGNATIONLEVELID
order by DESIGNATIONLEVEL.NAME, [APPEALS_CTE].APPEALNAME
end