begin


   execute immediate 
	 'CREATE OR REPLACE VIEW WB$_IV_TABLE_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, ' ||
         '''TableFunction'' 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.CMPTableFunction'' AND '|| 
         'f.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';         

   execute immediate
	'CREATE OR REPLACE VIEW WB$_IV_LOCATIONS AS ' ||
        'SELECT ' ||
	 'proj.elementid AS project_id, '||
	 'proj.name AS project_name, '||
	 'loc.elementid AS location_id, '||
	 'loc.name AS location_name, '||
         'loc.loctype AS location_target_type, '||
	 'loc.loctypeversion AS location_target_version, '||
         'loc.logicalname AS business_name, ' ||
         'loc.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'loc.updatetimestamp AS updated_on, ' ||
         'loc.creationtimestamp AS created_on, ' ||
	 'loc.updatedby AS updated_by, '||
	 'loc.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
	 'cmpwbproject_v proj, '||
	 'cmplogicallocation_v loc '||
        'WHERE ' ||
	 'loc.owningproject = proj.elementid AND '||
         'loc.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

   execute immediate
	'CREATE OR REPLACE VIEW WB$_IV_CONNECTORS AS ' ||
        'SELECT ' ||
	 'loc.elementid AS location_id, '||
	 'loc.name AS location_name, '||
         'con.elementid AS connector_id, '||
 	 'con.name AS connector_name, '||
         'con.logicalname AS business_name, ' ||
         'con.description AS description, ' ||
   	 'refloc.elementid AS referenced_location_id, '||
	 'refloc.name AS referenced_location_name, '||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'con.updatetimestamp AS updated_on, ' ||
         'con.creationtimestamp AS created_on, ' ||
	 'con.updatedby AS updated_by, '||
	 'con.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmplogicalconnector_v con, '||
	 'cmplogicallocation_v refloc, '||
	 'cmplogicallocation_v loc '||
        'WHERE ' ||
	 'con.owninglocation = loc.elementid AND '||
	 'con.referencedlocation = refloc.elementid(+) AND '||
         'con.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

   execute immediate
	'CREATE OR REPLACE VIEW WB$_IV_RUNTIME_REPOSITORIES AS ' ||
        'SELECT ' ||
	 'proj.elementid AS project_id, '||
	 'proj.name AS project_name, '||
	 'loc.elementid AS location_id, '||
	 'loc.name AS location_name, '||
         'loc.strongtypename AS location_type, '||
         'loc.logicalname AS business_name, ' ||
         'loc.description AS description, ' ||
	 'loc.host AS host, '||
	 'service_prop.value AS service_name, '||
	 'NVL(NULLIF(loc.port, 0), 1521) AS port, '||
	 'username_prop.value AS username, '||
	 'loc.schema AS schema, '||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'loc.updatetimestamp AS updated_on, ' ||
         'loc.creationtimestamp AS created_on, ' ||
	 'loc.updatedby AS updated_by, '||
	 'loc.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
	 'cmpwbproject_v proj, '||
   'cmpstringpropertyvalue_v service_prop, ' ||
   'cmpstringpropertyvalue_v username_prop, ' ||
	 'cmpruntimelocation_v loc '||
        'WHERE ' ||
	 'loc.owningproject = proj.elementid AND '||
   'loc.elementid = service_prop.propertyowner(+) AND ' ||
   'service_prop.logicalname(+) = ''CMPLocation_ServiceName'' AND ' ||
   'loc.elementid = username_prop.propertyowner(+) AND ' ||
   'username_prop.logicalname(+) = ''CMPLocation_User'' AND ' ||
         'loc.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

  execute immediate
	'CREATE OR REPLACE VIEW WB$_IV_CONTROL_CENTERS AS ' ||
     'SELECT ' ||
   'project_id, '||
   'project_name, '||
   'location_id AS control_center_id, '||
   'location_name AS control_center_name, '||
   'business_name, ' ||
   'description, ' ||
   'host, '||
   'service_name, '||
   'port, '||
   'username, '||
   'schema, '||
   'is_valid, ' ||
   'updated_on, ' ||
   'created_on, ' ||
   'updated_by, '||
   'created_by '||
     'FROM ' ||
   'WB$_IV_RUNTIME_REPOSITORIES ' ||
   'WITH READ ONLY';
         
--   execute immediate 
--   	'CREATE OR REPLACE VIEW WB$_IV_ADVANCED_QUEUES AS ' ||
--        'SELECT ' ||
--         'schema.elementid AS schema_id, ' ||
--         'schema.name AS schema_name, ' ||
--	 'queue.elementid AS queue_id, '||
--	 'queue.name AS queue_name, '||
--         'queue.logicalname AS business_name, ' ||
--         'queue.description AS description, ' ||
--	 'ot.elementid AS load_type_id, '||
--	 'ot.name AS load_type_name, '||
--	 'queue.queuetablename AS queue_table_name, '||
--         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
--         'queue.updatetimestamp AS updated_on, ' ||
--         'queue.creationtimestamp AS created_on, ' ||
--	 'queue.updatedby AS updated_by, '||
--	 'queue.createdby AS created_by '||
--        'FROM ' ||
--         'cmpwbvalidationresult_v v, ' ||
--         'cmpinstalledmodule_v schema, ' ||
--	 'cmpadvancedqueue_v queue, '||
--   	 'cmpobjecttype_v ot '||
--        'WHERE ' ||
--         'schema.elementid = queue.installedmodule AND ' ||
--	 'queue.payloadtype = ot.elementid AND '||
--         'queue.validationresult = v.elementid(+) ' ||
--         'WITH READ ONLY';

   execute immediate
	'CREATE OR REPLACE VIEW WB$_SQL_COLLECTIONS AS ' ||
        'SELECT ' ||
	 'folder.elementid AS folder_id, '||
	 'folder.name AS folder_name, '||
	 'scol.elementid AS collection_id, '||
	 'scol.name AS collection_name, '||
         'scol.logicalname AS business_name, ' ||
         'scol.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'scol.strongtypename AS collection_type, '||
         'scol.updatetimestamp AS updated_on, ' ||
         'scol.creationtimestamp AS created_on, ' ||
	 'scol.updatedby AS updated_by, '||
	 'scol.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
	 'cmpsqlcollection_v scol, '||
	 'folder_v folder '||
        'WHERE ' ||
	 'scol.owningfolder = folder.elementid AND '||
         'scol.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

--   execute immediate
--	'CREATE OR REPLACE VIEW WB$_IV_OBJECT_TYPES AS ' ||
--        'SELECT ' ||
--	 'mo.elementid AS schema_id, '||
--	 'mo.name AS schema_name, '||
--	 'folder.elementid AS folder_id, '||
--	 'folder.name AS folder_name, '||
--	 'ot.elementid AS object_type_id, '||
--	 'ot.name AS object_type_name, '||
--        'ot.logicalname AS business_name, ' ||
--         'ot.description AS description, ' ||
--         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
--	 'ot.typename AS type, '||
--         'ot.updatetimestamp AS updated_on, ' ||
--         'ot.creationtimestamp AS created_on, ' ||
--	 'ot.updatedby AS updated_by, '||
--	 'ot.createdby AS created_by '||
--        'FROM ' ||
--         'cmpwbvalidationresult_v v, ' ||
--	 'cmpobjecttype_v ot, '||
--	 'cmpinstalledmodule_v mo, ' ||
--	 'folder_v folder '||
--        'WHERE ' ||
--	 'ot.owningfolder = folder.elementid AND ' ||
--	 'ot.installedmodule = mo.elementid AND ' ||
--        'ot.validationresult = v.elementid(+) ' ||
--         'WITH READ ONLY';

   execute immediate
	'CREATE OR REPLACE VIEW WB$_IV_PLS_COLLECTIONS AS ' ||
        'SELECT ' ||
	 'lib.elementid AS library_id, '||
	 'lib.name AS library_name, '||
	 'pcol.elementid AS collection_id, '||
	 'pcol.name AS collection_name, '||
         'pcol.logicalname AS business_name, ' ||
         'pcol.description AS description, ' ||
         'pcol.strongtypename AS collection_type, '||
	 'rec.elementid AS related_record_id, '||
	 'rec.name AS related_record_name, '||
         'pcol.updatetimestamp AS updated_on, ' ||
         'pcol.creationtimestamp AS created_on, ' ||
	 'pcol.updatedby AS updated_by, '||
	 'pcol.createdby AS created_by '||
        'FROM ' ||
	 'cmpplscollection_v pcol, '||
	 'cmpplsrecord_v rec, '||
	 'cmpfunctioncategory_v lib '||
        'WHERE ' ||
	 'pcol.typedefinition = rec.elementid (+) AND '||
	 'pcol.functioncategory = lib.elementid '||
         'WITH READ ONLY';

   execute immediate
	'CREATE OR REPLACE VIEW WB$_IV_PLS_RECORDS AS ' ||
        'SELECT ' ||
	 'lib.elementid AS library_id, '||
	 'lib.name AS library_name, '||
	 'prec.elementid AS record_id, '||
	 'prec.name AS record_name, '||
         'prec.logicalname AS business_name, ' ||
         'prec.description AS description, ' ||
	 'prec.typename AS record_type, '||
         'prec.updatetimestamp AS updated_on, ' ||
         'prec.creationtimestamp AS created_on, ' ||
	 'prec.updatedby AS updated_by, '||
	 'prec.createdby AS created_by '||
        'FROM ' ||
	 'cmpplsrecord_v prec, '||
	 'cmpfunctioncategory_v lib '||
        'WHERE ' ||
	 'prec.functioncategory = lib.elementid '||
         'WITH READ ONLY';

   execute immediate
	'CREATE OR REPLACE VIEW WB$_PLS_ROWTYPES AS ' ||
        'SELECT ' ||
	 'lib.elementid AS library_id, '||
	 'lib.name AS library_name, '||
	 'rowtype.elementid AS rowtype_id, '||
	 'rowtype.name AS rowtype_name, '||
         'rowtype.logicalname AS business_name, ' ||
         'rowtype.description AS description, ' ||
	 'rowtype.typename AS rowtype_type, '||
	 'rel.elementid AS entity_id, '||
	 'rel.name AS entity_name, '||
	 'rel.classname AS entity_type, '||
         'rowtype.updatetimestamp AS updated_on, ' ||
         'rowtype.creationtimestamp AS created_on, ' ||
	 'rowtype.updatedby AS updated_by, '||
	 'rowtype.createdby AS created_by '||
        'FROM ' ||
	 'cmpplsrowtype_v rowtype, '||
	 'cmprelationaldbobject_v rel, '||
	 'cmpfunctioncategory_v lib '||
        'WHERE ' ||
	 'rowtype.functioncategory = lib.elementid AND '||
	 'rowtype.refersto = rel.elementid '||
         'WITH READ ONLY';

   execute immediate
	'CREATE OR REPLACE VIEW WB$_IV_REF_CURSORS AS ' ||
        'SELECT ' ||
	 'lib.elementid AS library_id, '||
	 'lib.name AS library_name, '||
	 'curtype.elementid AS cursor_id, '||
	 'curtype.name AS cursor_name, '||
         'curtype.logicalname AS business_name, ' ||
         'curtype.description AS description, ' ||
	 'curtype.typename AS cursor_type, '||
	 'rec.elementid AS return_record_id, '||
	 'rec.name AS return_record_name, '||
         'curtype.updatetimestamp AS updated_on, ' ||
         'curtype.creationtimestamp AS created_on, ' ||
	 'curtype.updatedby AS updated_by, '||
	 'curtype.createdby AS created_by '||
        'FROM ' ||
	 'cmprefcursortype_v curtype, '||
	 'cmpplsrecord_v rec, '||
	 'cmpfunctioncategory_v lib '||
        'WHERE ' ||
	 'curtype.functioncategory = lib.elementid AND '||
	 'curtype.returntype = rec.elementid '||
         'WITH READ ONLY';       

    -- View ALL_IV_ITEM_FOLDERS returns a row for each Item Folder
    -- in a Discoverer Business Intelligence Module.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_ITEM_FOLDERS AS ' ||
        'SELECT ' ||
         'schema.elementid AS module_id, ' ||
         'schema.name AS module_name, ' ||
         'qo.elementid AS item_folder_id, ' ||
         'qo.name AS item_folder_name, ' ||
         'qo.logicalname AS business_name, ' ||
         'qo.description AS description, ' ||
         'qo.objecttype AS folder_type, ' ||
         'decode(qo.hidden, 0, 1, 0) AS is_visible, ' ||
         'desrel.elementid AS source_object_id, ' ||
         'decode(desrel.elementid, null, null, OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE(desrel.classname)) AS source_object_type, ' ||
         'desrel.name AS source_object_name, ' ||
         'role.elementid AS dimension_role_id, ' ||
         'role.name AS dimension_role_name, ' ||
         'lev.elementid AS related_level_id, ' ||
         'lev.name AS related_level_name, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'qo.updatetimestamp AS updated_on, ' ||
         'qo.creationtimestamp AS created_on, ' ||
         'qo.updatedby AS updated_by, '||
         'qo.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpintelligenceschema_v schema, ' ||
         'cmpqueryobject_v qo, '||
         'cmpdesignrelation_v desrel, '||
         'cmpdimensionrole_v role, '||
         'cmplevel_v lev '||
        'WHERE ' ||
         'schema.strongtypename = ''oracle.wh.repos.impl.intelligenceSchema.OBISESchema'' AND ' ||
         'schema.elementid = qo.installedmodule AND ' ||
         'qo.relateddesignfco = desrel.elementid(+) AND ' ||
         'qo.relateddimensionrole = role.elementid(+) AND ' ||
         'qo.relatedlevel = lev.elementid(+) AND ' ||
         'qo.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_LOGICAL_TABLES returns a row for each Logical Table
    -- in an Oracle Business Intelligence Module.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_LOGICAL_TABLES AS ' ||
        'SELECT ' ||
         'schema.elementid AS module_id, ' ||
         'schema.name AS module_name, ' ||
         'qo.elementid AS logical_table_id, ' ||
         'qo.name AS logical_table_name, ' ||
         'qo.logicalname AS business_name, ' ||
         'qo.description AS description, ' ||
         'decode(qo.hidden, 0, 1, 0) AS is_visible, ' ||
         'qo.bridgetable AS is_bridge_table, ' ||
         'qo.distinctvalues AS distinct_values, ' ||
         'desrel.elementid AS source_object_id, ' ||
         'decode(desrel.elementid, null, null, OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE(desrel.classname)) AS source_object_type, ' ||
         'desrel.name AS source_object_name, ' ||
         'role.elementid AS dimension_role_id, ' ||
         'role.name AS dimension_role_name, ' ||
         'lev.elementid AS related_level_id, ' ||
         'lev.name AS related_level_name, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'qo.updatetimestamp AS updated_on, ' ||
         'qo.creationtimestamp AS created_on, ' ||
         'qo.updatedby AS updated_by, '||
         'qo.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpintelligenceschema_v schema, ' ||
         'cmpqueryobject_v qo, '||
         'cmpdesignrelation_v desrel, '||
         'cmpdimensionrole_v role, '||
         'cmplevel_v lev '||
        'WHERE ' ||
         'schema.strongtypename = ''oracle.wh.repos.impl.intelligenceSchema.OBIEESchema'' AND ' ||
         'schema.elementid = qo.installedmodule AND ' ||
         'qo.relateddesignfco = desrel.elementid(+) AND ' ||
         'qo.relateddimensionrole = role.elementid(+) AND ' ||
         'qo.relatedlevel = lev.elementid(+) AND ' ||
         'qo.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_ITEMS returns a row for each Item of a Logical Table or Item Folder.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_ITEMS AS ' ||
        'SELECT ' ||
         'qo.elementid AS item_owner_id, ' ||
         'qo.name AS item_owner_name, ' ||
         'decode(qi.strongtypename, ''oracle.wh.repos.impl.query.OBISEQueryItem'', qo.elementid, null) AS item_folder_id, ' ||
         'decode(qi.strongtypename, ''oracle.wh.repos.impl.query.OBISEQueryItem'', qo.name, null) AS item_folder_name, ' ||
         'qi.elementid AS item_id, ' ||
         'qi.name AS item_name, ' ||
         'qi.logicalname AS business_name, ' ||
         'qi.description AS description, ' ||
         'qi.columnitem AS is_column_item, ' ||
         'qi.itemdatatype AS datatype, ' ||
         'qi.alignment AS alignment, ' ||
         'qi.casestorage AS case_storage, ' ||
         'qi.contenttype AS content_type, ' ||
         'qi.defaultrollupfunction AS default_aggregate, ' ||
         'decode(qi.strongtypename, ''oracle.wh.repos.impl.query.OBISEQueryItem'', qi.defaultplacement, null) AS default_position, ' ||
         'decode(qi.strongtypename, ''oracle.wh.repos.impl.query.OBISEQueryItem'', qi.maxdisplaywidth, null) AS default_width, ' ||
         'qi.casedisplay AS display_case, ' ||
         'qi.formatmask AS format_mask, ' ||
         'qi.expressiontext AS formula, ' ||
         'qi.heading AS heading, ' ||
         'decode(qi.hidden, 0, 1, 0) AS is_visible, ' ||
         'decode(qi.strongtypename, ''oracle.wh.repos.impl.query.OBISEQueryItem'', qi.wordwrap, null) AS is_word_wrap, ' ||
         'qi.maxdatawidth AS max_char_fetched, ' ||
         'qi.displaynullvalue AS replace_null_with, ' ||
         'desatt.elementid AS related_attribute_id, ' ||
         'decode(desatt.elementid, null, null, OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE(desatt.classname)) AS related_attribute_type, ' ||
         'desatt.name AS related_attribute_name, ' ||
         'decode(ic.strongtypename, ''oracle.wh.repos.impl.query.CMPListOfValues'', ic.elementid, to_number(null)) AS list_of_values_id, ' ||
         'decode(ic.strongtypename, ''oracle.wh.repos.impl.query.CMPListOfValues'', ic.name, null) AS list_of_values_name, ' ||
         'decode(ic.strongtypename, ''oracle.wh.repos.impl.query.CMPAlternativeSortOrder'', ic.elementid, to_number(null)) AS alternative_sort_order_id, ' ||
         'decode(ic.strongtypename, ''oracle.wh.repos.impl.query.CMPAlternativeSortOrder'', ic.name, null) AS alternative_sort_order_name, ' ||
         'decode(ic.strongtypename, ''oracle.wh.repos.impl.query.CMPDrillToDetail'', ic.elementid, to_number(null)) AS drill_to_detail_id, ' ||
         'decode(ic.strongtypename, ''oracle.wh.repos.impl.query.CMPDrillToDetail'', ic.name, null) AS drill_to_detail_name, ' ||
         'qi.updatetimestamp AS updated_on, ' ||
         'qi.creationtimestamp AS created_on, ' ||
         'qi.updatedby AS updated_by, '||
         'qi.createdby AS created_by '||
        'FROM ' ||
         'cmpqueryobject_v qo, '||
         'cmpqueryitem_v qi, '||
         'cmpdesignattribute_v desatt, '||
         'cmplovitemclass_v ic '||
        'WHERE ' ||
         'qo.elementid = qi.owningrelation AND ' ||
         'qi.relatedattribute = desatt.elementid(+) AND ' ||
         'qi.lovitemclass = ic.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_ITEM_FORMULA_REFS returns a row for each Item or
    -- Registered Function referenced in the expression formula for an Item.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_ITEM_FORMULA_REFS AS ' ||
        'SELECT ' ||
         'qo.elementid AS item_owner_id, ' ||
         'qo.name AS item_owner_name, ' ||
         'decode(qi.strongtypename, ''oracle.wh.repos.impl.query.OBISEQueryItem'', qo.elementid, null) AS item_folder_id, ' ||
         'decode(qi.strongtypename, ''oracle.wh.repos.impl.query.OBISEQueryItem'', qo.name, null) AS item_folder_name, ' ||
         'qi.elementid AS item_id, ' ||
         'qi.name AS item_name, ' ||
         'qexpref.tag AS tag, ' ||
         'func.elementid AS referenced_function_id, ' ||
         'decode(func.elementid, null, null, OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(func.elementid, func.classname)) AS referenced_function_type, ' ||
         'func.name AS referenced_function_name, ' ||
         'refqo.elementid AS referenced_item_owner_id, ' ||
         'refqo.name AS referenced_item_owner_name, ' ||
         'decode(qi.strongtypename, ''oracle.wh.repos.impl.query.OBISEQueryItem'', refqo.elementid, null) AS referenced_item_folder_id, ' ||
         'decode(qi.strongtypename, ''oracle.wh.repos.impl.query.OBISEQueryItem'', refqo.name, null) AS referenced_item_folder_name, ' ||
         'refqi.elementid AS referenced_item_id, ' ||
         'refqi.name AS referenced_item_name ' ||
        'FROM ' ||
         'cmpqueryobject_v qo, '||
         'cmpqueryitem_v qi, '||
         'cmpqueryexpref_v qexpref, '||
         'cmpfunction_v func, '||
         'cmpqueryitem_v refqi, '||
         'cmpqueryobject_v refqo '||
        'WHERE ' ||
         'qo.elementid = qi.owningrelation AND ' ||
         'qexpref.queryexpowner = qi.elementid AND ' ||
         'qexpref.referencedfunction = func.elementid(+) AND ' ||
         'qexpref.referencedsco = refqi.elementid(+) AND ' ||
         'refqo.elementid = refqi.owningrelation ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_CONDITIONS returns a row for each Condition of a Discoverer
    -- Item Folder.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_CONDITIONS AS ' ||
        'SELECT ' ||
         'qo.elementid AS item_folder_id, ' ||
         'qo.name AS item_folder_name, ' ||
         'qc.elementid AS condition_id, ' ||
         'qc.name AS condition_name, ' ||
         'qc.logicalname AS business_name, ' ||
         'qc.description AS description, ' ||
         'qc.mandatory AS is_mandatory, ' ||
         'qc.expressiontext AS formula, ' ||
         'qc.casesensitive AS is_match_case, ' ||
         'qc.updatetimestamp AS updated_on, ' ||
         'qc.creationtimestamp AS created_on, ' ||
         'qc.updatedby AS updated_by, '||
         'qc.createdby AS created_by '||
        'FROM ' ||
         'cmpqueryobject_v qo, '||
         'cmpquerycondition_v qc '||
        'WHERE ' ||
         'qo.elementid = qc.queryobject ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_CONDITION_FORMULA_REFS returns a row for each Item or
    -- Registered Function referenced in the expression formula for a Condition.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_CONDITION_FORMULA_REFS AS ' ||
        'SELECT ' ||
         'qo.elementid AS item_folder_id, ' ||
         'qo.name AS item_folder_name, ' ||
         'qc.elementid AS item_id, ' ||
         'qc.name AS item_name, ' ||
         'qexpref.tag AS tag, ' ||
         'func.elementid AS referenced_function_id, ' ||
         'decode(func.elementid, null, null, OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(func.elementid, func.classname)) AS referenced_function_type, ' ||
         'func.name AS referenced_function_name, ' ||
         'refqi.elementid AS referenced_item_id, ' ||
         'refqi.name AS referenced_item_name, ' ||
         'refqc.elementid AS referenced_condition_id, ' ||
         'refqc.name AS referenced_condition_name ' ||
        'FROM ' ||
         'cmpqueryobject_v qo, '||
         'cmpquerycondition_v qc, '||
         'cmpqueryexpref_v qexpref, '||
         'cmpfunction_v func, '||
         'cmpqueryitem_v refqi, '||
         'cmpquerycondition_v refqc '||
        'WHERE ' ||
         'qo.elementid = qc.queryobject AND ' ||
         'qexpref.queryexpowner = qc.elementid AND ' ||
         'qexpref.referencedfunction = func.elementid(+) AND ' ||
         'qexpref.referencedsco = refqi.elementid(+) AND ' ||
         'qexpref.referencedsco = refqc.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_JOINS returns a row for each Join between Item Folders.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_JOINS AS ' ||
        'SELECT ' ||
         'qo.elementid AS item_owner_id, ' ||
         'qo.name AS item_owner_name, ' ||
         'decode(j.strongtypename, ''oracle.wh.repos.impl.query.OBISEJoin'', qo.elementid, null) AS item_folder_id, ' ||
         'decode(j.strongtypename, ''oracle.wh.repos.impl.query.OBISEJoin'', qo.name, null) AS item_folder_name, ' ||
         'j.elementid AS join_id, ' ||
         'j.name AS join_name, ' ||
         'j.logicalname AS business_name, ' ||
         'j.description AS description, ' ||
         'j.allowdetailnomaster AS is_outer_join_on_master, ' ||
         'j.allowmasternodetail AS is_outer_join_on_detail, ' ||
         'j.mandatory AS is_detail_exists_on_master, ' ||
         'j.onetoone AS is_one_to_one, ' ||
         'refqo.elementid AS referenced_item_owner_id, ' ||
         'refqo.name AS referenced_item_owner_name, ' ||
         'decode(j.strongtypename, ''oracle.wh.repos.impl.query.OBISEJoin'', refqo.elementid, null) AS referenced_item_folder_id, ' ||
         'decode(j.strongtypename, ''oracle.wh.repos.impl.query.OBISEJoin'', refqo.name, null) AS referenced_item_folder_name, ' ||
         'fk.elementid AS related_foreign_key_id, ' ||
         'fk.name AS related_foreign_key_name, ' ||
         'j.updatetimestamp AS updated_on, ' ||
         'j.creationtimestamp AS created_on, ' ||
         'j.updatedby AS updated_by, '||
         'j.createdby AS created_by '||
        'FROM ' ||
         'cmpqueryobject_v qo, '||
         'cmpjoin_v j, '||
         'cmpqueryobject_v refqo, '||
         'cmpforeignkey_v fk '||
        'WHERE ' ||
         'qo.elementid = j.owningrelation AND ' ||
         'j.referencedobject = refqo.elementid(+) AND ' ||
         'j.relatedforeignkey = fk.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_JOIN_COMPONENTS returns a row for each component condition
    -- of a Join.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_JOIN_COMPONENTS AS ' ||
        'SELECT ' ||
         'qo.elementid AS item_owner_id, ' ||
         'qo.name AS item_owner_name, ' ||
         'decode(j.strongtypename, ''oracle.wh.repos.impl.query.OBISEJoin'', qo.elementid, null) AS item_folder_id, ' ||
         'decode(j.strongtypename, ''oracle.wh.repos.impl.query.OBISEJoin'', qo.name, null) AS item_folder_name, ' ||
         'j.elementid AS join_id, ' ||
         'j.name AS join_name, ' ||
         'jc.elementid AS join_component_id, ' ||
         'jc.name AS join_component_name, ' ||
         'jc.logicalname AS business_name, ' ||
         'jc.description AS description, ' ||
         'jc.operator AS join_operator, ' ||
         'localqi.elementid AS detail_item_id, ' ||
         'localqi.name AS detail_item_name, ' ||
         'remoteqi.elementid AS master_item_id, ' ||
         'remoteqi.name AS master_item_name, ' ||
         'jc.updatetimestamp AS updated_on, ' ||
         'jc.creationtimestamp AS created_on, ' ||
         'jc.updatedby AS updated_by, '||
         'jc.createdby AS created_by '||
        'FROM ' ||
         'cmpqueryobject_v qo, '||
         'cmpjoin_v j, '||
         'cmpjoincomponent_v jc, '||
         'cmpqueryitem_v localqi, '||
         'cmpqueryitem_v remoteqi '||
        'WHERE ' ||
         'qo.elementid = j.owningrelation AND ' ||
         'j.elementid = jc.itemset AND ' ||
         'jc.attribute = localqi.elementid(+) AND ' ||
         'jc.remoteitem = remoteqi.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_ITEM_FOLDER_JOIN_USAGES returns a row for each join used to
    -- connect the base Item Folders used by a complex Discoverer Item Folder.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_ITEM_FOLDER_JOIN_USAGES AS ' ||
        'SELECT ' ||
         'qo.elementid AS item_folder_id, ' ||
         'qo.name AS item_folder_name, ' ||
         'j.elementid AS join_id, ' ||
         'j.name AS join_name '||
        'FROM ' ||
         'cmpqueryobject_v qo, '||
         'cmpobjectjoinusage_v joinusage, '||
         'cmpjoin_v j '||
        'WHERE ' ||
         'joinusage.queryobject = qo.elementid AND ' ||
         'joinusage.join = j.elementid ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_LOGICAL_TABLE_DBOBJECTS returns a row for each database
    -- object (Table, View or Materialized View) used by a Logical Table.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_LOGICAL_TABLE_DBOBJECTS AS ' ||
        'SELECT ' ||
         'qo.elementid AS logical_table_id, ' ||
         'qo.name AS logical_table_name, ' ||
         'dbo.elementid AS object_id, ' ||
         'OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE(dbo.classname) AS object_type, ' ||
         'dbo.name AS object_name '||
        'FROM ' ||
         'cmpqueryobject_v qo, '||
         'cmpquerydbobjectusage_v dbousage, '||
         'cmprelationaldbobject_v dbo '||
        'WHERE ' ||
         'dbousage.queryobject = qo.elementid AND ' ||
         'dbousage.databaseobject = dbo.elementid ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_LOGICAL_TABLE_FK_USAGES returns a row for each Foreign Key
    -- used to join the database objects (Tables, Views or Materialized Views)
    -- used by a Logical Table.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_LOGICAL_TABLE_FK_USAGES AS ' ||
        'SELECT ' ||
         'qo.elementid AS logical_table_id, ' ||
         'qo.name AS logical_table_name, ' ||
         'fk.elementid AS foreign_key_id, ' ||
         'fk.name AS foreign_key_name '||
        'FROM ' ||
         'cmpqueryobject_v qo, '||
         'cmpqueryforeignkeyusage_v fkusage, '||
         'cmpforeignkey_v fk '||
        'WHERE ' ||
         'fkusage.queryobject = qo.elementid AND ' ||
         'fkusage.foreignkey = fk.elementid ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_LOGICAL_TABLE_AGGLEVELS returns a row identifying each
    -- Drill Level at which data in a Logical Table is to be aggregated.
    -- (If this information is not present, the lowest relevant Drill Level
    -- is assumed.)
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_LOGICAL_TABLE_AGGLEVELS AS ' ||
        'SELECT ' ||
         'qo.elementid AS logical_table_id, ' ||
         'qo.name AS logical_table_name, ' ||
         'dp.elementid AS drill_path_id, ' ||
         'dp.name AS drill_path_name, '||
         'dl.elementid AS drill_level_id, ' ||
         'dl.name AS drill_level_name '||
        'FROM ' ||
         'cmpqueryobject_v qo, '||
         'cmpqueryaggregationlevel_v agglevel, '||
         'cmpdrilllevel_v dl, '||
         'cmpdrillpath_v dp '||
        'WHERE ' ||
         'agglevel.queryobject = qo.elementid AND ' ||
         'agglevel.drilllevel = dl.elementid AND ' ||
         'dl.drillpath = dp.elementid ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_BUSINESS_AREAS returns a row for each Business Area
    -- in a Discoverer Business Intelligence Module.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_BUSINESS_AREAS AS ' ||
        'SELECT ' ||
         'schema.elementid AS module_id, ' ||
         'schema.name AS module_name, ' ||
         'ba.elementid AS business_area_id, ' ||
         'ba.name AS business_area_name, ' ||
         'ba.logicalname AS business_name, ' ||
         'ba.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'ba.updatetimestamp AS updated_on, ' ||
         'ba.creationtimestamp AS created_on, ' ||
         'ba.updatedby AS updated_by, '||
         'ba.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpintelligenceschema_v schema, ' ||
         'cmpintelligencebusarea_v ba '||
        'WHERE ' ||
         'schema.strongtypename = ''oracle.wh.repos.impl.intelligenceSchema.OBISESchema'' AND ' ||
         'schema.elementid = ba.intelligenceschema AND ' ||
         'ba.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_BUSINESS_AREA_FOLDERS returns a row for each Item Folder
    -- in a Business Area.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_BUSINESS_AREA_FOLDERS AS ' ||
        'SELECT ' ||
         'schema.elementid AS module_id, ' ||
         'schema.name AS module_name, ' ||
         'ba.elementid AS business_area_id, ' ||
         'ba.name AS business_area_name, ' ||
         'qo.elementid AS item_folder_id, ' ||
         'qo.name AS item_folder_name, ' ||
         'qo.logicalname AS business_name, ' ||
         'qo.description AS description, ' ||
         'qo.objecttype AS folder_type, ' ||
         'decode(qo.hidden, 0, 1, 0) AS is_visible, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'qo.updatetimestamp AS updated_on, ' ||
         'qo.creationtimestamp AS created_on, ' ||
         'qo.updatedby AS updated_by, '||
         'qo.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpintelligenceschema_v schema, ' ||
         'cmpintelligencebusarea_v ba, '||
         'cmpintellbusareashortcut_v shortcut, '||
         'cmpqueryobject_v qo '||
        'WHERE ' ||
         'schema.strongtypename = ''oracle.wh.repos.impl.intelligenceSchema.OBISESchema'' AND ' ||
         'schema.elementid = ba.intelligenceschema AND ' ||
         'ba.elementid = shortcut.businessarea AND ' ||
         'qo.elementid = shortcut.fco AND ' ||
         'qo.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_CATALOG_FOLDERS returns a row for each Catalog Folder
    -- in an Oracle Business Intelligence Module.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_CATALOG_FOLDERS AS ' ||
        'SELECT ' ||
         'schema.elementid AS module_id, ' ||
         'schema.name AS module_name, ' ||
         'ba.elementid AS catalog_folder_id, ' ||
         'ba.name AS catalog_folder_name, ' ||
         'ba.logicalname AS business_name, ' ||
         'ba.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'ba.updatetimestamp AS updated_on, ' ||
         'ba.creationtimestamp AS created_on, ' ||
         'ba.updatedby AS updated_by, '||
         'ba.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpintelligenceschema_v schema, ' ||
         'cmpintelligencebusarea_v ba '||
        'WHERE ' ||
         'schema.strongtypename = ''oracle.wh.repos.impl.intelligenceSchema.OBIEESchema'' AND ' ||
         'schema.elementid = ba.intelligenceschema AND ' ||
         'ba.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_CATALOG_FOLDER_TABLES returns a row for each Logical Table
    -- in a Catalog Folder.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_CATALOG_FOLDER_TABLES AS ' ||
        'SELECT ' ||
         'schema.elementid AS module_id, ' ||
         'schema.name AS module_name, ' ||
         'ba.elementid AS catalog_folder_id, ' ||
         'ba.name AS catalog_folder_name, ' ||
         'qo.elementid AS logical_table_id, ' ||
         'qo.name AS logical_table_name, ' ||
         'qo.logicalname AS business_name, ' ||
         'qo.description AS description, ' ||
         'decode(qo.hidden, 0, 1, 0) AS is_visible, ' ||
         'qo.bridgetable AS is_bridge_table, ' ||
         'qo.distinctvalues AS distinct_values, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'qo.updatetimestamp AS updated_on, ' ||
         'qo.creationtimestamp AS created_on, ' ||
         'qo.updatedby AS updated_by, '||
         'qo.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpintelligenceschema_v schema, ' ||
         'cmpintelligencebusarea_v ba, '||
         'cmpintellbusareashortcut_v shortcut, '||
         'cmpqueryobject_v qo '||
        'WHERE ' ||
         'schema.strongtypename = ''oracle.wh.repos.impl.intelligenceSchema.OBIEESchema'' AND ' ||
         'schema.elementid = ba.intelligenceschema AND ' ||
         'ba.elementid = shortcut.businessarea AND ' ||
         'qo.elementid = shortcut.fco AND ' ||
         'qo.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_CATALOG_FOLDER_DRILLS returns a row for each Drill Path
    -- in a Catalog Folder.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_CATALOG_FOLDER_DRILLS AS ' ||
        'SELECT ' ||
         'schema.elementid AS module_id, ' ||
         'schema.name AS module_name, ' ||
         'ba.elementid AS catalog_folder_id, ' ||
         'ba.name AS catalog_folder_name, ' ||
         'dp.elementid AS drill_path_id, ' ||
         'dp.name AS drill_path_name, ' ||
         'dp.logicalname AS business_name, ' ||
         'dp.description AS description, ' ||
         'dp.timedimension AS is_time_dimension, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'dp.updatetimestamp AS updated_on, ' ||
         'dp.creationtimestamp AS created_on, ' ||
         'dp.updatedby AS updated_by, '||
         'dp.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpintelligenceschema_v schema, ' ||
         'cmpintelligencebusarea_v ba, '||
         'cmpintellbusareashortcut_v shortcut, '||
         'cmpdrillpath_v dp '||
        'WHERE ' ||
         'schema.elementid = ba.intelligenceschema AND ' ||
         'ba.elementid = shortcut.businessarea AND ' ||
         'dp.elementid = shortcut.fco AND ' ||
         'dp.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_DRILL_PATHS returns a row for each Drill Path
    -- in a Business Intelligence Module.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_DRILL_PATHS AS ' ||
        'SELECT ' ||
         'schema.elementid AS module_id, ' ||
         'schema.name AS module_name, ' ||
         'dp.elementid AS drill_path_id, ' ||
         'dp.name AS drill_path_name, ' ||
         'dp.logicalname AS business_name, ' ||
         'dp.description AS description, ' ||
         'decode(dp.strongtypename, ''oracle.wh.repos.impl.query.OBIEEDrillPath'', dp.timedimension, null) AS is_time_dimension, ' ||
         'dim.elementid AS dimension_id, ' ||
         'dim.name AS dimension_name, ' ||
         'role.elementid AS dimension_role_id, ' ||
         'role.name AS dimension_role_name, ' ||
         'hier.elementid AS hierarchy_id, ' ||
         'hier.name AS hierarchy_name, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'dp.updatetimestamp AS updated_on, ' ||
         'dp.creationtimestamp AS created_on, ' ||
         'dp.updatedby AS updated_by, '||
         'dp.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpintelligenceschema_v schema, ' ||
         'cmpdrillpath_v dp, '||
         'cmpdimension_v dim, '||
         'cmpdimensionrole_v role, '||
         'cmphierarchy_v hier '||
        'WHERE ' ||
         'schema.elementid = dp.intelligenceschema AND ' ||
         'dp.relateddimension = dim.elementid(+) AND ' ||
         'dp.relateddimensionrole = role.elementid(+) AND ' ||
         'dp.relatedhierarchy = hier.elementid(+) AND ' ||
         'dp.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_DRILL_LEVELS will return multiple rows for a
    -- Drill Level that has more than one parent Drill Level.
    -- To avoid this use view ALL_IV_DRILL_LEVELS_DISTINCT.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_DRILL_LEVELS AS ' ||
        'SELECT ' ||
         'dp.elementid AS drill_path_id, ' ||
         'dp.name AS drill_path_name, ' ||
         'dl.elementid AS drill_level_id, ' ||
         'dl.name AS drill_level_name, ' ||
         'dl.logicalname AS business_name, ' ||
         'dl.description AS description, ' ||
         'decode(dp.strongtypename, ''oracle.wh.repos.impl.query.OBIEEDrillPath'', dl.defaultrootlevel, null) AS is_default_root_level, ' ||
         'decode(dp.strongtypename, ''oracle.wh.repos.impl.query.OBIEEDrillPath'', dl.grandtotallevel, null) AS is_grand_total_level, ' ||
         'decode(dp.strongtypename, ''oracle.wh.repos.impl.query.OBIEEDrillPath'', dl.supportsrollup, null) AS supports_rollup, ' ||
         'parentdl.elementid AS parent_drill_level_id, ' ||
         'parentdl.name AS parent_drill_level_name, ' ||
         'lev.elementid AS related_level_id, ' ||
         'lev.name AS related_level_name, ' ||
         'qo.elementid AS item_owner_id, ' ||
         'qo.name AS item_owner_name, ' ||
         'decode(dp.strongtypename, ''oracle.wh.repos.impl.query.OBISEDrillPath'', qo.elementid, null) AS item_folder_id, ' ||
         'decode(dp.strongtypename, ''oracle.wh.repos.impl.query.OBISEDrillPath'', qo.name, null) AS item_folder_name, ' ||
         'dl.updatetimestamp AS updated_on, ' ||
         'dl.creationtimestamp AS created_on, ' ||
         'dl.updatedby AS updated_by, '||
         'dl.createdby AS created_by '||
        'FROM ' ||
         'cmpdrillpath_v dp, '||
         'cmpdrilllevel_v dl, '||
         'cmpdrillpathsegment_v dps, '||
         'cmpdrilllevel_v parentdl, '||
         'cmplevel_v lev, '||
         'cmpqueryobject_v qo '||
        'WHERE ' ||
         'dp.elementid = dl.drillpath AND ' ||
         'dl.relatedlevel = lev.elementid(+) AND ' ||
         'dl.elementid = dps.childlevel(+) AND ' ||
         'dps.parentlevel = parentdl.elementid(+) AND ' ||
         'dl.queryobject = qo.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_DRILL_LEVELS_DISTINCT will return a single row for each
    -- Drill Level (regardless of how many parent Drill Levels it has).
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_DRILL_LEVELS_DISTINCT AS ' ||
        'SELECT ' ||
         'dp.elementid AS drill_path_id, ' ||
         'dp.name AS drill_path_name, ' ||
         'dl.elementid AS drill_level_id, ' ||
         'dl.name AS drill_level_name, ' ||
         'dl.logicalname AS business_name, ' ||
         'dl.description AS description, ' ||
         'decode(dp.strongtypename, ''oracle.wh.repos.impl.query.OBIEEDrillPath'', dl.defaultrootlevel, null) AS is_default_root_level, ' ||
         'decode(dp.strongtypename, ''oracle.wh.repos.impl.query.OBIEEDrillPath'', dl.grandtotallevel, null) AS is_grand_total_level, ' ||
         'decode(dp.strongtypename, ''oracle.wh.repos.impl.query.OBIEEDrillPath'', dl.supportsrollup, null) AS supports_rollup, ' ||
         'lev.elementid AS related_level_id, ' ||
         'lev.name AS related_level_name, ' ||
         'qo.elementid AS item_owner_id, ' ||
         'qo.name AS item_owner_name, ' ||
         'decode(dp.strongtypename, ''oracle.wh.repos.impl.query.OBISEDrillPath'', qo.elementid, null) AS item_folder_id, ' ||
         'decode(dp.strongtypename, ''oracle.wh.repos.impl.query.OBISEDrillPath'', qo.name, null) AS item_folder_name, ' ||
         'dl.updatetimestamp AS updated_on, ' ||
         'dl.creationtimestamp AS created_on, ' ||
         'dl.updatedby AS updated_by, '||
         'dl.createdby AS created_by '||
        'FROM ' ||
         'cmpdrillpath_v dp, '||
         'cmpdrilllevel_v dl, '||
         'cmplevel_v lev, '||
         'cmpqueryobject_v qo '||
        'WHERE ' ||
         'dp.elementid = dl.drillpath AND ' ||
         'dl.relatedlevel = lev.elementid(+) AND ' ||
         'dl.queryobject = qo.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_DRILL_LEVEL_LINKS returns a row for each
    -- parent-child link between Drill Levels in a Drill Path.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_DRILL_LEVEL_LINKS AS ' ||
        'SELECT ' ||
         'dp.elementid AS drill_path_id, ' ||
         'dp.name AS drill_path_name, ' ||
         'parentdl.elementid AS parent_drill_level_id, ' ||
         'parentdl.name AS parent_drill_level_name, ' ||
         'childdl.elementid AS child_drill_level_id, ' ||
         'childdl.name AS child_drill_level_name ' ||
        'FROM ' ||
         'cmpdrillpath_v dp, '||
         'cmpdrilllevel_v parentdl, '||
         'cmpdrilllevel_v childdl, '||
         'cmpdrillpathsegment_v dps '||
        'WHERE ' ||
         'dp.elementid = dps.drillpath AND ' ||
         'dps.parentlevel = parentdl.elementid AND ' ||
         'childdl.elementid = dps.childlevel ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_DRILL_LEVEL_ITEMS returns a row for each Item
    -- associated with a Drill Level.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_DRILL_LEVEL_ITEMS AS ' ||
        'SELECT ' ||
         'dp.elementid AS drill_path_id, ' ||
         'dp.name AS drill_path_name, ' ||
         'dl.elementid AS drill_level_id, ' ||
         'dl.name AS drill_level_name, ' ||
         'di.elementid AS drill_item_id, ' ||
         'di.name AS drill_item_name, '||
         'di.logicalname AS business_name, ' ||
         'di.description AS description, ' ||
         'qo.elementid AS item_owner_id, ' ||
         'qo.name AS item_owner_name, ' ||
         'decode(dp.strongtypename, ''oracle.wh.repos.impl.query.OBISEDrillPath'', qo.elementid, null) AS item_folder_id, ' ||
         'decode(dp.strongtypename, ''oracle.wh.repos.impl.query.OBISEDrillPath'', qo.name, null) AS item_folder_name, ' ||
         'qi.elementid AS item_id, ' ||
         'qi.name AS item_name, ' ||
         'di.updatetimestamp AS updated_on, ' ||
         'di.creationtimestamp AS created_on, ' ||
         'di.updatedby AS updated_by, '||
         'di.createdby AS created_by ' ||
        'FROM ' ||
         'cmpdrillpath_v dp, '||
         'cmpdrilllevel_v dl, '||
         'cmpdrillitem_v di, '||
         'cmpqueryitem_v qi, '||
         'cmpqueryobject_v qo '||
        'WHERE ' ||
         'dp.elementid = dl.drillpath AND ' ||
         'dl.elementid = di.owningrelation AND ' ||
         'qi.elementid = di.queryitem AND ' ||
         'qo.elementid = qi.owningrelation ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_DRILL_LEVEL_KEYS returns a row for each Drill Level Key
    -- defined for an OBI Drill Level.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_DRILL_LEVEL_KEYS AS ' ||
        'SELECT ' ||
         'dp.elementid AS drill_path_id, ' ||
         'dp.name AS drill_path_name, ' ||
         'dl.elementid AS drill_level_id, ' ||
         'dl.name AS drill_level_name, ' ||
         'dlk.elementid AS drill_level_key_id, ' ||
         'dlk.name AS drill_level_key_name, '||
         'dlk.logicalname AS business_name, ' ||
         'dlk.description AS description, ' ||
         'dlk.primarykey AS is_primary_key, ' ||
         'dlk.chronologicalkey AS is_chronological_key, ' ||
         'dlk.usefordrilldown AS use_for_drilldown, ' ||
         'dlk.updatetimestamp AS updated_on, ' ||
         'dlk.creationtimestamp AS created_on, ' ||
         'dlk.updatedby AS updated_by, '||
         'dlk.createdby AS created_by ' ||
        'FROM ' ||
         'cmpdrillpath_v dp, '||
         'cmpdrilllevel_v dl, '||
         'cmpdrilllevelkey_v dlk '||
        'WHERE ' ||
         'dp.elementid = dl.drillpath AND ' ||
         'dl.elementid = dlk.owningrelation ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_DRILL_LEVEL_KEYS_ITEMS returns a row for each Item
    -- used in an OBI Drill Level Key.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_DRILL_LEVEL_KEY_ITEMS AS ' ||
        'SELECT ' ||
         'dp.elementid AS drill_path_id, ' ||
         'dp.name AS drill_path_name, ' ||
         'dl.elementid AS drill_level_id, ' ||
         'dl.name AS drill_level_name, ' ||
         'dlk.elementid AS drill_level_key_id, ' ||
         'dlk.name AS drill_level_key_name, '||
         'di.elementid AS drill_item_id, ' ||
         'di.name AS drill_item_name '||
        'FROM ' ||
         'cmpdrillpath_v dp, '||
         'cmpdrilllevel_v dl, '||
         'cmpdrilllevelkey_v dlk, '||
         'cmpdrilllevelkeyusage_v dlku, '||
         'cmpdrillitem_v di '||
        'WHERE ' ||
         'dp.elementid = dl.drillpath AND ' ||
         'dl.elementid = dlk.owningrelation AND ' ||
         'dlk.elementid = dlku.itemset AND ' ||
         'di.elementid = dlku.attribute ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_DRILL_PATH_JOIN_USAGES returns a row for each Join needed to
    -- navigate a Drill Level Link where the parent and child Drill Levels are
    -- associated with different Item Folders.
    -- (Only relevant for Discoverer Drill Paths.)
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_DRILL_PATH_JOIN_USAGES AS ' ||
        'SELECT ' ||
         'dp.elementid AS drill_path_id, ' ||
         'dp.name AS drill_path_name, ' ||
         'parentdl.elementid AS parent_drill_level_id, ' ||
         'parentdl.name AS parent_drill_level_name, ' ||
         'childdl.elementid AS child_drill_level_id, ' ||
         'childdl.name AS child_drill_level_name, ' ||
         'j.elementid AS join_id, ' ||
         'j.name AS join_name '||
        'FROM ' ||
         'cmpdrillpath_v dp, '||
         'cmpdrillpathsegment_v dps, '||
         'cmpdrillpathjoinusage_v joinusage, '||
         'cmpjoin_v j, '||
         'cmpdrilllevel_v parentdl, '||
         'cmpdrilllevel_v childdl '||
        'WHERE ' ||
         'dp.elementid = dps.drillpath AND ' ||
         'dps.parentlevel = parentdl.elementid AND ' ||
         'dps.childlevel = childdl.elementid AND ' ||
         'joinusage.drillpathsegment = dps.elementid AND ' ||
         'joinusage.join = j.elementid ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_LISTS_OF_VALUES returns a row for each List of Values
    -- in a Discoverer Business Intelligence Module.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_LISTS_OF_VALUES AS ' ||
        'SELECT ' ||
         'schema.elementid AS module_id, ' ||
         'schema.name AS module_name, ' ||
         'ic.elementid AS list_of_values_id, ' ||
         'ic.name AS list_of_values_name, ' ||
         'ic.logicalname AS business_name, ' ||
         'ic.description AS description, ' ||
         'ic.drilltodetail AS is_drill_to_detail, ' ||
         'ic.cached AS is_cache_values, ' ||
         'ic.requiresearch AS is_require_search, ' ||
         'ic.showinnavigator AS is_show_in_navigator, ' ||
         'ic.sorteddistinct AS is_sorted_distinct, ' ||
         'ic.arrayfetchsize AS retrieve_value_group_size, ' ||
         'defitem.elementid AS values_item_id, ' ||
         'defitem.name AS values_item_name, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'ic.updatetimestamp AS updated_on, ' ||
         'ic.creationtimestamp AS created_on, ' ||
         'ic.updatedby AS updated_by, '||
         'ic.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpintelligenceschema_v schema, ' ||
         'cmplovitemclass_v ic, '||
         'cmpqueryitem_v defitem '||
        'WHERE ' ||
         'schema.elementid = ic.intelligenceschema AND ' ||
         'ic.strongtypename = ''oracle.wh.repos.impl.query.CMPListOfValues'' AND '|| 
         'ic.itemdefininglov = defitem.elementid(+) AND ' ||
         'ic.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_ALTERNATIVE_SORT_ORDERS returns a row for each
    -- Alternative Sort Order in a Discoverer Business Intelligence Module.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_ALTERNATIVE_SORT_ORDERS AS ' ||
        'SELECT ' ||
         'schema.elementid AS module_id, ' ||
         'schema.name AS module_name, ' ||
         'ic.elementid AS alt_sort_order_id, ' ||
         'ic.name AS alt_sort_order_name, ' ||
         'ic.logicalname AS business_name, ' ||
         'ic.description AS description, ' ||
         'ic.drilltodetail AS is_drill_to_detail, ' ||
         'ic.cached AS is_cache_values, ' ||
         'ic.requiresearch AS is_require_search, ' ||
         'ic.showinnavigator AS is_show_in_navigator, ' ||
         'ic.sorteddistinct AS is_sorted_distinct, ' ||
         'ic.arrayfetchsize AS retrieve_value_group_size, ' ||
         'defitem.elementid AS values_item_id, ' ||
         'defitem.name AS values_item_name, ' ||
         'orderitem.elementid AS order_item_id, ' ||
         'orderitem.name AS order_item_name, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'ic.updatetimestamp AS updated_on, ' ||
         'ic.creationtimestamp AS created_on, ' ||
         'ic.updatedby AS updated_by, '||
         'ic.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpintelligenceschema_v schema, ' ||
         'cmplovitemclass_v ic, '||
         'cmpqueryitem_v defitem, '||
         'cmpqueryitem_v orderitem '||
        'WHERE ' ||
         'schema.elementid = ic.intelligenceschema AND ' ||
         'ic.strongtypename = ''oracle.wh.repos.impl.query.CMPAlternativeSortOrder'' AND '|| 
         'ic.itemdefininglov = defitem.elementid(+) AND ' ||
         'ic.itemdefiningorder = orderitem.elementid(+) AND ' ||
         'ic.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_DRILLS_TO_DETAIL returns a row for each Drill To Detail
    -- in a Discoverer Business Intelligence Module.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_DRILLS_TO_DETAIL AS ' ||
        'SELECT ' ||
         'schema.elementid AS module_id, ' ||
         'schema.name AS module_name, ' ||
         'ic.elementid AS drill_to_detail_id, ' ||
         'ic.name AS drill_to_detail_name, ' ||
         'ic.logicalname AS business_name, ' ||
         'ic.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'ic.updatetimestamp AS updated_on, ' ||
         'ic.creationtimestamp AS created_on, ' ||
         'ic.updatedby AS updated_by, '||
         'ic.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpintelligenceschema_v schema, ' ||
         'cmplovitemclass_v ic '||
        'WHERE ' ||
         'schema.elementid = ic.intelligenceschema AND ' ||
         'ic.strongtypename = ''oracle.wh.repos.impl.query.CMPDrillToDetail'' AND '|| 
         'ic.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';  

    -- View ALL_IV_REGISTERED_FUNCTIONS returns a row for each
    -- Registered Function in a Discoverer Business Intelligence Module.
    execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_REGISTERED_FUNCTIONS AS ' ||
        'SELECT ' ||
         'schema.elementid AS module_id, ' ||
         'schema.name AS module_name, ' ||
         'f.elementid AS registered_function_id, ' ||
         'f.name AS registered_function_name, ' ||
         'f.logicalname AS business_name, ' ||
         'f.description AS description, ' ||
         'f.signature AS signature, ' ||
         'f.available AS is_available, ' ||
         'scef.elementid AS source_function_id, ' ||
         'scef.name AS source_function_name, ' ||
         '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, ' ||
         'cmpintelligenceschema_v schema, ' ||
         'cmpfunction_v f, '||
         'cmpfunction_v scef '||
        'WHERE ' ||
         'schema.elementid = f.installedmodule AND ' ||
         'f.strongtypename = ''oracle.wh.repos.impl.functional.CMPIOFunction'' AND '|| 
         'f.designfunction = scef.elementid(+) AND '|| 
         'f.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';  

END;
/

BEGIN

  --Created for Paris: by Helen
  execute immediate
     'CREATE OR REPLACE VIEW WB$_IV_DATA_RULE_MODULES AS ' ||
        'SELECT  ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'schema.logicalname AS business_name, ' ||
         'schema.description AS description, ' ||
         'schema.status AS status, '||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||               
         'schema.updatetimestamp AS updated_on, ' ||
         'schema.creationtimestamp AS created_on,  ' ||
         'schema.updatedby AS updated_by, '||
         'schema.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'CMPBUSINESSRULEMODULE_V schema,  ' ||
         'cmpwbproject_v project ' ||
        'WHERE  ' ||
         'schema.owningproject = project.elementid AND ' ||
         'schema.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';


    --Created for Paris: by Helen

    execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_DATA_RULES AS ' ||
        'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'dr.elementid AS datarule_id, ' ||
         'dr.name AS datarule_name, ' ||
         'dr.logicalname AS business_name, ' ||
         'pv.value AS datarule_type, ' ||
         'dr.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'dr.updatetimestamp AS updated_on, ' ||
         'dr.creationtimestamp AS created_on, ' ||
         'dr.updatedby AS updated_by, '||
         'dr.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpbusinessrulemodule_v schema, ' ||
         'cmpbusinessruledefinition_v dr, ' ||
         'cmpstringpropertyvalue_v pv ' ||
        'WHERE  ' ||
         'schema.elementid = dr.OWNINGMODULE AND ' ||
         'dr.validationresult = v.elementid(+) AND ' ||
         'pv.propertyowner = dr.elementid(+) AND ' ||
         'pv.logicalname = ''DATA_RULE.RULE_TYPE'' ' ||
         'WITH READ ONLY';  


    --Created for Paris: by Helen
    execute immediate  
    'CREATE OR REPLACE VIEW WB$_IV_DATA_RULE_GROUPS AS ' ||
        'SELECT ' ||
         'rd.elementid AS datarule_id, ' ||
         'rd.name AS datarule_name, ' ||
         'g.elementid AS datarulegroup_id, ' ||
         'g.name AS datarulegroup_name, ' ||
         'g.logicalname AS business_name, ' ||
         'g.description AS description, ' ||
         'g.updatetimestamp AS updated_on, ' ||
         'g.creationtimestamp AS created_on, ' ||
         'g.updatedby AS updated_by, '||
         'g.createdby AS created_by '||
        'FROM ' ||
         'CMPBUSINESSRULEDEFINITION_V rd, ' ||
         'CMPBUSINESSRULERELPARAM_V g ' ||
        'WHERE  ' ||
         'rd.elementid = g.OWNINGRULEDEFINITION ' ||
         'WITH READ ONLY';  

--------------------------------------------------------------------------------
    --Created for Paris: by rspaul
    execute immediate  
    'CREATE OR REPLACE VIEW WB$_IV_DATA_RULE_ATTRIBUTES AS ' ||
        'SELECT ' ||
         'rd.elementid AS datarule_id, ' ||
         'rd.name AS datarule_name, ' ||
         'g.elementid AS datarulegroup_id, ' ||
         'g.name AS datarulegroup_name, ' ||
         'a.elementid AS dataruleattr_id,' ||
         'a.name AS name, ' ||
         'a.logicalname AS business_name, ' ||
         'a.description AS description, ' ||
         'a.updatetimestamp AS updated_on, ' ||
         'a.creationtimestamp AS created_on, ' ||
         'a.updatedby AS updated_by, '||
         'a.createdby AS created_by '||
        'FROM ' ||
         'CMPBUSINESSRULEDEFINITION_V rd, ' ||
         'CMPBUSINESSRULERELPARAM_V g, ' || 
         'CMPBUSINESSRULEITEMPARAM_V a ' ||
        'WHERE  ' ||
         'g.elementid = a.owningrelation AND ' ||
         'rd.elementid = g.owningruledefinition ' ||
         'WITH READ ONLY';  



    execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_DATA_RULE_PROPERTIES AS ' ||
        'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'dr.elementid AS datarule_id, ' ||
         'dr.name AS datarule_name, ' ||
         'pv.elementid AS property_id, ' ||
         'pv.name AS name, ' ||
         'pv.logicalname AS business_name, ' ||
         'pv.value AS value, ' ||
         'pv.description AS description, ' ||
         'pv.updatetimestamp AS updated_on, ' ||
         'pv.creationtimestamp AS created_on, ' ||
         'pv.updatedby AS updated_by, '||
         'pv.createdby AS created_by '||
        'FROM ' ||
         'cmpbusinessrulemodule_v schema, ' ||
         'cmpbusinessruledefinition_v dr, ' ||
         'cmpstringpropertyvalue_v pv ' ||
        'WHERE  ' ||
         'schema.elementid = dr.OWNINGMODULE AND ' ||
         'pv.propertyowner = dr.elementid(+) AND ' ||
         'pv.logicalname != ''DATA_RULE.RULE_TYPE'' ' ||
         'WITH READ ONLY';  

    execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_DATA_RULE_DOMAINS AS ' ||
        'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'dr.elementid AS datarule_id, ' ||
         'dr.name AS datarule_name, ' ||
         'pv.elementid AS property_id, ' ||
         'pv.name AS name, ' ||
         'pv.logicalname AS business_name, ' ||
         'pv.description AS description, ' ||
         'pv.updatetimestamp AS updated_on, ' ||
         'pv.creationtimestamp AS created_on, ' ||
         'pv.updatedby AS updated_by, '||
         'pv.createdby AS created_by '||
        'FROM ' ||
         'cmpbusinessrulemodule_v schema, ' ||
         'cmpbusinessruledefinition_v dr, ' ||
         'cmppurephysicalobject_v pv ' ||
        'WHERE  ' ||
         'schema.elementid = dr.OWNINGMODULE AND ' ||
         'pv.propertyowner = dr.elementid(+) AND ' ||
         'pv.logicalname = ''DATA_RULE.DOMAIN_VALUES'' ' ||
         'WITH READ ONLY';  


    execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_DATA_RULE_USAGES AS ' ||
        'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'r.elementid AS relation_id, ' ||
         'r.name AS relation_name, ' ||
         'dru.elementid AS datarule_usage_id, ' ||
         'dru.name AS name, ' ||
         'dru.logicalname AS business_name, ' ||
         'dru.description AS description, ' ||
         'dr_schema.elementid AS datarule_schema_id, ' ||
         'dr_schema.name AS datarule_schema_name, ' ||
         'dr.elementid AS datarule_id, ' ||
         'dr.name AS datarule_name, ' ||
         'dru.updatetimestamp AS updated_on, ' ||
         'dru.creationtimestamp AS created_on, ' ||
         'dru.updatedby AS updated_by, '||
         'dru.createdby AS created_by '||
         
        'FROM ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelationaldbobject_v r, ' ||
         'cmpbusinessrulemodule_v dr_schema, ' ||
         'cmpbusinessruledefinition_v dr, ' ||
         'cmpbusinessruleusage_v dru ' ||
        'WHERE  ' ||
         'schema.elementid = r.installedmodule AND ' ||
         'dr_schema.elementid = dr.owningmodule AND ' ||
         'dru.owningrelation = r.elementid(+) AND ' ||
         'dru.ruledefinition = dr.elementid(+) ' ||
         'WITH READ ONLY';


    execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_DATA_RULE_GROUP_USAGES AS ' ||
        'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'r.elementid AS relation_id, ' ||
         'r.name AS relation_name, ' ||
         'dru.elementid AS datarule_usage_id, ' ||
         'dru.name AS datarule_usage_name, ' ||
         'drgu.elementid AS datarule_group_usage_id, ' ||
         'drgu.name AS name, ' ||
         'drgu.logicalname as business_name, ' ||
         'drgu.description AS description, ' ||
         'dr_schema.elementid AS datarule_schema_id, ' ||
         'dr_schema.name AS datarule_schema_name, ' ||
         'dr.elementid AS datarule_id, ' ||
         'dr.name AS datarule_name, ' ||
         'drg.elementid as datarule_group_id, ' ||
         'drg.name as datarule_group_name, ' ||
         'drgr_schema.elementid AS referenced_schema, ' ||
         'drgr_schema.name AS reference_schema_name, ' ||
         'drgr.elementid as referenced_relation_id, ' ||
         'drgr.name as reference_relation_name, ' ||
         'drgu.updatetimestamp AS updated_on, ' ||
         'drgu.creationtimestamp AS created_on, ' ||
         'drgu.updatedby AS updated_by,' ||
         'drgu.createdby AS created_by ' ||
        'FROM ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelationaldbobject_v r, ' ||
         'cmpbusinessrulemodule_v dr_schema, ' ||
         'cmpbusinessruledefinition_v dr, ' ||
         'cmpbusinessruleusage_v dru, ' ||
         'cmprelationusage_v drgu, ' ||
         'cmpbusinessrulerelparam_v drg, ' ||
         'cmpinstalledmodule_v drgr_schema, ' ||
         'cmprelationaldbobject_v drgr ' ||
        'WHERE  ' ||
         'schema.elementid = r.installedmodule AND ' ||
         'dr_schema.elementid = dr.owningmodule AND ' ||
         'drgr_schema.elementid = drgr.installedmodule AND ' ||
         'dru.owningrelation = r.elementid(+) AND ' ||
         'drgu.referencedreldefinition = drg.elementid(+) AND ' ||
         'dru.ruledefinition = dr.elementid(+) AND ' ||
         'drgu.businessruleusage = dru.elementid(+) AND ' ||
         'drgu.referencedrelation = drgr.elementid(+) ' ||
         'WITH READ ONLY ';
         
    execute immediate 
      'CREATE OR REPLACE VIEW WB$_IV_DATA_RULE_ATTR_USAGES AS ' ||
        'SELECT ' ||
         'schema.elementid AS schema_id, '||
         'schema.name AS schema_name, '||
         'r.elementid AS relation_id, '||
         'r.name AS relation_name, '||
         'dru.elementid AS datarule_usage_id, '||
         'dru.name AS datarule_usage_name, '||
         'drgu.elementid AS datarule_group_usage_id, '||
         'drgu.name AS datarule_group_usage_name, ' ||
         'drau.elementid AS datarule_attr_usage_id, ' ||
         'drau.name AS name, ' ||
         'drau.logicalname AS business_name, '||
         'drau.description AS description, '||
         'dr_schema.elementid AS datarule_schema_id, '||
         'dr_schema.name AS datarule_schema_name, '||
         'dr.elementid AS datarule_id, '||
         'dr.name AS datarule_name, '||
         'drg.elementid as datarule_group_id, '||
         'drg.name as datarule_group_name, ' ||
         'draua.elementid as datarule_attr_id, ' ||
         'draua.name as datarule_attr_name, ' ||
         'drgr_schema.elementid AS referenced_schema, '||
         'drgr_schema.name AS reference_schema_name, '||
         'drgr.elementid as referenced_relation_id, '||
         'drgr.name as reference_relation_name, '||
         'draur.elementid as attribute_id, ' ||
         'draur.name as attribute_name, ' ||
         'drau.updatetimestamp AS updated_on, '||
         'drau.creationtimestamp AS created_on, '||
         'drau.updatedby AS updated_by, ' ||
         'drau.createdby AS created_by '||
        'FROM '||
         'cmpinstalledmodule_v schema, '||
         'cmprelationaldbobject_v r, '||
         'cmpbusinessrulemodule_v dr_schema, '||
         'cmpbusinessruledefinition_v dr, '||
         'cmpbusinessruleusage_v dru, '||
         'cmprelationusage_v drgu, '||
         'cmpbusinessrulerelparam_v drg, '||
         'cmpinstalledmodule_v drgr_schema, '||
         'cmprelationaldbobject_v drgr, ' ||
         'cmpwbitemsetusage_v drau, ' ||
         'cmpattribute_v draur, ' ||
         'cmpbusinessruleitemparam_v draua ' ||
        'WHERE  '||
         'schema.elementid = r.installedmodule AND '||
         'dr_schema.elementid = dr.owningmodule AND '||
         'drgr_schema.elementid = drgr.installedmodule AND '||
         'dru.owningrelation = r.elementid(+) AND '||
         'drgu.referencedreldefinition = drg.elementid(+) AND '||
         'dru.ruledefinition = dr.elementid(+) AND '||
         'drgu.businessruleusage = dru.elementid(+) AND '||
         'drgu.referencedrelation = drgr.elementid(+) AND ' ||
         'drau.attribute = draur.elementid(+) AND '||
         'drau.itemset = drgu.elementid(+) AND ' ||
         'drau.referenceditemdefinition = draua.elementid(+) ' ||
        'WITH READ ONLY' ;

         
------------------------------------------------------------------------------

---------------------------------------------------------------
--bug 4715297: data audit info is now moved into all_iv_xform_maps and all_iv_xform_map_components
    --Created for Paris: by Helen
--    execute immediate 
--    'CREATE OR REPLACE VIEW WB$_IV_DATA_AUDIT_MAPS AS ' ||
--        'SELECT ' ||
--         'schema.elementid AS schema_id, ' ||
--         'schema.name AS schema_name, ' ||
--         'd.elementid AS data_audit_id, ' ||
--         'd.name AS data_audit_name, ' ||
--         'd.logicalname AS business_name, ' ||
--         'd.description AS description, ' ||
--         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
--         'd.updatetimestamp AS updated_on, ' ||
--         'd.creationtimestamp AS created_on, ' ||
--         'd.updatedby AS updated_by, '||
--         'd.createdby AS created_by '||
--        'FROM ' ||
--         'cmpwbvalidationresult_v v, ' ||
--         'cmpinstalledmodule_v schema, ' ||
--         'CMPDATARULEMAP_V d ' ||
--        'WHERE  ' ||
--         'schema.elementid = d.INSTALLEDMODULE AND ' ||
--         'd.validationresult = v.elementid(+) ' ||
--         'WITH READ ONLY';  


   --Created for Paris: by Helen
--   execute immediate
--    'CREATE OR REPLACE VIEW WB$_IV_DATA_AUDIT_OPERATORS AS ' ||
--        'SELECT  ' ||
--         'm.elementid AS map_id, ' ||
--         'm.name AS map_name, ' ||
--         'o.elementid AS map_operator_id, ' ||
--         'o.name AS map_operator_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, ' ||
--         'CMPDATARULEMAP_V m, ' ||
--         'cmpmapoperator_v o ' ||
--        'WHERE  ' ||
--         'o.operatorowner = m.elementid AND ' ||
--         'o.referencingobject = bindee.elementid(+) ' ||
--         'WITH READ ONLY';




----------------------------------------------------------------
   --Created for Paris: by Helen
   execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_CALENDAR_MODULES AS ' ||
       'SELECT ' ||
         'proj.elementid AS project_id, '||
         'proj.name AS project_name, '||
         'cm.elementid AS calendar_module_id, '||
         'cm.name AS calendar_module_name, '||
         'cm.logicalname AS business_name, ' ||
         'cm.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'cm.updatetimestamp AS updated_on, ' ||
         'cm.creationtimestamp AS created_on, ' ||
         'cm.updatedby AS updated_by, '||
         'cm.createdby AS created_by '||
      'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v proj, '||
         'CMPCALENDARINSTALLEDMODULE_V cm '||
      'WHERE ' ||
         'cm.owningproject = proj.elementid AND '||
         'cm.validationresult = v.elementid(+) ' ||
     'WITH READ ONLY';

   
   --Created for Paris: by Helen
   execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_CALENDARS AS ' ||
       'SELECT ' ||
         'mod.elementid AS calendar_module_id, '||
         'mod.name AS calendar_module_name, '||
         'cd.elementid AS calendar_id, '||
         'cd.name AS calendar_name, '||
         'cd.logicalname AS business_name, ' ||
         'cd.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'cd.updatetimestamp AS updated_on, ' ||
         'cd.creationtimestamp AS created_on, ' ||
         'cd.updatedby AS updated_by, '||
         'cd.createdby AS created_by '||
      'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'CMPCALENDARINSTALLEDMODULE_V mod, '||
         'CMPCALENDAR_V cd '||
      'WHERE ' ||
         'cd.OWNINGFOLDER = mod.elementid AND '||
         'cd.validationresult = v.elementid(+)  ' ||
     'WITH READ ONLY';


   --Created for Paris: by Helen
   execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_CALENDAR_SCHEDULES AS ' ||
       'SELECT ' ||
         'c.elementid AS calendar_id, '||
         'c.name AS calendar_name, '||
         's.elementid AS schedule_id, '||
         's.name AS schedule_name, '||
         's.logicalname AS business_name, ' ||
         's.description AS description, ' ||
         's.updatetimestamp AS updated_on, ' ||
         's.creationtimestamp AS created_on, ' ||
         's.updatedby AS updated_by, '||
         's.createdby AS created_by, '||
         's.STARTTIME AS STARTTIME, '||
         's.ENDTIME AS ENDTIME, '||
         's.REPEATEXPRESSION AS REPEATEXPRESSION, '||
         's.TIMEZONE AS TIMEZONE '||
      'FROM ' ||
         'CMPCALENDAR_V c, '||
         'cmptimewindow_v s '||
      'WHERE ' ||
         's.OWNER = c.elementid '||
     'WITH READ ONLY';


   --Created for Paris: by Helen
   execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_ACTIVITY_FOLDERS AS ' ||
       'SELECT ' ||
         'proj.elementid AS project_id, '||
         'proj.name AS project_name, '||
         'cm.elementid AS activity_folder_id, '||
         'cm.name AS activity_folder_name, '||
         'cm.logicalname AS business_name, ' ||
         'cm.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'cm.updatetimestamp AS updated_on, ' ||
         'cm.creationtimestamp AS created_on, ' ||
         'cm.updatedby AS updated_by, '||
         'cm.createdby AS created_by '||
      'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v proj, '||
         'CMPPROGRAMGROUP_V cm '||
      'WHERE ' ||
         'cm.owningproject = proj.elementid AND '||
         'cm.validationresult = v.elementid(+) ' ||
     'WITH READ ONLY';


  --Created for Paris: by Helen
   execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_ACTIVITY_TEMPLATES AS ' ||
       'SELECT ' ||
         'fd.elementid AS activity_folder_id, '||
         'fd.name AS activity_folder_name, '||
         'p.elementid AS activity_template_id, '||
         'p.name AS activity_template_name, '||
         'p.logicalname AS business_name, ' ||
         'p.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'p.updatetimestamp AS updated_on, ' ||
         'p.creationtimestamp AS created_on, ' ||
         'p.updatedby AS updated_by, '||
         'p.createdby AS created_by '||
      'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'CMPPROGRAMGROUP_V fd, '||
         'CMPPROGRAM_V p '||
      'WHERE ' ||
         'p.OWNINGFOLDER = fd.elementid AND '||
         'p.validationresult = v.elementid(+) ' ||
     'WITH READ ONLY';



---------------------------------------

   --Created for Paris: by Helen
   execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_PLUGGABLE_MAPS AS ' ||
        'SELECT  ' ||
         'proj.elementid AS project_id, ' ||
         'proj.name AS project_name, ' ||
         'NULL AS map_library_id, ' ||
         'NULL AS map_library_name, ' ||
         'smap.elementid AS map_id, ' ||
         'smap.name AS map_name, ' ||
         'smap.logicalname AS business_name, ' ||
         'smap.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||         
         'smap.updatetimestamp AS updated_on, ' ||
         'smap.creationtimestamp AS created_on,  ' ||
         'smap.updatedby AS updated_by, '||
         'smap.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v proj , ' || 
         'CMPPUBLICSUBMAP_V smap  ' ||
        'WHERE  ' ||
         'smap.OWNINGPROJECT = proj.elementid AND ' ||
         'smap.validationresult = v.elementid(+) ' ||
    'UNION '||
        'SELECT  ' ||
         'proj.elementid AS project_id, ' ||
         'proj.name AS project_name, ' ||
         'lib.elementid AS map_library_id, ' ||
         'lib.name AS map_library_name, ' ||
         'smap.elementid AS map_id, ' ||
         'smap.name AS map_name, ' ||
         'smap.logicalname AS business_name, ' ||
         'smap.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||      
         'smap.updatetimestamp AS updated_on, ' ||
         'smap.creationtimestamp AS created_on,  ' ||
         'smap.updatedby AS updated_by, '||
         'smap.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v proj , ' || 
         'CMPSUBMAPLIBRARY_V lib, ' ||
         'CMPPUBLICSUBMAP_V smap  ' ||
        'WHERE  ' ||
         'smap.OWNINGPROJECT IS NULL AND ' ||
         'smap.OWNINGFOLDER = lib.elementid AND ' ||
         'lib.OWNINGFOLDER = proj.elementid AND ' ||
         'smap.validationresult = v.elementid(+)  ' ||  
         'WITH READ ONLY';        


   --Created for Paris: by Helen
   execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_PLUGGABLE_MAP_LIBRARIES AS ' ||
        'SELECT  ' ||
         'proj.elementid AS project_id, ' ||
         'proj.name AS project_name, ' ||
         'LIB.elementid AS map_library_id, ' ||
         'LIB.name AS map_library_name, ' ||
         'LIB.logicalname AS business_name, ' ||
         'LIB.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||      
         'LIB.updatetimestamp AS updated_on, ' ||
         'LIB.creationtimestamp AS created_on,  ' ||
         'LIB.updatedby AS updated_by, '||
         'LIB.createdby AS created_by '||
        'FROM  ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v proj , ' || 
         'CMPSUBMAPLIBRARY_V lib ' ||
        'WHERE  ' ||
         'lib.OWNINGFOLDER = proj.elementid AND ' ||
         'LIB.validationresult = v.elementid(+)  ' ||  
         'WITH READ ONLY';   


--bug4256131: adding pluggable map components public view
--Created by fryang
   execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_PLUG_MAP_COMPONENTS AS ' ||
        'SELECT  ' ||
         'smap.elementid AS map_id, ' ||
         'smap.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, ' ||
         'smap.elementid AS composite_component_id, ' ||
         'smap.name AS composite_component_name, ' ||
         'bindee.elementid AS data_entity_id, ' ||
         'OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(bindee.elementid,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, ' ||
         'CMPPUBLICSUBMAP_V smap,  ' ||
         'cmpmapoperator_v o ' ||
        'WHERE  ' ||
         'o.operatorowner = smap.elementid AND ' ||
         'o.referencingobject = bindee.elementid(+)' ||
         'WITH READ ONLY';
       
   
--bug4715297: adding pluggable map parameters public view
--Created by fryang
   execute immediate 
	  'CREATE OR REPLACE VIEW WB$_IV_PLUG_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, ' ||
         'smap.elementid AS map_id, ' ||
         'smap.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, ' ||        
         'CMPPUBLICSUBMAP_V smap,  ' ||
         'cmpmapoperator_v o, ' ||         
         'cmpmapattributegroup_v g, ' ||         
         'cmpmapattribute_v a ' ||        
        'WHERE ' ||
         'g.elementid = a.attributegroup AND  ' ||
         'o.elementid = g.attributegroupowner AND ' ||
         'o.operatorowner = smap.elementid AND ' ||
         'a.connectfrom = a2.elementid(+) AND ' ||
         'a.referencingobject = bindee.elementid(+) ' ||
        'WITH READ ONLY'; 
   
----------------------------------------------------------------
   
   --Created for Paris: by Helen
   execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_PROFILES AS ' ||
       'SELECT ' ||
         'proj.elementid AS project_id, '||
         'proj.name AS project_name, '||
         'pf.elementid AS profile_id, '||
         'pf.name AS profile_name, '||
         'pf.logicalname AS business_name, ' ||
         'pf.description AS description, ' ||
         'pf.updatetimestamp AS updated_on, ' ||
         'pf.creationtimestamp AS created_on, ' ||
         'pf.updatedby AS updated_by, '||
         'pf.createdby AS created_by '||
      'FROM ' ||
         'cmpwbproject_v proj, '||
         'cmpprofile_v pf '||
      'WHERE ' ||
         'pf.owningproject = proj.elementid  '||
     'WITH READ ONLY';


   --Created for Paris: by Helen
   execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_PROFILE_OBJECTS AS ' ||
       'SELECT ' ||
         'pf.elementid AS profile_id, '||
         'pf.name AS profile_name, '||
         'map.elementid AS profile_object_id, '||
         'map.referencedrelation AS object_id, '||
         'map.name AS profile_object_name, ' ||
         'r.name AS object_name, '||
         'OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(r.elementid, r.classname) AS object_type, '||
         'r.logicalname AS business_name, ' ||
         'map.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'TO_NUMBER(prop.value)  as  row_count, ' ||
         'map.updatetimestamp AS updated_on, ' ||
         'map.creationtimestamp AS created_on, ' ||
         'map.updatedby AS updated_by, '||
         'map.createdby AS created_by '||
      'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpprofiletable_v map, '||
         'cmpprofile_v pf, '||
         'cmprelation_v r, '||
         'cmpstringpropertyvalue_v prop ' ||
      'WHERE ' ||
         'map.OWNINGFOLDER       = pf.elementid   AND ' ||
         'map.REFERENCEDRELATION = r.elementid(+) AND ' ||
         'pf.validationresult    = v.elementid(+) AND ' ||
         'map.elementid          = prop.propertyowner(+)  AND ' ||
         'prop.logicalname(+)    = ''8i.PROFILE_TABLES.PROFILE_RESULTS.NUM_ROWS'' ' ||
     'WITH READ ONLY';

-- created for Paris: By Ben
execute immediate
'CREATE OR REPLACE VIEW WB$_IV_PROFILE_COLUMNS AS ' ||

  'SELECT ' || 
    
    'JOINED_PROFILE_ATTRIBUTES.PROFILE_ID   as PROFILE_ID, ' ||
    'JOINED_PROFILE_ATTRIBUTES.PROFILE_NAME as PROFILE_NAME, ' ||

    'JOINED_PROFILE_ATTRIBUTES.PROFILE_OBJECT_ID as PROFILE_OBJECT_ID, ' ||    
    'REF_RELATION.ELEMENTID                 as ENTITY_ID, ' ||
    'REF_RELATION.NAME                      as ENTITY_NAME, ' ||
    'JOINED_PROFILE_ATTRIBUTES.ELEMENTID    as PROFILE_COLUMN_ID, ' ||    
    'REF_ITEM.ELEMENTID                     as COLUMN_ID, ' ||
    'REF_ITEM.NAME                          as COLUMN_NAME, ' ||
    'REF_ITEM.LOGICALNAME                   as BUSINESS_NAME, ' ||

    -- profile results
    'COLUMN_PROPERTIES.AVG_VALUE                as AVG_VALUE, ' ||
    'COLUMN_PROPERTIES.MAX_VALUE                as MAX_VALUE, ' ||
    'COLUMN_PROPERTIES.MIN_VALUE                as MIN_VALUE, ' ||
    'COLUMN_PROPERTIES.MEDIAN_VALUE             as MEDIAN_VALUE, ' ||
    'COLUMN_PROPERTIES.STDDEV_VALUE             as STDDEV_VALUE, ' ||

    'TO_NUMBER(COLUMN_PROPERTIES.NUM_NULLS)     as NUM_NULLS, ' ||
    'TO_NUMBER(COLUMN_PROPERTIES.NUM_DISTINCT)  as NUM_DISTINCT, ' ||

    'COLUMN_PROPERTIES.CONSENSUS_DATATYPE       as CONSENSUS_DATATYPE, ' ||
    'COLUMN_PROPERTIES.CONSENSUS_DATATYPE_CNT   as CONSENSUS_DATATYPE_CNT, ' ||
    'TO_NUMBER(COLUMN_PROPERTIES.CONSENSUS_LENGTH)         as CONSENSUS_LENGTH, ' ||
    'TO_NUMBER(COLUMN_PROPERTIES.CONSENSUS_LENGTH_CNT)     as CONSENSUS_LENGTH_CNT, ' ||
    'TO_NUMBER(COLUMN_PROPERTIES.CONSENSUS_PRECISION)      as CONSENSUS_PRECISION, ' ||
    'TO_NUMBER(COLUMN_PROPERTIES.CONSENSUS_PRECISION_CNT)  as CONSENSUS_PRECISION_CNT, ' ||
    'TO_NUMBER(COLUMN_PROPERTIES.CONSENSUS_SCALE)          as CONSENSUS_SCALE, ' ||
    'TO_NUMBER(COLUMN_PROPERTIES.CONSENSUS_SCALE_CNT)      as CONSENSUS_SCALE_CNT, ' ||
    'COLUMN_PROPERTIES.COMMON_FORMAT            as COMMON_FORMAT, ' ||
    'COLUMN_PROPERTIES.COMMON_FORMAT_CNT        as COMMON_FORMAT_CNT, ' ||
    'COLUMN_PROPERTIES.DOMINANT_CHARACTER_PATTERN          as DOMINANT_CHARACTER_PATTERN, ' ||
    'COLUMN_PROPERTIES.DOMINANT_CHARACTER_PATTERN_CNT      as DOMINANT_CHARACTER_PATTERN_CNT, ' ||
    'COLUMN_PROPERTIES.DOMINANT_WORD_PATTERN               as DOMINANT_WORD_PATTERN, ' ||
    'COLUMN_PROPERTIES.DOMINANT_WORD_PATTERN_CNT           as DOMINANT_WORD_PATTERN_CNT, ' ||
    'TO_NUMBER(COLUMN_PROPERTIES.MAX_LENGTH)    as MAX_LENGTH, ' ||
    'TO_NUMBER(COLUMN_PROPERTIES.MIN_LENGTH)    as MIN_LENGTH, ' ||
    'TO_NUMBER(COLUMN_PROPERTIES.MAX_PRECISION) as MAX_PRECISION, ' ||
    'TO_NUMBER(COLUMN_PROPERTIES.MIN_PRECISION) as MIN_PRECISION, ' ||
    'TO_NUMBER(COLUMN_PROPERTIES.MAX_SCALE)     as MAX_SCALE, ' ||
    'TO_NUMBER(COLUMN_PROPERTIES.MIN_SCALE)     as MIN_SCALE, ' ||

    'COLUMN_QUERIES.CFORMAT_COMPLIANT_QUERY     as CFORMAT_COMPLIANT_QUERY, ' ||
    'COLUMN_QUERIES.CFORMAT_DRILLDOWN_QUERY     as CFORMAT_DRILLDOWN_QUERY, ' ||
    'COLUMN_QUERIES.CFORMAT_NONCOMPLIANT_QUERY  as CFORMAT_NONCOMPLIANT_QUERY, ' ||
    'COLUMN_QUERIES.DATATYPE_COMPLIANT_QUERY    as DATATYPE_COMPLIANT_QUERY, ' ||
    'COLUMN_QUERIES.DATATYPE_DRILLDOWN_QUERY    as DATATYPE_DRILLDOWN_QUERY, ' ||
    'COLUMN_QUERIES.DATATYPE_NONCOMPLIANT_QUERY as DATATYPE_NONCOMPLIANT_QUERY, ' ||
    'COLUMN_QUERIES.DOMAIN_COMPLIANT_QUERY      as DOMAIN_COMPLIANT_QUERY, ' ||
    'COLUMN_QUERIES.DOMAIN_DRILLDOWN_QUERY      as DOMAIN_DRILLDOWN_QUERY, ' ||
    'COLUMN_QUERIES.DOMAIN_NONCOMPLIANT_QUERY   as DOMAIN_NONCOMPLIANT_QUERY, ' ||
    'COLUMN_QUERIES.NULL_COMPLIANT_QUERY        as NULL_COMPLIANT_QUERY, ' ||
    'COLUMN_QUERIES.NULL_DRILLDOWN_QUERY        as NULL_DRILLDOWN_QUERY, ' ||
    'COLUMN_QUERIES.NULL_NONCOMPLIANT_QUERY     as NULL_NONCOMPLIANT_QUERY, ' ||
    'COLUMN_QUERIES.CHAR_PATTERN_COMPLIANT_QUERY           as CHAR_PATTERN_COMPLIANT_QUERY, ' ||
    'COLUMN_QUERIES.CHAR_PATTERN_DRILLDOWN_QUERY           as CHAR_PATTERN_DRILLDOWN_QUERY, ' ||
    'COLUMN_QUERIES.CHAR_PATTERN_NONCOM_QUERY              as CHAR_PATTERN_NONCOM_QUERY, ' ||
    'COLUMN_QUERIES.WORD_PATTERN_COMPLIANT_QUERY           as WORD_PATTERN_COMPLIANT_QUERY, ' ||
    'COLUMN_QUERIES.WORD_PATTERN_DRILLDOWN_QUERY           as WORD_PATTERN_DRILLDOWN_QUERY, ' ||
    'COLUMN_QUERIES.WORD_PATTERN_NONCOM_QUERY              as WORD_PATTERN_NONCOM_QUERY, ' ||

    'JOINED_PROFILE_ATTRIBUTES.UPDATED_ON   as UPDATED_ON, ' ||
    'JOINED_PROFILE_ATTRIBUTES.CREATED_ON   as CREATED_ON, ' ||
    'JOINED_PROFILE_ATTRIBUTES.UPDATED_BY   as UPDATED_BY, ' ||
    'JOINED_PROFILE_ATTRIBUTES.CREATED_BY   as CREATED_BY ' ||


  'FROM ' ||
    '( ' ||
    'SELECT ' ||
      'PROFILE.ELEMENTID      as  PROFILE_ID, ' ||
      'PROFILE.NAME           as  PROFILE_NAME, ' ||
      'TAB.ELEMENTID          as  PROFILE_OBJECT_ID, ' ||
      'TAB.REFERENCEDRELATION as  REFERENCED_RELATION, ' ||
      'ATTR.NAME              as  NAME, ' ||
      'ATTR.REFERENCEDITEM    as  REFERENCED_ITEM, ' ||
      'ATTR.UPDATETIMESTAMP   as  UPDATED_ON, ' ||
      'ATTR.CREATIONTIMESTAMP as  CREATED_ON, ' ||
      'ATTR.UPDATEDBY         as  UPDATED_BY, ' ||
      'ATTR.CREATEDBY         as  CREATED_BY, ' ||
      'ATTR.ELEMENTID         as  ELEMENTID   ' ||

    'FROM ' ||
      'CMPPROFILE_V             PROFILE, ' ||
      'CMPPROFILETABLE_V        TAB, ' ||
      'CMPPROFILEATTRIBUTE_V    ATTR ' ||

    'WHERE ' ||
      'ATTR.REFERENCEDITEM IS NOT NULL             AND ' ||
      'ATTR.OWNINGRELATION = TAB.ELEMENTID         AND ' ||
      'TAB.OWNINGFOLDER    = PROFILE.ELEMENTID         ' ||
    ') ' ||
    '                         JOINED_PROFILE_ATTRIBUTES,  ' ||
    'CMPRELATION_V            REF_RELATION, ' ||
    'CMPITEM_V                REF_ITEM, ' ||

    'TABLE(DATA_PROFILE_UTL.GET_PROFILE_COLUMN_PROPERTIES) COLUMN_PROPERTIES, ' ||
    'TABLE(DATA_PROFILE_UTL.GET_PROFILE_COLUMN_QUERIES)    COLUMN_QUERIES ' ||

  'WHERE ' ||

    'JOINED_PROFILE_ATTRIBUTES.REFERENCED_RELATION = REF_RELATION.ELEMENTID(+) AND ' ||
    'JOINED_PROFILE_ATTRIBUTES.REFERENCED_ITEM     = REF_ITEM.ELEMENTID(+) AND ' ||
    'JOINED_PROFILE_ATTRIBUTES.ELEMENTID           = COLUMN_PROPERTIES.PROPERTYOWNER(+) AND ' ||
    'JOINED_PROFILE_ATTRIBUTES.ELEMENTID           = COLUMN_QUERIES.PROPERTYOWNER(+) ' ||

'WITH READ ONLY'
;
    
-- created for Paris: By Ben
execute immediate
'CREATE OR REPLACE VIEW WB$_IV_PROFILE_UNIQUE_KEYS AS ' ||

  'SELECT ' || 
    
    'JOINED_PROFILE_UKS.PROFILE_ID     as PROFILE_ID, ' ||
    'JOINED_PROFILE_UKS.PROFILE_NAME   as PROFILE_NAME, ' ||
    'REF_RELATION.ELEMENTID            as ENTITY_ID, ' ||
    'REF_RELATION.NAME                 as ENTITY_NAME, ' ||
    'JOINED_PROFILE_UKS.ELEMENTID      as UNIQUE_KEY_ID, ' ||
    'JOINED_PROFILE_UKS.NAME           as UNIQUE_KEY_NAME, ' ||
    'JOINED_PROFILE_UKS.LOGICALNAME    as BUSINESS_NAME, ' ||

    'DECODE(JOINED_PROFILE_UKS.TYPE, 2, ''YES'', 3, ''YES'', ''NO'') as IS_DISCOVERED, ' ||
    'DECODE(JOINED_PROFILE_UKS.TYPE, 1, ''YES'', 3, ''YES'', ''NO'') as IS_DOCUMENTED, ' ||

    -- profile results

    'UK_PROPERTIES.UK_ERROR            as UK_ERROR, ' ||
    'UK_PROPERTIES.UK_PARTITION        as UK_PARTITION, ' ||

    'UK_QUERIES.COMPLIANT_QUERY        as COMPLIANT_QUERY, ' ||
    'UK_QUERIES.COMPLIANT_CNT_QUERY    as COMPLIANT_CNT_QUERY, ' ||
    'UK_QUERIES.NONCOMPLIANT_QUERY     as NONCOMPLIANT_QUERY, ' ||
    'UK_QUERIES.NONCOMPLIANT_CNT_QUERY as NONCOMPLIANT_CNT_QUERY, ' ||
    'UK_QUERIES.DRILLDOWN_QUERY        as DRILLDOWN_QUERY, ' ||


    'JOINED_PROFILE_UKS.UPDATED_ON     as UPDATED_ON, ' ||
    'JOINED_PROFILE_UKS.CREATED_ON     as CREATED_ON, ' ||
    'JOINED_PROFILE_UKS.UPDATED_BY     as UPDATED_BY, ' ||
    'JOINED_PROFILE_UKS.CREATED_BY     as CREATED_BY ' ||


  'FROM ' ||
    '( ' ||
    'SELECT ' ||
      'PROFILE.ELEMENTID      as  PROFILE_ID, ' ||
      'PROFILE.NAME           as  PROFILE_NAME, ' ||
      'TAB.REFERENCEDRELATION as  REFERENCED_RELATION, ' ||
      'UK.UPDATETIMESTAMP     as  UPDATED_ON, ' ||
      'UK.CREATIONTIMESTAMP   as  CREATED_ON, ' ||
      'UK.UPDATEDBY           as  UPDATED_BY, ' ||
      'UK.CREATEDBY           as  CREATED_BY, ' ||
      'UK.NAME                as  NAME, '       ||
      'UK.LOGICALNAME         as  LOGICALNAME, '||
      'UK.TYPE                as  TYPE, '       ||
      'UK.ELEMENTID           as  ELEMENTID   ' ||

    'FROM ' ||
      'CMPPROFILE_V             PROFILE, ' ||
      'CMPPROFILETABLE_V        TAB, ' ||
      'CMPPROFILEUNIQUEKEY_V    UK ' ||

    'WHERE ' ||
      'UK.OWNINGRELATION = TAB.ELEMENTID AND ' ||
      'TAB.OWNINGFOLDER  = PROFILE.ELEMENTID ' ||
    ') ' ||
    '                         JOINED_PROFILE_UKS,  ' ||
    'CMPRELATION_V            REF_RELATION, ' ||

    'TABLE(DATA_PROFILE_UTL.GET_PROFILE_UK_PROPERTIES) UK_PROPERTIES, ' ||
    'TABLE(DATA_PROFILE_UTL.GET_PROFILE_UK_QUERIES)    UK_QUERIES ' ||

  'WHERE ' ||

    'JOINED_PROFILE_UKS.REFERENCED_RELATION = REF_RELATION.ELEMENTID(+) AND ' ||
    'JOINED_PROFILE_UKS.ELEMENTID           = UK_PROPERTIES.PROPERTYOWNER(+) AND ' ||
    'JOINED_PROFILE_UKS.ELEMENTID           = UK_QUERIES.PROPERTYOWNER(+) ' ||

'WITH READ ONLY'
;

-- created for Paris: By Ben
execute immediate
'CREATE OR REPLACE VIEW WB$_IV_PROFILE_FOREIGN_KEYS AS ' ||

  'SELECT ' || 
    
    'JOINED_PROFILE_FKS.PROFILE_ID     as PROFILE_ID, ' ||
    'JOINED_PROFILE_FKS.PROFILE_NAME   as PROFILE_NAME, ' ||
    'REF_RELATION.ELEMENTID            as ENTITY_ID, ' ||
    'REF_RELATION.NAME                 as ENTITY_NAME, ' ||
    'JOINED_PROFILE_FKS.ELEMENTID      as FOREIGN_KEY_ID, ' ||
    'JOINED_PROFILE_FKS.NAME           as FOREIGN_KEY_NAME, ' ||
    'JOINED_PROFILE_FKS.LOGICALNAME    as BUSINESS_NAME, ' ||
    'JOINED_PROFILE_FKS.UNIQUEKEY      as UNIQUE_KEY_ID, ' ||

    'DECODE(JOINED_PROFILE_FKS.TYPE, 2, ''YES'', 3, ''YES'', ''NO'') as IS_DISCOVERED, ' ||
    'DECODE(JOINED_PROFILE_FKS.TYPE, 1, ''YES'', 3, ''YES'', ''NO'') as IS_DOCUMENTED, ' ||

    -- profile results

    'FK_PROPERTIES.LOCAL_MAX_CARDINALITY  as LOCAL_MAX_CARDINALITY, ' ||
    'FK_PROPERTIES.LOCAL_MIN_CARDINALITY  as LOCAL_MIN_CARDINALITY, ' ||
    'FK_PROPERTIES.REMOTE_MAX_CARDINALITY as REMOTE_MAX_CARDINALITY, ' ||
    'FK_PROPERTIES.REMOTE_MIN_CARDINALITY as REMOTE_MIN_CARDINALITY, ' ||
    'FK_PROPERTIES.FK_ERROR               as NUM_ORPHANS, ' ||

    'FK_QUERIES.COMPLIANT_QUERY        as COMPLIANT_QUERY, ' ||
    'FK_QUERIES.COMPLIANT_CNT_QUERY    as COMPLIANT_CNT_QUERY, ' ||
    'FK_QUERIES.NONCOMPLIANT_QUERY     as NONCOMPLIANT_QUERY, ' ||
    'FK_QUERIES.NONCOMPLIANT_CNT_QUERY as NONCOMPLIANT_CNT_QUERY, ' ||
    'FK_QUERIES.DRILLDOWN_QUERY        as DRILLDOWN_QUERY, ' ||
    'FK_QUERIES.CHILDLESS_QUERY        as CHILDLESS_QUERY, ' ||


    'JOINED_PROFILE_FKS.UPDATED_ON     as UPDATED_ON, ' ||
    'JOINED_PROFILE_FKS.CREATED_ON     as CREATED_ON, ' ||
    'JOINED_PROFILE_FKS.UPDATED_BY     as UPDATED_BY, ' ||
    'JOINED_PROFILE_FKS.CREATED_BY     as CREATED_BY ' ||


  'FROM ' ||
    '( ' ||
    'SELECT ' ||
      'PROFILE.ELEMENTID      as  PROFILE_ID, ' ||
      'PROFILE.NAME           as  PROFILE_NAME, ' ||
      'TAB.REFERENCEDRELATION as  REFERENCED_RELATION, ' ||
      'FK.UPDATETIMESTAMP     as  UPDATED_ON, ' ||
      'FK.CREATIONTIMESTAMP   as  CREATED_ON, ' ||
      'FK.UPDATEDBY           as  UPDATED_BY, ' ||
      'FK.CREATEDBY           as  CREATED_BY, ' ||
      'FK.NAME                as  NAME, '       ||
      'FK.LOGICALNAME         as  LOGICALNAME, '||
      'FK.TYPE                as  TYPE, '       ||
      'FK.UNIQUEKEY           as  UNIQUEKEY, '  ||
      'FK.ELEMENTID           as  ELEMENTID   ' ||

    'FROM ' ||
      'CMPPROFILE_V             PROFILE, ' ||
      'CMPPROFILETABLE_V        TAB, ' ||
      'CMPPROFILEFOREIGNKEY_V    FK ' ||

    'WHERE ' ||
      'FK.OWNINGRELATION = TAB.ELEMENTID AND ' ||
      'TAB.OWNINGFOLDER  = PROFILE.ELEMENTID ' ||
    ') ' ||
    '                         JOINED_PROFILE_FKS,  ' ||
    'CMPRELATION_V            REF_RELATION, ' ||

    'TABLE(DATA_PROFILE_UTL.GET_PROFILE_FK_PROPERTIES) FK_PROPERTIES, ' ||
    'TABLE(DATA_PROFILE_UTL.GET_PROFILE_FK_QUERIES)    FK_QUERIES ' ||

  'WHERE ' ||

    'JOINED_PROFILE_FKS.REFERENCED_RELATION = REF_RELATION.ELEMENTID(+) AND ' ||
    'JOINED_PROFILE_FKS.ELEMENTID           = FK_PROPERTIES.PROPERTYOWNER(+) AND ' ||
    'JOINED_PROFILE_FKS.ELEMENTID           = FK_QUERIES.PROPERTYOWNER(+) ' ||

'WITH READ ONLY'
;

-- created for Paris: By Ben
execute immediate
'CREATE OR REPLACE VIEW WB$_IV_FUNCTIONAL_DEPENDENCIES AS ' ||

  'SELECT ' || 
    
    'JOINED_PROFILE_FDS.PROFILE_ID     as PROFILE_ID, ' ||
    'JOINED_PROFILE_FDS.PROFILE_NAME   as PROFILE_NAME, ' ||
    'REF_RELATION.ELEMENTID            as ENTITY_ID, ' ||
    'REF_RELATION.NAME                 as ENTITY_NAME, ' ||
    'JOINED_PROFILE_FDS.ELEMENTID      as FUNCTIONAL_DEPENDENCY_ID, ' ||
    'JOINED_PROFILE_FDS.NAME           as FUNCTIONAL_DEPENDENCY_NAME, ' ||
    'JOINED_PROFILE_FDS.LOGICALNAME    as BUSINESS_NAME, ' ||
    'DECODE(JOINED_PROFILE_FDS.TYPE, ''0'', ''uni-directional'', ''bi-directional'') as TYPE, ' ||
    'JOINED_PROFILE_FDS.RHSATTRIBUTE   as DEPENDENT_COLUMN_ID, ' ||

    -- profile results

    'FD_PROPERTIES.FD_ERROR  as FD_ERROR, ' ||

    'FD_QUERIES.COMPLIANT_QUERY        as COMPLIANT_QUERY, ' ||
    'FD_QUERIES.COMPLIANT_CNT_QUERY    as COMPLIANT_CNT_QUERY, ' ||
    'FD_QUERIES.NONCOMPLIANT_QUERY     as NONCOMPLIANT_QUERY, ' ||
    'FD_QUERIES.NONCOMPLIANT_CNT_QUERY as NONCOMPLIANT_CNT_QUERY, ' ||
    'FD_QUERIES.DRILLDOWN_QUERY        as DRILLDOWN_QUERY, ' ||


    'JOINED_PROFILE_FDS.UPDATED_ON     as UPDATED_ON, ' ||
    'JOINED_PROFILE_FDS.CREATED_ON     as CREATED_ON, ' ||
    'JOINED_PROFILE_FDS.UPDATED_BY     as UPDATED_BY, ' ||
    'JOINED_PROFILE_FDS.CREATED_BY     as CREATED_BY ' ||


  'FROM ' ||
    '( ' ||
    'SELECT ' ||
      'PROFILE.ELEMENTID      as  PROFILE_ID, ' ||
      'PROFILE.NAME           as  PROFILE_NAME, ' ||
      'TAB.REFERENCEDRELATION as  REFERENCED_RELATION, ' ||
      'FD.UPDATETIMESTAMP     as  UPDATED_ON, ' ||
      'FD.CREATIONTIMESTAMP   as  CREATED_ON, ' ||
      'FD.UPDATEDBY           as  UPDATED_BY, ' ||
      'FD.CREATEDBY           as  CREATED_BY, ' ||
      'FD.NAME                as  NAME, '       ||
      'FD.LOGICALNAME         as  LOGICALNAME, '||
      'FD.TYPE                as  TYPE, '       ||
      'FD.RHSATTRIBUTE        as  RHSATTRIBUTE,'||
      'FD.ELEMENTID           as  ELEMENTID   ' ||

    'FROM ' ||
      'CMPPROFILE_V               PROFILE, ' ||
      'CMPPROFILETABLE_V          TAB, ' ||
      'CMPFUNCTIONALDEPENDENCY_V  FD ' ||

    'WHERE ' ||
      'FD.OWNINGRELATION = TAB.ELEMENTID AND ' ||
      'TAB.OWNINGFOLDER  = PROFILE.ELEMENTID ' ||
    ') ' ||
    '                         JOINED_PROFILE_FDS,  ' ||
    'CMPRELATION_V            REF_RELATION, ' ||

    'TABLE(DATA_PROFILE_UTL.GET_PROFILE_FD_PROPERTIES) FD_PROPERTIES, ' ||
    'TABLE(DATA_PROFILE_UTL.GET_PROFILE_FD_QUERIES)    FD_QUERIES ' ||

  'WHERE ' ||

    'JOINED_PROFILE_FDS.REFERENCED_RELATION = REF_RELATION.ELEMENTID(+) AND ' ||
    'JOINED_PROFILE_FDS.ELEMENTID           = FD_PROPERTIES.PROPERTYOWNER(+) AND ' ||
    'JOINED_PROFILE_FDS.ELEMENTID           = FD_QUERIES.PROPERTYOWNER(+) ' ||

'WITH READ ONLY'
;

-- created for Paris: By Ben
execute immediate
'CREATE OR REPLACE VIEW WB$_IV_ROW_RELATIONSHIPS AS ' ||

  'SELECT ' || 
    
    'JOINED_PROFILE_RRS.PROFILE_ID     as PROFILE_ID, ' ||
    'JOINED_PROFILE_RRS.PROFILE_NAME   as PROFILE_NAME, ' ||
    'REF_RELATION.ELEMENTID            as ENTITY_ID, ' ||
    'REF_RELATION.NAME                 as ENTITY_NAME, ' ||
    'JOINED_PROFILE_RRS.ELEMENTID      as ROW_RELATIONSHIP_ID, ' ||
    'JOINED_PROFILE_RRS.NAME           as ROW_RELATIONSHIP_NAME, ' ||
    'JOINED_PROFILE_RRS.LOGICALNAME    as BUSINESS_NAME, ' ||
    'JOINED_PROFILE_RRS.TARGETIND      as REMOTE_KEY_ID, ' ||

    '''YES'' as IS_DISCOVERED, ' ||
    '''NO''  as IS_DOCUMENTED, ' ||
    -- profile results

    'RR_PROPERTIES.LOCAL_MAX_CARDINALITY  as LOCAL_MAX_CARDINALITY, ' ||
    'RR_PROPERTIES.LOCAL_MIN_CARDINALITY  as LOCAL_MIN_CARDINALITY, ' ||
    'RR_PROPERTIES.REMOTE_MAX_CARDINALITY as REMOTE_MAX_CARDINALITY, ' ||
    'RR_PROPERTIES.REMOTE_MIN_CARDINALITY as REMOTE_MIN_CARDINALITY, ' ||
    'RR_PROPERTIES.IND_ERROR              as NUM_ORPHANS, ' ||

    'RR_QUERIES.COMPLIANT_QUERY        as COMPLIANT_QUERY, ' ||
    'RR_QUERIES.COMPLIANT_CNT_QUERY    as COMPLIANT_CNT_QUERY, ' ||
    'RR_QUERIES.NONCOMPLIANT_QUERY     as NONCOMPLIANT_QUERY, ' ||
    'RR_QUERIES.NONCOMPLIANT_CNT_QUERY as NONCOMPLIANT_CNT_QUERY, ' ||
    'RR_QUERIES.DRILLDOWN_QUERY        as DRILLDOWN_QUERY, ' ||


    'JOINED_PROFILE_RRS.UPDATED_ON     as UPDATED_ON, ' ||
    'JOINED_PROFILE_RRS.CREATED_ON     as CREATED_ON, ' ||
    'JOINED_PROFILE_RRS.UPDATED_BY     as UPDATED_BY, ' ||
    'JOINED_PROFILE_RRS.CREATED_BY     as CREATED_BY ' ||


  'FROM ' ||
    '( ' ||
    'SELECT ' ||
      'PROFILE.ELEMENTID      as  PROFILE_ID, ' ||
      'PROFILE.NAME           as  PROFILE_NAME, ' ||
      'TAB.REFERENCEDRELATION as  REFERENCED_RELATION, ' ||
      'RR.UPDATETIMESTAMP     as  UPDATED_ON, ' ||
      'RR.CREATIONTIMESTAMP   as  CREATED_ON, ' ||
      'RR.UPDATEDBY           as  UPDATED_BY, ' ||
      'RR.CREATEDBY           as  CREATED_BY, ' ||
      'RR.NAME                as  NAME, '       ||
      'RR.LOGICALNAME         as  LOGICALNAME, '||
      'RR.TARGETIND           as  TARGETIND, '  ||
      'RR.ELEMENTID           as  ELEMENTID   ' ||

    'FROM ' ||
      'CMPPROFILE_V              PROFILE, ' ||
      'CMPPROFILETABLE_V         TAB, ' ||
      'CMPINCLUSIONDEPENDENCY_V RR ' ||

    'WHERE ' ||
      'RR.OWNINGRELATION = TAB.ELEMENTID AND ' ||
      'TAB.OWNINGFOLDER  = PROFILE.ELEMENTID ' ||
    ') ' ||
    '                         JOINED_PROFILE_RRS,  ' ||
    'CMPRELATION_V            REF_RELATION, ' ||

    'TABLE(DATA_PROFILE_UTL.GET_PROFILE_RR_PROPERTIES) RR_PROPERTIES, ' ||
    'TABLE(DATA_PROFILE_UTL.GET_PROFILE_RR_QUERIES)    RR_QUERIES ' ||

  'WHERE ' ||

    'JOINED_PROFILE_RRS.REFERENCED_RELATION = REF_RELATION.ELEMENTID(+) AND ' ||
    'JOINED_PROFILE_RRS.ELEMENTID           = RR_PROPERTIES.PROPERTYOWNER(+) AND ' ||
    'JOINED_PROFILE_RRS.ELEMENTID           = RR_QUERIES.PROPERTYOWNER(+) ' ||

'WITH READ ONLY'
;

-- created for Paris: By Ben
execute immediate
'CREATE OR REPLACE VIEW WB$_IV_PROFILE_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,  ' ||
  'col.logicalname AS business_name, ' ||
  'setu.sequence   AS position  ' ||

'FROM   ' ||
  
  'cmpitemset_v          key,  ' ||
  'cmpprofileattribute_v col,  ' ||
  'cmpwbitemsetusage_v   setu  ' ||

'WHERE   ' ||

  'key.classname  in (''CMPProfileUniqueKey'', ''CMPProfileForeignKey'', ''CMPFunctionalDependency'', ''CMPInclusionDependency'') AND  ' ||
  'setu.attribute =  col.elementid AND  ' ||
  'key.elementid  =  setu.itemset ' ||

'WITH READ ONLY'
;

execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_PROFILE_DOMAIN_VALUES AS ' ||
       'SELECT ' ||
         'pa.OWNINGRELATION   AS PROFILE_OBJECT_ID, ' ||
         'pa.ELEMENTID        AS PROFILE_COLUMN_ID, ' ||
         'pa.NAME             AS COLUMN_NAME, ' ||
         'string_value.VALUE  AS VALUE, ' ||
         'string_count.VALUE  AS VALUE_COUNT, ' ||
         'DECODE(string_type.VALUE, 1, ''Y'', 0, ''N'', ''N'') AS COMPLIANT ' ||
       'FROM ' ||
         'CMPPROFILEATTRIBUTE_V    pa, ' ||
         '(SELECT * FROM CMPPUREPHYSICALOBJECT_V  WHERE LOGICALNAME = ''8i.PROFILE_ATTRIBUTES.DOMAIN_VALUES'') ppo, ' ||
         '(SELECT * FROM CMPSTRINGPROPERTYVALUE_V WHERE LOGICALNAME = ''8i.DOMAIN_VALUES.DOMAIN_VALUE'') string_value, ' ||
         '(SELECT * FROM CMPSTRINGPROPERTYVALUE_V WHERE LOGICALNAME = ''8i.DOMAIN_VALUES.DOMAIN_TYPE'')  string_type, ' ||
         '(SELECT * FROM CMPSTRINGPROPERTYVALUE_V WHERE LOGICALNAME = ''8i.DOMAIN_VALUES.DOMAIN_COUNT'') string_count ' ||
       'WHERE ' ||
         'pa.ELEMENTID = ppo.PROPERTYOWNER AND ' || 
         'ppo.ELEMENTID = string_value.PROPERTYOWNER AND ' ||
         'ppo.ELEMENTID = string_type.PROPERTYOWNER(+) AND ' ||
         'ppo.ELEMENTID = string_count.PROPERTYOWNER ' ||
     'WITH READ ONLY'
;

  execute immediate 
    'CREATE OR REPLACE VIEW WB$_IV_PROFILE_PATTERN_VALUES AS ' ||
       'SELECT ' ||
         'pa.OWNINGRELATION   AS PROFILE_OBJECT_ID, ' ||
         'pa.ELEMENTID        AS PROFILE_COLUMN_ID, ' ||
         'pa.NAME             AS COLUMN_NAME, ' ||
         'DATA_PROFILE_UTL.FORMAT_PATTERN(string_value.VALUE)  AS VALUE, ' ||
         'string_count.VALUE  AS VALUE_COUNT, ' ||
         'DECODE(ppo.LOGICALNAME, ''8i.PROFILE_ATTRIBUTES.PATTERN_2_VALUES'', ''Word'', ''8i.PROFILE_ATTRIBUTES.PATTERN_1_VALUES'', ''Character'', '''') AS TYPE, ' ||
         'TO_CHAR(clob_com.VALUE)      AS COMPLIANT_QUERY, ' ||
         'TO_CHAR(clob_noncom.VALUE)   AS NONCOMPLIANT_QUERY, ' ||
         'TO_CHAR(clob_drill.VALUE)    AS DRILLDOWN_QUERY ' ||
       'FROM ' ||
         'CMPPROFILEATTRIBUTE_V    pa, ' ||
         '(SELECT * FROM CMPPUREPHYSICALOBJECT_V  WHERE LOGICALNAME IN (''8i.PROFILE_ATTRIBUTES.PATTERN_1_VALUES'', ''8i.PROFILE_ATTRIBUTES.PATTERN_2_VALUES'')) ppo, ' ||
         '(SELECT * FROM CMPCLOBPROPERTYVALUE_V   WHERE LOGICALNAME = ''8i.DOMAIN_VALUES.COMPLIANT_QUERY'') clob_com, ' ||
         '(SELECT * FROM CMPCLOBPROPERTYVALUE_V   WHERE LOGICALNAME = ''8i.DOMAIN_VALUES.NONCOMPLIANT_QUERY'') clob_noncom, ' ||
         '(SELECT * FROM CMPCLOBPROPERTYVALUE_V   WHERE LOGICALNAME = ''8i.DOMAIN_VALUES.DRILLDOWN_QUERY'') clob_drill, ' ||
         '(SELECT * FROM CMPSTRINGPROPERTYVALUE_V WHERE LOGICALNAME = ''8i.DOMAIN_VALUES.DOMAIN_VALUE'') string_value, ' ||
         '(SELECT * FROM CMPSTRINGPROPERTYVALUE_V WHERE LOGICALNAME = ''8i.DOMAIN_VALUES.DOMAIN_COUNT'') string_count ' ||
       'WHERE ' ||
         'pa.ELEMENTID = ppo.PROPERTYOWNER AND ' || 
         'ppo.ELEMENTID = clob_com.PROPERTYOWNER AND ' ||
         'ppo.ELEMENTID = clob_noncom.PROPERTYOWNER AND ' ||
         'ppo.ELEMENTID = clob_drill.PROPERTYOWNER AND ' ||
         'ppo.ELEMENTID = string_value.PROPERTYOWNER AND ' ||
         'ppo.ELEMENTID = string_count.PROPERTYOWNER ' ||
     'WITH READ ONLY'
