[Home] [Help]
PACKAGE BODY: APPS.POS_EXCELASN_GRP
Source
1 PACKAGE BODY Pos_ExcelAsn_GRP AS
2 /* $Header: POSGEXAB.pls 120.1 2005/07/21 04:42:42 bgopired noship $ */
3
4 procedure ValidateAndSave_Records( p_api_version in number,
5 x_return_status out NOCOPY varchar2,
6 p_file_name in varchar2,
7 p_headers_tbl in POS_EXCELASN_HEADER_TABLE,
8 p_lines_tbl in POS_EXCELASN_LINE_TABLE,
9 p_lots_tbl in out NOCOPY POS_EXCELASN_LOT_TABLE,
10 p_serials_tbl in out NOCOPY POS_EXCELASN_SERIAL_TABLE,
11 p_lpns_tbl in out NOCOPY POS_EXCELASN_LPN_TABLE,
12 x_group_id out NOCOPY number,
13 x_return_code out NOCOPY varchar2,
14 x_return_msg out NOCOPY varchar2,
15 x_error_tbl out NOCOPY POS_EXCELASN_ERROR_TABLE )
16 IS
17 l_vendor_id number;
18 BEGIN
19
20 select rcv_interface_groups_s.nextval into x_group_id from dual;
21 forall i in 1..p_headers_tbl.header_id.count
22 insert into pos_exasn_headers(
23 lpn_group_id,
24 header_interface_id,
25 group_id,
26 Header_id ,
27 Shipment_Number ,
28 Shipment_Date ,
29 Expected_Receipt_Date ,
30 Bill_of_Lading ,
31 Packing_Slip ,
32 Freight_Carrier_Code ,
33 Num_of_containers ,
34 Waybill_Num ,
35 Gross_weight_UOM ,
36 Gross_Weight ,
37 Net_Weight_UOM ,
38 Net_Weight ,
39 Tar_weight_UOM ,
40 Tar_weight ,
41 Packaging_code ,
42 Carrier_Method ,
43 Special_handling_code ,
44 Hazard_code ,
45 Hazard_Class ,
46 Freight_Terms ,
47 Comments ,
48 Invoice_Number ,
49 Invoice_Date ,
50 Invoice_Amount ,
51 Tax_Amount ,
52 Freight_Amount ,
53 Payment_Terms ,
54 LINE_NUMBER ,
55 attribute_category ,
56 ship_from_location_code,
57 attribute1 ,
58 attribute2 ,
59 attribute3 ,
60 attribute4 ,
61 attribute5 ,
62 attribute6 ,
63 attribute7 ,
64 attribute8 ,
65 attribute9 ,
66 attribute10 ,
67 attribute11 ,
68 attribute12 ,
69 attribute13 ,
70 attribute14 ,
71 attribute15 )
72 values (
73 rcv_interface_groups_s.nextval,
74 rcv_headers_interface_s.nextval,
75 x_group_id,
76 p_headers_tbl.Header_id(i) ,
77 p_headers_tbl.Shipment_Number(i) ,
78 p_headers_tbl.Shipment_Date(i) ,
79 p_headers_tbl.Expected_Receipt_Date(i) ,
80 p_headers_tbl.Bill_of_Lading(i) ,
81 p_headers_tbl.Packing_Slip(i) ,
82 p_headers_tbl.Freight_Carrier_Code(i) ,
83 p_headers_tbl.Num_of_containers(i) ,
84 p_headers_tbl.Waybill_Num(i) ,
85 p_headers_tbl.Gross_weight_UOM(i) ,
86 p_headers_tbl.Gross_Weight(i) ,
87 p_headers_tbl.Net_Weight_UOM(i) ,
88 p_headers_tbl.Net_Weight(i) ,
89 p_headers_tbl.Tar_weight_UOM(i) ,
90 p_headers_tbl.Tar_weight(i) ,
91 p_headers_tbl.Packaging_code(i) ,
92 p_headers_tbl.Carrier_Method(i) ,
93 p_headers_tbl.Special_handling_code(i) ,
94 p_headers_tbl.Hazard_code(i) ,
95 p_headers_tbl.Hazard_Class(i) ,
96 p_headers_tbl.Freight_Terms(i) ,
97 p_headers_tbl.Comments(i) ,
98 p_headers_tbl.Invoice_Number(i) ,
99 p_headers_tbl.Invoice_Date(i) ,
100 p_headers_tbl.Invoice_Amount(i) ,
101 p_headers_tbl.Tax_Amount(i) ,
102 p_headers_tbl.Freight_Amount(i) ,
103 p_headers_tbl.Payment_Terms(i) ,
104 p_headers_tbl.LINE_NUMBER(i) ,
105 p_headers_tbl.attribute_category(i) ,
106 p_headers_tbl.ship_from_location_code(i),
107 p_headers_tbl.attribute1(i),
108 p_headers_tbl.attribute2(i),
109 p_headers_tbl.attribute3(i),
110 p_headers_tbl.attribute4(i),
111 p_headers_tbl.attribute5(i),
112 p_headers_tbl.attribute6(i),
113 p_headers_tbl.attribute7(i),
114 p_headers_tbl.attribute8(i),
115 p_headers_tbl.attribute9(i),
116 p_headers_tbl.attribute10(i),
117 p_headers_tbl.attribute11(i),
118 p_headers_tbl.attribute12(i),
119 p_headers_tbl.attribute13(i),
120 p_headers_tbl.attribute14(i),
121 p_headers_tbl.attribute15(i)
122
123 );
124
125 forall i in 1..p_lines_tbl.header_id.count
126 insert into pos_exasn_lines(
127 interface_transaction_id,
128 Header_ID,
129 Line_ID,
130 Operating_Unit,
131 PO_Number,
132 PO_Revision,
133 PO_Release_Num,
134 PO_Line,
135 PO_Shipment,
136 Quantity,
137 UOM,
138 Bill_of_Lading,
139 Packing_Slip,
140 Num_of_containers,
141 Waybill_Num,
142 Barcode_label,
143 Country_of_Origin,
144 Container_number,
145 Truck_number,
146 Vendor_lot,
147 Comments,
148 LINE_NUMBER,
149 attribute_category,
150 attribute1,
151 attribute2,
152 attribute3,
153 attribute4,
154 attribute5,
155 attribute6,
156 attribute7,
157 attribute8,
158 attribute9,
159 attribute10,
160 attribute11,
161 attribute12,
162 attribute13,
163 attribute14,
164 attribute15,
165 sh_attribute_category,
166 sh_attribute1,
167 sh_attribute2,
168 sh_attribute3,
169 sh_attribute4,
170 sh_attribute5,
171 sh_attribute6,
172 sh_attribute7,
173 sh_attribute8,
174 sh_attribute9,
175 sh_attribute10,
176 sh_attribute11,
177 sh_attribute12,
178 sh_attribute13,
179 sh_attribute14,
180 sh_attribute15,
181 sl_attribute_category,
182 sl_attribute1,
183 sl_attribute2,
184 sl_attribute3,
185 sl_attribute4,
186 sl_attribute5,
187 sl_attribute6,
188 sl_attribute7,
189 sl_attribute8,
190 sl_attribute9,
191 sl_attribute10,
192 sl_attribute11,
193 sl_attribute12,
194 sl_attribute13,
195 sl_attribute14,
196 sl_attribute15
197 )
198 values(
199 rcv_transactions_interface_s.nextval,
200 p_lines_tbl.Header_ID(i),
201 p_lines_tbl.Line_ID(i),
202 p_lines_tbl.Operating_Unit(i),
203 p_lines_tbl.PO_Number(i),
204 p_lines_tbl.PO_Revision(i),
205 p_lines_tbl.PO_Release_Num(i),
206 p_lines_tbl.PO_Line(i),
207 p_lines_tbl.PO_Shipment(i),
208 p_lines_tbl.Quantity(i),
209 p_lines_tbl.UOM(i),
210 p_lines_tbl.Bill_of_Lading(i),
211 p_lines_tbl.Packing_Slip(i),
212 p_lines_tbl.Num_of_containers(i),
213 p_lines_tbl.Waybill_Num(i),
214 p_lines_tbl.Barcode_label(i),
215 p_lines_tbl.Country_of_Origin(i),
216 p_lines_tbl.Container_number(i),
217 p_lines_tbl.Truck_number(i),
218 p_lines_tbl.Vendor_lot(i),
219 p_lines_tbl.Comments(i),
220 p_lines_tbl.LINE_NUMBER(i),
221 p_lines_tbl.attribute_category(i),
222 p_lines_tbl.attribute1(i),
223 p_lines_tbl.attribute2(i),
224 p_lines_tbl.attribute3(i),
225 p_lines_tbl.attribute4(i),
226 p_lines_tbl.attribute5(i),
227 p_lines_tbl.attribute6(i),
228 p_lines_tbl.attribute7(i),
229 p_lines_tbl.attribute8(i),
230 p_lines_tbl.attribute9(i),
231 p_lines_tbl.attribute10(i),
232 p_lines_tbl.attribute11(i),
233 p_lines_tbl.attribute12(i),
234 p_lines_tbl.attribute13(i),
235 p_lines_tbl.attribute14(i),
236 p_lines_tbl.attribute15(i),
237 p_lines_tbl.sh_attribute_category(i),
238 p_lines_tbl.sh_attribute1(i),
239 p_lines_tbl.sh_attribute2(i),
240 p_lines_tbl.sh_attribute3(i),
241 p_lines_tbl.sh_attribute4(i),
242 p_lines_tbl.sh_attribute5(i),
243 p_lines_tbl.sh_attribute6(i),
244 p_lines_tbl.sh_attribute7(i),
245 p_lines_tbl.sh_attribute8(i),
246 p_lines_tbl.sh_attribute9(i),
247 p_lines_tbl.sh_attribute10(i),
248 p_lines_tbl.sh_attribute11(i),
249 p_lines_tbl.sh_attribute12(i),
250 p_lines_tbl.sh_attribute13(i),
251 p_lines_tbl.sh_attribute14(i),
252 p_lines_tbl.sh_attribute15(i),
253 p_lines_tbl.sl_attribute_category(i),
254 p_lines_tbl.sl_attribute1(i),
255 p_lines_tbl.sl_attribute2(i),
256 p_lines_tbl.sl_attribute3(i),
257 p_lines_tbl.sl_attribute4(i),
258 p_lines_tbl.sl_attribute5(i),
259 p_lines_tbl.sl_attribute6(i),
260 p_lines_tbl.sl_attribute7(i),
261 p_lines_tbl.sl_attribute8(i),
262 p_lines_tbl.sl_attribute9(i),
263 p_lines_tbl.sl_attribute10(i),
264 p_lines_tbl.sl_attribute11(i),
265 p_lines_tbl.sl_attribute12(i),
266 p_lines_tbl.sl_attribute13(i),
267 p_lines_tbl.sl_attribute14(i),
268 p_lines_tbl.sl_attribute15(i)
269 );
270 forall i in 1..p_lots_tbl.header_id.count
271 insert into pos_exasn_lots(
272 Header_ID,
273 Line_ID,
274 Lot_ID,
275 Lot_Number,
276 License_Plate_Number,
277 Quantity,
278 LINE_NUMBER ,
279 Vendor_ID,
280 Grade_Code,
281 Status_ID,
282 Change_Date,
283 Age,
284 Retest_Date,
285 Maturity_Date,
286 Item_Size,
287 Color,
288 Volume,
289 Volume_UOM,
290 Place_Of_Origin,
291 Best_By_Date,
292 Length,
293 Length_UOM,
294 Recycled_Content,
295 Thickness,
296 Thickness_UOM,
297 Width,
298 Width_UOM,
299 Curl_Wrinkle_Fold,
300 Supplier_Lot_Number,
301 Territory_Code,
302 Vendor_Name,
303 lot_attribute_category,
304 cattribute1,
305 cattribute2,
306 cattribute3,
307 cattribute4,
308 cattribute5,
309 cattribute6,
310 cattribute7,
311 cattribute8,
312 cattribute9,
313 cattribute10,
314 cattribute11,
315 cattribute12,
316 cattribute13,
317 cattribute14,
318 cattribute15,
319 cattribute16,
320 cattribute17,
321 cattribute18,
322 cattribute19,
323 cattribute20,
324 dattribute1,
325 dattribute2,
326 dattribute3,
327 dattribute4,
328 dattribute5,
329 dattribute6,
330 dattribute7,
331 dattribute8,
332 dattribute9,
333 dattribute10,
334 nattribute1,
335 nattribute2,
336 nattribute3,
337 nattribute4,
338 nattribute5,
339 nattribute6,
340 nattribute7,
341 nattribute8,
342 nattribute9,
343 nattribute10
344 )
345 values(
346 p_lots_tbl.Header_ID(i),
347 p_lots_tbl.Line_ID(i),
348 p_lots_tbl.Lot_ID(i),
349 p_lots_tbl.Lot_Number(i),
350 p_lots_tbl.License_Plate_Number(i),
351 p_lots_tbl.Quantity(i),
352 p_lots_tbl.LINE_NUMBER(i),
353 p_lots_tbl.Vendor_ID(i),
354 P_lots_tbl.Grade_Code(i),
355 p_lots_tbl.Status_ID(i),
356 p_lots_tbl.Change_Date(i),
357 p_lots_tbl.Age(i),
358 p_lots_tbl.Retest_Date(i),
359 p_lots_tbl.Maturity_Date(i),
360 p_lots_tbl.Item_Size(i),
361 p_lots_tbl.Color(i),
362 p_lots_tbl.Volume(i),
363 p_lots_tbl.Volume_UOM(i),
364 p_lots_tbl.Place_Of_Origin(i),
365 p_lots_tbl.Best_By_Date(i),
366 p_lots_tbl.Length(i),
367 p_lots_tbl.Length_UOM(i),
368 p_lots_tbl.Recycled_Content(i),
369 p_lots_tbl.Thickness(i),
370 p_lots_tbl.Thickness_UOM(i),
371 p_lots_tbl.Width(i),
372 p_lots_tbl.Width_UOM(i),
373 p_lots_tbl.Curl_Wrinkle_Fold(i),
374 p_lots_tbl.Supplier_Lot_Number(i),
375 p_lots_tbl.Territory_Code(i),
376 p_lots_tbl.Vendor_Name(i),
377 p_lots_tbl.lot_attribute_category(i),
378 p_lots_tbl.cattribute1(i),
379 p_lots_tbl.cattribute2(i),
380 p_lots_tbl.cattribute3(i),
381 p_lots_tbl.cattribute4(i),
382 p_lots_tbl.cattribute5(i),
383 p_lots_tbl.cattribute6(i),
384 p_lots_tbl.cattribute7(i),
385 p_lots_tbl.cattribute8(i),
386 p_lots_tbl.cattribute9(i),
387 p_lots_tbl.cattribute10(i),
388 p_lots_tbl.cattribute11(i),
389 p_lots_tbl.cattribute12(i),
390 p_lots_tbl.cattribute13(i),
391 p_lots_tbl.cattribute14(i),
392 p_lots_tbl.cattribute15(i),
393 p_lots_tbl.cattribute16(i),
394 p_lots_tbl.cattribute17(i),
395 p_lots_tbl.cattribute18(i),
396 p_lots_tbl.cattribute19(i),
397 p_lots_tbl.cattribute20(i),
398 p_lots_tbl.dattribute1(i),
399 p_lots_tbl.dattribute2(i),
400 p_lots_tbl.dattribute3(i),
401 p_lots_tbl.dattribute4(i),
402 p_lots_tbl.dattribute5(i),
403 p_lots_tbl.dattribute6(i),
404 p_lots_tbl.dattribute7(i),
405 p_lots_tbl.dattribute8(i),
406 p_lots_tbl.dattribute9(i),
407 p_lots_tbl.dattribute10(i),
408 p_lots_tbl.nattribute1(i),
409 p_lots_tbl.nattribute2(i),
410 p_lots_tbl.nattribute3(i),
411 p_lots_tbl.nattribute4(i),
412 p_lots_tbl.nattribute5(i),
413 p_lots_tbl.nattribute6(i),
414 p_lots_tbl.nattribute7(i),
415 p_lots_tbl.nattribute8(i),
416 p_lots_tbl.nattribute9(i),
417 p_lots_tbl.nattribute10(i)
418 );
419 forall i in 1..p_serials_tbl.header_id.count
420 insert into pos_exasn_serials(
421 Header_ID,
422 Line_ID,
423 Lot_ID,
424 Serial_ID,
425 From_Serial,
426 To_Serial,
427 License_Plate_Number,
428 Quantity,
429 LINE_NUMBER,
430 Origination_Date,
431 Status_ID,
432 Territory_Code,
433 serial_attribute_category,
434 cattribute1,
435 cattribute2,
436 cattribute3,
437 cattribute4,
438 cattribute5,
439 cattribute6,
440 cattribute7,
441 cattribute8,
442 cattribute9,
443 cattribute10,
444 cattribute11,
445 cattribute12,
446 cattribute13,
447 cattribute14,
448 cattribute15,
449 cattribute16,
450 cattribute17,
451 cattribute18,
452 cattribute19,
453 cattribute20,
454 dattribute1,
455 dattribute2,
456 dattribute3,
457 dattribute4,
458 dattribute5,
459 dattribute6,
460 dattribute7,
461 dattribute8,
462 dattribute9,
463 dattribute10,
464 nattribute1,
465 nattribute2,
466 nattribute3,
467 nattribute4,
468 nattribute5,
469 nattribute6,
470 nattribute7,
471 nattribute8,
472 nattribute9,
473 nattribute10
474 )
475 values(
476 p_serials_tbl.Header_ID(i),
477 p_serials_tbl.Line_ID(i),
478 p_serials_tbl.Lot_ID(i),
479 p_serials_tbl.Serial_ID(i),
480 p_serials_tbl.From_Serial(i),
481 p_serials_tbl.To_Serial(i),
482 p_serials_tbl.License_Plate_Number(i),
483 p_serials_tbl.Quantity(i),
484 p_serials_tbl.LINE_NUMBER(i),
485 p_serials_tbl.Origination_Date(i),
486 p_serials_tbl.Status_ID(i),
487 p_serials_tbl.Territory_Code(i),
488 p_serials_tbl.serial_attribute_category(i),
489 p_serials_tbl.cattribute1(i),
490 p_serials_tbl.cattribute2(i),
491 p_serials_tbl.cattribute3(i),
492 p_serials_tbl.cattribute4(i),
493 p_serials_tbl.cattribute5(i),
494 p_serials_tbl.cattribute6(i),
495 p_serials_tbl.cattribute7(i),
496 p_serials_tbl.cattribute8(i),
497 p_serials_tbl.cattribute9(i),
498 p_serials_tbl.cattribute10(i),
499 p_serials_tbl.cattribute11(i),
500 p_serials_tbl.cattribute12(i),
501 p_serials_tbl.cattribute13(i),
502 p_serials_tbl.cattribute14(i),
503 p_serials_tbl.cattribute15(i),
504 p_serials_tbl.cattribute16(i),
505 p_serials_tbl.cattribute17(i),
506 p_serials_tbl.cattribute18(i),
507 p_serials_tbl.cattribute19(i),
508 p_serials_tbl.cattribute20(i),
509 p_serials_tbl.dattribute1(i),
510 p_serials_tbl.dattribute2(i),
511 p_serials_tbl.dattribute3(i),
512 p_serials_tbl.dattribute4(i),
513 p_serials_tbl.dattribute5(i),
514 p_serials_tbl.dattribute6(i),
515 p_serials_tbl.dattribute7(i),
516 p_serials_tbl.dattribute8(i),
517 p_serials_tbl.dattribute9(i),
518 p_serials_tbl.dattribute10(i),
519 p_serials_tbl.nattribute1(i),
520 p_serials_tbl.nattribute2(i),
521 p_serials_tbl.nattribute3(i),
522 p_serials_tbl.nattribute4(i),
523 p_serials_tbl.nattribute5(i),
524 p_serials_tbl.nattribute6(i),
525 p_serials_tbl.nattribute7(i),
526 p_serials_tbl.nattribute8(i),
527 p_serials_tbl.nattribute9(i),
528 p_serials_tbl.nattribute10(i)
529 );
530
531 forall i in 1..p_lpns_tbl.header_id.count
532 insert into pos_exasn_lpns(
533 Header_ID,
534 Line_ID,
535 Lpn_row_ID,
536 License_Plate_Number,
537 Parent_LPN,
538 Quantity,
539 Line_Number
540 )
541 values(
542 p_lpns_tbl.Header_ID(i),
543 p_lpns_tbl.Line_ID(i),
544 p_lpns_tbl.Lpn_row_ID(i),
545 p_lpns_tbl.License_Plate_Number(i),
546 p_lpns_tbl.Parent_LPN(i),
547 p_lpns_tbl.Quantity(i),
548 p_lpns_tbl.Line_Number(i)
549
550 );
551
552
553 Pos_ExcelAsn_PVT.ProcessExcelAsn(p_api_version => 1.0,
554 x_return_status => x_return_status,
555 x_return_code => x_return_code,
556 x_return_msg => x_return_msg,
557 x_error_tbl => x_error_tbl,
558 x_user_vendor_id => l_vendor_id);
559
560 if(x_return_status = FND_API.G_RET_STS_SUCCESS) then
561 insert into pos_asn_load(group_id,file_name,creation_date,created_by,vendor_id)
562 values(x_group_id,p_file_name,sysdate,fnd_global.user_id,l_vendor_id);
563 end if;
564 -- x_return_status := 'S'; --furia
565 exception when others then
566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
567 x_return_msg := 'Unexpected error in ValidateAndSave_Records:'||sqlerrm;
568 END ValidateAndSave_Records;
569
570 END Pos_ExcelAsn_GRP;