[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO m_RUN_ID FROM dual;
SELECT 1 INTO p_out_flag FROM CZ_PS_NODES
WHERE deleted_flag = NO_FLAG
AND ps_node_id=p_ps_node_id AND ps_node_type IN(COMPONENT_TYPE,PRODUCT_TYPE,
REFERENCE_TYPE,CONNECTOR_TYPE,BOM_MODEL_TYPE)
AND ps_node_id NOT IN
(SELECT ps_node_id FROM CZ_PS_NODES nodes
WHERE deleted_flag = NO_FLAG
AND devl_project_id =p_model_id
AND ps_node_type IN(COMPONENT_TYPE,REFERENCE_TYPE,CONNECTOR_TYPE,BOM_MODEL_TYPE)
AND MINIMUM = 1
AND MAXIMUM = 1
AND NOT EXISTS
(SELECT 1 FROM cz_expression_nodes x
WHERE deleted_flag = NO_FLAG
AND consequent_flag =YES_FLAG
AND ps_node_id = nodes.ps_node_id
AND EXISTS
(SELECT NULL FROM cz_rules WHERE (antecedent_id=x.express_id OR consequent_id=x.express_id
OR amount_id=x.express_id) AND disabled_flag=NO_FLAG
AND devl_project_id=p_model_id AND deleted_flag=NO_FLAG)));
SELECT 1 INTO p_out_flag FROM CZ_PS_NODES
WHERE deleted_flag = NO_FLAG
AND ps_node_id=p_ps_node_id AND (instantiable_flag IN (OPTIONAL_EXPL_TYPE,OPTIONAL_EXPL_TYPE) OR
(ps_node_type IN(COMPONENT_TYPE,PRODUCT_TYPE,REFERENCE_TYPE,CONNECTOR_TYPE,BOM_MODEL_TYPE) AND NOT(MAXIMUM=1 AND MINIMUM=1)));
SELECT CZ_MODEL_REF_EXPLS_S.NEXTVAL INTO v_next_id FROM dual;
SELECT model_ref_expl_id INTO v_expl_node_id FROM CZ_MODEL_REF_EXPLS
WHERE component_id=p_model_id AND model_id=p_model_id AND
parent_expl_node_id IS NULL AND deleted_flag=NO_FLAG;
SELECT YES_FLAG INTO p_circularity_exists FROM dual WHERE
EXISTS
(SELECT component_id FROM CZ_MODEL_REF_EXPLS
WHERE model_id=p_target_model_id AND deleted_flag=NO_FLAG
AND ps_node_type IN (REFERENCE_TYPE,CONNECTOR_TYPE) AND component_id=p_source_model_id) OR
(p_target_model_id=p_source_model_id);
t_upd_node_depth_models.DELETE;
PROCEDURE update_Node_Depth(p_model_id IN INTEGER DEFAULT NULL) IS
t_nodes_to_delete_tbl IntArray;
PROCEDURE update_Node_Depth_
(p_model_id IN INTEGER,
p_expl_id IN INTEGER DEFAULT NULL,
p_node_depth IN INTEGER DEFAULT NULL,
p_ps_node_id IN INTEGER DEFAULT NULL,
p_ps_node_type IN INTEGER DEFAULT NULL) IS
v_node_depth INTEGER;
UPDATE CZ_MODEL_REF_EXPLS
SET node_depth=0
WHERE model_id=p_model_id AND parent_expl_node_id IS NULL
AND deleted_flag=NO_FLAG
RETURNING model_ref_expl_id INTO v_model_ref_expl_id;
update_Node_Depth_(p_model_id,v_model_ref_expl_id,0);
FOR i IN(SELECT model_ref_expl_id,referring_node_id,ps_node_type FROM CZ_MODEL_REF_EXPLS
WHERE model_id=p_model_id AND parent_expl_node_id=p_expl_id
AND deleted_flag=NO_FLAG)
LOOP
UPDATE CZ_MODEL_REF_EXPLS
SET node_depth=v_node_depth
WHERE model_ref_expl_id=i.model_ref_expl_id AND node_depth<>v_node_depth;
t_nodes_to_delete_tbl(t_nodes_to_delete_tbl.COUNT+1):=i.model_ref_expl_id;
update_Node_Depth_(p_model_id,i.model_ref_expl_id,v_node_depth,i.referring_node_id,i.ps_node_type);
END update_Node_Depth_;
t_nodes_to_delete_tbl.DELETE;
update_Node_Depth_(t_upd_node_depth_models(i));
IF t_nodes_to_delete_tbl.COUNT>0 THEN
FORALL i IN t_nodes_to_delete_tbl.FIRST..t_nodes_to_delete_tbl.LAST
UPDATE CZ_MODEL_REF_EXPLS
SET deleted_flag=YES_FLAG
WHERE model_ref_expl_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
START WITH model_id=t_upd_node_depth_models(j) AND model_ref_expl_id=t_nodes_to_delete_tbl(i) AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG
AND PRIOR deleted_flag=NO_FLAG);
FOR i IN(SELECT DISTINCT model_id FROM cz_model_ref_expls
WHERE ((component_id=p_model_id AND
ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE)) OR model_id=p_model_id)
AND deleted_flag=NO_FLAG)
LOOP
update_Node_Depth_(i.model_id);
IF t_nodes_to_delete_tbl.COUNT>0 THEN
FORALL i IN t_nodes_to_delete_tbl.FIRST..t_nodes_to_delete_tbl.LAST
UPDATE CZ_MODEL_REF_EXPLS
SET deleted_flag=YES_FLAG
WHERE model_ref_expl_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
START WITH model_id=p_model_id AND model_ref_expl_id=t_nodes_to_delete_tbl(i) AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG
AND PRIOR deleted_flag=NO_FLAG);
END update_Node_Depth;
SELECT instantiable_flag INTO v_instantiable_flag
FROM CZ_PS_NODES
WHERE ps_node_id=p_ps_node_id;
SELECT 1 INTO v_iret FROM dual
WHERE
EXISTS(SELECT NULL FROM cz_expression_nodes x
WHERE deleted_flag = NO_FLAG
AND consequent_flag =YES_FLAG
AND ps_node_id = p_ps_node_id
AND EXISTS
(SELECT NULL FROM cz_rules WHERE (antecedent_id=x.express_id OR consequent_id=x.express_id
OR amount_id=x.express_id) AND disabled_flag=NO_FLAG
AND devl_project_id=p_model_id AND deleted_flag=NO_FLAG));
SELECT expr_parent_id INTO v_expr_parent
FROM CZ_EXPRESSION_NODES WHERE expr_node_id=p_expr_node_id AND
deleted_flag=NO_FLAG;
SELECT expr_subtype INTO v_expr_subtype
FROM CZ_EXPRESSION_NODES WHERE expr_parent_id=v_expr_parent AND ps_node_id IS NULL AND
deleted_flag=NO_FLAG;
FOR i IN (SELECT rule_id,antecedent_id,consequent_id,NAME FROM CZ_RULES
WHERE devl_project_id=p_model_id AND disabled_flag=NO_FLAG
AND deleted_flag=NO_FLAG)
LOOP
BEGIN
SELECT expr_parent_id INTO v_expr_parent_id FROM CZ_EXPRESSION_NODES
WHERE ps_node_id=p_ps_node_id AND deleted_flag=NO_FLAG AND
rule_id=i.rule_id;
SELECT expr_subtype INTO v_expr_subtype
FROM CZ_EXPRESSION_NODES WHERE expr_parent_id=v_expr_parent_id AND ps_node_id IS NULL AND
deleted_flag=NO_FLAG;
INSERT INTO CZ_MODEL_REF_EXPLS
(model_ref_expl_id,
parent_expl_node_id,
referring_node_id,
model_id,
component_id,
ps_node_type,
virtual_flag,
node_depth,
expl_node_type,
deleted_flag)
VALUES (v_expl_id,
NULL,
NULL,
p_ps_node_id,
p_ps_node_id,
p_ps_node_type,
YES_FLAG,
0,
MANDATORY_EXPL_TYPE,
NO_FLAG);
FOR i IN(SELECT DISTINCT devl_project_id FROM CZ_PS_NODES a
WHERE reference_id=p_id AND deleted_flag=NO_FLAG AND
devl_project_id IN(SELECT object_id FROM CZ_RP_ENTRIES
WHERE object_id=a.devl_project_id AND object_type='PRJ' AND deleted_flag=NO_FLAG))
LOOP
v_ind:=t_chain.COUNT+1;
FOR m IN(SELECT model_ref_expl_id,parent_expl_node_id,component_id,referring_node_id
FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=p_root_expl_id
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag='0' AND PRIOR deleted_flag='0')
LOOP
IF m.referring_node_id IS NULL THEN
IF x_paths_tbl.EXISTS(m.parent_expl_node_id) THEN
IF x_paths_tbl(m.parent_expl_node_id)='.' THEN
x_paths_tbl(m.model_ref_expl_id) := TO_CHAR(m.component_id);
FOR i IN (SELECT parent_expl_node_id,referring_node_id,ps_node_type,
virtual_flag,component_id,expl_node_type,model_ref_expl_id,LEVEL
FROM CZ_MODEL_REF_EXPLS
START WITH model_id=p_target_model_id AND component_id=p_target_model_id -- this condition specifies root of expl tree
AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG)
LOOP
IF i.parent_expl_node_id IS NULL THEN
px_root_expl_id:=i.model_ref_expl_id;
FOR i IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
WHERE model_id=p_target_model_id AND deleted_flag=NO_FLAG AND
ps_node_type IN(CONNECTOR_TYPE,REFERENCE_TYPE) )
LOOP
FOR j IN (SELECT model_ref_expl_id,ps_node_type
FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id = i.model_ref_expl_id AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG)
LOOP
t_model_ref_expl_ids_tbl(t_model_ref_expl_ids_tbl.COUNT+1):=j.model_ref_expl_id;
x_subtree_tbl.DELETE(t_model_ref_expl_ids_tbl(i));
INSERT INTO CZ_MODEL_REF_EXPLS
(model_ref_expl_id,
parent_expl_node_id,
referring_node_id,
model_id,
component_id,
child_model_expl_id,
ps_node_type,
virtual_flag,
node_depth,
expl_node_type,
deleted_flag)
VALUES
(t_subtree_tbl(v_ind).new_id,
v_parent_id,
t_subtree_tbl(v_ind).ps_node_id,
p_model_id,
t_subtree_tbl(v_ind).component_id,
NULL,
t_subtree_tbl(v_ind).ps_node_type,
t_subtree_tbl(v_ind).virtual_flag,
v_level,
t_subtree_tbl(v_ind).expl_node_type,
NO_FLAG);
t_subtree_tbl.DELETE;
FOR i IN(SELECT ps_node_id, devl_project_id FROM CZ_PS_NODES a
WHERE reference_id=p_model_id AND deleted_flag='0'
AND EXISTS (SELECT NULL FROM cz_devl_projects
WHERE devl_project_id = a.devl_project_id
AND deleted_flag='0'))
LOOP
FOR h IN (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
WHERE model_id=i.devl_project_id AND
child_model_expl_id=p_new_root_expl_id AND deleted_flag='0')
LOOP
g_hash_tree_tbl(h.model_ref_expl_id) := t_next_level_expl_ids_tbl;
t_next_level_expl_ids_tbl.DELETE;
UPDATE CZ_MODEL_REF_EXPLS
SET child_model_expl_id=t_attach_subtree_tbl(v_expl_id).new_id
WHERE model_ref_expl_id=l_new_expl_id;
l_expl_paths_tbl.DELETE;
g_hash_tree_tbl.DELETE;
g_root_point_tree_tbl.DELETE;
FOR m IN(SELECT model_ref_expl_id,component_id,ps_node_type,node_depth
FROM CZ_MODEL_REF_EXPLS
WHERE model_id=t_chain(i).model_id AND component_id=p_parent_ps_id AND
parent_expl_node_id IS NOT NULL AND deleted_flag=NO_FLAG)
LOOP
BEGIN
v_connector_parent_exists:=NO_FLAG;
SELECT YES_FLAG INTO v_connector_parent_exists FROM dual
WHERE EXISTS(SELECT NULL FROM CZ_MODEL_REF_EXPLS
WHERE ps_node_type=CONNECTOR_TYPE
START WITH model_ref_expl_id=m.model_ref_expl_id
CONNECT BY PRIOR parent_expl_node_id=model_ref_expl_id AND deleted_flag=NO_FLAG AND deleted_flag=NO_FLAG);
t_models_level:=t_new_models_level; t_new_models_level.DELETE;
FOR i IN (SELECT model_ref_expl_id,node_depth FROM CZ_MODEL_REF_EXPLS
WHERE model_id=p_project_id AND referring_node_id=p_ps_node_id
AND child_model_expl_id IS NULL AND deleted_flag=NO_FLAG)
LOOP
p_out_expl_id:=i.model_ref_expl_id;
FOR i IN (SELECT model_ref_expl_id,node_depth FROM CZ_MODEL_REF_EXPLS
WHERE model_id=p_project_id AND referring_node_id IS NULL
AND component_id=p_ps_node_id AND child_model_expl_id IS NULL AND
deleted_flag=NO_FLAG)
LOOP
p_out_expl_id:=i.model_ref_expl_id;
FOR i IN (SELECT ps_node_id,ps_node_type,parent_id FROM CZ_PS_NODES
WHERE ps_node_id=p_ps_node_id AND deleted_flag=NO_FLAG)
LOOP
get_Expl_Id(i.ps_node_id,p_project_id,v_expl_id,p_out_level,i.ps_node_type);
FOR i IN (SELECT ps_node_id,parent_id FROM CZ_PS_NODES
WHERE parent_id=p_ps_node_id AND deleted_flag=NO_FLAG)
LOOP
get_Expl_Id(i.ps_node_id,p_project_id,v_expl_id,p_out_level);
SELECT model_ref_expl_id INTO v_ref_id FROM CZ_MODEL_REF_EXPLS
WHERE parent_expl_node_id=p_expl_id
AND (child_model_expl_id=p_child_expl_id OR model_ref_expl_id=p_child_expl_id)
AND deleted_flag=NO_FLAG;
SELECT parent_expl_node_id INTO v_ref_id FROM CZ_MODEL_REF_EXPLS
WHERE model_ref_expl_id=p_expl_id;
p_deleted_expl_id IN INTEGER DEFAULT NULL) IS
v_model_ref_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE ;
SELECT model_ref_expl_id INTO v_model_ref_id FROM
CZ_MODEL_REF_EXPLS WHERE model_id=p_project_id
AND component_id=p_ps_node_id AND child_model_expl_id IS NULL AND deleted_flag=NO_FLAG;
v_model_ref_id:=p_deleted_expl_id;
FOR i IN (SELECT ps_node_id,ps_node_type,NAME FROM CZ_PS_NODES
START WITH devl_project_id=p_project_id AND deleted_flag=NO_FLAG AND ps_node_id=p_ps_node_id
CONNECT BY PRIOR ps_node_id=parent_id AND NVL(virtual_flag,'1')='1' AND deleted_flag=NO_FLAG
AND PRIOR deleted_flag=NO_FLAG)
LOOP
--
-- DEBUG ERROR CODE --
--
ERROR_CODE:=402;
FOR m IN(SELECT expr_node_id,rule_id,model_ref_expl_id FROM CZ_EXPRESSION_NODES
WHERE ps_node_id=i.ps_node_id AND deleted_flag=NO_FLAG)
LOOP
BEGIN
--
-- DEBUG ERROR CODE --
--
ERROR_CODE:=403;
UPDATE CZ_EXPRESSION_NODES
SET model_ref_expl_id=v_ref_id
WHERE expr_node_id=m.expr_node_id AND rule_id=m.rule_id;
FOR m IN(SELECT func_comp_id,component_id,model_ref_expl_id FROM CZ_FUNC_COMP_SPECS
WHERE component_id=i.ps_node_id AND deleted_flag=NO_FLAG)
LOOP
BEGIN
--
-- DEBUG ERROR CODE --
--
ERROR_CODE:=407;
UPDATE CZ_FUNC_COMP_SPECS
SET model_ref_expl_id=v_ref_id
WHERE func_comp_id=m.func_comp_id AND component_id=m.component_id;
FOR m IN(SELECT rule_id,feature_id,model_ref_expl_id FROM CZ_COMBO_FEATURES
WHERE feature_id=i.ps_node_id AND deleted_flag=NO_FLAG)
LOOP
BEGIN
--
-- DEBUG ERROR CODE --
--
ERROR_CODE:=411;
UPDATE CZ_COMBO_FEATURES
SET model_ref_expl_id=v_ref_id
WHERE rule_id=m.rule_id AND feature_id=m.feature_id;
FOR m IN(SELECT rule_id,feature_id,model_ref_expl_id FROM CZ_DES_CHART_FEATURES
WHERE feature_id=i.ps_node_id AND deleted_flag=NO_FLAG)
LOOP
BEGIN
--
-- DEBUG ERROR CODE --
--
ERROR_CODE:=415;
UPDATE CZ_DES_CHART_FEATURES
SET model_ref_expl_id=v_ref_id
WHERE rule_id=m.rule_id AND feature_id=m.feature_id;
FOR m IN(SELECT rule_id,primary_opt_id,secondary_opt_id,secondary_feature_id,
secondary_feat_expl_id FROM CZ_DES_CHART_CELLS
WHERE secondary_feature_id=i.ps_node_id AND deleted_flag=NO_FLAG)
LOOP
BEGIN
v_ref_id:=get_Next_ExplId(m.secondary_feat_expl_id,v_model_ref_id,p_mode);
UPDATE CZ_DES_CHART_CELLS
SET secondary_feat_expl_id=v_ref_id
WHERE rule_id=m.rule_id AND secondary_feature_id=m.secondary_feature_id AND
primary_opt_id=m.primary_opt_id AND secondary_opt_id=m.secondary_opt_id;
FOR m IN(SELECT rule_id,option_id,model_ref_expl_id FROM CZ_DES_CHART_COLUMNS
WHERE option_id=i.ps_node_id)
LOOP
BEGIN
v_ref_id:=get_Next_ExplId(m.model_ref_expl_id,v_model_ref_id,p_mode);
UPDATE CZ_DES_CHART_COLUMNS
SET model_ref_expl_id=v_ref_id
WHERE rule_id=m.rule_id AND option_id=m.option_id;
UPDATE CZ_UI_PAGE_ELEMENTS
SET model_ref_expl_id=p_new_expl_id
WHERE ui_def_id=p_current_ui_def_id AND
persistent_node_id=t_persistent_node_id_tbl(k) AND
model_ref_expl_id=p_old_expl_id AND
deleted_flag='0';
UPDATE CZ_UI_PAGES
SET pagebase_expl_node_id=p_new_expl_id
WHERE ui_def_id=p_current_ui_def_id AND
persistent_node_id=t_persistent_node_id_tbl(k) AND
pagebase_expl_node_id=p_old_expl_id AND
deleted_flag='0';
UPDATE CZ_UI_PAGE_REFS
SET target_expl_node_id=p_new_expl_id
WHERE ui_def_id=p_current_ui_def_id AND
target_persistent_node_id=t_persistent_node_id_tbl(k) AND
target_expl_node_id=p_old_expl_id AND
deleted_flag='0';
UPDATE CZ_UI_PAGE_SETS
SET pagebase_expl_node_id=p_new_expl_id
WHERE ui_def_id=p_current_ui_def_id AND
persistent_node_id=t_persistent_node_id_tbl(k) AND
pagebase_expl_node_id=p_old_expl_id AND
deleted_flag='0';
UPDATE CZ_UI_ACTIONS
SET target_expl_node_id=p_new_expl_id
WHERE ui_def_id=p_current_ui_def_id AND
target_persistent_node_id=t_persistent_node_id_tbl(k) AND
target_expl_node_id=p_old_expl_id AND
deleted_flag='0';
FOR i IN (SELECT ui_def_id FROM CZ_UI_DEFS
WHERE devl_project_id=p_current_model_id AND
ui_style='7' AND deleted_flag='0')
LOOP
refresh_It_For_Single_UI(i.ui_def_id);
SELECT persistent_node_id
BULK COLLECT INTO t_persistent_node_id_tbl
FROM CZ_PS_NODES
START WITH ps_node_id=p_ps_node_id AND deleted_flag='0' AND
(reference_id IS NULL AND component_id=p_component_id)
CONNECT BY PRIOR ps_node_id=parent_id AND deleted_flag='0' AND
PRIOR deleted_flag='0' AND (reference_id IS NULL AND component_id=p_component_id);
PROCEDURE delete_it
(p_expl_id IN INTEGER,
p_del_logically IN VARCHAR2 -- DEFAULT YES_FLAG
) IS
BEGIN
IF p_del_logically=YES_FLAG THEN
--
-- DEBUG ERROR CODE --
--
ERROR_CODE:=500;
UPDATE CZ_MODEL_REF_EXPLS SET deleted_flag=YES_FLAG WHERE model_ref_expl_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=p_expl_id AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG);
DELETE FROM CZ_MODEL_REF_EXPLS WHERE model_ref_expl_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=p_expl_id AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG);
PROCEDURE delete_Node_
(p_ps_node_id IN INTEGER,
p_ps_node_type IN INTEGER,
p_del_logically IN VARCHAR2 -- DEFAULT '1'
) IS
PROCEDURE delete_it_
(p_expl_id IN INTEGER,
p_del_logically IN VARCHAR2, -- DEFAULT YES_FLAG
p_ps_node_type IN INTEGER DEFAULT NULL) IS
BEGIN
IF p_del_logically=YES_FLAG THEN
UPDATE CZ_MODEL_REF_EXPLS SET deleted_flag=YES_FLAG
WHERE model_ref_expl_id=p_expl_id;
DELETE FROM CZ_MODEL_REF_EXPLS WHERE model_ref_expl_id=p_expl_id;
UPDATE CZ_MODEL_REF_EXPLS SET deleted_flag=YES_FLAG
WHERE deleted_flag=NO_FLAG AND model_ref_expl_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=p_expl_id AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND
PRIOR deleted_flag=NO_FLAG);
DELETE FROM CZ_MODEL_REF_EXPLS
WHERE model_ref_expl_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=p_expl_id AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG);
END delete_it_;
FOR i IN (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
WHERE referring_node_id=p_ps_node_id AND deleted_flag=NO_FLAG)
LOOP
delete_it_(i.model_ref_expl_id, YES_FLAG);
FOR i IN (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
WHERE component_id=p_ps_node_id AND deleted_flag=NO_FLAG)
LOOP
delete_it_(i.model_ref_expl_id, YES_FLAG);
END delete_Node_;
FOR i IN(SELECT model_ref_expl_id,referring_node_id,component_id,expl_node_type FROM CZ_MODEL_REF_EXPLS
START WITH model_id=p_model_id AND parent_expl_node_id IS NULL AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND
deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG
AND ps_node_type IN(BOM_MODEL_TYPE,COMPONENT_TYPE,REFERENCE_TYPE)
AND PRIOR ps_node_type IN(BOM_MODEL_TYPE,COMPONENT_TYPE,REFERENCE_TYPE))
LOOP
BEGIN
SELECT '1' INTO v_exist_bom_nodes
FROM dual WHERE
EXISTS(SELECT NULL FROM CZ_PS_NODES WHERE devl_project_id=i.component_id
AND ps_node_type=436 AND deleted_flag=NO_FLAG);
SELECT parent_id INTO v_parent_id
FROM CZ_PS_NODES WHERE ps_node_id=p_ps_node_id;
SELECT model_ref_expl_id,node_depth
INTO p_out_expl_id,p_out_level
FROM CZ_MODEL_REF_EXPLS WHERE model_id=p_ps_node_id
AND parent_expl_node_id IS NULL AND deleted_flag=NO_FLAG;
FOR i IN (SELECT ps_node_id,parent_id,virtual_flag FROM CZ_PS_NODES
WHERE ps_node_id=v_parent_id AND deleted_flag=NO_FLAG)
LOOP
IF i.virtual_flag=NO_FLAG OR i.parent_id IS NULL THEN
p_out_ps_node_id:=i.ps_node_id;
SELECT model_ref_expl_id,node_depth
INTO p_out_expl_id,p_out_level
FROM CZ_MODEL_REF_EXPLS WHERE model_id=p_project_id
AND component_id=i.ps_node_id AND deleted_flag=NO_FLAG;
PROCEDURE update_Rules(p_ps_node_id IN INTEGER) IS
v_old_expl_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
FOR n IN(SELECT ps_node_id FROM CZ_PS_NODES
START WITH ps_node_id=p_ps_node_id AND deleted_flag=NO_FLAG
CONNECT BY PRIOR ps_node_id=parent_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG)
LOOP
UPDATE CZ_EXPRESSION_NODES SET model_ref_expl_id=v_new_expl_id
WHERE model_ref_expl_id=v_old_expl_id AND deleted_flag=NO_FLAG
AND ps_node_id=n.ps_node_id;
UPDATE CZ_COMBO_FEATURES
SET model_ref_expl_id=v_new_expl_id
WHERE model_ref_expl_id=v_old_expl_id AND deleted_flag=NO_FLAG
AND feature_id=n.ps_node_id;
UPDATE CZ_DES_CHART_FEATURES
SET model_ref_expl_id=v_new_expl_id
WHERE model_ref_expl_id=v_old_expl_id AND deleted_flag=NO_FLAG
AND feature_id=n.ps_node_id;
UPDATE CZ_DES_CHART_CELLS
SET secondary_feat_expl_id=v_new_expl_id
WHERE secondary_feat_expl_id=v_old_expl_id AND deleted_flag=NO_FLAG
AND (secondary_feature_id=n.ps_node_id OR
primary_opt_id=n.ps_node_id OR secondary_opt_id=n.ps_node_id);
UPDATE CZ_FUNC_COMP_SPECS
SET model_ref_expl_id=v_new_expl_id
WHERE model_ref_expl_id=v_old_expl_id AND deleted_flag=NO_FLAG AND
component_id=n.ps_node_id;
UPDATE CZ_DES_CHART_COLUMNS
SET model_ref_expl_id=v_new_expl_id
WHERE model_ref_expl_id=v_old_expl_id AND
option_id=n.ps_node_id;
FOR l IN(SELECT model_ref_expl_id,node_depth
FROM CZ_MODEL_REF_EXPLS
WHERE model_id=t_chain(i).model_id AND
child_model_expl_id=p_parent_ref_expl_id
AND deleted_flag=NO_FLAG)
LOOP
--
-- generate the next PK for CZ_MODEL_REF_EXPLS table
--
v_expl_id:=allocate_Expl_Id;
INSERT INTO CZ_MODEL_REF_EXPLS
(model_ref_expl_id,
parent_expl_node_id,
referring_node_id,
model_id,
component_id,
ps_node_type,
virtual_flag,
node_depth,
expl_node_type,
child_model_expl_id,
deleted_flag)
VALUES
(v_expl_id,
l.model_ref_expl_id,
NULL,
v_model_id,
p_ps_node_id,
p_ps_node_type,
NO_FLAG,
l.node_depth+1,
p_expl_node_type,
p_model_ref_expl_id,
NO_FLAG);
t_child_nodes.DELETE;
UPDATE CZ_MODEL_REF_EXPLS
SET parent_expl_node_id=v_expl_id
WHERE parent_expl_node_id=l.model_ref_expl_id
AND model_id=v_model_id AND
((ps_node_type IN(PRODUCT_TYPE,COMPONENT_TYPE,BOM_MODEL_TYPE) AND component_id=v_ps_node_id)
OR (ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) AND referring_node_id=v_ps_node_id))
AND deleted_flag=NO_FLAG
RETURNING model_ref_expl_id BULK COLLECT INTO t_child_nodes ;
UPDATE CZ_MODEL_REF_EXPLS
SET node_depth=node_depth+1
WHERE model_ref_expl_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=t_child_nodes(h) AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG
AND PRIOR deleted_flag=NO_FLAG);
FOR l IN(SELECT model_ref_expl_id,node_depth
FROM CZ_MODEL_REF_EXPLS
WHERE model_id=t_chain(i).model_id AND
child_model_expl_id=p_parent_model_ref_expl_id
AND deleted_flag=NO_FLAG)
LOOP
FOR m IN(SELECT *
FROM CZ_MODEL_REF_EXPLS
WHERE model_ref_expl_id = p_model_ref_expl_id
AND deleted_flag=NO_FLAG)
LOOP
--
-- generate the next PK for CZ_MODEL_REF_EXPLS table
--
v_expl_id:=allocate_Expl_Id;
INSERT INTO CZ_MODEL_REF_EXPLS
(model_ref_expl_id,
parent_expl_node_id,
referring_node_id,
model_id,
component_id,
ps_node_type,
virtual_flag,
node_depth,
expl_node_type,
child_model_expl_id,
deleted_flag)
VALUES
(v_expl_id,
l.model_ref_expl_id,
m.referring_node_id,
v_model_id,
m.component_id,
m.ps_node_type,
m.virtual_flag,
l.node_depth+1,
m.expl_node_type,
p_model_ref_expl_id,
NO_FLAG);
FOR i IN (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS WHERE parent_expl_node_id = p_model_ref_expl_id AND deleted_flag = NO_FLAG)
LOOP
populate_parent_expl_tree_(p_model_id => p_model_id, p_model_ref_expl_id => i.model_ref_expl_id, p_parent_model_ref_expl_id => p_model_ref_expl_id);
SELECT devl_project_id,parent_id,reference_id,component_id,virtual_flag, ps_node_type
INTO l_model_id,l_parent_id,l_reference_id,l_component_id,l_virtual_flag, l_ps_node_type
FROM CZ_PS_NODES
WHERE ps_node_id=p_ps_node_id AND deleted_flag = NO_FLAG;
SELECT model_ref_expl_id, node_depth INTO l_expl_id, l_curr_node_depth
FROM CZ_MODEL_REF_EXPLS
WHERE model_id=l_model_id AND component_id=l_component_id AND
child_model_expl_id IS NULL AND deleted_flag = NO_FLAG;
SELECT MIN(node_depth) INTO l_min_node_depth
FROM CZ_MODEL_REF_EXPLS
WHERE model_id=l_model_id AND referring_node_id=p_ps_node_id
AND deleted_flag = NO_FLAG;
SELECT model_ref_expl_id, node_depth INTO l_expl_id, l_curr_node_depth
FROM CZ_MODEL_REF_EXPLS
WHERE model_id=l_model_id AND referring_node_id=p_ps_node_id AND
node_depth=l_min_node_depth AND deleted_flag = NO_FLAG;
t_chain.DELETE;t_projectCache.DELETE;
FOR i IN(SELECT model_ref_expl_id, node_depth FROM CZ_MODEL_REF_EXPLS
WHERE parent_expl_node_id=v_up_expl_id AND
deleted_flag = NO_FLAG AND
(referring_node_id IS NULL AND component_id IN
(SELECT ps_node_id FROM CZ_PS_NODES
START WITH ps_node_id=p_ps_node_id
CONNECT BY PRIOR ps_node_id=parent_id AND
deleted_flag = NO_FLAG AND PRIOR deleted_flag = NO_FLAG)) OR
(referring_node_id IS NOT NULL AND referring_node_id IN
(SELECT ps_node_id FROM CZ_PS_NODES
START WITH ps_node_id=p_ps_node_id
CONNECT BY PRIOR ps_node_id=parent_id AND
deleted_flag = NO_FLAG AND PRIOR deleted_flag = NO_FLAG)))
LOOP
populate_parent_expl_tree_(p_model_id => p_model_id,
p_model_ref_expl_id => i.model_ref_expl_id,
p_parent_model_ref_expl_id => v_up_expl_id);
PROCEDURE update_child_nodes(p_model_id IN NUMBER) IS
TYPE t_varchar2_tbl_type IS TABLE OF VARCHAR2(32000) INDEX BY BINARY_INTEGER;
l_model_updated_tbl IntArrayIndexVC2;
l_num_updated_records NUMBER := 0;
PROCEDURE update_layer
(
p_current_model_id IN NUMBER
) IS
l_paths_tbl t_varchar2_tbl_type;
IF l_model_updated_tbl.EXISTS(p_current_model_id) THEN
RETURN;
l_model_updated_tbl(p_current_model_id) := p_current_model_id;
FOR i IN(SELECT model_ref_expl_id, referring_node_id, component_id
FROM CZ_MODEL_REF_EXPLS a
WHERE a.model_id=p_current_model_id AND a.deleted_flag=NO_FLAG)
LOOP
IF i.referring_node_id IS NULL THEN
l_paths_tbl(i.model_ref_expl_id) := TO_CHAR(i.component_id);
FOR m IN(SELECT model_ref_expl_id,component_id,referring_node_id
FROM CZ_MODEL_REF_EXPLS
WHERE model_ref_expl_id<>i.model_ref_expl_id
START WITH model_ref_expl_id=i.model_ref_expl_id
CONNECT BY PRIOR parent_expl_node_id=model_ref_expl_id AND deleted_flag=NO_FLAG)
LOOP
IF m.referring_node_id IS NULL THEN
l_paths_tbl(i.model_ref_expl_id) := l_paths_tbl(i.model_ref_expl_id) || ':' ||TO_CHAR(m.component_id);
FOR n IN (SELECT * FROM CZ_PS_NODES a
WHERE reference_id=p_current_model_id AND
ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) AND deleted_flag=NO_FLAG AND
devl_project_id IN(SELECT object_id FROM CZ_RP_ENTRIES
WHERE object_id=a.devl_project_id AND object_type='PRJ' AND deleted_flag=NO_FLAG)
)
LOOP
l_new_expl_path_tbl.DELETE;
SELECT model_ref_expl_id INTO l_ref_expl_id FROM CZ_MODEL_REF_EXPLS
WHERE model_id=n.devl_project_id AND referring_node_id=n.ps_node_id;
l_new_paths_tbl.DELETE;
FOR k IN(SELECT model_ref_expl_id,component_id,
referring_node_id,ps_node_type,child_model_expl_id,model_id
FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=l_ref_expl_id AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND
deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG)
LOOP
IF k.referring_node_id IS NULL THEN
l_new_paths_tbl(k.model_ref_expl_id) := TO_CHAR(k.component_id);
FOR kk IN(SELECT model_ref_expl_id,component_id,referring_node_id
FROM CZ_MODEL_REF_EXPLS
WHERE model_ref_expl_id<>k.model_ref_expl_id
START WITH model_ref_expl_id=k.model_ref_expl_id
CONNECT BY PRIOR parent_expl_node_id=model_ref_expl_id AND deleted_flag=NO_FLAG)
LOOP
IF kk.referring_node_id IS NULL THEN
l_new_paths_tbl(k.model_ref_expl_id) := l_new_paths_tbl(k.model_ref_expl_id) || ':' ||TO_CHAR(kk.component_id);
SELECT child_model_expl_id INTO l_child_expl_id FROM CZ_MODEL_REF_EXPLS
WHERE model_ref_expl_id=l_loop_ind;
l_num_updated_records := l_num_updated_records + 1;
UPDATE CZ_MODEL_REF_EXPLS
SET child_model_expl_id=l_expl_path_tbl(l_new_paths_tbl(l_loop_ind))
WHERE model_ref_expl_id=l_loop_ind;
update_layer(l_next_model_level_tbl(v));
END update_layer;
FOR i IN(SELECT DISTINCT component_id FROM CZ_MODEL_REF_EXPLS a
WHERE model_id=p_model_id AND deleted_flag=NO_FLAG AND
ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) AND NOT EXISTS
(SELECT NULL FROM CZ_MODEL_REF_EXPLS
WHERE model_id=a.component_id AND ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) AND deleted_flag=NO_FLAG))
LOOP
update_layer(i.component_id);
END update_child_nodes;
PROCEDURE delete_duplicates(p_model_id IN NUMBER) IS
BEGIN
FOR i IN (SELECT DISTINCT model_id FROM CZ_MODEL_REF_EXPLS
WHERE component_id=p_model_id AND referring_node_id IS NOT NULL
AND deleted_flag=NO_FLAG)
LOOP
UPDATE CZ_MODEL_REF_EXPLS a
SET deleted_flag = '1'
WHERE model_id=i.model_id AND deleted_flag=NO_FLAG
AND referring_node_id IS NOT NULL
AND parent_expl_node_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
WHERE model_id=i.model_id AND deleted_flag=NO_FLAG
AND referring_node_id=a.referring_node_id);
FOR i IN (SELECT DISTINCT component_id FROM CZ_MODEL_REF_EXPLS
WHERE model_id=p_model_id AND referring_node_id IS NOT NULL
AND deleted_flag=NO_FLAG)
LOOP
UPDATE CZ_MODEL_REF_EXPLS a
SET deleted_flag = '1'
WHERE model_id=i.component_id AND deleted_flag=NO_FLAG
AND referring_node_id IS NOT NULL
AND parent_expl_node_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
WHERE model_id=i.component_id AND deleted_flag=NO_FLAG
AND referring_node_id=a.referring_node_id);
END delete_duplicates;
FOR i IN(SELECT ps_node_id,parent_id,ps_node_type,virtual_flag,devl_project_id,reference_id FROM CZ_PS_NODES
START WITH devl_project_id=p_model_id AND parent_id IS NULL and deleted_flag='0'
CONNECT BY PRIOR ps_node_id=parent_id AND deleted_flag='0' AND PRIOR deleted_flag='0')
LOOP
IF i.ps_node_type IN (REFERENCE_TYPE,CONNECTOR_TYPE) THEN
UPDATE CZ_PS_NODES SET component_id=i.reference_id
WHERE ps_node_id=i.ps_node_id AND component_id<>i.reference_id;
UPDATE CZ_PS_NODES SET component_id=i.ps_node_id
WHERE ps_node_id=i.ps_node_id AND component_id<>i.ps_node_id;
SELECT ps_node_id,parent_id INTO v_ps_node_id,v_parent_id FROM CZ_PS_NODES a
WHERE devl_project_id=i.devl_project_id AND
EXISTS(SELECT NULL FROM CZ_PS_NODES WHERE ps_node_id=a.parent_id AND
(virtual_flag='0' OR parent_id IS NULL) AND deleted_flag='0')
START WITH ps_node_id=i.ps_node_id
CONNECT BY PRIOR parent_id=ps_node_id AND deleted_flag='0' AND NVL(virtual_flag,'1')='1';
UPDATE CZ_PS_NODES SET component_id=v_ps_id
WHERE ps_node_id=i.ps_node_id AND component_id<>v_ps_id;
SELECT instantiable_flag,ps_node_type,minimum,maximum,component_id
INTO v_instantiable_flag,v_ps_node_type,v_minimum,v_maximum,v_component_id
FROM CZ_PS_NODES
WHERE ps_node_id=p_ps_node_id;
UPDATE CZ_PS_NODES
SET minimum=p_minimum,
maximum=p_maximum,
instantiable_flag=v_instantiable_flag
WHERE ps_node_id=p_ps_node_id;
SELECT model_ref_expl_id,parent_expl_node_id,ps_node_type,virtual_flag,expl_node_type
INTO v_model_ref_expl_id,v_parent_expl_node_id,v_ps_node_type,v_virtual_flag,v_curr_expl_node_type
FROM CZ_MODEL_REF_EXPLS
WHERE ((component_id=p_ps_node_id AND referring_node_id IS NULL) OR
referring_node_id=p_ps_node_id)
AND model_id=p_model_id AND deleted_flag=NO_FLAG AND ROWNUM<2;
UPDATE CZ_PS_NODES
SET component_id=p_model_id,
minimum=p_minimum,
maximum=p_maximum,
instantiable_flag=MANDATORY_EXPL_TYPE
WHERE devl_project_id=p_model_id AND
parent_id is null AND deleted_flag='0';
UPDATE CZ_PS_NODES
SET virtual_flag=p_out_virtual_flag,
minimum=p_minimum,
maximum=p_maximum
WHERE ps_node_id=p_ps_node_id;
UPDATE CZ_MODEL_REF_EXPLS
SET virtual_flag=p_out_virtual_flag,
expl_node_type=v_expl_node_type
WHERE component_id=p_ps_node_id AND deleted_flag=NO_FLAG;
UPDATE CZ_MODEL_REF_EXPLS
SET virtual_flag=p_out_virtual_flag,
expl_node_type=v_expl_node_type
WHERE referring_node_id=p_ps_node_id AND deleted_flag=NO_FLAG;
t_chain.DELETE;t_projectCache.DELETE;
FOR x IN (SELECT model_ref_expl_id,parent_expl_node_id,model_id FROM CZ_MODEL_REF_EXPLS a
WHERE component_id=p_ps_node_id AND deleted_flag=NO_FLAG AND
model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG))
LOOP
BEGIN
IF x.model_id=p_model_id THEN
v_del_ref_id:=x.model_ref_expl_id;
FOR y IN (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=x.model_ref_expl_id AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND
PRIOR deleted_flag=NO_FLAG)
LOOP
UPDATE CZ_MODEL_REF_EXPLS SET node_depth=node_depth-1
WHERE model_ref_expl_id=y.model_ref_expl_id;
UPDATE CZ_MODEL_REF_EXPLS SET parent_expl_node_id=x.parent_expl_node_id
WHERE parent_expl_node_id=x.model_ref_expl_id
AND model_id=x.model_id AND deleted_flag=NO_FLAG;
delete_Node_(p_ps_node_id,COMPONENT_TYPE, '1');
INSERT INTO CZ_MODEL_REF_EXPLS
(model_ref_expl_id,
parent_expl_node_id,
referring_node_id,
model_id,
component_id,
ps_node_type,
virtual_flag,
node_depth,
expl_node_type,
deleted_flag)
VALUES
(v_expl_id,
v_up_expl_id,
NULL,
p_model_id,
p_ps_node_id,
v_ps_node_type,
p_out_virtual_flag,
v_up_level+1,
v_expl_node_type,
NO_FLAG);
t_childs.DELETE;
SELECT ps_node_id BULK COLLECT INTO t_childs FROM CZ_PS_NODES
WHERE devl_project_id=p_model_id AND ps_node_id<>p_ps_node_id AND
(
(ps_node_type in(COMPONENT_TYPE,PRODUCT_TYPE) AND ps_node_id IN ---- fix for bug #3161931
(SELECT component_id FROM CZ_MODEL_REF_EXPLS
WHERE model_id=p_model_id AND deleted_flag=NO_FLAG AND parent_expl_node_id=v_up_expl_id AND
component_id IN(SELECT ps_node_id FROM CZ_PS_NODES
START WITH ps_node_id=p_ps_node_id AND deleted_flag=NO_FLAG
CONNECT BY PRIOR ps_node_id=parent_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG)
)
)
OR
(ps_node_type in(REFERENCE_TYPE,CONNECTOR_TYPE) AND ps_node_id IN ---- fix for bug #3161931
(SELECT referring_node_id FROM CZ_MODEL_REF_EXPLS
WHERE model_id=p_model_id AND deleted_flag=NO_FLAG AND parent_expl_node_id=v_up_expl_id AND
referring_node_id IN(SELECT ps_node_id FROM CZ_PS_NODES
START WITH ps_node_id=p_ps_node_id AND deleted_flag=NO_FLAG
CONNECT BY PRIOR ps_node_id=parent_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG)
)
)
) AND deleted_flag=NO_FLAG;
t_child_nodes.DELETE;
UPDATE CZ_MODEL_REF_EXPLS
SET parent_expl_node_id=v_expl_id
WHERE parent_expl_node_id=v_up_expl_id
AND model_id=p_model_id AND
((ps_node_type IN(PRODUCT_TYPE,COMPONENT_TYPE,BOM_MODEL_TYPE) AND component_id=v_ps_node_id)
OR (ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) AND referring_node_id=v_ps_node_id))
AND deleted_flag=NO_FLAG
RETURNING model_ref_expl_id BULK COLLECT INTO t_child_nodes ;
UPDATE CZ_MODEL_REF_EXPLS
SET node_depth=node_depth+1
WHERE model_ref_expl_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=t_child_nodes(h) AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG
AND PRIOR deleted_flag=NO_FLAG);
t_chain.DELETE;t_projectCache.DELETE;
SELECT MIN(model_ref_expl_id) INTO v_comp_expl_id
FROM CZ_MODEL_REF_EXPLS
WHERE model_id=p_model_id AND component_id=v_component_id AND
deleted_flag='0';
update_Node_Depth(p_model_id);
PROCEDURE delete_Node
(p_ps_node_id IN INTEGER,
p_ps_node_type IN INTEGER,
p_out_err OUT NOCOPY INTEGER,
p_del_logically IN VARCHAR2 -- DEFAULT '1'
) IS
v_component_id CZ_PS_NODES.component_id%TYPE;
FOR i IN (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS a
WHERE referring_node_id=p_ps_node_id AND deleted_flag=NO_FLAG AND
model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG))
LOOP
--
-- DEBUG ERROR CODE --
--
ERROR_CODE:=1001;
delete_it(i.model_ref_expl_id, YES_FLAG);
SELECT component_id,NVL(virtual_flag,'1') INTO v_component_id,v_virtual_flag FROM CZ_PS_NODES
WHERE ps_node_id=p_ps_node_id;
FOR i IN (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS a
WHERE component_id=p_ps_node_id AND deleted_flag=NO_FLAG AND
model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG))
LOOP
--
-- DEBUG ERROR CODE --
--
ERROR_CODE:=1003;
delete_it(i.model_ref_expl_id, YES_FLAG);
FOR i IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS a
WHERE parent_expl_node_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
WHERE model_id=a.model_id AND component_id=v_component_id AND deleted_flag='0')
AND (component_id IN
(SELECT ps_node_id FROM CZ_PS_NODES
START WITH ps_node_id=p_ps_node_id
CONNECT BY PRIOR ps_node_id=parent_id)
OR
referring_node_id IN
(SELECT ps_node_id FROM cz_ps_nodes
START WITH ps_node_id=p_ps_node_id
CONNECT BY PRIOR ps_node_id=parent_id)
)
)
LOOP
--
-- DEBUG ERROR CODE --
--
ERROR_CODE:=1005;
delete_it(i.model_ref_expl_id, YES_FLAG);
LOG_REPORT('delete_Node',
'Error : ps_node_id='||TO_CHAR(p_ps_node_id)||' < '||ERROR_CODE||' > : '||SQLERRM);
END delete_Node;
PROCEDURE update_levels
(p_expl_id IN INTEGER,
p_level IN INTEGER,
p_tree_level IN INTEGER) IS
BEGIN
FOR i IN(SELECT model_ref_expl_id,parent_expl_node_id FROM CZ_MODEL_REF_EXPLS
WHERE parent_expl_node_id=p_expl_id AND deleted_flag=NO_FLAG)
LOOP
UPDATE CZ_MODEL_REF_EXPLS SET node_depth=p_level+p_tree_level
WHERE model_ref_expl_id=i.model_ref_expl_id;
update_levels(i.model_ref_expl_id,p_level+p_tree_level,p_tree_level+1);
SELECT model_ref_expl_id,model_id
BULK COLLECT INTO t_refs,t_models
FROM CZ_MODEL_REF_EXPLS a
WHERE child_model_expl_id=p_expl_root_id
AND ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) AND deleted_flag=NO_FLAG AND
model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG);
SELECT model_ref_expl_id INTO v_from_up_expl_id FROM CZ_MODEL_REF_EXPLS a
WHERE child_model_expl_id=p_from_expl_id AND model_ref_expl_id
IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=t_refs(i) AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND
deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG)
AND
model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG);
SELECT model_ref_expl_id,node_depth
INTO v_up_expl_id,v_up_level FROM CZ_MODEL_REF_EXPLS a
WHERE child_model_expl_id=p_up_expl_id AND model_ref_expl_id
IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=t_refs(i) AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG)
AND
model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG);
UPDATE CZ_MODEL_REF_EXPLS
SET parent_expl_node_id=v_up_expl_id
WHERE model_ref_expl_id
IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
WHERE (parent_expl_node_id=v_from_up_expl_id OR model_ref_expl_id=v_from_up_expl_id)
AND child_model_expl_id=p_model_ref_expl(k)
AND deleted_flag=NO_FLAG);
SELECT model_ref_expl_id,node_depth
BULK COLLECT INTO t_model_ref_expl_,t_levels_
FROM CZ_MODEL_REF_EXPLS
WHERE parent_expl_node_id=v_up_expl_id
AND child_model_expl_id=p_model_ref_expl(k)
AND deleted_flag=NO_FLAG;
update_levels(v_up_expl_id,v_up_level,1);
t_old_expl_ids.DELETE;
t_new_expl_ids.DELETE;
SELECT ps_node_type,NVL(virtual_flag,YES_FLAG),parent_id,component_id
INTO v_ps_node_type,v_virtual_flag,v_parent_id,v_component_id
FROM CZ_PS_NODES
WHERE ps_node_id=p_from_ps_node_id;
SELECT model_ref_expl_id,node_depth
INTO v_from_up_expl_id,v_from_up_level
FROM CZ_MODEL_REF_EXPLS
WHERE model_id=p_project_id AND referring_node_id=p_from_ps_node_id
AND deleted_flag=NO_FLAG;
SELECT model_ref_expl_id,node_depth
INTO v_ref_up_expl_id,v_ref_up_level
FROM CZ_MODEL_REF_EXPLS
WHERE component_id=v_up_id
AND model_id=p_project_id AND deleted_flag=NO_FLAG;
UPDATE CZ_MODEL_REF_EXPLS
SET parent_expl_node_id=v_ref_up_expl_id
WHERE model_ref_expl_id=v_from_up_expl_id
AND deleted_flag=NO_FLAG;
UPDATE CZ_MODEL_REF_EXPLS SET node_depth=node_depth-v_from_up_level+v_ref_up_level+1
WHERE model_ref_expl_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=v_from_up_expl_id AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG
AND PRIOR deleted_flag=NO_FLAG);
t_chain.DELETE;t_projectCache.DELETE;
FOR k IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS WHERE
model_id=t_chain(i).model_id AND ps_node_type IN(REFERENCE_TYPE,264)
AND component_id=p_project_id AND deleted_flag=NO_FLAG)
LOOP
BEGIN
SELECT model_ref_expl_id,node_depth
INTO v_ref_up_expl_id,v_ref_up_level
FROM CZ_MODEL_REF_EXPLS
WHERE component_id=v_up_id
AND model_ref_expl_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=k.model_ref_expl_id AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG
AND PRIOR deleted_flag=NO_FLAG);
UPDATE CZ_MODEL_REF_EXPLS
SET parent_expl_node_id=v_ref_up_expl_id
WHERE model_ref_expl_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS a
WHERE referring_node_id=p_from_ps_node_id
AND model_ref_expl_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=k.model_ref_expl_id AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG
AND PRIOR deleted_flag=NO_FLAG) AND
model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG))
RETURNING model_ref_expl_id,node_depth INTO var_subroot_id,var_subroot_level ;
UPDATE CZ_MODEL_REF_EXPLS SET node_depth=node_depth-var_subroot_level+v_ref_up_level+1
WHERE model_ref_expl_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=var_subroot_id AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND
PRIOR deleted_flag=NO_FLAG);
SELECT model_ref_expl_id
BULK COLLECT INTO t_model_ref_expl
FROM CZ_MODEL_REF_EXPLS
WHERE parent_expl_node_id=v_from_up_expl_id AND model_id=p_project_id AND
component_id IN(SELECT ps_node_id FROM CZ_PS_NODES
START WITH ps_node_id=p_from_ps_node_id AND deleted_flag=NO_FLAG
CONNECT BY PRIOR ps_node_id=parent_id AND deleted_flag=NO_FLAG
AND PRIOR deleted_flag=NO_FLAG);
SELECT model_ref_expl_id,node_depth
INTO v_from_up_expl_id,v_from_up_level
FROM CZ_MODEL_REF_EXPLS
WHERE model_id=p_project_id AND component_id=v_from_up_id
AND ps_node_type NOT IN(REFERENCE_TYPE,CONNECTOR_TYPE) AND child_model_expl_id IS NULL AND
deleted_flag=NO_FLAG;
UPDATE CZ_MODEL_REF_EXPLS SET parent_expl_node_id=v_up_expl_id
WHERE model_ref_expl_id=t_model_ref_expl(i)
AND model_id=p_project_id;
UPDATE CZ_MODEL_REF_EXPLS SET node_depth=node_depth-v_from_up_level+v_up_level+1
WHERE model_ref_expl_id IN
(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=t_model_ref_expl(i) AND deleted_flag=NO_FLAG
CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND
PRIOR deleted_flag=NO_FLAG);
SELECT model_ref_expl_id INTO v_expl_root_id
FROM CZ_MODEL_REF_EXPLS WHERE model_id=p_project_id AND component_id=p_project_id
AND parent_expl_node_id IS NULL AND deleted_flag=NO_FLAG;
SELECT node_depth INTO v_temp FROM CZ_MODEL_REF_EXPLS
WHERE model_id=p_project_id AND model_ref_expl_id=t_model_ref_expl(t);
update_Rules(p_from_ps_node_id);
SELECT MIN(model_ref_expl_id) INTO v_comp_expl_id
FROM CZ_MODEL_REF_EXPLS
WHERE model_id=p_project_id AND component_id=v_component_id AND
deleted_flag='0';
SELECT MIN(model_ref_expl_id) INTO v_comp_expl_id
FROM CZ_MODEL_REF_EXPLS
WHERE model_id=p_project_id AND component_id=
(SELECT component_id FROM CZ_PS_NODES WHERE ps_node_id=p_to_ps_node_id) AND
deleted_flag='0';
update_child_nodes(p_project_id);
update_Node_Depth(p_project_id);
SELECT ps_node_id INTO v_BOM_node1 FROM CZ_PS_NODES
WHERE devl_project_id=p_refroot_model_id AND ps_node_type=BOM_MODEL_TYPE
AND deleted_flag=NO_FLAG AND rownum<2;
FOR n IN(SELECT DISTINCT model_id FROM CZ_MODEL_REF_EXPLS a
WHERE component_id=p_refroot_model_id AND ps_node_type=REFERENCE_TYPE AND
deleted_flag='0' AND
EXISTS(SELECT NULL FROM CZ_RP_ENTRIES WHERE object_id=a.model_id AND
object_type='PRJ' AND deleted_flag='0'))
LOOP
IF containsBOM(n.model_id, v_instanciable) > 0 THEN
p_out_status_code:=1;
PROCEDURE delete_childs(t_arr IN IntArray) IS
temp_arr IntArray;
temp_arr.DELETE;
UPDATE CZ_MODEL_REF_EXPLS a SET deleted_flag=YES_FLAG
WHERE child_model_expl_id=t_arr(i) AND
model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG)
RETURNING model_ref_expl_id BULK COLLECT INTO temp_arr;
delete_childs(v_arr);
PROCEDURE delete_subtree(p_model_id IN INTEGER,p_model_ref_expl_id IN INTEGER) IS
t_arr IntArray;
FOR j IN(SELECT model_ref_expl_id,ps_node_type,component_id FROM CZ_MODEL_REF_EXPLS
WHERE model_id=p_model_id AND parent_expl_node_id=p_model_ref_expl_id AND deleted_flag=NO_FLAG)
LOOP
IF j.ps_node_type=CONNECTOR_TYPE THEN
t_arr.DELETE;
UPDATE CZ_MODEL_REF_EXPLS SET deleted_flag=YES_FLAG
WHERE model_ref_expl_id IN(
SELECT model_ref_expl_id FROM cz_model_ref_expls
START WITH model_ref_expl_id=j.model_ref_expl_id AND deleted_flag=NO_FLAG
CONNECT by PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG
AND PRIOR deleted_flag=NO_FLAG);
delete_subtree(p_model_id,j.model_ref_expl_id);
FOR i IN(SELECT model_ref_expl_id,ps_node_type FROM CZ_MODEL_REF_EXPLS
WHERE model_id=p_model_id AND component_id=p_model_id AND
ps_node_type IN(CONNECTOR_TYPE,REFERENCE_TYPE) AND deleted_flag=NO_FLAG)
LOOP
delete_subtree(p_model_id,i.model_ref_expl_id);
FOR i IN(SELECT DISTINCT model_id FROM CZ_MODEL_REF_EXPLS
WHERE component_id=p_model_id AND
ps_node_type IN(CONNECTOR_TYPE,REFERENCE_TYPE) AND deleted_flag=NO_FLAG)
LOOP
change_structure_(i.model_id);
SELECT DISTINCT model_id BULK COLLECT INTO t_m_chain_tbl FROM
(SELECT DISTINCT component_id AS model_id
FROM CZ_MODEL_REF_EXPLS WHERE model_id=p_model_id AND ps_node_type=REFERENCE_TYPE
AND deleted_flag=NO_FLAG
UNION
SELECT DISTINCT model_id FROM CZ_MODEL_REF_EXPLS a
WHERE component_id=p_model_id AND ps_node_type=REFERENCE_TYPE AND deleted_flag=NO_FLAG AND
model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG));
UPDATE cz_model_ref_expls
SET has_trackable_children='1'
WHERE model_id=t_m_chain_tbl(i) AND component_id=t_m_chain_tbl(i) AND deleted_flag=NO_FLAG AND
EXISTS(SELECT NULL FROM CZ_MODEL_REF_EXPLS m WHERE model_id=t_m_chain_tbl(i) AND ps_node_type=REFERENCE_TYPE
AND deleted_flag=NO_FLAG AND
EXISTS(SELECT NULL FROM CZ_PS_NODES
WHERE devl_project_id = m.component_id AND
ib_trackable = '1' AND deleted_flag = NO_FLAG));
SELECT '1' INTO v_ib_trackable FROM dual
WHERE EXISTS(SELECT NULL FROM CZ_PS_NODES WHERE devl_project_id=t_m_chain_tbl(i)
AND deleted_flag=NO_FLAG AND ib_trackable='1');
UPDATE CZ_MODEL_REF_EXPLS SET has_trackable_children=v_ib_trackable
WHERE model_id=t_m_chain_tbl(i) AND component_id=t_m_chain_tbl(i) AND deleted_flag=NO_FLAG;
UPDATE CZ_MODEL_REF_EXPLS a SET has_trackable_children='1'
WHERE component_id=t_trk_tbl(i) AND ps_node_type=REFERENCE_TYPE AND deleted_flag=NO_FLAG AND
model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG);
UPDATE CZ_MODEL_REF_EXPLS a SET has_trackable_children=NO_FLAG
WHERE component_id=t_nontrk_tbl(i) AND ps_node_type=REFERENCE_TYPE AND deleted_flag=NO_FLAG AND
model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG);
FOR i IN(SELECT expr_node_id,rule_id
FROM CZ_EXPRESSION_NODES
WHERE rule_id IN(SELECT rule_id FROM CZ_RULES
WHERE devl_project_id IN(SELECT devl_project_id FROM CZ_PS_NODES
WHERE ps_node_id=p_ps_node_id) AND deleted_flag=NO_FLAG AND disabled_flag=NO_FLAG) AND
ps_node_id=p_ps_node_id AND expr_type=205 AND deleted_flag=NO_FLAG)
LOOP
BEGIN
SELECT template_id INTO l_template_id
FROM CZ_EXPRESSION_NODES
WHERE rule_id=i.rule_id AND expr_parent_id=i.expr_node_id AND
expr_type=210 AND
template_id in(43,44) AND deleted_flag=NO_FLAG;
SELECT instantiable_flag INTO l_instantiable_flag
FROM CZ_PS_NODES
WHERE ps_node_id=p_ps_node_id;
SELECT param_index, seq_nbr, expr_type, expr_node_id,
expr_parent_id,template_id, ps_node_id
FROM cz_expression_nodes
WHERE rule_id = p_rule_id
AND deleted_flag = '0'
ORDER BY expr_parent_id, seq_nbr;
FOR k IN (SELECT instantiable_flag FROM cz_ps_nodes
WHERE ps_node_id = l_ps_node_id(i)
AND deleted_flag = NO_FLAG) LOOP
IF (k.instantiable_flag = '2') THEN
IF (l_template_id(j) = 43) THEN
x_sys_prop := MIN_RULE;