V_APPCATALOG_CATALOGITEMS

CREATE view dbo.V_APPCATALOG_CATALOGITEMS
as
    -- AddDataFormTemplateSpec    
    select 0 as CATALOGITEMTYPE,
           ID as ID,
           TEMPLATENAME as NAME,
           DESCRIPTION as DESCRIPTION,
           TEMPLATESPECXML.value('declare namespace tns="bb_appfx_adddataformtemplate"; (tns:AddDataFormTemplateSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           TEMPLATESPECXML as SPECXML 
    from dbo.DATAFORMTEMPLATECATALOG 
    where MODE = 2

    -- CodeTableSpec
    union all
    select 1 as CATALOGITEMTYPE,
           ID as ID,
           CODETABLENAME as NAME,
           CODETABLESPECXML.value('declare namespace tns="bb_appfx_codetable"; (tns:CodeTableSpec/@Description)[1]', 'nvarchar(1000)') as DESCRIPTION,
           CODETABLESPECXML.value('declare namespace tns="bb_appfx_codetable"; (tns:CodeTableSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           CODETABLESPECXML as SPECXML
    from dbo.CODETABLECATALOG

    -- DataListSpec
    union all
    select 2 as CATALOGITEMTYPE,
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           DATALISTSPEC.value('declare namespace tns="bb_appfx_datalist"; (tns:DataListSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           DATALISTSPEC as SPECXML
    from dbo.DATALISTCATALOG

    -- EditDataFormTemplateSpec
    union all
    select 3 as CATALOGITEMTYPE, 
           ID as ID,
           TEMPLATENAME as NAME,
           DESCRIPTION as DESCRIPTION,
           TEMPLATESPECXML.value('declare namespace tns="bb_appfx_editdataformtemplate"; (tns:EditDataFormTemplateSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           TEMPLATESPECXML as SPECXML 
    from dbo.DATAFORMTEMPLATECATALOG 
    where MODE = 1

    -- FunctionalAreaSpec
    union all
    select 4 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           FUNCTIONALAREASPECXML.value('declare namespace tns="bb_appfx_functionalarea"; (tns:FunctionalAreaSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           FUNCTIONALAREASPECXML as SPECXML 
    from dbo.FUNCTIONALAREACATALOG

    -- PackageSpec
    union all
    select 5 as CATALOGITEMTYPE, 
           ID as ID,
           PACKAGENAME as NAME,
           DESCRIPTION as DESCRIPTION,
           PACKAGESPECXML.value('declare namespace tns="bb_appfx_package"; (tns:PackageSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           PACKAGESPECXML as SPECXML 
    from dbo.PACKAGECATALOG

    -- PageDefinitionSpec
    union all
    select 6 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           PAGEDEFINITIONSPEC.value('declare namespace tns="bb_appfx_pagedefinition"; (tns:PageDefinitionSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           PAGEDEFINITIONSPEC as SPECXML 
    from dbo.PAGEDEFINITIONCATALOG

    -- QueryViewSpec
    union all
    select 7 as CATALOGITEMTYPE, 
           ID as ID,
           DISPLAYNAME as NAME,
           DESCRIPTION as DESCRIPTION,
           QUERYVIEWSPEC.value('declare namespace tns="bb_appfx_queryview"; (tns:QueryViewSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           QUERYVIEWSPEC as SPECXML 
    from dbo.QUERYVIEWCATALOG

    -- QueryViewSpecExtension
    union all
    select 8 as CATALOGITEMTYPE, 
           ID as ID,
           DISPLAYNAME as NAME,
           DESCRIPTION as DESCRIPTION,
           QUERYVIEWSPECEXTENSION.value('declare namespace tns="bb_appfx_queryview"; (tns:QueryViewSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           QUERYVIEWSPECEXTENSION as SPECXML 
    from dbo.QUERYVIEWCATALOG
    where cast(QUERYVIEWSPECEXTENSION as nvarchar(max)) <> ''

    -- ReportSpec
    union all
    select 9 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           REPORTSPECXML.value('declare namespace tns="bb_appfx_report"; (tns:ReportSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           REPORTSPECXML as SPECXML 
    from dbo.REPORTCATALOG

    -- SearchListSpec
    union all
    select 10 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           SEARCHLISTSPEC.value('declare namespace tns="bb_appfx_searchlist"; (tns:SearchListSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           SEARCHLISTSPEC as SPECXML 
    from dbo.SEARCHLISTCATALOG

    -- SimpleDataListSpec        
    union all
    select 11 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           SIMPLEDATALISTSPECXML.value('declare namespace tns="bb_appfx_simpledatalist"; (tns:SimpleDataListSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           SIMPLEDATALISTSPECXML as SPECXML 
    from dbo.SIMPLEDATALISTCATALOG

    -- SmartQuerySpec
    union all
    select 12 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           SMARTQUERYSPEC.value('declare namespace tns="bb_appfx_smartquery"; (tns:SmartQuerySpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           SMARTQUERYSPEC as SPECXML 
    from dbo.SMARTQUERYCATALOG

    -- SQLFunctionSpec
    union all
    select 13 as CATALOGITEMTYPE, 
           ID as ID,
           FUNCTIONNAME as NAME,
           DESCRIPTION as DESCRIPTION,
           SQLFUNCTIONSPECXML.value('declare namespace tns="bb_appfx_sqlfunction"; (tns:SQLFunctionSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           SQLFUNCTIONSPECXML as SPECXML 
    from dbo.SQLFUNCTIONCATALOG

    -- SQLStoredProcedureSpec
    union all
    select 14 as CATALOGITEMTYPE, 
           ID as ID,
           PROCEDURENAME as NAME,
           DESCRIPTION as DESCRIPTION,
           SQLPROCEDURESPECXML.value('declare namespace tns="bb_appfx_sqlstoredprocedure"; (tns:SQLStoredProcedureSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           SQLPROCEDURESPECXML as SPECXML 
    from dbo.SQLPROCEDURECATALOG

    -- TableSpec
    union all
    select 15 as CATALOGITEMTYPE, 
           ID as ID,
           TABLESPECXML.value('declare namespace tns="bb_appfx_table"; (tns:TableSpec/@Name)[1]', 'nvarchar(500)') as NAME,
           DESCRIPTION as DESCRIPTION,
           TABLESPECXML.value('declare namespace tns="bb_appfx_table"; (tns:TableSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           TABLESPECXML as SPECXML 
    from dbo.TABLECATALOG

    -- 16 - TableSpecExtension are no longer supported

    -- TaskSpec
    union all
    select 17 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           TASKSPECXML.value('declare namespace tns="bb_appfx_task"; (tns:TaskSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           TASKSPECXML as SPECXML 
    from dbo.TASKCATALOG

    -- TranslationFunctionSpec
    union all
    select 18 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           TRANSLATIONFUNCTIONSPEC.value('declare namespace tns="bb_appfx_translationfunction"; (tns:TranslationFunctionSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           TRANSLATIONFUNCTIONSPEC as SPECXML 
    from dbo.TRANSLATIONFUNCTIONCATALOG

    -- ViewDataFormTemplateSpec
    union all
    select 19 as CATALOGITEMTYPE, 
           ID as ID,
           TEMPLATENAME as NAME,
           DESCRIPTION as DESCRIPTION,
           TEMPLATESPECXML.value('declare namespace tns="bb_appfx_viewdataformtemplate"; (tns:ViewDataFormTemplateSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           TEMPLATESPECXML as SPECXML 
    from dbo.DATAFORMTEMPLATECATALOG 
    where MODE = 0

    -- BatchTypeSpec
    union all
    select 20 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           SPECXML.value('declare namespace tns="bb_appfx_batchtype"; (tns:BatchTypeSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           SPECXML as SPECXML 
    from dbo.BATCHTYPECATALOG

    -- IDMapperSpec
    union all
    select 21 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           IDMAPPERSPECXML.value('declare namespace tns="bb_appfx_idmapper"; (tns:IDMapperSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           IDMAPPERSPECXML as SPECXML 
    from dbo.IDMAPPERCATALOG

    -- BusinessProcessSpec
    union all
    select 22 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           BUSINESSPROCESSSPECXML.value('declare namespace tns="bb_appfx_businessprocess"; (tns:BusinessProcessSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           BUSINESSPROCESSSPECXML as SPECXML 
    from dbo.BUSINESSPROCESSCATALOG

    -- RecordOperationSpec
    union all
    select 23 as CATALOGITEMTYPE, 
           ID as ID,
           DISPLAYNAME as NAME,
           DESCRIPTION as DESCRIPTION,
           RECORDOPERATIONSPECXML.value('declare namespace tns="bb_appfx_recordoperation"; (tns:RecordOperationSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           RECORDOPERATIONSPECXML as SPECXML 
    from dbo.RECORDOPERATIONCATALOG

    -- FlashDashSpec
    union all
    select 24 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           SPECXML.value('declare namespace tns="bb_appfx_flashdash"; (tns:FlashDashSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           SPECXML as SPECXML 
    from dbo.DASHBOARDCATALOG

    -- KpiSpec
    union all
    select 25 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           SPECXML.value('declare namespace tns="bb_appfx_kpi"; (tns:KpiSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           SPECXML as SPECXML 
    from dbo.KPICATALOG

    -- MergeTaskSpec
    union all
    select 26 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           SPECXML.value('declare namespace tns="bb_appfx_mergetask"; (tns:MergeTaskSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           SPECXML as SPECXML 
    from dbo.MERGETASKCATALOG

    -- SmartFieldSpec
    union all
    select 27 as CATALOGITEMTYPE, 
           ID as ID,
           DISPLAYNAME as NAME,
           DESCRIPTION as DESCRIPTION,
           SMARTFIELDSPECXML.value('declare namespace tns="bb_appfx_smartfield"; (tns:SmartFieldSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           SMARTFIELDSPECXML as SPECXML 
    from dbo.SMARTFIELDCATALOG

    -- GlobalChangeSpec
    union all
    select 28 as CATALOGITEMTYPE, 
           ID as ID,
           DISPLAYNAME as NAME,
           DESCRIPTION as DESCRIPTION,
           GLOBALCHANGESPECXML.value('declare namespace tns="bb_appfx_globalchange"; (tns:GlobalChangeSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           GLOBALCHANGESPECXML as SPECXML 
    from dbo.GLOBALCHANGECATALOG

    -- WebDashboardPageSpec
    union all
    select 29 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           null as AUTHOR,
           null as SPECXML 
    from dbo.WEBDASHBOARDPAGECATALOG 

    -- SystemPrivielgeSpec
    union all
    select 30 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           null as AUTHOR,
           null as SPECXML 
    from dbo.SYSTEMPRIVILEGECATALOG

    -- ConfigurationDataSpec
    union all
    select 31 as CATALOGITEMTYPE, 
           ID as ID,
           DISPLAYNAME as NAME,
           DESCRIPTION as DESCRIPTION,
           CONFIGURATIONDATASPECXML.value('declare namespace tns="bb_appfx_configurationdata"; (tns:ConfigurationDataSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           CONFIGURATIONDATASPECXML as SPECXML 
    from dbo.CONFIGURATIONDATACATALOG

    -- BatchTypeExtensionSpec
    union all
    select 32 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           SPECXML.value('declare namespace tns="bb_appfx_batchtypeextension"; (tns:BatchTypeExtensionSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           SPECXML as SPECXML 
    from dbo.BATCHTYPEEXTENSIONCATALOG

    -- MapEntitySpec
    union all
    select 33 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           MAPENTITYXML.value('declare namespace tns="bb_appfx_mapentity"; (tns:MapEntitySpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           MAPENTITYXML as SPECXML 
    from dbo.MAPENTITYCATALOG

    -- DataFormAddInSpec
    union all
    select 34 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           DATAFORMADDINSPECXML.value('declare namespace tns="bb_appfx_dataformaddin"; (tns:DataFormAddInSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           DATAFORMADDINSPECXML as SPECXML 
    from dbo.DATAFORMADDINCATALOG

    -- WorkflowSpec
    union all
    select 35 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           SPECXML.value('declare namespace tns="bb_appfx_workflow"; (tns:WorkflowSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           SPECXML as SPECXML 
    from dbo.BBWORKFLOWCATALOG

    -- CustomUIModelMetaData
    -- else if @CATALOGITEMTYPE = 36
        -- these are not stored in the database

    -- CalendarSpec
    union all
    select 37 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           CALENDARSPECXML.value('declare namespace tns="bb_appfx_calendar"; (tns:CalendarSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           CALENDARSPECXML as SPECXML 
    from dbo.CALENDARCATALOG

    -- RelationshipMapSpec
    union all
    select 38 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           RELATIONSHIPMAPSPECXML.value('declare namespace tns="bb_appfx_relationshipmap"; (tns:RelationshipMapSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           RELATIONSHIPMAPSPECXML as SPECXML 
    from dbo.RELATIONSHIPMAPCATALOG

    -- UIWidgetSpec
    union all
    select 39 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           UIWIDGETSPECXML.value('declare namespace tns="bb_appfx_uiwidget"; (tns:UIWidgetSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           UIWIDGETSPECXML as SPECXML 
    from dbo.UIWIDGETCATALOG

    -- WorkflowInboxSpec
    union all
    select 40 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           SPECXML.value('declare namespace tns="bb_appfx_workflow_inbox"; (tns:WorkflowInboxSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           SPECXML as SPECXML 
    from dbo.BBWORKFLOWINBOX

    -- SQLViewSpec
    union all
    select 41 as CATALOGITEMTYPE, 
           ID as ID,
           VIEWNAME as NAME,
           DESCRIPTION as DESCRIPTION,
           SQLVIEWSPECXML.value('declare namespace tns="bb_appfx_sqlview"; (tns:SQLViewSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           SQLVIEWSPECXML as SPECXML 
    from dbo.SQLVIEWCATALOG

    -- TaskWizardSpec
    union all
    select 42 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           SPECXML.value('declare namespace tns="bb_appfx_taskwizard"; (tns:TaskWizardSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           SPECXML as SPECXML 
    from dbo.TASKWIZARDCATALOG

    -- FeatureGroupSpec
    union all
    select 43 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           SPECXML.value('declare namespace tns="bb_appfx_featuregroup"; (tns:FeatureGroupSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           SPECXML as SPECXML 
    from dbo.FEATUREGROUPCATALOG

    -- BatchTypeAddInSpec
    union all
    select 44 as CATALOGITEMTYPE, 
           ID as ID,
           NAME as NAME,
           DESCRIPTION as DESCRIPTION,
           BATCHTYPEADDINSPECXML.value('declare namespace tns="bb_appfx_batchtypeaddin"; (tns:BatchTypeAddInSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
           BATCHTYPEADDINSPECXML as SPECXML 
    from dbo.BATCHTYPEADDINCATALOG