USP_DATALIST_PROSPECT_PLANNEDGIFTS
List of a prospect's planned gists.
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_PLANNEDGIFTS
(
@PROSPECTID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier = null,
@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;
declare @CURRENTDATE as date;
set @CURRENTDATE = getdate();
-------------------------------------
--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.NAME NAME,
PG.ID PLANNEDGIFTID
from
dbo.PLANNEDGIFT PG
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PG.CONSTITUENTID) NF
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 uniqueidentifier,
VEHICLE nvarchar(100),
DATE date,
AMOUNT money,
ISANONYMOUS bit,
ISREVOCABLE bit,
STATUS nvarchar(100),
EXPECTEDMATURITY smallint,
SITES nvarchar(400),
DISPLAYCURRENCY uniqueidentifier,
CONSTITUENTNAME nvarchar(400),
BALANCE money,
PAYMENTS money,
PARENTID uniqueidentifier,
VIEWFORMID uniqueidentifier,
CAMPAIGNS nvarchar(max)
)
insert into @PLANNEDGIFTINFO
select
PG.ID,
PG.VEHICLE,
PG.GIFTDATE DATE,
dbo.UFN_PLANNEDGIFTREVENUE_GETTOTALAMOUNT(PG.ID, @CURRENCYCODE, @CURRENCYID) as AMOUNT,
PG.ISANONYMOUS,
PG.ISREVOCABLE,
PG.STATUS,
PG.EXPECTEDMATURITY,
dbo.UFN_PLANNEDGIFT_GETSITELIST(PG.ID) SITES,
dbo.UFN_PLANNEDGIFTREVENUE_GETCURRENCYIDFROMCODE(PG.ID, @CURRENCYCODE, @CURRENCYID) as DISPLAYCURRENCY,
PROSPECT.NAME CONSTITUENTNAME,
dbo.UFN_PLANNEDGIFT_GETBALANCE(PG.ID, @CURRENCYID, @CURRENCYCODE) BALANCE,
dbo.UFN_PLANNEDGIFT_GETPAYMENTS(PG.ID, @CURRENCYID, @CURRENCYCODE) PAYMENTS,
null PARENTID,
'C1FBFDA5-2EF8-4211-8B9A-B1E30689D08C' 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
where
(
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
--Grab all household member planned gifts if this prospect is a household
select
PG.ID,
PG.VEHICLE,
PG.GIFTDATE DATE,
dbo.UFN_PLANNEDGIFTREVENUE_GETTOTALAMOUNT(PG.ID, @CURRENCYCODE, @CURRENCYID) as AMOUNT,
PG.ISANONYMOUS,
PG.ISREVOCABLE,
PG.STATUS,
PG.EXPECTEDMATURITY,
dbo.UFN_PLANNEDGIFT_GETSITELIST(PG.ID) SITES,
dbo.UFN_PLANNEDGIFTREVENUE_GETCURRENCYIDFROMCODE(PG.ID, @CURRENCYCODE, @CURRENCYID) as DISPLAYCURRENCY,
NF.NAME CONSTITUENTNAME,
dbo.UFN_PLANNEDGIFT_GETBALANCE(PG.ID, @CURRENCYID, @CURRENCYCODE) BALANCE,
dbo.UFN_PLANNEDGIFT_GETPAYMENTS(PG.ID, @CURRENCYID, @CURRENCYCODE) PAYMENTS,
null PARENTID,
'C1FBFDA5-2EF8-4211-8B9A-B1E30689D08C' 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.GROUPMEMBER GM
inner join
dbo.PLANNEDGIFT PG on PG.CONSTITUENTID = GM.MEMBERID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PG.CONSTITUENTID) NF
left outer join
dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
where
GM.GROUPID = @PROSPECTID
and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= @CURRENTDATE))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE))
or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO >= @CURRENTDATE))
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
--get all planned gift additions
select
PGA.ID,
PG.VEHICLE,
PGA.GIFTDATE DATE,
dbo.UFN_PLANNEDGIFTADDITIONREVENUE_GETTOTALAMOUNT(PGA.ID, @CURRENCYCODE, @CURRENCYID) as AMOUNT,
PG.ISANONYMOUS,
PG.ISREVOCABLE,
PG.STATUS,
PG.EXPECTEDMATURITY,
dbo.UFN_PLANNEDGIFT_GETSITELIST(PG.ID) SITES,
dbo.UFN_PLANNEDGIFTADDITIONREVENUE_GETCURRENCYIDFROMCODE(PGA.ID, @CURRENCYCODE, @CURRENCYID) as DISPLAYCURRENCY,
PROSPECT.NAME CONSTITUENTNAME,
dbo.UFN_PLANNEDGIFTADDITION_GETBALANCE(PGA.ID, @CURRENCYID, @CURRENCYCODE) BALANCE,
dbo.UFN_PLANNEDGIFTADDITION_GETPAYMENTS(PGA.ID, @CURRENCYID, @CURRENCYCODE) PAYMENTS,
PG.ID PARENTID,
'd8e2f2b5-4c4f-46de-a2f7-39c595dd2892' 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 PG.ID = PGA.PLANNEDGIFTID
where
(
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
)
)
union all
--Grab all household member planned gifts if this prospect is a household
select
PGA.ID,
PG.VEHICLE,
PGA.GIFTDATE DATE,
dbo.UFN_PLANNEDGIFTADDITIONREVENUE_GETTOTALAMOUNT(PGA.ID, @CURRENCYCODE, @CURRENCYID) as AMOUNT,
PG.ISANONYMOUS,
PG.ISREVOCABLE,
PG.STATUS,
PG.EXPECTEDMATURITY,
dbo.UFN_PLANNEDGIFT_GETSITELIST(PG.ID) SITES,
dbo.UFN_PLANNEDGIFTADDITIONREVENUE_GETCURRENCYIDFROMCODE(PGA.ID, @CURRENCYCODE, @CURRENCYID) as DISPLAYCURRENCY,
NF.NAME CONSTITUENTNAME,
dbo.UFN_PLANNEDGIFTADDITION_GETBALANCE(PGA.ID, @CURRENCYID, @CURRENCYCODE) BALANCE,
dbo.UFN_PLANNEDGIFTADDITION_GETPAYMENTS(PGA.ID, @CURRENCYID, @CURRENCYCODE) PAYMENTS,
PG.ID PARENTID,
'd8e2f2b5-4c4f-46de-a2f7-39c595dd2892' 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.GROUPMEMBER GM
inner join
dbo.PLANNEDGIFT PG on PG.CONSTITUENTID = GM.MEMBERID
inner join
dbo.PLANNEDGIFTADDITION PGA on PG.ID = PGA.PLANNEDGIFTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PG.CONSTITUENTID) NF
left outer join
dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
where
GM.GROUPID = @PROSPECTID
and ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO >= @CURRENTDATE))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE))
or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO >= @CURRENTDATE))
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,
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 DISPLAYCURRENCY from @PLANNEDGIFTINFO PGADDITIONINFO where PGINFO.ID = PGADDITIONINFO.PARENTID),PGINFO.DISPLAYCURRENCY) = PGINFO.DISPLAYCURRENCY 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.STATUS,
PGINFO.EXPECTEDMATURITY,
PGINFO.SITES,
PGINFO.DISPLAYCURRENCY,
PGINFO.CONSTITUENTNAME,
case when coalesce(PGCHILDNODEINFO.CURRENCYCOUNT, 1) = 1 and coalesce((select top 1 DISPLAYCURRENCY from @PLANNEDGIFTINFO PGADDITIONINFO where PGINFO.ID = PGADDITIONINFO.PARENTID),PGINFO.DISPLAYCURRENCY) = PGINFO.DISPLAYCURRENCY 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 DISPLAYCURRENCY from @PLANNEDGIFTINFO PGADDITIONINFO where PGINFO.ID = PGADDITIONINFO.PARENTID),PGINFO.DISPLAYCURRENCY) = PGINFO.DISPLAYCURRENCY 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.DISPLAYCURRENCY) 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,
VEHICLE,
DATE,
AMOUNT,
ISANONYMOUS,
ISREVOCABLE,
STATUS,
EXPECTEDMATURITY,
SITES,
DISPLAYCURRENCY,
'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,
VEHICLE,
DATE,
AMOUNT,
ISANONYMOUS,
ISREVOCABLE,
STATUS,
EXPECTEDMATURITY,
SITES,
DISPLAYCURRENCY,
'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