USP_DATALIST_CONSTITUENTDOCUMENTATION

                CREATE procedure dbo.USP_DATALIST_CONSTITUENTDOCUMENTATION 
                (
                    @CONSTITUENTID 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    
                        CONSTITUENTNOTE.ID,
                        '55EBA39F-1513-46EC-85DC-7FEBAE3F0698' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        CONSTITUENTNOTE.DATEENTERED,
                        CONSTITUENTNOTETYPECODE.DESCRIPTION as [TYPE],
                        CONSTITUENTNOTE.TITLE,
                        NF.NAME,
                        CAST(CONSTITUENTNOTE.ID AS NVARCHAR(36)) + ':0' AS RSSID,
                        dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('Constituent note',CONSTITUENTNOTE.ID) as ANNOTATIONEXISTS
                    from
                        dbo.CONSTITUENTNOTE
                    inner join
                        dbo.CONSTITUENTNOTETYPECODE
                    on
                        CONSTITUENTNOTETYPECODE.ID = CONSTITUENTNOTE.CONSTITUENTNOTETYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTNOTE.AUTHORID) NF
                    where 
                        CONSTITUENTNOTE.CONSTITUENTID = @CONSTITUENTID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                        (@TITLE = '' or CONSTITUENTNOTE.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,CONSTITUENTNOTETYPECODE.ID) = 1)

                    union all                    
                    select    
                        CONSTITUENTMEDIALINK.ID,
                        'E708EC1F-89D7-4453-BE18-808F6DE7419F' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        CONSTITUENTMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        CONSTITUENTMEDIALINK.DATEENTERED,
                        CONSTITUENTMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                        CONSTITUENTMEDIALINK.TITLE,
                        NF.NAME,
                        CAST(CONSTITUENTMEDIALINK.ID AS NVARCHAR(36)) + ':1'  AS RSSID,
                        cast (0 as bit) as ANNOTATIONEXISTS
                    from
                        dbo.CONSTITUENTMEDIALINK
                    inner join
                        dbo.CONSTITUENTMEDIALINKTYPECODE
                    on
                        CONSTITUENTMEDIALINKTYPECODE.ID = CONSTITUENTMEDIALINK.CONSTITUENTMEDIALINKTYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTMEDIALINK.AUTHORID) NF
                    where 
                        CONSTITUENTMEDIALINK.CONSTITUENTID = @CONSTITUENTID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                        (@TITLE = '' or CONSTITUENTMEDIALINK.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,CONSTITUENTMEDIALINKTYPECODE.ID) = 1)
                    union all                
                    select    
                        CONSTITUENTATTACHMENT.ID,
                        '7126725A-12F2-4A37-A992-10891CEEA336' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        CONSTITUENTATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        CONSTITUENTATTACHMENT.DATEENTERED,
                        CONSTITUENTATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                        CONSTITUENTATTACHMENT.TITLE,
                        NF.NAME,
                        CAST(CONSTITUENTATTACHMENT.ID AS NVARCHAR(36)) + ':2'  AS RSSID,
                        cast (0 as bit) as ANNOTATIONEXISTS
                    from
                        dbo.CONSTITUENTATTACHMENT
                    inner join
                        dbo.CONSTITUENTATTACHMENTTYPECODE
                    on
                        CONSTITUENTATTACHMENTTYPECODE.ID = CONSTITUENTATTACHMENT.CONSTITUENTATTACHMENTTYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(CONSTITUENTATTACHMENT.AUTHORID) NF
                    where 
                        CONSTITUENTATTACHMENT.CONSTITUENTID = @CONSTITUENTID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                        (@TITLE = '' or CONSTITUENTATTACHMENT.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,CONSTITUENTATTACHMENTTYPECODE.ID) = 1)
                    order by
                        DATEENTERED desc;