Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\browserasst\views\public

Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\browserasst\views\public_views.sql

begin

execute immediate 
      'CREATE OR REPLACE VIEW WB$_IV_PROJECTS AS ' ||
        'SELECT ' ||
        'project.elementid AS project_id, ' ||
        'project.name AS project_name, ' ||
        'project.logicalname AS business_name, ' ||
        'project.description AS description, ' ||
        'project.versionlabel AS version_label, ' ||       
        'v.valid AS valid, ' ||
        'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
        'project.updatetimestamp AS updated_on, ' ||
        'project.creationtimestamp AS created_on, ' ||
  'project.updatedby AS updated_by, '||
 'project.createdby AS created_by '||
        'FROM ' ||
        'cmpwbproject_v project, ' ||
        'cmpwbvalidationresult_v v ' ||
        'WHERE ' ||
        'project.validationresult = v.elementid(+) ' ||
        'WITH READ ONLY';

-- Public View Version set to 11.2 (11.1 for Tokyo, 10.1 for Paris)
   execute immediate 
        'CREATE OR REPLACE VIEW WB$_IV_INSTALLATIONS AS ' ||
        'SELECT ' ||
         'i.elementid AS installation_id, ' ||
         'i.name AS installation_name, ' ||
         'i.logicalname AS business_name, ' ||
         'i.description AS description, ' ||
         'i.installedversion AS installed_version, ' ||
         'i.release AS release, ' ||
         'i.metadataversion AS repository_model_version, ' ||
         '''11.2'' AS public_view_version, ' ||
         'i.updatetimestamp AS updated_on, ' ||
         'i.creationtimestamp AS created_on, ' ||
  'i.updatedby AS updated_by, '||
  'i.createdby AS created_by '||
        'FROM cmpinstallation_v i ' ||
        'WITH READ ONLY';

execute immediate 
   'CREATE OR REPLACE VIEW WB$_IV_OBJECTS AS ' ||
      'SELECT * FROM ( ' ||
        'SELECT ' ||
         'i.elementid AS object_id, ' ||
         'decode(i.name, ''Oracle Library'', ''Oracle Pre-Defined Transformations'', ''Global-Shared-Library'', ''Oracle Custom Transformations'', ''GENERIC_PREDEFINED_LIBRARY'', ''Heterogeneous Pre-Defined Transformations'', ''Generic-SQL-Library'', ''Generic Pre-Defined Referenced Transformation Library'', i.name) AS object_name, ' ||
         'decode(i.logicalname, ''Oracle_Library'', ''ORACLE_PREDEFINED_TRANS'', ''Global-Shared-Library'', ''ORACLE_CUSTOM_TRANS'', ''GENERIC_PREDEFINED_LIBRARY'', ''HETERO_PREDEFINED_TRANS'', i.logicalname) AS business_name,  ' ||
         'i.description AS description, ' ||
         'decode(i.strongtypename, ''oracle.wh.repos.impl.taskFlow.CMPControlKM'', ''ControlCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPLoadKM'', ''LoadCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPIntegrationKM'', ''IntegrationCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPJournalKM'', ''CDCCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPMapKM'', ''OracleTargetCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPPlatformKM'', ''FunctionCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPKMTaskFlowInstalledModule'', ''CodeTemplateFolder'', OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(i.elementid, i.classname)) AS object_type,  ' ||
         'decode(i.strongtypename, ''oracle.wh.repos.impl.taskFlow.CMPControlKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPLoadKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPIntegrationKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPJournalKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPMapKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPPlatformKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPKMTaskFlowInstalledModule'', ''CT_FOLDER'',OWM_VIEW_UTILITIES.OBJ_TYPE_TO_SCRIPTING_TYPE(OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(i.elementid, i.classname))) AS scripting_type, ' ||
         'i.updatetimestamp AS updated_on, ' ||
         'i.creationtimestamp AS created_on, ' ||
         'i.updatedby AS updated_by, '||
         'i.createdby AS created_by '||
        'FROM cmpelement_v i )' ||
      'WHERE (object_type != ''Unknown'') ' ||  
      'WITH READ ONLY';
        
  execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_FOLDERS AS ' ||
      'SELECT  ' ||
        'folder.owningfolder AS owner_id,  ' ||
        'parent.p_object_name AS owner_name,  ' ||
        'folder.elementid AS folder_id, ' ||
        'folder.name AS folder_name,  ' ||
        'folder.logicalname AS business_name,  ' ||
        'folder.description AS description,  ' ||
        'v.valid AS valid, ' ||
        'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid,  ' ||
        'folder.updatetimestamp AS updated_on, ' ||
        'folder.creationtimestamp AS created_on,  ' ||
        'folder.updatedby AS updated_by,  ' ||
        'folder.createdby AS created_by  ' ||
      'FROM cmpuserfolder_v folder, cmpwbvalidationresult_v v, (SELECT DISTINCT p_object_id, p_object_name FROM (  ' ||
         'SELECT  ' ||
          'fco.elementid AS p_object_id,  ' ||
          'fco.name AS p_object_name  ' ||
         'FROM cmpfcoelement_v fco, cmpuserfolder_v folder ' ||
         'WHERE folder.parentfolder = fco.elementid OR ' ||
               'folder.owningmodule = fco.elementid OR ' ||
               'folder.owningsubmaplibrary = fco.elementid ) ) parent  ' ||
      'WHERE folder.validationresult = v.elementid(+) AND ' ||
            '(folder.owningfolder = parent.p_object_id OR ' ||
             'folder.owningsubmaplibrary = parent.p_object_id OR ' ||
             'folder.owningmodule = parent.p_object_id) ' ||
      'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_FLD_CONTAINED_OBJECTS AS ' ||
      'SELECT ' ||
       'ref.owninguserfolder AS folder_id,  ' ||
       'owner.name AS folder_name,  ' ||
       'ref.elementid AS contained_object_id, ' ||
       'ref.name AS contained_object_name,  ' ||
       'ref.description AS description,  ' ||
       'ref.logicalname AS business_name,  ' ||
       'OWM_VIEW_UTILITIES.GET_FCO_PATH(ref.owninguserfolder) || ''.'' || fco.name AS contained_object_path,  ' ||
       'OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(fco.elementid, fco.classname) AS referenced_object_type,  ' ||
       'fco.elementid AS referenced_object_id,  ' ||
       'fco.name AS referenced_object_name,  ' ||
       'ref.contained AS contained,  ' ||
       'decode(ref.contained, 1, ''Y'', 0, ''N'', ''UNKNOWN'') AS is_contained,  ' ||
       'ref.updatetimestamp AS updated_on,  ' ||
       'ref.creationtimestamp AS created_on,  ' ||
       'ref.updatedby AS updated_by,  ' ||
       'ref.createdby AS created_by  ' ||
      'FROM cmpfcofolderreference_v ref, cmpuserfolder_v owner, firstclassobject_v fco ' ||
      'WHERE ref.owninguserfolder = owner.elementid AND ' ||
            'ref.fco = fco.elementid ' ||
     'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_FIRSTCLASS_OBJECTS AS ' ||
      'SELECT * FROM (' ||
        'SELECT ' ||
         'fco.elementid AS object_id, ' ||
         'decode(fco.name, ''Oracle Library'', ''Oracle Pre-Defined Transformations'', ''Global-Shared-Library'', ''Oracle Custom Transformations'', ''GENERIC_PREDEFINED_LIBRARY'', ''Heterogeneous Pre-Defined Transformations'', ''Generic-SQL-Library'', ''Generic Pre-Defined Referenced Transformation Library'', fco.name ) AS object_name, ' ||
         'decode(fco.logicalname, ''Oracle_Library'', ''ORACLE_PREDEFINED_TRANS'', ''Global-Shared-Library'', ''ORACLE_CUSTOM_TRANS'', ''GENERIC_PREDEFINED_LIBRARY'', ''HETERO_PREDEFINED_TRANS'', fco.logicalname) AS business_name, ' ||
         'fco.description AS description, ' ||
         'fco.classname AS classname, ' ||
         'decode(fco.classname, ''CMPWeakFirstClassObject'', ''UserDefinedFirstClassObject'', ''CMPWeakFolder'', ''UserDefinedFolder'', ''CMPTaskFlowInstalledModule'', decode(fco.strongtypename, ''oracle.wh.repos.impl.taskFlow.CMPKMTaskFlowInstalledModule'', ''CodeTemplateFolder'', OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(fco.elementid, fco.classname)), ''CMPWeakModule'', decode(fco.strongtypename, ''oracle.wh.repos.impl.application.CMPMIVInstalledModule'', OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(fco.elementid, fco.classname),  ''UserDefinedModule''), ''CMPTaskFlow'', decode(fco.strongtypename, ''oracle.wh.repos.impl.taskFlow.CMPControlKM'', ''ControlCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPLoadKM'', ''LoadCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPIntegrationKM'', ''IntegrationCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPJournalKM'', ''CDCCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPMapKM'', ''OracleTargetCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPPlatformKM'', ''FunctionCodeTemplate'', OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(fco.elementid, fco.classname)), OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(fco.elementid, fco.classname) ) AS object_type, ' ||
         'decode(fco.strongtypename, ''oracle.wh.repos.impl.taskFlow.CMPControlKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPLoadKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPIntegrationKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPJournalKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPMapKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPPlatformKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPKMTaskFlowInstalledModule'', ''CT_FOLDER'',OWM_VIEW_UTILITIES.OBJ_TYPE_TO_SCRIPTING_TYPE(OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(fco.elementid, fco.classname))) AS scripting_type, ' ||
         'fco.uoid AS object_uoid, ' ||
         'fco.owningfolder AS owningfolder, ' ||
         'fco.updatetimestamp AS updated_on, ' ||
         'fco.creationtimestamp AS created_on, ' ||
        'fco.updatedby AS updated_by, '||
        'fco.createdby AS created_by '||
        'FROM firstclassobject_v fco ' ||
        'UNION  ' ||
        'SELECT  ' ||
         'l.snapshotID AS object_id, ' ||
         'l.snapshotname AS object_name, ' ||
         'l.snapshotname AS business_name, ' ||
         ''' '' AS description, ' ||
         '''Snapshot'' AS classname, ' ||
         '''Snapshot'' AS object_type, ' ||
         '''SNAPSHOT'' AS scripting_type, ' ||
         'l.uoid AS object_uoid, ' ||
         '0 AS owningfolder, ' ||
         'TO_DATE(null) AS updated_on, ' ||
         's.creationtimestamp AS created_on, ' ||
         'TO_CHAR(null) AS updated_by, '||
         's.userName AS created_by '||
        'FROM  ' ||
         'SnapshotStoreTable s ,Snapshotlookup l' ||
        ' Where s.snapshotId = l.snapshotID' ||
        ' and s.creationtimestamp=(select min(s2.creationtimestamp) from SnapshotStoreTable s2 where s2.snapshotid=l.snapshotID) ) ' ||
      ' WHERE (object_type != ''Unknown'') ' || 
     'UNION ' ||
      'SELECT ' ||
       'folder.elementid AS object_id, ' ||
       'folder.name AS object_name, ' ||
       'folder.logicalname AS business_name, ' ||
       'folder.description AS description, ' ||
       'folder.classname AS classname,' ||
       '''Folder'' AS object_type, ' ||
       '''FOLDER'' AS scripting_type, ' ||
       'folder.uoid AS object_uoid, ' ||
       'folder.owningfolder AS owningfolder, ' ||
       'folder.updatetimestamp AS updated_on, ' ||
       'folder.creationtimestamp AS created_on, ' ||
       'folder.updatedby AS updated_by, ' ||
       'folder.createdby AS created_by ' ||
      'FROM cmpuserfolder_v folder ' ||
       'WITH READ ONLY';

execute immediate 
   'CREATE OR REPLACE VIEW WB$_IV_INFORMATION_SYSTEMS AS ' ||
        'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'sys.elementid AS information_system_id, ' ||
         'decode(sys.name, ''Oracle Library'', ''Oracle Pre-Defined Transformations'', ''Global-Shared-Library'', ''Oracle Custom Transformations'', ''GENERIC_PREDEFINED_LIBRARY'', ''Heterogeneous Pre-Defined Transformations'', sys.name) AS information_system_name, ' ||
         'decode(sys.strongtypename, ''oracle.wh.repos.impl.taskFlow.CMPKMTaskFlowInstalledModule'', ''CodeTemplateFolder'', ''oracle.wh.repos.impl.application.CMPMIVInstalledModule'', OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(sys.elementid, sys.classname), decode(sys.classname, ''CMPWeakModule'', ''UserDefinedModule'', OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(sys.elementid, sys.classname))) AS information_system_type, '||
         'decode(sys.strongtypename, ''oracle.wh.repos.impl.taskFlow.CMPKMTaskFlowInstalledModule'', ''CT_FOLDER'', OWM_VIEW_UTILITIES.OBJ_TYPE_TO_SCRIPTING_TYPE(OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(sys.elementid, sys.classname))) AS scripting_type, '||
         'decode(sys.logicalname, ''Oracle_Library'', ''ORACLE_PREDEFINED_TRANS'', ''Global-Shared-Library'', ''ORACLE_CUSTOM_TRANS'', ''GENERIC_PREDEFINED_LIBRARY'', ''HETERO_PREDEFINED_TRANS'', sys.logicalname) AS business_name, ' ||
         'sys.description AS description, ' ||
         'sys.databaselink AS database_link, ' ||
     'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'loc.elementid AS location_id, ' ||
         'loc.name AS location_name, ' ||
         'sys.status AS status, ' ||
         'sys.updatetimestamp AS updated_on, ' ||
         'sys.creationtimestamp AS created_on,  ' ||
         'sys.updatedby AS updated_by, '||
         'sys.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v project, ' ||
         'cmpinstalledmodule_v sys, ' ||
         'cmplocationusage_v lu, ' ||
         'cmplogicallocation_v loc ' ||
         'WHERE  ' ||
         'sys.name != ''Generic-SQL-Library'' AND ' ||
         'NVL(sys.strongtypename, ''NA'') != ''oracle.wh.repos.impl.application.CMPWBTMSchema'' AND ' ||
         'sys.owningproject = project.elementid AND ' ||
         'sys.elementid = lu.installedmodule(+) AND ' ||
         'NVL(lu.deploymentdefault, 1) = 1 AND ' ||
         'lu.location = loc.elementid(+) AND ' ||
         'sys.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';
 
   execute immediate 
   'CREATE OR REPLACE VIEW WB$_IV_MODULES AS ' ||
        'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'schema.elementid AS information_system_id, ' ||
         'schema.name AS information_system_name, ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'schema.logicalname AS business_name, ' ||
         'schema.description AS description, ' ||
     'schema.status AS status, '||
         'schema.platform AS platform_id, ' ||
         'plat.name AS platform_name, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'loc.elementid AS location_id, ' ||
         'loc.name AS location_name, ' ||
         'mloc.elementid AS metadata_location_id, ' ||
         'mloc.name AS metadata_location_name, ' ||
         'schema.updatetimestamp AS updated_on, ' ||
         'schema.creationtimestamp AS created_on,  ' ||
  'schema.updatedby AS updated_by, '||
  'schema.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpinstalledmodule_v schema,  ' ||
         'cmpplatform_v plat, ' ||
         'cmpwbproject_v project, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where deploymentdefault=1) lu, ' ||
         'cmplogicallocation_v loc, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where metadatasourceflag=1) mlu, ' ||
         'cmplogicallocation_v mloc ' ||
        'WHERE  ' ||
         'schema.owningproject = project.elementid AND ' ||
         'schema.platform = plat.elementid AND ' ||
         'schema.elementid = lu.installedmodule(+) AND ' ||
         'lu.location = loc.elementid(+) AND ' ||
         'schema.elementid = mlu.installedmodule(+) AND ' ||
         'mlu.location = mloc.elementid(+) AND ' ||
         'schema.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

--Updated: by Helen
   execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_DIMENSIONS AS ' ||
        'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'dim.elementid AS dimension_id, ' ||
         'dim.name AS dimension_name, ' ||
         'dim.logicalname AS business_name, ' ||
         'dim.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'dim.updatetimestamp AS updated_on, ' ||
         'dim.creationtimestamp AS created_on, ' ||
	       'dim.updatedby AS updated_by, '||
	       'dim.createdby AS created_by, '||
	       'dim.AWNAME AS AWNAME, '||
	       'dim.AWTABLESPACENAME AS AWTABLESPACENAME, '||
	       'dim.CREATECONSTRAINTS AS CREATECONSTRAINTS, '||
	       'dim.IMPLEMENTATION AS IMPLEMENTATION, '||
	       'dim.OLAPPRIMARYSORTORDER AS OLAPPRIMARYSORTORDER, '||
	       'dim.OLAPSECONDARYSORTORDER AS OLAPSECONDARYSORTORDER, '||
	       'dim.OLAPTYPE AS OLAPTYPE, '||
	       'dim.OLAPUSERVISIBLE AS OLAPUSERVISIBLE, '||
	       'dim.OWBTYPE AS OWBTYPE, '||
   	       'dim.LOADPOLICYFORINVALIDKEY AS LOADPOLICYFORINVALIDKEY, '||
	       'dim.LOADPOLICYFORNULL AS LOADPOLICYFORNULL, '||
	       'dim.REMOVEPOLICY AS REMOVEPOLICY, '||
	       'dim.SLOWLYCHANGINGTYPE AS SLOWLYCHANGINGTYPE, '||
	       'dim.STORAGEPROPERTYTYPE AS STORAGEPROPERTYTYPE, '||
	       'dim.VALUEBASED AS VALUEBASED, '||
	       'dim.DEFAULTDISPLAYHIERARCHY AS DEFAULTDISPLAYHIERARCHY, '||
	       'dim.INSTALLEDMODULE AS INSTALLEDMODULE, '||
	       'dim.TIMEDIMPOPULATIONMAP AS TIMEDIMPOPULATIONMAP, '||
	       'dim.DIMENSIONKEYSEQUENCE AS DIMENSIONKEYSEQUENCE '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpdimension_v dim, ' ||
         'cmpinstalledmodule_v schema ' ||
        'WHERE ' ||
         'schema.elementid=dim.installedmodule AND ' ||
         'dim.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';
 
   --Updated: by RUppala
   execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_DIM_ROLES AS ' ||
        'SELECT ' ||
         'dim.elementid AS dimension_id, ' ||
         'dim.name AS dimension_name, ' ||
         'role.elementid AS role_id, ' ||
         'role.name AS role_name, ' ||
         'role.logicalname AS business_name, ' ||
         'role.description AS description, ' ||
         'role.updatetimestamp AS updated_on, ' ||
         'role.creationtimestamp AS created_on,  ' ||
         'role.updatedby AS updated_by, '||
         'role.createdby AS created_by '||
        'FROM ' ||
         'cmpdimension_v dim, ' ||
         'cmpdimensionrole_v role ' ||
        'WHERE ' ||
         'dim.elementid=role.owningdimension ' ||
         'WITH READ ONLY';

--Updated: by Helen
   execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_DIM_LEVELS AS ' ||
        'SELECT  ' ||
         'dim.elementid AS dimension_id, ' ||
         'dim.name AS dimension_name, ' ||
         'l.elementid AS level_id, ' ||
         'l.name AS level_name, ' ||
         'l.logicalname AS business_name, ' ||
         'l.description AS description, ' ||
         'l.updatetimestamp AS updated_on, ' ||
         'l.creationtimestamp AS created_on,  ' ||
         'l.updatedby AS updated_by, '||
         'l.createdby AS created_by '||
        'FROM ' ||
         'cmplevel_v l, ' ||
         'cmpdimension_v dim ' ||
        'WHERE ' ||
         'dim.elementid=l.owningdimension ' ||
         'WITH READ ONLY';

--Updated: by Helen
   execute immediate 
	   'CREATE OR REPLACE VIEW WB$_IV_DIM_LEVEL_ATTRIBUTES AS ' ||
         'SELECT  ' ||
         'dim.name AS dimension_name, ' ||
         'l.elementid AS level_id, ' ||
         'l.name AS level_name, ' ||
         'a.elementid AS attribute_id, ' ||
         'a.name AS attribute_name, ' ||
         'dimatt.name as dimension_attribute, ' ||
         'dimatt.elementid as dimension_attribute_id, ' ||
         'a.logicalname AS business_name, ' ||
         'a.description AS description, ' ||
         'a.uiposition AS position, ' ||
         'd.name as data_type, ' ||
         'dimatt.length as length, ' ||
         'dimatt.precision as precision, ' ||
         'dimatt.scale as scale, ' ||
         'DECODE(dimatt.IDENTIFIERTYPE, 1, ''SURROGATE_KEY'', 2, ''BUSINESS_KEY'', 3, ''PARENT_KEY'','''') AS identifier_type, '||
         'a.updatetimestamp AS updated_on, ' ||
         'a.creationtimestamp AS created_on,  ' ||
         'a.updatedby AS updated_by, '||
         'a.createdby AS created_by '||
        'FROM  ' ||
         'cmplevel_v l, ' ||
         'cmplevelattribute_v a,  ' ||
         'cmpdimensionattribute_v dimatt,  ' ||
         'cmpdimension_v dim, ' ||
         'cmpdatatype_v d ' ||
        'WHERE  ' ||
         'dimatt.domain = d.elementid(+) AND ' ||
         'a.referenceddimensionattr = dimatt.elementid(+) AND ' ||
         'a.owninglevel = l.elementid AND ' ||
         'dim.elementid = dimatt.OWNINGDIMENSION ' ||
         'WITH READ ONLY';

