USP_DATALIST_SPONSORSHIPOPPORTUNITYDOCUMENTATION

      CREATE procedure dbo.USP_DATALIST_SPONSORSHIPOPPORTUNITYDOCUMENTATION
      (
      @SPONSORSHIPOPPORTUNITYID uniqueidentifier = null,
      @DOCUMENTATIONTYPECODE tinyint = null,
      @TITLE nvarchar(50) = '',
      @CURRENTAPPUSERID uniqueidentifier = null
      )
      as
          set nocount on;
          declare @ISSYSADMIN bit;
                    select @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);

                    select    
                        SPONSORSHIPOPPORTUNITYNOTE.ID,
                        '664ad089-af34-423f-9e53-33ada21f42ef' as [VIEWFORMID],
                        0 as [DOCUMENTATIONTYPECODE],
                        null as [MEDIAURL],
                        null as [FILENAME],
                        null as [FILEFIELDID],
                        'Note' as [DOCUMENTATIONTYPE],
                        SPONSORSHIPOPPORTUNITYNOTE.DATEENTERED,
                        SPROPPNOTETYPECODE.DESCRIPTION as [TYPE],
                        SPONSORSHIPOPPORTUNITYNOTE.TITLE,
                        CAST(SPONSORSHIPOPPORTUNITYNOTE.ID AS NVARCHAR(36)) + ':0' AS RSSID
                        --dbo.UFN_ANNOTATION_ANNOTATIONEXISTS('SPROPP note',SPONSORSHIPOPPORTUNITYNOTE.ID) as ANNOTATIONEXISTS

                    from
                        dbo.SPONSORSHIPOPPORTUNITYNOTE
                    inner join
                        dbo.SPROPPNOTETYPECODE
                    on
                        SPROPPNOTETYPECODE.ID = SPONSORSHIPOPPORTUNITYNOTE.SPROPPNOTETYPECODEID
                    where 
                        SPONSORSHIPOPPORTUNITYNOTE.SPONSORSHIPOPPORTUNITYID = @SPONSORSHIPOPPORTUNITYID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 0) and
                        (@TITLE = '' or SPONSORSHIPOPPORTUNITYNOTE.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,SPROPPNOTETYPECODE.ID) = 1)

                    union all                    
                    select    
                        SPONSORSHIPOPPORTUNITYMEDIALINK.ID,
                        'bb9582c0-be03-4988-9874-0dd2cd8015a1' as [VIEWFORMID],
                        1 as [DOCUMENTATIONTYPECODE],
                        SPONSORSHIPOPPORTUNITYMEDIALINK.MEDIAURL,
                        null,
                        null,
                        'Media link' as [DOCUMENTATIONTYPE],
                        SPONSORSHIPOPPORTUNITYMEDIALINK.DATEENTERED,
                        SPROPPMEDIALINKTYPECODE.DESCRIPTION as [TYPE],
                        SPONSORSHIPOPPORTUNITYMEDIALINK.TITLE,
                        CAST(SPONSORSHIPOPPORTUNITYMEDIALINK.ID AS NVARCHAR(36)) + ':1'  AS RSSID
                        --cast (0 as bit) as ANNOTATIONEXISTS

                    from
                        dbo.SPONSORSHIPOPPORTUNITYMEDIALINK
                    inner join
                        dbo.SPROPPMEDIALINKTYPECODE
                    on
                        SPROPPMEDIALINKTYPECODE.ID = SPONSORSHIPOPPORTUNITYMEDIALINK.SPROPPMEDIALINKTYPECODEID
                    where 
                        SPONSORSHIPOPPORTUNITYMEDIALINK.SPONSORSHIPOPPORTUNITYID = @SPONSORSHIPOPPORTUNITYID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 1) and
                        (@TITLE = '' or SPONSORSHIPOPPORTUNITYMEDIALINK.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,SPROPPMEDIALINKTYPECODE.ID) = 1)
                    union all                
                    select    
                        SPONSORSHIPOPPORTUNITYATTACHMENT.ID,
                        '2f09c59b-3a6f-430a-b358-18226b7bfcbb' as [VIEWFORMID],
                        2 as [DOCUMENTATIONTYPECODE],
                        null,
                        SPONSORSHIPOPPORTUNITYATTACHMENT.FILENAME,
                        'FILE',
                        'Attachment' as [DOCUMENTATIONTYPE],
                        SPONSORSHIPOPPORTUNITYATTACHMENT.DATEENTERED,
                        SPROPPATTACHMENTTYPECODE.DESCRIPTION as [TYPE],
                        SPONSORSHIPOPPORTUNITYATTACHMENT.TITLE,
                        CAST(SPONSORSHIPOPPORTUNITYATTACHMENT.ID AS NVARCHAR(36)) + ':2'  AS RSSID
                        --cast (0 as bit) as ANNOTATIONEXISTS

                    from
                        dbo.SPONSORSHIPOPPORTUNITYATTACHMENT
                    inner join
                        dbo.SPROPPATTACHMENTTYPECODE
                    on
                        SPROPPATTACHMENTTYPECODE.ID = SPONSORSHIPOPPORTUNITYATTACHMENT.SPROPPATTACHMENTTYPECODEID
                    where 
                        SPONSORSHIPOPPORTUNITYATTACHMENT.SPONSORSHIPOPPORTUNITYID = @SPONSORSHIPOPPORTUNITYID and
                        (@DOCUMENTATIONTYPECODE is null or @DOCUMENTATIONTYPECODE = 2) and
                        (@TITLE = '' or SPONSORSHIPOPPORTUNITYATTACHMENT.TITLE like @TITLE + '%') and
                        (@ISSYSADMIN = 1 or dbo.UFN_SECURITY_APPUSER_GRANTED_CODETABLEENTRY(@CURRENTAPPUSERID,SPROPPATTACHMENTTYPECODE.ID) = 1)
                    order by
                        DATEENTERED desc;