V_QUERY_DATAFORMINSTANCE

Provides the ability to query Data Form Instance.

Fields

Field	Field Type	Null	Description
ID	uniqueidentifier		System record ID
FORMNAME	nvarchar(60)		Form name
DESCRIPTION	nvarchar(1000)		Description
MODENAME	varchar(4)		Mode
IMPLEMENTATIONTYPENAME	varchar(3)		Implementation Type
ASSEMBLYNAME	nvarchar(128)		Assembly Name
CLASSNAME	nvarchar(128)		Class Name
SECURITYUIFOLDER	nvarchar(255)		Security Folder
RECORDTYPENAME	nvarchar(50)	yes	Record type
TEMPLATESPECXML	xml		Template Spec XML
DATEADDED	datetime		Date added
DATECHANGED	datetime		Date changed
TSLONG	bigint	yes	Timestamp value
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
AUTHOR	nvarchar(500)	yes	Author
IMPLEMENTATIONDESCRIPTION	nvarchar(264)	yes	Implementation description
INSTALLEDPRODUCTSLIST	nvarchar(max)	yes	Installed products list
INSTALLED	bit	yes	Installed
RESOURCEFILE	nvarchar(max)	yes	Resource file
FORMUIXML	xml		Instance Form UI XML
LOADPROCEDURE	nvarchar(128)		Load procedure name
LOADPROCEDURESQL	nvarchar(max)	yes	Load procedure sql
SAVEPROCEDURE	nvarchar(128)		Save procedure name
SAVEPROCEDURESQL	nvarchar(max)	yes	Save procedure sql
CONTEXTRECORDTYPENAME	nvarchar(50)	yes	Context record type
CONTEXTPARAMETERNAME	nvarchar(128)		Context parameter name
FORMUICOMPONENTTYPE	varchar(16)		Form UI component type
FORMCOMPONENTASSEMBLYNAME	nvarchar(max)	yes	Form component assembly name
FORMCOMPONENTCLASSNAME	nvarchar(max)	yes	Form component class name
WEBUICOMPONENTTYPE	varchar(17)		Web UI component type
WEBUIMODELASSEMBLYNAME	nvarchar(max)	yes	Web UI model assembly name
WEBUIMODELCLASSNAME	nvarchar(max)	yes	Web UI model class name
WEBUIEXTERNALRESOURCEURL	nvarchar(max)	yes	Web UI external resource url
NOSECURITYREQUIRED	int	yes	No security required
SPECUINAME	nvarchar(60)		Name override
UINAME	nvarchar(60)		Display name
POSTACTIONASSEMBLYNAME	nvarchar(max)	yes
POSTACTIONCLASSNAME	nvarchar(max)	yes
LASTUPDATEDSOURCE	varchar(16)	yes
LASTUPDATEDSOURCEOLAPDATASOURCEID	uniqueidentifier	yes
LASTUPDATEDSOURCEFORMFIELDID	nvarchar(128)	yes

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  3/19/2013 1:30:53 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_DATAFORMINSTANCE AS