--Updated: by RUppala
   execute immediate 
	   'CREATE OR REPLACE VIEW WB$_IV_DIM_ATTRIBUTES AS ' ||
         'SELECT  ' ||
         'dim.elementid AS dimension_id, ' ||
         'dim.name AS dimension_name, ' ||
         'dimatt.elementid AS attribute_id, ' ||
         'dimatt.name AS attribute_name, ' ||
         'dimatt.logicalname AS business_name, ' ||
         'dimatt.description AS description, ' ||
         'dimatt.position AS position, ' ||
         'd.name as data_type, ' ||
         'dimatt.length as length, ' ||
         'dimatt.precision as precision, ' ||
         'dimatt.scale as scale, ' ||
         'dimatt.olapdescriptor as olap_descriptor, ' ||
         'dimatt.updatetimestamp AS updated_on, ' ||
         'dimatt.creationtimestamp AS created_on,  ' ||
         'dimatt.updatedby AS updated_by, '||
         'dimatt.createdby AS created_by '||
        'FROM  ' ||
         'cmpdimensionattribute_v dimatt,  ' ||
         'cmpdatatype_v d, ' ||
         'cmpdimension_v dim ' ||
        'WHERE  ' ||
         'dimatt.domain = d.elementid(+) AND ' ||
         'dimatt.owningDimension = dim.elementid ' ||
         'WITH READ ONLY';

--Updated: by Helen
   execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_DIM_HIERARCHIES AS ' ||
        'SELECT  ' ||
         'dim.elementid AS dimension_id, ' ||
         'dim.name AS dimension_name, ' ||
         'h.elementid AS hierarchy_id, ' ||
         'h.name AS hierarchy_name, ' ||
         'h.logicalname AS business_name, ' ||
         'h.description AS description, ' ||
         'h.updatetimestamp AS updated_on, ' ||
         'h.creationtimestamp AS created_on,  ' ||
         'h.updatedby AS updated_by, '||
         'h.createdby AS created_by '||
        'FROM  ' ||
         'cmphierarchy_v h,  ' ||
         'cmpdimension_v dim ' ||
        'WHERE  ' ||
         'h.owningdimension = dim.elementid ' ||
         'WITH READ ONLY';

--Updated: by Helen
   execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_DIM_HIERARCHY_LEVELS AS ' ||
        'SELECT  ' ||
         'hlu.elementid AS level_use_id, ' ||
         'hlu.name AS level_use_name, ' ||
         'h.elementid AS hierarchy_id, ' ||
         'h.name AS hierarchy_name, ' ||
         'l.elementid AS level_id, ' ||
         'l.name AS level_name, ' ||
         'l.description AS level_description, '||
         'to_number(null) AS parent_level_id, ' ||   
         'null AS parent_level_name, ' ||           
         '(hlu.sequence + 1) AS position ' ||
        'FROM  ' ||
         'cmphierarchy_v h,  ' ||
         'cmphierarchylevelusage_v hlu, ' ||
         'cmplevel_v l ' ||
        'WHERE  ' ||
         'hlu.owninghierarchy = h.elementid AND ' ||
         'hlu.referencedlevel = l.elementid AND  ' ||
         'hlu.sequence =0  '||
      'UNION '||
        'SELECT  ' ||
         'hlu.elementid AS level_use_id, ' ||
         'hlu.name AS level_use_name, ' ||
         'h.elementid AS hierarchy_id, ' ||
         'h.name AS hierarchy_name, ' ||
         'l.elementid AS level_id, ' ||
         'l.name AS level_name, ' ||
         'l.description AS level_description, '||
         'l2.elementid AS parent_level_id, ' ||    
         'l2.name AS parent_level_name, ' ||          
         '(hlu.sequence + 1) AS position ' ||
        'FROM  ' ||
         'cmphierarchy_v h,  ' ||
         'cmphierarchylevelusage_v hlu, ' ||
         'cmplevel_v l, ' ||
         'cmphierarchylevelusage_v hlu2, ' ||
         'cmplevel_v l2 ' ||
        'WHERE  ' ||
         'hlu.owninghierarchy = h.elementid AND ' ||
         'hlu.referencedlevel = l.elementid AND ' ||
         'hlu2.owninghierarchy = h.elementid AND ' ||
         'hlu2.referencedlevel = l2.elementid AND ' ||
         'hlu2.sequence = hlu.sequence -1 ' ||
         'WITH READ ONLY';

--Updated: by Helen
    execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_CUBES AS ' ||
        'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'cube.elementid AS cube_id, ' ||
         'cube.name AS cube_name, ' ||
         'cube.logicalname AS business_name, ' ||
         'cube.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'cube.updatetimestamp AS updated_on, ' ||
         'cube.creationtimestamp AS created_on, ' ||
         'cube.updatedby AS updated_by, '||
         'cube.createdby AS created_by, '||
         'cube.AUTOSOLVE AS AUTOSOLVE, '||
         'cube.AWNAME AS AWNAME, '||
         'cube.AWTABLESPACENAME AS AWTABLESPACENAME, '||
         'cube.COMPRESSED AS COMPRESSED, '||
         'cube.CREATEBITMAPS AS CREATEBITMAPS, '||
         'cube.CREATECONSTRAINTS AS CREATECONSTRAINTS, '||
         'cube.IMPLEMENTATION AS IMPLEMENTATION, '||
         'cube.OLAPUSERVISIBLE AS OLAPUSERVISIBLE, '||
         'cube.STORAGEPROPERTYTYPE AS STORAGEPROPERTYTYPE, '||
         'cube.USEGLOBALINDEX AS USEGLOBALINDEX, '||
         'cube.PARTITIONHIERARCHY AS PARTITIONHIERARCHY, '||
         'cube.INSTALLEDMODULE AS INSTALLEDMODULE, '||
         'cube.PARTITIONLEVEL AS PARTITIONLEVEL, '||
	 'cube.LOADPOLICYFORINVALIDKEY AS LOADPOLICYFORINVALIDKEY, '||
         'cube.LOADPOLICYFORNULL AS LOADPOLICYFORNULL, '||
         'cube.BINDINGFACT AS BINDINGFACT '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpcube_v cube, ' ||
         'cmpinstalledmodule_v schema ' ||
        'WHERE ' ||
         'schema.elementid=cube.installedmodule AND ' ||
         'cube.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

--Updated: by Helen
    execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_CUBE_MEASURES AS ' ||
        'SELECT  ' ||
         'cube.elementid AS cube_id, ' ||
         'cube.name AS cube_name, ' ||
         'a.elementid AS measure_id, ' ||
         'a.name AS measure_name, ' ||
         'a.logicalname AS business_name, ' ||
         'a.description AS description, ' ||
         'a.position AS position, ' ||
         'd.name AS data_type, ' ||
         'a.length AS length, ' ||
         'a.precision AS precision, ' ||
         'a.scale AS scale, ' ||
         'a.updatetimestamp AS updated_on, ' ||
         'a.creationtimestamp AS created_on, ' ||
         'a.updatedby AS updated_by, '||
         'a.createdby AS created_by, '||
         'tab.elementid AS table_id, ' ||
         'tab.name AS table_name ' ||
        'FROM  ' ||
         'cmpcube_v cube,  ' ||
         'CMPRELATION_V tab, ' || 
         'cmpcubemeasure_v a, ' ||
         'cmpdatatype_v d  ' ||
        'WHERE  ' ||
         'a.owningcube = cube.elementid AND ' ||
         'cube.BINDINGFACT = tab.elementid AND ' ||
         'd.elementid = a.domain ' ||
         'WITH READ ONLY';
 
  --Updated: by Helen
  execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_CUBE_MEASURE_DIM_USES AS ' ||
        'SELECT ' ||
         'cube.elementid AS cube_id, ' ||
         'cube.name AS cube_name, ' ||
         'a.elementid AS measure_id, ' ||
         'a.name AS measure_name, ' ||
         'to_number(null) AS dimension_id, ' ||
         'null AS dimension_name, ' ||
         'null AS dimension_alias ' ||
        'FROM  ' ||
         'cmpcube_v cube, ' ||
         'cmpcubemeasure_v a ' ||
        'WHERE  ' ||
         'cube.elementid = a.OWNINGCUBE ' ||
        'UNION ' ||
        'SELECT ' ||
         'cube.elementid AS cube_id, ' ||
         'cube.name AS cube_name, ' ||
         'to_number(null) AS measure_id, ' ||
         'null AS measure_name, ' ||
         'dim.elementid AS dimension_id, ' ||
         'dim.name AS dimension_name, ' ||
         'key.name AS dimension_alias ' ||
        'FROM  ' ||
         'cmpdimension_v dim, ' ||
         'cmpcubedimreference_v key, ' ||
         'cmpcube_v cube ' ||         
        'WHERE ' ||
         'cube.elementid = key.OWNINGCUBE AND ' ||
         'key.REFERENCEDDIMENSION = dim.elementid ' ||
         'WITH READ ONLY';

--Updated: by Helen
  execute immediate 
	'CREATE OR REPLACE VIEW WB$_IV_CUBE_DIMENSIONS AS ' ||
        'SELECT ' ||
         'cube.elementid AS cube_id, ' ||
         'cube.name AS cube_name, ' ||
         'dim.elementid AS dimension_id, ' ||
         'dim.name AS dimension_name, ' ||
         'key.name AS dimension_alias ' ||
        'FROM  ' ||
         'cmpdimension_v dim, ' ||
         'cmpcubedimreference_v key, ' ||
         'cmpcube_v cube ' ||         
        'WHERE ' ||
         'cube.elementid = key.OWNINGCUBE AND ' ||
         'key.REFERENCEDDIMENSION = dim.elementid ' ||
         'WITH READ ONLY';

--Updated: by Helen
  execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_CUBE_IMPLS AS ' ||
        'SELECT DISTINCT ' ||
         'cube_id AS implementation_id, ' ||
         'measure_id AS item_id, ' ||
         '''MEASURE'' AS item_type, ' ||
         'measure_name AS item_name,  ' ||
         'cube_id AS cube_id, ' ||
         'cube_name AS cube_name, ' ||
         'to_number(null) AS dimension_id, ' ||
         'null AS dimension_name, ' ||
         'null AS dimension_alias, ' ||
         'measure_id AS column_id, ' ||
         'measure_name AS column_name, ' ||
         'position AS position, ' ||
         'table_id AS table_id, ' ||
         'table_name AS table_name, ' ||
         'to_number(null) AS foreign_key_id, ' ||
         'null AS foreign_key_name, ' ||
         'to_number(null) dim_implementation_id ' ||
        'FROM ' ||
         'WB$_IV_CUBE_MEASURES ' ||        
       'UNION ' ||     
        'SELECT DISTINCT ' ||
         'cube.elementid AS implementation_id, ' ||
         'key.elementid AS item_id, ' ||
         '''CUBE_DIMENSION_USE'' AS item_type, ' ||
         'key.name AS item_name,  ' ||
         'cube.elementid AS cube_id, ' ||
         'cube.name AS cube_name, ' ||
         'dim.elementid AS dimension_id, ' ||
         'dim.name AS dimension_name, ' ||
         'key.name AS dimension_alias,  ' ||
         'att.elementid AS column_id, ' ||
         'att.name AS column_name, ' ||
         'to_number(null) AS position, ' ||
         'tab.elementid AS table_id, ' ||
         'tab.name AS table_name, ' ||
         'fkey.elementid AS foreign_key_id, ' ||
         'fkey.name AS foreign_key_name, ' ||
         'to_number(null) dim_implementation_id ' ||
        'FROM ' ||
         'cmpdimension_v dim,  ' ||
         'cmpcubedimreference_v key, ' ||      
         'cmpcube_v cube, ' ||       
         'CMPRELATION_V tab, ' || 
         'cmpforeignkey_v fkey, ' ||
         'cmpattribute_v att ' || 
        'WHERE  ' ||
         'cube.elementid = key.OWNINGCUBE AND ' ||
         'cube.BINDINGFACT = tab.elementid AND ' ||
         'key.REFERENCEDDIMENSION = dim.elementid AND ' ||
         'key.BINDINGFOREIGNKEY = fkey.elementid(+) AND ' ||
         'key.BINDINGATTRIBUTE = att.elementid(+) ' ||
         'WITH READ ONLY';

