USP_DATALIST_APPEALDOCUMENTATION

This datalist returns all documentation for a appeal.
Parameters

Parameter	Parameter Type	Mode	Description
@APPEALID	uniqueidentifier	IN	Input parameter indicating the context ID for the data list.
@DOCUMENTATIONTYPECODE	tinyint	IN	Documentation type
@TITLE	nvarchar(50)	IN	Title
Definition

Copy


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

                    select    
                        APPEALNOTE.ID,
                        'DC0860D6-3B27-424E-AEE9-1AA8B7272E15' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        APPEALNOTE.DATEENTERED,
                        APPEALNOTETYPECODE.DESCRIPTION as [TYPE],
                        APPEALNOTE.TITLE,
                        NF.NAME
                    from
                        dbo.APPEALNOTE
                    inner join
                        dbo.APPEALNOTETYPECODE
                    on
                        APPEALNOTETYPECODE.ID = APPEALNOTE.APPEALNOTETYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(APPEALNOTE.AUTHORID) NF
                    where 
                        APPEALNOTE.APPEALID = @APPEALID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                        (@TITLE = '' or APPEALNOTE.TITLE like @TITLE + '%')

                    union all

                    select    
                        APPEALMEDIALINK.ID,
                        '13ED66F3-4F4F-40C9-B9F8-3200BF1B2615' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        APPEALMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        APPEALMEDIALINK.DATEENTERED,
                        APPEALMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                        APPEALMEDIALINK.TITLE,
                        NF.NAME
                    from
                        dbo.APPEALMEDIALINK
                    inner join
                        dbo.APPEALMEDIALINKTYPECODE
                    on
                        APPEALMEDIALINKTYPECODE.ID = APPEALMEDIALINK.APPEALMEDIALINKTYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(APPEALMEDIALINK.AUTHORID) NF
                    where 
                        APPEALMEDIALINK.APPEALID = @APPEALID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                        (@TITLE = '' or APPEALMEDIALINK.TITLE like @TITLE + '%')

                    union all

                    select    
                        APPEALATTACHMENT.ID,
                        '66E04066-5F4B-4910-ACF8-FFAC9F9D2D2D' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        APPEALATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        APPEALATTACHMENT.DATEENTERED,
                        APPEALATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                        APPEALATTACHMENT.TITLE,
                        NF.NAME
                    from
                        dbo.APPEALATTACHMENT
                    inner join
                        dbo.APPEALATTACHMENTTYPECODE
                    on
                        APPEALATTACHMENTTYPECODE.ID = APPEALATTACHMENT.APPEALATTACHMENTTYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(APPEALATTACHMENT.AUTHORID) NF
                    where 
                        APPEALATTACHMENT.APPEALID = @APPEALID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                        (@TITLE = '' or APPEALATTACHMENT.TITLE like @TITLE + '%')

                    order by
                        DATEENTERED desc;