SELECT REQXPL.MODEL_ID , REQXPL.MODEL_REF_EXPL_ID , ROOTUI.UI_DEF_ID AS ROOT_UI_DEF_ID , ROOTUI.NAME AS ROOT_UI_NAME , ENCLUI.UI_DEF_ID AS ENCL_UI_DEF_ID , ENCLUI.NAME AS ENCL_UI_NAME , DECODE (ROOTUI.UI_DEF_ID , ENCLUI.UI_DEF_ID , '1' , '0') AS IS_ROOT_UI_FLAG , '2005-08-10' AS CZ_VIEW_REVISION FROM CZ_MODEL_REF_EXPLS REQXPL , CZ_UI_DEFS ROOTUI , CZ_UI_DEFS ENCLUI WHERE REQXPL.DELETED_FLAG = '0' AND REQXPL.MODEL_ID = ROOTUI.DEVL_PROJECT_ID AND ROOTUI.DELETED_FLAG = '0' AND ENCLUI.UI_DEF_ID IN ( SELECT ROOTUI.UI_DEF_ID FROM DUAL UNION ALL SELECT REF_UI_DEF_ID FROM CZ_UI_REFS URF WHERE EXISTS (SELECT NULL FROM CZ_MODEL_REF_EXPLS CLIMBX WHERE CLIMBX.PS_NODE_TYPE = 263 AND CLIMBX.DELETED_FLAG = '0' AND (CLIMBX.COMPONENT_ID , CLIMBX.REFERRING_NODE_ID) IN (SELECT URXPL.COMPONENT_ID , URXPL.REFERRING_NODE_ID FROM CZ_MODEL_REF_EXPLS URXPL WHERE URXPL.MODEL_REF_EXPL_ID=URF.MODEL_REF_EXPL_ID) START WITH CLIMBX.MODEL_REF_EXPL_ID = REQXPL.MODEL_REF_EXPL_ID CONNECT BY CLIMBX.MODEL_REF_EXPL_ID = PRIOR CLIMBX.PARENT_EXPL_NODE_ID AND CLIMBX.DELETED_FLAG = '0' ) START WITH URF.UI_DEF_ID = ROOTUI.UI_DEF_ID CONNECT BY URF.UI_DEF_ID = PRIOR URF.REF_UI_DEF_ID AND DELETED_FLAG='0' AND PRIOR DELETED_FLAG='0' )