USP_DATALIST_PROSPECTRESEARCHREQUESTCONSTITUENTDOCUMENTATION

This datalist returns all documentation for a prospect research request constituent.
Parameters

Parameter	Parameter Type	Mode	Description
@PROSPECTRESEARCHREQUESTCONSTITUENTID	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.
Definition

Copy


                CREATE procedure dbo.USP_DATALIST_PROSPECTRESEARCHREQUESTCONSTITUENTDOCUMENTATION 
                (
                    @PROSPECTRESEARCHREQUESTCONSTITUENTID uniqueidentifier = null,
                    @DOCUMENTATIONTYPECODE tinyint = null,
                    @TITLE nvarchar(50) = '',
                    @CURRENTAPPUSERID uniqueidentifier = null
                )
                as
                    set nocount on;

                    declare @ISSYSADMIN bit;
                    select @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

                    select    
                        PROSPECTRESEARCHREQUESTCONSTITUENTNOTE.ID,
                        '206b9397-1f70-4481-99f6-41bd7028f453' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        PROSPECTRESEARCHREQUESTCONSTITUENTNOTE.DATEENTERED,
                        PROSPECTRESEARCHREQUESTNOTETYPECODE.DESCRIPTION as [TYPE],
                        PROSPECTRESEARCHREQUESTCONSTITUENTNOTE.TITLE,
                        NF.NAME,
                        CAST(PROSPECTRESEARCHREQUESTCONSTITUENTNOTE.ID AS NVARCHAR(36)) + ':0' AS RSSID
                    from
                        dbo.PROSPECTRESEARCHREQUESTCONSTITUENTNOTE
                    inner join
                        dbo.PROSPECTRESEARCHREQUESTNOTETYPECODE
                    on
                        PROSPECTRESEARCHREQUESTNOTETYPECODE.ID = PROSPECTRESEARCHREQUESTCONSTITUENTNOTE.PROSPECTRESEARCHREQUESTNOTETYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTRESEARCHREQUESTCONSTITUENTNOTE.AUTHORID) NF
                    where 
                        PROSPECTRESEARCHREQUESTCONSTITUENTNOTE.PROSPECTRESEARCHREQUESTCONSTITUENTID = @PROSPECTRESEARCHREQUESTCONSTITUENTID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                        (@TITLE = '' or PROSPECTRESEARCHREQUESTCONSTITUENTNOTE.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,PROSPECTRESEARCHREQUESTNOTETYPECODE.ID) = 1)

                    union all                    
                    select    
                        PROSPECTRESEARCHREQUESTCONSTITUENTMEDIALINK.ID,
                        'c8176dad-0698-4fa0-8751-d18d82093fc0' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        PROSPECTRESEARCHREQUESTCONSTITUENTMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        PROSPECTRESEARCHREQUESTCONSTITUENTMEDIALINK.DATEENTERED,
                        PROSPECTRESEARCHREQUESTMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                        PROSPECTRESEARCHREQUESTCONSTITUENTMEDIALINK.TITLE,
                        NF.NAME,
                        CAST(PROSPECTRESEARCHREQUESTCONSTITUENTMEDIALINK.ID AS NVARCHAR(36)) + ':1'  AS RSSID
                    from
                        dbo.PROSPECTRESEARCHREQUESTCONSTITUENTMEDIALINK
                    inner join
                        dbo.PROSPECTRESEARCHREQUESTMEDIALINKTYPECODE
                    on
                        PROSPECTRESEARCHREQUESTMEDIALINKTYPECODE.ID = PROSPECTRESEARCHREQUESTCONSTITUENTMEDIALINK.PROSPECTRESEARCHREQUESTMEDIALINKTYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTRESEARCHREQUESTCONSTITUENTMEDIALINK.AUTHORID) NF
                    where 
                        PROSPECTRESEARCHREQUESTCONSTITUENTMEDIALINK.PROSPECTRESEARCHREQUESTCONSTITUENTID = @PROSPECTRESEARCHREQUESTCONSTITUENTID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                        (@TITLE = '' or PROSPECTRESEARCHREQUESTCONSTITUENTMEDIALINK.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,PROSPECTRESEARCHREQUESTMEDIALINKTYPECODE.ID) = 1)
                    union all                
                    select    
                        PROSPECTRESEARCHREQUESTCONSTITUENTATTACHMENT.ID,
                        'd54e93be-599d-46bc-92a5-2dd29060adea' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        PROSPECTRESEARCHREQUESTCONSTITUENTATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        PROSPECTRESEARCHREQUESTCONSTITUENTATTACHMENT.DATEENTERED,
                        PROSPECTRESEARCHREQUESTATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                        PROSPECTRESEARCHREQUESTCONSTITUENTATTACHMENT.TITLE,
                        NF.NAME,
                        CAST(PROSPECTRESEARCHREQUESTCONSTITUENTATTACHMENT.ID AS NVARCHAR(36)) + ':2'  AS RSSID
                    from
                        dbo.PROSPECTRESEARCHREQUESTCONSTITUENTATTACHMENT
                    inner join
                        dbo.PROSPECTRESEARCHREQUESTATTACHMENTTYPECODE
                    on
                        PROSPECTRESEARCHREQUESTATTACHMENTTYPECODE.ID = PROSPECTRESEARCHREQUESTCONSTITUENTATTACHMENT.PROSPECTRESEARCHREQUESTATTACHMENTTYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTRESEARCHREQUESTCONSTITUENTATTACHMENT.AUTHORID) NF
                    where 
                        PROSPECTRESEARCHREQUESTCONSTITUENTATTACHMENT.PROSPECTRESEARCHREQUESTCONSTITUENTID = @PROSPECTRESEARCHREQUESTCONSTITUENTID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                        (@TITLE = '' or PROSPECTRESEARCHREQUESTCONSTITUENTATTACHMENT.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,PROSPECTRESEARCHREQUESTATTACHMENTTYPECODE.ID) = 1)
                    order by
                        DATEENTERED desc;