;

  execute immediate
   'CREATE OR REPLACE VIEW WB$_IV_PROFILE_RULES AS ' ||
      'SELECT ' ||
        'ru.OWNINGRELATION  AS PROFILE_OBJECT_ID, ' ||
        'ru.ELEMENTID       AS DATA_RULE_USAGE_ID, ' ||
        'ru.NAME            AS DATA_RULE_USAGE_NAME, ' ||
        'string_count.VALUE AS COMPLIANT_ROW_COUNT ' ||
      'FROM ' ||
        'CMPBUSINESSRULEUSAGE_V   ru, ' ||
        '(SELECT * FROM CMPSTRINGPROPERTYVALUE_V WHERE LOGICALNAME = ''DATA_RULE_USAGE.COMPLIANT_COUNT'') string_count ' ||
      'WHERE ' ||
        'ru.ELEMENTID = string_count.PROPERTYOWNER ' ||
      'WITH READ ONLY'
;

   --Created for Paris: by Helen
   execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_PF_CORRECTED_MODULES AS ' ||
       'SELECT ' ||
         'pf.elementid AS profile_id, '||
         'pf.name AS profile_name, '||
         'ds.CORRECTEDMODULE AS corrected_module_id, '||
         'm.name AS corrected_module_name, '||
         'ds.logicalname AS business_name, ' ||
         'ds.description AS description, ' ||
         'ds.updatetimestamp AS updated_on, ' ||
         'ds.creationtimestamp AS created_on, ' ||
         'ds.updatedby AS updated_by, '||
         'ds.createdby AS created_by '||
      'FROM ' ||
         'cmpprofile_v pf, '||
         'cmpderivationschema_v ds, '||
         'CMPDatabaseModule_v m ' ||
      'WHERE ' ||
         'pf.elementid = ds.OWNINGPROFILE AND '||
         'ds.CORRECTEDMODULE = m.elementid  '||
     'WITH READ ONLY';

