1. Tables and Views for SCM
  2. ACD_CONCEPTS_RELATIONSHIP_V

ACD_CONCEPTS_RELATIONSHIP_V

Details

  • Schema: FUSION

  • Object owner: ACD

  • 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_IDEA_ID

DERIVED_PROPOSAL_ID

DERIVED_REQUIREMENT_LINE_ID

DERIVED_COMPONENT_ID

DERIVED_ITEM_ID

DERIVED_PROJECTS_ID

FINAL_DERIVED_ID

FINAL_DRIVING_ID

Query

SQL_Statement

SELECT ConceptRel1.RELATIONSHIP_ID,

ConceptRel1.OBJECT_VERSION_NUMBER,

ConceptRel1.RELATIONSHIP_TYPE,

ConceptRel1.TARGET_TYPE,

ConceptRel1.DIRECTION_TYPE,

ConceptRel1.SRC_OBJ_TYPE,

ConceptRel1.SRC_OBJ_ID,

ConceptRel1.DEST_OBJ_TYPE,

ConceptRel1.DEST_OBJ_ID,

ConceptRel1.CREATED_BY,

ConceptRel1.CREATION_DATE,

ConceptRel1.LAST_UPDATED_BY,

ConceptRel1.LAST_UPDATE_DATE,

ConceptRel1.DERIVED_REQUIREMENT_ID,

ConceptRel1.DERIVED_IDEA_ID,

ConceptRel1.DERIVED_PROPOSAL_ID,

ConceptRel1.DERIVED_REQUIREMENT_LINE_ID,

ConceptRel1.DERIVED_COMPONENT_ID,

ConceptRel1.DERIVED_ITEM_ID,

ConceptRel1.DERIVED_PROJECTS_ID,

CASE

WHEN ConceptRel2.derived_CONCEPT_ID IS NOT NULL

THEN ConceptRel2.derived_CONCEPT_ID

WHEN ConceptRel1.derived_CONCEPT_ID IS NOT NULL

THEN ConceptRel1.derived_CONCEPT_ID

END AS FINAL_DERIVED_ID,

CASE

WHEN ConceptRel2.DRIVING_CONCEPT_ID IS NOT NULL

THEN ConceptRel2.DRIVING_CONCEPT_ID

WHEN ConceptRel1.DRIVING_CONCEPT_ID IS NOT NULL

THEN ConceptRel1.DRIVING_CONCEPT_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= 'CONCEPT'

AND AR.dest_obj_type = 'REQUIREMENT')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'REQUIREMENT'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.src_obj_id

END AS DERIVED_REQUIREMENT_ID,

CASE

WHEN (AR.src_obj_type= 'CONCEPT'

AND AR.dest_obj_type = 'IDEA')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'IDEA'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.src_obj_id

END AS DERIVED_IDEA_ID,

CASE

WHEN (AR.src_obj_type= 'CONCEPT'

AND AR.dest_obj_type = 'PROPOSAL')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'PROPOSAL'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.src_obj_id

END AS DERIVED_PROPOSAL_ID,

CASE

WHEN (AR.src_obj_type= 'CONCEPT'

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

THEN AR.src_obj_id

END AS DERIVED_REQUIREMENT_LINE_ID,

CASE

WHEN (AR.src_obj_type= 'CONCEPT'

AND AR.dest_obj_type = 'COMPONENT')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'COMPONENT'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.src_obj_id

END AS DERIVED_COMPONENT_ID,

CASE

WHEN (AR.src_obj_type= 'CONCEPT'

AND AR.dest_obj_type = 'ITEM')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'ITEM'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.src_obj_id

END AS DERIVED_ITEM_ID,

CASE

WHEN (AR.src_obj_type= 'CONCEPT'

AND AR.dest_obj_type = 'Projects')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'Projects'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.src_obj_id

END AS DERIVED_PROJECTS_ID,

CASE

WHEN (AR.src_obj_type = 'CONCEPT'

AND AR.dest_obj_type != 'CONCEPT')

THEN AR.src_obj_id

WHEN (AR.src_obj_type != 'CONCEPT'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.dest_obj_id

END AS DRIVING_CONCEPT_ID,

CASE

WHEN (AR.src_obj_type = 'CONCEPT'

AND AR.dest_obj_type != 'CONCEPT')

THEN AR.src_obj_id

WHEN (AR.src_obj_type!= 'CONCEPT'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.dest_obj_id

END AS derived_CONCEPT_ID

FROM ACA_CS_RELATIONSHIP AR

WHERE SRC_OBJ_TYPE = 'CONCEPT'

OR DEST_OBJ_TYPE = 'CONCEPT'

) ConceptRel1,

(SELECT RELATIONSHIP_ID,

CASE

WHEN (AR.src_obj_type= 'CONCEPT'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.dest_obj_id

END AS derived_CONCEPT_ID,

CASE

WHEN (AR.src_obj_type= 'CONCEPT'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.src_obj_id

END AS DRIVING_CONCEPT_ID

FROM ACA_CS_RELATIONSHIP AR

WHERE SRC_OBJ_TYPE = 'CONCEPT'

OR DEST_OBJ_TYPE = 'CONCEPT'

UNION

SELECT RELATIONSHIP_ID,

CASE

WHEN (AR.src_obj_type= 'CONCEPT'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.src_obj_id

END AS derived_CONCEPT_ID,

CASE

WHEN (AR.src_obj_type= 'CONCEPT'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.dest_obj_id

END AS DRIVING_CONCEPT_ID

FROM ACA_CS_RELATIONSHIP AR

WHERE SRC_OBJ_TYPE = 'CONCEPT'

OR DEST_OBJ_TYPE = 'CONCEPT'

) ConceptRel2

WHERE ConceptRel1.RELATIONSHIP_ID = ConceptRel2.RELATIONSHIP_ID