--Updated: by Helen
   execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_DIM_LEVEL_IMPLS AS ' ||
        'SELECT DISTINCT ' ||
         'i.elementid AS implementation_id, ' ||
         'i.elementid AS item_id, ' ||
         '''LEVEL_ATTRIBUTE'' AS item_type, ' ||
         'i.name AS item_name,  ' ||
         'd.elementid AS dimension_id, ' ||
         'd.name AS dimension_name, ' ||
         'l.elementid AS level_id, ' ||
         'l.name AS level_name, ' ||
         'att.elementid AS column_id, ' ||
         'att.name AS column_name, ' ||
         'tab.elementid AS table_id, ' ||
         'tab.name AS table_name ' ||
        'FROM ' ||
         'cmpdimension_v d, ' ||
         'cmplevel_v l, ' ||
         'CMPRELATION_V tab, ' || 
         'cmplevelattribute_v i, ' ||
         'CMPAttribute_V att ' ||
        'WHERE  ' ||
         'i.OWNINGLEVEL = l.elementid AND ' ||
         'l.owningdimension = d.elementid AND ' ||
         'i.BINDINGATTRIBUTE = att.elementid AND ' ||
         'l.BINDINGRELATION = tab.elementid ' ||
         'WITH READ ONLY';

--Updated: by Helen
   execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_DIM_IMPLS AS ' ||
        'SELECT DISTINCT ' ||
         'i.elementid AS implementation_id, ' ||
         'l.elementid AS level_id, ' ||
         'd.elementid AS dimension_id, ' ||
         'd.name AS dimension_name, ' ||
         'l.name AS level_name, ' ||
         'att.name AS dimensionkey_column, ' ||
         'i.name AS table_name ' ||
        'FROM ' ||
         'cmpdimension_v d, ' ||
         'cmplevel_v l, ' ||
         'CMPAttribute_V att, ' ||
         'cmprelation_v i ' ||
        'WHERE  ' ||
	 'd.elementid = l.owningdimension AND ' ||
         'att.elementid = l.bindingdimensionkeyattr AND ' ||
         'l.bindingrelation = i.elementid ' ||
         'WITH READ ONLY';
 
    execute immediate
         'CREATE OR REPLACE VIEW WB$_IV_TABLES AS ' ||
         'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'tab.elementid AS table_id, ' ||
         'tab.name AS table_name, ' ||
         'tab.logicalname AS business_name, ' ||
         'tab.description AS description, ' ||
         'upper(pk.PARTITIONTYPE) AS PARTITIONing_TYPE, ' ||
         'pk.HASHPARTITIONQUANTITY AS HASH_PARTITION_QUANTITY, ' ||
         'pk.ATTRIBUTE2 AS partitioning_interval, ' ||
         'pk2.HASHPARTITIONQUANTITY AS TEMPLATE_HASH_QUANTITY, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'tab.updatetimestamp AS updated_on, ' ||
         'tab.creationtimestamp AS created_on, ' ||
         'tab.updatedby AS updated_by, '||
         'tab.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelation_v tab, ' ||
         'firstclassobject_v fco, '||
         'CMPPARTITIONKEY_V pk, ' ||
         'CMPPARTITIONKEY_V pk2 ' ||
        'WHERE ' ||
         'pk.owningrelation(+) = tab.elementid AND ' ||
         'pk.ISSUBPARTITIONKEY(+) <> ''1'' AND ' ||
         'pk2.owningrelation(+) = tab.elementid AND ' ||
         'pk2.ISSUBPARTITIONKEY(+) = ''1'' AND ' ||
         'fco.classname in (''CMPWBTable'', ''CMPWBDimension'', ''CMPWBCube'') AND '||
         'schema.elementid=tab.installedmodule AND ' ||
         'tab.elementid = fco.elementid AND '||
         'fco.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate
         'CREATE OR REPLACE VIEW WB$_IV_RELATIONS AS ' ||
         'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'rel.elementid AS relation_id, ' ||
         'rel.name AS relation_name, ' ||
         'rel.logicalname AS business_name, ' ||
         'rel.description AS description, ' ||
         'upper(pk.PARTITIONTYPE) AS PARTITIONing_TYPE, ' ||
         'pk.HASHPARTITIONQUANTITY AS HASH_PARTITION_QUANTITY, ' ||
         'pk.ATTRIBUTE2 AS partitioning_interval, ' ||
         'pk2.HASHPARTITIONQUANTITY AS TEMPLATE_HASH_QUANTITY, ' ||
         'OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(rel.elementid, rel.classname) AS relation_type, '||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'rel.updatetimestamp AS updated_on, ' ||
         'rel.creationtimestamp AS created_on, ' ||
         'rel.updatedby AS updated_by, '||
         'rel.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelation_v rel, ' ||
         'firstclassobject_v fco, '||
         'CMPPARTITIONKEY_V pk, ' ||
         'CMPPARTITIONKEY_V pk2 ' ||
        'WHERE ' ||
         'pk.owningrelation(+) = rel.elementid AND ' ||
         'pk.ISSUBPARTITIONKEY(+) <> ''1'' AND ' ||
         'pk2.owningrelation(+) = rel.elementid AND ' ||
         'pk2.ISSUBPARTITIONKEY(+) = ''1'' AND ' ||
         'fco.classname in (''CMPWBTable'', ''CMPWBDimension'', ''CMPWBCube'', ' ||
         '''CMPAdvancedQueue'', ''CMPExternalTable'', ''CMPWBView'', ''CMPWBSequence'', ''CMPWBMaterializedView'') AND '||
         'schema.elementid=rel.installedmodule AND ' ||
         'rel.elementid = fco.elementid AND '||
         'fco.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_VIEWS AS ' ||
        'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'theview.elementid AS view_id, ' ||
         'theview.name AS view_name, ' ||
         'theview.logicalname AS business_name, ' ||
         'theview.description AS description, ' ||
				 'theview.selectquery AS query_text, '||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'theview.updatetimestamp AS updated_on, ' ||
         'theview.creationtimestamp AS created_on, ' ||
				 'theview.updatedby AS updated_by, '||
				 'theview.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmpwbview_v theview ' ||
        'WHERE ' ||
         'schema.elementid = theview.installedmodule AND ' ||
         'theview.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';         
 
    execute immediate
          'CREATE OR REPLACE VIEW WB$_IV_MATERIALIZED_VIEWS AS ' ||
        'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'matview.elementid AS view_id, ' ||
         'matview.name AS view_name, ' ||
         'matview.logicalname AS business_name, ' ||
         'matview.description AS description, ' ||
                                 'matview.selectquery AS query_text, '||
         'upper(pk.PARTITIONTYPE) AS PARTITIONing_TYPE, ' ||
         'pk.HASHPARTITIONQUANTITY AS HASH_PARTITION_QUANTITY, ' ||
         'pk.ATTRIBUTE2 AS partitioning_interval, ' ||
         'pk2.HASHPARTITIONQUANTITY AS TEMPLATE_HASH_QUANTITY, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'matview.updatetimestamp AS updated_on, ' ||
         'matview.creationtimestamp AS created_on, ' ||
                                 'matview.updatedby AS updated_by, '||
                                 'matview.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmpwbmaterializedview_v matview, ' ||
         'CMPPARTITIONKEY_V pk, ' ||
         'CMPPARTITIONKEY_V pk2 ' ||
        'WHERE  ' ||
         'pk.owningrelation(+) = matview.elementid AND ' ||
         'pk.ISSUBPARTITIONKEY(+) <> ''1'' AND ' ||
         'pk2.owningrelation(+) = matview.elementid AND ' ||
         'pk2.ISSUBPARTITIONKEY(+) = ''1'' AND ' ||
         'schema.elementid = matview.installedmodule AND ' ||
         'matview.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_SEQUENCES AS ' ||
        'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'seq.elementid AS sequence_id, ' ||
         'seq.name AS sequence_name, ' ||
         'seq.logicalname AS business_name, ' ||
         'seq.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid,  ' ||
         'seq.updatetimestamp AS updated_on, ' ||
         'seq.creationtimestamp AS created_on, ' ||
				 'seq.updatedby AS updated_by, '||
				 'seq.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbsequence_v seq, ' ||
         'cmpinstalledmodule_v schema ' ||
        'WHERE ' ||
         'schema.elementid=seq.installedmodule AND ' ||
         'seq.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_CHECK_CONSTRAINTS AS ' ||
        'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'rel.elementid AS relation_id, ' ||
         'rel.name AS relation_name, ' ||
         'chk.elementid AS constraint_id, ' ||
         'chk.name AS constraint_name, ' ||
         'chk.logicalname AS business_name, ' ||
         'chk.description AS description, ' ||
	 'chk.searchcondition AS constraint_text, '||
         'chk.updatetimestamp AS updated_on, ' ||
         'chk.creationtimestamp AS created_on, ' ||
	 'chk.updatedby AS updated_by, '||
	 'chk.createdby AS created_by '||
        'FROM ' ||
         'cmpinstalledmodule_v schema, ' ||
	 'cmprelation_v rel, '||
         'cmpwbcheckconstraint_v chk ' ||
        'WHERE  ' ||
         'schema.elementid = rel.installedmodule AND ' ||
	 'rel.elementid = chk.owningrelation '||
         'WITH READ ONLY'; 
         
    execute immediate
	  'CREATE OR REPLACE VIEW WB$_IV_CONSTRAINTS AS ' ||
        'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'rel.elementid AS relation_id, ' ||
         'rel.name AS relation_name, ' ||
         'chk.elementid AS constraint_id, ' ||
         'chk.name AS constraint_name, ' ||
         '''CHECK_CONSTRAINT'' AS constraint_type, ' ||
         'chk.logicalname AS business_name, ' ||
         'chk.description AS description, ' ||
         'chk.updatetimestamp AS updated_on, ' ||
         'chk.creationtimestamp AS created_on, ' ||
	 'chk.updatedby AS updated_by, '||
	 'chk.createdby AS created_by '||
        'FROM ' ||
         'cmpinstalledmodule_v schema, ' ||
	 'cmprelation_v rel, '||
         'cmpwbcheckconstraint_v chk ' ||
        'WHERE  ' ||
         'schema.elementid = rel.installedmodule AND ' ||
	 'rel.elementid = chk.owningrelation '||
	'UNION ' ||
        'SELECT ' ||
	 'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'relation.elementid AS relation_id, ' ||
         'relation.name AS relation_name, ' ||
         'key.elementid AS constraint_id, ' ||
         'key.name AS constraint_name, ' ||
         '''KEY_CONSTRAINT'' AS constraint_type, ' ||
         'key.logicalname AS business_name, ' ||
         'key.description AS description, ' ||
         'key.updatetimestamp AS updated_on, ' ||
         'key.creationtimestamp AS created_on, ' ||
	 'key.updatedby AS updated_by, '||
	 'key.createdby AS created_by '||
        'FROM  ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelation_v relation, ' ||
         'cmpuniquekey_v key ' ||
        'WHERE  ' ||
         'key.owningrelation = relation.elementid AND ' ||
         'relation.installedmodule = schema.elementid(+) ' ||
        'UNION ' ||
        'SELECT ' ||
	 'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'relation.elementid AS relation_id, ' ||
         'relation.name AS relation_name, ' ||
         'fkey.elementid AS constraint_id, ' ||
         'fkey.name AS constraint_name, ' ||
         '''FOREIGNKEY_CONSTRAINT'' AS constraint_type, ' ||
         'fkey.logicalname AS business_name, ' ||
         'fkey.description AS description, ' ||
         'fkey.updatetimestamp AS updated_on, ' ||
         'fkey.creationtimestamp AS created_on,  ' ||
	 'fkey.updatedby AS updated_by, '||
	 'fkey.createdby AS created_by '||
        'FROM  ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmpuniquekey_v key, ' ||
         'cmprelation_v relation, ' ||
         'cmpforeignkey_v fkey ' ||
        'WHERE  ' ||
         'fkey.owningrelation = relation.elementid AND ' ||
         'fkey.uniquekey = key.elementid(+) AND ' ||
         'relation.installedmodule = schema.elementid ' ||
         'WITH READ ONLY';
 
    execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_KEYS AS ' ||
        'SELECT ' ||
	 'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'relation.elementid AS entity_id, ' ||
         'OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE(relation.classname) AS entity_type, ' ||
         'relation.name AS entity_name, ' ||
         'key.elementid AS key_id, ' ||
         'key.name AS key_name, ' ||
         'key.logicalname AS business_name, ' ||
         'key.description AS description, ' ||
				 'key.primarykey AS primary, '||
				 'decode(key.primarykey, 1, ''Y'', 0, ''N'', ''Not Known'') AS is_primary, '||
         'key.updatetimestamp AS updated_on, ' ||
         'key.creationtimestamp AS created_on, ' ||
	 'key.updatedby AS updated_by, '||
	 'key.createdby AS created_by '||
        'FROM  ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelation_v relation, ' ||
         'cmpuniquekey_v key ' ||
        'WHERE  ' ||
         'key.owningrelation = relation.elementid AND ' ||
         'relation.installedmodule = schema.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_FOREIGN_KEYS AS ' ||
        'SELECT ' ||
	 'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'relation.elementid AS entity_id, ' ||
         'OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE(relation.classname) AS entity_type, ' ||
         'relation.name AS entity_name, ' ||
         'fkey.name AS foreign_key_name, ' ||
         'fkey.elementid AS foreign_key_id, ' ||
         'fkey.logicalname AS business_name, ' ||
         'fkey.description AS description, ' ||
         'key.elementid AS key_id, ' ||
         'key.name AS key_name, ' ||
         'fkey.updatetimestamp AS updated_on, ' ||
         'fkey.creationtimestamp AS created_on,  ' ||
	 'fkey.updatedby AS updated_by, '||
	 'fkey.createdby AS created_by '||
        'FROM  ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmpuniquekey_v key, ' ||
         'cmprelation_v relation, ' ||
         'cmpforeignkey_v fkey ' ||
        'WHERE  ' ||
         'fkey.owningrelation = relation.elementid AND ' ||
         'fkey.uniquekey = key.elementid(+) AND ' ||
         'relation.installedmodule = schema.elementid ' ||
         'WITH READ ONLY';         
 
    execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_KEY_COLUMN_USES AS ' ||
        'SELECT  ' ||
         'key.elementid AS key_id, ' ||
         'OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE(key.classname) AS key_type, ' ||
         'key.name AS key_name, ' ||
         'col.elementid AS column_id,  ' ||
         'col.name AS column_name, ' ||
         'setu.sequence AS position ' ||
        'FROM  ' ||
         'cmpitemset_v  key, ' ||
         'cmprelationaldbitem_v col, ' ||
         'cmpwbitemsetusage_v setu ' ||
        'WHERE  ' ||
         'key.classname in (''CMPUniqueKey'', ''CMPForeignKey'') AND '||
         'setu.attribute = col.elementid AND ' ||
         'key.elementid = setu.itemset ' ||
         'WITH READ ONLY';         
 
   execute immediate 
  	  'CREATE OR REPLACE VIEW WB$_IV_COLUMNS AS ' ||
         'SELECT ' ||
         'entity.elementid AS entity_id, ' ||
         'OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE(entity.classname) AS entity_type, ' ||
         'entity.name AS entity_name, ' ||
         'col.elementid AS column_id, ' ||
         'col.name AS column_name, ' ||
         'col.logicalname AS business_name, ' ||
         'decode(nvl(col.nullable, ''0''), ''0'', ''Y'', ''N'') AS nullable, ' ||
         'col.description AS description, ' ||
         'col.position AS position, ' ||
         'dt.data_type AS data_type, ' ||
         'decode(instr(dt.data_type, ''.''), ''0'', decode(col.length, ''0'', null, col.length)) AS length, ' ||
         'decode(col.length, ''0'', decode(instr(dt.data_type, ''.''), ''0'', decode(col.precision, ''0'',null,col.precision))) AS precision, ' ||
         'decode(col.length, ''0'', decode(instr(dt.data_type, ''.''), ''0'', decode(col.scale,''0'',null, col.scale))) AS scale, ' ||
         'decode(col.virtual, 1, ''Y'', ''N'') AS is_virtual_column, ' ||
         'col.expression AS virtual_column_expression, ' ||
         'col.updatetimestamp AS updated_on, ' ||
         'col.creationtimestamp AS created_on, ' ||
         'col.updatedby AS updated_by, ' ||
         'col.createdby AS created_by ' ||
        'FROM  ' ||
          '(SELECT ' ||
           'dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmpdatatype_v dt ' || 
          'UNION ' ||
           'SELECT ' ||
           'mod.name || ''.'' || dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmpobjecttype_v dt, ' ||
           'cmpinstalledmodule_v mod ' ||
           'WHERE ' ||
           'dt.installedmodule = mod.elementid ' ||
          'UNION ' ||
           'SELECT ' ||
           'mod.name || ''.'' || dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmpsqlcollection_v dt, ' ||
           'cmpinstalledmodule_v mod ' ||
           'WHERE ' ||
           'dt.owningmodule = mod.elementid ' ||
          'UNION ' ||
           'SELECT ' ||
           'dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmprefcursortype_v dt ' ||
          'UNION ' ||
           'SELECT ' ||
           'mod.name || ''.'' || func.name || ''.'' || dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmpplscollection_v dt, ' ||
           'cmpfunctioncategory_v func, ' ||
           'cmpinstalledmodule_v mod ' ||
           'WHERE ' ||
           'dt.functioncategory = func.elementid AND ' ||
           'func.installedmodule = mod.elementid ' ||
          'UNION ' ||
           'SELECT ' ||
           'mod.name || ''.'' || func.name || ''.'' || dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmpplsrecord_v dt, ' ||
           'cmpfunctioncategory_v func, ' ||
           'cmpinstalledmodule_v mod ' ||
           'WHERE ' ||
           'dt.functioncategory = func.elementid AND ' ||
           'func.installedmodule = mod.elementid ) dt, ' ||  
         'cmprelation_v entity, ' ||
         'cmpitem_v col ' ||
        'WHERE ' ||
         'col.classname in (''CMPRelationalDBItem'',''CMPWBCubeAttribute'', ''CMPExternalTableItem'') AND '||
         'col.owningrelation = entity.elementid AND ' ||
         'col.typedefinition = dt.type_id ' ||
         'WITH READ ONLY';       
 
    execute immediate 
   'CREATE OR REPLACE VIEW WB$_IV_FILES AS ' ||
        'SELECT ' ||
         'schema.elementid AS information_system_id, ' ||
         'schema.name AS information_system_name, ' ||
         'f.elementid AS file_id, ' ||
         'f.name AS file_name, ' ||
         'f.logicalname AS business_name, ' ||
         'f.description AS description, ' ||
         'f.fixedwidth AS fixedwidth, ' ||
         'decode(f.fixedwidth, ''1'', ''FixedWidth'', ''Delimited'') AS file_format, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'f.recordclassifierposition AS record_classifier_position, ' ||
         'f.recordclassifierlength AS record_classifier_length, ' ||
         'f.physicalrecordsize AS physicalrecordsize, ' ||
         'decode(f.physicalrecordsize, ''0'', ''(not available)'', f.physicalrecordsize) AS record_size, ' ||
         'decode(f.physicalrecordnumber, ''0'', ''1'', f.physicalrecordnumber) AS n_physical_records_in_logical, ' ||
         'f.continuationcharonnextline AS continuation_at_end, ' ||
         'f.linecontinuationchar AS continuation_delimiter, ' ||
         'decode(f.physicalrecordsize, ''0'', f.recorddelimiter, ''(not available)'') AS record_delimiter, ' ||
         'decode(f.fixedwidth, ''1'', ''(not available)'', f.fielddelimiter) AS field_delimiter, '||
         'substr(f.stringenclosure, 1, 1) AS text_start_delimiter, ' ||
         'substr(f.stringenclosure, 2, 1) AS text_end_delimiter,  ' ||
         'f.sourcefrom AS source_from, ' ||
         'f.characterset AS characterset, ' ||
         'f.skiprecords AS skiprecords, ' || 
         'to_char(f.copybooksource) AS copybooksource, ' ||
         'decode(to_char(f.copybooksource), NULL, ''0'', ''1'') AS is_imported_copybook, ' ||   
         'f.updatetimestamp AS updated_on, ' ||
         'f.creationtimestamp AS created_on, ' ||
				 'f.updatedby AS updated_by, '||
				 'f.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v,' ||
         'cmpwbfileinstalledmodule_v schema, ' ||
         'cmpwbfile_v f ' ||
        'WHERE  ' ||
         'f.installedmodule = schema.elementid AND ' ||
         'f.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';         
 
   execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_RECORDS AS ' ||
        'SELECT  ' ||
         'f.elementid AS file_id, ' ||
         'f.name AS file_name, ' ||
         'r.elementid AS record_id, ' ||
         'r.name AS record_name, ' ||
         'r.logicalname AS business_name, ' ||
         'r.description AS description, ' || 
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'r.updatetimestamp AS updated_on, ' ||
         'r.creationtimestamp AS created_on, ' ||
				 'r.updatedby AS updated_by, '||
				 'r.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmprecord_v r,  ' ||
         'cmpwbfile_v f ' ||
        'WHERE  ' ||
         'r.owningfile = f.elementid AND ' ||
         'f.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';         
 
    execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_FIELDS AS ' ||
        'SELECT ' ||
         'rec.elementid AS record_id, ' ||
         'rec.name AS record_name, ' ||
         'fld.elementid AS field_id, ' ||
         'fld.name AS field_name, ' ||
         'fld.logicalname AS business_name, ' ||
         'fld.description AS description, ' ||
         'fld.position AS position, ' ||
         'type.name AS data_type, ' ||
         'fld.fieldlength AS length, ' ||
         'fld.precision AS precision, ' ||
         'fld.scale AS scale,     ' ||
         'fld.formatmask AS mask, ' ||
         'fld.nullifvalue AS nullif, ' ||
         'fld.defaultifvalue AS defaultif, ' ||
         'fld.sqldatatype AS sql_data_type, ' ||
         'fld.sqllength AS sql_length, ' ||
         'fld.sqlprecision AS sql_precision, ' ||
         'fld.sqlscale AS sql_scale, ' ||
         'decode(fld.startposition, ''0'', ''(not available)'', fld.startposition) AS start_position, ' ||
         'decode(fld.endposition, ''0'', ''(not available)'', fld.endposition) AS end_position, ' ||
         'fld.updatetimestamp AS updated_on, ' ||
         'fld.creationtimestamp AS created_on, ' ||
	 'fld.updatedby AS updated_by, '||
	 'fld.createdby AS created_by '||
        'FROM  ' ||
         'cmpdatatype_v type, ' ||
         'cmprecord_v rec, ' ||         
         'cmpwbfield_v fld ' ||         
        'WHERE  ' ||
         'rec.elementid = fld.owningrelation AND ' ||
         'fld.typedefinition = type.elementid ' ||
         'WITH READ ONLY';

execute immediate 
     'CREATE OR REPLACE VIEW WB$_IV_ALL_OBJECTS AS ' ||
      'SELECT * FROM ( ' ||
        'SELECT  ' ||
         'e.elementid AS object_id, ' ||
         'e.uoid AS object_UOID, '||
         'decode(e.strongtypename, ''oracle.wh.repos.impl.taskFlow.CMPKMTaskFlowInstalledModule'', ''CodeTemplateFolder'', ''oracle.wh.repos.impl.taskFlow.CMPLoadKM'', ''LoadCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPIntegrationKM'', ''IntegrationCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPJournalKM'', ''CDCCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPMapKM'', ''OracleTargetCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPPlatformKM'', ''FunctionCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPControlKM'', ''ControlCodeTemplate'', OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(e.elementid, e.classname)) AS object_type,  ' ||
         'decode(e.strongtypename, ''oracle.wh.repos.impl.taskFlow.CMPLoadKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPIntegrationKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.JournalKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPMapKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPPlatformKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPKMTaskFlowInstalledModule'', ''CT_FOLDER'',OWM_VIEW_UTILITIES.OBJ_TYPE_TO_SCRIPTING_TYPE(OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(e.elementid, e.classname))) AS scripting_type, ' ||
         'decode(e.name, ''Oracle Library'', ''Oracle Pre-Defined Transformations'', ''Global-Shared-Library'', ''Oracle Custom Transformations'', ''GENERIC_PREDEFINED_LIBRARY'', ''Heterogeneous Pre-Defined Transformations'', ''Generic-SQL-Library'', ''Generic Pre-Defined Referenced Transformation Library'', e.name) AS object_name, ' ||
         'decode(e.logicalname, ''Oracle_Library'', ''ORACLE_PREDEFINED_TRANS'', ''Global-Shared-Library'', ''ORACLE_CUSTOM_TRANS'', ''GENERIC_PREDEFINED_LIBRARY'', ''HETERO_PREDEFINED_TRANS'', e.logicalname) AS business_name, ' ||
          -- bug 4335320 method kills performance 
          -- 'OWM_VIEW_UTILITIES.CONTEXT_NAME(e.elementid) AS context_name,  ' ||
         'e.description AS description, ' ||
         'parent.elementid AS parent_object_id,  ' ||
         'decode(parent.strongtypename, ''oracle.wh.repos.impl.taskFlow.CMPKMTaskFlowInstalledModule'', ''CodeTemplateFolder'', ''oracle.wh.repos.impl.taskFlow.CMPLoadKM'', ''LoadCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPIntegrationKM'', ''IntegrationCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPJournalKM'', ''CDCCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPMapKM'', ''OracleTargetCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPPlatformKM'', ''FunctionCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPControlKM'', ''ControlCodeTemplate'', OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(parent.elementid, parent.classname)) AS parent_object_type,  ' ||
         'decode(parent.strongtypename, ''oracle.wh.repos.impl.taskFlow.CMPLoadKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPIntegrationKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.JournalKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPMapKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPPlatformKM'', ''CT'', ''oracle.wh.repos.impl.taskFlow.CMPKMTaskFlowInstalledModule'', ''CT_FOLDER'',OWM_VIEW_UTILITIES.OBJ_TYPE_TO_SCRIPTING_TYPE(OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(parent.elementid, parent.classname))) AS parent_scripting_type, ' ||
         'parent.name AS parent_object_name,   ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'e.updatetimestamp AS updated_on, ' ||
         'e.creationtimestamp AS created_on, ' ||
	       'e.updatedby AS updated_by, '||
	       'e.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         -- 'cmpelement_v e, ' ||
         '( ' ||
            '(SELECT STRONGTYPENAME, ELEMENTID, OWNINGFOLDER PARENT, UOID, CLASSNAME, NAME, LOGICALNAME, DESCRIPTION, UPDATETIMESTAMP, CREATIONTIMESTAMP, UPDATEDBY, CREATEDBY ' ||
            ' FROM FIRSTCLASSOBJECT_V WHERE CLASSNAME != ''CMPACLContainer'') ' ||
             'UNION ' ||
            '(SELECT STRONGTYPENAME, ELEMENTID, FIRSTCLASSOBJECT PARENT, UOID, CLASSNAME, NAME, LOGICALNAME, DESCRIPTION, UPDATETIMESTAMP, CREATIONTIMESTAMP, UPDATEDBY, CREATEDBY ' ||
            ' FROM SECONDCLASSOBJECT_V) ' ||
         ') e, ' ||
         'cmpelement_v parent ' ||
        'WHERE ' ||
         'e.elementid=v.validatingelement(+) AND ' ||
         -- 'e.classname!=''CMPACLContainer'' AND ' ||
         'e.parent = parent.elementid(+) ' ||
       ') ' ||
       'WHERE (object_type != ''Unknown'') ' ||
       'UNION ' ||
        'SELECT ' ||
         'folder.elementid AS object_id, ' ||
         'folder.uoid AS object_UOID, ' ||
         '''Folder'' AS object_type, ' ||
         '''FOLDER'' AS scripting_type, ' ||
         'folder.name AS object_name, ' ||
         'folder.logicalname AS business_name, ' ||
         'folder.description AS description, ' ||
         'parent.p_object_id AS parent_object_id, ' ||
         'parent.p_object_type AS parent_object_type , ' ||
         'parent.p_scripting_type AS parent_scripting_type, ' ||
         'parent.p_object_name AS parent_object_name, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'folder.updatetimestamp AS updated_on, ' ||
         'folder.creationtimestamp AS created_on, ' ||
         'folder.updatedby AS updated_by, ' ||
         'folder.createdby AS created_by ' ||
        'FROM cmpuserfolder_v folder, (SELECT * FROM ( ' ||
         'SELECT ' ||
          'module.elementid AS p_object_id, ' ||
         'decode(module.strongtypename, ''oracle.wh.repos.impl.taskFlow.CMPKMTaskFlowInstalledModule'', ''CodeTemplateFolder'', OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(module.elementid, module.classname)) AS p_object_type,  ' ||
         'decode(module.strongtypename, ''oracle.wh.repos.impl.taskFlow.CMPKMTaskFlowInstalledModule'', ''CT_FOLDER'',OWM_VIEW_UTILITIES.OBJ_TYPE_TO_SCRIPTING_TYPE(OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(module.elementid, module.classname))) AS p_scripting_type, ' ||
          'module.name AS p_object_name ' ||
         'FROM cmpinstalledmodule_v module, cmpuserfolder_v folder ' ||
         'WHERE folder.owningmodule = module.elementid ' ||
         'UNION ' ||
         'SELECT ' ||
          'parentfolder.elementid AS p_object_id, ' ||
          '''Folder'' AS p_object_type, ' ||
          '''FOLDER'' AS p_scripting_type, ' ||
          'parentfolder.name AS p_object_name ' ||
         'FROM cmpuserfolder_v parentfolder, cmpuserfolder_v folder ' ||
         'WHERE folder.owningfolder = parentfolder.elementid ) ) parent, cmpwbvalidationresult_v v ' ||
        'WHERE (folder.owningfolder = parent.p_object_id OR ' ||
        'folder.owningmodule = parent.p_object_id ) AND ' ||
         'folder.validationresult = v.elementid(+) ' ||
    'WITH READ ONLY';

execute immediate 
	 'CREATE OR REPLACE VIEW WB$_IV_RECORD_FIELDS AS ' ||
        'SELECT ' ||
         'fco.elementid AS firstclass_object_id, ' ||
         'fco.name AS fristclass_object_name, ' ||
         'rel.elementid AS relation_id, '||
         'rel.name AS relation_name, '||
         'fld.elementid AS recordfield_id, ' ||
         'fld.name AS recordfield_name, ' ||
         'fld.logicalname AS business_name, ' ||
         'fld.description AS description, ' ||
         'dt.data_type AS datatype, '||
         'fld.position AS position, ' ||
         'decode(instr(dt.data_type, ''.''), ''0'', fld.length, ''0'') AS length, ' ||
         'decode(instr(dt.data_type, ''.''), ''0'', fld.precision, ''0'') AS precision, ' ||
         'decode(instr(dt.data_type, ''.''), ''0'', fld.scale, ''0'') AS scale, ' ||
         'fld.updatetimestamp AS updated_on, ' ||
         'fld.creationtimestamp AS created_on, ' ||
         'fld.updatedby AS updated_by, '||
         'fld.createdby AS created_by '||
        'FROM  ' ||
         '(SELECT ' ||
           'dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmpdatatype_v dt ' || 
          'UNION ' ||
           'SELECT ' ||
           'mod.name || ''.'' || dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmpobjecttype_v dt, ' ||
           'cmpinstalledmodule_v mod ' ||
           'WHERE ' ||
           'dt.installedmodule = mod.elementid ' ||
          'UNION ' ||
           'SELECT ' ||
           'mod.name || ''.'' || dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmpsqlcollection_v dt, ' ||
           'cmpinstalledmodule_v mod ' ||
           'WHERE ' ||
           'dt.owningmodule = mod.elementid ' ||
          'UNION ' ||
           'SELECT ' ||
           'dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmprefcursortype_v dt ' ||
          'UNION ' ||
           'SELECT ' ||
           'mod.name || ''.'' || func.name || ''.'' || dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmpplscollection_v dt, ' ||
           'cmpfunctioncategory_v func, ' ||
           'cmpinstalledmodule_v mod ' ||
           'WHERE ' ||
           'dt.functioncategory = func.elementid AND ' ||
           'func.installedmodule = mod.elementid ' ||
          'UNION ' ||
           'SELECT ' ||
           'mod.name || ''.'' || func.name || ''.'' || dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmpplsrecord_v dt, ' ||
           'cmpfunctioncategory_v func, ' ||
           'cmpinstalledmodule_v mod ' ||
           'WHERE ' ||
           'dt.functioncategory = func.elementid AND ' ||
           'func.installedmodule = mod.elementid ) dt, ' ||  
         'firstclassobject_v fco, '||    
         'cmprecordfield_v fld, ' || 
         'cmprelation_v rel '||        
        'WHERE  ' ||
         'fld.typedefinition = dt.type_id AND ' ||
         'fld.owningrelation = rel.elementid(+) AND '||
         'fco.elementid = fld.firstclassobject ' ||
         'WITH READ ONLY';

execute immediate
     'CREATE OR REPLACE VIEW WB$_IV_FUNCTION_LIBRARIES AS ' ||
        'SELECT  ' ||
         'm.elementid AS information_system_id, ' ||
         'm.name AS information_system_name, ' ||
         'f.elementid AS function_library_id, ' ||
         'f.name AS function_library_name, ' ||
         'f.logicalname AS business_name, ' ||
         'f.description AS description, ' ||
				 'f.categorytype AS function_library_type, '||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'f.updatetimestamp AS updated_on, ' ||
         'f.creationtimestamp AS created_on, ' ||
				 'f.updatedby AS updated_by, '||
				 'f.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpfunctioncategory_v f,  ' ||
         'cmpinstalledmodule_v m ' ||
        'WHERE  ' ||
         'f.installedmodule = m.elementid AND ' ||
         'f.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_FUNCTIONS AS ' ||
        'SELECT  ' ||
	 's.elementid AS schema_id, '||
	 's.name AS schema_name, '||
         'lib.elementid AS function_library_id, ' ||
         'lib.name AS function_library_name, ' ||
         'f.elementid AS function_id, ' ||
         'f.name AS function_name, ' ||
         'f.logicalname AS business_name, ' ||
         'f.description AS description, ' ||
         'f.signature AS signature, ' ||
         'OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(f.elementid, f.classname) AS function_type, ' ||
         'OWM_VIEW_UTILITIES.OBJ_TYPE_TO_SCRIPTING_TYPE(OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(f.elementid, f.classname)) AS scripting_type, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'f.updatetimestamp AS updated_on, ' ||
         'f.creationtimestamp AS created_on, ' ||
				 'f.updatedby AS updated_by, '||
				 'f.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpfunction_v f, ' ||
         'cmpfunctioncategory_v lib, ' ||
	 'cmpinstalledmodule_v s '||
        'WHERE  ' ||
	 'f.installedmodule = s.elementid(+) AND '||
         'f.functioncategory = lib.elementid(+) AND ' ||
         'f.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

--kaviswan adding procedures,functions only and table function properties
   execute immediate 
	 'CREATE OR REPLACE VIEW WB$_IV_PROCEDURES AS ' ||
        'SELECT  ' ||
         's.elementid AS schema_id, '||
	 's.name AS schema_name, '||
         'lib.elementid AS function_library_id, ' ||
         'lib.name AS function_library_name, ' ||
         'f.elementid AS function_id, ' ||
         'f.name AS function_name, ' ||
         'f.logicalname AS business_name, ' ||
         'f.description AS description, ' ||
         'f.signature AS signature, ' ||
         '''Procedure'' AS function_type, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'f.updatetimestamp AS updated_on, ' ||
         'f.creationtimestamp AS created_on, ' ||
				 'f.updatedby AS updated_by, '||
				 'f.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpfunction_v f, ' ||
         'cmpfunctioncategory_v lib, ' ||
         'cmpinstalledmodule_v s '||
        'WHERE  ' ||
         'f.installedmodule = s.elementid(+) AND '||
         'f.functioncategory = lib.elementid(+) AND ' ||
	 'f.strongtypename = ''oracle.wh.repos.impl.functional.CMPProcedure'' AND '|| 
         'f.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
	 'CREATE OR REPLACE VIEW WB$_IV_DB_FUNCTIONS AS ' ||
        'SELECT  ' ||
         's.elementid AS schema_id, '||
	 's.name AS schema_name, '||
         'lib.elementid AS function_library_id, ' ||
         'lib.name AS function_library_name, ' ||
         'f.elementid AS function_id, ' ||
         'f.name AS function_name, ' ||
         'f.logicalname AS business_name, ' ||
         'f.description AS description, ' ||
         'f.signature AS signature, ' ||
         '''Function'' AS function_type, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'f.updatetimestamp AS updated_on, ' ||
         'f.creationtimestamp AS created_on, ' ||
				 'f.updatedby AS updated_by, '||
				 'f.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpfunction_v f, ' ||
         'cmpfunctioncategory_v lib, ' ||
         'cmpinstalledmodule_v s '||
        'WHERE  ' ||
         'f.installedmodule = s.elementid(+) AND '||
         'f.functioncategory = lib.elementid(+) AND ' ||
	 'f.strongtypename = ''oracle.wh.repos.impl.functional.CMPFunction'' AND '|| 
         'f.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_TABLE_FUNC_PROPERTIES AS ' ||
        'SELECT  ' ||
         'a.function AS function_id, ' ||
         'f.name AS function_name, ' ||
         'a.elementid AS tf_properties_id, ' ||
         'a.name AS tf_properties_set_name, ' ||
         'a.logicalname AS business_name, ' ||
         'a.description AS description, ' ||
         'g.name AS parallel_argument_name, ' ||
         'a.parallel AS is_parallel, ' ||
         'a.pipelined AS is_pipelined, ' ||
         'a.partitionmethod AS partition_method, ' ||
         'a.ordermethod AS order_method, ' ||
         'a.updatetimestamp AS updated_on, ' ||
         'a.creationtimestamp AS created_on, ' ||
	 'a.updatedby AS updated_by, '||
	 'a.createdby AS created_by '||
        'FROM  ' ||
         'cmpfunctionParallel_v a,  ' ||
         'cmpfunction_v f,  ' ||
         'cmpfunctionArgument_v g ' ||
        'WHERE  ' ||
         'a.function = f.elementid AND ' ||
         'a.functionargument = g.elementid ' ||
         'WITH READ ONLY';

execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_FUNCTION_PARAMETERS AS ' ||
        'SELECT  ' ||
         'f.elementid AS function_id, ' ||
         'f.name AS function_name, ' ||
         'a.elementid AS parameter_id, ' ||
         'a.name AS parameter_name, ' ||
         'a.logicalname AS business_name, ' ||
         'a.description AS description, ' ||
         'a.position AS position, ' ||
         'dt.data_type AS data_type, ' ||
         'decode(instr(dt.data_type, ''.''), ''0'', a.length, ''0'') AS length, ' ||
         'decode(instr(dt.data_type, ''.''), ''0'', a.precision, ''0'') AS precision, ' ||
         'decode(instr(dt.data_type, ''.''), ''0'', a.scale, ''0'') AS scale, ' ||
         'a.typecode AS parameter_type, ' ||
         'a.defaultvalue AS default_value, '||
         'a.updatetimestamp AS updated_on, ' ||
         'a.creationtimestamp AS created_on, ' ||
         'a.updatedby AS updated_by, '||
         'a.createdby AS created_by '||
        'FROM  ' ||
          '(SELECT ' ||
           'dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmpdatatype_v dt ' || 
          'UNION ' ||
           'SELECT ' ||
           'mod.name || ''.'' || dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmpobjecttype_v dt, ' ||
           'cmpinstalledmodule_v mod ' ||
           'WHERE ' ||
           'dt.installedmodule = mod.elementid ' ||
          'UNION ' ||
           'SELECT ' ||
           'mod.name || ''.'' || dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmpsqlcollection_v dt, ' ||
           'cmpinstalledmodule_v mod ' ||
           'WHERE ' ||
           'dt.owningmodule = mod.elementid ' ||
          'UNION ' ||
           'SELECT ' ||
           'dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmprefcursortype_v dt ' ||
          'UNION ' ||
           'SELECT ' ||
           'mod.name || ''.'' || func.name || ''.'' || dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmpplscollection_v dt, ' ||
           'cmpfunctioncategory_v func, ' ||
           'cmpinstalledmodule_v mod ' ||
           'WHERE ' ||
           'dt.functioncategory = func.elementid AND ' ||
           'func.installedmodule = mod.elementid ' ||
          'UNION ' ||
           'SELECT ' ||
           'mod.name || ''.'' || func.name || ''.'' || dt.name AS data_type, ' ||
           'dt.elementid AS type_id ' ||
           'FROM ' ||
           'cmpplsrecord_v dt, ' ||
           'cmpfunctioncategory_v func, ' ||
           'cmpinstalledmodule_v mod ' ||
           'WHERE ' ||
           'dt.functioncategory = func.elementid AND ' ||
           'func.installedmodule = mod.elementid ) dt, ' ||  
         'cmpfunctionargument_v a,  ' ||
         'cmpfunction_v f  ' ||
        'WHERE  ' ||
         'a.owningrelation = f.elementid AND ' ||
         'a.typedefinition = dt.type_id ' ||
         'WITH READ ONLY';

execute immediate 
   'CREATE OR REPLACE VIEW WB$_IV_FUNCTION_IMPLS AS ' ||
        'SELECT  ' ||
         'f.elementid AS function_id, ' ||
         'f.name AS function_name, ' ||
         'im.elementid AS function_implementation_id, ' ||
         'im.name AS function_implementation_name, ' ||
         'im.logicalname AS business_name, ' ||
         'im.description AS description, ' ||
         'lang.name AS language, ' ||
         'OWM_VIEW_UTILITIES.FUNCTION_SCRIPT(im.elementid) AS script, ' ||
         'im.expression AS expression, ' ||
         'im.platform AS platform, ' ||  
         'im.updatetimestamp AS updated_on, ' ||
         'im.creationtimestamp AS created_on, ' ||
  'im.updatedby AS updated_by, '||
  'im.createdby AS created_by '||
        'FROM  ' ||
         'cmplanguage_v lang, ' ||
         'cmpfunctionimplementation_v im, ' ||
         'cmpfunction_v f ' ||
        'WHERE  ' ||
         'im.function = f.elementid AND ' ||
         'im.language = lang.elementid ' ||
         'WITH READ ONLY';

-- Added by rkarri. View for mining models
--  execute immediate
--  'CREATE OR REPLACE VIEW WB$_IV_MINING_MODELS AS ' ||
--  ' SELECT ' || 
--  ' i.elementid AS information_system_id, ' ||
--  ' i.name AS information_system_name, ' ||
--  ' m.elementid as mining_model_id,  ' ||
--  ' m.logicalname as business_name, ' ||
--  ' m.description as description, ' ||
--  ' m.functionname as mining_function, ' ||
--  ' decode(m.algoname, ' ||
--  '''algo_naive_bayes'', ''NAIVE_BAYES'', ''algo_adaptive_bayes_network'', ''ADAPTIVE_BAYES_NETWORK'', ''algo_kmeans'', ''KMEANS'', ''algo_ai_mdl'', ''AI_MDL'', ''algo_nonnegative_matrix_factor'', ''NONNEGATIVE_MATRIX_FACTOR'', ''algo_support_vector_machines'', ''SUPPORT_VECTOR_MACHINES'') AS mining_algorithm ,' ||
--  ' attrs.name as target_attribute , ' ||
--  ' decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
--  'm.updatetimestamp AS updated_on, ' ||
--         'm.creationtimestamp AS created_on, ' ||
--         'm.updatedby AS updated_by, ' ||
--  'm.createdby AS created_by ' ||
--  ' FROM cmpwbvalidationresult_v v, ' ||
--  ' cmpinstalledmodule_v i, ' ||
--  ' cmpdmmodel_v m, cmprelationaldbitem_v attrs ' ||
--  ' where attrs.owningrelation = m.elementid AND ' ||
--  ' m.targetattribute = attrs.elementid AND ' ||
--  ' m.installedmodule = i.elementid AND ' ||
--  ' m.validationresult = v.elementid(+) ' ||
--  ' WITH READ ONLY';
    execute immediate 
     'CREATE OR REPLACE VIEW WB$_IV_XFORM_MAPS AS ' ||
        'SELECT ' ||
         'i.elementid AS information_system_id, ' ||
         'i.name AS information_system_name,  ' ||
         'm.elementid AS map_id, ' ||
         'm.name AS map_name, ' ||
         'm.logicalname AS business_name, ' ||
         'm.description AS description, ' ||
         'decode(m.strongtypename, ''oracle.wh.repos.impl.mapping.CMPTrickleFeedMap'', ''REAL TIME MAP'', ' ||
         ' ''oracle.wh.repos.impl.businessRule.CMPDataRuleMap'', ''DATA AUDITOR'', ' ||
         ' ''oracle.wh.repos.impl.mapping.CMPKMMap'', ''TEMPLATE MAP'', ' ||
         ' ''BATCH MAP'') AS map_type, ' ||
         'm.elementid AS composite_map_component_id, ' ||
         'm.name AS composite_map_component_name, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'm.updatetimestamp AS updated_on, ' ||
         'm.creationtimestamp AS created_on, ' ||
         'm.updatedby AS updated_by, '||
         'm.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpinstalledmodule_v i, ' ||         
         'cmpmap_v m ' ||
        'WHERE ' ||
         'm.installedmodule = i.elementid AND ' ||
         'm.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

