USP_DATALIST_DONORCHALLENGEDOCUMENTATION

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

                    select    
                        DONORCHALLENGENOTE.ID,
                        '8fbb08ea-753b-4f8f-aed4-cbd55bea6450' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        DONORCHALLENGENOTE.DATEENTERED,
                        DONORCHALLENGENOTETYPECODE.DESCRIPTION as [TYPE],
                        DONORCHALLENGENOTE.TITLE,
                        NF.NAME
                    from
                        dbo.DONORCHALLENGENOTE
                    inner join
                        dbo.DONORCHALLENGENOTETYPECODE
                    on
                        DONORCHALLENGENOTETYPECODE.ID = DONORCHALLENGENOTE.DONORCHALLENGENOTETYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(DONORCHALLENGENOTE.AUTHORID) NF
                    where 
                        DONORCHALLENGENOTE.DONORCHALLENGEID = @DONORCHALLENGEID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                        (@TITLE = '' or DONORCHALLENGENOTE.TITLE like @TITLE + '%')

                    union all

                    select    
                        DONORCHALLENGEMEDIALINK.ID,
                        '6dc9aa14-9ee5-46dc-81e2-040fc6916dce' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        DONORCHALLENGEMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        DONORCHALLENGEMEDIALINK.DATEENTERED,
                        DONORCHALLENGEMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                        DONORCHALLENGEMEDIALINK.TITLE,
                        NF.NAME
                    from
                        dbo.DONORCHALLENGEMEDIALINK
                    inner join
                        dbo.DONORCHALLENGEMEDIALINKTYPECODE
                    on
                        DONORCHALLENGEMEDIALINKTYPECODE.ID = DONORCHALLENGEMEDIALINK.DONORCHALLENGEMEDIALINKTYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(DONORCHALLENGEMEDIALINK.AUTHORID) NF
                    where 
                        DONORCHALLENGEMEDIALINK.DONORCHALLENGEID = @DONORCHALLENGEID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                        (@TITLE = '' or DONORCHALLENGEMEDIALINK.TITLE like @TITLE + '%')

                    union all

                    select    
                        DONORCHALLENGEATTACHMENT.ID,
                        '7966ebf8-9145-423d-b8fe-cd9dea4c02b8' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        DONORCHALLENGEATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        DONORCHALLENGEATTACHMENT.DATEENTERED,
                        DONORCHALLENGEATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                        DONORCHALLENGEATTACHMENT.TITLE,
                        NF.NAME
                    from
                        dbo.DONORCHALLENGEATTACHMENT
                    inner join
                        dbo.DONORCHALLENGEATTACHMENTTYPECODE
                    on
                        DONORCHALLENGEATTACHMENTTYPECODE.ID = DONORCHALLENGEATTACHMENT.DONORCHALLENGEATTACHMENTTYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(DONORCHALLENGEATTACHMENT.AUTHORID) NF
                    where 
                        DONORCHALLENGEATTACHMENT.DONORCHALLENGEID = @DONORCHALLENGEID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                        (@TITLE = '' or DONORCHALLENGEATTACHMENT.TITLE like @TITLE + '%')

                    order by
                        DATEENTERED desc;