103: l_data VARCHAR2(2000);
104: item_cnv_rec GMIGAPI.conv_rec_typ;
105: l_p_dir VARCHAR2(50);
106: l_output_file VARCHAR2(20);
107: l_outfile_handle UTL_FILE.FILE_TYPE;
108: l_input_file VARCHAR2(20);
109: l_infile_handle UTL_FILE.FILE_TYPE;
110: l_line VARCHAR2(200);
111: l_delimiter VARCHAR(1);
105: l_p_dir VARCHAR2(50);
106: l_output_file VARCHAR2(20);
107: l_outfile_handle UTL_FILE.FILE_TYPE;
108: l_input_file VARCHAR2(20);
109: l_infile_handle UTL_FILE.FILE_TYPE;
110: l_line VARCHAR2(200);
111: l_delimiter VARCHAR(1);
112: l_log_dir VARCHAR2(50);
113: l_log_name VARCHAR2(20) :='wrapper';
110: l_line VARCHAR2(200);
111: l_delimiter VARCHAR(1);
112: l_log_dir VARCHAR2(50);
113: l_log_name VARCHAR2(20) :='wrapper';
114: l_log_handle UTL_FILE.FILE_TYPE;
115: l_global_file VARCHAR2(20);
116:
117: l_session_id VARCHAR2(10);
118: l_ic_item_cnv_row ic_item_cnv%ROWTYPE;
139: l_log_dir := p_dir;
140:
141: /* Open The Wrapper File For Output And The Input File for Input. */
142:
143: l_log_handle :=UTL_FILE.FOPEN(l_log_dir, l_log_name, 'w');
144: l_infile_handle :=UTL_FILE.FOPEN(l_p_dir, l_input_file, 'r');
145:
146: /* Loop thru flat file and call Item Lot/Sublot UOM Conversion create API */
147:
140:
141: /* Open The Wrapper File For Output And The Input File for Input. */
142:
143: l_log_handle :=UTL_FILE.FOPEN(l_log_dir, l_log_name, 'w');
144: l_infile_handle :=UTL_FILE.FOPEN(l_p_dir, l_input_file, 'r');
145:
146: /* Loop thru flat file and call Item Lot/Sublot UOM Conversion create API */
147:
148: UTL_FILE.PUT_LINE(l_log_handle, 'Process Started at '
144: l_infile_handle :=UTL_FILE.FOPEN(l_p_dir, l_input_file, 'r');
145:
146: /* Loop thru flat file and call Item Lot/Sublot UOM Conversion create API */
147:
148: UTL_FILE.PUT_LINE(l_log_handle, 'Process Started at '
149: || to_char(SYSDATE,'DD-MON-YY HH:MI:SS'));
150:
151: UTL_FILE.NEW_LINE(l_log_handle);
152: UTL_FILE.PUT_LINE(l_log_handle, 'Input Directory ' || l_p_dir );
147:
148: UTL_FILE.PUT_LINE(l_log_handle, 'Process Started at '
149: || to_char(SYSDATE,'DD-MON-YY HH:MI:SS'));
150:
151: UTL_FILE.NEW_LINE(l_log_handle);
152: UTL_FILE.PUT_LINE(l_log_handle, 'Input Directory ' || l_p_dir );
153: UTL_FILE.PUT_LINE(l_log_handle, 'Input File ' || l_input_file );
154: UTL_FILE.PUT_LINE(l_log_handle, 'Record Type ' || l_delimiter );
155: UTL_FILE.PUT_LINE(l_log_handle, 'Output File ' || l_output_file );
148: UTL_FILE.PUT_LINE(l_log_handle, 'Process Started at '
149: || to_char(SYSDATE,'DD-MON-YY HH:MI:SS'));
150:
151: UTL_FILE.NEW_LINE(l_log_handle);
152: UTL_FILE.PUT_LINE(l_log_handle, 'Input Directory ' || l_p_dir );
153: UTL_FILE.PUT_LINE(l_log_handle, 'Input File ' || l_input_file );
154: UTL_FILE.PUT_LINE(l_log_handle, 'Record Type ' || l_delimiter );
155: UTL_FILE.PUT_LINE(l_log_handle, 'Output File ' || l_output_file );
156:
149: || to_char(SYSDATE,'DD-MON-YY HH:MI:SS'));
150:
151: UTL_FILE.NEW_LINE(l_log_handle);
152: UTL_FILE.PUT_LINE(l_log_handle, 'Input Directory ' || l_p_dir );
153: UTL_FILE.PUT_LINE(l_log_handle, 'Input File ' || l_input_file );
154: UTL_FILE.PUT_LINE(l_log_handle, 'Record Type ' || l_delimiter );
155: UTL_FILE.PUT_LINE(l_log_handle, 'Output File ' || l_output_file );
156:
157: l_outfile_handle :=UTL_FILE.FOPEN(l_p_dir, l_output_file, 'w');
150:
151: UTL_FILE.NEW_LINE(l_log_handle);
152: UTL_FILE.PUT_LINE(l_log_handle, 'Input Directory ' || l_p_dir );
153: UTL_FILE.PUT_LINE(l_log_handle, 'Input File ' || l_input_file );
154: UTL_FILE.PUT_LINE(l_log_handle, 'Record Type ' || l_delimiter );
155: UTL_FILE.PUT_LINE(l_log_handle, 'Output File ' || l_output_file );
156:
157: l_outfile_handle :=UTL_FILE.FOPEN(l_p_dir, l_output_file, 'w');
158:
151: UTL_FILE.NEW_LINE(l_log_handle);
152: UTL_FILE.PUT_LINE(l_log_handle, 'Input Directory ' || l_p_dir );
153: UTL_FILE.PUT_LINE(l_log_handle, 'Input File ' || l_input_file );
154: UTL_FILE.PUT_LINE(l_log_handle, 'Record Type ' || l_delimiter );
155: UTL_FILE.PUT_LINE(l_log_handle, 'Output File ' || l_output_file );
156:
157: l_outfile_handle :=UTL_FILE.FOPEN(l_p_dir, l_output_file, 'w');
158:
159:
153: UTL_FILE.PUT_LINE(l_log_handle, 'Input File ' || l_input_file );
154: UTL_FILE.PUT_LINE(l_log_handle, 'Record Type ' || l_delimiter );
155: UTL_FILE.PUT_LINE(l_log_handle, 'Output File ' || l_output_file );
156:
157: l_outfile_handle :=UTL_FILE.FOPEN(l_p_dir, l_output_file, 'w');
158:
159:
160: LOOP
161: l_record_count :=l_record_count+1;
160: LOOP
161: l_record_count :=l_record_count+1;
162:
163: BEGIN
164: UTL_FILE.GET_LINE(l_infile_handle, l_line);
165: EXCEPTION
166: WHEN NO_DATA_FOUND THEN
167: EXIT;
168: END;
166: WHEN NO_DATA_FOUND THEN
167: EXIT;
168: END;
169:
170: UTL_FILE.NEW_LINE(l_log_handle);
171: UTL_FILE.PUT_LINE(l_log_handle, 'Reading Record ' || l_record_count );
172:
173: item_cnv_rec.item_no :=Get_Field(l_line,l_delimiter,1);
174: item_cnv_rec.lot_no :=Get_Field(l_line,l_delimiter,2);
167: EXIT;
168: END;
169:
170: UTL_FILE.NEW_LINE(l_log_handle);
171: UTL_FILE.PUT_LINE(l_log_handle, 'Reading Record ' || l_record_count );
172:
173: item_cnv_rec.item_no :=Get_Field(l_line,l_delimiter,1);
174: item_cnv_rec.lot_no :=Get_Field(l_line,l_delimiter,2);
175: item_cnv_rec.sublot_no :=Get_Field(l_line,l_delimiter,3);
188: ELSE
189: item_cnv_rec.user_name :=Get_Field(l_line,l_delimiter,7);
190: END IF;
191:
192: UTL_FILE.PUT_LINE(l_log_handle,'item no = '||item_cnv_rec.item_no );
193: UTL_FILE.PUT_LINE(l_log_handle,'lot no = '||item_cnv_rec.lot_no );
194: UTL_FILE.PUT_LINE(l_log_handle,'sublot no = '||item_cnv_rec.sublot_no );
195: UTL_FILE.PUT_LINE(l_log_handle,'from_uom = '||item_cnv_rec.from_uom );
196: UTL_FILE.PUT_LINE(l_log_handle,'to_uom = '||item_cnv_rec.to_uom );
189: item_cnv_rec.user_name :=Get_Field(l_line,l_delimiter,7);
190: END IF;
191:
192: UTL_FILE.PUT_LINE(l_log_handle,'item no = '||item_cnv_rec.item_no );
193: UTL_FILE.PUT_LINE(l_log_handle,'lot no = '||item_cnv_rec.lot_no );
194: UTL_FILE.PUT_LINE(l_log_handle,'sublot no = '||item_cnv_rec.sublot_no );
195: UTL_FILE.PUT_LINE(l_log_handle,'from_uom = '||item_cnv_rec.from_uom );
196: UTL_FILE.PUT_LINE(l_log_handle,'to_uom = '||item_cnv_rec.to_uom );
197: UTL_FILE.PUT_LINE(l_log_handle,'type_factor= '||item_cnv_rec.type_factor );
190: END IF;
191:
192: UTL_FILE.PUT_LINE(l_log_handle,'item no = '||item_cnv_rec.item_no );
193: UTL_FILE.PUT_LINE(l_log_handle,'lot no = '||item_cnv_rec.lot_no );
194: UTL_FILE.PUT_LINE(l_log_handle,'sublot no = '||item_cnv_rec.sublot_no );
195: UTL_FILE.PUT_LINE(l_log_handle,'from_uom = '||item_cnv_rec.from_uom );
196: UTL_FILE.PUT_LINE(l_log_handle,'to_uom = '||item_cnv_rec.to_uom );
197: UTL_FILE.PUT_LINE(l_log_handle,'type_factor= '||item_cnv_rec.type_factor );
198: UTL_FILE.PUT_LINE(l_log_handle,'op Code = '||item_cnv_rec.user_name );
191:
192: UTL_FILE.PUT_LINE(l_log_handle,'item no = '||item_cnv_rec.item_no );
193: UTL_FILE.PUT_LINE(l_log_handle,'lot no = '||item_cnv_rec.lot_no );
194: UTL_FILE.PUT_LINE(l_log_handle,'sublot no = '||item_cnv_rec.sublot_no );
195: UTL_FILE.PUT_LINE(l_log_handle,'from_uom = '||item_cnv_rec.from_uom );
196: UTL_FILE.PUT_LINE(l_log_handle,'to_uom = '||item_cnv_rec.to_uom );
197: UTL_FILE.PUT_LINE(l_log_handle,'type_factor= '||item_cnv_rec.type_factor );
198: UTL_FILE.PUT_LINE(l_log_handle,'op Code = '||item_cnv_rec.user_name );
199:
192: UTL_FILE.PUT_LINE(l_log_handle,'item no = '||item_cnv_rec.item_no );
193: UTL_FILE.PUT_LINE(l_log_handle,'lot no = '||item_cnv_rec.lot_no );
194: UTL_FILE.PUT_LINE(l_log_handle,'sublot no = '||item_cnv_rec.sublot_no );
195: UTL_FILE.PUT_LINE(l_log_handle,'from_uom = '||item_cnv_rec.from_uom );
196: UTL_FILE.PUT_LINE(l_log_handle,'to_uom = '||item_cnv_rec.to_uom );
197: UTL_FILE.PUT_LINE(l_log_handle,'type_factor= '||item_cnv_rec.type_factor );
198: UTL_FILE.PUT_LINE(l_log_handle,'op Code = '||item_cnv_rec.user_name );
199:
200: /* dbms_output.put_line('Calling creation routine'); */
193: UTL_FILE.PUT_LINE(l_log_handle,'lot no = '||item_cnv_rec.lot_no );
194: UTL_FILE.PUT_LINE(l_log_handle,'sublot no = '||item_cnv_rec.sublot_no );
195: UTL_FILE.PUT_LINE(l_log_handle,'from_uom = '||item_cnv_rec.from_uom );
196: UTL_FILE.PUT_LINE(l_log_handle,'to_uom = '||item_cnv_rec.to_uom );
197: UTL_FILE.PUT_LINE(l_log_handle,'type_factor= '||item_cnv_rec.type_factor );
198: UTL_FILE.PUT_LINE(l_log_handle,'op Code = '||item_cnv_rec.user_name );
199:
200: /* dbms_output.put_line('Calling creation routine'); */
201:
194: UTL_FILE.PUT_LINE(l_log_handle,'sublot no = '||item_cnv_rec.sublot_no );
195: UTL_FILE.PUT_LINE(l_log_handle,'from_uom = '||item_cnv_rec.from_uom );
196: UTL_FILE.PUT_LINE(l_log_handle,'to_uom = '||item_cnv_rec.to_uom );
197: UTL_FILE.PUT_LINE(l_log_handle,'type_factor= '||item_cnv_rec.type_factor );
198: UTL_FILE.PUT_LINE(l_log_handle,'op Code = '||item_cnv_rec.user_name );
199:
200: /* dbms_output.put_line('Calling creation routine'); */
201:
202: GMIPAPI.Create_Item_Lot_Conv
223: p_msg_index_out => l_dummy_cnt);
224:
225: /* dbms_output.put_line('Message ' || l_data ); */
226:
227: UTL_FILE.PUT_LINE(l_outfile_handle, 'Record = ' ||l_record_count );
228: UTL_FILE.PUT_LINE(l_outfile_handle, l_data);
229: UTL_FILE.NEW_LINE(l_outfile_handle);
230:
231: IF l_status = 'E' OR
224:
225: /* dbms_output.put_line('Message ' || l_data ); */
226:
227: UTL_FILE.PUT_LINE(l_outfile_handle, 'Record = ' ||l_record_count );
228: UTL_FILE.PUT_LINE(l_outfile_handle, l_data);
229: UTL_FILE.NEW_LINE(l_outfile_handle);
230:
231: IF l_status = 'E' OR
232: l_status = 'U'
225: /* dbms_output.put_line('Message ' || l_data ); */
226:
227: UTL_FILE.PUT_LINE(l_outfile_handle, 'Record = ' ||l_record_count );
228: UTL_FILE.PUT_LINE(l_outfile_handle, l_data);
229: UTL_FILE.NEW_LINE(l_outfile_handle);
230:
231: IF l_status = 'E' OR
232: l_status = 'U'
233: THEN
233: THEN
234: l_data := CONCAT('ERROR ',l_data);
235: END IF;
236:
237: UTL_FILE.PUT_LINE(l_log_handle, l_data);
238:
239: /* Update error status */
240: IF (l_status = 'U')
241: THEN
257:
258: END IF;
259:
260: END LOOP;
261: UTL_FILE.NEW_LINE(l_log_handle);
262: UTL_FILE.PUT_LINE(l_log_handle, 'Process Completed at '
263: || to_char(SYSDATE,'DD-MON-YY HH:MI:SS'));
264: /* Check if any messages generated. If so then decode and */
265: /* output to error message flat file */
258: END IF;
259:
260: END LOOP;
261: UTL_FILE.NEW_LINE(l_log_handle);
262: UTL_FILE.PUT_LINE(l_log_handle, 'Process Completed at '
263: || to_char(SYSDATE,'DD-MON-YY HH:MI:SS'));
264: /* Check if any messages generated. If so then decode and */
265: /* output to error message flat file */
266:
263: || to_char(SYSDATE,'DD-MON-YY HH:MI:SS'));
264: /* Check if any messages generated. If so then decode and */
265: /* output to error message flat file */
266:
267: UTL_FILE.FCLOSE_ALL;
268:
269: RETURN l_return_status;
270:
271: EXCEPTION
268:
269: RETURN l_return_status;
270:
271: EXCEPTION
272: WHEN UTL_FILE.INVALID_OPERATION THEN
273: /* dbms_output.put_line('Invalid Operation For '|| l_global_file); */
274: UTL_FILE.FCLOSE_ALL;
275: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
276:
270:
271: EXCEPTION
272: WHEN UTL_FILE.INVALID_OPERATION THEN
273: /* dbms_output.put_line('Invalid Operation For '|| l_global_file); */
274: UTL_FILE.FCLOSE_ALL;
275: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
276:
277: WHEN UTL_FILE.INVALID_PATH THEN
278: /* dbms_output.put_line('Invalid Path For '|| l_global_file); */
273: /* dbms_output.put_line('Invalid Operation For '|| l_global_file); */
274: UTL_FILE.FCLOSE_ALL;
275: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
276:
277: WHEN UTL_FILE.INVALID_PATH THEN
278: /* dbms_output.put_line('Invalid Path For '|| l_global_file); */
279: UTL_FILE.FCLOSE_ALL;
280: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
281:
275: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
276:
277: WHEN UTL_FILE.INVALID_PATH THEN
278: /* dbms_output.put_line('Invalid Path For '|| l_global_file); */
279: UTL_FILE.FCLOSE_ALL;
280: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
281:
282: WHEN UTL_FILE.INVALID_MODE THEN
283: /* dbms_output.put_line('Invalid Mode For '|| l_global_file); */
278: /* dbms_output.put_line('Invalid Path For '|| l_global_file); */
279: UTL_FILE.FCLOSE_ALL;
280: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
281:
282: WHEN UTL_FILE.INVALID_MODE THEN
283: /* dbms_output.put_line('Invalid Mode For '|| l_global_file); */
284: UTL_FILE.FCLOSE_ALL;
285: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
286:
280: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
281:
282: WHEN UTL_FILE.INVALID_MODE THEN
283: /* dbms_output.put_line('Invalid Mode For '|| l_global_file); */
284: UTL_FILE.FCLOSE_ALL;
285: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
286:
287: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
288: /* dbms_output.put_line('Invalid File Handle '|| l_global_file); */
283: /* dbms_output.put_line('Invalid Mode For '|| l_global_file); */
284: UTL_FILE.FCLOSE_ALL;
285: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
286:
287: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
288: /* dbms_output.put_line('Invalid File Handle '|| l_global_file); */
289: UTL_FILE.FCLOSE_ALL;
290: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
291:
285: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
286:
287: WHEN UTL_FILE.INVALID_FILEHANDLE THEN
288: /* dbms_output.put_line('Invalid File Handle '|| l_global_file); */
289: UTL_FILE.FCLOSE_ALL;
290: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
291:
292: WHEN UTL_FILE.WRITE_ERROR THEN
293: /* dbms_output.put_line('Invalid Write Error '|| l_global_file);*/
288: /* dbms_output.put_line('Invalid File Handle '|| l_global_file); */
289: UTL_FILE.FCLOSE_ALL;
290: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
291:
292: WHEN UTL_FILE.WRITE_ERROR THEN
293: /* dbms_output.put_line('Invalid Write Error '|| l_global_file);*/
294: UTL_FILE.FCLOSE_ALL;
295: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
296:
290: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
291:
292: WHEN UTL_FILE.WRITE_ERROR THEN
293: /* dbms_output.put_line('Invalid Write Error '|| l_global_file);*/
294: UTL_FILE.FCLOSE_ALL;
295: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
296:
297: WHEN UTL_FILE.READ_ERROR THEN
298: /* dbms_output.put_line('Invalid Read Error '|| l_global_file); */
293: /* dbms_output.put_line('Invalid Write Error '|| l_global_file);*/
294: UTL_FILE.FCLOSE_ALL;
295: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
296:
297: WHEN UTL_FILE.READ_ERROR THEN
298: /* dbms_output.put_line('Invalid Read Error '|| l_global_file); */
299: UTL_FILE.FCLOSE_ALL;
300: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
301:
295: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
296:
297: WHEN UTL_FILE.READ_ERROR THEN
298: /* dbms_output.put_line('Invalid Read Error '|| l_global_file); */
299: UTL_FILE.FCLOSE_ALL;
300: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
301:
302: WHEN UTL_FILE.INTERNAL_ERROR THEN
303: /* dbms_output.put_line('Internal Error'); */
298: /* dbms_output.put_line('Invalid Read Error '|| l_global_file); */
299: UTL_FILE.FCLOSE_ALL;
300: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
301:
302: WHEN UTL_FILE.INTERNAL_ERROR THEN
303: /* dbms_output.put_line('Internal Error'); */
304: UTL_FILE.FCLOSE_ALL;
305: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
306:
300: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
301:
302: WHEN UTL_FILE.INTERNAL_ERROR THEN
303: /* dbms_output.put_line('Internal Error'); */
304: UTL_FILE.FCLOSE_ALL;
305: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
306:
307: WHEN OTHERS THEN
308: /* dbms_output.put_line('Other Error'); */
305: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
306:
307: WHEN OTHERS THEN
308: /* dbms_output.put_line('Other Error'); */
309: UTL_FILE.FCLOSE_ALL;
310: RETURN FND_API.G_RET_STS_UNEXP_ERROR;
311:
312: END Create_Conv;
313: