[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
v_ui_features_ref.DELETE;
v_ui_opt_meth_ref.DELETE;
v_ui_opt_prop_ref.DELETE;
v_ui_opt_ord_ref.DELETE ;
SELECT ui_node_id
,option_sort_method
,option_sort_property
,option_sort_order
BULK
COLLECT
INTO v_ui_features_ref
,v_ui_opt_meth_ref
,v_ui_opt_prop_ref
,v_ui_opt_ord_ref
FROM cz_ui_nodes
WHERE cz_ui_nodes.ui_def_id = p_ui_def_id
AND cz_ui_nodes.deleted_flag = '0'
AND cz_ui_nodes.ui_node_type = 148
AND cz_ui_nodes.option_sort_method = 2;
x_sorted_table.DELETE;
SELECT ui_node_id
,option_sort_method
,option_sort_property
,option_sort_order
BULK
COLLECT
INTO v_ui_features_ref
,v_ui_opt_meth_ref
,v_ui_opt_prop_ref
,v_ui_opt_ord_ref
FROM cz_ui_nodes
WHERE cz_ui_nodes.ui_def_id = p_ui_def_id
AND cz_ui_nodes.deleted_flag = '0'
AND cz_ui_nodes.ui_node_type = 148
AND cz_ui_nodes.option_sort_method IN (-1, 2);
x_sorted_table.DELETE;
v_ui_nodes_ref.DELETE;
v_ps_nodes_ref.DELETE;
v_ui_feat_ref.DELETE;
v_ui_nodes_tbl.DELETE;
v_ps_nodes_tbl.DELETE;
SELECT data_type INTO v_property_data_type
FROM cz_properties
WHERE cz_properties.property_id = v_ui_opt_prop_ref(i)
AND cz_properties.deleted_flag = '0';
select v.ui_node_id, v.ps_node_id
bulk collect into v_ui_nodes_tbl, v_ps_nodes_tbl
from
(
SELECT ps_node_id, ui_node_id, parent_id, ps_node_name, property_id, property_name,
data_type, substr (min (cnct || property_value), 3) as
property_value,max (item_id) as item_id, max (item_type_id) as
item_type_id, devl_project_id,default_value
from (
select '1P' as cnct, psn1.devl_project_id, psn1.ps_node_id,
psn1.name as ps_node_name,psn1.parent_id, nvl (
psp1.data_value, prp1.def_value) as property_value,
prp1.name as property_name, prp1.property_id,
prp1.data_type, psn1.item_id, to_number (null)
as item_type_id, prp1.def_value as default_value, uin.ui_node_id
from cz_ps_nodes psn1,
cz_ps_prop_vals psp1,
CZ_UI_NODES uin,
cz_properties prp1
where psn1.deleted_flag = '0'
and psn1.ps_node_id =psp1.ps_node_id
and uin.ps_node_id = psn1.ps_node_id
and uin.PARENT_ID = v_ui_features_ref(i)
and uin.DELETED_FLAG = '0'
and psp1.deleted_flag = '0'
and psp1.property_id =prp1.property_id
and prp1.deleted_flag = '0'
union all
select '2I' as cnct, psn2.devl_project_id, psn2.ps_node_id,
psn2.name as ps_node_name, psn2.parent_id,nvl (
ipv2.property_value, prp2.def_value) as property_value,
prp2.name as property_name, prp2.property_id,
prp2.data_type,psn2.item_id, to_number (null) as item_type_id
, prp2.def_value as default_value, uin.ui_node_id
from cz_ps_nodes psn2,
cz_ui_nodes uin,
cz_item_property_values ipv2,
cz_properties prp2,
cz_item_masters itm2
where psn2.deleted_flag = '0'
and psn2.item_id = ipv2.item_id
and ipv2.deleted_flag = '0'
and ipv2.property_id = prp2.property_id
and uin.ps_node_id = psn2.ps_node_id
and uin.PARENT_ID = v_ui_features_ref(i)
and uin.DELETED_FLAG = '0'
and prp2.deleted_flag = '0'
and itm2.item_id = ipv2.item_id
union all
select
'3T' as cnct, psn3.devl_project_id, psn3.ps_node_id,
psn3.name as ps_node_name, psn3.parent_id, prp3.def_value
as property_value,prp3.name as property_name,
prp3.property_id,
prp3.data_type,psn3.item_id, itm3.item_type_id , prp3.def_value as
default_value, uin.ui_node_id
from
cz_ps_nodes psn3,
cz_item_masters itm3,
cz_ui_nodes uin,
cz_item_type_properties itp3,
cz_properties prp3
where psn3.deleted_flag = '0'
and psn3.item_id = itm3.item_id
and itm3.deleted_flag = '0'
and itm3.item_type_id = itp3.item_type_id
and uin.ps_node_id = psn3.ps_node_id
and uin.PARENT_ID = v_ui_features_ref(i)
and uin.DELETED_FLAG = '0'
and itp3.deleted_flag = '0'
and itp3.property_id = prp3.property_id
and prp3.deleted_flag = '0'
)
group by ps_node_id, parent_id, ps_node_name, devl_project_id,
property_id,property_name, data_type, default_value, ui_node_id
) v
WHERE v.PROPERTY_ID = v_ui_opt_prop_ref(i)
ORDER BY To_number(V.property_value) DESC;
select v.ui_node_id, v.ps_node_id
bulk collect into v_ui_nodes_tbl, v_ps_nodes_tbl
from
(
SELECT ps_node_id, ui_node_id, parent_id, ps_node_name, property_id, property_name,
data_type, substr (min (cnct || property_value), 3) as
property_value,max (item_id) as item_id, max (item_type_id) as
item_type_id,devl_project_id,default_value
from (
select '1P' as cnct, psn1.devl_project_id, psn1.ps_node_id,
psn1.name as ps_node_name,psn1.parent_id, nvl (
psp1.data_value, prp1.def_value) as property_value,
prp1.name as property_name, prp1.property_id,
prp1.data_type,psn1.item_id, to_number (null)
as item_type_id, prp1.def_value as default_value, uin.ui_node_id
from cz_ps_nodes psn1,
cz_ps_prop_vals psp1,
CZ_UI_NODES uin,
cz_properties prp1
where psn1.deleted_flag = '0'
and psn1.ps_node_id =psp1.ps_node_id
and uin.ps_node_id = psn1.ps_node_id
and uin.PARENT_ID = v_ui_features_ref(i)
and uin.DELETED_FLAG = '0'
and psp1.deleted_flag = '0'
and psp1.property_id =prp1.property_id
and prp1.deleted_flag = '0'
union all
select '2I' as cnct, psn2.devl_project_id, psn2.ps_node_id,
psn2.name as ps_node_name, psn2.parent_id,nvl (
ipv2.property_value, prp2.def_value) as property_value,
prp2.name as property_name, prp2.property_id,
prp2.data_type,psn2.item_id, to_number (null) as item_type_id
, prp2.def_value as default_value, uin.ui_node_id
from cz_ps_nodes psn2,
cz_ui_nodes uin,
cz_item_property_values ipv2,
cz_properties prp2,
cz_item_masters itm2
where psn2.deleted_flag = '0'
and psn2.item_id = ipv2.item_id
and ipv2.deleted_flag = '0'
and ipv2.property_id = prp2.property_id
and uin.ps_node_id = psn2.ps_node_id
and uin.PARENT_ID = v_ui_features_ref(i)
and uin.DELETED_FLAG = '0'
and prp2.deleted_flag = '0'
and itm2.item_id = ipv2.item_id
union all
select
'3T' as cnct, psn3.devl_project_id, psn3.ps_node_id,
psn3.name as ps_node_name, psn3.parent_id, prp3.def_value
as property_value,prp3.name as property_name,
prp3.property_id,
prp3.data_type,psn3.item_id, itm3.item_type_id , prp3.def_value as
default_value, uin.ui_node_id
from
cz_ps_nodes psn3,
cz_item_masters itm3,
cz_ui_nodes uin,
cz_item_type_properties itp3,
cz_properties prp3
where psn3.deleted_flag = '0'
and psn3.item_id = itm3.item_id
and itm3.deleted_flag = '0'
and itm3.item_type_id = itp3.item_type_id
and uin.ps_node_id = psn3.ps_node_id
and uin.PARENT_ID = v_ui_features_ref(i)
and uin.DELETED_FLAG = '0'
and itp3.deleted_flag = '0'
and itp3.property_id = prp3.property_id
and prp3.deleted_flag = '0'
)
group by ps_node_id, parent_id, ps_node_name, devl_project_id,
property_id,property_name, data_type, default_value, ui_node_id
) v
WHERE v.PROPERTY_ID = v_ui_opt_prop_ref(i)
ORDER BY V.PROPERTY_VALUE DESC;
select v.ui_node_id, v.ps_node_id
bulk collect into v_ui_nodes_tbl, v_ps_nodes_tbl
from
(
SELECT ps_node_id, ui_node_id, parent_id, ps_node_name, property_id, property_name,
data_type, substr (min (cnct || property_value), 3) as
property_value,max (item_id) as item_id, max (item_type_id) as
item_type_id,devl_project_id,default_value
from (
select '1P' as cnct, psn1.devl_project_id, psn1.ps_node_id,
psn1.name as ps_node_name,psn1.parent_id, nvl (
psp1.data_value, prp1.def_value) as property_value,
prp1.name as property_name, prp1.property_id,
prp1.data_type,psn1.item_id, to_number (null)
as item_type_id, prp1.def_value as default_value, uin.ui_node_id
from cz_ps_nodes psn1,
cz_ps_prop_vals psp1,
CZ_UI_NODES uin,
cz_properties prp1
where psn1.deleted_flag = '0'
and psn1.ps_node_id =psp1.ps_node_id
and uin.ps_node_id = psn1.ps_node_id
and uin.PARENT_ID = v_ui_features_ref(i)
and uin.DELETED_FLAG = '0'
and psp1.deleted_flag = '0'
and psp1.property_id =prp1.property_id
and prp1.deleted_flag = '0'
union all
select '2I' as cnct, psn2.devl_project_id, psn2.ps_node_id,
psn2.name as ps_node_name, psn2.parent_id,nvl (
ipv2.property_value, prp2.def_value) as property_value,
prp2.name as property_name, prp2.property_id,
prp2.data_type,psn2.item_id, to_number (null) as item_type_id
, prp2.def_value as default_value, uin.ui_node_id
from cz_ps_nodes psn2,
cz_ui_nodes uin,
cz_item_property_values ipv2,
cz_properties prp2,
cz_item_masters itm2
where psn2.deleted_flag = '0'
and psn2.item_id = ipv2.item_id
and ipv2.deleted_flag = '0'
and ipv2.property_id = prp2.property_id
and uin.ps_node_id = psn2.ps_node_id
and uin.PARENT_ID = v_ui_features_ref(i)
and uin.DELETED_FLAG = '0'
and prp2.deleted_flag = '0'
and itm2.item_id = ipv2.item_id
union all
select
'3T' as cnct, psn3.devl_project_id, psn3.ps_node_id,
psn3.name as ps_node_name, psn3.parent_id, prp3.def_value
as property_value,prp3.name as property_name,
prp3.property_id,
prp3.data_type,psn3.item_id, itm3.item_type_id , prp3.def_value as
default_value, uin.ui_node_id
from
cz_ps_nodes psn3,
cz_item_masters itm3,
cz_ui_nodes uin,
cz_item_type_properties itp3,
cz_properties prp3
where psn3.deleted_flag = '0'
and psn3.item_id = itm3.item_id
and itm3.deleted_flag = '0'
and itm3.item_type_id = itp3.item_type_id
and uin.ps_node_id = psn3.ps_node_id
and uin.PARENT_ID = v_ui_features_ref(i)
and uin.DELETED_FLAG = '0'
and itp3.deleted_flag = '0'
and itp3.property_id = prp3.property_id
and prp3.deleted_flag = '0'
)
group by ps_node_id, parent_id, ps_node_name, devl_project_id,
property_id,property_name, data_type, default_value, ui_node_id
) v
WHERE v.PROPERTY_ID = v_ui_opt_prop_ref(i)
ORDER BY To_number(V.property_value) ASC;
select v.ui_node_id, v.ps_node_id
bulk collect into v_ui_nodes_tbl, v_ps_nodes_tbl
from
(
SELECT ps_node_id, ui_node_id, parent_id, ps_node_name, property_id, property_name,
data_type, substr (min (cnct || property_value), 3) as
property_value,max (item_id) as item_id, max (item_type_id) as
item_type_id,devl_project_id,default_value
from (
select '1P' as cnct, psn1.devl_project_id, psn1.ps_node_id,
psn1.name as ps_node_name,psn1.parent_id, nvl (
psp1.data_value, prp1.def_value) as property_value,
prp1.name as property_name, prp1.property_id,
prp1.data_type, psn1.item_id, to_number (null)
as item_type_id, prp1.def_value as default_value, uin.ui_node_id
from cz_ps_nodes psn1,
cz_ps_prop_vals psp1,
CZ_UI_NODES uin,
cz_properties prp1
where psn1.deleted_flag = '0'
and psn1.ps_node_id =psp1.ps_node_id
and uin.ps_node_id = psn1.ps_node_id
and uin.PARENT_ID = v_ui_features_ref(i)
and uin.DELETED_FLAG = '0'
and psp1.deleted_flag = '0'
and psp1.property_id =prp1.property_id
and prp1.deleted_flag = '0'
union all
select '2I' as cnct, psn2.devl_project_id, psn2.ps_node_id,
psn2.name as ps_node_name, psn2.parent_id,nvl (
ipv2.property_value, prp2.def_value) as property_value,
prp2.name as property_name, prp2.property_id,
prp2.data_type, psn2.item_id, to_number (null) as item_type_id
, prp2.def_value as default_value, uin.ui_node_id
from cz_ps_nodes psn2,
cz_ui_nodes uin,
cz_item_property_values ipv2,
cz_properties prp2,
cz_item_masters itm2
where psn2.deleted_flag = '0'
and psn2.item_id = ipv2.item_id
and ipv2.deleted_flag = '0'
and ipv2.property_id = prp2.property_id
and uin.ps_node_id = psn2.ps_node_id
and uin.PARENT_ID = v_ui_features_ref(i)
and uin.DELETED_FLAG = '0'
and prp2.deleted_flag = '0'
and itm2.item_id = ipv2.item_id
union all
select
'3T' as cnct, psn3.devl_project_id, psn3.ps_node_id,
psn3.name as ps_node_name, psn3.parent_id, prp3.def_value
as property_value,prp3.name as property_name,
prp3.property_id,
prp3.data_type, psn3.item_id, itm3.item_type_id , prp3.def_value as
default_value, uin.ui_node_id
from
cz_ps_nodes psn3,
cz_item_masters itm3,
cz_ui_nodes uin,
cz_item_type_properties itp3,
cz_properties prp3
where psn3.deleted_flag = '0'
and psn3.item_id = itm3.item_id
and itm3.deleted_flag = '0'
and itm3.item_type_id = itp3.item_type_id
and uin.ps_node_id = psn3.ps_node_id
and uin.PARENT_ID = v_ui_features_ref(i)
and uin.DELETED_FLAG = '0'
and itp3.deleted_flag = '0'
and itp3.property_id = prp3.property_id
and prp3.deleted_flag = '0'
)
group by ps_node_id, parent_id, ps_node_name, devl_project_id,
property_id,property_name, data_type, default_value, ui_node_id
) v
WHERE v.PROPERTY_ID = v_ui_opt_prop_ref(i)
ORDER BY V.PROPERTY_VALUE ASC; END IF;
SELECT ps_node_id
FROM cz_ps_nodes
WHERE devl_project_id = p_model_id
START WITH ps_node_id = p_node_id
CONNECT BY ps_node_id = PRIOR parent_ID;
SELECT persistent_node_id, ps_node_id, parent_id, ps_node_type, reference_id
FROM CZ_PS_NODES
WHERE devl_project_id = p_model_id
AND ps_node_type in (PS_NODE_BOM_MODEL_TYPE, PS_NODE_REFERENCE_TYPE)
AND deleted_flag = '0'
ORDER BY ps_node_type DESC;
SELECT component_id, config_status, config_date_created, effective_date, usg.name
INTO x_component_id, l_config_status, x_config_date_created, x_effective_date, x_usage_name
FROM cz_config_hdrs hdr, cz_model_usages usg
WHERE config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
AND deleted_flag = '0'
AND hdr.effective_usage_id = usg.model_usage_id;
SELECT 1 INTO l_dummy
FROM cz_config_messages
WHERE config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
AND deleted_flag = '0' AND ROWNUM < 2;
SELECT node_identifier INTO l_node_identifier
FROM cz_config_items
WHERE config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
AND deleted_flag = '0'
AND node_identifier IS NOT NULL AND node_identifier <> 'PRD'
AND ROWNUM < 2;
SELECT organization_id, quantity, config_item_id
INTO x_organization_id, x_quantity, l_config_item_id
FROM cz_config_details_v
WHERE config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
AND inventory_item_id = x_top_item_id;
Select ui_style INTO l_ui_style
from cz_model_publications
where cz_model_publications.publication_id = l_publication_id
and cz_model_publications.deleted_flag = '0';
Select ui_style INTO l_ui_style
from cz_model_publications
where cz_model_publications.publication_id = l_publication_id
and cz_model_publications.deleted_flag = '0';
Select ui_style INTO l_ui_style
from cz_model_publications
where cz_model_publications.publication_id = l_publication_id
and cz_model_publications.deleted_flag = '0';
SELECT component_id,model_id INTO l_component_id, l_model_id FROM CZ_MODEL_REF_EXPLS
WHERE model_ref_expl_id=p_target_page_expl_id;
SELECT persistent_node_id INTO l_root_persist_id FROM CZ_PS_NODES
WHERE devl_project_id=p_root_model_id AND parent_id IS NULL AND deleted_flag='0';
SELECT ps_node_id INTO l_target_ps_node_id FROM CZ_PS_NODES WHERE devl_project_id=l_model_id AND
persistent_node_id=p_target_page_persist_id AND deleted_flag='0';
SELECT model_ref_expl_id INTO l_model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
WHERE model_id=l_model_id AND parent_expl_node_id IS NULL AND deleted_flag='0';
FOR l IN(SELECT component_id,referring_node_id FROM CZ_MODEL_REF_EXPLS
WHERE ps_node_type=263
START WITH model_ref_expl_id=p_root_model_expl_id
CONNECT BY PRIOR parent_expl_node_id=model_ref_expl_id AND deleted_flag='0'
AND PRIOR deleted_flag='0')
LOOP
BEGIN
SELECT l.referring_node_id INTO l_referring_node_id FROM CZ_MODEL_REF_EXPLS
WHERE model_id=l.component_id AND model_ref_expl_id=p_target_page_expl_id;
FOR i IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
WHERE referring_node_id=l_referring_node_id AND deleted_flag='0'
START WITH model_id=p_root_model_id AND parent_expl_node_id IS NULL AND deleted_flag='0'
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag='0'
AND PRIOR deleted_flag='0')
LOOP
FOR j IN (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
WHERE component_id=l_component_id AND deleted_flag='0'
START WITH model_ref_expl_id=i.model_ref_expl_id
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag='0'
AND PRIOR deleted_flag='0')
LOOP
SELECT ps_node_id INTO l_target_ps_node_id FROM CZ_PS_NODES
WHERE devl_project_id=(SELECT devl_project_id FROM CZ_UI_DEFS WHERE ui_def_id=p_target_ui_def_id) AND
persistent_node_id=p_target_page_persist_id AND deleted_flag='0';
SELECT component_id,model_id INTO l_component_id,l_model_id FROM CZ_MODEL_REF_EXPLS
WHERE model_ref_expl_id=p_target_page_expl_id;
FOR l IN(SELECT component_id,referring_node_id FROM CZ_MODEL_REF_EXPLS
WHERE ps_node_type=263
START WITH model_ref_expl_id=p_root_model_expl_id
CONNECT BY PRIOR parent_expl_node_id=model_ref_expl_id AND deleted_flag='0'
AND PRIOR deleted_flag='0')
LOOP
BEGIN
SELECT l.referring_node_id INTO l_referring_node_id FROM CZ_MODEL_REF_EXPLS
WHERE model_id=l.component_id AND model_ref_expl_id=p_target_page_expl_id;
FOR i IN(SELECT model_ref_expl_id,node_depth FROM CZ_MODEL_REF_EXPLS
WHERE referring_node_id=l_referring_node_id AND deleted_flag='0'
START WITH model_id=p_root_model_id AND parent_expl_node_id IS NULL AND deleted_flag='0'
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag='0'
AND PRIOR deleted_flag='0')
LOOP
FOR j IN (SELECT node_depth FROM CZ_MODEL_REF_EXPLS
WHERE component_id=l_component_id AND deleted_flag='0'
START WITH model_ref_expl_id=i.model_ref_expl_id
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag='0'
AND PRIOR deleted_flag='0')
LOOP
RETURN i.node_depth;
SELECT devl_project_id INTO l_root_model_id
FROM CZ_UI_DEFS WHERE ui_def_id=p_root_ui_def_id;
FOR k IN(SELECT vv.target_page_id, vv.target_ui_def_id,vv.element_id,vv.bound_via_parent_flag from cz_uipages_for_explnodes_v vv
WHERE vv.root_ui_def_id = p_root_ui_def_id AND
vv.root_model_expl_id = p_root_model_expl_id AND
vv.root_model_node_id = p_root_model_node_id AND
vv.node_collection_flag=p_node_collection_flag)
LOOP
IF k.bound_via_parent_flag='0' THEN
l_ui_pages_tbl0.extend;
FOR i IN(SELECT * FROM (
SELECT
uipages.ui_def_id AS TARGET_UI_DEF_ID,
uipages.page_id AS TARGET_PAGE_ID,
(SELECT annotated_node_path(l_root_model_id,
UIPAGES.PAGEBASE_EXPL_NODE_ID,UIPAGES.ui_def_id,UIPAGES.persistent_node_id,p_root_model_expl_id)
FROM dual
) AS TARGET_PAGE_ANN_PATH,
uipages.DISPLAY_CONDITION_ID AS TARGET_PAGE_DISPCOND_ID,
uipages.DISPLAY_CONDITION_COMP AS TARGET_PAGE_DISPCOND_COMP,
uipages.DISPLAY_CONDITION_VALUE AS TARGET_PAGE_DISPCOND_VALUE,
uiels.PAGE_LEVEL AS PAGE_INCLUSION_DEPTH,
uiels.element_id,
uiels.ui_def_id,
uiels.page_id,
(SELECT get_TARGET_PAGE_REF_DEPTH(l_root_model_id,
UIPAGES.PAGEBASE_EXPL_NODE_ID,UIPAGES.ui_def_id,p_root_model_expl_id)
FROM dual
) AS TARGET_PAGE_REFERENCE_DEPTH,
'0' AS BOUND_VIA_PARENT_FLAG
FROM
CZ_UI_PAGES uipages,
(SELECT els.ui_def_id,els.page_id,els.element_id,level as PAGE_LEVEL FROM CZ_UI_PAGE_ELEMENTS els
START WITH (els.page_id,els.ui_def_id,els.element_id) IN
(SELECT /*+ CARDINALITY (UITPAGES 1) */ uitpages.page_id,uitpages.ui_def_id,uitpages.element_id FROM TABLE(CAST(l_ui_pages_tbl0 AS SYSTEM.CZ_UI_PAGE_ELEM_TBL)) UITPAGES)
CONNECT BY els.element_signature_id=6073 AND
els.target_page_ui_def_id= PRIOR els.ui_def_id AND
els.target_page_id= PRIOR els.page_id AND deleted_flag='0' AND PRIOR deleted_flag='0') uiels
WHERE uipages.page_id=uiels.page_id AND uipages.ui_def_id=uiels.ui_def_id
UNION
select
uipages.ui_def_id AS TARGET_UI_DEF_ID,
uipages.page_id AS TARGET_PAGE_ID,
(SELECT annotated_node_path(l_root_model_id,
UIPAGES.PAGEBASE_EXPL_NODE_ID,UIPAGES.ui_def_id,UIPAGES.persistent_node_id,p_root_model_expl_id)
FROM dual
) AS TARGET_PAGE_ANN_PATH,
uipages.DISPLAY_CONDITION_ID AS TARGET_PAGE_DISPCOND_ID,
uipages.DISPLAY_CONDITION_COMP AS TARGET_PAGE_DISPCOND_COMP,
uipages.DISPLAY_CONDITION_VALUE AS TARGET_PAGE_DISPCOND_VALUE,
uiels.PAGE_LEVEL AS PAGE_INCLUSION_DEPTH,
uiels.element_id,
uiels.ui_def_id,
uiels.page_id,
(SELECT get_TARGET_PAGE_REF_DEPTH(l_root_model_id,
UIPAGES.PAGEBASE_EXPL_NODE_ID,UIPAGES.ui_def_id,p_root_model_expl_id)
FROM dual
) AS TARGET_PAGE_REFERENCE_DEPTH,
'1' AS BOUND_VIA_PARENT_FLAG
FROM
CZ_UI_PAGES uipages,
(SELECT els.ui_def_id,els.page_id,els.element_id,level as PAGE_LEVEL FROM CZ_UI_PAGE_ELEMENTS els
START WITH (els.page_id,els.ui_def_id,els.element_id) IN
(SELECT /*+ CARDINALITY(UITPAGES1 1) */ uitpages1.page_id,uitpages1.ui_def_id,uitpages1.element_id FROM TABLE(CAST(l_ui_pages_tbl1 AS SYSTEM.CZ_UI_PAGE_ELEM_TBL)) UITPAGES1)
CONNECT BY els.element_signature_id=6073 AND
els.target_page_ui_def_id= PRIOR els.ui_def_id AND
els.target_page_id= PRIOR els.page_id AND deleted_flag='0' AND PRIOR deleted_flag='0') uiels
WHERE uipages.page_id=uiels.page_id AND uipages.ui_def_id=uiels.ui_def_id
)
WHERE TARGET_UI_DEF_ID IN
(SELECT p_root_ui_def_id FROM dual
UNION
SELECT ref_ui_def_id FROM cz_ui_refs
START WITH ui_def_id=p_root_ui_def_id AND deleted_flag='0'
CONNECT BY PRIOR ref_ui_def_id=ui_def_id AND deleted_flag='0') AND
TARGET_PAGE_ANN_PATH IS NOT NULL
ORDER BY
BOUND_VIA_PARENT_FLAG asc,
DECODE(TARGET_PAGE_ID,p_curr_page_id,DECODE(TARGET_UI_DEF_ID,p_curr_ui_def_id,0,1),1) asc,
DECODE(PAGE_INCLUSION_DEPTH,1,DECODE(TARGET_UI_DEF_ID,p_curr_ui_def_id,G_MAX_PAGE_INCLUSION_DEPTH,1),PAGE_INCLUSION_DEPTH) desc,
TARGET_PAGE_REFERENCE_DEPTH desc
)
LOOP
IF NOT(l_hash_map.EXISTS(i.TARGET_PAGE_ID)) THEN
x_ui_page_tbl(l_tbl_counter).UI_DEF_ID := i.TARGET_UI_DEF_ID;
FOR i IN(SELECT * FROM (
SELECT
uipages.ui_def_id AS TARGET_UI_DEF_ID,
uipages.page_id AS TARGET_PAGE_ID,
(SELECT annotated_node_path(l_root_model_id,
UIPAGES.PAGEBASE_EXPL_NODE_ID,UIPAGES.ui_def_id,UIPAGES.persistent_node_id,p_root_model_expl_id)
FROM dual
) AS TARGET_PAGE_ANN_PATH,
uipages.DISPLAY_CONDITION_ID AS TARGET_PAGE_DISPCOND_ID,
uipages.DISPLAY_CONDITION_COMP AS TARGET_PAGE_DISPCOND_COMP,
uipages.DISPLAY_CONDITION_VALUE AS TARGET_PAGE_DISPCOND_VALUE,
uiels.PAGE_LEVEL AS PAGE_INCLUSION_DEPTH,
uiels.element_id,
uiels.ui_def_id,
uiels.page_id,
(SELECT get_TARGET_PAGE_REF_DEPTH(l_root_model_id,
UIPAGES.PAGEBASE_EXPL_NODE_ID,UIPAGES.ui_def_id,p_root_model_expl_id)
FROM dual
) AS TARGET_PAGE_REFERENCE_DEPTH,
'0' AS BOUND_VIA_PARENT_FLAG
FROM
CZ_UI_PAGES uipages,
(SELECT els.ui_def_id,els.page_id,els.element_id,level as PAGE_LEVEL FROM CZ_UI_PAGE_ELEMENTS els
START WITH (els.page_id,els.ui_def_id,els.element_id) IN
(SELECT uitpages.page_id,uitpages.ui_def_id,uitpages.element_id FROM TABLE(CAST(l_ui_pages_tbl0 AS SYSTEM.CZ_UI_PAGE_ELEM_TBL)) uitpages)
CONNECT BY els.element_signature_id=6073 AND
els.target_page_ui_def_id= PRIOR els.ui_def_id AND
els.target_page_id= PRIOR els.page_id AND deleted_flag='0' AND PRIOR deleted_flag='0') uiels
WHERE uipages.page_id=uiels.page_id AND uipages.ui_def_id=uiels.ui_def_id
UNION
select
uipages.ui_def_id AS TARGET_UI_DEF_ID,
uipages.page_id AS TARGET_PAGE_ID,
(SELECT annotated_node_path(l_root_model_id,
UIPAGES.PAGEBASE_EXPL_NODE_ID,UIPAGES.ui_def_id,UIPAGES.persistent_node_id,p_root_model_expl_id)
FROM dual
) AS TARGET_PAGE_ANN_PATH,
uipages.DISPLAY_CONDITION_ID AS TARGET_PAGE_DISPCOND_ID,
uipages.DISPLAY_CONDITION_COMP AS TARGET_PAGE_DISPCOND_COMP,
uipages.DISPLAY_CONDITION_VALUE AS TARGET_PAGE_DISPCOND_VALUE,
uiels.PAGE_LEVEL AS PAGE_INCLUSION_DEPTH,
uiels.element_id,
uiels.ui_def_id,
uiels.page_id,
(SELECT get_TARGET_PAGE_REF_DEPTH(l_root_model_id,
UIPAGES.PAGEBASE_EXPL_NODE_ID,UIPAGES.ui_def_id,p_root_model_expl_id)
FROM dual
) AS TARGET_PAGE_REFERENCE_DEPTH,
'1' AS BOUND_VIA_PARENT_FLAG
FROM
CZ_UI_PAGES uipages,
(SELECT els.ui_def_id,els.page_id,els.element_id,level as PAGE_LEVEL FROM CZ_UI_PAGE_ELEMENTS els
START WITH (els.page_id,els.ui_def_id,els.element_id) IN
(SELECT uitpages1.page_id,uitpages1.ui_def_id,uitpages1.element_id FROM TABLE(CAST(l_ui_pages_tbl1 AS SYSTEM.CZ_UI_PAGE_ELEM_TBL)) uitpages1)
CONNECT BY els.element_signature_id=6073 AND
els.target_page_ui_def_id= PRIOR els.ui_def_id AND
els.target_page_id= PRIOR els.page_id AND deleted_flag='0' AND PRIOR deleted_flag='0') uiels
WHERE uipages.page_id=uiels.page_id AND uipages.ui_def_id=uiels.ui_def_id )
WHERE TARGET_UI_DEF_ID IN
(SELECT p_root_ui_def_id FROM dual
UNION
SELECT ref_ui_def_id FROM cz_ui_refs
START WITH ui_def_id=p_root_ui_def_id AND deleted_flag='0'
CONNECT BY PRIOR ref_ui_def_id=ui_def_id AND deleted_flag='0') AND
TARGET_PAGE_ANN_PATH IS NOT NULL
ORDER BY
DECODE(TARGET_PAGE_ID,p_curr_page_id,DECODE(TARGET_UI_DEF_ID,p_curr_ui_def_id,0,1),1) desc,
BOUND_VIA_PARENT_FLAG asc,
DECODE(PAGE_INCLUSION_DEPTH,1,DECODE(TARGET_UI_DEF_ID,p_curr_ui_def_id,G_MAX_PAGE_INCLUSION_DEPTH,1),PAGE_INCLUSION_DEPTH) desc,
TARGET_PAGE_REFERENCE_DEPTH desc
)
LOOP
IF NOT(l_hash_map.EXISTS(i.TARGET_PAGE_ID)) THEN
x_ui_page_tbl(l_tbl_counter).UI_DEF_ID := i.TARGET_UI_DEF_ID;
x_ui_page_tbl.DELETE(l_tbl_counter);