V_QUERY_RECORDOPERATION
Provides support for querying on Record Operation metadata from the application catalog.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| DISPLAYNAME | nvarchar(60) | Name | |
| DESCRIPTION | nvarchar(1000) | Description | |
| AUTHOR | nvarchar(500) | yes | Author |
| CONTEXTRECORDTYPENAME | nvarchar(50) | yes | Context record type |
| IMPLEMENTATIONTYPENAME | varchar(3) | Implementation type | |
| IMPLEMENTATIONDESCRIPTION | nvarchar(264) | yes | Implementation description |
| SECURITYUIFOLDER | nvarchar(255) | Security UI folder | |
| SECURITYUIDISPLAYFEATURE | int | yes | Security UI display feature |
| BPSECURITYIDISPARAMETERSETID | int | yes | ID is parameter set ID |
| BPSECURITYIDISSTATUSID | int | yes | ID is status ID |
| INSTALLEDPRODUCTSLIST | nvarchar(max) | yes | Installed products list |
| INSTALLED | bit | yes | Installed |
| RECORDOPERATIONSPECXML | xml | Record operation spec xml | |
| RESOURCEFILE | nvarchar(max) | yes | Resource file |
| OPERATIONTYPENAME | varchar(6) | Operation type | |
| TIMEOUTSECONDS | int | Timeout seconds | |
| PROMPTTYPE | varchar(8) | Prompt type | |
| STANDARDPROMPTTEXT | nvarchar(max) | yes | Text |
| STANDARDPROMPTTEXTRESOURCEKEY | nvarchar(max) | yes | Text resource key |
| CLRPROMPTDEFINITION | nvarchar(max) | yes | Definition |
| PROCEDURENAME | nvarchar(128) | Procedure name | |
| CREATEPROCEDURESQL | nvarchar(max) | yes | Create procedure sql |
| ASSEMBLYNAME | nvarchar(128) | Assembly name | |
| CLASSNAME | nvarchar(128) | Class 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 |
| SPECUINAME | nvarchar(60) | Name override | |
| UINAME | nvarchar(60) | Display name | |
| NOSECURITYREQUIRED | int | yes | |
| HASID | int | yes | |
| POSTACTIONASSEMBLYNAME | nvarchar(max) | yes | |
| POSTACTIONCLASSNAME | nvarchar(max) | 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_RECORDOPERATION AS
with xmlnamespaces ('bb_appfx_recordoperation' as tns, 'bb_appfx_commontypes' as common)
select
R.DISPLAYNAME,
R.DESCRIPTION,
R.RECORDOPERATIONSPECXML.value('(tns:RecordOperationSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
RT.NAME as CONTEXTRECORDTYPENAME,
R.IMPLEMENTATIONTYPENAME,
case R.IMPLEMENTATIONTYPE
when 0 then 'SP (' + R.PROCEDURENAME + ')'
when 1 then 'CLR (' + R.ASSEMBLYNAME + ', ' + R.CLASSNAME + ')'
end as IMPLEMENTATIONDESCRIPTION,
R.SECURITYUIFOLDER,
coalesce(R.RECORDOPERATIONSPECXML.value('(tns:RecordOperationSpec/@SecurityUIDisplayFeature)[1]', 'bit'), 1) as SECURITYUIDISPLAYFEATURE,
coalesce(R.RECORDOPERATIONSPECXML.value('(tns:RecordOperationSpec/@BPSecurityIDIsParameterSetID)[1]', 'bit'), 0) as BPSECURITYIDISPARAMETERSETID,
coalesce(R.RECORDOPERATIONSPECXML.value('(tns:RecordOperationSpec/@BPSecurityIDIsStatusID)[1]', 'bit'), 0) as BPSECURITYIDISSTATUSID,
dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(R.RECORDOPERATIONSPECXML.query('tns:RecordOperationSpec/common:InstalledProductList')) as INSTALLEDPRODUCTSLIST,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(R.RECORDOPERATIONSPECXML.query('tns:RecordOperationSpec/common:InstalledProductList')) as INSTALLED,
R.RECORDOPERATIONSPECXML,
case R.RECORDOPERATIONSPECXML.exist('tns:RecordOperationSpec/tns:ResourceFile')
when 1 then
R.RECORDOPERATIONSPECXML.value('(tns:RecordOperationSpec/tns:ResourceFile/@AssemblyName)[1]', 'nvarchar(max)') + ', ' +
R.RECORDOPERATIONSPECXML.value('(tns:RecordOperationSpec/tns:ResourceFile/@ClassName)[1]', 'nvarchar(max)')
else null
end as RESOURCEFILE,
R.OPERATIONTYPENAME,
R.TIMEOUTSECONDS,
case
when R.RECORDOPERATIONSPECXML.exist('tns:RecordOperationSpec/tns:Prompt/tns:StandardPrompt') = 1 then 'Standard'
when R.RECORDOPERATIONSPECXML.exist('tns:RecordOperationSpec/tns:Prompt/tns:CLRPrompt') = 1 then 'CLR'
else 'None'
end as PROMPTTYPE,
case R.RECORDOPERATIONSPECXML.exist('tns:RecordOperationSpec/tns:Prompt/tns:StandardPrompt')
when 1 then R.RECORDOPERATIONSPECXML.value('(tns:RecordOperationSpec/tns:Prompt/tns:StandardPrompt/@Text)[1]', 'nvarchar(max)')
else null
end as STANDARDPROMPTTEXT,
case R.RECORDOPERATIONSPECXML.exist('tns:RecordOperationSpec/tns:Prompt/tns:StandardPrompt')
when 1 then R.RECORDOPERATIONSPECXML.value('(tns:RecordOperationSpec/tns:Prompt/tns:StandardPrompt/@TextResourceKey)[1]', 'nvarchar(max)')
else null
end as STANDARDPROMPTTEXTRESOURCEKEY,
case R.RECORDOPERATIONSPECXML.exist('tns:RecordOperationSpec/tns:Prompt/tns:CLRPrompt')
when 1 then
R.RECORDOPERATIONSPECXML.value('(tns:RecordOperationSpec/tns:Prompt/tns:CLRPrompt/@AssemblyName)[1]', 'nvarchar(max)') + ', ' +
R.RECORDOPERATIONSPECXML.value('(tns:RecordOperationSpec/tns:Prompt/tns:CLRPrompt/@ClassName)[1]', 'nvarchar(max)')
else null
end as CLRPROMPTDEFINITION,
R.PROCEDURENAME,
case R.RECORDOPERATIONSPECXML.exist('tns:RecordOperationSpec/tns:SPRecord/tns:SPOperationImplementation/common:CreateProcedureSQL')
when 1 then R.RECORDOPERATIONSPECXML.value('(tns:RecordOperationSpec/tns:SPRecord/tns:SPOperationImplementation/common:CreateProcedureSQL)[1]', 'nvarchar(max)')
else null
end as CREATEPROCEDURESQL,
R.ASSEMBLYNAME,
R.CLASSNAME,
R.ID,
ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
R.DATEADDED,
R.DATECHANGED,
R.TSLONG,
R.SPECUINAME,
R.UINAME,
coalesce(R.RECORDOPERATIONSPECXML.value('(tns:RecordOperationSpec/@NoSecurityRequired)[1]', 'bit'), 0) as NOSECURITYREQUIRED,
coalesce(R.RECORDOPERATIONSPECXML.value('(tns:RecordOperationSpec/@HasID)[1]', 'bit'), 1) as HASID,
R.RECORDOPERATIONSPECXML.value('(tns:RecordOperationSpec/tns:PostAction/@AssemblyName)[1]', 'nvarchar(max)') as POSTACTIONASSEMBLYNAME,
R.RECORDOPERATIONSPECXML.value('(tns:RecordOperationSpec/tns:PostAction/@ClassName)[1]', 'nvarchar(max)') as POSTACTIONCLASSNAME
/*#EXTENSION*/
from dbo.RECORDOPERATIONCATALOG as R
left outer join dbo.RECORDTYPE as RT on R.RECORDTYPEID = RT.ID
left outer join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = R.ADDEDBYID
left outer join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = R.CHANGEDBYID