[Home] [Help]
PACKAGE BODY: APPS.INV_XML_REPORTS
Source
1 PACKAGE BODY inv_xml_reports AS
2 /* $Header: INVXREPB.pls 120.12 2011/02/04 10:19:18 ksivasa ship $ */
3 -- Bug 8425698: store XML encoding specification
4 g_xml_encoding VARCHAR2(100) := '-9999';
5
6 --#11703502 Character date international calendar support to add calendar_aware in date function arguments created new function.
7 FUNCTION C_CALENDAR_VALUE RETURN NUMBER IS
8 BEGIN
9 RETURN FND_DATE.CALENDAR_AWARE_ALT;
10 END C_CALENDAR_VALUE;
14 , retcode OUT NOCOPY VARCHAR2
11
12 PROCEDURE lot_inventory_report (
13 errbuf OUT NOCOPY VARCHAR2
15 , p_organization_id IN NUMBER
16 , p_from_item IN VARCHAR2
17 , p_to_item IN VARCHAR2
18 , p_from_subinv IN VARCHAR2
19 , p_to_subinv IN VARCHAR2) IS
20
21 qryctx DBMS_XMLGEN.ctxhandle;
22 v_result CLOB;
23 QUERY VARCHAR2 (32000);
24 BEGIN
25 --#11703502 Character date international calendar support Added calendar_aware in date function arguments
26 QUERY :=
27 'SELECT mp.organization_code ORG_CODE
28 , sysdate REP_DATE, ''' || nvl(p_from_item, ' ') || ''' p_from_item,''' || nvl(p_to_item, ' ') || ''' p_to_item,
29 ''' || nvl(p_from_subinv, ' ') || ''' p_from_subinv,''' || nvl(p_to_subinv, ' ') || ''' p_to_subinv
30 , cursor(select mss.secondary_inventory_name SUBINV
31 ,cursor(select msi.concatenated_segments ITEM_NO
32 , msi.description ITEM_DESC
33 ,cursor(select mln.lot_number LOT
34 , fnd_date.date_to_displayDT(mln.origination_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE ) ORIG_DATE
35 , fnd_date.date_to_displayDT(mln.expiration_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) EXP_DATE
36 , mil.concatenated_segments LOCATOR
37 , sum(ohd.primary_transaction_quantity) QTY
38 , msi.primary_uom_code UOM
39 , sum(nvl(ohd.secondary_transaction_quantity,0)) SEC_QTY
40 , msi.secondary_uom_code SEC_UOM
41 , mms.status_code STATUS
42 , mln.grade_code GRADE
43 from mtl_lot_numbers mln,
44 mtl_item_locations_kfv mil,
45 mtl_onhand_quantities_detail ohd,
46 mtl_material_statuses_vl mms
47 WHERE msi.inventory_item_id = mln.inventory_item_id
48 AND msi.organization_id = mln.organization_id
49 -- for the bug 10236246
50 AND ohd.status_id is null
51 -- end of the bug 10236246
52 AND mln.status_id = mms.status_id(+)
53 AND ohd.organization_id = mln.organization_id
54 AND ohd.inventory_item_id = mln.inventory_item_id
55 AND ohd.lot_number = mln.lot_number
56 AND ohd.locator_id = mil.inventory_location_id(+)
57 and ohd.organization_id = mp.organization_id
58 and ohd.subinventory_code = mss.secondary_inventory_name
59 and ohd.organization_id = msi.organization_id
60 and ohd.inventory_item_id = msi.inventory_item_id
61 group by mln.lot_number , mln.origination_date,mln.expiration_date
62 , mil.concatenated_segments
63 , msi.primary_uom_code
64 , msi.secondary_uom_code
65 , mms.status_code
66 , mln.grade_code
67
68 -- For the bug 10236246 query the on hand material status tracked org.
69 union
70 select mln.lot_number LOT
71 , fnd_date.date_to_displayDT(mln.origination_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) ORIG_DATE
72 , fnd_date.date_to_displayDT(mln.expiration_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) EXP_DATE
73 , mil.concatenated_segments LOCATOR
74 , sum(ohd.primary_transaction_quantity) QTY
75 , msi.primary_uom_code UOM
76 , sum(nvl(ohd.secondary_transaction_quantity,0)) SEC_QTY
77 , msi.secondary_uom_code SEC_UOM
78 , mms.status_code STATUS
79 , mln.grade_code GRADE
80 from mtl_lot_numbers mln,
81 mtl_item_locations_kfv mil,
82 mtl_onhand_quantities_detail ohd,
83 mtl_material_statuses_vl mms
87 AND ohd.status_id = mms.status_id(+)
84 WHERE msi.inventory_item_id = mln.inventory_item_id
85 AND msi.organization_id = mln.organization_id
86 AND ohd.status_id is not null
88 AND ohd.organization_id = mln.organization_id
89 AND ohd.inventory_item_id = mln.inventory_item_id
90 AND ohd.lot_number = mln.lot_number
91 AND ohd.locator_id = mil.inventory_location_id(+)
92 and ohd.organization_id = mp.organization_id
93 and ohd.subinventory_code = mss.secondary_inventory_name
94 and ohd.organization_id = msi.organization_id
95 and ohd.inventory_item_id = msi.inventory_item_id
96 group by mln.lot_number , mln.origination_date,mln.expiration_date
97 , mil.concatenated_segments
98 , msi.primary_uom_code
99 , msi.secondary_uom_code
100 , mms.status_code
101 , mln.grade_code
102 ) as LOT_DETAILS
103 FROM mtl_system_items_kfv msi
104 WHERE msi.organization_id = mss.organization_id
105 and msi.organization_id = mp.organization_id
106 and msi.lot_control_code = 2';
107
108 IF ( p_from_item IS NOT NULL
109 AND p_to_item IS NOT NULL) THEN
110 QUERY := QUERY
111 || ' AND (msi.concatenated_segments >= '''
112 || p_from_item
113 || ''' AND msi.concatenated_segments <= '''
114 || p_to_item
115 || ''')';
116 ELSIF (p_from_item IS NOT NULL) THEN
117 QUERY := QUERY || ' AND msi.concatenated_segments >= ''' || p_from_item || '''';
118 ELSIF (p_to_item IS NOT NULL) THEN
119 QUERY := QUERY || ' AND msi.concatenated_segments <= ''' || p_to_item || '''';
120 END IF;
121
122 QUERY := QUERY
123 || ' and exists (select '' x '' from mtl_onhand_quantities_detail ohd1
124 where ohd1.organization_id = mp.organization_id
125 and ohd1.subinventory_code = mss.secondary_inventory_name
126 and ohd1.organization_id = msi.organization_id
127 and ohd1.inventory_item_id = msi.inventory_item_id) ) AS ITEM_DETAILS
128 FROM mtl_secondary_inventories mss
129 WHERE mss.organization_id = mp.organization_id';
130
131 IF ( p_from_subinv IS NOT NULL
132 AND p_to_subinv IS NOT NULL) THEN
133 QUERY := QUERY
134 || ' AND (mss.secondary_inventory_name >= '''
135 || p_from_subinv
136 || ''' AND mss.secondary_inventory_name <= '''
137 || p_to_subinv
138 || ''')';
139 ELSIF (p_from_subinv IS NOT NULL) THEN
140 QUERY := QUERY || ' AND mss.secondary_inventory_name >= ''' || p_from_subinv || '''';
141 ELSIF (p_to_subinv IS NOT NULL) THEN
142 QUERY := QUERY || ' AND mss.secondary_inventory_name <= ''' || p_to_subinv || '''';
143 END IF;
144
145 QUERY := QUERY
146 || ' AND EXISTS ( SELECT ''x''
147 FROM mtl_onhand_quantities_detail d
148 WHERE d.organization_id = mss.organization_id
149 AND d.subinventory_code = mss.secondary_inventory_name
150 and d.organization_id = mp.organization_id )) AS SUBINV_DETAILS
151 FROM mtl_parameters mp
152 WHERE mp.organization_id = '
153 || p_organization_id;
154
155 fnd_file.put_line (fnd_file.LOG, query);
156
157 qryctx := DBMS_XMLGEN.newcontext (QUERY);
158
159 LOOP
160 -- now get the result
161 v_result := DBMS_XMLGEN.getxml (qryctx);
162
163 -- if there were no rows processed, then quit
164 IF v_result IS NULL THEN
165 EXIT;
166 END IF;
167
168 xml_transfer (p_xml_clob => v_result);
169 END LOOP;
170
171 --close context
172 DBMS_XMLGEN.closecontext (qryctx);
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 fnd_file.put_line (fnd_file.LOG, 'Exception in procedure LOT_INVENTORY_REPORT ' || SQLCODE || ' ' || SQLERRM);
177 END lot_inventory_report;
178
179 PROCEDURE lot_master_report (
180 errbuf OUT NOCOPY VARCHAR2
181 , retcode OUT NOCOPY VARCHAR2
182 , p_organization_id IN NUMBER
183 , p_from_item IN VARCHAR2
184 , p_to_item IN VARCHAR2) IS
185
186 qryctx DBMS_XMLGEN.ctxhandle;
187 v_result CLOB;
188 QUERY VARCHAR2 (32000);
189 BEGIN
193 sysdate rep_date,''' || nvl(p_from_item, ' ') || ''' p_from_item,''' || nvl(p_to_item, ' ') || ''' p_to_item ,
190 --#11703502 Character date international calendar support Added calendar_aware in date function arguments
191 QUERY :=
192 ' SELECT mp.organization_code org,
194 cursor(select msi.concatenated_segments Item_number
195 , msi.description item_desc
196 , cursor(select mln.lot_number
197 , mln.description
198 , mln.grade_code
199 , mln.expiration_action_code
200 , fnd_date.date_to_displayDT(mln.expiration_action_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) expiration_action_date
201 , mln.origination_type
202 , mfgl.meaning lot_origination
203 , fnd_date.date_to_displayDT(mln.origination_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) origination_date
204 , fnd_date.date_to_displayDT(mln.expiration_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) expiration_date
205 , fnd_date.date_to_displayDT(mln.retest_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) retest_date
206 , fnd_date.date_to_displayDT(mln.maturity_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) maturity_date
207 , fnd_date.date_to_displayDT(mln.hold_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) hold_date
208 , mln.parent_lot_number
209 , mln.vendor_name
210 , mln.supplier_lot_number
211 FROM mtl_lot_numbers mln
212 , mfg_lookups mfgl
213 WHERE msi.organization_id = mln.organization_id
214 AND msi.inventory_item_id = mln.inventory_item_id
215 AND mln.origination_type = mfgl.LOOKUP_CODE(+)
216 AND mfgl.lookup_type(+) = ''MTL_LOT_ORIGINATION_TYPE'') as LOT_DETAILS
217 FROM mtl_system_items_kfv msi
218 WHERE msi.organization_id = mp.organization_id ';
219
220 IF ( p_from_item IS NOT NULL
221 AND p_to_item IS NOT NULL) THEN
222 QUERY := QUERY
223 || ' AND (msi.concatenated_segments >= '''
224 || p_from_item
225 || ''' AND msi.concatenated_segments <= '''
226 || p_to_item
227 || ''')';
228 ELSIF (p_from_item IS NOT NULL) THEN
229 QUERY := QUERY || ' AND msi.concatenated_segments >= ''' || p_from_item || '''';
230 ELSIF (p_to_item IS NOT NULL) THEN
231 QUERY := QUERY || ' AND msi.concatenated_segments <= ''' || p_to_item || '''';
232 END IF;
233
234 QUERY := QUERY
235 || ' and msi.lot_control_code = 2 and exists ( select mln1.inventory_item_id from mtl_lot_numbers mln1 where msi.organization_id = mln1.organization_id
236 AND msi.inventory_item_id = mln1.inventory_item_id and mln1.organization_id = mp.organization_id)
237 ORDER BY msi.concatenated_segments) as ITEM_DETAILS
238 from mtl_parameters mp
239 where mp.organization_id = '
240 || p_organization_id;
241
242 fnd_file.put_line (fnd_file.LOG, query);
243 qryctx := DBMS_XMLGEN.newcontext (QUERY);
244
245 LOOP
246 -- now get the result
247 v_result := DBMS_XMLGEN.getxml (qryctx);
248
249 -- if there were no rows processed, then quit
250 IF v_result IS NULL THEN
251 EXIT;
252 END IF;
253
254 xml_transfer (p_xml_clob => v_result);
255 END LOOP;
256
257 --close context
258 DBMS_XMLGEN.closecontext (qryctx);
259
260 EXCEPTION
261 WHEN OTHERS THEN
262 fnd_file.put_line (fnd_file.LOG, 'Exception in procedure LOT_MASTER_REPORT ' || SQLCODE || ' ' || SQLERRM);
263 END lot_master_report;
264
265 PROCEDURE mat_status_def_report (
266 errbuf OUT NOCOPY VARCHAR2
267 , retcode OUT NOCOPY VARCHAR2
268 , p_from_status IN VARCHAR2
269 , p_to_status IN VARCHAR2
270 , p_sort_order IN NUMBER) IS
271
272 qryctx DBMS_XMLGEN.ctxhandle;
273 v_result CLOB;
274 QUERY VARCHAR2 (32000);
275 l_order VARCHAR2 (10);
276 l_range VARCHAR2 (170) := NULL;
277 l_order_display VARCHAR2(80);
278 BEGIN
279 IF (p_sort_order = 2) THEN
280 l_order := 'DESC';
281 ELSE
282 l_order := 'ASC';
283 END IF;
284
285 SELECT meaning
286 INTO l_order_display
287 FROM mfg_lookups
288 WHERE lookup_type = 'INV_SRS_ASC_DESC'
289 AND enabled_flag = 'Y'
290 AND lookup_code = NVL(p_sort_order, 1);
291
292 /*
293 IF ( p_from_status IS NOT NULL
294 AND p_to_status IS NOT NULL) THEN
295 l_range := p_from_status || ' - ' || p_to_status;
296 ELSIF (p_from_status IS NOT NULL) THEN
300 ELSE
297 l_range := 'FROM STATUS CODE - ' || p_from_status;
298 ELSIF (p_to_status IS NOT NULL) THEN
299 l_range := 'TILL STATUS CODE - ' || p_to_status;
301 l_range := 'ALL';
302 END IF;
303 */
304 /* Modified below query to accomodate onhand_control flag added for Onhand Material status project Bug 6974968 */
305 QUERY :=
306 'SELECT sysdate rep_date,''' || l_order_display || ''' ORDER_BY,''' ||
307 nvl(p_from_status, ' ') || ''' p_from_status,''' ||
308 nvl(p_to_status, ' ') || ''' p_to_status
309 , mv.status_code
310 , mv.description
311 , ml1.meaning enabled_flag_value
312 , ml2.meaning allow_reservations_flag_value
313 , ml3.meaning include_in_atp_flag_value
314 , ml4.meaning nettable_flag_value
315 , ml5.meaning subinventory_usage_flag_value
316 , ml6.meaning locator_usage_flag_value
317 , ml7.meaning lot_usage_flag_value
318 , ml8.meaning serial_usage_flag_value
319 , m19.meaning onhand_usage_flag_value
320 , cursor (select tx.transaction_description
321 from mtl_status_control_v tx
322 where tx.status_id = mv.status_id and tx.is_allowed = 1
323 order by tx.transaction_description) as allowed_transactions
324 , cursor (select tx.transaction_description
325 from mtl_status_control_v tx
326 where tx.status_id = mv.status_id and tx.is_allowed = 2
327 order by tx.transaction_description) as disallowed_transactions
328 FROM mtl_material_statuses_vl mv
329 , mfg_lookups ml1
330 , mfg_lookups ml2
331 , mfg_lookups ml3
332 , mfg_lookups ml4
333 , mfg_lookups ml5
334 , mfg_lookups ml6
335 , mfg_lookups ml7
336 , mfg_lookups ml8
337 , mfg_lookups m19
338 WHERE ml1.lookup_code = mv.enabled_flag
339 and ml1.lookup_type = ''SYS_YES_NO''
340 and ml2.lookup_code = mv.reservable_type
341 and ml2.lookup_type = ''SYS_YES_NO''
342 and ml3.lookup_code = mv.inventory_atp_code
343 and ml3.lookup_type = ''SYS_YES_NO''
344 and ml4.lookup_code = mv.availability_type
345 and ml4.lookup_type = ''SYS_YES_NO''
346 and ml5.lookup_code = mv.zone_control
347 and ml5.lookup_type = ''SYS_YES_NO''
348 and ml6.lookup_code = mv.locator_control
349 and ml6.lookup_type = ''SYS_YES_NO''
350 and ml7.lookup_code = mv.lot_control
351 and ml7.lookup_type = ''SYS_YES_NO''
352 and ml8.lookup_code = mv.serial_control
353 and ml8.lookup_type = ''SYS_YES_NO''
354 and m19.lookup_code = mv.onhand_control
355 and m19.lookup_type = ''SYS_YES_NO''';
356
357 IF ( p_from_status IS NOT NULL
358 AND p_to_status IS NOT NULL) THEN
359 QUERY := QUERY
360 || 'AND (mv.status_code >= '''
361 || p_from_status
362 || ''' AND mv.status_code <= '''
363 || p_to_status
364 || ''')';
365 ELSIF (p_from_status IS NOT NULL) THEN
366 QUERY := QUERY || 'AND mv.status_code >= ''' || p_from_status || '''';
367 ELSIF (p_to_status IS NOT NULL) THEN
368 QUERY := QUERY || 'AND mv.status_code <= ''' || p_to_status || '''';
369 END IF;
370
371 QUERY := QUERY || ' ORDER BY mv.status_code ' || l_order;
372
373 fnd_file.put_line (fnd_file.LOG, query);
374
375 qryctx := DBMS_XMLGEN.newcontext (QUERY);
376
377 LOOP
378 -- now get the result
379 v_result := DBMS_XMLGEN.getxml (qryctx);
380
381 -- if there were no rows processed, then quit
382 IF v_result IS NULL THEN
383 EXIT;
384 END IF;
385
386 xml_transfer (p_xml_clob => v_result);
387 END LOOP;
388
389 --close context
390 DBMS_XMLGEN.closecontext (qryctx);
391
392 EXCEPTION
393 WHEN OTHERS THEN
394 fnd_file.put_line (fnd_file.LOG, 'Exception in procedure MAT_STATUS_DEF_REPORT ' || SQLCODE || ' ' || SQLERRM);
395 END mat_status_def_report;
396
397 PROCEDURE grade_change_history_report (
398 errbuf OUT NOCOPY VARCHAR2
399 , retcode OUT NOCOPY VARCHAR2
400 , p_organization_id IN NUMBER
401 , p_item_id IN NUMBER
402 , p_from_lot IN VARCHAR2
403 , p_to_lot IN VARCHAR2
404 , p_from_date IN VARCHAR2
405 , p_to_date IN VARCHAR2) IS
406
407 qryctx DBMS_XMLGEN.ctxhandle;
408 v_result CLOB;
409 QUERY VARCHAR2 (32000);
410 p_from_dt DATE := fnd_date.canonical_to_date(p_from_date);
411 p_to_dt DATE := fnd_date.canonical_to_date(p_to_date);
412 BEGIN
413 /* Jalaj Srivastava Bug 4998256
414 get the meaning of update_method and not the code */
415 --#11703502 Character date international calendar support Added calendar_aware in date function arguments
416 QUERY :=
417 'SELECT sysdate rep_date
418 , ood.organization_code org_code
419 , ood.organization_name org_name
420 , msi.concatenated_segments Item_No
421 , msi.primary_uom_code pri_uom
422 , DECODE(msi.tracking_quantity_ind,''PS'',msi.secondary_uom_code) sec_uom /*Bug#5436402*/
423 , cursor ( select mlgh.lot_number lot_no
424 , msi.default_grade def_grade
425 , mlgh.old_grade_code old_grade
426 , mlgh.new_grade_code new_grade
427 , DECODE (mlgh.from_mobile_apps_flag, ''Y'', ''Mobile'', ''N'', ''Desktop'') updated_from
428 , fnd_date.date_to_displayDT(mlgh.grade_update_date,calendar_aware=>inv_xml_reports.C_CALENDAR_VALUE) upd_date
429 , fnd.user_name user_name
430 , ml.meaning upd_method
431 , mlgh.primary_quantity pri_qty
432 , mlgh.secondary_quantity sec_qty
433 , mg1.description from_desc
434 , mg2.description to_desc
435 , mtr.reason_name reason
436 from mtl_lot_grade_history mlgh
437 , mtl_grades_vl mg1
438 , mtl_grades_vl mg2
439 , mtl_transaction_reasons mtr
440 , fnd_user fnd
441 , mfg_lookups ml
442 where msi.organization_id = mlgh.organization_id
443 AND msi.inventory_item_id = mlgh.inventory_item_id
444 AND mlgh.old_grade_code = mg1.grade_code
445 AND mlgh.new_grade_code = mg2.grade_code
446 AND mlgh.created_by = fnd.user_id
447 AND mlgh.update_reason_id = mtr.reason_id(+)
448 AND ml.lookup_type = ''MTL_STATUS_UPDATE_METHOD''
449 AND ml.lookup_code = mlgh.update_method ';
450
451 IF ( p_from_lot IS NOT NULL
452 AND p_to_lot IS NOT NULL) THEN
453 QUERY := QUERY
454 || 'AND (mlgh.lot_number >= '''
455 || p_from_lot
456 || ''' AND mlgh.lot_number <= '''
457 || p_to_lot
458 || ''')';
459 ELSIF (p_from_lot IS NOT NULL) THEN
460 QUERY := QUERY || 'AND mlgh.lot_number >= ''' || p_from_lot || '''';
461 ELSIF (p_to_lot IS NOT NULL) THEN
462 QUERY := QUERY || 'AND mlgh.lot_number <= ''' || p_to_lot || '''';
463 END IF;
464
465 IF ( p_from_date IS NOT NULL
466 AND p_to_date IS NOT NULL) THEN
467 QUERY := QUERY
468 || 'AND (mlgh.grade_update_date >= '''
469 || p_from_dt
470 || ''' AND mlgh.grade_update_date <= '''
471 || p_to_dt
472 || ''')';
473 ELSIF (p_from_date IS NOT NULL) THEN
474 QUERY := QUERY || 'AND mlgh.grade_update_date >= ''' || p_from_date || '''';
475 ELSIF (p_to_date IS NOT NULL) THEN
476 QUERY := QUERY || 'AND mlgh.grade_update_date <= ''' || p_to_date || '''';
477 END IF;
478
479 QUERY := QUERY
480 || ' order by mlgh.lot_number, mlgh.grade_update_date) as Lot_details
481 FROM org_organization_definitions ood,
482 mtl_system_items_kfv msi
483 WHERE ood.organization_id = msi.organization_id
484 AND ood.organization_id = '''
485 || p_organization_id
486 || ''''
487 || '
488 AND msi.inventory_item_id = '''
489 || p_item_id
490 || ''''
491 || '
492 ORDER BY msi.concatenated_segments';
493
494 fnd_file.put_line (fnd_file.LOG, query);
495
496 qryctx := DBMS_XMLGEN.newcontext (QUERY);
497
498 LOOP
499 -- now get the result
500 v_result := DBMS_XMLGEN.getxml (qryctx);
501
502 -- if there were no rows processed, then quit
503 IF v_result IS NULL THEN
504 EXIT;
505 END IF;
506
507 xml_transfer (p_xml_clob => v_result);
508 END LOOP;
509
510 --close context
511 DBMS_XMLGEN.closecontext (qryctx);
512
513 EXCEPTION
514 WHEN OTHERS THEN
515 fnd_file.put_line (fnd_file.LOG, 'Exception in procedure GRADE_CHANGE_HISTORY_REPORT ' || SQLCODE || ' ' || SQLERRM);
516 END grade_change_history_report;
517
518 /* ***************************************************************
519 * NAME
520 * PROCEDURE - xml_transfer
521 * PARAMETERS
522 * DESCRIPTION
523 * Procedure used provide the XML as output of the concurrent program.
524 * HISTORY
525 * Namit 31Mar05 - Initial Version
526 *************************************************************** */
527 PROCEDURE xml_transfer (
528 p_xml_clob IN CLOB) IS
529
530 l_file CLOB;
531 --bug 8238368 kbanddyo increased length of file_varchar2 from 4000 to 32767
532 file_varchar2 VARCHAR2 (32767);
533 l_len NUMBER;
534 m_len NUMBER;
535 l_limit NUMBER;
536 m_file CLOB;
537 l_xml_header VARCHAR2(300); -- Bug 8425698
538
539 BEGIN
540
541 -- Bug 8425698: determine XML encoding
542 IF g_xml_encoding = '-9999' THEN
543 g_xml_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
544 END IF;
545 l_xml_header := '<?xml version="1.0" encoding="'|| g_xml_encoding ||'"?>';
546
547 l_file := p_xml_clob;
548 l_limit := 1;
549 l_len := DBMS_LOB.getlength (l_file);
550
551 LOOP
552 m_file := DBMS_LOB.SUBSTR(l_file, 4000, l_limit);
553 file_varchar2 := TRIM(m_file);
554 file_varchar2 := REPLACE(file_varchar2,'<?xml version="1.0"?>',l_xml_header);
555 fnd_file.put(fnd_file.output, file_varchar2);
556 file_varchar2 := NULL;
557 m_file := NULL;
558
559 IF l_len > l_limit THEN
560 l_limit := l_limit + 4000;
561 ELSE
562 EXIT;
563 END IF;
564 END LOOP;
565
566 EXCEPTION
567 WHEN OTHERS THEN
568 fnd_file.put_line (fnd_file.LOG, 'Exception in procedure XML_TRANSFER ' || SQLCODE || ' ' || SQLERRM);
569 END xml_transfer;
570
571 END inv_xml_reports;