USP_DATALIST_PROSPECTPLAN_PLANNEDGIFTS
List of a prospect plan's planned gists.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @PROSPECTPLANID | 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. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_PROSPECTPLAN_PLANNEDGIFTS
(
@PROSPECTPLANID uniqueidentifier,
@CURRENTAPPUSERID uniqueidentifier = null,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
)
as begin
set nocount on;
declare @PLANNEDGIFTINFO table(
ID uniqueidentifier,
VEHICLE nvarchar(100),
DATE date,
AMOUNT money,
ISANONYMOUS bit,
ISREVOCABLE bit,
EXPECTEDMATURITY smallint,
STATUS nvarchar(100),
SITES nvarchar(400),
DISPLAYCURRENCY uniqueidentifier,
CONSTITUENTNAME nvarchar(400),
BALANCE money,
PAYMENTS money,
PARENTID uniqueidentifier,
VIEWFORMID uniqueidentifier
)
insert into @PLANNEDGIFTINFO
select
PG.ID,
PG.VEHICLE,
PG.GIFTDATE DATE,
dbo.UFN_PLANNEDGIFTREVENUE_GETTOTALAMOUNT(PG.ID, 2, PG.TRANSACTIONCURRENCYID) as AMOUNT,
PG.ISANONYMOUS,
PG.ISREVOCABLE,
PG.EXPECTEDMATURITY,
PG.STATUS,
dbo.UFN_PLANNEDGIFT_GETSITELIST(PG.ID) SITES,
PG.TRANSACTIONCURRENCYID DISPLAYCURRENCY,
NF.NAME CONSTITUENTNAME,
dbo.UFN_PLANNEDGIFT_GETBALANCE(PG.ID, PG.TRANSACTIONCURRENCYID, 2) BALANCE,
dbo.UFN_PLANNEDGIFT_GETPAYMENTS(PG.ID, PG.TRANSACTIONCURRENCYID, 2) PAYMENTS,
null PARENTID,
'C1FBFDA5-2EF8-4211-8B9A-B1E30689D08C' VIEWFORMID
from
dbo.PLANNEDGIFT PG
cross apply
dbo.UFN_CONSTITUENT_DISPLAYNAME(PG.CONSTITUENTID) NF
where
PG.PROSPECTPLANID=@PROSPECTPLANID 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
)
)
union all
--get all planned gift additions
select
PGA.ID,
PG.VEHICLE,
PGA.GIFTDATE DATE,
dbo.UFN_PLANNEDGIFTADDITIONREVENUE_GETTOTALAMOUNT(PGA.ID, 2, PGA.TRANSACTIONCURRENCYID) as AMOUNT,
PG.ISANONYMOUS,
PG.ISREVOCABLE,
PG.EXPECTEDMATURITY,
PG.STATUS,
dbo.UFN_PLANNEDGIFT_GETSITELIST(PG.ID) SITES,
PG.TRANSACTIONCURRENCYID DISPLAYCURRENCY,
NF.NAME CONSTITUENTNAME,
dbo.UFN_PLANNEDGIFTADDITION_GETBALANCE(PGA.ID, PGA.TRANSACTIONCURRENCYID, 2) BALANCE,
dbo.UFN_PLANNEDGIFTADDITION_GETPAYMENTS(PGA.ID, PGA.TRANSACTIONCURRENCYID, 2) PAYMENTS,
PG.ID PARENTID,
'd8e2f2b5-4c4f-46de-a2f7-39c595dd2892' VIEWFORMID
from
dbo.PLANNEDGIFT PG
cross apply
dbo.UFN_CONSTITUENT_DISPLAYNAME(PG.CONSTITUENTID) NF
inner join
dbo.PLANNEDGIFTADDITION PGA on PG.ID = PGA.PLANNEDGIFTID
where
PG.PROSPECTPLANID=@PROSPECTPLANID 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
)
)
--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.EXPECTEDMATURITY,
PGINFO.STATUS,
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
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,
EXPECTEDMATURITY,
STATUS,
SITES,
DISPLAYCURRENCY,
'Original gift' CONSTITUENTNAME,
BALANCE,
PAYMENTS,
ID PARENTID,
null IDFORTREE,
VIEWFORMID,
0 ISADDITION
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,
EXPECTEDMATURITY,
STATUS,
SITES,
DISPLAYCURRENCY,
'Addition' CONSTITUENTNAME,
BALANCE,
PAYMENTS,
PARENTID,
null IDFORTREE,
VIEWFORMID,
1 ISADDITION
from @PLANNEDGIFTINFO PGINFO
where
PARENTID is not null
order by
CONSTITUENTNAME desc, DATE desc
end