----------------------------------------------------------------

   --Created for Paris: by Helen
   execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_TM_MODULES AS ' ||
       'SELECT ' ||
         'proj.elementid AS project_id, '||
         'proj.name AS project_name, '||
         'cm.elementid AS tm_id, '||
         'cm.name AS tm_name, '||
         'cm.logicalname AS business_name, ' ||
         'cm.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'cm.updatetimestamp AS updated_on, ' ||
         'cm.creationtimestamp AS created_on, ' ||
         'cm.updatedby AS updated_by, '||
         'cm.createdby AS created_by '||
      'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwbproject_v proj, '||
         'cmpwboraclettsset_v cm '||
      'WHERE ' ||
         'cm.OWNINGPROJECT = proj.elementid AND '||
         'cm.validationresult = v.elementid(+) ' ||
     'WITH READ ONLY';


   --Created for Paris: by Helen
   execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_TM_TABLESPACES AS ' ||
        'SELECT ' ||
         'mod.elementid AS tm_id, ' ||
         'mod.name AS tm_name, ' ||
         'tb.elementid AS tm_tablespace_id, ' ||
         'tb.name AS tm_tablespace_name, ' ||
         'tb.logicalname AS business_name, ' ||
         'SUBSTR(SUBSTR(SUBSTR(SUBSTR(tb.logicalname,INSTR(tb.logicalname,'':'')+1 ), INSTR(SUBSTR(tb.logicalname,INSTR(tb.logicalname,'':'')+1 ),'':'')+1 ) ' ||
           ' ,INSTR(SUBSTR(SUBSTR(tb.logicalname,INSTR(tb.logicalname,'':'')+1 ), INSTR(SUBSTR(tb.logicalname,INSTR(tb.logicalname,'':'')+1 ),'':'')+1 )   ' ||
           ' ,'':'')+1 ),INSTR(SUBSTR(SUBSTR(SUBSTR(tb.logicalname,INSTR(tb.logicalname,'':'')+1 ), INSTR(SUBSTR(tb.logicalname,INSTR(tb.logicalname,'':'')+1 ),'':'')+1 ) ' ||
           ' ,INSTR(SUBSTR(SUBSTR(tb.logicalname,INSTR(tb.logicalname,'':'')+1 ), INSTR(SUBSTR(tb.logicalname,INSTR(tb.logicalname,'':'')+1 ),'':'')+1 )  ' ||
           ' ,'':'')+1 ),'':'')+1 ) AS tm_tablespace_ui_name, ' ||
         'tb.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'tb.updatetimestamp AS updated_on, ' ||
         'tb.creationtimestamp AS created_on, ' ||
         'tb.updatedby AS updated_by, '||
         'tb.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwboraclettsset_v mod, ' ||
         'cmpbusinesstreeshortcut_v tree, ' ||
         'cmpwboracletts_v tb ' ||
        'WHERE ' ||
         'mod.elementid = tree.CATALOG AND ' ||
         'tb.elementid = tree.FCO AND ' ||
         'tb.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';  


   --Created for Paris: by Helen
   execute immediate
    'CREATE OR REPLACE VIEW WB$_IV_TM_SCHEMAS AS ' ||
        'SELECT ' ||
         'tb.elementid AS tm_tablespace_id, ' ||
         'tb.name AS tm_tablespace_name, ' ||
         'im.elementid AS tm_schema_id, ' ||
         'im.name AS tm_schema_name, ' ||
         'tbv.tm_tablespace_ui_name||''_U'' AS tm_schema_ui_name, ' ||
         'im.logicalname AS business_name, ' ||
         'im.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'im.updatetimestamp AS updated_on, ' ||
         'im.creationtimestamp AS created_on, ' ||
         'im.updatedby AS updated_by, ' ||
         'im.createdby AS created_by ' ||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpwboracletts_v tb, ' ||
         'cmpbusinesstreeshortcut_v tree, ' ||
         'cmpDatabaseModule_v im, ' ||
         'WB$_IV_TM_TABLESPACES tbv '||
        'WHERE ' ||
         'tb.elementid = tree.CATALOG AND ' ||
         'im.elementid = tree.FCO AND ' ||
         'tbv.tm_tablespace_id = tb.elementid AND ' ||
         'im.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';  


    execute immediate 
     'CREATE OR REPLACE VIEW WB$_IV_OBJECT_TYPES AS ' ||
        'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'theObjectType.elementid AS object_type_id, ' ||
         'theObjectType.name AS object_type_name, ' ||
         'theObjectType.logicalname AS business_name, ' ||
         'theObjectType.seeded AS is_seeded, ' ||
         'theObjectType.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'theObjectType.updatetimestamp AS updated_on, ' ||
         'theObjectType.creationtimestamp AS created_on, ' ||
	 'theObjectType.updatedby AS updated_by, '||
	 'theObjectType.createdby AS created_by '||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmpobjecttype_v theObjectType ' ||
        'WHERE ' ||
         'schema.elementid = theObjectType.installedmodule AND ' ||
         'theObjectType.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';
 
    execute immediate 
     'CREATE OR REPLACE VIEW WB$_IV_VARRAYS AS ' ||
         'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'theVarray.elementid AS varray_id, ' ||
         'theVarray.name AS varray_name, ' ||
         'theVarray.logicalname AS business_name, ' ||
         'theVarray.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'dt.data_type AS base_element_name, ' ||
         'decode(instr(dt.data_type, ''.''), ''0'', theVarray.precision, ''0'') AS base_element_precision, ' ||
         'decode(instr(dt.data_type, ''.''), ''0'', theVarray.scale, ''0'') AS base_element_scale, ' ||
         'decode(instr(dt.data_type, ''.''), ''0'', theVarray.length, ''0'') AS base_element_length, ' ||
         'theVarray.arraylength AS array_length, ' ||
         'theVarray.updatetimestamp AS updated_on, ' ||
         'theVarray.creationtimestamp AS created_on, ' ||
         'theVarray.updatedby AS updated_by, '||
         'theVarray.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, ' ||  
         'cmpwbvalidationresult_v v, ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmpsqlcollection_v theVarray ' ||
         'WHERE ' ||
         'schema.elementid = theVarray.owningmodule AND ' ||
         'theVarray.strongtypename like ''%Varray'' AND ' ||
         'dt.type_id = theVarray.typeDefinition AND ' ||
         'theVarray.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

    execute immediate 
     'CREATE OR REPLACE VIEW WB$_IV_NESTED_TABLES AS ' ||
         'SELECT ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'theNestedTable.elementid AS Nested_Table_id, ' ||
         'theNestedTable.name AS Nested_Table_name, ' ||
         'theNestedTable.logicalname AS business_name, ' ||
         'theNestedTable.description AS description, ' ||
         'v.valid AS valid, ' ||
         'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
         'dt.data_type AS base_element_name, ' ||
         'decode(instr(dt.data_type, ''.''), ''0'', theNestedTable.precision, ''0'') AS base_element_precision, ' ||
         'decode(instr(dt.data_type, ''.''), ''0'', theNestedTable.scale, ''0'') AS base_element_scale, ' ||
         'decode(instr(dt.data_type, ''.''), ''0'', theNestedTable.length, ''0'') AS base_element_length, ' ||
         'theNestedTable.updatetimestamp AS updated_on, ' ||
         'theNestedTable.creationtimestamp AS created_on, ' ||
         'theNestedTable.updatedby AS updated_by, '||
         'theNestedTable.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, ' ||  
         'cmpwbvalidationresult_v v, ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmpsqlcollection_v theNestedTable ' ||
         'WHERE ' ||
         'schema.elementid = theNestedTable.owningmodule AND ' ||
         'theNestedTable.strongtypename like ''%NestedTable'' AND ' ||
         'dt.type_id = theNestedTable.typeDefinition AND ' ||
         'theNestedTable.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';         
