create or replace view deployment_dependency_v as
select
   map_operator.parentid as source_objID,
   entity_stage.linkTo as target_objID
from
   pctree map_operator,
   interlink entity_stage
where
   map_operator.parentrole = 'OperatorOwner' and
   entity_stage.linkfrom   = map_operator.childid and
   entity_stage.linkto     > 0
union
select
 miningmodel.parentid as source_objID,
 entity_stage.linkTo as target_objID
from
 pctree miningmodel,
 pctree embedmap,
 interlink entity_stage
where
 miningmodel.parentrole = 'EmbedMapOwner'
 and embedmap.parentrole = 'OperatorOwner'
 and miningmodel.childid = embedmap.parentid
 and entity_stage.linkfrom = embedmap.childid
 and entity_stage.linkto > 0
union
select
   view_operator.parentid as source_objID,
   entity_stage.linkTo as target_objID
from
   pctree view_operator,
   interlink entity_stage
where
   view_operator.parentrole = 'InstalledModule' and
   entity_stage.linkfrom    = view_operator.childid and
   entity_stage.linkto      > 0
union
select
    changesets.parentid as source_objID,
    capturedentity.linkto as target_objID
from
    pctree changesets,
    pctree capturerelation,
    interlink capturedentity
where
    capturerelation.parentrole = 'CaptureSetup' and
    capturedentity.linkfrom = capturerelation.childid and
    changesets.parentrole = 'ChangeLog' and
    changesets.childid    = capturerelation.parentid and
    capturedentity.linkto > 0
union
select
   activity.parentid as source_objID,
   activity_ref.linkTo as target_objID
from
   pctree activity,
   interlink activity_ref
where
   activity.parentrole   = 'OwningBaseProcess' and
   activity_ref.linkfrom = activity.childid and
   activity_ref.linkto   > 0
union
select
   ba_shortcuts.parentid as source_objID,
   shortcut_ref.linkto   as target_objID
from
   pctree ba_shortcuts,
   interlink shortcut_ref
where
   ba_shortcuts.parentrole = 'BusinessArea' and
   ba_shortcuts.childid    = shortcut_ref.linkfrom and
   shortcut_ref.linkto     > 0
union
select
   dependencies.targetfco as source_objID,
   dependencies.sourcefco as target_objID
from
   cmpdependency_v dependencies
where
   dependencies.type = 'BI'
union
select
   dependencies.sourcefco as source_objID,
   drill_path.elementid as target_objID
from
   cmpdependency_v dependencies,
   cmpdrillpath_v drill_path
where
   dependencies.targetfco = drill_path.elementid and
   dependencies.type = 'BI'
union
select
   itemclass.elementid as source_objID,
   dependencies.targetfco as target_objID
from
   cmpdependency_v dependencies,
   cmplovitemclass_v itemclass
where
   dependencies.sourcefco  = itemclass.elementid and
   dependencies.type = 'BI' and
   itemclass.drilltodetail = 1
union   
--Queue Propagation to Queue
select childid as source_objID,
       parentid as target_objID
from pctree 
where parentrole ='AdvancedQueue'
and childrole = 'QueueSubscriber'
union
--Queue table to Queue
select linkfrom as source_objID,
       linkto as target_objID
from interlink 
where linktorole = 'QueueTable'
and linkfromrole = 'AdvancedQueue'
union
--Queue table to Object type...
select firstclassobject as target_objID,
       typedefinition as source_objID
from cmprelationaldbitem_v
where firstclassobject in (select linkto as target_objID
from interlink
where linktorole = 'QueueTable'
and linkfromrole = 'AdvancedQueue')
union
--Relation to objectType 
select item.owningrelation as target_objID,
       item.typeDefinition as source_objID      
from cmpitem_v item
where item.typeDefinition in (select elementid from cmpobjecttype_v 
                              union 
                              select elementid from cmpsqlcollection_v)
union
--SQLCollection to objectType 
select coll.elementid as target_objID,
       coll.typeDefinition as source_objID      
from cmpsqlcollection_v coll
where coll.typeDefinition in (select elementid from cmpobjecttype_v 
                              union 
                              select elementid from cmpsqlcollection_v)
union
--Relation to cmppsrecord 
select item.owningrelation as target_objID,
       pak.elementid as source_objID      
from cmpfunctioncategory_v pak, cmpitem_v item, cmpplsrecord_v rec
where pak.elementid = rec.functioncategory and  
      item.typeDefinition = rec.elementid
union
--Table to Table
select fkey.FIRSTCLASSOBJECT as source_objID,
       key.FIRSTCLASSOBJECT as target_objID      
from cmpuniquekey_v key, cmpforeignkey_v fkey
where fkey.uniquekey = key.elementid
-- for dimension and cube
UNION 
SELECT
dim_level.parentid AS source_objID,
cube_dimref.parentid AS target_objID
FROM
interlink level_dimref,
pctree dim_level,
pctree cube_dimref
WHERE
level_dimref.linkfromrole = 'ReferencingCubeDimRef' AND
level_dimref.linktorole = 'ReferencedLevel' AND
cube_dimref.parentrole = 'OwningDimension' AND
cube_dimref.childid = level_dimref.linkto AND
dim_level.parentrole = 'OwningCube' AND
dim_level.childid = level_dimref.linkfrom
-- for implementation table and cube
UNION 
SELECT
target_table.parentid AS source_objID,
source_cube.parentid AS target_objID
FROM
interlink column_measure,
pctree source_cube,
pctree target_table
WHERE
source_cube.parentrole = 'OwningRelation' AND
source_cube.childid = column_measure.linkto AND
column_measure.linktorole = 'BindingAttribute' AND
target_table.childid = column_measure.linkfrom
---- for implementation table and dimension
UNION 
SELECT
source_dim.parentid AS source_objID,
target_table.parentid AS target_objID
FROM
interlink column_lvattr,
pctree source_dim,
pctree target_table,
pctree dim_level
WHERE
(column_lvattr.linktorole = 'BindingAttribute' OR
column_lvattr.linktorole = 'BindingDimensionKeyAttr') AND
dim_level.childid = column_lvattr.linkfrom AND
dim_level.parentrole = 'OwningLevel' AND
target_table.childid = column_lvattr.linkto AND
source_dim.parentrole = 'OwningDimension' AND
dim_level.parentid = source_dim.childid
UNION
---- for implementation sequence and dimension
SELECT
sequence.linkfrom AS source_objID,
source_dim.childid AS target_objID
FROM
interlink sequence,
pctree source_dim
WHERE
source_dim.childid = sequence.linkto AND
sequence.linktorole = 'DimensionKeySequence'
with read only;