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

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

BEGIN

execute immediate
'create or replace view all_iv_xform_map_components3 as '||
'SELECT /*+first_rows leading(comp) use_nl(compositeComp)*/ '||
'      compositeComp.elementid          AS map_id, '||
'      compositeComp.name              AS map_name, '||
'      comp.elementid                  AS map_component_id, '||
'      comp.name                        AS map_component_name, '||
'      comp.logicalname                AS business_name, '||
'      comp.description                AS description, '||
'      OWM_VIEW_UTILITIES.OPERATOR_TYPE(comp.operatortype) '||
'                                        AS operator_type, '||
'      compositeComp.elementid          AS composite_component_id, '||
'      compositeComp.name              AS composite_component_name, '||
'      (select rel.elementid from cmprelation_v rel '||
'        where comp.referencingobject = rel.elementid) AS data_entity_id, '||
'      (select OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE(rel.classname) '||
'        from cmprelation_v rel '||
'        where comp.referencingobject = rel.elementid) AS data_entity_type, '||
'      (select rel.name from cmprelation_v rel '||
'        where comp.referencingobject = rel.elementid) AS data_item_name, '||
'      comp.updatetimestamp            AS updated_on, '||
'      comp.creationtimestamp          AS created_on '||
'FROM '||
'      cmpmap_v compositeComp, '||
'      cmpmapoperator_v          comp '||
'WHERE '||
'        comp.operatorowner  = compositeComp.elementid '||
'WITH READ ONLY';

execute immediate 
'create or replace view all_iv_xform_map_parameters2 as  '||
'SELECT  /*+leading(c) use_nl(g) use_nl(p)*/  '||
'        c.elementid                              AS map_component_id,  '||
'        c.name                                    AS map_component_name,  '||
'        p.elementid                              AS parameter_id,  '||
'        p.name                                    AS parameter_name,  '||
'        p.logicalname                            AS business_name,  '||
'        p.description                            AS description,  '||
'        composite.elementid                      AS map_id,  '||
'        composite.name                            AS map_name,  '||
'        g.name                                    AS parameter_group_name,  '||
'        g.elementid                                    AS parameter_group_id,  '||
'        OWM_VIEW_UTILITIES.PARAM_DIRECTION(p.direction) AS parameter_type,  '||
'        p.position                                      AS position,  '||
'        p.datatype                                      AS data_type,  '||
'        OWM_VIEW_UTILITIES.PARAMETER_EXPRESSION(  '||
'          p.expression, p.elementid, c.elementid) AS transformation_expression,  '||
'        (select /*+no_merge*/ i.elementid from cmpitem_v i  '||
'        where p.referringitem = i.elementid)    AS data_item_id,  '||
'        (select /*+no_merge*/ i.typecode from cmpitem_v i  '||
'        where p.referringitem = i.elementid)    AS data_item_type,  '||
'        (select /*+no_merge*/ i.name from cmpitem_v i  '||
'        where p.referringitem = i.elementid)    AS data_item_name,  '||
'        p2.elementid                              AS source_parameter_id,  '||
'        p2.name                                  AS source_parameter_name,  '||
'        p.updatetimestamp                        AS updated_on,  '||
'        p.creationtimestamp                      AS created_on  '||
'FROM  '||
'        cmpmapattribute_v        p2,  '||
'        cmpmap_v composite,  '||
'        cmpmapattribute_v        p,  '||
'        cmpmapattributegroup_v  g,  '||
'        cmpmapoperator_v          c  '||
'WHERE  '||
'        c.elementid              = g.attributegroupowner '||
'AND    g.elementid              = p.attributegroup  '||
'AND    c.operatorowner                   = composite.elementid(+)  '||
'AND    p.connectfrom            = p2.elementid(+)  '||
'WITH READ ONLY';

