USP_DATALIST_PLANNEDGIFTDETAIL
Fetches planned gift detail information.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SELECTIONID | uniqueidentifier | IN | Selection |
| @FROMDATE | datetime | IN | From |
| @TODATE | datetime | IN | To |
| @VEHICLECODE | tinyint | IN | Vehicle |
| @STATUSCODE | tinyint | IN | Status |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_PLANNEDGIFTDETAIL
(
@SELECTIONID uniqueidentifier,
@FROMDATE datetime = null,
@TODATE datetime = null,
@VEHICLECODE tinyint = null,
@STATUSCODE tinyint = null,
@CURRENTAPPUSERID uniqueidentifier = null
)
as begin
set nocount on;
declare @ISADMIN bit;
declare @APPUSER_IN_NONRACROLE bit;
declare @APPUSER_IN_NOSECGROUPROLE bit;
declare @APPUSER_IN_NONSITEROLE bit;
declare @APPUSER_IN_NOSITEROLE 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);
set @APPUSER_IN_NONSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NONSITEROLE(@CURRENTAPPUSERID);
set @APPUSER_IN_NOSITEROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SITE_ROLE(@CURRENTAPPUSERID);
-- Temp table to hold selection
declare @TMP1 table
(
PLANNEDGIFTID uniqueidentifier,
CONSTITUENTNAME nvarchar(128),
GIFTDATE datetime,
GIFTVEHICLE nvarchar(64),
DESIGNATIONNAME nvarchar(256),
DESIGNATIONVANITYNAME nvarchar(256),
GIFTAMOUNT money,
REMAINDERVALUE decimal(30,10),
TOTALREMAINDERVALUE money,
EXPECTEDMATURITYYEAR nvarchar(4),
STATUS nvarchar(64),
SUBTYPE nvarchar(64),
PLANTYPE nvarchar(64),
PGDESIGNATIONID uniqueidentifier,
PGDSEQUENCE tinyint);
if @SELECTIONID is null
insert into @TMP1
(
PLANNEDGIFTID,
CONSTITUENTNAME,
GIFTDATE,
GIFTVEHICLE,
DESIGNATIONNAME,
DESIGNATIONVANITYNAME,
GIFTAMOUNT,
REMAINDERVALUE,
TOTALREMAINDERVALUE,
EXPECTEDMATURITYYEAR,
STATUS,
SUBTYPE,
PLANTYPE,
PGDESIGNATIONID,
PGDSEQUENCE
)
(select
PG.ID as PLANNEDGIFTID,
NF_C.NAME as CONSTITUENTNAME,
PG.GIFTDATE as GIFTDATE,
PG.VEHICLE as GIFTVEHICLE,
DESIGNATION.NAME as DESIGNATIONNAME,
DESIGNATION.VANITYNAME as DESIGNATIONVANITYNAME,
coalesce(PGD.AMOUNT, PG.GIFTAMOUNT) as GIFTAMOUNT,
case when PG.GIFTAMOUNT = 0 then
0 else
coalesce(cast(PG.REMAINDERVALUE as decimal(30,10)) * (cast(PGD.AMOUNT as decimal(30,10)) / cast(PG.GIFTAMOUNT as decimal(30,10))),PG.REMAINDERVALUE) end
as REMAINDERVALUE,
PG.REMAINDERVALUE as TOTALREMAINDERVALUE,
case when PG.EXPECTEDMATURITY = 0 then null else PG.EXPECTEDMATURITY end as EXPECTEDMATURITYYEAR,
PG.STATUS as STATUS,
coalesce(OST.DESCRIPTION, PG.SUBTYPE) as SUBTYPE,
dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID) as PLANTYPE,
PGD.ID as PGDESIGNATIONID,
PGD.SEQUENCE as PGDSEQUENCE
from
dbo.PLANNEDGIFT PG
left join dbo.PLANNEDGIFTDESIGNATION PGD on PGD.PLANNEDGIFTID = PG.ID
left join dbo.DESIGNATION on DESIGNATION.ID = PGD.DESIGNATIONID
left join dbo.PROSPECTPLAN PP on PP.ID = PG.PROSPECTPLANID
left join dbo.PLANNEDGIFTOTHERSUBTYPECODE OST on OST.ID = PG.OTHERSUBTYPECODEID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PG.CONSTITUENTID) NF_C
where
(PG.GIFTDATE between @FROMDATE and @TODATE or @FROMDATE is null or @TODATE is null) and
(PG.VEHICLECODE = @VEHICLECODE or @VEHICLECODE is null) and
(PG.STATUSCODE = @STATUSCODE or @STATUSCODE is null) and
(@ISADMIN = 1 or
(@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, PG.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)
and
(@APPUSER_IN_NONSITEROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, PG.CONSTITUENTID, @APPUSER_IN_NOSITEROLE) = 1)
)
);
else
insert into @TMP1
(
PLANNEDGIFTID,
CONSTITUENTNAME,
GIFTDATE,
GIFTVEHICLE,
DESIGNATIONNAME,
DESIGNATIONVANITYNAME,
GIFTAMOUNT,
REMAINDERVALUE,
TOTALREMAINDERVALUE,
EXPECTEDMATURITYYEAR,
STATUS,
SUBTYPE,
PLANTYPE,
PGDESIGNATIONID,
PGDSEQUENCE
)
(select
PG.ID as PLANNEDGIFTID,
NF_C.NAME as CONSTITUENTNAME,
PG.GIFTDATE as GIFTDATE,
PG.VEHICLE as GIFTVEHICLE,
DESIGNATION.NAME as DESIGNATIONNAME,
DESIGNATION.VANITYNAME as DESIGNATIONVANITYNAME,
coalesce(PGD.AMOUNT, PG.GIFTAMOUNT) as GIFTAMOUNT,
case when PG.GIFTAMOUNT = 0 then
0 else
coalesce(cast(PG.REMAINDERVALUE as decimal(30,10)) * (cast(PGD.AMOUNT as decimal(30,10)) / cast(PG.GIFTAMOUNT as decimal(30,10))),PG.REMAINDERVALUE) end
as REMAINDERVALUE,
PG.REMAINDERVALUE as TOTALREMAINDERVALUE,
case when PG.EXPECTEDMATURITY = 0 then null else PG.EXPECTEDMATURITY end as EXPECTEDMATURITYYEAR,
PG.STATUS as STATUS,
coalesce(OST.DESCRIPTION, PG.SUBTYPE) as SUBTYPE,
dbo.UFN_PROSPECTPLANTYPECODE_GETDESCRIPTION(PP.PROSPECTPLANTYPECODEID) as PLANTYPE,
PGD.ID as PGDESIGNATIONID,
PGD.SEQUENCE as PGDSEQUENCE
from
dbo.PLANNEDGIFT PG
left join dbo.PLANNEDGIFTDESIGNATION PGD on PGD.PLANNEDGIFTID = PG.ID
left join dbo.DESIGNATION on DESIGNATION.ID = PGD.DESIGNATIONID
left join dbo.PROSPECTPLAN PP on PP.ID = PG.PROSPECTPLANID
inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on PG.ID = SELECTION.ID
left join dbo.PLANNEDGIFTOTHERSUBTYPECODE OST on OST.ID = PG.OTHERSUBTYPECODEID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PG.CONSTITUENTID) NF_C
where
(PG.GIFTDATE between @FROMDATE and @TODATE or @FROMDATE is null or @TODATE is null) and
(PG.VEHICLECODE = @VEHICLECODE or @VEHICLECODE is null) and
(PG.STATUSCODE = @STATUSCODE or @STATUSCODE is null) and
(@ISADMIN = 1 or
(@APPUSER_IN_NONRACROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, PG.CONSTITUENTID, @APPUSER_IN_NOSECGROUPROLE) = 1)
and
(@APPUSER_IN_NONSITEROLE = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT_BYSITE(@CURRENTAPPUSERID, PG.CONSTITUENTID, @APPUSER_IN_NOSITEROLE) = 1)
)
);
-- Update original selection with corrected remainder values based upon the split between designations
update TMP1
set TMP1.REMAINDERVALUE = round(TMP1.REMAINDERVALUE, 2) + (TMP1.TOTALREMAINDERVALUE - (round(TMP1.REMAINDERVALUE, 2) * TMP1.PGDSEQUENCE))
from @TMP1 TMP1
inner join
(select
TMP1.PLANNEDGIFTID as PLANNEDGIFTID,
max(TMP1.PGDSEQUENCE) as PGDSEQUENCE
from
@TMP1 TMP1
where
TMP1.TOTALREMAINDERVALUE <> (select sum(round(NESTED_TMP1.REMAINDERVALUE, 2)) from @TMP1 NESTED_TMP1 where NESTED_TMP1.PLANNEDGIFTID = TMP1.PLANNEDGIFTID)
group by TMP1.PLANNEDGIFTID) TMP2
on TMP2.PLANNEDGIFTID = TMP1.PLANNEDGIFTID and TMP2.PGDSEQUENCE = TMP1.PGDSEQUENCE;
-- Return original selection with corrected remainder value for report
select
TMP1.PLANNEDGIFTID,
TMP1.CONSTITUENTNAME,
TMP1.GIFTDATE,
TMP1.GIFTVEHICLE,
TMP1.DESIGNATIONNAME,
TMP1.DESIGNATIONVANITYNAME,
TMP1.GIFTAMOUNT,
round(TMP1.REMAINDERVALUE, 2),
TMP1.EXPECTEDMATURITYYEAR,
TMP1.STATUS,
TMP1.SUBTYPE,
TMP1.PLANTYPE,
TMP1.PGDESIGNATIONID
from @TMP1 TMP1;
return 0;
end