1. Tables and Views for SCM
  2. ACN_REQUIREMENT_RELATIONSHIP_V

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

DERIVED_CONCEPT_ID

DERIVED_PROPOSAL_ID

DERIVED_REQUIREMENT_LINE_ID

DERIVED_COMPONENT_ID

FINAL_DERIVED_ID

FINAL_DRIVING_ID

Query

SQL_Statement

SELECT

Req_Relations.RELATIONSHIP_ID, Req_Relations.OBJECT_VERSION_NUMBER,

Req_Relations.RELATIONSHIP_TYPE, Req_Relations.TARGET_TYPE, Req_Relations.DIRECTION_TYPE,

Req_Relations.SRC_OBJ_TYPE, Req_Relations.SRC_OBJ_ID, Req_Relations.DEST_OBJ_TYPE,

Req_Relations.DEST_OBJ_ID, Req_Relations.CREATED_BY, Req_Relations.CREATION_DATE,

Req_Relations.LAST_UPDATED_BY, Req_Relations.LAST_UPDATE_DATE, Req_Relations.DERIVED_IDEA_ID,

Req_Relations.DERIVED_CONCEPT_ID, Req_Relations.DERIVED_PROPOSAL_ID,

Req_Relations.DERIVED_REQUIREMENT_LINE_ID, Req_Relations.DERIVED_COMPONENT_ID,

Req_Relations.FINAL_DERIVED_ID , Req_Relations.FINAL_DRIVING_ID

FROM

(SELECT ReqRel1.RELATIONSHIP_ID, ReqRel1.OBJECT_VERSION_NUMBER, ReqRel1.RELATIONSHIP_TYPE,

ReqRel1.TARGET_TYPE, ReqRel1.DIRECTION_TYPE, ReqRel1.SRC_OBJ_TYPE, ReqRel1.SRC_OBJ_ID,

ReqRel1.DEST_OBJ_TYPE, ReqRel1.DEST_OBJ_ID, ReqRel1.CREATED_BY, ReqRel1.CREATION_DATE,

ReqRel1.LAST_UPDATED_BY, ReqRel1.LAST_UPDATE_DATE, ReqRel1.DERIVED_IDEA_ID,

ReqRel1.DERIVED_CONCEPT_ID, ReqRel1.DERIVED_PROPOSAL_ID,

ReqRel1.DERIVED_REQUIREMENT_LINE_ID, ReqRel1.DERIVED_COMPONENT_ID,

CASE

WHEN ReqRel2.derived_REQUIREMENT_ID IS NOT NULL

THEN ReqRel2.derived_REQUIREMENT_ID

WHEN ReqRel1.derived_REQUIREMENT_ID IS NOT NULL

THEN ReqRel1.derived_REQUIREMENT_ID

END AS FINAL_DERIVED_ID,

CASE

WHEN ReqRel2.DRIVING_REQUIREMENT_ID IS NOT NULL

THEN ReqRel2.DRIVING_REQUIREMENT_ID

WHEN ReqRel1.DRIVING_REQUIREMENT_ID IS NOT NULL

THEN ReqRel1.DRIVING_REQUIREMENT_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.src_obj_id

WHEN (AR.src_obj_type= 'REQUIREMENT'

AND AR.dest_obj_type = 'IDEA')

THEN AR.dest_obj_id

END AS DERIVED_IDEA_ID,

CASE

WHEN (AR.src_obj_type= 'REQUIREMENT'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'CONCEPT'

AND AR.dest_obj_type = 'REQUIREMENT')

THEN AR.src_obj_id

END AS DERIVED_CONCEPT_ID,

CASE

WHEN (AR.src_obj_type= 'REQUIREMENT'

AND AR.dest_obj_type = 'PROPOSAL')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'PROPOSAL'

AND AR.dest_obj_type = 'REQUIREMENT')

THEN AR.src_obj_id

END AS DERIVED_PROPOSAL_ID,

CASE

WHEN (AR.src_obj_type= 'REQUIREMENT'

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

THEN AR.src_obj_id

END AS DERIVED_REQUIREMENT_LINE_ID,

CASE

WHEN (AR.src_obj_type= 'REQUIREMENT'

AND AR.dest_obj_type = 'COMPONENT')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'COMPONET'

AND AR.dest_obj_type = 'REQUIREMENT')

THEN AR.src_obj_id

END AS DERIVED_COMPONENT_ID,

CASE

WHEN (AR.src_obj_type = 'REQUIREMENT'

AND AR.dest_obj_type != 'REQUIREMENT')

THEN AR.src_obj_id

WHEN (AR.src_obj_type != 'REQUIREMENT'

AND AR.dest_obj_type = 'REQUIREMENT')

THEN AR.dest_obj_id

END AS DRIVING_REQUIREMENT_ID,

CASE

WHEN (AR.src_obj_type = 'REQUIREMENT'

AND AR.dest_obj_type != 'REQUIREMENT')

THEN AR.src_obj_id

WHEN (AR.src_obj_type!= 'REQUIREMENT'

AND AR.dest_obj_type = 'REQUIREMENT')

THEN AR.dest_obj_id

END AS derived_REQUIREMENT_ID

FROM ACA_CS_RELATIONSHIP AR

WHERE (src_obj_type = 'REQUIREMENT'

OR dest_obj_type = 'REQUIREMENT')

) ReqRel1,

(SELECT RELATIONSHIP_ID,

CASE

WHEN (AR.src_obj_type= 'REQUIREMENT'

AND AR.dest_obj_type = 'REQUIREMENT')

THEN AR.dest_obj_id

END AS derived_REQUIREMENT_ID,

CASE

WHEN (AR.src_obj_type= 'REQUIREMENT'

AND AR.dest_obj_type = 'REQUIREMENT')

THEN AR.src_obj_id

END AS DRIVING_REQUIREMENT_ID

FROM ACA_CS_RELATIONSHIP AR

WHERE SRC_OBJ_TYPE = 'REQUIREMENT'

OR DEST_OBJ_TYPE = 'REQUIREMENT'

UNION

SELECT RELATIONSHIP_ID,

CASE

WHEN (AR.src_obj_type= 'REQUIREMENT'

AND AR.dest_obj_type = 'REQUIREMENT')

THEN AR.src_obj_id

END AS derived_REQUIREMENT_ID,

CASE

WHEN (AR.src_obj_type= 'REQUIREMENT'

AND AR.dest_obj_type = 'REQUIREMENT')

THEN AR.dest_obj_id

END AS DRIVING_REQUIREMENT_ID

FROM ACA_CS_RELATIONSHIP AR

WHERE SRC_OBJ_TYPE = 'REQUIREMENT'

OR DEST_OBJ_TYPE = 'REQUIREMENT'

) ReqRel2

WHERE ReqRel1.RELATIONSHIP_ID = ReqRel2.RELATIONSHIP_ID ) Req_Relations,

(SELECT requirement_id,

MAX(version_number)

FROM ACN_REQ_VERSION_B

GROUP BY requirement_id

) ReqLast

WHERE Req_Relations.FINAL_DRIVING_ID = ReqLast.REQUIREMENT_ID(+)