USP_DATALIST_COURSEDOCUMENTATION

            CREATE procedure dbo.USP_DATALIST_COURSEDOCUMENTATION 
            (
                @COURSEID 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    
                    COURSENOTE.ID,
                    'a3bc7dab-d037-4021-b5f1-a0e0a3fbde16' as [VIEWFORMID],
                    0 as [DOCUMENTATIONTYPECODE],
                    null as [MEDIAURL],
                    null as [FILENAME],
                    null as [FILEFIELDID],
                    'Note' as [DOCUMENTATIONTYPE],
                    COURSENOTE.DATEENTERED,
                    COURSENOTETYPECODE.DESCRIPTION as [TYPE],
                    COURSENOTE.TITLE,
                    dbo.UFN_CONSTITUENT_BUILDNAME(COURSENOTE.AUTHORID) as NAME,
          CAST(COURSENOTE.ID AS NVARCHAR(36)) + ':0' AS RSSID,
                    dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('Course note',COURSENOTE.ID) as ANNOTATIONEXISTS          
                from
                    dbo.COURSENOTE
                    inner join dbo.COURSENOTETYPECODE on COURSENOTETYPECODE.ID = COURSENOTE.COURSENOTETYPECODEID        
                where 
                    COURSENOTE.COURSEID = @COURSEID and
                    (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                    (@TITLE = '' or COURSENOTE.TITLE like @TITLE + '%')     and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,COURSENOTETYPECODE.ID) = 1)
                union all    
                select    
                    COURSEMEDIALINK.ID,
                    '3621f07d-feff-4fed-92d4-5b1944e44096' as [VIEWFORMID],
                    1 as [DOCUMENTATIONTYPECODE],
                    COURSEMEDIALINK.MEDIAURL,
                    null,
                    null,
                    'Media link' as [DOCUMENTATIONTYPE],
                    COURSEMEDIALINK.DATEENTERED,
                    COURSEMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                    COURSEMEDIALINK.TITLE,
                    dbo.UFN_CONSTITUENT_BUILDNAME(COURSEMEDIALINK.AUTHORID) as NAME,
          CAST(COURSEMEDIALINK.ID AS NVARCHAR(36)) + ':1'  AS RSSID,
                    cast(0 as bit) as ANNOTATIONEXISTS          
                from
                    dbo.COURSEMEDIALINK
                    inner join dbo.COURSEMEDIALINKTYPECODE    on COURSEMEDIALINKTYPECODE.ID = COURSEMEDIALINK.COURSEMEDIALINKTYPECODEID    
                where 
                    COURSEMEDIALINK.COURSEID = @COURSEID and
                    (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                    (@TITLE = '' or COURSEMEDIALINK.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,COURSEMEDIALINKTYPECODE.ID) = 1)    
                union all    
                select    
                    COURSEATTACHMENT.ID,
                    '2974b0c2-922f-46c8-bd14-6f720fc9804d' as [VIEWFORMID],
                    2 as [DOCUMENTATIONTYPECODE],
                    null,
                    COURSEATTACHMENT.FILENAME,
                    'FILE',
                    'Attachment' as [DOCUMENTATIONTYPE],
                    COURSEATTACHMENT.DATEENTERED,
                    COURSEATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                    COURSEATTACHMENT.TITLE,
                    dbo.UFN_CONSTITUENT_BUILDNAME(COURSEATTACHMENT.AUTHORID) as NAME,
          CAST(COURSEATTACHMENT.ID AS NVARCHAR(36)) + ':2'  AS RSSID,
                    cast(0 as bit) as ANNOTATIONEXISTS          
                from
                    dbo.COURSEATTACHMENT
                    inner join dbo.COURSEATTACHMENTTYPECODE on COURSEATTACHMENTTYPECODE.ID = COURSEATTACHMENT.COURSEATTACHMENTTYPECODEID    
                where 
                    COURSEATTACHMENT.COURSEID = @COURSEID and
                    (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                    (@TITLE = '' or COURSEATTACHMENT.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,COURSEATTACHMENTTYPECODE.ID) = 1)    
                order by
                    DATEENTERED desc;