execute immediate 
'create or replace view all_iv_xform_map_components2 as '||
'SELECT /*+first_rows leading(rel) use_nl(comp) */ '||
'      compositeComp.elementid          AS map_id, '||
'      compositeComp.name              AS map_name, '||
'      comp.elementid                  AS map_component_id, '||
'      comp.name                        AS map_component_name, '||
'      comp.logicalname                AS business_name, '||
'      comp.description                AS description, '||
'      OWM_VIEW_UTILITIES.OPERATOR_TYPE(comp.operatortype) '||
'                                        AS operator_type, '||
'      compositeComp.elementid          AS composite_component_id, '||
'      compositeComp.name              AS composite_component_name, '||
'      rel.elementid                    AS data_entity_id, '||
'      OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE(rel.classname) '||
'                                        AS data_entity_type, '||
'      rel.name                        AS data_item_name, '||
'      comp.updatetimestamp            AS updated_on, '||
'      comp.creationtimestamp          AS created_on '||
'FROM '||
'      cmprelation_v                rel, '||
'      cmpmap_v compositeComp, '||
'      cmpmapoperator_v          comp '||
'WHERE '||
'       comp.operatorowner                  =  compositeComp.elementid '||
'AND    comp.referencingobject      = rel.elementid '||
'WITH READ ONLY ';

execute immediate 
'CREATE OR REPLACE VIEW ALL_IV_XFORM_MAP_PARAMETERS3 as  '||
'SELECT  /*+first_rows leading(p2) use_nl(p)*/  '||
'        c.elementid                              AS map_component_id,  '||
'        c.name                                    AS map_component_name,  '||
'        p.elementid                              AS parameter_id,  '||
'        p.name                                    AS parameter_name,  '||
'        p.logicalname                            AS business_name,  '||
'        p.description                            AS description, '|| 
'        composite.elementid                      AS map_id,  '||
'        composite.name                            AS map_name,  '||
'        g.name                                    AS parameter_group_name,  '||
'        g.elementid                                    AS parameter_group_id,  '||
'        OWM_VIEW_UTILITIES.PARAM_DIRECTION(p.direction) AS parameter_type,  '||
'        p.position                                      AS position,  '||
'        p.datatype                                      AS data_type,  '||
'        OWM_VIEW_UTILITIES.PARAMETER_EXPRESSION(  '||
'          p.expression, p.elementid, c.elementid) AS  '||
'transformation_expression,  '||
'        i.elementid                              AS data_item_id,  '||
'        i.typecode                                AS data_item_type,  '||
'        i.name                                    AS data_item_name,  '||
'        p2.elementid                              AS source_parameter_id,  '||
'        p2.name                                  AS source_parameter_name,  '||
'        p.updatetimestamp                        AS updated_on,  '||
'        p.creationtimestamp                      AS created_on  '||
'FROM  '||
'        cmpitem_v                    i,  '||
'        cmpmapattribute_v        p2,  '||
'        cmpmap_v composite,  '||
'        cmpmapattribute_v        p,  '||
'        cmpmapattributegroup_v  g,  '||
'        cmpmapoperator_v          c  '||
'WHERE  '||
'        c.elementid             = g.attributegroupowner  '||
'AND    g.elementid              = p.attributegroup  '||
'AND    c.operatorowner                  = composite.elementid(+)  '||
'AND    p.connectfrom            = p2.elementid  '||
'AND    p.referencingobject      = i.elementid(+)  '||
'WITH READ ONLY';

execute immediate
	'create or replace view ALL_IV_XFORM_MAP_PARAMETERS4 as '||
'	SELECT  /*+leading(c) use_nl(g) use_nl(p) index(c,idx_elementid2)*/ '||
'        c.elementid                              AS map_component_id, '||
'        p.elementid                              AS parameter_id, '||
'        (select i.elementid '||
'        from cmpitem_v i '||
'        where i.elementid = p.referencingobject)    AS data_item_id, '||
'        (select p2.elementid '||
'        from cmpmapattribute_v p2 '||
'        where p2.elementid = p.connectfrom)      AS source_parameter_id '||
'FROM '||
'        cmpmapattribute_v        p, '||
'        cmpmapattributegroup_v  g, '||
'        cmpmapoperator_v          c '||
'WHERE '||
'        c.elementid              = g.attributegroupowner '||
'AND    g.elementid              = p.attributegroup WITH READ ONLY';

