USP_DATALIST_CONSTITUENT_REVENUEHISTORY
This datalist returns a filtered list of revenue associated with a constituent.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @REVENUETYPEOPTIONCODE | smallint | IN | Revenue type |
| @DATERANGECODE | smallint | IN | Date range |
| @ISGROUP | bit | IN | |
| @PRESERVERELATIONSHIPS | bit | IN | Preserve revenue relationships |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @SITEFILTERMODE | tinyint | IN | Sites |
| @SITESSELECTED | xml | IN | |
| @SECURITYFEATUREID | uniqueidentifier | IN | Input parameter indicating the ID of the feature to use for site security checking. |
| @SECURITYFEATURETYPE | tinyint | IN | Input parameter indicating the type of the feature to use for site security checking. |
| @CURRENCYCODE | tinyint | IN | Currency |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_CONSTITUENT_REVENUEHISTORY
(
@CONSTITUENTID uniqueidentifier,
@REVENUETYPEOPTIONCODE smallint = 0,
@DATERANGECODE smallint = 2,
@ISGROUP bit = 0,
@PRESERVERELATIONSHIPS bit = 1,
@CURRENTAPPUSERID uniqueidentifier = null,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@CURRENCYCODE tinyint = 2
)
as
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
declare @STARTDATE datetime;
declare @ENDDATE datetime;
if @DATERANGECODE = 1 --Last 30 Days
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-29,@CURRENTDATE));
else if @DATERANGECODE = 2 --Last 90 Days
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,-89,@CURRENTDATE));
else if @DATERANGECODE = 3 --Last Year
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(dateadd(day,1,dateadd(year,-1,@CURRENTDATE)));
else
set @STARTDATE = @CURRENTDATE;
set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE);
declare @MULTICURRENCYENABLED bit;
set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency');
if @MULTICURRENCYENABLED = 0
set @CURRENCYCODE = 1;
declare @CURRENCYID uniqueidentifier;
if @CURRENCYCODE = 1
set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
if @CURRENCYCODE = 3
begin
set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
if @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
set @CURRENCYCODE = 1
end;
with MYSITESREV_CTE as
(
select
ID,
CONSTITUENTID,
AMOUNT,
TRANSACTIONTYPE,
TRANSACTIONTYPECODE,
DATE,
DATEADDED,
TRANSACTIONCURRENCYID,
BASECURRENCYID,
TRANSACTIONAMOUNT,
ORGANIZATIONAMOUNT
from
dbo.REVENUE
where
REVENUE.CONSTITUENTID = @CONSTITUENTID
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = REVENUE.ID
-- Using a case statement since the standard site extension filters
-- resulted in a poor plan
and case
when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 then 1
when exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)) then 1
else 0
end = 1
and
(
@SITEFILTERMODE = 0
or
exists(select UFN_SITE_BUILDDATALISTSITEFILTER.SITEID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER(@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) where UFN_SITE_BUILDDATALISTSITEFILTER.SITEID = REVSITES.SITEID)
)
)
)
select
ID,
RECORDID,
DATEADDED,
TYPECODE,
PARENT,
TYPE,
DATE,
AMOUNT,
GROSSAMOUNT,
BALANCE,
DESIGNATIONS,
PAYMENTMETHOD,
CATEGORY,
row_number() over (order by [TYPE]) as SEQUENCE,
MATCHINGGIFTCONSTITUENT,
CAMPAIGNS, --Bug 15945 - AdamBu - Each revenue type needs to retrieve campaigns differently, so do that below before they are unioned.
SITES,
DISPLAYCURRENCY
from
(
-- Donations, event registration payments, membership, and other payments
select
cast(RS.ID as nvarchar(36)) as ID,
MYSITESREV_CTE.ID as RECORDID,
ISVISIBLE =
Case
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE = 0 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 1 then 1 else 0 end -- Donations
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE = 1 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 5 then 1 else 0 end -- Event registration fees
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE = 4 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 6 then 1 else 0 end -- Other payment
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE = 5 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 7 then 1 else 0 end -- Membership fees
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE = 12 then case @REVENUETYPEOPTIONCODE when 0 then 1 else 0 end -- Auction purchases
end,
MYSITESREV_CTE.TRANSACTIONTYPECODE as TYPECODE,
case
when RS.TYPE = 'Sponsorship' then
case
when RS.APPLICATIONCODE = 0 then 'Sponsorship ' + lower(RS.APPLICATION)
else 'Sponsorship ' + RS.APPLICATION + ' payment'
end
else
case
when RS.APPLICATIONCODE = 0 then RS.APPLICATION
else RS.APPLICATION + ' payment'
end
end as TYPE,
dbo.UDA_BUILDLIST(distinct GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME) as CATEGORY,
MYSITESREV_CTE.DATE,
MYSITESREV_CTE.DATEADDED,
case @CURRENCYCODE
when 2 then RS.TRANSACTIONAMOUNT
when 1 then dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RS.ID,@CURRENCYID)
when 3 then dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RS.ID,@CURRENCYID)
else RS.AMOUNT
end as AMOUNT,
dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(RS.ID, 1, @CURRENCYID) as GROSSAMOUNT,
null as BALANCE,
dbo.UFN_DESIGNATION_GETNAME(RS.DESIGNATIONID) as DESIGNATIONS,
RPM.PAYMENTMETHOD as PAYMENTMETHOD,
PARENT = null,
MATCHINGGIFTCONSTITUENT = null,
(
select
dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME)
from
dbo.CAMPAIGN
inner join
dbo.REVENUESPLITCAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
where
REVENUESPLITCAMPAIGN.REVENUESPLITID = RS.ID
) as CAMPAIGNS,
dbo.UFN_REVENUESPLIT_BUILDSITELIST(RS.ID) SITES,
case @CURRENCYCODE
when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
when 0 then MYSITESREV_CTE.BASECURRENCYID
else @CURRENCYID
end as DISPLAYCURRENCY
from
MYSITESREV_CTE
left join dbo.REVENUEPAYMENTMETHOD RPM on MYSITESREV_CTE.ID = RPM.REVENUEID
inner join dbo.REVENUESPLIT RS on MYSITESREV_CTE.ID = RS.REVENUEID
left join dbo.REVENUECATEGORY RC on RS.ID = RC.ID
left join dbo.GLREVENUECATEGORYMAPPING on RC.GLREVENUECATEGORYMAPPINGID = GLREVENUECATEGORYMAPPING.ID
where
(MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE in (0,1,4,5,12)) and
(@DATERANGECODE = 0 or (MYSITESREV_CTE.DATE >= @STARTDATE and MYSITESREV_CTE.DATE <= @ENDDATE)) and
MYSITESREV_CTE.CONSTITUENTID = @CONSTITUENTID
group by
MYSITESREV_CTE.ID, RS.ID, MYSITESREV_CTE.TRANSACTIONTYPECODE, MYSITESREV_CTE.DATE, MYSITESREV_CTE.DATEADDED, RS.AMOUNT, RPM.PAYMENTMETHOD, MYSITESREV_CTE.TRANSACTIONTYPE, RS.APPLICATIONCODE, RS.APPLICATION, RS.DESIGNATIONID,RS.TYPE, RS.TRANSACTIONAMOUNT, MYSITESREV_CTE.TRANSACTIONCURRENCYID, MYSITESREV_CTE.BASECURRENCYID, RS.AMOUNT
union all
-- Pledges, Recurring gifts, Auction donations, Matching gift claims (by this constituent matching someone else's gift --> Orgs Only),
-- Planned gifts, Grant awards
select
cast(MYSITESREV_CTE.ID as nvarchar(36)) as ID,
MYSITESREV_CTE.ID as RECORDID,
ISVISIBLE =
Case
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 2 then 1 else 0 end -- Pledges
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 2 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 3 then 1 else 0 end -- Recurring gifts
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 3 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 4 then 1 else 0 end -- Matching gift claims
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 4 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 8 then 1 else 0 end -- Planned gifts
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 6 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 9 then 1 else 0 end -- Grant awards
when MYSITESREV_CTE.TRANSACTIONTYPECODE = 7 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 10 then 1 else 0 end -- Auction donations
end,
MYSITESREV_CTE.TRANSACTIONTYPECODE as TYPECODE,
case
when RS.TYPE = 'Sponsorship' then 'Sponsorship ' + LOWER(MYSITESREV_CTE.TRANSACTIONTYPE)
else MYSITESREV_CTE.TRANSACTIONTYPE
end as TYPE,
dbo.UDA_BUILDLIST(distinct GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME) as CATEGORY,
MYSITESREV_CTE.DATE,
MYSITESREV_CTE.DATEADDED,
case @CURRENCYCODE
when 2 then MYSITESREV_CTE.TRANSACTIONAMOUNT
when 1 then dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(MYSITESREV_CTE.ID,@CURRENCYID)
when 3 then dbo.UFN_REVENUE_GETAMOUNTINCURRENCY(MYSITESREV_CTE.ID,@CURRENCYID)
else MYSITESREV_CTE.AMOUNT
end as AMOUNT,
case MYSITESREV_CTE.TRANSACTIONTYPECODE
when 1 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGEGROSSAMOUNTINCURRENCY(MYSITESREV_CTE.ID, @CURRENCYID)
when 2 then dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEGROSSAMOUNTINCURRENCY(MYSITESREV_CTE.ID, 1, @CURRENCYID)
else null
end as GROSSAMOUNT,
case MYSITESREV_CTE.TRANSACTIONTYPECODE
when 1 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID, @CURRENCYID)
when 3 then dbo.UFN_PLEDGE_GETBALANCEINCURRENCY(MYSITESREV_CTE.ID, @CURRENCYID)
else null
end as BALANCE,
dbo.UFN_REVENUE_DESIGNATIONLIST(MYSITESREV_CTE.ID) as DESIGNATIONS,
case MYSITESREV_CTE.TRANSACTIONTYPECODE
when 1 then null
when 3 then null
else RPM.PAYMENTMETHOD
end as PAYMENTMETHOD,
PARENT = null,
MATCHINGGIFTCONSTITUENT = null,
dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME) as CAMPAIGNS,
dbo.UDA_BUILDLIST(distinct [SITE].NAME) as SITES,
case @CURRENCYCODE
when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
when 0 then MYSITESREV_CTE.BASECURRENCYID
else @CURRENCYID
end as DISPLAYCURRENCY
from
MYSITESREV_CTE
inner join dbo.REVENUESPLIT RS on MYSITESREV_CTE.ID = RS.REVENUEID
inner join dbo.REVENUEPAYMENTMETHOD RPM on MYSITESREV_CTE.ID = RPM.REVENUEID
left outer join dbo.REVENUECATEGORY RC on RS.ID = RC.ID
left outer join dbo.GLREVENUECATEGORYMAPPING on RC.GLREVENUECATEGORYMAPPINGID = GLREVENUECATEGORYMAPPING.ID
left outer join dbo.REVENUESPLITCAMPAIGN on REVENUESPLITCAMPAIGN.REVENUESPLITID = RS.ID
left outer join dbo.CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
outer apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RS.ID) REVENUESPLITSITE
left outer join dbo.[SITE] on [SITE].ID = REVENUESPLITSITE.SITEID
where
(MYSITESREV_CTE.TRANSACTIONTYPECODE <> 0) and
(@DATERANGECODE = 0 or (MYSITESREV_CTE.DATE >= @STARTDATE and MYSITESREV_CTE.DATE <= @ENDDATE)) and
MYSITESREV_CTE.CONSTITUENTID = @CONSTITUENTID
group by
MYSITESREV_CTE.ID, MYSITESREV_CTE.TRANSACTIONTYPECODE, MYSITESREV_CTE.DATE, MYSITESREV_CTE.DATEADDED, MYSITESREV_CTE.AMOUNT, RPM.PAYMENTMETHOD, MYSITESREV_CTE.TRANSACTIONTYPE,RS.TYPE, MYSITESREV_CTE.TRANSACTIONAMOUNT, MYSITESREV_CTE.TRANSACTIONCURRENCYID,MYSITESREV_CTE.BASECURRENCYID
union all
-- Pledge payment, Recurring gift payment, Matching gift payment, Planned gift payment, Grant award payment
-- Bug 15888 - AdamBu - Under a pledge, only break up the payments by split, not installment.
-- This allows us to match matching gift claim splits to payment splits based on designation.
select
ID =
Case RS.APPLICATIONCODE
when 3 then cast(RS.ID as nvarchar(36)) + cast(RGA.SOURCEREVENUEID as nvarchar(36))
else cast(RS.ID as nvarchar(36))
end,
MYSITESREV_CTE.ID as RECORDID,
ISVISIBLE =
Case RS.APPLICATIONCODE
when 2 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 2 then 1 else 0 end
when 3 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 3 then 1 else 0 end
when 6 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 8 then 1 else 0 end
when 7 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 4 then 1 else 0 end
when 8 then case @REVENUETYPEOPTIONCODE when 0 then 1 when 9 then 1 else 0 end
end,
MYSITESREV_CTE.TRANSACTIONTYPECODE as TYPECODE,
case
when RS.TYPE = 'Sponsorship' then 'Sponsorship payment'
else RS.APPLICATION + ' payment'
end as TYPE,
dbo.UDA_BUILDLIST(distinct GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME) as CATEGORY,
MYSITESREV_CTE.DATE,
MYSITESREV_CTE.DATEADDED,
-- For unapplied matching gift payments, use the revenue split amount
case when RS.APPLICATIONCODE = 7 and ISP.PLEDGEID is null then
case @CURRENCYCODE
when 2 then RS.TRANSACTIONAMOUNT
when 1 then dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RS.ID,@CURRENCYID)
when 3 then dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RS.ID,@CURRENCYID)
else RS.AMOUNT
end
else
case @CURRENCYCODE
when 2 then RS.TRANSACTIONAMOUNT
when 1 then coalesce(sum(dbo.UFN_RECURRINGGIFTACTIVITY_GETAMOUNTINCURRENCY(RGA.ID, @CURRENCYID)), 0) + coalesce(sum(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(ISP.ID,@CURRENCYID)), 0)
when 3 then coalesce(sum(dbo.UFN_RECURRINGGIFTACTIVITY_GETAMOUNTINCURRENCY(RGA.ID, @CURRENCYID)), 0) + coalesce(sum(dbo.UFN_INSTALLMENTSPLITPAYMENT_GETAMOUNTINCURRENCY(ISP.ID,@CURRENCYID)), 0)
else coalesce(sum(RGA.AMOUNT), 0) + coalesce(sum(ISP.AMOUNT), 0)
end
end as AMOUNT,
dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATESPLITGROSSAMOUNTINCURRENCY(RS.ID, 1, @CURRENCYID) as GROSSAMOUNT,
null as BALANCE,
dbo.UFN_DESIGNATION_GETNAME(RS.DESIGNATIONID) as DESIGNATIONS,
RPM.PAYMENTMETHOD,
PARENT =
case @PRESERVERELATIONSHIPS
when 1 then
case RS.APPLICATIONCODE
when 3 then cast(RGA.SOURCEREVENUEID as nvarchar(36))
else cast(ISP.PLEDGEID as nvarchar(36))
end
else null
end,
MATCHINGGIFTCONSTITUENT = null,
(
select
dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME)
from
dbo.CAMPAIGN
inner join
dbo.REVENUESPLITCAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
where
REVENUESPLITCAMPAIGN.REVENUESPLITID = RS.ID
) as CAMPAIGNS,
dbo.UFN_REVENUESPLIT_BUILDSITELIST(RS.ID) SITES,
case @CURRENCYCODE
when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
when 0 then MYSITESREV_CTE.BASECURRENCYID
else @CURRENCYID
end as DISPLAYCURRENCY
from
MYSITESREV_CTE
inner join dbo.REVENUESPLIT RS on MYSITESREV_CTE.ID = RS.REVENUEID
left outer join dbo.REVENUEPAYMENTMETHOD RPM on MYSITESREV_CTE.ID = RPM.REVENUEID
left outer join dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.PAYMENTID = RS.ID
left outer join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = ISP.INSTALLMENTSPLITID
left outer join dbo.RECURRINGGIFTACTIVITY RGA on RGA.PAYMENTREVENUEID = RS.ID
left join dbo.REVENUECATEGORY RC on RS.ID = RC.ID
left join dbo.GLREVENUECATEGORYMAPPING on RC.GLREVENUECATEGORYMAPPINGID = GLREVENUECATEGORYMAPPING.ID
where
MYSITESREV_CTE.TRANSACTIONTYPECODE = 0 and
RS.APPLICATIONCODE in (2,3,6,7,8) and -- Pledge payment, Recurring gift payment, Matching gift payment, Grant award payment
(@DATERANGECODE = 0 or (MYSITESREV_CTE.DATE >= @STARTDATE and MYSITESREV_CTE.DATE <= @ENDDATE))
group by
MYSITESREV_CTE.ID, RS.ID, MYSITESREV_CTE.TRANSACTIONTYPECODE, MYSITESREV_CTE.DATE, MYSITESREV_CTE.DATEADDED, MYSITESREV_CTE.AMOUNT, RS.APPLICATIONCODE, RS.APPLICATION, RS.DESIGNATIONID,
RGA.SOURCEREVENUEID, ISP.PLEDGEID, RPM.PAYMENTMETHOD, MYSITESREV_CTE.TRANSACTIONTYPE, INSTALLMENTSPLIT.DESIGNATIONID,RS.TYPE,RS.TRANSACTIONAMOUNT, MYSITESREV_CTE.TRANSACTIONCURRENCYID,
MYSITESREV_CTE.BASECURRENCYID, RS.AMOUNT
union all
-- write-offs
select
null as ID,
null as RECORDID,
case
when @REVENUETYPEOPTIONCODE = 0 then 1
when @REVENUETYPEOPTIONCODE = 2 and MYSITESREV_CTE.TRANSACTIONTYPECODE = 1 then 1
when @REVENUETYPEOPTIONCODE = 9 and MYSITESREV_CTE.TRANSACTIONTYPECODE = 6 then 1
else 0
end as ISVISIBLE,
null as TYPECODE,
case MYSITESREV_CTE.TRANSACTIONTYPECODE
when 1 then 'Pledge write-off'
when 6 then 'Grant award write-off'
end as TYPE,
null as CATEGORY,
WO.DATE as DATE,
WO.DATEADDED as DATEADDED,
case @CURRENCYCODE
when 2 then IWO.TRANSACTIONAMOUNT
when 1 then dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTINCURRENCY(IWO.ID,@CURRENCYID)
when 3 then dbo.UFN_INSTALLMENTSPLITWRITEOFF_GETAMOUNTINCURRENCY(IWO.ID,@CURRENCYID)
else IWO.AMOUNT
end as AMOUNT,
null as GROSSAMOUNT,
null as BALANCE,
null as DESIGNATIONS,
null as PAYMENTMETHOD,
PARENT =
case @PRESERVERELATIONSHIPS
when 1 then cast(WO.REVENUEID as nvarchar(36))
else null
end,
MATCHINGGIFTCONSTITUENT = null,
(
select
dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME)
from
dbo.CAMPAIGN
inner join
dbo.REVENUESPLITCAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
inner join
dbo.REVENUESPLIT on REVENUESPLIT.ID = REVENUESPLITCAMPAIGN.REVENUESPLITID
where
REVENUESPLIT.REVENUEID = MYSITESREV_CTE.ID
) as CAMPAIGNS,
SITE.NAME,
case @CURRENCYCODE
when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
when 0 then MYSITESREV_CTE.BASECURRENCYID
else @CURRENCYID
end as DISPLAYCURRENCY
from
dbo.WRITEOFF WO
inner join MYSITESREV_CTE on MYSITESREV_CTE.ID = WO.REVENUEID
inner join dbo.INSTALLMENTSPLITWRITEOFF IWO on IWO.WRITEOFFID = WO.ID
inner join dbo.WRITEOFFSPLIT WOS on WOS.WRITEOFFID = WO.ID
inner join dbo.DESIGNATION on DESIGNATION.ID = WOS.DESIGNATIONID
inner join dbo.DESIGNATIONLEVEL on coalesce(DESIGNATION.DESIGNATIONLEVEL5ID,DESIGNATION.DESIGNATIONLEVEL4ID,DESIGNATION.DESIGNATIONLEVEL3ID,DESIGNATION.DESIGNATIONLEVEL2ID,DESIGNATION.DESIGNATIONLEVEL1ID) = DESIGNATIONLEVEL.ID
left join dbo.SITE on SITE.ID = DESIGNATIONLEVEL.SITEID
where
MYSITESREV_CTE.CONSTITUENTID = @CONSTITUENTID
and exists(select ID from dbo.REVENUESPLIT where REVENUEID=MYSITESREV_CTE.ID)
union all
-- Matching gift claims, matching revenue from this constituent
select distinct
ID = case
when RS2.APPLICATIONCODE in (2,7) then cast(RS.ID as nvarchar(36)) + cast(RS.ID as nvarchar(36))
when RS2.APPLICATIONCODE = 3 then cast(RS.ID as nvarchar(36)) + cast(R2.ID as nvarchar(36)) + cast(RGA.SOURCEREVENUEID as nvarchar(36))
else cast(RS.ID as nvarchar(36)) + cast(RMG.MGSOURCEREVENUEID as nvarchar(36))
end,
MYSITESREV_CTE.ID as RECORDID,
ISVISIBLE =
case @REVENUETYPEOPTIONCODE
when 0 then 1
when 1 then case when R2.TRANSACTIONTYPECODE = 0 and RS2.APPLICATIONCODE = 0 then 1 else 0 end
when 2 then case when R2.TRANSACTIONTYPECODE = 0 and RS2.APPLICATIONCODE = 2 then 1 else 0 end
when 3 then case when R2.TRANSACTIONTYPECODE = 0 and RS2.APPLICATIONCODE = 3 then 1 else 0 end
when 4 then case when R2.TRANSACTIONTYPECODE = 0 and RS2.APPLICATIONCODE = 7 then 1 else 0 end
when 5 then case when R2.TRANSACTIONTYPECODE = 0 and RS2.APPLICATIONCODE = 1 then 1 else 0 end
when 7 then case when R2.TRANSACTIONTYPECODE = 0 and RS2.APPLICATIONCODE = 5 then 1 else 0 end
when 9 then case when R2.TRANSACTIONTYPECODE = 0 and RS2.APPLICATIONCODE = 8 then 1 else 0 end
end,
MYSITESREV_CTE.TRANSACTIONTYPECODE as TYPECODE,
MYSITESREV_CTE.TRANSACTIONTYPE + ' (' + C.NAME + ')' as TYPE,
dbo.UDA_BUILDLIST(distinct GLREVENUECATEGORYMAPPING.REVENUECATEGORYNAME) as CATEGORY,
MYSITESREV_CTE.DATE,
MYSITESREV_CTE.DATEADDED,
case @CURRENCYCODE
when 2 then RS.TRANSACTIONAMOUNT
when 1 then dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RS.ID,@CURRENCYID)
when 3 then dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RS.ID,@CURRENCYID)
else RS.AMOUNT
end as AMOUNT,
null as GROSSAMOUNT,
dbo.UFN_PLEDGE_GETDESIGNATIONBALANCEINCURRENCY(MYSITESREV_CTE.ID, RS.DESIGNATIONID, @CURRENCYID) [BALANCE],
dbo.UFN_DESIGNATION_GETNAME(RS.DESIGNATIONID) as DESIGNATIONS,
RPM.PAYMENTMETHOD,
PARENT =
case @PRESERVERELATIONSHIPS
when 1 then
case
when RS2.APPLICATIONCODE in (2,7,8) then cast(RS2.ID as nvarchar(36))
when RS2.APPLICATIONCODE = 3 then cast(R2.ID as nvarchar(36)) + cast(RGA.SOURCEREVENUEID as nvarchar(36))
else cast(RMG.MGSOURCEREVENUEID as nvarchar(36))
end
else null
end,
MATCHINGGIFTCONSTITUENT = C.NAME,
(
select
dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME)
from
dbo.CAMPAIGN
inner join
dbo.REVENUESPLITCAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
where
REVENUESPLITCAMPAIGN.REVENUESPLITID = RS.ID
) as CAMPAIGNS,
dbo.UFN_REVENUESPLIT_BUILDSITELIST(RS.ID) SITES,
case @CURRENCYCODE
when 2 then MYSITESREV_CTE.TRANSACTIONCURRENCYID
when 0 then MYSITESREV_CTE.BASECURRENCYID
else @CURRENCYID
end as DISPLAYCURRENCY
from
MYSITESREV_CTE
inner join dbo.REVENUEMATCHINGGIFT RMG on RMG.ID = MYSITESREV_CTE.ID
inner join dbo.REVENUE R2 on R2.ID = RMG.MGSOURCEREVENUEID
inner join dbo.REVENUESPLIT RS on RS.REVENUEID = MYSITESREV_CTE.ID
inner join dbo.REVENUESPLIT RS2 on RS2.REVENUEID = R2.ID
left outer join dbo.REVENUEPAYMENTMETHOD RPM on RPM.REVENUEID = MYSITESREV_CTE.ID
left outer join dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.PAYMENTID = RS2.ID
inner join dbo.CONSTITUENT C on C.ID = MYSITESREV_CTE.CONSTITUENTID
left outer join dbo.RECURRINGGIFTACTIVITY RGA on RGA.PAYMENTREVENUEID = RS2.ID
left join dbo.REVENUECATEGORY RC on RS.ID = RC.ID
left join dbo.GLREVENUECATEGORYMAPPING on RC.GLREVENUECATEGORYMAPPINGID = GLREVENUECATEGORYMAPPING.ID
where
MYSITESREV_CTE.TRANSACTIONTYPECODE = 3 and -- Matching gift claims
R2.CONSTITUENTID = @CONSTITUENTID and
(@DATERANGECODE = 0 or (MYSITESREV_CTE.DATE >= @STARTDATE and MYSITESREV_CTE.DATE <= @ENDDATE))
and RS.DESIGNATIONID = RS2.DESIGNATIONID
group by
MYSITESREV_CTE.ID, RS.ID, RS.AMOUNT, MYSITESREV_CTE.TRANSACTIONTYPECODE, MYSITESREV_CTE.TRANSACTIONTYPE, MYSITESREV_CTE.DATE, MYSITESREV_CTE.DATEADDED, RPM.PAYMENTMETHOD, RGA.SOURCEREVENUEID,
R2.TRANSACTIONTYPECODE, C.NAME, RS2.APPLICATIONCODE, RMG.MGSOURCEREVENUEID, R2.ID, RS.DESIGNATIONID, RS2.ID, RS.TRANSACTIONAMOUNT, MYSITESREV_CTE.TRANSACTIONCURRENCYID, MYSITESREV_CTE.BASECURRENCYID,
RS.AMOUNT
) as REVENUEHISTORY
where ISVISIBLE = 1
order by TYPE asc, DATE desc, DATEADDED desc;