USP_REVENUE_DOCUMENTATION_DATALIST_GETDATA

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

                select    
                    REVENUENOTE.ID,
                    '3a42d039-00b1-4ac4-9f96-2cd65e80e9fd' as [VIEWFORMID],
                    0 as [DOCUMENTATIONTYPECODE],
                    null as [MEDIAURL],
                    null as [FILENAME],
                    null as [FILEFIELDID],
                    'Note' as [DOCUMENTATIONTYPE],
                    REVENUENOTE.DATEENTERED,
                    REVENUENOTETYPECODE.DESCRIPTION as [TYPE],
                    REVENUENOTE.TITLE,
                    NF.NAME,
                    dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('Revenue Detail Note',REVENUENOTE.ID) as ANNOTATIONEXISTS
                from
                    dbo.REVENUENOTE
                inner join
                    dbo.REVENUENOTETYPECODE
                on
                    REVENUENOTETYPECODE.ID = REVENUENOTE.REVENUENOTETYPECODEID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUENOTE.AUTHORID) NF
                where 
                    REVENUENOTE.REVENUEID = @REVENUEID and
                    (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                    (@TITLE = '' or REVENUENOTE.TITLE like @TITLE + '%')

                union all

                select    
                    REVENUEMEDIALINK.ID,
                    'f91aec3c-60d2-46e3-b875-59424b4f0155' as [VIEWFORMID],
                    1 as [DOCUMENTATIONTYPECODE],
                    REVENUEMEDIALINK.MEDIAURL,
                    null,
                    null,
                    'Media link' as [DOCUMENTATIONTYPE],
                    REVENUEMEDIALINK.DATEENTERED,
                    REVENUEMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                    REVENUEMEDIALINK.TITLE,
                    NF.NAME,
                    0
                from
                    dbo.REVENUEMEDIALINK
                inner join
                    dbo.REVENUEMEDIALINKTYPECODE
                on
                    REVENUEMEDIALINKTYPECODE.ID = REVENUEMEDIALINK.REVENUEMEDIALINKTYPECODEID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUEMEDIALINK.AUTHORID) NF
                where 
                    REVENUEMEDIALINK.REVENUEID = @REVENUEID and
                    (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                    (@TITLE = '' or REVENUEMEDIALINK.TITLE like @TITLE + '%')

                union all

                select    
                    REVENUEATTACHMENT.ID,
                    'a6ddac7d-a2e1-4990-be72-765443238c90' as [VIEWFORMID],
                    2 as [DOCUMENTATIONTYPECODE],
                    null,
                    REVENUEATTACHMENT.FILENAME,
                    'FILE',
                    'Attachment' as [DOCUMENTATIONTYPE],
                    REVENUEATTACHMENT.DATEENTERED,
                    REVENUEATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                    REVENUEATTACHMENT.TITLE,
                    NF.NAME,
                    0
                from
                    dbo.REVENUEATTACHMENT
                inner join
                    dbo.REVENUEATTACHMENTTYPECODE
                on
                    REVENUEATTACHMENTTYPECODE.ID = REVENUEATTACHMENT.REVENUEATTACHMENTTYPECODEID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(REVENUEATTACHMENT.AUTHORID) NF
                where 
                    REVENUEATTACHMENT.REVENUEID = @REVENUEID and
                    (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                    (@TITLE = '' or REVENUEATTACHMENT.TITLE like @TITLE + '%')

                order by
                    DATEENTERED desc;