USP_DATALIST_PROSPECTPLANDOCUMENTATION

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

                    select    
                        PROSPECTPLANNOTE.ID,
                        '6CED157B-7505-4015-B6F4-3017CD5B0752' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        PROSPECTPLANNOTE.DATEENTERED,
                        PROSPECTPLANNOTETYPECODE.DESCRIPTION as [TYPE],
                        PROSPECTPLANNOTE.TITLE,
                        NF.NAME
                    from
                        dbo.PROSPECTPLANNOTE
                    inner join
                        dbo.PROSPECTPLANNOTETYPECODE
                    on
                        PROSPECTPLANNOTETYPECODE.ID = PROSPECTPLANNOTE.PROSPECTPLANNOTETYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLANNOTE.AUTHORID) NF
                    where 
                        PROSPECTPLANNOTE.PROSPECTPLANID = @PROSPECTPLANID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                        (@TITLE = '' or PROSPECTPLANNOTE.TITLE like @TITLE + '%')

                    union all

                    select    
                        PROSPECTPLANMEDIALINK.ID,
                        '7E2F498A-F189-4F76-ACB7-F06D76A61059' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        PROSPECTPLANMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        PROSPECTPLANMEDIALINK.DATEENTERED,
                        PROSPECTPLANMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                        PROSPECTPLANMEDIALINK.TITLE,
                        NF.NAME
                    from
                        dbo.PROSPECTPLANMEDIALINK
                    inner join
                        dbo.PROSPECTPLANMEDIALINKTYPECODE
                    on
                        PROSPECTPLANMEDIALINKTYPECODE.ID = PROSPECTPLANMEDIALINK.PROSPECTPLANMEDIALINKTYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLANMEDIALINK.AUTHORID) NF
                    where 
                        PROSPECTPLANMEDIALINK.PROSPECTPLANID = @PROSPECTPLANID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                        (@TITLE = '' or PROSPECTPLANMEDIALINK.TITLE like @TITLE + '%')

                    union all

                    select    
                        PROSPECTPLANATTACHMENT.ID,
                        '7786E3F5-4D54-4EDB-BF0E-2C8F57350800' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        PROSPECTPLANATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        PROSPECTPLANATTACHMENT.DATEENTERED,
                        PROSPECTPLANATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                        PROSPECTPLANATTACHMENT.TITLE,
                        NF.NAME
                    from
                        dbo.PROSPECTPLANATTACHMENT
                    inner join
                        dbo.PROSPECTPLANATTACHMENTTYPECODE
                    on
                        PROSPECTPLANATTACHMENTTYPECODE.ID = PROSPECTPLANATTACHMENT.PROSPECTPLANATTACHMENTTYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PROSPECTPLANATTACHMENT.AUTHORID) NF
                    where 
                        PROSPECTPLANATTACHMENT.PROSPECTPLANID = @PROSPECTPLANID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                        (@TITLE = '' or PROSPECTPLANATTACHMENT.TITLE like @TITLE + '%')

                    order by
                        DATEENTERED desc;