1 PACKAGE BODY cz_runtime AS
2 /* $Header: czrunb.pls 120.7 2007/05/04 19:01:22 qmao ship $ */
3 PS_NODE_BOM_MODEL_TYPE CONSTANT INTEGER := 436;
4 PS_NODE_REFERENCE_TYPE CONSTANT INTEGER := 263;
5 TYPE NUM_TBL_TYPE IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
6
7 G_MAX_PAGE_INCLUSION_DEPTH CONSTANT INTEGER := 1000000;
8
9 CONFIG_STATUS_COMPLETE CONSTANT VARCHAR2(1) := '2';
10 JRAD_STYLE_UI CONSTANT NUMBER := 7;
11
12
13 PROCEDURE sort_options ( p_ui_def_id IN NUMBER,
14 p_property_id IN NUMBER,
15 p_sort_order IN NUMBER,
16 x_sorted_table IN OUT NOCOPY system.cz_sort_tbl_type
17 )
18 IS
19 v_ui_features_ref NUM_TBL_TYPE;
20 v_ui_opt_meth_ref NUM_TBL_TYPE;
21 v_ui_opt_prop_ref NUM_TBL_TYPE;
22 v_ui_opt_ord_ref NUM_TBL_TYPE;
23 v_ui_nodes_tbl NUM_TBL_TYPE;
24 v_ui_nodes_ref NUM_TBL_TYPE;
25 v_ps_nodes_tbl NUM_TBL_TYPE;
26 v_ps_nodes_ref NUM_TBL_TYPE;
27 v_ui_feat_ref NUM_TBL_TYPE;
28 v_ui_node_count NUMBER;
29 v_property_data_type cz_properties.data_type%TYPE;
30 BEGIN
31 v_ui_features_ref.DELETE;
32 v_ui_opt_meth_ref.DELETE;
33 v_ui_opt_prop_ref.DELETE;
34 v_ui_opt_ord_ref.DELETE ;
35 x_sorted_table := system.cz_sort_tbl_type();
36 IF (p_ui_def_id IS NOT NULL) THEN
37 IF (p_property_id = -1) THEN
38 BEGIN
39 SELECT ui_node_id
40 ,option_sort_method
41 ,option_sort_property
42 ,option_sort_order
43 BULK
44 COLLECT
45 INTO v_ui_features_ref
46 ,v_ui_opt_meth_ref
47 ,v_ui_opt_prop_ref
48 ,v_ui_opt_ord_ref
49 FROM cz_ui_nodes
50 WHERE cz_ui_nodes.ui_def_id = p_ui_def_id
51 AND cz_ui_nodes.deleted_flag = '0'
52 AND cz_ui_nodes.ui_node_type = 148
53 AND cz_ui_nodes.option_sort_method = 2;
54 EXCEPTION
55 WHEN NO_DATA_FOUND THEN
56 x_sorted_table.DELETE;
57 WHEN OTHERS THEN
58 RAISE;
59 END;
60
61 ELSE
62 BEGIN
63 SELECT ui_node_id
64 ,option_sort_method
65 ,option_sort_property
66 ,option_sort_order
67 BULK
68 COLLECT
69 INTO v_ui_features_ref
70 ,v_ui_opt_meth_ref
71 ,v_ui_opt_prop_ref
72 ,v_ui_opt_ord_ref
73 FROM cz_ui_nodes
74 WHERE cz_ui_nodes.ui_def_id = p_ui_def_id
75 AND cz_ui_nodes.deleted_flag = '0'
76 AND cz_ui_nodes.ui_node_type = 148
77 AND cz_ui_nodes.option_sort_method IN (-1, 2);
78 EXCEPTION
79 WHEN NO_DATA_FOUND THEN
80 x_sorted_table.DELETE;
81 WHEN OTHERS THEN
82 RAISE;
83 END;
84 END IF;
85 END IF;
86 IF (v_ui_features_ref.COUNT > 0) THEN
87 v_ui_nodes_ref.DELETE;
88 v_ps_nodes_ref.DELETE;
89 v_ui_feat_ref.DELETE;
90 v_ui_node_count := 0.0;
91 FOR I IN v_ui_features_ref.FIRST..v_ui_features_ref.LAST
92 LOOP
93 IF ( (v_ui_features_ref(i) IS NOT NULL )
94 AND ( (v_ui_opt_meth_ref(i) = 2)
95 OR (v_ui_opt_meth_ref(i) = -1) ) ) THEN
96 v_ui_nodes_tbl.DELETE;
97 v_ps_nodes_tbl.DELETE;
98
99 IF (v_ui_opt_meth_ref(i) = -1) THEN
100 v_ui_opt_ord_ref(i) := p_sort_order;
101 v_ui_opt_prop_ref(i) := p_property_id;
102 END IF;
103
104 -----get the data_type of the property
105 IF (v_ui_opt_prop_ref(i) IS NOT NULL) THEN
106 BEGIN
107 SELECT data_type INTO v_property_data_type
108 FROM cz_properties
109 WHERE cz_properties.property_id = v_ui_opt_prop_ref(i)
110 AND cz_properties.deleted_flag = '0';
111 EXCEPTION
112 WHEN OTHERS THEN
113 RAISE;
114 END;
115 END IF;
116
117 IF (v_ui_opt_ord_ref(i) = 0) THEN
118
119 BEGIN
120 -----if datatype is integer or decimal then order numerically
121 IF (v_property_data_type IN (1,2,3) ) THEN
122
123 select v.ui_node_id, v.ps_node_id
124 bulk collect into v_ui_nodes_tbl, v_ps_nodes_tbl
125 from
126 (
127 SELECT ps_node_id, ui_node_id, parent_id, ps_node_name, property_id, property_name,
128 data_type, substr (min (cnct || property_value), 3) as
129 property_value,max (item_id) as item_id, max (item_type_id) as
130 item_type_id, devl_project_id,default_value
131 from (
132 select '1P' as cnct, psn1.devl_project_id, psn1.ps_node_id,
133 psn1.name as ps_node_name,psn1.parent_id, nvl (
134 psp1.data_value, prp1.def_value) as property_value,
135 prp1.name as property_name, prp1.property_id,
136 prp1.data_type, psn1.item_id, to_number (null)
137 as item_type_id, prp1.def_value as default_value, uin.ui_node_id
138 from cz_ps_nodes psn1,
139 cz_ps_prop_vals psp1,
140 CZ_UI_NODES uin,
141 cz_properties prp1
142 where psn1.deleted_flag = '0'
143 and psn1.ps_node_id =psp1.ps_node_id
144 and uin.ps_node_id = psn1.ps_node_id
145 and uin.PARENT_ID = v_ui_features_ref(i)
146 and uin.DELETED_FLAG = '0'
147 and psp1.deleted_flag = '0'
148 and psp1.property_id =prp1.property_id
149 and prp1.deleted_flag = '0'
150 union all
151 select '2I' as cnct, psn2.devl_project_id, psn2.ps_node_id,
152 psn2.name as ps_node_name, psn2.parent_id,nvl (
153 ipv2.property_value, prp2.def_value) as property_value,
154 prp2.name as property_name, prp2.property_id,
155 prp2.data_type,psn2.item_id, to_number (null) as item_type_id
156 , prp2.def_value as default_value, uin.ui_node_id
157 from cz_ps_nodes psn2,
158 cz_ui_nodes uin,
159 cz_item_property_values ipv2,
160 cz_properties prp2,
161 cz_item_masters itm2
162 where psn2.deleted_flag = '0'
163 and psn2.item_id = ipv2.item_id
164 and ipv2.deleted_flag = '0'
165 and ipv2.property_id = prp2.property_id
166 and uin.ps_node_id = psn2.ps_node_id
167 and uin.PARENT_ID = v_ui_features_ref(i)
168 and uin.DELETED_FLAG = '0'
169 and prp2.deleted_flag = '0'
170 and itm2.item_id = ipv2.item_id
171 union all
172 select
173 '3T' as cnct, psn3.devl_project_id, psn3.ps_node_id,
174 psn3.name as ps_node_name, psn3.parent_id, prp3.def_value
175 as property_value,prp3.name as property_name,
176 prp3.property_id,
177 prp3.data_type,psn3.item_id, itm3.item_type_id , prp3.def_value as
178 default_value, uin.ui_node_id
179 from
180 cz_ps_nodes psn3,
181 cz_item_masters itm3,
182 cz_ui_nodes uin,
183 cz_item_type_properties itp3,
184 cz_properties prp3
185 where psn3.deleted_flag = '0'
186 and psn3.item_id = itm3.item_id
187 and itm3.deleted_flag = '0'
188 and itm3.item_type_id = itp3.item_type_id
189 and uin.ps_node_id = psn3.ps_node_id
190 and uin.PARENT_ID = v_ui_features_ref(i)
191 and uin.DELETED_FLAG = '0'
192 and itp3.deleted_flag = '0'
193 and itp3.property_id = prp3.property_id
194 and prp3.deleted_flag = '0'
195 )
196 group by ps_node_id, parent_id, ps_node_name, devl_project_id,
197 property_id,property_name, data_type, default_value, ui_node_id
198 ) v
199 WHERE v.PROPERTY_ID = v_ui_opt_prop_ref(i)
200 ORDER BY To_number(V.property_value) DESC;
201
202 ELSE
203
204 select v.ui_node_id, v.ps_node_id
205 bulk collect into v_ui_nodes_tbl, v_ps_nodes_tbl
206 from
207 (
208 SELECT ps_node_id, ui_node_id, parent_id, ps_node_name, property_id, property_name,
209 data_type, substr (min (cnct || property_value), 3) as
210 property_value,max (item_id) as item_id, max (item_type_id) as
211 item_type_id,devl_project_id,default_value
212 from (
213 select '1P' as cnct, psn1.devl_project_id, psn1.ps_node_id,
214 psn1.name as ps_node_name,psn1.parent_id, nvl (
215 psp1.data_value, prp1.def_value) as property_value,
216 prp1.name as property_name, prp1.property_id,
217 prp1.data_type,psn1.item_id, to_number (null)
218 as item_type_id, prp1.def_value as default_value, uin.ui_node_id
219 from cz_ps_nodes psn1,
220 cz_ps_prop_vals psp1,
221 CZ_UI_NODES uin,
222 cz_properties prp1
223 where psn1.deleted_flag = '0'
224 and psn1.ps_node_id =psp1.ps_node_id
225 and uin.ps_node_id = psn1.ps_node_id
226 and uin.PARENT_ID = v_ui_features_ref(i)
227 and uin.DELETED_FLAG = '0'
228 and psp1.deleted_flag = '0'
229 and psp1.property_id =prp1.property_id
230 and prp1.deleted_flag = '0'
231 union all
232 select '2I' as cnct, psn2.devl_project_id, psn2.ps_node_id,
233 psn2.name as ps_node_name, psn2.parent_id,nvl (
234 ipv2.property_value, prp2.def_value) as property_value,
235 prp2.name as property_name, prp2.property_id,
236 prp2.data_type,psn2.item_id, to_number (null) as item_type_id
237 , prp2.def_value as default_value, uin.ui_node_id
238 from cz_ps_nodes psn2,
239 cz_ui_nodes uin,
240 cz_item_property_values ipv2,
241 cz_properties prp2,
242 cz_item_masters itm2
243 where psn2.deleted_flag = '0'
244 and psn2.item_id = ipv2.item_id
245 and ipv2.deleted_flag = '0'
246 and ipv2.property_id = prp2.property_id
247 and uin.ps_node_id = psn2.ps_node_id
248 and uin.PARENT_ID = v_ui_features_ref(i)
249 and uin.DELETED_FLAG = '0'
250 and prp2.deleted_flag = '0'
251 and itm2.item_id = ipv2.item_id
252 union all
253 select
254 '3T' as cnct, psn3.devl_project_id, psn3.ps_node_id,
255 psn3.name as ps_node_name, psn3.parent_id, prp3.def_value
256 as property_value,prp3.name as property_name,
257 prp3.property_id,
258 prp3.data_type,psn3.item_id, itm3.item_type_id , prp3.def_value as
259 default_value, uin.ui_node_id
260 from
261 cz_ps_nodes psn3,
262 cz_item_masters itm3,
263 cz_ui_nodes uin,
264 cz_item_type_properties itp3,
265 cz_properties prp3
266 where psn3.deleted_flag = '0'
267 and psn3.item_id = itm3.item_id
268 and itm3.deleted_flag = '0'
269 and itm3.item_type_id = itp3.item_type_id
270 and uin.ps_node_id = psn3.ps_node_id
271 and uin.PARENT_ID = v_ui_features_ref(i)
272 and uin.DELETED_FLAG = '0'
273 and itp3.deleted_flag = '0'
274 and itp3.property_id = prp3.property_id
275 and prp3.deleted_flag = '0'
276 )
277 group by ps_node_id, parent_id, ps_node_name, devl_project_id,
278 property_id,property_name, data_type, default_value, ui_node_id
279 ) v
280 WHERE v.PROPERTY_ID = v_ui_opt_prop_ref(i)
281 ORDER BY V.PROPERTY_VALUE DESC;
282
283 END IF;
284 EXCEPTION
285 WHEN NO_DATA_FOUND THEN
286 NULL;
287 WHEN OTHERS THEN
288 RAISE;
289 END;
290 ELSIF (v_ui_opt_ord_ref(i) = 1) THEN
291
292 BEGIN
293
294 IF (v_property_data_type IN (1,2) ) THEN
295 select v.ui_node_id, v.ps_node_id
296 bulk collect into v_ui_nodes_tbl, v_ps_nodes_tbl
297 from
298 (
299 SELECT ps_node_id, ui_node_id, parent_id, ps_node_name, property_id, property_name,
300 data_type, substr (min (cnct || property_value), 3) as
301 property_value,max (item_id) as item_id, max (item_type_id) as
302 item_type_id,devl_project_id,default_value
303 from (
304 select '1P' as cnct, psn1.devl_project_id, psn1.ps_node_id,
305 psn1.name as ps_node_name,psn1.parent_id, nvl (
306 psp1.data_value, prp1.def_value) as property_value,
307 prp1.name as property_name, prp1.property_id,
308 prp1.data_type,psn1.item_id, to_number (null)
309 as item_type_id, prp1.def_value as default_value, uin.ui_node_id
310 from cz_ps_nodes psn1,
311 cz_ps_prop_vals psp1,
312 CZ_UI_NODES uin,
313 cz_properties prp1
314 where psn1.deleted_flag = '0'
315 and psn1.ps_node_id =psp1.ps_node_id
316 and uin.ps_node_id = psn1.ps_node_id
317 and uin.PARENT_ID = v_ui_features_ref(i)
318 and uin.DELETED_FLAG = '0'
319 and psp1.deleted_flag = '0'
320 and psp1.property_id =prp1.property_id
321 and prp1.deleted_flag = '0'
322 union all
323 select '2I' as cnct, psn2.devl_project_id, psn2.ps_node_id,
324 psn2.name as ps_node_name, psn2.parent_id,nvl (
325 ipv2.property_value, prp2.def_value) as property_value,
326 prp2.name as property_name, prp2.property_id,
327 prp2.data_type,psn2.item_id, to_number (null) as item_type_id
328 , prp2.def_value as default_value, uin.ui_node_id
329 from cz_ps_nodes psn2,
330 cz_ui_nodes uin,
331 cz_item_property_values ipv2,
332 cz_properties prp2,
333 cz_item_masters itm2
334 where psn2.deleted_flag = '0'
335 and psn2.item_id = ipv2.item_id
336 and ipv2.deleted_flag = '0'
337 and ipv2.property_id = prp2.property_id
338 and uin.ps_node_id = psn2.ps_node_id
339 and uin.PARENT_ID = v_ui_features_ref(i)
340 and uin.DELETED_FLAG = '0'
341 and prp2.deleted_flag = '0'
342 and itm2.item_id = ipv2.item_id
343 union all
344 select
345 '3T' as cnct, psn3.devl_project_id, psn3.ps_node_id,
346 psn3.name as ps_node_name, psn3.parent_id, prp3.def_value
347 as property_value,prp3.name as property_name,
348 prp3.property_id,
349 prp3.data_type,psn3.item_id, itm3.item_type_id , prp3.def_value as
350 default_value, uin.ui_node_id
351 from
352 cz_ps_nodes psn3,
353 cz_item_masters itm3,
354 cz_ui_nodes uin,
355 cz_item_type_properties itp3,
356 cz_properties prp3
357 where psn3.deleted_flag = '0'
358 and psn3.item_id = itm3.item_id
359 and itm3.deleted_flag = '0'
360 and itm3.item_type_id = itp3.item_type_id
361 and uin.ps_node_id = psn3.ps_node_id
362 and uin.PARENT_ID = v_ui_features_ref(i)
363 and uin.DELETED_FLAG = '0'
364 and itp3.deleted_flag = '0'
365 and itp3.property_id = prp3.property_id
366 and prp3.deleted_flag = '0'
367 )
368 group by ps_node_id, parent_id, ps_node_name, devl_project_id,
369 property_id,property_name, data_type, default_value, ui_node_id
370 ) v
371 WHERE v.PROPERTY_ID = v_ui_opt_prop_ref(i)
372 ORDER BY To_number(V.property_value) ASC;
373
374
375 ELSE
376 select v.ui_node_id, v.ps_node_id
377 bulk collect into v_ui_nodes_tbl, v_ps_nodes_tbl
378 from
379 (
380 SELECT ps_node_id, ui_node_id, parent_id, ps_node_name, property_id, property_name,
381 data_type, substr (min (cnct || property_value), 3) as
382 property_value,max (item_id) as item_id, max (item_type_id) as
383 item_type_id,devl_project_id,default_value
384 from (
385 select '1P' as cnct, psn1.devl_project_id, psn1.ps_node_id,
386 psn1.name as ps_node_name,psn1.parent_id, nvl (
387 psp1.data_value, prp1.def_value) as property_value,
388 prp1.name as property_name, prp1.property_id,
389 prp1.data_type, psn1.item_id, to_number (null)
390 as item_type_id, prp1.def_value as default_value, uin.ui_node_id
391 from cz_ps_nodes psn1,
392 cz_ps_prop_vals psp1,
393 CZ_UI_NODES uin,
394 cz_properties prp1
395 where psn1.deleted_flag = '0'
396 and psn1.ps_node_id =psp1.ps_node_id
397 and uin.ps_node_id = psn1.ps_node_id
398 and uin.PARENT_ID = v_ui_features_ref(i)
399 and uin.DELETED_FLAG = '0'
400 and psp1.deleted_flag = '0'
401 and psp1.property_id =prp1.property_id
402 and prp1.deleted_flag = '0'
403 union all
404 select '2I' as cnct, psn2.devl_project_id, psn2.ps_node_id,
405 psn2.name as ps_node_name, psn2.parent_id,nvl (
406 ipv2.property_value, prp2.def_value) as property_value,
407 prp2.name as property_name, prp2.property_id,
408 prp2.data_type, psn2.item_id, to_number (null) as item_type_id
409 , prp2.def_value as default_value, uin.ui_node_id
410 from cz_ps_nodes psn2,
411 cz_ui_nodes uin,
412 cz_item_property_values ipv2,
413 cz_properties prp2,
414 cz_item_masters itm2
415 where psn2.deleted_flag = '0'
416 and psn2.item_id = ipv2.item_id
417 and ipv2.deleted_flag = '0'
418 and ipv2.property_id = prp2.property_id
419 and uin.ps_node_id = psn2.ps_node_id
420 and uin.PARENT_ID = v_ui_features_ref(i)
421 and uin.DELETED_FLAG = '0'
422 and prp2.deleted_flag = '0'
423 and itm2.item_id = ipv2.item_id
424 union all
425 select
426 '3T' as cnct, psn3.devl_project_id, psn3.ps_node_id,
427 psn3.name as ps_node_name, psn3.parent_id, prp3.def_value
428 as property_value,prp3.name as property_name,
429 prp3.property_id,
430 prp3.data_type, psn3.item_id, itm3.item_type_id , prp3.def_value as
431 default_value, uin.ui_node_id
432 from
433 cz_ps_nodes psn3,
434 cz_item_masters itm3,
435 cz_ui_nodes uin,
436 cz_item_type_properties itp3,
437 cz_properties prp3
438 where psn3.deleted_flag = '0'
439 and psn3.item_id = itm3.item_id
440 and itm3.deleted_flag = '0'
441 and itm3.item_type_id = itp3.item_type_id
442 and uin.ps_node_id = psn3.ps_node_id
443 and uin.PARENT_ID = v_ui_features_ref(i)
444 and uin.DELETED_FLAG = '0'
445 and itp3.deleted_flag = '0'
446 and itp3.property_id = prp3.property_id
447 and prp3.deleted_flag = '0'
448 )
449 group by ps_node_id, parent_id, ps_node_name, devl_project_id,
450 property_id,property_name, data_type, default_value, ui_node_id
451 ) v
452 WHERE v.PROPERTY_ID = v_ui_opt_prop_ref(i)
453 ORDER BY V.PROPERTY_VALUE ASC; END IF;
454 EXCEPTION
455 WHEN NO_DATA_FOUND THEN
456 NULL;
457 WHEN OTHERS THEN
458 RAISE;
459 END;
460 ELSE
461 -----do nothing
462 NULL;
463 END IF;
464 END IF;
465 v_ui_node_count := v_ui_nodes_ref.COUNT;
466 IF (v_ui_nodes_tbl.COUNT > 0) THEN
467 FOR J IN v_ui_nodes_tbl.FIRST..v_ui_nodes_tbl.LAST
468 LOOP
469 IF(v_ui_nodes_tbl(j) IS NOT NULL) THEN
470 v_ui_node_count := v_ui_node_count + 1;
471 v_ui_nodes_ref(v_ui_node_count) := v_ui_nodes_tbl(j);
472 v_ps_nodes_ref(v_ui_node_count) := v_ps_nodes_tbl(j);
473 v_ui_feat_ref(v_ui_node_count) := j;
474 END IF;
475 END LOOP;
476 END IF;
477 END LOOP;
478 END IF;
479 IF (v_ui_nodes_ref.COUNT > 0) THEN
480 x_sorted_table.EXTEND(v_ui_nodes_ref.COUNT);
481 FOR final IN v_ui_nodes_ref.FIRST..v_ui_nodes_ref.LAST
482 LOOP
483 x_sorted_table(final) := system.cz_sort_obj_type(v_ui_nodes_ref(final),v_ui_feat_ref(final));
484 END LOOP;
485 END IF;
486 EXCEPTION
487 WHEN OTHERS THEN
488 RAISE_APPLICATION_ERROR (-20001,SQLERRM);
489 END;
490 ------------------------------------------------------------------------
491 -- checks if a node (p_parent_id) is the ancestor of the other node (p_node_id)
492 FUNCTION check_parent(p_parent_id IN NUMBER, p_node_id IN NUMBER, p_model_id IN NUMBER)
493 RETURN BOOLEAN
494 IS
495 CURSOR ps_node_cursor IS
496 SELECT ps_node_id
497 FROM cz_ps_nodes
498 WHERE devl_project_id = p_model_id
499 START WITH ps_node_id = p_node_id
500 CONNECT BY ps_node_id = PRIOR parent_ID;
501 BEGIN
502 FOR ps_node_rec IN ps_node_cursor LOOP
503 EXIT WHEN ps_node_cursor%NOTFOUND;
504 IF (ps_node_rec.ps_node_id = p_parent_id) THEN
505 RETURN TRUE;
506 END IF;
507 END LOOP;
508 RETURN FALSE;
509 END check_parent;
510 ---------------------------------------------------------------------------------
511 -- identifies contained bom models in a model and returns root node id(s) of found bom(s)
512 PROCEDURE get_root_bom_node_internal(p_model_id IN NUMBER,
513 p_ps_node_id IN OUT NOCOPY NUMBER,
514 p_ids IN OUT NOCOPY NUM_TBL_TYPE
515 )
516 IS
517 v_persistent_node_id NUMBER;
518 v_ps_node_id NUMBER;
519 v_parent_id NUMBER;
520 v_ps_node_type NUMBER;
521 v_reference_id NUMBER;
522 v_bom_model_id NUMBER;
523 v_ids NUM_TBL_TYPE;
524 CURSOR ps_node_cursor IS
525 SELECT persistent_node_id, ps_node_id, parent_id, ps_node_type, reference_id
526 FROM CZ_PS_NODES
527 WHERE devl_project_id = p_model_id
528 AND ps_node_type in (PS_NODE_BOM_MODEL_TYPE, PS_NODE_REFERENCE_TYPE)
529 AND deleted_flag = '0'
530 ORDER BY ps_node_type DESC;
531 BEGIN
532 FOR ps_node_rec IN ps_node_cursor LOOP
533 EXIT WHEN ps_node_cursor%NOTFOUND;
534 v_persistent_node_id := ps_node_rec.persistent_node_id;
535 v_ps_node_id := ps_node_rec.ps_node_id;
536 v_parent_id := ps_node_rec.parent_id;
537 v_ps_node_type := ps_node_rec.ps_node_type;
538 v_reference_id := ps_node_rec.reference_id;
539 IF (v_ps_node_type = PS_NODE_BOM_MODEL_TYPE) THEN
540 p_ps_node_id := v_ps_node_id;
541 p_ids(p_ids.count + 1) := v_persistent_node_id;
542 IF (v_parent_id IS NULL) THEN
543 RETURN;
544 ELSE
545 v_bom_model_id := v_ps_node_id;
546 END IF;
547 ELSE
548 -- recursively looks up further by reference_id if no bom model found yet so far
549 -- or found a bom model but the referring node is not a descendant of the bom root
550 IF (v_bom_model_id IS NULL OR (NOT check_parent(v_bom_model_id, v_ps_node_id, p_model_id))) THEN
551 get_root_bom_node_internal(v_reference_id, p_ps_node_id, p_ids);
552 END IF;
553 END IF;
554 END LOOP;
555 END get_root_bom_node_internal;
556 --------------------------------------------------------------------------
557 /* wrapper for getting root bom node id(s) in a model
558 Aug. 28, 2001 created for dio root bom node looking up
559 Sept. 18, 2001 modified to set p_err_flag to 2 if no bom found for publication
560 */
561 PROCEDURE get_root_bom_node(p_model_id IN NUMBER,
562 p_persistent_node_id OUT NOCOPY NUMBER,
563 p_ps_node_id OUT NOCOPY NUMBER,
564 p_err_flag OUT NOCOPY VARCHAR2,
565 p_err_msg OUT NOCOPY VARCHAR2
566 )
567 IS
568 v_ids NUM_TBL_TYPE;
569 BEGIN
570 get_root_bom_node_internal(p_model_id, p_ps_node_id, v_ids);
571 IF (v_ids.count = 0) THEN
572 p_err_flag := 2;
573 p_err_msg := 'ERROR: No BOM component found in model ' || TO_CHAR(p_model_id);
574 ELSIF (v_ids.count = 1) THEN
575 p_err_flag := '0';
576 p_persistent_node_id := v_ids(1);
577 ELSE
578 p_err_flag := '1';
579 p_err_msg := 'ERROR: Two bom models exist in model ' || TO_CHAR(p_model_id) ||
580 ' at different subtrees (root bom persistent node id=' || TO_CHAR(v_ids(1)) || ',' || TO_CHAR(v_ids(2)) || ').';
581 END IF;
582 END get_root_bom_node;
583
584 ---------------------------------------------------------------------------------
585 PROCEDURE get_config_info(p_config_hdr_id IN NUMBER
586 ,p_config_rev_nbr IN NUMBER
587 ,x_component_id OUT NOCOPY NUMBER
588 ,x_top_item_id OUT NOCOPY NUMBER
589 ,x_organization_id OUT NOCOPY NUMBER
590 ,x_quantity OUT NOCOPY NUMBER
591 ,x_usage_name OUT NOCOPY VARCHAR2
592 ,x_effective_date OUT NOCOPY DATE
593 ,x_config_date_created OUT NOCOPY DATE
594 ,x_complete_flag OUT NOCOPY VARCHAR2
595 ,x_valid_flag OUT NOCOPY VARCHAR2
596 ,x_return_status OUT NOCOPY VARCHAR2
597 ,x_msg_data OUT NOCOPY VARCHAR2)
598 IS
599 l_config_status cz_config_hdrs.config_status%TYPE;
600 l_node_identifier cz_config_items.node_identifier%TYPE;
601 l_config_item_id cz_config_items.config_item_id%TYPE := NULL;
602 l_column_name VARCHAR2(80);
603 l_dummy NUMBER;
604
605 null_db_value_exc EXCEPTION;
606 no_bom_item_found_exc EXCEPTION;
607
608 BEGIN
609 SELECT component_id, config_status, config_date_created, effective_date, usg.name
610 INTO x_component_id, l_config_status, x_config_date_created, x_effective_date, x_usage_name
611 FROM cz_config_hdrs hdr, cz_model_usages usg
612 WHERE config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
613 AND deleted_flag = '0'
614 AND hdr.effective_usage_id = usg.model_usage_id;
615
616 IF (x_component_id IS NULL) THEN
617 l_column_name := 'component_id';
618 RAISE null_db_value_exc;
619 END IF;
620
621 IF (l_config_status IS NULL) THEN
622 l_column_name := 'config_status';
623 RAISE null_db_value_exc;
624 ELSIF (l_config_status = CONFIG_STATUS_COMPLETE) THEN
625 x_complete_flag := FND_API.G_TRUE;
626 ELSE
627 x_complete_flag := FND_API.G_FALSE;
628 END IF;
629
630 IF (x_config_date_created IS NULL) THEN
631 l_column_name := 'config_date_created';
632 RAISE null_db_value_exc;
633 END IF;
634
635 IF (x_effective_date IS NULL) THEN
636 l_column_name := 'effective_date';
637 RAISE null_db_value_exc;
638 END IF;
639
640 BEGIN
641 SELECT 1 INTO l_dummy
642 FROM cz_config_messages
643 WHERE config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
644 AND deleted_flag = '0' AND ROWNUM < 2;
645
646 x_valid_flag := FND_API.G_FALSE;
647
648 EXCEPTION
649 WHEN NO_DATA_FOUND THEN
650 x_valid_flag := FND_API.G_TRUE;
651 END;
652
653 BEGIN
654 SELECT node_identifier INTO l_node_identifier
655 FROM cz_config_items
656 WHERE config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
657 AND deleted_flag = '0'
658 AND node_identifier IS NOT NULL AND node_identifier <> 'PRD'
659 AND ROWNUM < 2;
660 EXCEPTION
661 WHEN NO_DATA_FOUND THEN
662 RAISE no_bom_item_found_exc;
663 END;
664
665 l_dummy := instr(l_node_identifier, '-');
666 IF (l_dummy > 0) THEN
667 x_top_item_id := to_number(substr(l_node_identifier, 1, l_dummy-1));
668 ELSE
669 x_top_item_id := to_number(l_node_identifier);
670 END IF;
671
672 SELECT organization_id, quantity, config_item_id
673 INTO x_organization_id, x_quantity, l_config_item_id
674 FROM cz_config_details_v
675 WHERE config_hdr_id = p_config_hdr_id AND config_rev_nbr = p_config_rev_nbr
676 AND inventory_item_id = x_top_item_id;
677
678 IF (x_organization_id IS NULL) THEN
679 l_column_name := 'organization_id';
680 RAISE null_db_value_exc;
681 END IF;
682
683 IF (x_quantity IS NULL) THEN
684 l_column_name := 'item_val';
685 RAISE null_db_value_exc;
686 END IF;
687
688 x_return_status := FND_API.G_RET_STS_SUCCESS;
689
690 EXCEPTION
691 WHEN no_data_found THEN
692 x_return_status := FND_API.G_RET_STS_ERROR;
693 x_msg_data := 'No such config header found in database with header id ' || to_char(p_config_hdr_id)
694 || ', revision ' || to_char(p_config_rev_nbr);
695
696 WHEN null_db_value_exc THEN
697 x_return_status := FND_API.G_RET_STS_ERROR;
698 x_msg_data := 'Data Error: ' || l_column_name || ' is NULL for record - config_hdr_id ' ||
699 to_char(p_config_hdr_id) || ', config_rev_nbr ' || to_char(p_config_rev_nbr);
700 IF (l_config_item_id IS NOT NULL) THEN
701 x_msg_data := x_msg_data || ', config_item_id ' || to_char(l_config_item_id);
702 END IF;
703
704 WHEN no_bom_item_found_exc THEN
705 x_return_status := FND_API.G_RET_STS_ERROR;
706 x_msg_data := 'No config item found having an inventory_item_id for the input config' ||
707 ': config_hdr_id ' || to_char(p_config_hdr_id) ||
708 ', config_rev_nbr ' || to_char(p_config_rev_nbr);
709
710 WHEN OTHERS THEN
711 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
712 x_msg_data := SQLERRM;
713
714 END get_config_info;
715
716 ----------------------------------
717 ----This procedure is called when a configurator runtime UI is
718 ----launched from embedded JRAD Region
719 ----The API returns NULL if the ui_style on the publication
720 ----is not a JRAD style UI otherwise it returns a publictaion_id.
721 FUNCTION embedded_publication_for_item (inventory_item_id IN NUMBER,
722 organization_id IN NUMBER,
723 config_lookup_date IN DATE,
724 calling_application_id IN NUMBER,
725 usage_name IN VARCHAR2,
726 publication_mode IN VARCHAR2 DEFAULT NULL,
727 language IN VARCHAR2 DEFAULT NULL
728 )
729 RETURN NUMBER
730 IS
731
732 l_publication_id cz_model_publications.publication_id%TYPE;
733 l_ui_style cz_model_publications.ui_style%TYPE;
734
735 BEGIN
736 l_publication_id := cz_cf_api.publication_for_item (inventory_item_id,organization_id,config_lookup_date,
737 calling_application_id,usage_name,publication_mode,language);
738
739 IF (l_publication_id IS NOT NULL) THEN
740 BEGIN
741 Select ui_style INTO l_ui_style
742 from cz_model_publications
743 where cz_model_publications.publication_id = l_publication_id
744 and cz_model_publications.deleted_flag = '0';
745 EXCEPTION
746 WHEN NO_DATA_FOUND THEN
747 l_ui_style := -1;
748 END;
749 IF (l_ui_style <> JRAD_STYLE_UI) THEN
750 RETURN NULL;
751 END IF;
752 END IF;
753 RETURN l_publication_id;
754 END embedded_publication_for_item ;
755
756 -------------------------------------
757 ----This procedure is called when a configurator runtime UI is
758 ----launched from embedded JRAD Region
759 ----The API returns NULL if the ui_style on the publication
760 ----is not a JRAD style UI otherwise it returns a publictaion_id.
761
762 FUNCTION embedded_pubId_for_product(product_key IN VARCHAR2,
763 config_lookup_date IN DATE,
764 calling_application_id IN NUMBER,
765 usage_name IN VARCHAR2,
766 publication_mode IN VARCHAR2 DEFAULT NULL,
767 language IN VARCHAR2 DEFAULT NULL
768 )
769 RETURN NUMBER
770 IS
771
772 l_publication_id cz_model_publications.publication_id%TYPE;
773 l_ui_style cz_model_publications.ui_style%TYPE;
774
775 BEGIN
776 l_publication_id := cz_cf_api.publication_for_product(product_key,
777 config_lookup_date,
778 calling_application_id,
779 usage_name,
780 publication_mode,
781 language);
782 IF (l_publication_id IS NOT NULL) THEN
783 BEGIN
784 Select ui_style INTO l_ui_style
785 from cz_model_publications
786 where cz_model_publications.publication_id = l_publication_id
787 and cz_model_publications.deleted_flag = '0';
788 EXCEPTION
789 WHEN NO_DATA_FOUND THEN
790 l_ui_style := -1;
791 END;
792 IF (l_ui_style <> JRAD_STYLE_UI) THEN
793 RETURN NULL;
794 END IF;
795 END IF;
796 RETURN l_publication_id;
797 END embedded_pubId_for_product;
798
799 ---------------------------------------
800 ----This procedure is called when a configurator runtime UI is
801 ----launched from embedded JRAD Region
802 ----The API returns NULL if the ui_style on the publication
803 ----is not a JRAD style UI otherwise it returns a publictaion_id.
804
805 FUNCTION embedded_pub_for_savedconfig (config_hdr_id IN NUMBER,
806 config_rev_nbr IN NUMBER,
807 config_lookup_date IN DATE,
808 calling_application_id IN NUMBER,
809 usage_name IN VARCHAR2,
810 publication_mode IN VARCHAR2 DEFAULT NULL,
811 language IN VARCHAR2 DEFAULT NULL
812 )
813 RETURN NUMBER
814 IS
815
816 l_publication_id cz_model_publications.publication_id%TYPE;
817 l_ui_style cz_model_publications.ui_style%TYPE;
818
819 BEGIN
820 l_publication_id := cz_cf_api.publication_for_saved_config (config_hdr_id,
821 config_rev_nbr,
822 config_lookup_date,
823 calling_application_id,
824 usage_name,
825 publication_mode,
826 language
827 );
828 IF (l_publication_id IS NOT NULL) THEN
829 BEGIN
830 Select ui_style INTO l_ui_style
831 from cz_model_publications
832 where cz_model_publications.publication_id = l_publication_id
833 and cz_model_publications.deleted_flag = '0';
834 EXCEPTION
835 WHEN NO_DATA_FOUND THEN
836 l_ui_style := -1;
837 END;
838 IF (l_ui_style <> JRAD_STYLE_UI) THEN
839 RETURN NULL;
840 END IF;
841 END IF;
842 RETURN l_publication_id;
843
844 END embedded_pub_for_savedconfig ;
845
846 ------------------------------------------
847
848 FUNCTION annotated_node_path
849 (p_root_model_id IN NUMBER,
850 p_target_page_expl_id IN NUMBER,
851 p_target_ui_def_id IN NUMBER,
852 p_target_page_persist_id IN NUMBER,
853 p_root_model_expl_id IN NUMBER
854 ) RETURN VARCHAR2 IS
855
856 l_component_id NUMBER;
857 l_referring_node_id NUMBER;
858 l_model_id NUMBER;
859 l_model_ref_expl_id NUMBER;
860 l_root_persist_id NUMBER;
861 l_target_ps_node_id NUMBER;
862 l_path VARCHAR2(2000);
863
864 BEGIN
865 SELECT component_id,model_id INTO l_component_id, l_model_id FROM CZ_MODEL_REF_EXPLS
866 WHERE model_ref_expl_id=p_target_page_expl_id;
867
868 SELECT persistent_node_id INTO l_root_persist_id FROM CZ_PS_NODES
869 WHERE devl_project_id=p_root_model_id AND parent_id IS NULL AND deleted_flag='0';
870
871 IF p_root_model_id=l_model_id THEN
872 SELECT ps_node_id INTO l_target_ps_node_id FROM CZ_PS_NODES WHERE devl_project_id=l_model_id AND
873 persistent_node_id=p_target_page_persist_id AND deleted_flag='0';
874 SELECT model_ref_expl_id INTO l_model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
875 WHERE model_id=l_model_id AND parent_expl_node_id IS NULL AND deleted_flag='0';
876
877 l_path := cz_developer_utils_pvt.annotated_node_path (p_root_model_id,
878 p_target_page_expl_id,
879 l_target_ps_node_id);
880 IF l_path IS NULL THEN
881 RETURN To_char(l_root_persist_id);
882 ELSE
883 RETURN To_char(l_root_persist_id)||'.'||l_path;
884 END IF;
885
886 ELSE
887
888 FOR l IN(SELECT component_id,referring_node_id FROM CZ_MODEL_REF_EXPLS
889 WHERE ps_node_type=263
890 START WITH model_ref_expl_id=p_root_model_expl_id
891 CONNECT BY PRIOR parent_expl_node_id=model_ref_expl_id AND deleted_flag='0'
892 AND PRIOR deleted_flag='0')
893 LOOP
894 BEGIN
895 SELECT l.referring_node_id INTO l_referring_node_id FROM CZ_MODEL_REF_EXPLS
896 WHERE model_id=l.component_id AND model_ref_expl_id=p_target_page_expl_id;
897 EXIT;
898 EXCEPTION
899 WHEN NO_DATA_FOUND THEN
900 NULL;
901 END;
902 END LOOP;
903
904 FOR i IN(SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
905 WHERE referring_node_id=l_referring_node_id AND deleted_flag='0'
906 START WITH model_id=p_root_model_id AND parent_expl_node_id IS NULL AND deleted_flag='0'
907 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag='0'
908 AND PRIOR deleted_flag='0')
909 LOOP
910 FOR j IN (SELECT model_ref_expl_id FROM CZ_MODEL_REF_EXPLS
911 WHERE component_id=l_component_id AND deleted_flag='0'
912 START WITH model_ref_expl_id=i.model_ref_expl_id
913 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag='0'
914 AND PRIOR deleted_flag='0')
915 LOOP
916
917 SELECT ps_node_id INTO l_target_ps_node_id FROM CZ_PS_NODES
918 WHERE devl_project_id=(SELECT devl_project_id FROM CZ_UI_DEFS WHERE ui_def_id=p_target_ui_def_id) AND
919 persistent_node_id=p_target_page_persist_id AND deleted_flag='0';
920
921 RETURN TO_CHAR(l_root_persist_id)||'.'||cz_developer_utils_pvt.annotated_node_path (
922 p_root_model_id,
923 j.model_ref_expl_id,
924 l_target_ps_node_id);
925 END LOOP;
926 END LOOP;
927
928 END IF;
929 RETURN NULL;
930 END annotated_node_path;
931
932 FUNCTION get_TARGET_PAGE_REF_DEPTH
933 (p_root_model_id IN NUMBER,
934 p_target_page_expl_id IN NUMBER,
935 p_target_ui_def_id IN NUMBER,
936 p_root_model_expl_id IN NUMBER) RETURN NUMBER IS
937
938 l_component_id NUMBER;
939 l_referring_node_id NUMBER;
940 l_model_id NUMBER;
941
942 BEGIN
943
944 SELECT component_id,model_id INTO l_component_id,l_model_id FROM CZ_MODEL_REF_EXPLS
945 WHERE model_ref_expl_id=p_target_page_expl_id;
946
947 IF p_root_model_id=l_model_id THEN
948 RETURN 0;
949 END IF;
950
951 FOR l IN(SELECT component_id,referring_node_id FROM CZ_MODEL_REF_EXPLS
952 WHERE ps_node_type=263
953 START WITH model_ref_expl_id=p_root_model_expl_id
954 CONNECT BY PRIOR parent_expl_node_id=model_ref_expl_id AND deleted_flag='0'
955 AND PRIOR deleted_flag='0')
956 LOOP
957 BEGIN
958 SELECT l.referring_node_id INTO l_referring_node_id FROM CZ_MODEL_REF_EXPLS
959 WHERE model_id=l.component_id AND model_ref_expl_id=p_target_page_expl_id;
960 EXIT;
961 EXCEPTION
962 WHEN NO_DATA_FOUND THEN
963 NULL;
964 END;
965 END LOOP;
966
967 FOR i IN(SELECT model_ref_expl_id,node_depth FROM CZ_MODEL_REF_EXPLS
968 WHERE referring_node_id=l_referring_node_id AND deleted_flag='0'
969 START WITH model_id=p_root_model_id AND parent_expl_node_id IS NULL AND deleted_flag='0'
970 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag='0'
971 AND PRIOR deleted_flag='0')
972 LOOP
973 FOR j IN (SELECT node_depth FROM CZ_MODEL_REF_EXPLS
974 WHERE component_id=l_component_id AND deleted_flag='0'
975 START WITH model_ref_expl_id=i.model_ref_expl_id
976 CONNECT BY PRIOR model_ref_expl_id=parent_expl_node_id AND deleted_flag='0'
977 AND PRIOR deleted_flag='0')
978 LOOP
979 RETURN i.node_depth;
980 END LOOP;
981 END LOOP;
982 RETURN 0;
983 END get_TARGET_PAGE_REF_DEPTH;
984
985
986 PROCEDURE get_Target_UI_Pages(p_root_ui_def_id IN NUMBER,
987 p_root_model_expl_id IN NUMBER,
988 p_root_model_node_id IN NUMBER,
989 p_node_collection_flag IN VARCHAR2,
990 p_curr_ui_def_id IN NUMBER,
991 p_curr_page_id IN NUMBER,
992 p_order_by_template IN NUMBER,
993 x_ui_page_tbl OUT NOCOPY SYSTEM.cz_tgt_ui_page_tbl) IS
994
995 TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
996 l_ui_pages_tbl0 SYSTEM.CZ_UI_PAGE_ELEM_TBL := SYSTEM.CZ_UI_PAGE_ELEM_TBL(SYSTEM.CZ_UI_PAGE_ELEM_REC(NULL,NULL,NULL));
997 l_ui_pages_tbl1 SYSTEM.CZ_UI_PAGE_ELEM_TBL := SYSTEM.CZ_UI_PAGE_ELEM_TBL(SYSTEM.CZ_UI_PAGE_ELEM_REC(NULL,NULL,NULL));
998
999 l_hash_map number_tbl_type;
1000 l_root_model_id NUMBER;
1001 l_tbl_counter NUMBER;
1002
1003 BEGIN
1004
1005 x_ui_page_tbl := SYSTEM.cz_tgt_ui_page_tbl(SYSTEM.CZ_TGT_UI_PAGE_REC(NULL,NULL,NULL,NULL,NULL,NULL));
1006 l_tbl_counter := 1;
1007
1008 SELECT devl_project_id INTO l_root_model_id
1009 FROM CZ_UI_DEFS WHERE ui_def_id=p_root_ui_def_id;
1010
1011
1012 FOR k IN(SELECT vv.target_page_id, vv.target_ui_def_id,vv.element_id,vv.bound_via_parent_flag from cz_uipages_for_explnodes_v vv
1013 WHERE vv.root_ui_def_id = p_root_ui_def_id AND
1014 vv.root_model_expl_id = p_root_model_expl_id AND
1015 vv.root_model_node_id = p_root_model_node_id AND
1016 vv.node_collection_flag=p_node_collection_flag)
1017 LOOP
1018 IF k.bound_via_parent_flag='0' THEN
1019 l_ui_pages_tbl0.extend;
1020 l_ui_pages_tbl0(l_ui_pages_tbl0.LAST) := SYSTEM.CZ_UI_PAGE_ELEM_REC(k.target_ui_def_id,k.target_page_id,k.element_id);
1021 ELSE
1022 l_ui_pages_tbl1.extend;
1023 l_ui_pages_tbl1(l_ui_pages_tbl1.LAST) := SYSTEM.CZ_UI_PAGE_ELEM_REC(k.target_ui_def_id,k.target_page_id,k.element_id);
1024 END IF;
1025 END LOOP;
1026
1027 IF p_order_by_template=0 THEN
1028
1029 -- Bug 5129001 - Performance team suggested CARDINALITY hints below for fixing 10G optimizer problem.
1030 FOR i IN(SELECT * FROM (
1031 SELECT
1032 uipages.ui_def_id AS TARGET_UI_DEF_ID,
1033 uipages.page_id AS TARGET_PAGE_ID,
1034 (SELECT annotated_node_path(l_root_model_id,
1035 UIPAGES.PAGEBASE_EXPL_NODE_ID,UIPAGES.ui_def_id,UIPAGES.persistent_node_id,p_root_model_expl_id)
1036 FROM dual
1037 ) AS TARGET_PAGE_ANN_PATH,
1038 uipages.DISPLAY_CONDITION_ID AS TARGET_PAGE_DISPCOND_ID,
1039 uipages.DISPLAY_CONDITION_COMP AS TARGET_PAGE_DISPCOND_COMP,
1040 uipages.DISPLAY_CONDITION_VALUE AS TARGET_PAGE_DISPCOND_VALUE,
1041 uiels.PAGE_LEVEL AS PAGE_INCLUSION_DEPTH,
1042 uiels.element_id,
1043 uiels.ui_def_id,
1044 uiels.page_id,
1045 (SELECT get_TARGET_PAGE_REF_DEPTH(l_root_model_id,
1046 UIPAGES.PAGEBASE_EXPL_NODE_ID,UIPAGES.ui_def_id,p_root_model_expl_id)
1047 FROM dual
1048 ) AS TARGET_PAGE_REFERENCE_DEPTH,
1049 '0' AS BOUND_VIA_PARENT_FLAG
1050 FROM
1051 CZ_UI_PAGES uipages,
1052 (SELECT els.ui_def_id,els.page_id,els.element_id,level as PAGE_LEVEL FROM CZ_UI_PAGE_ELEMENTS els
1053 START WITH (els.page_id,els.ui_def_id,els.element_id) IN
1054 (SELECT /*+ CARDINALITY (UITPAGES 1) */ uitpages.page_id,uitpages.ui_def_id,uitpages.element_id FROM TABLE(CAST(l_ui_pages_tbl0 AS SYSTEM.CZ_UI_PAGE_ELEM_TBL)) UITPAGES)
1055 CONNECT BY els.element_signature_id=6073 AND
1056 els.target_page_ui_def_id= PRIOR els.ui_def_id AND
1057 els.target_page_id= PRIOR els.page_id AND deleted_flag='0' AND PRIOR deleted_flag='0') uiels
1058 WHERE uipages.page_id=uiels.page_id AND uipages.ui_def_id=uiels.ui_def_id
1059 UNION
1060 select
1061 uipages.ui_def_id AS TARGET_UI_DEF_ID,
1062 uipages.page_id AS TARGET_PAGE_ID,
1063 (SELECT annotated_node_path(l_root_model_id,
1064 UIPAGES.PAGEBASE_EXPL_NODE_ID,UIPAGES.ui_def_id,UIPAGES.persistent_node_id,p_root_model_expl_id)
1065 FROM dual
1066 ) AS TARGET_PAGE_ANN_PATH,
1067 uipages.DISPLAY_CONDITION_ID AS TARGET_PAGE_DISPCOND_ID,
1068 uipages.DISPLAY_CONDITION_COMP AS TARGET_PAGE_DISPCOND_COMP,
1069 uipages.DISPLAY_CONDITION_VALUE AS TARGET_PAGE_DISPCOND_VALUE,
1070 uiels.PAGE_LEVEL AS PAGE_INCLUSION_DEPTH,
1071 uiels.element_id,
1072 uiels.ui_def_id,
1073 uiels.page_id,
1074 (SELECT get_TARGET_PAGE_REF_DEPTH(l_root_model_id,
1075 UIPAGES.PAGEBASE_EXPL_NODE_ID,UIPAGES.ui_def_id,p_root_model_expl_id)
1076 FROM dual
1077 ) AS TARGET_PAGE_REFERENCE_DEPTH,
1078 '1' AS BOUND_VIA_PARENT_FLAG
1079 FROM
1080 CZ_UI_PAGES uipages,
1081 (SELECT els.ui_def_id,els.page_id,els.element_id,level as PAGE_LEVEL FROM CZ_UI_PAGE_ELEMENTS els
1082 START WITH (els.page_id,els.ui_def_id,els.element_id) IN
1083 (SELECT /*+ CARDINALITY(UITPAGES1 1) */ uitpages1.page_id,uitpages1.ui_def_id,uitpages1.element_id FROM TABLE(CAST(l_ui_pages_tbl1 AS SYSTEM.CZ_UI_PAGE_ELEM_TBL)) UITPAGES1)
1084 CONNECT BY els.element_signature_id=6073 AND
1085 els.target_page_ui_def_id= PRIOR els.ui_def_id AND
1086 els.target_page_id= PRIOR els.page_id AND deleted_flag='0' AND PRIOR deleted_flag='0') uiels
1087 WHERE uipages.page_id=uiels.page_id AND uipages.ui_def_id=uiels.ui_def_id
1088 )
1089 WHERE TARGET_UI_DEF_ID IN
1090 (SELECT p_root_ui_def_id FROM dual
1091 UNION
1092 SELECT ref_ui_def_id FROM cz_ui_refs
1093 START WITH ui_def_id=p_root_ui_def_id AND deleted_flag='0'
1094 CONNECT BY PRIOR ref_ui_def_id=ui_def_id AND deleted_flag='0') AND
1095 TARGET_PAGE_ANN_PATH IS NOT NULL
1096 ORDER BY
1097 BOUND_VIA_PARENT_FLAG asc,
1098 DECODE(TARGET_PAGE_ID,p_curr_page_id,DECODE(TARGET_UI_DEF_ID,p_curr_ui_def_id,0,1),1) asc,
1099 DECODE(PAGE_INCLUSION_DEPTH,1,DECODE(TARGET_UI_DEF_ID,p_curr_ui_def_id,G_MAX_PAGE_INCLUSION_DEPTH,1),PAGE_INCLUSION_DEPTH) desc,
1100 TARGET_PAGE_REFERENCE_DEPTH desc
1101 )
1102 LOOP
1103 IF NOT(l_hash_map.EXISTS(i.TARGET_PAGE_ID)) THEN
1104 x_ui_page_tbl(l_tbl_counter).UI_DEF_ID := i.TARGET_UI_DEF_ID;
1105 x_ui_page_tbl(l_tbl_counter).PAGE_ID := i.TARGET_PAGE_ID;
1106 x_ui_page_tbl(l_tbl_counter).ANNOTATED_NODE_PATH := i.TARGET_PAGE_ANN_PATH;
1107 x_ui_page_tbl(l_tbl_counter).DISPLAY_COND_ID := i.TARGET_PAGE_DISPCOND_ID;
1108 x_ui_page_tbl(l_tbl_counter).DISPLAY_COND_COMP := i.TARGET_PAGE_DISPCOND_COMP;
1109 x_ui_page_tbl(l_tbl_counter).DISPLAY_COND_VALUE := i.TARGET_PAGE_DISPCOND_VALUE;
1110 l_hash_map(i.TARGET_PAGE_ID) := i.TARGET_PAGE_ID;
1111 x_ui_page_tbl.EXTEND(1,1);
1112 l_tbl_counter := l_tbl_counter + 1;
1113 END IF;
1114 END LOOP;
1115
1116 ELSIF p_order_by_template=1 THEN
1117
1118 FOR i IN(SELECT * FROM (
1119 SELECT
1120 uipages.ui_def_id AS TARGET_UI_DEF_ID,
1121 uipages.page_id AS TARGET_PAGE_ID,
1122 (SELECT annotated_node_path(l_root_model_id,
1123 UIPAGES.PAGEBASE_EXPL_NODE_ID,UIPAGES.ui_def_id,UIPAGES.persistent_node_id,p_root_model_expl_id)
1124 FROM dual
1125 ) AS TARGET_PAGE_ANN_PATH,
1126 uipages.DISPLAY_CONDITION_ID AS TARGET_PAGE_DISPCOND_ID,
1127 uipages.DISPLAY_CONDITION_COMP AS TARGET_PAGE_DISPCOND_COMP,
1128 uipages.DISPLAY_CONDITION_VALUE AS TARGET_PAGE_DISPCOND_VALUE,
1129 uiels.PAGE_LEVEL AS PAGE_INCLUSION_DEPTH,
1130 uiels.element_id,
1131 uiels.ui_def_id,
1132 uiels.page_id,
1133 (SELECT get_TARGET_PAGE_REF_DEPTH(l_root_model_id,
1134 UIPAGES.PAGEBASE_EXPL_NODE_ID,UIPAGES.ui_def_id,p_root_model_expl_id)
1135 FROM dual
1136 ) AS TARGET_PAGE_REFERENCE_DEPTH,
1137 '0' AS BOUND_VIA_PARENT_FLAG
1138 FROM
1139 CZ_UI_PAGES uipages,
1140 (SELECT els.ui_def_id,els.page_id,els.element_id,level as PAGE_LEVEL FROM CZ_UI_PAGE_ELEMENTS els
1141 START WITH (els.page_id,els.ui_def_id,els.element_id) IN
1142 (SELECT uitpages.page_id,uitpages.ui_def_id,uitpages.element_id FROM TABLE(CAST(l_ui_pages_tbl0 AS SYSTEM.CZ_UI_PAGE_ELEM_TBL)) uitpages)
1143 CONNECT BY els.element_signature_id=6073 AND
1144 els.target_page_ui_def_id= PRIOR els.ui_def_id AND
1145 els.target_page_id= PRIOR els.page_id AND deleted_flag='0' AND PRIOR deleted_flag='0') uiels
1146 WHERE uipages.page_id=uiels.page_id AND uipages.ui_def_id=uiels.ui_def_id
1147 UNION
1148 select
1149 uipages.ui_def_id AS TARGET_UI_DEF_ID,
1150 uipages.page_id AS TARGET_PAGE_ID,
1151 (SELECT annotated_node_path(l_root_model_id,
1152 UIPAGES.PAGEBASE_EXPL_NODE_ID,UIPAGES.ui_def_id,UIPAGES.persistent_node_id,p_root_model_expl_id)
1153 FROM dual
1154 ) AS TARGET_PAGE_ANN_PATH,
1155 uipages.DISPLAY_CONDITION_ID AS TARGET_PAGE_DISPCOND_ID,
1156 uipages.DISPLAY_CONDITION_COMP AS TARGET_PAGE_DISPCOND_COMP,
1157 uipages.DISPLAY_CONDITION_VALUE AS TARGET_PAGE_DISPCOND_VALUE,
1158 uiels.PAGE_LEVEL AS PAGE_INCLUSION_DEPTH,
1159 uiels.element_id,
1160 uiels.ui_def_id,
1161 uiels.page_id,
1162 (SELECT get_TARGET_PAGE_REF_DEPTH(l_root_model_id,
1163 UIPAGES.PAGEBASE_EXPL_NODE_ID,UIPAGES.ui_def_id,p_root_model_expl_id)
1164 FROM dual
1165 ) AS TARGET_PAGE_REFERENCE_DEPTH,
1166 '1' AS BOUND_VIA_PARENT_FLAG
1167 FROM
1168 CZ_UI_PAGES uipages,
1169 (SELECT els.ui_def_id,els.page_id,els.element_id,level as PAGE_LEVEL FROM CZ_UI_PAGE_ELEMENTS els
1170 START WITH (els.page_id,els.ui_def_id,els.element_id) IN
1171 (SELECT uitpages1.page_id,uitpages1.ui_def_id,uitpages1.element_id FROM TABLE(CAST(l_ui_pages_tbl1 AS SYSTEM.CZ_UI_PAGE_ELEM_TBL)) uitpages1)
1172 CONNECT BY els.element_signature_id=6073 AND
1173 els.target_page_ui_def_id= PRIOR els.ui_def_id AND
1174 els.target_page_id= PRIOR els.page_id AND deleted_flag='0' AND PRIOR deleted_flag='0') uiels
1175 WHERE uipages.page_id=uiels.page_id AND uipages.ui_def_id=uiels.ui_def_id )
1176 WHERE TARGET_UI_DEF_ID IN
1177 (SELECT p_root_ui_def_id FROM dual
1178 UNION
1179 SELECT ref_ui_def_id FROM cz_ui_refs
1180 START WITH ui_def_id=p_root_ui_def_id AND deleted_flag='0'
1181 CONNECT BY PRIOR ref_ui_def_id=ui_def_id AND deleted_flag='0') AND
1182 TARGET_PAGE_ANN_PATH IS NOT NULL
1183 ORDER BY
1184 DECODE(TARGET_PAGE_ID,p_curr_page_id,DECODE(TARGET_UI_DEF_ID,p_curr_ui_def_id,0,1),1) desc,
1185 BOUND_VIA_PARENT_FLAG asc,
1186 DECODE(PAGE_INCLUSION_DEPTH,1,DECODE(TARGET_UI_DEF_ID,p_curr_ui_def_id,G_MAX_PAGE_INCLUSION_DEPTH,1),PAGE_INCLUSION_DEPTH) desc,
1187 TARGET_PAGE_REFERENCE_DEPTH desc
1188 )
1189 LOOP
1190 IF NOT(l_hash_map.EXISTS(i.TARGET_PAGE_ID)) THEN
1191 x_ui_page_tbl(l_tbl_counter).UI_DEF_ID := i.TARGET_UI_DEF_ID;
1192 x_ui_page_tbl(l_tbl_counter).PAGE_ID := i.TARGET_PAGE_ID;
1193 x_ui_page_tbl(l_tbl_counter).ANNOTATED_NODE_PATH := i.TARGET_PAGE_ANN_PATH;
1194 x_ui_page_tbl(l_tbl_counter).DISPLAY_COND_ID := i.TARGET_PAGE_DISPCOND_ID;
1195 x_ui_page_tbl(l_tbl_counter).DISPLAY_COND_COMP := i.TARGET_PAGE_DISPCOND_COMP;
1196 x_ui_page_tbl(l_tbl_counter).DISPLAY_COND_VALUE := i.TARGET_PAGE_DISPCOND_VALUE;
1197 l_hash_map(i.TARGET_PAGE_ID) := i.TARGET_PAGE_ID;
1198 x_ui_page_tbl.EXTEND(1,1);
1199 l_tbl_counter := l_tbl_counter + 1;
1200 END IF;
1201 END LOOP;
1202
1203 END IF;
1204 x_ui_page_tbl.DELETE(l_tbl_counter);
1205
1206 END get_Target_UI_Pages;
1207
1208 -------------------------------------------
1209
1210 END cz_runtime;
[Home] [Help]