1. Tables and Views for SCM
  2. ENQ_ISSUE_RELATIONSHIP_V

ENQ_ISSUE_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

FINAL_DERIVED_ID

FINAL_DRIVING_ID

Query

SQL_Statement

SELECT IssueRel1.RELATIONSHIP_ID,

IssueRel1.OBJECT_VERSION_NUMBER,

IssueRel1.RELATIONSHIP_TYPE,

IssueRel1.TARGET_TYPE,

IssueRel1.DIRECTION_TYPE,

IssueRel1.SRC_OBJ_TYPE,

IssueRel1.SRC_OBJ_ID,

IssueRel1.DEST_OBJ_TYPE,

IssueRel1.DEST_OBJ_ID,

IssueRel1.src_obj_pk2value,

IssueRel1.dest_obj_pk2value,

IssueRel1.CREATED_BY,

IssueRel1.CREATION_DATE,

IssueRel1.LAST_UPDATED_BY,

IssueRel1.LAST_UPDATE_DATE,

IssueRel1.DERIVED_IDEA_ID,

IssueRel1.DERIVED_CONCEPT_ID,

IssueRel1.DERIVED_PROPOSAL_ID,

IssueRel1.DERIVED_REQUIREMENT_LINE_ID,

IssueRel1.DERIVED_COMPONENT_ID,

IssueRel1.DERIVED_ITEM_ID,

IssueRel1.DERIVED_ITEM_ID1,

IssueRel1.DERIVED_CHANGE_ID,

IssueRel1.DERIVED_ACTION_ID,

IssueRel1.DERIVED_REQUIREMENT_ID,

CASE

WHEN IssueRel2.derived_ISSUE_ID IS NOT NULL

THEN IssueRel2.derived_ISSUE_ID

WHEN IssueRel1.derived_ISSUE_ID IS NOT NULL

THEN IssueRel1.derived_ISSUE_ID

END AS FINAL_DERIVED_ID,

CASE

WHEN IssueRel2.DRIVING_ISSUE_ID IS NOT NULL

THEN IssueRel2.DRIVING_ISSUE_ID

WHEN IssueRel1.DRIVING_ISSUE_ID IS NOT NULL

THEN IssueRel1.DRIVING_ISSUE_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_ISSUE')

THEN AR.src_obj_id

WHEN (AR.src_obj_type= 'ORA_ISSUE'

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_ISSUE')

THEN AR.src_obj_pk2value

WHEN (AR.src_obj_type= 'ORA_ISSUE'

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_ISSUE')

THEN AR.src_obj_id

WHEN (AR.src_obj_type= 'ORA_ISSUE'

AND AR.dest_obj_type = 'CHANGE')

THEN AR.dest_obj_id

END AS DERIVED_CHANGE_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ACTION'

AND AR.dest_obj_type = 'ORA_ISSUE')

THEN AR.src_obj_id

WHEN (AR.src_obj_type= 'ORA_ISSUE'

AND AR.dest_obj_type = 'ORA_ACTION')

THEN AR.dest_obj_id

END AS DERIVED_ACTION_ID,

CASE

WHEN (AR.src_obj_type= 'REQUIREMENT'

AND AR.dest_obj_type = 'ORA_ISSUE')

THEN AR.src_obj_id

WHEN (AR.src_obj_type= 'ORA_ISSUE'

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_ISSUE')

THEN AR.src_obj_id

WHEN (AR.src_obj_type= 'ORA_ISSUE'

AND AR.dest_obj_type = 'IDEA')

THEN AR.dest_obj_id

END AS DERIVED_IDEA_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ISSUE'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'CONCEPT'

AND AR.dest_obj_type = 'ORA_ISSUE')

THEN AR.src_obj_id

END AS DERIVED_CONCEPT_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ISSUE'

AND AR.dest_obj_type = 'PROPOSAL')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'PROPOSAL'

AND AR.dest_obj_type = 'ORA_ISSUE')

THEN AR.src_obj_id

END AS DERIVED_PROPOSAL_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ISSUE'

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_ISSUE')

THEN AR.src_obj_id

END AS DERIVED_REQUIREMENT_LINE_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ISSUE'

AND AR.dest_obj_type = 'COMPONENT')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'COMPONENT'

AND AR.dest_obj_type = 'ORA_ISSUE')

THEN AR.src_obj_id

END AS DERIVED_COMPONENT_ID,

CASE

WHEN (AR.src_obj_type = 'ORA_ISSUE'

AND AR.dest_obj_type != 'ORA_ISSUE')

THEN AR.src_obj_id

WHEN (AR.src_obj_type != 'ORA_ISSUE'

AND AR.dest_obj_type = 'ORA_ISSUE')

THEN AR.dest_obj_id

END AS DRIVING_ISSUE_ID,

CASE

WHEN (AR.src_obj_type != 'ORA_ISSUE'

AND AR.dest_obj_type = 'ORA_ISSUE')

THEN AR.src_obj_id

WHEN (AR.src_obj_type = 'ORA_ISSUE'

AND AR.dest_obj_type != 'ORA_ISSUE')

THEN AR.dest_obj_id

END AS derived_Issue_ID

FROM fusion.ACA_CS_RELATIONSHIP AR

WHERE (src_obj_type = 'ORA_ISSUE'

OR dest_obj_type = 'ORA_ISSUE')

) IssueRel1,

(SELECT RELATIONSHIP_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ISSUE'

AND AR.dest_obj_type = 'ORA_ISSUE')

THEN AR.dest_obj_id

END AS derived_ISSUE_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ISSUE'

AND AR.dest_obj_type = 'ORA_ISSUE')

THEN AR.src_obj_id

END AS DRIVING_ISSUE_ID

FROM fusion.ACA_CS_RELATIONSHIP AR

WHERE SRC_OBJ_TYPE = 'ORA_ISSUE'

OR DEST_OBJ_TYPE = 'ORA_ISSUE'

UNION

SELECT RELATIONSHIP_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ISSUE'

AND AR.dest_obj_type = 'ORA_ISSUE')

THEN AR.src_obj_id

END AS derived_ISSUE_ID,

CASE

WHEN (AR.src_obj_type= 'ORA_ISSUE'

AND AR.dest_obj_type = 'ORA_ISSUE')

THEN AR.dest_obj_id

END AS DRIVING_ISSUE_ID

FROM fusion.ACA_CS_RELATIONSHIP AR

WHERE SRC_OBJ_TYPE = 'ORA_ISSUE'

OR DEST_OBJ_TYPE = 'ORA_ISSUE'

) IssueRel2

WHERE IssueRel1.RELATIONSHIP_ID = IssueRel2.RELATIONSHIP_ID