USP_DATALIST_RESERVATIONDOCUMENTATION

                CREATE procedure dbo.USP_DATALIST_RESERVATIONDOCUMENTATION 
                (
                    @RESERVATIONID 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    
                        RESERVATIONNOTE.ID,
                        '55793A42-E64D-4762-B479-847A3FB00294' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        RESERVATIONNOTE.DATEENTERED,
                        RESERVATIONNOTETYPECODE.DESCRIPTION as [TYPE],
                        RESERVATIONNOTE.TITLE,
                        CONSTITUENT.NAME,
                        dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('Reservation note',RESERVATIONNOTE.ID) as ANNOTATIONEXISTS,
                        cast(RESERVATIONNOTE.ID as nvarchar(36)) + ':0' as [RSSID]
                    from
                        dbo.RESERVATIONNOTE
                    inner join
                        dbo.RESERVATIONNOTETYPECODE
                    on
                        RESERVATIONNOTETYPECODE.ID = RESERVATIONNOTE.RESERVATIONNOTETYPECODEID
                    left join
                        dbo.CONSTITUENT
                    on
                        CONSTITUENT.ID = RESERVATIONNOTE.AUTHORID
                    where 
                        RESERVATIONNOTE.RESERVATIONID = @RESERVATIONID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                        (@TITLE = '' or RESERVATIONNOTE.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,RESERVATIONNOTETYPECODE.ID) = 1)

                    union all                    
                    select    
                        RESERVATIONMEDIALINK.ID,
                        '1F26D7D2-1A6A-49E6-B992-D5B9E2E38D9C' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        RESERVATIONMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        RESERVATIONMEDIALINK.DATEENTERED,
                        RESERVATIONMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                        RESERVATIONMEDIALINK.TITLE,
                        CONSTITUENT.NAME,
                        cast (0 as bit) as ANNOTATIONEXISTS,
                        cast(RESERVATIONMEDIALINK.ID as nvarchar(36)) + ':1' as [RSSID]
                    from
                        dbo.RESERVATIONMEDIALINK
                    inner join
                        dbo.RESERVATIONMEDIALINKTYPECODE
                    on
                        RESERVATIONMEDIALINKTYPECODE.ID = RESERVATIONMEDIALINK.RESERVATIONMEDIALINKTYPECODEID
                    left join
                        dbo.CONSTITUENT
                    on
                        CONSTITUENT.ID = RESERVATIONMEDIALINK.AUTHORID
                    where 
                        RESERVATIONMEDIALINK.RESERVATIONID = @RESERVATIONID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                        (@TITLE = '' or RESERVATIONMEDIALINK.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,RESERVATIONMEDIALINKTYPECODE.ID) = 1)

                    union all                
                    select    
                        RESERVATIONATTACHMENT.ID,
                        'B85C3DFE-7835-48D9-BAF8-2A049120E0C8' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        RESERVATIONATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        RESERVATIONATTACHMENT.DATEENTERED,
                        RESERVATIONATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                        RESERVATIONATTACHMENT.TITLE,
                        CONSTITUENT.NAME,
                        cast (0 as bit) as ANNOTATIONEXISTS,
                        cast(RESERVATIONATTACHMENT.ID as nvarchar(36)) + ':2' as [RSSID]
                    from
                        dbo.RESERVATIONATTACHMENT
                    inner join
                        dbo.RESERVATIONATTACHMENTTYPECODE
                    on
                        RESERVATIONATTACHMENTTYPECODE.ID = RESERVATIONATTACHMENT.RESERVATIONATTACHMENTTYPECODEID
                    left join
                        dbo.CONSTITUENT
                    on
                        CONSTITUENT.ID = RESERVATIONATTACHMENT.AUTHORID
                    where 
                        RESERVATIONATTACHMENT.RESERVATIONID = @RESERVATIONID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                        (@TITLE = '' or RESERVATIONATTACHMENT.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,RESERVATIONATTACHMENTTYPECODE.ID) = 1)
                    order by
                        DATEENTERED desc;