ACD_PROP_RELATIONSHIP

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_CONCEPT_ID DERIVED_IDEA_ID DERIVED_REQUIREMENT_LINE_ID DERIVED_COMPONENT_ID DERIVED_PROJECT_ID DERIVED_TASK_ID FINAL_DERIVED_ID FINAL_DRIVING_ID

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_IDEA_ID

DERIVED_REQUIREMENT_LINE_ID

DERIVED_COMPONENT_ID

DERIVED_PROJECT_ID

DERIVED_TASK_ID

FINAL_DERIVED_ID

FINAL_DRIVING_ID

Query

SQL_Statement
SELECT PropRel1.RELATIONSHIP_ID, PropRel1.OBJECT_VERSION_NUMBER, PropRel1.RELATIONSHIP_TYPE, PropRel1.TARGET_TYPE, PropRel1.DIRECTION_TYPE, PropRel1.SRC_OBJ_TYPE, PropRel1.SRC_OBJ_ID, PropRel1.DEST_OBJ_TYPE, PropRel1.DEST_OBJ_ID, PropRel1.CREATED_BY, PropRel1.CREATION_DATE, PropRel1.LAST_UPDATED_BY, PropRel1.LAST_UPDATE_DATE, PropRel1.DERIVED_REQUIREMENT_ID, PropRel1.DERIVED_CONCEPT_ID, PropRel1.DERIVED_IDEA_ID, PropRel1.DERIVED_REQUIREMENT_LINE_ID, PropRel1.DERIVED_COMPONENT_ID, NULL AS DERIVED_PROJECT_ID, NULL AS DERIVED_TASK_ID, CASE WHEN PropRel2.derived_PROP_ID IS NOT NULL THEN PropRel2.derived_PROP_ID WHEN PropRel1.derived_PROP_ID IS NOT NULL THEN PropRel1.derived_PROP_ID END AS FINAL_DERIVED_ID, CASE WHEN PropRel2.DRIVING_PROP_ID IS NOT NULL THEN PropRel2.DRIVING_PROP_ID WHEN PropRel1.DRIVING_PROP_ID IS NOT NULL THEN PropRel1.DRIVING_PROP_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= 'PROPOSAL' AND AR.dest_obj_type = 'REQUIREMENT') THEN AR.dest_obj_id WHEN (AR.src_obj_type= 'REQUIREMENT' AND AR.dest_obj_type = 'PROPOSAL') THEN AR.src_obj_id END AS DERIVED_REQUIREMENT_ID, CASE WHEN (AR.src_obj_type= 'PROPOSAL' AND AR.dest_obj_type = 'CONCEPT') THEN AR.dest_obj_id WHEN (AR.src_obj_type= 'CONCEPT' AND AR.dest_obj_type = 'PROPOSAL') THEN AR.src_obj_id END AS DERIVED_CONCEPT_ID, CASE WHEN (AR.src_obj_type= 'PROPOSAL' AND AR.dest_obj_type = 'IDEA') THEN AR.dest_obj_id WHEN (AR.src_obj_type= 'IDEA' AND AR.dest_obj_type = 'PROPOSAL') THEN AR.src_obj_id END AS DERIVED_IDEA_ID, CASE WHEN (AR.src_obj_type= 'PROPOSAL' 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 = 'PROPOSAL') THEN AR.src_obj_id END AS DERIVED_REQUIREMENT_LINE_ID, CASE WHEN (AR.src_obj_type= 'PROPOSAL' AND AR.dest_obj_type = 'COMPONENT') THEN AR.dest_obj_id WHEN (AR.src_obj_type= 'COMPONENT' AND AR.dest_obj_type = 'PROPOSAL') THEN AR.src_obj_id END AS DERIVED_COMPONENT_ID, CASE WHEN (AR.src_obj_type = 'PROPOSAL' AND AR.dest_obj_type != 'PROPOSAL') THEN AR.src_obj_id WHEN (AR.src_obj_type != 'PROPOSAL' AND AR.dest_obj_type = 'PROPOSAL') THEN AR.dest_obj_id END AS DRIVING_PROP_ID, CASE WHEN (AR.src_obj_type = 'PROPOSAL' AND AR.dest_obj_type != 'PROPOSAL') THEN AR.src_obj_id WHEN (AR.src_obj_type!= 'PROPOSAL' AND AR.dest_obj_type = 'PROPOSAL') THEN AR.dest_obj_id END AS derived_PROP_ID FROM ACA_CS_RELATIONSHIP AR WHERE SRC_OBJ_TYPE = 'PROPOSAL' OR DEST_OBJ_TYPE = 'PROPOSAL' ) PropRel1, (SELECT RELATIONSHIP_ID, CASE WHEN (AR.src_obj_type= 'PROPOSAL' AND AR.dest_obj_type = 'PROPOSAL') THEN AR.dest_obj_id END AS derived_PROP_ID, CASE WHEN (AR.src_obj_type= 'PROPOSAL' AND AR.dest_obj_type = 'PROPOSAL') THEN AR.src_obj_id END AS DRIVING_PROP_ID FROM ACA_CS_RELATIONSHIP AR WHERE SRC_OBJ_TYPE = 'PROPOSAL' OR DEST_OBJ_TYPE = 'PROPOSAL' UNION SELECT RELATIONSHIP_ID, CASE WHEN (AR.src_obj_type= 'PROPOSAL' AND AR.dest_obj_type = 'PROPOSAL') THEN AR.src_obj_id END AS derived_PROP_ID, CASE WHEN (AR.src_obj_type= 'PROPOSAL' AND AR.dest_obj_type = 'PROPOSAL') THEN AR.dest_obj_id END AS DRIVING_PROP_ID FROM ACA_CS_RELATIONSHIP AR WHERE SRC_OBJ_TYPE = 'PROPOSAL' OR DEST_OBJ_TYPE = 'PROPOSAL' ) PropRel2 WHERE PropRel1.RELATIONSHIP_ID = PropRel2.RELATIONSHIP_ID UNION ALL SELECT poa.object_association_id AS relationship_id, poa.object_version_number AS object_version_number, NULL AS relationship_type, NULL AS target_type, NULL AS direction_type, poa.object_type AS src_obj_type, poa.object_id AS src_obj_id, poa.associated_object_type AS dest_obj_type, poa.associated_object_id AS dest_obj_id, poa.created_by AS created_by, poa.creation_date AS creation_date, poa.last_updated_by AS last_updated_by, poa.last_update_date AS last_update_date, NULL AS derived_requirement_id, NULL AS derived_concept_id, NULL AS derived_idea_id, NULL AS derived_requirement_line_id, NULL AS derived_component_id, pel.project_id AS derived_project_id, poa.object_id AS derived_task_id, poa.associated_object_id AS final_derived_id, poa.associated_object_id AS final_driving_id FROM (SELECT * FROM fusion.pje_object_associations WHERE associated_object_sub_type_id IN (SELECT DELIVERABLE_TYPE_ID FROM PJE_DELIVERABLE_TYPES_VL PROPOSALCLASS WHERE PROPOSALCLASS.DELIVERABLE_TYPE_CLASS='PLM_PROPOSAL' ) ) poa, pjf_proj_elements_b pel WHERE pel.proj_element_id=poa.object_id

