1 PACKAGE BODY CZ_REFS AS
2 /* $Header: czrefb.pls 120.9 2010/08/14 07:51:04 kksriram ship $*/
3
4 PRODUCT_TYPE CONSTANT INTEGER:=258;
5 COMPONENT_TYPE CONSTANT INTEGER:=259;
6 FEATURE_TYPE CONSTANT INTEGER:=261;
7 OPTION_TYPE CONSTANT INTEGER:=262;
8 REFERENCE_TYPE CONSTANT INTEGER:=263;
9 CONNECTOR_TYPE CONSTANT INTEGER:=264;
10 BOM_MODEL_TYPE CONSTANT INTEGER:=436;
11
12 CONTRIBUTE_TO_MIN_SUBTYPE CONSTANT INTEGER:=4;
13 CONTRIBUTE_TO_MAX_SUBTYPE CONSTANT INTEGER:=5;
14
15 CONTRIBUTE_TO_MIN CONSTANT INTEGER:=1;
16 CONTRIBUTE_TO_MAX CONSTANT INTEGER:=2;
17 CONTRIBUTE_TO_MINMAX CONSTANT INTEGER:=3;
18
19 MINUS_MODE CONSTANT VARCHAR2(1):='-';
20 PLUS_MODE CONSTANT VARCHAR2(1):='+';
21
22 TYPE IdStructure IS RECORD(new_id INTEGER,
23 parent_id INTEGER,
24 ps_node_id INTEGER,
25 ps_node_type INTEGER,
26 component_id INTEGER,
27 child_model_id INTEGER,
28 current_level INTEGER,
29 expl_node_type INTEGER,
30 virtual_flag VARCHAR2(1),
31 expl_path VARCHAR2(32000));
32
33 TYPE modelArrStructure IS RECORD(model_id INTEGER,
34 parent_model_id INTEGER,
35 connector_loop BOOLEAN:=FALSE);
36
37 TYPE ArrayId IS TABLE OF IdStructure INDEX BY BINARY_INTEGER;
38 TYPE IntArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
39 TYPE IntArray_idx_vc2 IS TABLE OF NUMBER INDEX BY VARCHAR2(15); --Bug9979750
40 TYPE modelArray IS TABLE OF modelArrStructure INDEX BY BINARY_INTEGER;
41 TYPE Int2Array IS TABLE OF IntArray INDEX BY BINARY_INTEGER;
42 TYPE Varchar2Array IS TABLE OF VARCHAR2(32000) INDEX BY BINARY_INTEGER;
43
44 TYPE Int2RecArray IS TABLE OF ArrayId INDEX BY BINARY_INTEGER;
45
46 g_hash_tree_tbl Int2RecArray;
47 g_root_point_tree_tbl IntArray;
48 t_models modelArray;
49 t_chain modelArray;
50 t_projectCache IntArray;
51
52 --9446997 fix
53 t_upd_node_depth_models IntArray_idx_vc2;
54
55 t_old_expl_ids IntArray;
56 t_new_expl_ids IntArray;
57
58 m_RUN_ID NUMBER;
59
60 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
61 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
62
63 PROCEDURE Initialize IS
64 BEGIN
65 FND_MSG_PUB.initialize;
66 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO m_RUN_ID FROM dual;
67 END Initialize;
68
69 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
70 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
71
72 PROCEDURE LOG_REPORT
73 (p_caller IN VARCHAR2,
74 p_str IN VARCHAR2) IS
75
76 l_return BOOLEAN;
77
78 BEGIN
79 l_return := cz_utils.log_report(Msg => p_str,
80 Urgency => 1,
81 ByCaller => p_caller,
82 StatusCode => 11276,
83 RunId => m_RUN_ID);
84 END;
85
86 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
87 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
88
89 PROCEDURE DEBUG(p_str IN VARCHAR2) IS
90 BEGIN
91 NULL;
92 --DBMS_OUTPUT.PUT_LINE(p_str);
93 END;
94
95 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
96 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
97
98 PROCEDURE DEBUG(p_str IN VARCHAR2,v_num IN NUMBER) IS
99 BEGIN
100 DEBUG(p_str||'='||TO_CHAR(v_num));
101 END;
102
103 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
104 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
105
106 PROCEDURE DEBUG(p_name IN VARCHAR2,p_arr IN ArrayId) IS
107 v_ind PLS_INTEGER;
108 v_counter PLS_INTEGER:=0;
109 BEGIN
110 IF p_arr.COUNT=0 THEN
111 RETURN;
112 END IF;
113 v_ind:=p_arr.FIRST;
114 LOOP
115 IF v_ind IS NULL THEN
116 EXIT;
117 END IF;
118 v_counter:=v_counter+1;
119 DEBUG('++++++ '||TO_CHAR(v_counter)||' ++++++');
120 DEBUG(p_name||'('||TO_CHAR(v_ind)||').new_id='||TO_CHAR(p_arr(v_ind).new_id));
121 DEBUG(p_name||'('||TO_CHAR(v_ind)||').parent_id='||TO_CHAR(p_arr(v_ind).parent_id));
122 DEBUG(p_name||'('||TO_CHAR(v_ind)||').component_id='||TO_CHAR(p_arr(v_ind).component_id));
123 v_ind:=p_arr.NEXT(v_ind);
124 DEBUG('++++++++++++++++++++++++++++++++++++++');
125 END LOOP;
126 END DEBUG;
127 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
128 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
129
130 -- p_out_flag=0 - it's not nonVirtual --
131 -- p_out_flag=1 - nonVirtual --
132 /* old version
133 PROCEDURE IsNonVirtual
134 (p_ps_node_id IN INTEGER,
135 p_model_id IN INTEGER,
136 p_out_flag OUT NOCOPY INTEGER) IS
137
138 BEGIN
139 p_out_flag:=0;
140 SELECT 1 INTO p_out_flag FROM CZ_PS_NODES
141 WHERE deleted_flag = NO_FLAG
142 AND ps_node_id=p_ps_node_id AND ps_node_type IN(COMPONENT_TYPE,PRODUCT_TYPE,
143 REFERENCE_TYPE,CONNECTOR_TYPE,BOM_MODEL_TYPE)
144 AND ps_node_id NOT IN
145 (SELECT ps_node_id FROM CZ_PS_NODES nodes
146 WHERE deleted_flag = NO_FLAG
147 AND devl_project_id =p_model_id
148 AND ps_node_type IN(COMPONENT_TYPE,REFERENCE_TYPE,CONNECTOR_TYPE,BOM_MODEL_TYPE)
149 AND MINIMUM = 1
150 AND MAXIMUM = 1
151 AND NOT EXISTS
152 (SELECT 1 FROM cz_expression_nodes x
153 WHERE deleted_flag = NO_FLAG
154 AND consequent_flag =YES_FLAG
155 AND ps_node_id = nodes.ps_node_id
156 AND EXISTS
157 (SELECT NULL FROM cz_rules WHERE (antecedent_id=x.express_id OR consequent_id=x.express_id
158 OR amount_id=x.express_id) AND disabled_flag=NO_FLAG
159 AND devl_project_id=p_model_id AND deleted_flag=NO_FLAG)));
160 EXCEPTION
161 WHEN NO_DATA_FOUND THEN
162 NULL;
163 WHEN OTHERS THEN
164 LOG_REPORT('IsNonVirtual','Error : ps_node_id='||TO_CHAR(p_ps_node_id)||' '||SQLERRM);
165 END;
166 */
167
168 PROCEDURE IsNonVirtual
169 (p_ps_node_id IN INTEGER,
170 p_model_id IN INTEGER,
171 p_out_flag OUT NOCOPY INTEGER) IS
172
173 BEGIN
174 p_out_flag:=0;
175 SELECT 1 INTO p_out_flag FROM CZ_PS_NODES
176 WHERE deleted_flag = NO_FLAG
177 AND ps_node_id=p_ps_node_id AND (instantiable_flag IN (OPTIONAL_EXPL_TYPE,OPTIONAL_EXPL_TYPE) OR
178 (ps_node_type IN(COMPONENT_TYPE,PRODUCT_TYPE,REFERENCE_TYPE,CONNECTOR_TYPE,BOM_MODEL_TYPE) AND NOT(MAXIMUM=1 AND MINIMUM=1)));
179 EXCEPTION
180 WHEN NO_DATA_FOUND THEN
181 NULL;
182 WHEN OTHERS THEN
183 LOG_REPORT('IsNonVirtual','Error : ps_node_id='||TO_CHAR(p_ps_node_id)||' '||SQLERRM);
184 END;
185
186 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
187 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
188
189 FUNCTION allocate_Expl_Id RETURN NUMBER IS
190 v_next_id NUMBER;
191 BEGIN
192 SELECT CZ_MODEL_REF_EXPLS_S.NEXTVAL INTO v_next_id FROM dual;
193 RETURN v_next_id;
194 END allocate_Expl_Id;
195
196 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
197 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
198
199 FUNCTION get_Root_Expl_Id(p_model_id IN NUMBER) RETURN NUMBER IS
200 v_expl_node_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
201 BEGIN
202 SELECT model_ref_expl_id INTO v_expl_node_id FROM CZ_MODEL_REF_EXPLS
203 WHERE component_id=p_model_id AND model_id=p_model_id AND
204 parent_expl_node_id IS NULL AND deleted_flag=NO_FLAG;
205 RETURN v_expl_node_id;
206 END get_Root_Expl_Id;
207
208 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
209 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
210
211 FUNCTION circularity_Exists
212 (
213 p_source_model_id IN NUMBER,
214 p_target_model_id IN NUMBER
215 ) RETURN VARCHAR2 IS
216
217 p_circularity_exists VARCHAR2(1):=NO_FLAG;
218
219 BEGIN
220 SELECT YES_FLAG INTO p_circularity_exists FROM dual WHERE
221 EXISTS
222 (SELECT component_id FROM CZ_MODEL_REF_EXPLS
223 WHERE model_id=p_target_model_id AND deleted_flag=NO_FLAG
224 AND ps_node_type IN (REFERENCE_TYPE,CONNECTOR_TYPE) AND component_id=p_source_model_id) OR
225 (p_target_model_id=p_source_model_id);
226 RETURN p_circularity_exists;
227 EXCEPTION
228 WHEN OTHERS THEN
229 RETURN p_circularity_exists;
230 END;
231
232 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
233 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
234
235 -- Bugfix 9446997
236 -- This procedure was introduced to reset arrays/tables
237 -- which will store distinct models which will be used
238 -- to drive update_node_depth
239 --
240 -- TO DO - Also need to check it can be used for
241 -- update_child_nodes
242 --
243
244 PROCEDURE reset_model_array IS
245 BEGIN
246 t_upd_node_depth_models.DELETE;
247
248 END reset_model_array;
249
250
251 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
252 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
253
254 --
255 -- this procedure is used in temporary fix for the bug #2425995
256 --
257 -- As a part of Bugfix 9446997 array
258 -- t_upd_node_depth_models will be driving population of
259 -- update_node_depth during refresh process. Caller to check_node from where
260 -- this procedure is called should make sure that the reset_model_array
261 -- is called before calling check_node and pass value '1' for
262 -- p_skip_upd_nod_depth param in check_node and call update_node_depth
263 -- explicitly with NULL parameter. (as in cz_imp_ps_node pkg)
264 --
265 PROCEDURE update_Node_Depth(p_model_id IN INTEGER DEFAULT NULL) IS
266
267 t_nodes_to_delete_tbl IntArray;
268 i number;
269 j number;
270
271 PROCEDURE update_Node_Depth_
272 (p_model_id IN INTEGER,
273 p_expl_id IN INTEGER DEFAULT NULL,
274 p_node_depth IN INTEGER DEFAULT NULL,
275 p_ps_node_id IN INTEGER DEFAULT NULL,
276 p_ps_node_type IN INTEGER DEFAULT NULL) IS
277
278 v_node_depth INTEGER;
279 v_model_ref_expl_id INTEGER;
280
281 BEGIN
282 IF p_node_depth IS NULL THEN
283 UPDATE CZ_MODEL_REF_EXPLS
284 SET node_depth=0
285 WHERE model_id=p_model_id AND parent_expl_node_id IS NULL
286 AND deleted_flag=NO_FLAG
287 RETURNING model_ref_expl_id INTO v_model_ref_expl_id;
288 update_Node_Depth_(p_model_id,v_model_ref_expl_id,0);
289 ELSE
290 v_node_depth:=p_node_depth+1;
291 FOR i IN(SELECT model_ref_expl_id,referring_node_id,ps_node_type FROM CZ_MODEL_REF_EXPLS
292 WHERE model_id=p_model_id AND parent_expl_node_id=p_expl_id
293 AND deleted_flag=NO_FLAG)
294 LOOP
295 UPDATE CZ_MODEL_REF_EXPLS
296 SET node_depth=v_node_depth
297 WHERE model_ref_expl_id=i.model_ref_expl_id AND node_depth<>v_node_depth;
298
299 --
300 -- temporary workaround to delete possible reference duplicates
301 -- t_nodes_to_delete_tbl stores duplicate nodes
302 -- that need to be deleled later
303 --
304 IF p_ps_node_id=i.referring_node_id
305 AND p_ps_node_type=i.ps_node_type THEN
306 t_nodes_to_delete_tbl(t_nodes_to_delete_tbl.COUNT+1):=i.model_ref_expl_id;
307 END IF;
308
309 update_Node_Depth_(p_model_id,i.model_ref_expl_id,v_node_depth,i.referring_node_id,i.ps_node_type);
310 END LOOP;
311 END IF;
312 END update_Node_Depth_;
313
314 BEGIN
315 t_nodes_to_delete_tbl.DELETE;
316
317 -- Bugfix 9446997
318 IF p_model_id IS NULL THEN
319 -- Bugfix 9446997
320 -- Drive t_upd_node_depth_models array to update node depth calls
321 -- as this will be called after all check_nodes call in BOM Refresh process
322 i :=t_upd_node_depth_models.FIRST;
323 LOOP
324 EXIT WHEN i IS NULL;
325 update_Node_Depth_(t_upd_node_depth_models(i));
326 i := t_upd_node_depth_models.NEXT (i);
327 END LOOP;
328
329 -- Instead of using p_model_id as driver use t_upd_node_depth_models array as driver to
330 -- delete reference duplicates
331 j :=t_upd_node_depth_models.FIRST;
332 LOOP
333 EXIT WHEN j IS NULL;
334 IF t_nodes_to_delete_tbl.COUNT>0 THEN
335 FORALL i IN t_nodes_to_delete_tbl.FIRST..t_nodes_to_delete_tbl.LAST
336 UPDATE CZ_MODEL_REF_EXPLS
337 SET deleted_flag=YES_FLAG
338 WHERE model_ref_expl_id IN
339 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
340 START WITH model_id=t_upd_node_depth_models(j) AND model_ref_expl_id=t_nodes_to_delete_tbl(i) AND deleted_flag=NO_FLAG
341 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG
342 AND PRIOR deleted_flag=NO_FLAG);
343 END IF;
344 j := t_upd_node_depth_models.NEXT (j);
345 END LOOP;
346
347
348 ELSE
349 FOR i IN(SELECT DISTINCT model_id FROM cz_model_ref_expls
350 WHERE ((component_id=p_model_id AND
351 ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE)) OR model_id=p_model_id)
352 AND deleted_flag=NO_FLAG)
353 LOOP
354 update_Node_Depth_(i.model_id);
355 END LOOP;
356
357 IF t_nodes_to_delete_tbl.COUNT>0 THEN
358 FORALL i IN t_nodes_to_delete_tbl.FIRST..t_nodes_to_delete_tbl.LAST
359 UPDATE CZ_MODEL_REF_EXPLS
360 SET deleted_flag=YES_FLAG
361 WHERE model_ref_expl_id IN
362 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
363 START WITH model_id=p_model_id AND model_ref_expl_id=t_nodes_to_delete_tbl(i) AND deleted_flag=NO_FLAG
364 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG
365 AND PRIOR deleted_flag=NO_FLAG);
366 END IF;
367
368 END IF; -- p_model_id IS NULL
369
370
371 END update_Node_Depth;
372
373 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
374 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
375
376 -- p_out_flag=0 - it's not nonVirtual --
377 -- p_out_flag=1 - nonVirtual --
378
379 FUNCTION existContributeRule
380 (p_ps_node_id IN INTEGER,
381 p_model_id IN INTEGER) RETURN BOOLEAN IS
382
383 v_iret INTEGER:=0;
384 v_bret BOOLEAN:=FALSE;
385 v_instantiable_flag CZ_PS_NODES.instantiable_flag%TYPE;
386
387 BEGIN
388 SELECT instantiable_flag INTO v_instantiable_flag
389 FROM CZ_PS_NODES
390 WHERE ps_node_id=p_ps_node_id;
391 IF v_instantiable_flag IN(OPTIONAL_EXPL_TYPE,MINMAX_EXPL_TYPE) THEN
392 RETURN TRUE;
393 ELSE
394 RETURN FALSE;
395 END IF;
396 /* old code
397 BEGIN
398 SELECT 1 INTO v_iret FROM dual
399 WHERE
403 AND ps_node_id = p_ps_node_id
400 EXISTS(SELECT NULL FROM cz_expression_nodes x
401 WHERE deleted_flag = NO_FLAG
402 AND consequent_flag =YES_FLAG
404 AND EXISTS
405 (SELECT NULL FROM cz_rules WHERE (antecedent_id=x.express_id OR consequent_id=x.express_id
406 OR amount_id=x.express_id) AND disabled_flag=NO_FLAG
407 AND devl_project_id=p_model_id AND deleted_flag=NO_FLAG));
408 EXCEPTION
409 WHEN NO_DATA_FOUND THEN
410 NULL;
411 WHEN OTHERS THEN
412 LOG_REPORT('existContributeRule','Error : ps_node_id='||TO_CHAR(p_ps_node_id)||' '||SQLERRM);
413 END;
414
415 IF v_iret=0 THEN
416 v_bret:=FALSE;
417 ELSE
418 v_bret:=TRUE;
419 END IF;
420
421 RETURN v_bret;
422 */
423 END;
424
425 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
426 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
427
428 FUNCTION getExprSubType(p_expr_node_id IN INTEGER) RETURN NUMBER IS
429
430 v_expr_parent CZ_EXPRESSION_NODES.expr_parent_id%TYPE;
431 v_expr_subtype CZ_EXPRESSION_NODES.expr_subtype%TYPE;
432
433 BEGIN
434 SELECT expr_parent_id INTO v_expr_parent
435 FROM CZ_EXPRESSION_NODES WHERE expr_node_id=p_expr_node_id AND
436 deleted_flag=NO_FLAG;
437
438 SELECT expr_subtype INTO v_expr_subtype
439 FROM CZ_EXPRESSION_NODES WHERE expr_parent_id=v_expr_parent AND ps_node_id IS NULL AND
440 deleted_flag=NO_FLAG;
441
442 RETURN v_expr_subtype;
443
444 EXCEPTION
445 WHEN OTHERS THEN
446 RETURN -1;
447 END;
448
449 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
450 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
451
452 -- p_out_flag=0 - it's not nonVirtual --
453 -- p_out_flag=1 - nonVirtual --
454
455 FUNCTION getExplType
456 (p_ps_node_id IN INTEGER,
457 p_model_id IN INTEGER,
458 p_minimum IN INTEGER,
459 p_maximum IN INTEGER,
460 p_ps_node_type IN INTEGER) RETURN NUMBER IS
461
462 v_expr_parent_id CZ_EXPRESSION_NODES.expr_parent_id%TYPE;
463 v_expr_subtype CZ_EXPRESSION_NODES.expr_subtype%TYPE;
464 v_expl_node_type CZ_MODEL_REF_EXPLS.expl_node_type%TYPE;
465
466 BEGIN
467 FOR i IN (SELECT rule_id,antecedent_id,consequent_id,NAME FROM CZ_RULES
468 WHERE devl_project_id=p_model_id AND disabled_flag=NO_FLAG
469 AND deleted_flag=NO_FLAG)
470 LOOP
471 BEGIN
472 SELECT expr_parent_id INTO v_expr_parent_id FROM CZ_EXPRESSION_NODES
473 WHERE ps_node_id=p_ps_node_id AND deleted_flag=NO_FLAG AND
474 rule_id=i.rule_id;
475
476 SELECT expr_subtype INTO v_expr_subtype
477 FROM CZ_EXPRESSION_NODES WHERE expr_parent_id=v_expr_parent_id AND ps_node_id IS NULL AND
478 deleted_flag=NO_FLAG;
479
480 IF v_expr_subtype=CONTRIBUTE_TO_MIN_SUBTYPE THEN
481 IF (p_minimum=1 AND p_maximum=1) OR (p_minimum=0 AND p_maximum=1) THEN
482 v_expl_node_type:=OPTIONAL_EXPL_TYPE;
483 ELSE
484 RETURN MINMAX_EXPL_TYPE;
485 END IF;
486 END IF;
487
488 IF v_expr_subtype=CONTRIBUTE_TO_MAX_SUBTYPE THEN
489 RETURN MINMAX_EXPL_TYPE;
490 END IF;
491
492 EXCEPTION
493 WHEN OTHERS THEN
494 NULL;
495 END;
496 END LOOP;
497
498 IF p_minimum=1 AND p_maximum=1 AND v_expl_node_type=OPTIONAL_EXPL_TYPE THEN
499 RETURN OPTIONAL_EXPL_TYPE;
500 END IF;
501
502 IF NOT(p_minimum=1 AND p_maximum=1) AND v_expl_node_type=OPTIONAL_EXPL_TYPE THEN
503 IF p_minimum=0 AND p_maximum=1 THEN
504 RETURN OPTIONAL_EXPL_TYPE;
505 ELSE
506 RETURN MINMAX_EXPL_TYPE;
507 END IF;
508 END IF;
509
510 IF NOT(p_minimum=1 AND p_maximum=1) AND NOT(p_minimum=0 AND p_maximum=1) THEN
511 RETURN MINMAX_EXPL_TYPE;
512 END IF;
513
514 IF p_minimum=0 AND p_maximum=1 THEN
515 RETURN OPTIONAL_EXPL_TYPE;
516 END IF;
517
518 IF p_minimum=1 AND p_maximum=1 AND p_ps_node_type=REFERENCE_TYPE THEN
519 RETURN MANDATORY_EXPL_TYPE;
520 END IF;
521
522 IF p_minimum=1 AND p_maximum=1 AND p_ps_node_type=CONNECTOR_TYPE THEN
523 RETURN CONNECTOR_EXPL_TYPE;
524 END IF;
525
526 RETURN 0;
527 END;
528
529
530 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
531 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
532
533 PROCEDURE add_root_Model_record
534 (p_ps_node_id IN INTEGER,
535 p_ps_node_type IN INTEGER) IS
536
537 v_expl_id NUMBER;
538
539 BEGIN
540 v_expl_id:=allocate_Expl_Id;
541
545 referring_node_id,
542 INSERT INTO CZ_MODEL_REF_EXPLS
543 (model_ref_expl_id,
544 parent_expl_node_id,
546 model_id,
547 component_id,
548 ps_node_type,
549 virtual_flag,
550 node_depth,
551 expl_node_type,
552 deleted_flag)
553 VALUES (v_expl_id,
554 NULL,
555 NULL,
556 p_ps_node_id,
557 p_ps_node_id,
558 p_ps_node_type,
559 YES_FLAG,
560 0,
561 MANDATORY_EXPL_TYPE,
562 NO_FLAG);
563
564 END add_root_Model_record;
565
566 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
567 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
568
569 --
570 -- populate chain of connected models starting with model ( p_model_id )
571 -- format of record : model_id/parent_model_id
572 -- Example :
573 -- M3
574 -- |---> M2
575 -- |--->M1
576 -- |--~~~--> M3 ( Circular connector )
577 -- we start with M1 and go up on the tree
578 -- until M3
579 -- so t_chain is { M1/M2, M2/M3, M3/null }
580 -- note that element for M3 must have parent_model_id=null - not M1
581 -- otherwise we will have a VERY BIG PROBLEM because of circularity :
582 -- check_node/move_node/delete_node will go to infinite loop
583 --
584 PROCEDURE populate_chain
585 (p_model_id IN INTEGER) IS
586 TYPE IntArray IS TABLE OF NUMBER INDEX BY VARCHAR2(15);
587 t_models IntArray;
588 v_circularity_exists BOOLEAN:=FALSE;
589
590 PROCEDURE pop(p_id IN NUMBER) IS
591 v_ind NUMBER:=0;
592 BEGIN
593 FOR i IN(SELECT DISTINCT devl_project_id FROM CZ_PS_NODES a
594 WHERE reference_id=p_id AND deleted_flag=NO_FLAG AND
595 devl_project_id IN(SELECT object_id FROM CZ_RP_ENTRIES
596 WHERE object_id=a.devl_project_id AND object_type='PRJ' AND deleted_flag=NO_FLAG))
597 LOOP
598 v_ind:=t_chain.COUNT+1;
599 t_chain(v_ind).model_id:=i.devl_project_id;
600 t_chain(v_ind).parent_model_id:=p_id;
601
602 v_circularity_exists:=t_models.EXISTS(i.devl_project_id);
603 t_models(i.devl_project_id):=p_id;
604
605 IF i.devl_project_id <> p_model_id AND
606 NOT(v_circularity_exists) THEN
607 pop(i.devl_project_id);
608 ELSE
609 --t_chain(v_ind).parent_model_id:=NULL;
610 NULL;
611 END IF;
612 END LOOP;
613 END pop;
614
615 BEGIN
616 pop(p_model_id);
617 END populate_chain;
618
619 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
620 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
621
622 PROCEDURE calc_Expl_Paths(p_root_expl_id IN NUMBER,x_paths_tbl OUT NOCOPY Varchar2Array ) IS
623 BEGIN
624
625 FOR m IN(SELECT model_ref_expl_id,parent_expl_node_id,component_id,referring_node_id
626 FROM CZ_MODEL_REF_EXPLS
627 START WITH model_ref_expl_id=p_root_expl_id
628 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag='0' AND PRIOR deleted_flag='0')
629 LOOP
630
631 IF m.referring_node_id IS NULL THEN
632
633 IF x_paths_tbl.EXISTS(m.parent_expl_node_id) THEN
634 IF x_paths_tbl(m.parent_expl_node_id)='.' THEN
635 x_paths_tbl(m.model_ref_expl_id) := TO_CHAR(m.component_id);
636 ELSE
637 x_paths_tbl(m.model_ref_expl_id) := TO_CHAR(m.component_id)||'.'||x_paths_tbl(m.parent_expl_node_id);
638 END IF;
639 ELSE
640 x_paths_tbl(m.model_ref_expl_id) := '.';
641 END IF;
642
643 ELSE
644
645 IF x_paths_tbl.EXISTS(m.parent_expl_node_id) THEN
646 IF x_paths_tbl(m.parent_expl_node_id)='.' THEN
647 x_paths_tbl(m.model_ref_expl_id) := TO_CHAR(m.referring_node_id);
648 ELSE
649 x_paths_tbl(m.model_ref_expl_id) := TO_CHAR(m.referring_node_id)||'.'||x_paths_tbl(m.parent_expl_node_id);
650 END IF;
651 ELSE
652 x_paths_tbl(m.model_ref_expl_id) := '.';
653 END IF;
654
655 END IF;
656
657 END LOOP;
658
659 END calc_Expl_Paths;
660
661 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
662 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
663
664 --
665 -- populate target subtree = array that contains expl tree of the target model ( p_target_model_id )
666 -- Example :
667 -- M3
668 -- |-->M2
669 -- |--C2
670 -- |------>M1
671 -- |--->C1
672 -- now if we add a circular connector from M4 to M3
673 -- then target subtree will look like :
674 -- M3
675 -- |-->M2
676 -- |--C2
677 -- |------>M1
678 -- |--->C1
679 --
680 PROCEDURE get_Expl_Tree
681 (
682 p_target_model_id IN NUMBER,
683 px_root_expl_id OUT NOCOPY NUMBER,
684 p_target_subtree_tbl IN OUT NOCOPY ArrayId
685 ) IS
686 l_paths_tbl VArchar2Array;
687 l_expl_id NUMBER;
691 FROM CZ_MODEL_REF_EXPLS
688 BEGIN
689 FOR i IN (SELECT parent_expl_node_id,referring_node_id,ps_node_type,
690 virtual_flag,component_id,expl_node_type,model_ref_expl_id,LEVEL
692 START WITH model_id=p_target_model_id AND component_id=p_target_model_id -- this condition specifies root of expl tree
693 AND deleted_flag=NO_FLAG
694 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG)
695 LOOP
696 IF i.parent_expl_node_id IS NULL THEN
697 px_root_expl_id:=i.model_ref_expl_id;
698 END IF;
699 p_target_subtree_tbl(i.model_ref_expl_id).new_id:=i.model_ref_expl_id;
700 p_target_subtree_tbl(i.model_ref_expl_id).parent_id:=i.parent_expl_node_id;
701 p_target_subtree_tbl(i.model_ref_expl_id).ps_node_id:=i.referring_node_id;
702 p_target_subtree_tbl(i.model_ref_expl_id).ps_node_type:=i.ps_node_type;
703 p_target_subtree_tbl(i.model_ref_expl_id).virtual_flag:=i.virtual_flag;
704 p_target_subtree_tbl(i.model_ref_expl_id).component_id:=i.component_id;
705 p_target_subtree_tbl(i.model_ref_expl_id).expl_node_type:=i.expl_node_type;
706 p_target_subtree_tbl(i.model_ref_expl_id).child_model_id:=i.model_ref_expl_id;
707 p_target_subtree_tbl(i.model_ref_expl_id).current_level:=i.LEVEL;
708 END LOOP;
709
710 calc_Expl_Paths(px_root_expl_id,l_paths_tbl);
711 l_expl_id := p_target_subtree_tbl.First;
712 LOOP
713 IF l_expl_id IS NULL THEN
714 EXIT;
715 END IF;
716 p_target_subtree_tbl(l_expl_id).expl_path := l_paths_tbl(l_expl_id);
717 l_expl_id := p_target_subtree_tbl.NEXT(l_expl_id);
718 END LOOP;
719
720 END get_Expl_Tree;
721
722 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
723 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
724
725 --
726 -- the procedure uses target subtree to construct attached subtree in circular connectors case
727 --
728 PROCEDURE filter_Branches
729 (
730 p_target_model_id IN NUMBER,
731 p_target_subtree_tbl IN OUT NOCOPY ArrayId, -- stores target subtree
732 x_subtree_tbl IN OUT NOCOPY ArrayId -- will store subtree
733 ) IS
734
735 t_model_ref_expl_ids_tbl IntArray;
736
737 BEGIN
738 --
739 -- inialize OUTPUT subtree
740 --
741 x_subtree_tbl:=p_target_subtree_tbl;
742
743 --
744 -- collect expl_ids which belong to subtrees of the references/connectors of excluded branch
745 -- Example :
746 -- suppose p_target_subtree_tbl looks like :
747 -- M3
748 -- |---> M2
749 -- |---C2
750 -- |-~~~~~~~~->M1 ( CONNECTOR )
751 -- | |_C1
752 -- |--------------->M5
753 -- then we need to collect expl_ids of the following subtrees
754 -- M1 and M5
755 -- |--C1
756 -- so in this case t_model_ref_expl_ids_tbl will store 2 elements
757 --
758 FOR i IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
759 WHERE model_id=p_target_model_id AND deleted_flag=NO_FLAG AND
760 ps_node_type IN(CONNECTOR_TYPE,REFERENCE_TYPE) )
761 LOOP
762 FOR j IN (SELECT model_ref_expl_id,ps_node_type
763 FROM CZ_MODEL_REF_EXPLS
764 START WITH model_ref_expl_id = i.model_ref_expl_id AND deleted_flag=NO_FLAG
765 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG)
766 LOOP
767 t_model_ref_expl_ids_tbl(t_model_ref_expl_ids_tbl.COUNT+1):=j.model_ref_expl_id;
768 END LOOP;
769 END LOOP;
770
771 IF t_model_ref_expl_ids_tbl.COUNT=0 THEN
772 RETURN;
773 END IF;
774
775 --
776 -- remove those branches from the target subtree which point to model with model_id=p_exluded_model_id
777 -- <=> remove all expl_ids from ( x_subtree_tbl that was initilized as p_target_subtree_tbl )
778 -- that we collected before
779 --
780 FOR i IN t_model_ref_expl_ids_tbl.FIRST..t_model_ref_expl_ids_tbl.LAST
781 LOOP
782 x_subtree_tbl.DELETE(t_model_ref_expl_ids_tbl(i));
783 END LOOP;
784
785 END filter_Branches;
786
787 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
788 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
789
790 --
791 -- the procedure is used for attaching subtree to the new subtree root node
792 -- of model from the NEXT LEVEL of the chain of connected models
793 -- this means that p_subtree_tbl contains subtree from the PREVIOUS LEVEL
794 -- we need to rekey this subtree and attach it to the new subtree root node identified by p_new_root_expl_id
795 -- of the model from the NEXT LEVEL
796 --
797 PROCEDURE attach_Subtree
798 (
799 p_model_id IN NUMBER, -- model_id of the model to which we are attaching subtree
800 p_new_root_expl_id IN NUMBER, -- expl_id of new root node
801 p_new_root_level IN NUMBER, -- node_depth of new root node
802 p_subtree_root_expl_id IN NUMBER, -- expl_id of the rood node of subtree
803 p_subtree_tbl IN ArrayId) IS -- stores subtree
804
808
805 t_subtree_tbl ArrayId; -- stores rehashed ( rekeyed ) subtree
806
807 t_next_level_expl_ids_tbl ArrayId;
809 v_parent_id CZ_MODEL_REF_EXPLS.parent_expl_node_id%TYPE;
810 v_level CZ_MODEL_REF_EXPLS.node_depth%TYPE;
811 v_next_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
812 v_ind CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
813 v_subtree_root_level CZ_MODEL_REF_EXPLS.node_depth%TYPE;
814
815 BEGIN
816
817 t_subtree_tbl := p_subtree_tbl;
818
819 ---- FIRST STEP : rehash subtree
820
821 v_ind:=p_subtree_tbl.FIRST;
822 LOOP
823 IF v_ind IS NULL THEN
824 EXIT;
825 END IF;
826
827 IF (v_ind = p_subtree_root_expl_id) THEN
828 v_subtree_root_level := t_subtree_tbl(v_ind).current_level;
829 END IF;
830
831 t_subtree_tbl(v_ind).new_id := allocate_Expl_Id;
832 v_ind:=p_subtree_tbl.NEXT(v_ind);
833 END LOOP;
834
835 ---- NEXT STEP : attach subtree
836
837 v_ind:=t_subtree_tbl.FIRST;
838 LOOP
839 IF v_ind IS NULL THEN
840 EXIT;
841 END IF;
842
843 IF (v_ind = p_subtree_root_expl_id) THEN
844 --
845 -- if this is a root node
846 -- then attach the root node to a new subtree root identified by model_ref_expl_id = p_new_root_expl_id
847 --
848 v_parent_id := p_new_root_expl_id;
849 v_level := p_new_root_level+1;
850 ELSE
851 BEGIN
852 v_level:= p_new_root_level + (t_subtree_tbl(v_ind).current_level-v_subtree_root_level);
853 v_parent_id:=t_subtree_tbl(t_subtree_tbl(v_ind).parent_id).new_id;
854 EXCEPTION
855 WHEN NO_DATA_FOUND THEN
856 v_parent_id:=t_subtree_tbl(p_subtree_root_expl_id).new_id;
857 WHEN OTHERS THEN
858 v_parent_id:=t_subtree_tbl(p_subtree_root_expl_id).new_id;
859 END;
860 END IF;
861
862 IF v_parent_id=p_new_root_expl_id THEN -- this is a root of attached subtree
863 g_root_point_tree_tbl(p_new_root_expl_id) := t_subtree_tbl(v_ind).new_id; -- store maping between attach point and root of attached subtree
864 END IF;
865
866 INSERT INTO CZ_MODEL_REF_EXPLS
867 (model_ref_expl_id,
868 parent_expl_node_id,
869 referring_node_id,
870 model_id,
871 component_id,
872 child_model_expl_id,
873 ps_node_type,
874 virtual_flag,
875 node_depth,
876 expl_node_type,
877 deleted_flag)
878 VALUES
879 (t_subtree_tbl(v_ind).new_id,
880 v_parent_id,
881 t_subtree_tbl(v_ind).ps_node_id,
882 p_model_id,
883 t_subtree_tbl(v_ind).component_id,
884 NULL,
885 t_subtree_tbl(v_ind).ps_node_type,
886 t_subtree_tbl(v_ind).virtual_flag,
887 v_level,
888 t_subtree_tbl(v_ind).expl_node_type,
889 NO_FLAG);
890
891 t_next_level_expl_ids_tbl(v_ind).new_id := t_subtree_tbl(v_ind).new_id;
892 t_next_level_expl_ids_tbl(v_ind).ps_node_id := t_subtree_tbl(v_ind).ps_node_id;
893 t_next_level_expl_ids_tbl(v_ind).component_id := t_subtree_tbl(v_ind).component_id;
894 t_next_level_expl_ids_tbl(v_ind).expl_path := t_subtree_tbl(v_ind).expl_path;
895
896
897 v_ind:=t_subtree_tbl.NEXT(v_ind);
898 END LOOP;
899
900 -- release memory allocated for t_subtree_tbl array
901 t_subtree_tbl.DELETE;
902
903 FOR i IN(SELECT ps_node_id, devl_project_id FROM CZ_PS_NODES a
904 WHERE reference_id=p_model_id AND deleted_flag='0'
905 AND EXISTS (SELECT NULL FROM cz_devl_projects
906 WHERE devl_project_id = a.devl_project_id
907 AND deleted_flag='0'))
908 LOOP
909 FOR h IN (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
910 WHERE model_id=i.devl_project_id AND
911 child_model_expl_id=p_new_root_expl_id AND deleted_flag='0')
912 LOOP
913 g_hash_tree_tbl(h.model_ref_expl_id) := t_next_level_expl_ids_tbl;
914 END LOOP;
915 END LOOP;
916
917 t_next_level_expl_ids_tbl.DELETE;
918
919 END attach_Subtree;
920
921 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
922 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
923
924 --
925 -- this procedure populates child_model_expl_id's.
926 -- It uses global arrays g_root_point_tree_tbl and g_hash_tree_tbl which are
927 -- populated in attach_Subtree()
928 --
929 PROCEDURE populate_Child_Model_Expl_id IS
930
931 t_attach_subtree_tbl ArrayId;
932 l_expl_paths_tbl Varchar2Array;
933 l_new_expl_id NUMBER;
934
935 v_ind NUMBER;
936 v_expl_id NUMBER;
940
937 v_root_expl_id NUMBER;
938
939 BEGIN
941 v_ind := g_hash_tree_tbl.First;
942 LOOP
943 IF v_ind IS NULL THEN
944 EXIT;
945 END IF;
946
947 v_root_expl_id := g_root_point_tree_tbl(v_ind);
948 t_attach_subtree_tbl := g_hash_tree_tbl(v_ind);
949
950 calc_Expl_Paths(v_root_expl_id, l_expl_paths_tbl);
951
952 l_new_expl_id := l_expl_paths_tbl.First;
953 LOOP
954 IF l_new_expl_id IS NULL THEN
955 EXIT;
956 END IF;
957
958 v_expl_id := t_attach_subtree_tbl.First;
959 LOOP
960 IF v_expl_id IS NULL THEN
961 EXIT;
962 END IF;
963
964 IF l_expl_paths_tbl(l_new_expl_id) = t_attach_subtree_tbl(v_expl_id).expl_path THEN
965
966 UPDATE CZ_MODEL_REF_EXPLS
967 SET child_model_expl_id=t_attach_subtree_tbl(v_expl_id).new_id
968 WHERE model_ref_expl_id=l_new_expl_id;
969
970 END IF;
971 v_expl_id := t_attach_subtree_tbl.NEXT(v_expl_id);
972 END LOOP;
973
974 l_new_expl_id := l_expl_paths_tbl.NEXT(l_new_expl_id);
975 END LOOP;
976 l_expl_paths_tbl.DELETE;
977 v_ind := g_hash_tree_tbl.NEXT(v_ind);
978 END LOOP;
979
980 END populate_Child_Model_Expl_id;
981
982
983 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
984 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
985
986 --
987 -- attach subtree ( presented as array ) to all connected models
988 --
989 PROCEDURE copy_Subtree
990 (p_model_id IN INTEGER, -- current model id
991 p_target_model_id IN INTEGER,
992 p_parent_ps_id IN INTEGER, -- ps_node_id of the node to attach subtree
993 p_target_root_expl_id IN INTEGER, -- expl_id of the root node of subtree ( <=> p_target_subtree_tbl )
994 p_target_subtree_tbl IN OUT NOCOPY ArrayId, -- array which contains subtree
995 p_ps_node_type IN INTEGER
996 ) IS
997 TYPE IntArrayIndexVC2 IS TABLE OF NUMBER INDEX BY VARCHAR2(15);
998 t_temp_tree_tbl IntArray;
999 t_connect_point_tbl IntArray;
1000 t_models_level IntArrayIndexVC2; -- is populated by model_ids of the current level ( in a chain of connected models )
1001 t_new_models_level IntArrayIndexVC2; -- is populated by model_ids for the next level ( in a chain of connected models )
1002 t_hash_models IntArrayIndexVC2; -- stores already handled models
1003 t_subtree_tbl ArrayId;
1004 v_circularity_exists VARCHAR2(1):=NO_FLAG;
1005 v_connector_parent_exists VARCHAR2(1):=NO_FLAG;
1006 v_ind NUMBER;
1007 v_el NUMBER;
1008
1009 BEGIN
1010 -- initialize global arrays which stores expl_ids
1011 g_hash_tree_tbl.DELETE;
1012 g_root_point_tree_tbl.DELETE;
1013
1014 IF p_target_model_id=p_model_id THEN -- this is an obvious circular case
1015
1016 v_circularity_exists:=YES_FLAG;
1017
1018 --
1019 -- exlude all References/Connectors from the subtree that we are attaching
1020 -- to the current model (p_model_id)
1021 -- t_subtree_tbl will store the subtree without References/Connectors subtrees
1022 --
1023 filter_Branches(p_target_model_id => p_model_id,
1024 p_target_subtree_tbl => p_target_subtree_tbl,
1025 x_subtree_tbl => t_subtree_tbl);
1026
1027 ELSE
1028 --
1029 -- check circularity
1030 --
1031 v_circularity_exists := circularity_Exists(p_model_id,p_target_model_id);
1032 IF v_circularity_exists=YES_FLAG AND p_ps_node_type=CONNECTOR_TYPE THEN
1033
1034 filter_Branches(p_target_model_id => p_target_model_id,
1035 p_target_subtree_tbl => p_target_subtree_tbl,
1036 x_subtree_tbl => t_subtree_tbl);
1037 ELSE
1038 t_subtree_tbl:=p_target_subtree_tbl;
1039 END IF;
1040
1041 END IF;
1042
1043 g_hash_tree_tbl(t_subtree_tbl(p_target_root_expl_id).parent_id) := t_subtree_tbl;
1044
1045 --
1046 -- first - attach the subtree to the current model ( <=> p_model_id )
1047 --
1048 attach_Subtree(p_model_id => p_model_id, -- specifies model to which we are attaching the subtree
1049 p_new_root_expl_id => t_subtree_tbl(p_target_root_expl_id).parent_id, -- expl_id of new subtree root
1050 p_new_root_level => t_subtree_tbl(p_target_root_expl_id).current_level, -- node_depth of new subtree root
1051 p_subtree_root_expl_id => p_target_root_expl_id, -- expl_id=index of subtree root in subtree array p_subtree_tbl
1052 p_subtree_tbl => t_subtree_tbl); -- subtree array
1053
1054 --
1055 -- if there are no any connected models then just exit
1056 --
1057 IF t_chain.COUNT=0 THEN
1058 --
1059 -- populate child_model_expl_id's
1060 --
1061 populate_Child_Model_Expl_id();
1062 RETURN;
1063 END IF;
1064
1065 --
1066 -- initilaize hash map of models on the current level
1070
1067 --
1068 t_models_level(p_model_id):=p_model_id;
1069
1071 --
1072 -- initilaize hash map of already handled models
1073 --
1074 t_hash_models(p_model_id):=p_model_id;
1075
1076
1077 --
1078 -- loop through t_models_level array
1079 -- initially t_model_level contains just p_model_id ( source model )
1080 --
1081 LOOP
1082 --
1083 -- go through all connected models
1084 -- in order to find all models connected to the model with model_id = t_models_level(v_ind)
1085 -- Example :
1086 -- M3
1087 -- |--->M2
1088 -- |---->M1
1089 -- ^
1090 -- M10 |
1091 -- |----------
1092 --
1093 -- initially we have t_model_level(M1)=M1
1094 -- and t_chain ( model_id/parent_model_id ) = { M1/M2, M2/M3, M3/null }
1095 -- so in this algorithm we go through all t_chain elements and find
1096 -- those which are parent elements ( elementS because the same model can be referenced from the different models )
1097 -- if we start with M1 then
1098 -- next level will contain t_chain elements for which t_chain(i).parent_model_id = M1
1099 -- => it will contain M2 and M10
1100 -- t_new_models_level will be populated with {M2, M10}
1101 -- at the bottom of the loop we have t_models_level:=lt_new_models_level;
1102 -- so on next itteration we will be using t_models_level = { M2, M10}
1103 --
1104 FOR i IN t_chain.FIRST..t_chain.LAST
1105 LOOP
1106 v_ind:=t_models_level.FIRST;
1107 LOOP
1108 IF v_ind IS NULL THEN
1109 EXIT;
1110 END IF;
1111
1112 --
1113 -- starting with the current model go up to the chain of connected
1114 -- models level by level
1115 --
1116 IF t_chain(i).parent_model_id=t_models_level(v_ind) -- find all models connected to the current one
1117 AND t_chain(i).model_id <> t_chain(i).parent_model_id THEN -- if it's circular connector
1118 -- case then we don't need to attach
1119 -- the target subtree again
1120 --
1121 -- if t_chain has a duplicates of model_ids then
1122 -- we don't need to apply algorithm second time
1123 -- one model must be handled only once
1124 --
1125 IF NOT(t_hash_models.EXISTS(t_chain(i).model_id)) THEN
1126
1127 --
1128 -- t_connect_point_tbl stores already handled branches
1129 -- Example :
1130 -- M2
1131 -- |--Ref1--expl_id=1000---->M1
1132 --
1133 -- |--Ref2--expl_id=2000---->M1
1134 --
1135 -- and we want to attach the target subtree under M1
1136 -- in model M2 we have 2 entries that have component_id = p_parent_ps_id = M1
1137 -- these are references Ref1 and Ref2
1138
1139 FOR m IN(SELECT model_ref_expl_id,component_id,ps_node_type,node_depth
1140 FROM CZ_MODEL_REF_EXPLS
1141 WHERE model_id=t_chain(i).model_id AND component_id=p_parent_ps_id AND
1142 parent_expl_node_id IS NOT NULL AND deleted_flag=NO_FLAG)
1143 LOOP
1144
1145 BEGIN
1146 v_connector_parent_exists:=NO_FLAG;
1147 SELECT YES_FLAG INTO v_connector_parent_exists FROM dual
1148 WHERE EXISTS(SELECT NULL FROM CZ_MODEL_REF_EXPLS
1149 WHERE ps_node_type=CONNECTOR_TYPE
1150 START WITH model_ref_expl_id=m.model_ref_expl_id
1151 CONNECT BY PRIOR parent_expl_node_id=model_ref_expl_id AND deleted_flag=NO_FLAG AND deleted_flag=NO_FLAG);
1152 EXCEPTION
1153 WHEN NO_DATA_FOUND THEN
1154 NULL;
1155 END;
1156
1157 --
1158 -- t_connect_point_tbl - hash map that stores expl_ids of
1159 -- already handled branches
1160 -- so we need to attach subtree just in case when
1161 -- this given branch is not handled yet
1162 --
1163 IF NOT(t_connect_point_tbl.EXISTS(m.model_ref_expl_id)) AND
1164 NOT( p_ps_node_type=CONNECTOR_TYPE AND v_connector_parent_exists=YES_FLAG) THEN -- not a connector's subtree under connector
1165
1166 IF v_circularity_exists = YES_FLAG THEN
1167
1168 IF p_ps_node_type=CONNECTOR_TYPE THEN
1169
1170 --
1171 -- remove all expl subtrees under References/Connectors which
1172 -- belong to model with model_id=t_chain(i).model_id
1173 -- t_subtree_tbl will store OUTPUT subtree
1174 --
1175 filter_Branches(p_target_model_id => p_target_model_id,
1179 --
1176 p_target_subtree_tbl => p_target_subtree_tbl,
1177 x_subtree_tbl => t_subtree_tbl);
1178
1180 -- attach subtree with removed expl subtrees under References/Connectors which
1181 -- belong to model with model_id=t_chain(i).model_id
1182 -- to expl_id = m.model_ref_expl_id
1183 --
1184 attach_Subtree(p_model_id => t_chain(i).model_id,
1185 p_new_root_expl_id => m.model_ref_expl_id, -- expl_id of the next "attach point"
1186 p_new_root_level => m.node_depth, -- node_depth of the next "attach point"
1187 p_subtree_root_expl_id => p_target_root_expl_id, -- expl_id=index of subtree array associated
1188 -- with a root node
1189 p_subtree_tbl => t_subtree_tbl); -- subtree array
1190 END IF;
1191 ELSE
1192
1193 --
1194 -- attach the target subtree to expl_id = m.model_ref_expl_id
1195 -- here we don't need to change initial target subtree p_target_subtree_tbl
1196 -- we need just attach it to an appropriate expl node
1197 --
1198 attach_Subtree(p_model_id => t_chain(i).model_id,
1199 p_new_root_expl_id => m.model_ref_expl_id, -- expl_id of the next "attach point"
1200 p_new_root_level => m.node_depth, -- node_depth of the next "attach point"
1201 p_subtree_root_expl_id => p_target_root_expl_id, -- expl_id=index of subtree array associated
1202 -- with a root node
1203 p_subtree_tbl => p_target_subtree_tbl); -- subtree array
1204
1205 END IF;
1206
1207 END IF;
1208 --
1209 -- put expl_id of branch that we've just handled
1210 --
1211 t_connect_point_tbl(m.model_ref_expl_id):= t_chain(i).model_id;
1212 END LOOP; -- end of loop through CZ_MODEL_REF_EXPLS table --
1213
1214 --
1215 -- put the current model = t_chain(i).model_id to the hash
1216 -- of models which already hadnled
1217 -- this hash is used in order to avoid attaching
1218 -- the subtree twice ( or more than twice )
1219 --
1220 t_hash_models(t_chain(i).model_id):= t_chain(i).model_id;
1221
1222 --
1223 -- populate array of models for the next level
1224 -- models from the current level will be used as base level
1225 -- on the next itteration
1226 --
1227 t_new_models_level(t_chain(i).model_id):=t_chain(i).model_id;
1228
1229 END IF; -- end of if NOT(t_hash_models.EXISTS(t_chain(i).model_id)) --
1230
1231 END IF; -- end of if t_chain(i).parent_model_id=t_models_level(v_ind)
1232
1233 v_ind:=t_models_level.NEXT(v_ind);
1234
1235 END LOOP; -- end of loop for t_models_level array
1236
1237 END LOOP; -- end of loop for i in t_chain.First..t_chain.Last --
1238
1239 --
1240 -- now we going to the next itteration
1241 -- so we need to set t_models_level to array of the models from the just handled level
1242 --
1243 t_models_level:=t_new_models_level; t_new_models_level.DELETE;
1244
1245 --
1246 -- if there are no connected models to models on the current level then exit
1247 --
1248 IF (t_models_level.COUNT = 0) THEN
1249 EXIT;
1250 END IF;
1251
1252 END LOOP;
1253
1254 --
1255 -- populate child_model_expl_id's
1256 --
1257 populate_Child_Model_Expl_id();
1258
1259 EXCEPTION
1260 WHEN OTHERS THEN
1261 LOG_REPORT('copy_Subtree','ERROR CODE :'||ERROR_CODE||' : '||SQLERRM);
1262 END Copy_Subtree;
1263
1264 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1265 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1266
1267 PROCEDURE add_Reference
1268 (p_ps_node_id IN INTEGER,
1269 p_to_model_id IN INTEGER, -- reference to p_to_model_id
1270 p_containing_model_id IN INTEGER, -- current Model where reference is created
1271 p_virtual_flag IN VARCHAR2,
1272 p_out_err OUT NOCOPY INTEGER,
1273 p_ps_type IN INTEGER DEFAULT NULL,
1274 p_expl_node_type IN INTEGER -- DEFAULT MANDATORY_EXPL_TYPE
1275 ) IS
1276
1277 t_subtree_tbl ArrayId; -- stores subtree array
1278 t_prev_level_expl_ids_tbl IntArray; -- stores expl_ids of the target subtree
1282 v_parent_level CZ_MODEL_REF_EXPLS.node_depth%TYPE; -- node_depth of new subtree root node
1279 -- which will be used as child_expl_ids on the next level
1280 v_parent_ps_node_id CZ_PS_NODES.ps_node_id%TYPE; -- ps_node_id of new subtree root node
1281 v_parent_expl_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE; -- expl_id of new subtree root node
1283 v_target_root_expl_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE; -- root expl_id of the target model
1284 v_next_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE; -- stores expl_id generated by sequence
1285
1286 BEGIN
1287 Initialize;
1288 p_out_err:=0;
1289
1290 --
1291 -- find node in Model Ref Expls which can be used as parent node
1292 -- for Subtree
1293 --
1294 get_Node_Up(p_ps_node_id,p_containing_model_id,
1295 v_parent_ps_node_id,v_parent_expl_id,v_parent_level);
1296
1297 --
1298 -- get the target subtree
1299 --
1300 get_Expl_Tree(p_target_model_id => p_to_model_id,
1301 px_root_expl_id => v_target_root_expl_id,
1302 p_target_subtree_tbl => t_subtree_tbl);
1303
1304 --
1305 -- create array element associated with the Reference ( ps_node_id = p_ps_node_id) itself
1306 --
1307 t_subtree_tbl(v_target_root_expl_id).parent_id := v_parent_expl_id; -- <=> model_ref_expl_id of the node to attach subtree
1308 t_subtree_tbl(v_target_root_expl_id).ps_node_id := p_ps_node_id;
1309 t_subtree_tbl(v_target_root_expl_id).ps_node_type := p_ps_type;
1310 t_subtree_tbl(v_target_root_expl_id).virtual_flag := p_virtual_flag;
1311 t_subtree_tbl(v_target_root_expl_id).current_level := v_parent_level; -- node_depth of the node to attach subtree plus 1
1312 t_subtree_tbl(v_target_root_expl_id).component_id := p_to_model_id; -- referenced model ( <=> target model )
1313 t_subtree_tbl(v_target_root_expl_id).child_model_id := v_target_root_expl_id; -- <=> model_ref_expl_id of the root node of target model
1314 t_subtree_tbl(v_target_root_expl_id).expl_node_type := p_expl_node_type;
1315
1316 --
1317 -- attach Subtree to the containing model --
1318 -- here we assume that root node of subtree is already populated
1319 --
1320 copy_Subtree(p_model_id => p_containing_model_id,
1321 p_target_model_id => p_to_model_id,
1322 p_parent_ps_id => v_parent_ps_node_id,
1323 p_target_root_expl_id => v_target_root_expl_id,
1324 p_target_subtree_tbl => t_subtree_tbl,
1325 p_ps_node_type => p_ps_type);
1326
1327 EXCEPTION
1328 WHEN OTHERS THEN
1329 p_out_err:=m_RUN_ID;
1330 LOG_REPORT('add_Reference',
1331 'Error : ps_node_id='||TO_CHAR(p_ps_node_id)||' < '||ERROR_CODE||' > :'||SQLERRM);
1332 END add_Reference;
1333
1334 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1335 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1336
1337 PROCEDURE add_Ref
1338 (p_ps_node_id IN INTEGER,
1339 p_to_model_id IN INTEGER,
1340 p_containing_model_id IN INTEGER) IS
1341
1342 v_out_err INTEGER;
1343
1344 BEGIN
1345 add_Reference(p_ps_node_id,p_to_model_id, p_containing_model_id,
1346 NO_FLAG,v_out_err,REFERENCE_TYPE, MANDATORY_EXPL_TYPE);
1347 END;
1348
1349
1350 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1351 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1352
1353 PROCEDURE add_Connector
1354 (p_ps_node_id IN INTEGER,
1355 p_to_model_id IN INTEGER,
1356 p_containing_model_id IN INTEGER) IS
1357
1358 v_out_err INTEGER;
1359
1360 BEGIN
1361 add_Reference(p_ps_node_id,p_to_model_id, p_containing_model_id,
1362 NO_FLAG,v_out_err,CONNECTOR_TYPE, MANDATORY_EXPL_TYPE);
1363
1364 END;
1365
1366 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1367 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1368
1369 PROCEDURE get_Expl_Id
1370 (p_ps_node_id IN INTEGER,
1371 p_project_id IN INTEGER,
1372 p_out_expl_id IN OUT NOCOPY INTEGER,
1373 p_out_level IN OUT NOCOPY INTEGER,
1374 p_ps_node_type IN INTEGER DEFAULT NULL ) IS
1375
1376 ret INTEGER:=NULL_VALUE;
1377
1378 BEGIN
1379 p_out_expl_id:=NULL_VALUE;
1380 p_out_level:=NULL_VALUE;
1381
1382
1383 IF p_ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) THEN
1384 FOR i IN (SELECT model_ref_expl_id,node_depth FROM CZ_MODEL_REF_EXPLS
1385 WHERE model_id=p_project_id AND referring_node_id=p_ps_node_id
1386 AND child_model_expl_id IS NULL AND deleted_flag=NO_FLAG)
1387 LOOP
1388 p_out_expl_id:=i.model_ref_expl_id;
1389 p_out_level:=i.node_depth;
1390 END LOOP;
1391 ELSE
1392 FOR i IN (SELECT model_ref_expl_id,node_depth FROM CZ_MODEL_REF_EXPLS
1393 WHERE model_id=p_project_id AND referring_node_id IS NULL
1394 AND component_id=p_ps_node_id AND child_model_expl_id IS NULL AND
1398 p_out_level:=i.node_depth;
1395 deleted_flag=NO_FLAG)
1396 LOOP
1397 p_out_expl_id:=i.model_ref_expl_id;
1399 END LOOP;
1400 END IF;
1401
1402 EXCEPTION
1403 WHEN OTHERS THEN
1404 LOG_REPORT('get_Expl_Id','ERROR CODE :'||ERROR_CODE||' : '||SQLERRM);
1405 END;
1406
1407 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1408 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1409
1410 PROCEDURE get_Node_Up
1411 (p_ps_node_id IN INTEGER,
1412 p_project_id IN INTEGER,
1413 p_out_ps_node_id IN OUT NOCOPY INTEGER,
1414 p_out_expl_id IN OUT NOCOPY INTEGER,
1415 p_out_level IN OUT NOCOPY INTEGER) IS
1416
1417 v_expl_id INTEGER;
1418 v_temp INTEGER;
1419
1420 BEGIN
1421 p_out_ps_node_id:=NULL_VALUE;
1422 p_out_expl_id:=NULL_VALUE;
1423 p_out_level:=NULL_VALUE;
1424
1425 FOR i IN (SELECT ps_node_id,ps_node_type,parent_id FROM CZ_PS_NODES
1426 WHERE ps_node_id=p_ps_node_id AND deleted_flag=NO_FLAG)
1427 LOOP
1428 get_Expl_Id(i.ps_node_id,p_project_id,v_expl_id,p_out_level,i.ps_node_type);
1429
1430 IF v_expl_id<>NULL_VALUE THEN
1431 p_out_ps_node_id:=i.ps_node_id;
1432 p_out_expl_id:=v_expl_id;
1433 EXIT;
1434 END IF;
1435
1436 IF i.ps_node_id<>p_project_id AND v_expl_id=NULL_VALUE THEN
1437 get_Node_Up(i.parent_id,p_project_id,p_out_ps_node_id,
1438 p_out_expl_id,p_out_level);
1439 ELSE
1440 p_out_ps_node_id:=p_project_id;
1441 END IF;
1442 END LOOP;
1443
1444 EXCEPTION
1445 WHEN OTHERS THEN
1446 LOG_REPORT('get_Node_Up','ERROR CODE :'||ERROR_CODE||' : '||SQLERRM);
1447 END;
1448
1449 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1450 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1451
1452 PROCEDURE get_Node_Down
1453 (p_ps_node_id IN INTEGER,
1454 p_project_id IN INTEGER,
1455 p_out_ps_node_id IN OUT NOCOPY INTEGER,
1456 p_out_expl_id IN OUT NOCOPY INTEGER,
1457 p_out_level IN OUT NOCOPY INTEGER) IS
1458
1459 v_expl_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
1460 NODE_FOUND EXCEPTION;
1461
1462 BEGIN
1463 p_out_ps_node_id:=NULL_VALUE;
1464 p_out_expl_id:=NULL_VALUE;
1465 p_out_level:=NULL_VALUE;
1466 get_Expl_Id(p_ps_node_id,p_project_id,v_expl_id,p_out_level);
1467 IF v_expl_id<>NULL_VALUE THEN
1468 p_out_ps_node_id:=p_ps_node_id;
1469 p_out_expl_id:=v_expl_id;
1470 RAISE NODE_FOUND;
1471 END IF;
1472
1473 FOR i IN (SELECT ps_node_id,parent_id FROM CZ_PS_NODES
1474 WHERE parent_id=p_ps_node_id AND deleted_flag=NO_FLAG)
1475 LOOP
1476 get_Expl_Id(i.ps_node_id,p_project_id,v_expl_id,p_out_level);
1477
1478 IF v_expl_id<>NULL_VALUE THEN
1479 p_out_ps_node_id:=i.ps_node_id;
1480 p_out_expl_id:=v_expl_id;
1481 EXIT;
1482 END IF;
1483
1484 IF v_expl_id=NULL_VALUE THEN
1485 get_Node_Down(i.ps_node_id,p_project_id,p_out_ps_node_id,
1486 p_out_expl_id,p_out_level);
1487 ELSE
1488 p_out_ps_node_id:=NULL_VALUE;
1489 END IF;
1490 END LOOP;
1491
1492 EXCEPTION
1493 WHEN NODE_FOUND THEN
1494 NULL;
1495 WHEN OTHERS THEN
1496 LOG_REPORT('get_Node_Down','ERROR CODE :'||ERROR_CODE||' : '||SQLERRM);
1497 END;
1498
1499 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1500 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1501
1502 FUNCTION get_Next_ExplId
1503 (p_expl_id IN INTEGER,
1504 p_child_expl_id IN INTEGER,
1505 p_mode IN VARCHAR2 -- default PLUS_MODE
1506 )
1507 RETURN INTEGER IS
1508
1509 v_ref_id INTEGER;
1510
1511 BEGIN
1512
1513 IF p_mode=PLUS_MODE THEN
1514
1515 --
1516 -- DEBUG ERROR CODE --
1517 --
1518 ERROR_CODE:=300;
1519
1520 SELECT model_ref_expl_id INTO v_ref_id FROM CZ_MODEL_REF_EXPLS
1521 WHERE parent_expl_node_id=p_expl_id
1522 AND (child_model_expl_id=p_child_expl_id OR model_ref_expl_id=p_child_expl_id)
1523 AND deleted_flag=NO_FLAG;
1524
1525 --
1526 -- DEBUG ERROR CODE --
1527 --
1528 ERROR_CODE:=301;
1529
1530 ELSE
1531 --
1532 -- DEBUG ERROR CODE --
1533 --
1534 ERROR_CODE:=302;
1535
1536 SELECT parent_expl_node_id INTO v_ref_id FROM CZ_MODEL_REF_EXPLS
1537 WHERE model_ref_expl_id=p_expl_id;
1538
1539 --
1540 -- DEBUG ERROR CODE --
1541 --
1542 ERROR_CODE:=303;
1543
1544 END IF;
1545
1546 RETURN v_ref_id;
1547
1548 END;
1549
1550 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1551 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1552
1553 PROCEDURE set_Expls
1554 (p_project_id IN INTEGER,
1555 p_ps_node_id IN INTEGER,
1559 v_model_ref_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE ;
1556 p_mode IN VARCHAR2, -- DEFAULT PLUS_MODE
1557 p_deleted_expl_id IN INTEGER DEFAULT NULL) IS
1558
1560 v_project_id CZ_MODEL_REF_EXPLS.model_id%TYPE;
1561 v_ref_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
1562
1563 BEGIN
1564
1565 IF p_mode=PLUS_MODE THEN
1566
1567 --
1568 -- DEBUG ERROR CODE --
1569 --
1570 ERROR_CODE:=400;
1571
1572 SELECT model_ref_expl_id INTO v_model_ref_id FROM
1573 CZ_MODEL_REF_EXPLS WHERE model_id=p_project_id
1574 AND component_id=p_ps_node_id AND child_model_expl_id IS NULL AND deleted_flag=NO_FLAG;
1575
1576 --
1577 -- DEBUG ERROR CODE --
1578 --
1579 ERROR_CODE:=401;
1580
1581 END IF;
1582 IF p_mode=MINUS_MODE THEN
1583 v_model_ref_id:=p_deleted_expl_id;
1584 END IF;
1585
1586 FOR i IN (SELECT ps_node_id,ps_node_type,NAME FROM CZ_PS_NODES
1587 START WITH devl_project_id=p_project_id AND deleted_flag=NO_FLAG AND ps_node_id=p_ps_node_id
1588 CONNECT BY PRIOR ps_node_id=parent_id AND NVL(virtual_flag,'1')='1' AND deleted_flag=NO_FLAG
1589 AND PRIOR deleted_flag=NO_FLAG)
1590 LOOP
1591
1592 --
1593 -- DEBUG ERROR CODE --
1594 --
1595 ERROR_CODE:=402;
1596
1597 FOR m IN(SELECT expr_node_id,rule_id,model_ref_expl_id FROM CZ_EXPRESSION_NODES
1598 WHERE ps_node_id=i.ps_node_id AND deleted_flag=NO_FLAG)
1599 LOOP
1600 BEGIN
1601
1602 --
1603 -- DEBUG ERROR CODE --
1604 --
1605 ERROR_CODE:=403;
1606
1607 v_ref_id:=get_Next_ExplId(m.model_ref_expl_id,v_model_ref_id,p_mode);
1608
1609 --
1610 -- DEBUG ERROR CODE --
1611 --
1612 ERROR_CODE:=404;
1613
1614 UPDATE CZ_EXPRESSION_NODES
1615 SET model_ref_expl_id=v_ref_id
1616 WHERE expr_node_id=m.expr_node_id AND rule_id=m.rule_id;
1617
1618 --
1619 -- DEBUG ERROR CODE --
1620 --
1621 ERROR_CODE:=405;
1622
1623 EXCEPTION
1624 WHEN NO_DATA_FOUND THEN
1625 NULL;
1626 WHEN OTHERS THEN
1627 LOG_REPORT('set_Expls','CZ_EXPRESSION_NODES ERROR CODE :'||
1628 ERROR_CODE||' : '||SQLERRM);
1629 END;
1630 END LOOP;
1631
1632 IF i.ps_node_type IN(PRODUCT_TYPE,COMPONENT_TYPE) THEN
1633
1634 --
1635 -- DEBUG ERROR CODE --
1636 --
1637 ERROR_CODE:=406;
1638
1639 FOR m IN(SELECT func_comp_id,component_id,model_ref_expl_id FROM CZ_FUNC_COMP_SPECS
1640 WHERE component_id=i.ps_node_id AND deleted_flag=NO_FLAG)
1641 LOOP
1642 BEGIN
1643
1644 --
1645 -- DEBUG ERROR CODE --
1646 --
1647 ERROR_CODE:=407;
1648
1649 v_ref_id:=get_Next_ExplId(m.model_ref_expl_id,v_model_ref_id,p_mode);
1650
1651 --
1652 -- DEBUG ERROR CODE --
1653 --
1654 ERROR_CODE:=408;
1655
1656 UPDATE CZ_FUNC_COMP_SPECS
1657 SET model_ref_expl_id=v_ref_id
1658 WHERE func_comp_id=m.func_comp_id AND component_id=m.component_id;
1659
1660 --
1661 -- DEBUG ERROR CODE --
1662 --
1663 ERROR_CODE:=409;
1664
1665 EXCEPTION
1666 WHEN OTHERS THEN
1667 LOG_REPORT('set_Expls','CZ_FUNC_COMP_SPECS : func_comp_id='||TO_CHAR(m.func_comp_id));
1668 END;
1669
1670 END LOOP;
1671
1672 END IF;
1673
1674
1675 IF i.ps_node_type IN(FEATURE_TYPE) THEN
1676
1677 --
1678 -- DEBUG ERROR CODE --
1679 --
1680 ERROR_CODE:=410;
1681
1682 FOR m IN(SELECT rule_id,feature_id,model_ref_expl_id FROM CZ_COMBO_FEATURES
1683 WHERE feature_id=i.ps_node_id AND deleted_flag=NO_FLAG)
1684 LOOP
1685 BEGIN
1686
1687 --
1688 -- DEBUG ERROR CODE --
1689 --
1690 ERROR_CODE:=411;
1691
1692 v_ref_id:=get_Next_ExplId(m.model_ref_expl_id,v_model_ref_id,p_mode);
1693
1694 --
1695 -- DEBUG ERROR CODE --
1696 --
1697 ERROR_CODE:=412;
1698
1699 UPDATE CZ_COMBO_FEATURES
1700 SET model_ref_expl_id=v_ref_id
1701 WHERE rule_id=m.rule_id AND feature_id=m.feature_id;
1702
1703 --
1704 -- DEBUG ERROR CODE --
1705 --
1706 ERROR_CODE:=413;
1707
1708
1709 EXCEPTION
1710 WHEN NO_DATA_FOUND THEN
1711 LOG_REPORT('set_Expls','CZ_COMBO_FEATURES : feature_id='||TO_CHAR(m.feature_id)||' rule_id='||TO_CHAR(m.rule_id));
1712 WHEN OTHERS THEN
1716 END LOOP;
1713 LOG_REPORT('set_Expls','CZ_COMBO_FEATURES : feature_id='||TO_CHAR(m.feature_id)||' rule_id='||TO_CHAR(m.rule_id));
1714 END;
1715
1717
1718 --
1719 -- DEBUG ERROR CODE --
1720 --
1721 ERROR_CODE:=414;
1722
1723 FOR m IN(SELECT rule_id,feature_id,model_ref_expl_id FROM CZ_DES_CHART_FEATURES
1724 WHERE feature_id=i.ps_node_id AND deleted_flag=NO_FLAG)
1725 LOOP
1726 BEGIN
1727
1728 --
1729 -- DEBUG ERROR CODE --
1730 --
1731 ERROR_CODE:=415;
1732
1733 v_ref_id:=get_Next_ExplId(m.model_ref_expl_id,v_model_ref_id,p_mode);
1734
1735 --
1736 -- DEBUG ERROR CODE --
1737 --
1738 ERROR_CODE:=416;
1739
1740 UPDATE CZ_DES_CHART_FEATURES
1741 SET model_ref_expl_id=v_ref_id
1742 WHERE rule_id=m.rule_id AND feature_id=m.feature_id;
1743
1744 --
1745 -- DEBUG ERROR CODE --
1746 --
1747 ERROR_CODE:=417;
1748
1749 EXCEPTION
1750 WHEN NO_DATA_FOUND THEN
1751 LOG_REPORT('set_Expls','CZ_DES_CHART_FEATURES : feature_id='||TO_CHAR(m.feature_id)||' rule_id='||TO_CHAR(m.rule_id));
1752 WHEN OTHERS THEN
1753 LOG_REPORT('set_Expls','CZ_DES_CHART_FEATURES : feature_id='||TO_CHAR(m.feature_id)||' rule_id='||TO_CHAR(m.rule_id));
1754 END;
1755 END LOOP;
1756
1757 FOR m IN(SELECT rule_id,primary_opt_id,secondary_opt_id,secondary_feature_id,
1758 secondary_feat_expl_id FROM CZ_DES_CHART_CELLS
1759 WHERE secondary_feature_id=i.ps_node_id AND deleted_flag=NO_FLAG)
1760 LOOP
1761 BEGIN
1762 v_ref_id:=get_Next_ExplId(m.secondary_feat_expl_id,v_model_ref_id,p_mode);
1763 UPDATE CZ_DES_CHART_CELLS
1764 SET secondary_feat_expl_id=v_ref_id
1765 WHERE rule_id=m.rule_id AND secondary_feature_id=m.secondary_feature_id AND
1766 primary_opt_id=m.primary_opt_id AND secondary_opt_id=m.secondary_opt_id;
1767
1768 EXCEPTION
1769 WHEN NO_DATA_FOUND THEN
1770 LOG_REPORT('set_Expls','CZ_DES_CHART_CELLS : secondary_feature_id='||TO_CHAR(m.secondary_feature_id)||' rule_id='||TO_CHAR(m.rule_id));
1771 WHEN OTHERS THEN
1772 LOG_REPORT('set_Expls','CZ_DES_CHART_CELLS : secondary_feature_id='||TO_CHAR(m.secondary_feature_id)||' rule_id='||TO_CHAR(m.rule_id));
1773 END;
1774
1775 END LOOP;
1776
1777 END IF;
1778
1779 IF i.ps_node_type=OPTION_TYPE THEN
1780 FOR m IN(SELECT rule_id,option_id,model_ref_expl_id FROM CZ_DES_CHART_COLUMNS
1781 WHERE option_id=i.ps_node_id)
1782 LOOP
1783 BEGIN
1784 v_ref_id:=get_Next_ExplId(m.model_ref_expl_id,v_model_ref_id,p_mode);
1785
1786 UPDATE CZ_DES_CHART_COLUMNS
1787 SET model_ref_expl_id=v_ref_id
1788 WHERE rule_id=m.rule_id AND option_id=m.option_id;
1789
1790 EXCEPTION
1791 WHEN NO_DATA_FOUND THEN
1792 LOG_REPORT('set_Expls','CZ_DES_CHART_COLUMNS : option_id='||TO_CHAR(m.option_id)||' rule_id='||TO_CHAR(m.rule_id));
1793 WHEN OTHERS THEN
1794 LOG_REPORT('set_Expls','CZ_DES_CHART_COLUMNS : option_id='||TO_CHAR(m.option_id)||' rule_id='||TO_CHAR(m.rule_id));
1795 END;
1796 END LOOP;
1797 END IF;
1798
1799 END LOOP;
1800
1801 EXCEPTION
1802 WHEN OTHERS THEN
1803 LOG_REPORT('set_Expls','ERROR CODE : '||TO_CHAR(ERROR_CODE)||' ERROR MESSAGE : '||SQLERRM);
1804 END set_Expls;
1805
1806 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1807 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1808
1809 PROCEDURE refresh_UI_Expl_Ids(p_ps_node_id IN NUMBER,
1810 p_component_id IN NUMBER,
1811 p_model_id IN NUMBER,
1812 p_old_expl_id IN NUMBER,
1813 p_new_expl_id IN NUMBER) IS
1814
1815 t_persistent_node_id_tbl IntArray;
1816
1817 PROCEDURE refresh_It_For_Single_UI(p_current_ui_def_id IN NUMBER) IS
1818 BEGIN
1819
1820 FOR k IN t_persistent_node_id_tbl.First..t_persistent_node_id_tbl.Last
1821 LOOP
1822 UPDATE CZ_UI_PAGE_ELEMENTS
1823 SET model_ref_expl_id=p_new_expl_id
1824 WHERE ui_def_id=p_current_ui_def_id AND
1825 persistent_node_id=t_persistent_node_id_tbl(k) AND
1826 model_ref_expl_id=p_old_expl_id AND
1827 deleted_flag='0';
1828
1829 UPDATE CZ_UI_PAGES
1830 SET pagebase_expl_node_id=p_new_expl_id
1831 WHERE ui_def_id=p_current_ui_def_id AND
1832 persistent_node_id=t_persistent_node_id_tbl(k) AND
1833 pagebase_expl_node_id=p_old_expl_id AND
1834 deleted_flag='0';
1835
1836 UPDATE CZ_UI_PAGE_REFS
1837 SET target_expl_node_id=p_new_expl_id
1838 WHERE ui_def_id=p_current_ui_def_id AND
1839 target_persistent_node_id=t_persistent_node_id_tbl(k) AND
1840 target_expl_node_id=p_old_expl_id AND
1841 deleted_flag='0';
1842
1843 UPDATE CZ_UI_PAGE_SETS
1847 pagebase_expl_node_id=p_old_expl_id AND
1844 SET pagebase_expl_node_id=p_new_expl_id
1845 WHERE ui_def_id=p_current_ui_def_id AND
1846 persistent_node_id=t_persistent_node_id_tbl(k) AND
1848 deleted_flag='0';
1849
1850 UPDATE CZ_UI_ACTIONS
1851 SET target_expl_node_id=p_new_expl_id
1852 WHERE ui_def_id=p_current_ui_def_id AND
1853 target_persistent_node_id=t_persistent_node_id_tbl(k) AND
1854 target_expl_node_id=p_old_expl_id AND
1855 deleted_flag='0';
1856
1857 END LOOP;
1858
1859 END refresh_It_For_Single_UI;
1860
1861 PROCEDURE refresh_It_For_Single_Model(p_current_model_id IN NUMBER) IS
1862 BEGIN
1863 FOR i IN (SELECT ui_def_id FROM CZ_UI_DEFS
1864 WHERE devl_project_id=p_current_model_id AND
1865 ui_style='7' AND deleted_flag='0')
1866 LOOP
1867 refresh_It_For_Single_UI(i.ui_def_id);
1868 END LOOP;
1869 END refresh_It_For_Single_Model;
1870
1871 BEGIN
1872
1873 SELECT persistent_node_id
1874 BULK COLLECT INTO t_persistent_node_id_tbl
1875 FROM CZ_PS_NODES
1876 START WITH ps_node_id=p_ps_node_id AND deleted_flag='0' AND
1877 (reference_id IS NULL AND component_id=p_component_id)
1878 CONNECT BY PRIOR ps_node_id=parent_id AND deleted_flag='0' AND
1879 PRIOR deleted_flag='0' AND (reference_id IS NULL AND component_id=p_component_id);
1880
1881 refresh_It_For_Single_Model(p_model_id);
1882
1883 IF t_chain.COUNT>0 THEN
1884 FOR i IN t_chain.First..t_chain.Last
1885 LOOP
1886 refresh_It_For_Single_Model(t_chain(i).model_id);
1887 END LOOP;
1888 END IF;
1889
1890 END refresh_UI_Expl_Ids;
1891
1892 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1893 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1894
1895 PROCEDURE delete_it
1896 (p_expl_id IN INTEGER,
1897 p_del_logically IN VARCHAR2 -- DEFAULT YES_FLAG
1898 ) IS
1899
1900 BEGIN
1901 IF p_del_logically=YES_FLAG THEN
1902
1903 --
1904 -- DEBUG ERROR CODE --
1905 --
1906 ERROR_CODE:=500;
1907
1908 UPDATE CZ_MODEL_REF_EXPLS SET deleted_flag=YES_FLAG WHERE model_ref_expl_id IN
1909 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
1910 START WITH model_ref_expl_id=p_expl_id AND deleted_flag=NO_FLAG
1911 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG);
1912
1913 --
1914 -- DEBUG ERROR CODE --
1915 --
1916 ERROR_CODE:=501;
1917
1918 ELSE
1919 --
1920 -- DEBUG ERROR CODE --
1921 --
1922 ERROR_CODE:=502;
1923
1924 DELETE FROM CZ_MODEL_REF_EXPLS WHERE model_ref_expl_id IN
1925 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
1926 START WITH model_ref_expl_id=p_expl_id AND deleted_flag=NO_FLAG
1927 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG);
1928
1929 --
1930 -- DEBUG ERROR CODE --
1931 --
1932 ERROR_CODE:=503;
1933
1934 END IF;
1935 END;
1936
1937
1938 PROCEDURE delete_Node_
1939 (p_ps_node_id IN INTEGER,
1940 p_ps_node_type IN INTEGER,
1941 p_del_logically IN VARCHAR2 -- DEFAULT '1'
1942 ) IS
1943
1944 PROCEDURE delete_it_
1945 (p_expl_id IN INTEGER,
1946 p_del_logically IN VARCHAR2, -- DEFAULT YES_FLAG
1947 p_ps_node_type IN INTEGER DEFAULT NULL) IS
1948
1949 BEGIN
1950 IF p_del_logically=YES_FLAG THEN
1951 UPDATE CZ_MODEL_REF_EXPLS SET deleted_flag=YES_FLAG
1952 WHERE model_ref_expl_id=p_expl_id;
1953 ELSE
1954 DELETE FROM CZ_MODEL_REF_EXPLS WHERE model_ref_expl_id=p_expl_id;
1955 END IF;
1956
1957 IF p_ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) THEN
1958 IF p_del_logically=YES_FLAG THEN
1959 UPDATE CZ_MODEL_REF_EXPLS SET deleted_flag=YES_FLAG
1960 WHERE deleted_flag=NO_FLAG AND model_ref_expl_id IN
1961 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
1962 START WITH model_ref_expl_id=p_expl_id AND deleted_flag=NO_FLAG
1963 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND
1964 PRIOR deleted_flag=NO_FLAG);
1965 ELSE
1966 DELETE FROM CZ_MODEL_REF_EXPLS
1967 WHERE model_ref_expl_id IN
1968 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
1969 START WITH model_ref_expl_id=p_expl_id AND deleted_flag=NO_FLAG
1970 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG);
1971 END IF;
1972 END IF;
1973 END delete_it_;
1974
1975 BEGIN
1976 IF p_ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) THEN
1977 FOR i IN (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
1978 WHERE referring_node_id=p_ps_node_id AND deleted_flag=NO_FLAG)
1979 LOOP
1980 delete_it_(i.model_ref_expl_id, YES_FLAG);
1981 END LOOP;
1982 ELSE
1983 FOR i IN (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
1987 END LOOP;
1984 WHERE component_id=p_ps_node_id AND deleted_flag=NO_FLAG)
1985 LOOP
1986 delete_it_(i.model_ref_expl_id, YES_FLAG);
1988 END IF;
1989 END delete_Node_;
1990
1991 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
1992 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
1993
1994 FUNCTION containsBOM(p_model_id IN INTEGER,p_instanciable OUT NOCOPY INTEGER) RETURN NUMBER IS
1995
1996 v_exist_bom_nodes VARCHAR2(1);
1997 v_ret NUMBER:=NULL_VALUE;
1998
1999 BEGIN
2000 p_instanciable:=0;
2001
2002 FOR i IN(SELECT model_ref_expl_id,referring_node_id,component_id,expl_node_type FROM CZ_MODEL_REF_EXPLS
2003 START WITH model_id=p_model_id AND parent_expl_node_id IS NULL AND deleted_flag=NO_FLAG
2004 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND
2005 deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG
2006 AND ps_node_type IN(BOM_MODEL_TYPE,COMPONENT_TYPE,REFERENCE_TYPE)
2007 AND PRIOR ps_node_type IN(BOM_MODEL_TYPE,COMPONENT_TYPE,REFERENCE_TYPE))
2008 LOOP
2009 BEGIN
2010 SELECT '1' INTO v_exist_bom_nodes
2011 FROM dual WHERE
2012 EXISTS(SELECT NULL FROM CZ_PS_NODES WHERE devl_project_id=i.component_id
2013 AND ps_node_type=436 AND deleted_flag=NO_FLAG);
2014
2015 IF i.referring_node_id IS NOT NULL THEN
2016 v_ret:=i.referring_node_id;
2017 ELSE
2018 v_ret:=i.component_id;
2019 END IF;
2020 IF i.expl_node_type=MINMAX_EXPL_TYPE THEN
2021 p_instanciable:=1;
2022 RETURN v_ret;
2023 END IF;
2024 EXCEPTION
2025 WHEN OTHERS THEN
2026 NULL;
2027 END;
2028 END LOOP;
2029 RETURN v_ret;
2030 END containsBOM;
2031
2032 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2033 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
2034
2035 PROCEDURE get_from_Node_Up
2036 (p_ps_node_id IN INTEGER,
2037 p_project_id IN INTEGER,
2038 p_out_ps_node_id IN OUT NOCOPY INTEGER,
2039 p_out_expl_id IN OUT NOCOPY INTEGER,
2040 p_out_level IN OUT NOCOPY INTEGER) IS
2041
2042 v_expl_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE:=NULL_VALUE;
2043 v_parent_id CZ_PS_NODES.parent_id%TYPE:=NULL_VALUE;
2044
2045 BEGIN
2046
2047 p_out_ps_node_id:=NULL_VALUE;
2048 p_out_expl_id:=NULL_VALUE;
2049 p_out_level:=NULL_VALUE;
2050
2051 BEGIN
2052 --
2053 -- DEBUG ERROR CODE --
2054 --
2055 ERROR_CODE:=1001;
2056
2057 SELECT parent_id INTO v_parent_id
2058 FROM CZ_PS_NODES WHERE ps_node_id=p_ps_node_id;
2059
2060 IF v_parent_id IS NULL THEN
2061 p_out_ps_node_id:=p_ps_node_id;
2062 --
2063 -- DEBUG ERROR CODE --
2064 --
2065 ERROR_CODE:=1002;
2066
2067 SELECT model_ref_expl_id,node_depth
2068 INTO p_out_expl_id,p_out_level
2069 FROM CZ_MODEL_REF_EXPLS WHERE model_id=p_ps_node_id
2070 AND parent_expl_node_id IS NULL AND deleted_flag=NO_FLAG;
2071
2072 RETURN;
2073 END IF;
2074 EXCEPTION
2075 WHEN OTHERS THEN
2076 LOG_REPORT('get_from_Node_Up','ERROR CODE : '||TO_CHAR(ERROR_CODE)||' ERROR MESSAGE : '||SQLERRM);
2077 END;
2078
2079 FOR i IN (SELECT ps_node_id,parent_id,virtual_flag FROM CZ_PS_NODES
2080 WHERE ps_node_id=v_parent_id AND deleted_flag=NO_FLAG)
2081 LOOP
2082
2083 IF i.virtual_flag=NO_FLAG OR i.parent_id IS NULL THEN
2084 p_out_ps_node_id:=i.ps_node_id;
2085
2086 --
2087 -- DEBUG ERROR CODE --
2088 --
2089 ERROR_CODE:=700;
2090
2091 SELECT model_ref_expl_id,node_depth
2092 INTO p_out_expl_id,p_out_level
2093 FROM CZ_MODEL_REF_EXPLS WHERE model_id=p_project_id
2094 AND component_id=i.ps_node_id AND deleted_flag=NO_FLAG;
2095
2096 --
2097 -- DEBUG ERROR CODE --
2098 --
2099 ERROR_CODE:=708;
2100
2101 EXIT;
2102 ELSE
2103 --
2104 -- DEBUG ERROR CODE --
2105 --
2106 ERROR_CODE:=709;
2107
2108 get_from_Node_Up(i.ps_node_id,p_project_id,p_out_ps_node_id,p_out_expl_id,p_out_level);
2109
2110 --
2111 -- DEBUG ERROR CODE --
2112 --
2113 ERROR_CODE:=710;
2114
2115 END IF;
2116 END LOOP;
2117
2118 EXCEPTION
2119 WHEN OTHERS THEN
2120 LOG_REPORT('get_from_Node_Up','ERROR CODE : '||TO_CHAR(ERROR_CODE)||' ERROR MESSAGE : '||SQLERRM);
2121 END;
2122
2123 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2124 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
2125
2126 PROCEDURE update_Rules(p_ps_node_id IN INTEGER) IS
2127
2128 v_old_expl_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
2132 FOR i IN t_old_expl_ids.FIRST..t_old_expl_ids.LAST
2129 v_new_expl_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
2130
2131 BEGIN
2133 LOOP
2134 v_old_expl_id:=t_old_expl_ids(i);
2135 v_new_expl_id:=t_new_expl_ids(i);
2136
2137 IF v_old_expl_id IS NOT NULL AND v_new_expl_id IS NOT NULL THEN
2138 FOR n IN(SELECT ps_node_id FROM CZ_PS_NODES
2139 START WITH ps_node_id=p_ps_node_id AND deleted_flag=NO_FLAG
2140 CONNECT BY PRIOR ps_node_id=parent_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG)
2141 LOOP
2142 UPDATE CZ_EXPRESSION_NODES SET model_ref_expl_id=v_new_expl_id
2143 WHERE model_ref_expl_id=v_old_expl_id AND deleted_flag=NO_FLAG
2144 AND ps_node_id=n.ps_node_id;
2145
2146 UPDATE CZ_COMBO_FEATURES
2147 SET model_ref_expl_id=v_new_expl_id
2148 WHERE model_ref_expl_id=v_old_expl_id AND deleted_flag=NO_FLAG
2149 AND feature_id=n.ps_node_id;
2150
2151 UPDATE CZ_DES_CHART_FEATURES
2152 SET model_ref_expl_id=v_new_expl_id
2153 WHERE model_ref_expl_id=v_old_expl_id AND deleted_flag=NO_FLAG
2154 AND feature_id=n.ps_node_id;
2155
2156 UPDATE CZ_DES_CHART_CELLS
2157 SET secondary_feat_expl_id=v_new_expl_id
2158 WHERE secondary_feat_expl_id=v_old_expl_id AND deleted_flag=NO_FLAG
2159 AND (secondary_feature_id=n.ps_node_id OR
2160 primary_opt_id=n.ps_node_id OR secondary_opt_id=n.ps_node_id);
2161
2162 UPDATE CZ_FUNC_COMP_SPECS
2163 SET model_ref_expl_id=v_new_expl_id
2164 WHERE model_ref_expl_id=v_old_expl_id AND deleted_flag=NO_FLAG AND
2165 component_id=n.ps_node_id;
2166
2167 UPDATE CZ_DES_CHART_COLUMNS
2168 SET model_ref_expl_id=v_new_expl_id
2169 WHERE model_ref_expl_id=v_old_expl_id AND
2170 option_id=n.ps_node_id;
2171 END LOOP;
2172 END IF;
2173 END LOOP;
2174 END;
2175
2176 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2177 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
2178
2179 PROCEDURE populate_tree
2180 (p_parent_ref_expl_id IN INTEGER, -- model_ref_expl_id of parent node
2181 p_model_ref_expl_id IN INTEGER, -- model_ref_expl_id of current node
2182 p_ps_node_id IN INTEGER, -- ps_node_id of current node
2183 p_ps_node_type IN INTEGER, -- ps_node_type of current node
2184 p_expl_node_type IN INTEGER, -- expl_node_type of current node
2185 p_model_id IN INTEGER, -- model_id of current model
2186 p_child_array IN IntArray -- array of direct childs ( in PS tree )
2187 ) IS
2188
2189 t_child_nodes IntArray;
2190 v_expl_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
2191 v_model_id CZ_MODEL_REF_EXPLS.model_id%TYPE;
2192 v_ps_node_id CZ_PS_NODES.ps_node_id%TYPE;
2193
2194 BEGIN
2195 IF t_chain.COUNT=0 THEN
2196 RETURN;
2197 END IF;
2198 FOR i IN t_chain.FIRST..t_chain.LAST
2199 LOOP
2200 IF t_chain(i).parent_model_id=p_model_id THEN
2201 v_model_id:=t_chain(i).model_id;
2202
2203 FOR l IN(SELECT model_ref_expl_id,node_depth
2204 FROM CZ_MODEL_REF_EXPLS
2205 WHERE model_id=t_chain(i).model_id AND
2206 child_model_expl_id=p_parent_ref_expl_id
2207 AND deleted_flag=NO_FLAG)
2208 LOOP
2209
2210 --
2211 -- generate the next PK for CZ_MODEL_REF_EXPLS table
2212 --
2213 v_expl_id:=allocate_Expl_Id;
2214
2215 --
2216 -- DEBUG ERROR CODE --
2217 --
2218 ERROR_CODE:=800;
2219
2220 INSERT INTO CZ_MODEL_REF_EXPLS
2221 (model_ref_expl_id,
2222 parent_expl_node_id,
2223 referring_node_id,
2224 model_id,
2225 component_id,
2226 ps_node_type,
2227 virtual_flag,
2228 node_depth,
2229 expl_node_type,
2230 child_model_expl_id,
2231 deleted_flag)
2232 VALUES
2233 (v_expl_id,
2234 l.model_ref_expl_id,
2235 NULL,
2236 v_model_id,
2237 p_ps_node_id,
2238 p_ps_node_type,
2239 NO_FLAG,
2240 l.node_depth+1,
2241 p_expl_node_type,
2242 p_model_ref_expl_id,
2243 NO_FLAG);
2244
2245 --
2246 -- DEBUG ERROR CODE --
2247 --
2248 ERROR_CODE:=801;
2249
2250 --
2251 -- reset childs nodes
2252 --
2253 IF p_child_array.COUNT>0 THEN
2254 FOR t IN p_child_array.FIRST..p_child_array.LAST
2255 LOOP
2256 t_child_nodes.DELETE;
2257 v_ps_node_id:=p_child_array(t);
2258
2259 --
2263
2260 -- DEBUG ERROR CODE --
2261 --
2262 ERROR_CODE:=802;
2264 UPDATE CZ_MODEL_REF_EXPLS
2265 SET parent_expl_node_id=v_expl_id
2266 WHERE parent_expl_node_id=l.model_ref_expl_id
2267 AND model_id=v_model_id AND
2268 ((ps_node_type IN(PRODUCT_TYPE,COMPONENT_TYPE,BOM_MODEL_TYPE) AND component_id=v_ps_node_id)
2269 OR (ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) AND referring_node_id=v_ps_node_id))
2270 AND deleted_flag=NO_FLAG
2271 RETURNING model_ref_expl_id BULK COLLECT INTO t_child_nodes ;
2272
2273 --
2274 -- DEBUG ERROR CODE --
2275 --
2276 ERROR_CODE:=803;
2277
2278 IF t_child_nodes.COUNT>0 THEN
2279
2280 --
2281 -- DEBUG ERROR CODE --
2282 --
2283 ERROR_CODE:=804;
2284
2285 FORALL h IN t_child_nodes.FIRST..t_child_nodes.LAST
2286 UPDATE CZ_MODEL_REF_EXPLS
2287 SET node_depth=node_depth+1
2288 WHERE model_ref_expl_id IN
2289 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
2290 START WITH model_ref_expl_id=t_child_nodes(h) AND deleted_flag=NO_FLAG
2291 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG
2292 AND PRIOR deleted_flag=NO_FLAG);
2293
2294 --
2295 -- DEBUG ERROR CODE --
2296 --
2297 ERROR_CODE:=805;
2298
2299 END IF;
2300 END LOOP;
2301 END IF;
2302
2303 --
2304 -- DEBUG ERROR CODE --
2305 --
2306 ERROR_CODE:=806;
2307
2308 populate_tree
2309 (l.model_ref_expl_id,v_expl_id,p_ps_node_id,
2310 p_ps_node_type,p_expl_node_type,v_model_id,p_child_array);
2311
2312 --
2313 -- DEBUG ERROR CODE --
2314 --
2315 ERROR_CODE:=807;
2316
2317 END LOOP;
2318
2319 END IF;
2320 END LOOP;
2321
2322 --
2323 -- DEBUG ERROR CODE --
2324 --
2325 ERROR_CODE:=809;
2326
2327 END;
2328
2329 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2330 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
2331
2332 PROCEDURE populate_expl_tree_internal(
2333 p_parent_model_ref_expl_id IN NUMBER,
2334 p_model_ref_expl_id IN NUMBER,
2335 p_model_id IN NUMBER
2336 ) IS
2337 v_expl_id NUMBER;
2338 v_model_id NUMBER;
2339 BEGIN
2340
2341 IF t_chain.COUNT=0 THEN
2342 RETURN;
2343 END IF;
2344 FOR i IN t_chain.FIRST..t_chain.LAST
2345 LOOP
2346 IF t_chain(i).parent_model_id=p_model_id THEN
2347 v_model_id:=t_chain(i).model_id;
2348
2349 FOR l IN(SELECT model_ref_expl_id,node_depth
2350 FROM CZ_MODEL_REF_EXPLS
2351 WHERE model_id=t_chain(i).model_id AND
2352 child_model_expl_id=p_parent_model_ref_expl_id
2353 AND deleted_flag=NO_FLAG)
2354 LOOP
2355
2356 FOR m IN(SELECT *
2357 FROM CZ_MODEL_REF_EXPLS
2358 WHERE model_ref_expl_id = p_model_ref_expl_id
2359 AND deleted_flag=NO_FLAG)
2360 LOOP
2361 --
2362 -- generate the next PK for CZ_MODEL_REF_EXPLS table
2363 --
2364 v_expl_id:=allocate_Expl_Id;
2365
2366 INSERT INTO CZ_MODEL_REF_EXPLS
2367 (model_ref_expl_id,
2368 parent_expl_node_id,
2369 referring_node_id,
2370 model_id,
2371 component_id,
2372 ps_node_type,
2373 virtual_flag,
2374 node_depth,
2375 expl_node_type,
2376 child_model_expl_id,
2377 deleted_flag)
2378 VALUES
2379 (v_expl_id,
2380 l.model_ref_expl_id,
2381 m.referring_node_id,
2382 v_model_id,
2383 m.component_id,
2384 m.ps_node_type,
2385 m.virtual_flag,
2386 l.node_depth+1,
2387 m.expl_node_type,
2388 p_model_ref_expl_id,
2389 NO_FLAG);
2390 END LOOP;
2391
2392 populate_expl_tree_internal(l.model_ref_expl_id,v_expl_id,v_model_id);
2393 END LOOP;
2394 END IF;
2395 END LOOP;
2396 END populate_expl_tree_internal;
2400 p_model_id IN NUMBER,
2397 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2398 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
2399 PROCEDURE populate_parent_expl_tree_(
2401 p_model_ref_expl_id IN NUMBER,
2402 p_parent_model_ref_expl_id IN NUMBER
2403 ) IS
2404 BEGIN
2405
2406 populate_expl_tree_internal(p_parent_model_ref_expl_id,p_model_ref_expl_id,p_model_id);
2407
2408 FOR i IN (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS WHERE parent_expl_node_id = p_model_ref_expl_id AND deleted_flag = NO_FLAG)
2409 LOOP
2410 populate_parent_expl_tree_(p_model_id => p_model_id, p_model_ref_expl_id => i.model_ref_expl_id, p_parent_model_ref_expl_id => p_model_ref_expl_id);
2411 END LOOP;
2412
2413
2414 END populate_parent_expl_tree_;
2415
2416 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2417 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
2418 --
2419 -- vsingava bug7831246 02th Mar '09
2420 -- procedure which populates the set of explosions p_ps_node_id of p_model_id
2421 -- to all model referencing it, up the entire model heirarchy. Usually called when a node
2422 -- in structure is being copied
2423 --
2424 PROCEDURE populate_parent_expl_tree(
2425 p_ps_node_id IN NUMBER, -- ps_node_id of the copied node
2426 p_model_id IN NUMBER
2427 ) IS
2428
2429
2430 l_model_id NUMBER;
2431 l_component_id NUMBER;
2432 l_reference_id NUMBER;
2433 l_expl_id NUMBER;
2434 l_parent_id NUMBER;
2435 v_up_id INTEGER;
2436 v_up_expl_id INTEGER;
2437 v_up_level INTEGER;
2438 l_virtual_flag VARCHAR2(1);
2439 l_curr_node_depth NUMBER;
2440 l_min_node_depth NUMBER;
2441 l_ps_node_type NUMBER;
2442
2443 BEGIN
2444
2445 -- get the info from the node to be copied
2446 SELECT devl_project_id,parent_id,reference_id,component_id,virtual_flag, ps_node_type
2447 INTO l_model_id,l_parent_id,l_reference_id,l_component_id,l_virtual_flag, l_ps_node_type
2448 FROM CZ_PS_NODES
2449 WHERE ps_node_id=p_ps_node_id AND deleted_flag = NO_FLAG;
2450
2451 -- if not a reference node, get the model_refexpl_id record in model ref expls
2452 IF l_reference_id IS NULL THEN
2453 SELECT model_ref_expl_id, node_depth INTO l_expl_id, l_curr_node_depth
2454 FROM CZ_MODEL_REF_EXPLS
2455 WHERE model_id=l_model_id AND component_id=l_component_id AND
2456 child_model_expl_id IS NULL AND deleted_flag = NO_FLAG;
2457 ELSE
2458 SELECT MIN(node_depth) INTO l_min_node_depth
2459 FROM CZ_MODEL_REF_EXPLS
2460 WHERE model_id=l_model_id AND referring_node_id=p_ps_node_id
2461 AND deleted_flag = NO_FLAG;
2462
2463 SELECT model_ref_expl_id, node_depth INTO l_expl_id, l_curr_node_depth
2464 FROM CZ_MODEL_REF_EXPLS
2465 WHERE model_id=l_model_id AND referring_node_id=p_ps_node_id AND
2466 node_depth=l_min_node_depth AND deleted_flag = NO_FLAG;
2467 END IF;
2468
2469 t_chain.DELETE;t_projectCache.DELETE;
2470 populate_chain(p_model_id);
2471
2472 --
2473 -- find nearest non-virtual node above the target node --
2474 --
2475 get_Node_Up(p_ps_node_id,p_model_id,v_up_id,
2476 v_up_expl_id,v_up_level);
2477
2478 IF v_up_id = p_ps_node_id THEN
2479 get_Node_Up(l_parent_id,p_model_id,v_up_id,
2480 v_up_expl_id,v_up_level);
2481 END IF;
2482
2483 IF (l_virtual_flag='1' OR l_virtual_flag IS NULL) AND l_parent_id IS NOT NULL THEN
2484
2485 FOR i IN(SELECT model_ref_expl_id, node_depth FROM CZ_MODEL_REF_EXPLS
2486 WHERE parent_expl_node_id=v_up_expl_id AND
2487 deleted_flag = NO_FLAG AND
2488 (referring_node_id IS NULL AND component_id IN
2489 (SELECT ps_node_id FROM CZ_PS_NODES
2490 START WITH ps_node_id=p_ps_node_id
2491 CONNECT BY PRIOR ps_node_id=parent_id AND
2492 deleted_flag = NO_FLAG AND PRIOR deleted_flag = NO_FLAG)) OR
2493 (referring_node_id IS NOT NULL AND referring_node_id IN
2494 (SELECT ps_node_id FROM CZ_PS_NODES
2495 START WITH ps_node_id=p_ps_node_id
2496 CONNECT BY PRIOR ps_node_id=parent_id AND
2497 deleted_flag = NO_FLAG AND PRIOR deleted_flag = NO_FLAG)))
2498 LOOP
2499 populate_parent_expl_tree_(p_model_id => p_model_id,
2500 p_model_ref_expl_id => i.model_ref_expl_id,
2501 p_parent_model_ref_expl_id => v_up_expl_id);
2502 END LOOP;
2503 ELSIF l_virtual_flag='0' -- OR l_parent_id IS NULL
2504 THEN
2505 populate_parent_expl_tree_(p_model_id => p_model_id,
2506 p_model_ref_expl_id => l_expl_id,
2507 p_parent_model_ref_expl_id => v_up_expl_id);
2508 END IF;
2509
2510
2511 END populate_parent_expl_tree;
2512
2513 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2514 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
2515
2516 PROCEDURE update_child_nodes(p_model_id IN NUMBER) IS
2520 TYPE IntArrayIndexVC2 IS TABLE OF NUMBER INDEX BY VARCHAR2(15);
2517
2518 TYPE t_varchar2_tbl_type IS TABLE OF VARCHAR2(32000) INDEX BY BINARY_INTEGER;
2519 TYPE t_indchar2_tbl_type IS TABLE OF NUMBER INDEX BY VARCHAR2(32000);
2521 l_model_updated_tbl IntArrayIndexVC2;
2522
2523 l_num_updated_records NUMBER := 0;
2524
2525 PROCEDURE update_layer
2526 (
2527 p_current_model_id IN NUMBER
2528 ) IS
2529
2530 l_paths_tbl t_varchar2_tbl_type;
2531 l_new_paths_tbl t_varchar2_tbl_type;
2532 l_expl_path_tbl t_indchar2_tbl_type;
2533 l_new_expl_path_tbl t_indchar2_tbl_type;
2534 t_model_ref_expl_tbl IntArray;
2535 t_component_tbl IntArray;
2536
2537 l_next_model_level_tbl IntArray;
2538 l_child_expl_id NUMBER;
2539 l_path1 VARCHAR2(32000);
2540 l_path2 VARCHAR2(32000);
2541 l_loop_char_id VARCHAR2(32000);
2542 l_ref_expl_id NUMBER;
2543 l_counter NUMBER;
2544 l_loop_ind NUMBER;
2545
2546 BEGIN
2547
2548 IF l_model_updated_tbl.EXISTS(p_current_model_id) THEN
2549 RETURN;
2550 ELSE
2551 l_model_updated_tbl(p_current_model_id) := p_current_model_id;
2552 END IF;
2553
2554 FOR i IN(SELECT model_ref_expl_id, referring_node_id, component_id
2555 FROM CZ_MODEL_REF_EXPLS a
2556 WHERE a.model_id=p_current_model_id AND a.deleted_flag=NO_FLAG)
2557 LOOP
2558
2559 IF i.referring_node_id IS NULL THEN
2560 l_paths_tbl(i.model_ref_expl_id) := TO_CHAR(i.component_id);
2561 ELSE
2562 l_paths_tbl(i.model_ref_expl_id) := TO_CHAR(i.referring_node_id);
2563 END IF;
2564
2565 FOR m IN(SELECT model_ref_expl_id,component_id,referring_node_id
2566 FROM CZ_MODEL_REF_EXPLS
2567 WHERE model_ref_expl_id<>i.model_ref_expl_id
2568 START WITH model_ref_expl_id=i.model_ref_expl_id
2569 CONNECT BY PRIOR parent_expl_node_id=model_ref_expl_id AND deleted_flag=NO_FLAG)
2570 LOOP
2571
2572 IF m.referring_node_id IS NULL THEN
2573 l_paths_tbl(i.model_ref_expl_id) := l_paths_tbl(i.model_ref_expl_id) || ':' ||TO_CHAR(m.component_id);
2574 ELSE
2575 l_paths_tbl(i.model_ref_expl_id) := l_paths_tbl(i.model_ref_expl_id) || ':' ||TO_CHAR(m.referring_node_id);
2576 END IF;
2577
2578 END LOOP;
2579
2580 END LOOP;
2581
2582 l_loop_ind := l_paths_tbl.FIRST;
2583 LOOP
2584 IF l_loop_ind IS NULL THEN
2585 EXIT;
2586 END IF;
2587
2588 l_expl_path_tbl(l_paths_tbl(l_loop_ind)) := l_loop_ind;
2589
2590 l_loop_ind := l_paths_tbl.NEXT(l_loop_ind);
2591 END LOOP;
2592
2593 FOR n IN (SELECT * FROM CZ_PS_NODES a
2594 WHERE reference_id=p_current_model_id AND
2595 ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) AND deleted_flag=NO_FLAG AND
2596 devl_project_id IN(SELECT object_id FROM CZ_RP_ENTRIES
2597 WHERE object_id=a.devl_project_id AND object_type='PRJ' AND deleted_flag=NO_FLAG)
2598 )
2599 LOOP
2600
2601 l_new_expl_path_tbl.DELETE;
2602
2603 SELECT model_ref_expl_id INTO l_ref_expl_id FROM CZ_MODEL_REF_EXPLS
2604 WHERE model_id=n.devl_project_id AND referring_node_id=n.ps_node_id;
2605
2606 l_new_paths_tbl.DELETE;
2607
2608 FOR k IN(SELECT model_ref_expl_id,component_id,
2609 referring_node_id,ps_node_type,child_model_expl_id,model_id
2610 FROM CZ_MODEL_REF_EXPLS
2611 START WITH model_ref_expl_id=l_ref_expl_id AND deleted_flag=NO_FLAG
2612 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND
2613 deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG)
2614 LOOP
2615
2616 IF k.referring_node_id IS NULL THEN
2617 l_new_paths_tbl(k.model_ref_expl_id) := TO_CHAR(k.component_id);
2618 ELSE
2619 l_new_paths_tbl(k.model_ref_expl_id) := TO_CHAR(k.referring_node_id);
2620
2621 IF k.model_ref_expl_id=l_ref_expl_id THEN
2622 l_new_paths_tbl(k.model_ref_expl_id) := TO_CHAR(k.component_id);
2623 END IF;
2624
2625 END IF;
2626
2627 IF k.model_ref_expl_id <> l_ref_expl_id THEN
2628
2629 FOR kk IN(SELECT model_ref_expl_id,component_id,referring_node_id
2630 FROM CZ_MODEL_REF_EXPLS
2631 WHERE model_ref_expl_id<>k.model_ref_expl_id
2632 START WITH model_ref_expl_id=k.model_ref_expl_id
2633 CONNECT BY PRIOR parent_expl_node_id=model_ref_expl_id AND deleted_flag=NO_FLAG)
2634 LOOP
2635
2636 IF kk.referring_node_id IS NULL THEN
2637 l_new_paths_tbl(k.model_ref_expl_id) := l_new_paths_tbl(k.model_ref_expl_id) || ':' ||TO_CHAR(kk.component_id);
2638
2639 ELSE
2640
2641 IF kk.model_ref_expl_id=l_ref_expl_id THEN
2642 l_new_paths_tbl(k.model_ref_expl_id) := l_new_paths_tbl(k.model_ref_expl_id) || ':' || TO_CHAR(kk.component_id);
2643 EXIT;
2644 ELSE
2648 END IF;
2645 l_new_paths_tbl(k.model_ref_expl_id) := l_new_paths_tbl(k.model_ref_expl_id) || ':' ||TO_CHAR(kk.referring_node_id);
2646 END IF;
2647
2649
2650 END LOOP;
2651
2652 END IF;
2653
2654 END LOOP;
2655
2656 l_loop_ind := l_new_paths_tbl.FIRST;
2657 LOOP
2658 IF l_loop_ind IS NULL THEN
2659 EXIT;
2660 END IF;
2661
2662 IF l_expl_path_tbl.EXISTS(l_new_paths_tbl(l_loop_ind)) THEN
2663
2664 SELECT child_model_expl_id INTO l_child_expl_id FROM CZ_MODEL_REF_EXPLS
2665 WHERE model_ref_expl_id=l_loop_ind;
2666
2667 IF l_expl_path_tbl(l_new_paths_tbl(l_loop_ind)) <> l_child_expl_id THEN
2668
2669 l_num_updated_records := l_num_updated_records + 1;
2670
2671 UPDATE CZ_MODEL_REF_EXPLS
2672 SET child_model_expl_id=l_expl_path_tbl(l_new_paths_tbl(l_loop_ind))
2673 WHERE model_ref_expl_id=l_loop_ind;
2674
2675 END IF;
2676
2677 END IF;
2678
2679 l_loop_ind := l_new_paths_tbl.NEXT(l_loop_ind);
2680 END LOOP;
2681
2682 l_next_model_level_tbl(l_next_model_level_tbl.COUNT+1) := n.devl_project_id;
2683
2684 END LOOP;
2685
2686 IF l_next_model_level_tbl.COUNT > 0 THEN
2687
2688 FOR v IN l_next_model_level_tbl.FIRST..l_next_model_level_tbl.LAST
2689 LOOP
2690 update_layer(l_next_model_level_tbl(v));
2691 END LOOP;
2692
2693 END IF;
2694
2695
2696 END update_layer;
2697
2698 BEGIN
2699
2700 --
2701 -- start with those explosion tree branches which do not have references/connectors to other models
2702 --
2703 FOR i IN(SELECT DISTINCT component_id FROM CZ_MODEL_REF_EXPLS a
2704 WHERE model_id=p_model_id AND deleted_flag=NO_FLAG AND
2705 ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) AND NOT EXISTS
2706 (SELECT NULL FROM CZ_MODEL_REF_EXPLS
2707 WHERE model_id=a.component_id AND ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) AND deleted_flag=NO_FLAG))
2708 LOOP
2709 update_layer(i.component_id);
2710 END LOOP;
2711
2712 END update_child_nodes;
2713
2714 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2715 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
2716
2717 PROCEDURE delete_duplicates(p_model_id IN NUMBER) IS
2718
2719 BEGIN
2720 FOR i IN (SELECT DISTINCT model_id FROM CZ_MODEL_REF_EXPLS
2721 WHERE component_id=p_model_id AND referring_node_id IS NOT NULL
2722 AND deleted_flag=NO_FLAG)
2723 LOOP
2724 UPDATE CZ_MODEL_REF_EXPLS a
2725 SET deleted_flag = '1'
2726 WHERE model_id=i.model_id AND deleted_flag=NO_FLAG
2727 AND referring_node_id IS NOT NULL
2728 AND parent_expl_node_id IN
2729 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
2730 WHERE model_id=i.model_id AND deleted_flag=NO_FLAG
2731 AND referring_node_id=a.referring_node_id);
2732 END LOOP;
2733
2734 FOR i IN (SELECT DISTINCT component_id FROM CZ_MODEL_REF_EXPLS
2735 WHERE model_id=p_model_id AND referring_node_id IS NOT NULL
2736 AND deleted_flag=NO_FLAG)
2737 LOOP
2738 UPDATE CZ_MODEL_REF_EXPLS a
2739 SET deleted_flag = '1'
2740 WHERE model_id=i.component_id AND deleted_flag=NO_FLAG
2741 AND referring_node_id IS NOT NULL
2742 AND parent_expl_node_id IN
2743 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
2744 WHERE model_id=i.component_id AND deleted_flag=NO_FLAG
2745 AND referring_node_id=a.referring_node_id);
2746 END LOOP;
2747 END delete_duplicates;
2748
2749 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2750 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
2751
2752 PROCEDURE populate_COMPONENT_ID(p_model_id IN NUMBER) IS
2753
2754 v_ps_node_id CZ_PS_NODES.ps_node_id%TYPE;
2755 v_parent_id CZ_PS_NODES.parent_id%TYPE;
2756 v_ps_id CZ_PS_NODES.ps_node_id%TYPE;
2757
2758 BEGIN
2759 FND_MSG_PUB.initialize;
2760
2761 FOR i IN(SELECT ps_node_id,parent_id,ps_node_type,virtual_flag,devl_project_id,reference_id FROM CZ_PS_NODES
2762 START WITH devl_project_id=p_model_id AND parent_id IS NULL and deleted_flag='0'
2763 CONNECT BY PRIOR ps_node_id=parent_id AND deleted_flag='0' AND PRIOR deleted_flag='0')
2764 LOOP
2765 IF i.ps_node_type IN (REFERENCE_TYPE,CONNECTOR_TYPE) THEN
2766 UPDATE CZ_PS_NODES SET component_id=i.reference_id
2767 WHERE ps_node_id=i.ps_node_id AND component_id<>i.reference_id;
2768 GOTO CONTINUE_LOOP;
2769 END IF;
2770 IF i.parent_id IS NULL OR i.virtual_flag='0' THEN
2771 UPDATE CZ_PS_NODES SET component_id=i.ps_node_id
2772 WHERE ps_node_id=i.ps_node_id AND component_id<>i.ps_node_id;
2773 GOTO CONTINUE_LOOP;
2774 END IF;
2775 IF NVL(i.virtual_flag,'1')='1' AND i.ps_node_type NOT IN(REFERENCE_TYPE,CONNECTOR_TYPE) AND i.parent_id IS NOT NULL THEN
2779 (virtual_flag='0' OR parent_id IS NULL) AND deleted_flag='0')
2776 SELECT ps_node_id,parent_id INTO v_ps_node_id,v_parent_id FROM CZ_PS_NODES a
2777 WHERE devl_project_id=i.devl_project_id AND
2778 EXISTS(SELECT NULL FROM CZ_PS_NODES WHERE ps_node_id=a.parent_id AND
2780 START WITH ps_node_id=i.ps_node_id
2781 CONNECT BY PRIOR parent_id=ps_node_id AND deleted_flag='0' AND NVL(virtual_flag,'1')='1';
2782
2783 IF v_parent_id IS NULL THEN
2784 v_ps_id:=v_ps_node_id;
2785 ELSE
2786 v_ps_id:=v_parent_id;
2787 END IF;
2788
2789 UPDATE CZ_PS_NODES SET component_id=v_ps_id
2790 WHERE ps_node_id=i.ps_node_id AND component_id<>v_ps_id;
2791 END IF;
2792
2793 <<CONTINUE_LOOP>>
2794 NULL;
2795 END LOOP;
2796 END populate_COMPONENT_ID;
2797
2798 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
2799 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
2800
2801 PROCEDURE check_Node
2802 (p_ps_node_id IN INTEGER,
2803 p_model_id IN INTEGER,
2804 p_maximum IN INTEGER,
2805 p_minimum IN INTEGER,
2806 p_reference_id IN INTEGER,
2807 p_out_err OUT NOCOPY INTEGER,
2808 p_out_virtual_flag OUT NOCOPY INTEGER,
2809 p_consequent_flag IN VARCHAR2 , -- DEFAULT NO_FLAG,
2810 p_expr_node_id IN INTEGER DEFAULT NULL,
2811 p_ps_type IN INTEGER DEFAULT NULL,
2812 p_expr_subtype IN INTEGER DEFAULT NULL,
2813 p_skip_upd_nod_dep IN VARCHAR2 DEFAULT NO_FLAG
2814 ) IS
2815
2816 t_child_nodes IntArray;
2817 t_childs IntArray;
2818
2819 v_expl_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
2820 v_model_ref_expl_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
2821 v_expl_node_type CZ_MODEL_REF_EXPLS.expl_node_type%TYPE;
2822 v_del_ref_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
2823 v_up_expl_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
2824 v_up_level CZ_MODEL_REF_EXPLS.node_depth%TYPE;
2825 v_curr_expl_node_type CZ_MODEL_REF_EXPLS.expl_node_type%TYPE;
2826
2827 v_up_id CZ_PS_NODES.ps_node_id%TYPE;
2828 v_virtual_flag CZ_PS_NODES.virtual_flag%TYPE;
2829 v_ps_node_type CZ_PS_NODES.ps_node_type%TYPE;
2830 v_ps_node_id CZ_PS_NODES.ps_node_id%TYPE;
2831 v_minimum CZ_PS_NODES.MINIMUM%TYPE;
2832 v_maximum CZ_PS_NODES.MAXIMUM%TYPE;
2833 v_instantiable_flag CZ_PS_NODES.instantiable_flag%TYPE;
2834 v_parent_expl_node_id NUMBER;
2835 v_component_id NUMBER;
2836 v_comp_expl_id NUMBER;
2837 v_ndebug NUMBER;
2838 v_node_Exist BOOLEAN:=FALSE;
2839
2840 BEGIN
2841
2842 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2843 v_ndebug := 0;
2844 cz_utils.log_report('CZ_REFS', 'check_Node', v_ndebug,
2845 'Starting CZ_REFS.check_Node for model node with ps_node_id='||TO_CHAR(p_ps_node_id)||
2846 ' current time : '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS'),
2847 fnd_log.LEVEL_PROCEDURE);
2848 cz_utils.log_report('CZ_REFS', 'check_Node', v_ndebug,
2849 'Parameters : '||
2850 'p_ps_node_id = '||TO_CHAR(p_ps_node_id)||
2851 'p_model_id = '||TO_CHAR(p_model_id)||
2852 'p_maximum = '||TO_CHAR(p_maximum)||
2853 'p_minimum = '||TO_CHAR(p_minimum)||
2854 'p_reference_id = '||TO_CHAR(p_reference_id)||
2855 'p_consequent_flag = '||p_consequent_flag||
2856 'p_expr_node_id = '||TO_CHAR(p_expr_node_id)||
2857 'p_ps_type = '||TO_CHAR(p_ps_type)||
2858 'p_ps_type = '||TO_CHAR(p_ps_type)
2859 ,fnd_log.LEVEL_PROCEDURE);
2860 END IF;
2861
2862 BEGIN
2863 Initialize;
2864 p_out_err:=0;
2865 p_out_virtual_flag:=1;
2866
2867 SELECT instantiable_flag,ps_node_type,minimum,maximum,component_id
2868 INTO v_instantiable_flag,v_ps_node_type,v_minimum,v_maximum,v_component_id
2869 FROM CZ_PS_NODES
2870 WHERE ps_node_id=p_ps_node_id;
2871
2872 -- Developer passes max/min
2873 v_minimum := p_minimum;
2874 v_maximum := p_maximum;
2875
2876 IF NOT(v_minimum=1 AND v_maximum=1) AND v_ps_node_type<>BOM_MODEL_TYPE
2877 AND v_instantiable_flag=MANDATORY_EXPL_TYPE THEN
2878 p_out_err:=m_RUN_ID;
2879 LOG_REPORT('check_Node','CZ_PS_NODES.instantiable_flag=2 violates min/max=1/1.');
2880 RETURN;
2881 END IF;
2882
2883
2884 IF v_instantiable_flag NOT IN(OPTIONAL_EXPL_TYPE,MANDATORY_EXPL_TYPE,
2885 CONNECTOR_EXPL_TYPE,MINMAX_EXPL_TYPE) THEN
2886
2887 IF v_minimum=1 AND v_maximum=1 AND
2888 v_ps_node_type<>CONNECTOR_TYPE THEN
2889 v_instantiable_flag:=MANDATORY_EXPL_TYPE;
2890 END IF;
2891
2892 IF v_minimum=0 AND v_maximum=1 AND
2893 v_ps_node_type<>CONNECTOR_TYPE THEN
2894 v_instantiable_flag:=OPTIONAL_EXPL_TYPE;
2895 END IF;
2896
2897 IF NOT(v_minimum=1 AND v_maximum=1) AND
2898 v_ps_node_type<>CONNECTOR_TYPE THEN
2899 v_instantiable_flag:=MINMAX_EXPL_TYPE;
2900 END IF;
2901
2902 IF v_ps_node_type=CONNECTOR_TYPE THEN
2906 UPDATE CZ_PS_NODES
2903 v_instantiable_flag:=CONNECTOR_EXPL_TYPE;
2904 END IF;
2905
2907 SET minimum=p_minimum,
2908 maximum=p_maximum,
2909 instantiable_flag=v_instantiable_flag
2910 WHERE ps_node_id=p_ps_node_id;
2911
2912 END IF;
2913
2914 --
2915 -- DEBUG ERROR CODE --
2916 --
2917 ERROR_CODE:=901;
2918
2919 v_expl_node_type := v_instantiable_flag;
2920
2921 --
2922 -- DEBUG ERROR CODE --
2923 --
2924 ERROR_CODE:=902;
2925
2926 --
2927 -- do check :
2928 -- does Node with ps_node_id=p_ps_node_id exist ? --
2929 --
2930 BEGIN
2931 v_node_Exist:=FALSE;
2932 SELECT model_ref_expl_id,parent_expl_node_id,ps_node_type,virtual_flag,expl_node_type
2933 INTO v_model_ref_expl_id,v_parent_expl_node_id,v_ps_node_type,v_virtual_flag,v_curr_expl_node_type
2934 FROM CZ_MODEL_REF_EXPLS
2935 WHERE ((component_id=p_ps_node_id AND referring_node_id IS NULL) OR
2936 referring_node_id=p_ps_node_id)
2937 AND model_id=p_model_id AND deleted_flag=NO_FLAG AND ROWNUM<2;
2938 v_node_Exist:=TRUE;
2939 EXCEPTION
2940 WHEN NO_DATA_FOUND THEN
2941 NULL;
2942 END;
2943
2944 --
2945 -- this is "Root Node" case --
2946 --
2947 IF p_ps_node_id=p_model_id THEN
2948 IF v_node_Exist=FALSE THEN
2949 --
2950 -- DEBUG ERROR CODE --
2951 --
2952 ERROR_CODE:=903;
2953
2954 --
2955 -- add root node
2956 --
2957 add_root_Model_record(p_ps_node_id,v_ps_node_type);
2958
2959 UPDATE CZ_PS_NODES
2960 SET component_id=p_model_id,
2961 minimum=p_minimum,
2962 maximum=p_maximum,
2963 instantiable_flag=MANDATORY_EXPL_TYPE
2964 WHERE devl_project_id=p_model_id AND
2965 parent_id is null AND deleted_flag='0';
2966
2967 --
2968 -- DEBUG ERROR CODE --
2969 --
2970 ERROR_CODE:=904;
2971
2972 END IF;
2973 GOTO FINAL_SECTION;
2974 END IF;
2975
2976 --
2977 -- case when we have Min=1 and Max=1 for not Connectors/References nodes
2978 -- and this node with ps_node_id=p_ps_node_id does not
2979 -- exist in CZ_MODEL_REF_EXPLS table
2980 -- and no Numeric Rules which contribute to Min/Max are specified
2981 -- in this case we just need to exit from program
2982 --
2983 IF (v_expl_node_type=MANDATORY_EXPL_TYPE) AND (v_ps_node_type NOT IN(REFERENCE_TYPE,CONNECTOR_TYPE))
2984 AND v_node_Exist=FALSE THEN
2985 GOTO FINAL_SECTION;
2986 END IF;
2987
2988 IF v_expl_node_type IN(OPTIONAL_EXPL_TYPE,MINMAX_EXPL_TYPE) THEN
2989 p_out_virtual_flag:=NO_FLAG;
2990 ELSE
2991 p_out_virtual_flag:=YES_FLAG;
2992 END IF;
2993
2994 UPDATE CZ_PS_NODES
2995 SET virtual_flag=p_out_virtual_flag,
2996 minimum=p_minimum,
2997 maximum=p_maximum
2998 WHERE ps_node_id=p_ps_node_id;
2999
3000 IF SQL%ROWCOUNT>0 THEN
3001
3002 IF (v_ps_node_type NOT IN(REFERENCE_TYPE,CONNECTOR_TYPE)) THEN
3003 UPDATE CZ_MODEL_REF_EXPLS
3004 SET virtual_flag=p_out_virtual_flag,
3005 expl_node_type=v_expl_node_type
3006 WHERE component_id=p_ps_node_id AND deleted_flag=NO_FLAG;
3007 ELSE
3008 UPDATE CZ_MODEL_REF_EXPLS
3009 SET virtual_flag=p_out_virtual_flag,
3010 expl_node_type=v_expl_node_type
3011 WHERE referring_node_id=p_ps_node_id AND deleted_flag=NO_FLAG;
3012 END IF;
3013
3014 END IF;
3015
3016 IF v_ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) AND v_node_Exist=FALSE THEN
3017 --
3018 -- find "Models Chain" that contains
3019 -- the given model ( CZ_DEVL_PROJECTS.devl_project_id=p_model_id )
3020 -- t_chain array will store Model Ids of this chain
3021 --
3022 t_chain.DELETE;t_projectCache.DELETE;
3023 populate_chain(p_model_id);
3024
3025 add_Reference(p_ps_node_id,p_reference_id,p_model_id,
3026 p_out_virtual_flag,p_out_err,v_ps_node_type,v_expl_node_type);
3027
3028 GOTO FINAL_SECTION;
3029 END IF;
3030
3031 --
3032 -- this means UPDATE from (n,m) to (1,1)
3033 --
3034 IF p_out_virtual_flag=YES_FLAG AND
3035 (v_ps_node_type NOT IN(REFERENCE_TYPE,CONNECTOR_TYPE)) AND v_node_Exist THEN
3036
3037 FOR x IN (SELECT model_ref_expl_id,parent_expl_node_id,model_id FROM CZ_MODEL_REF_EXPLS a
3038 WHERE component_id=p_ps_node_id AND deleted_flag=NO_FLAG AND
3039 model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
3040 WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG))
3041 LOOP
3042 BEGIN
3043 IF x.model_id=p_model_id THEN
3044 v_del_ref_id:=x.model_ref_expl_id;
3045 END IF;
3046
3047 --
3048 -- DEBUG ERROR CODE --
3049 --
3053 START WITH model_ref_expl_id=x.model_ref_expl_id AND deleted_flag=NO_FLAG
3050 ERROR_CODE:=905;
3051
3052 FOR y IN (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
3054 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND
3055 PRIOR deleted_flag=NO_FLAG)
3056 LOOP
3057 UPDATE CZ_MODEL_REF_EXPLS SET node_depth=node_depth-1
3058 WHERE model_ref_expl_id=y.model_ref_expl_id;
3059 END LOOP;
3060
3061 --
3062 -- DEBUG ERROR CODE --
3063 --
3064 ERROR_CODE:=906;
3065
3066 UPDATE CZ_MODEL_REF_EXPLS SET parent_expl_node_id=x.parent_expl_node_id
3067 WHERE parent_expl_node_id=x.model_ref_expl_id
3068 AND model_id=x.model_id AND deleted_flag=NO_FLAG;
3069
3070 EXCEPTION
3071 WHEN NO_DATA_FOUND THEN
3072 NULL;
3073 END;
3074 END LOOP;
3075
3076 --
3077 -- DEBUG ERROR CODE --
3078 --
3079 ERROR_CODE:=907;
3080
3081 delete_Node_(p_ps_node_id,COMPONENT_TYPE, '1');
3082
3083 --
3084 -- DEBUG ERROR CODE --
3085 --
3086 ERROR_CODE:=908;
3087
3088 set_Expls(p_model_id,p_ps_node_id,MINUS_MODE,v_del_ref_id);
3089
3090 --
3091 -- DEBUG ERROR CODE --
3092 --
3093 ERROR_CODE:=909;
3094
3095 refresh_UI_Expl_Ids(p_ps_node_id => p_ps_node_id,
3096 p_component_id => v_component_id,
3097 p_model_id => p_model_id,
3098 p_old_expl_id => v_model_ref_expl_id,
3099 p_new_expl_id => v_parent_expl_node_id);
3100
3101 GOTO FINAL_SECTION;
3102 END IF;
3103
3104 --
3105 -- this means UPDATE from (1,1) to (n,m)
3106 --
3107 IF p_out_virtual_flag=NO_FLAG AND (v_ps_node_type NOT IN(REFERENCE_TYPE,CONNECTOR_TYPE))
3108 AND v_node_Exist=FALSE THEN
3109
3110 --
3111 -- find nearest non-virtual node above the target node --
3112 --
3113 get_Node_Up(p_ps_node_id,p_model_id,v_up_id,
3114 v_up_expl_id,v_up_level);
3115
3116 v_expl_id:=allocate_Expl_Id;
3117 --
3118 -- DEBUG ERROR CODE --
3119 --
3120 ERROR_CODE:=910;
3121
3122 INSERT INTO CZ_MODEL_REF_EXPLS
3123 (model_ref_expl_id,
3124 parent_expl_node_id,
3125 referring_node_id,
3126 model_id,
3127 component_id,
3128 ps_node_type,
3129 virtual_flag,
3130 node_depth,
3131 expl_node_type,
3132 deleted_flag)
3133 VALUES
3134 (v_expl_id,
3135 v_up_expl_id,
3136 NULL,
3137 p_model_id,
3138 p_ps_node_id,
3139 v_ps_node_type,
3140 p_out_virtual_flag,
3141 v_up_level+1,
3142 v_expl_node_type,
3143 NO_FLAG);
3144
3145 --
3146 -- DEBUG ERROR CODE --
3147 --
3148 ERROR_CODE:=911;
3149
3150 --
3151 -- reset childs nodes
3152 --
3153 t_childs.DELETE;
3154
3155 SELECT ps_node_id BULK COLLECT INTO t_childs FROM CZ_PS_NODES
3156 WHERE devl_project_id=p_model_id AND ps_node_id<>p_ps_node_id AND
3157 (
3158 (ps_node_type in(COMPONENT_TYPE,PRODUCT_TYPE) AND ps_node_id IN ---- fix for bug #3161931
3159 (SELECT component_id FROM CZ_MODEL_REF_EXPLS
3160 WHERE model_id=p_model_id AND deleted_flag=NO_FLAG AND parent_expl_node_id=v_up_expl_id AND
3161 component_id IN(SELECT ps_node_id FROM CZ_PS_NODES
3162 START WITH ps_node_id=p_ps_node_id AND deleted_flag=NO_FLAG
3163 CONNECT BY PRIOR ps_node_id=parent_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG)
3164 )
3165 )
3166 OR
3167 (ps_node_type in(REFERENCE_TYPE,CONNECTOR_TYPE) AND ps_node_id IN ---- fix for bug #3161931
3168 (SELECT referring_node_id FROM CZ_MODEL_REF_EXPLS
3169 WHERE model_id=p_model_id AND deleted_flag=NO_FLAG AND parent_expl_node_id=v_up_expl_id AND
3170 referring_node_id IN(SELECT ps_node_id FROM CZ_PS_NODES
3171 START WITH ps_node_id=p_ps_node_id AND deleted_flag=NO_FLAG
3172 CONNECT BY PRIOR ps_node_id=parent_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG)
3173 )
3174 )
3175 ) AND deleted_flag=NO_FLAG;
3176
3177 --
3178 -- DEBUG ERROR CODE --
3179 --
3180 ERROR_CODE:=912;
3181
3182 IF t_childs.COUNT>0 THEN
3183 FOR t IN t_childs.FIRST..t_childs.LAST
3184 LOOP
3185 t_child_nodes.DELETE;
3186 v_ps_node_id:= t_childs(t);
3187 UPDATE CZ_MODEL_REF_EXPLS
3188 SET parent_expl_node_id=v_expl_id
3189 WHERE parent_expl_node_id=v_up_expl_id
3190 AND model_id=p_model_id AND
3194 RETURNING model_ref_expl_id BULK COLLECT INTO t_child_nodes ;
3191 ((ps_node_type IN(PRODUCT_TYPE,COMPONENT_TYPE,BOM_MODEL_TYPE) AND component_id=v_ps_node_id)
3192 OR (ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) AND referring_node_id=v_ps_node_id))
3193 AND deleted_flag=NO_FLAG
3195
3196 IF t_child_nodes.COUNT>0 THEN
3197
3198 --
3199 -- DEBUG ERROR CODE --
3200 --
3201 ERROR_CODE:=913;
3202
3203 FORALL h IN t_child_nodes.FIRST..t_child_nodes.LAST
3204 UPDATE CZ_MODEL_REF_EXPLS
3205 SET node_depth=node_depth+1
3206 WHERE model_ref_expl_id IN
3207 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
3208 START WITH model_ref_expl_id=t_child_nodes(h) AND deleted_flag=NO_FLAG
3209 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG
3210 AND PRIOR deleted_flag=NO_FLAG);
3211
3212 --
3213 -- DEBUG ERROR CODE --
3214 --
3215 ERROR_CODE:=914;
3216
3217 END IF;
3218 END LOOP; -- end of loop for t in t_childs.First..t_childs.Last --
3219 END IF; -- end of t_childs.Count>0 case --
3220
3221 --
3222 -- DEBUG ERROR CODE --
3223 --
3224 ERROR_CODE:=915;
3225
3226 --
3227 -- find "Models Chain" that contains
3228 -- the given model ( CZ_DEVL_PROJECTS.devl_project_id=v_ref_model_id )
3229 -- t_chain array will store Model Ids of this chain
3230 --
3231 t_chain.DELETE;t_projectCache.DELETE;
3232 populate_chain(p_model_id);
3233
3234 --
3235 -- DEBUG ERROR CODE --
3236 --
3237 ERROR_CODE:=916;
3238
3239 populate_tree
3240 (v_up_expl_id,v_expl_id,p_ps_node_id,v_ps_node_type,
3241 v_expl_node_type,p_model_id,t_childs);
3242
3243 --
3244 -- DEBUG ERROR CODE --
3245 --
3246 ERROR_CODE:=917;
3247
3248 set_Expls(p_model_id,p_ps_node_id, PLUS_MODE);
3249
3250 SELECT MIN(model_ref_expl_id) INTO v_comp_expl_id
3251 FROM CZ_MODEL_REF_EXPLS
3252 WHERE model_id=p_model_id AND component_id=v_component_id AND
3253 deleted_flag='0';
3254
3255 refresh_UI_Expl_Ids(p_ps_node_id => p_ps_node_id,
3256 p_component_id => v_component_id,
3257 p_model_id => p_model_id,
3258 p_old_expl_id => v_comp_expl_id,
3259 p_new_expl_id => v_expl_id);
3260
3261 END IF;
3262
3263 EXCEPTION
3264 WHEN OTHERS THEN
3265 p_out_err:=m_RUN_ID;
3266 LOG_REPORT('check_Node','Error : ps_node_id='||
3267 TO_CHAR(p_ps_node_id)||' < '||ERROR_CODE||' > : '||SQLERRM);
3268 END;
3269
3270
3271 <<FINAL_SECTION>>
3272 --
3273 -- fix child_model_expl_id's if they are wrong
3274 -- ( recursion is used )
3275 -- this code was commented out because of
3276 -- perfomance problem
3277 --
3278 --update_child_nodes(p_model_id);
3279
3280 --
3281 -- fix node_depth's if they are wrong
3282 -- ( recursion is used )
3283 --
3284 --
3285 -- Due to BOM Refresh performance problem reported in bug 9446997
3286 -- conditionalize call update_Node_Depth. update_Node_Depth need to
3287 -- be called explicitly at then end of check_node call
3288 -- if p_skip_upd_nod_dep = YES_FLAG
3289
3290 IF p_skip_upd_nod_dep = YES_FLAG THEN
3291 t_upd_node_depth_models(p_model_id) := p_model_id;
3292 ELSE
3293 update_Node_Depth(p_model_id);
3294 END IF;
3295
3296
3297 --
3298 -- delete duplicates
3299 -- like
3300 -- Reference-n
3301 -- |---------Reference-n
3302 --
3303 --delete_duplicates(p_model_id);
3304
3305 populate_COMPONENT_ID(p_model_id);
3306
3307 NULL;
3308 END check_Node;
3309
3310 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
3311 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
3312
3313 PROCEDURE delete_Node
3314 (p_ps_node_id IN INTEGER,
3315 p_ps_node_type IN INTEGER,
3316 p_out_err OUT NOCOPY INTEGER,
3317 p_del_logically IN VARCHAR2 -- DEFAULT '1'
3318 ) IS
3319
3320 v_component_id CZ_PS_NODES.component_id%TYPE;
3321 v_virtual_flag CZ_PS_NODES.virtual_flag%TYPE;
3322 v_ps_node_type CZ_PS_NODES.ps_node_type%TYPE;
3323
3324 BEGIN
3325 Initialize;
3326 p_out_err:=0;
3327
3328 IF p_ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) THEN
3329
3330 FOR i IN (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS a
3331 WHERE referring_node_id=p_ps_node_id AND deleted_flag=NO_FLAG AND
3332 model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
3333 WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG))
3334 LOOP
3335 --
3336 -- DEBUG ERROR CODE --
3337 --
3341
3338 ERROR_CODE:=1001;
3339
3340 delete_it(i.model_ref_expl_id, YES_FLAG);
3342 --
3343 -- DEBUG ERROR CODE --
3344 --
3345 ERROR_CODE:=1002;
3346
3347 END LOOP;
3348 ELSE
3349 SELECT component_id,NVL(virtual_flag,'1') INTO v_component_id,v_virtual_flag FROM CZ_PS_NODES
3350 WHERE ps_node_id=p_ps_node_id;
3351
3352 IF v_virtual_flag='0' THEN
3353 FOR i IN (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS a
3354 WHERE component_id=p_ps_node_id AND deleted_flag=NO_FLAG AND
3355 model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
3356 WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG))
3357 LOOP
3358 --
3359 -- DEBUG ERROR CODE --
3360 --
3361 ERROR_CODE:=1003;
3362
3363 delete_it(i.model_ref_expl_id, YES_FLAG);
3364
3365 --
3366 -- DEBUG ERROR CODE --
3367 --
3368 ERROR_CODE:=1004;
3369
3370 END LOOP;
3371
3372 ELSE
3373
3374 FOR i IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS a
3375 WHERE parent_expl_node_id IN
3376 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
3377 WHERE model_id=a.model_id AND component_id=v_component_id AND deleted_flag='0')
3378 AND (component_id IN
3379 (SELECT ps_node_id FROM CZ_PS_NODES
3380 START WITH ps_node_id=p_ps_node_id
3381 CONNECT BY PRIOR ps_node_id=parent_id)
3382 OR
3383 referring_node_id IN
3384 (SELECT ps_node_id FROM cz_ps_nodes
3385 START WITH ps_node_id=p_ps_node_id
3386 CONNECT BY PRIOR ps_node_id=parent_id)
3387 )
3388 )
3389 LOOP
3390 --
3391 -- DEBUG ERROR CODE --
3392 --
3393 ERROR_CODE:=1005;
3394
3395 delete_it(i.model_ref_expl_id, YES_FLAG);
3396
3397 --
3398 -- DEBUG ERROR CODE --
3399 --
3400 ERROR_CODE:=1006;
3401 END LOOP;
3402
3403 END IF;
3404
3405 END IF;
3406
3407 EXCEPTION
3408 WHEN OTHERS THEN
3409 p_out_err:=m_RUN_ID;
3410 LOG_REPORT('delete_Node',
3411 'Error : ps_node_id='||TO_CHAR(p_ps_node_id)||' < '||ERROR_CODE||' > : '||SQLERRM);
3412 END delete_Node;
3413
3414 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
3415 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
3416
3417 PROCEDURE update_levels
3418 (p_expl_id IN INTEGER,
3419 p_level IN INTEGER,
3420 p_tree_level IN INTEGER) IS
3421
3422 BEGIN
3423 FOR i IN(SELECT model_ref_expl_id,parent_expl_node_id FROM CZ_MODEL_REF_EXPLS
3424 WHERE parent_expl_node_id=p_expl_id AND deleted_flag=NO_FLAG)
3425 LOOP
3426 UPDATE CZ_MODEL_REF_EXPLS SET node_depth=p_level+p_tree_level
3427 WHERE model_ref_expl_id=i.model_ref_expl_id;
3428
3429 --
3430 -- DEBUG ERROR CODE --
3431 --
3432 ERROR_CODE:=1100;
3433
3434 update_levels(i.model_ref_expl_id,p_level+p_tree_level,p_tree_level+1);
3435
3436 --
3437 -- DEBUG ERROR CODE --
3438 --
3439 ERROR_CODE:=1101;
3440
3441 END LOOP;
3442 END;
3443
3444 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
3445 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
3446
3447 --
3448 -- this is general recursive procedure to reconstruct Parent/Childs
3449 --
3450 PROCEDURE reConstruct
3451 (p_project_id IN INTEGER,
3452 p_expl_root_id IN INTEGER,
3453 p_from_expl_id IN INTEGER,
3454 p_up_expl_id IN INTEGER,
3455 p_model_ref_expl IN IntArray,
3456 p_levels IN IntArray) IS
3457
3458 t_levels IntArray;
3459 t_levels_ IntArray;
3460 t_refs IntArray;
3461 t_models IntArray;
3462 t_model_ref_expl IntArray;
3463 t_model_ref_expl_ IntArray;
3464 v_expl_root_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
3465 v_from_up_expl_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
3466 v_up_expl_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
3467 v_from_expl_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
3468 v_up_level CZ_MODEL_REF_EXPLS.node_depth%TYPE;
3469 STOP_IT EXCEPTION;
3470
3471 BEGIN
3472
3473 --
3474 -- DEBUG ERROR CODE --
3475 --
3476 ERROR_CODE:=1200;
3477
3478 --
3479 -- find nodes of parent Models which corresponds with
3480 -- a moved node in source Model
3481 --
3482 SELECT model_ref_expl_id,model_id
3483 BULK COLLECT INTO t_refs,t_models
3487 model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
3484 FROM CZ_MODEL_REF_EXPLS a
3485 WHERE child_model_expl_id=p_expl_root_id
3486 AND ps_node_type IN(REFERENCE_TYPE,CONNECTOR_TYPE) AND deleted_flag=NO_FLAG AND
3488 WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG);
3489
3490 --
3491 -- DEBUG ERROR CODE --
3492 --
3493 ERROR_CODE:=1201;
3494
3495 IF t_refs.COUNT=0 THEN
3496 RAISE STOP_IT;
3497 END IF;
3498
3499 FOR i IN t_refs.FIRST..t_refs.LAST
3500 LOOP
3501 --
3502 -- find a root node of subtree in the current parent Model
3503 -- associated with a child referenced Model
3504 --
3505 v_expl_root_id:=t_refs(i);
3506
3507 --
3508 -- DEBUG ERROR CODE --
3509 --
3510 ERROR_CODE:=1202;
3511
3512 SELECT model_ref_expl_id INTO v_from_up_expl_id FROM CZ_MODEL_REF_EXPLS a
3513 WHERE child_model_expl_id=p_from_expl_id AND model_ref_expl_id
3514 IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
3515 START WITH model_ref_expl_id=t_refs(i) AND deleted_flag=NO_FLAG
3516 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND
3517 deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG)
3518 AND
3519 model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
3520 WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG);
3521
3522 --
3523 -- DEBUG ERROR CODE --
3524 --
3525 ERROR_CODE:=1203;
3526
3527 SELECT model_ref_expl_id,node_depth
3528 INTO v_up_expl_id,v_up_level FROM CZ_MODEL_REF_EXPLS a
3529 WHERE child_model_expl_id=p_up_expl_id AND model_ref_expl_id
3530 IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
3531 START WITH model_ref_expl_id=t_refs(i) AND deleted_flag=NO_FLAG
3532 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND PRIOR deleted_flag=NO_FLAG)
3533 AND
3534 model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
3535 WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG);
3536
3537 --
3538 -- DEBUG ERROR CODE --
3539 --
3540 ERROR_CODE:=1204;
3541
3542 FOR k IN p_model_ref_expl.FIRST..p_model_ref_expl.LAST
3543 LOOP
3544 UPDATE CZ_MODEL_REF_EXPLS
3545 SET parent_expl_node_id=v_up_expl_id
3546 WHERE model_ref_expl_id
3547 IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
3548 WHERE (parent_expl_node_id=v_from_up_expl_id OR model_ref_expl_id=v_from_up_expl_id)
3549 AND child_model_expl_id=p_model_ref_expl(k)
3550 AND deleted_flag=NO_FLAG);
3551
3552 END LOOP;
3553
3554 --
3555 -- DEBUG ERROR CODE --
3556 --
3557 ERROR_CODE:=1205;
3558
3559 t_old_expl_ids(t_old_expl_ids.COUNT+1):=v_from_up_expl_id;
3560 t_new_expl_ids(t_new_expl_ids.COUNT+1):=v_up_expl_id;
3561
3562 FOR k IN p_model_ref_expl.FIRST..p_model_ref_expl.LAST
3563 LOOP
3564
3565 --
3566 -- DEBUG ERROR CODE --
3567 --
3568 ERROR_CODE:=1206;
3569
3570 --
3571 -- recreate array of child_model_expl_id-s --
3572 --
3573 SELECT model_ref_expl_id,node_depth
3574 BULK COLLECT INTO t_model_ref_expl_,t_levels_
3575 FROM CZ_MODEL_REF_EXPLS
3576 WHERE parent_expl_node_id=v_up_expl_id
3577 AND child_model_expl_id=p_model_ref_expl(k)
3578 AND deleted_flag=NO_FLAG;
3579
3580 --
3581 -- DEBUG ERROR CODE --
3582 --
3583 ERROR_CODE:=1207;
3584
3585 END LOOP;
3586
3587
3588 IF t_model_ref_expl_.COUNT>0 THEN
3589 --
3590 -- DEBUG ERROR CODE --
3591 --
3592 ERROR_CODE:=1208;
3593
3594 update_levels(v_up_expl_id,v_up_level,1);
3595 reConstruct(p_project_id =>p_project_id,
3596 p_expl_root_id =>v_expl_root_id,
3597 p_from_expl_id =>v_from_up_expl_id,
3598 p_up_expl_id =>v_up_expl_id,
3599 p_model_ref_expl=>t_model_ref_expl_,
3600 p_levels =>t_levels_ );
3601
3602 --
3603 -- DEBUG ERROR CODE --
3604 --
3605 ERROR_CODE:=1209;
3606
3607 END IF;
3608 END LOOP;
3609
3610 EXCEPTION
3611 WHEN STOP_IT THEN
3612 NULL;
3613 WHEN OTHERS THEN
3614 LOG_REPORT('reConstruct','ERROR CODE :'||ERROR_CODE||' : '||SQLERRM);
3615 END reConstruct;
3616
3617 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
3618 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
3619
3620 PROCEDURE move_Node
3621 (p_from_ps_node_id IN INTEGER,
3622 p_to_ps_node_id IN INTEGER,
3623 p_project_id IN INTEGER,
3624 p_out_err OUT NOCOPY INTEGER) IS
3625
3626 t_levels IntArray;
3627 t_refs IntArray;
3628 t_models IntArray;
3629 t_model_ref_expl IntArray;
3630 v_up_id INTEGER;
3634 v_down_expl_id INTEGER;
3631 v_down_id INTEGER;
3632 v_up_expl_id INTEGER;
3633 v_expr_node_id INTEGER;
3635 v_level INTEGER;
3636 v_up_level INTEGER;
3637 v_from_up_id INTEGER;
3638 v_from_up_expl_id INTEGER;
3639 v_from_up_level INTEGER;
3640 v_down_level INTEGER;
3641 v_delta_level INTEGER;
3642 v_expl_root_id INTEGER;
3643 var_subroot_id INTEGER;
3644 var_subroot_level INTEGER;
3645 v_temp INTEGER;
3646 err INTEGER;
3647 v_ps_node_type CZ_PS_NODES.ps_node_type%TYPE;
3648 v_virtual_flag CZ_PS_NODES.virtual_flag%TYPE;
3649
3650 v_ref_up_expl_id CZ_MODEL_REF_EXPLS.model_ref_expl_id%TYPE;
3651 v_ref_up_level CZ_MODEL_REF_EXPLS.node_depth%TYPE;
3652 v_contains_non_virt BOOLEAN:=FALSE;
3653 v_circularity_exists BOOLEAN:=FALSE;
3654 v_comp_expl_id NUMBER;
3655 v_model_ref_expl_id NUMBER;
3656 v_component_id NUMBER;
3657 v_parent_id NUMBER;
3658 v_ndebug NUMBER;
3659
3660 BEGIN
3661
3662 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3663 v_ndebug := 0;
3664 cz_utils.log_report('CZ_REFS', 'move_Node', v_ndebug,
3665 'Starting CZ_REFS.move_Node for model node with ps_node_id='||TO_CHAR(p_from_ps_node_id)||
3666 ' current time : '||TO_CHAR(SYSDATE,'DD-MM-YYYY HH24:MI:SS'),
3667 fnd_log.LEVEL_PROCEDURE);
3668 cz_utils.log_report('CZ_REFS', 'move_Node', v_ndebug,
3669 'Parameters : '||
3670 'p_from_ps_node_id = '||TO_CHAR(p_from_ps_node_id)||
3671 'p_to_ps_node_id = '||TO_CHAR(p_to_ps_node_id)||
3672 'p_project_id = '||TO_CHAR(p_project_id)
3673 ,fnd_log.LEVEL_PROCEDURE);
3674 END IF;
3675
3676 BEGIN -- main try/catch block --
3677 Initialize;
3678 p_out_err:=0;
3679 t_old_expl_ids.DELETE;
3680 t_new_expl_ids.DELETE;
3681
3682 --
3683 -- DEBUG ERROR CODE --
3684 --
3685 ERROR_CODE:=1300;
3686
3687 SELECT ps_node_type,NVL(virtual_flag,YES_FLAG),parent_id,component_id
3688 INTO v_ps_node_type,v_virtual_flag,v_parent_id,v_component_id
3689 FROM CZ_PS_NODES
3690 WHERE ps_node_id=p_from_ps_node_id;
3691
3692 LOG_REPORT('*','p_from_ps_node_id='||to_char(p_from_ps_node_id)||
3693 ' v_parent_id='||to_char(v_parent_id));
3694
3695 --
3696 -- DEBUG ERROR CODE --
3697 --
3698 ERROR_CODE:=1301;
3699
3700 --
3701 -- find nearest non-virtual node above the target node --
3702 --
3703 get_Node_Up(p_to_ps_node_id,p_project_id,v_up_id,
3704 v_up_expl_id,v_up_level);
3705
3706
3707 --
3708 -- DEBUG ERROR CODE --
3709 --
3710 ERROR_CODE:=1302;
3711
3712 IF v_ps_node_type IN (REFERENCE_TYPE,CONNECTOR_TYPE) THEN
3713
3714 v_from_up_id:=p_from_ps_node_id;
3715 SELECT model_ref_expl_id,node_depth
3716 INTO v_from_up_expl_id,v_from_up_level
3717 FROM CZ_MODEL_REF_EXPLS
3718 WHERE model_id=p_project_id AND referring_node_id=p_from_ps_node_id
3719 AND deleted_flag=NO_FLAG;
3720
3721 BEGIN
3722 SELECT model_ref_expl_id,node_depth
3723 INTO v_ref_up_expl_id,v_ref_up_level
3724 FROM CZ_MODEL_REF_EXPLS
3725 WHERE component_id=v_up_id
3726 AND model_id=p_project_id AND deleted_flag=NO_FLAG;
3727
3728 UPDATE CZ_MODEL_REF_EXPLS
3729 SET parent_expl_node_id=v_ref_up_expl_id
3730 WHERE model_ref_expl_id=v_from_up_expl_id
3731 AND deleted_flag=NO_FLAG;
3732
3733 UPDATE CZ_MODEL_REF_EXPLS SET node_depth=node_depth-v_from_up_level+v_ref_up_level+1
3734 WHERE model_ref_expl_id IN
3735 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
3736 START WITH model_ref_expl_id=v_from_up_expl_id AND deleted_flag=NO_FLAG
3737 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG
3738 AND PRIOR deleted_flag=NO_FLAG);
3739 EXCEPTION
3740 WHEN OTHERS THEN
3741 p_out_err:=m_RUN_ID;
3742 LOG_REPORT('move_Node','ERROR CODE :'||ERROR_CODE||' : '||SQLERRM);
3743 GOTO FINAL_SECTION;
3744 END;
3745
3746 t_chain.DELETE;t_projectCache.DELETE;
3747 populate_chain(p_project_id);
3748
3749 IF t_chain.COUNT>0 THEN
3750 FOR i IN t_chain.FIRST..t_chain.LAST
3751 LOOP
3752 FOR k IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS WHERE
3753 model_id=t_chain(i).model_id AND ps_node_type IN(REFERENCE_TYPE,264)
3754 AND component_id=p_project_id AND deleted_flag=NO_FLAG)
3755 LOOP
3756 BEGIN
3757 SELECT model_ref_expl_id,node_depth
3758 INTO v_ref_up_expl_id,v_ref_up_level
3759 FROM CZ_MODEL_REF_EXPLS
3760 WHERE component_id=v_up_id
3761 AND model_ref_expl_id IN
3762 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
3763 START WITH model_ref_expl_id=k.model_ref_expl_id AND deleted_flag=NO_FLAG
3767
3764 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG
3765 AND PRIOR deleted_flag=NO_FLAG);
3766
3768 UPDATE CZ_MODEL_REF_EXPLS
3769 SET parent_expl_node_id=v_ref_up_expl_id
3770 WHERE model_ref_expl_id IN
3771 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS a
3772 WHERE referring_node_id=p_from_ps_node_id
3773 AND model_ref_expl_id IN
3774 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
3775 START WITH model_ref_expl_id=k.model_ref_expl_id AND deleted_flag=NO_FLAG
3776 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG
3777 AND PRIOR deleted_flag=NO_FLAG) AND
3778 model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
3779 WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG))
3780 RETURNING model_ref_expl_id,node_depth INTO var_subroot_id,var_subroot_level ;
3781
3782 UPDATE CZ_MODEL_REF_EXPLS SET node_depth=node_depth-var_subroot_level+v_ref_up_level+1
3783 WHERE model_ref_expl_id IN
3784 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
3785 START WITH model_ref_expl_id=var_subroot_id AND deleted_flag=NO_FLAG
3786 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND
3787 PRIOR deleted_flag=NO_FLAG);
3788
3789 EXCEPTION
3790 WHEN OTHERS THEN
3791 p_out_err:=m_RUN_ID;
3792 LOG_REPORT('move_Node','ERROR CODE :'||ERROR_CODE||' : '||SQLERRM);
3793 GOTO FINAL_SECTION;
3794 END;
3795 END LOOP;
3796 END LOOP;
3797 END IF;
3798
3799 GOTO FINAL_SECTION;
3800
3801 ELSE
3802
3803 IF v_virtual_flag='1' THEN
3804
3805 get_from_Node_Up(p_from_ps_node_id,p_project_id,v_from_up_id,
3806 v_from_up_expl_id,v_from_up_level);
3807
3808 --
3809 -- DEBUG ERROR CODE --
3810 --
3811 ERROR_CODE:=1303;
3812
3813 SELECT model_ref_expl_id
3814 BULK COLLECT INTO t_model_ref_expl
3815 FROM CZ_MODEL_REF_EXPLS
3816 WHERE parent_expl_node_id=v_from_up_expl_id AND model_id=p_project_id AND
3817 component_id IN(SELECT ps_node_id FROM CZ_PS_NODES
3818 START WITH ps_node_id=p_from_ps_node_id AND deleted_flag=NO_FLAG
3819 CONNECT BY PRIOR ps_node_id=parent_id AND deleted_flag=NO_FLAG
3820 AND PRIOR deleted_flag=NO_FLAG);
3821
3822 --
3823 -- DEBUG ERROR CODE --
3824 --
3825 ERROR_CODE:=1304;
3826
3827 ELSE
3828
3829 --
3830 -- DEBUG ERROR CODE --
3831 --
3832 ERROR_CODE:=1305;
3833
3834 v_from_up_id:=p_from_ps_node_id;
3835 SELECT model_ref_expl_id,node_depth
3836 INTO v_from_up_expl_id,v_from_up_level
3837 FROM CZ_MODEL_REF_EXPLS
3838 WHERE model_id=p_project_id AND component_id=v_from_up_id
3839 AND ps_node_type NOT IN(REFERENCE_TYPE,CONNECTOR_TYPE) AND child_model_expl_id IS NULL AND
3840 deleted_flag=NO_FLAG;
3841 t_model_ref_expl(1):=v_from_up_expl_id;
3842
3843 --
3844 -- DEBUG ERROR CODE --
3845 --
3846 ERROR_CODE:=1306;
3847
3848 END IF;
3849 END IF;
3850
3851 IF t_model_ref_expl.COUNT>0 THEN
3852
3853 FOR i IN t_model_ref_expl.FIRST..t_model_ref_expl.LAST
3854 LOOP
3855
3856 --
3857 -- DEBUG ERROR CODE --
3858 --
3859 ERROR_CODE:=1307;
3860
3861 UPDATE CZ_MODEL_REF_EXPLS SET parent_expl_node_id=v_up_expl_id
3862 WHERE model_ref_expl_id=t_model_ref_expl(i)
3863 AND model_id=p_project_id;
3864
3865 --
3866 -- DEBUG ERROR CODE --
3867 --
3868 ERROR_CODE:=1308;
3869
3870 UPDATE CZ_MODEL_REF_EXPLS SET node_depth=node_depth-v_from_up_level+v_up_level+1
3871 WHERE model_ref_expl_id IN
3872 (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
3873 START WITH model_ref_expl_id=t_model_ref_expl(i) AND deleted_flag=NO_FLAG
3874 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG AND
3875 PRIOR deleted_flag=NO_FLAG);
3876
3877 --
3878 -- DEBUG ERROR CODE --
3879 --
3880 ERROR_CODE:=1309;
3881
3882 END LOOP;
3883
3884 END IF;
3885
3886 --
3887 -- DEBUG ERROR CODE --
3888 --
3889 ERROR_CODE:=1310;
3890
3891 SELECT model_ref_expl_id INTO v_expl_root_id
3892 FROM CZ_MODEL_REF_EXPLS WHERE model_id=p_project_id AND component_id=p_project_id
3893 AND parent_expl_node_id IS NULL AND deleted_flag=NO_FLAG;
3894
3895 --
3896 -- DEBUG ERROR CODE --
3900 IF t_model_ref_expl.COUNT>0 THEN
3897 --
3898 ERROR_CODE:=1311;
3899
3901 FOR t IN t_model_ref_expl.FIRST..t_model_ref_expl.LAST
3902 LOOP
3903
3904 --
3905 -- DEBUG ERROR CODE --
3906 --
3907 ERROR_CODE:=1312;
3908
3909 SELECT node_depth INTO v_temp FROM CZ_MODEL_REF_EXPLS
3910 WHERE model_id=p_project_id AND model_ref_expl_id=t_model_ref_expl(t);
3911 t_levels(t_levels.COUNT+1):=v_temp;
3912
3913 --
3914 -- DEBUG ERROR CODE --
3915 --
3916 ERROR_CODE:=1313;
3917
3918 END LOOP;
3919 END IF;
3920
3921 --
3922 -- DEBUG ERROR CODE --
3923 --
3924 ERROR_CODE:=1314;
3925
3926 t_old_expl_ids(t_old_expl_ids.COUNT+1):=v_from_up_expl_id;
3927
3928 t_new_expl_ids(t_new_expl_ids.COUNT+1):=v_up_expl_id;
3929
3930 reConstruct(p_project_id =>p_project_id,
3931 p_expl_root_id =>v_expl_root_id,
3932 p_from_expl_id =>v_from_up_expl_id,
3933 p_up_expl_id =>v_up_expl_id,
3934 p_model_ref_expl=>t_model_ref_expl,
3935 p_levels =>t_levels);
3936
3937 IF v_virtual_flag=YES_FLAG THEN
3938 update_Rules(p_from_ps_node_id);
3939
3940 SELECT MIN(model_ref_expl_id) INTO v_comp_expl_id
3941 FROM CZ_MODEL_REF_EXPLS
3942 WHERE model_id=p_project_id AND component_id=v_component_id AND
3943 deleted_flag='0';
3944
3945 SELECT MIN(model_ref_expl_id) INTO v_comp_expl_id
3946 FROM CZ_MODEL_REF_EXPLS
3947 WHERE model_id=p_project_id AND component_id=
3948 (SELECT component_id FROM CZ_PS_NODES WHERE ps_node_id=p_to_ps_node_id) AND
3949 deleted_flag='0';
3950
3951 refresh_UI_Expl_Ids(p_ps_node_id => p_from_ps_node_id,
3952 p_component_id => v_component_id,
3953 p_model_id => p_project_id,
3954 p_old_expl_id => v_model_ref_expl_id,
3955 p_new_expl_id => v_comp_expl_id);
3956
3957 END IF;
3958
3959 --
3960 -- DEBUG ERROR CODE --
3961 --
3962 ERROR_CODE:=1315;
3963
3964 EXCEPTION
3965 WHEN OTHERS THEN
3966 p_out_err:=m_RUN_ID;
3967 LOG_REPORT('move_Node','ERROR CODE :'||ERROR_CODE||' : '||SQLERRM);
3968 END;
3969
3970 <<FINAL_SECTION>>
3971 --
3972 -- fix child_model_expl_id's if they are wrong
3973 -- ( recursion is used )
3974 -- this code was commented out because of
3975 -- perfomance problem
3976 --
3977 update_child_nodes(p_project_id);
3978
3979 --
3980 -- fix node_depth's if they are wrong
3981 -- ( recursion is used )
3982 --
3983 update_Node_Depth(p_project_id);
3984
3985 --
3986 -- delete duplicates
3987 -- like
3988 -- Reference-n
3989 -- |---------Reference-n
3990 --
3991 -- delete_duplicates(p_project_id);
3992
3993 populate_COMPONENT_ID(p_project_id);
3994
3995 END move_Node;
3996
3997 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
3998 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
3999
4000 PROCEDURE CHECK_REF_REQUEST
4001 (p_refroot_model_id IN INTEGER,
4002 p_ref_parent_node_id IN INTEGER,
4003 p_ref_target_model_id IN INTEGER,
4004 p_out_status_code OUT NOCOPY INTEGER) IS
4005
4006 v_BOM_node1 CZ_PS_NODES.ps_node_id%TYPE;
4007 v_BOM_node2 CZ_PS_NODES.ps_node_id%TYPE;
4008 v_instanciable INTEGER;
4009
4010 BEGIN
4011 p_out_status_code:=0;
4012
4013 --
4014 -- DEBUG ERROR CODE --
4015 --
4016 ERROR_CODE:=1400;
4017
4018 BEGIN
4019 SELECT ps_node_id INTO v_BOM_node1 FROM CZ_PS_NODES
4020 WHERE devl_project_id=p_refroot_model_id AND ps_node_type=BOM_MODEL_TYPE
4021 AND deleted_flag=NO_FLAG AND rownum<2;
4022 EXCEPTION
4023 WHEN NO_DATA_FOUND THEN
4024 v_BOM_node1:=containsBOM(p_refroot_model_id,v_instanciable);
4025 END;
4026
4027 --
4028 -- DEBUG ERROR CODE --
4029 --
4030 ERROR_CODE:=1401;
4031
4032 v_BOM_node2:=containsBOM(p_ref_target_model_id,v_instanciable);
4033
4034 --
4035 -- DEBUG ERROR CODE --
4036 --
4037 ERROR_CODE:=1402;
4038
4039 IF v_BOM_node1>0 AND v_BOM_node2>0 THEN
4040 IF v_instanciable=1 THEN
4041 p_out_status_code:=1;
4042 ELSE
4043 p_out_status_code:=2;
4044 END IF;
4045 RETURN;
4046 END IF;
4047
4048 IF v_BOM_node2>0 THEN
4049 -- check models that reference the current model ( p_refroot_model_id )
4050 FOR n IN(SELECT DISTINCT model_id FROM CZ_MODEL_REF_EXPLS a
4051 WHERE component_id=p_refroot_model_id AND ps_node_type=REFERENCE_TYPE AND
4052 deleted_flag='0' AND
4053 EXISTS(SELECT NULL FROM CZ_RP_ENTRIES WHERE object_id=a.model_id AND
4057 p_out_status_code:=1;
4054 object_type='PRJ' AND deleted_flag='0'))
4055 LOOP
4056 IF containsBOM(n.model_id, v_instanciable) > 0 THEN
4058 RETURN;
4059 END IF;
4060 END LOOP;
4061 END IF;
4062
4063 EXCEPTION
4064 WHEN NO_DATA_FOUND THEN
4065 NULL;
4066 WHEN TOO_MANY_ROWS THEN
4067 p_out_status_code:=1;
4068 LOG_REPORT('CHECK_REF_REQUEST',
4069 'Wrong PS Tree : there are more than one BOM model in PS Tree');
4070 WHEN OTHERS THEN
4071 LOG_REPORT('CHECK_REF_REQUEST','ERROR CODE :'||ERROR_CODE||' : '||SQLERRM);
4072 END CHECK_REF_REQUEST;
4073
4074 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
4075 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
4076
4077 PROCEDURE SolutionBasedModelcheck
4078 (p_model_id IN INTEGER,
4079 p_instanciable OUT NOCOPY INTEGER) IS
4080
4081 v_bom_node_id CZ_PS_NODES.ps_node_id%TYPE;
4082
4083 BEGIN
4084 p_instanciable:=0;
4085 v_bom_node_id:=containsBOM(p_model_id,p_instanciable);
4086 END SolutionBasedModelcheck;
4087
4088 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
4089 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
4090
4091 PROCEDURE delete_childs(t_arr IN IntArray) IS
4092 temp_arr IntArray;
4093 v_arr IntArray;
4094 BEGIN
4095 IF t_arr.COUNT>0 THEN
4096 FOR i IN t_arr.FIRST..t_arr.LAST
4097 LOOP
4098 temp_arr.DELETE;
4099
4100 --
4101 -- DEBUG ERROR CODE --
4102 --
4103 ERROR_CODE:=1500;
4104
4105 UPDATE CZ_MODEL_REF_EXPLS a SET deleted_flag=YES_FLAG
4106 WHERE child_model_expl_id=t_arr(i) AND
4107 model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
4108 WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG)
4109 RETURNING model_ref_expl_id BULK COLLECT INTO temp_arr;
4110
4111 --
4112 -- DEBUG ERROR CODE --
4113 --
4114 ERROR_CODE:=1501;
4115
4116 IF temp_arr.COUNT>0 THEN
4117 FOR j IN temp_arr.FIRST..temp_arr.LAST
4118 LOOP
4119 v_arr(v_arr.COUNT+1):=temp_arr(j);
4120 END LOOP;
4121 END IF;
4122 END LOOP;
4123
4124 --
4125 -- DEBUG ERROR CODE --
4126 --
4127 ERROR_CODE:=1502;
4128
4129 delete_childs(v_arr);
4130 END IF;
4131 END;
4132
4133 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
4134 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
4135
4136 PROCEDURE delete_subtree(p_model_id IN INTEGER,p_model_ref_expl_id IN INTEGER) IS
4137 t_arr IntArray;
4138 BEGIN
4139 FOR j IN(SELECT model_ref_expl_id,ps_node_type,component_id FROM CZ_MODEL_REF_EXPLS
4140 WHERE model_id=p_model_id AND parent_expl_node_id=p_model_ref_expl_id AND deleted_flag=NO_FLAG)
4141 LOOP
4142 IF j.ps_node_type=CONNECTOR_TYPE THEN
4143 t_arr.DELETE;
4144
4145 --
4146 -- DEBUG ERROR CODE --
4147 --
4148 ERROR_CODE:=1600;
4149
4150 UPDATE CZ_MODEL_REF_EXPLS SET deleted_flag=YES_FLAG
4151 WHERE model_ref_expl_id IN(
4152 SELECT model_ref_expl_id FROM cz_model_ref_expls
4153 START WITH model_ref_expl_id=j.model_ref_expl_id AND deleted_flag=NO_FLAG
4154 CONNECT by PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag=NO_FLAG
4155 AND PRIOR deleted_flag=NO_FLAG);
4156
4157 --
4158 -- DEBUG ERROR CODE --
4159 --
4160 ERROR_CODE:=1601;
4161 ELSE
4162 --
4163 -- DEBUG ERROR CODE --
4164 --
4165 ERROR_CODE:=1602;
4166
4167 delete_subtree(p_model_id,j.model_ref_expl_id);
4168
4169 --
4170 -- DEBUG ERROR CODE --
4171 --
4172 ERROR_CODE:=1603;
4173
4174 END IF;
4175 END LOOP;
4176 END;
4177
4178 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
4179 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
4180
4181
4182 PROCEDURE change_structure_(p_model_id IN INTEGER) IS
4183
4184 BEGIN
4185 FOR i IN(SELECT model_ref_expl_id,ps_node_type FROM CZ_MODEL_REF_EXPLS
4186 WHERE model_id=p_model_id AND component_id=p_model_id AND
4187 ps_node_type IN(CONNECTOR_TYPE,REFERENCE_TYPE) AND deleted_flag=NO_FLAG)
4188 LOOP
4189 delete_subtree(p_model_id,i.model_ref_expl_id);
4190 END LOOP;
4191 END;
4192
4193 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
4194 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
4195
4196 PROCEDURE change_structure(p_model_id IN INTEGER) IS
4197
4198 BEGIN
4199 FOR i IN(SELECT DISTINCT model_id FROM CZ_MODEL_REF_EXPLS
4200 WHERE component_id=p_model_id AND
4201 ps_node_type IN(CONNECTOR_TYPE,REFERENCE_TYPE) AND deleted_flag=NO_FLAG)
4205 END;
4202 LOOP
4203 change_structure_(i.model_id);
4204 END LOOP;
4206
4207 /*>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
4208 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
4209
4210 PROCEDURE set_Trackable_Children_Flag(p_model_id IN NUMBER) IS
4211
4212 t_m_chain_tbl IntArray;
4213 t_trk_tbl IntArray;
4214 t_nontrk_tbl IntArray;
4215 v_ib_trackable CZ_PS_NODES.ib_trackable%TYPE;
4216
4217 BEGIN
4218
4219 SELECT DISTINCT model_id BULK COLLECT INTO t_m_chain_tbl FROM
4220 (SELECT DISTINCT component_id AS model_id
4221 FROM CZ_MODEL_REF_EXPLS WHERE model_id=p_model_id AND ps_node_type=REFERENCE_TYPE
4222 AND deleted_flag=NO_FLAG
4223 UNION
4224 SELECT DISTINCT model_id FROM CZ_MODEL_REF_EXPLS a
4225 WHERE component_id=p_model_id AND ps_node_type=REFERENCE_TYPE AND deleted_flag=NO_FLAG AND
4226 model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
4227 WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG));
4228
4229 t_m_chain_tbl(t_m_chain_tbl.COUNT+1):=p_model_id;
4230
4231 FOR i IN t_m_chain_tbl.First..t_m_chain_tbl.Last
4232 LOOP
4233 --
4234 -- set has_trackable_children to '1' for those references in explosion tree of t_m_chain_tbl(i) model
4235 -- which points to models which have CZ_PS_NODES.ib_trackable='1'. So here we assume
4236 -- that CZ_PS_NODES.ib_trackable is populated correctly by Import
4237 --
4238 UPDATE cz_model_ref_expls
4239 SET has_trackable_children='1'
4240 WHERE model_id=t_m_chain_tbl(i) AND component_id=t_m_chain_tbl(i) AND deleted_flag=NO_FLAG AND
4241 EXISTS(SELECT NULL FROM CZ_MODEL_REF_EXPLS m WHERE model_id=t_m_chain_tbl(i) AND ps_node_type=REFERENCE_TYPE
4242 AND deleted_flag=NO_FLAG AND
4243 EXISTS(SELECT NULL FROM CZ_PS_NODES
4244 WHERE devl_project_id = m.component_id AND
4245 ib_trackable = '1' AND deleted_flag = NO_FLAG));
4246
4247 IF SQL%ROWCOUNT=0 THEN
4248 BEGIN
4249 v_ib_trackable:=NO_FLAG;
4250 SELECT '1' INTO v_ib_trackable FROM dual
4251 WHERE EXISTS(SELECT NULL FROM CZ_PS_NODES WHERE devl_project_id=t_m_chain_tbl(i)
4252 AND deleted_flag=NO_FLAG AND ib_trackable='1');
4253 v_ib_trackable:='1';
4254 t_trk_tbl(t_trk_tbl.COUNT+1):=t_m_chain_tbl(i);
4255 EXCEPTION
4256 WHEN NO_DATA_FOUND THEN
4257 NULL;
4258 END;
4259 ELSE
4260 v_ib_trackable:='1';
4261 t_trk_tbl(t_trk_tbl.COUNT+1):=t_m_chain_tbl(i);
4262 END IF;
4263
4264 IF v_ib_trackable=NO_FLAG THEN
4265 t_nontrk_tbl(t_nontrk_tbl.COUNT+1):=t_m_chain_tbl(i);
4266 END IF;
4267
4268 UPDATE CZ_MODEL_REF_EXPLS SET has_trackable_children=v_ib_trackable
4269 WHERE model_id=t_m_chain_tbl(i) AND component_id=t_m_chain_tbl(i) AND deleted_flag=NO_FLAG;
4270
4271 END LOOP;
4272
4273 IF t_trk_tbl.COUNT>0 THEN
4274 FORALL i IN t_trk_tbl.First..t_trk_tbl.Last
4275 UPDATE CZ_MODEL_REF_EXPLS a SET has_trackable_children='1'
4276 WHERE component_id=t_trk_tbl(i) AND ps_node_type=REFERENCE_TYPE AND deleted_flag=NO_FLAG AND
4277 model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
4278 WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG);
4279 END IF;
4280
4281 IF t_nontrk_tbl.COUNT>0 THEN
4282 FORALL i IN t_nontrk_tbl.First..t_nontrk_tbl.Last
4283 UPDATE CZ_MODEL_REF_EXPLS a SET has_trackable_children=NO_FLAG
4284 WHERE component_id=t_nontrk_tbl(i) AND ps_node_type=REFERENCE_TYPE AND deleted_flag=NO_FLAG AND
4285 model_id IN(SELECT object_id FROM CZ_RP_ENTRIES
4286 WHERE object_id=a.model_id AND object_type='PRJ' AND deleted_flag=NO_FLAG);
4287 END IF;
4288
4289
4290 END set_Trackable_Children_Flag;
4291
4292 FUNCTION check_Rules_For_Ps_Node(p_ps_node_id IN NUMBER)
4293 RETURN NUMBER IS
4294
4295 l_template_id NUMBER;
4296 l_contribute_min_flag VARCHAR2(1);
4297 l_contribute_max_flag VARCHAR2(1);
4298
4299 BEGIN
4300
4301 FND_MSG_PUB.initialize;
4302
4303 FOR i IN(SELECT expr_node_id,rule_id
4304 FROM CZ_EXPRESSION_NODES
4305 WHERE rule_id IN(SELECT rule_id FROM CZ_RULES
4306 WHERE devl_project_id IN(SELECT devl_project_id FROM CZ_PS_NODES
4307 WHERE ps_node_id=p_ps_node_id) AND deleted_flag=NO_FLAG AND disabled_flag=NO_FLAG) AND
4308 ps_node_id=p_ps_node_id AND expr_type=205 AND deleted_flag=NO_FLAG)
4309 LOOP
4310 BEGIN
4311 SELECT template_id INTO l_template_id
4312 FROM CZ_EXPRESSION_NODES
4313 WHERE rule_id=i.rule_id AND expr_parent_id=i.expr_node_id AND
4314 expr_type=210 AND
4315 template_id in(43,44) AND deleted_flag=NO_FLAG;
4316
4317 IF l_template_id=43 THEN
4318 l_contribute_min_flag := YES_FLAG;
4319 END IF;
4320
4321 IF l_template_id=44 THEN
4322 l_contribute_max_flag := YES_FLAG;
4323 END IF;
4324
4328
4325 IF l_contribute_min_flag=YES_FLAG AND l_contribute_max_flag=YES_FLAG THEN
4326 RETURN CONTRIBUTE_TO_MINMAX;
4327 END IF;
4329 EXCEPTION
4330 WHEN OTHERS THEN
4331 NULL;
4332 END;
4333 END LOOP;
4334
4335 IF l_contribute_max_flag=YES_FLAG THEN
4336 RETURN CONTRIBUTE_TO_MAX;
4337 ELSIF l_contribute_min_flag=YES_FLAG THEN
4338 RETURN CONTRIBUTE_TO_MIN;
4339 ELSE
4340 RETURN 0;
4341 END IF;
4342
4343 END check_Rules_For_Ps_Node;
4344
4345
4346 PROCEDURE validate_Inst_Flag
4347 (
4348 p_ps_node_id IN NUMBER,
4349 p_instantiable_flag IN NUMBER,
4350 x_validation_flag OUT NOCOPY VARCHAR2,
4351 x_run_id OUT NOCOPY NUMBER) IS
4352
4353 l_contribute NUMBER;
4354 l_instantiable_flag NUMBER;
4355
4356 BEGIN
4357
4358 FND_MSG_PUB.initialize;
4359
4360 x_run_id := 0; x_validation_flag := NO_FLAG;
4361
4362 SELECT instantiable_flag INTO l_instantiable_flag
4363 FROM CZ_PS_NODES
4364 WHERE ps_node_id=p_ps_node_id;
4365
4366 l_contribute := check_Rules_For_Ps_Node(p_ps_node_id);
4367
4368 IF l_instantiable_flag=MINMAX_EXPL_TYPE AND p_instantiable_flag=MANDATORY_EXPL_TYPE THEN
4369 IF l_contribute IN(CONTRIBUTE_TO_MIN,CONTRIBUTE_TO_MAX,CONTRIBUTE_TO_MINMAX) THEN
4370 x_validation_flag := YES_FLAG;
4371 END IF;
4372 ELSIF l_instantiable_flag=MINMAX_EXPL_TYPE AND p_instantiable_flag=OPTIONAL_EXPL_TYPE THEN
4373 IF l_contribute IN(CONTRIBUTE_TO_MAX) THEN
4374 x_validation_flag := YES_FLAG;
4375 END IF;
4376 ELSIF l_instantiable_flag=OPTIONAL_EXPL_TYPE AND p_instantiable_flag=MANDATORY_EXPL_TYPE THEN
4377 IF l_contribute IN(CONTRIBUTE_TO_MIN) THEN
4378 x_validation_flag := YES_FLAG;
4379 END IF;
4380 ELSE
4381 null;
4382 END IF;
4383
4384 END validate_Inst_Flag;
4385
4386 PROCEDURE check_Inst_Rule
4387 (
4388 p_rule_id IN NUMBER,
4389 x_inst_flag OUT NOCOPY NUMBER,
4390 x_sys_prop OUT NOCOPY NUMBER,
4391 x_validation_flag OUT NOCOPY VARCHAR2) IS
4392
4393 TYPE tExprNodeTbl IS TABLE OF cz_expression_nodes%ROWTYPE INDEX BY BINARY_INTEGER; --Not Used Anywhere, we should remove this.
4394 TYPE tNumber IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4395 TYPE tNumArrayVc2 IS TABLE OF NUMBER INDEX BY VARCHAR2(15); -- New Array defined
4396 TYPE tExprType IS TABLE OF cz_expression_nodes.expr_type%TYPE INDEX BY BINARY_INTEGER;
4397
4398 l_children_index tNumArrayVc2;
4399 l_number_of_children tNumArrayVc2;
4400 l_index_by_expr_node_id tNumArrayVc2;
4401 l_param_index tNumber;
4402 l_seq_nbr tNumber;
4403 l_expr_node_id tNumber;
4404 l_expr_parent_id tNumber;
4405 l_expr_type tExprType;
4406 l_template_id tNumber;
4407 l_ps_node_id tNumber;
4408
4409 l_children number;
4410
4411 CURSOR C1 IS
4412 SELECT param_index, seq_nbr, expr_type, expr_node_id,
4413 expr_parent_id,template_id, ps_node_id
4414 FROM cz_expression_nodes
4415 WHERE rule_id = p_rule_id
4416 AND deleted_flag = '0'
4417 ORDER BY expr_parent_id, seq_nbr;
4418
4419 BEGIN
4420
4421 FND_MSG_PUB.initialize;
4422
4423 x_inst_flag := NULL;
4424 x_sys_prop := NULL;
4425 x_validation_flag := YES_FLAG;
4426
4427 OPEN C1;
4428 FETCH C1 BULK COLLECT INTO l_param_index, l_seq_nbr,l_expr_type,
4429 l_expr_node_id, l_expr_parent_id, l_template_id, l_ps_node_id;
4430 CLOSE C1;
4431 IF (l_expr_node_id.COUNT = 0) THEN
4432 RETURN;
4433 END IF;
4434
4435 FOR i IN l_expr_parent_id.FIRST..l_expr_parent_id.LAST LOOP
4436 IF(l_expr_parent_id(i) IS NOT NULL) THEN
4437 IF(l_number_of_children.EXISTS(l_expr_parent_id(i))) THEN
4438 l_number_of_children(l_expr_parent_id(i)) := l_number_of_children(l_expr_parent_id(i)) + 1;
4439 ELSE
4440 l_number_of_children(l_expr_parent_id(i)) := 1;
4441 END IF;
4442 IF(NOT l_children_index.EXISTS(l_expr_parent_id(i)))THEN
4443 l_children_index(l_expr_parent_id(i)) := i;
4444 END IF;
4445 END IF;
4446 --Add the indexing option.
4447 l_index_by_expr_node_id(l_expr_node_id(i)) := i;
4448 END LOOP;
4449
4450 IF (l_template_id(l_expr_node_id.COUNT) = 22) THEN /* simple numeric rule */
4451 FOR i IN 1..l_expr_node_id.COUNT lOOP
4452 IF (l_param_index(i)=5) THEN
4453 IF (l_children_index.EXISTS(l_expr_node_id(i))) THEN
4454 l_children := l_number_of_children(l_expr_node_id(i));
4455 FOR j IN l_children_index(l_expr_node_id(i))..l_children_index(l_expr_node_id(i)) + l_children LOOP
4456 IF (l_expr_type(j) IN (207,210) AND l_template_id(j) IN (43,44)) THEN
4457 FOR k IN (SELECT instantiable_flag FROM cz_ps_nodes
4458 WHERE ps_node_id = l_ps_node_id(i)
4459 AND deleted_flag = NO_FLAG) LOOP
4460 IF (k.instantiable_flag = '2') THEN
4461 IF (l_template_id(j) = 43) THEN
4462 x_sys_prop := MIN_RULE;
4463 ELSE
4464 x_sys_prop := MAX_RULE;
4465 END IF;
4466 x_inst_flag := TO_NUMBER(k.instantiable_flag);
4467 x_validation_flag := NO_FLAG;
4471 x_validation_flag := NO_FLAG;
4468 ELSIF (l_template_id(j) = 44 AND k.instantiable_flag = '1') THEN
4469 x_sys_prop := MAX_RULE;
4470 x_inst_flag := TO_NUMBER(k.instantiable_flag);
4472 END IF;
4473 END LOOP;
4474 END IF;
4475 IF (x_validation_flag = NO_FLAG) THEN
4476 EXIT;
4477 END IF;
4478 END LOOP;
4479 END IF;
4480 END IF;
4481 IF (x_validation_flag = NO_FLAG) THEN
4482 EXIT;
4483 END IF;
4484 END LOOP;
4485 ELSIF (l_template_id(l_expr_node_id.COUNT) IS NULL) THEN /* statement rule, Developer must call parser */
4486 x_validation_flag := NULL;
4487 END IF;
4488 END check_Inst_Rule;
4489
4490 END;
[Home] [Help]