USP_DATALIST_CONSTITUENTPLANNEDGIFTDOCUMENTATION
Returns a list of all planned gift documentation for the given constituent.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @DOCUMENTATIONTYPECODE | tinyint | IN | Documentation type |
| @TITLE | nvarchar(50) | IN | Title |
| @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_CONSTITUENTPLANNEDGIFTDOCUMENTATION
(
@CONSTITUENTID uniqueidentifier,
@DOCUMENTATIONTYPECODE tinyint = null,
@TITLE nvarchar(50) = '',
@CURRENTAPPUSERID uniqueidentifier = null,
@SITEFILTERMODE tinyint = 0,
@SITESSELECTED xml = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
)
as
set nocount on;
select
PLANNEDGIFTNOTE.ID,
PLANNEDGIFT.VEHICLE as PLANNEDGIFTVEHICLE,
PLANNEDGIFT.GIFTAMOUNT,
'75A30151-C10A-4E92-A0E2-10A9578CEA0F' as [VIEWFORMID],
0 as [DOCUMENTATIONTYPECODE],
null as [MEDIAURL],
null as [FILENAME],
null as [FILEFIELDID],
'Note' as [DOCUMENTATIONTYPE],
PLANNEDGIFTNOTE.DATEENTERED,
PLANNEDGIFTNOTETYPECODE.DESCRIPTION as [TYPE],
PLANNEDGIFTNOTE.TITLE,
AUTHOR.NAME,
PLANNEDGIFT.ID as PLANNEDGIFTID,
dbo.UFN_PLANNEDGIFT_GETSITELIST(PLANNEDGIFT.ID) as SITES,
PLANNEDGIFT.BASECURRENCYID,
dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('Planned gift note',PLANNEDGIFTNOTE.ID) as ANNOTATIONEXISTS
from
dbo.PLANNEDGIFTNOTE
inner join dbo.PLANNEDGIFT on PLANNEDGIFTNOTE.PLANNEDGIFTID = PLANNEDGIFT.ID
left join dbo.PLANNEDGIFTNOTETYPECODE on PLANNEDGIFTNOTETYPECODE.ID = PLANNEDGIFTNOTE.PLANNEDGIFTNOTETYPECODEID
left join dbo.CONSTITUENT AUTHOR on AUTHOR.ID = PLANNEDGIFTNOTE.AUTHORID
where
PLANNEDGIFT.CONSTITUENTID = @CONSTITUENTID
and (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0)
and (@TITLE = '' or PLANNEDGIFTNOTE.TITLE like @TITLE + '%')
and
(
select count(*) from dbo.UFN_SITEID_MAPFROM_PLANNEDGIFTID(PLANNEDGIFT.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
PLANNEDGIFT.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
select
PLANNEDGIFTMEDIALINK.ID,
PLANNEDGIFT.VEHICLE as PLANNEDGIFTVEHICLE,
PLANNEDGIFT.GIFTAMOUNT,
'039D34F1-438F-4A46-A2AD-BE79B9D8DD78' as [VIEWFORMID],
1 as [DOCUMENTATIONTYPECODE],
PLANNEDGIFTMEDIALINK.MEDIAURL,
null,
null,
'Media link' as [DOCUMENTATIONTYPE],
PLANNEDGIFTMEDIALINK.DATEENTERED,
PLANNEDGIFTMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
PLANNEDGIFTMEDIALINK.TITLE,
AUTHOR.NAME,
PLANNEDGIFT.ID as PLANNEDGIFTID,
dbo.UFN_PLANNEDGIFT_GETSITELIST(PLANNEDGIFT.ID) as SITES,
PLANNEDGIFT.BASECURRENCYID,
cast (0 as bit) as ANNOTATIONEXISTS
from
dbo.PLANNEDGIFTMEDIALINK
inner join dbo.PLANNEDGIFT on PLANNEDGIFTMEDIALINK.PLANNEDGIFTID = PLANNEDGIFT.ID
left join dbo.PLANNEDGIFTMEDIALINKTYPECODE on PLANNEDGIFTMEDIALINKTYPECODE.ID = PLANNEDGIFTMEDIALINK.PLANNEDGIFTMEDIALINKTYPECODEID
left join dbo.CONSTITUENT AUTHOR on AUTHOR.ID = PLANNEDGIFTMEDIALINK.AUTHORID
where
PLANNEDGIFT.CONSTITUENTID = @CONSTITUENTID
and (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1)
and (@TITLE = '' or PLANNEDGIFTMEDIALINK.TITLE like @TITLE + '%')
and
(
select count(*) from dbo.UFN_SITEID_MAPFROM_PLANNEDGIFTID(PLANNEDGIFT.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
PLANNEDGIFT.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
select
PLANNEDGIFTATTACHMENT.ID,
PLANNEDGIFT.VEHICLE as PLANNEDGIFTVEHICLE,
PLANNEDGIFT.GIFTAMOUNT,
'70494575-E26C-45B4-B55B-60DADE88365D' as [VIEWFORMID],
2 as [DOCUMENTATIONTYPECODE],
null,
PLANNEDGIFTATTACHMENT.FILENAME,
'FILE',
'Attachment' as [DOCUMENTATIONTYPE],
PLANNEDGIFTATTACHMENT.DATEENTERED,
PLANNEDGIFTATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
PLANNEDGIFTATTACHMENT.TITLE,
AUTHOR.NAME,
PLANNEDGIFT.ID as PLANNEDGIFTID,
dbo.UFN_PLANNEDGIFT_GETSITELIST(PLANNEDGIFT.ID) as SITES,
PLANNEDGIFT.BASECURRENCYID,
cast (0 as bit) as ANNOTATIONEXISTS
from
dbo.PLANNEDGIFTATTACHMENT
inner join dbo.PLANNEDGIFT on PLANNEDGIFTATTACHMENT.PLANNEDGIFTID = PLANNEDGIFT.ID
left join dbo.PLANNEDGIFTATTACHMENTTYPECODE on PLANNEDGIFTATTACHMENTTYPECODE.ID = PLANNEDGIFTATTACHMENT.PLANNEDGIFTATTACHMENTTYPECODEID
left join dbo.CONSTITUENT AUTHOR on AUTHOR.ID = PLANNEDGIFTATTACHMENT.AUTHORID
where
PLANNEDGIFT.CONSTITUENTID = @CONSTITUENTID
and (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2)
and (@TITLE = '' or PLANNEDGIFTATTACHMENT.TITLE like @TITLE + '%')
and
(
select count(*) from dbo.UFN_SITEID_MAPFROM_PLANNEDGIFTID(PLANNEDGIFT.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
PLANNEDGIFT.ID in (select PLANNEDGIFTSITE.PLANNEDGIFTID from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER inner join dbo.PLANNEDGIFTSITE on PLANNEDGIFTSITE.SITEID = SITEFILTER.SITEID)
)
order by
DATEENTERED desc, PLANNEDGIFTVEHICLE, DOCUMENTATIONTYPE;