V_QUERY_TASK
Provides support for querying on task metadata from the application catalog.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| NAME | nvarchar(100) | Name | |
| DESCRIPTION | nvarchar(max) | Description | |
| AUTHOR | nvarchar(500) | yes | Author |
| TASKGROUP | nvarchar(100) | Task group | |
| SEQUENCE | int | Sequence | |
| IMAGEKEY | nvarchar(256) | yes | Image key |
| SMALLIMAGEKEY | nvarchar(256) | yes | Small image key |
| HELPKEY | nvarchar(200) | yes | Help key |
| FUNCTIONALAREA | nvarchar(100) | Functional area | |
| DISPLAYASACTION | int | yes | Display as action |
| APPENDSEPARATOR | int | yes | Append separator |
| VISIBLE | int | yes | Visible |
| ISCUSTOMIZABLE | int | yes | Is customizable |
| FOLDER | nvarchar(256) | yes | Folder |
| FOLDERIMAGEKEY | nvarchar(256) | yes | Folder image key |
| FOLDERTASKGROUP | nvarchar(256) | yes | Folder task group |
| FOLDERTASKGROUPIMAGEKEY | nvarchar(256) | yes | Folder task group image key |
| INSTALLEDPRODUCTSLIST | nvarchar(max) | yes | Installed products list |
| INSTALLED | bit | yes | Installed |
| TASKSPECXML | xml | Task spec xml | |
| RESOURCEFILE | nvarchar(max) | yes | Resource file |
| SHELLTASKFILTER | nvarchar(max) | yes | Shell task filter |
| TASKACTIONTYPE | varchar(24) | yes | Task action type |
| TASKACTIONCONTEXTTYPE | varchar(24) | Context type | |
| EXPRESSION | nvarchar(max) | yes | Expression |
| SEARCHLISTID | uniqueidentifier | yes | Search list record |
| SEARCHLISTNAME | nvarchar(60) | yes | Search list name |
| POSTACTIONEVENTTYPE | varchar(21) | Post action event type | |
| POSTACTIONFUNCTIONALAREANAME | nvarchar(100) | yes | Functional area name |
| POSTACTIONSPECIFICPAGEID | uniqueidentifier | yes | Specific page record |
| POSTACTIONSPECIFICPAGENAME | nvarchar(60) | yes | Specific page name |
| CLRACTIONASSEMBLYNAME | nvarchar(max) | yes | Assembly name |
| CLRACTIONCLASSNAME | nvarchar(max) | yes | Class name |
| SCRIPTIDENTIFIERURL | nvarchar(max) | yes | Script identifier Url |
| SCRIPTIDENTIFIEROBJECTNAME | nvarchar(max) | yes | Script identifier object name |
| RECORDOPERATIONID | uniqueidentifier | yes | Record operation record |
| RECORDOPERATIONNAME | nvarchar(60) | yes | Record operation name |
| RECORDOPERATIONSHOWPROMPT | int | yes | Show prompt |
| ADDDATAFORMID | uniqueidentifier | yes | Data form record |
| ADDDATAFORMNAME | nvarchar(60) | yes | Data form name |
| DATAFORMID | uniqueidentifier | yes | Data form record |
| DATAFORMNAME | nvarchar(60) | yes | Data form name |
| SHOWPAGEID | uniqueidentifier | yes | Page record |
| SHOWPAGENAME | nvarchar(60) | yes | Page name |
| REPORTID | uniqueidentifier | yes | Report record |
| REPORTNAME | nvarchar(100) | yes | Report name |
| REPORTACTIONCAPTION | nvarchar(max) | yes | Caption |
| SHOWINNEWWINDOW | int | yes | Show in new window |
| DISPLAYPROMPTAREA | int | yes | Display prompt area |
| DISPLAYDOCUMENTMAP | int | yes | Display document map |
| DISPLAYTOOLBAR | int | yes | Display toolbar |
| EXPORTTYPE | nvarchar(5) | yes | Export type |
| REPORTACTIONCAPTIONRESOURCEKEY | nvarchar(max) | yes | Caption resource key |
| BUSINESSPROCESSID | uniqueidentifier | yes | Business process record |
| BUSINESSPROCESSNAME | nvarchar(60) | yes | Business process name |
| BUSINESSPROCESSSTATUSPAGEID | uniqueidentifier | yes | Business process status page record |
| BUSINESSPROCESSSTATUSPAGENAME | nvarchar(60) | yes | Business process status page name |
| BUSINESSPROCESSPREPROCESSEDITFORMID | uniqueidentifier | yes | Business process preprocess edit form record |
| BUSINESSPROCESSPREPROCESSEDITFORMNAME | nvarchar(60) | yes | Business process preprocess edit form name |
| BROWSEQUERYRESULTSPAGEID | uniqueidentifier | yes | Browse query results page record |
| BROWSEQUERYRESULTSPAGENAME | nvarchar(60) | yes | Browse query results page name |
| BROWSEQUERYRESULTSQUERYTYPE | varchar(20) | yes | Query type |
| BROWSEQUERYRESULTSQUERYNAME | nvarchar(255) | yes | Browse query results query name |
| RUNBUSINESSPROCESSPAGEID | uniqueidentifier | yes | Run business process page record |
| RUNBUSINESSPROCESSPAGENAME | nvarchar(60) | yes | Run business process page name |
| ID | uniqueidentifier | System record ID | |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| FOLDERRESOURCEKEY | nvarchar(256) | yes | |
| ISFUNCTIONALAREADEFAULT | int | yes | |
| GLOBALADDMENUCAPTION | nvarchar(256) | yes | |
| GLOBALADDMENUCAPTIONRESOURCEKEY | nvarchar(256) | yes | |
| PERVASIVESEARCHCAPTION | nvarchar(256) | yes | |
| PERVASIVESEARCHCAPTIONRESOURCEKEY | nvarchar(256) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:30:56 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_TASK AS
with xmlnamespaces ('bb_appfx_task' as tns, 'bb_appfx_commontypes' as common)
select
T.NAME,
T.DESCRIPTION,
T.TASKSPECXML.value('(tns:TaskSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
T.TASKGROUP,
T.SEQUENCE,
coalesce(T.TASKSPECXML.value('(tns:TaskSpec/@ImageKey)[1]', 'nvarchar(256)'), 'res:tasks') as IMAGEKEY,
T.TASKSPECXML.value('(tns:TaskSpec/@SmallImageKey)[1]', 'nvarchar(256)') as SMALLIMAGEKEY,
T.TASKSPECXML.value('(tns:TaskSpec/@HelpKey)[1]', 'nvarchar(200)') as HELPKEY,
F.NAME as FUNCTIONALAREA,
coalesce(T.TASKSPECXML.value('(tns:TaskSpec/@DisplayAsAction)[1]', 'bit'), 0) as DISPLAYASACTION,
coalesce(T.TASKSPECXML.value('(tns:TaskSpec/@AppendSeparator)[1]', 'bit'), 0) as APPENDSEPARATOR,
coalesce(T.TASKSPECXML.value('(tns:TaskSpec/@Visible)[1]', 'bit'), 1) as VISIBLE,
coalesce(T.TASKSPECXML.value('(tns:TaskSpec/@IsCustomizable)[1]', 'bit'), 1) as ISCUSTOMIZABLE,
T.TASKSPECXML.value('(tns:TaskSpec/@Folder)[1]', 'nvarchar(256)') as FOLDER,
T.TASKSPECXML.value('(tns:TaskSpec/@FolderImageKey)[1]', 'nvarchar(256)') as FOLDERIMAGEKEY,
T.TASKSPECXML.value('(tns:TaskSpec/@FolderTaskGroup)[1]', 'nvarchar(256)') as FOLDERTASKGROUP,
T.TASKSPECXML.value('(tns:TaskSpec/@FolderTaskGroupImageKey)[1]', 'nvarchar(256)') as FOLDERTASKGROUPIMAGEKEY,
dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(T.TASKSPECXML.query('tns:TaskSpec/common:InstalledProductList')) as INSTALLEDPRODUCTSLIST,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(T.TASKSPECXML.query('tns:TaskSpec/common:InstalledProductList')) as INSTALLED,
T.TASKSPECXML as TASKSPECXML,
case T.TASKSPECXML.exist('tns:TaskSpec/tns:ResourceFile')
when 1 then
T.TASKSPECXML.value('(tns:TaskSpec/tns:ResourceFile/@AssemblyName)[1]', 'nvarchar(max)') + ', ' +
T.TASKSPECXML.value('(tns:TaskSpec/tns:ResourceFile/@ClassName)[1]', 'nvarchar(max)')
else null
end as RESOURCEFILE,
case T.TASKSPECXML.exist('tns:TaskSpec/tns:ShellTaskFilter')
when 1 then
T.TASKSPECXML.value('(tns:TaskSpec/tns:ShellTaskFilter/@AssemblyName)[1]', 'nvarchar(max)') + ', ' +
T.TASKSPECXML.value('(tns:TaskSpec/tns:ShellTaskFilter/@ClassName)[1]', 'nvarchar(max)')
else null
end as SHELLTASKFILTER,
case
when T.TASKSPECXML.exist('tns:TaskSpec/common:ExecuteCLRAction') = 1 then 'Execute CLR action'
when T.TASKSPECXML.exist('tns:TaskSpec/common:ExecuteRecordOperation') = 1 then 'Execute record operation'
when T.TASKSPECXML.exist('tns:TaskSpec/common:ShowAddDataForm') = 1 then 'Show add data form'
when T.TASKSPECXML.exist('tns:TaskSpec/common:ShowDataForm') = 1 then 'Show data form'
when T.TASKSPECXML.exist('tns:TaskSpec/common:ShowPage') = 1 then 'Show page'
when T.TASKSPECXML.exist('tns:TaskSpec/common:ShowReport') = 1 then 'Show report'
when T.TASKSPECXML.exist('tns:TaskSpec/common:StartBusinessProcess') = 1 then 'Start business process'
when T.TASKSPECXML.exist('tns:TaskSpec/common:BrowseQueryResults') = 1 then 'Browse query results'
when T.TASKSPECXML.exist('tns:TaskSpec/common:RunBusinessProcess') = 1 then 'Run business process'
else null
end as TASKACTIONTYPE,
case
when T.TASKSPECXML.exist('*/common:ActionContext/common:Expression') = 1 then 'Expression'
when T.TASKSPECXML.exist('*/common:ActionContext/common:SearchListReturnValue') = 1 then 'Search list return value'
else 'None'
end as TASKACTIONCONTEXTTYPE,
T.TASKSPECXML.value('(*/common:ActionContext/common:Expression)[1]', 'nvarchar(max)') as EXPRESSION,
T.TASKSPECXML.value('(*/common:ActionContext/common:SearchListReturnValue/@SearchListID)[1]', 'uniqueidentifier') AS SEARCHLISTID,
(select S.NAME
from dbo.SEARCHLISTCATALOG as S
where S.ID = T.TASKSPECXML.value('(*/common:ActionContext/common:SearchListReturnValue/@SearchListID)[1]', 'uniqueidentifier'))
as SEARCHLISTNAME,
case
when T.TASKSPECXML.exist('*/common:PostActionEvent/common:GoToPage/common:FunctionalArea') = 1 then 'Go to functional area'
when T.TASKSPECXML.exist('*/common:PostActionEvent/common:GoToPage/common:Home') = 1 then 'Go home'
when T.TASKSPECXML.exist('*/common:PostActionEvent/common:GoToPage/common:PreviousPage') = 1 then 'Go to previous page'
when T.TASKSPECXML.exist('*/common:PostActionEvent/common:GoToPage/common:SpecificPage') = 1 then 'Go to specific page'
when T.TASKSPECXML.exist('*/common:PostActionEvent/common:RefreshPage') = 1 then 'Refresh page'
else 'None'
end as POSTACTIONEVENTTYPE,
(select F.NAME
from dbo.FUNCTIONALAREACATALOG as F
where F.ID = T.TASKSPECXML.value('(*/common:PostActionEvent/common:GoToPage/common:FunctionalArea/@FunctionalAreaID)[1]', 'uniqueidentifier'))
as POSTACTIONFUNCTIONALAREANAME,
T.TASKSPECXML.value('(*/common:PostActionEvent/common:GoToPage/common:SpecificPage/@PageID)[1]', 'uniqueidentifier') as POSTACTIONSPECIFICPAGEID,
(select P.NAME
from dbo.PAGEDEFINITIONCATALOG as P
where P.ID = T.TASKSPECXML.value('(*/common:PostActionEvent/common:GoToPage/common:SpecificPage/@PageID)[1]', 'uniqueidentifier'))
as POSTACTIONSPECIFICPAGENAME,
T.TASKSPECXML.value('(tns:TaskSpec/common:ExecuteCLRAction/common:ComponentIdentifier/@AssemblyName)[1]', 'nvarchar(max)') as CLRACTIONASSEMBLYNAME,
T.TASKSPECXML.value('(tns:TaskSpec/common:ExecuteCLRAction/common:ComponentIdentifier/@ClassName)[1]', 'nvarchar(max)') as CLRACTIONCLASSNAME,
T.TASKSPECXML.value('(tns:TaskSpec/common:ExecuteCLRAction/common:ScriptIdentifier/@Url)[1]', 'nvarchar(max)') as SCRIPTIDENTIFIERURL,
T.TASKSPECXML.value('(tns:TaskSpec/common:ExecuteCLRAction/common:ScriptIdentifier/@ObjectName)[1]', 'nvarchar(max)') as SCRIPTIDENTIFIEROBJECTNAME,
T.TASKSPECXML.value('(tns:TaskSpec/common:ExecuteRecordOperation/@RecordOperationID)[1]', 'uniqueidentifier') AS RECORDOPERATIONID,
(select R.DISPLAYNAME
from dbo.RECORDOPERATIONCATALOG as R
where R.ID = T.TASKSPECXML.value('(tns:TaskSpec/common:ExecuteRecordOperation/@RecordOperationID)[1]', 'uniqueidentifier'))
as RECORDOPERATIONNAME,
coalesce(T.TASKSPECXML.value('(tns:TaskSpec/common:ExecuteRecordOperation/@ShowPrompt)[1]', 'bit'), 1) AS RECORDOPERATIONSHOWPROMPT,
T.TASKSPECXML.value('(tns:TaskSpec/common:ShowAddDataForm/@DataFormID)[1]', 'uniqueidentifier') AS ADDDATAFORMID,
(select INSTANCE.FORMNAME
from dbo.DATAFORMINSTANCECATALOG as INSTANCE
where INSTANCE.ID = T.TASKSPECXML.value('(tns:TaskSpec/common:ShowAddDataForm/@DataFormID)[1]', 'uniqueidentifier'))
as ADDDATAFORMNAME,
T.TASKSPECXML.value('(tns:TaskSpec/common:ShowDataForm/@DataFormID)[1]', 'uniqueidentifier') AS DATAFORMID,
(select INSTANCE.FORMNAME
from dbo.DATAFORMINSTANCECATALOG as INSTANCE
where INSTANCE.ID = T.TASKSPECXML.value('(tns:TaskSpec/common:ShowDataForm/@DataFormID)[1]', 'uniqueidentifier'))
as DATAFORMNAME,
T.TASKSPECXML.value('(tns:TaskSpec/common:ShowPage/@PageID)[1]', 'uniqueidentifier') AS SHOWPAGEID,
(select P.NAME
from dbo.PAGEDEFINITIONCATALOG as P
where P.ID = T.TASKSPECXML.value('(tns:TaskSpec/common:ShowPage/@PageID)[1]', 'uniqueidentifier'))
as SHOWPAGENAME,
T.TASKSPECXML.value('(tns:TaskSpec/common:ShowReport/@ReportID)[1]', 'uniqueidentifier') AS REPORTID,
(select R.NAME
from dbo.REPORTCATALOG as R
where R.ID = T.TASKSPECXML.value('(tns:TaskSpec/common:ShowReport/@ReportID)[1]', 'uniqueidentifier'))
as REPORTNAME,
T.TASKSPECXML.value('(tns:TaskSpec/common:ShowReport/@Caption)[1]', 'nvarchar(max)') AS REPORTACTIONCAPTION,
coalesce(T.TASKSPECXML.value('(tns:TaskSpec/common:ShowReport/@ShowInNewWindow)[1]', 'bit'), 0) AS SHOWINNEWWINDOW,
coalesce(T.TASKSPECXML.value('(tns:TaskSpec/common:ShowReport/@DisplayPromptArea)[1]', 'bit'), 0) AS DISPLAYPROMPTAREA,
coalesce(T.TASKSPECXML.value('(tns:TaskSpec/common:ShowReport/@DisplayDocumentMap)[1]', 'bit'), 0) AS DISPLAYDOCUMENTMAP,
coalesce(T.TASKSPECXML.value('(tns:TaskSpec/common:ShowReport/@DisplayToolbar)[1]', 'bit'), 1) AS DISPLAYTOOLBAR,
coalesce(T.TASKSPECXML.value('(tns:TaskSpec/common:ShowReport/@ExportType)[1]', 'nvarchar(5)'), 'none') AS EXPORTTYPE,
T.TASKSPECXML.value('(tns:TaskSpec/common:ShowReport/@CaptionResourceKey)[1]', 'nvarchar(max)') AS REPORTACTIONCAPTIONRESOURCEKEY,
T.TASKSPECXML.value('(tns:TaskSpec/common:StartBusinessProcess/@BusinessProcessID)[1]', 'uniqueidentifier') AS BUSINESSPROCESSID,
(select B.NAME
from dbo.BUSINESSPROCESSCATALOG as B
where B.ID = T.TASKSPECXML.value('(tns:TaskSpec/common:StartBusinessProcess/@BusinessProcessID)[1]', 'uniqueidentifier'))
as BUSINESSPROCESSNAME,
T.TASKSPECXML.value('(tns:TaskSpec/common:StartBusinessProcess/@StatusPageID)[1]', 'uniqueidentifier') AS BUSINESSPROCESSSTATUSPAGEID,
(select P.NAME
from dbo.PAGEDEFINITIONCATALOG as P
where P.ID = T.TASKSPECXML.value('(tns:TaskSpec/common:StartBusinessProcess/@StatusPageID)[1]', 'uniqueidentifier'))
as BUSINESSPROCESSSTATUSPAGENAME,
T.TASKSPECXML.value('(tns:TaskSpec/common:StartBusinessProcess/@PreprocessEditFormID)[1]', 'uniqueidentifier') AS BUSINESSPROCESSPREPROCESSEDITFORMID,
(select INSTANCE.FORMNAME
from dbo.DATAFORMINSTANCECATALOG as INSTANCE
where INSTANCE.ID = T.TASKSPECXML.value('(tns:TaskSpec/common:StartBusinessProcess/@PreprocessEditFormID)[1]', 'uniqueidentifier'))
as BUSINESSPROCESSPREPROCESSEDITFORMNAME,
T.TASKSPECXML.value('(tns:TaskSpec/common:BrowseQueryResults/@PageID)[1]', 'uniqueidentifier') AS BROWSEQUERYRESULTSPAGEID,
(select P.NAME
from dbo.PAGEDEFINITIONCATALOG as P
where P.ID = T.TASKSPECXML.value('(tns:TaskSpec/common:BrowseQueryResults/@PageID)[1]', 'uniqueidentifier'))
as BROWSEQUERYRESULTSPAGENAME,
case coalesce(T.TASKSPECXML.value('(tns:TaskSpec/common:BrowseQueryResults/@QueryType)[1]', 'nvarchar(20)'), 'AdhocQuery')
when 'AdhocQuery' then 'Ad-hoc query'
when 'SmartQueryInstance' then 'Smart query instance'
end as BROWSEQUERYRESULTSQUERYTYPE,
case coalesce(T.TASKSPECXML.value('(tns:TaskSpec/common:BrowseQueryResults/@QueryType)[1]', 'nvarchar(20)'), 'AdhocQuery')
when 'AdhocQuery' then
(select Q.DISPLAYNAME from dbo.QUERYVIEWCATALOG as Q where Q.ID = T.TASKSPECXML.value('(tns:TaskSpec/common:BrowseQueryResults/@QueryID)[1]', 'uniqueidentifier'))
when 'SmartQueryInstance' then
(select Q.NAME from dbo.SMARTQUERYINSTANCE as Q where Q.ID = T.TASKSPECXML.value('(tns:TaskSpec/common:BrowseQueryResults/@QueryID)[1]', 'uniqueidentifier'))
end as BROWSEQUERYRESULTSQUERYNAME,
T.TASKSPECXML.value('(tns:TaskSpec/common:RunBusinessProcess/@PageID)[1]', 'uniqueidentifier') AS RUNBUSINESSPROCESSPAGEID,
(select P.NAME
from dbo.PAGEDEFINITIONCATALOG as P
where P.ID = T.TASKSPECXML.value('(tns:TaskSpec/common:RunBusinessProcess/@PageID)[1]', 'uniqueidentifier'))
as RUNBUSINESSPROCESSPAGENAME,
T.ID,
ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
T.DATEADDED,
T.DATECHANGED,
T.TSLONG,
T.TASKSPECXML.value('(tns:TaskSpec/@FolderResourceKey)[1]', 'nvarchar(256)') as FOLDERRESOURCEKEY,
coalesce(T.TASKSPECXML.value('(tns:TaskSpec/@IsFunctionalAreaDefault)[1]', 'bit'), 0) as ISFUNCTIONALAREADEFAULT,
T.TASKSPECXML.value('(tns:TaskSpec/tns:GlobalAddMenuOptions/@Caption)[1]', 'nvarchar(256)') as GLOBALADDMENUCAPTION,
T.TASKSPECXML.value('(tns:TaskSpec/tns:GlobalAddMenuOptions/@CaptionResourceKey)[1]', 'nvarchar(256)') as GLOBALADDMENUCAPTIONRESOURCEKEY,
T.TASKSPECXML.value('(tns:TaskSpec/tns:PervasiveSearchOptions/@Caption)[1]', 'nvarchar(256)') as PERVASIVESEARCHCAPTION,
T.TASKSPECXML.value('(tns:TaskSpec/tns:PervasiveSearchOptions/@CaptionResourceKey)[1]', 'nvarchar(256)') as PERVASIVESEARCHCAPTIONRESOURCEKEY
/*#EXTENSION*/
from dbo.TASKCATALOG as T
inner join dbo.FUNCTIONALAREACATALOG as F on T.FUNCTIONALAREAID = F.ID
left outer join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = T.ADDEDBYID
left outer join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = T.CHANGEDBYID