[Home] [Help]
PACKAGE BODY: APPS.GMI_QUANTITY_WRP
Source
1 PACKAGE BODY GMI_QUANTITY_WRP AS
2 /* $Header: GMIPQTWB.pls 115.17 2003/09/09 04:49:28 gmangari gmigapib.pls $
3 +==========================================================================+
4 | PROCEDURE NAME |
5 | Post |
6 | |
7 | TYPE |
8 | Public |
9 | |
10 | USAGE |
11 | Post an inventory transaction |
12 | |
13 | DESCRIPTION |
14 | This is a PL/SQL wrapper procedure to call the Post transaction |
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 | |
29 +==========================================================================+
30 Api end of comments
31 */
32 PROCEDURE Post
33 ( p_dir IN VARCHAR2
34 , p_input_file IN VARCHAR2
35 , p_output_file IN VARCHAR2
36 , p_delimiter IN VARCHAR2
37 )
38 IS
39
40 l_return_status VARCHAR2(1);
41
42 BEGIN
43
44 l_return_status :=Post( p_dir
45 , p_input_file
46 , p_output_file
47 , p_delimiter
48 );
49
50 End Post;
51
52 /* +==========================================================================+
53 | FUNCTION NAME |
54 | Post |
55 | |
56 | TYPE |
57 | Public |
58 | |
59 | USAGE |
60 | Post an inventory transaction |
61 | |
62 | DESCRIPTION |
63 | This is a PL/SQL wrapper function to call the OPM |
64 | Inventory Quantities API. |
65 | It reads item data from a flat file and outputs any error |
66 | messages to a second flat file. It also generates a Status |
67 | called wrapper<session_id>.log in the /tmp directory. |
68 | |
69 | PARAMETERS |
70 | p_dir IN VARCHAR2 - Working directory for input |
71 | and output files. |
72 | p_input_file IN VARCHAR2 - Name of input file |
73 | p_output_file IN VARCHAR2 - Name of output file |
74 | p_delimiter IN VARCHAR2 - Delimiter character |
75 | |
76 | RETURNS |
77 | None |
78 | |
79 | HISTORY |
80 | 01/Nov/2001 K.RajaSekhar Bug 1962677 The field journal_comment is |
81 | got from the flat file and assigned to |
82 | trans_rec and also stored in UTL_FILE. |
83 | 16/Apr/2002 Sastry BUG#1492002 Changed the date format to |
84 | include the time stamp. Also added code to |
85 | write a message into the log file if the |
86 | user does not pass the date in a valid date|
87 | format while calling the API. |
88 | 22/May/2002 Sastry BUG#1492002 Modified the date format so |
89 | that the user can input the timestamp in |
90 | 24-hour time format. |
91 | 07/04/2002 Jalaj Srivastava Bug 2483656
92 | Added 33 new columns
93 | 07/24/2003 Sastry BUG#3054841 Increased the size of l_line |
94 | from 200 to 4000. |
95 | 08/14/2003 Sastry BUG#2861715 Added code to read the newly |
96 | added parameter from the flat file. |
97 +==========================================================================+
98 Api end of comments
99 */
100 FUNCTION Post
101 ( p_dir IN VARCHAR2
102 , p_input_file IN VARCHAR2
103 , p_output_file IN VARCHAR2
104 , p_delimiter IN VARCHAR2
105 )
106 RETURN VARCHAR2
107 IS
108
109 /*
110 Local variables
111 */
112
113 l_status VARCHAR2(1);
114 l_return_status VARCHAR2(1) :=FND_API.G_RET_STS_SUCCESS;
115 l_count NUMBER ;
116 l_loop_cnt NUMBER :=0;
117 l_dummy_cnt NUMBER :=0;
118 l_record_count NUMBER :=0;
119 l_data VARCHAR2(2000);
120 trans_rec GMIGAPI.qty_rec_typ;
121 l_p_dir VARCHAR2(50);
122 l_output_file VARCHAR2(20);
123 l_outfile_handle UTL_FILE.FILE_TYPE;
124 l_input_file VARCHAR2(20);
125 l_infile_handle UTL_FILE.FILE_TYPE;
126 l_line VARCHAR2(4000); --BUG#3054841 Increased the size from 200 to 4000.
127 l_delimiter VARCHAR(1);
128 l_log_dir VARCHAR2(50);
129 l_log_name VARCHAR2(20) :='wrapper';
130 l_log_handle UTL_FILE.FILE_TYPE;
131 l_global_file VARCHAR2(20);
132
133 l_session_id VARCHAR2(10);
134
135 l_ic_jrnl_mst_row ic_jrnl_mst%ROWTYPE;
136 l_ic_adjs_jnl_row1 ic_adjs_jnl%ROWTYPE;
137 l_ic_adjs_jnl_row2 ic_adjs_jnl%ROWTYPE;
138 BEGIN
139
140 /* Enable The Buffer
141 DBMS_OUTPUT.ENABLE(1000000);
142 Disable The Buffer
143 DBMS_OUTPUT.DISABLE;
144 */
145
146 l_p_dir :=p_dir;
147 l_input_file :=p_input_file;
148 l_output_file :=p_output_file;
149 l_delimiter :=p_delimiter;
150 l_global_file :=l_input_file;
151
152 /*
153 Obtain The SessionId To Append To wrapper File Name.
154 */
155
156 l_session_id := USERENV('sessionid');
157
158 l_log_name := CONCAT(l_log_name,l_session_id);
159 l_log_name := CONCAT(l_log_name,'.log');
160
161 /* Set the Wrapper file to be placed in the default working directory */
162
163 l_log_dir := p_dir;
164
165 /*
166 Open The Wrapper File For Output And The Input File for Input.
167 */
168 /* dbms_output.put_line(l_log_name||' '||l_input_file||' '||l_log_dir||' '||l_p_dir); */
169 l_log_handle :=UTL_FILE.FOPEN(l_log_dir, l_log_name, 'w');
170 l_infile_handle :=UTL_FILE.FOPEN(l_p_dir, l_input_file, 'r');
171
172 /*
173 Loop thru flat file and call Inventory Quantities API
174 */
175
176 /* dbms_output.put_line('Start Processing'); */
177 UTL_FILE.PUT_LINE(l_log_handle, 'Process Started at '
178 || to_char(SYSDATE,'DD-MON-YY HH:MI:SS'));
179
180 UTL_FILE.NEW_LINE(l_log_handle);
181 UTL_FILE.PUT_LINE(l_log_handle, 'Input Directory ' || l_p_dir );
182 UTL_FILE.PUT_LINE(l_log_handle, 'Input File ' || l_input_file );
183 UTL_FILE.PUT_LINE(l_log_handle, 'Record Type ' || l_delimiter );
184 UTL_FILE.PUT_LINE(l_log_handle, 'Output File ' || l_output_file );
185
186 l_outfile_handle :=UTL_FILE.FOPEN(l_p_dir, l_output_file, 'w');
187 /* dbms_output.put_line('Opened Log file: '||l_p_dir||l_output_file); */
188
189 LOOP
190 l_record_count :=l_record_count+1;
191
192 BEGIN
193 UTL_FILE.GET_LINE(l_infile_handle, l_line);
194 /* dbms_output.put_line('LINE IS ' ||l_line); */
195 EXCEPTION
196 WHEN NO_DATA_FOUND THEN
197 EXIT;
198 END;
199 -- BEGIN BUG#1492002 Sastry
200 l_return_status := FND_API.G_RET_STS_SUCCESS;
201 -- END BUG#1492002
202 UTL_FILE.NEW_LINE(l_log_handle);
203 UTL_FILE.PUT_LINE(l_log_handle, 'Reading Record ' || l_record_count );
204 trans_rec.trans_type :=TO_NUMBER(Get_Field(l_line,l_delimiter,1));
205 trans_rec.item_no :=Get_Field(l_line,l_delimiter,2);
206 trans_rec.journal_no :=Get_Field(l_line,l_delimiter,3);
207 trans_rec.from_whse_code :=Get_Field(l_line,l_delimiter,4);
208 trans_rec.to_whse_code :=Get_Field(l_line,l_delimiter,5);
209 trans_rec.item_um :=Get_Field(l_line,l_delimiter,6);
210 trans_rec.item_um2 :=Get_Field(l_line,l_delimiter,7);
211 trans_rec.lot_no :=Get_Field(l_line,l_delimiter,8);
212 trans_rec.sublot_no :=Get_Field(l_line,l_delimiter,9);
213 trans_rec.from_location :=Get_Field(l_line,l_delimiter,10);
214 trans_rec.to_location :=Get_Field(l_line,l_delimiter,11);
215 trans_rec.trans_qty :=TO_NUMBER(Get_Field(l_line,l_delimiter,12));
216 trans_rec.trans_qty2 :=TO_NUMBER(Get_Field(l_line,l_delimiter,13));
217 trans_rec.qc_grade :=Get_Field(l_line,l_delimiter,14);
218 trans_rec.lot_status :=Get_Field(l_line,l_delimiter,15);
219 trans_rec.co_code :=Get_Field(l_line,l_delimiter,16);
220 trans_rec.orgn_code :=Get_Field(l_line,l_delimiter,17);
221 IF Get_Field(l_line,l_delimiter,18) IS NULL
222 THEN
223 trans_rec.trans_date :=SYSDATE;
224 ELSE
225 -- BEGIN BUG#1492002 Sastry
226 -- Modified the date format from 'DDMMYYYY' to 'DDMMYYYYHH24MISS'
227 -- such that the user can pass the timestamp in 24-hour format.
228 -- Added code to write an appropriate error message into the log
229 -- file if the user passes an invalid date format.
230 BEGIN
231 trans_rec.trans_date :=TO_DATE(Get_Field(l_line,l_delimiter,18)
232 ,'DDMMYYYYHH24MISS');
233 EXCEPTION
234 WHEN OTHERS THEN
235 FND_MSG_PUB.Initialize;
236 FND_MESSAGE.SET_NAME ('GMA', 'SY_BAD_DATEFORMAT');
237 FND_MSG_PUB.Add;
238 L_LOOP_CNT := 1;
239 FND_MSG_PUB.Get(
240 p_msg_index => l_loop_cnt,
241 p_data => l_data,
242 p_encoded => FND_API.G_FALSE,
243 p_msg_index_out => l_dummy_cnt);
244 UTL_FILE.PUT_LINE(l_outfile_handle, 'Record = ' ||l_record_count );
245 UTL_FILE.PUT_LINE(l_outfile_handle, l_data);
246 UTL_FILE.NEW_LINE(l_outfile_handle);
247 l_data := CONCAT('ERROR: ', l_data);
248 UTL_FILE.PUT_LINE(l_log_handle, l_data);
249 l_return_status :='E';
250 END;
251 -- END BUG#1492002
252 END IF;
253 trans_rec.reason_code :=Get_Field(l_line,l_delimiter,19);
254 IF ((Get_Field(l_line,l_delimiter,20)) IS NULL)
255 THEN
256 trans_rec.user_name :='OPM';
257 ELSE
258 trans_rec.user_name :=Get_Field(l_line,l_delimiter,20);
259 END IF;
260 --BEGIN BUG#1962677 K.RajaSekhar
261 trans_rec.journal_comment :=Get_Field(l_line,l_delimiter,21);
262 --END BUG#1962677
263 trans_rec.attribute1 :=Get_Field(l_line,l_delimiter,22);
264 trans_rec.attribute2 :=Get_Field(l_line,l_delimiter,23);
265 trans_rec.attribute3 :=Get_Field(l_line,l_delimiter,24);
266 trans_rec.attribute4 :=Get_Field(l_line,l_delimiter,25);
267 trans_rec.attribute5 :=Get_Field(l_line,l_delimiter,26);
268 trans_rec.attribute6 :=Get_Field(l_line,l_delimiter,27);
269 trans_rec.attribute7 :=Get_Field(l_line,l_delimiter,28);
270 trans_rec.attribute8 :=Get_Field(l_line,l_delimiter,29);
271 trans_rec.attribute9 :=Get_Field(l_line,l_delimiter,30);
272 trans_rec.attribute10 :=Get_Field(l_line,l_delimiter,31);
273 trans_rec.attribute11 :=Get_Field(l_line,l_delimiter,32);
274 trans_rec.attribute12 :=Get_Field(l_line,l_delimiter,33);
275 trans_rec.attribute13 :=Get_Field(l_line,l_delimiter,34);
276 trans_rec.attribute14 :=Get_Field(l_line,l_delimiter,35);
277 trans_rec.attribute15 :=Get_Field(l_line,l_delimiter,36);
278 trans_rec.attribute16 :=Get_Field(l_line,l_delimiter,37);
279 trans_rec.attribute17 :=Get_Field(l_line,l_delimiter,38);
280 trans_rec.attribute18 :=Get_Field(l_line,l_delimiter,39);
281 trans_rec.attribute19 :=Get_Field(l_line,l_delimiter,40);
282 trans_rec.attribute20 :=Get_Field(l_line,l_delimiter,41);
283 trans_rec.attribute21 :=Get_Field(l_line,l_delimiter,42);
284 trans_rec.attribute22 :=Get_Field(l_line,l_delimiter,43);
285 trans_rec.attribute23 :=Get_Field(l_line,l_delimiter,44);
286 trans_rec.attribute24 :=Get_Field(l_line,l_delimiter,45);
287 trans_rec.attribute25 :=Get_Field(l_line,l_delimiter,46);
288 trans_rec.attribute26 :=Get_Field(l_line,l_delimiter,47);
289 trans_rec.attribute27 :=Get_Field(l_line,l_delimiter,48);
290 trans_rec.attribute28 :=Get_Field(l_line,l_delimiter,49);
291 trans_rec.attribute29 :=Get_Field(l_line,l_delimiter,50);
292 trans_rec.attribute30 :=Get_Field(l_line,l_delimiter,51);
293 trans_rec.attribute_category :=Get_Field(l_line,l_delimiter,52);
294 trans_rec.acctg_unit_no :=Get_Field(l_line,l_delimiter,53);
295 trans_rec.acct_no :=Get_Field(l_line,l_delimiter,54);
296 trans_rec.move_entire_qty :=NVL(Get_Field(l_line,l_delimiter,55),'Y'); --BUG#2861715 Sastry
297
298 UTL_FILE.PUT_LINE(l_log_handle,'trans type = '||trans_rec.trans_type);
299 UTL_FILE.PUT_LINE(l_log_handle,'item no = '||trans_rec.item_no);
300 UTL_FILE.PUT_LINE(l_log_handle,'journal no = '||trans_rec.journal_no);
301 UTL_FILE.PUT_LINE(l_log_handle,'from_whse_code = '||
302 trans_rec.from_whse_code);
303 UTL_FILE.PUT_LINE(l_log_handle,'to_whse_code = '||
304 trans_rec.to_whse_code);
305 UTL_FILE.PUT_LINE(l_log_handle,'item_um = '||trans_rec.item_um);
306 UTL_FILE.PUT_LINE(l_log_handle,'item_um2 = '||trans_rec.item_um2);
307 UTL_FILE.PUT_LINE(l_log_handle,'lot no = '||trans_rec.lot_no);
308 UTL_FILE.PUT_LINE(l_log_handle,'sublot no = '||trans_rec.sublot_no);
309 UTL_FILE.PUT_LINE(l_log_handle,'from_location = '||
310 trans_rec.from_location);
311 UTL_FILE.PUT_LINE(l_log_handle,'to_location = '||
312 trans_rec.to_location);
313 UTL_FILE.PUT_LINE(l_log_handle,'trans_qty = '||trans_rec.trans_qty);
314 UTL_FILE.PUT_LINE(l_log_handle,'trans_qty2 = '||trans_rec.trans_qty2);
315 UTL_FILE.PUT_LINE(l_log_handle,'qc_grade = '||trans_rec.qc_grade);
316 UTL_FILE.PUT_LINE(l_log_handle,'lot_status = '||trans_rec.lot_status);
317 UTL_FILE.PUT_LINE(l_log_handle,'co code = '||trans_rec.co_code);
318 UTL_FILE.PUT_LINE(l_log_handle,'orgn code = '||trans_rec.orgn_code);
319 UTL_FILE.PUT_LINE(l_log_handle,'trans_date = '||trans_rec.trans_date);
320 UTL_FILE.PUT_LINE(l_log_handle,'reason code = '||trans_rec.reason_code);
321 UTL_FILE.PUT_LINE(l_log_handle,'user name = '||trans_rec.user_name );
322 --BEGIN BUG#1962677 K.RajaSekhar
323 UTL_FILE.PUT_LINE(l_log_handle,'journal comment= '||trans_rec.journal_comment);
324 --END BUG#1962677
325 UTL_FILE.PUT_LINE(l_log_handle,'acctg_unit_no = '||trans_rec.acctg_unit_no);
326 UTL_FILE.PUT_LINE(l_log_handle,'acct_no = '||trans_rec.acct_no);
327 UTL_FILE.PUT_LINE(l_log_handle,'attribute1 = '||trans_rec.attribute1);
328 UTL_FILE.PUT_LINE(l_log_handle,'attribute2 = '||trans_rec.attribute2);
329 UTL_FILE.PUT_LINE(l_log_handle,'attribute3 = '||trans_rec.attribute3);
330 UTL_FILE.PUT_LINE(l_log_handle,'attribute4 = '||trans_rec.attribute4);
331 UTL_FILE.PUT_LINE(l_log_handle,'attribute5 = '||trans_rec.attribute5);
332 UTL_FILE.PUT_LINE(l_log_handle,'attribute6 = '||trans_rec.attribute6);
333 UTL_FILE.PUT_LINE(l_log_handle,'attribute7 = '||trans_rec.attribute7);
334 UTL_FILE.PUT_LINE(l_log_handle,'attribute8 = '||trans_rec.attribute8);
335 UTL_FILE.PUT_LINE(l_log_handle,'attribute9 = '||trans_rec.attribute9);
336 UTL_FILE.PUT_LINE(l_log_handle,'attribute10 = '||trans_rec.attribute10);
337 UTL_FILE.PUT_LINE(l_log_handle,'attribute11 = '||trans_rec.attribute11);
338 UTL_FILE.PUT_LINE(l_log_handle,'attribute12 = '||trans_rec.attribute12);
339 UTL_FILE.PUT_LINE(l_log_handle,'attribute13 = '||trans_rec.attribute13);
340 UTL_FILE.PUT_LINE(l_log_handle,'attribute14 = '||trans_rec.attribute14);
341 UTL_FILE.PUT_LINE(l_log_handle,'attribute15 = '||trans_rec.attribute15);
342 UTL_FILE.PUT_LINE(l_log_handle,'attribute16 = '||trans_rec.attribute16);
343 UTL_FILE.PUT_LINE(l_log_handle,'attribute17 = '||trans_rec.attribute17);
344 UTL_FILE.PUT_LINE(l_log_handle,'attribute18 = '||trans_rec.attribute18);
345 UTL_FILE.PUT_LINE(l_log_handle,'attribute19 = '||trans_rec.attribute19);
346 UTL_FILE.PUT_LINE(l_log_handle,'attribute20 = '||trans_rec.attribute20);
347 UTL_FILE.PUT_LINE(l_log_handle,'attribute21 = '||trans_rec.attribute21);
348 UTL_FILE.PUT_LINE(l_log_handle,'attribute22 = '||trans_rec.attribute22);
349 UTL_FILE.PUT_LINE(l_log_handle,'attribute23 = '||trans_rec.attribute23);
350 UTL_FILE.PUT_LINE(l_log_handle,'attribute24 = '||trans_rec.attribute24);
351 UTL_FILE.PUT_LINE(l_log_handle,'attribute25 = '||trans_rec.attribute25);
352 UTL_FILE.PUT_LINE(l_log_handle,'attribute26 = '||trans_rec.attribute26);
353 UTL_FILE.PUT_LINE(l_log_handle,'attribute27 = '||trans_rec.attribute27);
354 UTL_FILE.PUT_LINE(l_log_handle,'attribute28 = '||trans_rec.attribute28);
355 UTL_FILE.PUT_LINE(l_log_handle,'attribute29 = '||trans_rec.attribute29);
356 UTL_FILE.PUT_LINE(l_log_handle,'attribute30 = '||trans_rec.attribute30);
357 UTL_FILE.PUT_LINE(l_log_handle,'attribute_category = '||trans_rec.attribute_category);
358 -- BEGIN BUG#1492002 Sastry
359 -- Perform posting only if the return status is not an error.
360 IF l_return_status <> 'E' THEN
361 -- END BUG#1492002
362 /* Allow Default Allocation Of User If NULL. */
363 IF trans_rec.user_name IS NULL THEN
364 trans_rec.user_name :='OPM';
365 END IF;
366
367 GMIPAPI.Inventory_Posting
368 ( p_api_version => 3.0
369 , p_init_msg_list => FND_API.G_TRUE
370 , p_commit => FND_API.G_TRUE
371 , p_validation_level => FND_API.G_valid_level_full
372 , p_qty_rec => trans_rec
373 , x_ic_jrnl_mst_row => l_ic_jrnl_mst_row
374 , x_ic_adjs_jnl_row1 => l_ic_adjs_jnl_row1
375 , x_ic_adjs_jnl_row2 => l_ic_adjs_jnl_row2
376 , x_return_status =>l_status
377 , x_msg_count =>l_count
378 , x_msg_data =>l_data
379 );
380
381 IF l_count > 0
382 THEN
383 l_loop_cnt :=1;
384 LOOP
385
386 FND_MSG_PUB.Get(
387 p_msg_index => l_loop_cnt,
388 p_data => l_data,
389 p_encoded => FND_API.G_FALSE,
390 p_msg_index_out => l_dummy_cnt);
391
392
393 UTL_FILE.PUT_LINE(l_outfile_handle, 'Record = ' ||l_record_count );
394 UTL_FILE.PUT_LINE(l_outfile_handle, l_data);
395 UTL_FILE.NEW_LINE(l_outfile_handle);
396
397 IF l_status = 'E' OR
398 l_status = 'U'
399 THEN
400 l_data := CONCAT('ERROR ',l_data);
401 END IF;
402
403 UTL_FILE.PUT_LINE(l_log_handle, l_data);
404
405 /* Update error status */
406 IF (l_status = 'U')
407 THEN
408 l_return_status :=l_status;
409 ELSIF (l_status = 'E' and l_return_status <> 'U')
410 THEN
411 l_return_status :=l_status;
412 ELSE
413 l_return_status :=l_status;
414 END IF;
415
416 l_loop_cnt := l_loop_cnt + 1;
417 IF l_loop_cnt > l_count
418 THEN
419 EXIT;
420 END IF;
421
422 END LOOP;
423
424 END IF;
425 -- BEGIN BUG#1492002 Sastry
426 END IF;
427 -- END BUG#1492002
428 END LOOP;
429 UTL_FILE.NEW_LINE(l_log_handle);
430 UTL_FILE.PUT_LINE(l_log_handle, 'Process Completed at '
431 || to_char(SYSDATE,'DD-MON-YY HH:MI:SS'));
432 /*
433 Check if any messages generated. If so then decode and
434 output to error message flat file
435 */
436
437 UTL_FILE.FCLOSE_ALL;
438
439 RETURN l_return_status;
440
441 EXCEPTION
442 WHEN UTL_FILE.INVALID_OPERATION THEN
443 UTL_FILE.FCLOSE_ALL;
444 RETURN l_return_status;
445
446 WHEN UTL_FILE.INVALID_PATH THEN
447 UTL_FILE.FCLOSE_ALL;
448 RETURN l_return_status;
449
450 WHEN UTL_FILE.INVALID_MODE THEN
451 UTL_FILE.FCLOSE_ALL;
452 RETURN l_return_status;
453
454 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
455 UTL_FILE.FCLOSE_ALL;
456 RETURN l_return_status;
457
458 WHEN UTL_FILE.WRITE_ERROR THEN
459 UTL_FILE.FCLOSE_ALL;
460 RETURN l_return_status;
461
462 WHEN UTL_FILE.READ_ERROR THEN
463 UTL_FILE.FCLOSE_ALL;
464 RETURN l_return_status;
465
466 WHEN UTL_FILE.INTERNAL_ERROR THEN
467 UTL_FILE.FCLOSE_ALL;
468 RETURN l_return_status;
469
470 WHEN OTHERS THEN
471 UTL_FILE.FCLOSE_ALL;
472 RETURN l_return_status;
473
474 END Post;
475
476 /* +==========================================================================+
477 | FUNCTION NAME |
478 | Get_Field |
479 | |
480 | TYPE |
481 | Public |
482 | |
483 | USAGE |
484 | Get value of field n from a delimited line of ASCII data |
485 | |
486 | DESCRIPTION |
487 | This utility function will return the value of a field from |
488 | a delimited line of ASCII text |
489 | |
490 | PARAMETERS |
491 | p_line IN VARCHAR2 - line of data |
492 | p_delimiter IN VARCHAR2 - Delimiter character |
493 | p_field_no IN NUMBER - Field occurance to be |
494 | returned |
495 | |
496 | RETURNS |
497 | VARCHAR2 - Value of field |
498 | |
499 | HISTORY |
500 | |
501 +==========================================================================+
502 Api end of comments
503 */
504 FUNCTION Get_Field
505 ( p_line IN VARCHAR2
506 , p_delimiter IN VARCHAR2
507 , p_field_no IN NUMBER
508 )
509 RETURN VARCHAR2
510 IS
511 /*
512 Local variables
513 */
514 l_start NUMBER :=0;
515 l_end NUMBER :=0;
516
517 BEGIN
518
519 /* Determine start position */
520 IF p_field_no = 1
521 THEN
522 l_start :=0;
523 ELSE
524 l_start :=INSTR(p_line,p_delimiter,1,(p_field_no - 1));
525 IF l_start = 0
526 THEN
527 RETURN NULL;
528 END IF;
529 END IF;
530
531 /* Determine end position */
532 l_end :=INSTR(p_line,p_delimiter,1,p_field_no);
533 IF l_end = 0
534 THEN
535 l_end := LENGTH(p_line) + 1;
536 END IF;
537
538 /* Extract the field data */
539 IF (l_end - l_start) = 1
540 THEN
541 RETURN NULL;
542 ELSE
543 RETURN SUBSTR(p_line,(l_start + 1),((l_end - l_start) - 1));
544 END IF;
545
546 EXCEPTION
547 WHEN OTHERS
548 THEN
549 RETURN NULL;
550
551 END Get_Field;
552
553 /* +==========================================================================+
554 | FUNCTION NAME |
555 | Get_Substring |
556 | |
557 | TYPE |
558 | Public |
559 | |
560 | USAGE |
561 | Get value of Sub-string from formatted ASCII data file record |
562 | |
563 | DESCRIPTION |
564 | This utility function will return the value of a passed sub-string |
565 | of a formatted ASCII data file record |
566 | |
567 | PARAMETERS |
568 | p_substring IN VARCHAR2 - substring data |
569 | |
570 | RETURNS |
571 | VARCHAR2 - Value of field |
572 | |
573 | HISTORY |
574 | |
575 +==========================================================================+
576 Api end of comments
577 */
578 FUNCTION Get_Substring
579 ( p_substring IN VARCHAR2
580 )
581 RETURN VARCHAR2
582 IS
583 /*
584 Local variables
585 */
586 l_string_value VARCHAR2(200) :=' ';
587
588 BEGIN
589
590 /* Determine start position */
591 l_string_value :=NVL(RTRIM(LTRIM(p_substring)),' ');
592
593 RETURN l_string_value;
594 EXCEPTION
595 WHEN OTHERS
596 THEN
597 RETURN ' ';
598
599 END Get_Substring;
600
601 END GMI_QUANTITY_WRP;