View: AHL_CSI_INSTANCE_RELNS_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	This view contains the instance id, its immediate parent(if exists) and root instance id ( i.e. instance id for unit instances).
Implementation/DBA Data:	APPS.AHL_CSI_INSTANCE_RELNS_V

View Text

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)))

Columns