UI Templates used by Configurator UI

Following query returns the list of templates used by given UI. Replace ui_def_id in following query.


SELECT DISTINCT templ_ui_def_id,
  templ_id
FROM
  (SELECT ctrl_template_ui_def_id AS templ_ui_def_id,
     ctrl_template_id AS templ_id
   FROM cz_ui_page_elements pe
   WHERE ui_def_id = 113607
   AND deleted_flag = 0
   AND ctrl_template_id IS NOT NULL
   AND ctrl_template_ui_def_id IS NOT NULL
   AND EXISTS
    (SELECT 1
     FROM cz_ui_templates
     WHERE seeded_flag = 0
     AND deleted_flag = 0
     AND template_id = pe.ctrl_template_id
     AND ui_def_id = pe.ctrl_template_ui_def_id)
  UNION ALL
   SELECT page_status_templ_uidef_id AS templ_ui_def_id,
     page_status_template_id AS templ_id
   FROM cz_ui_pages pg
   WHERE ui_def_id = 113607
   AND deleted_flag = 0
   AND page_status_templ_uidef_id IS NOT NULL
   AND page_status_template_id IS NOT NULL
   AND EXISTS
    (SELECT 1
     FROM cz_ui_templates
     WHERE seeded_flag = 0
     AND deleted_flag = 0
     AND template_id = pg.page_status_template_id
     AND ui_def_id = pg.page_status_templ_uidef_id)
  UNION ALL
   SELECT ref_template_ui_def_id AS templ_ui_def_id,
     ref_template_id AS templ_id
   FROM cz_ui_ref_templates r
   WHERE deleted_flag = 0 START WITH deleted_flag = 0
   AND ref_templ_seeded_flag = 0
   AND(EXISTS
    (SELECT 1
     FROM cz_ui_page_elements pe
     WHERE pe.ui_def_id = 113607
     AND pe.deleted_flag = 0
     AND pe.ctrl_template_ui_def_id = r.template_ui_def_id
     AND pe.ctrl_template_id = r.template_id) OR EXISTS
      (SELECT 1
       FROM cz_ui_pages pg
       WHERE pg.ui_def_id = 113607
       AND pg.deleted_flag = 0
       AND pg.page_status_templ_uidef_id = r.template_ui_def_id
       AND pg.page_status_template_id = r.template_id)
    )
  CONNECT BY deleted_flag = 0
   AND ref_templ_seeded_flag = 0
   AND template_ui_def_id = PRIOR ref_template_ui_def_id
   AND template_id = PRIOR ref_template_id) ;