with xmlnamespaces ('bb_appfx_viewdataformtemplate' as viewspec, 'bb_appfx_adddataformtemplate' as addspec, 'bb_appfx_editdataformtemplate' as editspec, 'bb_appfx_commontypes' as common)
select 
    INSTANCE.ID, 
    INSTANCE.FORMNAME, 
    INSTANCE.DESCRIPTION, 
    TEMPLATE.MODENAME, 
    TEMPLATE.IMPLEMENTATIONTYPENAME, 
    TEMPLATE.ASSEMBLYNAME, 
    TEMPLATE.CLASSNAME, 
    TEMPLATE.SECURITYUIFOLDER, 
    RT.NAME as RECORDTYPENAME,
    TEMPLATE.TEMPLATESPECXML,
    INSTANCE.DATEADDED,
    INSTANCE.DATECHANGED,
    INSTANCE.TSLONG,
    ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
    ADDEDBY.USERNAME as ADDEDBY_USERNAME,
    CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
    CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,

    case TEMPLATE.MODE
        when 0 then
            TEMPLATE.TEMPLATESPECXML.value('(viewspec:ViewDataFormTemplateSpec/@Author)[1]', 'nvarchar(500)')
        when 1 then 
            TEMPLATE.TEMPLATESPECXML.value('(editspec:EditDataFormTemplateSpec/@Author)[1]', 'nvarchar(500)')
        when 2 then
            TEMPLATE.TEMPLATESPECXML.value('(addspec:AddDataFormTemplateSpec/@Author)[1]', 'nvarchar(500)')
    end as AUTHOR,
    case TEMPLATE.IMPLEMENTATIONTYPE
        when 0 then
            case TEMPLATE.MODE
                when 0 then 
                    'SP (' + TEMPLATE.LOADPROCEDURE + ')'
                when 1 then 
                    'SP (' + TEMPLATE.LOADPROCEDURE + ', ' + TEMPLATE.SAVEPROCEDURE + ')'
                when 2 then 
                    case TEMPLATE.LOADPROCEDURE
                        when '' then
                            'SP (' + TEMPLATE.SAVEPROCEDURE + ')'
                        else 
                            'SP (' + TEMPLATE.LOADPROCEDURE + ', ' + TEMPLATE.SAVEPROCEDURE + ')'
                    end
            end
        when 1 then 
            'CLR (' + TEMPLATE.ASSEMBLYNAME + ', ' + TEMPLATE.CLASSNAME + ')'
    end as IMPLEMENTATIONDESCRIPTION,
    case TEMPLATE.MODE
            when 0 then
                dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(TEMPLATE.TEMPLATESPECXML.query('viewspec:ViewDataFormTemplateSpec/common:InstalledProductList'))
            when 1 then 
                dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(TEMPLATE.TEMPLATESPECXML.query('editspec:EditDataFormTemplateSpec/common:InstalledProductList'))
            when 2 then
                dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(TEMPLATE.TEMPLATESPECXML.query('addspec:AddDataFormTemplateSpec/common:InstalledProductList'))
    end as INSTALLEDPRODUCTSLIST,
    case TEMPLATE.MODE
        when 0 then
            dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(TEMPLATE.TEMPLATESPECXML.query('viewspec:ViewDataFormTemplateSpec/common:InstalledProductList'))
        when 1 then 
            dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(TEMPLATE.TEMPLATESPECXML.query('editspec:EditDataFormTemplateSpec/common:InstalledProductList'))
        when 2 then
            dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(TEMPLATE.TEMPLATESPECXML.query('addspec:AddDataFormTemplateSpec/common:InstalledProductList'))
    end as INSTALLED,
    case TEMPLATE.MODE
        when 0 then
            case TEMPLATE.TEMPLATESPECXML.exist('viewspec:ViewDataFormTemplateSpec/viewspec:ResourceFile')
                when 1 then 
                    TEMPLATE.TEMPLATESPECXML.value('(viewspec:ViewDataFormTemplateSpec/viewspec:ResourceFile/@AssemblyName)[1]', 'nvarchar(max)') + ', ' +
                    TEMPLATE.TEMPLATESPECXML.value('(viewspec:ViewDataFormTemplateSpec/viewspec:ResourceFile/@ClassName)[1]', 'nvarchar(max)') 
                else null
            end
        when 1 then 
            case TEMPLATE.TEMPLATESPECXML.exist('editspec:EditDataFormTemplateSpec/editspec:ResourceFile')
                when 1 then 
                    TEMPLATE.TEMPLATESPECXML.value('(editspec:EditDataFormTemplateSpec/editspec:ResourceFile/@AssemblyName)[1]', 'nvarchar(max)') + ', ' +
                    TEMPLATE.TEMPLATESPECXML.value('(editspec:EditDataFormTemplateSpec/editspec:ResourceFile/@ClassName)[1]', 'nvarchar(max)') 
                else null
            end
        when 2 then
            case TEMPLATE.TEMPLATESPECXML.exist('addspec:EditDataFormTemplateSpec/addspec:ResourceFile')
                when 1 then 
                    TEMPLATE.TEMPLATESPECXML.value('(addspec:AddDataFormTemplateSpec/addspec:ResourceFile/@AssemblyName)[1]', 'nvarchar(max)') + ', ' +
                    TEMPLATE.TEMPLATESPECXML.value('(addspec:AddDataFormTemplateSpec/addspec:ResourceFile/@ClassName)[1]', 'nvarchar(max)') 
                else null
            end
    end as RESOURCEFILE,
    INSTANCE.FORMUIXML,

    TEMPLATE.LOADPROCEDURE,
    case TEMPLATE.MODE
        when 0 then
            case TEMPLATE.TEMPLATESPECXML.exist('viewspec:ViewDataFormTemplateSpec/viewspec:SPDataForm/common:CreateProcedureSQL')
                when 1 then TEMPLATE.TEMPLATESPECXML.value('(viewspec:ViewDataFormTemplateSpec/viewspec:SPDataForm/common:CreateProcedureSQL)[1]', 'nvarchar(max)')
                else null
            end
        when 1 then 
            case TEMPLATE.TEMPLATESPECXML.exist('editspec:EditDataFormTemplateSpec/editspec:SPDataForm/editspec:LoadImplementation/common:CreateProcedureSQL')
                when 1 then TEMPLATE.TEMPLATESPECXML.value('(editspec:EditDataFormTemplateSpec/editspec:SPDataForm/editspec:LoadImplementation/common:CreateProcedureSQL)[1]', 'nvarchar(max)')
                else null
            end
        when 2 then
            case TEMPLATE.TEMPLATESPECXML.exist('addspec:AddDataFormTemplateSpec/addspec:SPDataForm/addspec:LoadImplementation/common:CreateProcedureSQL')
                when 1 then TEMPLATE.TEMPLATESPECXML.value('(addspec:AddDataFormTemplateSpec/addspec:SPDataForm/addspec:LoadImplementation/common:CreateProcedureSQL)[1]', 'nvarchar(max)')
                else null
            end
    end as LOADPROCEDURESQL,

    TEMPLATE.SAVEPROCEDURE,
    case TEMPLATE.MODE
        when 0 then
            null
        when 1 then 
            case TEMPLATE.TEMPLATESPECXML.exist('editspec:EditDataFormTemplateSpec/editspec:SPDataForm/editspec:SaveImplementation/common:CreateProcedureSQL')
                when 1 then TEMPLATE.TEMPLATESPECXML.value('(editspec:EditDataFormTemplateSpec/editspec:SPDataForm/editspec:SaveImplementation/common:CreateProcedureSQL)[1]', 'nvarchar(max)')
                else null
            end
        when 2 then
            case TEMPLATE.TEMPLATESPECXML.exist('addspec:AddDataFormTemplateSpec/addspec:SPDataForm/addspec:SaveImplementation/common:CreateProcedureSQL')
                when 1 then TEMPLATE.TEMPLATESPECXML.value('(addspec:AddDataFormTemplateSpec/addspec:SPDataForm/addspec:SaveImplementation/common:CreateProcedureSQL)[1]', 'nvarchar(max)')
                else null
            end
    end as SAVEPROCEDURESQL,

    RTCONTEXT.NAME as CONTEXTRECORDTYPENAME,
    TEMPLATE.CONTEXTPARAMETERNAME,

    case TEMPLATE.TEMPLATESPECXML.exist('*/common:FormMetaData/common:FormUIComponent')
        when 1 then
            case TEMPLATE.TEMPLATESPECXML.value('(*/common:FormMetaData/common:FormUIComponent/@FormUIComponentType)[1]', 'nvarchar(15)')
                when 'CustomComponent' then 'Custom component'
                else 'Default UI'
            end
        else
            'None'
    end as FORMUICOMPONENTTYPE,
    TEMPLATE.TEMPLATESPECXML.value('(*/common:FormMetaData/common:FormUIComponent/common:CustomComponentID/@AssemblyName)[1]', 'nvarchar(max)') as FORMCOMPONENTASSEMBLYNAME,
    TEMPLATE.TEMPLATESPECXML.value('(*/common:FormMetaData/common:FormUIComponent/common:CustomComponentID/@ClassName)[1]', 'nvarchar(max)') as FORMCOMPONENTCLASSNAME,

    case TEMPLATE.TEMPLATESPECXML.exist('*/common:FormMetaData/common:WebUIComponent')
        when 1 then
            case TEMPLATE.TEMPLATESPECXML.exist('*/common:FormMetaData/common:WebUIComponent/common:WebUI/common:ExternalResource')
                when 1 then 'External resource'
                else 'Default UI'
            end
        else
            'None'
    end as WEBUICOMPONENTTYPE,
    TEMPLATE.TEMPLATESPECXML.value('(*/common:FormMetaData/common:WebUIComponent/common:UIModel/@AssemblyName)[1]', 'nvarchar(max)') as WEBUIMODELASSEMBLYNAME,
    TEMPLATE.TEMPLATESPECXML.value('(*/common:FormMetaData/common:WebUIComponent/common:UIModel/@ClassName)[1]', 'nvarchar(max)') as WEBUIMODELCLASSNAME,
    TEMPLATE.TEMPLATESPECXML.value('(*/common:FormMetaData/common:WebUIComponent/common:WebUI/common:ExternalResource/@Url)[1]', 'nvarchar(max)') as WEBUIEXTERNALRESOURCEURL,

    case TEMPLATE.MODE
        when 0 then coalesce(TEMPLATE.TEMPLATESPECXML.value('(viewspec:ViewDataFormTemplateSpec/@NoSecurityRequired)[1]', 'bit'), 0)
        when 1 then coalesce(TEMPLATE.TEMPLATESPECXML.value('(editspec:EditDataFormTemplateSpec/@NoSecurityRequired)[1]', 'bit'), 0)
        when 2 then coalesce(TEMPLATE.TEMPLATESPECXML.value('(addspec:AddDataFormTemplateSpec/@NoSecurityRequired)[1]', 'bit'), 0)
    end as NOSECURITYREQUIRED, 
    INSTANCE.SPECUINAME,
    INSTANCE.UINAME,

    TEMPLATE.TEMPLATESPECXML.value('(*/common:DataFormPostAction/@AssemblyName)[1]', 'nvarchar(max)') as POSTACTIONASSEMBLYNAME,
    TEMPLATE.TEMPLATESPECXML.value('(*/common:DataFormPostAction/@ClassName)[1]', 'nvarchar(max)') as POSTACTIONCLASSNAME,

    case TEMPLATE.MODE 
        when 0 then 
            case TEMPLATE.TEMPLATESPECXML.exist('viewspec:ViewDataFormTemplateSpec/viewspec:LastUpdatedSource/common:OlapDataSource') 
                when 1 then 'OLAP data source'
                else case TEMPLATE.TEMPLATESPECXML.exist('viewspec:ViewDataFormTemplateSpec/viewspec:LastUpdatedSource/common:FormField') 
                    when 1 then 'Form field'
                else null
                end
            end
        else null
    end as LASTUPDATEDSOURCE,
    case TEMPLATE.MODE 
        when 0 then 
            case TEMPLATE.TEMPLATESPECXML.exist('viewspec:ViewDataFormTemplateSpec/viewspec:LastUpdatedSource/common:OlapDataSource') 
                when 1 then TEMPLATE.TEMPLATESPECXML.value('(viewspec:ViewDataFormTemplateSpec/viewspec:LastUpdatedSource/common:OlapDataSource/@ID)[1]', 'uniqueidentifier')
                else null
            end
        else null
    end as LASTUPDATEDSOURCEOLAPDATASOURCEID,
    case TEMPLATE.MODE 
        when 0 then 
            case TEMPLATE.TEMPLATESPECXML.exist('viewspec:ViewDataFormTemplateSpec/viewspec:LastUpdatedSource/common:FormField') 
                when 1 then TEMPLATE.TEMPLATESPECXML.value('(viewspec:ViewDataFormTemplateSpec/viewspec:LastUpdatedSource/common:FormField/@FieldID)[1]', 'nvarchar(128)')
                else null
            end
        else null
    end as LASTUPDATEDSOURCEFORMFIELDID

    /*#EXTENSION*/ 

from dbo.DATAFORMINSTANCECATALOG as INSTANCE
    inner join dbo.DATAFORMTEMPLATECATALOG as TEMPLATE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
    left join dbo.RECORDTYPE as RT on TEMPLATE.RECORDTYPEID = RT.ID
    left join dbo.RECORDTYPE as RTCONTEXT on TEMPLATE.CONTEXTRECORDTYPEID = RTCONTEXT.ID
    left join dbo.CHANGEAGENT ADDEDBY on ADDEDBY.ID = INSTANCE.ADDEDBYID
    left join dbo.CHANGEAGENT CHANGEDBY on CHANGEDBY.ID = INSTANCE.CHANGEDBYID