USP_DATALIST_PLANNEDGIFTDOCUMENTATION

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

    select    
        PLANNEDGIFTNOTE.ID,
        '75A30151-C10A-4E92-A0E2-10A9578CEA0F' as [VIEWFORMID],
        0 as [DOCUMENTATIONTYPECODE],
        null as [MEDIAURL],
        null as [FILENAME],
        null as [FILEFIELDID],
        'Note' as [DOCUMENTATIONTYPE],
        PLANNEDGIFTNOTE.DATEENTERED,
        PLANNEDGIFTNOTETYPECODE.DESCRIPTION as [TYPE],
        PLANNEDGIFTNOTE.TITLE,
        NF.NAME,
        dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('Planned gift note',PLANNEDGIFTNOTE.ID) as ANNOTATIONEXISTS
    from
        dbo.PLANNEDGIFTNOTE
        left join dbo.PLANNEDGIFTNOTETYPECODE on PLANNEDGIFTNOTETYPECODE.ID = PLANNEDGIFTNOTE.PLANNEDGIFTNOTETYPECODEID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PLANNEDGIFTNOTE.AUTHORID) NF
    where 
        PLANNEDGIFTNOTE.PLANNEDGIFTID = @PLANNEDGIFTID and
        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
        (@TITLE = '' or PLANNEDGIFTNOTE.TITLE like @TITLE + '%')

    union all

    select    
        PLANNEDGIFTMEDIALINK.ID,
        '039D34F1-438F-4A46-A2AD-BE79B9D8DD78' as [VIEWFORMID],
        1 as [DOCUMENTATIONTYPECODE],
        PLANNEDGIFTMEDIALINK.MEDIAURL,
        null,
        null,
        'Media link' as [DOCUMENTATIONTYPE],
        PLANNEDGIFTMEDIALINK.DATEENTERED,
        PLANNEDGIFTMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
        PLANNEDGIFTMEDIALINK.TITLE,
        NF.NAME,
        cast (0 as bit) as ANNOTATIONEXISTS
    from
        dbo.PLANNEDGIFTMEDIALINK
        left join dbo.PLANNEDGIFTMEDIALINKTYPECODE on PLANNEDGIFTMEDIALINKTYPECODE.ID = PLANNEDGIFTMEDIALINK.PLANNEDGIFTMEDIALINKTYPECODEID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PLANNEDGIFTMEDIALINK.AUTHORID) NF
    where 
        PLANNEDGIFTMEDIALINK.PLANNEDGIFTID = @PLANNEDGIFTID and
        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
        (@TITLE = '' or PLANNEDGIFTMEDIALINK.TITLE like @TITLE + '%')

    union all

    select    
        PLANNEDGIFTATTACHMENT.ID,
        '70494575-E26C-45B4-B55B-60DADE88365D' as [VIEWFORMID],
        2 as [DOCUMENTATIONTYPECODE],
        null,
        PLANNEDGIFTATTACHMENT.FILENAME,
        'FILE',
        'Attachment' as [DOCUMENTATIONTYPE],
        PLANNEDGIFTATTACHMENT.DATEENTERED,
        PLANNEDGIFTATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
        PLANNEDGIFTATTACHMENT.TITLE,
        NF.NAME,
        cast (0 as bit) as ANNOTATIONEXISTS
    from
        dbo.PLANNEDGIFTATTACHMENT
        left join dbo.PLANNEDGIFTATTACHMENTTYPECODE on PLANNEDGIFTATTACHMENTTYPECODE.ID = PLANNEDGIFTATTACHMENT.PLANNEDGIFTATTACHMENTTYPECODEID
        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PLANNEDGIFTATTACHMENT.AUTHORID) NF
    where 
        PLANNEDGIFTATTACHMENT.PLANNEDGIFTID = @PLANNEDGIFTID and
        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
        (@TITLE = '' or PLANNEDGIFTATTACHMENT.TITLE like @TITLE + '%')

    order by
        DATEENTERED desc;