92: l_sql_string VARCHAR2(1500); --The dynamic sql constructed
93:
94: l_output_loc VARCHAR2(250); --The Output location of the CSV file.
95: l_out_file_name VARCHAR2(150); --The Output file name.
96: l_handle UTL_FILE.FILE_TYPE; -- File handle for the O/P file.
97:
98: l_cln_stat NUMBER; --The collaboration status of an item
99: l_indicator CHAR(1); --Action indicator for each item
100: --'I' for insert, 'U' for update.
168: xnb_debug.log('gen_item_batch_file','Inside IF output_format is CSV');
169:
170: l_out_file_name := l_out_file_name || '.csv';
171:
172: l_handle := UTL_FILE.FOPEN( l_output_loc, l_out_file_name, 'W');
173:
174: --debug
175: xnb_debug.log('gen_item_batch_file','After opening the file'||l_output_loc);
176:
176:
177: --Date:19-Apr-05 Author: DPUTHIYE Bug:4314879
178: --Change: Removing spaces after commas in the CSV header. CSV files are machine read.
179: --Other files impacted: None.
180: UTL_FILE.PUTF(l_handle,'INDICATOR,PUBLISH_DATE,PROGRAM_NAME,ITEMID,ITEM_NAME,BOM_ITEM_TYPE,BOM_ITYPE_DESC,UOM,ITEM_DESCRIPTION,');
181: UTL_FILE.PUTF(l_handle,'ITEM_STATUS,ITEM_STATUS_DESC,ITEM_TYPE,ITEM_TYPE_DESC,ITEM_CLASS,START_DATE,END_DATE,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,');
182: UTL_FILE.PUTF(l_handle,'ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15\n');
183:
184: --debug
177: --Date:19-Apr-05 Author: DPUTHIYE Bug:4314879
178: --Change: Removing spaces after commas in the CSV header. CSV files are machine read.
179: --Other files impacted: None.
180: UTL_FILE.PUTF(l_handle,'INDICATOR,PUBLISH_DATE,PROGRAM_NAME,ITEMID,ITEM_NAME,BOM_ITEM_TYPE,BOM_ITYPE_DESC,UOM,ITEM_DESCRIPTION,');
181: UTL_FILE.PUTF(l_handle,'ITEM_STATUS,ITEM_STATUS_DESC,ITEM_TYPE,ITEM_TYPE_DESC,ITEM_CLASS,START_DATE,END_DATE,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,');
182: UTL_FILE.PUTF(l_handle,'ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15\n');
183:
184: --debug
185: xnb_debug.log('gen_item_batch_file','After writing the first Line');
178: --Change: Removing spaces after commas in the CSV header. CSV files are machine read.
179: --Other files impacted: None.
180: UTL_FILE.PUTF(l_handle,'INDICATOR,PUBLISH_DATE,PROGRAM_NAME,ITEMID,ITEM_NAME,BOM_ITEM_TYPE,BOM_ITYPE_DESC,UOM,ITEM_DESCRIPTION,');
181: UTL_FILE.PUTF(l_handle,'ITEM_STATUS,ITEM_STATUS_DESC,ITEM_TYPE,ITEM_TYPE_DESC,ITEM_CLASS,START_DATE,END_DATE,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,');
182: UTL_FILE.PUTF(l_handle,'ATTRIBUTE5,ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15\n');
183:
184: --debug
185: xnb_debug.log('gen_item_batch_file','After writing the first Line');
186:
240:
241: --Date:19-Apr-05 Author: DPUTHIYE Bug:4314879
242: --Change: Added missing comma after the 5th column (item_name).
243: --Other files impacted: None.
244: UTL_FILE.PUTF(l_handle, '%s,%s,XNB,%s,%s,', l_indicator, to_char(sysdate,'DD-MON-YYYY'), l_item_rec.item_id, l_item_rec.item_name);
245: UTL_FILE.PUTF(l_handle, '%s,"%s",%s,"%s",', l_item_rec.bom_item_type, l_item_rec.bom_itype_desc, l_item_rec.unit_of_measure, l_item_rec.description);
246: UTL_FILE.PUTF(l_handle, '%s,"%s",%s,"%s",', l_item_rec.status_code, l_item_rec.status_desc, l_item_rec.item_type, l_item_rec.item_type_desc);
247: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,', l_item_rec.item_class, l_item_rec.start_date, l_item_rec.end_date, l_item_rec.attribute1 );
248: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,', l_item_rec.attribute2, l_item_rec.attribute3, l_item_rec.attribute4, l_item_rec.attribute5);
241: --Date:19-Apr-05 Author: DPUTHIYE Bug:4314879
242: --Change: Added missing comma after the 5th column (item_name).
243: --Other files impacted: None.
244: UTL_FILE.PUTF(l_handle, '%s,%s,XNB,%s,%s,', l_indicator, to_char(sysdate,'DD-MON-YYYY'), l_item_rec.item_id, l_item_rec.item_name);
245: UTL_FILE.PUTF(l_handle, '%s,"%s",%s,"%s",', l_item_rec.bom_item_type, l_item_rec.bom_itype_desc, l_item_rec.unit_of_measure, l_item_rec.description);
246: UTL_FILE.PUTF(l_handle, '%s,"%s",%s,"%s",', l_item_rec.status_code, l_item_rec.status_desc, l_item_rec.item_type, l_item_rec.item_type_desc);
247: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,', l_item_rec.item_class, l_item_rec.start_date, l_item_rec.end_date, l_item_rec.attribute1 );
248: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,', l_item_rec.attribute2, l_item_rec.attribute3, l_item_rec.attribute4, l_item_rec.attribute5);
249: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,%s,', l_item_rec.attribute6, l_item_rec.attribute7, l_item_rec.attribute8, l_item_rec.attribute9, l_item_rec.attribute10);
242: --Change: Added missing comma after the 5th column (item_name).
243: --Other files impacted: None.
244: UTL_FILE.PUTF(l_handle, '%s,%s,XNB,%s,%s,', l_indicator, to_char(sysdate,'DD-MON-YYYY'), l_item_rec.item_id, l_item_rec.item_name);
245: UTL_FILE.PUTF(l_handle, '%s,"%s",%s,"%s",', l_item_rec.bom_item_type, l_item_rec.bom_itype_desc, l_item_rec.unit_of_measure, l_item_rec.description);
246: UTL_FILE.PUTF(l_handle, '%s,"%s",%s,"%s",', l_item_rec.status_code, l_item_rec.status_desc, l_item_rec.item_type, l_item_rec.item_type_desc);
247: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,', l_item_rec.item_class, l_item_rec.start_date, l_item_rec.end_date, l_item_rec.attribute1 );
248: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,', l_item_rec.attribute2, l_item_rec.attribute3, l_item_rec.attribute4, l_item_rec.attribute5);
249: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,%s,', l_item_rec.attribute6, l_item_rec.attribute7, l_item_rec.attribute8, l_item_rec.attribute9, l_item_rec.attribute10);
250: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,%s\n', l_item_rec.attribute11, l_item_rec.attribute12, l_item_rec.attribute13, l_item_rec.attribute14, l_item_rec.attribute15);
243: --Other files impacted: None.
244: UTL_FILE.PUTF(l_handle, '%s,%s,XNB,%s,%s,', l_indicator, to_char(sysdate,'DD-MON-YYYY'), l_item_rec.item_id, l_item_rec.item_name);
245: UTL_FILE.PUTF(l_handle, '%s,"%s",%s,"%s",', l_item_rec.bom_item_type, l_item_rec.bom_itype_desc, l_item_rec.unit_of_measure, l_item_rec.description);
246: UTL_FILE.PUTF(l_handle, '%s,"%s",%s,"%s",', l_item_rec.status_code, l_item_rec.status_desc, l_item_rec.item_type, l_item_rec.item_type_desc);
247: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,', l_item_rec.item_class, l_item_rec.start_date, l_item_rec.end_date, l_item_rec.attribute1 );
248: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,', l_item_rec.attribute2, l_item_rec.attribute3, l_item_rec.attribute4, l_item_rec.attribute5);
249: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,%s,', l_item_rec.attribute6, l_item_rec.attribute7, l_item_rec.attribute8, l_item_rec.attribute9, l_item_rec.attribute10);
250: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,%s\n', l_item_rec.attribute11, l_item_rec.attribute12, l_item_rec.attribute13, l_item_rec.attribute14, l_item_rec.attribute15);
251:
244: UTL_FILE.PUTF(l_handle, '%s,%s,XNB,%s,%s,', l_indicator, to_char(sysdate,'DD-MON-YYYY'), l_item_rec.item_id, l_item_rec.item_name);
245: UTL_FILE.PUTF(l_handle, '%s,"%s",%s,"%s",', l_item_rec.bom_item_type, l_item_rec.bom_itype_desc, l_item_rec.unit_of_measure, l_item_rec.description);
246: UTL_FILE.PUTF(l_handle, '%s,"%s",%s,"%s",', l_item_rec.status_code, l_item_rec.status_desc, l_item_rec.item_type, l_item_rec.item_type_desc);
247: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,', l_item_rec.item_class, l_item_rec.start_date, l_item_rec.end_date, l_item_rec.attribute1 );
248: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,', l_item_rec.attribute2, l_item_rec.attribute3, l_item_rec.attribute4, l_item_rec.attribute5);
249: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,%s,', l_item_rec.attribute6, l_item_rec.attribute7, l_item_rec.attribute8, l_item_rec.attribute9, l_item_rec.attribute10);
250: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,%s\n', l_item_rec.attribute11, l_item_rec.attribute12, l_item_rec.attribute13, l_item_rec.attribute14, l_item_rec.attribute15);
251:
252: --debug
245: UTL_FILE.PUTF(l_handle, '%s,"%s",%s,"%s",', l_item_rec.bom_item_type, l_item_rec.bom_itype_desc, l_item_rec.unit_of_measure, l_item_rec.description);
246: UTL_FILE.PUTF(l_handle, '%s,"%s",%s,"%s",', l_item_rec.status_code, l_item_rec.status_desc, l_item_rec.item_type, l_item_rec.item_type_desc);
247: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,', l_item_rec.item_class, l_item_rec.start_date, l_item_rec.end_date, l_item_rec.attribute1 );
248: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,', l_item_rec.attribute2, l_item_rec.attribute3, l_item_rec.attribute4, l_item_rec.attribute5);
249: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,%s,', l_item_rec.attribute6, l_item_rec.attribute7, l_item_rec.attribute8, l_item_rec.attribute9, l_item_rec.attribute10);
250: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,%s\n', l_item_rec.attribute11, l_item_rec.attribute12, l_item_rec.attribute13, l_item_rec.attribute14, l_item_rec.attribute15);
251:
252: --debug
253: xnb_debug.log('gen_item_batch_file','After writing it into the File');
246: UTL_FILE.PUTF(l_handle, '%s,"%s",%s,"%s",', l_item_rec.status_code, l_item_rec.status_desc, l_item_rec.item_type, l_item_rec.item_type_desc);
247: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,', l_item_rec.item_class, l_item_rec.start_date, l_item_rec.end_date, l_item_rec.attribute1 );
248: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,', l_item_rec.attribute2, l_item_rec.attribute3, l_item_rec.attribute4, l_item_rec.attribute5);
249: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,%s,', l_item_rec.attribute6, l_item_rec.attribute7, l_item_rec.attribute8, l_item_rec.attribute9, l_item_rec.attribute10);
250: UTL_FILE.PUTF(l_handle, '%s,%s,%s,%s,%s\n', l_item_rec.attribute11, l_item_rec.attribute12, l_item_rec.attribute13, l_item_rec.attribute14, l_item_rec.attribute15);
251:
252: --debug
253: xnb_debug.log('gen_item_batch_file','After writing it into the File');
254:
260:
261: --debug
262: xnb_debug.log('gen_item_batch_file','End of Cursor');
263:
264: UTL_FILE.PUTF(l_handle,'##ENDOFBATCH: %s Records##', l_rec_count);
265: CLOSE l_items_cur_R;
266: --debug
267: xnb_debug.log('gen_item_batch_file','Cusor closed');
268:
269: ELSIF (p_output_format = 'XML') THEN --XML
270:
271: l_out_file_name := l_out_file_name || '.xml';
272:
273: l_handle := UTL_FILE.FOPEN( l_output_loc, l_out_file_name, 'W');
274:
275: ----------------------------------------------------------------------------------------------
276: --The first Tag in the XML file is the RowSet Tag.
277: --
275: ----------------------------------------------------------------------------------------------
276: --The first Tag in the XML file is the RowSet Tag.
277: --
278: ----------------------------------------------------------------------------------------------
279: UTL_FILE.PUTF(l_handle, '
281: ----------------------------------------------------------------------------------------------
282: --Retrieve the items to be exported. Iterate through the row set.
283: --
330: --Write current item record with the Indicator to the export file
331: --Each item will be written as a
332: ----------------------------------------------------------------------------------------------
333:
334: UTL_FILE.PUTF(l_handle, '
336: UTL_FILE.PUTF(l_handle, '
337: l_item_rec.bom_item_type, l_item_rec.bom_itype_desc, l_item_rec.unit_of_measure, l_item_rec.description);
338: UTL_FILE.PUTF(l_handle, '
331: --Each item will be written as a
332: ----------------------------------------------------------------------------------------------
333:
334: UTL_FILE.PUTF(l_handle, '
335: UTL_FILE.PUTF(l_handle, '
336: UTL_FILE.PUTF(l_handle, '
337: l_item_rec.bom_item_type, l_item_rec.bom_itype_desc, l_item_rec.unit_of_measure, l_item_rec.description);
338: UTL_FILE.PUTF(l_handle, '
339: l_item_rec.status_code, l_item_rec.status_desc , l_item_rec.item_type , l_item_rec.item_type_desc );
332: ----------------------------------------------------------------------------------------------
333:
334: UTL_FILE.PUTF(l_handle, '
335: UTL_FILE.PUTF(l_handle, '
336: UTL_FILE.PUTF(l_handle, '
337: l_item_rec.bom_item_type, l_item_rec.bom_itype_desc, l_item_rec.unit_of_measure, l_item_rec.description);
338: UTL_FILE.PUTF(l_handle, '
339: l_item_rec.status_code, l_item_rec.status_desc , l_item_rec.item_type , l_item_rec.item_type_desc );
340: UTL_FILE.PUTF(l_handle, '
334: UTL_FILE.PUTF(l_handle, '
335: UTL_FILE.PUTF(l_handle, '
336: UTL_FILE.PUTF(l_handle, '
337: l_item_rec.bom_item_type, l_item_rec.bom_itype_desc, l_item_rec.unit_of_measure, l_item_rec.description);
338: UTL_FILE.PUTF(l_handle, '
339: l_item_rec.status_code, l_item_rec.status_desc , l_item_rec.item_type , l_item_rec.item_type_desc );
340: UTL_FILE.PUTF(l_handle, '
341: l_item_rec.item_class, l_item_rec.start_date, l_item_rec.end_date, l_item_rec.attribute1);
342: UTL_FILE.PUTF(l_handle, '
336: UTL_FILE.PUTF(l_handle, '
337: l_item_rec.bom_item_type, l_item_rec.bom_itype_desc, l_item_rec.unit_of_measure, l_item_rec.description);
338: UTL_FILE.PUTF(l_handle, '
339: l_item_rec.status_code, l_item_rec.status_desc , l_item_rec.item_type , l_item_rec.item_type_desc );
340: UTL_FILE.PUTF(l_handle, '
341: l_item_rec.item_class, l_item_rec.start_date, l_item_rec.end_date, l_item_rec.attribute1);
342: UTL_FILE.PUTF(l_handle, '
343: l_item_rec.attribute2, l_item_rec.attribute3, l_item_rec.attribute4, l_item_rec.attribute5);
344: UTL_FILE.PUTF(l_handle, '
338: UTL_FILE.PUTF(l_handle, '
339: l_item_rec.status_code, l_item_rec.status_desc , l_item_rec.item_type , l_item_rec.item_type_desc );
340: UTL_FILE.PUTF(l_handle, '
341: l_item_rec.item_class, l_item_rec.start_date, l_item_rec.end_date, l_item_rec.attribute1);
342: UTL_FILE.PUTF(l_handle, '
343: l_item_rec.attribute2, l_item_rec.attribute3, l_item_rec.attribute4, l_item_rec.attribute5);
344: UTL_FILE.PUTF(l_handle, '
345: l_item_rec.attribute6, l_item_rec.attribute7, l_item_rec.attribute8, l_item_rec.attribute9, l_item_rec.attribute10);
346: UTL_FILE.PUTF(l_handle, '
340: UTL_FILE.PUTF(l_handle, '
341: l_item_rec.item_class, l_item_rec.start_date, l_item_rec.end_date, l_item_rec.attribute1);
342: UTL_FILE.PUTF(l_handle, '
343: l_item_rec.attribute2, l_item_rec.attribute3, l_item_rec.attribute4, l_item_rec.attribute5);
344: UTL_FILE.PUTF(l_handle, '
345: l_item_rec.attribute6, l_item_rec.attribute7, l_item_rec.attribute8, l_item_rec.attribute9, l_item_rec.attribute10);
346: UTL_FILE.PUTF(l_handle, '
347: l_item_rec.attribute11, l_item_rec.attribute12, l_item_rec.attribute13, l_item_rec.attribute14, l_item_rec.attribute15);
348:
342: UTL_FILE.PUTF(l_handle, '
343: l_item_rec.attribute2, l_item_rec.attribute3, l_item_rec.attribute4, l_item_rec.attribute5);
344: UTL_FILE.PUTF(l_handle, '
345: l_item_rec.attribute6, l_item_rec.attribute7, l_item_rec.attribute8, l_item_rec.attribute9, l_item_rec.attribute10);
346: UTL_FILE.PUTF(l_handle, '
347: l_item_rec.attribute11, l_item_rec.attribute12, l_item_rec.attribute13, l_item_rec.attribute14, l_item_rec.attribute15);
348:
349: --Next record
350: FETCH l_items_cur_R INTO l_item_rec;
352: --End of while Loop for Cursor
353: END LOOP;
354:
355: CLOSE l_items_cur_R;
356: UTL_FILE.PUTF( l_handle, '');
357:
358:
359: /* End of IF p_output_format = 'CSV'*/
360: END IF;
360: END IF;
361:
362: --debug
363: xnb_debug.log('gen_item_batch_file','File Handler Closed');
364: UTL_FILE.FCLOSE(l_handle);
365:
366:
367:
368: --Return the count of records to the caller.
372: RETURN l_rec_count;
373:
374: EXCEPTION
375:
376: WHEN UTL_FILE.INVALID_PATH THEN
377: UTL_FILE.FCLOSE(l_handle);
378: RETCODE := 2;
379: ERRBUF := 'Invalid directory path';
380: fnd_file.put_line(fnd_file.log , ERRBUF);
373:
374: EXCEPTION
375:
376: WHEN UTL_FILE.INVALID_PATH THEN
377: UTL_FILE.FCLOSE(l_handle);
378: RETCODE := 2;
379: ERRBUF := 'Invalid directory path';
380: fnd_file.put_line(fnd_file.log , ERRBUF);
381:
379: ERRBUF := 'Invalid directory path';
380: fnd_file.put_line(fnd_file.log , ERRBUF);
381:
382:
383: WHEN UTL_FILE.INVALID_MODE THEN
384: UTL_FILE.FCLOSE(l_handle);
385: RETCODE := 2;
386: ERRBUF := 'Invalid mode of opening the file';
387: fnd_file.put_line(fnd_file.log , ERRBUF);
380: fnd_file.put_line(fnd_file.log , ERRBUF);
381:
382:
383: WHEN UTL_FILE.INVALID_MODE THEN
384: UTL_FILE.FCLOSE(l_handle);
385: RETCODE := 2;
386: ERRBUF := 'Invalid mode of opening the file';
387: fnd_file.put_line(fnd_file.log , ERRBUF);
388:
385: RETCODE := 2;
386: ERRBUF := 'Invalid mode of opening the file';
387: fnd_file.put_line(fnd_file.log , ERRBUF);
388:
389: WHEN UTL_FILE.INVALID_OPERATION THEN
390: UTL_FILE.FCLOSE(l_handle);
391: RETCODE := 2;
392: ERRBUF := 'Invalid operation performed on the file';
393: fnd_file.put_line(fnd_file.log , ERRBUF);
386: ERRBUF := 'Invalid mode of opening the file';
387: fnd_file.put_line(fnd_file.log , ERRBUF);
388:
389: WHEN UTL_FILE.INVALID_OPERATION THEN
390: UTL_FILE.FCLOSE(l_handle);
391: RETCODE := 2;
392: ERRBUF := 'Invalid operation performed on the file';
393: fnd_file.put_line(fnd_file.log , ERRBUF);
394:
392: ERRBUF := 'Invalid operation performed on the file';
393: fnd_file.put_line(fnd_file.log , ERRBUF);
394:
395:
396: WHEN UTL_FILE.READ_ERROR THEN
397: UTL_FILE.FCLOSE(l_handle);
398: RETCODE := 2;
399: ERRBUF := 'Read error';
400: fnd_file.put_line(fnd_file.log , ERRBUF);
393: fnd_file.put_line(fnd_file.log , ERRBUF);
394:
395:
396: WHEN UTL_FILE.READ_ERROR THEN
397: UTL_FILE.FCLOSE(l_handle);
398: RETCODE := 2;
399: ERRBUF := 'Read error';
400: fnd_file.put_line(fnd_file.log , ERRBUF);
401:
399: ERRBUF := 'Read error';
400: fnd_file.put_line(fnd_file.log , ERRBUF);
401:
402:
403: WHEN UTL_FILE.WRITE_ERROR THEN
404: UTL_FILE.FCLOSE(l_handle);
405: RETCODE := 2;
406: ERRBUF := 'Write error';
407: fnd_file.put_line(fnd_file.log , ERRBUF);
400: fnd_file.put_line(fnd_file.log , ERRBUF);
401:
402:
403: WHEN UTL_FILE.WRITE_ERROR THEN
404: UTL_FILE.FCLOSE(l_handle);
405: RETCODE := 2;
406: ERRBUF := 'Write error';
407: fnd_file.put_line(fnd_file.log , ERRBUF);
408:
406: ERRBUF := 'Write error';
407: fnd_file.put_line(fnd_file.log , ERRBUF);
408:
409:
410: WHEN UTL_FILE.INTERNAL_ERROR THEN
411: UTL_FILE.FCLOSE(l_handle);
412: RETCODE := 2;
413: ERRBUF := 'Some internal UTL_FILE error';
414: fnd_file.put_line(fnd_file.log , ERRBUF);
407: fnd_file.put_line(fnd_file.log , ERRBUF);
408:
409:
410: WHEN UTL_FILE.INTERNAL_ERROR THEN
411: UTL_FILE.FCLOSE(l_handle);
412: RETCODE := 2;
413: ERRBUF := 'Some internal UTL_FILE error';
414: fnd_file.put_line(fnd_file.log , ERRBUF);
415:
409:
410: WHEN UTL_FILE.INTERNAL_ERROR THEN
411: UTL_FILE.FCLOSE(l_handle);
412: RETCODE := 2;
413: ERRBUF := 'Some internal UTL_FILE error';
414: fnd_file.put_line(fnd_file.log , ERRBUF);
415:
416: WHEN OTHERS THEN
417: UTL_FILE.FCLOSE(l_handle);
413: ERRBUF := 'Some internal UTL_FILE error';
414: fnd_file.put_line(fnd_file.log , ERRBUF);
415:
416: WHEN OTHERS THEN
417: UTL_FILE.FCLOSE(l_handle);
418: RETCODE := 2;
419: ERRBUF := SQLERRM(SQLCODE);
420: fnd_file.put_line(fnd_file.log , ERRBUF);
421: RETURN -1;
773: p_from_date IN VARCHAR2)
774: AS
775:
776: l_sql_string VARCHAR2(2500);
777: l_handle UTL_FILE.FILE_TYPE;
778: l_indicator VARCHAR2(1);
779: l_ret_val NUMBER;
780: l_ref_id VARCHAR2(100);
781: result NUMBER ;
1045:
1046: EXCEPTION
1047:
1048: WHEN NO_DATA_FOUND THEN
1049: UTL_FILE.FCLOSE(l_handle);
1050: RETCODE := result;
1051: ERRBUF := 'Collaboration for Item does not exist';
1052: fnd_file.put_line(fnd_file.log , ERRBUF);
1053:
1051: ERRBUF := 'Collaboration for Item does not exist';
1052: fnd_file.put_line(fnd_file.log , ERRBUF);
1053:
1054: WHEN TOO_MANY_ROWS THEN
1055: UTL_FILE.FCLOSE(l_handle);
1056: RETCODE := result;
1057: ERRBUF := 'More than one Collaboration Exists for the Item';
1058: fnd_file.put_line(fnd_file.log , ERRBUF);
1059:
1060:
1061:
1062:
1063: WHEN OTHERS THEN
1064: UTL_FILE.FCLOSE(l_handle);
1065: RETCODE := result;
1066: ERRBUF := SQLERRM;
1067: fnd_file.put_line(fnd_file.log , ERRBUF);
1068: -- End Function publish_item