Query to get the details of all the child model UIs in Oracle Configurator

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 ;