USP_REPORT_DESIGNATIONLEVELREVENUE_BYPURPOSE_2
Returns the 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_BYPURPOSE_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;
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 @SPLITS 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 @SPLITS
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 [NEWCOMMITMENTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT]
from @SPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE in (1, 6)
union all
select
[SPLITS].DESIGNATIONLEVELID,
[SPLITS].REVENUEID,
[SPLITS].REVENUESPLITID,
case
when ([SPLITS].BASECURRENCYID = @ORGANIZATIONCURRENCYID)
then WRITEOFFSPLIT.ORGANIZATIONAMOUNT * -1
when [SPLITS].BASECURRENCYID = WRITEOFFSPLIT.BASECURRENCYID
then WRITEOFFSPLIT.AMOUNT * -1
else dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WRITEOFFSPLIT.ID, [SPLITS].BASECURRENCYID) * -1
end as [REVENUESPLITAMOUNT],
0 as [COUNT]
from dbo.WRITEOFFSPLIT
inner join dbo.WRITEOFF on WRITEOFFSPLIT.WRITEOFFID = WRITEOFF.ID
inner join @SPLITS [SPLITS] on [SPLITS].REVENUEID = WRITEOFF.REVENUEID
where [SPLITS].TRANSACTIONTYPECODE in (1,6)
),
[RECEIVED_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT]
from @SPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE = 0
and [SPLITS].APPLICATIONCODE in (0, 1, 3, 6, 7, 10, 13)
and ([SPLITS].APPLICATIONCODE <> 10 or [SPLITS].SPLITTYPECODE = 0)
),
[PLANNEDGIFTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
0 as [GIFTCOUNT]
from @SPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE = 4
),
[REPORT_CTE] as
(
select
[RECEIVED_CTE].DESIGNATIONLEVELID,
sum([RECEIVED_CTE].REVENUESPLITAMOUNT) as [RECEIVED_AMOUNT],
sum(0) as [NEWCOMMITMENTS_AMOUNT],
sum(0) as [PLANNEDGIFT_AMOUNT],
sum([RECEIVED_CTE].GIFTCOUNT) as [GIFTCOUNT]
from [RECEIVED_CTE]
group by DESIGNATIONLEVELID
union all
select
[NEWCOMMITMENTS_CTE].DESIGNATIONLEVELID,
sum(0) as [RECEIVED_AMOUNT],
sum([NEWCOMMITMENTS_CTE].REVENUESPLITAMOUNT) as [NEWCOMMITMENTS_AMOUNT],
sum(0) as [PLANNEDGIFT_AMOUNT],
sum([NEWCOMMITMENTS_CTE].GIFTCOUNT) as [GIFTCOUNT]
from [NEWCOMMITMENTS_CTE]
group by DESIGNATIONLEVELID
union all
select
[PLANNEDGIFTS_CTE].DESIGNATIONLEVELID,
sum(0) as [RECEIVED_AMOUNT],
sum(0) as [NEWCOMMITMENTS_AMOUNT],
sum([PLANNEDGIFTS_CTE].REVENUESPLITAMOUNT) as [PLANNEDGIFT_AMOUNT],
sum([PLANNEDGIFTS_CTE].GIFTCOUNT) as [GIFTCOUNT]
from [PLANNEDGIFTS_CTE]
group by DESIGNATIONLEVELID
)
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].GIFTCOUNT), 0) as [TOTALNUMGIFTS],
-1 as [TOTALDONORS],
-1 as [TOTALRAISED],
-1 as [PERIODNUMGIFTS],
-1 as [MAXGIFT],
-1 as [PERIODAVGGIFT],
coalesce(sum([DATA].RECEIVED_AMOUNT), 0) + coalesce(sum([DATA].NEWCOMMITMENTS_AMOUNT), 0) as [OVERALLTOTALRAISED],
coalesce(sum([DATA].RECEIVED_AMOUNT), 0) as [OVERALLTOTALRECEIVED],
coalesce(sum([DATA].NEWCOMMITMENTS_AMOUNT), 0) as [OVERALLTOTALEXPECTED],
0 as [TOTALAVGGIFT],
0 as [TOTALRECEIVED],
0 as [TOTALEXPECTED],
0 as [PERIODNUMDONORS],
null as [APPEALNAME],
coalesce(sum([DATA].PLANNEDGIFT_AMOUNT), 0) as [OVERALLTOTALPLANNEDGIFTS],
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, DESIGNATIONLEVEL.BASECURRENCYID, CURRENCYPROPERTIES.ID,CURRENCYPROPERTIES.ISO4217,
CURRENCYPROPERTIES.DECIMALDIGITS, CURRENCYPROPERTIES.CURRENCYSYMBOL, CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE
order by NAME;
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 @SPLITS
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 [NEWCOMMITMENTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT]
from @SPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE in (1, 6)
union all
select
[SPLITS].DESIGNATIONLEVELID,
[SPLITS].REVENUEID,
[SPLITS].REVENUESPLITID,
case
when (@CURRENCYID = @ORGANIZATIONCURRENCYID)
then WRITEOFFSPLIT.ORGANIZATIONAMOUNT * -1
when @CURRENCYID = WRITEOFFSPLIT.BASECURRENCYID
then WRITEOFFSPLIT.AMOUNT * -1
else dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WRITEOFFSPLIT.ID, @CURRENCYID) * -1
end as [REVENUESPLITAMOUNT],
0 as [COUNT]
from dbo.WRITEOFFSPLIT
inner join dbo.WRITEOFF on WRITEOFFSPLIT.WRITEOFFID = WRITEOFF.ID
inner join @SPLITS [SPLITS] on [SPLITS].REVENUEID = WRITEOFF.REVENUEID
where [SPLITS].TRANSACTIONTYPECODE in (1,6)
),
[RECEIVED_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT]
from @SPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE = 0
and [SPLITS].APPLICATIONCODE in (0, 1, 6, 7, 3, 10, 13)
and ([SPLITS].APPLICATIONCODE <> 10 or [SPLITS].SPLITTYPECODE = 0)
),
[PLANNEDGIFTS_CTE] as
(
select
[SPLITS].DESIGNATIONLEVELID,
REVENUEID,
REVENUESPLITID,
REVENUESPLITAMOUNT,
1 as [GIFTCOUNT]
from @SPLITS [SPLITS]
where [SPLITS].TRANSACTIONTYPECODE = 4
),
[REPORT_CTE] as
(
select
[RECEIVED_CTE].DESIGNATIONLEVELID,
sum([RECEIVED_CTE].REVENUESPLITAMOUNT) as [RECEIVED_AMOUNT],
sum(0) as [NEWCOMMITMENTS_AMOUNT],
sum(0) as [PLANNEDGIFT_AMOUNT],
sum([RECEIVED_CTE].GIFTCOUNT) as [GIFTCOUNT]
from [RECEIVED_CTE]
group by DESIGNATIONLEVELID
union all
select
[NEWCOMMITMENTS_CTE].DESIGNATIONLEVELID,
sum(0) as [RECEIVED_AMOUNT],
sum([NEWCOMMITMENTS_CTE].REVENUESPLITAMOUNT) as [NEWCOMMITMENTS_AMOUNT],
sum(0) as [PLANNEDGIFT_AMOUNT],
sum([NEWCOMMITMENTS_CTE].GIFTCOUNT) as [GIFTCOUNT]
from [NEWCOMMITMENTS_CTE]
group by DESIGNATIONLEVELID
union all
select
[PLANNEDGIFTS_CTE].DESIGNATIONLEVELID,
sum(0) as [RECEIVED_AMOUNT],
sum(0) as [NEWCOMMITMENTS_AMOUNT],
sum([PLANNEDGIFTS_CTE].REVENUESPLITAMOUNT) as [PLANNEDGIFT_AMOUNT],
sum([PLANNEDGIFTS_CTE].GIFTCOUNT) as [GIFTCOUNT]
from [PLANNEDGIFTS_CTE]
group by DESIGNATIONLEVELID
)
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].GIFTCOUNT), 0) as [TOTALNUMGIFTS],
-1 as [TOTALDONORS],
-1 as [TOTALRAISED],
-1 as [PERIODNUMGIFTS],
-1 as [MAXGIFT],
-1 as [PERIODAVGGIFT],
coalesce(sum([DATA].RECEIVED_AMOUNT), 0) + coalesce(sum([DATA].NEWCOMMITMENTS_AMOUNT), 0) as [OVERALLTOTALRAISED],
coalesce(sum([DATA].RECEIVED_AMOUNT), 0) as [OVERALLTOTALRECEIVED],
coalesce(sum([DATA].NEWCOMMITMENTS_AMOUNT), 0) as [OVERALLTOTALEXPECTED],
0 as [TOTALAVGGIFT],
0 as [TOTALRECEIVED],
0 as [TOTALEXPECTED],
0 as [PERIODNUMDONORS],
null as [APPEALNAME],
coalesce(sum([DATA].PLANNEDGIFT_AMOUNT), 0) as [OVERALLTOTALPLANNEDGIFTS],
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
order by NAME;
end