SELECT UDF.UI_DEF_ID , UDF.NAME , UDF.DEVL_PROJECT_ID , UDF.COMPONENT_ID , UDF.PERSISTENT_UI_DEF_ID , UDF.MASTER_TEMPLATE_FLAG , UDF.FROM_MASTER_TEMPLATE_ID , UIT.TEMPLATE_ID , UIT.TEMPLATE_NAME , UIT.TEMPLATE_TYPE , UIT.PARENT_CONTAINER_TYPE , UIT.UI_DEF_ID AS TEMPLATE_UI_DEF_ID FROM CZ_UI_DEFS UDF , CZ_UI_TEMPLATES UIT WHERE UDF.UI_DEF_ID = UIT.UI_DEF_ID AND UDF.DELETED_FLAG = '0' AND UIT.DELETED_FLAG = '0' OR EXISTS (SELECT 1 FROM ( SELECT UI_DEF_ID , TEMPLATE_ID , TEMPLATE_UI_DEF_ID AS TEMPL_UI_DEF_ID FROM CZ_UI_CONT_TYPE_TEMPLS UCT WHERE DELETED_FLAG = '0' UNION ALL SELECT UI_DEF_ID , UPE.CTRL_TEMPLATE_ID AS TEMPLATE_ID , UPE.CTRL_TEMPLATE_UI_DEF_ID AS TEMPL_UI_DEF_ID FROM CZ_UI_PAGE_ELEMENTS UPE WHERE DELETED_FLAG = '0' UNION ALL SELECT UI_DEF_ID , OUTER_PAGE_TEMPLATE_ID AS TEMPLATE_ID , OUTER_PAGE_TEMPL_UIDEF_ID AS TEMPL_UI_DEF_ID FROM CZ_UI_PAGES WHERE DELETED_FLAG = '0' UNION ALL SELECT UI_DEF_ID , PAGE_STATUS_TEMPLATE_ID AS TEMPLATE_ID , PAGE_STATUS_TEMPL_UIDEF_ID AS TEMPL_UI_DEF_ID FROM CZ_UI_PAGES WHERE DELETED_FLAG = '0' UNION ALL SELECT UI_DEF_ID , PROCESSING_PAGE_TEMPL_ID AS TEMPLATE_ID , PROC_PAGE_TEMPL_UI_DEF_ID AS TEMPL_UI_DEF_ID FROM CZ_UI_ACTIONS WHERE DELETED_FLAG = '0' ) UI_TEMPL_REFS WHERE UI_TEMPL_REFS.UI_DEF_ID = UDF.UI_DEF_ID AND UIT.TEMPLATE_ID = UI_TEMPL_REFS.TEMPLATE_ID AND UI_TEMPL_REFS.TEMPL_UI_DEF_ID = UIT.UI_DEF_ID )