USP_REPORT_DESIGNATIONPROGRESSBYQUARTER
Returns the data necessary for the Campaign Progress By Quarter report.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @DESIGNATIONLEVELID | uniqueidentifier | IN | |
| @GOALID | uniqueidentifier | IN | |
| @CURRENCYCODE | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_REPORT_DESIGNATIONPROGRESSBYQUARTER
(
@DESIGNATIONLEVELID uniqueidentifier = null,
@GOALID uniqueidentifier = null,
@CURRENCYCODE tinyint = 0
)
as begin
-- If the user has selected "Base" then use the existing logic to return the values
declare @OVERALLTOTALRECEIVED money = 0,
@OVERALLTOTALEXPECTED money = 0,
@OVERALLTOTALPLANNEDGIFT money = 0;
if @CURRENCYCODE = 0
begin
with CTE_OVERALLTOTALS as
(
select
DESIGNATION.ID,
DESIGNATION.DESIGNATIONLEVEL1ID,
DESIGNATION.DESIGNATIONLEVEL2ID,
DESIGNATION.DESIGNATIONLEVEL3ID,
DESIGNATIONLEVELGOAL.ID as DESIGNATIONLEVELGOALID,
DESIGNATIONLEVELTYPE.DESCRIPTION LEVEL1TYPE,
TOTALRECEIVEDREVENUE.TOTALRECEIVED OVERALLTOTALRECEIVED,
(NEWCOMMITMENTREVENUE.TOTALNEWCOMMITMENT - NEWCOMMITMENTREVENUE.TOTALWRITEOFFS) OVERALLTOTALEXPECTED,
PLANNEDGIFTREVENUE.TOTALPLANNEDGIFT OVERALLTOTALPLANNEDGIFT,
dbo.UFN_DESIGNATIONLEVEL_GETNAME(DESIGNATION.DESIGNATIONLEVEL1ID) LEVEL1NAME,
dbo.UFN_DESIGNATIONLEVEL_GETNAME(DESIGNATION.DESIGNATIONLEVEL2ID) LEVEL2NAME,
DESIGNATIONLEVELGOAL.GOAL as DESIGNATIONLEVELGOALGOAL,
DESIGNATIONLEVELGOAL.STARTDATE,
DESIGNATIONLEVELGOAL.ENDDATE,
CURRENCYPROPERTIES.ID as CURRENCYID,
CURRENCYPROPERTIES.ISO4217 as CURRENCYISO,
CURRENCYPROPERTIES.DECIMALDIGITS as CURRENCYDECIMALDIGITS,
CURRENCYPROPERTIES.CURRENCYSYMBOL as CURRENCYSYMBOL,
CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE
from
dbo.DESIGNATION
inner join dbo.DESIGNATIONLEVEL on DESIGNATION.DESIGNATIONLEVEL1ID = DESIGNATIONLEVEL.ID
inner join dbo.DESIGNATIONLEVELTYPE on DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = DESIGNATIONLEVELTYPE.ID
inner join dbo.DESIGNATIONLEVELGOAL on DESIGNATIONLEVEL.ID = DESIGNATIONLEVELGOAL.DESIGNATIONLEVELID
outer apply dbo.UFN_DESIGNATION_TOTALRECEIVED_INCURRENCY(DESIGNATIONLEVELGOAL.STARTDATE, DESIGNATIONLEVELGOAL.ENDDATE, DESIGNATION.BASECURRENCYID, DESIGNATION.ID) TOTALRECEIVEDREVENUE
outer apply dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEINCURRENCY(DESIGNATIONLEVELGOAL.STARTDATE, DESIGNATIONLEVELGOAL.ENDDATE, DESIGNATION.BASECURRENCYID, DESIGNATION.ID) NEWCOMMITMENTREVENUE
outer apply dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUEINCURRENCY(DESIGNATIONLEVELGOAL.STARTDATE, DESIGNATIONLEVELGOAL.ENDDATE, DESIGNATION.BASECURRENCYID, DESIGNATION.ID) PLANNEDGIFTREVENUE
outer apply dbo.UFN_CURRENCY_GETPROPERTIES(DESIGNATION.BASECURRENCYID) CURRENCYPROPERTIES
where DESIGNATIONLEVELGOAL.ID = @GOALID
and DESIGNATION.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID
and DESIGNATION.DESIGNATIONLEVEL3ID is null
)
select
CTE_OVERALLTOTALS.LEVEL1NAME,
CTE_OVERALLTOTALS.LEVEL2NAME,
CTE_OVERALLTOTALS.LEVEL1TYPE,
CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID,
CTE_OVERALLTOTALS.DESIGNATIONLEVEL2ID,
DESIGNATIONGOAL.GOAL DESIGNATIONGOAL,
CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALGOAL OVERALLGOAL,
DESIGNATIONLEVELQUARTERLYGOAL.YEARNAME as DATEYEAR,
DESIGNATIONLEVELQUARTERLYGOAL.QUARTER as DATEQUARTER,
case TOTALQUARTERS when 1 then CTE_OVERALLTOTALS.OVERALLTOTALRECEIVED
else TOTALRECEIVEDREVENUE.TOTALRECEIVED end TOTALRECEIVED,
case TOTALQUARTERS when 1 then CTE_OVERALLTOTALS.OVERALLTOTALEXPECTED
else (NEWCOMMITMENTREVENUE.TOTALNEWCOMMITMENT - NEWCOMMITMENTREVENUE.TOTALWRITEOFFS) end TOTALEXPECTED,
case TOTALQUARTERS when 1 then CTE_OVERALLTOTALS.OVERALLTOTALPLANNEDGIFT
else PLANNEDGIFTREVENUE.TOTALPLANNEDGIFT end TOTALPLANNEDGIFT,
CTE_OVERALLTOTALS.OVERALLTOTALRECEIVED,
CTE_OVERALLTOTALS.OVERALLTOTALEXPECTED,
CTE_OVERALLTOTALS.OVERALLTOTALPLANNEDGIFT,
CTE_OVERALLTOTALS.CURRENCYID,
CTE_OVERALLTOTALS.CURRENCYISO,
CTE_OVERALLTOTALS.CURRENCYDECIMALDIGITS,
CTE_OVERALLTOTALS.CURRENCYSYMBOL,
CTE_OVERALLTOTALS.CURRENCYSYMBOLDISPLAYSETTINGCODE,
CTE_OVERALLTOTALS.ID
from
CTE_OVERALLTOTALS
inner join dbo.UFN_DESIGNATIONLEVELGOAL_GETQUARTERLYGOALS(@GOALID) as DESIGNATIONLEVELQUARTERLYGOAL on CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID = DESIGNATIONLEVELQUARTERLYGOAL.DESIGNATIONLEVELID
left join dbo.DESIGNATIONGOAL on CTE_OVERALLTOTALS.ID = DESIGNATIONGOAL.DESIGNATIONID and CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALID = DESIGNATIONGOAL.DESIGNATIONLEVELGOALID
outer apply dbo.UFN_DESIGNATION_TOTALRECEIVED_INCURRENCY(CTE_OVERALLTOTALS.STARTDATE,(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE)+ 1, 0))), CTE_OVERALLTOTALS.CURRENCYID, CTE_OVERALLTOTALS.ID) TOTALRECEIVEDREVENUE
outer apply dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEINCURRENCY(CTE_OVERALLTOTALS.STARTDATE,(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE)+ 1, 0))), CTE_OVERALLTOTALS.CURRENCYID, CTE_OVERALLTOTALS.ID) NEWCOMMITMENTREVENUE
outer apply dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUEINCURRENCY(CTE_OVERALLTOTALS.STARTDATE,(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE)+ 1, 0))), CTE_OVERALLTOTALS.CURRENCYID, CTE_OVERALLTOTALS.ID) PLANNEDGIFTREVENUE
where CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALID = @GOALID
and CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID
and CTE_OVERALLTOTALS.DESIGNATIONLEVEL3ID is null
and DESIGNATIONLEVELQUARTERLYGOAL.SEQUENCE = 1
union all
select
CTE_OVERALLTOTALS.LEVEL1NAME,
CTE_OVERALLTOTALS.LEVEL2NAME,
CTE_OVERALLTOTALS.LEVEL1TYPE,
CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID,
CTE_OVERALLTOTALS.DESIGNATIONLEVEL2ID,
DESIGNATIONGOAL.GOAL DESIGNATIONGOAL,
CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALGOAL OVERALLGOAL,
DESIGNATIONLEVELQUARTERLYGOAL.YEARNAME as DATEYEAR,
DESIGNATIONLEVELQUARTERLYGOAL.QUARTER as DATEQUARTER,
TOTALRECEIVEDREVENUE.TOTALRECEIVED TOTALRECEIVED,
(NEWCOMMITMENTREVENUE.TOTALNEWCOMMITMENT - NEWCOMMITMENTREVENUE.TOTALWRITEOFFS) TOTALEXPECTED,
PLANNEDGIFTREVENUE.TOTALPLANNEDGIFT TOTALPLANNEDGIFT,
CTE_OVERALLTOTALS.OVERALLTOTALRECEIVED,
CTE_OVERALLTOTALS.OVERALLTOTALEXPECTED,
CTE_OVERALLTOTALS.OVERALLTOTALPLANNEDGIFT,
CTE_OVERALLTOTALS.CURRENCYID,
CTE_OVERALLTOTALS.CURRENCYISO,
CTE_OVERALLTOTALS.CURRENCYDECIMALDIGITS,
CTE_OVERALLTOTALS.CURRENCYSYMBOL,
CTE_OVERALLTOTALS.CURRENCYSYMBOLDISPLAYSETTINGCODE,
CTE_OVERALLTOTALS.ID
from
CTE_OVERALLTOTALS
inner join dbo.UFN_DESIGNATIONLEVELGOAL_GETQUARTERLYGOALS(@GOALID) as DESIGNATIONLEVELQUARTERLYGOAL on CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID = DESIGNATIONLEVELQUARTERLYGOAL.DESIGNATIONLEVELID
left join dbo.DESIGNATIONGOAL on CTE_OVERALLTOTALS.ID = DESIGNATIONGOAL.DESIGNATIONID and CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALID = DESIGNATIONGOAL.DESIGNATIONLEVELGOALID
outer apply dbo.UFN_DESIGNATION_TOTALRECEIVED_INCURRENCY((DATEADD(qq, DATEDIFF(qq,0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE), 0)),CTE_OVERALLTOTALS.ENDDATE, CTE_OVERALLTOTALS.CURRENCYID, CTE_OVERALLTOTALS.ID) TOTALRECEIVEDREVENUE
outer apply dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEINCURRENCY((DATEADD(qq, DATEDIFF(qq,0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE), 0)),CTE_OVERALLTOTALS.ENDDATE, CTE_OVERALLTOTALS.CURRENCYID, CTE_OVERALLTOTALS.ID) NEWCOMMITMENTREVENUE
outer apply dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUEINCURRENCY((DATEADD(qq, DATEDIFF(qq,0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE), 0)),CTE_OVERALLTOTALS.ENDDATE, CTE_OVERALLTOTALS.CURRENCYID, CTE_OVERALLTOTALS.ID) PLANNEDGIFTREVENUE
where CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALID = @GOALID
and CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID
and CTE_OVERALLTOTALS.DESIGNATIONLEVEL3ID is null
and (DESIGNATIONLEVELQUARTERLYGOAL.SEQUENCE = DESIGNATIONLEVELQUARTERLYGOAL.TOTALQUARTERS
and DESIGNATIONLEVELQUARTERLYGOAL.SEQUENCE <> 1)
union all
select
CTE_OVERALLTOTALS.LEVEL1NAME,
CTE_OVERALLTOTALS.LEVEL2NAME,
CTE_OVERALLTOTALS.LEVEL1TYPE,
CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID,
CTE_OVERALLTOTALS.DESIGNATIONLEVEL2ID,
DESIGNATIONGOAL.GOAL DESIGNATIONGOAL,
CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALGOAL OVERALLGOAL,
DESIGNATIONLEVELQUARTERLYGOAL.YEARNAME as DATEYEAR,
DESIGNATIONLEVELQUARTERLYGOAL.QUARTER as DATEQUARTER,
TOTALRECEIVEDREVENUE.TOTALRECEIVED TOTALRECEIVED,
(NEWCOMMITMENTREVENUE.TOTALNEWCOMMITMENT - NEWCOMMITMENTREVENUE.TOTALWRITEOFFS) TOTALEXPECTED,
PLANNEDGIFTREVENUE.TOTALPLANNEDGIFT TOTALPLANNEDGIFT,
CTE_OVERALLTOTALS.OVERALLTOTALRECEIVED,
CTE_OVERALLTOTALS.OVERALLTOTALEXPECTED,
CTE_OVERALLTOTALS.OVERALLTOTALPLANNEDGIFT,
CTE_OVERALLTOTALS.CURRENCYID,
CTE_OVERALLTOTALS.CURRENCYISO,
CTE_OVERALLTOTALS.CURRENCYDECIMALDIGITS,
CTE_OVERALLTOTALS.CURRENCYSYMBOL,
CTE_OVERALLTOTALS.CURRENCYSYMBOLDISPLAYSETTINGCODE,
CTE_OVERALLTOTALS.ID
from
CTE_OVERALLTOTALS
inner join dbo.UFN_DESIGNATIONLEVELGOAL_GETQUARTERLYGOALS(@GOALID) as DESIGNATIONLEVELQUARTERLYGOAL on CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID = DESIGNATIONLEVELQUARTERLYGOAL.DESIGNATIONLEVELID
left join dbo.DESIGNATIONGOAL on CTE_OVERALLTOTALS.ID = DESIGNATIONGOAL.DESIGNATIONID and CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALID = DESIGNATIONGOAL.DESIGNATIONLEVELGOALID
outer apply dbo.UFN_DESIGNATION_TOTALRECEIVED_INCURRENCY((DATEADD(qq, DATEDIFF(qq,0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE), 0)),(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE)+ 1, 0))), CTE_OVERALLTOTALS.CURRENCYID, CTE_OVERALLTOTALS.ID) TOTALRECEIVEDREVENUE
outer apply dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEINCURRENCY((DATEADD(qq, DATEDIFF(qq,0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE), 0)),(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE)+ 1, 0))), CTE_OVERALLTOTALS.CURRENCYID, CTE_OVERALLTOTALS.ID) NEWCOMMITMENTREVENUE
outer apply dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUEINCURRENCY((DATEADD(qq, DATEDIFF(qq,0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE), 0)),(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE)+ 1, 0))), CTE_OVERALLTOTALS.CURRENCYID, CTE_OVERALLTOTALS.ID) PLANNEDGIFTREVENUE
where CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALID = @GOALID
and CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID
and CTE_OVERALLTOTALS.DESIGNATIONLEVEL3ID is null
and (DESIGNATIONLEVELQUARTERLYGOAL.SEQUENCE <> DESIGNATIONLEVELQUARTERLYGOAL.TOTALQUARTERS
and DESIGNATIONLEVELQUARTERLYGOAL.SEQUENCE <> 1)
order by CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID, CTE_OVERALLTOTALS.DESIGNATIONLEVEL2ID, DESIGNATIONLEVELQUARTERLYGOAL.YEARNAME, DESIGNATIONLEVELQUARTERLYGOAL.QUARTER
end
else -- The user has selected "Organization". Use the "INCURRENCY" function(s) to return the converted amounts
begin
-- Return all data in Organization currency
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 = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
with CTE_OVERALLTOTALS as
(
select
DESIGNATION.ID,
DESIGNATION.DESIGNATIONLEVEL1ID,
DESIGNATION.DESIGNATIONLEVEL2ID,
DESIGNATION.DESIGNATIONLEVEL3ID,
DESIGNATIONLEVELGOAL.ID as DESIGNATIONLEVELGOALID,
DESIGNATIONLEVELTYPE.DESCRIPTION LEVEL1TYPE,
TOTALRECEIVEDREVENUE.TOTALRECEIVED OVERALLTOTALRECEIVED,
(NEWCOMMITMENTREVENUE.TOTALNEWCOMMITMENT - NEWCOMMITMENTREVENUE.TOTALWRITEOFFS) OVERALLTOTALEXPECTED,
PLANNEDGIFTREVENUE.TOTALPLANNEDGIFT OVERALLTOTALPLANNEDGIFT,
dbo.UFN_DESIGNATIONLEVEL_GETNAME(DESIGNATION.DESIGNATIONLEVEL1ID) LEVEL1NAME,
dbo.UFN_DESIGNATIONLEVEL_GETNAME(DESIGNATION.DESIGNATIONLEVEL2ID) LEVEL2NAME,
DESIGNATIONLEVELGOAL.ORGANIZATIONGOAL as DESIGNATIONLEVELGOALGOAL,
DESIGNATIONLEVELGOAL.STARTDATE,
DESIGNATIONLEVELGOAL.ENDDATE
from
dbo.DESIGNATION
inner join dbo.DESIGNATIONLEVEL on DESIGNATION.DESIGNATIONLEVEL1ID = DESIGNATIONLEVEL.ID
inner join dbo.DESIGNATIONLEVELTYPE on DESIGNATIONLEVEL.DESIGNATIONLEVELTYPEID = DESIGNATIONLEVELTYPE.ID
inner join dbo.DESIGNATIONLEVELGOAL on DESIGNATIONLEVEL.ID = DESIGNATIONLEVELGOAL.DESIGNATIONLEVELID
outer apply dbo.UFN_DESIGNATION_TOTALRECEIVED_INCURRENCY(DESIGNATIONLEVELGOAL.STARTDATE,DESIGNATIONLEVELGOAL.ENDDATE, @CURRENCYID, DESIGNATION.ID) TOTALRECEIVEDREVENUE
outer apply dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEINCURRENCY(DESIGNATIONLEVELGOAL.STARTDATE, DESIGNATIONLEVELGOAL.ENDDATE, @CURRENCYID, DESIGNATION.ID) NEWCOMMITMENTREVENUE
outer apply dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUEINCURRENCY(DESIGNATIONLEVELGOAL.STARTDATE, DESIGNATIONLEVELGOAL.ENDDATE, @CURRENCYID, DESIGNATION.ID) PLANNEDGIFTREVENUE
where DESIGNATIONLEVELGOAL.ID = @GOALID
and DESIGNATION.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID
and DESIGNATION.DESIGNATIONLEVEL3ID is null
)
select
CTE_OVERALLTOTALS.LEVEL1NAME,
CTE_OVERALLTOTALS.LEVEL2NAME,
CTE_OVERALLTOTALS.LEVEL1TYPE,
CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID,
CTE_OVERALLTOTALS.DESIGNATIONLEVEL2ID,
DESIGNATIONGOAL.GOAL DESIGNATIONGOAL,
CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALGOAL OVERALLGOAL,
DESIGNATIONLEVELQUARTERLYGOAL.YEARNAME as DATEYEAR,
DESIGNATIONLEVELQUARTERLYGOAL.QUARTER as DATEQUARTER,
case TOTALQUARTERS when 1 then CTE_OVERALLTOTALS.OVERALLTOTALRECEIVED
else TOTALRECEIVEDREVENUE.TOTALRECEIVED end TOTALRECEIVED,
case TOTALQUARTERS when 1 then CTE_OVERALLTOTALS.OVERALLTOTALEXPECTED
else (NEWCOMMITMENTREVENUE.TOTALNEWCOMMITMENT - NEWCOMMITMENTREVENUE.TOTALWRITEOFFS) end TOTALEXPECTED,
case TOTALQUARTERS when 1 then CTE_OVERALLTOTALS.OVERALLTOTALPLANNEDGIFT
else PLANNEDGIFTREVENUE.TOTALPLANNEDGIFT end TOTALPLANNEDGIFT,
CTE_OVERALLTOTALS.OVERALLTOTALRECEIVED,
CTE_OVERALLTOTALS.OVERALLTOTALEXPECTED,
CTE_OVERALLTOTALS.OVERALLTOTALPLANNEDGIFT,
@CURRENCYID as CURRENCYID,
@CURRENCYISO as CURRENCYISO,
@CURRENCYDECIMALDIGITS as CURRENCYDECIMALDIGITS,
@CURRENCYSYMBOL as CURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
CTE_OVERALLTOTALS.ID
from
CTE_OVERALLTOTALS
inner join dbo.UFN_DESIGNATIONLEVELGOAL_GETQUARTERLYGOALS(@GOALID) as DESIGNATIONLEVELQUARTERLYGOAL on CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID = DESIGNATIONLEVELQUARTERLYGOAL.DESIGNATIONLEVELID
left join dbo.DESIGNATIONGOAL on CTE_OVERALLTOTALS.ID = DESIGNATIONGOAL.DESIGNATIONID and CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALID = DESIGNATIONGOAL.DESIGNATIONLEVELGOALID
outer apply dbo.UFN_DESIGNATION_TOTALRECEIVED_INCURRENCY(CTE_OVERALLTOTALS.STARTDATE,(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE)+ 1, 0))), @CURRENCYID, CTE_OVERALLTOTALS.ID) TOTALRECEIVEDREVENUE
outer apply dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEINCURRENCY(CTE_OVERALLTOTALS.STARTDATE,(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE)+ 1, 0))), @CURRENCYID, CTE_OVERALLTOTALS.ID) NEWCOMMITMENTREVENUE
outer apply dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUEINCURRENCY(CTE_OVERALLTOTALS.STARTDATE,(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE)+ 1, 0))), @CURRENCYID, CTE_OVERALLTOTALS.ID) PLANNEDGIFTREVENUE
where CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALID = @GOALID
and CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID
and CTE_OVERALLTOTALS.DESIGNATIONLEVEL3ID is null
and DESIGNATIONLEVELQUARTERLYGOAL.SEQUENCE = 1
union all
select
CTE_OVERALLTOTALS.LEVEL1NAME,
CTE_OVERALLTOTALS.LEVEL2NAME,
CTE_OVERALLTOTALS.LEVEL1TYPE,
CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID,
CTE_OVERALLTOTALS.DESIGNATIONLEVEL2ID,
DESIGNATIONGOAL.GOAL DESIGNATIONGOAL,
CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALGOAL OVERALLGOAL,
DESIGNATIONLEVELQUARTERLYGOAL.YEARNAME as DATEYEAR,
DESIGNATIONLEVELQUARTERLYGOAL.QUARTER as DATEQUARTER,
TOTALRECEIVEDREVENUE.TOTALRECEIVED TOTALRECEIVED,
(NEWCOMMITMENTREVENUE.TOTALNEWCOMMITMENT - NEWCOMMITMENTREVENUE.TOTALWRITEOFFS) TOTALEXPECTED,
PLANNEDGIFTREVENUE.TOTALPLANNEDGIFT TOTALPLANNEDGIFT,
CTE_OVERALLTOTALS.OVERALLTOTALRECEIVED,
CTE_OVERALLTOTALS.OVERALLTOTALEXPECTED,
CTE_OVERALLTOTALS.OVERALLTOTALPLANNEDGIFT,
@CURRENCYID as CURRENCYID,
@CURRENCYISO as CURRENCYISO,
@CURRENCYDECIMALDIGITS as CURRENCYDECIMALDIGITS,
@CURRENCYSYMBOL as CURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
CTE_OVERALLTOTALS.ID
from
CTE_OVERALLTOTALS
inner join dbo.UFN_DESIGNATIONLEVELGOAL_GETQUARTERLYGOALS(@GOALID) as DESIGNATIONLEVELQUARTERLYGOAL on CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID = DESIGNATIONLEVELQUARTERLYGOAL.DESIGNATIONLEVELID
left join dbo.DESIGNATIONGOAL on CTE_OVERALLTOTALS.ID = DESIGNATIONGOAL.DESIGNATIONID and CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALID = DESIGNATIONGOAL.DESIGNATIONLEVELGOALID
outer apply dbo.UFN_DESIGNATION_TOTALRECEIVED_INCURRENCY((DATEADD(qq, DATEDIFF(qq,0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE), 0)),CTE_OVERALLTOTALS.ENDDATE, @CURRENCYID, CTE_OVERALLTOTALS.ID) TOTALRECEIVEDREVENUE
outer apply dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEINCURRENCY((DATEADD(qq, DATEDIFF(qq,0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE), 0)),CTE_OVERALLTOTALS.ENDDATE, @CURRENCYID, CTE_OVERALLTOTALS.ID) NEWCOMMITMENTREVENUE
outer apply dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUEINCURRENCY((DATEADD(qq, DATEDIFF(qq,0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE), 0)),CTE_OVERALLTOTALS.ENDDATE, @CURRENCYID, CTE_OVERALLTOTALS.ID) PLANNEDGIFTREVENUE
where CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALID = @GOALID
and CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID
and CTE_OVERALLTOTALS.DESIGNATIONLEVEL3ID is null
and (DESIGNATIONLEVELQUARTERLYGOAL.SEQUENCE = DESIGNATIONLEVELQUARTERLYGOAL.TOTALQUARTERS
and DESIGNATIONLEVELQUARTERLYGOAL.SEQUENCE <> 1)
union all
select
CTE_OVERALLTOTALS.LEVEL1NAME,
CTE_OVERALLTOTALS.LEVEL2NAME,
CTE_OVERALLTOTALS.LEVEL1TYPE,
CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID,
CTE_OVERALLTOTALS.DESIGNATIONLEVEL2ID,
DESIGNATIONGOAL.GOAL DESIGNATIONGOAL,
CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALGOAL OVERALLGOAL,
DESIGNATIONLEVELQUARTERLYGOAL.YEARNAME as DATEYEAR,
DESIGNATIONLEVELQUARTERLYGOAL.QUARTER as DATEQUARTER,
TOTALRECEIVEDREVENUE.TOTALRECEIVED TOTALRECEIVED,
(NEWCOMMITMENTREVENUE.TOTALNEWCOMMITMENT - NEWCOMMITMENTREVENUE.TOTALWRITEOFFS) TOTALEXPECTED,
PLANNEDGIFTREVENUE.TOTALPLANNEDGIFT TOTALPLANNEDGIFT,
CTE_OVERALLTOTALS.OVERALLTOTALRECEIVED,
CTE_OVERALLTOTALS.OVERALLTOTALEXPECTED,
CTE_OVERALLTOTALS.OVERALLTOTALPLANNEDGIFT,
@CURRENCYID as CURRENCYID,
@CURRENCYISO as CURRENCYISO,
@CURRENCYDECIMALDIGITS as CURRENCYDECIMALDIGITS,
@CURRENCYSYMBOL as CURRENCYSYMBOL,
@CURRENCYSYMBOLDISPLAYSETTINGCODE as CURRENCYSYMBOLDISPLAYSETTINGCODE,
CTE_OVERALLTOTALS.ID
from
CTE_OVERALLTOTALS
inner join dbo.UFN_DESIGNATIONLEVELGOAL_GETQUARTERLYGOALS(@GOALID) as DESIGNATIONLEVELQUARTERLYGOAL on CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID = DESIGNATIONLEVELQUARTERLYGOAL.DESIGNATIONLEVELID
left join dbo.DESIGNATIONGOAL on CTE_OVERALLTOTALS.ID = DESIGNATIONGOAL.DESIGNATIONID and CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALID = DESIGNATIONGOAL.DESIGNATIONLEVELGOALID
outer apply dbo.UFN_DESIGNATION_TOTALRECEIVED_INCURRENCY((DATEADD(qq, DATEDIFF(qq,0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE), 0)),(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE)+ 1, 0))), @CURRENCYID, CTE_OVERALLTOTALS.ID) TOTALRECEIVEDREVENUE
outer apply dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUEINCURRENCY((DATEADD(qq, DATEDIFF(qq,0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE), 0)),(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE)+ 1, 0))), @CURRENCYID, CTE_OVERALLTOTALS.ID) NEWCOMMITMENTREVENUE
outer apply dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUEINCURRENCY((DATEADD(qq, DATEDIFF(qq,0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE), 0)),(DATEADD(ms, -3, DATEADD(qq,DATEDIFF(qq, 0,DESIGNATIONLEVELQUARTERLYGOAL.STARTDATE)+ 1, 0))), @CURRENCYID, CTE_OVERALLTOTALS.ID) PLANNEDGIFTREVENUE
where CTE_OVERALLTOTALS.DESIGNATIONLEVELGOALID = @GOALID
and CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID
and CTE_OVERALLTOTALS.DESIGNATIONLEVEL3ID is null
and (DESIGNATIONLEVELQUARTERLYGOAL.SEQUENCE <> DESIGNATIONLEVELQUARTERLYGOAL.TOTALQUARTERS
and DESIGNATIONLEVELQUARTERLYGOAL.SEQUENCE <> 1)
order by CTE_OVERALLTOTALS.DESIGNATIONLEVEL1ID, CTE_OVERALLTOTALS.DESIGNATIONLEVEL2ID, DESIGNATIONLEVELQUARTERLYGOAL.YEARNAME, DESIGNATIONLEVELQUARTERLYGOAL.QUARTER
end
end