eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.GMI_LOTS_WRP

Source


1 PACKAGE BODY GMI_LOTS_WRP AS
2 /*  $Header: GMIPLOWB.pls 115.14 2003/10/22 18:37:18 jsrivast gmigapib.pls $
3  +==========================================================================+
4  | PROCEDURE NAME                                                           |
5  |    Create_Lot                                                            |
6  |                                                                          |
7  | TYPE                                                                     |
8  |    Public                                                                |
9  |                                                                          |
10  | USAGE                                                                    |
11  |    Create an Item Lot                                                    |
12  |                                                                          |
13  | DESCRIPTION                                                              |
14  |    This is a PL/SQL wrapper procedure to call the Create_Lot             |
15  |    API wrapper function                                                  |
16  |                                                                          |
17  | PARAMETERS                                                               |
18  |    p_dir              IN VARCHAR2         - Working directory for input  |
19  |                                             and output files.            |
20  |    p_input_file       IN VARCHAR2         - Name of input file           |
21  |    p_output_file      IN VARCHAR2         - Name of output file          |
22  |    p_delimiter        IN VARCHAR2         - Delimiter character          |
23  |                                                                          |
24  | RETURNS                                                                  |
25  |    None                                                                  |
26  |                                                                          |
27  | HISTORY                                                                  |
28  |   Joe DiIorio 12/30/2002 Bug#2729049 11.5.1J plus                        |
29  |   Correct delimiter sequence.  Inactive field was ignored so from there  |
30  |   on all fields were incorrect.                                          |
31  +==========================================================================+
32   Api end of comments
33 */
34 PROCEDURE Create_Lot
35 ( p_dir          IN VARCHAR2
36 , p_input_file   IN VARCHAR2
37 , p_output_file  IN VARCHAR2
38 , p_delimiter    IN VARCHAR2
39 )
40 IS
41 
42 l_return_status  VARCHAR2(1);
43 
44 BEGIN
45 
46 l_return_status  :=Create_Lot( p_dir
47 			      , p_input_file
48 			      , p_output_file
49 			      , p_delimiter
50 			      );
51 
52 End Create_Lot;
53 
54 /* +==========================================================================+
55  | FUNCTION NAME                                                            |
56  |    Create_Lot                                                            |
57  |                                                                          |
58  | TYPE                                                                     |
59  |    Public                                                                |
60  |                                                                          |
61  | USAGE                                                                    |
62  |    Create a Lot/Sublot                                                   |
63  |                                                                          |
64  | DESCRIPTION                                                              |
65  |    This is a PL/SQL wrapper function to call the OPM Lot/Sublot Create   |
66  |    API                                                                   |
67  |    It reads item data from a flat file and outputs any error             |
68  |    messages to a second flat file. It also generates a Status            |
69  |    called wrapper<session_id>.log in the /tmp directory.                 |
70  |                                                                          |
71  | PARAMETERS                                                               |
72  |    p_dir              IN VARCHAR2         - Working directory for input  |
73  |                                             and output files.            |
74  |    p_input_file       IN VARCHAR2         - Name of input file           |
75  |    p_output_file      IN VARCHAR2         - Name of output file          |
76  |    p_delimiter        IN VARCHAR2         - Delimiter character          |
77  |                                                                          |
78  | RETURNS                                                                  |
79  |    VARCHAR2 - 'S' All records processed successfully                     |
80  |               'E' 1 or more records errored                              |
81  |               'U' 1 or more record unexpected error                      |
82  |                                                                          |
83  | HISTORY                                                                  |
84  |                                                                          |
85  |    24-DEC-2001  K. RajaSekhar Reddy BUG#2158123                          |
86  |                          Modified the code to create the Retest Date,    |
87  |                          Expire Date and Expaction Dates correctly.      |
88  +==========================================================================+
89   Api end of comments
90 */
91 FUNCTION Create_Lot
92 ( p_dir          IN VARCHAR2
93 , p_input_file   IN VARCHAR2
94 , p_output_file  IN VARCHAR2
95 , p_delimiter    IN VARCHAR2
96 )
97 RETURN VARCHAR2
98 IS
99 
100 /*
101   Local variables
102 */
103 
104 l_status             VARCHAR2(1);
105 l_return_status      VARCHAR2(1)  :=FND_API.G_RET_STS_SUCCESS;
106 l_count              NUMBER  ;
107 l_loop_cnt           NUMBER  :=0;
108 l_dummy_cnt          NUMBER  :=0;
109 l_record_count       NUMBER  :=0;
110 l_data               VARCHAR2(2000);
111 lot_rec              GMIGAPI.lot_rec_typ;
112 l_p_dir              VARCHAR2(50);
113 l_output_file        VARCHAR2(20);
114 l_outfile_handle     UTL_FILE.FILE_TYPE;
115 l_input_file         VARCHAR2(20);
116 l_infile_handle      UTL_FILE.FILE_TYPE;
117 l_line               VARCHAR2(200);
118 l_delimiter          VARCHAR(1);
119 l_log_dir            VARCHAR2(50);
120 l_log_name           VARCHAR2(20)  :='wrapper';
121 l_log_handle         UTL_FILE.FILE_TYPE;
122 l_global_file        VARCHAR2(20);
123 
124 l_session_id         VARCHAR2(10);
125 l_ic_lots_mst_row    ic_lots_mst%ROWTYPE;
126 l_ic_lots_cpg_row    ic_lots_cpg%ROWTYPE;
127 BEGIN
128 
129 /*  Enable The Buffer
130   DBMS_OUTPUT.ENABLE(1000000);
131 */
132 
133 l_p_dir              :=p_dir;
134 l_input_file         :=p_input_file;
135 l_output_file        :=p_output_file;
136 l_delimiter          :=p_delimiter;
137 l_global_file        :=l_input_file;
138 
139 /*
140   Obtain The SessionId To Append To wrapper File Name.
141 */
142 
143 l_session_id := USERENV('sessionid');
144 
145 l_log_name  := CONCAT(l_log_name,l_session_id);
146 l_log_name  := CONCAT(l_log_name,'.log');
147 /*
148   Directory is now the same same as for the out file
149 */
150 l_log_dir   := p_dir;
151 
152 /*
153   Open The Wrapper File For Output And The Input File for Input.
154 */
155 
156 l_log_handle      :=UTL_FILE.FOPEN(l_log_dir, l_log_name, 'w');
157 l_infile_handle   :=UTL_FILE.FOPEN(l_p_dir, l_input_file, 'r');
158 
159 /*
160   Loop thru flat file and call Inventory Quantities API
161 */
162 
163 /*  dbms_output.put_line('Start Processing');   */
164 UTL_FILE.PUT_LINE(l_log_handle, 'Process Started at '
165 || to_char(SYSDATE,'DD-MON-YY HH:MI:SS'));
166 
167 UTL_FILE.NEW_LINE(l_log_handle);
168 UTL_FILE.PUT_LINE(l_log_handle, 'Input Directory  ' || l_p_dir );
169 UTL_FILE.PUT_LINE(l_log_handle, 'Input File       ' || l_input_file );
170 UTL_FILE.PUT_LINE(l_log_handle, 'Record Type      ' || l_delimiter );
171 UTL_FILE.PUT_LINE(l_log_handle, 'Output File      ' || l_output_file );
172 
173 l_outfile_handle  :=UTL_FILE.FOPEN(l_p_dir, l_output_file, 'w');
174 
175 LOOP
176 l_record_count    :=l_record_count+1;
177 
178   BEGIN
179   UTL_FILE.GET_LINE(l_infile_handle, l_line);
180   EXCEPTION
181     WHEN NO_DATA_FOUND THEN
182 EXIT;
183   END;
184 
185 UTL_FILE.NEW_LINE(l_log_handle);
186 UTL_FILE.PUT_LINE(l_log_handle, 'Reading Record ' || l_record_count );
187 
188   lot_rec.item_no         :=Get_Field(l_line,l_delimiter,1);
189   lot_rec.lot_no          :=Get_Field(l_line,l_delimiter,2);
190   lot_rec.sublot_no       :=Get_Field(l_line,l_delimiter,3);
191   lot_rec.lot_desc        :=Get_Field(l_line,l_delimiter,4);
192   lot_rec.qc_grade        :=Get_Field(l_line,l_delimiter,5);
193   lot_rec.expaction_code  :=Get_Field(l_line,l_delimiter,6);
194   --BEGIN BUG#2158123 12/21/2001 RajaSekhar
195   --Modified the IF condition from Null  to NOT NULL and commented the
196   --two lines.
197   IF (Get_Field(l_line,l_delimiter,7) IS NOT NULL)
198   THEN
199     --lot_rec.expaction_date :=GMA_GLOBAL_GRP.SY$MAX_DATE;
200     --  ELSE
201     lot_rec.expaction_date :=TO_DATE(
202 			     Get_Field(l_line,l_delimiter,7),'DDMMYYYY');
203   END IF;
204   --END BUG#2158123
205   IF (Get_Field(l_line,l_delimiter,8) IS NULL)
206   THEN
207     lot_rec.lot_created   :=SYSDATE;
208   ELSE
209     lot_rec.lot_created   :=TO_DATE(
210 			     Get_Field(l_line,l_delimiter,8),'DDMMYYYY');
211   END IF;
212   --BEGIN BUG#2158123 12/21/2001 RajaSekhar
213   --Modified the IF conditions from Null  to NOT NULL and commented the
214   --four lines.
215   IF (Get_Field(l_line,l_delimiter,9) IS NOT NULL)
216   THEN
217   --lot_rec.expire_date   :=GMA_GLOBAL_GRP.SY$MAX_DATE;
218   --ELSE
219     lot_rec.expire_date   :=TO_DATE(
220 			     Get_Field(l_line,l_delimiter,9),'DDMMYYYY');
221   END IF;
222 
223   IF (Get_Field(l_line,l_delimiter,10) IS NOT NULL)
224   THEN
225   --lot_rec.retest_date   :=GMA_GLOBAL_GRP.SY$MAX_DATE;
226   -- ELSE
227     lot_rec.retest_date   :=TO_DATE(
228 			     Get_Field(l_line,l_delimiter,10),'DDMMYYYY');
229   END IF;
230   --END BUG#2158123
231   IF (Get_Field(l_line,l_delimiter,11) IS NULL)
232   THEN
233     lot_rec.strength      :=100;
234   ELSE
235     lot_rec.strength      :=TO_NUMBER(Get_Field(l_line,l_delimiter,11));
236   END IF;
237   IF (Get_Field(l_line,l_delimiter,12) IS NULL) THEN
238      lot_rec.inactive_ind    :=0;
239   ELSE
240      lot_rec.inactive_ind    := TO_NUMBER(Get_Field(l_line,l_delimiter,12));
241   END IF;
242   IF (Get_Field(l_line,l_delimiter,13) IS NULL)
243   THEN
244     lot_rec.origination_type     :=0;
245   ELSE
246     lot_rec.origination_type     :=TO_NUMBER(Get_Field(l_line,l_delimiter,13));
247   END IF;
248   lot_rec.shipvendor_no   :=Get_Field(l_line,l_delimiter,14);
249   lot_rec.vendor_lot_no   :=Get_Field(l_line,l_delimiter,15);
250   IF (Get_Field(l_line,l_delimiter,16) IS NULL)
251   THEN
252     lot_rec.ic_matr_date   :=GMA_GLOBAL_GRP.SY$MAX_DATE;
253   ELSE
254     lot_rec.ic_matr_date   :=TO_DATE(
255 			     Get_Field(l_line,l_delimiter,16),'DDMMYYYY');
256   END IF;
257   /* Jalaj Srivastava Bug 3158806
258      Null hold date is allowed */
259   IF (Get_Field(l_line,l_delimiter,17) IS NOT NULL)
260   THEN
261     lot_rec.ic_hold_date   :=TO_DATE(
262 			     Get_Field(l_line,l_delimiter,17),'DDMMYYYY');
263   END IF;
264   IF (Get_Field(l_line,l_delimiter,18) IS NULL)
265   THEN
266     lot_rec.user_name       :='OPM';
267   ELSE
268     lot_rec.user_name       :=Get_Field(l_line,l_delimiter,18);
269   END IF;
270   lot_rec.attribute1      :=Get_Field(l_line,l_delimiter,19);
271   lot_rec.attribute2      :=Get_Field(l_line,l_delimiter,20);
272   lot_rec.attribute3      :=Get_Field(l_line,l_delimiter,21);
273   lot_rec.attribute4      :=Get_Field(l_line,l_delimiter,22);
274   lot_rec.attribute5      :=Get_Field(l_line,l_delimiter,23);
275   lot_rec.attribute6      :=Get_Field(l_line,l_delimiter,24);
276   lot_rec.attribute7      :=Get_Field(l_line,l_delimiter,25);
277   lot_rec.attribute8      :=Get_Field(l_line,l_delimiter,26);
278   lot_rec.attribute9      :=Get_Field(l_line,l_delimiter,27);
279   lot_rec.attribute10     :=Get_Field(l_line,l_delimiter,28);
280   lot_rec.attribute11     :=Get_Field(l_line,l_delimiter,29);
281   lot_rec.attribute12     :=Get_Field(l_line,l_delimiter,30);
282   lot_rec.attribute13     :=Get_Field(l_line,l_delimiter,31);
283   lot_rec.attribute14     :=Get_Field(l_line,l_delimiter,32);
284   lot_rec.attribute15     :=Get_Field(l_line,l_delimiter,33);
285   lot_rec.attribute16     :=Get_Field(l_line,l_delimiter,34);
286   lot_rec.attribute17     :=Get_Field(l_line,l_delimiter,35);
287   lot_rec.attribute18     :=Get_Field(l_line,l_delimiter,36);
288   lot_rec.attribute19     :=Get_Field(l_line,l_delimiter,37);
289   lot_rec.attribute20     :=Get_Field(l_line,l_delimiter,38);
290   lot_rec.attribute21     :=Get_Field(l_line,l_delimiter,39);
291   lot_rec.attribute22     :=Get_Field(l_line,l_delimiter,40);
292   lot_rec.attribute23     :=Get_Field(l_line,l_delimiter,41);
293   lot_rec.attribute24     :=Get_Field(l_line,l_delimiter,42);
294   lot_rec.attribute25     :=Get_Field(l_line,l_delimiter,43);
295   lot_rec.attribute26     :=Get_Field(l_line,l_delimiter,44);
296   lot_rec.attribute27     :=Get_Field(l_line,l_delimiter,45);
297   lot_rec.attribute28     :=Get_Field(l_line,l_delimiter,46);
298   lot_rec.attribute29     :=Get_Field(l_line,l_delimiter,47);
299   lot_rec.attribute30     :=Get_Field(l_line,l_delimiter,48);
300   lot_rec.attribute_category  :=Get_Field(l_line,l_delimiter,49);
301 
302 UTL_FILE.PUT_LINE(l_log_handle,'item no        = '||lot_rec.item_no);
303 UTL_FILE.PUT_LINE(l_log_handle,'lot_no         = '||lot_rec.lot_no);
304 UTL_FILE.PUT_LINE(l_log_handle,'sublot_no      = '||lot_rec.sublot_no);
305 UTL_FILE.PUT_LINE(l_log_handle,'lot_desc       = '||lot_rec.lot_desc);
306 UTL_FILE.PUT_LINE(l_log_handle,'qc_grade       = '||lot_rec.qc_grade);
307 UTL_FILE.PUT_LINE(l_log_handle,'expaction_code = '||
308 				lot_rec.expaction_code);
309 UTL_FILE.PUT_LINE(l_log_handle,'expaction_date = '||
310 				lot_rec.expaction_date);
311 UTL_FILE.PUT_LINE(l_log_handle,'lot_created    = '||
312 				lot_rec.lot_created);
313 UTL_FILE.PUT_LINE(l_log_handle,'expire_date    = '||
314 				lot_rec.expire_date);
315 UTL_FILE.PUT_LINE(l_log_handle,'retest_date    = '||
316 				lot_rec.retest_date);
317 UTL_FILE.PUT_LINE(l_log_handle,'strength       = '||lot_rec.strength);
318 UTL_FILE.PUT_LINE(l_log_handle,'inactive_ind   = '||lot_rec.inactive_ind);
319 UTL_FILE.PUT_LINE(l_log_handle,'origination_type = '||
320 				lot_rec.origination_type);
321 UTL_FILE.PUT_LINE(l_log_handle,'shipvendor_no  = '||lot_rec.shipvendor_no);
322 UTL_FILE.PUT_LINE(l_log_handle,'vendor_lot_no  = '||lot_rec.vendor_lot_no);
323 UTL_FILE.PUT_LINE(l_log_handle,'ic_matr_date   = '||lot_rec.ic_matr_date);
324 UTL_FILE.PUT_LINE(l_log_handle,'ic_hold_date   = '||lot_rec.ic_hold_date);
325 UTL_FILE.PUT_LINE(l_log_handle,'user name        = '||lot_rec.user_name );
326 UTL_FILE.PUT_LINE(l_log_handle,'Attribute1        = '||                                                         lot_rec.attribute1 );
327 UTL_FILE.PUT_LINE(l_log_handle,'Attribute2        = '||                                                         lot_rec.attribute2 );
328 UTL_FILE.PUT_LINE(l_log_handle,'Attribute3        = '||                                                         lot_rec.attribute3 );
329 UTL_FILE.PUT_LINE(l_log_handle,'Attribute4        = '||                                                         lot_rec.attribute4 );
330 UTL_FILE.PUT_LINE(l_log_handle,'Attribute5        = '||                                                         lot_rec.attribute5 );
331 UTL_FILE.PUT_LINE(l_log_handle,'Attribute6        = '||                                                         lot_rec.attribute6 );
332 UTL_FILE.PUT_LINE(l_log_handle,'Attribute7        = '||                                                         lot_rec.attribute7 );
333 UTL_FILE.PUT_LINE(l_log_handle,'Attribute8        = '||                                                         lot_rec.attribute8 );
334 UTL_FILE.PUT_LINE(l_log_handle,'Attribute9        = '||                                                         lot_rec.attribute9 );
335 UTL_FILE.PUT_LINE(l_log_handle,'Attribute10        = '||                                                         lot_rec.attribute10 );
336 UTL_FILE.PUT_LINE(l_log_handle,'Attribute11        = '||                                                         lot_rec.attribute11 );
337 UTL_FILE.PUT_LINE(l_log_handle,'Attribute12        = '||                                                         lot_rec.attribute12 );
338 UTL_FILE.PUT_LINE(l_log_handle,'Attribute13        = '||                                                         lot_rec.attribute13 );
339 UTL_FILE.PUT_LINE(l_log_handle,'Attribute14        = '||                                                         lot_rec.attribute14 );
340 UTL_FILE.PUT_LINE(l_log_handle,'Attribute15        = '||                                                         lot_rec.attribute15 );
341 UTL_FILE.PUT_LINE(l_log_handle,'Attribute16        = '||                                                         lot_rec.attribute16 );
342 UTL_FILE.PUT_LINE(l_log_handle,'Attribute17        = '||                                                         lot_rec.attribute17 );
343 UTL_FILE.PUT_LINE(l_log_handle,'Attribute18        = '||                                                         lot_rec.attribute18 );
344 UTL_FILE.PUT_LINE(l_log_handle,'Attribute19        = '||                                                         lot_rec.attribute19 );
345 UTL_FILE.PUT_LINE(l_log_handle,'Attribute20        = '||                                                         lot_rec.attribute20 );
346 UTL_FILE.PUT_LINE(l_log_handle,'Attribute21        = '||                                                         lot_rec.attribute21 );
347 UTL_FILE.PUT_LINE(l_log_handle,'Attribute22        = '||                                                         lot_rec.attribute22 );
348 UTL_FILE.PUT_LINE(l_log_handle,'Attribute23        = '||                                                         lot_rec.attribute23 );
349 UTL_FILE.PUT_LINE(l_log_handle,'Attribute24        = '||                                                         lot_rec.attribute24 );
350 UTL_FILE.PUT_LINE(l_log_handle,'Attribute25        = '||                                                         lot_rec.attribute25 );
351 UTL_FILE.PUT_LINE(l_log_handle,'Attribute26        = '||                                                         lot_rec.attribute26 );
352 UTL_FILE.PUT_LINE(l_log_handle,'Attribute27        = '||                                                         lot_rec.attribute27 );
353 UTL_FILE.PUT_LINE(l_log_handle,'Attribute28        = '||                                                         lot_rec.attribute28 );
354 UTL_FILE.PUT_LINE(l_log_handle,'Attribute29        = '||                                                         lot_rec.attribute29 );
355 UTL_FILE.PUT_LINE(l_log_handle,'Attribute30        = '||                                                         lot_rec.attribute30 );
356 UTL_FILE.PUT_LINE(l_log_handle,'Attribute_Category = '||                                                         lot_rec.attribute_category );
357 
358 GMIPAPI.Create_Lot
359 ( p_api_version    => 3.0
360 , p_init_msg_list  => FND_API.G_TRUE
361 , p_commit         => FND_API.G_TRUE
362 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
363 , p_lot_rec        =>lot_rec
364 , x_ic_lots_mst_row => l_ic_lots_mst_row
365 , x_ic_lots_cpg_row => l_ic_lots_cpg_row
366 , x_return_status  =>l_status
367 , x_msg_count      =>l_count
368 , x_msg_data       =>l_data
369 );
370 
371 IF l_count > 0
372 THEN
373   l_loop_cnt  :=1;
374   LOOP
375 
376   FND_MSG_PUB.Get(
377     p_msg_index     => l_loop_cnt,
378     p_data          => l_data,
379     p_encoded       => FND_API.G_FALSE,
380     p_msg_index_out => l_dummy_cnt);
381 
382   /*  dbms_output.put_line('Message ' || l_data );  */
383 
384   UTL_FILE.PUT_LINE(l_outfile_handle, 'Record = ' ||l_record_count );
385   UTL_FILE.PUT_LINE(l_outfile_handle, l_data);
386   UTL_FILE.NEW_LINE(l_outfile_handle);
387 
388   IF l_status = 'E' OR
389      l_status = 'U'
390   THEN
391     l_data    := CONCAT('ERROR ',l_data);
392   END IF;
393 
394   UTL_FILE.PUT_LINE(l_log_handle, l_data);
395 
396   /*  Update error status  */
397     IF (l_status = 'U')
398     THEN
399       l_return_status  :=l_status;
400     ELSIF (l_status = 'E' and l_return_status <> 'U')
401     THEN
402       l_return_status  :=l_status;
403     ELSE
404       l_return_status  :=l_status;
405     END IF;
406 
407   l_loop_cnt  := l_loop_cnt + 1;
408   IF l_loop_cnt > l_count
409   THEN
410     EXIT;
411   END IF;
412 
413   END LOOP;
414 
415 END IF;
416 
417 END LOOP;
418   UTL_FILE.NEW_LINE(l_log_handle);
419   UTL_FILE.PUT_LINE(l_log_handle, 'Process Completed at '
420   || to_char(SYSDATE,'DD-MON-YY HH:MI:SS'));
421 /*
422   Check if any messages generated. If so then decode and
423   output to error message flat file
424 */
425 
426 UTL_FILE.FCLOSE_ALL;
427 
428 RETURN l_return_status;
429 
430 EXCEPTION
431 WHEN UTL_FILE.INVALID_OPERATION THEN
432    /*  dbms_output.put_line('Invalid Operation For '|| l_global_file);  */
433    UTL_FILE.FCLOSE_ALL;
434 
435 WHEN UTL_FILE.INVALID_PATH THEN
436    /*  dbms_output.put_line('Invalid Path For      '|| l_global_file);  */
437    UTL_FILE.FCLOSE_ALL;
438 
439 WHEN UTL_FILE.INVALID_MODE THEN
440    /*  dbms_output.put_line('Invalid Mode For      '|| l_global_file); */
441    UTL_FILE.FCLOSE_ALL;
442 
443 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
444    /*  dbms_output.put_line('Invalid File Handle   '|| l_global_file);  */
445    UTL_FILE.FCLOSE_ALL;
446 
447 WHEN UTL_FILE.WRITE_ERROR THEN
448    /*  dbms_output.put_line('Invalid Write Error   '|| l_global_file);  */
449    UTL_FILE.FCLOSE_ALL;
450 
451 WHEN UTL_FILE.READ_ERROR THEN
452    /*  dbms_output.put_line('Invalid Read  Error   '|| l_global_file);  */
453    UTL_FILE.FCLOSE_ALL;
454 
455 WHEN UTL_FILE.INTERNAL_ERROR THEN
456    /*  dbms_output.put_line('Internal Error');   */
457    UTL_FILE.FCLOSE_ALL;
458 
459 WHEN OTHERS THEN
460    /*  dbms_output.put_line('Other Error');  */
461    UTL_FILE.FCLOSE_ALL;
462 
463 END Create_Lot;
464 
465 /* +==========================================================================+
466  | FUNCTION NAME                                                            |
467  |    Get_Field                                                             |
468  |                                                                          |
469  | TYPE                                                                     |
470  |    Public                                                                |
471  |                                                                          |
472  | USAGE                                                                    |
473  |    Get value of field n from a delimited line of ASCII data              |
474  |                                                                          |
475  | DESCRIPTION                                                              |
476  |    This utility function will return the value of a field from           |
477  |    a delimited line of ASCII text                                        |
478  |                                                                          |
479  | PARAMETERS                                                               |
480  |    p_line             IN VARCHAR2         - line of data                 |
481  |    p_delimiter        IN VARCHAR2         - Delimiter character          |
482  |    p_field_no         IN NUMBER           - Field occurance to be        |
483  |                                             returned                     |
484  |                                                                          |
485  | RETURNS                                                                  |
486  |    VARCHAR2                               - Value of field               |
487  |                                                                          |
488  | HISTORY                                                                  |
489  |                                                                          |
490  +==========================================================================+
491   Api end of comments
492 */
493 FUNCTION Get_Field
494 ( p_line         IN VARCHAR2
495 , p_delimiter    IN VARCHAR2
496 , p_field_no     IN NUMBER
497 )
498 RETURN VARCHAR2
499 IS
500 /*
501   Local variables
502 */
503 l_start         NUMBER  :=0;
504 l_end           NUMBER  :=0;
505 
506 BEGIN
507 
508 /*   Determine start position  */
509 IF p_field_no = 1
510 THEN
511   l_start       :=0;
512 ELSE
513   l_start       :=INSTR(p_line,p_delimiter,1,(p_field_no - 1));
514   IF l_start    = 0
515   THEN
516     RETURN NULL;
517   END IF;
518 END IF;
519 
520 /*  Determine end position   */
521 l_end           :=INSTR(p_line,p_delimiter,1,p_field_no);
522 IF l_end        = 0
523 THEN
524   l_end         := LENGTH(p_line) + 1;
525 END IF;
526 
527 /*  Extract the field data  */
528 IF (l_end - l_start) = 1
529 THEN
530   RETURN NULL;
531 ELSE
532   RETURN SUBSTR(p_line,(l_start + 1),((l_end - l_start) - 1));
533 END IF;
534 
535 EXCEPTION
536   WHEN OTHERS
537   THEN
538     RETURN NULL;
539 
540 END Get_Field;
541 
542 /* +==========================================================================+
543  | FUNCTION NAME                                                            |
544  |    Get_Substring                                                         |
545  |                                                                          |
546  | TYPE                                                                     |
547  |    Public                                                                |
548  |                                                                          |
549  | USAGE                                                                    |
550  |    Get value of Sub-string from formatted ASCII data file record         |
551  |                                                                          |
552  | DESCRIPTION                                                              |
553  |    This utility function will return the value of a passed sub-string    |
554  |    of a formatted ASCII data file record                                 |
555  |                                                                          |
556  | PARAMETERS                                                               |
557  |    p_substring        IN VARCHAR2         - substring data               |
558  |                                                                          |
559  | RETURNS                                                                  |
560  |    VARCHAR2                               - Value of field               |
561  |                                                                          |
562  | HISTORY                                                                  |
563  |                                                                          |
564  +==========================================================================+
565   Api end of comments
566 */
567 FUNCTION Get_Substring
568 ( p_substring    IN VARCHAR2
569 )
570 RETURN VARCHAR2
571 IS
572 /*
573   Local variables
574 */
575 l_string_value   VARCHAR2(200)  :=' ';
576 
577 BEGIN
578 
579 /*  Determine start position    */
580 l_string_value  :=NVL(RTRIM(LTRIM(p_substring)),' ');
581 
582 RETURN l_string_value;
583 EXCEPTION
584   WHEN OTHERS
585   THEN
586     RETURN ' ';
587 
588 END Get_Substring;
589 
590 END GMI_LOTS_WRP;