USP_DATALIST_ALTRUCONSTITUENTDOCUMENTATION

            CREATE procedure dbo.USP_DATALIST_ALTRUCONSTITUENTDOCUMENTATION 
            (
                @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,
                    coalesce(ALTRUANNOTATIONNOTIFICATION.DISPLAYINDAILYSALES, 0) as DISPLAYINDAILYSALES
                from
                    dbo.CONSTITUENTNOTE
                inner join
                    dbo.CONSTITUENTNOTETYPECODE
                on
                    CONSTITUENTNOTETYPECODE.ID = CONSTITUENTNOTE.CONSTITUENTNOTETYPECODEID
                left join
                    dbo.ALTRUANNOTATIONNOTIFICATION
                on
                    ALTRUANNOTATIONNOTIFICATION.ID = CONSTITUENTNOTE.ID
                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,
                    0
                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,
                    0
                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;