--------------------------------------
-- added by ramu
   execute immediate 
   	  'CREATE OR REPLACE VIEW WB$_IV_STREAMS_QUEUES AS ' ||
        'SELECT ' ||
        'schema.elementid AS schema_id, '||
        'schema.name AS schema_name, '||
        'queue.elementid AS queue_id, ' ||
        'queue.name AS queue_name, ' ||
        'queue.logicalname AS business_name, ' ||
        'queue.description AS description, ' ||
        'decode(queuetable.name, null, ''Not Specified'', queuetable.name) AS queue_table, ' ||
        'queuetable.elementid AS queue_table_id, ' ||
        '''SYS.ANYDATA'' AS payload_type, ' ||
         'v.valid AS valid, ' ||
        'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
        'queue.updatetimestamp AS updated_on, ' ||
        'queue.creationtimestamp AS created_on, ' ||
 	'queue.updatedby AS updated_by, '||
	'queue.createdby AS created_by '||
        'FROM ' ||
        'cmpadvancedqueue_v queue, ' ||
        'cmpinstalledmodule_v schema, '||
        'cmpwbvalidationresult_v v, ' ||
        'cmpqueuetable_v queuetable ' ||
        'WHERE ' ||
        'queue.validationresult = v.elementid(+) ' ||
        'and schema.elementid = queue.installedmodule '||
        'and queue.queuetable = queuetable.elementid(+) ' ||
	'and queue.strongtypename = ''oracle.wh.repos.impl.queue.CMPAnydataQueue'' ' ||
        'WITH READ ONLY';

   execute immediate 
   	'CREATE OR REPLACE VIEW WB$_IV_ADVANCED_QUEUES AS ' ||
        'SELECT ' ||
        'schema.elementid AS schema_id, '||
        'schema.name AS schema_name, '||
        'queue.elementid AS queue_id, ' ||
        'queue.name AS queue_name, ' ||
        'queue.logicalname AS business_name, ' ||
        'queue.description AS description, ' ||
        'decode(queuetable.name, null, ''Not Specified'', queuetable.name) AS queue_table, ' ||
        'queuetable.elementid AS queue_table_id, ' ||
        'decode(dt.data_type, null, ''Not Specified'', dt.data_type) AS payload_type, ' ||
        'dt.type_id AS payload_type_id, ' ||
         'v.valid AS valid, ' ||
        'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
        'queue.updatetimestamp AS updated_on, ' ||
        'queue.creationtimestamp AS created_on, ' ||
 	'queue.updatedby AS updated_by, '||
	'queue.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, ' ||  
        'cmpadvancedqueue_v queue, ' ||
        'cmpinstalledmodule_v schema, '||
        'cmpwbvalidationresult_v v, ' ||
        'cmprelationaldbitem_v qtabcol, ' ||
        'cmpqueuetable_v queuetable ' ||
        'WHERE ' ||
        'queue.validationresult = v.elementid(+) ' ||
        'and schema.elementid = queue.installedmodule '||
        'and queue.queuetable = queuetable.elementid(+) ' ||
        'and queuetable.elementid = qtabcol.owningrelation(+) ' ||
        'and qtabcol.typedefinition = dt.type_id(+) ' ||
	'and queue.strongtypename = ''oracle.wh.repos.impl.queue.CMPTypedQueue'' ' ||
        'WITH READ ONLY';

   execute immediate 
   	  'CREATE OR REPLACE VIEW WB$_IV_QUEUES AS ' ||
        'SELECT ' ||
        'schema.elementid AS schema_id, '||
        'schema.name AS schema_name, '||
        'queue.elementid AS queue_id, ' ||
        'queue.name AS queue_name, ' ||
        'queue.logicalname AS business_name, ' ||
        'queue.description AS description, ' ||
        'decode(queuetable.name, null, ''Not Specified'', queuetable.name) AS queue_table, ' ||
        'queuetable.elementid AS queue_table_id, ' ||
        '''SYS.ANYDATA'' AS payload_type, ' ||
        'null AS payload_type_id, ' ||
         'v.valid AS valid, ' ||
        'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
        'queue.updatetimestamp AS updated_on, ' ||
        'queue.creationtimestamp AS created_on, ' ||
 	'queue.updatedby AS updated_by, '||
	'queue.createdby AS created_by '||
        'FROM ' ||
        'cmpadvancedqueue_v queue, ' ||
        'cmpinstalledmodule_v schema, '||
        'cmpwbvalidationresult_v v, ' ||
        'cmpqueuetable_v queuetable ' ||
        'WHERE ' ||
        'queue.validationresult = v.elementid(+) ' ||
        'and queue.installedmodule = schema.elementid '||
        'and queue.queuetable = queuetable.elementid(+) ' ||
	'and queue.strongtypename = ''oracle.wh.repos.impl.queue.CMPAnydataQueue'' ' ||
        'UNION ' ||
        'SELECT ' ||
        'schema.elementid AS schema_id, '||
        'schema.name AS schema_name, '||
        'queue.elementid AS queue_id, ' ||
        'queue.name AS queue_name, ' ||
        'queue.logicalname AS business_name, ' ||
        'queue.description AS description, ' ||
        'decode(queuetable.name, null, ''Not Specified'', queuetable.name) AS queue_table, ' ||
        'queuetable.elementid AS queue_table_id, ' ||
        'decode(payload.name, null, ''Not Specified'', payload.name) AS payload_type, ' ||
        'payload.elementid AS payload_type_id, ' ||
         'v.valid AS valid, ' ||
        'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
        'queue.updatetimestamp AS updated_on, ' ||
        'queue.creationtimestamp AS created_on, ' ||
 	'queue.updatedby AS updated_by, '||
	'queue.createdby AS created_by '||
        'FROM ' ||
        'cmpadvancedqueue_v queue, ' ||
        'cmpinstalledmodule_v schema, '||
        'cmpwbvalidationresult_v v, ' ||
        'cmprelationaldbitem_v qtabcol, ' ||
        'cmptypedefinition_v payload, ' ||
        'cmpqueuetable_v queuetable ' ||
        'WHERE ' ||
        'queue.validationresult = v.elementid(+) ' ||
        'and queue.installedmodule = schema.elementid '||
        'and queue.queuetable = queuetable.elementid(+) ' ||
        'and queuetable.elementid = qtabcol.owningrelation(+) ' ||
        'and qtabcol.typedefinition = payload.elementid(+) ' ||
	'and queue.strongtypename = ''oracle.wh.repos.impl.queue.CMPTypedQueue'' ' ||
        'WITH READ ONLY';

   execute immediate 
   	  'CREATE OR REPLACE VIEW WB$_IV_QUEUE_TABLES AS ' ||
        'SELECT ' ||
        'schema.elementid AS schema_id, '||
        'schema.name AS schema_name, '||
        'queuetable.elementid AS queuetable_id, ' ||
        'queuetable.name AS queuetable_name, ' ||
        'queuetable.logicalname AS business_name, ' ||
        'queuetable.description AS description, ' ||
        'decode(dt.data_type, null, ''Not Specified'', dt.data_type) AS payload_type, ' ||
        'dt.type_id AS payload_type_id, ' ||
         'v.valid AS valid, ' ||
        'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
        'queuetable.updatetimestamp AS updated_on, ' ||
        'queuetable.creationtimestamp AS created_on, ' ||
 	'queuetable.updatedby AS updated_by, '||
	'queuetable.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, ' ||  
        'cmpqueuetable_v queuetable, ' ||
        'cmpinstalledmodule_v schema, '||
        'cmprelationaldbitem_v qtabcol, ' ||
        'cmpwbvalidationresult_v v ' ||
        'WHERE ' ||
        'queuetable.validationresult = v.elementid(+) ' ||
        'and queuetable.installedmodule = schema.elementid '||
        'and queuetable.elementid = qtabcol.owningrelation(+) ' ||
        'and qtabcol.typedefinition = dt.type_id(+) ' ||
        'WITH READ ONLY';

   execute immediate 
   	  'CREATE OR REPLACE VIEW WB$_IV_QUEUE_PROPAGATIONS AS ' ||
        'SELECT ' ||
        'queueprop.elementid AS queuepropagation_id, ' ||
        'queueprop.name AS queuetable_name, ' ||
        'queueprop.logicalname AS business_name, ' ||
        'queueprop.description AS description, ' ||
        'srcqueue.name AS source_queue, ' ||
        'srcqueue.elementid AS source_queue_id, ' ||
        'decode(tgtqueue.name, null, ''Not Specified'', tgtqueue.name) AS target_queue, ' ||
        'tgtqueue.elementid AS target_queue_id, ' ||
         'v.valid AS valid, ' ||
        'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
        'queueprop.updatetimestamp AS updated_on, ' ||
        'queueprop.creationtimestamp AS created_on, ' ||
 	'queueprop.updatedby AS updated_by, '||
	'queueprop.createdby AS created_by '||
        'FROM ' ||
        'cmpqueuepropagation_v queueprop, ' ||
        'cmpadvancedqueue_v srcqueue, ' ||
        'cmpadvancedqueue_v tgtqueue, ' ||
        'cmpwbvalidationresult_v v ' ||
        'WHERE ' ||
        'queueprop.validationresult = v.elementid(+) ' ||
        'and queueprop.owningfolder = srcqueue.elementid ' ||
        'and queueprop.targetqueue = tgtqueue.elementid(+) ' ||
        'WITH READ ONLY';

