[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FOR psn_rec IN (SELECT ps_node_id, reference_id FROM cz_ps_nodes
WHERE deleted_flag = '0' AND parent_id = p_parent_id
AND ps_node_type IN (PS_NODE_TYPE_REFERENCE,
PS_NODE_TYPE_COMPONENT,
PS_NODE_TYPE_OPTION_CLASS)
ORDER BY tree_seq) LOOP
IF (psn_rec.reference_id IS NOT NULL) THEN
IF (NOT px_model_map.EXISTS(psn_rec.reference_id)) THEN
px_model_tbl(px_model_tbl.COUNT + 1) := psn_rec.reference_id;
SELECT
XPLNODE.MODEL_REF_EXPL_ID,
XPLNODE.psn_component_id as COMPONENT_ID,
XPLNODE.PS_NODE_ID,
--
CZ_TYPES.get_rule_signature_id ( -- ADW20040730:1
XPLNODE.instantiable_flag
,XPLNODE.feature_type
,XPLNODE.counted_options_flag
,XPLNODE.maximum
,XPLNODE.minimum
,XPLNODE.psn_ps_node_type
,XPLNODE.reference_id
,XPLNODE.maximum_selected
,XPLNODE.decimal_qty_flag
,XPLNODE.ib_trackable
,XPLNODE.devl_project_id
) as DETAILED_TYPE_ID,
XPLNODE.ps_node_id as PATH_HEAD_PSNODE_ID,
XPLNODE.model_ref_expl_id as PATH_HEAD_EXPL_ID,
--
XPLNODE.effective_parent_id as PATHHEAD_EFFPARENT_NODE_ID,
XPLNODE.parent_psnode_expl_id as PATHHEAD_EFFPARENT_EXPL_ID,
XPLNODE.effective_from as NODE_EFFECTIVE_FROM,
--
XPLNODE.effective_until as NODE_EFFECTIVE_UNTIL,
XPLNODE.effective_from as WHOLE_PATH_EFFECTIVE_FROM,
XPLNODE.effective_until as WHOLE_PATH_EFFECTIVE_UNTIL,
--
decode (XPLNODE.parent_psnode_expl_id, -- ADW20040730:3a
null, path_preference_root_only, -- only the model root has this calculated field null
ROOTEXPL.model_ref_expl_id, decode (XPLNODE.parent_id,
XPLNODE.model_id, path_preference_root_kids,
-- if parent is MODEL_ID, and the calc parent expl
-- is the root expl, this is a direct child of root
99),
999
) as NEAR_ROOT_MARKER
FROM CZ_EXPLMODEL_NODES_V XPLNODE
, CZ_MODEL_REF_EXPLS ROOTEXPL
WHERE XPLNODE.model_id = p_model_to_search
AND ROOTEXPL.MODEL_ID = P_MODEL_TO_SEARCH
AND ROOTEXPL.parent_expl_node_id IS NULL
AND ROOTEXPL.DELETED_FLAG = '0'
AND XPLNODE.name = p_namepath(pathiter)
AND suppress_flag = '0'
)
LOOP
foundcount := foundcount + 1;
SELECT
XPLNODE.MODEL_REF_EXPL_ID,
XPLNODE.PSN_COMPONENT_ID as COMPONENT_ID,
XPLNODE.PS_NODE_ID,
--
CZ_TYPES.get_rule_signature_id ( -- ADW20040730:1
XPLNODE.instantiable_flag
,XPLNODE.feature_type
,XPLNODE.counted_options_flag
,XPLNODE.maximum
,XPLNODE.minimum
,XPLNODE.psn_ps_node_type
,XPLNODE.reference_id
,XPLNODE.maximum_selected
,XPLNODE.decimal_qty_flag
,XPLNODE.ib_trackable
,XPLNODE.devl_project_id
) as DETAILED_TYPE_ID,
PARNODE.PATH_HEAD_PSNODE_ID,
PARNODE.PATH_HEAD_EXPL_ID,
--
PARNODE.PATHHEAD_EFFPARENT_NODE_ID,
PARNODE.PATHHEAD_EFFPARENT_EXPL_ID,
XPLNODE.effective_from as NODE_EFFECTIVE_FROM,
--
XPLNODE.effective_until as NODE_EFFECTIVE_UNTIL,
greatest (
PARNODE.whole_path_effective_from,
XPLNODE.effective_from
) as WHOLE_PATH_EFFECTIVE_FROM,
least (
PARNODE.whole_path_effective_until,
XPLNODE.effective_until
) as WHOLE_PATH_EFFECTIVE_UNTIL,
--
PARNODE.NEAR_ROOT_MARKER -- ADW20040730:3a
FROM
CZ_EXPLMODEL_NODES_V XPLNODE,
-- table (cast (LASTMARKS AS cz_model_node_tbl_type)) PARMARK, -- ADW20040730:3a
table (cast (LASTFOUND as "SYSTEM".cz_model_node_tbl_type)) PARNODE
WHERE
XPLNODE.model_id = p_model_to_search
AND XPLNODE.name = p_namepath(pathiter)
AND XPLNODE.effective_parent_id = PARNODE.PS_NODE_ID
AND XPLNODE.parent_psnode_expl_id = PARNODE.model_ref_expl_id -- ADW20040730:2
-- AND PARMARK.model_ref_expl_id = PARNODE.model_ref_expl_id -- ADW20040730:3
-- AND PARMARK.ps_node_id = PARNODE.ps_node_id
AND suppress_flag='0'
)
LOOP
-- push each node into retlist
foundcount := foundcount + 1;
select
udf.devl_project_id as root_model_id,
udf.ui_def_id as root_ui_def_id,
xpl.model_ref_expl_id as root_model_expl_id,
xpl.parent_expl_node_id as root_mdl_parnt_expl_id,
to_number (null) as referring_node_id,
xpl.node_depth as expl_node_depth,
0 as model_reference_depth,
'1' as virtual_flag,
udf.ui_def_id as child_ui_def_id,
to_number (null) as parent_ui_def_id,
udf.devl_project_id as child_model_id,
xpl.model_ref_expl_id as ref_expl_node_id,
xpl.model_ref_expl_id as leaf_expl_node_id,
rootn.persistent_node_id as leaf_persistent_node_id
from
cz_ui_defs udf,
cz_model_ref_expls xpl,
cz_ps_nodes rootn
where
rootn.deleted_flag = '0' and
rootn.ps_node_id = xpl.component_id and
xpl.model_id = udf.devl_project_id and
xpl.deleted_flag = '0' and
xpl.parent_expl_node_id is null and
udf.ui_def_id = p_root_ui_def_id and
udf.deleted_flag = '0'
)
LOOP
listsofar.extend ();
select
rootxp.model_id as root_model_id,
extlist.root_ui_def_id as root_ui_def_id,
rootxp.model_ref_expl_id as root_model_expl_id,
rootxp.parent_expl_node_id as root_mdl_parnt_expl_id,
rootxp.referring_node_id,
rootxp.node_depth as expl_node_depth,
extlist.model_reference_depth + decode (extlist.child_model_id,
DECODE (enclexpl.ps_node_type,
263, enclexpl.component_id,
264, enclexpl.component_id,
enclexpl.model_id
), 0,
1
) as model_reference_depth,
rootxp.virtual_flag,
extlist.child_ui_def_id as child_ui_def_id,
extlist.parent_ui_def_id as parent_ui_def_id,
DECODE (enclexpl.ps_node_type,
263, enclexpl.component_id,
264, enclexpl.component_id,
enclexpl.model_id
) as child_model_id,
enclexpl.model_ref_expl_id as ref_expl_node_id,
enclexpl.child_model_expl_id as leaf_expl_node_id,
comp.persistent_node_id as leaf_persistent_node_id
from
cz_ps_nodes comp,
cz_model_ref_expls rootxp,
cz_model_ref_expls enclexpl,
table (cast (listsofar as "SYSTEM".cz_uirefs_inmodel_tbl_type)) extlist
where
comp.ps_node_id = rootxp.component_id and
comp.deleted_flag = '0' and
rootxp.node_depth = curdepth and
extlist.expl_node_depth = curdepth - 1 and
rootxp.component_id = enclexpl.component_id and
decode (rootxp.referring_node_id, enclexpl.referring_node_id, 1, 0) = 1 and
rootxp.deleted_flag = '0' and
rootxp.parent_expl_node_id = EXTLIST.ROOT_model_expl_id and
enclexpl.deleted_flag = '0' and
enclexpl.parent_expl_node_id in (EXTLIST.REF_EXPL_NODE_id,
EXTLIST.LEAF_EXPL_NODE_ID)
-- 2004-09-22 ADW: this join criterion from the 'parent row' would spelunk down references
-- to the child UI/expl but NOT in the parent UI/expl tree. I think only the following clause
-- caused this behavior. Minor tweak above may fix the problem...
-- enclexpl.parent_expl_node_id = decode (EXTLIST.referring_node_id,
-- null, EXTLIST.REF_EXPL_NODE_id,
-- EXTLIST.LEAF_EXPL_NODE_ID)
)
loop
listsofar.extend ();
select
urf.ref_ui_def_id as current_child_ui,
urf.ui_def_id as current_parent_ui
into
childuihack,
parentuihack
from cz_ui_refs urf
where
nextinfo.ref_expl_node_id = urf.model_ref_expl_id AND
nextinfo.child_ui_def_id = urf.ui_def_id and
urf.deleted_flag = '0';
select (level - 1) into desc_ref_depth
from cz_model_ref_expls
where model_ref_expl_id = p_desc_expl_id and deleted_flag = '0'
start with model_ref_expl_id = p_encl_expl_id and deleted_flag = '0'
connect by model_ref_expl_id = prior child_model_expl_id and deleted_flag = '0' and
prior model_ref_expl_id <> p_desc_expl_id;
select node_depth into start_encl_depth
from cz_model_ref_expls
where model_ref_expl_id = p_encl_expl_id;
select
r_xp.model_ref_expl_id as encl_expl,
r_xp.node_depth as encl_depth,
c_xp.model_ref_expl_id as desc_expl
from
cz_model_ref_expls r_xp,
cz_model_ref_expls c_xp
where
-- retrieve the child expl nodes of both the ENCL and DESC nodes in the current pair
r_xp.parent_expl_node_id = pairs (cur_pair_index).enclosing_expl_id and
c_xp.parent_expl_node_id = pairs (cur_pair_index).descendant_expl_id and
-- restrict to expl pairs that match by component ID and by non-null REFERRING_NODE_ID
r_xp.component_id = c_xp.component_id and
decode (r_xp.referring_node_id, c_xp.referring_node_id, 1, 0) = 1 and
-- filter deleted expls
r_xp.deleted_flag = '0' and
c_xp.deleted_flag = '0'
)
loop
newpair := "SYSTEM".cz_expl_pair (
ENCLOSING_EXPL_ID => nextpair.encl_expl,
DESCENDANT_EXPL_ID => nextpair.desc_expl,
DESC_EXPL_REFERENCE_DEPTH => desc_ref_depth);