[Home] [Help]
Skip to content
PACKAGE BODY: APPS.WMS_TASK_UTILS_PVT
Source
1 PACKAGE BODY wms_task_utils_pvt AS
2 /* $Header: WMSTSKUB.pls 120.11.12020000.3 2012/12/22 12:13:30 ssrikaku ship $ */
3 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_TASK_UTILS_PVT';
4
5 PROCEDURE mydebug(msg IN VARCHAR2) IS
6 BEGIN
7 inv_trx_util_pub.trace(msg, 'WMS_TASK_UTILS_PVT', 3);
8 END mydebug;
9
10 FUNCTION can_drop(p_lpn_id IN NUMBER)
11 RETURN VARCHAR2 IS
12 txn_temp_id NUMBER := NULL;
13 txn_type_id NUMBER := NULL;
14 mol_id NUMBER := NULL;
15 ln_status NUMBER := NULL;
16 l_ret VARCHAR2(1) := 'Y';
17
18 CURSOR c_tasks IS
19 SELECT mmtt.transaction_temp_id
20 , mmtt.transaction_type_id
21 , mmtt.move_order_line_id
22 , mol.line_status
23 FROM mtl_material_transactions_temp mmtt
24 , mtl_txn_request_lines mol
25 WHERE mmtt.transfer_lpn_id = p_lpn_id
26 AND mmtt.move_order_line_id = mol.line_id
27 AND mol.line_status = inv_globals.g_to_status_cancel_by_source;
28
29 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
30 BEGIN
31 IF (l_debug = 1) THEN
32 mydebug('In CAN_DROP for LPN = ' || p_lpn_id);
33 END IF;
34
35 OPEN c_tasks;
36 FETCH c_tasks INTO txn_temp_id
37 , txn_type_id
38 , mol_id
39 , ln_status;
40
41 IF c_tasks%FOUND
42 THEN
43 IF (l_debug = 1) THEN
44 mydebug(' Found cancelled task ' || txn_temp_id);
45 END IF;
46
47 IF txn_type_id IN (35, 51)
48 THEN
49 l_ret := 'N';
50 IF (l_debug = 1) THEN
51 mydebug('Cannot Drop a Cancelled WIP Task: ' || txn_temp_id);
52 END IF;
53 ELSE
54 l_ret := 'W';
55 END IF;
56 ELSE
57 l_ret := 'Y';
58 END IF;
59
60 IF c_tasks%ISOPEN
61 THEN
62 CLOSE c_tasks;
63 END IF;
64
65 IF (l_debug = 1) THEN
66 mydebug('Return Status = ' || l_ret);
67 END IF;
68
69 RETURN l_ret;
70
71 EXCEPTION
72 WHEN OTHERS THEN
73 mydebug('Exception occurred: ' || sqlerrm);
74 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
75 END;
76
77
78
79 PROCEDURE unload_task
80 ( x_ret_value OUT NOCOPY NUMBER
81 , x_message OUT NOCOPY VARCHAR2
82 , p_temp_id IN NUMBER
83 ) IS
84 msg_cnt NUMBER;
85 cnt NUMBER := -1;
86 l_temp_id NUMBER := NULL;
87 l_ser_temp_id NUMBER := NULL;
88 l_org_id NUMBER := NULL;
89 l_item_id NUMBER := NULL;
90 l_del_quantity NUMBER := 0;
91 l_quantity NUMBER := 0;
92 mol_id NUMBER := NULL;
93 line_status NUMBER := NULL;
94 v_lot_control_code NUMBER := NULL;
95 v_serial_control_code NUMBER := NULL;
96 v_allocate_serial_flag VARCHAR2(1) := NULL;
97 l_msg_count NUMBER;
98 l_return_status VARCHAR2(1);
99 -- bug 2091680
100 l_transfer_lpn_id NUMBER;
101 l_wms_task_types NUMBER;
102 l_content_lpn_id NUMBER;
103 l_count NUMBER;
104 l_fm_serial_number VARCHAR2(30);
105 l_to_serial_number VARCHAR2(30);
106 l_serial_transaction_temp_id NUMBER;
107 l_lpn WMS_CONTAINER_PUB.LPN;
108 l_lpn_context NUMBER;
109 l_msg_data VARCHAR2(100);
110
111 CURSOR mmtt_to_del(mol_id NUMBER) IS
112 SELECT mmtt.transaction_temp_id
113 , ABS(mmtt.transaction_quantity) --mmtt.primary_quantity
114 FROM mtl_material_transactions_temp mmtt
115 WHERE mmtt.move_order_line_id = mol_id
116 AND NOT EXISTS(
117 SELECT wdt.transaction_temp_id
118 FROM wms_dispatched_tasks wdt
119 WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id
120 AND wdt.transaction_temp_id IS NOT NULL
121 AND wdt.transaction_temp_id <> p_temp_id);
122
123 CURSOR msnt_to_del(p_tmp_id NUMBER) IS
124 SELECT serial_transaction_temp_id
125 FROM mtl_transaction_lots_temp
126 WHERE transaction_temp_id = p_tmp_id;
127
128 CURSOR c_fm_to_serial_number IS
129 SELECT fm_serial_number
130 , to_serial_number
131 FROM mtl_serial_numbers_temp
132 WHERE transaction_temp_id = p_temp_id;
133
134 CURSOR c_fm_to_lot_serial_number IS
135 SELECT fm_serial_number
136 , to_serial_number
137 FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
138 WHERE mtlt.transaction_temp_id = p_temp_id
139 AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
140
141 CURSOR c_lot_allocations IS
142 SELECT serial_transaction_temp_id
143 FROM mtl_transaction_lots_temp
144 WHERE transaction_temp_id = p_temp_id;
145
146 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
147 BEGIN
148 IF (l_debug = 1) THEN
149 mydebug(' in unload_task ');
150 END IF;
151
152 IF (WMS_CONTROL.GET_CURRENT_RELEASE_LEVEL >=
153 INV_RELEASE.GET_J_RELEASE_LEVEL)
154 THEN
155 WMS_UNLOAD_UTILS_PVT.unload_task
156 ( x_ret_value => x_ret_value
157 , x_message => x_message
158 , p_temp_id => p_temp_id
159 );
160
161 IF (l_debug = 1) THEN
162 mydebug('WMS_UNLOAD_UTILS_PVT.unload task returned value ' || x_ret_value);
163 mydebug('Message: ' || x_message);
164 END IF;
165 ELSE
166 x_ret_value := 0;
167
168 SELECT COUNT(transaction_temp_id)
169 INTO cnt
170 FROM wms_dispatched_tasks
171 WHERE transaction_temp_id = p_temp_id;
172
173 IF (cnt IN(0, -1)) THEN
174 x_ret_value := 0;
175 x_message := ' NO TASK TO UNLOAD ';
176 RETURN;
177 ELSIF(cnt > 1) THEN
178 x_ret_value := 0;
179 x_message := ' MULTIPLE TASKS IN WDT FOR ' || p_temp_id;
180 RETURN;
181 END IF;
182
183 IF (l_debug = 1) THEN
184 mydebug(' in unload_task past 1 ');
185 END IF;
186
187 BEGIN
188 SELECT move_order_line_id
189 , organization_id
190 , inventory_item_id
191 , content_lpn_id
192 , transfer_lpn_id
193 , wms_task_type
194 INTO mol_id
195 , l_org_id
196 , l_item_id
197 , l_content_lpn_id
198 , l_transfer_lpn_id
199 , l_wms_task_types
200 FROM mtl_material_transactions_temp
201 WHERE transaction_temp_id = p_temp_id;
202
203 IF (l_debug = 1) THEN
204 mydebug(' mol_id ' || mol_id);
205 mydebug(' org_id ' || l_org_id);
206 mydebug(' item_id ' || l_item_id);
207 END IF;
208 EXCEPTION
209 WHEN NO_DATA_FOUND THEN
210 IF (l_debug = 1) THEN
211 mydebug(' No data found in mtl_material_transactions_temp ');
212 END IF;
213
214 mol_id := -1;
215 END;
216
217 IF (l_debug = 1) THEN
218 mydebug(' mol id :' || mol_id);
219 END IF;
220
221 IF (mol_id IS NOT NULL) THEN
222 BEGIN
223 SELECT line_status
224 INTO line_status
225 FROM mtl_txn_request_lines
226 WHERE line_id = mol_id;
227
228 IF (l_debug = 1) THEN
229 mydebug(' Status ' || line_status);
230 END IF;
231 EXCEPTION
232 WHEN NO_DATA_FOUND THEN
233 IF (l_debug = 1) THEN
234 mydebug('No data found in mtl_txn_request_lines');
235 END IF;
236
237 line_status := -1;
238 END;
239 END IF;
240
241 IF (l_debug = 1) THEN
242 mydebug(' move order line status ' || line_status);
243 END IF;
244
245 IF (line_status = inv_globals.g_to_status_cancel_by_source) THEN
246 IF (l_debug = 1) THEN
247 mydebug(' move order line cancelled ');
248 END IF;
249
250 IF (l_debug = 1) THEN
251 mydebug('deleting allocations ');
252 END IF;
253
254 OPEN mmtt_to_del(mol_id);
255
256 LOOP
257 FETCH mmtt_to_del INTO l_temp_id, l_quantity;
258 EXIT WHEN mmtt_to_del%NOTFOUND;
259
260 IF (l_debug = 1) THEN
261 mydebug('deleting allocations l_temp_id:' || l_temp_id || ' l_quantity:' || l_quantity);
262 END IF;
263
264 inv_mo_cancel_pvt.reduce_rsv_allocation(
265 x_return_status => l_return_status
266 , x_msg_count => l_msg_count
267 , x_msg_data => x_message
268 , p_transaction_temp_id => l_temp_id
269 , p_quantity_to_delete => l_quantity
270 );
271
272 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
273 IF (l_debug = 1) THEN
274 mydebug(' error returned from inv_mo_cancel_pvt.reduce_rsv_allocation');
275 mydebug(x_message);
276 END IF;
277
278 RAISE fnd_api.g_exc_error;
279 ELSE
280 IF (l_debug = 1) THEN
281 mydebug(' Successful from inv_mo_cancel_pvt.reduce_rsv_allocation Call');
282 END IF;
283
284 l_del_quantity := l_del_quantity + l_quantity;
285 END IF;
286 END LOOP;
287
288 IF (l_debug = 1) THEN
289 mydebug(' alloc quantity deleted ' || l_del_quantity);
290 END IF;
291
292 UPDATE mtl_txn_request_lines
293 SET quantity_detailed =(quantity_detailed - l_del_quantity)
294 WHERE line_id = mol_id;
295
296 IF (l_debug = 1) THEN
297 mydebug('updated mol:' || mol_id);
298 END IF;
299
300 DELETE wms_dispatched_tasks
301 WHERE transaction_temp_id = p_temp_id;
302
303 IF (l_debug = 1) THEN
304 mydebug('deleted from wms_dispatched_tasks ');
305 END IF;
306
307 SELECT COUNT(transaction_temp_id)
308 INTO cnt
309 FROM mtl_material_transactions_temp mmtt
310 WHERE mmtt.move_order_line_id = mol_id;
311
312 IF (cnt = 0) THEN
313 IF (l_debug = 1) THEN
314 mydebug('No more allocations in mmtt left for this mo line ' || mol_id);
315 mydebug(' so closing the mo line ' || mol_id);
316 END IF;
317
318 UPDATE mtl_txn_request_lines
319 SET line_status = inv_globals.g_to_status_closed
320 WHERE line_id = mol_id;
321
322 IF (l_debug = 1) THEN
323 mydebug(' updated the mo line status to ' || inv_globals.g_to_status_closed);
324 END IF;
325 ELSE
326 IF (l_debug = 1) THEN
327 mydebug(' allocations in mmtt left for this mo line - count ' || mol_id || ' - ' || cnt);
328 mydebug(' so not closing the mo line ' || mol_id);
329 END IF;
330 END IF;
331 ELSE
332 IF (l_debug = 1) THEN
333 mydebug(' move order line not cancelled ');
334 END IF;
335
336 SELECT msi.lot_control_code
337 , msi.serial_number_control_code
338 INTO v_lot_control_code
339 , v_serial_control_code
340 FROM mtl_system_items msi, mtl_material_transactions_temp mmtt
341 WHERE msi.inventory_item_id = mmtt.inventory_item_id
342 AND msi.organization_id = mmtt.organization_id
343 AND mmtt.transaction_temp_id = p_temp_id;
344
345 SELECT nvl(mp.allocate_serial_flag,'N') /*Bug#4003553.Added NVL function*/
346 INTO v_allocate_serial_flag
347 FROM mtl_parameters mp, mtl_material_transactions_temp mmtt
348 WHERE mp.organization_id = mmtt.organization_id
349 AND mmtt.transaction_temp_id = p_temp_id;
350
351 IF l_wms_task_types IN(wms_globals.g_wms_task_type_stg_move) THEN
352 -- We need to do this for staging move as staging move will
353 -- have no MSNT/MTLT lines
354 v_lot_control_code := 0;
355 v_serial_control_code := 0;
356 END IF;
357
358 IF (l_debug = 1) THEN
359 mydebug(' lot code ' || v_lot_control_code);
360 mydebug(' ser_code ' || v_serial_control_code);
361 mydebug(' alloc ser flag' || v_allocate_serial_flag);
362 END IF;
363
364 IF (v_allocate_serial_flag <> 'Y') THEN
365 IF (l_debug = 1) THEN
366 mydebug(' alloc serial flag is not y ');
367 END IF;
368
369 IF (v_lot_control_code = 1
370 AND v_serial_control_code NOT IN(1, 6)) THEN
371 IF (l_debug = 1) THEN
372 mydebug(' serial controlled only ');
373 END IF;
374
375 IF (l_debug = 1) THEN
376 mydebug(' deleting msnt with temp id ' || p_temp_id);
377 END IF;
378
379 --UPDATE GROUP_MARK_ID for Serial controlled
380
381 OPEN c_fm_to_serial_number;
382
383 LOOP
384 FETCH c_fm_to_serial_number INTO l_fm_serial_number, l_to_serial_number;
385 EXIT WHEN c_fm_to_serial_number%NOTFOUND;
386
387 UPDATE mtl_serial_numbers
388 SET group_mark_id = NULL
389 WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
390 --Bug 2940878 fix added org and item restriction
391 AND current_organization_id = l_org_id
392 AND inventory_item_id = l_item_id;
393 END LOOP;
394
395 CLOSE c_fm_to_serial_number;
396
397 /**Serial Controlled only ****/
398 DELETE mtl_serial_numbers_temp
399 WHERE transaction_temp_id = p_temp_id;
400 ELSIF(v_lot_control_code = 2
401 AND v_serial_control_code NOT IN(1, 6)) THEN
402 /** Both lot and serial controlled **/
403 IF (l_debug = 1) THEN
404 mydebug(' lot and serial controlled ');
405 END IF;
406
407 IF (l_debug = 1) THEN
408 mydebug(' deleting msnt ');
409 END IF;
410
411 OPEN c_lot_allocations;
412
413 LOOP
414 FETCH c_lot_allocations INTO l_serial_transaction_temp_id;
415 EXIT WHEN c_lot_allocations%NOTFOUND;
416 --UPDATE GROUP_MARK_ID for Lot and serial Controlled
417 OPEN c_fm_to_lot_serial_number;
418
419 LOOP
420 FETCH c_fm_to_lot_serial_number INTO l_fm_serial_number, l_to_serial_number;
421 EXIT WHEN c_fm_to_lot_serial_number%NOTFOUND;
422
423 UPDATE mtl_serial_numbers
424 SET group_mark_id = NULL
425 WHERE serial_number BETWEEN l_fm_serial_number AND l_to_serial_number
426 --Bug 2940878 fix added org and item restriction
427 AND current_organization_id = l_org_id
428 AND inventory_item_id = l_item_id;
429 END LOOP;
430
431 CLOSE c_fm_to_lot_serial_number;
432
433 DELETE FROM mtl_serial_numbers_temp
434 WHERE transaction_temp_id = l_serial_transaction_temp_id;
435 END LOOP;
436
437 CLOSE c_lot_allocations;
438
439 DELETE mtl_serial_numbers_temp
440 WHERE transaction_temp_id IN(SELECT mtlt.serial_transaction_temp_id
441 FROM mtl_transaction_lots_temp mtlt
442 WHERE mtlt.transaction_temp_id = p_temp_id);
443
444 IF (l_debug = 1) THEN
445 mydebug(' updating mtlt ');
446 END IF;
447
448 UPDATE mtl_transaction_lots_temp
449 SET serial_transaction_temp_id = NULL
450 WHERE transaction_temp_id = p_temp_id;
451
452 IF (l_debug = 1) THEN
453 mydebug(' update done ');
454 END IF;
455 END IF;
456 END IF;
457
458 IF (l_debug = 1) THEN
459 mydebug('deleting WDT with temp_id ' || p_temp_id);
460 END IF;
461
462 -- added following for bug fix 2769358
463
464 IF l_content_lpn_id IS NOT NULL THEN
465 IF (l_debug = 1) THEN
466 mydebug('Set lpn context to packing for lpn_ID : ' || l_content_lpn_id);
467 END IF;
468
469 --bug 4411814
470 l_lpn.lpn_id := l_content_lpn_id;
471 l_lpn.organization_id := l_org_id;
472 l_lpn.lpn_context := wms_container_pub.lpn_context_inv;
473
474 wms_container_pvt.Modify_LPN
475 (
476 p_api_version => 1.0
477 , p_validation_level => fnd_api.g_valid_level_none
478 , x_return_status => l_return_status
479 , x_msg_count => l_msg_count
480 , x_msg_data => l_msg_data
481 , p_lpn => l_lpn
482 ) ;
483
484 l_lpn := NULL;
485
486
487 END IF;
488
489 --The lpn ids must be set to null for this task
490 UPDATE mtl_material_transactions_temp
491 SET lpn_id = NULL
492 , content_lpn_id = NULL
493 , transfer_lpn_id = NULL
494 WHERE transaction_temp_id = p_temp_id;
495
496 DELETE wms_dispatched_tasks
497 WHERE transaction_temp_id = p_temp_id;
498
499 IF (l_debug = 1) THEN
500 mydebug('deleted WDT with temp_id ' || p_temp_id);
501 END IF;
502
503 IF l_wms_task_types IN(wms_globals.g_wms_task_type_stg_move) THEN
504 DELETE FROM mtl_material_transactions_temp
505 WHERE transaction_temp_id = p_temp_id;
506 END IF;
507 END IF;
508
509 -- Bug 2091680 . Update the LPN context to defined but not used if the
510 -- lpn is unloaded with a context of packaging and update the context to
511 -- inventory if the entire lpn is picked
512 -- this happens only if there are no more allocations for that lpn and
513 -- the last line IS being unloaded
514 IF l_wms_task_types IN ( wms_globals.g_wms_task_type_pick
515 , wms_globals.g_wms_task_type_replenish
516 , wms_globals.g_wms_task_type_moxfer
517 )
518 THEN
519 SELECT COUNT(1)
520 INTO l_count
521 FROM mtl_material_transactions_temp
522 WHERE transfer_lpn_id = l_transfer_lpn_id;
523
524 IF l_count = 0 THEN -- no more rows and the current row is the
525 --last allocation
526 BEGIN
527 SELECT lpn_context INTO l_lpn_context
528 FROM wms_license_plate_numbers
529 WHERE lpn_id = l_transfer_lpn_id;
530 EXCEPTION
531 WHEN no_data_found THEN
532 l_lpn_context := NULL;
533 END;
534
535 IF l_content_lpn_id IS NOT NULL
536 AND l_content_lpn_id = l_transfer_lpn_id THEN
537
538
539 IF l_lpn_context <> 1 AND l_lpn_context IS NOT NULL THEN
540
541 --bug 4411814
542 l_lpn.lpn_id := l_transfer_lpn_id;
543 l_lpn.organization_id := l_org_id;
544 l_lpn.lpn_context := 1;
545
546 wms_container_pvt.Modify_LPN
547 (
548 p_api_version => 1.0
549 , p_validation_level => fnd_api.g_valid_level_none
550 , x_return_status => l_return_status
551 , x_msg_count => l_msg_count
552 , x_msg_data => l_msg_data
553 , p_lpn => l_lpn
554 ) ;
555
556 l_lpn := NULL;
557 END IF;
558
559 ELSE
560
561 IF l_lpn_context = 8 THEN
562
563 --bug 4411814
564 l_lpn.lpn_id := l_transfer_lpn_id;
565 l_lpn.organization_id := l_org_id;
566 l_lpn.lpn_context := 5;
567
568 wms_container_pvt.Modify_LPN
569 (
570 p_api_version => 1.0
571 , p_validation_level => fnd_api.g_valid_level_none
572 , x_return_status => l_return_status
573 , x_msg_count => l_msg_count
574 , x_msg_data => l_msg_data
575 , p_lpn => l_lpn
576 ) ;
577
578 l_lpn := NULL;
579
580 END IF;
581
582 END IF;
583
584 END IF;
585
586 ELSIF l_wms_task_types = wms_globals.g_wms_task_type_stg_move THEN
587
588 IF (l_debug = 1) THEN
589 mydebug('Calling wms_container_pvt.Modify_LPN_Wrapper for staging move. p_lpn_id = '||l_content_lpn_id);
590 mydebug('p_lpn_context = '|| wms_container_pub.LPN_CONTEXT_PICKED );
591 END IF;
592
593 wms_container_pub.Modify_LPN_Wrapper
594 ( p_api_version => 1.0
595 ,x_return_status => l_return_status
596 ,x_msg_count => l_msg_count
597 ,x_msg_data => x_message
598 ,p_lpn_id => l_content_lpn_id
599 ,p_lpn_context => wms_container_pub.lpn_context_picked
600 );
601
602 IF (l_debug = 1) THEN
603 mydebug('wms_container_pvt.Modify_LPN_Wrapper x_return_status = '||l_return_status);
604 END IF;
605
606 END IF;
607
608 x_ret_value := 1;
609
610 IF (l_debug = 1) THEN
611 mydebug('done unload_task x_ret ' || x_ret_value);
612 END IF;
613
614 -- Doing an explicit commit
615 -- HERE
616
617 COMMIT;
618 END IF;
619
620 EXCEPTION
621 WHEN OTHERS THEN
622 x_ret_value := 0;
623
624 IF (l_debug = 1) THEN
625 mydebug(' In exception unload_task x_ret' || x_ret_value);
626 END IF;
627
628 fnd_msg_pub.count_and_get(p_count => msg_cnt, p_data => x_message);
629 END unload_task;
630
631 PROCEDURE is_task_processed(x_processed OUT NOCOPY VARCHAR2, p_header_id IN NUMBER) IS
632 l_processed VARCHAR2(1) := 'Y';
633 l_err_status NUMBER := NULL;
634 l_process_flag VARCHAR2(1) := NULL;
635 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
636 BEGIN
637 -- If there are more than one row for this putaway tasks' transaction
638 -- header id , returning an error status of M to discontinue work
639 -- flow processing
640
641 IF (l_debug = 1) THEN
642 mydebug('in Is_task_processed with header is :' || p_header_id);
643 END IF;
644
645 l_processed := NULL;
646
647 BEGIN
648 SELECT 'E'
649 INTO l_processed
650 FROM DUAL
651 WHERE EXISTS(SELECT 1
652 FROM mtl_material_transactions_temp
653 WHERE transaction_header_id = p_header_id
654 AND process_flag = 'E');
655
656 IF (l_debug = 1) THEN
657 mydebug('transaction status ' || l_err_status);
658 END IF;
659 EXCEPTION
660 WHEN NO_DATA_FOUND THEN
661 NULL;
662 END;
663
664 IF l_processed = 'E' THEN
665 IF (l_debug = 1) THEN
666 mydebug('transaction has errored out so ret E');
667 END IF;
668 ELSE
669 IF (l_debug = 1) THEN
670 mydebug('Before the select:');
671 END IF;
672
673 SELECT 'Y'
674 INTO l_processed
675 FROM DUAL
676 WHERE EXISTS(SELECT transaction_set_id
677 FROM mtl_material_transactions
678 WHERE transaction_set_id = p_header_id);
679
680 IF (l_debug = 1) THEN
681 mydebug('After the select: l_processed ' || l_processed);
682 END IF;
683 END IF;
684
685 x_processed := l_processed;
686 EXCEPTION
687 WHEN NO_DATA_FOUND THEN
688 IF (l_debug = 1) THEN
689 mydebug('in no data found');
690 END IF;
691
692 x_processed := 'N';
693 WHEN TOO_MANY_ROWS THEN
694 IF (l_debug = 1) THEN
695 mydebug('in too many rows');
696 END IF;
697
698 x_processed := 'M';
699 WHEN OTHERS THEN
700 IF (l_debug = 1) THEN
701 mydebug('IN OTHERS');
702 END IF;
703
704 x_processed := 'O';
705 END is_task_processed;
706
707
708 FUNCTION check_qty_avail(
709 mmtt_row IN mmtt_type
710 , lot_row IN mtlt_type
711 , ser_row IN msnt_type
712 , p_is_revision_control IN VARCHAR2
713 , p_is_lot_control IN VARCHAR2
714 , p_is_serial_control IN VARCHAR2
715 , p_allocate_serial_flag IN VARCHAR2
716 )
717 RETURN BOOLEAN IS
718 l_ret BOOLEAN := TRUE;
719 l_msg_count VARCHAR2(100);
720 l_msg_data VARCHAR2(1000);
721 l_is_revision_control BOOLEAN := FALSE;
722 l_is_lot_control BOOLEAN := FALSE;
723 l_is_serial_control BOOLEAN := FALSE;
724 l_tree_mode NUMBER;
725 l_api_version_number CONSTANT NUMBER := 1.0;
726 l_api_name CONSTANT VARCHAR2(30) := 'check_qty_avail';
727 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
728 l_tree_id INTEGER;
729 l_rqoh NUMBER;
730 l_qr NUMBER;
731 l_qs NUMBER;
732 l_atr NUMBER;
733 l_qoh NUMBER;
734 l_att NUMBER;
735 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
736 l_lot_number VARCHAR2(80);
737 l_qty NUMBER := NULL;
738 l_already_used VARCHAR2(1) := 'N';
739 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
740 BEGIN
741 IF (l_debug = 1) THEN
742 mydebug('Enter check_qty_avail');
743 END IF;
744
745 inv_quantity_tree_pub.clear_quantity_cache;
746
747 IF (l_debug = 1) THEN
748 mydebug('rev control' || p_is_revision_control);
749 END IF;
750
751 IF p_is_revision_control = 'Y' THEN
752 l_is_revision_control := TRUE;
753 END IF;
754
755 IF (l_debug = 1) THEN
756 mydebug('lot control' || p_is_lot_control);
757 END IF;
758
759 IF p_is_lot_control = 'Y' THEN
760 l_is_lot_control := TRUE;
761 l_lot_number := lot_row.lot_number;
762 END IF;
763
764 IF (l_debug = 1) THEN
765 mydebug('ser control' || p_is_serial_control);
766 END IF;
767
768 IF p_is_serial_control = 'Y' THEN
769 l_is_serial_control := TRUE;
770 END IF;
771
772 l_tree_mode := inv_quantity_tree_pub.g_transaction_mode;
773
774 IF (l_debug = 1) THEN
775 mydebug('querying quantity tree');
776 END IF;
777
778 inv_quantity_tree_pub.query_quantities(
779 p_api_version_number => 1.0
780 , p_init_msg_lst => fnd_api.g_false
781 , x_return_status => l_return_status
782 , x_msg_count => l_msg_count
783 , x_msg_data => l_msg_data
784 , p_organization_id => mmtt_row.organization_id
785 , p_inventory_item_id => mmtt_row.inventory_item_id
786 , p_tree_mode => l_tree_mode
787 , p_is_revision_control => l_is_revision_control
788 , p_is_lot_control => l_is_lot_control
789 , p_is_serial_control => l_is_serial_control
790 , p_revision => mmtt_row.revision
791 , p_lot_number => l_lot_number
792 , p_lot_expiration_date => NULL --for bug# 2219136
793 , p_subinventory_code => mmtt_row.subinventory_code
794 , p_locator_id => mmtt_row.locator_id
795 , p_cost_group_id => mmtt_row.cost_group_id
796 , p_lpn_id => mmtt_row.allocated_lpn_id -- bug 4230494
797 , x_qoh => l_qoh
798 , x_rqoh => l_rqoh
799 , x_qr => l_qr
800 , x_qs => l_qs
801 , x_att => l_att
802 , x_atr => l_atr
803 );
804
805 ---WHY DOESNT THE QTY TREE API HAVE A PARAM FOR SERIAL
806 IF (l_debug = 1) THEN
807 mydebug('qty tree ret status' || l_return_status);
808 mydebug('qty tree ret msg' || l_msg_data);
809 END IF;
810
811 IF (l_debug = 1) THEN
812 mydebug('qty tree ret x_qoh' || l_qoh);
813 mydebug('qty tree ret x_rqoh' || l_rqoh);
814 mydebug('qty tree ret x_qr' || l_qr);
815 mydebug('qty tree ret x_qs' || l_qs);
816 mydebug('qty tree ret x_att' || l_att);
817 mydebug('qty tree ret x_atr' || l_atr);
818 END IF;
819
820 IF (p_is_lot_control = 'Y') THEN
821 l_qty := lot_row.primary_quantity;
822 ELSE
823 l_qty := mmtt_row.primary_quantity;
824 END IF;
825
826 IF (l_debug = 1) THEN
827 mydebug('qty we are checking for' || l_qty);
828 END IF;
829
830 IF (l_att < l_qty) THEN
831 IF (l_debug = 1) THEN
832 mydebug('check_qty_avail ret FALSE');
833 END IF;
834
835 l_ret := FALSE;
836 ELSE
837 /** 2706001 fix removed group mark check from here **/
838 IF (l_debug = 1) THEN
839 mydebug('quantities match');
840 END IF;
841 l_ret := TRUE;
842 END IF;
843
844 RETURN l_ret;
845 EXCEPTION
846 WHEN fnd_api.g_exc_unexpected_error THEN
847 IF (l_debug = 1) THEN
848 mydebug('unexpected error in check_qty_avail');
849 END IF;
850
851 l_ret := FALSE;
852 RAISE fnd_api.g_exc_unexpected_error;
853 RETURN l_ret;
854 WHEN OTHERS THEN
855 IF (l_debug = 1) THEN
856 mydebug('Exception in check_qty_avail');
857 END IF;
858
859 l_ret := FALSE;
860 RETURN l_ret;
861 END check_qty_avail;
862
863 PROCEDURE get_temp_tables(p_set_id IN NUMBER, x_mmtt OUT NOCOPY mmtt_tb, x_mtlt OUT NOCOPY mtlt_tb, x_msnt OUT NOCOPY msnt_tb) IS
864 v_lot_control_code NUMBER := -1;
865 v_serial_control_code NUMBER := -1;
866 cnt NUMBER := 1;
867 v_allocate_serial_flag VARCHAR2(1) := 'X';
868
869 CURSOR mmt(p_set_id NUMBER) IS
870 SELECT *
871 FROM mtl_material_transactions
872 WHERE transaction_set_id = p_set_id;
873
874 CURSOR mtln(p_set_id NUMBER) IS
875 SELECT *
876 FROM mtl_transaction_lot_numbers
877 WHERE transaction_id IN(SELECT transaction_id
878 FROM mtl_material_transactions
879 WHERE transaction_set_id = p_set_id);
880
881 CURSOR mut1(p_set_id NUMBER) IS
882 SELECT *
883 FROM mtl_unit_transactions
884 WHERE transaction_id IN(SELECT transaction_id
885 FROM mtl_material_transactions
886 WHERE transaction_set_id = p_set_id);
887
888 CURSOR mut2(p_set_id NUMBER) IS
889 SELECT *
890 FROM mtl_unit_transactions
891 WHERE transaction_id IN(SELECT serial_transaction_id
892 FROM mtl_transaction_lot_numbers
893 WHERE transaction_id IN(SELECT transaction_id
894 FROM mtl_material_transactions
895 WHERE transaction_set_id = p_set_id));
896
897 mmtt_table mmtt_tb;
898 mmtt_row mmtt_type;
899 mtlt_row mtlt_type;
900 mtlt_table mtlt_tb;
901 msnt_row msnt_type;
902 msnt_table msnt_tb;
903 mmt_row mmt_type;
904 mtln_row mtln_type;
905 mut_row mut_type;
906 l_item_id NUMBER;
907 l_org_id NUMBER;
908 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
909
910 l_lpn_control_flag NUMBER; -- bug 4230494
911 l_item_uom_code VARCHAR2(3); --Bug#5010991
912 l_lpn_ctx NUMBER ; --Bug#5984021
913 BEGIN
914 IF (l_debug = 1) THEN
915 mydebug(' entering get_temp_tables');
916 END IF;
917
918 cnt := 1;
919 OPEN mmt(p_set_id);
920
921 LOOP
922 IF (l_debug = 1) THEN
923 mydebug(' inside mmt loop ');
924 END IF;
925
926 FETCH mmt INTO mmt_row;
927 EXIT WHEN mmt%NOTFOUND;
928
929 IF (l_debug = 1) THEN
930 mydebug(' transaction id ' || mmt_row.transaction_id);
931 END IF;
932
933 l_item_id := mmt_row.inventory_item_id;
934 l_org_id := mmt_row.organization_id;
935
936 --Bug#5010991. Get the PRIMARY_UOM_CODE for the item.
937 SELECT msi.primary_uom_code INTO l_item_uom_code
938 FROM mtl_system_items msi
939 WHERE msi.inventory_item_id=l_item_id
940 AND msi.organization_id=l_org_id;
941
942
943 IF (l_debug = 1) THEN
944 mydebug(' item id ' || l_item_id ||' , primary uom:'||l_item_uom_code);
945 END IF;
946
947 mmtt_row.transaction_temp_id := mmt_row.transaction_id;
948 mmtt_row.last_update_date := mmt_row.last_update_date;
949 mmtt_row.last_updated_by := mmt_row.last_updated_by;
950 mmtt_row.creation_date := mmt_row.creation_date;
951 mmtt_row.created_by := mmt_row.created_by;
952 mmtt_row.last_update_login := mmt_row.last_update_login;
953 mmtt_row.request_id := mmt_row.request_id;
954 mmtt_row.program_application_id := mmt_row.program_application_id;
955 mmtt_row.program_id := mmt_row.program_id;
956 mmtt_row.program_update_date := mmt_row.program_update_date;
957 mmtt_row.inventory_item_id := mmt_row.inventory_item_id;
958
959 mmtt_row.item_primary_uom_code := l_item_uom_code; --Bug#5010991.Add PRIMARY_UOM_CODE to MMTT.
960 mmtt_row.revision := mmt_row.revision;
961 mmtt_row.organization_id := mmt_row.organization_id;
962 mmtt_row.subinventory_code := mmt_row.subinventory_code;
963 mmtt_row.locator_id := mmt_row.locator_id;
964 mmtt_row.transaction_type_id := mmt_row.transaction_type_id;
965 mmtt_row.transaction_action_id := mmt_row.transaction_action_id;
966 mmtt_row.transaction_source_type_id := mmt_row.transaction_source_type_id;
967 mmtt_row.transaction_source_id := mmt_row.transaction_source_id;
968 mmtt_row.transaction_source_name := mmt_row.transaction_source_name;
969 mmtt_row.transaction_quantity := mmt_row.transaction_quantity;
970 mmtt_row.transaction_uom := mmt_row.transaction_uom;
971 mmtt_row.primary_quantity := mmt_row.primary_quantity;
972 mmtt_row.transaction_date := mmt_row.transaction_date;
973 --VARIANCE_AMOUNT , ;
974 mmtt_row.acct_period_id := mmt_row.acct_period_id;
975 mmtt_row.transaction_reference := mmt_row.transaction_reference;
976 mmtt_row.reason_id := mmt_row.reason_id;
977 mmtt_row.distribution_account_id := mmt_row.distribution_account_id;
978 mmtt_row.encumbrance_account := mmt_row.encumbrance_account;
979 mmtt_row.encumbrance_amount := mmt_row.encumbrance_amount;
980 --COST_UPDATE_ID ;
981 --COSTED_FLAG ;
982 --INVOICED_FLAG ;
983 --ACTUAL_COST ;
984 -- mmtt_row.transaction_cost := mmt_row.transaction_cost;bug#4011886 transaction cost copying
985 --would resultin orphan reocrds in MTL_CST_TXN_COST_DETAILS
986 --PRIOR_COST ;
987 --NEW_COST ;
988 mmtt_row.currency_code := mmt_row.currency_code;
989 mmtt_row.currency_conversion_rate := mmt_row.currency_conversion_rate;
990 mmtt_row.currency_conversion_type := mmt_row.currency_conversion_type;
991 mmtt_row.currency_conversion_date := mmt_row.currency_conversion_date;
992 mmtt_row.ussgl_transaction_code := mmt_row.ussgl_transaction_code;
993 --QUANTITY_ADJUSTED ;
994 mmtt_row.employee_code := mmt_row.employee_code;
995 mmtt_row.department_id := mmt_row.department_id;
996 mmtt_row.operation_seq_num := mmt_row.operation_seq_num;
997 --MASTER_SCHEDULE_UPDATE_CODE ;
998 mmtt_row.receiving_document := mmt_row.receiving_document;
999 mmtt_row.picking_line_id := mmt_row.picking_line_id;
1000 mmtt_row.trx_source_line_id := mmt_row.trx_source_line_id;
1001 mmtt_row.trx_source_delivery_id := mmt_row.trx_source_delivery_id;
1002 mmtt_row.repetitive_line_id := mmt_row.repetitive_line_id;
1003 mmtt_row.physical_adjustment_id := mmt_row.physical_adjustment_id;
1004 mmtt_row.cycle_count_id := mmt_row.cycle_count_id;
1005 mmtt_row.rma_line_id := mmt_row.rma_line_id;
1006 --TRANSFER_TRANSACTION_ID ;
1007 --TRANSACTION_SET_ID ;
1008 mmtt_row.rcv_transaction_id := mmt_row.rcv_transaction_id;
1009 mmtt_row.move_transaction_id := mmt_row.move_transaction_id;
1010 mmtt_row.completion_transaction_id := mmt_row.completion_transaction_id;
1011 mmtt_row.source_code := mmt_row.source_code;
1012 mmtt_row.source_line_id := mmt_row.source_line_id;
1013 mmtt_row.vendor_lot_number := mmt_row.vendor_lot_number;
1014 --Bug 5218617
1015 --mmtt_row.transfer_organization := mmt_row.transfer_organization_id;
1016 mmtt_row.transfer_subinventory := mmt_row.transfer_subinventory;
1017 mmtt_row.transfer_to_location := mmt_row.transfer_locator_id;
1018 mmtt_row.shipment_number := mmt_row.shipment_number;
1019 mmtt_row.transfer_cost := mmt_row.transfer_cost;
1020 --TRANSPORTATION_DIST_ACCOUNT ;
1021 mmtt_row.transportation_cost := mmt_row.transportation_cost;
1022 --TRANSFER_COST_DIST_ACCOUNT ;
1023 mmtt_row.waybill_airbill := mmt_row.waybill_airbill;
1024 mmtt_row.freight_code := mmt_row.freight_code;
1025 --NUMBER_OF_CONTAINERS ;
1026 mmtt_row.value_change := mmt_row.value_change;
1027 mmtt_row.percentage_change := mmt_row.percentage_change;
1028 mmtt_row.attribute_category := mmt_row.attribute_category;
1029 mmtt_row.attribute1 := mmt_row.attribute1;
1030 mmtt_row.attribute2 := mmt_row.attribute2;
1031 mmtt_row.attribute3 := mmt_row.attribute3;
1032 mmtt_row.attribute4 := mmt_row.attribute4;
1033 mmtt_row.attribute5 := mmt_row.attribute5;
1034 mmtt_row.attribute6 := mmt_row.attribute6;
1035 mmtt_row.attribute7 := mmt_row.attribute7;
1036 mmtt_row.attribute8 := mmt_row.attribute8;
1037 mmtt_row.attribute9 := mmt_row.attribute9;
1038 mmtt_row.attribute10 := mmt_row.attribute10;
1039 mmtt_row.attribute11 := mmt_row.attribute11;
1040 mmtt_row.attribute12 := mmt_row.attribute12;
1041 mmtt_row.attribute13 := mmt_row.attribute13;
1042 mmtt_row.attribute14 := mmt_row.attribute14;
1043 mmtt_row.attribute15 := mmt_row.attribute15;
1044 mmtt_row.movement_id := mmt_row.movement_id;
1045 --TRANSACTION_GROUP_ID ;
1046 mmtt_row.task_id := mmt_row.task_id;
1047 mmtt_row.to_task_id := mmt_row.to_task_id;
1048 mmtt_row.project_id := mmt_row.project_id;
1049 mmtt_row.to_project_id := mmt_row.to_project_id;
1050 mmtt_row.source_project_id := mmt_row.source_project_id;
1051 mmtt_row.pa_expenditure_org_id := mmt_row.pa_expenditure_org_id;
1052 mmtt_row.source_task_id := mmt_row.source_task_id;
1053 mmtt_row.expenditure_type := mmt_row.expenditure_type;
1054 mmtt_row.ERROR_CODE := mmt_row.ERROR_CODE;
1055 mmtt_row.error_explanation := mmt_row.error_explanation;
1056 --PRIOR_COSTED_QUANTITY ;
1057 mmtt_row.final_completion_flag := mmt_row.final_completion_flag;
1058 --PM_COST_COLLECTED ;
1059 --PM_COST_COLLECTOR_GROUP_ID ;
1060 --SHIPMENT_COSTED ;
1061 mmtt_row.transfer_percentage := mmt_row.transfer_percentage;
1062 mmtt_row.material_account := mmt_row.material_account;
1063 mmtt_row.material_overhead_account := mmt_row.material_overhead_account;
1064 mmtt_row.resource_account := mmt_row.resource_account;
1065 mmtt_row.outside_processing_account := mmt_row.outside_processing_account;
1066 mmtt_row.overhead_account := mmt_row.overhead_account;
1067 --BUG 2698630 fix no need to put cost groups on the new task
1068 --They will be determined by the cost group api while processing the
1069 --transaction
1070 mmtt_row.cost_group_id := NULL;--mmt_row.cost_group_id;
1071 mmtt_row.transfer_cost_group_id := NULL;--mmt_row.transfer_cost_group_id;
1072 mmtt_row.flow_schedule := mmt_row.flow_schedule;
1073 --TRANSFER_PRIOR_COSTED_QUANTITY ;
1074 --SHORTAGE_PROCESS_CODE ;
1075 mmtt_row.qa_collection_id := mmt_row.qa_collection_id;
1076 mmtt_row.overcompletion_transaction_qty := mmt_row.overcompletion_transaction_qty;
1077 mmtt_row.overcompletion_primary_qty := mmt_row.overcompletion_primary_qty;
1078 mmtt_row.overcompletion_transaction_id := mmt_row.overcompletion_transaction_id;
1079 --MVT_STAT_STATUS ;
1080 mmtt_row.common_bom_seq_id := mmt_row.common_bom_seq_id;
1081 mmtt_row.common_routing_seq_id := mmt_row.common_routing_seq_id;
1082 mmtt_row.org_cost_group_id := mmt_row.org_cost_group_id;
1083 mmtt_row.cost_type_id := mmt_row.cost_type_id;
1084 --PERIODIC_PRIMARY_QUANTITY ;
1085 mmtt_row.move_order_line_id := mmt_row.move_order_line_id;
1086 mmtt_row.task_group_id := mmt_row.task_group_id;
1087 mmtt_row.pick_slip_number := mmt_row.pick_slip_number;
1088 --mmtt_row.LPN_ID := mmt_row.LPN_ID ;
1089 --mmtt_row.TRANSFER_LPN_ID := mmt_row.TRANSFER_LPN_ID ;
1090
1091 mmtt_row.lpn_id := NULL;
1092 mmtt_row.transfer_lpn_id := NULL;
1093 mmtt_row.pick_strategy_id := mmt_row.pick_strategy_id;
1094 mmtt_row.pick_rule_id := mmt_row.pick_rule_id;
1095 mmtt_row.put_away_strategy_id := mmt_row.put_away_strategy_id;
1096 mmtt_row.put_away_rule_id := mmt_row.put_away_rule_id;
1097 --mmtt_row.CONTENT_LPN_ID := mmt_row.CONTENT_LPN_ID;
1098 mmtt_row.content_lpn_id := NULL;
1099 mmtt_row.pick_slip_date := mmt_row.pick_slip_date;
1100 --COST_CATEGORY_ID ;
1101
1102 --For the BUG No. 2172959, Since reservation_id is of no use in mmt
1103 --mmtt_row.RESERVATION_ID := mmt_row.RESERVATION_ID;
1104 mmtt_row.reservation_id := NULL;
1105 mmtt_row.organization_type := mmt_row.organization_type;
1106 mmtt_row.transfer_organization_type := mmt_row.transfer_organization_type;
1107
1108 /* -- Commenting these for Bug#9459027
1109 mmtt_row.owning_organization_id := mmt_row.owning_organization_id;
1110 mmtt_row.owning_tp_type := mmt_row.owning_tp_type;
1111 mmtt_row.xfr_owning_organization_id := mmt_row.xfr_owning_organization_id;
1112 mmtt_row.transfer_owning_tp_type := mmt_row.transfer_owning_tp_type;
1113 mmtt_row.planning_organization_id := mmt_row.planning_organization_id;
1114 mmtt_row.planning_tp_type := mmt_row.planning_tp_type;
1115 mmtt_row.xfr_planning_organization_id := mmt_row.xfr_planning_organization_id;
1116 mmtt_row.transfer_planning_tp_type := mmt_row.transfer_planning_tp_type;*/
1117
1118 mmtt_row.secondary_uom_code := mmt_row.secondary_uom_code;
1119 mmtt_row.secondary_transaction_quantity := mmt_row.secondary_transaction_quantity;
1120
1121 IF ( mmtt_row.primary_quantity < 0 ) THEN --Bug#5984021
1122 -- bug 4230494
1123 SELECT lpn_controlled_flag
1124 INTO l_lpn_control_flag
1125 FROM mtl_secondary_inventories
1126 WHERE organization_id = mmt_row.organization_id
1127 AND secondary_inventory_name = Nvl(mmt_row.transfer_subinventory, mmt_row.subinventory_code);
1128 ELSE
1129 SELECT lpn_controlled_flag
1130 INTO l_lpn_control_flag
1131 FROM mtl_secondary_inventories
1132 WHERE organization_id = mmt_row.organization_id
1133 AND secondary_inventory_name = Nvl(mmt_row.subinventory_code, mmt_row.transfer_subinventory);
1134 END IF;
1135
1136
1137 IF(l_lpn_control_flag = 1)THEN
1138 IF (l_debug = 1) THEN
1139 mydebug('Populate LPN ID '|| Nvl(Nvl(mmt_row.content_lpn_id, mmt_row.transfer_lpn_id), mmt_row.lpn_id)||' into mmtt.allocated_lpn_id. ');
1140 END IF;
1141
1142 mmtt_row.allocated_lpn_id := Nvl(mmt_row.content_lpn_id, mmt_row.transfer_lpn_id);
1143
1144 --Bug#5984021.If LPN is empty, no need of stamping it on MMTT.
1145 IF ( NVL(mmtt_row.allocated_lpn_id , 0 ) > 0 ) THEN
1146 SELECT wlpn.lpn_context INTO l_lpn_ctx
1147 FROM WMS_LICENSE_PLATE_NUMBERS wlpn
1148 WHERE wlpn.lpn_id = mmtt_row.allocated_lpn_id ;
1149
1150 IF (l_debug = 1) THEN
1151 mydebug('LPN id : '||mmtt_row.allocated_lpn_id ||', context:' || l_lpn_ctx );
1152 END IF;
1153
1154 IF ( l_lpn_ctx = WMS_Container_PUB.LPN_CONTEXT_PREGENERATED ) THEN
1155 mmtt_row.allocated_lpn_id := NULL ;
1156 IF (l_debug = 1) THEN
1157 mydebug('LPN has context 5, so null it out in MMTTT' );
1158 END IF;
1159 END IF;
1160 END IF;
1161 --Bug#5984021.End of fix.
1162 END IF;
1163 -- bug 4230494
1164
1165 mmtt_table(cnt) := mmtt_row;
1166 cnt := cnt + 1;
1167 mmtt_row := NULL;
1168 END LOOP;
1169
1170 IF (l_debug = 1) THEN
1171 mydebug('after creating mmtt_table');
1172 mydebug(' Item id ' || l_item_id);
1173 mydebug(' org id ' || l_org_id);
1174 END IF;
1175
1176 SELECT lot_control_code
1177 , serial_number_control_code
1178 INTO v_lot_control_code
1179 , v_serial_control_code
1180 FROM mtl_system_items
1181 WHERE inventory_item_id = l_item_id
1182 AND organization_id = l_org_id;
1183
1184 IF (l_debug = 1) THEN
1185 mydebug(' lot code ' || v_lot_control_code);
1186 mydebug(' ser code ' || v_serial_control_code);
1187 END IF;
1188
1189 SELECT allocate_serial_flag
1190 INTO v_allocate_serial_flag
1191 FROM mtl_parameters
1192 WHERE organization_id = l_org_id;
1193
1194 /*****LOT controlled only **********/
1195 cnt := 1;
1196
1197 IF (v_lot_control_code = 2) THEN
1198 OPEN mtln(p_set_id);
1199
1200 LOOP
1201 FETCH mtln INTO mtln_row;
1202 EXIT WHEN mtln%NOTFOUND;
1203 mtlt_row.transaction_temp_id := mtln_row.transaction_id;
1204 mtlt_row.last_update_date := mtln_row.last_update_date;
1205 mtlt_row.last_updated_by := mtln_row.last_updated_by;
1206 mtlt_row.creation_date := mtln_row.creation_date;
1207 mtlt_row.created_by := mtln_row.created_by;
1208 mtlt_row.last_update_login := mtln_row.last_update_login;
1209 --mtlt_row.INVENTORY_ITEM_ID := l_item_id;
1210 --mtlt_row.ORGANIZATION_ID := l_org_id;
1211 --mtlt_row.TRANSACTION_DATE := l_txn_date;
1212 --mtlt_row.transaction_source_id := l_txn_source_id;
1213 --mtlt_row.transaction_source_type_id := l_txn_source_type_id;
1214 --mtlt_row.TRANSACTION_SOURCE_NAME := l_txn_source_name;
1215
1216 mtlt_row.transaction_quantity := mtln_row.transaction_quantity;
1217 mtlt_row.primary_quantity := mtln_row.primary_quantity;
1218 mtlt_row.lot_number := mtln_row.lot_number;
1219 mtlt_row.serial_transaction_temp_id := mtln_row.serial_transaction_id;
1220 mtlt_row.description := mtln_row.description;
1221 mtlt_row.vendor_name := mtln_row.vendor_name;
1222 mtlt_row.supplier_lot_number := mtln_row.supplier_lot_number;
1223 mtlt_row.origination_date := mtln_row.origination_date;
1224 mtlt_row.date_code := mtln_row.date_code;
1225 mtlt_row.grade_code := mtln_row.grade_code;
1226 mtlt_row.change_date := mtln_row.change_date;
1227 mtlt_row.maturity_date := mtln_row.maturity_date;
1228 mtlt_row.status_id := mtln_row.status_id;
1229 mtlt_row.retest_date := mtln_row.retest_date;
1230 mtlt_row.age := mtln_row.age;
1231 mtlt_row.item_size := mtln_row.item_size;
1232 mtlt_row.color := mtln_row.color;
1233 mtlt_row.volume := mtln_row.volume;
1234 mtlt_row.volume_uom := mtln_row.volume_uom;
1235 mtlt_row.place_of_origin := mtln_row.place_of_origin;
1236 mtlt_row.best_by_date := mtln_row.best_by_date;
1237 mtlt_row.LENGTH := mtln_row.LENGTH;
1238 mtlt_row.length_uom := mtln_row.length_uom;
1239 mtlt_row.width := mtln_row.width;
1240 mtlt_row.width_uom := mtln_row.width_uom;
1241 mtlt_row.recycled_content := mtln_row.recycled_content;
1242 mtlt_row.thickness := mtln_row.thickness;
1243 mtlt_row.thickness_uom := mtln_row.thickness_uom;
1244 mtlt_row.curl_wrinkle_fold := mtln_row.curl_wrinkle_fold;
1245 mtlt_row.lot_attribute_category := mtln_row.lot_attribute_category;
1246 mtlt_row.c_attribute1 := mtln_row.c_attribute1;
1247 mtlt_row.c_attribute2 := mtln_row.c_attribute2;
1248 mtlt_row.c_attribute3 := mtln_row.c_attribute3;
1249 mtlt_row.c_attribute4 := mtln_row.c_attribute4;
1250 mtlt_row.c_attribute5 := mtln_row.c_attribute5;
1251 mtlt_row.c_attribute6 := mtln_row.c_attribute6;
1252 mtlt_row.c_attribute7 := mtln_row.c_attribute7;
1253 mtlt_row.c_attribute8 := mtln_row.c_attribute8;
1254 mtlt_row.c_attribute9 := mtln_row.c_attribute9;
1255 mtlt_row.c_attribute10 := mtln_row.c_attribute10;
1256 mtlt_row.c_attribute11 := mtln_row.c_attribute11;
1257 mtlt_row.c_attribute12 := mtln_row.c_attribute12;
1258 mtlt_row.c_attribute13 := mtln_row.c_attribute13;
1259 mtlt_row.c_attribute14 := mtln_row.c_attribute14;
1260 mtlt_row.c_attribute15 := mtln_row.c_attribute15;
1261 mtlt_row.c_attribute16 := mtln_row.c_attribute16;
1262 mtlt_row.c_attribute17 := mtln_row.c_attribute17;
1263 mtlt_row.c_attribute18 := mtln_row.c_attribute18;
1264 mtlt_row.c_attribute19 := mtln_row.c_attribute19;
1265 mtlt_row.c_attribute20 := mtln_row.c_attribute20;
1266 mtlt_row.d_attribute1 := mtln_row.d_attribute1;
1267 mtlt_row.d_attribute2 := mtln_row.d_attribute2;
1268 mtlt_row.d_attribute3 := mtln_row.d_attribute3;
1269 mtlt_row.d_attribute4 := mtln_row.d_attribute4;
1270 mtlt_row.d_attribute5 := mtln_row.d_attribute5;
1271 mtlt_row.d_attribute6 := mtln_row.d_attribute6;
1272 mtlt_row.d_attribute7 := mtln_row.d_attribute7;
1273 mtlt_row.d_attribute8 := mtln_row.d_attribute8;
1274 mtlt_row.d_attribute9 := mtln_row.d_attribute9;
1275 mtlt_row.d_attribute10 := mtln_row.d_attribute10;
1276 mtlt_row.n_attribute1 := mtln_row.n_attribute1;
1277 mtlt_row.n_attribute2 := mtln_row.n_attribute2;
1278 mtlt_row.n_attribute3 := mtln_row.n_attribute3;
1279 mtlt_row.n_attribute4 := mtln_row.n_attribute4;
1280 mtlt_row.n_attribute5 := mtln_row.n_attribute5;
1281 mtlt_row.n_attribute6 := mtln_row.n_attribute6;
1282 mtlt_row.n_attribute7 := mtln_row.n_attribute7;
1283 mtlt_row.n_attribute8 := mtln_row.n_attribute8;
1284 mtlt_row.n_attribute9 := mtln_row.n_attribute9;
1285 mtlt_row.n_attribute10 := mtln_row.n_attribute10;
1286 mtlt_row.vendor_id := mtln_row.vendor_id;
1287 mtlt_row.territory_code := mtln_row.territory_code;
1288 mtlt_table(cnt) := mtlt_row;
1289 cnt := cnt + 1;
1290 mtlt_row := NULL;
1291 END LOOP;
1292
1293 IF (l_debug = 1) THEN
1294 mydebug('after creating mtlt_table');
1295 END IF;
1296 END IF;
1297 /********* serial Controlled **************/
1298 IF((v_serial_control_code NOT IN(1, 6))
1299 AND(v_lot_control_code IN(1, 2))) THEN
1300
1301 cnt := 1;
1302
1303 /**2706001 conditionally opening cursors **/
1304 IF(v_lot_control_code = 1 AND v_serial_control_code NOT IN
1305 (1,6) ) THEN
1306 OPEN mut1(p_set_id);
1307 ELSIF(v_lot_control_code = 2 AND v_serial_control_code NOT IN
1308 (1,6)) THEN
1309 OPEN mut2(p_set_id);
1310 END IF;
1311
1312
1313 LOOP
1314 IF (v_lot_control_code = 1
1315 AND v_serial_control_code NOT IN(1, 6)) THEN
1316 FETCH mut1 INTO mut_row;
1317 EXIT WHEN mut1%NOTFOUND;
1318 ELSIF(v_lot_control_code = 2
1319 AND v_serial_control_code NOT IN(1, 6)) THEN
1320 FETCH mut2 INTO mut_row;
1321 /**2706001 earlier mut1%notfound **/
1322 EXIT WHEN mut2%NOTFOUND;
1323 ELSE
1324 EXIT;
1325 END IF;
1326
1327 msnt_row.transaction_temp_id := mut_row.transaction_id;
1328 msnt_row.last_update_date := mut_row.last_update_date;
1329 msnt_row.last_updated_by := mut_row.last_updated_by;
1330 msnt_row.creation_date := mut_row.creation_date;
1331 msnt_row.created_by := mut_row.created_by;
1332 msnt_row.last_update_login := mut_row.last_update_login;
1333 msnt_row.fm_serial_number := mut_row.serial_number;
1334 msnt_row.to_serial_number := mut_row.serial_number;
1335 --msnt_row.INVENTORY_ITEM_ID := l_item_id;
1336 --msnt_row.ORGANIZATION_ID := l_org_id;
1337 --msnt_row.SUBINVENTORY_CODE := l_sub_code;
1338 --msnt_row.LOCATOR_ID := l_loc_id;
1339 --msnt_row.TRANSACTION_DATE := l_txn_date;
1340 --msnt_row.TRANSACTION_SOURCE_ID := l_txn_source_id;
1341 --msnt_row.transaction_source_type_id := l_txn_source_type_id;
1342 --msnt_row.TRANSACTION_SOURCE_NAME := l_txn_source_name;
1343 --msnt_row.RECEIPT_ISSUE_TYPE := mut_row.;
1344 --msnt_row.CUSTOMER_ID := mut_row.;
1345 --msnt_row.SHIP_ID := mut_row.;
1346 msnt_row.serial_attribute_category := mut_row.serial_attribute_category;
1347 msnt_row.origination_date := mut_row.origination_date;
1348 msnt_row.c_attribute1 := mut_row.c_attribute1;
1349 msnt_row.c_attribute2 := mut_row.c_attribute2;
1350 msnt_row.c_attribute3 := mut_row.c_attribute3;
1351 msnt_row.c_attribute4 := mut_row.c_attribute4;
1352 msnt_row.c_attribute5 := mut_row.c_attribute5;
1353 msnt_row.c_attribute6 := mut_row.c_attribute6;
1354 msnt_row.c_attribute7 := mut_row.c_attribute7;
1355 msnt_row.c_attribute8 := mut_row.c_attribute8;
1356 msnt_row.c_attribute9 := mut_row.c_attribute9;
1357 msnt_row.c_attribute10 := mut_row.c_attribute10;
1358 msnt_row.c_attribute11 := mut_row.c_attribute11;
1359 msnt_row.c_attribute12 := mut_row.c_attribute12;
1360 msnt_row.c_attribute13 := mut_row.c_attribute13;
1361 msnt_row.c_attribute14 := mut_row.c_attribute14;
1362 msnt_row.c_attribute15 := mut_row.c_attribute15;
1363 msnt_row.c_attribute16 := mut_row.c_attribute16;
1364 msnt_row.c_attribute17 := mut_row.c_attribute17;
1365 msnt_row.c_attribute18 := mut_row.c_attribute18;
1366 msnt_row.c_attribute19 := mut_row.c_attribute19;
1367 msnt_row.c_attribute20 := mut_row.c_attribute20;
1368 msnt_row.d_attribute1 := mut_row.d_attribute1;
1369 msnt_row.d_attribute2 := mut_row.d_attribute2;
1370 msnt_row.d_attribute3 := mut_row.d_attribute3;
1371 msnt_row.d_attribute4 := mut_row.d_attribute4;
1372 msnt_row.d_attribute5 := mut_row.d_attribute5;
1373 msnt_row.d_attribute6 := mut_row.d_attribute6;
1374 msnt_row.d_attribute7 := mut_row.d_attribute7;
1375 msnt_row.d_attribute8 := mut_row.d_attribute8;
1376 msnt_row.d_attribute9 := mut_row.d_attribute9;
1377 msnt_row.d_attribute10 := mut_row.d_attribute10;
1378 msnt_row.n_attribute1 := mut_row.n_attribute1;
1379 msnt_row.n_attribute2 := mut_row.n_attribute2;
1380 msnt_row.n_attribute3 := mut_row.n_attribute3;
1381 msnt_row.n_attribute4 := mut_row.n_attribute4;
1382 msnt_row.n_attribute5 := mut_row.n_attribute5;
1383 msnt_row.n_attribute6 := mut_row.n_attribute6;
1384 msnt_row.n_attribute7 := mut_row.n_attribute7;
1385 msnt_row.n_attribute8 := mut_row.n_attribute8;
1386 msnt_row.n_attribute9 := mut_row.n_attribute9;
1387 msnt_row.n_attribute10 := mut_row.n_attribute10;
1388 msnt_row.status_id := mut_row.status_id;
1389 msnt_row.territory_code := mut_row.territory_code;
1390 msnt_row.time_since_new := mut_row.time_since_new;
1391 msnt_row.cycles_since_new := mut_row.cycles_since_new;
1392 msnt_row.time_since_overhaul := mut_row.time_since_overhaul;
1393 msnt_row.cycles_since_overhaul := mut_row.cycles_since_overhaul;
1394 msnt_row.time_since_repair := mut_row.time_since_repair;
1395 msnt_row.cycles_since_repair := mut_row.cycles_since_repair;
1396 msnt_row.time_since_visit := mut_row.time_since_visit;
1397 msnt_row.cycles_since_visit := mut_row.cycles_since_visit;
1398 msnt_row.time_since_mark := mut_row.time_since_mark;
1399 msnt_row.cycles_since_mark := mut_row.cycles_since_mark;
1400 msnt_row.number_of_repairs := mut_row.number_of_repairs;
1401 msnt_table(cnt) := msnt_row;
1402 cnt := cnt + 1;
1403 msnt_row := NULL;
1404 END LOOP;
1405
1406 IF (l_debug = 1) THEN
1407 mydebug('after creating msnt_table');
1408 END IF;
1409 END IF;
1410
1411 x_mmtt := mmtt_table;
1412 x_mtlt := mtlt_table;
1413 x_msnt := msnt_table;
1414
1415 IF (l_debug = 1) THEN
1416 mydebug('end of get_temp_tables');
1417 END IF;
1418 END get_temp_tables;
1419
1420 PROCEDURE generate_next_task(
1421 x_return_status OUT NOCOPY VARCHAR2
1422 , x_msg_count OUT NOCOPY NUMBER
1423 , x_msg_data OUT NOCOPY VARCHAR2
1424 , x_ret_code OUT NOCOPY VARCHAR2
1425 , p_old_header_id IN NUMBER
1426 , p_mo_line_id IN NUMBER
1427 , p_old_sub_code IN VARCHAR2
1428 , p_old_loc_id IN NUMBER
1429 , p_wms_task_type IN NUMBER
1430 ) IS
1431 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_NEXT_TASK';
1432 l_api_version CONSTANT NUMBER := 1.0;
1433 mmtt_table mmtt_tb;
1434 mmtt_row mmtt_type;
1435 lot_row mtlt_type;
1436 mtlt_table mtlt_tb;
1437 ser_row msnt_type;
1438 msnt_table msnt_tb;
1439 mmt_row mmtt_type;
1440 mtln_row mtln_type;
1441 mut_row mut_type;
1442 cnt NUMBER := 0;
1443 new_txn_temp_id NUMBER;
1444 new_txn_header_id NUMBER;
1445 ser_transaction_temp_id NUMBER;
1446 v_rev_control_code NUMBER := -1;
1447 v_lot_control_code NUMBER := -1;
1448 v_serial_control_code NUMBER := -1;
1449 v_allocate_serial_flag VARCHAR2(1) := 'X';
1450 l_rev_ctrl VARCHAR2(1) := 'N';
1451 l_alloc_ser VARCHAR2(1) := 'N';
1452 --Bug 2561167 fix
1453 l_crossdocked VARCHAR2(1) := 'N';
1454 l_already_used VARCHAR2(1) := 'N';
1455 --BUG 2698630 fix
1456 l_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
1457 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
1458 l_trolin_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
1459 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
1460 l_msg_count NUMBER;
1461 l_msg_data VARCHAR2(2000);
1462 l_line_num Number := 0;
1463 l_uom VARCHAR2(60);
1464 l_trohdr_val_rec INV_Move_Order_PUB.Trohdr_Val_Rec_Type;
1465 l_ref VARCHAR2(240);
1466 l_ref_type NUMBER;
1467 l_ref_id NUMBER;
1468 l_req_msg VARCHAR2(30) := NULL;
1469 --BUG 2698630 fix
1470
1471 CURSOR mtlt(txn_tmp_id NUMBER) IS
1472 SELECT *
1473 FROM mtl_transaction_lots_temp
1474 WHERE transaction_temp_id = txn_tmp_id;
1475
1476 CURSOR msnt(txn_tmp_id NUMBER) IS
1477 SELECT *
1478 FROM mtl_serial_numbers_temp
1479 WHERE transaction_temp_id = txn_tmp_id;
1480
1481 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1482 BEGIN
1483 SAVEPOINT generate_next_task;
1484 x_ret_code := fnd_api.g_ret_sts_success;
1485
1486 IF (l_debug = 1) THEN
1487 mydebug('In generate_next_task');
1488 END IF;
1489
1490 IF (l_debug = 1) THEN
1491 mydebug(' p_old_header_id is ' || p_old_header_id);
1492 END IF;
1493
1494 IF (l_debug = 1) THEN
1495 mydebug(' p_mo_line_id is ' || p_mo_line_id);
1496 END IF;
1497
1498 IF (l_debug = 1) THEN
1499 mydebug(' p_old_sub_CODE is ' || p_old_sub_code);
1500 END IF;
1501
1502 IF (l_debug = 1) THEN
1503 mydebug(' p_old_loc_id is ' || p_old_loc_id);
1504 END IF;
1505
1506 IF (l_debug = 1) THEN
1507 mydebug(' p_wms_task_type is ' || p_wms_task_type);
1508 END IF;
1509
1510 --Bug 2561167 fix
1511
1512 IF (p_wms_task_type = 2 OR p_wms_task_type = -1
1513 OR p_wms_task_type = 5 -- bug fix 4230494
1514 ) THEN
1515 --2697301 fix earlier doing p_wms_task_type = 6
1516 IF (l_debug = 1) THEN
1517 mydebug('Putaway task');
1518 END IF;
1519
1520 l_crossdocked := 'N';
1521
1522 BEGIN
1523 SELECT 'Y'
1524 INTO l_crossdocked
1525 FROM DUAL
1526 WHERE EXISTS(
1527 SELECT mtrl.line_id
1528 FROM mtl_txn_request_lines mtrl, mtl_material_transactions mmt
1529 WHERE mtrl.line_id = mmt.move_order_line_id
1530 AND mtrl.backorder_delivery_detail_id IS NOT NULL
1531 AND mmt.transaction_set_id = p_old_header_id);
1532 EXCEPTION
1533 WHEN OTHERS THEN
1534 l_crossdocked := 'N';
1535
1536 IF (l_debug = 1) THEN
1537 mydebug('Not cross docked');
1538 END IF;
1539 END;
1540
1541 IF l_crossdocked = 'Y' THEN
1542 IF (l_debug = 1) THEN
1543 mydebug('crossdocked - so dont generate next task');
1544 END IF;
1545
1546 RETURN;
1547 END IF;
1548 ELSIF p_wms_task_type = 4 THEN
1549 IF (l_debug = 1) THEN
1550 mydebug('Replenishment task - ok ');
1551 END IF;
1552 ELSE
1553 IF (l_debug = 1) THEN
1554 mydebug('not repl or putaway task - so dont generate next task');
1555 END IF;
1556
1557 RETURN;
1558 END IF;
1559
1560 --Bug 2561167 fix
1561
1562
1563
1564 wms_task_utils_pvt.get_temp_tables(p_set_id => p_old_header_id, x_mmtt => mmtt_table, x_mtlt => mtlt_table, x_msnt => msnt_table);
1565
1566 IF (l_debug = 1) THEN
1567 mydebug('After calling get_temp_tables ');
1568 END IF;
1569
1570 SELECT mtl_material_transactions_s.NEXTVAL
1571 INTO new_txn_header_id
1572 FROM DUAL;
1573
1574 IF (l_debug = 1) THEN
1575 mydebug('New Txn Hdr id is ' || new_txn_header_id);
1576 END IF;
1577
1578 /* IF mmtt_table.COUNT > 1 THEN
1579
1580 IF (l_debug = 1) THEN
1581 mydebug('ERROR - Number of rows for this header are more than one ');
1582 mydebug('Raising an unexpected error ');
1583 END IF;
1584 RAISE fnd_api.g_exc_unexpected_error;
1585
1586 END IF;
1587 */
1588 FOR cnt IN 1 .. mmtt_table.COUNT LOOP
1589 mmtt_row := mmtt_table(cnt);
1590
1591 IF ((mmtt_row.subinventory_code = p_old_sub_code)
1592 AND(mmtt_row.locator_id = p_old_loc_id)) THEN
1593 IF (l_debug = 1) THEN
1594 mydebug(' source and destination sub location is same ');
1595 mydebug(' hence new task is not created ');
1596 END IF;
1597 ELSIF mmtt_row.primary_quantity <= 0 THEN
1598 IF (l_debug = 1) THEN
1599 mydebug(' ignoring the Replenishment task with negative quntity ');
1600 END IF;
1601
1602 -- We should always skip the negative quantity one, whenever two
1603 -- txns are created for the putaway( in case of a po receipt
1604 -- only one txn gets submitted)
1605 -- For a replenishment tasks, two rows will be created in MMT
1606 -- one with a positive quantity corresponding to the movement of
1607 -- material to the destination
1608 -- one with negative quantity corresponding to the issue of
1609 -- material from the source location
1610 -- so ignoring te negative line here , +ve line is picked up in
1611 -- the else next
1612
1613 IF (l_debug = 1) THEN
1614 mydebug(' ignoring the Replenishment task with negative quntity ');
1615 END IF;
1616 ELSIF mmtt_row.transaction_action_id = 50 THEN
1617 --Bug 2561167 fix
1618 IF (l_debug = 1) THEN
1619 mydebug(' ignoring the pack transaction ');
1620 END IF;
1621 --Bug 2561167 fix
1622 -- 9486883
1623 ELSIF mmtt_row.transaction_action_id = 6 THEN
1624 IF (l_debug = 1) THEN
1625 mydebug(' ignoring the consumption transaction for consigned items');
1626 END IF;
1627
1628 ELSE
1629 IF (l_debug = 1) THEN
1630 mydebug(' source and destination sub location are not same ');
1631 END IF;
1632
1633 SELECT mtl_material_transactions_s.NEXTVAL
1634 INTO new_txn_temp_id
1635 FROM DUAL;
1636
1637 IF (l_debug = 1) THEN
1638 mydebug('New Txn Temp id is ' || new_txn_temp_id);
1639 END IF;
1640
1641 IF (l_debug = 1) THEN
1642 mydebug('updating the new task ');
1643 END IF;
1644
1645 --mmtt_row.transaction_temp_id := new_txn_temp_id;
1646 mmtt_row.transaction_header_id := new_txn_header_id;
1647 -- Always the second task is a replenishment task
1648
1649 mmtt_row.wms_task_type := 4;
1650 mmtt_row.move_order_line_id := p_mo_line_id;
1651 mmtt_row.transaction_source_type_id := inv_globals.g_sourcetype_moveorder; -- bug 4230494
1652 mmtt_row.transaction_type_id := 64; -- bug 4230494 move order xfer
1653 mmtt_row.transaction_action_id := inv_globals.g_action_subxfr;
1654 mmtt_row.process_flag := 'Y';
1655 mmtt_row.transaction_status := 2;
1656 mmtt_row.transfer_subinventory := p_old_sub_code;
1657 mmtt_row.transfer_to_location := p_old_loc_id;
1658 mmtt_row.posting_flag := 'Y';
1659
1660 IF (l_debug = 1) THEN
1661 mydebug(' mmtt_row.wms_task_type ' || mmtt_row.wms_task_type);
1662 mydebug(' mmtt_row.mmtt_row.move_order_line_id ' || mmtt_row.move_order_line_id);
1663 mydebug(' mmtt_row.transaction_source_type_id ' || mmtt_row.transaction_source_type_id);
1664 mydebug(' mmtt_row.transaction_type_id ' || mmtt_row.transaction_type_id);
1665 mydebug(' mmtt_row.transaction_action_id ' || mmtt_row.transaction_action_id);
1666 mydebug(' mmtt_row.process_flag ' || mmtt_row.process_flag);
1667 mydebug(' mmtt_row.transaction_status ' || mmtt_row.transaction_status);
1668 mydebug(' mmtt_row.transfer_subinventory ' || mmtt_row.transfer_subinventory);
1669 mydebug(' mmtt_row.transfer_to_location ' || mmtt_row.transfer_to_location);
1670 mydebug(' mmtt_row.primary_quantity ' || mmtt_row.primary_quantity);
1671 mydebug(' mmtt_row.transaction_quantity ' || mmtt_row.transaction_quantity);
1672 mydebug('sub ' || mmtt_row.subinventory_code);
1673 mydebug('loc ' || mmtt_row.locator_id);
1674 mydebug('t sub ' || mmtt_row.transfer_subinventory);
1675 mydebug('t loc ' || mmtt_row.transfer_to_location);
1676 END IF;
1677
1678 BEGIN
1679 SELECT revision_qty_control_code
1680 , lot_control_code
1681 , serial_number_control_code
1682 , primary_uom_code
1683 INTO v_rev_control_code
1684 , v_lot_control_code
1685 , v_serial_control_code
1686 , l_uom
1687 FROM mtl_system_items
1688 WHERE inventory_item_id = mmtt_table(cnt).inventory_item_id
1689 AND organization_id = mmtt_table(cnt).organization_id;
1690 EXCEPTION
1691 WHEN OTHERS THEN
1692 mydebug('Exception getting the item information');
1693 RAISE fnd_api.g_exc_unexpected_error;
1694 END;
1695
1696 /***** Bug 2999296 Updating locator capacity ***********/
1697
1698 mydebug('Updating locator capacity of loc '||mmtt_row.transfer_to_location);
1699
1700 inv_loc_wms_utils.update_loc_sugg_capacity_nauto
1701 ( x_return_status => l_return_status
1702 , x_msg_count => l_msg_count
1703 , x_msg_data => l_msg_data
1704 , p_organization_id => mmtt_row.organization_id
1705 , p_inventory_location_id => mmtt_row.transfer_to_location
1706 , p_inventory_item_id => mmtt_row.inventory_item_id
1707 , p_primary_uom_flag => 'Y'
1708 , p_transaction_uom_code => NULL
1709 , p_quantity => mmtt_row.primary_quantity
1710 );
1711
1712 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1713 mydebug('Unexpected error in update_loc_suggested_capacity');
1714 -- Bug 5393727: do not raise an exception if revert API returns an error
1715 -- RAISE fnd_api.g_exc_unexpected_error;
1716 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1717 mydebug('Error in update_loc_suggested_capacity');
1718 -- Bug 5393727: do not raise an exception if revert API returns an error
1719 -- RAISE fnd_api.g_exc_error;
1720 END IF;
1721
1722 /***** Bug 2999296 Updating locator capacity ***********/
1723
1724 /******* BUG 2698630 fix creating move order*/
1725
1726 BEGIN
1727 SELECT reference,reference_type_code,reference_id
1728 INTO l_ref,l_ref_type, l_ref_id
1729 FROM mtl_txn_request_lines
1730 WHERE
1731 line_id = mmtt_row.move_order_line_id;
1732 EXCEPTION
1733 WHEN others THEN
1734 mydebug('Exception getting the move order line information');
1735 RAISE fnd_api.g_exc_unexpected_error;
1736 END;
1737
1738 l_trohdr_rec.request_number := FND_API.G_MISS_CHAR ; --5984021
1739 l_trohdr_rec.header_id := FND_API.G_MISS_NUM;
1740 l_trohdr_rec.created_by := FND_GLOBAL.USER_ID;
1741 l_trohdr_rec.creation_date := sysdate;
1742 l_trohdr_rec.date_required := sysdate;
1743 l_trohdr_rec.from_subinventory_code := mmtt_row.subinventory_code;
1744 l_trohdr_rec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
1745 l_trohdr_rec.last_updated_by := FND_GLOBAL.USER_ID;
1746 l_trohdr_rec.last_update_date := sysdate;
1747 l_trohdr_rec.last_update_login := FND_GLOBAL.USER_ID;
1748 l_trohdr_rec.organization_id := mmtt_row.organization_id;
1749 l_trohdr_rec.status_date := sysdate;
1750 l_trohdr_rec.to_subinventory_code := mmtt_row.transfer_subinventory;
1751 l_trohdr_rec. move_order_type := INV_GLOBALS.g_move_order_replenishment;
1752 l_trohdr_rec.db_flag := FND_API.G_TRUE;
1753 l_trohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;
1754
1755 l_line_num := 1;
1756 l_trolin_tbl(1).header_id := l_trohdr_rec.header_id;
1757 l_trolin_tbl(1).created_by := FND_GLOBAL.USER_ID;
1758 l_trolin_tbl(1).creation_date := sysdate;
1759 l_trolin_tbl(1).date_required := sysdate;
1760 l_trolin_tbl(1).from_subinventory_code := mmtt_row.subinventory_code;
1761 l_trolin_tbl(1).from_locator_id := mmtt_row.locator_id;
1762 l_trolin_tbl(1).inventory_item_id := mmtt_row.inventory_item_id;
1763 l_trolin_tbl(1).last_updated_by := FND_GLOBAL.USER_ID;
1764 l_trolin_tbl(1).last_update_date := sysdate;
1765 l_trolin_tbl(1).last_updated_by := FND_GLOBAL.USER_ID;
1766 l_trolin_tbl(1).last_update_date := sysdate;
1767 l_trolin_tbl(1).last_update_login := FND_GLOBAL.LOGIN_ID;
1768 l_trolin_tbl(1).line_id := FND_API.G_MISS_NUM;
1769 l_trolin_tbl(1).line_number := l_line_num;
1770 l_trolin_tbl(1).line_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
1771 l_trolin_tbl(1).organization_id := mmtt_row.organization_id;
1772 l_trolin_tbl(1).quantity := mmtt_row.primary_quantity;
1773 l_trolin_tbl(1).quantity_detailed := mmtt_row.primary_quantity;
1774 --Bug 4593622 stamping mmtt_row.primary_quantity as quantity_detailed
1775
1776 l_trolin_tbl(1).status_date := sysdate;
1777 l_trolin_tbl(1).to_subinventory_code := mmtt_row.transfer_subinventory;
1778 l_trolin_tbl(1).to_locator_id := mmtt_row.transfer_to_location; --14801304
1779 l_trolin_tbl(1).uom_code := l_uom;
1780 l_trolin_tbl(1).db_flag := FND_API.G_TRUE;
1781 l_trolin_tbl(1).operation := INV_GLOBALS.G_OPR_CREATE;
1782
1783 l_trolin_tbl(1).lpn_id := NULL;
1784 l_trolin_tbl(1).reference:=l_ref;
1785 l_trolin_tbl(1).reference_type_code:=l_ref_type;
1786 l_trolin_tbl(1).reference_id:=l_ref_id;
1787 l_trolin_tbl(1).project_id:=NULL;
1788 l_trolin_tbl(1).task_id:=NULL;
1789 l_trolin_tbl(1).lot_number:=NULL;
1790 l_trolin_tbl(1).revision:=mmtt_row.revision;
1791 l_trolin_tbl(1).transaction_type_id:=mmtt_row.transaction_type_id;
1792 l_trolin_tbl(1).transaction_source_type_id:=mmtt_row.transaction_source_type_id;
1793 l_trolin_tbl(1).inspection_status:=NULL;
1794 l_trolin_tbl(1).wms_process_flag:=NULL;
1795 l_trolin_tbl(1).to_organization_id:=mmtt_row.transfer_organization;
1796 l_trolin_tbl(1).txn_source_id:=mmtt_row.transaction_source_id;
1797 l_trolin_tbl(1).from_cost_group_id:=mmtt_row.cost_group_id;
1798 l_trolin_tbl(1).to_cost_group_id:=mmtt_row.transfer_cost_group_id;
1799 --Adding for Bug 13495082
1800 l_trolin_tbl(1).secondary_uom:=mmtt_row.secondary_uom_code;
1801 l_trolin_tbl(1).secondary_quantity:=mmtt_row.secondary_transaction_quantity;
1802 l_trolin_tbl(1).secondary_quantity_detailed:= mmtt_row.secondary_transaction_quantity;
1803 --Adding for Bug 13495082
1804
1805 INV_Move_Order_PUB.Process_Move_Order
1806 ( p_api_version_number => 1.0 ,
1807 p_init_msg_list => 'F',
1808 p_commit => FND_API.G_FALSE,
1809 x_return_status => l_return_status,
1810 x_msg_count => l_msg_count,
1811 x_msg_data => l_msg_data,
1812 p_trohdr_rec => l_trohdr_rec,
1813 p_trohdr_val_rec => l_trohdr_val_rec,
1814 p_trolin_tbl => l_trolin_tbl,
1815 p_trolin_val_tbl => l_trolin_val_tbl,
1816 x_trohdr_rec => l_trohdr_rec,
1817 x_trohdr_val_rec => l_trohdr_val_rec,
1818 x_trolin_tbl => l_trolin_tbl,
1819 x_trolin_val_tbl => l_trolin_val_tbl
1820 );
1821
1822
1823 fnd_msg_pub.count_and_get
1824 ( p_count => l_msg_count
1825 , p_data => l_msg_data
1826 );
1827 IF (l_msg_count = 0) THEN
1828 IF (l_debug = 1) THEN
1829 mydebug('create_mo: Successful');
1830 END IF;
1831 ELSIF (l_msg_count = 1) THEN
1832 IF (l_debug = 1) THEN
1833 mydebug('create_mo: Not Successful');
1834 mydebug('create_mo: ' || replace(l_msg_data,fnd_global.local_chr(0),' '));
1835 END IF;
1836 ELSE
1837 IF (l_debug = 1) THEN
1838 mydebug('create_mo: Not Successful2');
1839 END IF;
1840 For I in 1..l_msg_count LOOP
1841 l_msg_data := fnd_msg_pub.get(I,'F');
1842 IF (l_debug = 1) THEN
1843 mydebug('create_mo: ' || replace(l_msg_data,fnd_global.local_chr(0),' '));
1844 END IF;
1845 END LOOP;
1846 END IF;
1847
1848
1849 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1850 FND_MESSAGE.SET_NAME('WMS','WMS_TD_MO_ERROR' );
1851 FND_MSG_PUB.ADD;
1852 RAISE FND_API.g_exc_unexpected_error;
1853
1854 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1855 FND_MESSAGE.SET_NAME('WMS','WMS_TD_MO_ERROR');
1856 FND_MSG_PUB.ADD;
1857 RAISE FND_API.G_EXC_ERROR;
1858 END IF;
1859
1860
1861 /* Get header and line ids */
1862 IF (l_debug = 1) THEN
1863 mydebug('create_mo: Header'||l_trohdr_rec.header_id);
1864 mydebug('create_mo: line'||l_trolin_tbl(1).line_id);
1865 mydebug('create_mo: ' || l_trolin_tbl(1).organization_id);
1866 END IF;
1867
1868 mmtt_row.move_order_line_id := l_trolin_tbl(1).line_id;
1869 mmtt_row.trx_source_line_id := l_trolin_tbl(1).line_id; -- bug 4230494
1870 /******* BUG 2698630 fix creating move order*/
1871
1872 /*************NOW Updating MTLT and MSNT *************************/
1873
1874
1875 IF v_rev_control_code = 2 THEN
1876 l_rev_ctrl := 'Y';
1877 ELSE
1878 l_rev_ctrl := 'N';
1879 END IF;
1880
1881 SELECT allocate_serial_flag
1882 INTO v_allocate_serial_flag
1883 FROM mtl_parameters
1884 WHERE organization_id = mmtt_table(cnt).organization_id;
1885
1886 IF v_allocate_serial_flag = 'Y' THEN
1887 l_alloc_ser := 'Y';
1888 ELSE
1889 l_alloc_ser := 'N';
1890 END IF;
1891
1892 /*****LOT controlled only **********/
1893 IF (v_lot_control_code = 2
1894 AND v_serial_control_code IN(1, 6)) THEN
1895 IF (l_debug = 1) THEN
1896 mydebug(' LOT controlled only ');
1897 END IF;
1898
1899 FOR cnt2 IN 1 .. mtlt_table.COUNT LOOP
1900 lot_row := mtlt_table(cnt2);
1901
1902 IF lot_row.transaction_temp_id = mmtt_row.transaction_temp_id THEN
1903 IF (l_debug = 1) THEN
1904 mydebug('child row with temp id ' || lot_row.transaction_temp_id);
1905 END IF;
1906
1907 IF NOT check_qty_avail(
1908 mmtt_row => mmtt_row
1909 , lot_row => lot_row
1910 , ser_row => NULL
1911 , p_is_revision_control => l_rev_ctrl
1912 , p_is_lot_control => 'Y'
1913 , p_is_serial_control => 'N'
1914 , p_allocate_serial_flag => l_alloc_ser
1915 ) THEN
1916 IF (l_debug = 1) THEN
1917 mydebug('failed quantity check ');
1918 END IF;
1919
1920 RAISE g_qty_not_avail;
1921 END IF;
1922
1923 lot_row.transaction_temp_id := new_txn_temp_id;
1924 inv_rcv_common_apis.insert_mtlt(lot_row);
1925 lot_row := NULL;
1926 END IF;
1927 END LOOP;
1928 /********* serial Controlled only **************/
1929 ELSIF(v_lot_control_code = 1
1930 AND v_serial_control_code NOT IN(1, 6)) THEN
1931 IF (l_debug = 1) THEN
1932 mydebug(' Serial controlled only ');
1933 END IF;
1934
1935 IF (v_allocate_serial_flag = 'Y') THEN
1936 IF (l_debug = 1) THEN
1937 mydebug(' allocate_serial_flag is Y ');
1938 END IF;
1939 /**2706001 checking the avail outside loop **/
1940 IF NOT check_qty_avail(mmtt_row => mmtt_row,
1941 lot_row => null,
1942 ser_row => null,
1943 p_is_revision_control => l_rev_ctrl,
1944 p_is_lot_control => 'N',
1945 p_is_serial_control => 'Y',
1946 p_allocate_serial_flag => l_alloc_ser) THEN
1947 mydebug('failed quantity check ');
1948 RAISE g_qty_not_avail;
1949 END IF;
1950
1951 FOR cnt3 IN 1 .. msnt_table.COUNT LOOP
1952 ser_row := msnt_table(cnt3);
1953
1954 IF ser_row.transaction_temp_id = mmtt_row.transaction_temp_id THEN
1955 IF (l_debug = 1) THEN
1956 mydebug('child row with temp id ' || ser_row.transaction_temp_id);
1957 END IF;
1958
1959 /**2706001 checking group mark ids here **/
1960 l_already_used := 'N';
1961 BEGIN
1962 SELECT 'Y' INTO l_already_used FROM dual WHERE exists
1963 (SELECT 1
1964 FROM mtl_serial_numbers
1965 WHERE
1966 --Bug 2940878 fix added current_organization_id ,
1967 --inventory_item_id in the query
1968 -- also changed the condition on group_mark_id
1969 current_organization_id = mmtt_row.organization_id AND
1970 inventory_item_id = mmtt_row.inventory_item_id AND
1971 serial_number >= ser_row.fm_serial_number AND
1972 serial_number <= ser_row.to_serial_number AND
1973 --group_mark_id IS NOT NULL
1974 Nvl(group_mark_id, -1) <> -1
1975 );
1976 EXCEPTION
1977 WHEN no_data_found THEN
1978 l_already_used := 'N';
1979 WHEN OTHERS THEN
1980 mydebug('Error occurred '||Sqlerrm);
1981 l_already_used := NULL;
1982 RAISE fnd_api.g_exc_unexpected_error;
1983 END;
1984
1985 IF l_already_used = 'Y' then
1986 mydebug('failed quantity check ');
1987 RAISE g_qty_not_avail;
1988 END IF;
1989 /**2706001 checking group mark ids here **/
1990 ser_row.transaction_temp_id := new_txn_temp_id;
1991 inv_rcv_common_apis.insert_msnt(ser_row);
1992 ser_row := NULL;
1993 END IF;
1994 END LOOP;
1995 END IF;
1996 /********* LOT and serial Controlled **************/
1997 ELSIF(v_lot_control_code = 2
1998 AND v_serial_control_code NOT IN(1, 6)) THEN
1999 IF (l_debug = 1) THEN
2000 mydebug(' Both lot and Serial controlled ');
2001 END IF;
2002
2003 IF (v_allocate_serial_flag = 'N') THEN
2004 /*******************same as LOT CONTROLLED ONLY***********/
2005 FOR cnt4 IN 1 .. mtlt_table.COUNT LOOP
2006 lot_row := mtlt_table(cnt4);
2007
2008 IF lot_row.transaction_temp_id = mmtt_row.transaction_temp_id THEN
2009 IF (l_debug = 1) THEN
2010 mydebug('child row with temp id ' || lot_row.transaction_temp_id);
2011 END IF;
2012
2013 IF NOT check_qty_avail(
2014 mmtt_row => mmtt_row
2015 , lot_row => lot_row
2016 , ser_row => NULL
2017 , p_is_revision_control => l_rev_ctrl
2018 , p_is_lot_control => 'Y'
2019 , p_is_serial_control => 'Y'
2020 , p_allocate_serial_flag => l_alloc_ser
2021 ) THEN
2022 IF (l_debug = 1) THEN
2023 mydebug('failed quantity check ');
2024 END IF;
2025
2026 RAISE g_qty_not_avail;
2027 END IF;
2028
2029 lot_row.serial_transaction_temp_id := NULL;
2030 lot_row.transaction_temp_id := new_txn_temp_id;
2031 inv_rcv_common_apis.insert_mtlt(lot_row);
2032 lot_row := NULL;
2033 END IF;
2034 END LOOP;
2035 --END IF;
2036 ELSE
2037 /*Need to insert both lot and serial tables*/
2038 IF (l_debug = 1) THEN
2039 mydebug(' allocate_serial_flag is Y ');
2040 END IF;
2041
2042 FOR cnt5 IN 1 .. mtlt_table.COUNT LOOP
2043 lot_row := mtlt_table(cnt5);
2044
2045 /***********Serial Stuff *****************************/
2046 IF lot_row.transaction_temp_id = mmtt_row.transaction_temp_id THEN
2047 IF (l_debug = 1) THEN
2048 mydebug('child lot row with temp id ' || lot_row.transaction_temp_id);
2049 END IF;
2050 /**2706001 checking avail qty outside loop **/
2051 IF NOT check_qty_avail(mmtt_row => mmtt_row,
2052 lot_row => lot_row,
2053 ser_row => null,
2054 p_is_revision_control => l_rev_ctrl,
2055 p_is_lot_control => 'Y',
2056 p_is_serial_control => 'Y',
2057 p_allocate_serial_flag => l_alloc_ser) THEN
2058 mydebug('failed quantity check ');
2059 RAISE g_qty_not_avail;
2060 END IF;
2061
2062 /**2706001 moved this out of below loop **/
2063 SELECT mtl_material_transactions_s.NEXTVAL
2064 INTO ser_transaction_temp_id
2065 FROM dual;
2066
2067 /**2706001 was using cnt6 earlier **/
2068 FOR cnt6 IN 1 .. msnt_table.COUNT LOOP
2069 ser_row := msnt_table(cnt6);
2070
2071 IF ser_row.transaction_temp_id = lot_row.serial_transaction_temp_id THEN
2072 /**2706001 checking group mark ids here **/
2073 l_already_used := 'N';
2074 BEGIN
2075 SELECT 'Y' INTO l_already_used FROM dual WHERE exists
2076 (SELECT 1
2077 FROM mtl_serial_numbers
2078 WHERE
2079 --Bug 2940878 fix added current_organization_id ,
2080 --inventory_item_id in the query
2081 -- also changed the condition on group_mark_id
2082 current_organization_id = mmtt_row.organization_id AND
2083 inventory_item_id = mmtt_row.inventory_item_id AND
2084 serial_number >= ser_row.fm_serial_number AND
2085 serial_number <= ser_row.to_serial_number AND
2086 --group_mark_id IS NOT NULL
2087 Nvl(group_mark_id, -1) <> -1
2088 );
2089 EXCEPTION
2090 WHEN no_data_found THEN
2091 l_already_used := 'N';
2092 WHEN OTHERS THEN
2093 mydebug('Error occurred '||Sqlerrm);
2094 l_already_used := NULL;
2095 RAISE fnd_api.g_exc_unexpected_error;
2096 END;
2097
2098 IF l_already_used = 'Y' then
2099 mydebug('failed quantity check ');
2100 RAISE g_qty_not_avail;
2101 END IF;
2102 /**2706001 checking group mark ids here **/
2103
2104 ser_row.transaction_temp_id:= ser_transaction_temp_id;
2105 inv_rcv_common_apis.insert_msnt(ser_row);
2106 ser_row := NULL;
2107 --lot_row.serial_transaction_temp_id := ser_row.transaction_temp_id;
2108 END IF;
2109 END LOOP;
2110
2111 /***********Serial Stuff *****************************/
2112 /**2706001 moved this assignment out of the loop **/
2113 lot_row.serial_transaction_temp_id := ser_transaction_temp_id;
2114
2115 lot_row.transaction_temp_id := new_txn_temp_id;
2116 inv_rcv_common_apis.insert_mtlt(lot_row);
2117 lot_row := NULL;
2118 END IF;
2119 END LOOP;
2120 END IF;
2121 ELSE
2122 IF (l_debug = 1) THEN
2123 mydebug('vanilla item');
2124 END IF;
2125
2126 IF NOT check_qty_avail(
2127 mmtt_row => mmtt_row
2128 , lot_row => NULL
2129 , ser_row => NULL
2130 , p_is_revision_control => l_rev_ctrl
2131 , p_is_lot_control => 'N'
2132 , p_is_serial_control => 'N'
2133 , p_allocate_serial_flag => l_alloc_ser
2134 ) THEN
2135 IF (l_debug = 1) THEN
2136 mydebug('failed quantity check ');
2137 END IF;
2138
2139 RAISE g_qty_not_avail;
2140 END IF;
2141 END IF;
2142
2143 IF (l_debug = 1) THEN
2144 mydebug(' inserting the new row into mmtt using ' || 'wms_task_dispatch_engine.insert_mmtt ');
2145 END IF;
2146
2147 mmtt_row.transaction_temp_id := new_txn_temp_id;
2148
2149
2150 --//***************//
2151
2152 --Add code here
2153 IF wms_device_integration_pvt.wms_call_device_request IS NULL THEN
2154 wms_device_integration_pvt.is_device_set_up(mmtt_row.organization_id,wms_device_integration_pvt.WMS_BE_MO_TASK_ALLOC,l_return_status);
2155 END IF;
2156
2157 --Insert records into WMS_DEVICE_REQUESTS TABLE
2158 wms_cartnzn_pub.insert_device_request_rec(mmtt_row);
2159
2160
2161
2162 -- Call Device Integration API to send the details of this
2163 -- Move Order Task Allocation to devices, if it is a WMS organization.
2164 -- Note: We don't check for the return condition of this API as
2165 -- we let the Allocation process succee irrespective of
2166 -- DeviceIntegration succeed or fail.
2167
2168 WMS_DEVICE_INTEGRATION_PVT.device_request
2169 (p_bus_event => WMS_DEVICE_INTEGRATION_PVT.WMS_BE_MO_TASK_ALLOC,
2170 p_call_ctx => WMS_Device_integration_pvt.DEV_REQ_AUTO,
2171 p_task_trx_id => NULL,
2172 x_request_msg => l_req_msg,
2173 x_return_status => l_return_status,
2174 x_msg_count => l_msg_count,
2175 x_msg_data => l_msg_data
2176 );
2177
2178 IF (l_debug = 1) THEN
2179 mydebug('Device_API: return stat:'||l_return_status);
2180 END IF;
2181
2182 --//**************//
2183
2184
2185 wms_task_dispatch_engine.insert_mmtt(l_mmtt_rec => mmtt_row);
2186
2187 IF (l_debug = 1) THEN
2188 mydebug(' calling wms_rule_pvt.assigntt ');
2189 END IF;
2190
2191 wms_rule_pvt.assigntt(
2192 p_api_version => 1.0
2193 , p_task_id => new_txn_temp_id
2194 , x_return_status => x_return_status
2195 , x_msg_count => x_msg_count
2196 , x_msg_data => x_msg_data
2197 );
2198
2199 IF (l_debug = 1) THEN
2200 mydebug('After calling wms_rule_pvt.assigntt l_return_status :'||x_return_status||' new_txn_temp_id :'||new_txn_temp_id);
2201 END IF;
2202
2203 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
2204 IF (l_debug = 1) THEN
2205 mydebug(' error returned from wms_rule_pvt.assigntt ');
2206 mydebug(x_msg_data);
2207 END IF;
2208
2209 RAISE fnd_api.g_exc_error;
2210 ELSE
2211 IF (l_debug = 1) THEN
2212 mydebug(' success returned from wms_rule_pvt.assigntt ');
2213 END IF;
2214 END IF;
2215 END IF;
2216 END LOOP;
2217 EXCEPTION
2218 WHEN g_qty_not_avail THEN
2219 ROLLBACK TO generate_next_task;
2220 x_return_status := fnd_api.g_ret_sts_error;
2221 x_ret_code := 'QTY_NOT_AVAIL';
2222 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2223 -- IF (x_msg_count = 0) THEN
2224 -- dbms_output.put_line('Successful');
2225 -- ELSIF (x_msg_count = 1) THEN
2226 -- dbms_output.put_line ('Not Successful');
2227 -- dbms_output.put_line (replace(x_msg_data,chr(0),' '));
2228 -- ELSE
2229 -- dbms_output.put_line ('Not Successful2');
2230 -- For I in 1..x_msg_count LOOP
2231 -- x_msg_data := fnd_msg_pub.get(I,'F');
2232 -- dbms_output.put_line(replace(x_msg_data,chr(0),' '));
2233 -- END LOOP;
2234 -- END IF;
2235
2236 WHEN fnd_api.g_exc_error THEN
2237 ROLLBACK TO generate_next_task;
2238 x_return_status := fnd_api.g_ret_sts_error;
2239 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2240 -- IF (x_msg_count = 0) THEN
2241 -- dbms_output.put_line('Successful');
2242 -- ELSIF (x_msg_count = 1) THEN
2243 -- dbms_output.put_line ('Not Successful');
2244 -- dbms_output.put_line (replace(x_msg_data,chr(0),' '));
2245 -- ELSE
2246 -- dbms_output.put_line ('Not Successful2');
2247 -- For I in 1..x_msg_count LOOP
2248 -- x_msg_data := fnd_msg_pub.get(I,'F');
2249 -- dbms_output.put_line(replace(x_msg_data,chr(0),' '));
2250 -- END LOOP;
2251 -- END IF;
2252
2253
2254 WHEN fnd_api.g_exc_unexpected_error THEN
2255 ROLLBACK TO generate_next_task;
2256 x_return_status := fnd_api.g_ret_sts_unexp_error;
2257 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2258 WHEN OTHERS THEN
2259 ROLLBACK TO generate_next_task;
2260 x_return_status := fnd_api.g_ret_sts_unexp_error;
2261
2262 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2263 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2264 END IF;
2265
2266 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2267 END generate_next_task;
2268
2269 PROCEDURE cancel_task(
2270 x_return_status OUT NOCOPY VARCHAR2
2271 , x_msg_count OUT NOCOPY NUMBER
2272 , x_msg_data OUT NOCOPY VARCHAR2
2273 , p_emp_id IN NUMBER
2274 , p_temp_id IN NUMBER
2275 , p_previous_task_status IN NUMBER := -1/*added for 3602199*/
2276
2277 ) IS
2278 l_dev_temp_id NUMBER := 0;
2279 l_dev_request_id NUMBER := 0;
2280 l_dev_request_msg VARCHAR2(1000);
2281 l_mo_line_id NUMBER := NULL;
2282 l_mmtt_count NUMBER;
2283 l_txn_temp_id NUMBER := NULL;
2284 l_txn_quantity NUMBER := 0;
2285 l_deleted_quantity NUMBER := 0;
2286
2287 CURSOR c_wdt_dispatched IS
2288 SELECT transaction_temp_id, device_request_id
2289 FROM wms_dispatched_tasks
2290 WHERE person_id = p_emp_id
2291 AND(status <= 3 OR status = 9)
2292 AND device_request_id IS NOT NULL;
2293
2294 CURSOR c_mo_line_id IS
2295 SELECT mtrl.line_id
2296 FROM mtl_material_transactions_temp mmtt
2297 , mtl_txn_request_lines mtrl
2298 WHERE (mmtt.transaction_temp_id = p_temp_id OR mmtt.parent_line_id = p_temp_id)
2299 AND mtrl.line_id = mmtt.move_order_line_id
2300 AND mtrl.line_status = INV_GLOBALS.G_TO_STATUS_CANCEL_BY_SOURCE;
2301
2302 CURSOR c_mmtt_to_del IS
2303 SELECT mmtt.transaction_temp_id, mmtt.primary_quantity
2304 FROM mtl_material_transactions_temp mmtt
2305 WHERE mmtt.move_order_line_id = l_mo_line_id
2306 AND NOT EXISTS(SELECT 1
2307 FROM mtl_material_transactions_temp t1
2308 WHERE t1.parent_line_id = mmtt.transaction_temp_id)
2309 AND NOT EXISTS(SELECT 1
2310 FROM wms_dispatched_tasks wdt
2311 WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id);
2312
2313 CURSOR c_get_mmtt_count IS
2314 SELECT count(*)
2315 FROM mtl_material_transactions_temp mmtt
2316 WHERE mmtt.move_order_line_id = l_mo_line_id
2317 AND NOT EXISTS ( SELECT 1
2318 FROM mtl_material_transactions_temp t1
2319 WHERE t1.parent_line_id = mmtt.transaction_temp_id);
2320
2321 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2322 BEGIN
2323 IF (l_debug = 1) THEN
2324 mydebug('Cancelling the Task: TxnTempID = ' || p_temp_id || ' : EmployeeID = ' || p_emp_id);
2325 END IF;
2326
2327 x_return_status := fnd_api.g_ret_sts_success;
2328
2329 -- Call device request for task cancel
2330 OPEN c_wdt_dispatched;
2331 LOOP
2332 FETCH c_wdt_dispatched INTO l_dev_temp_id, l_dev_request_id;
2333 EXIT WHEN c_wdt_dispatched%NOTFOUND;
2334
2335 IF l_dev_request_id IS NOT NULL THEN
2336 IF (l_debug = 1) THEN
2337 mydebug('Calling device Request for Device Temp ID = ' || l_dev_temp_id);
2338 END IF;
2339
2340 wms_device_integration_pvt.device_request(
2341 p_bus_event => wms_device_integration_pvt.wms_be_task_cancel
2342 , p_call_ctx => 'U'
2343 , p_task_trx_id => l_dev_temp_id
2344 , x_request_msg => l_dev_request_msg
2345 , x_return_status => x_return_status
2346 , x_msg_count => x_msg_count
2347 , x_msg_data => x_msg_data
2348 , p_request_id => l_dev_request_id
2349 );
2350 END IF;
2351 END LOOP;
2352 CLOSE c_wdt_dispatched;
2353
2354 ROLLBACK; --bug#2458131
2355
2356 -- Making all dispatched and active (Patchset I) tasks to pending tasks assigned to this user
2357 -- bug 3602199 keep queued tasks as queued and dont delete wdt
2358 -- DELETE FROM wms_dispatched_tasks WHERE person_id = p_emp_id AND status IN(3, 9);
2359 if(p_previous_task_status = 2/*queued*/) then
2360 DELETE FROM wms_dispatched_tasks WHERE person_id = p_emp_id AND status IN(3, 9) and transaction_temp_id <> p_temp_id;
2361 update wms_dispatched_tasks set status = 2 where transaction_temp_id = p_temp_id and person_id = p_emp_id;
2362 /* mydebug('Rows update in wdt 3602199' || SQL%ROWCOUNT);*/
2363 else/*old code*/
2364 DELETE FROM wms_dispatched_tasks where person_id = p_emp_id and status in (3,9);
2365 /* mydebug('All rows deleted from wdt' || SQL%ROWCOUNT);*/
2366 end if;
2367
2368 OPEN c_mo_line_id;
2369
2370 LOOP
2371 FETCH c_mo_line_id INTO l_mo_line_id;
2372 EXIT WHEN c_mo_line_id%NOTFOUND;
2373 IF (l_debug = 1) THEN
2374 mydebug('Cancelling Tasks for MO Line ID = ' || l_mo_line_id);
2375 END IF;
2376 l_deleted_quantity := 0;
2377
2378 OPEN c_mmtt_to_del;
2379 LOOP
2380 FETCH c_mmtt_to_del INTO l_txn_temp_id, l_txn_quantity;
2381 EXIT WHEN c_mmtt_to_del%NOTFOUND;
2382
2383 inv_trx_util_pub.delete_transaction(
2384 x_return_status => x_return_status
2385 , x_msg_data => x_msg_data
2386 , x_msg_count => x_msg_count
2387 , p_transaction_temp_id => l_txn_temp_id
2388 );
2389 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2390 IF l_debug = 1 THEN
2391 mydebug('Not able to delete the Txn = ' || l_txn_temp_id);
2392 END IF;
2393 RAISE fnd_api.g_exc_unexpected_error;
2394 END IF;
2395
2396 l_deleted_quantity := l_deleted_quantity + l_txn_quantity;
2397 END LOOP;
2398 CLOSE c_mmtt_to_del;
2399
2400 OPEN c_get_mmtt_count;
2401 FETCH c_get_mmtt_count INTO l_mmtt_count;
2402 CLOSE c_get_mmtt_count;
2403
2404 UPDATE mtl_txn_request_lines
2405 SET quantity_detailed =(quantity_detailed - l_deleted_quantity)
2406 , line_status = DECODE(l_mmtt_count, 0, INV_GLOBALS.G_TO_STATUS_CLOSED, line_status)
2407 WHERE line_id = l_mo_line_id;
2408 END LOOP;
2409 CLOSE c_mo_line_id;
2410
2411 COMMIT;
2412 EXCEPTION
2413 WHEN OTHERS THEN
2414 x_return_status := fnd_api.g_ret_sts_unexp_error;
2415 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2416 fnd_msg_pub.add_exc_msg(g_pkg_name, 'CANCEL_TASK');
2417 END IF;
2418 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2419 END cancel_task;
2420
2421 /*****************************************************************/
2422 --This function is called from the currentTasksFListener on pressing
2423 --the Unload button,
2424 --returns Y if you can continue with the unload,
2425 --returns E,U if an error occurred in this api
2426 --returns N if you cannot unload and puts the appropriate error in the stack
2427 --returns M if you cannot unload because lpn has multiple allocations
2428 /*****************************************************************/
2429 FUNCTION can_unload(p_temp_id IN NUMBER)
2430 RETURN VARCHAR2 IS
2431 l_transfer_lpn_id NUMBER := NULL;
2432 l_multiple_rows VARCHAR2(1) := NULL;
2433 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2434 BEGIN
2435 IF (l_debug = 1) THEN
2436 mydebug(' In CAN_UNLOAD for transaction_temp_id ' || p_temp_id);
2437 END IF;
2438
2439 IF (p_temp_id IS NULL) THEN
2440 RAISE fnd_api.g_exc_unexpected_error;
2441 END IF;
2442
2443 BEGIN
2444 IF (l_debug = 1) THEN
2445 mydebug(' checking if the row has same lpn_id and content_lpn_id ');
2446 END IF;
2447
2448 SELECT transfer_lpn_id
2449 INTO l_transfer_lpn_id
2450 FROM mtl_material_transactions_temp
2451 WHERE transaction_temp_id = p_temp_id
2452 AND content_lpn_id = transfer_lpn_id;
2453
2454 IF (l_debug = 1) THEN
2455 mydebug(' lpn_id and content_lpn_id are the same ' || l_transfer_lpn_id);
2456 END IF;
2457 EXCEPTION
2458 WHEN NO_DATA_FOUND THEN
2459 IF (l_debug = 1) THEN
2460 mydebug(' lpn_id and content_lpn_id are different ');
2461 END IF;
2462
2463 RETURN 'Y';
2464 END;
2465
2466 IF (l_transfer_lpn_id IS NULL) THEN
2467 IF (l_debug = 1) THEN
2468 mydebug('ERROR: transfer_lpn passed is null');
2469 END IF;
2470
2471 RAISE fnd_api.g_exc_unexpected_error;
2472 END IF;
2473
2474 IF (l_debug = 1) THEN
2475 mydebug(' checking if the lpn has multiple allocations ');
2476 END IF;
2477
2478 BEGIN
2479 SELECT 'Y'
2480 INTO l_multiple_rows
2481 FROM DUAL
2482 WHERE EXISTS(SELECT transaction_temp_id
2483 FROM mtl_material_transactions_temp
2484 WHERE transfer_lpn_id = l_transfer_lpn_id
2485 AND transaction_temp_id <> p_temp_id);
2486
2487 IF (l_debug = 1) THEN
2488 mydebug(' lpn has multiple allocations ' || l_multiple_rows);
2489 END IF;
2490 EXCEPTION
2491 WHEN NO_DATA_FOUND THEN
2492 IF (l_debug = 1) THEN
2493 mydebug(' lpn has single allocation ');
2494 END IF;
2495
2496 RETURN 'Y';
2497 END;
2498
2499 fnd_message.set_name('WMS', 'WMS_LPN_MULTIPLE_ALLOC_ERR');
2500 fnd_msg_pub.ADD;
2501 RETURN 'M';
2502 EXCEPTION
2503 WHEN fnd_api.g_exc_error THEN
2504 fnd_message.set_name('WMS', 'WMS_CAN_UNLOAD_ERROR');
2505 fnd_msg_pub.ADD;
2506 RETURN fnd_api.g_ret_sts_error;
2507 WHEN fnd_api.g_exc_unexpected_error THEN
2508 fnd_message.set_name('WMS', 'WMS_CAN_UNLOAD_ERROR');
2509 fnd_msg_pub.ADD;
2510 RETURN fnd_api.g_ret_sts_unexp_error;
2511 WHEN OTHERS THEN
2512 IF (l_debug = 1) THEN
2513 mydebug('Exception occurred in can_unload api' || SQLERRM);
2514 END IF;
2515
2516 fnd_message.set_name('WMS', 'WMS_CAN_UNLOAD_ERROR');
2517 fnd_msg_pub.ADD;
2518 RETURN fnd_api.g_ret_sts_unexp_error;
2519 END can_unload;
2520
2521 /* over loaded the procedure can_unload to resolve the JDBC error */
2522 PROCEDURE can_unload(x_can_unload out NOCOPY VARCHAR2, p_temp_id IN NUMBER)
2523 IS
2524 BEGIN
2525 x_can_unload := can_unload(p_temp_id);
2526 END;
2527
2528 END wms_task_utils_pvt;