USP_DATALIST_MERCHANDISEPRODUCTDOCUMENTATION

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

                    declare @ISSYSADMIN bit;
                    select @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

                    select    
                        MERCHANDISEPRODUCTNOTE.ID,
                        '482eb119-b775-4595-84fb-1e6946d0d03d' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        MERCHANDISEPRODUCTNOTE.DATEENTERED,
                        MERCHANDISEPRODUCTNOTETYPECODE.DESCRIPTION as [TYPE],
                        MERCHANDISEPRODUCTNOTE.TITLE,
                        CONSTITUENT.NAME,
                        CAST(MERCHANDISEPRODUCTNOTE.ID AS NVARCHAR(36)) + ':0' AS RSSID,
                        dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('Merchandise Item Note',MERCHANDISEPRODUCTNOTE.ID) as ANNOTATIONEXISTS
                    from
                        dbo.MERCHANDISEPRODUCTNOTE
                    inner join
                        dbo.MERCHANDISEPRODUCTNOTETYPECODE
                    on
                        MERCHANDISEPRODUCTNOTETYPECODE.ID = MERCHANDISEPRODUCTNOTE.MERCHANDISEPRODUCTNOTETYPECODEID
                    left join
                        dbo.CONSTITUENT
                    on
                        CONSTITUENT.ID = MERCHANDISEPRODUCTNOTE.AUTHORID
                    where 
                        @MERCHANDISEPRODUCTID = MERCHANDISEPRODUCTNOTE.MERCHANDISEPRODUCTID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                        (@TITLE = '' or MERCHANDISEPRODUCTNOTE.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,MERCHANDISEPRODUCTNOTETYPECODE.ID) = 1)

                    union all                    
                    select    
                        MERCHANDISEPRODUCTMEDIALINK.ID,
                        '00cf59bf-ab46-46cf-9331-24621fe98f88' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        MERCHANDISEPRODUCTMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        MERCHANDISEPRODUCTMEDIALINK.DATEENTERED,
                        MERCHANDISEPRODUCTMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                        MERCHANDISEPRODUCTMEDIALINK.TITLE,
                        CONSTITUENT.NAME,
                        CAST(MERCHANDISEPRODUCTMEDIALINK.ID AS NVARCHAR(36)) + ':1'  AS RSSID,
                        cast (0 as bit) as ANNOTATIONEXISTS
                    from
                        dbo.MERCHANDISEPRODUCTMEDIALINK
                    inner join
                        dbo.MERCHANDISEPRODUCTMEDIALINKTYPECODE
                    on
                        MERCHANDISEPRODUCTMEDIALINKTYPECODE.ID = MERCHANDISEPRODUCTMEDIALINK.MERCHANDISEPRODUCTMEDIALINKTYPECODEID
                    left join
                        dbo.CONSTITUENT
                    on
                        CONSTITUENT.ID = MERCHANDISEPRODUCTMEDIALINK.AUTHORID
                    where 
                        @MERCHANDISEPRODUCTID = MERCHANDISEPRODUCTMEDIALINK.MERCHANDISEPRODUCTID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                        (@TITLE = '' or MERCHANDISEPRODUCTMEDIALINK.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,MERCHANDISEPRODUCTMEDIALINKTYPECODE.ID) = 1)

                    union all                
                    select    
                        MERCHANDISEPRODUCTATTACHMENT.ID,
                        '53120402-3f66-45d3-af3b-a8ac15144bb2' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        MERCHANDISEPRODUCTATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        MERCHANDISEPRODUCTATTACHMENT.DATEENTERED,
                        MERCHANDISEPRODUCTATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                        MERCHANDISEPRODUCTATTACHMENT.TITLE,
                        CONSTITUENT.NAME,
                        CAST(MERCHANDISEPRODUCTATTACHMENT.ID AS NVARCHAR(36)) + ':2'  AS RSSID,
                        cast (0 as bit) as ANNOTATIONEXISTS
                    from
                        dbo.MERCHANDISEPRODUCTATTACHMENT
                    inner join
                        dbo.MERCHANDISEPRODUCTATTACHMENTTYPECODE
                    on
                        MERCHANDISEPRODUCTATTACHMENTTYPECODE.ID = MERCHANDISEPRODUCTATTACHMENT.MERCHANDISEPRODUCTATTACHMENTTYPECODEID
                    left join
                        dbo.CONSTITUENT
                    on
                        CONSTITUENT.ID = MERCHANDISEPRODUCTATTACHMENT.AUTHORID
                    where 
                        @MERCHANDISEPRODUCTID = MERCHANDISEPRODUCTATTACHMENT.MERCHANDISEPRODUCTID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                        (@TITLE = '' or MERCHANDISEPRODUCTATTACHMENT.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,MERCHANDISEPRODUCTATTACHMENTTYPECODE.ID) = 1)
                    order by
                        DATEENTERED desc;