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