[Home] [Help]
PACKAGE BODY: APPS.WMS_UNLOAD_UTILS_PVT
Source
1 PACKAGE BODY WMS_UNLOAD_UTILS_PVT AS
2 /* $Header: WMSUNLDB.pls 120.2.12000000.2 2007/01/27 02:03:03 sfu ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_UNLOAD_UTILS_PVT';
4 g_pkg_body_ver CONSTANT VARCHAR2(100) := '$Header: WMSUNLDB.pls 120.2.12000000.2 2007/01/27 02:03:03 sfu ship $';
5 g_newline CONSTANT VARCHAR2(10) := fnd_global.newline;
6
7
8 PROCEDURE mydebug(msg IN VARCHAR2) IS
9 BEGIN
10 inv_log_util.trace(msg, g_pkg_name, 3);
11 END mydebug;
12
13
14
15 PROCEDURE print_version_info
16 IS
17 BEGIN
18 mydebug ('Package body: ' || g_pkg_body_ver);
19 END print_version_info;
20
21
22
23 PROCEDURE unload_task
24 ( x_ret_value OUT NOCOPY NUMBER
25 , x_message OUT NOCOPY VARCHAR2
26 , p_temp_id IN NUMBER
27 ) IS
28 msg_cnt NUMBER;
29 cnt NUMBER := -1;
30 l_temp_id NUMBER := NULL;
31 l_ser_temp_id NUMBER := NULL;
32 l_org_id NUMBER := NULL;
33 l_item_id NUMBER := NULL;
34 l_del_quantity NUMBER := 0;
35 l_quantity NUMBER := 0;
36 mol_id NUMBER := NULL;
37 line_status NUMBER := NULL;
38 v_lot_control_code NUMBER := NULL;
39 v_serial_control_code NUMBER := NULL;
40 v_allocate_serial_flag VARCHAR2(1) := NULL;
41 l_msg_count NUMBER;
42 l_return_status VARCHAR2(1);
43 l_msg_data VARCHAR2(100);
44 -- bug 2091680
45 l_transfer_lpn_id NUMBER;
46 l_wms_task_types NUMBER;
47 l_content_lpn_id NUMBER;
48 l_count NUMBER;
49 l_fm_serial_number VARCHAR2(30);
50 l_to_serial_number VARCHAR2(30);
51 l_serial_transaction_temp_id NUMBER;
52 l_lpn WMS_CONTAINER_PUB.LPN;
53 l_lpn_context NUMBER;
54
55 CURSOR mmtt_to_del(mol_id NUMBER) IS
56 SELECT mmtt.transaction_temp_id
57 , ABS(mmtt.transaction_quantity) --mmtt.primary_quantity
58 FROM mtl_material_transactions_temp mmtt
59 WHERE mmtt.move_order_line_id = mol_id
60 AND NOT EXISTS(
61 SELECT wdt.transaction_temp_id
62 FROM wms_dispatched_tasks wdt
63 WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id
64 AND wdt.transaction_temp_id IS NOT NULL
65 AND wdt.transaction_temp_id <> p_temp_id);
66
67 CURSOR msnt_to_del(p_tmp_id NUMBER) IS
68 SELECT serial_transaction_temp_id
69 FROM mtl_transaction_lots_temp
70 WHERE transaction_temp_id = p_tmp_id;
71
72 CURSOR c_fm_to_serial_number IS
73 SELECT fm_serial_number
74 , to_serial_number
75 FROM mtl_serial_numbers_temp
76 WHERE transaction_temp_id = p_temp_id;
77
78 CURSOR c_fm_to_lot_serial_number (p_sn_temp_id IN NUMBER) IS
79 SELECT fm_serial_number
80 , to_serial_number
81 FROM mtl_serial_numbers_temp msnt
82 WHERE msnt.transaction_temp_id = p_sn_temp_id;
83
84 CURSOR c_lot_allocations IS
85 SELECT serial_transaction_temp_id
86 FROM mtl_transaction_lots_temp
87 WHERE transaction_temp_id = p_temp_id;
88
89 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
90
91 BEGIN
92 IF (l_debug = 1) THEN
93 mydebug(' in unload_task ');
94 END IF;
95
96 print_version_info;
97
98 x_ret_value := 0;
99
100 SELECT COUNT(transaction_temp_id)
101 INTO cnt
102 FROM wms_dispatched_tasks
103 WHERE transaction_temp_id = p_temp_id;
104
105 IF (cnt IN(0, -1)) THEN
106 x_ret_value := 0;
107 x_message := ' NO TASK TO UNLOAD ';
108 RETURN;
109 ELSIF(cnt > 1) THEN
110 x_ret_value := 0;
111 x_message := ' MULTIPLE TASKS IN WDT FOR ' || p_temp_id;
112 RETURN;
113 END IF;
114
115 IF (l_debug = 1) THEN
116 mydebug(' in unload_task past 1 ');
117 END IF;
118
119 BEGIN
120 SELECT move_order_line_id
121 , organization_id
122 , inventory_item_id
123 , content_lpn_id
124 , transfer_lpn_id
125 , wms_task_type
126 INTO mol_id
127 , l_org_id
128 , l_item_id
129 , l_content_lpn_id
130 , l_transfer_lpn_id
131 , l_wms_task_types
132 FROM mtl_material_transactions_temp
133 WHERE transaction_temp_id = p_temp_id;
134
135 IF (l_debug = 1) THEN
136 mydebug(' mol_id ' || mol_id);
137 mydebug(' org_id ' || l_org_id);
138 mydebug(' item_id ' || l_item_id);
139 END IF;
140 EXCEPTION
141 WHEN NO_DATA_FOUND THEN
142 IF (l_debug = 1) THEN
143 mydebug(' No data found in mtl_material_transactions_temp ');
144 END IF;
145
146 mol_id := -1;
147 END;
148
149 IF (l_debug = 1) THEN
150 mydebug(' mol id :' || mol_id);
151 END IF;
152
153 IF (mol_id IS NOT NULL) THEN
154 BEGIN
155 SELECT line_status
156 INTO line_status
157 FROM mtl_txn_request_lines
158 WHERE line_id = mol_id;
159
160 IF (l_debug = 1) THEN
161 mydebug(' Status ' || line_status);
162 END IF;
163 EXCEPTION
164 WHEN NO_DATA_FOUND THEN
165 IF (l_debug = 1) THEN
166 mydebug('No data found in mtl_txn_request_lines');
167 END IF;
168
169 line_status := -1;
170 END;
171 END IF;
172
173 IF (l_debug = 1) THEN
174 mydebug(' move order line status ' || line_status);
175 END IF;
176
177 IF (line_status = inv_globals.g_to_status_cancel_by_source) THEN
178 IF (l_debug = 1) THEN
179 mydebug(' move order line cancelled ');
180 END IF;
181
182 IF (l_debug = 1) THEN
183 mydebug('deleting allocations ');
184 END IF;
185
186 OPEN mmtt_to_del(mol_id);
187
188 LOOP
189 FETCH mmtt_to_del INTO l_temp_id, l_quantity;
190 EXIT WHEN mmtt_to_del%NOTFOUND;
191
192 IF (l_debug = 1) THEN
193 mydebug('deleting allocations l_temp_id:' || l_temp_id || ' l_quantity:' || l_quantity);
194 END IF;
195
196 inv_mo_cancel_pvt.reduce_rsv_allocation(
197 x_return_status => l_return_status
198 , x_msg_count => l_msg_count
199 , x_msg_data => x_message
200 , p_transaction_temp_id => l_temp_id
201 , p_quantity_to_delete => l_quantity
202 );
203
204 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
205 IF (l_debug = 1) THEN
206 mydebug(' error returned from inv_mo_cancel_pvt.reduce_rsv_allocation');
207 mydebug(x_message);
208 END IF;
209
210 RAISE fnd_api.g_exc_error;
211 ELSE
212 IF (l_debug = 1) THEN
213 mydebug(' Successful from inv_mo_cancel_pvt.reduce_rsv_allocation Call');
214 END IF;
215
216 l_del_quantity := l_del_quantity + l_quantity;
217 END IF;
218 END LOOP;
219
220 IF (l_debug = 1) THEN
221 mydebug(' alloc quantity deleted ' || l_del_quantity);
222 END IF;
223
224 UPDATE mtl_txn_request_lines
225 SET quantity_detailed =(quantity_detailed - l_del_quantity)
226 WHERE line_id = mol_id;
227
228 IF (l_debug = 1) THEN
229 mydebug('updated mol:' || mol_id);
230 END IF;
231
232 DELETE wms_dispatched_tasks
233 WHERE transaction_temp_id = p_temp_id;
234
235 IF (l_debug = 1) THEN
236 mydebug('deleted from wms_dispatched_tasks ');
237 END IF;
238
239 SELECT COUNT(transaction_temp_id)
240 INTO cnt
241 FROM mtl_material_transactions_temp mmtt
242 WHERE mmtt.move_order_line_id = mol_id;
243
244 IF (cnt = 0) THEN
245 IF (l_debug = 1) THEN
246 mydebug('No more allocations in mmtt left for this mo line ' || mol_id);
247 mydebug(' so closing the mo line ' || mol_id);
248 END IF;
249
250 UPDATE mtl_txn_request_lines
251 SET line_status = inv_globals.g_to_status_closed
252 WHERE line_id = mol_id;
253
254 IF (l_debug = 1) THEN
255 mydebug(' updated the mo line status to ' || inv_globals.g_to_status_closed);
256 END IF;
257 ELSE
258 IF (l_debug = 1) THEN
259 mydebug(' allocations in mmtt left for this mo line - count ' || mol_id || ' - ' || cnt);
260 mydebug(' so not closing the mo line ' || mol_id);
261 END IF;
262 END IF;
263 ELSE
264 IF (l_debug = 1) THEN
265 mydebug(' move order line not cancelled ');
266 END IF;
267
268 SELECT msi.lot_control_code
269 , msi.serial_number_control_code
270 , mmtt.serial_allocated_flag
271 INTO v_lot_control_code
272 , v_serial_control_code
273 , v_allocate_serial_flag
274 FROM mtl_system_items msi
275 , mtl_material_transactions_temp mmtt
276 WHERE msi.inventory_item_id = mmtt.inventory_item_id
277 AND msi.organization_id = mmtt.organization_id
278 AND mmtt.transaction_temp_id = p_temp_id;
279
280 IF l_wms_task_types IN(wms_globals.g_wms_task_type_stg_move) THEN
281 -- We need to do this for staging move as staging move will
282 -- have no MSNT/MTLT lines
283 v_lot_control_code := 0;
284 v_serial_control_code := 0;
285 END IF;
286
287 IF (l_debug = 1) THEN
288 mydebug(' lot code ' || v_lot_control_code);
289 mydebug(' ser_code ' || v_serial_control_code);
290 mydebug(' alloc ser flag' || v_allocate_serial_flag);
291 END IF;
292
293 IF (v_allocate_serial_flag <> 'Y') THEN
294 IF (l_debug = 1) THEN
295 mydebug(' alloc serial flag is not y ');
296 END IF;
297
298 IF (v_lot_control_code = 1
299 AND v_serial_control_code NOT IN(1, 6)) THEN
300 IF (l_debug = 1) THEN
301 mydebug(' serial controlled only ');
302 END IF;
303
304 IF (l_debug = 1) THEN
305 mydebug(' deleting msnt with temp id ' || p_temp_id);
306 END IF;
307
308 --UPDATE GROUP_MARK_ID for Serial controlled
309
310 OPEN c_fm_to_serial_number;
311
312 LOOP
313 FETCH c_fm_to_serial_number INTO l_fm_serial_number, l_to_serial_number;
314 EXIT WHEN c_fm_to_serial_number%NOTFOUND;
315
316 UPDATE mtl_serial_numbers
317 SET group_mark_id = NULL
318 WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
319 --Bug 2940878 fix added org and item restriction
320 AND current_organization_id = l_org_id
321 AND inventory_item_id = l_item_id;
322 END LOOP;
323
324 CLOSE c_fm_to_serial_number;
325
326 /**Serial Controlled only ****/
327 DELETE mtl_serial_numbers_temp
328 WHERE transaction_temp_id = p_temp_id;
329 ELSIF(v_lot_control_code = 2
330 AND v_serial_control_code NOT IN(1, 6)) THEN
331 /** Both lot and serial controlled **/
332 IF (l_debug = 1) THEN
333 mydebug(' lot and serial controlled ');
334 END IF;
335
336 IF (l_debug = 1) THEN
337 mydebug(' deleting msnt ');
338 END IF;
339
340 OPEN c_lot_allocations;
341
342 LOOP
343 FETCH c_lot_allocations INTO l_serial_transaction_temp_id;
344 EXIT WHEN c_lot_allocations%NOTFOUND;
345 --UPDATE GROUP_MARK_ID for Lot and serial Controlled
346 OPEN c_fm_to_lot_serial_number (l_serial_transaction_temp_id);
347
348 LOOP
349 FETCH c_fm_to_lot_serial_number INTO l_fm_serial_number, l_to_serial_number;
350 EXIT WHEN c_fm_to_lot_serial_number%NOTFOUND;
351
352 UPDATE mtl_serial_numbers
353 SET group_mark_id = NULL
354 WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
355 --Bug 2940878 fix added org and item restriction
356 AND current_organization_id = l_org_id
357 AND inventory_item_id = l_item_id;
358 END LOOP;
359
360 CLOSE c_fm_to_lot_serial_number;
361
362 DELETE FROM mtl_serial_numbers_temp
363 WHERE transaction_temp_id = l_serial_transaction_temp_id;
364 END LOOP;
365
366 CLOSE c_lot_allocations;
367
368 DELETE mtl_serial_numbers_temp
369 WHERE transaction_temp_id IN(SELECT mtlt.serial_transaction_temp_id
370 FROM mtl_transaction_lots_temp mtlt
371 WHERE mtlt.transaction_temp_id = p_temp_id);
372
373 IF (l_debug = 1) THEN
374 mydebug(' updating mtlt ');
375 END IF;
376
377 UPDATE mtl_transaction_lots_temp
378 SET serial_transaction_temp_id = NULL
379 WHERE transaction_temp_id = p_temp_id;
380
381 IF (l_debug = 1) THEN
382 mydebug(' update done ');
383 END IF;
384 END IF;
385 END IF;
386
387 IF (l_debug = 1) THEN
388 mydebug('deleting WDT with temp_id ' || p_temp_id);
389 END IF;
390
391 -- added following for bug fix 2769358
392
393 IF l_content_lpn_id IS NOT NULL THEN
394 IF (l_debug = 1) THEN
395 mydebug('Set lpn context to packing for lpn_ID : ' || l_content_lpn_id);
396 END IF;
397
398 --bug 4411814
399 l_lpn.lpn_id := l_content_lpn_id;
400 l_lpn.organization_id := l_org_id;
401 l_lpn.lpn_context := wms_container_pub.lpn_context_inv;
402
403 wms_container_pvt.Modify_LPN
404 (
405 p_api_version => 1.0
406 , p_validation_level => fnd_api.g_valid_level_none
407 , x_return_status => l_return_status
408 , x_msg_count => l_msg_count
409 , x_msg_data => l_msg_data
410 , p_lpn => l_lpn
411 ) ;
412
413 l_lpn := NULL;
414
415 END IF;
416
417 --The lpn ids must be set to null for this task
418 UPDATE mtl_material_transactions_temp
419 SET lpn_id = NULL
420 , content_lpn_id = NULL
421 , transfer_lpn_id = NULL
422 , wms_task_status = 1 -- Bug4185621: update mmtt task status back to pending
423 WHERE transaction_temp_id = p_temp_id;
424
425 DELETE wms_dispatched_tasks
426 WHERE transaction_temp_id = p_temp_id;
427
428 IF (l_debug = 1) THEN
429 mydebug('deleted WDT with temp_id ' || p_temp_id);
430 END IF;
431
432 IF l_wms_task_types IN(wms_globals.g_wms_task_type_stg_move) THEN
433 DELETE FROM mtl_material_transactions_temp
434 WHERE transaction_temp_id = p_temp_id;
435 END IF;
436 END IF;
437
438
439 -- Bug 2091680 . Update the LPN context to defined but not used if the
440 -- lpn is unloaded with a context of packaging and update the context to
441 -- inventory if the entire lpn is picked
442 -- this happens only if there are no more allocations for that lpn and
443 -- the last line IS being unloaded
444 IF l_wms_task_types IN ( wms_globals.g_wms_task_type_pick
445 , wms_globals.g_wms_task_type_replenish
446 , wms_globals.g_wms_task_type_moxfer
447 )
448 THEN
449 SELECT COUNT(1)
450 INTO l_count
451 FROM mtl_material_transactions_temp
452 WHERE transfer_lpn_id = l_transfer_lpn_id;
453
454 IF l_count = 0 THEN -- no more rows and the current row is the
455 --last allocation
456 BEGIN
457 SELECT lpn_context INTO l_lpn_context
458 FROM wms_license_plate_numbers
459 WHERE lpn_id = l_transfer_lpn_id;
460 EXCEPTION
461 WHEN no_data_found THEN
462 l_lpn_context := NULL;
463 END;
464
465
466 IF l_content_lpn_id IS NOT NULL
467 AND l_content_lpn_id = l_transfer_lpn_id THEN
468
469 IF l_lpn_context <> 1 AND l_lpn_context IS NOT NULL THEN
470
471
472 --bug 4411814
473 l_lpn.lpn_id := l_transfer_lpn_id;
474 l_lpn.organization_id := l_org_id;
475 l_lpn.lpn_context := 1;
476
477 wms_container_pvt.Modify_LPN
478 (
479 p_api_version => 1.0
480 , p_validation_level => fnd_api.g_valid_level_none
481 , x_return_status => l_return_status
482 , x_msg_count => l_msg_count
483 , x_msg_data => l_msg_data
484 , p_lpn => l_lpn
485 ) ;
486
487 l_lpn := NULL;
488 END IF;
489
490 ELSE
491
492 IF l_lpn_context = 8 THEN
493
494 --bug 4411814
495 l_lpn.lpn_id := l_transfer_lpn_id;
496 l_lpn.organization_id := l_org_id;
497 l_lpn.lpn_context := 5;
498
499 wms_container_pvt.Modify_LPN
500 (
501 p_api_version => 1.0
502 , p_validation_level => fnd_api.g_valid_level_none
503 , x_return_status => l_return_status
504 , x_msg_count => l_msg_count
505 , x_msg_data => l_msg_data
506 , p_lpn => l_lpn
507 ) ;
508
509 l_lpn := NULL;
510
511 END IF;
512
513 END IF;
514
515 END IF;
516
517 ELSIF l_wms_task_types = wms_globals.g_wms_task_type_stg_move THEN
518
519 IF (l_debug = 1) THEN
520 mydebug('Calling wms_container_pvt.Modify_LPN_Wrapper for staging move. p_lpn_id = '||l_content_lpn_id);
521 mydebug('p_lpn_context = '|| wms_container_pub.LPN_CONTEXT_PICKED );
522 END IF;
523
524 wms_container_pub.Modify_LPN_Wrapper
525 ( p_api_version => 1.0
526 ,x_return_status => l_return_status
527 ,x_msg_count => l_msg_count
528 ,x_msg_data => x_message
529 ,p_lpn_id => l_content_lpn_id
530 ,p_lpn_context => wms_container_pub.lpn_context_picked
531 );
532
533 IF (l_debug = 1) THEN
534 mydebug('wms_container_pvt.Modify_LPN_Wrapper x_return_status = '||l_return_status);
535 END IF;
536
537 END IF;
538
539 x_ret_value := 1;
540
541 IF (l_debug = 1) THEN
542 mydebug('done unload_task x_ret ' || x_ret_value);
543 END IF;
544
545 -- Doing an explicit commit
546 -- HERE
547
548 COMMIT;
549 EXCEPTION
550 WHEN OTHERS THEN
551 x_ret_value := 0;
552
553 IF (l_debug = 1) THEN
554 mydebug(' In exception unload_task x_ret' || x_ret_value);
555 END IF;
556
557 fnd_msg_pub.count_and_get(p_count => msg_cnt, p_data => x_message);
558 END unload_task;
559
560
561
562 PROCEDURE unload_bulk_task
563 ( x_next_temp_id OUT NOCOPY NUMBER
564 , x_return_status OUT NOCOPY VARCHAR2
565 , p_txn_temp_id IN NUMBER
566 ) IS
567 l_count NUMBER := 0;
568 l_line_type VARCHAR2(20) := NULL;
569 l_temp_id NUMBER := NULL;
570 l_org_id NUMBER := NULL;
571 l_item_id NUMBER := NULL;
572 l_quantity NUMBER := 0;
573 l_txn_uom VARCHAR2(3) := NULL;
574 l_mo_line_uom VARCHAR2(3) := NULL;
575 l_conv_qty NUMBER := 0;
576 l_mo_line_id NUMBER := NULL;
577 v_lot_control_code NUMBER := NULL;
578 v_serial_control_code NUMBER := NULL;
579 v_allocate_serial_flag VARCHAR2(1) := NULL;
580 l_msg_count NUMBER;
581 l_msg_data VARCHAR2(2000);
582 l_return_status VARCHAR2(1);
583 -- bug 2091680
584 l_transfer_lpn_id NUMBER;
585 l_content_lpn_id NUMBER;
586 l_fm_serial_number VARCHAR2(30);
587 l_to_serial_number VARCHAR2(30);
588 l_serial_transaction_temp_id NUMBER;
589 l_lpn WMS_CONTAINER_PUB.LPN;
590 l_lpn_context NUMBER;
591
592 CURSOR c_cncl_ovrpick_lines (p_temp_id IN NUMBER) IS
593 SELECT 'CANCELLED'
594 , mmtt.transaction_temp_id
595 , ABS(mmtt.transaction_quantity)
596 , mmtt.transaction_uom
597 , mmtt.move_order_line_id
598 , mtrl.uom_code
599 FROM mtl_material_transactions_temp mmtt
600 , mtl_txn_request_lines mtrl
601 WHERE mmtt.parent_line_id = p_temp_id
602 AND mtrl.line_id = mmtt.move_order_line_id
603 AND mtrl.line_status = 9
604 UNION ALL
605 SELECT 'OVERPICKED'
606 , mmtt.transaction_temp_id
607 , ABS(mmtt.transaction_quantity)
608 , mmtt.transaction_uom
609 , to_number(NULL)
610 , to_char(NULL)
611 FROM mtl_material_transactions_temp mmtt
612 WHERE mmtt.parent_line_id = p_temp_id
613 AND mmtt.transaction_temp_id <> mmtt.parent_line_id
614 AND mmtt.transaction_action_id = 2
615 AND mmtt.move_order_line_id IS NULL;
616
617
618 CURSOR msnt_to_del (p_temp_id IN NUMBER) IS
619 SELECT serial_transaction_temp_id
620 FROM mtl_transaction_lots_temp
621 WHERE transaction_temp_id = p_temp_id;
622
623
624 CURSOR c_fm_to_serial_number (p_temp_id IN NUMBER) IS
625 SELECT fm_serial_number
626 , to_serial_number
627 FROM mtl_serial_numbers_temp
628 WHERE transaction_temp_id = p_temp_id;
629
630
631 CURSOR c_fm_to_lot_serial_number (p_sn_temp_id IN NUMBER) IS
632 SELECT fm_serial_number
633 , to_serial_number
634 FROM mtl_serial_numbers_temp msnt
635 WHERE msnt.transaction_temp_id = p_sn_temp_id;
636
637
638 CURSOR c_lot_allocations (p_temp_id IN NUMBER) IS
639 SELECT serial_transaction_temp_id
640 FROM mtl_transaction_lots_temp
641 WHERE transaction_temp_id = p_temp_id;
642
643
644 CURSOR c_next_temp_id
645 ( p_xfer_lpn_id IN NUMBER
646 , p_temp_id IN NUMBER
647 ) IS
648 -- Material packed into content LPNs
649 SELECT m.transaction_temp_id
650 , 1 dummy_sort
651 FROM wms_dispatched_tasks w
652 , mtl_material_transactions_temp m
653 WHERE m.transfer_lpn_id = p_xfer_lpn_id
654 AND m.transaction_temp_id <> p_temp_id
655 AND m.transaction_temp_id = m.parent_line_id
656 AND w.transaction_temp_id = m.transaction_temp_id
657 AND w.status = 4
658 AND EXISTS
659 ( SELECT 'x'
660 FROM mtl_material_transactions_temp m2
661 WHERE m2.transfer_lpn_id = m.transfer_lpn_id
662 AND m2.organization_id = m.organization_id
663 AND m2.transaction_temp_id = m2.parent_line_id
664 AND m2.transaction_temp_id <> m.transaction_temp_id
665 AND m2.transaction_temp_id <> p_temp_id
666 AND m2.content_lpn_id = m.transfer_lpn_id
667 )
668 UNION ALL
669 -- Content LPNs
670 SELECT m.transaction_temp_id
671 , 2 dummy_sort
672 FROM wms_dispatched_tasks w
673 , mtl_material_transactions_temp m
674 WHERE m.transfer_lpn_id = p_xfer_lpn_id
675 AND m.transaction_temp_id <> p_temp_id
676 AND m.transaction_temp_id = m.parent_line_id
677 AND w.transaction_temp_id = m.transaction_temp_id
678 AND w.status = 4
679 AND m.content_lpn_id IS NOT NULL
680 UNION ALL
681 -- Material unpacked from content LPNs
682 SELECT m.transaction_temp_id
683 , 3 dummy_sort
684 FROM wms_dispatched_tasks w
685 , mtl_material_transactions_temp m
686 WHERE m.transfer_lpn_id = p_xfer_lpn_id
687 AND m.transaction_temp_id <> p_temp_id
688 AND m.transaction_temp_id = m.parent_line_id
689 AND w.transaction_temp_id = m.transaction_temp_id
690 AND w.status = 4
691 AND EXISTS
692 ( SELECT 'x'
693 FROM mtl_material_transactions_temp m2
694 WHERE m2.transfer_lpn_id = m.transfer_lpn_id
695 AND m2.organization_id = m.organization_id
696 AND m2.transaction_temp_id = m2.parent_line_id
697 AND m2.transaction_temp_id <> m.transaction_temp_id
698 AND m2.transaction_temp_id <> p_temp_id
699 AND m2.content_lpn_id = m.lpn_id
700 )
701 UNION ALL
702 -- All other picked material
703 SELECT m.transaction_temp_id
704 , 4 dummy_sort
705 FROM wms_dispatched_tasks w
706 , mtl_material_transactions_temp m
707 WHERE m.transfer_lpn_id = p_xfer_lpn_id
708 AND m.transaction_temp_id <> p_temp_id
709 AND m.transaction_temp_id = m.parent_line_id
710 AND w.transaction_temp_id = m.transaction_temp_id
711 AND w.status = 4
712 AND m.content_lpn_id IS NULL
713 AND ( (m.lpn_id IS NOT NULL
714 AND NOT EXISTS
715 ( SELECT 'x'
716 FROM mtl_material_transactions_temp m2
717 WHERE m2.transfer_lpn_id = m.transfer_lpn_id
718 AND m2.organization_id = m.organization_id
719 AND m2.transaction_temp_id = m2.parent_line_id
720 AND m2.transaction_temp_id <> m.transaction_temp_id
721 AND m2.transaction_temp_id <> p_temp_id
722 AND m2.content_lpn_id = m.lpn_id
723 )
724 )
725 OR m.lpn_id IS NULL
726 )
727 AND NOT EXISTS
728 ( SELECT 'x'
729 FROM mtl_material_transactions_temp m3
730 WHERE m3.transfer_lpn_id = m.transfer_lpn_id
731 AND m3.organization_id = m.organization_id
732 AND m3.transaction_temp_id = m3.parent_line_id
733 AND m3.transaction_temp_id <> m.transaction_temp_id
734 AND m3.transaction_temp_id <> p_temp_id
735 AND m3.content_lpn_id = m.transfer_lpn_id
736 )
737 ORDER BY dummy_sort;
738
739
740 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
741 l_parent_deleted BOOLEAN := FALSE;
742 l_dum_sort NUMBER;
743
744 BEGIN
745
746 IF l_debug = 1 THEN
747 mydebug
748 ( 'Entered with parameters: ' || g_newline ||
749 'p_txn_temp_id => ' || to_char(p_txn_temp_id)
750 );
751 END IF;
752
753 print_version_info;
754
755 x_return_status := fnd_api.g_ret_sts_success;
756 x_next_temp_id := NULL;
757
758 SAVEPOINT unload_bulk_sp;
759
760 BEGIN
761 SELECT organization_id
762 , inventory_item_id
763 , content_lpn_id
764 , transfer_lpn_id
765 INTO l_org_id
766 , l_item_id
767 , l_content_lpn_id
768 , l_transfer_lpn_id
769 FROM mtl_material_transactions_temp
770 WHERE transaction_temp_id = p_txn_temp_id;
771
772 IF (l_debug = 1) THEN
773 mydebug('Org_id: ' || to_char(l_org_id) ||
774 ', item_id: ' || to_char(l_item_id) ||
775 ', content LPN: ' || to_char(l_content_lpn_id) ||
776 ', transfer LPN: ' || to_char(l_transfer_lpn_id)
777 );
778 END IF;
779 EXCEPTION
780 WHEN NO_DATA_FOUND THEN
781 IF (l_debug = 1) THEN
782 mydebug(' No data found in mtl_material_transactions_temp ');
783 END IF;
784 END;
785
786 IF l_content_lpn_id IS NOT NULL THEN
787 IF (l_debug = 1) THEN
788 mydebug('Set lpn context to resides in INV for lpn_ID: ' ||
789 to_char(l_content_lpn_id)
790 );
791 END IF;
792
793
794 --bug 4411814
795 l_lpn.lpn_id := l_content_lpn_id;
796 l_lpn.organization_id := l_org_id;
797 l_lpn.lpn_context := wms_container_pub.lpn_context_inv;
798
799 wms_container_pvt.Modify_LPN
800 (
801 p_api_version => 1.0
802 , p_validation_level => fnd_api.g_valid_level_none
803 , x_return_status => l_return_status
804 , x_msg_count => l_msg_count
805 , x_msg_data => l_msg_data
806 , p_lpn => l_lpn
807 ) ;
808
809 l_lpn := NULL;
810
811 END IF;
812
813 OPEN c_cncl_ovrpick_lines (p_txn_temp_id);
814 LOOP
815 FETCH c_cncl_ovrpick_lines
816 INTO l_line_type
817 , l_temp_id
818 , l_quantity
819 , l_txn_uom
820 , l_mo_line_id
821 , l_mo_line_uom;
822 EXIT WHEN c_cncl_ovrpick_lines%NOTFOUND;
823
824 IF (l_debug = 1) THEN
825 mydebug('Deleting l_temp_id: ' || to_char(l_temp_id) ||
826 ', l_quantity: ' || to_char(l_quantity) ||
827 ', l_txn_uom: ' || l_txn_uom ||
828 ', l_mo_line_id: ' || to_char(l_mo_line_id) ||
829 ', l_mo_line_uom: ' || l_mo_line_uom ||
830 ', l_line_type: ' || l_line_type
831 );
832 END IF;
833
834 l_return_status := fnd_api.g_ret_sts_success;
835
836 IF l_line_type = 'CANCELLED'
837 THEN
838 inv_mo_cancel_pvt.reduce_rsv_allocation
839 ( x_return_status => l_return_status
840 , x_msg_count => l_msg_count
841 , x_msg_data => l_msg_data
842 , p_transaction_temp_id => l_temp_id
843 , p_quantity_to_delete => l_quantity
844 );
845 ELSIF l_line_type = 'OVERPICKED'
846 THEN
847 inv_trx_util_pub.delete_transaction
848 ( x_return_status => l_return_status
849 , x_msg_data => l_msg_data
850 , x_msg_count => l_msg_count
851 , p_transaction_temp_id => l_temp_id
852 , p_update_parent => TRUE
853 );
854 END IF;
855
856 IF (l_return_status <> fnd_api.g_ret_sts_success)
857 THEN
858 IF (l_debug = 1) THEN
859 mydebug('Error returned from API for deleting transaction');
860 mydebug(l_msg_data);
861 END IF;
862
863 RAISE fnd_api.g_exc_error;
864 END IF;
865
866 IF l_mo_line_id IS NOT NULL
867 THEN
868 IF l_mo_line_uom <> l_txn_uom
869 THEN
870 l_conv_qty := inv_convert.inv_um_convert
871 ( item_id => l_item_id
872 , PRECISION => NULL
873 , from_quantity => l_quantity
874 , from_unit => l_txn_uom
875 , to_unit => l_mo_line_uom
876 , from_name => NULL
877 , to_name => NULL
878 );
879 ELSE
880 l_conv_qty := l_quantity;
881 END IF;
882
883 UPDATE mtl_txn_request_lines
884 SET quantity_detailed = (quantity_detailed - l_conv_qty)
885 WHERE line_id = l_mo_line_id;
886
887 IF (l_debug = 1) THEN
888 mydebug('Updated mol: ' || to_char(l_mo_line_id));
889 END IF;
890
891 SELECT COUNT(transaction_temp_id)
892 INTO l_count
893 FROM mtl_material_transactions_temp mmtt
894 WHERE mmtt.move_order_line_id = l_mo_line_id;
895
896 IF (l_count = 0) THEN
897 IF (l_debug = 1) THEN
898 mydebug('No more allocations for mo line ' || to_char(l_mo_line_id));
899 END IF;
900
901 UPDATE mtl_txn_request_lines
902 SET line_status = inv_globals.g_to_status_closed
903 WHERE line_id = l_mo_line_id;
904 ELSE
905 IF (l_debug = 1) THEN
906 mydebug('Allocations left: ' || to_char(l_count));
907 END IF;
908 END IF;
909 END IF; -- end if move order line ID is not null
910 END LOOP;
911
912 --
913 -- Proceed only if parent still exists
914 --
915 BEGIN
916 SELECT msi.lot_control_code
917 , msi.serial_number_control_code
918 , mmtt.serial_allocated_flag
919 INTO v_lot_control_code
920 , v_serial_control_code
921 , v_allocate_serial_flag
922 FROM mtl_system_items msi
923 , mtl_material_transactions_temp mmtt
924 WHERE msi.inventory_item_id = mmtt.inventory_item_id
925 AND msi.organization_id = mmtt.organization_id
926 AND mmtt.transaction_temp_id = p_txn_temp_id;
927 EXCEPTION
928 WHEN NO_DATA_FOUND THEN
929 IF (l_debug = 1) THEN
930 mydebug('Parent MMTT deleted when cancelled child tasks were processed');
931 END IF;
932 l_parent_deleted := TRUE;
933
934 WHEN OTHERS THEN
935 RAISE;
936 END;
937
938 IF NOT l_parent_deleted THEN
939 IF (l_debug = 1) THEN
940 mydebug(' lot code ' || v_lot_control_code);
941 mydebug(' ser_code ' || v_serial_control_code);
942 mydebug(' alloc ser flag' || v_allocate_serial_flag);
943 END IF;
944
945 IF (v_allocate_serial_flag <> 'Y') THEN
946 IF (l_debug = 1) THEN
947 mydebug('Alloc serial flag is not y ');
948 END IF;
949
950 IF (v_lot_control_code = 1
951 AND v_serial_control_code NOT IN(1, 6))
952 THEN
953 IF (l_debug = 1) THEN
954 mydebug('Serial controlled only.');
955 END IF;
956
957 --
958 -- Update group_mark_id for Serial controlled
959 --
960 OPEN c_fm_to_serial_number (p_txn_temp_id);
961 LOOP
962 FETCH c_fm_to_serial_number INTO l_fm_serial_number, l_to_serial_number;
963 EXIT WHEN c_fm_to_serial_number%NOTFOUND;
964
965 UPDATE mtl_serial_numbers
966 SET group_mark_id = NULL
967 WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
968 AND current_organization_id = l_org_id
969 AND inventory_item_id = l_item_id;
970
971 IF (l_debug = 1) THEN
972 mydebug('Unmarked serials between ' || l_fm_serial_number ||
973 ' and ' || l_to_serial_number ||
974 '. Now deleting MSNT ' || to_char(p_txn_temp_id)
975 );
976 END IF;
977 END LOOP;
978
979 CLOSE c_fm_to_serial_number;
980
981 DELETE mtl_serial_numbers_temp
982 WHERE transaction_temp_id = p_txn_temp_id;
983
984 ELSIF(v_lot_control_code = 2
985 AND v_serial_control_code NOT IN(1, 6))
986 THEN
987 /** Both lot and serial controlled **/
988 IF (l_debug = 1) THEN
989 mydebug('Lot and serial controlled ');
990 END IF;
991
992 OPEN c_lot_allocations (p_txn_temp_id);
993 LOOP
994 FETCH c_lot_allocations INTO l_serial_transaction_temp_id;
995 EXIT WHEN c_lot_allocations%NOTFOUND;
996
997 --
998 -- Update group_mark_id for lot and serial controlled
999 --
1000 OPEN c_fm_to_lot_serial_number (l_serial_transaction_temp_id);
1001 LOOP
1002 FETCH c_fm_to_lot_serial_number INTO l_fm_serial_number, l_to_serial_number;
1003 EXIT WHEN c_fm_to_lot_serial_number%NOTFOUND;
1004
1005 UPDATE mtl_serial_numbers
1006 SET group_mark_id = NULL
1007 WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
1008 AND current_organization_id = l_org_id
1009 AND inventory_item_id = l_item_id;
1010 END LOOP;
1011
1012 CLOSE c_fm_to_lot_serial_number;
1013
1014 IF (l_debug = 1) THEN
1015 mydebug('Unmarked serials between ' || l_fm_serial_number ||
1016 ' and ' || l_to_serial_number ||
1017 '. Now deleting MSNT ' || to_char(l_serial_transaction_temp_id)
1018 );
1019 END IF;
1020
1021 DELETE FROM mtl_serial_numbers_temp
1022 WHERE transaction_temp_id = l_serial_transaction_temp_id;
1023 END LOOP;
1024 CLOSE c_lot_allocations;
1025
1026 UPDATE mtl_transaction_lots_temp
1027 SET serial_transaction_temp_id = NULL
1028 WHERE transaction_temp_id = p_txn_temp_id;
1029
1030 IF (l_debug = 1) THEN
1031 mydebug('Updated MTLT');
1032 END IF;
1033 END IF; -- end if lot/serial controlled
1034 END IF; -- end if serial not allocated
1035
1036 IF (l_debug = 1) THEN
1037 mydebug('deleting WDT with temp_id: ' || to_char(p_txn_temp_id));
1038 END IF;
1039
1040 DELETE wms_dispatched_tasks
1041 WHERE transaction_temp_id = p_txn_temp_id;
1042
1043 --
1044 -- The lpn ids must be set to null for this task
1045 -- for both parent and child records
1046 --
1047 UPDATE mtl_material_transactions_temp
1048 SET lpn_id = NULL
1049 , content_lpn_id = NULL
1050 , transfer_lpn_id = NULL
1051 , wms_task_status = 1 -- Bug4185621: update mmtt task status back to pending
1052 WHERE parent_line_id = p_txn_temp_id;
1053
1054 END IF; -- end if parent not deleted
1055
1056 l_count := 0;
1057 SELECT COUNT(*)
1058 INTO l_count
1059 FROM mtl_material_transactions_temp
1060 WHERE transfer_lpn_id = l_transfer_lpn_id;
1061
1062 IF l_count = 0 THEN
1063 --
1064 -- no more rows and the current row is the
1065 -- last allocation
1066 --
1067 IF l_content_lpn_id IS NULL THEN
1068 BEGIN
1069 SELECT lpn_context INTO l_lpn_context
1070 FROM wms_license_plate_numbers
1071 WHERE lpn_id = l_transfer_lpn_id;
1072 EXCEPTION
1073 WHEN no_data_found THEN
1074 l_lpn_context := NULL;
1075 END;
1076
1077 IF l_lpn_context = 8 THEN
1078
1079 --bug 4411814
1080 l_lpn.lpn_id := l_transfer_lpn_id;
1081 l_lpn.organization_id := l_org_id;
1082 l_lpn.lpn_context := 5;
1083
1084 wms_container_pvt.Modify_LPN
1085 (
1086 p_api_version => 1.0
1087 , p_validation_level => fnd_api.g_valid_level_none
1088 , x_return_status => l_return_status
1089 , x_msg_count => l_msg_count
1090 , x_msg_data => l_msg_data
1091 , p_lpn => l_lpn
1092 ) ;
1093
1094 l_lpn := NULL;
1095
1096 END IF;
1097
1098 END IF;
1099 ELSE
1100 OPEN c_next_temp_id (l_transfer_lpn_id, p_txn_temp_id);
1101 FETCH c_next_temp_id INTO x_next_temp_id, l_dum_sort;
1102 CLOSE c_next_temp_id;
1103 END IF;
1104
1105 IF (l_debug = 1) THEN
1106 mydebug('Done with unload_bulk_task');
1107 END IF;
1108
1109 --
1110 -- Explicit commit required
1111 --
1112 COMMIT;
1113
1114 EXCEPTION
1115 WHEN OTHERS THEN
1116 ROLLBACK TO unload_bulk_sp;
1117
1118 x_return_status := fnd_api.g_ret_sts_error;
1119
1120 IF (l_debug = 1) THEN
1121 mydebug('Exception unload_bulk_task: ' || sqlerrm);
1122 END IF;
1123 END unload_bulk_task;
1124
1125 END WMS_UNLOAD_UTILS_PVT;