1. Tables and Views for SCM
  2. ACN_IDEA_RELATIONSHIP_V

ACN_IDEA_RELATIONSHIP_V

Details

  • Schema: FUSION

  • Object owner: ACN

  • 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

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

DERIVED_REQUIREMENT_ID

DERIVED_CONCEPT_ID

DERIVED_PROPOSAL_ID

DERIVED_REQUIREMENT_LINE_ID

DERIVED_COMPONENT_ID

FINAL_DERIVED_ID

FINAL_DRIVING_ID

Query

SQL_Statement

SELECT IdeaRel1.RELATIONSHIP_ID, IdeaRel1.OBJECT_VERSION_NUMBER,

IdeaRel1.RELATIONSHIP_TYPE, IdeaRel1.TARGET_TYPE, IdeaRel1.DIRECTION_TYPE,

IdeaRel1.SRC_OBJ_TYPE, IdeaRel1.SRC_OBJ_ID, IdeaRel1.DEST_OBJ_TYPE,

IdeaRel1.DEST_OBJ_ID, IdeaRel1.CREATED_BY, IdeaRel1.CREATION_DATE,

IdeaRel1.LAST_UPDATED_BY, IdeaRel1.LAST_UPDATE_DATE, IdeaRel1.DERIVED_REQUIREMENT_ID,

IdeaRel1.DERIVED_CONCEPT_ID, IdeaRel1.DERIVED_PROPOSAL_ID, IdeaRel1.DERIVED_REQUIREMENT_LINE_ID,

IdeaRel1.DERIVED_COMPONENT_ID,

Case when IdeaRel2.derived_IDEA_ID is not null then IdeaRel2.derived_IDEA_ID

When IdeaRel1.derived_IDEA_ID is not null then IdeaRel1.derived_IDEA_ID

End as FINAL_DERIVED_ID,

Case when IdeaRel2.DRIVING_IDEA_ID is not null then IdeaRel2.DRIVING_IDEA_ID

When IdeaRel1.DRIVING_IDEA_ID is not null then IdeaRel1.DRIVING_IDEA_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, CREATED_BY, CREATION_DATE,

LAST_UPDATED_BY, LAST_UPDATE_DATE,

CASE

WHEN (AR.src_obj_type= 'IDEA'

AND AR.dest_obj_type = 'REQUIREMENT')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'REQUIREMENT'

AND AR.dest_obj_type = 'IDEA')

THEN AR.src_obj_id

END AS DERIVED_REQUIREMENT_ID,

CASE

WHEN (AR.src_obj_type= 'IDEA'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'CONCEPT'

AND AR.dest_obj_type = 'IDEA')

THEN AR.src_obj_id

END AS DERIVED_CONCEPT_ID,

CASE

WHEN (AR.src_obj_type= 'IDEA'

AND AR.dest_obj_type = 'PROPOSAL')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'PROPOSAL'

AND AR.dest_obj_type = 'IDEA')

THEN AR.src_obj_id

END AS DERIVED_PROPOSAL_ID,

CASE

WHEN (AR.src_obj_type= 'IDEA'

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 = 'IDEA')

THEN AR.src_obj_id

END AS DERIVED_REQUIREMENT_LINE_ID,

CASE

WHEN (AR.src_obj_type= 'IDEA'

AND AR.dest_obj_type = 'COMPONENT')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'COMPONET'

AND AR.dest_obj_type = 'IDEA')

THEN AR.src_obj_id

END AS DERIVED_COMPONENT_ID,

CASE

WHEN (AR.src_obj_type = 'IDEA'

AND AR.dest_obj_type != 'IDEA')

THEN AR.src_obj_id

WHEN (AR.src_obj_type != 'IDEA'

AND AR.dest_obj_type = 'IDEA')

THEN AR.dest_obj_id

END AS DRIVING_IDEA_ID,

CASE

WHEN (AR.src_obj_type = 'IDEA'

AND AR.dest_obj_type != 'IDEA')

THEN AR.src_obj_id

WHEN (AR.src_obj_type!= 'IDEA'

AND AR.dest_obj_type = 'IDEA')

THEN AR.dest_obj_id

END AS derived_IDEA_ID

FROM ACA_CS_RELATIONSHIP AR

WHERE SRC_OBJ_TYPE = 'IDEA'

OR DEST_OBJ_TYPE = 'IDEA'

) IdeaRel1,

(SELECT RELATIONSHIP_ID,

CASE

WHEN (AR.src_obj_type= 'IDEA'

AND AR.dest_obj_type = 'IDEA')

THEN AR.dest_obj_id

END AS derived_IDEA_ID,

CASE

WHEN (AR.src_obj_type= 'IDEA'

AND AR.dest_obj_type = 'IDEA')

THEN AR.src_obj_id

END AS DRIVING_IDEA_ID

FROM ACA_CS_RELATIONSHIP AR

WHERE SRC_OBJ_TYPE = 'IDEA'

OR DEST_OBJ_TYPE = 'IDEA'

UNION

SELECT RELATIONSHIP_ID,

CASE

WHEN (AR.src_obj_type= 'IDEA'

AND AR.dest_obj_type = 'IDEA')

THEN AR.src_obj_id

END AS derived_IDEA_ID,

CASE

WHEN (AR.src_obj_type= 'IDEA'

AND AR.dest_obj_type = 'IDEA')

THEN AR.dest_obj_id

END AS DRIVING_IDEA_ID

FROM ACA_CS_RELATIONSHIP AR

WHERE SRC_OBJ_TYPE = 'IDEA'

OR DEST_OBJ_TYPE = 'IDEA'

) IdeaRel2

WHERE IdeaRel1.RELATIONSHIP_ID = IdeaRel2.RELATIONSHIP_ID