The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO CZ_DB_LOGS (LOGTIME, LOGUSER, URGENCY, CALLER , MESSAGE, MODEL_ID , OBJECT_TYPE ,OBJECT_ID,MODEL_CONVERSION_SET_ID,ELEMENT_ID,ELEMENT_TYPE)
VALUES(SYSDATE, USER , p_urgency, p_caller, l_msg , p_model_id , p_object_type ,p_object_id,v_model_conversion_set_id,p_element_id,p_element_type);
SELECT rule_folder_id
FROM cz_rule_folders
WHERE deleted_flag ='0'
AND name = 'Rules Generated by Model Conversion'
AND devl_project_id = p_dev_project_id;
select rule_folder_id into p_parent_rule_fldr from cz_rule_folders where devl_project_id=p_dev_project_id and deleted_flag='0' and parent_rule_folder_id is null;
INSERT
INTO cz_rule_folders(rule_folder_id, name, devl_project_id, tree_seq, deleted_flag, folder_type, effective_usage_mask, persistent_rule_folder_id, object_type, disabled_flag , parent_rule_folder_id)
VALUES(cz_rule_folders_s.nextval, 'Rules Generated by Model Conversion', p_dev_project_id, '1', '0', '0', '0000000000000000', cz_rule_folders_s.nextval, 'RFL', '0' , p_parent_rule_fldr)
RETURNING rule_folder_id INTO p_rule_folderid;
INSERT INTO cz_localized_texts (localized_str, intl_text_id, deleted_flag, language, source_lang, model_id, seeded_flag, persistent_intl_text_id)
VALUES (v_cz_rules(v_cz_rules_count).name, v_cz_rules(v_cz_rules_count).reason_id, 0, 'US', 'US', v_cz_rules(v_cz_rules_count).devl_project_id, 0, v_cz_rules(v_cz_rules_count).reason_id);
INSERT INTO cz_rules
VALUES v_cz_rules(v_cz_rules_count);
v_cz_expression_nodes(v_cz_expr_nodes_count).deleted_flag:=0;
INSERT INTO cz_expression_nodes
VALUES v_cz_expression_nodes(v_cz_expr_nodes_count);
v_cz_expression_nodes.DELETE;
v_cz_rules.DELETE;
FOR i IN(SELECT ui_def_id FROM CZ_UI_DEFS
WHERE ui_style='7' AND seeded_flag='0' AND deleted_flag='0' and devl_project_id=p_devl_project_id)
LOOP
FORALL j IN 1..l_cont_types_tbl.COUNT
INSERT INTO CZ_UI_CONT_TYPE_TEMPLS
(
UI_DEF_ID
,CONTENT_TYPE
,TEMPLATE_ID
,MASTER_TEMPLATE_FLAG
,TEMPLATE_UI_DEF_ID
,WRAP_TEMPLATE_FLAG
,DELETED_FLAG
,SEEDED_FLAG
)
SELECT
i.ui_def_id
,l_cont_types_tbl(j)
,l_template_ids_tbl(j)
,'0'
,0
,'0'
,'0'
,'0'
FROM dual
WHERE NOT EXISTS
(SELECT NULL FROM CZ_UI_CONT_TYPE_TEMPLS
WHERE ui_def_id=i.ui_def_id AND content_type=l_cont_types_tbl(j));
FOR c_ct IN(SELECT c.*
FROM cz_ui_defs ui,
cz_ui_cont_type_templs c
WHERE ui.deleted_flag = '0'
AND ui.devl_project_id = p_devl_project_id
AND c.ui_def_id = ui.ui_def_id
AND c.deleted_flag = '0'
AND content_type = 543
AND NOT EXISTS
(SELECT 1
FROM cz_ui_cont_type_templs
WHERE ui_def_id = ui.ui_def_id
AND content_type =560)) LOOP
INSERT INTO cz_ui_cont_type_templs (UI_DEF_ID,CONTENT_TYPE,TEMPLATE_ID,DELETED_FLAG,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,MASTER_TEMPLATE_FLAG,SEEDED_FLAG,TEMPLATE_UI_DEF_ID,WRAP_TEMPLATE_FLAG)
VALUES
(
c_ct.UI_DEF_ID,560,c_ct.TEMPLATE_ID,c_ct.DELETED_FLAG,c_ct.CREATED_BY,c_ct.CREATION_DATE,c_ct.LAST_UPDATED_BY,
c_ct.LAST_UPDATE_DATE,c_ct.LAST_UPDATE_LOGIN,c_ct.MASTER_TEMPLATE_FLAG,c_ct.SEEDED_FLAG,c_ct.TEMPLATE_UI_DEF_ID,c_ct.WRAP_TEMPLATE_FLAG
);
UPDATE cz_ui_cont_type_templs uiout set content_type=561
WHERE
ui_def_id IN (select ui.ui_def_id from cz_ui_defs ui WHERE ui.deleted_flag = '0' AND ui.devl_project_id = p_devl_project_id)
AND deleted_flag = '0'
AND content_type = 543
AND NOT EXISTS
(SELECT NULL FROM CZ_UI_CONT_TYPE_TEMPLS
WHERE ui_def_id=uiout.ui_def_id AND content_type=561) ;
UPDATE cz_ui_defs SET deleted_flag='1' WHERE ui_style = 0
AND deleted_flag ='0'
AND devl_project_id =p_dev_project_id
RETURNING ui_def_id BULK COLLECT INTO v_cz_ids_tbl;
displayMessage(2,p_dev_project_id , 'UI' ,v_cz_ids_tbl(i),l_api_name,'CZ_CNV_WARN_DHTML_DELETE');
v_cz_ids_tbl.DELETE;
FOR v_ui IN(SELECT DISTINCT a.ui_def_id
FROM cz_ui_nodes a,
cz_ui_defs ui
WHERE a.deleted_flag = '0'
AND ui.deleted_flag = '0'
AND a.ui_def_id = ui.ui_def_id
AND ui.devl_project_id = p_dev_project_id
AND a.func_comp_id IS NOT NULL) LOOP
displayMessage(2,p_dev_project_id , 'UI' ,v_ui.ui_def_id,l_api_name,'CZ_CNV_WARN_FC_NOT_SUPPORTED');
(SELECT ui.ui_def_id
FROM cz_ui_defs ui
WHERE ui.deleted_flag ='0'
AND ui.devl_project_id = p_dev_project_id)
LOOP
displayMessage(3,p_dev_project_id , 'UI' ,v_ui.ui_def_id,l_api_name,'CZ_CNV_ADV_DEFAULT_UIS');
SELECT DISTINCT * FROM(
SELECT text_str,
te.template_id,
'NodeUnsatisfied' lceprop,
'UserInputRequired' fceprop,
te.element_id ,
te.element_type
FROM cz_ui_cont_type_templs c, cz_ui_templates t , cz_signatures s ,cz_intl_texts text , cz_ui_template_elements te ,cz_ui_defs ui
WHERE c.ui_def_id = ui.ui_def_id
AND c.template_id = t.template_id
AND c.content_type = s.signature_id
AND c.deleted_flag = '0'
AND t.deleted_flag = '0'
AND te.deleted_flag = '0'
AND te.element_type = 8 -- is a text element
and text.ui_page_id=t.template_id AND UPPER(text_str) LIKE '%'||fnd_global.local_chr(38)||'NODEUNSATISFIED%'
and text.deleted_flag='0'
and c.TEMPLATE_UI_DEF_ID = t.UI_DEF_ID
and s.signature_type IN ('SES','UCO')
AND t.ui_def_id=c.template_ui_def_id
and t.template_id=te.template_id
and te.element_id=text.intl_text_id
and ui.deleted_flag ='0'
and text.ui_def_id = t.ui_def_id
AND ui.devl_project_id = p_dev_project_id
AND t.ui_def_id=te.ui_def_id
UNION ALL
SELECT text_str,
te.template_id,
'Unsatisfied' lceprop,
'UserInputRequired' fceprop,
te.element_id ,
te.element_type
FROM cz_ui_cont_type_templs c, cz_ui_templates t , cz_signatures s ,cz_intl_texts text, cz_ui_template_elements te , cz_ui_defs ui
WHERE c.ui_def_id = ui.ui_def_id
AND c.template_id = t.template_id
AND c.content_type = s.signature_id
AND c.deleted_flag = '0'
AND t.deleted_flag = '0'
AND te.deleted_flag = '0'
AND te.element_type = 8 -- is a text element
and text.ui_page_id=t.template_id AND UPPER(text_str) LIKE '%'||fnd_global.local_chr(38)||'UNSATISFIED%'
and text.deleted_flag='0'
and c.TEMPLATE_UI_DEF_ID = t.UI_DEF_ID
and s.signature_type IN ('SES','UCO')
AND t.ui_def_id=c.template_ui_def_id
and t.template_id=te.template_id
and te.element_id=text.intl_text_id
and ui.deleted_flag ='0'
and text.ui_def_id = t.ui_def_id
AND ui.devl_project_id = p_dev_project_id
AND t.ui_def_id=te.ui_def_id
UNION ALL
SELECT text_str,
te.template_id,
'SubtreeUnsatisfied' lceprop,
'UserInputRequiredInSubtree' fceprop,
te.element_id ,
te.element_type
FROM cz_ui_cont_type_templs c, cz_ui_templates t , cz_signatures s ,cz_intl_texts text, cz_ui_template_elements te , cz_ui_defs ui
WHERE c.ui_def_id = ui.ui_def_id
AND c.template_id = t.template_id
AND c.content_type = s.signature_id
AND c.deleted_flag = '0'
AND t.deleted_flag = '0'
AND te.deleted_flag = '0'
AND te.element_type = 8 -- is a text element
and text.ui_page_id=t.template_id AND UPPER(text_str) LIKE '%'||fnd_global.local_chr(38)||'SUBTREEUNSATISFIED%'
and text.deleted_flag='0'
and c.TEMPLATE_UI_DEF_ID = t.UI_DEF_ID
and s.signature_type IN ('SES','UCO')
AND t.ui_def_id=c.template_ui_def_id
and t.template_id=te.template_id
and te.element_id=text.intl_text_id
and ui.deleted_flag ='0'
and text.ui_def_id = t.ui_def_id
AND ui.devl_project_id = p_dev_project_id
AND t.ui_def_id=te.ui_def_id
)
)
LOOP
--Refer TD section 4.1.5.2
--UI Has Text Expression that references a System Property that was redefined for FCE(
--Unsatisfied (session),NodeUnsatisfied,SubtreeUnsatisfied)
--Unsatisfied becomes UserInputRequired()
--SubtreeUnsatisfied becomes UserInputRequiredInSubtree
--Session.Unsatisfied.UserInputRequired
displayMessage(1,p_dev_project_id , 'TEMPLATE' ,v_ui1.template_id,l_api_name,'CZ_CNV_FAIL_REDEF_SYSPROP','LCEPROPERTYNAME',
v_ui1.lceprop,'EXPRESSION_OR_CONDITION' ,' Text Expression ' ,'FCEPROPERTYNAME' ,v_ui1.fceprop
,v_ui1.element_id , v_ui1.element_type);
SELECT DISTINCT tempel.element_id ,te.template_id,
decode(ex.template_id, 820, 'NodeUnsatisfied', 836, 'Unsatisfied', 849, 'SubtreeUnsatisfied') lceprop,
decode(ex.template_id, 820, 'InputRequired', 836, 'InputRequired', 849, 'InputRequiredInSubtree') fceprop,
ru.name,
ui.ui_def_id,
ru.ui_page_element_id,
tempel.element_type
FROM cz_expression_nodes ex,
cz_rules ru,
cz_ui_defs ui ,
cz_ui_cont_type_templs ct,
cz_ui_defs ui2,
cz_ui_templates te ,cz_ui_template_elements tempel
WHERE ex.template_id IN(820, 836, 849)
AND ex.deleted_flag = '0'
AND ex.rule_id = ru.rule_id
AND ru.deleted_flag = '0'
AND ui.deleted_flag = '0'
AND ui2.deleted_flag = '0'
AND ru.ui_def_id = ui.ui_def_id
AND ui.ui_def_id = te.ui_def_id
and te.template_id=ct.template_id
and ct.ui_def_id=ui2.ui_def_id
and ui2.devl_project_id = p_dev_project_id
and tempel.template_id=te.template_id
AND tempel.ui_def_id = te.ui_def_id
)
LOOP
--Refer TD section 4.1.5.2
--UI Has Display Condition that references a System Property that was redefined for FCE(
--Unsatisfied (session),NodeUnsatisfied,SubtreeUnsatisfied)
--Unsatisfied becomes UserInputRequired()
--SubtreeUnsatisfied becomes UserInputRequiredInSubtree
--Session.Unsatisfied UserInputRequired
displayMessage(1,p_dev_project_id , 'TEMPLATE' , v_ui.template_id,l_api_name,'CZ_CNV_FAIL_REDEF_SYSPROP','LCEPROPERTYNAME',
v_ui.lceprop,'EXPRESSION_OR_CONDITION' , ' Display Condition ' ,'FCEPROPERTYNAME' ,v_ui.fceprop ,
v_ui.element_id ,v_ui.element_type );
SELECT text_str,
cz_intl_texts.ui_def_id,
'NodeUnsatisfied' lceprop,
'UserInputRequired' fceprop
FROM cz_intl_texts,cz_ui_defs
WHERE UPPER(text_str) LIKE '%'||fnd_global.local_chr(38)||'NODEUNSATISFIED%'
AND model_id = p_dev_project_id
AND cz_intl_texts.ui_def_id=cz_ui_defs.ui_def_id
AND cz_ui_defs.devl_project_id=p_dev_project_id
AND cz_ui_defs.deleted_flag='0'
UNION ALL
SELECT text_str,
cz_intl_texts.ui_def_id,
'Unsatisfied' lceprop,
'UserInputRequired' fceprop
FROM cz_intl_texts,cz_ui_defs
WHERE UPPER(text_str) LIKE '%'||fnd_global.local_chr(38)||'UNSATISFIED%'
AND model_id = p_dev_project_id
AND cz_intl_texts.ui_def_id=cz_ui_defs.ui_def_id
AND cz_ui_defs.devl_project_id=p_dev_project_id
AND cz_ui_defs.deleted_flag='0'
UNION ALL
SELECT text_str,
cz_intl_texts.ui_def_id,
'SubtreeUnsatisfied' lceprop,
'UserInputRequiredInSubtree' fceprop
FROM cz_intl_texts,cz_ui_defs
WHERE UPPER(text_str) LIKE '%'||fnd_global.local_chr(38)||'SUBTREEUNSATISFIED%'
AND model_id = p_dev_project_id
AND cz_intl_texts.ui_def_id=cz_ui_defs.ui_def_id
AND cz_ui_defs.devl_project_id=p_dev_project_id
AND cz_ui_defs.deleted_flag='0'
)
LOOP
--Refer TD section 4.1.5.2
--UI Has Text Expression that references a System Property that was redefined for FCE(
--Unsatisfied (session),NodeUnsatisfied,SubtreeUnsatisfied)
--Unsatisfied becomes UserInputRequired()
--SubtreeUnsatisfied becomes UserInputRequiredInSubtree
--Session.Unsatisfied.UserInputRequired
--todo to do check this message and see element description .
displayMessage(2,p_dev_project_id , 'UIE' ,v_ui.ui_def_id,l_api_name,'CZ_CNV_WARN_REDEF_SYSPROP','LCEPROPERTYNAME' , v_ui.lceprop,'EXPRESSION_OR_CONDITION' ,' Text Expression ' ,'FCEPROPERTYNAME' ,v_ui.fceprop);
Update cz_localized_texts
set localized_str=REPLACE(REPLACE(REPLACE(localized_str,fnd_global.local_chr(38)||'NODEUNSATISFIED',fnd_global.local_chr(38)||'USERINPUTREQUIRED'),fnd_global.local_chr(38)||'UNSATISFIED%'
,fnd_global.local_chr(38)||'USERINPUTREQUIRED'),fnd_global.local_chr(38)||'SUBTREEUNSATISFIED%',fnd_global.local_chr(38)||'USERINPUTREQUIREDINSUBTREE')
WHERE UPPER(localized_str) LIKE '%'||fnd_global.local_chr(38)||'%UNSATISFIED%'
AND model_id = p_dev_project_id
AND ui_def_id IN ( SELECT ui_def_id FROM cz_ui_defs
WHERE deleted_flag='0' AND cz_localized_texts.ui_def_id = ui_def_id
AND devl_project_id=p_dev_project_id
)
AND deleted_flag='0';
(SELECT DISTINCT template_id , decode(template_id,820,'NodeUnsatisfied' ,836,'Unsatisfied',849,'SubtreeUnsatisfied') lceprop ,
decode(template_id,820,'InputRequired' ,836,'InputRequired',849,'InputRequiredInSubtree') fceprop,ru.name ,ui.ui_def_id, ru.ui_page_element_id
FROM cz_expression_nodes ex,
cz_rules ru,
cz_ui_defs ui
WHERE template_id IN(820, 836, 849)
AND ex.deleted_flag ='0'
AND ex.rule_id = ru.rule_id
AND ru.deleted_flag ='0'
AND ui.deleted_flag ='0'
AND ru.ui_def_id = ui.ui_def_id
AND ui.devl_project_id = p_dev_project_id
)
LOOP
--Refer TD section 4.1.5.2
--UI Has Display Condition that references a System Property that was redefined for FCE(
--Unsatisfied (session),NodeUnsatisfied,SubtreeUnsatisfied)
--Unsatisfied becomes UserInputRequired()
--SubtreeUnsatisfied becomes UserInputRequiredInSubtree
--Session.Unsatisfied UserInputRequired
displayMessage(2,p_dev_project_id , 'UIE' ,GET_UI_ELEMENT_ID(v_ui.ui_page_element_id),l_api_name,'CZ_CNV_WARN_REDEF_SYSPROP','LCEPROPERTYNAME' , v_ui.lceprop,'EXPRESSION_OR_CONDITION' ,' Display Condition ' ,'FCEPROPERTYNAME' ,v_ui.fceprop);
815 974 MinSelected DefinitionMinSelections
816 975 MaxSelected DefinitionMaxSelections
*/
FOR v_ui IN
(SELECT template_id , (select name from cz_rules where rule_id=template_id) name , REPLACE(REPLACE((select 'Definition'||name from cz_rules where rule_id=template_id),'DefinitionMaxSelected','DefinitionMaxSelections')
,'DefinitionMinSelected','DefinitionMinSelections') ruleName,ui.ui_def_id , ru.ui_page_element_id
FROM cz_expression_nodes ex,
cz_rules ru,
cz_ui_defs ui
WHERE template_id IN (894,818,812,847,893,817,811,846,813,814,815,816)
AND ex.deleted_flag ='0'
AND ex.rule_id = ru.rule_id
AND ru.deleted_flag ='0'
AND ui.deleted_flag ='0'
AND ru.ui_def_id = ui.ui_def_id
AND ui.devl_project_id = p_dev_project_id
)
LOOP
--Refer TD section 4.1.5.2
--If this ui element has a display condition / text expression which reference Min or Max system properties
displayMessage(2,p_dev_project_id , 'UIE' ,GET_UI_ELEMENT_ID(v_ui.ui_page_element_id),l_api_name,'CZ_CNV_WARN_DIFF_SYSPROP','PROPERTYNAME_1'
, v_ui.name , 'EXPRESSION_OR_CONDITION', ' Display Condition ' ,'PROPERTYNAME_2', 'Definition'||v_ui.rulename);
UPDATE cz_expression_nodes SET template_id= DECODE(template_id,894,979,818,977,812,899,847,898,893,978,817,976,811,897,846,896,813,971,814,973,815,974,816,975,template_id)
WHERE expr_node_id IN
(
SELECT ex.expr_node_id
FROM cz_expression_nodes ex,
cz_rules ru,
cz_ui_defs ui
WHERE template_id IN (894,818,812,847,893,817,811,846,813,814,815,816)
AND ex.deleted_flag ='0'
AND ex.rule_id = ru.rule_id
AND ru.deleted_flag ='0'
AND ui.deleted_flag ='0'
AND ru.ui_def_id = ui.ui_def_id
AND ui.devl_project_id = p_dev_project_id
);
(select intl.ui_def_id , ru.name lceprop , REPLACE(REPLACE('Definition'||ru.name ,'DefinitionMaxSelected','DefinitionMaxSelections'),'DefinitionMinSelected','DefinitionMinSelections') fceprop, intl.ui_page_element_id
from cz_intl_texts intl, cz_ui_defs ui , cz_rules ru where
ru.rule_id in (894,818,812,893,817,811,813,814,815,816)
and text_str like '%'||fnd_global.local_chr(38)||upper(ru.name)||'%'
and intl.ui_def_id = ui.ui_def_id
and intl.deleted_flag='0'
and ui.deleted_flag='0'
and intl.model_id =ui.devl_project_id
and ui.devl_project_id= p_dev_project_id
)
LOOP
--Refer TD section 4.1.5.2
--If this ui element has a display condition / text expression which reference Min or Max system properties
displayMessage(2,p_dev_project_id , 'UIE' ,GET_UI_ELEMENT_ID(v_ui.ui_page_element_id),l_api_name,'CZ_CNV_WARN_DIFF_SYSPROP','PROPERTYNAME_1' , v_ui.lceprop,'EXPRESSION_OR_CONDITION' ,' Text Expression ' ,'PROPERTYNAME_2' ,v_ui.fceprop);
UPDATE cz_localized_texts intl SET localized_str = REPLACE(REPLACE(REPLACE(REPLACE(localized_str , ' '||fnd_global.local_chr(38)||'MIN' , ' '||fnd_global.local_chr(38)||'DEFINITIONMIN')
,' '||fnd_global.local_chr(38)||'MAX' , ' '||fnd_global.local_chr(38)||'DEFINITIONMAX'),'DEFINITIONMAXSELECTED','DEFINITIONMAXSELECTIONS'),'DEFINITIONMINSELECTED','DEFINITIONMINSELECTIONS')
WHERE ui_def_id IN(select ui_def_id from cz_ui_defs WHERE deleted_flag='0' AND devl_project_id= p_dev_project_id )
AND model_id = p_dev_project_id
AND EXISTS (
SELECT 1 FROM cz_rules WHERE rule_id IN (894,818,812,893,817,811,813,814,815,816)
AND localized_str LIKE '%'||fnd_global.local_chr(38)||upper(name)||'%' )
AND deleted_flag='0';
UPDATE cz_ps_nodes
SET effective_usage_mask = '0000000000000000',
effective_from = cz_utils.epoch_begin,
effective_until = cz_utils.epoch_end,
effectivity_set_id = NULL,
eff_from = NULL,
eff_to = NULL,
eff_mask = NULL
WHERE(ps_node_type IN(PS_TYPE_TOTAL, PS_TYPE_RESOURCE) OR(ps_node_type = PS_TYPE_FEATURE
AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT)) OR(ps_node_type = PS_TYPE_COMPONENT AND virtual_flag = 1))
AND devl_project_id = p_dev_project_id
AND (effective_usage_mask <> '0000000000000000' or
effective_from IS NOT NULL or
effective_until IS NOT NULL or
effectivity_set_id IS NOT NULL or
eff_from IS NOT NULL or
eff_to IS NOT NULL or
eff_mask IS NOT NULL)
RETURNING ps_node_id , name BULK COLLECT INTO v_cz_ids_tbl ,v_cz_names_tbl;
v_cz_ids_tbl.DELETE;
v_cz_names_tbl.DELETE;
SELECT cz_rules_s.NEXTVAL INTO last_id_allocated FROM DUAL;
SELECT cz_intl_texts_s.nextval INTO l_intl_text_id FROM Dual ;
SELECT name , nvl(initial_value, initial_num_value ) INTO l_ps_node_name , initialvalue from cz_ps_nodes where ps_node_id=p_ps_node_id;
V_CZ_RULES(v_cz_rule_count).deleted_flag:= '0' ;
INSERT INTO cz_rule_folders(rule_folder_id, name, devl_project_id, tree_seq, deleted_flag, folder_type, effective_usage_mask, persistent_rule_folder_id, object_type, disabled_flag , parent_rule_folder_id)
VALUES(l_rule_id, 'Constraint-' || l_ps_node_name , p_devl_project_id, '1', '0', '0', '0000000000000000', l_rule_id, 'RUL', '0' , V_CZ_RULES(v_cz_rule_count).rule_folder_id );
PROCEDURE insertImpliesRuleRecords(dev_project_id IN cz_devl_projects.devl_project_id%TYPE, ps_node_id IN cz_ps_nodes.ps_node_id%TYPE,
ps_node_type IN cz_ps_nodes.ps_node_type%TYPE, l_rule_id IN cz_rules.rule_id%TYPE, initialvalue IN cz_ps_nodes.initial_value%TYPE) AS
l_msg VARCHAR2(2000);
l_api_name constant VARCHAR2(30) := 'insertImpliesRuleRecords';
SELECT cz_expression_nodes_s.nextval
INTO l_expression_node_id
FROM dual;
SELECT cz_expression_nodes_s.nextval
INTO l_expression_node_id1
FROM dual;
SELECT cz_expression_nodes_s.nextval
INTO l_expression_node_id1
FROM dual;
SELECT MIN(MODEL_REF_EXPL_ID) INTO l_mod_ref FROM cz_model_ref_expls WHERE model_id=dev_project_id AND deleted_flag='0';
UPDATE cz_rules
SET rule_class = RULE_CLASS_DEFAULT
WHERE rule_id = l_rule_id;
END insertImpliesRuleRecords;
SELECT cz_expression_nodes_s.nextval INTO l_expression_node_id FROM dual;
v_cz_expression_nodes(v_cz_expr_node_count).DELETED_FLAG:= '0' ;
SELECT cz_expression_nodes_s.nextval INTO l_expression_node_id1 FROM dual;
v_cz_expression_nodes(v_cz_expr_node_count).DELETED_FLAG:= '0' ;
SELECT cz_expression_nodes_s.nextval INTO l_expression_node_id1 FROM dual;
SELECT MIN(MODEL_REF_EXPL_ID) INTO l_mod_ref FROM cz_model_ref_expls WHERE model_id=dev_project_id AND deleted_flag='0' AND component_id=(select component_id from cz_ps_nodes ps where ps.ps_node_id=l_ps_node_id) ;
v_cz_expression_nodes(v_cz_expr_node_count).DELETED_FLAG:= '0' ;
SELECT DISTINCT r.devl_project_id,
e.template_id,
e1.rule_id,
r.rule_folder_id,
ps.name,
p.name devname,
r.reason_id,
e1.model_ref_expl_id
FROM cz_rules r,
cz_devl_projects p,
cz_expression_nodes e,
cz_expression_nodes e1,
cz_ps_nodes ps
WHERE r.deleted_flag = '0'
AND e.deleted_flag = '0'
AND e1.deleted_flag = '0'
AND p.deleted_flag = '0'
AND p.config_engine_type = 'F'
AND e.template_id IN(708, 710 ,712 ,714)
AND e1.ps_node_id = p_ps_node_id
AND e1.ps_node_id = ps.ps_node_id
AND e1.rule_id = e.rule_id
AND r.rule_id = e.rule_id
AND p.devl_project_id=ps.devl_project_id
AND p.devl_project_id = p_devl_project_id
ORDER BY r.devl_project_id , e1.model_ref_expl_id;
Update cz_expression_nodes set template_id=decode(template_id,708 , OPERATOR_ADDSTO , 710 , OPERATOR_SUBTRACTSFROM) where rule_id=v_contribute_consume.rule_id and template_id in (708,710);
Update cz_expression_nodes set template_id=25 where rule_id=v_contribute_consume.rule_id and template_id =22;
UPDATE cz_localized_texts SET deleted_flag =1 WHERE intl_text_id=v_contribute_consume.reason_id;
UPDATE cz_rules SET reason_id=null WHERE rule_id=v_contribute_consume.rule_id;
select name into l_ps_node_name from cz_ps_nodes where ps_node_id =p_ps_node_id;
insertImpliesRuleRecords(p_devl_project_id, p_ps_node_id, p_ps_node_type, l_rule_id, initialvalue );
select name into l_ps_node_name from cz_ps_nodes where ps_node_id =p_ps_node_id;
UPDATE cz_ps_nodes
SET initial_num_value = NULL
WHERE devl_project_id = p_dev_project_id
AND initial_num_value IS NOT NULL
AND(ps_node_type IN(PS_TYPE_TOTAL, PS_TYPE_RESOURCE)
OR(ps_node_type = PS_TYPE_FEATURE AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT))
OR(ps_node_type = PS_TYPE_FEATURE AND feature_type = FEATURE_TYPE_BOOLEAN));
(SELECT ps_node_id , feature_type
FROM cz_ps_nodes
WHERE devl_project_id = p_dev_project_id
AND minimum IS NULL
AND(ps_node_type = PS_TYPE_FEATURE
AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT)))
LOOP
IF c_processing.feature_type= FEATURE_TYPE_INTEGER THEN
displayMessage(3,p_dev_project_id , 'NODE' , c_processing.ps_node_id,l_api_name,'CZ_CNV_ADV_VAL_MIN_BOUND','MINVAL', INTEGER_MIN_VAL);
UPDATE cz_ps_nodes
SET minimum = decode(feature_type, FEATURE_TYPE_INTEGER, INTEGER_MIN_VAL, FEATURE_TYPE_FLOAT, -SOLVER_MAX_DOUBLE)
WHERE devl_project_id = p_dev_project_id
AND minimum IS NULL
AND(ps_node_type = PS_TYPE_FEATURE
AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT));
(SELECT ps_node_id
FROM cz_ps_nodes
WHERE devl_project_id = p_dev_project_id
AND ps_node_type = PS_TYPE_FEATURE
AND feature_type=FEATURE_TYPE_LIST_OF_OPTIONS
AND counted_options_flag='1')
LOOP
displayMessage(3,p_dev_project_id , 'NODE' , c_processing.ps_node_id,l_api_name,'CZ_CNV_ADV_OPT_QTY_MAX','MAXVAL', INTEGER_MAX_VALUE);
UPDATE cz_ps_nodes
SET MAX_QTY_PER_OPTION = fnd_profile.value('CZ_DEFAULT_MAX_QTY_INT')
WHERE devl_project_id = p_dev_project_id
AND ps_node_type = PS_TYPE_FEATURE
AND feature_type=FEATURE_TYPE_LIST_OF_OPTIONS
AND counted_options_flag='1';
(SELECT ps_node_id, feature_type
FROM cz_ps_nodes
WHERE devl_project_id = p_dev_project_id
AND maximum IS NULL
AND(ps_node_type = PS_TYPE_FEATURE
AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT , FEATURE_TYPE_LIST_OF_OPTIONS)))
LOOP
IF c_processing.feature_type= FEATURE_TYPE_INTEGER OR c_processing.feature_type=FEATURE_TYPE_LIST_OF_OPTIONS THEN
displayMessage(3,p_dev_project_id , 'NODE' ,c_processing.ps_node_id,l_api_name,'CZ_CNV_ADV_VAL_MAX_BOUND','MAXVAL', INTEGER_MAX_VALUE);
UPDATE cz_ps_nodes
SET maximum = decode(feature_type, FEATURE_TYPE_INTEGER, INTEGER_MAX_VALUE, FEATURE_TYPE_LIST_OF_OPTIONS, INTEGER_MAX_VALUE, FEATURE_TYPE_FLOAT, SOLVER_MAX_DOUBLE)
WHERE devl_project_id = p_dev_project_id
AND maximum IS NULL
AND(ps_node_type = PS_TYPE_FEATURE
AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT));
(SELECT ps_node_id ,decimal_qty_flag , decode(USE_BOM_DEFAULT_QTY,'TRUE',initial_num_value,decode(decimal_qty_flag, 1, SOLVER_MAX_DOUBLE, INTEGER_MAX_VALUE)) changedValue
FROM cz_ps_nodes
WHERE devl_project_id = p_dev_project_id
AND ((maximum IS NULL OR maximum =-1)
AND(ps_node_type IN(PS_TYPE_BOM_MODEL, PS_TYPE_BOM_OPTION_CLASS, PS_TYPE_BOM_STD_ITEM)))
)
LOOP
displayMessage(3,p_dev_project_id , 'NODE' ,c_processing.ps_node_id,l_api_name,'CZ_CNV_ADV_QTY_MAX_BOUND','MAXVAL', c_processing.changedValue);
UPDATE cz_ps_nodes
SET maximum = decode(USE_BOM_DEFAULT_QTY,'TRUE',initial_num_value,decode(decimal_qty_flag, 1, SOLVER_MAX_DOUBLE, INTEGER_MAX_VALUE))
WHERE devl_project_id = p_dev_project_id
AND (maximum IS NULL OR maximum =-1)
AND(ps_node_type IN(PS_TYPE_BOM_MODEL, PS_TYPE_BOM_OPTION_CLASS, PS_TYPE_BOM_STD_ITEM));
UPDATE cz_ps_nodes
SET minimum = decode(USE_BOM_DEFAULT_QTY,'TRUE',initial_num_value,decode(decimal_qty_flag, 1,0 ,1 ))
WHERE devl_project_id = p_dev_project_id
AND (minimum IS NULL OR minimum =0)
AND(ps_node_type IN(PS_TYPE_BOM_MODEL, PS_TYPE_BOM_OPTION_CLASS, PS_TYPE_BOM_STD_ITEM))
RETURNING ps_node_id , minimum BULK COLLECT INTO v_cz_ids_tbl , v_cz_num_tbl;
v_cz_ids_tbl.DELETE;
v_cz_num_tbl.DELETE;
UPDATE cz_ps_nodes psout
SET minimum_selected = decode(USE_BOM_DEFAULT_QTY,'TRUE',initial_num_value,decode(decimal_qty_flag, 1,0,1 ))
WHERE devl_project_id = p_dev_project_id
AND (minimum_selected IS NULL OR minimum_selected =0)
AND(ps_node_type =PS_TYPE_REFERENCE)
AND EXISTS( SELECT 1 FROM cz_ps_nodes WHERE ps_node_id= psout.component_id
AND ps_node_type =PS_TYPE_BOM_MODEL )
RETURNING ps_node_id , minimum_selected BULK COLLECT INTO v_cz_ids_tbl , v_cz_num_tbl;
v_cz_ids_tbl.DELETE;
v_cz_num_tbl.DELETE;
FOR c_processing in ( SELECT ps_node_id ,decimal_qty_flag , initial_num_value , decode(USE_BOM_DEFAULT_QTY,'TRUE',initial_num_value,decode(decimal_qty_flag, 1, SOLVER_MAX_DOUBLE, INTEGER_MAX_VALUE)) changedValue FROM cz_ps_nodes
WHERE devl_project_id = p_dev_project_id
AND (maximum_selected IS NULL OR maximum_selected =-1)
AND(ps_node_type =PS_TYPE_REFERENCE)
and item_id is not null
)LOOP
displayMessage(3,p_dev_project_id , 'NODE' ,c_processing.ps_node_id,l_api_name,'CZ_CNV_ADV_QTY_MAX_BOUND','MAXVAL', c_processing.changedValue);
UPDATE cz_ps_nodes
SET maximum_selected = decode(USE_BOM_DEFAULT_QTY,'TRUE',initial_num_value,decode(decimal_qty_flag, 1, SOLVER_MAX_DOUBLE, INTEGER_MAX_VALUE))
WHERE devl_project_id = p_dev_project_id
AND (maximum_selected IS NULL OR maximum_selected =-1)
AND(ps_node_type =PS_TYPE_REFERENCE)
and item_id is not null ;
UPDATE cz_ps_nodes
SET maximum = INTEGER_MAX_VALUE
WHERE devl_project_id = p_dev_project_id
AND maximum IS NULL
AND(ps_node_type IN(232))
RETURNING ps_node_id BULK COLLECT INTO v_cz_ids_tbl;
v_cz_ids_tbl.DELETE;
UPDATE cz_ps_nodes
SET maximum = DECODE(SIGN(nvl(initial_num_value,0) - SOLVER_MAX_DOUBLE), -1 , SOLVER_MAX_DOUBLE, 0 , SOLVER_MAX_DOUBLE , 1 , initial_num_value)
WHERE devl_project_id = p_dev_project_id
AND maximum IS NULL
AND ps_node_type IN(PS_TYPE_TOTAL,PS_TYPE_RESOURCE)
RETURNING ps_node_id , maximum BULK COLLECT INTO v_cz_ids_tbl , v_cz_num_tbl;
v_cz_ids_tbl.DELETE;
v_cz_num_tbl.DELETE;
UPDATE cz_ps_nodes
SET minimum = -SOLVER_MAX_DOUBLE
WHERE devl_project_id = p_dev_project_id
AND minimum IS NULL
AND ps_node_type IN(PS_TYPE_TOTAL,PS_TYPE_RESOURCE)
RETURNING ps_node_id BULK COLLECT INTO v_cz_ids_tbl;
v_cz_ids_tbl.DELETE;
SELECT ex.*
FROM cz_expression_nodes ex,
cz_rules ru
WHERE ru.devl_project_id = p_dev_project_id
AND ru.rule_id = ex.rule_id
AND ru.deleted_flag ='0'
AND ex.template_id = 430
AND ex.deleted_flag ='0';
SELECT exp1.expr_node_id exp1id,
exp2.expr_node_id exp2id,
exp2.expr_parent_id exp2parentid,
exp2.rule_id , psnode.ps_node_id
FROM cz_expression_nodes exp1,
cz_expression_nodes exp2,
cz_ps_nodes psnode
WHERE exp1.deleted_flag ='0'
AND exp2.deleted_flag ='0'
AND exp1.template_id IN(1, 2, 3, 4, 5)
AND exp2.ps_node_id = psnode.ps_node_id
AND exp1.rule_id = exp2.rule_id
AND psnode.ps_node_type = PS_TYPE_FEATURE
AND psnode.feature_type = FEATURE_TYPE_INTEGER
AND psnode.minimum >= 0
AND psnode.devl_project_id = p_dev_project_id
AND exists (select 1 from cz_expression_nodes where
expr_node_id=exp2.expr_parent_id
and template_id in ( 306 ,307 ,360 ,552 ,21 ) -- All Logic operators
);
SELECT DISTINCT cz_rules.rule_id,
cz_rules.devl_project_id
FROM cz_rules,
cz_expression_nodes exp1,
cz_expression_nodes exp2,
cz_expression_nodes exp3
WHERE rule_type IN(RULE_TYPE_COMPAT_TABLE, RULE_TYPE_DESIGN_CHART_RULE, RULE_TEMPLATE_FREEFORM_RULE)
AND exp1.rule_id = exp2.rule_id
AND exp1.rule_id = exp3.rule_id
AND exp3.template_id = 23
AND exp1.rule_id = cz_rules.rule_id
AND exp1.expr_type = 207
AND exp2.expr_type = 207
AND exp1.expr_node_id <> exp2.expr_node_id
AND EXISTS
(SELECT 1
FROM cz_expression_nodes expin1,
cz_expression_nodes expin2,
cz_ps_nodes ps1,
cz_ps_nodes ps2
WHERE expin1.expr_node_id = exp1.expr_parent_id
AND expin2.expr_node_id = exp2.expr_parent_id
AND expin2.ps_node_id = ps2.ps_node_id
AND expin1.ps_node_id = ps1.ps_node_id
AND ((ps1.maximum_selected > 1
AND ps2.maximum_selected > 1
)
OR
(ps1.maximum > 1
AND ps2.maximum > 1
)
)
)
AND cz_rules.devl_project_id = p_dev_project_id
UNION ALL
SELECT DISTINCT cf.rule_id ,ps.devl_project_id
FROM cz_des_chart_features cf,
cz_des_chart_features cf1,
cz_ps_nodes ps,
cz_ps_nodes ps1,
cz_rules rule
WHERE cf.rule_id = cf1.rule_id
AND cf.feature_id = ps.ps_node_id
AND cf1.feature_id = ps1.ps_node_id
AND cf1.feature_id <> cf.feature_id
AND ((ps.maximum_selected > 1
AND ps1.maximum_selected > 1
)
OR
(ps.maximum > 1
AND ps1.maximum > 1
)
)
AND rule.devl_project_id = p_dev_project_id
AND rule.rule_id = cf.rule_id
AND rule_type IN(24, 30);
SELECT DISTINCT cz_rules.rule_id,
cz_rules.devl_project_id
FROM cz_rules,
cz_expression_nodes exp1,
cz_expression_nodes exp2,
cz_expression_nodes exp3
WHERE rule_type IN(RULE_TYPE_COMPAT_TABLE, RULE_TYPE_DESIGN_CHART_RULE, RULE_TEMPLATE_FREEFORM_RULE)
AND exp1.rule_id = exp2.rule_id
AND exp1.rule_id = exp3.rule_id
AND exp3.template_id = 23
AND exp1.rule_id = cz_rules.rule_id
AND exp1.expr_type = 207
AND exp2.expr_type = 207
AND exp1.expr_node_id <> exp2.expr_node_id
AND EXISTS
(SELECT 1
FROM cz_expression_nodes expin1,
cz_expression_nodes expin2,
cz_ps_nodes ps1,
cz_ps_nodes ps2
WHERE expin1.expr_node_id = exp1.expr_parent_id
AND expin2.expr_node_id = exp2.expr_parent_id
AND expin2.ps_node_id = ps2.ps_node_id
AND expin1.ps_node_id = ps1.ps_node_id
AND (ps1.ps_node_type =PS_TYPE_BOM_MODEL
OR ps2.ps_node_type =PS_TYPE_BOM_MODEL)
)
AND cz_rules.devl_project_id = p_dev_project_id
UNION ALL
SELECT DISTINCT cf.rule_id ,ps.devl_project_id
FROM cz_des_chart_features cf,
cz_des_chart_features cf1,
cz_ps_nodes ps,
cz_ps_nodes ps1,
cz_rules rule
WHERE cf.rule_id = cf1.rule_id
AND cf.feature_id = ps.ps_node_id
AND cf1.feature_id = ps1.ps_node_id
AND cf1.feature_id <> cf.feature_id
AND (ps.ps_node_type =PS_TYPE_BOM_MODEL OR ps1.ps_node_type =PS_TYPE_BOM_MODEL )
AND rule.devl_project_id = p_dev_project_id
AND rule.rule_id = cf.rule_id
AND rule_type IN(24,30);
UPDATE cz_expression_nodes
SET template_id = 408
WHERE template_id = 404
AND rule_id IN
(SELECT rule_id
FROM cz_rules
WHERE devl_project_id = p_dev_project_id
AND deleted_flag ='0')
AND deleted_flag ='0'
RETURNING rule_id BULK COLLECT INTO v_cz_ids_tbl;
v_cz_ids_tbl.DELETE;
UPDATE cz_expression_nodes
SET template_id = 2
WHERE template_id = 5
AND rule_id IN
(SELECT rule_id
FROM cz_rules
WHERE devl_project_id = p_dev_project_id
AND deleted_flag ='0')
AND deleted_flag ='0'
RETURNING rule_id BULK COLLECT INTO v_cz_ids_tbl;
UPDATE cz_rules SET RULE_CLASS=RULE_CLASS_DEFAULT
WHERE rule_id=v_cz_ids_tbl(l_cz_rules_id) ;
v_cz_ids_tbl.DELETE;
UPDATE cz_expression_nodes
SET template_id = 552
WHERE template_id = 360
AND rule_id IN
(SELECT rule_id
FROM cz_rules
WHERE devl_project_id = p_dev_project_id
AND deleted_flag ='0')
AND deleted_flag ='0'
RETURNING rule_id BULK COLLECT INTO v_cz_ids_tbl;
v_cz_ids_tbl.DELETE;
SELECT cz_expression_nodes_s.nextval
INTO l_expression_node_id
FROM dual;
UPDATE cz_expression_nodes
SET template_id = 408,
expr_parent_id = l_expression_node_id
WHERE expr_node_id = l_atan.expr_node_id;
UPDATE CZ_RULES set presentation_flag=0 where rule_id in
(SELECT exp2.rule_id
FROM cz_expression_nodes exp1,
cz_expression_nodes exp2,
cz_ps_nodes psnode
WHERE exp1.deleted_flag ='0'
AND exp2.deleted_flag ='0'
AND exp1.template_id IN(1, 2, 3, 4, 5)
AND exp2.ps_node_id = psnode.ps_node_id
AND exp1.rule_id = exp2.rule_id
AND psnode.ps_node_type = PS_TYPE_FEATURE
AND psnode.feature_type = FEATURE_TYPE_INTEGER
AND psnode.minimum >= 0
AND psnode.devl_project_id = p_dev_project_id);
FOR c_cur IN (select 1 from cz_expression_nodes czr where czr.rule_id=l_numeric_participant.rule_id and czr.template_id=21) --Just to check that this is logic rule , will loop only once
LOOP
--make this as a statement rule
update cz_expression_nodes set expr_type=200 where expr_type=222 and rule_id=l_numeric_participant.rule_id;
select min(seq_nbr) ,max(seq_nbr) into minseq, maxseq from cz_expression_nodes where rule_id= l_numeric_participant.rule_id and template_id in (306,307);
update cz_expression_nodes set expr_parent_id=(select expr_node_id from cz_expression_nodes where rule_id= l_numeric_participant.rule_id and seq_nbr=minseq and template_id in (306,307))
where rule_id= l_numeric_participant.rule_id and seq_nbr between minseq+1 and maxseq-2 ;
update cz_expression_nodes set expr_parent_id=(select expr_node_id from cz_expression_nodes where rule_id= l_numeric_participant.rule_id and seq_nbr=maxseq and template_id in (306,307))
where rule_id= l_numeric_participant.rule_id and seq_nbr >maxseq;
update cz_expression_nodes set expr_parent_id=(select expr_node_id from cz_expression_nodes where rule_id= l_numeric_participant.rule_id and seq_nbr=maxseq-1 and template_id IN(1, 2, 3, 4, 5))
where rule_id= l_numeric_participant.rule_id and seq_nbr in (minseq,maxseq);
update cz_expression_nodes set expr_parent_id=null , seq_nbr=1 where expr_node_id=l_numeric_participant.exp1id;
SELECT cz_expression_nodes_s.nextval
INTO l_expression_node_id
FROM dual;
UPDATE cz_expression_nodes
SET expr_parent_id = l_expression_node_id,
seq_nbr = 1
WHERE expr_node_id = l_numeric_participant.exp2id;
SELECT cz_expression_nodes_s.nextval
INTO l_expression_node_id1
FROM dual;
SELECT name INTO l_ps_node_name from cz_ps_nodes where ps_node_id=l_numeric_participant.ps_node_id;
DELETE FROM cz_expression_nodes where rule_id= l_numeric_participant.rule_id and template_id=21;
UPDATE cz_expression_nodes
SET deleted_flag = '1'
WHERE expr_node_id
IN (
SELECT expr_node_id
FROM cz_rules czrules ,cz_expression_nodes EXP
WHERE rule_type = RULE_TYPE_CONFIGURATION_EXT
and expr_type = 216
AND EXP.rule_id = czrules.rule_id
AND EXP.argument_signature_id = 2204
AND czrules.deleted_flag ='0'
AND exp.deleted_flag ='0'
AND devl_project_id=p_dev_project_id
)
RETURNING rule_id BULK COLLECT INTO v_cz_ids_tbl ;
v_cz_ids_tbl.DELETE;
SELECT DISTINCT czrules.rule_id
BULK COLLECT INTO v_cz_ids_tbl
FROM cz_rules czrules,
cz_expression_nodes exp
WHERE rule_type = RULE_TYPE_CONFIGURATION_EXT
AND expr_type = 216
AND exp.rule_id = czrules.rule_id
AND czrules.deleted_flag = '0'
AND exp.deleted_flag = '0'
AND devl_project_id = p_dev_project_id;
v_cz_ids_tbl.DELETE;
SELECT czrules.rule_id,
expout.expr_node_id
FROM cz_rules czrules,
cz_expression_nodes expout
WHERE czrules.rule_type = RULE_TYPE_CONFIGURATION_EXT
AND czrules.rule_id = expout.rule_id
AND EXISTS
(SELECT 1
FROM cz_expression_nodes EXP
WHERE EXP.expr_type = 216
AND EXP.rule_id = czrules.rule_id
AND EXP.argument_signature_id IN(2209, --postInstanceAdd
2210, --postInstanceDelete
2215, --postConnect
2216, --postDisconnect
2217 --postValueChange
--todo to do find values for 'onValueBound'--'preInstanceDelete',--'preInstanceAdd',
)
AND EXP.deleted_flag ='0')
AND expout.deleted_flag ='0'
AND czrules.deleted_flag ='0'
AND devl_project_id = p_dev_project_id
)
LOOP
displayMessage(3,p_dev_project_id , 'RULE' ,v_rule_ids.rule_id,l_api_name,'CZ_CNV_ADV_CX_AUTOCOMPLETE');
SELECT ps_node_id
FROM cz_ps_nodes psout
WHERE ps_node_type = PS_TYPE_REFERENCE
AND devl_project_id = p_dev_project_id
AND instantiable_flag = NODE_INSTANTIABILITY_MULTIPLE
AND component_id IN
(SELECT ps_node_id
FROM cz_ps_nodes psin
WHERE ps_node_type = PS_TYPE_BOM_MODEL)
;
(SELECT ps_node_id,
ps_node_type,
feature_type,
nvl(initial_value,initial_num_value ) initial_value,
minimum,
maximum,
virtual_flag,name, ROWID
FROM cz_ps_nodes
WHERE(ps_node_type IN(PS_TYPE_FEATURE, PS_TYPE_TOTAL, PS_TYPE_RESOURCE, PS_TYPE_BOM_MODEL, PS_TYPE_BOM_OPTION_CLASS, PS_TYPE_BOM_STD_ITEM, PS_TYPE_OPTION)
OR(ps_node_type = PS_TYPE_COMPONENT AND virtual_flag = 1)) AND devl_project_id = p_dev_project_id)
LOOP
-- kdande; 09-Jan-2008; Bug 6722494
UPDATE cz_ps_nodes
SET user_input_required_flag = DECODE (v_ps_node.minimum, 1, '1', '0')
WHERE ROWID = v_ps_node.ROWID;
UPDATE cz_ps_nodes psout
SET maximum = NULL,
minimum = NULL
WHERE ps_node_type = PS_TYPE_REFERENCE
AND devl_project_id = p_dev_project_id
AND instantiable_flag = NODE_INSTANTIABILITY_MULTIPLE
AND component_id IN (SELECT ps_node_id
FROM cz_ps_nodes psin
WHERE ps_node_type = PS_TYPE_BOM_MODEL);
v_cz_expression_nodes.DELETE;
V_CZ_RULES.DELETE;
( SELECT remote_model_id
FROM cz_model_publications p , cz_pb_model_exports z
WHERE p.export_status IN('OK')
AND p.server_id = 0
AND p.publication_mode = 'M'
AND p.migration_group_id = p_model_conversion_set_id
AND z.publication_id = p.publication_id
AND z.model_id = p.object_id
AND z.server_id = 0
AND z.status = 'OK'
and p.source_target_flag='S'
AND p.deleted_flag='0'
)
LOOP
displayMessage(0,v_models.remote_model_id , NULL ,v_models.remote_model_id,l_api_name, 'Model record');
UPDATE cz_devl_projects
SET config_engine_type = 'F' ,post_migr_change_flag =NULL
WHERE devl_project_id = v_models.remote_model_id;
UPDATE cz_rules
SET config_engine_type = 'F' , rule_class=nvl(rule_class,0)
WHERE deleted_flag='0'
AND devl_project_id IN( SELECT remote_model_id
FROM cz_model_publications p , cz_pb_model_exports z
WHERE p.export_status IN('OK')
AND p.server_id = 0
AND p.publication_mode = 'M'
AND p.migration_group_id = p_model_conversion_set_id
AND z.publication_id = p.publication_id
AND z.model_id = p.object_id
AND z.server_id = 0
AND z.status = 'OK'
and p.source_target_flag='S'
AND p.deleted_flag='0');
( SELECT remote_model_id
FROM cz_model_publications p , cz_pb_model_exports z
WHERE p.export_status IN('OK')
AND p.server_id = 0
AND p.publication_mode = 'M'
AND p.migration_group_id = p_model_conversion_set_id
AND z.publication_id = p.publication_id
AND z.model_id = p.object_id
AND z.server_id = 0
AND z.status = 'OK'
and p.source_target_flag='S'
AND p.deleted_flag='0'
)
LOOP
--Clear Initial values for certain node types
clearinitialvalues(v_models.remote_model_id);
v_cz_expression_nodes.DELETE;
V_CZ_RULES.DELETE;
v_cz_expression_nodes.DELETE;
V_CZ_RULES.DELETE;
* Migration request is created by Developer and contains the list of all models selected
* for Migration from the source's Configurator Repository, target Instance name and
* target Repository Folder.
*/
PROCEDURE copy_model_for_conversion(errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
p_request_id IN NUMBER
) IS
l_status VARCHAR2(3);
FOR c_pub IN (SELECT publication_id ,max(node_depth) mdepth FROM cz_model_publications mp ,cz_model_ref_expls mr
WHERE mp.migration_group_id = p_request_id AND mp.deleted_flag = '0' and mr.deleted_flag = '0'
AND mp.publication_mode='M'
and mr.model_id =mp.object_id
group by publication_id
order by mdepth asc
)LOOP
l_mig_group_found :=TRUE;
SELECT COUNT(*)
INTO noofreports
FROM
(SELECT DISTINCT migration_group_id
FROM cz_model_publications
WHERE deleted_flag ='0'
AND publication_mode = 'M'
AND(migration_group_id = p_request_id OR(p_request_id IS NULL AND migration_group_id IS NOT NULL))
AND export_status = 'PEN'
AND server_id = 0);
SELECT DISTINCT migration_group_id
FROM cz_model_publications mp
WHERE deleted_flag ='0' and
publication_mode='M'
AND server_id = 0
AND export_status = 'PEN'
AND (migration_group_id = p_request_id
OR(P_request_id is null AND migration_group_id IS NOT NULL ))
) LOOP
BEGIN
l_migration_group_id:=c_model_conv.migration_group_id;
UPDATE cz_model_publications set export_status='OK' where export_status='PEN' and migration_group_id=c_model_conv.migration_group_id;
SELECT remote_model_id
FROM cz_model_publications p , cz_pb_model_exports z
WHERE p.export_status IN('OK')
AND p.server_id = 0
AND p.publication_mode = 'M'
AND p.migration_group_id = P_request_id
AND z.publication_id = p.publication_id
AND z.model_id = p.object_id
AND z.server_id = 0
AND z.status = 'OK'
and p.source_target_flag='S'
AND p.deleted_flag='0'
)LOOP
BEGIN
cz_developer_utils_pvt.delete_model(c_process.remote_model_id,L_RETURN_STATUS,L_MSG_COUNT,L_MSG_DATA);
UPDATE cz_model_publications SET export_status='ERR' WHERE migration_group_id = l_migration_group_id and server_id=0 ;
select name INTO l_qualified from cz_ui_defs where ui_def_id = (select max(ui_def_id) from cz_ui_page_elements where element_id = to_char(inParent_id));
FOR parent_node IN (SELECT distinct(name) , element_id FROM cz_ui_page_elements
WHERE deleted_flag = '0'
START WITH element_id = to_char(inParent_id)
CONNECT BY PRIOR parent_element_id=element_id
order by element_id ) LOOP
IF(LENGTH(parent_node.name) + LENGTH(l_qualified) + 1 < 2000) THEN
l_qualified := l_qualified||'.' || parent_node.name ;