--   execute immediate 
--   	  'CREATE OR REPLACE VIEW WB$_IV_STREAMS_CAPTURE AS ' ||
--        'SELECT ' ||
--        'strmcap.elementid AS streamscapture_id, ' ||
--        'strmcap.name AS streamscapture_name, ' ||
--        'strmcap.logicalname AS business_name, ' ||
--        'strmcap.description AS description, ' ||
--        'srcqueue.name AS streams_queue, ' ||
--        'srcqueue.elementid AS streams_queue_id, ' ||
--        'decode(v.valid, 1, ''Y'', 0, ''N'', ''Not Validated'') AS is_valid, ' ||
--        'strmcap.updatetimestamp AS updated_on, ' ||
--        'strmcap.creationtimestamp AS created_on, ' ||
-- 	'strmcap.updatedby AS updated_by, '||
--	'strmcap.createdby AS created_by '||
--        'FROM ' ||
--        'cmpstreamscapture_v strmcap, ' ||
--        'cmpadvancedqueue_v srcqueue, ' ||
--        'cmpwbvalidationresult_v v ' ||
--        'WHERE ' ||
--        'strmcap.validationresult = v.elementid(+) ' ||
--        'and strmcap.capturequeue = srcqueue.elementid ' ||
--        'WITH READ ONLY';

