[Home] [Help]
PACKAGE BODY: APPS.CZ_MODEL_UTIL_PVT
Source
1 PACKAGE BODY cz_model_util_pvt AS
2 /* $Header: czvmdlub.pls 120.4 2008/05/08 12:14:54 jonatara ship $ */
3 -- COPIED FROM ARCS 2004-09-22 1230 EDT by ADW
4
5 /*
6 * Revisions 2004-07-30 by ADW as part of bugs 3804946 (3732895)
7 * in function: FIND_NODES_BY_PATH
8 *
9 * Revision 2006-Feb by ADW for bug 4760372, parsing references that appear
10 * ambiguous, but which are uniquely resolved at runtime due to non-
11 * overlapping effectivities
12 */
13
14 PS_NODE_TYPE_REFERENCE CONSTANT PLS_INTEGER := 263;
15 PS_NODE_TYPE_COMPONENT CONSTANT PLS_INTEGER := 259;
16 PS_NODE_TYPE_OPTION_CLASS CONSTANT PLS_INTEGER := 437;
17
18 TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
19 TYPE number_tbl_type_idx_vc2 IS TABLE OF NUMBER INDEX BY VARCHAR2(15); -- --jonatara:int2long:bug7028517
20
21 --------------------------------------------------------------------------------
22 PROCEDURE get_referenced_models_pvt(p_parent_id IN NUMBER
23 ,px_model_tbl IN OUT NOCOPY number_tbl_type
24 ,px_model_map IN OUT NOCOPY number_tbl_type_idx_vc2
25 )
26 IS
27
28 BEGIN
29 FOR psn_rec IN (SELECT ps_node_id, reference_id FROM cz_ps_nodes
30 WHERE deleted_flag = '0' AND parent_id = p_parent_id
31 AND ps_node_type IN (PS_NODE_TYPE_REFERENCE,
32 PS_NODE_TYPE_COMPONENT,
33 PS_NODE_TYPE_OPTION_CLASS)
34 ORDER BY tree_seq) LOOP
35 IF (psn_rec.reference_id IS NOT NULL) THEN
36 IF (NOT px_model_map.EXISTS(psn_rec.reference_id)) THEN
37 px_model_tbl(px_model_tbl.COUNT + 1) := psn_rec.reference_id;
38 px_model_map(psn_rec.reference_id) := psn_rec.reference_id;
39 END IF;
40 ELSE
41 get_referenced_models_pvt(psn_rec.ps_node_id, px_model_tbl, px_model_map);
42 END IF;
43 END LOOP;
44
45 EXCEPTION
46 WHEN OTHERS THEN
47 RAISE;
48 END get_referenced_models_pvt;
49
50 --------------------------------------------------------------------------------
51 -- Returns array of distinct ordered referenced model ids under the top-level
52 -- model specified by p_model_id
53 --
54 -- For example, given the following model tree,
55 -- M1
56 -- |_OC
57 -- | |_M2
58 -- | |_OC
59 -- | | |_OC
60 -- | | |_OC
61 -- | | |_M3
62 -- | | |_M4
63 -- | |_M5
64 -- |_M6
65 -- |_OC
66 -- |_M7
67 -- |_M5
68 -- the model_id field of the returned array will be [M1, M2, M6, M3, M5, M7, M4]
69 -- while the seq_nbr will be the same as the array indexes.
70
71 FUNCTION get_referenced_models(p_model_id IN NUMBER)
72 RETURN system.cz_model_order_tbl_type
73 IS
74 l_ret_tbl system.cz_model_order_tbl_type := system.cz_model_order_tbl_type();
75 l_model_map number_tbl_type_idx_vc2;
76 l_model_tbl number_tbl_type;
77
78 l_last_finished_index PLS_INTEGER := 0;
79
80 BEGIN
81 l_model_tbl(1) := p_model_id;
82 l_model_map(p_model_id) := p_model_id;
83
84 WHILE (l_last_finished_index < l_model_tbl.COUNT) LOOP
85 l_last_finished_index := l_last_finished_index + 1;
86 get_referenced_models_pvt(l_model_tbl(l_last_finished_index)
87 ,l_model_tbl
88 ,l_model_map
89 );
90 END LOOP;
91
92 l_ret_tbl.EXTEND(l_last_finished_index);
93 FOR i IN l_model_tbl.FIRST .. l_model_tbl.LAST LOOP
94 l_ret_tbl(i) := system.cz_model_order_obj_type(l_model_tbl(i), i);
95 END LOOP;
96 RETURN l_ret_tbl;
97
98 EXCEPTION
99 WHEN OTHERS THEN
100 raise_application_error(-20999,
101 'cz_model_util_pvt.get_referenced_models: ' || SQLERRM);
102 return NULL;
103 END get_referenced_models;
104
105 --------------------------------------------------------------------------------
106 FUNCTION find_nodes_by_path(p_model_to_search IN NUMBER
107 ,p_namepath IN system.cz_varchar2_2000_tbl_type
108 )
109 RETURN system.cz_model_node_tbl_type
110 IS
111 nodelist system.cz_model_node_tbl_type;
112 BEGIN
113
114 nodelist := find_unique_node_by_path (
115 p_model_to_search,
116 p_namepath,
117 p_path_preference => PATH_PREFERENCE_ROOT_KIDS
118 );
119
120 return nodelist;
121 END find_nodes_by_path;
122
123 --------------------------------------------------------------------------------
124 --------------------------------------------------------------------------------
125 FUNCTION find_unique_node_by_path(p_model_to_search IN NUMBER
126 ,p_namepath IN system.cz_varchar2_2000_tbl_type
127 ,p_path_preference IN NUMBER
128 )
129 RETURN "SYSTEM".cz_model_node_tbl_type
130 /*
131 * Revision 2006-02-21 by ADW for ambiguous reference bug 4760372
132 * 1. moved marklist variables into main structure CZ_MODEL_NODE_OBJ_TYPE
133 * 2. removed marklist handling from routine
134 * 3. added new path-head information retrieval
135 *
136 * Revisions 2004-07-30 by ADW as part of bugs 3804946 (3732895)
137 *
138 * 1. move call to CZ_TYPES.GET_RULE_SIGNATURE_ID into queries instead of iterating
139 * 2. fix parent/child relationship in subsequent-path query to use PARENT_PSNODE_EXPL_ID
140 * instead of decode
141 * 3. implement preference for absolute path and/or direct-under-root path
142 * a. mark each partial path with whether or not it is ROOT using a parallel TABLE
143 * b. keep track of a high water mark for matches
144 * c. implement preference for 'root' paths
145 */
146
147 IS
148 retlist "SYSTEM".cz_model_node_tbl_type := "SYSTEM".cz_model_node_tbl_type();
149 lastfound "SYSTEM".cz_model_node_tbl_type;
150
151 /* ADW20040730:3a
152 * Set up a parallel structure to mark which entries are paths from root
153 * why overload the object types:1 1. need to do a table (cast), so an internal
154 * record type won't work; must be an object type; 2. do not want to create a
155 * new object type and table type in SYSTEM; 3. the structure is already almost
156 * exactly what is needed
157 */
158 -- marklist "SYSTEM".cz_model_node_tbl_type := "SYSTEM".cz_model_node_tbl_type ();
159 -- lastmarks "SYSTEM".cz_model_node_tbl_type;
160 root_closest_path_index number; -- 20040730:3b
161 root_closest_path_marker number;
162
163 pathiter integer;
164 foundnodes CZ_EXPLMODEL_NODES_V%rowtype;
165 foundcount integer := 0;
166
167 BEGIN
168 -- Parameter check: namepath
169 IF p_namepath.last IS NULL THEN
170 RETURN NULL;
171 END IF;
172
173 FOR pathiter IN p_namepath.first .. p_namepath.last LOOP
174 -- Another parm check... no embedded empty strings
175 EXIT WHEN p_namepath(pathiter) IS NULL;
176
177 -- copy RETLIST to another variable; it will be used as a query paremeter
178 lastfound := retlist;
179
180 -- clear RETLIST to get the next set of nodes
181 retlist := "SYSTEM".cz_model_node_tbl_type();
182 foundcount := 0;
183
184 -- ADW20040730:3a move last MARKLIST into LASTMARKS for later query and reinitialize MARKLIST
185 -- LASTMARKS := MARKLIST;
186 -- MARKLIST := "SYSTEM".cz_model_node_tbl_type ();
187 -- ADW20060221 don't really need marklist any more
188
189 -- ADW20040730:3b
190 root_closest_path_marker := 10;
191 root_closest_path_index := null;
192
193 IF pathiter = p_namepath.first THEN
194 -- query all nodes in the model by the start of the path
195 FOR foundnodes IN (
196 SELECT
197 XPLNODE.MODEL_REF_EXPL_ID,
198 XPLNODE.psn_component_id as COMPONENT_ID,
199 XPLNODE.PS_NODE_ID,
200 --
201 CZ_TYPES.get_rule_signature_id ( -- ADW20040730:1
202 XPLNODE.instantiable_flag
203 ,XPLNODE.feature_type
204 ,XPLNODE.counted_options_flag
205 ,XPLNODE.maximum
206 ,XPLNODE.minimum
207 ,XPLNODE.psn_ps_node_type
208 ,XPLNODE.reference_id
209 ,XPLNODE.maximum_selected
210 ,XPLNODE.decimal_qty_flag
211 ,XPLNODE.ib_trackable
212 ,XPLNODE.devl_project_id
213 ) as DETAILED_TYPE_ID,
214 XPLNODE.ps_node_id as PATH_HEAD_PSNODE_ID,
215 XPLNODE.model_ref_expl_id as PATH_HEAD_EXPL_ID,
216 --
217 XPLNODE.effective_parent_id as PATHHEAD_EFFPARENT_NODE_ID,
218 XPLNODE.parent_psnode_expl_id as PATHHEAD_EFFPARENT_EXPL_ID,
219 XPLNODE.effective_from as NODE_EFFECTIVE_FROM,
220 --
221 XPLNODE.effective_until as NODE_EFFECTIVE_UNTIL,
222 XPLNODE.effective_from as WHOLE_PATH_EFFECTIVE_FROM,
223 XPLNODE.effective_until as WHOLE_PATH_EFFECTIVE_UNTIL,
224 --
225 decode (XPLNODE.parent_psnode_expl_id, -- ADW20040730:3a
226 null, path_preference_root_only, -- only the model root has this calculated field null
227 ROOTEXPL.model_ref_expl_id, decode (XPLNODE.parent_id,
228 XPLNODE.model_id, path_preference_root_kids,
229 -- if parent is MODEL_ID, and the calc parent expl
230 -- is the root expl, this is a direct child of root
231 99),
232 999
233 ) as NEAR_ROOT_MARKER
234 FROM CZ_EXPLMODEL_NODES_V XPLNODE
235 , CZ_MODEL_REF_EXPLS ROOTEXPL
236 WHERE XPLNODE.model_id = p_model_to_search
237 AND ROOTEXPL.MODEL_ID = P_MODEL_TO_SEARCH
238 AND ROOTEXPL.parent_expl_node_id IS NULL
239 AND ROOTEXPL.DELETED_FLAG = '0'
240 AND XPLNODE.name = p_namepath(pathiter)
241 AND suppress_flag = '0'
242 )
243 LOOP
244 foundcount := foundcount + 1;
245 retlist.extend ();
246 -- push each node into retlist
247 retlist (foundcount) := "SYSTEM".cz_model_node_obj_type (
248 model_ref_expl_id => foundnodes.MODEL_REF_EXPL_ID,
249 component_id => foundnodes.COMPONENT_ID,
250 ps_node_id => foundnodes.PS_NODE_ID,
251 --
252 detailed_type_id => foundnodes.DETAILED_TYPE_ID,
253 path_head_psnode_id => foundnodes.PATH_HEAD_PSNODE_ID,
254 path_head_expl_id => foundnodes.PATH_HEAD_EXPL_ID,
255 --
256 pathhead_effparent_node_id => foundnodes.PATHHEAD_EFFPARENT_NODE_ID,
257 pathhead_effparent_expl_id => foundnodes.PATHHEAD_EFFPARENT_EXPL_ID,
258 node_effective_from => foundnodes.NODE_EFFECTIVE_FROM,
259 --
260 node_effective_until => foundnodes.NODE_EFFECTIVE_UNTIL,
261 whole_path_effective_from => foundnodes.WHOLE_PATH_EFFECTIVE_FROM,
262 whole_path_effective_until => foundnodes.WHOLE_PATH_EFFECTIVE_UNTIL,
263 --
264 near_root_marker => foundnodes.NEAR_ROOT_MARKER
265 ); -- ADW20040730:1
266
267 if foundnodes.near_root_marker < root_closest_path_marker -- ADW20040730:3b
268 then
269 root_closest_path_marker := foundnodes.near_root_marker; -- record the nearest to root
270 root_closest_path_index := foundcount; -- record the index of nearest
271 elsif foundnodes.near_root_marker = root_closest_path_marker
272 then -- a previous entry started just as close to root -- NULL THE INDEX OUT
273 root_closest_path_index := NULL;
274 end if;
275
276 END LOOP;
277 ELSE
278 -- query nodes in the model, but based on a parent-child relationship
279 -- with records in prior query "lastfound"
280 FOR foundnodes IN (
281 SELECT
282 XPLNODE.MODEL_REF_EXPL_ID,
283 XPLNODE.PSN_COMPONENT_ID as COMPONENT_ID,
284 XPLNODE.PS_NODE_ID,
285 --
286 CZ_TYPES.get_rule_signature_id ( -- ADW20040730:1
287 XPLNODE.instantiable_flag
288 ,XPLNODE.feature_type
289 ,XPLNODE.counted_options_flag
290 ,XPLNODE.maximum
291 ,XPLNODE.minimum
292 ,XPLNODE.psn_ps_node_type
293 ,XPLNODE.reference_id
294 ,XPLNODE.maximum_selected
295 ,XPLNODE.decimal_qty_flag
296 ,XPLNODE.ib_trackable
297 ,XPLNODE.devl_project_id
298 ) as DETAILED_TYPE_ID,
299 PARNODE.PATH_HEAD_PSNODE_ID,
300 PARNODE.PATH_HEAD_EXPL_ID,
301 --
302 PARNODE.PATHHEAD_EFFPARENT_NODE_ID,
303 PARNODE.PATHHEAD_EFFPARENT_EXPL_ID,
304 XPLNODE.effective_from as NODE_EFFECTIVE_FROM,
305 --
306 XPLNODE.effective_until as NODE_EFFECTIVE_UNTIL,
307 greatest (
308 PARNODE.whole_path_effective_from,
309 XPLNODE.effective_from
310 ) as WHOLE_PATH_EFFECTIVE_FROM,
311 least (
312 PARNODE.whole_path_effective_until,
313 XPLNODE.effective_until
314 ) as WHOLE_PATH_EFFECTIVE_UNTIL,
315 --
316 PARNODE.NEAR_ROOT_MARKER -- ADW20040730:3a
317 FROM
318 CZ_EXPLMODEL_NODES_V XPLNODE,
319 -- table (cast (LASTMARKS AS cz_model_node_tbl_type)) PARMARK, -- ADW20040730:3a
320 table (cast (LASTFOUND as "SYSTEM".cz_model_node_tbl_type)) PARNODE
321 WHERE
322 XPLNODE.model_id = p_model_to_search
323 AND XPLNODE.name = p_namepath(pathiter)
324 AND XPLNODE.effective_parent_id = PARNODE.PS_NODE_ID
325 AND XPLNODE.parent_psnode_expl_id = PARNODE.model_ref_expl_id -- ADW20040730:2
326 -- AND PARMARK.model_ref_expl_id = PARNODE.model_ref_expl_id -- ADW20040730:3
327 -- AND PARMARK.ps_node_id = PARNODE.ps_node_id
328 AND suppress_flag='0'
329 )
330 LOOP
334 retlist (foundcount) := "SYSTEM".cz_model_node_obj_type (
331 -- push each node into retlist
332 foundcount := foundcount + 1;
333 retlist.extend ();
335 model_ref_expl_id => foundnodes.MODEL_REF_EXPL_ID,
336 component_id => foundnodes.COMPONENT_ID,
337 ps_node_id => foundnodes.PS_NODE_ID,
338 --
339 detailed_type_id => foundnodes.DETAILED_TYPE_ID,
340 path_head_psnode_id => foundnodes.PATH_HEAD_PSNODE_ID,
341 path_head_expl_id => foundnodes.PATH_HEAD_EXPL_ID,
342 --
343 pathhead_effparent_node_id => foundnodes.PATHHEAD_EFFPARENT_NODE_ID,
344 pathhead_effparent_expl_id => foundnodes.PATHHEAD_EFFPARENT_EXPL_ID,
345 node_effective_from => foundnodes.NODE_EFFECTIVE_FROM,
346 --
347 node_effective_until => foundnodes.NODE_EFFECTIVE_UNTIL,
348 whole_path_effective_from => foundnodes.WHOLE_PATH_EFFECTIVE_FROM,
349 whole_path_effective_until => foundnodes.WHOLE_PATH_EFFECTIVE_UNTIL,
350 --
351 near_root_marker => foundnodes.NEAR_ROOT_MARKER
352 ); -- ADW20060221
353
354 if foundnodes.near_root_marker < root_closest_path_marker -- ADW20040730:3b
355 then
356 root_closest_path_marker := foundnodes.near_root_marker; -- record the nearest to root
357 root_closest_path_index := foundcount; -- record the index of nearest
358 elsif foundnodes.NEAR_ROOT_MARKER = root_closest_path_marker
359 then -- a previous entry started just as close to root -- NULL THE INDEX OUT
360 root_closest_path_index := NULL;
361 end if;
362
363 END LOOP;
364 END IF;
365 EXIT WHEN foundcount = 0;
366 END LOOP;
367
368 if -- ADW20040730:3c
369 foundcount > 1
370 and root_closest_path_index is not null
371 and root_closest_path_marker <= p_path_preference
372 then
373 lastfound := "SYSTEM".cz_model_node_tbl_type ();
374 lastfound.extend ();
375 lastfound (1) := retlist (root_closest_path_index);
376 return lastfound;
377 else
378 RETURN retlist;
379 end if;
380
381
382
383 END find_unique_node_by_path;
384
385 --------------------------------------------------------------------------------
386
387 FUNCTION URFINMODEL_TO_STRING (P_UI_MODEL_ENTRY IN "SYSTEM".CZ_UIREFS_INMODEL_OBJ_TYPE) RETURN STRING
388 IS
389 WORKINGSTRING VARCHAR2 (2000);
390 BEGIN
391 WORKINGSTRING :=
392 'MODEL => ' || TO_CHAR (P_UI_MODEL_ENTRY.ROOT_MODEL_ID) ||
393 ', RTUDF => ' || TO_CHAR (P_UI_MODEL_ENTRY.ROOT_UI_DEF_ID) ||
394 ', RTXPL => ' || TO_CHAR (P_UI_MODEL_ENTRY.ROOT_MODEL_EXPL_ID) ||
395 --
396 ', REFND => ' || TO_CHAR (P_UI_MODEL_ENTRY.REFERRING_NODE_ID) ||
397 ', XDEEP => ' || TO_CHAR (P_UI_MODEL_ENTRY.EXPL_NODE_DEPTH) ||
398 ', REFDP => ' || TO_CHAR (P_UI_MODEL_ENTRY.MODEL_REFERENCE_DEPTH) ||
399 --
400 ', VIRTF => ' || P_UI_MODEL_ENTRY.VIRTUAL_FLAG ||
401 ', CHLUI => ' || TO_CHAR (P_UI_MODEL_ENTRY.CHILD_UI_DEF_ID) ||
402 ', PARUI => ' || TO_CHAR (P_UI_MODEL_ENTRY.PARENT_UI_DEF_ID) ||
403 --
404 ', CHMDL => ' || TO_CHAR (P_UI_MODEL_ENTRY.CHILD_MODEL_ID) ||
405 ', REFXP => ' || TO_CHAR (P_UI_MODEL_ENTRY.REF_EXPL_NODE_ID) ||
406 ', LEAFX => ' || TO_CHAR (P_UI_MODEL_ENTRY.LEAF_EXPL_NODE_ID) ||
407 --
408 ', LEAFN => ' || TO_CHAR (P_UI_MODEL_ENTRY.LEAF_PERSISTENT_NODE_ID) ||
409 '';
410 RETURN WORKINGSTRING;
411 END URFINMODEL_TO_STRING;
412
413 --------------------------------------------------------------------------------
414 FUNCTION get_ui_refs_under_model (
415 p_root_ui_def_id IN NUMBER,
416 p_maxdepth IN NUMBER
417 ) return
418 system.CZ_UIREFS_INMODEL_TBL_TYPE
419 IS
420 listsofar system.CZ_UIREFS_INMODEL_TBL_TYPE := "SYSTEM".CZ_UIREFS_INMODEL_TBL_TYPE ();
421 listsize number := 0;
422 curdepth number := 0;
423 depthcount number := 0;
424 childuihack number;
425 parentuihack number;
426 BEGIN
427 FOR rootinfo in (
428 select
429 udf.devl_project_id as root_model_id,
430 udf.ui_def_id as root_ui_def_id,
431 xpl.model_ref_expl_id as root_model_expl_id,
432 xpl.parent_expl_node_id as root_mdl_parnt_expl_id,
433 to_number (null) as referring_node_id,
434 xpl.node_depth as expl_node_depth,
435 0 as model_reference_depth,
436 '1' as virtual_flag,
437 udf.ui_def_id as child_ui_def_id,
438 to_number (null) as parent_ui_def_id,
439 udf.devl_project_id as child_model_id,
440 xpl.model_ref_expl_id as ref_expl_node_id,
441 xpl.model_ref_expl_id as leaf_expl_node_id,
442 rootn.persistent_node_id as leaf_persistent_node_id
443 from
444 cz_ui_defs udf,
445 cz_model_ref_expls xpl,
446 cz_ps_nodes rootn
447 where
448 rootn.deleted_flag = '0' and
449 rootn.ps_node_id = xpl.component_id and
450 xpl.model_id = udf.devl_project_id and
451 xpl.deleted_flag = '0' and
452 xpl.parent_expl_node_id is null and
453 udf.ui_def_id = p_root_ui_def_id and
454 udf.deleted_flag = '0'
455 )
456 LOOP
457 listsofar.extend ();
461 ROOT_UI_DEF_ID => rootinfo.ROOT_UI_DEF_ID,
458 listsize := listsize + 1;
459 listsofar (listsize) := system.cz_uirefs_inmodel_obj_type (
460 ROOT_MODEL_ID => rootinfo.ROOT_MODEL_ID,
462 ROOT_MODEL_EXPL_ID => rootinfo.ROOT_MODEL_EXPL_ID,
463 ROOT_MDL_PARNT_EXPL_ID => rootinfo.ROOT_MDL_PARNT_EXPL_ID,
464 referring_node_id => rootinfo.referring_node_id,
465 expl_node_depth => rootinfo.expl_node_depth,
466 model_reference_depth => rootinfo.model_reference_depth,
467 virtual_flag => rootinfo.virtual_flag,
468 child_ui_def_id => rootinfo.child_ui_def_id,
469 parent_ui_def_id => rootinfo.parent_ui_def_id,
470 child_model_id => rootinfo.child_model_id,
471 ref_expl_node_id => rootinfo.ref_expl_node_id,
472 leaf_expl_node_id => rootinfo.leaf_expl_node_id,
473 leaf_persistent_node_id => rootinfo.leaf_persistent_node_id
474 );
475 depthcount := depthcount + 1;
476
477 -- DBMS_OUTPUT.PUT_LINE ('Root fetch: curdepth ' || to_char (curdepth) ||
478 -- ', depthcount ' || to_char (depthcount) ||
479 -- ', listsize ' || to_char (listsize));
480 -- dbms_output.put_line (urfinmodel_to_string (listsofar (listsize)));
481 END LOOP;
482
483 curdepth := curdepth + 1;
484 if depthcount = 0 then return listsofar; end if;
485
486
487 LOOP
488 EXIT WHEN curdepth > p_maxdepth;
489
490 depthcount := 0;
491 for nextinfo in (
492 select
493 rootxp.model_id as root_model_id,
494 extlist.root_ui_def_id as root_ui_def_id,
495 rootxp.model_ref_expl_id as root_model_expl_id,
496 rootxp.parent_expl_node_id as root_mdl_parnt_expl_id,
497 rootxp.referring_node_id,
498 rootxp.node_depth as expl_node_depth,
499 extlist.model_reference_depth + decode (extlist.child_model_id,
500 DECODE (enclexpl.ps_node_type,
501 263, enclexpl.component_id,
502 264, enclexpl.component_id,
503 enclexpl.model_id
504 ), 0,
505 1
506 ) as model_reference_depth,
507 rootxp.virtual_flag,
508 extlist.child_ui_def_id as child_ui_def_id,
509 extlist.parent_ui_def_id as parent_ui_def_id,
510 DECODE (enclexpl.ps_node_type,
511 263, enclexpl.component_id,
512 264, enclexpl.component_id,
513 enclexpl.model_id
514 ) as child_model_id,
515 enclexpl.model_ref_expl_id as ref_expl_node_id,
516 enclexpl.child_model_expl_id as leaf_expl_node_id,
517 comp.persistent_node_id as leaf_persistent_node_id
518 from
519 cz_ps_nodes comp,
520 cz_model_ref_expls rootxp,
521 cz_model_ref_expls enclexpl,
522 table (cast (listsofar as "SYSTEM".cz_uirefs_inmodel_tbl_type)) extlist
523 where
524 comp.ps_node_id = rootxp.component_id and
525 comp.deleted_flag = '0' and
526 rootxp.node_depth = curdepth and
527 extlist.expl_node_depth = curdepth - 1 and
528 rootxp.component_id = enclexpl.component_id and
529 decode (rootxp.referring_node_id, enclexpl.referring_node_id, 1, 0) = 1 and
530 rootxp.deleted_flag = '0' and
531 rootxp.parent_expl_node_id = EXTLIST.ROOT_model_expl_id and
532 enclexpl.deleted_flag = '0' and
533 enclexpl.parent_expl_node_id in (EXTLIST.REF_EXPL_NODE_id,
534 EXTLIST.LEAF_EXPL_NODE_ID)
535 -- 2004-09-22 ADW: this join criterion from the 'parent row' would spelunk down references
536 -- to the child UI/expl but NOT in the parent UI/expl tree. I think only the following clause
537 -- caused this behavior. Minor tweak above may fix the problem...
538 -- enclexpl.parent_expl_node_id = decode (EXTLIST.referring_node_id,
539 -- null, EXTLIST.REF_EXPL_NODE_id,
540 -- EXTLIST.LEAF_EXPL_NODE_ID)
541 )
542 loop
543 listsofar.extend ();
544 listsize := listsize + 1;
545 begin
546 select
547 urf.ref_ui_def_id as current_child_ui,
548 urf.ui_def_id as current_parent_ui
549 into
550 childuihack,
551 parentuihack
552 from cz_ui_refs urf
553 where
554 nextinfo.ref_expl_node_id = urf.model_ref_expl_id AND
555 nextinfo.child_ui_def_id = urf.ui_def_id and
556 urf.deleted_flag = '0';
557 -- dbms_output.put_line ('Fetched reference info for expl and UI');
558 exception
559 when NO_DATA_FOUND then
560 -- dbms_output.put_line ('No ref info available, using prior');
561 childuihack := nextinfo.child_ui_def_id;
562 parentuihack := nextinfo.parent_ui_def_id;
563 end;
564 listsofar (listsize) := system.cz_uirefs_inmodel_obj_type (
565 ROOT_MODEL_ID => nextinfo.ROOT_MODEL_ID,
566 ROOT_UI_DEF_ID => nextinfo.ROOT_UI_DEF_ID,
567 ROOT_MODEL_EXPL_ID => nextinfo.ROOT_MODEL_EXPL_ID,
568 ROOT_MDL_PARNT_EXPL_ID => nextinfo.ROOT_MDL_PARNT_EXPL_ID,
569 referring_node_id => nextinfo.referring_node_id,
570 expl_node_depth => nextinfo.expl_node_depth,
571 model_reference_depth => nextinfo.model_reference_depth,
572 virtual_flag => nextinfo.virtual_flag,
573 child_ui_def_id => childuihack,
574 parent_ui_def_id => parentuihack,
575 child_model_id => nextinfo.child_model_id,
576 ref_expl_node_id => nextinfo.ref_expl_node_id,
577 leaf_expl_node_id => nextinfo.leaf_expl_node_id,
578 leaf_persistent_node_id => nextinfo.leaf_persistent_node_id
579 );
580 depthcount := depthcount + 1;
581
582 -- DBMS_OUTPUT.PUT_LINE ('Next-level fetch: curdepth ' || to_char (curdepth) ||
586 end loop;
583 -- ', depthcount ' || to_char (depthcount) ||
584 -- ', listsize ' || to_char (listsize));
585 -- dbms_output.put_line (urfinmodel_to_string (listsofar (listsize)));
587
588 -- DBMS_OUTPUT.PUT_LINE ('End of per-level loop: curdepth ' || to_char (curdepth) ||
589 -- ', depthcount ' || to_char (depthcount) ||
590 -- ', listsize ' || to_char (listsize));
591 -- dbms_output.put_line (urfinmodel_to_string (listsofar (listsize)));
592
593 if depthcount = 0 then return listsofar; end if;
594
595 curdepth := curdepth + 1;
596
597 END LOOP;
598 return listsofar;
599 EXCEPTION
600 WHEN OTHERS THEN
601 -- dbms_output.put_line ('Received exception ' || sqlerrm);
602 raise;
603 END get_ui_refs_under_model;
604 --------------------------------------------------------------------------------
605
606 FUNCTION get_parallel_expls (p_encl_expl_id in number, p_desc_expl_id in number, p_max_expl_depth in number)
607 return "SYSTEM".cz_expl_pair_tbl
608 is
609 -- Added new component to CZ_EXPL_PAIR for bug 4486182, 7/12/2005
610 -- model reference depth, to support field of same name in cz_uicomponent_hgrid_v
611 --
612 -- restructured algorithm 2004-10-12
613 -- uses the same parallel-expl query structure, but instead of joining to a table case as cz_expl_pair_tbl,
614 -- this approach simply iterates over the pairs and gets the matching children for each
615 -- this allows me to use a single cz_expl_pair_tbl.
616 pairs "SYSTEM".cz_expl_pair_tbl := "SYSTEM".cz_expl_pair_tbl ();
617 paircount number;
618 newpair "SYSTEM".cz_expl_pair;
619 cur_pair_index number;
620 start_encl_depth number;
621 desc_ref_depth number;
622 curdepth number;
623 begin
624 -- first -- verify that p_desc_expl_id really is a child model expl ID of p_encl_expl_id
625 -- we can also use this to calculate MODEL_REFERENCE_DEPTH
626 begin
627 select (level - 1) into desc_ref_depth
628 from cz_model_ref_expls
629 where model_ref_expl_id = p_desc_expl_id and deleted_flag = '0'
630 start with model_ref_expl_id = p_encl_expl_id and deleted_flag = '0'
631 connect by model_ref_expl_id = prior child_model_expl_id and deleted_flag = '0' and
632 prior model_ref_expl_id <> p_desc_expl_id;
633
634 -- FOLLOWING probably won't occur, I expect NO_DATA_FOUND exception, but I'll play
635 -- it safe
636 if SQL%ROWCOUNT = 0 then return pairs; end if;
637
638 exception
639 when NO_DATA_FOUND then
640 return pairs;
641 end;
642
643 -- to honor the "limiter", we get the original explosion depth. This will be used to determine
644 -- if we have exceeded the depth limit specified by the P_MAX_EXPL_DEPTH parameter
645 select node_depth into start_encl_depth
646 from cz_model_ref_expls
647 where model_ref_expl_id = p_encl_expl_id;
648
649 -- get some room
650 pairs.extend ();
651 paircount := 1;
652
653 -- take the requested pair, instantiate it, and put it at the top of the results
654 pairs (paircount) := "SYSTEM".cz_expl_pair (
655 ENCLOSING_EXPL_ID => p_encl_expl_id,
656 DESCENDANT_EXPL_ID => p_desc_expl_id,
657 DESC_EXPL_REFERENCE_DEPTH => desc_ref_depth
658 );
659 curdepth := start_encl_depth;
660 cur_pair_index := 0;
661
662 -- LOOP: track down the explosions in parallel
663 -- loop invariants:
664 -- CUR_PAIR_INDEX -- each iteration of the loop "consumes" a pair from the results table ("pairs")
665 -- and searches for child explosions (paired) for that pair. Each one found is appended to PAIRS,
666 -- which obviously increases PAIRS.LAST, continuing the iteration
667 -- CURDEPTH -- the loops track the depth from the NODE_DEPTH variable. When the depth has gotten to
668 -- the limit specified in P_MAX_EXPL_DEPTH, the loop exits
669 while
670 cur_pair_index < pairs.last
671 and
672 (p_max_expl_depth is null or (1 + curdepth - start_encl_depth) < p_max_expl_depth)
673 loop
674 cur_pair_index := cur_pair_index + 1;
675 -- increment the index to "look" at the next pair in the results table
676
677 for nextpair in (
678 select
679 r_xp.model_ref_expl_id as encl_expl,
680 r_xp.node_depth as encl_depth,
681 c_xp.model_ref_expl_id as desc_expl
682 from
683 cz_model_ref_expls r_xp,
684 cz_model_ref_expls c_xp
685 where
686 -- retrieve the child expl nodes of both the ENCL and DESC nodes in the current pair
687 r_xp.parent_expl_node_id = pairs (cur_pair_index).enclosing_expl_id and
688 c_xp.parent_expl_node_id = pairs (cur_pair_index).descendant_expl_id and
689 -- restrict to expl pairs that match by component ID and by non-null REFERRING_NODE_ID
690 r_xp.component_id = c_xp.component_id and
691 decode (r_xp.referring_node_id, c_xp.referring_node_id, 1, 0) = 1 and
692 -- filter deleted expls
693 r_xp.deleted_flag = '0' and
694 c_xp.deleted_flag = '0'
695 )
696 loop
697 newpair := "SYSTEM".cz_expl_pair (
698 ENCLOSING_EXPL_ID => nextpair.encl_expl,
699 DESCENDANT_EXPL_ID => nextpair.desc_expl,
700 DESC_EXPL_REFERENCE_DEPTH => desc_ref_depth);
701 pairs.extend ();
702 paircount := paircount + 1;
703 pairs (paircount) := newpair;
704 curdepth := nextpair.encl_depth;
705 -- dbms_output.put_line ('ending inner loop with ' || to_char (pairs.last) || ' pairs and ' ||
706 -- to_char (frompairs.last) || ' in frompairs');
707 end loop;
708
709 end loop;
710
711 return pairs;
712 end get_parallel_expls;
713
714 END cz_model_util_pvt;