EGO_CHANGE_RELATIONSHIPS_VL

Details

• Schema: FUSION

• Object owner: EGO

• 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_REVISION_ID DERIVED_PK2VALUE DERIVED_CHANGE_ID DERIVED_ACTION_ID DERIVED_ISSUE_ID DERIVED_REQUIREMENT_ID DERIVED_PROJECTTASK_ID FINAL_DERIVED_ID FINAL_DRIVING_ID RELATIONSHIP_DIRECTION DERIVED_COMPONENT_NUMBER DEST_OBJ_NUMBER SRC_OBJ_NUMBER RELATIONSHIPOBJECTTYPE DERIVED_IP_EVENT_ID DERIVED_SHIPMENT_HEADER_ID DERIVED_RESOURCE_ID DERIVED_WORK_CENTER_ID DERIVED_ASSET_ID DERIVED_WO_OPERATION_ID

Query

SQL_Statement

SELECT ChangeRel.relationship_id, ChangeRel.object_version_number, ChangeRel.relationship_type, ChangeRel.target_type, ChangeRel.direction_type, ChangeRel.src_obj_type, ChangeRel.src_obj_id, ChangeRel.dest_obj_type, ChangeRel.dest_obj_id, ChangeRel.src_obj_pk2value, ChangeRel.dest_obj_pk2value, ChangeRel.created_by, ChangeRel.creation_date, ChangeRel.last_updated_by, ChangeRel.last_update_date, CASE WHEN ChangeRel.dest_obj_type = 'IDEA' THEN DEST_OBJ_ID END AS derived_idea_id, CASE WHEN ChangeRel.dest_obj_type = 'CONCEPT' THEN DEST_OBJ_ID END AS derived_concept_id, CASE WHEN ChangeRel.dest_obj_type = 'PROPOSAL' THEN DEST_OBJ_ID END AS derived_proposal_id, CASE WHEN ChangeRel.dest_obj_type = 'REQUIREMENT_LINE_ITEM' THEN DEST_OBJ_ID END AS derived_requirement_line_id, CASE WHEN ChangeRel.dest_obj_type = 'COMPONENT' THEN DEST_OBJ_ID END AS derived_component_id, CASE WHEN ChangeRel.dest_obj_type = 'ITEM' THEN DEST_OBJ_ID END AS derived_item_revision_id, CASE WHEN ChangeRel.dest_obj_type = 'ITEM' THEN DEST_OBJ_PK2VALUE END AS derived_pk2value, CASE WHEN ChangeRel.dest_obj_type in ('CHANGE','CORRECTIVE_ACTION','PROBLEM_REPORT') THEN DEST_OBJ_ID END AS derived_change_id, CASE WHEN ChangeRel.dest_obj_type = 'ORA_ACTION' THEN DEST_OBJ_ID END AS derived_action_id, CASE WHEN ChangeRel.dest_obj_type = 'ORA_ISSUE' THEN DEST_OBJ_ID END AS derived_ISSUE_id, CASE WHEN ChangeRel.dest_obj_type = 'REQUIREMENT' THEN DEST_OBJ_ID END AS derived_requirement_id, CASE WHEN ChangeRel.dest_obj_type = 'Projects' THEN DEST_OBJ_ID END AS derived_Projecttask_id, ChangeRel.dest_obj_id AS final_derived_id, ChangeRel.src_obj_id AS final_driving_id, RELATIONSHIP_DIRECTION, CASE WHEN ChangeRel.dest_obj_type = 'COMPONENT' THEN DEST_OBJ_NUMBER END AS derived_component_number, ChangeRel.DEST_OBJ_NUMBER, ChangeRel.SRC_OBJ_NUMBER, CASE WHEN ChangeRel.dest_obj_type = 'IDEA' THEN 'IDEA' WHEN ChangeRel.dest_obj_type = 'CONCEPT' THEN 'CONCEPT' WHEN ChangeRel.dest_obj_type = 'PROPOSAL' THEN 'PROPOSAL' WHEN ChangeRel.dest_obj_type = 'REQUIREMENT_LINE_ITEM' THEN 'REQUIREMENTS' WHEN ChangeRel.dest_obj_type = 'COMPONENT' THEN 'CONCEPT COMPONENT' WHEN ChangeRel.dest_obj_type = 'ITEM' THEN 'ITEM' WHEN ChangeRel.dest_obj_type = 'CHANGE' THEN 'CHANGE ORDERS' WHEN ChangeRel.dest_obj_type = 'CORRECTIVE_ACTION' THEN 'CORRECTIVE ACTION' WHEN ChangeRel.dest_obj_type = 'PROBLEM_REPORT' THEN 'PROBLEM REPORT' WHEN ChangeRel.dest_obj_type = 'ORA_ACTION' THEN 'QUALITY ACTIONS' WHEN ChangeRel.dest_obj_type = 'ORA_ISSUE' THEN 'QUALITY ISSUES' WHEN ChangeRel.dest_obj_type = 'REQUIREMENT' THEN 'REQUIREMENT SPECIFICATIONS' WHEN ChangeRel.dest_obj_type = 'Projects' THEN 'PROJECT TASK' WHEN ChangeRel.dest_obj_type = 'ORA_INSPECTION_RESULT' THEN 'INSPECTION RESULT' WHEN ChangeRel.dest_obj_type = 'ORA_RECEIPT' THEN 'RECEIPT' WHEN ChangeRel.dest_obj_type = 'ORA_RESOURCE' THEN 'RESOURCE' WHEN ChangeRel.dest_obj_type = 'ORA_WORK_CENTER' THEN 'WORK CENTER' WHEN ChangeRel.dest_obj_type = 'ORA_ASSET' THEN 'ASSET' WHEN ChangeRel.dest_obj_type = 'ORA_WORK_OPERATION' THEN 'WORK OPERATION' END RelationshipObjectType, CASE WHEN ChangeRel.dest_obj_type = 'ORA_INSPECTION_RESULT' THEN DEST_OBJ_ID END AS derived_ip_event_id, CASE WHEN ChangeRel.dest_obj_type = 'ORA_RECEIPT' THEN DEST_OBJ_ID END AS derived_shipment_header_id, CASE WHEN ChangeRel.dest_obj_type = 'ORA_RESOURCE' THEN DEST_OBJ_ID END AS derived_resource_id, CASE WHEN ChangeRel.dest_obj_type = 'ORA_WORK_CENTER' THEN DEST_OBJ_ID END AS derived_work_center_id, CASE WHEN ChangeRel.dest_obj_type = 'ORA_ASSET' THEN DEST_OBJ_ID END AS derived_asset_id, CASE WHEN ChangeRel.dest_obj_type = 'ORA_WORK_OPERATION' THEN DEST_OBJ_ID END AS derived_wo_operation_id FROM (SELECT RELATIONSHIP_ID, SRC_OBJ_NUMBER, SRC_OBJ_TYPE, SRC_OBJ_ID, DEST_OBJ_NUMBER, dest_obj_type, DEST_OBJ_ID, RELATIONSHIP_TYPE, TARGET_TYPE, DIRECTION_TYPE, SRC_OBJ_PK2VALUE, SRC_OBJ_PK3VALUE, SRC_OBJ_PK4VALUE, SRC_OBJ_PK5VALUE, DEST_OBJ_PK2VALUE, DEST_OBJ_PK3VALUE, DEST_OBJ_PK4VALUE, DEST_OBJ_PK5VALUE, OBJECT_VERSION_NUMBER, AUX_UNIQUE_ID, AUX_RELATIONSHIP_ID, CREATED_BY, CREATION_DATE , LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, 'S' RELATIONSHIP_DIRECTION FROM aca_cs_relationship WHERE SRC_OBJ_TYPE in ( 'CHANGE', 'CORRECTIVE_ACTION', 'PROBLEM_REPORT' ) AND DEST_OBJ_TYPE <> 'AGILE_CO' UNION ALL SELECT RELATIONSHIP_ID, DEST_OBJ_NUMBER, dest_obj_type, DEST_OBJ_ID, SRC_OBJ_NUMBER, SRC_OBJ_TYPE, SRC_OBJ_ID, CASE WHEN RELATIONSHIP_TYPE='ORA_FULFILLED_BY' THEN 'ORA_FULFILLS' WHEN RELATIONSHIP_TYPE='ORA_FULFILLS' THEN 'ORA_FULFILLED_BY' WHEN RELATIONSHIP_TYPE='ORA_IMPACTED_BY' THEN 'ORA_IMPACTS' WHEN RELATIONSHIP_TYPE='ORA_IMPACTS' THEN 'ORA_IMPACTED_BY' WHEN RELATIONSHIP_TYPE='ORA_TRACKED_BY' THEN 'ORA_TRACKS' WHEN RELATIONSHIP_TYPE='ORA_TRACKS' THEN 'ORA_TRACKED_BY' WHEN RELATIONSHIP_TYPE='ORA_VALIDATED_BY' THEN 'ORA_VALIDATES' WHEN RELATIONSHIP_TYPE='ORA_VALIDATES' THEN 'ORA_VALIDATED_BY' WHEN RELATIONSHIP_TYPE='ORA_VERIFIED_BY' THEN 'ORA_VERIFIES' WHEN RELATIONSHIP_TYPE='ORA_VERIFIES' THEN 'ORA_VERIFIED_BY' ELSE RELATIONSHIP_TYPE END RELATIONSHIP_TYPE, TARGET_TYPE, DIRECTION_TYPE, DEST_OBJ_PK2VALUE, DEST_OBJ_PK3VALUE, DEST_OBJ_PK4VALUE, DEST_OBJ_PK5VALUE, SRC_OBJ_PK2VALUE, SRC_OBJ_PK3VALUE, SRC_OBJ_PK4VALUE, SRC_OBJ_PK5VALUE, OBJECT_VERSION_NUMBER, AUX_UNIQUE_ID, AUX_RELATIONSHIP_ID, CREATED_BY, CREATION_DATE , LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, 'D' RELATIONSHIP_DIRECTION FROM aca_cs_relationship WHERE DEST_OBJ_TYPE in ( 'CHANGE', 'CORRECTIVE_ACTION', 'PROBLEM_REPORT' ) AND SRC_OBJ_TYPE <> 'AGILE_CO' UNION ALL SELECT OBJECT_ASSOCIATION_ID RELATIONSHIP_ID, NULL SRC_OBJ_NUMBER, 'CHANGE' SRC_OBJ_TYPE, ASSOCIATED_OBJECT_ID SRC_OBJ_ID, NULL DEST_OBJ_NUMBER, 'Projects' dest_obj_type, OBJECT_ID DEST_OBJ_ID, NULL AS RELATIONSHIP_TYPE, NULL AS TARGET_TYPE, NULL AS DIRECTION_TYPE, NULL AS SRC_OBJ_PK2VALUE, NULL AS SRC_OBJ_PK3VALUE, NULL AS SRC_OBJ_PK4VALUE, NULL AS SRC_OBJ_PK5VALUE, NULL AS DEST_OBJ_PK2VALUE, NULL AS DEST_OBJ_PK3VALUE, NULL AS DEST_OBJ_PK4VALUE, NULL AS DEST_OBJ_PK5VALUE, A.OBJECT_VERSION_NUMBER, NULL AS AUX_UNIQUE_ID, NULL AS AUX_RELATIONSHIP_ID, A.CREATED_BY, A.CREATION_DATE , A.LAST_UPDATED_BY, A.LAST_UPDATE_DATE, A.LAST_UPDATE_LOGIN, 'D' RELATIONSHIP_DIRECTION FROM pje_object_associations a, pje_deliverable_types_b b where a.associated_object_sub_type_id = b.deliverable_type_id and b.DELIVERABLE_TYPE_CLASS='PLM_CHANGE' ) ChangeRel