1. Tables and Views for SCM
  2. ENQ_ACTION_RELATIONSHIP_V

ENQ_ACTION_RELATIONSHIP_V

Details

  • Schema: FUSION

  • Object owner: ENQ

  • Object type: VIEW

Columns

Name

RELATIONSHIP_ID

OBJECT_VERSION_NUMBER

RELATIONSHIP_TYPE

TARGET_TYPE

DIRECTION_TYPE

SRC_OBJ_TYPE

SRC_OBJ_ID

DEST_OBJ_TYPE

DEST_OBJ_ID

SRC_OBJ_PK2VALUE

DEST_OBJ_PK2VALUE

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

DERIVED_IDEA_ID

DERIVED_CONCEPT_ID

DERIVED_PROPOSAL_ID

DERIVED_REQUIREMENT_LINE_ID

DERIVED_COMPONENT_ID

DERIVED_ITEM_ID

DERIVED_ITEM_ID1

DERIVED_CHANGE_ID

DERIVED_ACTION_ID

DERIVED_REQUIREMENT_ID

DERIVED_ISSUE_ID

FINAL_DERIVED_ID

FINAL_DRIVING_ID

Query

SQL_Statement

SELECT ActionRel1.RELATIONSHIP_ID,

ActionRel1.OBJECT_VERSION_NUMBER,

ActionRel1.RELATIONSHIP_TYPE,

ActionRel1.TARGET_TYPE,

ActionRel1.DIRECTION_TYPE,

ActionRel1.SRC_OBJ_TYPE,

ActionRel1.SRC_OBJ_ID,

ActionRel1.DEST_OBJ_TYPE,

ActionRel1.DEST_OBJ_ID,

ActionRel1.src_obj_pk2value,

ActionRel1.dest_obj_pk2value,

ActionRel1.CREATED_BY,

ActionRel1.CREATION_DATE,

ActionRel1.LAST_UPDATED_BY,

ActionRel1.LAST_UPDATE_DATE,

ActionRel1.DERIVED_IDEA_ID,

ActionRel1.DERIVED_CONCEPT_ID,

ActionRel1.DERIVED_PROPOSAL_ID,

ActionRel1.DERIVED_REQUIREMENT_LINE_ID,

ActionRel1.DERIVED_COMPONENT_ID,

ActionRel1.DERIVED_ITEM_ID,

ActionRel1.DERIVED_ITEM_ID1,

ActionRel1.DERIVED_CHANGE_ID,

ActionRel1.DERIVED_ACTION_ID,

ActionRel1.DERIVED_REQUIREMENT_ID,

ActionRel1.DERIVED_ISSUE_ID,

CASE

WHEN ActionRel2.DERIVED_ACTION_ID IS NOT NULL

THEN ActionRel2.DERIVED_ACTION_ID

WHEN ActionRel1.DERIVED_ACTION_ID IS NOT NULL

THEN ActionRel1.DERIVED_ACTION_ID

END AS FINAL_DERIVED_ID,

CASE

WHEN ActionRel2.DRIVING_ACTION_ID IS NOT NULL

THEN ActionRel2.DRIVING_ACTION_ID

WHEN ActionRel1.DRIVING_ACTION_ID IS NOT NULL

THEN ActionRel1.DRIVING_ACTION_ID

END AS FINAL_DRIVING_ID

FROM

