[Home] [Help]
PACKAGE BODY: APPS.GMI_ITEM_WRP
Source
1 PACKAGE BODY GMI_ITEM_WRP AS
2 /* $Header: GMIPITWB.pls 115.12 2003/09/17 15:45:26 txyu gmigapib.pls $ */
3 /* Body start of comments
4 +==========================================================================+
5 | PROCEDURE NAME |
6 | Create_Item |
7 | |
8 | TYPE |
9 | Public |
10 | |
11 | USAGE |
12 | Create an Inventory Item |
13 | |
14 | DESCRIPTION |
15 | This is a PL/SQL wrapper procedure to call the Create_Item |
16 | API wrapper function |
17 | |
18 | PARAMETERS |
19 | p_dir IN VARCHAR2 - Working directory for input |
20 | and output files. |
21 | p_input_file IN VARCHAR2 - Name of input file |
22 | p_output_file IN VARCHAR2 - Name of output file |
23 | p_delimiter IN VARCHAR2 - Delimiter character |
24 | |
25 | RETURNS |
26 | None |
27 | |
28 | HISTORY |
29 | |
30 | 16-AUG-1999 B965832(1) Set lot_status/qc_grade to NULL if |
31 | they are read in as spaces |
32 | 18-Oct-2002 Bug 2513463 - Fixed code so that errors are returned. |
33 | 11-Sep-2003 B2378017 - Added code to read in four new classes. |
34 +==========================================================================+
35 Api end of comments
36 */
37 PROCEDURE Create_Item
38 ( p_dir IN VARCHAR2
39 , p_input_file IN VARCHAR2
40 , p_output_file IN VARCHAR2
41 , p_delimiter IN VARCHAR2
42 )
43 IS
44
45 l_return_status VARCHAR2(1);
46
47 BEGIN
48
49 l_return_status :=Create_item( p_dir
50 , p_input_file
51 , p_output_file
52 , p_delimiter
53 );
54
55 End Create_Item;
56
57 /* +==========================================================================+
58 | FUNCTION NAME |
59 | Create_Item |
60 | |
61 | TYPE |
62 | Public |
63 | |
64 | USAGE |
65 | Create an inventory item |
66 | |
67 | DESCRIPTION |
68 | This is a PL/SQL wrapper function to call the FND |
69 | Inventory Create Item API. |
70 | It reads item data from a flat file and outputs any error |
71 | messages to a second flat file. It also generates a Status |
72 | called wrapper<session_id>.log in the /tmp directory. |
73 | |
74 | PARAMETERS |
75 | p_dir IN VARCHAR2 - Working directory for input |
76 | and output files. |
77 | p_input_file IN VARCHAR2 - Name of input file |
78 | p_output_file IN VARCHAR2 - Name of output file |
79 | p_delimiter IN VARCHAR2 - Delimiter character |
80 | |
81 | RETURNS |
82 | VARCHAR2 - 'S' All records processed successfully |
83 | 'E' 1 or more records errored |
84 | 'U' 1 or more record unexpected error |
85 | |
86 | HISTORY |
87 | 18-Oct-2002 Bug 2513463 - Fixed code so that errors are returned. |
88 | |
89 +==========================================================================+
90 Api end of comments
91 */
92 FUNCTION Create_Item
93 ( p_dir IN VARCHAR2
94 , p_input_file IN VARCHAR2
95 , p_output_file IN VARCHAR2
96 , p_delimiter IN VARCHAR2
97 )
98 RETURN VARCHAR2
99 IS
100
101 /* Local variables */
102
103 l_status VARCHAR2(1);
104 l_return_status VARCHAR2(1) :=FND_API.G_RET_STS_SUCCESS;
105 l_count NUMBER ;
106 l_record_count NUMBER :=0;
107 l_loop_cnt NUMBER :=0;
108 l_dummy_cnt NUMBER :=0;
109 l_data VARCHAR2(2000);
110 item_rec GMIGAPI.item_rec_typ;
111 l_ic_item_mst_row ic_item_mst%ROWTYPE;
112 l_ic_item_cpg_row ic_item_cpg%ROWTYPE;
113 l_p_dir VARCHAR2(50);
114 l_output_file VARCHAR2(20);
115 l_outfile_handle UTL_FILE.FILE_TYPE;
116 l_input_file VARCHAR2(20);
117 l_infile_handle UTL_FILE.FILE_TYPE;
118 l_line VARCHAR2(800);
119 l_delimiter VARCHAR(1);
120 l_log_dir VARCHAR2(50);
121 l_log_name VARCHAR2(20) :='wrapper';
122 l_log_handle UTL_FILE.FILE_TYPE;
123 l_global_file VARCHAR2(20);
124
125 l_session_id VARCHAR2(10);
126
127 BEGIN
128
129 /* Enable The Buffer */
130 /* DBMS_OUTPUT.ENABLE(1000000); */
131
132 l_p_dir :=p_dir;
133 l_input_file :=p_input_file;
134 l_output_file :=p_output_file;
135 l_delimiter :=p_delimiter;
136 l_global_file :=l_input_file;
137
138 /* Obtain The SessionId To Append To wrapper File Name. */
139
140 l_session_id := USERENV('sessionid');
141
142 l_log_name := CONCAT(l_log_name,l_session_id);
143 l_log_name := CONCAT(l_log_name,'.log');
144
145 /* Directory is now the same same as for the out file */
146 l_log_dir := p_dir;
147
148
149 /* Open The Wrapper File For Output And The Input File for Input. */
150
151 l_log_handle :=UTL_FILE.FOPEN(l_log_dir, l_log_name, 'w');
152 l_infile_handle :=UTL_FILE.FOPEN(l_p_dir, l_input_file, 'r');
153
154 /* Loop thru flat file and call Inventory Quantities API */
155
156 /* dbms_output.put_line('Start Processing'); */
157 UTL_FILE.PUT_LINE(l_log_handle, 'Process Started at '
158 || to_char(SYSDATE,'DD-MON-YY HH24:MI:SS'));
159
160 UTL_FILE.NEW_LINE(l_log_handle);
161 UTL_FILE.PUT_LINE(l_log_handle, 'Input Directory ' || l_p_dir );
162 UTL_FILE.PUT_LINE(l_log_handle, 'Input File ' || l_input_file );
163 UTL_FILE.PUT_LINE(l_log_handle, 'Record Type ' || l_delimiter );
164 UTL_FILE.PUT_LINE(l_log_handle, 'Output File ' || l_output_file );
165
166 l_outfile_handle :=UTL_FILE.FOPEN(l_p_dir, l_output_file, 'w');
167
168
169 LOOP
170 l_record_count :=l_record_count+1;
171
172 BEGIN
173 UTL_FILE.GET_LINE(l_infile_handle, l_line);
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176 EXIT;
177 END;
178
179 UTL_FILE.NEW_LINE(l_log_handle);
180 UTL_FILE.PUT_LINE(l_log_handle, 'Reading Record ' || l_record_count );
181
182 item_rec.item_no :=Get_Field(l_line,l_delimiter,1);
183 item_rec.item_desc1 :=Get_Field(l_line,l_delimiter,2);
184 item_rec.item_desc2 :=Get_Field(l_line,l_delimiter,3);
185 item_rec.alt_itema :=Get_Field(l_line,l_delimiter,4);
186 item_rec.alt_itemb :=Get_Field(l_line,l_delimiter,5);
187 item_rec.item_um :=Get_Field(l_line,l_delimiter,6);
188 item_rec.dualum_ind :=
189 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,7),' '),' ','0'));
190 item_rec.item_um2 :=Get_Field(l_line,l_delimiter,8);
191 item_rec.deviation_lo :=
192 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,9),' '),' ','0'));
193 item_rec.deviation_hi :=
194 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,10),' '),' ','0'));
195 item_rec.level_code :=TO_NUMBER(Get_Field(l_line,l_delimiter,11));
196 item_rec.lot_ctl :=
197 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,12),' '),' ','0'));
198 item_rec.lot_indivisible :=
199 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,13),' '),' ','0'));
200 item_rec.sublot_ctl :=
201 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,14),' '),' ','0'));
202 item_rec.loct_ctl :=
203 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,15),' '),' ','0'));
204 item_rec.noninv_ind :=
205 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,16),' '),' ','0'));
206 item_rec.match_type :=
207 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,17),' '),' ','0'));
208 item_rec.inactive_ind :=
209 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,18),' '),' ','0'));
210 item_rec.inv_type :=Get_Field(l_line,l_delimiter,19);
211 item_rec.shelf_life :=
212 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,20),' '),' ','0'));
213 item_rec.retest_interval :=
214 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,21),' '),' ','0'));
215 item_rec.item_abccode :=Get_Field(l_line,l_delimiter,22);
216 item_rec.gl_class :=Get_Field(l_line,l_delimiter,23);
217 item_rec.inv_class :=Get_Field(l_line,l_delimiter,24);
218 item_rec.sales_class :=Get_Field(l_line,l_delimiter,25);
219 item_rec.ship_class :=Get_Field(l_line,l_delimiter,26);
220 item_rec.frt_class :=Get_Field(l_line,l_delimiter,27);
221 item_rec.price_class :=Get_Field(l_line,l_delimiter,28);
222 item_rec.storage_class :=Get_Field(l_line,l_delimiter,29);
223 item_rec.purch_class :=Get_Field(l_line,l_delimiter,30);
224 item_rec.tax_class :=Get_Field(l_line,l_delimiter,31);
225 item_rec.customs_class :=Get_Field(l_line,l_delimiter,32);
226 item_rec.alloc_class :=Get_Field(l_line,l_delimiter,33);
227 item_rec.planning_class :=Get_Field(l_line,l_delimiter,34);
228 item_rec.itemcost_class :=Get_Field(l_line,l_delimiter,35);
229 item_rec.cost_mthd_code :=Get_Field(l_line,l_delimiter,36);
230 item_rec.upc_code :=Get_Field(l_line,l_delimiter,37);
231 item_rec.grade_ctl :=
232 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,38),' '),' ','0'));
233 item_rec.status_ctl :=
234 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,39),' '),' ','0'));
235 item_rec.qc_grade :=Get_Field(l_line,l_delimiter,40);
236 /* B965832(1) Check for spaces */
237 IF item_rec.qc_grade = ' '
238 THEN
239 item_rec.qc_grade := '';
240 END IF;
241 /* B965832(1) End */
242 item_rec.lot_status :=Get_Field(l_line,l_delimiter,41);
243 /* B965832(1) Check for spaces */
244 IF item_rec.lot_status = ' '
245 THEN
246 item_rec.lot_status :='';
247 END IF;
248 /* B965832(1) End */
249 item_rec.bulk_id :=TO_NUMBER(Get_Field(l_line,l_delimiter,42));
250 item_rec.pkg_id :=TO_NUMBER(Get_Field(l_line,l_delimiter,43));
251 item_rec.qcitem_no :=Get_Field(l_line,l_delimiter,44);
252 item_rec.qchold_res_code :=Get_Field(l_line,l_delimiter,45);
253 item_rec.expaction_code :=Get_Field(l_line,l_delimiter,46);
254 item_rec.fill_qty :=
255 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,47),' '),' ','0'));
256 item_rec.fill_um :=Get_Field(l_line,l_delimiter,48);
257 item_rec.expaction_interval :=
258 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,49),' '),' ','0'));
259 item_rec.phantom_type :=
260 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,50),' '),' ','0'));
261 item_rec.whse_item_no :=Get_Field(l_line,l_delimiter,51);
262 item_rec.experimental_ind:=
263 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,52),' '),' ','0'));
264 IF (Get_Field(l_line,l_line,53) IS NULL)
265 THEN
266 item_rec.exported_date :=TO_DATE('02011970','DDMMYYYY');
267 ELSE
268 item_rec.exported_date :=TO_DATE(
269 Get_Field(l_line,l_delimiter,53),'DDMMYYYY');
270 END IF;
271 item_rec.seq_dpnd_class :=Get_Field(l_line,l_delimiter,54);
272 item_rec.commodity_code :=Get_Field(l_line,l_delimiter,55);
273 item_rec.ic_matr_days :=
274 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,56),' '),' ','0'));
275 item_rec.ic_hold_days :=
276 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,57),' '),' ','0'));
277 IF ((Get_Field(l_line,l_delimiter,58)) IS NULL)
278 THEN
279 item_rec.user_name :='OPM';
280 ELSE
281 item_rec.user_name :=Get_Field(l_line,l_delimiter,58);
282 END IF;
283 item_rec.attribute1 :=Get_Field(l_line,l_delimiter,59);
284 item_rec.attribute2 :=Get_Field(l_line,l_delimiter,60);
285 item_rec.attribute3 :=Get_Field(l_line,l_delimiter,61);
286 item_rec.attribute4 :=Get_Field(l_line,l_delimiter,62);
287 item_rec.attribute5 :=Get_Field(l_line,l_delimiter,63);
288 item_rec.attribute6 :=Get_Field(l_line,l_delimiter,64);
289 item_rec.attribute7 :=Get_Field(l_line,l_delimiter,65);
290 item_rec.attribute8 :=Get_Field(l_line,l_delimiter,66);
291 item_rec.attribute9 :=Get_Field(l_line,l_delimiter,67);
292 item_rec.attribute10 :=Get_Field(l_line,l_delimiter,68);
293 item_rec.attribute11 :=Get_Field(l_line,l_delimiter,69);
294 item_rec.attribute12 :=Get_Field(l_line,l_delimiter,70);
295 item_rec.attribute13 :=Get_Field(l_line,l_delimiter,71);
296 item_rec.attribute14 :=Get_Field(l_line,l_delimiter,72);
297 item_rec.attribute15 :=Get_Field(l_line,l_delimiter,73);
298 item_rec.attribute16 :=Get_Field(l_line,l_delimiter,74);
299 item_rec.attribute17 :=Get_Field(l_line,l_delimiter,75);
300 item_rec.attribute18 :=Get_Field(l_line,l_delimiter,76);
301 item_rec.attribute19 :=Get_Field(l_line,l_delimiter,77);
302 item_rec.attribute20 :=Get_Field(l_line,l_delimiter,78);
303 item_rec.attribute21 :=Get_Field(l_line,l_delimiter,79);
304 item_rec.attribute22 :=Get_Field(l_line,l_delimiter,80);
305 item_rec.attribute23 :=Get_Field(l_line,l_delimiter,81);
306 item_rec.attribute24 :=Get_Field(l_line,l_delimiter,82);
307 item_rec.attribute25 :=Get_Field(l_line,l_delimiter,83);
308 item_rec.attribute26 :=Get_Field(l_line,l_delimiter,84);
309 item_rec.attribute27 :=Get_Field(l_line,l_delimiter,85);
310 item_rec.attribute28 :=Get_Field(l_line,l_delimiter,86);
311 item_rec.attribute29 :=Get_Field(l_line,l_delimiter,87);
312 item_rec.attribute30 :=Get_Field(l_line,l_delimiter,88);
313 item_rec.attribute_category :=Get_Field(l_line,l_delimiter,89);
314 item_rec.ont_pricing_qty_source :=
315 TO_NUMBER(TRANSLATE(NVL(Get_Field(l_line,l_delimiter,90),' '),' ','0'));
316 -- TKW 9/11/2003 B2378017
317 item_rec.gl_business_class :=Get_Field(l_line,l_delimiter,91);
318 item_rec.gl_prod_line :=Get_Field(l_line,l_delimiter,92);
319 item_rec.sub_standard_class :=Get_Field(l_line,l_delimiter,93);
320 item_rec.tech_class :=Get_Field(l_line,l_delimiter,94);
321
322
323
324 UTL_FILE.PUT_LINE(l_log_handle,'item_no = '||item_rec.item_no);
325 UTL_FILE.PUT_LINE(l_log_handle,'item_desc1 = '||item_rec.item_desc1);
326 UTL_FILE.PUT_LINE(l_log_handle,'item_desc2 = '||item_rec.item_desc2);
330 UTL_FILE.PUT_LINE(l_log_handle,'dualum_ind = '||item_rec.dualum_ind);
327 UTL_FILE.PUT_LINE(l_log_handle,'alt_itema = '||item_rec.alt_itema);
328 UTL_FILE.PUT_LINE(l_log_handle,'alt_itemb = '||item_rec.alt_itemb);
329 UTL_FILE.PUT_LINE(l_log_handle,'item_um = '||item_rec.item_um);
331 UTL_FILE.PUT_LINE(l_log_handle,'item_um2 = '||item_rec.item_um2);
332 UTL_FILE.PUT_LINE(l_log_handle,'deviation_lo = '||item_rec.deviation_lo);
333 UTL_FILE.PUT_LINE(l_log_handle,'deviation_hi = '||item_rec.deviation_hi);
334 UTL_FILE.PUT_LINE(l_log_handle,'level_code = '||item_rec.level_code);
335 UTL_FILE.PUT_LINE(l_log_handle,'lot_ctl = '||item_rec.lot_ctl);
336 UTL_FILE.PUT_LINE(l_log_handle,'lot_indivisible= '||item_rec.lot_indivisible);
337 UTL_FILE.PUT_LINE(l_log_handle,'sublot_ctl = '||item_rec.sublot_ctl);
338 UTL_FILE.PUT_LINE(l_log_handle,'loct_ctl = '||item_rec.loct_ctl);
339 UTL_FILE.PUT_LINE(l_log_handle,'noninv_ind = '||item_rec.noninv_ind);
340 UTL_FILE.PUT_LINE(l_log_handle,'match_type = '||item_rec.match_type);
341 UTL_FILE.PUT_LINE(l_log_handle,'inactive_ind = '||item_rec.inactive_ind);
342 UTL_FILE.PUT_LINE(l_log_handle,'inv_type = '||item_rec.inv_type);
343 UTL_FILE.PUT_LINE(l_log_handle,'shelf_life = '||item_rec.shelf_life);
344 UTL_FILE.PUT_LINE(l_log_handle,'retest_interval= '||item_rec.retest_interval);
345 UTL_FILE.PUT_LINE(l_log_handle,'item_abccode = '||item_rec.item_abccode);
346 UTL_FILE.PUT_LINE(l_log_handle,'gl_class = '||item_rec.gl_class);
347 UTL_FILE.PUT_LINE(l_log_handle,'inv_class = '||item_rec.inv_class);
348 UTL_FILE.PUT_LINE(l_log_handle,'sales_class = '||item_rec.sales_class);
349 UTL_FILE.PUT_LINE(l_log_handle,'ship_class = '||item_rec.ship_class);
350 UTL_FILE.PUT_LINE(l_log_handle,'frt_class = '||item_rec.frt_class);
351 UTL_FILE.PUT_LINE(l_log_handle,'price_class = '||item_rec.price_class);
352 UTL_FILE.PUT_LINE(l_log_handle,'storage_class = '||item_rec.storage_class);
353 UTL_FILE.PUT_LINE(l_log_handle,'purch_class = '||item_rec.purch_class);
354 UTL_FILE.PUT_LINE(l_log_handle,'tax_class = '||item_rec.tax_class);
355 UTL_FILE.PUT_LINE(l_log_handle,'customs_class = '||item_rec.customs_class);
356 UTL_FILE.PUT_LINE(l_log_handle,'alloc_class = '||item_rec.alloc_class);
357 UTL_FILE.PUT_LINE(l_log_handle,'planning_class = '||item_rec.planning_class);
358 UTL_FILE.PUT_LINE(l_log_handle,'itemcost_class = '||item_rec.itemcost_class);
359 UTL_FILE.PUT_LINE(l_log_handle,'cost_mthd_code = '||item_rec.cost_mthd_code);
360 UTL_FILE.PUT_LINE(l_log_handle,'upc_code = '||item_rec.upc_code);
361 UTL_FILE.PUT_LINE(l_log_handle,'grade_ctl = '||item_rec.grade_ctl);
362 UTL_FILE.PUT_LINE(l_log_handle,'status_ctl = '||item_rec.status_ctl);
363 UTL_FILE.PUT_LINE(l_log_handle,'qc_grade = '||item_rec.qc_grade);
364 UTL_FILE.PUT_LINE(l_log_handle,'lot_status = '||item_rec.lot_status);
365 UTL_FILE.PUT_LINE(l_log_handle,'bulk_id = '||item_rec.bulk_id);
366 UTL_FILE.PUT_LINE(l_log_handle,'pkg_id = '||item_rec.pkg_id);
367 UTL_FILE.PUT_LINE(l_log_handle,'qcitem_no = '||item_rec.qcitem_no);
368 UTL_FILE.PUT_LINE(l_log_handle,'qchold_res_code= '||item_rec.qchold_res_code);
369 UTL_FILE.PUT_LINE(l_log_handle,'expaction_code = '||item_rec.expaction_code);
370 UTL_FILE.PUT_LINE(l_log_handle,'fill_qty = '||item_rec.fill_qty);
371 UTL_FILE.PUT_LINE(l_log_handle,'fill_um = '||item_rec.fill_um);
372 UTL_FILE.PUT_LINE(
373 l_log_handle,'expaction_interval = '||item_rec.expaction_interval);
374 UTL_FILE.PUT_LINE(l_log_handle,'phantom_type = '||item_rec.phantom_type);
375 UTL_FILE.PUT_LINE(l_log_handle,'whse_item_no = '||item_rec.whse_item_no);
376 UTL_FILE.PUT_LINE(
377 l_log_handle,'experimental_ind = '||item_rec.experimental_ind);
378 UTL_FILE.PUT_LINE(l_log_handle,'exported_date = '||item_rec.exported_date);
379 UTL_FILE.PUT_LINE(l_log_handle,'seq_dpnd_class = '||item_rec.seq_dpnd_class);
380 UTL_FILE.PUT_LINE(l_log_handle,'commodity_code = '||item_rec.commodity_code);
381 UTL_FILE.PUT_LINE(l_log_handle,'ic_matr_days = '||item_rec.ic_matr_days);
382 UTL_FILE.PUT_LINE(l_log_handle,'ic_hold_days = '||item_rec.ic_hold_days);
383 UTL_FILE.PUT_LINE(l_log_handle,'user_name = '||item_rec.user_name);
384 UTL_FILE.PUT_LINE(l_log_handle,'Attribute1 = '|| item_rec.attribute1 );
385 UTL_FILE.PUT_LINE(l_log_handle,'Attribute2 = '|| item_rec.attribute2 );
386 UTL_FILE.PUT_LINE(l_log_handle,'Attribute3 = '|| item_rec.attribute3 );
387 UTL_FILE.PUT_LINE(l_log_handle,'Attribute4 = '|| item_rec.attribute4 );
388 UTL_FILE.PUT_LINE(l_log_handle,'Attribute5 = '|| item_rec.attribute5 );
389 UTL_FILE.PUT_LINE(l_log_handle,'Attribute6 = '|| item_rec.attribute6 );
390 UTL_FILE.PUT_LINE(l_log_handle,'Attribute7 = '|| item_rec.attribute7 );
391 UTL_FILE.PUT_LINE(l_log_handle,'Attribute8 = '|| item_rec.attribute8 );
392 UTL_FILE.PUT_LINE(l_log_handle,'Attribute9 = '|| item_rec.attribute9 );
393 UTL_FILE.PUT_LINE(l_log_handle,'Attribute10 = '|| item_rec.attribute10 );
394 UTL_FILE.PUT_LINE(l_log_handle,'Attribute11 = '|| item_rec.attribute11 );
395 UTL_FILE.PUT_LINE(l_log_handle,'Attribute12 = '|| item_rec.attribute12 );
399 UTL_FILE.PUT_LINE(l_log_handle,'Attribute16 = '|| item_rec.attribute16 );
396 UTL_FILE.PUT_LINE(l_log_handle,'Attribute13 = '|| item_rec.attribute13 );
397 UTL_FILE.PUT_LINE(l_log_handle,'Attribute14 = '|| item_rec.attribute14 );
398 UTL_FILE.PUT_LINE(l_log_handle,'Attribute15 = '|| item_rec.attribute15 );
400 UTL_FILE.PUT_LINE(l_log_handle,'Attribute17 = '|| item_rec.attribute17 );
401 UTL_FILE.PUT_LINE(l_log_handle,'Attribute18 = '|| item_rec.attribute18 );
402 UTL_FILE.PUT_LINE(l_log_handle,'Attribute19 = '|| item_rec.attribute19 );
403 UTL_FILE.PUT_LINE(l_log_handle,'Attribute20 = '|| item_rec.attribute20 );
404 UTL_FILE.PUT_LINE(l_log_handle,'Attribute21 = '|| item_rec.attribute21 );
405 UTL_FILE.PUT_LINE(l_log_handle,'Attribute22 = '|| item_rec.attribute22 );
406 UTL_FILE.PUT_LINE(l_log_handle,'Attribute23 = '|| item_rec.attribute23 );
407 UTL_FILE.PUT_LINE(l_log_handle,'Attribute24 = '|| item_rec.attribute24 );
408 UTL_FILE.PUT_LINE(l_log_handle,'Attribute25 = '|| item_rec.attribute25 );
409 UTL_FILE.PUT_LINE(l_log_handle,'Attribute26 = '|| item_rec.attribute26 );
410 UTL_FILE.PUT_LINE(l_log_handle,'Attribute27 = '|| item_rec.attribute27 );
411 UTL_FILE.PUT_LINE(l_log_handle,'Attribute28 = '|| item_rec.attribute28 );
412 UTL_FILE.PUT_LINE(l_log_handle,'Attribute29 = '|| item_rec.attribute29 );
413 UTL_FILE.PUT_LINE(l_log_handle,'Attribute30 = '|| item_rec.attribute30 );
414 UTL_FILE.PUT_LINE(l_log_handle,'Attribute_Category = '|| item_rec.attribute_category );
415 UTL_FILE.PUT_LINE(l_log_handle,'ont_pricing_qty_source = '|| item_rec.ont_pricing_qty_source );
416 -- TKW 9/11/2003 B2378017
417 UTL_FILE.PUT_LINE(l_log_handle,'GL Business Class = '|| item_rec.gl_business_class);
418 UTL_FILE.PUT_LINE(l_log_handle,'GL Product Line = '|| item_rec.gl_prod_line);
419 UTL_FILE.PUT_LINE(l_log_handle,'Substandard Item Class = '|| item_rec.sub_standard_class);
420 UTL_FILE.PUT_LINE(l_log_handle,'Tech Class and Subclass = '|| item_rec.tech_class);
421
422 GMIPAPI.Create_Item
423 ( p_api_version => 3.0
424 , p_init_msg_list => FND_API.G_TRUE
425 , p_commit => FND_API.G_TRUE
426 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
427 , p_item_rec =>item_rec
428 , x_ic_item_mst_row => l_ic_item_mst_row
429 , x_ic_item_cpg_row => l_ic_item_cpg_row
430 , x_return_status =>l_status
431 , x_msg_count =>l_count
432 , x_msg_data =>l_data
433 );
434
435 /* Bug 2513463 - Avoid having Default value of S overwriting errors incurred */
436 /* Added the equals sign to the condition so the errors are returned properly*/
437
438 IF l_count >= 0
439 THEN
440 l_loop_cnt :=1;
441 LOOP
442
443 FND_MSG_PUB.Get(
444 p_msg_index => l_loop_cnt,
445 p_data => l_data,
446 p_encoded => FND_API.G_FALSE,
447 p_msg_index_out => l_dummy_cnt);
448
449 /* dbms_output.put_line('Message ' || l_data ); */
450
451 UTL_FILE.PUT_LINE(l_outfile_handle, 'Record = ' ||l_record_count );
452 UTL_FILE.PUT_LINE(l_outfile_handle, l_data);
453 UTL_FILE.NEW_LINE(l_outfile_handle);
454
455 IF l_status = 'E' OR
456 l_status = 'U'
457 THEN
458 l_data := CONCAT('ERROR ',l_data);
459 END IF;
460
461 UTL_FILE.PUT_LINE(l_log_handle, l_data);
462
463 /* Update error status */
464 IF (l_status = 'U')
465 THEN
466 l_return_status :=l_status;
467 ELSIF (l_status = 'E' and l_return_status <> 'U')
468 THEN
469 l_return_status :=l_status;
470 ELSE
471 l_return_status :=l_status;
472 END IF;
473
474 l_loop_cnt := l_loop_cnt + 1;
475 IF l_loop_cnt > l_count
476 THEN
477 EXIT;
478 END IF;
479
480 END LOOP;
481
482 END IF;
483
484 END LOOP;
485 UTL_FILE.NEW_LINE(l_log_handle);
486 UTL_FILE.PUT_LINE(l_log_handle, 'Process Completed at '
487 || to_char(SYSDATE,'DD-MON-YY HH24:MI:SS'));
488 /* Check if any messages generated. If so then decode and */
489 /* output to error message flat file */
490
491 UTL_FILE.FCLOSE_ALL;
492
493 RETURN l_return_status;
494
495 EXCEPTION
496 WHEN UTL_FILE.INVALID_OPERATION THEN
500
497 /* dbms_output.put_line('Invalid Operation For '|| l_global_file); */
498 UTL_FILE.FCLOSE_ALL;
499 RETURN l_return_status;
501 WHEN UTL_FILE.INVALID_PATH THEN
502 /* dbms_output.put_line('Invalid Path For '|| l_global_file); */
503 UTL_FILE.FCLOSE_ALL;
504 RETURN l_return_status;
505
506 WHEN UTL_FILE.INVALID_MODE THEN
507 /* dbms_output.put_line('Invalid Mode For '|| l_global_file); */
508 UTL_FILE.FCLOSE_ALL;
509 RETURN l_return_status;
510
511 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
512 /* dbms_output.put_line('Invalid File Handle '|| l_global_file); */
513 UTL_FILE.FCLOSE_ALL;
514 RETURN l_return_status;
515
516 WHEN UTL_FILE.WRITE_ERROR THEN
517 /* dbms_output.put_line('Invalid Write Error '|| l_global_file); */
518 UTL_FILE.FCLOSE_ALL;
519 RETURN l_return_status;
520
521 WHEN UTL_FILE.READ_ERROR THEN
522 /* dbms_output.put_line('Invalid Read Error '|| l_global_file); */
523 UTL_FILE.FCLOSE_ALL;
524 RETURN l_return_status;
525
526 WHEN UTL_FILE.INTERNAL_ERROR THEN
527 /* dbms_output.put_line('Internal Error'); */
528 UTL_FILE.FCLOSE_ALL;
529 RETURN l_return_status;
530
531 WHEN OTHERS THEN
532 /* dbms_output.put_line('Other Error'); */
533 UTL_FILE.FCLOSE_ALL;
534 RETURN l_return_status;
535
536 END Create_Item;
537
538 /* +==========================================================================+
539 | FUNCTION NAME |
540 | Get_Field |
541 | |
542 | TYPE |
543 | Public |
544 | |
545 | USAGE |
546 | Get value of field n from a delimited line of ASCII data |
547 | |
548 | DESCRIPTION |
549 | This utility function will return the value of a field from |
550 | a delimited line of ASCII text |
551 | |
552 | PARAMETERS |
553 | p_line IN VARCHAR2 - line of data |
554 | p_delimiter IN VARCHAR2 - Delimiter character |
555 | p_field_no IN NUMBER - Field occurance to be |
556 | returned |
557 | |
558 | RETURNS |
559 | VARCHAR2 - Value of field |
560 | |
561 | HISTORY |
562 | |
563 +==========================================================================+
564 Api end of comments
565 */
566 FUNCTION Get_Field
567 ( p_line IN VARCHAR2
568 , p_delimiter IN VARCHAR2
569 , p_field_no IN NUMBER
570 )
571 RETURN VARCHAR2
572 IS
573 /* Local variables */
574 l_start NUMBER :=0;
575 l_end NUMBER :=0;
576
577 BEGIN
578
579 /* Determine start position */
580 IF p_field_no = 1
581 THEN
582 l_start :=0;
583 ELSE
584 l_start :=INSTR(p_line,p_delimiter,1,(p_field_no - 1));
585 IF l_start = 0
586 THEN
587 RETURN NULL;
588 END IF;
589 END IF;
590
591 /* Determine end position */
592 l_end :=INSTR(p_line,p_delimiter,1,p_field_no);
593 IF l_end = 0
594 THEN
595 l_end := LENGTH(p_line) + 1;
596 END IF;
597
598 /* Extract the field data */
599 IF (l_end - l_start) = 1
600 THEN
601 RETURN NULL;
602 ELSE
603 RETURN SUBSTR(p_line,(l_start + 1),((l_end - l_start) - 1));
604 END IF;
605
606 EXCEPTION
607 WHEN OTHERS
608 THEN
609 RETURN NULL;
610
611 END Get_Field;
612
613 /* +==========================================================================+
614 | FUNCTION NAME |
615 | Get_Substring |
616 | |
617 | TYPE |
618 | Public |
619 | |
620 | USAGE |
621 | Get value of Sub-string from formatted ASCII data file record |
622 | |
623 | DESCRIPTION |
624 | This utility function will return the value of a passed sub-string |
625 | of a formatted ASCII data file record |
626 | |
627 | PARAMETERS |
628 | p_substring IN VARCHAR2 - substring data |
629 | |
630 | RETURNS |
631 | VARCHAR2 - Value of field |
632 | |
633 | HISTORY |
634 | |
635 +==========================================================================+
636 Api end of comments
637 */
638 FUNCTION Get_Substring
639 ( p_substring IN VARCHAR2
640 )
641 RETURN VARCHAR2
642 IS
643 /* Local variables */
644 l_string_value VARCHAR2(200) :=' ';
645
646 BEGIN
647
648 /* Determine start position */
649 l_string_value :=NVL(RTRIM(LTRIM(p_substring)),' ');
650
651 RETURN l_string_value;
652 EXCEPTION
653 WHEN OTHERS
654 THEN
655 RETURN ' ';
656
657 END Get_Substring;
658
659 END GMI_ITEM_WRP;