USP_REPORT_CAMPAIGNRECOGNITIONCREDIT
Generates data on campaign recognition credits.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CAMPAIGNID | uniqueidentifier | IN | |
| @GROUPBY | tinyint | IN | |
| @GOALID | uniqueidentifier | IN | |
| @RECOGNITIONTYPEID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_REPORT_CAMPAIGNRECOGNITIONCREDIT
(
@CAMPAIGNID uniqueidentifier,
@GROUPBY tinyint,
@GOALID uniqueidentifier,
@RECOGNITIONTYPEID uniqueidentifier
)
as
set nocount on;
--Retrieve campaign goal information we'll need later
declare @STARTDATE datetime;
declare @ENDDATE datetime;
declare @CAMPAIGNGOAL money;
declare @PARAMGOALNAME varchar(100);
declare @CAMPAIGNHIERARCHYID uniqueidentifier;
select
@STARTDATE=STARTDATE,
@ENDDATE=ENDDATE,
@CAMPAIGNGOAL=AMOUNT,
@PARAMGOALNAME=NAME,
@CAMPAIGNHIERARCHYID = CAMPAIGNID
from CAMPAIGNHIERARCHYGOAL
where ID=@GOALID;
--If campaign isn't the top of the hierarchy, get its goal amount
if @CAMPAIGNHIERARCHYID <> @CAMPAIGNID
begin
set @CAMPAIGNGOAL=null
select @CAMPAIGNGOAL=AMOUNT
from CAMPAIGNGOAL
where CAMPAIGNID=@CAMPAIGNID and CAMPAIGNHIERARCHYGOALID=@GOALID
end
--Retrieve the name of the type of recognition credits we are filtering by
declare @PARAMRECOGNITIONTYPENAME nvarchar(100)
if @RECOGNITIONTYPEID is null
begin
set @PARAMRECOGNITIONTYPENAME='All'
end
else
begin
select @PARAMRECOGNITIONTYPENAME=DESCRIPTION
from REVENUERECOGNITIONTYPECODE
where ID=@RECOGNITIONTYPEID
end
--Build a table of recognition totals for all campaigns, by time period.
declare @CAMPAIGNRECOGNITION table(
CAMPAIGNID uniqueidentifier,
AMOUNT money,
PERIODYEAR int,
SEQUENCE int
);
-- Handle grouping by quarter or year
if @GROUPBY <> 0
begin
with CTE_ALLOWEDRECOGNITION(REVENUESPLITID, AMOUNT, DATE, ID)
as(
--All recognitions of the appropriate type
select
RECOGNITIONS.REVENUESPLITID,
RECOGNITIONS.AMOUNT,
RECOGNITIONS.EFFECTIVEDATE,
RECOGNITIONS.ID
from dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS(1, @RECOGNITIONTYPEID, null) RECOGNITIONS
)
insert into @CAMPAIGNRECOGNITION(
CAMPAIGNID,
AMOUNT,
PERIODYEAR,
SEQUENCE
)
select
REVENUESPLITCAMPAIGN.CAMPAIGNID,
sum(coalesce(NONPLEDGEREGONITIION.AMOUNT,0))+sum(coalesce(PLEDGEREGONITIION.AMOUNT,0)),
PERIODS.PERIODYEAR,
PERIODS.SEQUENCE
from dbo.UFN_DESIGNATIONREPORT_GETPERIODS(@STARTDATE, @ENDDATE, @GROUPBY) PERIODS
cross join dbo.REVENUESPLITCAMPAIGN
left join ( --All the recognition that has been given on revenue that isn't a pledge or pledge payment
select
REVENUESPLIT.ID SPLITID,
REVENUERECOGNITION.AMOUNT,
REVENUERECOGNITION.DATE
from dbo.REVENUESPLIT
inner join dbo.REVENUE with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join CTE_ALLOWEDRECOGNITION REVENUERECOGNITION on REVENUERECOGNITION.REVENUESPLITID = REVENUESPLIT.ID
where REVENUE.TRANSACTIONTYPECODE=0
and REVENUESPLIT.APPLICATIONCODE not in (2, 6, 7, 8)
) NONPLEDGEREGONITIION
on NONPLEDGEREGONITIION.SPLITID=REVENUESPLITCAMPAIGN.REVENUESPLITID
and NONPLEDGEREGONITIION.DATE >= PERIODS.STARTDATE
and NONPLEDGEREGONITIION.DATE <= PERIODS.ENDDATE
left join ( -- All the recognition that has been given on revenue that is a pledge or pledge payment.
-- Recognitions on pledge payments defer when there exists a recognition on that payment's pledge.
select distinct
coalesce(PLEDGERECOGNITION.ID,PAYMENTRECOGNITION.ID) as ID,
case when PLEDGERECOGNITION.AMOUNT is null then PAYMENTSPLIT.ID else PLEDGESPLIT.ID end SPLITID,
coalesce(PLEDGERECOGNITION.AMOUNT, PAYMENTRECOGNITION.AMOUNT,0) AMOUNT,
coalesce(PLEDGERECOGNITION.DATE, PAYMENTRECOGNITION.DATE) DATE
from dbo.REVENUESPLIT PLEDGESPLIT
inner join dbo.REVENUE PLEDGE with (nolock) on PLEDGE.ID = PLEDGESPLIT.REVENUEID
left join CTE_ALLOWEDRECOGNITION PLEDGERECOGNITION on PLEDGERECOGNITION.REVENUESPLITID = PLEDGESPLIT.ID
left join (
select
INSTALLMENTSPLITPAYMENT.PLEDGEID as PLEDGEID,
INSTALLMENTSPLITPAYMENT.PAYMENTID as PAYMENTID,
INSTALLMENTSPLIT.DESIGNATIONID as DESIGNATIONID
from dbo.INSTALLMENTSPLITPAYMENT
inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID=INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
) INSTALLMENTS on INSTALLMENTS.PLEDGEID=PLEDGE.ID and INSTALLMENTS.DESIGNATIONID=PLEDGESPLIT.DESIGNATIONID
left join dbo.REVENUESPLIT PAYMENTSPLIT on PAYMENTSPLIT.ID=INSTALLMENTS.PAYMENTID
left join CTE_ALLOWEDRECOGNITION PAYMENTRECOGNITION on PAYMENTRECOGNITION.REVENUESPLITID=PAYMENTSPLIT.ID
-- Pledge or matching gift claim
where PLEDGE.TRANSACTIONTYPECODE in (1, 3, 4, 6)
) PLEDGEREGONITIION
on PLEDGEREGONITIION.SPLITID=REVENUESPLITCAMPAIGN.REVENUESPLITID
and PLEDGEREGONITIION.DATE >= PERIODS.STARTDATE
and PLEDGEREGONITIION.DATE <= PERIODS.ENDDATE
where @GROUPBY <> 0
group by
PERIODYEAR,
SEQUENCE,
REVENUESPLITCAMPAIGN.CAMPAIGNID
--Calculate the total recognition associated directly with the parent campaign.
declare @CAMPAIGNAMOUNT money
select @CAMPAIGNAMOUNT=sum(AMOUNT)
from @CAMPAIGNRECOGNITION
where CAMPAIGNID=@CAMPAIGNID
--Build result set.
select
case @GROUPBY
when 2 then 'Quarter'
when 3 then 'Year'
else '' end PARAMGROUPTYPE,
@PARAMGOALNAME PARAMGOALNAME,
@STARTDATE PARAMGOALSTARTDATE,
@ENDDATE PARAMGOALENDDATE,
@PARAMRECOGNITIONTYPENAME PARAMRECOGNITIONTYPENAME,
case @GROUPBY
when 2
then 'Q' + convert(nvarchar(10),PERIODS.SEQUENCE) + ', '
+ convert(nvarchar(10),PERIODS.PERIODYEAR)
when 3
then convert(nvarchar(10),PERIODS.PERIODYEAR)
else '' end PERIOD,
@CAMPAIGNID CAMPAIGNID,
PARENT.NAME CAMPAIGNNAME,
@CAMPAIGNGOAL CAMPAIGNGOAL,
@CAMPAIGNAMOUNT CAMPAIGNAMOUNT,
coalesce(PARENTRECOGNITION.AMOUNT,0) CAMPAIGNAMOUNTBYPERIOD,
CHILD.ID CHILDCAMPAIGNID,
CHILD.NAME CHILDCAMPAIGNNAME,
CAMPAIGNGOAL.AMOUNT CHILDCAMPAIGNGOAL,
sum(coalesce(DESCENDANTRECOGNITION.AMOUNT,0)) CHILDCAMPAIGNAMOUNT
from dbo.UFN_DESIGNATIONREPORT_GETPERIODS(@STARTDATE, @ENDDATE, @GROUPBY) PERIODS
cross join dbo.CAMPAIGN PARENT
left join @CAMPAIGNRECOGNITION PARENTRECOGNITION
on PARENTRECOGNITION.CAMPAIGNID=PARENT.ID
and (PARENTRECOGNITION.PERIODYEAR = PERIODS.PERIODYEAR)
and (PARENTRECOGNITION.SEQUENCE = PERIODS.SEQUENCE)
left join dbo.CAMPAIGN CHILD on CHILD.HIERARCHYPATH.GetAncestor(1) = PARENT.HIERARCHYPATH
left join dbo.CAMPAIGNGOAL
on CAMPAIGNGOAL.CAMPAIGNID=CHILD.ID and CAMPAIGNGOAL.CAMPAIGNHIERARCHYGOALID=@GOALID
left join dbo.CAMPAIGN DESCENDANT on DESCENDANT.HIERARCHYPATH.IsDescendantOf(CHILD.HIERARCHYPATH) = 1
left join @CAMPAIGNRECOGNITION DESCENDANTRECOGNITION
on DESCENDANTRECOGNITION.CAMPAIGNID=DESCENDANT.ID
and (DESCENDANTRECOGNITION.PERIODYEAR = PERIODS.PERIODYEAR)
and (DESCENDANTRECOGNITION.SEQUENCE = PERIODS.SEQUENCE)
where PARENT.ID=@CAMPAIGNID
group by
PERIODS.PERIODYEAR,
PERIODS.SEQUENCE,
CHILD.ID,
CHILD.NAME,
CAMPAIGNGOAL.AMOUNT,
PARENT.NAME,
PARENTRECOGNITION.AMOUNT
order by
PERIODS.PERIODYEAR,
PERIODS.SEQUENCE,
CHILD.NAME;
end
else
begin
with CTE_ALLOWEDRECOGNITION(REVENUESPLITID, AMOUNT, DATE, ID)
as(
--All recognitions of the appropriate type
select
RECOGNITIONS.REVENUESPLITID,
RECOGNITIONS.AMOUNT,
RECOGNITIONS.EFFECTIVEDATE,
RECOGNITIONS.ID
from dbo.UFN_RECOGNITIONCREDIT_GETRECOGNITIONS(1, @RECOGNITIONTYPEID, null) RECOGNITIONS
)
insert into @CAMPAIGNRECOGNITION(
CAMPAIGNID,
AMOUNT,
PERIODYEAR,
SEQUENCE
)
select
REVENUESPLITCAMPAIGN.CAMPAIGNID,
sum(coalesce(NONPLEDGEREGONITIION.AMOUNT,0))+sum(coalesce(PLEDGEREGONITIION.AMOUNT,0)),
0 as PERIODYEAR,
0 as SEQUENCE
from dbo.REVENUESPLITCAMPAIGN
left join ( --All the recognition that has been given on revenue that isn't a pledge, grant award, planned gift payment, matching gift payment, grant award payment, or pledge payment
select
REVENUESPLIT.ID SPLITID,
REVENUERECOGNITION.AMOUNT,
REVENUERECOGNITION.DATE
from dbo.REVENUESPLIT
inner join dbo.REVENUE with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join CTE_ALLOWEDRECOGNITION REVENUERECOGNITION on REVENUERECOGNITION.REVENUESPLITID = REVENUESPLIT.ID
where REVENUE.TRANSACTIONTYPECODE=0
and REVENUESPLIT.APPLICATIONCODE not in (2, 6, 7, 8)
) NONPLEDGEREGONITIION
on NONPLEDGEREGONITIION.SPLITID=REVENUESPLITCAMPAIGN.REVENUESPLITID
and NONPLEDGEREGONITIION.DATE >= @STARTDATE
and NONPLEDGEREGONITIION.DATE <= @ENDDATE
left join ( --All the recognition that has been given on revenue that is a pledge or pledge payment.
-- Recognitions on pledge payments defer when there exists a recognition on that payment's pledge.
select distinct
coalesce(PLEDGERECOGNITION.ID,PAYMENTRECOGNITION.ID) as ID,
case when PLEDGERECOGNITION.AMOUNT is null then PAYMENTSPLIT.ID else PLEDGESPLIT.ID end SPLITID,
coalesce(PLEDGERECOGNITION.AMOUNT, PAYMENTRECOGNITION.AMOUNT,0) AMOUNT,
coalesce(PLEDGERECOGNITION.DATE, PAYMENTRECOGNITION.DATE) DATE
from dbo.REVENUESPLIT PLEDGESPLIT
inner join dbo.REVENUE PLEDGE with (nolock) on PLEDGE.ID = PLEDGESPLIT.REVENUEID
left join CTE_ALLOWEDRECOGNITION PLEDGERECOGNITION on PLEDGERECOGNITION.REVENUESPLITID = PLEDGESPLIT.ID
left join (
select
INSTALLMENTSPLITPAYMENT.PLEDGEID as PLEDGEID,
INSTALLMENTSPLITPAYMENT.PAYMENTID as PAYMENTID,
INSTALLMENTSPLIT.DESIGNATIONID as DESIGNATIONID
from dbo.INSTALLMENTSPLITPAYMENT
inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID=INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
) INSTALLMENTS on INSTALLMENTS.PLEDGEID=PLEDGE.ID and INSTALLMENTS.DESIGNATIONID=PLEDGESPLIT.DESIGNATIONID
left join dbo.REVENUESPLIT PAYMENTSPLIT on PAYMENTSPLIT.ID=INSTALLMENTS.PAYMENTID
left join CTE_ALLOWEDRECOGNITION PAYMENTRECOGNITION on PAYMENTRECOGNITION.REVENUESPLITID=PAYMENTSPLIT.ID
where PLEDGE.TRANSACTIONTYPECODE in (1, 3, 4, 6)
) PLEDGEREGONITIION
on PLEDGEREGONITIION.SPLITID=REVENUESPLITCAMPAIGN.REVENUESPLITID
and PLEDGEREGONITIION.DATE >= @STARTDATE
and PLEDGEREGONITIION.DATE <= @ENDDATE
where @GROUPBY = 0
group by
REVENUESPLITCAMPAIGN.CAMPAIGNID
--Calculate the total recognition associated directly with the parent campaign.
declare @CAMPAIGNAMOUNTNOGROUPING money
select @CAMPAIGNAMOUNTNOGROUPING=sum(AMOUNT)
from @CAMPAIGNRECOGNITION
where CAMPAIGNID=@CAMPAIGNID
--Build result set.
select
'None' as PARAMGROUPTYPE,
@PARAMGOALNAME PARAMGOALNAME,
@STARTDATE PARAMGOALSTARTDATE,
@ENDDATE PARAMGOALENDDATE,
@PARAMRECOGNITIONTYPENAME PARAMRECOGNITIONTYPENAME,
'' as PERIOD,
@CAMPAIGNID CAMPAIGNID,
PARENT.NAME CAMPAIGNNAME,
@CAMPAIGNGOAL CAMPAIGNGOAL,
@CAMPAIGNAMOUNTNOGROUPING CAMPAIGNAMOUNT,
coalesce(PARENTRECOGNITION.AMOUNT,0) CAMPAIGNAMOUNTBYPERIOD,
CHILD.ID CHILDCAMPAIGNID,
CHILD.NAME CHILDCAMPAIGNNAME,
CAMPAIGNGOAL.AMOUNT CHILDCAMPAIGNGOAL,
sum(coalesce(DESCENDANTRECOGNITION.AMOUNT,0)) CHILDCAMPAIGNAMOUNT
from dbo.CAMPAIGN PARENT
left join @CAMPAIGNRECOGNITION PARENTRECOGNITION
on PARENTRECOGNITION.CAMPAIGNID=PARENT.ID
left join dbo.CAMPAIGN CHILD on CHILD.HIERARCHYPATH.GetAncestor(1) = PARENT.HIERARCHYPATH
left join dbo.CAMPAIGNGOAL
on CAMPAIGNGOAL.CAMPAIGNID=CHILD.ID and CAMPAIGNGOAL.CAMPAIGNHIERARCHYGOALID=@GOALID
left join dbo.CAMPAIGN DESCENDANT on DESCENDANT.HIERARCHYPATH.IsDescendantOf(CHILD.HIERARCHYPATH) = 1
left join @CAMPAIGNRECOGNITION DESCENDANTRECOGNITION
on DESCENDANTRECOGNITION.CAMPAIGNID=DESCENDANT.ID
where PARENT.ID=@CAMPAIGNID
group by
CHILD.ID,
CHILD.NAME,
CAMPAIGNGOAL.AMOUNT,
PARENT.NAME,
PARENTRECOGNITION.AMOUNT
order by
CHILD.NAME;
end