USP_DATALIST_CONSTITUENTINTERACTIONSDOCUMENTATION
This datalist returns all documentation for all of a constituent's interactions.
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_CONSTITUENTINTERACTIONSDOCUMENTATION
(
@CONSTITUENTID uniqueidentifier = null,
@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
INTERACTIONNOTE.ID,
case when PROSPECTPLAN.ID is null
then 'FC892CA7-41AF-4F32-8D10-DAA1FDCB4A1D'
else '4EC56467-5BC9-4977-8B83-4FCD0AC3B9F9'
end as [VIEWFORMID],
INTERACTION.ID as INTERACTIONID,
INTERACTION.OBJECTIVE as INTERACTIONSUMMARY,
coalesce(PROSPECTPLAN.NAME, '') as PROSPECTPLANNAME,
0 as [DOCUMENTATIONTYPECODE],
null as [MEDIAURL],
null as [FILENAME],
null as [FILEFIELDID],
'Note' as [DOCUMENTATIONTYPE],
INTERACTIONNOTE.DATEENTERED,
INTERACTIONNOTETYPECODE.DESCRIPTION as [TYPE],
INTERACTIONNOTE.TITLE,
AUTHOR_NF.NAME,
coalesce(ADDEDBY_NF.NAME, APPUSER.USERNAME),
INTERACTION.ISINTERACTION as [ISINTERACTION],
case when PROSPECTPLAN.ID is null
then 0
else 1
end as [ISSTEP]
from
dbo.INTERACTIONNOTE
inner join dbo.INTERACTION on INTERACTIONNOTE.INTERACTIONID = INTERACTION.ID
left join dbo.PROSPECTPLAN on INTERACTION.PROSPECTPLANID = PROSPECTPLAN.ID
left join dbo.INTERACTIONNOTETYPECODE on INTERACTIONNOTETYPECODE.ID = INTERACTIONNOTE.INTERACTIONNOTETYPECODEID
left join dbo.APPUSER on APPUSER.ID = INTERACTIONNOTE.ENTEREDBYAPPUSERID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(APPUSER.CONSTITUENTID) ADDEDBY_NF
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTIONNOTE.AUTHORID) AUTHOR_NF
where
INTERACTION.CONSTITUENTID = @CONSTITUENTID and
(@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
(@TITLE = '' or INTERACTIONNOTE.TITLE like @TITLE + '%') and
(
select count(*)
from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(INTERACTION.ID) INTERACTIONSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[INTERACTIONSITE].[SITEID] or (SITEID is null and [INTERACTIONSITE].[SITEID] is null)))
) > 0 and
(
@SITEFILTERMODE = 0 or
(
INTERACTION.PROSPECTPLANID is null and
INTERACTION.ID in (
select INTERACTIONSITE.INTERACTIONID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.INTERACTIONSITE on INTERACTIONSITE.SITEID = SITEFILTER.SITEID
)
) or
(
INTERACTION.PROSPECTPLANID is not null and
INTERACTION.PROSPECTPLANID in(
select PROSPECTPLANSITE.PROSPECTPLANID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
)
)
)
union all
select
INTERACTIONMEDIALINK.ID,
case when PROSPECTPLAN.ID is null
then '7F956A38-C7BC-4FAA-B6B0-8618233268D6'
else 'E7EB7C48-98D2-4EA3-8CB4-7305C3738592'
end as [VIEWFORMID],
INTERACTION.ID as INTERACTIONID,
INTERACTION.OBJECTIVE as INTERACTIONSUMMARY,
coalesce(PROSPECTPLAN.NAME, '') as PROSPECTPLANNAME,
1 as [DOCUMENTATIONTYPECODE],
INTERACTIONMEDIALINK.MEDIAURL,
null,
null,
'Media link' as [DOCUMENTATIONTYPE],
INTERACTIONMEDIALINK.DATEENTERED,
INTERACTIONMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
INTERACTIONMEDIALINK.TITLE,
AUTHOR_NF.NAME,
coalesce(ADDEDBY_NF.NAME, APPUSER.USERNAME),
INTERACTION.ISINTERACTION as [ISINTERACTION],
case when PROSPECTPLAN.ID is null
then 0
else 1
end as [ISSTEP]
from
dbo.INTERACTIONMEDIALINK
inner join dbo.INTERACTION on INTERACTIONMEDIALINK.INTERACTIONID = INTERACTION.ID
left join dbo.PROSPECTPLAN on INTERACTION.PROSPECTPLANID = PROSPECTPLAN.ID
left join dbo.INTERACTIONMEDIALINKTYPECODE on INTERACTIONMEDIALINKTYPECODE.ID = INTERACTIONMEDIALINK.INTERACTIONMEDIALINKTYPECODEID
left join dbo.APPUSER on APPUSER.ID = INTERACTIONMEDIALINK.ENTEREDBYAPPUSERID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(APPUSER.CONSTITUENTID) ADDEDBY_NF
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTIONMEDIALINK.AUTHORID) AUTHOR_NF
where
INTERACTION.CONSTITUENTID = @CONSTITUENTID and
(@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
(@TITLE = '' or INTERACTIONMEDIALINK.TITLE like @TITLE + '%') and
(
select count(*)
from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(INTERACTION.ID) INTERACTIONSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[INTERACTIONSITE].[SITEID] or (SITEID is null and [INTERACTIONSITE].[SITEID] is null)))
) > 0 and
(
@SITEFILTERMODE = 0 or
(
INTERACTION.PROSPECTPLANID is null and
INTERACTION.ID in (
select INTERACTIONSITE.INTERACTIONID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.INTERACTIONSITE on INTERACTIONSITE.SITEID = SITEFILTER.SITEID
)
) or
(
INTERACTION.PROSPECTPLANID is not null and
INTERACTION.PROSPECTPLANID in(
select PROSPECTPLANSITE.PROSPECTPLANID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
)
)
)
union all
select
INTERACTIONATTACHMENT.ID,
case when PROSPECTPLAN.ID is null
then 'F1EE1C4A-7DA1-4EF1-9AD7-D8F8C7BEF250'
else '42BC386E-DAAB-4701-B29A-6BC90FB3894F'
end as [VIEWFORMID],
INTERACTION.ID as INTERACTIONID,
INTERACTION.OBJECTIVE as INTERACTIONSUMMARY,
coalesce(PROSPECTPLAN.NAME, '') as PROSPECTPLANNAME,
2 as [DOCUMENTATIONTYPECODE],
null,
INTERACTIONATTACHMENT.FILENAME,
'FILE',
'Attachment' as [DOCUMENTATIONTYPE],
INTERACTIONATTACHMENT.DATEENTERED,
INTERACTIONATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
INTERACTIONATTACHMENT.TITLE,
AUTHOR_NF.NAME,
coalesce(ADDEDBY_NF.NAME, APPUSER.USERNAME),
INTERACTION.ISINTERACTION as [ISINTERACTION],
case when PROSPECTPLAN.ID is null
then 0
else 1
end as [ISSTEP]
from
dbo.INTERACTIONATTACHMENT
inner join dbo.INTERACTION on INTERACTIONATTACHMENT.INTERACTIONID = INTERACTION.ID
left join dbo.PROSPECTPLAN on INTERACTION.PROSPECTPLANID = PROSPECTPLAN.ID
left join dbo.INTERACTIONATTACHMENTTYPECODE on INTERACTIONATTACHMENTTYPECODE.ID = INTERACTIONATTACHMENT.INTERACTIONATTACHMENTTYPECODEID
left join dbo.APPUSER on APPUSER.ID = INTERACTIONATTACHMENT.ENTEREDBYAPPUSERID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(APPUSER.CONSTITUENTID) ADDEDBY_NF
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(INTERACTIONATTACHMENT.AUTHORID) AUTHOR_NF
where
INTERACTION.CONSTITUENTID = @CONSTITUENTID and
(@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
(@TITLE = '' or INTERACTIONATTACHMENT.TITLE like @TITLE + '%') and
(
select count(*)
from dbo.UFN_SITEID_MAPFROM_INTERACTIONID(INTERACTION.ID) INTERACTIONSITE
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[INTERACTIONSITE].[SITEID] or (SITEID is null and [INTERACTIONSITE].[SITEID] is null)))
) > 0 and
(
@SITEFILTERMODE = 0 or
(
INTERACTION.PROSPECTPLANID is null and
INTERACTION.ID in (
select INTERACTIONSITE.INTERACTIONID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.INTERACTIONSITE on INTERACTIONSITE.SITEID = SITEFILTER.SITEID
)
) or
(
INTERACTION.PROSPECTPLANID is not null and
INTERACTION.PROSPECTPLANID in(
select PROSPECTPLANSITE.PROSPECTPLANID
from dbo.UFN_SITE_BUILDDATALISTSITEFILTER (@CURRENTAPPUSERID, @SITEFILTERMODE, @SITESSELECTED) SITEFILTER
inner join dbo.PROSPECTPLANSITE on PROSPECTPLANSITE.SITEID = SITEFILTER.SITEID
)
)
)
order by
DATEENTERED desc;