--Updated: by Helen
   execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_XFORM_MAP_COMPONENTS AS ' ||
        'SELECT  ' ||
         'm.elementid AS map_id, ' ||
         'm.name AS map_name, ' ||
         'o.elementid AS map_component_id, ' ||
         'o.name AS map_component_name, ' ||
         'o.logicalname AS business_name, ' ||
         'o.description AS description, ' ||
         'OWM_VIEW_UTILITIES.OPERATOR_TYPE(o.STRONGTYPENAME) AS operator_type, ' ||
         'm.elementid AS composite_component_id, ' ||
         'm.name AS composite_component_name, ' ||
         'bindee.elementid AS data_entity_id, ' ||
         'OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE(bindee.classname) AS data_entity_type, ' ||
         'bindee.name AS data_entity_name, ' ||
         'o.updatetimestamp AS updated_on, ' ||
         'o.creationtimestamp AS created_on, ' ||
         'o.updatedby AS updated_by, '||
         'o.createdby AS created_by '||
        'FROM  ' ||
         'cmpmapoperatorbindee_v bindee, ' ||
         'cmpmap_v m, ' ||
         'cmpmapoperator_v o ' ||
        'WHERE  ' ||
         'o.operatorowner = m.elementid AND ' ||
         'o.referencingobject = bindee.elementid(+) ' ||
         'WITH READ ONLY';
       
   execute immediate 
        'CREATE OR REPLACE VIEW WB$_IV_XFORM_MAP_GROUPS AS ' ||
        'SELECT  ' ||
         'm.elementid AS map_id, ' ||
         'm.name AS map_name, ' ||
         'o.elementid AS map_component_id, ' ||
         'o.name AS map_component_name, ' ||
         'g.elementid AS parameter_group_id, ' ||
         'g.name AS parameter_group_name, ' ||
         'g.logicalname AS business_name, ' ||
         'DECODE(g.direction, 1, ''IN'', 2, ''OUT'', ''INOUT'') AS parameter_group_type, '||
         'g.description AS description, ' ||
         'g.position AS position, ' ||
         'g.updatetimestamp AS updated_on, ' ||
         'g.creationtimestamp AS created_on, ' ||
         'g.updatedby AS updated_by, '||
         'g.createdby AS created_by '||
        'FROM  ' ||
         'cmpmap_v m, ' ||         
         'cmpmapoperator_v o, ' ||         
         'cmpmapattributegroup_v g  ' ||         
        'WHERE ' ||
         'o.elementid = g.attributegroupowner AND ' ||
         'o.operatorowner = m.elementid ' ||
        'WITH READ ONLY';

execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_XFORM_MAP_PARAMETERS AS ' ||
        'SELECT  ' ||
         'o.elementid AS map_component_id, ' ||
         'o.name AS map_component_name, ' ||
         'a.elementid AS parameter_id, ' ||
         'a.name AS parameter_name, ' ||
         'a.logicalname AS business_name, ' ||
         'a.description AS description, ' ||
         'm.elementid AS map_id, ' ||
         'm.name AS map_name, ' ||
         'g.name AS parameter_group_name, ' ||
         'g.elementid AS parameter_group_id,  ' ||
         'DECODE(g.direction, 1, ''IN'', 2, ''OUT'', ''INOUT'') AS parameter_type, '||
         'a.position AS position, ' ||
         'a.datatype AS data_type, ' ||
         'OWM_VIEW_UTILITIES.PARAMETER_EXPRESSION2(a.expression, o.elementid) AS transformation_expression, ' ||
         'bindee.elementid AS data_item_id, ' ||
         '/****** bindee.typecode ******/ null AS data_item_type, ' ||   /****** needs fixing ******/
         'bindee.name AS data_item_name, ' ||
         'a2.elementid AS source_parameter_id, ' ||
         'a2.name AS source_parameter_name, ' ||
         'a.updatetimestamp AS updated_on, ' ||
         'a.creationtimestamp AS created_on, ' ||
         'a.updatedby AS updated_by, '||
         'a.createdby AS created_by '||
        'FROM  ' ||
         'cmpmapattributebindee_v bindee, ' ||
         'cmpmapattribute_v a2, ' ||         
         'cmpmap_v m, ' ||         
         'cmpmapoperator_v o, ' ||         
         'cmpmapattributegroup_v g, ' ||         
         'cmpmapattribute_v a ' ||         
        'WHERE ' ||
         'g.elementid = a.attributegroup AND  ' ||
         'o.elementid = g.attributegroupowner AND ' ||
         -- bug 4715297: removing pluggable maps from xform_map view
         --'o.operatorowner = m.elementid(+) AND ' ||
         'o.operatorowner = m.elementid AND ' ||
         'a.connectfrom = a2.elementid(+) AND ' ||
         'a.referencingobject = bindee.elementid(+) ' ||
        'WITH READ ONLY';

execute immediate
     'CREATE OR REPLACE VIEW WB$_IV_XFORM_MAP_DETAILS AS ' ||
      'SELECT ' ||
        ' p.map_component_id, ' ||
        ' p.map_component_name, ' ||
        ' p.parameter_name, ' ||
        ' p.parameter_id, ' ||
        ' p.position, ' ||
        ' p.business_name, ' ||
        ' p.transformation_expression, ' ||
        ' p.description, ' ||
        ' OWM_VIEW_UTILITIES.PARAMETER_EXPRESSION2(a.expression,  o.elementid) AS source_expression ' ||
      'FROM  ' ||
        ' cmpmapoperator_v o, ' ||
        ' cmpmapattributegroup_v g, ' ||
        ' cmpmapattribute_v a, ' ||
        ' wb$_iv_xform_map_parameters p ' ||
      'WHERE ' ||
        ' p.source_parameter_id = a.elementid(+) AND ' ||
        ' a.attributegroup = g.elementid AND ' ||
        ' g.attributegroupowner = o.elementid ' ||
      'WITH READ ONLY';

execute immediate 
     'CREATE OR REPLACE VIEW WB$_IV_EXEC_UNITS AS ' ||
        'SELECT ' ||
         'm.elementid AS map_id, ' ||
         'm.name AS map_name, ' ||
         'e.elementid AS execution_unit_id , ' ||
         'e.name AS execution_unit_name, ' ||
         'e.logicalname AS business_name, ' ||
         'e.description AS description, ' ||
         'c.elementid AS configuration_id, '||
         'c.name AS configuration_name, '||
         'e.updatetimestamp AS updated_on, ' ||
         'e.creationtimestamp AS created_on, ' ||
         'e.updatedby AS updated_by, '||
         'e.createdby AS created_by '||
        'FROM  ' ||
         'cmpmap_v m, ' ||
         'cmpmapexecutionunit_v e, ' ||
         'cmpphysicalmap_v p, ' ||
         'cmpnamedconfiguration_v c ' ||
        'WHERE ' ||
         'e.physicalmap = p.elementid AND ' ||
         'p.logicalobject = m.elementid AND ' ||
         'p.namedconfiguration = c.elementid ' ||
         'WITH READ ONLY';

execute immediate 
     'CREATE OR REPLACE VIEW WB$_IV_EXEC_UNIT_MAP_COMP_USES AS ' ||
        'SELECT ' ||
         'e.elementid AS execution_unit_id , ' ||
         'e.name AS execution_unit_name, ' ||
         'm.elementid AS map_component_id, ' ||
         'm.name AS map_component_name ' ||
        'FROM  ' ||
         'cmpmapexecutionunit_v e, ' ||
         'cmpmapexecutionunitusage_v u, ' ||
         'cmpmappable_v m ' ||
        'WHERE ' ||
         'u.executionunit = e.elementid AND ' ||
         'u.mappable = m.elementid (+) ' ||
         'WITH READ ONLY';

execute immediate 
     'CREATE OR REPLACE VIEW WB$_IV_EXEC_UNIT_CT_USES AS ' ||
        'SELECT ' ||
         'e.elementid AS execution_unit_id , ' ||
         'e.name AS execution_unit_name, ' ||
         'decode(u.name, ' ||
           '''IKM_LKM'', ''INTEGRATION/LOADING CT'', ' ||
           '''CKM'', ''CONTROL CT'', ' ||
           '''UNKNOWN'') AS ct_type, ' ||
         'u.referencingtaskflow as ct_id, ' ||
         'k.name as ct_name, ' ||
         'p.name AS parameter_name, ' ||
         'p.logicalname AS business_name, ' ||
         'p.description AS description, ' ||
         'p.value AS value, '||
         'p.updatetimestamp AS updated_on, ' ||
         'p.creationtimestamp AS created_on, ' ||
         'p.updatedby AS updated_by, '||
         'p.createdby AS created_by '||
        'FROM  ' ||
         'cmpmapexecutionunit_v e, ' ||
         'cmpmaptaskflowusage_v u, ' ||
         'cmpmapexecutionunitparameter_v p, ' ||
         'cmptaskflow_v k ' ||
        'WHERE ' ||
         'p.executionunit = u.elementid AND ' ||
         'u.executionunit = e.elementid AND ' ||
         'p.name != ''KM_PARAMS'' AND ' ||
         'u.referencingtaskflow = k.elementid (+) ' ||
         'WITH READ ONLY';

execute immediate
       'CREATE OR REPLACE VIEW WB$_IV_OBJECT_PROPERTIES AS ' ||
       ' SELECT os.object_id, os.object_type, os.object_name, ' ||
       ' nvl(prop.elementId, os.property_id) property_id, ' ||
       ' os.property_name, os.property_type, ' ||
       ' nvl(nvl(prop.value, os.value),  ' ||
       '    (SELECT value  FROM cmpprimitivemodelattribute_v  ' ||
	'    WHERE modelattributeowner=os.datatype and upper(name)=''DEFAULTVALUE'')) property_value ' || 
       ' FROM   ' ||
       ' ( SELECT  vs.elementid AS object_id,  '||
       '   defs.scriptname AS object_type,  '||
       '   vs.name AS object_name, defs.name AS property_name, ' ||
	'  defs.elementId AS property_id, vs.physicalId AS propertyOwner,  ' ||
	'  defs.value AS value, defs.definitionKey AS propKey, ' ||
        '  vs.elementId AS fcoID, defs.datatype As datatype, defs.property_type ' ||
        '  FROM ( ' ||	
	' SELECT fco.elementId elementId, fco.name name, fco.className className,  ' ||
        '       nvl(fco.strongtypename, fco.classname) typename,  '||
	'       phy.elementId physicalID   ' ||
        ' FROM FirstClassObject_v fco,  cmpphysicalobject_v phy  ' ||
        ' WHERE fco.elementId=phy.logicalObject(+)) vs, ' ||
       ' (SELECT c.name className, c.logicalname scriptname, d.name name,  ' ||
	'       decode(c.weaklytyped, 1, c.name, substr(c.name, instr(c.name, ''.'', -1)+1)) typename,  ' ||
	'       p.value value,  nvl(d.definitionKey, d.logicalname) definitionKey, d.elementId elementId,  ' ||
	'       c.elementId classDefinition, d.type datatype,  ' ||
        '       t.name property_type	' ||
        ' FROM cmpclassdefinition_v c,  cmpprimitivemodelattribute_v p, cmppropertysetdefinition_v s, ' ||
	'     cmppropertydefinition_v d, cmpplatform_v m, cmpabstracttype_v t ' ||
        ' WHERE m.name=''GENERIC'' and c.owningfolder=m.elementid and d.type=t.elementid ' ||
	'      and c.elementId = d.firstclassobject and d.owningpropertysetdefinition=s.elementid ' ||
        '      AND s.propertytype = 3 and s.owningclassdefinition=c.elementid  ' ||
        '      AND p.modelattributeowner(+) = d.elementId and upper(p.name(+))=''DEFAULTVALUE'') defs   ' ||
        '  WHERE defs.typename=vs.typename) os,   CMPStringPropertyValue_v prop   ' ||
        ' WHERE prop.propertyowner(+)=os.propertyOwner and prop.firstclassobject(+)=os.propertyowner   ' ||
      ' and prop.logicalname(+)=os.propKey    ' ||
      ' UNION ALL ' || 
      ' SELECT os.object_id, os.object_type, os.object_name, ' ||
      '  nvl(prop.elementId, os.property_id) property_id, ' ||
      '  os.property_name, os.property_type, ' ||
      '  nvl(nvl(prop.value, os.value),  ' ||
      '     (SELECT value  FROM cmpprimitivemodelattribute_v  ' ||
	'    WHERE modelattributeowner=os.datatype and upper(name)=''DEFAULTVALUE'')) property_value ' ||       
       ' FROM   ' ||
       ' ( SELECT  vs.elementid AS object_id,  ' ||
       '   defs.scriptname AS object_type, ' ||
       '   vs.name AS object_name, defs.name AS property_name,  ' || 
	'  defs.elementId AS property_id, vs.propertyOwner AS propertyOwner,  ' || 
	'  defs.value AS value, defs.definitionKey AS propKey, ' ||
        '  vs.elementId AS fcoID, defs.datatype As datatype, defs.property_type ' ||
        '  FROM ( ' ||
        ' SELECT sco.elementId elementId, sco.name name,  ' || 
        '    sco.className className, ' ||
        '    nvl(sco.strongtypename, sco.classname) typename, ' ||
        '    refs.refElem refID, refs.elementId propertyOwner,  ' ||
        '    sco.firstClassObject fcoID  ' || 
       ' FROM (SELECT ref.referencedElement refElem,   ' ||
        '          ref.firstClassObject, ref.elementId   ' ||
        '   FROM cmpphysicalobject_v phy, cmpreferencepropertyvalue_v ref   ' ||
        '   WHERE ref.firstclassobject=phy.elementid AND  ' ||
        '         ref.propertyowner=phy.elementId AND  ' ||
        '         ref.logicalname=''USER_DEFINED_PROPERTIES'') refs,   ' ||
        '  secondclassobject_v sco  ' ||
       ' WHERE refs.refElem(+)=sco.elementId AND  refs.firstclassobject(+)=sco.firstclassobject) vs,  ' || 
       ' (SELECT c.name className, c.logicalname scriptname, d.name name,  ' ||
	'       decode(c.weaklytyped, 1, c.name, substr(c.name, instr(c.name, ''.'', -1)+1)) typename,  ' || 
	'       p.value value,  nvl(d.definitionKey, d.logicalName) definitionKey, d.elementId elementId,  ' ||
	 '      c.elementId classDefinition, d.type datatype,  ' ||
         '      t.name property_type	' ||
        ' FROM cmpclassdefinition_v c,  cmpprimitivemodelattribute_v p, cmppropertysetdefinition_v s, ' ||
	'     cmppropertydefinition_v d, cmpplatform_v m, cmpabstracttype_v t ' ||
        ' WHERE m.name=''GENERIC'' and c.owningfolder=m.elementid and d.type=t.elementid ' ||
	'      and c.elementId = d.firstclassobject and d.owningpropertysetdefinition=s.elementid ' ||
        '      AND s.propertytype = 3 and s.owningclassdefinition=c.elementid ' ||
        '      AND p.modelattributeowner(+) = d.elementId and upper(p.name(+))=''DEFAULTVALUE'') defs   ' || 
       '  WHERE defs.typename=vs.typename) os,   CMPStringPropertyValue_v prop   ' ||
       ' WHERE prop.propertyowner(+)=os.propertyOwner and prop.firstclassobject(+)=os.propertyowner   ' || 
      ' and prop.logicalname(+)=os.propKey    ' ||
        ' WITH READ ONLY';

execute immediate
	 'CREATE OR REPLACE VIEW WB$_IV_SUPPORTED_LANGUAGES AS ' ||
         'select a.name AS language_id, ' || 
         'b.name AS language_name, ' || 
         'decode(a.ISBASELANGUAGE, 1, ''Y'', ''N'') ISBASELANGUAGE ' || 
         'from CMPSUPPORTEDLANGUAGE_V a, ' ||
         'OWB_UTIL_LANGUAGES b ' ||
         'WHERE a.isoid = b.language_id ' ||
         'WITH READ ONLY';

execute immediate 
	 'CREATE OR REPLACE VIEW WB$_IV_MLS_OBJECTS AS ' ||
         'select a.MLSTRANSLATABLE AS object_id, ' ||
         'a.ISOID AS language_id, ' || 
         'a.logicalname AS business_name, ' || 
         'a.description AS description ' || 
         'from CMPTRANSLATION_V a ' ||
         'WITH READ ONLY';

execute immediate 
        'CREATE OR REPLACE VIEW WB$_IV_ATTR_GROUPS AS '||
        'SELECT ' || 
 'rel.elementid AS data_entity_id, '||
 'OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(rel.elementid, rel.className) AS data_entity_type, '||
 'rel.name AS data_entity_name, '||
 'iset.name AS attribute_group_name, '||
 'iset.elementid AS attribute_group_id, '||
 'iset.logicalname AS business_name, '||
 'iset.description AS description, '||
 'iset.typecode AS attribute_group_type, '||
 'iset.updatetimestamp AS updated_on, '||
 'iset.creationtimestamp AS created_on, '||
 'iset.updatedby AS updated_by, '||
 'iset.createdby AS created_by '||
 'FROM '||
 'CMPWBNamedItemSet_v iset, '||
 'CMPRelation_v rel '||
 'WHERE rel.elementid = iset.owningrelation  '||
     'WITH READ ONLY';

execute immediate 
        'CREATE OR REPLACE VIEW WB$_IV_ATTR_GROUP_ITEM_USES AS '||
        'SELECT '||
        'iset.elementid AS attribute_group_id, '||
        'iset.name AS attribute_group_name, '||
        'attr.elementid AS data_item_id, '||
        'OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE(attr.className) AS data_item_type, '||
        'attr.name AS data_item_name, '||
        'attr.position AS position '||
        'FROM '||
        'cmpwbitemsetusage_v us, '|| 
        'cmpwbnameditemset_v iset, '||
        'CMPAttribute_v attr '||
        'WHERE '||
        'us.attribute = attr.elementid AND '||
        'us.itemset = iset.elementid '||
        'WITH READ ONLY';

execute immediate
  'CREATE OR REPLACE VIEW WB$_IV_COLLECTIONS AS ' ||
         'SELECT DISTINCT ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'cat.elementid AS collection_id, ' ||
         'cat.name AS collection_name, ' ||
         'cat.logicalname AS business_name, ' ||
         'cat.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'cat.updatetimestamp AS updated_on, ' ||
         'cat.creationtimestamp AS created_on, ' ||
     'cat.updatedby AS updated_by, '||
     'cat.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpcatalog_v cat, ' ||
         'cmpwbproject_v project ' ||
        'WHERE ' ||
         'cat.owningproject = project.elementid AND ' ||
         'cat.classname != ''CMPWBOracleTTS'' AND cat.classname != ''CMPWBOracleTTSSet'' AND ' ||
         'cat.validationresult = v.elementid(+) ' ||
        'WITH READ ONLY';

execute immediate 
  'CREATE OR REPLACE VIEW WB$_IV_COLLECTION_REFERENCES AS ' ||
        'SELECT ' ||
         'cat.elementid AS collection_id, ' ||
         'cat.name AS collection_name, ' ||
         'e.elementid AS collection_reference_id, ' ||
         'OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(e.elementid, e.classname) AS collection_reference_type, ' ||
         'OWM_VIEW_UTILITIES.OBJ_TYPE_TO_SCRIPTING_TYPE(OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(e.elementid, e.classname)) AS scripting_type, ' ||
         'e.name AS collection_reference_name, ' ||
     'e.logicalname AS business_name, ' ||
         'e.description AS description, ' ||
         --bug4884749
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
     'e.updatetimestamp AS updated_on, ' ||
         'e.creationtimestamp AS created_on, ' ||
     'e.updatedby AS updated_by, '||
     'e.createdby AS created_by '||
        'FROM  ' ||
         'cmpaccesscontrolledelement_v e, ' ||
         'cmpbusinesstreeshortcut_v usage, ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpcatalog_v cat ' ||
        'WHERE  ' ||
         'cat.elementid = usage.catalog AND ' ||
         'v.firstclassobject(+) = cat.elementid AND ' ||
         'usage.fco = e.elementid ' ||
         'WITH READ ONLY';

------ Process Flow, Process Package  ---------------------------------

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_PROCESS_MODULES AS ' ||
  'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'sys.elementid AS information_system_id, ' ||
         'sys.name AS information_system_name, ' ||
         'sys.logicalname AS business_name, ' ||
         'sys.description AS description, ' ||
         'sys.databaselink AS database_link, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'sys.status AS status, '||
         'loc.elementid AS location_id, ' ||
         'loc.name AS location_name, ' ||
         'sys.updatetimestamp AS updated_on, ' ||
         'sys.creationtimestamp AS created_on,  ' ||
         'sys.updatedby AS updated_by, '||
         'sys.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v project, ' ||
         'cmpprocessinstalledmodule_v sys, ' ||
         'cmplocationusage_v lu, ' ||
         'cmplogicallocation_v loc ' ||
         'WHERE  ' ||
         'sys.owningproject = project.elementid AND ' ||
         'sys.elementid = lu.installedmodule(+) AND ' ||
         'NVL(lu.deploymentdefault, 1) = 1 AND ' ||
         'lu.location = loc.elementid(+) AND ' ||
         'sys.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_PROCESSES AS ' ||
        'SELECT ' ||
         'pkg.elementid AS package_id, '||
         'pkg.name AS package_name, '||
         '0 AS parent_process_id, '||
         ''''' AS parent_process_name, '||
         'proc.elementid AS process_id, ' ||
         'proc.name AS process_name, ' ||
         'proc.logicalname AS business_name, ' ||
         'proc.description AS description, ' ||
         'proc.copyof AS bound_object_id, '||
         'proc.boundname AS bound_object_name, '||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'proc.updatetimestamp AS updated_on, ' ||
         'proc.creationtimestamp AS created_on, ' ||
         'proc.updatedby AS updated_by, '||
         'proc.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpprocess_v proc, ' ||
         'cmpprocesspackage_v pkg '||
        'WHERE ' ||
         'proc.owningfolder = pkg.elementid AND '||
         'proc.validationresult = v.elementid(+) ' ||
  'WITH READ ONLY';

