1 PACKAGE BODY cz_model_convert AS
2 /* $Header: czmdlconb.pls 120.38.12010000.2 2009/02/06 17:53:45 vsingava ship $ */
3
4 g_pkg_name constant VARCHAR2(30) := 'cz_model_convert';
5 v_ndebug INTEGER := 1;
6 v_model_conversion_set_id NUMBER;
7
8 PS_TYPE_FEATURE CONSTANT NUMBER:= 261;
9 PS_TYPE_OPTION CONSTANT NUMBER:= 262;
10 PS_TYPE_COMPONENT CONSTANT NUMBER:= 259;
11 PS_TYPE_TOTAL CONSTANT NUMBER:= 272;
12 PS_TYPE_RESOURCE CONSTANT NUMBER:= 273;
13 PS_TYPE_BOM_MODEL CONSTANT NUMBER:= 436;
14 PS_TYPE_REFERENCE CONSTANT NUMBER:= 263;
15 PS_TYPE_BOM_OPTION_CLASS CONSTANT NUMBER:= 437;
16 PS_TYPE_BOM_STD_ITEM CONSTANT NUMBER:= 438;
17 RULE_CLASS_DEFAULT CONSTANT NUMBER:= 1;
18 RULE_CLASS_CONSTRAINT CONSTANT NUMBER:= 0;
19 FEATURE_TYPE_INTEGER CONSTANT NUMBER:= 1;
20 FEATURE_TYPE_FLOAT CONSTANT NUMBER:= 2;
21 FEATURE_TYPE_BOOLEAN CONSTANT NUMBER:= 3;
22 FEATURE_TYPE_TEXT CONSTANT NUMBER:= 4;
23 FEATURE_TYPE_LIST_OF_OPTIONS CONSTANT NUMBER:=0;
24 NODE_INSTANTIABILITY_MULTIPLE CONSTANT NUMBER:=4;
25 RULE_TYPE_COMPAT_TABLE CONSTANT NUMBER:=24;
26 RULE_TYPE_DESIGN_CHART_RULE CONSTANT NUMBER:=30;
27 RULE_TEMPLATE_FREEFORM_RULE CONSTANT NUMBER:=200;
28 RULE_TYPE_CONFIGURATION_EXT CONSTANT NUMBER:=300;
29
30 USE_BOM_DEFAULT_QTY VARCHAR2(6):='TRUE';
31 INTEGER_MIN_VAL NUMBER(10) := -2147483648;
32 SOLVER_MAX_DOUBLE NUMBER := 1E125;
33 INTEGER_MAX_VALUE NUMBER(10) := 2147483647;
34
35
36 OPERATOR_ADDSTO CONSTANT NUMBER:= 712;
37 OPERATOR_SUBTRACTSFROM CONSTANT NUMBER:= 714;
38
39 --Table to store the ID's of rows affected by an Update statement .
40 TYPE t_ref IS TABLE OF NUMBER
41 INDEX BY BINARY_INTEGER;
42
43 TYPE t_ref_num IS TABLE OF VARCHAR2(1000)
44 INDEX BY BINARY_INTEGER;
45
46 v_cz_num_tbl t_ref;
47 v_cz_ids_tbl t_ref;
48 v_cz_names_tbl t_ref_num;
49
50
51 TYPE t_cz_expression_nodes IS TABLE OF cz_expression_nodes%ROWTYPE INDEX BY BINARY_INTEGER;
52 v_cz_expression_nodes t_cz_expression_nodes;
53 v_cz_expr_node_count NUMBER(10);
54
55 TYPE t_cz_rules IS TABLE OF cz_rules%ROWTYPE INDEX BY BINARY_INTEGER;
56 v_cz_rules t_cz_rules ;
57 v_cz_rule_count NUMBER(10);
58
59
60 -- used for getting the next rule id since rule sequence is incremented by 20 every time
61 last_id_allocated NUMBER := NULL;
62 next_id_to_use NUMBER := 0;
63 CZ_SEQUENCE_INCREMENT number :=20;
64
65
66 --procedure to log debug messages to conc request output file / cz_db_logs
67 PROCEDURE log_msg(p_caller IN VARCHAR2, p_ndebug IN NUMBER, p_msg IN VARCHAR2, p_level IN NUMBER) IS
68 l_msg varchar2(2000):=NULL;
69 l_api_name constant VARCHAR2(30) := 'log_msg';
70 BEGIN
71 IF fnd_global.conc_request_id > 0 THEN
72 fnd_file.PUT_LINE(fnd_file.LOG, p_msg);
73 END IF;
74 cz_utils.log_report(g_pkg_name, p_caller, p_ndebug, p_msg, p_level);
75 EXCEPTION WHEN OTHERS THEN
76 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
77 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
78 RAISE;
79 END log_msg;
80
81 --procedure to log messages to cz_db_logs for use by the model conversion xml publisher report
82 PROCEDURE displayMessage(p_urgency In NUMBER,p_model_id IN NUMBER, p_object_type IN VARCHAR2 ,p_object_id IN NUMBER
83 ,p_caller IN VARCHAR2,msg VARCHAR2
84 ,token1 IN VARCHAR2 DEFAULT NULL, value1 IN VARCHAR2 DEFAULT NULL
85 ,token2 IN VARCHAR2 DEFAULT NULL, value2 IN VARCHAR2 DEFAULT NULL
86 ,token3 IN VARCHAR2 DEFAULT NULL, value3 IN VARCHAR2 DEFAULT NULL
87 ,p_element_id IN NUMBER DEFAULT NULL
88 ,p_element_type IN NUMBER DEFAULT NULL) AS
89 PRAGMA AUTONOMOUS_TRANSACTION;
90 l_msg varchar2(2000):=NULL;
91 l_api_name constant VARCHAR2(30) := 'displayMessage';
92 BEGIN
93 IF msg IS NOT NULL THEN
94 IF token3 IS NOT NULL THEN
95 l_msg:=cz_utils.get_text(msg,token1, value1,token2, value2,token3, value3);
96 ELSE IF token2 IS NOT NULL THEN
97 l_msg:=cz_utils.get_text(msg,token1, value1,token2, value2);
98
99 ELSE IF token1 IS NOT NULL THEN
100 l_msg:=cz_utils.get_text(msg,token1, value1);
101 ELSE
102 l_msg:=cz_utils.get_text(msg);
103 END IF;
104 END IF;
105 END IF;
106
107 INSERT INTO CZ_DB_LOGS (LOGTIME, LOGUSER, URGENCY, CALLER , MESSAGE, MODEL_ID , OBJECT_TYPE ,OBJECT_ID,MODEL_CONVERSION_SET_ID,ELEMENT_ID,ELEMENT_TYPE)
108 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);
109
110 END IF;
111 /*log_msg(p_caller , v_ndebug, l_msg, 1 );*/
112
113
114 COMMIT;
115
116 EXCEPTION WHEN OTHERS THEN
117 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
118 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
119 RAISE;
120 END;
121
122
123 FUNCTION findOrCreateRuleFolder(p_dev_project_id IN cz_devl_projects.devl_project_id%TYPE) RETURN cz_rules.rule_folder_id%TYPE AS
124 p_rule_folderid cz_rules.rule_folder_id%TYPE;
125 p_parent_rule_fldr cz_rules.rule_folder_id%TYPE;
126
127 CURSOR c_rule_fld IS
128 SELECT rule_folder_id
129 FROM cz_rule_folders
130 WHERE deleted_flag ='0'
131 AND name = 'Rules Generated by Model Conversion'
132 AND devl_project_id = p_dev_project_id;
133 l_msg VARCHAR2(2000);
134 l_api_name constant VARCHAR2(30) := 'findOrCreateRuleFolder';
135 BEGIN
136 OPEN c_rule_fld;
137 FETCH c_rule_fld
138 INTO p_rule_folderid;
139 CLOSE c_rule_fld;
140 IF p_rule_folderid IS NULL THEN
141 --Create a rule folder with name "Rules Generated by Model Conversion" populate rulefolderID
142 -- INSERTING into
143
144
145 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;
146
147
148 INSERT
149 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)
150 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)
151 RETURNING rule_folder_id INTO p_rule_folderid;
152
153 END IF;
154 RETURN p_rule_folderid;
155 EXCEPTION WHEN OTHERS THEN
156 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
157 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
158 RAISE;
159 END findOrCreateRuleFolder;
160
161 -- procedure to populate CZ tables with data in temporary tables v_cz_rules and v_cz_expression_nodes
162 PROCEDURE populate_cz_tables (commit_all boolean default false) AS
163 l_msg VARCHAR2(2000);
164 l_api_name constant VARCHAR2(30) := 'populate_cz_expression_table';
165 BEGIN
166
167 IF ((v_cz_rules.LAST IS NOT NULL and v_cz_rules.LAST >20) OR commit_all ) THEN
168
169 IF (v_cz_rules.LAST IS NOT NULL) THEN
170 for v_cz_rules_count IN v_cz_rules.FIRST .. v_cz_rules.LAST Loop
171 begin
172 INSERT INTO cz_localized_texts (localized_str, intl_text_id, deleted_flag, language, source_lang, model_id, seeded_flag, persistent_intl_text_id)
173 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);
174 exception when others then
175 null;
176 end;
177 END LOOP;
178 -- use FORALL statement for bulk insert.
179 forall v_cz_rules_count IN v_cz_rules.FIRST .. v_cz_rules.LAST
180 INSERT INTO cz_rules
181 VALUES v_cz_rules(v_cz_rules_count);
182 END IF;
183 -- use FORALL statement for bulk insert.
184 IF (v_cz_expression_nodes.LAST IS NOT NULL) THEN
185 for v_cz_expr_nodes_count IN v_cz_expression_nodes.FIRST .. v_cz_expression_nodes.LAST loop
186 v_cz_expression_nodes(v_cz_expr_nodes_count).seeded_flag:=0;
187 v_cz_expression_nodes(v_cz_expr_nodes_count).deleted_flag:=0;
188 end loop;
189 forall v_cz_expr_nodes_count IN v_cz_expression_nodes.FIRST .. v_cz_expression_nodes.LAST
190 INSERT INTO cz_expression_nodes
191 VALUES v_cz_expression_nodes(v_cz_expr_nodes_count);
192 v_cz_expression_nodes.DELETE;
193 END IF;
194 IF (v_cz_rules.LAST IS NOT NULL) THEN
195
196 FOR v_cz_rules_count IN v_cz_rules.FIRST .. v_cz_rules.LAST LOOP
197 IF v_cz_rules(v_cz_rules_count).presentation_flag=0 THEN
198 cz_rule_text_gen.parse_rules( v_cz_rules(v_cz_rules_count).devl_project_id, v_cz_rules(v_cz_rules_count).rule_id);
199 END IF;
200 END LOOP;
201 v_cz_rules.DELETE;
202 END IF;
203
204 v_cz_rule_count:=0;
205 v_cz_expr_node_count:=0;
206 END IF;
207
208
209
210 EXCEPTION
211 WHEN others THEN
212 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
213 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
214 RAISE;
215 END populate_cz_tables;
216
217
218
219 PROCEDURE UPG_UI_CONT_TYPE_TMPLS(p_devl_project_id IN NUMBER) IS
220 TYPE num_tbl_type IS TABLE OF NUMBER;
221 l_cont_types_tbl num_tbl_type := num_tbl_type(633,635,189,565,622, 638,636,637);
222 l_template_ids_tbl num_tbl_type := num_tbl_type(1009,1020,301,221,572, 1017,1016,1015);
223 l_msg VARCHAR2(2000);
224 l_api_name constant VARCHAR2(30) := 'UPG_UI_CONT_TYPE_TMPLS';
225 BEGIN
226
227 FOR i IN(SELECT ui_def_id FROM CZ_UI_DEFS
228 WHERE ui_style='7' AND seeded_flag='0' AND deleted_flag='0' and devl_project_id=p_devl_project_id)
229 LOOP
230 FORALL j IN 1..l_cont_types_tbl.COUNT
231 INSERT INTO CZ_UI_CONT_TYPE_TEMPLS
232 (
233 UI_DEF_ID
234 ,CONTENT_TYPE
235 ,TEMPLATE_ID
236 ,MASTER_TEMPLATE_FLAG
237 ,TEMPLATE_UI_DEF_ID
238 ,WRAP_TEMPLATE_FLAG
239 ,DELETED_FLAG
240 ,SEEDED_FLAG
241 )
242 SELECT
243 i.ui_def_id
244 ,l_cont_types_tbl(j)
245 ,l_template_ids_tbl(j)
246 ,'0'
247 ,0
248 ,'0'
249 ,'0'
250 ,'0'
251 FROM dual
252 WHERE NOT EXISTS
253 (SELECT NULL FROM CZ_UI_CONT_TYPE_TEMPLS
254 WHERE ui_def_id=i.ui_def_id AND content_type=l_cont_types_tbl(j));
255 END LOOP;
256
257
258
259 -- Get the cz_ui_cont_type_templs.template_Id for content_type 543
260 -- Create two records with template_id from i) and content_types 560 and 561
261 -- Delete the record with content_type 543
262 FOR c_ct IN(SELECT c.*
263 FROM cz_ui_defs ui,
264 cz_ui_cont_type_templs c
265 WHERE ui.deleted_flag = '0'
266 AND ui.devl_project_id = p_devl_project_id
267 AND c.ui_def_id = ui.ui_def_id
268 AND c.deleted_flag = '0'
269 AND content_type = 543
270 AND NOT EXISTS
271 (SELECT 1
272 FROM cz_ui_cont_type_templs
273 WHERE ui_def_id = ui.ui_def_id
274 AND content_type =560)) LOOP
275 INSERT INTO cz_ui_cont_type_templs (UI_DEF_ID,CONTENT_TYPE,TEMPLATE_ID,DELETED_FLAG,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
276 LAST_UPDATE_DATE,LAST_UPDATE_LOGIN,MASTER_TEMPLATE_FLAG,SEEDED_FLAG,TEMPLATE_UI_DEF_ID,WRAP_TEMPLATE_FLAG)
277 VALUES
278 (
279 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,
280 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
281 );
282
283 END LOOP;
284
285 UPDATE cz_ui_cont_type_templs uiout set content_type=561
286 WHERE
287 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)
288 AND deleted_flag = '0'
289 AND content_type = 543
290 AND NOT EXISTS
291 (SELECT NULL FROM CZ_UI_CONT_TYPE_TEMPLS
292 WHERE ui_def_id=uiout.ui_def_id AND content_type=561) ;
293
294 EXCEPTION WHEN OTHERS THEN
295 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
296 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
297 RAISE;
298
299 END UPG_UI_CONT_TYPE_TMPLS;
300
301
302
303 FUNCTION GET_UI_ELEMENT_ID(inPageElemID IN VARCHAR2) RETURN NUMBER is
304 l_qualified VARCHAR2(2000) := ' ';
305 count1 number:=0;
306
307 BEGIN
308 l_qualified:=inPageElemID;
309 count1:=instr(inPageElemID,'_') ;
310 while (count1>0 )loop
311 l_qualified:=substr(l_qualified,count1+1);
312 count1:=instr(l_qualified,'_');
313 end loop;
314
315
316 RETURN to_number(l_qualified);
317
318 EXCEPTION
319 WHEN others THEN
320 RAISE;
321 END GET_UI_ELEMENT_ID;
322
323
324
325
326 --This procedure removes the effectivity information from Total, Resource, Integer Feat, Decimal Feat, and Virtual Component.
327 PROCEDURE processUI(p_dev_project_id IN cz_devl_projects.devl_project_id%TYPE) AS
328 l_msg VARCHAR2(2000);
329 l_api_name constant VARCHAR2(30) := 'processUI';
330 BEGIN
331
332
333
334 --DHTML UI's will not be supported in fusion .Mark these as deleted
335
336 UPDATE cz_ui_defs SET deleted_flag='1' WHERE ui_style = 0
337 AND deleted_flag ='0'
338 AND devl_project_id =p_dev_project_id
339 RETURNING ui_def_id BULK COLLECT INTO v_cz_ids_tbl;
340
341 IF v_cz_ids_tbl.COUNT >0 THEN
342 FOR i in v_cz_ids_tbl.FIRST .. v_cz_ids_tbl.LAST
343 LOOP
344 displayMessage(2,p_dev_project_id , 'UI' ,v_cz_ids_tbl(i),l_api_name,'CZ_CNV_WARN_DHTML_DELETE');
345 END LOOP;
346 v_cz_ids_tbl.DELETE;
347 END IF;
348
349 --As Functional Companions are not supported in FCE models, Model Conversion should warn on detecting them.
350 --Bug 6489541
351 FOR v_ui IN(SELECT DISTINCT a.ui_def_id
352 FROM cz_ui_nodes a,
353 cz_ui_defs ui
354 WHERE a.deleted_flag = '0'
355 AND ui.deleted_flag = '0'
356 AND a.ui_def_id = ui.ui_def_id
357 AND ui.devl_project_id = p_dev_project_id
358 AND a.func_comp_id IS NOT NULL) LOOP
359 displayMessage(2,p_dev_project_id , 'UI' ,v_ui.ui_def_id,l_api_name,'CZ_CNV_WARN_FC_NOT_SUPPORTED');
360 END LOOP;
361
362
363
364 --Display message "UI will use the default Icons, Message Templates, and Utility Templates for functions specific to Fusion Engine. These can be overridden in the UI Definition."
365
366 FOR v_ui IN
367 (SELECT ui.ui_def_id
368 FROM cz_ui_defs ui
369 WHERE ui.deleted_flag ='0'
370 AND ui.devl_project_id = p_dev_project_id)
371 LOOP
372
373 displayMessage(3,p_dev_project_id , 'UI' ,v_ui.ui_def_id,l_api_name,'CZ_CNV_ADV_DEFAULT_UIS');
374 END LOOP;
375
376 --Update UI's to use the new content templates
377 UPG_UI_CONT_TYPE_TMPLS(p_dev_project_id);
378
379
380
381 FOR v_ui1 IN
382 (
383 SELECT DISTINCT * FROM(
384 SELECT text_str,
385 te.template_id,
386 'NodeUnsatisfied' lceprop,
387 'UserInputRequired' fceprop,
388 te.element_id ,
389 te.element_type
390 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
391 WHERE c.ui_def_id = ui.ui_def_id
392 AND c.template_id = t.template_id
393 AND c.content_type = s.signature_id
394 AND c.deleted_flag = '0'
395 AND t.deleted_flag = '0'
396 AND te.deleted_flag = '0'
400 and c.TEMPLATE_UI_DEF_ID = t.UI_DEF_ID
397 AND te.element_type = 8 -- is a text element
398 and text.ui_page_id=t.template_id AND UPPER(text_str) LIKE '%'||fnd_global.local_chr(38)||'NODEUNSATISFIED%'
399 and text.deleted_flag='0'
401 and s.signature_type IN ('SES','UCO')
402 AND t.ui_def_id=c.template_ui_def_id
403 and t.template_id=te.template_id
404 and te.element_id=text.intl_text_id
405 and ui.deleted_flag ='0'
406 and text.ui_def_id = t.ui_def_id
407 AND ui.devl_project_id = p_dev_project_id
408 AND t.ui_def_id=te.ui_def_id
409
410 UNION ALL
411 SELECT text_str,
412 te.template_id,
413 'Unsatisfied' lceprop,
414 'UserInputRequired' fceprop,
415 te.element_id ,
416 te.element_type
417 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
418 WHERE c.ui_def_id = ui.ui_def_id
419 AND c.template_id = t.template_id
420 AND c.content_type = s.signature_id
421 AND c.deleted_flag = '0'
422 AND t.deleted_flag = '0'
423 AND te.deleted_flag = '0'
424 AND te.element_type = 8 -- is a text element
425 and text.ui_page_id=t.template_id AND UPPER(text_str) LIKE '%'||fnd_global.local_chr(38)||'UNSATISFIED%'
426 and text.deleted_flag='0'
427 and c.TEMPLATE_UI_DEF_ID = t.UI_DEF_ID
428 and s.signature_type IN ('SES','UCO')
429 AND t.ui_def_id=c.template_ui_def_id
430 and t.template_id=te.template_id
431 and te.element_id=text.intl_text_id
432 and ui.deleted_flag ='0'
433 and text.ui_def_id = t.ui_def_id
434 AND ui.devl_project_id = p_dev_project_id
435 AND t.ui_def_id=te.ui_def_id
436
437 UNION ALL
438 SELECT text_str,
439 te.template_id,
440 'SubtreeUnsatisfied' lceprop,
441 'UserInputRequiredInSubtree' fceprop,
442 te.element_id ,
443 te.element_type
444 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
445 WHERE c.ui_def_id = ui.ui_def_id
446 AND c.template_id = t.template_id
447 AND c.content_type = s.signature_id
448 AND c.deleted_flag = '0'
449 AND t.deleted_flag = '0'
450 AND te.deleted_flag = '0'
451 AND te.element_type = 8 -- is a text element
452 and text.ui_page_id=t.template_id AND UPPER(text_str) LIKE '%'||fnd_global.local_chr(38)||'SUBTREEUNSATISFIED%'
453 and text.deleted_flag='0'
454 and c.TEMPLATE_UI_DEF_ID = t.UI_DEF_ID
455 and s.signature_type IN ('SES','UCO')
456 AND t.ui_def_id=c.template_ui_def_id
457 and t.template_id=te.template_id
458 and te.element_id=text.intl_text_id
459 and ui.deleted_flag ='0'
460 and text.ui_def_id = t.ui_def_id
461 AND ui.devl_project_id = p_dev_project_id
462 AND t.ui_def_id=te.ui_def_id
463 )
464
465 )
466 LOOP
467 --Refer TD section 4.1.5.2
468 --UI Has Text Expression that references a System Property that was redefined for FCE(
469 --Unsatisfied (session),NodeUnsatisfied,SubtreeUnsatisfied)
470 --Unsatisfied becomes UserInputRequired()
471 --SubtreeUnsatisfied becomes UserInputRequiredInSubtree
472 --Session.Unsatisfied.UserInputRequired
473 displayMessage(1,p_dev_project_id , 'TEMPLATE' ,v_ui1.template_id,l_api_name,'CZ_CNV_FAIL_REDEF_SYSPROP','LCEPROPERTYNAME',
474 v_ui1.lceprop,'EXPRESSION_OR_CONDITION' ,' Text Expression ' ,'FCEPROPERTYNAME' ,v_ui1.fceprop
475 ,v_ui1.element_id , v_ui1.element_type);
476 END LOOP;
477
478
479
480 FOR v_ui IN
481 (
482 SELECT DISTINCT tempel.element_id ,te.template_id,
483 decode(ex.template_id, 820, 'NodeUnsatisfied', 836, 'Unsatisfied', 849, 'SubtreeUnsatisfied') lceprop,
484 decode(ex.template_id, 820, 'InputRequired', 836, 'InputRequired', 849, 'InputRequiredInSubtree') fceprop,
485 ru.name,
486 ui.ui_def_id,
487 ru.ui_page_element_id,
488 tempel.element_type
489 FROM cz_expression_nodes ex,
490 cz_rules ru,
491 cz_ui_defs ui ,
492 cz_ui_cont_type_templs ct,
493 cz_ui_defs ui2,
494 cz_ui_templates te ,cz_ui_template_elements tempel
495 WHERE ex.template_id IN(820, 836, 849)
496 AND ex.deleted_flag = '0'
497 AND ex.rule_id = ru.rule_id
498 AND ru.deleted_flag = '0'
499 AND ui.deleted_flag = '0'
500 AND ui2.deleted_flag = '0'
501 AND ru.ui_def_id = ui.ui_def_id
502 AND ui.ui_def_id = te.ui_def_id
506 and tempel.template_id=te.template_id
503 and te.template_id=ct.template_id
504 and ct.ui_def_id=ui2.ui_def_id
505 and ui2.devl_project_id = p_dev_project_id
507 AND tempel.ui_def_id = te.ui_def_id
508
509 )
510 LOOP
511 --Refer TD section 4.1.5.2
512 --UI Has Display Condition that references a System Property that was redefined for FCE(
513 --Unsatisfied (session),NodeUnsatisfied,SubtreeUnsatisfied)
514 --Unsatisfied becomes UserInputRequired()
515 --SubtreeUnsatisfied becomes UserInputRequiredInSubtree
516 --Session.Unsatisfied UserInputRequired
517 displayMessage(1,p_dev_project_id , 'TEMPLATE' , v_ui.template_id,l_api_name,'CZ_CNV_FAIL_REDEF_SYSPROP','LCEPROPERTYNAME',
518 v_ui.lceprop,'EXPRESSION_OR_CONDITION' , ' Display Condition ' ,'FCEPROPERTYNAME' ,v_ui.fceprop ,
519 v_ui.element_id ,v_ui.element_type );
520 END LOOP;
521
522
523
524
525
526 --for each converted UI. Conversion Advisory message is to be displayed
527 --refer section 4.1.5.1
528
529
530 FOR v_ui IN
531 (
532 SELECT text_str,
533 cz_intl_texts.ui_def_id,
534 'NodeUnsatisfied' lceprop,
535 'UserInputRequired' fceprop
536 FROM cz_intl_texts,cz_ui_defs
537 WHERE UPPER(text_str) LIKE '%'||fnd_global.local_chr(38)||'NODEUNSATISFIED%'
538 AND model_id = p_dev_project_id
539 AND cz_intl_texts.ui_def_id=cz_ui_defs.ui_def_id
540 AND cz_ui_defs.devl_project_id=p_dev_project_id
541 AND cz_ui_defs.deleted_flag='0'
542
543 UNION ALL
544 SELECT text_str,
545 cz_intl_texts.ui_def_id,
546 'Unsatisfied' lceprop,
547 'UserInputRequired' fceprop
548 FROM cz_intl_texts,cz_ui_defs
549 WHERE UPPER(text_str) LIKE '%'||fnd_global.local_chr(38)||'UNSATISFIED%'
550 AND model_id = p_dev_project_id
551 AND cz_intl_texts.ui_def_id=cz_ui_defs.ui_def_id
552 AND cz_ui_defs.devl_project_id=p_dev_project_id
553 AND cz_ui_defs.deleted_flag='0'
554
555 UNION ALL
556 SELECT text_str,
557 cz_intl_texts.ui_def_id,
558 'SubtreeUnsatisfied' lceprop,
559 'UserInputRequiredInSubtree' fceprop
560 FROM cz_intl_texts,cz_ui_defs
561 WHERE UPPER(text_str) LIKE '%'||fnd_global.local_chr(38)||'SUBTREEUNSATISFIED%'
562 AND model_id = p_dev_project_id
563 AND cz_intl_texts.ui_def_id=cz_ui_defs.ui_def_id
564 AND cz_ui_defs.devl_project_id=p_dev_project_id
565 AND cz_ui_defs.deleted_flag='0'
566
567 )
568 LOOP
569 --Refer TD section 4.1.5.2
570 --UI Has Text Expression that references a System Property that was redefined for FCE(
571 --Unsatisfied (session),NodeUnsatisfied,SubtreeUnsatisfied)
572 --Unsatisfied becomes UserInputRequired()
573 --SubtreeUnsatisfied becomes UserInputRequiredInSubtree
574 --Session.Unsatisfied.UserInputRequired
575
576 --todo to do check this message and see element description .
577 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);
578 END LOOP;
579
580
581 Update cz_localized_texts
582 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%'
583 ,fnd_global.local_chr(38)||'USERINPUTREQUIRED'),fnd_global.local_chr(38)||'SUBTREEUNSATISFIED%',fnd_global.local_chr(38)||'USERINPUTREQUIREDINSUBTREE')
584 WHERE UPPER(localized_str) LIKE '%'||fnd_global.local_chr(38)||'%UNSATISFIED%'
585 AND model_id = p_dev_project_id
586 AND ui_def_id IN ( SELECT ui_def_id FROM cz_ui_defs
587 WHERE deleted_flag='0' AND cz_localized_texts.ui_def_id = ui_def_id
588 AND devl_project_id=p_dev_project_id
589 )
590 AND deleted_flag='0';
591
592
593
594 FOR v_ui IN
595 (SELECT DISTINCT template_id , decode(template_id,820,'NodeUnsatisfied' ,836,'Unsatisfied',849,'SubtreeUnsatisfied') lceprop ,
596 decode(template_id,820,'InputRequired' ,836,'InputRequired',849,'InputRequiredInSubtree') fceprop,ru.name ,ui.ui_def_id, ru.ui_page_element_id
597 FROM cz_expression_nodes ex,
598 cz_rules ru,
599 cz_ui_defs ui
600 WHERE template_id IN(820, 836, 849)
601 AND ex.deleted_flag ='0'
602 AND ex.rule_id = ru.rule_id
603 AND ru.deleted_flag ='0'
604 AND ui.deleted_flag ='0'
605 AND ru.ui_def_id = ui.ui_def_id
606 AND ui.devl_project_id = p_dev_project_id
607 )
608 LOOP
609 --Refer TD section 4.1.5.2
610 --UI Has Display Condition that references a System Property that was redefined for FCE(
611 --Unsatisfied (session),NodeUnsatisfied,SubtreeUnsatisfied)
612 --Unsatisfied becomes UserInputRequired()
613 --SubtreeUnsatisfied becomes UserInputRequiredInSubtree
614 --Session.Unsatisfied UserInputRequired
618
615 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);
616 END LOOP;
617
619 /*
620 Mappings Table for System Properties
621
622 OLD ID New ID OLD Property Name New Property Name
623 894 979 MaxConnections DefinitionMaxConnections
624 818 977 MaxInstances DefinitionMaxInstances
625 812 899 MaxValue DefinitionMaxValue
626 847 898 MaxValue DefinitionMaxValue
627 893 978 MinConnections DefinitionMinConnections
628 817 976 MinInstances DefinitionMinInstances
629 811 897 MinValue DefinitionMinValue
630 846 896 MinValue DefinitionMinValue
631 813 971 MinQuantity DefinitionMinQuantity
632 814 973 MaxQuantity DefinitionMaxQuantity
633 815 974 MinSelected DefinitionMinSelections
634 816 975 MaxSelected DefinitionMaxSelections
635
636 */
637
638
639 FOR v_ui IN
640 (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')
641 ,'DefinitionMinSelected','DefinitionMinSelections') ruleName,ui.ui_def_id , ru.ui_page_element_id
642 FROM cz_expression_nodes ex,
643 cz_rules ru,
644 cz_ui_defs ui
645 WHERE template_id IN (894,818,812,847,893,817,811,846,813,814,815,816)
646 AND ex.deleted_flag ='0'
647 AND ex.rule_id = ru.rule_id
648 AND ru.deleted_flag ='0'
649 AND ui.deleted_flag ='0'
650 AND ru.ui_def_id = ui.ui_def_id
651 AND ui.devl_project_id = p_dev_project_id
652 )
653 LOOP
654 --Refer TD section 4.1.5.2
655 --If this ui element has a display condition / text expression which reference Min or Max system properties
656 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'
657 , v_ui.name , 'EXPRESSION_OR_CONDITION', ' Display Condition ' ,'PROPERTYNAME_2', 'Definition'||v_ui.rulename);
658 END LOOP;
659
660
661 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)
662 WHERE expr_node_id IN
663 (
664 SELECT ex.expr_node_id
665 FROM cz_expression_nodes ex,
666 cz_rules ru,
667 cz_ui_defs ui
668 WHERE template_id IN (894,818,812,847,893,817,811,846,813,814,815,816)
669 AND ex.deleted_flag ='0'
670 AND ex.rule_id = ru.rule_id
671 AND ru.deleted_flag ='0'
672 AND ui.deleted_flag ='0'
673 AND ru.ui_def_id = ui.ui_def_id
674 AND ui.devl_project_id = p_dev_project_id
675 );
676
677
678
679
680
681 FOR v_ui IN
682 (select intl.ui_def_id , ru.name lceprop , REPLACE(REPLACE('Definition'||ru.name ,'DefinitionMaxSelected','DefinitionMaxSelections'),'DefinitionMinSelected','DefinitionMinSelections') fceprop, intl.ui_page_element_id
683 from cz_intl_texts intl, cz_ui_defs ui , cz_rules ru where
684 ru.rule_id in (894,818,812,893,817,811,813,814,815,816)
685 and text_str like '%'||fnd_global.local_chr(38)||upper(ru.name)||'%'
686 and intl.ui_def_id = ui.ui_def_id
687 and intl.deleted_flag='0'
688 and ui.deleted_flag='0'
689 and intl.model_id =ui.devl_project_id
690 and ui.devl_project_id= p_dev_project_id
691 )
692 LOOP
693 --Refer TD section 4.1.5.2
694 --If this ui element has a display condition / text expression which reference Min or Max system properties
695 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);
696 END LOOP;
697
698
699 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')
700 ,' '||fnd_global.local_chr(38)||'MAX' , ' '||fnd_global.local_chr(38)||'DEFINITIONMAX'),'DEFINITIONMAXSELECTED','DEFINITIONMAXSELECTIONS'),'DEFINITIONMINSELECTED','DEFINITIONMINSELECTIONS')
701 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 )
702 AND model_id = p_dev_project_id
703 AND EXISTS (
704 SELECT 1 FROM cz_rules WHERE rule_id IN (894,818,812,893,817,811,813,814,815,816)
705 AND localized_str LIKE '%'||fnd_global.local_chr(38)||upper(name)||'%' )
706 AND deleted_flag='0';
707
708
709
710
711 EXCEPTION
712 WHEN others THEN
713 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
714 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
715 RAISE;
716 END processUI;
717
718
719 --This procedure removes the effectivity information from Total, Resource, Integer Feat, Decimal Feat, and Virtual Component.
723 BEGIN
720 PROCEDURE removeEffectivityInfo(p_dev_project_id IN cz_devl_projects.devl_project_id%TYPE) AS
721 l_msg VARCHAR2(2000);
722 l_api_name constant VARCHAR2(30) := 'removeEffectivityInfo';
724
725
726 UPDATE cz_ps_nodes
727 SET effective_usage_mask = '0000000000000000',
728 effective_from = cz_utils.epoch_begin,
729 effective_until = cz_utils.epoch_end,
730 effectivity_set_id = NULL,
731 eff_from = NULL,
732 eff_to = NULL,
733 eff_mask = NULL
734 WHERE(ps_node_type IN(PS_TYPE_TOTAL, PS_TYPE_RESOURCE) OR(ps_node_type = PS_TYPE_FEATURE
735 AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT)) OR(ps_node_type = PS_TYPE_COMPONENT AND virtual_flag = 1))
736 AND devl_project_id = p_dev_project_id
737 AND (effective_usage_mask <> '0000000000000000' or
738 effective_from IS NOT NULL or
739 effective_until IS NOT NULL or
740 effectivity_set_id IS NOT NULL or
741 eff_from IS NOT NULL or
742 eff_to IS NOT NULL or
743 eff_mask IS NOT NULL)
744 RETURNING ps_node_id , name BULK COLLECT INTO v_cz_ids_tbl ,v_cz_names_tbl;
745
746 IF v_cz_ids_tbl.COUNT >0 THEN
747 FOR i in v_cz_ids_tbl.FIRST .. v_cz_ids_tbl.LAST
748 LOOP
749 --For each of these nodes the effectivity will be removed , display appropriate message.
750 displayMessage(2,p_dev_project_id , 'NODE' ,v_cz_ids_tbl(i),l_api_name,'CZ_CNV_WARN_EFF_REMOVED','NODETYPE',v_cz_names_tbl(i) );
751 END LOOP;
752 v_cz_ids_tbl.DELETE;
753 v_cz_names_tbl.DELETE;
754 END IF;
755
756
757 EXCEPTION
758 WHEN others THEN
759 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
760 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
761 RAISE;
762 END removeEffectivityInfo;
763
764
765
766
767
768 -- function to get rule id values . As cz_rules_s sequence is incremented by 20 everytime hence we need this function
769 FUNCTION next_rule_id RETURN NUMBER IS
770 id_to_return NUMBER;
771 BEGIN
772 IF((last_id_allocated IS NULL) OR
773 (next_id_to_use = (NVL(last_id_allocated, 0) + CZ_SEQUENCE_INCREMENT)))THEN
774
775 SELECT cz_rules_s.NEXTVAL INTO last_id_allocated FROM DUAL;
776 next_id_to_use := last_id_allocated;
777 END IF;
778
779 id_to_return := next_id_to_use;
780 next_id_to_use := next_id_to_use + 1;
781 RETURN id_to_return;
782 END next_rule_id;
783
784
785
786 FUNCTION createConstraintRuleRecord(p_rulefolderid IN cz_rules.rule_folder_id%TYPE, p_ps_node_id IN cz_ps_nodes.ps_node_id%TYPE,
787 p_devl_project_id IN cz_devl_projects.devl_project_id%TYPE , p_default_rule boolean DEFAULT FALSE) RETURN cz_rules.rule_id%TYPE AS
788 l_rule_id cz_rules.rule_id%TYPE;
789 l_msg VARCHAR2(2000);
790 l_api_name constant VARCHAR2(30) := 'createConstraintRuleRecord';
791 l_ps_node_name cz_ps_nodes.name%type;
792 l_intl_text_id CZ_LOCALIZED_TEXTS.intl_text_id%type;
793 initialvalue varchar2(2000);
794 BEGIN
795
796 l_rule_id:=next_rule_id;
797
798 -- kdande; 10-Jan-2008; Bug 6730553; Changed cz_localized_texts_s to cz_intl_texts_s as cz_localized_texts_s is obsolete.
799 SELECT cz_intl_texts_s.nextval INTO l_intl_text_id FROM Dual ;
800
801 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;
802
803 -- INSERTING into cz_rules
804
805 v_cz_rule_count:=v_cz_rule_count+1;
806 V_CZ_RULES(v_cz_rule_count).rule_id:= l_rule_id;
807 V_CZ_RULES(v_cz_rule_count).reason_id:= l_intl_text_id;
808 IF p_rulefolderid IS NULL THEN
809 V_CZ_RULES(v_cz_rule_count).rule_folder_id:=findOrCreateRuleFolder(p_devl_project_id) ;
810 ELSE
811 V_CZ_RULES(v_cz_rule_count).rule_folder_id:= p_rulefolderid ;
812 END IF;
813 V_CZ_RULES(v_cz_rule_count).devl_project_id:= p_devl_project_id;
814 V_CZ_RULES(v_cz_rule_count).invalid_flag:= '0';
815 IF p_default_rule THEN
816 V_CZ_RULES(v_cz_rule_count).name:= 'Defaults-' || l_ps_node_name;
817 ELSE
818 V_CZ_RULES(v_cz_rule_count).name:= 'Constraint-' || l_ps_node_name;
819 END IF;
820 V_CZ_RULES(v_cz_rule_count).rule_type:= '200';
821 V_CZ_RULES(v_cz_rule_count).reason_type:= '0' ;
822 V_CZ_RULES(v_cz_rule_count).disabled_flag:= '0' ;
823 V_CZ_RULES(v_cz_rule_count).deleted_flag:= '0' ;
824 V_CZ_RULES(v_cz_rule_count).effective_usage_mask:='0000000000000000';
825 V_CZ_RULES(v_cz_rule_count).seq_nbr:= '1';
826 V_CZ_RULES(v_cz_rule_count).effective_from:= cz_utils.epoch_begin;
827 V_CZ_RULES(v_cz_rule_count).effective_until:= cz_utils.epoch_end;
828 V_CZ_RULES(v_cz_rule_count).persistent_rule_id:=l_rule_id;
829 V_CZ_RULES(v_cz_rule_count).presentation_flag:= '0';
830 V_CZ_RULES(v_cz_rule_count).mutable_flag:= '0';
831 V_CZ_RULES(v_cz_rule_count).seeded_flag:= '0';
832
833 -- INSERTING into cz_rules -- rule folder entry
834
835 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)
839
836 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 );
837
838 populate_cz_tables();
840 RETURN l_rule_id;
841 EXCEPTION
842 WHEN others THEN
843 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
844 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
845 RAISE;
846 END;
847
848
849 --Create implies rule for initial values on Boolean features .refer section 4..1.3.3 for Boolean features
850 PROCEDURE insertImpliesRuleRecords(dev_project_id IN cz_devl_projects.devl_project_id%TYPE, ps_node_id IN cz_ps_nodes.ps_node_id%TYPE,
851 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
852 l_msg VARCHAR2(2000);
853 l_api_name constant VARCHAR2(30) := 'insertImpliesRuleRecords';
854 l_expression_node_id cz_expression_nodes.expr_node_id%TYPE;
855 l_expression_node_id1 cz_expression_nodes.expr_node_id%TYPE;
856 l_mod_ref cz_expression_nodes.MODEL_REF_EXPL_ID%TYPE;
857 BEGIN
858
859 SELECT cz_expression_nodes_s.nextval
860 INTO l_expression_node_id
861 FROM dual;
862 v_cz_expr_node_count := v_cz_expr_node_count + 1;
863 --insert the implies record
864 -- INSERTING into cz_expression_nodes
865 v_cz_expression_nodes(v_cz_expr_node_count).expr_node_id := l_expression_node_id;
866 v_cz_expression_nodes(v_cz_expr_node_count).seq_nbr := 1;
867 v_cz_expression_nodes(v_cz_expr_node_count).expr_type := 200;
868 v_cz_expression_nodes(v_cz_expr_node_count).token_list_seq := 8;
869 v_cz_expression_nodes(v_cz_expr_node_count).rule_id := l_rule_id;
870 v_cz_expression_nodes(v_cz_expr_node_count).template_id := 1;
871 v_cz_expression_nodes(v_cz_expr_node_count).data_type := 0;
872 v_cz_expression_nodes(v_cz_expr_node_count).collection_flag := 0;
873 v_cz_expression_nodes(v_cz_expr_node_count).mutable_flag := 0;
874
875 -- v_cz_expression_nodes(v_cz_expr_node_count).source_offset := 7;
876 -- v_cz_expression_nodes(v_cz_expr_node_count).source_length := 7;
877
878
879 v_cz_expr_node_count := v_cz_expr_node_count + 1;
880 --insert initial value record
881 SELECT cz_expression_nodes_s.nextval
882 INTO l_expression_node_id1
883 FROM dual;
884 v_cz_expression_nodes(v_cz_expr_node_count).expr_node_id := l_expression_node_id1;
885 v_cz_expression_nodes(v_cz_expr_node_count).seq_nbr := 1;
886 v_cz_expression_nodes(v_cz_expr_node_count).expr_parent_id := l_expression_node_id;
887 v_cz_expression_nodes(v_cz_expr_node_count).data_value := initialvalue;
888 v_cz_expression_nodes(v_cz_expr_node_count).expr_type := 201;
889 v_cz_expression_nodes(v_cz_expr_node_count).token_list_seq := 7;
890 v_cz_expression_nodes(v_cz_expr_node_count).rule_id := l_rule_id;
891 v_cz_expression_nodes(v_cz_expr_node_count).param_signature_id := 81;
892 v_cz_expression_nodes(v_cz_expr_node_count).param_index := 1;
893 v_cz_expression_nodes(v_cz_expr_node_count).data_type := 3;
894 v_cz_expression_nodes(v_cz_expr_node_count).mutable_flag := 0;
895 -- v_cz_expression_nodes(v_cz_expr_node_count).source_offset := 1;
896 -- v_cz_expression_nodes(v_cz_expr_node_count).source_length := 4;
897
898 v_cz_expr_node_count := v_cz_expr_node_count + 1;
899 --insert boolean feature record
900 SELECT cz_expression_nodes_s.nextval
901 INTO l_expression_node_id1
902 FROM dual;
903 v_cz_expression_nodes(v_cz_expr_node_count).expr_node_id := l_expression_node_id1;
904 v_cz_expression_nodes(v_cz_expr_node_count).seq_nbr := 2;
905 v_cz_expression_nodes(v_cz_expr_node_count).ps_node_id := ps_node_id;
906 v_cz_expression_nodes(v_cz_expr_node_count).expr_parent_id := l_expression_node_id;
907 v_cz_expression_nodes(v_cz_expr_node_count).expr_type := 205;
908 v_cz_expression_nodes(v_cz_expr_node_count).token_list_seq := 15;
909 v_cz_expression_nodes(v_cz_expr_node_count).rule_id := l_rule_id;
910 v_cz_expression_nodes(v_cz_expr_node_count).param_signature_id := 81;
911 v_cz_expression_nodes(v_cz_expr_node_count).param_index := 2;
912 v_cz_expression_nodes(v_cz_expr_node_count).data_type := 502;
913 v_cz_expression_nodes(v_cz_expr_node_count).display_node_depth := 1;
914 v_cz_expression_nodes(v_cz_expr_node_count).mutable_flag := '1';
915 -- v_cz_expression_nodes(v_cz_expr_node_count).source_offset := 15;
916 -- v_cz_expression_nodes(v_cz_expr_node_count).source_length := 24;
917
918 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';
919 v_cz_expression_nodes(v_cz_expr_node_count).model_ref_expl_id := l_mod_ref;
920
921 --Mark the rule as a default rule
922 populate_cz_tables(true);
923 UPDATE cz_rules
924 SET rule_class = RULE_CLASS_DEFAULT
925 WHERE rule_id = l_rule_id;
926
927
928
929 EXCEPTION
930 WHEN others THEN
931 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
932 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
933 RAISE;
934 END insertImpliesRuleRecords;
935
936
937 --Procedure to create the expression tree for a contribute/consume target
941 ps_node_type IN cz_ps_nodes.ps_node_type%TYPE, initialvalue IN cz_ps_nodes.initial_value%TYPE, minvalue IN cz_ps_nodes.minimum%TYPE,
938 --This procedure adds an ADDTO rule for a contribute/consume rule where the initial value of target is not null
939
940 PROCEDURE createAccumulatorRule(dev_project_id IN cz_devl_projects.devl_project_id%TYPE, ps_node_id IN cz_ps_nodes.ps_node_id%TYPE,
942 l_rule_id IN cz_rules.rule_id%TYPE ,p_model_ref_expl_id IN cz_expression_nodes.model_ref_expl_id%TYPE,p_feature_type IN cz_ps_nodes.feature_type%TYPE) AS
943 l_msg VARCHAR2(2000);
944 l_api_name constant VARCHAR2(30) := 'createAccumulatorRule';
945 l_expression_node_id cz_expression_nodes.expr_node_id%TYPE;
946 l_expression_node_id1 cz_expression_nodes.expr_node_id%TYPE;
947 l_mod_ref cz_expression_nodes.MODEL_REF_EXPL_ID%type;
948 l_ps_node_id cz_ps_nodes.ps_node_id%TYPE;
949 BEGIN
950 IF initialvalue IS NOT NULL THEN
951
952 -- Insert AddTo expression record;
953
954 SELECT cz_expression_nodes_s.nextval INTO l_expression_node_id FROM dual;
955 v_cz_expr_node_count:=v_cz_expr_node_count+1;
956
957 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_NODE_ID:= l_expression_node_id ;
958 v_cz_expression_nodes(v_cz_expr_node_count).SEQ_NBR:= '1' ;
959 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_TYPE:= '200' ;
960 v_cz_expression_nodes(v_cz_expr_node_count).TOKEN_LIST_SEQ:= '1' ;
961 v_cz_expression_nodes(v_cz_expr_node_count).DELETED_FLAG:= '0' ;
962 v_cz_expression_nodes(v_cz_expr_node_count).RULE_ID:= l_rule_id ;
963 v_cz_expression_nodes(v_cz_expr_node_count).TEMPLATE_ID:= OPERATOR_ADDSTO ;
964 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE:= '0' ;
965 v_cz_expression_nodes(v_cz_expr_node_count).COLLECTION_FLAG:= '0' ;
966 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_OFFSET:= '1' ;
967 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_LENGTH:= '10' ;
968 v_cz_expression_nodes(v_cz_expr_node_count).MUTABLE_FLAG:= '0' ;
969 -- Insert initial value record;
970
971
972 SELECT cz_expression_nodes_s.nextval INTO l_expression_node_id1 FROM dual;
973 v_cz_expr_node_count:=v_cz_expr_node_count+1;
974
975
976
977 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_NODE_ID:= l_expression_node_id1 ;
978 v_cz_expression_nodes(v_cz_expr_node_count).SEQ_NBR:= '1' ;
979 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_PARENT_ID:= l_expression_node_id ;
980 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_TYPE:= '201' ;
981 v_cz_expression_nodes(v_cz_expr_node_count).TOKEN_LIST_SEQ:= '8' ;
982 v_cz_expression_nodes(v_cz_expr_node_count).DELETED_FLAG:= '0' ;
983 v_cz_expression_nodes(v_cz_expr_node_count).RULE_ID:= l_rule_id ;
984 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_SIGNATURE_ID:='96' ;
985 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_INDEX:= '1' ;
986
987 IF p_feature_type=FEATURE_TYPE_FLOAT THEN
988 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE:= '2' ;
989 ELSE
990 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE:= '1' ;
991 END IF;
992
993 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_OFFSET:= '13' ;
994 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_LENGTH:= '1' ;
995 v_cz_expression_nodes(v_cz_expr_node_count).MUTABLE_FLAG:= '0' ;
996 v_cz_expression_nodes(v_cz_expr_node_count).DATA_NUM_VALUE:= initialvalue ;
997 -- Insert record for ps_node_id, ps_node_type;
998
999 SELECT cz_expression_nodes_s.nextval INTO l_expression_node_id1 FROM dual;
1000 v_cz_expr_node_count:=v_cz_expr_node_count+1;
1001
1002 IF p_model_ref_expl_id IS NULL THEN
1003 l_ps_node_id :=ps_node_id;
1004 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) ;
1005 ELSE
1006 l_mod_ref:=p_model_ref_expl_id; -- use model_ref_expl_id from parent contribute / consume rule .
1007 END IF;
1008
1009
1010
1011 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_NODE_ID:= l_expression_node_id1 ;
1012 v_cz_expression_nodes(v_cz_expr_node_count).SEQ_NBR:= '2' ;
1013 v_cz_expression_nodes(v_cz_expr_node_count).PS_NODE_ID:= ps_node_id ;
1014 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_PARENT_ID:= l_expression_node_id ;
1015 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_TYPE:= '205' ;
1019 v_cz_expression_nodes(v_cz_expr_node_count).RULE_ID:= l_rule_id ;
1016 v_cz_expression_nodes(v_cz_expr_node_count).TOKEN_LIST_SEQ:= '9' ;
1017 v_cz_expression_nodes(v_cz_expr_node_count).DELETED_FLAG:= '0' ;
1018 v_cz_expression_nodes(v_cz_expr_node_count).MODEL_REF_EXPL_ID:= l_mod_ref ;
1020 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_SIGNATURE_ID:='96' ;
1021 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_INDEX:= '2' ;
1022
1023 IF ps_node_type=PS_TYPE_TOTAL THEN
1024 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE:=CZ_TYPES.TOTAL_TYPEID;
1025 ELSIF ps_node_type=PS_TYPE_RESOURCE THEN
1026 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE:=CZ_TYPES.RESOURCE_TYPEID;
1027 ELSIF ps_node_type=PS_TYPE_FEATURE and p_feature_type=FEATURE_TYPE_FLOAT THEN
1028 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE:=CZ_TYPES.DECIMAL_FEATURE_TYPEID;
1029 END IF;
1030
1031 v_cz_expression_nodes(v_cz_expr_node_count).DISPLAY_NODE_DEPTH:='1' ;
1032 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_OFFSET:= '18' ;
1033 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_LENGTH:= '14' ;
1034 v_cz_expression_nodes(v_cz_expr_node_count).MUTABLE_FLAG:= '1' ;
1035
1036
1037 END IF;
1038 EXCEPTION
1039 WHEN others THEN
1040 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
1041 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
1042 RAISE;
1043 END createAccumulatorRule;
1044
1045
1046 --Procedure to create accumulator rule records for contribute/consume rule initial values
1047 PROCEDURE createRules(p_devl_project_id IN cz_devl_projects.devl_project_id%TYPE, p_ps_node_id IN cz_ps_nodes.ps_node_id%TYPE,
1048 p_ps_node_type IN cz_ps_nodes.ps_node_type%TYPE, feature_type IN cz_ps_nodes.feature_type%TYPE,
1049 initialvalue IN cz_ps_nodes.initial_value%TYPE, minvalue IN cz_ps_nodes.maximum%TYPE, rulefolderid IN cz_rules.rule_folder_id%TYPE,
1050 maxvalue IN cz_ps_nodes.maximum%TYPE) AS
1051 --cursor to find all contribute/consume sources for a given target
1052 CURSOR c_contrib_consume IS
1053 SELECT DISTINCT r.devl_project_id,
1054 e.template_id,
1055 e1.rule_id,
1056 r.rule_folder_id,
1057 ps.name,
1058 p.name devname,
1059 r.reason_id,
1060 e1.model_ref_expl_id
1061 FROM cz_rules r,
1062 cz_devl_projects p,
1063 cz_expression_nodes e,
1064 cz_expression_nodes e1,
1065 cz_ps_nodes ps
1066 WHERE r.deleted_flag = '0'
1067 AND e.deleted_flag = '0'
1068 AND e1.deleted_flag = '0'
1069 AND p.deleted_flag = '0'
1070 AND p.config_engine_type = 'F'
1071 AND e.template_id IN(708, 710 ,712 ,714)
1072 AND e1.ps_node_id = p_ps_node_id
1073 AND e1.ps_node_id = ps.ps_node_id
1074 AND e1.rule_id = e.rule_id
1075 AND r.rule_id = e.rule_id
1076 AND p.devl_project_id=ps.devl_project_id
1077 AND p.devl_project_id = p_devl_project_id
1078 ORDER BY r.devl_project_id , e1.model_ref_expl_id;
1079
1080 l_msg VARCHAR2(2000);
1081 l_api_name constant VARCHAR2(30) := 'createRules';
1082 l_rule_id cz_rules.rule_id%TYPE;
1083 l_rulefolderid cz_rules.rule_folder_id%TYPE;
1084 l_rule_class cz_rules.rule_class%TYPE;
1085 l_ps_node_name cz_ps_nodes.name%type;
1086 l_previous_devl_project_id cz_devl_projects.devl_project_id%type;
1087 l_has_mult_down_cont_cons boolean;
1088
1089 l_devl_project_id cz_devl_projects.devl_project_id%type;
1090 l_model_ref_expl_id cz_expression_nodes.model_ref_expl_id%type;
1091
1092
1093 ACC_RULE_CREATED boolean;
1094
1095
1096 BEGIN
1097
1098 ACC_RULE_CREATED:=FALSE;
1099 l_has_mult_down_cont_cons :=FALSE;
1100
1101 FOR v_contribute_consume IN c_contrib_consume
1102 LOOP
1103 displayMessage(3,p_devl_project_id , 'RULE' ,v_contribute_consume.rule_id,l_api_name,'CZ_CNV_CONT_CONS_TO_ADD_SUB');
1104 IF v_contribute_consume.template_id IN (708,710) THEN
1105 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);
1106 Update cz_expression_nodes set template_id=25 where rule_id=v_contribute_consume.rule_id and template_id =22;
1107 cz_rule_text_gen.parse_rules(v_contribute_consume.devl_project_id, v_contribute_consume.rule_id);
1108 END IF;
1109 IF(initialvalue IS NOT NULL
1110 AND(p_ps_node_type IN(PS_TYPE_TOTAL, PS_TYPE_RESOURCE) OR(p_ps_node_type = PS_TYPE_FEATURE
1111 AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT)))) THEN
1112 IF v_contribute_consume.devl_project_id = p_devl_project_id THEN
1113 ACC_RULE_CREATED:=TRUE;
1114 END IF;
1115
1116 IF ((l_devl_project_id IS NULL AND l_model_ref_expl_id IS NULL )
1117 OR ( l_devl_project_id<> v_contribute_consume.devl_project_id AND l_model_ref_expl_id <> v_contribute_consume.model_ref_expl_id ) )THEN
1118
1119 -- Total/resource/Num feature
1120 --Create a new Rule Folder in model dev_project_id and get its id in l_rulefolderID
1124 l_rule_id := createConstraintRuleRecord(l_rulefolderid, p_ps_node_id, v_contribute_consume.devl_project_id);
1121 l_rulefolderid := findOrCreateRuleFolder(v_contribute_consume.devl_project_id);
1122
1123 --Create a new rule record in cz_rules in folder specified by l_rulefolderID with Name "Constraint-<<ps_node name>>" get its value in l_rule_id
1125
1126 l_rule_class := RULE_CLASS_CONSTRAINT;
1127 -- create one additional Accumulator Rule to add the initial value to the target
1128 createAccumulatorRule(v_contribute_consume.devl_project_id, p_ps_node_id, p_ps_node_type, initialvalue, minvalue, l_rule_id , v_contribute_consume.model_ref_expl_id,feature_type);
1129 IF v_contribute_consume.devl_project_id = p_devl_project_id THEN
1130 displayMessage(4,p_devl_project_id , 'NODE' ,p_ps_node_id,l_api_name,'CZ_CNV_INFO_ACC_INIT_VAL' , 'RULENAME', 'Constraint-'||v_contribute_consume.name );
1131 ELSE
1132 displayMessage(3,p_devl_project_id , 'NODE',p_ps_node_id,l_api_name,'CZ_CNV_ADV_ACC_DESC_INIT_VAL','NODENAME',v_contribute_consume.name,'MODELNAME',v_contribute_consume.devname,'RULENAME','Constraint-'||v_contribute_consume.name);
1133 END IF;
1134
1135 l_devl_project_id:= v_contribute_consume.devl_project_id;
1136 l_model_ref_expl_id:= v_contribute_consume.model_ref_expl_id;
1137 END IF;
1138
1139 END IF;
1140
1141 UPDATE cz_localized_texts SET deleted_flag =1 WHERE intl_text_id=v_contribute_consume.reason_id;
1142 UPDATE cz_rules SET reason_id=null WHERE rule_id=v_contribute_consume.rule_id;
1143
1144
1145 IF(l_previous_devl_project_id IS NULL) THEN
1146 l_previous_devl_project_id := v_contribute_consume.devl_project_id;
1147 ELSE IF (l_previous_devl_project_id<> v_contribute_consume.devl_project_id) THEN
1148 l_has_mult_down_cont_cons :=TRUE;
1149 END IF;
1150 END IF;
1151
1152 END LOOP;
1153
1154 IF (l_has_mult_down_cont_cons ) THEN
1155 displayMessage(2,p_devl_project_id , 'NODE' ,p_ps_node_id,l_api_name,'CZ_CNV_WARN_NUM_INIT_DOWN_SUM');
1156 END IF;
1157
1158
1159 -- Create accumulator rules for initial values
1160 IF initialvalue IS NOT NULL AND NOT ACC_RULE_CREATED THEN
1161 IF(p_ps_node_type = PS_TYPE_FEATURE
1162 AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT)) OR p_ps_node_type = PS_TYPE_TOTAL OR p_ps_node_type = PS_TYPE_RESOURCE THEN
1163 -- Total/resource/Num feature
1164 --Create a new Rule Folder in model dev_project_id and get its id in l_rulefolderID
1165 l_rulefolderid := findOrCreateRuleFolder(p_devl_project_id);
1166 --Create a new rule record in cz_rules in folder specified by l_rulefolderID with Name "Constraint-<<ps_node name>>" get its value in l_rule_id
1167 l_rule_id := createConstraintRuleRecord(l_rulefolderid, p_ps_node_id, p_devl_project_id);
1168 l_rule_class := RULE_CLASS_CONSTRAINT;
1169 --defaults
1170 -- create one additional Accumulator Rule to add the initial value to the target
1171 createAccumulatorRule(p_devl_project_id, p_ps_node_id, p_ps_node_type, initialvalue, minvalue, l_rule_id , null, feature_type);
1172 select name into l_ps_node_name from cz_ps_nodes where ps_node_id =p_ps_node_id;
1173 displayMessage(4,p_devl_project_id , 'NODE' ,p_ps_node_id,l_api_name,'CZ_CNV_INFO_ACC_INIT_VAL' , 'RULENAME', 'Constraint-'||l_ps_node_name );
1174 END IF;
1175 END IF;
1176
1177 IF initialvalue IS NOT NULL THEN
1178 IF(p_ps_node_type = PS_TYPE_FEATURE
1179 AND feature_type = FEATURE_TYPE_BOOLEAN) THEN
1180 -- Boolean
1181 --Create a new Rule Folder in model dev_project_id and get its id in l_rulefolderID
1182 l_rulefolderid := findOrCreateRuleFolder(p_devl_project_id);
1183 --Create a new rule record in cz_rules in folder specified by rulefolderID with Name "Constraint-<<ps_node name>>" get its value in l_rule_id
1184 l_rule_id := createConstraintRuleRecord(l_rulefolderid, p_ps_node_id, p_devl_project_id, true );
1185
1186
1187 --refer section 4..1.3.3 for Boolean features
1188 insertImpliesRuleRecords(p_devl_project_id, p_ps_node_id, p_ps_node_type, l_rule_id, initialvalue );
1189 IF initialvalue IS NOT NULL THEN
1190 select name into l_ps_node_name from cz_ps_nodes where ps_node_id =p_ps_node_id;
1191 displayMessage(4,p_devl_project_id , 'NODE' ,p_ps_node_id,l_api_name,'CZ_CNV_INFO_BOOL_INIT_VAL','RULENAME' , 'Constraint-'||l_ps_node_name );
1192 END IF;
1193 END IF;
1194 END IF;
1195 EXCEPTION
1196 WHEN others THEN
1197 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
1198 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
1199 RAISE;
1200 END createRules;
1201
1202
1203 -- procedure to remove initial values from nodes
1204 PROCEDURE clearinitialvalues(p_dev_project_id IN cz_devl_projects.devl_project_id%TYPE) AS
1205 l_msg VARCHAR2(2000);
1206 l_api_name constant VARCHAR2(30) := 'ClearInitialValues';
1207 BEGIN
1208 UPDATE cz_ps_nodes
1209 SET initial_num_value = NULL
1210 WHERE devl_project_id = p_dev_project_id
1211 AND initial_num_value IS NOT NULL
1212 AND(ps_node_type IN(PS_TYPE_TOTAL, PS_TYPE_RESOURCE)
1213 OR(ps_node_type = PS_TYPE_FEATURE AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT))
1217 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
1214 OR(ps_node_type = PS_TYPE_FEATURE AND feature_type = FEATURE_TYPE_BOOLEAN));
1215 EXCEPTION
1216 WHEN others THEN
1218 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
1219 RAISE;
1220 END clearinitialvalues;
1221
1222
1223
1224 --procedure to fill in min/max domain range values per type where no value has been defined by the modeler
1225 --Reference TD section 4.1.3.2
1226 PROCEDURE assignDefaultMinMaxvalues(p_dev_project_id IN cz_devl_projects.devl_project_id%TYPE) AS
1227 l_msg VARCHAR2(2000);
1228 l_api_name constant VARCHAR2(30) := 'assignDefaultMinMaxvalues';
1229 BEGIN
1230 FOR c_processing IN
1231 (SELECT ps_node_id , feature_type
1232 FROM cz_ps_nodes
1233 WHERE devl_project_id = p_dev_project_id
1234 AND minimum IS NULL
1235 AND(ps_node_type = PS_TYPE_FEATURE
1236 AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT)))
1237 LOOP
1238 IF c_processing.feature_type= FEATURE_TYPE_INTEGER THEN
1239 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);
1240 ELSE IF c_processing.feature_type= FEATURE_TYPE_FLOAT THEN
1241 displayMessage(3,p_dev_project_id , 'NODE' ,c_processing.ps_node_id,l_api_name,'CZ_CNV_ADV_VAL_MIN_BOUND','MINVAL', -SOLVER_MAX_DOUBLE);
1242 END IF;
1243 END IF;
1244 END LOOP;
1245 UPDATE cz_ps_nodes
1246 SET minimum = decode(feature_type, FEATURE_TYPE_INTEGER, INTEGER_MIN_VAL, FEATURE_TYPE_FLOAT, -SOLVER_MAX_DOUBLE)
1247 WHERE devl_project_id = p_dev_project_id
1248 AND minimum IS NULL
1249 AND(ps_node_type = PS_TYPE_FEATURE
1250 AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT));
1251
1252
1253 --
1254
1255 FOR c_processing IN
1256 (SELECT ps_node_id
1257 FROM cz_ps_nodes
1258 WHERE devl_project_id = p_dev_project_id
1259 AND ps_node_type = PS_TYPE_FEATURE
1260 AND feature_type=FEATURE_TYPE_LIST_OF_OPTIONS
1261 AND counted_options_flag='1')
1262 LOOP
1263 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);
1264 END LOOP;
1265
1266 UPDATE cz_ps_nodes
1267 SET MAX_QTY_PER_OPTION = fnd_profile.value('CZ_DEFAULT_MAX_QTY_INT')
1268 WHERE devl_project_id = p_dev_project_id
1269 AND ps_node_type = PS_TYPE_FEATURE
1270 AND feature_type=FEATURE_TYPE_LIST_OF_OPTIONS
1271 AND counted_options_flag='1';
1272
1273 --
1274
1275 FOR c_processing IN
1276 (SELECT ps_node_id, feature_type
1277 FROM cz_ps_nodes
1278 WHERE devl_project_id = p_dev_project_id
1279 AND maximum IS NULL
1280 AND(ps_node_type = PS_TYPE_FEATURE
1281 AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT , FEATURE_TYPE_LIST_OF_OPTIONS)))
1282 LOOP
1283
1284 IF c_processing.feature_type= FEATURE_TYPE_INTEGER OR c_processing.feature_type=FEATURE_TYPE_LIST_OF_OPTIONS THEN
1285 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);
1286 ELSE IF c_processing.feature_type= FEATURE_TYPE_FLOAT THEN
1287 displayMessage(3,p_dev_project_id , 'NODE' ,c_processing.ps_node_id,l_api_name,'CZ_CNV_ADV_VAL_MAX_BOUND','MAXVAL', SOLVER_MAX_DOUBLE);
1288 END IF;
1289 END IF;
1290 END LOOP;
1291 UPDATE cz_ps_nodes
1292 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)
1293 WHERE devl_project_id = p_dev_project_id
1294 AND maximum IS NULL
1295 AND(ps_node_type = PS_TYPE_FEATURE
1296 AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT));
1297
1298 --
1299 FOR c_processing IN
1300 (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
1301 FROM cz_ps_nodes
1302 WHERE devl_project_id = p_dev_project_id
1303 AND ((maximum IS NULL OR maximum =-1)
1304 AND(ps_node_type IN(PS_TYPE_BOM_MODEL, PS_TYPE_BOM_OPTION_CLASS, PS_TYPE_BOM_STD_ITEM)))
1305 )
1306 LOOP
1307 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);
1308 END LOOP;
1309
1310 UPDATE cz_ps_nodes
1311 SET maximum = decode(USE_BOM_DEFAULT_QTY,'TRUE',initial_num_value,decode(decimal_qty_flag, 1, SOLVER_MAX_DOUBLE, INTEGER_MAX_VALUE))
1312 WHERE devl_project_id = p_dev_project_id
1313 AND (maximum IS NULL OR maximum =-1)
1314 AND(ps_node_type IN(PS_TYPE_BOM_MODEL, PS_TYPE_BOM_OPTION_CLASS, PS_TYPE_BOM_STD_ITEM));
1315
1316 ---Changed for bug 6737779
1317
1318 UPDATE cz_ps_nodes
1319 SET minimum = decode(USE_BOM_DEFAULT_QTY,'TRUE',initial_num_value,decode(decimal_qty_flag, 1,0 ,1 ))
1320 WHERE devl_project_id = p_dev_project_id
1321 AND (minimum IS NULL OR minimum =0)
1322 AND(ps_node_type IN(PS_TYPE_BOM_MODEL, PS_TYPE_BOM_OPTION_CLASS, PS_TYPE_BOM_STD_ITEM))
1323 RETURNING ps_node_id , minimum BULK COLLECT INTO v_cz_ids_tbl , v_cz_num_tbl;
1324
1325
1326 IF v_cz_ids_tbl.COUNT > 0 THEN
1327 FOR i IN v_cz_ids_tbl.FIRST .. v_cz_ids_tbl.LAST
1328 LOOP
1332 v_cz_num_tbl.DELETE;
1329 displayMessage(3,p_dev_project_id , 'NODE' ,v_cz_ids_tbl(i),l_api_name,'CZ_CNV_ADV_QTY_MIN_BOUND','MINVAL', v_cz_num_tbl(i));
1330 END LOOP;
1331 v_cz_ids_tbl.DELETE;
1333 END IF;
1334
1335 UPDATE cz_ps_nodes psout
1336 SET minimum_selected = decode(USE_BOM_DEFAULT_QTY,'TRUE',initial_num_value,decode(decimal_qty_flag, 1,0,1 ))
1337 WHERE devl_project_id = p_dev_project_id
1338 AND (minimum_selected IS NULL OR minimum_selected =0)
1339 AND(ps_node_type =PS_TYPE_REFERENCE)
1340 AND EXISTS( SELECT 1 FROM cz_ps_nodes WHERE ps_node_id= psout.component_id
1341 AND ps_node_type =PS_TYPE_BOM_MODEL )
1342 RETURNING ps_node_id , minimum_selected BULK COLLECT INTO v_cz_ids_tbl , v_cz_num_tbl;
1343
1344
1345 IF v_cz_ids_tbl.COUNT > 0 THEN
1346 FOR i IN v_cz_ids_tbl.FIRST .. v_cz_ids_tbl.LAST
1347 LOOP
1348 displayMessage(3,p_dev_project_id , 'NODE' ,v_cz_ids_tbl(i),l_api_name,'CZ_CNV_ADV_QTY_MIN_BOUND','MINVAL', v_cz_num_tbl(i));
1349 END LOOP;
1350 v_cz_ids_tbl.DELETE;
1351 v_cz_num_tbl.DELETE;
1352 END IF;
1353
1354
1355 --
1356
1357 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
1358 WHERE devl_project_id = p_dev_project_id
1359 AND (maximum_selected IS NULL OR maximum_selected =-1)
1360 AND(ps_node_type =PS_TYPE_REFERENCE)
1361 and item_id is not null
1362 )LOOP
1363
1364 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);
1365 END LOOP;
1366
1367 UPDATE cz_ps_nodes
1368 SET maximum_selected = decode(USE_BOM_DEFAULT_QTY,'TRUE',initial_num_value,decode(decimal_qty_flag, 1, SOLVER_MAX_DOUBLE, INTEGER_MAX_VALUE))
1369 WHERE devl_project_id = p_dev_project_id
1370 AND (maximum_selected IS NULL OR maximum_selected =-1)
1371 AND(ps_node_type =PS_TYPE_REFERENCE)
1372 and item_id is not null ;
1373
1374
1375
1376 ---
1377
1378
1379 UPDATE cz_ps_nodes
1380 SET maximum = INTEGER_MAX_VALUE
1381 WHERE devl_project_id = p_dev_project_id
1382 AND maximum IS NULL
1383 AND(ps_node_type IN(232))
1384 RETURNING ps_node_id BULK COLLECT INTO v_cz_ids_tbl;
1385
1386 IF v_cz_ids_tbl.COUNT>0 THEN
1387 FOR i IN v_cz_ids_tbl.FIRST.. v_cz_ids_tbl.LAST
1388 LOOP
1389 displayMessage(3,p_dev_project_id , 'NODE' ,v_cz_ids_tbl(i),l_api_name,'CZ_CNV_ADV_OPT_QTY_MAX','MAXVAL', INTEGER_MAX_VALUE );
1390 END LOOP;
1391 v_cz_ids_tbl.DELETE;
1392 END IF;
1393
1394
1395
1396 ----- Update default maximum values for total and resource .
1397
1398 UPDATE cz_ps_nodes
1399 SET maximum = DECODE(SIGN(nvl(initial_num_value,0) - SOLVER_MAX_DOUBLE), -1 , SOLVER_MAX_DOUBLE, 0 , SOLVER_MAX_DOUBLE , 1 , initial_num_value)
1400 WHERE devl_project_id = p_dev_project_id
1401 AND maximum IS NULL
1402 AND ps_node_type IN(PS_TYPE_TOTAL,PS_TYPE_RESOURCE)
1403 RETURNING ps_node_id , maximum BULK COLLECT INTO v_cz_ids_tbl , v_cz_num_tbl;
1404
1405 IF v_cz_ids_tbl.COUNT > 0 THEN
1406 FOR i IN v_cz_ids_tbl.FIRST .. v_cz_ids_tbl.LAST
1407 LOOP
1408 displayMessage(3,p_dev_project_id , 'NODE' ,v_cz_ids_tbl(i),l_api_name,'CZ_CNV_ADV_VAL_MAX_BOUND','MAXVAL', v_cz_num_tbl(i));
1409 END LOOP;
1410 v_cz_ids_tbl.DELETE;
1411 v_cz_num_tbl.DELETE;
1412 END IF;
1413
1414 ----
1415
1416 UPDATE cz_ps_nodes
1417 SET minimum = -SOLVER_MAX_DOUBLE
1418 WHERE devl_project_id = p_dev_project_id
1419 AND minimum IS NULL
1420 AND ps_node_type IN(PS_TYPE_TOTAL,PS_TYPE_RESOURCE)
1421 RETURNING ps_node_id BULK COLLECT INTO v_cz_ids_tbl;
1422
1423 IF v_cz_ids_tbl.COUNT>0 THEN
1424 FOR i IN v_cz_ids_tbl.FIRST..v_cz_ids_tbl.LAST
1425 LOOP
1426 displayMessage(3,p_dev_project_id , 'NODE' ,v_cz_ids_tbl(i),l_api_name,'CZ_CNV_ADV_VAL_MIN_BOUND','MINVAL', -SOLVER_MAX_DOUBLE);
1427 END LOOP;
1428 v_cz_ids_tbl.DELETE;
1429 END IF;
1430
1431
1432
1433 EXCEPTION
1434 WHEN others THEN
1435 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
1436 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
1437 RAISE;
1438 END assignDefaultMinMaxvalues;
1439
1440
1441 --procedure to convert existing rules to those suitable for the FCE
1442 --Refer section 4.1.4 rule requirements
1443 PROCEDURE convertRules(p_dev_project_id IN cz_devl_projects.devl_project_id%TYPE, p_rulefolderid IN cz_rules.rule_folder_id%TYPE) AS
1444
1445 -- cursor to fetch ATAN2 rules which will be converted into ATAN
1446 CURSOR c_atan IS
1447 SELECT ex.*
1448 FROM cz_expression_nodes ex,
1449 cz_rules ru
1450 WHERE ru.devl_project_id = p_dev_project_id
1451 AND ru.rule_id = ex.rule_id
1452 AND ru.deleted_flag ='0'
1453 AND ex.template_id = 430
1454 AND ex.deleted_flag ='0';
1455
1456 -- cursor to fetch data from rules having a numeric participant in a logic rule for a given devl_project_id
1457 CURSOR c_numeric_participant IS
1461 exp2.rule_id , psnode.ps_node_id
1458 SELECT exp1.expr_node_id exp1id,
1459 exp2.expr_node_id exp2id,
1460 exp2.expr_parent_id exp2parentid,
1462 FROM cz_expression_nodes exp1,
1463 cz_expression_nodes exp2,
1464 cz_ps_nodes psnode
1465 WHERE exp1.deleted_flag ='0'
1466 AND exp2.deleted_flag ='0'
1467 AND exp1.template_id IN(1, 2, 3, 4, 5)
1468 AND exp2.ps_node_id = psnode.ps_node_id
1469 AND exp1.rule_id = exp2.rule_id
1470 AND psnode.ps_node_type = PS_TYPE_FEATURE
1471 AND psnode.feature_type = FEATURE_TYPE_INTEGER
1472 AND psnode.minimum >= 0
1473 AND psnode.devl_project_id = p_dev_project_id
1474 AND exists (select 1 from cz_expression_nodes where
1475 expr_node_id=exp2.expr_parent_id
1476 and template_id in ( 306 ,307 ,360 ,552 ,21 ) -- All Logic operators
1477 );
1478
1479 --cursor to fetch compatibility rule where more than one participant feature has a maximum number of selections greater than 1
1480 CURSOR c_compat IS
1481 SELECT DISTINCT cz_rules.rule_id,
1482 cz_rules.devl_project_id
1483 FROM cz_rules,
1484 cz_expression_nodes exp1,
1485 cz_expression_nodes exp2,
1486 cz_expression_nodes exp3
1487 WHERE rule_type IN(RULE_TYPE_COMPAT_TABLE, RULE_TYPE_DESIGN_CHART_RULE, RULE_TEMPLATE_FREEFORM_RULE)
1488 AND exp1.rule_id = exp2.rule_id
1489 AND exp1.rule_id = exp3.rule_id
1490 AND exp3.template_id = 23
1491 AND exp1.rule_id = cz_rules.rule_id
1492 AND exp1.expr_type = 207
1493 AND exp2.expr_type = 207
1494 AND exp1.expr_node_id <> exp2.expr_node_id
1495 AND EXISTS
1496 (SELECT 1
1497 FROM cz_expression_nodes expin1,
1498 cz_expression_nodes expin2,
1499 cz_ps_nodes ps1,
1500 cz_ps_nodes ps2
1501 WHERE expin1.expr_node_id = exp1.expr_parent_id
1502 AND expin2.expr_node_id = exp2.expr_parent_id
1503 AND expin2.ps_node_id = ps2.ps_node_id
1504 AND expin1.ps_node_id = ps1.ps_node_id
1505 AND ((ps1.maximum_selected > 1
1506 AND ps2.maximum_selected > 1
1507 )
1508 OR
1509 (ps1.maximum > 1
1510 AND ps2.maximum > 1
1511 )
1512 )
1513 )
1514 AND cz_rules.devl_project_id = p_dev_project_id
1515 UNION ALL
1516 SELECT DISTINCT cf.rule_id ,ps.devl_project_id
1517 FROM cz_des_chart_features cf,
1518 cz_des_chart_features cf1,
1519 cz_ps_nodes ps,
1520 cz_ps_nodes ps1,
1521 cz_rules rule
1522 WHERE cf.rule_id = cf1.rule_id
1523 AND cf.feature_id = ps.ps_node_id
1524 AND cf1.feature_id = ps1.ps_node_id
1525 AND cf1.feature_id <> cf.feature_id
1526 AND ((ps.maximum_selected > 1
1527 AND ps1.maximum_selected > 1
1528 )
1529 OR
1530 (ps.maximum > 1
1531 AND ps1.maximum > 1
1532 )
1533 )
1534 AND rule.devl_project_id = p_dev_project_id
1535 AND rule.rule_id = cf.rule_id
1536 AND rule_type IN(24, 30);
1537
1538
1539 --cursor to fetch compatibility rule where one participant is a BOM Node
1540 CURSOR c_bom_compat IS
1541 SELECT DISTINCT cz_rules.rule_id,
1542 cz_rules.devl_project_id
1543 FROM cz_rules,
1544 cz_expression_nodes exp1,
1545 cz_expression_nodes exp2,
1546 cz_expression_nodes exp3
1547 WHERE rule_type IN(RULE_TYPE_COMPAT_TABLE, RULE_TYPE_DESIGN_CHART_RULE, RULE_TEMPLATE_FREEFORM_RULE)
1548 AND exp1.rule_id = exp2.rule_id
1549 AND exp1.rule_id = exp3.rule_id
1550 AND exp3.template_id = 23
1551 AND exp1.rule_id = cz_rules.rule_id
1552 AND exp1.expr_type = 207
1553 AND exp2.expr_type = 207
1554 AND exp1.expr_node_id <> exp2.expr_node_id
1555 AND EXISTS
1556 (SELECT 1
1557 FROM cz_expression_nodes expin1,
1558 cz_expression_nodes expin2,
1559 cz_ps_nodes ps1,
1560 cz_ps_nodes ps2
1561 WHERE expin1.expr_node_id = exp1.expr_parent_id
1562 AND expin2.expr_node_id = exp2.expr_parent_id
1563 AND expin2.ps_node_id = ps2.ps_node_id
1564 AND expin1.ps_node_id = ps1.ps_node_id
1565 AND (ps1.ps_node_type =PS_TYPE_BOM_MODEL
1566 OR ps2.ps_node_type =PS_TYPE_BOM_MODEL)
1567 )
1568 AND cz_rules.devl_project_id = p_dev_project_id
1569 UNION ALL
1570 SELECT DISTINCT cf.rule_id ,ps.devl_project_id
1571 FROM cz_des_chart_features cf,
1572 cz_des_chart_features cf1,
1573 cz_ps_nodes ps,
1574 cz_ps_nodes ps1,
1575 cz_rules rule
1576 WHERE cf.rule_id = cf1.rule_id
1577 AND cf.feature_id = ps.ps_node_id
1578 AND cf1.feature_id = ps1.ps_node_id
1579 AND cf1.feature_id <> cf.feature_id
1580 AND (ps.ps_node_type =PS_TYPE_BOM_MODEL OR ps1.ps_node_type =PS_TYPE_BOM_MODEL )
1581 AND rule.devl_project_id = p_dev_project_id
1582 AND rule.rule_id = cf.rule_id
1583 AND rule_type IN(24,30);
1584
1585 l_atan c_atan % rowtype;
1586 l_expression_node_id cz_expression_nodes.expr_node_id%TYPE;
1587 l_api_name constant VARCHAR2(30) := 'convertRules';
1588 l_numeric_participant c_numeric_participant%rowtype;
1589 minseq NUMBER;
1590 maxseq NUMBER;
1591 l_expression_node_id1 cz_expression_nodes.expr_node_id%TYPE;
1592 l_ps_node_name cz_ps_nodes.name%type;
1593 l_msg VARCHAR2(2000);
1594 l_cz_rules_id cz_rules.rule_id%TYPE;
1595 BEGIN
1596
1597 --Bug 6725690 , convert ZDIV operator to divide (/) operator.
1598
1599 UPDATE cz_expression_nodes
1603 (SELECT rule_id
1600 SET template_id = 408
1601 WHERE template_id = 404
1602 AND rule_id IN
1604 FROM cz_rules
1605 WHERE devl_project_id = p_dev_project_id
1606 AND deleted_flag ='0')
1607 AND deleted_flag ='0'
1608 RETURNING rule_id BULK COLLECT INTO v_cz_ids_tbl;
1609
1610 IF v_cz_ids_tbl.COUNT>0 THEN
1611 FOR i IN v_cz_ids_tbl.FIRST ..v_cz_ids_tbl.LAST
1612 LOOP
1613 cz_rule_text_gen.parse_rules(p_dev_project_id, v_cz_ids_tbl(i));
1614 END LOOP;
1615 v_cz_ids_tbl.DELETE;
1616 END IF;
1617
1618
1619 -- DEFAULTS rules will be converted into IMPLIES, with default rule type indicator
1620 --Refer section 4.1.4.1.
1621
1622 UPDATE cz_expression_nodes
1623 SET template_id = 2
1624 WHERE template_id = 5
1625 AND rule_id IN
1626 (SELECT rule_id
1627 FROM cz_rules
1628 WHERE devl_project_id = p_dev_project_id
1629 AND deleted_flag ='0')
1630 AND deleted_flag ='0'
1631 RETURNING rule_id BULK COLLECT INTO v_cz_ids_tbl;
1632
1633
1634
1635 FORALL l_cz_rules_id IN v_cz_ids_tbl.FIRST .. v_cz_ids_tbl.LAST
1636 UPDATE cz_rules SET RULE_CLASS=RULE_CLASS_DEFAULT
1637 WHERE rule_id=v_cz_ids_tbl(l_cz_rules_id) ;
1638
1639
1640 IF v_cz_ids_tbl.COUNT>0 THEN
1641 FOR i IN v_cz_ids_tbl.FIRST ..v_cz_ids_tbl.LAST
1642 LOOP
1643 --change defaults to implies
1644 displayMessage(2,p_dev_project_id , 'RULE' ,v_cz_ids_tbl(i),l_api_name,'CZ_CNV_WARN_DEFAULTS');
1645 --Call procedure to generate rule text from the expression tree
1646 populate_cz_tables(true);
1647 cz_rule_text_gen.parse_rules(p_dev_project_id, v_cz_ids_tbl(i));
1648 END LOOP;
1649 v_cz_ids_tbl.DELETE;
1650 END IF;
1651
1652
1653
1654 ---
1655
1656
1657 -- NotTrue rules will be converted into NOT.
1658 --Refer section 4.1.4.2
1659
1660 UPDATE cz_expression_nodes
1661 SET template_id = 552
1662 WHERE template_id = 360
1663 AND rule_id IN
1664 (SELECT rule_id
1665 FROM cz_rules
1666 WHERE devl_project_id = p_dev_project_id
1667 AND deleted_flag ='0')
1668 AND deleted_flag ='0'
1669 RETURNING rule_id BULK COLLECT INTO v_cz_ids_tbl;
1670
1671 IF v_cz_ids_tbl.COUNT>0 THEN
1672 FOR i IN v_cz_ids_tbl.FIRST .. v_cz_ids_tbl.LAST
1673 LOOP
1674 --change template_id from 360 to 552
1675 displayMessage(2,p_dev_project_id , 'RULE' ,v_cz_ids_tbl(i),l_api_name,'CZ_CNV_WARN_NOTTRUE');
1676 populate_cz_tables(true);
1677 cz_rule_text_gen.parse_rules(p_dev_project_id, v_cz_ids_tbl(i));
1678 END LOOP;
1679 v_cz_ids_tbl.DELETE;
1680 END IF;
1681
1682
1683
1684 -- ATAN2 rules will be converted into ATAN.
1685 --Refer section 4.1.4.3
1686 FOR l_atan IN c_atan
1687 LOOP
1688 SELECT cz_expression_nodes_s.nextval
1689 INTO l_expression_node_id
1690 FROM dual;
1691 --Insert new ATAN record
1692
1693 v_cz_expr_node_count:=v_cz_expr_node_count+1;
1694 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_NODE_ID:= l_expression_node_id ;
1695 v_cz_expression_nodes(v_cz_expr_node_count).SEQ_NBR:= '1' ;
1696 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_PARENT_ID:= l_atan.expr_parent_id ;
1697 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_TYPE:= '200' ;
1698 v_cz_expression_nodes(v_cz_expr_node_count).TOKEN_LIST_SEQ:= '8' ;
1699 v_cz_expression_nodes(v_cz_expr_node_count).RULE_ID:= l_atan.rule_id ;
1700 v_cz_expression_nodes(v_cz_expr_node_count).TEMPLATE_ID:= '438' ;
1701 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_SIGNATURE_ID:='91' ;
1702 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_INDEX:= '1' ;
1703 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE:= '2' ;
1704 v_cz_expression_nodes(v_cz_expr_node_count).COLLECTION_FLAG:= '0' ;
1705 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_OFFSET:= '12' ;
1706 v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_LENGTH:= '4' ;
1707 v_cz_expression_nodes(v_cz_expr_node_count).MUTABLE_FLAG:= '0' ;
1708
1709
1710 --update Atan2 to DIV record
1711 UPDATE cz_expression_nodes
1712 SET template_id = 408,
1713 expr_parent_id = l_expression_node_id
1714 WHERE expr_node_id = l_atan.expr_node_id;
1715 displayMessage(2,p_dev_project_id , 'RULE' ,l_atan.rule_id,l_api_name,'CZ_CNV_WARN_ATAN2_REMOVED');
1716 populate_cz_tables(true);
1717 cz_rule_text_gen.parse_rules(p_dev_project_id, l_atan.rule_id);
1718 END LOOP;
1719 --
1720 -- Logic rules may only contain logical expressions as participants.
1721 --Any numeric participants in existing logic rules will be mapped to "expr > 0".
1722 --Refer section 4.1.4.6
1723
1724 ---make this logic rule a statement rule
1725 UPDATE CZ_RULES set presentation_flag=0 where rule_id in
1726 (SELECT exp2.rule_id
1727 FROM cz_expression_nodes exp1,
1728 cz_expression_nodes exp2,
1732 AND exp1.template_id IN(1, 2, 3, 4, 5)
1729 cz_ps_nodes psnode
1730 WHERE exp1.deleted_flag ='0'
1731 AND exp2.deleted_flag ='0'
1733 AND exp2.ps_node_id = psnode.ps_node_id
1734 AND exp1.rule_id = exp2.rule_id
1735 AND psnode.ps_node_type = PS_TYPE_FEATURE
1736 AND psnode.feature_type = FEATURE_TYPE_INTEGER
1737 AND psnode.minimum >= 0
1738 AND psnode.devl_project_id = p_dev_project_id);
1739
1740
1741
1742 FOR l_numeric_participant IN c_numeric_participant
1743 LOOP
1744
1745
1746 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
1747 LOOP
1748
1749 --make this as a statement rule
1750
1751 update cz_expression_nodes set expr_type=200 where expr_type=222 and rule_id=l_numeric_participant.rule_id;
1752
1753
1754 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);
1755 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))
1756 where rule_id= l_numeric_participant.rule_id and seq_nbr between minseq+1 and maxseq-2 ;
1757
1758 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))
1759 where rule_id= l_numeric_participant.rule_id and seq_nbr >maxseq;
1760
1761
1762 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))
1763 where rule_id= l_numeric_participant.rule_id and seq_nbr in (minseq,maxseq);
1764
1765 update cz_expression_nodes set expr_parent_id=null , seq_nbr=1 where expr_node_id=l_numeric_participant.exp1id;
1766 END LOOP;
1767
1768 END LOOP;
1769
1770
1771
1772 FOR l_numeric_participant IN c_numeric_participant
1773 LOOP
1774 SELECT cz_expression_nodes_s.nextval
1775 INTO l_expression_node_id
1776 FROM dual;
1777 -- > record
1778
1779 v_cz_expr_node_count:=v_cz_expr_node_count+1;
1780 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_NODE_ID :=l_expression_node_id ;
1781 v_cz_expression_nodes(v_cz_expr_node_count).SEQ_NBR :='1' ;
1782 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_PARENT_ID :=l_numeric_participant.exp2parentid ;
1783 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_TYPE :='200' ;
1784 v_cz_expression_nodes(v_cz_expr_node_count).TOKEN_LIST_SEQ :='10' ;
1785 v_cz_expression_nodes(v_cz_expr_node_count).RULE_ID :=l_numeric_participant.rule_id ;
1786 v_cz_expression_nodes(v_cz_expr_node_count).TEMPLATE_ID :='350' ;
1787 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_SIGNATURE_ID :='81' ;
1788 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_INDEX :='1' ;
1789 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE :='3' ;
1790 v_cz_expression_nodes(v_cz_expr_node_count).COLLECTION_FLAG :='0' ;
1791 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_OFFSET :='4' ;
1792 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_LENGTH :='38' ;
1793 v_cz_expression_nodes(v_cz_expr_node_count).MUTABLE_FLAG :='0' ;
1794
1795
1796
1797
1798 UPDATE cz_expression_nodes
1799 SET expr_parent_id = l_expression_node_id,
1800 seq_nbr = 1
1801 WHERE expr_node_id = l_numeric_participant.exp2id;
1802 -- make count feature child of >
1803 SELECT cz_expression_nodes_s.nextval
1804 INTO l_expression_node_id1
1805 FROM dual;
1806 -- insert 0 record
1807
1808 v_cz_expr_node_count:=v_cz_expr_node_count+1;
1809 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_NODE_ID :=l_expression_node_id1 ;
1810 v_cz_expression_nodes(v_cz_expr_node_count).SEQ_NBR :='2' ;
1811 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_PARENT_ID :=l_expression_node_id ;
1812 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_TYPE :='201' ;
1813 v_cz_expression_nodes(v_cz_expr_node_count).TOKEN_LIST_SEQ :='10' ;
1814 v_cz_expression_nodes(v_cz_expr_node_count).RULE_ID :=l_numeric_participant.rule_id ;
1815 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_SIGNATURE_ID :='2069' ;
1816 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_INDEX :='1' ;
1820 v_cz_expression_nodes(v_cz_expr_node_count).MUTABLE_FLAG :='0' ;
1817 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE :='1' ;
1818 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_OFFSET :='4' ;
1819 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_LENGTH :='38' ;
1821 v_cz_expression_nodes(v_cz_expr_node_count).DATA_NUM_VALUE :='0' ;
1822
1823
1824 -- insert record for const 0 under > record
1825 SELECT name INTO l_ps_node_name from cz_ps_nodes where ps_node_id=l_numeric_participant.ps_node_id;
1826 displayMessage(3,p_dev_project_id , 'RULE' ,l_numeric_participant.rule_id ,l_api_name,'CZ_CNV_ADV_NUM_IN_LOGIC','NODENAME1', l_ps_node_name,'NODENAME2', l_ps_node_name);
1827 DELETE FROM cz_expression_nodes where rule_id= l_numeric_participant.rule_id and template_id=21;
1828 populate_cz_tables(true);
1829 cz_rule_text_gen.parse_rules(p_dev_project_id, l_numeric_participant.rule_id);
1830 END LOOP;
1831
1832 -- display warning for compatibility rules design chart /property based /explicit compatibility
1833 FOR l_compat IN c_compat
1834 LOOP
1835 displayMessage(1,p_dev_project_id , 'RULE' ,l_compat.rule_id,l_api_name,'CZ_CNV_FAIL_COMPAT_MAXSEL');
1836 END LOOP;
1837
1838
1839
1840 -- LCE supported the use of BOM Models as participants in compatibility rules,
1841 -- however FCE will not support this. Model Conversion should fail any
1842 -- compatibility rule (explicit, property-based, design chart, or CDL
1843 -- equivalent) that has a BOM Model node as a participant. Ref bug 6488867
1844
1845 FOR l_compat IN c_bom_compat
1846 LOOP
1847 displayMessage(1,p_dev_project_id , 'RULE' ,l_compat.rule_id,l_api_name,'CZ_CNV_FAIL_BOM_NODE_COMPAT');
1848 END LOOP;
1849
1850
1851 -- Remove binding for onValidateEligibleTarget event
1852 -- Refer section 4.1.4.7
1853
1854
1855 --fetch Event bindings bound to the onValidateEligibleTarget event
1856
1857 UPDATE cz_expression_nodes
1858 SET deleted_flag = '1'
1859 WHERE expr_node_id
1860 IN (
1861 SELECT expr_node_id
1862 FROM cz_rules czrules ,cz_expression_nodes EXP
1863 WHERE rule_type = RULE_TYPE_CONFIGURATION_EXT
1864 and expr_type = 216
1865 AND EXP.rule_id = czrules.rule_id
1866 AND EXP.argument_signature_id = 2204
1867 AND czrules.deleted_flag ='0'
1868 AND exp.deleted_flag ='0'
1869 AND devl_project_id=p_dev_project_id
1870 )
1871 RETURNING rule_id BULK COLLECT INTO v_cz_ids_tbl ;
1872 --mark this binding as deleted
1873
1874 IF v_cz_ids_tbl.COUNT >0 THEN
1875 FOR i IN v_cz_ids_tbl.FIRST .. v_cz_ids_tbl.LAST
1876 LOOP
1877 displayMessage(2,p_dev_project_id , 'RULE' ,v_cz_ids_tbl(i),l_api_name,'CZ_CNV_WARN_ON_VALIDATE_REM');
1878 END LOOP;
1879 v_cz_ids_tbl.DELETE;
1880 END IF;
1881
1882 --Arguments to Configurator Extension event bindings now require the use of new Java interfaces.
1883
1884 SELECT DISTINCT czrules.rule_id
1885 BULK COLLECT INTO v_cz_ids_tbl
1886 FROM cz_rules czrules,
1887 cz_expression_nodes exp
1888 WHERE rule_type = RULE_TYPE_CONFIGURATION_EXT
1889 AND expr_type = 216
1890 AND exp.rule_id = czrules.rule_id
1891 AND czrules.deleted_flag = '0'
1892 AND exp.deleted_flag = '0'
1893 AND devl_project_id = p_dev_project_id;
1894
1895
1896 IF v_cz_ids_tbl.COUNT >0 THEN
1897 FOR i IN v_cz_ids_tbl.FIRST .. v_cz_ids_tbl.LAST
1898 LOOP
1899 displayMessage(1,p_dev_project_id , 'RULE' ,v_cz_ids_tbl(i),l_api_name,'CZ_CNV_ERR_BIND_EVT');
1900 END LOOP;
1901 v_cz_ids_tbl.DELETE;
1902 END IF;
1903
1904 --Display warning For each Configurator Extension bound to an event that can occur during search.
1905 --Refer section 4.1.4.8
1906
1907
1908
1909 -- fetch records for Configurator Extension bound to an event that can occur during search.
1910
1911
1912 FOR v_rule_ids IN (
1913 SELECT czrules.rule_id,
1914 expout.expr_node_id
1915 FROM cz_rules czrules,
1916 cz_expression_nodes expout
1917 WHERE czrules.rule_type = RULE_TYPE_CONFIGURATION_EXT
1918 AND czrules.rule_id = expout.rule_id
1919 AND EXISTS
1920 (SELECT 1
1921 FROM cz_expression_nodes EXP
1922 WHERE EXP.expr_type = 216
1923 AND EXP.rule_id = czrules.rule_id
1924 AND EXP.argument_signature_id IN(2209, --postInstanceAdd
1925 2210, --postInstanceDelete
1926 2215, --postConnect
1927 2216, --postDisconnect
1928 2217 --postValueChange
1929 --todo to do find values for 'onValueBound'--'preInstanceDelete',--'preInstanceAdd',
1930 )
1931 AND EXP.deleted_flag ='0')
1932 AND expout.deleted_flag ='0'
1933 AND czrules.deleted_flag ='0'
1934 AND devl_project_id = p_dev_project_id
1935 )
1936 LOOP
1937 displayMessage(3,p_dev_project_id , 'RULE' ,v_rule_ids.rule_id,l_api_name,'CZ_CNV_ADV_CX_AUTOCOMPLETE');
1938 END LOOP;
1939
1940 EXCEPTION
1944 RAISE;
1941 WHEN others THEN
1942 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
1943 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
1945 END convertRules;
1946
1947
1948 --This procedure removes effectivity info and displays messages for conversion of total and resources to float and
1949 -- also messages for contribute/consume rule conversion.
1950 PROCEDURE processModel(p_dev_project_id IN cz_devl_projects.devl_project_id%TYPE, p_rule_folderid cz_rules.rule_folder_id%TYPE) AS
1951 --Cursor to fetch Multi-Instantiable BOM Model Reference
1952 CURSOR c_bom_minmax IS
1953 SELECT ps_node_id
1954 FROM cz_ps_nodes psout
1955 WHERE ps_node_type = PS_TYPE_REFERENCE
1956 AND devl_project_id = p_dev_project_id
1957 AND instantiable_flag = NODE_INSTANTIABILITY_MULTIPLE
1958 AND component_id IN
1959 (SELECT ps_node_id
1960 FROM cz_ps_nodes psin
1961 WHERE ps_node_type = PS_TYPE_BOM_MODEL)
1962 ;
1963 l_msg VARCHAR2(2000);
1964 l_api_name constant VARCHAR2(30) := 'processModel';
1965 BEGIN
1966
1967 FOR v_ps_node IN
1968 (SELECT ps_node_id,
1969 ps_node_type,
1970 feature_type,
1971 nvl(initial_value,initial_num_value ) initial_value,
1972 minimum,
1973 maximum,
1974 virtual_flag,name, ROWID
1975 FROM cz_ps_nodes
1976 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)
1977 OR(ps_node_type = PS_TYPE_COMPONENT AND virtual_flag = 1)) AND devl_project_id = p_dev_project_id)
1978 LOOP
1979 -- kdande; 09-Jan-2008; Bug 6722494
1980 IF ((v_ps_node.minimum IS NOT NULL) AND (v_ps_node.ps_node_type = PS_TYPE_FEATURE) AND (v_ps_node.feature_type = FEATURE_TYPE_TEXT)) THEN
1981 UPDATE cz_ps_nodes
1982 SET user_input_required_flag = DECODE (v_ps_node.minimum, 1, '1', '0')
1983 WHERE ROWID = v_ps_node.ROWID;
1984 END IF;
1985
1986 createRules(p_dev_project_id, v_ps_node.ps_node_id, v_ps_node.ps_node_type, v_ps_node.feature_type, v_ps_node.initial_value, v_ps_node.minimum, p_rule_folderid, v_ps_node.maximum);
1987 -- Totals and Resources will be converted to float Totals and Resources.
1988 --Reference section 4.1.3.4
1989 IF v_ps_node.ps_node_type IN(PS_TYPE_TOTAL, PS_TYPE_RESOURCE) THEN
1990 IF v_ps_node.ps_node_type = PS_TYPE_TOTAL THEN
1991 displayMessage(3,p_dev_project_id , 'NODE' ,v_ps_node.ps_node_id,l_api_name,'CZ_CNV_ADV_MAP_TO_DECIMAL');
1992 ELSE
1993 displayMessage(3,p_dev_project_id , 'NODE' ,v_ps_node.ps_node_id,l_api_name,'CZ_CNV_ADV_RES_TO_DECIMAL');
1994 END IF;
1995 END IF;
1996 END LOOP;
1997
1998
1999 -- Effectivity is no longer supported on model node types Total, Resource, Integer Feat,
2000 -- Decimal Feature, and Virtual Component. All Effectivity-related information should be
2001 -- cleared for nodes of these types .Reference TD section 4.1.3.1.
2002 removeEffectivityInfo(p_dev_project_id);
2003 -- Settings for Initial Minimum and Maximum Instances removed; BOM Maximum Quantity setting now defines the
2004 --total Quantity allowed across all Instances.
2005
2006 UPDATE cz_ps_nodes psout
2007 SET maximum = NULL,
2008 minimum = NULL
2009 WHERE ps_node_type = PS_TYPE_REFERENCE
2010 AND devl_project_id = p_dev_project_id
2011 AND instantiable_flag = NODE_INSTANTIABILITY_MULTIPLE
2012 AND component_id IN (SELECT ps_node_id
2013 FROM cz_ps_nodes psin
2014 WHERE ps_node_type = PS_TYPE_BOM_MODEL);
2015
2016 FOR v_ps_node_id IN c_bom_minmax
2017 LOOP
2018 displayMessage(2,p_dev_project_id , 'NODE' ,v_ps_node_id.ps_node_id,l_api_name,'CZ_CNV_WARN_BOM_INIT_VAL_REM');
2019 END LOOP;
2020 --Assign Default domain values as per section 4.1.3.2
2021 assignDefaultMinMaxvalues(p_dev_project_id);
2022
2023
2024 EXCEPTION
2025 WHEN others THEN
2026 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
2027 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
2028 RAISE;
2029 END processModel;
2030
2031
2032 --Note this procedure is called from model conversion concurrent program
2033 PROCEDURE convertModels(p_model_conversion_set_id IN NUMBER) AS
2034 l_rule_folderid cz_rule_folders.rule_folder_id%TYPE;
2035 l_msg VARCHAR2(2000);
2036 l_api_name constant VARCHAR2(30) := 'convertModels';
2037 l_run_id NUMBER;
2038 BEGIN
2039
2040
2041 v_cz_expr_node_count := 0;
2042 v_cz_expression_nodes.DELETE;
2043
2044 v_cz_rule_count := 0;
2045 V_CZ_RULES.DELETE;
2046
2047 v_model_conversion_set_id:=p_model_conversion_set_id;
2048 FOR v_models IN
2049 ( SELECT remote_model_id
2050 FROM cz_model_publications p , cz_pb_model_exports z
2051 WHERE p.export_status IN('OK')
2052 AND p.server_id = 0
2053 AND p.publication_mode = 'M'
2054 AND p.migration_group_id = p_model_conversion_set_id
2055 AND z.publication_id = p.publication_id
2056 AND z.model_id = p.object_id
2057 AND z.server_id = 0
2058 AND z.status = 'OK'
2062 LOOP
2059 and p.source_target_flag='S'
2060 AND p.deleted_flag='0'
2061 )
2063
2064 displayMessage(0,v_models.remote_model_id , NULL ,v_models.remote_model_id,l_api_name, 'Model record');
2065
2066
2067 --mark this model as a fusion model
2068 UPDATE cz_devl_projects
2069 SET config_engine_type = 'F' ,post_migr_change_flag =NULL
2070 WHERE devl_project_id = v_models.remote_model_id;
2071
2072 l_rule_folderid := findOrCreateRuleFolder(v_models.remote_model_id);
2073 --Handle model conversion
2074 processModel(v_models.remote_model_id, l_rule_folderid);
2075 --Handle UI conversion
2076 processUI(v_models.remote_model_id);
2077 convertRules(v_models.remote_model_id, l_rule_folderid);
2078
2079
2080 END LOOP;
2081
2082 --Dump rules data for newly created rules to the database tables .
2083 populate_cz_tables(true);
2084 --Mark rule_class=0 and config_engine_type='F' for all rules having null values for these fields
2085 UPDATE cz_rules
2086 SET config_engine_type = 'F' , rule_class=nvl(rule_class,0)
2087 WHERE deleted_flag='0'
2088 AND devl_project_id IN( SELECT remote_model_id
2089 FROM cz_model_publications p , cz_pb_model_exports z
2090 WHERE p.export_status IN('OK')
2091 AND p.server_id = 0
2092 AND p.publication_mode = 'M'
2093 AND p.migration_group_id = p_model_conversion_set_id
2094 AND z.publication_id = p.publication_id
2095 AND z.model_id = p.object_id
2096 AND z.server_id = 0
2097 AND z.status = 'OK'
2098 and p.source_target_flag='S'
2099 AND p.deleted_flag='0');
2100
2101
2102 -- Clearing Initial values need to be done at last for all models
2103 -- as some models may have a downward contribute/consume and hence may require the initial values for accumulator rule creation
2104 FOR v_models IN
2105 ( SELECT remote_model_id
2106 FROM cz_model_publications p , cz_pb_model_exports z
2107 WHERE p.export_status IN('OK')
2108 AND p.server_id = 0
2109 AND p.publication_mode = 'M'
2110 AND p.migration_group_id = p_model_conversion_set_id
2111 AND z.publication_id = p.publication_id
2112 AND z.model_id = p.object_id
2113 AND z.server_id = 0
2114 AND z.status = 'OK'
2115 and p.source_target_flag='S'
2116 AND p.deleted_flag='0'
2117 )
2118 LOOP
2119 --Clear Initial values for certain node types
2120 clearinitialvalues(v_models.remote_model_id);
2121
2122 /************** For now donot call logicgen
2123 --After processing rules and when done with conversion process for this model run logicgen .
2124 l_run_id:=0;
2125 BEGIN
2126 cz_fce_compile.compile_logic(v_models.remote_model_id,l_run_id);
2127 EXCEPTION WHEN OTHERS THEN
2128 NULL; -- we donot want conversion to fail if logicgen reported an error .
2129 END;
2130 ***************/
2131 END LOOP;
2132 --Do Cleanup
2133 v_cz_expr_node_count := 0;
2134 v_cz_expression_nodes.DELETE;
2135 v_cz_rule_count := 0;
2136 V_CZ_RULES.DELETE;
2137 v_model_conversion_set_id:=NULL;
2138 EXCEPTION
2139 WHEN others THEN
2140 v_cz_expr_node_count := 0;
2141 v_cz_expression_nodes.DELETE;
2142 v_cz_rule_count := 0;
2143 V_CZ_RULES.DELETE;
2144 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
2145 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
2146 v_model_conversion_set_id:=NULL;
2147 RAISE;
2148 END convertModels;
2149
2150
2151
2152
2153 ---------------------------------------------------------------------------------------
2154 /*
2155 * Copy Model For Conversionprocedure.
2156 * @param errbuf Standard Oracle Concurrent Program output parameters.
2157 * @param retcode Standard Oracle Concurrent Program output parameters.
2158 * @param p_request_id This is the CZ_MODEL_PUBLICATIONS, MIGRATION_GROUP_ID of the migration request.
2159 * Migration request is created by Developer and contains the list of all models selected
2160 * for Migration from the source's Configurator Repository, target Instance name and
2161 * target Repository Folder.
2162 */
2163
2164 PROCEDURE copy_model_for_conversion(errbuf OUT NOCOPY VARCHAR2,
2165 retcode OUT NOCOPY NUMBER,
2166 p_request_id IN NUMBER
2167 ) IS
2168 l_status VARCHAR2(3);
2169 l_publication_id NUMBER;
2170 l_run_id NUMBER := 0;
2171 l_mig_group_found BOOLEAN :=FALSE;
2172 l_api_name CONSTANT VARCHAR2(30) := 'copy_model_for_conversion';
2173 PUB_ERROR EXCEPTION;
2174 BEGIN
2175
2176 retcode:=0;
2177 cz_pb_mgr.GLOBAL_EXPORT_RETCODE := 0;
2178
2179 FOR c_pub IN (SELECT publication_id ,max(node_depth) mdepth FROM cz_model_publications mp ,cz_model_ref_expls mr
2180 WHERE mp.migration_group_id = p_request_id AND mp.deleted_flag = '0' and mr.deleted_flag = '0'
2181 AND mp.publication_mode='M'
2185 )LOOP
2182 and mr.model_id =mp.object_id
2183 group by publication_id
2184 order by mdepth asc
2186
2187 l_mig_group_found :=TRUE;
2188 cz_pb_mgr.publish_model(c_pub.publication_id, l_run_id, l_status);
2189 IF l_status = cz_pb_mgr.PUBLICATION_ERROR THEN
2190 RAISE PUB_ERROR;
2191 END IF;
2192
2193 errbuf := NULL;
2194 IF(cz_pb_mgr.GLOBAL_EXPORT_RETCODE = 1)THEN
2195 errbuf := CZ_UTILS.GET_TEXT('CZ_MM_WARNING');
2196 ELSIF(cz_pb_mgr.GLOBAL_EXPORT_RETCODE = 2) THEN
2197 errbuf := CZ_UTILS.GET_TEXT('CZ_MM_FAILURE');
2198 END IF;
2199 END LOOP;
2200
2201 IF NOT l_mig_group_found THEN
2202 errbuf := cz_utils.get_text('CZ_INVALID_MIGR_GROUP_NUMBER', 'MIGRGRP', p_request_id);
2203 log_msg(l_api_name, v_ndebug, errbuf , FND_LOG.LEVEL_PROCEDURE);
2204 raise_application_error('-20020', 'INVALID_MIGRATION_GROUP');
2205 END IF;
2206
2207 retcode := cz_pb_mgr.GLOBAL_EXPORT_RETCODE;
2208
2209 EXCEPTION
2210 WHEN OTHERS THEN
2211 retcode := 2;
2212 log_msg(l_api_name, v_ndebug, errbuf, fnd_log.level_unexpected);
2213 errbuf := CZ_UTILS.GET_TEXT('CZ_MM_UNEXPECTED');
2214 RAISE;
2215 END;
2216
2217
2218 Procedure Model_Convert_CP(errbuf out nocopy varchar2,
2219 Retcode out nocopy number,
2220 P_request_id in number default null) is
2221 req_data varchar2(10);
2222 r number;
2223 i number;
2224 b boolean;
2225 noofreports number:=0;
2226
2227 call_status boolean;
2228 rphase varchar2(80);
2229 rstatus varchar2(80);
2230 dphase varchar2(30);
2231 dstatus varchar2(30);
2232 message varchar2(240);
2233 L_API_NAME CONSTANT VARCHAR2(30) := 'Model_Convert_CP';
2234 L_RETURN_STATUS VARCHAR2(100) ;
2235 L_MSG_COUNT NUMBER ;
2236 L_MSG_DATA VARCHAR2(2000) ;
2237 ERR_PROFILE EXCEPTION;
2238 l_migration_group_id cz_model_publications.migration_group_id%TYPE;
2239 L_ERROR_IN_CP BOOLEAN;
2240
2241 Begin
2242
2243 fnd_msg_pub.initialize;
2244 cz_model_convert.CONVERT_MODEL:=TRUE;
2245 L_ERROR_IN_CP :=FALSE;
2246
2247 IF fnd_profile.value('CZ_BOM_DEFAULT_QTY_DOMN')='Y' THEN
2248 USE_BOM_DEFAULT_QTY:='TRUE';
2249 ELSE
2250 USE_BOM_DEFAULT_QTY:='FALSE';
2251 END IF;
2252
2253
2254 --get integer and decimal max values from profiles
2255
2256
2257 INTEGER_MAX_VALUE := fnd_profile.value('CZ_DEFAULT_MAX_QTY_INT');
2258 SOLVER_MAX_DOUBLE := fnd_profile.value('CZ_DEFAULT_MAX_QTY_DEC');
2259
2260 IF (INTEGER_MAX_VALUE IS NULL OR SOLVER_MAX_DOUBLE IS NULL) THEN
2261 log_msg('Model_Convert_CP', 0, cz_utils.get_text('CZ_CNV_ERR_PROFILE_NOT_SET') , fnd_log.level_unexpected);
2262 RAISE ERR_PROFILE;
2263 END IF;
2264
2265
2266 INTEGER_MIN_VAL := INTEGER_MAX_VALUE * -1;
2267
2268
2269
2270 SELECT COUNT(*)
2271 INTO noofreports
2272 FROM
2273 (SELECT DISTINCT migration_group_id
2274 FROM cz_model_publications
2275 WHERE deleted_flag ='0'
2276 AND publication_mode = 'M'
2277 AND(migration_group_id = p_request_id OR(p_request_id IS NULL AND migration_group_id IS NOT NULL))
2278 AND export_status = 'PEN'
2279 AND server_id = 0);
2280
2281 IF(noofreports=0 and fnd_conc_global.request_data IS NULL ) THEN
2282 log_msg('Model_Convert_CP', 0, 'No pending request with the supplied Conversion Set ID', fnd_log.level_unexpected);
2283 errbuf := 'Error in model conversion!';
2284 retcode := 2;
2285 raise ERR_PROFILE;
2286
2287 END IF;
2288
2289 For c_model_conv IN (
2290 SELECT DISTINCT migration_group_id
2291 FROM cz_model_publications mp
2292 WHERE deleted_flag ='0' and
2293 publication_mode='M'
2294 AND server_id = 0
2295 AND export_status = 'PEN'
2296 AND (migration_group_id = p_request_id
2297 OR(P_request_id is null AND migration_group_id IS NOT NULL ))
2298 ) LOOP
2299
2300
2301 BEGIN
2302
2303 l_migration_group_id:=c_model_conv.migration_group_id;
2304 --
2305 -- Read the value from REQUEST_DATA. If this is the PL/SQL APIs for Concurrent Processing
2306 -- first run of the program, then this value will be
2307 -- null.
2308 -- Otherwise, this will be the value that we passed to
2309 -- SET_REQ_GLOBALS on the previous run.
2310 --
2311 req_data := fnd_conc_global.request_data;
2312
2313 -- If this is the first run, well set i = 1.
2314 -- Otherwise we will set i = request_data + 1, and we will
2315 -- exit if we are done.
2316 --
2317 IF (req_data is not null) then
2318 i := to_number(req_data);
2319 i := i + 1;
2320 IF (i < noofreports+1 ) THEN
2321 errbuf := 'Done!';
2322 retcode := 0 ;
2323 return;
2327 END IF;
2324 END IF;
2325 ELSE
2326 i := 1;
2328 --this procedures creates copy of the existing model
2329 copy_model_for_conversion(errbuf, Retcode, c_model_conv.migration_group_id);
2330 commit;
2331 UPDATE cz_model_publications set export_status='OK' where export_status='PEN' and migration_group_id=c_model_conv.migration_group_id;
2332 --this procedure converts the copied LCE models to FCE standard
2333 convertModels(c_model_conv.migration_group_id);
2334 commit;
2335 --
2336 -- Submit the child request. The sub_request parameter
2337 -- must be set to 'Y'.
2338 --
2339 b:=fnd_request.ADD_LAYOUT (
2340 TEMPLATE_APPL_NAME => 'CZ',
2341 TEMPLATE_CODE => 'CZ_MDLCONV',
2342 TEMPLATE_LANGUAGE => 'EN',
2343 TEMPLATE_TERRITORY => 'US',
2344 OUTPUT_FORMAT => 'PDF'
2345 );
2346
2347 --Submit a new sub request for generating a XML publisher report
2348 r := fnd_request.submit_request('CZ','CZ_MDLCONV', 'Model Conversion Report - Conversion Set '|| c_model_conv.migration_group_id, NULL ,TRUE, c_model_conv.migration_group_id);
2349
2350 IF r = 0 THEN
2351 --
2352 -- If request submission failed, exit with error.
2353 --
2354 errbuf := fnd_message.get;
2355 retcode := 2;
2356 ELSE
2357 --
2358 log_msg('Model_Convert_CP', 0 ,
2359 'Model Conversion Report Generation has been submitted as a Concurrent Request with ID '||r||'. Please review the output of the Concurrent Process for important messages about the conversion.', fnd_log.level_unexpected);
2360 errbuf := 'Request submitted!';
2361 retcode := 0 ;
2362 END IF;
2363
2364 COMMIT;
2365 EXCEPTION WHEN OTHERS THEN
2366 L_ERROR_IN_CP := TRUE;
2367 retcode := 2;
2368 errbuf := cz_utils.get_text('CZ_CNV_UNEXPECTED');
2369 log_msg('Model_Convert_CP', 0, 'Error submitting request for report generation.', fnd_log.level_unexpected);
2370 cz_model_convert.CONVERT_MODEL:=FALSE;
2371 BEGIN
2372 call_status := FND_CONCURRENT.GET_REQUEST_STATUS(r, '', '', rphase,rstatus,dphase,dstatus, message);
2373 IF dphase<>'COMPLETE' THEN
2374 fnd_conc_global.set_req_globals(conc_status => 'PAUSED', request_data => to_char(i));
2375 END IF;
2376 --no need to pause if child completed
2377 EXCEPTION WHEN OTHERS THEN
2378 NULL;
2379 END;
2380
2381 raise;
2382 END;
2383 END LOOP;
2384
2385 cz_model_convert.CONVERT_MODEL:=FALSE;
2386 BEGIN
2387 call_status := FND_CONCURRENT.GET_REQUEST_STATUS(r, '', '', rphase,rstatus,dphase,dstatus, message);
2388 IF dphase<>'COMPLETE' THEN
2389 fnd_conc_global.set_req_globals(conc_status => 'PAUSED', request_data => to_char(i));
2390 END IF;
2391 --no need to pause if child completed
2392 EXCEPTION WHEN OTHERS THEN
2393 NULL;
2394 END;
2395
2396
2397 IF(L_ERROR_IN_CP)THEN
2398 retcode :=2;
2399 errbuf := cz_utils.get_text('CZ_CNV_UNEXPECTED');
2400 END IF;
2401 EXCEPTION WHEN ERR_PROFILE THEN
2402 IF(errbuf <> cz_utils.get_text('CZ_CNV_UNEXPECTED'))THEN
2403 log_msg(l_api_name, v_ndebug, errbuf, fnd_log.level_unexpected);
2404 END IF;
2405 cz_model_convert.CONVERT_MODEL:=FALSE;
2406 retcode := 2;
2407 errbuf := cz_utils.get_text('CZ_CNV_UNEXPECTED');
2408 commit;
2409 WHEN OTHERS THEN
2410 IF(errbuf <> cz_utils.get_text('CZ_CNV_UNEXPECTED'))THEN
2411 log_msg(l_api_name, v_ndebug, errbuf, fnd_log.level_unexpected);
2412 END IF;
2413 cz_model_convert.CONVERT_MODEL:=FALSE;
2414 -- rollback model copy operation when there is an error
2415 --If "cz_model_migration_pvt .migrate_models_cp" call not completed successfully then Mark the migrated models as deleted .
2416
2417 retcode := 2;
2418 errbuf := cz_utils.get_text('CZ_CNV_UNEXPECTED');
2419
2420 FOR c_process IN (
2421
2422 SELECT remote_model_id
2423 FROM cz_model_publications p , cz_pb_model_exports z
2424 WHERE p.export_status IN('OK')
2425 AND p.server_id = 0
2426 AND p.publication_mode = 'M'
2427 AND p.migration_group_id = P_request_id
2428 AND z.publication_id = p.publication_id
2429 AND z.model_id = p.object_id
2430 AND z.server_id = 0
2431 AND z.status = 'OK'
2432 and p.source_target_flag='S'
2433 AND p.deleted_flag='0'
2434 )LOOP
2435 BEGIN
2436 cz_developer_utils_pvt.delete_model(c_process.remote_model_id,L_RETURN_STATUS,L_MSG_COUNT,L_MSG_DATA);
2437 EXCEPTION WHEN OTHERS THEN
2438 errbuf := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
2439 log_msg(l_api_name, v_ndebug, errbuf, fnd_log.level_unexpected);
2440 END;
2441
2442 END LOOP;
2443
2444 --an error has occured update status to reflect this
2445 UPDATE cz_model_publications SET export_status='ERR' WHERE migration_group_id = l_migration_group_id and server_id=0 ;
2446
2447
2448 commit;
2449 End Model_Convert_CP;
2450
2451
2452 FUNCTION GET_UI_PATH(inParent_id IN NUMBER) RETURN VARCHAR2 is
2453 l_qualified VARCHAR2(2000) := ' ';
2454 BEGIN
2455
2456 IF inParent_id IS NOT NULL THEN
2457 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));
2458 l_qualified:=l_qualified||'.Pages';
2459 FOR parent_node IN (SELECT distinct(name) , element_id FROM cz_ui_page_elements
2460 WHERE deleted_flag = '0'
2461 START WITH element_id = to_char(inParent_id)
2462 CONNECT BY PRIOR parent_element_id=element_id
2463 order by element_id ) LOOP
2464
2465 IF(LENGTH(parent_node.name) + LENGTH(l_qualified) + 1 < 2000) THEN
2466 l_qualified := l_qualified||'.' || parent_node.name ;
2467 ELSE
2468 EXIT;
2469 END IF;
2470 END LOOP;
2471 END IF;
2472 RETURN l_qualified;
2473 END GET_UI_PATH;
2474
2475
2476
2477 END CZ_MODEL_CONVERT;
[Home] [Help]