USP_DATALIST_CONSTITUENTSTEWARDSHIPPLANDOCUMENTATION

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

                    select    
                        STEWARDSHIPNOTE.ID,
                        '1C0EF10D-2CE8-4F9C-A3E5-2D3B6BDB59AB' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        STEWARDSHIPNOTE.DATEENTERED,
                        STEWARDSHIPNOTETYPECODE.DESCRIPTION as [TYPE],
                        STEWARDSHIPNOTE.TITLE,
                        NF.NAME,
                        STEWARDSHIPNOTE.PLANID as STEWARDSHIPPLANID
                    from 
                        dbo.STEWARDSHIPNOTE
                        inner join dbo.STEWARDSHIPNOTETYPECODE on STEWARDSHIPNOTETYPECODE.ID = STEWARDSHIPNOTE.STEWARDSHIPNOTETYPECODEID
                        inner join dbo.STEWARDSHIPPLAN on STEWARDSHIPPLAN.ID = STEWARDSHIPNOTE.PLANID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPNOTE.AUTHORID) NF
                        left outer join dbo.STEWARDSHIPPLANSTEP on STEWARDSHIPNOTE.STEPID = STEWARDSHIPPLANSTEP.ID
                    where 
                        STEWARDSHIPPLAN.CONSTITUENTID = @CONSTITUENTID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                        (@TITLE = '' or STEWARDSHIPNOTE.TITLE like @TITLE + '%')

                    union all

                    select    
                        STEWARDSHIPMEDIALINK.ID,
                        'A8B5DA4E-B47D-4A93-A756-E4BDF47E738B' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        STEWARDSHIPMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        STEWARDSHIPMEDIALINK.DATEENTERED,
                        STEWARDSHIPMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                        STEWARDSHIPMEDIALINK.TITLE,
                        NF.NAME,
                        STEWARDSHIPMEDIALINK.PLANID as STEWARDSHIPPLANID
                    from 
                        dbo.STEWARDSHIPMEDIALINK
                        inner join dbo.STEWARDSHIPMEDIALINKTYPECODE on STEWARDSHIPMEDIALINKTYPECODE.ID = STEWARDSHIPMEDIALINK.STEWARDSHIPMEDIALINKTYPECODEID
                        inner join dbo.STEWARDSHIPPLAN on STEWARDSHIPPLAN.ID = STEWARDSHIPMEDIALINK.PLANID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPMEDIALINK.AUTHORID) NF
                        left outer join dbo.STEWARDSHIPPLANSTEP on STEWARDSHIPMEDIALINK.STEPID = STEWARDSHIPPLANSTEP.ID
                    where 
                        STEWARDSHIPPLAN.CONSTITUENTID = @CONSTITUENTID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                        (@TITLE = '' or STEWARDSHIPMEDIALINK.TITLE like @TITLE + '%')

                    union all

                    select    
                        STEWARDSHIPATTACHMENT.ID,
                        'D486D697-42B3-49BC-8F45-EB2DB2069DE3' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        STEWARDSHIPATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        STEWARDSHIPATTACHMENT.DATEENTERED,
                        STEWARDSHIPATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                        STEWARDSHIPATTACHMENT.TITLE,
                        NF.NAME,
                        STEWARDSHIPATTACHMENT.PLANID as STEWARDSHIPPLANID
                    from 
                        dbo.STEWARDSHIPATTACHMENT
                        inner join dbo.STEWARDSHIPATTACHMENTTYPECODE on STEWARDSHIPATTACHMENTTYPECODE.ID = STEWARDSHIPATTACHMENT.STEWARDSHIPATTACHMENTTYPECODEID
                        inner join dbo.STEWARDSHIPPLAN on STEWARDSHIPPLAN.ID = STEWARDSHIPATTACHMENT.PLANID
                        outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPATTACHMENT.AUTHORID) NF
                        left outer join dbo.STEWARDSHIPPLANSTEP on STEWARDSHIPATTACHMENT.STEPID = STEWARDSHIPPLANSTEP.ID
                    where 
                        STEWARDSHIPPLAN.CONSTITUENTID = @CONSTITUENTID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                        (@TITLE = '' or STEWARDSHIPATTACHMENT.TITLE like @TITLE + '%')

                    order by
                        DATEENTERED desc, DOCUMENTATIONTYPE;