VIEW: APPS.AHL_CSI_INSTANCE_RELNS_V

Source

View Text - Preformatted

SELECT subject_id Instance_id, object_id parent_instance_id, (select object_id from csi_ii_relationships parent where not exists (select 'x' from csi_ii_relationships where subject_id = parent.object_id and trunc(sysdate) < trunc(nvl(active_end_date,sysdate+1))) start with parent.subject_id = ii.subject_id and parent. relationship_type_code = 'COMPONENT-OF' and trunc(nvl(parent.active_start_date, sysdate)) <= trunc(sysdate) and trunc(sysdate) < trunc(nvl(parent.active_end_date, sysdate+1)) connect by prior parent.object_id = parent.subject_id and parent.relationship_type_code = 'COMPONENT-OF' and trunc(nvl(parent.active_start_date, sysdate)) <= trunc(sysdate) and trunc(sysdate) < trunc(nvl(parent.active_end_date, sysdate+1)) ) Root_instance_id from csi_ii_relationships ii WHERE relationship_type_code = 'COMPONENT-OF' and trunc(nvl(active_start_date, sysdate)) <= trunc(sysdate) and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)) UNION select instance_id, to_number(null) parent_instance_id, instance_id root_instance_id from csi_item_instances csi where trunc(nvl(csi.active_start_date, sysdate)) <= trunc(sysdate) and trunc(sysdate) < trunc(nvl(csi.active_end_date, sysdate+1)) and not exists (select 'x' from csi_ii_relationships where subject_id = csi.instance_id and trunc(sysdate) < trunc(nvl(active_end_date, sysdate+1)))

View Text - HTML Formatted

SELECT SUBJECT_ID INSTANCE_ID
, OBJECT_ID PARENT_INSTANCE_ID
, (SELECT OBJECT_ID
FROM CSI_II_RELATIONSHIPS PARENT
WHERE NOT EXISTS (SELECT 'X'
FROM CSI_II_RELATIONSHIPS
WHERE SUBJECT_ID = PARENT.OBJECT_ID
AND TRUNC(SYSDATE) < TRUNC(NVL(ACTIVE_END_DATE
, SYSDATE+1))) START WITH PARENT.SUBJECT_ID = II.SUBJECT_ID
AND PARENT. RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(PARENT.ACTIVE_START_DATE
, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(SYSDATE) < TRUNC(NVL(PARENT.ACTIVE_END_DATE
, SYSDATE+1)) CONNECT BY PRIOR PARENT.OBJECT_ID = PARENT.SUBJECT_ID
AND PARENT.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(PARENT.ACTIVE_START_DATE
, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(SYSDATE) < TRUNC(NVL(PARENT.ACTIVE_END_DATE
, SYSDATE+1)) ) ROOT_INSTANCE_ID
FROM CSI_II_RELATIONSHIPS II
WHERE RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(NVL(ACTIVE_START_DATE
, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(SYSDATE) < TRUNC(NVL(ACTIVE_END_DATE
, SYSDATE+1)) UNION SELECT INSTANCE_ID
, TO_NUMBER(NULL) PARENT_INSTANCE_ID
, INSTANCE_ID ROOT_INSTANCE_ID
FROM CSI_ITEM_INSTANCES CSI
WHERE TRUNC(NVL(CSI.ACTIVE_START_DATE
, SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(SYSDATE) < TRUNC(NVL(CSI.ACTIVE_END_DATE
, SYSDATE+1))
AND NOT EXISTS (SELECT 'X'
FROM CSI_II_RELATIONSHIPS
WHERE SUBJECT_ID = CSI.INSTANCE_ID
AND TRUNC(SYSDATE) < TRUNC(NVL(ACTIVE_END_DATE
, SYSDATE+1)))