--   execute immediate
--          'CREATE OR REPLACE VIEW WB$_IV_CAPTURE_RELATIONS AS ' ||
--       'SELECT ' ||
--        'capture.elementid AS streams_capture_id, ' ||
--        'capture.name AS streams_capture_name, ' ||
--        'rel.elementid AS table_id, ' ||
--        'rel.name AS table_name, ' ||
--        'caprel.elementid AS capturerelation_id, ' ||
--        'caprel.name AS capturerealtion_name, ' ||
--        'caprel.logicalname AS business_name, ' ||
--        'caprel.description AS description, ' ||
--        'caprel.updatetimestamp AS updated_on, ' ||
--        'caprel.creationtimestamp AS created_on, ' ||
--        'caprel.updatedby AS updated_by, '||
--        'caprel.createdby AS created_by '||
--       'FROM  ' ||
--        'cmpstreamscapture_v capture, ' ||
--       'cmpcapturerelation_v caprel, ' ||
--        'cmprelation_v rel ' ||
--       'WHERE ' ||
--        'caprel.firstclassobject = capture.elementid and ' ||
--        'caprel.relation = rel.elementid ' ||
--        'WITH READ ONLY';     

   execute immediate
       'CREATE OR REPLACE VIEW WB$_IV_CMIV_DEFINITIONS AS ' ||
       'SELECT ' ||
       'a.elementid AS cmiv_id, ' ||
       'a.name AS name, ' ||
       'a.logicalname AS business_name, ' ||
       'a.mivmode as mivmode,' ||
       'a.updatetimestamp AS updated_on, ' ||
       'a.creationtimestamp AS created_on, ' ||
       'a.updatedby AS updated_by, ' ||
       'a.createdby AS created_by ' ||
       'FROM ' ||
       'cmpwbmivdefinition_v a ' ||
       'WITH READ ONLY';

   execute immediate
      'CREATE OR REPLACE VIEW WB$_IV_CMIV_VIEWS AS ' ||
      'SELECT ' ||
      'a.mivdefinition as miv_definition_id, ' ||
      'b.name as miv_definition, ' ||
      'a.elementid as miv_view_id, ' ||
      'a.name as miv_view, ' ||
      'a.logicalname as business_name,  ' ||
      'a.viewtype as view_type, ' ||
      'decode(a.isdefault, 1, ''Y'', ''N'') as is_default, ' ||
      'a.updatetimestamp as updated_on,  ' ||
      'a.creationtimestamp as created_on, ' ||
      'a.updatedby as update_by, ' ||
      'a.createdby as created_by  ' ||
      'FROM  ' ||
      'cmpwbmivview_v a, ' ||
      'cmpwbmivdefinition_v b ' ||
      'WHERE  ' ||
      'b.elementid = a.mivdefinition ' ||
      'WITH READ ONLY';

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

  execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_SCHEMAS AS ' ||
        'SELECT ' ||
         'project.elementid AS project_id, ' ||
         'project.name AS project_name, ' ||
         'schema.elementid AS schema_id, ' ||
         'schema.name AS schema_name, ' ||
         'schema.logicalname AS business_name, ' ||
         'schema.description AS description, ' ||
         'schema.elementid AS information_system_id, ' ||
         'schema.name AS information_system_name, ' ||
         '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, ' ||
         'schema.updatetimestamp AS updated_on, ' ||
         'schema.creationtimestamp AS created_on ' ||
        'FROM ' ||
         'cmpwbvalidationresult_v v, ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmpplatform_v plat, ' ||
         'cmpwbproject_v project ' ||
        'WHERE ' ||
         'schema.owningproject = project.elementid AND ' ||
         'schema.platform = plat.elementid AND ' ||
         'schema.validationresult = v.elementid(+) ' ||
         'WITH READ ONLY';