(SELECT RELATIONSHIP_ID,

OBJECT_VERSION_NUMBER,

RELATIONSHIP_TYPE,

TARGET_TYPE,

DIRECTION_TYPE,

SRC_OBJ_TYPE,

SRC_OBJ_ID,

DEST_OBJ_TYPE,

DEST_OBJ_ID,

src_obj_pk2value,

dest_obj_pk2value,

CREATED_BY,

CREATION_DATE,

LAST_UPDATED_BY,

LAST_UPDATE_DATE,

CASE

WHEN (AR.src_obj_type= 'ITEM'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.src_obj_id

WHEN (AR.src_obj_type= 'ORA_ACTION'

AND AR.dest_obj_type = 'ITEM')

THEN AR.dest_obj_id

END AS DERIVED_ITEM_ID,

CASE

WHEN (AR.src_obj_type= 'ITEM'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.src_obj_pk2value

WHEN (AR.src_obj_type= 'ORA_ACTION'

AND AR.dest_obj_type = 'ITEM')

THEN AR.dest_obj_pk2value

END AS DERIVED_ITEM_ID1,

CASE

WHEN (AR.src_obj_type= 'CHANGE'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.src_obj_id

WHEN (AR.src_obj_type= 'ORA_ACTION'

AND AR.dest_obj_type = 'CHANGE')

THEN AR.dest_obj_id

END AS DERIVED_CHANGE_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ISSUE'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.src_obj_id

WHEN (AR.src_obj_type= 'ORA_ACTION'

AND AR.dest_obj_type = 'ORA_ISSUE')

THEN AR.dest_obj_id

END AS DERIVED_ISSUE_ID,

CASE

WHEN (AR.src_obj_type= 'REQUIREMENT'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.src_obj_id

WHEN (AR.src_obj_type= 'ORA_ACTION'

AND AR.dest_obj_type = 'REQUIREMENT')

THEN AR.dest_obj_id

END AS DERIVED_REQUIREMENT_ID,

CASE

WHEN (AR.src_obj_type= 'IDEA'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.src_obj_id

WHEN (AR.src_obj_type= 'ORA_ACTION'

AND AR.dest_obj_type = 'IDEA')

THEN AR.dest_obj_id

END AS DERIVED_IDEA_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ACTION'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'CONCEPT'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.src_obj_id

END AS DERIVED_CONCEPT_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ACTION'

AND AR.dest_obj_type = 'PROPOSAL')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'PROPOSAL'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.src_obj_id

END AS DERIVED_PROPOSAL_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ACTION'

AND AR.dest_obj_type = 'REQUIREMENT_LINE_ITEM')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'REQUIREMENT_LINE_ITEM'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.src_obj_id

END AS DERIVED_REQUIREMENT_LINE_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ACTION'

AND AR.dest_obj_type = 'COMPONENT')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'COMPONENT'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.src_obj_id

END AS DERIVED_COMPONENT_ID,

CASE

WHEN (AR.src_obj_type = 'ORA_ACTION'

AND AR.dest_obj_type != 'ORA_ACTION')

THEN AR.src_obj_id

WHEN (AR.src_obj_type != 'ORA_ACTION'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.dest_obj_id

END AS DRIVING_ACTION_ID,

CASE

WHEN (AR.src_obj_type != 'ORA_ACTION'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.src_obj_id

WHEN (AR.src_obj_type = 'ORA_ACTION'

AND AR.dest_obj_type != 'ORA_ACTION')

THEN AR.dest_obj_id

END AS DERIVED_ACTION_ID

FROM fusion.ACA_CS_RELATIONSHIP AR

WHERE (src_obj_type = 'ORA_ACTION'

OR dest_obj_type = 'ORA_ACTION')

) ActionRel1,

(SELECT RELATIONSHIP_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ACTION'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.dest_obj_id

END AS DERIVED_ACTION_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ACTION'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.src_obj_id

END AS DRIVING_ACTION_ID

FROM fusion.ACA_CS_RELATIONSHIP AR

WHERE SRC_OBJ_TYPE = 'ORA_ACTION'

OR DEST_OBJ_TYPE = 'ORA_ACTION'

UNION

SELECT RELATIONSHIP_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ACTION'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.src_obj_id

END AS DERIVED_ACTION_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ACTION'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.dest_obj_id

END AS DRIVING_ACTION_ID

FROM fusion.ACA_CS_RELATIONSHIP AR

WHERE SRC_OBJ_TYPE = 'ORA_ACTION'

OR DEST_OBJ_TYPE = 'ORA_ACTION'

) ActionRel2

WHERE ActionRel1.RELATIONSHIP_ID = ActionRel2.RELATIONSHIP_ID