USP_DATALIST_TASKS

CREATE procedure dbo.USP_DATALIST_TASKS
as
    set nocount on;

with xmlnamespaces (  
'bb_appfx_task' as bbtask,
'bb_appfx_commontypes' as common)
select TASK.TASKSPECXML.value('/bbtask:TaskSpec[1]/@ImageKey', 'nvarchar(500)'),
           TASK.ID,
           TASK.[NAME],
           TASK.DESCRIPTION,
           (select [NAME] from dbo.FUNCTIONALAREACATALOG where ID = TASK.FUNCTIONALAREAID) as 'Functional Area',
           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 TaskConvertedForWebShell
    from dbo.TASKCATALOG as TASK
    where 1 = dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(TASK.TASKSPECXML.query('/*/common:InstalledProductList'))

    order by TASK.SEQUENCE, TASK.[NAME]