USP_REPORT_PLEDGEASOF
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ASOFDATE | date | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @PDACCOUNTSYSTEMID | uniqueidentifier | IN | |
| @POSTSTATUSCODEBITMAP | tinyint | IN | |
| @ALLDETAILS | smallint | IN | |
| @GROUPBY | tinyint | IN |
Definition
Copy
create procedure [dbo].[USP_REPORT_PLEDGEASOF]
(
@ASOFDATE date = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@PDACCOUNTSYSTEMID uniqueidentifier = null,
@POSTSTATUSCODEBITMAP tinyint = 4,
@ALLDETAILS smallint = 0,
@GROUPBY tinyint = 0
)
as
set nocount on
--Force the default values if null is specified (for BBMetal bug)
select @ALLDETAILS = isnull(@ALLDETAILS,0),
@GROUPBY = isnull(@GROUPBY,0),
@POSTSTATUSCODEBITMAP = isnull(@POSTSTATUSCODEBITMAP,4)
declare @ISADMIN bit;
declare @APPUSER_IN_NONRACROLE bit;
declare @APPUSER_IN_NOSECGROUPROLE bit;
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 @SELECTEDCURRENCYID uniqueidentifier
select @SELECTEDCURRENCYID = BASECURRENCYID
from dbo.PDACCOUNTSYSTEM inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
where PDACCOUNTSYSTEM.ID = @PDACCOUNTSYSTEMID
declare @DECIMALDIGITS tinyint;
declare @ROUNDINGTYPECODE tinyint;
declare @ISOCODE nvarchar(3);
declare @SYMBOLDISPLAYSETTINGCODE tinyint;
declare @CURRENCYSYMBOL nvarchar(5);
select
@DECIMALDIGITS = DECIMALDIGITS,
@ROUNDINGTYPECODE = ROUNDINGTYPECODE,
@ISOCODE = ISO4217,
@SYMBOLDISPLAYSETTINGCODE = SYMBOLDISPLAYSETTINGCODE,
@CURRENCYSYMBOL = CURRENCYSYMBOL
from
dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID);
declare @USERGRANTEDTRANSACTIONPAGE bit = 1;
declare @USERGRANTEDDESIGNATIONPAGE bit = 0;
if @ISADMIN = 1
begin
set @USERGRANTEDTRANSACTIONPAGE = 1;
set @USERGRANTEDDESIGNATIONPAGE = 1;
end
else
begin
select @USERGRANTEDTRANSACTIONPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, 'd00e6c42-2434-4d85-8a04-2323ca6bb2e7')
select @USERGRANTEDDESIGNATIONPAGE = [dbo].[UFN_SECURITY_APPUSER_GRANTED_FORM_IN_SYSTEMROLE](@CURRENTAPPUSERID, '4EADC264-0A44-4DF5-8C8C-D89A1C48746C');
end
declare @DesignationNames table (DesignationID uniqueidentifier primary key, DesignationName varchar(255))
insert into @DesignationNames (DesignationID, DesignationName)
select V1.DESIGNATIONID, CASE
WHEN (dl5.NAME is not null) THEN isnull(dl1.NAME, '<Unspecified>') + ' \ ' + isnull(dl2.NAME, '<Unspecified>') + ' \ ' + isnull(dl3.NAME, '<Unspecified>') + ' \ ' + isnull(dl4.NAME, '<Unspecified>') + ' \ ' + dl5.NAME
WHEN (dl4.NAME is not null) THEN isnull(dl1.NAME, '<Unspecified>') + ' \ ' + isnull(dl2.NAME, '<Unspecified>') + ' \ ' + isnull(dl3.NAME, '<Unspecified>') + ' \ ' + dl4.NAME
WHEN (dl3.NAME is not null) THEN isnull(dl1.NAME, '<Unspecified>') + ' \ ' + isnull(dl2.NAME, '<Unspecified>') + ' \ ' + dl3.NAME
WHEN (dl2.NAME is not null) THEN isnull(dl1.NAME, '<Unspecified>') + ' \ ' + dl2.NAME
ELSE dl1.NAME
END
from
(select distinct REVENUESPLIT_EXT.DESIGNATIONID
from dbo.FINANCIALTRANSACTIONLINEITEM inner join REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
inner join DBO.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
where FINANCIALTRANSACTION.TYPECODE in (0, 1, 20)
and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 2
and FINANCIALTRANSACTIONLINEITEM.POSTDATE <= @ASOFDATE) V1
inner join dbo.DESIGNATION on V1.DESIGNATIONID = DESIGNATION.ID
inner join dbo.DESIGNATIONLEVEL dl1 on DESIGNATION.DESIGNATIONLEVEL1ID = dl1.id
left join dbo.DESIGNATIONLEVEL dl2 on DESIGNATION.DESIGNATIONLEVEL2ID = dl2.id
left join dbo.DESIGNATIONLEVEL dl3 on DESIGNATION.DESIGNATIONLEVEL3ID = dl3.id
left join dbo.DESIGNATIONLEVEL dl4 on DESIGNATION.DESIGNATIONLEVEL4ID = dl4.id
left join dbo.DESIGNATIONLEVEL dl5 on DESIGNATION.DESIGNATIONLEVEL5ID = dl5.id
create table #tempPledges (PledgeID uniqueidentifier,ConstituentID uniqueidentifier, DesignationID uniqueidentifier, PledgeDate date, UserDefinedID varchar(50), PledgeAmount money, PostDate date, PRIMARY KEY (PledgeID, DesignationID) )
insert into #tempPledges (PledgeID, ConstituentID, DesignationID, PledgeDate, UserDefinedID, PledgeAmount, PostDate)
select V1.FINANCIALTRANSACTIONID, V1.CONSTITUENTID, V1.DESIGNATIONID, convert(date,V1.DATE), V1.USERDEFINEDID, sum(V1.BASEAMOUNT), min(V1.POSTDATE) as POSTDATE
from
(select FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID, FINANCIALTRANSACTION.DATE, FINANCIALTRANSACTIONLINEITEM.POSTDATE, FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT, REVENUESPLIT_EXT.DESIGNATIONID,
dense_rank() over (partition by FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID order by isnull(FINANCIALTRANSACTIONLINEITEMADJUSTMENT.DATEADDED, '1900-01-01') desc) as rownum,
isnull(FINANCIALTRANSACTIONLINEITEMADJUSTMENT.CONSTITUENTID, FINANCIALTRANSACTION.CONSTITUENTID) as CONSTITUENTID, isnull(nullif(FINANCIALTRANSACTION.USERDEFINEDID,''), FINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID) as USERDEFINEDID,
FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE
from dbo.FINANCIALTRANSACTION inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
left join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = FINANCIALTRANSACTIONLINEITEMADJUSTMENT.ID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
where FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0
and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 2
and FINANCIALTRANSACTION.TYPECODE = 1
and FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
and FINANCIALTRANSACTION.POSTSTATUSCODE = 2
and FINANCIALTRANSACTIONLINEITEM.POSTDATE <= @AsOfDate
and (FINANCIALTRANSACTION.DELETEDON is null
or exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID and POSTSTATUSCODE = 1 and TYPECODE = 1)
)
) V1
where rownum = 1
group by V1.FINANCIALTRANSACTIONID, V1.DESIGNATIONID, V1.DATE, V1.CONSTITUENTID, V1.UserDefinedID
--**********Get the latest adjustment that would have been valid on @AsOfDate for payments to pledges in #tempPledges********************
create table #tempAdjustmentsAsOf (PaymentID uniqueidentifier PRIMARY KEY, AdjustmentID uniqueidentifier)
insert into #tempAdjustmentsAsOf (PaymentID, AdjustmentID)
select distinct V2.PaymentID, V2.AdjustmentID
from
(select V1.PaymentID, FinancialTransactionLineItemAdjustment.ID as AdjustmentID, Dense_Rank() over (Partition by v1.PaymentID order by FinancialTransactionLineItemAdjustment.DateAdded desc) as RowNum
from
(select distinct PaymentFTLI.FinancialTransactionID as PaymentID
from #tempPledges inner join dbo.FinancialTransactionLineItem as PledgeFTLI on #tempPledges.PledgeID = PledgeFTLI.FinancialTransactionID
inner join dbo.FinancialTransactionLineItem as PaymentFTLI on PledgeFTLI.ID = PaymentFTLI.SourceLineItemID
where PaymentFTLI.PostStatusCode = 2
and PaymentFTLI.PostDate <= @AsOfDate) as V1
inner join FinancialTransactionLineItem on V1.PaymentID = FinancialTransactionLineItem.FinancialTransactionID
inner join FinancialTransactionLineItemAdjustment on FinancialTransactionLineItem.FinancialTransactionLineItemAdjustmentID = FinancialTransactionLineItemAdjustment.ID
left join FinancialTransactionLineItem ReversedLineItem on ReversedLineItem.ID = FinancialTransactionLineItem.ReversedLineItemID
where FinancialTransactionLineItem.PostDate <= @AsOfDate
and FinancialTransactionLineItem.PostStatusCode = 2
and FinancialTransactionLineItemAdjustment.Date < dateadd(d,1,@AsOfDate)
and FinancialTransactionLineItem.typecode != 7 -- ignore gift fee
and (ReversedLineItem.TYPECODE is null or ReversedLineItem.TYPECODE != 7 ) -- ignore gift fee
) as V2
where RowNum = 1
create table #tempPayments (PledgeID uniqueidentifier, DesignationID uniqueidentifier, PaymentAmount money, PRIMARY KEY (PledgeID, DesignationID) )
insert into #tempPayments (PledgeID, DesignationID, PaymentAmount)
select V3.ID as PledgeID, V3.DesignationID, sum(BASEAMOUNT)
from
(select FTPledge.ID, REVENUESPLIT_EXT.DESIGNATIONID, FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT
from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
inner join #tempAdjustmentsAsOf
on (FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = #tempAdjustmentsAsOf.PaymentID and FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = #tempAdjustmentsAsOf.AdjustmentID)
left join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = FINANCIALTRANSACTIONLINEITEMADJUSTMENT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLIPledge on FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID = FTLIPledge.ID
inner join dbo.FINANCIALTRANSACTION as FTPledge on FTLIPledge.FINANCIALTRANSACTIONID = FTPledge.ID
where FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0
and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 2
and FINANCIALTRANSACTION.TYPECODE = 0
and FTPledge.TYPECODE = 1
and FTPledge.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
and FINANCIALTRANSACTIONLINEITEM.POSTDATE <= @AsOfDate
and (FINANCIALTRANSACTION.DELETEDON is null
or exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID and POSTSTATUSCODE = 1 and TYPECODE = 1)
)
union all
select FTPledge.ID, REVENUESPLIT_EXT.DESIGNATIONID, FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT
from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
left join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = FINANCIALTRANSACTIONLINEITEMADJUSTMENT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLIPledge on FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID = FTLIPledge.ID
inner join dbo.FINANCIALTRANSACTION as FTPledge on FTLIPledge.FINANCIALTRANSACTIONID = FTPledge.ID
where FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0
and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 2
and FINANCIALTRANSACTION.TYPECODE = 0
and FTPledge.TYPECODE = 1
and FTPledge.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
and FINANCIALTRANSACTIONLINEITEM.POSTDATE <= @AsOfDate
and FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID is null
and (FINANCIALTRANSACTION.DELETEDON is null
or exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID and POSTSTATUSCODE = 1 and TYPECODE = 1)
)) as V3
group by V3.ID, V3.DesignationID
create table #tempWriteOffs (PledgeID uniqueidentifier, DesignationID uniqueidentifier, WriteOffAmount money, PRIMARY KEY (PledgeID, DesignationID) )
insert into #tempWriteOffs (PledgeID, DesignationID, WriteOffAmount)
select V4.ID as PledgeID, V4.DesignationID, sum(BASEAMOUNT)
from
(select FTPledge.ID, REVENUESPLIT_EXT.DESIGNATIONID, FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
dense_rank() over (partition by FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID order by isnull(FINANCIALTRANSACTIONLINEITEMADJUSTMENT.DATEADDED,'1900-01-01') desc) as rownum
from dbo.FINANCIALTRANSACTION inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
left join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = FINANCIALTRANSACTIONLINEITEMADJUSTMENT.ID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION as FTPledge on FINANCIALTRANSACTION.PARENTID = FTPledge.ID
where FINANCIALTRANSACTION.TYPECODE = 20
and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE = 2
and FTPLEDGE.TYPECODE = 1
and FTPledge.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID
and FINANCIALTRANSACTIONLINEITEM.POSTDATE <= @AsOfDate
and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0
and (FINANCIALTRANSACTION.DELETEDON is null
or exists (select 1 from dbo.FINANCIALTRANSACTIONLINEITEM where FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID and POSTSTATUSCODE = 1 and TYPECODE = 1)
)
) V4
where V4.rownum = 1
group by V4.ID, V4.DesignationID
if @ALLDETAILS = 1 and @GROUPBY = 0
select CONSTITUENT,
'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + convert(nvarchar(36),CONSTITUENTID) CONSTITUENTLINK,
DESIGNATIONNAME,
case when @USERGRANTEDDESIGNATIONPAGE = 1 then 'http://www.blackbaud.com/DESIGNATIONID?DESIGNATIONID=' + convert(nvarchar(36),DESIGNATIONID) else convert(nvarchar(90),null) end DESIGNATIONLINK,
PLEDGEDATE,
POSTDATE,
USERDEFINEDID,
case when @USERGRANTEDTRANSACTIONPAGE = 1 then 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + convert(nvarchar(36),PLEDGEID) else convert(nvarchar(81),null) end PLEDGELINK,
PLEDGEAMOUNT,
WRITEOFFAMOUNT,
PAYMENTAMOUNT,
@ISOCODE PLEDGEISOCURRENCYCODE,
@CURRENCYSYMBOL PLEDGECURRENCYSYMBOL,
@SYMBOLDISPLAYSETTINGCODE PLEDGECURRENCYSYMBOLDISPLAYSETTINGCODE,
@DECIMALDIGITS PLEDGEDECIMALDIGITS,
0 as HASNOTPOSTEDTRANSACTIONS,
ROWTYPE
from
(select case CONSTITUENT.ISORGANIZATION when 1 then CONSTITUENT.KEYNAME else CONSTITUENT.KEYNAME+isnull(','+nullif(CONSTITUENT.FIRSTNAME,''),'') end + char(13) + ' / ' + CONSTITUENT.LOOKUPID as CONSTITUENT,
t1.DESIGNATIONNAME,
#tempPledges.PLEDGEDATE,
#tempPledges.POSTDATE,
#tempPledges.USERDEFINEDID,
V1.PLEDGEAMOUNT,
V1.WRITEOFFAMOUNT,
V1.PAYMENTAMOUNT,
V1.PLEDGEAMOUNT - V1.WRITEOFFAMOUNT - V1.PAYMENTAMOUNT as BALANCE,
V1.ROWTYPE,
case ROWTYPE when 7 then 1 else 0 end as SORT1,
case ROWTYPE when 0 then 0 else 1 end as SORT2,
#tempPledges.PLEDGEID,
V1.CONSTITUENTID,
V1.DESIGNATIONID
from
(select #tempPledges.PLEDGEID, #tempPledges.CONSTITUENTID, #tempPledges.DESIGNATIONID,
sum(#tempPledges.PLEDGEAMOUNT) as PLEDGEAMOUNT, isnull(sum(#tempPayments.PAYMENTAMOUNT),0.0) as PAYMENTAMOUNT, isnull(sum(#tempWriteOffs.WRITEOFFAMOUNT),0.0) as WRITEOFFAMOUNT,
grouping_id(#tempPledges.CONSTITUENTID, #tempPledges.DESIGNATIONID, #tempPledges.PLEDGEID) as RowType
from #tempPledges left join #tempPayments on #tempPledges.PLEDGEID = #tempPayments.PLEDGEID and #tempPledges.DESIGNATIONID = #tempPayments.DESIGNATIONID
left join #tempWriteOffs on #tempPledges.PLEDGEID = #tempWriteOffs.PLEDGEID and #tempPledges.DESIGNATIONID = #tempWriteOffs.DESIGNATIONID
where #tempPledges.PLEDGEAMOUNT - isnull(#tempPayments.PAYMENTAMOUNT,0.0) - isnull(#tempWriteOffs.WRITEOFFAMOUNT,0.0) > 0
and
( --constituent security
@ISADMIN = 1 or
@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, #tempPledges.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1
)
and exists
-- Site security filter
(
select HASPERMISSION
from dbo.UFN_SITEID_MAPFROM_REVENUEID(#tempPledges.PLEDGEID) REVSITES
cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, '65359276-0c0f-44fa-8b79-e1f2d1cce0c2', REVSITES.SITEID)
)
group by grouping sets
(
(constituentid, #tempPledges.DESIGNATIONID, #tempPledges.PLEDGEID),
(constituentid),
()
)
) V1
left join #tempPledges on V1.PledgeID = #tempPledges.PledgeID and V1.DesignationID = #tempPledges.DesignationID
left join dbo.CONSTITUENT on V1.CONSTITUENTID = CONSTITUENT.ID
left join @DesignationNames t1 on V1.DESIGNATIONID = t1.DESIGNATIONID) V2
order by SORT1, CONSTITUENT, SORT2, V2.DESIGNATIONNAME, POSTDATE, USERDEFINEDID
if @ALLDETAILS = 1 and @GROUPBY = 1
select CONSTITUENT,
'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + convert(nvarchar(36),CONSTITUENTID) CONSTITUENTLINK,
DESIGNATIONNAME,
case when @USERGRANTEDDESIGNATIONPAGE = 1 then 'http://www.blackbaud.com/DESIGNATIONID?DESIGNATIONID=' + convert(nvarchar(36),DESIGNATIONID) else convert(nvarchar(90),null) end DESIGNATIONLINK,
PLEDGEDATE,
POSTDATE,
USERDEFINEDID,
case when @USERGRANTEDTRANSACTIONPAGE = 1 then 'http://www.blackbaud.com/REVENUEID?REVENUEID=' + convert(nvarchar(36),PLEDGEID) else convert(nvarchar(81),null) end PLEDGELINK,
PLEDGEAMOUNT,
WRITEOFFAMOUNT,
PAYMENTAMOUNT,
@ISOCODE PLEDGEISOCURRENCYCODE,
@CURRENCYSYMBOL PLEDGECURRENCYSYMBOL,
@SYMBOLDISPLAYSETTINGCODE PLEDGECURRENCYSYMBOLDISPLAYSETTINGCODE,
@DECIMALDIGITS PLEDGEDECIMALDIGITS,
0 as HASNOTPOSTEDTRANSACTIONS,
ROWTYPE
from
(select case CONSTITUENT.ISORGANIZATION when 1 then CONSTITUENT.KEYNAME else CONSTITUENT.KEYNAME+isnull(','+nullif(CONSTITUENT.FIRSTNAME,''),'') end + char(13) + ' / ' + CONSTITUENT.LOOKUPID as CONSTITUENT,
t1.DESIGNATIONNAME,
#tempPledges.PLEDGEDATE,
#tempPledges.POSTDATE,
#tempPledges.USERDEFINEDID,
V1.PLEDGEAMOUNT,
V1.WRITEOFFAMOUNT,
V1.PAYMENTAMOUNT,
V1.PLEDGEAMOUNT - V1.WRITEOFFAMOUNT - V1.PAYMENTAMOUNT as BALANCE,
V1.ROWTYPE,
case ROWTYPE when 7 then 1 else 0 end as SORT1,
case ROWTYPE when 0 then 0 else 1 end as SORT2,
#tempPledges.PLEDGEID,
V1.CONSTITUENTID,
V1.DESIGNATIONID
from
(select #tempPledges.PLEDGEID, #tempPledges.CONSTITUENTID, #tempPledges.DESIGNATIONID,
sum(#tempPledges.PLEDGEAMOUNT) as PLEDGEAMOUNT, isnull(sum(#tempPayments.PAYMENTAMOUNT),0.0) as PAYMENTAMOUNT, isnull(sum(#tempWriteOffs.WRITEOFFAMOUNT),0.0) as WRITEOFFAMOUNT,
grouping_id(#tempPledges.DESIGNATIONID, #tempPledges.CONSTITUENTID, #tempPledges.PLEDGEID) as RowType
from #tempPledges left join #tempPayments on #tempPledges.PLEDGEID = #tempPayments.PLEDGEID and #tempPledges.DESIGNATIONID = #tempPayments.DESIGNATIONID
left join #tempWriteOffs on #tempPledges.PLEDGEID = #tempWriteOffs.PLEDGEID and #tempPledges.DESIGNATIONID = #tempWriteOffs.DESIGNATIONID
where #tempPledges.PLEDGEAMOUNT - isnull(#tempPayments.PAYMENTAMOUNT,0.0) - isnull(#tempWriteOffs.WRITEOFFAMOUNT,0.0) > 0
and
( --constituent security
@ISADMIN = 1 or
@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, #tempPledges.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1
)
and exists
-- Site security filter
(
select HASPERMISSION
from dbo.UFN_SITEID_MAPFROM_REVENUEID(#tempPledges.PLEDGEID) REVSITES
cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, '65359276-0c0f-44fa-8b79-e1f2d1cce0c2', REVSITES.SITEID)
)
group by grouping sets
(
(#tempPledges.DESIGNATIONID, constituentid, #tempPledges.PLEDGEID),
(#tempPledges.DESIGNATIONID),
()
)
) V1
left join #tempPledges on V1.PledgeID = #tempPledges.PledgeID and V1.DesignationID = #tempPledges.DesignationID
left join dbo.CONSTITUENT on V1.CONSTITUENTID = CONSTITUENT.ID
left join @DesignationNames t1 on V1.DESIGNATIONID = t1.DESIGNATIONID) V2
order by SORT1, V2.DESIGNATIONNAME, SORT2, CONSTITUENT, POSTDATE, USERDEFINEDID
if @ALLDETAILS = 0 and @GROUPBY = 0
select CONSTITUENT,
'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + convert(nvarchar(36),CONSTITUENTID) CONSTITUENTLINK,
DESIGNATIONNAME,
convert(nvarchar(90),null) as DESIGNATIONLINK,
PLEDGEDATE,
POSTDATE,
USERDEFINEDID,
convert(nvarchar(81),null) as PLEDGELINK,
PLEDGEAMOUNT,
WRITEOFFAMOUNT,
PAYMENTAMOUNT,
@ISOCODE PLEDGEISOCURRENCYCODE,
@CURRENCYSYMBOL PLEDGECURRENCYSYMBOL,
@SYMBOLDISPLAYSETTINGCODE PLEDGECURRENCYSYMBOLDISPLAYSETTINGCODE,
@DECIMALDIGITS PLEDGEDECIMALDIGITS,
0 as HASNOTPOSTEDTRANSACTIONS,
case ROWTYPE when 1 then 7 else 3 end AS ROWTYPE
from
(select case CONSTITUENT.ISORGANIZATION when 1 then CONSTITUENT.KEYNAME else CONSTITUENT.KEYNAME+isnull(','+nullif(CONSTITUENT.FIRSTNAME,''),'') end + char(13) + ' / ' + CONSTITUENT.LOOKUPID as CONSTITUENT,
convert(nvarchar(512),null) as DESIGNATIONNAME,
convert(date,null) as PLEDGEDATE,
convert(date,null) as POSTDATE,
convert(nvarchar(100),null) as USERDEFINEDID,
V1.PLEDGEAMOUNT,
V1.WRITEOFFAMOUNT,
V1.PAYMENTAMOUNT,
V1.PLEDGEAMOUNT - V1.WRITEOFFAMOUNT - V1.PAYMENTAMOUNT as BALANCE,
V1.ROWTYPE,
V1.CONSTITUENTID
from
(select #tempPledges.CONSTITUENTID,
sum(#tempPledges.PLEDGEAMOUNT) as PLEDGEAMOUNT, isnull(sum(#tempPayments.PAYMENTAMOUNT),0.0) as PAYMENTAMOUNT, isnull(sum(#tempWriteOffs.WRITEOFFAMOUNT),0.0) as WRITEOFFAMOUNT,
grouping_id(#tempPledges.CONSTITUENTID) as RowType
from #tempPledges left join #tempPayments on #tempPledges.PLEDGEID = #tempPayments.PLEDGEID and #tempPledges.DESIGNATIONID = #tempPayments.DESIGNATIONID
left join #tempWriteOffs on #tempPledges.PLEDGEID = #tempWriteOffs.PLEDGEID and #tempPledges.DESIGNATIONID = #tempWriteOffs.DESIGNATIONID
where #tempPledges.PLEDGEAMOUNT - isnull(#tempPayments.PAYMENTAMOUNT,0.0) - isnull(#tempWriteOffs.WRITEOFFAMOUNT,0.0) > 0
and
( --constituent security
@ISADMIN = 1 or
@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, #tempPledges.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1
)
and exists
-- Site security filter
(
select HASPERMISSION
from dbo.UFN_SITEID_MAPFROM_REVENUEID(#tempPledges.PLEDGEID) REVSITES
cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, '65359276-0c0f-44fa-8b79-e1f2d1cce0c2', REVSITES.SITEID)
)
group by grouping sets
(
(constituentid),
()
)
) V1
left join dbo.CONSTITUENT on V1.CONSTITUENTID = CONSTITUENT.ID) V2
order by ROWTYPE, CONSTITUENT
if @ALLDETAILS = 0 and @GROUPBY = 1
select CONSTITUENT,
convert(nvarchar(90),null) as CONSTITUENTLINK,
DESIGNATIONNAME,
case when @USERGRANTEDDESIGNATIONPAGE = 1 then 'http://www.blackbaud.com/DESIGNATIONID?DESIGNATIONID=' + convert(nvarchar(36),DESIGNATIONID) else null end DESIGNATIONLINK,
PLEDGEDATE,
POSTDATE,
USERDEFINEDID,
convert(nvarchar(81),null) as PLEDGELINK,
PLEDGEAMOUNT,
WRITEOFFAMOUNT,
PAYMENTAMOUNT,
@ISOCODE PLEDGEISOCURRENCYCODE,
@CURRENCYSYMBOL PLEDGECURRENCYSYMBOL,
@SYMBOLDISPLAYSETTINGCODE PLEDGECURRENCYSYMBOLDISPLAYSETTINGCODE,
@DECIMALDIGITS PLEDGEDECIMALDIGITS,
0 as HASNOTPOSTEDTRANSACTIONS,
case ROWTYPE when 1 then 7 else 3 end as ROWTYPE
from
(select convert(nvarchar(255),null) as CONSTITUENT,
t1.DESIGNATIONNAME,
convert(date,null) as PLEDGEDATE,
convert(date,null) as POSTDATE,
convert(nvarchar(100),null) as USERDEFINEDID,
V1.PLEDGEAMOUNT,
V1.WRITEOFFAMOUNT,
V1.PAYMENTAMOUNT,
V1.PLEDGEAMOUNT - V1.WRITEOFFAMOUNT - V1.PAYMENTAMOUNT as BALANCE,
V1.ROWTYPE,
V1.DESIGNATIONID
from
(select #tempPledges.DESIGNATIONID,
sum(#tempPledges.PLEDGEAMOUNT) as PLEDGEAMOUNT, isnull(sum(#tempPayments.PAYMENTAMOUNT),0.0) as PAYMENTAMOUNT, isnull(sum(#tempWriteOffs.WRITEOFFAMOUNT),0.0) as WRITEOFFAMOUNT,
grouping_id(#tempPledges.DESIGNATIONID) as RowType
from #tempPledges left join #tempPayments on #tempPledges.PLEDGEID = #tempPayments.PLEDGEID and #tempPledges.DESIGNATIONID = #tempPayments.DESIGNATIONID
left join #tempWriteOffs on #tempPledges.PLEDGEID = #tempWriteOffs.PLEDGEID and #tempPledges.DESIGNATIONID = #tempWriteOffs.DESIGNATIONID
where #tempPledges.PLEDGEAMOUNT - isnull(#tempPayments.PAYMENTAMOUNT,0.0) - isnull(#tempWriteOffs.WRITEOFFAMOUNT,0.0) > 0
and
( --constituent security
@ISADMIN = 1 or
@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, #tempPledges.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1
)
and exists
-- Site security filter
(
select HASPERMISSION
from dbo.UFN_SITEID_MAPFROM_REVENUEID(#tempPledges.PLEDGEID) REVSITES
cross apply dbo.UFN_REPORT_HASUSERSITEPERMISSION(@CURRENTAPPUSERID, '65359276-0c0f-44fa-8b79-e1f2d1cce0c2', REVSITES.SITEID)
)
group by grouping sets
(
(#tempPledges.DESIGNATIONID),
()
)
) V1
left join @DesignationNames t1 on V1.DESIGNATIONID = t1.DESIGNATIONID) V2
order by V2.ROWTYPE, V2.DESIGNATIONNAME
drop table #tempPledges
drop table #tempPayments
drop table #tempWriteOffs
drop table #tempAdjustmentsAsOf