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