USP_DATALIST_STEWARDSHIPPLANSTEPDOCUMENTATION

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

    declare @temp table (
        ID uniqueidentifier,
        VIEWFORMID uniqueidentifier,
        DOCUMENTATIONTYPECODE tinyint,
        MEDIAURL nvarchar(2047),
        FILENAME nvarchar(255),
        FILEFIELDID nvarchar(100),
        DOCUMENTATIONTYPE nvarchar(50),
        DATEENTERED datetime,
        TYPE nvarchar(100),
        TITLE nvarchar(50),
        NAME nvarchar(155)
    )

    insert into @temp
        (ID, VIEWFORMID, DOCUMENTATIONTYPECODE, MEDIAURL, [FILENAME], FILEFIELDID, DOCUMENTATIONTYPE, DATEENTERED, [TYPE], TITLE, NAME)
        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
        from
            dbo.STEWARDSHIPNOTE
            left join dbo.STEWARDSHIPNOTETYPECODE on STEWARDSHIPNOTETYPECODE.ID = STEWARDSHIPNOTE.STEWARDSHIPNOTETYPECODEID
            left join dbo.APPUSER on APPUSER.ID = STEWARDSHIPNOTE.ENTEREDBYAPPUSERID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPNOTE.AUTHORID) NF
        where 
            STEWARDSHIPNOTE.STEPID = @STEPID and
            (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
            (@TITLE = '' or STEWARDSHIPNOTE.TITLE like @TITLE + '%')

    insert into @temp
        (ID, VIEWFORMID, DOCUMENTATIONTYPECODE, MEDIAURL, [FILENAME], FILEFIELDID, DOCUMENTATIONTYPE, DATEENTERED, [TYPE], TITLE, NAME)
        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
        from
            dbo.STEWARDSHIPMEDIALINK
            left join dbo.STEWARDSHIPMEDIALINKTYPECODE on STEWARDSHIPMEDIALINKTYPECODE.ID = STEWARDSHIPMEDIALINK.STEWARDSHIPMEDIALINKTYPECODEID
            left join dbo.APPUSER on APPUSER.ID = STEWARDSHIPMEDIALINK.ENTEREDBYAPPUSERID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPMEDIALINK.AUTHORID) NF
        where 
            STEWARDSHIPMEDIALINK.STEPID = @STEPID and
            (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
            (@TITLE = '' or STEWARDSHIPMEDIALINK.TITLE like @TITLE + '%')

    insert into @temp
        (ID, VIEWFORMID, DOCUMENTATIONTYPECODE, MEDIAURL, [FILENAME], FILEFIELDID, DOCUMENTATIONTYPE, DATEENTERED, [TYPE], TITLE, NAME)
        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
        from
            dbo.STEWARDSHIPATTACHMENT
            left join dbo.STEWARDSHIPATTACHMENTTYPECODE on STEWARDSHIPATTACHMENTTYPECODE.ID = STEWARDSHIPATTACHMENT.STEWARDSHIPATTACHMENTTYPECODEID
            left join dbo.APPUSER on APPUSER.ID = STEWARDSHIPATTACHMENT.ENTEREDBYAPPUSERID
            outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(STEWARDSHIPATTACHMENT.AUTHORID) NF
        where 
            STEWARDSHIPATTACHMENT.STEPID = @STEPID and
            (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
            (@TITLE = '' or STEWARDSHIPATTACHMENT.TITLE like @TITLE + '%')

    select *
    from @temp
    order by
        DOCUMENTATIONTYPE asc, DATEENTERED desc;

end