USP_DATALIST_SALESORDERDOCUMENTATION

                CREATE procedure dbo.USP_DATALIST_SALESORDERDOCUMENTATION 
                (
                    @SALESORDERID 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    
                        SALESORDERNOTE.ID,
                        '4ECF4903-B02C-4A49-9ED3-8735023316A1' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        SALESORDERNOTE.DATEENTERED,
                        SALESORDERNOTETYPECODE.DESCRIPTION as [TYPE],
                        SALESORDERNOTE.TITLE,
                        CONSTITUENT.NAME,
                        dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('Sales order note',SALESORDERNOTE.ID) as ANNOTATIONEXISTS
                    from
                        dbo.SALESORDERNOTE
                    inner join
                        dbo.SALESORDERNOTETYPECODE
                    on
                        SALESORDERNOTETYPECODE.ID = SALESORDERNOTE.SALESORDERNOTETYPECODEID
                    left join
                        dbo.CONSTITUENT
                    on
                        CONSTITUENT.ID = SALESORDERNOTE.AUTHORID
                    where 
                        SALESORDERNOTE.SALESORDERID = @SALESORDERID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                        (@TITLE = '' or SALESORDERNOTE.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,SALESORDERNOTETYPECODE.ID) = 1)

                    union all                    
                    select    
                        SALESORDERMEDIALINK.ID,
                        'AA105CD3-02C7-4F0A-8DA7-A9D4B39412DB' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        SALESORDERMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        SALESORDERMEDIALINK.DATEENTERED,
                        SALESORDERMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                        SALESORDERMEDIALINK.TITLE,
                        CONSTITUENT.NAME,
                        cast (0 as bit) as ANNOTATIONEXISTS
                    from
                        dbo.SALESORDERMEDIALINK
                    inner join
                        dbo.SALESORDERMEDIALINKTYPECODE
                    on
                        SALESORDERMEDIALINKTYPECODE.ID = SALESORDERMEDIALINK.SALESORDERMEDIALINKTYPECODEID
                    left join
                        dbo.CONSTITUENT
                    on
                        CONSTITUENT.ID = SALESORDERMEDIALINK.AUTHORID
                    where 
                        SALESORDERMEDIALINK.SALESORDERID = @SALESORDERID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                        (@TITLE = '' or SALESORDERMEDIALINK.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,SALESORDERMEDIALINKTYPECODE.ID) = 1)

                    union all                
                    select    
                        SALESORDERATTACHMENT.ID,
                        '6DF69AFC-E78D-4535-A258-39CEC05BB4E0' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        SALESORDERATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        SALESORDERATTACHMENT.DATEENTERED,
                        SALESORDERATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                        SALESORDERATTACHMENT.TITLE,
                        CONSTITUENT.NAME,
                        cast (0 as bit) as ANNOTATIONEXISTS
                    from
                        dbo.SALESORDERATTACHMENT
                    inner join
                        dbo.SALESORDERATTACHMENTTYPECODE
                    on
                        SALESORDERATTACHMENTTYPECODE.ID = SALESORDERATTACHMENT.SALESORDERATTACHMENTTYPECODEID
                    left join
                        dbo.CONSTITUENT
                    on
                        CONSTITUENT.ID = SALESORDERATTACHMENT.AUTHORID
                    where 
                        SALESORDERATTACHMENT.SALESORDERID = @SALESORDERID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                        (@TITLE = '' or SALESORDERATTACHMENT.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,SALESORDERATTACHMENTTYPECODE.ID) = 1)
                    order by
                        DATEENTERED desc;