USP_CONSTITUENTGROUP_CUMULATIVEGIVINGSUMMARYGET
Returns the cumulative giving history for a constituent group.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @SITEFILTERMODE | tinyint | IN | |
| @SITESSELECTED | xml | IN | |
| @SECURITYFEATUREID | uniqueidentifier | IN | |
| @SECURITYFEATURETYPE | tinyint | IN | |
| @CURRENCYCODE | tinyint | IN |
Definition
Copy
CREATE procedure [dbo].[USP_CONSTITUENTGROUP_CUMULATIVEGIVINGSUMMARYGET]
(
@CONSTITUENTID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@CURRENCYCODE tinyint = 1
)
as
set nocount on;
declare @GROUPINCLUDESMEMBERGIVING bit = null;
declare @ISHOUSEHOLD bit = null;
declare @TOTALNUMBER int = null;
declare @TOTALAMOUNT money = null;
declare @TOTALGROUPAMOUNT money = null;
declare @TOTALGROUPMEMBERAMOUNT money = null;
declare @AVERAGEAMOUNT money = null;
declare @MOSTFREQUENTAMOUNT money = null;
declare @TOTALPLEDGEBALANCE money = null;
declare @TOTALYEARS int = null;
declare @CONSECUTIVEYEARS int = null;
declare @GIVENSINCEFISCALYEAR datetime = null;
declare @LARGESTID uniqueidentifier = null;
declare @LARGESTRECORDID uniqueidentifier = null;
declare @LARGESTDATE datetime = null;
declare @LARGESTTYPECODE tinyint = null;
declare @LARGESTTYPE nvarchar(22) = null;
declare @LARGESTAMOUNT money = null;
declare @LARGESTPLEDGEBALANCE money = null;
declare @LARGESTSPLITS xml = null;
declare @LARGESTCONSTITUENTNAME nvarchar(300) = null;
declare @FIRSTID uniqueidentifier = null;
declare @FIRSTRECORDID uniqueidentifier = null;
declare @FIRSTDATE datetime = null;
declare @FIRSTTYPECODE tinyint = null;
declare @FIRSTTYPE nvarchar(22) = null;
declare @FIRSTAMOUNT money = null;
declare @FIRSTPLEDGEBALANCE money = null;
declare @FIRSTSPLITS xml = null;
declare @FIRSTCONSTITUENTNAME nvarchar(300) = null;
declare @LATESTID uniqueidentifier = null;
declare @LATESTRECORDID uniqueidentifier = null;
declare @LATESTDATE datetime = null;
declare @LATESTTYPECODE tinyint = null;
declare @LATESTTYPE nvarchar(22) = null;
declare @LATESTAMOUNT money = null;
declare @LATESTPLEDGEBALANCE money = null;
declare @LATESTSPLITS xml = null;
declare @LATESTCONSTITUENTNAME nvarchar(300) = null;
declare @RECOGNITIONTOTALNUMBER int = null;
declare @RECOGNITIONTOTALAMOUNT money = null;
declare @RECOGNITIONTOTALGROUPAMOUNT money = null;
declare @RECOGNITIONTOTALGROUPMEMBERAMOUNT money = null;
declare @RECOGNITIONAVERAGEAMOUNT money = null;
declare @RECOGNITIONMOSTFREQUENTAMOUNT money = null;
declare @RECOGNITIONTOTALYEARS int = null;
declare @RECOGNITIONCONSECUTIVEYEARS int = null;
declare @RECOGNITIONGIVENSINCEFISCALYEAR datetime = null;
declare @RECOGNITIONLARGESTRECORDID uniqueidentifier = null;
declare @RECOGNITIONLARGESTDATE datetime = null;
declare @RECOGNITIONLARGESTTYPECODE tinyint = null;
declare @RECOGNITIONLARGESTTYPE nvarchar(22) = null;
declare @RECOGNITIONLARGESTAMOUNT money = null;
declare @RECOGNITIONLARGESTCONSTITUENTNAME nvarchar(300) = null;
declare @RECOGNITIONFIRSTRECORDID uniqueidentifier = null;
declare @RECOGNITIONFIRSTDATE datetime = null;
declare @RECOGNITIONFIRSTTYPECODE tinyint = null;
declare @RECOGNITIONFIRSTTYPE nvarchar(22) = null;
declare @RECOGNITIONFIRSTAMOUNT money = null;
declare @RECOGNITIONFIRSTCONSTITUENTNAME nvarchar(300) = null;
declare @RECOGNITIONLATESTRECORDID uniqueidentifier = null;
declare @RECOGNITIONLATESTDATE datetime = null;
declare @RECOGNITIONLATESTTYPECODE tinyint = null;
declare @RECOGNITIONLATESTTYPE nvarchar(22) = null;
declare @RECOGNITIONLATESTAMOUNT money = null;
declare @RECOGNITIONLATESTCONSTITUENTNAME nvarchar(300) = null;
declare @GROUPMEMBERTAXCLAIMAMOUNT money = null;
declare @RECOGNITIONGROUPMEMBERTAXCLAIMAMOUNT money = null;
declare @MATCHEDGIFTSAMOUNT money = null;
declare @CURRENCYID uniqueidentifier
if @CURRENCYCODE = 1
set @CURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
else
set @CURRENCYID = dbo.UFN_APPUSER_GETBASECURRENCY(@CURRENTAPPUSERID);
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
declare @DECIMALDIGITS tinyint;
declare @ROUNDINGTYPECODE tinyint;
declare @ORIGINCODE tinyint;
select @ORIGINCODE = coalesce(ORGANIZATIONAMOUNTORIGINCODE, 0) from dbo.MULTICURRENCYCONFIGURATION;
select @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),
@DECIMALDIGITS = DECIMALDIGITS,
@ROUNDINGTYPECODE = ROUNDINGTYPECODE
from
dbo.CURRENCY
where
CURRENCY.ID = @CURRENCYID;
exec dbo.USP_GROUP_GIVINGSUMMARYGET
@CONSTITUENTID,
@GROUPINCLUDESMEMBERGIVING = @GROUPINCLUDESMEMBERGIVING output,
@ISHOUSEHOLD = @ISHOUSEHOLD output,
@TOTALNUMBER = @TOTALNUMBER output,
@TOTALAMOUNT = @TOTALAMOUNT output,
@TOTALGROUPAMOUNT = @TOTALGROUPAMOUNT output,
@TOTALGROUPMEMBERAMOUNT = @TOTALGROUPMEMBERAMOUNT output,
@AVERAGEAMOUNT = @AVERAGEAMOUNT output,
@MOSTFREQUENTAMOUNT = @MOSTFREQUENTAMOUNT output,
@TOTALPLEDGEBALANCE = @TOTALPLEDGEBALANCE output,
@TOTALYEARS = @TOTALYEARS output,
@CONSECUTIVEYEARS = @CONSECUTIVEYEARS output,
@GIVENSINCEFISCALYEAR = @GIVENSINCEFISCALYEAR output,
@CURRENTAPPUSERID = @CURRENTAPPUSERID,
@SITEFILTERMODE = @SITEFILTERMODE,
@SITESSELECTED = @SITESSELECTED,
@SECURITYFEATUREID = @SECURITYFEATUREID,
@SECURITYFEATURETYPE = @SECURITYFEATURETYPE,
@GROUPMEMBERTAXCLAIMAMOUNT = @GROUPMEMBERTAXCLAIMAMOUNT output,
@MATCHEDGIFTSAMOUNT = @MATCHEDGIFTSAMOUNT output,
@CURRENCYID = @CURRENCYID;
declare @HOUSEHOLDSCANBEDONORS as bit = dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS()
declare @GIVING table
(
REVENUEID uniqueidentifier,
TRANSACTIONTYPECODE tinyint,
TRANSACTIONTYPE nvarchar(19),
REVENUEAMOUNT money,
[DATE] datetime,
DATEADDED datetime,
SPLITID uniqueidentifier,
APPLICATIONCODE tinyint,
DESIGNATIONID uniqueidentifier,
SPLITAMOUNT money,
WRITEOFFAMOUNT money,
CONSTITUENTID uniqueidentifier,
SPLITNETAMOUNT money
)
if @GROUPINCLUDESMEMBERGIVING = 1
begin
declare @MEMBERIDS table
(
ID uniqueidentifier,
DATEFROM datetime,
DATETO datetime
)
insert into @MEMBERIDS
select
GROUPMEMBER.MEMBERID ID, DATEFROM, DATETO
from
dbo.GROUPMEMBER
left join
dbo.GROUPMEMBERDATERANGE on GROUPMEMBERDATERANGE.GROUPMEMBERID = GROUPMEMBER.ID
where
GROUPMEMBER.GROUPID = @CONSTITUENTID;
insert into @GIVING
select
REVENUEID,
TRANSACTIONTYPECODE,
TRANSACTIONTYPE,
REVENUEAMOUNT,
[DATE],
DATEADDED,
SPLITID,
APPLICATIONCODE,
DESIGNATIONID,
SPLITAMOUNT,
WRITEOFFAMOUNT,
MEMBERIDS.ID CONSTITUENTID,
REV.SPLITNETAMOUNT
from
@MEMBERIDS MEMBERIDS
cross apply dbo.UFN_CONSTITUENT_GIVINGHISTORYINCURRENCY_BULK(
MEMBERIDS.ID,
@CURRENTAPPUSERID,
@SITEFILTERMODE,
@SITESSELECTED,
@SECURITYFEATUREID,
@SECURITYFEATURETYPE,
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@DECIMALDIGITS,
@ROUNDINGTYPECODE) REV
where
((MEMBERIDS.DATEFROM is null or REV.DATE >= MEMBERIDS.DATEFROM) and
(MEMBERIDS.DATETO is null or REV.DATE <= MEMBERIDS.DATETO));
end
if not (@ISHOUSEHOLD = 1 and @HOUSEHOLDSCANBEDONORS = 0)
begin
insert into @GIVING
select
REVENUEID,
TRANSACTIONTYPECODE,
TRANSACTIONTYPE,
REVENUEAMOUNT,
[DATE],
DATEADDED,
SPLITID,
APPLICATIONCODE,
DESIGNATIONID,
SPLITAMOUNT,
WRITEOFFAMOUNT,
@CONSTITUENTID,
SPLITNETAMOUNT
from dbo.UFN_CONSTITUENT_GIVINGHISTORYINCURRENCY_BULK(
@CONSTITUENTID,
@CURRENTAPPUSERID,
@SITEFILTERMODE,
@SITESSELECTED,
@SECURITYFEATUREID,
@SECURITYFEATURETYPE,
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@DECIMALDIGITS,
@ROUNDINGTYPECODE);
end
-- largest gift
select top 1
@LARGESTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=G.CONSTITUENTID),
@LARGESTID = G.REVENUEID,
@LARGESTRECORDID = G.REVENUEID,
@LARGESTDATE = G.DATE,
@LARGESTTYPECODE = G.TRANSACTIONTYPECODE,
@LARGESTTYPE = G.TRANSACTIONTYPE,
@LARGESTAMOUNT = sum(G.SPLITNETAMOUNT),
@LARGESTPLEDGEBALANCE = case G.TRANSACTIONTYPECODE when 1 then PLEDGEBALANCE.BALANCEINCURRENCY else null end,
--using this instead of TOXML function, because a different root element is needed
@LARGESTSPLITS = (select
[AMOUNT],
[ID],
[PURPOSE]
from
dbo.[UFN_REVENUE_GETSPLITS_LISTINCURRENCY_BULK](G.REVENUEID, @CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ORIGINCODE)
order by
ID
for xml raw('ITEM'),type,elements,root('LARGESTSPLITS'),BINARY BASE64)
from @GIVING G
left join dbo.UFN_PLEDGE_GETBALANCEASOFINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, getdate(), @ORIGINCODE, 3) PLEDGEBALANCE on PLEDGEBALANCE.ID = G.REVENUEID
group by
G.REVENUEID, G.TRANSACTIONTYPE, G.TRANSACTIONTYPECODE, G.DATE, G.DATEADDED, G.CONSTITUENTID, PLEDGEBALANCE.BALANCEINCURRENCY
order by
sum(SPLITNETAMOUNT) desc, G.DATE desc, G.DATEADDED desc;
-- first gift
select top 1
@FIRSTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=G.CONSTITUENTID),
@FIRSTID = G.REVENUEID,
@FIRSTRECORDID = G.REVENUEID,
@FIRSTDATE = G.DATE,
@FIRSTTYPECODE = G.TRANSACTIONTYPECODE,
@FIRSTTYPE = G.TRANSACTIONTYPE,
@FIRSTAMOUNT = sum(G.SPLITNETAMOUNT),
@FIRSTPLEDGEBALANCE = case G.TRANSACTIONTYPECODE when 1 then PLEDGEBALANCE.BALANCEINCURRENCY else null end,
--using this instead of TOXML function, because a different root element is needed
@FIRSTSPLITS = (select
[AMOUNT],
[ID],
[PURPOSE]
from
dbo.[UFN_REVENUE_GETSPLITS_LISTINCURRENCY_BULK](G.REVENUEID, @CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ORIGINCODE)
order by
ID
for xml raw('ITEM'),type,elements,root('FIRSTSPLITS'),BINARY BASE64)
from @GIVING G
left join dbo.UFN_PLEDGE_GETBALANCEASOFINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, getdate(), @ORIGINCODE, 3) PLEDGEBALANCE on PLEDGEBALANCE.ID = G.REVENUEID
group by
G.REVENUEID, G.TRANSACTIONTYPE, G.TRANSACTIONTYPECODE, G.DATE, G.DATEADDED, G.CONSTITUENTID, PLEDGEBALANCE.BALANCEINCURRENCY
order by
G.DATE asc, G.DATEADDED asc;
-- latest gift
select top 1
@LATESTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=G.CONSTITUENTID),
@LATESTID = G.REVENUEID,
@LATESTRECORDID = G.REVENUEID,
@LATESTDATE = G.DATE,
@LATESTTYPECODE = G.TRANSACTIONTYPECODE,
@LATESTTYPE = G.TRANSACTIONTYPE,
@LATESTAMOUNT = sum(G.SPLITNETAMOUNT),
@LATESTPLEDGEBALANCE = case G.TRANSACTIONTYPECODE when 1 then PLEDGEBALANCE.BALANCEINCURRENCY else null end,
--using this instead of TOXML function, because a different root element is needed
@LATESTSPLITS = (select
[AMOUNT],
[ID],
[PURPOSE]
from
dbo.[UFN_REVENUE_GETSPLITS_LISTINCURRENCY_BULK](G.REVENUEID, @CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @ORIGINCODE)
order by
ID
for xml raw('ITEM'),type,elements,root('LATESTSPLITS'),BINARY BASE64)
from @GIVING G
left join dbo.UFN_PLEDGE_GETBALANCEASOFINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, getdate(), @ORIGINCODE, 3) PLEDGEBALANCE on PLEDGEBALANCE.ID = G.REVENUEID
group by
G.REVENUEID, G.TRANSACTIONTYPE, G.TRANSACTIONTYPECODE, G.DATE, G.DATEADDED, G.CONSTITUENTID, PLEDGEBALANCE.BALANCEINCURRENCY
order by
G.DATE desc, G.DATEADDED desc;
-- Handle recognitions
exec dbo.USP_GROUP_RECOGNITIONSUMMARYGET
@CONSTITUENTID,
@RECOGNITIONTOTALNUMBER = @RECOGNITIONTOTALNUMBER output,
@RECOGNITIONTOTALAMOUNT = @RECOGNITIONTOTALAMOUNT output,
@RECOGNITIONTOTALGROUPAMOUNT = @RECOGNITIONTOTALGROUPAMOUNT output,
@RECOGNITIONTOTALGROUPMEMBERAMOUNT = @RECOGNITIONTOTALGROUPMEMBERAMOUNT output,
@RECOGNITIONAVERAGEAMOUNT = @RECOGNITIONAVERAGEAMOUNT output,
@RECOGNITIONMOSTFREQUENTAMOUNT = @RECOGNITIONMOSTFREQUENTAMOUNT output,
@RECOGNITIONTOTALYEARS = @RECOGNITIONTOTALYEARS output,
@RECOGNITIONCONSECUTIVEYEARS = @RECOGNITIONCONSECUTIVEYEARS output,
@RECOGNITIONGIVENSINCEFISCALYEAR = @RECOGNITIONGIVENSINCEFISCALYEAR output,
@CURRENTAPPUSERID = @CURRENTAPPUSERID,
@SITEFILTERMODE = @SITEFILTERMODE,
@SITESSELECTED = @SITESSELECTED,
@SECURITYFEATUREID = @SECURITYFEATUREID,
@SECURITYFEATURETYPE = @SECURITYFEATURETYPE,
@RECOGNITIONGROUPMEMBERTAXCLAIMAMOUNT = @RECOGNITIONGROUPMEMBERTAXCLAIMAMOUNT output,
@CURRENCYID = @CURRENCYID;
--group member largest gift
select top 1
@RECOGNITIONLARGESTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=RR.CONSTITUENTID),
@RECOGNITIONLARGESTRECORDID = R.ID,
@RECOGNITIONLARGESTDATE = RR.EFFECTIVEDATE,
@RECOGNITIONLARGESTTYPECODE = R.TYPECODE,
@RECOGNITIONLARGESTTYPE = R.TYPE,
@RECOGNITIONLARGESTAMOUNT = RR.AMOUNTINCURRENCY
from
dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RR
inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RR.REVENUESPLITID = RS.ID
inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = RS.ID
inner join dbo.FINANCIALTRANSACTION R on RS.FINANCIALTRANSACTIONID = R.ID
inner join dbo.GROUPMEMBER GM on RR.CONSTITUENTID = GM.MEMBERID
left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
where @GROUPINCLUDESMEMBERGIVING = 1
and GM.GROUPID = @CONSTITUENTID
and R.DELETEDON is null
and RS.DELETEDON is null
and RS.TYPECODE <> 1
and
(R.TYPECODE = 1 or
(R.TYPECODE = 3) or -- Matching gift claim
(R.TYPECODE = 5 and RSE.APPLICATIONCODE = 0) or -- Donations on orders
(R.TYPECODE = 0 and RSE.APPLICATIONCODE in (0, 3, 18, 19)) or --Pledge or Payment (Gift or Recurring gift payment)
(R.TYPECODE = 7)) --Auction donation
and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= RR.EFFECTIVEDATE))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= RR.EFFECTIVEDATE))
or (GMDR.DATEFROM <= RR.EFFECTIVEDATE and GMDR.DATETO >= RR.EFFECTIVEDATE))
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = R.ID
/*next line is #SITEEXTENTION code*/
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
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)
)
)
order by
RR.AMOUNTINCURRENCY desc, RR.EFFECTIVEDATE desc, RR.DATEADDED desc;
-- group largest recognition
select top 1
@RECOGNITIONLARGESTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=RR.CONSTITUENTID),
@RECOGNITIONLARGESTRECORDID = R.ID,
@RECOGNITIONLARGESTDATE = RR.EFFECTIVEDATE,
@RECOGNITIONLARGESTTYPECODE = R.TYPECODE,
@RECOGNITIONLARGESTTYPE = R.TYPE,
@RECOGNITIONLARGESTAMOUNT = RR.AMOUNTINCURRENCY
from
dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RR
inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RR.REVENUESPLITID = RS.ID
inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = RS.ID
inner join dbo.FINANCIALTRANSACTION R on RS.FINANCIALTRANSACTIONID = R.ID
where RR.CONSTITUENTID = @CONSTITUENTID
and R.DELETEDON is null
and RS.DELETEDON is null
and RS.TYPECODE <> 1
and
(R.TYPECODE = 1 or
(R.TYPECODE = 3) or -- Matching gift claim
(R.TYPECODE = 5 and RSE.APPLICATIONCODE = 0) or -- Donations on orders
(R.TYPECODE = 0 and RSE.APPLICATIONCODE in (0, 3, 18, 19)) or --Pledge or Payment (Gift or Recurring gift payment)
(R.TYPECODE = 7)) --Auction donation
and (@RECOGNITIONLARGESTAMOUNT is null or RR.AMOUNTINCURRENCY > @RECOGNITIONLARGESTAMOUNT)
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = R.ID
/*next line is #SITEEXTENTION code*/
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
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)
)
)
order by
RR.AMOUNTINCURRENCY desc, RR.EFFECTIVEDATE desc, RR.DATEADDED desc;
-- first group member recognition
select top 1
@RECOGNITIONFIRSTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=RR.CONSTITUENTID),
@RECOGNITIONFIRSTRECORDID = R.ID,
@RECOGNITIONFIRSTDATE = RR.EFFECTIVEDATE,
@RECOGNITIONFIRSTTYPECODE = R.TYPECODE,
@RECOGNITIONFIRSTTYPE = R.TYPE,
@RECOGNITIONFIRSTAMOUNT = RR.AMOUNTINCURRENCY
from
dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RR
inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RR.REVENUESPLITID = RS.ID
inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = RS.ID
inner join dbo.FINANCIALTRANSACTION R on RS.FINANCIALTRANSACTIONID = R.ID
inner join dbo.GROUPMEMBER GM on RR.CONSTITUENTID = GM.MEMBERID
left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
where @GROUPINCLUDESMEMBERGIVING = 1
and GM.GROUPID = @CONSTITUENTID
and R.DELETEDON is null
and RS.DELETEDON is null
and RS.TYPECODE <> 1
and
(R.TYPECODE = 1 or
(R.TYPECODE = 3) or -- Matching gift claim
(R.TYPECODE = 5 and RSE.APPLICATIONCODE = 0) or -- Donations on orders
(R.TYPECODE = 0 and RSE.APPLICATIONCODE in (0, 3, 18, 19)) or --Pledge or Payment (Gift or Recurring gift payment)
(R.TYPECODE = 7)) --Auction donation
and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= RR.EFFECTIVEDATE))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= RR.EFFECTIVEDATE))
or (GMDR.DATEFROM <= RR.EFFECTIVEDATE and GMDR.DATETO >= RR.EFFECTIVEDATE))
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = R.ID
/*next line is #SITEEXTENTION code*/
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
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)
)
)
order by
RR.EFFECTIVEDATE asc, RR.DATEADDED asc;
-- first group recognition
select top 1
@RECOGNITIONFIRSTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=RR.CONSTITUENTID),
@RECOGNITIONFIRSTRECORDID = R.ID,
@RECOGNITIONFIRSTDATE = RR.EFFECTIVEDATE,
@RECOGNITIONFIRSTTYPECODE = R.TYPECODE,
@RECOGNITIONFIRSTTYPE = R.TYPE,
@RECOGNITIONFIRSTAMOUNT = RR.AMOUNTINCURRENCY
from
dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RR
inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RR.REVENUESPLITID = RS.ID
inner join dbo.REVENUESPLIT_EXT RSE on RSE.ID = RS.ID
inner join dbo.FINANCIALTRANSACTION R on RS.FINANCIALTRANSACTIONID = R.ID
where RR.CONSTITUENTID = @CONSTITUENTID
and R.DELETEDON is null
and RS.DELETEDON is null
and RS.TYPECODE <> 1
and
(R.TYPECODE = 1 or
(R.TYPECODE = 3) or -- Matching gift claim
(R.TYPECODE = 5 and RSE.APPLICATIONCODE = 0) or -- Donations on orders
(R.TYPECODE = 0 and RSE.APPLICATIONCODE in (0, 3, 18, 19)) or --Pledge or Payment (Gift or Recurring gift payment)
(R.TYPECODE = 7)) --Auction donation
and (@RECOGNITIONFIRSTDATE is null or RR.EFFECTIVEDATE < @RECOGNITIONFIRSTDATE)
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = R.ID
/*next line is #SITEEXTENTION code*/
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
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)
)
)
order by
RR.EFFECTIVEDATE asc, RR.DATEADDED asc;
-- group member latest recognition
select top 1
@RECOGNITIONLATESTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=RR.CONSTITUENTID),
@RECOGNITIONLATESTRECORDID = R.ID,
@RECOGNITIONLATESTDATE = RR.EFFECTIVEDATE,
@RECOGNITIONLATESTTYPECODE = R.TYPECODE,
@RECOGNITIONLATESTTYPE = R.TYPE,
@RECOGNITIONLATESTAMOUNT = RR.AMOUNTINCURRENCY
from
dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RR
inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RR.REVENUESPLITID = RS.ID
inner join REVENUESPLIT_EXT RSE on RSE.ID = RS.ID
inner join dbo.FINANCIALTRANSACTION R on RS.FINANCIALTRANSACTIONID = R.ID
inner join dbo.GROUPMEMBER GM on RR.CONSTITUENTID = GM.MEMBERID
left join dbo.GROUPMEMBERDATERANGE GMDR on GMDR.GROUPMEMBERID = GM.ID
where @GROUPINCLUDESMEMBERGIVING = 1
and GM.GROUPID = @CONSTITUENTID
and R.DELETEDON is null
and RS.DELETEDON is null
and RS.TYPECODE <> 1
and
(R.TYPECODE = 1 or
(R.TYPECODE = 3) or -- Matching gift claim
(R.TYPECODE = 5 and RSE.APPLICATIONCODE = 0) or -- Donations on orders
(R.TYPECODE = 0 and RSE.APPLICATIONCODE in (0, 3, 18, 19)) or --Pledge or Payment (Gift or Recurring gift payment)
(R.TYPECODE = 7)) --Auction donation
and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= RR.EFFECTIVEDATE))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= RR.EFFECTIVEDATE))
or (GMDR.DATEFROM <= RR.EFFECTIVEDATE and GMDR.DATETO >= RR.EFFECTIVEDATE))
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = R.ID
/*next line is #SITEEXTENTION code*/
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
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)
)
)
order by
RR.EFFECTIVEDATE desc, RR.DATEADDED desc;
-- group latest recognition
select top 1
@RECOGNITIONLATESTCONSTITUENTNAME = (select NAME from dbo.CONSTITUENT where ID=RR.CONSTITUENTID),
@RECOGNITIONLATESTRECORDID = R.ID,
@RECOGNITIONLATESTDATE = RR.EFFECTIVEDATE,
@RECOGNITIONLATESTTYPECODE = R.TYPECODE,
@RECOGNITIONLATESTTYPE = R.TYPE,
@RECOGNITIONLATESTAMOUNT = RR.AMOUNTINCURRENCY
from
dbo.UFN_REVENUERECOGNITION_GETAMOUNTINCURRENCY_BULK(@CURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) RR
inner join dbo.FINANCIALTRANSACTIONLINEITEM RS on RR.REVENUESPLITID = RS.ID
inner join REVENUESPLIT_EXT RSE on RSE.ID = RS.ID
inner join dbo.FINANCIALTRANSACTION R on RS.FINANCIALTRANSACTIONID = R.ID
where RR.CONSTITUENTID = @CONSTITUENTID
and (@RECOGNITIONLATESTDATE is null or RR.EFFECTIVEDATE > @RECOGNITIONLATESTDATE)
and R.DELETEDON is null
and RS.DELETEDON is null
and RS.TYPECODE <> 1
and
(R.TYPECODE = 1 or
(R.TYPECODE = 3) or -- Matching gift claim
(R.TYPECODE = 5 and RSE.APPLICATIONCODE = 0) or -- Donations on orders
(R.TYPECODE = 0 and RSE.APPLICATIONCODE in (0, 3, 18, 19)) or --Pledge or Payment (Gift or Recurring gift payment)
(R.TYPECODE = 7)) --Auction donation
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = R.ID
/*next line is #SITEEXTENTION code*/
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
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)
)
)
order by
RR.EFFECTIVEDATE desc, RR.DATEADDED desc;
select
@GROUPINCLUDESMEMBERGIVING as GROUPINCLUDESMEMBERGIVING,
@ISHOUSEHOLD as ISHOUSEHOLD,
@TOTALNUMBER as TOTALNUMBER,
@TOTALAMOUNT as TOTALAMOUNT,
@TOTALGROUPAMOUNT as TOTALGROUPAMOUNT,
@TOTALGROUPMEMBERAMOUNT as TOTALGROUPMEMBERAMOUNT,
@AVERAGEAMOUNT as AVERAGEAMOUNT,
@MOSTFREQUENTAMOUNT as MOSTFREQUENTAMOUNT,
@TOTALPLEDGEBALANCE as TOTALPLEDGEBALANCE,
@TOTALYEARS as TOTALYEARS,
@CONSECUTIVEYEARS as CONSECUTIVEYEARS,
@GIVENSINCEFISCALYEAR as GIVENSINCEFISCALYEAR,
@LARGESTID as LARGESTID,
@LARGESTRECORDID as LARGESTRECORDID,
@LARGESTDATE as LARGESTDATE,
@LARGESTTYPECODE as LARGESTTYPECODE,
@LARGESTTYPE as LARGESTTYPE,
@LARGESTAMOUNT as LARGESTAMOUNT,
@LARGESTPLEDGEBALANCE as LARGESTPLEDGEBALANCE,
@LARGESTSPLITS as LARGESTSPLITS,
@LARGESTCONSTITUENTNAME as LARGESTCONSTITUENTNAME,
@FIRSTID as FIRSTID,
@FIRSTRECORDID as FIRSTRECORDID,
@FIRSTDATE as FIRSTDATE,
@FIRSTTYPECODE as FIRSTTYPECODE,
@FIRSTTYPE as FIRSTTYPE,
@FIRSTAMOUNT as FIRSTAMOUNT,
@FIRSTPLEDGEBALANCE as FIRSTPLEDGEBALANCE,
@FIRSTSPLITS as FIRSTSPLITS,
@FIRSTCONSTITUENTNAME as FIRSTCONSTITUENTNAME,
@LATESTID as LATESTID,
@LATESTRECORDID as LATESTRECORDID,
@LATESTDATE as LATESTDATE,
@LATESTTYPECODE as LATESTTYPECODE,
@LATESTTYPE as LATESTTYPE,
@LATESTAMOUNT as LATESTAMOUNT,
@LATESTPLEDGEBALANCE as LATESTPLEDGEBALANCE,
@LATESTSPLITS as LATESTSPLITS,
@LATESTCONSTITUENTNAME as LATESTCONSTITUENTNAME,
@RECOGNITIONTOTALNUMBER as RECOGNITIONTOTALNUMBER,
@RECOGNITIONTOTALAMOUNT as RECOGNITIONTOTALAMOUNT,
@RECOGNITIONTOTALGROUPAMOUNT as RECOGNITIONTOTALGROUPAMOUNT,
@RECOGNITIONTOTALGROUPMEMBERAMOUNT as RECOGNITIONTOTALGROUPMEMBERAMOUNT,
@RECOGNITIONAVERAGEAMOUNT as RECOGNITIONAVERAGEAMOUNT,
@RECOGNITIONMOSTFREQUENTAMOUNT as RECOGNITIONMOSTFREQUENTAMOUNT,
@RECOGNITIONTOTALYEARS as RECOGNITIONTOTALYEARS,
@RECOGNITIONCONSECUTIVEYEARS as RECOGNITIONCONSECUTIVEYEARS,
@RECOGNITIONGIVENSINCEFISCALYEAR as RECOGNITIONGIVENSINCEFISCALYEAR,
@RECOGNITIONLARGESTRECORDID as RECOGNITIONLARGESTRECORDID,
@RECOGNITIONLARGESTDATE as RECOGNITIONLARGESTDATE,
@RECOGNITIONLARGESTTYPECODE as RECOGNITIONLARGESTTYPECODE,
@RECOGNITIONLARGESTTYPE as RECOGNITIONLARGESTTYPE,
@RECOGNITIONLARGESTAMOUNT as RECOGNITIONLARGESTAMOUNT,
@RECOGNITIONLARGESTCONSTITUENTNAME as RECOGNITIONLARGESTCONSTITUENTNAME,
@RECOGNITIONFIRSTRECORDID as RECOGNITIONFIRSTRECORDID,
@RECOGNITIONFIRSTDATE as RECOGNITIONFIRSTDATE,
@RECOGNITIONFIRSTTYPECODE as RECOGNITIONFIRSTTYPECODE,
@RECOGNITIONFIRSTTYPE as RECOGNITIONFIRSTTYPE,
@RECOGNITIONFIRSTAMOUNT as RECOGNITIONFIRSTAMOUNT,
@RECOGNITIONFIRSTCONSTITUENTNAME as RECOGNITIONFIRSTCONSTITUENTNAME,
@RECOGNITIONLATESTRECORDID as RECOGNITIONLATESTRECORDID,
@RECOGNITIONLATESTDATE as RECOGNITIONLATESTDATE,
@RECOGNITIONLATESTTYPECODE as RECOGNITIONLATESTTYPECODE,
@RECOGNITIONLATESTTYPE as RECOGNITIONLATESTTYPE,
@RECOGNITIONLATESTAMOUNT as RECOGNITIONLATESTAMOUNT,
@RECOGNITIONLATESTCONSTITUENTNAME as RECOGNITIONLATESTCONSTITUENTNAME,
@GROUPMEMBERTAXCLAIMAMOUNT as GROUPMEMBERTAXCLAIMAMOUNT,
@RECOGNITIONGROUPMEMBERTAXCLAIMAMOUNT as RECOGNITIONGROUPMEMBERTAXCLAIMAMOUNT,
@MATCHEDGIFTSAMOUNT as MATCHEDGIFTSAMOUNT,
CURRENCY.ISO4217,
CURRENCY.DECIMALDIGITS,
CURRENCY.CURRENCYSYMBOL,
CURRENCY.SYMBOLDISPLAYSETTINGCODE
from
-- Have a placeholder row so we can left join to CURRENCY and still have all other values returned
-- if @CURRENCYID is null
(
select 1 as PLACEHOLDERVALUE
) as PLACEHOLDERROW
left join dbo.CURRENCY on ID = @CURRENCYID;