[Home] [Help]
PACKAGE BODY: APPS.WMS_ASN_INTERFACE
Source
1 PACKAGE BODY WMS_ASN_INTERFACE AS
2 /* $Header: WMSASNIB.pls 115.28 2004/05/19 00:23:24 surpatel ship $ */
3
4 g_num_recs_per_group NUMBER := 0;
5 TYPE g_number_tb_tp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6 g_error_rhi_id_tb g_number_tb_tp;
7
8 g_prior_interface_id NUMBER := 0;
9
10 PROCEDURE print_debug(p_err_msg VARCHAR2,
11 p_level NUMBER)
12 IS
13 l_trace_on NUMBER := 0;
14
15
16 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
17 BEGIN
18
19 inv_mobile_helper_functions.tracelog
20 (p_err_msg => p_err_msg,
21 p_module => 'WMS_ASN_INTERFACE',
22 p_level => p_level);
23
24
25 SELECT fnd_profile.value('INV_DEBUG_TRACE')
26 INTO l_trace_on
27 FROM dual;
28
29 IF l_trace_on = 1 THEN
30 FND_FILE.put_line(FND_FILE.LOG, 'WMS_ASN_INTERFACE : ' || p_err_msg);
31 END IF;
32
33 -- dbms_output.put_line(p_err_msg);
34 END print_debug;
35
36
37 PROCEDURE shipment_header_cleanup
38 (p_shipment_header_id NUMBER)
39 IS
40 CURSOR l_lpn_curs IS
41 SELECT lpn_id
42 FROM wms_license_plate_numbers
43 WHERE source_header_id = p_shipment_header_id
44 AND lpn_context = 7
45 AND source_type_id = 1;
46 l_lpn_id NUMBER;
47 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
48 BEGIN
49 IF (l_debug = 1) THEN
50 print_debug('shipment_header_cleanup - p_shipment_header_id : '||p_shipment_header_id, 1);
51 END IF;
52
53 OPEN l_lpn_curs;
54 LOOP
55 FETCH l_lpn_curs INTO l_lpn_id;
56 EXIT WHEN l_lpn_curs%notfound;
57
58 IF (l_debug = 1) THEN
59 print_debug('Clean up this LPN - l_lpn_id : '||l_lpn_id, 4);
60 END IF;
61
62
63 DELETE mtl_serial_numbers
64 WHERE lpn_id = l_lpn_id;
65
66 DELETE wms_lpn_contents
67 WHERE parent_lpn_id = l_lpn_id;
68
69 DELETE wms_license_plate_numbers
70 WHERE lpn_id = l_lpn_id;
71
72 END LOOP;
73
74 CLOSE l_lpn_curs;
75
76 IF (l_debug = 1) THEN
77 print_debug('shipment_header_cleanup - complete', 1);
78 END IF;
79
80
81 EXCEPTION
82 WHEN OTHERS THEN
83 IF (l_debug = 1) THEN
84 print_debug('shipment_header_cleanup - unexpected error ', 1);
85 END IF;
86
87 IF l_lpn_curs%isopen THEN
88 CLOSE l_lpn_curs;
89 END IF;
90
91 IF SQLCODE IS NOT NULL THEN
92 IF (l_debug = 1) THEN
93 print_debug('SQL Error : '||SQLERRM(SQLCODE)||' SQL Error code : '||SQLCODE, 1);
94 END IF;
95 END IF;
96
97 END;
98
99
100 PROCEDURE process
101 (
102 x_return_status OUT NOCOPY VARCHAR2
103 , x_msg_count OUT NOCOPY NUMBER
104 , x_msg_data OUT NOCOPY VARCHAR2
105 , p_interface_transaction_id IN NUMBER )
106 IS
107 CURSOR l_cur_lpn_interface IS
108 -- Bug# 1546081
109 SELECT license_plate_number,
110 lot_number,
111 from_serial_number,
112 to_serial_number,
113 item_description,
114 quantity,
115 uom_code,
116 serial_transaction_intf_id
117 FROM wms_lpn_contents_interface
118 WHERE interface_transaction_id = p_interface_transaction_id;
119
120 CURSOR l_print_lpn_curs
121 (v_group_id NUMBER)
122 IS
123 SELECT DISTINCT wlpn.lpn_id
124 FROM wms_license_plate_numbers wlpn,
125 rcv_transactions_interface rti
126 WHERE wlpn.source_header_id = rti.shipment_header_id
127 AND wlpn.lpn_context = 7
128 AND wlpn.source_type_id = 1
129 AND rti.group_id = v_group_id;
130
131 -- Bug# 1546081
132 CURSOR c_rcv_txn_interface_rec IS
133 SELECT group_id,
134 to_organization_id,
135 item_id,
136 item_revision,
137 shipment_header_id,
138 po_line_id,
139 quantity,
140 unit_of_measure,
141 uom_code,
142 header_interface_id,
143 shipment_num
144 FROM rcv_transactions_interface
145 WHERE interface_transaction_id = p_interface_transaction_id;
146
147 -- Bug# 1546081
148
149 CURSOR l_lpn_interface_UOM_curs IS
150 SELECT uom_code
151 FROM wms_lpn_contents_interface
152 WHERE interface_transaction_id = p_interface_transaction_id;
153
154
155 l_lpn_interface_rec l_cur_lpn_interface%ROWTYPE;
156 l_rcv_txn_interface_rec c_rcv_txn_interface_rec%ROWTYPE;
157 l_lpn_interface_UOM_rec l_lpn_interface_UOM_curs%ROWTYPE;
158 l_lpn_rec wms_container_pub.lpn;
159 l_lpn_id NUMBER;
160 l_return_status VARCHAR2(1) := FND_API.g_ret_sts_success;
161 l_msg_count NUMBER;
162 l_msg_data VARCHAR2(400);
163 l_inventory_item_id NUMBER;
164 l_revision VARCHAR2(30);
165 l_organization_id NUMBER;
166 l_expiration_date DATE := Sysdate;
167 l_object_id NUMBER;
168 l_api_version NUMBER := 1.0;
169 l_shipment_header_id NUMBER;
170 l_po_line_id NUMBER;
171 l_input_param_rec INV_LABEL.input_parameter_rec_type;
172 l_label_status VARCHAR2(500);
173 l_lot_control_code NUMBER;
174 l_serial_control_code NUMBER;
175 l_shelf_life_code NUMBER;
176 l_shelf_life_days NUMBER;
177 l_total_quantity NUMBER;
178 l_is_header_error BOOLEAN := FALSE;
179 l_progress VARCHAR2(10);
180 l_total_quantity_rti NUMBER;
181 l_num_recs_per_group NUMBER;
182
183 -- For Asn Details Report
184 l_asn_details_rec inv_cr_asn_details.rcv_intf_rec_tp;
185
186 /* Bug 2224521 */
187 l_valid_lot BOOLEAN := TRUE;
188
189 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
190 BEGIN
191 IF (l_debug = 1) THEN
192 print_debug('Enter Process 10', 1);
193 print_debug('p_interface_transaction_id = ' || p_interface_transaction_id, 4);
194 END IF;
195
196 l_progress := '10';
197
198 -- For INV/WMS Patchset J just return
199 IF ((inv_rcv_common_apis.g_inv_patch_level >= inv_rcv_common_apis.g_patchset_j) AND
200 (inv_rcv_common_apis.g_wms_patch_level >= inv_rcv_common_apis.g_patchset_j)) THEN
201 IF (l_debug = 1) THEN
202 print_debug('Insatnce on patchset J or higher, returning from this api', 1);
203 END IF;
204 return;
205 END IF;
206
207 SAVEPOINT process_lpn_intf;
208 -- Initialize API return status to success
209 x_return_status := FND_API.G_RET_STS_SUCCESS;
210
211 -- Bug# 1546081
212 /*
213 BEGIN
214 SELECT *
215 INTO l_rcv_txn_interface_rec
216 FROM rcv_transactions_interface
217 WHERE interface_transaction_id = p_interface_transaction_id;
218 EXCEPTION
219 WHEN OTHERS THEN
220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
221 END;
222 */
223
224 l_progress := '20';
225
226 IF g_prior_interface_id = p_interface_transaction_id THEN
227 IF (l_debug = 1) THEN
228 print_debug('The LPN related with Interface ID is already processed ', 4);
229 END IF;
230 return;
231 ELSE
232 g_prior_interface_id := p_interface_transaction_id ;
233 END IF;
234
235 OPEN c_rcv_txn_interface_rec;
236 FETCH c_rcv_txn_interface_rec INTO l_rcv_txn_interface_rec;
237
238 IF (l_debug = 1) THEN
239 print_debug('Group_id = '||l_rcv_txn_interface_rec.group_id, 4);
240 END IF;
241
242 /*Start of fix for Bug 1900958 */
243 IF c_rcv_txn_interface_rec%NOTFOUND THEN
244 CLOSE c_rcv_txn_interface_rec;
245 IF (l_debug = 1) THEN
246 print_debug('No record exists in RCV_TRANSACTIONS_INTERFACE for this interface_transaction_ID', 4);
247 END IF;
248
249 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
250 END IF;
251 /*End of fix for Bug 1900958 */
252
253 CLOSE c_rcv_txn_interface_rec;
254 -- End Bug# 1546081
255
256 l_progress := '30';
257
258 -- IF g_num_recs_per_group = 0 THEN
259 -- if g_num_recs_per_group > l_num_recs_per_group
260 -- the rti is cascaded and we don't need to do count
261 -- from wms_lpn_contents_interface again as
262 -- the contents of wms_lpn_contents_interface does not
263 -- exist anymore and already processed.
264
265 SELECT COUNT(*)
266 INTO l_num_recs_per_group
267 FROM wms_lpn_contents_interface
268 WHERE group_id = l_rcv_txn_interface_rec.group_id
269 ;
270
271 IF (l_debug = 1) THEN
272 print_debug('There are '|| to_char(l_num_recs_per_group) || ' records in WLPNC for this group_ID : '|| l_rcv_txn_interface_rec.group_id, 4);
273 END IF;
274 l_progress := '40';
275
276 -- one rti record gets processed
277 g_num_recs_per_group := g_num_recs_per_group + 1;
278
279 FOR i IN 1..g_error_rhi_id_tb.COUNT LOOP
280 IF l_rcv_txn_interface_rec.shipment_header_id = g_error_rhi_id_tb(i) THEN
281 IF (l_debug = 1) THEN
282 print_debug('This interface_transaction_ID : '||p_interface_transaction_id||' belongs to this RCV_SHIPMENT_HEADER record : '||l_rcv_txn_interface_rec.shipment_header_id || ' that has errored out.', 4);
283 END IF;
284 l_is_header_error := TRUE;
285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
286 END IF;
287 END LOOP;
288
289 --default following values from RTI
290 l_organization_id := l_rcv_txn_interface_rec.to_organization_id;
291 l_inventory_item_id := l_rcv_txn_interface_rec.item_id; --??
292 l_revision := l_rcv_txn_interface_rec.item_revision;
293 l_shipment_header_id := l_rcv_txn_interface_rec.shipment_header_id;
294 l_po_line_id := l_rcv_txn_interface_rec.po_line_id;
295
296
297 l_progress := '50';
298
299 -- validate the total quantity within WMS_LPN_CONTENTS_INTERFACE for one RTI record
300 -- matches RTI quantity
301
302 --
303 -- Validate the quantity based on group and item
304 --
305
306 if (g_num_recs_per_group = l_num_recs_per_group)
307 then
308 IF (l_debug = 1) THEN
309 print_debug ('Quantity Validation at the end of the LPN ', 4);
310 print_debug(' Total quantity LPN '|| l_total_quantity, 4);
311 print_debug(' Total quantity RTI '|| l_total_quantity_rti, 4);
312 END IF;
313
314 for c_group in (
315 SELECT group_id,
316 item_id,
317 item_num,
318 validation_flag,
319 processing_status_code,
320 PO_LINE_LOCATION_ID,
321 SHIPMENT_NUM
322 FROM rcv_transactions_interface where group_id = l_rcv_txn_interface_rec.group_id
323 and item_id is not null )
324 loop
325 IF (l_debug = 1) THEN
326 print_debug(' 1. Group Id' || c_group.group_id , 4);
327 print_debug(' 2. Item Id' || c_group.item_id , 4);
328 print_debug(' 3. Item Num' || c_group.item_num , 4);
329 END IF;
330
331 BEGIN
332
333 SELECT nvl(SUM(quantity),0)
334 INTO l_total_quantity
335 FROM wms_lpn_contents_interface
336 WHERE group_id = c_group.group_id
337 AND item_num = c_group.item_num
338 ;
339
340 SELECT nvl(SUM(quantity),0)
341 INTO l_total_quantity_rti
342 FROM rcv_transactions_interface
343 WHERE group_id = c_group.group_id
344 AND item_id = c_group.item_id
345 ;
346 EXCEPTION
347 WHEN OTHERS THEN
348 IF (l_debug = 1) THEN
349 print_debug('Unexpected error while calculating total quantity within WMS_LPN_CONTENTS_INTERFACE.', 4);
350 END IF;
351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
352 END;
353
354 IF l_total_quantity < l_total_quantity_rti THEN
355 IF (l_debug = 1) THEN
356 print_debug('Total quantity within WMS_LPN_CONTENTS_INTERFACE shpuld not be less than quantity in RCV_TRANSACTIONS_INTERFACE for this interface_transaction_id at any time : ' || p_interface_transaction_id, 4);
357 print_debug('l_total_quantity = ' || l_total_quantity, 4);
358 print_debug('l_total_quantity_rti = ' || l_total_quantity_rti, 4);
359 print_debug('l_rcv_txn_interface_rec.quantity = '|| l_rcv_txn_interface_rec.quantity, 4);
360 END IF;
361 RAISE FND_API.G_EXC_ERROR;
362 END IF;
363
364 end loop;
365
366 end if;
367
368 -- validate UOM Code within WMS_LPN_CONTENTS_INTERFACE for one RTI record
369 -- matches RTI UOM Code
370
371 BEGIN
372 SELECT uom_code
373 INTO l_rcv_txn_interface_rec.uom_code
374 FROM mtl_units_of_measure
375 WHERE unit_of_measure = l_rcv_txn_interface_rec.unit_of_measure;
376 EXCEPTION
377 WHEN OTHERS THEN
378 IF (l_debug = 1) THEN
379 print_debug('Unexpected error in UOM to UOM code converstion ' || p_interface_transaction_id, 4);
380 END IF;
381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
382 END;
383
384 OPEN l_lpn_interface_uom_curs;
385
386 LOOP
387 FETCH l_lpn_interface_uom_curs INTO l_lpn_interface_uom_rec;
388 EXIT WHEN l_lpn_interface_uom_curs%notfound;
389
390 IF l_lpn_interface_UOM_rec.uom_code IS NOT NULL AND
391 l_rcv_txn_interface_rec.uom_code <> l_lpn_interface_UOM_rec.uom_code THEN
392 IF (l_debug = 1) THEN
393 print_debug('UOM Code in WMS_LPN_Content_Interface does not match the UOM Code in RCV_TRANSACTIONS_INTERFACE for this interface_transaction_id : ' || p_interface_transaction_id, 4);
394 END IF;
395 RAISE FND_API.G_EXC_ERROR;
396 END IF;
397
398 END LOOP;
399
400 CLOSE l_lpn_interface_uom_curs;
401
402 l_progress := '55';
403
404 OPEN l_cur_lpn_interface;
405 -- loop through WMS_LPN_CONTENTS_INTERFACE records for this txn_intf_id
406 LOOP
407 FETCH l_cur_lpn_interface INTO l_lpn_interface_rec;
408 EXIT WHEN l_cur_lpn_interface%notfound;
409
410 -- create LPN
411 l_lpn_rec.license_plate_number := l_lpn_interface_rec.license_plate_number;
412 IF (l_debug = 1) THEN
413 print_debug('Process WMS_LPN_Content_Interface record ' ||l_lpn_interface_rec.license_plate_number, 4);
414 END IF;
415 l_progress := '60';
416
417
418 IF wms_container_pub.validate_lpn(l_lpn_rec) = inv_validate.f THEN
419 wms_container_pub.create_lpn
420 (p_api_version => l_api_version,
421 x_return_status => l_return_status,
422 x_msg_count => l_msg_count,
423 x_msg_data => l_msg_data,
427 p_source => 7,
424 p_lpn => l_lpn_interface_rec.license_plate_number,
425 p_organization_id => l_organization_id,
426 x_lpn_id => l_lpn_id,
428 p_source_type_id => 1, -- PO
429 p_source_header_id => l_shipment_header_id,
430 p_source_name => l_rcv_txn_interface_rec.shipment_num -- Need to be passed for clearing lpn contents during confirm receive
431 );
432
433
434 -- IF l_return_status <> FND_API.g_ret_sts_success THEN
435 IF ( (l_return_status = FND_API.g_ret_sts_error) or (l_return_status = FND_API.g_ret_sts_unexp_error) ) THEN
436 x_return_status := l_return_status;
437 IF (l_debug = 1) THEN
438 print_debug('Failed to create LPN. ', 4);
439 END IF;
440 l_progress := '65';
441
442 FND_MESSAGE.SET_NAME('WMS', 'WMS_LPN_GENERATION_FAIL');
443 FND_MSG_PUB.ADD;
444 RAISE FND_API.g_exc_error;
445
446 END IF;
447
448 ELSE -- lpn exists
449 IF (l_debug = 1) THEN
450 print_debug('This is an exisiting License Plate Number in the system. ', 4);
451 END IF;
452 l_progress := '70';
453
454 BEGIN
455 SELECT lpn_id
456 INTO l_lpn_id
457 FROM wms_license_plate_numbers
458 WHERE license_plate_number = l_lpn_interface_rec.license_plate_number
459 AND source_header_id = l_shipment_header_id
460 AND lpn_context = 7
461 AND source_type_id = 1;
462
463 IF (l_debug = 1) THEN
464 print_debug('But it is for the shipment that is currently importing. OK to proceed.', 4);
465 END IF;
466
467 EXCEPTION
468 WHEN no_data_found THEN
469 IF (l_debug = 1) THEN
470 print_debug('And this License Plate Number is not for this shipment. ', 4);
471 END IF;
472 l_progress := '80';
473 -- this existing LPN is not for this shipment
474 FND_MESSAGE.SET_NAME('WMS', 'WMS_ASN_INTF_LPN_EXIST');
475 FND_MSG_PUB.ADD;
476 RAISE FND_API.g_exc_error;
477 WHEN OTHERS THEN
478 IF (l_debug = 1) THEN
479 print_debug('Unexpected error while checking if the existing LPN matches the current shipment. ', 4);
480 END IF;
481 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
482 END;
483
484 l_progress := '80';
485 END IF;
486
487 IF (l_debug = 1) THEN
488 print_debug('LPN has been created. ', 4);
489 END IF;
490 l_progress := '90';
491
492 BEGIN
493 SELECT lot_control_code,
494 serial_number_control_code,
495 shelf_life_code,
496 shelf_life_days
497 INTO l_lot_control_code,
498 l_serial_control_code,
499 l_shelf_life_code,
500 l_shelf_life_days
501 FROM mtl_system_items
502 WHERE inventory_item_id = l_inventory_item_id
503 AND organization_id = l_organization_id;
504
505 EXCEPTION
506 WHEN OTHERS THEN
507 IF (l_debug = 1) THEN
508 print_debug('Item validation failed unexpectedly while querying lot_control_code, etc. ', 4);
509 END IF;
510 l_progress := '100';
511 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
512 END;
513
514
515 -- validate/insert lot
516 IF l_lpn_interface_rec.lot_number IS NOT NULL THEN
517 IF (l_debug = 1) THEN
518 print_debug('Lot number exists in WMS_LPN_CONTENTS_INTERFACE : ' || l_lpn_interface_rec.lot_number, 4);
519 END IF;
520 l_progress := '110';
521
522 IF l_lot_control_code <> 2 THEN
523 IF (l_debug = 1) THEN
524 print_debug('This item is not lot controlled though. Proceed procesing as non-lot controlled.', 4);
525 END IF;
526 l_progress := '120';
527 l_lpn_interface_rec.lot_number := NULL;
528
529 ELSE
530
531 /***********************************************************************
532 Calling New API to validate Lot Attributes
533 This is replaced by the new call below.
534 *************************************************************************/
535 /*
536 inv_lot_api_pub.validate_unique_lot
537 (p_org_id => l_organization_id,
538 p_inventory_item_id => l_inventory_item_id,
539 p_lot_uniqueness => NULL,
540 p_auto_lot_number => l_lpn_interface_rec.lot_number)
541 THEN
542
543 IF l_shelf_life_code = 2 THEN
544 l_expiration_date := l_expiration_date + l_shelf_life_days;
545 IF (l_debug = 1) THEN
549 END IF;
546 print_debug('Item is shelf life controlled, expiration date : ' || l_expiration_date, 4);
547 END IF;
548 l_progress := '125';
550
551 IF (l_debug = 1) THEN
552 print_debug('About to insert a lot - nothing will happen if this lot/item combination already exists', 4);
553 END IF;
554 l_progress := '130';
555
556 inv_lot_api_pub.insertlot
557 (p_api_version => l_api_version,
558 p_inventory_item_id => l_inventory_item_id,
559 p_organization_id => l_organization_id,
560 p_lot_number => l_lpn_interface_rec.lot_number,
561 p_expiration_date => l_expiration_date,
562 x_object_id => l_object_id,
563 x_return_status => l_return_status,
564 x_msg_count => l_msg_count,
565 x_msg_data => l_msg_data);
566
567
568 IF l_return_status <> FND_API.g_ret_sts_success THEN
569 IF (l_debug = 1) THEN
570 print_debug('Lot insertion failed. ', 4);
571 END IF;
572 l_progress := '140';
573
574 FND_MESSAGE.SET_NAME('WMS', 'WMS_ASN_INTF_INST_LOT_FAIL');
575 FND_MSG_PUB.ADD;
576 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
577
578
579 END IF;
580 */
581 --Call to New Lot Validation API here
582
583 IF (l_debug = 1) THEN
584 print_debug('Before Calling procedure to validate Lot' , 4);
585 END IF;
586
587 -- Check for Lot UniqueNess
588 /* Bug 2224521 */
589 --
590 -- Call the API provided in Standard Lot API.
591 -- This is to ensure that no other transactions
592 -- is accessing the same Lot while this trnsaction is running
593 --
594 IF (l_debug = 1) THEN
595 print_debug('Before Calling Validate Unique Lot API ', 4);
596 END IF;
597
598 l_valid_lot := inv_lot_api_pub.validate_unique_lot(l_organization_id,
599 l_inventory_item_id,
600 '',
601 l_lpn_interface_rec.lot_number);
602 if l_valid_lot then
603 null;
604 else
605 fnd_message.set_name('INV','INV_INT_LOTUNIQEXP');
606 fnd_msg_pub.add;
607 x_return_status := FND_API.G_RET_STS_ERROR;
608 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
609 end if;
610
611 wms_asn_lot_att.validate_lot ( l_return_status,l_msg_count,
612 l_msg_data,p_interface_transaction_id );
613
614 IF (l_debug = 1) THEN
615 print_debug('After Calling procedure to validate Lot' , 4);
616 END IF;
617
618 IF l_return_status <> FND_API.g_ret_sts_success THEN
619 IF (l_debug = 1) THEN
620 print_debug('Creating Lot number failed - Existing Lot', 4);
621 END IF;
622 l_progress := '130';
623
624 FND_MESSAGE.SET_NAME('WMS', 'WMS_ASN_INTF_INST_LOT_FAIL');
625 FND_MSG_PUB.ADD;
626
627 /* Bug 2224521 -- For Existing Lots we don't raise an error */
628 -- RAISE FND_API.g_exc_error;
629
630 END IF;
631
632
633 /************************************************************************
634 This part is not needed anymore as lot unique ness check is taken care
635 by the above call
636 ************************************************************************/
637
638 /*
639 ELSE
640 IF (l_debug = 1) THEN
641 print_debug('Lot uniqueness check failed ', 4);
642 END IF;
643 l_progress := '150';
644 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
645 */
646
647 END IF;
648
649
650 ELSIF l_lpn_interface_rec.lot_number IS NULL
651 AND l_lot_control_code = 2 THEN
652 IF (l_debug = 1) THEN
653 print_debug('Required lot number is not passed for a lot controlled item ', 4);
654 END IF;
655 l_progress := '160';
656 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
657 END IF;
658
659
660 -- validate/insert serial
661 IF l_lpn_interface_rec.from_serial_number IS NOT NULL
662 AND l_lpn_interface_rec.to_serial_number IS NOT NULL THEN
663
664 IF (l_debug = 1) THEN
665 print_debug('Serial numbers exist in WMS_LPN_CONTENTS_INTERFACE - FROM : ' || l_lpn_interface_rec.from_serial_number || ' TO : ' || l_lpn_interface_rec.to_serial_number, 4);
666 END IF;
667 l_progress := '170';
668
669 IF l_serial_control_code <> 2 AND
670 l_serial_control_code <> 5 THEN
671 IF (l_debug = 1) THEN
672 print_debug('This item is not serial controlled though. Proceed procesing as non-serial controlled.', 4);
673 END IF;
674 l_progress := '180';
675 l_lpn_interface_rec.from_serial_number := NULL;
676 l_lpn_interface_rec.to_serial_number := NULL;
677 ELSE
678
679 /* This is Replaced by the New Serial Validation API
680
681 inv_serial_number_pub.insert_range_serial
682 (p_api_version => l_api_version,
686 p_to_serial_number => l_lpn_interface_rec.to_serial_number,
683 p_inventory_item_id => l_inventory_item_id,
684 p_organization_id => l_organization_id,
685 p_from_serial_number => l_lpn_interface_rec.from_serial_number,
687 p_initialization_date => Sysdate,
688 p_completion_date => NULL,
689 p_ship_date => NULL,
690 p_revision => l_revision,
691 p_lot_number => l_lpn_interface_rec.lot_number,
692 p_current_locator_id => NULL,
693 p_subinventory_code => NULL,
694 p_trx_src_id => NULL,
695 p_unit_vendor_id => NULL,
696 p_vendor_lot_number => NULL,
697 p_vendor_serial_number => NULL,
698 p_receipt_issue_type => NULL,
699 p_txn_src_id => NULL,
700 p_txn_src_name => NULL,
701 p_txn_src_type_id => NULL,
702 p_transaction_id => NULL,
703 p_current_status => 5,
704 p_parent_item_id => NULL,
705 p_parent_serial_number => NULL,
706 p_cost_group_id => NULL,
707 p_transaction_action_id => 27,
708 p_transaction_temp_id => NULL,
709 p_status_id => NULL,
710 p_inspection_status => NULL,
711 x_object_id => l_object_id,
712 x_return_status => x_return_status,
713 x_msg_count => x_msg_count,
714 x_msg_data => x_msg_data);
715 */
716
717 wms_asn_lot_att.insert_range_serial
718 (p_inventory_item_id => l_inventory_item_id,
719 p_organization_id => l_organization_id,
720 p_from_serial_number => l_lpn_interface_rec.from_serial_number,
721 p_to_serial_number => l_lpn_interface_rec.to_serial_number,
722 p_initialization_date => Sysdate,
723 p_completion_date => NULL,
724 p_ship_date => NULL,
725 p_revision => l_revision,
726 p_lot_number => l_lpn_interface_rec.lot_number,
727 p_current_locator_id => NULL,
728 p_subinventory_code => NULL,
729 p_trx_src_id => NULL,
730 p_unit_vendor_id => NULL,
731 p_vendor_lot_number => NULL,
732 p_vendor_serial_number => NULL,
733 p_receipt_issue_type => NULL,
734 p_txn_src_id => NULL,
735 p_txn_src_name => NULL,
736 p_txn_src_type_id => NULL,
737 p_transaction_id => NULL,
738 p_current_status => 5,
739 p_parent_item_id => NULL,
740 p_parent_serial_number => NULL,
741 p_cost_group_id => NULL,
742 p_serial_transaction_intf_id => l_lpn_interface_rec.serial_transaction_intf_id,
743 p_status_id => NULL,
744 p_inspection_status => NULL,
745 x_object_id => l_object_id,
746 x_return_status => l_return_status,
747 x_msg_count => x_msg_count,
748 x_msg_data => x_msg_data);
749
750 --IF l_return_status <> FND_API.g_ret_sts_success THEN
751 IF ( (l_return_status = FND_API.g_ret_sts_error) or (l_return_status = FND_API.g_ret_sts_unexp_error) ) THEN
752 x_return_status := l_return_status;
753 IF (l_debug = 1) THEN
754 print_debug('Creating serial number failed', 4);
755 END IF;
756 l_progress := '190';
757
758 FND_MESSAGE.SET_NAME('WMS', 'WMS_ASN_INTF_INST_SERIAL_FAIL');
759 FND_MSG_PUB.ADD;
760 RAISE FND_API.g_exc_error;
761 END IF;
762 END IF;
763
764 ELSIF (l_lpn_interface_rec.from_serial_number IS NULL OR
765 l_lpn_interface_rec.to_serial_number IS NULL) AND
766 (l_serial_control_code = 2 OR
767 l_serial_control_code = 5) THEN
768 IF (l_debug = 1) THEN
769 print_debug('Required serial number is not passed for a serial controlled item ', 4);
770 END IF;
771 l_progress := '200';
772
773 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
774 END IF;
775
776 IF (l_debug = 1) THEN
777 print_debug('Pack this interface record into LPN ', 4);
778 END IF;
779 l_progress := '210';
780
781 -- pack LPN
782 WMS_Container_PUB.PackUnpack_Container
783 (p_api_version => l_api_version,
784 x_return_status => l_return_status,
785 x_msg_count => l_msg_count,
786 x_msg_data => l_msg_data,
787 p_lpn_id => l_lpn_id,
788 p_content_lpn_id => NULL,
789 p_content_item_id => l_inventory_item_id,
790 p_content_item_desc => l_lpn_interface_rec.item_description,
791 p_revision => l_revision,
792 p_lot_number => l_lpn_interface_rec.lot_number,
793 p_from_serial_number => l_lpn_interface_rec.from_serial_number,
794 p_to_serial_number => l_lpn_interface_rec.to_serial_number,
795 p_quantity => l_lpn_interface_rec.quantity,
796 p_uom => l_rcv_txn_interface_rec.uom_code,
797 p_organization_id => l_organization_id,
798 p_subinventory => NULL,
799 p_locator_id => NULL,
800 p_enforce_wv_constraints => NULL,
801 p_operation => 1, -- pack
802 p_cost_group_id => NULL,
803 p_source_type_id => 1, -- PO
804 p_source_header_id => NULL,
805 p_source_name => NULL,
806 p_source_line_id => l_po_line_id,
807 p_source_line_detail_id => NULL,
808 p_homogeneous_container => NULL,
809 p_match_locations => NULL,
813 p_match_mtl_status => NULL
810 p_match_lpn_context => NULL,
811 p_match_lot => NULL,
812 p_match_cost_groups => NULL,
814 );
815
816 -- IF l_return_status <> FND_API.g_ret_sts_success THEN
817 IF ( (l_return_status = FND_API.g_ret_sts_error) or (l_return_status = FND_API.g_ret_sts_unexp_error) ) THEN
818 x_return_status := l_return_status;
819 IF (l_debug = 1) THEN
820 print_debug('LPN Packing failed ', 4);
821 END IF;
822 l_progress := '220';
823
824 FND_MESSAGE.SET_NAME('WMS', 'WMS_ASN_INTF_PACK_LPN_FAIL');
825 FND_MSG_PUB.ADD;
826 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
827 END IF;
828
829 END LOOP;
830
831 l_progress := '230';
832
833 CLOSE l_cur_lpn_interface;
834
835 l_progress := '240';
836
837
838
839 -- Adding WMS ASN Discrepancy Report Specific Call.
840 l_progress := '241';
841 -- Initialize Values
842 l_asn_details_rec.group_id := l_rcv_txn_interface_rec.group_id ;
843 l_asn_details_rec.to_organization_id := l_rcv_txn_interface_rec.to_organization_id ;
844 l_asn_details_rec.item_id := l_rcv_txn_interface_rec.item_id ;
845 l_asn_details_rec.item_revision := l_rcv_txn_interface_rec.item_revision ;
846 l_asn_details_rec.shipment_header_id := l_rcv_txn_interface_rec.shipment_header_id ;
847 l_asn_details_rec.po_line_id := l_rcv_txn_interface_rec.po_line_id ;
848 l_asn_details_rec.quantity := l_rcv_txn_interface_rec.quantity ;
849 l_asn_details_rec.unit_of_measure := l_rcv_txn_interface_rec.unit_of_measure;
850 l_asn_details_rec.uom_code := l_rcv_txn_interface_rec.uom_code ;
851 l_asn_details_rec.header_interface_id := l_rcv_txn_interface_rec.header_interface_id;
852
853
854 inv_cr_asn_details.create_asn_details_from_intf(
855 l_asn_details_rec,
856 x_return_status,
857 x_msg_data
858 );
859
860
861
862 -- Commented this Part to do the Quantity Check for wms_lpn_contents_interface
863 --
864 -- DELETE wms_lpn_contents_interface
865 -- WHERE interface_transaction_id = p_interface_transaction_id;
866
867 l_progress := '250';
868
869 IF (l_debug = 1) THEN
870 print_debug('Process - after processing WMS_LPN_CONTENTS_INTERFACE - g_num_recs_per_group = ' || g_num_recs_per_group, 4);
871 END IF;
872
873 l_progress := '260';
874
875 IF g_num_recs_per_group = l_num_recs_per_group THEN -- this is the last record in this group for LPN, call printing
876
877 IF (l_debug = 1) THEN
878 print_debug('Process - Processing WMS_LPN_CONTENTS_INTERFACE - Before Deletion from wms_lpn_contents_interface ' , 4);
879 END IF;
880
881 DELETE wms_lpn_contents_interface
882 WHERE group_id = l_rcv_txn_interface_rec.group_id ;
883
884 OPEN l_print_lpn_curs (l_rcv_txn_interface_rec.group_id);
885
886 LOOP
887 FETCH l_print_lpn_curs
888 INTO l_input_param_rec(1).lpn_id;
889 EXIT WHEN l_print_lpn_curs%notfound;
890
891 END LOOP;
892
893 CLOSE l_print_lpn_curs;
894
895 IF (l_debug = 1) THEN
896 print_debug('Process - before calling inv_label.print_label', 4);
897 END IF;
898
899 l_progress := '270';
900
901 inv_label.print_label
902 (x_return_status => l_return_status
903 , x_msg_count => l_msg_count
904 , x_msg_data => l_msg_data
905 , x_label_status => l_label_status
906 , p_api_version => 1.0
907 , p_print_mode =>2
908 , p_business_flow_code => 25
909 , p_input_param_rec => l_input_param_rec
910 );
911
912 IF (l_debug = 1) THEN
913 print_debug('Process - after calling inv_label.print_label', 1);
914 END IF;
915 l_progress := '280';
916
917 END IF;
918
919 IF (l_debug = 1) THEN
920 print_debug('Process complete', 1);
921 END IF;
922
923
924
925 EXCEPTION
926 WHEN FND_API.g_exc_error THEN
927 IF (l_debug = 1) THEN
928 print_debug('Process - expected error happened - l_progress : '||l_progress, 1);
929 END IF;
930
931 IF l_is_header_error <> TRUE THEN
932 g_error_rhi_id_tb(g_error_rhi_id_tb.COUNT + 1) := l_rcv_txn_interface_rec.shipment_header_id;
933 shipment_header_cleanup(l_rcv_txn_interface_rec.shipment_header_id);
934
935 END IF;
936
937 ROLLBACK TO process_lpn_intf;
938
939 IF l_cur_lpn_interface%isopen THEN
940 CLOSE l_cur_lpn_interface;
941 END IF;
942 IF l_print_lpn_curs%isopen THEN
943 CLOSE l_print_lpn_curs;
944 END IF;
945 IF l_lpn_interface_uom_curs%isopen THEN
946 CLOSE l_lpn_interface_uom_curs;
947 END IF;
948 x_return_status := FND_API.G_RET_STS_ERROR;
949 FND_MSG_PUB.Count_And_Get
950 (p_count => x_msg_count,
951 p_data => x_msg_data
952 );
953
954
955 WHEN FND_API.g_exc_unexpected_error THEN
956 IF (l_debug = 1) THEN
957 print_debug('Process - unexpected error happened - l_progress : '||l_progress, 1);
958 END IF;
959
960 IF l_is_header_error <> TRUE THEN
961 g_error_rhi_id_tb(g_error_rhi_id_tb.COUNT + 1) := l_rcv_txn_interface_rec.shipment_header_id;
962 shipment_header_cleanup(l_rcv_txn_interface_rec.shipment_header_id);
963 END IF;
964
965 ROLLBACK TO process_lpn_intf;
966
967 IF l_cur_lpn_interface%isopen THEN
968 CLOSE l_cur_lpn_interface;
969 END IF;
970 IF l_print_lpn_curs%isopen THEN
971 CLOSE l_print_lpn_curs;
972 END IF;
973 IF l_lpn_interface_uom_curs%isopen THEN
974 CLOSE l_lpn_interface_uom_curs;
975 END IF;
976 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
977 FND_MSG_PUB.Count_And_Get
978 (p_count => x_msg_count,
979 p_data => x_msg_data
980 );
981
982
983 WHEN OTHERS THEN
984 IF (l_debug = 1) THEN
985 print_debug('Process - other error happened - l_progress : '||l_progress, 1);
986 END IF;
987
988 IF l_is_header_error <> TRUE THEN
989 g_error_rhi_id_tb(g_error_rhi_id_tb.COUNT + 1) := l_rcv_txn_interface_rec.shipment_header_id;
990 shipment_header_cleanup(l_rcv_txn_interface_rec.shipment_header_id);
991
992 END IF;
993
994 IF SQLCODE IS NOT NULL THEN
995 IF (l_debug = 1) THEN
996 print_debug('SQL Error : '||SQLERRM(SQLCODE)||' SQL Error code : '||SQLCODE, 1);
997 END IF;
998 END IF;
999
1000 ROLLBACK TO process_lpn_intf;
1001 IF l_cur_lpn_interface%isopen THEN
1002 CLOSE l_cur_lpn_interface;
1003 END IF;
1004 IF l_print_lpn_curs%isopen THEN
1005 CLOSE l_print_lpn_curs;
1006 END IF;
1007 IF l_lpn_interface_uom_curs%isopen THEN
1008 CLOSE l_lpn_interface_uom_curs;
1009 END IF;
1010 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1011
1012
1013 END process;
1014
1015
1016
1017
1018 PROCEDURE print_label
1019 (p_shipment_header_id IN NUMBER)
1020 IS
1021 CURSOR l_lpn_curs IS
1022 SELECT lpn_id
1023 FROM wms_license_plate_numbers
1024 WHERE source_header_id = p_shipment_header_id
1025 AND lpn_context = 7
1026 AND source_type_id = 1;
1027
1028
1029
1030 l_input_param_rec INV_LABEL.input_parameter_rec_type;
1031 l_return_status VARCHAR2(1);
1032 l_msg_count NUMBER;
1033 l_msg_data VARCHAR2(400);
1034 l_label_status VARCHAR2(500);
1035
1036
1037 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1038 BEGIN
1039 OPEN l_lpn_curs;
1040
1041 LOOP
1042 FETCH l_lpn_curs
1043 INTO l_input_param_rec(1).lpn_id;
1044 EXIT WHEN l_lpn_curs%notfound;
1045
1046 END LOOP;
1047
1048 CLOSE l_lpn_curs;
1049
1050 inv_label.print_label
1051 (x_return_status => l_return_status
1052 , x_msg_count => l_msg_count
1053 , x_msg_data => l_msg_data
1054 , x_label_status => l_label_status
1055 , p_api_version => 1.0
1056 , p_print_mode =>1
1057 , p_business_flow_code => 25
1058 , p_input_param_rec => l_input_param_rec
1059 );
1060
1061
1062 EXCEPTION
1063 WHEN OTHERS THEN
1064 IF l_lpn_curs%isopen THEN
1065 CLOSE l_lpn_curs;
1066 ELSE
1067 NULL;
1068 END IF;
1069
1070
1071 END print_label;
1072
1073
1074
1075 END WMS_ASN_INTERFACE;