[Home] [Help]
PACKAGE BODY: APPS.WMS_TASK_UTILS_PVT
Source
1 PACKAGE BODY wms_task_utils_pvt AS
2 /* $Header: WMSTSKUB.pls 120.8.12000000.2 2007/04/17 02:55:38 mchemban 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 mmtt_row.owning_organization_id := mmt_row.owning_organization_id;
1108 mmtt_row.owning_tp_type := mmt_row.owning_tp_type;
1109 mmtt_row.xfr_owning_organization_id := mmt_row.xfr_owning_organization_id;
1110 mmtt_row.transfer_owning_tp_type := mmt_row.transfer_owning_tp_type;
1111 mmtt_row.planning_organization_id := mmt_row.planning_organization_id;
1112 mmtt_row.planning_tp_type := mmt_row.planning_tp_type;
1113 mmtt_row.xfr_planning_organization_id := mmt_row.xfr_planning_organization_id;
1114 mmtt_row.transfer_planning_tp_type := mmt_row.transfer_planning_tp_type;
1115 mmtt_row.secondary_uom_code := mmt_row.secondary_uom_code;
1116 mmtt_row.secondary_transaction_quantity := mmt_row.secondary_transaction_quantity;
1117
1118 IF ( mmtt_row.primary_quantity < 0 ) THEN --Bug#5984021
1119 -- bug 4230494
1120 SELECT lpn_controlled_flag
1121 INTO l_lpn_control_flag
1122 FROM mtl_secondary_inventories
1123 WHERE organization_id = mmt_row.organization_id
1124 AND secondary_inventory_name = Nvl(mmt_row.transfer_subinventory, mmt_row.subinventory_code);
1125 ELSE
1126 SELECT lpn_controlled_flag
1127 INTO l_lpn_control_flag
1128 FROM mtl_secondary_inventories
1129 WHERE organization_id = mmt_row.organization_id
1130 AND secondary_inventory_name = Nvl(mmt_row.subinventory_code, mmt_row.transfer_subinventory);
1131 END IF;
1132
1133
1134 IF(l_lpn_control_flag = 1)THEN
1135 IF (l_debug = 1) THEN
1136 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. ');
1137 END IF;
1138
1139 mmtt_row.allocated_lpn_id := Nvl(mmt_row.content_lpn_id, mmt_row.transfer_lpn_id);
1140
1141 --Bug#5984021.If LPN is empty, no need of stamping it on MMTT.
1142 IF ( NVL(mmtt_row.allocated_lpn_id , 0 ) > 0 ) THEN
1143 SELECT wlpn.lpn_context INTO l_lpn_ctx
1144 FROM WMS_LICENSE_PLATE_NUMBERS wlpn
1145 WHERE wlpn.lpn_id = mmtt_row.allocated_lpn_id ;
1146
1147 IF (l_debug = 1) THEN
1148 mydebug('LPN id : '||mmtt_row.allocated_lpn_id ||', context:' || l_lpn_ctx );
1149 END IF;
1150
1151 IF ( l_lpn_ctx = WMS_Container_PUB.LPN_CONTEXT_PREGENERATED ) THEN
1152 mmtt_row.allocated_lpn_id := NULL ;
1153 IF (l_debug = 1) THEN
1154 mydebug('LPN has context 5, so null it out in MMTTT' );
1155 END IF;
1156 END IF;
1157 END IF;
1158 --Bug#5984021.End of fix.
1159 END IF;
1160 -- bug 4230494
1161
1162 mmtt_table(cnt) := mmtt_row;
1163 cnt := cnt + 1;
1164 mmtt_row := NULL;
1165 END LOOP;
1166
1167 IF (l_debug = 1) THEN
1168 mydebug('after creating mmtt_table');
1169 mydebug(' Item id ' || l_item_id);
1170 mydebug(' org id ' || l_org_id);
1171 END IF;
1172
1173 SELECT lot_control_code
1174 , serial_number_control_code
1175 INTO v_lot_control_code
1176 , v_serial_control_code
1177 FROM mtl_system_items
1178 WHERE inventory_item_id = l_item_id
1179 AND organization_id = l_org_id;
1180
1181 IF (l_debug = 1) THEN
1182 mydebug(' lot code ' || v_lot_control_code);
1183 mydebug(' ser code ' || v_serial_control_code);
1184 END IF;
1185
1186 SELECT allocate_serial_flag
1187 INTO v_allocate_serial_flag
1188 FROM mtl_parameters
1189 WHERE organization_id = l_org_id;
1190
1191 /*****LOT controlled only **********/
1192 cnt := 1;
1193
1194 IF (v_lot_control_code = 2) THEN
1195 OPEN mtln(p_set_id);
1196
1197 LOOP
1198 FETCH mtln INTO mtln_row;
1199 EXIT WHEN mtln%NOTFOUND;
1200 mtlt_row.transaction_temp_id := mtln_row.transaction_id;
1201 mtlt_row.last_update_date := mtln_row.last_update_date;
1202 mtlt_row.last_updated_by := mtln_row.last_updated_by;
1203 mtlt_row.creation_date := mtln_row.creation_date;
1204 mtlt_row.created_by := mtln_row.created_by;
1205 mtlt_row.last_update_login := mtln_row.last_update_login;
1206 --mtlt_row.INVENTORY_ITEM_ID := l_item_id;
1207 --mtlt_row.ORGANIZATION_ID := l_org_id;
1208 --mtlt_row.TRANSACTION_DATE := l_txn_date;
1209 --mtlt_row.transaction_source_id := l_txn_source_id;
1210 --mtlt_row.transaction_source_type_id := l_txn_source_type_id;
1211 --mtlt_row.TRANSACTION_SOURCE_NAME := l_txn_source_name;
1212
1213 mtlt_row.transaction_quantity := mtln_row.transaction_quantity;
1214 mtlt_row.primary_quantity := mtln_row.primary_quantity;
1215 mtlt_row.lot_number := mtln_row.lot_number;
1216 mtlt_row.serial_transaction_temp_id := mtln_row.serial_transaction_id;
1217 mtlt_row.description := mtln_row.description;
1218 mtlt_row.vendor_name := mtln_row.vendor_name;
1219 mtlt_row.supplier_lot_number := mtln_row.supplier_lot_number;
1220 mtlt_row.origination_date := mtln_row.origination_date;
1221 mtlt_row.date_code := mtln_row.date_code;
1222 mtlt_row.grade_code := mtln_row.grade_code;
1223 mtlt_row.change_date := mtln_row.change_date;
1224 mtlt_row.maturity_date := mtln_row.maturity_date;
1225 mtlt_row.status_id := mtln_row.status_id;
1226 mtlt_row.retest_date := mtln_row.retest_date;
1227 mtlt_row.age := mtln_row.age;
1228 mtlt_row.item_size := mtln_row.item_size;
1229 mtlt_row.color := mtln_row.color;
1230 mtlt_row.volume := mtln_row.volume;
1231 mtlt_row.volume_uom := mtln_row.volume_uom;
1232 mtlt_row.place_of_origin := mtln_row.place_of_origin;
1233 mtlt_row.best_by_date := mtln_row.best_by_date;
1234 mtlt_row.LENGTH := mtln_row.LENGTH;
1235 mtlt_row.length_uom := mtln_row.length_uom;
1236 mtlt_row.width := mtln_row.width;
1237 mtlt_row.width_uom := mtln_row.width_uom;
1238 mtlt_row.recycled_content := mtln_row.recycled_content;
1239 mtlt_row.thickness := mtln_row.thickness;
1240 mtlt_row.thickness_uom := mtln_row.thickness_uom;
1241 mtlt_row.curl_wrinkle_fold := mtln_row.curl_wrinkle_fold;
1242 mtlt_row.lot_attribute_category := mtln_row.lot_attribute_category;
1243 mtlt_row.c_attribute1 := mtln_row.c_attribute1;
1244 mtlt_row.c_attribute2 := mtln_row.c_attribute2;
1245 mtlt_row.c_attribute3 := mtln_row.c_attribute3;
1246 mtlt_row.c_attribute4 := mtln_row.c_attribute4;
1247 mtlt_row.c_attribute5 := mtln_row.c_attribute5;
1248 mtlt_row.c_attribute6 := mtln_row.c_attribute6;
1249 mtlt_row.c_attribute7 := mtln_row.c_attribute7;
1250 mtlt_row.c_attribute8 := mtln_row.c_attribute8;
1251 mtlt_row.c_attribute9 := mtln_row.c_attribute9;
1252 mtlt_row.c_attribute10 := mtln_row.c_attribute10;
1253 mtlt_row.c_attribute11 := mtln_row.c_attribute11;
1254 mtlt_row.c_attribute12 := mtln_row.c_attribute12;
1255 mtlt_row.c_attribute13 := mtln_row.c_attribute13;
1256 mtlt_row.c_attribute14 := mtln_row.c_attribute14;
1257 mtlt_row.c_attribute15 := mtln_row.c_attribute15;
1258 mtlt_row.c_attribute16 := mtln_row.c_attribute16;
1259 mtlt_row.c_attribute17 := mtln_row.c_attribute17;
1260 mtlt_row.c_attribute18 := mtln_row.c_attribute18;
1261 mtlt_row.c_attribute19 := mtln_row.c_attribute19;
1262 mtlt_row.c_attribute20 := mtln_row.c_attribute20;
1263 mtlt_row.d_attribute1 := mtln_row.d_attribute1;
1264 mtlt_row.d_attribute2 := mtln_row.d_attribute2;
1265 mtlt_row.d_attribute3 := mtln_row.d_attribute3;
1266 mtlt_row.d_attribute4 := mtln_row.d_attribute4;
1267 mtlt_row.d_attribute5 := mtln_row.d_attribute5;
1268 mtlt_row.d_attribute6 := mtln_row.d_attribute6;
1269 mtlt_row.d_attribute7 := mtln_row.d_attribute7;
1270 mtlt_row.d_attribute8 := mtln_row.d_attribute8;
1271 mtlt_row.d_attribute9 := mtln_row.d_attribute9;
1272 mtlt_row.d_attribute10 := mtln_row.d_attribute10;
1273 mtlt_row.n_attribute1 := mtln_row.n_attribute1;
1274 mtlt_row.n_attribute2 := mtln_row.n_attribute2;
1275 mtlt_row.n_attribute3 := mtln_row.n_attribute3;
1276 mtlt_row.n_attribute4 := mtln_row.n_attribute4;
1277 mtlt_row.n_attribute5 := mtln_row.n_attribute5;
1278 mtlt_row.n_attribute6 := mtln_row.n_attribute6;
1279 mtlt_row.n_attribute7 := mtln_row.n_attribute7;
1280 mtlt_row.n_attribute8 := mtln_row.n_attribute8;
1281 mtlt_row.n_attribute9 := mtln_row.n_attribute9;
1282 mtlt_row.n_attribute10 := mtln_row.n_attribute10;
1283 mtlt_row.vendor_id := mtln_row.vendor_id;
1284 mtlt_row.territory_code := mtln_row.territory_code;
1285 mtlt_table(cnt) := mtlt_row;
1286 cnt := cnt + 1;
1287 mtlt_row := NULL;
1288 END LOOP;
1289
1290 IF (l_debug = 1) THEN
1291 mydebug('after creating mtlt_table');
1292 END IF;
1293 END IF;
1294 /********* serial Controlled **************/
1295 IF((v_serial_control_code NOT IN(1, 6))
1296 AND(v_lot_control_code IN(1, 2))) THEN
1297
1298 cnt := 1;
1299
1300 /**2706001 conditionally opening cursors **/
1301 IF(v_lot_control_code = 1 AND v_serial_control_code NOT IN
1302 (1,6) ) THEN
1303 OPEN mut1(p_set_id);
1304 ELSIF(v_lot_control_code = 2 AND v_serial_control_code NOT IN
1305 (1,6)) THEN
1306 OPEN mut2(p_set_id);
1307 END IF;
1308
1309
1310 LOOP
1311 IF (v_lot_control_code = 1
1312 AND v_serial_control_code NOT IN(1, 6)) THEN
1313 FETCH mut1 INTO mut_row;
1314 EXIT WHEN mut1%NOTFOUND;
1315 ELSIF(v_lot_control_code = 2
1316 AND v_serial_control_code NOT IN(1, 6)) THEN
1317 FETCH mut2 INTO mut_row;
1318 /**2706001 earlier mut1%notfound **/
1319 EXIT WHEN mut2%NOTFOUND;
1320 ELSE
1321 EXIT;
1322 END IF;
1323
1324 msnt_row.transaction_temp_id := mut_row.transaction_id;
1325 msnt_row.last_update_date := mut_row.last_update_date;
1326 msnt_row.last_updated_by := mut_row.last_updated_by;
1327 msnt_row.creation_date := mut_row.creation_date;
1328 msnt_row.created_by := mut_row.created_by;
1329 msnt_row.last_update_login := mut_row.last_update_login;
1330 msnt_row.fm_serial_number := mut_row.serial_number;
1331 msnt_row.to_serial_number := mut_row.serial_number;
1332 --msnt_row.INVENTORY_ITEM_ID := l_item_id;
1333 --msnt_row.ORGANIZATION_ID := l_org_id;
1334 --msnt_row.SUBINVENTORY_CODE := l_sub_code;
1335 --msnt_row.LOCATOR_ID := l_loc_id;
1336 --msnt_row.TRANSACTION_DATE := l_txn_date;
1337 --msnt_row.TRANSACTION_SOURCE_ID := l_txn_source_id;
1338 --msnt_row.transaction_source_type_id := l_txn_source_type_id;
1339 --msnt_row.TRANSACTION_SOURCE_NAME := l_txn_source_name;
1340 --msnt_row.RECEIPT_ISSUE_TYPE := mut_row.;
1341 --msnt_row.CUSTOMER_ID := mut_row.;
1342 --msnt_row.SHIP_ID := mut_row.;
1343 msnt_row.serial_attribute_category := mut_row.serial_attribute_category;
1344 msnt_row.origination_date := mut_row.origination_date;
1345 msnt_row.c_attribute1 := mut_row.c_attribute1;
1346 msnt_row.c_attribute2 := mut_row.c_attribute2;
1347 msnt_row.c_attribute3 := mut_row.c_attribute3;
1348 msnt_row.c_attribute4 := mut_row.c_attribute4;
1349 msnt_row.c_attribute5 := mut_row.c_attribute5;
1350 msnt_row.c_attribute6 := mut_row.c_attribute6;
1351 msnt_row.c_attribute7 := mut_row.c_attribute7;
1352 msnt_row.c_attribute8 := mut_row.c_attribute8;
1353 msnt_row.c_attribute9 := mut_row.c_attribute9;
1354 msnt_row.c_attribute10 := mut_row.c_attribute10;
1355 msnt_row.c_attribute11 := mut_row.c_attribute11;
1356 msnt_row.c_attribute12 := mut_row.c_attribute12;
1357 msnt_row.c_attribute13 := mut_row.c_attribute13;
1358 msnt_row.c_attribute14 := mut_row.c_attribute14;
1359 msnt_row.c_attribute15 := mut_row.c_attribute15;
1360 msnt_row.c_attribute16 := mut_row.c_attribute16;
1361 msnt_row.c_attribute17 := mut_row.c_attribute17;
1362 msnt_row.c_attribute18 := mut_row.c_attribute18;
1363 msnt_row.c_attribute19 := mut_row.c_attribute19;
1364 msnt_row.c_attribute20 := mut_row.c_attribute20;
1365 msnt_row.d_attribute1 := mut_row.d_attribute1;
1366 msnt_row.d_attribute2 := mut_row.d_attribute2;
1367 msnt_row.d_attribute3 := mut_row.d_attribute3;
1368 msnt_row.d_attribute4 := mut_row.d_attribute4;
1369 msnt_row.d_attribute5 := mut_row.d_attribute5;
1370 msnt_row.d_attribute6 := mut_row.d_attribute6;
1371 msnt_row.d_attribute7 := mut_row.d_attribute7;
1372 msnt_row.d_attribute8 := mut_row.d_attribute8;
1373 msnt_row.d_attribute9 := mut_row.d_attribute9;
1374 msnt_row.d_attribute10 := mut_row.d_attribute10;
1375 msnt_row.n_attribute1 := mut_row.n_attribute1;
1376 msnt_row.n_attribute2 := mut_row.n_attribute2;
1377 msnt_row.n_attribute3 := mut_row.n_attribute3;
1378 msnt_row.n_attribute4 := mut_row.n_attribute4;
1379 msnt_row.n_attribute5 := mut_row.n_attribute5;
1380 msnt_row.n_attribute6 := mut_row.n_attribute6;
1381 msnt_row.n_attribute7 := mut_row.n_attribute7;
1382 msnt_row.n_attribute8 := mut_row.n_attribute8;
1383 msnt_row.n_attribute9 := mut_row.n_attribute9;
1384 msnt_row.n_attribute10 := mut_row.n_attribute10;
1385 msnt_row.status_id := mut_row.status_id;
1386 msnt_row.territory_code := mut_row.territory_code;
1387 msnt_row.time_since_new := mut_row.time_since_new;
1388 msnt_row.cycles_since_new := mut_row.cycles_since_new;
1389 msnt_row.time_since_overhaul := mut_row.time_since_overhaul;
1390 msnt_row.cycles_since_overhaul := mut_row.cycles_since_overhaul;
1391 msnt_row.time_since_repair := mut_row.time_since_repair;
1392 msnt_row.cycles_since_repair := mut_row.cycles_since_repair;
1393 msnt_row.time_since_visit := mut_row.time_since_visit;
1394 msnt_row.cycles_since_visit := mut_row.cycles_since_visit;
1395 msnt_row.time_since_mark := mut_row.time_since_mark;
1396 msnt_row.cycles_since_mark := mut_row.cycles_since_mark;
1397 msnt_row.number_of_repairs := mut_row.number_of_repairs;
1398 msnt_table(cnt) := msnt_row;
1399 cnt := cnt + 1;
1400 msnt_row := NULL;
1401 END LOOP;
1402
1403 IF (l_debug = 1) THEN
1404 mydebug('after creating msnt_table');
1405 END IF;
1406 END IF;
1407
1408 x_mmtt := mmtt_table;
1409 x_mtlt := mtlt_table;
1410 x_msnt := msnt_table;
1411
1412 IF (l_debug = 1) THEN
1413 mydebug('end of get_temp_tables');
1414 END IF;
1415 END get_temp_tables;
1416
1417 PROCEDURE generate_next_task(
1418 x_return_status OUT NOCOPY VARCHAR2
1419 , x_msg_count OUT NOCOPY NUMBER
1420 , x_msg_data OUT NOCOPY VARCHAR2
1421 , x_ret_code OUT NOCOPY VARCHAR2
1422 , p_old_header_id IN NUMBER
1423 , p_mo_line_id IN NUMBER
1424 , p_old_sub_code IN VARCHAR2
1425 , p_old_loc_id IN NUMBER
1426 , p_wms_task_type IN NUMBER
1427 ) IS
1428 l_api_name CONSTANT VARCHAR2(30) := 'GENERATE_NEXT_TASK';
1429 l_api_version CONSTANT NUMBER := 1.0;
1430 mmtt_table mmtt_tb;
1431 mmtt_row mmtt_type;
1432 lot_row mtlt_type;
1433 mtlt_table mtlt_tb;
1434 ser_row msnt_type;
1435 msnt_table msnt_tb;
1436 mmt_row mmtt_type;
1437 mtln_row mtln_type;
1438 mut_row mut_type;
1439 cnt NUMBER := 0;
1440 new_txn_temp_id NUMBER;
1441 new_txn_header_id NUMBER;
1442 ser_transaction_temp_id NUMBER;
1443 v_rev_control_code NUMBER := -1;
1444 v_lot_control_code NUMBER := -1;
1445 v_serial_control_code NUMBER := -1;
1446 v_allocate_serial_flag VARCHAR2(1) := 'X';
1447 l_rev_ctrl VARCHAR2(1) := 'N';
1448 l_alloc_ser VARCHAR2(1) := 'N';
1449 --Bug 2561167 fix
1450 l_crossdocked VARCHAR2(1) := 'N';
1451 l_already_used VARCHAR2(1) := 'N';
1452 --BUG 2698630 fix
1453 l_trohdr_rec INV_Move_Order_PUB.Trohdr_Rec_Type;
1454 l_trolin_tbl INV_Move_Order_PUB.Trolin_Tbl_Type;
1455 l_trolin_val_tbl INV_Move_Order_PUB.Trolin_Val_Tbl_Type;
1456 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
1457 l_msg_count NUMBER;
1458 l_msg_data VARCHAR2(2000);
1459 l_line_num Number := 0;
1460 l_uom VARCHAR2(60);
1461 l_trohdr_val_rec INV_Move_Order_PUB.Trohdr_Val_Rec_Type;
1462 l_ref VARCHAR2(240);
1463 l_ref_type NUMBER;
1464 l_ref_id NUMBER;
1465 l_req_msg VARCHAR2(30) := NULL;
1466 --BUG 2698630 fix
1467
1468 CURSOR mtlt(txn_tmp_id NUMBER) IS
1469 SELECT *
1470 FROM mtl_transaction_lots_temp
1471 WHERE transaction_temp_id = txn_tmp_id;
1472
1473 CURSOR msnt(txn_tmp_id NUMBER) IS
1474 SELECT *
1475 FROM mtl_serial_numbers_temp
1476 WHERE transaction_temp_id = txn_tmp_id;
1477
1478 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
1479 BEGIN
1480 SAVEPOINT generate_next_task;
1481 x_ret_code := fnd_api.g_ret_sts_success;
1482
1483 IF (l_debug = 1) THEN
1484 mydebug('In generate_next_task');
1485 END IF;
1486
1487 IF (l_debug = 1) THEN
1488 mydebug(' p_old_header_id is ' || p_old_header_id);
1489 END IF;
1490
1491 IF (l_debug = 1) THEN
1492 mydebug(' p_mo_line_id is ' || p_mo_line_id);
1493 END IF;
1494
1495 IF (l_debug = 1) THEN
1496 mydebug(' p_old_sub_CODE is ' || p_old_sub_code);
1497 END IF;
1498
1499 IF (l_debug = 1) THEN
1500 mydebug(' p_old_loc_id is ' || p_old_loc_id);
1501 END IF;
1502
1503 IF (l_debug = 1) THEN
1504 mydebug(' p_wms_task_type is ' || p_wms_task_type);
1505 END IF;
1506
1507 --Bug 2561167 fix
1508
1509 IF (p_wms_task_type = 2 OR p_wms_task_type = -1
1510 OR p_wms_task_type = 5 -- bug fix 4230494
1511 ) THEN
1512 --2697301 fix earlier doing p_wms_task_type = 6
1513 IF (l_debug = 1) THEN
1514 mydebug('Putaway task');
1515 END IF;
1516
1517 l_crossdocked := 'N';
1518
1519 BEGIN
1520 SELECT 'Y'
1521 INTO l_crossdocked
1522 FROM DUAL
1523 WHERE EXISTS(
1524 SELECT mtrl.line_id
1525 FROM mtl_txn_request_lines mtrl, mtl_material_transactions mmt
1526 WHERE mtrl.line_id = mmt.move_order_line_id
1527 AND mtrl.backorder_delivery_detail_id IS NOT NULL
1528 AND mmt.transaction_set_id = p_old_header_id);
1529 EXCEPTION
1530 WHEN OTHERS THEN
1531 l_crossdocked := 'N';
1532
1533 IF (l_debug = 1) THEN
1534 mydebug('Not cross docked');
1535 END IF;
1536 END;
1537
1538 IF l_crossdocked = 'Y' THEN
1539 IF (l_debug = 1) THEN
1540 mydebug('crossdocked - so dont generate next task');
1541 END IF;
1542
1543 RETURN;
1544 END IF;
1545 ELSIF p_wms_task_type = 4 THEN
1546 IF (l_debug = 1) THEN
1547 mydebug('Replenishment task - ok ');
1548 END IF;
1549 ELSE
1550 IF (l_debug = 1) THEN
1551 mydebug('not repl or putaway task - so dont generate next task');
1552 END IF;
1553
1554 RETURN;
1555 END IF;
1556
1557 --Bug 2561167 fix
1558
1559
1560
1561 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);
1562
1563 IF (l_debug = 1) THEN
1564 mydebug('After calling get_temp_tables ');
1565 END IF;
1566
1567 SELECT mtl_material_transactions_s.NEXTVAL
1568 INTO new_txn_header_id
1569 FROM DUAL;
1570
1571 IF (l_debug = 1) THEN
1572 mydebug('New Txn Hdr id is ' || new_txn_header_id);
1573 END IF;
1574
1575 /* IF mmtt_table.COUNT > 1 THEN
1576
1577 IF (l_debug = 1) THEN
1578 mydebug('ERROR - Number of rows for this header are more than one ');
1579 mydebug('Raising an unexpected error ');
1580 END IF;
1581 RAISE fnd_api.g_exc_unexpected_error;
1582
1583 END IF;
1584 */
1585 FOR cnt IN 1 .. mmtt_table.COUNT LOOP
1586 mmtt_row := mmtt_table(cnt);
1587
1588 IF ((mmtt_row.subinventory_code = p_old_sub_code)
1589 AND(mmtt_row.locator_id = p_old_loc_id)) THEN
1590 IF (l_debug = 1) THEN
1591 mydebug(' source and destination sub location is same ');
1592 mydebug(' hence new task is not created ');
1593 END IF;
1594 ELSIF mmtt_row.primary_quantity <= 0 THEN
1595 IF (l_debug = 1) THEN
1596 mydebug(' ignoring the Replenishment task with negative quntity ');
1597 END IF;
1598
1599 -- We should always skip the negative quantity one, whenever two
1600 -- txns are created for the putaway( in case of a po receipt
1601 -- only one txn gets submitted)
1602 -- For a replenishment tasks, two rows will be created in MMT
1603 -- one with a positive quantity corresponding to the movement of
1604 -- material to the destination
1605 -- one with negative quantity corresponding to the issue of
1606 -- material from the source location
1607 -- so ignoring te negative line here , +ve line is picked up in
1608 -- the else next
1609
1610 IF (l_debug = 1) THEN
1611 mydebug(' ignoring the Replenishment task with negative quntity ');
1612 END IF;
1613 ELSIF mmtt_row.transaction_action_id = 50 THEN
1614 --Bug 2561167 fix
1615 IF (l_debug = 1) THEN
1616 mydebug(' ignoring the pack transaction ');
1617 END IF;
1618 --Bug 2561167 fix
1619 ELSE
1620 IF (l_debug = 1) THEN
1621 mydebug(' source and destination sub location are not same ');
1622 END IF;
1623
1624 SELECT mtl_material_transactions_s.NEXTVAL
1625 INTO new_txn_temp_id
1626 FROM DUAL;
1627
1628 IF (l_debug = 1) THEN
1629 mydebug('New Txn Temp id is ' || new_txn_temp_id);
1630 END IF;
1631
1632 IF (l_debug = 1) THEN
1633 mydebug('updating the new task ');
1634 END IF;
1635
1636 --mmtt_row.transaction_temp_id := new_txn_temp_id;
1637 mmtt_row.transaction_header_id := new_txn_header_id;
1638 -- Always the second task is a replenishment task
1639
1640 mmtt_row.wms_task_type := 4;
1641 mmtt_row.move_order_line_id := p_mo_line_id;
1642 mmtt_row.transaction_source_type_id := inv_globals.g_sourcetype_moveorder; -- bug 4230494
1643 mmtt_row.transaction_type_id := 64; -- bug 4230494 move order xfer
1644 mmtt_row.transaction_action_id := inv_globals.g_action_subxfr;
1645 mmtt_row.process_flag := 'Y';
1646 mmtt_row.transaction_status := 2;
1647 mmtt_row.transfer_subinventory := p_old_sub_code;
1648 mmtt_row.transfer_to_location := p_old_loc_id;
1649 mmtt_row.posting_flag := 'Y';
1650
1651 IF (l_debug = 1) THEN
1652 mydebug(' mmtt_row.wms_task_type ' || mmtt_row.wms_task_type);
1653 mydebug(' mmtt_row.mmtt_row.move_order_line_id ' || mmtt_row.move_order_line_id);
1654 mydebug(' mmtt_row.transaction_source_type_id ' || mmtt_row.transaction_source_type_id);
1655 mydebug(' mmtt_row.transaction_type_id ' || mmtt_row.transaction_type_id);
1656 mydebug(' mmtt_row.transaction_action_id ' || mmtt_row.transaction_action_id);
1657 mydebug(' mmtt_row.process_flag ' || mmtt_row.process_flag);
1658 mydebug(' mmtt_row.transaction_status ' || mmtt_row.transaction_status);
1659 mydebug(' mmtt_row.transfer_subinventory ' || mmtt_row.transfer_subinventory);
1660 mydebug(' mmtt_row.transfer_to_location ' || mmtt_row.transfer_to_location);
1661 mydebug(' mmtt_row.primary_quantity ' || mmtt_row.primary_quantity);
1662 mydebug(' mmtt_row.transaction_quantity ' || mmtt_row.transaction_quantity);
1663 mydebug('sub ' || mmtt_row.subinventory_code);
1664 mydebug('loc ' || mmtt_row.locator_id);
1665 mydebug('t sub ' || mmtt_row.transfer_subinventory);
1666 mydebug('t loc ' || mmtt_row.transfer_to_location);
1667 END IF;
1668
1669 BEGIN
1670 SELECT revision_qty_control_code
1671 , lot_control_code
1672 , serial_number_control_code
1673 , primary_uom_code
1674 INTO v_rev_control_code
1675 , v_lot_control_code
1676 , v_serial_control_code
1677 , l_uom
1678 FROM mtl_system_items
1679 WHERE inventory_item_id = mmtt_table(cnt).inventory_item_id
1680 AND organization_id = mmtt_table(cnt).organization_id;
1681 EXCEPTION
1682 WHEN OTHERS THEN
1683 mydebug('Exception getting the item information');
1684 RAISE fnd_api.g_exc_unexpected_error;
1685 END;
1686
1687 /***** Bug 2999296 Updating locator capacity ***********/
1688
1689 mydebug('Updating locator capacity of loc '||mmtt_row.transfer_to_location);
1690
1691 inv_loc_wms_utils.update_loc_sugg_capacity_nauto
1692 ( x_return_status => l_return_status
1693 , x_msg_count => l_msg_count
1694 , x_msg_data => l_msg_data
1695 , p_organization_id => mmtt_row.organization_id
1696 , p_inventory_location_id => mmtt_row.transfer_to_location
1697 , p_inventory_item_id => mmtt_row.inventory_item_id
1698 , p_primary_uom_flag => 'Y'
1699 , p_transaction_uom_code => NULL
1700 , p_quantity => mmtt_row.primary_quantity
1701 );
1702
1703 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1704 mydebug('Unexpected error in update_loc_suggested_capacity');
1705 -- Bug 5393727: do not raise an exception if revert API returns an error
1706 -- RAISE fnd_api.g_exc_unexpected_error;
1707 ELSIF x_return_status = fnd_api.g_ret_sts_error THEN
1708 mydebug('Error in update_loc_suggested_capacity');
1709 -- Bug 5393727: do not raise an exception if revert API returns an error
1710 -- RAISE fnd_api.g_exc_error;
1711 END IF;
1712
1713 /***** Bug 2999296 Updating locator capacity ***********/
1714
1715 /******* BUG 2698630 fix creating move order*/
1716
1717 BEGIN
1718 SELECT reference,reference_type_code,reference_id
1719 INTO l_ref,l_ref_type, l_ref_id
1720 FROM mtl_txn_request_lines
1721 WHERE
1722 line_id = mmtt_row.move_order_line_id;
1723 EXCEPTION
1724 WHEN others THEN
1725 mydebug('Exception getting the move order line information');
1726 RAISE fnd_api.g_exc_unexpected_error;
1727 END;
1728
1729 l_trohdr_rec.request_number := FND_API.G_MISS_CHAR ; --5984021
1730 l_trohdr_rec.header_id := FND_API.G_MISS_NUM;
1731 l_trohdr_rec.created_by := FND_GLOBAL.USER_ID;
1732 l_trohdr_rec.creation_date := sysdate;
1733 l_trohdr_rec.date_required := sysdate;
1734 l_trohdr_rec.from_subinventory_code := mmtt_row.subinventory_code;
1735 l_trohdr_rec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
1736 l_trohdr_rec.last_updated_by := FND_GLOBAL.USER_ID;
1737 l_trohdr_rec.last_update_date := sysdate;
1738 l_trohdr_rec.last_update_login := FND_GLOBAL.USER_ID;
1739 l_trohdr_rec.organization_id := mmtt_row.organization_id;
1740 l_trohdr_rec.status_date := sysdate;
1741 l_trohdr_rec.to_subinventory_code := mmtt_row.transfer_subinventory;
1742 l_trohdr_rec. move_order_type := INV_GLOBALS.g_move_order_replenishment;
1743 l_trohdr_rec.db_flag := FND_API.G_TRUE;
1744 l_trohdr_rec.operation := INV_GLOBALS.G_OPR_CREATE;
1745
1746 l_line_num := 1;
1747 l_trolin_tbl(1).header_id := l_trohdr_rec.header_id;
1748 l_trolin_tbl(1).created_by := FND_GLOBAL.USER_ID;
1749 l_trolin_tbl(1).creation_date := sysdate;
1750 l_trolin_tbl(1).date_required := sysdate;
1751 l_trolin_tbl(1).from_subinventory_code := mmtt_row.subinventory_code;
1752 l_trolin_tbl(1).from_locator_id := mmtt_row.locator_id;
1753 l_trolin_tbl(1).inventory_item_id := mmtt_row.inventory_item_id;
1754 l_trolin_tbl(1).last_updated_by := FND_GLOBAL.USER_ID;
1755 l_trolin_tbl(1).last_update_date := sysdate;
1756 l_trolin_tbl(1).last_updated_by := FND_GLOBAL.USER_ID;
1757 l_trolin_tbl(1).last_update_date := sysdate;
1758 l_trolin_tbl(1).last_update_login := FND_GLOBAL.LOGIN_ID;
1759 l_trolin_tbl(1).line_id := FND_API.G_MISS_NUM;
1760 l_trolin_tbl(1).line_number := l_line_num;
1761 l_trolin_tbl(1).line_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
1762 l_trolin_tbl(1).organization_id := mmtt_row.organization_id;
1763 l_trolin_tbl(1).quantity := mmtt_row.primary_quantity;
1764 l_trolin_tbl(1).quantity_detailed := mmtt_row.primary_quantity;
1765 --Bug 4593622 stamping mmtt_row.primary_quantity as quantity_detailed
1766
1767 l_trolin_tbl(1).status_date := sysdate;
1768 l_trolin_tbl(1).to_subinventory_code := mmtt_row.transfer_subinventory;
1769 l_trolin_tbl(1).uom_code := l_uom;
1770 l_trolin_tbl(1).db_flag := FND_API.G_TRUE;
1771 l_trolin_tbl(1).operation := INV_GLOBALS.G_OPR_CREATE;
1772
1773 l_trolin_tbl(1).lpn_id := NULL;
1774 l_trolin_tbl(1).reference:=l_ref;
1775 l_trolin_tbl(1).reference_type_code:=l_ref_type;
1776 l_trolin_tbl(1).reference_id:=l_ref_id;
1777 l_trolin_tbl(1).project_id:=NULL;
1778 l_trolin_tbl(1).task_id:=NULL;
1779 l_trolin_tbl(1).lot_number:=NULL;
1780 l_trolin_tbl(1).revision:=mmtt_row.revision;
1781 l_trolin_tbl(1).transaction_type_id:=mmtt_row.transaction_type_id;
1782 l_trolin_tbl(1).transaction_source_type_id:=mmtt_row.transaction_source_type_id;
1783 l_trolin_tbl(1).inspection_status:=NULL;
1784 l_trolin_tbl(1).wms_process_flag:=NULL;
1785 l_trolin_tbl(1).to_organization_id:=mmtt_row.transfer_organization;
1786 l_trolin_tbl(1).txn_source_id:=mmtt_row.transaction_source_id;
1787 l_trolin_tbl(1).from_cost_group_id:=mmtt_row.cost_group_id;
1788 l_trolin_tbl(1).to_cost_group_id:=mmtt_row.transfer_cost_group_id;
1789
1790 INV_Move_Order_PUB.Process_Move_Order
1791 ( p_api_version_number => 1.0 ,
1792 p_init_msg_list => 'F',
1793 p_commit => FND_API.G_FALSE,
1794 x_return_status => l_return_status,
1795 x_msg_count => l_msg_count,
1796 x_msg_data => l_msg_data,
1797 p_trohdr_rec => l_trohdr_rec,
1798 p_trohdr_val_rec => l_trohdr_val_rec,
1799 p_trolin_tbl => l_trolin_tbl,
1800 p_trolin_val_tbl => l_trolin_val_tbl,
1801 x_trohdr_rec => l_trohdr_rec,
1802 x_trohdr_val_rec => l_trohdr_val_rec,
1803 x_trolin_tbl => l_trolin_tbl,
1804 x_trolin_val_tbl => l_trolin_val_tbl
1805 );
1806
1807
1808 fnd_msg_pub.count_and_get
1809 ( p_count => l_msg_count
1810 , p_data => l_msg_data
1811 );
1812 IF (l_msg_count = 0) THEN
1813 IF (l_debug = 1) THEN
1814 mydebug('create_mo: Successful');
1815 END IF;
1816 ELSIF (l_msg_count = 1) THEN
1817 IF (l_debug = 1) THEN
1818 mydebug('create_mo: Not Successful');
1819 mydebug('create_mo: ' || replace(l_msg_data,fnd_global.local_chr(0),' '));
1820 END IF;
1821 ELSE
1822 IF (l_debug = 1) THEN
1823 mydebug('create_mo: Not Successful2');
1824 END IF;
1825 For I in 1..l_msg_count LOOP
1826 l_msg_data := fnd_msg_pub.get(I,'F');
1827 IF (l_debug = 1) THEN
1828 mydebug('create_mo: ' || replace(l_msg_data,fnd_global.local_chr(0),' '));
1829 END IF;
1830 END LOOP;
1831 END IF;
1832
1833
1834 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1835 FND_MESSAGE.SET_NAME('WMS','WMS_TD_MO_ERROR' );
1836 FND_MSG_PUB.ADD;
1837 RAISE FND_API.g_exc_unexpected_error;
1838
1839 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1840 FND_MESSAGE.SET_NAME('WMS','WMS_TD_MO_ERROR');
1841 FND_MSG_PUB.ADD;
1842 RAISE FND_API.G_EXC_ERROR;
1843 END IF;
1844
1845
1846 /* Get header and line ids */
1847 IF (l_debug = 1) THEN
1848 mydebug('create_mo: Header'||l_trohdr_rec.header_id);
1849 mydebug('create_mo: line'||l_trolin_tbl(1).line_id);
1850 mydebug('create_mo: ' || l_trolin_tbl(1).organization_id);
1851 END IF;
1852
1853 mmtt_row.move_order_line_id := l_trolin_tbl(1).line_id;
1854 mmtt_row.trx_source_line_id := l_trolin_tbl(1).line_id; -- bug 4230494
1855 /******* BUG 2698630 fix creating move order*/
1856
1857 /*************NOW Updating MTLT and MSNT *************************/
1858
1859
1860 IF v_rev_control_code = 2 THEN
1861 l_rev_ctrl := 'Y';
1862 ELSE
1863 l_rev_ctrl := 'N';
1864 END IF;
1865
1866 SELECT allocate_serial_flag
1867 INTO v_allocate_serial_flag
1868 FROM mtl_parameters
1869 WHERE organization_id = mmtt_table(cnt).organization_id;
1870
1871 IF v_allocate_serial_flag = 'Y' THEN
1872 l_alloc_ser := 'Y';
1873 ELSE
1874 l_alloc_ser := 'N';
1875 END IF;
1876
1877 /*****LOT controlled only **********/
1878 IF (v_lot_control_code = 2
1879 AND v_serial_control_code IN(1, 6)) THEN
1880 IF (l_debug = 1) THEN
1881 mydebug(' LOT controlled only ');
1882 END IF;
1883
1884 FOR cnt2 IN 1 .. mtlt_table.COUNT LOOP
1885 lot_row := mtlt_table(cnt2);
1886
1887 IF lot_row.transaction_temp_id = mmtt_row.transaction_temp_id THEN
1888 IF (l_debug = 1) THEN
1889 mydebug('child row with temp id ' || lot_row.transaction_temp_id);
1890 END IF;
1891
1892 IF NOT check_qty_avail(
1893 mmtt_row => mmtt_row
1894 , lot_row => lot_row
1895 , ser_row => NULL
1896 , p_is_revision_control => l_rev_ctrl
1897 , p_is_lot_control => 'Y'
1898 , p_is_serial_control => 'N'
1899 , p_allocate_serial_flag => l_alloc_ser
1900 ) THEN
1901 IF (l_debug = 1) THEN
1902 mydebug('failed quantity check ');
1903 END IF;
1904
1905 RAISE g_qty_not_avail;
1906 END IF;
1907
1908 lot_row.transaction_temp_id := new_txn_temp_id;
1909 inv_rcv_common_apis.insert_mtlt(lot_row);
1910 lot_row := NULL;
1911 END IF;
1912 END LOOP;
1913 /********* serial Controlled only **************/
1914 ELSIF(v_lot_control_code = 1
1915 AND v_serial_control_code NOT IN(1, 6)) THEN
1916 IF (l_debug = 1) THEN
1917 mydebug(' Serial controlled only ');
1918 END IF;
1919
1920 IF (v_allocate_serial_flag = 'Y') THEN
1921 IF (l_debug = 1) THEN
1922 mydebug(' allocate_serial_flag is Y ');
1923 END IF;
1924 /**2706001 checking the avail outside loop **/
1925 IF NOT check_qty_avail(mmtt_row => mmtt_row,
1926 lot_row => null,
1927 ser_row => null,
1928 p_is_revision_control => l_rev_ctrl,
1929 p_is_lot_control => 'N',
1930 p_is_serial_control => 'Y',
1931 p_allocate_serial_flag => l_alloc_ser) THEN
1932 mydebug('failed quantity check ');
1933 RAISE g_qty_not_avail;
1934 END IF;
1935
1936 FOR cnt3 IN 1 .. msnt_table.COUNT LOOP
1937 ser_row := msnt_table(cnt3);
1938
1939 IF ser_row.transaction_temp_id = mmtt_row.transaction_temp_id THEN
1940 IF (l_debug = 1) THEN
1941 mydebug('child row with temp id ' || ser_row.transaction_temp_id);
1942 END IF;
1943
1944 /**2706001 checking group mark ids here **/
1945 l_already_used := 'N';
1946 BEGIN
1947 SELECT 'Y' INTO l_already_used FROM dual WHERE exists
1948 (SELECT 1
1949 FROM mtl_serial_numbers
1950 WHERE
1951 --Bug 2940878 fix added current_organization_id ,
1952 --inventory_item_id in the query
1953 -- also changed the condition on group_mark_id
1954 current_organization_id = mmtt_row.organization_id AND
1955 inventory_item_id = mmtt_row.inventory_item_id AND
1956 serial_number >= ser_row.fm_serial_number AND
1957 serial_number <= ser_row.to_serial_number AND
1958 --group_mark_id IS NOT NULL
1959 Nvl(group_mark_id, -1) <> -1
1960 );
1961 EXCEPTION
1962 WHEN no_data_found THEN
1963 l_already_used := 'N';
1964 WHEN OTHERS THEN
1965 mydebug('Error occurred '||Sqlerrm);
1966 l_already_used := NULL;
1967 RAISE fnd_api.g_exc_unexpected_error;
1968 END;
1969
1970 IF l_already_used = 'Y' then
1971 mydebug('failed quantity check ');
1972 RAISE g_qty_not_avail;
1973 END IF;
1974 /**2706001 checking group mark ids here **/
1975 ser_row.transaction_temp_id := new_txn_temp_id;
1976 inv_rcv_common_apis.insert_msnt(ser_row);
1977 ser_row := NULL;
1978 END IF;
1979 END LOOP;
1980 END IF;
1981 /********* LOT and serial Controlled **************/
1982 ELSIF(v_lot_control_code = 2
1983 AND v_serial_control_code NOT IN(1, 6)) THEN
1984 IF (l_debug = 1) THEN
1985 mydebug(' Both lot and Serial controlled ');
1986 END IF;
1987
1988 IF (v_allocate_serial_flag = 'N') THEN
1989 /*******************same as LOT CONTROLLED ONLY***********/
1990 FOR cnt4 IN 1 .. mtlt_table.COUNT LOOP
1991 lot_row := mtlt_table(cnt4);
1992
1993 IF lot_row.transaction_temp_id = mmtt_row.transaction_temp_id THEN
1994 IF (l_debug = 1) THEN
1995 mydebug('child row with temp id ' || lot_row.transaction_temp_id);
1996 END IF;
1997
1998 IF NOT check_qty_avail(
1999 mmtt_row => mmtt_row
2000 , lot_row => lot_row
2001 , ser_row => NULL
2002 , p_is_revision_control => l_rev_ctrl
2003 , p_is_lot_control => 'Y'
2004 , p_is_serial_control => 'Y'
2005 , p_allocate_serial_flag => l_alloc_ser
2006 ) THEN
2007 IF (l_debug = 1) THEN
2008 mydebug('failed quantity check ');
2009 END IF;
2010
2011 RAISE g_qty_not_avail;
2012 END IF;
2013
2014 lot_row.serial_transaction_temp_id := NULL;
2015 lot_row.transaction_temp_id := new_txn_temp_id;
2016 inv_rcv_common_apis.insert_mtlt(lot_row);
2017 lot_row := NULL;
2018 END IF;
2019 END LOOP;
2020 --END IF;
2021 ELSE
2022 /*Need to insert both lot and serial tables*/
2023 IF (l_debug = 1) THEN
2024 mydebug(' allocate_serial_flag is Y ');
2025 END IF;
2026
2027 FOR cnt5 IN 1 .. mtlt_table.COUNT LOOP
2028 lot_row := mtlt_table(cnt5);
2029
2030 /***********Serial Stuff *****************************/
2031 IF lot_row.transaction_temp_id = mmtt_row.transaction_temp_id THEN
2032 IF (l_debug = 1) THEN
2033 mydebug('child lot row with temp id ' || lot_row.transaction_temp_id);
2034 END IF;
2035 /**2706001 checking avail qty outside loop **/
2036 IF NOT check_qty_avail(mmtt_row => mmtt_row,
2037 lot_row => lot_row,
2038 ser_row => null,
2039 p_is_revision_control => l_rev_ctrl,
2040 p_is_lot_control => 'Y',
2041 p_is_serial_control => 'Y',
2042 p_allocate_serial_flag => l_alloc_ser) THEN
2043 mydebug('failed quantity check ');
2044 RAISE g_qty_not_avail;
2045 END IF;
2046
2047 /**2706001 moved this out of below loop **/
2048 SELECT mtl_material_transactions_s.NEXTVAL
2049 INTO ser_transaction_temp_id
2050 FROM dual;
2051
2052 /**2706001 was using cnt6 earlier **/
2053 FOR cnt6 IN 1 .. msnt_table.COUNT LOOP
2054 ser_row := msnt_table(cnt6);
2055
2056 IF ser_row.transaction_temp_id = lot_row.serial_transaction_temp_id THEN
2057 /**2706001 checking group mark ids here **/
2058 l_already_used := 'N';
2059 BEGIN
2060 SELECT 'Y' INTO l_already_used FROM dual WHERE exists
2061 (SELECT 1
2062 FROM mtl_serial_numbers
2063 WHERE
2064 --Bug 2940878 fix added current_organization_id ,
2065 --inventory_item_id in the query
2066 -- also changed the condition on group_mark_id
2067 current_organization_id = mmtt_row.organization_id AND
2068 inventory_item_id = mmtt_row.inventory_item_id AND
2069 serial_number >= ser_row.fm_serial_number AND
2070 serial_number <= ser_row.to_serial_number AND
2071 --group_mark_id IS NOT NULL
2072 Nvl(group_mark_id, -1) <> -1
2073 );
2074 EXCEPTION
2075 WHEN no_data_found THEN
2076 l_already_used := 'N';
2077 WHEN OTHERS THEN
2078 mydebug('Error occurred '||Sqlerrm);
2079 l_already_used := NULL;
2080 RAISE fnd_api.g_exc_unexpected_error;
2081 END;
2082
2083 IF l_already_used = 'Y' then
2084 mydebug('failed quantity check ');
2085 RAISE g_qty_not_avail;
2086 END IF;
2087 /**2706001 checking group mark ids here **/
2088
2089 ser_row.transaction_temp_id:= ser_transaction_temp_id;
2090 inv_rcv_common_apis.insert_msnt(ser_row);
2091 ser_row := NULL;
2092 --lot_row.serial_transaction_temp_id := ser_row.transaction_temp_id;
2093 END IF;
2094 END LOOP;
2095
2096 /***********Serial Stuff *****************************/
2097 /**2706001 moved this assignment out of the loop **/
2098 lot_row.serial_transaction_temp_id := ser_transaction_temp_id;
2099
2100 lot_row.transaction_temp_id := new_txn_temp_id;
2101 inv_rcv_common_apis.insert_mtlt(lot_row);
2102 lot_row := NULL;
2103 END IF;
2104 END LOOP;
2105 END IF;
2106 ELSE
2107 IF (l_debug = 1) THEN
2108 mydebug('vanilla item');
2109 END IF;
2110
2111 IF NOT check_qty_avail(
2112 mmtt_row => mmtt_row
2113 , lot_row => NULL
2114 , ser_row => NULL
2115 , p_is_revision_control => l_rev_ctrl
2116 , p_is_lot_control => 'N'
2117 , p_is_serial_control => 'N'
2118 , p_allocate_serial_flag => l_alloc_ser
2119 ) THEN
2120 IF (l_debug = 1) THEN
2121 mydebug('failed quantity check ');
2122 END IF;
2123
2124 RAISE g_qty_not_avail;
2125 END IF;
2126 END IF;
2127
2128 IF (l_debug = 1) THEN
2129 mydebug(' inserting the new row into mmtt using ' || 'wms_task_dispatch_engine.insert_mmtt ');
2130 END IF;
2131
2132 mmtt_row.transaction_temp_id := new_txn_temp_id;
2133
2134
2135 --//***************//
2136
2137 --Add code here
2138 IF wms_device_integration_pvt.wms_call_device_request IS NULL THEN
2139 wms_device_integration_pvt.is_device_set_up(mmtt_row.organization_id,wms_device_integration_pvt.WMS_BE_MO_TASK_ALLOC,l_return_status);
2140 END IF;
2141
2142 --Insert records into WMS_DEVICE_REQUESTS TABLE
2143 wms_cartnzn_pub.insert_device_request_rec(mmtt_row);
2144
2145
2146
2147 -- Call Device Integration API to send the details of this
2148 -- Move Order Task Allocation to devices, if it is a WMS organization.
2149 -- Note: We don't check for the return condition of this API as
2150 -- we let the Allocation process succee irrespective of
2151 -- DeviceIntegration succeed or fail.
2152
2153 WMS_DEVICE_INTEGRATION_PVT.device_request
2154 (p_bus_event => WMS_DEVICE_INTEGRATION_PVT.WMS_BE_MO_TASK_ALLOC,
2155 p_call_ctx => WMS_Device_integration_pvt.DEV_REQ_AUTO,
2156 p_task_trx_id => NULL,
2157 x_request_msg => l_req_msg,
2158 x_return_status => l_return_status,
2159 x_msg_count => l_msg_count,
2160 x_msg_data => l_msg_data
2161 );
2162
2163 IF (l_debug = 1) THEN
2164 mydebug('Device_API: return stat:'||l_return_status);
2165 END IF;
2166
2167 --//**************//
2168
2169
2170 wms_task_dispatch_engine.insert_mmtt(l_mmtt_rec => mmtt_row);
2171
2172 IF (l_debug = 1) THEN
2173 mydebug(' calling wms_rule_pvt.assigntt ');
2174 END IF;
2175
2176 wms_rule_pvt.assigntt(
2177 p_api_version => 1.0
2178 , p_task_id => new_txn_temp_id
2179 , x_return_status => x_return_status
2180 , x_msg_count => x_msg_count
2181 , x_msg_data => x_msg_data
2182 );
2183
2184 IF (l_debug = 1) THEN
2185 mydebug('After calling wms_rule_pvt.assigntt l_return_status :'||x_return_status||' new_txn_temp_id :'||new_txn_temp_id);
2186 END IF;
2187
2188 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
2189 IF (l_debug = 1) THEN
2190 mydebug(' error returned from wms_rule_pvt.assigntt ');
2191 mydebug(x_msg_data);
2192 END IF;
2193
2194 RAISE fnd_api.g_exc_error;
2195 ELSE
2196 IF (l_debug = 1) THEN
2197 mydebug(' success returned from wms_rule_pvt.assigntt ');
2198 END IF;
2199 END IF;
2200 END IF;
2201 END LOOP;
2202 EXCEPTION
2203 WHEN g_qty_not_avail THEN
2204 ROLLBACK TO generate_next_task;
2205 x_return_status := fnd_api.g_ret_sts_error;
2206 x_ret_code := 'QTY_NOT_AVAIL';
2207 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2208 -- IF (x_msg_count = 0) THEN
2209 -- dbms_output.put_line('Successful');
2210 -- ELSIF (x_msg_count = 1) THEN
2211 -- dbms_output.put_line ('Not Successful');
2212 -- dbms_output.put_line (replace(x_msg_data,chr(0),' '));
2213 -- ELSE
2214 -- dbms_output.put_line ('Not Successful2');
2215 -- For I in 1..x_msg_count LOOP
2216 -- x_msg_data := fnd_msg_pub.get(I,'F');
2217 -- dbms_output.put_line(replace(x_msg_data,chr(0),' '));
2218 -- END LOOP;
2219 -- END IF;
2220
2221 WHEN fnd_api.g_exc_error THEN
2222 ROLLBACK TO generate_next_task;
2223 x_return_status := fnd_api.g_ret_sts_error;
2224 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2225 -- IF (x_msg_count = 0) THEN
2226 -- dbms_output.put_line('Successful');
2227 -- ELSIF (x_msg_count = 1) THEN
2228 -- dbms_output.put_line ('Not Successful');
2229 -- dbms_output.put_line (replace(x_msg_data,chr(0),' '));
2230 -- ELSE
2231 -- dbms_output.put_line ('Not Successful2');
2232 -- For I in 1..x_msg_count LOOP
2233 -- x_msg_data := fnd_msg_pub.get(I,'F');
2234 -- dbms_output.put_line(replace(x_msg_data,chr(0),' '));
2235 -- END LOOP;
2236 -- END IF;
2237
2238
2239 WHEN fnd_api.g_exc_unexpected_error THEN
2240 ROLLBACK TO generate_next_task;
2241 x_return_status := fnd_api.g_ret_sts_unexp_error;
2242 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2243 WHEN OTHERS THEN
2244 ROLLBACK TO generate_next_task;
2245 x_return_status := fnd_api.g_ret_sts_unexp_error;
2246
2247 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2248 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2249 END IF;
2250
2251 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2252 END generate_next_task;
2253
2254 PROCEDURE cancel_task(
2255 x_return_status OUT NOCOPY VARCHAR2
2256 , x_msg_count OUT NOCOPY NUMBER
2257 , x_msg_data OUT NOCOPY VARCHAR2
2258 , p_emp_id IN NUMBER
2259 , p_temp_id IN NUMBER
2260 , p_previous_task_status IN NUMBER := -1/*added for 3602199*/
2261
2262 ) IS
2263 l_dev_temp_id NUMBER := 0;
2264 l_dev_request_id NUMBER := 0;
2265 l_dev_request_msg VARCHAR2(1000);
2266 l_mo_line_id NUMBER := NULL;
2267 l_mmtt_count NUMBER;
2268 l_txn_temp_id NUMBER := NULL;
2269 l_txn_quantity NUMBER := 0;
2270 l_deleted_quantity NUMBER := 0;
2271
2272 CURSOR c_wdt_dispatched IS
2273 SELECT transaction_temp_id, device_request_id
2274 FROM wms_dispatched_tasks
2275 WHERE person_id = p_emp_id
2276 AND(status <= 3 OR status = 9)
2277 AND device_request_id IS NOT NULL;
2278
2279 CURSOR c_mo_line_id IS
2280 SELECT mtrl.line_id
2281 FROM mtl_material_transactions_temp mmtt
2282 , mtl_txn_request_lines mtrl
2283 WHERE (mmtt.transaction_temp_id = p_temp_id OR mmtt.parent_line_id = p_temp_id)
2284 AND mtrl.line_id = mmtt.move_order_line_id
2285 AND mtrl.line_status = INV_GLOBALS.G_TO_STATUS_CANCEL_BY_SOURCE;
2286
2287 CURSOR c_mmtt_to_del IS
2288 SELECT mmtt.transaction_temp_id, mmtt.primary_quantity
2289 FROM mtl_material_transactions_temp mmtt
2290 WHERE mmtt.move_order_line_id = l_mo_line_id
2291 AND NOT EXISTS(SELECT 1
2292 FROM mtl_material_transactions_temp t1
2293 WHERE t1.parent_line_id = mmtt.transaction_temp_id)
2294 AND NOT EXISTS(SELECT 1
2295 FROM wms_dispatched_tasks wdt
2296 WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id);
2297
2298 CURSOR c_get_mmtt_count IS
2299 SELECT count(*)
2300 FROM mtl_material_transactions_temp mmtt
2301 WHERE mmtt.move_order_line_id = l_mo_line_id
2302 AND NOT EXISTS ( SELECT 1
2303 FROM mtl_material_transactions_temp t1
2304 WHERE t1.parent_line_id = mmtt.transaction_temp_id);
2305
2306 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2307 BEGIN
2308 IF (l_debug = 1) THEN
2309 mydebug('Cancelling the Task: TxnTempID = ' || p_temp_id || ' : EmployeeID = ' || p_emp_id);
2310 END IF;
2311
2312 x_return_status := fnd_api.g_ret_sts_success;
2313
2314 -- Call device request for task cancel
2315 OPEN c_wdt_dispatched;
2316 LOOP
2317 FETCH c_wdt_dispatched INTO l_dev_temp_id, l_dev_request_id;
2318 EXIT WHEN c_wdt_dispatched%NOTFOUND;
2319
2320 IF l_dev_request_id IS NOT NULL THEN
2321 IF (l_debug = 1) THEN
2322 mydebug('Calling device Request for Device Temp ID = ' || l_dev_temp_id);
2323 END IF;
2324
2325 wms_device_integration_pvt.device_request(
2326 p_bus_event => wms_device_integration_pvt.wms_be_task_cancel
2327 , p_call_ctx => 'U'
2328 , p_task_trx_id => l_dev_temp_id
2329 , x_request_msg => l_dev_request_msg
2330 , x_return_status => x_return_status
2331 , x_msg_count => x_msg_count
2332 , x_msg_data => x_msg_data
2333 , p_request_id => l_dev_request_id
2334 );
2335 END IF;
2336 END LOOP;
2337 CLOSE c_wdt_dispatched;
2338
2339 ROLLBACK; --bug#2458131
2340
2341 -- Making all dispatched and active (Patchset I) tasks to pending tasks assigned to this user
2342 -- bug 3602199 keep queued tasks as queued and dont delete wdt
2343 -- DELETE FROM wms_dispatched_tasks WHERE person_id = p_emp_id AND status IN(3, 9);
2344 if(p_previous_task_status = 2/*queued*/) then
2345 DELETE FROM wms_dispatched_tasks WHERE person_id = p_emp_id AND status IN(3, 9) and transaction_temp_id <> p_temp_id;
2346 update wms_dispatched_tasks set status = 2 where transaction_temp_id = p_temp_id and person_id = p_emp_id;
2347 /* mydebug('Rows update in wdt 3602199' || SQL%ROWCOUNT);*/
2348 else/*old code*/
2349 DELETE FROM wms_dispatched_tasks where person_id = p_emp_id and status in (3,9);
2350 /* mydebug('All rows deleted from wdt' || SQL%ROWCOUNT);*/
2351 end if;
2352
2353 OPEN c_mo_line_id;
2354
2355 LOOP
2356 FETCH c_mo_line_id INTO l_mo_line_id;
2357 EXIT WHEN c_mo_line_id%NOTFOUND;
2358 IF (l_debug = 1) THEN
2359 mydebug('Cancelling Tasks for MO Line ID = ' || l_mo_line_id);
2360 END IF;
2361 l_deleted_quantity := 0;
2362
2363 OPEN c_mmtt_to_del;
2364 LOOP
2365 FETCH c_mmtt_to_del INTO l_txn_temp_id, l_txn_quantity;
2366 EXIT WHEN c_mmtt_to_del%NOTFOUND;
2367
2368 inv_trx_util_pub.delete_transaction(
2369 x_return_status => x_return_status
2370 , x_msg_data => x_msg_data
2371 , x_msg_count => x_msg_count
2372 , p_transaction_temp_id => l_txn_temp_id
2373 );
2374 IF x_return_status <> fnd_api.g_ret_sts_success THEN
2375 IF l_debug = 1 THEN
2376 mydebug('Not able to delete the Txn = ' || l_txn_temp_id);
2377 END IF;
2378 RAISE fnd_api.g_exc_unexpected_error;
2379 END IF;
2380
2381 l_deleted_quantity := l_deleted_quantity + l_txn_quantity;
2382 END LOOP;
2383 CLOSE c_mmtt_to_del;
2384
2385 OPEN c_get_mmtt_count;
2386 FETCH c_get_mmtt_count INTO l_mmtt_count;
2387 CLOSE c_get_mmtt_count;
2388
2389 UPDATE mtl_txn_request_lines
2390 SET quantity_detailed =(quantity_detailed - l_deleted_quantity)
2391 , line_status = DECODE(l_mmtt_count, 0, INV_GLOBALS.G_TO_STATUS_CLOSED, line_status)
2392 WHERE line_id = l_mo_line_id;
2393 END LOOP;
2394 CLOSE c_mo_line_id;
2395
2396 COMMIT;
2397 EXCEPTION
2398 WHEN OTHERS THEN
2399 x_return_status := fnd_api.g_ret_sts_unexp_error;
2400 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2401 fnd_msg_pub.add_exc_msg(g_pkg_name, 'CANCEL_TASK');
2402 END IF;
2403 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2404 END cancel_task;
2405
2406 /*****************************************************************/
2407 --This function is called from the currentTasksFListener on pressing
2408 --the Unload button,
2409 --returns Y if you can continue with the unload,
2410 --returns E,U if an error occurred in this api
2411 --returns N if you cannot unload and puts the appropriate error in the stack
2412 --returns M if you cannot unload because lpn has multiple allocations
2413 /*****************************************************************/
2414 FUNCTION can_unload(p_temp_id IN NUMBER)
2415 RETURN VARCHAR2 IS
2416 l_transfer_lpn_id NUMBER := NULL;
2417 l_multiple_rows VARCHAR2(1) := NULL;
2418 l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
2419 BEGIN
2420 IF (l_debug = 1) THEN
2421 mydebug(' In CAN_UNLOAD for transaction_temp_id ' || p_temp_id);
2422 END IF;
2423
2424 IF (p_temp_id IS NULL) THEN
2425 RAISE fnd_api.g_exc_unexpected_error;
2426 END IF;
2427
2428 BEGIN
2429 IF (l_debug = 1) THEN
2430 mydebug(' checking if the row has same lpn_id and content_lpn_id ');
2431 END IF;
2432
2433 SELECT transfer_lpn_id
2434 INTO l_transfer_lpn_id
2435 FROM mtl_material_transactions_temp
2436 WHERE transaction_temp_id = p_temp_id
2437 AND content_lpn_id = transfer_lpn_id;
2438
2439 IF (l_debug = 1) THEN
2440 mydebug(' lpn_id and content_lpn_id are the same ' || l_transfer_lpn_id);
2441 END IF;
2442 EXCEPTION
2443 WHEN NO_DATA_FOUND THEN
2444 IF (l_debug = 1) THEN
2445 mydebug(' lpn_id and content_lpn_id are different ');
2446 END IF;
2447
2448 RETURN 'Y';
2449 END;
2450
2451 IF (l_transfer_lpn_id IS NULL) THEN
2452 IF (l_debug = 1) THEN
2453 mydebug('ERROR: transfer_lpn passed is null');
2454 END IF;
2455
2456 RAISE fnd_api.g_exc_unexpected_error;
2457 END IF;
2458
2459 IF (l_debug = 1) THEN
2460 mydebug(' checking if the lpn has multiple allocations ');
2461 END IF;
2462
2463 BEGIN
2464 SELECT 'Y'
2465 INTO l_multiple_rows
2466 FROM DUAL
2467 WHERE EXISTS(SELECT transaction_temp_id
2468 FROM mtl_material_transactions_temp
2469 WHERE transfer_lpn_id = l_transfer_lpn_id
2470 AND transaction_temp_id <> p_temp_id);
2471
2472 IF (l_debug = 1) THEN
2473 mydebug(' lpn has multiple allocations ' || l_multiple_rows);
2474 END IF;
2475 EXCEPTION
2476 WHEN NO_DATA_FOUND THEN
2477 IF (l_debug = 1) THEN
2478 mydebug(' lpn has single allocation ');
2479 END IF;
2480
2481 RETURN 'Y';
2482 END;
2483
2484 fnd_message.set_name('WMS', 'WMS_LPN_MULTIPLE_ALLOC_ERR');
2485 fnd_msg_pub.ADD;
2486 RETURN 'M';
2487 EXCEPTION
2488 WHEN fnd_api.g_exc_error THEN
2489 fnd_message.set_name('WMS', 'WMS_CAN_UNLOAD_ERROR');
2490 fnd_msg_pub.ADD;
2491 RETURN fnd_api.g_ret_sts_error;
2492 WHEN fnd_api.g_exc_unexpected_error THEN
2493 fnd_message.set_name('WMS', 'WMS_CAN_UNLOAD_ERROR');
2494 fnd_msg_pub.ADD;
2495 RETURN fnd_api.g_ret_sts_unexp_error;
2496 WHEN OTHERS THEN
2497 IF (l_debug = 1) THEN
2498 mydebug('Exception occurred in can_unload api' || SQLERRM);
2499 END IF;
2500
2501 fnd_message.set_name('WMS', 'WMS_CAN_UNLOAD_ERROR');
2502 fnd_msg_pub.ADD;
2503 RETURN fnd_api.g_ret_sts_unexp_error;
2504 END can_unload;
2505
2506 /* over loaded the procedure can_unload to resolve the JDBC error */
2507 PROCEDURE can_unload(x_can_unload out NOCOPY VARCHAR2, p_temp_id IN NUMBER)
2508 IS
2509 BEGIN
2510 x_can_unload := can_unload(p_temp_id);
2511 END;
2512
2513 END wms_task_utils_pvt;