SQL_Statement

SELECT PropRel1.RELATIONSHIP_ID,

PropRel1.OBJECT_VERSION_NUMBER,

PropRel1.RELATIONSHIP_TYPE,

PropRel1.TARGET_TYPE,

PropRel1.DIRECTION_TYPE,

PropRel1.SRC_OBJ_TYPE,

PropRel1.SRC_OBJ_ID,

PropRel1.DEST_OBJ_TYPE,

PropRel1.DEST_OBJ_ID,

PropRel1.CREATED_BY,

PropRel1.CREATION_DATE,

PropRel1.LAST_UPDATED_BY,

PropRel1.LAST_UPDATE_DATE,

PropRel1.DERIVED_REQUIREMENT_ID,

PropRel1.DERIVED_CONCEPT_ID,

PropRel1.DERIVED_IDEA_ID,

PropRel1.DERIVED_REQUIREMENT_LINE_ID,

PropRel1.DERIVED_COMPONENT_ID,

NULL AS DERIVED_PROJECT_ID,

NULL AS DERIVED_TASK_ID,

CASE

WHEN PropRel2.derived_PROP_ID IS NOT NULL

THEN PropRel2.derived_PROP_ID

WHEN PropRel1.derived_PROP_ID IS NOT NULL

THEN PropRel1.derived_PROP_ID

END AS FINAL_DERIVED_ID,

CASE

WHEN PropRel2.DRIVING_PROP_ID IS NOT NULL

THEN PropRel2.DRIVING_PROP_ID

WHEN PropRel1.DRIVING_PROP_ID IS NOT NULL

THEN PropRel1.DRIVING_PROP_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= 'PROPOSAL'

AND AR.dest_obj_type = 'REQUIREMENT')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'REQUIREMENT'

AND AR.dest_obj_type = 'PROPOSAL')

THEN AR.src_obj_id

END AS DERIVED_REQUIREMENT_ID,

CASE

WHEN (AR.src_obj_type= 'PROPOSAL'

AND AR.dest_obj_type = 'CONCEPT')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'CONCEPT'

AND AR.dest_obj_type = 'PROPOSAL')

