CSE_GENEALOGY_RELATIONSHIPS_V

Details

• Schema: FUSION

• Object owner: CSE

• Object type: VIEW

Columns

Name
CHILD_GEN_OBJECT_ID RELATIONSHIP_TYPE_CODE PARENT_GEN_OBJECT_ID OBJECT_TYPE SPLIT_FLAG MERGE_FLAG TRANSLATE_FLAG

Query

SQL_Statement

SELECT UNIQUE CHILD.GEN_OBJECT_ID CHILD_GEN_OBJECT_ID, RELATIONSHIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE, PARENT.GEN_OBJECT_ID PARENT_GEN_OBJECT_ID, RELATIONSHIP.OBJECT_TYPE, NVL(RELATIONSHIP.SPLIT_FLAG,'N') SPLIT_FLAG, NVL(RELATIONSHIP.MERGE_FLAG,'N') MERGE_FLAG, NVL(RELATIONSHIP.TRANSLATE_FLAG,'N') TRANSLATE_FLAG FROM CSE_ASSETS_B PARENT, CSE_ASSET_RELATIONSHIPS RELATIONSHIP, CSE_ASSETS_B CHILD WHERE RELATIONSHIP.RELATIONSHIP_TYPE_CODE = 'ORA_COMPONENT' AND PARENT.ASSET_ID = RELATIONSHIP.OBJECT_ID AND CHILD.ASSET_ID = RELATIONSHIP.SUBJECT_ID AND SYSDATE BETWEEN NVL(RELATIONSHIP.ACTIVE_START_DATE, SYSDATE - 1) AND NVL(RELATIONSHIP.ACTIVE_END_DATE, SYSDATE + 1) AND relationship.object_type = 'A' UNION SELECT CHILD.GEN_OBJECT_ID CHILD_GEN_OBJECT_ID, RELATIONSHIP.RELATIONSHIP_TYPE_CODE RELATIONSHIP_TYPE_CODE, PARENT.GEN_OBJECT_ID PARENT_GEN_OBJECT_ID, RELATIONSHIP.OBJECT_TYPE, RELATIONSHIP.SPLIT_FLAG, RELATIONSHIP.MERGE_FLAG, RELATIONSHIP.TRANSLATE_FLAG FROM CSE_GENEALOGY_OBJECTS PARENT, CSE_ASSET_RELATIONSHIPS RELATIONSHIP, CSE_GENEALOGY_OBJECTS CHILD WHERE RELATIONSHIP.RELATIONSHIP_TYPE_CODE = 'ORA_LOT_TXN' AND PARENT.GEN_OBJECT_ID = RELATIONSHIP.OBJECT_ID AND CHILD.GEN_OBJECT_ID = RELATIONSHIP.SUBJECT_ID AND RELATIONSHIP.OBJECT_TYPE = 'G'