USP_DATALIST_DESIGNATIONLEVELDOCUMENTATION

This datalist returns all documentation for a designation level.
Parameters

Parameter	Parameter Type	Mode	Description
@DESIGNATIONLEVELID	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_DESIGNATIONLEVELDOCUMENTATION 
                (
                    @DESIGNATIONLEVELID uniqueidentifier = null,
                    @DOCUMENTATIONTYPECODE tinyint = null,
                    @TITLE nvarchar(50) = ''
                )
                as
                    set nocount on;

                    select    
                        DESIGNATIONLEVELNOTE.ID,
                        'C4EFEC6B-0FBF-4333-9EF6-2C6036AADA30' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        DESIGNATIONLEVELNOTE.DATEENTERED,
                        DESIGNATIONLEVELNOTETYPECODE.DESCRIPTION as [TYPE],
                        DESIGNATIONLEVELNOTE.TITLE,
                        NF.NAME
                    from
                        dbo.DESIGNATIONLEVELNOTE
                    inner join
                        dbo.DESIGNATIONLEVELNOTETYPECODE
                    on
                        DESIGNATIONLEVELNOTETYPECODE.ID = DESIGNATIONLEVELNOTE.DESIGNATIONLEVELNOTETYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(DESIGNATIONLEVELNOTE.AUTHORID) NF
                    where 
                        DESIGNATIONLEVELNOTE.DESIGNATIONLEVELID = @DESIGNATIONLEVELID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                        (@TITLE = '' or DESIGNATIONLEVELNOTE.TITLE like @TITLE + '%')

                    union all

                    select    
                        DESIGNATIONLEVELMEDIALINK.ID,
                        'A8515FAE-4FDA-4D8F-95C1-4E69D5683967' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        DESIGNATIONLEVELMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        DESIGNATIONLEVELMEDIALINK.DATEENTERED,
                        DESIGNATIONLEVELMEDLINTYPECODE.DESCRIPTION as [TYPE],
                        DESIGNATIONLEVELMEDIALINK.TITLE,
                        NF.NAME
                    from
                        dbo.DESIGNATIONLEVELMEDIALINK
                    inner join
                        dbo.DESIGNATIONLEVELMEDLINTYPECODE
                    on
                        DESIGNATIONLEVELMEDLINTYPECODE.ID = DESIGNATIONLEVELMEDIALINK.DESIGNATIONLEVELMEDLINTYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(DESIGNATIONLEVELMEDIALINK.AUTHORID) NF
                    where 
                        DESIGNATIONLEVELMEDIALINK.DESIGNATIONLEVELID = @DESIGNATIONLEVELID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                        (@TITLE = '' or DESIGNATIONLEVELMEDIALINK.TITLE like @TITLE + '%')

                    union all

                    select    
                        DESIGNATIONLEVELATTACHMENT.ID,
                        'F7953B5B-3E48-4471-B5B2-C3956AFFAF2C' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        DESIGNATIONLEVELATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        DESIGNATIONLEVELATTACHMENT.DATEENTERED,
                        DESIGNATIONLEVELATTACHTYPECODE.DESCRIPTION as [TYPE],
                        DESIGNATIONLEVELATTACHMENT.TITLE,
                        NF.NAME
                    from
                        dbo.DESIGNATIONLEVELATTACHMENT
                    inner join
                        dbo.DESIGNATIONLEVELATTACHTYPECODE
                    on
                        DESIGNATIONLEVELATTACHTYPECODE.ID = DESIGNATIONLEVELATTACHMENT.DESIGNATIONLEVELATTACHTYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(DESIGNATIONLEVELATTACHMENT.AUTHORID) NF
                    where 
                        DESIGNATIONLEVELATTACHMENT.DESIGNATIONLEVELID = @DESIGNATIONLEVELID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                        (@TITLE = '' or DESIGNATIONLEVELATTACHMENT.TITLE like @TITLE + '%')

                    order by
                        DATEENTERED desc;