execute immediate 
	'CREATE OR REPLACE VIEW ALL_IV_XFORM_MAP_PRIM_SOURCES as  '||
		'select pv.firstclassobject as map_id,  '||
		'     pv.propertyowner as map_component_id,  '||
		'     comp.referencingobject as data_entity_id '||
		'from  '||
		'cmpstringpropertyvalue_v pv, '||
		'cmpmapoperator_v comp '||
		'where '||
		'pv.propertyowner = comp.elementid and '||
		'pv.logicalname = ''DATAENTITY.DEFAULT.PRIMARY_SOURCE'' and '||
		'pv.value = ''YES''' ||
		' with read only';      
 
   execute immediate
	'create materialized view ALL_IV_XFORM_MAP_SOURCES BUILD IMMEDIATE as '||
	'select '||
	'	src.compositestagecomponent map_id, '||
	'	csc.name map_name, '||
	'	src.elementid map_component_id, '||
	'	src.name map_component_name, '||
	'	src.referencingobject data_entity_id, '||
	'	OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE(src.classname) data_entity_type, '||
	'	rel.name data_entity_name '||
	'from 	cmpmapoperator_v src, cmpmap_v csc, '||
	'	cmprelation_v rel  '||
	'where 	src.operatorowner = csc.elementid '||
	'	and src.referencingobject = rel.elementId '||
	'	and not exists (select 1 from cmpmapoperatorusage_v si '||
	'	where si.firstClassObject = src.firstClassObject '||
	'	and src.elementid = si.targetoperator) '||
	'	and src.operatorType in (''oracle.wh.service.impl.mapping.component.Dimension'', '||
	'		''oracle.wh.service.impl.mapping.component.Fact'', '||
	'		''oracle.wh.service.impl.mapping.component.Sequence'', '||
	'		''oracle.wh.service.impl.mapping.component.Table'')';

execute immediate
	'create materialized view ALL_IV_XFORM_MAP_TARGETS BUILD IMMEDIATE as '||
	'select /*+ NO_EXPAND */   '||
	'	csc.elementid map_id,  '||
	'	csc.name map_name,  '||
	'	tgt.elementid map_component_id, '||
	'	tgt.name map_component_name, '||
	'	tgt.referencingobject data_entity_id, '||
	'	OWM_VIEW_UTILITIES.CLASSIFIED_OBJ_TYPE(tgt.classname) data_entity_type, '||
	'	rel.name data_entity_name '||
	'from cmpmapoperator_v tgt, cmpmap_v csc, '||
	'	cmprelation_v rel  '||
	'where tgt.operatorowner = csc.elementid '||
	'	and tgt.referencingobject = rel.elementId '||
	'	and not exists (select 1 from cmpmapoperatorusage_v si '||
        '        			where si.firstclassobject = tgt.firstclassobject '||
	'				and si.sourceoperator = tgt.elementid) '||
	'	and exists (select 1 from cmpmapoperatorusage_v si '||
	'			where si.firstClassObject = tgt.firstClassObject '||
	'	                and si.targetoperator = tgt.elementId)   '||
	'	and tgt.operatorType in (''oracle.wh.service.impl.mapping.component.Dimension'', '||
	'		''oracle.wh.service.impl.mapping.component.Fact'', '||
	'		''oracle.wh.service.impl.mapping.component.Sequence'', '||
	'		''oracle.wh.service.impl.mapping.component.Table'')';

execute immediate
'create or replace view all_iv_operator_sources as '||
'select /*+ INDEX(CMPAllClasses, IDX_REFREL) '||
'  INDEX(CMPAllClasses, IDX_PARAM_GRP) '||
'  INDEX(CMPAllClasses, IDX_STAGE_CMP) '||
'  INDEX(CMPAllClasses, IDX_CONNECTFROM) '||
'  INDEX(CMPAllClasses, IDX_COMP_STAGE_CMP) */ '||
'      func.elementid function_id, func.name function_name, '||
'      func.functioncategory function_library_id, '||
'      map.elementid map_id, map.name map_name, '||
'      op.elementid operator_id, op.name operator_name, '||
'      opp.elementid op_param_id, opp.name op_param_name, '||
'      src.elementid src_component_id, src.name src_component_name, '||
'      src.referringRelation referring_object, '||
'      srcp.elementid src_parameter_id, srcp.name src_parameter_name '||
'from cmpfunction_v func, cmpmap_v map, '||
'    cmpmapoperator_v op, '||
'    cmpmapattributegroup_v oppg, cmpmapattribute_v opp, '||
'    cmpmapoperator_v src, '||
'    cmpmapattributegroup_v srcpg, cmpmapattribute_v srcp '||
'where map.elementid = op.operatorowner '||
'  and func.elementid = op.referencingobject '||
'  and op.elementid = oppg.attributegroupowner '||
'  and oppg.elementid = opp.attributegroup '||
'  and srcp.elementid(+) = opp.connectFrom '||
'  and srcpg.elementid = srcp.attributegroup '||
'  and src.elementid = srcpg.attributegroupowner '||
'WITH READ ONLY'; 
 
