[Home] [Help]
PACKAGE BODY: APPS.CZ_XML
Source
1 PACKAGE BODY CZ_XML AS
2 /* $Header: czxmlb.pls 120.3 2006/09/18 19:59:30 skudryav ship $ */
3
4 G_GEN_VERSION CONSTANT VARCHAR2(25) :='11.5.20';
5 G_GEN_HEADER CONSTANT VARCHAR2(100) :='$Header: czxmlb.pls 120.3 2006/09/18 19:59:30 skudryav ship $';
6 G_NULL_VALUE CONSTANT VARCHAR2(100) := 'NULL';
7 G_UMPERS CONSTANT VARCHAR2(1) := fnd_global.local_chr(38);
8
9 G_USER_ATTRIBUTE1_NAME CONSTANT VARCHAR2(255) := 'user:attribute1';
10 G_USER_ATTRIBUTE2_NAME CONSTANT VARCHAR2(255) := 'user:attribute2';
11 G_USER_ATTRIBUTE3_NAME CONSTANT VARCHAR2(255) := 'user:attribute3';
12 G_USER_ATTRIBUTE4_NAME CONSTANT VARCHAR2(255) := 'user:attribute4';
13 G_USER_ATTRIBUTE5_NAME CONSTANT VARCHAR2(255) := 'user:attribute5';
14
15 G_UI_CONTENTS_TAG CONSTANT VARCHAR2(255) := 'ui:contents';
16 G_ID_ATTRIBUTE CONSTANT VARCHAR2(255) := 'id';
17
18 G_HEADER_TAG CONSTANT VARCHAR2(255) := 'oa:header';
19 G_NESTED_TAG CONSTANT VARCHAR2(255) := 'user:nested';
20 G_ROW_LAYOUT_TAG CONSTANT VARCHAR2(255) := 'oa:rowLayout';
21 G_CONTENT_CONTAINER_TAG CONSTANT VARCHAR2(255) := 'oa:contentContainer';
22 G_STACK_LAYOUT_TAG CONSTANT VARCHAR2(255) := 'oa:stackLayout';
23 G_TABLE_TAG CONSTANT VARCHAR2(255) := 'oa:table';
24 G_SUBMIT_BUTTON_TAG CONSTANT VARCHAR2(255) := 'oa:submitButton';
25 G_MSG_CHECKBOX_TAG CONSTANT VARCHAR2(255) := 'oa:messageCheckbox';
26 G_MSG_RADIO_BUTTON_TAG CONSTANT VARCHAR2(255) := 'oa:messageRadioButton';
27 G_MSG_STYLED_TEXT_TAG CONSTANT VARCHAR2(255) := 'oa:messageStyledText';
28 G_MSG_TEXT_INPUT_TAG CONSTANT VARCHAR2(255) := 'oa:messageTextInput';
29 G_MSG_CHOICE_TAG CONSTANT VARCHAR2(255) := 'oa:messageChoice';
30 G_TABLE_LAYOUT_TAG CONSTANT VARCHAR2(255) := 'oa:tableLayout';
31 G_CELL_FORMAT_TAG CONSTANT VARCHAR2(255) := 'oa:cellFormat';
32 G_FLOW_LAYOUT_TAG CONSTANT VARCHAR2(255) := 'oa:flowLayout';
33 G_DBL_COL_LAYOUT_TAG CONSTANT VARCHAR2(255) := 'oa:doubleColumnLayout';
34 G_SUB_TAB_LAYOUT_TAG CONSTANT VARCHAR2(255) := 'oa:subTabLayout';
35 G_TEXT_TAG CONSTANT VARCHAR2(255) := 'oa:staticStyledText';
36 G_BUTTON_TAG CONSTANT VARCHAR2(255) := 'oa:button';
37 G_IMAGE_TAG CONSTANT VARCHAR2(255) := 'oa:image';
38 G_SPACER_TAG CONSTANT VARCHAR2(255) := 'oa:spacer';
39 G_TEMPLATE_INCLUDE_TAG CONSTANT VARCHAR2(255) := 'oa:templateInclude';
40 G_RAW_TEXT_TAG CONSTANT VARCHAR2(255) := 'oa:rawText';
41 G_SWITCHER_TAG CONSTANT VARCHAR2(255) := 'oa:switcher';
42
43
44 -------------- JDR DOC BUILDER/XML Parsing part -----------------
45
46 g_DOC xmldom.DOMDocument;
47 g_JRADDOC jdr_docbuilder.Document;
48 g_PARSER xmlparser.parser;
49 g_Null_Xml_Node xmldom.DOMNode;
50
51 g_MSG_COUNT NUMBER := 0;
52 g_MSG_DATA VARCHAR2(32000);
53 g_RETURN_STATUS VARCHAR2(10);
54 g_WRONG_PS_NODE_ID NUMBER;
55
56 MAX_CHUNK_SIZE CONSTANT INTEGER := 32000;
57
58 WRONG_UI_TEMPLATE EXCEPTION;
59 WRONG_UI_TO_REFRESH EXCEPTION;
60 PAGE_CANNOT_BE_SPLIT EXCEPTION;
61 WRONG_EXT_PS_TYPE EXCEPTION;
62 UNREACH_UI_NODE EXCEPTION;
63 FAILED_TO_LOCK_MODEL EXCEPTION;
64 FAILED_TO_LOCK_TEMPLATE EXCEPTION;
65
66 TYPE char_tbl_type IS TABLE OF VARCHAR2(255);
67 g_toplevel_attr_tbl char_tbl_type := char_tbl_type('version',
68 'xml:lang',
69 'file-version',
70 'xmlns:oa',
71 'xmlns:ui',
72 'xmlns:jrad',
73 'xmlns:user',
74 'FILE-version',
75 'file-version',
76 'xmlns:user',
77 'xmlns:USER',
78 'xmlns');
79
80 g_condition_attr_tbl char_tbl_type := char_tbl_type('displayCondnId',
81 'enabledCondnId',
82 'colDisplayCondnId',
83 'liDisplayCondnId',
84 'rowDisplayCondnId',
85 'rowEnabledCondnId',
86 'switcherCondnId');
87
88 g_caption_attr_tbl char_tbl_type := char_tbl_type('captionIntlTextId',
89 'rolloverIntlTextId',
90 'cellIntlTextId',
91 'urlIntlTextId',
92 'tableSummaryIntlTextId',
93 'imageSourceIntlTextId',
94 'addInstBtnIntlTextId');
95
96 g_view_prefix_tbl char_tbl_type := char_tbl_type('COMP_',
97 'SIM_',
98 'CS_',
99 'OF_',
100 'IF_',
101 'DF_',
102 'BF_',
103 'TF_',
104 'TOT_',
105 'RSC_',
106 'REF_',
107 'CON_',
108 'BOMM_',
109 'OPT_' );
110
111 TYPE attribute_record_type IS RECORD(
112 NAME jdr_attributes.att_name%TYPE,
113 VALUE jdr_attributes.att_value%TYPE);
114
115 TYPE attributes_tbl_type IS TABLE OF attribute_record_type INDEX BY BINARY_INTEGER;
116
117 TYPE ui_tbl_type IS TABLE OF VARCHAR2(255) INDEX BY VARCHAR2(255);
118
119 -------------------------------------------------------------------
120
121 PROCEDURE Initialize(x_run_id OUT NOCOPY NUMBER) IS
122 BEGIN
123 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO G_RUN_ID FROM dual;
124 x_run_id := G_RUN_ID;
125 END Initialize;
126
127 PROCEDURE Initialize
128 (x_run_id OUT NOCOPY NUMBER,
129 x_return_status OUT NOCOPY VARCHAR2,
130 x_msg_count OUT NOCOPY NUMBER,
131 x_msg_data OUT NOCOPY VARCHAR2) IS
132
133 BEGIN
134 SELECT CZ_XFR_RUN_INFOS_S.NEXTVAL INTO G_RUN_ID FROM dual;
135 x_run_id := G_RUN_ID;
136 x_return_status := FND_API.G_RET_STS_SUCCESS;
137 x_msg_count := 0;
138 x_msg_data := NULL;
139 END Initialize;
140
141 PROCEDURE LOG_REPORT
142 (p_caller IN VARCHAR2,
143 p_error_message IN VARCHAR2
144 ) IS
145 PRAGMA AUTONOMOUS_TRANSACTION;
146 l_error BOOLEAN;
147 l_status INTEGER;
148 BEGIN
149 l_status:=11276;
150 INSERT INTO CZ_DB_LOGS
151 (RUN_ID,
152 LOGTIME,
153 LOGUSER,
154 URGENCY,
155 CALLER,
156 STATUSCODE,
157 MESSAGE)
158 VALUES (G_RUN_ID,
159 SYSDATE,
160 USER,
161 1,
162 p_caller,
163 l_status,
164 p_error_message);
165 COMMIT;
166 END LOG_REPORT;
167
168 --
169 -- section for a different DEBUG procedures
170 --
171 PROCEDURE DEBUG(p_str IN VARCHAR2) IS
172 l_error BOOLEAN;
173 l_run_id NUMBER;
174 BEGIN
175 --dbms_output.put_line(p_str);
176 IF G_RUN_ID IS NULL THEN
177 Initialize(l_run_id);
178 END IF;
179 l_error:=CZ_UTILS.LOG_REPORT(p_str,1,'CZ_XML',11276,G_RUN_ID);
180 NULL;
181 END DEBUG;
182
183 PROCEDURE DEBUG(p_var_name IN VARCHAR2, p_var_value IN VARCHAR2) IS
184 BEGIN
185 DEBUG(p_var_name || ' = ' || p_var_value);
186 END DEBUG;
187
188 PROCEDURE DEBUG(p_var_name IN VARCHAR2, p_var_value IN NUMBER) IS
189 BEGIN
190 DEBUG(p_var_name || ' = ' || TO_CHAR(p_var_value));
191 END DEBUG;
192
193 PROCEDURE DEBUG(p_var_name IN VARCHAR2, p_var_value IN DATE) IS
194 BEGIN
195 DEBUG(p_var_name || ' = ' ||
196 TO_CHAR(p_var_value, 'DD-MM-YYYY HH24:MI:SS'));
197 END DEBUG;
198
199 --
200 -- add FND error message
201 --
202 PROCEDURE add_Error_Message(p_caller IN VARCHAR2,
203 p_message_name IN VARCHAR2,
204 p_fatal_error IN BOOLEAN) IS
205
206 BEGIN
207
208 FND_MESSAGE.SET_NAME('CZ', p_message_name);
209 IF p_fatal_error THEN
210 FND_MSG_PUB.ADD;
211 ELSE
212 fnd_msg_pub.add_detail(p_message_type => FND_MSG_PUB.G_WARNING_MSG);
213 END IF;
214 FND_MSG_PUB.count_and_get(p_count => g_MSG_COUNT,
215 p_data => g_MSG_DATA);
216 IF p_fatal_error OR g_RETURN_STATUS=FND_API.G_RET_STS_ERROR THEN
217 g_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
218 ELSE
219 g_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
220 END IF;
221
222 LOG_REPORT(p_caller, p_message_name);
223
224 END add_Error_Message;
225
226 ---------------------------------------------------------------------
227 ------------------- JDR DOC BUILDER / XML Parsing -------------------
228 ---------------------------------------------------------------------
229
230 --
231 -- remove common attributes in case when we attach template
232 -- starting with DOM node p_node
233 -- Parameters : p_node - identifies DOM node of subtree's root
234 --
235 PROCEDURE remove_TopLevel_Attributes(p_node xmldom.DOMNode) IS
236 l_root_elem xmldom.DOMElement;
237 BEGIN
238 l_root_elem := xmldom.makeElement(p_node);
239 FOR i IN g_toplevel_attr_tbl.FIRST .. g_toplevel_attr_tbl.LAST
240 LOOP
241 BEGIN
242 xmldom.removeAttribute(l_root_elem, g_toplevel_attr_tbl(i));
243 EXCEPTION
244 WHEN OTHERS THEN
245 NULL;
246 END;
247 END LOOP;
248 END remove_TopLevel_Attributes;
249
250
251 --
252 -- get attributes of a given DOM node
253 -- Parameters : p_node - DOM node
254 -- Return : array of attributes in format of attributes_tbl_type array
255 --
256 FUNCTION get_Attributes(p_node IN xmldom.DOMNode)
257 RETURN attributes_tbl_type IS
258
259 l_attr_tbl attributes_tbl_type;
260 l_node_map_tbl xmldom.DOMNamedNodeMap;
261 l_node_attr xmldom.DOMNode;
262 l_index NUMBER;
263 l_length NUMBER;
264
265 BEGIN
266 l_node_map_tbl := xmldom.getAttributes(p_node);
267 IF (xmldom.isNull(l_node_map_tbl) = FALSE) THEN
268 l_length := xmldom.getLength(l_node_map_tbl);
269 --
270 -- loop through attributes
271 --
272 l_index := 1;
273 FOR i IN 0 .. l_length - 1
274 LOOP
275 l_node_attr := xmldom.item(l_node_map_tbl, i);
276 l_attr_tbl(l_index).NAME := xmldom.getNodeName(l_node_attr);
277 l_attr_tbl(l_index).VALUE := xmldom.getNodeValue(l_node_attr);
278 l_index := l_index + 1;
279 END LOOP;
280 END IF;
281 RETURN l_attr_tbl;
282 END get_Attributes;
283
284 --
285 -- set attributes for JRAD doc builder element p_jraddoc_node
286 -- to attributes of DOM node p_node
287 -- so this means that we just copy all attributes from DOM Node p_node
288 -- to attributes of jdr_docbuilder.Element p_jraddoc_node
289 -- it is used when DOM tree is converted to JRAD document
290 -- Parameters : p_node - DOM node
291 -- p_jraddoc_node - JRAD doc builder element
292 -- Notes : it is not used in UI Generation/UI Refresh process directly
293 --
294 PROCEDURE set_Attributes(p_node xmldom.DOMNode,
295 p_jraddoc_node jdr_docbuilder.Element) IS
296
297 l_attr_tbl attributes_tbl_type;
298
299 BEGIN
300 l_attr_tbl := get_Attributes(p_node);
301 IF l_attr_tbl.COUNT > 0 THEN
302 FOR l IN l_attr_tbl.FIRST .. l_attr_tbl.LAST
303 LOOP
304 jdr_docbuilder.setAttribute(p_jraddoc_node,
305 l_attr_tbl(l).NAME,
306 l_attr_tbl(l).VALUE);
307 END LOOP;
308 l_attr_tbl.DELETE;
309 END IF;
310 END set_Attributes;
311
312 --
313 -- set CZ Attributes for a given DOM node
314 -- Parameters : p_dom_element - DOM Element which identifies DOM node
315 -- p_attribute_name - attribute name
316 -- p_attribute_value - attribute value
317 --
318 PROCEDURE set_Attribute(p_dom_element xmldom.DOMElement,
319 p_attribute_name IN VARCHAR2,
320 p_attribute_value IN VARCHAR2) IS
321
322 BEGIN
323 xmldom.setAttribute(p_dom_element, p_attribute_name, p_attribute_value);
324 END set_Attribute;
325
326 --
327 -- set CZ Attributes for a given DOM node
328 -- Parameters : p_dom_element - DOM Element which identifies DOM node
329 -- p_attribute_name - attribute name
330 -- p_attribute_value - attribute value
331 --
332 PROCEDURE set_Attribute(p_dom_node xmldom.DOMNode,
333 p_attribute_name IN VARCHAR2,
334 p_attribute_value IN VARCHAR2) IS
335
336 BEGIN
337 xmldom.setAttribute(xmldom.makeElement(p_dom_node), p_attribute_name, p_attribute_value);
338 END set_Attribute;
339
340
341 --
342 -- return value of specified user atribute
343 --
344 FUNCTION get_User_Attribute(p_user_attribute_value IN VARCHAR2,
345 p_cz_attribute_name IN VARCHAR2)
346 RETURN VARCHAR2 IS
347
348 l_ind1 NUMBER;
349 l_ind2 NUMBER;
350 l_substr VARCHAR2(32000);
351
352 BEGIN
353
354 l_ind1 := INSTR(p_user_attribute_value,p_cz_attribute_name);
355
356 IF l_ind1 > 0 THEN
357 l_substr := SUBSTR(p_user_attribute_value,l_ind1+LENGTH(p_cz_attribute_name)+LENGTH('='));
358 l_ind2 := INSTR(l_substr, '|');
359 IF l_ind2 > 0 THEN
360 RETURN SUBSTR(l_substr,1,l_ind2-1);
361 ELSE
362 RETURN l_substr;
363 END IF;
364 ELSE
365 RETURN NULL;
366 END IF;
367 END get_User_Attribute;
368
369 --
370 -- set internal CZ attributes within "user:attribute1"
371 --
372 PROCEDURE set_User_Attribute(p_cz_attribute_name IN VARCHAR2,
373 p_cz_attribute_value IN VARCHAR2,
374 px_xml_attribute_value IN OUT NOCOPY VARCHAR2) IS
375
376 l_str VARCHAR2(4000);
377 l_sub_str VARCHAR2(4000);
378 l_next_equal_ind NUMBER;
379 l_next_ind NUMBER;
380 l_ind NUMBER;
381
382 BEGIN
383
384 --
385 -- get string of CZ user attributes
386 --
387 l_str := px_xml_attribute_value;
388 l_ind := INSTR(l_str, p_cz_attribute_name);
389
390 IF l_ind > 0 THEN
391
392 l_sub_str := SUBSTR(l_str, l_ind);
393 l_next_ind := INSTR(l_sub_str, '|');
394
395 l_next_equal_ind := INSTR(l_sub_str, '=');
396
397 IF l_next_equal_ind > 1 THEN
398
399 IF l_next_ind > 0 THEN
400 px_xml_attribute_value := SUBSTR(l_str, 1, l_ind + l_next_equal_ind - 1) ||
401 p_cz_attribute_value || SUBSTR(l_sub_str, l_next_ind);
402 ELSE
403 px_xml_attribute_value := SUBSTR(l_str, 1,l_ind + l_next_equal_ind - 1) || p_cz_attribute_value;
404 END IF;
405
406 END IF;
407 ELSE
408 NULL;
409 END IF;
410
411 END set_User_Attribute;
412
413
414 --
415 -- create JRAD element based on a given VARCHAR2 string
416 -- which must be in the following form :
417 -- <NS>:<Tag>
418 -- Example : 'oa:button'
419 --
420 -- Parameters : p_node_name - VARCHAR2 string which contains string
421 -- described above
422 --
423 FUNCTION createElement(p_node_name IN VARCHAR2)
424 RETURN jdr_docbuilder.Element IS
425
426 l_ns VARCHAR2(255);
427 l_el VARCHAR2(255);
428
429 BEGIN
430 l_ns := SUBSTR(p_node_name, 1, INSTR(p_node_name, ':'));
431 l_el := SUBSTR(p_node_name, INSTR(p_node_name, ':') + 1);
432
433 RETURN jdr_docbuilder.createElement(l_ns, l_el);
434 END createElement;
435
436 --
437 -- recursive procedure which
438 -- scans DOM tree and create a corresponding JRAD document
439 -- Parameters :
440 -- p_nodeList - list of DOM nodes of the current level in DOM tree
441 -- p_groupingTag - identifies XML grouping tag
442 -- p_parent - identifes parent JRAD docbuilder element
443 --
444 PROCEDURE traverse_DOM_Tree(p_nodeList xmldom.DOMNodeList,
445 p_groupingTag VARCHAR2,
446 p_parent jdr_docbuilder.Element) IS
447
448 l_next_level_tbl xmldom.DOMNodeList;
449 l_node xmldom.DOMNode;
450 l_parent_xml_node xmldom.DOMNode;
451 l_attr_tbl attributes_tbl_type;
452 l_child jdr_docbuilder.Element;
453 l_tag_name VARCHAR2(255);
454 l_parent_tag_name VARCHAR2(255);
455 l_grouping_tag VARCHAR2(255);
456 l_ns VARCHAR2(255);
457 l_gr VARCHAR2(255);
458 l_attr_value VARCHAR2(32000);
459 l_st VARCHAR2(1) := '';
460 l_length NUMBER;
461
462 BEGIN
463
464 --
465 -- now we have a DOM tree of the target JRAD document
466 -- and we need to populate JRAD tables by using jdr_docbuilder API
467 --
468 l_length := xmldom.getLength(p_nodeList);
469
470 FOR i IN 0 .. l_length - 1
471 LOOP
472 l_node := xmldom.item(p_nodeList, i);
473
474 l_tag_name := xmldom.getNodeName(l_node);
475
476 l_parent_xml_node := xmldom.getParentNode(l_node);
477
478 IF NOT(xmldom.isNull(l_parent_xml_node)) THEN
479 l_parent_tag_name := xmldom.getNodeName(l_parent_xml_node);
480 END IF;
481
482 l_grouping_tag := '';
483
484 l_attr_tbl := get_Attributes(l_node);
485
486 IF l_attr_tbl.COUNT = 0 AND l_tag_name NOT IN('ui:firePartialAction') THEN
487
488 --
489 -- this is grouping tag
490 --
491 l_grouping_tag := l_tag_name;
492
493 END IF;
494
495 l_st := NULL;
496
497 IF p_groupingTag IS NOT NULL THEN
498
499 l_child := createElement(l_tag_name);
500
501 l_st := '1';
502
503 IF l_attr_tbl.COUNT > 0 THEN
504 FOR l IN l_attr_tbl.FIRST .. l_attr_tbl.LAST
505 LOOP
506 l_attr_value := l_attr_tbl(l).VALUE;
507
508 l_attr_value := REPLACE(l_attr_value, G_UMPERS, G_UMPERS||'amp;');
509 l_attr_value := REPLACE(l_attr_value, '<', G_UMPERS||'lt;');
510 l_attr_value := REPLACE(l_attr_value, '>', G_UMPERS||'gt;');
511 l_attr_value := REPLACE(l_attr_value, '"', G_UMPERS||'quot;');
512 l_attr_value := REPLACE(l_attr_value, '''', G_UMPERS||'apos;');
513
514 jdr_docbuilder.setAttribute(l_child,
515 l_attr_tbl(l).NAME,
516 l_attr_value);
517 END LOOP;
518 l_attr_tbl.DELETE;
519 END IF; -- end of IF l_attr_tbl.COUNT > 0 THEN
520
521 l_ns := SUBSTR(p_groupingTag, 1, INSTR(p_groupingTag, ':'));
522 l_gr := SUBSTR(p_groupingTag, INSTR(p_groupingTag, ':') + 1);
523
524 IF l_ns IS NULL THEN
525 l_ns := 'jrad:';
526 END IF;
527 jdr_docbuilder.addChild(p_parent, l_ns, l_gr, l_child);
528
529 /* new jdr_docbuilder function */
530
531 ELSE
532 IF (l_tag_name='ui:case' AND
533 l_parent_tag_name IN('oa:switcher')) OR
534 (l_tag_name='oa:stackLayout' AND l_parent_tag_name = 'ui:case') THEN
535
536 l_child := createElement(l_tag_name);
537
538 l_st := '1';
539 IF l_attr_tbl.COUNT > 0 THEN
540 FOR l IN l_attr_tbl.FIRST .. l_attr_tbl.LAST
541 LOOP
542 l_attr_value := l_attr_tbl(l).VALUE;
543
544 l_attr_value := REPLACE(l_attr_value, G_UMPERS, G_UMPERS||'amp;');
545 l_attr_value := REPLACE(l_attr_value, '<', G_UMPERS||'lt;');
546 l_attr_value := REPLACE(l_attr_value, '>', G_UMPERS||'gt;');
547 l_attr_value := REPLACE(l_attr_value, '"', G_UMPERS||'quot;');
548 l_attr_value := REPLACE(l_attr_value, '''', G_UMPERS||'apos;');
549
550 jdr_docbuilder.setAttribute(l_child,
551 l_attr_tbl(l).NAME,
552 l_attr_value);
553
554 END LOOP;
555 l_attr_tbl.DELETE;
556 END IF;
557
558 jdr_docbuilder.addChild(p_parent, l_child);
559
560 END IF;
561
562 NULL;
563 END IF; -- end of IF p_groupingTag IS NOT NULL THEN
564
565 l_next_level_tbl := xmldom.getChildNodes(l_node);
566 IF NOT(xmldom.isNull(l_next_level_tbl)) AND
567 xmldom.getLENGTH(l_next_level_tbl) <> 0 THEN
568
569 IF l_st IS NULL THEN
570 traverse_DOM_Tree(l_next_level_tbl,
571 l_grouping_tag,
572 p_parent);
573 ELSE
574 traverse_DOM_Tree(l_next_level_tbl,
575 l_grouping_tag,
576 l_child);
577 END IF;
578 END IF;
579
580 END LOOP;
581
582 END traverse_DOM_Tree;
583
584 FUNCTION get_JRADNLS_Lang RETURN VARCHAR2 IS
585 l_lang VARCHAR2(255);
586 BEGIN
587 SELECT ISO_LANGUAGE||'-'||
588 ISO_TERRITORY
589 INTO l_lang
590 FROM FND_LANGUAGES_VL
591 WHERE language_code=USERENV('LANG');
592 RETURN l_lang;
593 EXCEPTION
594 WHEN OTHERS THEN
595 RETURN 'EN-US';
596 END get_JRADNLS_Lang;
597
598 --
599 -- convert DOM Tree to JRAD record sets
600 -- by ising DOM methods AND jdr_docbuilder API
601 -- Parameters : p_jrad_doc_name - specifies full JRAD name of
602 -- document that will be created from DOM tree
603 -- which is identified by global DOM Document variable g_DOC
604 --
605 PROCEDURE convert_DOM_to_JRAD(p_doc xmldom.DOMDocument,
606 p_jrad_doc_name IN VARCHAR2) IS
607
608 l_dom_root_node xmldom.DOMNode;
609 l_topLevel jdr_docbuilder.Element;
610 l_status PLS_INTEGER;
611 l_lang VARCHAR2(255);
612 BEGIN
613
614 g_DOC := p_doc;
615
616 --
617 -- refresh global jdr_docbuilder's structures
618 --
619 jdr_docbuilder.refresh;
620
621 --
622 -- get Document's root node
623 --
624 l_dom_root_node := xmldom.makeNode(xmldom.getDocumentElement(g_DOC));
625
626 --
627 -- remove top level xml attributes - jdrdocbuilder always adds these attributes
628 -- so we don't need to have a duplicates
629 --
630 remove_TopLevel_Attributes(l_dom_root_node);
631
632 l_lang := get_JRADNLS_Lang();
633 --
634 -- create a target JRAD document
635 --
636 g_JRADDOC := jdr_docbuilder.createDocument(p_jrad_doc_name, l_lang);
637
638 --
639 -- create root element of the target JRAD document
640 --
641 l_topLevel := createElement(xmldom.getNodeName(l_dom_root_node));
642
643 --
644 -- set top level attributes
645 --
646 set_Attributes(l_dom_root_node, l_topLevel);
647
648 --
649 -- set JRAD top level node
650 --
651 jdr_docbuilder.setTopLevelElement(g_JRADDOC, l_topLevel);
652
653 --
654 -- modify the source DOM tree and create the target JRAD document
655 -- traverse_DOM_Tree() is recursive procedure
656 --
657 traverse_DOM_Tree(xmldom.getChildNodes(l_dom_root_node),
658 '',
659 l_topLevel);
660
661 --
662 -- finally SAVE the target JRAD document
663 --
664 l_status := jdr_docbuilder.SAVE;
665
666 END convert_DOM_to_JRAD;
667
668 --
669 -- save JRAD document
670 --
671 PROCEDURE Save_Document(p_xml_doc xmldom.DOMDocument,
672 p_doc_name IN VARCHAR2) IS
673 BEGIN
674
675 convert_DOM_to_JRAD(p_doc => p_xml_doc, p_jrad_doc_name => p_doc_name);
676
677 END Save_Document;
678
679 ---------------------------------------------------------------------
680 ---------- end of JDR DOC BUILDER / XML Parsing Part ---------------
681 ---------------------------------------------------------------------
682
683 --
684 -- open XML parser
685 --
686 PROCEDURE Open_Parser IS
687 BEGIN
688 --
689 -- create a new XML parser ( global )
690 --
691 g_PARSER := xmlparser.newParser;
692 END Open_Parser;
693
694 --
695 -- close XML parser
696 --
697 PROCEDURE Close_Parser IS
698 BEGIN
699 --
700 -- close XML parser ( global )
701 --
702 xmlparser.freeParser(g_PARSER);
703 EXCEPTION
704 WHEN OTHERS THEN
705 IF SQLCODE='-20103' THEN
706 NULL;
707 ELSE
708 add_Error_Message(p_caller => 'CZ_XML.Close_Parser',
709 p_message_name => 'Close_Parser() : XML Parser can not be closed : '||SQLERRM,
710 p_fatal_error => TRUE);
711 RAISE;
712 END IF;
713 END Close_Parser;
714
715 --
716 -- returns value of a given attribute
717 -- Parameters :
718 -- p_node - DOM node
719 -- p_attr_name - attribute name
720 -- Return : attribute value as VARCHAR2 string
721 --
722 FUNCTION get_Attribute_Value(p_node IN xmldom.DOMNode,
723 p_attr_name IN VARCHAR2) RETURN VARCHAR2 IS
724
725 l_node_map_tbl xmldom.DOMNamedNodeMap;
726 l_node_attr xmldom.DOMNode;
727 l_attr_value VARCHAR2(32000);
728 l_length NUMBER;
729
730 BEGIN
731 IF xmldom.IsNull(p_node) THEN
732 RETURN NULL;
733 END IF;
734 l_node_map_tbl := xmldom.getAttributes(p_node);
735
736 IF (xmldom.isNull(l_node_map_tbl) = FALSE) THEN
737 l_length := xmldom.getLength(l_node_map_tbl);
738 --
739 -- loop through attributes
740 --
741 FOR i IN 0 .. l_length - 1
742 LOOP
743 l_node_attr := xmldom.item(l_node_map_tbl, i);
744
745 IF xmldom.getNodeName(l_node_attr) = p_attr_name THEN
746 l_attr_value := xmldom.getNodeValue(l_node_attr);
747 RETURN l_attr_value;
748 END IF;
749 END LOOP;
750 END IF;
751 RETURN G_NULL_VALUE;
752 END get_Attribute_Value;
753
754 --
755 -- find DOM node by persistent_node_id
756 --
757 FUNCTION find_XML_Node_By_Attribute(p_subtree_doc xmldom.DOMDocument,
758 p_attribute_name IN VARCHAR2,
759 p_attribute_value IN VARCHAR2,
760 p_in_user_attributes IN VARCHAR2 DEFAULT NULL)
761 RETURN xmldom.DOMNode IS
762
763 l_node xmldom.DOMNode;
764 l_nodeslist xmldom.DOMNodeList;
765 l_empty_xml_node xmldom.DOMNode;
766 l_length NUMBER;
767 l_attribute_value VARCHAR2(32000);
768
769 BEGIN
770 --
771 -- here we don't need to know about hierachy of nodes
772 -- so we just need to get list of all nodes of XML subtree
773 --
774 l_nodeslist := xmldom.getElementsByTagName(p_subtree_doc, '*');
775 l_length := xmldom.getLength(l_nodeslist);
776
777 l_node := xmldom.makeNode(xmldom.getDocumentElement(p_subtree_doc));
778
779 l_attribute_value := get_Attribute_Value(l_node, p_attribute_name);
780
781 IF p_in_user_attributes='1' THEN
782 l_attribute_value := get_Attribute_Value(l_node, G_USER_ATTRIBUTE4_NAME);
783 l_attribute_value := get_User_Attribute(l_attribute_value, p_attribute_name);
784 ELSE
785 l_attribute_value := get_Attribute_Value(l_node, p_attribute_name);
786 END IF;
787
788 IF l_attribute_value = p_attribute_value THEN
789 RETURN l_node;
790 END IF;
791
792 --
793 -- scan subtree and substitute macros "%" to real values
794 --
795 FOR i IN 0 .. l_length - 1
796 LOOP
797 l_node := xmldom.item(l_nodeslist, i);
798
799 IF p_in_user_attributes='1' THEN
800 l_attribute_value := get_Attribute_Value(l_node, G_USER_ATTRIBUTE4_NAME);
801 l_attribute_value := get_User_Attribute(l_attribute_value, p_attribute_name);
802 ELSE
803 l_attribute_value := get_Attribute_Value(l_node, p_attribute_name);
804 END IF;
805
806 IF l_attribute_value = p_attribute_value THEN
807 RETURN l_node;
808 END IF;
809 END LOOP;
810 RETURN l_empty_xml_node;
811
812 END find_XML_Node_By_Attribute;
813
814 --
815 -- parse or export/parse JRAD document
816 -- Parameters :
817 -- p_doc_full_name - full JRAD name of the document
818 --
819 FUNCTION parse_JRAD_Document(p_doc_full_name IN VARCHAR2)
820 RETURN xmldom.DOMDocument IS
821
822 l_buffer VARCHAR2(32000);
823 l_lob_loc CLOB;
824 l_amount BINARY_INTEGER;
825 l_position INTEGER := 1;
826 l_xmldoc xmldom.DOMDocument;
827 l_exportfinished BOOLEAN;
828
829 BEGIN
830
831 DBMS_LOB.CREATETEMPORARY(l_lob_loc, TRUE);
832 DBMS_LOB.OPEN(l_lob_loc, DBMS_LOB.LOB_READWRITE);
833
834 l_buffer := jdr_utils.ExportDocument(p_document => p_doc_full_name,
835 p_exportfinished => l_exportfinished);
836
837 IF l_buffer IS NULL THEN
838 RETURN l_xmldoc;
839 END IF;
840
841 l_amount := LENGTH(l_buffer);
842
843 DBMS_LOB.WRITE(l_lob_loc, l_amount, l_position, l_buffer);
844 l_position := l_position + l_amount;
845
846 IF l_exportfinished=FALSE THEN
847 LOOP
848 l_buffer := jdr_utils.ExportDocument(p_document => NULL,
849 p_exportfinished => l_exportfinished);
850
851 IF l_buffer IS NULL THEN
852 EXIT;
853 END IF;
854
855 l_amount := LENGTH(l_buffer);
856
857 DBMS_LOB.WRITE(l_lob_loc, l_amount, l_position, l_buffer);
858 l_position := l_position + l_amount;
859
860 IF l_buffer IS NULL OR l_exportfinished THEN
861 EXIT;
862 END IF;
863 END LOOP;
864
865 END IF;
866
867 xmlparser.parseCLOB(g_PARSER, l_lob_loc);
868
869 l_xmldoc := xmlparser.getDocument(g_PARSER);
870
871 DBMS_LOB.CLOSE(l_lob_loc);
872 DBMS_LOB.FREETEMPORARY(l_lob_loc);
873
874 RETURN l_xmldoc;
875
876 END parse_JRAD_Document;
877
878
879 PROCEDURE handle_Doc_Bad_Attributes
880 (p_doc_full_name IN VARCHAR2,
881 p_remove_bad_attributes IN BOOLEAN,
882 x_bad_attributes_detected OUT NOCOPY BOOLEAN) IS
883
884 l_xmldoc xmldom.DOMDocument;
885 l_node xmldom.DOMNode;
886 l_nodeslist xmldom.DOMNodeList;
887 l_empty_xml_node xmldom.DOMNode;
888 l_length NUMBER;
889 l_attribute_value VARCHAR2(32000);
890
891 BEGIN
892
893 x_bad_attributes_detected := FALSE;
894
895 l_xmldoc := parse_JRAD_Document(p_doc_full_name);
896
897 IF xmldom.IsNull(l_xmldoc) THEN
898 RETURN;
899 END IF;
900
901 l_node := xmldom.makeNode(xmldom.getDocumentElement(l_xmldoc));
902 l_nodeslist := xmldom.getElementsByTagName(l_xmldoc, '*');
903 l_length := xmldom.getLength(l_nodeslist);
904
905 IF NOT(xmldom.IsNull(l_node)) THEN
906 l_attribute_value := get_Attribute_Value(l_node, 'xmlns');
907 IF NVL(l_attribute_value,'x')='x' THEN
908 x_bad_attributes_detected := TRUE;
909 IF p_remove_bad_attributes=FALSE THEN
910 RETURN;
911 END IF;
912 END IF;
913 END IF;
914
915 FOR i IN 0 .. l_length - 1
916 LOOP
917 l_node := xmldom.item(l_nodeslist, i);
918 IF NOT(xmldom.IsNull(l_node)) THEN
919 l_attribute_value := get_Attribute_Value(l_node, 'xmlns');
920 IF NVL(l_attribute_value,'x')='x' THEN
921 x_bad_attributes_detected := TRUE;
922 IF p_remove_bad_attributes THEN
923 xmldom.removeAttribute(xmldom.makeElement(l_node),'xmlns');
924 ELSE
925 RETURN;
926 END IF;
927 END IF;
928 END IF;
929 END LOOP;
930
931 IF p_remove_bad_attributes THEN
932 Save_Document(p_xml_doc => l_xmldoc,
933 p_doc_name => p_doc_full_name);
934 END IF;
935
936 xmldom.freeDocument(l_xmldoc);
937
938 END handle_Doc_Bad_Attributes;
939
940 PROCEDURE handle_Bad_Element_Ids
941 (p_doc_full_name IN VARCHAR2,
942 p_ui_def_id IN NUMBER,
943 p_ui_page_id IN NUMBER) IS
944
945 l_xmldoc xmldom.DOMDocument;
946 l_node xmldom.DOMNode;
947 l_nodeslist xmldom.DOMNodeList;
948 l_empty_xml_node xmldom.DOMNode;
949 l_length NUMBER;
950
951 l_attribute_value VARCHAR2(32000);
952 l_user_attribute3_value VARCHAR2(32000);
953 l_case_node_name VARCHAR2(255);
954 l_switcher_casename VARCHAR2(255);
955
956 BEGIN
957
958 l_xmldoc := parse_JRAD_Document(p_doc_full_name);
959
960 IF xmldom.IsNull(l_xmldoc) THEN
961 RETURN;
962 END IF;
963
964 l_node := xmldom.makeNode(xmldom.getDocumentElement(l_xmldoc));
965 l_nodeslist := xmldom.getElementsByTagName(l_xmldoc, '*');
966 l_length := xmldom.getLength(l_nodeslist);
967
968 IF NOT(xmldom.IsNull(l_node)) THEN
969 l_attribute_value := get_Attribute_Value(l_node, G_ID_ATTRIBUTE);
970 IF l_attribute_value IS NOT NULL AND l_attribute_value<>G_NULL_VALUE THEN
971 set_Attribute(l_node,
972 G_ID_ATTRIBUTE,
973 REPLACE(REPLACE(l_attribute_value,'_czt','_czn'),'_czc','_czn'));
974 END IF;
975 END IF;
976
977 FOR i IN 0 .. l_length - 1
978 LOOP
979 l_node := xmldom.item(l_nodeslist, i);
980 IF NOT(xmldom.IsNull(l_node)) THEN
981
982 l_attribute_value := get_Attribute_Value(l_node, G_ID_ATTRIBUTE);
983
984 IF l_attribute_value IS NOT NULL AND l_attribute_value<>G_NULL_VALUE THEN
985 set_Attribute(l_node,
986 G_ID_ATTRIBUTE,
987 REPLACE(REPLACE(l_attribute_value,'_czt','_czn'),'_czc','_czn'));
988 END IF;
989
990 IF xmldom.getNodeName(l_node)='ui:case' THEN
991 l_case_node_name := get_Attribute_Value(l_node, 'name');
992 IF l_case_node_name IS NOT NULL THEN
993 set_Attribute(l_node,
994 'name',
995 REPLACE(REPLACE(l_case_node_name,'_czt','_czn'),'_czc','_czn'));
996 END IF;
997 END IF;
998
999 IF xmldom.getNodeName(l_node)='oa:switcher' THEN
1000
1001 l_user_attribute3_value := get_Attribute_Value(l_node, G_USER_ATTRIBUTE3_NAME);
1002
1003 l_switcher_casename := get_User_Attribute(l_user_attribute3_value ,'switcherDefaultCaseName');
1004
1005 l_switcher_casename := REPLACE(REPLACE(l_switcher_casename,'_czt', '_czn'),'_czc','_czn');
1006
1007 set_User_Attribute(p_cz_attribute_name => 'switcherDefaultCaseName',
1008 p_cz_attribute_value => l_switcher_casename,
1009 px_xml_attribute_value => l_user_attribute3_value);
1010
1011 set_Attribute(l_node,
1012 G_USER_ATTRIBUTE3_NAME,
1013 l_user_attribute3_value);
1014
1015 END IF;
1016
1017 END IF;
1018 END LOOP;
1019
1020 Save_Document(p_xml_doc => l_xmldoc,
1021 p_doc_name => p_doc_full_name);
1022
1023 xmldom.freeDocument(l_xmldoc);
1024
1025 END handle_Bad_Element_Ids;
1026
1027 PROCEDURE detect_Doc_Bad_Attributes
1028 (p_doc_full_name IN VARCHAR2,
1029 x_bad_attributes_detected OUT NOCOPY BOOLEAN) IS
1030
1031 l_xmldoc xmldom.DOMDocument;
1032 l_node xmldom.DOMNode;
1033 l_nodeslist xmldom.DOMNodeList;
1034 l_empty_xml_node xmldom.DOMNode;
1035 l_length NUMBER;
1036 l_attribute_value VARCHAR2(32000);
1037
1038 BEGIN
1039 handle_Doc_Bad_Attributes
1040 (p_doc_full_name => p_doc_full_name,
1041 p_remove_bad_attributes => FALSE,
1042 x_bad_attributes_detected => x_bad_attributes_detected);
1043 END detect_Doc_Bad_Attributes;
1044
1045 PROCEDURE remove_Bad_Attributes_in_Doc
1046 (p_doc_full_name IN VARCHAR2) IS
1047
1048 l_bad_attributes_detected BOOLEAN;
1049
1050 BEGIN
1051 handle_Doc_Bad_Attributes
1052 (p_doc_full_name => p_doc_full_name,
1053 p_remove_bad_attributes => TRUE,
1054 x_bad_attributes_detected => l_bad_attributes_detected);
1055 IF l_bad_attributes_detected THEN
1056 DEBUG('Bad attributes have been removed...');
1057 END IF;
1058 END remove_Bad_Attributes_in_Doc;
1059
1060
1061 PROCEDURE handle_UIS_with_Bad_Attributes(p_ui_def_id IN NUMBER DEFAULT NULL,
1062 p_remove_bad_attributes IN BOOLEAN) IS
1063 l_bad_attributes_detected BOOLEAN;
1064 l_flag BOOLEAN;
1065 BEGIN
1066 IF p_ui_def_id IS NULL THEN
1067 FOR i IN(SELECT ui_def_id,NAME
1068 FROM CZ_UI_DEFS
1069 WHERE deleted_flag='0' AND
1070 ui_style=G_OA_STYLE_UI AND seeded_flag='0')
1071 LOOP
1072 l_flag := FALSE;
1073 FOR j IN(SELECT jrad_doc,NAME FROM CZ_UI_PAGES
1074 WHERE ui_def_id=i.ui_def_id AND deleted_flag='0')
1075 LOOP
1076 handle_Doc_Bad_Attributes
1077 (p_doc_full_name => j.jrad_doc,
1078 p_remove_bad_attributes => p_remove_bad_attributes,
1079 x_bad_attributes_detected => l_bad_attributes_detected);
1080 IF l_bad_attributes_detected THEN
1081 l_flag := TRUE;
1082 IF l_flag THEN
1083 DEBUG('Corrupted UI : "'||i.NAME||'"');
1084 l_flag := FALSE;
1085 END IF;
1086 DEBUG(' |---> corrupted UI Page : "'||j.NAME||'"');
1087 END IF;
1088 END LOOP;
1089 END LOOP;
1090
1091 ELSE
1092 FOR i IN(SELECT ui_def_id,NAME
1093 FROM CZ_UI_DEFS
1094 WHERE ui_def_id=p_ui_def_id AND deleted_flag='0' AND
1095 ui_style=G_OA_STYLE_UI AND seeded_flag='0')
1096 LOOP
1097 FOR j IN(SELECT jrad_doc,NAME FROM CZ_UI_PAGES
1098 WHERE ui_def_id=i.ui_def_id AND deleted_flag='0')
1099 LOOP
1100 l_bad_attributes_detected := FALSE;
1101 handle_Doc_Bad_Attributes
1102 (p_doc_full_name => j.jrad_doc,
1103 p_remove_bad_attributes => p_remove_bad_attributes,
1104 x_bad_attributes_detected => l_bad_attributes_detected);
1105 IF l_bad_attributes_detected THEN
1106 DEBUG(' |---> corrupted UI Page : "'||j.NAME||'"');
1107 END IF;
1108 END LOOP;
1109 END LOOP;
1110
1111 END IF;
1112 END handle_UIS_with_Bad_Attributes;
1113
1114
1115 PROCEDURE replace_Bad_Element_Ids(p_ui_def_id IN NUMBER DEFAULT NULL) IS
1116 BEGIN
1117 IF p_ui_def_id IS NULL THEN
1118
1119 FOR i IN(SELECT ui_def_id,NAME
1120 FROM CZ_UI_DEFS
1121 WHERE deleted_flag='0' AND
1122 ui_style=G_OA_STYLE_UI AND seeded_flag='0')
1123 LOOP
1124 FOR j IN(SELECT jrad_doc,page_id,NAME FROM CZ_UI_PAGES
1125 WHERE ui_def_id=i.ui_def_id AND deleted_flag='0')
1126 LOOP
1127 handle_Bad_Element_Ids(p_doc_full_name => j.jrad_doc,
1128 p_ui_def_id => j.page_id,
1129 p_ui_page_id => i.ui_def_id);
1130 END LOOP;
1131
1132 UPDATE CZ_RULES
1133 SET UI_PAGE_ELEMENT_ID=REPLACE(REPLACE(UI_PAGE_ELEMENT_ID,'_czt','_czn'),'_czc','_czn')
1134 WHERE ui_def_id=i.ui_def_id;
1135
1136 UPDATE CZ_INTL_TEXTS
1137 SET UI_PAGE_ELEMENT_ID=REPLACE(REPLACE(UI_PAGE_ELEMENT_ID,'_czt','_czn'),'_czc','_czn')
1138 WHERE ui_def_id=i.ui_def_id;
1139
1140 END LOOP;
1141
1142 ELSE
1143
1144 FOR i IN(SELECT ui_def_id,NAME
1145 FROM CZ_UI_DEFS
1146 WHERE ui_def_id=p_ui_def_id AND deleted_flag='0' AND
1147 ui_style=G_OA_STYLE_UI AND seeded_flag='0')
1148 LOOP
1149 FOR j IN(SELECT jrad_doc,page_id,NAME FROM CZ_UI_PAGES
1150 WHERE ui_def_id=i.ui_def_id AND deleted_flag='0')
1151 LOOP
1152 handle_Bad_Element_Ids(p_doc_full_name => j.jrad_doc,
1153 p_ui_def_id => j.page_id,
1154 p_ui_page_id => i.ui_def_id);
1155
1156 END LOOP;
1157
1158 UPDATE CZ_RULES
1159 SET UI_PAGE_ELEMENT_ID=REPLACE(REPLACE(UI_PAGE_ELEMENT_ID,'_czt','_czn'),'_czc','_czn')
1160 WHERE ui_def_id=i.ui_def_id;
1161
1162 UPDATE CZ_INTL_TEXTS
1163 SET UI_PAGE_ELEMENT_ID=REPLACE(REPLACE(UI_PAGE_ELEMENT_ID,'_czt','_czn'),'_czc','_czn')
1164 WHERE ui_def_id=i.ui_def_id;
1165
1166 END LOOP;
1167
1168 END IF;
1169
1170 END replace_Bad_Element_Ids;
1171
1172 PROCEDURE detect_UIS_with_Bad_Attributes
1173 (p_ui_def_id IN NUMBER DEFAULT NULL,
1174 x_run_id OUT NOCOPY NUMBER,
1175 x_return_status OUT NOCOPY VARCHAR2,
1176 x_msg_count OUT NOCOPY NUMBER,
1177 x_msg_data OUT NOCOPY VARCHAR2) IS
1178
1179 BEGIN
1180
1181 Initialize(x_run_id,x_return_status,x_msg_count,x_msg_data);
1182
1183 x_return_status := FND_API.G_RET_STS_SUCCESS;x_msg_count:=0;
1184 Open_Parser();
1185 handle_UIS_with_Bad_Attributes(p_ui_def_id => p_ui_def_id,
1186 p_remove_bad_attributes => FALSE);
1187 Close_Parser();
1188
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 x_return_status := FND_API.G_RET_STS_ERROR;
1192 x_msg_count := 1;
1193 x_msg_data := 'Fatal error : '||SQLERRM;
1194 add_Error_Message(p_caller => 'CZ_XML.detect_UIS_with_Bad_Attributes',
1195 p_message_name => 'Close_Parser() : XML Parser can not be closed : '||SQLERRM,
1196 p_fatal_error => TRUE);
1197
1198 Close_Parser();
1199 END detect_UIS_with_Bad_Attributes;
1200
1201 PROCEDURE remove_Bad_Attributes_in_UIS
1202 (p_ui_def_id IN NUMBER DEFAULT NULL,
1203 x_run_id OUT NOCOPY NUMBER,
1204 x_return_status OUT NOCOPY VARCHAR2,
1205 x_msg_count OUT NOCOPY NUMBER,
1206 x_msg_data OUT NOCOPY VARCHAR2) IS
1207
1208 BEGIN
1209 Initialize(x_run_id,x_return_status,x_msg_count,x_msg_data);
1210 x_return_status := FND_API.G_RET_STS_SUCCESS;x_msg_count:=0;
1211 Open_Parser();
1212 handle_UIS_with_Bad_Attributes(p_ui_def_id => p_ui_def_id,
1213 p_remove_bad_attributes => TRUE);
1214 Close_Parser();
1215 EXCEPTION
1216 WHEN OTHERS THEN
1217 x_return_status := FND_API.G_RET_STS_ERROR;
1218 x_msg_count := 1;
1219 x_msg_data := 'Fatal error : '||SQLERRM;
1220 Close_Parser();
1221 END remove_Bad_Attributes_in_UIS;
1222
1223
1224 PROCEDURE handle_TMPLS_with_Bad_Attrs(p_template_id IN NUMBER DEFAULT NULL,
1225 p_remove_bad_attributes IN BOOLEAN) IS
1226 l_bad_attributes_detected BOOLEAN;
1227 BEGIN
1228 IF p_template_id IS NULL THEN
1229 FOR i IN(SELECT jrad_doc,template_name
1230 FROM CZ_UI_TEMPLATES
1231 WHERE deleted_flag='0' AND
1232 seeded_flag='0')
1233 LOOP
1234 handle_Doc_Bad_Attributes
1235 (p_doc_full_name => i.jrad_doc,
1236 p_remove_bad_attributes => p_remove_bad_attributes,
1237 x_bad_attributes_detected => l_bad_attributes_detected);
1238 IF l_bad_attributes_detected THEN
1239 DEBUG('Corrupted UI Template : "'||i.template_name||'"');
1240 END IF;
1241 END LOOP;
1242
1243 ELSE
1244 FOR i IN(SELECT jrad_doc,template_name
1245 FROM CZ_UI_TEMPLATES
1246 WHERE template_id=p_template_id AND
1247 deleted_flag='0' AND
1248 seeded_flag='0')
1249 LOOP
1250 handle_Doc_Bad_Attributes
1251 (p_doc_full_name => i.jrad_doc,
1252 p_remove_bad_attributes => p_remove_bad_attributes,
1253 x_bad_attributes_detected => l_bad_attributes_detected);
1254 IF l_bad_attributes_detected THEN
1255 DEBUG('Corrupted UI Template : "'||i.template_name||'"');
1256 END IF;
1257 END LOOP;
1258
1259 END IF;
1260 END handle_TMPLS_with_Bad_Attrs;
1261
1262 PROCEDURE detect_TMPLS_with_Bad_Attrs
1263 (p_template_id IN NUMBER DEFAULT NULL,
1264 x_run_id OUT NOCOPY NUMBER,
1265 x_return_status OUT NOCOPY VARCHAR2,
1266 x_msg_count OUT NOCOPY NUMBER,
1267 x_msg_data OUT NOCOPY VARCHAR2) IS
1268
1269 BEGIN
1270 Initialize(x_run_id,x_return_status,x_msg_count,x_msg_data);
1271 x_return_status := FND_API.G_RET_STS_SUCCESS;x_msg_count:=0;
1272 Open_Parser();
1273 handle_TMPLS_with_Bad_Attrs(p_template_id => p_template_id,
1274 p_remove_bad_attributes => FALSE);
1275 Close_Parser();
1276
1277 /*
1278 EXCEPTION
1279 WHEN OTHERS THEN
1280 x_return_status := FND_API.G_RET_STS_ERROR;
1281 x_msg_count := 1;
1282 x_msg_data := 'Fatal error : '||SQLERRM;
1283 Close_Parser();
1284 */
1285 END detect_TMPLS_with_Bad_Attrs;
1286
1287 PROCEDURE remove_Bad_Attributes_in_TMPLS
1288 (p_template_id IN NUMBER DEFAULT NULL,
1289 x_run_id OUT NOCOPY NUMBER,
1290 x_return_status OUT NOCOPY VARCHAR2,
1291 x_msg_count OUT NOCOPY NUMBER,
1292 x_msg_data OUT NOCOPY VARCHAR2) IS
1293 BEGIN
1294 Initialize(x_run_id,x_return_status,x_msg_count,x_msg_data);
1295 x_return_status := FND_API.G_RET_STS_SUCCESS;x_msg_count:=0;
1296 Open_Parser();
1297 handle_TMPLS_with_Bad_Attrs(p_template_id => p_template_id,
1298 p_remove_bad_attributes => TRUE);
1299 Close_Parser();
1300 EXCEPTION
1301 WHEN OTHERS THEN
1302 x_return_status := FND_API.G_RET_STS_ERROR;
1303 x_msg_count := 1;
1304 x_msg_data := 'Fatal error : '||SQLERRM;
1305 Close_Parser();
1306 END remove_Bad_Attributes_in_TMPLS;
1307
1308 PROCEDURE Test_UI_Pages(p_ui_def_id IN NUMBER) IS
1309
1310 l_xmldoc xmldom.DOMDocument;
1311 l_node xmldom.DOMNode;
1312
1313 BEGIN
1314 Open_Parser();
1315
1316 FOR i IN(SELECT * FROM CZ_UI_PAGES WHERE ui_def_id=p_ui_def_id AND deleted_flag='0')
1317 LOOP
1318 BEGIN
1319 l_xmldoc := parse_JRAD_Document(i.jrad_doc);
1320 Save_Document(p_xml_doc => l_xmldoc,
1321 p_doc_name => i.jrad_doc||'_TEST');
1322 xmldom.freeDocument(l_xmldoc);
1323 ROLLBACK;
1324 EXCEPTION
1325 WHEN OTHERS THEN
1326 --DBMS_OUTPUT.PUT_LINE('Fatal Error for : UI ui_def_id='||TO_CHAR(i.jrad_doc)||
1327 --' UI page page_id='||TO_CHAR(i.page_id));
1328 NULL;
1329 END;
1330 END LOOP;
1331
1332 Close_Parser();
1333
1334 END Test_UI_Pages;
1335
1336
1337 PROCEDURE replace_Bad_Element_Ids
1338 (p_ui_def_id IN NUMBER DEFAULT NULL,
1339 x_run_id OUT NOCOPY NUMBER,
1340 x_return_status OUT NOCOPY VARCHAR2,
1341 x_msg_count OUT NOCOPY NUMBER,
1342 x_msg_data OUT NOCOPY VARCHAR2) IS
1343
1344 BEGIN
1345 Initialize(x_run_id,x_return_status,x_msg_count,x_msg_data);
1346 x_return_status := FND_API.G_RET_STS_SUCCESS;x_msg_count:=0;
1347 Open_Parser();
1348 replace_Bad_Element_Ids(p_ui_def_id => p_ui_def_id);
1349 Close_Parser();
1350 /*
1351 EXCEPTION
1352 WHEN OTHERS THEN
1353 x_return_status := FND_API.G_RET_STS_ERROR;
1354 x_msg_count := 1;
1355 x_msg_data := 'Fatal error in CZ_XML.replace_Element_Ids() : '||SQLERRM;
1356 Close_Parser();
1357 */
1358 END replace_Bad_Element_Ids;
1359
1360
1361
1362 PROCEDURE restore_Elements_Hierarchy(p_xml_doc xmldom.DOMDocument,
1363 p_ui_def_id NUMBER,
1364 p_page_id NUMBER,
1365 p_element_id VARCHAR2
1366
1367 ) IS
1368
1369 l_xml_node xmldom.DOMNode;
1370 l_element_path VARCHAR2(32000);
1371
1372 PROCEDURE reconstruct_Parent_Elements(p_xml_node xmldom.DOMNode) IS
1373
1374 l_parent_node xmldom.DOMNode;
1375 l_element_id CZ_UI_PAGE_ELEMENTS.element_id%TYPE;
1376 l_user_attribute4_value VARCHAR2(32000);
1377 l_node_name VARCHAR2(32000);
1378
1379 BEGIN
1380
1381 IF xmldom.isNull(p_xml_node) THEN
1382 RETURN;
1383 END IF;
1384 l_parent_node := xmldom.getParentNode(p_xml_node);
1385 l_element_id := get_Attribute_Value(l_parent_node, G_ID_ATTRIBUTE);
1386
1387 IF l_element_id IS NOT NULL AND l_element_id<>G_NULL_VALUE AND xmldom.getNodeName(l_parent_node)<>'#document'THEN
1388
1389 FOR n IN(SELECT element_id FROM CZ_UI_PAGE_ELEMENTS
1390 WHERE ui_def_id=p_ui_def_id AND
1391 page_id=p_page_id AND
1392 element_id=l_element_id AND deleted_flag='0')
1393 LOOP
1394 UPDATE CZ_UI_PAGE_ELEMENTS
1395 SET parent_element_id=l_element_id
1396 WHERE ui_def_id=p_ui_def_id AND
1397 page_id=p_page_id AND
1398 element_id=p_element_id;
1399 RETURN;
1400 END LOOP;
1401
1402 END IF;
1403
1404 IF NOT(xmldom.IsNull(p_xml_node)) THEN
1405 IF NOT(xmldom.IsNull(l_parent_node)) THEN
1406 reconstruct_Parent_Elements(l_parent_node);
1407 END IF;
1408 END IF;
1409
1410 END reconstruct_Parent_Elements;
1411
1412 BEGIN
1413
1414 l_xml_node := find_XML_Node_By_Attribute(p_xml_doc, G_ID_ATTRIBUTE, p_element_id);
1415
1416 IF NOT(xmldom.IsNull(l_xml_node)) THEN
1417 reconstruct_Parent_Elements(l_xml_node);
1418 ELSE
1419 UPDATE CZ_UI_PAGE_ELEMENTS
1420 SET deleted_flag='1'
1421 WHERE ui_def_id=p_ui_def_id AND
1422 page_id=p_page_id AND
1423 element_id=p_element_id AND
1424 NOT(element_type=523 AND
1425 ctrl_template_id IS NULL) AND
1426 deleted_flag='0';
1427 END IF;
1428
1429 END restore_Elements_Hierarchy;
1430
1431
1432 PROCEDURE restore_Parent_Elements
1433 (p_ui_def_id IN NUMBER) IS
1434
1435 l_xmldoc xmldom.DOMDocument;
1436
1437 BEGIN
1438
1439 FOR i IN(SELECT page_id, jrad_doc FROM CZ_UI_PAGES
1440 WHERE ui_def_id=p_ui_def_id AND deleted_flag='0')
1441 LOOP
1442
1443 l_xmldoc := parse_JRAD_Document(i.jrad_doc);
1444
1445 FOR j IN(SELECT element_id
1446 FROM cz_ui_page_elements a
1447 WHERE a.ui_def_id=p_ui_def_id AND a.page_id=i.page_id AND a.deleted_flag='0' AND
1448 a.parent_element_id IS NOT NULL AND
1449 EXISTS(SELECT NULL FROM CZ_RP_ENTRIES d
1450 WHERE d.object_id IN(SELECT e.devl_project_id FROM CZ_UI_DEFS e
1451 WHERE e.ui_def_id=p_ui_def_id AND
1452 e.deleted_flag='0') AND d.object_type='PRJ' AND
1453 d.deleted_flag='0') AND
1454 EXISTS(SELECT NULL FROM CZ_UI_PAGES c
1455 WHERE c.page_id=i.page_id AND c.ui_def_id=p_ui_def_id AND
1456 c.deleted_flag='0') AND
1457 NOT EXISTS(SELECT NULL FROM cz_ui_page_elements b WHERE b.ui_def_id=p_ui_def_id AND
1458 b.page_id=i.page_id AND b.element_id=NVL(a.parent_element_id,'0')))
1459 LOOP
1460 restore_Elements_Hierarchy(l_xmldoc, p_ui_def_id, i.page_id, j.element_id);
1461 END LOOP;
1462
1463 xmldom.freeDocument(l_xmldoc);
1464
1465 END LOOP;
1466
1467 END restore_Parent_Elements;
1468
1469 PROCEDURE restore_Parent_Elements
1470 (p_ui_def_id IN NUMBER DEFAULT NULL,
1471 x_run_id OUT NOCOPY NUMBER,
1472 x_return_status OUT NOCOPY VARCHAR2,
1473 x_msg_count OUT NOCOPY NUMBER,
1474 x_msg_data OUT NOCOPY VARCHAR2) IS
1475
1476 BEGIN
1477 Initialize(x_run_id,x_return_status,x_msg_count,x_msg_data);
1478 x_return_status := FND_API.G_RET_STS_SUCCESS;x_msg_count:=0;
1479
1480 IF p_ui_def_id IS NULL THEN
1481 FOR i IN(SELECT ui_def_id FROM CZ_UI_DEFS WHERE ui_style='7' AND deleted_flag='0' AND seeded_flag='0')
1482 LOOP
1483 Open_Parser();
1484 BEGIN
1485 restore_Parent_Elements(p_ui_def_id => i.ui_def_id);
1486 EXCEPTION
1487 WHEN OTHERS THEN
1488 --DBMS_OUTPUT.PUT_LINE('UI '||i.ui_def_id||' can not be restored : '||SQLERRM);
1489 NULL;
1490 END;
1491 Close_Parser();
1492 END LOOP;
1493 ELSE
1494 FOR i IN(SELECT ui_def_id FROM CZ_UI_DEFS WHERE ui_style='7' AND deleted_flag='0' AND seeded_flag='0' AND
1495 ui_def_id IN(
1496 (SELECT ref_ui_def_id
1497 FROM cz_ui_refs
1498 START WITH ui_def_id = p_ui_def_id
1499 CONNECT by PRIOR ref_ui_def_id = ui_def_id AND
1500 deleted_flag = '0')
1501 UNION
1502 SELECT p_ui_def_id FROM dual) )
1503 LOOP
1504 Open_Parser();
1505 BEGIN
1506 restore_Parent_Elements(p_ui_def_id => i.ui_def_id);
1507 EXCEPTION
1508 WHEN OTHERS THEN
1509 --DBMS_OUTPUT.PUT_LINE('UI '||i.ui_def_id||' can not be restored : '||SQLERRM);
1510 NULL;
1511 END;
1512 Close_Parser();
1513 END LOOP;
1514 END IF;
1515
1516 EXCEPTION
1517 WHEN OTHERS THEN
1518 x_return_status := FND_API.G_RET_STS_ERROR;
1519 x_msg_count := 1;
1520 x_msg_data := 'Fatal error in CZ_XML.restore_Parent_Elements() : '||SQLERRM;
1521 Close_Parser();
1522 END restore_Parent_Elements;
1523
1524 PROCEDURE replace_UI_Rule_Ids(p_xml_node xmldom.DOMNode, p_replaced_flag OUT NOCOPY BOOLEAN) IS
1525 l_attribute_value VARCHAR2(32000);
1526 l_user_attribute3_value VARCHAR2(32000);
1527 BEGIN
1528 p_replaced_flag := FALSE;
1529
1530 l_user_attribute3_value := get_Attribute_Value(p_xml_node, G_USER_ATTRIBUTE3_NAME);
1531
1532 IF l_user_attribute3_value IS NULL OR l_user_attribute3_value=G_NULL_VALUE THEN
1533 RETURN;
1534 END IF;
1535
1536 l_attribute_value := get_User_Attribute(l_user_attribute3_value, 'displayCondnId');
1537
1538 IF l_attribute_value=822 THEN
1539 set_User_Attribute(p_cz_attribute_name => 'displayCondnId',
1540 p_cz_attribute_value => '915',
1541 px_xml_attribute_value => l_user_attribute3_value);
1542 set_Attribute(p_xml_node,
1543 G_USER_ATTRIBUTE3_NAME,
1544 l_user_attribute3_value);
1545 p_replaced_flag := TRUE;
1546 ELSIF l_attribute_value=875 THEN
1547 set_User_Attribute(p_cz_attribute_name => 'displayCondnId',
1548 p_cz_attribute_value => '916',
1549 px_xml_attribute_value => l_user_attribute3_value);
1550
1551 set_Attribute(p_xml_node,
1552 G_USER_ATTRIBUTE3_NAME,
1553 l_user_attribute3_value);
1554 p_replaced_flag := TRUE;
1555 END IF;
1556
1557 l_user_attribute3_value := get_Attribute_Value(p_xml_node, G_USER_ATTRIBUTE3_NAME);
1558 l_attribute_value := get_User_Attribute(l_user_attribute3_value, 'switcherCondnId');
1559
1560 IF l_attribute_value=822 THEN
1561 set_User_Attribute(p_cz_attribute_name => 'switcherCondnId',
1562 p_cz_attribute_value => '915',
1563 px_xml_attribute_value => l_user_attribute3_value);
1564 set_Attribute(p_xml_node,
1565 G_USER_ATTRIBUTE3_NAME,
1566 l_user_attribute3_value);
1567 p_replaced_flag := TRUE;
1568 ELSIF l_attribute_value=875 THEN
1569 set_User_Attribute(p_cz_attribute_name => 'switcherCondnId',
1570 p_cz_attribute_value => '916',
1571 px_xml_attribute_value => l_user_attribute3_value);
1572 set_Attribute(p_xml_node,
1573 G_USER_ATTRIBUTE3_NAME,
1574 l_user_attribute3_value);
1575 p_replaced_flag := TRUE;
1576 END IF;
1577
1578 END replace_UI_Rule_Ids;
1579
1580 PROCEDURE restore_UI_Rule_ids
1581 (p_ui_def_id IN NUMBER,
1582 p_template_id IN NUMBER) IS
1583
1584 l_xmldoc xmldom.DOMDocument;
1585 l_node xmldom.DOMNode;
1586 l_nodeslist xmldom.DOMNodeList;
1587 l_empty_xml_node xmldom.DOMNode;
1588 l_length NUMBER;
1589 l_save_doc BOOLEAN;
1590 l_replaced BOOLEAN;
1591
1592 BEGIN
1593 IF p_template_id IS NOT NULL AND p_template_id > 0 THEN -- UI Templates
1594 FOR i IN(SELECT jrad_doc FROM CZ_UI_TEMPLATES
1595 WHERE template_id=p_template_id AND ui_def_id IN(0,1) AND seeded_flag='0' AND deleted_flag='0')
1596 LOOP
1597 l_xmldoc := parse_JRAD_Document(i.jrad_doc);
1598
1599 l_nodeslist := xmldom.getElementsByTagName(l_xmldoc, '*');
1600 l_length := xmldom.getLength(l_nodeslist);
1601
1602 l_node := xmldom.makeNode(xmldom.getDocumentElement(l_xmldoc));
1603
1604 l_save_doc := FALSE;
1605
1606 replace_UI_Rule_Ids(l_node, l_replaced);
1607
1608 IF l_replaced THEN
1609 l_save_doc := TRUE;
1610 END IF;
1611
1612 --
1613 -- scan subtree and substitute macros "%" to real values
1614 --
1615 FOR i IN 0 .. l_length - 1
1616 LOOP
1617 l_node := xmldom.item(l_nodeslist, i);
1618 replace_UI_Rule_Ids(l_node, l_replaced);
1619 IF l_replaced THEN
1620 l_save_doc := TRUE;
1621 END IF;
1622 END LOOP;
1623
1624 IF l_save_doc THEN
1625 Save_Document(p_xml_doc => l_xmldoc,
1626 p_doc_name => i.jrad_doc);
1627 END IF;
1628
1629 xmldom.freeDocument(l_xmldoc);
1630
1631 END LOOP;
1632
1633 ELSE -- UIs
1634 FOR i IN(SELECT jrad_doc FROM CZ_UI_PAGES
1635 WHERE ui_def_id=p_ui_def_id AND deleted_flag='0')
1636 LOOP
1637
1638 l_xmldoc := parse_JRAD_Document(i.jrad_doc);
1639
1640 IF NOT(xmldom.isNull(l_xmldoc)) THEN
1641
1642 l_nodeslist := xmldom.getElementsByTagName(l_xmldoc, '*');
1643 l_length := xmldom.getLength(l_nodeslist);
1644
1645 l_node := xmldom.makeNode(xmldom.getDocumentElement(l_xmldoc));
1646
1647 l_save_doc := FALSE;
1648
1649 replace_UI_Rule_Ids(l_node, l_replaced);
1650
1651 IF l_replaced THEN
1652 l_save_doc := TRUE;
1653 END IF;
1654
1655 --
1656 -- scan subtree and substitute macros "%" to real values
1657 --
1658 FOR i IN 0 .. l_length - 1
1659 LOOP
1660 l_node := xmldom.item(l_nodeslist, i);
1661 replace_UI_Rule_Ids(l_node,l_replaced);
1662
1663 IF l_replaced THEN
1664 l_save_doc := TRUE;
1665 END IF;
1666 END LOOP;
1667
1668 IF l_save_doc THEN
1669 Save_Document(p_xml_doc => l_xmldoc,
1670 p_doc_name => i.jrad_doc);
1671 END IF;
1672 END IF;
1673 xmldom.freeDocument(l_xmldoc);
1674 END LOOP;
1675 END IF;
1676 END restore_UI_Rule_ids;
1677
1678
1679 PROCEDURE restore_UI_Rule_ids
1680 (p_ui_def_id IN NUMBER DEFAULT NULL,
1681 x_run_id OUT NOCOPY NUMBER,
1682 x_return_status OUT NOCOPY VARCHAR2,
1683 x_msg_count OUT NOCOPY NUMBER,
1684 x_msg_data OUT NOCOPY VARCHAR2) IS
1685
1686 BEGIN
1687 --dbms_output.enable(2000000);
1688 Initialize(x_run_id,x_return_status,x_msg_count,x_msg_data);
1689 x_return_status := FND_API.G_RET_STS_SUCCESS;x_msg_count:=0;
1690
1691 IF p_ui_def_id IS NULL THEN
1692 FOR i IN(SELECT ui_def_id FROM CZ_UI_DEFS WHERE ui_style='7' AND seeded_flag='0' AND deleted_flag='0')
1693 LOOP
1694 Open_Parser();
1695 BEGIN
1696 restore_UI_Rule_ids(p_ui_def_id => i.ui_def_id, p_template_id => NULL);
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699 IF SQLCODE=-29532 THEN
1700 Close_Parser(); --release all XMLDOM objects
1701 Open_Parser();
1702 --DBMS_OUTPUT.PUT_LINE('UI Rules for UI '||i.ui_def_id||' can not be restored (XMP Parser problem) : '||SQLERRM);
1703 ELSE
1704 --DBMS_OUTPUT.PUT_LINE('UI Rules for UI '||i.ui_def_id||' can not be restored : '||SQLERRM);
1705 NULL;
1706 END IF;
1707 END;
1708 Close_Parser();
1709 END LOOP;
1710 ELSE
1711 FOR i IN(SELECT ui_def_id FROM CZ_UI_DEFS
1712 WHERE ui_def_id=p_ui_def_id AND ui_style='7' AND seeded_flag='0' AND
1713 deleted_flag='0')
1714 LOOP
1715 Open_Parser();
1716 BEGIN
1717 restore_UI_Rule_ids(p_ui_def_id => i.ui_def_id, p_template_id => NULL);
1718 EXCEPTION
1719 WHEN OTHERS THEN
1720 --DBMS_OUTPUT.PUT_LINE('UI Rules for UI '||i.ui_def_id||' can not be restored : '||SQLERRM);
1721 NULL;
1722 END;
1723 Close_Parser();
1724 END LOOP;
1725 END IF;
1726
1727 EXCEPTION
1728 WHEN OTHERS THEN
1729 x_return_status := FND_API.G_RET_STS_ERROR;
1730 x_msg_count := 1;
1731 x_msg_data := 'Fatal error in CZ_XML.restore_UI_Rule_ids() : '||SQLERRM;
1732 Close_Parser();
1733 END restore_UI_Rule_ids;
1734
1735 PROCEDURE restore_TMPL_Rule_ids
1736 (p_template_id IN NUMBER DEFAULT NULL,
1737 x_run_id OUT NOCOPY NUMBER,
1738 x_return_status OUT NOCOPY VARCHAR2,
1739 x_msg_count OUT NOCOPY NUMBER,
1740 x_msg_data OUT NOCOPY VARCHAR2) IS
1741
1742 BEGIN
1743
1744 --dbms_output.enable(2000000);
1745
1746 Initialize(x_run_id,x_return_status,x_msg_count,x_msg_data);
1747 x_return_status := FND_API.G_RET_STS_SUCCESS;x_msg_count:=0;
1748
1749 IF p_template_id IS NULL THEN
1750 FOR i IN(SELECT template_id FROM CZ_UI_TEMPLATES WHERE seeded_flag='0' AND deleted_flag='0')
1751 LOOP
1752 Open_Parser();
1753 BEGIN
1754 restore_UI_Rule_ids(p_ui_def_id => NULL, p_template_id => i.template_id);
1755 EXCEPTION
1756 WHEN OTHERS THEN
1757 --DBMS_OUTPUT.PUT_LINE('UI Rules for UI Template '||i.template_id||' can not be restored : '||SQLERRM);
1758 NULL;
1759 END;
1760 Close_Parser();
1761 END LOOP;
1762 ELSE
1763 FOR i IN(SELECT template_id FROM CZ_UI_TEMPLATES
1764 WHERE template_id=p_template_id AND seeded_flag='0' AND
1765 deleted_flag='0')
1766
1767 LOOP
1768 Open_Parser();
1769 BEGIN
1770 restore_UI_Rule_ids(p_ui_def_id => NULL, p_template_id => i.template_id);
1771 EXCEPTION
1772 WHEN OTHERS THEN
1773 --DBMS_OUTPUT.PUT_LINE('UI Rules for UI Template '||i.template_id||' can not be restored : '||SQLERRM);
1774 NULL;
1775 END;
1776 Close_Parser();
1777 END LOOP;
1778 END IF;
1779
1780 EXCEPTION
1781 WHEN OTHERS THEN
1782 x_return_status := FND_API.G_RET_STS_ERROR;
1783 x_msg_count := 1;
1784 x_msg_data := 'Fatal error in CZ_XML.restore_TMPL_Rule_ids() : '||SQLERRM;
1785 Close_Parser();
1786 END restore_TMPL_Rule_ids;
1787
1788 -------------------------------------------------------------------------------------
1789 -------------------------------------------------------------------------------------
1790
1791 PROCEDURE cache_UI_elements(p_ui_def_id IN NUMBER, x_ui_elements_tbl IN OUT NOCOPY ui_tbl_type) IS
1792
1793 TYPE arr_tbl_type IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
1794 l_arr_tbl arr_tbl_type;
1795 BEGIN
1796
1797 SELECT element_id BULK COLLECT INTO l_arr_tbl FROM CZ_UI_PAGE_ELEMENTS
1798 WHERE ui_def_id=p_ui_def_id AND deleted_flag='0';
1799
1800 IF l_arr_tbl.COUNT>0 THEN
1801 FOR i IN l_arr_tbl.First..l_arr_tbl.Last
1802 LOOP
1803 x_ui_elements_tbl(l_arr_tbl(i)) := l_arr_tbl(i);
1804 END LOOP;
1805 END IF;
1806
1807 END cache_UI_elements;
1808
1809 --------------------------------------------------------------------
1810 --------------------------------------------------------------------
1811
1812 PROCEDURE add_Template_References
1813 (x_run_id OUT NOCOPY NUMBER,
1814 x_return_status OUT NOCOPY VARCHAR2,
1815 x_msg_count OUT NOCOPY NUMBER,
1816 x_msg_data OUT NOCOPY VARCHAR2) IS
1817
1818 l_error_message VARCHAR2(32000);
1819 l_error BOOLEAN;
1820 l_xmldoc xmldom.DOMDocument;
1821 l_element_id CZ_UI_PAGE_ELEMENTS.element_id%TYPE;
1822 l_xml_node xmldom.DOMNode;
1823 l_nodeslist xmldom.DOMNodeList;
1824 l_empty_xml_node xmldom.DOMNode;
1825 l_length NUMBER;
1826 l_attribute_value VARCHAR2(32000);
1827 l_id_attribute_value VARCHAR2(32000);
1828 l_ui_elements_tbl ui_tbl_type;
1829 l_seq_nbr NUMBER;
1830 l_parent_element_id CZ_UI_PAGE_ELEMENTS.parent_element_id%TYPE;
1831 l_ui_node CZ_UI_PAGE_ELEMENTS%ROWTYPE;
1832 l_ui_def_id NUMBER;
1833
1834 FUNCTION find_Parent_UI_Element(p_xml_node xmldom.DOMNode) RETURN VARCHAR2 IS
1835
1836 l_ui_element CZ_UI_PAGE_ELEMENTS.element_id%TYPE;
1837
1838 PROCEDURE get_Parent_XML(p_check_xml_node xmldom.DOMNode) IS
1839
1840 l_parent_xml_node xmldom.DOMNode;
1841 l_id_attr_value VARCHAR2(32000);
1842
1843 BEGIN
1844
1845 l_id_attr_value := get_Attribute_Value(p_check_xml_node, G_ID_ATTRIBUTE);
1846
1847 IF l_id_attr_value IS NOT NULL AND l_id_attr_value<>G_NULL_VALUE THEN
1848
1849 IF l_ui_elements_tbl.EXISTS(l_id_attr_value) THEN
1850 l_ui_element := l_ui_elements_tbl(l_id_attr_value);
1851 RETURN;
1852 END IF;
1853 END IF;
1854
1855 l_parent_xml_node := xmldom.getParentNode(p_check_xml_node);
1856
1857
1858 IF NOT(xmldom.isNull(l_parent_xml_node)) THEN
1859
1860 l_id_attr_value := get_Attribute_Value(l_parent_xml_node , G_ID_ATTRIBUTE);
1861
1862
1863 IF l_id_attr_value IS NOT NULL AND l_id_attr_value<>G_NULL_VALUE THEN
1864
1865 IF l_ui_elements_tbl.EXISTS(l_id_attr_value) THEN
1866 l_ui_element := l_ui_elements_tbl(l_id_attr_value);
1867 RETURN;
1868 ELSE
1869 get_Parent_XML(l_parent_xml_node);
1870 END IF;
1871
1872 ELSE
1873 get_Parent_XML(l_parent_xml_node);
1874 END IF;
1875
1876 ELSE
1877 RETURN;
1878 END IF;
1879
1880 END get_Parent_XML;
1881
1882
1883 BEGIN
1884
1885 get_Parent_XML(p_xml_node);
1886
1887 RETURN l_ui_element;
1888
1889 END find_Parent_UI_Element;
1890
1891
1892
1893 BEGIN
1894
1895 Initialize(x_run_id,x_return_status,x_msg_count,x_msg_data);
1896 x_return_status := FND_API.G_RET_STS_SUCCESS;x_msg_count:=0;
1897
1898 Open_Parser();
1899
1900 FOR i IN(SELECT a.ui_def_id FROM CZ_UI_DEFS a
1901 WHERE a.ui_style='7' AND a.deleted_flag='0' AND
1902 EXISTS(SELECT NULL FROM CZ_RP_ENTRIES b WHERE b.object_id=a.devl_project_id AND
1903 b.object_type='PRJ' AND b.deleted_flag='0')
1904 )
1905 LOOP
1906
1907 l_ui_def_id := i.ui_def_id;
1908
1909 l_ui_elements_tbl.DELETE;
1910 cache_UI_elements(i.ui_def_id, l_ui_elements_tbl);
1911
1912 FOR j IN(SELECT * FROM CZ_UI_PAGES WHERE ui_def_id=i.ui_def_id AND deleted_flag='0')
1913 LOOP
1914 BEGIN
1915 jdr_docbuilder.REFRESH;
1916 l_xmldoc := parse_JRAD_Document(j.jrad_doc);
1917
1918 IF xmldom.IsNull(l_xmldoc) THEN
1919 EXIT;
1920 END IF;
1921
1922 l_nodeslist := xmldom.getElementsByTagName(l_xmldoc, '*');
1923 l_length := xmldom.getLength(l_nodeslist);
1924
1925 FOR ixml IN 0 .. l_length - 1
1926 LOOP
1927 l_xml_node := xmldom.item(l_nodeslist, ixml);
1928
1929 l_attribute_value := get_Attribute_Value(l_xml_node, 'extends');
1930
1931 IF l_attribute_value IS NOT NULL AND l_attribute_value <> G_NULL_VALUE THEN
1932
1933 l_id_attribute_value := get_Attribute_Value(l_xml_node, G_ID_ATTRIBUTE);
1934
1935 IF l_id_attribute_value IS NOT NULL AND l_id_attribute_value <> G_NULL_VALUE THEN
1936
1937 l_parent_element_id := find_Parent_UI_Element(l_xml_node);
1938
1939 IF l_parent_element_id <> l_id_attribute_value AND l_attribute_value IS NOT NULL AND l_attribute_value <> G_NULL_VALUE THEN
1940
1941 SELECT * INTO l_ui_node FROM CZ_UI_PAGE_ELEMENTS
1942 WHERE ui_def_id=i.ui_def_id AND page_id=j.page_id AND element_id=l_parent_element_id;
1943
1944 SELECT CZ_UI_PAGE_ELEMENTS_S.NEXTVAL INTO l_element_id FROM dual;
1945
1946 FOR templ IN(SELECT template_id,template_name FROM CZ_UI_TEMPLATES
1947 WHERE ui_def_id=0 AND jrad_doc=l_attribute_value AND deleted_flag='0')
1948 LOOP
1949 SELECT NVL(max(seq_nbr),0)+1 INTO l_seq_nbr FROM CZ_UI_PAGE_ELEMENTS
1950 WHERE ui_def_id=i.ui_def_id AND
1951 page_id=j.page_id AND
1952 parent_element_id=l_parent_element_id AND
1953 deleted_flag='0';
1954
1955 INSERT INTO CZ_UI_PAGE_ELEMENTS
1956 (
1957 UI_DEF_ID
1958 ,PAGE_ID
1959 ,PERSISTENT_NODE_ID
1960 ,ELEMENT_ID
1961 ,PARENT_PERSISTENT_NODE_ID
1962 ,REGION_PERSISTENT_NODE_ID
1963 ,PAGEBASE_PERSISTENT_NODE_ID
1964 ,CTRL_TEMPLATE_ID
1965 ,BASE_PAGE_FLAG
1966 ,INSTANTIABLE_FLAG
1967 ,SEQ_NBR
1968 ,DELETED_FLAG
1969 ,CTRL_TEMPLATE_UI_DEF_ID
1970 ,MODEL_REF_EXPL_ID
1971 ,SUPPRESS_REFRESH_FLAG
1972 ,PARENT_ELEMENT_ID
1973 ,ELEMENT_TYPE
1974 ,NAME
1975 ,ELEMENT_SIGNATURE_ID
1976 ,TARGET_PAGE_UI_DEF_ID
1977 ,TARGET_PAGE_ID
1978 )
1979 VALUES
1980 (
1981 i.ui_def_id
1982 ,j.page_id
1983 ,NULL
1984 ,l_element_id
1985 ,l_ui_node.parent_persistent_node_id
1986 ,l_ui_node.region_persistent_node_id
1987 ,l_ui_node.pagebase_persistent_node_id
1988 ,templ.template_id
1989 ,'0'
1990 ,'0'
1991 ,l_seq_nbr
1992 ,'0'
1993 ,0
1994 ,NULL
1995 ,'0'
1996 ,l_parent_element_id
1997 ,NULL
1998 ,templ.template_name
1999 ,6011
2000 ,NULL
2001 ,NULL
2002 );
2003
2004 set_Attribute(l_xml_node, G_ID_ATTRIBUTE, l_element_id);
2005
2006 END LOOP;
2007
2008 END IF; -- end of IF l_parent_element_id <> l_id_attribute_value THEN
2009
2010 END IF; -- end of IF l_id_attribute_value IS NOT NULL THEN
2011
2012 END IF;
2013
2014 END LOOP;
2015
2016 BEGIN
2017 Save_Document(p_xml_doc => l_xmldoc,
2018 p_doc_name => j.jrad_doc);
2019 EXCEPTION
2020 WHEN OTHERS THEN
2021 IF SQLCODE=1 THEN -- exception in JDR_DOCBUILDER
2022 l_error_message := 'UI with ui_def_id='||i.ui_def_id||' page_id='||j.page_id||
2023 ' can not be upgraded because of some problems with its JRAD XML';
2024 l_error :=CZ_UTILS.REPORT(l_error_message, 1, 'czueupg.sql', 13000);
2025 END IF;
2026 END;
2027
2028 xmldom.freeDocument(l_xmldoc);
2029
2030 END;
2031
2032 END LOOP;
2033
2034 END LOOP;
2035
2036 Close_Parser();
2037
2038 EXCEPTION
2039 WHEN OTHERS THEN
2040 x_return_status := FND_API.G_RET_STS_ERROR;
2041 x_msg_count := 1;
2042 x_msg_data := 'Fatal error in add_Template_References() : '||SQLERRM;
2043 END add_Template_References;
2044
2045 END CZ_XML;