USP_DATALIST_MEMBERDOCUMENTATION

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

                select    
                    MEMBERNOTE.ID,
                    'f6651922-9ec2-4449-a214-c773f6eae072' as [VIEWFORMID],
                    0 as [DOCUMENTATIONTYPECODE],
                    null as [MEDIAURL],
                    null as [FILENAME],
                    null as [FILEFIELDID],
                    'Note' as [DOCUMENTATIONTYPE],
                    MEMBERNOTE.DATEENTERED,
                    MEMBERNOTETYPECODE.DESCRIPTION as [TYPE],
                    MEMBERNOTE.TITLE,
                    NF.NAME as NAME,
                    dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('Member note',MEMBERNOTE.ID) as ANNOTATIONEXISTS
                from
                    dbo.MEMBERNOTE
                    inner join dbo.MEMBERNOTETYPECODE on MEMBERNOTETYPECODE.ID = MEMBERNOTE.MEMBERNOTETYPECODEID        
                    inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERNOTE.MEMBERSHIPID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBERNOTE.AUTHORID) NF
                where 
                    MEMBER.ID = @MEMBERID and
                    (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                    (@TITLE = '' or MEMBERNOTE.TITLE like @TITLE + '%')    
                union all    
                select    
                    MEMBERMEDIALINK.ID,
                    '53a9494e-27c4-48d7-a78a-a1bbf59e02d7' as [VIEWFORMID],
                    1 as [DOCUMENTATIONTYPECODE],
                    MEMBERMEDIALINK.MEDIAURL,
                    null,
                    null,
                    'Media link' as [DOCUMENTATIONTYPE],
                    MEMBERMEDIALINK.DATEENTERED,
                    MEMBERMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                    MEMBERMEDIALINK.TITLE,
                    NF.NAME as NAME,
                    cast(0 as bit) as ANNOTATIONEXISTS
                from
                    dbo.MEMBERMEDIALINK
                    inner join dbo.MEMBERMEDIALINKTYPECODE    on MEMBERMEDIALINKTYPECODE.ID = MEMBERMEDIALINK.MEMBERMEDIALINKTYPECODEID    
                    inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERMEDIALINK.MEMBERSHIPID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBERMEDIALINK.AUTHORID) NF
                where 
                    MEMBER.ID = @MEMBERID and
                    (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                    (@TITLE = '' or MEMBERMEDIALINK.TITLE like @TITLE + '%')        
                union all    
                select    
                    MEMBERATTACHMENT.ID,
                    'a5ee4793-6f46-4a05-87ec-43c2da5ea3ca' as [VIEWFORMID],
                    2 as [DOCUMENTATIONTYPECODE],
                    null,
                    MEMBERATTACHMENT.FILENAME,
                    'FILE',
                    'Attachment' as [DOCUMENTATIONTYPE],
                    MEMBERATTACHMENT.DATEENTERED,
                    MEMBERATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                    MEMBERATTACHMENT.TITLE,
                    NF.NAME as NAME,
                    cast(0 as bit) as ANNOTATIONEXISTS
                from
                    dbo.MEMBERATTACHMENT
                    inner join dbo.MEMBERATTACHMENTTYPECODE on MEMBERATTACHMENTTYPECODE.ID = MEMBERATTACHMENT.MEMBERATTACHMENTTYPECODEID    
                    inner join dbo.MEMBER on MEMBER.MEMBERSHIPID = MEMBERATTACHMENT.MEMBERSHIPID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MEMBERATTACHMENT.AUTHORID) NF
                where 
                    MEMBER.ID = @MEMBERID and
                    (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                    (@TITLE = '' or MEMBERATTACHMENT.TITLE like @TITLE + '%')        
                order by
                    DATEENTERED desc;