1 PACKAGE BODY CZ_UPGRADE AS
2 /* $Header: czupgrdb.pls 120.2 2008/06/05 20:42:40 misheehy ship $ */
3
4 currentmodelid NUMBER;
5
6 base_expl_id NUMBER;
7 next_expl_id NUMBER;
8 base_node_id NUMBER;
9 next_node_id NUMBER;
10
11 current_expl_id NUMBER;
12 local_expl_id NUMBER;
13 current_node_id NUMBER;
14
15 root_model_id NUMBER;
16
17 -- generate cz_xfr_project_bills for references
18 PROCEDURE generate_xfr_reference_bills IS
19
20 BEGIN
21
22 FOR c_devl IN (SELECT devl_project_id, orig_sys_ref FROM cz_devl_projects p
23 WHERE deleted_flag = flag_not_deleted
24 AND devl_project_id <> 0
25 AND orig_sys_ref is not NULL
26 AND EXISTS (SELECT NULL FROM cz_rp_entries
27 WHERE object_id = p.devl_project_id
28 AND object_type = 'PRJ'
29 AND deleted_flag = flag_not_deleted)
30 AND NOT EXISTS (SELECT NULL FROM cz_xfr_project_bills
31 WHERE model_ps_node_id = p.devl_project_id )
32 )
33 LOOP
34
35 INSERT INTO cz_xfr_project_bills (model_ps_node_id,
36 organization_id,
37 top_item_id,
38 explosion_type,
39 deleted_flag,
40 source_server,
41 last_import_date)
42 SELECT c_devl.devl_project_id,
43 substr(c_devl.orig_sys_ref, instr(c_devl.orig_sys_ref, ':') + 1, instr(c_devl.orig_sys_ref, ':', 1, 2) - instr(c_devl.orig_sys_ref, ':') - 1),
44 substr(c_devl.orig_sys_ref, instr(c_devl.orig_sys_ref, ':', -1, 1) + 1),
45 substr(c_devl.orig_sys_ref, 1, instr(c_devl.orig_sys_ref, ':', 1) - 1),
46 '0',
47 0,
48 sysdate
49 FROM dual;
50
51 END LOOP;
52
53 END generate_xfr_reference_bills;
54
55 -----------------------------------------------------
56 -----------------------------------------------------
57
58 PROCEDURE AUTO_PUBLISH(p_server_id NUMBER) IS
59
60 TYPE devl_project_id_table IS TABLE OF cz_devl_projects.devl_project_id%TYPE INDEX BY BINARY_INTEGER;
61 TYPE orig_sys_ref_table IS TABLE OF cz_devl_projects.orig_sys_ref%TYPE INDEX BY BINARY_INTEGER;
62 TYPE last_import_date_table IS TABLE OF cz_xfr_project_bills.last_import_date%TYPE INDEX BY BINARY_INTEGER;
63
64 APPLET constant VARCHAR2(3) := '3';
65 DHTML constant VARCHAR2(3) := '0';
66 v_models_to_be_published devl_project_id_table;
67 v_orig_sys_ref orig_sys_ref_table;
68 v_last_import_date last_import_date_table;
69 v_application_id cz_ext_applications_v.application_id%TYPE;
70 v_to_publish_id cz_model_publications.publication_id%TYPE;
71 v_ui_def_id NUMBER;
72 v_run_id NUMBER;
73 v_ui_def_id_tmp NUMBER;
74 v_applet_ui_def_id NUMBER;
75 v_dhtml_ui_def_id NUMBER;
76 v_ui_style VARCHAR2(3);
77 v_product_key cz_model_publications.product_key%TYPE;
78 v_top_item_id NUMBER;
79 v_org_id NUMBER;
80 v_pr_orig_sys_ref cz_devl_projects.orig_sys_ref%TYPE := 'DUMMY';
81 v_pb_run_id NUMBER;
82 v_pb_status VARCHAR2(3);
83 v_base_language cz_pb_languages.language%TYPE;
84 xERROR BOOLEAN:=FALSE;
85
86
87 CURSOR to_publish_cur IS
88 SELECT publication_id
89 FROM cz_model_publications
90 WHERE cz_model_publications.export_status = 'PEN'
91 AND cz_model_publications.product_key like '%:%';
92
93 CURSOR ui_def_cur(v_models cz_devl_projects.devl_project_id%TYPE) IS
94 SELECT ui_def_id, ui_style
95 FROM cz_ui_defs
96 WHERE cz_ui_defs.devl_project_id = v_models
97 AND deleted_flag = '0'
98 order by LAST_UPDATE_DATE desc;
99
100 CURSOR appl_cur(v_preferred_ui_style VARCHAR2) IS
101 SELECT distinct application_id, application_short_name
102 FROM cz_ext_applications_v
103 WHERE preferred_ui_style = v_preferred_ui_style;
104
105 PROCEDURE get_base_language(x_base_lang OUT NOCOPY VARCHAR2)
106 AS
107 BEGIN
108 ---select base language from fnd languages
109 SELECT UPPER(language_code) INTO x_base_lang
110 FROM fnd_languages
111 WHERE fnd_languages.installed_flag IN ('B');
112 EXCEPTION
113 WHEN NO_DATA_FOUND THEN
114 cz_pb_mgr.log_pb_errors(SQLERRM,1,'LANGUAGE: AUTO PUBLISH',SQLCODE);
115 END;
116
117 PROCEDURE insert_publication(p_ui_def_id NUMBER,
118 p_ui_style VARCHAR2,
119 p_models cz_devl_projects.devl_project_id%TYPE) AS
120 v_application_id cz_ext_applications_v.application_id%TYPE;
121 v_application_short_name cz_ext_applications_v.application_short_name%TYPE;
122 BEGIN
123 get_base_language(v_base_language);
124
125 INSERT INTO cz_model_publications(
126 PUBLICATION_ID,
127 MODEL_ID,
128 SERVER_ID,
129 PRODUCT_KEY,
130 organization_id,
131 top_item_id,
132 PUBLICATION_MODE,
133 ui_def_id,
134 UI_STYLE,
135 APPLICABLE_FROM,
136 APPLICABLE_UNTIL,
137 EXPORT_STATUS,
138 DELETED_FLAG,
139 SOURCE_TARGET_FLAG,
140 REMOTE_PUBLICATION_ID
141 )
142 VALUES ( cz_model_publications_s.NEXTVAL,
143 p_models,
144 p_server_id,
145 v_product_key,
146 v_org_id,
147 v_top_item_id,
148 'P',
149 p_ui_def_id,
150 p_ui_style,
151 sysdate,
152 CZ_UTILS.EPOCH_END,
153 'PEN',
154 '0',
155 'S',
156 null
157 );
158
159 OPEN appl_cur(p_ui_style);
160 LOOP
161 FETCH appl_cur INTO v_application_id, v_application_short_name;
162 EXIT WHEN appl_cur%NOTFOUND;
163 BEGIN
164 INSERT INTO cz_pb_client_apps( PUBLICATION_ID,
165 FND_APPLICATION_ID,
166 APPLICATION_SHORT_NAME,
167 NOTES
168 )
169 VALUES (cz_model_publications_s.CURRVAL,
170 v_application_id,
171 v_application_short_name,
172 NULL
173 );
174 EXCEPTION
175 WHEN OTHERS THEN
176 cz_pb_mgr.log_pb_errors(SQLERRM,1,'AUTO PUBLISH',SQLCODE);
177 END;
178
179 INSERT INTO cz_pb_languages (publication_id,language)
180 values (cz_model_publications_s.CURRVAL,v_base_language);
181 END LOOP;
182 CLOSE appl_cur;
183
184 INSERT INTO cz_publication_usages ( PUBLICATION_ID,
185 USAGE_ID
186 )
187 VALUES (cz_model_publications_s.CURRVAL,
188 -1
189 );
190 END;
191
192
193 BEGIN
194
195 v_models_to_be_published.DELETE;
196
197 SELECT devl_project_id, orig_sys_ref, last_import_date
198 BULK
199 COLLECT
200 INTO v_models_to_be_published,
201 v_orig_sys_ref,
202 v_last_import_date
203 FROM cz_devl_projects, cz_rp_entries, cz_xfr_project_bills
204 WHERE cz_devl_projects.ORIG_SYS_REF IS NOT NULL
205 AND cz_devl_projects.deleted_flag = '0'
206 AND cz_devl_projects.devl_project_id = cz_rp_entries.object_id
207 AND cz_rp_entries.object_type = 'PRJ' and cz_rp_entries.deleted_flag = '0'
208 AND cz_devl_projects.devl_project_id = cz_xfr_project_bills.model_ps_node_id(+)
209 ORDER BY orig_sys_ref, last_import_date DESC;
210
211 IF (v_models_to_be_published.COUNT > 0 ) THEN
212
213 FOR I IN v_models_to_be_published.FIRST..v_models_to_be_published.LAST
214 LOOP
215
216 IF (v_last_import_date(i) IS NULL or v_orig_sys_ref(i) = v_pr_orig_sys_ref) THEN
217 xERROR:=CZ_UTILS.REPORT('The model ' || v_models_to_be_published(i) || ' has not been published. Another model based on the same BOM will be published',1,'CZ_AUTO_PUBLISH',11222);
218 ELSE
219 v_pr_orig_sys_ref := v_orig_sys_ref(i);
220 v_ui_def_id_tmp := NULL;
221 v_applet_ui_def_id := NULL;
222 v_dhtml_ui_def_id := NULL;
223 BEGIN
224 OPEN ui_def_cur(v_models_to_be_published(i));
225 FETCH ui_def_cur INTO v_ui_def_id_tmp, v_ui_style;
226
227 WHILE ui_def_cur%FOUND AND (v_applet_ui_def_id IS NULL OR v_dhtml_ui_def_id IS NULL) LOOP
228 IF (v_ui_style = APPLET AND v_applet_ui_def_id IS NULL) THEN
229 v_applet_ui_def_id := v_ui_def_id_tmp;
230 ELSIF (v_ui_style = DHTML AND v_dhtml_ui_def_id IS NULL) THEN
231 v_dhtml_ui_def_id := v_ui_def_id_tmp;
232 END IF;
233 FETCH ui_def_cur INTO v_ui_def_id_tmp, v_ui_style;
234 END LOOP;
235 CLOSE ui_def_cur;
236
237 IF (v_applet_ui_def_id IS NULL) THEN
238 v_applet_ui_def_id := 0;
239 END IF;
240
241 IF (v_dhtml_ui_def_id IS NULL) THEN
242 v_dhtml_ui_def_id := 0;
243 END IF;
244
245 END;
246
247
248 BEGIN
249 select substr(v_orig_sys_ref(i), instr(v_orig_sys_ref(i), ':')+1) into
250 v_product_key from dual;
251 EXCEPTION
252 WHEN OTHERS THEN
253 v_product_key := NULL;
254 END;
255
256 BEGIN
257 select substr(v_product_key, instr(v_product_key, ':')+1) into
258 v_top_item_id from dual;
259 EXCEPTION
260 WHEN OTHERS THEN
261 v_top_item_id := NULL;
262 END;
263
264 BEGIN
265 select substr(v_product_key, 1, instr(v_product_key, ':')-1) into
266 v_org_id from dual;
267 EXCEPTION
268 WHEN OTHERS THEN
269 v_org_id := NULL;
270 END;
271
272 BEGIN
273
274 IF (v_applet_ui_def_id > 0) THEN
275 insert_publication(v_applet_ui_def_id, APPLET, v_models_to_be_published(i));
276 END IF;
277
278 IF (v_dhtml_ui_def_id > 0) THEN
279
280 IF (v_applet_ui_def_id = 0) THEN
281 insert_publication(v_dhtml_ui_def_id, APPLET, v_models_to_be_published(i));
282 -- publication has incorrect ui_style at this point, need to update it
283 -- to DHTML
284 update cz_model_publications set ui_style = DHTML where ui_def_id
285 = v_dhtml_ui_def_id;
286 END IF;
287
288 insert_publication(v_dhtml_ui_def_id, DHTML, v_models_to_be_published(i));
289
290 END IF;
291
292 IF (v_applet_ui_def_id = 0 AND v_dhtml_ui_def_id = 0) THEN
293 cz_ui_generator.createui(v_models_to_be_published(i), v_ui_def_id, v_run_id, APPLET,
294 30, 640, 480, '0', '1', 'BLAF', 10, '0');
295
296 BEGIN
297 get_base_language(v_base_language);
298 INSERT INTO cz_model_publications(
299 PUBLICATION_ID
300 ,MODEL_ID
301 ,SERVER_ID
302 ,PRODUCT_KEY
303 ,organization_id
304 ,top_item_id
305 ,PUBLICATION_MODE
306 ,ui_def_id
307 ,UI_STYLE
308 ,APPLICABLE_FROM
309 ,APPLICABLE_UNTIL
310 ,EXPORT_STATUS
311 ,DELETED_FLAG
312 ,SOURCE_TARGET_FLAG
313 ,REMOTE_PUBLICATION_ID
314 )
315 VALUES ( cz_model_publications_s.NEXTVAL,
316 v_models_to_be_published(i),
317 p_server_id,
318 v_product_key,
319 v_org_id,
320 v_top_item_id,
321 'P',
322 v_ui_def_id,
323 APPLET,
324 sysdate,
325 CZ_UTILS.EPOCH_END,
326 'PEN',
327 '0',
328 'S',
329 null
330 );
331
332 BEGIN
333 SELECT application_id
334 INTO v_application_id
335 FROM fnd_application
336 WHERE application_short_name = 'ONT';
337 EXCEPTION
338 WHEN OTHERS THEN
339 v_application_id := -50;
340 END;
341
342
343 INSERT INTO cz_pb_client_apps( PUBLICATION_ID
344 ,FND_APPLICATION_ID
345 ,APPLICATION_SHORT_NAME
346 ,NOTES
347 )
348 VALUES ( cz_model_publications_s.CURRVAL,
349 v_application_id,
350 'ONT',
351 null
352 );
353
354
355 INSERT INTO cz_publication_usages ( PUBLICATION_ID
356 ,USAGE_ID
357 )
358 VALUES (cz_model_publications_s.CURRVAL,
359 -1
360 );
361
362 INSERT INTO cz_pb_languages (publication_id,language)
363 values (cz_model_publications_s.CURRVAL,v_base_language);
364 EXCEPTION
365 WHEN OTHERS THEN
366 cz_pb_mgr.log_pb_errors(SQLERRM,1,'AUTO PUBLISH',SQLCODE);
367 END;
368 END IF;
369
370 EXCEPTION
371 WHEN OTHERS THEN
372 cz_pb_mgr.log_pb_errors(SQLERRM,1,'AUTO PUBLISH',SQLCODE);
373 END;
374 END IF;
375 END LOOP;
376 COMMIT;
377 END IF;
378
379
380 OPEN to_publish_cur;
381 LOOP
382 FETCH to_publish_cur INTO v_to_publish_id;
383 EXIT WHEN to_publish_cur%NOTFOUND;
384 BEGIN
385 cz_pb_mgr.publish_model(v_to_publish_id, v_pb_run_id, v_pb_status);
386 EXCEPTION
387 WHEN OTHERS THEN
388 cz_pb_mgr.log_pb_errors(SQLERRM,1,'AUTO PUBLISH',SQLCODE);
389 END;
390 END LOOP;
391
392 -- insert cz_xfr_project_bills records for references
393 generate_xfr_reference_bills;
394 COMMIT;
395
396 EXCEPTION
397 WHEN OTHERS THEN
398 cz_pb_mgr.log_pb_errors(SQLERRM,1,'AUTO PUBLISH',SQLCODE);
399 END AUTO_PUBLISH;
400
401 -----------------------------------------------------
402 -----------------------------------------------------
403
404 PROCEDURE CZBOMSORT(p_model_id IN INTEGER,
405 p_sort_width IN INTEGER,
406 p_batch_size IN INTEGER) IS
407
408 v_ps_node_id INTEGER;
409 numRecord INTEGER:=0;
410 BatchSize INTEGER:=p_batch_size;
411 var_bom_sort CZ_PS_NODES.bom_sort_order%TYPE;
412 xERROR BOOLEAN:=FALSE;
413
414
415 FUNCTION getNum(p_number IN INTEGER) RETURN VARCHAR2 IS
416 ret VARCHAR2(100);
417 BEGIN
418 SELECT LPAD(TO_CHAR(p_number),p_sort_width,'0') INTO ret FROM dual;
419 RETURN ret;
420 END getNum;
421
422 PROCEDURE populate(p_ps_node_id IN INTEGER,p_string1 IN VARCHAR2,p_string2 IN VARCHAR2) IS
423 var_token VARCHAR2(1);
424 var_string1 CZ_PS_NODES.bom_sort_order%TYPE;
425 var_string2 CZ_PS_NODES.component_sequence_path%TYPE;
426 BEGIN
427 FOR i IN (SELECT ps_node_id,parent_id,tree_seq,ps_node_type,
428 component_sequence_id,component_sequence_path FROM CZ_PS_NODES
429 WHERE parent_id=p_ps_node_id AND ps_node_type IN(263,436,437,438)
430 AND deleted_flag='0')
431 LOOP
432 IF p_string2='' OR p_string2 IS NULL THEN
433 var_token:='';
434 ELSE
435 var_token:='-';
436 END IF;
437 var_string1:=p_string1||getNum(i.tree_seq);
438 var_string2:=p_string2||var_token||TO_CHAR(i.component_sequence_id);
439 UPDATE CZ_PS_NODES SET bom_sort_order=var_string1
440 WHERE ps_node_id=i.ps_node_id AND bom_sort_order is NULL;
441 UPDATE CZ_PS_NODES SET component_sequence_path=var_string2
442 WHERE ps_node_id=i.ps_node_id AND component_sequence_path is NULL;
443 populate(i.ps_node_id,var_string1,var_string2);
444 IF numRecord>BatchSize THEN
445 COMMIT;
446 numRecord:=0;
447 ELSE
448 numRecord:=numRecord+1;
449 END IF;
450 END LOOP;
451 END populate;
452
453 BEGIN
454 var_bom_sort:=getNum(1);
455
456 UPDATE CZ_PS_NODES SET component_sequence_path=NULL,bom_sort_order=var_bom_sort
457 WHERE ps_node_id = p_model_id;
458
459 populate(p_model_id,var_bom_sort,'');
460
461 EXCEPTION
462 WHEN NO_DATA_FOUND THEN
463 NULL;
464 WHEN OTHERS THEN
465 NULL;
466 END CZBOMSORT;
467
468 -----------------------------------------------------
469 -----------------------------------------------------
470
471 PROCEDURE generate_explosion IS
472
473 v_origsysref cz_ps_nodes.orig_sys_ref%TYPE;
474 x_error BOOLEAN;
475
476 schema_version NUMBER;
477
478 BEGIN
479
480 --Initialize id allocation for model explosions ids
481
482 SELECT cz_model_ref_expls_s.NEXTVAL INTO base_expl_id FROM dual;
483 next_expl_id := base_expl_id;
484
485 --Initialize id allocation for ps_node ids
486
487 SELECT cz_ps_nodes_s.NEXTVAL INTO base_node_id FROM dual;
488 next_node_id := base_node_id;
489
490 --Set the virtual_flag for all the nodes
491
492 UPDATE cz_expression_nodes SET consequent_flag = flag_not_consequent;
493 COMMIT;
494
495 --Updates consequent flags in a bunch for all projects in the schema
496
497 UPDATE cz_expression_nodes SET consequent_flag = flag_is_consequent
498 WHERE expr_node_id IN
499 (SELECT child1.expr_node_id
500 FROM cz_rules rule, cz_expression_nodes parent, cz_expression_nodes child1,
501 cz_expression_nodes child2
502 WHERE
503 --Parent is not deleted and is operator dot
504 parent.deleted_flag = flag_not_deleted
505 AND parent.expr_type = expr_node_type_operator
506 AND parent.expr_subtype = operator_dot
507 --Rule is not deleted or disabled
508 AND rule.deleted_flag = flag_not_deleted
509 AND rule.disabled_flag = flag_not_disabled
510 --Both children are not deleted and are children of the parent
511 AND child1.deleted_flag = flag_not_deleted
512 AND child2.deleted_flag = flag_not_deleted
513 AND child1.expr_parent_id = parent.expr_node_id
514 AND child2.expr_parent_id = parent.expr_node_id
515 --Parent is the consequent expression for the rule
516 AND rule.consequent_id = parent.express_id
517 --One child is a node expression node
518 AND child1.expr_type = expr_node_type_node
519 --Another child is system property, min or max
520 AND child2.expr_type = expr_node_type_sysprop
521 AND child2.expr_subtype IN (sys_prop_min, sys_prop_max)
522 );
523
524 COMMIT;
525
526 UPDATE cz_ps_nodes SET virtual_flag = flag_virtual
527 WHERE ps_node_type IN (ps_node_type_product, ps_node_type_component, ps_node_type_bom_model);
528
529 COMMIT;
530
531 UPDATE cz_ps_nodes SET virtual_flag = flag_non_virtual WHERE ps_node_id IN (
532 SELECT structure.ps_node_id
533 FROM cz_ps_nodes structure, cz_ps_nodes parent
534 WHERE structure.ps_node_type IN (ps_node_type_product, ps_node_type_component, ps_node_type_bom_model)
535 AND parent.ps_node_id = structure.parent_id
536 AND (structure.ps_node_type IN (ps_node_type_product, ps_node_type_component) OR parent.ps_node_type <> ps_node_type_product)
537 AND structure.deleted_flag = flag_not_deleted
538 AND (structure.ps_node_type = ps_node_type_bom_model OR (
539 (structure.minimum <> 1 OR structure.maximum <> 1 OR EXISTS
540 --Expressions are joined to bring in project
541 (SELECT NULL
542 FROM cz_expressions expr, cz_expression_nodes node
543 WHERE expr.devl_project_id = structure.devl_project_id
544 AND node.ps_node_id = structure.ps_node_id
545 AND expr.deleted_flag = flag_not_deleted
546 AND node.deleted_flag = flag_not_deleted
547 --Consequent flag '1' guarantees existence of a rule
548 AND node.consequent_flag = flag_is_consequent
549 )))));
550
551 COMMIT;
552
553 FOR c_devl IN (SELECT devl_project_id, orig_sys_ref FROM cz_devl_projects p
554 WHERE deleted_flag = flag_not_deleted
555 AND devl_project_id <> 0
556 AND EXISTS (
557 SELECT NULL FROM cz_ps_nodes
558 WHERE deleted_flag = flag_not_deleted
559 AND ps_node_type = ps_node_type_product
560 AND devl_project_id = p.devl_project_id)
561 AND NOT EXISTS
562 (SELECT NULL FROM cz_model_ref_expls
563 WHERE model_id = p.devl_project_id)
564 ) LOOP
565
566 IF(c_devl.orig_sys_ref IS NOT NULL)THEN
567
568 UPDATE cz_devl_projects SET
569 orig_sys_ref =
570 (SELECT nvl(substr(parent.orig_sys_ref,instr(parent.orig_sys_ref,':',-1,3)+1),
571 substr(child.orig_sys_ref,instr(child.orig_sys_ref,':',-1,3)+1))
572 FROM cz_ps_nodes parent, cz_ps_nodes child
573 WHERE parent.ps_node_id = c_devl.devl_project_id
574 AND parent.ps_node_type = 258
575 AND child.ps_node_type IN (258, 436)
576 AND parent.ps_node_id = child.parent_id
577 AND ROWNUM = 1)
578 WHERE devl_project_id = c_devl.devl_project_id;
579
580 UPDATE cz_ps_nodes SET
581 orig_sys_ref =
582 (SELECT orig_sys_ref FROM cz_devl_projects WHERE devl_project_id = c_devl.devl_project_id)
583 WHERE ps_node_id = c_devl.devl_project_id
584 returning orig_sys_ref INTO v_origsysref;
585
586 INSERT INTO cz_xfr_project_bills (model_ps_node_id,
587 organization_id,
588 top_item_id,
589 explosion_type,
590 deleted_flag,
591 source_server,
592 last_import_date) -- fix for bug # 2406244
593 SELECT c_devl.devl_project_id,
594 substr(v_origsysref, instr(v_origsysref, ':') + 1, instr(v_origsysref, ':', 1, 2) - instr(v_origsysref, ':') - 1),
595 substr(v_origsysref, instr(v_origsysref, ':', -1, 1) + 1),
596 substr(v_origsysref, 1, instr(v_origsysref, ':') - 1),
597 '0',
598 0,
599 sysdate -- fix for bug # 2406244
600 FROM dual
601 WHERE NOT EXISTS (SELECT NULL FROM cz_xfr_project_bills
602 WHERE model_ps_node_id = c_devl.devl_project_id);
603
604 END IF;
605
606 generate_model_tree(c_devl.devl_project_id);
607 END LOOP;
608
609 COMMIT;
610 EXCEPTION
611 WHEN OTHERS THEN
612 x_error:=cz_utils.report(SQLERRM,1,'CZ_EXPLS_GEN.GENERATE_EXPLOSION',11500);
613 END generate_explosion;
614 ---------------------------------------------------------------------------------------
615 PROCEDURE generate_model_tree(indevlprojectid IN NUMBER) IS
616 npsnodeid NUMBER;
617 nminimum NUMBER;
618 nmaximum NUMBER;
619 x_error BOOLEAN;
620 BEGIN
621
622 --Start off the recursion
623
624 root_model_id := indevlprojectid;
625 generate_component_tree(indevlprojectid, 0, NULL, NULL, NULL);
626
627 --Handle here the exceptions that should terminate the model tree generation
628 --process or the logic generation at all. An exception that is not re-raised
629 --here will terminate just the model tree generation and go on with logic
630 --generation. Exceptions that are re-raised should be caught in the calling
631 --routine.
632
633 COMMIT;
634
635 EXCEPTION
636 WHEN OTHERS THEN
637 --***May require change before final
638 x_error:=cz_utils.report(SQLERRM,1,'CZ_EXPLS_GEN.GENERATE_MODEL_TREE',11500);
639 END generate_model_tree;
640 ---------------------------------------------------------------------------------------
641 PROCEDURE generate_component_tree(incomponentid IN NUMBER,
642 inlogicnetlevel IN NUMBER,
643 inparentexplid IN NUMBER,
644 inparentcomponentid IN NUMBER,
645 inreferringnodeid IN NUMBER)
646 IS
647
648 TYPE tpsnodeid IS TABLE OF cz_ps_nodes.ps_node_id%TYPE INDEX BY BINARY_INTEGER;
649 TYPE tpsnodetype IS TABLE OF cz_ps_nodes.ps_node_type%TYPE INDEX BY BINARY_INTEGER;
650 TYPE tinitialvalue IS TABLE OF cz_ps_nodes.initial_value%TYPE INDEX BY BINARY_INTEGER;
651 TYPE tinitnumval IS TABLE OF cz_ps_nodes.initial_num_value%TYPE INDEX BY BINARY_INTEGER; -- sselahi
652 TYPE tparentid IS TABLE OF cz_ps_nodes.parent_id%TYPE INDEX BY BINARY_INTEGER;
653 TYPE tvirtualflag IS TABLE OF cz_ps_nodes.virtual_flag%TYPE INDEX BY BINARY_INTEGER;
654 TYPE tfeaturetype IS TABLE OF cz_ps_nodes.feature_type%TYPE INDEX BY BINARY_INTEGER;
655 TYPE tname IS TABLE OF cz_ps_nodes.name%TYPE INDEX BY BINARY_INTEGER;
656 TYPE tdescriptionid IS TABLE OF cz_ps_nodes.intl_text_id%TYPE INDEX BY BINARY_INTEGER;
657 TYPE tminimumsel IS TABLE OF cz_ps_nodes.minimum_selected%TYPE INDEX BY BINARY_INTEGER;
658 TYPE tmaximumsel IS TABLE OF cz_ps_nodes.maximum_selected%TYPE INDEX BY BINARY_INTEGER;
659 TYPE tbomrequired IS TABLE OF cz_ps_nodes.bom_required_flag%TYPE INDEX BY BINARY_INTEGER;
660 TYPE treferenceid IS TABLE OF cz_ps_nodes.reference_id%TYPE INDEX BY BINARY_INTEGER;
661 TYPE ttreeseq IS TABLE OF cz_ps_nodes.tree_seq%TYPE INDEX BY BINARY_INTEGER;
662 TYPE torigsysref IS TABLE OF cz_ps_nodes.orig_sys_ref%TYPE INDEX BY BINARY_INTEGER;
663 TYPE teffectivefrom IS TABLE OF cz_ps_nodes.effective_from%TYPE INDEX BY BINARY_INTEGER;
664 TYPE teffectiveuntil IS TABLE OF cz_ps_nodes.effective_until%TYPE INDEX BY BINARY_INTEGER;
665 TYPE tcomponentsequenceid IS TABLE OF cz_ps_nodes.component_sequence_id%TYPE INDEX BY BINARY_INTEGER;
666
667 ntpsnodeid tpsnodeid;
668 ntpsnodetype tpsnodetype;
669 nvpsnodetype tpsnodetype;
670 ntinitialvalue tinitialvalue;
671 ntinitnumval tinitnumval; --sselahi
672 ntparentid tparentid;
673 ntvirtualflag tvirtualflag;
674 ntfeaturetype tfeaturetype;
675 ntname tname;
676 ntdescriptionid tdescriptionid;
677 ntminimumsel tminimumsel;
678 ntmaximumsel tmaximumsel;
679 ntbomrequired tbomrequired;
680 ntreferenceid treferenceid;
681 nttreeseq ttreeseq;
682 ntorigsysref torigsysref;
683 nteffectivefrom teffectivefrom;
684 nteffectiveuntil teffectiveuntil;
685 ntcomponentsequenceid tcomponentsequenceid;
686
687 npsnodetype NUMBER;
688 svirtualflag VARCHAR2(1);
689 save_expl_id NUMBER;
690 localmodelid NUMBER;
691 modelpsnodeid NUMBER;
692 ncount NUMBER;
693 genname cz_ps_nodes.name%TYPE;
694
695 stopitemid VARCHAR2(25);
696 ncutstart PLS_INTEGER;
697
698 correct_expl_id NUMBER;
699 x_error BOOLEAN;
700
701 BEGIN
702
703 current_expl_id := next_expl_id;
704 IF(next_expl_id = base_expl_id)THEN
705 SELECT cz_model_ref_expls_s.NEXTVAL INTO base_expl_id FROM dual;
706 END IF;
707 next_expl_id := next_expl_id + 1;
708
709 IF(inparentexplid IS NULL)THEN
710 currentmodelid := incomponentid;
711 END IF;
712
713 SELECT ps_node_type, virtual_flag INTO npsnodetype, svirtualflag
714 FROM cz_ps_nodes WHERE ps_node_id = incomponentid;
715
716 localmodelid := currentmodelid;
717 IF(npsnodetype IN (ps_node_type_bom_model/*, ps_node_type_product*/))THEN
718 localmodelid := incomponentid;
719 END IF;
720
721 INSERT INTO cz_model_ref_expls
722 (model_ref_expl_id, parent_expl_node_id, component_id, model_id,
723 node_depth, virtual_flag, deleted_flag, ps_node_type)
724 SELECT
725 current_expl_id,
726 decode(npsnodetype, ps_node_type_bom_model, NULL, /*PS_NODE_TYPE_PRODUCT, NULL,*/ inparentexplid),
727 incomponentid,
728 localmodelid,
729 decode(npsnodetype, ps_node_type_bom_model, 0, /*PS_NODE_TYPE_PRODUCT, 0,*/ inlogicnetlevel),
730 decode(inlogicnetlevel, 0, flag_virtual, svirtualflag),
731 flag_not_deleted, npsnodetype
732 FROM dual WHERE NOT EXISTS
733 (SELECT NULL FROM cz_model_ref_expls WHERE model_id = localmodelid AND component_id = incomponentid);
734
735 correct_expl_id := current_expl_id;
736
737 IF(npsnodetype IN (ps_node_type_bom_model/*, ps_node_type_product*/))THEN
738
739 FOR expl IN (SELECT model_ref_expl_id, node_depth, model_id FROM cz_model_ref_expls
740 WHERE component_id = inparentcomponentid) LOOP
741
742 BEGIN
743
744 local_expl_id := next_expl_id;
745 IF(next_expl_id = base_expl_id)THEN
746 SELECT cz_model_ref_expls_s.NEXTVAL INTO base_expl_id FROM dual;
747 END IF;
748 next_expl_id := next_expl_id + 1;
749
750 IF(expl.model_id = root_model_id)THEN correct_expl_id := local_expl_id; END IF;
751
752 INSERT INTO cz_model_ref_expls
753 (model_ref_expl_id, parent_expl_node_id, node_depth, ps_node_type, virtual_flag,
754 component_id, model_id, referring_node_id, child_model_expl_id, deleted_flag)
755 SELECT
756 local_expl_id, expl.model_ref_expl_id, expl.node_depth + 1, ps_node_type_reference,
757 flag_virtual, incomponentid, expl.model_id, inreferringnodeid, current_expl_id,
758 flag_not_deleted
759 FROM dual WHERE NOT EXISTS
760 (SELECT NULL FROM cz_model_ref_expls
761 WHERE component_id = incomponentid
762 AND model_id = expl.model_id
763 AND referring_node_id = inreferringnodeid
764 AND child_model_expl_id = current_expl_id);
765
766 EXCEPTION
767 WHEN OTHERS THEN
768 --***May require change before final
769 x_error:=cz_utils.report(SQLERRM,1,'CZ_EXPLS_GEN.GENERATE_COMPONENT_TREE1',11500);
770 END;
771 END LOOP;
772
773 END IF;
774
775 --This select statement reads the whole 'virtual' tree under a non-virtual component
776 --which doesn't include the chief non-virtual component itself, although it includes
777 --non-virtual components underneath in order to recurse,this function will be called
778 --for every non-virtual component found underneath.
779 --The resulting order provided by this statement will be used later when generating
780 --list of options for an option feature.
781
782 SELECT ps_node_id, parent_id, name, intl_text_id, tree_seq,
783 minimum, maximum, ps_node_type, initial_value, initial_num_value, -- sselahi
784 virtual_flag, feature_type, bom_required_flag, reference_id, orig_sys_ref,
785 effective_from, effective_until, component_sequence_id
786 bulk collect INTO ntpsnodeid, ntparentid, ntname, ntdescriptionid, nttreeseq,
787 ntminimumsel, ntmaximumsel, ntpsnodetype, ntinitialvalue, ntinitnumval, -- sselahi
788 ntvirtualflag, ntfeaturetype, ntbomrequired, ntreferenceid, ntorigsysref,
789 nteffectivefrom, nteffectiveuntil, ntcomponentsequenceid
790 FROM cz_ps_nodes
791 WHERE deleted_flag = flag_not_deleted
792 START WITH parent_id = incomponentid
793 CONNECT BY
794 (PRIOR virtual_flag IS NULL OR PRIOR virtual_flag = flag_virtual)
795 AND PRIOR ps_node_id = parent_id;
796
797 UPDATE cz_expression_nodes SET model_ref_expl_id = correct_expl_id
798 WHERE ps_node_id = incomponentid AND deleted_flag = flag_not_deleted;
799
800 UPDATE cz_func_comp_specs SET model_ref_expl_id = correct_expl_id
801 WHERE component_id = incomponentid AND deleted_flag = flag_not_deleted;
802
803 UPDATE cz_combo_features SET model_ref_expl_id = correct_expl_id
804 WHERE feature_id = incomponentid AND deleted_flag = flag_not_deleted;
805
806 UPDATE cz_des_chart_features SET model_ref_expl_id = correct_expl_id
807 WHERE feature_id = incomponentid AND deleted_flag = flag_not_deleted;
808
809 UPDATE cz_des_chart_cells SET secondary_feat_expl_id = correct_expl_id
810 WHERE secondary_feature_id = incomponentid AND deleted_flag = flag_not_deleted;
811
812 UPDATE cz_ui_nodes SET model_ref_expl_id = correct_expl_id
813 WHERE ps_node_id = incomponentid AND deleted_flag = flag_not_deleted;
814
815 --Make sure there is some data returned
816
817 IF(ntpsnodeid.last IS NOT NULL)THEN
818
819 FOR i IN ntpsnodeid.first..ntpsnodeid.last LOOP
820
821 UPDATE cz_expression_nodes SET model_ref_expl_id = correct_expl_id
822 WHERE ps_node_id = ntpsnodeid(i) AND deleted_flag = flag_not_deleted;
823
824 UPDATE cz_func_comp_specs SET model_ref_expl_id = correct_expl_id
825 WHERE component_id = ntpsnodeid(i) AND deleted_flag = flag_not_deleted;
826
827 UPDATE cz_combo_features SET model_ref_expl_id = correct_expl_id
828 WHERE feature_id = ntpsnodeid(i) AND deleted_flag = flag_not_deleted;
829
830 UPDATE cz_des_chart_features SET model_ref_expl_id = correct_expl_id
831 WHERE feature_id = ntpsnodeid(i) AND deleted_flag = flag_not_deleted;
832
833 UPDATE cz_des_chart_cells SET secondary_feat_expl_id = correct_expl_id
834 WHERE secondary_feature_id = ntpsnodeid(i) AND deleted_flag = flag_not_deleted;
835
836 UPDATE cz_ui_nodes SET model_ref_expl_id = correct_expl_id
837 WHERE ps_node_id = ntpsnodeid(i) AND deleted_flag = flag_not_deleted;
838
839 END LOOP;
840
841 save_expl_id := current_expl_id;
842
843 FOR i IN ntpsnodeid.first..ntpsnodeid.last LOOP
844
845 modelpsnodeid := ntpsnodeid(i);
846
847 IF(ntpsnodetype(i) IN (ps_node_type_component, ps_node_type_product, ps_node_type_bom_model) AND
848 ntvirtualflag(i) = flag_non_virtual)THEN
849
850 IF(ntpsnodetype(i) IN (ps_node_type_bom_model/*, ps_node_type_product*/))THEN
851
852 genname := ntname(i);
853
854 BEGIN
855 SELECT object_id INTO ncount FROM cz_rp_entries
856 WHERE deleted_flag = flag_not_deleted
857 AND object_type = 'PRJ'
858 AND name = ntname(i);
859
860 ncount := NULL;
861 BEGIN
862 SELECT MAX(cz_utils.conv_num(substr(name, 7, instr(name, ')') - 7))) INTO ncount
863 FROM cz_rp_entries
864 WHERE deleted_flag = flag_not_deleted
865 AND object_type = 'PRJ'
866 AND name LIKE 'Copy (%) of ' || ntname(i);
867 EXCEPTION
868 WHEN OTHERS THEN
869 NULL;
870 END;
871
872 IF(ncount IS NULL)THEN ncount := 0; END IF;
873 genname := 'Copy (' || to_char(ncount + 1) || ') of ' || ntname(i);
874
875 EXCEPTION
876 WHEN OTHERS THEN
877 genname := ntname(i);
878 END;
879
880 current_node_id := next_node_id;
881 IF(next_node_id = base_node_id)THEN
882 SELECT cz_ps_nodes_s.NEXTVAL INTO base_node_id FROM dual;
883 END IF;
884 next_node_id := next_node_id + 1;
885
886 BEGIN
887
888 --Insert the reference node
889
890 INSERT INTO cz_ps_nodes
891 (ps_node_id, parent_id, ps_node_type, minimum, maximum, minimum_selected, maximum_selected,
892 name, tree_seq, deleted_flag, devl_project_id, virtual_flag, reference_id,
893 system_node_flag, ui_omit, effective_from, effective_until, orig_sys_ref,
894 component_sequence_id)
895 SELECT
896 current_node_id, ntparentid(i), ps_node_type_reference, 1, 1, ntminimumsel(i), ntmaximumsel(i),
897 genname, nttreeseq(i), flag_not_deleted, localmodelid, flag_virtual, modelpsnodeid,
898 '0', '0', nteffectivefrom(i), nteffectiveuntil(i), ntorigsysref(i),
899 ntcomponentsequenceid(i)
900 FROM dual;
901
902 ncutstart := instr(ntorigsysref(i), '-', -1, 1) + 1;
903 stopitemid := substr(ntorigsysref(i),ncutstart,instr(ntorigsysref(i),':')-ncutstart);
904
905 UPDATE cz_ps_nodes SET
906 parent_id = NULL,
907 minimum = 0,
908 maximum = -1,
909 tree_seq = 1,
910 component_sequence_id = NULL,
911 virtual_flag = flag_virtual
912 WHERE ps_node_id = ntpsnodeid(i);
913
914 UPDATE cz_ps_nodes SET
915 devl_project_id = ntpsnodeid(i),
916 --orig_sys_ref = SUBSTR(orig_sys_ref, INSTR(orig_sys_ref, '-', 1, inLogicNetLevel + 2) + 1)
917 orig_sys_ref = substr(substr(orig_sys_ref, ncutstart),1,instr(substr(orig_sys_ref, ncutstart),':',-1,1)) || stopitemid
918 WHERE ps_node_id IN
919 (SELECT ps_node_id FROM cz_ps_nodes
920 WHERE deleted_flag = flag_not_deleted
921 START WITH ps_node_id = ntpsnodeid(i)
922 CONNECT BY PRIOR ps_node_id = parent_id);
923
924 --Insert into cz_rule_folders
925
926 INSERT INTO cz_rule_folders
927 (rule_folder_id,name,tree_seq,devl_project_id,created_by,last_updated_by,
928 creation_date,last_update_date,deleted_flag)
929 SELECT cz_rule_folders_s.NEXTVAL,ntname(i)||' Rules',0,
930 ntpsnodeid(i),UID,UID,SYSDATE,SYSDATE,'0'
931 FROM dual WHERE NOT EXISTS
932 (SELECT 1 FROM cz_rule_folders WHERE
933 devl_project_id=ntpsnodeid(i) AND
934 parent_rule_folder_id IS NULL AND name=ntname(i)||' Rules');
935
936 --Insert and take care of orig_sys_ref
937
938 INSERT INTO cz_devl_projects
939 (devl_project_id, name, persistent_project_id, deleted_flag, orig_sys_ref)
940 SELECT
941 ntpsnodeid(i), genname, ntpsnodeid(i), flag_not_deleted,
942 substr(ntorigsysref(i),instr(ntorigsysref(i),':',-1,3)+1,instr(ntorigsysref(i),':',-1,2)-instr(ntorigsysref(i),':',-1,3)-1) || ':' ||
943 substr(ntorigsysref(i),instr(ntorigsysref(i),':',-1,2)+1,instr(ntorigsysref(i),':',-1,1)-instr(ntorigsysref(i),':',-1,2)-1) || ':' ||
944 stopitemid /*substr(ntOrigSysRef(i),instr(ntOrigSysRef(i),':',-1,1)+1)*/
945 FROM dual WHERE NOT EXISTS
946 (SELECT NULL FROM cz_devl_projects WHERE devl_project_id = ntpsnodeid(i));
947
948 --Insert into cz_rp_entries
949
950 INSERT INTO cz_rp_entries
951 (object_type,object_id,enclosing_folder,name,description,deleted_flag)
952 SELECT 'PRJ',ntpsnodeid(i),0,
953 genname,genname,'0'
954 FROM dual WHERE NOT EXISTS
955 (SELECT 1 FROM cz_rp_entries WHERE
956 (object_type='PRJ' AND object_id=ntpsnodeid(i)) OR
957 (enclosing_folder=0 AND name=genname));
958
959 EXCEPTION
960 WHEN OTHERS THEN
961 --***May require change before final
962 x_error:=cz_utils.report(SQLERRM,1,'CZ_EXPLS_GEN.GENERATE_COMPONENT_TREE2',11500);
963 END;
964
965 END IF;
966
967 --This is another non-virtual component. Call this function for it - recursion
968
969 generate_component_tree(modelpsnodeid, inlogicnetlevel + 1, save_expl_id, incomponentid, current_node_id);
970
971 END IF;
972
973 END LOOP;
974
975 END IF;
976
977 END generate_component_tree;
978 ---------------------------------------------------------------------------------------
979 ------procedures used for upgrading logic from builds 14,15,16,17 to 18 to more
980 ------function that gets the major schema version from db settings table
981
982 FUNCTION get_major_version
983 RETURN VARCHAR2
984 IS
985
986 v_schema_version cz_db_settings.value%TYPE;
987 BEGIN
988 SELECT value
989 INTO cz_upgrade.v_schema_version
990 FROM cz_db_settings
991 WHERE cz_db_settings.setting_id = MAJOR_SCHEMA_VERSION;
992 RETURN cz_upgrade.v_schema_version;
993 EXCEPTION
994 WHEN OTHERS THEN
995 cz_upgrade.v_schema_version := 0;
996 RETURN cz_upgrade.v_schema_version;
997 END get_major_version;
998 ------------------------
999 ---------procedure that logs errors to cz_db_logs.
1000 PROCEDURE report_upgrade_logic_errors(p_from_schema VARCHAR2,
1001 p_to_schema VARCHAR2,
1002 p_lce_header_id NUMBER,
1003 p_err_message VARCHAR2,
1004 p_message_flag VARCHAR2
1005 )
1006 IS
1007 v_message VARCHAR2(4000);
1008 v_run_id cz_db_logs.run_id%TYPE;
1009 v_caller cz_db_logs.caller%TYPE;
1010
1011 BEGIN
1012 SELECT cz_xfr_run_infos_s.nextval into v_run_id FROM dual;
1013
1014 v_message := 'Logic upgrade from schema '||p_from_schema||' for lce_header_id '||p_lce_header_id||' : '||p_err_message ;
1015 v_message := SUBSTR(v_message,1,2000);
1016
1017 IF (p_message_flag = 'LOGIC_UPGRADE') THEN
1018 v_caller := 'UPGRADE_LOGIC';
1019 ELSIF (p_message_flag = 'VERIFY_LOGIC') THEN
1020 v_caller := 'VERIFY_LOGIC';
1021 END IF;
1022
1023 INSERT INTO cz_db_logs (LOGTIME,LOGUSER,URGENCY,CALLER,STATUSCODE,MESSAGE,CREATED_BY,CREATION_DATE,SESSION_ID
1024 ,MESSAGE_ID,RUN_ID)
1025 VALUES (sysdate,'upgrade_logic_user',1,v_caller,0,v_message,-1,sysdate,1,1,v_run_id);
1026 COMMIT;
1027 END report_upgrade_logic_errors;
1028 ----------------------------
1029 --------procedure that upgrades logic from a 14 build to a 18 build
1030 PROCEDURE upgrade_logic_from_14
1031 IS
1032
1033 TYPE tDevlProjectId IS TABLE OF cz_devl_projects.devl_project_id%TYPE;
1034 runId number;
1035 devlProjectId tDevlProjectId;
1036 v_rule_count number := 0;
1037
1038 begin
1039 ------------select all source models to generate logic
1040 SELECT devl_project_id
1041 BULK
1042 COLLECT
1043 INTO devlProjectId
1044 FROM cz_devl_projects
1045 WHERE cz_devl_projects.deleted_flag = '0'
1046 AND cz_devl_projects.devl_project_id IN (SELECT object_id
1047 FROM cz_rp_entries
1048 WHERE cz_rp_entries.deleted_flag = '0'
1049 AND cz_rp_entries.object_type = 'PRJ');
1050
1051 IF (devlProjectId.COUNT > 0) THEN
1052 FOR i IN devlProjectId.FIRST..devlProjectId.LAST
1053 LOOP
1054 -- delete cz_lce_headers for component_id = devl_project_id with model_ref_expl_flag = -1
1055 -- Bug #2369725
1056 update cz_lce_headers set deleted_flag = '1'
1057 where component_id = devlProjectId(i)
1058 and deleted_flag = '0'
1059 and model_ref_expl_id = -1;
1060 commit;
1061 cz_logic_gen.generate_logic(devlProjectId(i), runId);
1062 END LOOP;
1063 END IF;
1064 end upgrade_logic_from_14;
1065 --------------------------------
1066 -------------function that does a check if load_specs has to be populated
1067 FUNCTION has_to_populate_load_specs(p_lce_header_id IN NUMBER)
1068 RETURN BOOLEAN
1069 IS
1070
1071 v_count PLS_INTEGER := 0;
1072 BEGIN
1073 SELECT count(*)
1074 INTO v_count
1075 FROM cz_lce_load_specs
1076 WHERE cz_lce_load_specs.lce_header_id = p_lce_header_id
1077 AND cz_lce_load_specs.deleted_flag = '0';
1078
1079 IF (v_count > 0) THEN
1080 SELECT count(*)
1081 INTO v_count
1082 FROM cz_lce_load_specs
1083 WHERE cz_lce_load_specs.lce_header_id = p_lce_header_id
1084 AND cz_lce_load_specs.attachment_expl_id > 0
1085 AND cz_lce_load_specs.required_expl_id > 0
1086 AND cz_lce_load_specs.attachment_comp_id > 0
1087 AND cz_lce_load_specs.model_id > 0
1088 AND cz_lce_load_specs.net_type > 0
1089 AND cz_lce_load_specs.deleted_flag = '0';
1090
1091 IF (v_count = 0) THEN
1092 update cz_lce_load_specs
1093 set deleted_flag = '1'
1094 where cz_lce_load_specs.lce_header_id = p_lce_header_id
1095 and cz_lce_load_specs.deleted_flag = '0';
1096 commit;
1097 RETURN TRUE;
1098 ELSE
1099 RETURN FALSE;
1100 END IF;
1101 ELSE
1102 RETURN TRUE;
1103 END IF;
1104 EXCEPTION
1105 WHEN OTHERS THEN
1106 RETURN TRUE;
1107 END has_to_populate_load_specs;
1108 --------------------------------------
1109 -----------------
1110 ---------procedure that gets valid LCE headers for source and published models
1111 ---------p_model_flag is 'S' for a source model and 'P' for a published model
1112 ---------x_lce_header_tbl is the table of valid lce headers.
1113
1114 PROCEDURE get_lce_headers(p_model_flag IN VARCHAR2, x_lce_header_tbl IN OUT NOCOPY cz_upgrade.t_ref, x_err_message IN OUT NOCOPY VARCHAR2)
1115 IS
1116
1117 v_published_root_models_tbl cz_upgrade.t_ref;
1118 v_all_published_models_tbl cz_upgrade.t_ref;
1119 v_child_models_tbl cz_upgrade.t_ref;
1120 v_lce_hdrs_tbl cz_upgrade.t_ref;
1121 v_published_model_count NUMBER := 0;
1122
1123 BEGIN
1124 IF (p_model_flag = 'S') THEN
1125
1126 BEGIN
1127 SELECT lce_header_id
1128 BULK
1129 COLLECT
1130 INTO x_lce_header_tbl
1131 FROM cz_lce_headers
1132 WHERE cz_lce_headers.deleted_flag = '0'
1133 AND cz_lce_headers.component_id IN
1134 (
1135 SELECT ps_node_id
1136 FROM cz_ps_nodes
1137 WHERE cz_ps_nodes.deleted_flag = '0'
1138 AND cz_ps_nodes.devl_project_id IN
1139 (
1140 SELECT object_id
1141 FROM cz_rp_entries
1142 WHERE cz_rp_entries.deleted_flag = '0'
1143 AND cz_rp_entries.object_type = 'PRJ'
1144 )
1145 );
1146 EXCEPTION
1147 WHEN OTHERS THEN
1148 RAISE;
1149 END;
1150
1151 ELSIF (p_model_flag = 'P') THEN
1152
1153 ------get model_id of all valid target publications
1154 BEGIN
1155 SELECT model_id
1156 BULK
1157 COLLECT
1158 INTO v_published_root_models_tbl
1159 FROM cz_model_publications
1160 WHERE cz_model_publications.deleted_flag = '0'
1161 AND cz_model_publications.export_status = 'OK'
1162 AND cz_model_publications.source_target_flag = 'T';
1163 EXCEPTION
1164 WHEN OTHERS THEN
1165 RAISE;
1166 END;
1167
1168 ------get the child model(s) of the published models
1169 v_all_published_models_tbl.DELETE;
1170 IF (v_published_root_models_tbl.COUNT > 0) THEN
1171 FOR rootModel IN v_published_root_models_tbl.FIRST..v_published_root_models_tbl.LAST
1172 LOOP
1173 IF (v_published_root_models_tbl(rootModel) IS NOT NULL) THEN
1174 v_child_models_tbl.DELETE;
1175 BEGIN
1176 SELECT component_id
1177 BULK
1178 COLLECT
1179 INTO v_child_models_tbl
1180 FROM cz_model_ref_expls
1181 WHERE cz_model_ref_expls.model_id = v_published_root_models_tbl(rootModel)
1182 AND cz_model_ref_expls.deleted_flag = '0';
1183 -----AND cz_model_ref_expls.ps_node_type = '263';
1184 EXCEPTION
1185 WHEN NO_DATA_FOUND THEN
1186 x_err_message := substr(SQLERRM,1,2000);
1187 WHEN OTHERS THEN
1188 x_err_message := substr(SQLERRM,1,2000);
1189 END;
1190 IF (v_child_models_tbl.COUNT > 0) THEN
1191 FOR childModel IN v_child_models_tbl.FIRST..v_child_models_tbl.LAST
1192 LOOP
1193 v_published_model_count := v_all_published_models_tbl.COUNT + 1;
1194 v_all_published_models_tbl(v_published_model_count) := v_child_models_tbl(childModel);
1195 END LOOP;
1196 END IF;
1197
1198 v_published_model_count := v_all_published_models_tbl.COUNT + 1;
1199 v_all_published_models_tbl(v_published_model_count) := v_published_root_models_tbl(rootModel);
1200 END IF;
1201 END LOOP;
1202 END IF;
1203
1204 IF (v_all_published_models_tbl.COUNT > 0) THEN
1205 FOR pubLceHdr IN v_all_published_models_tbl.FIRST..v_all_published_models_tbl.LAST
1206 LOOP
1207 BEGIN
1208 SELECT lce_header_id
1209 BULK
1210 COLLECT
1211 INTO v_lce_hdrs_tbl
1212 FROM cz_lce_headers
1213 WHERE cz_lce_headers.component_id = v_all_published_models_tbl(pubLceHdr)
1214 AND cz_lce_headers.deleted_flag = '0';
1215 EXCEPTION
1216 WHEN NO_DATA_FOUND THEN
1217 x_err_message := 'The lce_header_id for component '||v_all_published_models_tbl(pubLceHdr)||' does not have data in cz_lce_headers';
1218 WHEN OTHERS THEN
1219 RAISE;
1220 END;
1221
1222 IF (v_lce_hdrs_tbl.COUNT > 0) THEN
1223 FOR k IN v_lce_hdrs_tbl.FIRST..v_lce_hdrs_tbl.LAST
1224 LOOP
1225 v_published_model_count := x_lce_header_tbl.COUNT + 1;
1226 x_lce_header_tbl(v_published_model_count) := v_lce_hdrs_tbl(k);
1227 END LOOP;
1228 END IF;
1229 END LOOP;
1230 END IF;
1231 END IF;
1232
1233
1234 x_err_message := NULL;
1235 EXCEPTION
1236 WHEN OTHERS THEN
1237 x_err_message := substr(SQLERRM,1,2000);
1238 RAISE;
1239 END get_lce_headers;
1240 ---------------------------------------------
1241 ---------procedure that inserts LCE data into cz_lce_load_specs table
1242 ---------
1243 PROCEDURE cz_populate_lce_load_specs(p_lce_header_id IN NUMBER,
1244 x_populate_error_flag IN OUT NOCOPY VARCHAR2,
1245 x_populate_error_msg IN OUT NOCOPY VARCHAR2)
1246 IS
1247
1248 v_lce_header_id cz_lce_headers.lce_header_id%TYPE;
1249 v_component_id cz_lce_headers.component_id%TYPE;
1250 v_model_ref_expl_id cz_lce_headers.model_ref_expl_id%TYPE;
1251 v_net_type cz_lce_headers.net_type%TYPE;
1252 v_devl_project_id cz_lce_headers.devl_project_id%TYPE;
1253 v_model_id cz_lce_headers.devl_project_id%TYPE;
1254 v_exception_err NUMBER := 0;
1255
1256 BEGIN
1257 IF (p_lce_header_id IS NOT NULL) THEN
1258 BEGIN
1259 SELECT lce_header_id
1260 ,component_id
1261 ,model_ref_expl_id
1262 ,net_type
1263 ,devl_project_id
1264 INTO v_lce_header_id
1265 ,v_component_id
1266 ,v_model_ref_expl_id
1267 ,v_net_type
1268 ,v_devl_project_id
1269 FROM cz_lce_headers
1270 WHERE cz_lce_headers.lce_header_id = p_lce_header_id
1271 AND cz_lce_headers.deleted_flag = '0';
1272 EXCEPTION
1273 WHEN NO_DATA_FOUND THEN
1274 x_populate_error_msg := 'The lce_deader_id '||p_lce_header_id||' does not have data in cz_lce_headers';
1275 RAISE;
1276 WHEN OTHERS THEN
1277 RAISE;
1278 END;
1279
1280 IF (v_net_type = 1) THEN
1281 ----get the devl_project_id from cz_ps_nodes where ps_node_id is the v_component_id
1282 BEGIN
1283 SELECT devl_project_id
1284 INTO v_model_id
1285 FROM cz_ps_nodes
1286 WHERE cz_ps_nodes.deleted_flag = '0'
1287 AND cz_ps_nodes.ps_node_id = v_component_id;
1288 EXCEPTION
1289 WHEN NO_DATA_FOUND THEN
1290 x_populate_error_msg := 'The component_id '||v_component_id||' of lce_header_id '||p_lce_header_id||' does not have associated record in cz_ps_nodes';
1291 RAISE;
1292 WHEN OTHERS THEN
1293 RAISE;
1294 END;
1295
1296 -----get explosion id for above selected v_model_id
1297 BEGIN
1298 SELECT model_ref_expl_id
1299 INTO v_model_ref_expl_id
1300 FROM cz_model_ref_expls
1301 WHERE cz_model_ref_expls.model_id = v_model_id
1302 AND cz_model_ref_expls.component_id = v_component_id
1303 AND cz_model_ref_expls.deleted_flag = '0';
1304 EXCEPTION
1305 WHEN NO_DATA_FOUND THEN
1306 x_populate_error_msg := 'The component_id '||v_component_id||' of lce_header_id '||p_lce_header_id||' does not have associated record in cz_model_ref_expls for model_id '||v_model_id;
1307 RAISE;
1308 WHEN OTHERS THEN
1309 RAISE;
1310 END;
1311
1312 ELSIF (v_net_type = 2) THEN
1313 ------get the component_id from cz_model_ref_expls for v_model_ref_expl_id
1314 BEGIN
1315 SELECT component_id
1316 INTO v_component_id
1317 FROM cz_model_ref_expls
1318 WHERE cz_model_ref_expls.model_ref_expl_id = v_model_ref_expl_id
1319 AND cz_model_ref_expls.deleted_flag = '0';
1320 EXCEPTION
1321 WHEN NO_DATA_FOUND THEN
1322 x_populate_error_msg := 'The component_id '||v_component_id||' of lce_header_id '||p_lce_header_id||' does not have associated record in cz_ps_nodes';
1323 RAISE;
1324 WHEN OTHERS THEN
1325 RAISE;
1326 END;
1327
1328 ------get the component_id from cz_model_ref_expls for v_model_ref_expl_id
1329 BEGIN
1330 SELECT devl_project_id
1331 INTO v_model_id
1332 FROM cz_ps_nodes
1333 WHERE cz_ps_nodes.deleted_flag = '0'
1334 AND cz_ps_nodes.ps_node_id = v_component_id;
1335 EXCEPTION
1336 WHEN NO_DATA_FOUND THEN
1337 x_populate_error_msg := 'The component_id '||v_component_id||' of lce_header_id '||p_lce_header_id||' does not have associated record in cz_ps_nodes';
1338 RAISE;
1339 WHEN OTHERS THEN
1340 RAISE;
1341 END;
1342
1343 END IF; /* end if of net_type */
1344 END IF; /* end if of p_lce_header_id is not null */
1345
1346 BEGIN
1347 insert into cz_lce_load_specs ( ATTACHMENT_EXPL_ID
1348 ,LCE_HEADER_ID
1349 ,REQUIRED_EXPL_ID
1350 ,ATTACHMENT_COMP_ID
1351 ,MODEL_ID
1352 ,NET_TYPE )
1353 values ( v_model_ref_expl_id
1354 ,v_lce_header_id
1355 ,v_model_ref_expl_id
1356 ,v_component_id
1357 ,v_model_id
1358 ,v_net_type );
1359
1360 UPDATE cz_lce_headers
1361 SET model_ref_expl_id = v_model_ref_expl_id,
1362 devl_project_id = v_model_id,
1363 NBR_REQUIRED_EXPLS = 0
1364 WHERE lce_header_id = v_lce_header_id;
1365 EXCEPTION
1366 WHEN OTHERS THEN
1367 ROLLBACK;
1368 END;
1369 x_populate_error_flag := '0';
1370 x_populate_error_msg := SQLERRM;
1371
1372 COMMIT;
1373 EXCEPTION
1374 WHEN OTHERS THEN
1375 cz_upgrade.v_lce_hdr := p_lce_header_id ;
1376 x_populate_error_flag := '1';
1377 report_upgrade_logic_errors(cz_upgrade.v_schema_version,'18',cz_upgrade.v_lce_hdr,x_populate_error_msg,'LOGIC_UPGRADE' );
1378 END cz_populate_lce_load_specs;
1379 -------------------------------------------
1380 ----------procedure that upgrades the existing logic files to 18 schema
1381
1382 PROCEDURE upgrade_logic_files_to_18
1383 IS
1384
1385 v_source_lce_headers_tbl cz_upgrade.t_ref;
1386 v_published_lce_headers_tbl cz_upgrade.t_ref;
1387 x_populate_flag VARCHAR2(1);
1388 x_populate_error VARCHAR2(2000);
1389 v_major_version cz_db_settings.value%TYPE;
1390
1391
1392 BEGIN
1393 -----get the existing major version of the schema
1394 v_major_version := LTRIM(RTRIM(get_major_version));
1395
1396 IF (v_major_version = '14') THEN
1397 -----logic is generated for all source models. No published models exist in a 14 build
1398 upgrade_logic_from_14;
1399
1400 ELSIF (v_major_version IN ('15','16','17')) THEN
1401
1402 -----get valid lce headers for all source models
1403 get_lce_headers('S', v_source_lce_headers_tbl,x_populate_error);
1404
1405 -----get valid lce headers for all published models
1406 get_lce_headers('P', v_published_lce_headers_tbl,x_populate_error);
1407
1408 -------upgrade logic files of source models
1409 IF (v_source_lce_headers_tbl.COUNT > 0) THEN
1410 FOR sourceLceHeader IN v_source_lce_headers_tbl.FIRST..v_source_lce_headers_tbl.LAST
1411 LOOP
1412 IF (has_to_populate_load_specs(v_source_lce_headers_tbl(sourceLceHeader))) THEN
1413 cz_populate_lce_load_specs(v_source_lce_headers_tbl(sourceLceHeader), x_populate_flag, x_populate_error);
1414 END IF;
1415 END LOOP;
1416 END IF;
1417
1418 -------upgrade logic files of published models
1419 IF (v_published_lce_headers_tbl.COUNT > 0) THEN
1420 FOR publishedLceHeader IN v_published_lce_headers_tbl.FIRST..v_published_lce_headers_tbl.LAST
1421 LOOP
1422 IF (has_to_populate_load_specs(v_published_lce_headers_tbl(publishedLceHeader))) THEN
1423 cz_populate_lce_load_specs(v_published_lce_headers_tbl(publishedLceHeader), x_populate_flag,x_populate_error);
1424 END IF;
1425 END LOOP;
1426 END IF;
1427 ELSE
1428 x_populate_error := 'Logic upgrade is not required when upgrading from an 18 to 18 schema or a higher version' ;
1429 report_upgrade_logic_errors(v_major_version,'18',cz_upgrade.v_lce_hdr,x_populate_error,'LOGIC_UPGRADE');
1430 END IF;
1431 EXCEPTION
1432 WHEN OTHERS THEN
1433 report_upgrade_logic_errors(v_major_version,'18',cz_upgrade.v_lce_hdr,x_populate_error,'LOGIC_UPGRADE');
1434 END upgrade_logic_files_to_18;
1435 --------------------------------------------
1436 --------------procedure that verifies upgraded logic
1437 PROCEDURE verify_logic (x_logic_status IN OUT NOCOPY VARCHAR2)
1438 IS
1439
1440 v_source_lce_headers_tbl cz_upgrade.t_ref;
1441 v_published_lce_headers_tbl cz_upgrade.t_ref;
1442 v_all_lce_headers cz_upgrade.t_ref;
1443 v_attachment_expl_id_tbl cz_upgrade.t_ref;
1444 v_net_type_tbl cz_upgrade.t_ref;
1445 v_required_expl_tbl cz_upgrade.t_ref;
1446 x_error_msg VARCHAR2(2000);
1447 allHeadersCount NUMBER := 0;
1448 SOURCELCEHEADER NUMBER := 0;
1449 publishedLceHeader NUMBER := 0;
1450 allLceHeader NUMBER := 0;
1451 x_error_status NUMBER := 0;
1452 v_required_expl_chk_count NUMBER := 0;
1453 V_ATTACHMENT_EXPL_ID NUMBER := 0;
1454
1455
1456 BEGIN
1457
1458 -----get valid lce headers for all source models
1459 get_lce_headers('S', v_source_lce_headers_tbl,x_error_msg);
1460
1461 -----get valid lce headers for all published models
1462 get_lce_headers('P', v_published_lce_headers_tbl,x_error_msg);
1463
1464 ----accumulate source and published lce headers into one array
1465 v_all_lce_headers.DELETE;
1466 IF (v_source_lce_headers_tbl.COUNT > 0) THEN
1467 FOR sourceLceHeader IN v_source_lce_headers_tbl.FIRST..v_source_lce_headers_tbl.LAST
1468 LOOP
1469 allHeadersCount := v_all_lce_headers.COUNT + 1;
1470 v_all_lce_headers(allHeadersCount) := v_source_lce_headers_tbl(sourceLceHeader);
1471 END LOOP;
1472 END IF;
1473
1474 IF (v_published_lce_headers_tbl.COUNT > 0) THEN
1475 FOR publishedLceHeader IN v_published_lce_headers_tbl.FIRST..v_published_lce_headers_tbl.LAST
1476 LOOP
1477 allHeadersCount := v_all_lce_headers.COUNT + 1;
1478 v_all_lce_headers(allHeadersCount) := v_published_lce_headers_tbl(publishedLceHeader);
1479 END LOOP;
1480 END IF;
1481
1482 ----validate all lce_headers
1483 IF (v_all_lce_headers.COUNT > 0) THEN
1484 -------validate attachment expl id
1485 FOR allLceHeader IN v_all_lce_headers.FIRST..v_all_lce_headers.LAST
1486 LOOP
1487 v_attachment_expl_id_tbl.DELETE;
1488 BEGIN
1489 SELECT distinct attachment_expl_id
1490 BULK
1491 COLLECT
1492 INTO v_attachment_expl_id_tbl
1493 FROM cz_lce_load_specs
1494 WHERE cz_lce_load_specs.lce_header_id = v_all_lce_headers(allLceHeader)
1495 AND cz_lce_load_specs.deleted_flag = '0';
1496 EXCEPTION
1497 WHEN NO_DATA_FOUND THEN
1498 x_error_msg := 'No attachment expl id found in cz_lce_load_specs for lce_header_id '||v_all_lce_headers(allLceHeader);
1499 report_upgrade_logic_errors(NULL,NULL,v_all_lce_headers(allLceHeader),x_error_msg,'VERIFY_LOGIC');
1500 WHEN OTHERS THEN
1501 x_error_status := -1;
1502 x_error_msg := 'Error in retrieving attachment expl id from cz_load_specs for lce_header_id '||v_all_lce_headers(allLceHeader)||' '||SQLERRM;
1503 report_upgrade_logic_errors(NULL,NULL,v_all_lce_headers(allLceHeader),x_error_msg,'VERIFY_LOGIC');
1504 END;
1505
1506 v_net_type_tbl.DELETE;
1507 BEGIN
1508 SELECT distinct net_type
1509 BULK
1510 COLLECT
1511 INTO v_net_type_tbl
1512 FROM cz_lce_load_specs
1513 WHERE cz_lce_load_specs.lce_header_id = v_all_lce_headers(allLceHeader)
1514 AND cz_lce_load_specs.deleted_flag = '0';
1515 EXCEPTION
1516 WHEN NO_DATA_FOUND THEN
1517 x_error_msg := 'No net_type found in cz_lce_load_specs for lce_header_id '||v_all_lce_headers(allLceHeader);
1518 report_upgrade_logic_errors(NULL,NULL,v_all_lce_headers(allLceHeader),x_error_msg,'VERIFY_LOGIC');
1519 WHEN OTHERS THEN
1520 x_error_status := -1;
1521 x_error_msg := 'Error in retrieving net type from cz_load_specs for lce_header_id '||v_all_lce_headers(allLceHeader)||' '||SQLERRM;
1522 report_upgrade_logic_errors(NULL,NULL,v_all_lce_headers(allLceHeader),x_error_msg,'VERIFY_LOGIC');
1523 END;
1524
1525
1526 -----all records with the same lce header id must have the same attachment expl id and net type
1527 IF (v_attachment_expl_id_tbl.COUNT > 1) THEN
1528 x_error_status := -1;
1529 x_error_msg := 'For the same lce header id '||v_all_lce_headers(allLceHeader)||' attachment expl id is not the same';
1530 report_upgrade_logic_errors(NULL,NULL,v_all_lce_headers(allLceHeader),x_error_msg,'VERIFY_LOGIC');
1531 END IF;
1532
1533 -----all records with the same lce header id must have the same attachment expl id and net type
1534 IF (v_net_type_tbl.COUNT > 1) THEN
1535 x_error_status := -1;
1536 x_error_msg := 'For the same lce header id '||v_all_lce_headers(allLceHeader)||' net type is not the same';
1537 report_upgrade_logic_errors(NULL,NULL,v_all_lce_headers(allLceHeader),x_error_msg,'VERIFY_LOGIC');
1538 END IF;
1539
1540 -----required expl id is the same expl node or a descendant expl node of attachment expl id
1541 v_required_expl_tbl.DELETE;
1542 BEGIN
1543 SELECT distinct required_expl_id
1544 BULK
1545 COLLECT
1546 INTO v_required_expl_tbl
1547 FROM cz_lce_load_specs
1548 WHERE cz_lce_load_specs.lce_header_id = v_all_lce_headers(allLceHeader)
1549 AND cz_lce_load_specs.deleted_flag = '0';
1550 EXCEPTION
1551 WHEN NO_DATA_FOUND THEN
1552 x_error_msg := 'No required_expl_id found in cz_lce_load_specs for lce_header_id '||v_all_lce_headers(allLceHeader);
1553 report_upgrade_logic_errors(NULL,NULL,v_all_lce_headers(allLceHeader),x_error_msg,'VERIFY_LOGIC');
1554 WHEN OTHERS THEN
1555 x_error_status := -1;
1556 x_error_msg := 'Error in retrieving required_expl_id from cz_load_specs for lce_header_id '||v_all_lce_headers(allLceHeader)||' '||SQLERRM;
1557 report_upgrade_logic_errors(NULL,NULL,v_all_lce_headers(allLceHeader),x_error_msg,'VERIFY_LOGIC');
1558 END;
1559
1560 IF (v_required_expl_tbl.COUNT > 0) THEN
1561 FOR reqdExplId IN v_required_expl_tbl.FIRST..v_required_expl_tbl.LAST
1562 LOOP
1563 BEGIN
1564 SELECT attachment_expl_id
1565 INTO v_attachment_expl_id
1566 FROM cz_lce_load_specs
1567 WHERE cz_lce_load_specs.required_expl_id = v_required_expl_tbl(reqdExplId)
1568 AND cz_lce_load_specs.lce_header_id = v_all_lce_headers(allLceHeader)
1569 AND cz_lce_load_specs.deleted_flag = '0';
1570 EXCEPTION
1571 WHEN NO_DATA_FOUND THEN
1572 x_error_msg := 'No attachment expl id found in cz_lce_load_specs for lce_header_id '||v_all_lce_headers(allLceHeader)||' and required_expl_id '||v_required_expl_tbl(reqdExplId);
1573 report_upgrade_logic_errors(NULL,NULL,v_all_lce_headers(allLceHeader),x_error_msg,'VERIFY_LOGIC');
1574 WHEN OTHERS THEN
1575 x_error_status := -1;
1576 x_error_msg := 'Error in retrieving attachment expl id from cz_load_specs for lce_header_id '||v_all_lce_headers(allLceHeader)||' '||SQLERRM;
1577 report_upgrade_logic_errors(NULL,NULL,v_all_lce_headers(allLceHeader),x_error_msg,'VERIFY_LOGIC');
1578 END;
1579
1580 IF (v_attachment_expl_id IS NOT NULL) THEN
1581 v_required_expl_chk_count := 0;
1582 SELECT count(*)
1583 INTO v_required_expl_chk_count
1584 FROM cz_model_ref_expls
1585 WHERE cz_model_ref_expls.model_id = (SELECT model_id
1586 FROM cz_model_ref_expls t
1587 WHERE t.model_ref_expl_id = v_attachment_expl_id
1588 AND t.deleted_flag = '0')
1589 AND cz_model_ref_expls.model_ref_expl_id = v_required_expl_tbl(reqdExplId)
1590 AND cz_model_ref_expls.deleted_flag = '0';
1591
1592 IF (v_required_expl_chk_count = 0) THEN
1593 x_error_status := -1;
1594 x_error_msg := 'The required expl id '||v_required_expl_tbl(reqdExplId)||' is not in the explosion tree of root expl id '||v_attachment_expl_id;
1595 report_upgrade_logic_errors(NULL,NULL,v_all_lce_headers(allLceHeader),x_error_msg,'VERIFY_LOGIC');
1596 END IF;
1597 END IF;
1598 END LOOP;
1599 END IF;
1600 END LOOP;
1601 END IF;
1602
1603 IF (x_error_status = -1) THEN
1604 x_logic_status := 'Verification logic has errors for some lce headers. Check cz_db_logs using the query select message from cZ_db_logs where caller = VERIFY_LOGIC';
1605 ELSE
1606 x_logic_status := 'Verification logic reported no errors';
1607 END IF;
1608 END verify_logic ;
1609 ---------------------------------------------------------------------------------------
1610 PROCEDURE VERIFY_RULES(inDevlProjectId IN NUMBER,
1611 thisRunId IN OUT NOCOPY NUMBER)
1612 IS
1613
1614 TYPE tShortStringArray IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
1615 GenHeader VARCHAR2(100) := '$Header: czupgrdb.pls 120.2 2008/06/05 20:42:40 misheehy ship $';
1616
1617 TYPE tIntegerArray IS TABLE OF PLS_INTEGER INDEX BY BINARY_INTEGER;
1618 TYPE tIntegerArray_idx_vc2 IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(15); --jonatara:int2long:bug6054920
1619 TYPE tStringArray IS TABLE OF VARCHAR2(2000) INDEX BY BINARY_INTEGER;
1620 TYPE tNumberArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1621 TYPE tDateArray IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1622
1623 TYPE tPsNodeId IS TABLE OF cz_ps_nodes.ps_node_id%TYPE INDEX BY BINARY_INTEGER;
1624 TYPE tItemId IS TABLE OF cz_ps_nodes.item_id%TYPE INDEX BY BINARY_INTEGER;
1625 TYPE tPersistentId IS TABLE OF cz_ps_nodes.persistent_node_id%TYPE INDEX BY BINARY_INTEGER;
1626 TYPE tPersistentId_idx_vc2 IS TABLE OF cz_ps_nodes.persistent_node_id%TYPE INDEX BY VARCHAR2(15); --jonatara:int2long:bug6054920
1627 TYPE tPsNodeType IS TABLE OF cz_ps_nodes.ps_node_type%TYPE INDEX BY BINARY_INTEGER;
1628 TYPE tInitialValue IS TABLE OF cz_ps_nodes.initial_value%TYPE INDEX BY BINARY_INTEGER;
1629 TYPE tInitNumVal IS TABLE OF cz_ps_nodes.initial_num_value%TYPE INDEX BY BINARY_INTEGER; -- sselahi
1630 TYPE tParentId IS TABLE OF cz_ps_nodes.parent_id%TYPE INDEX BY BINARY_INTEGER;
1631 TYPE tMinimum IS TABLE OF cz_ps_nodes.minimum%TYPE INDEX BY BINARY_INTEGER;
1632 TYPE tMaximum IS TABLE OF cz_ps_nodes.maximum%TYPE INDEX BY BINARY_INTEGER;
1633 TYPE tVirtualFlag IS TABLE OF cz_ps_nodes.virtual_flag%TYPE INDEX BY BINARY_INTEGER;
1634 TYPE tFeatureType IS TABLE OF cz_ps_nodes.feature_type%TYPE INDEX BY BINARY_INTEGER;
1635 TYPE tName IS TABLE OF cz_ps_nodes.name%TYPE INDEX BY BINARY_INTEGER;
1636 TYPE tDescriptionId IS TABLE OF cz_ps_nodes.intl_text_id%TYPE INDEX BY BINARY_INTEGER;
1637 TYPE tMinimumSel IS TABLE OF cz_ps_nodes.minimum_selected%TYPE INDEX BY BINARY_INTEGER;
1638 TYPE tMaximumSel IS TABLE OF cz_ps_nodes.maximum_selected%TYPE INDEX BY BINARY_INTEGER;
1639 TYPE tBomRequired IS TABLE OF cz_ps_nodes.bom_required_flag%TYPE INDEX BY BINARY_INTEGER;
1640 TYPE tReferenceId IS TABLE OF cz_ps_nodes.reference_id%TYPE INDEX BY BINARY_INTEGER;
1641 TYPE tReferenceId_idx_vc2 IS TABLE OF cz_ps_nodes.reference_id%TYPE INDEX BY VARCHAR2(15); --jonatara:int2long:bug6054920
1642 TYPE tUsageMask IS TABLE OF cz_ps_nodes.effective_usage_mask%TYPE INDEX BY BINARY_INTEGER;
1643 TYPE tDecimalQty IS TABLE OF cz_ps_nodes.decimal_qty_flag%TYPE INDEX BY BINARY_INTEGER;
1644 TYPE tDecimalQty_idx_vc2 IS TABLE OF cz_ps_nodes.decimal_qty_flag%TYPE INDEX BY VARCHAR2(15); --jonatara:int2long:bug6054920
1645
1646 TYPE tSetEffFrom IS TABLE OF cz_effectivity_sets.effective_from%TYPE INDEX BY BINARY_INTEGER;
1647 TYPE tSetEffUntil IS TABLE OF cz_effectivity_sets.effective_until%TYPE INDEX BY BINARY_INTEGER;
1648 TYPE tSetEffId IS TABLE OF cz_effectivity_sets.effectivity_set_id%TYPE INDEX BY BINARY_INTEGER;
1649 TYPE tSetName IS TABLE OF cz_effectivity_sets.name%TYPE INDEX BY BINARY_INTEGER;
1650
1651 TYPE tHeaderId IS TABLE OF cz_lce_headers.lce_header_id%TYPE INDEX BY BINARY_INTEGER;
1652 TYPE tExplNodeId IS TABLE OF cz_model_ref_expls.model_ref_expl_id%TYPE INDEX BY BINARY_INTEGER;
1653 TYPE tExplNodeId_idx_vc2 IS TABLE OF cz_model_ref_expls.model_ref_expl_id%TYPE INDEX BY VARCHAR2(15);
1654
1655 IsLogicGenerated tIntegerArray_idx_vc2; --jonatara:int2long:bug6054920
1656
1657 glPsNodeId tPsNodeId;
1658 glItemId tItemId;
1659 glPersistentId tPersistentId_idx_vc2; --jonatara:int2long:bug6054920
1660 glReferenceId tReferenceId_idx_vc2; --jonatara:int2long:bug6054920
1661 glPsNodeType tPsNodeType;
1662 glIndexByPsNodeId tIntegerArray_idx_vc2; --jonatara:int2long:bug6054920
1663 glLastChildIndex tIntegerArray;
1664 glParentId tParentId;
1665 glFeatureType tFeatureType;
1666 glName tName;
1667 glBomRequired tBomRequired;
1668 glHeaderByPsNodeId tNumberArray;
1669 glEffFrom tDateArray;
1670 glEffUntil tDateArray;
1671 glUsageMask tUsageMask;
1672 glMinimum tMinimum;
1673 glMaximum tMaximum;
1674 glMinimumSel tMinimumSel;
1675 glMaximumSel tMaximumSel;
1676 glVirtualFlag tVirtualFlag;
1677 glDecimalQty tDecimalQty_idx_vc2; --jonatara:int2long:bug6054920
1678 featOptionsCount tIntegerArray;
1679
1680 v_NodeIdByComponent tExplNodeId_idx_vc2; --jonatara:int2long:bug6054920
1681
1682 globalCount PLS_INTEGER := 1;
1683 --Just to support debugging
1684 nDebug PLS_INTEGER := 7777777;
1685 --Auxiliery parameters for reporting
1686 nParam PLS_INTEGER;
1687 errorMessage VARCHAR2(2000);
1688
1689 --Referencing level indicator and model stack
1690 globalLevel PLS_INTEGER := 0;
1691 globalStack tIntegerArray;
1692 ---------------------------------------------------------------------------------------
1693 --Reporting procedure
1694
1695 PROCEDURE REPORT(inMessage IN VARCHAR2, inUrgency IN PLS_INTEGER) IS
1696 BEGIN
1697
1698 INSERT INTO cz_db_logs (message, statuscode, caller, urgency, run_id)
1699 VALUES (SUBSTR(inMessage, 1, 2000), nDebug, 'Rules Verification', inUrgency, thisRunId);
1700 COMMIT;
1701
1702 EXCEPTION
1703 WHEN OTHERS THEN
1704 RAISE CZ_G_UNABLE_TO_REPORT_ERROR;
1705 END;
1706 ---------------------------------------------------------------------------------------
1707 PROCEDURE GENERATE_COMPONENT_TREE(inComponentId IN NUMBER,
1708 inProjectId IN NUMBER)
1709 IS
1710
1711 TYPE tNodeDepth IS TABLE OF cz_model_ref_expls.node_depth%TYPE INDEX BY BINARY_INTEGER;
1712 TYPE tNodeType IS TABLE OF cz_model_ref_expls.ps_node_type%TYPE INDEX BY BINARY_INTEGER;
1713 TYPE tVirtualFlag IS TABLE OF cz_model_ref_expls.virtual_flag%TYPE INDEX BY BINARY_INTEGER;
1714 TYPE tParentId IS TABLE OF cz_model_ref_expls.parent_expl_node_id%TYPE INDEX BY BINARY_INTEGER;
1715 TYPE tPsNodeId IS TABLE OF cz_model_ref_expls.component_id%TYPE INDEX BY BINARY_INTEGER;
1716 TYPE tChildModelExpl IS TABLE OF cz_model_ref_expls.child_model_expl_id%TYPE INDEX BY BINARY_INTEGER;
1717 TYPE tExplNodeType IS TABLE OF cz_model_ref_expls.expl_node_type%TYPE INDEX BY BINARY_INTEGER;
1718
1719 ntPsNodeId tPsNodeId;
1720 ntItemId tItemId;
1721 ntPersistentId tPersistentId;
1722 ntPsNodeType tPsNodeType;
1723 ntInitialValue tInitialValue;
1724 ntInitNumVal tInitNumVal; -- sselahi
1725 ntParentId tParentId;
1726 ntMinimum tMinimum;
1727 ntMaximum tMaximum;
1728 ntVirtualFlag tVirtualFlag;
1729 ntFeatureType tFeatureType;
1730 ntName tName;
1731 ntDescriptionId tDescriptionId;
1732 ntMinimumSel tMinimumSel;
1733 ntMaximumSel tMaximumSel;
1734 ntBomRequired tBomRequired;
1735 ntReferenceId tReferenceId;
1736 dtEffFrom tDateArray;
1737 dtEffUntil tDateArray;
1738 vtUsageMask tUsageMask;
1739 ntEffSetId tSetEffId;
1740 ntDecimalQty tDecimalQty;
1741
1742 v_tNodeDepth tNodeDepth;
1743 v_tNodeType tNodeType;
1744 v_tVirtualFlag tVirtualFlag;
1745 v_tParentId tParentId;
1746 v_tPsNodeId tPsNodeId;
1747 v_tReferringId tPsNodeId;
1748 v_tChildModelExpl tChildModelExpl;
1749 v_tExplNodeType tExplNodeType;
1750 v_NodeId tExplNodeId;
1751
1752 v_IndexByNodeId tIntegerArray;
1753 v_TypeByExplId tExplNodeType;
1754
1755 thisComponentExplId cz_model_ref_expls.model_ref_expl_id%TYPE;
1756 thisProjectId cz_devl_projects.devl_project_id%TYPE;
1757 thisRootExplIndex PLS_INTEGER;
1758
1759 i PLS_INTEGER;
1760 j PLS_INTEGER;
1761 localCount PLS_INTEGER;
1762 optionCounter PLS_INTEGER;
1763 ---------------------------------------------------------------------------------------
1764 PROCEDURE GENERATE_RULES IS
1765
1766 TYPE tExprType IS TABLE OF cz_expression_nodes.expr_type%TYPE INDEX BY BINARY_INTEGER;
1767 TYPE tExprSubtype IS TABLE OF cz_expression_nodes.expr_subtype%TYPE INDEX BY BINARY_INTEGER;
1768 TYPE tExprId IS TABLE OF cz_expression_nodes.expr_node_id%TYPE INDEX BY BINARY_INTEGER;
1769 TYPE tExprParentId IS TABLE OF cz_expression_nodes.expr_parent_id%TYPE INDEX BY BINARY_INTEGER;
1770 TYPE tExpressId IS TABLE OF cz_expression_nodes.express_id%TYPE INDEX BY BINARY_INTEGER;
1771 TYPE tExprDataValue IS TABLE OF cz_expression_nodes.data_value%TYPE INDEX BY BINARY_INTEGER;
1772 TYPE tExprDataNumValue IS TABLE OF cz_expression_nodes.data_num_value%TYPE INDEX BY BINARY_INTEGER; -- sselahi
1773 TYPE tExprPropertyId IS TABLE OF cz_expression_nodes.property_id%TYPE INDEX BY BINARY_INTEGER;
1774 TYPE tPresentType IS TABLE OF cz_expressions.present_type%TYPE INDEX BY BINARY_INTEGER;
1775 TYPE tGridColId IS TABLE OF cz_combo_features.grid_col_id%TYPE INDEX BY BINARY_INTEGER;
1776 TYPE tOptionId IS TABLE OF cz_grid_cells.ps_node_id%TYPE INDEX BY BINARY_INTEGER;
1777 TYPE tConsequentFlag IS TABLE OF cz_expression_nodes.consequent_flag%TYPE INDEX BY BINARY_INTEGER;
1778 TYPE tDesFeatureType IS TABLE OF cz_des_chart_features.feature_type%TYPE INDEX BY BINARY_INTEGER;
1779
1780 --The cursor returns all the rules assigned in this project (model)
1781
1782 CURSOR c_rules IS
1783 SELECT rule_id, rule_type, antecedent_id, consequent_id, name, reason_id,
1784 expr_rule_type, rule_folder_id
1785 FROM cz_rules
1786 WHERE devl_project_id = inComponentId
1787 AND deleted_flag = FLAG_NOT_DELETED
1788 AND disabled_flag = FLAG_NOT_DISABLED;
1789
1790 v_tExpressionId tExpressId;
1791 v_tPresentType tPresentType;
1792
1793 v_tExplNodeId tExplNodeId;
1794 v_tExprType tExprType;
1795 v_tExprSubtype tExprSubtype;
1796 v_tExprId tExprId;
1797 v_tExprParentId tExprParentId;
1798 v_tExpressId tExpressId;
1799 v_tExprPsNodeId tExplNodeId;
1800 v_tExprDataValue tExprDataValue;
1801 v_tExprDataNumValue tExprDataNumValue; -- sselahi
1802 v_tExprPropertyId tExprPropertyId;
1803 v_tGridColId tGridColId;
1804 v_tConsequentFlag tConsequentFlag;
1805 v_tFeatureType tDesFeatureType;
1806 v_LoadConditionId tExplNodeId;
1807
1808 v_isExprAdvanced tIntegerArray;
1809 v_InstByLevel tIntegerArray;
1810 v_IndexByExprNodeId tIntegerArray;
1811 v_Assignable tIntegerArray;
1812 v_Participant tIntegerArray;
1813 v_DistinctIndex tIntegerArray;
1814 v_ParticipantIndex tIntegerArray;
1815 v_BeneathNodes tIntegerArray;
1816 v_BeneathCount tIntegerArray;
1817 v_MarkLoadCondition tIntegerArray;
1818 v_tIsHeaderGenerated tIntegerArray;
1819 v_tSequenceNbr tIntegerArray;
1820 v_tLogicNetType tIntegerArray;
1821
1822 v_NodeLogicLevel tIntegerArray;
1823 v_NodeAssignable tIntegerArray;
1824 v_IsConnectorNet tIntegerArray;
1825 v_ChildrenIndex tIntegerArray;
1826 v_NumberOfChildren tIntegerArray;
1827 v_MaxRuleExists tIntegerArray;
1828 v_ProhibitInRules tIntegerArray;
1829 v_ProhibitConnector tIntegerArray;
1830 v_NodeIndexPath tIntegerArray;
1831 v_NodeDownPath tStringArray;
1832 v_AssignedDownPath tStringArray;
1833 v_NodeUpPath tStringArray;
1834 v_RuleQualifiedName tStringArray;
1835
1836 v_LoadHeaders tHeaderId;
1837 v_LoadConditions tStringArray;
1838
1839 nAntecedentId cz_rules.antecedent_id%TYPE;
1840 nConsequentId cz_rules.consequent_id%TYPE;
1841 nRuleId cz_rules.rule_id%TYPE;
1842 nRuleFolderId cz_rules.rule_folder_id%TYPE;
1843 nRuleType cz_rules.rule_type%TYPE;
1844 nRuleOperator cz_rules.expr_rule_type%TYPE;
1845 nReasonId cz_rules.reason_id%TYPE;
1846 vRuleName cz_rules.name%TYPE;
1847 vUsageMask cz_rules.effective_usage_mask%TYPE;
1848 MaxDepthId cz_model_ref_expls.model_ref_expl_id%TYPE;
1849 nAux cz_model_ref_expls.model_ref_expl_id%TYPE;
1850 MaxDepthValue cz_model_ref_expls.node_depth%TYPE;
1851 nHeaderId cz_lce_headers.lce_header_id%TYPE;
1852 nPreviousHeaderId cz_lce_headers.lce_header_id%TYPE;
1853 nNewLogicFileFlag PLS_INTEGER := 0;
1854 nRuleAssignedLevel PLS_INTEGER;
1855 MaxDepthIndex PLS_INTEGER;
1856 logicNetType PLS_INTEGER;
1857
1858 ConnectorIndex PLS_INTEGER;
1859 InstantiableIndex PLS_INTEGER;
1860 AssignableIndex PLS_INTEGER;
1861
1862 jAntecedentRoot PLS_INTEGER;
1863 jConsequentRoot PLS_INTEGER;
1864 jAntecedentRootCount PLS_INTEGER;
1865 jConsequentRootCount PLS_INTEGER;
1866 ListType PLS_INTEGER;
1867 nLocalDefaults PLS_INTEGER := 1000;
1868
1869 nCounter PLS_INTEGER;
1870 distinctCount PLS_INTEGER;
1871 participantCount PLS_INTEGER;
1872 localFeatureType PLS_INTEGER;
1873 localMinimum PLS_INTEGER;
1874 auxIndex PLS_INTEGER;
1875 auxCount PLS_INTEGER;
1876 localString VARCHAR2(2000);
1877 ---------------------------------------------------------------------------------------
1878 --This function returns fully qualified rule name given rule_folder_id of a rule
1879 --and puts generated names into a hash table for reuse.
1880 FUNCTION RULE_NAME RETURN VARCHAR2 IS
1881 vQualified VARCHAR2(2000) := '.';
1882 nRuleName PLS_INTEGER;
1883 BEGIN
1884 IF(nRuleFolderId IS NULL OR nRuleFolderId = -1)THEN RETURN vRuleName; END IF;
1885 IF(v_RuleQualifiedName.EXISTS(nRuleFolderId))THEN RETURN v_RuleQualifiedName(nRuleFolderId) || vRuleName; END IF;
1886 nRuleName := LENGTH(vRuleName);
1887 FOR folder IN (SELECT name FROM cz_rule_folders
1888 WHERE deleted_flag = FLAG_NOT_DELETED
1889 AND parent_rule_folder_id IS NOT NULL
1890 START WITH rule_folder_id = nRuleFolderId
1891 CONNECT BY PRIOR parent_rule_folder_id = rule_folder_id)LOOP
1892 IF(LENGTH(folder.name) + LENGTH(vQualified) + 1 < 2000 - nRuleName)THEN
1893 vQualified := '.' || folder.name || vQualified;
1894 ELSE
1895 EXIT;
1896 END IF;
1897 END LOOP;
1898 v_RuleQualifiedName(nRuleFolderId) := vQualified;
1899 RETURN vQualified || vRuleName;
1900 END;
1901 ---------------------------------------------------------------------------------------
1902 BEGIN --GENERATE_RULES
1903
1904 nDebug := 1000007;
1905
1906 FOR i IN v_NodeId.FIRST..v_NodeId.LAST LOOP
1907
1908 nDebug := 1000008;
1909
1910 --Here we will construct model level downpaths, which do not depend on a particular rule,
1911 --but only on explosion id within the model's explosion tree. When a particular rule is
1912 --assigned, downpaths of its participants may be prepended with segments including all A
1913 --type nodes from assignee to assignable. This corrected downpaths are always used when
1914 --generating names of the rule's participants.
1915
1916 nAux := v_NodeId(i);
1917 auxIndex := v_tNodeDepth(i) + 1;
1918
1919 IF(NOT v_NodeDownPath.EXISTS(nAux))THEN
1920
1921 nDebug := 1000009;
1922
1923 v_NodeDownPath(nAux) := ''; --start building the downpath
1924 v_NodeIndexPath.DELETE; --reset the table
1925
1926 --These all are index values in v_NodeIndexPath.
1927
1928 ConnectorIndex := 0;
1929 InstantiableIndex := auxIndex;
1930 nCounter := 1;
1931 auxCount := 0;
1932
1933 --Go all the way up from the explosion id and find the deepest D node and the
1934 --shallowest connector.
1935
1936 WHILE(nAux IS NOT NULL) LOOP
1937
1938 auxCount := auxCount + 1;
1939 IF(auxCount > 1000)THEN RAISE CZ_S_MODEL_IGNORED; END IF;
1940
1941 IF(v_TypeByExplId(nAux) = EXPL_NODE_TYPE_CONNECTOR)THEN
1942
1943 ConnectorIndex := nCounter;
1944 ELSIF(v_TypeByExplId(nAux) = EXPL_NODE_TYPE_INSTANTIABLE AND InstantiableIndex = auxIndex)THEN
1945
1946 InstantiableIndex := nCounter;
1947 END IF;
1948
1949 v_NodeIndexPath(nCounter) := v_IndexByNodeId(nAux);
1950 nCounter := nCounter + 1;
1951
1952 nAux := v_tParentId(v_IndexByNodeId(nAux));
1953 END LOOP;
1954
1955 IF(InstantiableIndex < ConnectorIndex)THEN
1956
1957 --There are D nodes under connectors on the path - this explosion cannot participate
1958 --in any rule because it would be impossible to assign such a rule. No downpath.
1959 --For reporting purposes store the cz_ps_nodes indexes of the instantiable component
1960 --and the connector, corresponding exception/message is CZ_R_UNASSIGNABLE_RULE.
1961
1962 v_ProhibitInRules(v_NodeId(i)) := glIndexByPsNodeId(v_tPsNodeId(v_NodeIndexPath(InstantiableIndex)));
1963 v_ProhibitConnector(v_NodeId(i)) := glIndexByPsNodeId(v_tReferringId(v_NodeIndexPath(ConnectorIndex)));
1964 ELSE
1965
1966 nDebug := 1000010;
1967
1968 AssignableIndex := InstantiableIndex;
1969
1970 --Find the deepest A node between the deepest D node and the shallowest connector.
1971 --This node is the assignable for the explosion id (can be the D node itself).
1972
1973 FOR n IN ConnectorIndex + 1..InstantiableIndex LOOP
1974 IF(v_tExplNodeType(v_NodeIndexPath(n)) = EXPL_NODE_TYPE_OPTIONAL)THEN
1975
1976 AssignableIndex := n;
1977 EXIT;
1978 END IF;
1979 END LOOP;
1980
1981 v_NodeLogicLevel(v_NodeId(i)) := v_tNodeDepth(v_NodeIndexPath(AssignableIndex));
1982
1983 --Store the explosion node's assignable in the form of the main index.
1984
1985 v_NodeAssignable(v_NodeId(i)) := v_NodeIndexPath(AssignableIndex);
1986
1987 nDebug := 1000011;
1988
1989 --Finally, construct the downpath from the assignable to the explosion id.
1990
1991 FOR n IN 1..AssignableIndex - 1 LOOP
1992 IF(v_tExplNodeType(v_NodeIndexPath(n)) IN (EXPL_NODE_TYPE_OPTIONAL, EXPL_NODE_TYPE_MANDATORY))THEN
1993
1994 --This is a mandatory reference or optional component, add N_<persistent_node_id>
1995 --to the path.
1996
1997 v_NodeDownPath(v_NodeId(i)) := PATH_DELIMITER || 'N_' ||
1998 TO_CHAR(glPersistentId(NVL(v_tReferringId(v_NodeIndexPath(n)), v_tPsNodeId(v_NodeIndexPath(n))))) ||
1999 v_NodeDownPath(v_NodeId(i));
2000 ELSIF(v_tExplNodeType(v_NodeIndexPath(n)) = EXPL_NODE_TYPE_CONNECTOR)THEN
2001
2002 --This is a connector, add C_<model_ref_expl_id> to the path.
2003
2004 v_NodeDownPath(v_NodeId(i)) := PATH_DELIMITER || 'C_' ||
2005 TO_CHAR(v_NodeId(v_NodeIndexPath(n))) || v_NodeDownPath(v_NodeId(i));
2006
2007 --We will stop here, we do not want anything above the deepest connector to be reflected
2008 --in the path. Set a flag for this explosion because we do not want to prepend downpaths
2009 --for these explosions after the rule is assigned either.
2010
2011 v_IsConnectorNet(v_NodeId(i)) := v_NodeIndexPath(n);
2012 EXIT;
2013
2014 END IF;
2015 END LOOP;
2016 END IF;
2017 END IF;
2018 END LOOP;
2019
2020 nDebug := 2;
2021
2022 OPEN c_rules;
2023 LOOP
2024 BEGIN
2025
2026 FETCH c_rules INTO
2027 nRuleId, nRuleType, nAntecedentId, nConsequentId, vRuleName,
2028 nReasonId, nRuleOperator, nRuleFolderId;
2029 EXIT WHEN c_rules%NOTFOUND;
2030
2031 --Do nothing for those rules.
2032
2033 IF(nRuleType NOT IN (RULE_TYPE_FUNC_COMP, RULE_TYPE_RULE_FOLDER))THEN
2034
2035 v_tExplNodeId.DELETE;
2036 v_tExprType.DELETE;
2037 v_tExprSubtype.DELETE;
2038 v_InstByLevel.DELETE;
2039 v_Assignable.DELETE;
2040 v_Participant.DELETE;
2041 v_DistinctIndex.DELETE;
2042 v_ParticipantIndex.DELETE;
2043 v_BeneathNodes.DELETE;
2044 v_BeneathCount.DELETE;
2045 v_MarkLoadCondition.DELETE;
2046 v_LoadConditionId.DELETE;
2047 v_tExprId.DELETE;
2048 v_tExprParentId.DELETE;
2049 v_tExpressId.DELETE;
2050 v_tExprPsNodeId.DELETE;
2051 v_tExprDataValue.DELETE;
2052 v_tExprDataNumValue.DELETE; -- sselahi
2053 v_tExprPropertyId.DELETE;
2054 v_tGridColId.DELETE;
2055 v_ChildrenIndex.DELETE;
2056 v_NodeUpPath.DELETE;
2057 v_IndexByExprNodeId.DELETE;
2058 v_NumberOfChildren.DELETE;
2059
2060 jAntecedentRoot := NULL;
2061 jConsequentRoot := NULL;
2062
2063 nDebug := 3;
2064
2065 --Get the rule participants, differently for different types of rules
2066
2067 IF(nRuleType IN (RULE_TYPE_LOGIC_RULE, RULE_TYPE_NUMERIC_RULE, RULE_TYPE_COMPARISON_RULE))THEN
2068
2069 --Read all the expression nodes for the current rule into memory,
2070 --will be generating from there
2071
2072 SELECT model_ref_expl_id, expr_type, expr_node_id, expr_parent_id, -- sselahi
2073 express_id, expr_subtype, ps_node_id, data_value, data_num_value, property_id, consequent_flag
2074 BULK COLLECT INTO v_tExplNodeId, v_tExprType, v_tExprId, v_tExprParentId,
2075 v_tExpressId, v_tExprSubtype, v_tExprPsNodeId,
2076 v_tExprDataValue, v_tExprDataNumValue, v_tExprPropertyId, v_tConsequentFlag
2077 FROM cz_expression_nodes
2078 WHERE express_id IN (nAntecedentId, nConsequentId)
2079 AND expr_type <> EXPR_NODE_TYPE_PUNCT
2080 AND deleted_flag = FLAG_NOT_DELETED
2081 ORDER BY expr_parent_id, seq_nbr;
2082
2083 nDebug := 31;
2084
2085 --The COUNT attribute can never be NULL, it will be 0 if nothing has been read
2086 IF(v_tExprType.COUNT = 0)THEN
2087 RAISE CZ_R_RULE_IGNORED;
2088 END IF;
2089
2090 FOR i IN v_tExprType.FIRST..v_tExprType.LAST LOOP
2091
2092 --If this rule is against max of some component, mark this component as having such a rule.
2093 --Later we will generate INC for this component's actual max.
2094
2095 IF(v_tExprType(i) = EXPR_NODE_TYPE_SYSPROP AND v_tExprSubtype(i) = SYS_PROP_MAX AND
2096 v_tConsequentFlag(i) = FLAG_IS_CONSEQUENT)THEN
2097
2098 v_MaxRuleExists(v_tExplNodeId(i)) := 1;
2099
2100 END IF;
2101
2102 --Add the indexing option.
2103
2104 v_IndexByExprNodeId(v_tExprId(i)) := i;
2105 END LOOP;
2106
2107 ELSIF(nRuleType = RULE_TYPE_COMPAT_TABLE)THEN
2108
2109 --Read all the features
2110
2111 SELECT model_ref_expl_id, feature_id, grid_col_id, EXPR_NODE_TYPE_NODE
2112 BULK COLLECT INTO v_tExplNodeId, v_tExprPsNodeId, v_tGridColId, v_tExprType
2113 FROM cz_combo_features
2114 WHERE rule_id = nRuleId
2115 AND deleted_flag = FLAG_NOT_DELETED;
2116
2117 nDebug := 32;
2118
2119 --The COUNT property can never be NULL, it will be 0 if nothing has been read
2120 IF(v_tExprType.COUNT < 2)THEN
2121 RAISE CZ_R_RULE_IGNORED;
2122 END IF;
2123
2124 ELSIF(nRuleType = RULE_TYPE_COMPAT_RULE)THEN
2125
2126 SELECT model_ref_expl_id, expr_type, expr_node_id, expr_parent_id, -- sselahi
2127 express_id, expr_subtype, ps_node_id, data_value, data_num_value, property_id
2128 BULK COLLECT INTO v_tExplNodeId, v_tExprType, v_tExprId, v_tExprParentId,
2129 v_tExpressId, v_tExprSubtype, v_tExprPsNodeId,
2130 v_tExprDataValue, v_tExprDataNumValue, v_tExprPropertyId
2131 FROM cz_expression_nodes
2132 WHERE express_id = nAntecedentId
2133 AND expr_type <> EXPR_NODE_TYPE_PUNCT
2134 AND deleted_flag = FLAG_NOT_DELETED
2135 ORDER BY expr_parent_id, seq_nbr;
2136
2137 nDebug := 33;
2138
2139 --The COUNT attribute can never be NULL, it will be 0 if nothing has been read
2140 IF(v_tExprType.COUNT = 0)THEN
2141 RAISE CZ_R_RULE_IGNORED;
2142 END IF;
2143
2144 FOR i IN v_tExprType.FIRST..v_tExprType.LAST LOOP
2145
2146 --Add the indexing option.
2147
2148 v_IndexByExprNodeId(v_tExprId(i)) := i;
2149 END LOOP;
2150
2151 ELSIF(nRuleType = RULE_TYPE_DESIGNCHART_RULE)THEN
2152
2153 --Read all the features
2154
2155 SELECT model_ref_expl_id, feature_id, feature_type, EXPR_NODE_TYPE_NODE
2156 BULK COLLECT INTO v_tExplNodeId, v_tExprPsNodeId, v_tFeatureType, v_tExprType
2157 FROM cz_des_chart_features
2158 WHERE rule_id = nRuleId
2159 AND deleted_flag = FLAG_NOT_DELETED;
2160
2161 nDebug := 34;
2162
2163 --The COUNT attribute can never be NULL, it will be 0 if nothing has been read
2164 IF(v_tExprType.COUNT < 2)THEN
2165 RAISE CZ_R_RULE_IGNORED;
2166 END IF;
2167
2168 ELSE
2169
2170 --Unknown rule type
2171 RAISE CZ_R_RULE_IGNORED;
2172 END IF;
2173
2174 --General rule data validation section - all rule types----------------------------Start
2175
2176 FOR i IN v_tExprType.FIRST..v_tExprType.LAST LOOP
2177
2178 IF(v_tExprPsNodeId(i) IS NOT NULL)THEN
2179
2180 IF(NOT glIndexByPsNodeId.EXISTS(v_tExprPsNodeId(i)))THEN
2181
2182 nDebug := 35;
2183
2184 --Every participating node must actually exist in the product structure
2185
2186 RAISE CZ_R_RULE_IGNORED;
2187 END IF;
2188
2189 IF(v_tExplNodeId(i) IS NULL)THEN
2190
2191 nDebug := 36;
2192
2193 --Every not null ps_node_id should have a not null assosiated model_ref_expl_id (data corruption)
2194
2195 RAISE CZ_R_RULE_IGNORED;
2196
2197 ELSIF(NOT v_IndexByNodeId.EXISTS(v_tExplNodeId(i)))THEN
2198
2199 nDebug := 37;
2200
2201 --All the participants' model_ref_expl_id must be in the current model's explosion table (data corruption)
2202
2203 RAISE CZ_R_RULE_IGNORED;
2204
2205 END IF;
2206 END IF;
2207
2208 nDebug := 38;
2209
2210 IF(v_tExprType(i) = EXPR_NODE_TYPE_NODE)THEN
2211 IF(v_tExprPsNodeId(i) IS NULL)THEN
2212
2213 nDebug := 381;
2214
2215 --Every node type node must have assosiated ps_node_id
2216
2217 RAISE CZ_R_RULE_IGNORED;
2218 END IF;
2219 ELSIF(v_tExprType(i) = EXPR_NODE_TYPE_LITERAL)THEN
2220 IF(v_tExprDataValue(i) IS NULL AND v_tExprDataNumValue(i) IS NULL)THEN
2221
2222 nDebug := 382;
2223
2224 --Every literal must have not null value
2225
2226 RAISE CZ_R_RULE_IGNORED;
2227 END IF;
2228 ELSIF(v_tExprType(i) = EXPR_NODE_TYPE_FEATPROP)THEN
2229 IF(v_tExprPsNodeId(i) IS NULL)THEN
2230
2231 nDebug := 383;
2232
2233 --Every feature property node must have assosiated ps_node_id
2234
2235 RAISE CZ_R_RULE_IGNORED;
2236 ELSIF(v_tExprPropertyId(i) IS NULL)THEN
2237
2238 nDebug := 384;
2239
2240 --Every feature property node must have assosiated property_id
2241
2242 RAISE CZ_R_RULE_IGNORED;
2243 END IF;
2244 END IF;
2245 END LOOP;
2246 --General rule data validation section-----------------------------------------------End
2247
2248 nDebug := 40;
2249
2250 nCounter := 0;
2251 distinctCount := 0;
2252 participantCount := 0;
2253 MaxDepthValue := 0;
2254 MaxDepthIndex := thisRootExplIndex;
2255
2256 FOR i IN v_tExprType.FIRST..v_tExprType.LAST LOOP
2257 IF(v_tExprPsNodeId(i) IS NOT NULL)THEN
2258
2259 participantCount := participantCount + 1;
2260
2261 --Soft fix the explosion nodes whenever necessary:
2262
2263 --When a rule has a reference node as a participant,Developer would put the explosion id
2264 --of the reference node itself instead of the explosion id of its parent. This should be
2265 --fixed in some cases (see below).
2266 --If a participant is a component, then it's the component's MIN,MAX or COUNT and actual
2267 --participant should be it's parent. However,it can also be features of the component or
2268 --some other (new) operator. That's why we make sure that the parent of the component is
2269 --the operator DOT and, in addition, the component is non-virtual.
2270
2271 --Later remark:
2272 --A reference, as well as a component, should be fixed only when they are in combination
2273 --with system property. So, it's not enough to check that the parent operator is DOT, we
2274 --also have to make sure that the another operand is EXPR_SYS_PROP with MIN,MAX or COUNT
2275 --subtype.
2276
2277 auxIndex := glIndexByPsNodeId(v_tExprPsNodeId(i));
2278
2279 IF(glPsNodeType(auxIndex) = PS_NODE_TYPE_REFERENCE OR
2280 (glPsNodeType(auxIndex) = PS_NODE_TYPE_COMPONENT AND glVirtualFlag(auxIndex) = FLAG_NON_VIRTUAL))THEN
2281
2282 IF(v_tExprParentId(i) IS NOT NULL AND
2283 v_tExprType(v_IndexByExprNodeId(v_tExprParentId(i))) = EXPR_NODE_TYPE_OPERATOR AND
2284 v_tExprSubtype(v_IndexByExprNodeId(v_tExprParentId(i))) = OPERATOR_DOT AND
2285 v_tExprType.EXISTS(i + 1) AND
2286 v_tExprType(i + 1) = EXPR_NODE_TYPE_SYSPROP AND
2287 v_tExprSubtype(i + 1) IN (SYS_PROP_MIN, SYS_PROP_MAX, SYS_PROP_COUNT)
2288 )THEN
2289
2290 v_tExplNodeId(i) := v_tParentId(v_IndexByNodeId(v_tExplNodeId(i)));
2291
2292 ELSIF(glPsNodeType(auxIndex) = PS_NODE_TYPE_REFERENCE)THEN
2293
2294 --If we are here, than this is a reference to a BOM model, because it should be prohibited
2295 --for a reference to a component to participate with anything other than it's MIN or MAX.
2296 --We will fix the corresponding PS_NODE_ID value to be not the reference node's PS_NODE_ID
2297 --but the PS_NODE_ID of the referenced BOM model.This is necessary to generate the correct
2298 --object name.
2299
2300 v_tExprPsNodeId(i) := glReferenceId(v_tExprPsNodeId(i));
2301
2302 END IF;
2303 END IF;
2304
2305 nDebug := 41;
2306
2307 --Select a participant and get its explosion id.
2308
2309 nAux := v_tExplNodeId(i);
2310
2311 IF(v_ProhibitInRules.EXISTS(nAux))THEN
2312
2313 --This explosion node has D nodes under connectors on the way up in the explosion table.
2314 --It will be impossible to assign this rule, so just stop here.
2315
2316 localString := glName(glIndexByPsNodeId(v_tExprPsNodeId(i)));
2317 auxIndex := v_ProhibitInRules(nAux);
2318 auxCount := v_ProhibitConnector(nAux);
2319 RAISE CZ_R_RULE_IGNORED;
2320 END IF;
2321
2322 nDebug := 43;
2323
2324 IF(NOT v_Participant.EXISTS(nAux))THEN
2325
2326 --Add to the list of indexes of distinct participants' explosions.
2327
2328 v_Participant(nAux) := 1;
2329 distinctCount := distinctCount + 1;
2330 v_ParticipantIndex(distinctCount) := v_IndexByNodeId(nAux);
2331
2332 --Make a rule-specific copy of this explosion downpath which may have to be prepended
2333 --after the rule is assigned. It is this copy that will be used for name generation.
2334
2335 v_AssignedDownPath(nAux) := v_NodeDownPath(nAux);
2336 END IF;
2337
2338 --The node is not prohibited from participating in rules, so assignable exists.
2339
2340 auxIndex := v_NodeAssignable(nAux);
2341
2342 --Select and store all the distinct assignables for all the current rule's participants.
2343 --Main indexes are stored in v_DistinctIndex. Also find the deepest D node among all of
2344 --participants here. MaxDepthValue is initialized to the root (0), so that if there are
2345 --no D nodes, the root node will act as one.
2346
2347 nDebug := 44;
2348
2349 IF(NOT v_Assignable.EXISTS(auxIndex))THEN
2350
2351 v_Assignable(auxIndex) := 1;
2352 nCounter := nCounter + 1;
2353 v_DistinctIndex(nCounter) := auxIndex;
2354
2355 IF(v_tExplNodeType(auxIndex) = EXPL_NODE_TYPE_INSTANTIABLE AND
2356 v_tNodeDepth(auxIndex) > MaxDepthValue)THEN
2357
2358 MaxDepthValue := v_tNodeDepth(auxIndex);
2359 MaxDepthIndex := auxIndex;
2360 END IF;
2361 END IF;
2362 END IF;
2363 END LOOP;
2364
2365 nDebug := 45;
2366
2367 --Now populate the <index in memory>(NODE_DEPTH) table for assignables of any type which
2368 --are above the deepest D component. They should form a chain, without duplicates on the
2369 --same level. Here we verify that there are no two components on the same level. This is
2370 --necessary but not sufficient for the rule to be valid.
2371
2372 FOR i IN 1..v_DistinctIndex.COUNT LOOP
2373
2374 auxIndex := v_DistinctIndex(i);
2375
2376 IF(v_tNodeDepth(auxIndex) <= MaxDepthValue)THEN
2377 IF(v_InstByLevel.EXISTS(v_tNodeDepth(auxIndex)))THEN
2378
2379 --There is already a node on this level. Two or more non-virtual components on the
2380 --same level are prohibited.
2381
2382 auxCount := glIndexByPsNodeId(v_tPsNodeId(auxIndex));
2383 auxIndex := glIndexByPsNodeId(v_tPsNodeId(v_InstByLevel(v_tNodeDepth(auxIndex))));
2384 RAISE CZ_R_RULE_IGNORED;
2385 ELSE
2386
2387 --This level is now occupied by a node with memory index auxIndex.
2388
2389 v_InstByLevel(v_tNodeDepth(auxIndex)) := auxIndex;
2390 END IF;
2391 END IF;
2392 END LOOP;
2393
2394 nDebug := 46;
2395
2396 --Now we make sure that if we move up from the deepest D assignable, we will step over
2397 --all other assignable which are above this D, so they all form a chain.
2398 --We start with the deepest D component and move up to its parent and so on thus going
2399 --through every level in the hierarchy. On every level, if an assignable exists there,
2400 --we make sure that this node is what we expect - the parent we just moved up to.
2401
2402 nCounter := 0;
2403 auxIndex := MaxDepthIndex;
2404
2405 LOOP
2406 IF(v_InstByLevel.EXISTS(v_tNodeDepth(auxIndex)))THEN
2407 IF(v_InstByLevel(v_tNodeDepth(auxIndex)) <> auxIndex)THEN
2408
2409 --Incorrect node on the level. The rule goes across non-virual boundaries.
2410
2411 auxCount := glIndexByPsNodeId(v_tPsNodeId(v_InstByLevel(v_tNodeDepth(auxIndex))));
2412 auxIndex := glIndexByPsNodeId(v_tPsNodeId(MaxDepthIndex));
2413 RAISE CZ_R_RULE_IGNORED;
2414 END IF;
2415 nCounter := nCounter + 1;
2416 END IF;
2417
2418 EXIT WHEN nCounter = v_InstByLevel.COUNT OR v_tParentId(auxIndex) IS NULL;
2419 auxIndex := v_IndexByNodeId(v_tParentId(auxIndex));
2420 END LOOP;
2421
2422 nDebug := 47;
2423
2424 --We verified that on the way up from the deepest D node we pass ONLY through eligible
2425 --assignables. Now lets see if we passed through ALL of them.
2426
2427 IF(nCounter <> v_InstByLevel.COUNT)THEN
2428
2429 --Not all the assignables have been passed on the way up. The rule goes across
2430 --non-virual boundaries.
2431
2432 RAISE CZ_R_RULE_IGNORED;
2433 END IF;
2434
2435 --So, there exists the deepest type D assignable (it can be the root node) and we verified
2436 --that above it there is no non-virtual boundaries crossing. However, if there are A type
2437 --assignables beneath that D node, we want to assign the rule to the shallowest of them.
2438 --Or there may be not assignables but just regular A nodes between assignables and D.
2439
2440 --First of all, let us see if there are connector's nets attached to the deepest component
2441 --among the rule participants, because if there are, then the rule will be assigned to the
2442 --deepst D already found and there's no need to work with A type components. Example:
2443
2444 -- M
2445 -- |_D
2446 -- |_A0
2447 -- | |_A
2448 -- | | |_F2
2449 -- | |_A
2450 -- | |_F3
2451 -- |
2452 -- |_Connector->M1-F4
2453
2454 --For both rules relating either (F2, F3) or (F2, F3, F4), D is the deepest D node. However,
2455 --the first rule should be assigned to A0 while the second rule should be assigned to D.
2456 --Reference bug #2188507.
2457
2458 --We also identify possible connector's nets attached to a node above the deepest D node.
2459 --Such explosions will have assignables above the deepest D and so may have passed all the
2460 --tests above, but a rule may still cross non-virual boundaries. Example:
2461
2462 -- M
2463 -- |_D
2464 -- | |_A
2465 -- | |_F2
2466 -- |
2467 -- |___Connector->M1-F4
2468
2469 --F4 has M as its assignable, and although M and D form a good chain, an (F2, F4) rule is
2470 --prohibited.
2471 --Reference bug #2190399.
2472
2473 auxCount := 0;
2474
2475 FOR i IN 1..v_ParticipantIndex.COUNT LOOP
2476
2477 nAux := v_NodeId(v_ParticipantIndex(i));
2478
2479 IF(v_IsConnectorNet.EXISTS(nAux))THEN
2480 IF(v_tNodeDepth(v_NodeAssignable(nAux)) < MaxDepthValue)THEN
2481
2482 --This is a connector's net attached to a node above the deepest D assignable, report
2483 --the rule.
2484
2485 auxCount := glIndexByPsNodeId(v_tReferringId(v_IsConnectorNet(nAux)));
2486 auxIndex := glIndexByPsNodeId(v_tPsNodeId(MaxDepthIndex));
2487 RAISE CZ_R_RULE_IGNORED;
2488 ELSIF(v_NodeAssignable(nAux) = MaxDepthIndex)THEN
2489
2490 --This is a connector's net attached to the D, so the rule will be assigned to the D.
2491
2492 auxCount := 1;
2493
2494 --Just one attached net is enough, but we cannot exit here because we need to examine
2495 --all other participants on account of connector's nets attached above the D node
2496 --(the previous IF does that).
2497 END IF;
2498 END IF;
2499 END LOOP;
2500
2501 IF(auxCount = 0)THEN
2502
2503 --We start with identifying all the A type nodes beneath the deepest D node. From every of
2504 --them we go up the hierarchy and make sure that we end up with the deepest D on its level.
2505
2506 nDebug := 48;
2507
2508 auxCount := 0;
2509 nCounter := 0;
2510
2511 FOR i IN 1..v_DistinctIndex.COUNT LOOP
2512
2513 auxIndex := v_DistinctIndex(i);
2514 nAux := v_tNodeDepth(auxIndex);
2515
2516 IF(v_tExplNodeType(auxIndex) = EXPL_NODE_TYPE_OPTIONAL AND nAux > MaxDepthValue)THEN
2517
2518 nCounter := nCounter + 1;
2519
2520 --This is an A type node beneath the deepest D. We want to know how many such nodes
2521 --there are OUT NOCOPY there.
2522
2523 auxCount := auxCount + 1;
2524 v_BeneathNodes(auxCount) := auxIndex;
2525
2526 --We want to know how many pathes from other A type nodes will pass through this
2527 --node, because if it happens to be exactly the number of all other A type nodes,
2528 --then this node is an ancestor of them all. Initialize the counter array here.
2529
2530 IF(NOT v_BeneathCount.EXISTS(auxIndex))THEN
2531
2532 v_BeneathCount(auxIndex) := 1;
2533 ELSE
2534
2535 v_BeneathCount(auxIndex) := v_BeneathCount(auxIndex) + 1;
2536 END IF;
2537
2538 --Lets now go up from the node and make sure we will come to D on level MaxDepthValue.
2539
2540 FOR n IN REVERSE MaxDepthValue + 1..nAux LOOP
2541
2542 auxIndex := v_IndexByNodeId(v_tParentId(auxIndex));
2543
2544 --If parent is an A type node, increment its descendant count array because this
2545 --path goes through it. Also include it into the list of all the A descendants.
2546
2547 IF(v_tExplNodeType(auxIndex) = EXPL_NODE_TYPE_OPTIONAL)THEN
2548 IF(NOT v_BeneathCount.EXISTS(auxIndex))THEN
2549
2550 auxCount := auxCount + 1;
2551 v_BeneathNodes(auxCount) := auxIndex;
2552
2553 v_BeneathCount(auxIndex) := 1;
2554 ELSE
2555
2556 v_BeneathCount(auxIndex) := v_BeneathCount(auxIndex) + 1;
2557 END IF;
2558 END IF;
2559 END LOOP;
2560
2561 IF(auxIndex <> MaxDepthIndex)THEN
2562
2563 --The way up from the A node doesn't pass through the D node on level MaxDepthValue.
2564 --Crossing of non-virtual boundaries detected.
2565
2566 auxCount := glIndexByPsNodeId(v_tPsNodeId(v_DistinctIndex(i)));
2567 auxIndex := glIndexByPsNodeId(v_tPsNodeId(MaxDepthIndex));
2568 RAISE CZ_R_RULE_IGNORED;
2569 END IF;
2570 END IF;
2571 END LOOP;
2572
2573 nDebug := 49;
2574
2575 --Now lets see if there is one A node that is ancestor of all other A nodes, that is we
2576 --passed through this node on the way up from any other A node. If there are several of
2577 --them, we want to select the deepest.
2578
2579 auxIndex := MaxDepthValue;
2580
2581 FOR i IN 1..v_BeneathNodes.COUNT LOOP
2582 IF(v_BeneathCount(v_BeneathNodes(i)) = nCounter)THEN
2583 IF(v_tNodeDepth(v_BeneathNodes(i)) > auxIndex)THEN
2584
2585 --This may be the A node under the deepest D node we want to assign the rule to.
2586
2587 auxIndex := v_tNodeDepth(v_BeneathNodes(i));
2588 MaxDepthIndex := v_BeneathNodes(i);
2589 END IF;
2590 END IF;
2591 END LOOP;
2592 END IF;
2593
2594 nDebug := 50;
2595
2596 --The rule is assigned to this component (identified by model_ref_expl_id). This variable
2597 --is used mostly for identification of rule logic files, but also in rule generation code.
2598
2599 MaxDepthId := v_NodeId(MaxDepthIndex);
2600 MaxDepthValue := v_tNodeDepth(MaxDepthIndex);
2601
2602 --We need to prepend downpaths for all the distinct participating explosions. If the
2603 --assignable of an explosion id is deeper than the rule's assignee, we are going to
2604 --prepend the downpath with all optional (type A) components or mandatory references
2605 --on the way down from the assignee to the assignable. We do not need to change the
2606 --node's logic level.
2607 --We do not prepend downpaths for explosions corresponding to connectors.
2608
2609 FOR i IN 1..v_ParticipantIndex.COUNT LOOP
2610
2611 nAux := v_NodeId(v_ParticipantIndex(i));
2612
2613 IF(NOT v_IsConnectorNet.EXISTS(nAux))THEN
2614
2615 auxIndex := v_NodeAssignable(nAux);
2616
2617 WHILE(v_tNodeDepth(auxIndex) > MaxDepthValue)LOOP
2618 IF(v_tExplNodeType(auxIndex) IN (EXPL_NODE_TYPE_OPTIONAL, EXPL_NODE_TYPE_MANDATORY))THEN
2619
2620 v_AssignedDownPath(nAux) := PATH_DELIMITER || 'N_' ||
2621 TO_CHAR(glPersistentId(NVL(v_tReferringId(auxIndex), v_tPsNodeId(auxIndex)))) ||
2622 v_AssignedDownPath(nAux);
2623 END IF;
2624
2625 auxIndex := v_IndexByNodeId(v_tParentId(auxIndex));
2626 END LOOP;
2627 END IF;
2628 END LOOP;
2629
2630 nDebug := 51;
2631
2632 --Now we can go ahead and collect the load conditions for this rule. Those would be all
2633 --type A (optional) and C (connector) descendants of the rule assignee. To collect them
2634 --we need to go up from each (distinct) rule participant's explosion id (index).
2635 --There may also be no load conditions at all and then this is the 'standard' rule file
2636 --identified by explosion id of the assignee as a load condition (NET_TYPE = 2).
2637
2638 FOR i IN 1..v_ParticipantIndex.COUNT LOOP
2639
2640 auxIndex := v_ParticipantIndex(i);
2641
2642 WHILE(v_tNodeDepth(auxIndex) > MaxDepthValue AND v_tParentId(auxIndex) IS NOT NULL) LOOP
2643
2644 IF(v_tExplNodeType(auxIndex) IN (EXPL_NODE_TYPE_OPTIONAL, EXPL_NODE_TYPE_CONNECTOR))THEN
2645
2646 --It is enough to just mark the index as a load condition.
2647
2648 v_MarkLoadCondition(auxIndex) := 1;
2649 END IF;
2650
2651 auxIndex := v_IndexByNodeId(v_tParentId(auxIndex));
2652 END LOOP;
2653 END LOOP;
2654
2655 IF(v_MarkLoadCondition.COUNT <> 0)THEN
2656
2657 --UPDATE cz_rules SET disabled_flag = '1' WHERE rule_id = nRuleId;
2658 RAISE CZ_R_RULE_REPORTED; --This rule should be reported.
2659
2660 END IF;
2661 END IF; --Not a rule folder or functional companion
2662
2663 --This block handles the exceptions during a rule generation. Every such exception
2664 --will stop generation only for the particular rule if not re-raised here.
2665
2666 EXCEPTION
2667 WHEN CZ_R_RULE_IGNORED THEN
2668 NULL;
2669 WHEN CZ_R_RULE_REPORTED THEN
2670 --'Upgrade of rule ''%RULENAME'' in model ''%MODELNAME'' may cause a change in behavior of the model
2671 -- at run-time. The rule has been disabled.'
2672 REPORT(CZ_UTILS.GET_TEXT('CZ_R_RULE_REPORTED', 'RULENAME', RULE_NAME, 'MODELNAME', inProjectId || '->' || glName(glIndexByPsNodeId(inProjectId))), 1);
2673 WHEN OTHERS THEN
2674 NULL;
2675 END;
2676 END LOOP;
2677 CLOSE c_rules;
2678 END; --GENERATE_RULES
2679 ---------------------------------------------------------------------------------------
2680 BEGIN --GENERATE_COMPONENT_TREE - Product Structure Generation
2681
2682 nDebug := 1110000;
2683
2684 IF(NOT IsLogicGenerated.EXISTS(inComponentId))THEN
2685
2686 --Read the explosions table here. It will be extensively used in rule generation.
2687
2688 IF(inComponentId = inProjectId)THEN
2689
2690 --If this is the root model, read the table and populate project's id and explosion id
2691 --variables, and hash tables.
2692
2693 SELECT model_ref_expl_id, parent_expl_node_id, node_depth,
2694 ps_node_type, virtual_flag, component_id, referring_node_id,
2695 child_model_expl_id, expl_node_type
2696 BULK COLLECT INTO v_NodeId, v_tParentId, v_tNodeDepth,
2697 v_tNodeType, v_tVirtualFlag, v_tPsNodeId, v_tReferringId,
2698 v_tChildModelExpl, v_tExplNodeType
2699 FROM cz_model_ref_expls
2700 WHERE model_id = inComponentId and deleted_flag = FLAG_NOT_DELETED;
2701
2702 FOR i IN 1..v_NodeId.COUNT LOOP
2703
2704 nDebug := 1110001;
2705
2706 --Add another indexing option - by model_ref_expl_id
2707
2708 v_IndexByNodeId(v_NodeId(i)) := i;
2709
2710 --Store the explosion id and the index of the root node - the project node itself.
2711
2712 IF(v_tNodeDepth(i) = 0)THEN
2713 thisComponentExplId := v_NodeId(i);
2714 thisRootExplIndex := i;
2715 END IF;
2716
2717 --Create the EXPL_NODE_TYPE(MODEL_REF_EXPL_ID) hash table. Other explosion columns
2718 --are currently indexed through v_IndexByNodeId. Using direct hash may provide for
2719 --some performance improvement.
2720
2721 v_TypeByExplId(v_NodeId(i)) := v_tExplNodeType(i);
2722
2723 --Build the MODEL_REF_EXPL_ID(COMPONENT_ID) hash table for all the components
2724 --inside this project (not inside referenced projects). All such components
2725 --have CHILD_MODEL_EXPL_ID null. We need this table to populate MODEL_REF_EXPL_ID
2726 --in CZ_LCE_HEADERS records for structure file of this component.
2727
2728 IF(v_tChildModelExpl(i) IS NULL)THEN
2729 v_NodeIdByComponent(v_tPsNodeId(i)) := v_NodeId(i);
2730 END IF;
2731 END LOOP;
2732
2733 BEGIN
2734
2735 --Get the project name for reporting purposes
2736
2737 SELECT name INTO errorMessage
2738 FROM cz_devl_projects
2739 WHERE devl_project_id = inProjectId;
2740
2741 EXCEPTION
2742 WHEN OTHERS THEN
2743 errorMessage := NULL;
2744 END;
2745
2746 ELSE
2747
2748 nDebug := 1110002;
2749
2750 --This is a non-virtual component inside this project, so the value in the
2751 --hash table exists.
2752 --We do not have to populate thisRootExplIndex, because it is used only in
2753 --rule generation and this will not be called for a non-root component.
2754 --Have to populate the other two variables though as they are used here in
2755 --the structure generation.
2756
2757 thisComponentExplId := v_NodeIdByComponent(inComponentId);
2758 END IF;
2759
2760 thisProjectId := inProjectId;
2761
2762 END IF;
2763
2764 nDebug := 1110004;
2765
2766 --This select statement reads the whole 'virtual' tree under a non-virtual component
2767 --which doesn't include the chief non-virtual component itself, although it includes
2768 --non-virtual components underneath in order to recurse,this function will be called
2769 --for every non-virtual component found underneath.
2770 --The resulting order provided by this statement will be used later when generating
2771 --list of options for an option feature.
2772
2773 SELECT ps_node_id, parent_id, item_id, minimum, maximum, name, intl_text_id,
2774 minimum_selected, maximum_selected, ps_node_type, initial_value, initial_num_value, -- sselahi
2775 virtual_flag, feature_type, bom_required_flag, reference_id, persistent_node_id,
2776 effective_from, effective_until, effective_usage_mask, effectivity_set_id, decimal_qty_flag
2777 BULK COLLECT INTO ntPsNodeId, ntParentId, ntItemId, ntMinimum, ntMaximum, ntName, ntDescriptionId,
2778 ntMinimumSel, ntMaximumSel, ntPsNodeType, ntInitialValue, ntInitNumVal, -- sselahi
2779 ntVirtualFlag, ntFeatureType, ntBomRequired, ntReferenceId, ntPersistentId,
2780 dtEffFrom, dtEffUntil, vtUsageMask, ntEffSetId, ntDecimalQty
2781 FROM cz_ps_nodes
2782 WHERE deleted_flag = FLAG_NOT_DELETED
2783 START WITH ps_node_id = inComponentId
2784 CONNECT BY
2785 (PRIOR virtual_flag IS NULL OR PRIOR virtual_flag = FLAG_VIRTUAL OR
2786 PRIOR ps_node_id = inComponentId)
2787 AND PRIOR ps_node_id = parent_id;
2788
2789 nDebug := 1110005;
2790
2791 --Make sure there is some data returned
2792
2793 IF(ntPsNodeId.LAST IS NOT NULL)THEN
2794
2795 nDebug := 1110006;
2796
2797 --Check if the logic already exists (or has been considered up-to-date).
2798
2799 IF(isLogicGenerated.EXISTS(inComponentId))THEN
2800
2801 --If the value is 0, it has been pre-populated in GENERATE_LOGIC_ procedure because
2802 --logic for this model was considered up-to-date. Report the message and change the
2803 --value to 1 which is the constant used within this procedure (bug #1941626).
2804
2805 IF(isLogicGenerated(inComponentId) = 0)THEN
2806
2807 --We want no message about skipping a model displayed, bug #2055845
2808
2809 isLogicGenerated(inComponentId) := 1;
2810 END IF;
2811
2812 nDebug := 1110007;
2813
2814 --Logic exists, just follow the references and connectors.
2815
2816 FOR i IN ntPsNodeId.FIRST..ntPsNodeId.LAST LOOP
2817 IF(ntPsNodeType(i) IN (PS_NODE_TYPE_REFERENCE, PS_NODE_TYPE_CONNECTOR))THEN
2818
2819 --Check for circularity.
2820
2821 localCount := 0;
2822
2823 FOR n IN 1..globalLevel LOOP
2824 IF(globalStack(n) = ntReferenceId(i))THEN
2825
2826 --Circularity detected.
2827
2828 localCount := 1;
2829 EXIT;
2830 END IF;
2831 END LOOP;
2832
2833 IF(localCount = 0)THEN
2834
2835 globalLevel := globalLevel + 1;
2836 globalStack(globalLevel) := ntReferenceId(i);
2837
2838 GENERATE_COMPONENT_TREE(ntReferenceId(i), ntReferenceId(i));
2839 globalLevel := globalLevel - 1;
2840 END IF;
2841 END IF;
2842 END LOOP;
2843
2844 nDebug := 1110008;
2845
2846 END IF;
2847
2848 nDebug := 1110009;
2849
2850 --Having this dummy boundary node eliminates the necessity of potentially time
2851 --consuming boundary checks during the option feature options' list generation
2852
2853 ntParentId(ntPsNodeId.LAST + 1) := NEVER_EXISTS_ID;
2854
2855 --Prepare to start the main cycle
2856
2857 i := ntPsNodeId.FIRST;
2858
2859 WHILE(i <= ntPsNodeId.LAST) LOOP --Start the main structure generating cycle
2860
2861 BEGIN
2862
2863 --Populate the 'global' arrays - required for rules generation
2864
2865 nDebug := 1110010;
2866
2867 IF(NOT glIndexByPsNodeId.EXISTS(ntPsNodeId(i)))THEN
2868
2869 glPsNodeId(globalCount) := ntPsNodeId(i);
2870 glItemId(globalCount) := ntItemId(i);
2871 glPsNodeType(globalCount) := ntPsNodeType(i);
2872 glParentId(globalCount) := ntParentId(i);
2873 glFeatureType(globalCount) := ntFeatureType(i);
2874 glName(globalCount) := ntName(i);
2875 glBomRequired(globalCount) := ntBomRequired(i);
2876 glMinimum(globalCount) := ntMinimum(i);
2877 glMaximum(globalCount) := ntMaximum(i);
2878 glMinimumSel(globalCount) := ntMinimumSel(i);
2879 glMaximumSel(globalCount) := ntMaximumSel(i);
2880 glVirtualFlag(globalCount) := ntVirtualFlag(i);
2881
2882 --Indexing by ps_node_id, will be used in expressions generation to get back to
2883 --the structure
2884
2885 glIndexByPsNodeId(ntPsNodeId(i)) := globalCount;
2886
2887 --These global arrays will be indexed differently because we only need to get
2888 --persistent_node_id or reference_id by ps_node_id. Probably, good indexing
2889 --option for some of the other global arrays, too.
2890
2891 glPersistentId(ntPsNodeId(i)) := ntPersistentId(i);
2892 glReferenceId(ntPsNodeId(i)) := ntReferenceId(i);
2893 glDecimalQty(ntPsNodeId(i)) := ntDecimalQty(i);
2894
2895 globalCount := globalCount + 1;
2896 END IF;
2897
2898 nDebug := 1110011;
2899
2900 IF(isLogicGenerated.EXISTS(inComponentId))THEN
2901
2902 --We need to call the procedure for any non-virtual component (bug #2065239).
2903
2904 IF(ntVirtualFlag(i) = FLAG_NON_VIRTUAL AND
2905 ntPsNodeType(i) IN (PS_NODE_TYPE_COMPONENT, PS_NODE_TYPE_PRODUCT) AND
2906 ntPsNodeId(i) <> inComponentId)THEN
2907
2908 --We emulate the component as a model with generated logic because we do not want to
2909 --generate anything but still want to follow everything underneath this component.
2910
2911 IsLogicGenerated(ntPsNodeId(i)) := 1;
2912
2913 --We can pass logic header as NULL because it will never be actually used.
2914
2915 GENERATE_COMPONENT_TREE(ntPsNodeId(i), inProjectId);
2916 END IF;
2917
2918 --If no logic already exists, generate the structure.
2919
2920 ELSE
2921
2922 IF(ntPsNodeType(i) IN (PS_NODE_TYPE_COMPONENT, PS_NODE_TYPE_PRODUCT) AND
2923 ntVirtualFlag(i) = FLAG_NON_VIRTUAL)THEN
2924
2925 nDebug := 1110030;
2926
2927 --We don't want to go into an infinite cycle - don't call the procedure for the current
2928 --root component
2929
2930 IF(ntPsNodeId(i) <> inComponentId)THEN
2931
2932 GENERATE_COMPONENT_TREE(ntPsNodeId(i), inProjectId);
2933 END IF;
2934
2935 ELSIF(ntPsNodeType(i) IN (PS_NODE_TYPE_REFERENCE, PS_NODE_TYPE_CONNECTOR))THEN
2936
2937 nDebug := 1110031;
2938
2939 localCount := 0;
2940
2941 FOR n IN 1..globalLevel LOOP
2942 IF(globalStack(n) = ntReferenceId(i))THEN
2943
2944 --Circularity detected.
2945
2946 localCount := 1;
2947 EXIT;
2948 END IF;
2949 END LOOP;
2950
2951 IF(localCount = 0)THEN
2952
2953 globalLevel := globalLevel + 1;
2954 globalStack(globalLevel) := ntReferenceId(i);
2955
2956 GENERATE_COMPONENT_TREE(ntReferenceId(i), ntReferenceId(i));
2957 globalLevel := globalLevel - 1;
2958 END IF;
2959 END IF;
2960 END IF; --End of the IF block of 'if logic does not already exist' inside the main loop
2961 END;
2962
2963 --Increase the main cycle counter
2964
2965 i := i + 1;
2966
2967 END LOOP; --End of the main structure generation cycle
2968
2969 nDebug := 1110038;
2970
2971 ELSE --IF 'there is some data returned'
2972
2973 --The project is empty, stop here
2974 RAISE CZ_S_MODEL_IGNORED;
2975 END IF; --Ends the ELSE block of IF 'there is some data returned'
2976
2977 nDebug := 1110039;
2978
2979 --If a model, generate rules and set the logic generated flag.
2980
2981 IF(inComponentId = inProjectId)THEN
2982
2983 --Generate model's rules and expressions if necessary
2984
2985 IF(NOT IsLogicGenerated.EXISTS(inComponentId))THEN
2986 GENERATE_RULES;
2987 IsLogicGenerated(inComponentId) := 1;
2988 END IF;
2989 END IF;
2990 END; --GENERATE_COMPONENT_TREE
2991 ---------------------------------------------------------------------------------------
2992 BEGIN --VERIFY_RULES
2993
2994 --Get the logic generation run id. If a valid value has been passed as a parameter, use it,
2995 --else generate a new value.
2996
2997 IF(thisRunId IS NULL OR thisRunId = 0)THEN
2998 SELECT cz_xfr_run_infos_s.NEXTVAL INTO thisRunId FROM DUAL;
2999 END IF;
3000
3001 globalLevel := globalLevel + 1;
3002 globalStack(globalLevel) := inDevlProjectId;
3003
3004 --Start off the recursion
3005
3006 GENERATE_COMPONENT_TREE(inDevlProjectId, inDevlProjectId);
3007
3008 EXCEPTION
3009 WHEN CZ_G_UNABLE_TO_REPORT_ERROR THEN
3010 REPORT(SQLERRM, 0);
3011 WHEN OTHERS THEN
3012 NULL;
3013 END;
3014 ---------------------------------------------------------------------------------------
3015 PROCEDURE CZNATIVEBOMSORT(p_sort_width IN INTEGER,
3016 p_batch_size IN INTEGER) IS
3017
3018 v_config_item_id INTEGER;
3019 startRec PLS_INTEGER;
3020 endRec PLS_INTEGER;
3021 BatchSize INTEGER:=p_batch_size;
3022 var_bom_sort cz_config_items.bom_sort_order%TYPE;
3023 xERROR BOOLEAN:=FALSE;
3024
3025 TYPE tConfigItemId IS TABLE OF cz_config_items.config_item_id%TYPE INDEX BY BINARY_INTEGER;
3026 TYPE tConfigHdrId IS TABLE OF cz_config_items.config_hdr_id%TYPE INDEX BY BINARY_INTEGER;
3027 TYPE tConfigRevNbr IS TABLE OF cz_config_items.config_rev_nbr%TYPE INDEX BY BINARY_INTEGER;
3028 TYPE tBomSortOrder IS TABLE OF cz_config_items.bom_sort_order%TYPE INDEX BY BINARY_INTEGER;
3029
3030 tabConfigItemId tConfigItemId;
3031 tabConfigHdrId tConfigHdrId;
3032 tabConfigRevNbr tConfigRevNbr;
3033 tabBomSortOrder tBomSortOrder;
3034
3035 globalIndex PLS_INTEGER := 0;
3036
3037 FUNCTION getNum(p_number IN INTEGER) RETURN VARCHAR2 IS
3038 ret VARCHAR2(100);
3039 BEGIN
3040 SELECT LPAD(TO_CHAR(p_number),p_sort_width,'0') INTO ret FROM dual;
3041 RETURN ret;
3042 END getNum;
3043
3044 PROCEDURE populate(p_config_item_id IN INTEGER,
3045 p_config_hdr_id IN INTEGER,
3046 p_config_rev_nbr IN INTEGER,
3047 p_string1 IN VARCHAR2)
3048 IS
3049
3050 var_string1 CZ_PS_NODES.bom_sort_order%TYPE;
3051 sequenceNbr PLS_INTEGER := 1;
3052
3053 BEGIN
3054 FOR i IN (SELECT config_item_id
3055 FROM cz_config_items
3056 WHERE parent_config_item_id = p_config_item_id
3057 AND config_hdr_id = p_config_hdr_id
3058 AND config_rev_nbr = p_config_rev_nbr
3059 AND (ps_node_id IS NULL OR ps_node_id < 0))
3060 LOOP
3061 var_string1 := p_string1 || getNum(sequenceNbr);
3062 sequenceNbr := sequenceNbr + 1;
3063
3064 globalIndex := globalIndex + 1;
3065 tabConfigItemId(globalIndex) := i.config_item_id;
3066 tabConfigHdrId(globalIndex) := p_config_hdr_id;
3067 tabConfigRevNbr(globalIndex) := p_config_rev_nbr;
3068 tabBomSortOrder(globalIndex) := var_string1;
3069
3070 populate(i.config_item_id, p_config_hdr_id, p_config_rev_nbr, var_string1);
3071 END LOOP;
3072 END populate;
3073
3074 BEGIN
3075 var_bom_sort:=getNum(1);
3076
3077 FOR c_native IN (SELECT config_hdr_id,
3078 config_rev_nbr,
3079 config_item_id
3080 FROM cz_config_details_v c
3081 WHERE (ps_node_id IS NULL OR ps_node_id < 0)
3082 AND bom_sort_order IS NULL
3083 AND parent_config_item_id NOT IN
3084 (SELECT config_item_id FROM cz_config_details_v
3085 WHERE config_hdr_id = c.config_hdr_id
3086 AND config_rev_nbr = c.config_rev_nbr))LOOP
3087
3088 globalIndex := globalIndex + 1;
3089 tabConfigItemId(globalIndex) := c_native.config_item_id;
3090 tabConfigHdrId(globalIndex) := c_native.config_hdr_id;
3091 tabConfigRevNbr(globalIndex) := c_native.config_rev_nbr;
3092 tabBomSortOrder(globalIndex) := var_bom_sort;
3093
3094 populate(c_native.config_item_id, c_native.config_hdr_id, c_native.config_rev_nbr, var_bom_sort);
3095 END LOOP;
3096
3097 startRec := 1;
3098
3099 WHILE(startRec <= globalIndex)LOOP
3100
3101 endRec := startRec + BatchSize;
3102 IF(endRec > globalIndex)THEN endRec := globalIndex; END IF;
3103
3104 FORALL i IN startRec..endRec
3105 UPDATE cz_config_items
3106 SET bom_sort_order = tabBomSortOrder(i)
3107 WHERE config_item_id = tabConfigItemId(i)
3108 AND config_hdr_id = tabConfigHdrId(i)
3109 AND config_rev_nbr = tabConfigRevNbr(i)
3110 AND bom_sort_order is NULL
3111 AND deleted_flag = '0';
3112
3113 startRec := endRec + 1;
3114 COMMIT;
3115 END LOOP;
3116
3117 EXCEPTION
3118 WHEN NO_DATA_FOUND THEN
3119 NULL;
3120 WHEN OTHERS THEN
3121 RAISE;
3122 END CZNATIVEBOMSORT;
3123
3124 ----------------------------------------------
3125 -------this procedure is a fix for bug# 3854632
3126 -------this procedure republished the following models
3127 -------Envoy Custom Laptop(204 143), Sentinal Custom Desktop(204 137)
3128 -------Server System(204 3791)
3129 PROCEDURE publish_vision_models
3130 IS
3131
3132 TYPE t_varchar_tbl IS TABLE OF VARCHAR2(255) index by binary_integer;
3133 TYPE t_number_tbl IS TABLE OF NUMBER index by binary_integer;
3134
3135 t_model_names_tbl t_varchar_tbl;
3136 t_models_tbl t_number_tbl;
3137 t_model_ids_tbl t_number_tbl;
3138 t_publ_ids_tbl t_number_tbl;
3139 t_publ_ids_ref t_number_tbl;
3140 t_uis_ref t_number_tbl;
3141 rec_count NUMBER := 0;
3142 l_runId NUMBER := 0;
3143 l_status VARCHAR2(3);
3144 l_message VARCHAr2(2000);
3145 begin
3146 ------initialize vision model names
3147 t_model_names_tbl.DELETE;
3148 t_model_names_tbl(1) := 'Envoy Custom Laptop(204 143)';
3149 t_model_names_tbl(2) := 'Sentinal Custom Desktop(204 137)';
3150 t_model_names_tbl(3) := 'Server System(204 3791)';
3151
3152 -----get model id(s) from cz_rp_entries
3153 IF (t_model_names_tbl.COUNT > 0) THEN
3154 t_model_ids_tbl.DELETE;
3155 FOR I IN t_model_names_tbl.FIRST..t_model_names_tbl.LAST
3156 LOOP
3157 t_models_tbl.DELETE;
3158 BEGIN
3159 SELECT object_id
3160 BULK
3161 COLLECT
3162 INTO t_models_tbl
3163 FROM cz_rp_entries
3164 WHERE object_type = 'PRJ'
3165 AND deleted_flag = '0'
3166 AND name = t_model_names_tbl(i);
3167 EXCEPTION
3168 WHEN NO_DATA_FOUND THEN
3169 NULL;
3170 END;
3171 IF (t_models_tbl.COUNT > 0) THEN
3172 rec_count := t_model_ids_tbl.COUNT;
3173 FOR J IN t_models_tbl.FIRST..t_models_tbl.LAST
3174 LOOP
3175 rec_count := rec_count + 1;
3176 t_model_ids_tbl(rec_count) := t_models_tbl(j);
3177 END LOOP;
3178 END IF;
3179 END LOOP;
3180 END IF;
3181
3182 -----get publication id(s) for the above model id(s),
3183 -----generate logic for each model (top level only)
3184 ---- refresh UI(s) including the child UI(s)
3185
3186 IF (t_model_ids_tbl.COUNT > 0) THEN
3187 t_publ_ids_ref.DELETE;
3188 FOR I IN t_model_ids_tbl.FIRST..t_model_ids_tbl.LAST
3189 LOOP
3190 t_publ_ids_tbl.DELETE;
3191 SELECT publication_id
3192 BULK
3193 COLLECT
3194 INTO t_publ_ids_tbl
3195 FROM cz_model_publications
3196 WHERE deleted_flag = '0'
3197 AND source_target_flag = 'S'
3198 AND export_status = 'OK'
3199 AND trunc(creation_date) < TO_DATE('12/31/2002', 'mm/dd/yyyy')
3200 AND object_id = t_model_ids_tbl(i);
3201
3202 IF (t_publ_ids_tbl.COUNT > 0) THEN
3203 rec_count := t_publ_ids_ref.COUNT;
3204 FOR J IN t_publ_ids_tbl.FIRST..t_publ_ids_tbl.LAST
3205 LOOP
3206 rec_count := rec_count + 1;
3207 t_publ_ids_ref(rec_count) := t_publ_ids_tbl(j);
3208 END LOOP;
3209
3210 ------generate logic
3211 cz_logic_gen.generate_logic(t_model_ids_tbl(i), l_runId);
3212 COMMIT;
3213
3214 ------refresh UI(S)
3215 t_uis_ref.DELETE;
3216 BEGIN
3217 SELECT ui_def_id
3218 BULK
3219 COLLECT
3220 INTO t_uis_ref
3221 FROM cz_ui_defs
3222 WHERE cz_ui_defs.devl_project_id IN (SELECT COMPONENT_ID
3223 FROM CZ_MODEL_REF_EXPLS
3224 WHERE model_id = t_model_ids_tbl(i)
3225 AND deleted_flag = '0')
3226 AND cz_ui_defs.deleted_flag = '0'
3227 AND cz_ui_defs.ui_style = '0';
3228 EXCEPTION
3229 WHEN NO_DATA_FOUND THEN
3230 NULL;
3231 END;
3232
3233 IF (t_uis_ref.COUNT > 0) THEN
3234 FOR J IN t_uis_ref.FIRST..t_uis_ref.LAST
3235 LOOP
3236 cz_ui_generator.refresh_UI(t_uis_ref(j));
3237 COMMIT;
3238 END LOOP;
3239 END IF;
3240 END IF;
3241 END LOOP;
3242 END IF;
3243
3244 -------for the above publication id(s) reset the publications
3245 IF (t_publ_ids_ref.COUNT > 0) THEN
3246 FOR I IN t_publ_ids_ref.FIRST..t_publ_ids_ref.LAST
3247 LOOP
3248 update cz_model_publications set remote_publication_id = NULL where publication_id = t_publ_ids_ref(i);
3249 update cz_model_publications set export_status = 'PEN' where publication_id = t_publ_ids_ref(i);
3250 update cz_model_publications set creation_date = sysdate where publication_id = t_publ_ids_ref(i);
3251 delete from cz_pb_model_exports where publication_id = t_publ_ids_ref(i);
3252 delete from cZ_model_publications where remote_publication_id = t_publ_ids_ref(i);
3253 END LOOP;
3254 COMMIT;
3255
3256 ----publish models
3257 FOR J IN t_publ_ids_ref.FIRST..t_publ_ids_ref.LAST
3258 LOOP
3259 cz_pb_mgr.publish_model(t_publ_ids_ref(j),l_runId,l_status);
3260 END LOOP;
3261 COMMIT;
3262 END IF;
3263 EXCEPTION
3264 WHEN OTHERS THEN
3265 l_message := SQLERRM;
3266 insert into cz_db_logs (LOGTIME,message,caller)
3267 values (sysdate,l_message,'PBVISIONMODELS');
3268 commit;
3269 END;
3270 --------------------------------------------------------------------------------------
3271 END CZ_UPGRADE;
[Home] [Help]