--sochen added user/role etc objects for security feature
--WBA stands for warehouse builder admin
   execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_USERS AS ' ||
        'SELECT ' || 
        'wbuser.elementid AS user_id, ' ||
        'wbuser.name AS user_name, ' ||
        'wbuser.logicalname AS business_name, ' ||
        'wbuser.description AS description, ' ||
        'wbuser.updatetimestamp AS updated_on, ' ||
        'wbuser.creationtimestamp AS created_on, ' ||
 	'wbuser.updatedby AS updated_by, ' ||
	'wbuser.createdby AS created_by ' ||
        'FROM ' ||
        'cmpwbuser_v wbuser ' ||
        'WITH READ ONLY';

   execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_ROLES AS ' ||
        'SELECT ' || 
        'wbrole.elementid AS role_id, ' ||
        'wbrole.name AS role_name, ' ||
        'wbrole.logicalname AS business_name, ' ||
        'wbrole.description AS description, ' ||
        'wbrole.updatetimestamp AS updated_on, ' ||
        'wbrole.creationtimestamp AS created_on, ' ||
 	'wbrole.updatedby AS updated_by, ' ||
	'wbrole.createdby AS created_by ' ||
        'FROM ' ||
        'cmpwbrole_v wbrole ' ||
        'WITH READ ONLY';
 
