[Home] [Help]
PACKAGE BODY: APPS.GMI_ITEM_LOT_CONV_WRP
Source
1 PACKAGE BODY GMI_ITEM_LOT_CONV_WRP AS
2 /* $Header: GMIPILWB.pls 115.8 2000/11/28 08:56:58 pkm ship $ */
3 /* +==========================================================================+
4 | PROCEDURE NAME |
5 | Create_conv |
6 | |
7 | TYPE |
8 | Public |
9 | |
10 | USAGE |
11 | Create an Item/Lot/Sublot UoM conversion |
12 | |
13 | DESCRIPTION |
14 | This is a PL/SQL wrapper procedure to call the Create_Conv |
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 | |
30 Api end of comments
27 | HISTORY |
28 | |
29 +==========================================================================+
31 */
32 PROCEDURE Create_Conv
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 :=Create_conv( p_dir
45 , p_input_file
46 , p_output_file
47 , p_delimiter
48 );
49
50 End Create_Conv;
51
52 /* +==========================================================================+
53 | FUNCTION NAME |
54 | Create_conv |
55 | |
56 | TYPE |
57 | Public |
58 | |
59 | USAGE |
60 | Create an Item/Lot/Sublot UoM conversion |
61 | |
62 | DESCRIPTION |
63 | This is a PL/SQL wrapper function to call the OPM |
64 | Inventory Item Lot/Sublot UOM Conversion Create 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 /Out 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 | VARCHAR2 - 'S' All records processed successfully |
78 | 'E' 1 or more records errored |
79 | 'U' 1 or more record unexpected error |
80 | |
81 | HISTORY |
82 | |
83 +==========================================================================+
84 Api end of comments
85 */
86 FUNCTION Create_Conv
87 ( p_dir IN VARCHAR2
88 , p_input_file IN VARCHAR2
89 , p_output_file IN VARCHAR2
90 , p_delimiter IN VARCHAR2
91 )
92 RETURN VARCHAR2
93 IS
94
95 /* Local variables */
96
97 l_status VARCHAR2(1);
98 l_return_status VARCHAR2(1) :=FND_API.G_RET_STS_SUCCESS;
99 l_count NUMBER ;
100 l_dummy_cnt NUMBER :=0;
101 l_loop_cnt NUMBER :=0;
102 l_record_count NUMBER :=0;
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);
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;
119
120 BEGIN
121
122 /* Enable The Buffer */
123 /* DBMS_OUTPUT.ENABLE(1000000); */
124
125 l_p_dir :=p_dir;
126 l_input_file :=p_input_file;
127 l_output_file :=p_output_file;
128 l_delimiter :=p_delimiter;
129 l_global_file :=l_input_file;
130
131 /* Obtain The SessionId To Append To wrapper File Name. */
132
133 l_session_id := USERENV('sessionid');
134
135 l_log_name := CONCAT(l_log_name,l_session_id);
136 l_log_name := CONCAT(l_log_name,'.log');
137
138 /* Directory is now the same same as for the out file */
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
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
157 l_outfile_handle :=UTL_FILE.FOPEN(l_p_dir, l_output_file, 'w');
158
159
163 BEGIN
160 LOOP
161 l_record_count :=l_record_count+1;
162
164 UTL_FILE.GET_LINE(l_infile_handle, l_line);
165 EXCEPTION
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);
175 item_cnv_rec.sublot_no :=Get_Field(l_line,l_delimiter,3);
176 item_cnv_rec.from_uom :=Get_Field(l_line,l_delimiter,4);
177 item_cnv_rec.to_uom :=Get_Field(l_line,l_delimiter,5);
178 IF (Get_Field(l_line,l_delimiter,6) IS NULL)
179 THEN
180 item_cnv_rec.type_factor:=0;
181 ELSE
182 item_cnv_rec.type_factor:=TO_NUMBER(Get_Field(l_line,l_delimiter,6));
183 END IF;
184
185 IF (Get_Field(l_line,l_delimiter,7) IS NULL)
186 THEN
187 item_cnv_rec.user_name :='OPM';
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 );
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
203 ( p_api_version => 3.0
204 , p_init_msg_list => FND_API.G_TRUE
205 , p_commit => FND_API.G_TRUE
206 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
207 , p_conv_rec => item_cnv_rec
208 , x_ic_item_cnv_row => l_ic_item_cnv_row
209 , x_return_status =>l_status
210 , x_msg_count =>l_count
211 , x_msg_data =>l_data
212 );
213
214 IF l_count > 0
215 THEN
216 l_loop_cnt :=1;
217 LOOP
218
219 FND_MSG_PUB.Get(
220 p_msg_index => l_loop_cnt,
221 p_data => l_data,
222 p_encoded => FND_API.G_FALSE,
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
232 l_status = 'U'
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
242 l_return_status :=l_status;
243 ELSIF (l_status = 'E' and l_return_status <> 'U')
244 THEN
245 l_return_status :=l_status;
246 ELSE
247 l_return_status :=l_status;
248 END IF;
249
250 l_loop_cnt := l_loop_cnt + 1;
251 IF l_loop_cnt > l_count
252 THEN
253 EXIT;
254 END IF;
255
256 END LOOP;
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 */
266
267 UTL_FILE.FCLOSE_ALL;
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
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); */
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
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); */
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
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
314 /* +==========================================================================+
315 | FUNCTION NAME |
316 | Get_Field |
317 | |
318 | TYPE |
319 | Public |
320 | |
321 | USAGE |
322 | Get value of field n from a delimited line of ASCII data |
323 | |
324 | DESCRIPTION |
325 | This utility function will return the value of a field from |
326 | a delimited line of ASCII text |
327 | |
328 | PARAMETERS |
329 | p_line IN VARCHAR2 - line of data |
330 | p_delimiter IN VARCHAR2 - Delimiter character |
331 | p_field_no IN NUMBER - Field occurance to be |
332 | returned |
333 | |
334 | RETURNS |
335 | VARCHAR2 - Value of field |
336 | |
337 | HISTORY |
338 | |
339 +==========================================================================+
340 Api end of comments
341 */
342 FUNCTION Get_Field
343 ( p_line IN VARCHAR2
344 , p_delimiter IN VARCHAR2
345 , p_field_no IN NUMBER
346 )
347 RETURN VARCHAR2
348 IS
349 /* Local variables */
350 l_start NUMBER :=0;
351 l_end NUMBER :=0;
352
353 BEGIN
354
355 /* Determine start position */
356 IF p_field_no = 1
357 THEN
358 l_start :=0;
359 ELSE
360 l_start :=INSTR(p_line,p_delimiter,1,(p_field_no - 1));
361 IF l_start = 0
362 THEN
363 RETURN NULL;
364 END IF;
365 END IF;
366
367 /* Determine end position */
368 l_end :=INSTR(p_line,p_delimiter,1,p_field_no);
369 IF l_end = 0
370 THEN
371 l_end := LENGTH(p_line) + 1;
372 END IF;
373
374 /* Extract the field data */
375 IF (l_end - l_start) = 1
376 THEN
377 RETURN NULL;
378 ELSE
379 RETURN SUBSTR(p_line,(l_start + 1),((l_end - l_start) - 1));
380 END IF;
381
382 EXCEPTION
383 WHEN OTHERS
384 THEN
385 RETURN NULL;
386
387 END Get_Field;
388
389 /* +==========================================================================+
390 | FUNCTION NAME |
391 | Get_Substring |
392 | |
393 | TYPE |
394 | Public |
395 | |
396 | USAGE |
397 | Get value of Sub-string from formatted ASCII data file record |
398 | |
399 | DESCRIPTION |
400 | This utility function will return the value of a passed sub-string |
401 | of a formatted ASCII data file record |
402 | |
403 | PARAMETERS |
404 | p_substring IN VARCHAR2 - substring data |
405 | |
406 | RETURNS |
407 | VARCHAR2 - Value of field |
408 | |
409 | HISTORY |
410 | |
411 +==========================================================================+
412 Api end of comments
413 */
414 FUNCTION Get_Substring
415 ( p_substring IN VARCHAR2
416 )
417 RETURN VARCHAR2
418 IS
419 /* Local variables */
420 l_string_value VARCHAR2(200) :=' ';
421
422 BEGIN
423
424 /* Determine start position */
425 l_string_value :=NVL(RTRIM(LTRIM(p_substring)),' ');
426
427 RETURN l_string_value;
428 EXCEPTION
429 WHEN OTHERS
430 THEN
431 RETURN ' ';
432
433 END Get_Substring;
434
435 END GMI_ITEM_LOT_CONV_WRP;