[Home] [Help]
PACKAGE BODY: APPS.AMS_IMPORT_XML_PVT
Source
1 PACKAGE BODY AMS_Import_XML_PVT AS
2 /* $Header: amsvmixb.pls 120.1 2006/01/18 03:14:10 rmbhanda noship $ */
3 --------------------------------------------------------------------------------
4 --
5 -- NAME
6 -- AMS_Import_XML_PVT
7 --
8 -- HISTORY
9 -- 02-Apr-2002 huili Created
10 -- 01-May-2002 huili Added profile checking and possible concurrent program.
11 -- 18-May-2002 huili Removed the "Filter_XML" call inside the "Store_XML_Util".
12 -- 03-June-2002 huili Added code to deal with unmapped columns.
13 -- 04-June-2002 huili Tuned APIs.
14 -- 09-Aug-2002 huili Added overloaded "Get_Children_Nodes" which returns
15 -- table of all children records.
16 ------------------------------------------------------------------------------
17 --
18 -- Global variables and constants.
19 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMS_Import_XML_PVT'; -- Name of the current package.
20 G_ORDER_INITIAL_START_NUMBER CONSTANT NUMBER := 1;
21 G_COUNT NUMBER := 1;
22 G_ARC_IMPORT_HEADER CONSTANT VARCHAR2(30) := 'IMPH';
23 G_DATA_TYPE_DATA CONSTANT VARCHAR2(1) := 'D';
24 G_DATA_TYPE_TAG CONSTANT VARCHAR2(1) := 'T';
25
26 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
27 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
28 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
29
30 FUNCTION Get_Children_Cursor (
31 p_imp_doc_id IN NUMBER,
32
33 p_order_initial IN NUMBER) RETURN rc_type
34 IS
35 l_children rc_type;
36 BEGIN
37 OPEN l_children FOR
38 SELECT e3.*
39 FROM ams_imp_xml_elements e3
40 START WITH e3.imp_xml_document_id = p_imp_doc_id
41 AND e3.order_initial = p_order_initial + 1
42 CONNECT BY e3.imp_xml_document_id = p_imp_doc_id
43 AND PRIOR order_final + 1 = order_initial;
44 RETURN l_children;
45 END Get_Children_Cursor;
46
47 PROCEDURE Get_Element_Info (
48 p_element_id IN NUMBER,
49 x_element_info OUT NOCOPY AMS_IMP_XML_ELEMENTS%ROWTYPE
50 )
51 IS
52 CURSOR c_get_element (p_imp_xml_element_id NUMBER) IS
53 SELECT *
54 FROM ams_imp_xml_elements
55 WHERE IMP_XML_ELEMENT_ID = p_imp_xml_element_id;
56 BEGIN
57 OPEN c_get_element (p_element_id);
58 FETCH c_get_element INTO x_element_info;
59 CLOSE c_get_element;
60 END Get_Element_Info;
61
62 PROCEDURE write_debug (
63 p_msg VARCHAR2
64 )
65 IS
66 BEGIN
67 --insert into ams_xml_test1 values (G_COUNT || p_msg);
68 --G_COUNT := G_COUNT + 1;
69 --commit;
70 NULL;
71 END write_debug;
72
73 PROCEDURE Filter_XML_Helper (
74 p_node IN OUT NOCOPY xmldom.DOMNode,
75 p_mapping IN xml_source_column_set_type,
76 p_source_col_name IN VARCHAR2
77 );
78
79 FUNCTION Is_In_Mapping (
80 p_item IN VARCHAR2,
81 p_mapping IN xml_source_column_set_type
82 ) RETURN BOOLEAN;
83
84 PROCEDURE Store_XML_Elements (
85 p_xml_doc_id IN NUMBER,
86 p_imp_list_header_id IN NUMBER,
87 p_xml_content IN CLOB,
88 p_commit IN VARCHAR2 := FND_API.G_FALSE,
89 x_return_status OUT NOCOPY VARCHAR2,
90 x_msg_data OUT NOCOPY VARCHAR2
91 );
92
93 --PROCEDURE Store_XML_Elements_Helper (
94 -- p_node IN xmldom.DOMNode,
95 -- p_source_fields IN xml_source_column_set_type,
96 -- p_target_fields IN xml_target_column_set_type,
97 -- p_col_name IN VARCHAR2,
98 -- p_xml_doc_id IN NUMBER,
99 -- p_commit IN VARCHAR2 := FND_API.G_TRUE,
100 -- x_order_num IN OUT NUMBER
101 --);
102
103 PROCEDURE Store_XML_Elements_Helper (
104 p_node IN xmldom.DOMNode,
105 p_source_fields IN xml_source_column_set_type,
106 p_target_fields IN xml_target_column_set_type,
107 p_col_name IN VARCHAR2,
108 p_xml_doc_id IN NUMBER,
109 p_commit IN VARCHAR2 := FND_API.G_FALSE,
110 x_order_num IN OUT NOCOPY NUMBER,
111 p_result_node IN OUT NOCOPY xmldom.DOMNode,
112 p_column_name IN OUT NOCOPY VARCHAR2,
113 p_value IN OUT NOCOPY VARCHAR2,
114 p_result_doc IN OUT NOCOPY xmldom.DOMDocument
115 );
116
117 FUNCTION Store_XML_Attributes (
118 p_node IN xmldom.DOMNode,
119 p_order_init IN NUMBER,
120 p_xml_doc_id IN NUMBER,
121 p_commit IN VARCHAR2 := FND_API.G_FALSE
122 ) RETURN NUMBER;
123
124 FUNCTION Get_Col_name (
125 p_source_fields IN xml_source_column_set_type,
126 p_target_fields IN xml_target_column_set_type,
127 p_col_name IN VARCHAR2
128 ) RETURN VARCHAR2;
129 --- End forward modules
130
131
132 -- Start of comments
133 -- API Name Is_Leaf_Node
134 -- Type Public
135 -- Pre-reqs None.
136 -- Function Determine whether the given element is leaf or not
137 -- Parameters
138 -- IN
139 -- p_imp_xml_element_id NUMBER Required
140 -- OUT
141 -- x_return_status VARCHAR2
142 -- x_msg_data VARCHAR2
143 --
144 -- Version Current version: 1.0
145 -- Previous version: 1.0
146 -- Initial version: 1.0
147 -- End of comments
148 FUNCTION Is_Leaf_Node (
149 p_imp_xml_element_id IN NUMBER,
150 x_return_status OUT NOCOPY VARCHAR2,
151 x_msg_data OUT NOCOPY VARCHAR2
152 ) RETURN BOOLEAN
153
154 IS
155 L_API_NAME CONSTANT VARCHAR2(30) := 'Is_Leaf_Node';
156 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
157
158 CURSOR c_element_info (p_xml_element_id NUMBER)
159 IS SELECT ORDER_INITIAL, ORDER_FINAL
160 FROM AMS_IMP_XML_ELEMENTS
161 WHERE IMP_XML_ELEMENT_ID = p_xml_element_id;
162
163 l_element_info c_element_info%ROWTYPE;
164 BEGIN
165
166 --
167 -- Initialize API return status to success.
168 --
169 x_return_status := FND_API.G_RET_STS_SUCCESS;
170
171 IF p_imp_xml_element_id IS NULL THEN
172 x_msg_data := 'Expected error in ' || L_FULL_NAME
173 || ' list import header is null';
174 RAISE FND_API.G_EXC_ERROR;
175 END IF;
176
177 OPEN c_element_info (p_imp_xml_element_id);
178 FETCH c_element_info INTO l_element_info;
179 CLOSE c_element_info;
180
181 IF l_element_info.ORDER_INITIAL IS NOT NULL AND l_element_info.ORDER_FINAL IS NOT NULL
182 AND l_element_info.ORDER_FINAL = l_element_info.ORDER_INITIAL + 1 THEN
183 RETURN TRUE;
184 ELSE
185 RETURN FALSE;
186 END IF;
187
188 EXCEPTION
189 WHEN FND_API.G_EXC_ERROR THEN
190 x_return_status := FND_API.G_RET_STS_ERROR;
191 WHEN OTHERS THEN
192 x_msg_data := 'Unexpected error in '
193 || L_FULL_NAME || ': '|| SQLERRM;
194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
195 END Is_Leaf_Node;
196
197
198 -- Start of comments
199 -- API Name Get_File_Type
200 -- Type Public
201 -- Pre-reqs None.
202 -- Function Retrieve the information for the root node in the
203 -- "AMS_IMP_XML_ELEMENTS" table, given the
204 -- "import_list_header_id".
205 -- Parameters
206 -- IN
207 -- p_import_list_header_id NUMBER Required
208 -- OUT x_node_rec AMS_IMP_XML_ELEMENTS%ROWTYPE
209 -- x_return_status VARCHAR2
210 --
211 -- Version Current version: 1.0
212 -- Previous version: 1.0
213 -- Initial version: 1.0
214 -- End of comments
215 PROCEDURE Get_File_Type (
216 p_import_list_header_id IN NUMBER,
217 x_file_type OUT NOCOPY AMS_IMP_DOCUMENTS.FILE_TYPE%TYPE,
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_data OUT NOCOPY VARCHAR2
220 )
221 IS
222 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_File_Type';
223 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
224
225 CURSOR c_file_type (p_import_list_header_id NUMBER) IS
226 SELECT FILE_TYPE
227 FROM AMS_IMP_DOCUMENTS
228 WHERE IMPORT_LIST_HEADER_ID = p_import_list_header_id;
229 BEGIN
230
231 --
232 -- Initialize API return status to success.
233 --
234 x_return_status := FND_API.G_RET_STS_SUCCESS;
235
236 IF p_import_list_header_id IS NULL THEN
237 x_msg_data := 'Expected error in ' || L_FULL_NAME
238 || ' list import header is null';
239 RAISE FND_API.G_EXC_ERROR;
240 END IF;
241
242 OPEN c_file_type (p_import_list_header_id);
243 FETCH c_file_type INTO x_file_type;
244 CLOSE c_file_type;
245
246 EXCEPTION
247 WHEN FND_API.G_EXC_ERROR THEN
248 x_return_status := FND_API.G_RET_STS_ERROR;
249 WHEN OTHERS THEN
250 x_msg_data := 'Unexpected error in '
251 || L_FULL_NAME || ': '|| SQLERRM;
252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
253 END Get_File_Type;
254
255
256 -- Start of comments
257 -- API Name Get_Root_Node
258 -- Type Public
259 -- Pre-reqs None.
260 -- Function Retrieve the information for the root node in the
261 -- "AMS_IMP_XML_ELEMENTS" table, given the
262 -- "import_list_header_id".
263 -- Parameters
264 -- IN
265 -- p_import_list_header_id NUMBER Required
266 -- OUT x_node_rec AMS_IMP_XML_ELEMENTS%ROWTYPE
267 -- x_return_status VARCHAR2
268 --
269 -- Version Current version: 1.0
270 -- Previous version: 1.0
271 -- Initial version: 1.0
272 -- End of comments
273 PROCEDURE Get_Root_Node (
274 p_import_list_header_id IN NUMBER,
275 x_node_rec OUT NOCOPY AMS_IMP_XML_ELEMENTS%ROWTYPE,
276 x_return_status OUT NOCOPY VARCHAR2,
277 x_msg_data OUT NOCOPY VARCHAR2
278 )
279 IS
280 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Root_Node';
281 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
282
283 CURSOR c_root_node_rec (p_import_list_header_id NUMBER) IS
284 SELECT *
285 FROM AMS_IMP_XML_ELEMENTS
286 WHERE ORDER_INITIAL = G_ORDER_INITIAL_START_NUMBER
287 AND EXISTS (SELECT 1
288 FROM AMS_IMP_DOCUMENTS
289 WHERE AMS_IMP_DOCUMENTS.IMPORT_LIST_HEADER_ID = p_import_list_header_id
290 AND AMS_IMP_DOCUMENTS.IMP_DOCUMENT_ID = AMS_IMP_XML_ELEMENTS.IMP_XML_DOCUMENT_ID);
291 BEGIN
292
293 --
294 -- Initialize API return status to success.
295 --
296 x_return_status := FND_API.G_RET_STS_SUCCESS;
297
298 IF p_import_list_header_id IS NULL THEN
299 x_msg_data := 'Expected error in ' || L_FULL_NAME
300 || ' list import header is null';
301 RAISE FND_API.G_EXC_ERROR;
302 END IF;
303
304 OPEN c_root_node_rec (p_import_list_header_id);
305 FETCH c_root_node_rec INTO x_node_rec;
306 CLOSE c_root_node_rec;
307
308 EXCEPTION
309 WHEN FND_API.G_EXC_ERROR THEN
310 x_return_status := FND_API.G_RET_STS_ERROR;
311 WHEN OTHERS THEN
312 x_msg_data := 'Unexpected error in '
313 || L_FULL_NAME || ': '|| SQLERRM;
314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
315 END Get_Root_Node;
316
317 -- Start of comments
318 -- API Name Get_First_Child_Node
319 -- Type Public
320 -- Pre-reqs None.
321 -- Function Retrieve the information for the first child node in the
322 -- "AMS_IMP_XML_ELEMENTS" table, given the node id
323 -- Parameters
324 -- IN
325 -- p_imp_xml_element_id NUMBER Required
326 -- OUT x_node_rec AMS_IMP_XML_ELEMENTS%ROWTYPE
327 -- x_return_status VARCHAR2
328 --
329 -- Version Current version: 1.0
330 -- Previous version: 1.0
331 -- Initial version: 1.0
332 -- End of comments
333 PROCEDURE Get_First_Child_Node (
334 p_imp_xml_element_id IN NUMBER,
335 x_node_rec OUT NOCOPY AMS_IMP_XML_ELEMENTS%ROWTYPE,
336 x_return_status OUT NOCOPY VARCHAR2,
337 x_msg_data OUT NOCOPY VARCHAR2
338 )
339 IS
340 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_First_Child_Node';
341 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
342
343 CURSOR c_xml_element (p_xml_element_id NUMBER) IS
344 SELECT IMP_XML_ELEMENT_ID,
345 IMP_XML_DOCUMENT_ID,
346 ORDER_INITIAL
347 FROM AMS_IMP_XML_ELEMENTS
348 WHERE IMP_XML_ELEMENT_ID = p_xml_element_id;
349
350 CURSOR c_xml_first_child_element (p_xml_doc_id NUMBER, p_order_initial NUMBER) IS
351 SELECT *
352 FROM AMS_IMP_XML_ELEMENTS
353 WHERE IMP_XML_DOCUMENT_ID = p_xml_doc_id
354 AND ORDER_INITIAL = p_order_initial + 1;
355
356 l_xml_element_rec c_xml_element%ROWTYPE;
357 BEGIN
358 --
359 -- Initialize API return status to success.
360 --
361 x_return_status := FND_API.G_RET_STS_SUCCESS;
362
363 IF p_imp_xml_element_id IS NULL THEN
364 x_msg_data := 'Expected error in ' || L_FULL_NAME
365 || ' xml element id is null';
366 RAISE FND_API.G_EXC_ERROR;
367 END IF;
368
369 OPEN c_xml_element (p_imp_xml_element_id);
370 FETCH c_xml_element INTO l_xml_element_rec;
371
372
373 IF c_xml_element%FOUND THEN
374 OPEN c_xml_first_child_element (l_xml_element_rec.IMP_XML_DOCUMENT_ID,
375 l_xml_element_rec.ORDER_INITIAL);
376 FETCH c_xml_first_child_element INTO x_node_rec;
377 CLOSE c_xml_first_child_element;
378 END IF;
379 CLOSE c_xml_element;
380
381 EXCEPTION
382 WHEN FND_API.G_EXC_ERROR THEN
383 x_return_status := FND_API.G_RET_STS_ERROR;
384 WHEN OTHERS THEN
385 x_msg_data := 'Unexpected error in '
386 || L_FULL_NAME || ': '|| SQLERRM;
387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
388 END Get_First_Child_Node;
389
390 -- Start of comments
391 -- API Name Get_Next_Sibling_Node
392 -- Type Public
393 -- Pre-reqs None.
394 -- Function Retrieve the information for the first child node in the
395 -- "AMS_IMP_XML_ELEMENTS" table, given the node id
396 -- Parameters
397 -- IN
398 -- p_imp_xml_element_id NUMBER Required
399 -- OUT x_node_rec AMS_IMP_XML_ELEMENTS%ROWTYPE
400 -- x_return_status VARCHAR2
401 --
402 -- Version Current version: 1.0
403 -- Previous version: 1.0
404 -- Initial version: 1.0
405 -- End of comments
406 PROCEDURE Get_Next_Sibling_Node (
407 p_imp_xml_element_id IN NUMBER,
408 x_node_rec OUT NOCOPY AMS_IMP_XML_ELEMENTS%ROWTYPE,
409 x_return_status OUT NOCOPY VARCHAR2,
410 x_msg_data OUT NOCOPY VARCHAR2
411 )
412 IS
413 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Next_Sibling_Node';
414 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
415
416 CURSOR c_xml_element (p_xml_element_id NUMBER) IS
417 SELECT IMP_XML_ELEMENT_ID,
418 IMP_XML_DOCUMENT_ID,
419 ORDER_FINAL
423 CURSOR c_xml_next_sibling_element (p_xml_doc_id NUMBER,
420 FROM AMS_IMP_XML_ELEMENTS
421 WHERE IMP_XML_ELEMENT_ID = p_xml_element_id;
422
424 p_order_final NUMBER) IS
425 SELECT *
426 FROM AMS_IMP_XML_ELEMENTS
427 WHERE IMP_XML_DOCUMENT_ID = p_xml_doc_id
428 AND ORDER_INITIAL = p_order_final + 1;
429
430 l_xml_element_rec c_xml_element%ROWTYPE;
431 BEGIN
432 --
433 -- Initialize API return status to success.
434 --
435 x_return_status := FND_API.G_RET_STS_SUCCESS;
436
437 IF p_imp_xml_element_id IS NULL THEN
438 x_msg_data := 'Expected error in ' || L_FULL_NAME
439 || ' xml element id is null';
440 RAISE FND_API.G_EXC_ERROR;
441 END IF;
442
443 OPEN c_xml_element (p_imp_xml_element_id);
444 FETCH c_xml_element INTO l_xml_element_rec;
445
446 IF c_xml_element%FOUND THEN
447 OPEN c_xml_next_sibling_element (l_xml_element_rec.IMP_XML_DOCUMENT_ID,
448 l_xml_element_rec.ORDER_FINAL);
449 FETCH c_xml_next_sibling_element INTO x_node_rec;
450 CLOSE c_xml_next_sibling_element;
451 END IF;
452 CLOSE c_xml_element;
453
454 EXCEPTION
455 WHEN FND_API.G_EXC_ERROR THEN
456 x_return_status := FND_API.G_RET_STS_ERROR;
457 WHEN OTHERS THEN
458 x_msg_data := 'Unexpected error in '
459 || L_FULL_NAME || ': '|| SQLERRM;
460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
461 END Get_Next_Sibling_Node;
462
463 -- Start of comments
464 -- API Name Get_Parent_Node
465 -- Type Public
466 -- Pre-reqs None.
467 -- Function Retrieve the information for the parent node in the
468 -- "AMS_IMP_XML_ELEMENTS" table, given the node id
469 -- Parameters
470 -- IN
471 -- p_imp_xml_element_id NUMBER Required
472 -- OUT x_node_rec AMS_IMP_XML_ELEMENTS%ROWTYPE
473 -- x_return_status VARCHAR2
474 --
475 -- Version Current version: 1.0
476 -- Previous version: 1.0
477 -- Initial version: 1.0
478 -- End of comments
479 PROCEDURE Get_Parent_Node (
480 p_imp_xml_element_id IN NUMBER,
481 x_node_rec OUT NOCOPY AMS_IMP_XML_ELEMENTS%ROWTYPE,
482 x_return_status OUT NOCOPY VARCHAR2,
483 x_msg_data OUT NOCOPY VARCHAR2
484 )
485 IS
486 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Parent_Node';
487 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
488
489 CURSOR c_xml_element (p_xml_element_id NUMBER) IS
490 SELECT IMP_XML_ELEMENT_ID,
491 IMP_XML_DOCUMENT_ID,
492 ORDER_INITIAL,
493 ORDER_FINAL
494 FROM AMS_IMP_XML_ELEMENTS
495 WHERE IMP_XML_ELEMENT_ID = p_xml_element_id;
496
497 CURSOR c_xml_parent_element (p_xml_doc_id NUMBER,
498 p_order_initial NUMBER,
499 p_order_final NUMBER) IS
500 SELECT *
501 FROM AMS_IMP_XML_ELEMENTS
502 WHERE IMP_XML_DOCUMENT_ID = p_xml_doc_id
503 --AND ORDER_INITIAL < p_order_initial
504 --AND ORDER_FINAL > p_order_final
505 AND ORDER_INITIAL =
506 (SELECT MAX(ORDER_INITIAL)
507 FROM AMS_IMP_XML_ELEMENTS e2
508 WHERE e2.IMP_XML_DOCUMENT_ID = p_xml_doc_id
509 AND e2.ORDER_INITIAL < p_order_initial
510 AND e2.ORDER_FINAL > p_order_final);
511 l_xml_element_rec c_xml_element%ROWTYPE;
512
513 BEGIN
514 --
515 -- Initialize API return status to success.
516 --
517 x_return_status := FND_API.G_RET_STS_SUCCESS;
518
519 IF p_imp_xml_element_id IS NULL THEN
520 x_msg_data := 'Expected error in ' || L_FULL_NAME
521 || ' xml element id is null';
522 RAISE FND_API.G_EXC_ERROR;
523 END IF;
524
525 OPEN c_xml_element (p_imp_xml_element_id);
526 FETCH c_xml_element INTO l_xml_element_rec;
527
528 IF c_xml_element%FOUND THEN
529 OPEN c_xml_parent_element (l_xml_element_rec.IMP_XML_DOCUMENT_ID,
530 l_xml_element_rec.ORDER_INITIAL,
531 l_xml_element_rec.ORDER_FINAL);
532 FETCH c_xml_parent_element INTO x_node_rec;
533 CLOSE c_xml_parent_element;
534 ELSE
535 x_node_rec.IMP_XML_ELEMENT_ID := NULL;
536 END IF;
537 CLOSE c_xml_element;
538
539 EXCEPTION
540 WHEN FND_API.G_EXC_ERROR THEN
541 x_return_status := FND_API.G_RET_STS_ERROR;
542 WHEN OTHERS THEN
543 x_msg_data := 'Unexpected error in '
544 || L_FULL_NAME || ': '|| SQLERRM;
545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
546 END Get_Parent_Node;
547
548 -- Start of comments
549 -- API Name Get_Error_Info
550 -- Type Public
551 -- Pre-reqs None.
552 -- Function Retrieve the tag name and text data for an error node.
553 -- Version Current version: 1.0
554 -- Previous version: 1.0
555 -- Initial version: 1.0
556 -- End of comments
557 PROCEDURE Get_Error_Info (
558 p_imp_xml_element_id IN NUMBER,
559 x_column_name OUT NOCOPY VARCHAR2,
560 x_column_value OUT NOCOPY VARCHAR2,
561 x_return_status OUT NOCOPY VARCHAR2,
562 x_msg_data OUT NOCOPY VARCHAR2
563 )
567 l_parent_node_rec AMS_IMP_XML_ELEMENTS%ROWTYPE;
564 IS
565 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Error_Info';
566 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
568
569 CURSOR c_error_xml_element (p_xml_element_id NUMBER) IS
570 SELECT IMP_XML_ELEMENT_ID,
571 COLUMN_NAME,
572 DATA
573 FROM AMS_IMP_XML_ELEMENTS
574 WHERE IMP_XML_ELEMENT_ID = p_xml_element_id;
575 l_error_info c_error_xml_element%ROWTYPE;
576 l_column_name AMS_IMP_XML_ELEMENTS.COLUMN_NAME%TYPE;
577 l_data AMS_IMP_XML_ELEMENTS.DATA%TYPE;
578 l_element_id NUMBER;
579 BEGIN
580 --
581 -- Initialize API return status to success.
582 --
583 x_return_status := FND_API.G_RET_STS_SUCCESS;
584
585 IF p_imp_xml_element_id IS NULL THEN
586 x_msg_data := 'Expected error in ' || L_FULL_NAME
587 || ' xml element id is null';
588 RAISE FND_API.G_EXC_ERROR;
589 END IF;
590
591 OPEN c_error_xml_element (p_imp_xml_element_id);
592 FETCH c_error_xml_element INTO l_error_info;
593
594 IF c_error_xml_element%FOUND THEN
595 l_element_id := l_error_info.IMP_XML_ELEMENT_ID;
596 l_column_name := '(' || l_error_info.COLUMN_NAME || ')';
597 l_data := l_error_info.DATA;
598 WHILE l_element_id IS NOT NULL
599 LOOP
600 l_parent_node_rec.IMP_XML_ELEMENT_ID := NULL;
601 Get_Parent_Node (
602 p_imp_xml_element_id => l_element_id,
603 x_node_rec => l_parent_node_rec,
604 x_return_status => x_return_status,
605 x_msg_data => x_msg_data
606 );
607
608 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
609 CLOSE c_error_xml_element;
610 RAISE FND_API.G_EXC_ERROR;
611 END IF;
612
613 l_element_id := l_parent_node_rec.IMP_XML_ELEMENT_ID;
614 IF l_element_id IS NOT NULL THEN
615 l_column_name := '(' || l_parent_node_rec.COLUMN_NAME || ').'
616 || l_column_name;
617 END IF;
618 END LOOP;
619 END IF;
620 CLOSE c_error_xml_element;
621
622 x_column_name := l_column_name;
623 x_column_value := l_data;
624
625 EXCEPTION
626 WHEN FND_API.G_EXC_ERROR THEN
627 x_return_status := FND_API.G_RET_STS_ERROR;
628 WHEN OTHERS THEN
629 x_msg_data := 'Unexpected error in '
630 || L_FULL_NAME || ': '|| SQLERRM;
631 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
632
633 END Get_Error_Info;
634
635 -- Start of comments
636 -- API Name Get_Children_Nodes
637 -- Type Public
638 -- Pre-reqs None.
639 -- Function Retrieve the information for all child nodes in the
640 -- "AMS_IMP_XML_ELEMENTS" table, given the node id
641 -- Parameters
642 -- IN
643 -- p_imp_xml_element_id NUMBER Required
644 -- OUT x_node_rec AMS_IMP_XML_ELEMENTS%ROWTYPE
645 -- x_return_status VARCHAR2
646 --
647 -- Version Current version: 1.0
648 -- Previous version: 1.0
649 -- Initial version: 1.0
650 -- End of comments
651 PROCEDURE Get_Children_Nodes (
652 p_imp_xml_element_id IN NUMBER,
653 x_child_ids OUT NOCOPY xml_element_key_set_type,
654 x_return_status OUT NOCOPY VARCHAR2,
655 x_msg_data OUT NOCOPY VARCHAR2
656 )
657 IS
658 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Children_Nodes';
659 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
660
661 --l_child_node_rec AMS_IMP_XML_ELEMENTS%ROWTYPE;
662
663 l_return_status VARCHAR2(1);
664 l_msg_data VARCHAR2(4000);
665 l_child_count NUMBER := 1;
666 l_element_rec AMS_IMP_XML_ELEMENTS%ROWTYPE;
667
668 rc_child_set rc_type;
669 l_child rc_child_set%ROWTYPE;
670 BEGIN
671
672 --
673 -- Initialize API return status to success.
674 --
675 x_return_status := FND_API.G_RET_STS_SUCCESS;
676 l_return_status := FND_API.G_RET_STS_SUCCESS;
677
678 IF p_imp_xml_element_id IS NULL THEN
679 x_msg_data := 'Expected error in ' || L_FULL_NAME
680 || ' xml element id is null';
681 RAISE FND_API.G_EXC_ERROR;
682 END IF;
683
684 Get_Element_Info (
685 p_element_id => p_imp_xml_element_id,
686 x_element_info => l_element_rec);
687
688 IF l_element_rec.imp_xml_document_id IS NULL
689 OR l_element_rec.order_initial IS NULL THEN
690 RAISE FND_API.G_EXC_ERROR;
691 END IF;
692
693 rc_child_set := Get_Children_Cursor (
694 p_imp_doc_id => l_element_rec.imp_xml_document_id,
695 p_order_initial => l_element_rec.order_initial);
696 LOOP
697 FETCH rc_child_set INTO l_child;
698 EXIT WHEN rc_child_set%NOTFOUND;
699 x_child_ids(l_child_count) := l_child.IMP_XML_ELEMENT_ID;
700 l_child_count := l_child_count + 1;
701 END LOOP;
702 CLOSE rc_child_set;
703
704 EXCEPTION
705 WHEN FND_API.G_EXC_ERROR THEN
706 x_return_status := FND_API.G_RET_STS_ERROR;
707 WHEN OTHERS THEN
711
708 x_msg_data := 'Unexpected error in ' || L_FULL_NAME || ': '|| SQLERRM;
709 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
710 END Get_Children_Nodes;
712
713 -- Start of comments
714 -- API Name Get_Children_Nodes
715 -- Type Public
716 -- Pre-reqs None.
717 -- Function Retrieve the information for all child nodes in the
718 -- "AMS_IMP_XML_ELEMENTS" table, given the node id
719 -- Parameters
720 -- IN
721 -- p_imp_xml_element_id NUMBER Required
722 -- OUT x_child_set xml_element_set_type
723 -- x_return_status VARCHAR2
724 --
725 -- Version Current version: 1.0
726 -- Previous version: 1.0
727 -- Initial version: 1.0
728 -- End of comments
729 PROCEDURE Get_Children_Nodes (
730 p_imp_xml_element_id IN NUMBER,
731 x_child_set OUT NOCOPY xml_element_set_type,
732 x_return_status OUT NOCOPY VARCHAR2,
733 x_msg_data OUT NOCOPY VARCHAR2
734 )
735 IS
736 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Children_Nodes';
737 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
738
739 l_return_status VARCHAR2(1);
740 l_msg_data VARCHAR2(4000);
741 l_child_count NUMBER := 1;
742 l_element_rec AMS_IMP_XML_ELEMENTS%ROWTYPE;
743 rc_child_set rc_type;
744 l_child rc_child_set%ROWTYPE;
745 BEGIN
746
747 --
748 -- Initialize API return status to success.
749 --
750 x_return_status := FND_API.G_RET_STS_SUCCESS;
751 l_return_status := FND_API.G_RET_STS_SUCCESS;
752
753 IF p_imp_xml_element_id IS NULL THEN
754 x_msg_data := 'Expected error in ' || L_FULL_NAME
755 || ' xml element id is null';
756 RAISE FND_API.G_EXC_ERROR;
757 END IF;
758
759 Get_Element_Info (
760 p_element_id => p_imp_xml_element_id,
761 x_element_info => l_element_rec);
762
763 IF l_element_rec.imp_xml_document_id IS NULL
764 OR l_element_rec.order_initial IS NULL THEN
765 RAISE FND_API.G_EXC_ERROR;
766 END IF;
767
768 rc_child_set := Get_Children_Cursor (
769 p_imp_doc_id => l_element_rec.imp_xml_document_id,
770 p_order_initial => l_element_rec.order_initial);
771 LOOP
772 FETCH rc_child_set INTO l_child;
773 EXIT WHEN rc_child_set%NOTFOUND;
774 x_child_set(l_child_count) := l_child;
775 l_child_count := l_child_count + 1;
776 END LOOP;
777 CLOSE rc_child_set;
778
779 EXCEPTION
780 WHEN FND_API.G_EXC_ERROR THEN
781 x_return_status := FND_API.G_RET_STS_ERROR;
782 WHEN OTHERS THEN
783 x_msg_data := 'Unexpected error in '
784 || L_FULL_NAME || ': '|| SQLERRM;
785 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
786 END Get_Children_Nodes;
787
788 -- Start of comments
789 -- API Name Get_Children_Nodes
790 -- Type Public
791 -- Pre-reqs None.
792 -- Function Retrieve the information for all child nodes in the
793 -- "AMS_IMP_XML_ELEMENTS" table, given the node id
794 -- Parameters
795 -- IN
796 -- p_imp_xml_element_id NUMBER Required
797 -- OUT x_child_set xml_element_set_type
798 -- x_return_status VARCHAR2
799 --
800 -- Version Current version: 1.0
801 -- Previous version: 1.0
802 -- Initial version: 1.0
803 -- End of comments
804 PROCEDURE Get_Children_Nodes (
805 p_imp_xml_element_id IN NUMBER,
806 x_rc_child_set OUT NOCOPY rc_type,
807 x_return_status OUT NOCOPY VARCHAR2,
808 x_msg_data OUT NOCOPY VARCHAR2
809 )
810 IS
811 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Children_Nodes';
812 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
813
814 l_return_status VARCHAR2(1);
815 l_msg_data VARCHAR2(4000);
816 l_child_count NUMBER := 1;
817 l_element_rec AMS_IMP_XML_ELEMENTS%ROWTYPE;
818
819 BEGIN
820
821 --
822 -- Initialize API return status to success.
823 --
824 x_return_status := FND_API.G_RET_STS_SUCCESS;
825 l_return_status := FND_API.G_RET_STS_SUCCESS;
826
827 IF p_imp_xml_element_id IS NULL THEN
828 x_msg_data := 'Expected error in ' || L_FULL_NAME
829 || ' xml element id is null';
830 RAISE FND_API.G_EXC_ERROR;
831 END IF;
832
833 Get_Element_Info (
834 p_element_id => p_imp_xml_element_id,
835 x_element_info => l_element_rec);
836
837 IF l_element_rec.imp_xml_document_id IS NULL
838 OR l_element_rec.order_initial IS NULL THEN
839 RAISE FND_API.G_EXC_ERROR;
840 END IF;
841
842 x_rc_child_set := Get_Children_Cursor (
843 p_imp_doc_id => l_element_rec.imp_xml_document_id,
844 p_order_initial => l_element_rec.order_initial);
845
846 EXCEPTION
847 WHEN FND_API.G_EXC_ERROR THEN
848 x_return_status := FND_API.G_RET_STS_ERROR;
849 WHEN OTHERS THEN
850 x_msg_data := 'Unexpected error in '
851 || L_FULL_NAME || ': '|| SQLERRM;
855 -- Start of comments
852 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
853 END Get_Children_Nodes;
854
856 -- API Name Filter_XML
857 -- Type Public
858 -- Pre-reqs None.
859 -- Function Filter out the leaf nodes of an xml doc if they are not in mapping
860 -- Parameters
861 -- IN
862 -- p_import_list_header_id NUMBER Required
863 -- OUT x_return_status VARCHAR2
864 -- x_msg_data VARCHAR2
865 --
866 -- Version Current version: 1.0
867 -- Previous version: 1.0
868 -- Initial version: 1.0
869 -- End of comments
870 PROCEDURE Filter_XML (
871 p_import_list_header_id IN NUMBER,
872 x_return_status OUT NOCOPY VARCHAR2,
873 x_msg_data OUT NOCOPY VARCHAR2,
874 x_result_xml IN OUT NOCOPY CLOB,
875 x_doc_id OUT NOCOPY NUMBER
876 )
877 IS
878 L_API_NAME CONSTANT VARCHAR2(30) := 'Filter_XML';
879 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
880
881 l_return_status VARCHAR2(1);
882 l_msg_data VARCHAR2(4000);
883 l_mapping_source_fields xml_source_column_set_type;
884 l_xml_doc_clob CLOB;
885 l_xml_doc_result_clob CLOB;
886
887 l_parser xmlparser.Parser;
888 l_xml_doc xmldom.DOMDocument;
889 l_dom_root xmldom.DOMNode;
890 l_dom_actual_root xmldom.DOMNode;
891
892 CURSOR c_import_source_fields (p_import_list_header_id NUMBER) IS
893 SELECT A.SOURCE_COLUMN_NAME
894 FROM ams_list_src_fields A, ams_imp_list_headers_all b
895 WHERE b.IMPORT_LIST_HEADER_ID = p_import_list_header_id
896 AND b.LIST_SOURCE_TYPE_ID = A.LIST_SOURCE_TYPE_ID
897 ORDER BY LIST_SOURCE_FIELD_ID;
898
899 CURSOR c_xml_doc_content (p_import_list_header_id NUMBER) IS
900 SELECT CONTENT_TEXT, IMP_DOCUMENT_ID
901 FROM AMS_IMP_DOCUMENTS
902 WHERE IMPORT_LIST_HEADER_ID = p_import_list_header_id;
903
904 XMLParseError EXCEPTION;
905 PRAGMA EXCEPTION_INIT (XMLParseError, -20100);
906 p_buffer VARCHAR2(4000);
907 l_result_xml_content CLOB;
908 l_actual_root_name VARCHAR2 (2000);
909
910 BEGIN
911
912 --
913 -- Initialize API return status to success.
914 --
915 x_return_status := FND_API.G_RET_STS_SUCCESS;
916 l_return_status := FND_API.G_RET_STS_SUCCESS;
917
918 IF p_import_list_header_id IS NULL THEN
919 x_msg_data := 'Expected error in ' || L_FULL_NAME
920 || ' list header id is null';
921 RAISE FND_API.G_EXC_ERROR;
922 END IF;
923
924 OPEN c_import_source_fields (p_import_list_header_id);
925 FETCH c_import_source_fields BULK COLLECT INTO
926 l_mapping_source_fields;
927 CLOSE c_import_source_fields;
928
929 --l_mapping_source_fields(1) := '(PRODUCT).(PRICING).(PRICE).(PRICE_LIST_NAME)';
930 --l_mapping_source_fields(2) := '(PRODUCT).(PRICING).(PRICE).(CURRENCY)';
931 --l_mapping_source_fields(3) := '(PRODUCT).(PRICING).(PRICE).(AMOUNT)';
932 --l_mapping_source_fields(4) := '(PRODUCT).(CATEGORY).(CAT).(CATEGORY_SET)';
933 --l_mapping_source_fields(5) := '(PRODUCT).(CATEGORY).(CAT).(CATEGORY_CODE)';
934
935 /*********************** END ************************************/
936 IF l_mapping_source_fields.COUNT > 1 THEN
937 OPEN c_xml_doc_content (p_import_list_header_id);
938 FETCH c_xml_doc_content INTO l_xml_doc_clob, x_doc_id;
939 IF c_xml_doc_content%FOUND THEN
940 l_parser := xmlparser.newParser;
941 xmlparser.parseClob (l_parser, l_xml_doc_clob);
942 l_xml_doc := xmlparser.getDocument(l_parser);
943
944 -- virtual root
945 l_dom_root := xmldom.makeNode(l_xml_doc);
946
947 --actual root
948 l_dom_actual_root := xmldom.item (xmldom.getChildNodes(l_dom_root), 1);
949
950 l_actual_root_name := xmldom.getNodeName(l_dom_actual_root);
951
952 Filter_XML_Helper (
953 p_node => l_dom_actual_root,
954 p_mapping => l_mapping_source_fields,
955 p_source_col_name => '(' || l_actual_root_name || ')'
956 );
957
958 SELECT FILTER_CONTENT_TEXT INTO l_result_xml_content
959 FROM AMS_IMP_DOCUMENTS
960 WHERE IMPORT_LIST_HEADER_ID = p_import_list_header_id
961 FOR UPDATE;
962
963 xmldom.writeToClob(l_dom_actual_root, l_result_xml_content);
964 --commit;
965 xmlparser.freeParser (l_parser);
966 END IF;
967 CLOSE c_xml_doc_content;
968 END IF;
969
970 EXCEPTION
971 WHEN XMLParseError THEN
972 xmlparser.freeParser (l_parser);
973 x_return_status := FND_API.G_RET_STS_ERROR;
974 x_msg_data := x_msg_data || 'XML parse error in ' || L_FULL_NAME || ': '|| SQLERRM;
975 WHEN FND_API.G_EXC_ERROR THEN
976 x_return_status := FND_API.G_RET_STS_ERROR;
977 WHEN OTHERS THEN
978 x_msg_data := x_msg_data || 'Unexpected error in '
979 || L_FULL_NAME || ': '|| SQLERRM;
980 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
981 END Filter_XML;
982
983 PROCEDURE Filter_XML_Helper (
984 p_node IN OUT NOCOPY xmldom.DOMNode,
985 p_mapping IN xml_source_column_set_type,
986 p_source_col_name IN VARCHAR2
987 )
988 IS
989 l_child_list xmldom.DOMNodeList;
993 l_cur_source_col_name VARCHAR2(2000);
990 l_num_children NUMBER;
991 l_child_node xmldom.DOMNode;
992 l_grand_child_node xmldom.DOMNode;
994 l_num_child_offset NUMBER;
995
996 l_temp VARCHAR2(4000);
997 l_temp1 VARCHAR2(4000);
998 BEGIN
999 l_child_list := xmldom.getChildNodes(p_node);
1000 l_num_children := xmldom.getLength (l_child_list);
1001
1002 FOR i IN 0 .. l_num_children -1
1003 LOOP
1004
1005 l_child_node := xmldom.item(l_child_list, i);
1006
1007 IF p_source_col_name IS NULL THEN
1008 l_cur_source_col_name := '(' || xmldom.getNodeName(l_child_node) || ')';
1009 ELSE
1010 l_cur_source_col_name := p_source_col_name || '.(' || xmldom.getNodeName(l_child_node) || ')';
1011 END IF;
1012
1013 l_grand_child_node := xmldom.getFirstChild(l_child_node);
1014 IF xmldom.hasChildNodes(l_child_node)
1015 AND ( xmldom.getLength (xmldom.getChildNodes(l_child_node)) <> 1
1016 OR UPPER(RTRIM(LTRIM(xmldom.getNodeName (l_grand_child_node)))) <> '#TEXT')
1017 THEN
1018
1019 Filter_XML_Helper (
1020 p_node => l_child_node,
1021 p_mapping => p_mapping,
1022 p_source_col_name => l_cur_source_col_name
1023 );
1024 ELSE
1025 -- one grand child
1026 IF xmldom.getLength (xmldom.getChildNodes(l_child_node)) = 1
1027 -- grand child is leaf
1028 AND UPPER(RTRIM(LTRIM(xmldom.getNodeName (l_grand_child_node)))) = '#TEXT'
1029 AND NOT Is_In_Mapping ( --leaf not in mapping
1030 p_item => l_cur_source_col_name,
1031 p_mapping => p_mapping
1032 ) THEN
1033 p_node := xmldom.removeChild(l_child_node, l_grand_child_node);
1034 END IF;
1035 END IF;
1036
1037 END LOOP;
1038
1039 EXCEPTION
1040 WHEN OTHERS THEN
1041 l_temp1 := 'Unexpected error in '
1042 || ': '|| SQLERRM;
1043 END Filter_XML_Helper;
1044
1045 FUNCTION Is_In_Mapping (
1046 p_item IN VARCHAR2,
1047 p_mapping IN xml_source_column_set_type
1048 ) RETURN BOOLEAN
1049 IS
1050 BEGIN
1051 FOR i IN p_mapping.FIRST .. p_mapping.LAST
1052 LOOP
1053 IF p_mapping (i) = p_item THEN
1054 RETURN TRUE;
1055 END IF;
1056 END LOOP;
1057 RETURN FALSE;
1058 END Is_In_Mapping;
1059
1060 -- Start of comments
1061 -- API Name Store_XML_Elements
1062 -- Type Private
1063 -- Pre-reqs None.
1064 -- Function Takes an XML as a CLOB, parses and stores it into the "AMS_IMP_XML_ELEMENTS" table
1065 -- and "AMS_IMP_XML_ATTRIBUTES" table.
1066 -- Parameters
1067 -- IN
1068 -- p_xml_content CLOB Required
1069 -- OUT x_return_status VARCHAR2
1070 -- x_msg_data VARCHAR2
1071 --
1072 -- Version Current version: 1.0
1073 -- Previous version: 1.0
1074 -- Initial version: 1.0
1075 -- End of comments
1076 PROCEDURE Store_XML_Elements (
1077 p_xml_doc_id IN NUMBER,
1078 p_imp_list_header_id IN NUMBER,
1079 p_xml_content IN CLOB,
1080 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1081 x_return_status OUT NOCOPY VARCHAR2,
1082 x_msg_data OUT NOCOPY VARCHAR2
1083 )
1084 IS
1085 L_API_NAME CONSTANT VARCHAR2(30) := 'Store_XML_Elements';
1086 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
1087
1088 l_return_status VARCHAR2(1);
1089 l_msg_data VARCHAR2(4000);
1090 l_order_num NUMBER;
1091 l_mapping_source_fields xml_source_column_set_type;
1092 l_mapping_target_fields xml_target_column_set_type;
1093
1094 l_parser xmlparser.Parser;
1095 l_xml_doc xmldom.DOMDocument;
1096 l_dom_root xmldom.DOMNode;
1097 l_dom_actual_root xmldom.DOMNode;
1098
1099 CURSOR c_import_mapping_fields (p_import_list_header_id NUMBER) IS
1100 SELECT A.SOURCE_COLUMN_NAME, A.FIELD_COLUMN_NAME
1101 FROM ams_list_src_fields A, ams_imp_list_headers_all b
1102 WHERE b.IMPORT_LIST_HEADER_ID = p_import_list_header_id
1103 AND b.LIST_SOURCE_TYPE_ID = A.LIST_SOURCE_TYPE_ID
1104 ORDER BY LIST_SOURCE_FIELD_ID;
1105
1106 CURSOR c_xml_content (p_import_list_header_id NUMBER) IS
1107 SELECT CONTENT_TEXT
1108 FROM AMS_IMP_DOCUMENTS
1109 WHERE IMPORT_LIST_HEADER_ID = p_import_list_header_id;
1110
1111 XMLParseError EXCEPTION;
1112 PRAGMA EXCEPTION_INIT (XMLParseError, -20100);
1113 p_buffer VARCHAR2(4000);
1114 l_xml_content CLOB;
1115 l_temp VARCHAR2(4000);
1116
1117 l_result_xml_content CLOB;
1118 l_result_xml_node xmldom.DOMNode;
1119 l_col VARCHAR2(2000);
1120 l_value VARCHAR2(2000);
1121
1122 --l_clone_node xmldom.DOMNode;
1123 l_temp_doc xmldom.DOMDocument;
1124 l_temp_element xmldom.DOMElement;
1125
1126 l_actual_root_name VARCHAR2(2000);
1127
1128 BEGIN
1129
1130 --
1131 -- Initialize API return status to success.
1132 --
1133 x_return_status := FND_API.G_RET_STS_SUCCESS;
1134 l_return_status := FND_API.G_RET_STS_SUCCESS;
1135
1136 OPEN c_xml_content (p_imp_list_header_id);
1137 FETCH c_xml_content INTO l_xml_content;
1138 CLOSE c_xml_content;
1142 OPEN c_import_mapping_fields (p_imp_list_header_id);
1139
1140 IF l_xml_content IS NOT NULL THEN
1141
1143 FETCH c_import_mapping_fields BULK COLLECT INTO
1144 l_mapping_source_fields, l_mapping_target_fields;
1145 CLOSE c_import_mapping_fields;
1146 l_parser := xmlparser.newParser;
1147
1148 xmlparser.parseClob (l_parser, l_xml_content);
1149
1150 l_xml_doc := xmlparser.getDocument(l_parser);
1151
1152 -- virtual root
1153 l_dom_root := xmldom.makeNode(l_xml_doc);
1154
1155 --l_temp := ' Store_XML_Elements 05-01:' || xmldom.getNodeName (l_dom_root);
1156
1157 --actual root
1158 IF xmldom.getLength (xmldom.getChildNodes(l_dom_root)) = 1 THEN
1159 l_dom_actual_root := xmldom.item (xmldom.getChildNodes(l_dom_root), 0);
1160 ELSE
1161 l_dom_actual_root := xmldom.item (xmldom.getChildNodes(l_dom_root), 1);
1162 END IF;
1163
1164 --l_temp := ' Store_XML_Elements 06:' || xmldom.getNodeName (l_dom_actual_root);
1165 l_order_num := G_ORDER_INITIAL_START_NUMBER;
1166
1167 l_temp_doc := xmldom.newDOMDocument;
1168
1169 l_actual_root_name := xmldom.getNodeName (l_dom_actual_root);
1170 l_temp_element := xmldom.createElement(l_temp_doc, l_actual_root_name);
1171 l_result_xml_node := xmldom.makeNode(l_temp_element);
1172
1173 Store_XML_Elements_Helper (
1174 p_node => l_dom_actual_root,
1175 p_source_fields => l_mapping_source_fields,
1176 p_target_fields => l_mapping_target_fields,
1177 p_col_name => NULL,
1178 p_xml_doc_id => p_xml_doc_id,
1179 p_commit => p_commit,
1180 x_order_num => l_order_num,
1181 p_result_node => l_result_xml_node,
1182 p_column_name => l_col,
1183 p_value => l_value,
1184 p_result_doc => l_temp_doc
1185 );
1186
1187 SELECT FILTER_CONTENT_TEXT INTO l_result_xml_content
1188 FROM AMS_IMP_DOCUMENTS
1189 WHERE IMPORT_LIST_HEADER_ID = p_imp_list_header_id
1190 FOR UPDATE;
1191
1192 xmldom.writeToClob (l_result_xml_node, l_result_xml_content);
1193 --commit;
1194 IF FND_API.to_boolean(p_commit) THEN
1195 COMMIT;
1196 END IF;
1197 xmldom.freeDocument(l_temp_doc);
1198 ELSE
1199 x_return_status := FND_API.G_RET_STS_ERROR;
1200 x_msg_data := 'No XML data found';
1201 RAISE FND_API.G_EXC_ERROR;
1202 END IF;
1203
1204 EXCEPTION
1205 WHEN XMLParseError THEN
1206 xmlparser.freeParser (l_parser);
1207 x_return_status := FND_API.G_RET_STS_ERROR;
1208 x_msg_data := x_msg_data || 'XML parse error in ' || L_FULL_NAME || ': '|| SQLERRM;
1209
1210 WHEN FND_API.G_EXC_ERROR THEN
1211 x_return_status := FND_API.G_RET_STS_ERROR;
1212 WHEN OTHERS THEN
1213 x_msg_data := x_msg_data || 'Unexpected error in '
1214 || L_FULL_NAME || ': '|| SQLERRM;
1215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1216 END Store_XML_Elements;
1217
1218 PROCEDURE Store_XML_Elements_Helper (
1219 p_node IN xmldom.DOMNode,
1220 p_source_fields IN xml_source_column_set_type,
1221 p_target_fields IN xml_target_column_set_type,
1222 p_col_name IN VARCHAR2,
1223 p_xml_doc_id IN NUMBER,
1224 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1225 x_order_num IN OUT NOCOPY NUMBER,
1226 p_result_node IN OUT NOCOPY xmldom.DOMNode,
1227 p_column_name IN OUT NOCOPY VARCHAR2,
1228 p_value IN OUT NOCOPY VARCHAR2,
1229 p_result_doc IN OUT NOCOPY xmldom.DOMDocument
1230 )
1231 IS
1232 l_xml_element_rec AMS_IMP_XML_ELEMENTS%ROWTYPE;
1233 l_col_name VARCHAR2(2000);
1234 l_cur_col_name VARCHAR2(2000);
1235 l_child_list xmldom.DOMNodeList;
1236 l_num_children NUMBER;
1237 l_child_node xmldom.DOMNode;
1238 l_now DATE := SYSDATE;
1239 l_in_mapping_flag BOOLEAN := FALSE;
1240 l_curr_element xmldom.DOMElement;
1241 l_col VARCHAR2(2000);
1242 l_value VARCHAR2(2000);
1243 l_child_element xmldom.DOMElement;
1244 l_child_dom_node xmldom.DOMNode;
1245 --l_dom_doc xmldom.DOMDocument := xmldom.makeDocument (p_node); --commented for bug4961953
1246
1247 l_dummy_node xmldom.DOMNode;
1248
1249 l_msg VARCHAR2(2000);
1250 l_temp VARCHAR2(2000);
1251 l_temp1 VARCHAR2(2000);
1252 --l_clone_node xmldom.DOMNode;
1253 l_child_doc xmldom.DOMDocument;
1254
1255 l_text_data xmldom.DOMText;
1256 --createTextNode(doc DOMDocument, data IN VARCHAR2) RETURN DOMText
1257 BEGIN
1258
1259 --l_temp := ' TEST01::';
1260 l_xml_element_rec.DATA_TYPE := G_DATA_TYPE_TAG;
1261 l_xml_element_rec.IMP_XML_DOCUMENT_ID := p_xml_doc_id;
1262 l_xml_element_rec.ORDER_INITIAL := x_order_num;
1263 IF p_col_name IS NULL THEN
1264 l_cur_col_name := '(' || xmldom.getNodeName (p_node) || ')';
1265 ELSE
1266 l_cur_col_name := p_col_name || '.(' || xmldom.getNodeName (p_node) || ')';
1267 END IF;
1268
1269 --l_temp := l_temp || ' TEST02::';
1270 l_col_name := Get_Col_name (
1271 p_source_fields => p_source_fields,
1272 p_target_fields => p_target_fields,
1273 p_col_name => l_cur_col_name
1274 );
1275 --l_temp := l_temp || ' l_col_name::' || l_col_name;
1276 IF l_col_name IS NULL THEN --not in mapping
1280 l_xml_element_rec.COLUMN_NAME := l_col_name;
1277 l_xml_element_rec.COLUMN_NAME := xmldom.getNodeName (p_node);
1278 ELSE -- in mapping
1279 l_in_mapping_flag := TRUE;
1281 END IF;
1282
1283 --l_temp := l_temp || ' l_xml_element_rec.COLUMN_NAME::'
1284 -- || l_xml_element_rec.COLUMN_NAME;
1285
1286 l_child_list := xmldom.getChildNodes(p_node);
1287 l_num_children := xmldom.getLength (l_child_list);
1288
1289 IF l_num_children <> 1
1290 OR UPPER(RTRIM(LTRIM(xmldom.getNodeName (xmldom.item(l_child_list, 0))))) <> '#TEXT' THEN
1291 FOR i IN 0 .. l_num_children -1
1292 LOOP
1293 l_child_node := xmldom.item(l_child_list, i);
1294 x_order_num := x_order_num + 1;
1295
1296 l_col := l_cur_col_name || '.(' || xmldom.getNodeName (l_child_node) || ')';
1297
1298 l_col := Get_Col_name (
1299 p_source_fields => p_source_fields,
1300 p_target_fields => p_target_fields,
1301 p_col_name => l_col
1302 );
1303 IF l_col IS NULL THEN
1304 l_col := xmldom.getNodeName (l_child_node);
1305 END IF;
1306
1307 --l_clone_node := xmldom.cloneNode (l_child_node, FALSE);
1308 --l_child_doc := xmldom.newDOMDocument;
1309 l_child_element := xmldom.createElement(p_result_doc, l_col);
1310
1311 --l_temp := xmldom.getTagName (l_child_element);
1312
1313 l_child_dom_node := xmldom.makeNode(l_child_element);
1314
1315 --l_temp := xmldom.getNodeName(l_child_dom_node);
1316 --l_temp := 'hui 30::::name::' || l_temp;
1317
1318
1319 Store_XML_Elements_Helper (
1320 p_node => l_child_node,
1321 p_source_fields => p_source_fields,
1322 p_target_fields => p_target_fields,
1323 p_col_name => l_cur_col_name,
1324 p_xml_doc_id => p_xml_doc_id,
1325 x_order_num => x_order_num,
1326 p_result_node => l_child_dom_node,
1327 p_column_name => l_col,
1328 p_value => l_value,
1329 p_result_doc => p_result_doc
1330 );
1331
1332 IF l_value IS NOT NULL THEN
1333 l_text_data := xmldom.createTextNode(p_result_doc, l_value);
1334 l_dummy_node := xmldom.appendChild (l_child_dom_node, xmldom.makeNode(l_text_data));
1335 END IF;
1336
1337 --l_dummy_node := xmldom.appendChild (l_child_dom_node, xmldom.makeNode(l_text_data));
1338
1339
1340 l_dummy_node := xmldom.appendChild (p_result_node, l_child_dom_node);
1341
1342 --xmldom.freeDocument (l_child_doc);
1343
1344 END LOOP;
1345 END IF;
1346 x_order_num := x_order_num + 1;
1347 l_xml_element_rec.ORDER_FINAL := x_order_num;
1348
1349 IF l_num_children = 1
1350 AND UPPER(RTRIM(LTRIM(xmldom.getNodeName (xmldom.item(l_child_list, 0))))) = '#TEXT' THEN
1351 l_xml_element_rec.DATA_TYPE := G_DATA_TYPE_DATA;
1352 IF l_in_mapping_flag THEN
1353 l_xml_element_rec.DATA := RTRIM(LTRIM(xmldom.getNodeValue(xmldom.item(l_child_list, 0))));
1354 ELSE
1355 l_xml_element_rec.DATA := '';
1356 END IF;
1357 ELSIF l_num_children = 0 THEN
1358 l_xml_element_rec.DATA_TYPE := G_DATA_TYPE_DATA;
1359 ELSE
1360 l_xml_element_rec.DATA_TYPE := G_DATA_TYPE_TAG;
1361 END IF;
1362
1363 /****
1364 IF xmldom.getNodeType (p_node) = xmldom.TEXT_NODE THEN
1365 l_xml_element_rec.DATA_TYPE := G_DATA_TYPE_DATA;
1366 ELSE
1367 l_xml_element_rec.DATA_TYPE := G_DATA_TYPE_TAG;
1368 END IF;
1369 ****/
1370
1371 l_xml_element_rec.NUM_ATTR := Store_XML_Attributes (
1372 p_node => p_node,
1373 p_order_init => l_xml_element_rec.ORDER_INITIAL,
1374 p_xml_doc_id => l_xml_element_rec.IMP_XML_DOCUMENT_ID,
1375 p_commit => p_commit);
1376
1377 INSERT INTO AMS_IMP_XML_ELEMENTS (
1378 IMP_XML_ELEMENT_ID,
1379 LAST_UPDATED_BY,
1380 OBJECT_VERSION_NUMBER,
1381 CREATED_BY,
1382 LAST_UPDATE_LOGIN,
1383 LAST_UPDATE_DATE,
1384 CREATION_DATE,
1385 IMP_XML_DOCUMENT_ID,
1386 ORDER_INITIAL,
1387 ORDER_FINAL,
1388 COLUMN_NAME,
1389 DATA,
1390 NUM_ATTR,
1391 LOAD_STATUS,
1392 DATA_TYPE)
1393 VALUES (
1394 AMS_IMP_XML_ELEMENTS_S.NEXTVAL,
1395 FND_GLOBAL.User_ID,
1396 1.0,
1397 FND_GLOBAL.User_ID,
1398 FND_GLOBAL.Conc_Login_ID,
1399 l_now,
1400 l_now,
1401 l_xml_element_rec.IMP_XML_DOCUMENT_ID,
1402 l_xml_element_rec.ORDER_INITIAL,
1403 l_xml_element_rec.ORDER_FINAL,
1404 l_xml_element_rec.COLUMN_NAME,
1405 l_xml_element_rec.DATA,
1406 l_xml_element_rec.NUM_ATTR,
1407 'ACTIVE',
1408 l_xml_element_rec.DATA_TYPE);
1409
1410 p_column_name := l_xml_element_rec.COLUMN_NAME;
1411 p_value := l_xml_element_rec.DATA;
1412
1413 --COMMIT WORK;
1414 --
1415 -- Standard check for commit request.
1416 --
1417 IF FND_API.To_Boolean(p_commit) THEN
1418 COMMIT WORK;
1419 END IF;
1420 EXCEPTION
1421
1422 WHEN OTHERS THEN
1423 RAISE;
1424 END Store_XML_Elements_Helper;
1425
1426 FUNCTION Store_XML_Attributes (
1427 p_node IN xmldom.DOMNode,
1431 ) RETURN NUMBER
1428 p_order_init IN NUMBER,
1429 p_xml_doc_id IN NUMBER,
1430 p_commit IN VARCHAR2 := FND_API.G_FALSE
1432 IS
1433 l_xml_attribute_rec AMS_IMP_XML_ATTRIBUTES%ROWTYPE;
1434 l_num_attr NUMBER := 0;
1435 l_att_seq NUMBER := 1;
1436 --l_element xmldom.DOMElement;
1437 l_attr_map xmldom.DOMNamedNodeMap;
1438 l_node xmldom.DOMNode;
1439 l_now DATE := SYSDATE;
1440
1441 BEGIN
1442 l_attr_map := xmldom.getAttributes (p_node);
1443 IF NOT xmldom.isNull(l_attr_map) THEN
1444 l_num_attr := xmldom.getLength(l_attr_map);
1445 FOR i IN 0 .. l_num_attr - 1
1446 LOOP
1447 l_node := xmldom.item (l_attr_map, i);
1448 l_xml_attribute_rec.ATT_NAME := xmldom.getNodeName (l_node);
1449 l_xml_attribute_rec.ATT_VALUE := xmldom.getNodeValue (l_node);
1450 l_xml_attribute_rec.ATT_SEQ := i + 1;
1451
1452 INSERT INTO AMS_IMP_XML_ATTRIBUTES (
1453 IMP_XML_ATTRIBUTE_ID,
1454 LAST_UPDATED_BY,
1455 OBJECT_VERSION_NUMBER,
1456 CREATED_BY,
1457 LAST_UPDATE_LOGIN,
1458 LAST_UPDATE_DATE,
1459 CREATION_DATE,
1460 --IMP_DOCUMENT_ID,
1461 IMP_XML_DOCUMENT_ID,
1462 ORDER_INITIAL,
1463 ATT_SEQ,
1464 ATT_NAME,
1465 ATT_VALUE)
1466 VALUES (
1467 AMS_IMP_XML_ATTRIBUTES_S.NEXTVAL,
1468 FND_GLOBAL.User_ID,
1469 1.0,
1470 FND_GLOBAL.User_ID,
1471 FND_GLOBAL.Conc_Login_ID,
1472 l_now,
1473 l_now,
1474 p_xml_doc_id,
1475 p_order_init,
1476 l_xml_attribute_rec.ATT_SEQ,
1477 l_xml_attribute_rec.ATT_NAME,
1478 l_xml_attribute_rec.ATT_VALUE);
1479 END LOOP;
1480 END IF;
1481
1482 --
1483 -- Standard check for commit request.
1484 --
1485 IF FND_API.To_Boolean (p_commit) THEN
1486 COMMIT WORK;
1487 END IF;
1488 RETURN l_num_attr;
1489 END Store_XML_Attributes;
1490
1491 FUNCTION Get_Col_name (
1492 p_source_fields IN xml_source_column_set_type,
1493 p_target_fields IN xml_target_column_set_type,
1494 p_col_name IN VARCHAR2
1495 ) RETURN VARCHAR2
1496 IS
1497 l_seq_num NUMBER := 1;
1498 BEGIN
1499 FOR i IN p_source_fields.FIRST .. p_source_fields.LAST
1500 LOOP
1501 IF p_source_fields(i) = p_col_name THEN
1502 IF INSTR (UPPER(LTRIM(p_target_fields(i))), 'AMS_COL') = 1 THEN
1503 RETURN NULL;
1504 ELSE
1505 RETURN p_target_fields(i);
1506 END IF;
1507 END IF;
1508 END LOOP;
1509 RETURN NULL;
1510 END Get_Col_name;
1511
1512 -- Start of comments
1513 -- API Name Store_XML_Util
1514 -- Type Public
1515 -- Pre-reqs None.
1516 -- Function Takes the list import header id, filter and populate xml into the xml element
1517 -- xml attribute tables.
1518 -- Parameters
1519 -- IN
1520 -- p_import_list_header_id IN NUMBER,
1521 -- p_ownerId IN NUMBER,
1522 -- p_generateList_flag IN VARCHAR2,
1523 -- p_list_name IN VARCHAR2,
1524 -- p_import_flag IN VARCHAR2,
1525 -- p_status_code IN VARCHAR2
1526 -- OUT Retcode VARCHAR2
1527 -- Errbuf VARCHAR2
1528 --
1529 -- Version Current version: 1.0
1530 -- Previous version: 1.0
1531 -- Initial version: 1.0
1532 -- End of comments
1533 PROCEDURE Store_XML_Util (
1534 Errbuf OUT NOCOPY VARCHAR2,
1535 Retcode OUT NOCOPY VARCHAR2,
1536 p_import_list_header_id IN NUMBER,
1537 p_ownerId IN NUMBER,
1538 p_generateList_flag IN VARCHAR2,
1539 p_list_name IN VARCHAR2,
1540 p_import_flag IN VARCHAR2,
1541 p_status_code IN VARCHAR2
1542 )
1543 IS
1544 L_API_NAME CONSTANT VARCHAR2(30) := 'Store_XML_Util';
1545 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME ||'.'|| L_API_NAME;
1546 l_xml_doc_content CLOB;
1547 l_doc_id NUMBER;
1548
1549 CURSOR c_doc_id (p_import_list_header_id NUMBER) IS
1550 SELECT IMP_DOCUMENT_ID
1551 FROM AMS_IMP_DOCUMENTS
1552 WHERE IMPORT_LIST_HEADER_ID = p_import_list_header_id;
1553
1554 CURSOR c_status_id
1555 IS SELECT user_status_id
1556 FROM ams_user_statuses_vl
1557 WHERE system_status_type = 'AMS_IMPORT_STATUS'
1558 AND system_status_code = 'STAGED'
1559 AND default_flag = 'Y';
1560
1561 l_user_status_id NUMBER;
1562 BEGIN
1563 Retcode := FND_API.G_RET_STS_SUCCESS;
1564 SAVEPOINT Store_XML_Util;
1565
1566 IF UPPER(p_status_code) = 'NEW' THEN
1567 --Filter_XML (
1568 -- p_import_list_header_id => p_import_list_header_id,
1569 -- x_return_status => Retcode,
1570 --x_msg_data => Errbuf,
1571 -- x_result_xml => l_xml_doc_content,
1572 -- x_doc_id => l_doc_id
1573 --);
1574
1575 --IF Retcode <> FND_API.G_RET_STS_SUCCESS THEN
1576 -- RAISE FND_API.G_EXC_ERROR;
1577 --END IF;
1578
1579 OPEN c_doc_id (p_import_list_header_id);
1580 FETCH c_doc_id INTO l_doc_id;
1584 Errbuf := 'Can not find document id:';
1581 CLOSE c_doc_id;
1582
1583 IF l_doc_id IS NULL THEN
1585 RAISE FND_API.G_EXC_ERROR;
1586 END IF;
1587
1588 Store_XML_Elements (
1589 p_xml_doc_id => l_doc_id,
1590 p_imp_list_header_id => p_import_list_header_id,
1591 p_xml_content => l_xml_doc_content,
1592 p_commit => FND_API.G_TRUE,
1593 x_return_status => Retcode,
1594 x_msg_data => Errbuf
1595 );
1596
1597 IF Retcode <> FND_API.G_RET_STS_SUCCESS THEN
1598 RAISE FND_API.G_EXC_ERROR;
1599 END IF;
1600
1601 OPEN c_status_id;
1602 FETCH c_status_id INTO l_user_status_id;
1603 CLOSE c_status_id;
1604
1605 UPDATE AMS_IMP_LIST_HEADERS_ALL
1606 SET STATUS_CODE = 'STAGED', USER_STATUS_ID = l_user_status_id
1607 WHERE IMPORT_LIST_HEADER_ID = p_import_list_header_id;
1608 END IF;
1609
1610 IF UPPER (p_import_flag) = 'Y' THEN
1611
1612 AMS_ListImport_PVT.client_load(
1613 p_import_list_header_id => p_import_list_header_id,
1614 p_owner_user_id => p_ownerId,
1615 p_generate_list => p_generateList_flag,
1616 p_list_name => p_list_name
1617 );
1618
1619 END IF;
1620
1621 --DBMS_LOB.FREETEMPORARY (l_xml_doc_content);
1622 COMMIT WORK;
1623
1624 EXCEPTION
1625 WHEN FND_API.G_EXC_ERROR THEN
1626 --DBMS_LOB.FREETEMPORARY (l_xml_doc_content);
1627 ROLLBACK TO Store_XML_Util;
1628 Retcode := FND_API.G_RET_STS_ERROR;
1629 WHEN OTHERS THEN
1630 --DBMS_LOB.FREETEMPORARY (l_xml_doc_content);
1631 ROLLBACK TO Store_XML_Util;
1632 Errbuf := 'Unexpected error in '
1633 || L_FULL_NAME || ': '|| SQLERRM;
1634 Retcode := FND_API.G_RET_STS_UNEXP_ERROR;
1635 END Store_XML_Util;
1636
1637 -- Start of comments
1638 -- API Name Store_XML
1639 -- Type Public
1640 -- Pre-reqs None.
1641 -- Function Takes the list import header id, filter and populate xml into the xml element
1642 -- xml attribute tables.
1643 -- Parameters
1644 -- IN
1645 -- p_import_list_header_id IN NUMBER,
1646 -- p_commit IN VARCHAR2 := FND_API.G_TRUE,
1647 -- p_ownerId IN NUMBER,
1648 -- p_generateList_flag IN VARCHAR2,
1649 -- p_list_name IN VARCHAR2,
1650 -- p_import_flag IN VARCHAR2,
1651 -- p_status_code IN VARCHAR2
1652 -- OUT x_return_status VARCHAR2
1653 -- x_msg_data VARCHAR2
1654 --
1655 -- Version Current version: 1.0
1656 -- Previous version: 1.0
1657 -- Initial version: 1.0
1658 -- End of comments
1659 PROCEDURE Store_XML (
1660 p_import_list_header_id IN NUMBER,
1661 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1662 p_ownerId IN NUMBER,
1663 p_generateList_flag IN VARCHAR2,
1664 p_list_name IN VARCHAR2,
1665 p_import_flag IN VARCHAR2,
1666 p_status_code IN VARCHAR2,
1667 x_return_status OUT NOCOPY VARCHAR2,
1668 x_msg_data OUT NOCOPY VARCHAR2
1669 )
1670 IS
1671 CURSOR c_xml_doc (p_import_list_header_id NUMBER)
1672 IS SELECT CONTENT_TEXT
1673 FROM AMS_IMP_DOCUMENTS
1674 WHERE IMPORT_LIST_HEADER_ID = p_import_list_header_id;
1675
1676 l_xml_clob c_xml_doc%ROWTYPE;
1677 l_doc_length NUMBER;
1678 l_profile_length NUMBER := 0;
1679 l_request_id NUMBER;
1680 l_ret_status VARCHAR2(1);
1681 l_client_file_size VARCHAR2 (20);
1682 l_msg_count NUMBER;
1683 BEGIN
1684
1685 SAVEPOINT Store_XML;
1686 x_return_status := FND_API.G_RET_STS_SUCCESS;
1687
1688 --l_request_id := FND_REQUEST.SUBMIT_REQUEST (
1689 -- application => 'AMS',
1690 -- program => 'AMS_IMP_REP_TEST',
1691 -- argument1 => '14123');
1692
1693 l_client_file_size := fnd_profile.value('AMS_IMP_CLIENT_FILE_SIZE');
1694 l_profile_length := TO_NUMBER (l_client_file_size);
1695 OPEN c_xml_doc (p_import_list_header_id);
1696 FETCH c_xml_doc INTO l_xml_clob;
1697 IF c_xml_doc%FOUND THEN
1698 l_doc_length := DBMS_LOB.GETLENGTH (l_xml_clob.CONTENT_TEXT);
1699 IF l_doc_length >= l_profile_length THEN
1700 l_request_id := FND_REQUEST.SUBMIT_REQUEST (
1701 application => 'AMS',
1702 program => 'AMSILXOM',
1703 argument1 => p_import_list_header_id,
1704 argument2 => p_ownerId,
1705 argument3 => p_generateList_flag,
1706 argument4 => p_list_name,
1707 argument5 => p_import_flag,
1708 argument6 => p_status_code);
1709 IF l_request_id = 0 THEN
1710 AMS_Utility_PVT.Create_Log (
1711 x_return_status => l_ret_status,
1712 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1713 p_log_used_by_id => p_import_list_header_id,
1714 p_msg_data => 'Can not start the list import XML Load Concurrent Program.',
1715 p_msg_type => 'DEBUG'
1716 );
1717 x_msg_data := 'Can not start the list import XML Load Concurrent Program.';
1718
1719 RAISE FND_API.G_EXC_ERROR;
1720 END IF;
1721
1722 AMS_Utility_PVT.Create_Log (
1723 x_return_status => l_ret_status,
1724 p_arc_log_used_by => G_ARC_IMPORT_HEADER,
1725 p_log_used_by_id => p_import_list_header_id,
1726 p_msg_data => 'List Import XML Load Concurrent Program AMSILXOM Started.',
1727 p_msg_type => 'DEBUG'
1728 );
1729
1730 ELSE
1731 Store_XML_Util (
1735 p_ownerId => p_ownerId,
1732 Errbuf => x_msg_data,
1733 Retcode => x_return_status,
1734 p_import_list_header_id => p_import_list_header_id,
1736 p_generateList_flag => p_generateList_flag,
1737 p_list_name => p_list_name,
1738 p_import_flag => p_import_flag,
1739 p_status_code => p_status_code
1740 );
1741 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1742 RAISE FND_API.G_EXC_ERROR;
1743 END IF;
1744 END IF;
1745 END IF;
1746 CLOSE c_xml_doc;
1747
1748 EXCEPTION
1749 WHEN FND_API.G_EXC_ERROR THEN
1750 ROLLBACK TO Store_XML;
1751 x_return_status := FND_API.G_RET_STS_ERROR;
1752 FND_MSG_PUB.count_and_get(
1753 p_encoded => FND_API.g_false,
1754 p_count => l_msg_count,
1755 p_data => x_msg_data
1756 );
1757 WHEN OTHERS THEN
1758 ROLLBACK TO Store_XML;
1759 x_return_status := FND_API.g_ret_sts_unexp_error ;
1760
1761 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
1762 FND_MSG_PUB.add_exc_msg(g_pkg_name, 'error_capture');
1763 END IF;
1764
1765 FND_MSG_PUB.count_and_get(
1766 p_encoded => FND_API.g_false,
1767 p_count => l_msg_count,
1768 p_data => x_msg_data
1769 );
1770
1771 END Store_XML;
1772
1773 END AMS_Import_XML_PVT;