USP_DATALIST_CONSTITUENTPROSPECTPLANDOCUMENTATION

Returns a list of all prospect plan 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_CONSTITUENTPROSPECTPLANDOCUMENTATION
                (
                    @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    
                        PROSPECTPLANNOTE.ID,
                        PROSPECTPLAN.NAME as PROSPECTPLAN,
                        '6CED157B-7505-4015-B6F4-3017CD5B0752' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        PROSPECTPLANNOTE.DATEENTERED,
                        PROSPECTPLANNOTETYPECODE.DESCRIPTION as [TYPE],
                        PROSPECTPLANNOTE.TITLE,
                        CONSTITUENT.NAME,
                        PROSPECTPLAN.ID as PROSPECTPLANID,
                        dbo.UFN_PROSPECTPLAN_GETSITELIST(PROSPECTPLAN.ID) as SITES
                    from
                        dbo.PROSPECTPLANNOTE
                        inner join dbo.PROSPECTPLAN on PROSPECTPLANNOTE.PROSPECTPLANID = PROSPECTPLAN.ID
                        inner join dbo.PROSPECTPLANNOTETYPECODE on PROSPECTPLANNOTETYPECODE.ID = PROSPECTPLANNOTE.PROSPECTPLANNOTETYPECODEID
                        left outer join dbo.CONSTITUENT on CONSTITUENT.ID = PROSPECTPLANNOTE.AUTHORID
                    where 
                        PROSPECTPLAN.PROSPECTID = @CONSTITUENTID
                        and (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0)
                        and (@TITLE = '' or PROSPECTPLANNOTE.TITLE like @TITLE + '%')
                        and 
                        (
                            select count(*) from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.ID) as PROSPECTPLANSITE where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
                        ) > 0
                        and 
                        (
                            @SITEFILTERMODE = 0
                            or 
                            PROSPECTPLAN.ID 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    
                        PROSPECTPLANMEDIALINK.ID,
                        PROSPECTPLAN.NAME as PROSPECTPLAN,
                        '7E2F498A-F189-4F76-ACB7-F06D76A61059' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        PROSPECTPLANMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        PROSPECTPLANMEDIALINK.DATEENTERED,
                        PROSPECTPLANMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                        PROSPECTPLANMEDIALINK.TITLE,
                        CONSTITUENT.NAME,
                        PROSPECTPLAN.ID as PROSPECTPLANID,
                        dbo.UFN_PROSPECTPLAN_GETSITELIST(PROSPECTPLAN.ID) as SITES
                    from
                        dbo.PROSPECTPLANMEDIALINK
                        inner join dbo.PROSPECTPLAN on PROSPECTPLANMEDIALINK.PROSPECTPLANID = PROSPECTPLAN.ID
                        inner join dbo.PROSPECTPLANMEDIALINKTYPECODE on PROSPECTPLANMEDIALINKTYPECODE.ID = PROSPECTPLANMEDIALINK.PROSPECTPLANMEDIALINKTYPECODEID
                        left outer join dbo.CONSTITUENT on CONSTITUENT.ID = PROSPECTPLANMEDIALINK.AUTHORID
                    where 
                        PROSPECTPLAN.PROSPECTID = @CONSTITUENTID
                        and (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1)
                        and (@TITLE = '' or PROSPECTPLANMEDIALINK.TITLE like @TITLE + '%')
                        and 
                        (
                            select count(*) from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.ID) as PROSPECTPLANSITE where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
                        ) > 0
                        and 
                        (
                            @SITEFILTERMODE = 0
                            or 
                            PROSPECTPLAN.ID 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    
                        PROSPECTPLANATTACHMENT.ID,
                        PROSPECTPLAN.NAME as PROSPECTPLAN,
                        '7786E3F5-4D54-4EDB-BF0E-2C8F57350800' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        PROSPECTPLANATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        PROSPECTPLANATTACHMENT.DATEENTERED,
                        PROSPECTPLANATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                        PROSPECTPLANATTACHMENT.TITLE,
                        CONSTITUENT.NAME,
                        PROSPECTPLAN.ID as PROSPECTPLANID,
                        dbo.UFN_PROSPECTPLAN_GETSITELIST(PROSPECTPLAN.ID) as SITES
                    from
                        dbo.PROSPECTPLANATTACHMENT
                        inner join dbo.PROSPECTPLAN on PROSPECTPLANATTACHMENT.PROSPECTPLANID = PROSPECTPLAN.ID
                        inner join dbo.PROSPECTPLANATTACHMENTTYPECODE on PROSPECTPLANATTACHMENTTYPECODE.ID = PROSPECTPLANATTACHMENT.PROSPECTPLANATTACHMENTTYPECODEID
                        left outer join dbo.CONSTITUENT on CONSTITUENT.ID = PROSPECTPLANATTACHMENT.AUTHORID
                    where 
                        PROSPECTPLAN.PROSPECTID = @CONSTITUENTID
                        and (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2)
                        and (@TITLE = '' or PROSPECTPLANATTACHMENT.TITLE like @TITLE + '%')
                        and 
                        (
                            select count(*) from dbo.UFN_SITEID_MAPFROM_PROSPECTPLANID(PROSPECTPLAN.ID) as PROSPECTPLANSITE where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[PROSPECTPLANSITE].[SITEID] or (SITEID is null and [PROSPECTPLANSITE].[SITEID] is null)))
                        ) > 0
                        and 
                        (
                            @SITEFILTERMODE = 0
                            or 
                            PROSPECTPLAN.ID 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, PROSPECTPLAN, DOCUMENTATIONTYPE;