SQL Query, given below, will list out all the child model User Interfaces for a given parent model in Oracle Configurator.
*please note: This query will provide all the UIs in child models, even if it is not associated with parent model UI .
select
dp1.name root_model, root.name root_item, dp2.name child_name,
pn.name child_Item, cd1.name Root_ui,cd1.ui_def_id,cd2.name Child_ui,cd2.desc_text,cd2.ui_def_id
from
cz_devl_projects dp1,
cz_ps_nodes Root,
cz_devl_projects dp2,
Cz_ps_nodes pn,
cz_ui_defs cd1,
cz_ui_defs cd2,
cz_model_ref_expls mrf
where
1=1
and root.ps_node_id = Root.devl_project_id
and root.devl_project_id = dp1.devl_project_id
and root.persistent_node_id = root.ps_node_id
and mrf.model_id = dp1.devl_project_id
and pn.devl_project_id = pn.ps_node_id
and pn.devl_project_id = dp2.devl_project_id
and mrf.component_id = dp2.devl_project_id
and pn.ps_node_type = 436 ----> to restrict only for BOM models
and dp1.devl_project_id = cd1.devl_project_id
and dp2.devl_project_id = cd2.devl_project_id
and root.deleted_flag = 0
and dp1.deleted_flag = 0
and pn.deleted_flag = 0
and dp2.deleted_flag = 0
and mrf.deleted_flag = 0
and root.name = 'Model Item Name'
order by mrf.node_depth ;