--Created for Paris: by Helen
  execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_SUB_PROCESSES AS ' ||
        'SELECT ' ||
         'pkg.elementid AS package_id, '||
         'pkg.name AS package_name, '||
         'proc.elementid AS parent_process_id, '||
         'proc.name AS parent_process_name, '||
         'subproc.elementid AS process_id, ' ||
         'subproc.name AS process_name, ' ||
         'subproc.logicalname AS business_name, ' ||
         'subproc.description AS description, ' ||
         'subproc.copyof AS bound_object_id, ' ||
         'subproc.boundname AS bound_object_name, '||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'subproc.updatetimestamp AS updated_on, ' ||
         'subproc.creationtimestamp AS created_on, ' ||
         'subproc.updatedby AS updated_by, '||
         'subproc.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpprocess_v proc, ' ||
         'cmpsubprocess_v subproc, '||
         'cmpprocesspackage_v pkg '||
        'WHERE ' ||
         'proc.owningfolder = pkg.elementid AND '||
         'subproc.owningprocess = proc.elementid AND '||
         'proc.validationresult = v.elementid(+) ' ||
  'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_PACKAGES AS ' ||
        'SELECT ' ||
         'mod.elementid AS schema_id, ' ||
         'mod.name AS schema_name, ' ||
         'pkg.elementid AS package_id, ' ||
         'pkg.name AS package_name, ' ||
         'pkg.logicalname AS business_name, ' ||
         'pkg.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'pkg.updatetimestamp AS updated_on, ' ||
         'pkg.creationtimestamp AS created_on, ' ||
         'pkg.updatedby AS updated_by, '||
         'pkg.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpprocesspackage_v pkg, ' ||
         'cmpprocessinstalledmodule_v mod ' ||
        'WHERE ' ||
         'mod.elementid=pkg.processinstalledmodule AND ' ||
         'pkg.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_PROCESS_ACTIVITIES AS ' ||
        'SELECT ' ||
         'proc.elementid AS process_id, '||
         'proc.name AS process_name, '||
         'aty.elementid AS activity_id, ' ||
         'aty.name AS activity_name, ' ||
         'aty.logicalname AS business_name, ' ||
         'aty.description AS description, ' ||
         'aty.handler AS activity_type, '||
         'aty.copyof AS bound_object_id, '||
         'aty.boundname AS bound_object_name, '||
         'aty.updatetimestamp AS updated_on, ' ||
         'aty.creationtimestamp AS created_on, ' ||
         'aty.updatedby AS updated_by, '||
         'aty.createdby AS created_by '||
        'FROM ' ||
         'cmpbaseprocess_v proc, ' ||
         'cmpprocessactivity_v aty '||
        'WHERE ' ||
         'aty.owningbaseprocess = proc.elementid '||
       'UNION ' ||
        'SELECT ' ||
         'proc.elementid AS process_id, '||
         'proc.name AS process_name, '||
         'subproc.elementid AS activity_id, ' ||
         'subproc.name AS activity_name, ' ||
         'subproc.logicalname AS business_name, ' ||
         'subproc.description AS description, ' ||
         '''SUBPROCESS'' AS activity_type, '||
         'subproc.copyof AS bound_object_id, ' ||
         'subproc.boundname AS bound_object_name, '||
         'subproc.updatetimestamp AS updated_on, ' ||
         'subproc.creationtimestamp AS created_on, ' ||
         'subproc.updatedby AS updated_by, '||
         'subproc.createdby AS created_by '||
        'FROM ' ||
         'cmpprocess_v proc, ' ||
         'cmpsubprocess_v subproc '||
        'WHERE ' ||
         'subproc.owningprocess = proc.elementid '||
         'WITH READ ONLY';

--Updated: by Helen
  execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_PROCESS_VARIABLES AS ' ||
      'SELECT ' ||
         'proc.elementid AS process_id, ' ||
         'proc.name AS process_name, ' ||
         'data.elementid AS variable_id, '||
         'data.name AS variable_name, '||
         'data.logicalname AS business_name, ' ||
         'data.description AS description, ' ||
         'data.position AS position, '||
         'data.datatype AS data_type, '||
         'data.value AS default_value, '||
         'data.updatetimestamp AS updated_on, ' ||
         'data.creationtimestamp AS created_on, ' ||
         'data.updatedby AS updated_by, '||
         'data.createdby AS created_by '||
       'FROM ' ||
         'cmpbaseprocess_v proc, ' ||
         'cmpvariable_v data '||
       'WHERE ' ||
          'data.OWNER = proc.elementid '||
      'WITH READ ONLY';

--Updated: by Helen
  execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_PROCESS_PARAMETERS AS ' ||
      'SELECT ' ||
         'owner.elementid AS parameter_owner_id, '||
         'owner.name AS parameter_owner_name, '||
         '''NEED TO DEFINE'' AS parameter_owner_type, ' ||
         'prm.elementid AS parameter_id, '||
         'prm.name AS parameter_name, '||
         'prm.logicalname AS business_name, ' ||
         'prm.description AS description, ' ||
         'prm.position AS position, '||
         'prm.datatype AS data_type, '||
         'prm.value AS default_value, '||
         'decode(prm.direction, 0, ''IN'', ''OUT'') AS direction, '||
         'data.elementid AS bounddata_id, '||
         'data.name AS bounddata_name, '||
         'data.datatype AS bounddata_type, '||
         'data.value AS bounddata_value, '||
         'prm.updatetimestamp AS updated_on, ' ||
         'prm.creationtimestamp AS created_on, ' ||
         'prm.updatedby AS updated_by, '||
         'prm.createdby AS created_by '|| 
       'FROM ' ||
         'cmpbaseprocessactivity_v owner, ' ||
         'cmpprocessactivityparameter_v prm,  '||
         'cmpvariable_v data '||
       'WHERE ' ||
         'prm.bounddata = data.elementid (+) AND '||
         'prm.owner = owner.elementid '||
      'WITH READ ONLY';

execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_PROCESS_TRANSITIONS AS ' ||
        'SELECT ' ||
         'proc.elementid AS process_id, ' ||
         'proc.name AS process_name, ' ||
         'trans.elementid AS transition_id, ' ||
         'trans.name AS transition_name, ' ||
         'trans.logicalname AS business_name, ' ||
         'trans.description AS description, ' ||
         'trans.transitioncondition AS condition, ' ||
         'trans.transitionorder AS transition_order, ' ||
         'source.elementid AS source_activity_id, ' ||
         'source.name AS source_activity_name, ' ||
         'target.elementid AS target_activity_id, ' ||
         'target.name AS target_activity_name, '||
         'trans.updatetimestamp AS updated_on, ' ||
         'trans.creationtimestamp AS created_on, ' ||
         'trans.updatedby as updated_by, '||
         'trans.createdby as created_by '||
        'FROM ' ||
         'cmpbaseprocess_v proc, ' ||
         'cmpprocesstransition_v trans, '||
         '(select name, elementid from cmpprocessactivity_v ' ||
               '  union select name, elementid from cmpsubprocess_v)  source, '||
         '(select name, elementid from cmpprocessactivity_v ' ||
               '  union select name, elementid from cmpsubprocess_v) target '||
        'WHERE ' ||
         'trans.owningprocess = proc.elementid AND '||
         'trans.sourceactivity = source.elementid AND '||
         'trans.targetactivity = target.elementid '||
         'WITH READ ONLY';

-- Expert public views. created by fryang

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_EXPERTS AS ' ||
        'SELECT ' ||
         'sys.elementid AS module_id, ' ||
         'sys.name AS module_name,' ||
         'expert.elementid AS expert_id, ' ||
         'expert.name AS expert_name, ' ||
         'expert.logicalname AS business_name, ' ||
         'expert.description AS description, ' ||
         'expert.copyof AS bound_object_id, ' ||
         'expert.boundname AS bound_object_name, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'expert.updatetimestamp AS updated_on, ' ||
         'expert.creationtimestamp AS created_on, ' ||
         'expert.updatedby AS updated_by, ' ||
         'expert.createdby AS created_by ' ||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||   
         'cmptaskflow_v expert, ' ||        
         'cmptaskflowinstalledmodule_v sys ' || 
        'WHERE ' ||
        'expert.strongtypename = ''oracle.wh.repos.impl.taskFlow.CMPDesignKM'' AND ' || 
         'expert.owningfolder = sys.elementid AND ' ||
         'expert.validationresult = v.elementid(+) ' ||
  'WITH READ ONLY';

execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_EXPERT_MODULES AS ' ||
  'SELECT ' ||
        'project.elementid AS project_id, ' ||
        'project.name AS project_name, ' ||
        'sys.elementid AS information_system_id, ' ||
        'sys.name AS information_system_name, ' ||
        'sys.logicalname AS business_name, ' ||
        'sys.description AS description, ' ||
				'v.valid AS valid, ' ||
        'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
        'sys.status AS status, ' ||
        'sys.updatetimestamp AS updated_on, ' ||
        'sys.creationtimestamp AS created_on, ' ||
        'sys.updatedby AS updated_by, ' ||
        'sys.createdby AS created_by ' ||
       'FROM ' ||
        'cmpwbvalidationresult_v v, ' ||
        'cmpwbproject_v project, ' ||
        'cmptaskflowinstalledmodule_v sys ' ||
       'WHERE ' ||
        'sys.owningproject = project.elementid AND ' ||
        'sys.strongtypename = ''oracle.wh.repos.impl.taskFlow.CMPExpertTaskFlowInstalledModule'' AND ' ||	        
        'sys.validationresult = v.elementid(+) ' ||
        'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_EXPERT_VARIABLES AS ' ||
      'SELECT ' ||
         'expert.elementid AS expert_id, ' ||
         'expert.name AS expert_name, ' ||
         'data.elementid AS variable_id, ' ||
         'data.name AS variable_name, ' ||
         'data.logicalname AS business_name, ' ||
         'data.description AS description, ' ||
         'data.datatype AS data_type, ' ||
         'data.value AS default_value, ' ||
         'data.updatetimestamp AS updated_on, ' ||
         'data.creationtimestamp AS created_on, ' ||
         'data.updatedby AS updated_by, ' ||
         'data.createdby AS created_by ' ||
       'FROM ' ||
         'cmptaskflow_v expert, ' ||    
         'cmpvariable_v data ' ||
       'WHERE ' ||
          'data.OWNER = expert.elementid  AND ' ||
          'expert.strongtypename = ''oracle.wh.repos.impl.taskFlow.CMPDesignKM'' ' ||
      'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_EXPERT_PARAMETERS AS ' ||
      'SELECT ' ||
         'owner.elementid AS parameter_owner_id, ' ||
         'owner.name AS parameter_owner_name,  ' ||
         '''EXPERT'' AS parameter_owner_type, ' || 
         'prm.elementid AS parameter_id, ' ||
         'prm.name AS parameter_name, ' ||
         'prm.logicalname AS business_name, ' ||
         'prm.description AS description, ' ||
         'prm.position AS position, ' ||
         'prm.datatype AS data_type, ' ||
         'prm.value AS default_value, ' ||
         'decode(prm.direction, 0, ''IN'', 1, ''OUT'', ''INOUT'') AS direction, ' || 
         'prm.updatetimestamp AS updated_on, ' ||
         'prm.creationtimestamp AS created_on, ' ||
         'prm.updatedby AS updated_by, ' ||
         'prm.createdby AS created_by ' || 
       'FROM ' ||
         'cmptaskflow_v owner, ' || 
         'cmpprocessactivityparameter_v prm  ' ||  
       'WHERE ' ||
         'prm.owner = owner.elementid AND ' ||
         'owner.strongtypename = ''oracle.wh.repos.impl.taskFlow.CMPDesignKM'' ' ||
      'UNION ' ||
        'SELECT ' ||
         'owner.elementid AS parameter_owner_id, ' ||
         'owner.name AS parameter_owner_name,  ' ||
         '''TASK'' AS parameter_owner_type, ' || 
         'prm.elementid AS parameter_id, ' ||
         'prm.name AS parameter_name, ' ||
         'prm.logicalname AS business_name, ' ||
         'prm.description AS description, ' ||
         'prm.position AS position, ' ||
         'prm.datatype AS data_type, ' ||
         'prm.value AS default_value, ' ||
         'decode(prm.direction, 0, ''IN'', 1, ''OUT'', ''INOUT'') AS direction, ' || 
         'prm.updatetimestamp AS updated_on, ' ||
         'prm.creationtimestamp AS created_on, ' ||
         'prm.updatedby AS updated_by, ' ||
         'prm.createdby AS created_by ' || 
       'FROM ' ||
         'cmptask_v owner, ' || 
         'cmpprocessactivityparameter_v prm  ' ||  
       'WHERE ' ||
         'prm.owner = owner.elementid AND ' || 
         'owner.strongtypename = ''oracle.wh.repos.impl.taskFlow.CMPDesignKM'' ' ||
      'WITH READ ONLY';

execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_EXPERT_TRANSITIONS AS ' ||
        'SELECT ' ||
         'proc.elementid AS expert_id, ' ||
         'proc.name AS expert_name, ' ||
         'trans.elementid AS transition_id, ' ||
         'trans.name AS transition_name, ' ||
         'trans.logicalname AS business_name, ' ||
         'trans.description AS description, ' ||
         'trans.transitioncondition AS condition, ' ||
         'trans.transitionorder AS transition_order, ' ||
         'source.elementid AS source_activity_id, ' ||
         'source.name AS source_activity_name, ' ||
         'target.elementid AS target_activity_id, ' ||
         'target.name AS target_activity_name, ' ||
         'trans.updatetimestamp AS updated_on, ' ||
         'trans.creationtimestamp AS created_on, ' ||
         'trans.updatedby as updated_by, ' ||
         'trans.createdby as created_by ' ||
        'FROM ' ||
         'cmptaskflow_v proc, ' || 
         'cmpprocesstransition_v trans, ' || 
         '(select name, elementid from cmpprocessactivity_v ' ||
               '  union select name, elementid from cmpsubprocess_v)  source, ' || 
         '(select name, elementid from cmpprocessactivity_v ' ||
               '  union select name, elementid from cmpsubprocess_v) target ' ||   
        'WHERE ' ||
         'trans.owningprocess = proc.elementid AND ' ||
         'trans.sourceactivity = source.elementid AND ' ||
         'trans.targetactivity = target.elementid AND ' ||
         'proc.strongtypename = ''oracle.wh.repos.impl.taskFlow.CMPDesignKM'' ' ||
         'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_NESTED_EXPERTS AS ' ||
        'SELECT ' ||
         'sys.elementid AS module_id, ' ||
         'sys.name AS module_name, ' ||
         'expert.elementid AS parent_expert_id, ' ||
         'expert.name AS parent_expert_name, ' ||
         'subproc.elementid AS expert_id, ' ||
         'subproc.name AS expert_name, ' ||
         'subproc.logicalname AS business_name, ' ||
         'subproc.description AS description, ' ||
         'subproc.copyof AS bound_object_id, ' ||
         'subproc.boundname AS bound_object_name, '||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'subproc.updatetimestamp AS updated_on, ' ||
         'subproc.creationtimestamp AS created_on, ' ||
         'subproc.updatedby AS updated_by, ' ||
         'subproc.createdby AS created_by ' ||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmptaskflow_v expert, ' ||      
         'cmpsubprocess_v subproc, '||
         'cmptaskflowinstalledmodule_v sys ' ||     
        'WHERE ' ||
         'expert.owningfolder = sys.elementid AND ' ||
         'subproc.owningtaskflow = expert.elementid AND '||
         'expert.strongtypename = ''oracle.wh.repos.impl.taskFlow.CMPDesignKM'' AND ' ||
         'expert.validationresult = v.elementid(+) ' ||
  'WITH READ ONLY';
  
  
   execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_EXPERT_TASKS AS ' ||
        'SELECT ' ||
         'proc.elementid AS expert_id, ' ||
         'proc.name AS expert_name, ' ||
         'aty.elementid AS task_id, ' ||
         'aty.name AS task_name, ' ||
         'aty.logicalname AS business_name, ' ||
         'aty.description AS description, ' ||
         'aty.handler AS task_type, ' ||
         'aty.copyof AS bound_object_id, ' ||
         'aty.boundname AS bound_object_name, ' ||
         'aty.updatetimestamp AS updated_on, ' ||
         'aty.creationtimestamp AS created_on, ' ||
         'aty.updatedby AS updated_by, ' ||
         'aty.createdby AS created_by, ' ||
         'to_char(impl.main) as main, ' ||
         'to_char(impl.preprocessing) as preprocessing, ' ||
         'to_char(impl.postprocessing) as postprocessing, ' ||
         'instr.description AS instruction ' ||
        'FROM ' ||
         'cmptaskflow_v proc, ' || 
         'cmptask_v aty, ' || 
         'cmptaskimplementation_v impl, ' ||
         'cmptaskinstruction_v instr ' ||
        'WHERE ' ||
         'aty.owningbaseprocess = proc.elementid AND ' ||
         'aty.elementid = impl.referringtask AND ' ||
         'aty.elementid = instr.owningtask(+) AND ' ||
         'proc.strongtypename = ''oracle.wh.repos.impl.taskFlow.CMPDesignKM'' ' ||
       'UNION ' ||
        'SELECT ' ||
         'expert.elementid AS expert_id, ' ||
         'expert.name AS expert_name, ' ||
         'subproc.elementid AS activity_id, ' ||
         'subproc.name AS activity_name, ' ||
         'subproc.logicalname AS business_name, ' ||
         'subproc.description AS description, ' ||
         '''SUBPROCESS'' AS activity_type, ' ||
         'subproc.copyof AS bound_object_id, ' ||
         'subproc.boundname AS bound_object_name, ' ||
         'subproc.updatetimestamp AS updated_on, ' ||
         'subproc.creationtimestamp AS created_on, ' ||
         'subproc.updatedby AS updated_by, ' ||
         'subproc.createdby AS created_by, ' ||
         'to_char(impl.main) as main, ' ||
         'to_char(impl.preprocessing) as preprocessing, ' ||
         'to_char(impl.postprocessing) as postprocessing, ' ||
         'instr.description AS instruction '||
        'FROM ' ||
         'cmptaskflow_v expert, ' ||
         'cmpsubprocess_v subproc, ' ||
         'cmptask_v aty, ' || 
         'cmptaskimplementation_v impl, ' ||
         'cmptaskinstruction_v instr ' ||
        'WHERE ' ||
         'subproc.owningtaskflow = expert.elementid AND ' ||
         'aty.owningbaseprocess = expert.elementid AND ' ||
         'aty.elementid = impl.referringtask AND ' ||
         'aty.elementid = instr.owningtask(+) AND ' ||
         'expert.strongtypename = ''oracle.wh.repos.impl.taskFlow.CMPDesignKM'' ' ||
         'WITH READ ONLY';
         
         
         
  
---Knowledge Module public views
   execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_CTS AS ' ||
        'SELECT ' ||
         'sys.elementid AS module_id, ' ||
         'sys.name AS module_name,' ||
         'km.elementid AS ct_id, ' ||
         'km.name AS ct_name, ' ||
         'km.logicalname AS business_name, ' ||
         'km.description AS description, ' ||
         'decode (km.strongtypename, ''oracle.wh.repos.impl.taskFlow.CMPControlKM'', ''ControlCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPLoadKM'', ''LoadCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPIntegrationKM'', ''IntegrationCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPJournalKM'', ''CDCCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPMapKM'', ''OracleTargetCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPPlatformKM'', ''FunctionCodeTemplate'', '''') AS ct_type, ' ||
         'km.copyof AS bound_object_id, ' ||
         'km.boundname AS bound_object_name, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'km.updatetimestamp AS updated_on, ' ||
         'km.creationtimestamp AS created_on, ' ||
         'km.updatedby AS updated_by, ' ||
         'km.createdby AS created_by ' ||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||   
         'cmptaskflow_v km, ' ||        
         'cmptaskflowinstalledmodule_v sys ' || 
        'WHERE ' ||
        'km.strongtypename != ''oracle.wh.repos.impl.taskFlow.CMPDesignKM'' AND ' || 
         'km.owningfolder = sys.elementid AND ' ||
         'km.validationresult = v.elementid(+) ' ||
      'UNION ' ||
        'SELECT ' ||
         'sys.elementid AS module_id, ' ||
         'sys.name AS module_name, ' ||
         'subproc.elementid AS ct_id, ' ||
         'subproc.name AS ct_name, ' ||
         'subproc.logicalname AS business_name, ' ||
         'subproc.description AS description, ' ||
         'decode (km.strongtypename, ''oracle.wh.repos.impl.taskFlow.CMPLoadKM'', ''LoadCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPIntegrationKM'', ''IntegrationCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPJournalKM'', ''CDCCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPMapKM'', ''OracleTargetCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPPlatformKM'', ''FunctionCodeTemplate'', ''oracle.wh.repos.impl.taskFlow.CMPControlKM'', ''ControlCodeTemplate'', '''') AS ct_type, ' ||
         'subproc.copyof AS bound_object_id, ' ||
         'subproc.boundname AS bound_object_name, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'subproc.updatetimestamp AS updated_on, ' ||
         'subproc.creationtimestamp AS created_on, ' ||
         'subproc.updatedby AS updated_by, ' ||
         'subproc.createdby AS created_by ' ||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmptaskflow_v km, ' ||       
         'cmpsubprocess_v subproc, ' ||  
         'cmptaskflowinstalledmodule_v sys ' ||    
        'WHERE ' ||
         'km.owningfolder = sys.elementid AND ' ||
         'km.strongtypename != ''oracle.wh.repos.impl.taskFlow.CMPDesignKM'' AND ' || 
         'subproc.owningtaskflow = km.elementid AND ' ||
         'km.validationresult = v.elementid(+) ' ||
  'WITH READ ONLY';

execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_CT_FOLDERS AS ' ||
  'SELECT ' ||
        'project.elementid AS project_id, ' ||
        'project.name AS project_name, ' ||
        'sys.elementid AS information_system_id, ' ||
        'sys.name AS information_system_name, ' ||
        'sys.logicalname AS business_name, ' ||
        'sys.description AS description, ' ||
				'v.valid AS valid, ' ||
        'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
        'sys.status AS status, ' ||
        'sys.updatetimestamp AS updated_on, ' ||
        'sys.creationtimestamp AS created_on, ' ||
        'sys.updatedby AS updated_by, ' ||
        'sys.createdby AS created_by ' ||
       'FROM ' ||
        'cmpwbvalidationresult_v v, ' ||
        'cmpwbproject_v project, ' ||
        'cmptaskflowinstalledmodule_v sys ' ||
       'WHERE ' ||
        'sys.owningproject = project.elementid AND ' ||
        'sys.strongtypename != ''oracle.wh.repos.impl.taskFlow.CMPExpertTaskFlowInstalledModule'' AND ' ||	        
        'sys.validationresult = v.elementid(+) ' ||
        'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_CT_VARIABLES AS ' ||
      'SELECT ' ||
         'km.elementid AS ct_id, ' ||
         'km.name AS ct_name, ' ||
         'data.elementid AS variable_id, ' ||
         'data.name AS variable_name, ' ||
         'data.logicalname AS business_name, ' ||
         'data.description AS description, ' ||
         'data.datatype AS data_type, ' ||
         'data.value AS default_value, ' ||
         'data.updatetimestamp AS updated_on, ' ||
         'data.creationtimestamp AS created_on, ' ||
         'data.updatedby AS updated_by, ' ||
         'data.createdby AS created_by ' ||
       'FROM ' ||
         'cmptaskflow_v km, ' ||    
         'cmpvariable_v data ' ||
       'WHERE ' ||
          'data.OWNER = km.elementid  AND ' ||
          'km.strongtypename != ''oracle.wh.repos.impl.taskFlow.CMPDesignKM'' ' ||
      'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_CT_PARAMETERS AS ' ||
      'SELECT ' ||
         'owner.elementid AS parameter_owner_id, ' ||
         'owner.name AS parameter_owner_name,  ' ||
         '''CT'' AS parameter_owner_type, ' || 
         'prm.elementid AS parameter_id, ' ||
         'prm.name AS parameter_name, ' ||
         'prm.logicalname AS business_name, ' ||
         'prm.description AS description, ' ||
         'prm.position AS position, ' ||
         'prm.datatype AS data_type, ' ||
         'prm.value AS default_value, ' ||
         'decode(prm.direction, 0, ''IN'', 1, ''OUT'', ''INOUT'') AS direction, ' || 
         'prm.updatetimestamp AS updated_on, ' ||
         'prm.creationtimestamp AS created_on, ' ||
         'prm.updatedby AS updated_by, ' ||
         'prm.createdby AS created_by ' || 
       'FROM ' ||
         'cmptaskflow_v owner, ' || 
         'cmpprocessactivityparameter_v prm  ' ||  
       'WHERE ' ||
         'prm.owner = owner.elementid AND ' ||
         'owner.strongtypename != ''oracle.wh.repos.impl.taskFlow.CMPDesignKM'' ' ||
      'UNION ' ||
        'SELECT ' ||
         'owner.elementid AS parameter_owner_id, ' ||
         'owner.name AS parameter_owner_name,  ' ||
         '''TASK'' AS parameter_owner_type, ' || 
         'prm.elementid AS parameter_id, ' ||
         'prm.name AS parameter_name, ' ||
         'prm.logicalname AS business_name, ' ||
         'prm.description AS description, ' ||
         'prm.position AS position, ' ||
         'prm.datatype AS data_type, ' ||
         'prm.value AS default_value, ' ||
         'decode(prm.direction, 0, ''IN'', 1, ''OUT'', ''INOUT'') AS direction, ' || 
         'prm.updatetimestamp AS updated_on, ' ||
         'prm.creationtimestamp AS created_on, ' ||
         'prm.updatedby AS updated_by, ' ||
         'prm.createdby AS created_by ' || 
       'FROM ' ||
         'cmptask_v owner, ' || 
         'cmpprocessactivityparameter_v prm  ' ||  
       'WHERE ' ||
         'prm.owner = owner.elementid AND ' || 
         'owner.strongtypename != ''oracle.wh.repos.impl.taskFlow.CMPDesignKM'' ' ||
      'WITH READ ONLY';

execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_CT_TRANSITIONS AS ' ||
        'SELECT ' ||
         'proc.elementid AS ct_id, ' ||
         'proc.name AS ct_name, ' ||
         'trans.elementid AS transition_id, ' ||
         'trans.name AS transition_name, ' ||
         'trans.logicalname AS business_name, ' ||
         'trans.description AS description, ' ||
         'trans.transitioncondition AS condition, ' ||
         'trans.transitionorder AS transition_order, ' ||
         'source.elementid AS source_activity_id, ' ||
         'source.name AS source_activity_name, ' ||
         'target.elementid AS target_activity_id, ' ||
         'target.name AS target_activity_name, ' ||
         'trans.updatetimestamp AS updated_on, ' ||
         'trans.creationtimestamp AS created_on, ' ||
         'trans.updatedby as updated_by, ' ||
         'trans.createdby as created_by ' ||
        'FROM ' ||
         'cmptaskflow_v proc, ' || 
         'cmpprocesstransition_v trans, ' || 
         '(select name, elementid from cmpprocessactivity_v ' ||
               '  union select name, elementid from cmpsubprocess_v)  source, ' || 
         '(select name, elementid from cmpprocessactivity_v ' ||
               '  union select name, elementid from cmpsubprocess_v) target ' ||   
        'WHERE ' ||
         'trans.owningprocess = proc.elementid AND ' ||
         'trans.sourceactivity = source.elementid AND ' ||
         'trans.targetactivity = target.elementid AND ' ||
         'proc.strongtypename != ''oracle.wh.repos.impl.taskFlow.CMPDesignKM'' ' ||
         'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_CT_TASKS AS ' ||
        'SELECT ' ||
         'proc.elementid AS ct_id, ' ||
         'proc.name AS ct_name, ' ||
         'aty.elementid AS task_id, ' ||
         'aty.name AS task_name, ' ||
         'aty.logicalname AS business_name, ' ||
         'aty.description AS description, ' ||
         'aty.handler AS task_type, ' ||
         'aty.copyof AS bound_object_id, ' ||
         'aty.boundname AS bound_object_name, ' ||
         'aty.updatetimestamp AS updated_on, ' ||
         'aty.creationtimestamp AS created_on, ' ||
         'aty.updatedby AS updated_by, ' ||
         'aty.createdby AS created_by, ' ||
         'to_char(impl.main) as main, ' ||
         'to_char(impl.preprocessing) as preprocessing, ' ||
         'to_char(impl.postprocessing) as postprocessing, ' ||
         'instr.description AS instruction ' ||
        'FROM ' ||
         'cmptaskflow_v proc, ' || 
         'cmptask_v aty, ' || 
         'cmptaskimplementation_v impl, ' ||
         'cmptaskinstruction_v instr ' ||
        'WHERE ' ||
         'aty.owningbaseprocess = proc.elementid AND ' ||
         'aty.elementid = impl.referringtask AND ' ||
         'aty.elementid = instr.owningtask(+) AND ' ||
         'proc.strongtypename != ''oracle.wh.repos.impl.taskFlow.CMPDesignKM'' ' ||
         'WITH READ ONLY';         
         
         
  
------- end knowledge module public views

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_SAP_MODULES AS ' ||
 'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'sys.elementid AS information_system_id, ' ||
         'sys.name AS information_system_name, ' ||
         'sys.logicalname AS business_name, ' ||
         'sys.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
     'sys.status AS status, '||
         'loc.elementid AS location_id, ' ||
     'loc.name AS location_name, ' ||
         'mloc.elementid AS metadata_location_id, ' ||
     'mloc.name AS metadata_location_name, ' ||
         'sys.updatetimestamp AS updated_on, ' ||
         'sys.creationtimestamp AS created_on,  ' ||
     'sys.updatedby AS updated_by, '||
     'sys.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v project, ' ||
         'cmpwbsapinstalledmodule_v sys, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where deploymentdefault=1) lu, ' ||
         'cmplogicallocation_v loc, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where metadatasourceflag=1) mlu, ' ||
  'cmplogicallocation_v mloc ' ||
         'WHERE  ' ||
         'sys.owningproject = project.elementid AND ' ||
         'sys.elementid = lu.installedmodule(+) AND ' ||
         'lu.location = loc.elementid(+) AND ' ||
         'sys.elementid = mlu.installedmodule(+) AND ' ||
         'mlu.location = mloc.elementid(+) AND ' ||
         'sys.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_FILE_MODULES AS ' ||
 'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'sys.elementid AS information_system_id, ' ||
         'sys.name AS information_system_name, ' ||
         'sys.logicalname AS business_name, ' ||
         'sys.description AS description, ' ||
         'sys.databaselink AS database_link, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
     'sys.status AS status, '||
         'loc.elementid AS location_id, ' ||
     'loc.name AS location_name, ' ||
         'mloc.elementid AS metadata_location_id, ' ||
     'mloc.name AS metadata_location_name, ' ||
         'sys.updatetimestamp AS updated_on, ' ||
         'sys.creationtimestamp AS created_on,  ' ||
  'sys.updatedby AS updated_by, '||
  'sys.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v project, ' ||
         'cmpwbfileinstalledmodule_v sys, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where deploymentdefault=1) lu, ' ||
         'cmplogicallocation_v loc, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where metadatasourceflag=1) mlu, ' ||
  'cmplogicallocation_v mloc ' ||
         'WHERE  ' ||
         'sys.owningproject = project.elementid AND ' ||
         'sys.elementid = lu.installedmodule(+) AND ' ||
         'lu.location = loc.elementid(+) AND ' ||
         'sys.elementid = mlu.installedmodule(+) AND ' ||
         'mlu.location = mloc.elementid(+) AND ' ||
         'sys.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_WAREHOUSE_MODULES AS ' ||
 'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'sys.elementid AS information_system_id, ' ||
         'sys.name AS information_system_name, ' ||
         'sys.logicalname AS business_name, ' ||
         'sys.description AS description, ' ||
         'sys.databaselink AS database_link, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
				 'sys.status AS status, '||
         'loc.elementid AS location_id, ' ||
				 'loc.name AS location_name, ' ||
         'mloc.elementid AS metadata_location_id, ' ||
				 'mloc.name AS metadata_location_name, ' ||
         'sys.updatetimestamp AS updated_on, ' ||
         'sys.creationtimestamp AS created_on,  ' ||
	 'sys.updatedby AS updated_by, '||
	 'sys.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v project, ' ||
         'cmpdatabasemodule_v sys, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where deploymentdefault=1) lu, ' ||
         'cmplogicallocation_v loc, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where metadatasourceflag=1) mlu, ' ||
	 'cmplogicallocation_v mloc ' ||
          'WHERE  ' ||
         'sys.owningproject = project.elementid AND ' ||
         'sys.strongtypename = ''oracle.wh.repos.impl.application.OracleNativeModule'' AND ' ||
         'sys.elementid = lu.installedmodule(+) AND ' ||
  'lu.location = loc.elementid(+) AND ' ||
  'sys.elementid = mlu.installedmodule(+) AND ' ||
  'mlu.location = mloc.elementid(+) AND ' ||
         'sys.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';
         
    execute immediate 
        'CREATE OR REPLACE VIEW WB$_IV_BUSINESS_DEF_MODULES AS ' ||
        'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'sys.elementid AS module_id, ' ||
         'sys.name AS module_name, ' ||
         'sys.logicalname AS business_name, ' ||
         'sys.description AS description, ' ||
         'decode(sys.strongtypename, ''oracle.wh.repos.impl.intelligenceSchema.OBISESchema'', 1, 0) AS for_discoverer, ' ||
         'decode(sys.strongtypename, ''oracle.wh.repos.impl.intelligenceSchema.OBIEESchema'', 1, 0) AS for_obi, ' ||
         'sys.databaselink AS database_link, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'sys.status AS status, '||
         'loc.elementid AS location_id, ' ||
         'loc.name AS location_name, ' ||
         'sys.updatetimestamp AS updated_on, ' ||
         'sys.creationtimestamp AS created_on,  ' ||
         'sys.updatedby AS updated_by, '||
         'sys.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v project, ' ||
         'cmpintelligenceschema_v sys, ' ||
         'cmplocationusage_v lu, ' ||
         'cmplogicallocation_v loc ' ||
         'WHERE  ' ||
         'sys.owningproject = project.elementid AND ' ||
         'sys.elementid = lu.installedmodule(+) AND ' ||
         'NVL(lu.deploymentdefault, 1) = 1 AND ' ||
         'lu.location = loc.elementid(+) AND ' ||
         'sys.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_GATEWAY_MODULES AS ' ||
 'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'sys.elementid AS information_system_id, ' ||
         'sys.name AS information_system_name, ' ||
         'sys.logicalname AS business_name, ' ||
         'sys.description AS description, ' ||
         'sys.databaselink AS database_link, ' ||
     'sys.strongtypename AS strong_type_name, '||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
     'sys.status AS status, ' ||
         'loc.elementid AS location_id, ' ||
         'loc.name AS location_name, ' ||
         'mloc.elementid AS metadata_location_id, ' ||
         'mloc.name AS metadata_location_name, ' ||
         'sys.updatetimestamp AS updated_on, ' ||
         'sys.creationtimestamp AS created_on,  ' ||
  'sys.updatedby AS updated_by, '||
  'sys.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v project, ' ||
         'cmpdatabasemodule_v sys, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where deploymentdefault=1) lu, ' ||
         'cmplogicallocation_v loc, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where metadatasourceflag=1) mlu, ' ||
         'cmplogicallocation_v mloc ' ||
         'WHERE  ' ||
         'sys.owningproject = project.elementid AND ' ||
         'sys.strongtypename like ''%GatewayInstalledModule'' AND ' ||
         'sys.elementid = lu.installedmodule(+) AND ' ||
         'lu.location = loc.elementid(+) AND ' ||
         'sys.elementid = mlu.installedmodule(+) AND ' ||
         'mlu.location = mloc.elementid(+) AND ' ||
         'sys.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_DATABASE_MODULES AS ' ||
        'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'sys.elementid AS information_system_id, ' ||
         'sys.name AS information_system_name, ' ||
         'sys.logicalname AS business_name, ' ||
         'sys.description AS description, ' ||
         'sys.databaselink AS database_link, ' ||
         'sys.strongtypename AS strong_type_name, '||
         'sys.platform AS platform_id, ' ||
         'plat.name AS platform_name, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'sys.status AS status, ' ||
         'loc.elementid AS location_id, ' ||
         'loc.name AS location_name, ' ||
         'mloc.elementid AS metadata_location_id, ' ||
         'mloc.name AS metadata_location_name, ' ||
         'sys.updatetimestamp AS updated_on, ' ||
         'sys.creationtimestamp AS created_on,  ' ||
         'sys.updatedby AS updated_by, '||
         'classdef.logicalname AS module_type, ' ||
         'sys.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v project, ' ||
         'cmpdatabasemodule_v sys, ' ||
         'cmpplatform_v plat, ' || 
         'cmpclassdefinition_v classdef, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where deploymentdefault=1) lu, ' ||
         'cmplogicallocation_v loc, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where metadatasourceflag=1) mlu, ' ||
         'cmplogicallocation_v mloc ' ||
         'WHERE  ' ||
         'sys.owningproject = project.elementid AND ' ||
         'sys.platform = plat.elementid AND ' ||
         'sys.elementid = lu.installedmodule(+) AND ' ||
         'lu.location = loc.elementid(+) AND ' ||
         'sys.elementid = mlu.installedmodule(+) AND ' ||
         'classdef.name = sys.strongtypename AND ' ||
         'classdef.owningModel = (select elementid from cmpplatform_v where name=''GENERIC'') AND ' ||
         'mlu.location = mloc.elementid(+) AND ' ||
         'sys.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_CMIV_MODULES AS ' ||
 'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'sys.elementid AS information_system_id, ' ||
         'sys.name AS information_system_name, ' ||
         'sys.logicalname AS business_name, ' ||
         'sys.description AS description, ' ||
         'sys.databaselink AS database_link, ' ||
         'sys.platform AS platform_id, ' ||
         'plat.name AS platform_name, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
     'sys.status AS status, '||
         'loc.elementid AS location_id, ' ||
         'loc.name AS location_name, ' ||
         'mloc.elementid AS metadata_location_id, ' ||
         'mloc.name AS metadata_location_name, ' ||
         'sys.updatetimestamp AS updated_on, ' ||
         'sys.creationtimestamp AS created_on,  ' ||
  'sys.updatedby AS updated_by, '||
  'sys.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v project, ' ||
         'cmpweakmodule_v sys, ' ||
         'cmpplatform_v plat, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where deploymentdefault=1) lu, ' ||
         'cmplogicallocation_v loc, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where metadatasourceflag=1) mlu, ' ||
         'cmplogicallocation_v mloc ' ||
         'WHERE  ' ||
  'sys.strongtypename != ''oracle.wh.repos.impl.application.CMPMIVInstalledModule'' AND ' || 
         'sys.owningproject = project.elementid AND ' ||
         'sys.platform = plat.elementid AND ' ||
         'sys.elementid = lu.installedmodule(+) AND ' ||
         'lu.location = loc.elementid(+) AND ' ||
         'sys.elementid = mlu.installedmodule(+) AND ' ||
         'mlu.location = mloc.elementid(+) AND ' ||
         'sys.validationresult = v.elementid(+) ' ||
       'UNION ' ||
 'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'sys.elementid AS information_system_id, ' ||
         'sys.name AS information_system_name, ' ||
         'sys.logicalname AS business_name, ' ||
         'sys.description AS description, ' ||
         'sys.databaselink AS database_link, ' ||
         'sys.platform AS platform_id, ' ||
         'plat.name AS platform_name, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
				 'sys.status AS status, '||
         'loc.elementid AS location_id, ' ||
         'loc.name AS location_name, ' ||
         'mloc.elementid AS metadata_location_id, ' ||
         'mloc.name AS metadata_location_name, ' ||
         'sys.updatetimestamp AS updated_on, ' ||
         'sys.creationtimestamp AS created_on,  ' ||
	 'sys.updatedby AS updated_by, '||
	 'sys.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v project, ' ||
         'cmpinstalledmodule_v sys, ' ||
         'cmpplatform_v plat, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where deploymentdefault=1) lu, ' ||
         'cmplogicallocation_v loc, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where metadatasourceflag=1) mlu, ' ||
         'cmplogicallocation_v mloc ' ||
         'WHERE  ' ||
         '(sys.strongtypename = ''oracle.wh.repos.impl.application.CMPOracleEBSInstalledModule'' OR ' || 
         'sys.strongtypename = ''oracle.wh.repos.impl.application.CMPPeoplesoftInstalledModule'' OR ' || 
         'sys.strongtypename = ''oracle.wh.repos.impl.application.CMPSiebelInstalledModule'' OR ' || 
         '(sys.strongtypename = ''oracle.wh.repos.impl.application.CMPMIVInstalledModule'' AND ' ||
				 'sys.classname = ''CMPWeakModule'')) AND ' || 
         'sys.owningproject = project.elementid AND ' ||
         'sys.platform = plat.elementid AND ' ||
         'sys.elementid = lu.installedmodule(+) AND ' ||
         'lu.location = loc.elementid(+) AND ' ||
         'sys.elementid = mlu.installedmodule(+) AND ' ||
         'mlu.location = mloc.elementid(+) AND ' ||
         'sys.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate
   	'CREATE OR REPLACE VIEW WB$_IV_PREDEFINED_MODULES AS ' ||
         'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' || 
         'sys.elementid AS information_system_id, ' ||
         'decode(sys.logicalname, ''Oracle_Library'', ''Oracle Pre-Defined Transformations'', ''GENERIC_PREDEFINED_LIBRARY'', ''Heterogeneous Pre-Defined Transformations'', ''Unknown'') AS information_system_name, ' ||
         'decode(sys.logicalname, ''Oracle_Library'', ''ORACLE_PREDEFINED_TRANS'', ''Global-Shared-Library'', ''ORACLE_CUSTOM_TRANS'', ''GENERIC_PREDEFINED_LIBRARY'', ''HETERO_PREDEFINED_TRANS'', sys.logicalname) AS business_name, ' ||
         'sys.description AS description, ' ||
         'sys.databaselink AS database_link, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
                                 'sys.status AS status, '||
         'sys.updatetimestamp AS updated_on, ' ||
         'sys.creationtimestamp AS created_on,  ' ||
                                 'sys.updatedby AS updated_by, '||
                                 'sys.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v project, ' ||
         'cmpwbrepinstalledmodule_v sys ' ||
         'WHERE  ' ||
         'sys.logicalname != ''Generic-SQL-Library'' AND ' ||
         'sys.owningproject = project.elementid AND ' ||
         'sys.validationresult = v.elementid(+) ' ||
        'UNION '||
	'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'sys.elementid AS information_system_id, ' ||
         'decode(sys.logicalname, ''Global-Shared-Library'', ''Oracle Custom Transformations'', ''Unknown'') AS information_system_name, ' ||
         'decode(sys.logicalname, ''Oracle_Library'', ''ORACLE_PREDEFINED_TRANS'', ''Global-Shared-Library'', ''ORACLE_CUSTOM_TRANS'', ''GENERIC_PREDEFINED_LIBRARY'', ''HETERO_PREDEFINED_TRANS'', sys.logicalname) AS business_name, ' ||
         'sys.description AS description, ' ||
         'sys.databaselink AS database_link, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
                                 'sys.status AS status, '||
         'sys.updatetimestamp AS updated_on, ' ||
         'sys.creationtimestamp AS created_on,  ' ||
                                 'sys.updatedby AS updated_by, '||
                                 'sys.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v project, ' ||
         'cmpsharedinstalledmodule_v sys ' ||
         'WHERE  ' ||
         'sys.owningproject = project.elementid AND ' ||
         'sys.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
   	'CREATE OR REPLACE VIEW WB$_IV_PACKAGED_APPS_MODULES AS ' ||
	'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'sys.elementid AS information_system_id, ' ||
         'sys.name AS information_system_name, ' ||
         'sys.logicalname AS business_name, ' ||
         'sys.description AS description, ' ||
         'sys.databaselink AS database_link, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
				 'sys.status AS status, '||
				 'loc.elementid AS location_id, ' ||
				 'loc.name AS location_name, ' ||
				 'mloc.elementid AS metadata_location_id, ' ||
				 'mloc.name AS metadata_location_name, ' ||
         'sys.updatetimestamp AS updated_on, ' ||
         'sys.creationtimestamp AS created_on,  ' ||
	 'sys.updatedby AS updated_by, '||
	 'sys.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v project, ' ||
         'cmpwbsapinstalledmodule_v sys, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where deploymentdefault=1) lu, ' ||
         'cmplogicallocation_v loc, ' ||
         '(select u.installedmodule, u.location from cmplocationusage_v u where metadatasourceflag=1) mlu, ' ||
	 'cmplogicallocation_v mloc ' ||
         'WHERE  ' ||
         'sys.owningproject = project.elementid AND ' ||
	 'sys.elementid = lu.installedmodule(+) AND ' ||
	 'lu.location = loc.elementid(+) AND ' ||
	 'sys.elementid = mlu.installedmodule(+) AND ' ||
	 'mlu.location = mloc.elementid(+) AND ' ||
         'sys.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
	 'CREATE OR REPLACE VIEW WB$_IV_EXTERNAL_TABLES AS ' ||
         'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
				 'loc.elementid AS location_id, '||
				 'loc.name AS location_name, '||
         'tab.elementid AS table_id, ' ||
         'tab.name AS table_name, ' ||
         'tab.logicalname AS business_name, ' ||
         'tab.description AS description, ' ||
				 'rec.elementid AS source_record_id, '||
				 'rec.name AS source_record_name, '||
				 'f.name AS source_file_name, '||
				 'tab.accessparameters AS access_parameters, '||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'tab.updatetimestamp AS updated_on, ' ||
         'tab.creationtimestamp AS created_on, ' ||
				 'tab.updatedby AS updated_by, '||
				 'tab.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmpexternaltable_v tab, ' ||
				 'cmplocation_v loc, '||
				 'cmprecord_v rec, '||
				 'cmpwbfile_v f '||
        'WHERE ' ||
         'schema.elementid=tab.installedmodule AND ' ||
				 'tab.refersto = rec.elementid(+) AND '||
				 'rec.owningfile = f.elementid(+) AND '||
				 'tab.defaultlocation = loc.elementid(+) AND '||
         'tab.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
  	 'CREATE OR REPLACE VIEW WB$_IV_EXTERNAL_COLUMNS AS ' ||
         'SELECT ' ||
         'entity.elementid AS entity_id, ' ||
         'entity.name AS entity_name, ' ||
         'col.elementid AS column_id, ' ||
         'col.name AS column_name, ' ||
         'col.logicalname AS business_name, ' ||
         'col.description AS description, ' ||
         'col.position AS position, ' ||
         'dt.name AS data_type, ' ||
         'col.length AS length, ' ||
         'col.precision AS precision, ' ||
         'col.scale AS scale, ' ||
				 'field.elementid AS source_field_id, '||
				 'field.name AS source_field_name, '||
         'col.updatetimestamp AS updated_on, ' ||
         'col.creationtimestamp AS created_on, ' ||
				 'col.updatedby AS updated_by, '||
				 'col.createdby AS created_by '||
        'FROM  ' ||
         'cmpdatatype_v dt, ' ||
         'cmpexternaltable_v entity, ' ||
	 'cmpwbfield_v field, '||
         'cmpexternaltableitem_v col ' ||
        'WHERE ' ||
         'col.owningrelation = entity.elementid AND ' ||
         'col.typedefinition = dt.elementid AND ' ||
	 'col.externaltablefield = field.elementid(+) '||
         'WITH READ ONLY';

execute immediate
      'CREATE OR REPLACE VIEW WB$_IV_UDO_MODULES AS ' ||
        'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'schema.elementid AS information_system_id, ' ||
         'schema.name AS information_system_name, ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         '(select logicalname from cmpclassdefinition_v cdef ' ||
         '     where cdef.weaklytyped=1 and ' ||
         '         cdef.name=schema.strongtypename and cdef.seeded=0) AS module_type, ' ||
         'schema.logicalname AS business_name, ' ||
         'schema.description AS description, ' ||
         'schema.status AS status, '||
         'schema.updatetimestamp AS updated_on, ' ||
         'schema.creationtimestamp AS created_on,  ' ||
         'schema.updatedby AS updated_by, '||
         'schema.createdby AS created_by '||
        'FROM  ' ||
         'cmpweakmodule_v schema,  ' ||
         'cmpwbproject_v project ' ||
        'WHERE  ' ||
         'schema.owningproject = project.elementid AND ' ||
         'schema.strongtypename is not null  AND ' || 
         'schema.strongtypename != ''oracle.wh.repos.impl.application.CMPMIVInstalledModule''' ||
         'WITH READ ONLY';