--all role assignment
 execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_ROLE_PRIVS AS ' ||
        'SELECT ' || 
 	'wbusers.name as grantee, ' ||
	'wbroles.name as granted_role ' ||
        'FROM ' ||
	'cmpwbuser_v wbusers, '||
        'cmpwbrole_v wbroles, ' ||
	'cmpRoleAssignment_v assign ' ||
	'WHERE '||
	'assign.wbuser = wbusers.elementid AND ' ||
	'assign.wbrole = wbroles.elementid  ' ||		
        'WITH READ ONLY';	

--all user/role sys priv assignment
 execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_SYS_PRIVS AS ' ||
        'SELECT ' || 
	'owner.name as grantee, sysPrivs.name as privilege ' ||
	'FROM '||
	'cmpPrivilegeOwner_v owner, '||
	'TABLE(SECURITY_PV_UTILITIES.GetSysPrivNames(owner.SYSPRIVSACCESSCHARMAP)) sysPrivs '||
	'WITH READ ONLY';


--all user/role obj priv assignment
 execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_OBJ_PRIVS AS ' ||
        'SELECT ' || 
	'owner.name as grantee,fco.elementid as object_id, fco.name as  object_name, OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE2(fco.elementid, fco.classname)  as object_type, objPrivs.name as privilege ' ||
	
	'FROM '||
	'cmpprivilegeOwner_v  owner, cmpACLContainer_v con, '||
	'cmpAccessControlList_v list, firstclassobject_v fco, '||
	'TABLE(SECURITY_PV_UTILITIES.GetObjPrivNames(list.ACCESSCHARMAP)) objPrivs '||
	'WHERE '||
           'fco.aclcontainer = con.elementid AND ' ||
	   'con.elementid = list.firstclassobject AND '||
	   'list.privilegeOwner= owner.elementid ' ||
	'WITH READ ONLY';

execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_WORKSPACES AS ' ||
        'SELECT ' || 
	'wksp.elementid as workspace_id,  wksp.name as workspace_name, wksp.logicalname as business_name , wksp.description as description, wbuser.name as workspace_owner, wksp.createdBy as created_by, wksp.CreationTimestamp as created_on , wksp.UpdatedBY as Updated_By, wksp.UpdateTimestamp as Updated_on ' ||	
	'FROM '||
	'cmpworkspace_v wksp, cmpallwbuser_v wbuser '||	
	'WHERE '||
           ' wbuser.workspaceid = wksp.elementid and wbuser.isWorkspaceOwner = ''1'' ' ||
	'WITH READ ONLY';

execute immediate
        'CREATE OR REPLACE VIEW WB$_IV_WORKSPACE_ASSIGNMENTS AS ' ||
        'SELECT ' || 
	'workspace_id as workspace_id,  workspace_name as workspace_name, business_name as workspace_business_name , description as workspace_description, user_name as user_name, isWorkspaceOwner as isWorkspaceOwner, createdBy as user_createdBy, creationTimestamp as user_creationTimestamp, updatedby as user_updatedby,  UpdateTimestamp as user_updateTimestamp ' ||
	'FROM '||
	'workspace_assignment ' ||
	'WITH READ ONLY';

    execute immediate
          'CREATE OR REPLACE VIEW WB$_IV_INDEXES 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, ' ||
         'ind.name AS index_name, ' ||
         'ind.elementid AS index_id, ' ||
         'ind.logicalname AS business_name, ' ||
         'ind.description AS description, ' ||
         'ind.INDEXTYPE AS index_type, ' ||
         'decode(ind.LOCALINDEX, ''1'', ''Y'', ''N'') AS local_index, ' ||
         'upper(pk.PARTITIONTYPE) AS index_PARTITIONING_TYPE, ' ||
         'pk.HASHPARTITIONQUANTITY AS HASH_PARTITION_QUANTITY, ' ||
         'ind.updatetimestamp AS updated_on, ' ||
         'ind.creationtimestamp AS created_on,  ' ||
         'ind.updatedby AS updated_by, '||
         'ind.createdby AS created_by '||
        'FROM  ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelation_v relation, ' ||
         'cmpindex_v ind, ' ||
         'CMPPARTITIONKEY_V pk ' ||
        'WHERE  ' ||
         'pk.OWNINGINDEX(+) = ind.elementid AND ' ||
         'ind.owningrelation = relation.elementid AND ' ||
         'relation.installedmodule = schema.elementid ' ||
         'WITH READ ONLY';

    execute immediate
          'CREATE OR REPLACE VIEW WB$_IV_INDEX_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, ' ||
         'ind.name AS index_name, ' ||
         'ind.elementid AS index_id, ' ||
         'ind.INDEXTYPE AS index_type, ' ||
         'attr.ELEMENTID AS index_key_column_id, ' ||
         'attr.name AS index_key_column, ' ||
         'attr.logicalname AS business_name, ' ||
         'attr.description AS description, ' ||
         'pkc.sequence AS position, ' ||
         'pkc.updatetimestamp AS updated_on, ' ||
         'pkc.creationtimestamp AS created_on,  ' ||
         'pkc.updatedby AS updated_by, '||
         'pkc.createdby AS created_by '||
        'FROM  ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelation_v relation, ' ||
         'cmpindex_v ind, ' ||
         'CMPWBItemSetUsage_v pkc,  ' ||
         'cmpattribute_v attr ' || 
        'WHERE  ' ||
         'attr.elementid = pkc.ATTRIBUTE AND ' ||
         'ind.owningrelation = relation.elementid AND ' ||
         'relation.installedmodule = schema.elementid AND ' ||
         'ind.elementid = pkc.ITEMSET ' ||
         'WITH READ ONLY';

    execute immediate
          'CREATE OR REPLACE VIEW WB$_IV_INDEX_PARTITION_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, ' ||
         'ind.name AS index_name, ' ||
         'pk.PARTITIONTYPE AS index_PARTITIONING_TYPE, ' ||
         'pk.OWNINGINDEX AS INDEX_ID, ' ||
         'attr.ELEMENTID AS index_partition_key_column_id, ' ||
         'attr.name AS index_partition_key_column, ' ||
         'attr.logicalname AS business_name, ' ||
         'attr.description AS description, ' ||
         'pkc.sequence AS position, ' ||
         'pk.updatetimestamp AS updated_on, ' ||
         'pk.creationtimestamp AS created_on,  ' ||
         'pk.updatedby AS updated_by, '||
         'pk.createdby AS created_by '||
        'FROM  ' ||
         'CMPPARTITIONKEY_V pk, ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelation_v relation, ' ||
         'cmpindex_v ind, ' ||
         'CMPWBItemSetUsage_v pkc,  ' ||
         'cmpattribute_v attr ' ||
        'WHERE  ' ||
         'attr.elementid = pkc.ATTRIBUTE AND ' ||
         'pk.OWNINGINDEX = ind.elementid AND ' ||
         'pk.ISSUBPARTITIONKEY <> ''1'' AND ' ||
         'ind.owningrelation = relation.elementid AND ' ||
         'relation.installedmodule = schema.elementid AND ' ||
         'pk.elementid = pkc.ITEMSET(+) ' ||
         'WITH READ ONLY';

    execute immediate
          'CREATE OR REPLACE VIEW WB$_IV_INDEX_PARTITIONS 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, ' ||
         'ind.elementid AS INDEX_ID, ' ||
         'ind.name AS index_name, ' ||
         'ind.INDEXTYPE AS index_type, ' ||
         'upper(pk.PARTITIONTYPE) AS index_PARTITIONING_TYPE, ' ||
         'par.elementid as partition_id, ' ||
         'par.name as name, ' ||
         'par.logicalname AS business_name, ' ||
         'par.description AS description, ' ||
         'par.PARTITIONORDER as position, ' ||
         'par.VALUECLAUSE AS VALUE_CLAUSE, ' ||
         'par.updatetimestamp AS updated_on, ' ||
         'par.creationtimestamp AS created_on,  ' ||
         'par.updatedby AS updated_by, '||
         'par.createdby AS created_by '||
        'FROM  ' ||
         'CMPPARTITIONKEY_V pk, ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelation_v relation, ' ||
         'cmpindex_v ind, ' ||
         'CMPPARTITION_V par ' ||
        'WHERE  ' ||
         'par.PARTITIONKEY = pk.elementid AND ' ||
         'upper(pk.PARTITIONTYPE) <> ''HASH BY QUANTITY'' AND ' ||
         'pk.OWNINGINDEX = ind.elementid AND ' ||
         'pk.ISSUBPARTITIONKEY <> ''1'' AND ' ||
         'ind.owningrelation = relation.elementid AND ' ||
         'relation.installedmodule = schema.elementid ' ||
         'WITH READ ONLY';

    execute immediate
          'CREATE OR REPLACE VIEW WB$_IV_PARTITION_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, ' ||
         'upper(pk.PARTITIONTYPE) AS PARTITIONING_TYPE, ' ||
         'attr.ELEMENTID AS partition_key_column_id, ' ||
         'attr.name AS partition_key_column, ' ||
         'attr.logicalname AS business_name, ' ||
         'attr.description AS description, ' ||
         'pkc.sequence AS position, ' ||
         'pk.COUNT AS COUNT, ' ||
         'pk.HASHPARTITIONQUANTITY AS HASH_PARTITION_QUANTITY, ' ||
         'pkc.updatetimestamp AS updated_on, ' ||
         'pkc.creationtimestamp AS created_on,  ' ||
         'pkc.updatedby AS updated_by, '||
         'pkc.createdby AS created_by '||
        'FROM  ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelation_v relation, ' ||
         'CMPPARTITIONKEY_V pk, ' ||
         'CMPWBItemSetUsage_v pkc,  ' ||
         'cmpattribute_v attr ' ||
        ' WHERE  ' ||
         'attr.elementid = pkc.ATTRIBUTE AND ' ||
         'pk.owningrelation = relation.elementid AND ' ||
         'pk.ISSUBPARTITIONKEY <> ''1'' and ' ||
         'pk.OWNINGINDEX is null and ' ||
         'pk.elementid = pkc.ITEMSET and ' ||
         'relation.installedmodule = schema.elementid ' ||
         'WITH READ ONLY';

    execute immediate
          'CREATE OR REPLACE VIEW WB$_IV_SUBPARTITION_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, ' ||
         'upper(pk.PARTITIONTYPE) AS PARTITIONING_TYPE, ' ||
         'attr.ELEMENTID AS subpartition_key_column_id, ' ||
         'attr.name AS subpartition_key_column, ' ||
         'attr.logicalname AS business_name, ' ||
         'attr.description AS description, ' ||
         'pkc.sequence AS position, ' ||
         'pk.COUNT AS COUNT, ' ||
         'pk.HASHPARTITIONQUANTITY AS HASH_PARTITION_QUANTITY, ' ||
         'pkc.updatetimestamp AS updated_on, ' ||
         'pkc.creationtimestamp AS created_on,  ' ||
         'pkc.updatedby AS updated_by, '||
         'pkc.createdby AS created_by '||
        'FROM  ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelation_v relation, ' ||
         'CMPPARTITIONKEY_V pk, ' ||
         'CMPWBItemSetUsage_v pkc,  ' ||
         'cmpattribute_v attr ' ||
        ' WHERE  ' ||
         'attr.elementid = pkc.ATTRIBUTE AND ' ||
         'pk.owningrelation = relation.elementid AND ' ||
         'pk.ISSUBPARTITIONKEY = ''1'' and ' ||
         'pk.OWNINGINDEX is null and ' ||
         'pk.elementid = pkc.ITEMSET and ' ||
         'relation.installedmodule = schema.elementid ' ||
         'WITH READ ONLY';

    execute immediate
          'CREATE OR REPLACE VIEW WB$_IV_PARTITIONS 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, ' ||
         'par.elementid as partition_id, ' ||
         'upper(pk.PARTITIONTYPE) AS PARTITIONING_TYPE, ' ||
         'par.name as name, ' ||
         'par.logicalname AS business_name, ' ||
         'par.description AS description, ' ||
         'par.PARTITIONORDER as position, ' ||
         'par.VALUECLAUSE AS VALUE_CLAUSE, ' ||
         'par.HASHSUBPARTITIONCOUNT AS HASH_SUBPARTITION_COUNT, ' ||
         'par.updatetimestamp AS updated_on, ' ||
         'par.creationtimestamp AS created_on,  ' ||
         'par.updatedby AS updated_by, '||
         'par.createdby AS created_by '||
        'FROM  ' ||
         'CMPPARTITIONKEY_V pk, ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelation_v relation, ' ||
         'CMPPARTITION_V par ' ||
        'WHERE  ' ||
         'par.PARTITIONKEY = pk.elementid AND ' ||
         'upper(pk.PARTITIONTYPE) <> ''HASH BY QUANTITY'' AND ' ||
         'pk.OWNINGINDEX is null AND ' ||
         'pk.ISSUBPARTITIONKEY <> ''1'' AND ' ||
         'pk.owningrelation = relation.elementid AND ' ||
         'relation.installedmodule = schema.elementid ' ||
         'WITH READ ONLY';

    execute immediate
          'CREATE OR REPLACE VIEW WB$_IV_SUBPARTITION_TEMPLATES 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, ' ||
         'par.elementid as partition_id, ' ||
         'upper(pk.PARTITIONTYPE) AS PARTITIONING_TYPE, ' ||
         'par.name as name, ' ||
         'par.logicalname AS business_name, ' ||
         'par.description AS description, ' ||
         'par.PARTITIONORDER as position, ' ||
         'par.VALUECLAUSE AS VALUE_CLAUSE, ' ||
         'par.updatetimestamp AS updated_on, ' ||
         'par.creationtimestamp AS created_on,  ' ||
         'par.updatedby AS updated_by, '||
         'par.createdby AS created_by '||
        'FROM  ' ||
         'CMPPARTITIONKEY_V pk, ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelation_v relation, ' ||
         'CMPPARTITION_V par ' ||
        'WHERE  ' ||
         'par.PARTITIONKEY = pk.elementid AND ' ||
         'upper(pk.PARTITIONTYPE) <> ''HASH BY QUANTITY'' AND ' ||
         'pk.OWNINGINDEX is null AND ' ||
         'pk.ISSUBPARTITIONKEY = ''1'' AND ' ||
         'pk.owningrelation = relation.elementid AND ' ||
         'relation.installedmodule = schema.elementid ' ||
         'WITH READ ONLY';

    execute immediate
          'CREATE OR REPLACE VIEW WB$_IV_SUBPARTITIONS 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, ' ||
         'par.elementid as parent_partition_id, ' ||
         'par.name as parent_name, ' ||
         'upper(pk.PARTITIONTYPE) AS PARTITIONING_TYPE, ' ||
         'par2.elementid as subpartition_id, ' ||
         'par2.name as name, ' ||
         'par2.logicalname AS business_name, ' ||
         'par2.description AS description, ' ||
         'par2.PARTITIONORDER as position, ' ||
         'par2.VALUECLAUSE AS VALUE_CLAUSE, ' ||
         'par2.updatetimestamp AS updated_on, ' ||
         'par2.creationtimestamp AS created_on,  ' ||
         'par2.updatedby AS updated_by, '||
         'par2.createdby AS created_by '||
        'FROM  ' ||
         'CMPPARTITIONKEY_V pk, ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelation_v relation, ' ||
         'CMPPARTITION_V par, ' ||
         'CMPPARTITION_V par2 ' ||
        'WHERE  ' ||
         'par2.PARENTPARTITION = par.elementid AND ' ||
         'par.PARTITIONKEY = pk.elementid AND ' ||
         'upper(pk.PARTITIONTYPE) <> ''HASH BY QUANTITY'' AND ' ||
         'pk.OWNINGINDEX is null AND ' ||
         'pk.ISSUBPARTITIONKEY <> ''1'' AND ' ||
         'pk.owningrelation = relation.elementid AND ' ||
         'relation.installedmodule = schema.elementid ' ||
         'WITH READ ONLY';

    execute immediate
          'CREATE OR REPLACE VIEW WB$_IV_ATTRIBUTE_SETS 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, ' ||
         'attset.name AS attribute_set_name, ' ||
         'attset.elementid AS attribute_set_id, ' ||
         'attset.logicalname AS business_name, ' ||
         'attset.description AS description, ' ||
         'attset.updatetimestamp AS updated_on, ' ||
         'attset.creationtimestamp AS created_on,  ' ||
         'attset.updatedby AS updated_by, '||
         'attset.createdby AS created_by '||
        'FROM  ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelation_v relation, ' ||
         'CMPWBNAMEDITEMSET_V attset ' ||
        'WHERE  ' ||
         'attset.TYPECODE = ''USER_DEFINED'' AND ' ||
         'attset.owningrelation = relation.elementid AND ' ||
         'relation.installedmodule = schema.elementid ' ||
         'WITH READ ONLY';

    execute immediate
          'CREATE OR REPLACE VIEW WB$_IV_ATTRIBUTES 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, ' ||
         'attset.name AS attribute_set_name, ' ||
         'attset.elementid AS attribute_set_id, ' ||
         'attr.name AS attribute_name, ' ||
         'att.SEQUENCE AS position, ' ||
         'att.ATTRIBUTE AS attribute_id, ' ||
         'attr.logicalname AS business_name, ' ||
         'attr.description AS description, ' ||
         'attr.updatetimestamp AS updated_on, ' ||
         'attr.creationtimestamp AS created_on,  ' ||
         'attr.updatedby AS updated_by, '||
         'attr.createdby AS created_by '||
        'FROM  ' ||
         'cmpinstalledmodule_v schema, ' ||
         'cmprelation_v relation, ' ||
         'CMPWBNAMEDITEMSET_V attset, ' ||
         'CMPWBITEMSETUSAGE_V att, ' ||
         'cmpattribute_v attr ' ||
        'WHERE  ' ||
         'attset.elementid = att.ITEMSET AND ' ||
         'attr.elementid = att.ATTRIBUTE AND ' ||
         'attset.owningrelation = relation.elementid AND ' ||
         'relation.installedmodule = schema.elementid ' ||
         'WITH READ ONLY';

END; 
/