execute immediate
'create or replace view all_iv_operator_targets as '||
'select /*+ INDEX(CMPAllClasses, IDX_REFREL) '||
'  INDEX(CMPAllClasses, IDX_PARAM_GRP) '||
'  INDEX(CMPAllClasses, IDX_STAGE_CMP) '||
'  INDEX(CMPAllClasses, IDX_CONNECTFROM) '||
'  INDEX(CMPAllClasses, IDX_COMP_STAGE_CMP) */ '||
'      func.elementid function_id, func.name function_name, '||
'      func.functioncategory function_library_id, '||
'      map.elementid map_id, map.name map_name, '||
'      op.elementid operator_id, op.name operator_name, '||
'      opp.elementid op_param_id, opp.name op_param_name, '||
'      tgt.elementid tgt_component_id, tgt.name tgt_component_name, '||
'      tgt.referringRelation referring_object, '||
'      tgtp.elementid tgt_parameter_id, tgtp.name tgt_parameter_name '||
'from cmpfunction_v func, cmpmap_v map, '||
'    cmpmapoperator_v op, '||
'    cmpmapattributegroup_v oppg, cmpmapattribute_v opp, '||
'    cmpmapoperator_v tgt, '||
'    cmpmapattributegroup_v tgtpg, cmpmapattribute_v tgtp '||
'where map.elementid = op.operatorowner '||
'  and func.elementid = op.referencingobject '||
'  and op.elementid = oppg.attributegroupowner '||
'  and oppg.elementid = opp.attributegroup '||
'  and opp.elementid = tgtp.connectFrom(+) '||
'  and tgtpg.elementid = tgtp.attributegroup '||
'  and tgt.elementid = tgtpg.attributegroupowner '||
'WITH READ ONLY';

execute immediate
'create or replace view all_iv_operator_sources2 as '||
'select /*+ first_rows leading(func) index(func,idx_name) index(op,idx_refrel) '||
    '    index(oppg,idx_stage_cmp, index(opp,idx_param_grp) */ '||
    '  func.elementid        function_id, '||
    ' func.name            function_name, '||
    '  func.functioncategory function_library_id, '||
    '  map.elementid        map_id, '||
    '  map.name              map_name, '||
    '  op.elementid          operator_id, '||
    '  op.name              operator_name, '||
    '  opp.elementid        op_param_id, '||
    '  opp.name              op_param_name, '||
    '  src.elementid        src_component_id, '||
    '  src.name              src_component_name, '||
    '  src.referringrelation referring_object, '||
    '  srcp.elementid        src_parameter_id, '||
    '  srcp.name            src_parameter_name '||
' from  '||
    '  cmpfunction_v                func, '||
    '  cmpmapoperator_v          op, '||
    '  cmpmap_v map, '||
    '  cmpmapattributegroup_v  oppg, '||
    '  cmpmapattribute_v        opp, '||
    '  cmpmapattribute_v        srcp, '||
    '  cmpmapattributegroup_v  srcpg, '||
    '  cmpmapoperator_v          src '||
' where  '||
    '  op.referringrelation      = func.elementid '||
' and    op.operatorowner         = map.elementid '||
' and    op.elementid             = oppg.attributegroupowner '||
' and    opp.attributegroup       = oppg.elementid '||
' and    opp.connectfrom is not null '||
' and    opp.connectfrom            = srcp.elementid(+) '||
' and    srcpg.elementid            = srcp.attributegroup'||
' and    srcpg.attributegroupowner       = src.elementid '||
' with read only';

execute immediate
'create or replace view all_iv_classifications as select 1 colid from dual with read only';

execute immediate
'create or replace view all_iv_classification_items as select 1 colid from dual with read only';

execute immediate
'create or replace view all_iv_impact_dependents as select 1 colid from dual with read only';

execute immediate
'create or replace view all_iv_lineage_dependents as select 1 colid from dual with read only';

execute immediate
'create or replace view all_iv_schemas as select 1 colid from dual with read only';

END;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de