UFN_MKTSEGMENT_GETSELECTIONS

CREATE function dbo.[UFN_MKTSEGMENT_GETSELECTIONS]
(
  @SEGMENTID uniqueidentifier,
  @CURRENTAPPUSERID uniqueidentifier
)
returns table
as
  return 
  (
    select 
      [MKTSEGMENTSELECTION].[ID],
      [MKTSEGMENTSELECTION].[SELECTIONID],
      [IDSETREGISTER].[NAME],
      [IDSETREGISTER].[DESCRIPTION],
      [RECORDTYPE].[NAME] as [RECORDTYPE],
      [RECORDTYPE].[ID] as [RECORDTYPEID],
      coalesce([ADHOCQUERY].[QUERYVIEWCATALOGID], [SMARTQUERYINSTANCE].[SMARTQUERYCATALOGID], [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]) as [QUERYVIEWID],
      isnull([ADHOCQUERY].[ID], [SMARTQUERYINSTANCE].[ID]) as [QUERYID],
      (case when [ADHOCQUERY].[ID] is not null then 1 else case when [SMARTQUERYINSTANCE].[ID] is not null then 2 else 0 end end) as [QUERYTYPE],
      convert(bit,
        case when [ADHOCQUERY].[QUERYVIEWCATALOGID] is not null and (select [ROOTOBJECT] from dbo.[QUERYVIEWCATALOG] where [ID] = [ADHOCQUERY].[QUERYVIEWCATALOGID]) = 1 
             then dbo.[UFN_SECURITY_APPUSER_CANACCESS_QUERYVIEW](@CURRENTAPPUSERID, [ADHOCQUERY].[QUERYVIEWCATALOGID])
             when [SMARTQUERYINSTANCE].[SMARTQUERYCATALOGID] is not null 
             then case when (select [ISSYSADMIN] from dbo.[APPUSER] where [ID] = @CURRENTAPPUSERID) = 1 
                       then 1
                       else case when exists(select top 1 1 from dbo.[UFN_SECURITY_GETGRANTEDSMARTQUERIESFORUSER](@CURRENTAPPUSERID) where [SMARTQUERYCATALOGID] = [SMARTQUERYINSTANCE].[SMARTQUERYCATALOGID]) 
                                 then 1
                                 else 0
                            end
                  end
             else 0
        end
      ) as [CANEDIT],
      convert(bit,
        case when [ADHOCQUERY].[ID] is not null
             then dbo.[UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, [ADHOCQUERY].[ID])
             when [SMARTQUERYINSTANCE].[ID] is not null
             then dbo.[UFN_SECURITY_APPUSER_GRANTED_SMARTQUERYINSTANCE_IN_SYSTEMROLE](@CURRENTAPPUSERID, [SMARTQUERYINSTANCE].[ID])
             else 1
        end
      ) as [USERGRANTEDRIGHTS]
    from dbo.[MKTSEGMENTSELECTION]
    inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTSELECTION].[SEGMENTID]
    inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [MKTSEGMENTSELECTION].[SELECTIONID]
    inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [IDSETREGISTER].[RECORDTYPEID]
    left join dbo.[IDSETREGISTERADHOCQUERY] on [IDSETREGISTERADHOCQUERY].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
    left join dbo.[ADHOCQUERY] on [ADHOCQUERY].[ID] = [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID]
    left join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] on [IDSETREGISTERSMARTQUERYINSTANCE].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
    left join dbo.[SMARTQUERYINSTANCE] on [SMARTQUERYINSTANCE].[ID] = [IDSETREGISTERSMARTQUERYINSTANCE].[SMARTQUERYINSTANCEID]
    left join dbo.[MKTSEGMENTLIST] on [MKTSEGMENTLIST].[ID] = [MKTSEGMENT].[CURRENTSEGMENTLISTID]
    where [MKTSEGMENTSELECTION].[SEGMENTID] = @SEGMENTID
  );