CREATE OR REPLACE VIEW dataflow_dependency_v
  AS
  SELECT 
     a.target_comp_id as source_comp_id,
     null as source_entity_id,
	   a.target_param_id as source_param_id,
	   null as source_item_id,
	   b.target_param_id as target_param_id,
	   b.target_item_id as target_item_id ,
	   b.target_comp_id as target_comp_id,
	   b.target_entity_id as target_entity_id from
     (
           SELECT -- for Maplet dataflow
           source_comp.parentid AS source_comp_id,
           NULL AS source_entity_id,
           intralink.linkto AS source_param_id,
           NULL AS source_item_id,
           intralink.linkfrom AS target_param_id,
           NULL AS target_item_id,
           target_comp.parentid AS target_comp_id,
           NULL AS target_entity_id
           FROM
           intralink,
           pctree source_grp,
           pctree target_grp,
           pctree source_comp,
           pctree target_comp,
           cmpmapoperator_v a
           WHERE
           intralink.linkfromrole = 'ChildAttribute' AND
           intralink.linktorole = 'ParentAttribute' AND
           intralink.linkto = source_grp.childid AND
           source_grp.parentid = source_comp.childid AND
           intralink.linkfrom = target_grp.childid AND
           target_grp.parentid = target_comp.childid AND
           target_comp.parentid = a.elementid 
      ) a,
      (
           SELECT -- for Mapping dataflow
           source_comp.parentid AS source_comp_id,
           source_entity.linkto AS source_entity_id,
           intralink.linkto AS source_param_id,
           source_item.linkto AS source_item_id,
           intralink.linkfrom AS target_param_id,
           target_item.linkto AS target_item_id,
           target_comp.parentid AS target_comp_id,
           target_entity.linkto AS target_entity_id
           FROM
           intralink,
           pctree source_grp,
           pctree target_grp,
           pctree source_comp,
           pctree target_comp,
           interlink source_item,
           interlink target_item,
           interlink source_entity,
           interlink target_entity,
           cmpmapoperator_v a
           WHERE
           intralink.linkfromrole = 'ConnectTo' AND
           intralink.linktorole = 'ConnectFrom' AND
           intralink.linkto = source_grp.childid AND
           source_grp.parentid = source_comp.childid AND
           intralink.linkfrom = target_grp.childid AND
           target_grp.parentid = target_comp.childid AND
           source_item.linktorole = 'ReferencingObject' AND
           target_item.linktorole = 'ReferencingObject' AND
           source_item.linkfrom = intralink.linkto AND
           target_item.linkfrom = intralink.linkfrom AND
           source_entity.linkfrom = source_comp.parentid AND
           target_entity.linkfrom = target_comp.parentid AND
           source_comp.parentid = a.elementid AND a.strongtypename  like '%SubMap'
        ) b
        WHERE 
        a.source_param_id = b.source_param_id AND 
        a.source_comp_id = b.source_comp_id
        UNION
           SELECT -- for Maplet dataflow
           source_comp.parentid AS source_comp_id,
           NULL AS source_entity_id,
           intralink.linkto AS source_param_id,
           NULL AS source_item_id,
           intralink.linkfrom AS target_param_id,
           NULL AS target_item_id,
           target_comp.parentid AS target_comp_id,
           NULL AS target_entity_id
           FROM
           intralink,
           pctree source_grp,
           pctree target_grp,
           pctree source_comp,
           pctree target_comp
           WHERE
           intralink.linkfromrole = 'ChildAttribute' AND
           intralink.linktorole = 'ParentAttribute' AND
           intralink.linkto = source_grp.childid AND
           source_grp.parentid = source_comp.childid AND
           intralink.linkfrom = target_grp.childid AND
           target_grp.parentid = target_comp.childid 
           UNION
           SELECT -- for Mapping dataflow
           source_comp.parentid AS source_comp_id,
           source_entity.linkto AS source_entity_id,
           intralink.linkto AS source_param_id,
           source_item.linkto AS source_item_id,
           intralink.linkfrom AS target_param_id,
           target_item.linkto AS target_item_id,
           target_comp.parentid AS target_comp_id,
           target_entity.linkto AS target_entity_id
           FROM
           intralink,
           pctree source_grp,
           pctree target_grp,
           pctree source_comp,
           pctree target_comp,
           pctree parent_map,
           pctree parent_folder,
           interlink source_item,
           interlink target_item,
           interlink source_entity,
           interlink target_entity
           WHERE
           intralink.linkfromrole = 'ConnectTo' AND
           intralink.linktorole = 'ConnectFrom' AND
           intralink.linkto = source_grp.childid AND
           source_grp.parentid = source_comp.childid AND
           intralink.linkfrom = target_grp.childid AND
           target_grp.parentid = target_comp.childid AND
           source_item.linktorole = 'ReferencingObject' AND
           target_item.linktorole = 'ReferencingObject' AND
           source_item.linkfrom = intralink.linkto AND
           target_item.linkfrom = intralink.linkfrom AND
           source_entity.linkfrom = source_comp.parentid AND
           target_entity.linkfrom = target_comp.parentid AND
            source_comp.parentid = parent_map.childid AND
           parent_map.parentid = parent_folder.childid AND
           parent_folder.parentid not in (select elementid from cmpprofile_v)
           UNION -- for unique key and foreign key connection
           SELECT
           NULL AS source_comp_id,
           source_rel.parentid AS source_entity_id,
           NULL AS source_param_id,
           keyRef.linkto AS source_item_id,
           NULL AS target_param_id,
           keyRef.linkfrom AS target_item_id,
           NULL AS target_comp_id,
           target_rel.parentid AS target_entity_id
           FROM
           interlink keyRef,
           pctree source_rel,
           pctree target_rel
           WHERE
           keyRef.linktorole = 'UniqueKey' AND
           keyRef.linkfromrole = 'ForeignKeys' AND
           source_rel.childid = keyRef.linkto AND
           target_rel.childid = keyRef.linkfrom
           UNION -- for external table and file/record/field connection
           SELECT
           NULL AS source_comp_id,
           source_file.parentid AS source_entity_id,
           NULL AS source_param_id,
           field_item.linkto AS source_item_id,
           NULL AS target_param_id,
           field_item.linkfrom AS target_item_id,
           NULL AS target_comp_id,
           target_exttab.parentid AS target_entity_id
           FROM
           interlink field_item,
           pctree source_file,
           pctree source_rec,
           pctree target_exttab
           WHERE
           field_item.linktorole = 'ExternalTableField' AND
           field_item.linkfromrole = 'ExternalTableItem' AND
           source_rec.childid = field_item.linkto AND
           source_file.childid = source_rec.parentid AND
           target_exttab.childid = field_item.linkfrom
           UNION -- for dimension and cube
           SELECT
           NULL AS source_comp_id,
           dim_level.parentid AS source_entity_id,
           NULL AS source_param_id,
           level_dimref.linkto AS source_item_id,
           NULL AS target_param_id,
           level_dimref.linkfrom AS target_item_id,
           NULL AS target_comp_id,
           cube_dimref.parentid AS target_entity_id
           FROM
           interlink level_dimref,
           pctree dim_level,
           pctree cube_dimref
           WHERE
           level_dimref.linkfromrole = 'ReferencingCubeDimRef' AND
           level_dimref.linktorole = 'ReferencedLevel' AND
           dim_level.parentrole = 'OwningDimension' AND
           dim_level.childid = level_dimref.linkto AND
           cube_dimref.parentrole = 'OwningCube' AND
           cube_dimref.childid = level_dimref.linkfrom
           UNION -- for implementation table and cube
           SELECT
           NULL AS source_comp_id,
           source_table.parentid AS source_entity_id,
           NULL AS source_param_id,
           column_measure.linkto AS source_item_id,
           NULL AS target_param_id,
           column_measure.linkfrom AS target_item_id,
           NULL AS target_comp_id,
           target_cube.parentid AS target_entity_id
           FROM
           interlink column_measure,
           pctree source_table,
           pctree target_cube
           WHERE
           column_measure.linktorole = 'BindingAttribute' AND
           source_table.childid = column_measure.linkto AND
           target_cube.parentrole = 'OwningCube' AND
           target_cube.childid = column_measure.linkfrom
           UNION -- for implementation table and dimension
           SELECT
           NULL AS source_comp_id,
           source_table.parentid AS source_entity_id,
           NULL AS source_param_id,
           column_lvattr.linkto AS source_item_id,
           NULL AS target_param_id,
           column_lvattr.linkfrom AS target_item_id,
           NULL AS target_comp_id,
           target_dim.parentid AS target_entity_id
           FROM
           interlink column_lvattr,
           pctree source_table,
           pctree dim_level,
           pctree target_dim
           WHERE
           (column_lvattr.linktorole = 'BindingAttribute' OR
           column_lvattr.linktorole = 'BindingDimensionKeyAttr') AND
           source_table.childid = column_lvattr.linkto AND
           dim_level.parentrole = 'OwningLevel' AND
           dim_level.childid = column_lvattr.linkfrom AND
           target_dim.parentrole = 'OwningDimension' AND
           dim_level.parentid = target_dim.childid
           UNION -- for dataflow between queryItem/reportItem and attributes of design object
           SELECT
           NULL AS source_comp_id,
           sourceRel.parentid AS source_entity_id,
           NULL AS source_param_id,
           itemCon.linkto AS source_item_id,
           NULL AS target_param_id,
           itemCon.linkfrom AS target_item_id,
           NULL AS target_comp_id,
           targetObj.parentid AS target_entity_id
           FROM
           interlink itemCon,
           pctree sourceRel,
           pctree targetObj
           WHERE
           itemCon.linkfromrole = 'IntelligenceItems' AND
           itemCon.linkto = sourceRel.childid AND
           itemCon.linkfrom = targetObj.childid
           UNION -- for dataflow between reportItem and dimension
           SELECT
           NULL AS source_comp_id,
           itemCon.linkto AS source_entity_id,
           NULL AS source_param_id,
           itemCon.linkto AS source_item_id,
           NULL AS target_param_id,
           itemCon.linkfrom AS target_item_id,
           NULL AS target_comp_id,
           targetObj.parentid AS target_entity_id
           FROM
           interlink itemCon,
           pctree targetObj
           WHERE
           itemCon.linkfromrole = 'ReportItems' AND
           itemCon.linktorole = 'Dimension' AND
           itemCon.linkfrom = targetObj.childid AND
           itemCon.linkto NOT IN (SELECT sourceRel.parentid
	         FROM interlink itemCon, pctree sourceRel, pctree targetObj
	         WHERE itemCon.linkfromrole = 'ReportItems' AND
	               (itemCon.linktorole = 'Hierarchy' OR
	               itemCon.linktorole = 'InitialLevel') AND
	               itemCon.linkto = sourceRel.childid AND
                       itemCon.linkfrom = targetObj.childid )
           UNION -- for dataflow between reportItem and hierarchy/level
           SELECT
           NULL AS source_comp_id,
           sourceRel.parentid AS source_entity_id,
           NULL AS source_param_id,
           itemCon.linkto AS source_item_id,
           NULL AS target_param_id,
           itemCon.linkfrom AS target_item_id,
           NULL AS target_comp_id,
           targetObj.parentid AS target_entity_id
           FROM
           interlink itemCon,
           pctree sourceRel,
           pctree targetObj
           WHERE
           itemCon.linkfromrole = 'ReportItems' AND
           (itemCon.linktorole = 'Hierarchy' OR
           itemCon.linktorole = 'InitialLevel') AND
           itemCon.linkto = sourceRel.childid AND
           itemCon.linkfrom = targetObj.childid
           UNION -- for dataflow between LOV (Defining, Ordered) and queryItem
           SELECT
           NULL AS source_comp_id,
           sourceObj.parentid AS source_entity_id,
           NULL AS source_param_id,
           itemCon.linkto AS source_item_id,
           NULL AS target_param_id,
           itemCon.linkfrom AS target_item_id,
           NULL AS target_comp_id,
           itemCon.linkfrom AS target_entity_id
           FROM
           interlink itemCon,
           pctree sourceObj
           WHERE
           (itemCon.linkfromrole = 'ItemClassesUsingValues' OR
           itemCon.linkfromrole = 'ItemClassesOrdered') AND
           itemCon.linkto = sourceObj.childid
           UNION -- for dataflow between queryItem and LOV (as target)
           SELECT
           NULL AS source_comp_id,
           itemCon.linkto AS source_entity_id,
           NULL AS source_param_id,
           itemCon.linkto AS source_item_id,
           NULL AS target_param_id,
           itemCon.linkfrom AS target_item_id,
           NULL AS target_comp_id,
           targetObj.parentid AS target_entity_id
           FROM
           interlink itemCon,
           pctree targetObj
           WHERE
           itemCon.linkfromrole = 'QueryItems' AND
           itemCon.linkfrom = targetObj.childid
           UNION -- for dataflow between Simple QueryItem and Complex QueryItem/QueryCondition
           SELECT
           NULL AS source_comp_id,
           sourceObj.parentid AS source_entity_id,
           NULL AS source_param_id,
           srcItem.linkto AS source_item_id,
           NULL AS target_param_id,
           trgItem.parentid AS target_item_id,
           NULL AS target_comp_id,
           targetObj.parentid AS target_entity_id
           FROM
           interlink srcItem,
           pctree trgItem,
           pctree sourceObj,
           pctree targetObj
           WHERE
           (srcItem.linktorole = 'ReferencedSCO' OR
           srcItem.linktorole = 'ReferencedFunction') AND
           trgItem.parentrole = 'QueryExpOwner' AND
           srcItem.linkfrom = trgItem.childid AND
           srcItem.linkto = sourceObj.childid AND
           trgItem.parentid = targetObj.childid
           UNION -- for dataflow between QueryItem and DrillItem
           SELECT
           NULL AS source_comp_id,
           queryObj.parentid AS source_entity_id,
           NULL AS source_param_id,
           item.linkto AS source_item_id,
           NULL AS target_param_id,
           item.linkfrom AS target_item_id,
           NULL AS target_comp_id,
           drillpath.parentid AS target_entity_id
           FROM
           interlink item,
           pctree queryObj,
           pctree drilllevel,
           pctree drillpath
           WHERE
           item.linkfromrole = 'DrillItems' AND
           item.linktorole = 'QueryItem' AND
           queryObj.childid = item.linkto AND
           drilllevel.childid = item.linkfrom AND
           drilllevel.parentid = drillpath.childid
           UNION -- for dataflow between derivationSourceSCO and derivedSCO
           SELECT
           NULL AS source_comp_id,
           sourceObj.parentid AS source_entity_id,
           NULL AS source_param_id,
           itemCon.linkto AS source_item_id,
           NULL AS target_param_id,
           itemCon.linkfrom AS target_item_id,
           NULL AS target_comp_id,
           targetObj.parentid AS target_entity_id
           FROM
           interlink itemCon,
           pctree sourceObj,
           pctree targetObj
           WHERE
           itemCon.linktorole = 'DerivationSourceSCO' AND
           itemCon.linkto = sourceObj.childid AND
           itemCon.linkfrom = targetObj.childid AND
           itemCon.linkfrom NOT IN 
	         (SELECT queryExpRef.childid FROM pctree queryExpRef
	          WHERE queryExpRef.parentrole = 'QueryExpOwner' AND
                  queryExpRef.childrole = 'QueryExpRefs')
           UNION -- for dataflow between Table to Capture Process
           SELECT
           NULL AS source_comp_id,
           srcItem.linkto AS source_entity_id,
           NULL AS source_param_id,
           srcItem.linkto AS source_item_id,
           NULL AS target_param_id,
           trgItem.parentid AS target_item_id,
           NULL AS target_comp_id,
           trgItem.parentid AS target_entity_id
           FROM
           interlink srcItem,
           pctree trgItem
           WHERE
           srcItem.linkfromrole = 'CaptureReference' AND
           trgItem.parentrole = 'CaptureSetup' AND
           srcItem.linkfrom = trgItem.childid
           UNION -- for dataflow between Capture Process to Queue
           SELECT
           NULL AS source_comp_id,
           trgItem.childid AS source_entity_id,
           NULL AS source_param_id,
           trgItem.childid AS source_item_id,
           NULL AS target_param_id,
           trgItem.parentid AS target_item_id,
           NULL AS target_comp_id,
           trgItem.parentid AS target_entity_id
           FROM
           pctree trgItem
           WHERE
           trgItem.childrole = 'StreamsCaptures'
           UNION -- for dataflow between Queue to Propagation
           SELECT
           NULL AS source_comp_id,
           trgItem.parentid AS source_entity_id,
           NULL AS source_param_id,
           trgItem.parentid AS source_item_id,
           NULL AS target_param_id,
           trgItem.childid AS target_item_id,
           NULL AS target_comp_id,
           trgItem.childid AS target_entity_id
           FROM
           pctree trgItem
           WHERE
           trgItem.childrole = 'QueueSubscriber'
           UNION -- for dataflow between Propagation to Target Queue
           SELECT
           NULL AS source_comp_id,
           trgItem.linkfrom AS source_entity_id,
           NULL AS source_param_id,
           trgItem.linkfrom AS source_item_id,
           NULL AS target_param_id,
           trgItem.linkto AS target_item_id,
           NULL AS target_comp_id,
           trgItem.linkto AS target_entity_id
           FROM
           interlink trgItem
           WHERE
           trgItem.linkfromrole = 'Dummy' AND
           trgItem.linktorole = 'TargetQueue'
           UNION -- for dataflow between Queue Table and Queue
           SELECT
           NULL AS source_comp_id,
           srcItem.parentid AS source_entity_id,
           NULL AS source_param_id,
           srcItem.childid AS source_item_id,
           NULL AS target_param_id,
           trgItem.linkfrom AS target_item_id,
           NULL AS target_comp_id,
           trgItem.linkfrom AS target_entity_id
           FROM
           interlink trgItem,
           pctree srcItem
           WHERE
           trgItem.linktorole = 'QueueTable' AND
           trgItem.linkto = srcItem.parentid AND
           srcItem.parentrole = 'OwningRelation'
           UNION -- for bug 6730603 to return Configuration instead of Physical Object
           SELECT
           to_number(NULL) AS source_comp_id ,
           source_rel.parentid AS source_entity_id,
           to_number(NULL) AS source_param_id ,
           keyRef.linkto AS source_item_id,
           to_number(NULL) AS target_param_id ,
           targetObj.parentid AS target_item_id,
           to_number(NULL) AS target_comp_id ,
           targetObj2.parentid AS target_entity_id
           FROM
           interlink keyRef,
           pctree source_rel,
           pctree target_rel,
           pctree targetObj,
           pctree targetObj2
           WHERE
           source_rel.childid = keyRef.linkto AND
           target_rel.childid = keyRef.linkfrom AND 
           keyRef.linktorole = 'LogicalObject' AND
           keyRef.linkfromrole = 'PhysicalConfigs' AND
           targetObj.childid = target_rel.parentid AND
           targetObj2.childid = targetObj.parentid
           UNION -- for relational dependencies using CMPDependency
           SELECT
           NULL AS source_comp_id,
           srcItem.linkto AS source_entity_id,
           NULL AS source_param_id,
           srcItem.linkto AS source_item_id,
           NULL AS target_param_id,
           tgtItem.parentid AS target_item_id,
           NULL AS target_comp_id,
           tgtItem.parentid AS target_entity_id
           FROM
           interlink srcItem,
           pctree tgtItem,
           cmpdependency_v depFilter
           WHERE
           srcItem.linktorole = 'SourceFCO' AND
           srcItem.linkfromrole = 'Dependents' AND
           srcItem.linkfrom = tgtItem.childid AND
           tgtItem.parentrole = 'TargetFCO' AND
           tgtItem.childrole = 'Dependencies' AND
           srcItem.linkfrom = depFilter.elementid AND
           depFilter.type = 'RELATIONAL'
           UNION -- for all other relationships that are not in MCM tables(e.g. UDO)
           SELECT 
           source_comp_id,
           source_entity_id,
           source_param_id,
           source_item_id,
           target_param_id,
           target_item_id,
           target_comp_id,
           target_entity_id
           FROM
           dataflow_dependency_t
           UNION
           SELECT
           to_number(NULL) AS source_comp_id ,
           source_rel.parentid AS source_entity_id,
           to_number(NULL) AS source_param_id ,
           keyRef.linkto AS source_item_id,
           to_number(NULL) AS target_param_id ,
           keyRef.linkfrom AS target_item_id,
           to_number(NULL) AS target_comp_id ,
           target_rel.parentid AS target_entity_id
           FROM
           interlink keyRef,
           pctree source_rel,
           pctree target_rel
           WHERE
           source_rel.childid = keyRef.linkto AND
           target_rel.childid = keyRef.linkfrom AND 
           keyRef.linktorole <> 'ReferencingObject' AND
           keyRef.linktorole <> 'ParamOrArg' AND
           keyRef.linktorole <> 'UniqueKey' AND
           keyRef.linkfromrole <> 'ForeignKeys' AND
           keyRef.linktorole <> 'ExternalTableField' AND
           keyRef.linkfromrole <> 'ExternalTableItem' AND
           keyRef.linkfromrole <> 'ReferencingCubeDimRef' AND
           keyRef.linktorole <> 'ReferencedLevel' AND
           keyRef.linktorole <> 'BindingAttribute' AND
           keyRef.linktorole <> 'BindingDimensionKeyAttr' AND
           keyRef.linkfromrole <> 'IntelligenceItems' AND
           keyRef.linkfromrole <> 'ReportItems' AND
           keyRef.linktorole <> 'Dimension' AND
           keyRef.linkfromrole <> 'ReportItems' AND
           keyRef.linktorole <> 'Hierarchy' AND
           keyRef.linktorole <> 'InitialLevel' AND
           keyRef.linkfromrole <> 'ItemClassesUsingValues' AND
           keyRef.linkfromrole <> 'ItemClassesOrdered' AND
           keyRef.linkfromrole <> 'QueryItems' AND
           keyRef.linktorole <> 'ReferencedSCO' AND
           keyRef.linktorole <> 'ReferencedFunction' AND
           keyRef.linkfromrole <> 'DrillItems' AND
           keyRef.linktorole <> 'QueryItem' AND
           keyRef.linktorole <> 'DerivationSourceSCO' AND
           keyRef.linkfromrole <> 'CaptureReference' AND
           keyRef.linktorole <> 'ReferencedElement' AND             
           keyRef.linktorole <> 'SourceFCO' AND         
           keyRef.linkfromrole <> 'Dependents' AND
           keyRef.linkfromrole <> 'DerivationSets' AND
	   keyRef.linkfromrole <> 'ReferencingPrfTable' AND
           keyRef.linktorole <> 'ProfileTable' AND
           keyRef.linkfromrole <> 'ProfileAttribute' AND
	   keyRef.linktorole <> 'ProfileObject' AND
           keyRef.linktorole <> 'Tmp1Table' AND
           keyRef.linkfromrole <> 'Tmp1Usage' AND
           keyRef.linktorole <> 'Tmp2Table' AND
           keyRef.linkfromrole <> 'Tmp2Usage' AND
           keyRef.linktorole <> 'StageTable' AND
           keyRef.linkfromrole <> 'StageUsage' AND
           keyRef.linkfromrole <> 'SourceIND'  AND
           keyRef.linkfromrole <> 'ExternalTables' AND
           keyRef.linktorole <> 'DefaultLocation' AND
           keyRef.linktorole <> 'RuntimeLocation' AND
           keyRef.linktorole <> 'FolderReference' AND
           keyRef.linkfromrole <> 'FolderReference' AND
           keyRef.linktorole <> 'CopyOf' AND
           keyRef.linktorole <> 'LogicalObject' AND
           keyRef.linkfromrole <> 'PhysicalConfigs' AND
           keyRef.linktorole <> 'RemoteAgent' AND
           keyRef.linkfromrole <> 'Overridable' AND
           keyRef.linktorole <> 'Overridee' AND
           source_rel.childrole <> 'Datatype' 
           ;