[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT CZ_PS_NODES_S.NEXTVAL INTO currentPSNode FROM dual;
SELECT TO_NUMBER(value) INTO mINCREMENT FROM cz_db_settings
WHERE UPPER(setting_id)=UPPER('OracleSequenceIncr') AND section_name='SCHEMA';
SELECT CZ_PS_NODES_S.nextval INTO currPSSeqVal FROM dual;
select CZ_XFR_RUN_INFOS_S.NEXTVAL INTO RUN_ID FROM dual;
SELECT object_id
INTO l_rp_folder
FROM cz_rp_entries
WHERE cz_rp_entries.object_id = p_rp_folder_id
AND cz_rp_entries.object_type = 'FLD'
AND cz_rp_entries.deleted_flag = '0';
l_rp_fld_tbl.DELETE;
SELECT object_id
BULK
COLLECT
INTO l_rp_fld_tbl
FROM cz_rp_entries
WHERE cz_rp_entries.deleted_flag = '0'
AND cz_rp_entries.object_type = 'FLD'
START WITH cz_rp_entries.object_type = 'FLD'
AND cz_rp_entries.object_id = l_rp_folder
CONNECT BY PRIOR cz_rp_entries.object_id = cz_rp_entries.enclosing_folder
AND PRIOR cz_rp_entries.object_type = 'FLD';
l_rp_model_tbl.DELETE;
SELECT object_id, checkout_user, cz_devl_projects.name
BULK COLLECT
INTO l_rp_model_tbl, l_checkout_user_tbl, l_devl_prj_name_tbl
FROM cz_rp_entries, cz_devl_projects
WHERE cz_rp_entries.object_type = 'PRJ'
AND cz_rp_entries.deleted_flag = '0'
AND cz_rp_entries.enclosing_folder = l_rp_fld_tbl(i)
AND cz_rp_entries.object_id = cz_devl_projects.devl_project_id;
l_uct_tbl.DELETE;
SELECT object_id, checkout_user, template_name
BULK COLLECT
INTO l_uct_tbl, l_checkout_user_tbl, l_template_name_tbl
FROM cz_rp_entries, cz_ui_templates
WHERE cz_rp_entries.object_type = 'UCT'
AND cz_rp_entries.deleted_flag = '0'
AND cz_rp_entries.seeded_flag <> '1'
AND cz_rp_entries.enclosing_folder = l_rp_fld_tbl(i)
AND cz_rp_entries.object_id = cz_ui_templates.template_id
AND cz_ui_templates.ui_def_id = 0;
UPDATE CZ_RULES
SET rule_text = EMPTY_CLOB()
WHERE rule_id = p_rule_id;
SELECT rule_text INTO l_clob_loc FROM CZ_RULES
WHERE rule_id = p_rule_id;
SELECT rule_text INTO l_clob_loc FROM CZ_RULES
WHERE rule_id = p_rule_id;
SELECT NAME INTO l_property_name FROM CZ_PROPERTIES
WHERE property_id=p_property_id;
SELECT devl_project_id INTO l_model_id FROM CZ_RULES
WHERE rule_id=p_rule_id;
FOR i IN(SELECT source_offset,source_length,display_node_depth,
ps_node_id,property_id,model_ref_expl_id FROM CZ_EXPRESSION_NODES
WHERE rule_id = p_rule_id AND expr_type IN(205,207)
AND deleted_flag='0' ORDER BY -source_offset)
LOOP
IF i.source_offset=0 THEN
l_source_offset := 1;
'SELECT '||p_sequence_name||'.NEXTVAL FROM dual' INTO l_id;
FOR i IN(SELECT intl_text_id,language,localized_str,source_lang FROM CZ_LOCALIZED_TEXTS
WHERE intl_text_id=p_intl_text_id AND deleted_flag='0')
LOOP
INSERT INTO CZ_LOCALIZED_TEXTS
(INTL_TEXT_ID,
LOCALIZED_STR,
LANGUAGE,
SOURCE_LANG,
DELETED_FLAG,
SECURITY_MASK,
CHECKOUT_USER,
UI_DEF_ID,
MODEL_ID,
PERSISTENT_INTL_TEXT_ID,
SEEDED_FLAG)
SELECT
l_new_intl_text_id,
LOCALIZED_STR,
LANGUAGE,
SOURCE_LANG,
DELETED_FLAG,
SECURITY_MASK,
CHECKOUT_USER,
UI_DEF_ID,
MODEL_ID,
l_new_intl_text_id,
'0'
FROM CZ_LOCALIZED_TEXTS
WHERE intl_text_id=p_intl_text_id AND
language=i.LANGUAGE AND
source_lang=i.SOURCE_LANG AND deleted_flag='0';
FOR i IN(SELECT * FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=p_model_ref_expl_id 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
x_expl_nodes_tbl(i.model_ref_expl_id) := allocateId('CZ_MODEL_REF_EXPLS_S');
FOR i IN(SELECT * FROM CZ_MODEL_REF_EXPLS
WHERE model_ref_expl_id = l_index )
LOOP
l_referring_node_id := NULL;
INSERT INTO CZ_MODEL_REF_EXPLS
(
MODEL_REF_EXPL_ID
,COMPONENT_ID
,PARENT_EXPL_NODE_ID
,PS_NODE_TYPE
,MODEL_ID
,VIRTUAL_FLAG
,NODE_DEPTH
,DELETED_FLAG
,REFERRING_NODE_ID
,CHILD_MODEL_EXPL_ID
,EXPL_NODE_TYPE
,HAS_TRACKABLE_CHILDREN
)
VALUES
(
l_new_expl_id
,l_new_component_id
,l_new_parent_expl_id
,i.PS_NODE_TYPE
,i.MODEL_ID
,i.VIRTUAL_FLAG
,l_node_depth
,i.DELETED_FLAG
,l_referring_node_id
,i.CHILD_MODEL_EXPL_ID
,i.EXPL_NODE_TYPE
,i.HAS_TRACKABLE_CHILDREN
);
l_ps_nodes_tbl.delete;
l_persistent_tbl.delete;
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_node_id;
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='0';
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_node_id
AND deleted_flag='0';
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_node_id AND
node_depth=l_min_node_depth AND deleted_flag='0';
SELECT model_ref_expl_id,node_depth INTO l_new_pr_expl_id, l_node_depth
FROM CZ_MODEL_REF_EXPLS
WHERE model_id=l_model_id AND component_id=(SELECT component_id
FROM CZ_PS_NODES WHERE ps_node_id = p_new_parent_id) AND referring_node_id IS NULL AND
child_model_expl_id IS NULL AND deleted_flag='0';
FOR i IN (SELECT ps_node_id, ps_node_type, component_id, persistent_node_id, name FROM CZ_PS_NODES
START WITH ps_node_id=p_node_id AND deleted_flag='0'
CONNECT BY PRIOR ps_node_id=parent_id AND deleted_flag='0' AND PRIOR deleted_flag='0')
LOOP
l_ps_nodes_tbl(i.ps_node_id) := getPSSeqVal;
FOR i IN (SELECT * FROM CZ_PS_NODES
WHERE ps_node_id=l_index AND deleted_flag='0')
LOOP
IF i.ps_node_id=p_node_id THEN
l_new_parent_id := p_new_parent_id;
SELECT NVL(MAX(tree_seq),0) + 1 INTO l_tree_seq
FROM cz_ps_nodes
WHERE parent_id=p_new_parent_id;
SELECT COUNT(*) INTO l_node_counter FROM CZ_PS_NODES
WHERE parent_id=p_new_parent_id AND
(name=i.NAME OR name LIKE 'Copy (%) of '||i.NAME) AND deleted_flag='0';
INSERT INTO CZ_PS_NODES
(
PS_NODE_ID
,DEVL_PROJECT_ID
,FROM_POPULATOR_ID
,PROPERTY_BACKPTR
,ITEM_TYPE_BACKPTR
,INTL_TEXT_ID
,SUB_CONS_ID
,ITEM_ID
,NAME
,ORIG_SYS_REF
,RESOURCE_FLAG
,INITIAL_VALUE
,PARENT_ID
,MINIMUM
,MAXIMUM
,PS_NODE_TYPE
,FEATURE_TYPE
,PRODUCT_FLAG
,REFERENCE_ID
,MULTI_CONFIG_FLAG
,ORDER_SEQ_FLAG
,SYSTEM_NODE_FLAG
,TREE_SEQ
,COUNTED_OPTIONS_FLAG
,UI_OMIT
,UI_SECTION
,BOM_TREATMENT
,COMPONENT_SEQUENCE_ID
,BOM_REQUIRED_FLAG
,SO_ITEM_TYPE_CODE
,MINIMUM_SELECTED
,MAXIMUM_SELECTED
,DELETED_FLAG
,EFF_FROM
,EFF_TO
,SECURITY_MASK
,EFF_MASK
,CHECKOUT_USER
,USER_NUM01
,USER_NUM02
,USER_NUM03
,USER_NUM04
,USER_STR01
,USER_STR02
,USER_STR03
,USER_STR04
,VIRTUAL_FLAG
,EFFECTIVE_USAGE_MASK
,EFFECTIVE_FROM
,EFFECTIVE_UNTIL
,DECIMAL_QTY_FLAG
,PERSISTENT_NODE_ID
,COMPONENT_SEQUENCE_PATH
,VIOLATION_TEXT_ID
,EFFECTIVITY_SET_ID
,QUOTEABLE_FLAG
,PRIMARY_UOM_CODE
,BOM_SORT_ORDER
,IB_TRACKABLE
,COMPONENT_ID
,ACCUMULATOR_FLAG
,NOTES_TEXT_ID
,INSTANTIABLE_FLAG
,INITIAL_NUM_VALUE
,SRC_APPLICATION_ID
,MAX_LENGTH
,DISPLAYNAME_CAPT_RULE_ID
,DISPLAYNAME_TEXT_ID
)
VALUES
(
l_new_node_id
,i.DEVL_PROJECT_ID
,i.FROM_POPULATOR_ID
,i.PROPERTY_BACKPTR
,i.ITEM_TYPE_BACKPTR
,l_new_intl_text_id
,i.SUB_CONS_ID
,i.ITEM_ID
,l_name
,NULL
,i.RESOURCE_FLAG
,i.INITIAL_VALUE
,l_new_parent_id
,i.MINIMUM
,i.MAXIMUM
,i.PS_NODE_TYPE
,i.FEATURE_TYPE
,i.PRODUCT_FLAG
,i.REFERENCE_ID
,i.MULTI_CONFIG_FLAG
,i.ORDER_SEQ_FLAG
,i.SYSTEM_NODE_FLAG
,l_tree_seq
,i.COUNTED_OPTIONS_FLAG
,i.UI_OMIT
,i.UI_SECTION
,i.BOM_TREATMENT
,i.COMPONENT_SEQUENCE_ID
,i.BOM_REQUIRED_FLAG
,i.SO_ITEM_TYPE_CODE
,i.MINIMUM_SELECTED
,i.MAXIMUM_SELECTED
,i.DELETED_FLAG
,i.EFF_FROM
,i.EFF_TO
,i.SECURITY_MASK
,i.EFF_MASK
,i.CHECKOUT_USER
,i.USER_NUM01
,i.USER_NUM02
,i.USER_NUM03
,i.USER_NUM04
,i.USER_STR01
,i.USER_STR02
,i.USER_STR03
,i.USER_STR04
,i.VIRTUAL_FLAG
,i.EFFECTIVE_USAGE_MASK
,i.EFFECTIVE_FROM
,i.EFFECTIVE_UNTIL
,i.DECIMAL_QTY_FLAG
,l_new_node_id
,i.COMPONENT_SEQUENCE_PATH
,l_new_violation_text_id
,i.EFFECTIVITY_SET_ID
,i.QUOTEABLE_FLAG
,i.PRIMARY_UOM_CODE
,i.BOM_SORT_ORDER
,i.IB_TRACKABLE
,i.COMPONENT_ID
,i.ACCUMULATOR_FLAG
,i.NOTES_TEXT_ID
,i.INSTANTIABLE_FLAG
,i.INITIAL_NUM_VALUE
,i.SRC_APPLICATION_ID
,i.MAX_LENGTH
,i.DISPLAYNAME_CAPT_RULE_ID
,i.DISPLAYNAME_TEXT_ID
);
INSERT INTO CZ_PS_PROP_VALS(
PS_NODE_ID
,PROPERTY_ID
,DATA_VALUE
,DELETED_FLAG
,EFF_FROM
,EFF_TO
,SECURITY_MASK
,EFF_MASK
,CHECKOUT_USER
,ORIG_SYS_REF
,DATA_NUM_VALUE
)
SELECT
l_new_node_id
,PROPERTY_ID
,DATA_VALUE
,DELETED_FLAG
,EFF_FROM
,EFF_TO
,SECURITY_MASK
,EFF_MASK
,CHECKOUT_USER
,NULL
,DATA_NUM_VALUE
FROM CZ_PS_PROP_VALS
WHERE PS_NODE_ID=l_index
AND DELETED_FLAG='0';
FOR pspropval IN (SELECT propval.property_id, propval.data_num_value
FROM cz_properties prop, cz_ps_prop_vals propval
WHERE prop.property_id = propval.property_id
AND propval.ps_node_id = l_new_node_id
AND prop.data_type=8
AND propval.deleted_flag = 0
AND propval.data_num_value IS NOT NULL) LOOP
l_text_id := pspropval.data_num_value;
UPDATE cz_ps_prop_vals
SET data_num_value = l_new_intl_text_id
WHERE property_id= pspropval.property_id
AND ps_node_id = l_new_node_id;
FOR i IN(SELECT model_ref_expl_id, node_depth FROM CZ_MODEL_REF_EXPLS
WHERE parent_expl_node_id=l_expl_id AND
(referring_node_id IS NULL AND component_id IN
(SELECT ps_node_id FROM CZ_PS_NODES
START WITH ps_node_id=p_node_id
CONNECT BY PRIOR ps_node_id=parent_id AND
deleted_flag='0' AND PRIOR deleted_flag='0'))
UNION
SELECT model_ref_expl_id, node_depth FROM CZ_MODEL_REF_EXPLS
WHERE parent_expl_node_id=l_expl_id AND
(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_node_id
CONNECT BY PRIOR ps_node_id=parent_id AND
deleted_flag='0' AND PRIOR deleted_flag='0')))
LOOP
copy_Expl_Subtree(p_model_ref_expl_id => i.model_ref_expl_id,
p_curr_node_depth => i.node_depth,
p_new_parent_expl_id => l_new_pr_expl_id,
p_new_parent_expl_node_depth => l_node_depth,
p_ps_nodes_tbl => l_ps_nodes_tbl,
x_expl_nodes_tbl => l_expl_nodes_tbl);
SELECT rule_id, rule_folder_id, rule_type, component_id, model_ref_expl_id
FROM cz_rules
WHERE devl_project_id = l_model_id
AND deleted_flag = '0'
AND rule_type IN (RULE_TYPE_EXPRESSION,RULE_TYPE_COMPAT_TABLE,RULE_TYPE_DESIGNCHART,RULE_TYPE_JAVA_METHOD);
SELECT DISTINCT e.ps_node_id, p.devl_project_id, e.model_ref_expl_id
FROM cz_expression_nodes e, cz_rules r, cz_ps_nodes p
WHERE e.deleted_flag = '0'
AND r.deleted_flag = '0'
AND p.deleted_flag = '0'
AND p.ps_node_id = e.ps_node_id
AND r.rule_id = e.rule_id
AND r.rule_id = iRuleId
AND e.ps_node_id IS NOT NULL;
SELECT DISTINCT f.feature_id, p.devl_project_id, f.model_ref_expl_id
FROM cz_des_chart_features f, cz_rules r, cz_ps_nodes p
WHERE f.deleted_flag = '0'
AND r.deleted_flag = '0'
AND p.deleted_flag = '0'
AND p.ps_node_id = f.feature_id
AND r.rule_id = f.rule_id
AND r.rule_id = iRuleId
AND f.feature_id IS NOT NULL;
SELECT model_ref_expl_id INTO l_new_expl_id
FROM cz_model_ref_expls a, cz_ps_nodes b
WHERE a.referring_node_id = l_ps_nodes_tbl(l_index)
AND a.referring_node_id = b.ps_node_id
AND b.devl_project_id = l_model_id
AND a.model_id = l_model_id
AND b.ps_node_type = l_ps_node_type_tbl(l_index)
AND a.deleted_flag='0'
AND b.deleted_flag='0';
SELECT component_id INTO l_new_component_id
FROM cz_ps_nodes
WHERE ps_node_id=l_ps_nodes_tbl(l_index);
SELECT model_ref_expl_id INTO l_new_expl_id
FROM cz_model_ref_expls a, cz_ps_nodes b
WHERE a.component_id = b.ps_node_id
AND b.ps_node_id = l_new_component_id
AND b.devl_project_id = l_model_id
AND a.model_id = b.devl_project_id
AND a.deleted_flag = '0'
AND b.deleted_flag = '0';
SELECT devl_project_id INTO l_referenced_model_id
FROM cz_ps_nodes
WHERE ps_node_id = l_rule_compid_tbl(i)
AND deleted_flag = '0';
FOR k IN (SELECT * FROM cz_model_ref_expls
START WITH model_id=l_model_id AND referring_node_id = l_index
AND component_id = l_comp_tbl(l_index) AND ps_node_type = l_ps_node_type_tbl(l_index)
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
IF(k.component_id = l_referenced_model_id AND k.model_ref_expl_id = l_rule_model_refexpl_tbl(i)) THEN
l_node_found := TRUE;
l_rule_nodes_tbl.delete; l_rule_node_model_tbl.delete; l_model_ref_expl_tbl.delete;
FOR k IN (SELECT * FROM cz_model_ref_expls
START WITH model_id=l_model_id AND referring_node_id = l_index
AND component_id = l_comp_tbl(l_index) AND ps_node_type = l_ps_node_type_tbl(l_index)
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
IF(k.component_id = l_rule_node_model_tbl(j) AND k.model_ref_expl_id = l_model_ref_expl_tbl(j)) THEN
l_node_found := TRUE;
UPDATE cz_expression_nodes
SET ps_node_id = l_ps_nodes_tbl(l_index)
WHERE ps_node_id = l_index
AND rule_id = l_new_rule_id;
UPDATE cz_expression_nodes
SET relative_node_path = REPLACE(relative_node_path,TO_CHAR(l_index),TO_CHAR(l_persistent_tbl(l_index)))
WHERE rule_id = l_new_rule_id
AND relative_node_path IS NOT NULL;
UPDATE cz_rules
SET component_id = l_ps_nodes_tbl(l_index)
WHERE rule_id = l_new_rule_id
AND component_id = l_index
AND deleted_flag = '0';
UPDATE cz_expression_nodes
SET model_ref_expl_id = l_expl_nodes_tbl(l_index)
WHERE model_ref_expl_id = l_index
AND rule_id = l_new_rule_id;
UPDATE cz_rules
SET model_ref_expl_id = l_expl_nodes_tbl(l_index)
WHERE model_ref_expl_id = l_index
AND rule_id = l_new_rule_id;
UPDATE cz_expression_nodes
SET model_ref_expl_id = l_expl_nodes_all_tbl(l_index)
WHERE model_ref_expl_id = l_index
AND rule_id = l_new_rule_id;
UPDATE cz_expression_nodes
SET model_ref_expl_id = l_new_expl_id
WHERE rule_id=l_new_rule_id
AND model_ref_expl_id=l_expl_id;
UPDATE cz_rules
SET model_ref_expl_id = l_expl_nodes_all_tbl(l_index)
WHERE model_ref_expl_id = l_index
AND rule_id = l_new_rule_id;
UPDATE cz_des_chart_cells
SET primary_opt_id = l_ps_nodes_tbl(l_index)
WHERE primary_opt_id = l_index
AND rule_id = l_new_rule_id;
UPDATE cz_des_chart_cells
SET secondary_opt_id = l_ps_nodes_tbl(l_index)
WHERE secondary_opt_id = l_index
AND rule_id = l_new_rule_id;
UPDATE cz_des_chart_cells
SET secondary_feature_id = l_ps_nodes_tbl(l_index)
WHERE secondary_feature_id = l_index
AND rule_id = l_new_rule_id;
UPDATE cz_des_chart_features
SET feature_id = l_ps_nodes_tbl(l_index)
WHERE feature_id = l_index
AND rule_id = l_new_rule_id;
END LOOP; -- updated node ids
UPDATE cz_des_chart_cells
SET secondary_feat_expl_id = l_expl_nodes_tbl(l_index)
WHERE secondary_feat_expl_id = l_index
AND rule_id = l_new_rule_id;
UPDATE cz_des_chart_features
SET model_ref_expl_id = l_expl_nodes_tbl(l_index)
WHERE model_ref_expl_id = l_index
AND rule_id = l_new_rule_id;
UPDATE cz_des_chart_cells
SET secondary_feat_expl_id = l_expl_nodes_all_tbl(l_index)
WHERE secondary_feat_expl_id = l_index
AND rule_id = l_new_rule_id;
UPDATE cz_des_chart_features
SET model_ref_expl_id = l_expl_nodes_all_tbl(l_index)
WHERE model_ref_expl_id = l_index
AND rule_id = l_new_rule_id;
UPDATE cz_des_chart_cells
SET secondary_feat_expl_id = l_new_expl_id
WHERE secondary_feat_expl_id = l_expl_id
AND rule_id = l_new_rule_id;
UPDATE cz_des_chart_features
SET model_ref_expl_id = l_new_expl_id
WHERE model_ref_expl_id = l_expl_id
AND rule_id = l_new_rule_id;
SELECT devl_project_id,rule_folder_id, rule_type, seeded_flag
INTO l_model_id,l_rule_folder_id, l_rule_type, l_seeded_flag
FROM CZ_RULES
WHERE rule_id=p_rule_id AND deleted_flag='0';
SELECT filter_set_id INTO l_filter_set_id
FROM CZ_FILTER_SETS
WHERE rule_id=p_rule_id AND rownum<2;
SELECT object_type INTO l_object_type
FROM CZ_RULE_FOLDERS
WHERE rule_folder_id=p_rule_folder_id
AND object_type IN ('RFL','RSQ')
AND deleted_flag='0';
FOR i IN (SELECT * FROM CZ_RULES
WHERE rule_id=p_rule_id AND deleted_flag='0')
LOOP
IF i.reason_id IS NOT NULL THEN
l_text_id := i.reason_id;
UPDATE cz_localized_texts
SET localized_str=REPLACE(localized_str, i.NAME, i.NAME||'-'||TO_CHAR(l_new_rule_id))
WHERE intl_text_id = l_new_reason_id;
UPDATE cz_localized_texts
SET localized_str=REPLACE(localized_str, i.NAME, i.NAME||'-'||TO_CHAR(l_new_rule_id))
WHERE intl_text_id = l_new_unsatisfied_msg_id;
SELECT NVL(MAX(seq_nbr),0)+1
INTO l_new_seq_nbr
FROM CZ_RULES
WHERE rule_folder_id=l_new_rfl_id AND deleted_flag='0';
SELECT effective_from,effective_until
INTO l_effective_from,l_effective_until
FROM CZ_EFFECTIVITY_SETS
WHERE effectivity_set_id = i.EFFECTIVITY_SET_ID;
SELECT devl_project_id INTO l_devl_project_id FROM CZ_UI_DEFS
WHERE ui_def_id=p_ui_def_id AND deleted_flag='0';
SELECT persistent_node_id,pagebase_expl_node_id
INTO l_persistent_node_id, l_model_ref_expl_id
FROM cz_ui_pages
WHERE page_id=p_ui_page_id
AND ui_def_id=p_ui_def_id;
SELECT ps_node_id INTO l_component_id FROM CZ_PS_NODES
WHERE devl_project_id=l_devl_project_id AND
persistent_node_id=l_persistent_node_id AND
deleted_flag='0';
FOR p IN (SELECT referring_node_id
FROM cz_model_ref_expls
START WITH model_ref_expl_id=l_model_ref_expl_id
CONNECT by PRIOR parent_expl_node_id=model_ref_expl_id AND
deleted_flag='0' AND PRIOR deleted_flag='0' AND
PRIOR referring_node_id IS NULL)
LOOP
l_reference_ps_node_id := p.referring_node_id;
SELECT ps_node_id INTO l_component_id FROM CZ_PS_NODES
WHERE devl_project_id=
(
SELECT devl_project_id FROM CZ_PS_NODES
WHERE ps_node_id=l_reference_ps_node_id
) AND
persistent_node_id=l_persistent_node_id AND
deleted_flag='0';
SELECT max(nvl(class_seq,0))+1 INTO l_class_seq
FROM cz_rules
WHERE deleted_flag = '0' AND devl_project_id = l_devl_project_id
AND rule_class = i.rule_class;
INSERT INTO CZ_RULES(
RULE_ID,
SUB_CONS_ID,
REASON_ID,
AMOUNT_ID,
GRID_ID,
RULE_FOLDER_ID,
DEVL_PROJECT_ID,
INVALID_FLAG,
DESC_TEXT,
NAME,
ANTECEDENT_ID,
CONSEQUENT_ID,
RULE_TYPE,
EXPR_RULE_TYPE,
COMPONENT_ID,
REASON_TYPE,
DISABLED_FLAG,
ORIG_SYS_REF,
DELETED_FLAG,
SECURITY_MASK,
CHECKOUT_USER,
EFFECTIVITY_SET_ID,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
EFFECTIVE_USAGE_MASK,
SEQ_NBR,
RULE_FOLDER_TYPE,
UNSATISFIED_MSG_ID,
UNSATISFIED_MSG_SOURCE,
SIGNATURE_ID,
TEMPLATE_PRIMITIVE_FLAG,
PRESENTATION_FLAG,
TEMPLATE_TOKEN,
RULE_TEXT,
NOTES,
CLASS_NAME,
INSTANTIATION_SCOPE,
MODEL_REF_EXPL_ID,
MUTABLE_FLAG,
SEEDED_FLAG,
UI_DEF_ID,
UI_PAGE_ID,
UI_PAGE_ELEMENT_ID,
RULE_CLASS,
CLASS_SEQ,
CONFIG_ENGINE_TYPE,
ACCUMULATOR_FLAG)
VALUES(
l_new_rule_id,
i.SUB_CONS_ID,
l_new_reason_id,
i.AMOUNT_ID,
i.GRID_ID,
l_new_rfl_id,
l_devl_project_id,
i.INVALID_FLAG,
i.DESC_TEXT,
i.NAME||'-'||TO_CHAR(l_new_rule_id),
i.ANTECEDENT_ID,
i.CONSEQUENT_ID,
i.RULE_TYPE,
i.EXPR_RULE_TYPE,
l_component_id,
i.REASON_TYPE,
i.DISABLED_FLAG,
NULL,
i.DELETED_FLAG,
i.SECURITY_MASK,
i.CHECKOUT_USER,
l_effective_set_id,
l_effective_from,
l_effective_until,
i.EFFECTIVE_USAGE_MASK,
l_new_seq_nbr,
i.RULE_FOLDER_TYPE,
l_new_unsatisfied_msg_id,
i.UNSATISFIED_MSG_SOURCE,
i.SIGNATURE_ID,
i.TEMPLATE_PRIMITIVE_FLAG,
i.PRESENTATION_FLAG,
i.TEMPLATE_TOKEN,
i.RULE_TEXT,
i.NOTES,
i.CLASS_NAME,
i.INSTANTIATION_SCOPE,
l_model_ref_expl_id,
i.MUTABLE_FLAG,
'0',
DECODE(p_ui_def_id,NULL,i.ui_def_id,p_ui_def_id),
DECODE(p_ui_page_id,NULL,i.ui_page_id,p_ui_page_id),
DECODE(p_ui_page_element_id,NULL,i.ui_page_element_id,p_ui_page_element_id),
i.RULE_CLASS,
l_class_seq,
i.CONFIG_ENGINE_TYPE,
i.ACCUMULATOR_FLAG
);
SELECT NVL(MAX(tree_seq),0)+1
INTO l_new_tree_seq
FROM CZ_RULE_FOLDERS
WHERE parent_rule_folder_id=l_new_rfl_id
AND deleted_flag='0';
INSERT INTO CZ_RULE_FOLDERS
(RULE_FOLDER_ID,
FOLDER_TYPE,
NAME,
DESC_TEXT,
PARENT_RULE_FOLDER_ID,
TREE_SEQ,
DEVL_PROJECT_ID,
PERSISTENT_RULE_FOLDER_ID,
EFFECTIVE_USAGE_MASK,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
EFFECTIVITY_SET_ID,
DELETED_FLAG,
SECURITY_MASK,
CHECKOUT_USER,
OBJECT_TYPE,
DISABLED_FLAG,
ORIG_SYS_REF)
SELECT
l_new_rule_id,
FOLDER_TYPE,
NAME||'-'||TO_CHAR(l_new_rule_id),
DESC_TEXT,
l_new_rfl_id,
l_new_tree_seq,
DEVL_PROJECT_ID,
l_new_rule_id,
EFFECTIVE_USAGE_MASK,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
EFFECTIVITY_SET_ID,
DELETED_FLAG,
SECURITY_MASK,
CHECKOUT_USER,
OBJECT_TYPE,
DISABLED_FLAG,
ORIG_SYS_REF
FROM CZ_RULE_FOLDERS
WHERE rule_folder_id=p_rule_id AND parent_rule_folder_id = l_rule_folder_id AND deleted_flag='0';
INSERT INTO CZ_DES_CHART_CELLS
(RULE_ID,
PRIMARY_OPT_ID,
SECONDARY_OPT_ID,
MARK_CHAR,
SECONDARY_FEAT_EXPL_ID,
SECONDARY_FEATURE_ID,
DELETED_FLAG,
SECURITY_MASK ,
CHECKOUT_USER )
SELECT
l_new_rule_id,
PRIMARY_OPT_ID,
SECONDARY_OPT_ID,
MARK_CHAR,
SECONDARY_FEAT_EXPL_ID,
SECONDARY_FEATURE_ID,
DELETED_FLAG,
SECURITY_MASK,
CHECKOUT_USER
FROM CZ_DES_CHART_CELLS
WHERE rule_id=p_rule_id AND deleted_flag='0';
INSERT INTO CZ_DES_CHART_FEATURES
(RULE_ID,
FEATURE_ID,
FEATURE_TYPE ,
MODEL_REF_EXPL_ID,
DELETED_FLAG,
SECURITY_MASK,
CHECKOUT_USER )
SELECT
l_new_rule_id,
FEATURE_ID,
FEATURE_TYPE ,
MODEL_REF_EXPL_ID,
DELETED_FLAG,
SECURITY_MASK,
CHECKOUT_USER
FROM CZ_DES_CHART_FEATURES
WHERE rule_id=p_rule_id AND deleted_flag='0';
INSERT INTO CZ_FILTER_SETS
(
FILTER_SET_ID
,DEVL_PROJECT_ID
,RULE_ID
,EXPRESS_ID
,SOURCE_TYPE
,DELETED_FLAG
,EFF_FROM
,EFF_TO
,SECURITY_MASK
,EFF_MASK
,CHECKOUT_USER
,SOURCE_SYNTAX
,SOURCE_VIEW_OWNER
,SOURCE_VIEW_NAME
)
SELECT
l_new_filter_set_id
,DEVL_PROJECT_ID
,l_new_rule_id
,EXPRESS_ID
,SOURCE_TYPE
,DELETED_FLAG
,EFF_FROM
,EFF_TO
,SECURITY_MASK
,EFF_MASK
,CHECKOUT_USER
,SOURCE_SYNTAX
,SOURCE_VIEW_OWNER
,SOURCE_VIEW_NAME
FROM CZ_FILTER_SETS
WHERE rule_id = p_rule_id AND deleted_flag='0';
INSERT INTO CZ_POPULATORS
(
POPULATOR_ID
,OWNED_BY_NODE_ID
,FILTER_SET_ID
,RESULT_TYPE
,DELETED_FLAG
,EFF_FROM
,EFF_TO
,SECURITY_MASK
,EFF_MASK
,CHECKOUT_USER
,PERSISTENT_POPULATOR_ID
,DESCRIPTION
,NAME
,HAS_LEVEL
,HAS_DESCRIPTION
,HAS_PROPERTY
,HAS_ITEM_TYPE
,HAS_ITEM
,VIEW_NAME
,FEATURE_TYPE
,QUERY_SYNTAX
,XFR_GROUP
,SEEDED_FLAG
)
SELECT
l_new_populator_id
,OWNED_BY_NODE_ID
,l_new_filter_set_id
,RESULT_TYPE
,DELETED_FLAG
,EFF_FROM
,EFF_TO
,SECURITY_MASK
,EFF_MASK
,CHECKOUT_USER
,l_new_populator_id
,DESCRIPTION
,NAME
,HAS_LEVEL
,HAS_DESCRIPTION
,HAS_PROPERTY
,HAS_ITEM_TYPE
,HAS_ITEM
,VIEW_NAME
,FEATURE_TYPE
,QUERY_SYNTAX
,XFR_GROUP
,SEEDED_FLAG
FROM CZ_POPULATORS
WHERE filter_set_id=l_filter_set_id AND deleted_flag='0';
l_new_exprnode_tbl.delete;
l_parent_exprnode_tbl.delete;
l_new_parent_exprnode_tbl.delete;
SELECT expr_node_id, expr_parent_id
FROM CZ_EXPRESSION_NODES
WHERE rule_id=p_rule_id AND deleted_flag='0') LOOP
l_new_exprnode_tbl(i.expr_node_id) := allocateId('CZ_EXPRESSION_NODES_S');
INSERT INTO CZ_EXPRESSION_NODES
(
EXPR_NODE_ID
,EXPRESS_ID
,SEQ_NBR
,ITEM_TYPE_ID
,PS_NODE_ID
,ITEM_ID
,FILTER_SET_ID
,GRID_COL_ID
,EXPR_PARENT_ID
,PROPERTY_ID
,COMPILE_ADVICE
,COL
,DATA_VALUE
,FIELD_NAME
,EXPR_TYPE
,EXPR_SUBTYPE
,TOKEN_LIST_SEQ
,DELETED_FLAG
,EFF_FROM
,EFF_TO
,SECURITY_MASK
,EFF_MASK
,CHECKOUT_USER
,CONSEQUENT_FLAG
,MODEL_REF_EXPL_ID
,RULE_ID
,TEMPLATE_ID
,ARGUMENT_SIGNATURE_ID
,ARGUMENT_INDEX
,PARAM_SIGNATURE_ID
,PARAM_INDEX
,DATA_TYPE
,COLLECTION_FLAG
,DISPLAY_NODE_DEPTH
,ARGUMENT_NAME
,SOURCE_OFFSET
,SOURCE_LENGTH
,MUTABLE_FLAG
,RELATIVE_NODE_PATH
,EVENT_EXECUTION_SCOPE
,DATA_NUM_VALUE
,SEEDED_FLAG
)
SELECT
l_new_exprnode_tbl(l_key)
,EXPRESS_ID
,SEQ_NBR
,ITEM_TYPE_ID
,PS_NODE_ID
,ITEM_ID
,decode(l_rule_type, RULE_TYPE_POPULATOR,l_new_filter_set_id, FILTER_SET_ID)
,GRID_COL_ID
,l_new_parent
,PROPERTY_ID
,COMPILE_ADVICE
,COL
,DATA_VALUE
,FIELD_NAME
,EXPR_TYPE
,EXPR_SUBTYPE
,TOKEN_LIST_SEQ
,DELETED_FLAG
,EFF_FROM
,EFF_TO
,SECURITY_MASK
,EFF_MASK
,CHECKOUT_USER
,CONSEQUENT_FLAG
,MODEL_REF_EXPL_ID
,l_new_rule_id
,TEMPLATE_ID
,ARGUMENT_SIGNATURE_ID
,ARGUMENT_INDEX
,PARAM_SIGNATURE_ID
,PARAM_INDEX
,DATA_TYPE
,COLLECTION_FLAG
,DISPLAY_NODE_DEPTH
,ARGUMENT_NAME
,SOURCE_OFFSET
,SOURCE_LENGTH
,MUTABLE_FLAG
,RELATIVE_NODE_PATH
,EVENT_EXECUTION_SCOPE
,DATA_NUM_VALUE
,SEEDED_FLAG
FROM CZ_EXPRESSION_NODES
WHERE expr_node_id=l_key;
l_locked_entities_tbl.DELETE;
SELECT CZ_RULE_FOLDERS_S.NEXTVAL INTO x_out_rule_folder_id FROM dual;
INSERT INTO CZ_RULE_FOLDERS
(RULE_FOLDER_ID,
FOLDER_TYPE,
NAME,
DESC_TEXT,
PARENT_RULE_FOLDER_ID,
TREE_SEQ,
DEVL_PROJECT_ID,
PERSISTENT_RULE_FOLDER_ID,
EFFECTIVE_USAGE_MASK,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
EFFECTIVITY_SET_ID,
DELETED_FLAG,
SECURITY_MASK,
CHECKOUT_USER,
OBJECT_TYPE,
DISABLED_FLAG,
ORIG_SYS_REF)
SELECT
x_out_rule_folder_id,
FOLDER_TYPE,
NAME||'-'||TO_CHAR(x_out_rule_folder_id),
DESC_TEXT,
p_new_parent_folder_id,
TREE_SEQ,
DEVL_PROJECT_ID,
x_out_rule_folder_id,
EFFECTIVE_USAGE_MASK,
EFFECTIVE_FROM,
EFFECTIVE_UNTIL,
EFFECTIVITY_SET_ID,
DELETED_FLAG,
SECURITY_MASK,
CHECKOUT_USER,
OBJECT_TYPE,
DISABLED_FLAG,
ORIG_SYS_REF
FROM CZ_RULE_FOLDERS
WHERE rule_folder_id=p_rule_folder_id AND object_type IN ('RFL', 'RSQ') AND deleted_flag='0';
FOR i IN (SELECT rule_folder_id, object_type FROM CZ_RULE_FOLDERS
WHERE parent_rule_folder_id=p_rule_folder_id AND deleted_flag='0')
LOOP
IF i.object_type IN ('RFL', 'RSQ') THEN
copy_rule_folder(p_rule_folder_id => i.rule_folder_id
,p_new_parent_folder_id => x_out_rule_folder_id
,p_init_msg_list => FND_API.G_FALSE
,x_out_rule_folder_id => l_rule_folder_id
,x_run_id => x_run_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
FOR i IN(SELECT NAME FROM CZ_PS_NODES
START WITH ps_node_id=p_ps_node_id AND deleted_flag='0'
CONNECT BY PRIOR parent_id=ps_node_id AND
PRIOR deleted_flag='0' AND deleted_flag='0')
LOOP
--If the node name contains special characters (bug #3817913), enclose the name into single quotes.
l_name := set_name_entity(i.NAME);
SELECT ps_node_id INTO l_ps_node_id
FROM CZ_PS_NODES
WHERE ps_node_id=p_ps_node_id AND devl_project_id=p_model_id;
FOR i IN(SELECT * FROM CZ_MODEL_REF_EXPLS
START WITH model_ref_expl_id=p_model_ref_expl_id
CONNECT BY PRIOR parent_expl_node_id=model_ref_expl_id
AND deleted_flag='0' AND PRIOR deleted_flag='0')
LOOP
IF i.referring_node_id IS NOT NULL THEN
IF(i.referring_node_id = p_ps_node_id)THEN
--The ps node is a reference itself. In this case l_ref_model_path calculated below will
--duplicate the l_model_path calculated above, but we need it only once.
l_model_path := NULL;
FOR i IN(SELECT parent_id,name FROM CZ_PS_NODES
START WITH ps_node_id=p_ps_node_id AND deleted_flag='0'
CONNECT BY PRIOR parent_id=ps_node_id AND
PRIOR deleted_flag='0' AND deleted_flag='0' AND NVL(virtual_flag,'1')='1')
LOOP
l_name := set_name_entity(i.NAME);
SELECT name INTO l_name FROM CZ_PS_NODES
WHERE ps_node_id=l_ps_node_id;
FOR i IN(SELECT parent_id,name FROM CZ_PS_NODES
START WITH ps_node_id=p_ps_node_id AND deleted_flag='0'
CONNECT BY PRIOR parent_id=ps_node_id AND
PRIOR deleted_flag='0' AND deleted_flag='0' AND NVL(virtual_flag,'1')='1')
LOOP
l_name := set_name_entity(i.NAME);
SELECT name INTO l_name FROM CZ_PS_NODES
WHERE ps_node_id=l_ps_node_id;
FOR i IN (SELECT name, intl_text_id, ps_node_type FROM cz_ps_nodes
START WITH ps_node_id = p_ps_node_id AND deleted_flag='0'
CONNECT BY PRIOR parent_id = ps_node_id AND
PRIOR deleted_flag='0' AND deleted_flag='0') LOOP
l_name := i.name;
SELECT text_str INTO l_name FROM cz_localized_texts_vl WHERE intl_text_id = i.intl_text_id;
SELECT ps_node_type, intl_text_id, name INTO v_ps_node_type, v_intl_text_id, v_name
FROM cz_ps_nodes WHERE ps_node_id = p_ps_node_id;
SELECT text_str INTO v_description FROM cz_localized_texts_vl WHERE intl_text_id = v_intl_text_id;
SELECT ps_node_id INTO l_ps_node_id
FROM CZ_PS_NODES
WHERE ps_node_id=p_ps_node_id AND devl_project_id=p_model_id;
FOR i IN (SELECT * FROM cz_model_ref_expls
START WITH model_ref_expl_id = p_model_ref_expl_id
CONNECT BY PRIOR parent_expl_node_id = model_ref_expl_id
AND deleted_flag='0' AND PRIOR deleted_flag='0') LOOP
IF i.referring_node_id IS NOT NULL THEN
IF(i.referring_node_id = p_ps_node_id)THEN
--The ps node is a reference itself. In this case l_ref_model_path calculated below will
--duplicate the l_model_path calculated above, but we need it only once.
l_model_path := NULL;
SELECT name,enclosing_folder INTO l_repository_path,l_enclosing_folder FROM CZ_RP_ENTRIES
WHERE object_id=p_object_id AND object_type=p_object_type AND deleted_flag='0';
FOR i IN(SELECT name FROM CZ_RP_ENTRIES
START WITH object_id=l_enclosing_folder AND object_type='FLD' AND deleted_flag='0'
CONNECT BY PRIOR enclosing_folder=object_id
AND deleted_flag='0' AND PRIOR deleted_flag='0'
AND object_type='FLD' AND PRIOR object_type='FLD' AND object_id<>0 AND PRIOR object_id<>0)
LOOP
l_name := set_name_entity(i.NAME);
SELECT name INTO l_root_name FROM CZ_RP_ENTRIES
WHERE object_id=0 AND object_type='FLD' AND deleted_flag='0' AND rownum<2;
SELECT name,NVL(parent_rule_folder_id,0)
INTO l_rule_folder_path,l_enclosing_folder FROM CZ_RULE_FOLDERS
WHERE rule_folder_id=p_rule_folder_id AND object_type=p_object_type AND deleted_flag='0';
FOR i IN(SELECT name FROM CZ_RULE_FOLDERS
START WITH rule_folder_id=l_enclosing_folder AND object_type='RFL' AND deleted_flag='0'
CONNECT BY PRIOR parent_rule_folder_id=rule_folder_id
AND deleted_flag='0' AND PRIOR deleted_flag='0'
AND object_type='RFL' AND PRIOR object_type='RFL')
LOOP
l_name := set_name_entity(i.NAME);
SELECT name INTO l_folder_name
FROM cz_rp_entries
WHERE object_id = p_encl_folder_id
AND object_type = 'FLD'
AND deleted_flag = '0';
SELECT object_id,object_type , enclosing_folder
BULK COLLECT INTO l_object_id_tbl,l_object_type_tbl, l_encl_folder_tbl
FROM CZ_RP_ENTRIES
START WITH object_id=p_folder_id AND object_type='FLD' AND deleted_flag='0'
CONNECT BY PRIOR object_id=ENCLOSING_FOLDER AND PRIOR object_type = 'FLD' AND deleted_flag='0'
AND PRIOR deleted_flag='0';
INSERT INTO CZ_RP_ENTRIES
(
OBJECT_TYPE
,OBJECT_ID
,ENCLOSING_FOLDER
,NAME
,DESCRIPTION
,NOTES
,SEEDED_FLAG
,DELETED_FLAG
)
SELECT
OBJECT_TYPE
,l_new_object_id_tbl(i)
,l_new_encl_folder_tbl(i)
,NAME||' - '||TO_CHAR(l_new_object_id_tbl(i))
,DESCRIPTION
,NOTES
,0
,DELETED_FLAG
FROM CZ_RP_ENTRIES
WHERE object_id=l_object_id_tbl(i) AND object_type=l_object_type_tbl(i)
AND deleted_flag='0';
PROCEDURE delete_model_node
(p_ps_node_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_procedure_name CONSTANT VARCHAR2(30) := 'delete_model_node';
SELECT ps_node_id, ps_node_type, virtual_flag
FROM cz_ps_nodes
WHERE parent_id = inPsNodeId
AND deleted_flag = '0';
SELECT ps_node_id, ps_node_type, virtual_flag, feature_type
FROM cz_ps_nodes
WHERE parent_id = p_ps_node_id
AND deleted_flag = '0';
-- finally delete this child node
--------------------------
UPDATE cz_ps_nodes
SET deleted_flag = '1'
WHERE ps_node_id = l_ps_node_tbl(i)
AND ps_node_type NOT IN (436,437,438);
SELECT virtual_flag, ps_node_type, feature_type,
parent_id, tree_seq, devl_project_id, name
INTO l_vf, l_node_type, l_feature_type,
l_parent_id, l_tree_seq, l_devl_project_id, l_node_name
FROM cz_ps_nodes
WHERE ps_node_id = p_ps_node_id
AND deleted_flag = '0';
CZ_REFS.delete_node(p_ps_node_id,l_node_type,p_out_err,p_del_logically);
UPDATE cz_ps_nodes
SET deleted_flag = '1'
WHERE ps_node_id = p_ps_node_id
AND ps_node_type NOT IN (436,437,438);
UPDATE cz_ps_nodes
SET tree_seq = tree_seq - 1
WHERE parent_id = l_parent_id
AND devl_project_id = l_devl_project_id
AND tree_seq > l_tree_seq
AND deleted_flag = '0';
handle_Error(p_procedure_name => 'delete_model_node',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END delete_model_node;
PROCEDURE delete_ui_def
(p_ui_def_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
TYPE l_tName IS TABLE OF cz_ui_defs.name%TYPE INDEX BY BINARY_INTEGER;
l_procedure_name CONSTANT VARCHAR2(30) := 'delete_ui_def';
SELECT DISTINCT r.ui_def_id, d.name
FROM cz_ui_refs r, cz_ui_defs d
WHERE r.ref_ui_def_id = inUIDefID
AND r.ui_def_id = d.ui_def_id
AND d.deleted_flag = '0'
AND r.deleted_flag = '0'
AND d.devl_project_id IN (SELECT object_id
FROM cz_rp_entries
WHERE cz_rp_entries.object_type = 'PRJ'
AND cz_rp_entries.deleted_flag = '0');
SELECT DISTINCT d.ui_def_id, d.name
FROM cz_ui_nodes n, cz_ui_defs d
WHERE n.ui_def_ref_id = inUIDefID
AND n.ui_def_id = d.ui_def_id
AND d.deleted_flag = '0'
AND n.deleted_flag = '0';
SELECT devl_project_id, seeded_flag, ui_style, name
INTO l_devl_project_id, l_seeded_flag, l_ui_style, l_uidef_name
FROM cz_ui_defs
WHERE ui_def_id = p_ui_def_id;
l_ui_def_tbl.DELETE;
l_model_name_tbl.DELETE;
FOR k IN(SELECT publication_id FROM CZ_MODEL_PUBLICATIONS
WHERE ui_def_id=p_ui_def_id AND deleted_flag='0')
LOOP
l_ui_is_published := TRUE;
UPDATE cz_ui_defs
SET deleted_flag = '1'
WHERE ui_def_id = p_ui_def_id;
UPDATE CZ_RULES
SET deleted_flag = '1'
WHERE ui_def_id = p_ui_def_id;
UPDATE CZ_LOCALIZED_TEXTS
SET deleted_flag = '1'
WHERE ui_def_id = p_ui_def_id;
UPDATE CZ_UI_ACTIONS
SET deleted_flag = '1'
WHERE ui_def_id = p_ui_def_id;
UPDATE CZ_UI_PAGE_REFS
SET deleted_flag = '1'
WHERE ui_def_id = p_ui_def_id;
UPDATE CZ_UI_PAGES
SET deleted_flag = '1'
WHERE ui_def_id = p_ui_def_id;
UPDATE CZ_UI_PAGE_SETS
SET deleted_flag = '1'
WHERE ui_def_id = p_ui_def_id;
UPDATE CZ_UI_PAGE_ELEMENTS
SET deleted_flag = '1'
WHERE ui_def_id = p_ui_def_id;
FOR I in (SELECT JRAD_DOC from CZ_UI_PAGES
WHERE ui_def_id = p_ui_def_id)
LOOP
IF jdr_docbuilder.documentexists(i.JRAD_DOC)=TRUE
THEN
jdr_docbuilder.deleteDocument(i.JRAD_DOC);
handle_Error(p_procedure_name => 'delete_ui_def',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END delete_ui_def;
PROCEDURE delete_rule_folder
(p_rule_folder_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_procedure_name CONSTANT VARCHAR2(30) := 'delete_rule_folder';
SELECT devl_project_id, nvl(parent_rule_folder_id,0), name
INTO l_devl_project_id, l_parent_rule_folder_id, l_rfl_name
FROM cz_rule_folders
WHERE rule_folder_id = p_rule_folder_id
AND object_type = 'RFL'
AND deleted_flag = '0';
l_rule_folder_id_tbl.delete;
SELECT rule_folder_id, object_type
BULK
COLLECT
INTO l_rule_folder_id_tbl, l_object_type_tbl
FROM cz_rule_folders
START WITH rule_folder_id = p_rule_folder_id and object_type='RFL'
CONNECT BY PRIOR rule_folder_id = parent_rule_folder_id
AND PRIOR object_type in ('RFL','RSQ')
AND PRIOR deleted_flag = '0'
AND deleted_flag = '0';
update cz_rule_folders
set deleted_flag = '1'
where rule_folder_id = l_rule_folder_id_tbl(i)
and object_type=l_object_type_tbl(i);
handle_Error(p_procedure_name => 'delete_rule_folder',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END delete_rule_folder;
PROCEDURE delete_rule_sequence
(p_rule_sequence_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_procedure_name CONSTANT VARCHAR2(30) := 'delete_rule_sequence';
SELECT devl_project_id, name
INTO l_devl_project_id, l_rsq_name
FROM cz_rule_folders
WHERE rule_folder_id = p_rule_sequence_id
AND object_type = 'RSQ';
UPDATE cz_rules
SET deleted_flag = '1'
WHERE rule_folder_id = p_rule_sequence_id;
UPDATE cz_rule_folders
SET deleted_flag = '1'
WHERE rule_folder_id = p_rule_sequence_id
AND object_type = 'RSQ';
handle_Error(p_procedure_name => 'delete_rule_sequence',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END delete_rule_sequence;
PROCEDURE delete_item_type
(p_item_type_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_procedure_name CONSTANT VARCHAR2(30) := 'delete_item_type';
SELECT item_id
FROM cz_item_masters
WHERE item_type_id = p_item_type_id
AND deleted_flag = '0';
l_item_id_tbl.DELETE;
UPDATE cz_item_property_values
SET deleted_flag = '1'
WHERE item_id = l_item_id_tbl(i);
UPDATE cz_item_masters
SET item_type_id = G_DEFAULT_ITEM_TYPE
WHERE item_type_id = p_item_type_id;
UPDATE cz_item_type_properties
SET deleted_flag ='1'
WHERE item_type_id = p_item_type_id;
UPDATE cz_item_types
SET deleted_flag ='1'
WHERE item_type_id = p_item_type_id;
handle_Error(p_procedure_name => 'delete_item_type',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END delete_item_type;
PROCEDURE is_model_deleteable (p_model_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
TYPE number_type_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
MODEL_DELETED EXCEPTION;
l_deleted_flag VARCHAR2(1);
l_procedure_name CONSTANT VARCHAR2(30) := 'is_model_deleteable';
SELECT a.seeded_flag, a.name, a.deleted_flag, checkout_user
INTO l_seeded_flag, l_model_name, l_deleted_flag, l_checkout_user
FROM cz_rp_entries a, cz_devl_projects b
WHERE object_id = p_model_id
AND object_type = 'PRJ'
AND object_id = devl_project_id;
IF (l_deleted_flag = '1') THEN
RAISE MODEL_DELETED;
SELECT d.devl_project_id, d.name
BULK COLLECT INTO x_ref_model_ids_tbl, x_ref_model_names_tbl
FROM cz_ps_nodes p, cz_devl_projects d
WHERE p.reference_id = p_model_id
AND p.ps_node_type IN (263, 264)
AND p.deleted_flag = '0'
AND p.devl_project_id = d.devl_project_id
AND d.deleted_flag = '0';
SELECT publication_id BULK COLLECT INTO l_publication_tbl
FROM cz_model_publications
WHERE object_id = p_model_id
AND object_type = 'PRJ'
AND deleted_flag = '0';
WHEN MODEL_DELETED THEN
NULL;
handle_Error(p_procedure_name => 'is_model_deleteable',
p_error_message => ' unexpected error in cz_security_pvt.has_model_privileges()',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
handle_Error(p_procedure_name => 'is_model_deleteable',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END is_model_deleteable;
PROCEDURE delete_model(p_model_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2)
IS
LOCK_MODEL_EXCP EXCEPTION;
is_model_deleteable(p_model_id, x_return_status, x_msg_count, x_msg_data);
UPDATE cz_devl_projects
SET deleted_flag = '1',
name = append_name (p_model_id, 'PRJ', name)
WHERE devl_project_id = p_model_id
AND deleted_flag = '0';
handle_Error(p_procedure_name => 'delete_model',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END delete_model;
UPDATE cz_rp_entries
SET name = l_usage_name
WHERE object_id = usageID
AND object_type = 'USG';
UPDATE cz_model_usages
SET name = l_usage_name,
note=(select note from cz_model_usages
where model_usage_id=usageID)
WHERE model_usage_id = usageID;
update cz_model_usages_tl target
set (LANGUAGE,SOURCE_LANG,DESCRIPTION)=(select LANGUAGE,SOURCE_LANG,DESCRIPTION
from
cz_model_usages_tl source where model_usage_id=p_model_usage_id and target.language=source.language)
where model_usage_id=usageID;
INSERT INTO CZ_PROPERTIES
(
PROPERTY_ID
,PROPERTY_UNIT
,DESC_TEXT
,NAME
,DATA_TYPE
,DEF_VALUE
,USER_NUM01
,USER_NUM02
,USER_NUM03
,USER_NUM04
,USER_STR01
,USER_STR02
,USER_STR03
,USER_STR04
,DELETED_FLAG
,EFF_FROM
,EFF_TO
,SECURITY_MASK
,EFF_MASK
,CHECKOUT_USER
,def_num_value
)
SELECT
x_new_property_id
,PROPERTY_UNIT
,DESC_TEXT
,l_name
,DATA_TYPE
,DEF_VALUE
,USER_NUM01
,USER_NUM02
,USER_NUM03
,USER_NUM04
,USER_STR01
,USER_STR02
,USER_STR03
,USER_STR04
,DELETED_FLAG
,EFF_FROM
,EFF_TO
,SECURITY_MASK
,EFF_MASK
,CHECKOUT_USER
,def_num_value
FROM CZ_PROPERTIES
WHERE property_id=p_property_id;
FOR i IN (SELECT def_num_value FROM cz_properties
WHERE property_id=p_property_id
AND data_type=8
AND def_num_value IS NOT NULL) LOOP
l_text_id := i.def_num_value;
UPDATE cz_properties
SET def_num_value=l_new_intl_text_id
WHERE property_id=x_new_property_id;
INSERT INTO CZ_RP_ENTRIES
(
OBJECT_TYPE
,OBJECT_ID
,ENCLOSING_FOLDER
,NAME
,DESCRIPTION
,NOTES
,SEEDED_FLAG
,DELETED_FLAG
)
SELECT
OBJECT_TYPE
,x_new_property_id
,p_encl_folder_id
,l_name
,DESCRIPTION
,NOTES
,'0'
,DELETED_FLAG
FROM CZ_RP_ENTRIES
WHERE object_id= p_property_id AND object_type='PRP'
AND deleted_flag='0';
INSERT INTO CZ_RP_ENTRIES
(
OBJECT_TYPE
,OBJECT_ID
,ENCLOSING_FOLDER
,NAME
,DESCRIPTION
,NOTES
,SEEDED_FLAG
,DELETED_FLAG
)
SELECT
OBJECT_TYPE
,x_new_effectivity_set_id
,p_encl_folder_id
,l_name
,DESCRIPTION
,NOTES
,'0'
,DELETED_FLAG
FROM CZ_RP_ENTRIES
WHERE object_id= p_effectivity_set_id AND object_type='EFF'
AND deleted_flag='0';
INSERT INTO CZ_EFFECTIVITY_SETS
(
EFFECTIVITY_SET_ID
,NAME
,DESCRIPTION
,NOTE
,EFFECTIVE_FROM
,EFFECTIVE_UNTIL
,USER_STR01
,USER_STR02
,USER_STR03
,USER_STR04
,USER_NUM01
,USER_NUM02
,USER_NUM03
,USER_NUM04
,DELETED_FLAG
)
SELECT
x_new_effectivity_set_id
,l_name
,DESCRIPTION
,NOTE
,EFFECTIVE_FROM
,EFFECTIVE_UNTIL
,USER_STR01
,USER_STR02
,USER_STR03
,USER_STR04
,USER_NUM01
,USER_NUM02
,USER_NUM03
,USER_NUM04
,DELETED_FLAG
FROM CZ_EFFECTIVITY_SETS
WHERE effectivity_set_id = p_effectivity_set_id;
INSERT INTO CZ_ARCHIVES(
ARCHIVE_ID
,NAME
,DESCRIPTION
,ARCHIVE_TYPE
,ARCHIVE_BLOB
,ARCHIVE_URL
,PERSISTENT_ARCHIVE_ID
,DELETED_FLAG
,DOCUMENTATION_URL
)
SELECT
x_new_archive_id
,NAME||' - '||TO_CHAR(x_new_archive_id)
,DESCRIPTION
,ARCHIVE_TYPE
,ARCHIVE_BLOB
,ARCHIVE_URL
,PERSISTENT_ARCHIVE_ID
,DELETED_FLAG
,DOCUMENTATION_URL
FROM CZ_ARCHIVES
WHERE archive_id = p_archive_id;
INSERT INTO CZ_RP_ENTRIES
(
OBJECT_TYPE
,OBJECT_ID
,ENCLOSING_FOLDER
,NAME
,DESCRIPTION
,NOTES
,SEEDED_FLAG
,DELETED_FLAG
)
SELECT
OBJECT_TYPE
,x_new_archive_id
,p_encl_folder_id
,NAME||' - '||TO_CHAR(x_new_archive_id)
,DESCRIPTION
,NOTES
,'0'
,DELETED_FLAG
FROM CZ_RP_ENTRIES
WHERE object_id= p_archive_id AND object_type='ARC'
AND deleted_flag='0';
SELECT cz_ui_page_elements_s.nextval INTO l_new_seq FROM dual;
SELECT cz_ui_page_elements_s.nextval INTO l_new_seq FROM dual;
SELECT rule_folder_id INTO l_rule_folder_id
FROM cz_rules
WHERE cz_rules.rule_id = l_element_id
AND cz_rules.deleted_flag = '0';
SELECT cz_ui_actions_s.nextval INTO l_new_element_id FROM dual;
INSERT INTO CZ_UI_ACTIONS
(UI_ACTION_ID,UI_DEF_ID,SOURCE_PAGE_ID,CONTEXT_COMPONENT_ID,ELEMENT_ID,RENDER_CONDITION_ID,
UI_ACTION_TYPE,TARGET_UI_DEF_ID,TARGET_PERSISTENT_NODE_ID,TARGET_NODE_PATH,TARGET_PAGE_SET_ID,
TARGET_PAGE_ID,TARGET_URL,FRAME_NAME,TARGET_ANCHOR,DELETED_FLAG,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,SEEDED_FLAG,CX_COMMAND_NAME,WINDOW_PARAMETERS,TARGET_WINDOW_TYPE,TARGET_WINDOW_NAME,
TARGET_EXPL_NODE_ID,URL_PROPERTY_ID )
SELECT l_new_element_id,UI_DEF_ID,SOURCE_PAGE_ID,CONTEXT_COMPONENT_ID,ELEMENT_ID,RENDER_CONDITION_ID,
UI_ACTION_TYPE,TARGET_UI_DEF_ID,TARGET_PERSISTENT_NODE_ID,TARGET_NODE_PATH,TARGET_PAGE_SET_ID,
TARGET_PAGE_ID,TARGET_URL,FRAME_NAME,TARGET_ANCHOR,DELETED_FLAG,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,SEEDED_FLAG,CX_COMMAND_NAME,WINDOW_PARAMETERS,TARGET_WINDOW_TYPE,TARGET_WINDOW_NAME,
TARGET_EXPL_NODE_ID,URL_PROPERTY_ID FROM CZ_UI_ACTIONS
WHERE ui_def_id = 0
AND UI_ACTION_ID = l_element_id;
INSERT
INTO cz_ui_template_elements (TEMPLATE_ID,
UI_DEF_ID,
ELEMENT_TYPE,
ELEMENT_ID,
PERSISTENT_ELEMENT_ID,
DELETED_FLAG,
SEEDED_FLAG)
VALUES (p_new_template_id,
0,
g_element_type_tbl(p_id_type || l_element_id),
l_new_element_id,
l_new_element_id,
'0',
'0');
g_element_type_tbl.DELETE;
FOR i in (SELECT persistent_element_id,
element_type,
decode(element_type,
552, G_UI_ACTION_TYPE,
8, G_INTL_TEXT_TYPE,
G_RULE_TYPE) entity_type
FROM cz_ui_template_elements
WHERE template_id = p_template_id
AND ui_def_Id = 0
AND deleted_flag = '0')
LOOP
-- Populate the table as follows
-- R12345 => 33
-- R64354 => 34
-- T12345 => 8
-- We need to use the prefixes (R,T,A) because the rule_ids, text_ids and action_ids can clash
-- We use the following table for two purposes
-- 1. To determine if an id found in the XML has a representation in cz_ui_template_elements
-- If it does, we copy the cz_ui_tempalte_elements record. If it does not, then the id is
-- probably a seeded id and we wont create a cz_ui_template_elements record for it.
-- 2. In case of a UI Condition, to find the rule_type which can be 33 or 34.
g_element_type_tbl(i.entity_type || TO_CHAR(i.persistent_element_id)) := i.element_type;
g_attribute_map.DELETE;
SELECT jrad_doc, template_name, template_type
INTO l_jrad_doc, l_template_name, l_template_type
FROM CZ_UI_TEMPLATES
WHERE ui_def_id=0 AND template_id=p_template_id;
INSERT INTO CZ_UI_TEMPLATES(
TEMPLATE_ID
,UI_DEF_ID
,TEMPLATE_NAME
,TEMPLATE_TYPE
,TEMPLATE_DESC
,PARENT_CONTAINER_TYPE
,JRAD_DOC
,BUTTON_BAR_TEMPLATE_ID
,MESSAGE_TYPE
,MAIN_MESSAGE_ID
,TITLE_ID
,DELETED_FLAG
,SEEDED_FLAG
,LAYOUT_UI_STYLE
,ROOT_REGION_TYPE
,BUTTON_BAR_TEMPL_UIDEF_ID
,ROOT_ELEMENT_SIGNATURE_ID
,AMN_USAGE
)
SELECT
x_new_template_id
,UI_DEF_ID
,l_name
,TEMPLATE_TYPE
,TEMPLATE_DESC
,PARENT_CONTAINER_TYPE
,l_copied_jrad_doc
,BUTTON_BAR_TEMPLATE_ID
,MESSAGE_TYPE
,MAIN_MESSAGE_ID
,TITLE_ID
,DELETED_FLAG
,'0'
,LAYOUT_UI_STYLE
,ROOT_REGION_TYPE
,BUTTON_BAR_TEMPL_UIDEF_ID
,ROOT_ELEMENT_SIGNATURE_ID
,AMN_USAGE
FROM CZ_UI_TEMPLATES
WHERE ui_def_id=0 AND template_id = p_template_id;
SELECT title_id, main_message_id
INTO l_old_title_id, l_old_main_message_id
FROM cz_ui_templates
WHERE template_id = p_template_id
AND ui_def_id = 0;
UPDATE cz_ui_templates
SET title_id = l_title_id,
main_message_id = l_main_message_id
WHERE template_id = x_new_template_id
AND ui_def_id = 0;
INSERT INTO CZ_UI_REF_TEMPLATES
(
TEMPLATE_ID
,REF_TEMPLATE_ID
,DELETED_FLAG
,TEMPLATE_UI_DEF_ID
,REF_TEMPLATE_UI_DEF_ID
,SEEDED_FLAG
,REF_TEMPL_SEEDED_FLAG
,REF_COUNT
)
SELECT
x_new_template_id
,REF_TEMPLATE_ID
,DELETED_FLAG
,0
,REF_TEMPLATE_UI_DEF_ID
,'0'
,REF_TEMPL_SEEDED_FLAG
,REF_COUNT
FROM CZ_UI_REF_TEMPLATES
WHERE template_id=p_template_id AND
template_ui_def_id=0 AND
deleted_flag='0';
INSERT INTO CZ_RP_ENTRIES
(
OBJECT_TYPE
,OBJECT_ID
,ENCLOSING_FOLDER
,NAME
,DESCRIPTION
,NOTES
,SEEDED_FLAG
,DELETED_FLAG
)
SELECT
OBJECT_TYPE
,x_new_template_id
,p_encl_folder_id
,l_name
,DESCRIPTION
,NOTES
,'0'
,DELETED_FLAG
FROM CZ_RP_ENTRIES
WHERE object_id= p_template_id AND object_type='UCT'
AND deleted_flag='0';
INSERT INTO CZ_UI_DEFS(
UI_DEF_ID
,DESC_TEXT
,NAME
,DEVL_PROJECT_ID
,COMPONENT_ID
,TREE_SEQ
,DELETED_FLAG
,EFF_FROM
,EFF_TO
,SECURITY_MASK
,EFF_MASK
,UI_STYLE
,GEN_VERSION
,TREENODE_DISPLAY_SOURCE
,GEN_HEADER
,LOOK_AND_FEEL
,CONTROLS_PER_SCREEN
,PRIMARY_NAVIGATION
,PERSISTENT_UI_DEF_ID
,MODEL_TIMESTAMP
,UI_STATUS
,PAGE_SET_ID
,START_PAGE_ID
,ERR_RUN_ID
,START_URL
,PAGE_LAYOUT
,PRICE_UPDATE
,SEEDED_FLAG
,MASTER_TEMPLATE_FLAG
,PRICE_DISPLAY
,FROM_MASTER_TEMPLATE_ID
,PAGIN_MAXCONTROLS
,PAGIN_NONINST
,PAGIN_NONINST_REFCOMP
,CONTROL_LAYOUT
,PAGIN_DRILLDOWNCTRL
,OUTER_TEMPLATE_USAGE
,PAGIN_BOMOC
,BOMUI_LAYOUT
,BOMQTYINPUTCTRLS
,CTRLTEMPLUSE_BOM
,CTRLTEMPLUSE_NONBOM
,NONBOM_UILAYOUT
,CTRLTEMPLUSE_COMMON
,CTRLTEMPLUSE_REQDMSG
,CTRLTEMPLUSE_OPTMSG
,MENU_CAPTION_RULE_ID
,PAGE_CAPTION_RULE_ID
,PRESERVE_MODEL_HIERARCHY
,EMPTY_UI_FLAG
,SHOW_TRAIN
,PAGINATION_SLOT
,DRILLDOWN_CONTROL_TEXT_ID
,DRILLDOWN_IMAGE_URL
,ROWS_PER_TABLE
,CTRLTEMPLATEUSE_BUTTONBAR
,CTRLTEMPLATEUSE_UTILITYPAGE
,OPTION_SORT_SELECT_FIRST
,OPTION_SORT_ORDER
,OPTION_SORT_METHOD
,OPTION_SORT_PROPERTY_ID
,SHOW_ALL_NODES_FLAG
)
SELECT
x_new_ui_def_id
,DESC_TEXT
,l_name
,DEVL_PROJECT_ID
,COMPONENT_ID
,TREE_SEQ
,DELETED_FLAG
,EFF_FROM
,EFF_TO
,SECURITY_MASK
,EFF_MASK
,UI_STYLE
,GEN_VERSION
,TREENODE_DISPLAY_SOURCE
,GEN_HEADER
,LOOK_AND_FEEL
,CONTROLS_PER_SCREEN
,PRIMARY_NAVIGATION
,PERSISTENT_UI_DEF_ID
,MODEL_TIMESTAMP
,UI_STATUS
,PAGE_SET_ID
,START_PAGE_ID
,ERR_RUN_ID
,START_URL
,PAGE_LAYOUT
,PRICE_UPDATE
,'0'
,MASTER_TEMPLATE_FLAG
,PRICE_DISPLAY
,FROM_MASTER_TEMPLATE_ID
,PAGIN_MAXCONTROLS
,PAGIN_NONINST
,PAGIN_NONINST_REFCOMP
,CONTROL_LAYOUT
,PAGIN_DRILLDOWNCTRL
,OUTER_TEMPLATE_USAGE
,PAGIN_BOMOC
,BOMUI_LAYOUT
,BOMQTYINPUTCTRLS
,CTRLTEMPLUSE_BOM
,CTRLTEMPLUSE_NONBOM
,NONBOM_UILAYOUT
,CTRLTEMPLUSE_COMMON
,CTRLTEMPLUSE_REQDMSG
,CTRLTEMPLUSE_OPTMSG
,MENU_CAPTION_RULE_ID
,PAGE_CAPTION_RULE_ID
,PRESERVE_MODEL_HIERARCHY
,EMPTY_UI_FLAG
,SHOW_TRAIN
,PAGINATION_SLOT
,DRILLDOWN_CONTROL_TEXT_ID
,DRILLDOWN_IMAGE_URL
,ROWS_PER_TABLE
,CTRLTEMPLATEUSE_BUTTONBAR
,CTRLTEMPLATEUSE_UTILITYPAGE
,OPTION_SORT_SELECT_FIRST
,OPTION_SORT_ORDER
,OPTION_SORT_METHOD
,OPTION_SORT_PROPERTY_ID
,SHOW_ALL_NODES_FLAG
FROM CZ_UI_DEFS
WHERE ui_def_id = p_ui_def_id;
INSERT INTO CZ_UI_CONT_TYPE_TEMPLS(
UI_DEF_ID
,CONTENT_TYPE
,TEMPLATE_ID
,DELETED_FLAG
,MASTER_TEMPLATE_FLAG
,SEEDED_FLAG
,TEMPLATE_UI_DEF_ID
,WRAP_TEMPLATE_FLAG
)
SELECT
x_new_ui_def_id
,CONTENT_TYPE
,TEMPLATE_ID
,DELETED_FLAG
,MASTER_TEMPLATE_FLAG
,'0'
,TEMPLATE_UI_DEF_ID
,WRAP_TEMPLATE_FLAG
FROM CZ_UI_CONT_TYPE_TEMPLS
WHERE ui_def_id = p_ui_def_id;
INSERT INTO CZ_UI_IMAGES
(
UI_DEF_ID
,MASTER_TEMPLATE_FLAG
,IMAGE_USAGE_CODE
,IMAGE_FILE
,DELETED_FLAG
,SEEDED_FLAG
,ENTITY_CODE
)
SELECT
x_new_ui_def_id
,MASTER_TEMPLATE_FLAG
,IMAGE_USAGE_CODE
,IMAGE_FILE
,DELETED_FLAG
,'0'
,ENTITY_CODE
FROM CZ_UI_IMAGES
WHERE ui_def_id = p_ui_def_id AND deleted_flag='0';
INSERT INTO CZ_RP_ENTRIES
(
OBJECT_TYPE
,OBJECT_ID
,ENCLOSING_FOLDER
,NAME
,DESCRIPTION
,NOTES
,SEEDED_FLAG
,DELETED_FLAG
)
SELECT
OBJECT_TYPE
,x_new_ui_def_id
,p_encl_folder_id
,l_name
,DESCRIPTION
,NOTES
,'0'
,DELETED_FLAG
FROM CZ_RP_ENTRIES
WHERE object_id= p_ui_def_id AND object_type='UMT'
AND deleted_flag='0';
PROCEDURE is_eff_set_deleteable(p_eff_set_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
TYPE prj_name_tbl_type IS TABLE of cz_devl_projects.name%TYPE INDEX BY BINARY_INTEGER;
SELECT object_id,name
INTO l_eff_set_id,l_eff_name
FROM cz_rp_entries
WHERE cz_rp_entries.object_id = p_eff_set_id
AND cz_rp_entries.object_type = 'EFF'
AND cz_rp_entries.deleted_flag = '0'
AND cz_rp_entries.seeded_flag <> '1';
SELECT name BULK COLLECT INTO l_prj_name_tbl
FROM cz_devl_projects
WHERE deleted_flag = '0' AND devl_project_id IN
(SELECT devl_project_id FROM cz_rules
WHERE effectivity_set_id = p_eff_set_id
AND deleted_flag = '0'
UNION ALL
SELECT devl_project_id FROM cz_ps_nodes
WHERE effectivity_set_id = p_eff_set_id
AND deleted_flag = '0');
handle_Error(p_procedure_name => 'is_eff_set_deleteable',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END is_eff_set_deleteable;
PROCEDURE delete_eff_set(p_eff_set_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_return_status VARCHAR2(1);
is_eff_set_deleteable(p_eff_set_id,l_return_status,l_msg_count,l_msg_data);
UPDATE cz_effectivity_sets
SET deleted_flag = '1',
name = append_name(p_eff_set_id, 'EFF', name)
WHERE effectivity_set_id = p_eff_set_id;
UPDATE cz_rp_entries
SET deleted_flag = '1',
name = append_name(p_eff_set_id, 'EFF', name)
WHERE object_id = p_eff_set_id
AND object_type = 'EFF';
handle_Error(p_procedure_name => 'delete_eff_set',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END delete_eff_set;
PROCEDURE is_archive_deleteable(p_archive_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_devl_project_tbl t_indexes;
SELECT object_id,name,seeded_flag
INTO l_object_id,l_object_name,l_seeded_flag
FROM cz_rp_entries
WHERE cz_rp_entries.object_id = p_archive_id
AND cz_rp_entries.object_type = 'ARC'
AND cz_rp_entries.deleted_flag = '0';
l_devl_project_tbl.DELETE;
SELECT DISTINCT devl_project_id
BULK
COLLECT
INTO l_devl_project_tbl
FROM cz_archive_refs
WHERE cz_archive_refs.archive_id = p_archive_id
AND cz_archive_refs.deleted_flag = '0'
AND cz_archive_refs.devl_project_id IN (SELECT object_id
FROM cz_rp_entries
WHERE object_type = 'PRJ'
AND deleted_flag = '0');
SELECT name into l_devl_name FROM cz_devl_projects WHERE devl_project_id = l_devl_project_tbl(i);
handle_Error(p_procedure_name => 'is_archive_deleteable',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END is_archive_deleteable;
PROCEDURE delete_archive(p_archive_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_return_status VARCHAR2(1);
is_archive_deleteable(p_archive_id,l_return_status,l_msg_count,l_msg_data);
UPDATE cz_rp_entries
set deleted_flag = '1',
name = append_name(p_archive_id, 'ARC', name)
where object_id = p_archive_id
and object_type = 'ARC'
and deleted_flag = '0'
and seeded_flag <> '1';
UPDATE cz_archives
SET cz_archives.deleted_flag = '1',
cz_archives.name = append_name(p_archive_id, 'ARC', name)
WHERE cz_archives.archive_id = p_archive_id;
handle_Error(p_procedure_name => 'delete_archive',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END delete_archive;
PROCEDURE is_property_deleteable (p_property_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_object_id cz_rp_entries.object_id%TYPE;
SELECT object_id,name,seeded_flag
INTO l_object_id,l_object_name,l_seeded_flag
FROM cz_rp_entries
WHERE cz_rp_entries.object_id = p_property_id
AND cz_rp_entries.object_type = 'PRP'
AND cz_rp_entries.deleted_flag = '0';
SELECT 1
INTO l_prop_count
FROM CZ_PSNODE_PROPVAL_V
WHERE ps_node_id IN (select ps_node_id
from cz_ps_nodes
where cz_ps_nodes.deleted_flag = '0')
AND property_id = p_property_id
AND rownum < 2;
SELECT 1
INTO l_item_prop_count
FROM CZ_ITEM_TYPE_PROPERTIES
WHERE item_type_id IN (select item_type_id
from cz_item_types
where deleted_flag = '0')
AND property_id = p_property_id
AND deleted_flag = '0';
SELECT 1
INTO l_ui_pages_count
FROM CZ_UI_PAGES
WHERE property_id = p_property_id
AND deleted_flag = '0';
handle_Error(p_procedure_name => 'is_property_deleteable',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END is_property_deleteable ;
PROCEDURE delete_property(p_property_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_return_status VARCHAR2(1);
is_property_deleteable(p_property_id,l_return_status,l_msg_count,l_msg_data);
UPDATE cz_rp_entries
SET deleted_flag = '1',
name = append_name(p_property_id, 'PRP', name)
WHERE object_id = p_property_id
AND object_type = 'PRP'
AND deleted_flag = '0'
AND seeded_flag <> '1';
UPDATE cz_properties
SET cz_properties.deleted_flag = '1',
cz_properties.name = append_name(p_property_id, 'PRP', name)
WHERE property_id = p_property_id;
handle_Error(p_procedure_name => 'delete_property',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END delete_property;
PROCEDURE is_umt_deleteable (p_umt_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_object_id cz_rp_entries.object_id%TYPE;
SELECT object_id,name,seeded_flag
INTO l_object_id,l_object_name,l_seeded_flag
FROM cz_rp_entries
WHERE cz_rp_entries.object_id = p_umt_id
AND cz_rp_entries.object_type = 'UMT'
AND cz_rp_entries.deleted_flag = '0';
SELECT name
BULK
COLLECT
INTO l_ui_name_tbl
FROM cz_ui_defs ui
WHERE from_master_template_id = p_umt_id
AND deleted_flag = '0'
AND seeded_flag <> '1'
AND exists (SELECT 1 FROM cz_rp_entries WHERE deleted_flag = '0'
AND object_id = ui.devl_project_id and object_type = 'PRJ');
handle_Error(p_procedure_name => 'is_umt_deleteable',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END is_umt_deleteable;
PROCEDURE delete_umt(p_umt_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_return_status VARCHAR2(1);
is_umt_deleteable(p_umt_id,l_return_status,l_msg_count,l_msg_data);
UPDATE cz_rp_entries
SET deleted_flag = '1',
name = append_name(p_umt_id, 'UMT', name)
WHERE object_id = p_umt_id
AND object_type = 'UMT';
UPDATE cz_ui_defs
set deleted_flag = '1',
name = append_name(p_umt_id, 'UMT', name)
WHERE ui_def_id = p_umt_id
AND master_template_flag = '1'
AND seeded_flag <> '1';
handle_Error(p_procedure_name => 'delete_umt',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END delete_umt;
PROCEDURE is_uct_deleteable(p_uct_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
TYPE ui_name_tbl is TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
SELECT object_id,name,seeded_flag
INTO l_object_id,l_object_name,l_seeded_flag
FROM cz_rp_entries
WHERE cz_rp_entries.object_id = p_uct_id
AND cz_rp_entries.object_type = 'UCT'
AND cz_rp_entries.deleted_flag = '0';
l_ui_name_tbl.DELETE;
SELECT name name, master_template_flag
BULK COLLECT INTO l_ui_name_tbl, l_mt_flag_tbl
FROM cz_ui_defs ui
WHERE deleted_flag = '0'
AND ui_def_id IN
(SELECT ui_def_id
FROM cz_ui_cont_type_templs
WHERE cz_ui_cont_type_templs.template_id = p_uct_id
AND cz_ui_cont_type_templs.template_ui_def_id = 0
AND cz_ui_cont_type_templs.deleted_flag = '0'
UNION ALL
SELECT ui_def_id
FROM cz_ui_page_elements
WHERE cz_ui_page_elements.ctrl_template_id = p_uct_id
AND cz_ui_page_elements.ctrl_template_ui_def_id = 0
AND cz_ui_page_elements.deleted_flag = '0')
AND exists (SELECT 1 FROM cz_rp_entries WHERE deleted_flag = '0'
AND object_id = ui.devl_project_id and object_type = 'PRJ');
SELECT template_id, template_ui_def_id
BULK COLLECT INTO l_ref_template_tbl, l_ref_templ_ui_tbl
FROM cz_ui_ref_templates
WHERE cz_ui_ref_templates.deleted_flag = '0'
AND cz_ui_ref_templates.ref_template_id = p_uct_id
AND cz_ui_ref_templates.ref_template_ui_def_id = 0;
SELECT template_name INTO l_ref_template_name
FROM cz_ui_templates
WHERE cz_ui_templates.template_id = l_ref_template_tbl(i)
AND cz_ui_templates.ui_def_id = l_ref_templ_ui_tbl(i)
AND cz_ui_templates.deleted_flag = '0';
handle_Error(p_procedure_name => 'is_uct_deleteable',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END is_uct_deleteable;
PROCEDURE delete_uct(p_uct_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_return_status VARCHAR2(1);
is_uct_deleteable(p_uct_id,l_return_status,l_msg_count,l_msg_data);
SELECT template_name INTO l_template_name FROM cz_ui_templates
WHERE template_id = p_uct_id;
UPDATE cz_rp_entries
SET deleted_flag = '1',
name = append_name(p_uct_id, 'UCT', name)
WHERE object_id = p_uct_id
AND object_type = 'UCT'
AND seeded_flag <> '1';
UPDATE cz_ui_templates
SET deleted_flag = '1',
template_name = append_name(p_uct_id, 'UCT', template_name)
WHERE template_id = p_uct_id;
handle_Error(p_procedure_name => 'delete_uct',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END delete_uct;
PROCEDURE is_usage_deleteable(p_usage_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
TYPE t_indexes IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
SELECT name
INTO l_usage_name
FROM cz_model_usages
WHERE cz_model_usages.model_usage_id = p_usage_id
AND cz_model_usages.in_use <> 'X';
SELECT 1
INTO l_usage_count
FROM cz_publication_usages
WHERE cz_publication_usages.usage_id = p_usage_id
AND cz_publication_usages.publication_id IN (SELECT publication_id
FROM cz_model_publications
WHERE cz_model_publications.deleted_flag = '0')
AND rownum < 2;
v_nodes.DELETE;
v_rules.DELETE;
v_masks_nodes.DELETE;
v_masks_rules.DELETE;
SELECT ps_node_id,
effective_usage_mask
BULK
COLLECT
INTO v_nodes,
v_masks_nodes
FROM cz_ps_nodes
WHERE effective_usage_mask NOT IN ('0', '0000000000000000')
AND deleted_flag = '0'
ORDER BY effective_usage_mask;
SELECT rule_id,
effective_usage_mask
BULK
COLLECT
INTO v_rules,
v_masks_rules
FROM cz_rules
WHERE effective_usage_mask NOT IN ('0', '0000000000000000')
AND deleted_flag = '0'
ORDER BY effective_usage_mask;
END is_usage_deleteable;
PROCEDURE DELETE_USAGE(usageId IN NUMBER, delete_status IN OUT NOCOPY VARCHAR2)
AS
TYPE t_indexes IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_nodes.DELETE;
v_rules.DELETE;
v_masks_nodes.DELETE;
v_masks_rules.DELETE;
SELECT ps_node_id,
effective_usage_mask
BULK
COLLECT
INTO v_nodes,
v_masks_nodes
FROM cz_ps_nodes
WHERE effective_usage_mask NOT IN ('0', '0000000000000000')
ORDER BY effective_usage_mask;
SELECT rule_id,
effective_usage_mask
BULK
COLLECT
INTO v_rules,
v_masks_rules
FROM cz_rules
WHERE effective_usage_mask NOT IN ('0', '0000000000000000')
ORDER BY effective_usage_mask;
UPDATE cz_model_usages
SET in_use = 'X',
name = append_name(usageId, 'USG', name)
WHERE model_usage_id = usageId;
DELETE FROM cz_publication_usages
WHERE usage_id = usageId;
DELETE FROM cz_rp_entries
WHERE object_type ='USG' and object_id = usageId;
UPDATE cz_ps_nodes
SET effective_usage_mask = v_masks_nodes(i)
WHERE ps_node_id = v_nodes(i);
UPDATE cz_rules
SET effective_usage_mask = v_masks_rules(i)
WHERE rule_id = v_rules(i);
delete_status := '-1';
delete_status := '0';
END DELETE_USAGE;
PROCEDURE delete_usage (p_usage_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
l_return_status VARCHAR2(1);
is_usage_deleteable(p_usage_id,l_return_status,l_msg_count,l_msg_data);
delete_usage(p_usage_id,l_usg_status);
handle_Error(p_procedure_name => 'delete_usage',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END delete_usage;
PROCEDURE is_repos_fld_deleteable ( p_rp_folder_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
TYPE number_type_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
SELECT object_id
INTO l_rp_folder
FROM cz_rp_entries
WHERE cz_rp_entries.object_id = p_rp_folder_id
AND cz_rp_entries.object_type = 'FLD'
AND cz_rp_entries.deleted_flag = '0';
l_rp_fld_tbl.DELETE;
l_encl_idx_ref.DELETE;
l_encl_fld_tbl.DELETE;
SELECT object_id,enclosing_folder
BULK
COLLECT
INTO l_rp_fld_tbl,l_encl_fld_tbl
FROM cz_rp_entries
WHERE cz_rp_entries.deleted_flag = '0'
AND cz_rp_entries.object_type = 'FLD'
START WITH cz_rp_entries.object_type = 'FLD'
AND cz_rp_entries.object_id = l_rp_folder
CONNECT BY PRIOR cz_rp_entries.object_id = cz_rp_entries.enclosing_folder
AND PRIOR cz_rp_entries.object_type = 'FLD';
l_rp_model_tbl.DELETE;
SELECT object_id, checkout_user, cz_devl_projects.name
BULK COLLECT
INTO l_rp_model_tbl, l_checkout_user_tbl, l_devl_prj_name_tbl
FROM cz_rp_entries, cz_devl_projects
WHERE cz_rp_entries.object_type = 'PRJ'
AND cz_rp_entries.deleted_flag = '0'
AND cz_rp_entries.enclosing_folder = l_rp_fld_tbl(i)
AND cz_rp_entries.object_id = cz_devl_projects.devl_project_id;
l_eff_set_tbl.DELETE;
SELECT object_id
BULK COLLECT
INTO l_eff_set_tbl
FROM cz_rp_entries
WHERE cz_rp_entries.object_type = 'EFF'
AND cz_rp_entries.deleted_flag = '0'
AND cz_rp_entries.enclosing_folder = l_rp_fld_tbl(i);
l_usg_tbl.DELETE;
SELECT object_id
BULK COLLECT
INTO l_usg_tbl
FROM cz_rp_entries
WHERE cz_rp_entries.object_type = 'USG'
AND cz_rp_entries.deleted_flag = '0'
AND cz_rp_entries.enclosing_folder = l_rp_fld_tbl(i);
l_arc_tbl.DELETE;
SELECT object_id
BULK COLLECT
INTO l_arc_tbl
FROM cz_rp_entries
WHERE cz_rp_entries.object_type = 'ARC'
AND cz_rp_entries.deleted_flag = '0'
AND cz_rp_entries.seeded_flag <> '1'
AND cz_rp_entries.enclosing_folder = l_rp_fld_tbl(i);
l_prp_tbl.DELETE;
SELECT object_id
BULK COLLECT
INTO l_prp_tbl
FROM cz_rp_entries
WHERE cz_rp_entries.object_type = 'PRP'
AND cz_rp_entries.deleted_flag = '0'
AND cz_rp_entries.seeded_flag <> '1'
AND cz_rp_entries.enclosing_folder = l_rp_fld_tbl(i);
l_umt_tbl.DELETE;
SELECT object_id
BULK COLLECT
INTO l_umt_tbl
FROM cz_rp_entries
WHERE cz_rp_entries.object_type = 'UMT'
AND cz_rp_entries.deleted_flag = '0'
AND cz_rp_entries.seeded_flag <> '1'
AND cz_rp_entries.enclosing_folder = l_rp_fld_tbl(i);
l_uct_tbl.DELETE;
SELECT object_id, checkout_user, template_name
BULK COLLECT
INTO l_uct_tbl, l_checkout_user_tbl, l_template_name_tbl
FROM cz_rp_entries, cz_ui_templates
WHERE cz_rp_entries.object_type = 'UCT'
AND cz_rp_entries.deleted_flag = '0'
AND cz_rp_entries.seeded_flag <> '1'
AND cz_rp_entries.enclosing_folder = l_rp_fld_tbl(i)
AND cz_rp_entries.object_id = cz_ui_templates.template_id
AND cz_ui_templates.ui_def_id = 0;
l_ref_model_ids_tbl.DELETE;
l_encl_devl_tbl.DELETE;
l_ref_model_names_tbl.DELETE;
SELECT d.devl_project_id,d.name, rp.enclosing_folder
BULK
COLLECT
INTO l_ref_model_ids_tbl,l_ref_model_names_tbl,l_encl_devl_tbl
FROM cz_ps_nodes p,
cz_devl_projects d,
cz_rp_entries rp
WHERE p.reference_id = l_rp_model_ref(modelId)
AND p.ps_node_type IN (263, 264)
AND p.deleted_flag = '0'
AND p.devl_project_id = d.devl_project_id
AND rp.object_id = d.devl_project_id
AND rp.object_type = 'PRJ'
AND d.deleted_flag = '0';
SELECT name into l_model_name from cz_devl_projects
WHERE devl_project_id = l_ref_model_ids_tbl(i);
SELECT name into l_ref_model_name from cz_devl_projects
WHERE devl_project_id = l_rp_model_ref(modelId);
l_publication_tbl.DELETE;
SELECT publication_id
BULK
COLLECT
INTO l_publication_tbl
FROM cz_model_publications
WHERE object_id = l_ref_model_ids_tbl(i)
AND object_type = 'PRJ'
AND deleted_flag = '0';
SELECT name into l_model_name from cz_devl_projects
WHERE devl_project_id = l_ref_model_ids_tbl(i);
SELECT object_id,name
INTO l_eff_set_id,l_eff_name
FROM cz_rp_entries
WHERE cz_rp_entries.object_id = l_eff_set_ref(i)
AND cz_rp_entries.object_type = 'EFF'
AND cz_rp_entries.deleted_flag = '0'
AND cz_rp_entries.seeded_flag <> '1';
l_eff_tbl.DELETE;
SELECT distinct devl_project_id
BULK
COLLECT
INTO l_eff_tbl
FROM cz_rules
WHERE cz_rules.effectivity_set_id = l_eff_set_ref(i)
AND cz_rules.deleted_flag = '0';
l_eff_tbl.DELETE;
SELECT distinct devl_project_id
BULK
COLLECT
INTO l_eff_tbl
FROM cz_ps_nodes
WHERE cz_ps_nodes.effectivity_set_id = l_eff_set_ref(i)
AND cz_ps_nodes.deleted_flag = '0';
SELECT enclosing_folder,name
INTO l_encl_eff_fld,l_model_name
from cz_rp_entries
WHERE object_id = l_eff_ref(i)
AND object_type = 'PRJ'
AND deleted_flag = '0';
SELECT object_id,name,seeded_flag,enclosing_folder
INTO l_object_id,l_object_name,l_seeded_flag,l_encl_arc_fld
FROM cz_rp_entries
WHERE cz_rp_entries.object_id = l_arc_ref(modelId)
AND cz_rp_entries.object_type = 'ARC'
AND cz_rp_entries.deleted_flag = '0';
l_devl_project_tbl.DELETE;
SELECT DISTINCT devl_project_id
BULK
COLLECT
INTO l_devl_project_tbl
FROM cz_archive_refs
WHERE cz_archive_refs.archive_id = l_arc_ref(modelId)
AND cz_archive_refs.deleted_flag = '0'
AND cz_archive_refs.devl_project_id IN (SELECT object_id
FROM cz_rp_entries
WHERE object_type = 'PRJ'
AND deleted_flag = '0');
SELECT name,enclosing_folder
into l_devl_name,l_encl_arc_fld
FROM cz_rp_entries
WHERE object_id = l_devl_project_tbl(i)
AND object_type = 'PRJ';
SELECT object_id,name,seeded_flag
INTO l_object_id,l_object_name,l_seeded_flag
FROM cz_rp_entries
WHERE cz_rp_entries.object_id = l_prp_ref(modelId)
AND cz_rp_entries.object_type = 'PRP'
AND cz_rp_entries.deleted_flag = '0';
SELECT devl_project_id BULK COLLECT INTO l_devl_project_tbl
FROM (SELECT devl_project_id
FROM cz_ps_prop_vals psp,
cz_ps_nodes ps
WHERE psp.deleted_flag = '0'
AND ps.deleted_flag = '0'
AND ps.ps_node_id = psp.ps_node_id
AND property_id = l_prp_ref(modelId)
UNION
SELECT devl_project_id
FROM cz_item_type_properties itypr,
cz_item_masters itm,
cz_ps_nodes psnd
WHERE itypr.deleted_flag = '0'
AND itypr.item_type_id = itm.item_type_id
AND itm.deleted_flag = '0'
AND psnd.item_id = itm.item_id
AND psnd.deleted_flag = '0'
AND itypr.property_id = l_prp_ref(modelId));
SELECT enclosing_folder into l_encl_arc_fld
FROM cz_rp_entries WHERE object_id = l_devl_project_tbl(j)
AND object_type = 'PRJ';
SELECT 1
INTO l_item_prop_count
FROM CZ_ITEM_TYPE_PROPERTIES
WHERE item_type_id IN (select item_type_id
from cz_item_types
where deleted_flag = '0')
AND property_id = l_prp_ref(modelId)
AND deleted_flag = '0';
SELECT 1
INTO l_ui_pages_count
FROM CZ_UI_PAGES
WHERE property_id = l_prp_ref(modelId)
AND deleted_flag = '0';
SELECT object_id,name,seeded_flag
INTO l_object_id,l_object_name,l_seeded_flag
FROM cz_rp_entries
WHERE cz_rp_entries.object_id = l_umt_ref(modelId)
AND cz_rp_entries.object_type = 'UMT'
AND cz_rp_entries.deleted_flag = '0';
l_ui_name_tbl.DELETE;
SELECT name
BULK
COLLECT
INTO l_ui_name_tbl
FROM cz_ui_defs
WHERE cz_ui_defs.from_master_template_id = l_umt_ref(modelId)
AND cz_ui_defs.deleted_flag = '0'
AND cz_ui_defs.seeded_flag <> '1'
--Additional Checks added. Must be a active model
AND EXISTS (SELECT 1 FROM cz_rp_entries WHERE deleted_flag = '0'
AND object_id = cz_ui_defs.devl_project_id AND object_type = 'PRJ');
SELECT object_id,name,seeded_flag
INTO l_object_id,l_object_name,l_seeded_flag
FROM cz_rp_entries
WHERE cz_rp_entries.object_id = l_uct_ref(modelId)
AND cz_rp_entries.object_type = 'UCT'
AND cz_rp_entries.deleted_flag = '0';
l_ui_name_tbl.DELETE;
SELECT name
BULK
COLLECT
INTO l_ui_name_tbl
FROM cz_ui_defs
WHERE cz_ui_defs.ui_def_id IN (SELECT ui_def_id
FROM cz_ui_cont_type_templs
WHERE cz_ui_cont_type_templs.template_id = l_uct_ref(modelId)
AND cz_ui_cont_type_templs.deleted_flag = '0'
AND cz_ui_cont_type_templs.seeded_flag <> '1'
UNION ALL --Bug#13770170: Added Missing Check for cz_ui_page_elements
SELECT ui_def_id
FROM cz_ui_page_elements
WHERE cz_ui_page_elements.ctrl_template_id = l_uct_ref(modelId)
AND cz_ui_page_elements.ctrl_template_ui_def_id = 0
AND cz_ui_page_elements.deleted_flag = '0')
AND EXISTS (SELECT 1 FROM cz_rp_entries WHERE deleted_flag = '0'
AND object_id = cz_ui_defs.devl_project_id AND object_type = 'PRJ')
AND cz_ui_defs.deleted_flag = '0';
SELECT template_id, template_ui_def_id
BULK COLLECT INTO l_ref_template_tbl, l_ref_templ_ui_tbl
FROM cz_ui_ref_templates
WHERE cz_ui_ref_templates.deleted_flag = '0'
AND cz_ui_ref_templates.ref_template_id = l_uct_ref(modelId)
AND cz_ui_ref_templates.ref_template_ui_def_id = 0;
SELECT template_name INTO l_ref_template_name
FROM cz_ui_templates
WHERE cz_ui_templates.template_id = l_ref_template_tbl(i)
AND cz_ui_templates.ui_def_id = l_ref_templ_ui_tbl(i)
AND cz_ui_templates.deleted_flag = '0';
SELECT model_usage_id,name
INTO l_usage_count,l_usage_name
FROM cz_model_usages
WHERE cz_model_usages.model_usage_id = l_usg_ref(modelId)
AND cz_model_usages.in_use <> 'X';
SELECT 1
INTO l_usage_count
FROM cz_publication_usages
WHERE cz_publication_usages.usage_id = l_usg_ref(modelId)
AND cz_publication_usages.publication_id IN (SELECT publication_id
FROM cz_model_publications
WHERE cz_model_publications.deleted_flag = '0')
AND rownum < 2;
v_nodes.DELETE;
v_rules.DELETE;
v_masks_nodes.DELETE;
v_masks_rules.DELETE;
l_nodes_name_tbl.DELETE;
l_rules_name_tbl.DELETE;
SELECT ps_node_id,effective_usage_mask,devl_project_id,name
BULK
COLLECT
INTO v_nodes,v_masks_nodes,l_nodes_project_tbl,l_nodes_name_tbl
FROM cz_ps_nodes
WHERE effective_usage_mask NOT IN ('0', '0000000000000000')
ORDER BY effective_usage_mask;
SELECT rule_id,effective_usage_mask,devl_project_id,name
BULK
COLLECT
INTO v_rules,v_masks_rules,l_rules_project_tbl,l_rules_name_tbl
FROM cz_rules
WHERE effective_usage_mask NOT IN ('0', '0000000000000000')
ORDER BY effective_usage_mask;
SELECT enclosing_folder,name into l_encl_arc_fld,l_model_name
FROM cz_rp_entries WHERE object_id = l_nodes_project_tbl(i)
AND object_type = 'PRJ';
SELECT enclosing_folder,name into l_encl_arc_fld,l_model_name
FROM cz_rp_entries WHERE object_id = l_nodes_project_tbl(i)
AND object_type = 'PRJ';
handle_Error( p_procedure_name => 'is_repos_fld_deleteable',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END is_repos_fld_deleteable;
PROCEDURE delete_repository_folder (p_rp_folder_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
TYPE number_type_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_rp_fld_tbl.DELETE;
SELECT object_id
BULK
COLLECT
INTO l_rp_fld_tbl
FROM cz_rp_entries
WHERE cz_rp_entries.deleted_flag = '0'
AND cz_rp_entries.object_type = 'FLD'
START WITH cz_rp_entries.object_type = 'FLD'
AND cz_rp_entries.object_id = p_rp_folder_id
CONNECT BY PRIOR cz_rp_entries.object_id = cz_rp_entries.enclosing_folder
AND PRIOR cz_rp_entries.object_type = 'FLD';
is_repos_fld_deleteable (p_rp_folder_id,x_return_status,x_msg_count,x_msg_data);
l_object_typ_tbl.DELETE;
l_object_id_tbl.DELETE;
SELECT object_id, object_type
BULK
COLLECT
INTO l_object_id_tbl, l_object_typ_tbl
FROM cz_rp_entries
WHERE object_type <> 'FLD'
AND enclosing_folder = l_rp_fld_tbl(i)
AND deleted_flag = '0';
delete_model(l_object_id_tbl(i), x_return_status, x_msg_count ,x_msg_data);
delete_uct(l_object_id_tbl(i),x_return_status,x_msg_count,x_msg_data);
delete_umt(l_object_id_tbl(i),x_return_status,x_msg_count,x_msg_data);
delete_usage(l_object_id_tbl(i),x_return_status,x_msg_count,x_msg_data);
delete_archive(l_object_id_tbl(i),x_return_status,x_msg_count,x_msg_data);
delete_eff_set(l_object_id_tbl(i),x_return_status,x_msg_count,x_msg_data);
delete_property(l_object_id_tbl(i),x_return_status,x_msg_count,x_msg_data);
UPDATE cz_rp_entries
SET deleted_flag = '1',
name = append_name (l_rp_fld_tbl(l_count), 'FLD', name)
WHERE object_type = 'FLD'
AND object_id = l_rp_fld_tbl(l_count);
handle_Error(p_procedure_name => 'delete_repository_folder',
p_error_message => SQLERRM,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END delete_repository_folder;
SELECT model_usage_id
FROM cz_model_usages
WHERE in_use = 'X'
AND model_usage_id < 64
ORDER BY model_usage_id;
UPDATE cz_model_usages SET
in_use = '1', name = 'New Usage ' || try_id
WHERE model_usage_id = try_id AND in_use = 'X';
INSERT INTO cz_rp_entries(object_id, object_type, enclosing_folder, name)
VALUES (usageId, 'USG',enclosingFolderId, 'New Usage ' || try_id);
SELECT name
INTO original_name
FROM cz_rp_entries
WHERE object_id = p_object_id
AND object_type = p_object_type
AND deleted_flag = '0';
SELECT nvl(max(to_number(substr(SUBSTR(name, 1, instr(name,original_name, -1, 1)-1),7,instr(SUBSTR(name, 1, instr(name,original_name, -1, 1)-1),')',7)-7))),0)
INTO L
FROM cz_rp_entries
WHERE name LIKE 'Copy (%) of '||original_name
and instr(SUBSTR(name, 1, instr(name,original_name, -1, 1)-1),'Copy (',7)=0
and is_val_number(substr(SUBSTR(name, 1, instr(name,original_name, -1, 1)-1),7,instr(SUBSTR(name, 1, instr(name,original_name, -1, 1)-1),')',7)-7))='TRUE'
AND deleted_flag = '0' and object_type=p_object_type;
FOR c_name IN (SELECT name, parent_id FROM cz_ps_nodes
START WITH ps_node_id = p_ps_node_id
CONNECT BY PRIOR parent_id = ps_node_id) LOOP
IF(v_Name IS NULL)THEN
v_Name := '''' || REPLACE(c_name.name, '''', '\''') || '''';
FOR c_node IN (SELECT NULL FROM cz_ps_nodes WHERE deleted_flag = '0'
AND devl_project_id = v_devl_project_id
AND name = c_name.name
AND ps_node_id <> p_ps_node_id)LOOP
h_FullName(p_ps_node_id) := 1;
FOR c_node IN (SELECT NULL FROM cz_ps_nodes WHERE deleted_flag = '0'
AND devl_project_id IN
(SELECT component_id FROM cz_model_ref_expls
WHERE deleted_flag = '0'
AND model_id = v_devl_project_id
AND ps_node_type IN (PS_NODE_TYPE_REFERENCE, PS_NODE_TYPE_CONNECTOR))
AND name = c_name.name)LOOP
h_FullName(p_ps_node_id) := 1;
SELECT name INTO v_ModelName FROM cz_ps_nodes
WHERE ps_node_id = h_ComponentId(v_Node);
SELECT name INTO v_Name FROM cz_properties
WHERE property_id = v_ExprPropertyId(j);
SELECT model_ref_expl_id, expr_type, expr_node_id, expr_parent_id, template_id,
ps_node_id, data_value, property_id, argument_index,
argument_name, data_num_value, data_type
BULK COLLECT INTO v_tTmplNodeId, v_tTmplType, v_tTmplId, v_tTmplParentId, v_tTmplTemplateId,
v_tTmplPsNodeId, v_tTmplDataValue, v_tTmplPropertyId,
v_tTmplArgumentIndex, v_tTmplArgumentName, v_tTmplDataNumValue, v_tTmplDataType
FROM cz_expression_nodes
WHERE rule_id = p_template_id
AND expr_type <> EXPR_PUNCT
AND deleted_flag = '0'
ORDER BY expr_parent_id, seq_nbr;
v_ExprParentId.DELETE(-1);
v_ExplNodeId.DELETE(-1);
v_ExprType.DELETE(-1);
v_ExprTemplateId.DELETE(-1);
v_ExprPsNodeId.DELETE(-1);
v_ExprDataType.DELETE(-1);
v_ExprDataValue.DELETE(-1);
v_ExprDataNumValue.DELETE(-1);
v_ExprPropertyId.DELETE(-1);
v_ExprArgumentName.DELETE(-1);
v_ExprParamIndex.DELETE(-1);
v_ExprId.DELETE(-1);
v_NumberOfChildren.DELETE;
v_ChildrenIndex.DELETE;
SELECT rule_id, name, template_token BULK COLLECT INTO h_RuleId, v_RuleName, v_TemplateToken
FROM cz_rules
WHERE devl_project_id = 0
AND deleted_flag = '0'
AND disabled_flag = '0'
AND seeded_flag = '1';
SELECT value INTO StoreNlsCharacters FROM NLS_SESSION_PARAMETERS
WHERE UPPER(parameter) = 'NLS_NUMERIC_CHARACTERS';
SELECT devl_project_id, presentation_flag INTO v_devl_project_id, v_template_flag FROM cz_rules
WHERE deleted_flag = '0'
AND rule_id = p_rule_id;
SELECT model_ref_expl_id, parent_expl_node_id, component_id, referring_node_id, ps_node_type
BULK COLLECT INTO v_NodeId, v_ParentId, v_ComponentId, v_ReferringId, v_NodeType
FROM cz_model_ref_expls
WHERE model_id = v_devl_project_id
AND deleted_flag = '0';
SELECT expr_node_id, expr_parent_id, expr_type, template_id,
ps_node_id, model_ref_expl_id, property_id, data_type, data_value, data_num_value,
param_index, argument_name
BULK COLLECT INTO v_ExprId, v_ExprParentId, v_ExprType, v_ExprTemplateId,
v_ExprPsNodeId, v_ExplNodeId, v_ExprPropertyId, v_ExprDataType, v_ExprDataValue, v_ExprDataNumValue,
v_ExprParamIndex, v_ExprArgumentName
FROM cz_expression_nodes
WHERE rule_id = p_rule_id
AND expr_type <> EXPR_PUNCT
AND deleted_flag = '0'
ORDER BY expr_parent_id, seq_nbr;
SELECT ps_node_id, parent_id, persistent_node_id, virtual_flag
BULK COLLECT INTO v_start_node_id_tab, v_start_parent_id_tab, v_start_persist_id_tab, v_start_virtual_tab
FROM cz_ps_nodes
WHERE deleted_flag = '0'
START WITH ps_node_id = p_start_node_id
CONNECT BY PRIOR parent_id = ps_node_id;
SELECT ps_node_id, parent_id, persistent_node_id, virtual_flag
BULK COLLECT INTO v_end_node_id_tab, v_end_parent_id_tab, v_end_persist_id_tab, v_end_virtual_tab
FROM cz_ps_nodes
WHERE deleted_flag = '0'
START WITH ps_node_id = p_end_node_id
CONNECT BY PRIOR parent_id = ps_node_id;
SELECT model_id,component_id,referring_node_id
INTO l_root_model_id, l_component_id, l_referring_node_id
FROM CZ_MODEL_REF_EXPLS WHERE model_ref_expl_id=p_node_expl_id;
SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
WHERE ps_node_id=l_component_id;
SELECT ps_node_id INTO l_ps_node_id FROM CZ_PS_NODES
WHERE devl_project_id=l_model_id AND
persistent_node_id=p_node_persistent_node_id AND
deleted_flag='0';
SELECT persistent_node_id INTO l_persistent_node_id FROM CZ_PS_NODES
WHERE ps_node_id=l_referring_node_id;
SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
WHERE ps_node_id=l_referring_node_id;
SELECT devl_project_id INTO l_model_id FROM CZ_PS_NODES
WHERE ps_node_id=l_component_id;
SELECT ps_node_id INTO l_ps_node_id FROM CZ_PS_NODES
WHERE devl_project_id=l_model_id AND
persistent_node_id=p_node_persistent_node_id AND
deleted_flag='0';
SELECT model_ref_expl_id, expl_node_type
BULK COLLECT INTO v_base_expl_id_tab, v_base_expl_type_tab
FROM cz_model_ref_expls
WHERE deleted_flag = '0'
START WITH model_ref_expl_id = p_base_expl_id
CONNECT BY PRIOR parent_expl_node_id = model_ref_expl_id;
SELECT model_ref_expl_id, expl_node_type
BULK COLLECT INTO v_node_expl_id_tab, v_node_expl_type_tab
FROM cz_model_ref_expls
WHERE deleted_flag = '0'
START WITH model_ref_expl_id = p_node_expl_id
CONNECT BY PRIOR parent_expl_node_id = model_ref_expl_id;
SELECT component_id, referring_node_id
INTO l_component_id, l_referring_node_id
FROM cz_model_ref_expls WHERE model_ref_expl_id = p_base_expl_id;
SELECT component_id, referring_node_id
INTO l_component_id, l_referring_node_id
FROM cz_model_ref_expls WHERE model_ref_expl_id = p_node_expl_id;
SELECT model_ref_expl_id, expl_node_type
BULK COLLECT INTO v_base_expl_id_tab, v_base_expl_type_tab
FROM cz_model_ref_expls
WHERE deleted_flag = '0'
START WITH model_ref_expl_id = p_base_expl_id
CONNECT BY PRIOR parent_expl_node_id = model_ref_expl_id;
SELECT model_ref_expl_id, expl_node_type
BULK COLLECT INTO v_node_expl_id_tab, v_node_expl_type_tab
FROM cz_model_ref_expls
WHERE deleted_flag = '0'
START WITH model_ref_expl_id = p_node_expl_id
CONNECT BY PRIOR parent_expl_node_id = model_ref_expl_id;
SELECT devl_project_id INTO v_base_project_id FROM cz_ps_nodes
WHERE ps_node_id = p_base_node_id;
SELECT devl_project_id INTO v_node_project_id FROM cz_ps_nodes
WHERE ps_node_id = p_node_node_id;
SELECT model_ref_expl_id, referring_node_id, ps_node_type
BULK COLLECT INTO v_base_expl_id_tab, v_base_ref_id_tab, v_base_expl_type_tab
FROM cz_model_ref_expls
WHERE deleted_flag = '0'
START WITH model_ref_expl_id = p_base_expl_id
CONNECT BY PRIOR parent_expl_node_id = model_ref_expl_id;
SELECT model_ref_expl_id, referring_node_id, ps_node_type
BULK COLLECT INTO v_node_expl_id_tab, v_node_ref_id_tab, v_node_expl_type_tab
FROM cz_model_ref_expls
WHERE deleted_flag = '0'
START WITH model_ref_expl_id = p_node_expl_id
CONNECT BY PRIOR parent_expl_node_id = model_ref_expl_id;
EVT_POSTDELETE_NAME CONSTANT VARCHAR2(30) := 'POSTINSTANCEDELETE';
INSERT INTO cz_db_logs (message, statuscode, caller, urgency, run_id, logtime)
VALUES (inMessage, nDebug, 'verify_special_rule', inUrgency, x_run_id, SYSDATE);
UPDATE cz_rules SET invalid_flag = '1' WHERE rule_id = p_rule_id;
UPDATE cz_rules SET invalid_flag = '0' WHERE rule_id = p_rule_id;
FOR folder IN (SELECT name FROM cz_rule_folders
WHERE deleted_flag = '0'
AND parent_rule_folder_id IS NOT NULL
START WITH rule_folder_id = v_rule_folder_id
AND object_type = 'RFL'
CONNECT BY PRIOR parent_rule_folder_id = rule_folder_id
AND object_type = 'RFL')LOOP
IF(LENGTHB(folder.name) + LENGTHB(v_qualified) + 1 < 2000 - n_rule_name)THEN
v_qualified := '.' || folder.name || v_qualified;
SELECT persistent_node_id, name, virtual_flag
INTO x_persistent_id, x_name, x_virtual_flag
FROM cz_ps_nodes
WHERE deleted_flag = '0' AND ps_node_id = p_node_id;
SELECT NULL INTO aux_flag FROM cz_model_ref_expls
WHERE deleted_flag = '0' AND model_ref_expl_id = p_expl_id
AND model_id = v_model_id;
SELECT NULL INTO v_null FROM cz_conversion_rels_v rel, cz_cx_event_params_v par
WHERE par.event_signature_id = p_signature_id
AND par.argument_index = p_argument_index
AND rel.subject_type = par.data_type
AND rel.object_type = p_data_type AND par.p_model_id = v_model_id;
SELECT NULL INTO v_null FROM cz_conversion_rels_v rel, cz_cx_system_params_v par
WHERE par.data_value = p_argument_name
AND rel.subject_type = par.data_type
AND rel.object_type = p_data_type;
SELECT cz_xfr_run_infos_s.NEXTVAL INTO x_run_id FROM DUAL;
SELECT rule_type, component_id, model_ref_expl_id, devl_project_id, rule_folder_id, name,
class_name, seeded_flag, invalid_flag, instantiation_scope
INTO v_rule_type, v_component_id, v_expl_id, v_model_id, v_rule_folder_id, v_rule_name,
v_class_name, v_seeded_flag, initRuleStatus, v_inst_scope
FROM cz_rules
WHERE rule_id = p_rule_id;
SELECT name INTO v_model_name
FROM cz_devl_projects
WHERE devl_project_id = v_model_id;
SELECT expr_node_id, expr_parent_id, model_ref_expl_id, expr_type, expr_subtype, ps_node_id,
argument_signature_id, param_signature_id, param_index, data_value,
data_num_value, argument_name, data_type, argument_index, property_id, relative_node_path
BULK COLLECT INTO v_tExprId, v_tExprParentId, v_tExplNodeId, v_tExprType, v_tExprSubtype, v_tExprPsNodeId,
v_tExprArgSignature, v_tExprParSignature, v_tExprParamIndex, v_tExprDataValue,
v_tExprDataNumValue, v_tExprArgumentName, v_tExprDataType, v_tExprArgumentIndex,
v_tPropertyId, v_ExistingRelativePath
FROM cz_expression_nodes
WHERE rule_id = p_rule_id
AND expr_type <> EXPR_NODE_TYPE_PUNCT
AND deleted_flag = '0';
SELECT name INTO v_signature_name FROM cz_signatures
WHERE deleted_flag = '0' AND signature_id = v_tExprArgSignature(i);
SELECT argument_count INTO v_arg_count FROM cz_signatures
WHERE deleted_flag = '0' AND signature_id = v_tExprParSignature(i);
v_tArgumentIndex.DELETE;
v_tDataType.DELETE;
v_tArgumentName.DELETE;
v_tJavaDataType.DELETE;
SELECT argument_index, data_type, argument_name, java_data_type
BULK COLLECT INTO v_tArgumentIndex, v_tDataType, v_tArgumentName, v_tJavaDataType
FROM cz_signature_arguments
WHERE deleted_flag = '0'
AND argument_signature_id = v_tExprParSignature(i);
SELECT NVL(property_value, property_num_value) INTO v_property_value
FROM cz_psnode_propval_v
WHERE ps_node_id = v_tExprPsNodeId(ii)
AND property_id = v_tPropertyId(i);
SELECT name INTO v_property_name
FROM cz_properties
WHERE deleted_flag = '0'
AND property_id = v_tPropertyId(i);
SELECT parent_id, name INTO v_parent_id, v_item_name
FROM cz_ps_nodes
WHERE deleted_flag = '0'
AND ps_node_id = v_tExprPsNodeId(ii);
SELECT name INTO v_parent_name
FROM cz_ps_nodes
WHERE deleted_flag = '0'
AND ps_node_id = v_parent_id;
UPDATE cz_expression_nodes SET relative_node_path = v_RelativeNodePath(i)
WHERE expr_node_id = v_tExprId(i);
SELECT ps_node_id, parent_id, persistent_node_id
BULK COLLECT INTO v_start_node_id_tab, v_start_parent_id_tab, v_start_persist_id_tab
FROM cz_ps_nodes
WHERE deleted_flag = '0'
START WITH ps_node_id = p_start_node_id
CONNECT BY PRIOR parent_id = ps_node_id;
SELECT ps_node_id, parent_id, persistent_node_id
BULK COLLECT INTO v_end_node_id_tab, v_end_parent_id_tab, v_end_persist_id_tab
FROM cz_ps_nodes
WHERE deleted_flag = '0'
START WITH ps_node_id = p_end_node_id
CONNECT BY PRIOR parent_id = ps_node_id;
SELECT model_ref_expl_id, referring_node_id, ps_node_type, component_id
BULK COLLECT INTO v_base_expl_id_tab, v_base_ref_id_tab, v_base_expl_type_tab, v_base_component_id_tab
FROM cz_model_ref_expls
WHERE deleted_flag = '0'
START WITH model_ref_expl_id = p_base_expl_id
CONNECT BY PRIOR parent_expl_node_id = model_ref_expl_id;
SELECT model_ref_expl_id, referring_node_id, ps_node_type, component_id
BULK COLLECT INTO v_node_expl_id_tab, v_node_ref_id_tab, v_node_expl_type_tab, v_node_component_id_tab
FROM cz_model_ref_expls
WHERE deleted_flag = '0'
START WITH model_ref_expl_id = p_node_expl_id
CONNECT BY PRIOR parent_expl_node_id = model_ref_expl_id;
SELECT ps_node_type INTO v_base_node_type
FROM cz_ps_nodes
WHERE deleted_flag = '0'
AND persistent_node_id = p_base_pers_id
AND ROWNUM = 1;
SELECT ps_node_type INTO v_node_node_type
FROM cz_ps_nodes
WHERE deleted_flag = '0'
AND persistent_node_id = p_node_pers_id
AND ROWNUM = 1;
SELECT devl_project_id INTO v_base_devl_project_id FROM cz_ps_nodes
WHERE deleted_flag = '0' AND ps_node_id = p_base_node_id;
SELECT ps_node_id, devl_project_id INTO p_base_node_id, v_base_devl_project_id FROM cz_ps_nodes
WHERE deleted_flag = '0'
AND persistent_node_id = p_base_pers_id
AND devl_project_id =
(SELECT devl_project_id FROM cz_ps_nodes WHERE ps_node_id = v_base_component_id_tab(v_base_ref_expl_index));
SELECT devl_project_id INTO v_node_devl_project_id FROM cz_ps_nodes
WHERE deleted_flag = '0' AND ps_node_id = p_node_node_id;
SELECT ps_node_id, devl_project_id INTO p_node_node_id, v_node_devl_project_id FROM cz_ps_nodes
WHERE deleted_flag = '0'
AND persistent_node_id = p_node_pers_id
AND devl_project_id =
(SELECT devl_project_id FROM cz_ps_nodes WHERE ps_node_id = v_node_component_id_tab(v_node_ref_expl_index));
l_deleted_flag VARCHAR2(1);
SRCRULE_IS_DELETED EXCEPTION;
SELECT rule_folder_id,object_type,deleted_flag,parent_rule_folder_id,devl_project_id
INTO l_src_rule_id,l_src_object_type,l_deleted_flag,l_parent_rule_folder_id,l_src_devl_project_id
FROM cz_rule_folders
WHERE cz_rule_folders.rule_folder_id = p_src_rule_id
AND cz_rule_folders.object_type = p_src_rule_type;
IF (l_deleted_flag = '1') THEN
RAISE SRCRULE_IS_DELETED;
SELECT rule_folder_id,parent_rule_folder_id,devl_project_id,object_type
INTO l_tgt_rule_fld_id,l_tgt_parent_fld_id,l_tgt_devl_project_id,l_tgt_object_type
FROM cz_rule_folders
WHERE cz_rule_folders.rule_folder_id = p_tgt_rule_fld_id
AND cz_rule_folders.object_type IN ('RFL','RSQ')
AND cz_rule_folders.deleted_flag = '0';
l_rule_fld_tbl.DELETE;
SELECT rule_folder_id
BULK
COLLECT
INTO l_rule_fld_tbl
FROM cz_rule_folders
WHERE cz_rule_folders.object_type = 'RFL'
AND cz_rule_folders.deleted_flag = '0'
START WITH cz_rule_folders.rule_folder_id = p_src_rule_id
CONNECT BY PRIOR rule_folder_id = parent_rule_folder_id
AND PRIOR object_type = 'RFL'
AND PRIOR deleted_flag = '0';
WHEN SRCRULE_IS_DELETED THEN
x_return_status := FND_API.G_RET_STS_ERROR;
FND_MESSAGE.SET_NAME('CZ', 'CZ_SRCRULE_IS_DELETED');
SELECT effective_from, effective_until INTO v_eff_from, v_eff_until
FROM cz_effectivity_sets
WHERE deleted_flag = '0'
AND effectivity_set_id = v_eff_set_id;
SELECT effective_parent_id, parent_psnode_expl_id, effective_from, effective_until, effectivity_set_id
INTO v_parent_id, v_expl_id, v_eff_from, v_eff_until, v_eff_set_id
FROM cz_explmodel_nodes_v
WHERE model_id = p_model_id
AND model_ref_expl_id = v_expl_id
AND ps_node_id = v_parent_id;
SELECT parent_id, effective_from, effective_until, effectivity_set_id
INTO v_parent_id, v_eff_from, v_eff_until, v_eff_set_id
FROM cz_ps_nodes
WHERE deleted_flag = '0'
AND ps_node_id = v_parent_id;
SELECT effective_from, effective_until INTO v_eff_from, v_eff_until
FROM cz_effectivity_sets
WHERE deleted_flag = '0'
AND effectivity_set_id = v_eff_set_id;
SELECT referring_node_id, parent_expl_node_id INTO v_parent_id, v_expl_id
FROM cz_model_ref_expls
WHERE deleted_flag = '0'
AND model_ref_expl_id = v_expl_id;
SELECT effective_from, effective_until INTO v_eff_from, v_eff_until
FROM cz_effectivity_sets
WHERE deleted_flag = '0'
AND effectivity_set_id = v_eff_set_id;
SELECT model_ref_expl_id INTO v_model_expl_id FROM cz_model_ref_expls
WHERE deleted_flag = '0'
AND model_id = p_model_id
AND parent_expl_node_id IS NULL;
SELECT description INTO trans_desc FROM cz_model_usages_tl
where model_usage_id=object_id AND language = userenv('LANG');
SELECT config_engine_type
INTO l_config_engine_type
FROM cz_devl_projects
WHERE devl_project_id = p_model_id;
SELECT creation_date
INTO l_last_log_gen_date
FROM cz_lce_headers
WHERE deleted_flag = 0 AND
net_type = 1 AND
component_id = devl_project_id AND devl_project_id = p_model_id;
SELECT MAX(creation_date)
INTO l_last_log_gen_date
FROM cz_fce_files
WHERE deleted_flag = 0 AND
component_id = p_model_id AND
fce_file_type = 1;
SELECT REVERSE(p_class_name) INTO l_reverse_string FROM dual;
SELECT REVERSE(p_pattern) INTO l_reverse_pattern FROM dual;
FOR i IN(SELECT args.argument_signature_id, args.java_data_type, args.data_type, args.argument_index, rul.class_name, rul.rule_id
FROM CZ_RULES rul,
CZ_EXPRESSION_NODES expr,
CZ_SIGNATURE_ARGUMENTS args
WHERE rul.rule_type = 300 AND rul.devl_project_id = p_model_id AND
expr.rule_id = rul.rule_id AND expr.expr_parent_id is null AND
expr.param_signature_id = args.argument_signature_id AND
args.java_data_type like 'oracle.apps.cz.cio.%' AND
args.deleted_flag = '0' AND args.seeded_flag = '0' AND
rul.deleted_flag = '0' AND expr.deleted_flag = '0')
LOOP
UPDATE CZ_SIGNATURE_ARGUMENTS arg
SET java_data_type = REPLACE(i.java_data_type,'oracle.apps.cz.cio.','oracle.apps.cz.cioemu.')
WHERE argument_signature_id=i.argument_signature_id AND
argument_index=i.argument_index AND
EXISTS(SELECT NULL FROM cz_node_type_Classes
WHERE class_name = arg.java_data_type);
UPDATE CZ_RULES
SET class_name=l_new_class_name
WHERE rule_id=i.rule_id;
SELECT model_ref_expl_id, parent_expl_node_id, component_id, referring_node_id, ps_node_type
BULK COLLECT INTO v_NodeId, v_ParentId, v_ComponentId, v_ReferringId, v_NodeType
FROM cz_model_ref_expls
WHERE model_id IN (SELECT component_id FROM cz_model_ref_expls
WHERE model_id = p_devl_project_id AND deleted_flag = '0')
AND deleted_flag = '0';
h_ParentId.DELETE;
h_NodeType.DELETE;
h_ReferringId.DELETE;
h_ComponentId.DELETE;
v_NodeId.DELETE;
v_ParentId.DELETE;
v_ComponentId.DELETE;
v_ReferringId.DELETE;
v_NodeType.DELETE;
h_ParentId.DELETE;
h_NodeType.DELETE;
h_ReferringId.DELETE;
h_ComponentId.DELETE;
* required by SUN (Oracle) to directly insert/update the table.
* p_property_value will be always passed as char and will be populated in
*respective column as per the data_type of the property.
*/
PROCEDURE create_item_property_value
(p_item_id IN NUMBER,
p_property_id IN NUMBER,
p_property_value IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_item_name VARCHAR2(500);
SELECT ref_part_nbr, item_type_id INTO l_item_name, l_item_type_id
FROM cz_item_masters
WHERE item_id = p_item_id
AND deleted_flag = '0';
SELECT name, data_type INTO l_property_name, l_data_type
FROM cz_properties
WHERE property_id = p_property_id
AND data_type IN (1,2,3,4)
AND deleted_flag = '0';
SELECT TO_NUMBER(p_property_value) INTO l_invalid FROM dual;
SELECT item_type_id INTO l_item_type_exists FROM cz_item_type_properties
WHERE item_type_id=l_item_type_id
AND property_id=p_property_id;
INSERT INTO cz_item_type_properties( item_type_id,property_id,deleted_flag,checkout_user)
VALUES(l_item_type_id, p_property_id,'0','API CREATED');
x_msg_data := 'Error in Insert Item Type Property : '||sqlerrm;
SELECT item_id INTO l_data_exists
FROM cz_item_property_values
WHERE item_id = p_item_id
AND property_id = p_property_id;
INSERT INTO cz_item_property_values(property_id, item_id, deleted_flag, checkout_user, property_value, property_num_value)
VALUES(p_property_id, p_item_id, '0','API CREATED', DECODE(l_data_type,4,p_property_value,NULL),
DECODE(l_data_type,1,p_property_value,2,p_property_value,3,p_property_value,NULL));
x_msg_data := 'Error in Insert Item Property Values : '||sqlerrm;
UPDATE cz_item_property_values
SET property_value = DECODE(l_data_type,4,p_property_value, NULL),
property_num_value = DECODE(l_data_type,1,p_property_value,2,p_property_value,3,p_property_value,NULL)
WHERE item_id = p_item_id
AND property_id = p_property_id
AND NVL(src_application_id,0) NOT IN ('702','431');