1 PACKAGE BODY WMS_UNLOAD_UTILS_PVT AS
2 /* $Header: WMSUNLDB.pls 120.7.12020000.4 2012/12/30 10:47:10 raminoch ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_UNLOAD_UTILS_PVT';
4 g_pkg_body_ver CONSTANT VARCHAR2(100) := '$Header: WMSUNLDB.pls 120.7.12020000.4 2012/12/30 10:47:10 raminoch 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 PROCEDURE print_version_info
14 IS
15 BEGIN
16 mydebug ('Package body: ' || g_pkg_body_ver);
17 END print_version_info;
18
19 --Forward Declaration Added for 14699845 (Flexible Lot Allocation)
20 /* PROCEDURE get_allocation_params
21 ( x_return_status OUT NOCOPY VARCHAR2
22 , x_reserved_lot OUT NOCOPY VARCHAR2
23 , x_allocate_serial_flag IN OUT NOCOPY VARCHAR2
24 , p_serial_control_code IN NUMBER
25 , p_allocate_lot_flag IN VARCHAR2
26 , p_org_id IN NUMBER
27 , p_temp_id IN NUMBER);*/
28
29 PROCEDURE clean_LS_Flexible_unload
30 ( x_return_status OUT NOCOPY VARCHAR2
31 , p_txn_temp_id IN NUMBER
32 , p_allocate_lot_flag IN VARCHAR2
33 , p_reserved_lot IN VARCHAR2
34 , p_org_id IN NUMBER
35 , p_is_bulk_task IN VARCHAR2 DEFAULT 'N'
36 );
37 --Forward Declaration Added for 14699845 (Flexible Lot Allocation)
38 PROCEDURE unload_task
39 ( x_ret_value OUT NOCOPY NUMBER
40 , x_message OUT NOCOPY VARCHAR2
41 , p_temp_id IN NUMBER
42 ) IS
43 msg_cnt NUMBER;
44 cnt NUMBER := -1;
45 l_temp_id NUMBER := NULL;
46 l_ser_temp_id NUMBER := NULL;
47 l_org_id NUMBER := NULL;
48 l_item_id NUMBER := NULL;
49 l_del_quantity NUMBER := 0;
50 l_sec_del_quantity NUMBER := 0; -- Added for bug 8703085
51 l_quantity NUMBER := 0;
52 l_sec_quantity NUMBER := 0; -- Added for bug 8703085
53 mol_id NUMBER := NULL;
54 line_status NUMBER := NULL;
55 v_lot_control_code NUMBER := NULL;
56 v_serial_control_code NUMBER := NULL;
57 v_allocate_serial_flag VARCHAR2(1) := NULL;
58 l_msg_count NUMBER;
59 l_return_status VARCHAR2(1);
60 l_msg_data VARCHAR2(100);
61 -- bug 2091680
62 l_transfer_lpn_id NUMBER;
63 l_wms_task_types NUMBER;
64 l_content_lpn_id NUMBER;
65 l_count NUMBER;
66 l_fm_serial_number VARCHAR2(30);
67 l_to_serial_number VARCHAR2(30);
68 l_serial_transaction_temp_id NUMBER;
69 l_lpn WMS_CONTAINER_PUB.LPN;
70 l_lpn_context NUMBER;
71
72 l_lpn_id NUMBER; --BUG 12670785 Unload problem
73 l_wlc_count NUMBER;--BUG 12670785 Unload problem
74 -- Added for 14699845 (Flexible Lot Allocation)
75 l_allocate_lot_flag VARCHAR2(1);
76 l_reserved_lot VARCHAR2(240);
77 l_subinventory VARCHAR2(240);
78 l_locator_id NUMBER;
79 l_revision VARCHAR2(10);
80 l_allocated_lpn_id NUMBER;
81 -- Added for 14699845 (Flexible Lot Allocation)
82
83 CURSOR mmtt_to_del(mol_id NUMBER) IS
84 SELECT mmtt.transaction_temp_id
85 , ABS(mmtt.transaction_quantity) --mmtt.primary_quantity
86 , ABS(mmtt.secondary_transaction_quantity) -- Added for bug 8703085
87 FROM mtl_material_transactions_temp mmtt
88 WHERE mmtt.move_order_line_id = mol_id
89 AND NOT EXISTS(
90 SELECT wdt.transaction_temp_id
91 FROM wms_dispatched_tasks wdt
92 WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id
93 AND wdt.transaction_temp_id IS NOT NULL
94 AND wdt.transaction_temp_id <> p_temp_id);
95
96 CURSOR msnt_to_del(p_tmp_id NUMBER) IS
97 SELECT serial_transaction_temp_id
98 FROM mtl_transaction_lots_temp
99 WHERE transaction_temp_id = p_tmp_id;
100
101 CURSOR c_fm_to_serial_number IS
102 SELECT fm_serial_number
103 , to_serial_number
104 FROM mtl_serial_numbers_temp
105 WHERE transaction_temp_id = p_temp_id;
106
107 CURSOR c_fm_to_lot_serial_number (p_sn_temp_id IN NUMBER) IS
108 SELECT fm_serial_number
109 , to_serial_number
110 FROM mtl_serial_numbers_temp msnt
111 WHERE msnt.transaction_temp_id = p_sn_temp_id;
112
113 CURSOR c_lot_allocations IS
114 SELECT serial_transaction_temp_id
115 FROM mtl_transaction_lots_temp
116 WHERE transaction_temp_id = p_temp_id;
117
118 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
119
120 BEGIN
121 IF (l_debug = 1) THEN
122 mydebug(' in unload_task ');
123 END IF;
124
125 print_version_info;
126
127 x_ret_value := 0;
128
129 SELECT COUNT(transaction_temp_id)
130 INTO cnt
131 FROM wms_dispatched_tasks
132 WHERE transaction_temp_id = p_temp_id;
133
134 IF (cnt IN(0, -1)) THEN
135 x_ret_value := 0;
136 x_message := ' NO TASK TO UNLOAD ';
137 RETURN;
138 ELSIF(cnt > 1) THEN
139 x_ret_value := 0;
140 x_message := ' MULTIPLE TASKS IN WDT FOR ' || p_temp_id;
141 RETURN;
142 END IF;
143
144 IF (l_debug = 1) THEN
145 mydebug(' in unload_task past 1 ');
146 END IF;
147
148 BEGIN
149 SELECT move_order_line_id
150 , organization_id
151 , inventory_item_id
152 , content_lpn_id
153 , transfer_lpn_id
154 , wms_task_type
155 , lpn_id --BUG 12670785 Unload problem
156 , subinventory_code -- Added for 14699845 (Flexible Lot Allocation)
157 , locator_id -- Added for 14699845 (Flexible Lot Allocation)
158 , revision -- Added for 14699845 (Flexible Lot Allocation)
159 , allocated_lpn_id -- Added for 14699845 (Flexible Lot Allocation)
160 INTO mol_id
161 , l_org_id
162 , l_item_id
163 , l_content_lpn_id
164 , l_transfer_lpn_id
165 , l_wms_task_types
166 , l_lpn_id --BUG 12670785 Unload problem
167 , l_subinventory -- Added for 14699845 (Flexible Lot Allocation)
168 , l_locator_id -- Added for 14699845 (Flexible Lot Allocation)
169 , l_revision -- Added for 14699845 (Flexible Lot Allocation)
170 , l_allocated_lpn_id -- Added for 14699845 (Flexible Lot Allocation)
171 FROM mtl_material_transactions_temp
172 WHERE transaction_temp_id = p_temp_id;
173
174 IF (l_debug = 1) THEN
175 mydebug(' mol_id ' || mol_id);
176 mydebug(' org_id ' || l_org_id);
177 mydebug(' item_id ' || l_item_id);
178 mydebug(' Printing the value of l_lpn_id : '|| l_lpn_id);
179 mydebug(' printing the value of l_transfer_lpn_id: '|| l_transfer_lpn_id);
180 mydebug(' printing the value of l_content_lpn_id : '|| l_content_lpn_id);
181 END IF;
182 EXCEPTION
183 WHEN NO_DATA_FOUND THEN
184 IF (l_debug = 1) THEN
185 mydebug(' No data found in mtl_material_transactions_temp ');
186 END IF;
187
188 mol_id := -1;
189 END;
190
191 IF (l_debug = 1) THEN
192 mydebug(' mol id :' || mol_id);
193 END IF;
194
195 IF (mol_id IS NOT NULL) THEN
196 BEGIN
197 SELECT line_status
198 INTO line_status
199 FROM mtl_txn_request_lines
200 WHERE line_id = mol_id;
201
202 IF (l_debug = 1) THEN
203 mydebug(' Status ' || line_status);
204 END IF;
205 EXCEPTION
206 WHEN NO_DATA_FOUND THEN
207 IF (l_debug = 1) THEN
208 mydebug('No data found in mtl_txn_request_lines');
209 END IF;
210
211 line_status := -1;
212 END;
213 END IF;
214
215 IF (l_debug = 1) THEN
216 mydebug(' move order line status ' || line_status);
217 END IF;
218
219 IF (line_status = inv_globals.g_to_status_cancel_by_source) THEN
220 IF (l_debug = 1) THEN
221 mydebug(' move order line cancelled ');
222 END IF;
223
224 IF (l_debug = 1) THEN
225 mydebug('deleting allocations ');
226 END IF;
227
228 OPEN mmtt_to_del(mol_id);
229
230 LOOP
231 FETCH mmtt_to_del INTO l_temp_id, l_quantity,l_sec_quantity; -- Added for bug 8703085
232 EXIT WHEN mmtt_to_del%NOTFOUND;
233
234 IF (l_debug = 1) THEN
235 mydebug('deleting allocations l_temp_id:' || l_temp_id || ' l_quantity:' || l_quantity|| ' l_sec_quantity:' || l_sec_quantity);
236 END IF;
237
238 inv_mo_cancel_pvt.reduce_rsv_allocation(
239 x_return_status => l_return_status
240 , x_msg_count => l_msg_count
241 , x_msg_data => x_message
242 , p_transaction_temp_id => l_temp_id
243 , p_quantity_to_delete => l_quantity
244 );
245
246 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
247 IF (l_debug = 1) THEN
248 mydebug(' error returned from inv_mo_cancel_pvt.reduce_rsv_allocation');
249 mydebug(x_message);
250 END IF;
251
252 RAISE fnd_api.g_exc_error;
253 ELSE
254 IF (l_debug = 1) THEN
255 mydebug(' Successful from inv_mo_cancel_pvt.reduce_rsv_allocation Call');
256 END IF;
257
258 l_del_quantity := l_del_quantity + l_quantity;
259 l_sec_del_quantity := l_sec_del_quantity + l_sec_quantity; -- Added for bug 8703085
260 END IF;
261 END LOOP;
262
263 IF (l_debug = 1) THEN
264 mydebug(' alloc quantity deleted ' || l_del_quantity);
265 mydebug(' alloc quantity deleted ' || l_sec_del_quantity);
266 END IF;
267
268 UPDATE mtl_txn_request_lines
269 SET quantity_detailed =(quantity_detailed - l_del_quantity),
270 secondary_quantity_detailed =(secondary_quantity_detailed - l_sec_del_quantity)
271 WHERE line_id = mol_id;
272
273 IF (l_debug = 1) THEN
274 mydebug('updated mol:' || mol_id);
275 END IF;
276
277 DELETE wms_dispatched_tasks
278 WHERE transaction_temp_id = p_temp_id;
279
280 IF (l_debug = 1) THEN
281 mydebug('deleted from wms_dispatched_tasks ');
282 END IF;
283
284 SELECT COUNT(transaction_temp_id)
285 INTO cnt
286 FROM mtl_material_transactions_temp mmtt
287 WHERE mmtt.move_order_line_id = mol_id;
288
289 IF (cnt = 0) THEN
290 IF (l_debug = 1) THEN
291 mydebug('No more allocations in mmtt left for this mo line ' || mol_id);
292 mydebug(' so closing the mo line ' || mol_id);
293 END IF;
294
295 UPDATE mtl_txn_request_lines
296 SET line_status = inv_globals.g_to_status_closed
297 WHERE line_id = mol_id;
298
299 IF (l_debug = 1) THEN
300 mydebug(' updated the mo line status to ' || inv_globals.g_to_status_closed);
301 END IF;
302 ELSE
303 IF (l_debug = 1) THEN
304 mydebug(' allocations in mmtt left for this mo line - count ' || mol_id || ' - ' || cnt);
305 mydebug(' so not closing the mo line ' || mol_id);
306 END IF;
307 END IF;
308 ELSE
309 IF (l_debug = 1) THEN
310 mydebug(' move order line not cancelled ');
311 END IF;
312
313 SELECT msi.lot_control_code
314 , msi.serial_number_control_code
315 , NVL(mmtt.serial_allocated_flag,'N') --HWSNIssue 13860546 --Latent Issue
316 INTO v_lot_control_code
317 , v_serial_control_code
318 , v_allocate_serial_flag
319 FROM mtl_system_items msi
320 , mtl_material_transactions_temp mmtt
321 WHERE msi.inventory_item_id = mmtt.inventory_item_id
322 AND msi.organization_id = mmtt.organization_id
323 AND mmtt.transaction_temp_id = p_temp_id;
324
325 IF l_wms_task_types IN(wms_globals.g_wms_task_type_stg_move) THEN
326 -- We need to do this for staging move as staging move will
327 -- have no MSNT/MTLT lines
328 v_lot_control_code := 0;
329 v_serial_control_code := 0;
330 END IF;
331
332 IF (l_debug = 1) THEN
333 mydebug(' lot code ' || v_lot_control_code);
334 mydebug(' ser_code ' || v_serial_control_code);
335 mydebug(' alloc ser flag' || v_allocate_serial_flag);
336 END IF;
337
338 -- Start changes for 14699845 (Flexible Lot Allocation)
339 IF (v_lot_control_code = 2 )THEN
340 IF l_org_id IS NULL THEN
341 SELECT organization_id
342 INTO l_org_id
343 FROM mtl_material_transactions_temp
344 WHERE transaction_temp_id = p_temp_id;
345 END IF;
346 IF ( l_debug = 1 ) THEN
347 mydebug('l_org_id '||l_org_id);
348 END IF;
349
350 IF (inv_cache.set_org_rec(l_org_id)) THEN
351 IF ( l_debug = 1 ) THEN
352 mydebug('FlexibleLotAlloc Organization id '||l_org_id);
353 mydebug('FlexibleLotAlloc Move Order Line '||mol_id);
354 mydebug('FlexibleLotAlloc Transaction Temp Id '||p_temp_id);
355 mydebug('FlexibleLotAlloc Sub '||l_subinventory);
356 mydebug('FlexibleLotAlloc Loc '||l_locator_id);
357 mydebug('FlexibleLotAlloc Revision '||l_revision);
358 mydebug('FlexibleLotAlloc LPN ID '||l_lpn_id);
359 END IF;
360 l_allocate_lot_flag := wms_rule_pvt.get_allocate_lot_flag(p_organization_id => l_org_id,
361 p_move_order_line_id => mol_id,
362 p_transaction_temp_id => p_temp_id,
363 p_inventory_item_id => l_item_id,
364 p_subinventory_code => l_subinventory,
365 p_locator_id => l_locator_id,
366 p_revision => l_revision,
367 p_lpn_id => l_allocated_lpn_id);
368 IF ( l_debug = 1 ) THEN
369 mydebug('l_allocate_lot_flag: '||l_allocate_lot_flag);
370 END IF;
371 ELSE
372 IF ( l_debug = 1 ) THEN
373 mydebug('ORG Context not found');
374 mydebug('l_allocate_lot_flag: '||l_allocate_lot_flag);
375 END IF;
376 RAISE no_data_found;
377 END IF;
378
379 IF ( l_debug = 1 ) THEN
380 mydebug('FlexibleLotAlloc: Getting allocation params: ');
381 mydebug('FlexibleLotAlloc: v_serial_control_code: '||v_serial_control_code);
382 mydebug('FlexibleLotAlloc: l_allocate_lot_flag: '||l_allocate_lot_flag);
383 mydebug('FlexibleLotAlloc: l_org_id: '||l_org_id);
384 mydebug('FlexibleLotAlloc: p_temp_id: '||p_temp_id);
385 mydebug('FlexibleLotAlloc: v_allocate_serial_flag:'||v_allocate_serial_flag);
386 END IF;
387
388 IF (NVL(l_allocate_lot_flag, 'Y') = 'N' )
389 THEN
390
391 BEGIN
392
393 SELECT NVL(mr.lot_number, mtrl.lot_number)
394 INTO l_reserved_lot
395 FROM mtl_material_transactions_temp mmtt,
396 mtl_reservations mr,
397 mtl_txn_request_lines mtrl
398 WHERE mmtt.transaction_temp_id = p_temp_id
399 AND mmtt.organization_id = l_org_id
400 AND mmtt.reservation_id = mr.reservation_id (+)
401 AND mtrl.line_id = mmtt.move_order_line_id
402 AND mtrl.organization_id = mmtt.organization_id;
403 IF ( l_debug = 1 ) THEN
404 mydebug('FlexibleLotAlloc: l_reserved_lot: '||l_reserved_lot);
405 END IF;
406 EXCEPTION
407 WHEN NO_DATA_FOUND THEN
408 l_reserved_lot := NULL;
409 IF ( l_debug = 1 ) THEN
410 mydebug('FlexibleLotAlloc: No reservation found for this mmtt');
411 END IF;
412 WHEN OTHERS THEN
413 l_reserved_lot := NULL;
414 IF ( l_debug = 1 ) THEN
415 mydebug('FlexibleLotAlloc: No reservation found for this mmtt. SQLCODE '||SQLCODE||' SQLERRM '||SQLERRM);
416 END IF;
417
418 END;
419
420 IF (l_reserved_lot IS NULL
421 AND v_serial_control_code NOT IN (1,6))
422 THEN
423 v_allocate_serial_flag :='N';
424 END IF;
425
426
427 END IF;
428
429 END IF;
430 -- End changes for 14699845 (Flexible Lot Allocation)
431
432 IF (v_allocate_serial_flag <> 'Y') THEN
433 IF (l_debug = 1) THEN
434 mydebug(' alloc serial flag is not y ');
435 END IF;
436
437 IF (v_lot_control_code = 1
438 AND v_serial_control_code NOT IN(1, 6)) THEN
439 IF (l_debug = 1) THEN
440 mydebug(' serial controlled only ');
441 END IF;
442
443 IF (l_debug = 1) THEN
444 mydebug(' deleting msnt with temp id ' || p_temp_id);
445 END IF;
446
447 --UPDATE GROUP_MARK_ID for Serial controlled
448
449 OPEN c_fm_to_serial_number;
450
451 LOOP
452 FETCH c_fm_to_serial_number INTO l_fm_serial_number, l_to_serial_number;
453 EXIT WHEN c_fm_to_serial_number%NOTFOUND;
454
455 UPDATE mtl_serial_numbers
456 SET group_mark_id = NULL
457 WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
458 --Bug 2940878 fix added org and item restriction
459 AND current_organization_id = l_org_id
460 AND inventory_item_id = l_item_id;
461 END LOOP;
462
463 CLOSE c_fm_to_serial_number;
464
465 /**Serial Controlled only ****/
466 DELETE mtl_serial_numbers_temp
467 WHERE transaction_temp_id = p_temp_id;
468 ELSIF(v_lot_control_code = 2
469 AND v_serial_control_code NOT IN(1, 6)) THEN
470 /** Both lot and serial controlled **/
471 IF (l_debug = 1) THEN
472 mydebug(' lot and serial controlled ');
473 END IF;
474
475 IF (l_debug = 1) THEN
476 mydebug(' deleting msnt ');
477 END IF;
478
479 OPEN c_lot_allocations;
480
481 LOOP
482 FETCH c_lot_allocations INTO l_serial_transaction_temp_id;
483 EXIT WHEN c_lot_allocations%NOTFOUND;
484 --UPDATE GROUP_MARK_ID for Lot and serial Controlled
485 OPEN c_fm_to_lot_serial_number (l_serial_transaction_temp_id);
486
487 LOOP
488 FETCH c_fm_to_lot_serial_number INTO l_fm_serial_number, l_to_serial_number;
489 EXIT WHEN c_fm_to_lot_serial_number%NOTFOUND;
490
491 UPDATE mtl_serial_numbers
492 SET group_mark_id = NULL
493 WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
494 --Bug 2940878 fix added org and item restriction
495 AND current_organization_id = l_org_id
496 AND inventory_item_id = l_item_id;
497 END LOOP;
498
499 CLOSE c_fm_to_lot_serial_number;
500
501 DELETE FROM mtl_serial_numbers_temp
502 WHERE transaction_temp_id = l_serial_transaction_temp_id;
503 END LOOP;
504
505 CLOSE c_lot_allocations;
506
507 DELETE mtl_serial_numbers_temp
508 WHERE transaction_temp_id IN(SELECT mtlt.serial_transaction_temp_id
509 FROM mtl_transaction_lots_temp mtlt
510 WHERE mtlt.transaction_temp_id = p_temp_id);
511
512 IF (l_debug = 1) THEN
513 mydebug(' updating mtlt ');
514 END IF;
515
516 UPDATE mtl_transaction_lots_temp
517 SET serial_transaction_temp_id = NULL
518 WHERE transaction_temp_id = p_temp_id;
519 IF (l_debug = 1) THEN
520 mydebug(' update done ');
521 END IF;
522 END IF;
523 END IF;
524 -- Start changes for 14699845 (Flexible Lot Allocation)
525 IF v_lot_control_code = 2 THEN
526 clean_LS_Flexible_unload( l_return_status
527 , p_temp_id
528 , l_allocate_lot_flag
529 , l_reserved_lot
530 , l_org_id);
531 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
532 RAISE fnd_api.g_exc_error;
533 END IF;
534 END IF;
535 -- End changes for 14699845 (Flexible Lot Allocation)
536
537 IF (l_debug = 1) THEN
538 mydebug('deleting WDT with temp_id ' || p_temp_id);
539 END IF;
540
541 -- added following for bug fix 2769358
542
543 IF l_content_lpn_id IS NOT NULL THEN
544 IF (l_debug = 1) THEN
545 mydebug('Set lpn context to packing for lpn_ID : ' || l_content_lpn_id);
546 END IF;
547
548 --bug 4411814
549 l_lpn.lpn_id := l_content_lpn_id;
550 l_lpn.organization_id := l_org_id;
551 l_lpn.lpn_context := wms_container_pub.lpn_context_inv;
552
553 wms_container_pvt.Modify_LPN
554 (
555 p_api_version => 1.0
556 , p_validation_level => fnd_api.g_valid_level_none
557 , x_return_status => l_return_status
558 , x_msg_count => l_msg_count
559 , x_msg_data => l_msg_data
560 , p_lpn => l_lpn
561 ) ;
562
563 l_lpn := NULL;
564
565 END IF;
566
567 --The lpn ids must be set to null for this task
568 UPDATE mtl_material_transactions_temp
569 SET lpn_id = NULL
570 , content_lpn_id = NULL
571 , transfer_lpn_id = NULL
572 , wms_task_status = 1 -- Bug4185621: update mmtt task status back to pending
573 WHERE transaction_temp_id = p_temp_id;
574
575 DELETE wms_dispatched_tasks
576 WHERE transaction_temp_id = p_temp_id;
577
578 IF (l_debug = 1) THEN
579 mydebug('deleted WDT with temp_id ' || p_temp_id);
580 END IF;
581
582 IF l_wms_task_types IN(wms_globals.g_wms_task_type_stg_move) THEN
583 DELETE FROM mtl_material_transactions_temp
584 WHERE transaction_temp_id = p_temp_id;
585 END IF;
586 END IF;
587
588
589 -- Bug 2091680 . Update the LPN context to defined but not used if the
590 -- lpn is unloaded with a context of packaging and update the context to
591 -- inventory if the entire lpn is picked
592 -- this happens only if there are no more allocations for that lpn and
593 -- the last line IS being unloaded
594 IF l_wms_task_types IN ( wms_globals.g_wms_task_type_pick
595 , wms_globals.g_wms_task_type_replenish
596 , wms_globals.g_wms_task_type_moxfer
597 , wms_globals.g_wms_task_type_moissue--13807883
598 )
599 THEN
600 SELECT COUNT(1)
601 INTO l_count
602 FROM mtl_material_transactions_temp
603 WHERE transfer_lpn_id = l_transfer_lpn_id;
604
605 IF l_count = 0 THEN -- no more rows and the current row is the
606 --last allocation
607 BEGIN
608 SELECT lpn_context INTO l_lpn_context
609 FROM wms_license_plate_numbers
610 WHERE lpn_id = l_transfer_lpn_id;
611 EXCEPTION
612 WHEN no_data_found THEN
613 l_lpn_context := NULL;
614 END;
615
616 IF (l_debug = 1) THEN
617 mydebug('before going to my condition printing content lpn id ' || l_content_lpn_id);
618 END IF;
619
620 IF l_content_lpn_id IS NOT NULL
621 AND l_content_lpn_id = l_transfer_lpn_id THEN
622
623 IF l_lpn_context <> 1 AND l_lpn_context IS NOT NULL THEN
624 IF (l_debug = 1) THEN
625 mydebug('at place where content lpn is not null and content lpn equals txfer lpn the value of txfer lpn is ' || l_transfer_lpn_id);
626 END IF;
627
628
629 --bug 4411814
630 l_lpn.lpn_id := l_transfer_lpn_id;
631 l_lpn.organization_id := l_org_id;
632 l_lpn.lpn_context := 1;
633
634 wms_container_pvt.Modify_LPN
635 (
636 p_api_version => 1.0
637 , p_validation_level => fnd_api.g_valid_level_none
638 , x_return_status => l_return_status
639 , x_msg_count => l_msg_count
640 , x_msg_data => l_msg_data
641 , p_lpn => l_lpn
642 ) ;
643
644 l_lpn := NULL;
645 END IF;
646 --Added for Lot substitution Unload changing context to 5 issue..
647 ELSIF l_lpn_id = l_transfer_lpn_id THEN
648 IF (l_debug = 1) THEN
649 mydebug('the value of txfer lpn is same as lpn id , value of txfer lpn is ' || l_transfer_lpn_id);
650 END IF;
651
652 l_lpn.lpn_id := l_transfer_lpn_id;
653 l_lpn.organization_id := l_org_id;
654 l_lpn.lpn_context := 1;
655
656 wms_container_pvt.Modify_LPN
657 (
658 p_api_version => 1.0
659 , p_validation_level => fnd_api.g_valid_level_none
660 , x_return_status => l_return_status
661 , x_msg_count => l_msg_count
662 , x_msg_data => l_msg_data
663 , p_lpn => l_lpn
664 ) ;
665
666 l_lpn := NULL;
667 --End of change for unloading issue.
668
669 ELSE
670
671 IF l_lpn_context = 8 THEN
672 IF (l_debug = 1) THEN
673 mydebug('in the else portion where lpn context is 8 , value of txfer lpn is ' || l_transfer_lpn_id);
674 END IF;
675 --bug 4411814
676 l_lpn.lpn_id := l_transfer_lpn_id;
677 l_lpn.organization_id := l_org_id;
678
679 --BUG 12670785 adding to get WLC count. If WLC is present then make LPN context to 1 other wise make it 5.
680 --changed below to txfer lpn id otherwise context was going to 1 for txfer lpn
681 l_wlc_count := 0;
682 select count(1) into l_wlc_count from wms_lpn_contents where parent_lpn_id = l_transfer_lpn_id and organization_id = l_org_id;
683 if (l_wlc_count > 0) then
684 l_lpn.lpn_context := 1;
685 else
686 l_lpn.lpn_context := 5;
687 end if;
688
689 IF (l_debug = 1) THEN
690 mydebug('Added code to set the lpn context correctly in case of unload .. 1 if WLC present 5 otherwise..' || l_lpn.lpn_context);
691 END IF;
692 --BUG 12670785 end
693
694 wms_container_pvt.Modify_LPN
695 (
696 p_api_version => 1.0
697 , p_validation_level => fnd_api.g_valid_level_none
698 , x_return_status => l_return_status
699 , x_msg_count => l_msg_count
700 , x_msg_data => l_msg_data
701 , p_lpn => l_lpn
702 ) ;
703
704 l_lpn := NULL;
705
706 END IF;
707
708 END IF;
709
710 END IF;
711
712 ELSIF l_wms_task_types = wms_globals.g_wms_task_type_stg_move THEN
713
714 IF (l_debug = 1) THEN
715 mydebug('Calling wms_container_pvt.Modify_LPN_Wrapper for staging move. p_lpn_id = '||l_content_lpn_id);
716 mydebug('p_lpn_context = '|| wms_container_pub.LPN_CONTEXT_PICKED );
717 END IF;
718
719 wms_container_pub.Modify_LPN_Wrapper
720 ( p_api_version => 1.0
721 ,x_return_status => l_return_status
722 ,x_msg_count => l_msg_count
723 ,x_msg_data => x_message
724 ,p_lpn_id => l_content_lpn_id
725 ,p_lpn_context => wms_container_pub.lpn_context_picked
726 );
727
728 IF (l_debug = 1) THEN
729 mydebug('wms_container_pvt.Modify_LPN_Wrapper x_return_status = '||l_return_status);
730 END IF;
731
732 END IF;
733
734 x_ret_value := 1;
735
736 IF (l_debug = 1) THEN
737 mydebug('done unload_task x_ret ' || x_ret_value);
738 END IF;
739
740 -- Doing an explicit commit
741 -- HERE
742
743 COMMIT;
744 EXCEPTION
745 WHEN OTHERS THEN
746 x_ret_value := 0;
747
748 IF (l_debug = 1) THEN
749 mydebug(' In exception unload_task x_ret' || x_ret_value);
750 END IF;
751
752 fnd_msg_pub.count_and_get(p_count => msg_cnt, p_data => x_message);
753 END unload_task;
754
755
756
757 PROCEDURE unload_bulk_task
758 ( x_next_temp_id OUT NOCOPY NUMBER
759 , x_return_status OUT NOCOPY VARCHAR2
760 , p_txn_temp_id IN NUMBER
761 ) IS
762 l_count NUMBER := 0;
763 l_line_type VARCHAR2(20) := NULL;
764 l_temp_id NUMBER := NULL;
765 l_org_id NUMBER := NULL;
766 l_item_id NUMBER := NULL;
767 l_quantity NUMBER := 0;
768 l_txn_uom VARCHAR2(3) := NULL;
769 l_mo_line_uom VARCHAR2(3) := NULL;
770 l_conv_qty NUMBER := 0;
771 l_mo_line_id NUMBER := NULL;
772 v_lot_control_code NUMBER := NULL;
773 v_serial_control_code NUMBER := NULL;
774 v_allocate_serial_flag VARCHAR2(1) := NULL;
775 l_msg_count NUMBER;
776 l_msg_data VARCHAR2(2000);
777 l_return_status VARCHAR2(1);
778 -- bug 2091680
779 l_transfer_lpn_id NUMBER;
780 l_content_lpn_id NUMBER;
781 l_fm_serial_number VARCHAR2(30);
782 l_to_serial_number VARCHAR2(30);
783 l_serial_transaction_temp_id NUMBER;
784 l_lpn WMS_CONTAINER_PUB.LPN;
785 l_lpn_context NUMBER;
786
787 CURSOR c_cncl_ovrpick_lines (p_temp_id IN NUMBER) IS
788 SELECT 'CANCELLED'
789 , mmtt.transaction_temp_id
790 , ABS(mmtt.transaction_quantity)
791 , mmtt.transaction_uom
792 , mmtt.move_order_line_id
793 , mtrl.uom_code
794 FROM mtl_material_transactions_temp mmtt
795 , mtl_txn_request_lines mtrl
796 WHERE mmtt.parent_line_id = p_temp_id
797 AND mtrl.line_id = mmtt.move_order_line_id
798 AND mtrl.line_status = 9
799 UNION ALL
800 SELECT 'OVERPICKED'
801 , mmtt.transaction_temp_id
802 , ABS(mmtt.transaction_quantity)
803 , mmtt.transaction_uom
804 , to_number(NULL)
805 , to_char(NULL)
806 FROM mtl_material_transactions_temp mmtt
807 WHERE mmtt.parent_line_id = p_temp_id
808 AND mmtt.transaction_temp_id <> mmtt.parent_line_id
809 AND mmtt.transaction_action_id = 2
810 AND mmtt.move_order_line_id IS NULL;
811
812
813 CURSOR msnt_to_del (p_temp_id IN NUMBER) IS
814 SELECT serial_transaction_temp_id
815 FROM mtl_transaction_lots_temp
816 WHERE transaction_temp_id = p_temp_id;
817
818
819 CURSOR c_fm_to_serial_number (p_temp_id IN NUMBER) IS
820 SELECT fm_serial_number
821 , to_serial_number
822 FROM mtl_serial_numbers_temp
823 WHERE transaction_temp_id = p_temp_id;
824
825
826 CURSOR c_fm_to_lot_serial_number (p_sn_temp_id IN NUMBER) IS
827 SELECT fm_serial_number
828 , to_serial_number
829 FROM mtl_serial_numbers_temp msnt
830 WHERE msnt.transaction_temp_id = p_sn_temp_id;
831
832
833 CURSOR c_lot_allocations (p_temp_id IN NUMBER) IS
834 SELECT serial_transaction_temp_id
835 FROM mtl_transaction_lots_temp
836 WHERE transaction_temp_id = p_temp_id;
837
838
839 CURSOR c_next_temp_id
840 ( p_xfer_lpn_id IN NUMBER
841 , p_temp_id IN NUMBER
842 ) IS
843 -- Material packed into content LPNs
844 SELECT m.transaction_temp_id
845 , 1 dummy_sort
846 FROM wms_dispatched_tasks w
847 , mtl_material_transactions_temp m
848 WHERE m.transfer_lpn_id = p_xfer_lpn_id
849 AND m.transaction_temp_id <> p_temp_id
850 AND m.transaction_temp_id = m.parent_line_id
851 AND w.transaction_temp_id = m.transaction_temp_id
852 AND w.status = 4
853 AND EXISTS
854 ( SELECT 'x'
855 FROM mtl_material_transactions_temp m2
856 WHERE m2.transfer_lpn_id = m.transfer_lpn_id
857 AND m2.organization_id = m.organization_id
858 AND m2.transaction_temp_id = m2.parent_line_id
859 AND m2.transaction_temp_id <> m.transaction_temp_id
860 AND m2.transaction_temp_id <> p_temp_id
861 AND m2.content_lpn_id = m.transfer_lpn_id
862 )
863 UNION ALL
864 -- Content LPNs
865 SELECT m.transaction_temp_id
866 , 2 dummy_sort
867 FROM wms_dispatched_tasks w
868 , mtl_material_transactions_temp m
869 WHERE m.transfer_lpn_id = p_xfer_lpn_id
870 AND m.transaction_temp_id <> p_temp_id
871 AND m.transaction_temp_id = m.parent_line_id
872 AND w.transaction_temp_id = m.transaction_temp_id
873 AND w.status = 4
874 AND m.content_lpn_id IS NOT NULL
875 UNION ALL
876 -- Material unpacked from content LPNs
877 SELECT m.transaction_temp_id
878 , 3 dummy_sort
879 FROM wms_dispatched_tasks w
880 , mtl_material_transactions_temp m
881 WHERE m.transfer_lpn_id = p_xfer_lpn_id
882 AND m.transaction_temp_id <> p_temp_id
883 AND m.transaction_temp_id = m.parent_line_id
884 AND w.transaction_temp_id = m.transaction_temp_id
885 AND w.status = 4
886 AND EXISTS
887 ( SELECT 'x'
888 FROM mtl_material_transactions_temp m2
889 WHERE m2.transfer_lpn_id = m.transfer_lpn_id
890 AND m2.organization_id = m.organization_id
891 AND m2.transaction_temp_id = m2.parent_line_id
892 AND m2.transaction_temp_id <> m.transaction_temp_id
893 AND m2.transaction_temp_id <> p_temp_id
894 AND m2.content_lpn_id = m.lpn_id
895 )
896 UNION ALL
897 -- All other picked material
898 SELECT m.transaction_temp_id
899 , 4 dummy_sort
900 FROM wms_dispatched_tasks w
901 , mtl_material_transactions_temp m
902 WHERE m.transfer_lpn_id = p_xfer_lpn_id
903 AND m.transaction_temp_id <> p_temp_id
904 AND m.transaction_temp_id = m.parent_line_id
905 AND w.transaction_temp_id = m.transaction_temp_id
906 AND w.status = 4
907 AND m.content_lpn_id IS NULL
908 AND ( (m.lpn_id IS NOT NULL
909 AND NOT EXISTS
910 ( SELECT 'x'
911 FROM mtl_material_transactions_temp m2
912 WHERE m2.transfer_lpn_id = m.transfer_lpn_id
913 AND m2.organization_id = m.organization_id
914 AND m2.transaction_temp_id = m2.parent_line_id
915 AND m2.transaction_temp_id <> m.transaction_temp_id
916 AND m2.transaction_temp_id <> p_temp_id
917 AND m2.content_lpn_id = m.lpn_id
918 )
919 )
920 OR m.lpn_id IS NULL
921 )
922 AND NOT EXISTS
923 ( SELECT 'x'
924 FROM mtl_material_transactions_temp m3
925 WHERE m3.transfer_lpn_id = m.transfer_lpn_id
926 AND m3.organization_id = m.organization_id
927 AND m3.transaction_temp_id = m3.parent_line_id
928 AND m3.transaction_temp_id <> m.transaction_temp_id
929 AND m3.transaction_temp_id <> p_temp_id
930 AND m3.content_lpn_id = m.transfer_lpn_id
931 )
932 ORDER BY dummy_sort;
933
934
935 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
936 l_parent_deleted BOOLEAN := FALSE;
937 l_dum_sort NUMBER;
938 -- Added for 14699845 (Flexible Lot Allocation)
939 l_allocate_lot_flag VARCHAR2(1);
940 l_mol_id NUMBER;
941 l_subinventory VARCHAR2(80);
942 l_locator_id NUMBER;
943 l_revision VARCHAR2(10);
944 l_reserved_lot VARCHAR2(80);
945 l_allocated_lpn_id NUMBER;
946 -- Added for 14699845 (Flexible Lot Allocation)
947
948 BEGIN
949
950 IF l_debug = 1 THEN
951 mydebug
952 ( 'Entered with parameters: ' || g_newline ||
953 'p_txn_temp_id => ' || to_char(p_txn_temp_id)
954 );
955 END IF;
956
957 print_version_info;
958
959 x_return_status := fnd_api.g_ret_sts_success;
960 x_next_temp_id := NULL;
961
962 SAVEPOINT unload_bulk_sp;
963
964 BEGIN
965 SELECT organization_id
966 , inventory_item_id
967 , content_lpn_id
968 , transfer_lpn_id
969 , move_order_line_id -- Added for 14699845 (Flexible Lot Allocation)
970 , subinventory_code -- Added for 14699845 (Flexible Lot Allocation)
971 , locator_id -- Added for 14699845 (Flexible Lot Allocation)
972 , revision -- Added for 14699845 (Flexible Lot Allocation)
973 INTO l_org_id
974 , l_item_id
975 , l_content_lpn_id
976 , l_transfer_lpn_id
977 , l_mol_id -- Added for 14699845 (Flexible Lot Allocation)
978 , l_subinventory -- Added for 14699845 (Flexible Lot Allocation)
979 , l_locator_id -- Added for 14699845 (Flexible Lot Allocation)
980 , l_revision -- Added for 14699845 (Flexible Lot Allocation)
981 FROM mtl_material_transactions_temp
982 WHERE transaction_temp_id = p_txn_temp_id;
983
984 IF (l_debug = 1) THEN
985 mydebug('Org_id: ' || to_char(l_org_id) ||
986 ', item_id: ' || to_char(l_item_id) ||
987 ', content LPN: ' || to_char(l_content_lpn_id) ||
988 ', transfer LPN: ' || to_char(l_transfer_lpn_id)
989 );
990 END IF;
991 EXCEPTION
992 WHEN NO_DATA_FOUND THEN
993 IF (l_debug = 1) THEN
994 mydebug(' No data found in mtl_material_transactions_temp ');
995 END IF;
996 END;
997
998 IF l_content_lpn_id IS NOT NULL THEN
999 IF (l_debug = 1) THEN
1000 mydebug('Set lpn context to resides in INV for lpn_ID: ' ||
1001 to_char(l_content_lpn_id)
1002 );
1003 END IF;
1004
1005
1006 --bug 4411814
1007 l_lpn.lpn_id := l_content_lpn_id;
1008 l_lpn.organization_id := l_org_id;
1009 l_lpn.lpn_context := wms_container_pub.lpn_context_inv;
1010
1011 wms_container_pvt.Modify_LPN
1012 (
1013 p_api_version => 1.0
1014 , p_validation_level => fnd_api.g_valid_level_none
1015 , x_return_status => l_return_status
1016 , x_msg_count => l_msg_count
1017 , x_msg_data => l_msg_data
1018 , p_lpn => l_lpn
1019 ) ;
1020
1021 l_lpn := NULL;
1022
1023 END IF;
1024
1025 OPEN c_cncl_ovrpick_lines (p_txn_temp_id);
1026 LOOP
1027 FETCH c_cncl_ovrpick_lines
1028 INTO l_line_type
1029 , l_temp_id
1030 , l_quantity
1031 , l_txn_uom
1032 , l_mo_line_id
1033 , l_mo_line_uom;
1034 EXIT WHEN c_cncl_ovrpick_lines%NOTFOUND;
1035
1036 IF (l_debug = 1) THEN
1037 mydebug('Deleting l_temp_id: ' || to_char(l_temp_id) ||
1038 ', l_quantity: ' || to_char(l_quantity) ||
1039 ', l_txn_uom: ' || l_txn_uom ||
1040 ', l_mo_line_id: ' || to_char(l_mo_line_id) ||
1041 ', l_mo_line_uom: ' || l_mo_line_uom ||
1042 ', l_line_type: ' || l_line_type
1043 );
1044 END IF;
1045
1046 l_return_status := fnd_api.g_ret_sts_success;
1047
1048 IF l_line_type = 'CANCELLED'
1049 THEN
1050 inv_mo_cancel_pvt.reduce_rsv_allocation
1051 ( x_return_status => l_return_status
1052 , x_msg_count => l_msg_count
1053 , x_msg_data => l_msg_data
1054 , p_transaction_temp_id => l_temp_id
1055 , p_quantity_to_delete => l_quantity
1056 );
1057 ELSIF l_line_type = 'OVERPICKED'
1058 THEN
1059 inv_trx_util_pub.delete_transaction
1060 ( x_return_status => l_return_status
1061 , x_msg_data => l_msg_data
1062 , x_msg_count => l_msg_count
1063 , p_transaction_temp_id => l_temp_id
1064 , p_update_parent => TRUE
1065 );
1066 END IF;
1067
1068 IF (l_return_status <> fnd_api.g_ret_sts_success)
1069 THEN
1070 IF (l_debug = 1) THEN
1071 mydebug('Error returned from API for deleting transaction');
1072 mydebug(l_msg_data);
1073 END IF;
1074
1075 RAISE fnd_api.g_exc_error;
1076 END IF;
1077
1078 IF l_mo_line_id IS NOT NULL
1079 THEN
1080 IF l_mo_line_uom <> l_txn_uom
1081 THEN
1082 l_conv_qty := inv_convert.inv_um_convert
1083 ( item_id => l_item_id
1084 , PRECISION => NULL
1085 , from_quantity => l_quantity
1086 , from_unit => l_txn_uom
1087 , to_unit => l_mo_line_uom
1088 , from_name => NULL
1089 , to_name => NULL
1090 );
1091 ELSE
1092 l_conv_qty := l_quantity;
1093 END IF;
1094
1095 UPDATE mtl_txn_request_lines
1096 SET quantity_detailed = (quantity_detailed - l_conv_qty)
1097 WHERE line_id = l_mo_line_id;
1098
1099 IF (l_debug = 1) THEN
1100 mydebug('Updated mol: ' || to_char(l_mo_line_id));
1101 END IF;
1102
1103 SELECT COUNT(transaction_temp_id)
1104 INTO l_count
1105 FROM mtl_material_transactions_temp mmtt
1106 WHERE mmtt.move_order_line_id = l_mo_line_id;
1107
1108 IF (l_count = 0) THEN
1109 IF (l_debug = 1) THEN
1110 mydebug('No more allocations for mo line ' || to_char(l_mo_line_id));
1111 END IF;
1112
1113 UPDATE mtl_txn_request_lines
1114 SET line_status = inv_globals.g_to_status_closed
1115 WHERE line_id = l_mo_line_id;
1116 ELSE
1117 IF (l_debug = 1) THEN
1118 mydebug('Allocations left: ' || to_char(l_count));
1119 END IF;
1120 END IF;
1121 END IF; -- end if move order line ID is not null
1122 END LOOP;
1123
1124 --
1125 -- Proceed only if parent still exists
1126 --
1127 BEGIN
1128 SELECT msi.lot_control_code
1129 , msi.serial_number_control_code
1130 , NVL(mmtt.serial_allocated_flag,'N') -- Modified for 14699845 (Flexible Lot Allocation) Added NVL Latent
1131 INTO v_lot_control_code
1132 , v_serial_control_code
1133 , v_allocate_serial_flag
1134 FROM mtl_system_items msi
1135 , mtl_material_transactions_temp mmtt
1136 WHERE msi.inventory_item_id = mmtt.inventory_item_id
1137 AND msi.organization_id = mmtt.organization_id
1138 AND mmtt.transaction_temp_id = p_txn_temp_id;
1139 EXCEPTION
1140 WHEN NO_DATA_FOUND THEN
1141 IF (l_debug = 1) THEN
1142 mydebug('Parent MMTT deleted when cancelled child tasks were processed');
1143 END IF;
1144 l_parent_deleted := TRUE;
1145
1146 WHEN OTHERS THEN
1147 RAISE;
1148 END;
1149
1150 IF NOT l_parent_deleted THEN
1151 IF (l_debug = 1) THEN
1152 mydebug(' lot code ' || v_lot_control_code);
1153 mydebug(' ser_code ' || v_serial_control_code);
1154 mydebug(' alloc ser flag' || v_allocate_serial_flag);
1155 END IF;
1156 -- Start changes for 14699845 (Flexible Lot Allocation)
1157 IF (v_lot_control_code = 2 )THEN
1158 IF l_org_id IS NULL THEN
1159 SELECT organization_id
1160 INTO l_org_id
1161 FROM mtl_material_transactions_temp
1162 WHERE transaction_temp_id = p_txn_temp_id;
1163 END IF;
1164 IF ( l_debug = 1 ) THEN
1165 mydebug('l_org_id '||l_org_id);
1166 END IF;
1167
1168 IF (inv_cache.set_org_rec(l_org_id)) THEN
1169 IF ( l_debug = 1 ) THEN
1170 mydebug('FlexibleLotAlloc Organization id '||l_org_id);
1171 mydebug('FlexibleLotAlloc Move Order Line '||l_mol_id);
1172 mydebug('FlexibleLotAlloc Transaction Temp Id '||p_txn_temp_id);
1173 mydebug('FlexibleLotAlloc Sub '||l_subinventory);
1174 mydebug('FlexibleLotAlloc Loc '||l_locator_id);
1175 mydebug('FlexibleLotAlloc Revision '||l_revision);
1176 END IF;
1177
1178 BEGIN
1179 SELECT mmttc.move_order_line_id
1180 INTO l_mol_id
1181 FROM mtl_material_transactions_temp mmttc, mtl_material_transactions_temp mmttp
1182 WHERE mmttc.parent_line_id = mmttp.transaction_temp_id
1183 AND mmttc.parent_line_id <> mmttc.transaction_temp_id
1184 AND mmttc.organization_id = mmttp.organization_id
1185 AND mmttc.organization_id = l_org_id
1186 AND mmttp.transaction_temp_id = p_txn_temp_id
1187 AND mmttp.transaction_temp_id = mmttp.parent_line_id
1188 AND ROWNUM = 1;
1189 EXCEPTION
1190 WHEN OTHERS THEN
1191 IF ( l_debug = 1 ) THEN
1192 mydebug('This should not have happened');
1193 mydebug('SQLCODE: '||SQLCODE||' SQLERRM '||SQLERRM);
1194 END IF;
1195 END;
1196
1197 l_allocate_lot_flag := wms_rule_pvt.get_allocate_lot_flag(p_organization_id => l_org_id,
1198 p_move_order_line_id => l_mol_id,
1199 p_transaction_temp_id => p_txn_temp_id,
1200 p_inventory_item_id => l_item_id,
1201 p_subinventory_code => l_subinventory,
1202 p_locator_id => l_locator_id,
1203 p_revision => l_revision,
1204 p_lpn_id => NULL);
1205 IF ( l_debug = 1 ) THEN
1206 mydebug('l_allocate_lot_flag: '||l_allocate_lot_flag);
1207 END IF;
1208 ELSE
1209 IF ( l_debug = 1 ) THEN
1210 mydebug('ORG Context not found');
1211 mydebug('l_allocate_lot_flag: '||l_allocate_lot_flag);
1212 END IF;
1213 RAISE no_data_found;
1214 END IF;
1215 IF (l_debug = 1) THEN
1216 mydebug('Calling get_allocation_params ');
1217 END IF;
1218
1219 IF (NVL(l_allocate_lot_flag, 'Y') = 'N' )
1220 THEN
1221
1222 BEGIN
1223
1224 SELECT mr.lot_number
1225 INTO l_reserved_lot
1226 FROM mtl_material_transactions_temp mmttc, mtl_material_transactions_temp mmttp,
1227 mtl_reservations mr
1228 WHERE mmttc.parent_line_id = mmttp.transaction_temp_id
1229 AND mmttc.transaction_temp_id <> mmttc.parent_line_id
1230 AND mmttc.organization_id = l_org_id
1231 AND mmttc.reservation_id = mr.reservation_id (+)
1232 AND mmttp.transaction_temp_id = p_txn_temp_id
1233 AND mmttp.parent_line_id = mmttp.transaction_temp_id
1234 AND mmttp.organization_id = mmttc.organization_id
1235 AND ROWNUM = 1;
1236
1237 IF ( l_debug = 1 ) THEN
1238 mydebug('FlexibleLotAlloc: l_reserved_lot: '||l_reserved_lot);
1239 END IF;
1240 EXCEPTION
1241 WHEN NO_DATA_FOUND THEN
1242 l_reserved_lot := NULL;
1243 IF ( l_debug = 1 ) THEN
1244 mydebug('FlexibleLotAlloc: No reservation found for this mmtt');
1245 END IF;
1246 WHEN OTHERS THEN
1247 l_reserved_lot := NULL;
1248 IF ( l_debug = 1 ) THEN
1249 mydebug('FlexibleLotAlloc: No reservation found for this mmtt. SQLCODE '||SQLCODE||' SQLERRM '||SQLERRM);
1250 END IF;
1251
1252 END;
1253
1254 IF (l_reserved_lot IS NULL
1255 AND v_serial_control_code NOT IN (1,6))
1256 THEN
1257 v_allocate_serial_flag :='N';
1258 END IF;
1259
1260
1261 END IF;
1262
1263 END IF;
1264 -- End changes for 14699845 (Flexible Lot Allocation)
1265
1266 IF (v_allocate_serial_flag <> 'Y') THEN
1267 IF (l_debug = 1) THEN
1268 mydebug('Alloc serial flag is not y ');
1269 END IF;
1270
1271 IF (v_lot_control_code = 1
1272 AND v_serial_control_code NOT IN(1, 6))
1273 THEN
1274 IF (l_debug = 1) THEN
1275 mydebug('Serial controlled only.');
1276 END IF;
1277
1278 --
1279 -- Update group_mark_id for Serial controlled
1280 --
1281 OPEN c_fm_to_serial_number (p_txn_temp_id);
1282 LOOP
1283 FETCH c_fm_to_serial_number INTO l_fm_serial_number, l_to_serial_number;
1284 EXIT WHEN c_fm_to_serial_number%NOTFOUND;
1285
1286 UPDATE mtl_serial_numbers
1287 SET group_mark_id = NULL
1288 WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
1289 AND current_organization_id = l_org_id
1290 AND inventory_item_id = l_item_id;
1291
1292 IF (l_debug = 1) THEN
1293 mydebug('Unmarked serials between ' || l_fm_serial_number ||
1294 ' and ' || l_to_serial_number ||
1295 '. Now deleting MSNT ' || to_char(p_txn_temp_id)
1296 );
1297 END IF;
1298 END LOOP;
1299
1300 CLOSE c_fm_to_serial_number;
1301
1302 DELETE mtl_serial_numbers_temp
1303 WHERE transaction_temp_id = p_txn_temp_id;
1304
1305 ELSIF(v_lot_control_code = 2
1306 AND v_serial_control_code NOT IN(1, 6))
1307 THEN
1308 /** Both lot and serial controlled **/
1309 IF (l_debug = 1) THEN
1310 mydebug('Lot and serial controlled ');
1311 END IF;
1312
1313 OPEN c_lot_allocations (p_txn_temp_id);
1314 LOOP
1315 FETCH c_lot_allocations INTO l_serial_transaction_temp_id;
1316 EXIT WHEN c_lot_allocations%NOTFOUND;
1317
1318 --
1319 -- Update group_mark_id for lot and serial controlled
1320 --
1321 OPEN c_fm_to_lot_serial_number (l_serial_transaction_temp_id);
1322 LOOP
1323 FETCH c_fm_to_lot_serial_number INTO l_fm_serial_number, l_to_serial_number;
1324 EXIT WHEN c_fm_to_lot_serial_number%NOTFOUND;
1325
1326 UPDATE mtl_serial_numbers
1327 SET group_mark_id = NULL
1328 WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
1329 AND current_organization_id = l_org_id
1330 AND inventory_item_id = l_item_id;
1331 END LOOP;
1332
1333 CLOSE c_fm_to_lot_serial_number;
1334
1335 IF (l_debug = 1) THEN
1336 mydebug('Unmarked serials between ' || l_fm_serial_number ||
1337 ' and ' || l_to_serial_number ||
1338 '. Now deleting MSNT ' || to_char(l_serial_transaction_temp_id)
1339 );
1340 END IF;
1341
1342 DELETE FROM mtl_serial_numbers_temp
1343 WHERE transaction_temp_id = l_serial_transaction_temp_id;
1344 END LOOP;
1345 CLOSE c_lot_allocations;
1346
1347 UPDATE mtl_transaction_lots_temp
1348 SET serial_transaction_temp_id = NULL
1349 WHERE transaction_temp_id = p_txn_temp_id;
1350
1351 IF (l_debug = 1) THEN
1352 mydebug('Updated MTLT');
1353 END IF;
1354 END IF; -- end if lot/serial controlled
1355 END IF; -- end if serial not allocated
1356
1357 -- Start changes for 14699845 (Flexible Lot Allocation)
1358 IF v_lot_control_code = 2 THEN
1359 clean_LS_Flexible_unload( l_return_status
1360 , p_txn_temp_id
1361 , l_allocate_lot_flag
1362 , l_reserved_lot
1363 , l_org_id
1364 , 'Y');
1365 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1366 RAISE fnd_api.g_exc_error;
1367 END IF;
1368 END IF;
1369 -- End changes for 14699845 (Flexible Lot Allocation)
1370 IF (l_debug = 1) THEN
1371 mydebug('deleting WDT with temp_id: ' || to_char(p_txn_temp_id));
1372 END IF;
1373
1374 DELETE wms_dispatched_tasks
1375 WHERE transaction_temp_id = p_txn_temp_id;
1376
1377 --
1378 -- The lpn ids must be set to null for this task
1379 -- for both parent and child records
1380 --
1381 UPDATE mtl_material_transactions_temp
1382 SET lpn_id = NULL
1383 , content_lpn_id = NULL
1384 , transfer_lpn_id = NULL
1385 , wms_task_status = 1 -- Bug4185621: update mmtt task status back to pending
1386 WHERE parent_line_id = p_txn_temp_id;
1387
1388 END IF; -- end if parent not deleted
1389
1390 l_count := 0;
1391 SELECT COUNT(*)
1392 INTO l_count
1393 FROM mtl_material_transactions_temp
1394 WHERE transfer_lpn_id = l_transfer_lpn_id;
1395
1396 IF l_count = 0 THEN
1397 --
1398 -- no more rows and the current row is the
1399 -- last allocation
1400 --
1401 IF l_content_lpn_id IS NULL THEN
1402 BEGIN
1403 SELECT lpn_context INTO l_lpn_context
1404 FROM wms_license_plate_numbers
1405 WHERE lpn_id = l_transfer_lpn_id;
1406 EXCEPTION
1407 WHEN no_data_found THEN
1408 l_lpn_context := NULL;
1409 END;
1410
1411 IF l_lpn_context = 8 THEN
1412
1413 --bug 4411814
1414 l_lpn.lpn_id := l_transfer_lpn_id;
1415 l_lpn.organization_id := l_org_id;
1416 l_lpn.lpn_context := 5;
1417
1418 wms_container_pvt.Modify_LPN
1419 (
1420 p_api_version => 1.0
1421 , p_validation_level => fnd_api.g_valid_level_none
1422 , x_return_status => l_return_status
1423 , x_msg_count => l_msg_count
1424 , x_msg_data => l_msg_data
1425 , p_lpn => l_lpn
1426 ) ;
1427
1428 l_lpn := NULL;
1429
1430 END IF;
1431
1432 END IF;
1433 ELSE
1434 OPEN c_next_temp_id (l_transfer_lpn_id, p_txn_temp_id);
1435 FETCH c_next_temp_id INTO x_next_temp_id, l_dum_sort;
1436 CLOSE c_next_temp_id;
1437 END IF;
1438
1439 IF (l_debug = 1) THEN
1440 mydebug('Done with unload_bulk_task');
1441 END IF;
1442
1443 --
1444 -- Explicit commit required
1445 --
1446 COMMIT;
1447
1448 EXCEPTION
1449 WHEN OTHERS THEN
1450 ROLLBACK TO unload_bulk_sp;
1451
1452 x_return_status := fnd_api.g_ret_sts_error;
1453
1454 IF (l_debug = 1) THEN
1455 mydebug('Exception unload_bulk_task: ' || sqlerrm);
1456 END IF;
1457 END unload_bulk_task;
1458
1459 -- Start Changes for 14699845 (Flexible Lot Allocation)
1460 PROCEDURE clean_LS_Flexible_unload
1461 ( x_return_status OUT NOCOPY VARCHAR2
1462 , p_txn_temp_id IN NUMBER
1463 , p_allocate_lot_flag IN VARCHAR2
1464 , p_reserved_lot IN VARCHAR2
1465 , p_org_id IN NUMBER
1466 , p_is_bulk_task IN VARCHAR2 DEFAULT 'N'
1467 ) IS
1468
1469 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1470 BEGIN
1471 IF ( l_debug = 1 ) THEN
1472 mydebug('FlexibleLotAlloc: In clean_LS_Flexible_unload');
1473 mydebug('FlexibleLotAlloc: Cleaning up the Lots if Lot Allocation is turned OFF');
1474 mydebug('FlexibleLotAlloc: p_txn_temp_id: '||p_txn_temp_id);
1475 mydebug('FlexibleLotAlloc: p_allocate_lot_flag: '||p_allocate_lot_flag);
1476 mydebug('FlexibleLotAlloc: p_reserved_lot: '||p_reserved_lot);
1477 mydebug('FlexibleLotAlloc: p_org_id: '||p_org_id);
1478 mydebug('FlexibleLotAlloc: p_is_bulk_task: '||p_is_bulk_task);
1479 END IF;
1480
1481 x_return_status := fnd_api.g_ret_sts_success;
1482
1483 IF(p_allocate_lot_flag <> 'Y' AND p_reserved_lot IS NULL) THEN
1484 IF (p_is_bulk_task = 'Y') THEN
1485 DELETE FROM MTL_TRANSACTION_LOTS_TEMP
1486 WHERE transaction_temp_id IN (SELECT transaction_temp_id
1487 FROM mtl_material_transactions_temp
1488 WHERE parent_line_id = p_txn_temp_id);
1489 ELSE
1490 DELETE FROM MTL_TRANSACTION_LOTS_TEMP
1491 WHERE transaction_temp_id = p_txn_temp_id;
1492 END IF;
1493 IF (l_debug = 1) THEN
1494 mydebug('Deleted '||SQL%ROWCOUNT||' rows from MTLT');
1495 END IF;
1496 END IF;
1497
1498 EXCEPTION
1499 WHEN OTHERS THEN
1500 x_return_status := fnd_api.g_ret_sts_error;
1501 END;
1502
1503 -- Not Used Yet
1504 /*
1505 PROCEDURE get_allocation_params
1506 ( x_return_status OUT NOCOPY VARCHAR2
1507 , x_reserved_lot OUT NOCOPY VARCHAR2
1508 , x_allocate_serial_flag IN OUT NOCOPY VARCHAR2
1509 , p_serial_control_code IN NUMBER
1510 , p_allocate_lot_flag IN VARCHAR2
1511 , p_org_id IN NUMBER
1512 , p_temp_id IN NUMBER)
1513 IS
1514 l_reserved_lot VARCHAR2(80);
1515 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1516 BEGIN
1517 x_return_status := fnd_api.g_ret_sts_success;
1518 BEGIN
1519
1520 IF ( l_debug = 1 ) THEN
1521 mydebug('FlexibleLotAlloc: In get_allocation_params: ');
1522 mydebug('FlexibleLotAlloc: p_serial_control_code: '||p_serial_control_code);
1523 mydebug('FlexibleLotAlloc: p_allocate_lot_flag: '||p_allocate_lot_flag);
1524 mydebug('FlexibleLotAlloc: p_org_id: '||p_org_id);
1525 mydebug('FlexibleLotAlloc: p_temp_id: '||p_temp_id);
1526 mydebug('FlexibleLotAlloc: x_allocate_serial_flag:'||x_allocate_serial_flag);
1527 END IF;
1528
1529 SELECT mr.lot_number
1530 INTO l_reserved_lot
1531 FROM mtl_material_transactions_temp mmtt, mtl_reservations mr
1532 WHERE mmtt.transaction_temp_id = p_temp_id
1533 AND mmtt.organization_id = p_org_id
1534 AND mmtt.reservation_id = mr.reservation_id;
1535 IF ( l_debug = 1 ) THEN
1536 mydebug('FlexibleLotAlloc: l_reserved_lot: '||l_reserved_lot);
1537 END IF;
1538 EXCEPTION
1539 WHEN NO_DATA_FOUND THEN
1540 l_reserved_lot := NULL;
1541 IF ( l_debug = 1 ) THEN
1542 mydebug('FlexibleLotAlloc: No reservation found for this mmtt');
1543 END IF;
1544 WHEN OTHERS THEN
1545 l_reserved_lot := NULL;
1546 IF ( l_debug = 1 ) THEN
1547 mydebug('FlexibleLotAlloc: No reservation found for this mmtt. SQLCODE '||SQLCODE||' SQLERRM '||SQLERRM);
1548 END IF;
1549 END;
1550 x_reserved_lot := l_reserved_lot;
1551
1552 IF (p_allocate_lot_flag <> 'Y' AND l_reserved_lot IS NULL AND p_serial_control_code NOT IN (1,6))
1553 THEN
1554 x_allocate_serial_flag:= 'N';
1555 END IF;
1556 EXCEPTION
1557 WHEN OTHERS THEN
1558 x_return_status := fnd_api.g_ret_sts_error;
1559
1560 END get_allocation_params;*/
1561 -- End Changes for 14699845 (Flexible Lot Allocation)
1562 END WMS_UNLOAD_UTILS_PVT;
[Home] [Help]