UFN_QUERY_TASKS

create function dbo.UFN_QUERY_TASKS()
returns @TASKS table
(
  ID uniqueidentifier,
  IMAGEKEY nvarchar(500),
  NAME nvarchar(100),
  DESCRIPTION nvarchar(max),
  FUNCTIONALAREAID uniqueidentifier,
  FUNCTIONALAREANAME nvarchar(100),
  TASKGROUP nvarchar(100),
  SEQUENCE int,
  ISCUSTOMIZABLE bit,
  TASKCONVERTEDFORWEBSHELL bit
)
with execute as caller
as
begin

  with xmlnamespaces
  (
    'bb_appfx_task' as bbtask,
    'bb_appfx_commontypes' as common
  )
  insert into @TASKS
  (
    ID,
    IMAGEKEY,
    NAME,
    DESCRIPTION,
    FUNCTIONALAREAID,
    FUNCTIONALAREANAME,
    TASKGROUP,
    SEQUENCE,
    ISCUSTOMIZABLE,
    TASKCONVERTEDFORWEBSHELL
  )
  select
    TASK.ID,
    TASK.TASKSPECXML.value('/bbtask:TaskSpec[1]/@ImageKey', 'nvarchar(500)'),
    TASK.NAME,
    TASK.DESCRIPTION,
    FUNCTIONALAREA.ID,
    FUNCTIONALAREA.NAME,
    TASK.TASKGROUP,
    TASK.SEQUENCE,
    coalesce(TASK.TASKSPECXML.value('/bbtask:TaskSpec[1]/@IsCustomizable', 'bit'), 1),
    case
      when TASK.TASKSPECXML.exist('(/bbtask:TaskSpec/common:ExecuteCLRAction)[1]') = 1 and
        TASK.TASKSPECXML.exist('(/bbtask:TaskSpec/common:ExecuteCLRAction/common:ScriptIdentifier)[1]') = 0 then 0
      else 1
    end
  from dbo.TASKCATALOG as TASK
    inner join dbo.FUNCTIONALAREACATALOG FUNCTIONALAREA on TASK.FUNCTIONALAREAID = FUNCTIONALAREA.ID
  where dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(TASK.TASKSPECXML.query('/*/common:InstalledProductList')) = 1
  order by
    TASK.SEQUENCE,
    TASK.NAME;

  return;

end