USP_REPORT_ACTIVITY
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @REVENUETRANSACTIONQUERY | uniqueidentifier | IN | |
| @CONSTITUENTID | uniqueidentifier | IN | |
| @REPORTUSERID | nvarchar(128) | IN | |
| @CURRENCYCODE | tinyint | IN | |
| @ALTREPORTUSERID | nvarchar(128) | IN |
Definition
Copy
create procedure dbo.USP_REPORT_ACTIVITY
(
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@REVENUETRANSACTIONQUERY uniqueidentifier = null,
@CONSTITUENTID uniqueidentifier = null,
@REPORTUSERID nvarchar(128) = null,
@CURRENCYCODE tinyint = null, -- (null, 1) = Organization, 0 = Base, 2 = Transaction
@ALTREPORTUSERID nvarchar(128) = null
)
with execute as owner
as
set nocount on;
set transaction isolation level read uncommitted;
declare @STARTDATEEARLIEST datetime = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);
declare @ENDDATELATEST datetime = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);
declare @CURRENTAPPUSERID uniqueidentifier;
declare @ISADMIN bit;
declare @APPUSER_IN_NONRACROLE bit;
declare @APPUSER_IN_NOSECGROUPROLE bit;
set @CURRENTAPPUSERID = dbo.UFN_APPUSER_GETREPORTAPPUSERID(@REPORTUSERID, @ALTREPORTUSERID);
set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
declare @DBOBJECTNAME nvarchar(128);
declare @DBOBJECTTYPE smallint;
if @REVENUETRANSACTIONQUERY is not null begin
if not exists(select ID from dbo.IDSETREGISTER where ID = @REVENUETRANSACTIONQUERY) raiserror('ID set does not exist in the database.', 15, 1);
select @DBOBJECTNAME = DBOBJECTNAME, @DBOBJECTTYPE = OBJECTTYPE from dbo.IDSETREGISTER where ID = @REVENUETRANSACTIONQUERY;
if @DBOBJECTTYPE = 1 set @DBOBJECTNAME = @DBOBJECTNAME + '()';
else if @DBOBJECTTYPE = 2 set @DBOBJECTNAME = @DBOBJECTNAME + '(''' + convert(nvarchar(36), @REVENUETRANSACTIONQUERY) + ''')';
end
if object_id('tempdb..#POTENTIALREVENUE') is not null
drop table #POTENTIALREVENUE;
create table #POTENTIALREVENUE
(
REVPARENTID uniqueidentifier,
PAYMENTDATE datetime,
REVENUEID uniqueidentifier,
REVENUEAMOUNT money,
REVENUETRANSACTIONAMOUNT money,
TOTALREVENUESPLITORGANIZATIONAMOUNT money,
TOTALREVENUESPLITTRANSACTIONAMOUNT money,
TOTALREVENUESPLITAMOUNT money,
TRANSACTIONCURRENCYID uniqueidentifier,
BASECURRENCYID uniqueidentifier
)
declare @POPULATENOPAYMENTSPOTENTIALREVENUETABLESQL as nvarchar(max) = '
insert into #POTENTIALREVENUE
(
REVPARENTID,
PAYMENTDATE,
REVENUEID,
REVENUEAMOUNT,
REVENUETRANSACTIONAMOUNT,
TOTALREVENUESPLITORGANIZATIONAMOUNT,
TOTALREVENUESPLITTRANSACTIONAMOUNT,
TOTALREVENUESPLITAMOUNT,
TRANSACTIONCURRENCYID,
BASECURRENCYID
)
select
REVPARENT.ID as REVPARENTID,
null PAYMENTDATE,
null REVENUEID,
null as REVENUEAMOUNT,
null as REVENUETRANSACTIONAMOUNT,
null as TOTALREVENUESPLITORGANIZATIONAMOUNT,
null as TOTALREVENUESPLITTRANSACTIONAMOUNT,
null as TOTALREVENUESPLITAMOUNT,
null as TRANSACTIONCURRENCYID,
null as BASECURRENCYID
from dbo.FINANCIALTRANSACTION REVPARENT ' +
case when @REVENUETRANSACTIONQUERY is not null then 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on REVPARENT.[ID] = SELECTION.[ID]' else '' end +
'
left outer join RECURRINGGIFTACTIVITY RGA on RGA.SOURCEREVENUEID = REVPARENT.ID
left outer join INSTALLMENTPAYMENT IP on IP.PLEDGEID = REVPARENT.ID
left outer join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on
(
REVENUESPLIT.ID = coalesce(RGA.PAYMENTREVENUEID,IP.PAYMENTID)
and
( -- Only rev splits within date range
exists(select
R.ID
from
dbo.FINANCIALTRANSACTIONLINEITEM as RS
inner join
dbo.FINANCIALTRANSACTION AS R on R.ID = RS.FINANCIALTRANSACTIONID
where
R.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) and RS.DELETEDON is null and RS.TYPECODE != 1 and RS.ID = REVENUESPLIT.ID
' +
case when @STARTDATEEARLIEST is not null then ' and cast(R.DATE as datetime) >= @STARTDATEEARLIEST ' else '' end +
case when @ENDDATELATEST is not null then ' and cast(R.DATE as datetime) <= @ENDDATELATEST ' else '' end +
'
)
)
)
left outer join dbo.FINANCIALTRANSACTION REVENUE with (nolock) on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
where REVPARENT.TYPECODE in (1, 2, 3, 6, 8,15) and REVPARENT.DELETEDON is null and REVENUE.ID is null ' +
case when @STARTDATEEARLIEST is not null then ' and (cast(REVPARENT.DATE as datetime) >= @STARTDATEEARLIEST) ' else '' end +
case when @ENDDATELATEST is not null then ' and (cast(REVPARENT.DATE as datetime) <= @ENDDATELATEST) ' else '' end +
case when @CONSTITUENTID is not null then ' and (REVPARENT.CONSTITUENTID = @CONSTITUENTID) ' else '' end;
exec sp_executesql @POPULATENOPAYMENTSPOTENTIALREVENUETABLESQL, N'@STARTDATEEARLIEST datetime, @ENDDATELATEST datetime, @CONSTITUENTID uniqueidentifier',
@STARTDATEEARLIEST=@STARTDATEEARLIEST,@ENDDATELATEST=@ENDDATELATEST,@CONSTITUENTID=@CONSTITUENTID;
declare @POPULATERECURRINGGIFTPAYMENTSPOTENTIALREVENUETABLESQL as nvarchar(max) = '
insert into #POTENTIALREVENUE
(
REVPARENTID,
PAYMENTDATE,
REVENUEID,
REVENUEAMOUNT,
REVENUETRANSACTIONAMOUNT,
TOTALREVENUESPLITORGANIZATIONAMOUNT,
TOTALREVENUESPLITTRANSACTIONAMOUNT,
TOTALREVENUESPLITAMOUNT,
TRANSACTIONCURRENCYID,
BASECURRENCYID
)
select
REVPARENT.ID as REVPARENTID,
cast(REVENUE.DATE as datetime) PAYMENTDATE,
REVENUE.ID as REVENUEID,
REVENUE.BASEAMOUNT as REVENUEAMOUNT,
REVENUE.TRANSACTIONAMOUNT as REVENUETRANSACTIONAMOUNT,
Sum(REVENUESPLIT.ORGAMOUNT) as TOTALREVENUESPLITORGANIZATIONAMOUNT,
Sum(REVENUESPLIT.TRANSACTIONAMOUNT) as TOTALREVENUESPLITTRANSACTIONAMOUNT,
Sum(REVENUESPLIT.BASEAMOUNT) as TOTALREVENUESPLITAMOUNT,
REVENUE.TRANSACTIONCURRENCYID,
REVENUE_EXT.NONPOSTABLEBASECURRENCYID
from dbo.FINANCIALTRANSACTION REVENUE
inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join RECURRINGGIFTACTIVITY RGA on RGA.PAYMENTREVENUEID = REVENUESPLIT.ID
inner join FINANCIALTRANSACTION as REVPARENT with (nolock) on REVPARENT.ID = RGA.SOURCEREVENUEID ' +
case when @REVENUETRANSACTIONQUERY is not null then 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on REVPARENT.[ID] = SELECTION.[ID]' else '' end +
'
where REVPARENT.TYPECODE in (1, 2, 3, 6, 8, 15) and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) and REVENUESPLIT.DELETEDON is null and REVPARENT.DELETEDON is null
and not (REVENUESPLIT_EXT.TYPECODE = 2 and REVENUESPLIT_EXT.APPLICATIONCODE = 3) ' +
case when @STARTDATEEARLIEST is not null then ' and (cast(REVENUE.DATE as datetime) >= @STARTDATEEARLIEST) ' else '' end +
case when @ENDDATELATEST is not null then ' and (cast(REVENUE.DATE as datetime) <= @ENDDATELATEST) ' else '' end +
case when @CONSTITUENTID is not null then ' and (REVENUE.CONSTITUENTID = @CONSTITUENTID or REVPARENT.CONSTITUENTID = @CONSTITUENTID) ' else '' end +
'group by REVENUE.ID, REVPARENT.ID, REVENUE.BASEAMOUNT, REVENUE.TRANSACTIONAMOUNT, REVENUE.DATE, REVENUE.TRANSACTIONCURRENCYID, REVENUE_EXT.NONPOSTABLEBASECURRENCYID';
exec sp_executesql @POPULATERECURRINGGIFTPAYMENTSPOTENTIALREVENUETABLESQL, N'@STARTDATEEARLIEST datetime, @ENDDATELATEST datetime, @CONSTITUENTID uniqueidentifier',
@STARTDATEEARLIEST=@STARTDATEEARLIEST,@ENDDATELATEST=@ENDDATELATEST,@CONSTITUENTID=@CONSTITUENTID;
declare @POPULATEPLEDGEPAYMENTSPOTENTIALREVENUETABLESQL as nvarchar(max) = '
insert into #POTENTIALREVENUE
(
REVPARENTID,
PAYMENTDATE,
REVENUEID,
REVENUEAMOUNT,
REVENUETRANSACTIONAMOUNT,
TOTALREVENUESPLITORGANIZATIONAMOUNT,
TOTALREVENUESPLITTRANSACTIONAMOUNT,
TOTALREVENUESPLITAMOUNT,
TRANSACTIONCURRENCYID,
BASECURRENCYID
)
select
REVPARENT.ID as REVPARENTID,
cast(REVENUE.DATE as datetime) PAYMENTDATE,
REVENUE.ID as REVENUEID,
REVENUE.BASEAMOUNT as REVENUEAMOUNT,
REVENUE.TRANSACTIONAMOUNT as REVENUETRANSACTIONAMOUNT,
Sum(REVENUESPLIT.ORGAMOUNT) as TOTALREVENUESPLITORGANIZATIONAMOUNT,
Sum(REVENUESPLIT.TRANSACTIONAMOUNT) as TOTALREVENUESPLITTRANSACTIONAMOUNT,
Sum(REVENUESPLIT.BASEAMOUNT) as TOTALREVENUESPLITAMOUNT,
REVENUE.TRANSACTIONCURRENCYID,
V.BASECURRENCYID
from dbo.FINANCIALTRANSACTION REVENUE
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUE.ID = REVENUESPLIT.FINANCIALTRANSACTIONID
inner join (select
INSTALLMENTPAYMENT.PAYMENTID,
INSTALLMENTPAYMENT.PLEDGEID,
ROW_NUMBER() over (partition by INSTALLMENTPAYMENT.PAYMENTID order by INSTALLMENTPAYMENT.PAYMENTID) NUM
from dbo.INSTALLMENTPAYMENT) IP on IP.PAYMENTID = REVENUESPLIT.ID AND IP.NUM = 1
inner join FINANCIALTRANSACTION as REVPARENT with (nolock) on REVPARENT.ID = IP.PLEDGEID ' +
case when @REVENUETRANSACTIONQUERY is not null then 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on REVPARENT.[ID] = SELECTION.[ID]' else '' end +
'
where REVPARENT.TYPECODE in (1, 2, 3, 6, 8, 15) and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9,15) and REVENUESPLIT.DELETEDON is null and REVPARENT.DELETEDON is null ' +
case when @STARTDATEEARLIEST is not null then ' and (cast(REVENUE.DATE as datetime) >= @STARTDATEEARLIEST) ' else '' end +
case when @ENDDATELATEST is not null then ' and (cast(REVENUE.DATE as datetime) <= @ENDDATELATEST) ' else '' end +
case when @CONSTITUENTID is not null then ' and (REVENUE.CONSTITUENTID = @CONSTITUENTID or REVPARENT.CONSTITUENTID = @CONSTITUENTID) ' else '' end +
'group by REVENUE.ID, REVPARENT.ID, REVENUE.BASEAMOUNT, REVENUE.TRANSACTIONAMOUNT, REVENUE.DATE, REVENUE.TRANSACTIONCURRENCYID, V.BASECURRENCYID';
exec sp_executesql @POPULATEPLEDGEPAYMENTSPOTENTIALREVENUETABLESQL, N'@STARTDATEEARLIEST datetime, @ENDDATELATEST datetime, @CONSTITUENTID uniqueidentifier',
@STARTDATEEARLIEST=@STARTDATEEARLIEST,@ENDDATELATEST=@ENDDATELATEST,@CONSTITUENTID=@CONSTITUENTID;
declare @ORGANIZATIONCURRENCYID as uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @ORIGINCODE tinyint;
select @ORIGINCODE = coalesce(ORGANIZATIONAMOUNTORIGINCODE, 0) from dbo.MULTICURRENCYCONFIGURATION;
declare @CURRENCYID uniqueidentifier;
declare @DECIMALDIGITS tinyint;
declare @ROUNDINGTYPECODE tinyint;
declare @CURRENCYPROPERTIESOUTERAPPLY nvarchar(255);
declare @PARENTCURRENCYPROPERTIESOUTERAPPLY nvarchar(255);
declare @PARENTCURRENCYPROPERTIESOUTERAPPLY1 nvarchar(255);
if @CURRENCYCODE = 0 -- Base
select
@CURRENCYPROPERTIESOUTERAPPLY = 'outer apply dbo.UFN_CURRENCY_GETPROPERTIES(POTENTIALREVENUE.BASECURRENCYID) CURRENCYPROPERTIES ',
@PARENTCURRENCYPROPERTIESOUTERAPPLY = 'outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, REVPARENT.BASECURRENCYID)) PARENTCURRENCYPROPERTIES ',
@PARENTCURRENCYPROPERTIESOUTERAPPLY1 = 'outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID)) PARENTCURRENCYPROPERTIES ';
else if @CURRENCYCODE = 2 -- Transaction
select
@CURRENCYPROPERTIESOUTERAPPLY = 'outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(POTENTIALREVENUE.TRANSACTIONCURRENCYID, POTENTIALREVENUE.BASECURRENCYID)) CURRENCYPROPERTIES ',
@PARENTCURRENCYPROPERTIESOUTERAPPLY = 'outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(REVPARENT.TRANSACTIONCURRENCYID, REVPARENT.BASECURRENCYID)) PARENTCURRENCYPROPERTIES ',
@PARENTCURRENCYPROPERTIESOUTERAPPLY1 = 'outer apply dbo.UFN_CURRENCY_GETPROPERTIES(coalesce(REVPARENT.TRANSACTIONCURRENCYID, V.BASECURRENCYID)) PARENTCURRENCYPROPERTIES ';
else -- Organization
begin
select
@CURRENCYPROPERTIESOUTERAPPLY = 'outer apply dbo.UFN_CURRENCY_GETPROPERTIES(' + case when @ORGANIZATIONCURRENCYID is null then 'POTENTIALREVENUE.BASECURRENCYID' else '@ORGANIZATIONCURRENCYID' end + ') CURRENCYPROPERTIES ',
@PARENTCURRENCYPROPERTIESOUTERAPPLY = 'outer apply dbo.UFN_CURRENCY_GETPROPERTIES(' + case when @ORGANIZATIONCURRENCYID is null then 'REVPARENT.BASECURRENCYID' else '@ORGANIZATIONCURRENCYID' end + ') PARENTCURRENCYPROPERTIES ',
@PARENTCURRENCYPROPERTIESOUTERAPPLY1 = 'outer apply dbo.UFN_CURRENCY_GETPROPERTIES(' + case when @ORGANIZATIONCURRENCYID is null then 'V.BASECURRENCYID' else '@ORGANIZATIONCURRENCYID' end + ') PARENTCURRENCYPROPERTIES ';
select
@CURRENCYID = CURRENCY.ID,
@DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
@ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
from dbo.CURRENCY where CURRENCY.ID = @ORGANIZATIONCURRENCYID;
end
--select 'USP_REPORT_ACTIVITY - #PotentialRevenue' as QUERYNAME, * from #PotentialRevenue
-- KevinHi - WI209626: I am removing the use of start date from all calls to the TVF UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK3
-- This is because having a date range on a Balance calculation is excluding transactions for this report whose creation date is outside
-- of the date range
if object_id('tempdb..#REVPARENT') is not null
drop table #REVPARENT;
create table #REVPARENT
(
ID uniqueidentifier,
BALANCEINCURRENCY money,
TRANSACTIONBALANCE money,
CURRENCYID uniqueidentifier,
TRANSACTIONCURRENCYID uniqueidentifier,
BASECURRENCYID uniqueidentifier,
DATE datetime,
TRANSACTIONTYPECODE int,
TRANSACTIONTYPE nvarchar(100) collate database_default,
APPEALID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
AMOUNT money,
TRANSACTIONAMOUNT money,
ORGANIZATIONAMOUNT money,
AMOUNTINCURRENCY money,
INTERMEDIATEBALANCE money
);
declare @POPULATEREVPARENTTABLESQL as nvarchar(max) = '
insert into #REVPARENT
(
ID,
BALANCEINCURRENCY,
TRANSACTIONBALANCE,
CURRENCYID,
TRANSACTIONCURRENCYID,
BASECURRENCYID,
DATE,
TRANSACTIONTYPECODE,
TRANSACTIONTYPE,
APPEALID,
CONSTITUENTID,
AMOUNT,
TRANSACTIONAMOUNT,
ORGANIZATIONAMOUNT,
AMOUNTINCURRENCY,
INTERMEDIATEBALANCE
)
select
REVPARENT.ID,
REVPARENT.BALANCEINCURRENCY,
REVPARENT.TRANSACTIONBALANCE,
REVPARENT.CURRENCYID,
REVPARENT.TRANSACTIONCURRENCYID,
REVPARENT.BASECURRENCYID,
REVPARENT.DATE,
REVPARENT.TRANSACTIONTYPECODE,
REVPARENT.TRANSACTIONTYPE,
REVPARENT.APPEALID,
REVPARENT.CONSTITUENTID,
REVPARENT.AMOUNT,
REVPARENT.TRANSACTIONAMOUNT,
REVPARENT.ORGANIZATIONAMOUNT,
REVPARENT.AMOUNTINCURRENCY,
REVPARENT.INTERMEDIATEBALANCE
from
#PotentialRevenue POTENTIALREVENUE
outer apply
dbo.UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK3
(
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@DECIMALDIGITS,
@ROUNDINGTYPECODE,
null,
@ENDDATE,
@ORIGINCODE,
@CURRENCYCODE,
POTENTIALREVENUE.REVPARENTID
) as REVPARENT
group by REVPARENT.ID,
REVPARENT.BALANCEINCURRENCY,
REVPARENT.TRANSACTIONBALANCE,
REVPARENT.CURRENCYID,
REVPARENT.TRANSACTIONCURRENCYID,
REVPARENT.BASECURRENCYID,
REVPARENT.DATE,
REVPARENT.TRANSACTIONTYPECODE,
REVPARENT.TRANSACTIONTYPE,
REVPARENT.APPEALID,
REVPARENT.CONSTITUENTID,
REVPARENT.AMOUNT,
REVPARENT.TRANSACTIONAMOUNT,
REVPARENT.ORGANIZATIONAMOUNT,
REVPARENT.AMOUNTINCURRENCY,
REVPARENT.INTERMEDIATEBALANCE '
exec sp_executesql @POPULATEREVPARENTTABLESQL, N'@CURRENCYID uniqueidentifier, @ORGANIZATIONCURRENCYID uniqueidentifier, @DECIMALDIGITS tinyint, @ROUNDINGTYPECODE tinyint, @ENDDATE datetime, @ORIGINCODE tinyint, @CURRENCYCODE tinyint',
@CURRENCYID=@CURRENCYID, @ORGANIZATIONCURRENCYID=@ORGANIZATIONCURRENCYID, @DECIMALDIGITS=@DECIMALDIGITS, @ROUNDINGTYPECODE=@ROUNDINGTYPECODE, @ENDDATE=@ENDDATE, @ORIGINCODE=@ORIGINCODE, @CURRENCYCODE=@CURRENCYCODE;
declare @SQLTOEXEC nvarchar(max);
set @SQLTOEXEC =
'select ''http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID='' + CONVERT(nvarchar(36),CONSTITUENT.ID) as CONSTITUENTID,
CONSTITUENT.KEYNAME,
CONSTITUENT_NF.NAME,
REVPARENT.ID PARENTID,
cast(REVPARENT.DATE as datetime) PARENTDATE,
REVPARENT.TRANSACTIONTYPE PARENTTRANSACTIONTYPE,
REVPARENT.TRANSACTIONTYPECODE PARENTTRANSACTIONTYPECODE,
REVPARENT.%CURRENCYAMOUNT% PARENTAMOUNT,
dbo.UDA_BUILDLIST(distinct DESIGNATION.NAME) DESIGNATIONLIST,
POTENTIALREVENUE.PAYMENTDATE,
POTENTIALREVENUE.TOTALREVENUESPLIT%CURRENCYAMOUNT% PAYMENTAMOUNT,
case when REVPARENT.TRANSACTIONTYPECODE in (1, 3, 6, 8, 15)
then REVPARENT.BALANCEINCURRENCY
else 0.00 end PLEDGEBALANCE,
case when REVPARENT.TRANSACTIONTYPECODE = 2
then dbo.UFN_RECURRINGGIFT_GETBALANCEASOFINCURRENCY(REVPARENT.ID, @ENDDATE, ' + case @CURRENCYCODE when 0 then 'REVPARENT.BASECURRENCYID' when 2 then 'REVPARENT.TRANSACTIONCURRENCYID' else '@ORGANIZATIONCURRENCYID' end + ')
else 0.00 end RECURRINGBALANCE,
cast(case when POTENTIALREVENUE.REVENUEID is not null then ''Payment'' else null end as nvarchar(10)) TRANSACTIONTYPE,
PARENTCURRENCYPROPERTIES.ISO4217 PARENTISOCURRENCYCODE,
PARENTCURRENCYPROPERTIES.CURRENCYSYMBOL PARENTCURRENCYSYMBOL,
PARENTCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE PARENTSYMBOLDISPLAYSETTINGCODE,
PARENTCURRENCYPROPERTIES.DECIMALDIGITS PARENTDECIMALDIGITS,
CURRENCYPROPERTIES.ISO4217 ISOCURRENCYCODE,
CURRENCYPROPERTIES.CURRENCYSYMBOL,
CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
CURRENCYPROPERTIES.DECIMALDIGITS
from #PotentialRevenue POTENTIALREVENUE
inner join #REVPARENT REVPARENT on POTENTIALREVENUE.REVPARENTID = REVPARENT.ID
inner join CONSTITUENT with (nolock) on CONSTITUENT.ID = REVPARENT.CONSTITUENTID
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = REVPARENT.ID and LI.DELETEDON is null and LI.TYPECODE != 1
inner join REVENUESPLIT_EXT REVPARENTSPLIT on LI.ID = REVPARENTSPLIT.ID
left join DESIGNATION on REVPARENTSPLIT.DESIGNATIONID = DESIGNATION.ID ' + nchar(13);
if @CURRENCYCODE = 0 --base
begin
set @SQLTOEXEC = @SQLTOEXEC +
' inner join REVENUE_EXT on REVENUE_EXT.ID = POTENTIALREVENUE.REVPARENTID ' + nchar(13);
end
set @SQLTOEXEC = @SQLTOEXEC +
' outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) CONSTITUENT_NF '
+ @CURRENCYPROPERTIESOUTERAPPLY + @PARENTCURRENCYPROPERTIESOUTERAPPLY + nchar(13);
set @SQLTOEXEC = @SQLTOEXEC +
'where (@ISADMIN = 1 or (
(
@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1
)
and exists (
-- Check site security
select HASPERMISSION
from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVPARENT.ID) REVSITES
cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''C99B9784-809E-489E-B46D-715543ECA82F'', REVSITES.SITEID)
)
)
)
and (not (REVPARENT.AMOUNT = 0 and REVPARENT.TRANSACTIONAMOUNT > 0))
and (POTENTIALREVENUE.REVENUEID is null or (not (POTENTIALREVENUE.REVENUEAMOUNT = 0 and POTENTIALREVENUE.REVENUETRANSACTIONAMOUNT > 0)))
group by CONSTITUENT.ID, POTENTIALREVENUE.REVENUEID, REVPARENT.ID, CONSTITUENT.KEYNAME, CONSTITUENT_NF.NAME, cast(REVPARENT.DATE as datetime),
REVPARENT.TRANSACTIONTYPE, REVPARENT.%CURRENCYAMOUNT%, POTENTIALREVENUE.PAYMENTDATE, POTENTIALREVENUE.TOTALREVENUESPLIT%CURRENCYAMOUNT%, REVPARENT.TRANSACTIONTYPECODE,
REVPARENT.BASECURRENCYID, REVPARENT.TRANSACTIONCURRENCYID,
CURRENCYPROPERTIES.ISO4217, CURRENCYPROPERTIES.CURRENCYSYMBOL, CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, CURRENCYPROPERTIES.DECIMALDIGITS,
PARENTCURRENCYPROPERTIES.ISO4217, PARENTCURRENCYPROPERTIES.CURRENCYSYMBOL, PARENTCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, PARENTCURRENCYPROPERTIES.DECIMALDIGITS, REVPARENT.BALANCEINCURRENCY' + nchar(13);
/* union writeoffs */
set @SQLTOEXEC = @SQLTOEXEC +
'union all
select ''http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID='' + CONVERT(nvarchar(36),CONSTITUENT.ID) as CONSTITUENTID,
CONSTITUENT.KEYNAME,
CONSTITUENT_NF.NAME,
REVPARENT.ID PARENTID,
cast(REVPARENT.DATE as datetime) PARENTDATE,
REVPARENT.TYPE PARENTTRANSACTIONTYPE,
REVPARENT.TYPECODE PARENTTRANSACTIONTYPECODE,
REVPARENT.%CURRENCYAMOUNT1% PARENTAMOUNT,
dbo.UDA_BUILDLIST(distinct DESIGNATION.NAME) DESIGNATIONLIST,
cast(WRITEOFF.DATE as datetime) PAYMENTDATE,
(select sum(INSTALLMENTSPLITWRITEOFF.%CURRENCYAMOUNT%) from dbo.INSTALLMENTSPLITWRITEOFF
inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
where INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
and (not (INSTALLMENTSPLITWRITEOFF.AMOUNT = 0 and INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT > 0))
) PAYMENTAMOUNT,
(
select BALANCEINCURRENCY
from
dbo.UFN_PLEDGE_GETREVALUEDBALANCEINCURRENCY_BULK3
(
@CURRENCYID,
@ORGANIZATIONCURRENCYID,
@DECIMALDIGITS,
@ROUNDINGTYPECODE,
null,
@ENDDATE,
@ORIGINCODE,
@CURRENCYCODE,
REVPARENT.ID
) as REVENUEBALANCE
) as PLEDGEBALANCE,
0.00 RECURRINGBALANCE,
''Write-off'' TRANSACTIONTYPE,
PARENTCURRENCYPROPERTIES.ISO4217 PARENTISOCURRENCYCODE,
PARENTCURRENCYPROPERTIES.CURRENCYSYMBOL PARENTCURRENCYSYMBOL,
PARENTCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE PARENTSYMBOLDISPLAYSETTINGCODE,
PARENTCURRENCYPROPERTIES.DECIMALDIGITS PARENTDECIMALDIGITS,
PARENTCURRENCYPROPERTIES.ISO4217 ISOCURRENCYCODE,
PARENTCURRENCYPROPERTIES.CURRENCYSYMBOL,
PARENTCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
PARENTCURRENCYPROPERTIES.DECIMALDIGITS
from dbo.FINANCIALTRANSACTION as REVPARENT
inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVPARENT.ID = V.FINANCIALTRANSACTIONID
inner join CONSTITUENT with (nolock) on CONSTITUENT.ID = REVPARENT.CONSTITUENTID
inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = REVPARENT.ID and LI.DELETEDON is null and LI.TYPECODE != 1
inner join REVENUESPLIT_EXT REVPARENTSPLIT on LI.ID = REVPARENTSPLIT.ID
inner join DESIGNATION on REVPARENTSPLIT.DESIGNATIONID = DESIGNATION.ID
inner join INSTALLMENT on INSTALLMENT.REVENUEID = REVPARENT.ID
inner join INSTALLMENTWRITEOFF on INSTALLMENTWRITEOFF.INSTALLMENTID = INSTALLMENT.ID
inner join dbo.FINANCIALTRANSACTION WRITEOFF on INSTALLMENTWRITEOFF.WRITEOFFID = WRITEOFF.ID and WRITEOFF.TYPECODE = 20 and WRITEOFF.DELETEDON is null
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENT.ID) CONSTITUENT_NF ' + nchar(13);
if @CURRENCYCODE = 0 --base
begin
set @SQLTOEXEC = @SQLTOEXEC +
'inner join REVENUE_EXT on REVENUE_EXT.ID = REVPARENT.ID ' + nchar(13);
end
set @SQLTOEXEC = @SQLTOEXEC + @PARENTCURRENCYPROPERTIESOUTERAPPLY1 + nchar(13);
if @REVENUETRANSACTIONQUERY is not null
set @SQLTOEXEC = @SQLTOEXEC + 'inner join dbo.' + @DBOBJECTNAME + ' as SELECTION on REVPARENT.[ID] = SELECTION.[ID]' + nchar(13);
set @SQLTOEXEC = @SQLTOEXEC +
'where REVPARENT.TYPECODE in (1, 2, 3, 6, 8, 15) and LI.DELETEDON is null ' +
case when @STARTDATEEARLIEST is not null then ' and (cast(WRITEOFF.DATE as datetime) >= @STARTDATEEARLIEST) ' else '' end +
case when @ENDDATELATEST is not null then ' and (cast(WRITEOFF.DATE as datetime) <= @ENDDATELATEST) ' else '' end +
case when @CONSTITUENTID is not null then ' and (CONSTITUENT.ID = @CONSTITUENTID) ' else '' end +
'
and (@ISADMIN = 1 or (
(
@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENT.ID, @APPUSER_IN_NOSECGROUPROLE) = 1
)
and exists (
select HASPERMISSION
from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVPARENT.ID) REVSITES
cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, ''C99B9784-809E-489E-B46D-715543ECA82F'', REVSITES.SITEID)
)
)
)
and (not (REVPARENT.BASEAMOUNT = 0 and REVPARENT.TRANSACTIONAMOUNT > 0))
group by CONSTITUENT.ID, REVPARENT.ID, CONSTITUENT.KEYNAME, CONSTITUENT_NF.NAME, cast(REVPARENT.DATE as datetime),
REVPARENT.TYPE, REVPARENT.%CURRENCYAMOUNT1%, cast(WRITEOFF.DATE as datetime), WRITEOFF.ID, REVPARENT.TYPECODE,
V.BASECURRENCYID, REVPARENT.TRANSACTIONCURRENCYID,
PARENTCURRENCYPROPERTIES.ISO4217, PARENTCURRENCYPROPERTIES.CURRENCYSYMBOL, PARENTCURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE, PARENTCURRENCYPROPERTIES.DECIMALDIGITS' + nchar(13);
if @CURRENCYCODE = 1
begin
set @SQLTOEXEC = replace(@SQLTOEXEC, '%CURRENCYAMOUNT%', 'ORGANIZATIONAMOUNT');
set @SQLTOEXEC = replace(@SQLTOEXEC, '%CURRENCYAMOUNT1%', 'ORGAMOUNT');
end
else if @CURRENCYCODE = 2
begin
set @SQLTOEXEC = replace(@SQLTOEXEC, '%CURRENCYAMOUNT%', 'TRANSACTIONAMOUNT');
set @SQLTOEXEC = replace(@SQLTOEXEC, '%CURRENCYAMOUNT1%', 'TRANSACTIONAMOUNT');
end
else
begin
set @SQLTOEXEC = replace(@SQLTOEXEC, '%CURRENCYAMOUNT%', 'AMOUNT');
set @SQLTOEXEC = replace(@SQLTOEXEC, '%CURRENCYAMOUNT1%', 'BASEAMOUNT');
end
set @SQLTOEXEC = @SQLTOEXEC +
'order by
CONSTITUENT.KEYNAME, PAYMENTDATE'
exec sp_executesql @SQLTOEXEC,
N'@STARTDATE datetime, @ENDDATE datetime, @CONSTITUENTID uniqueidentifier, @ISADMIN bit, @APPUSER_IN_NONRACROLE bit, @APPUSER_IN_NOSECGROUPROLE bit, @CURRENTAPPUSERID uniqueidentifier, @CURRENCYPROPERTIESOUTERAPPLY nvarchar(255), @ORGANIZATIONCURRENCYID as uniqueidentifier, @ORIGINCODE tinyint, @CURRENCYCODE tinyint, @CURRENCYID uniqueidentifier, @DECIMALDIGITS tinyint, @ROUNDINGTYPECODE tinyint, @STARTDATEEARLIEST datetime, @ENDDATELATEST datetime',
@STARTDATE=@STARTDATE, @ENDDATE=@ENDDATE, @CONSTITUENTID=@CONSTITUENTID, @ISADMIN=@ISADMIN, @APPUSER_IN_NONRACROLE=@APPUSER_IN_NONRACROLE, @APPUSER_IN_NOSECGROUPROLE=@APPUSER_IN_NOSECGROUPROLE, @CURRENTAPPUSERID=@CURRENTAPPUSERID, @CURRENCYPROPERTIESOUTERAPPLY=@CURRENCYPROPERTIESOUTERAPPLY,
@ORGANIZATIONCURRENCYID=@ORGANIZATIONCURRENCYID, @ORIGINCODE=@ORIGINCODE, @CURRENCYCODE=@CURRENCYCODE, @CURRENCYID=@CURRENCYID, @DECIMALDIGITS=@DECIMALDIGITS, @ROUNDINGTYPECODE=@ROUNDINGTYPECODE, @STARTDATEEARLIEST=@STARTDATEEARLIEST, @ENDDATELATEST=@ENDDATELATEST;