THEN AR.src_obj_id

END AS DERIVED_CONCEPT_ID,

CASE

WHEN (AR.src_obj_type= 'PROPOSAL'

AND AR.dest_obj_type = 'IDEA')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'IDEA'

AND AR.dest_obj_type = 'PROPOSAL')

THEN AR.src_obj_id

END AS DERIVED_IDEA_ID,

CASE

WHEN (AR.src_obj_type= 'PROPOSAL'

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

THEN AR.src_obj_id

END AS DERIVED_REQUIREMENT_LINE_ID,

CASE

WHEN (AR.src_obj_type= 'PROPOSAL'

AND AR.dest_obj_type = 'COMPONENT')

THEN AR.dest_obj_id

WHEN (AR.src_obj_type= 'COMPONENT'

AND AR.dest_obj_type = 'PROPOSAL')

THEN AR.src_obj_id

END AS DERIVED_COMPONENT_ID,

CASE

WHEN (AR.src_obj_type = 'PROPOSAL'

AND AR.dest_obj_type != 'PROPOSAL')

THEN AR.src_obj_id

WHEN (AR.src_obj_type != 'PROPOSAL'

AND AR.dest_obj_type = 'PROPOSAL')

THEN AR.dest_obj_id

END AS DRIVING_PROP_ID,

CASE

WHEN (AR.src_obj_type = 'PROPOSAL'

AND AR.dest_obj_type != 'PROPOSAL')

THEN AR.src_obj_id

WHEN (AR.src_obj_type!= 'PROPOSAL'

AND AR.dest_obj_type = 'PROPOSAL')

THEN AR.dest_obj_id

END AS derived_PROP_ID

FROM ACA_CS_RELATIONSHIP AR

WHERE SRC_OBJ_TYPE = 'PROPOSAL'

OR DEST_OBJ_TYPE = 'PROPOSAL'

) PropRel1,

(SELECT RELATIONSHIP_ID,

CASE

WHEN (AR.src_obj_type= 'PROPOSAL'

AND AR.dest_obj_type = 'PROPOSAL')

THEN AR.dest_obj_id

END AS derived_PROP_ID,

CASE

WHEN (AR.src_obj_type= 'PROPOSAL'

AND AR.dest_obj_type = 'PROPOSAL')

THEN AR.src_obj_id

END AS DRIVING_PROP_ID

FROM ACA_CS_RELATIONSHIP AR

WHERE SRC_OBJ_TYPE = 'PROPOSAL'

OR DEST_OBJ_TYPE = 'PROPOSAL'

UNION

SELECT RELATIONSHIP_ID,

CASE

WHEN (AR.src_obj_type= 'PROPOSAL'

AND AR.dest_obj_type = 'PROPOSAL')

THEN AR.src_obj_id

END AS derived_PROP_ID,

CASE

WHEN (AR.src_obj_type= 'PROPOSAL'

AND AR.dest_obj_type = 'PROPOSAL')

THEN AR.dest_obj_id

END AS DRIVING_PROP_ID

FROM ACA_CS_RELATIONSHIP AR

WHERE SRC_OBJ_TYPE = 'PROPOSAL'

OR DEST_OBJ_TYPE = 'PROPOSAL'

) PropRel2

WHERE PropRel1.RELATIONSHIP_ID = PropRel2.RELATIONSHIP_ID

UNION ALL

SELECT poa.object_association_id AS relationship_id,

poa.object_version_number AS object_version_number,

NULL AS relationship_type,

NULL AS target_type,

NULL AS direction_type,

poa.object_type AS src_obj_type,

poa.object_id AS src_obj_id,

poa.associated_object_type AS dest_obj_type,

poa.associated_object_id AS dest_obj_id,

poa.created_by AS created_by,

poa.creation_date AS creation_date,

poa.last_updated_by AS last_updated_by,

poa.last_update_date AS last_update_date,

NULL AS derived_requirement_id,

NULL AS derived_concept_id,

NULL AS derived_idea_id,

NULL AS derived_requirement_line_id,

NULL AS derived_component_id,

pel.project_id AS derived_project_id,

poa.object_id AS derived_task_id,

poa.associated_object_id AS final_derived_id,

poa.associated_object_id AS final_driving_id

FROM

(SELECT *

FROM fusion.pje_object_associations

WHERE associated_object_sub_type_id IN

(SELECT DELIVERABLE_TYPE_ID

FROM PJE_DELIVERABLE_TYPES_VL PROPOSALCLASS

WHERE PROPOSALCLASS.DELIVERABLE_TYPE_CLASS='PLM_PROPOSAL'

)

) poa,

pjf_proj_elements_b pel

WHERE pel.proj_element_id=poa.object_id