USP_DATALIST_LEDGERACCOUNTDOCUMENTATION

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

                set @TITLE = replace(@TITLE,'*','%')

                select    
                    LEDGERACCOUNTNOTE.ID,
                    '6e8fd72b-e23b-4600-9868-0adc4d55c4e1' as [VIEWFORMID],
                    0 as [DOCUMENTATIONTYPECODE],
                    null as [MEDIAURL],
                    null as [FILENAME],
                    null as [FILEFIELDID],
                    'Note' as [DOCUMENTATIONTYPE],
                    LEDGERACCOUNTNOTE.DATEENTERED,
                    LEDGERACCOUNTNOTETYPECODE.DESCRIPTION as [TYPE],
                    LEDGERACCOUNTNOTE.TITLE,
                    dbo.UFN_CONSTITUENT_BUILDNAME(LEDGERACCOUNTNOTE.AUTHORID) as NAME,
                    cast(LEDGERACCOUNTNOTE.ID as nvarchar(36)) + ':0' as RSSID,
                    dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('Ledger Account Note', LEDGERACCOUNTNOTE.ID) as ANNOTATIONEXISTS
                from
                    dbo.LEDGERACCOUNTNOTE
                    inner join dbo.LEDGERACCOUNTNOTETYPECODE on LEDGERACCOUNTNOTETYPECODE.ID = LEDGERACCOUNTNOTE.LEDGERACCOUNTNOTETYPECODEID        
                where 
                    LEDGERACCOUNTNOTE.LEDGERACCOUNTID = @LEDGERACCOUNTID and
                    (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                    (@TITLE = '' or LEDGERACCOUNTNOTE.TITLE like @TITLE + '%')    
                union all    
                select    
                    LEDGERACCOUNTMEDIALINK.ID,
                    'c8daa190-16f3-46da-a026-c48379e7517a' as [VIEWFORMID],
                    1 as [DOCUMENTATIONTYPECODE],
                    LEDGERACCOUNTMEDIALINK.MEDIAURL,
                    null,
                    null,
                    'Media link' as [DOCUMENTATIONTYPE],
                    LEDGERACCOUNTMEDIALINK.DATEENTERED,
                    LEDGERACCOUNTMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                    LEDGERACCOUNTMEDIALINK.TITLE,
                    dbo.UFN_CONSTITUENT_BUILDNAME(LEDGERACCOUNTMEDIALINK.AUTHORID) as NAME,
                    cast(LEDGERACCOUNTMEDIALINK.ID as nvarchar(36)) + ':1' as RSSID,
                    cast(0 as bit) as ANNOTATIONEXISTS
                from
                    dbo.LEDGERACCOUNTMEDIALINK
                    inner join dbo.LEDGERACCOUNTMEDIALINKTYPECODE    on LEDGERACCOUNTMEDIALINKTYPECODE.ID = LEDGERACCOUNTMEDIALINK.LEDGERACCOUNTMEDIALINKTYPECODEID    
                where 
                    LEDGERACCOUNTMEDIALINK.LEDGERACCOUNTID = @LEDGERACCOUNTID and
                    (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                    (@TITLE = '' or LEDGERACCOUNTMEDIALINK.TITLE like @TITLE + '%')        
                union all    
                select    
                    LEDGERACCOUNTATTACHMENT.ID,
                    '2556cec7-06a6-4689-9c69-2ef941809281' as [VIEWFORMID],
                    2 as [DOCUMENTATIONTYPECODE],
                    null,
                    LEDGERACCOUNTATTACHMENT.FILENAME,
                    'FILE',
                    'Attachment' as [DOCUMENTATIONTYPE],
                    LEDGERACCOUNTATTACHMENT.DATEENTERED,
                    ACCOUNTATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                    LEDGERACCOUNTATTACHMENT.TITLE,
                    dbo.UFN_CONSTITUENT_BUILDNAME(LEDGERACCOUNTATTACHMENT.AUTHORID) as NAME,
                    cast(LEDGERACCOUNTATTACHMENT.ID as nvarchar(36)) + ':2' as RSSID,
                    cast(0 as bit) as ANNOTATIONEXISTS
                from
                    dbo.LEDGERACCOUNTATTACHMENT
                    inner join dbo.ACCOUNTATTACHMENTTYPECODE on ACCOUNTATTACHMENTTYPECODE.ID = LEDGERACCOUNTATTACHMENT.ACCOUNTATTACHMENTTYPECODEID    
                where 
                    LEDGERACCOUNTATTACHMENT.LEDGERACCOUNTID = @LEDGERACCOUNTID and
                    (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                    (@TITLE = '' or LEDGERACCOUNTATTACHMENT.TITLE like @TITLE + '%')        
                order by
                    DATEENTERED desc;