execute immediate
      'CREATE OR REPLACE VIEW WB$_IV_UDO_FCOS AS ' ||
        'SELECT ' ||
         'owner.elementid AS owner_id, ' ||
         'owner.name AS owner_name, ' ||
         'fco.elementid AS first_class_object_id, ' ||
         'fco.name AS first_class_object_name, ' ||
         '(select logicalname from cmpclassdefinition_v cdef ' ||
         '     where cdef.weaklytyped=1 and ' ||
         '         cdef.name=fco.strongtypename and cdef.seeded=0) AS first_class_object_type, ' ||
         'fco.logicalname AS business_name, ' ||
         'fco.description AS description, ' ||
         'fco.updatetimestamp AS updated_on, ' ||
         'fco.creationtimestamp AS created_on, ' ||
         'fco.updatedby AS updated_by, '||
         'fco.createdby AS created_by '||
        'FROM ' ||
         'cmpweakfirstclassobject_v fco, ' ||
         'cmpelement_v owner ' ||
        'WHERE ' ||
         'owner.elementid=fco.owningfolder ' ||
         'WITH READ ONLY';

execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_PLATFORMS AS ' ||
        'SELECT ' ||
        'plat.elementid AS platform_id, ' ||
        'plat.name AS platform_name, ' ||
        'plat.logicalname AS business_name, ' ||
        'plat.description AS description, ' ||
        'plat.updatetimestamp AS updated_on, ' ||
        'plat.creationtimestamp AS created_on, ' ||
        'plat.updatedby AS updated_by, ' ||
        'plat.createdby AS created_by ' ||
        'FROM ' ||
        'cmpplatform_v plat ' ||
        'WITH READ ONLY';

execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_CONFIG_TEMPLATE_SETS AS ' ||
        'SELECT ' ||
        'userset.elementid AS config_template_set_id, ' ||
        'userset.name AS config_template_set_name, ' ||
        'userset.logicalname AS business_name, ' ||
        'userset.description AS description, ' ||
        'userset.updatetimestamp AS updated_on, ' ||
        'userset.creationtimestamp AS created_on, ' ||
        'userset.updatedby AS updated_by, ' ||
        'userset.createdby AS created_by ' ||
        'FROM ' ||
        'CMPUserConfigTemplateSet_v userset ' ||
        'WITH READ ONLY';

execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_CONFIG_TEMPLATES AS ' ||
        'SELECT ' ||
        'temp.elementid AS config_template_id, ' ||
        'temp.name AS config_template_name, ' ||
        'temp.logicalname AS business_name, ' ||
        'temp.description AS description, ' ||
        'temp.updatetimestamp AS updated_on, ' ||
        'temp.creationtimestamp AS created_on, ' ||
        'temp.updatedby AS updated_by, ' ||
        'temp.createdby AS created_by, ' ||
        'model.name AS override_model, ' || 
        'model.elementid AS override_model_id, ' ||
        'userset.name AS config_template_set, ' || 
        'userset.elementid AS config_template_set_id ' ||  
        'FROM ' ||
        'CMPPlatformConfigTemplate_v temp, ' ||
        'cmpmodeldefinition_v model, ' ||
        'CMPUserConfigTemplateSet_v userset ' || 
        'WHERE ' ||
         'model.elementid=temp.OVERRIDEE AND ' ||
         'userset.elementid=temp.modelowner ' || 
        'WITH READ ONLY';

execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_CLASS_DEFINITIONS AS ' ||
        'SELECT ' ||
        'temp.elementid AS class_definition_id, ' ||
        'temp.name AS class_definition_name, ' ||
        'temp.logicalname AS business_name, ' ||
        'temp.description AS description, ' ||
        'temp.updatetimestamp AS updated_on, ' ||
        'temp.creationtimestamp AS created_on, ' ||
        'temp.updatedby AS updated_by, ' ||
        'temp.createdby AS created_by, ' ||
        'model.name AS owning_model, ' ||
        'model.elementid AS owning_model_id, ' ||
        'temp.logicalname AS scripting_name, ' || 
        'temp.abstract AS abstract, ' ||
        'decode(temp.stereotype, 0, ''interface'', 1, ''multiple_inheritance'', ''class'') AS class_type ' ||
        'FROM ' ||
        'CMPClassDefinition_v temp, ' ||
        'cmpmodeldefinition_v model ' ||
        'WHERE ' ||
         'model.elementid=temp.owningmodel ' ||
        'WITH READ ONLY';

execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_PROPERTY_DEFINITIONS AS ' ||
        'SELECT ' ||
        'temp.elementid AS property_definition_id, ' ||
        'temp.name AS property_definition_name, ' ||
        'temp.logicalname AS business_name, ' ||
        'temp.seeded AS predefined, ' || 
        'temp.description AS description, ' ||
        'temp.updatetimestamp AS updated_on, ' ||
        'temp.creationtimestamp AS created_on, ' ||
        'temp.updatedby AS updated_by, ' ||
        'temp.createdby AS created_by, ' ||
        'classDef.name AS owning_class, ' ||
        'classDef.elementid AS owning_class_id, ' ||
        'pset.elementid AS owning_property_set_id, ' ||
        'prop.value AS default_value ' ||
        'FROM ' ||
        'CMPPropertyDefinition_v temp, ' ||
        'cmppropertysetdefinition_v pset, ' ||
        'cmpclassdefinition_v classDef, ' ||
        'cmpprimitivemodelattribute_v prop ' || 
        'WHERE ' ||
         'pset.elementid=temp.owningpropertysetdefinition AND ' ||
         'temp.firstclassobject=classDef.elementid AND ' || 
         'prop.modelattributeowner(+)=temp.elementid AND ' || 
         'prop.name(+)=''DEFAULTVALUE'' ' ||
        'WITH READ ONLY';

execute immediate
      'CREATE OR REPLACE VIEW WB$_IV_UDO_FOLDERS AS ' ||
        'SELECT ' ||
         'owner.elementid AS owner_id, ' ||
         'owner.name AS owner_name, ' ||
         'folder.elementid AS folder_id, ' ||
         'folder.name AS folder_name, ' ||
         '(select logicalname from cmpclassdefinition_v cdef ' ||
         '     where cdef.weaklytyped=1 and ' ||
         '         cdef.name=folder.strongtypename and cdef.seeded=0) AS folder_type, ' ||
         'folder.logicalname AS business_name, ' ||
         'folder.description AS description, ' ||
         'folder.updatetimestamp AS updated_on, ' ||
         'folder.creationtimestamp AS created_on, ' ||
         'folder.updatedby AS updated_by, '||
         'folder.createdby AS created_by '||
        'FROM ' ||
         'cmpweakfolder_v folder, ' ||
         'cmpelement_v owner ' ||
        'WHERE ' ||
         'owner.elementid=folder.owningfolder ' ||
         'WITH READ ONLY';

execute immediate
      'CREATE OR REPLACE VIEW WB$_IV_UDO_SCOS AS ' ||
        'SELECT  ' ||
         'owner.elementid AS owner_id, ' ||
         'owner.name AS owner_name, ' ||
         'sco.elementid AS second_class_object_id, ' ||
         'sco.name AS second_class_object_name, ' ||
         '(select logicalname from cmpclassdefinition_v cdef ' ||
         '     where cdef.weaklytyped=1 and ' ||
         '         cdef.name=sco.strongtypename and cdef.seeded=0) AS second_class_object_type, ' ||
         'sco.logicalname AS business_name, ' ||
         'sco.description AS description, ' ||
         'sco.classname AS classname, ' ||
         'sco.updatetimestamp AS updated_on, ' ||
         'sco.creationtimestamp AS created_on,  ' ||
         'sco.updatedby AS updated_by, '||
         'sco.createdby AS created_by '||
        'FROM ' ||
         'cmpweaksecondclassobject_v sco, ' ||
         'cmpelement_v owner ' ||
        'WHERE ' ||
         'owner.elementid=sco.weaksecondclassobjectowner ' ||
         'WITH READ ONLY';

execute immediate
      'CREATE OR REPLACE VIEW WB$_IV_UDO_ASSOCIATIONS AS ' ||
        'SELECT  ' ||
         'owner.elementid AS owner_id, ' ||
         'owner.name AS owner_name, ' ||
         '(select logicalname from cmpclassdefinition_v cdef ' ||
         '     where cdef.weaklytyped=1 and ' ||
         '         cdef.name=owner.strongtypename and cdef.seeded=0) AS owner_type, ' ||
         'assoc.elementid AS association_id, ' ||
         'assoc.name AS association_name, ' ||
         'assoc.logicalname AS business_name, ' ||
         'assoc.description AS description, ' ||
         'assoc.sourcerole AS source_role, ' ||
         'assoc.targetrole AS target_role, ' ||
         'target.elementid AS target_id, ' ||
         'target.name AS target_name, ' ||
         'assoc.updatetimestamp AS updated_on, ' ||
         'assoc.creationtimestamp AS created_on,  ' ||
         'assoc.updatedby AS updated_by, '||
         'assoc.createdby AS created_by '||
        'FROM ' ||
         'cmpweakassociation_v assoc, ' ||
         'cmpelement_v owner, ' ||
         'cmpelement_v target ' ||
        'WHERE ' ||
         'owner.elementid=assoc.weakassociationowner AND ' ||
         'target.elementid=assoc.associatedelement ' || 
         'WITH READ ONLY';

--shawn added, public view for application server module, web service package and web service
--clob can not be unioned, so specification will not be displayed
	execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_WEB_SERVICES AS ' ||
         'SELECT ' ||
         'mod.elementid AS schema_id, '||
         'mod.name AS schema_name, '||
				 'null AS package_id, '||
         'null AS package_name, '||
         'ws.elementid AS webservice_id, ' ||
         'ws.name AS webservice_name, ' ||
         'ws.logicalname AS business_name, ' ||
         'ws.description AS description, ' ||
         'ws.async AS async, ' ||
         'ws.soapversion AS soap_version, ' ||
--         'ws.specification AS specification, ' ||
         'ws.wsdlurl AS wsdl_url, ' ||
         'ws.wsdlversion AS wsdl_version, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'ws.updatetimestamp AS updated_on, ' ||
         'ws.creationtimestamp AS created_on, ' ||
         'ws.updatedby AS updated_by, '||
         'ws.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwebservice_v ws, ' ||
         'cmpappserverinstalledmodule_v mod ' ||
        'WHERE ' ||
         'ws.owninginstalledmodule = mod.elementid AND '||
         'ws.validationresult = v.elementid(+) ' ||
    'UNION ' ||
        'SELECT ' ||
				 'mod.elementid AS schema_id, '||
         'mod.name AS schema_name, '||
         'pkg.elementid AS package_id, '||
         'pkg.name AS package_name, '||
         'ws.elementid AS webservice_id, ' ||
         'ws.name AS webservice_name, ' ||
         'ws.logicalname AS business_name, ' ||
         'ws.description AS description, ' ||
         'ws.async AS async, ' ||
         'ws.soapversion AS soap_version, ' ||
--         'ws.specification AS specification, ' ||
         'ws.wsdlurl AS wsdl_url, ' ||
         'ws.wsdlversion AS wsdl_version, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'ws.updatetimestamp AS updated_on, ' ||
         'ws.creationtimestamp AS created_on, ' ||
         'ws.updatedby AS updated_by, '||
         'ws.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwebservice_v ws, ' ||
         'cmpwebservicepackage_v pkg, '||
				 'cmpappserverinstalledmodule_v mod ' ||
        'WHERE ' ||
         'ws.owningwebservicecategory = pkg.elementid AND '||
				 'pkg.owninginstalledmodule = mod.elementid AND '||
         'ws.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_WEB_SERVICE_PACKAGES AS ' ||
        'SELECT ' ||
         'mod.elementid AS schema_id, ' ||
         'mod.name AS schema_name, ' ||
         'pkg.elementid AS package_id, ' ||
         'pkg.name AS package_name, ' ||
         'pkg.logicalname AS business_name, ' ||
         'pkg.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'pkg.updatetimestamp AS updated_on, ' ||
         'pkg.creationtimestamp AS created_on, ' ||
         'pkg.updatedby AS updated_by, '||
         'pkg.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwebservicepackage_v pkg, ' ||
         'cmpappserverinstalledmodule_v mod ' ||
        'WHERE ' ||
         'mod.elementid=pkg.owninginstalledmodule AND ' ||
         'pkg.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_APP_SERVER_MODULES AS ' ||
  'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'sys.elementid AS information_system_id, ' ||
         'sys.name AS information_system_name, ' ||
         'sys.logicalname AS business_name, ' ||
         'sys.description AS description, ' ||
         'sys.databaselink AS database_link, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'sys.status AS status, '||
         'loc.elementid AS location_id, ' ||
         'loc.name AS location_name, ' ||
         'loc.loctype AS location_type, ' ||
         'sys.updatetimestamp AS updated_on, ' ||
         'sys.creationtimestamp AS created_on,  ' ||
         'sys.updatedby AS updated_by, '||
         'sys.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v project, ' ||
         'cmpappserverinstalledmodule_v sys, ' ||
         'cmplocationusage_v lu, ' ||
         'cmplogicallocation_v loc ' ||
         'WHERE  ' ||
         'sys.owningproject = project.elementid AND ' ||
         'sys.elementid = lu.installedmodule(+) AND ' ||
         'NVL(lu.deploymentdefault, 1) = 1 AND ' ||
         'lu.location = loc.elementid(+) AND ' ||
         'sys.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

END;
/

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de