1 PACKAGE BODY cz_model_convert AS
2 /* $Header: czmdlconb.pls 120.44.12020000.3 2012/12/17 01:52:34 spitre 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'
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'
400 and c.TEMPLATE_UI_DEF_ID = t.UI_DEF_ID
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
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
506 and tempel.template_id=te.template_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
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
618
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.
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';
723 BEGIN
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 -- BUG9176281 -
805 -- If you call populate_cz_tables() after inserting the data in v_cz_rules - it will create a Rule with
806 -- blank CDL as the v_cz_expression_nodes is not yet populated with required data.
807
808 populate_cz_tables();
809
810 -- End Fixing BUG9176281
811 v_cz_rule_count:=v_cz_rule_count+1;
812 V_CZ_RULES(v_cz_rule_count).rule_id:= l_rule_id;
813 V_CZ_RULES(v_cz_rule_count).reason_id:= l_intl_text_id;
814 IF p_rulefolderid IS NULL THEN
815 V_CZ_RULES(v_cz_rule_count).rule_folder_id:=findOrCreateRuleFolder(p_devl_project_id) ;
816 ELSE
817 V_CZ_RULES(v_cz_rule_count).rule_folder_id:= p_rulefolderid ;
818 END IF;
819 V_CZ_RULES(v_cz_rule_count).devl_project_id:= p_devl_project_id;
820 V_CZ_RULES(v_cz_rule_count).invalid_flag:= '0';
821 IF p_default_rule THEN
822 V_CZ_RULES(v_cz_rule_count).name:= 'Defaults-' || l_ps_node_name;
823 ELSE
824 V_CZ_RULES(v_cz_rule_count).name:= 'Constraint-' || l_ps_node_name;
825 END IF;
826 V_CZ_RULES(v_cz_rule_count).rule_type:= '200';
827 V_CZ_RULES(v_cz_rule_count).reason_type:= '0' ;
828 V_CZ_RULES(v_cz_rule_count).disabled_flag:= '0' ;
829 V_CZ_RULES(v_cz_rule_count).deleted_flag:= '0' ;
830 V_CZ_RULES(v_cz_rule_count).effective_usage_mask:='0000000000000000';
831 V_CZ_RULES(v_cz_rule_count).seq_nbr:= '1';
832 V_CZ_RULES(v_cz_rule_count).effective_from:= cz_utils.epoch_begin;
833 V_CZ_RULES(v_cz_rule_count).effective_until:= cz_utils.epoch_end;
834 V_CZ_RULES(v_cz_rule_count).persistent_rule_id:=l_rule_id;
835 V_CZ_RULES(v_cz_rule_count).presentation_flag:= '0';
836 V_CZ_RULES(v_cz_rule_count).mutable_flag:= '0';
837 V_CZ_RULES(v_cz_rule_count).seeded_flag:= '0';
838
839 -- INSERTING into cz_rules -- rule folder entry
840 -- Fixing BUG9176281 - Rule name for Default Rule
841 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)
842 VALUES(l_rule_id, V_CZ_RULES(v_cz_rule_count).name , p_devl_project_id, '1', '0', '0', '0000000000000000', l_rule_id, 'RUL', '0' , V_CZ_RULES(v_cz_rule_count).rule_folder_id );
843
844 --populate_cz_tables(); moved prior to inserting data to v_cz_rules
845
846 RETURN l_rule_id;
847 EXCEPTION
848 WHEN others THEN
849 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
850 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
851 RAISE;
852 END;
853
854
855 --Create implies rule for initial values on Boolean features .refer section 4..1.3.3 for Boolean features
856 PROCEDURE insertImpliesRuleRecords(dev_project_id IN cz_devl_projects.devl_project_id%TYPE, ps_node_id IN cz_ps_nodes.ps_node_id%TYPE,
857 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
858 l_msg VARCHAR2(2000);
859 l_api_name constant VARCHAR2(30) := 'insertImpliesRuleRecords';
860 l_expression_node_id cz_expression_nodes.expr_node_id%TYPE;
861 l_expression_node_id1 cz_expression_nodes.expr_node_id%TYPE;
862 l_mod_ref cz_expression_nodes.MODEL_REF_EXPL_ID%TYPE;
863 BEGIN
864
865 SELECT cz_expression_nodes_s.nextval
866 INTO l_expression_node_id
867 FROM dual;
868 v_cz_expr_node_count := v_cz_expr_node_count + 1;
869 --insert the implies record
870 -- INSERTING into cz_expression_nodes
871 v_cz_expression_nodes(v_cz_expr_node_count).expr_node_id := l_expression_node_id;
872 v_cz_expression_nodes(v_cz_expr_node_count).seq_nbr := 1;
873 v_cz_expression_nodes(v_cz_expr_node_count).expr_type := 200;
874 v_cz_expression_nodes(v_cz_expr_node_count).token_list_seq := 8;
875 v_cz_expression_nodes(v_cz_expr_node_count).rule_id := l_rule_id;
876 v_cz_expression_nodes(v_cz_expr_node_count).template_id := 1;
877 v_cz_expression_nodes(v_cz_expr_node_count).data_type := 0;
878 v_cz_expression_nodes(v_cz_expr_node_count).collection_flag := 0;
879 v_cz_expression_nodes(v_cz_expr_node_count).mutable_flag := 0;
880
881 -- v_cz_expression_nodes(v_cz_expr_node_count).source_offset := 7;
882 -- v_cz_expression_nodes(v_cz_expr_node_count).source_length := 7;
883
884
885 v_cz_expr_node_count := v_cz_expr_node_count + 1;
886 --insert initial value record
887 SELECT cz_expression_nodes_s.nextval
888 INTO l_expression_node_id1
889 FROM dual;
890 v_cz_expression_nodes(v_cz_expr_node_count).expr_node_id := l_expression_node_id1;
891 v_cz_expression_nodes(v_cz_expr_node_count).seq_nbr := 1;
892 v_cz_expression_nodes(v_cz_expr_node_count).expr_parent_id := l_expression_node_id;
893 v_cz_expression_nodes(v_cz_expr_node_count).data_value := initialvalue;
894 v_cz_expression_nodes(v_cz_expr_node_count).expr_type := 201;
895 v_cz_expression_nodes(v_cz_expr_node_count).token_list_seq := 7;
896 v_cz_expression_nodes(v_cz_expr_node_count).rule_id := l_rule_id;
897 v_cz_expression_nodes(v_cz_expr_node_count).param_signature_id := 81;
898 v_cz_expression_nodes(v_cz_expr_node_count).param_index := 1;
899 v_cz_expression_nodes(v_cz_expr_node_count).data_type := 3;
900 v_cz_expression_nodes(v_cz_expr_node_count).mutable_flag := 0;
901 -- v_cz_expression_nodes(v_cz_expr_node_count).source_offset := 1;
902 -- v_cz_expression_nodes(v_cz_expr_node_count).source_length := 4;
903
904 v_cz_expr_node_count := v_cz_expr_node_count + 1;
905 --insert boolean feature record
906 SELECT cz_expression_nodes_s.nextval
907 INTO l_expression_node_id1
908 FROM dual;
909 v_cz_expression_nodes(v_cz_expr_node_count).expr_node_id := l_expression_node_id1;
910 v_cz_expression_nodes(v_cz_expr_node_count).seq_nbr := 2;
911 v_cz_expression_nodes(v_cz_expr_node_count).ps_node_id := ps_node_id;
912 v_cz_expression_nodes(v_cz_expr_node_count).expr_parent_id := l_expression_node_id;
913 v_cz_expression_nodes(v_cz_expr_node_count).expr_type := 205;
914 v_cz_expression_nodes(v_cz_expr_node_count).token_list_seq := 15;
915 v_cz_expression_nodes(v_cz_expr_node_count).rule_id := l_rule_id;
916 v_cz_expression_nodes(v_cz_expr_node_count).param_signature_id := 81;
917 v_cz_expression_nodes(v_cz_expr_node_count).param_index := 2;
918 v_cz_expression_nodes(v_cz_expr_node_count).data_type := 502;
919 v_cz_expression_nodes(v_cz_expr_node_count).display_node_depth := 1;
920 v_cz_expression_nodes(v_cz_expr_node_count).mutable_flag := '1';
921 -- v_cz_expression_nodes(v_cz_expr_node_count).source_offset := 15;
922 -- v_cz_expression_nodes(v_cz_expr_node_count).source_length := 24;
923
924 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';
925 v_cz_expression_nodes(v_cz_expr_node_count).model_ref_expl_id := l_mod_ref;
926
927 --Mark the rule as a default rule
928 populate_cz_tables(true);
929 UPDATE cz_rules
930 SET rule_class = RULE_CLASS_DEFAULT
931 WHERE rule_id = l_rule_id;
932
933
934
935 EXCEPTION
936 WHEN others THEN
937 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
938 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
939 RAISE;
940 END insertImpliesRuleRecords;
941
942
943 --Procedure to create the expression tree for a contribute/consume target
944 --This procedure adds an ADDTO rule for a contribute/consume rule where the initial value of target is not null
945
946 PROCEDURE createAccumulatorRule(dev_project_id IN cz_devl_projects.devl_project_id%TYPE, ps_node_id IN cz_ps_nodes.ps_node_id%TYPE,
947 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,
948 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
949 l_msg VARCHAR2(2000);
950 l_api_name constant VARCHAR2(30) := 'createAccumulatorRule';
951 l_expression_node_id cz_expression_nodes.expr_node_id%TYPE;
952 l_expression_node_id1 cz_expression_nodes.expr_node_id%TYPE;
953 l_mod_ref cz_expression_nodes.MODEL_REF_EXPL_ID%type;
954 l_ps_node_id cz_ps_nodes.ps_node_id%TYPE;
955 BEGIN
956 IF initialvalue IS NOT NULL THEN
957
958 -- Insert AddTo expression record;
959
960 SELECT cz_expression_nodes_s.nextval INTO l_expression_node_id FROM dual;
961 v_cz_expr_node_count:=v_cz_expr_node_count+1;
962
963 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_NODE_ID:= l_expression_node_id ;
964 v_cz_expression_nodes(v_cz_expr_node_count).SEQ_NBR:= '1' ;
965 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_TYPE:= '200' ;
966 v_cz_expression_nodes(v_cz_expr_node_count).TOKEN_LIST_SEQ:= '1' ;
967 v_cz_expression_nodes(v_cz_expr_node_count).DELETED_FLAG:= '0' ;
968 v_cz_expression_nodes(v_cz_expr_node_count).RULE_ID:= l_rule_id ;
969 v_cz_expression_nodes(v_cz_expr_node_count).TEMPLATE_ID:= OPERATOR_ADDSTO ;
970 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE:= '0' ;
971 v_cz_expression_nodes(v_cz_expr_node_count).COLLECTION_FLAG:= '0' ;
972 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_OFFSET:= '1' ;
973 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_LENGTH:= '10' ;
974 v_cz_expression_nodes(v_cz_expr_node_count).MUTABLE_FLAG:= '0' ;
975 -- Insert initial value record;
976
977
978 SELECT cz_expression_nodes_s.nextval INTO l_expression_node_id1 FROM dual;
979 v_cz_expr_node_count:=v_cz_expr_node_count+1;
980
981
982
983 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_NODE_ID:= l_expression_node_id1 ;
984 v_cz_expression_nodes(v_cz_expr_node_count).SEQ_NBR:= '1' ;
985 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_PARENT_ID:= l_expression_node_id ;
986 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_TYPE:= '201' ;
987 v_cz_expression_nodes(v_cz_expr_node_count).TOKEN_LIST_SEQ:= '8' ;
988 v_cz_expression_nodes(v_cz_expr_node_count).DELETED_FLAG:= '0' ;
989 v_cz_expression_nodes(v_cz_expr_node_count).RULE_ID:= l_rule_id ;
990 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_SIGNATURE_ID:='96' ;
991 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_INDEX:= '1' ;
992
993 IF p_feature_type=FEATURE_TYPE_FLOAT THEN
994 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE:= '2' ;
995 ELSE
996 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE:= '1' ;
997 END IF;
998
999 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_OFFSET:= '13' ;
1000 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_LENGTH:= '1' ;
1001 v_cz_expression_nodes(v_cz_expr_node_count).MUTABLE_FLAG:= '0' ;
1002 v_cz_expression_nodes(v_cz_expr_node_count).DATA_NUM_VALUE:= initialvalue ;
1003 -- Insert record for ps_node_id, ps_node_type;
1004
1005 SELECT cz_expression_nodes_s.nextval INTO l_expression_node_id1 FROM dual;
1006 v_cz_expr_node_count:=v_cz_expr_node_count+1;
1007
1008 IF p_model_ref_expl_id IS NULL THEN
1009 l_ps_node_id :=ps_node_id;
1010 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) ;
1011 ELSE
1012 l_mod_ref:=p_model_ref_expl_id; -- use model_ref_expl_id from parent contribute / consume rule .
1013 END IF;
1014
1015
1016
1017 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_NODE_ID:= l_expression_node_id1 ;
1018 v_cz_expression_nodes(v_cz_expr_node_count).SEQ_NBR:= '2' ;
1019 v_cz_expression_nodes(v_cz_expr_node_count).PS_NODE_ID:= ps_node_id ;
1020 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_PARENT_ID:= l_expression_node_id ;
1021 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_TYPE:= '205' ;
1022 v_cz_expression_nodes(v_cz_expr_node_count).TOKEN_LIST_SEQ:= '9' ;
1023 v_cz_expression_nodes(v_cz_expr_node_count).DELETED_FLAG:= '0' ;
1024 v_cz_expression_nodes(v_cz_expr_node_count).MODEL_REF_EXPL_ID:= l_mod_ref ;
1025 v_cz_expression_nodes(v_cz_expr_node_count).RULE_ID:= l_rule_id ;
1026 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_SIGNATURE_ID:='96' ;
1027 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_INDEX:= '2' ;
1028
1029 IF ps_node_type=PS_TYPE_TOTAL THEN
1030 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE:=CZ_TYPES.TOTAL_TYPEID;
1031 ELSIF ps_node_type=PS_TYPE_RESOURCE THEN
1032 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE:=CZ_TYPES.RESOURCE_TYPEID;
1033 ELSIF ps_node_type=PS_TYPE_FEATURE and p_feature_type=FEATURE_TYPE_FLOAT THEN
1034 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE:=CZ_TYPES.DECIMAL_FEATURE_TYPEID;
1035 END IF;
1036
1037 v_cz_expression_nodes(v_cz_expr_node_count).DISPLAY_NODE_DEPTH:='1' ;
1038 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_OFFSET:= '18' ;
1039 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_LENGTH:= '14' ;
1040 v_cz_expression_nodes(v_cz_expr_node_count).MUTABLE_FLAG:= '1' ;
1041
1042
1043 END IF;
1044 EXCEPTION
1045 WHEN others THEN
1046 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
1047 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
1048 RAISE;
1049 END createAccumulatorRule;
1050
1051
1052 --Procedure to create accumulator rule records for contribute/consume rule initial values
1053 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,
1054 p_ps_node_type IN cz_ps_nodes.ps_node_type%TYPE, feature_type IN cz_ps_nodes.feature_type%TYPE,
1055 initialvalue IN cz_ps_nodes.initial_value%TYPE, minvalue IN cz_ps_nodes.maximum%TYPE, rulefolderid IN cz_rules.rule_folder_id%TYPE,
1056 maxvalue IN cz_ps_nodes.maximum%TYPE) AS
1057 --cursor to find all contribute/consume sources for a given target
1058 CURSOR c_contrib_consume IS
1059 SELECT DISTINCT r.devl_project_id,
1060 e.template_id,
1061 e1.rule_id,
1062 r.rule_folder_id,
1063 ps.name,
1064 p.name devname,
1065 r.reason_id,
1066 e1.model_ref_expl_id
1067 FROM cz_rules r,
1068 cz_devl_projects p,
1069 cz_expression_nodes e,
1070 cz_expression_nodes e1,
1071 cz_ps_nodes ps
1072 WHERE r.deleted_flag = '0'
1073 AND e.deleted_flag = '0'
1074 AND e1.deleted_flag = '0'
1075 AND p.deleted_flag = '0'
1076 AND p.config_engine_type = 'F'
1077 AND e.template_id IN(708, 710 ,712 ,714)
1078 AND e1.ps_node_id = p_ps_node_id
1079 AND e1.ps_node_id = ps.ps_node_id
1080 AND e1.rule_id = e.rule_id
1081 AND r.rule_id = e.rule_id
1082 AND p.devl_project_id=ps.devl_project_id
1083 AND p.devl_project_id = p_devl_project_id
1084 ORDER BY r.devl_project_id , e1.model_ref_expl_id;
1085
1086 l_msg VARCHAR2(2000);
1087 l_api_name constant VARCHAR2(30) := 'createRules';
1088 l_rule_id cz_rules.rule_id%TYPE;
1089 l_rulefolderid cz_rules.rule_folder_id%TYPE;
1090 l_rule_class cz_rules.rule_class%TYPE;
1091 l_ps_node_name cz_ps_nodes.name%type;
1092 l_previous_devl_project_id cz_devl_projects.devl_project_id%type;
1093 l_has_mult_down_cont_cons boolean;
1094
1095 l_devl_project_id cz_devl_projects.devl_project_id%type;
1096 l_model_ref_expl_id cz_expression_nodes.model_ref_expl_id%type;
1097
1098
1099 ACC_RULE_CREATED boolean;
1100
1101
1102 BEGIN
1103
1104 ACC_RULE_CREATED:=FALSE;
1105 l_has_mult_down_cont_cons :=FALSE;
1106
1107 FOR v_contribute_consume IN c_contrib_consume
1108 LOOP
1109 displayMessage(3,p_devl_project_id , 'RULE' ,v_contribute_consume.rule_id,l_api_name,'CZ_CNV_CONT_CONS_TO_ADD_SUB');
1110 IF v_contribute_consume.template_id IN (708,710) THEN
1111 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);
1112 Update cz_expression_nodes set template_id=25 where rule_id=v_contribute_consume.rule_id and template_id =22;
1113 cz_rule_text_gen.parse_rules(v_contribute_consume.devl_project_id, v_contribute_consume.rule_id);
1114 END IF;
1115 IF(initialvalue IS NOT NULL
1116 AND(p_ps_node_type IN(PS_TYPE_TOTAL, PS_TYPE_RESOURCE) OR(p_ps_node_type = PS_TYPE_FEATURE
1117 AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT)))) THEN
1118 IF v_contribute_consume.devl_project_id = p_devl_project_id THEN
1119 ACC_RULE_CREATED:=TRUE;
1120 END IF;
1121
1122 IF ((l_devl_project_id IS NULL AND l_model_ref_expl_id IS NULL )
1123 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
1124
1125 -- Total/resource/Num feature
1126 --Create a new Rule Folder in model dev_project_id and get its id in l_rulefolderID
1127 l_rulefolderid := findOrCreateRuleFolder(v_contribute_consume.devl_project_id);
1128
1129 --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
1130 l_rule_id := createConstraintRuleRecord(l_rulefolderid, p_ps_node_id, v_contribute_consume.devl_project_id);
1131
1132 l_rule_class := RULE_CLASS_CONSTRAINT;
1133 -- create one additional Accumulator Rule to add the initial value to the target
1134 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);
1135 IF v_contribute_consume.devl_project_id = p_devl_project_id THEN
1136 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 );
1137 ELSE
1138 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);
1139 END IF;
1140
1141 l_devl_project_id:= v_contribute_consume.devl_project_id;
1142 l_model_ref_expl_id:= v_contribute_consume.model_ref_expl_id;
1143 END IF;
1144
1145 END IF;
1146
1147 UPDATE cz_localized_texts SET deleted_flag =1 WHERE intl_text_id=v_contribute_consume.reason_id;
1148 UPDATE cz_rules SET reason_id=null, reason_type=0, accumulator_flag=1 WHERE rule_id=v_contribute_consume.rule_id; -- fix for BUG7209057
1149
1150
1151 IF(l_previous_devl_project_id IS NULL) THEN
1152 l_previous_devl_project_id := v_contribute_consume.devl_project_id;
1153 ELSE IF (l_previous_devl_project_id<> v_contribute_consume.devl_project_id) THEN
1154 l_has_mult_down_cont_cons :=TRUE;
1155 END IF;
1156 END IF;
1157
1158 END LOOP;
1159
1160 IF (l_has_mult_down_cont_cons ) THEN
1161 displayMessage(2,p_devl_project_id , 'NODE' ,p_ps_node_id,l_api_name,'CZ_CNV_WARN_NUM_INIT_DOWN_SUM');
1162 END IF;
1163
1164
1165 -- Create accumulator rules for initial values
1166 IF initialvalue IS NOT NULL AND NOT ACC_RULE_CREATED THEN
1167 IF(p_ps_node_type = PS_TYPE_FEATURE
1168 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
1169 -- Total/resource/Num feature
1170 --Create a new Rule Folder in model dev_project_id and get its id in l_rulefolderID
1171 l_rulefolderid := findOrCreateRuleFolder(p_devl_project_id);
1172 --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
1173 l_rule_id := createConstraintRuleRecord(l_rulefolderid, p_ps_node_id, p_devl_project_id);
1174 l_rule_class := RULE_CLASS_CONSTRAINT;
1175 --defaults
1176 -- create one additional Accumulator Rule to add the initial value to the target
1177 createAccumulatorRule(p_devl_project_id, p_ps_node_id, p_ps_node_type, initialvalue, minvalue, l_rule_id , null, feature_type);
1178 select name into l_ps_node_name from cz_ps_nodes where ps_node_id =p_ps_node_id;
1179 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 );
1180 END IF;
1181 END IF;
1182
1183 IF initialvalue IS NOT NULL THEN
1184 IF(p_ps_node_type = PS_TYPE_FEATURE
1185 AND feature_type = FEATURE_TYPE_BOOLEAN) THEN
1186 -- Boolean
1187 --Create a new Rule Folder in model dev_project_id and get its id in l_rulefolderID
1188 l_rulefolderid := findOrCreateRuleFolder(p_devl_project_id);
1189 --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
1190 l_rule_id := createConstraintRuleRecord(l_rulefolderid, p_ps_node_id, p_devl_project_id, true );
1191
1192
1193 --refer section 4..1.3.3 for Boolean features
1194 insertImpliesRuleRecords(p_devl_project_id, p_ps_node_id, p_ps_node_type, l_rule_id, initialvalue );
1195 IF initialvalue IS NOT NULL THEN
1196 select name into l_ps_node_name from cz_ps_nodes where ps_node_id =p_ps_node_id;
1197 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 );
1198 END IF;
1199 END IF;
1200 END IF;
1201 EXCEPTION
1202 WHEN others THEN
1203 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
1204 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
1205 RAISE;
1206 END createRules;
1207
1208
1209 -- procedure to remove initial values from nodes
1210 PROCEDURE clearinitialvalues(p_dev_project_id IN cz_devl_projects.devl_project_id%TYPE) AS
1211 l_msg VARCHAR2(2000);
1212 l_api_name constant VARCHAR2(30) := 'ClearInitialValues';
1213 BEGIN
1214 UPDATE cz_ps_nodes
1215 SET initial_num_value = NULL
1216 WHERE devl_project_id = p_dev_project_id
1217 AND initial_num_value IS NOT NULL
1218 AND(ps_node_type IN(PS_TYPE_TOTAL, PS_TYPE_RESOURCE)
1219 OR(ps_node_type = PS_TYPE_FEATURE AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT))
1220 OR(ps_node_type = PS_TYPE_FEATURE AND feature_type = FEATURE_TYPE_BOOLEAN));
1221 EXCEPTION
1222 WHEN others THEN
1223 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
1224 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
1225 RAISE;
1226 END clearinitialvalues;
1227
1228
1229
1230 --procedure to fill in min/max domain range values per type where no value has been defined by the modeler
1231 --Reference TD section 4.1.3.2
1232 PROCEDURE assignDefaultMinMaxvalues(p_dev_project_id IN cz_devl_projects.devl_project_id%TYPE) AS
1233 l_msg VARCHAR2(2000);
1234 l_api_name constant VARCHAR2(30) := 'assignDefaultMinMaxvalues';
1235 BEGIN
1236 FOR c_processing IN
1237 (SELECT ps_node_id , feature_type
1238 FROM cz_ps_nodes
1239 WHERE devl_project_id = p_dev_project_id
1240 AND minimum IS NULL
1241 AND(ps_node_type = PS_TYPE_FEATURE
1242 AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT)))
1243 LOOP
1244 IF c_processing.feature_type= FEATURE_TYPE_INTEGER THEN
1245 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);
1246 ELSE IF c_processing.feature_type= FEATURE_TYPE_FLOAT THEN
1247 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);
1248 END IF;
1249 END IF;
1250 END LOOP;
1251 UPDATE cz_ps_nodes
1252 SET minimum = decode(feature_type, FEATURE_TYPE_INTEGER, INTEGER_MIN_VAL, FEATURE_TYPE_FLOAT, -SOLVER_MAX_DOUBLE)
1253 WHERE devl_project_id = p_dev_project_id
1254 AND minimum IS NULL
1255 AND(ps_node_type = PS_TYPE_FEATURE
1256 AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT));
1257
1258
1259 --
1260
1261 FOR c_processing IN
1262 (SELECT ps_node_id
1263 FROM cz_ps_nodes
1264 WHERE devl_project_id = p_dev_project_id
1265 AND ps_node_type = PS_TYPE_FEATURE
1266 AND feature_type=FEATURE_TYPE_LIST_OF_OPTIONS
1267 AND counted_options_flag='1')
1268 LOOP
1269 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);
1270 END LOOP;
1271
1272 UPDATE cz_ps_nodes
1273 SET MAX_QTY_PER_OPTION = fnd_profile.value('CZ_DEFAULT_MAX_QTY_INT')
1274 WHERE devl_project_id = p_dev_project_id
1275 AND ps_node_type = PS_TYPE_FEATURE
1276 AND feature_type=FEATURE_TYPE_LIST_OF_OPTIONS
1277 AND counted_options_flag='1';
1278
1279 --
1280
1281 FOR c_processing IN
1282 (SELECT ps_node_id, feature_type
1283 FROM cz_ps_nodes
1284 WHERE devl_project_id = p_dev_project_id
1285 AND maximum IS NULL
1286 AND(ps_node_type = PS_TYPE_FEATURE
1287 AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT , FEATURE_TYPE_LIST_OF_OPTIONS)))
1288 LOOP
1289
1290 IF c_processing.feature_type= FEATURE_TYPE_INTEGER OR c_processing.feature_type=FEATURE_TYPE_LIST_OF_OPTIONS THEN
1291 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);
1292 ELSE IF c_processing.feature_type= FEATURE_TYPE_FLOAT THEN
1293 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);
1294 END IF;
1295 END IF;
1296 END LOOP;
1297 UPDATE cz_ps_nodes
1298 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)
1299 WHERE devl_project_id = p_dev_project_id
1300 AND maximum IS NULL
1301 AND(ps_node_type = PS_TYPE_FEATURE
1302 AND feature_type IN(FEATURE_TYPE_INTEGER , FEATURE_TYPE_FLOAT));
1303
1304 --
1305 FOR c_processing IN
1306 (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
1307 FROM cz_ps_nodes
1308 WHERE devl_project_id = p_dev_project_id
1309 AND ((maximum IS NULL OR maximum =-1)
1310 AND(ps_node_type IN(PS_TYPE_BOM_MODEL, PS_TYPE_BOM_OPTION_CLASS, PS_TYPE_BOM_STD_ITEM)))
1311 )
1312 LOOP
1313 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);
1314 END LOOP;
1315
1316 UPDATE cz_ps_nodes
1317 SET maximum = decode(USE_BOM_DEFAULT_QTY,'TRUE',initial_num_value,decode(decimal_qty_flag, 1, SOLVER_MAX_DOUBLE, INTEGER_MAX_VALUE))
1318 WHERE devl_project_id = p_dev_project_id
1319 AND (maximum IS NULL OR maximum =-1)
1320 AND(ps_node_type IN( PS_TYPE_BOM_OPTION_CLASS, PS_TYPE_BOM_STD_ITEM)); -- Bug15986942, removed PS_TYPE_BOM_MODEL from IN clause
1321
1322 --- BUG 9467823 - Set maximum on non bom models - this is needed if MAX is not defined for the instantiable non-bom model
1323 FOR c_processing IN
1324 (SELECT ps_node_id ,decimal_qty_flag , INTEGER_MAX_VALUE changedValue
1325 FROM cz_ps_nodes
1326 WHERE devl_project_id = p_dev_project_id
1327 AND ((maximum IS NULL OR maximum =-1)
1328 AND instantiable_flag = NODE_INSTANTIABILITY_MULTIPLE
1329 AND (ps_node_type = PS_TYPE_REFERENCE OR ps_node_type = PS_TYPE_COMPONENT)
1330 AND item_id is null)
1331 )
1332 LOOP
1333 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);
1334 END LOOP;
1335
1336 UPDATE cz_ps_nodes
1337 SET maximum = INTEGER_MAX_VALUE
1338 WHERE devl_project_id = p_dev_project_id
1339 AND (maximum IS NULL OR maximum =-1)
1340 AND instantiable_flag = NODE_INSTANTIABILITY_MULTIPLE
1341 AND (ps_node_type = PS_TYPE_REFERENCE OR ps_node_type = PS_TYPE_COMPONENT)
1342 AND item_id is null;
1343
1344 ---Changed for bug 6737779
1345
1346 UPDATE cz_ps_nodes
1347 SET minimum = decode(USE_BOM_DEFAULT_QTY,'TRUE',initial_num_value,decode(decimal_qty_flag, 1,0 ,1 ))
1348 WHERE devl_project_id = p_dev_project_id
1349 AND (minimum IS NULL OR minimum =0)
1350 AND(ps_node_type IN( PS_TYPE_BOM_OPTION_CLASS, PS_TYPE_BOM_STD_ITEM)) -- Bug15986942, removed PS_TYPE_BOM_MODEL from IN clause
1351 RETURNING ps_node_id , minimum BULK COLLECT INTO v_cz_ids_tbl , v_cz_num_tbl;
1352
1353
1354 IF v_cz_ids_tbl.COUNT > 0 THEN
1355 FOR i IN v_cz_ids_tbl.FIRST .. v_cz_ids_tbl.LAST
1356 LOOP
1357 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));
1358 END LOOP;
1359 v_cz_ids_tbl.DELETE;
1360 v_cz_num_tbl.DELETE;
1361 END IF;
1362
1363 UPDATE cz_ps_nodes psout
1364 SET minimum_selected = decode(USE_BOM_DEFAULT_QTY,'TRUE',initial_num_value,decode(decimal_qty_flag, 1,0,1 ))
1365 WHERE devl_project_id = p_dev_project_id
1366 AND (minimum_selected IS NULL OR minimum_selected =0)
1367 AND(ps_node_type =PS_TYPE_REFERENCE)
1368 AND EXISTS( SELECT 1 FROM cz_ps_nodes WHERE ps_node_id= psout.component_id
1369 AND ps_node_type =PS_TYPE_BOM_MODEL )
1370 RETURNING ps_node_id , minimum_selected BULK COLLECT INTO v_cz_ids_tbl , v_cz_num_tbl;
1371
1372
1373 IF v_cz_ids_tbl.COUNT > 0 THEN
1374 FOR i IN v_cz_ids_tbl.FIRST .. v_cz_ids_tbl.LAST
1375 LOOP
1376 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));
1377 END LOOP;
1378 v_cz_ids_tbl.DELETE;
1379 v_cz_num_tbl.DELETE;
1380 END IF;
1381
1382
1383 --
1384
1385 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
1386 WHERE devl_project_id = p_dev_project_id
1387 AND (maximum_selected IS NULL OR maximum_selected =-1)
1388 AND(ps_node_type =PS_TYPE_REFERENCE)
1389 and item_id is not null
1390 )LOOP
1391
1392 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);
1393 END LOOP;
1394
1395 UPDATE cz_ps_nodes
1396 SET maximum_selected = decode(USE_BOM_DEFAULT_QTY,'TRUE',initial_num_value,decode(decimal_qty_flag, 1, SOLVER_MAX_DOUBLE, INTEGER_MAX_VALUE))
1397 WHERE devl_project_id = p_dev_project_id
1398 AND (maximum_selected IS NULL OR maximum_selected =-1)
1399 AND(ps_node_type =PS_TYPE_REFERENCE)
1400 and item_id is not null ;
1401
1402
1403
1404 ---
1405
1406
1407 UPDATE cz_ps_nodes
1408 SET maximum = INTEGER_MAX_VALUE
1409 WHERE devl_project_id = p_dev_project_id
1410 AND maximum IS NULL
1411 AND(ps_node_type IN(232))
1412 RETURNING ps_node_id BULK COLLECT INTO v_cz_ids_tbl;
1413
1414 IF v_cz_ids_tbl.COUNT>0 THEN
1415 FOR i IN v_cz_ids_tbl.FIRST.. v_cz_ids_tbl.LAST
1416 LOOP
1417 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 );
1418 END LOOP;
1419 v_cz_ids_tbl.DELETE;
1420 END IF;
1421
1422
1423
1424 ----- Update default maximum values for total and resource .
1425
1426 UPDATE cz_ps_nodes
1427 SET maximum = DECODE(SIGN(nvl(initial_num_value,0) - SOLVER_MAX_DOUBLE), -1 , SOLVER_MAX_DOUBLE, 0 , SOLVER_MAX_DOUBLE , 1 , initial_num_value)
1428 WHERE devl_project_id = p_dev_project_id
1429 AND maximum IS NULL
1430 AND ps_node_type IN(PS_TYPE_TOTAL,PS_TYPE_RESOURCE)
1431 RETURNING ps_node_id , maximum BULK COLLECT INTO v_cz_ids_tbl , v_cz_num_tbl;
1432
1433 IF v_cz_ids_tbl.COUNT > 0 THEN
1434 FOR i IN v_cz_ids_tbl.FIRST .. v_cz_ids_tbl.LAST
1435 LOOP
1436 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));
1437 END LOOP;
1438 v_cz_ids_tbl.DELETE;
1439 v_cz_num_tbl.DELETE;
1440 END IF;
1441
1442 ----
1443
1444 UPDATE cz_ps_nodes
1445 SET minimum = -SOLVER_MAX_DOUBLE
1446 WHERE devl_project_id = p_dev_project_id
1447 AND minimum IS NULL
1448 AND ps_node_type IN(PS_TYPE_TOTAL,PS_TYPE_RESOURCE)
1449 RETURNING ps_node_id BULK COLLECT INTO v_cz_ids_tbl;
1450
1451 IF v_cz_ids_tbl.COUNT>0 THEN
1452 FOR i IN v_cz_ids_tbl.FIRST..v_cz_ids_tbl.LAST
1453 LOOP
1454 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);
1455 END LOOP;
1456 v_cz_ids_tbl.DELETE;
1457 END IF;
1458
1459
1460 EXCEPTION
1461 WHEN others THEN
1462 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
1463 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
1464 RAISE;
1465 END assignDefaultMinMaxvalues;
1466
1467
1468 --procedure to convert existing rules to those suitable for the FCE
1469 --Refer section 4.1.4 rule requirements
1470 PROCEDURE convertRules(p_dev_project_id IN cz_devl_projects.devl_project_id%TYPE, p_rulefolderid IN cz_rules.rule_folder_id%TYPE) AS
1471
1472 -- cursor to fetch ATAN2 rules which will be converted into ATAN
1473 CURSOR c_atan IS
1474 SELECT ex.*
1475 FROM cz_expression_nodes ex,
1476 cz_rules ru
1477 WHERE ru.devl_project_id = p_dev_project_id
1478 AND ru.rule_id = ex.rule_id
1479 AND ru.deleted_flag ='0'
1480 AND ex.template_id = 430
1481 AND ex.deleted_flag ='0';
1482
1483 -- cursor to fetch data from rules having a numeric participant in a logic rule for a given devl_project_id
1484 CURSOR c_numeric_participant IS
1485 SELECT exp1.expr_node_id exp1id,
1486 exp2.expr_node_id exp2id,
1487 exp2.expr_parent_id exp2parentid,
1488 exp2.rule_id , psnode.ps_node_id
1489 FROM cz_expression_nodes exp1,
1490 cz_expression_nodes exp2,
1491 cz_ps_nodes psnode
1492 WHERE exp1.deleted_flag ='0'
1493 AND exp2.deleted_flag ='0'
1494 AND exp1.template_id IN(1, 2, 3, 4, 5)
1495 AND exp2.ps_node_id = psnode.ps_node_id
1496 AND exp1.rule_id = exp2.rule_id
1497 AND psnode.ps_node_type = PS_TYPE_FEATURE
1498 AND psnode.feature_type = FEATURE_TYPE_INTEGER
1499 AND psnode.minimum >= 0
1500 AND psnode.devl_project_id = p_dev_project_id
1501 AND exists (select 1 from cz_expression_nodes where
1502 expr_node_id=exp2.expr_parent_id
1503 and template_id in ( 306 ,307 ,360 ,552 ,21 ) -- All Logic operators
1504 );
1505
1506 --cursor to fetch compatibility rule where more than one participant feature has a maximum number of selections greater than 1
1507 CURSOR c_compat IS
1508 SELECT DISTINCT cz_rules.rule_id,
1509 cz_rules.devl_project_id
1510 FROM cz_rules,
1511 cz_expression_nodes exp1,
1512 cz_expression_nodes exp2,
1513 cz_expression_nodes exp3
1514 WHERE rule_type IN(RULE_TYPE_COMPAT_TABLE, RULE_TYPE_DESIGN_CHART_RULE, RULE_TEMPLATE_FREEFORM_RULE)
1515 AND exp1.rule_id = exp2.rule_id
1516 AND exp1.rule_id = exp3.rule_id
1517 AND exp3.template_id = 23
1518 AND exp1.rule_id = cz_rules.rule_id
1519 AND exp1.expr_type = 207
1520 AND exp2.expr_type = 207
1521 AND exp1.expr_node_id <> exp2.expr_node_id
1522 AND EXISTS
1523 (SELECT 1
1524 FROM cz_expression_nodes expin1,
1525 cz_expression_nodes expin2,
1526 cz_ps_nodes ps1,
1527 cz_ps_nodes ps2
1528 WHERE expin1.expr_node_id = exp1.expr_parent_id
1529 AND expin2.expr_node_id = exp2.expr_parent_id
1530 AND expin2.ps_node_id = ps2.ps_node_id
1531 AND expin1.ps_node_id = ps1.ps_node_id
1532 AND ((ps1.maximum_selected > 1
1533 AND ps2.maximum_selected > 1
1534 )
1535 OR
1536 (ps1.maximum > 1
1537 AND ps2.maximum > 1
1538 )
1539 )
1540 )
1541 AND cz_rules.devl_project_id = p_dev_project_id
1542 UNION ALL
1543 SELECT DISTINCT cf.rule_id ,ps.devl_project_id
1544 FROM cz_des_chart_features cf,
1545 cz_des_chart_features cf1,
1546 cz_ps_nodes ps,
1547 cz_ps_nodes ps1,
1548 cz_rules rule
1549 WHERE cf.rule_id = cf1.rule_id
1550 AND cf.feature_id = ps.ps_node_id
1551 AND cf1.feature_id = ps1.ps_node_id
1552 AND cf1.feature_id <> cf.feature_id
1553 AND ((ps.maximum_selected > 1
1554 AND ps1.maximum_selected > 1
1555 )
1556 OR
1557 (ps.maximum > 1
1558 AND ps1.maximum > 1
1559 )
1560 )
1561 AND rule.devl_project_id = p_dev_project_id
1562 AND rule.rule_id = cf.rule_id
1563 AND rule_type IN(24, 30);
1564
1565
1566 --cursor to fetch compatibility rule where one participant is a BOM Node
1567 CURSOR c_bom_compat IS
1568 SELECT DISTINCT cz_rules.rule_id,
1569 cz_rules.devl_project_id
1570 FROM cz_rules,
1571 cz_expression_nodes exp1,
1572 cz_expression_nodes exp2,
1573 cz_expression_nodes exp3
1574 WHERE rule_type IN(RULE_TYPE_COMPAT_TABLE, RULE_TYPE_DESIGN_CHART_RULE, RULE_TEMPLATE_FREEFORM_RULE)
1575 AND exp1.rule_id = exp2.rule_id
1576 AND exp1.rule_id = exp3.rule_id
1577 AND exp3.template_id = 23
1578 AND exp1.rule_id = cz_rules.rule_id
1579 AND exp1.expr_type = 207
1580 AND exp2.expr_type = 207
1581 AND exp1.expr_node_id <> exp2.expr_node_id
1582 AND EXISTS
1583 (SELECT 1
1584 FROM cz_expression_nodes expin1,
1585 cz_expression_nodes expin2,
1586 cz_ps_nodes ps1,
1587 cz_ps_nodes ps2
1588 WHERE expin1.expr_node_id = exp1.expr_parent_id
1589 AND expin2.expr_node_id = exp2.expr_parent_id
1590 AND expin2.ps_node_id = ps2.ps_node_id
1591 AND expin1.ps_node_id = ps1.ps_node_id
1592 AND (ps1.ps_node_type =PS_TYPE_BOM_MODEL
1593 OR ps2.ps_node_type =PS_TYPE_BOM_MODEL)
1594 )
1595 AND cz_rules.devl_project_id = p_dev_project_id
1596 UNION ALL
1597 SELECT DISTINCT cf.rule_id ,ps.devl_project_id
1598 FROM cz_des_chart_features cf,
1599 cz_des_chart_features cf1,
1600 cz_ps_nodes ps,
1601 cz_ps_nodes ps1,
1602 cz_rules rule
1603 WHERE cf.rule_id = cf1.rule_id
1604 AND cf.feature_id = ps.ps_node_id
1605 AND cf1.feature_id = ps1.ps_node_id
1606 AND cf1.feature_id <> cf.feature_id
1607 AND (ps.ps_node_type =PS_TYPE_BOM_MODEL OR ps1.ps_node_type =PS_TYPE_BOM_MODEL )
1608 AND rule.devl_project_id = p_dev_project_id
1609 AND rule.rule_id = cf.rule_id
1610 AND rule_type IN(24,30);
1611
1612 l_atan c_atan % rowtype;
1613 l_expression_node_id cz_expression_nodes.expr_node_id%TYPE;
1614 l_api_name constant VARCHAR2(30) := 'convertRules';
1615 l_numeric_participant c_numeric_participant%rowtype;
1616 minseq NUMBER;
1617 maxseq NUMBER;
1618 l_expression_node_id1 cz_expression_nodes.expr_node_id%TYPE;
1619 l_ps_node_name cz_ps_nodes.name%type;
1620 l_msg VARCHAR2(2000);
1621 l_cz_rules_id cz_rules.rule_id%TYPE;
1622 BEGIN
1623
1624 --Bug 6725690 , convert ZDIV operator to divide (/) operator.
1625
1626 UPDATE cz_expression_nodes
1627 SET template_id = 408
1628 WHERE template_id = 404
1629 AND rule_id IN
1630 (SELECT rule_id
1631 FROM cz_rules
1632 WHERE devl_project_id = p_dev_project_id
1633 AND deleted_flag ='0')
1634 AND deleted_flag ='0'
1635 RETURNING rule_id BULK COLLECT INTO v_cz_ids_tbl;
1636
1637 IF v_cz_ids_tbl.COUNT>0 THEN
1638 FOR i IN v_cz_ids_tbl.FIRST ..v_cz_ids_tbl.LAST
1639 LOOP
1640 cz_rule_text_gen.parse_rules(p_dev_project_id, v_cz_ids_tbl(i));
1641 END LOOP;
1642 v_cz_ids_tbl.DELETE;
1643 END IF;
1644
1645
1646 -- DEFAULTS rules will be converted into IMPLIES, with default rule type indicator
1647 --Refer section 4.1.4.1.
1648
1649 UPDATE cz_expression_nodes
1650 SET template_id = 2
1651 WHERE template_id = 5
1652 AND rule_id IN
1653 (SELECT rule_id
1654 FROM cz_rules
1655 WHERE devl_project_id = p_dev_project_id
1656 AND deleted_flag ='0')
1657 AND deleted_flag ='0'
1658 RETURNING rule_id BULK COLLECT INTO v_cz_ids_tbl;
1659
1660
1661
1662 FORALL l_cz_rules_id IN v_cz_ids_tbl.FIRST .. v_cz_ids_tbl.LAST
1663 UPDATE cz_rules SET RULE_CLASS=RULE_CLASS_DEFAULT
1664 WHERE rule_id=v_cz_ids_tbl(l_cz_rules_id) ;
1665
1666
1667 IF v_cz_ids_tbl.COUNT>0 THEN
1668 FOR i IN v_cz_ids_tbl.FIRST ..v_cz_ids_tbl.LAST
1669 LOOP
1670 --change defaults to implies
1671 displayMessage(2,p_dev_project_id , 'RULE' ,v_cz_ids_tbl(i),l_api_name,'CZ_CNV_WARN_DEFAULTS');
1672 --Call procedure to generate rule text from the expression tree
1673 populate_cz_tables(true);
1674 cz_rule_text_gen.parse_rules(p_dev_project_id, v_cz_ids_tbl(i));
1675 END LOOP;
1676 v_cz_ids_tbl.DELETE;
1677 END IF;
1678
1679
1680
1681 ---
1682
1683
1684 -- NotTrue rules will be converted into NOT.
1685 --Refer section 4.1.4.2
1686
1687 UPDATE cz_expression_nodes
1688 SET template_id = 552
1689 WHERE template_id = 360
1690 AND rule_id IN
1691 (SELECT rule_id
1692 FROM cz_rules
1693 WHERE devl_project_id = p_dev_project_id
1694 AND deleted_flag ='0')
1695 AND deleted_flag ='0'
1696 RETURNING rule_id BULK COLLECT INTO v_cz_ids_tbl;
1697
1698 IF v_cz_ids_tbl.COUNT>0 THEN
1699 FOR i IN v_cz_ids_tbl.FIRST .. v_cz_ids_tbl.LAST
1700 LOOP
1701 --change template_id from 360 to 552
1702 displayMessage(2,p_dev_project_id , 'RULE' ,v_cz_ids_tbl(i),l_api_name,'CZ_CNV_WARN_NOTTRUE');
1703 populate_cz_tables(true);
1704 cz_rule_text_gen.parse_rules(p_dev_project_id, v_cz_ids_tbl(i));
1705 END LOOP;
1706 v_cz_ids_tbl.DELETE;
1707 END IF;
1708
1709
1710
1711 -- ATAN2 rules will be converted into ATAN.
1712 --Refer section 4.1.4.3
1713 FOR l_atan IN c_atan
1714 LOOP
1715 SELECT cz_expression_nodes_s.nextval
1716 INTO l_expression_node_id
1717 FROM dual;
1718 --Insert new ATAN record
1719
1720 v_cz_expr_node_count:=v_cz_expr_node_count+1;
1721 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_NODE_ID:= l_expression_node_id ;
1722 v_cz_expression_nodes(v_cz_expr_node_count).SEQ_NBR:= '1' ;
1723 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_PARENT_ID:= l_atan.expr_parent_id ;
1724 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_TYPE:= '200' ;
1725 v_cz_expression_nodes(v_cz_expr_node_count).TOKEN_LIST_SEQ:= '8' ;
1726 v_cz_expression_nodes(v_cz_expr_node_count).RULE_ID:= l_atan.rule_id ;
1727 v_cz_expression_nodes(v_cz_expr_node_count).TEMPLATE_ID:= '438' ;
1728 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_SIGNATURE_ID:='91' ;
1729 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_INDEX:= '1' ;
1730 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE:= '2' ;
1731 v_cz_expression_nodes(v_cz_expr_node_count).COLLECTION_FLAG:= '0' ;
1732 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_OFFSET:= '12' ;
1733 v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_LENGTH:= '4' ;
1734 v_cz_expression_nodes(v_cz_expr_node_count).MUTABLE_FLAG:= '0' ;
1735
1736
1737 --update Atan2 to DIV record
1738 UPDATE cz_expression_nodes
1739 SET template_id = 408,
1740 expr_parent_id = l_expression_node_id
1741 WHERE expr_node_id = l_atan.expr_node_id;
1742 displayMessage(2,p_dev_project_id , 'RULE' ,l_atan.rule_id,l_api_name,'CZ_CNV_WARN_ATAN2_REMOVED');
1743 populate_cz_tables(true);
1744 cz_rule_text_gen.parse_rules(p_dev_project_id, l_atan.rule_id);
1745 END LOOP;
1746 --
1747 -- Logic rules may only contain logical expressions as participants.
1748 --Any numeric participants in existing logic rules will be mapped to "expr > 0".
1749 --Refer section 4.1.4.6
1750
1751 ---make this logic rule a statement rule
1752 UPDATE CZ_RULES set presentation_flag=0 where rule_id in
1753 (SELECT exp2.rule_id
1754 FROM cz_expression_nodes exp1,
1755 cz_expression_nodes exp2,
1756 cz_ps_nodes psnode
1757 WHERE exp1.deleted_flag ='0'
1758 AND exp2.deleted_flag ='0'
1759 AND exp1.template_id IN(1, 2, 3, 4, 5)
1760 AND exp2.ps_node_id = psnode.ps_node_id
1761 AND exp1.rule_id = exp2.rule_id
1762 AND psnode.ps_node_type = PS_TYPE_FEATURE
1763 AND psnode.feature_type = FEATURE_TYPE_INTEGER
1764 AND psnode.minimum >= 0
1765 AND psnode.devl_project_id = p_dev_project_id);
1766
1767
1768
1769 FOR l_numeric_participant IN c_numeric_participant
1770 LOOP
1771
1772
1773 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
1774 LOOP
1775
1776 --make this as a statement rule
1777
1778 update cz_expression_nodes set expr_type=200 where expr_type=222 and rule_id=l_numeric_participant.rule_id;
1779
1780
1781 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);
1782 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))
1783 where rule_id= l_numeric_participant.rule_id and seq_nbr between minseq+1 and maxseq-2 ;
1784
1785 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))
1786 where rule_id= l_numeric_participant.rule_id and seq_nbr >maxseq;
1787
1788
1789 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))
1790 where rule_id= l_numeric_participant.rule_id and seq_nbr in (minseq,maxseq);
1791
1792 update cz_expression_nodes set expr_parent_id=null , seq_nbr=1 where expr_node_id=l_numeric_participant.exp1id;
1793 END LOOP;
1794
1795 END LOOP;
1796
1797
1798
1799 FOR l_numeric_participant IN c_numeric_participant
1800 LOOP
1801 SELECT cz_expression_nodes_s.nextval
1802 INTO l_expression_node_id
1803 FROM dual;
1804 -- > record
1805
1806 v_cz_expr_node_count:=v_cz_expr_node_count+1;
1807 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_NODE_ID :=l_expression_node_id ;
1808 v_cz_expression_nodes(v_cz_expr_node_count).SEQ_NBR :='1' ;
1809 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_PARENT_ID :=l_numeric_participant.exp2parentid ;
1810 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_TYPE :='200' ;
1811 v_cz_expression_nodes(v_cz_expr_node_count).TOKEN_LIST_SEQ :='10' ;
1812 v_cz_expression_nodes(v_cz_expr_node_count).RULE_ID :=l_numeric_participant.rule_id ;
1813 v_cz_expression_nodes(v_cz_expr_node_count).TEMPLATE_ID :='350' ;
1814 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_SIGNATURE_ID :='81' ;
1815 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_INDEX :='1' ;
1816 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE :='3' ;
1817 v_cz_expression_nodes(v_cz_expr_node_count).COLLECTION_FLAG :='0' ;
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' ;
1820 v_cz_expression_nodes(v_cz_expr_node_count).MUTABLE_FLAG :='0' ;
1821
1822
1823
1824
1825 UPDATE cz_expression_nodes
1826 SET expr_parent_id = l_expression_node_id,
1827 seq_nbr = 1
1828 WHERE expr_node_id = l_numeric_participant.exp2id;
1829 -- make count feature child of >
1830 SELECT cz_expression_nodes_s.nextval
1831 INTO l_expression_node_id1
1832 FROM dual;
1833 -- insert 0 record
1834
1835 v_cz_expr_node_count:=v_cz_expr_node_count+1;
1836 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_NODE_ID :=l_expression_node_id1 ;
1837 v_cz_expression_nodes(v_cz_expr_node_count).SEQ_NBR :='2' ;
1838 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_PARENT_ID :=l_expression_node_id ;
1839 v_cz_expression_nodes(v_cz_expr_node_count).EXPR_TYPE :='201' ;
1840 v_cz_expression_nodes(v_cz_expr_node_count).TOKEN_LIST_SEQ :='10' ;
1841 v_cz_expression_nodes(v_cz_expr_node_count).RULE_ID :=l_numeric_participant.rule_id ;
1842 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_SIGNATURE_ID :='2069' ;
1843 v_cz_expression_nodes(v_cz_expr_node_count).PARAM_INDEX :='1' ;
1844 v_cz_expression_nodes(v_cz_expr_node_count).DATA_TYPE :='1' ;
1845 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_OFFSET :='4' ;
1846 -- v_cz_expression_nodes(v_cz_expr_node_count).SOURCE_LENGTH :='38' ;
1847 v_cz_expression_nodes(v_cz_expr_node_count).MUTABLE_FLAG :='0' ;
1848 v_cz_expression_nodes(v_cz_expr_node_count).DATA_NUM_VALUE :='0' ;
1849
1850
1851 -- insert record for const 0 under > record
1852 SELECT name INTO l_ps_node_name from cz_ps_nodes where ps_node_id=l_numeric_participant.ps_node_id;
1853 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);
1854 DELETE FROM cz_expression_nodes where rule_id= l_numeric_participant.rule_id and template_id=21;
1855 populate_cz_tables(true);
1856 cz_rule_text_gen.parse_rules(p_dev_project_id, l_numeric_participant.rule_id);
1857 END LOOP;
1858
1859 -- display warning for compatibility rules design chart /property based /explicit compatibility
1860 FOR l_compat IN c_compat
1861 LOOP
1862 displayMessage(1,p_dev_project_id , 'RULE' ,l_compat.rule_id,l_api_name,'CZ_CNV_FAIL_COMPAT_MAXSEL');
1863 END LOOP;
1864
1865
1866
1867 -- LCE supported the use of BOM Models as participants in compatibility rules,
1868 -- however FCE will not support this. Model Conversion should fail any
1869 -- compatibility rule (explicit, property-based, design chart, or CDL
1870 -- equivalent) that has a BOM Model node as a participant. Ref bug 6488867
1871
1872 FOR l_compat IN c_bom_compat
1873 LOOP
1874 displayMessage(1,p_dev_project_id , 'RULE' ,l_compat.rule_id,l_api_name,'CZ_CNV_FAIL_BOM_NODE_COMPAT');
1875 END LOOP;
1876
1877
1878 -- Remove binding for onValidateEligibleTarget event
1879 -- Refer section 4.1.4.7
1880
1881
1882 --fetch Event bindings bound to the onValidateEligibleTarget event
1883
1884 UPDATE cz_expression_nodes
1885 SET deleted_flag = '1'
1886 WHERE expr_node_id
1887 IN (
1888 SELECT expr_node_id
1889 FROM cz_rules czrules ,cz_expression_nodes EXP
1890 WHERE rule_type = RULE_TYPE_CONFIGURATION_EXT
1891 and expr_type = 216
1892 AND EXP.rule_id = czrules.rule_id
1893 AND EXP.argument_signature_id = 2204
1894 AND czrules.deleted_flag ='0'
1895 AND exp.deleted_flag ='0'
1896 AND devl_project_id=p_dev_project_id
1897 )
1898 RETURNING rule_id BULK COLLECT INTO v_cz_ids_tbl ;
1899 --mark this binding as deleted
1900
1901 IF v_cz_ids_tbl.COUNT >0 THEN
1902 FOR i IN v_cz_ids_tbl.FIRST .. v_cz_ids_tbl.LAST
1903 LOOP
1904 displayMessage(2,p_dev_project_id , 'RULE' ,v_cz_ids_tbl(i),l_api_name,'CZ_CNV_WARN_ON_VALIDATE_REM');
1905 END LOOP;
1906 v_cz_ids_tbl.DELETE;
1907 END IF;
1908
1909 --Arguments to Configurator Extension event bindings now require the use of new Java interfaces.
1910
1911 SELECT DISTINCT czrules.rule_id
1912 BULK COLLECT INTO v_cz_ids_tbl
1913 FROM cz_rules czrules,
1914 cz_expression_nodes exp
1915 WHERE rule_type = RULE_TYPE_CONFIGURATION_EXT
1916 AND expr_type = 216
1917 AND exp.rule_id = czrules.rule_id
1918 AND czrules.deleted_flag = '0'
1919 AND exp.deleted_flag = '0'
1920 AND devl_project_id = p_dev_project_id;
1921
1922
1923 IF v_cz_ids_tbl.COUNT >0 THEN
1924 FOR i IN v_cz_ids_tbl.FIRST .. v_cz_ids_tbl.LAST
1925 LOOP
1926 displayMessage(1,p_dev_project_id , 'RULE' ,v_cz_ids_tbl(i),l_api_name,'CZ_CNV_ERR_BIND_EVT');
1927 END LOOP;
1928 v_cz_ids_tbl.DELETE;
1929 END IF;
1930
1931 --Display warning For each Configurator Extension bound to an event that can occur during search.
1932 --Refer section 4.1.4.8
1933
1934
1935
1936 -- fetch records for Configurator Extension bound to an event that can occur during search.
1937
1938
1939 FOR v_rule_ids IN (
1940 SELECT czrules.rule_id,
1941 expout.expr_node_id
1942 FROM cz_rules czrules,
1943 cz_expression_nodes expout
1944 WHERE czrules.rule_type = RULE_TYPE_CONFIGURATION_EXT
1945 AND czrules.rule_id = expout.rule_id
1946 AND EXISTS
1947 (SELECT 1
1948 FROM cz_expression_nodes EXP
1949 WHERE EXP.expr_type = 216
1950 AND EXP.rule_id = czrules.rule_id
1951 AND EXP.argument_signature_id IN(2209, --postInstanceAdd
1952 2210, --postInstanceDelete
1953 2215, --postConnect
1954 2216, --postDisconnect
1955 2217 --postValueChange
1956 --todo to do find values for 'onValueBound'--'preInstanceDelete',--'preInstanceAdd',
1957 )
1958 AND EXP.deleted_flag ='0')
1959 AND expout.deleted_flag ='0'
1960 AND czrules.deleted_flag ='0'
1961 AND devl_project_id = p_dev_project_id
1962 )
1963 LOOP
1964 displayMessage(3,p_dev_project_id , 'RULE' ,v_rule_ids.rule_id,l_api_name,'CZ_CNV_ADV_CX_AUTOCOMPLETE');
1965 END LOOP;
1966
1967 EXCEPTION
1968 WHEN others THEN
1969 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
1970 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
1971 RAISE;
1972 END convertRules;
1973
1974
1975 --This procedure removes effectivity info and displays messages for conversion of total and resources to float and
1976 -- also messages for contribute/consume rule conversion.
1977 PROCEDURE processModel(p_dev_project_id IN cz_devl_projects.devl_project_id%TYPE, p_rule_folderid cz_rules.rule_folder_id%TYPE) AS
1978 --Cursor to fetch Multi-Instantiable BOM Model Reference
1979 CURSOR c_bom_minmax IS
1980 SELECT ps_node_id
1981 FROM cz_ps_nodes psout
1982 WHERE ps_node_type = PS_TYPE_REFERENCE
1983 AND devl_project_id = p_dev_project_id
1984 AND instantiable_flag = NODE_INSTANTIABILITY_MULTIPLE
1985 AND component_id IN
1986 (SELECT ps_node_id
1987 FROM cz_ps_nodes psin
1988 WHERE ps_node_type = PS_TYPE_BOM_MODEL)
1989 ;
1990 l_msg VARCHAR2(2000);
1991 l_api_name constant VARCHAR2(30) := 'processModel';
1992 BEGIN
1993
1994 FOR v_ps_node IN
1995 (SELECT ps_node_id,
1996 ps_node_type,
1997 feature_type,
1998 nvl(initial_value,initial_num_value ) initial_value,
1999 minimum,
2000 maximum,
2001 virtual_flag,name, ROWID
2002 FROM cz_ps_nodes
2003 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)
2004 OR(ps_node_type = PS_TYPE_COMPONENT AND virtual_flag = 1)) AND devl_project_id = p_dev_project_id)
2005 LOOP
2006 -- kdande; 09-Jan-2008; Bug 6722494
2007 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
2008 UPDATE cz_ps_nodes
2009 SET user_input_required_flag = DECODE (v_ps_node.minimum, 1, '1', '0')
2010 WHERE ROWID = v_ps_node.ROWID;
2011 END IF;
2012
2013 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);
2014 -- Totals and Resources will be converted to float Totals and Resources.
2015 --Reference section 4.1.3.4
2016 IF v_ps_node.ps_node_type IN(PS_TYPE_TOTAL, PS_TYPE_RESOURCE) THEN
2017 IF v_ps_node.ps_node_type = PS_TYPE_TOTAL THEN
2018 displayMessage(3,p_dev_project_id , 'NODE' ,v_ps_node.ps_node_id,l_api_name,'CZ_CNV_ADV_MAP_TO_DECIMAL');
2019 ELSE
2020 displayMessage(3,p_dev_project_id , 'NODE' ,v_ps_node.ps_node_id,l_api_name,'CZ_CNV_ADV_RES_TO_DECIMAL');
2021 END IF;
2022 END IF;
2023 END LOOP;
2024
2025
2026 -- Effectivity is no longer supported on model node types Total, Resource, Integer Feat,
2027 -- Decimal Feature, and Virtual Component. All Effectivity-related information should be
2028 -- cleared for nodes of these types .Reference TD section 4.1.3.1.
2029 removeEffectivityInfo(p_dev_project_id);
2030 -- Settings for Initial Minimum and Maximum Instances removed; BOM Maximum Quantity setting now defines the
2031 --total Quantity allowed across all Instances.
2032
2033 UPDATE cz_ps_nodes psout
2034 SET maximum = NULL,
2035 minimum = NULL
2036 WHERE ps_node_type = PS_TYPE_REFERENCE
2037 AND devl_project_id = p_dev_project_id
2038 AND instantiable_flag = NODE_INSTANTIABILITY_MULTIPLE
2039 AND component_id IN (SELECT ps_node_id
2040 FROM cz_ps_nodes psin
2041 WHERE ps_node_type = PS_TYPE_BOM_MODEL);
2042
2043 FOR v_ps_node_id IN c_bom_minmax
2044 LOOP
2045 displayMessage(2,p_dev_project_id , 'NODE' ,v_ps_node_id.ps_node_id,l_api_name,'CZ_CNV_WARN_BOM_INIT_VAL_REM');
2046 END LOOP;
2047 --Assign Default domain values as per section 4.1.3.2
2048 assignDefaultMinMaxvalues(p_dev_project_id);
2049
2050
2051 EXCEPTION
2052 WHEN others THEN
2053 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
2054 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
2055 RAISE;
2056 END processModel;
2057
2058
2059 --Note this procedure is called from model conversion concurrent program
2060 PROCEDURE convertModels(p_model_conversion_set_id IN NUMBER) AS
2061 l_rule_folderid cz_rule_folders.rule_folder_id%TYPE;
2062 l_msg VARCHAR2(2000);
2063 l_api_name constant VARCHAR2(30) := 'convertModels';
2064 l_run_id NUMBER;
2065 BEGIN
2066
2067
2068 v_cz_expr_node_count := 0;
2069 v_cz_expression_nodes.DELETE;
2070
2071 v_cz_rule_count := 0;
2072 V_CZ_RULES.DELETE;
2073
2074 v_model_conversion_set_id:=p_model_conversion_set_id;
2075 FOR v_models IN
2076 ( SELECT remote_model_id
2077 FROM cz_model_publications p , cz_pb_model_exports z
2078 WHERE p.export_status IN('OK')
2079 AND p.server_id = 0
2080 AND p.publication_mode = 'M'
2081 AND p.migration_group_id = p_model_conversion_set_id
2082 AND z.publication_id = p.publication_id
2083 AND z.model_id = p.object_id
2084 AND z.server_id = 0
2085 AND z.status = 'OK'
2086 and p.source_target_flag='S'
2087 AND p.deleted_flag='0'
2088 )
2089 LOOP
2090
2091 displayMessage(0,v_models.remote_model_id , NULL ,v_models.remote_model_id,l_api_name, 'Model record');
2092
2093
2094 --mark this model as a fusion model
2095 --Bug 9176281
2096 EXECUTE IMMEDIATE ' BEGIN '
2097 ||' UPDATE cz_devl_projects'
2098 ||' SET config_engine_type = ''F'','
2099 ||' post_migr_change_flag = NULL'
2100 ||' WHERE devl_project_id = :1;'
2101 ||' COMMIT;'
2102 ||' END;' USING v_models.remote_model_id;
2103
2104 l_rule_folderid := findOrCreateRuleFolder(v_models.remote_model_id);
2105 --Handle model conversion
2106 processModel(v_models.remote_model_id, l_rule_folderid);
2107 --Handle UI conversion
2108 processUI(v_models.remote_model_id);
2109 convertRules(v_models.remote_model_id, l_rule_folderid);
2110
2111
2112 END LOOP;
2113
2114 --Dump rules data for newly created rules to the database tables .
2115 populate_cz_tables(true);
2116 --Mark rule_class=0 and config_engine_type='F' for all rules having null values for these fields
2117 UPDATE cz_rules
2118 SET config_engine_type = 'F' , rule_class=nvl(rule_class,0)
2119 WHERE deleted_flag='0'
2120 AND devl_project_id IN( SELECT remote_model_id
2121 FROM cz_model_publications p , cz_pb_model_exports z
2122 WHERE p.export_status IN('OK')
2123 AND p.server_id = 0
2124 AND p.publication_mode = 'M'
2125 AND p.migration_group_id = p_model_conversion_set_id
2126 AND z.publication_id = p.publication_id
2127 AND z.model_id = p.object_id
2128 AND z.server_id = 0
2129 AND z.status = 'OK'
2130 and p.source_target_flag='S'
2131 AND p.deleted_flag='0');
2132
2133
2134 -- Clearing Initial values need to be done at last for all models
2135 -- as some models may have a downward contribute/consume and hence may require the initial values for accumulator rule creation
2136 FOR v_models IN
2137 ( SELECT remote_model_id
2138 FROM cz_model_publications p , cz_pb_model_exports z
2139 WHERE p.export_status IN('OK')
2140 AND p.server_id = 0
2141 AND p.publication_mode = 'M'
2142 AND p.migration_group_id = p_model_conversion_set_id
2143 AND z.publication_id = p.publication_id
2144 AND z.model_id = p.object_id
2145 AND z.server_id = 0
2146 AND z.status = 'OK'
2147 and p.source_target_flag='S'
2148 AND p.deleted_flag='0'
2149 )
2150 LOOP
2151 --Clear Initial values for certain node types
2152 clearinitialvalues(v_models.remote_model_id);
2153
2154 /************** For now donot call logicgen
2155 --After processing rules and when done with conversion process for this model run logicgen .
2156 l_run_id:=0;
2157 BEGIN
2158 cz_fce_compile.compile_logic(v_models.remote_model_id,l_run_id);
2159 EXCEPTION WHEN OTHERS THEN
2160 NULL; -- we donot want conversion to fail if logicgen reported an error .
2161 END;
2162 ***************/
2163 END LOOP;
2164 --Do Cleanup
2165 v_cz_expr_node_count := 0;
2166 v_cz_expression_nodes.DELETE;
2167 v_cz_rule_count := 0;
2168 V_CZ_RULES.DELETE;
2169 v_model_conversion_set_id:=NULL;
2170 EXCEPTION
2171 WHEN others THEN
2172 v_cz_expr_node_count := 0;
2173 v_cz_expression_nodes.DELETE;
2174 v_cz_rule_count := 0;
2175 V_CZ_RULES.DELETE;
2176 l_msg := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
2177 log_msg(l_api_name, v_ndebug, l_msg, fnd_log.level_unexpected);
2178 v_model_conversion_set_id:=NULL;
2179 RAISE;
2180 END convertModels;
2181
2182
2183
2184
2185 ---------------------------------------------------------------------------------------
2186 /*
2187 * Copy Model For Conversionprocedure.
2188 * @param errbuf Standard Oracle Concurrent Program output parameters.
2189 * @param retcode Standard Oracle Concurrent Program output parameters.
2190 * @param p_request_id This is the CZ_MODEL_PUBLICATIONS, MIGRATION_GROUP_ID of the migration request.
2191 * Migration request is created by Developer and contains the list of all models selected
2192 * for Migration from the source's Configurator Repository, target Instance name and
2193 * target Repository Folder.
2194 */
2195
2196 PROCEDURE copy_model_for_conversion(errbuf OUT NOCOPY VARCHAR2,
2197 retcode OUT NOCOPY NUMBER,
2198 p_request_id IN NUMBER
2199 ) IS
2200 l_status VARCHAR2(3);
2201 l_publication_id NUMBER;
2202 l_run_id NUMBER := 0;
2203 l_mig_group_found BOOLEAN :=FALSE;
2204 l_api_name CONSTANT VARCHAR2(30) := 'copy_model_for_conversion';
2205 PUB_ERROR EXCEPTION;
2206 BEGIN
2207
2208 retcode:=0;
2209 cz_pb_mgr.GLOBAL_EXPORT_RETCODE := 0;
2210
2211 FOR c_pub IN (SELECT publication_id ,max(node_depth) mdepth FROM cz_model_publications mp ,cz_model_ref_expls mr
2212 WHERE mp.migration_group_id = p_request_id AND mp.deleted_flag = '0' and mr.deleted_flag = '0'
2213 AND mp.publication_mode='M'
2214 and mr.model_id =mp.object_id
2215 group by publication_id
2216 order by mdepth asc
2217 )LOOP
2218
2219 l_mig_group_found :=TRUE;
2220 cz_pb_mgr.publish_model(c_pub.publication_id, l_run_id, l_status);
2221 IF l_status = cz_pb_mgr.PUBLICATION_ERROR THEN
2222 RAISE PUB_ERROR;
2223 END IF;
2224
2225 errbuf := NULL;
2226 IF(cz_pb_mgr.GLOBAL_EXPORT_RETCODE = 1)THEN
2227 errbuf := CZ_UTILS.GET_TEXT('CZ_MM_WARNING');
2228 ELSIF(cz_pb_mgr.GLOBAL_EXPORT_RETCODE = 2) THEN
2229 errbuf := CZ_UTILS.GET_TEXT('CZ_MM_FAILURE');
2230 END IF;
2231 END LOOP;
2232
2233 IF NOT l_mig_group_found THEN
2234 errbuf := cz_utils.get_text('CZ_INVALID_MIGR_GROUP_NUMBER', 'MIGRGRP', p_request_id);
2235 log_msg(l_api_name, v_ndebug, errbuf , FND_LOG.LEVEL_PROCEDURE);
2236 raise_application_error('-20020', 'INVALID_MIGRATION_GROUP');
2237 END IF;
2238
2239 retcode := cz_pb_mgr.GLOBAL_EXPORT_RETCODE;
2240
2241 EXCEPTION
2242 WHEN OTHERS THEN
2243 retcode := 2;
2244 log_msg(l_api_name, v_ndebug, errbuf, fnd_log.level_unexpected);
2245 errbuf := CZ_UTILS.GET_TEXT('CZ_MM_UNEXPECTED');
2246 RAISE;
2247 END;
2248
2249
2250 Procedure Model_Convert_CP(errbuf out nocopy varchar2,
2251 Retcode out nocopy number,
2252 P_request_id in number default null) is
2253 req_data varchar2(10);
2254 r number;
2255 i number;
2256 b boolean;
2257 noofreports number:=0;
2258
2259 call_status boolean;
2260 rphase varchar2(80);
2261 rstatus varchar2(80);
2262 dphase varchar2(30);
2263 dstatus varchar2(30);
2264 message varchar2(240);
2265 L_API_NAME CONSTANT VARCHAR2(30) := 'Model_Convert_CP';
2266 L_RETURN_STATUS VARCHAR2(100) ;
2267 L_MSG_COUNT NUMBER ;
2268 L_MSG_DATA VARCHAR2(2000) ;
2269 ERR_PROFILE EXCEPTION;
2270 l_migration_group_id cz_model_publications.migration_group_id%TYPE;
2271 L_ERROR_IN_CP BOOLEAN;
2272
2273 Begin
2274
2275 fnd_msg_pub.initialize;
2276 cz_model_convert.CONVERT_MODEL:=TRUE;
2277 L_ERROR_IN_CP :=FALSE;
2278
2279 IF fnd_profile.value('CZ_BOM_DEFAULT_QTY_DOMN')='Y' THEN
2280 USE_BOM_DEFAULT_QTY:='TRUE';
2281 ELSE
2282 USE_BOM_DEFAULT_QTY:='FALSE';
2283 END IF;
2284
2285
2286 --get integer and decimal max values from profiles
2287
2288
2289 INTEGER_MAX_VALUE := fnd_profile.value('CZ_DEFAULT_MAX_QTY_INT');
2290 SOLVER_MAX_DOUBLE := fnd_profile.value('CZ_DEFAULT_MAX_QTY_DEC');
2291
2292 IF (INTEGER_MAX_VALUE IS NULL OR SOLVER_MAX_DOUBLE IS NULL) THEN
2293 log_msg('Model_Convert_CP', 0, cz_utils.get_text('CZ_CNV_ERR_PROFILE_NOT_SET') , fnd_log.level_unexpected);
2294 RAISE ERR_PROFILE;
2295 END IF;
2296
2297
2298 INTEGER_MIN_VAL := INTEGER_MAX_VALUE * -1;
2299
2300
2301
2302 SELECT COUNT(*)
2303 INTO noofreports
2304 FROM
2305 (SELECT DISTINCT migration_group_id
2306 FROM cz_model_publications
2307 WHERE deleted_flag ='0'
2308 AND publication_mode = 'M'
2309 AND(migration_group_id = p_request_id OR(p_request_id IS NULL AND migration_group_id IS NOT NULL))
2310 AND export_status = 'PEN'
2311 AND server_id = 0);
2312
2313 IF(noofreports=0 and fnd_conc_global.request_data IS NULL ) THEN
2314 log_msg('Model_Convert_CP', 0, 'No pending request with the supplied Conversion Set ID', fnd_log.level_unexpected);
2315 errbuf := 'Error in model conversion!';
2316 retcode := 2;
2317 raise ERR_PROFILE;
2318
2319 END IF;
2320
2321 For c_model_conv IN (
2322 SELECT DISTINCT migration_group_id
2323 FROM cz_model_publications mp
2324 WHERE deleted_flag ='0' and
2325 publication_mode='M'
2326 AND server_id = 0
2327 AND export_status = 'PEN'
2328 AND (migration_group_id = p_request_id
2329 OR(P_request_id is null AND migration_group_id IS NOT NULL ))
2330 ) LOOP
2331
2332 BEGIN
2333 BEGIN
2334
2335 l_migration_group_id:=c_model_conv.migration_group_id;
2336 log_msg('Model_Convert_CP', 0, 'The Migration Group id that is in process is '||l_migration_group_id, fnd_log.level_unexpected);
2337 --
2338 -- Read the value from REQUEST_DATA. If this is the PL/SQL APIs for Concurrent Processing
2339 -- first run of the program, then this value will be
2340 -- null.
2341 -- Otherwise, this will be the value that we passed to
2342 -- SET_REQ_GLOBALS on the previous run.
2343 --
2344 req_data := fnd_conc_global.request_data;
2345
2346 -- If this is the first run, well set i = 1.
2347 -- Otherwise we will set i = request_data + 1, and we will
2348 -- exit if we are done.
2349 --
2350 IF (req_data is not null) then
2351 i := to_number(req_data);
2352 i := i + 1;
2353 IF (i > noofreports+1 ) THEN
2354 errbuf := 'Done!';
2355 retcode := 0 ;
2356 return;
2357 END IF;
2358 ELSE
2359 i := 1;
2360 END IF;
2361 --this procedures creates copy of the existing model
2362 copy_model_for_conversion(errbuf, Retcode, c_model_conv.migration_group_id);
2363 commit;
2364 UPDATE cz_model_publications set export_status='OK' where export_status='PEN' and migration_group_id=c_model_conv.migration_group_id;
2365 --this procedure converts the copied LCE models to FCE standard
2366 convertModels(c_model_conv.migration_group_id);
2367 commit;
2368 --
2369 -- Submit the child request. The sub_request parameter
2370 -- must be set to 'Y'.
2371 --
2372 b:=fnd_request.ADD_LAYOUT (
2373 TEMPLATE_APPL_NAME => 'CZ',
2374 TEMPLATE_CODE => 'CZ_MDLCONV',
2375 TEMPLATE_LANGUAGE => 'EN',
2376 TEMPLATE_TERRITORY => 'US',
2377 OUTPUT_FORMAT => 'PDF'
2378 );
2379
2380 --Submit a new sub request for generating a XML publisher report
2381 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);
2382
2383 IF r = 0 THEN
2384 --
2385 -- If request submission failed, exit with error.
2386 --
2387 errbuf := fnd_message.get;
2388 retcode := 2;
2389 ELSE
2390 --
2391 log_msg('Model_Convert_CP', 0 ,
2392 '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);
2393 errbuf := 'Request submitted!';
2394 retcode := 0 ;
2395 END IF;
2396
2397 COMMIT;
2398 EXCEPTION WHEN OTHERS THEN
2399 L_ERROR_IN_CP := TRUE;
2400 retcode := 2;
2401 errbuf := cz_utils.get_text('CZ_CNV_UNEXPECTED');
2402 log_msg('Model_Convert_CP', 0, 'Error submitting request for report generation.', fnd_log.level_unexpected);
2403 IF(errbuf <> cz_utils.get_text('CZ_CNV_UNEXPECTED'))THEN
2404 log_msg(l_api_name, v_ndebug, errbuf, fnd_log.level_unexpected);
2405 END IF;
2406 cz_model_convert.CONVERT_MODEL:=FALSE;
2407 -- rollback model copy operation when there is an error
2408 --If "cz_model_migration_pvt .migrate_models_cp" call not completed successfully then Mark the migrated models as deleted .
2409
2410 retcode := 2;
2411 errbuf := cz_utils.get_text('CZ_CNV_UNEXPECTED');
2412
2413 FOR c_process IN (
2414
2415 SELECT remote_model_id
2416 FROM cz_model_publications p , cz_pb_model_exports z
2417 WHERE p.export_status IN('OK')
2418 AND p.server_id = 0
2419 AND p.publication_mode = 'M'
2420 AND p.migration_group_id = P_request_id
2421 AND z.publication_id = p.publication_id
2422 AND z.model_id = p.object_id
2423 AND z.server_id = 0
2424 AND z.status = 'OK'
2425 and p.source_target_flag='S'
2426 AND p.deleted_flag='0'
2427 )LOOP
2428 BEGIN
2429 cz_developer_utils_pvt.delete_model(c_process.remote_model_id,L_RETURN_STATUS,L_MSG_COUNT,L_MSG_DATA);
2430 EXCEPTION WHEN OTHERS THEN
2431 errbuf := 'Fatal error in ' || l_api_name || '.' || v_ndebug || ': ' || SUBSTR(sqlerrm, 1, 900);
2432 log_msg(l_api_name, v_ndebug, errbuf, fnd_log.level_unexpected);
2433 END;
2434
2435 END LOOP;
2436
2437 --an error has occured update status to reflect this
2438 UPDATE cz_model_publications SET export_status='ERR' WHERE migration_group_id = l_migration_group_id and server_id=0 ;
2439 commit;
2440 END;
2441 BEGIN
2442 call_status := FND_CONCURRENT.GET_REQUEST_STATUS(r, '', '', rphase,rstatus,dphase,dstatus, message);
2443 IF dphase<>'COMPLETE' THEN
2444 fnd_conc_global.set_req_globals(conc_status => 'PAUSED', request_data => to_char(i));
2445 END IF;
2446 --no need to pause if child completed
2447 EXCEPTION WHEN OTHERS THEN
2448 NULL;
2449 END;
2450 log_msg('Model_Convert_CP', 0, 'The Migration Group id that is completed is '||l_migration_group_id, fnd_log.level_unexpected);
2451 END;
2452 END LOOP;
2453 cz_model_convert.CONVERT_MODEL:=FALSE;
2454 IF(L_ERROR_IN_CP)THEN
2455 retcode :=2;
2456 errbuf := cz_utils.get_text('CZ_CNV_UNEXPECTED');
2457 END IF;
2458 EXCEPTION WHEN ERR_PROFILE THEN
2459 IF(errbuf <> cz_utils.get_text('CZ_CNV_UNEXPECTED'))THEN
2460 log_msg(l_api_name, v_ndebug, errbuf, fnd_log.level_unexpected);
2461 END IF;
2462 cz_model_convert.CONVERT_MODEL:=FALSE;
2463 retcode := 2;
2464 errbuf := cz_utils.get_text('CZ_CNV_UNEXPECTED');
2465 commit;
2466 WHEN OTHERS THEN
2467 IF(errbuf <> cz_utils.get_text('CZ_CNV_UNEXPECTED'))THEN
2468 log_msg(l_api_name, v_ndebug, errbuf, fnd_log.level_unexpected);
2469 END IF;
2470 End Model_Convert_CP;
2471
2472
2473 FUNCTION GET_UI_PATH(inParent_id IN NUMBER) RETURN VARCHAR2 is
2474 l_qualified VARCHAR2(2000) := ' ';
2475 BEGIN
2476
2477 IF inParent_id IS NOT NULL THEN
2478 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));
2479 l_qualified:=l_qualified||'.Pages';
2480 FOR parent_node IN (SELECT distinct(name) , element_id FROM cz_ui_page_elements
2481 WHERE deleted_flag = '0'
2482 START WITH element_id = to_char(inParent_id)
2483 CONNECT BY PRIOR parent_element_id=element_id
2484 order by element_id ) LOOP
2485
2486 IF(LENGTH(parent_node.name) + LENGTH(l_qualified) + 1 < 2000) THEN
2487 l_qualified := l_qualified||'.' || parent_node.name ;
2488 ELSE
2489 EXIT;
2490 END IF;
2491 END LOOP;
2492 END IF;
2493 RETURN l_qualified;
2494 END GET_UI_PATH;
2495
2496
2497
2498 END CZ_MODEL_CONVERT;
[Home] [Help]