UFN_DESIGNATION_GETPROGRESSINCURRENCY
This function returns progress information for the fundraising reports.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @DESIGNATIONLEVELID | uniqueidentifier | IN | |
| @GOALID | uniqueidentifier | IN | |
| @REPORTCODE1ID | uniqueidentifier | IN | |
| @REPORTCODE2ID | uniqueidentifier | IN | |
| @ROLLUPTOTALS | bit | IN | |
| @GROUPBY | tinyint | IN | |
| @CURRENCYID | uniqueidentifier | IN |
Definition
Copy
CREATE function [dbo].[UFN_DESIGNATION_GETPROGRESSINCURRENCY]
(
@DESIGNATIONLEVELID uniqueidentifier,
@GOALID uniqueidentifier,
@REPORTCODE1ID uniqueidentifier = null,
@REPORTCODE2ID uniqueidentifier = null,
@ROLLUPTOTALS bit = 0,
@GROUPBY tinyint = null,
@CURRENCYID uniqueidentifier = null
)
returns @DESIGNATIONPROGRESS table
(
DESIGNATIONID uniqueidentifier NOT NULL,
LEVEL1NAME nvarchar(100),
LEVEL2NAME nvarchar(100),
LEVEL3NAME nvarchar(100),
LEVEL4NAME nvarchar(100),
LEVEL5NAME nvarchar(100),
DESIGNATIONLEVEL1ID uniqueidentifier,
DESIGNATIONLEVEL2ID uniqueidentifier,
DESIGNATIONLEVEL3ID uniqueidentifier,
DESIGNATIONLEVEL4ID uniqueidentifier,
DESIGNATIONLEVEL5ID uniqueidentifier,
DESIGNATIONLEVELTYPE nvarchar(100),
LASTCATEGORYCODEID uniqueidentifier,
LASTTYPEID uniqueidentifier,
LASTTYPE nvarchar(100),
REPORTCODE1 nvarchar(100),
REPORTCODE2 nvarchar(100),
DESIGNATIONGOAL money,
OVERALLGOAL money,
GOALNAME nvarchar(100),
STARTDATE datetime,
ENDDATE datetime,
DATERANGE nvarchar(24),
TOTALEXPECTED money,
TOTALRECEIVED money,
TOTALGIFTS int,
TOTALDONORS int,
APPEALNAME nvarchar(100),
APPEALRECEIVED money,
APPEALEXPECTED money,
APPEALNUMGIFTS int,
APPEALNUMDONORS int,
APPEALMAXGIFT money,
APPEALPLANNEDGIFT money,
TOTALPLANNEDGIFT money,
APPEALCURRENCYID uniqueidentifier,
APPEALCURRENCYISO nvarchar(3),
APPEALCURRENCYDECIMALDIGITS tinyint,
APPEALCURRENCYSYMBOL nvarchar(5),
APPEALCURRENCYSYMBOLDISPLAYSETTINGCODE tinyint,
DESIGNATIONCURRENCYID uniqueidentifier,
DESIGNATIONCURRENCYISO nvarchar(3),
DESIGNATIONCURRENCYDECIMALDIGITS tinyint,
DESIGNATIONCURRENCYSYMBOL nvarchar(5),
DESIGNATIONCURRENCYSYMBOLDISPLAYSETTINGCODE tinyint,
ISACTIVE bit
)
as
begin
declare @STARTDATE datetime;
declare @ENDDATE datetime;
select @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(STARTDATE),
@ENDDATE = dbo.UFN_DATE_GETLATESTTIME(ENDDATE)
from dbo.DESIGNATIONLEVELGOAL where ID = @GOALID;
if @CURRENCYID is null
-- use each Appeal's base currency, and Designation's base currency
with APPEALRECEIVED_CTE as (
select
case
when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then APPEALBUSINESSUNIT.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end as APPEALID,
APPEALRAISED.DESID,
SUM(APPEALRAISED.DESTOTALRECEIVED) as RECEIVED
from dbo.[UFN_DESIGNATION_RAISEDBYAPPEALNODESNAME_INCURRENCY](@STARTDATE,@ENDDATE,@CURRENCYID) APPEALRAISED
inner join dbo.APPEAL on APPEALRAISED.APPEALID = APPEAL.ID
left join dbo.APPEALBUSINESSUNIT on APPEALBUSINESSUNIT.APPEALID = APPEAL.ID
group by APPEALRAISED.DESID,
case
when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then APPEALBUSINESSUNIT.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end),
APPEALNEWCOMMITMENT_CTE as (
select
case
when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then APPEALBUSINESSUNIT.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end as APPEALID,
APPEALNEWCOMMITMENT.DESID,
sum(APPEALNEWCOMMITMENT.DESTOTALNEWCOMMITMENT) - sum(APPEALNEWCOMMITMENT.DESNEWCOMMITMENTWRITTENOFF) as NEWCOMMITMENT
from dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEBYAPPEALNODESNAME_INCURRENCY(@STARTDATE,@ENDDATE,@CURRENCYID) APPEALNEWCOMMITMENT
inner join dbo.APPEAL on APPEALNEWCOMMITMENT.APPEALID = APPEAL.ID
left join dbo.APPEALBUSINESSUNIT on APPEALBUSINESSUNIT.APPEALID = APPEAL.ID
group by APPEALNEWCOMMITMENT.DESID,
case
when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then APPEALBUSINESSUNIT.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end),
APPEALPLANNEDGIFTREVENUE_CTE as (
select
case
when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then APPEALBUSINESSUNIT.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end as APPEALID,
APPEALPLANNEDGIFT.DESID,
sum(APPEALPLANNEDGIFT.DESTOTALPLANNEDGIFT) as PLANNEDGIFT
from dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUEBYAPPEALNODESNAME_INCURRENCY(@STARTDATE,@ENDDATE,@CURRENCYID) APPEALPLANNEDGIFT
inner join dbo.APPEAL on APPEALPLANNEDGIFT.APPEALID = APPEAL.ID
left join dbo.APPEALBUSINESSUNIT on APPEALBUSINESSUNIT.APPEALID = APPEAL.ID
group by APPEALPLANNEDGIFT.DESID,
case
when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then APPEALBUSINESSUNIT.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end),
APPEALCOUNTS_CTE as (
select
case
when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then APPEALBUSINESSUNIT.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end as APPEALID,
APPEALCOUNTS.DESIGNATIONID,
sum(APPEALCOUNTS.NUMDONORS) AS NUMDONORS,
SUM(APPEALCOUNTS.NUMGIFTS) AS NUMGIFTS,
MAX(APPEALCOUNTS.MAXGIFT) AS MAXGIFT
from dbo.UFN_DESIGNATION_REVENUECOUNTSBYAPPEAL_INCURRENCY_2(@STARTDATE,@ENDDATE,@CURRENCYID) APPEALCOUNTS
inner join dbo.APPEAL on APPEALCOUNTS.APPEALID = APPEAL.ID
left join dbo.APPEALBUSINESSUNIT on APPEALBUSINESSUNIT.APPEALID = APPEAL.ID
group by APPEALCOUNTS.DESIGNATIONID,
case
when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then APPEALBUSINESSUNIT.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end),
APPEALS_CTE as (
select distinct
case @GROUPBY
when 1 then APPEAL.ID
when 2 then APPEALBUSINESSUNIT.BUSINESSUNITCODEID
when 3 then APPEAL.APPEALREPORT1CODEID
end as APPEALID,
case @GROUPBY
when 1 then APPEAL.NAME
when 2 then BUSINESSUNITCODE.DESCRIPTION
when 3 then APPEALREPORTCODE.DESCRIPTION
end as APPEALNAME,
coalesce(@CURRENCYID,APPEAL.BASECURRENCYID) as BASECURRENCYID
from dbo.APPEAL
left join dbo.APPEALBUSINESSUNIT on APPEALBUSINESSUNIT.APPEALID = APPEAL.ID
left join dbo.BUSINESSUNITCODE on APPEALBUSINESSUNIT.BUSINESSUNITCODEID = BUSINESSUNITCODE.ID
left join dbo.APPEALREPORT1CODE APPEALREPORTCODE on APPEAL.APPEALREPORT1CODEID = APPEALREPORTCODE.ID)
insert into @DESIGNATIONPROGRESS
select distinct
DESIGNATION.ID DESIGNATIONID,
DESIGNATIONLEVEL.NAME LEVEL1NAME,
dbo.UFN_DESIGNATIONLEVEL_GETNAME(DESIGNATION.DESIGNATIONLEVEL2ID) LEVEL2NAME,
dbo.UFN_DESIGNATIONLEVEL_GETNAME(DESIGNATION.DESIGNATIONLEVEL3ID) LEVEL3NAME,
dbo.UFN_DESIGNATIONLEVEL_GETNAME(DESIGNATION.DESIGNATIONLEVEL4ID) LEVEL4NAME,
dbo.UFN_DESIGNATIONLEVEL_GETNAME(DESIGNATION.DESIGNATIONLEVEL5ID) LEVEL5NAME,
DESIGNATIONLEVEL.ID DESIGNATIONLEVEL1ID,
DESIGNATION.DESIGNATIONLEVEL2ID DESIGNATIONLEVEL2ID,
DESIGNATION.DESIGNATIONLEVEL3ID DESIGNATIONLEVEL3ID,
DESIGNATION.DESIGNATIONLEVEL4ID DESIGNATIONLEVEL4ID,
DESIGNATION.DESIGNATIONLEVEL5ID DESIGNATIONLEVEL5ID,
DESIGNATIONLEVELTYPE.DESCRIPTION DESIGNATIONLEVELTYPE,
COALESCE(DL5.DESIGNATIONLEVELCATEGORYCODEID,DL4.DESIGNATIONLEVELCATEGORYCODEID,DL3.DESIGNATIONLEVELCATEGORYCODEID,DL2.DESIGNATIONLEVELCATEGORYCODEID,DESIGNATIONLEVEL.DESIGNATIONLEVELCATEGORYCODEID) LASTCATEGORYCODEID,
COALESCE(DL5.DESIGNATIONLEVELTYPEID,DL4.DESIGNATIONLEVELTYPEID,DL3.DESIGNATIONLEVELTYPEID,DL2.DESIGNATIONLEVELTYPEID,DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID) LASTTYPEID,
dbo.UFN_DESIGNATIONLEVELTYPE_GETDESCRIPTION(COALESCE(DL5.DESIGNATIONLEVELTYPEID,DL4.DESIGNATIONLEVELTYPEID,DL3.DESIGNATIONLEVELTYPEID,DL2.DESIGNATIONLEVELTYPEID,DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID)) LASTTYPE,
RC1.DESCRIPTION REPORTCODE1,
RC2.DESCRIPTION REPORTCODE2,
case when DESIGNATION.DESIGNATIONLEVEL2ID is null then coalesce(DESIGNATIONLEVELGOAL.GOAL, 0)
else coalesce(DESIGNATIONGOAL.GOAL, 0) end DESIGNATIONGOAL,
ISNULL(DESIGNATIONLEVELGOAL.GOAL, 0) OVERALLGOAL,
DESIGNATIONLEVELGOAL.NAME GOALNAME,
DESIGNATIONLEVELGOAL.STARTDATE STARTDATE,
DESIGNATIONLEVELGOAL.ENDDATE ENDDATE,
ISNULL(CONVERT(nvarchar(10), DESIGNATIONLEVELGOAL.STARTDATE, 101) + ' to ' + CONVERT(nvarchar(10), DESIGNATIONLEVELGOAL.ENDDATE, 101), '') DATERANGE,
(NEWCOMMITMENT.TOTALNEWCOMMITMENT - NEWCOMMITMENT.TOTALWRITEOFFS) TOTALEXPECTED,
RECEIVED.TOTALRECEIVED TOTALRECEIVED,
REVCOUNTS.NUMGIFTS TOTALGIFTS,
REVCOUNTS.NUMDONORS TOTALDONORS,
APPEALSGROUPED.APPEALNAME,
coalesce(APPEALRECEIVED_CTE.RECEIVED, 0) as APPEALRECEIVED,
coalesce(APPEALNEWCOMMITMENT_CTE.NEWCOMMITMENT, 0) as APPEALEXPECTED,
coalesce(APPEALCOUNTS_CTE.NUMGIFTS, 0) as APPEALNUMGIFTS,
coalesce(APPEALCOUNTS_CTE.NUMDONORS, 0) as APPEALNUMDONORS,
coalesce(APPEALCOUNTS_CTE.MAXGIFT, 0) as APPEALMAXGIFT,
coalesce(APPEALPLANNEDGIFTREVENUE_CTE.PLANNEDGIFT, 0) as APPEALPLANNEDGIFT,
coalesce(PLANNEDGIFT.TOTALPLANNEDGIFT, 0) TOTALPLANNEDGIFT,
APPEALCURRENCYPROPERTIES.ID as APPEALCURRENCYID,
APPEALCURRENCYPROPERTIES.ISO4217 as APPEALCURRENCYISO,
APPEALCURRENCYPROPERTIES.DECIMALDIGITS as APPEALCURRENCYDECIMALDIGITS,
APPEALCURRENCYPROPERTIES.CURRENCYSYMBOL as APPEALCURRENCYSYMBOL,
APPEALCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as APPEALCURRENCYSYMBOLDISPLAYSETTINGCODE,
DESIGNATIONCURRENCYPROPERTIES.ID as DESIGNATIONCURRENCYID,
DESIGNATIONCURRENCYPROPERTIES.ISO4217 as DESIGNATIONCURRENCYISO,
DESIGNATIONCURRENCYPROPERTIES.DECIMALDIGITS as DESIGNATIONCURRENCYDECIMALDIGITS,
DESIGNATIONCURRENCYPROPERTIES.CURRENCYSYMBOL as DESIGNATIONCURRENCYSYMBOL,
DESIGNATIONCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as DESIGNATIONCURRENCYSYMBOLDISPLAYSETTINGCODE,
DESIGNATION.ISACTIVE
from APPEALS_CTE cross join
dbo.DESIGNATIONLEVEL
inner join dbo.DESIGNATIONLEVELTYPE on DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = DESIGNATIONLEVELTYPE.ID
inner join dbo.DESIGNATION on DESIGNATIONLEVEL.ID = DESIGNATION.DESIGNATIONLEVEL1ID
left join dbo.DESIGNATIONLEVELGOAL on DESIGNATIONLEVEL.ID = DESIGNATIONLEVELGOAL.DESIGNATIONLEVELID
left join dbo.DESIGNATIONGOAL on DESIGNATIONLEVELGOAL.ID = DESIGNATIONGOAL.DESIGNATIONLEVELGOALID and DESIGNATION.ID = DESIGNATIONGOAL.DESIGNATIONID
left join dbo.DESIGNATIONREPORT1CODE as RC1 on DESIGNATION.DESIGNATIONREPORT1CODEID = RC1.ID
left join dbo.DESIGNATIONREPORT2CODE as RC2 on DESIGNATION.DESIGNATIONREPORT2CODEID = RC2.ID
left join dbo.DESIGNATIONLEVEL DL2 on DESIGNATION.DESIGNATIONLEVEL2ID = DL2.ID
left join dbo.DESIGNATIONLEVEL DL3 on DESIGNATION.DESIGNATIONLEVEL3ID = DL3.ID
left join dbo.DESIGNATIONLEVEL DL4 on DESIGNATION.DESIGNATIONLEVEL4ID = DL4.ID
left join dbo.DESIGNATIONLEVEL DL5 on DESIGNATION.DESIGNATIONLEVEL5ID = DL5.ID
left join dbo.UFN_DESIGNATION_REVENUECOUNTS(@STARTDATE,@ENDDATE) REVCOUNTS on DESIGNATION.ID = REVCOUNTS.DESIGNATIONID
outer apply dbo.UFN_DESIGNATION_REVENUERECEIVEDINCURRENCY(@STARTDATE,@ENDDATE,DESIGNATION.BASECURRENCYID,DESIGNATION.ID) RECEIVED
outer apply dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEINCURRENCY(@STARTDATE,@ENDDATE,DESIGNATION.BASECURRENCYID,DESIGNATION.ID) NEWCOMMITMENT
outer apply dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUEINCURRENCY(@STARTDATE,@ENDDATE,DESIGNATION.BASECURRENCYID,DESIGNATION.ID) PLANNEDGIFT
left join APPEALRECEIVED_CTE on DESIGNATION.ID = APPEALRECEIVED_CTE.DESID and APPEALS_CTE.APPEALID = APPEALRECEIVED_CTE.APPEALID
left join APPEALNEWCOMMITMENT_CTE on DESIGNATION.ID = APPEALNEWCOMMITMENT_CTE.DESID and APPEALS_CTE.APPEALID = APPEALNEWCOMMITMENT_CTE.APPEALID
left join APPEALPLANNEDGIFTREVENUE_CTE on DESIGNATION.ID = APPEALPLANNEDGIFTREVENUE_CTE.DESID and APPEALS_CTE.APPEALID = APPEALPLANNEDGIFTREVENUE_CTE.APPEALID
left join APPEALCOUNTS_CTE on DESIGNATION.ID = APPEALCOUNTS_CTE.DESIGNATIONID and APPEALS_CTE.APPEALID = APPEALCOUNTS_CTE.APPEALID
left join APPEALS_CTE APPEALSGROUPED on APPEALSGROUPED.APPEALID = coalesce(APPEALRECEIVED_CTE.APPEALID, APPEALNEWCOMMITMENT_CTE.APPEALID, APPEALPLANNEDGIFTREVENUE_CTE.APPEALID)
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(APPEALS_CTE.BASECURRENCYID) APPEALCURRENCYPROPERTIES
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(DESIGNATION.BASECURRENCYID) DESIGNATIONCURRENCYPROPERTIES
where (DESIGNATIONLEVEL.ID = @DESIGNATIONLEVELID)
and (DESIGNATIONLEVELGOAL.ID = @GOALID or @GOALID is null)
and (RC1.ID = @REPORTCODE1ID or @REPORTCODE1ID is null) and
(RC2.ID = @REPORTCODE2ID or @REPORTCODE2ID is null);
else
begin
declare @CURRENCYISO nvarchar(3);
declare @CURRENCYDECIMALDIGITS tinyint;
declare @CURRENCYSYMBOL nvarchar(5);
declare @CURRENCYSYMBOLDISPLAYSETTINGCODE tinyint;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
declare @ROUNDINGTYPECODE tinyint;
select
@CURRENCYISO = ISO4217,
@CURRENCYDECIMALDIGITS = DECIMALDIGITS,
@CURRENCYSYMBOL = CURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE = SYMBOLDISPLAYSETTINGCODE,
@ROUNDINGTYPECODE = ROUNDINGTYPECODE,
@ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
from dbo.CURRENCY where ID = @CURRENCYID;
with APPEALRECEIVED_CTE as (
select
case
when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then ABU.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end as APPEALID,
AR.DESID,
SUM(AR.DESTOTALRECEIVED) as RECEIVED
from dbo.UFN_DESIGNATION_RAISEDBYAPPEALNODESNAME_INCURRENCY(@STARTDATE,@ENDDATE,@CURRENCYID) AR
inner join dbo.APPEAL on AR.APPEALID = APPEAL.ID
left join dbo.APPEALBUSINESSUNIT ABU on ABU.APPEALID = APPEAL.ID
group by AR.DESID,
case
when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then ABU.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end),
APPEALNEWCOMMITMENT_CTE as (
select
case
when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then ABU.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end as APPEALID,
APPEALNEWCOMMITMENT.DESID,
sum(APPEALNEWCOMMITMENT.DESTOTALNEWCOMMITMENT) - sum(APPEALNEWCOMMITMENT.DESNEWCOMMITMENTWRITTENOFF) as NEWCOMMITMENT
from dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEBYAPPEALNODESNAME_INCURRENCY(@STARTDATE,@ENDDATE,@CURRENCYID) APPEALNEWCOMMITMENT
inner join dbo.APPEAL on APPEALNEWCOMMITMENT.APPEALID = APPEAL.ID
left join dbo.APPEALBUSINESSUNIT ABU on ABU.APPEALID = APPEAL.ID
group by APPEALNEWCOMMITMENT.DESID,
case
when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then ABU.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end),
APPEALPLANNEDGIFTREVENUE_CTE as (
select
case
when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then ABU.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end as APPEALID,
APPEALPLANNEDGIFTREVENUE.DESID,
sum(APPEALPLANNEDGIFTREVENUE.DESTOTALPLANNEDGIFT) as PLANNEDGIFT
from dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUEBYAPPEALNODESNAME_INCURRENCY(@STARTDATE,@ENDDATE,@CURRENCYID) APPEALPLANNEDGIFTREVENUE
inner join dbo.APPEAL on APPEALPLANNEDGIFTREVENUE.APPEALID = APPEAL.ID
left join dbo.APPEALBUSINESSUNIT ABU on ABU.APPEALID = APPEAL.ID
group by APPEALPLANNEDGIFTREVENUE.DESID,
case
when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then ABU.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end),
APPEALCOUNTS_CTE as (
select
case
when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then ABU.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end as APPEALID,
APPEALCOUNTS.DESIGNATIONID,
sum(APPEALCOUNTS.NUMDONORS) AS NUMDONORS,
SUM(APPEALCOUNTS.NUMGIFTS) AS NUMGIFTS,
MAX(APPEALCOUNTS.MAXGIFT) AS MAXGIFT
from dbo.UFN_DESIGNATION_REVENUECOUNTSBYAPPEAL_INCURRENCY_2(@STARTDATE,@ENDDATE,@CURRENCYID) APPEALCOUNTS
inner join dbo.APPEAL on APPEALCOUNTS.APPEALID = APPEAL.ID
left join dbo.APPEALBUSINESSUNIT ABU on ABU.APPEALID = APPEAL.ID
group by APPEALCOUNTS.DESIGNATIONID,
case
when @GROUPBY = 1 then APPEAL.ID
when @GROUPBY = 2 then ABU.BUSINESSUNITCODEID
when @GROUPBY = 3 then APPEAL.APPEALREPORT1CODEID
end),
APPEALS_CTE as (
select distinct
case @GROUPBY
when 1 then APPEAL.ID
when 2 then ABU.BUSINESSUNITCODEID
when 3 then APPEAL.APPEALREPORT1CODEID
end as APPEALID,
case @GROUPBY
when 1 then APPEAL.NAME
when 2 then BUSINESSUNITCODE.DESCRIPTION
when 3 then APPEALREPORTCODE.DESCRIPTION
end as APPEALNAME,
@CURRENCYID as BASECURRENCYID
from dbo.APPEAL
left join dbo.APPEALBUSINESSUNIT ABU on ABU.APPEALID = APPEAL.ID
left join dbo.BUSINESSUNITCODE on ABU.BUSINESSUNITCODEID = BUSINESSUNITCODE.ID
left join dbo.APPEALREPORT1CODE APPEALREPORTCODE on APPEAL.APPEALREPORT1CODEID = APPEALREPORTCODE.ID)
insert into @DESIGNATIONPROGRESS
select distinct
DESIGNATION.ID DESIGNATIONID,
DESIGNATIONLEVEL.NAME LEVEL1NAME,
dbo.UFN_DESIGNATIONLEVEL_GETNAME(DESIGNATION.DESIGNATIONLEVEL2ID) LEVEL2NAME,
dbo.UFN_DESIGNATIONLEVEL_GETNAME(DESIGNATION.DESIGNATIONLEVEL3ID) LEVEL3NAME,
dbo.UFN_DESIGNATIONLEVEL_GETNAME(DESIGNATION.DESIGNATIONLEVEL4ID) LEVEL4NAME,
dbo.UFN_DESIGNATIONLEVEL_GETNAME(DESIGNATION.DESIGNATIONLEVEL5ID) LEVEL5NAME,
DESIGNATIONLEVEL.ID DESIGNATIONLEVEL1ID,
DESIGNATION.DESIGNATIONLEVEL2ID DESIGNATIONLEVEL2ID,
DESIGNATION.DESIGNATIONLEVEL3ID DESIGNATIONLEVEL3ID,
DESIGNATION.DESIGNATIONLEVEL4ID DESIGNATIONLEVEL4ID,
DESIGNATION.DESIGNATIONLEVEL5ID DESIGNATIONLEVEL5ID,
DESIGNATIONLEVELTYPE.DESCRIPTION DESIGNATIONLEVELTYPE,
COALESCE(DL5.DESIGNATIONLEVELCATEGORYCODEID,DL4.DESIGNATIONLEVELCATEGORYCODEID,DL3.DESIGNATIONLEVELCATEGORYCODEID,DL2.DESIGNATIONLEVELCATEGORYCODEID,DESIGNATIONLEVEL.DESIGNATIONLEVELCATEGORYCODEID) LASTCATEGORYCODEID,
COALESCE(DL5.DESIGNATIONLEVELTYPEID,DL4.DESIGNATIONLEVELTYPEID,DL3.DESIGNATIONLEVELTYPEID,DL2.DESIGNATIONLEVELTYPEID,DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID) LASTTYPEID,
dbo.UFN_DESIGNATIONLEVELTYPE_GETDESCRIPTION(COALESCE(DL5.DESIGNATIONLEVELTYPEID,DL4.DESIGNATIONLEVELTYPEID,DL3.DESIGNATIONLEVELTYPEID,DL2.DESIGNATIONLEVELTYPEID,DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID)) LASTTYPE,
RC1.DESCRIPTION REPORTCODE1,
RC2.DESCRIPTION REPORTCODE2,
case when DESIGNATION.DESIGNATIONLEVEL2ID is null then coalesce(DESIGNATIONLEVELGOALBULK.GOALINCURRENCY, 0)
else coalesce(DESIGNATIONGOALBULK.GOALINCURRENCY, 0) end DESIGNATIONGOAL,
ISNULL(DESIGNATIONLEVELGOALBULK.GOALINCURRENCY, 0) OVERALLGOAL,
DESIGNATIONLEVELGOAL.NAME GOALNAME,
DESIGNATIONLEVELGOAL.STARTDATE STARTDATE,
DESIGNATIONLEVELGOAL.ENDDATE ENDDATE,
ISNULL(CONVERT(nvarchar(10), DESIGNATIONLEVELGOAL.STARTDATE, 101) + ' to ' + CONVERT(nvarchar(10), DESIGNATIONLEVELGOAL.ENDDATE, 101), '') DATERANGE,
(NEWCOMMITMENT.TOTALNEWCOMMITMENT - NEWCOMMITMENT.TOTALWRITEOFFS) TOTALEXPECTED,
RECEIVED.TOTALRECEIVED TOTALRECEIVED,
REVCOUNTS.NUMGIFTS TOTALGIFTS,
REVCOUNTS.NUMDONORS TOTALDONORS,
APPEALSGROUP.APPEALNAME,
coalesce(APPEALRECEIVED_CTE.RECEIVED, 0) as APPEALRECEIVED,
coalesce(APPEALNEWCOMMITMENT_CTE.NEWCOMMITMENT, 0) as APPEALEXPECTED,
coalesce(APPEALCOUNTS_CTE.NUMGIFTS, 0) as APPEALNUMGIFTS,
coalesce(APPEALCOUNTS_CTE.NUMDONORS, 0) as APPEALNUMDONORS,
coalesce(APPEALCOUNTS_CTE.MAXGIFT, 0) as APPEALMAXGIFT,
coalesce(APPEALPLANNEDGIFTREVENUE_CTE.PLANNEDGIFT, 0) as APPEALPLANNEDGIFT,
coalesce(PLANNEDGIFT.TOTALPLANNEDGIFT, 0) TOTALPLANNEDGIFT,
@CURRENCYID as APPEALCURRENCYID,
@CURRENCYISO as APPEALCURRENCYISO,
@CURRENCYDECIMALDIGITS as APPEALCURRENCYDECIMALDIGITS,
@CURRENCYSYMBOL as APPEALCURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE as APPEALCURRENCYSYMBOLDISPLAYSETTINGCODE,
@CURRENCYID as DESIGNATIONCURRENCYID,
@CURRENCYISO as DESIGNATIONCURRENCYISO,
@CURRENCYDECIMALDIGITS as DESIGNATIONCURRENCYDECIMALDIGITS,
@CURRENCYSYMBOL as DESIGNATIONCURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE as DESIGNATIONCURRENCYSYMBOLDISPLAYSETTINGCODE,
DESIGNATION.ISACTIVE
from APPEALS_CTE cross join
dbo.DESIGNATIONLEVEL
inner join dbo.DESIGNATIONLEVELTYPE on DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = DESIGNATIONLEVELTYPE.ID
inner join dbo.DESIGNATION on DESIGNATIONLEVEL.ID = DESIGNATION.DESIGNATIONLEVEL1ID
left join dbo.DESIGNATIONLEVELGOAL on DESIGNATIONLEVEL.ID = DESIGNATIONLEVELGOAL.DESIGNATIONLEVELID
left join dbo.UFN_DESIGNATIONLEVELGOAL_GETGOALINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @ROUNDINGTYPECODE) DESIGNATIONLEVELGOALBULK on DESIGNATIONLEVELGOAL.ID = DESIGNATIONLEVELGOALBULK.ID
left join dbo.DESIGNATIONGOAL on DESIGNATIONLEVELGOAL.ID = DESIGNATIONGOAL.DESIGNATIONLEVELGOALID and DESIGNATION.ID = DESIGNATIONGOAL.DESIGNATIONID
left join dbo.UFN_DESIGNATIONGOAL_GETGOALINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @CURRENCYDECIMALDIGITS, @ROUNDINGTYPECODE) DESIGNATIONGOALBULK on DESIGNATIONGOAL.ID = DESIGNATIONGOALBULK.ID
left join dbo.DESIGNATIONREPORT1CODE as RC1 on DESIGNATION.DESIGNATIONREPORT1CODEID = RC1.ID
left join dbo.DESIGNATIONREPORT2CODE as RC2 on DESIGNATION.DESIGNATIONREPORT2CODEID = RC2.ID
left join dbo.DESIGNATIONLEVEL DL2 on DESIGNATION.DESIGNATIONLEVEL2ID = DL2.ID
left join dbo.DESIGNATIONLEVEL DL3 on DESIGNATION.DESIGNATIONLEVEL3ID = DL3.ID
left join dbo.DESIGNATIONLEVEL DL4 on DESIGNATION.DESIGNATIONLEVEL4ID = DL4.ID
left join dbo.DESIGNATIONLEVEL DL5 on DESIGNATION.DESIGNATIONLEVEL5ID = DL5.ID
left join dbo.UFN_DESIGNATION_REVENUECOUNTS(@STARTDATE,@ENDDATE) REVCOUNTS on DESIGNATION.ID = REVCOUNTS.DESIGNATIONID
outer apply dbo.UFN_DESIGNATION_REVENUERECEIVEDINCURRENCY(@STARTDATE,@ENDDATE,@CURRENCYID,DESIGNATION.ID) RECEIVED
outer apply dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEINCURRENCY(@STARTDATE,@ENDDATE,@CURRENCYID,DESIGNATION.ID) NEWCOMMITMENT
outer apply dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUEINCURRENCY(@STARTDATE,@ENDDATE,@CURRENCYID,DESIGNATION.ID) PLANNEDGIFT
left join APPEALRECEIVED_CTE on DESIGNATION.ID = APPEALRECEIVED_CTE.DESID and APPEALS_CTE.APPEALID = APPEALRECEIVED_CTE.APPEALID
left join APPEALNEWCOMMITMENT_CTE on DESIGNATION.ID = APPEALNEWCOMMITMENT_CTE.DESID and APPEALS_CTE.APPEALID = APPEALNEWCOMMITMENT_CTE.APPEALID
left join APPEALPLANNEDGIFTREVENUE_CTE on DESIGNATION.ID = APPEALPLANNEDGIFTREVENUE_CTE.DESID and APPEALS_CTE.APPEALID = APPEALPLANNEDGIFTREVENUE_CTE.APPEALID
left join APPEALCOUNTS_CTE on DESIGNATION.ID = APPEALCOUNTS_CTE.DESIGNATIONID and APPEALS_CTE.APPEALID = APPEALCOUNTS_CTE.APPEALID
left join APPEALS_CTE APPEALSGROUP on APPEALSGROUP.APPEALID = coalesce(APPEALRECEIVED_CTE.APPEALID, APPEALNEWCOMMITMENT_CTE.APPEALID, APPEALPLANNEDGIFTREVENUE_CTE.APPEALID)
where (DESIGNATIONLEVEL.ID = @DESIGNATIONLEVELID)
and (DESIGNATIONLEVELGOAL.ID = @GOALID or @GOALID is null)
and (RC1.ID = @REPORTCODE1ID or @REPORTCODE1ID is null) and
(RC2.ID = @REPORTCODE2ID or @REPORTCODE2ID is null);
end
return;
end