USP_DATALIST_NAMINGOPPORTUNITYDOCUMENTATION

This datalist returns all documentation for a naming opportunity.
Parameters

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

                    select    
                        NAMINGOPPORTUNITYNOTE.ID,
                        '4a5d4441-08bd-4a81-87b1-96b28cfed13e' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        NAMINGOPPORTUNITYNOTE.DATEENTERED,
                        NAMINGOPPNOTETYPECODE.DESCRIPTION as [TYPE],
                        NAMINGOPPORTUNITYNOTE.TITLE,
                        NF.NAME
                    from
                        dbo.NAMINGOPPORTUNITYNOTE
                    inner join
                        dbo.NAMINGOPPNOTETYPECODE
                    on
                        NAMINGOPPNOTETYPECODE.ID = NAMINGOPPORTUNITYNOTE.NAMINGOPPORTUNITYNOTETYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(NAMINGOPPORTUNITYNOTE.AUTHORID) NF
                    where 
                        NAMINGOPPORTUNITYNOTE.NAMINGOPPORTUNITYID = @NAMINGOPPORTUNITYID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                        (@TITLE = '' or NAMINGOPPORTUNITYNOTE.TITLE like @TITLE + '%')

                    union all

                    select    
                        NAMINGOPPORTUNITYMEDIALINK.ID,
                        'b73a297c-32c0-43cb-81bf-4ea13698ae04' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        NAMINGOPPORTUNITYMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        NAMINGOPPORTUNITYMEDIALINK.DATEENTERED,
                        NAMINGOPPMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                        NAMINGOPPORTUNITYMEDIALINK.TITLE,
                        NF.NAME
                    from
                        dbo.NAMINGOPPORTUNITYMEDIALINK
                    inner join
                        dbo.NAMINGOPPMEDIALINKTYPECODE
                    on
                        NAMINGOPPMEDIALINKTYPECODE.ID = NAMINGOPPORTUNITYMEDIALINK.NAMINGOPPORTUNITYMEDIALINKTYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(NAMINGOPPORTUNITYMEDIALINK.AUTHORID) NF
                    where 
                        NAMINGOPPORTUNITYMEDIALINK.NAMINGOPPORTUNITYID = @NAMINGOPPORTUNITYID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                        (@TITLE = '' or NAMINGOPPORTUNITYMEDIALINK.TITLE like @TITLE + '%')

                    union all

                    select    
                        NAMINGOPPORTUNITYATTACHMENT.ID,
                        '64E7402E-6AD8-4fa6-9BF0-62E48EDE0909' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        NAMINGOPPORTUNITYATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        NAMINGOPPORTUNITYATTACHMENT.DATEENTERED,
                        NAMINGOPPATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                        NAMINGOPPORTUNITYATTACHMENT.TITLE,
                        NF.NAME
                    from
                        dbo.NAMINGOPPORTUNITYATTACHMENT
                    inner join
                        dbo.NAMINGOPPATTACHMENTTYPECODE
                    on
                        NAMINGOPPATTACHMENTTYPECODE.ID = NAMINGOPPORTUNITYATTACHMENT.NAMINGOPPORTUNITYATTACHMENTTYPECODEID
                    outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(NAMINGOPPORTUNITYATTACHMENT.AUTHORID) NF
                    where 
                        NAMINGOPPORTUNITYATTACHMENT.NAMINGOPPORTUNITYID = @NAMINGOPPORTUNITYID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                        (@TITLE = '' or NAMINGOPPORTUNITYATTACHMENT.TITLE like @TITLE + '%')

                    order by
                        DATEENTERED desc;