SELECT DELIVERY_DETAIL_ID CONTAINER_INSTANCE_ID , PARENT_DELIVERY_DETAIL_ID PARENT_CONTAINER_INSTANCE_ID , DELIVERY_ID FROM WSH_DELIVERY_ASSIGNMENTS WDA WHERE DELIVERY_DETAIL_ID = (SELECT WDD.DELIVERY_DETAIL_ID FROM WSH_DELIVERY_DETAILS WDD WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID AND CONTAINER_FLAG = 'Y') AND ACTIVE_FLAG = 'Y' START WITH PARENT_DELIVERY_DETAIL_ID IS NULL CONNECT BY PRIOR DELIVERY_DETAIL_ID = PARENT_DELIVERY_DETAIL_ID