UFN_TASK_FEATURES
Returns a table of features used by the given task
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @TASKID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_TASK_FEATURES
(
@TASKID uniqueidentifier
)
returns @TABLE table
(
ITEMTYPE integer,
ITEMID uniqueidentifier,
ITEMNAME nvarchar(500),
ITEMIMAGEKEY nvarchar(500),
INSTALLED bit
)
with execute as caller
as
begin
-- build a temp table for the results
declare @t table
(
ITEMTYPE integer,
ITEMID uniqueidentifier,
ITEMNAME nvarchar(500),
ITEMIMAGEKEY nvarchar(500),
INSTALLED bit
);
declare @taskActionType int;
declare @recordOperationId uniqueidentifier;
declare @addDataFormInstanceId uniqueidentifier;
declare @dataFormInstanceId uniqueidentifier;
declare @pageId uniqueidentifier;
declare @searchListId uniqueidentifier;
declare @postActionPageId uniqueidentifier;
declare @reportId uniqueidentifier;
declare @businessProcessId uniqueidentifier;
declare @businessProcessPageId uniqueidentifier;
declare @businessProcessPreprocessEditFormId uniqueidentifier;
declare @queryId uniqueidentifier;
declare @queryType nvarchar(20);
declare @browseQueryResultsPageId uniqueidentifier;
declare @runBusinessProcessPageId uniqueidentifier;
with xmlnamespaces ('bb_appfx_task' as tns, 'bb_appfx_commontypes' as common)
select
@taskActionType =
case
when T.TASKSPECXML.exist('tns:TaskSpec/common:ExecuteCLRAction') = 1 then 0
when T.TASKSPECXML.exist('tns:TaskSpec/common:ExecuteRecordOperation') = 1 then 1
when T.TASKSPECXML.exist('tns:TaskSpec/common:ShowAddDataForm') = 1 then 2
when T.TASKSPECXML.exist('tns:TaskSpec/common:ShowDataForm') = 1 then 3
when T.TASKSPECXML.exist('tns:TaskSpec/common:ShowPage') = 1 then 4
when T.TASKSPECXML.exist('tns:TaskSpec/common:ShowReport') = 1 then 5
when T.TASKSPECXML.exist('tns:TaskSpec/common:StartBusinessProcess') = 1 then 6
when T.TASKSPECXML.exist('tns:TaskSpec/common:BrowseQueryResults') = 1 then 8
when T.TASKSPECXML.exist('tns:TaskSpec/common:RunBusinessProcess') = 1 then 9
end,
@recordOperationId = T.TASKSPECXML.value('(tns:TaskSpec/common:ExecuteRecordOperation/@recordOperationId)[1]', 'uniqueidentifier'),
@addDataFormInstanceId = T.TASKSPECXML.value('(tns:TaskSpec/common:ShowAddDataForm/@DataFormID)[1]', 'uniqueidentifier'),
@dataFormInstanceId = T.TASKSPECXML.value('(tns:TaskSpec/common:ShowDataForm/@DataFormID)[1]', 'uniqueidentifier'),
@pageId = T.TASKSPECXML.value('(tns:TaskSpec/common:ShowPage/@PageID)[1]', 'uniqueidentifier'),
@searchListId = T.TASKSPECXML.value('(//common:SearchListReturnValue/@SearchListID)[1]', 'uniqueidentifier'),
@postActionPageId = T.TASKSPECXML.value('(//common:PostActionEvent/common:GoToPage/common:SpecificPage/@PageID)[1]', 'uniqueidentifier'),
@reportId = T.TASKSPECXML.value('(tns:TaskSpec/common:ShowReport/@ReportID)[1]', 'uniqueidentifier'),
@businessProcessId = T.TASKSPECXML.value('(tns:TaskSpec/common:StartBusinessProcess/@BusinessProcessID)[1]', 'uniqueidentifier'),
@businessProcessPageId = T.TASKSPECXML.value('(tns:TaskSpec/common:StartBusinessProcess/@StatusPageID)[1]', 'uniqueidentifier'),
@businessProcessPreprocessEditFormId = T.TASKSPECXML.value('(tns:TaskSpec/common:StartBusinessProcess/@PreprocessEditFormID)[1]', 'uniqueidentifier'),
@queryType =
case T.TASKSPECXML.exist('tns:TaskSpec/common:BrowseQueryResults')
when 1 then
coalesce(T.TASKSPECXML.value('(tns:TaskSpec/common:BrowseQueryResults/@QueryType)[1]', 'nvarchar(20)'), 'AdhocQuery')
when 0 then null
end,
@queryId = T.TASKSPECXML.value('(tns:TaskSpec/common:BrowseQueryResults/@QueryID)[1]', 'uniqueidentifier'),
@browseQueryResultsPageId = T.TASKSPECXML.value('(tns:TaskSpec/common:BrowseQueryResults/@PageID)[1]', 'uniqueidentifier'),
@runBusinessProcessPageId = T.TASKSPECXML.value('(tns:TaskSpec/common:RunBusinessProcess/@PageID)[1]', 'uniqueidentifier')
from dbo.TASKCATALOG as T
where T.ID = @TASKID;
-- ExecuteRecordOperation
if @recordOperationId is not null
with xmlnamespaces ('bb_appfx_recordoperation' as tns, 'bb_appfx_commontypes' as common)
insert into @t
select 23,
R.ID,
R.UINAME,
'res:recordoperationspec',
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(R.RECORDOPERATIONSPECXML.query('/*/common:InstalledProductList'))
from dbo.RECORDOPERATIONCATALOG as R
where R.ID = @recordOperationId;
-- ShowAddDataForm
if @addDataFormInstanceId is not null
with xmlnamespaces ('bb_appfx_adddataformtemplate' as tns, 'bb_appfx_commontypes' as common)
insert into @t
select 0,
INSTANCE.ID,
INSTANCE.UINAME,
'res:dataform_large',
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(TEMPLATE.TEMPLATESPECXML.query('/*/common:InstalledProductList'))
from dbo.DATAFORMINSTANCECATALOG as INSTANCE
inner join dbo.DATAFORMTEMPLATECATALOG as TEMPLATE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
where INSTANCE.ID = @addDataFormInstanceId;
-- ShowDataForm
if @dataFormInstanceId is not null
with xmlnamespaces ('bb_appfx_editdataformtemplate' as editform, 'bb_appfx_viewdataformtemplate' as viewform, 'bb_appfx_commontypes' as common)
insert into @t
select
case
when TEMPLATE.MODE = 0 then 19
when TEMPLATE.MODE = 1 then 3
end,
INSTANCE.ID,
INSTANCE.UINAME,
'res:dataform_large',
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(TEMPLATE.TEMPLATESPECXML.query('/*/common:InstalledProductList'))
from dbo.DATAFORMINSTANCECATALOG as INSTANCE
inner join dbo.DATAFORMTEMPLATECATALOG as TEMPLATE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
where INSTANCE.ID = @dataFormInstanceId;
-- ShowPage
if @pageId is not null
with xmlnamespaces ('bb_appfx_pagedefinition' as tns, 'bb_appfx_commontypes' as common)
insert into @t
select 6,
P.ID,
P.UINAME,
case
when P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/tns:PageHeader/@ImageKey)[1]', 'nvarchar(200)') is null then 'res:page_definition_large'
when left(ltrim(P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/tns:PageHeader/@ImageKey)[1]', 'nvarchar(200)')), 1) = '=' then 'res:page_definition_large'
else P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/tns:PageHeader/@ImageKey)[1]', 'nvarchar(200)')
end,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(P.PAGEDEFINITIONSPEC.query('/*/common:InstalledProductList'))
from dbo.PAGEDEFINITIONCATALOG as P
where P.ID = @pageId;
-- ShowReport
if @reportId is not null
with xmlnamespaces ('bb_appfx_report' as tns, 'bb_appfx_commontypes' as common)
insert into @t
select 9,
R.ID,
R.UINAME,
'res:reportspec',
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(R.REPORTSPECXML.query('/*/common:InstalledProductList'))
from dbo.REPORTCATALOG as R
where R.ID = @reportId;
-- StartBusinessProcess
if @businessProcessId is not null
with xmlnamespaces ('bb_appfx_businessprocess' as tns, 'bb_appfx_commontypes' as common)
insert into @t
select 22,
B.ID,
B.NAME,
'res:businessprocessspec',
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(B.BUSINESSPROCESSSPECXML.query('/*/common:InstalledProductList'))
from dbo.BUSINESSPROCESSCATALOG as B
where B.ID = @businessProcessId;
if @businessProcessPageId is not null
with xmlnamespaces ('bb_appfx_pagedefinition' as tns, 'bb_appfx_commontypes' as common)
insert into @t
select 6,
P.ID,
P.UINAME,
case
when P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/tns:PageHeader/@ImageKey)[1]', 'nvarchar(200)') is null then 'res:page_definition_large'
when left(ltrim(P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/tns:PageHeader/@ImageKey)[1]', 'nvarchar(200)')), 1) = '=' then 'res:page_definition_large'
else P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/tns:PageHeader/@ImageKey)[1]', 'nvarchar(200)')
end,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(P.PAGEDEFINITIONSPEC.query('/*/common:InstalledProductList'))
from dbo.PAGEDEFINITIONCATALOG as P
where P.ID = @businessProcessPageId;
if @businessProcessPreprocessEditFormId is not null
with xmlnamespaces ('bb_appfx_editdataformtemplate' as editform, 'bb_appfx_commontypes' as common)
insert into @t
select
3,
INSTANCE.ID,
INSTANCE.UINAME,
'res:dataform_large',
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(TEMPLATE.TEMPLATESPECXML.query('/*/common:InstalledProductList'))
from dbo.DATAFORMINSTANCECATALOG as INSTANCE
inner join dbo.DATAFORMTEMPLATECATALOG as TEMPLATE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
where INSTANCE.ID = @businessProcessPreprocessEditFormId;
-- BrowseQueryResults
if (@queryType = 'AdhocQuery') and (@queryId is not null)
with xmlnamespaces ('bb_appfx_queryview' as editform, 'bb_appfx_commontypes' as common)
insert into @t
select
7,
Q.ID,
Q.DISPLAYNAME,
'RES:Query_Large',
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(Q.QUERYVIEWSPEC.query('/*/common:InstalledProductList'))
from dbo.QUERYVIEWCATALOG as Q
where Q.ID = @queryId;
if @browseQueryResultsPageId is not null
with xmlnamespaces ('bb_appfx_pagedefinition' as tns, 'bb_appfx_commontypes' as common)
insert into @t
select 6,
P.ID,
P.UINAME,
case
when P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/tns:PageHeader/@ImageKey)[1]', 'nvarchar(200)') is null then 'res:page_definition_large'
when left(ltrim(P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/tns:PageHeader/@ImageKey)[1]', 'nvarchar(200)')), 1) = '=' then 'res:page_definition_large'
else P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/tns:PageHeader/@ImageKey)[1]', 'nvarchar(200)')
end,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(P.PAGEDEFINITIONSPEC.query('/*/common:InstalledProductList'))
from dbo.PAGEDEFINITIONCATALOG as P
where P.ID = @browseQueryResultsPageId;
-- RunBusinessProcess
if @runBusinessProcessPageId is not null
with xmlnamespaces ('bb_appfx_pagedefinition' as tns, 'bb_appfx_commontypes' as common)
insert into @t
select 6,
P.ID,
P.UINAME,
case
when P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/tns:PageHeader/@ImageKey)[1]', 'nvarchar(200)') is null then 'res:page_definition_large'
when left(ltrim(P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/tns:PageHeader/@ImageKey)[1]', 'nvarchar(200)')), 1) = '=' then 'res:page_definition_large'
else P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/tns:PageHeader/@ImageKey)[1]', 'nvarchar(200)')
end,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(P.PAGEDEFINITIONSPEC.query('/*/common:InstalledProductList'))
from dbo.PAGEDEFINITIONCATALOG as P
where P.ID = @runBusinessProcessPageId;
-- check for any SearchListReturnValue in the task context
if @searchListId is not null
with xmlnamespaces ('bb_appfx_searchlist' as tns, 'bb_appfx_commontypes' as common)
insert into @t
select 10,
S.ID,
S.UINAME,
case
when S.SEARCHLISTSPEC.value('(tns:SearchListSpec/@ImageKey)[1]', 'nvarchar(200)') is null then 'res:search'
else S.SEARCHLISTSPEC.value('(tns:SearchListSpec/@ImageKey)[1]', 'nvarchar(200)')
end,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(S.SEARCHLISTSPEC.query('/*/common:InstalledProductList'))
from dbo.SEARCHLISTCATALOG as S
where S.ID = @searchListId;
-- check for post-action specific pages
if @postActionPageId is not null
with xmlnamespaces ('bb_appfx_pagedefinition' as tns, 'bb_appfx_commontypes' as common)
insert into @t
select 6,
P.ID,
P.UINAME,
case
when P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/tns:PageHeader/@ImageKey)[1]', 'nvarchar(200)') is null then 'res:page_definition_large'
when left(ltrim(P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/tns:PageHeader/@ImageKey)[1]', 'nvarchar(200)')), 1) = '=' then 'res:page_definition_large'
else P.PAGEDEFINITIONSPEC.value('(tns:PageDefinitionSpec/tns:PageHeader/@ImageKey)[1]', 'nvarchar(200)')
end,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(P.PAGEDEFINITIONSPEC.query('/*/common:InstalledProductList'))
from dbo.PAGEDEFINITIONCATALOG as P
where P.ID = @postActionPageId;
-- add any search list Add forms
with xmlnamespaces ('bb_appfx_adddataformtemplate' as tns, 'bb_appfx_commontypes' as common)
insert into @t
select 0,
INSTANCE.ID,
INSTANCE.UINAME,
'res:dataform_large',
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(TEMPLATE.TEMPLATESPECXML.query('/*/common:InstalledProductList'))
from dbo.TASKCATALOG as T
cross apply T.TASKSPECXML.nodes('//common:AddDataForms/common:AddDataForm') as list(addforms)
inner join dbo.DATAFORMINSTANCECATALOG as INSTANCE on INSTANCE.ID = list.addforms.value('@ID', 'uniqueidentifier')
inner join dbo.DATAFORMTEMPLATECATALOG as TEMPLATE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
where T.ID = @TASKID;
-- now build the results
insert into @TABLE
select
ITEMTYPE,
ITEMID,
ITEMNAME,
ITEMIMAGEKEY,
INSTALLED
from @t;
return;
end