USP_DATALIST_PROSPECT_PLANNEDGIFTSACCEPTED
List of a prospect's accepted planned gifts.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @PROSPECTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @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 |
| @CAMPAIGNFILTERMODE | tinyint | IN | Campaigns |
| @CAMPAIGNSSELECTED | xml | IN | |
| @INCLUDERELATED | tinyint | IN | Include planned gifts from relationships |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_PROSPECT_PLANNEDGIFTSACCEPTED
(
@PROSPECTID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@CURRENCYCODE tinyint = 2,
@CAMPAIGNFILTERMODE tinyint = 0,
@CAMPAIGNSSELECTED xml = null,
@INCLUDERELATED tinyint = 1
)
as begin
set nocount on;
-------------------------------------
--CAMPAIGNSSELECTED
-------------------------------------
declare @CAMPAIGNFILTERTABLE table (ID uniqueidentifier)
if @CAMPAIGNFILTERMODE != 0
begin
insert into @CAMPAIGNFILTERTABLE
select T.c.value('(ID)[1]','uniqueidentifier')
from @CAMPAIGNSSELECTED.nodes('/CAMPAIGNSSELECTED/ITEM') T(c);
end
-------------------------------------
--MULTICURRENCY
-------------------------------------
declare @CURRENCYID uniqueidentifier;
declare @MULTICURRENCYENABLED bit;
set @MULTICURRENCYENABLED = dbo.UFN_CONDITIONSETTING_EVALUATEEXISTSCONDITION('Multicurrency');
if @MULTICURRENCYENABLED = 0
set @CURRENCYCODE = 1;
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()
begin
set @CURRENCYCODE = 1
end
end;
-------------------------------------
--GET PROSPECTIDS
-------------------------------------
declare @PROSPECTIDS table
(
ID uniqueidentifier,
NAME nvarchar(300),
PLANNEDGIFTID uniqueidentifier
);
insert into @PROSPECTIDS
select distinct
PG.CONSTITUENTID ID,
NF_PROSPECT.NAME NAME,
PG.ID PLANNEDGIFTID
from
dbo.PLANNEDGIFT PG
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PG.CONSTITUENTID) NF_PROSPECT
where
PG.CONSTITUENTID = @PROSPECTID
union all
select distinct
RELATEDCONSTITUENT.ID ID,
NF.NAME NAME,
PG.ID PLANNEDGIFTID
from
dbo.PLANNEDGIFT PG
left join
dbo.PLANNEDGIFTRELATIONSHIP on PG.ID = PLANNEDGIFTRELATIONSHIP.PLANNEDGIFTID
left join
dbo.RELATIONSHIP on PLANNEDGIFTRELATIONSHIP.RELATIONSHIPID = RELATIONSHIP.ID
left join
dbo.CONSTITUENT RELATEDCONSTITUENT on
(RELATEDCONSTITUENT.ID = RELATIONSHIP.RECIPROCALCONSTITUENTID and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @PROSPECTID) or
(RELATEDCONSTITUENT.ID = RELATIONSHIP.RELATIONSHIPCONSTITUENTID and RELATIONSHIP.RECIPROCALCONSTITUENTID = @PROSPECTID)
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(RELATEDCONSTITUENT.ID) NF
where @INCLUDERELATED = 1
-------------------------------------
--MAIN WORK
-------------------------------------
declare @PLANNEDGIFTINFO table(
ID nvarchar(80),
REVENUEID uniqueidentifier,
VEHICLE nvarchar(100),
DATE date,
AMOUNT money,
ISANONYMOUS bit,
ISREVOCABLE bit,
EXPECTEDMATURITY smallint,
STATUS nvarchar(100),
SITES nvarchar(400),
DISPLAYCURRENCYID uniqueidentifier,
CONSTITUENTNAME nvarchar(400),
BALANCE money,
PAYMENTS money,
PARENTID nvarchar(80),
VIEWFORMID uniqueidentifier,
CAMPAIGNS nvarchar(max)
)
insert into @PLANNEDGIFTINFO
select
cast(PG.ID as nvarchar(36)) + '|' + cast(@CURRENCYCODE as nvarchar(1)) + '|' + cast(@CURRENTAPPUSERID as nvarchar(36)) as ID,
PGR.REVENUEID,
PG.VEHICLE,
PG.GIFTDATE DATE,
dbo.UFN_PLANNEDGIFTREVENUE_GETTOTALAMOUNT(PG.ID, @CURRENCYCODE, @CURRENCYID) as AMOUNT,
PG.ISANONYMOUS,
PG.ISREVOCABLE,
PG.EXPECTEDMATURITY,
PG.STATUS,
dbo.UFN_PLANNEDGIFT_GETSITELIST(PG.ID) SITES,
dbo.UFN_PLANNEDGIFTREVENUE_GETCURRENCYIDFROMCODE(PG.ID, @CURRENCYCODE, @CURRENCYID) as DISPLAYCURRENCYID,
PROSPECT.NAME CONSTITUENTNAME,
dbo.UFN_PLANNEDGIFT_GETBALANCE(PG.ID, @CURRENCYID, @CURRENCYCODE) BALANCE,
dbo.UFN_PLANNEDGIFT_GETPAYMENTS(PG.ID, @CURRENCYID, @CURRENCYCODE) PAYMENTS,
null PARENTID,
'bcf0a5df-a874-4107-80c1-9cc2b3f27f0f' VIEWFORMID,
(
select dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME)
from dbo.PLANNEDGIFTDESIGNATION
inner join dbo.PLANNEDGIFTDESIGNATIONCAMPAIGN on PLANNEDGIFTDESIGNATION.ID = PLANNEDGIFTDESIGNATIONCAMPAIGN.PLANNEDGIFTDESIGNATIONID
inner join dbo.CAMPAIGN on CAMPAIGN.ID = PLANNEDGIFTDESIGNATIONCAMPAIGN.CAMPAIGNID
where PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = PG.ID
) CAMPAIGNS
from dbo.PLANNEDGIFT PG
inner join @PROSPECTIDS PROSPECT on PG.CONSTITUENTID = PROSPECT.ID and PROSPECT.PLANNEDGIFTID = PG.ID
left outer join dbo.PLANNEDGIFTREVENUE PGR on PGR.ID = PG.ID
where
STATUSCODE in (2,5) --accepted or matured
and
(
select count(*)
from dbo.UFN_SITEID_MAPFROM_PLANNEDGIFTID(PG.ID) as PLANNEDGIFTSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PLANNEDGIFTSITE].[SITEID] or (SITEID is null and [PLANNEDGIFTSITE].[SITEID] is null)))
) > 0
and
(
@SITEFILTERMODE = 0
or
PG.ID in
(
select PLANNEDGIFTSITE.PLANNEDGIFTID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.PLANNEDGIFTSITE on PLANNEDGIFTSITE.SITEID = SITEFILTER.SITEID
)
)
and
(
@CAMPAIGNFILTERMODE = 0
or
exists
(
select 1
from dbo.PLANNEDGIFTDESIGNATION
inner join dbo.PLANNEDGIFTDESIGNATIONCAMPAIGN on PLANNEDGIFTDESIGNATION.ID = PLANNEDGIFTDESIGNATIONCAMPAIGN.PLANNEDGIFTDESIGNATIONID
inner join @CAMPAIGNFILTERTABLE CAMPAIGNFILTER on PLANNEDGIFTDESIGNATIONCAMPAIGN.CAMPAIGNID = CAMPAIGNFILTER.ID
where PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = PG.ID
)
)
union all
select
cast(PGA.ID as nvarchar(36)) + '|' + cast(@CURRENCYCODE as nvarchar(1)) + '|' + cast(@CURRENTAPPUSERID as nvarchar(36)) as ID,
PGAR.REVENUEID,
PG.VEHICLE,
PGA.GIFTDATE DATE,
dbo.UFN_PLANNEDGIFTADDITIONREVENUE_GETTOTALAMOUNT(PGA.ID, @CURRENCYCODE, @CURRENCYID) as AMOUNT,
PG.ISANONYMOUS,
PG.ISREVOCABLE,
PG.EXPECTEDMATURITY,
PG.STATUS,
dbo.UFN_PLANNEDGIFT_GETSITELIST(PG.ID) SITES,
dbo.UFN_PLANNEDGIFTADDITIONREVENUE_GETCURRENCYIDFROMCODE(PGA.ID, @CURRENCYCODE, @CURRENCYID) as DISPLAYCURRENCYID,
PROSPECT.NAME CONSTITUENTNAME,
dbo.UFN_PLANNEDGIFTADDITION_GETBALANCE(PGA.ID, @CURRENCYID, @CURRENCYCODE) BALANCE,
dbo.UFN_PLANNEDGIFTADDITION_GETPAYMENTS(PGA.ID, @CURRENCYID, @CURRENCYCODE) PAYMENTS,
cast(PG.ID as nvarchar(36)) + '|' + cast(@CURRENCYCODE as nvarchar(1)) + '|' + cast(@CURRENTAPPUSERID as nvarchar(36)) PARENTID,
'9b8221c9-910e-40dd-b34a-202b54d71e2c' VIEWFORMID,
(
select dbo.UDA_BUILDLIST(distinct CAMPAIGN.NAME)
from dbo.PLANNEDGIFTADDITIONDESIGNATION
inner join dbo.PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN on PLANNEDGIFTADDITIONDESIGNATION.ID = PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN.PLANNEDGIFTADDITIONDESIGNATIONID
inner join dbo.CAMPAIGN on CAMPAIGN.ID = PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN.CAMPAIGNID
where PLANNEDGIFTADDITIONDESIGNATION.PLANNEDGIFTADDITIONID = PGA.ID
) CAMPAIGNS
from dbo.PLANNEDGIFT PG
inner join @PROSPECTIDS PROSPECT on PG.CONSTITUENTID = PROSPECT.ID and PROSPECT.PLANNEDGIFTID = PG.ID
inner join dbo.PLANNEDGIFTADDITION PGA on PGA.PLANNEDGIFTID = PG.ID
left outer join dbo.PLANNEDGIFTADDITIONREVENUE PGAR on PGAR.ID = PGA.ID
where
STATUSCODE in (2,5) --accepted or matured
and
(
select count(*)
from dbo.UFN_SITEID_MAPFROM_PLANNEDGIFTID(PG.ID) as PLANNEDGIFTSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PLANNEDGIFTSITE].[SITEID] or (SITEID is null and [PLANNEDGIFTSITE].[SITEID] is null)))
) > 0
and
(
@SITEFILTERMODE = 0
or
PG.ID in
(
select PLANNEDGIFTSITE.PLANNEDGIFTID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.PLANNEDGIFTSITE on PLANNEDGIFTSITE.SITEID = SITEFILTER.SITEID
)
)
and
(
@CAMPAIGNFILTERMODE = 0
or
exists
(
select 1
from dbo.PLANNEDGIFTADDITIONDESIGNATION
inner join dbo.PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN on PLANNEDGIFTADDITIONDESIGNATION.ID = PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN.PLANNEDGIFTADDITIONDESIGNATIONID
inner join @CAMPAIGNFILTERTABLE CAMPAIGNFILTER on PLANNEDGIFTADDITIONDESIGNATIONCAMPAIGN.CAMPAIGNID = CAMPAIGNFILTER.ID
where PLANNEDGIFTADDITIONDESIGNATION.PLANNEDGIFTADDITIONID = PGA.ID
)
);
--TOP ROLLUP LEVEL
select
PGINFO.ID,
REVENUEID,
PGINFO.VEHICLE,
case when PGCHILDNODEINFO.PARENTID is null then PGINFO.DATE else null end DATE,
case when coalesce(PGCHILDNODEINFO.CURRENCYCOUNT, 1) = 1 and coalesce((select top 1 DISPLAYCURRENCYID from @PLANNEDGIFTINFO PGADDITIONINFO where PGINFO.ID = PGADDITIONINFO.PARENTID),PGINFO.DISPLAYCURRENCYID) = PGINFO.DISPLAYCURRENCYID then
case
when PGINFO.AMOUNT is not null and PGCHILDNODEINFO.TOTALAMOUNT is not null then
PGINFO.AMOUNT + PGCHILDNODEINFO.TOTALAMOUNT
when PGINFO.AMOUNT is not null and PGCHILDNODEINFO.TOTALAMOUNT is null then
PGINFO.AMOUNT
when PGINFO.AMOUNT is null and PGCHILDNODEINFO.TOTALAMOUNT is not null then
PGCHILDNODEINFO.TOTALAMOUNT
else
null
end
end AMOUNT,
PGINFO.ISANONYMOUS,
PGINFO.ISREVOCABLE,
PGINFO.EXPECTEDMATURITY,
PGINFO.STATUS,
PGINFO.SITES,
PGINFO.DISPLAYCURRENCYID,
PGINFO.CONSTITUENTNAME,
case when coalesce(PGCHILDNODEINFO.CURRENCYCOUNT, 1) = 1 and coalesce((select top 1 DISPLAYCURRENCYID from @PLANNEDGIFTINFO PGADDITIONINFO where PGINFO.ID = PGADDITIONINFO.PARENTID),PGINFO.DISPLAYCURRENCYID) = PGINFO.DISPLAYCURRENCYID then
case
when PGINFO.BALANCE is not null and PGCHILDNODEINFO.TOTALBALANCE is not null then
PGINFO.BALANCE + PGCHILDNODEINFO.TOTALBALANCE
when PGINFO.BALANCE is not null and PGCHILDNODEINFO.TOTALBALANCE is null then
PGINFO.BALANCE
when PGINFO.BALANCE is null and PGCHILDNODEINFO.TOTALBALANCE is not null then
PGCHILDNODEINFO.TOTALBALANCE
else
null
end
end BALANCE,
case when coalesce(PGCHILDNODEINFO.CURRENCYCOUNT, 1) = 1 and coalesce((select top 1 DISPLAYCURRENCYID from @PLANNEDGIFTINFO PGADDITIONINFO where PGINFO.ID = PGADDITIONINFO.PARENTID),PGINFO.DISPLAYCURRENCYID) = PGINFO.DISPLAYCURRENCYID then
case
when PGINFO.PAYMENTS is not null and PGCHILDNODEINFO.TOTALPAYMENTS is not null then
PGINFO.PAYMENTS + PGCHILDNODEINFO.TOTALPAYMENTS
when PGINFO.PAYMENTS is not null and PGCHILDNODEINFO.TOTALPAYMENTS is null then
PGINFO.PAYMENTS
when PGINFO.PAYMENTS is null and PGCHILDNODEINFO.TOTALPAYMENTS is not null then
PGCHILDNODEINFO.TOTALPAYMENTS
else
null
end
end PAYMENTS,
null PARENTID,
PGINFO.ID IDFORTREE,
PGINFO.VIEWFORMID,
0 ISADDITION,
case when PGINFO.ISANONYMOUS = 1 then 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.gift_anonymous_16.png' else '' end IMAGEKEY,
PGINFO.CAMPAIGNS
from @PLANNEDGIFTINFO PGINFO
left join (
select
PGADDITIONINFO.PARENTID,
count(distinct PGADDITIONINFO.DISPLAYCURRENCYID) CURRENCYCOUNT,
sum(PGADDITIONINFO.AMOUNT) TOTALAMOUNT,
sum(PGADDITIONINFO.BALANCE) TOTALBALANCE,
sum(PGADDITIONINFO.PAYMENTS) TOTALPAYMENTS
from @PLANNEDGIFTINFO PGADDITIONINFO
group by PGADDITIONINFO.PARENTID
) PGCHILDNODEINFO on PGINFO.ID = PGCHILDNODEINFO.PARENTID
where
PGINFO.PARENTID is null
union all
--PLANNED GIFTS
select
ID,
REVENUEID,
VEHICLE,
DATE,
AMOUNT,
ISANONYMOUS,
ISREVOCABLE,
EXPECTEDMATURITY,
STATUS,
SITES,
DISPLAYCURRENCYID,
'Original gift' CONSTITUENTNAME,
BALANCE,
PAYMENTS,
ID PARENTID,
null IDFORTREE,
VIEWFORMID,
0 ISADDITION,
case when PGINFO.ISANONYMOUS = 1 then 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.gift_anonymous_16.png' else '' end IMAGEKEY,
PGINFO.CAMPAIGNS
from @PLANNEDGIFTINFO PGINFO
where
PARENTID is null and
ID in (select PGINFOSUB.PARENTID from @PLANNEDGIFTINFO as PGINFOSUB)
union all
--PLANNED GIFT ADDITIONS
select
ID,
REVENUEID,
VEHICLE,
DATE,
AMOUNT,
ISANONYMOUS,
ISREVOCABLE,
EXPECTEDMATURITY,
STATUS,
SITES,
DISPLAYCURRENCYID,
'Addition' CONSTITUENTNAME,
BALANCE,
PAYMENTS,
PARENTID,
null IDFORTREE,
VIEWFORMID,
1 ISADDITION,
case when PGINFO.ISANONYMOUS = 1 then 'catalog:Blackbaud.AppFx.Fundraising.Catalog.dll,Blackbaud.AppFx.Fundraising.Catalog.gift_anonymous_16.png' else '' end IMAGEKEY,
PGINFO.CAMPAIGNS
from @PLANNEDGIFTINFO PGINFO
where
PARENTID is not null
order by
CONSTITUENTNAME desc, DATE desc
end